Wants Combain query of GRPO -Return- AP Invoice

[Former Member]

Dear All,

Pls. help me for this query.

A) GRPO :

Vendor code

Vendor name

Doc Num

Doc status

Posting date

Total Before discount

Tax

Freight

Total Payment Due

B) IF GOODS RETURNS ARE DONE THEN:

Doc Num

Doc status

Posting date

Total Before discount

Tax

Freight

Total Payment Due

C) IF AFTER GRPO ,AP INVOICE DONE THEN:

Doc Num

Vendor referance Number.

Doc status

Posting date

Total Before discount

Tax

Freight

Total Payment Due

(A) ,(B),(C) should in a single query.

If possible pls. send me query.

Regards.

Mahesh.

[Former Member · Accepted solution]

All the inventory transactions are stored in OINM table.

You can write a query to display the required values from OINM table

[Former Member]

Try this one:

SELECT DISTINCt T1.CARDCODE, T1.CARDNAME , T1.DOCNUM GRPO#,T1.DocStatus 'GRPO Status', T1.DOCDATE 'GRPO Date', T1.DocTotal 'GRPO Total', T3.DOCNUM Return#,T3.DocStatus 'Return Status', T3.DOCDATE 'Return Date', T3.DocTotal 'Return Total',T5.DOCNUM Invoice#,T5.DocStatus 'Invoice Status', T5.DOCDATE 'Invoice Date', (T5.DocTotal-T5.VatSum-T5.TotalExpns-T5.DiscSum) 'Total Before Discount', T5.VatSum 'Tax', T5.TotalExpns 'Freight', (T5.DocTotal - T5.PaidToDate) 'Total Payment Due'

FROM DBO.PDN1 T0

INNER JOIN DBO.OPDN T1 ON T1.DOCENTRY = T0.DOCENTRY

LEFT JOIN DBO.RPD1 T2 ON T2.BASEENTRY = T1.DOCENTRY

LEFT JOIN DBO.ORPD T3 ON T2.DOCENTRY = T3.DOCENTRY

LEFT JOIN DBO.PCH1 T4 ON T4.BASEENTRY = T1.DOCENTRY

LEFT JOIN DBO.OPCH T5 ON T5.DOCENTRY = T4.DOCENTRY

WHERE T1.DOCNUM like '[%0\]%'

Thanks,

Gordon