or_retrieve

SELECT Mid(A.OrNo,4,11) AS [OR], A.OrDate, A.OrType, A.FormOfPayment, A.Payment, A.ActualPayment, A.RebateDiscount, A.Interest, A.BusinessName, A.TIN, A.InvoiceType, A.InvoiceNo, Iif(not IsNull(B.InvoiceDate),C.InvoiceDate,
Iif(not IsNull(C.InvoiceDate),C.InvoiceDate,
Iif(not Isnull(D.InvoiceDate),D.InvoiceDate))), A.Fullname, A.Address, A.BankBranch, A.CheckNo, A.DateofCheck
FROM
(
(
(
OfficialReceipt AS A
LEFT JOIN AccountCashBasis AS B ON ((A.InvoiceNo=B.InvoiceNo) AND (A.InvoiceType='MSC'))
)
LEFT JOIN AccountInstallment AS C ON ((A.Invoiceno=C.InvoiceNo) AND (A.InvoiceType='MSI'))
)
LEFT JOIN ItemInvoiceHeader AS D ON ((A.InvoiceNo=D.InvoiceNo) AND (A.InvoiceType='ISC'))
)


WHERE A.OrNo = p_OrNo;

blangkong invoiceno

pag nag-save yung or, pag naka-select yung option either Motor Sales Invoice or Motor Sales Cash, dapat hindi blangko yung invoiceno

OUT WITH NO IN

CREATE PROCEDURE RptOnOutMotorWithNoIn
@AsOf DATETIME
AS

select
*
from
(
SELECT Y.ModelNo, Y.ChassisNo, Y.EngineNo, Y.KeyNo, Y.TranType, OutNo = Y.ReferenceNo, OutDate = Y.TransactDate, InNo = X.ReferenceNo, InDate = X.TransactDate FROM
(
select modelno, chassisno, engineno, keyno, trantype, referenceno, transactdate
from audittrailmotor where
transactdate <= @AsOf
branchcode = '09'
and inveffect = '-'
) AS Y


LEFT JOIN


(
select modelno, chassisno, engineno, keyno, trantype, referenceno, transactdate from audittrailmotor where
branchcode = '09'
and inveffect = '+'
) AS X

ON X.MODELNO = Y.MODELNO
and X.Chassisno = Y.ChassisNo
AND X.EngineNo = Y.EngineNo
AND X.KeyNo = Y.KeyNo

) as z

WHERE InDate IS NULL
ORDER BY MODELNO, ChassisNo, EngineNo, KeyNo

GO

IN WITH NO OUT

CREATE PROCEDURE RptOnInMotorWithNoOut
@AsOf DATETIME
AS

select
*
from
(
SELECT Y.ModelNo, Y.ChassisNo, Y.EngineNo, Y.KeyNo, Y.TranType, InNo = Y.ReferenceNo, InDate = Y.TransactDate, OutNo = X.ReferenceNo, OutDate = X.TransactDate FROM
(
select modelno, chassisno, engineno, keyno, trantype, referenceno, transactdate from audittrailmotor where
transactdate <= @AsOf
branchcode = '09'
and inveffect = '+'
) AS Y


LEFT JOIN


(
select modelno, chassisno, engineno, keyno, trantype, referenceno, transactdate from audittrailmotor where
branchcode = '09'
and inveffect = '-'
) AS X

ON X.MODELNO = Y.MODELNO
and X.Chassisno = Y.ChassisNo
AND X.EngineNo = Y.EngineNo
AND X.KeyNo = Y.KeyNo

) as z
WHERE OUTDATE IS NULL
ORDER BY MODELNO, CHASSISNO, ENGINENO, KEYNO