Force close of Purchase Order via Custom SQL Script

Hi @AndrewA 

Did you verify the complete order action in PO 

Hi @AndrewA,

I would recommend checking your PO Accruals GL Account, you might be in for a surprise, we have had the same issue.

If you just want the status to be closed, there is a tickbox in the POLine which is called closed, you can tick this and it will close the line and the PO.

Hi @Manikanta Dhulipudi and @aiwan ,

Thanks for your responses. The Complete Order action doesn't seem to do anything at all for this order, the order just stays as it is. I imagine it is because all lines are already closed, as shown below, so there is nothing else to complete.

Would an SQL query work in this case? If so, how should the status field be updated to ensure this order is set to closed in the database?

Kind regards,

Andrew

Hi @AndrewA 

The PO should, and that’s the key word ‘Should’, close itself when all line are satisfied.

I.E., the PO has been completely receipted and billed.

An issue we have encountered is that the receipts and the bills weren’t a carbon copy of each other as in they contained the same inventoryID, qty and cost/price. However, the bill had 1 line and the receipt had 3 lines therefore the PO would sit at open because the system thinks you have billed for one line at an excessive quantity rather than 3 lines combined.

Could you please examine the billed vs. the receipted qty or share a screenshot with us please?

Hi @aiwan , thanks for that suggestion. I have examined the billed vs receipted qty and everything looks good to me. The quantities match up and all the lines are the same in each, so it is unclear why the system is refusing to close this order.

So, as a last resort, do you think an SQL query would work to force this PO to close?

Hi @AndrewA 

Apologies for the back and forth, before we start considering that, could you please examine the ‘Purchase Accrual Balance by Period’ Custom Inquiry for that PO, if anything isn’t closed out it will show there.

If that is all closed out in that inquiry, then I think considering closing it with a script might be an option.

Hi @aiwan , thanks for your assistance on this issue. The supplier doesn’t have a PO Accrual account set up, as shown below, and since the Account field is mandatory for this inquiry, I am not sure which account I should use instead. Do you have any ideas of how this could work?

If I am unable to run this inquiry as a result of how this supplier is set up, I might just try the SQL script and see how that goes.

Kind regards,

Andrew

Hi @AndrewA 

No problem at all, that’s where my knowledge ends unfortunately, a script would probably work, however I do not have much experience in the database side.

If the supplier doesn’t have an accruals account allocated then we go back to the PO.

In one of the screenshots provided, the accrual account allocated on the PO was 21210, I would recommend using that one.

Again, I have little experience with the database side of things however, I would try to do as little as possible with using SQL to modify orders. The data displayed can be different to the server and cause data corruption.

Aleks

Thanks for that @aiwan , I will steer clear of the SQL for now as I don’t want to risk any data corruption. I will talk to them about adding an accrual account and see if that helps.

Kind regards,

Andrew

Thanks for those suggestions @aiwan ! I will give them a go and see which one works.

Hi @AndrewA 

Did any of those suggestions work/help?