REM $Header: ap_reissue_fix.sql 120.0.12010000.6 2014/09/11 20:31:32 vinaik noship $ REM +=======================================================================+ REM +=======================================================================+ REM | FILENAME | REM | ap_reissue_fix.sql | REM | | REM | DESCRIPTION | REM | This Script will run undo accounting for all the events for all the | REM | checks selected as wrongly accounted through the patch application | REM | or execution ap_reissue_sel.sql | REM | Please have patches 8975671 , 9257606 and 9272615 applied before | REM | execution of this fix file | REM | | REM | SELECTION SCRIPT | REM | ap_reissue_fix.sql | REM | sqlplus /@ @ap_reissue_fix.sql | REM | | REM | HISTORY Created by William Green | REM +=======================================================================+ REM dbdrv:none SET VERIFY OFF; SET SERVEROUTPUT ON; WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; PROMPT ________________________________________________________ PROMPT PROMPT Following are the parameters that would be asked for PROMPT by the script, along with their meanings: PROMPT PROMPT P_User_Name: PROMPT ============ PROMPT A valid application user name. PROMPT PROMPT P_Responsibility_Name: PROMPT ====================== PROMPT A valid responsibility associated to the user PROMPT entered in case sensitive letters. PROMPT PROMPT Email_id: PROMPT ======== PROMPT A valid email id to get log output files in email. PROMPT This is an optional features, to make use of this feature please PROMPT Follow the Metalink Note 1582550.1 and pass valid email id. PROMPT PROMPT This feature can be ignored by not passing email id PROMPT (just by pressing enter key). PROMPT PROMPT ________________________________________________________ DECLARE l_file_location v$parameter.value%TYPE; l_message VARCHAR2(500); l_count NUMBER; l_count1 NUMBER; l_col_str VARCHAR2(5000); l_bug_no NUMBER := 9323907; l_user_name FND_USER.USER_NAME%TYPE; l_resp_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE; l_calling_sequence VARCHAR2(4000) := 'ap_reissue_fix.sql'; l_driver_table ALL_TABLES.TABLE_NAME%TYPE := 'AP_UNDO_CHECKS_9323907'; l_check_id AP_CHECKS_ALL.CHECK_ID%TYPE; l_where_clause LONG; c_del_adj SYS_REFCURSOR; c_check_adj SYS_REFCURSOR; l_adj_present NUMBER; l_instance_name VARCHAR2(100); l_host_name VARCHAR2(100); l_email_flag BOOLEAN DEFAULT FALSE; l_email_id VARCHAR2(255) := '&Email_id'; l_date VARCHAR2(100); BEGIN --------------------------------------------------------------------- -- Step 1: Set the Log and Output files --------------------------------------------------------------------- l_message := 'Set log and out files'; AP_Acctg_Data_Fix_PKG.Open_Log_Out_Files (9323907||'-fix', l_file_location); AP_Acctg_Data_Fix_PKG.Print(''); --------------------------------------------------------------------------- -- Step 1: Printing instance details --------------------------------------------------------------------------- BEGIN l_date := to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'); SELECT instance_name, host_name INTO l_instance_name, l_host_name FROM v$instance; AP_Acctg_Data_Fix_PKG.Print('Fix script executed on instance '|| l_instance_name||' host_name '||l_host_name||' on '|| l_date); EXCEPTION WHEN OTHERS THEN AP_Acctg_Data_Fix_PKG.Print('While querying instance name'); l_message := 'Exception :: '||SQLERRM||''; AP_Acctg_Data_Fix_PKG.Print(l_message); END; ------------------------------------------------------------------------ -- STEP 2: SET ORG CONTEXT ------------------------------------------------------------------------ BEGIN l_user_name := upper('&user_name'); l_resp_name := '&resp_name'; AP_ACCTG_DATA_FIX_PKG.apps_initialize (l_user_name, l_resp_name, l_calling_sequence); EXCEPTION WHEN OTHERS THEN l_message := 'Exception :: '|| SQLERRM || ' while setting org context '; FND_File.Put_Line(fnd_file.output,l_message); END; ---------------------------------------------------------------------------------- -- STEP 3: UPDATING POSTED_FLAG ON DRIVER TABLE ON BASIS OF TEMP DATA DRIVER TABLE ---------------------------------------------------------------------------------- Begin UPDATE ap_undo_checks_9323907 drv SET Process_flag = 'N' WHERE EXISTS (SELECT 1 FROM ap_temp_data_driver_9323907 tmp WHERE tmp.check_id = drv.check_id AND tmp.Process_flag = 'N' ); EXCEPTION WHEN OTHERS THEN l_message := 'Exception :: '|| SQLERRM || ' while updating process_flag of ap_undo_checks_9323907 '; FND_File.Put_Line(fnd_file.output,l_message); END; ------------------------------------------------------------------------ -- STEP 4: BULK UNDO CALL ------------------------------------------------------------------------ Begin Execute Immediate 'SELECT COUNT(*) from AP_UNDO_CHECKS_9323907 where Process_flag = ''Y'' ' into l_count; EXCEPTION WHEN OTHERS THEN l_message := 'Exception in selecting count from '|| 'AP_UNDO_CHECKS_9323907 for selecting affected records'|| SQLERRM ; AP_ACCTG_DATA_FIX_PKG.Print(l_message); END; IF (l_count > 0) THEN AP_ACCTG_DATA_FIX_PKG.Undo_Acctg_Entries (p_bug_no => l_bug_no, p_driver_table => l_driver_table, p_mode => 'UNDO_DEL_ADJ', p_calling_sequence => l_calling_sequence); END IF; ------------------------------------------------------------------------ -- STEP 5: UPDATE THE PROCESS_FLAG IN DRIVER TABLE -- ap_temp_data_driver_9323907 TO D ------------------------------------------------------------------------ IF (l_count > 0) THEN UPDATE ap_temp_data_driver_9323907 tmp SET process_flag = 'D' WHERE process_flag = 'Y' AND NOT EXISTS (SELECT 1 FROM AP_UNDO_CHECKS_9323907 undo WHERE undo.check_id = tmp.check_id AND undo.process_flag <> 'D' ); END IF; --------------------------------------------------------------------- -- Step 6: Taking Backup --------------------------------------------------------------------- BEGIN l_col_str := 'CHECK_ID,CHECK_DATE,CHECK_NUMBER,EVENT_ID,'|| 'EVENT_TYPE_CODE,ORG_ID,ACCTD_AMT,'|| 'CHECK_AMOUNT'; l_where_clause := ' WHERE process_flag = ''D'' '; AP_Acctg_Data_Fix_PKG.Backup_data('AP_TEMP_DATA_DRIVER_9323907', 'AP_CHECK_EVENTS_9323907_BKP', l_col_str, l_where_clause, 'ap_reissue_fix.sql' ); COMMIT; EXCEPTION WHEN OTHERS THEN l_message := 'Exception :: '|| SQLERRM || ' while backing up AP_TEMP_DATA_DRIVER_9323907'; FND_File.Put_Line(fnd_file.output,l_message); END; BEGIN l_col_str := 'CHECK_ID,EVENT_ID'; l_where_clause := ' WHERE process_flag = ''D'' '; AP_Acctg_Data_Fix_PKG.Backup_data('AP_UNDO_CHECKS_9323907', 'AP_UNDONE_CHECKS_9323907_BKP', l_col_str, l_where_clause, 'ap_reissue_fix.sql' ); COMMIT; EXCEPTION WHEN OTHERS THEN l_message := 'Exception :: '|| SQLERRM || ' while backing up AP_UNDO_CHECKS_9323907'; FND_File.Put_Line(fnd_file.output,l_message); END; --------------------------------------------------------------------- -- STEP 8: INFORM USER ABOUT BACKUP TABLE NAMES --------------------------------------------------------------------- Begin Execute Immediate 'SELECT COUNT(DISTINCT CHECK_ID) from AP_TEMP_DATA_DRIVER_9323907 '|| 'WHERE process_flag = ''D'' ' into l_count1; EXCEPTION WHEN OTHERS THEN l_message := 'Exception in selecting count from '|| 'AP_TEMP_DATA_DRIVER_9323907 for selecting count of fixed checks '|| SQLERRM ; AP_ACCTG_DATA_FIX_PKG.Print(l_message); END; IF (l_count1 > 0) THEN AP_Acctg_Data_Fix_PKG.Print('_______________________________________'|| '_______________________________________'); l_message := 'No of Distinct checks fixed is '||l_count1||'

'; AP_Acctg_Data_Fix_PKG.Print(l_message); l_message := 'Following is the backup table created

'; AP_Acctg_Data_Fix_PKG.Print(l_message); l_message := 'AP_CHECK_EVENTS_9323907_BKP contains all the checks data and '|| 'the corresponding event_id which was wrongly accounted as identified by '|| 'by the GDF when applied

'; AP_Acctg_Data_Fix_PKG.Print(l_message); l_message := 'AP_UNDONE_CHECKS_9323907_BKP contains the check_id and the list of'|| 'events which were undo accounted successfully by the fix file of this '|| 'GDF patch

'; AP_Acctg_Data_Fix_PKG.Print(l_message); l_message := '_______________________________________'|| '_______________________________________'; AP_Acctg_Data_Fix_PKG.Print(l_message); ELSE l_message := '_______________________________________'|| '_______________________________________'; AP_Acctg_Data_Fix_PKG.Print(l_message); l_message := 'No Data to be corrected '; AP_Acctg_Data_Fix_PKG.Print(l_message); l_message := '_______________________________________'|| '_______________________________________'; AP_Acctg_Data_Fix_PKG.Print(l_message); END IF; AP_Acctg_Data_Fix_PKG.Print(''); AP_Acctg_Data_Fix_PKG.Close_Log_Out_Files; IF (l_email_id IS NOT NULL) THEN l_email_flag := AP_Acctg_Data_Fix_PKG.send_mail_clob_impl(l_email_id,l_file_location); END IF; IF (l_email_flag) THEN dbms_output.put_line ('Email Sent'); ELSE dbms_output.put_line ('Email Sending Failed'); END IF; dbms_output.put_line('--------------------------------------------------'|| '-----------------------------'); dbms_output.put_line(l_file_location||' is the log file created'); dbms_output.put_line('--------------------------------------------------'|| '-----------------------------'); EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -20001 THEN l_message := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM|| ' in '||l_calling_sequence; AP_ACCTG_DATA_FIX_PKG.Print(l_message); END IF; AP_Acctg_Data_Fix_PKG.Print(''); AP_ACCTG_DATA_FIX_PKG.Close_Log_Out_Files; IF (l_email_id IS NOT NULL) THEN l_email_flag := AP_Acctg_Data_Fix_PKG.send_mail_clob_impl(l_email_id,l_file_location); END IF; IF (l_email_flag) THEN dbms_output.put_line ('Email Sent'); ELSE dbms_output.put_line ('Email Sending Failed'); END IF; dbms_output.put_line('--------------------------------------------------'|| '-----------------------------'); dbms_output.put_line(l_file_location||' is the log file created'); dbms_output.put_line('--------------------------------------------------'|| '-----------------------------'); END; / COMMIT; EXIT;