JAI column issue

[VinodNN]

Hello Erman,

Getting the error - shown in the screenshot - in 12.2 instance. Could the following action plan have caused it?

create table JA.JAI_REPORTING_CODES_bkp2 as select * from JA.JAI_REPORTING_CODES;
update JA.JAI_REPORTING_CODES set REPORTING_CODE_DESCRIPTION#1=REPORTING_CODE_DESCRIPTION WHERE  REPORTING_CODE_DESCRIPTION#1 IS NULL AND REPORTING_CODE_DESCRIPTION IS NOT NULL;

commit;

create table JA.JAI_REPORTING_ASSOCIATIONS_bkp as select * from JA.JAI_REPORTING_ASSOCIATIONS;

update JA.JAI_REPORTING_ASSOCIATIONS set REPORTING_CODE_DESCRIPTION#1=REPORTING_CODE_DESCRIPTION WHERE  REPORTING_CODE_DESCRIPTION#1 IS NULL
AND    REPORTING_CODE_DESCRIPTION IS NOT NULL;

commit;

create table JA.JAI_RGM_RECOVERY_LINES_bkp2 as select * from JA.JAI_RGM_RECOVERY_LINES;

update JA.JAI_RGM_RECOVERY_LINES set DOCUMENT_NUMBER#1=DOCUMENT_NUMBER WHERE DOCUMENT_NUMBER#1 IS NULL AND DOCUMENT_NUMBER IS NOT NULL;

update JA.JAI_RGM_RECOVERY_LINES set ORGANIZATION_ID#1 =ORGANIZATION_ID WHERE ORGANIZATION_ID#1  IS NULL AND ORGANIZATION_ID IS NOT NULL;
commit;

create table JA.JAI_DET_FACTORS_INTERFACE_bkp as select * from JA.JAI_DET_FACTORS_INTERFACE;

UPDATE JA.JAI_DET_FACTORS_INTERFACE
SET    SHIP_FROM_STATE#1 = SHIP_FROM_STATE
WHERE  SHIP_FROM_STATE#1 IS NULL
AND    SHIP_FROM_STATE IS NOT NULL;

UPDATE JA.JAI_DET_FACTORS_INTERFACE
SET    SHIP_FROM_COUNTRY#1 = SHIP_FROM_COUNTRY
WHERE  SHIP_FROM_COUNTRY#1 IS NULL
AND    SHIP_FROM_COUNTRY IS NOT NULL;

UPDATE JA.JAI_DET_FACTORS_INTERFACE
SET    SHIP_TO_STATE#1 = SHIP_TO_STATE
WHERE  SHIP_TO_STATE#1 IS NULL
AND    SHIP_TO_STATE IS NOT NULL;

UPDATE JA.JAI_DET_FACTORS_INTERFACE
SET    SHIP_TO_COUNTRY#1 = SHIP_TO_COUNTRY
WHERE  SHIP_TO_COUNTRY#1 IS NULL
AND    SHIP_TO_COUNTRY IS NOT NULL;

UPDATE JA.JAI_DET_FACTORS_INTERFACE
SET    BILL_TO_STATE#1 = BILL_TO_STATE
WHERE  BILL_TO_STATE#1 IS NULL
AND    BILL_TO_STATE IS NOT NULL;

UPDATE JA.JAI_DET_FACTORS_INTERFACE
SET    BILL_TO_COUNTRY#1 = BILL_TO_COUNTRY
WHERE  BILL_TO_COUNTRY#1 IS NULL
AND    BILL_TO_COUNTRY IS NOT NULL;

If yes, how can I rectify it?

[ErmanArslansOracleBlog]

No, I don't think so. Your action plan only do the updates on the standard tables, it is not changing the table structure.

You re getting ORA-00904 for the column named invoice_reference_number.. So, it seems the underlying table lacks the invoice_reference_number column.

1 What is the form name? - is it custom or standard?
2 What else did you do? Any patching related with JAI
3 Do you have that column in the database? use dba_tab_columns if you don't know the table name..
4 Enable the forms trace and find the query, what is the query?
5 Are the grants and synonyms OK? APPS grants ..
6 Push the details button on the error message, what else does it say?..

Check these things.. I mean go this way.. It seems you have a code/SQL - table structure alignment issue.

[VinodNN]

Yes, we applied Poland localisation patches.

The column exists in the following tables:

JAI_TAX_DET_FACTORS
JAI_DET_FACTORS_INTERFACE
JAI_INTERFACE_LINES_ALL
JAI_TAX_DET_FACTORS_BKP
JAI_DET_FACTORS_INTERFACE_BKP
JAI_DET_FACTORS_INTERFACE#
JAI_TAX_DET_FACTORS#
JAI_INTERFACE_LINES_ALL#

Will get the trace and form name.

[VinodNN]

The form is - Tools - India Tax Details (Applying GST while booking Invoice).

The Details button shows - FRM-40505 Oracle error: Unable to perform query.

I enabled trace but no .trc file was produced in $FORMS_TRACE_DIR