--OBJECTIVE: THIS SCRIPT IS TO UPDATE THE TRANSACTION NUMBER. THE NEW TRANSACTION NUMBER IS THE INVOICE_NUMBER GENERATED AT THE CITY HALL
--TO EXECUTE THIS OPERATION, THIS SCRIPT USES A EXCEL FILE *.XLSX, 
--WHICH HAS 3 COLUMNS: THE NEW_TRX_NUMBER , OLD_TRX_NUMBER , ORIGEM AND CUSTOMER_TRX_ID
--THIS SCRIPT WILL UPDATE LINES INTO THE TABLES: RA_CUSTOMER_TRX_ALL, AR_PAYMENT_SCHEDULES_ALL


--1. CREATE TABLE xx_invoices_load_br

1.1) Check the table exist or not if exist drop and recreate.
DROP TABLE xx_invoices_load_br;
DROP TABLE SCRATCH.xx_invoices_load_br;

1.2) CREATE TABLE SCRATCH.xx_invoices_load_br ( new_trx_number  VARCHAR2(20),
                                old_trx_number  VARCHAR2(150),
                                ORIGEM          VARCHAR2(50),
                                customer_trx_id number);


--2. DATA LOAD - xx_invoices_load_br

2.1 
Load/Import from BOOk.xls to mr_invoices_load_br table
									
2.1.2 commit;	

2.2.1 Execute the below scripts for backup plan	


create table scratch.ra_customer_trx_all_NC0734344 as Select * from apps.ra_customer_trx_all
WHERE customer_trx_id in( select  customer_trx_id from SCRATCH.xx_invoices_load_br);

create table scratch.ar_payment_sch_all_NC0734344 as Select * from apps.ar_payment_schedules_all 
WHERE customer_trx_id in( select  customer_trx_id from SCRATCH.xx_invoices_load_br);

create table scratch.Jl_Br_Cust_Trx_INC0734344 as Select * from apps.Jl_Br_Customer_Trx_Exts
WHERE customer_trx_id in( select  customer_trx_id from SCRATCH.xx_invoices_load_br);

--2.2.2. EXECUTE THE FOLLOWING STATEMENT:

select *
from SCRATCH.xx_invoices_load_br
for update;

--2.2.2. OPEN THE PADLOCK (cadeado)
--2.2.3. CLICK ON THE STAR AT THE FIRST LINE TO CHANGE IT TO AN ARROW.
--2.2.4. CLICK ON THE ARROW TO SELECT ALL COLUMNS.
--2.2.5. PASTE (THE COLUMNS FROM EXCEL)
--2.2.6. CLICK ON THE "POST CHANGES" BUTTON.
--2.2.7. CLOSE THE PADLOCK (cadeado)
--2.2.8. COMMIT;										
										

--3. EXECUTE THE SCRIPT BELOW
--   TO EXECUTE SCRIPT BELOW PLEASE SET SERVEROUTPUT ON

DECLARE

CURSOR c_invoice_trx IS
 select a.customer_trx_id,
        c.old_trx_number,
        c.new_trx_number 
   from apps.ra_customer_trx_all a, 
        apps.ra_batch_sources_all b,
        SCRATCH.xx_invoices_load_br c 
  where a.trx_number = c.old_trx_number 
    and b.name = c.origem 
    and a.customer_trx_id=c.customer_trx_id
    and a.batch_source_id = b.batch_source_id;
    
  r_invoice_trx c_invoice_trx%rowtype;   
  
CURSOR c_validate IS
 select old_trx_number,
        origem,customer_trx_id
   from SCRATCH.xx_invoices_load_br;
    
  r_validate c_validate%rowtype;   
  
l_customer_trx_id number;  

BEGIN
  
    --Validate witch trx_numbers weren't found
  FOR r_validate IN c_validate LOOP
    
      l_customer_trx_id := null;
  Begin
     select a.customer_trx_id
       Into l_customer_trx_id
       from apps.ra_customer_trx_all a, 
            apps.ra_batch_sources_all b
      where a.trx_number = r_validate.old_trx_number 
        and b.name       = r_validate.origem 
        and a.batch_source_id = b.batch_source_id
        and a.customer_trx_id=r_validate.customer_trx_id;
  Exception when no_data_found then
            dbms_output.put_line('NOT FOUND -> Customer_trx_id: '|| r_validate.old_trx_number|| ' - ' ||r_validate.origem);
            
  End;
  END LOOP;
  
--
FOR r_invoice_trx IN c_invoice_trx LOOP

        Begin
          update apps.ra_customer_trx_all 
             set interface_header_attribute1 = r_invoice_trx.old_trx_number  
                ,trx_number                  = r_invoice_trx.new_trx_number 
           where customer_trx_id = r_invoice_trx.customer_trx_id;
        End;   
         --  
           Begin                      
             update apps.ar_payment_schedules_all 
                set global_attribute9             = 'MANUAL_RECEIPT'
                   ,global_attribute11            = 'N' 
                   ,global_attribute20            = Null 
                   ,SELECTED_FOR_RECEIPT_BATCH_ID = Null
                   ,trx_number                    = r_invoice_trx.new_trx_number 
              Where customer_trx_id               = r_invoice_trx.customer_trx_id;
           End;
		   --
           Begin                      
             update apps.Jl_Br_Customer_Trx_Exts 
                set Electronic_Inv_Access_Key     = r_invoice_trx.new_trx_number  
              Where customer_trx_id               = r_invoice_trx.customer_trx_id;
           End;		   
commit;
           
END LOOP;

--        
END;



--4. DROP TABLE xx_invoices_load_br
--DROP TABLE xx_invoices_load_br