Purchase Report

[former_member794521]

Good day,

Would like some assistance in building up a report using the query generator.

I want to generate a report for Purchases with the dates for each order from start to finish with number of days it took.

- purchase order,

- down & balance payment,

- landed cost.

-vendor

[former_member794521 · Accepted solution]

This did the trick, closing thread.

SELECT

T0."CardName" as "Vendor",

T0."DocEntry" as "A/P INV",

T0."DocNum" as "Invoice #",

T1."DocEntry" as "PO",

T2."DocEntry" as "LC",

T2."DocTotal" as "Landed Cost",

DAYS_BETWEEN(T0."DocDate",T0."DocDueDate") as "Days between start to finish",

T3."DrawnSum" as "Down Payment",

T0."DocTotal" - T0."PaidToDate" as "Balance Payment Due"

FROM OPCH T0

INNER JOIN ( SELECT DISTINCT _PC1."DocEntry", _PC1."BaseEntry" FROM PCH1 _PC1) as "AP_LL" ON AP_LL."DocEntry" = T0."DocEntry"

LEFT JOIN OPOR T1 ON T1."DocEntry" = AP_LL."BaseEntry"

LEFT JOIN ( SELECT DISTINCT _IP1."DocEntry", _IP1."BaseEntry" FROM IPF1 _IP1) as "LC_LL" ON LC_LL."BaseEntry" = T0."DocEntry"

LEFT JOIN OIPF T2 ON T2."DocEntry" = LC_LL."DocEntry"

LEFT JOIN PCH9 T3 ON T3."DocEntry" = T0."DocEntry"

WHERE

( T0."DocDate" BETWEEN [%0] AND [%1] ) OR (T0."CardName" = [%2]) AND

T0."CANCELED" = 'N'

ORDER BY T0."CardName", T2."DocEntry"