Deleting Old Invoice Data from the Extract Tables
Billing information moved into the extract tables when invoices are generated accumulates if you do not periodically delete it. To assist you in maintaining the Billing extract tables, PeopleSoft provides an online utility and a batch utility.
Note: After deleting old invoice data from the Billing extract tables, the database or system administrator should update the extract table statistics. This will insure a better performance for the invoice extract process.
Here are all the extract tables: PS_BI_EXTRCT, PS_BI_EXTRCT_LINE, PS_BI_EXT_HDR_NOTE, PS_BI_EXT_LIN_NOTE, PS_BI_EXTRCT_TAX, PS_BI_EXTRCT_VAT, PS_BI_EXT_INST_SEC, PS_BI_EXT_INST_SUM, PS_BI_EXTRCT_PROJ, PS_BI_EXTR_CONTR, PS_BI_EXTRCT_UTL, PS_BI_EXT_SHIP_TO, PS_BI_EXTRCT_TXDTL, PS_BI_EXT_EXSDTL, PS_BI_EXTRCT_PAY, PS_BI_EXT_SUM_GPHR, PS_BI_EXT_SUM_IVC, PS_BI_EXT_SUM_NT, PS_BI_EXT_SUM_LNNT, BI_EXT_1034, BI_EXT_BAE_1035, BI_EXT_RAE_1035, BI_EXT_FEE_1035, BI_EXT_GPLN_1035, BI_EXT_LN_1035, BI_EXT_NOTE_1035, BI_EXT_NT_BAE, BI_EXT_NT_RAE, BI_EXT_NT_FEE, BI_EXT_NT_GRP, BI_EXT_NT_LN, BI_EXT_BILL_TO, BI_EXT_EXSGST
The invoice extract process generates run control records for the invoice print SQR processes. Once the old invoice data is deleted from the extract tables, the run control records that are associated with the deleted extract data becomes obsolete. Therefore, it also makes sense to update the run control table statistics.
Here are all the invoice run print process run control tables for all invoice print SQR processes: PS_RUN_BI_PRNCRX1, PS_RUN_BI_PRNCRX5, PS_RUN_BI_PRNCRX5A, PS_RUN_BI_PRNCRX6, PS_RUN_BI_PRNCRX6A, PS_RUN_BI_PRNCRX7, PS_RUN_BI_PRNCRX7A, PS_RUN_BI_PRNCRX8, PS_RUN_BI_PRNCRX8A, PS_RUN_BI_PRNCRX9, PS_RUN_BI_PRNCRX9A, PS_RUN_BI_PRNCRX1A, PS_RUN_BI_PRNSQR1, PS_RUN_BI_PRNSQR1A, PS_RUN_BI_PRNSQR2, PS_RUN_BI_PRNSQR2S, PS_RUN_BI_PRNSQR3, PS_RUN_BI_PRNSQR3A, PS_RUN_BI_PRNSQR4, PS_RUN_BI_PRNSQR4S, PS_RUN_BI_PRNSQR5, PS_RUN_BI_PRNSQR6, PS_RUN_BI_PRNCRX1S, PS_RUN_BI_PRNCRX2, PS_RUN_BI_PRNCRX3, PS_RUN_BI_PRNCRX3A, PS_RUN_BI_PRNCRX4, PS_RUN_BI_PRNCRX4A, PS_RUN_BI_PRNCRX4S, PS_BI_PRNXP1, PS_BI_PRNXP2, PS_BI_PRNXP3, PS_BI_PRNXP4, PS_BI_PRNXP5, PS_BI_PRNXP6, PS_BI_PRNXP7, PS_BI_PRNXP8, PS_BI_PRNXP9, PS_BI_PRNXP10, PS_BI_PRNXP11, PS_BI_PRNXP12, PS_BI_PRNXP13.
On the Delete Extract Bills page, define the range selection and select the bills for deletion. Click the Delete button to delete the data from all extract-related tables.
The Workflow Batch Parameters page enables you to designate the number of days that extracted PeopleSoft Billing information can collect in the Billing Extract table before deletion. Schedule and run this process to look for and delete extracted information on a regular basis.
To define the number of days that extracted information can remain before deletion:
Access the Define Workflow page.
Indicate the number of days after which you want to delete PeopleSoft Billing information from the extract tables in the Delete Extract Bills, Number of Days Old field.
On the Delete Bills run control page, select Delete Extract Bills. Run the BIDELDATA process to delete from all extract-related tables and its associated run control tables. This process will delete any row older than the number of days specified Workflow Batch Parameters page, from those tables.