Skip to Content
0

SAP B1 Query for Customer Statement

Feb 01, 2017 at 04:59 PM

233

avatar image

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]'

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

avatar image
Former Member Feb 05, 2017 at 11:42 AM
0

Hello,

What do you mean when you say open payment? Do you want to see open Invoices still not paid?

Thanks,

Engr. Taseeb Saeed

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 08, 2017 at 06:57 AM
0

Hi,

It is not easy build such query. Better use standard system report.

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Danielle Napoli Feb 16, 2017 at 12:58 AM
0

What I need is to show the balance due for a customer I already have the invoices and credits but need to show any payments on account added to my report to show the total due

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

HI

u need to see on account payment as well as Customer balance due

so what about pay on invoice and other payments such as , JE

Regards

0
Danielle Napoli Feb 16, 2017 at 01:01 AM
0

How do you change the tag?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Danielle,

Please click the Action button under your avatar. Then you can choose the edit option from the menu.

Regards,

Johan

0