How To Resolve the Error "ORA-00001: unique constraint (SYSADM.PS_UPGDLV_MP_TAO4) violated" at Step UPG_DELIVERY.INIT.ShFrtMsc (SQL) when Running the Upgrade Application Engine Process EOUFDATACONV
(Doc ID 1413745.1)
Last updated on MARCH 01, 2024
Applies to:
PeopleSoft Enterprise SCM Order Management - Version 8.9 and laterInformation in this document applies to any platform.
Goal
When upgrading from release 8.8 to a release >= 8.9, the upgrade Application Engine process EOUFDATACONV is failing with following error:
ORA-00001: unique constraint (SYSADM.PS_UPGDLV_MP_TAO4) violated
Process 6549707 ABENDED at Step UPG_DELIVERY.INIT.ShFrtMsc (SQL) -- RC = 805 (108,524)
Failed SQL stmt:INSERT INTO PS_UPGDLV_MP_TAO4 (PROCESS_INSTANCE, SOURCE_BUS_UNIT, DEMAND_SOURCE, ORDER_NO, SHIP_ID_EST, SHIP_ID, DEPLETED_FLAG, DUPLICATE_FLG,
ORDER_TYPE_CD, FREIGHT_BILL_TYPE, SOURCE_CD, BUSINESS_UNIT, DELIVERY_ID, BASE_CURRENCY, RT_TYPE, RATE_MULT, RATE_DIV, RATE_DATE, CURRENCY_CD, CURRENCY_CD_BASE,
SETID_AUTO_NUM, SETID_TRFT, SETID_SRC_CD, SETID_INV_ITEM, BILL_ONLY, DOC_REFNUM, FRT_CHRG_METHOD, EFFDT) SELECT DISTINCT 6289649, M.SOURCE_BUS_UNIT,
M.DEMAND_SOURCE, M.ORDER_NO, ' ', M.SHIP_ID, 'Y', 'N', H.ORDER_TYPE_CD, H.FREIGHT_BILL_TYPE, H.SOURCE_CD, M.BUSINESS_UNIT, ' ', B.BASE_CURRENCY, B.CUR_RT_TYPE, 0, 0,
H.RATE_DATE, H.CURRENCY_CD, H.CURRENCY_CD_BASE, C.SETID, ' ', ' ', ' ', 'Y', ' ', '02', TO_DATE(TO_CHAR(M.DT_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') FROM PS_SHIP_FRT_MISC M JOIN PS_ORD_HEADER H ON H.BUSINESS_UNIT = M.SOURCE_BUS_UNIT AND H.ORDER_NO = M.ORDER_NO AND M.DEMAND_SOURCE = 'OM' JOIN PS_BUS_UNIT_TBL_IN B ON B.BUSINESS_UNIT =
M.BUSINESS_UNIT LEFT OUTER JOIN PS_SET_CNTRL_REC C ON C.SETCNTRLVALUE = B.BUSINESS_UNIT AND C.RECNAME = 'INV_AN_DEL_VW' WHERE NOT EXISTS ( SELECT 'X' FROM
PS_UPGDLV_MP_TAO4 U WHERE U.PROCESS_INSTANCE = 6289649 AND U.SOURCE_BUS_UNIT = M.SOURCE_BUS_UNIT AND U.DEMAND_SOURCE = M.DEMAND_SOURCE AND U.ORDER_NO =
M.ORDER_NO AND U.SHIP_ID = M.SHIP_ID AND U.SHIP_ID_EST > ' ')
Process 6549707 ABENDED at Step UPG_DELIVERY.INIT.ShFrtMsc (SQL) -- RC = 805 (108,524)
Failed SQL stmt:INSERT INTO PS_UPGDLV_MP_TAO4 (PROCESS_INSTANCE, SOURCE_BUS_UNIT, DEMAND_SOURCE, ORDER_NO, SHIP_ID_EST, SHIP_ID, DEPLETED_FLAG, DUPLICATE_FLG,
ORDER_TYPE_CD, FREIGHT_BILL_TYPE, SOURCE_CD, BUSINESS_UNIT, DELIVERY_ID, BASE_CURRENCY, RT_TYPE, RATE_MULT, RATE_DIV, RATE_DATE, CURRENCY_CD, CURRENCY_CD_BASE,
SETID_AUTO_NUM, SETID_TRFT, SETID_SRC_CD, SETID_INV_ITEM, BILL_ONLY, DOC_REFNUM, FRT_CHRG_METHOD, EFFDT) SELECT DISTINCT 6289649, M.SOURCE_BUS_UNIT,
M.DEMAND_SOURCE, M.ORDER_NO, ' ', M.SHIP_ID, 'Y', 'N', H.ORDER_TYPE_CD, H.FREIGHT_BILL_TYPE, H.SOURCE_CD, M.BUSINESS_UNIT, ' ', B.BASE_CURRENCY, B.CUR_RT_TYPE, 0, 0,
H.RATE_DATE, H.CURRENCY_CD, H.CURRENCY_CD_BASE, C.SETID, ' ', ' ', ' ', 'Y', ' ', '02', TO_DATE(TO_CHAR(M.DT_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') FROM PS_SHIP_FRT_MISC M JOIN PS_ORD_HEADER H ON H.BUSINESS_UNIT = M.SOURCE_BUS_UNIT AND H.ORDER_NO = M.ORDER_NO AND M.DEMAND_SOURCE = 'OM' JOIN PS_BUS_UNIT_TBL_IN B ON B.BUSINESS_UNIT =
M.BUSINESS_UNIT LEFT OUTER JOIN PS_SET_CNTRL_REC C ON C.SETCNTRLVALUE = B.BUSINESS_UNIT AND C.RECNAME = 'INV_AN_DEL_VW' WHERE NOT EXISTS ( SELECT 'X' FROM
PS_UPGDLV_MP_TAO4 U WHERE U.PROCESS_INSTANCE = 6289649 AND U.SOURCE_BUS_UNIT = M.SOURCE_BUS_UNIT AND U.DEMAND_SOURCE = M.DEMAND_SOURCE AND U.ORDER_NO =
M.ORDER_NO AND U.SHIP_ID = M.SHIP_ID AND U.SHIP_ID_EST > ' ')
How to resolve it?
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |
| References |