x Want to participate? Join the group to interact
Options
  • State Verified Answer
  • Replies 5 replies
  • Subscribers 81 subscribers
  • Views 2726 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
47
topics
95
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
965
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
Kevin M

Script to update SO Header, as an invoice is being posted

SOLVED
Posted By

I'm trying to write a script to track (in a UDF) how much freight has been charged already for an SO. 

When I try to trigger the update on the SO Header table (filtered for oSession.Updating = 1), using the LastInvoiceOrderNo, it doesn't work because the SO_InvoiceHistoryLink table entry hasn't been written yet (for querying the invoice HeaderSeqNo) when the script is triggered.

I then thought to set up a trigger on AR Invoice History Header, but those scripts are linked to AR_InvoiceHistoryInquiry_bus, which (apparently?) doesn't execute during a sales journal update.

AR_TransactionPosting is unavailable for script triggers.

I'd prefer to use proper primary key values when opening other tables in a script, but aside from the ideas above the only method I can think of is a partial key SetBrowseFilter lookup using the KSALESORDER index in invoice history (without the HeaderSeqNo).  I'm thinking this "should" always provide the correct invoice record, but before I go rewriting my script for that I'm wondering if there's a better way.

Any ideas?

  • 0
    Let me repeat back what I think you are saying.

    When you are updating a sales INVOICE, you want to reference back to the sales order it was generated from and update a UDF (in the sales order header) showing how much freight has been charged relative to the original sales order.

    Your problem is that you need to know which sales order to update and you are trying to get that from SO_InvoiceHistoryLink Correct?

    The SO_InvoiceHeader (data entry file for invoices) includes the sales order number. Can't you use that to reference back?
  • 0
    I think your issue is that as the invoice is being posted the sales order is being purged from the open sales order file.
  • 0 in reply to TomTarget

    "When you are updating a sales INVOICE, you want to reference back to the sales order it was generated from and update a UDF (in the sales order header) showing how much freight has been charged relative to the original sales order."

    Correct.

    If I trigger on the SO header table, the current business object is the correct SO but I don't know the freight charged against the invoice being posted.  For that I need the invoice number and header sequence number to open up invoice history and get the FreightAmt.  However I can't get the HeaderSeqNo from the linking table, because it isn't written to that table yet (when the script executes).

    Since I only want this updated when the invoice is actually posted, I can't use the SO invoice data entry tables... unless a pre-delete script would work.  Hmm.  That's an idea to try.

  • 0 in reply to BigLouie

    "I think your issue is that as the invoice is being posted the sales order is being purged from the open sales order file."

    Nope.  I'm excluding that specifically in the script, because it only matters for Sales Orders that are still open after the invoice is posted.  The end goal is to prevent duplicate charges of freight amounts entered in the SO, when there are multiple invoices posted against it.  I'm also tracking the invoiced freight in a Sales Order Header UDF, which is what I need working first.

  • 0 in reply to Kevin M
    verified answer
    It turns out I was over thinking things. Apparently the invoice data is still in the SO Invoice tables when the Sales Order is updated. Going to AR History is completely unnecessary.
    I added this restriction to only run the calculations when appropriate.
    oSession.StartProgram = "SO_SALESJOURNAL_UI"

*Community Hub is the new name for Sage City