PO Calculation process(PO_POCALC) Abends At Step PO_CMVATCLC.VC2100.CTRYSHFR UNIQUE CONST (Doc ID 2291380.1)

Last updated on JULY 28, 2017

Applies to:

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

Symptoms

Issue:-PO Calculation process fails with unique constraint error when supplier has multiple effective dates for same address sequence# but with different state/province and there is data with different PO date such that more than one effective selected

Error:-
-- 03:07:35.297 ......(PO_CMVATCLC.VC2100.CTRYSHFR) (SQL)
INSERT INTO PS_PO_SHF_CTRY_T4( PROCESS_INSTANCE , VENDOR_SETID , VENDOR_ID ,
VNDR_LOC , COUNTRY , STATE) SELECT DISTINCT I.PROCESS_INSTANCE ,
I.VENDOR_SETID , I.VENDOR_ID , I.VNDR_LOC , A.COUNTRY , A.STATE FROM
PS_PO_VC_IN1_T4 I , PS_VNDR_LOC_ADDRVW A , PS_PO_VAT_ENT_T4 E WHERE
I.PROCESS_INSTANCE = 122053 AND A.SETID = I.VENDOR_SETID AND A.VENDOR_ID =
I.VENDOR_ID AND A.VNDR_LOC = I.VNDR_LOC AND A.ADDRESS_SEQ_NUM =
I.ADDRESS_SEQ_NUM AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(A2.EFFDT)
FROM PS_VNDR_LOC_ADDRVW A2 WHERE A2.SETID = A.SETID AND A2.VENDOR_ID =
A.VENDOR_ID AND A2.VNDR_LOC = A.VNDR_LOC AND A2.ADDRESS_SEQ_NUM =
A.ADDRESS_SEQ_NUM AND A2.EFFDT <= I.PO_DT AND A2.EFF_STATUS = 'A') AND
E.PROCESS_INSTANCE = I.PROCESS_INSTANCE AND E.BUSINESS_UNIT = I.BUSINESS_UNIT
AND E.VAT_ENVIRONMENT = 'Y'
/
-- Row(s) affected: 0
-- 03:07:35.601 Process 122053 ABENDED at Step PO_CMVATCLC.VC2100.CTRYSHFR
(Action SQL) -- RC = 805
ROLLBACK
/
-- 03:07:35.696 SQL Error: ORA-00001: unique constraint
(EMDBO.PS_PO_SHF_CTRY_T4) violated

Steps To Reproduce the Issue:-
1. Setup supplier address with multiple effective dates and different states.
2. Populate PO staging tables for same supplier with multiple POs having different PO dates such that PO date falls under different effective dates.
3. Run the PO CALC process and it will fail on steps PO_CMVATCLC VC2100 CTRYSHFR SQL

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