India - Check Delivery OM/INV Interface ends in error "ORA-00001: unique constraint (JA.JAI_IRN_DETAILS_N1) violated" and CESS Tax (Unit Of Measure based) is causing incorrect data in JAI_IRN_DETAILS table. (Doc ID 2849361.1)

Last updated on FEBRUARY 14, 2022

Applies to:

Symptoms

PROBLEM STATEMENT

When attempting to Ship confirm,
the below error occurs in concurrent India - Check Delivery OM/INV Interface as well as JAI_IRN_DETAILS is populated with few columns incorrectly

ERROR
-----------------------
Delivery Id :XXXXXXX, Exception Entity :INVENTORY_INTERFACE, Error Message : ,Unexpected Error pi_trx_id-->XXXXXXXORA-00001: unique constraint (JA.JAI_IRN_DETAILS_N1) violated,Run India - Process Delivery OM/INV Interface program with parameter :XXXXXXXto rectify issue


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a Sales Order (N) > Oracle Order management > Orders,Returns > Sales Order
2. Populate the Header and Line details.
3. Navigate back to Header .Tools > India Tax Details.
4. Make sure to add two different tax types (Say for example IGST and CESS) (IGST is % Based and CESS is UOM(Unit Of Measure) based)
5. Book the order
6. Pick Release and Ship Confirm the same
7. Concurrent " India - Check Delivery OM/INV Interface" fires and completes in below error

Delivery Id :5137372, Exception Entity :INVENTORY_INTERFACE, Error Message : ,Unexpected Error pi_trx_id-->XXXXXXXORA-00001: unique constraint (JA.JAI_IRN_DETAILS_N1) violated,Run India - Process Delivery OM/INV Interface program with parameter :XXXXXXXto rectify issue

Also in JAI_IRN_DETAILS either the CESS or IGST value is only populated

a) When CESS amount is populated the columns UNIT_PRICE,TOTAL_AMOUNT,ASSESSABLE_AMOUNT ,TOTAL_ITEM_VALUE ,TOTAL_ASSESSABLE_VALUE,TOTAL_INVOICE_VALUE is calculated incorrectly
b) When IGST value is populated the columns TOTAL_ASSESSABLE_VALUE,TOTAL_INVOICE_VALUE is calculated incorrectly

Changes

Cause

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