Update UDF for Delivery Note

[bbranco]

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
 

[BattleshipCobra · Accepted solution]

OK, this should do it.  It's relatively complex so hopefully you know how to modify StorProcs to use the PTN system.  I have a video here (PTN is later on but it's worth watching the whole thing HANA is not identical but similar): https://youtu.be/V4P_RCWOuk8

Create UDT

Table Name: FREIGHT

Description: Freight Tracking

Object Type: No Object with Auto-Increment

Add UDFs to UDT Freight

1. SalesOrder - Sales Order - Numeric ** Same as ORDR.DocNum

2. TrackNo - Tracking Number - Alpha(30) **Same as ORDR.TrackNo

3. ShipCharge - Shipping Charges - Amount - Default 0 - Mandatory

4. Updated - Updated - Alpha(1) - Valid Values (Y-Yes/N-No) - Default N - Mandatory

Add UDFs to Marketing Document Header

1. SalesOrder - Sales Order - Numeric ** Same as ORDR.DocNum

This will be used to make it easier to associate the delivery directly with the originating order

2. TrackNo - Tracking Number - Alpha(254)

Will be used on Delivery documents to output tracking numbers

3. ShipCharge - Shipping Charges - Amount - Default 0 - Mandatory

Will be used on Delivery documents to output shipping charges

PTN to Auto Update Sales Order Header

See text file with code here.  The forum literally would not let me post, said it was unsupported HTML...

PTN to Auto Pull FREIGHT Tracking Data into Deliveries

See text file with code here.  The forum literally would not let me post, said it was unsupported HTML...

You will need both of the PTN statements installed for it to work.  You can update the new U_SalesOrder UDF by using:

See text file with code here.  The forum literally would not let me post, said it was unsupported HTML...

I have it working in my development system.

Warning, there may be bugs but you should be able to finish the system off.

Let me know!

Mike

[bbranco · Accepted solution]

Thanks!!!

[BattleshipCobra]

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

[bbranco]

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

[BattleshipCobra]

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?

[bbranco]

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>

[BattleshipCobra]

This is the easiest way actually. Give me a bit of time, I'll try to write up an example for you to use.