jgzzvatupgutidt.sql fails on 12.1.1 upgrade patch 6678700 application

Error:  sqlplus -s APPS/***** @/R12PI/apps/appl/jg/12.0.0/patch/115/sql/jgzzvatupgutidt.sql &un_jg &batchsize 6 30
           WHERE stg.ledger_id NOT IN (SELECT ledger_id FROM jg_zz_vat_upg_stg_tmp)
                                                             *
ERROR at line 90:
ORA-06550: line 90, column 62:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 73, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 108, column 10:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 104, column 13:
PL/SQL: SQL Statement ignored

Solution  The table jg_zz_vat_upg_stg_tmp is a simple temp table so, please try to create that table via DDL script ( Executing adodfcmp against jgjgzx.odf) and continue with the paused patch 6678700 adpatch session.


adodfcmp utility: 

The ODF Comparison utility is used to compare the datamodel of a customer’s data to a standard set of data model files from the current Oracle Apps release. It can optionally modify the database to match the standard data model.

Whenever we apply the oracle application patches,patches supply odf's file and adpatch run odf comparison utlity to make the changes in the database.

There is an object descriptor file (ODF) describing the tables, views, indexes, sequences and privilege sets for the particular building block.ODF Comparison compares the building block to the object descriptor file (ODF). A log file is created showing any missing, extra, or incorrectly defined objects as shown in below example


Example/Syntax :
 /R12PI/apps/appl/jg/12.0.0/patch/115/odf
adodfcmp odffile=jgjgzx.odf userid=jg/jg mode=tables touser=apps/****** logfile=jgjgzx.log priv_schema=system/*******

Log file will be as below :

*******************************************************
The database is missing the table JG_ZZ_VAT_UPG_STG_TMP.
Create it with the statement(s):

CREATE TABLE JG.JG_ZZ_VAT_UPG_STG_TMP (LEDGER_ID NUMBER(15) NOT NULL,
 COUNTRY_CODE VARCHAR2(2) NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY
 NUMBER(15) NOT NULL, LAST_UPDATED_BY NUMBER(15) NOT NULL, LAST_UPDATE_DATE
 DATE NOT NULL, LAST_UPDATE_LOGIN NUMBER(15)) STORAGE(INITIAL 4K NEXT 32K
 MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4
 FREELISTS 4 ) PCTFREE 10 PCTUSED 60 INITRANS 10 MAXTRANS 255

CREATE UNIQUE INDEX JG.JG_ZZ_VAT_UPG_STG_TMP_U1 ON JG.JG_ZZ_VAT_UPG_STG_TMP
 (LEDGER_ID) LOGGING STORAGE (INITIAL 4K NEXT 128K MINEXTENTS 1 MAXEXTENTS
 UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10
 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS

Schema is "JG", SQL statement is:
GRANT ALL ON JG.JG_ZZ_VAT_UPG_STG_TMP TO APPS WITH GRANT OPTION
[did not execute above statement -- Mode changedb = NO]

CREATE OR REPLACE SYNONYM APPS.JG_ZZ_VAT_UPG_STG_TMP FOR
 JG.JG_ZZ_VAT_UPG_STG_TMP

ODF Comparison Utility is complete.
***********************************************************

Connect to sqlplus with apps user and execute above scripts , restart the failed adworker

SQL> CREATE TABLE JG.JG_ZZ_VAT_UPG_STG_TMP (LEDGER_ID NUMBER(15) NOT NULL,
  2   COUNTRY_CODE VARCHAR2(2) NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY
  3   NUMBER(15) NOT NULL, LAST_UPDATED_BY NUMBER(15) NOT NULL, LAST_UPDATE_DATE
  4   DATE NOT NULL, LAST_UPDATE_LOGIN NUMBER(15)) STORAGE(INITIAL 4K NEXT 32K
  5   MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4
  6   FREELISTS 4 ) PCTFREE 10 PCTUSED 60 INITRANS 10 MAXTRANS 255
  7  /

Table created.

SQL> CREATE UNIQUE INDEX JG.JG_ZZ_VAT_UPG_STG_TMP_U1 ON JG.JG_ZZ_VAT_UPG_STG_TMP
  2   (LEDGER_ID) LOGGING STORAGE (INITIAL 4K NEXT 128K MINEXTENTS 1 MAXEXTENTS
 UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10
  3    4   INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  5  /

Index created.

SQL> CREATE OR REPLACE SYNONYM APPS.JG_ZZ_VAT_UPG_STG_TMP FOR
 JG.JG_ZZ_VAT_UPG_STG_TMP
  2    3  /

Synonym created.


Ref : Patch 6678700 fails running Jgzzvatupgutidt.sql script With Ora-00942 On Jg_zz_vat_upg_stg_tmp [ID 1421163.1]