EAP 9.2: AP_FG_PROC AE Program Fails At Step AP_FG_PROC.SENDPMT.Step01 When Pay Cycle Is Processing Expense Report Transactions Under Certain Conditions

(Doc ID 2318518.1)

Last updated on OCTOBER 17, 2017

Applies to:

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

Symptoms

ISSUE:

Accounts Payable Payment to Financial Gateway process (AE Program AP_FG_PROC) is failing when running for Expense Report related Pay Cycle. This happens when between Payment Creation (AP_APY2015) and Financial Gateway Electronic Payments (AP_FG_PROC) the Expense Processing AE Program (EX_TRAN_PRCS) is run twice with the below options selected:

     - Stage Payments = Y
     - Post Liabilities = Y
     - Post Payments = Y
     - Advance Reconciliation = Y
     - Update Paid Statuses = Y

It appears the issue is occurring due to a corresponding row for the Expense Report being paid missing from PS_EX_SUMM_PYMNT. In PeopleSoft Enterprise FSCM 9.2 Application Release, AP_PYMT_FG now joins to that Record for Expense related Payments in LoadStg2.Step02. It appears Expense Report transactions will not always be in this table.

REPLICATION STEPS:

     1.- Set up an External Bank Account, so that Automatic Clearing House (ACH) is set to be paid via Financial Gateway
     2.- Ensure at the Expenses Profile level, under Bank Account details, that an Employee has the default Payment Method of ACH
     3.- Create, submit, approve, and stage for payment a new Expense Report for the Employee at hand
     4.- Run Payment Selection on a new Pay Cycle targeting the ACH Expenses transaction
     5.- Launch Payment Creation, so that a new ACH Payment is created
     6.- Ensure that the Pay Cycle is Approved
     7.- Define a new Run Control ID for AE Program EX_TRAN_PRCS with the options of stage payments, post liabilities, post payments, advance reconciliation, and update paid statuses
     8.- Launch the EX_TRAN_PRCS process twice to success
     9.- Go back to Pay Cycle Manager, and launch the AP_FG_PROC program to send the generated Payment into Financial Gateway
     10.- The AP_FG_PROC abends in Error Message

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:

     " SQL error. Function: Record.Insert
       Error Position: 0
       Return: 1400 - ORA-01400: cannot insert NULL into ("EMDBO"."PS_PMT_DETAIL_STG"."PYMNT_DT")
      Statement: INSERT INTO PS_PMT_DETAIL_STG (PMT_MSG_ID,PMT_SEQ_NUM,PMT_SOURCE,BUSINESS_UNIT,SRC_REF_ID,AMOUNT,AMOUNT_CURRENCY_CD,PMT_STATUS,PMT_TYPE,BANK_SETID,BANK_CD,BANK_ACCT_KEY,FROM_BNK_ID_NBR,FROM_BNK_ACCT_NUM,FROM_DFI_ID_NUM,
FROM_DFI_ID_QUAL,PYMNT_METHOD,FORMAT_ID,PRENOTE_FLAG,PAY_IMMEDIATELY,COUNTRY,BANK_ID_QUAL,BNK_ID_NBR,BRANCH_ID,BANK_ACCT_TYPE,BANK_ACCOUNT_NUM,CHECK_DIGIT,DFI_ID_QUAL,DFI_ID_NUM,BANK_NAME,BANK_NAME_AC,
BRANCH_DESCR,BRANCH_NAME_AC,IBAN_CHECK_DIGIT,IBAN_ID,PARTY_ID_TYPE,PARTY_SETID,PARTY_ID,NAME1,NAME1_AC,NAME2,STD_ID_NUM,STD_ID_NUM_QUAL,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,ADDENDA_VALUE,
PMT_FIELD1,PMT_FIELD2,PMT_FIELD3,REPETITVE_TRNFR_CD,DRAWDOWN_FLG,CREATED_BY_USER,PAY_CYCLE,PAY_CYCLE_SEQ_NUM,BATCH_NAME,IN_PROCESS_FLG,PROCESS_INSTANCE,MANDATE_BU,PMT_MANDATE_ID,SEQ_NUM,CAN_LAYOUT,
PMT_REASON,CAN_REASON,ORG_PMT_ID,RVL_LAYOUT,RVL_REASON,SEQUENCE_TYPE,CD,PRTRY) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,:69,:70,:71,:72,:73,:74,:75,:76,:77,:78,
:79,:80,:81,:82)

       Fatal SQL error occurred. (2,125) PMT_LOAD.Payment.PaymentHandler.OnExecute Name:InsertRowsetAndChildren PCPC:8187 Statement:129
       Called from:PMT_LOAD.Payment.PaymentHandler.OnExecute Name:InsertRowsetAndChildren Statement:135
       Called from:PMT_LOAD.Payment.PaymentHandler.OnExecute Name:ValidateAndInsert Statement:315
       Called from:PMT_LOAD.Payment.PaymentHandler.OnExecute Name:ProcessSyncPayment Statement:15
       Called from:PMT_LOAD.Interface.PaymentRequestSync.OnExecute Name:Process Statement:7

       Process 124786 ABENDED at Step AP_FG_PROC.SENDPMT.Step01 (PeopleCode) -- RC = 24 (108,524)
       ROLLBACK "

ACTUAL RESULTS:

Payment information cannot be interfaced into Financial Gateway, which can delay the reimbursement of Employees, and Credit Card Vendors, with its subsequent penalty fees for delayed payments.

EXPECTED BEHAVIOR:

The Payables to Financial Gateway AE Program (AP_FG_PROC) should be able to successfully process all transactions no matter their originating module.

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