Target Allocation Rows Are Not Generated When Doing PC To PC Allocations (FS_ALLOC), The Process Goes To Success When In Trace, There Is A Unique Constraint Error

(Doc ID 2391244.1)

Last updated on APRIL 27, 2018

Applies to:

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

Symptoms

ACTUAL BEHAVIOR
----------------------------
When running project costing allocation with RES_USER1 field defined in allocation steps for target, the allocation rows were not created. The process goes to success but then the trace shows unique constraint error

616882 23:00:00.537 0.000000 Cur#1.14340.E920GXAU RC=0 Dur=0.000000 COM Stmt=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 A.BUSINESS_UNIT, A.PROJECT_ID, A.ACTIVITY_ID, A.RESOURCE_ID, A.RESOURCE_ID_FROM, A.BUSINESS_UNIT_GL, ' ', TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'), 0, 0, A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.ACCOUNT, A.ALTACCT, A.DEPTID, A.OPERATING_UNIT, A.PRODUCT, A.FUND_CODE, A.CLASS_FLD, A.PROGRAM_CODE, A.BUDGET_REF, A.AFFILIATE, A.AFFILIATE_INTRA1, A.AFFILIATE_INTRA2, A.CHARTFIELD1, A.CHARTFIELD2, A.CHARTFIELD3, A.BUS_UNIT_GL_FROM, A.CURRENCY_CD, A.STATISTICS_CODE, A.LEDGER_GROUP, A.ANALYSIS_TYPE, A.RESOURCE_TYPE, A.RESOURCE_CATEGORY, A.RESOURCE_SUB_CAT, A.RES_USER1, ' ', ' ', ' ', ' ', A.TRANS_DT, A.ACCOUNTING_DT, ' ', CAST(SYSTIMESTAMP AS TIMESTAMP), ' ', ' ', ' ', ' ', NULL, A.FOREIGN_CURRENCY, A.RT_TYPE, A.FOREIGN_AMOUNT, A.RATE_MULT, A.RATE_DIV, A.CUR_EFFDT, 232858, A.PC_DISTRIB_STATUS, A.GL_DISTRIB_STATUS, A.PROJ_TRANS_TYPE, A.PROJ_TRANS_CODE, A.RESOURCE_STATUS, ' ', A.SYSTEM_SOURCE, A.UNIT_OF_MEASURE, ' ', 0, A.SEQ_NBR, ' ', A.JOBCODE, ' ', A.BUSINESS_UNIT_AP, A.VENDOR_ID, A.VOUCHER_ID, A.VOUCHER_LINE_NUM, ' ', 0, A.DST_ACCT_TYPE, A.PO_DISTRIB_STATUS, A.BUSINESS_UNIT_PO, A.REQ_ID, 0, 0, 0, A.PO_ID, NULL, A.LINE_NBR, A.SCHED_NBR, A.DISTRIB_LINE_NUM, A.AM_DISTRIB_STATUS, A.BUSINESS_UNIT_AM, A.ASSET_ID, A.PROFILE_ID, A.COST_TYPE, A.BOOK, ' ', 0, ' ', 0, 0, A.BI_DISTRIB_STATUS, A.BUSINESS_UNIT_BI, NULL, ' ', 'N', A.BUSINESS_UNIT_AR, A.CUST_ID, A.ITEM, A.ITEM_LINE, 0, A.DST_SEQ_NUM, A.BUSINESS_UNIT_IN, A.SCHED_LINE_NO, 0, A.INV_ITEM_ID, NULL, A.BUSINESS_UNIT_OM, A.ORDER_NO, A.ORDER_INT_LINE_NO, ' ', ' ', A.RESOURCE_QUANTITY, A.RESOURCE_AMOUNT, 'V', ' ', ' ', ' ', NULL, ' ', ' ', NULL, A.LEDGER, 'N', ' ', 'N', ' ', ' ', NULL, 0, 'Y', 0, ' ', 'N', ' ', 0, ' ', ' ', ' ', 0, ' ', 0, 0, ' ', ' ', 'N', 0, 0, ' ', 0, 0, ' ', ' ', 0 FROM PS_ZALC_PCTS_TAO A WHERE A.PROCESS_INSTANCE = 232858 AND (FOREIGN_AMOUNT <> 0.00 OR RESOURCE_AMOUNT <> 0.00)
616883 23:00:00.553 0.015000 Cur#1.14340.E920GXAU RC=805 Dur=0.014000 EXE
616884 23:00:00.553 0.000000 Cur#1.14340.E920GXAU RC=0 Dur=0.000000 EPO error pos=0
616885 23:00:00.554 0.001000 Cur#1.14340.E920GXAU RC=0 Dur=0.000000 RCD rtncd=805
616886 23:00:00.554 0.000000 Cur#0.14340.notSamTran RC=-1 Dur=0.000000 XER rtncd=0 msg=ORA-00001: unique constraint (EMDBO.PS_PROJ_RESOURCE) violated
616887 23:00:00.554 0.000000 Cur#0.14340.notSamTran RC=0 Dur=0.000000 ERR rtncd=805 msg=ORA-00001: unique constraint (EMDBO.PS_PROJ_RESOURCE) violated

EXPECTED BEHAVIOR
---------------------------------
The PC to PC allocation should works properly creating the target and offset rows. If there are any unique constraint issues, the process should not go to success.

Replication
-----------------
1. Create a Project
2. Create an activity
3. Create 2 ACT transactions with different RES_USER1 field values
4. Create PC_TO_PC Allocation step

Type - Copy
Pool- ACT rows from PROJ_RES_CAL_VW table for a specific project/activity
Target - Uses the Transaction User1, Transaction ID fields in the specified field values section apart from any other relevant fields
offset - As appropriate to create offset rows
Batch Records - Create custom batch records from delivered batch records to incorporate the addition of RES_USER1 field.

5. Run the Allocation Request.
6. The process goes to success and no allocation rows are created - This is incorrect
7. When reviewed the trace, the trace shows the above unique constraint error. Looks like an issue with the way the SEQUENCE_NBR_9 is calculated.

The same with other user fields like RES_USER2,RES_USER3, RES_USER4, RES_USER5

8. The process going to success with an abend in the trace is incorrect and the user will not know unless they trace back to figure what went wrong.

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