[Home] [Help]
546:
547:
548: -- bug 3676745 in order to archive/purge stmts, the user
549: -- must have access to all the reconciled trx's
550: -- org (ce_statement_reconcils_all.org_id and ce_statement_reconcils_all.legal_entity_id)
551: -- This is to ensure that there are no orphan trx data in the system
552:
553:
554: INSERT INTO ce_arch_headers (
644: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
645: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
646: AND not exists
647: (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
648: from ce_statement_headers sh, ce_statement_lines sl , ce_statement_reconcils_all sr
649: where sh.statement_header_id = sl.statement_header_id
650: and sl.statement_line_id= sr.statement_line_id
651: and sh.statement_header_id= csh.statement_header_id
652: and sh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID
660: where lbg.organization_id = sr.org_id or
661: sr.legal_entity_id = lbg.organization_id ) )
662: AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/
663: (SELECT 1
664: FROM ce_statement_lines csl, ce_statement_reconcils_all csr
665: WHERE csl.statement_header_id = csh.statement_header_id
666: and csl.statement_line_id= csr.statement_line_id
667: AND csr.CURRENT_RECORD_FLAG = 'Y'
668: and csr.STATUS_FLAG = 'M'
772: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
773: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
774: AND not exists
775: (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
776: from ce_statement_headers sh, ce_statement_lines sl , ce_statement_reconcils_all sr
777: where sh.statement_header_id = sl.statement_header_id
778: and sl.statement_line_id= sr.statement_line_id
779: and sh.statement_header_id = csh.statement_header_id
780: and sh.bank_account_id IN (SELECT aba.bank_account_id
790: where lbg.organization_id = sr.org_id or
791: sr.legal_entity_id = lbg.organization_id )
792: ) AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/
793: (SELECT 1
794: FROM ce_statement_lines csl, ce_statement_reconcils_all csr
795: WHERE csl.statement_header_id = csh.statement_header_id
796: and csl.statement_line_id= csr.statement_line_id
797: AND csr.CURRENT_RECORD_FLAG = 'Y'
798: and csr.STATUS_FLAG = 'M'
894: WHERE csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
895: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
896: AND not exists
897: (select 1
898: from ce_statement_headers sh, ce_statement_lines sl , ce_statement_reconcils_all sr
899: where sh.statement_header_id = sl.statement_header_id
900: and sl.statement_line_id= sr.statement_line_id
901: and sh.statement_header_id= csh.statement_header_id
902: and sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
908: (select 1 from ce_security_profiles_gt lbg
909: where lbg.organization_id = sr.org_id or sr.legal_entity_id = lbg.organization_id )
910: ) AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/
911: (SELECT 1
912: FROM ce_statement_lines csl, ce_statement_reconcils_all csr
913: WHERE csl.statement_header_id = csh.statement_header_id
914: and csl.statement_line_id= csr.statement_line_id
915: AND csr.CURRENT_RECORD_FLAG = 'Y'
916: and csr.STATUS_FLAG = 'M'
1742: l_def_org_id number;
1743: l_sp_id number;
1744: -- bug 3676745 in order to archive/purge stmts, the user
1745: -- must have access to all the reconciled trx's
1746: -- org (ce_statement_reconcils_all.org_id and ce_statement_reconcils_all.legal_entity_id)
1747: -- This is to ensure that there are no orphan trx data in the system
1748: -- 1) Main query - should query from security view (ce_bank_accts_gt_v)
1749: -- 2) Sub-query (not exists) should select from ce_bank_accounts table,
1750: -- This query is to select all stmts that the users do not have access to.
1763: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1764: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1765: AND not exists
1766: (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
1767: from ce_statement_headers sh, ce_statement_lines sl , ce_statement_reconcils_all sr
1768: where sh.statement_header_id = sl.statement_header_id
1769: and sl.statement_line_id= sr.statement_line_id
1770: -- AND sl.status = Decode(G_Only_Unrec,'Y','RECONCILED',sl.status)
1771: and sh.statement_header_id= csh.statement_header_id
1784: where lbg.organization_id = sr.org_id or sr.legal_entity_id = lbg.organization_id )
1785: )
1786: AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /* 10400780 start*/
1787: (SELECT 1
1788: FROM ce_statement_lines csl, ce_statement_reconcils_all csr
1789: WHERE csl.statement_header_id = csh.statement_header_id
1790: and csl.statement_line_id= csr.statement_line_id
1791: AND csr.CURRENT_RECORD_FLAG = 'Y'
1792: and csr.STATUS_FLAG = 'M'
1862: WHERE statement_header_id = X_header_id;
1863: purge_lines := purge_lines + SQL%ROWCOUNT;
1864:
1865: --DELETE FROM ce_statement_reconciliations
1866: DELETE FROM ce_statement_reconcils_all
1867: WHERE statement_line_id = X_line_id
1868: and (org_id in (select organization_id from ce_security_profiles_gt) or
1869: legal_entity_id in (select organization_id from ce_security_profiles_gt) or
1870: REFERENCE_TYPE in ('JE_LINE', 'ROI_LINE','STATEMENT')
2341: G_Archive_Stat_Lines := SQL%ROWCOUNT;
2342:
2343: cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Lines: '||G_Archive_Stat_Lines);
2344:
2345: INSERT INTO ce_statement_reconcils_all
2346: (STATEMENT_LINE_ID,
2347: REFERENCE_TYPE,
2348: REFERENCE_ID,
2349: JE_HEADER_ID,