create or replace PACKAGE          PKG_ARCHIVE_PUCM AS

  /* TODO enter package declarations (types, exceptions, methods etc) here */
/**constants*/
   --SCHEMA_NAME CONSTANT VARCHAR2(30) := SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA');
   --
   SHIPMENT_DELIVERED CONSTANT INTEGER := 5;
   SHIPMENT_CANCELLED CONSTANT INTEGER := 6;
   --
   /*
   TRANS_ORDER_NOSHIPMENT   CONSTANT INTEGER := 1;
   TRANS_ORDER_WITHSHIPMENT CONSTANT INTEGER := 2;
   SHIPMENT_NOTRANS_ORDER   CONSTANT INTEGER := 3;
   SHIPMENT_WITHTRANS_ORDER CONSTANT INTEGER := 4;
  */


  /**variables*/
   lint_sellimit INTEGER := archive_pckg.SEL_LIMIT;
   /*- PK master tables*/
   ltab_ids1 archive_tab_id_type;
   ltab_ids1_to archive_tab_id_type;
   ltab_ids1_from archive_tab_id_type;
   ltab_ids1_bill_to archive_tab_id_type;
   ltab_ids2 archive_tab_id_type;
   ltab_ids3 archive_tab_id_type;
   ltab_ids4 archive_tab_id_type;
   ltab_rowids1 archive_tab_rowid_type;
   ltab_shipment_withorder_ids archive_tab_id_type;


   CURSOR lcur_trans_order_noship(p_archive_trans_order_shipment NUMBER) IS
      SELECT archive_obj_id_type(order_id),
             archive_obj_id_type(to_contact_info_id),
             archive_obj_id_type(from_contact_info_id),
             archive_obj_id_type(bill_to_contact_info_id)
        FROM trans_order trans, stg_archive.yfs_order_tmp yfs
       WHERE  trim(trans.org_order_id)=trim(yfs.order_no)

               AND order_id NOT IN (SELECT order_id
                                FROM order_line
                               WHERE id IN (SELECT order_line_id
                                              FROM shipment_line
                                           )
                             );
       --  AND COALESCE(created, updated, canceled) < SYSDATE - p_archive_trans_order_shipment;


    CURSOR lcur_trans_order_withship IS
      SELECT archive_obj_id_type(order_id),
             archive_obj_id_type(to_contact_info_id),
             archive_obj_id_type(from_contact_info_id),
             archive_obj_id_type(bill_to_contact_info_id)
        FROM trans_order trans, stg_archive.yfs_order_tmp yfs
       WHERE trim(trans.org_order_id)=trim(yfs.order_no)
       AND order_id IN (SELECT order_id
                            FROM order_line
                           WHERE id IN (SELECT order_line_id
                                          FROM  stg_PUCM_archive.shipment_line_tmp
                                       )
                         );

   CURSOR lcur_order_comment IS
      SELECT archive_obj_id_type(order_comment_id)
        FROM order_comment
       WHERE order_id IN (SELECT id
                            FROM TABLE(ltab_ids1)
                         );

    CURSOR lcur_order_instruction IS
      SELECT archive_obj_id_type(id)
        FROM order_instruction
       WHERE order_id IN (SELECT id
                            FROM TABLE(ltab_ids1)
                         );

   CURSOR lcur_order_line IS
      SELECT archive_obj_id_type(id)
        FROM order_line
       WHERE order_id IN (SELECT id
                            FROM TABLE(ltab_ids1)
                         );

   CURSOR lcur_order_line_instruction IS
      SELECT archive_obj_id_type(id)
        FROM order_line_instruction
       WHERE order_line_id IN (SELECT id
                                 FROM TABLE(ltab_ids2)
                              );

  CURSOR lcur_contact_info IS
      SELECT archive_obj_id_type(id)
        FROM contact_info
       WHERE id IN (SELECT id
                      FROM TABLE(ltab_ids1_to)
                     WHERE id IS NOT NULL
                    UNION ALL
                    SELECT id
                      FROM TABLE(ltab_ids1_from)
                     WHERE id IS NOT NULL
                    UNION ALL
                    SELECT id
                      FROM TABLE(ltab_ids1_bill_to)
                     WHERE id IS NOT NULL
                   );


 CURSOR lcur_shipment_noorder(p_archive_trans_order_shipment NUMBER) IS
      SELECT archive_obj_id_type(shipment_id),
             archive_obj_id_type(to_contact_info_id),
             archive_obj_id_type(from_contact_info_id),
             archive_obj_id_type(NULL)
        FROM shipment sh, stg_archive.yfs_ship_tmp yfs
       WHERE trim(sh.orig_shipment_id)=trim(yfs.shipment_no)
       AND sh.shipment_id NOT IN (SELECT shipment_id
                                   FROM shipment_line
                                  WHERE order_line_id IN (SELECT id
                                                            FROM order_line
                                                         )
                                )
         AND shipment_status_id IN (SHIPMENT_DELIVERED, SHIPMENT_CANCELLED);
         --AND COALESCE(created, updated) < SYSDATE - p_archive_trans_order_shipment;


  CURSOR lcur_shipment_withorder IS
      SELECT archive_obj_id_type(shipment_id),
             archive_obj_id_type(to_contact_info_id),
             archive_obj_id_type(from_contact_info_id),
             archive_obj_id_type(NULL)
        FROM shipment sh, stg_archive.yfs_ship_tmp yfs
       WHERE trim(sh.orig_shipment_id)=trim(yfs.shipment_no)
       AND  sh.shipment_id IN (SELECT id
                               FROM TABLE(ltab_shipment_withorder_ids)
                            );


CURSOR lcur_shipment_comment IS
      SELECT archive_obj_id_type(shipment_comment_id)
        FROM shipment_comment
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );

CURSOR lcur_shipment_container IS
      SELECT archive_obj_id_type(id)
        FROM shipment_container
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );

-- Added below 2 cursors on 14/06/15
CURSOR lcur_shipment_container_det IS
      SELECT archive_obj_id_type(id)
        FROM shipment_container_detail
       WHERE shipment_container_id IN (SELECT id
                               FROM TABLE(ltab_ids2)
                            );
  CURSOR lcur_shipment_container_det_tg IS
      SELECT archive_obj_id_type(id)
        FROM shipment_container_detail_tag
       WHERE shipment_container_detail_id IN (SELECT id
                               FROM TABLE(ltab_ids3)
                            );
-- Ended here on 14/06/15
CURSOR lcur_shipment_event IS
      SELECT archive_obj_id_type(shipment_event_id)
        FROM shipment_event
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );
-----------------------------------------------------------------------Added Tracking Event------------------------------------------------------
CURSOR lcur_tracking_event IS
      SELECT archive_obj_id_type(tracking_event_id)
        FROM tracking_event_bck
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );		
----------------------------------------------------------------------Closed Tracking Event------------------------------------------------------
 CURSOR lcur_shipment_line IS
      SELECT archive_obj_id_type(id)
        FROM shipment_line
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );
   CURSOR lcur_shipment_line_instruction IS
      SELECT archive_obj_id_type(id)
        FROM shipment_line_instruction
       WHERE shipment_line_id IN (SELECT id
                                    FROM TABLE(ltab_ids2)
                                 );
   CURSOR lcur_delivery IS
      SELECT archive_obj_id_type(delivery_id)
        FROM delivery
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );
   CURSOR lcur_addition_fee IS
      SELECT archive_obj_id_type(addition_fee_id)
        FROM addition_fee
       WHERE delivery_id IN (SELECT id
                               FROM TABLE(ltab_ids2)
                            );
   CURSOR lcur_document IS
      SELECT archive_obj_id_type(content_id),
             archive_obj_id_type(id),
             archive_obj_id_type(NULL)
        FROM document
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            )
      UNION ALL
      SELECT archive_obj_id_type(content_id),
             archive_obj_id_type(NULL),
             archive_obj_id_type(id)
        FROM document_history
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );
   CURSOR lcur_eta IS
      SELECT archive_obj_id_type(eta_id)
        FROM eta
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );
   CURSOR lcur_pickup IS
      SELECT archive_obj_id_type(pickup_id)
        FROM pickup
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );
   CURSOR lcur_shipment_rawb IS
      SELECT archive_obj_rowid_type(ROWIDTOCHAR(ROWID))
        FROM shipment_rawb
       WHERE shipment_id IN (SELECT id
                               FROM TABLE(ltab_ids1)
                            );




FUNCTION trans_order_process(pint_process_id IN INTEGER,l_archive_trans_order_shipment NUMBER)
   RETURN BOOLEAN;

PROCEDURE shipment_withorder(p_archive_trans_order_shipment NUMBER);

FUNCTION shipment_process(pint_process_id IN INTEGER,l_archive_trans_order_shipment NUMBER)
   RETURN BOOLEAN;

FUNCTION shipment_line_process
   RETURN BOOLEAN;

--PROCEDURE archive_purge (purge_job_log NUMBER,purge_message NUMBER,purge_attachment NUMBER,archive_trans_order_shipment NUMBER);

END PKG_ARCHIVE_PUCM;
/
-------------------------------------------------------------------------------------BODY--------------------------------------------------------------------------------------------------

create or replace PACKAGE BODY          PKG_ARCHIVE_PUCM AS

  FUNCTION trans_order_process(pint_process_id IN INTEGER,l_archive_trans_order_shipment NUMBER)
   RETURN BOOLEAN AS

   TRANS_ORDER_NOSHIPMENT   CONSTANT INTEGER := 1;
   TRANS_ORDER_WITHSHIPMENT CONSTANT INTEGER := 2;
   SHIPMENT_NOTRANS_ORDER   CONSTANT INTEGER := 3;
   SHIPMENT_WITHTRANS_ORDER CONSTANT INTEGER := 4;

BEGIN
      CASE pint_process_id
         WHEN TRANS_ORDER_NOSHIPMENT   THEN OPEN lcur_trans_order_noship(l_archive_trans_order_shipment);
         WHEN TRANS_ORDER_WITHSHIPMENT THEN OPEN lcur_trans_order_withship;
      END CASE;
      LOOP
         /**fetch master table PKs - maximum limited*/

         CASE pint_process_id
            WHEN TRANS_ORDER_NOSHIPMENT   THEN FETCH lcur_trans_order_noship   BULK COLLECT INTO ltab_ids1, ltab_ids1_to, ltab_ids1_from, ltab_ids1_bill_to LIMIT lint_sellimit;
            WHEN TRANS_ORDER_WITHSHIPMENT THEN FETCH lcur_trans_order_withship BULK COLLECT INTO ltab_ids1, ltab_ids1_to, ltab_ids1_from, ltab_ids1_bill_to LIMIT lint_sellimit;
         END CASE;

               /**archive old master rows [COPY]*/
         /*- insert into arch table TRANS_ORDER*/

         BEGIN
		 FORALL i in 1..ltab_ids1.count SAVE EXCEPTIONS
			INSERT INTO stg_PUCM_archive.trans_order
						SELECT *
							FROM trans_order
						WHERE order_id = ltab_ids1(i).id;
	     EXCEPTION
			WHEN OTHERS THEN
			NULL;
		 END;

         archive_pckg.rows_count('TRANS_ORDER', archive_pckg.OPER_INSERT);
         /**archive old slave rows*/
         /**open cursor of slave table ORDER_COMMENT*/

         OPEN lcur_order_comment;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_order_comment BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table ORDER_COMMENT*/
			BEGIN
			FORALL i in 1..ltab_ids2.count SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.order_comment
							SELECT *
								FROM order_comment
							WHERE order_comment_id =ltab_ids2(i).id;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
            archive_pckg.rows_count('ORDER_COMMENT', archive_pckg.OPER_INSERT);
           /*- delete from orig table ORDER_COMMENT*/
           BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE order_comment
                WHERE order_comment_id = ltab_ids2(i).id;
           EXCEPTION
           WHEN OTHERS THEN
           NULL;
           END;

            archive_pckg.rows_bulkcount('ORDER_COMMENT', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_order_comment%NOTFOUND;
         END LOOP;
         CLOSE lcur_order_comment;


         /**archive old slave rows*/
         /**open cursor of slave table ORDER_INSTRUCTION*/


         OPEN lcur_order_instruction;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_order_instruction BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table ORDER_INSTRUCTION*/
            BEGIN
			FORALL i in 1..ltab_ids2.count SAVE EXCEPTIONS
            INSERT INTO stg_PUCM_archive.order_instruction
						SELECT *
							FROM order_instruction
						WHERE id = ltab_ids2(i).id;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
            archive_pckg.rows_count('ORDER_INSTRUCTION', archive_pckg.OPER_INSERT);
            /*- delete from orig table ORDER_INSTRUCTION*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE order_instruction
                WHERE id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('ORDER_INSTRUCTION', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_order_instruction%NOTFOUND;
         END LOOP;
         CLOSE lcur_order_instruction;


         /**archive old slave rows*/
         /**open cursor of master table ORDER_LINE*/


         OPEN lcur_order_line;
         LOOP
            /**fetch master/slave table PKs - maximum limited*/
            FETCH lcur_order_line BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old master/slave rows [COPY]*/
            /*- insert into arch table ORDER_LINE*/
			BEGIN
			FORALL i in 1..ltab_ids2.count SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.order_line
							SELECT *
								FROM order_line
							WHERE id = ltab_ids2(i).id;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
            archive_pckg.rows_count('ORDER_LINE', archive_pckg.OPER_INSERT);
            /**archive old slave rows*/
            /**open cursor of slave table ORDER_LINE_INSTRUCTION*/
            OPEN lcur_order_line_instruction;
            LOOP
               /**fetch slave table PKs - maximum limited*/
               FETCH lcur_order_line_instruction BULK COLLECT INTO ltab_ids3 LIMIT lint_sellimit;
               /*- insert into arch table ORDER_LINE_INSTRUCTION*/
               BEGIN
			   FORALL i in 1..ltab_ids3.count SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.order_line_instruction
						SELECT *
							FROM order_line_instruction
						WHERE id = ltab_ids3(i).id;
			   EXCEPTION
					WHEN OTHERS THEN
					NULL;
			   END;
			   archive_pckg.rows_count('ORDER_LINE_INSTRUCTION', archive_pckg.OPER_INSERT);
               /*- delete from orig table ORDER_LINE_INSTRUCTION*/
               BEGIN
               FORALL i IN 1..ltab_ids3.COUNT SAVE EXCEPTIONS
                  DELETE order_line_instruction
                   WHERE id = ltab_ids3(i).id;
               EXCEPTION
               WHEN OTHERS THEN
               NULL;
               END;

               archive_pckg.rows_bulkcount('ORDER_LINE_INSTRUCTION', archive_pckg.OPER_DELETE, ltab_ids3.COUNT);
               /**exit loop when no data found*/
               EXIT WHEN lcur_order_line_instruction%NOTFOUND;
            END LOOP;
            CLOSE lcur_order_line_instruction;
            /**archive old master/slave rows [REMOVE]*/
            /*- delete from orig table ORDER_LINE*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE order_line
                WHERE id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('ORDER_LINE', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_order_line%NOTFOUND;
         END LOOP;
         CLOSE lcur_order_line;


         /**archive old master rows [REMOVE]*/
         /*- delete from orig table TRANS_ORDER*/
         BEGIN
         FORALL i IN 1..ltab_ids1.COUNT SAVE EXCEPTIONS
            DELETE trans_order
             WHERE order_id = ltab_ids1(i).id;
         EXCEPTION
         WHEN OTHERS THEN
         NULL;
         END;
         archive_pckg.rows_bulkcount('TRANS_ORDER', archive_pckg.OPER_DELETE, ltab_ids1.COUNT);

                  /**archive old master rows*/
         /**open cursor of master table CONTACT_INFO*/
         OPEN lcur_contact_info;
         LOOP
            /**fetch master table PKs - maximum limited*/
            FETCH lcur_contact_info BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old master rows*/
            /*- insert into arch table CONTACT_INFO*/
            BEGIN
			FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.contact_info
                 SELECT *
                   FROM contact_info
                  WHERE id = ltab_ids2(i).id;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
            archive_pckg.rows_count('CONTACT_INFO', archive_pckg.OPER_INSERT);
            /*- delete from orig table CONTACT_INFO*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE contact_info
                WHERE id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('CONTACT_INFO', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_contact_info%NOTFOUND;
         END LOOP;
         CLOSE lcur_contact_info;
         /**exit loop when no data found*/

        /*
        CASE
            WHEN pint_process_id = TRANS_ORDER_NOSHIPMENT   AND lcur_trans_order_noship%NOTFOUND   THEN  EXIT;
            WHEN pint_process_id = TRANS_ORDER_WITHSHIPMENT AND lcur_trans_order_withship%NOTFOUND THEN EXIT;
         END CASE;
         */


         CASE pint_process_id
            WHEN  TRANS_ORDER_NOSHIPMENT   THEN
            EXIT WHEN lcur_trans_order_noship%NOTFOUND;
            WHEN  TRANS_ORDER_WITHSHIPMENT THEN
            EXIT WHEN lcur_trans_order_withship%NOTFOUND ;
         END CASE;


      END LOOP;

      IF lcur_trans_order_noship%ISOPEN THEN
         CLOSE lcur_trans_order_noship;
      END IF;
      IF lcur_trans_order_withship%ISOPEN THEN
         CLOSE lcur_trans_order_withship;
      END IF;

      RETURN TRUE;

   EXCEPTION
   WHEN OTHERS THEN
         archive_pckg.print_error;
         IF lcur_trans_order_noship%ISOPEN THEN
            CLOSE lcur_trans_order_noship;
         END IF;
         IF lcur_trans_order_withship%ISOPEN THEN
            CLOSE lcur_trans_order_withship;
         END IF;
         IF lcur_order_comment%ISOPEN THEN
            CLOSE lcur_order_comment;
         END IF;
         IF lcur_order_instruction%ISOPEN THEN
            CLOSE lcur_order_instruction;
        END IF;
         IF lcur_order_line%ISOPEN THEN
            CLOSE lcur_order_line;
         END IF;
         IF lcur_order_line_instruction%ISOPEN THEN
            CLOSE lcur_order_line_instruction;
         END IF;
         IF lcur_contact_info%ISOPEN THEN
            CLOSE lcur_contact_info;
         END IF;
         RETURN FALSE;
    END trans_order_process;

    PROCEDURE shipment_withorder(p_archive_trans_order_shipment NUMBER)
   IS
      lint_shipment_count INTEGER := 0;
     ltab_shipment_ids1 archive_tab_id_type;
    ltab_shipment_ids2 archive_tab_id_type;


   BEGIN


   SELECT archive_obj_id_type(sh.shipment_id)        BULK COLLECT INTO ltab_shipment_withorder_ids
   FROM shipment sh, stg_archive.yfs_ship_tmp yfs WHERE TRIM(yfs.shipment_no)=TRIM(sh.orig_shipment_id)
   AND sh.shipment_status_id IN (SHIPMENT_DELIVERED,SHIPMENT_CANCELLED);

   /*
   SELECT archive_obj_id_type(shipment_id)
           BULK COLLECT INTO ltab_shipment_withorder_ids
           FROM (SELECT DISTINCT s1.shipment_id
                   FROM order_line o1,
                        shipment_line s1
                  WHERE o1.id = s1.order_line_id
                    AND o1.order_id IN (SELECT o2.order_id
                                          FROM order_line o2,
                                               shipment_line s2
                                         WHERE o2.id = s2.order_line_id
                                           AND s2.shipment_id IN ( SELECT shipment_id FROM shipment sh , stg_archive.yfs_ship_tmp yfs where TRIM(sh.orig_shipment_id)=TRIM(shipment_no)
										                           AND sh.shipment_status_id IN (SHIPMENT_DELIVERED,SHIPMENT_CANCELLED)
                                                                 )
                                       )
                                       );
     */


   END shipment_withorder;

   FUNCTION shipment_process(pint_process_id IN INTEGER,l_archive_trans_order_shipment NUMBER)
   RETURN BOOLEAN
   IS
   TRANS_ORDER_NOSHIPMENT   CONSTANT INTEGER := 1;
   TRANS_ORDER_WITHSHIPMENT CONSTANT INTEGER := 2;
   SHIPMENT_NOTRANS_ORDER   CONSTANT INTEGER := 3;
   SHIPMENT_WITHTRANS_ORDER CONSTANT INTEGER := 4;

   BEGIN
      /**open cursor of master table*/
      CASE pint_process_id
         WHEN SHIPMENT_NOTRANS_ORDER   THEN OPEN lcur_shipment_noorder(l_archive_trans_order_shipment);
         WHEN SHIPMENT_WITHTRANS_ORDER THEN shipment_withorder(l_archive_trans_order_shipment);
                                            OPEN lcur_shipment_withorder;
      END CASE;
      LOOP
         /**fetch master table PKs - maximum limited*/
         CASE pint_process_id
            WHEN SHIPMENT_NOTRANS_ORDER   THEN FETCH lcur_shipment_noorder   BULK COLLECT INTO ltab_ids1, ltab_ids1_to, ltab_ids1_from, ltab_ids1_bill_to LIMIT lint_sellimit;
            WHEN SHIPMENT_WITHTRANS_ORDER THEN FETCH lcur_shipment_withorder BULK COLLECT INTO ltab_ids1, ltab_ids1_to, ltab_ids1_from, ltab_ids1_bill_to LIMIT lint_sellimit;
         END CASE;
         /**archive old master rows [COPY]*/
         /*- insert into arch table SHIPMENT*/
		 BEGIN
		 FORALL i IN 1..ltab_ids1.COUNT SAVE EXCEPTIONS
			INSERT INTO stg_PUCM_archive.shipment
					SELECT *
						FROM shipment
					WHERE shipment_id = ltab_ids1(i).id;
		 EXCEPTION
			WHEN OTHERS THEN
			NULL;
		 END;
		 archive_pckg.rows_count('SHIPMENT', archive_pckg.OPER_INSERT);
         /**archive old slave rows*/
         /**open cursor of slave table SHIPMENT_COMMENT*/
         OPEN lcur_shipment_comment;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_shipment_comment BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table SHIPMENT_COMMENT*/
			BEGIN
			FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.shipment_comment
                 SELECT *
                   FROM shipment_comment
                  WHERE shipment_comment_id = ltab_ids2(i).ID;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
            archive_pckg.rows_count('SHIPMENT_COMMENT', archive_pckg.OPER_INSERT);
            /*- delete from orig table SHIPMENT_COMMENT*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE shipment_comment
                WHERE shipment_comment_id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('SHIPMENT_COMMENT', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_shipment_comment%NOTFOUND;
         END LOOP;
         CLOSE lcur_shipment_comment;
         /**open cursor of slave table SHIPMENT_CONTAINER*/
         OPEN lcur_shipment_container;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_shipment_container BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table SHIPMENT_CONTAINER*/
			BEGIN
			FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
					INSERT INTO stg_PUCM_archive.shipment_container
						SELECT *
							FROM shipment_container
						WHERE id = ltab_ids2(i).ID;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
            archive_pckg.rows_count('SHIPMENT_CONTAINER', archive_pckg.OPER_INSERT);

         --- Added shipment_container_det,shipment_container_det_tg on 14/06/15
                     /**open cursor of slave table SHIPMENT_CONTAINER_DETAIL*/
            OPEN lcur_shipment_container_det;
            LOOP
              /**fetch slave table PKs - maximum limited*/
              FETCH lcur_shipment_container_det BULK COLLECT INTO ltab_ids3 LIMIT lint_sellimit;
              /*- insert into arch table SHIPMENT_CONTAINER_DETAIL*/
			BEGIN
			 FORALL i IN 1..ltab_ids3.COUNT SAVE EXCEPTIONS
              INSERT INTO stg_PUCM_archive.shipment_container_detail
					SELECT *
						FROM shipment_container_detail
					WHERE id = ltab_ids3(i).id;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
			archive_pckg.rows_count('SHIPMENT_CONTAINER_DETAIL', archive_pckg.OPER_INSERT);
              /**open cursor of slave table SHIPMENT_CONTAINER_DETAIL_TAG*/
              OPEN lcur_shipment_container_det_tg;
              LOOP
                /**fetch slave table PKs - maximum limited*/
                FETCH lcur_shipment_container_det_tg BULK COLLECT INTO ltab_ids4 LIMIT lint_sellimit;
                /**archive old slave rows*/
                /*- insert into arch table SHIPMENT_CONTAINER_DETAIL_TAG*/
				BEGIN
				FORALL i IN 1..ltab_ids4.COUNT SAVE EXCEPTIONS
                INSERT INTO stg_PUCM_archive.shipment_container_detail_tag
					SELECT *
						FROM shipment_container_detail_tag
					WHERE id = ltab_ids4(i).id;
				EXCEPTION
					WHEN OTHERS THEN
					NULL;
				END;
                archive_pckg.rows_count('SHIPMENT_CONTAINER_DETAIL_TAG', archive_pckg.OPER_INSERT);
                  /*- delete from orig table SHIPMENT_CONTAINER_DETAIL_TAG*/
                  BEGIN
                  FORALL i IN 1..ltab_ids4.COUNT SAVE EXCEPTIONS
                    DELETE shipment_container_detail_tag
                    WHERE id = ltab_ids4(i).id;
                  EXCEPTION
                  WHEN OTHERS THEN
                  NULL;
                  END;
                  archive_pckg.rows_bulkcount('SHIPMENT_CONTAINER_DETAIL_TAG', archive_pckg.OPER_DELETE, ltab_ids4.COUNT);
                  /**exit loop when no data found*/
                  EXIT WHEN lcur_shipment_container_det_tg%NOTFOUND;
              END LOOP;
              CLOSE lcur_shipment_container_det_tg;
              /**archive old slave rows*/
              /*- delete from orig table SHIPMENT_CONTAINER*/
              BEGIN
              FORALL i IN 1..ltab_ids3.COUNT SAVE EXCEPTIONS
                DELETE shipment_container_detail
                WHERE id = ltab_ids3(i).id;
              EXCEPTION
              WHEN OTHERS THEN
              NULL;
              END;
              archive_pckg.rows_bulkcount('SHIPMENT_CONTAINER_DETAIL', archive_pckg.OPER_DELETE, ltab_ids3.COUNT);
              /**exit loop when no data found*/
              EXIT WHEN lcur_shipment_container_det%NOTFOUND;
            END LOOP;
            CLOSE lcur_shipment_container_det;

         -- Ended on 14/06/15



            /*- delete from orig table SHIPMENT_CONTAINER*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE shipment_container
                WHERE id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('SHIPMENT_CONTAINER', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_shipment_container%NOTFOUND;
         END LOOP;
         CLOSE lcur_shipment_container;
         /**open cursor of slave table SHIPMENT_EVENT*/
         OPEN lcur_shipment_event;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_shipment_event BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table SHIPMENT_EVENT*/
			BEGIN
			FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.shipment_event
					SELECT *
						FROM shipment_event
					WHERE shipment_event_id = ltab_ids2(i).id;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
            archive_pckg.rows_count('SHIPMENT_EVENT', archive_pckg.OPER_INSERT);
            /*- delete from orig table SHIPMENT_EVENT*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE shipment_event
                WHERE shipment_event_id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('SHIPMENT_EVENT', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_shipment_event%NOTFOUND;
         END LOOP;
         CLOSE lcur_shipment_event;
         /**archive old slave rows*/
		 /**open cursor of slave table TRACKING_EVENT*/
         OPEN lcur_tracking_event;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_tracking_event BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table TRACKING_EVENT*/
			BEGIN
			FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.tracking_event
					SELECT *
						FROM tracking_event_bck
					WHERE tracking_event_id = ltab_ids2(i).id;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
            archive_pckg.rows_count('TRACING_EVENT', archive_pckg.OPER_INSERT);
            /*- delete from orig table TRACKING_EVENT*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE tracking_event_bck
                WHERE tracking_event_id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('TRACKING_EVENT', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_tracking_event%NOTFOUND;
         END LOOP;
         CLOSE lcur_tracking_event;
		 /**archive old slave rows*/
         /**open cursor of master table SHIPMENT_LINE*/
         OPEN lcur_shipment_line;
         LOOP
            /**fetch master/slave table PKs - maximum limited*/
            FETCH lcur_shipment_line BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old master/slave rows [COPY]*/
            CASE pint_process_id
               WHEN SHIPMENT_NOTRANS_ORDER THEN
                  /*- insert into arch table SHIPMENT_LINE*/
				BEGIN
				  FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
					INSERT INTO stg_PUCM_archive.shipment_line
						SELECT *
							FROM shipment_line
                        WHERE id = ltab_ids2(i).id;
				EXCEPTION
					WHEN OTHERS THEN
					NULL;
				END;
                archive_pckg.rows_count('SHIPMENT_LINE', archive_pckg.OPER_INSERT);
               WHEN SHIPMENT_WITHTRANS_ORDER THEN
                  /*- insert into arch temporary table SHIPMENT_LINE_TMP*/
                  INSERT INTO stg_PUCM_archive.shipment_line_tmp
                       SELECT *
                         FROM shipment_line
                        WHERE id IN (SELECT id
                                       FROM TABLE(ltab_ids2)
                                    );
            END CASE;
            /**archive old slave rows*/
            /**open cursor of slave table SHIPMENT_LINE_INSTRUCTION*/
            OPEN lcur_shipment_line_instruction;
            LOOP
               /**fetch slave table PKs - maximum limited*/
               FETCH lcur_shipment_line_instruction BULK COLLECT INTO ltab_ids3 LIMIT lint_sellimit;
               CASE pint_process_id
                  WHEN SHIPMENT_NOTRANS_ORDER THEN
                     /*- insert into arch table SHIPMENT_LINE_INSTRUCTION*/
                     BEGIN
					 FORALL i IN 1..ltab_ids3.COUNT SAVE EXCEPTIONS
						INSERT INTO stg_PUCM_archive.shipment_line_instruction
                          SELECT *
                            FROM shipment_line_instruction
                           WHERE id = ltab_ids3(i).id;
					 EXCEPTION
						WHEN OTHERS THEN
						NULL;
					 END;
					 archive_pckg.rows_count('SHIPMENT_LINE_INSTRUCTION', archive_pckg.OPER_INSERT);
                  WHEN SHIPMENT_WITHTRANS_ORDER THEN
                     /*- insert into arch temporary table SHIPMENT_LINE_INSTRUCTION_TMP*/
                     INSERT INTO stg_PUCM_archive.shipment_line_instruction_tmp
                          SELECT *
                            FROM shipment_line_instruction
                           WHERE id IN (SELECT id
                                          FROM TABLE(ltab_ids3)
                                       );
               END CASE;
               /*- delete from orig table SHIPMENT_LINE_INSTRUCTION*/
               BEGIN
               FORALL i IN 1..ltab_ids3.COUNT SAVE EXCEPTIONS
                  DELETE shipment_line_instruction
                   WHERE id = ltab_ids3(i).id;
               EXCEPTION
               WHEN OTHERS THEN
               NULL;
               END;
               archive_pckg.rows_bulkcount('SHIPMENT_LINE_INSTRUCTION', archive_pckg.OPER_DELETE, ltab_ids3.COUNT);
               /**exit loop when no data found*/
               EXIT WHEN lcur_shipment_line_instruction%NOTFOUND;
            END LOOP;
            CLOSE lcur_shipment_line_instruction;
            /**archive old master/slave rows [REMOVE]*/
            /*- delete from orig table SHIPMENT_LINE*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE shipment_line
                WHERE id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('SHIPMENT_LINE', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_shipment_line%NOTFOUND;
         END LOOP;
         CLOSE lcur_shipment_line;
         /**archive old slave rows*/
         /**open cursor of master table DELIVERY*/
         OPEN lcur_delivery;
         LOOP
            /**fetch master/slave table PKs - maximum limited*/
            FETCH lcur_delivery BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old master/slave rows [COPY]*/
            /*- insert into arch table DELIVERY*/
            BEGIN
			FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.delivery
                 SELECT *
                   FROM delivery
                  WHERE delivery_id = ltab_ids2(i).id;
			EXCEPTION
				WHEN OTHERS THEN
				NULL;
			END;
			archive_pckg.rows_count('DELIVERY', archive_pckg.OPER_INSERT);
            /**archive old slave rows*/
            /**open cursor of slave table ADDITION_FEE*/
            OPEN lcur_addition_fee;
            LOOP
               /**fetch slave table PKs - maximum limited*/
               FETCH lcur_addition_fee BULK COLLECT INTO ltab_ids3 LIMIT lint_sellimit;
               /*- insert into arch table ADDITION_FEE*/
			   BEGIN
			   FORALL i IN 1..ltab_ids3.COUNT SAVE EXCEPTIONS
				INSERT INTO stg_PUCM_archive.addition_fee
                    SELECT *
                      FROM addition_fee
                     WHERE addition_fee_id = ltab_ids3(i).id;
			   EXCEPTION
			   WHEN OTHERS THEN
				NULL;
			   END;
               archive_pckg.rows_count('ADDITION_FEE', archive_pckg.OPER_INSERT);
               /*- delete from orig table ADDITION_FEE*/
               BEGIN
               FORALL i IN 1..ltab_ids3.COUNT SAVE EXCEPTIONS
                  DELETE addition_fee
                   WHERE addition_fee_id = ltab_ids3(i).id;
               EXCEPTION
               WHEN OTHERS THEN
               NULL;
               END;
               archive_pckg.rows_bulkcount('ADDITION_FEE', archive_pckg.OPER_DELETE, ltab_ids3.COUNT);
               /**exit loop when no data found*/
               EXIT WHEN lcur_addition_fee%NOTFOUND;
            END LOOP;
            CLOSE lcur_addition_fee;
            /**archive old master/slave rows [REMOVE]*/
            /*- delete from orig table DELIVERY*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE delivery
                WHERE delivery_id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
            NULL;
            END;
            archive_pckg.rows_bulkcount('DELIVERY', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_delivery%NOTFOUND;
         END LOOP;
         CLOSE lcur_delivery;
         /**open cursor of slave table*/
         OPEN lcur_document;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_document BULK COLLECT INTO ltab_ids2, ltab_ids3, ltab_ids4 LIMIT lint_sellimit;
            /**archive old master/slave rows [COPY]*/
            /*- insert into/update arch table DOCUMENT_CONTENT*/
            MERGE INTO stg_PUCM_archive.document_content t
            USING (SELECT *
                     FROM document_content
                   WHERE id IN (SELECT id
                                  FROM TABLE(ltab_ids2)
                                 WHERE id IS NOT NULL
                               )
                  ) a
               ON (t.id = a.id)
             WHEN MATCHED THEN
                UPDATE SET bytes = a.bytes
             WHEN NOT MATCHED THEN
                INSERT (id, bytes)
                VALUES (a.id, a.bytes);
            archive_pckg.rows_count('DOCUMENT_CONTENT', archive_pckg.OPER_MERGE);
            /**archive old slave rows*/
            /*- insert into arch table DOCUMENT*/
            INSERT INTO stg_PUCM_archive.document
                 SELECT *
                   FROM document
                  WHERE id IN (SELECT id
                                 FROM TABLE(ltab_ids3)
                                WHERE id IS NOT NULL
                              );
            archive_pckg.rows_count('DOCUMENT', archive_pckg.OPER_INSERT);
            /*- delete from orig table DOCUMENT*/
            BEGIN
            FORALL i IN 1..ltab_ids3.COUNT SAVE EXCEPTIONS
               DELETE document
                WHERE id = ltab_ids3(i).id;
            EXCEPTION
            WHEN OTHERS THEN
              NULL;
            END;
            archive_pckg.rows_bulkcount('DOCUMENT', archive_pckg.OPER_DELETE, ltab_ids3.COUNT);
            /*- insert into arch table DOCUMENT_HISTORY*/
            INSERT INTO stg_PUCM_archive.document_history
                 SELECT *
                   FROM document_history
                  WHERE id IN (SELECT id
                                 FROM TABLE(ltab_ids4)
                                WHERE id IS NOT NULL
                              );
            archive_pckg.rows_count('DOCUMENT_HISTORY', archive_pckg.OPER_INSERT);
            /*- delete from orig table DOCUMENT_HISTORY*/
            BEGIN
            FORALL i IN 1..ltab_ids4.COUNT SAVE EXCEPTIONS
               DELETE document_history
                WHERE id = ltab_ids4(i).id;
            EXCEPTION
            WHEN OTHERS THEN
              NULL;
            END;
            archive_pckg.rows_bulkcount('DOCUMENT_HISTORY', archive_pckg.OPER_DELETE, ltab_ids4.COUNT);
            /**archive old master/slave rows [REMOVE]*/
            /*- delete from orig table DOCUMENT_CONTENT*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE document_content
                WHERE id = ltab_ids2(i).id
                  AND id NOT IN (SELECT content_id
                                   FROM document
                                 UNION ALL
                                 SELECT content_id
                                   FROM document_history
                                );
            EXCEPTION
            WHEN OTHERS THEN
              NULL;
            END;
            archive_pckg.rows_bulkcount('DOCUMENT_CONTENT', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_document%NOTFOUND;
         END LOOP;
         CLOSE lcur_document;
         /**open cursor of slave table ETA*/
         OPEN lcur_eta;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_eta BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table ETA*/
            INSERT INTO stg_PUCM_archive.eta
                 SELECT *
                   FROM eta
                  WHERE eta_id IN (SELECT id
                                     FROM TABLE(ltab_ids2)
                                  );
            archive_pckg.rows_count('ETA', archive_pckg.OPER_INSERT);
            /*- delete from orig table ETA*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE eta
                WHERE eta_id = ltab_ids2(i).id;
            EXCEPTION
              WHEN OTHERS THEN
              NULL;
            END;
            archive_pckg.rows_bulkcount('ETA', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_eta%NOTFOUND;
         END LOOP;
         CLOSE lcur_eta;
         /**open cursor of slave table PICKUP*/
         OPEN lcur_pickup;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_pickup BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table PICKUP*/
            INSERT INTO stg_PUCM_archive.pickup
                 SELECT *
                   FROM pickup
                  WHERE pickup_id IN (SELECT id
                                        FROM TABLE(ltab_ids2)
                                     );
            archive_pckg.rows_count('PICKUP', archive_pckg.OPER_INSERT);
            /*- delete from orig table PICKUP*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE pickup
                WHERE pickup_id = ltab_ids2(i).id;
            EXCEPTION
              WHEN OTHERS THEN
              NULL;
            END;
            archive_pckg.rows_bulkcount('PICKUP', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_pickup%NOTFOUND;
         END LOOP;
         CLOSE lcur_pickup;
         /**open cursor of slave table SHIPMENT_RAWB*/
         OPEN lcur_shipment_rawb;
         LOOP
            /**fetch slave table PKs - maximum limited*/
            FETCH lcur_shipment_rawb BULK COLLECT INTO ltab_rowids1 LIMIT lint_sellimit;
            /**archive old slave rows*/
            /*- insert into arch table SHIPMENT_RAWB*/
            INSERT INTO stg_PUCM_archive.shipment_rawb
                 SELECT *
                   FROM shipment_rawb
                  WHERE ROWID IN (SELECT row_id
                                    FROM TABLE(ltab_rowids1)
                                 );
            archive_pckg.rows_count('SHIPMENT_RAWB', archive_pckg.OPER_INSERT);
            /*- delete from orig table SHIPMENT_RAWB*/
            BEGIN
            FORALL i IN 1..ltab_rowids1.COUNT SAVE EXCEPTIONS
               DELETE shipment_rawb
                WHERE ROWID = ltab_rowids1(i).row_id;
            EXCEPTION
            WHEN OTHERS THEN
              NULL;
            END;
            archive_pckg.rows_bulkcount('SHIPMENT_RAWB', archive_pckg.OPER_DELETE, ltab_rowids1.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_shipment_rawb%NOTFOUND;
         END LOOP;
         CLOSE lcur_shipment_rawb;
         /**archive old master rows [REMOVE]*/
         /*- delete from orig table SHIPMENT*/
         BEGIN
         FORALL i IN 1..ltab_ids1.COUNT SAVE EXCEPTIONS
            DELETE shipment
             WHERE shipment_id = ltab_ids1(i).id;
         EXCEPTION
         WHEN OTHERS THEN
          NULL;
         END;
         archive_pckg.rows_bulkcount('SHIPMENT', archive_pckg.OPER_DELETE, ltab_ids1.COUNT);
         /**open cursor of master table CONTACT_INFO*/
         OPEN lcur_contact_info;
         LOOP
            /**fetch master table PKs - maximum limited*/
            FETCH lcur_contact_info BULK COLLECT INTO ltab_ids2 LIMIT lint_sellimit;
            /**archive old master rows*/
            /*- insert into arch table CONTACT_INFO*/
            INSERT INTO stg_PUCM_archive.contact_info
                 SELECT *
                   FROM contact_info
                  WHERE id IN (SELECT id
                                 FROM TABLE(ltab_ids2)
                              );
            archive_pckg.rows_count('CONTACT_INFO', archive_pckg.OPER_INSERT);
            /*- delete from orig table CONTACT_INFO*/
            BEGIN
            FORALL i IN 1..ltab_ids2.COUNT SAVE EXCEPTIONS
               DELETE contact_info
                WHERE id = ltab_ids2(i).id;
            EXCEPTION
            WHEN OTHERS THEN
              NULL;
            END;
            archive_pckg.rows_bulkcount('CONTACT_INFO', archive_pckg.OPER_DELETE, ltab_ids2.COUNT);
            /**exit loop when no data found*/
            EXIT WHEN lcur_contact_info%NOTFOUND;
         END LOOP;
         CLOSE lcur_contact_info;
         /**exit loop when no data found*/
        /* CASE
            WHEN pint_process_id = SHIPMENT_NOTRANS_ORDER   AND lcur_shipment_noorder%NOTFOUND   THEN EXIT;
            WHEN pint_process_id = SHIPMENT_WITHTRANS_ORDER AND lcur_shipment_withorder%NOTFOUND THEN EXIT;
         END CASE;
         */

          CASE pint_process_id
            WHEN  SHIPMENT_NOTRANS_ORDER   THEN
            EXIT WHEN lcur_shipment_noorder%NOTFOUND;
            WHEN  SHIPMENT_WITHTRANS_ORDER THEN
            EXIT WHEN lcur_shipment_withorder%NOTFOUND ;
         END CASE;


      END LOOP;
      IF lcur_shipment_noorder%ISOPEN THEN
         CLOSE lcur_shipment_noorder;
      END IF;
      IF lcur_shipment_withorder%ISOPEN THEN
         CLOSE lcur_shipment_withorder;
      END IF;
      RETURN TRUE;
   EXCEPTION
      WHEN OTHERS THEN
         archive_pckg.print_error;
         IF lcur_shipment_noorder%ISOPEN THEN
            CLOSE lcur_shipment_noorder;
         END IF;
         IF lcur_shipment_withorder%ISOPEN THEN
            CLOSE lcur_shipment_withorder;
         END IF;
         IF lcur_shipment_comment%ISOPEN THEN
            CLOSE lcur_shipment_comment;
         END IF;
         IF lcur_shipment_container%ISOPEN THEN
            CLOSE lcur_shipment_container;
         END IF;

        -- Added below on 14/06/15
         IF lcur_shipment_container_det%ISOPEN THEN
            CLOSE lcur_shipment_container_det;
         END IF;

         IF lcur_shipment_container_det_tg%ISOPEN THEN
            CLOSE lcur_shipment_container_det_tg;
         END IF;

        -- Ended here on 14/06/15


         IF lcur_shipment_event%ISOPEN THEN
            CLOSE lcur_shipment_event;
         END IF;
		 IF lcur_tracking_event%ISOPEN THEN
            CLOSE lcur_tracking_event;
         END IF;
         IF lcur_shipment_line%ISOPEN THEN
            CLOSE lcur_shipment_line;
         END IF;
         IF lcur_shipment_line_instruction%ISOPEN THEN
            CLOSE lcur_shipment_line_instruction;
         END IF;
         IF lcur_delivery%ISOPEN THEN
            CLOSE lcur_delivery;
         END IF;
         IF lcur_addition_fee%ISOPEN THEN
            CLOSE lcur_addition_fee;
         END IF;
         IF lcur_document%ISOPEN THEN
            CLOSE lcur_document;
         END IF;
         IF lcur_eta%ISOPEN THEN
            CLOSE lcur_eta;
         END IF;
         IF lcur_pickup%ISOPEN THEN
            CLOSE lcur_pickup;
         END IF;
         IF lcur_shipment_rawb%ISOPEN THEN
            CLOSE lcur_shipment_rawb;
         END IF;
         IF lcur_contact_info%ISOPEN THEN
            CLOSE lcur_contact_info;
         END IF;
         RETURN FALSE;
   END shipment_process;

   /**archive ORDERS in passed cursor*/
   FUNCTION shipment_line_process
   RETURN BOOLEAN
   IS
   BEGIN
      /*- insert into arch table SHIPMENT_LINE*/
      INSERT INTO stg_PUCM_archive.shipment_line
           SELECT *
             FROM stg_PUCM_archive.shipment_line_tmp;
      archive_pckg.rows_count('SHIPMENT_LINE', archive_pckg.OPER_INSERT);
      /*- insert into arch table SHIPMENT_LINE_INSTRUCTION*/
      INSERT INTO stg_PUCM_archive.shipment_line_instruction
           SELECT *
             FROM stg_PUCM_archive.shipment_line_instruction_tmp;
      archive_pckg.rows_count('SHIPMENT_LINE_INSTRUCTION', archive_pckg.OPER_INSERT);
      RETURN TRUE;
   EXCEPTION
      WHEN OTHERS THEN
         archive_pckg.print_error;
         RETURN FALSE;
   End Shipment_Line_Process;

END PKG_ARCHIVE_PUCM;
/