PV_PO_CREATE Fails at Step PO_CMVATCLC.VC2100.LOADETER (SQL) with Unique Constraint Error (Doc ID 2194791.1)

Last updated on JANUARY 18, 2017

Applies to:

PeopleSoft Enterprise SCM eProcurement - Version 9.1 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

The process PV_PO_CREATE fails at Step PO_CMVATCLC.VC2100.LOADETER (SQL) with Unique Constraint Error.

Steps to reproduce the issue:

1. The process is triggered from the Expedite Requisitions Component.
  Navigation : eProcurement --> Buyer Set Up --> Expedite Requisitions.

Error:

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1620 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_PO_VAT_DF_T) violated


Failed SQL stmt:INSERT INTO PS_PO_VAT_DF_T (PROCESS_INSTANCE, BUSINESS_UNIT, PO_ID, PO_STG_TYPE, PO_STG_ID, LINE_NBR, SCHED_NBR, DISTRIB_LINE_NUM, VENDOR_SETID, VENDOR_ID, VNDR_LOC, SHIPTO_SETID, SHIPTO_ID, ITM_SETID, INV_ITEM_ID, CATEGORY_ID, CATEGORY_CD, SETID, PRCS_OPTION_ID, SYSTEM_SOURCE, VAT_ENTITY, PHYSICAL_NATURE, VAT_SVC_PERFRM_FLG, VAT_TREATMENT_GRP, VAT_TREATMENT, COUNTRY_LOC_BUYER, STATE_LOC_BUYER, COUNTRY_LOC_SELLER, STATE_LOC_SELLER, COUNTRY_VAT_PERFRM, STATE_VAT_PERFRM, COUNTRY_VAT_SUPPLY, STATE_VAT_SUPPLY, COUNTRY_SHIP_FROM, STATE_SHIP_FROM, STATE_VAT_DEFAULT, VAT_SVC_SUPPLY_FLG, VAT_SERVICE_TYPE, COUNTRY_SHIP_TO, STATE_SHIP_TO, COUNTRY_VAT_SHIPTO, COUNTRY_VAT_BILLTO, COUNTRY_VAT_BILLFR, DROP_SHIP_FLAG, VAT_RGSTRN_SELLER, REQ_ID, REQ_LINE_NBR, REQ_SCHED_NBR, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, PROJECT_ID, ACCOUNT, ACTIVITY_ID, ALTACCT, DEPTID, ANALYSIS_TYPE, BUSINESS_UNIT_GL, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, RESOURCE_TYPE, STATISTICS_CODE, CURRENCY_CD, CURRENCY_CD_BASE, MERCHANDISE_AMT, RATE_DIV, RATE_MULT, PO_DT, PYMNT_TERMS_CD, LOC_SETID, LOCATION, ADDRESS_SEQ_NUM, VAT_TXN_TYPE_CD, DSCNT_PCT, FREIGHT_AMT, COUNTRY, STATE, VAT_DEFAULT_TYPE_C, VAT_DEFAULT_TYPE_T, VAT_RPT_CNTRY_SRC, VAT_APPLICABLE, VAT_APPLICABILITY, VAT_ENVIRONMENT, VAT_USE_ID, TAX_CD_VAT, TAX_CD_VAT_PCT, VAT_DCLRTN_POINT, VAT_DCLRTN_PT_GDS, VAT_DCLRTN_PT_SVC, VAT_CALC_GROSS_NET, VAT_CALC_FRGHT_FLG, VAT_CALC_MISC_FLG, VAT_RCRD_INPT_FLG, VAT_RCRD_OUTPT_FLG, VAT_CALC_TYPE, VAT_ROUND_RULE, VAT_EXCPTN_TYPE, VAT_EXCPTN_CERTIF, VAT_SUSPENSION_FLG, VAT_NON_SUSP_FLG, VAT_REBATE_PCT, VAT_RECOVERY_PCT, MISC_AMT, DISTRIB_PCT, VAT_ERROR_FLAG, MESSAGE_SET_NBR, MESSAGE_NBR, ERROR_FIELDNAME, CNTRCT_SETID, CNTRCT_ID, CNTRCT_LINE_NBR, BUSINESS_UNIT_DS, BUSINESS_UNIT_VAT, VAT_APORT_CNTRL, VAT_RECALC_FLG, ERROR_FLAG, IST_TXN_FLG, VAT_DFLT_DONE_FLG, VAT_RVRSE_CHG_GDS, TAX_CD_VAT_RVC, TAX_CD_VAT_RVC_PCT, VAT_AMT_RVC, VAT_AMT_RVC_BSE, VAT_SUBJ_RVRSE_CHG, DUE_DT) SELECT I.PROCESS_INSTANCE, I.BUSINESS_UNIT, I.PO_ID, I.PO_STG_TYPE, I.PO_STG_ID, I.LINE_NBR, I.SCHED_NBR, I.DISTRIB_LINE_NUM, I.VENDOR_SETID, I.VENDOR_ID, I.VNDR_LOC, I.SHIPTO_SETID, I.SHIPTO_ID, I.ITM_SETID, I.INV_ITEM_ID, I.CATEGORY_ID, CAT.CATEGORY_CD, BUY.SETID, ' ', I.SYSTEM_SOURCE, E.VAT_ENTITY, I.PHYSICAL_NATURE, I.VAT_SVC_PERFRM_FLG, ' ', ' ', BUY.COUNTRY, BUY.STATE, VN.COUNTRY, VN.STATE, ' ', ' ', ' ', ' ', SF.COUNTRY, SF.STATE, ' ', ' ', ' ', ST.COUNTRY, ST.STATE, ' ', ' ', ' ', ' ', ' ', I.REQ_ID, I.REQ_LINE_NBR, I.REQ_SCHED_NBR, I.OPERATING_UNIT, I.PRODUCT, I.FUND_CODE, I.CLASS_FLD, I.PROGRAM_CODE, I.BUDGET_REF, I.AFFILIATE, I.AFFILIATE_INTRA1, I.AFFILIATE_INTRA2, I.CHARTFIELD1, I.CHARTFIELD2, I.CHARTFIELD3, I.PROJECT_ID, I.ACCOUNT, I.ACTIVITY_ID, I.ALTACCT, I.DEPTID, I.ANALYSIS_TYPE, I.BUSINESS_UNIT_GL, I.RESOURCE_CATEGORY, I.RESOURCE_SUB_CAT, I.RESOURCE_TYPE, I.STATISTICS_CODE, I.CURRENCY_CD, I.CURRENCY_CD_BASE, I.MERCHANDISE_AMT, I.RATE_DIV, I.RATE_MULT, I.PO_DT, I.PYMNT_TERMS_CD, I.LOC_SETID, I.LOCATION, I.ADDRESS_SEQ_NUM, ' ', I.DSCNT_PCT, I.FREIGHT_AMT, BUY.COUNTRY, BUY.STATE, ' ', ' ', ' ', ' ', ' ', E.VAT_ENVIRONMENT, ' ', ' ', 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, I.MISC_AMT, I.DISTRIB_PCT, ' ', 0, 0, ' ', I.CNTRCT_SETID, I.CNTRCT_ID, I.CNTRCT_LINE_NBR, I.BUSINESS_UNIT_DS, ' ', I.VAT_APORT_CNTRL, ' ', ' ', ' ', 'N', ' ', ' ', 0, 0, 0, ' ', I.DUE_DT FROM PS_PO_VC_IN1_T I, PS_PO_VAT_ENT_T E, PS_PO_BUY_CTRY_T BUY, PS_PO_SH_CTRY_T ST, PS_PO_VND_CTRY_T VN , PS_PO_SHF_CTRY_T SF , PS_PO_CAT_CD_T CAT WHERE I.PROCESS_INSTANCE = 6586138 AND E.PROCESS_INSTANCE = I.PROCESS_INSTANCE AND E.BUSINESS_UNIT = I.BUSINESS_UNIT AND E.VAT_ENVIRONMENT = 'Y' AND BUY.PROCESS_INSTANCE = I.PROCESS_INSTANCE AND BUY.BUSINESS_UNIT = I.BUSINESS_UNIT AND BUY.SETID = I.LOC_SETID AND BUY.LOCATION = I.BILL_LOCATION AND ST.PROCESS_INSTANCE = I.PROCESS_INSTANCE AND ST.SETID = I.SHIPTO_SETID AND ST.LOCATION = I.SHIPTO_ID AND VN.PROCESS_INSTANCE = I.PROCESS_INSTANCE AND VN.VENDOR_SETID = I.VENDOR_SETID AND VN.VENDOR_ID = I.VENDOR_ID AND VN.ADDRESS_SEQ_NUM = I.ADDRESS_SEQ_NUM AND SF.PROCESS_INSTANCE = I.PROCESS_INSTANCE AND SF.VENDOR_SETID = I.VENDOR_SETID AND SF.VENDOR_ID = I.VENDOR_ID AND SF.VNDR_LOC = I.VNDR_LOC AND CAT.ITM_SETID = I.ITM_SETID AND CAT.CATEGORY_ID = I.CATEGORY_ID

Process 6586138 ABENDED at Step PO_CMVATCLC.VC2100.LOADETER (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