I decided to go ahead and test out the Workflow_Status field in the PM20000 table. I updated the Workflow_Status field to 6 for every Open payable invoice that hasn't been paid yet. Then we went ahead and altered our Integration routine to set the Workflow_Status field to 6 when we take an invoice off Hold. That routine ran at 4:00 PM Friday. Here's the UPDATE query that runs in the integration routine:
UPDATE dbo.PM20000
SET Hold = 0
, Workflow_Status = 6
WHERE VCHRNMBR = @vchNo
AND Workflow_Status != 6
AND Hold = 1
Afterwards, when I looked at the list of invoices that were not on Hold, I thought I'd check for those that had a Workflow_Status of something other than 6 (Approved). I had just changed them all before 4:00 PM.
Well, the handful of invoices that were approved at 4:00 in our Integration routine were flagged with a 9 (Not activated) in the Workflow_Status column. Crud buckets! That seems to mean that GP overrides the value in that field after we've updated it to 6.
I considered creating a SQL scheduled job that runs 5 minutes after the integration routine runs and look for any invoices that are not on Hold but have a 9 in the Workflow_Status column and change them to 6! Kinda brute force.
One of our other developers suggested a new table that contains the invoice numbers of every invoice that our integration routine takes off Hold. Then if an invoice remains unpaid the next time the integration routine runs (it runs four times during the day) it would check that new table. It will ignore any invoices it finds in that table. That is, if Accounting puts an invoice back on Hold, the integration routine will ignore it because its number has been recorded in the new table. Pretty simple.
What I can't figure out is this: I did a mass update of the Workflow_Status field on Friday afternoon before the 4:00 integration run... and, as far as I can tell, none of those invoices have had their Workflow_Statuses changed. What is it about our integration routine that would allow GP to further affect the values in the Workflow_Status field after we've updated a few invoices to 6?
I might run SQL Profiler at the same time as our integration routine runs to see when the 6 is changed to a 9. That's all I can think of. Anyone else have any ideas?
Thank you,
Steve Erbach