42Q-MES0191-A Oracle Adapter Uninstallation Guide

From 42Q
Jump to navigation Jump to search

42Q Home > Tools > Oracle Adapter Uninstallation Guide

 

 

 

Tools
Oracle Adapter Uninstallation Guide
Version MES15.80
      Revision A1      

 

 

 

Introduction

This guide is designed to help Oracle DBA and UNIX SysAdmin users execute the Oracle Apadapter uninstallation process and important instructions to back up the data before this process.

Pre-requisites

The following Pre-requisites condition has to be fulfilled:

  • Environment Setup
  • Custom Setup table and Index backup
  • Cancelation of Scheduled concurrent programs
  • Stopping the tomcat services
  • AOL objects and invalid XX42Q database object check
  • Verifying the Active sessions


Environment Setup

This action is intended for UNIX SysAdmin only, once this option is selected, follow the next steps: 

  1. Log in to the Unix Server.
  2. Ensure the environment setup is completed.
  3. Backup custom XX42Q_TOP:
  • Run the following command to backup custom XX42Q_TOP : tar -czvf /XXX/YYY/XX42Q_TOP.tgz $XX42Q_TOP.
  • Change the directory cd /XXX/YYY/ and ensure the XX42Q_TOP backup folder is created.

Custom Setup table and Index backup

Backup the custom tables and index values before the uninstallation process. It can be imported in case of re-installation of the XX42Q adapter.

 

Custom setup table backup

This action is intended for UNIX SysAdmin only, once this option is selected, follow the next steps: 

  1. Connect to the database.
  2. Export the data of the following custom setup tables as insert statements.
  • XX42Q_NG_CORRELATION
  • XX42Q_WO_ATTRIBUTE_LOOKUP 
  • XX42Q_MDSWIP_DEFAULT_PARAMS

       3. The following XX42Q adapter transaction table backups should also be performed.

  • XX42Q_DOF_PRE_SHIP_SERIALS
  • XX42Q_INBOUND_WS_XMLPAYLOADS
  • XX42Q_INBOUND_WS_XMLPAYLOADS_H
  • XX42Q_INTERFACE_STATUS
  • XX42Q_MDSWIP_WORKORDER_LOOKUPS
  • XX42Q_MDSWIP_WO_COMPONENTS
  • XX42Q_MDSWIP_WO_COMP_REF_DESG
  • XX42Q_MDSWIP_WO_COMP_SUBST
  • XX42Q_MDSWIP_WO_DETAILS
  • XX42Q_MDSWIP_WO_OUTBOUND
  • XX42Q_MDSWIP_WO_RESOURCES
  • XX42Q_MDSWIP_WO_STATUS
  • XX42Q_MDS_CUST_OMTYPE_LOOKUP
  • XX42Q_MDS_SHIP_DEFAULT
  • XX42Q_MES_LOT_SERIAL_INBOUND
  • XX42Q_NOTIFICATION_TRANS_DATA
  • XX42Q_OSI_API
  • XX42Q_OSI_API_MODULES
  • XX42Q_OSI_CONTROLS
  • XX42Q_SFDC_MTL_INBOUND
  • XX42Q_SFDC_MTL_STG
  • XX42Q_SFDC_SHIP_NOTE_HEADERS
  • XX42Q_SFDC_SHIP_NOTE_LINES
  • XX42Q_SFDC_TXN_ACK
  • XX42Q_SFDC_TXN_INBOUND
  • XX42Q_SFDC_TXN_STG
  • XX42Q_SFDC_WIP_SERIAL
  • XX42Q_SYSTEM_BUILD_ACK_REL
  • XX42Q_TRANS_DATA_DETAILS
  • XX42Q_WDJ_CHANGES
  • XX42Q_WIPINTF_EXTRACT
  • XX42Q_WO_NOTIFICATION_DATA
  • XX42Q_WO_NOTIFICATION_DETAILS

Sequence backup

  1. Take a backup of all the custom sequences belonging to the XX42Q adapter. 
  2. SELECT 'DECLARE i NUMBER; BEGIN LOOP SELECT' || sequence_name||'.nextval into i FROM dual; EXIT WHEN i >= '||last_number||' - 1 ; END LOOP; END;'FROM  dba_sequences WHERE sequence_name LIKE 'XX42Q%’

 

Cancelation of Scheduled concurrent programs

This action is intended for Oracle DBA/SysAdmin only, once this option is selected, follow the next steps: 

  1. Before executing the uninstallation script, all the 42Q custom concurrent programs have to be canceled.
  2. Also, users should ensure that no jobs are scheduled or kept on hold.
  3. Navigate to 42Q Admin(OUR) > View > Request responsibility used to schedule 42Q concurrent programs.
  4. Check for the following concurrent programs and cancel if found.
  • 42Q WIP Job Extract and Webservice Outbound
  • 42Q Shipment Notification Extract and Webservice Outbound
  • 42Q WIP Move and Material Issue Inbound Processing with ACK

Figure 1: Cancel request

Oracle cancelrequest.png

Stopping the tomcat services

This action is intended for Oracle DBA/SysAdmin only, once this option is selected, follow the next steps: 

  1. Execute the ./stop_tomcat.sh bash file to stop the tomcat service.
  2. Check the log to verify if the service has been stopped.

 

AOL objects and invalid XX42Q database object check

This action is intended for Oracle DBA/SysAdmin only, once this option is selected, follow the next steps: 

 

  1. Oracle Application Object Library (AOL): Collection of pre-built application components
  2. Before uninstalling, note all XX42Q AOL object counts and check for any invalid XX42Q object.
  3. Here is the list of XX42Q AOL Objects and database objects given:

                  a. 42Q AOL objects:

  • Menu
  • Request Group
  • Functions
  • Forms
  • Concurrent programs
  •  Lookups
  • Value Sets

                  b. Database Objects:

  • Packages
  • Functions
  • Triggers
  • Views
  • Synonyms
  • Queue Tables
  • Sequence
  • Custom Tables

     4. Query to check AOL Objects count 

 

S.No

OBJECT TYPE

QUERY

OBJECTS

OBJECT COUNT

1

Menu Objects

SELECT DISTINCT fme.menu_id ,fme.menu_name FROM fnd_menus fme WHERE 1 = 1 AND fme.menu_name LIKE 'XX42Q%';

XX42Q_ADMIN_MENU XX42Q_SUPPORT_MENU XX42Q_USER_MENU

3

2

Request Group

SELECT DISTINCT rg.request_group_name ,rg.application_id ,fat.application_name ,fa.application_short_name FROM fnd_request_groups rg ,fnd_request_group_units rgu ,fnd_concurrent_programs cp ,fnd_concurrent_programs_tl cpt ,fnd_application fa ,fnd_application_tl fat WHERE rg.request_group_id = rgu.request_group_id AND rgu.request_unit_id = cp.concurrent_program_id AND cp.concurrent_program_id = cpt.concurrent_program_id AND rg.application_id = fat.application_id AND fa.application_id = fat.application_id AND cpt.LANGUAGE = USERENV('LANG') AND fat.LANGUAGE = USERENV('LANG') AND rg.request_group_name LIKE 'XX42Q%';

XX42Q Request Group

1

3

Form Functions

SELECT DISTINCT fff.function_id ,fff.function_name FROM fnd_form_functions fff WHERE 1 = 1 AND fff.function_name LIKE 'XX42Q%';

XX42Q_API_DEF XX42Q_API_DEF_INQUIRY XX42Q_DEF_JOB XX42Q_DEF_JOB_INQUIRY XX42Q_MTL XX42Q_MTL_INQUIRY XX42Q_TRANS_DATA XX42Q_TRANS_DATA_INQUIRY XX42Q_WIP XX42Q_WIP_INQUIRY XX42Q_WIP_MRMDF XX42Q_WIP_MRMDF_INQUIRY XX42Q_WIP_OPMDF XX42Q_WIP_OPMDF_INQUIRY XX42Q_WO_ATTR_LKUP

18

4

Forms

SELECT DISTINCT form_id ,application_id ,form_name FROM fnd_form WHERE 1 = 1 AND form_name LIKE 'XX42Q%';

XX42Q_API_DEF XX42Q_DEF_JOB XX42Q_MTL XX42Q_TRANS_DATA XX42Q_WIP XX42Q_WIP_MRMDF XX42Q_WIP_OPMDF XX42Q_WO_ATTR_LKUP XX42Q_WO_OB

9

5

Concurrent Programs

SELECT DISTINCT conc.concurrent_program_name ,appl.application_short_name application ,exec.executable_name ,conc.concurrent_program_id ,lookup.meaning execution_method_code ,exec.execution_file_name ,exec.executable_id FROM fnd_concurrent_programs conc ,fnd_executables exec ,fnd_application appl ,fnd_lookup_values lookup WHERE exec.executable_id = conc.executable_id AND exec.executable_name LIKE 'XX42Q%' AND appl.application_id = exec.application_id AND lookup.lookup_code = exec.execution_method_code AND lookup.lookup_type = 'CP_EXECUTION_METHOD_CODE';

XX42Q_WIP_PROG XX42Q_WIP_EXTR XX42Q_OUTBOUND_SN XX42Q_WIPMOVE_MTL_TXN_PROCESS XX42Q_MTL_WRK XX42Q_MTL_ISSUE XX42Q_SN_EXTR_WS_OUTBOUND XX42Q_XML_VIEW XX42Q_SHIP_NOTE XX42Q_WIP_WK_PROG_N XX42Q_WIPDELTMP XX42Q_OUTBOUND XX42Q_WIP_WK_PROG XX42Q_WIP_EXTR_WS_OUTBOUND XX42Q_OUTBOUND_WMACK

15

6

Lookups

SELECT DISTINCT lookup_type ,security_group_id ,view_application_id ,lookup_code ,description FROM fnd_lookup_values WHERE 1 = 1 AND lookup_type LIKE 'XX42Q%';
 

XX42Q_RESUBMIT_TIMES

1

7

Value Sets

SELECT DISTINCT ffvs.flex_value_set_name FROM fnd_flex_value_sets ffvs ,fnd_flex_values ffv ,fnd_flex_values_tl ffvt WHERE ffvs.flex_value_set_name LIKE 'XX42Q%' AND ffvt.flex_value_id(+) = ffv.flex_value_id AND ffvs.flex_value_set_id = ffv.flex_value_set_id(+) AND ffvt.LANGUAGE(+) = 'US' AND ffv.enabled_flag(+) = 'Y';

XX42Q_HUB_LOCATION XX42Q_INTF_SYSTEM_CODE XX42Q_INTF_SYSTEM_TYPE XX42Q_WIP_DISCRETE_JOBS XX42Q_WIP_INTF_EXTRACT XX42Q_Yes_No

6

     5. Query to check Invalid database object: SELECT * FROM all_Objects WHERE object_name LIKE 'XX42Q%' AND Status ='INVALID'

Verifying the Active sessions

This action is intended for Oracle DBA/SysAdmin only, once this option is selected, follow the next steps: 

  1. Connect to Database. 
  2. Check for active sessions/locks and kill the active database sessions if found.

Uninstallation Execution

This action is intended for Oracle DBA/SysAdmin only, once this option is selected, follow the next steps: 

Run the script (XX42Q_DROP_SCRIPT_REORDERED.sh ) providing the below parameters:

  1. Apps database user name and password (APPS/XXXX).
  2. Custom Schema database user name and password (XX42Q/yyyy)
  3. Custom TOP: $XX42Q_TOP 
  4. Execute the drop script ./XX42Q_DROP_SCRIPT_REORDERED.sh

 

Post Uninstallation Check

This action is intended for Oracle DBA/SysAdmin only, once this option is selected, follow the next steps:

  1. After the uninstall process, validate the deletion of all 42Q AOL and XX42Q objects. 
  2. This uninstallation script does not delete the following custom objects.
  • 42Q Responsibilities - 42Q User(OUR), 42Q Admin(OUR), 42Q Support(OUR). It can be end-dated.
  • Value sets - XX42Q_INTF_SYSTEM_CODE, XX42Q_INTF_SYSTEM_TYPE cannot be deleted.

      3. List of 42Q AOL and Database objects.

                a. 42Q AOL objects:

  • Menu
  • Request Group
  • Functions
  • Forms
  • Concurrent programs
  •  Lookups
  • Value Sets

                  b. Database Objects:

  • Packages
  • Functions
  • Triggers
  • Views
  • Synonyms
  • Queue Tables
  • Sequence
  • Custom Tables
Retrieved from "https://42qrpt1.42-q.com/mes_wiki/index.php?title=42Q-MES0191-A_Oracle_Adapter_Uninstallation_Guide&oldid=49610"