EAP: Clear out the temp tables at the beginning of 1099 Report (AP_APY1099) (Doc ID 2240887.1)

Last updated on MARCH 06, 2017

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

The 1099 Report (AP_APY1099) needs to be coded to delete any data from the temp tables when the process is initially executed.  The process will delete the data from the temp tables as long as the process completes to success.  However, if AP_APY1099 abends and the process is not restarted, it's possible that old data can remain in the temp tables.  As a result, when AP_APY1099 is run in the future, the process can abend due to old data.

For example, after applying the 1099 Update for 2016, ran the 1099 processes for the 2015 year instead of the 2016 year.  When 1099 Report ran, process abended at Step AP_APY1099.BD100.Step32 with the an error.

ERROR
-- 09:23:52.950 ....(AP_APY1099.BD100.Step32) (SQL)
UPDATE PS_WTHD_1099_3TMP SET COUNTRY = ( SELECT ADDR.COUNTRY FROM
PS_VENDOR_ADDR ADDR WHERE ADDR.SETID = PS_WTHD_1099_3TMP.VENDOR_SETID AND
ADDR.VENDOR_ID = PS_WTHD_1099_3TMP.VENDOR_ID AND ADDR.ADDRESS_SEQ_NUM =
PS_WTHD_1099_3TMP.ADDRESS_SEQ_NUM AND ADDR.EFFDT = ( SELECT MAX(ADDR1.EFFDT)
FROM PS_VENDOR_ADDR ADDR1 WHERE ADDR.SETID = ADDR1.SETID AND ADDR.VENDOR_ID =
ADDR1.VENDOR_ID AND ADDR.ADDRESS_SEQ_NUM = ADDR1.ADDRESS_SEQ_NUM AND
ADDR1.EFFDT <= TO_DATE('2015-06-30','YYYY-MM-DD'))), ADDRESS1 = ( SELECT
UPPER(ADDR.ADDRESS1) FROM PS_VENDOR_ADDR ADDR WHERE ADDR.SETID =
PS_WTHD_1099_3TMP.VENDOR_SETID AND ADDR.VENDOR_ID =
PS_WTHD_1099_3TMP.VENDOR_ID AND ADDR.ADDRESS_SEQ_NUM =
.
.
.
-- Row(s) affected: 0
-- 09:23:53.128 Process 665380 ABENDED at Step AP_APY1099.BD100.Step32 (Action SQL) -- RC = 1407
ROLLBACK
/
-- 09:23:53.145 SQL Error: ORA-01407: cannot update ("SYSADM"."PS_WTHD_1099_3TMP"."COUNTRY") to NULL

Process abended due to old data residing in the temp table, WTHD_1099_3TMP.  Once the old data was deleted, and the process restarted, the process ran successfully to completion.  

It appears that the 1099 Report abended in the past, and the issue was eventually resolved.  When the 1099 Report was ran again, it abended with the above error due to the old data.  If the temp tables were cleared out when the 1099 Report ran, this issue would not have occurred.

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms