REM +=======================================================================+
REM +=======================================================================+
REM | FILENAME                                                              |
REM |   DataFix_00164579A.sql                                               |
REM | DESCRIPTION                                                           |
REM |   GDF for generating missing accounting events on AP transactions     |
REM | HISTORY                                                               |
REM |   Created By : William Green                                                |
REM +=======================================================================+

SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
SET SERVEROUTPUT ON;

DECLARE

  l_sql_stmt                    LONG;
  l_invoice_id                  AP_INVOICES_ALL.INVOICE_ID%TYPE := 4224100;
  l_invoice_dist_id             AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE;
  l_org_id                      AP_INVOICES_ALL.ORG_ID%TYPE := 482;
  l_accounting_event_id         NUMBER;
  l_instance_name       		VARCHAR2(100);
  l_host_name           		VARCHAR2(100);
  l_block_debug    				varchar2(1000) := 'Declaration';
  l_calling_sequence            FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE := 'GDF script DataFix_00164579A.sql';
  
BEGIN
	l_block_debug := ' GDF script DataFix_00164579A.sql ';
	dbms_output.put_line(l_block_debug);

	/*********************************************************/
	/*            Printing instance details                  */
	/*********************************************************/
	
	l_block_debug := 'Printing instance details';
	dbms_output.put_line(l_block_debug);

	  BEGIN
		SELECT instance_name, host_name
		  INTO l_instance_name, l_host_name
		  FROM v$instance;
		  dbms_output.put_line('Selection script executed on instance '||
		  l_instance_name||' host_name '||l_host_name||' on '||to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS'));  
	  EXCEPTION
	  WHEN OTHERS THEN
		dbms_output.put_line('While querying instance name');
		l_block_debug := 'Exception :: '||SQLERRM||'';
		dbms_output.put_line(l_block_debug);
	  END;  

	/*********************************************************/
	/*    TAKING THE BACKUP OF THE AFFECTED TRANSACTIONS     */
	/*********************************************************/
	
	l_block_debug := 'TAKING THE BACKUP OF THE AFFECTED TRANSACTIONS';
	dbms_output.put_line(l_block_debug);
  
	l_sql_stmt := 'create table rsi_ap_inv_dist_164579 as '||
				'select * from ap_invoice_distributions_all '||
				'where invoice_id =  4224100 '||
				'and accounting_event_id is null';
				
	EXECUTE IMMEDIATE l_sql_stmt;	

	/*********************************************************/
	/*    SETTING ORG PROFILE							     */
	/*********************************************************/
	
	l_block_debug := 'SETTING ORG PROFILE';
	dbms_output.put_line(l_block_debug);
	
	MO_GLOBAL.SET_POLICY_CONTEXT('S', l_org_id);

	/*********************************************************/
	/*    SETTING EVENT ID  							     */
	/*********************************************************/
	
	l_block_debug := 'SETTING EVENT ID';
	dbms_output.put_line(l_block_debug);
	
	SELECT xla_event_s.nextval into l_accounting_event_id
	from dual;

	/*********************************************************/
	/*    UPDATING DISTRIBUTIONS WITH MISSING EVENT ID'S     */
	/*********************************************************/
	
	l_block_debug := 'UPDATING DISTRIBUTIONS WITH MISSING EVENT ID';
	dbms_output.put_line(l_block_debug);
	
	BEGIN
		update ap_invoice_distributions_all set
		accounting_event_id = l_accounting_event_id
		where invoice_id = l_invoice_id
		and accounting_event_id is null;
		
		COMMIT;

	EXCEPTION
	  WHEN OTHERS THEN
		ROLLBACK;
		l_block_debug := 'Encountered an Exception '||SQLERRM||
					 ' at '||l_calling_sequence||
					 ' on updating missing event_id'||
					 ' while Processing the Invoice_id :'||l_invoice_id;
		dbms_output.put_line(l_block_debug);
		RAISE;
	END;

	/*********************************************************/
	/*    CALLING AP_ACCOUNTING_EVENTS_PKG.Create_Events     */
	/*********************************************************/
	  
	l_block_debug := 'Calling Event Generation Routine for Validated Distributions of Invoice_id:'||l_invoice_id||' accounting event ID: '||l_accounting_event_id;
	dbms_output.put_line(l_block_debug);
  
	BEGIN

		AP_ACCOUNTING_EVENTS_PKG.Create_Events (
			'INVOICES',
			NULL,
			l_invoice_id,
			trunc(sysdate),
			l_accounting_event_id,
			NULL,
			l_calling_sequence);

	  -- Committing per invoice
	  COMMIT;

	EXCEPTION
	  WHEN OTHERS THEN
		ROLLBACK;
		l_block_debug := ' Encountered an Exception '||SQLERRM||
					 ' at '||l_calling_sequence||
					 ' on Calling Event Generation Routine'||
					 ' while Processing the Invoice_id :'||l_invoice_id;
		dbms_output.put_line(l_block_debug);
	END;	
	
EXCEPTION
WHEN OTHERS THEN
	ROLLBACK;
	dbms_output.put_line(chr(9));
	dbms_output.put_line(sqlerrm ||' error occurred in Block: '||l_block_debug);
	dbms_output.put_line('Please report the above error to Support');
	dbms_output.put_line(chr(9));   
	RAISE;	  
END;