REM $Header: ap_prepay_dist_id_pop_fix.sql 120.0.12010000.3 2013/09/30 06:57:00 lyanduru noship $
REM +===============================================================================+
REM +===============================================================================+
REM * File: ap_prepay_dist_id_pop_fix.sql                                           *
REM * Bug Number: 9243855                                                           *
REM * Issue: Prepay_distribution_id is not populated on AP_INVOICE_DISTRIBUTIONS_ALL*
REM *        for the tax distributions created as part of prepay application.       *
REM *                                                                               *
REM * Description: This script will fix the transactions identified by              *
REM *              ap_prepay_dist_id_pop_sel.sql                                    *
REM *              Fix would do the following: -                                    *
REM *               - populate the prepay_distribution_id on tax distributions of   *
REM *                 prepay application/unapplication distribution lines.          *
REM *               - populate the prepay_invoice_id and prepay_line_number on the  *
REM *                 exclusive tax lines associated to a prepay application        *
REM *                 /unapplication line.                                          *
REM *               - Delete and recreate the prepay_app_dists for new accounting   *
REM *                                                                               *
REM * NOTES:                                                                        *
REM *  ap_prepay_dist_id_pop_sel.sql need to be run in order                        *
REM *  identify the invoices.                                                       *
REM *  Script will create back-up tables for all the records that will be           *
REM *  affected by this script.                                                     *
REM *  The process_flag will be stamped 'P' for records that are completed          *
REM *  successfully and 'E' for errored records  in driver table                    *
REM *  AP_TEMP_DATA_DRIVER_9243855                                                  *
REM *                                                                               *
REM *  Script ap_prepay_dist_id_pop_fix.sql need to be run prior to this script.    *
REM *                                                                               *
REM * RCA  : 9243854                                                                *
REM *                                                                               *
REM * Execute command line :                                                        *
REM * sqlplus <user_name>/<pwd>@<database>@ap_prepay_dist_id_pop_fix.sql            *
REM * HISTORY                                                                       *
REM *  Created by William Green           on 20-AUG-2010                                   *
REM *********************************************************************************
REM dbdrv: none
SET VERIFY OFF
SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

PROMPT Following parameters are required to execute this script
PROMPT 1. User Name  Format Eg.CBROWN
PROMPT 2. Responsibility Format Eg. Payables Manager
PROMPT 3. Email Id, if Email needs to be sent of the logs
PROMPT 
ACCEPT CONTEND PROMPT 'Review the above messages and press <Enter> to acknowlege.'

DECLARE
         l_count                       NUMBER;
         l_message                     VARCHAR2( 4000 );
         l_dists_col_str               VARCHAR2( 10000 );
         l_dists_where_str             VARCHAR2( 10000 );
         l_success_flag                VARCHAR2( 1 );
         l_file_location               v$parameter.value%TYPE;
		 l_table_name                  VARCHAR2( 100 ) ;
         l_calling_sequence            VARCHAR2( 100 ) := 'ap_prepay_dist_id_pop_fix';
         l_resp_id                     NUMBER;
         l_user_id                     NUMBER;
	 l_user_name                   VARCHAR2(20):='&l_user_name'; /*Bug 17412439*/
         l_resp_name                   VARCHAR2(2000):='&l_resp_name'; /*Bug 17412439*/
	 l_email_id VARCHAR2(255) := '&Email_id';/*Bug 17412439*/
	 l_email_flag          BOOLEAN DEFAULT FALSE;/*Bug 17412439*/
	 TYPE  Tab_Number IS TABLE OF AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE INDEX BY BINARY_INTEGER;
	 l_inv_dist_id_tab             TAB_NUMBER;
	 l_acct_event_id_tab           TAB_NUMBER;
	 l_bc_event_id_tab             TAB_NUMBER;
         TYPE T_ORG_TYP IS TABLE OF AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID%TYPE INDEX BY BINARY_INTEGER;
	 l_org_id_tab                  T_ORG_TYP;
	 TYPE T_INV_TYP IS TABLE OF AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID%TYPE INDEX BY BINARY_INTEGER;
	 l_invoice_id_tab              T_INV_TYP;
	 l_prev_org_id                 AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID%TYPE;
	 CUST_EXCEPTION                EXCEPTION;
	 l_instance_name        VARCHAR2(100); /*Bug 17412439*/
	 l_host_name            VARCHAR2(100); /*Bug 17412439*/
	 l_success                     VARCHAR2(10) ;

BEGIN

-----------------------------------------------------------------------------
  -- Initialize the log file
  -----------------------------------------------------------------------------
  l_calling_sequence := ' Generic Data Fix Script (ap_prepay_dist_id_pop_fix.sql ) ';

  AP_Acctg_Data_Fix_PKG.Open_Log_Out_Files( 9243855||'-fix', l_file_location );
  AP_Acctg_Data_Fix_PKG.Print( '<html><body>' );
  l_message:='querying instance details';
  
    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 '||
       to_char(sysdate,'DD-MON-YYYY HH:MI:SS'));
   
  AP_Acctg_Data_Fix_PKG.Print('Username provided :'||l_user_name);  /*Bug 17412439*/
  AP_Acctg_Data_Fix_PKG.Print('Responsibility provided :'||l_resp_name);  /*Bug 17412439*/

  -----------------------------------------------------------------------------
  -- Added following code to initialize apps for bug 17412439
  -----------------------------------------------------------------------------
  l_message:= 'initializing apps for access and verfication of user details <p>';
  AP_Acctg_Data_Fix_PKG.Print( l_message );

  AP_Acctg_Data_Fix_PKG.apps_initialize(UPPER(l_user_name),l_resp_name,l_calling_sequence);   /*Bug 17412439*/

		 
         l_message := 'Start of ap_prepay_dist_id_pop_fix <p>';
         AP_Acctg_Data_Fix_PKG.Print( l_message );
  -----------------------------------------------------------------------------
  -- Backup Transaction data before upgrade
  -----------------------------------------------------------------------------

	 l_message := 'Backup transaction data before update - Start';
         AP_Acctg_Data_Fix_PKG.Print( l_message );

         /* Backup invoice lines table. */

	 l_message := 'Backup - AP_INVOICE_LINES_ALL';
         AP_Acctg_Data_Fix_PKG.Print( l_message );

         l_dists_where_str := 'where invoice_id in '
				|| '(select invoice_id from AP_TEMP_DATA_DRIVER_9243855 '
				|| 'where process_flag = ''Y'')';
					
         AP_Acctg_Data_Fix_PKG.get_cols( 'AP_INVOICE_LINES_ALL', 'AP', l_dists_col_str );
                  
	 AP_Acctg_Data_Fix_PKG.Backup_data
			( 'AP_INVOICE_LINES_ALL',
			'AP_INV_LINES_9243855_BKP',
			l_dists_col_str, 
			l_dists_where_str,
			'ap_prepay_dist_id_pop_fix.sql' );
				
         l_message := 'Backup successfully taken into table AP_INV_LINES_9243855_BKP <p>';
         AP_Acctg_Data_Fix_PKG.Print( l_message );

         -- Backup invoice distribution table

	 l_message := 'Backup - ap_invoice_distributions_all';
         AP_Acctg_Data_Fix_PKG.Print( l_message );

         l_dists_where_str := 'where invoice_distribution_id in '
				|| '(select invoice_distribution_id from AP_TEMP_DATA_DRIVER_9243855 '
				|| 'where process_flag = ''Y'')';
					
         AP_Acctg_Data_Fix_PKG.get_cols( 'AP_INVOICE_DISTRIBUTIONS_ALL', 'AP', l_dists_col_str );
                  
	 AP_Acctg_Data_Fix_PKG.Backup_data
			( 'AP_INVOICE_DISTRIBUTIONS_ALL',
			'AP_INV_DISTS_9243855_BKP',
			l_dists_col_str, 
			l_dists_where_str,
			'ap_prepay_dist_id_pop_fix.sql' );
				
         l_message := 'Backup successfully taken into table AP_INV_DISTS_9243855_BKP <p>';
         AP_Acctg_Data_Fix_PKG.Print( l_message );

	 l_message := 'Backup transaction data before update - End';
         AP_Acctg_Data_Fix_PKG.Print( l_message );


               BEGIN
	                 Merge into AP_TEMP_DATA_DRIVER_9243855 drv
			    using
			     (Select distinct CONNECT_BY_ROOT aid.invoice_distribution_id "ROOT",
			             aid.invoice_id,
				     aid.invoice_line_number,
				     aid.dist_code_combination_id,
				     aid.set_of_books_id,
			             aid.org_id,
				     aid.tax_code_id,
				     DECODE(aid.line_type_lookup_code,
				            'REC_TAX','REC_TAX',
					    'NONREC_TAX','NONREC_TAX',
					    'PREPAY') line_type_lookup_code,
				     aid.invoice_distribution_id
			      from ap_invoice_distributions_all aid,
				   AP_TEMP_DATA_DRIVER_9243855 t
			     where aid.invoice_id = t.prepay_invoice_id
				start with aid.charge_applicable_to_dist_id is null         
				connect by NOCYCLE prior aid.invoice_distribution_id = aid.charge_applicable_to_dist_id) t_vw
			   on (drv.root = t_vw.root
			   and drv.tax_code_id = t_vw.tax_code_id
			   and DECODE(drv.line_type_lookup_code, 
			               'TAX', DECODE(drv.tax_recoverable_flag,'N','NONREC_TAX',
				                                              'Y','REC_TAX',
									      'TAX'),
				drv.line_type_lookup_code) 
			           = t_vw.line_type_lookup_code
			   and drv.dist_code_combination_id = t_vw.dist_code_combination_id
			   and drv.org_id = t_vw.org_id
			   and drv.set_of_books_id = t_vw.set_of_books_id)
                       when matched then update
		        set drv.prepay_distribution_id = t_vw.invoice_distribution_id,
			    drv.prepay_invoice_id = t_vw.invoice_id,
			    drv.prepay_line_number = t_vw.invoice_line_number,
			    drv.error_message = null,
			    drv.process_flag = 'P'
		      where drv.prepay_distribution_id is null
		        and drv.process_flag = 'Y';

         l_message := ' Merge into driver table AP_TEMP_DATA_DRIVER_9243855 updated:'||sql%rowcount ||' records.';
         AP_Acctg_Data_Fix_PKG.Print( l_message );

	                 Update AP_TEMP_DATA_DRIVER_9243855 drv
		           set drv.process_flag = 'P',
		               drv.error_message = null
		         where drv.process_flag = 'Y'
		           and drv.prepay_distribution_id is not null
			   and not exists(SELECT 'no updated tax dist'
			              FROM AP_TEMP_DATA_DRIVER_9243855 drv1
				      WHERE drv.root = drv1.root
				      AND   drv.invoice_id = drv1.invoice_id
				      AND   drv1.process_flag <> 'P'
				      AND   drv1.line_type_lookup_code = 'TAX');

         l_message := ' DML on driver table AP_TEMP_DATA_DRIVER_9243855 updated:'||sql%rowcount ||' records.';
         AP_Acctg_Data_Fix_PKG.Print( l_message );

                         Merge into AP_INVOICE_DISTRIBUTIONS_ALL aid
			    using AP_TEMP_DATA_DRIVER_9243855 t
			    on (aid.old_distribution_id = t.invoice_distribution_id
			        and t.process_flag = 'P')
			  when matched then update
			   set aid.prepay_distribution_id = t.prepay_distribution_id,
			   aid.last_updated_by= FND_GLOBAL.user_id,       /*Added for bug 17412439*/
             		   aid.last_update_login= '-17412439',
	                   aid.last_update_date= sysdate
			where aid.prepay_distribution_id is null;

         l_message := ' Merge into actual table AP_INVOICE_DISTRIBUTIONS_ALL updated:'||sql%rowcount ||' records.';
         AP_Acctg_Data_Fix_PKG.Print( l_message );

	                 Merge into AP_INVOICE_LINES_ALL ail
			     using (select DISTINCT 
			                   aid.invoice_id,
				           t.prepay_invoice_id,
					   t.prepay_line_number,
					   aid.invoice_line_number
				      from ap_invoice_distributions_all aid,
					   AP_TEMP_DATA_DRIVER_9243855 t
				   where aid.old_distribution_id = t.invoice_distribution_id
				     and t.process_flag = 'P') t_vw
					on (ail.invoice_id = t_vw.invoice_id
					and ail.line_number = t_vw.invoice_line_number)
				   when matched then update
					set ail.prepay_invoice_id = t_vw.prepay_invoice_id,
					    ail.prepay_line_number = t_vw.prepay_line_number,
						ail.last_updated_by= FND_GLOBAL.user_id,       /*Added for bug 17412439*/
						ail.last_update_login= '-17412439',
						ail.last_update_date= sysdate
				      where ail.prepay_invoice_id is null;

        l_message := ' Merge into actual table AP_INVOICE_LINES_ALL updated:'||sql%rowcount ||' records.';
        AP_Acctg_Data_Fix_PKG.Print( l_message );

	  l_success := 'Y';

        l_message := ' l_success is Y';
        AP_Acctg_Data_Fix_PKG.Print( l_message );

         EXCEPTION
	   WHEN OTHERS THEN
	    
             ROLLBACK;
	     l_success := 'N';
              l_message := 'Error occurred in the batch process is : ' || SQLERRM;
              AP_Acctg_Data_Fix_PKG.Print( l_message );

         END;


     IF l_success = 'N' THEN

      l_message := 'l_success flag is N and the data processing in loop';
      AP_Acctg_Data_Fix_PKG.Print( l_message );

        FOR i IN (SELECT DISTINCT invoice_id FROM AP_TEMP_DATA_DRIVER_9243855 where process_flag = 'Y')
	LOOP

	  BEGIN
			 Merge into AP_TEMP_DATA_DRIVER_9243855 drv
			    using
			     (Select distinct CONNECT_BY_ROOT aid.invoice_distribution_id "ROOT",
			             aid.invoice_id,
				     aid.invoice_line_number,
				     aid.dist_code_combination_id,
				     aid.set_of_books_id,
			             aid.org_id,
				     aid.tax_code_id,
				     DECODE(aid.line_type_lookup_code,
				            'REC_TAX','TAX',
					    'NONREC_TAX','TAX',
					    'PREPAY') line_type_lookup_code,
				     aid.invoice_distribution_id
			      from ap_invoice_distributions_all aid,
				   AP_TEMP_DATA_DRIVER_9243855 t
			     where aid.invoice_id = t.prepay_invoice_id
				start with aid.charge_applicable_to_dist_id is null         
				connect by NOCYCLE prior aid.invoice_distribution_id = aid.charge_applicable_to_dist_id) t_vw
			   on (drv.root = t_vw.root
			   and drv.invoice_id = i.invoice_id
			   and drv.tax_code_id = t_vw.tax_code_id
			   and drv.line_type_lookup_code = t_vw.line_type_lookup_code
			   --and drv.dist_code_combination_id = t_vw.dist_code_combination_id
			   and drv.org_id = t_vw.org_id
			   and drv.set_of_books_id = t_vw.set_of_books_id)
                       when matched then update
		        set drv.prepay_distribution_id = t_vw.invoice_distribution_id,
			    drv.prepay_invoice_id = t_vw.invoice_id,
			    drv.prepay_line_number = t_vw.invoice_line_number,
			    drv.error_message = null,
			    drv.process_flag = 'P'
		      where drv.prepay_distribution_id is null
		        and drv.process_flag = 'Y'
			and drv.invoice_id = i.invoice_id;

			
                AP_Acctg_Data_Fix_PKG.Print( 'merge done for invoice_id : '|| i.invoice_id);
  
	                 Update AP_TEMP_DATA_DRIVER_9243855 drv
		           set drv.process_flag = 'P',
		               drv.error_message = null
		         where drv.process_flag = 'Y'
			 and   drv.invoice_id = i.invoice_id
		           and drv.prepay_distribution_id is not null;

         l_message := ' DML on invoice: ';
         AP_Acctg_Data_Fix_PKG.Print( l_message || i.invoice_id );

                         Merge into AP_INVOICE_DISTRIBUTIONS_ALL aid
			    using AP_TEMP_DATA_DRIVER_9243855 t
			    on (aid.old_distribution_id = t.invoice_distribution_id
			        and t.process_flag = 'P'
				and t.invoice_id = i.invoice_id
				and t.invoice_id = aid.invoice_id)
			  when matched then update
			   set aid.prepay_distribution_id = t.prepay_distribution_id,
			   aid.last_updated_by= FND_GLOBAL.user_id,       /*Added for bug 17412439*/
             		   aid.last_update_login= '-17412439',
		           aid.last_update_date= sysdate
			where aid.prepay_distribution_id is null;

         l_message := ' Merge into actual table AP_INVOICE_DISTRIBUTIONS_ALL updated:'|| i.invoice_id;
         AP_Acctg_Data_Fix_PKG.Print( l_message );

	                 Merge into AP_INVOICE_LINES_ALL ail
			     using (select DISTINCT aid.invoice_id,
				           t.prepay_invoice_id,
					   t.prepay_line_number,
					   aid.invoice_line_number
				      from ap_invoice_distributions_all aid,
					   AP_TEMP_DATA_DRIVER_9243855 t
				   where aid.old_distribution_id = t.invoice_distribution_id
				     and t.process_flag = 'P'
				     and t.invoice_id = i.invoice_id ) t_vw
					on (ail.invoice_id = t_vw.invoice_id
					and ail.line_number = t_vw.invoice_line_number)
				   when matched then update
					set ail.prepay_invoice_id = t_vw.prepay_invoice_id,
					    ail.prepay_line_number = t_vw.prepay_line_number,
						ail.last_updated_by= FND_GLOBAL.user_id,       /*Added for bug 17412439*/
						ail.last_update_login= '-17412439',
						ail.last_update_date= sysdate
				      where ail.prepay_invoice_id is null;

        l_message := ' Merge into actual table AP_INVOICE_LINES_ALL updated:'||sql%rowcount ||' records.';
        AP_Acctg_Data_Fix_PKG.Print( l_message );

        COMMIT;

        EXCEPTION
	  WHEN OTHERS THEN
             ROLLBACK;
             AP_Acctg_Data_Fix_PKG.Print( 'error occured while mergint the drv table for ' ||
		                                   ' invoice_id  :  ' || i.invoice_id 
						   || ' error is : '|| sqlerrm);
        END;

       END LOOP;
  
     END IF;


            Select distinct t.org_id,
                            t.invoice_id
               bulk collect into l_org_id_tab,
                                 l_invoice_id_tab
             from AP_TEMP_DATA_DRIVER_9243855 t
              Where t.process_flag = 'P'
	      order by 1;

     FOR l_loop_counter in nvl(l_org_id_tab.first,0) .. nvl(l_org_id_tab.last,-1) 
	LOOP

          IF l_org_id_tab(l_loop_counter) <> nvl(l_prev_org_id,-1) then
           MO_GLOBAL.SET_POLICY_CONTEXT('S', l_org_id_tab(l_loop_counter));
          END IF;

		DELETE FROM ap_prepay_app_dists appd
		  WHERE appd.prepay_app_distribution_id IN
		     (SELECT DISTINCT invoice_distribution_id
			FROM ap_invoice_distributions_all aid
			WHERE aid.invoice_id = l_invoice_id_tab(l_loop_counter))
			 AND (accounting_event_id IS NULL OR
		       NOT EXISTS
			(SELECT 1
				FROM ap_prepay_history_all apph
			 WHERE apph.prepay_history_id = appd.prepay_history_id));

        l_message := 'Cleanup on the Prepay Distributions table deleted '||SQL%ROWCOUNT||' records';
        AP_Acctg_Data_Fix_PKG.Print( l_message );

                 DELETE FROM ap_prepay_history_all apph
		   WHERE apph.invoice_id = l_invoice_id_tab(l_loop_counter)
		 AND(accounting_event_id IS NULL OR
		 NOT EXISTS
			(SELECT 1
			   FROM ap_prepay_app_dists appd
			 WHERE appd.prepay_history_id = apph.prepay_history_id));

        l_message := 'Cleanup on the Prepay History table deleted '||SQL%ROWCOUNT||' records';
        AP_Acctg_Data_Fix_PKG.Print( l_message );

                      SELECT aid.invoice_distribution_id,
			     aid.accounting_event_id,
			     aid.bc_event_id
			BULK COLLECT INTO l_inv_dist_id_tab,
					  l_acct_event_id_tab,
					  l_bc_event_id_tab
		       FROM ap_invoice_distributions_all aid,
			    xla_events xe
		      WHERE xe.application_id = 200
			AND aid.accounting_event_id = xe.event_id
			AND aid.invoice_id = l_invoice_id_tab(l_loop_counter)
			AND xe.event_status_code IN ('I','U')
			AND xe.event_type_code IN ('PREPAYMENT APPLIED','PREPAYMENT UNAPPLIED')	
			AND aid.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
			AND aid.prepay_distribution_id IS NOT NULL
		       ORDER BY aid.invoice_id,xe.event_type_code;

            FORALL i IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
		UPDATE ap_invoice_distributions_all aid
		SET aid.accounting_event_id = NULL,
		    aid.bc_event_id = NULL,
		    aid.posted_flag = DECODE(aid.posted_flag, 'S', 'N', aid.posted_flag),
			aid.last_updated_by= FND_GLOBAL.user_id,       /*Added for bug 17412439*/
			aid.last_update_login= '-17412439',
			aid.last_update_date= sysdate
		WHERE aid.invoice_distribution_id = l_inv_dist_id_tab(i);

	    FORALL i IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
		DELETE FROM ap_prepay_app_dists
		 WHERE (accounting_event_id = l_acct_event_id_tab(i) OR
			bc_event_id = l_bc_event_id_tab(i) OR
			prepay_app_distribution_id = l_inv_dist_id_tab(i));

	    FORALL i IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
		DELETE FROM ap_prepay_history_all
		 WHERE (accounting_event_id = l_acct_event_id_tab(i) OR
			bc_event_id = l_bc_event_id_tab(i));

             l_message := 'Before the call to the prepay_dist_appl routine for Invoice_id :'|| l_invoice_id_tab(l_loop_counter);
             AP_Acctg_Data_Fix_PKG.Print( l_message );

              AP_ACCTG_PREPAY_DIST_PKG.prepay_dist_appl
                          (l_invoice_id_tab(l_loop_counter),
                           l_calling_sequence);

             l_message := 'API call successful for Invoice_Id :'||l_invoice_id_tab(l_loop_counter);
             AP_Acctg_Data_Fix_PKG.Print( l_message );

	   FORALL i IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
	      UPDATE ap_invoice_distributions_all aid
		SET aid.accounting_event_id = l_acct_event_id_tab(i),
		    aid.bc_event_id = l_bc_event_id_tab(i),
			aid.last_updated_by= FND_GLOBAL.user_id,       /*Added for bug 17412439*/
			aid.last_update_login= '-17412439',
			aid.last_update_date= sysdate
		WHERE aid.invoice_distribution_id = l_inv_dist_id_tab(i);

	   FORALL i IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
	      UPDATE ap_prepay_app_dists
		SET accounting_event_id = l_acct_event_id_tab(i),
		    bc_event_id = l_bc_event_id_tab(i),
			last_updated_by= FND_GLOBAL.user_id,       /*Added for bug 17412439*/
			last_update_login= '-17412439',
			last_update_date= sysdate
		WHERE prepay_app_distribution_id = l_inv_dist_id_tab(i);

	   FORALL i IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
	      UPDATE ap_prepay_history_all apph
		SET apph.accounting_event_id = l_acct_event_id_tab(i),
		    apph.bc_event_id = l_bc_event_id_tab(i),
			last_updated_by= FND_GLOBAL.user_id,       /*Added for bug 17412439*/
			last_update_login= '-17412439',
			last_update_date= sysdate
		WHERE EXISTS
			(SELECT 1
			   FROM ap_prepay_app_dists apad
			WHERE apad.prepay_history_id = apph.prepay_history_id
			AND apad.prepay_app_distribution_id = l_inv_dist_id_tab(i));
	
             l_prev_org_id := l_org_id_tab(l_loop_counter);

        END LOOP;

	COMMIT;

         l_message := ' Following transactions were fixed as a part of '||
                      ' the Data Fix ';
         AP_ACCTG_DATA_FIX_PKG.Print(l_message);
         l_message := '____________________________________________________';
         AP_ACCTG_DATA_FIX_PKG.Print(l_message);

         l_dists_col_str := 'INVOICE_ID,LINE_TYPE_LOOKUP_CODE,DIST_CODE_COMBINATION_ID,'
			    || 'INVOICE_DISTRIBUTION_ID,PREPAY_TAX_PARENT_ID,PREPAY_DISTRIBUTION_ID,PREPAY_INVOICE_ID,'
			    || 'PREPAY_LINE_NUMBER,TAX_CODE_ID,AMOUNT,POSTED_FLAG,ORG_ID';
         l_table_name := 'AP_TEMP_DATA_DRIVER_9243855';
         l_dists_where_str := 'process_flag = ''P'' ';

         AP_ACCTG_DATA_FIX_PKG.Print_html_table
           (p_select_list       => l_dists_col_str,
            p_table_in          => l_table_name,
            p_where_in          => l_dists_where_str,
            P_calling_sequence  => l_calling_sequence);

        l_message :=  '____________________________________________________';
        AP_ACCTG_DATA_FIX_PKG.Print(l_message);
   
        l_message := ' Following transactions were NOT fixed as a part of '||
                     ' the Data Fix ';
         AP_ACCTG_DATA_FIX_PKG.Print(l_message);
         l_message := '____________________________________________________';
         AP_ACCTG_DATA_FIX_PKG.Print(l_message);

         l_dists_col_str := 'INVOICE_ID,LINE_TYPE_LOOKUP_CODE,DIST_CODE_COMBINATION_ID,'
			    || 'INVOICE_DISTRIBUTION_ID,PREPAY_TAX_PARENT_ID,PREPAY_DISTRIBUTION_ID,PREPAY_INVOICE_ID,'
			    || 'PREPAY_LINE_NUMBER,TAX_CODE_ID,AMOUNT,POSTED_FLAG,ORG_ID';
         l_table_name := 'AP_TEMP_DATA_DRIVER_9243855';
         l_dists_where_str := 'process_flag <> ''P'' ';

         AP_ACCTG_DATA_FIX_PKG.Print_html_table
           (p_select_list       => l_dists_col_str,
            p_table_in          => l_table_name,
            p_where_in          => l_dists_where_str,
            P_calling_sequence  => l_calling_sequence);

        l_message :=  '____________________________________________________';
        AP_ACCTG_DATA_FIX_PKG.Print(l_message);

        l_message := 'Datafix completed !!! <P>';

	 dbms_output.put_line( '--------------------------------------------------'
			|| '-----------------------------' );


        AP_Acctg_Data_Fix_PKG.Print( '</body></html>' );
        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( l_file_location||' is the log file created' );
		 
	 dbms_output.put_line( '--------------------------------------------------'
			|| '-----------------------------' );

EXCEPTION
  WHEN OTHERS THEN
         l_message := 'after '||l_message||'';
         AP_Acctg_Data_Fix_PKG.Print( l_message );
		 
         l_message := 'Exception :: '||SQLERRM||'';
         AP_Acctg_Data_Fix_PKG.Print( l_message );
		 
         AP_Acctg_Data_Fix_PKG.Print( '</body></html>' );
         AP_Acctg_Data_Fix_PKG.Close_Log_Out_Files;
		--Send Email
		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 with error.' );
         dbms_output.put_line( '--------------------------------------------------'
							|| '-----------------------------' );

         APP_EXCEPTION.RAISE_EXCEPTION;
END ;
/
EXIT;