undefine v_headerinfo
Define   v_headerinfo =  '$Header: DATAFIX_80896A.sql 1.0  28-APR-2016 support $'
undefine v_testlongname
Define   v_testlongname = 'AP Accounting Date Mismatch Data Fix for Case 80896'

REM   ==================================================================================
REM   Created By: William Green
REM   ==================================================================================
REM   PURPOSE:           This script will correct the data mismatch issues in the AP tables
REM   PRODUCT(S):        AP
REM   PRODUCT VERSIONS:  11.5
REM   PLATFORM:          Generic
REM   PARAMETERS:        None 
REM   ==================================================================================


REM   ==================================================================================
REM   USAGE:     sqlplus apps/password @DATAFIX_80896A.sql
REM   EXAMPLE: 
REM   OUTPUT:   The script will create an output file in text format named 
REM                  DATAFIX_80896A.txt. Open the file in a text editor and 
REM                  review the output
REM   ==================================================================================


REM   ==================================================================================
REM   CHANGE HISTORY:

REM   23-SEP-2016   William Green  Created
REM     
REM   ==================================================================================

REM  ================SQL PLUS Environment setup=========================================
set serveroutput on size 1000000
set verify off
set feedback off
set linesize 132
set head on
set pagesize 100

REM ============== Define SQL Variables for input parameters ===========================
clear columns

REM ============= Accept other Input Parameters ========================================

set serveroutput on verify off linesize 132 

-- Setup system

set serveroutput on verify off linesize 120 head off
set null 'Not Defined'

/*
--Undefine Statements

undefine p_variable1

--setting variables

--prompt
accept p_variable1 prompt 'Enter the variable1 value: '
--prompt
*/

--getting vendor sites 
PROMPT Invoices to Be Corrected:
set heading on
set lines 1000
COLUMN INVOICE_NUM FORMAT A50
COLUMN CHECK_NUMBER FORMAT A50

select INVOICE_NUM 
from RSI_AP_INV_DIST_80896;

PROMPT
PROMPT Checks to Be Corrected:

SELECT TO_CHAR(CHECK_NUMBER) CHECK_NUMBER
from RSI_AP_INV_PAY_80896
UNION
select TO_CHAR(CHECK_NUMBER) CHECK_NUMBER
from RSI_AP_INV_PAY_HIST_80896;

set heading off

REM ============ Spooling the output file===============================================
Define v_spoolfilename  = 'DATAFIX_80896A._OUTPUT.txt'

PROMPT
PROMPT Running.....
PROMPT 
spool  &v_spoolfilename

REM =================Run the Pl/SQL api file ===========================================
--@@CoreApiTxt.sql

DECLARE 

v_user_id        	NUMBER := -9999;
v_date            	DATE := sysdate;

l_sql_debug      	varchar2(1000);
l_block_debug    	varchar2(1000) := 'Declaration';
l_tot_cnt       	number := 0;
l_succ_cnt        	number := 0;
l_err_cnt        	number := 0;

CURSOR c1
  IS     
    select * from RSI_AP_INV_DIST_80896;
	
CURSOR c2
  IS     
    select * from RSI_AP_INV_PAY_80896;
	
CURSOR c3
  IS     
    select * from RSI_AP_INV_PAY_HIST_80896;
	
BEGIN

    dbms_output.put_line(chr(9));
    l_block_debug := 'Beginning of the AP Date Mismatch Correction Script';
    dbms_output.put_line('Beginning of the AP Date Mismatch Correction Script');
    dbms_output.put_line(chr(9));
    dbms_output.put_line(chr(9));

    /*
	l_block_debug := 'Deleting error records';
    dbms_output.put_line('Deleting error records');

    DELETE FROM rsi_[ERROR_TABLE_NAME];
    COMMIT;

    dbms_output.put_line(chr(9));
    dbms_output.put_line(chr(9));
	*/

    l_block_debug := 'Starting loop through records';
    dbms_output.put_line('Starting loop through records');
    dbms_output.put_line(chr(9));

    --Invoice Distributions Loop 
    FOR c1rec IN c1 LOOP     
        BEGIN
            l_block_debug := 'Processing Invoice Distribution ID: '||c1rec.INVOICE_DISTRIBUTION_ID;
            dbms_output.put_line('Processing Invoice Distribution ID: '||c1rec.INVOICE_DISTRIBUTION_ID);
            dbms_output.put_line(chr(9));
            
            update AP_INVOICE_DISTRIBUTIONS_ALL set 
			accounting_date = c1rec.XE_ACCOUNTING_DATE,
			period_name = c1rec.xe_period_name
			where INVOICE_DISTRIBUTION_ID = c1rec.INVOICE_DISTRIBUTION_ID;
			
			l_succ_cnt := l_succ_cnt + 1;
        
            EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK;
                dbms_output.put_line(chr(9));
                dbms_output.put_line('ERROR: An error occurred processing Invoice Distribution ID: '||c1rec.INVOICE_DISTRIBUTION_ID||' at block '||l_block_debug||' proceeding to next record');
                dbms_output.put_line('ERROR MESSAGE: '||sqlerrm);
                dbms_output.put_line('Proceeding to next record');
                dbms_output.put_line('===========================');
                l_err_cnt := l_err_cnt + 1;  
                dbms_output.put_line(chr(9));
        END; 

    END LOOP;

    --Invoice Payment Loop 
    FOR c2rec IN c2 LOOP     
        BEGIN
            l_block_debug := 'Processing Invoice Payment ID: '||c2rec.INVOICE_PAYMENT_ID;
            dbms_output.put_line('Processing Invoice Payment ID: '||c2rec.INVOICE_PAYMENT_ID);
            dbms_output.put_line(chr(9));
            
            update AP_INVOICE_PAYMENTS_ALL set 
			accounting_date = c2rec.XE_ACCOUNTING_DATE,
			period_name = c2rec.xe_period_name
			where INVOICE_PAYMENT_ID = c2rec.INVOICE_PAYMENT_ID;
			
			l_succ_cnt := l_succ_cnt + 1;
        
            EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK;
                dbms_output.put_line(chr(9));
                dbms_output.put_line('ERROR: An error occurred processing Invoice Payment ID: '||c2rec.INVOICE_PAYMENT_ID||' at block '||l_block_debug||' proceeding to next record');
                dbms_output.put_line('ERROR MESSAGE: '||sqlerrm);
                dbms_output.put_line('Proceeding to next record');
                dbms_output.put_line('===========================');
                l_err_cnt := l_err_cnt + 1;  
                dbms_output.put_line(chr(9));
        END; 

    END LOOP;

    --Invoice Payment History Loop 
    FOR c3rec IN c3 LOOP     
        BEGIN
            l_block_debug := 'Processing Invoice Payment History ID: '||c3rec.payment_history_id;
            dbms_output.put_line('Processing Invoice Payment History ID: '||c3rec.payment_history_id);
            dbms_output.put_line(chr(9));
            
            update AP_PAYMENT_HISTORY_ALL set 
			accounting_date = c3rec.XE_ACCOUNTING_DATE
			where payment_history_id = c3rec.payment_history_id;
			
			l_succ_cnt := l_succ_cnt + 1;
        
            EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK;
                dbms_output.put_line(chr(9));
                dbms_output.put_line('ERROR: An error occurred processing Invoice Payment History ID: '||c3rec.payment_history_id||' at block '||l_block_debug||' proceeding to next record');
                dbms_output.put_line('ERROR MESSAGE: '||sqlerrm);
                dbms_output.put_line('Proceeding to next record');
                dbms_output.put_line('===========================');
                l_err_cnt := l_err_cnt + 1;  
                dbms_output.put_line(chr(9));
        END; 

    END LOOP;

    dbms_output.put_line(chr(9));
    l_block_debug := 'Completed loop through records';
    dbms_output.put_line('Completed loop through records');
    dbms_output.put_line(chr(9));

    dbms_output.put_line(chr(9));
    l_block_debug := 'Printing totals';
    dbms_output.put_line('Total successful records: '||l_succ_cnt);
    dbms_output.put_line('Total unsuccessful records: '||l_err_cnt);
    l_tot_cnt := l_succ_cnt + l_err_cnt;
    dbms_output.put_line('Total processed records: '||l_tot_cnt);
    dbms_output.put_line(chr(9));

    dbms_output.put_line(chr(9));
    l_block_debug := 'End of the AP Date Mismatch Correction Script';
    dbms_output.put_line('End of the AP Date Mismatch Correction Script');
    dbms_output.put_line(chr(9));

EXCEPTION 
WHEN OTHERS THEN
    ROLLBACK;
      dbms_output.put_line(chr(9));
      dbms_output.put_line(sqlerrm ||' error occurred in Block: '||l_block_debug||'<br>for SQL: '||l_sql_debug);
      dbms_output.put_line('Please report the above error to Support');
      dbms_output.put_line(chr(9));    

END;
/

REM  ==============SQL PLUS Environment setup=======================================================
Spool off
set term on
/*
PROMPT
PROMPT Printing Errors:

SET heading ON
CLEAR COLUMNS
SET LINESIZE 450
COLUMN [COLUMN_NAME] FORMAT A20
[SELECT_ERROR_TABLE_SQL]
SET heading OFF
PROMPT
*/

PROMPT
PROMPT    ************      NOTE: THE PROCEDURE IS NOT COMPETE      *************
PROMPT  ============================================================================================
PROMPT  Please review the output file:  &v_spoolfilename
PROMPT    All inserted records are identified by LAST_UPDATED_BY and CREATED_BY
PROMPT  set to -9999 and CREATION_DATE and LAST_UPDATE_DATE fields set to SYSDATE
PROMPT  ============================================================================================
PROMPT