PO_POCREATE Fails For Unique Constraint At PO_POCREATE.PB4800.STEP06A (Doc ID 2245679.1)

Last updated on MARCH 20, 2017

Applies to:

PeopleSoft Enterprise SCM Purchasing - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

PO_POCREATE fails for unique constraint PB4800 Step6A

Scenario-1

Steps to reproduce the issue:


 1. Stage several POs with multiple lines in the Staging tables that are NOT sourced from requisitions.
2. They should not have req ids, and in our example are the type of "O" (not that I think that matters).
3. Lines should be marked as "amount only". In other words -- the field PS_PO_ITM_STG.amt_only_flg = Y
 4. Run PO_CALC and PO_POCREATE. PO Create fails. The join with the new table PS_PO_DISTRIB_STG does a cartegian join and matches all the lines with each other. See attached trace file. Also attaching excel spreadsheet for what is in each of the tables at the time it runs. "

Scenario-2

While sourcing a contract which has 2 milestone releases. encountered an Unique constraint error at step PO_POCREATE.PB4800.STEP06A.

Steps to reproduce the issue:

1) Create a contract.
2) 1 adhoc line.
3) Check on release milestone.
4) Create 2 milestones.
5) Create 2 contract releases.
6) Run auto sourcing.
7) Error at step PO_POCREATE.PB4800.STEP06A


Error


SQL error. Stmt #: 1723 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_PO_POB_DIST_T17) violated
Failed SQL stmt: INSERT INTO PS_PO_POB_DIST_T17( BUSINESS_UNIT , PO_ID , LINE_NBR , SCHED_NBR , DISTRIB_LINE_NUM , QTY_PO , MERCHANDISE_AMT , DST_ACCT_TYPE , LOCATION , ACCOUNT , ALTACCT , DEPTID , CHARTFIELD_STATUS , BUSINESS_UNIT_GL , DISTRIB_LN_STATUS , SYSTEM_SOURCE , BUSINESS_UNIT_REQ , BUSINESS_UNIT_PC , ACTIVITY_ID , ANALYSIS_TYPE , RESOURCE_TYPE , RESOURCE_CATEGORY , RESOURCE_SUB_CAT , PROCESS_INSTANCE , PROCESS_MAN_CLOSE , REQ_ID , REQ_LINE_NBR , REQ_SCHED_NBR , REQ_DISTRIB_NBR , PO_POST_STATUS , PO_POST_AMT , CLOSE_AMOUNT , PC_DISTRIB_STATUS , PC_DISTRIB_AMT , PROFILE_ID , BUSINESS_UNIT_AM , BUSINESS_UNIT_IN , STATISTICS_CODE , STATISTIC_AMOUNT , QTY_REQ , DISTRIB_TYPE , DIST_PROCESSED_FLG , INVOICE_CLOSE_IND , FINANCIAL_ASSET_SW , DISTRIB_PCT , CURRENCY_CD , CURRENCY_CD_BASE , RT_TYPE , RATE_DIV , RATE_MULT , MERCH_AMT_BSE , PO_POST_AMT_BSE , PC_DISTRIB_AMT_BSE , CLOSE_AMOUNT_BSE , EMPLID , CAP_NUM , CAP_SEQUENCE , TAX_CD_SUT , TAX_CD_SUT_PCT , SALETX_AMT , SALETX_AMT_BSE , USETAX_AMT , USETAX_AMT_BSE , SUT_APPLICABILITY , VAT_TXN_TYPE_CD , VAT_APPLICABILITY , TAX_CD_VAT , TAX_CD_VAT_PCT , VAT_AMT , VAT_AMT_BASE , VAT_BASIS_AMT , VAT_BASIS_AMT_BSE , VAT_RECOVERY_PCT , VAT_RCVRY_AMT , VAT_RCVRY_AMT_BSE , VAT_REBATE_PCT , VAT_REBATE_AMT , VAT_REBATE_AMT_BSE , VAT_USE_ID , FREIGHT_AMT , FREIGHT_AMT_BSE , FREIGHT_AMT_NP , FREIGHT_AMT_NP_BSE , MISC_AMT , MISC_AMT_BSE , MISC_AMT_NP , MISC_AMT_NP_BSE , MONETARY_AMOUNT , MONETARY_AMT_BSE , BUDGET_DT , BUDGET_LINE_STATUS , VAT_CALC_TYPE , SALETX_PRORATE_FLG , USETAX_PRORATE_FLG , VAT_NRCVR_PRO_FLG , VAT_PRORATE_FLG , COST_TYPE , KK_CLOSE_FLAG , KK_PROCESS_PRIOR , TAG_NUMBER , CONSIGNED_FLAG , PUBLISHED_IBU , VAT_RCVRY_PCT_SRC , VAT_REBATE_PCT_SRC , VAT_TRANS_AMT , VAT_TRANS_AMT_BSE , VAT_APORT_CNTRL, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3,PROJECT_ID,KK_CLOSE_PRIOR,DOC_TOL_LN_STATUS, ROLL_STAT_R, USER_DIST_CHAR1, CUSTOM_C100_D1, CUSTOM_C100_D2, CUSTOM_C100_D3, CUSTOM_C100_D4, CUSTOM_DATE_D, CUSTOM_C1_D,ENTRY_EVENT, QTY_PO_STD_EXP, PEG_STATUS, ATTN_TO, EE_SEQ_NUM , TAX_VAT_FLG, TAX_FRGHT_FLG, TAX_MISC_FLG, SALES_USE_TX_FLG, QTY_REQ_SHIP, MERCH_AMT_SHIP, MERCH_AMT_SHIP_BSE, PO_DIST_LINE_NUM, RATE_DATE, ACCOUNTING_DT, CONVERSION_RATE) SELECT DISTINCT D.BUSINESS_UNIT , D.PO_ID , D.LINE_NBR , D.SCHED_NBR , D.DISTRIB_LINE_NUM , D.QTY_REQ , ROUND(((((DST.MERCHANDISE_AMT) * ( DST.RATE_MULT))) / ( DST.RATE_DIV)), 2) , 'DST' , D.LOCATION , D.ACCOUNT , D.ALTACCT , D.DEPTID , D.CHARTFIELD_STATUS , D.BUSINESS_UNIT_GL , 'O' , S.SYSTEM_SOURCE , D.BUSINESS_UNIT_REQ , D.BUSINESS_UNIT_PC , D.ACTIVITY_ID , D.ANALYSIS_TYPE , D.RESOURCE_TYPE , D.RESOURCE_CATEGORY , D.RESOURCE_SUB_CAT , D.PROCESS_INSTANCE , 'N' , D.REQ_ID , D.REQ_LINE_NBR , D.REQ_SCHED_NBR , D.REQ_DISTRIB_NBR , 'N' , 0 , 0 , 'N' , 0 , D.PROFILE_ID , D.BUSINESS_UNIT_AM , D.BUSINESS_UNIT_IN , D.STATISTICS_CODE , 0 , D.QTY_REQ , D.DISTRIB_TYPE , 'N' , 'N' , D.FINANCIAL_ASSET_SW , D.DISTRIB_PCT , D.CURRENCY_CD , D.CURRENCY_CD_BASE , D.RT_TYPE , D.RATE_DIV , D.RATE_MULT , S.MERCH_AMT_BSE , 0 , 0 , 0 , D.EMPLID , D.CAP_NUM , D.CAP_SEQUENCE , D.TAX_CD_SUT , D.TAX_CD_SUT_PCT , D.SALETX_AMT , D.SALETX_AMT_BSE , D.USETAX_AMT , D.USETAX_AMT_BSE , D.SUT_APPLICABILITY , D.VAT_TXN_TYPE_CD , D.VAT_APPLICABILITY , D.TAX_CD_VAT , D.TAX_CD_VAT_PCT , D.VAT_AMT , D.VAT_AMT_BASE , D.VAT_BASIS_AMT , D.VAT_BASIS_AMT_BASE , D.VAT_RECOVERY_PCT , D.VAT_RCVRY_AMT , D.VAT_RCVRY_AMT_BSE , D.VAT_REBATE_PCT , D.VAT_REBATE_AMT , D.VAT_REBATE_AMT_BSE , D.VAT_USE_ID , D.FREIGHT_AMT , D.FREIGHT_AMT_BSE , D.FREIGHT_AMT_NP , D.FREIGHT_AMT_NP_BSE , D.MISC_AMT , D.MISC_AMT_BSE , D.MISC_AMT_NP , D.MISC_AMT_NP_BSE , S.MERCHANDISE_AMT , S.MERCH_AMT_BSE , H.ACCOUNTING_DT , 'V' , S.VAT_CALC_TYPE , B.SALETX_PRORATE_FLG , B.USETAX_PRORATE_FLG , B.VAT_NRCVR_PRO_FLG , B.VAT_PRORATE_FLG , D.COST_TYPE , 'N' , 'N' , D.TAG_NUMBER , 'N' , ' ' , 'A' , 'A' , D.VAT_TRANS_AMT , D.VAT_TRANS_AMT_BSE , D.VAT_APORT_CNTRL , D.OPERATING_UNIT, D.PRODUCT, D.FUND_CODE, D.CLASS_FLD, D.PROGRAM_CODE, D.BUDGET_REF, D.AFFILIATE, D.AFFILIATE_INTRA1, D.AFFILIATE_INTRA2, D.CHARTFIELD1, D.CHARTFIELD2, D.CHARTFIELD3 , D.PROJECT_ID , 'N' , 'V' , 'N' , D.USER_DIST_CHAR1, D.CUSTOM_C100_D1, D.CUSTOM_C100_D2, D.CUSTOM_C100_D3, D.CUSTOM_C100_D4, D.CUSTOM_DATE_D, D.CUSTOM_C1_D , D.ENTRY_EVENT , 0 , '40' , S.ATTN_TO , 0 , D.TAX_VAT_FLG , D.TAX_FRGHT_FLG , D.TAX_MISC_FLG , D.SALES_USE_TX_FLG , S.QTY_REQ , DST.MERCHANDISE_AMT , S.MERCH_AMT_BSE , D.DISTRIB_LINE_NUM , D.RATE_DATE , H.ACCOUNTING_DT , S.CONVERSION_RATE FROM PS_PO_POB_DIS2_T17 D , PS_PO_POB_SHP_T17 S , PS_PO_POB_HDR_T17 H , PS_PO_POB_BU_T17 B , PS_PO_DISTRIB_STG DST , PS_PO_POB_LINE_T17 LN WHERE D.PROCESS_INSTANCE = 530525 AND DST.BUSINESS_UNIT = D.BUSINESS_UNIT AND DST.LINE_NBR = D.LINE_NBR AND DST.SCHED_NBR = D.SCHED_NBR AND DST.DISTRIB_LINE_NUM = D.DISTRIB_LINE_NUM AND DST.PROCESS_INSTANCE = D.PROCESS_INSTANCE AND DST.REQ_ID = ' ' AND S.PROCESS_INSTANCE = D.PROCESS_INSTANCE AND S.BUSINESS_UNIT = D.BUSINESS_UNIT AND S.PO_ID = D.PO_ID AND S.PO_LINE_NBR = D.LINE_NBR AND S.PO_SCHED_NBR = D.SCHED_NBR AND H.PROCESS_INSTANCE = D.PROCESS_INSTANCE AND H.BUSINESS_UNIT = D.BUSINESS_UNIT AND H.PO_ID = D.PO_ID AND B.PROCESS_INSTANCE = D.PROCESS_INSTANCE AND B.PROC_INST_POCALC = D.PROC_INST_POCALC AND B.BUSINESS_UNIT = D.BUSINESS_UNIT AND LN.process_instance = D.process_instance AND LN.business_unit = D.business_unit AND LN.po_id = D.po_id AND LN.po_line_nbr = D.line_nbr AND LN.amt_only_flg = 'Y' AND DST.LINE_NBR = LN.PO_LINE_NBR AND DST.BUSINESS_UNIT_PC = D.BUSINESS_UNIT_PC

Process 530525 ABENDED at Step PO_POCREATE.PB4800.STEP06A (SQL) -- RC = 805 (108,524)



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