Mentioned the Relationship Map in the last blog. If you want to review some information on the usage for the data fields of LineNum versus VisOrder, have a look here:
https://blogs.sap.com/2018/12/23/sap-business-one-linenum-or-visorder-which-one-to-use/
When SAP approved my first blog, I received an Email about an hour later requesting some clarification on LineNum and what I meant…geez – that was fast, but…a request is a request so…onwards we go.
I wish someone had shown me this when I first started out - sure would have helped me!!! And I will provide examples on the recreation of Relationship Map in several different ways, since we all learn differently.
THE STANDARD SAP B1 RELATIONSHIP MAP FUNCTION.
The Relationship Map is a pretty nice tool in researching the way a document has been handled in the regular business process inside SAP B1 when End Users use the "Copy To" and the "Copy From" functions. The Users MUST use the "Copy To" or "Copy From" for SAP B1 to display the Relationship Map - that is the only way the standard "connection" is made. That is also true for the SQL included below. There are different ways (Posting Details. Etc.) you can display on the Relationship Map itself inside of SAP B1, but I am covering the "Document Tree" map selection as below.
What I want to do is to give you some basic, routine SQL JOINs you can use to duplicate this Relationship Map so you can provide even more information to the End User. The Relationship Map is a great tool, but it gives you just a "high-level" view to the relationships between documents, and you need to open up each document to find out what is on those multiple Deliveries and multiple AR Invoices. It can get very confusing if you are looking to see what happened to Line 1 on a Sales Order which has been fulfilled by 5 different Deliveries. It can also take a lot of time.
I have had about 50 Customers or so who requested more information than what they could see in this "high-level" Standard SAP B1 Function. For example, some wanted to see the relationship at a Line Level just to do basic research on Item Movement (like the SQL provided below). Other Customers wanted to see how the lines of a Sales Orders are related to the Delivery, along with Quantities, Item Description, Price, Cost, etc. You can provide a lot more information than what is seen on the Relationship Map using the SQL provided below, just by adding more detail within the SELECT DISTINCT section of the SQL.
I am going to concentrate on Sales Order 58536 and the multiple Deliveries associated with that Sales Order as you see below. For our example, let's just say the Customer wants to see only the Item Codes associated with that Sales Order. SQL to answer the question of "what products are on each of those deliveries for Sales Order 58536?".
CONCEPTUAL POINTS AND BASIS OF RELATIONSHIP.
The scenario described here is an End User uses the "Copy To" button on a Sales Order to create multiple Deliveries BY LINE. Or maybe they used "Copy From" on the Delivery. The SQL provided will cover "Copy To" and "Copy From".
Tables involved are ORDR (Sales Order), RDR1 (Sales Order Line), ODLN (Delivery), and DLN1 (Delivery Line).
The Sales Order is called the BASE Document and the Delivery is called the TARGET Document, and for it to be correct per Customer requirements, relationship information must be shown at the line level for products.
So, we start with the BASE Document information first (Sales Order tables) with the important fields to combine with the TARGET Document information (Delivery tables) for this example.
ORDR.DocEntry = DLN1.BaseEntry
RDR1.LineNum = DLN1.BaseLine
This relationship JOIN is where LineNum is used.
Just for good measure, I like to include a check on the ItemCode as well – this is not required, but just a personal preference I have when doing the SQL.
RDR1.ItemCode = DLN1.ItemCode
CROSS REFERENCE DIAGRAM.
Some learn it best from the way described above, but some like to see illustrations, so here is something for us "Visual Learners":
If this cross-refence concept is new to you, keep this diagram handy so you can duplicate it with other tables. Draw it out again with other tables on a blank sheet of paper to confirm what you have learned. I did that over and over again until it was burned into my brain cells.
NOTE: There is another way to build a Relationship Map with SQL, but I am not going to cover that in the SQL provided. Go back and look at the Cross-Reference above. You can try this on your own later on by using:
RDR1.TrgetEntry = ODLN.DocEntry
RDR1.LineNum = DLN1.BaseLine
RDR1.ItemCode = DLN1.ItemCode
SQL EXAMPLE
However, others learn best by using the SQL itself, so copy this code into your test system if you happen to learn best by doing. See what you get when it runs.
After you are done getting comfortable with the SQL above, and for your own benefit, you should probably add more columns to the SQL like LineStatus, DocStatus, Quantity, or whatever else you might think is interesting to see – just try something to display more information which might apply to your Company. I have personally found that End Users love this duplication of the Relationship Map function and the amount of detail they can ask for.
USAGE OF LEFT OUTER JOIN IN THE SQL.
This point is very important for your SQL results.
You can continue onwards with the duplication of the Relationship Map via SQL in the Sales - AR Module by adding more tables to this SQL with LEFT OUTER JOIN. Use additional LEFT OUTER JOINS following the pattern of DocEntry / BaseEntry and LineNum / BaseLine to combine the Delivery to the AR Invoice and the AR Invoice to the AR Credit Memo.
The reason why I use LEFT OUTER JOIN on all tables is that IF a Sales Order Line has not been moved to a Delivery, the coumns of DLN1 BaseEntry, DLN1 BaseLine, RDR1 TrgetEntry, and the ODLN DocEntry will be empty. Open Sales Order Lines look like this inside the SQL Results:
IMPORTANT CAVEAT: If you have personnel who are doing the Sales Process by using Sales Order to Delivery to AR Invoice one time, and then doing the Sales Process by using Sales Order to AR Invoice the next time, the SQL needs to be written differently, but it goes along the same lines with BaseEntry and BaseLine. I know the End Users are supposed to be following a standard process, but sometimes you find those "inquisitive" Users who try out everything they can. Thank goodness for them, because it means we have a job which continually gets interesting, challenging, and rewarding!!!
Well, there is the usage and purpose of LineNum as I mentioned in the first blog. There are other usages specific to LineNum – maybe I will write something up on that. But go ahead and try this concept out…I bet you are going to like it (along with your End Users)…
A few other SCN items connected with this topic:
https://answers.sap.com/questions/723578/linking-delivery-document-with-invoice-in-sap-b1.html?child...
https://answers.sap.com/questions/711770/importing-pricing.html?childToView=718118#comment-718118
https://blogs.sap.com/2018/12/23/sap-business-one-linenum-or-visorder-which-one-to-use/
SAP B1 Forum SQL 02 2018 12 29 Build the Relationship Map with LineNum and BaseLine
https://blogs.sap.com/2018/12/23/sap-business-one-linenum-or-visorder-which-one-to-use/
When SAP approved my first blog, I received an Email about an hour later requesting some clarification on LineNum and what I meant…geez – that was fast, but…a request is a request so…onwards we go.
I wish someone had shown me this when I first started out - sure would have helped me!!! And I will provide examples on the recreation of Relationship Map in several different ways, since we all learn differently.
THE STANDARD SAP B1 RELATIONSHIP MAP FUNCTION.
The Relationship Map is a pretty nice tool in researching the way a document has been handled in the regular business process inside SAP B1 when End Users use the "Copy To" and the "Copy From" functions. The Users MUST use the "Copy To" or "Copy From" for SAP B1 to display the Relationship Map - that is the only way the standard "connection" is made. That is also true for the SQL included below. There are different ways (Posting Details. Etc.) you can display on the Relationship Map itself inside of SAP B1, but I am covering the "Document Tree" map selection as below.
What I want to do is to give you some basic, routine SQL JOINs you can use to duplicate this Relationship Map so you can provide even more information to the End User. The Relationship Map is a great tool, but it gives you just a "high-level" view to the relationships between documents, and you need to open up each document to find out what is on those multiple Deliveries and multiple AR Invoices. It can get very confusing if you are looking to see what happened to Line 1 on a Sales Order which has been fulfilled by 5 different Deliveries. It can also take a lot of time.
I have had about 50 Customers or so who requested more information than what they could see in this "high-level" Standard SAP B1 Function. For example, some wanted to see the relationship at a Line Level just to do basic research on Item Movement (like the SQL provided below). Other Customers wanted to see how the lines of a Sales Orders are related to the Delivery, along with Quantities, Item Description, Price, Cost, etc. You can provide a lot more information than what is seen on the Relationship Map using the SQL provided below, just by adding more detail within the SELECT DISTINCT section of the SQL.
I am going to concentrate on Sales Order 58536 and the multiple Deliveries associated with that Sales Order as you see below. For our example, let's just say the Customer wants to see only the Item Codes associated with that Sales Order. SQL to answer the question of "what products are on each of those deliveries for Sales Order 58536?".
CONCEPTUAL POINTS AND BASIS OF RELATIONSHIP.
The scenario described here is an End User uses the "Copy To" button on a Sales Order to create multiple Deliveries BY LINE. Or maybe they used "Copy From" on the Delivery. The SQL provided will cover "Copy To" and "Copy From".
Tables involved are ORDR (Sales Order), RDR1 (Sales Order Line), ODLN (Delivery), and DLN1 (Delivery Line).
The Sales Order is called the BASE Document and the Delivery is called the TARGET Document, and for it to be correct per Customer requirements, relationship information must be shown at the line level for products.
So, we start with the BASE Document information first (Sales Order tables) with the important fields to combine with the TARGET Document information (Delivery tables) for this example.
ORDR.DocEntry = DLN1.BaseEntry
RDR1.LineNum = DLN1.BaseLine
This relationship JOIN is where LineNum is used.
Just for good measure, I like to include a check on the ItemCode as well – this is not required, but just a personal preference I have when doing the SQL.
RDR1.ItemCode = DLN1.ItemCode
CROSS REFERENCE DIAGRAM.
Some learn it best from the way described above, but some like to see illustrations, so here is something for us "Visual Learners":
If this cross-refence concept is new to you, keep this diagram handy so you can duplicate it with other tables. Draw it out again with other tables on a blank sheet of paper to confirm what you have learned. I did that over and over again until it was burned into my brain cells.
NOTE: There is another way to build a Relationship Map with SQL, but I am not going to cover that in the SQL provided. Go back and look at the Cross-Reference above. You can try this on your own later on by using:
RDR1.TrgetEntry = ODLN.DocEntry
RDR1.LineNum = DLN1.BaseLine
RDR1.ItemCode = DLN1.ItemCode
SQL EXAMPLE
However, others learn best by using the SQL itself, so copy this code into your test system if you happen to learn best by doing. See what you get when it runs.
SELECT DISTINCT
T1.DocNum, T1.DocDate, T1.CardCode,
T1.DocEntry AS 'ORDR DocEntry',
T0.LineNum AS 'RDR1 LineNum',
T0.ItemCode,
T2.BaseEntry AS 'DLN1 BaseEntry',
T2.BaseLine AS 'DLN1 BaseLine',
T2.ItemCode,
T0.TrgetEntry AS 'RDR1TrgetEntry',
T3.DocEntry AS 'ODLN DocEntry'
FROM RDR1 T0
LEFT OUTER JOIN ORDR T1
ON T0.DocEntry = T1.DocEntry
LEFT OUTER JOIN DLN1 T2
ON T1.DocEntry = T2.BaseEntry
AND T0.LineNum = T2.BaseLine
AND T0.ItemCode = T2.ItemCode
LEFT OUTER JOIN ODLN T3
ON T2.DocEntry = T3.DocEntry
After you are done getting comfortable with the SQL above, and for your own benefit, you should probably add more columns to the SQL like LineStatus, DocStatus, Quantity, or whatever else you might think is interesting to see – just try something to display more information which might apply to your Company. I have personally found that End Users love this duplication of the Relationship Map function and the amount of detail they can ask for.
USAGE OF LEFT OUTER JOIN IN THE SQL.
This point is very important for your SQL results.
You can continue onwards with the duplication of the Relationship Map via SQL in the Sales - AR Module by adding more tables to this SQL with LEFT OUTER JOIN. Use additional LEFT OUTER JOINS following the pattern of DocEntry / BaseEntry and LineNum / BaseLine to combine the Delivery to the AR Invoice and the AR Invoice to the AR Credit Memo.
The reason why I use LEFT OUTER JOIN on all tables is that IF a Sales Order Line has not been moved to a Delivery, the coumns of DLN1 BaseEntry, DLN1 BaseLine, RDR1 TrgetEntry, and the ODLN DocEntry will be empty. Open Sales Order Lines look like this inside the SQL Results:
IMPORTANT CAVEAT: If you have personnel who are doing the Sales Process by using Sales Order to Delivery to AR Invoice one time, and then doing the Sales Process by using Sales Order to AR Invoice the next time, the SQL needs to be written differently, but it goes along the same lines with BaseEntry and BaseLine. I know the End Users are supposed to be following a standard process, but sometimes you find those "inquisitive" Users who try out everything they can. Thank goodness for them, because it means we have a job which continually gets interesting, challenging, and rewarding!!!
Well, there is the usage and purpose of LineNum as I mentioned in the first blog. There are other usages specific to LineNum – maybe I will write something up on that. But go ahead and try this concept out…I bet you are going to like it (along with your End Users)…
A few other SCN items connected with this topic:
https://answers.sap.com/questions/723578/linking-delivery-document-with-invoice-in-sap-b1.html?child...
https://answers.sap.com/questions/711770/importing-pricing.html?childToView=718118#comment-718118
https://blogs.sap.com/2018/12/23/sap-business-one-linenum-or-visorder-which-one-to-use/
SAP B1 Forum SQL 02 2018 12 29 Build the Relationship Map with LineNum and BaseLine
- SAP Managed Tags:
2 Comments
