Feb 01, 2017 at 04:59 PM

SAP B1 Query for Customer Statement

1179 Views Last edit Feb 16, 2017 at 04:02 PM 3 rev

How can I create an SAP query for a customer statement I currently have a revenue query open items, but want to join the open payments. I want to show what is open on a customers account. Currently I can show open invoice and credits (Below) when I ask for open only, but how do I join open payments to the report.

Select T4.[SlpName] as 'Sales Employee', T0.CardCode as 'Customer Account Code',T0.cardname as 'Customer',T0.Docdate as 'Invoice Date', T0.docnum as 'Invoice Number', T0.Taxdate as 'Month Of Service',isnull(T0.U_AIS_DVISFSO,T3.U_AIS_DVISFSO) as 'SO#',T0.NumAtCard as 'PO#', T0.[U_DV_LOB_Brand], isnull(T0.U_AIS_DVIAdvNm,T3.U_AIS_DVIAdvNm) as 'Advertiser', isnull(T0.U_AIS_DVIOpptyNm,T3.U_AIS_DVIOpptyNm) as 'Campaign',T1.Dscription as 'Product',-T1.Quantity as 'Impressions',T1.Pricebefdi as 'CPM', Case T0.CurSource When 'C' Then T0.DocCur When 'L' Then T5.MainCurncy When 'S' Then T6.SysCurrncy End As 'Currency DC', Case T0.CurSource When 'C' Then -T1.TotalFrgn When 'L' Then -T1.LineTotal When 'S' Then -T1.TotalSumSy End As 'Total Bef Discount DC', Case T0.CurSource When 'L' Then -T1.[LineVat] When 'C' Then -T1.[LineVatlF] When 'S' Then -T1.[LineVatS] END As 'Vat Tax DC', Case when t1.visorder = 0 Then (Cast(Round((Case T0.CurSource When 'L' Then -T0.DocTotal When 'C' Then -T0.DocTotalFC When 'S' Then -T0.DocTotalSy End), 2) As DECIMAL(18,2))) ELSE 0 END As 'Doc Total DC', Case when t1.visorder = 0 Then (Cast(Round((Case T0.CurSource When 'L' Then -(T0.DocTotal - T0.PaidToDate) When 'C' Then -(T0.DocTotalFC - T0.PaidFC) When 'S' Then -(T0.DocTotalSy - T0.PaidSys) End), 2) As DECIMAL(18,2))) ELSE 0 END As 'Balance after Payment DC', ISNULL(T6.SysCurrncy,'USD') As 'Currency SC', -T1.TotalSumSy As 'Total Bef Discount SC', -T1.[LineVatS] As 'Vat Tax SC', Case when t1.visorder = 0 Then (Cast(Round(-(T0.DocTotalSy), 2) As DECIMAL(18,2))) ELSE 0 END As 'Doc Total SC', Case when t1.visorder = 0 Then (Cast(Round(-(T0.DocTotalSy - T0.PaidSys), 2) As DECIMAL(18,2))) ELSE 0 END As 'Balance after Payment SC', T0.docstatus, 'Credit Memo' AS TransactionType, T0.CurSource,T0.[Comments],T1.[U_DVIInvName], T1.[U_InvoiceAdj], T1.[U_DV_AdjustInvReason],T0.[U_CmpgnMinFreq],T0.[U_CmpgnMinAmt],T0.[U_DV_Orig_Inv], T0.[U_DV_SFStage], T0.[U_CampaignModifier], T0.[U_InvoiceType] from ORIN T0 left outer join RIN1 T1 on T0.docentry = T1.docentry left outer join RDR1 T2 on T1.Baseentry = T2.docentry and T1.baseline = T2.linenum left outer join ORDR T3 on T2.docentry = T3.docentry left outer join OSLP T4 ON T0.Slpcode= T4.Slpcode Left Join OADM T5 On T0.CurSource = 'L' Left Join OADM T6 On T0.CurSource = 'S' Where T0.[TaxDate] >= '[%1]' AND T0.[TaxDate] <= '[%2]' UNION ALL Select T4.[SlpName] as 'Sales Employee', T0.CardCode as "Customer Account Code",T0.cardname as 'Customer',T0.Docdate as 'Month of Service', T0.docnum as 'Invoice Number', T0.Taxdate as 'Date Sent',isnull(T0.U_AIS_DVISFSO,T3.U_AIS_DVISFSO) as 'SO#',T0.NumAtCard as 'PO#', T0.[U_DV_LOB_Brand], isnull(T0.U_AIS_DVIAdvNm,T3.U_AIS_DVIAdvNm) as 'Advertiser', isnull(T0.U_AIS_DVIOpptyNm,T3.U_AIS_DVIOpptyNm) as 'Campaign',T1.Dscription as 'product',T1.Quantity as 'Impressions',T1.Pricebefdi as 'CPM', Case T0.CurSource When 'C' Then T0.DocCur When 'L' Then T5.MainCurncy When 'S' Then T6.SysCurrncy End As 'Currency DC', Case T0.CurSource When 'C' Then T1.TotalFrgn When 'L' Then T1.LineTotal When 'S' Then T1.TotalSumSy End As 'Total Bef Discount DC', Case T0.CurSource When 'L' Then T1.[LineVat] When 'C' Then T1.[LineVatlF] When 'S' Then T1.[LineVatS] END As 'Vat Tax DC', Case when t1.visorder = 0 THEN (Cast(Round((Case T0.CurSource When 'L' Then T0.DocTotal When 'C' Then T0.DocTotalFC When 'S' Then T0.DocTotalSy End), 2) As DECIMAL(18,2))) ELSE 0 END As 'Doc Total DC', Case when t1.visorder = 0 Then (Cast(Round((Case T0.CurSource When 'L' Then T0.DocTotal - T0.PaidToDate When 'C' Then T0.DocTotalFC - T0.PaidFC When 'S' Then T0.DocTotalSy - T0.PaidSys End), 2) As DECIMAL(18,2))) ELSE 0 END As 'Balance after Payment DC', ISNULL(T6.SysCurrncy,'USD') As 'Currency SC', T1.TotalSumSy As 'Total Bef Discount SC', T1.[LineVatS] As 'Vat Tax SC', Case when t1.visorder = 0 Then (Cast(Round(T0.DocTotalSy, 2) As DECIMAL(18,2))) ELSE 0 END As 'Doc Total SC', Case when t1.visorder = 0 Then (Cast(Round(T0.DocTotalSy - T0.PaidSys, 2) As DECIMAL(18,2))) ELSE 0 END As 'Balance after Payment SC', T0.docstatus, 'Invoice' AS TransactionType,T0.CurSource, T0.[Comments],T1.[U_DVIInvName], T1.[U_InvoiceAdj], T1.[U_DV_AdjustInvReason],T0.[U_CmpgnMinFreq],T0.[U_CmpgnMinAmt],T0.[U_DV_Orig_Inv], T0.[U_DV_SFStage], T0.[U_CampaignModifier],T0.[U_InvoiceType] from OINV T0 left outer join INV1 T1 on T0.docentry = T1.docentry left outer join RDR1 T2 on T1.Baseentry = T2.docentry and T1.baseline = T2.linenum left outer join ORDR T3 on T2.docentry = T3.docentry left outer join OSLP T4 ON T0.Slpcode= T4.Slpcode Left Join OADM T5 On T0.CurSource = 'L' Left Join OADM T6 On T0.CurSource = 'S' Where T0.[TaxDate] >= '[%1]' AND T0.[TaxDate] <= '[%2]'