Sage 100

We have a question from a customer, and I am wondering if anyone has done this via script before. 
I'm looking for a way to delete an SO line, and set a specific reason code in the deleted line.  Is this possible (by script)?
The scripting guide for oLines.Delete() and oLinesDeleteLine() don't say anything about the reason codes, and SO_SalesOrderDetail doesn't have the field so it isn't as though I can SetValue before deleting...

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

I think DeleteLine() doesn't do that unless in S/O Options you have it set for Prompt which would mean a UI would need to appear to have you choose the Cancel / Reason code which defeats the purpose of scripting it. Hmm I don't think there's a conventional way to pass the code through the business objects. I'll think about it.

Yeah... the UI pop-ups are not what we want to happen... the idea is to do a bulk update (if a seasonal item is out of stock) from an Item Maintenance button script.
Plan "B" is to have a UDF for the reason (set / saved, before the line delete) but that is not as clean as using the reason code feature... if that is possible.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-19-2020 20:57
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

I think DeleteLine() doesn't do that unless in S/O Options you have it set for Prompt which would mean a UI would need to appear to have you choose the Cancel / Reason code which defeats the purpose of scripting it. Hmm I don't think there's a conventional way to pass the code through the business objects. I'll think about it.

(Thanks to Moira for moving this post to the correct community)!

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-20-2020 09:26
From: Kevin Moyes
Subject: Script command to delete SO line, and have a line reason code in SO history

Yeah... the UI pop-ups are not what we want to happen... the idea is to do a bulk update (if a seasonal item is out of stock) from an Item Maintenance button script.

Plan "B" is to have a UDF for the reason (set / saved, before the line delete) but that is not as clean as using the reason code feature... if that is possible.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 03-19-2020 20:57
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

I think DeleteLine() doesn't do that unless in S/O Options you have it set for Prompt which would mean a UI would need to appear to have you choose the Cancel / Reason code which defeats the purpose of scripting it. Hmm I don't think there's a conventional way to pass the code through the business objects. I'll think about it.

Oh so you are deleting the SO line but instead of from S/O Entry, it's from Item Maintenance button script where you are traversing thru the SOs to find same ItemCode and delete it from the order, and then set the Cancel / Reason code on the deleted order hist dtl line, right? In that case, I think so.

Prior to issuing DeleteLine, do a GetValue on SOHistoryDetlSeqNo field. This and the SalesOrderNo gives you the primary key for GetObject to SO_SalesOrderHistoryInquiryDetail_bus where you can SetKey to it then SetValue on CancelReasonCode, then Write() and maybe a Clear() too.

Bear with me:

1. Suggest you move the GetObject above into a separate SO Detail Pre-Delete script which runs only if StartProgram = "IM_ITEM_UI". Definitely end that script with a Clear() to avoid contention with the pending execute of DeleteLine() 

2. The history detail objects are usually a little weird so if SetKey doesn't work you would have to use a SetFilter technique. If you search on SetFilter on SageCity you'll see my post on traversing thru PO_ReceiptHistoryDetail you can adapt.

------------------------------
Alnoor Cassim

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA
------------------------------

Original Message:
Sent: 03-20-2020 09:26
From: Kevin Moyes
Subject: Script command to delete SO line, and have a line reason code in SO history

Yeah... the UI pop-ups are not what we want to happen... the idea is to do a bulk update (if a seasonal item is out of stock) from an Item Maintenance button script.

Plan "B" is to have a UDF for the reason (set / saved, before the line delete) but that is not as clean as using the reason code feature... if that is possible.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 03-19-2020 20:57
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

I think DeleteLine() doesn't do that unless in S/O Options you have it set for Prompt which would mean a UI would need to appear to have you choose the Cancel / Reason code which defeats the purpose of scripting it. Hmm I don't think there's a conventional way to pass the code through the business objects. I'll think about it.

The original thought I was going with was to run an ODBC query in the button script to quickly find all the lines containing the item.  In that query I can grab whatever columns I need.
Then I was going to open each SO, delete the appropriate line(s), save the SO, move to the next... not sure what will happen if I end up deleting the last line on an SO (something to test).
Now it sounds like I'll need another object to update the SO history lines with the reason code (as I am going through deleting the SO lines, as step 2).  Not too bad at all, and nicer for the user (vs a UDF, if I can get it to work). 
Thanks for the ideas Alnoor!

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-20-2020 17:11
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

Oh so you are deleting the SO line but instead of from S/O Entry, it's from Item Maintenance button script where you are traversing thru the SOs to find same ItemCode and delete it from the order, and then set the Cancel / Reason code on the deleted order hist dtl line, right? In that case, I think so.

Prior to issuing DeleteLine, do a GetValue on SOHistoryDetlSeqNo field. This and the SalesOrderNo gives you the primary key for GetObject to SO_SalesOrderHistoryInquiryDetail_bus where you can SetKey to it then SetValue on CancelReasonCode, then Write() and maybe a Clear() too.

Bear with me:

1. Suggest you move the GetObject above into a separate SO Detail Pre-Delete script which runs only if StartProgram = "IM_ITEM_UI". Definitely end that script with a Clear() to avoid contention with the pending execute of DeleteLine()

2. The history detail objects are usually a little weird so if SetKey doesn't work you would have to use a SetFilter technique. If you search on SetFilter on SageCity you'll see my post on traversing thru PO_ReceiptHistoryDetail you can adapt.

------------------------------
Alnoor Cassim

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA
------------------------------


Original Message:
Sent: 03-20-2020 09:26
From: Kevin Moyes
Subject: Script command to delete SO line, and have a line reason code in SO history

Yeah... the UI pop-ups are not what we want to happen... the idea is to do a bulk update (if a seasonal item is out of stock) from an Item Maintenance button script.

Plan "B" is to have a UDF for the reason (set / saved, before the line delete) but that is not as clean as using the reason code feature... if that is possible.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-19-2020 20:57
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

I think DeleteLine() doesn't do that unless in S/O Options you have it set for Prompt which would mean a UI would need to appear to have you choose the Cancel / Reason code which defeats the purpose of scripting it. Hmm I don't think there's a conventional way to pass the code through the business objects. I'll think about it.

See if the pre-write event fires on SO_SalesOrderHistoryDetail. If it does, maybe you can make this work by creating a UDF in SO_SalesOrderDetail which will automatically be mirrored into SO_SalesOrderHistoryDetail. In your delete script, set the value in the UDF and make sure it is committed before deleting it, then during the pre-write on SO_SalesOrderHistoryDetail, check the UDF and if not blank, set its value in the cancel reason code.

Another approach might be to trying setting the cancel reason code into a storage variable using oSession's ScriptObject's SetStorageVar. I'm not positive that the pre-write event script will be sharing the same oSession object though, worth a shot since you would be avoiding the UDF, write, delete approach in the first approach.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 03-20-2020 17:23
From: Kevin Moyes
Subject: Script command to delete SO line, and have a line reason code in SO history

The original thought I was going with was to run an ODBC query in the button script to quickly find all the lines containing the item.  In that query I can grab whatever columns I need.
Then I was going to open each SO, delete the appropriate line(s), save the SO, move to the next... not sure what will happen if I end up deleting the last line on an SO (something to test).
Now it sounds like I'll need another object to update the SO history lines with the reason code (as I am going through deleting the SO lines, as step 2).  Not too bad at all, and nicer for the user (vs a UDF, if I can get it to work).
Thanks for the ideas Alnoor!

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 03-20-2020 17:11
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

Oh so you are deleting the SO line but instead of from S/O Entry, it's from Item Maintenance button script where you are traversing thru the SOs to find same ItemCode and delete it from the order, and then set the Cancel / Reason code on the deleted order hist dtl line, right? In that case, I think so.

Prior to issuing DeleteLine, do a GetValue on SOHistoryDetlSeqNo field. This and the SalesOrderNo gives you the primary key for GetObject to SO_SalesOrderHistoryInquiryDetail_bus where you can SetKey to it then SetValue on CancelReasonCode, then Write() and maybe a Clear() too.

Bear with me:

1. Suggest you move the GetObject above into a separate SO Detail Pre-Delete script which runs only if StartProgram = "IM_ITEM_UI". Definitely end that script with a Clear() to avoid contention with the pending execute of DeleteLine()

2. The history detail objects are usually a little weird so if SetKey doesn't work you would have to use a SetFilter technique. If you search on SetFilter on SageCity you'll see my post on traversing thru PO_ReceiptHistoryDetail you can adapt.

------------------------------
Alnoor Cassim

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA


Original Message:
Sent: 03-20-2020 09:26
From: Kevin Moyes
Subject: Script command to delete SO line, and have a line reason code in SO history

Yeah... the UI pop-ups are not what we want to happen... the idea is to do a bulk update (if a seasonal item is out of stock) from an Item Maintenance button script.

Plan "B" is to have a UDF for the reason (set / saved, before the line delete) but that is not as clean as using the reason code feature... if that is possible.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-19-2020 20:57
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

I think DeleteLine() doesn't do that unless in S/O Options you have it set for Prompt which would mean a UI would need to appear to have you choose the Cancel / Reason code which defeats the purpose of scripting it. Hmm I don't think there's a conventional way to pass the code through the business objects. I'll think about it.

Pre-write on SO_SalesOrderHistoryDetail does not seem to trigger... I'm going to try a new object within the same script (to update SO history with the reason code).
Thanks for the ideas David.  I will post an update after I've tried a few things.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-24-2020 12:00
From: David Speck II
Subject: Script command to delete SO line, and have a line reason code in SO history

See if the pre-write event fires on SO_SalesOrderHistoryDetail. If it does, maybe you can make this work by creating a UDF in SO_SalesOrderDetail which will automatically be mirrored into SO_SalesOrderHistoryDetail. In your delete script, set the value in the UDF and make sure it is committed before deleting it, then during the pre-write on SO_SalesOrderHistoryDetail, check the UDF and if not blank, set its value in the cancel reason code.

Another approach might be to trying setting the cancel reason code into a storage variable using oSession's ScriptObject's SetStorageVar. I'm not positive that the pre-write event script will be sharing the same oSession object though, worth a shot since you would be avoiding the UDF, write, delete approach in the first approach.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 03-20-2020 17:23
From: Kevin Moyes
Subject: Script command to delete SO line, and have a line reason code in SO history

The original thought I was going with was to run an ODBC query in the button script to quickly find all the lines containing the item.  In that query I can grab whatever columns I need.
Then I was going to open each SO, delete the appropriate line(s), save the SO, move to the next... not sure what will happen if I end up deleting the last line on an SO (something to test).
Now it sounds like I'll need another object to update the SO history lines with the reason code (as I am going through deleting the SO lines, as step 2).  Not too bad at all, and nicer for the user (vs a UDF, if I can get it to work).
Thanks for the ideas Alnoor!

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-20-2020 17:11
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

Oh so you are deleting the SO line but instead of from S/O Entry, it's from Item Maintenance button script where you are traversing thru the SOs to find same ItemCode and delete it from the order, and then set the Cancel / Reason code on the deleted order hist dtl line, right? In that case, I think so.

Prior to issuing DeleteLine, do a GetValue on SOHistoryDetlSeqNo field. This and the SalesOrderNo gives you the primary key for GetObject to SO_SalesOrderHistoryInquiryDetail_bus where you can SetKey to it then SetValue on CancelReasonCode, then Write() and maybe a Clear() too.

Bear with me:

1. Suggest you move the GetObject above into a separate SO Detail Pre-Delete script which runs only if StartProgram = "IM_ITEM_UI". Definitely end that script with a Clear() to avoid contention with the pending execute of DeleteLine()

2. The history detail objects are usually a little weird so if SetKey doesn't work you would have to use a SetFilter technique. If you search on SetFilter on SageCity you'll see my post on traversing thru PO_ReceiptHistoryDetail you can adapt.

------------------------------
Alnoor Cassim

Accounting Systems, Inc. (ASI)
Email: alnoor@asifocus.com
Orange County, CA


Original Message:
Sent: 03-20-2020 09:26
From: Kevin Moyes
Subject: Script command to delete SO line, and have a line reason code in SO history

Yeah... the UI pop-ups are not what we want to happen... the idea is to do a bulk update (if a seasonal item is out of stock) from an Item Maintenance button script.

Plan "B" is to have a UDF for the reason (set / saved, before the line delete) but that is not as clean as using the reason code feature... if that is possible.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 03-19-2020 20:57
From: Alnoor Cassim
Subject: Script command to delete SO line, and have a line reason code in SO history

I think DeleteLine() doesn't do that unless in S/O Options you have it set for Prompt which would mean a UI would need to appear to have you choose the Cancel / Reason code which defeats the purpose of scripting it. Hmm I don't think there's a conventional way to pass the code through the business objects. I'll think about it.

------Original Message------

Pre-write on SO_SalesOrderHistoryDetail does not seem to trigger... I'm going to try a new object within the same script (to update SO history with the reason code).
Thanks for the ideas David.  I will post an update after I've tried a few things.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

If i were you, i would handle this all in a single button script in item maintenance, i don't really see a need for a script on either of the delete events on SO_SalesOrderDetail.

I think you should start by getting object handles to the following objects.

1. SO_SalesOrderDetail_Bus (this will be used to locate the current item code on open sales orders or you can use ODBC)
2. SO_SalesOrder_Bus (this will be used to handle the actual deletion of the lines on open sales orders)
3. SO_SalesOrderHistoryInquiryDetail_Bus (this will be used to update the CancelReasonCode for the deleted lines)

You should be able to first get an object handle to SO_SalesOrderDetail_Bus and set the browse filter index to either "kItemType" or "kItemWhse" then set the browse filter to the current item code + chr(0), alternatively, you could use ODBC to return the lines with the current item code. This will allow you to easily use MoveFirst, MoveNext and EoF to loop through the lines for the current item code. Make sure to Clear after getting the key values you need so you don't lock the record when deleting the line using the SO_SalesOrder_Bus object.

Then get a handle to SO_SalesOrder_Bus and proceed through each sales order and as Alnoor said, prior to deleting the line, get the value of SalesOrderNo and SOHistoryDetlSeqNo, proceed with deleting the line and writing the header, then use SalesOrderNo and SOHistoryDetlSeqNoin in the SetKey of the object handle for SO_SalesOrderHistoryInquiryDetail_Bus and set the value for the CancelReasonCode and write the line.

As long you aren't changing quantities or amounts, you shouldn't have to worry about totals on the header getting out of sync since you are letting the header handle those updates when deleting the line. 

Don't forget to include some Clear methods where needed.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 03-25-2020 11:46
From: David Speck II
Subject: Script command to delete SO line, and have a line reason code in SO history

That's too bad, think it would have been the cleanest way to make it happen too.

 

 

 

Thanks

David Speck

TSS

Sage | Sage 100 Certified Consultant

 

Original Message------

Pre-write on SO_SalesOrderHistoryDetail does not seem to trigger... I'm going to try a new object within the same script (to update SO history with the reason code).
Thanks for the ideas David.  I will post an update after I've tried a few things.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

(I received an error when I tried to open SO_SalesOrderDetail_bus, so I went with ODBC instead of trying to figure that out...).

OK, here is what I ended up doing:

• ODBC query to come up with a list of lines to delete.
  • Since this is an Advanced customer, this was tricky because SOTAMAS90 was not available for a server side button script... but named system DSN's did it.
  • SELECT SalesOrderNo, LineKey, SOHistoryDetlSeqNo FROM SO_SalesOrderDetail WHERE ItemCode=...
  • Query results stored in an array (for looping through to process the deletions).
• Open an SO object to delete each line.
  • Special case when there was only one line on the SO... delete the SO instead.
• Open an object to set the reason code in SO history: SO_SalesOrderHistoryInquiryDetail_bus

Pop-up to confirm results to the user, with a copy of that written to the Activity Log.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-25-2020 12:35
From: David Speck II
Subject: Script command to delete SO line, and have a line reason code in SO history

If i were you, i would handle this all in a single button script in item maintenance, i don't really see a need for a script on either of the delete events on SO_SalesOrderDetail.

I think you should start by getting object handles to the following objects.

1. SO_SalesOrderDetail_Bus (this will be used to locate the current item code on open sales orders or you can use ODBC)
2. SO_SalesOrder_Bus (this will be used to handle the actual deletion of the lines on open sales orders)
3. SO_SalesOrderHistoryInquiryDetail_Bus (this will be used to update the CancelReasonCode for the deleted lines)

You should be able to first get an object handle to SO_SalesOrderDetail_Bus and set the browse filter index to either "kItemType" or "kItemWhse" then set the browse filter to the current item code + chr(0), alternatively, you could use ODBC to return the lines with the current item code. This will allow you to easily use MoveFirst, MoveNext and EoF to loop through the lines for the current item code. Make sure to Clear after getting the key values you need so you don't lock the record when deleting the line using the SO_SalesOrder_Bus object.

Then get a handle to SO_SalesOrder_Bus and proceed through each sales order and as Alnoor said, prior to deleting the line, get the value of SalesOrderNo and SOHistoryDetlSeqNo, proceed with deleting the line and writing the header, then use SalesOrderNo and SOHistoryDetlSeqNoin in the SetKey of the object handle for SO_SalesOrderHistoryInquiryDetail_Bus and set the value for the CancelReasonCode and write the line.

As long you aren't changing quantities or amounts, you shouldn't have to worry about totals on the header getting out of sync since you are letting the header handle those updates when deleting the line.

Don't forget to include some Clear methods where needed.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 03-25-2020 11:46
From: David Speck II
Subject: Script command to delete SO line, and have a line reason code in SO history

That's too bad, think it would have been the cleanest way to make it happen too.

 

 

 

Thanks

David Speck

TSS

Sage | Sage 100 Certified Consultant

 

Original Message------

Pre-write on SO_SalesOrderHistoryDetail does not seem to trigger... I'm going to try a new object within the same script (to update SO history with the reason code).
Thanks for the ideas David.  I will post an update after I've tried a few things.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------