Nov 05, 2014 at 03:57 PM

AR Invoice and CM Query Merge

47 Views

We run 2 reports that show us AR Invoices and AR Credit Memos by date and a few properties. The reports are exactly the same except one is pulling from OINV and the other from ORIN. They are shown below. Is there a way to make them into one report?

Thanks for the help,

Credit Memo Query:

SELECT T0.[DocNum], T0.[DocType], T0.[CANCELED], T0.[DocStatus], T0.[DocDueDate], T0.[DocDate], T0.[TaxDate], T0.[CardCode], T0.[CardName], T2.[GroupCode], T1.[LineStatus], T1.[ItemCode], T1.[Dscription], T3.[ItmsGrpCod], T1.[Quantity], T1.[LineTotal], T0.[DocTotal] FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode WHERE T0.[TaxDate] >=[%0] and T0.[TaxDate] <=[%1] AND T0.[CANCELED] = 'N' AND T1.[ItemCode] <> 'Free Freight Promo' AND T1.[ItemCode] <> 'Free Freight Bedell' AND T1.[ItemCode] <> 'Shipping' AND T2.[QryGroup6] = [%2] and T2.[QryGroup23] = [%3] AND T2.[QryGroup24] = [%4]

AR Invoice Query:

SELECT T0.[DocNum], T0.[DocType], T0.[CANCELED], T0.[DocStatus], T0.[DocDueDate], T0.[DocDate], T0.[TaxDate], T0.[CardCode], T0.[CardName], T2.[GroupCode], T1.[LineStatus], T1.[ItemCode], T1.[Dscription], T3.[ItmsGrpCod], T1.[Quantity], T1.[LineTotal], T0.[DocTotal] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode WHERE T0.[TaxDate] >=[%0] and T0.[TaxDate] <=[%1] AND T0.[CANCELED] = 'N' AND T1.[ItemCode] <> 'Free Freight Promo' AND T1.[ItemCode] <> 'Free Freight Bedell' AND T1.[ItemCode] <> 'Shipping' AND T2.[QryGroup6] = [%2] and T2.[QryGroup23] = [%3] AND T2.[QryGroup24] = [%4]