2 weeks ago
Hi,
I have a user defined table that contains the following
Sales Order Number Tracking Number Shipping Charges Updated
10000 1Z72345678 25 N
10000 1Z1234567 35 N
10001 1Z1235555 40 N
On the marketing documents, I have fields for Tracking Number and Shipping Charges
When the user copies the sales order to a delivery note, I want to pull the data from the UDT and put it in the same fields on the marketing document and sum and concatenate the numbers if the updated field is set to N.
Delivery Note Tracking Number Shipping Charge
20000 1Z72345678, 1Z1234567 60.00
I would also like to mark the updated field as Y in the UDT
Any help would be appreciated
Hello!
Couple of quick questions which will help determine the exact code and approach.
Are you using HANA or SQL? HANA is a bit easier to do aggregate string concatenation STRING_AGG() although it's definitely something I have done in SQL too.
Secondly, you mention "I have fields for Tracking Number and Shipping Charges". I'm assuming these are UDFs? The system tracking number might be OK to UPDATE directly but I would never update any system shipping charge field through raw UPDATE queries.
If you are using all UDFs on the end documents then it's really pretty easy, you would be able to use the PTN (PostTransactionNotification), just detect object 15 and update the fields directly.
If you wanted to extend this and actually write to system fields or directly write into the document header (or row) freight to automate the process you would need to use the DI API. You would want something like B1UP to do this.
BUT, if you just want to take your UDT info and output it to UDFs on the delivery then it's simple, just let me know if it's HANA or SQL and I'll throw out some steps and examples,
M
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Good morning,
Its HANA and the fields are UDF on the marketing documents at the header level.
So I just want to take the data in the UDT and put it in the UDF on the delivery note for any lines where the UPDATED field <> Y
One other thing, I need to update the UPDATED field on the UDT to be Y once its updated.
They can have partial shipments day apart so I need to get just the shipping data that has not been attached to a delivery.
Thanks
Brian
OK this is good, is there any situation where the delivery is made and then days later more rows are added to your UDT?
IE, will I have to go back to re-aggregate the data if a new UDT row is added? If not, then I simply update the UDFs when the delivery is added, mark the UDT rows "Y" and then for that series of order --> delivery it's done?
The way it would work is
Day 1: Sales Order is partially shipped, so tracking info (number and cost) will be in the UDT, then the delivery gets created and this data will go there. The records in the UDT will be marked as Closed
Day 3: The balance of the order is shipped and new tracking info (number and cost will be un the UDT, then the delivery gets created and the new data will go there, only taking into account records that are not closed.
Does this make sense>
User | Count |
---|---|
93 | |
8 | |
6 | |
5 | |
5 | |
4 | |
2 | |
2 | |
2 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.