My Oracle Support Banner

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

To view full details, 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 a vibrant support community of peers and Oracle experts.