SQL Query for Retreiving SAP sales data containing details of 1) Posting date 2) Document Number 3) Customer Name 4) Invoice Amount 5) payment group 6) Aging

SQL Query for Retreiving SAP sales data containing details of 1) Posting date 2) Document Number 3) Customer Name 4) Invoice Amount 5) payment group 6)  Aging:

If you want to create a query in SAP Business One to retrieve sales data containing the details of the Posting Date, Document Number, Customer Name, Invoice Amount, Payment Group, and Aging, you can use the following query:

 

(Open SAP, Click Tools, Click Query, Click Query Generator and Paste following query and click execute):

SQL Query code:

SELECT T0.DocDate AS 'Posting Date', T0.DocNum AS 'Document Number', T1.CardName AS 'Customer Name', T0.DocTotal AS 'Invoice Amount', T0.GroupNum AS 'Payment Group', (DATEDIFF(dd, T0.DocDueDate, GETDATE())) AS 'Aging' FROM ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode WHERE T0.DocType = 'I'

 

In this query, we are using the ORDR table to retrieve sales order information, and we are joining it with the OCRD table to retrieve customer names. The "DocDate" field in the ORDR table corresponds to the Posting Date field in SAP Business One, and the "DocNum" field corresponds to the Document Number field.

The "Aging" field is calculated by subtracting the due date (DocDueDate) from the current system date (GETDATE()). The result is the number of days between the two dates, which gives us the aging of the invoice.

 

Labels:

SQL Query for SAP

SQL Query code for SAP Sales Data

SQL Query code for SAP Customers' receivable data