LineNum or VisOrder – Which One to Use???
I was once very active in the SAP B1 Forum doing quite a few things: answering questions, writing blogs, and developing a Wiki. I really enjoyed sharing what little bit of knowledge I had, thanks to Marilyn Pratt and Gordon Du who pushed me forward. In a phone call with Marilyn, she chided me (in her own unique way) for thinking what I knew was not that important, and I was being selfish to the "newbies". With Gordon – well, the fellow's attitude and willingness just inspired me to help, and Gordon was an excellent role model. It was working out very well with folks like Tim Guest. Tim and I worked together on a few things and it felt like I was really moving forward – got the chance to go to England several times. Even got to the point of being an SAP Mentor for SAP B1!!!
Then came "THE change" to the new SCN. All of my work somehow disappeared, badges were lost, I was unable to access areas I had been to, I somehow ended up with two User IDs (neither one worked), etc., etc. – if you were around at that time, you know the story. But with a recent lull in workload, I decided to peek into the SAP B1 forum again. A young lady (let's call her Pia) reached out to me about a problem she had with some SQL. I got Gordon's "attitude" again of being here. And I remember Marilyn saying "Just one little tid-bit. It does not have to be earth-shattering, but a piece of information which might help others. That's all, you got it, go for it".
But I think things are changing a bit. When folks start talking about the "good old days" that means progress is afoot.
https://answers.sap.com/articles/667215/a-sad-status-update-sad-how-things-are-going-aroun.html
So, as before, I am going to commit to doing periodic (weekly?) blogs to help those "newbies", and maybe a seasoned person along the way. This one might not be earth-shattering, but it is a piece of information which might help others along.
LineNum or VisOrder – Which one to use???
The vast majority of times when SQL is written, developers will use LineNum in the SQL. This LineNum is an internal system counter in SAP B1 which allows a person to do a complex JOIN to re-create the Standard SAP B1 "Relationship Map" function. If you go to a Sales Oder which has been closed, right click on the Sales Order, and select "Relationship Map", you will see all the Deliveries associated with that Sales Order and possibly links to AR Invoices, Sales Quotations, etc. The LineNum is what is used in part of the JOIN to create information similar to the "Relationship Map". (in another blog, I will show the usage of LineNum with BaseLine).
If you have End Users who are really good at verifying and testing SQL, LineNum might not give the actual Line Number on a displayed document. The End User might not even sign off on the SQL because it would be difficult for Users to relate / research SQL results to SAP B1 information. [Believe me, in the many years I have been around, I have seen a multitude of instances when an End User rejects an SQL on one little piece of data like this].
However, if you use VisOrder, your SQL will be correct every time. VisOrder = Visual Order = the line number displayed on the SAP B1 document while the End User is reviewing and testing your SQL.
How do the two fields of LineNum and VisOrder differ???
Let's take an example. For instance, when an End User uses the "Copy To" function to take a Delivery over to the AR Invoice, it might contain four lines. However, the End User sees there is a problem with the Pricing on displayed lines 1 and 2 which the person has to research later. Instead of impacting Cash Flow by delaying invoicing, the End User deletes lines 1 and 2, to bill those later, and invoices only displayed lines 3 and 4. To show the relationship map in SAP B1, SAP B1 keeps the original LineNum in INV1 (to go back to the original base Delivery Line), and creates the AR Invoice populating the data field VisOrder in INV1 with new numbers. Which is exactly what happened with AR Invoice Number 2121599 below.
Check out your own LineNum versus VisOrder.
Try running this SQL in your system. Use a good date range (say all of 2018) or use a longer range if you want, as this might happen infrequently. But you might just find it happens more often than you think. If there are no differences, you are lucky and must have Mega-Stars as Sales Personnel – every Customer I have worked with has had some kind of difference. It seems to always be there at some point in time.
Side note: I assume you know that you must add 1 to the VisOrder to get the displayed value. For some reason, technical folks decided to start with zero. Don't ask me why – I am not a techie and have no idea. You will see what I mean and what you have to do in the SQL below.
So, I am starting again with a small "tid-bit of information", like I did before to see what happens. Certainly, not earth-shattering, but it just might / maybe help someone sharpen their SQL skills.
But, you know what??? It is going to get a lot more interesting when I get on a roll. I promise...
Zal
Here is another blog and another posting on using LineNum in DTW.
https://blogs.sap.com/2018/12/30/sap-business-one-recreate-the-relationship-map-with-linenum-and-bas...
https://blogs.sap.com/2018/05/03/tips-for-linenum-of-dtw/
https://answers.sap.com/questions/711770/importing-pricing.html?childToView=719976#answer-719976
SAP B1 Forum SQL 01 2018 12 22 LineNum or VisOrder
I was once very active in the SAP B1 Forum doing quite a few things: answering questions, writing blogs, and developing a Wiki. I really enjoyed sharing what little bit of knowledge I had, thanks to Marilyn Pratt and Gordon Du who pushed me forward. In a phone call with Marilyn, she chided me (in her own unique way) for thinking what I knew was not that important, and I was being selfish to the "newbies". With Gordon – well, the fellow's attitude and willingness just inspired me to help, and Gordon was an excellent role model. It was working out very well with folks like Tim Guest. Tim and I worked together on a few things and it felt like I was really moving forward – got the chance to go to England several times. Even got to the point of being an SAP Mentor for SAP B1!!!
Then came "THE change" to the new SCN. All of my work somehow disappeared, badges were lost, I was unable to access areas I had been to, I somehow ended up with two User IDs (neither one worked), etc., etc. – if you were around at that time, you know the story. But with a recent lull in workload, I decided to peek into the SAP B1 forum again. A young lady (let's call her Pia) reached out to me about a problem she had with some SQL. I got Gordon's "attitude" again of being here. And I remember Marilyn saying "Just one little tid-bit. It does not have to be earth-shattering, but a piece of information which might help others. That's all, you got it, go for it".
But I think things are changing a bit. When folks start talking about the "good old days" that means progress is afoot.
https://answers.sap.com/articles/667215/a-sad-status-update-sad-how-things-are-going-aroun.html
So, as before, I am going to commit to doing periodic (weekly?) blogs to help those "newbies", and maybe a seasoned person along the way. This one might not be earth-shattering, but it is a piece of information which might help others along.
LineNum or VisOrder – Which one to use???
The vast majority of times when SQL is written, developers will use LineNum in the SQL. This LineNum is an internal system counter in SAP B1 which allows a person to do a complex JOIN to re-create the Standard SAP B1 "Relationship Map" function. If you go to a Sales Oder which has been closed, right click on the Sales Order, and select "Relationship Map", you will see all the Deliveries associated with that Sales Order and possibly links to AR Invoices, Sales Quotations, etc. The LineNum is what is used in part of the JOIN to create information similar to the "Relationship Map". (in another blog, I will show the usage of LineNum with BaseLine).
If you have End Users who are really good at verifying and testing SQL, LineNum might not give the actual Line Number on a displayed document. The End User might not even sign off on the SQL because it would be difficult for Users to relate / research SQL results to SAP B1 information. [Believe me, in the many years I have been around, I have seen a multitude of instances when an End User rejects an SQL on one little piece of data like this].
However, if you use VisOrder, your SQL will be correct every time. VisOrder = Visual Order = the line number displayed on the SAP B1 document while the End User is reviewing and testing your SQL.
How do the two fields of LineNum and VisOrder differ???
Let's take an example. For instance, when an End User uses the "Copy To" function to take a Delivery over to the AR Invoice, it might contain four lines. However, the End User sees there is a problem with the Pricing on displayed lines 1 and 2 which the person has to research later. Instead of impacting Cash Flow by delaying invoicing, the End User deletes lines 1 and 2, to bill those later, and invoices only displayed lines 3 and 4. To show the relationship map in SAP B1, SAP B1 keeps the original LineNum in INV1 (to go back to the original base Delivery Line), and creates the AR Invoice populating the data field VisOrder in INV1 with new numbers. Which is exactly what happened with AR Invoice Number 2121599 below.
Check out your own LineNum versus VisOrder.
Try running this SQL in your system. Use a good date range (say all of 2018) or use a longer range if you want, as this might happen infrequently. But you might just find it happens more often than you think. If there are no differences, you are lucky and must have Mega-Stars as Sales Personnel – every Customer I have worked with has had some kind of difference. It seems to always be there at some point in time.
Side note: I assume you know that you must add 1 to the VisOrder to get the displayed value. For some reason, technical folks decided to start with zero. Don't ask me why – I am not a techie and have no idea. You will see what I mean and what you have to do in the SQL below.
SELECT
T1.DocNum AS 'AR Invoice Numb',
T1.DocDate AS 'Posting Date',
T0.LineNum + 1 AS 'Internal Line Numb',
T0.VisOrder + 1 AS 'Display Line Numb',
(T0.LineNum - T0.VisOrder) AS 'Diff Between LineNum and VisOrder'
FROM INV1 T0
LEFT OUTER JOIN OINV T1
ON T0.DocEntry = T1.DocEntry
WHERE
T1.DocDate >= '[%0]'
AND T1.DocDate <= '[%1]'So, I am starting again with a small "tid-bit of information", like I did before to see what happens. Certainly, not earth-shattering, but it just might / maybe help someone sharpen their SQL skills.
But, you know what??? It is going to get a lot more interesting when I get on a roll. I promise...
Zal
Here is another blog and another posting on using LineNum in DTW.
https://blogs.sap.com/2018/12/30/sap-business-one-recreate-the-relationship-map-with-linenum-and-bas...
https://blogs.sap.com/2018/05/03/tips-for-linenum-of-dtw/
https://answers.sap.com/questions/711770/importing-pricing.html?childToView=719976#answer-719976
SAP B1 Forum SQL 01 2018 12 22 LineNum or VisOrder
- SAP Managed Tags:
8 Comments
