Symptoms

• GL Journal Import fails or produces out-of-balance journals for receiving transactions processed through the India localization (JAI) module.
• GL_INTERFACE rows for the affected receipt numbers with USER_JE_CATEGORY_NAME = 'Receiving India' and USER_JE_SOURCE_NAME = 'Purchasing India' have a NULL ACCOUNTED_CR (debit-only rows with no matching credit).
• JAI_RCV_JOURNAL_ENTRIES rows for the same receipt numbers have a NULL ENTERED_CR, causing the entered amounts to be unbalanced.
• The receipts cannot be fully accounted or transferred to GL without correcting the missing credit amounts.

Root Cause

During India localization receiving journal entry generation, the credit-side amounts (ACCOUNTED_CR in GL_INTERFACE and ENTERED_CR in JAI_RCV_JOURNAL_ENTRIES) were not populated for certain receipt transactions. The debit-side amounts (ACCOUNTED_DR / ENTERED_DR) are correct. The net effect is unbalanced journal entries that cannot be imported to GL. The fix sets the missing credit amounts equal to the existing debit amounts for the affected rows, restoring balance.

Identification SQL

Use the following queries to identify unbalanced rows:

-- GL Interface unbalanced rows
SELECT reference25, USER_JE_CATEGORY_NAME, USER_JE_SOURCE_NAME, JE_HEADER_ID, JE_LINE_NUM,
       SUM(NVL(ACCOUNTED_DR,0)) ACCOUNTED_DR,
       SUM(NVL(ACCOUNTED_CR,0)) ACCOUNTED_CR
FROM   gl_interface
WHERE  USER_JE_CATEGORY_NAME = 'Receiving India'
  AND  USER_JE_SOURCE_NAME   = 'Purchasing India'
GROUP BY reference25, USER_JE_CATEGORY_NAME, USER_JE_SOURCE_NAME, JE_HEADER_ID, JE_LINE_NUM
HAVING SUM(NVL(ACCOUNTED_DR,0)) <> SUM(NVL(ACCOUNTED_CR,0));

-- JAI journal entries unbalanced rows
SELECT transaction_id, RECEIPT_NUM, CATEGORY_NAME, SOURCE_NAME,
       SUM(NVL(ENTERED_DR,0)) ENTERED_DR,
       SUM(NVL(ENTERED_CR,0)) ENTERED_CR
FROM   jai_rcv_journal_entries
WHERE  category_name = 'Receiving India'
  AND  source_name   = 'Purchasing India'
GROUP BY transaction_id, RECEIPT_NUM, CATEGORY_NAME, SOURCE_NAME
HAVING SUM(NVL(ENTERED_DR,0)) <> SUM(NVL(ENTERED_CR,0));

Resolution

Run data_fix.sql. For each unbalanced row group in GL_INTERFACE (where ACCOUNTED_DR <> ACCOUNTED_CR), update ACCOUNTED_CR = ACCOUNTED_DR on the rows where ACCOUNTED_DR IS NULL (i.e., the credit-only side that is missing its debit, or debit-only rows needing a credit). The cursor groups rows by receipt reference, journal header, line number, category, and source, then updates the NULL credit amount to match the debit.

For each unbalanced row group in JAI_RCV_JOURNAL_ENTRIES (where ENTERED_DR <> ENTERED_CR), update ENTERED_CR = ENTERED_DR on the rows where ENTERED_DR IS NULL (the rows missing the credit amount). The cursor groups by transaction ID, receipt number, category, and source.

Commit the changes. Re-run the GL Journal Import for the Purchasing India source to import the now-balanced receiving journals to GL.

Script Inventory

Key Tables Affected