PT Sterling Tulus Cemerlang
www.sterling-team.com

Phone
+62-877-8655-5055
+62 (21) 580-6055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower 18th
Jl Jend. Sudirman kav 45-46
Jakarta 12930 - Indonesia

400+ FREE Tips for you from Sterling Tulus Cemerlang (STEM) - SAP Indonesia Gold Partner, and Become SAP B1 Super User

Custom Report – Query List of Sales Order (SO) Header and Detail

SAP Business One Tips Query for Sales Order
SHARE ON

Tips SAP Indonesia – As we know, SAP Business One provides a feature / facilities to make it easier for us in retrieving data from the system using queries to the database.

In this example, the standard Sales Order report from SAP Business One does not satisfy you because it does not directly display Header Data and Details, or there are certain additional fields that you want to display, even custom fields.

Below is one example of using a custom report query to show list of  Header and Detail Sales Orders (SO) in SAP Business One, created in certain date period :

/* List SO Per Periode */

SELECT

                CASE WHEN T1.[LineNum] = ” THEN T0.[DocNum] ELSE NULL END SO_Number,

                CASE WHEN T1.[LineNum] = ” THEN T0.[DocDate] ELSE NULL END SO_Date,

                CASE WHEN T1.[LineNum] = ” THEN T0.[CardCode] ELSE NULL END Customer_Code,

                CASE WHEN T1.[LineNum] = ” THEN T0.[CardName] ELSE NULL END Customer_Name,

                CASE WHEN T1.[LineNum] = ” THEN 1 ELSE T1.[LineNum]+1 END LINE_NO,

                T1.[ItemCode],

                T1.[Dscription],

                T1.[Quantity],

                T1.[Price],

                T1.[Currency],

                T1.[Rate],

                T1.[DiscPrcnt],

                T1.[LineTotal]

FROM ORDR T0 

INNER JOIN RDR1 T1

ON         T0.[DocEntry] = T1.[DocEntry]

                AND T0.[DocDate] >= [%0]

                AND T0.[DocDate] <= [%1]

ORDER BY T0.[DocNum], T1.[LineNum]

 


When we execute the query, SAP Business One will ask for period of SO date that we want to retrieve

And when we click the “OK” button, SAP Business One will execute the query and show the result as below :

You can see other tips related with query manager in SAP Business One in the link below :

Run Your Business With The Piece of Mind Using SAP Business One

This article was written by PT. Sterling Tulus Cemerlang, SAP Business One GOLD Partner in Indonesia.
More details about SAP Business One,
email to: sales-enquiry@sterling-team.com or call +6221-5806336 or wa +6287786555055

, , ,