create or replace PROCEDURE CDR_PROC_ARCHIVE_ORDER_EXTRACT
IS

/*
Criteria to be followed to Order Archival

* Order Status should be 'Cancelled' or 'Complete'
* Order Closed date should be 6 months before
*/

-- main Cursor to spool the Orders to be archived based on criteria
CURSOR GET_ORD_DET IS
(
SELECT ORD.ROW_WID ORDER_ID      
FROM SRMW.W_ORDER_D@ARCHIVAL_TO_CRMSPA2 ORD
WHERE  
  (
  (ORD.STATUS_CD ='Complete' 
  --AND (ORD.STATUS_DT<add_months(trunc(sysdate,'MM'),GET_ENTITY('Orders'))
	--or (ORD.STATUS_DT is null AND ORD.ORDER_COMPL_DATE<add_months(trunc(sysdate,'MM'),GET_ENTITY('Orders'))))
  AND (trunc(ORD.STATUS_DT)<'01-AUG-2009'
   or (ORD.STATUS_DT is null AND trunc(ORD.ORDER_COMPL_DATE)<'01-AUG-2009'))
  )
  OR  
  (ORD.STATUS_CD ='Cancelled'
  --AND (ORD.X_CANCEL_DT<add_months(trunc(sysdate,'MM'),GET_ENTITY('Orders'))
  --or (ord.x_cancel_dt is null AND ORD.ORDER_COMPL_DATE<add_months(trunc(sysdate,'MM'),GET_ENTITY('Orders')))
  AND (trunc(ORD.X_CANCEL_DT)<'01-AUG-2009'
   or (ORD.X_CANCEL_DT is null AND trunc(ORD.ORDER_COMPL_DATE)<'01-AUG-2009'))
  )
  )
  --AND ORD.ROW_WID='246723'
 );
	  
	  
    type ORDER_ID is table of varchar(20);
    l_data ORDER_ID;

    dml_errors EXCEPTION;
    PRAGMA exception_init(dml_errors, -24381);
	
    l_errors number;
    l_errno    number;
    l_msg    varchar2(4000);
    --l_idx    number;
	
	
begin
    open GET_ORD_DET;
    loop
    fetch GET_ORD_DET bulk collect into l_data limit 2000;
    begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.W_ORDER_F
    (SELECT ORF.*
     FROM SRMW.W_ORDER_F@ARCHIVAL_TO_CRMSPA2 ORF
     WHERE  ORF.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into W_ORDER_F tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
-----------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.W_ORDER_D
    (SELECT ORD.*
FROM SRMW.W_ORDER_D@ARCHIVAL_TO_CRMSPA2 ORD
WHERE ORD.ROW_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into W_ORDER_D tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
 ----------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.W_ORDERITEM_F
    (SELECT ITEM.*
FROM SRMW.W_ORDERITEM_F@ARCHIVAL_TO_CRMSPA2 ITEM
WHERE ITEM.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into W_ORDERITEM_F tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
-----------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.WC_ORDERITEM_XA_F
    (SELECT  XA.*
FROM SRMW.WC_ORDERITEM_XA_F@ARCHIVAL_TO_CRMSPA2 XA
WHERE XA.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into WC_ORDERITEM_XA_F tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
-----------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.WC_ORDERITEM_XA_D
    (SELECT  XAD.*
FROM SRMW.WC_ORDERITEM_XA_D@ARCHIVAL_TO_CRMSPA2 XAD,
     SRMW.W_ORDERITEM_F@ARCHIVAL_TO_CRMSPA2 ITM
WHERE XAD.ORDER_ITEM_ID=ITM.INTEGRATION_ID
AND ITM.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into WC_ORDERITEM_XA_D tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;
   
   --------------------------------------------------------------
   begin
    for i in 1 .. l_data.count --SAVE EXCEPTIONS
    loop
    --SAVE EXCEPTIONS
     INSERT INTO OBI_ARCH.W_ACTIVITY_F
    (SELECT EVT.*
FROM SRMW.W_ACTIVITY_F@ARCHIVAL_TO_CRMSPA2 EVT,
	 SRMW.W_ORDERITEM_F@ARCHIVAL_TO_CRMSPA2 ITM
WHERE EVT.ORDER_ITEM_ID=ITM.INTEGRATION_ID
AND ITM.ORDER_WID=l_data(i)
    );  
    end loop;
	COMMIT;
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    l_msg   := sqlerrm(-l_errno);
                    --l_idx   := sql%bulk_exceptions(i).error_index;
                    insert into H_ARCHIVAL_ERROR_HANDLING 
		values
                    (l_data(i),
   'Order Archival',
  'Error while inserting into W_ACTIVITY_F tbl',
  l_msg,
  'CDR_PKG_ARCHIVE_ORDER',
  'CDR_PROC_ARCHIVE_ORDER',null,
  --REC_ORD_DET.SSA_NAME,
  TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
   end loop;
   COMMIT;
   end;   
   
   
exit when GET_ORD_DET%notfound;
		
		
    end loop;
    close GET_ORD_DET;
end;


/*
 TRUNCATE TABLE OBI_ARCH.W_ORDER_F;
 TRUNCATE TABLE OBI_ARCH.W_ORDER_D;
 TRUNCATE TABLE OBI_ARCH.W_ORDERITEM_F;
 TRUNCATE TABLE OBI_ARCH.WC_ORDERITEM_XA_F;
 TRUNCATE TABLE OBI_ARCH.WC_ORDERITEM_XA_D;
 TRUNCATE TABLE OBI_ARCH.W_ACTIVITY_F;
 */

  AND (trunc(ORD.STATUS_DT)<'01-AUG-2009'

AND ORD.STATUS_DT < to_date('01-AUG-2009','DD-MON-YYYY')