PC_POADJUST ABENDED at Step PC_POADJUST.PR_INS.INSERT (Action SQL) -- RC = 805 (Doc ID 2231692.1)

Last updated on FEBRUARY 10, 2017

Applies to:

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

Symptoms

On : 9.2 version, Other

PC_POADJUST process is failing with error.

ERROR
-----------------------

-- 10:09:20.263 ..(PC_POADJUST.PR_INS.INSERT) (SQL)
INSERT INTO PS_PROJ_RESOURCE (BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID,
RESOURCE_ID, RESOURCE_ID_FROM, BUSINESS_UNIT_GL, JOURNAL_ID, JOURNAL_DATE,
UNPOST_SEQ, JOURNAL_LINE, FISCAL_YEAR, ACCOUNTING_PERIOD, ACCOUNT, ALTACCT,
DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE,
BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1,
CHARTFIELD2, CHARTFIELD3, BUS_UNIT_GL_FROM, CURRENCY_CD, STATISTICS_CODE,
LEDGER_GROUP, ANALYSIS_TYPE, RESOURCE_TYPE, RESOURCE_CATEGORY,
RESOURCE_SUB_CAT, RES_USER1, RES_USER2, RES_USER3, RES_USER4, RES_USER5,
TRANS_DT, ACCOUNTING_DT, OPRID, DTTM_STAMP, JRNL_LN_REF, OPEN_ITEM_STATUS,
LINE_DESCR, JRNL_LINE_STATUS, JOURNAL_LINE_DATE, FOREIGN_CURRENCY, RT_TYPE,
FOREIGN_AMOUNT, RATE_MULT, RATE_DIV, CUR_EFFDT, PROCESS_INSTANCE,
PC_DISTRIB_STATUS, GL_DISTRIB_STATUS, PROJ_TRANS_TYPE, PROJ_TRANS_CODE,
RESOURCE_STATUS, DESCR, SYSTEM_SOURCE, UNIT_OF_MEASURE, EMPLID, EMPL_RCD,
SEQ_NBR, TIME_RPTG_CD, JOBCODE, COMPANY, BUSINESS_UNIT_AP, VENDOR_ID,
VOUCHER_ID, VOUCHER_LINE_NUM, APPL_JRNL_ID, PYMNT_CNT, DST_ACCT_TYPE,
PO_DISTRIB_STATUS, BUSINESS_UNIT_PO, REQ_ID, REQ_LINE_NBR, REQ_SCHED_NBR,
REQ_DISTRIB_NBR, PO_ID, DUE_DATE, LINE_NBR, SCHED_NBR, DISTRIB_LINE_NUM,
AM_DISTRIB_STATUS, BUSINESS_UNIT_AM, ASSET_ID, PROFILE_ID, COST_TYPE, BOOK,
INCENTIVE_ID, MSTONE_SEQ, CONTRACT_NUM, CONTRACT_LINE_NUM, CONTRACT_PPD_SEQ,
BI_DISTRIB_STATUS, BUSINESS_UNIT_BI, BILLING_DATE, INVOICE,
REV_DISTRIB_STATUS, BUSINESS_UNIT_AR, CUST_ID, ITEM, ITEM_LINE, ITEM_SEQ_NUM,
DST_SEQ_NUM, BUSINESS_UNIT_IN, SCHED_LINE_NO, DEMAND_LINE_NO, INV_ITEM_ID,
PAY_END_DT, BUSINESS_UNIT_OM, ORDER_NO, ORDER_INT_LINE_NO, EX_DOC_ID,
EX_DOC_TYPE, RESOURCE_QUANTITY, RESOURCE_AMOUNT, BUDGET_HDR_STATUS,
KK_AMOUNT_TYPE, KK_TRAN_OVER_FLAG, KK_TRAN_OVER_OPRID, KK_TRAN_OVER_DTTM,
BUDGET_OVER_ALLOW, BUDGET_LINE_STATUS, BUDGET_DT, LEDGER, BD_DISTRIB_STATUS,
BUSINESS_UNIT_BD, FA_STATUS, TIME_SHEET_ID, SHEET_ID, DT_TIMESTAMP,
VCHR_DIST_LINE_NUM, PM_REVIEWED, PRICED_RATE, ACTIVITY_ID_DETAIL,
CST_DISTRIB_STATUS, TXN_LMT_TRANS_ID, EVENT_NUM, CA_FEE_STATUS,
BUSINESS_UNIT_WO, WO_ID, WO_TASK_ID, RSRC_TYPE, RES_LN_NBR, COMPRESS_ID,
AMOUNT_IN_EXCESS, RECLAIMED_FROM_OL, FND_DIST_STATUS, SEQ_TRANS_ID,
DIST_TRANS_ID, ADJ_LINE_TYPE, FEEDER_SUM_ID, PRICE_SUM_ID, DEPOSIT_BU,
DEPOSIT_ID, PAYMENT_SEQ_NUM) SELECT BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID,
RESOURCE_ID, RESOURCE_ID_FROM, BUSINESS_UNIT_GL, JOURNAL_ID, JOURNAL_DATE,
UNPOST_SEQ, JOURNAL_LINE, FISCAL_YEAR, ACCOUNTING_PERIOD, ACCOUNT, ALTACCT,
DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE,
BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1,
CHARTFIELD2, CHARTFIELD3, BUS_UNIT_GL_FROM, CURRENCY_CD, STATISTICS_CODE,
LEDGER_GROUP, ANALYSIS_TYPE, RESOURCE_TYPE, RESOURCE_CATEGORY,
RESOURCE_SUB_CAT, RES_USER1, RES_USER2, RES_USER3, RES_USER4, RES_USER5,
TRANS_DT, ACCOUNTING_DT, OPRID, DTTM_STAMP, JRNL_LN_REF, OPEN_ITEM_STATUS,
LINE_DESCR, JRNL_LINE_STATUS, JOURNAL_LINE_DATE, FOREIGN_CURRENCY, RT_TYPE,
FOREIGN_AMOUNT, RATE_MULT, RATE_DIV, CUR_EFFDT, PROCESS_INSTANCE,
PC_DISTRIB_STATUS, GL_DISTRIB_STATUS, PROJ_TRANS_TYPE, PROJ_TRANS_CODE,
RESOURCE_STATUS, DESCR, SYSTEM_SOURCE, UNIT_OF_MEASURE, EMPLID, EMPL_RCD,
SEQ_NBR, TIME_RPTG_CD, JOBCODE, COMPANY, BUSINESS_UNIT_AP, VENDOR_ID,
VOUCHER_ID, VOUCHER_LINE_NUM, APPL_JRNL_ID, PYMNT_CNT, DST_ACCT_TYPE,
PO_DISTRIB_STATUS, BUSINESS_UNIT_PO, REQ_ID, REQ_LINE_NBR, REQ_SCHED_NBR,
REQ_DISTRIB_NBR, PO_ID, DUE_DATE, LINE_NBR, SCHED_NBR, DISTRIB_LINE_NUM,
AM_DISTRIB_STATUS, BUSINESS_UNIT_AM, ASSET_ID, PROFILE_ID, COST_TYPE, BOOK,
INCENTIVE_ID, MSTONE_SEQ, CONTRACT_NUM, CONTRACT_LINE_NUM, CONTRACT_PPD_SEQ,
BI_DISTRIB_STATUS, BUSINESS_UNIT_BI, BILLING_DATE, INVOICE,
REV_DISTRIB_STATUS, BUSINESS_UNIT_AR, CUST_ID, ITEM, ITEM_LINE, ITEM_SEQ_NUM,
DST_SEQ_NUM, BUSINESS_UNIT_IN, SCHED_LINE_NO, DEMAND_LINE_NO, INV_ITEM_ID,
PAY_END_DT, BUSINESS_UNIT_OM, ORDER_NO, ORDER_INT_LINE_NO, EX_DOC_ID,
EX_DOC_TYPE, RESOURCE_QUANTITY, RESOURCE_AMOUNT, BUDGET_HDR_STATUS,
KK_AMOUNT_TYPE, KK_TRAN_OVER_FLAG, KK_TRAN_OVER_OPRID, KK_TRAN_OVER_DTTM,
BUDGET_OVER_ALLOW, BUDGET_LINE_STATUS, BUDGET_DT, LEDGER, BD_DISTRIB_STATUS,
BUSINESS_UNIT_BD, FA_STATUS, TIME_SHEET_ID, SHEET_ID, DT_TIMESTAMP,
VCHR_DIST_LINE_NUM, PM_REVIEWED, PRICED_RATE, ACTIVITY_ID_DETAIL,
CST_DISTRIB_STATUS, TXN_LMT_TRANS_ID, EVENT_NUM, CA_FEE_STATUS,
BUSINESS_UNIT_WO, WO_ID, WO_TASK_ID, RSRC_TYPE, RES_LN_NBR, COMPRESS_ID,
AMOUNT_IN_EXCESS, RECLAIMED_FROM_OL, FND_DIST_STATUS, SEQ_TRANS_ID,
DIST_TRANS_ID, ADJ_LINE_TYPE, FEEDER_SUM_ID, PRICE_SUM_ID, DEPOSIT_BU,
DEPOSIT_ID, PAYMENT_SEQ_NUM FROM PS_PC_RES_PA_TA14 WHERE PROCESS_INSTANCE =
1704114 AND PC_DISTRIB_STATUS <> 'D'
/
-- Row(s) affected: 0
-- 10:09:20.559 Process 1704114 ABENDED at Step PC_POADJUST.PR_INS.INSERT (Action SQL) -- RC = 805
ROLLBACK
/
-- 10:09:20.612 SQL Error: [IBM][CLI Driver][DB2] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "PSRP1JVL" constrains


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a Project/Activity
2. Create Req, PO and Voucher for say date 11/30/2016.
3. Run PC_PO_TO_PC, PC_AP_TO_PC and PC_POADJUST,
4. The accounting date of all these set of rows will be 11/30/2016
5. Create another set of Req, PO, Voucher for say date 12/13/2016.
6. Run PC_PO_TO_PC,PC_POADJUST, PC_AP_TO_PC. Observe that COM, REQ, RRV,ACT are created with accounting date of 12/13/2016
7. Run PC_POADJUST with all options selected and with Date option as accounting date for 12/13/2016 only.
8. The process abends with the above error.


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