EEX 9.0+: Stage Payments Abend At Step EX_STAGE_PMT.WORKTBLS.VND_PMT If Multiple Payment Types For Same Vendor Exist Using Different Location/Address (Doc ID 1639876.1)

Last updated on MARCH 16, 2016

Applies to:

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

Symptoms

ISSUE:

The Expense Processing Application Engine Program (EX_TRAN_PRCS) goes into a No Success status when trying to Stage Payments when the following criteria are met:

   a.- The Vendor ID linked to the Credit Card Vendor AMEX has been defined with multiple Locations and Addresses
   b.- And multiple Vendor Payment Types exist defined in the system, for the same Vendor ID, but different Location and Address combination

If the above conditions are met, then, upon running the Stage Payments process, should there be any Expense Reports containing Lines making reference to Payment Types from AMEX Vendor, the process will abend in Error Message.

REPLICATION STEPS:

   - Log into the FSCM Online Application as User ID VP1
   - Navigate to: Travel and Expenses > Manage Employee Information > Update Profile
   - Open existing Employee ID KU0021
   - At the Corporate Card Information tab, make sure the Employee has one row for Card Issuer AMEX
   - Navigate to: Vendors > Vendor Information > Add/Update > Vendor
   - Open existing Vendor ID USA0000030 under Set ID SHARE
   - Go to the Location tab, and add a second Location
   - Go to the Address tab, and add a second Address
   - Navigate to: Set Up Financials/Supply Chain > Product Related > Expenses > Payment Information > Payment Type
   - Under Set ID SHARE, open the existing Payment Type AMX, and make sure to set up the following configuration:
        - Set ID = SHARE
        - Payment Type = AMX
        - Effective Date = January 1st 1900
        - Status = Active
        - Vendor = Y
        - Vendor Set ID = SHARE
        - Vendor ID = USA0000030
        - Vendor Location = 1
        - Address = 1
   - Navigate to: Set Up Financials/Supply Chain > Product Related > Expenses > Payment Information > Payment Type
   - Go now and create a brand new Payment Type called AMI, making sure of defining the below settings:
        - Set ID = SHARE
        - Payment Type = AMI
        - Effective Date = January 1st 1900
        - Status = Active
        - Vendor = Y
        - Vendor Set ID = SHARE
        - Vendor ID = USA0000030
        - Vendor Location = 2
        - Address = 2
   - Log into the FSCM Online Application as User ID EXA1
   - Navigate to: Employee Self-Service > Travel and Expenses > My Wallet
   - Define Employee ID KU0021, and click on search
   - Add a brand new Credit Card transactions with the below information:
        - Expense Type = Groceries
        - Transaction Date = March 10th 2014
        - Payment Type = American Express (AMX)
        - Expense Location = New York
        - Cardmember Number = ************1234
        - Transaction Amount = 350.00
        - Currency Code = USD
   - Save the changes
   - Navigate to: Employee Self-Service > Travel and Expense Center > Expense Report > Create
   - Define Employee ID KU0021, and add a new value
   - Default into the new Expense Report the My Wallet transaction that has just been created
   - Complete filling the remaining Fields, and submit for approval the Expense Report
   - Log into the FSCM Online Application as User ID EXS1
   - Navigate to: Manager Self-Services > Travel and Expense Center > Approvals > Approve Transactions
   - Find the routed Expense Report, and approve it for payment
   - Log into the FSCM Online Application as User ID VP1
   - Navigate to: Travel and Expenses > Process Expenses > Expense Processing
   - Add a new Run Control ID with the following settings:
        - Run Control ID = GCS
        - Stage Payments = Y
        - Business Unit = US001
   - Launch the process
   - After a while, check that EX_TRAN_PRCS Application Engine Program has gone to No Success with an Error Message in the Logs

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.

ERROR MESSAGE:

   " EX_STAGE_PMT.WORKTBLS.VND_PMT) (SQL)
     INSERT INTO PS_EXVNDPMT_TAO4 (PROCESS_INSTANCE, SETID, VENDOR_ID, VENDOR_SETID, VENDOR_NAME_SHORT, NAME1, NAME2, NAME1_AC, NAME2_AC, VNDR_LOC, REMIT_BANK_SETID, REMIT_BANK_CD, REMIT_BNK_ACCT_KEY, REMIT_SETID, REMIT_VENDOR, PYMNT_HOLD, EFT_PYMNT_FMT_CD, EFT_RTNG_FLG, EFT_RTNG_STATUS, EFT_TRANS_HANDLING, EFT_DOM_COSTS_CD, EFT_CORR_COSTS_CD, EFT_CROSSED_CHECK, EFT_CHECK_FWRD_CD, EFT_CHECK_DRAW_CD, EFT_PAY_INST_CD1, EFT_PAY_INST_CD2, EFT_PAY_INST_CD3, EFT_PAY_INST_CD4, EFT_PAY_INST_REF1, EFT_PAY_INST_REF2, COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, NUM1, NUM2, HOUSE_TYPE, ADDR_FIELD1, ADDR_FIELD2, ADDR_FIELD3, COUNTY, STATE, POSTAL, GEO_CODE, IN_CITY_LIMIT, BANK_SETID, BANK_CD, BANK_ACCT_KEY, PYMNT_METHOD, BANK_CD_OVR, BANK_ACCT_KEY_OVR, PYMNT_METHOD_OVR) SELECT  DISTINCT 13832, X.SETID, X.VENDOR_ID, X.VENDOR_SETID, A.VENDOR_NAME_SHORT, A.NAME1, A.NAME2, A.NAME1_AC, A.NAME2_AC, X.VNDR_LOC, ' ', ' ', ' ', A.REMIT_SETID, A.REMIT_VENDOR, C.PYMNT_HOLD, C.EFT_PYMNT_FMT_CD, C.EFT_RTNG_FLG, C.EFT_RTNG_STATUS, C.EFT_TRANS_HANDLING, C.EFT_DOM_COSTS_CD, C.EFT_CORR_COSTS_CD, C.EFT_CROSSED_CHECK, C.EFT_CHECK_FWRD_CD, C.EFT_CHECK_DRAW_CD, C.EFT_PAY_INST_CD1, C.EFT_PAY_INST_CD2, C.EFT_PAY_INST_CD3, C.EFT_PAY_INST_CD4, C.EFT_PAY_INST_REF1, C.EFT_PAY_INST_REF2, D.COUNTRY, D.ADDRESS1, D.ADDRESS2, D.ADDRESS3, D.ADDRESS4, D.CITY, D.NUM1, D.NUM2, D.HOUSE_TYPE, D.ADDR_FIELD1, D.ADDR_FIELD2, D.ADDR_FIELD3, D.COUNTY, D.STATE, D.POSTAL, D.GEO_CODE, D.IN_CITY_LIMIT, E.SETID, C.BANK_CD, C.BANK_ACCT_KEY, C.PYMNT_METHOD, ' ', ' ', ' ' FROM PS_EX_EXP_MTHD_TBL X , PS_VENDOR A , PS_VENDOR_LOC B, PS_VENDOR_PAY C, PS_VENDOR_ADDR D, PS_SET_CNTRL_REC E WHERE
X.EXPEND_MTHD_EDIT = 'VND' AND X.EFF_STATUS = 'A' AND X.EFFDT=( SELECT MAX(X1.EFFDT) FROM PS_EX_EXP_MTHD_TBL X1 WHERE X.SETID = X1.SETID AND X.EXPEND_MTHD = X1.EXPEND_MTHD AND X1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND X.VENDOR_SETID = A.SETID AND X.VENDOR_ID = A.VENDOR_ID AND A.SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND E.SETCNTRLVALUE = A.SETID AND E.RECNAME = 'BANK_ACCT_TBL' AND X.VNDR_LOC = B.VNDR_LOC AND B.EFFDT=( SELECT MAX(B1.EFFDT) FROM PS_VENDOR_LOC B1 WHERE B1.SETID = B.SETID AND B1.VENDOR_ID = B.VENDOR_ID AND B1.VNDR_LOC = B.VNDR_LOC AND B1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND B.EFF_STATUS='A' AND C.PYMNT_HOLD='N' AND C.EFF_STATUS='A' AND C.SETID=B.SETID AND C.VENDOR_ID=B.VENDOR_ID AND C.VNDR_LOC=B.VNDR_LOC AND C.EFFDT=( SELECT MAX(C1.EFFDT) FROM PS_VENDOR_PAY C1 WHERE C1.SETID = C.SETID AND C1.VENDOR_ID= C.VENDOR_ID AND C1.VNDR_LOC = C.VNDR_LOC AND C1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND C.SEQ_NUM = ( SELECT MAX(C2.SEQ_NUM) FROM PS_VENDOR_PAY C2 WHERE C2.SETID = C.SETID AND
C2.VENDOR_ID = C.VENDOR_ID AND C2.VNDR_LOC = C.VNDR_LOC AND C2.EFFDT = C.EFFDT) AND A.SETID = D.SETID AND A.VENDOR_ID = D.VENDOR_ID AND X.ADDRESS_SEQ_NUM = D.ADDRESS_SEQ_NUM AND D.EFFDT=( SELECT MAX(D1.EFFDT) FROM PS_VENDOR_ADDR D1 WHERE D1.SETID = D.SETID AND D1.VENDOR_ID = D.VENDOR_ID AND D1.ADDRESS_SEQ_NUM = D.ADDRESS_SEQ_NUM AND D1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND D.EFF_STATUS='A'
     Process 13832 ABENDED at Step EX_STAGE_PMT.WORKTBLS.VND_PMT (Action SQL) -- RC = 805
     ROLLBACK
     SQL Error: ORA-00001: unique constraint (EMDBO.PS_EXVNDPMT_TAO4) violated  "

BUSINESS IMPACT:

The Stage Payments program will always end up in abend due to the codeline delivered and not being prepared to handle multiple Payment Types being defined in the system for the same Vendor ID but different Location/Address combination. This prevents approved Expense Report transactions from being properly staged for payment, and thus eventually paid by the company, to reimburse both Employees and Credit Card Vendors.

EXPECTED BEHAVIOR:

No matter what are the defined Payment Types in the system, the codeline should always be able to identify the Payment Type value used in the processed Expense transactions, and make the proper defaults and updates to the affected items, so that at all times the further handling of transactions can be done without problems.

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