PC_POADJUST ABENDED at Step PC_POADJUST.QUANTITY.COND_1 (Action SQL) -- RC = 805

(Doc ID 2316111.1)

Last updated on OCTOBER 16, 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, Interfaces

PC POADJUST AP Engine failing on insert duplicate key row in object 'dbo.PS_PC_RES_PA_TA14'

Issue: SQL ABENDED at Step PC_POADJUST.QUANTITY.COND_1 (SQL) when running Purchase Order Reversals in the Cost Collection Purchasing Adjustments

ERROR
-----------------------
-- 09:57:08.840 .....(PC_POADJUST.QUANTITY.COND_1) (SQL)
INSERT INTO PS_PC_RES_PA_TA14 (PROCESS_INSTANCE, 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, 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 O.PROCESS_INSTANCE, O.BUSINESS_UNIT,
O.PROJECT_ID, O.ACTIVITY_ID, 'CRV' + O.BUSINESS_UNIT_PO + O.PO_ID + '#' +
CONVERT(CHAR(5),O.LINE_NBR) + '#' + CONVERT(CHAR(3),O.SCHED_NBR) + '#' +
O.DST_ACCT_TYPE + '#' + CONVERT(CHAR(5),O.DISTRIB_LINE_NUM) , 'CRV' +
O.BUSINESS_UNIT_PO + O.PO_ID + '#' + CONVERT(CHAR(5),O.LINE_NBR) + '#' +
CONVERT(CHAR(3),O.SCHED_NBR) + '#' + O.DST_ACCT_TYPE + '#' +
CONVERT(CHAR(5),O.DISTRIB_LINE_NUM) , O.BUSINESS_UNIT_GL, O.JOURNAL_ID,
O.JOURNAL_DATE, O.UNPOST_SEQ, O.JOURNAL_LINE, O.FISCAL_YEAR,
O.ACCOUNTING_PERIOD, O.ACCOUNT, O.ALTACCT, O.DEPTID, O.OPERATING_UNIT,
O.PRODUCT, O.FUND_CODE, O.CLASS_FLD, O.PROGRAM_CODE, O.BUDGET_REF,
O.AFFILIATE, O.AFFILIATE_INTRA1, O.AFFILIATE_INTRA2, O.CHARTFIELD1,
O.CHARTFIELD2, O.CHARTFIELD3, O.BUS_UNIT_GL_FROM, O.CURRENCY_CD,
O.STATISTICS_CODE, O.LEDGER_GROUP, 'CRV' , O.RESOURCE_TYPE,
O.RESOURCE_CATEGORY, O.RESOURCE_SUB_CAT, O.RES_USER1, O.RES_USER2,
O.RES_USER3, O.RES_USER4, O.RES_USER5, T.TRANS_DT, T.ACCOUNTING_DT, O.OPRID,
GETDATE(), O.JRNL_LN_REF, O.OPEN_ITEM_STATUS, O.LINE_DESCR,
O.JRNL_LINE_STATUS, O.JOURNAL_LINE_DATE, O.CURRENCY_CD, O.RT_TYPE, CASE WHEN
O.AMT_ONLY_FLG = 'N' and O.RESOURCE_AMOUNT > (((((O.RESOURCE_AMOUNT) / (
O.RESOURCE_QUANTITY))) * ( T.RESOURCE_QUANTITY))) THEN
(((((O.RESOURCE_AMOUNT) / ( O.RESOURCE_QUANTITY))) * ( T.RESOURCE_QUANTITY)))
WHEN O.AMT_ONLY_FLG = 'Y' AND O.RESOURCE_AMOUNT > T.RESOURCE_AMOUNT THEN
T.RESOURCE_AMOUNT ELSE O.RESOURCE_AMOUNT END , 1.0 , 1.0 , O.CUR_EFFDT, 'N',
'I', O.PROJ_TRANS_TYPE, O.PROJ_TRANS_CODE, O.RESOURCE_STATUS, O.DESCR,
O.SYSTEM_SOURCE, T.UNIT_OF_MEASURE, O.EMPLID, O.EMPL_RCD, O.SEQ_NBR,
O.TIME_RPTG_CD, O.JOBCODE, O.COMPANY, O.BUSINESS_UNIT_AP, O.VENDOR_ID,
O.VOUCHER_ID, O.VOUCHER_LINE_NUM, O.APPL_JRNL_ID, O.PYMNT_CNT,
O.DST_ACCT_TYPE, O.PO_DISTRIB_STATUS, T.BUSINESS_UNIT_PO, O.REQ_ID,
O.REQ_LINE_NBR, O.REQ_SCHED_NBR, O.REQ_DISTRIB_NBR, T.PO_ID , O.DUE_DATE,
T.LINE_NBR , T.SCHED_NBR , T.DISTRIB_LINE_NUM , O.AM_DISTRIB_STATUS,
O.BUSINESS_UNIT_AM, O.ASSET_ID, O.PROFILE_ID, O.COST_TYPE, O.BOOK,
O.INCENTIVE_ID, O.MSTONE_SEQ, O.CONTRACT_NUM, O.CONTRACT_LINE_NUM,
O.CONTRACT_PPD_SEQ, 'I', O.BUSINESS_UNIT_BI, O.BILLING_DATE, O.INVOICE, 'I',
O.BUSINESS_UNIT_AR, O.CUST_ID, O.ITEM, O.ITEM_LINE, O.ITEM_SEQ_NUM,
O.DST_SEQ_NUM, O.BUSINESS_UNIT_IN, O.SCHED_LINE_NO, O.DEMAND_LINE_NO,
O.INV_ITEM_ID, O.PAY_END_DT, O.BUSINESS_UNIT_OM, O.ORDER_NO,
O.ORDER_INT_LINE_NO, O.EX_DOC_ID, O.EX_DOC_TYPE, CASE WHEN
O.RESOURCE_QUANTITY > T.RESOURCE_QUANTITY AND O.UNIT_OF_MEASURE =
T.UNIT_OF_MEASURE THEN T.RESOURCE_QUANTITY WHEN O.RESOURCE_QUANTITY <=
T.RESOURCE_QUANTITY THEN O.RESOURCE_QUANTITY WHEN O.UNIT_OF_MEASURE <>
T.UNIT_OF_MEASURE THEN O.RESOURCE_QUANTITY ELSE O.RESOURCE_QUANTITY END ,
CASE WHEN O.AMT_ONLY_FLG = 'N' and O.RESOURCE_AMOUNT >
(((((O.RESOURCE_AMOUNT) / ( O.RESOURCE_QUANTITY))) * ( T.RESOURCE_QUANTITY)))
THEN (((((O.RESOURCE_AMOUNT) / ( O.RESOURCE_QUANTITY))) * (
T.RESOURCE_QUANTITY))) WHEN O.AMT_ONLY_FLG = 'Y' AND O.RESOURCE_AMOUNT >
T.RESOURCE_AMOUNT THEN T.RESOURCE_AMOUNT ELSE O.RESOURCE_AMOUNT END,
O.BUDGET_HDR_STATUS, O.KK_AMOUNT_TYPE, O.KK_TRAN_OVER_FLAG,
O.KK_TRAN_OVER_OPRID, O.KK_TRAN_OVER_DTTM, O.BUDGET_OVER_ALLOW,
O.BUDGET_LINE_STATUS, T.BUDGET_DT, O.LEDGER, O.BD_DISTRIB_STATUS,
O.BUSINESS_UNIT_BD, O.FA_STATUS, O.TIME_SHEET_ID, O.SHEET_ID, O.DT_TIMESTAMP,
O.VCHR_DIST_LINE_NUM, O.PM_REVIEWED, O.PRICED_RATE, O.ACTIVITY_ID_DETAIL,
'I', O.TXN_LMT_TRANS_ID, O.EVENT_NUM, O.CA_FEE_STATUS, O.BUSINESS_UNIT_WO,
O.WO_ID, O.WO_TASK_ID, O.RSRC_TYPE, O.RES_LN_NBR, O.COMPRESS_ID,
O.AMOUNT_IN_EXCESS, O.RECLAIMED_FROM_OL, O.FND_DIST_STATUS, O.SEQ_TRANS_ID,
O.DIST_TRANS_ID, O.ADJ_LINE_TYPE, O.FEEDER_SUM_ID, O.PRICE_SUM_ID,
O.DEPOSIT_BU, O.DEPOSIT_ID, O.PAYMENT_SEQ_NUM FROM PS_PC_ORIG_BAL_TAO O ,
PS_PC_TRAN_BAL_TAO T WHERE O.PROCESS_INSTANCE = 621895 AND T.PROCESS_INSTANCE
= 621895 AND O.BUSINESS_UNIT_PO = T.BUSINESS_UNIT_PO AND O.PO_ID = T.PO_ID
AND O.LINE_NBR = T.LINE_NBR AND O.SCHED_NBR = T.SCHED_NBR AND
O.DISTRIB_LINE_NUM = T.DISTRIB_LINE_NUM AND O.DST_ACCT_TYPE = T.DST_ACCT_TYPE
AND O.CURRENCY_CD = T.CURRENCY_CD AND (O.RESOURCE_QUANTITY >
T.RESOURCE_QUANTITY OR O.RESOURCE_AMOUNT > T.RESOURCE_AMOUNT) AND
O.UNIT_OF_MEASURE = T.UNIT_OF_MEASURE AND O.RESOURCE_ID <> T.RESOURCE_ID AND
T.AMOUNT_FLG <> 'Y'
/
-- Row(s) affected: 0
-- 09:57:09.381 Process 621895 ABENDED at Step PC_POADJUST.QUANTITY.COND_1 (Action SQL) -- RC = 805
ROLLBACK
/
-- 09:57:09.440 SQL Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert duplicate key row in object 'dbo.PS_PC_RES_PA_TA14' with unique index 'PS_PC_RES_PA_TA14'. The duplicate key value is (621895, 10000, 20000103, EQUP_IT_CONSULT, CRV100000000007729#1 #

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Purchase Order Reversal Option - COM Rate option chosen in the Installation Options for PC
2. Create project/activity with Asset Integration Rules of Many Assets for Each Activity Distribution - Either Project Percent or Activity percent Distribution
3. Add VIN to CLOSE analysis group
4. Create a Req with VAT.
5. Create a PO from Req and Dispatch
6. Run PC_PO_TO_PC. REQ and COM rows created
7. Run PC_POADJUST. RRV row gets created
8. Create a Voucher from the PO and Post it.
9. Run PC_AP_TO_PC. Run it with detail mode. ACT and VIN transactions get created (2 lines)
10. Run PC_POADJUST. CRV row gets created
11. Run PC_EXPRSS_AM. (Additional rows offset rows and target rows)
12. Run PC_REVERSE_AM after selecting the row for reversal. (Additional rows offset rows and target rows)
13. Run PC_EXPRSS_AM (Additional rows offset rows and target rows). There is an issue reported as the target rows created are more.. This issue is captured in Bug 25714994
14. Using SQL delete the duplicate rows being sent to AM. If bug is resolved it should not send the duplicate rows.
15. Run PC_POADJUST. The process fails with above abend.

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