I've finally been able to dedicate some time to this process at my organization and I now have the complete scope of the issue at hand. What's happened is many users at our organization felt the existing Payables Clearing Audit Clearing List report was insufficient and too cluttered to be used on a regular basis because it doesn't clear completed transactions from the report.
Because the report wasn't well received, someone created a macro which produces an Excel report that displays only incomplete POs from POAAPC (this should indicate receipts that have not yet been completed). In doing so there were some short comings with the macro (didn't handle multiple receipts/invoices/credit notes/debit notes) and also it turns out some users weren't keeping up with the report and the process so at quarter or year end there would be hundreds or thousands of receipts that weren't invoiced but needed to be removed from the report immediately. To remove these receipts from the report the user would produce a list of POs that were never going to be invoiced and a script was created to update ISCOMPLETE = 1 and/or COMPLETION = 3 in the relevant tables (PORCPH1/PORCPL, POINVH1/POINVL, etc).
Recently I looked at the process and discovered the macro was required because all the necessary data could be gathered by joining POAAPC to the audit lines table (PORCPAL), then the audit header table (PORCPAH), detail lines (PORCPL) and the header table (PORCPH). Please note I'm only showing receipts as my example but this is applicable for all document types. Once I wrote the SQL statement I started trying to roll this report out and in branches where the existing macro was not used or where emergency receipt removal through scripting had not been completed everything works fine, but in cases where the macro was/is in use the report doesn't work.
What I'm seeing is "orphaned" documents in the system. For example, when the scripting was completed sometimes incorrect documents were "matched" so there might be a receipt that was updated to completed, but it had a return that is outstanding in the system and isn't being applied. That's a simple scenario, but there are some records where the header is set to ISCOMPLETE = 0 but the detail is set to COMPLETION = 3 because an error was found in the existing macro.
Luckily all the updates were tracked in a table that has DAYENDSEQ, HEADERSEQ, LINESEQ and DOCZSEQ so I can isolate the records that were updated by script and reverse the changes if required, but I'm not sure what would happen if I did that. During the next day end process would ISCOMPLETE and COMPLETION be updated correctly or is there another process that would update these? I'm also wondering what a data integrity check would do to these records since I'm more inclined to let the system do this than to continue scripting in the system.
My end goal here is to stop the scripting in the macro, the emergency scripting in the database and provide an accurate report that matches the GL.
Ian
Because the report wasn't well received, someone created a macro which produces an Excel report that displays only incomplete POs from POAAPC (this should indicate receipts that have not yet been completed). In doing so there were some short comings with the macro (didn't handle multiple receipts/invoices/credit notes/debit notes) and also it turns out some users weren't keeping up with the report and the process so at quarter or year end there would be hundreds or thousands of receipts that weren't invoiced but needed to be removed from the report immediately. To remove these receipts from the report the user would produce a list of POs that were never going to be invoiced and a script was created to update ISCOMPLETE = 1 and/or COMPLETION = 3 in the relevant tables (PORCPH1/PORCPL, POINVH1/POINVL, etc).
Recently I looked at the process and discovered the macro was required because all the necessary data could be gathered by joining POAAPC to the audit lines table (PORCPAL), then the audit header table (PORCPAH), detail lines (PORCPL) and the header table (PORCPH). Please note I'm only showing receipts as my example but this is applicable for all document types. Once I wrote the SQL statement I started trying to roll this report out and in branches where the existing macro was not used or where emergency receipt removal through scripting had not been completed everything works fine, but in cases where the macro was/is in use the report doesn't work.
What I'm seeing is "orphaned" documents in the system. For example, when the scripting was completed sometimes incorrect documents were "matched" so there might be a receipt that was updated to completed, but it had a return that is outstanding in the system and isn't being applied. That's a simple scenario, but there are some records where the header is set to ISCOMPLETE = 0 but the detail is set to COMPLETION = 3 because an error was found in the existing macro.
Luckily all the updates were tracked in a table that has DAYENDSEQ, HEADERSEQ, LINESEQ and DOCZSEQ so I can isolate the records that were updated by script and reverse the changes if required, but I'm not sure what would happen if I did that. During the next day end process would ISCOMPLETE and COMPLETION be updated correctly or is there another process that would update these? I'm also wondering what a data integrity check would do to these records since I'm more inclined to let the system do this than to continue scripting in the system.
My end goal here is to stop the scripting in the macro, the emergency scripting in the database and provide an accurate report that matches the GL.
Ian