GM_GMFACS Failing With Unique Constraint Error--- ABENDED at Step FS_BP_STAO.iSrcTAO.iSrcTAO (Action SQL) -- RC = 805

(Doc ID 2394148.1)

Last updated on MAY 07, 2018

Applies to:

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

Symptoms

On : 9.2 version, Commitment Control

GM_GMFACS failing with unique constraint error

GM_GMFACS failing with unique constraint error . checked the insert into statement but there is no duplicate row.Please find the necessary details attached.

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

-- 11.18.23 .........(FS_BP_STAO.iSrcTAO.iSrcTAO) (SQL)
INSERT INTO PS_BP_GM_S_TAO5 (PROCESS_INSTANCE, BUSINESS_UNIT, RESOURCE_ID,
LINE_SEQ_NUM, LEDGER_GROUP, LEDGER, KK_TRAN_ID, KK_TRAN_DT, KK_TRAN_LN,
BUDGET_DT, SUBTYPE, KK_PROCESS_PRIOR, KK_CLOSE_PRIOR, KK_CLOSE_FLAG,
FISCAL_YEAR, ACCOUNTING_PERIOD, ACCOUNT, DEPTID, OPERATING_UNIT, PRODUCT,
FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1,
AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, BUSINESS_UNIT_PC,
PROJECT_ID, ACTIVITY_ID, RESOURCE_TYPE, BUDGET_PERIOD, ACCOUNT_TYPE,
KK_OVERRIDE_ACCT, FOREIGN_CURRENCY, FOREIGN_AMOUNT, CURRENCY_CD,
MONETARY_AMOUNT, KK_QUANTITY, STATISTICS_CODE, STATISTIC_AMOUNT, STATUS_CODE,
KK_PROC_INSTANCE, ENTRY_EVENT, LINE_SELECT, LIQUIDATE_METHOD,
BUDGET_LINE_STATUS, EXIST, KK_REFD_ID, KK_REFD_DT, KK_REFD_LN, FUND_SOURCE,
TRAN_NUM_ASSIGN, SEQUENCE_NBR_9, KK_AMOUNT_TYPE, ACCOUNTING_DT,
BUSINESS_UNIT_GL, FIELDNAME_ORDER_BY, EFFDT_SOURCE, KK_BUDG_TRANS_TYPE,
KK_GEN_PARENT, KK_DEFAULT_EE, KK_PARENT_ENT_TYPE, EFFDT_BD, KK_SKIP_EDITS,
KK_ACCOUNTING_DT, KK_TRAN_OVER_FLAG, KK_TRAN_OVER_OPRID, KK_TRAN_OVER_DTTM,
INSERT_ROW_FLG, CONCAT_STRING, DELETE_FLAG, EXIST2) SELECT DISTINCT 633244,
LN.BUSINESS_UNIT, LN.RESOURCE_ID, LN.LINE_SEQ_NUM, LN.LEDGER_GROUP,
LN.LEDGER, KKSH.KK_TRAN_ID, KKSH.KK_TRAN_DT, 0, LN.BUDGET_DT, ' ', ' ', ' ',
'N', LN.FISCAL_YEAR, LN.ACCOUNTING_PERIOD, LN.ACCOUNT, LN.DEPTID,
LN.OPERATING_UNIT, LN.PRODUCT, LN.FUND_CODE, LN.CLASS_FLD, LN.PROGRAM_CODE,
LN.BUDGET_REF, LN.AFFILIATE, LN.AFFILIATE_INTRA1, LN.AFFILIATE_INTRA2,
LN.CHARTFIELD1, LN.CHARTFIELD2, LN.CHARTFIELD3, LN.BUSINESS_UNIT_PC,
LN.PROJECT_ID, LN.ACTIVITY_ID, LN.RESOURCE_TYPE, ' ', ' ', 'N',
LN.FOREIGN_CURRENCY, LN.FOREIGN_AMOUNT, LN.CURRENCY_CD, LN.RESOURCE_AMOUNT,
0, LN.STATISTICS_CODE, LN.STATISTIC_AMOUNT, 'N', 633244, ' ', ' ', ' ',
LN.BUDGET_LINE_STATUS, 'N', ' ', NULL, 0, ' ', 0, 0, '7', LN.ACCOUNTING_DT,
LN.BUSINESS_UNIT_GL, ' ', LN.BUDGET_DT, ' ', 'N', 'N', ' ', NULL, 'N',
HDR.ACCOUNTING_DT, HDR.KK_TRAN_OVER_FLAG, HDR.KK_TRAN_OVER_OPRID,
HDR.KK_TRAN_OVER_DTTM, 'Y', LN.BUSINESS_UNIT_GL || LN.LEDGER_GROUP ||
LN.LEDGER, ' ', 'N' FROM PS_GM_PRJ_RES_VW LN, PS_GM_PRJ_RHDR_VW HDR,
PS_KK_SHDR_GMFA KKSH WHERE LN.BUSINESS_UNIT = KKSH.BUSINESS_UNIT AND
LN.PROJECT_ID = KKSH.PROJECT_ID AND LN.ACTIVITY_ID = KKSH.ACTIVITY_ID AND
LN.RESOURCE_ID = KKSH.RESOURCE_ID AND HDR.BUSINESS_UNIT = KKSH.BUSINESS_UNIT
AND HDR.PROJECT_ID = KKSH.PROJECT_ID AND HDR.ACTIVITY_ID = KKSH.ACTIVITY_ID
AND HDR.RESOURCE_ID = KKSH.RESOURCE_ID AND KKSH.KK_PROC_INSTANCE = 633244 AND
KKSH.KK_PROCESS_STATUS = 'I' AND KKSH.KK_SOURCE_TRAN = 'GM_FA'
/
-- Row(s) affected: 0
-- 11.18.24 Process 633244 ABENDED at Step FS_BP_STAO.iSrcTAO.iSrcTAO (Action SQL) -- RC = 805
ROLLBACK
/
-- 11.18.24 SQL Error: ORA-00001: unique constraint (SYSADM.PSABP_GM_S_TAO5) violated

STEPS
-----------------------
No Specific scenario that identifies the issue
1. Probably an old GM_GMFACS was abended. Based on data in PS_GM_PRJ_RHDR_TAO. User might have just deleted the process instance without much analysis at that time
2. Reset the FA_STATUS for the same rows that were touched by earlier abended process. User might have updated the FA_STATUS on PS_PROJ_RESOURCE from 'I' to 'N'
3. Run GM_GMFACS batch process

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