APPS.CE_PURGE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 75

       p_Delete_Or_Append_Action  VARCHAR2,
       p_Objects                  VARCHAR2,
       p_Statement_type           VARCHAR2,
       p_hdr_int_status           VARCHAR2,
       p_Only_Unrec               VARCHAR2) IS
BEGIN
   G_BANK_ACCOUNT_ID           :=    p_BANK_ACCOUNT_ID;

Line: 91

   G_Delete_Or_Append_Action 	 :=    p_Delete_Or_Append_Action;

Line: 199

|	 delete_duplicate_data 		  			                                    |
|								                                                      	|
|  DESCRIPTION							                                            |
|	 Removes the duplicate entries from Archive Tables                    |
|   	         							                                            |
|								                                                      	|
|  CALLED BY							                                            	|
|	 purge_process                                                    		|
|  REQUIRES							                                              	|
|								                                                      	|
|  HISTORY							                                              	|
|	 11-Jan-2011	Created		Rtumati 		                                  |
 --------------------------------------------------------------------- */

PROCEDURE delete_duplicate_data IS
l_count NUMBER:=0;

Line: 219

            DELETE FROM CE_ARCH_RECONCILIATIONS car
            WHERE car.statement_line_id IN
                (SELECT cl.statement_line_id FROM ce_arch_lines cl WHERE
                  cl.statement_header_id IN (SELECT cah.statement_header_id
                  FROM ce_arch_headers cah ,
                  ce_statement_headers csh
                  WHERE cah.bank_account_id = csh.bank_account_id
                  AND csh.statement_number = cah.statement_number
                  AND csh.org_id = cah.org_id
                  AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                  AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
                  AND csh.bank_account_id IN (SELECT bank_account_id
                      FROM ce_bank_accts_gt_v ba
                      WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
                  AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)));

Line: 239

            DELETE FROM CE_ARCH_RECON_ERRORS cre
            WHERE cre.statement_line_id IN (SELECT cah.statement_header_id
                  FROM ce_arch_headers cah ,
                  ce_statement_headers csh
                  WHERE cah.bank_account_id = csh.bank_account_id
                  AND csh.statement_number = cah.statement_number
                  AND csh.org_id = cah.org_id
                  AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                  AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
                  AND csh.bank_account_id IN (SELECT bank_account_id
                      FROM ce_bank_accts_gt_v ba
                      WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
                  AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

Line: 257

            DELETE FROM CE_ARCH_LINES
            WHERE statement_header_id IN (SELECT cah.statement_header_id
                  FROM ce_arch_headers cah ,
                  ce_statement_headers csh
                  WHERE cah.bank_account_id = csh.bank_account_id
                  AND csh.statement_number = cah.statement_number
                  AND csh.org_id = cah.org_id
                  AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                  AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
                  AND csh.bank_account_id IN (SELECT bank_account_id
                      FROM ce_bank_accts_gt_v ba
                      WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
                  AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

Line: 275

            DELETE FROM CE_ARCH_HEADERS
            WHERE statement_header_id IN (SELECT cah.statement_header_id
                  FROM ce_arch_headers cah ,
                  ce_statement_headers csh
                  WHERE cah.bank_account_id = csh.bank_account_id
                  AND csh.statement_number = cah.statement_number
                  AND csh.org_id = cah.org_id
                  AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                  AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
                  AND csh.bank_account_id IN (SELECT bank_account_id
                      FROM ce_bank_accts_gt_v ba
                      WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
                  AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

Line: 297

            DELETE FROM CE_ARCH_INTERFACE_LINES cal
            WHERE cal.statement_number ||'-'|| cal.bank_account_num IN
	                ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
	                FROM	 ce_statement_headers_int csh
                  WHERE  NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
				          FROM   ce_bank_branches_v bb, ce_bank_accts_gt_v ba
				          WHERE  bb.branch_party_id = ba.bank_branch_id
				          AND    ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				          AND    bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
                  AND   csh.bank_account_num IN (SELECT bank_account_num
				          FROM   ce_bank_accts_gt_v
				          WHERE  bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
				          AND    bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
                  AND     trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                  AND     trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
	                AND     csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
   	              AND  	  nvl(csh.intra_day_flag, 'N') = 'N');

Line: 319

            DELETE FROM CE_ARCH_INTERFACE_HEADERS cah
            WHERE cah.statement_number ||'-'|| cah.bank_account_num IN
	                ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
                   FROM ce_statement_headers_int csh
                  WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
                          (SELECT bb.bank_branch_name
				                  FROM   ce_bank_branches_v bb, ce_bank_accts_gt_v ba
				                  WHERE  bb.branch_party_id = ba.bank_branch_id
				                  AND    ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				                  AND    bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
                  AND   csh.bank_account_num IN
                          (SELECT bank_account_num
				                  FROM ce_bank_accts_gt_v
				                  WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
				                  AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
                  AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                  AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
	                AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	                AND	nvl(csh.intra_day_flag, 'N') = 'N');

Line: 349

            delete FROM CE_ARCH_INTRA_LINES cal
            WHERE cal.statement_header_id IN (
	          SELECT cah.statement_header_id
	          FROM ce_intra_stmt_headers csh , CE_ARCH_INTRA_HEADERS cah
	          WHERE cah.statement_number = csh.statement_number
            AND cah.org_id = csh.org_id
            AND cah.bank_account_id = csh.bank_account_id
            AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)
            AND csh.bank_account_id IN
            			(SELECT bank_account_id
             		  FROM ce_bank_accts_gt_v ba
               		WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
     	            AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
     	            AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));

Line: 368

          DELETE from CE_ARCH_INTRA_HEADERS cah2
          WHERE cah2.statement_header_id IN (
	          SELECT cah.statement_header_id
	          FROM ce_intra_stmt_headers csh , CE_ARCH_INTRA_HEADERS cah
	          WHERE cah.statement_number = csh.statement_number
            AND cah.org_id = csh.org_id
            AND cah.bank_account_id = csh.bank_account_id
            AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)
            AND csh.bank_account_id IN
            			(SELECT bank_account_id
             		  FROM ce_bank_accts_gt_v ba
               		WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
     	            AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
     	            AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));

Line: 392

              DELETE FROM CE_ARCH_INTERFACE_LINES cal
              WHERE cal.statement_number ||'-'|| cal.bank_account_num IN
	                ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
	                      FROM	 ce_statement_headers_int csh
                        WHERE  NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (
                                    SELECT bb.bank_branch_name
				                            FROM   ce_bank_branches_v bb, ce_bank_accts_gt_v ba
				                            WHERE  bb.branch_party_id = ba.bank_branch_id
				                            AND    ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				                            AND    bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
                        AND   csh.bank_account_num IN
                                    (SELECT bank_account_num
				                            FROM   ce_bank_accts_gt_v
				                            WHERE  bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
				                            AND    bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
                        AND     trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                        AND     trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
	                      AND     csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	                      AND	    csh.intra_day_flag = 'Y');

Line: 417

              DELETE FROM CE_ARCH_INTERFACE_HEADERS cah
              WHERE cah.statement_number ||'-'|| cah.bank_account_num IN
                        (SELECT csh.statement_number ||'-'|| csh.bank_account_num
	                      FROM	 ce_statement_headers_int csh
                        WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
                              (SELECT bb.bank_branch_name
				                      FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
				                      WHERE bb.branch_party_id = ba.bank_branch_id
				                      AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				                      AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
                              AND csh.bank_account_num IN
                                            (SELECT bank_account_num
				                                    FROM ce_bank_accts_gt_v
				                                    WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
				                                    AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
                         AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                         AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
	                       AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	                       AND csh.intra_day_flag = 'Y');

Line: 445

END delete_duplicate_data;

Line: 449

|	 delete_archive_tables 		  			                                    |
|								                                                      	|
|  DESCRIPTION							                                            |
|	 Empty Archive Tables                                                	|
|   	         							                                            |
|								                                                      	|
|  CALLED BY							                                            	|
|	 purge_process                                                    		|
|  REQUIRES							                                              	|
|								                                                      	|
|  HISTORY							                                              	|
|	29-Dec-2010	Created		Rtumati 		                                  	|
 --------------------------------------------------------------------- */

PROCEDURE delete_archive_tables IS

BEGIN
 IF (G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')) THEN
        IF (G_OBJECTS IN ('BOTH','STATEMENT')) THEN

	          DELETE FROM CE_ARCH_RECONCILIATIONS_all
	          where org_id in (select org_id from ce_security_profiles_gt) or
	          legal_entity_id in (select org_id from ce_security_profiles_gt) or
		        REFERENCE_TYPE in ('JE_LINE', 'ROI_LINE','STATEMENT');

Line: 474

            DELETE FROM CE_ARCH_RECON_ERRORS;

Line: 476

	          DELETE FROM CE_ARCH_LINES;

Line: 478

            DELETE FROM CE_ARCH_HEADERS;

Line: 484

  	        DELETE FROM CE_ARCH_INTERFACE_LINES
	          WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN
	            (SELECT BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
	            FROM CE_ARCH_INTERFACE_HEADERS
	            WHERE NVL(INTRA_DAY_FLAG, 'N') = 'N');

Line: 490

	          DELETE FROM CE_ARCH_INTERFACE_HEADERS
  	        WHERE NVL(INTRA_DAY_FLAG, 'N') = 'N';

Line: 499

	          DELETE FROM CE_ARCH_INTRA_HEADERS;

Line: 501

  	        DELETE FROM CE_ARCH_INTRA_LINES;

Line: 507

  	        DELETE FROM CE_ARCH_INTERFACE_LINES
	          WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN
	          (SELECT BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
	          FROM CE_ARCH_INTERFACE_HEADERS
	          WHERE NVL(INTRA_DAY_FLAG, 'N') = 'Y');

Line: 513

	          DELETE FROM CE_ARCH_INTERFACE_HEADERS
        	  WHERE NVL(INTRA_DAY_FLAG, 'N') = 'Y';

Line: 519

END delete_archive_tables;

Line: 554

    INSERT INTO ce_arch_headers (
	  STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
                CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
          SELECT
		STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
		CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
            FROM    ce_statement_headers csh
            WHERE   csh.bank_account_id     = CE_PURGE.G_BANK_ACCOUNT_ID
     	    AND     csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
     	    AND     csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
	    AND not exists
		(select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
		 from ce_statement_headers sh,  ce_statement_lines sl , ce_statement_reconcils_all sr
		 where  sh.statement_header_id = sl.statement_header_id
		 and sl.statement_line_id= sr.statement_line_id
		 and sh.statement_header_id= csh.statement_header_id
		 and sh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID
     	    	 AND     sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
     	    	 AND     sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
		 and sr.CURRENT_RECORD_FLAG = 'Y'
		 and sr.STATUS_FLAG = 'M'
		 and (sr.org_id is not null or sr.legal_entity_id is not null)
		 and not exists
			 (select 1 from ce_security_profiles_gt lbg
			  where lbg.organization_id = sr.org_id or
				sr.legal_entity_id = lbg.organization_id  )	)
        AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS   /*10400780 start*/
                                      (SELECT 1
                                      FROM ce_statement_lines csl, ce_statement_reconcils_all csr
                                      WHERE csl.statement_header_id =  csh.statement_header_id
                                      and csl.statement_line_id= csr.statement_line_id
                                      AND csr.CURRENT_RECORD_FLAG = 'Y'
	                                    and csr.STATUS_FLAG = 'M'
                                      )
                                     )
       ) OR CE_PURGE.G_Only_Unrec = 'N');  /*10400780 end*/

Line: 680

            INSERT INTO ce_arch_headers (
	  	STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
                CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
            SELECT
		STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
		CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
            FROM  ce_statement_headers csh
   	    WHERE csh.bank_account_id IN (SELECT  aba.bank_account_id
					FROM	CE_BANK_ACCTS_GT_V aba
					WHERE   aba.bank_branch_id 	=  CE_PURGE.G_BANK_BRANCH_ID)
            AND     csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
            AND     csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
	    AND not exists
		(select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
		 from ce_statement_headers sh,  ce_statement_lines sl , ce_statement_reconcils_all sr
		 where  sh.statement_header_id = sl.statement_header_id
		 and sl.statement_line_id= sr.statement_line_id
		 and sh.statement_header_id = csh.statement_header_id
		 and sh.bank_account_id IN (SELECT  aba.bank_account_id
					FROM	ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
					WHERE   aba.bank_branch_id 	=   CE_PURGE.G_BANK_BRANCH_ID)
            	 AND     sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
            	 AND     sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
		 and sr.CURRENT_RECORD_FLAG = 'Y'
		 and sr.STATUS_FLAG = 'M'
		 and (sr.org_id is not null or sr.legal_entity_id is not null)
		 and not exists
			 (select 1 from ce_security_profiles_gt lbg
			  where lbg.organization_id = sr.org_id or
			  sr.legal_entity_id = lbg.organization_id  )
		)   AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS   /*10400780 start*/
                                      (SELECT 1
                                      FROM ce_statement_lines csl, ce_statement_reconcils_all csr
                                      WHERE csl.statement_header_id =  csh.statement_header_id
                                      and csl.statement_line_id= csr.statement_line_id
                                      AND csr.CURRENT_RECORD_FLAG = 'Y'
	                                    and csr.STATUS_FLAG = 'M'
                                      )
                                     )
       ) OR CE_PURGE.G_Only_Unrec = 'N');  /*10400780 end*/

Line: 805

            INSERT INTO ce_arch_headers (
	  	STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
                CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
          SELECT
		STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
		CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
            FROM    ce_statement_headers csh
            WHERE   csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
     	    AND   	csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
	    AND not exists
		(select 1
		 from ce_statement_headers sh,  ce_statement_lines sl , ce_statement_reconcils_all sr
		 where  sh.statement_header_id = sl.statement_header_id
		 and sl.statement_line_id= sr.statement_line_id
		 and sh.statement_header_id= csh.statement_header_id
  		 and sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
     	         AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
		 and sr.CURRENT_RECORD_FLAG = 'Y'
		 and sr.STATUS_FLAG = 'M'
		 and (sr.org_id is not null or sr.legal_entity_id is not null)
		 and not exists
			 (select 1 from ce_security_profiles_gt lbg
			  where lbg.organization_id = sr.org_id or  sr.legal_entity_id = lbg.organization_id  )
		)   AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS   /*10400780 start*/
                                      (SELECT 1
                                      FROM ce_statement_lines csl, ce_statement_reconcils_all csr
                                      WHERE csl.statement_header_id =  csh.statement_header_id
                                      and csl.statement_line_id= csr.statement_line_id
                                      AND csr.CURRENT_RECORD_FLAG = 'Y'
	                                    and csr.STATUS_FLAG = 'M'
                                      )
                                     )
       ) OR CE_PURGE.G_Only_Unrec = 'N');  /*10400780 end*/

Line: 924

    INSERT INTO ce_arch_lines
		(STATEMENT_LINE_ID,
		STATEMENT_HEADER_ID,
		LINE_NUMBER,
		TRX_DATE,
		TRX_TYPE,
		AMOUNT,
		CHARGES_AMOUNT,
		STATUS,
		TRX_CODE_ID,
		EFFECTIVE_DATE,
		BANK_TRX_NUMBER,
		TRX_TEXT,
		CUSTOMER_TEXT,
		INVOICE_TEXT,
		CURRENCY_CODE,
		EXCHANGE_RATE_TYPE,
		EXCHANGE_RATE,
		EXCHANGE_RATE_DATE,
		ORIGINAL_AMOUNT,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		RECONCILE_TO_STATEMENT_FLAG)
          SELECT
		STATEMENT_LINE_ID,
		STATEMENT_HEADER_ID,
		LINE_NUMBER,
		TRX_DATE,
		TRX_TYPE,
		AMOUNT,
		CHARGES_AMOUNT,
		STATUS,
		TRX_CODE_ID,
		EFFECTIVE_DATE,
		BANK_TRX_NUMBER,
		TRX_TEXT,
		CUSTOMER_TEXT,
		INVOICE_TEXT,
		CURRENCY_CODE,
		EXCHANGE_RATE_TYPE,
		EXCHANGE_RATE,
		EXCHANGE_RATE_DATE,
		ORIGINAL_AMOUNT,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		RECONCILE_TO_STATEMENT_FLAG
          FROM      ce_statement_lines
          WHERE     statement_header_id IN
            ( SELECT      statement_header_id
             FROM        ce_arch_headers );

Line: 1014

          INSERT INTO ce_arch_reconciliations_all
		(STATEMENT_LINE_ID,
		REFERENCE_TYPE,
		REFERENCE_ID,
		JE_HEADER_ID,
		ORG_ID,
		LEGAL_ENTITY_ID,
		REFERENCE_STATUS,
		STATUS_FLAG,
		ACTION_FLAG,
		CURRENT_RECORD_FLAG,
		AUTO_RECONCILED_FLAG,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		REQUEST_ID,
		PROGRAM_APPLICATION_ID,
		PROGRAM_ID,
		PROGRAM_UPDATE_DATE,
		AMOUNT)
         SELECT
		STATEMENT_LINE_ID,
		REFERENCE_TYPE,
		REFERENCE_ID,
		JE_HEADER_ID,
		ORG_ID,
		LEGAL_ENTITY_ID,
		REFERENCE_STATUS,
		STATUS_FLAG,
		ACTION_FLAG,
		CURRENT_RECORD_FLAG,
		AUTO_RECONCILED_FLAG,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		REQUEST_ID,
		PROGRAM_APPLICATION_ID,
		PROGRAM_ID,
		PROGRAM_UPDATE_DATE,
		AMOUNT
            FROM    ce_statement_recon_gt_v  --ce_statement_reconciliations
        WHERE     statement_line_id IN
          ( SELECT      statement_line_id
            FROM        ce_arch_lines );

Line: 1062

        INSERT INTO ce_arch_recon_errors
		(STATEMENT_LINE_ID,
		MESSAGE_NAME,
		CREATION_DATE,
		CREATED_BY,
		APPLICATION_SHORT_NAME,
		STATEMENT_HEADER_ID)
        SELECT
		STATEMENT_LINE_ID,
		MESSAGE_NAME,
		CREATION_DATE,
		CREATED_BY,
		APPLICATION_SHORT_NAME,
		STATEMENT_HEADER_ID
        FROM      ce_reconciliation_errors
        WHERE     statement_line_id IN
          ( SELECT      statement_line_id
            FROM        ce_arch_lines );

Line: 1104

  INSERT INTO ce_arch_interface_headers
  	(STATEMENT_NUMBER,
		BANK_ACCOUNT_NUM,
		STATEMENT_DATE,
		BANK_NAME,
		BANK_BRANCH_NAME,
    CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CONTROL_LINE_COUNT,
		RECORD_STATUS_FLAG,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
    --ORG_ID
    INTRA_DAY_FLAG)
    SELECT
		STATEMENT_NUMBER,
		BANK_ACCOUNT_NUM,
		STATEMENT_DATE,
		BANK_NAME,
		BANK_BRANCH_NAME,
    CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CONTROL_LINE_COUNT,
		RECORD_STATUS_FLAG,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
    INTRA_DAY_FLAG
		--ORG_ID
    FROM    ce_statement_headers_int csh
	  WHERE	NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
					  FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
					  WHERE	bb.branch_party_id = ba.bank_branch_id
					  AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				    AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
	  AND	csh.bank_account_num IN (SELECT bank_account_num
					  FROM ce_bank_accts_gt_v
					  WHERE	bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
					  AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
            AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
            AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
            AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	  AND	nvl(csh.intra_day_flag, 'N') = p_intra_day_flag ;

Line: 1210

    INSERT INTO ce_arch_interface_lines
		(EXCHANGE_RATE_DATE,
		EXCHANGE_RATE,
		BANK_TRX_NUMBER,
		CUSTOMER_TEXT,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		ORIGINAL_AMOUNT,
		BANK_ACCOUNT_NUM,
		STATEMENT_NUMBER,
		LINE_NUMBER,
		TRX_DATE,
		TRX_CODE,
		EFFECTIVE_DATE,
		TRX_TEXT,
		INVOICE_TEXT,
		AMOUNT,
		CHARGES_AMOUNT,
		CURRENCY_CODE,
		USER_EXCHANGE_RATE_TYPE)
    SELECT
		EXCHANGE_RATE_DATE,
		EXCHANGE_RATE,
		BANK_TRX_NUMBER,
		CUSTOMER_TEXT,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		ORIGINAL_AMOUNT,
		BANK_ACCOUNT_NUM,
		STATEMENT_NUMBER,
		LINE_NUMBER,
		TRX_DATE,
		TRX_CODE,
		EFFECTIVE_DATE,
		TRX_TEXT,
		INVOICE_TEXT,
		AMOUNT,
		CHARGES_AMOUNT,
		CURRENCY_CODE,
		USER_EXCHANGE_RATE_TYPE
          FROM ce_statement_lines_interface csl
          WHERE csl.statement_number ||'-'|| csl.bank_account_num IN (
          SELECT csh.statement_number ||'-'|| csh.bank_account_num
	        FROM	 ce_statement_headers_int csh
          WHERE	NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
				 	  FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
					  WHERE	bb.branch_party_id = ba.bank_branch_id
					  AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				    AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
	          AND	csh.bank_account_num IN (SELECT cv.bank_account_num
					  FROM ce_bank_accts_gt_v cv
					  WHERE	cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)
					  AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))
            AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
            AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
            AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	          AND	nvl(csh.intra_day_flag, 'N') = p_intra_day_flag);

Line: 1333

            INSERT INTO ce_arch_intra_headers (
	  	STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
                CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
          SELECT
		STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
		CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
            FROM    ce_intra_stmt_headers csh
            WHERE   csh.bank_account_id     = G_BANK_ACCOUNT_ID
     	    AND     trunc(csh.statement_date) >= NVL(G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
     	    AND     trunc(csh.statement_date) <= Nvl(G_STATEMENT_DATE_TO, trunc(csh.statement_date));

Line: 1429

    INSERT INTO ce_arch_intra_headers (
	  STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
    CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
    SELECT
		STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
		CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
    FROM  ce_intra_stmt_headers csh
   	    WHERE csh.bank_account_id IN (SELECT  aba.bank_account_id
					FROM	ce_BANK_ACCOUNTS aba
					WHERE   aba.bank_branch_id 	=  G_BANK_BRANCH_ID)
            AND     trunc(csh.statement_date) >= NVL(G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
            AND     trunc(csh.statement_date) <= NVL(G_STATEMENT_DATE_TO, trunc(csh.statement_date));

Line: 1527

    INSERT INTO ce_arch_intra_headers (
	  STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
    CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
    SELECT
		STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
		CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
            FROM    ce_intra_stmt_headers csh
            WHERE   trunc(csh.statement_date) >= NVL(G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
     	        AND   	trunc(csh.statement_date) <= NVL(G_STATEMENT_DATE_TO, trunc(csh.statement_date));

Line: 1623

    INSERT INTO ce_arch_intra_lines
		(STATEMENT_LINE_ID,
		STATEMENT_HEADER_ID,
		LINE_NUMBER,
		TRX_DATE,
		TRX_TYPE,
		AMOUNT,
		CHARGES_AMOUNT,
		STATUS,
		TRX_CODE_ID,
		EFFECTIVE_DATE,
		BANK_TRX_NUMBER,
		TRX_TEXT,
		CUSTOMER_TEXT,
		INVOICE_TEXT,
		CURRENCY_CODE,
		EXCHANGE_RATE_TYPE,
		EXCHANGE_RATE,
		EXCHANGE_RATE_DATE,
		ORIGINAL_AMOUNT,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		RECONCILE_TO_STATEMENT_FLAG)
    SELECT
		STATEMENT_LINE_ID,
		STATEMENT_HEADER_ID,
		LINE_NUMBER,
		TRX_DATE,
		TRX_TYPE,
		AMOUNT,
		CHARGES_AMOUNT,
		STATUS,
		TRX_CODE_ID,
		EFFECTIVE_DATE,
		BANK_TRX_NUMBER,
		TRX_TEXT,
		CUSTOMER_TEXT,
		INVOICE_TEXT,
		CURRENCY_CODE,
		EXCHANGE_RATE_TYPE,
		EXCHANGE_RATE,
		EXCHANGE_RATE_DATE,
		ORIGINAL_AMOUNT,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		RECONCILE_TO_STATEMENT_FLAG
          FROM      ce_intra_stmt_lines
          WHERE     statement_header_id IN
            ( SELECT      statement_header_id
             FROM        ce_arch_intra_headers );

Line: 1749

	-- 2) Sub-query (not exists) should select from ce_bank_accounts table,
	--	This query is to select all stmts that the users do not have access to.
	--	If stmts exists in this subquery then the stmt should not be deleted.
 	--	This is to ensure that there are no orphan trx data in the system

  -- Cursor to purge the statement Headers
  CURSOR C_PURGE_HEADERS IS
	SELECT csh.statement_header_id
	FROM ce_statement_headers csh
	WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
  AND  	csh.bank_account_id IN
            			              (SELECT bank_account_id
             		  	            FROM ce_bank_accts_gt_v ba
               		  	          WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
  AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
  AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
 	AND not exists
		                            (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
		                            from ce_statement_headers sh,  ce_statement_lines sl , ce_statement_reconcils_all sr
		                            where  sh.statement_header_id = sl.statement_header_id
		                            and sl.statement_line_id= sr.statement_line_id
                               -- AND sl.status = Decode(G_Only_Unrec,'Y','RECONCILED',sl.status)
		                            and sh.statement_header_id= csh.statement_header_id
		                            and sh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
     	  	                      AND sh.bank_account_id IN
            			                            (SELECT bank_account_id
             		  	                          FROM ce_bank_accounts ba --ce_bank_accts_gt_v ba
               		  	                        WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
     	  	                      AND trunc(sh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(sh.statement_date))
     	  	                      AND trunc(sh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(sh.statement_date))
		                            and sr.CURRENT_RECORD_FLAG = 'Y'
		                            and sr.STATUS_FLAG = 'M'
		                            and (sr.org_id is not null or sr.legal_entity_id is not null)
		                            AND not exists
			                                        (select 1 from ce_security_profiles_gt lbg
			                                         where lbg.organization_id = sr.org_id or  sr.legal_entity_id = lbg.organization_id  )
                                )
    AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS  /* 10400780  start*/
                                      (SELECT 1
                                      FROM ce_statement_lines csl, ce_statement_reconcils_all csr
                                      WHERE csl.statement_header_id =  csh.statement_header_id
                                      and csl.statement_line_id= csr.statement_line_id
                                      AND csr.CURRENT_RECORD_FLAG = 'Y'
	                                    and csr.STATUS_FLAG = 'M'
                                      )
                                     )
       ) OR CE_PURGE.G_Only_Unrec = 'N');  /*10400780 end*/

Line: 1800

	SELECT csl.statement_line_id
	FROM ce_statement_lines csl
	WHERE csl.statement_header_id = X_header_id;

Line: 1806

	SELECT csh.statement_header_id
	FROM ce_intra_stmt_headers csh
	WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
  AND csh.bank_account_id IN
            			(SELECT bank_account_id
             		  FROM ce_bank_accts_gt_v ba
               		WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
     	            AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
     	            AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date));

Line: 1818

	SELECT csl.statement_line_id
	FROM ce_intra_stmt_lines csl
	WHERE csl.statement_header_id = X_header_id;

Line: 1829

      IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'DELETE') THEN
        delete_archive_tables;

Line: 1831

      ELSIF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'APPEND') THEN
        delete_duplicate_data;

Line: 1852

                    DELETE FROM ce_statement_headers
                    WHERE statement_header_id = X_header_id;

Line: 1861

                      DELETE FROM ce_statement_lines
                      WHERE statement_header_id = X_header_id;

Line: 1866

                      DELETE FROM ce_statement_reconcils_all
                      WHERE statement_line_id = X_line_id
                      and (org_id in (select organization_id from ce_security_profiles_gt) or
	                    legal_entity_id in (select organization_id from ce_security_profiles_gt) or
		                  REFERENCE_TYPE in ('JE_LINE', 'ROI_LINE','STATEMENT')
		                  );

Line: 1875

                      DELETE FROM ce_reconciliation_errors
                      WHERE statement_line_id = X_line_id;

Line: 1900

                  DELETE FROM ce_statement_lines_interface csl
                    WHERE csl.statement_number ||'-'|| csl.bank_account_num IN
	                ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
	                FROM	 ce_statement_headers_int csh
                      WHERE  NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
				                FROM   ce_bank_branches_v bb, ce_bank_accts_gt_v ba
				                WHERE  bb.branch_party_id = ba.bank_branch_id
				                AND    ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				                AND    bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
                      AND   csh.bank_account_num IN (SELECT bank_account_num
				                FROM   ce_bank_accts_gt_v
				                WHERE  bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
				                AND    bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
                      AND     trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                      AND     trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
	                 AND     csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
   	               AND  	  nvl(csh.intra_day_flag, 'N') = 'N');

Line: 1920

                  DELETE FROM ce_statement_headers_int csh
                  WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
                          (SELECT bb.bank_branch_name
				                  FROM   ce_bank_branches_v bb, ce_bank_accts_gt_v ba
				                  WHERE  bb.branch_party_id = ba.bank_branch_id
				                  AND    ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				                  AND    bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
                  AND   csh.bank_account_num IN
                          (SELECT bank_account_num
				                  FROM ce_bank_accts_gt_v
				                  WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
				                  AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
                  AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                  AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
	                AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	                AND	nvl(csh.intra_day_flag, 'N') = 'N';

Line: 1961

                      DELETE FROM ce_intra_stmt_headers
                      WHERE statement_header_id = X_header_id;

Line: 1970

                        DELETE FROM ce_intra_stmt_lines
                        WHERE statement_header_id = X_header_id;

Line: 1995

              DELETE FROM ce_statement_lines_interface csl
              WHERE csl.statement_number ||'-'|| csl.bank_account_num IN
	                    ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
	                      FROM	 ce_statement_headers_int csh
                        WHERE  NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
				                FROM   ce_bank_branches_v bb, ce_bank_accts_gt_v ba
				                WHERE  bb.branch_party_id = ba.bank_branch_id
				                AND    ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				                AND    bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
                        AND   csh.bank_account_num IN
                                            (SELECT bank_account_num
				                                    FROM   ce_bank_accts_gt_v
				                                    WHERE  bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
				                                    AND    bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
                                            AND     trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                                            AND     trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
	                                          AND     csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	                                          AND	  csh.intra_day_flag = 'Y');

Line: 2016

              DELETE FROM ce_statement_headers_int csh
              WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
                              (SELECT bb.bank_branch_name
				                      FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
				                      WHERE bb.branch_party_id = ba.bank_branch_id
				                      AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				                      AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
                              AND csh.bank_account_num IN
                                            (SELECT bank_account_num
				                                    FROM ce_bank_accts_gt_v
				                                    WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
				                                    AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
                                            AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
                                            AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
	                                          AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	                                          AND csh.intra_day_flag = 'Y';

Line: 2059

 SELECT
		STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
		CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
    FROM    ce_arch_headers csh
    WHERE  (  csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
                      OR
                    (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
                    AND (csh.bank_account_id  in (SELECT  aba.bank_account_id
					                                        FROM	ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
					                                        WHERE   aba.bank_branch_id 	= Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
    AND     csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
    AND     csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
	  AND not exists
		(select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
		 from ce_arch_headers sh,  ce_arch_lines sl , ce_arch_reconciliations_all sr
		 where  sh.statement_header_id = sl.statement_header_id
		 and sl.statement_line_id= sr.statement_line_id
		 and sh.statement_header_id= csh.statement_header_id
		 and  ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
                      OR
                      (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
                      AND (sh.bank_account_id  in (SELECT  aba.bank_account_id
					                FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
					                WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
     AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
     AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
		 and sr.CURRENT_RECORD_FLAG = 'Y'
		 and sr.STATUS_FLAG = 'M'
		 and (sr.org_id is not null or sr.legal_entity_id is not null)
		 and not exists
			 (select 1 from ce_security_profiles_gt lbg
			  where lbg.organization_id = sr.org_id or
				sr.legal_entity_id = lbg.organization_id  )	)
        FOR update;

Line: 2156

 SELECT  Bank_Account_Name,BANK_ACCOUNT_NUM, currency_code
    INTO    l_bank_account_name,  l_bank_account_num , l_currency_code
 FROM ce_bank_accounts
 WHERE bank_account_id =  r_stmt.bank_account_id;

Line: 2161

    INSERT INTO ce_statement_headers (
	  STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
    CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
    VALUES(r_stmt.STATEMENT_COMPLETE_FLAG,
--r_stmt.ORG_ID,
r_stmt.DOC_SEQUENCE_ID,
r_stmt.DOC_SEQUENCE_VALUE,
r_stmt.STATEMENT_HEADER_ID,
r_stmt.BANK_ACCOUNT_ID,
r_stmt.STATEMENT_NUMBER,
r_stmt.STATEMENT_DATE,
r_stmt.AUTO_LOADED_FLAG,
r_stmt.GL_DATE,
r_stmt.CHECK_DIGITS,
r_stmt.CONTROL_BEGIN_BALANCE,
r_stmt.CONTROL_TOTAL_DR,
r_stmt.CONTROL_TOTAL_CR,
r_stmt.CONTROL_END_BALANCE,
r_stmt.CASHFLOW_BALANCE,
r_stmt.INT_CALC_BALANCE,
r_stmt.ONE_DAY_FLOAT,
r_stmt.TWO_DAY_FLOAT,
r_stmt.CONTROL_DR_LINE_COUNT,
r_stmt.CONTROL_CR_LINE_COUNT,
r_stmt.CURRENCY_CODE,
r_stmt.ATTRIBUTE_CATEGORY,
r_stmt.ATTRIBUTE1,
r_stmt.ATTRIBUTE2,
r_stmt.ATTRIBUTE3,
r_stmt.ATTRIBUTE4,
r_stmt.ATTRIBUTE5,
r_stmt.ATTRIBUTE6,
r_stmt.ATTRIBUTE7,
r_stmt.ATTRIBUTE8,
r_stmt.ATTRIBUTE9,
r_stmt.ATTRIBUTE10,
r_stmt.ATTRIBUTE11,
r_stmt.ATTRIBUTE12,
r_stmt.ATTRIBUTE13,
r_stmt.ATTRIBUTE14,
r_stmt.ATTRIBUTE15,
r_stmt.LAST_UPDATE_LOGIN,
r_stmt.CREATED_BY,
r_stmt.CREATION_DATE,
r_stmt.LAST_UPDATED_BY,
r_stmt.LAST_UPDATE_DATE);

Line: 2253

    INSERT INTO ce_statement_lines
		(STATEMENT_LINE_ID,
		STATEMENT_HEADER_ID,
		LINE_NUMBER,
		TRX_DATE,
		TRX_TYPE,
		AMOUNT,
		CHARGES_AMOUNT,
		STATUS,
		TRX_CODE_ID,
		EFFECTIVE_DATE,
		BANK_TRX_NUMBER,
		TRX_TEXT,
		CUSTOMER_TEXT,
		INVOICE_TEXT,
		CURRENCY_CODE,
		EXCHANGE_RATE_TYPE,
		EXCHANGE_RATE,
		EXCHANGE_RATE_DATE,
		ORIGINAL_AMOUNT,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		RECONCILE_TO_STATEMENT_FLAG)
          SELECT
		STATEMENT_LINE_ID,
		STATEMENT_HEADER_ID,
		LINE_NUMBER,
		TRX_DATE,
		TRX_TYPE,
		AMOUNT,
		CHARGES_AMOUNT,
		STATUS,
		TRX_CODE_ID,
		EFFECTIVE_DATE,
		BANK_TRX_NUMBER,
		TRX_TEXT,
		CUSTOMER_TEXT,
		INVOICE_TEXT,
		CURRENCY_CODE,
		EXCHANGE_RATE_TYPE,
		EXCHANGE_RATE,
		EXCHANGE_RATE_DATE,
		ORIGINAL_AMOUNT,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		RECONCILE_TO_STATEMENT_FLAG
          FROM      ce_arch_lines
          WHERE     statement_header_id = r_stmt.STATEMENT_HEADER_ID;

Line: 2345

    INSERT INTO ce_statement_reconcils_all
		(STATEMENT_LINE_ID,
		REFERENCE_TYPE,
		REFERENCE_ID,
		JE_HEADER_ID,
		ORG_ID,
		LEGAL_ENTITY_ID,
		REFERENCE_STATUS,
		STATUS_FLAG,
		ACTION_FLAG,
		CURRENT_RECORD_FLAG,
		AUTO_RECONCILED_FLAG,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		REQUEST_ID,
		PROGRAM_APPLICATION_ID,
		PROGRAM_ID,
		PROGRAM_UPDATE_DATE,
		AMOUNT)
         SELECT
		STATEMENT_LINE_ID,
		REFERENCE_TYPE,
		REFERENCE_ID,
		JE_HEADER_ID,
		ORG_ID,
		LEGAL_ENTITY_ID,
		REFERENCE_STATUS,
		STATUS_FLAG,
		ACTION_FLAG,
		CURRENT_RECORD_FLAG,
		AUTO_RECONCILED_FLAG,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		REQUEST_ID,
		PROGRAM_APPLICATION_ID,
		PROGRAM_ID,
		PROGRAM_UPDATE_DATE,
		AMOUNT
    FROM   ce_arch_reconciliations_all   --ce_statement_reconciliations
    WHERE     statement_line_id IN
    ( SELECT      statement_line_id
     FROM        ce_arch_lines
     WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID );

Line: 2397

    INSERT INTO ce_reconciliation_errors
		(STATEMENT_LINE_ID,
		MESSAGE_NAME,
		CREATION_DATE,
		CREATED_BY,
		APPLICATION_SHORT_NAME,
		STATEMENT_HEADER_ID)
    SELECT
		STATEMENT_LINE_ID,
		MESSAGE_NAME,
		CREATION_DATE,
		CREATED_BY,
		APPLICATION_SHORT_NAME,
		STATEMENT_HEADER_ID
    FROM ce_arch_recon_errors
    WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID;

Line: 2429

     /*Delete Restored Statements*/

     DELETE FROM  ce_arch_reconciliations_all   --ce_statement_reconciliations
     WHERE     statement_line_id IN
     ( SELECT      statement_line_id
     FROM        ce_arch_lines
     WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID );

Line: 2437

     DELETE FROM ce_arch_recon_errors
     WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID;

Line: 2440

     DELETE FROM  ce_arch_lines
     WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID;

Line: 2443

     DELETE FROM   ce_arch_headers
     WHERE CURRENT OF  C_restore_stmts;

Line: 2489

    SELECT
		STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
		CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
    FROM  ce_arch_intra_headers csh
   	    WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
              OR
            (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
              AND  csh.bank_account_id IN (SELECT  aba.bank_account_id
				                          	FROM	ce_BANK_ACCOUNTS aba
					                          WHERE aba.bank_branch_id 	=  Nvl(CE_PURGE.G_BANK_BRANCH_ID,aba.bank_branch_id))))
            AND     trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
            AND     trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
            FOR update;

Line: 2563

    SELECT  Bank_Account_Name,BANK_ACCOUNT_NUM, currency_code
    INTO    l_bank_account_name,  l_bank_account_num , l_currency_code
    FROM    ce_bank_accounts
    WHERE bank_account_id =  r_intraStmt.bank_account_id;

Line: 2568

    INSERT INTO ce_intra_stmt_headers (
	  STATEMENT_COMPLETE_FLAG,
		--ORG_ID,
		DOC_SEQUENCE_ID,
		DOC_SEQUENCE_VALUE,
		STATEMENT_HEADER_ID,
		BANK_ACCOUNT_ID,
		STATEMENT_NUMBER,
		STATEMENT_DATE,
		AUTO_LOADED_FLAG,
		GL_DATE,
    CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE)
  VALUES
  (r_intraStmt.STATEMENT_COMPLETE_FLAG,
r_intraStmt.DOC_SEQUENCE_ID,
r_intraStmt.DOC_SEQUENCE_VALUE,
r_intraStmt.STATEMENT_HEADER_ID,
r_intraStmt.BANK_ACCOUNT_ID,
r_intraStmt.STATEMENT_NUMBER,
r_intraStmt.STATEMENT_DATE,
r_intraStmt.AUTO_LOADED_FLAG,
r_intraStmt.GL_DATE,
r_intraStmt.CHECK_DIGITS,
r_intraStmt.CONTROL_BEGIN_BALANCE,
r_intraStmt.CONTROL_TOTAL_DR,
r_intraStmt.CONTROL_TOTAL_CR,
r_intraStmt.CONTROL_END_BALANCE,
r_intraStmt.CASHFLOW_BALANCE,
r_intraStmt.INT_CALC_BALANCE,
r_intraStmt.ONE_DAY_FLOAT,
r_intraStmt.TWO_DAY_FLOAT,
r_intraStmt.CONTROL_DR_LINE_COUNT,
r_intraStmt.CONTROL_CR_LINE_COUNT,
r_intraStmt.CURRENCY_CODE,
r_intraStmt.ATTRIBUTE_CATEGORY,
r_intraStmt.ATTRIBUTE1,
r_intraStmt.ATTRIBUTE2,
r_intraStmt.ATTRIBUTE3,
r_intraStmt.ATTRIBUTE4,
r_intraStmt.ATTRIBUTE5,
r_intraStmt.ATTRIBUTE6,
r_intraStmt.ATTRIBUTE7,
r_intraStmt.ATTRIBUTE8,
r_intraStmt.ATTRIBUTE9,
r_intraStmt.ATTRIBUTE10,
r_intraStmt.ATTRIBUTE11,
r_intraStmt.ATTRIBUTE12,
r_intraStmt.ATTRIBUTE13,
r_intraStmt.ATTRIBUTE14,
r_intraStmt.ATTRIBUTE15,
r_intraStmt.LAST_UPDATE_LOGIN,
r_intraStmt.CREATED_BY,
r_intraStmt.CREATION_DATE,
r_intraStmt.LAST_UPDATED_BY,
r_intraStmt.LAST_UPDATE_DATE);

Line: 2663

    INSERT INTO  ce_intra_stmt_lines
		(STATEMENT_LINE_ID,
		STATEMENT_HEADER_ID,
		LINE_NUMBER,
		TRX_DATE,
		TRX_TYPE,
		AMOUNT,
		CHARGES_AMOUNT,
		STATUS,
		TRX_CODE_ID,
		EFFECTIVE_DATE,
		BANK_TRX_NUMBER,
		TRX_TEXT,
		CUSTOMER_TEXT,
		INVOICE_TEXT,
		CURRENCY_CODE,
		EXCHANGE_RATE_TYPE,
		EXCHANGE_RATE,
		EXCHANGE_RATE_DATE,
		ORIGINAL_AMOUNT,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		RECONCILE_TO_STATEMENT_FLAG)
    SELECT
		STATEMENT_LINE_ID,
		STATEMENT_HEADER_ID,
		LINE_NUMBER,
		TRX_DATE,
		TRX_TYPE,
		AMOUNT,
		CHARGES_AMOUNT,
		STATUS,
		TRX_CODE_ID,
		EFFECTIVE_DATE,
		BANK_TRX_NUMBER,
		TRX_TEXT,
		CUSTOMER_TEXT,
		INVOICE_TEXT,
		CURRENCY_CODE,
		EXCHANGE_RATE_TYPE,
		EXCHANGE_RATE,
		EXCHANGE_RATE_DATE,
		ORIGINAL_AMOUNT,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		LAST_UPDATE_LOGIN,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		RECONCILE_TO_STATEMENT_FLAG
          FROM      ce_arch_intra_lines
          WHERE     statement_header_id = r_intraStmt.statement_header_id;

Line: 2763

    /* Delete Restored Lines   */
    DELETE FROM ce_arch_intra_lines
    WHERE statement_header_id = r_intraStmt.statement_header_id;

Line: 2767

    DELETE FROM ce_arch_intra_headers
    WHERE CURRENT OF  C_Restore_Intra_Stmts;

Line: 2817

     SELECT
		STATEMENT_NUMBER,
		BANK_ACCOUNT_NUM,
		STATEMENT_DATE,
		BANK_NAME,
		BANK_BRANCH_NAME,
    CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CONTROL_LINE_COUNT,
		RECORD_STATUS_FLAG,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
    intra_day_flag,
		ORG_ID
    FROM  ce_arch_interface_headers   csh
	  WHERE	NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
					  FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
					  WHERE	bb.branch_party_id = ba.bank_branch_id
					  AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
				    AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
	          AND	csh.bank_account_num IN (SELECT bank_account_num
					  FROM ce_bank_accts_gt_v
					  WHERE	bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
					  AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
            AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
            AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
            AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
	  AND	nvl(csh.intra_day_flag, 'N') = p_intra_day_flag
    FOR update;

Line: 2909

                SELECT Bank_Account_Name
                INTO l_bank_account_name
                FROM ce_bank_accounts ce
                WHERE ce.bank_account_num = r_interface.bank_account_num
                AND ce.bank_branch_id=(SELECT Branch_party_id
                                    FROM ce_bank_Branches_v
                                    WHERE bank_branch_name = Nvl(r_interface.BANK_BRANCH_NAME,G_BANK_BRANCH_NAME))
                AND ce.currency_code=r_interface.CURRENCY_CODE ;

Line: 2931

    INSERT INTO ce_statement_headers_int
  	(STATEMENT_NUMBER,
		BANK_ACCOUNT_NUM,
		STATEMENT_DATE,
		BANK_NAME,
		BANK_BRANCH_NAME,
    CHECK_DIGITS,
		CONTROL_BEGIN_BALANCE,
		CONTROL_TOTAL_DR,
		CONTROL_TOTAL_CR,
		CONTROL_END_BALANCE,
		CASHFLOW_BALANCE,
		INT_CALC_BALANCE,
		ONE_DAY_FLOAT,
		TWO_DAY_FLOAT,
		CONTROL_DR_LINE_COUNT,
		CONTROL_CR_LINE_COUNT,
		CONTROL_LINE_COUNT,
		RECORD_STATUS_FLAG,
		CURRENCY_CODE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
    intra_day_flag)
		VALUES ( r_interface.STATEMENT_NUMBER,
    r_interface.BANK_ACCOUNT_NUM,
    r_interface.STATEMENT_DATE,
    r_interface.BANK_NAME,
    r_interface.BANK_BRANCH_NAME,
    r_interface.CHECK_DIGITS,
    r_interface.CONTROL_BEGIN_BALANCE,
    r_interface.CONTROL_TOTAL_DR,
    r_interface.CONTROL_TOTAL_CR,
    r_interface.CONTROL_END_BALANCE,
    r_interface.CASHFLOW_BALANCE,
    r_interface.INT_CALC_BALANCE,
    r_interface.ONE_DAY_FLOAT,
    r_interface.TWO_DAY_FLOAT,
    r_interface.CONTROL_DR_LINE_COUNT,
    r_interface.CONTROL_CR_LINE_COUNT,
    r_interface.CONTROL_LINE_COUNT,
    r_interface.RECORD_STATUS_FLAG,
    r_interface.CURRENCY_CODE,
    r_interface.ATTRIBUTE_CATEGORY,
    r_interface.ATTRIBUTE1,
    r_interface.ATTRIBUTE2,
    r_interface.ATTRIBUTE3,
    r_interface.ATTRIBUTE4,
    r_interface.ATTRIBUTE5,
    r_interface.ATTRIBUTE6,
    r_interface.ATTRIBUTE7,
    r_interface.ATTRIBUTE8,
    r_interface.ATTRIBUTE9,
    r_interface.ATTRIBUTE10,
    r_interface.ATTRIBUTE11,
    r_interface.ATTRIBUTE12,
    r_interface.ATTRIBUTE13,
    r_interface.ATTRIBUTE14,
    r_interface.ATTRIBUTE15,
    r_interface.CREATED_BY,
    r_interface.CREATION_DATE,
    r_interface.LAST_UPDATED_BY,
    r_interface.LAST_UPDATE_DATE,
    r_interface.intra_day_flag);

Line: 3021

    INSERT INTO ce_statement_lines_interface
		(EXCHANGE_RATE_DATE,
		EXCHANGE_RATE,
		BANK_TRX_NUMBER,
		CUSTOMER_TEXT,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		ORIGINAL_AMOUNT,
		BANK_ACCOUNT_NUM,
		STATEMENT_NUMBER,
		LINE_NUMBER,
		TRX_DATE,
		TRX_CODE,
		EFFECTIVE_DATE,
		TRX_TEXT,
		INVOICE_TEXT,
		AMOUNT,
		CHARGES_AMOUNT,
		CURRENCY_CODE,
		USER_EXCHANGE_RATE_TYPE)
    SELECT
		EXCHANGE_RATE_DATE,
		EXCHANGE_RATE,
		BANK_TRX_NUMBER,
		CUSTOMER_TEXT,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		ORIGINAL_AMOUNT,
		BANK_ACCOUNT_NUM,
		STATEMENT_NUMBER,
		LINE_NUMBER,
		TRX_DATE,
		TRX_CODE,
		EFFECTIVE_DATE,
		TRX_TEXT,
		INVOICE_TEXT,
		AMOUNT,
		CHARGES_AMOUNT,
		CURRENCY_CODE,
		USER_EXCHANGE_RATE_TYPE
          FROM ce_arch_interface_lines csl
          WHERE csl.statement_number ||'-'|| csl.bank_account_num =
           r_interface.statement_number ||'-'|| r_interface.bank_account_num;

Line: 3129

   DELETE FROM ce_arch_interface_headers
   WHERE CURRENT OF C_Restore_interface;

Line: 3132

   DELETE FROM ce_arch_interface_lines csl
   WHERE csl.statement_number ||'-'|| csl.bank_account_num =
   r_interface.statement_number ||'-'|| r_interface.bank_account_num;

Line: 3229

  /* If this is deleted, also delete SRWEXIT in After Report Trigger! */

   G_BANK_ACCOUNT_ID           :=    p_BANK_ACCOUNT_ID;

Line: 3240

      SELECT meaning INTO G_status
                    FROM ce_lookups
                    WHERE lookup_type = 'HEADER_INTERFACE_STATUS'
                    AND LOOKUP_CODE = G_hdr_int_status;

Line: 3264

          SELECT bb.bank_name,
	        bb.bank_branch_name,
	        ba.bank_account_name,
	        ba.bank_account_num,
	        ba.currency_code
          INTO   G_Bank_Name,
	        G_Bank_Branch_Name,
	        G_Bank_Account_Name,
	        G_Bank_Account_Num,
	        G_Currency_Code
          FROM   ce_bank_branches_v bb, ce_bank_accts_gt_v ba
          WHERE  ba.bank_account_id = G_BANK_ACCOUNT_ID
          AND    bb.branch_party_id = ba.bank_branch_id;

Line: 3280

          SELECT bb.bank_name,
	        bb.bank_branch_name
          INTO   G_Bank_Name,
	        G_Bank_Branch_Name
          FROM   ce_bank_branches_v bb
          WHERE  bb.branch_party_id = G_BANK_BRANCH_ID;

Line: 3292

          SELECT  l.meaning
          INTO	l_dummy
          FROM	gl_sets_of_books gl,
		      ce_system_parameters cb,
		      ce_lookups l
          WHERE	gl.set_of_books_id = cb.set_of_books_id AND
		      l.lookup_type = 'LITERAL'		AND
		      l.lookup_code = 'ALL' and rownum = 1;

Line: 3309

        SELECT BANK_BRANCH_ID
        INTO   l_bank_branch_id
        FROM   CE_BANK_ACCTS_GT_V
        WHERE  BANK_ACCOUNT_ID = G_BANK_ACCOUNT_ID;