The purpose of this blog is to assist DBAs encountering ebs database custom table column missing while running adpatching cycle. The steps shared here will help to identify the table column missing that causes data loss issues in oracle database.
Below are the steps:
Issue:
While applying adpatch ADOP patching cycle, we have faced the below issue, while running adop phase full cleanup adop phase=cleanup cleanup_mode=full set a few columns to UNUSED for some custom tables in ebs database.
(“XXCUST”.”ATFMWF_UAT_HISTORY” , “XXCUST”.”ATFMWF_UAT_DATA” , “XXCUST”.”ATFM_UAT_HEADERS_T”)
548319553 10:09:20 00:00:00 ad.plsql.ad_zd_table.cleanup EVENT Cleanup unused columns
548319553 10:09:37 00:00:17 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFMWF_UAT_HISTORY” set unused (PRICE)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFMWF_UAT_HISTORY” set unused (SESSION_NO)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFMWF_UAT_HISTORY” set unused (SUBSIDIARY_ORG)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFMWF_UAT_HISTORY” set unused (TRF_PRICE_UPDATION)548319553 10:09:38
00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFMWF_UAT_DATA” set unused (LAST_WF_DATE)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFMWF_UAT_DATA” set unused (LAST_WF_PR)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFMWF_UAT_DATA” set unused (LAST_WF_PRICE)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFMWF_UAT_DATA” set unused (LAST_WF_VENDOR)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFM_UAT_HEADERS_T” set unused (ATTRIBUTE1)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFM_UAT_HEADERS_T” set unused (ATTRIBUTE2)
548319553 10:09:38 00:00:00 ad.plsql.ad_zd_table.cleanup STATEMENT SQL: alter table “XXCUST”.”ATFM_UAT_HEADERS_T” set unused (SOURCE)
ISSUE:
Editioning view for table XXCUST.ATFMWF_UAT_HISTORY was not referring the columns that were set to UNUSED.
Whenever new columns are added we need to regenerate Editioning view (EV) using exec ad_zd_table.patch(‘XXCUST’,’ATFMWF_UAT_DATA’);
Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)
STEP-1:
SQL> desc XXCUST.ATFMWF_UAT_DATA
Name Null? Type
—————————————– ——– —————————-
PLAN_NAME VARCHAR2(30)
ORGANIZATION_CODE VARCHAR2(30)
ITEM_NAME VARCHAR2(60)
ITEM_BUYER VARCHAR2(60)
PLANNER VARCHAR2(30)
OA NUMBER
OA_VAL NUMBER
DESCRIPTION VARCHAR2(240)
PLANNING_MAKE_BUY_CODE VARCHAR2(30)
ALLOCATED_SUPPLY_QTY NUMBER
END_ITEM_USAGE NUMBER
SUPPLY_DUE_DATE DATE
SUPPLY_ORDER_NUMBER VARCHAR2(200)
SUPPLY_ORDER_TYPE VARCHAR2(60)
SUPPLIER VARCHAR2(100)
SUPPLIER_SITE VARCHAR2(500)
NEED_BY_DATE DATE
END_DEMAND_TYPE VARCHAR2(60)
END_DEMAND_ITEM VARCHAR2(60)
END_DEMAND_QTY NUMBER
END_DEMAND_ORDER_NUMBER VARCHAR2(100)
END_DEMAND_DATE DATE
END_DEMAND_CUSTOMER VARCHAR2(60)
DEMAND_ORDER_NUMBER VARCHAR2(100)
ORG_ID NUMBER
END_DEMAND_OA_LINE NUMBER
DEMAND_OA_LINE NUMBER
PROJECT_NUMBER VARCHAR2(25)
ISO_REF VARCHAR2(200)
INVENTORY_ITEM_ID NUMBER
USING_ASSEMBLY_ITEM_ID NUMBER
DEMAND_ID NUMBER
PEGGING_ID NUMBER
PREV_PEGGING_ID NUMBER
ONHAND_QTY_ASCP NUMBER
PROMISED_DATE DATE
IMMEDIATE_PARENT_CODE VARCHAR2(250)
ISO_REF_LINE_ID NUMBER
COMPRESS_DAYS NUMBER
SOURCE_ORGANIZATION_ID NUMBER
WF_DATE DATE
WFSTPROCESSING_LEAD_TIME NUMBER
PREPROCESSING_LEAD_TIME NUMBER
END_PEGGING_ID NUMBER
TRANSACTION_ID NUMBER
WF_LINE_LOCATION_ID NUMBER
WF_LINE_ID NUMBER
OA_LINE_ID NUMBER
DEPENDANT_DEMAND_QTY NUMBER
ISO_REF_ORG_ID NUMBER
SUGGESTED_START_DATE DATE
LAST_WF_PR VARCHAR2(100)
LAST_WF_VENDOR VARCHAR2(100)
LAST_WF_PRICE VARCHAR2(100)
STEP-2
The script “ADZDSHOWEV.sql” is a SQL script used in Oracle E-Business Suite’s (EBS) Application DBA (AD) utilities. It is typically executed within the context of the “adpatch” utility, which is used for applying patches and updates to an Oracle E-Business Suite environment and this script helps us on the re-work of the unused column in ebs database.
Then we need to execute the @ADZDSHOWEV.sql,this script will available in the below location
[oracle@uatapp sql]$ ll /oracle/uat/fs1/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWEV.sql-rwxr-xr-x. 1 oracle dba 2144 Nov 6 2021 /oracle/uat/fs1/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWEV.sql
SQL> @ADZDSHOWEV.sql
=========================================================================
= EV Column Mapping
=========================================================================
Enter value for 1: ATFMWF_UAT_DATA
VIEW_COLUMN -> TABLE_COLUMN
—————————— —- ——————————
PLAN_NAME = PLAN_NAME
ORGANIZATION_CODE = ORGANIZATION_CODE
ITEM_NAME = ITEM_NAME
ITEM_BUYER = ITEM_BUYER
PLANNER = PLANNER
OA = OA
OA_VAL = OA_VAL
DESCRIPTION = DESCRIPTION
PLANNING_MAKE_BUY_CODE = PLANNING_MAKE_BUY_CODE
ALLOCATED_SUPPLY_QTY = ALLOCATED_SUPPLY_QTY
END_ITEM_USAGE = END_ITEM_USAGE
SUPPLY_DUE_DATE = SUPPLY_DUE_DATE
SUPPLY_ORDER_NUMBER = SUPPLY_ORDER_NUMBER
SUPPLY_ORDER_TYPE = SUPPLY_ORDER_TYPE
SUPPLIER = SUPPLIER
SUPPLIER_SITE = SUPPLIER_SITE
NEED_BY_DATE = NEED_BY_DATE
END_DEMAND_TYPE = END_DEMAND_TYPE
END_DEMAND_ITEM = END_DEMAND_ITEM
END_DEMAND_QTY = END_DEMAND_QTY
END_DEMAND_ORDER_NUMBER = END_DEMAND_ORDER_NUMBER
END_DEMAND_DATE = END_DEMAND_DATE
END_DEMAND_CUSTOMER = END_DEMAND_CUSTOMER
DEMAND_ORDER_NUMBER = DEMAND_ORDER_NUMBER
ORG_ID = ORG_ID
END_DEMAND_OA_LINE = END_DEMAND_OA_LINE
DEMAND_OA_LINE = DEMAND_OA_LINE
PROJECT_NUMBER = PROJECT_NUMBER
ISO_REF = ISO_REF
INVENTORY_ITEM_ID = INVENTORY_ITEM_ID
USING_ASSEMBLY_ITEM_ID = USING_ASSEMBLY_ITEM_ID
DEMAND_ID = DEMAND_ID
PEGGING_ID = PEGGING_ID
PREV_PEGGING_ID = PREV_PEGGING_ID
ONHAND_QTY_ASCP = ONHAND_QTY_ASCP
PROMISED_DATE = PROMISED_DATE
IMMEDIATE_PARENT_CODE = IMMEDIATE_PARENT_CODE
ISO_REF_LINE_ID = ISO_REF_LINE_ID
COMPRESS_DAYS = COMPRESS_DAYS
SOURCE_ORGANIZATION_ID = SOURCE_ORGANIZATION_ID
WF_DATE = WF_DATE
WFSTPROCESSING_LEAD_TIME = WFSTPROCESSING_LEAD_TIME
PREPROCESSING_LEAD_TIME = PREPROCESSING_LEAD_TIME
END_PEGGING_ID = END_PEGGING_ID
TRANSACTION_ID = TRANSACTION_ID
WF_LINE_LOCATION_ID = WF_LINE_LOCATION_ID
WF_LINE_ID = WF_LINE_ID
OA_LINE_ID = OA_LINE_ID
DEPENDANT_DEMAND_QTY = DEPENDANT_DEMAND_QTY
ISO_REF_ORG_ID = ISO_REF_ORG_ID
SUGGESTED_START_DATE = SUGGESTED_START_DATE
STEP-3
After executing the below script the unused column in table ebs database will be now able to work.
SQL> exec ad_zd_table.patch(‘XXCUST’,’ATFMWF_UAT_DATA’);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> @ADZDSHOWEV.sql
=========================================================================
= EV Column Mapping
=========================================================================
Enter value for 1: ATFMWF_UAT_DATA
VIEW_COLUMN -> TABLE_COLUMN
—————————— —- ——————————
PLAN_NAME = PLAN_NAME
ORGANIZATION_CODE = ORGANIZATION_CODE
ITEM_NAME = ITEM_NAME
ITEM_BUYER = ITEM_BUYER
PLANNER = PLANNER
OA = OA
OA_VAL = OA_VAL
DESCRIPTION = DESCRIPTION
PLANNING_MAKE_BUY_CODE = PLANNING_MAKE_BUY_CODE
ALLOCATED_SUPPLY_QTY = ALLOCATED_SUPPLY_QTY
END_ITEM_USAGE = END_ITEM_USAGE
SUPPLY_DUE_DATE = SUPPLY_DUE_DATE
SUPPLY_ORDER_NUMBER = SUPPLY_ORDER_NUMBER
SUPPLY_ORDER_TYPE = SUPPLY_ORDER_TYPE
SUPPLIER = SUPPLIER
SUPPLIER_SITE = SUPPLIER_SITE
NEED_BY_DATE = NEED_BY_DATE
END_DEMAND_TYPE = END_DEMAND_TYPE
END_DEMAND_ITEM = END_DEMAND_ITEM
END_DEMAND_QTY = END_DEMAND_QTY
END_DEMAND_ORDER_NUMBER = END_DEMAND_ORDER_NUMBER
END_DEMAND_DATE = END_DEMAND_DATE
END_DEMAND_CUSTOMER = END_DEMAND_CUSTOMER
DEMAND_ORDER_NUMBER = DEMAND_ORDER_NUMBER
ORG_ID = ORG_ID
END_DEMAND_OA_LINE = END_DEMAND_OA_LINE
DEMAND_OA_LINE = DEMAND_OA_LINE
PROJECT_NUMBER = PROJECT_NUMBER
ISO_REF = ISO_REF
INVENTORY_ITEM_ID = INVENTORY_ITEM_ID
USING_ASSEMBLY_ITEM_ID = USING_ASSEMBLY_ITEM_ID
DEMAND_ID = DEMAND_ID
PEGGING_ID = PEGGING_ID
PREV_PEGGING_ID = PREV_PEGGING_ID
ONHAND_QTY_ASCP = ONHAND_QTY_ASCP
PROMISED_DATE = PROMISED_DATE
IMMEDIATE_PARENT_CODE = IMMEDIATE_PARENT_CODE
ISO_REF_LINE_ID = ISO_REF_LINE_ID
COMPRESS_DAYS = COMPRESS_DAYS
SOURCE_ORGANIZATION_ID = SOURCE_ORGANIZATION_ID
WF_DATE = WF_DATE
WFSTPROCESSING_LEAD_TIME = WFSTPROCESSING_LEAD_TIME
PREPROCESSING_LEAD_TIME = PREPROCESSING_LEAD_TIME
END_PEGGING_ID = END_PEGGING_ID
TRANSACTION_ID = TRANSACTION_ID
WF_LINE_LOCATION_ID = WF_LINE_LOCATION_ID
WF_LINE_ID = WF_LINE_ID
OA_LINE_ID = OA_LINE_ID
DEPENDANT_DEMAND_QTY = DEPENDANT_DEMAND_QTY
ISO_REF_ORG_ID = ISO_REF_ORG_ID
SUGGESTED_START_DATE = SUGGESTED_START_DATE
LAST_WF_PR = LAST_WF_PR
LAST_WF_VENDOR = LAST_WF_VENDOR
LAST_WF_PRICE = LAST_WF_PRICE
Thanks for visiting a blog, hope you would have fixed the issue you have faced using above steps.
