EAR 9.2: Payment Predictor AE Program Abends At AR_PREDICT2.PGEN.BLD_W10 As It Is Unable To Insert A NULL Value Into Record Field PS_PAYMENT_ITEM.STX_TAX_AUTH_RATE

(Doc ID 2406326.1)

Last updated on MAY 31, 2018

Applies to:

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

Symptoms

ISSUE:

After completing a system upgrade with individual fixes extracted from PeopleSoft Enterprise FSCM 9.2 Image #27, whenever the Functional Users launch Payment Predictor AE Program (ARPREDCT Job), this one abends in Error Message.

Upon some analysis, it seems that delivered Record PS_PAYMENT_ITEM has been modified with the addition of the below two new Fields by <Bug 27366601> (SUP: PAYMENT_ITEM DATA MODEL CHANGE FOR ORDER NO AS PR ITEM REFERENCE FOR GST):

        - Field STX_TAX_AUTH_RATE
        - Field STX_TAX_RATE_CD

REPLICATION STEPS:

     1.- Upgrade the FSCM 9.2 environment with some fixes from Image #27
     2.- Create AR Items, and post them into the Customer's Account
     3.- Define a new Deposit, with a Payment referencing the Items, and select 'Payment Predictor' flag
     4.- Launch Payment Predictor
     5.- The process abends in Error Message

ERROR MESSAGE:

     " -- 16:20:59.197 ....(AR_PREDICT2.PGEN.BLD_W10) (SQL)
       INSERT INTO PS_PAYMENT_ITEM (DEPOSIT_BU, DEPOSIT_ID, PAYMENT_SEQ_NUM, BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE, AR_SEQ_NUM, ITEM_SELECTED, PAY_AMT, BAL_AMT, ORIG_BAL_AMT, DISC_AMT, DISC_ORIG_AMT, DISC_TAKEN, DISC_STATUS, WS_SEQ, DISC_ACTION, ENTRY_TYPE, ENTRY_REASON,ENTRY_EVENT, REF_REASON, DUE_DT, ENTRY_USE_ID, DOCUMENT, DISC_AMT1, DISC_DT, DISC_DT1, ACCOUNTING_DT, DST_ID_AR, DISC_DAYS, WO_AMT, ALLOW_DISC, DISC_AVAIL, SUBCUST_QUAL1, SUBCUST_QUAL2, PO_REF, PO_LINE, BILL_OF_LADING, PYMNT_TERMS_CD, ASOF_DT, ITEM_ENTRY_TYPE, ITEM_ENTRY_REASON, WO_ENTRY_TYPE, WO_ENTRY_REASON, WO_ENTRY_USE_ID, ITEM_ACCTG_DT, BAL_CURRENCY, BAL_AMT_BASE, RATE_MULT, RATE_DIV, PAY_AMT_ITEM, RT_TYPE_NEW, RATE_MULT_NEW, RATE_DIV_NEW, CURRENCY_CD, PAY_AMT_BASE, REAL_GAIN_LOSS, DISC_REAL_GAINLOSS, DST_ID_REAL, WO_AMT_BASE, DISC_BASE, WO_AMT_PAY, DISC_PAY, ORDER_NO, CONTRACT_NUM, PAYMENT_CURRENCY, VAT_ENTITY, VAT_CALC_GROSS_NET, VAT_EXCPTN_TYPE, VAT_EXCPTN_CERTIF, VAT_DCLRTN_POINT, VAT_RECALC_FLG, COUNTRY_VAT_BILLFR, COUNTRY_VAT_BILLTO, COUNTRY_SHIP_TO, COUNTRY_SHIP_FROM, VAT_TREATMENT_GRP, VAT_RGSTRN_BUYER, VAT_ROUND_RULE, NEW_WS_ITEM, DD_BU, DD_ID, DD_CURRENCY, DD_PROFILE_ID, DD_STATUS, DRAFT_TYPE, CR_ANALYST, COLLECTOR, SALES_PERSON, SALES_PERSON2, REGION_CD, DOUBTFUL, REVALUE_FLAG, DRAFT_BUSN_EVENT, DRAFT_SUB_EVENT, VAT_ADVPAY_FLG, VAT_BASIS_AMT, VAT_BASIS_AMT_BASE, VAT_AMT, VAT_AMT_BASE, VAT_TRANS_AMT,PACKSLIP_NO, SBI_NUM, LC_ID , ITM_PAY_AMT_BASE , MRL_STATUS , MRL_TAX_AMT , PPRC_PROMO_CD , CLAIM_NO,CONTROL_CURRENCY , CONTROL_CURR_BASE , CONTROL_RATE_MULT , CONTROL_RATE_DIV , CONTROL_RT_TYPE, DISC_PAY_AMT_BASE,AR_SPECIALIST , BROKER_ID , CARRIER_ID , CLAIM_DT , CLASS_OF_TRADE , DEDUCTION_STATUS , DEDUCTION_DT , DIVISION , DT_INVOICED , INV_PROD_FAM_CD , INVOICE , INVOICE_BU , MAJOR_CLASS , MERCH_TYPE , PROOF_OF_DELIVERY , SHIP_FROM_BU , SHIP_TO_ADDR_NUM , SHIP_TO_CUST_ID , SOLD_TO_ADDR_NUM , SOLD_TO_CUST_ID, DISPUTE_STATUS, DISPUTE_DT, DISPUTE_AMOUNT , COLLECTION_STATUS, PHYSICAL_NATURE , COUNTRY_LOC_BUYER , STATE_LOC_BUYER , COUNTRY_LOC_SELLER , STATE_LOC_SELLER , VAT_SVC_SUPPLY_FLG , VAT_SERVICE_TYPE , COUNTRY_VAT_PERFRM , STATE_VAT_PERFRM , COUNTRY_VAT_SUPPLY , STATE_VAT_SUPPLY , STATE_SHIP_FROM , STATE_SHIP_TO , VAT_RPT_CNTRY_SRC, STATE_VAT_DEFAULT, GL_LVL, CONVERS_EXISTS, ADDRESS_SEQ_NUM, AG_REF_NBR, INVOICE_DT, BUSINESS_UNIT_CA, CONTRACT_LINE_NUM, SP_ID, AR_TRAN_TYPE, ITEM_MSG_CD) SELECT DISTINCT X.DEPOSIT_BU , X.DEPOSIT_ID , X.PAYMENT_SEQ_NUM , X.BUSINESS_UNIT , X.CUST_ID , X.ITEM , X.ITEM_LINE , 0 , 'Y' , ((I.PAY_AMT) * ( -1)) , ((X.BAL_AMT) * ( -1)) , 0 , I.DISC_AMT , 0 , 0 , I.DISC_STATUS , X.GROUP_SEQ_NUM , 'N' , E.ENTRY_TYPE , E.PP_ENTRY_REASON , ' ' , ' ' , P.PAYMENT_DT , I.ENTRY_USE_ID , I.DOCUMENT , 0 , I.DISC_DT , I.DISC_DT1 , P.PAYMENT_DT , B.DST_ID_AR , 0 , 0 , 'N' , 0 , I.SUBCUST_QUAL1 , I.SUBCUST_QUAL2 , ' ' , 0 , ' ' , ' ' , P.PAYMENT_DT , E.ENTRY_TYPE , E.PP_ENTRY_REASON , ' ' , ' ' , ' ' , I.ACCOUNTING_DT , I.BAL_CURRENCY , ((I.BAL_AMT_BASE) * ( -1)) , I.RATE_MULT , I.RATE_DIV , ((I.BAL_AMT) * ( -1)) , P.PYMT_RT_TYPE , P.PYMT_RATE_MULT , P.PYMT_RATE_DIV , I.CURRENCY_CD , ((I.BAL_AMT_BASE) * ( -1)) , I.REAL_GAIN_LOSS , 0 , I.DST_ID_REAL , 0 , I.DISC_BASE , 0 , 0 , ' ' , ' ' , P.PAYMENT_CURRENCY , I.VAT_ENTITY , I.VAT_CALC_GROSS_NET , I.VAT_EXCPTN_TYPE , I.VAT_EXCPTN_CERTIF , I.VAT_DCLRTN_POINT , 'N' , I.COUNTRY_VAT_BILLFR , I.COUNTRY_VAT_BILLTO , I.COUNTRY_SHIP_TO , I.COUNTRY_SHIP_FROM , I.VAT_TREATMENT_GRP , I.VAT_RGSTRN_BUYER , ' ' , 'Y' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , I.REGION_CD , I.DOUBTFUL ,'Y' , ' ' , ' ' , 'N' , 0 , 0 , 0 , 0 , 0 , I.PACKSLIP_NO , I.SBI_NUM , I.LC_ID , CASE WHEN P.PAYMENT_CURRENCY = P.CURRENCY_CD AND P.PAYMENT_CURRENCY <> I.CURRENCY_CD THEN ((I.PAY_AMT) * ( -1)) WHEN P.PAYMENT_CURRENCY = P.CURRENCY_CD AND P.PAYMENT_CURRENCY = I.CURRENCY_CD THEN 0 WHEN P.PAYMENT_CURRENCY <> P.CURRENCY_CD THEN ROUND((((((((I.PAY_AMT) * ( -1))) / ( P.PYMT_RATE_DIV))) * ( P.PYMT_RATE_MULT))), :1) END , ' ' , 0 , ' ' , ' ' , P.PAYMENT_CURRENCY , P.CURRENCY_CD , P.PYMT_RATE_MULT , P.PYMT_RATE_DIV , P.PYMT_RT_TYPE , CASE WHEN P.PAYMENT_CURRENCY <> P.CURRENCY_CD THEN ROUND((((((I.DISC_PAY) / ( P.PYMT_RATE_DIV))) * ( P.PYMT_RATE_MULT))), 3) WHEN P.CURRENCY_CD <> I.BAL_CURRENCY THEN ROUND((((((I.DISC_EARNED) / ( I.RATE_DIV_NEW))) * ( I.RATE_MULT_NEW))), 3) WHEN P.CURRENCY_CD = I.BAL_CURRENCY THEN I.DISC_EARNED END , I.AR_SPECIALIST, ' ', ' ', NULL, ' ', I.DEDUCTION_STATUS, NULL, ' ', NULL,' ', ' ', ' ', ' ', ' ', ' ', ' ',0,' ',0,' ', I.DISPUTE_STATUS, I.DISPUTE_DT, I.DISPUTE_AMOUNT, ' ' , I.PHYSICAL_NATURE , I.COUNTRY_LOC_BUYER , I.STATE_LOC_BUYER , I.COUNTRY_LOC_SELLER , I.STATE_LOC_SELLER , I.VAT_SVC_SUPPLY_FLG , I.VAT_SERVICE_TYPE , I.COUNTRY_VAT_PERFRM , I.STATE_VAT_PERFRM , I.COUNTRY_VAT_SUPPLY , I.STATE_VAT_SUPPLY , I.STATE_SHIP_FROM , I.STATE_SHIP_TO, I.VAT_RPT_CNTRY_SRC, I.STATE_VAT_DEFAULT, 'N', ' ', I.ADDRESS_SEQ_NUM , I.AG_REF_NBR , NULL , ' ' , 0 , 0 , 'N' , ' ' FROM PS_PP_ITEM_TAO4 I , PS_PP_MATCH_TAO4 X , PS_AUTO_ENTRY_TBL E , PS_PP_PYMNT_TAO4 P , PS_BUS_UNIT_TBL_AR B WHERE (P.PROCESS_INSTANCE = 46622 AND X.PROCESS_INSTANCE = P.PROCESS_INSTANCE AND X.DEPOSIT_BU = P.DEPOSIT_BU AND X.DEPOSIT_ID = P.DEPOSIT_ID AND X.PAYMENT_SEQ_NUM = P.PAYMENT_SEQ_NUM AND P.PP_DISPOSITION = 'W' AND X.PROCESS_INSTANCE = I.PROCESS_INSTANCE AND X.DEPOSIT_BU = I.DEPOSIT_BU AND X.DEPOSIT_ID = I.DEPOSIT_ID AND X.PAYMENT_SEQ_NUM = I.PAYMENT_SEQ_NUM AND X.CUST_ID = I.CUST_ID AND X.ITEM = I.ITEM AND X.ITEM_LINE = I.ITEM_LINE AND I.ADJUSTMENT_REASON NOT IN ('OV', 'UN') AND I.ENTRY_REASON = ' ' AND I.ENTRY_USE_ID = 'WS-05' AND NOT (I.ITEM_SELECTED = 'Y' AND I.BAL_CURRENCY <> I.CURRENCY_CD AND I.PAYMENT_CURRENCY <> I.CURRENCY_CD) AND E.SETID = ( SELECT T.SETID FROM PS_SET_CNTRL_REC T WHERE T.SETCNTRLVALUE = I.BUSINESS_UNIT AND T.RECNAME = 'AUTO_ENTRY_TBL') AND E.ENTRY_USE_ID = I.ENTRY_USE_ID AND E.EFFDT = ( SELECT MAX(V.EFFDT) FROM PS_AUTO_ENTRY_TBL V WHERE V.SETID = E.SETID AND V.ENTRY_USE_ID = E.ENTRY_USE_ID AND V.EFFDT <= P.PAYMENT_DT AND V.EFF_STATUS = 'A') AND B.BUSINESS_UNIT = X.BUSINESS_UNIT) AND P.CURRENCY_CD = :2
       /
       -- 16:20:59.363 Process 46622 ABENDED at Step AR_PREDICT2.PGEN.BLD_W10 (Action SQL) -- RC = 1400
       ROLLBACK
       /
       -- 16:20:59.381 SQL Error: ORA-01400: cannot insert NULL into ("SYSADM"."PS_PAYMENT_ITEM"."STX_TAX_AUTH_RATE") "

ACTUAL RESULTS:

Payment Predictor fails at each run, and does not generate the needed Payment Worksheets, and Groups.

EXPECTED BEHAVIOR:

Payment Predictor should not abend in such manner because India GST functionality is not being used.

 

Changes

 

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