EBI 9.2: The Billing Interface Program Abends at Step BIIF0001.NEWHDRS.UPDSTCST as It Cannot Place NULL into PS_INTFC_BI_HTMP5.SHIP_TO_ADDR_NUM (Doc ID 2762154.1)

Last updated on NOVEMBER 27, 2021

Applies to:

Symptoms

The Billing Interface process (BIIF0001 AE Program) abends at Step BIIF0001.NEWHDRS.UPDSTCST with Error Type ORA-01407 when unable to place a NULL value into Record Field PS_INTFC_BI_HTMP5.SHIP_TO_ADDR_NUM.

This is taking place after a Contact User has deleted what had been the Primary Billing Address (Bill To, Ship To, and Sold To) for a Customer, via the CUSTOMER Portal in eBill Payment module, after defining a new Address as the Primary one. This leaves the new Customer Address as the Primary Bill To, but not for Ship To, Sold To, nor Correspondence Address. As such, the old deleted Address still has the Primary flag selected on Ship To and Sold To, and Correspondence Address selected, but has a Status of INACTIVE.

When trying to then process Billing Interface IDs for that Customer, the system is unable to find a valid Ship To Address, and hence abends.

REPLICATION STEPS:

    1.- Have a Customer defined with one single Address Location, set as Primary for Bill To, Ship To, and Sold To
    2.- Have a Contact defined for this Customer
    3.- Get the Contact User log into the CUSTOMER Portal, and access the Account Settings under eBill Payment module
    4.- Click on Billing Address section, and confirm that the system lists the Customer's Address Location existing, and flagged as Primary
    5.- Have the User create a NEW Billing Address, with all needed fields (Address 1, Address 2, Postal, City, State, etc...), and mark it as PRIMARY
    6.- Confirm that the old existing Address is no longer defined as Primary. Open it, and click on DELETE button.
    7.- Save the changes at this stage
    8.- Log as User ID VP1 into the EMPLOYEE Portal, and navigate to the Customer General Information main page, to see that:
         a.- The old existing Address has a new Effective Date row with today's date, and a Status of INACTIVE. While this is expected, what is not, is that the Ship To and Sold To PRIMARY flags are still selected for this INACTIVE Address now
         b.- The new Address entered by the Contact has also an Effective Date row, which is correct, and a Status of ACTIVE, which is also correct, and the Bill To and Primary flags selected, which is also correct. However, the Ship To and Sold To Primary flags are NOT selected.
    9.- As a result, we have left a Customer with 2 Addresses, and the one defined for Ship To and Sold To Primaries is set as INACTIVE.
    10.- ISSUE: Hence, when running the BIIF0001 AE Program containing third party software transaction data to be processed, the system is unable to find a valid active Ship To Address, and abends the process at Step BIIF0001.NEWHDRS.UPDSTCST.

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

ERROR MESSAGE:

    " (BIIF0001.NEWHDRS.UPDSTCST) (SQL)
      Error Position: 405  Return: 1407 - ORA-01407: cannot update ("EMDBO"."PS_INTFC_BI_HTMP5"."SHIP_TO_ADDR_NUM") to NULL
      Failed SQL stmt: UPDATE PS_INTFC_BI_HTMP5   SET SHIP_TO_CUST_ID = (   SELECT SHIP_TO_CUST_ID   FROM PS_INTFC_BI_TAO5   WHERE PS_INTFC_BI_TAO5.INTFC_ID = PS_INTFC_BI_HTMP5.INTFC_ID   AND PS_INTFC_BI_TAO5.INTFC_LINE_NUM = PS_INTFC_BI_HTMP5.INTFC_LINE_NUM   AND PS_INTFC_BI_TAO5.PROCESS_INSTANCE = 123456789 AND PS_INTFC_BI_TAO5.TRANS_TYPE_BI = 'LINE'   AND PS_INTFC_BI_TAO5.TARGET_INVOICE = ' '   AND PS_INTFC_BI_TAO5.SHIP_TO_CUST_ID <> ' '), SHIP_TO_ADDR_NUM = (   SELECT SHIP_TO_ADDR_NUM   FROM PS_INTFC_BI_TAO5   WHERE PS_INTFC_BI_TAO5.INTFC_ID = PS_INTFC_BI_HTMP5.INTFC_ID   AND PS_INTFC_BI_TAO5.INTFC_LINE_NUM = PS_INTFC_BI_HTMP5.INTFC_LINE_NUM   AND PS_INTFC_BI_TAO5.PROCESS_INSTANCE = 123456789 AND PS_INTFC_BI_TAO5.TRANS_TYPE_BI = 'LINE'   AND PS_INTFC_BI_TAO5.TARGET_INVOICE = ' '   AND PS_INTFC_BI_TAO5.SHIP_TO_ADDR_NUM <> 0)   WHERE PROCESS_INSTANCE = 123456789 AND SHIP_TO_CUST_ID = ' '   AND EXISTS (   SELECT 'X'   FROM PS_INTFC_BI_TAO5   WHERE PROCESS_INSTANCE = 123456789 AND TRANS_TYPE_BI = 'LINE'   AND TARGET_INVOICE = ' '   AND SHIP_TO_CUST_ID <> ' '   AND PS_INTFC_BI_TAO5.INTFC_ID = PS_INTFC_BI_HTMP5.INTFC_ID   AND PS_INTFC_BI_TAO5.INTFC_LINE_NUM = PS_INTFC_BI_HTMP5.INTFC_LINE_NUM)
      Process 123456789 ABENDED at Step BIIF0001.NEWHDRS.UPDSTCST (SQL) -- RC = 1407 (108,524) "

Needed interfaced Billing Invoices are not being created successfully, and as such, further collection of customer's payments and further accounting are being delayed.

The BIIF0001 AE Program should not abend in such a manner, and instead, should place the Interface ID and its affected Line transactions into a Status of ERROR, stating that the Ship To Address cannot be found, or is invalid.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.