VENDINVC Aborts With Error "ORA-01405:FETCHED COLUMN VALUE IS NULL" (Doc ID 2955359.1)

Last updated on JUNE 14, 2023

Applies to:

Symptoms

The batch VENDINVC aborts with error:

ERROR
-----------------------
vendinvc_1~YYYYMMDDHHMMSS~calculate_end_invoice_date~deal_actuals_item_loc~-1405~ORA-01405: fetched column value is NULL~Fetching c_get_end_invoice_date, deal_id XXXXXX, deal_detail_id 1

Steps to reproduce:

1. Create a BRB deal.
2. Add component to the deal.
3. Add threshold to the deal.
4. Add Item/Locations information.
5. Add deal income information.
6. Submit & approve the deal.
7. Create a N/B purchase order.
8. Add order details.
9. Apply the Deal to the order.
10. Submit & approve the order.
11. Ship the order from UI.
12. Receive the order from UI.
13. Create a RTV (return to vendor) from UI, where RTV quantity is greater than the PO quantity.
14. Approve & ship the RTV.
15. Change vdate to 30th of April.
16. Run vendinvc batch.
17. Notice that job prepost vendinvc pre won't insert the deal in vendinvc_temp table as est_next_invoice_date (30-04-2022) is not < next_eom_date (30-04-2022).
18. Notice that job prepost vendinvc post won't change est_next_invoice_date and last_invoice_date as est_next_invoice_date (30-04-2022) is not < next_eom_date (30-04-2022).
19. Run salmth and notice that the next_eom_date is updated to 28-05-2022.
20. Change vdate for 1st of May.
21. Run vendinvc batch and notice it aborts with "ORA-01405-FETCHED COLUMN VALUE IS NULL" error.
22. Notice that job prepost vendinvc pre inserts the deal in vendinvc_temp table as est_next_invoice_date (30-04-2022) is not < next_eom_date (28-05-2022).
23. Notice that job prepost vendinvc post will change est_next_invoice_date to 28-05-2022 and last_invoice_date to 30-04-2022 as est_next_invoice_date (30-04-2022) is not < next_eom_date (28-05-2022).

Actual result: The batch aborts with "ORA-01405-FETCHED COLUMN VALUE IS NULL" error one day after the change of EOM_DATE.

Cause

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