This toolkit is designed to correct the situation where a unique constraint SQL error occurred when posting amortizations due to duplicate keys inserted into the GL_POST_SUM table.
When an invoice is created in the Create Invoices or Maintain Invoices (Materials » Sales Order Entry » Invoices) applications for a SO line with an Amortization Code associated with it, the system automatically creates an amortization schedule based on the code.
When the PD_SEQ_KEY is copied from AMORT_SCH_PD into SO_INVC_LN_AMORT, however, if rows are inserted (rather than added) to the AMORT_SCH_PD (when creating the code), it is possible to have a situation where the PD_SEQ_KEY is decreasing even though PD_SEQ_NO is actually increasing. When posting amortizations, the PD_SEQ_KEY is copied to the LVL1_KEY column for rows that are inserted into Z_OEPPOST_GL_DETL. When the system inserts rows into GL_POST_SUM, the MAX(Z.LVL1_KEY) is based on the maximum value of LVL1_KEY from Z_OEPPOST_GL_DETL for rows with the same combination of ACCT_ID, ORG_ID, PROJ_ID, FY_CD, PD_NO. For a scenario where the subsequent period has a lower PD_SEQ_KEY, the system does not use the lower key, but uses the previous higher one (same as that of previous period insert), thus resulting in a duplicate.
This is a one-time application to correct existing data. New data does not have this problem since the Create Invoices and Maintain Invoices (Materials » Sales Order Entry » Invoices) applications assign a new set of serialized keys into SO_INVC_LN_AMORT for the amortization schedules.
Click the Select button to access this application.
Click the Execute button to run this application.
Execution of this toolkit does the following:
A temporary worktable (copy) of the existing SO_INVC_LN_AMORT table is generated.
The application checks for the highest value of the PD_SEQ_KEY from all the rows in this table.
New serialized values are reassigned to the PD_SEQ_KEY column for all the unposted rows (indicated by POST_SEQ_NO = Null) in this worktable. The starting PD_SEQ_KEY in the SEQ_GENERATOR table is set equal to the max SO_LN_INVC_AMORT.PD_SEQ_KEY + 1 for posted rows. To conserve the number of keys assigned from the SEQ_GENERATOR table, this process can assign the same PD_SEQ_KEY across multiple lines of the same invoice for a given fiscal year/period.
All unposted rows in the existing SO_INVC_LN_AMORT table are deleted.
All the corresponding unposted rows copy back to SO_INVC_LN_AMORT from the updated worktable.