AR Invoice and AR Credit Query

[bbranco]

Hi,

Can someone help me with this?

I have query in Crystal that pulls in AR Invoices from OINV and quantity and line total from INV1 and it pulls a value from OITM for a standard cost.

This is working fine, I need to add AR Credits the same way as its pulling in this data.

SELECT T0."DocNum", T0."CardCode", SUM(T1."LineTotal") AS "Total Sales", T0."CardName", T0."DocDate", SUM(T1."Quantity" * T2."U_AIS_STD_Cost") AS "Total Cost", SUM(T1."LineTotal") - SUM(T1."Quantity" * T2."U_AIS_STD_Cost") AS "Total Profit" FROM "SSAC_GOLIVE".OINV T0 INNER JOIN "SSAC_GOLIVE".INV1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN "SSAC_GOLIVE".OITM T2 ON T1."ItemCode" = T2."ItemCode" WHERE T0."CANCELED" = 'N' GROUP BY T0."DocNum",T0."CardCode",T0."CardName",T0."DocDate" ORDER BY T0."DocNum"

It needs to show each AR invoice and each AR Credit on separate lines sorted by the DocNum

Any help would be appreciated.

[NEMOTO]

Hi How about this?

SELECT T0."DocNum", T0."CardCode", SUM(T1."LineTotal") AS "Total Sales", T0."CardName", T0."DocDate", SUM(T1."Quantity" * T2."U_AIS_STD_Cost") AS "Total Cost", SUM(T1."LineTotal") - SUM(T1."Quantity" * T2."U_AIS_STD_Cost") AS "Total Profit" FROM "SSAC_GOLIVE".OINV T0 INNER JOIN "SSAC_GOLIVE".INV1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN "SSAC_GOLIVE".OITM T2 ON T1."ItemCode" = T2."ItemCode" WHERE T0."CANCELED" = 'N' GROUP BY T0."DocNum",T0."CardCode",T0."CardName",T0."DocDate"

UNION ALL

SELECT T0."DocNum", T0."CardCode", SUM(T1."LineTotal") AS "Total Sales", T0."CardName", T0."DocDate", SUM(T1."Quantity" * T2."U_AIS_STD_Cost") AS "Total Cost", SUM(T1."LineTotal") - SUM(T1."Quantity" * T2."U_AIS_STD_Cost") AS "Total Profit" FROM "SSAC_GOLIVE".ORIN T0 INNER JOIN "SSAC_GOLIVE".RIN1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN "SSAC_GOLIVE".OITM T2 ON T1."ItemCode" = T2."ItemCode" WHERE T0."CANCELED" = 'N' GROUP BY T0."DocNum",T0."CardCode",T0."CardName",T0."DocDate" ORDER BY "DocNum"

Regards,

Ryoji

[bbranco]

Ryoji,

Thanks for your help, it worked great!!!

Brian