APPS.CE_PURGE dependencies on CE_PURGE

Line 1: PACKAGE BODY ce_purge AS

1: PACKAGE BODY ce_purge AS
2: /* $Header: cepurestrb.pls 120.5 2011/03/13 05:55:39 rtumati noship $ */
3:
4: l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5:

Line 228: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

224: ce_statement_headers csh
225: WHERE cah.bank_account_id = csh.bank_account_id
226: AND csh.statement_number = cah.statement_number
227: AND csh.org_id = cah.org_id
228: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
229: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
230: AND csh.bank_account_id IN (SELECT bank_account_id
231: FROM ce_bank_accts_gt_v ba
232: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))

Line 229: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

225: WHERE cah.bank_account_id = csh.bank_account_id
226: AND csh.statement_number = cah.statement_number
227: AND csh.org_id = cah.org_id
228: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
229: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
230: AND csh.bank_account_id IN (SELECT bank_account_id
231: FROM ce_bank_accts_gt_v ba
232: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
233: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)));

Line 232: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))

228: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
229: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
230: AND csh.bank_account_id IN (SELECT bank_account_id
231: FROM ce_bank_accts_gt_v ba
232: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
233: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)));
234:
235: IF( G_Archive_Purge_Option = 'Restore' ) THEN
236: G_Purge_Stat_Rec := SQL%ROWCOUNT;

Line 233: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)));

229: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
230: AND csh.bank_account_id IN (SELECT bank_account_id
231: FROM ce_bank_accts_gt_v ba
232: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
233: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)));
234:
235: IF( G_Archive_Purge_Option = 'Restore' ) THEN
236: G_Purge_Stat_Rec := SQL%ROWCOUNT;
237: END IF;

Line 246: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

242: ce_statement_headers csh
243: WHERE cah.bank_account_id = csh.bank_account_id
244: AND csh.statement_number = cah.statement_number
245: AND csh.org_id = cah.org_id
246: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
247: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
248: AND csh.bank_account_id IN (SELECT bank_account_id
249: FROM ce_bank_accts_gt_v ba
250: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))

Line 247: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

243: WHERE cah.bank_account_id = csh.bank_account_id
244: AND csh.statement_number = cah.statement_number
245: AND csh.org_id = cah.org_id
246: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
247: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
248: AND csh.bank_account_id IN (SELECT bank_account_id
249: FROM ce_bank_accts_gt_v ba
250: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
251: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

Line 250: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))

246: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
247: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
248: AND csh.bank_account_id IN (SELECT bank_account_id
249: FROM ce_bank_accts_gt_v ba
250: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
251: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
252:
253: IF( G_Archive_Purge_Option = 'Restore' ) THEN
254: G_Purge_Stat_Errors := SQL%ROWCOUNT;

Line 251: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

247: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
248: AND csh.bank_account_id IN (SELECT bank_account_id
249: FROM ce_bank_accts_gt_v ba
250: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
251: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
252:
253: IF( G_Archive_Purge_Option = 'Restore' ) THEN
254: G_Purge_Stat_Errors := SQL%ROWCOUNT;
255: END IF;

Line 264: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

260: ce_statement_headers csh
261: WHERE cah.bank_account_id = csh.bank_account_id
262: AND csh.statement_number = cah.statement_number
263: AND csh.org_id = cah.org_id
264: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
265: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
266: AND csh.bank_account_id IN (SELECT bank_account_id
267: FROM ce_bank_accts_gt_v ba
268: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))

Line 265: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

261: WHERE cah.bank_account_id = csh.bank_account_id
262: AND csh.statement_number = cah.statement_number
263: AND csh.org_id = cah.org_id
264: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
265: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
266: AND csh.bank_account_id IN (SELECT bank_account_id
267: FROM ce_bank_accts_gt_v ba
268: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
269: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

Line 268: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))

264: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
265: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
266: AND csh.bank_account_id IN (SELECT bank_account_id
267: FROM ce_bank_accts_gt_v ba
268: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
269: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
270:
271: IF( G_Archive_Purge_Option = 'Restore' ) THEN
272: G_Purge_Stat_Lines := SQL%ROWCOUNT;

Line 269: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

265: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
266: AND csh.bank_account_id IN (SELECT bank_account_id
267: FROM ce_bank_accts_gt_v ba
268: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
269: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
270:
271: IF( G_Archive_Purge_Option = 'Restore' ) THEN
272: G_Purge_Stat_Lines := SQL%ROWCOUNT;
273: END IF;

Line 282: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

278: ce_statement_headers csh
279: WHERE cah.bank_account_id = csh.bank_account_id
280: AND csh.statement_number = cah.statement_number
281: AND csh.org_id = cah.org_id
282: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
283: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
284: AND csh.bank_account_id IN (SELECT bank_account_id
285: FROM ce_bank_accts_gt_v ba
286: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))

Line 283: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

279: WHERE cah.bank_account_id = csh.bank_account_id
280: AND csh.statement_number = cah.statement_number
281: AND csh.org_id = cah.org_id
282: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
283: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
284: AND csh.bank_account_id IN (SELECT bank_account_id
285: FROM ce_bank_accts_gt_v ba
286: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
287: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

Line 286: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))

282: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
283: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
284: AND csh.bank_account_id IN (SELECT bank_account_id
285: FROM ce_bank_accts_gt_v ba
286: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
287: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
288:
289: IF( G_Archive_Purge_Option = 'Restore' ) THEN
290: G_Purge_Stat_Headers := SQL%ROWCOUNT;

Line 287: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));

283: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
284: AND csh.bank_account_id IN (SELECT bank_account_id
285: FROM ce_bank_accts_gt_v ba
286: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
287: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
288:
289: IF( G_Archive_Purge_Option = 'Restore' ) THEN
290: G_Purge_Stat_Headers := SQL%ROWCOUNT;
291: END IF;

Line 301: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name

297: DELETE FROM CE_ARCH_INTERFACE_LINES cal
298: WHERE cal.statement_number ||'-'|| cal.bank_account_num IN
299: ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
300: FROM ce_statement_headers_int csh
301: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
302: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
303: WHERE bb.branch_party_id = ba.bank_branch_id
304: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
305: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

Line 304: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

300: FROM ce_statement_headers_int csh
301: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
302: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
303: WHERE bb.branch_party_id = ba.bank_branch_id
304: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
305: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
306: AND csh.bank_account_num IN (SELECT bank_account_num
307: FROM ce_bank_accts_gt_v
308: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 305: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

301: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
302: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
303: WHERE bb.branch_party_id = ba.bank_branch_id
304: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
305: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
306: AND csh.bank_account_num IN (SELECT bank_account_num
307: FROM ce_bank_accts_gt_v
308: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
309: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

Line 308: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

304: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
305: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
306: AND csh.bank_account_num IN (SELECT bank_account_num
307: FROM ce_bank_accts_gt_v
308: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
309: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
310: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
311: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
312: 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'))

Line 309: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

305: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
306: AND csh.bank_account_num IN (SELECT bank_account_num
307: FROM ce_bank_accts_gt_v
308: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
309: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
310: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
311: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
312: 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'))
313: AND nvl(csh.intra_day_flag, 'N') = 'N');

Line 310: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

306: AND csh.bank_account_num IN (SELECT bank_account_num
307: FROM ce_bank_accts_gt_v
308: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
309: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
310: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
311: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
312: 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'))
313: AND nvl(csh.intra_day_flag, 'N') = 'N');
314:

Line 311: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

307: FROM ce_bank_accts_gt_v
308: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
309: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
310: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
311: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
312: 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'))
313: AND nvl(csh.intra_day_flag, 'N') = 'N');
314:
315: IF( G_Archive_Purge_Option = 'Restore' ) THEN

Line 312: 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'))

308: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
309: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
310: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
311: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
312: 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'))
313: AND nvl(csh.intra_day_flag, 'N') = 'N');
314:
315: IF( G_Archive_Purge_Option = 'Restore' ) THEN
316: G_Purge_Inf_Lines := SQL%ROWCOUNT;

Line 323: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN

319: DELETE FROM CE_ARCH_INTERFACE_HEADERS cah
320: WHERE cah.statement_number ||'-'|| cah.bank_account_num IN
321: ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
322: FROM ce_statement_headers_int csh
323: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
324: (SELECT bb.bank_branch_name
325: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
326: WHERE bb.branch_party_id = ba.bank_branch_id
327: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

Line 327: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

323: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
324: (SELECT bb.bank_branch_name
325: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
326: WHERE bb.branch_party_id = ba.bank_branch_id
327: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
328: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
329: AND csh.bank_account_num IN
330: (SELECT bank_account_num
331: FROM ce_bank_accts_gt_v

Line 328: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

324: (SELECT bb.bank_branch_name
325: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
326: WHERE bb.branch_party_id = ba.bank_branch_id
327: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
328: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
329: AND csh.bank_account_num IN
330: (SELECT bank_account_num
331: FROM ce_bank_accts_gt_v
332: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 332: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

328: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
329: AND csh.bank_account_num IN
330: (SELECT bank_account_num
331: FROM ce_bank_accts_gt_v
332: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
333: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
334: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
335: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
336: 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'))

Line 333: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

329: AND csh.bank_account_num IN
330: (SELECT bank_account_num
331: FROM ce_bank_accts_gt_v
332: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
333: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
334: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
335: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
336: 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'))
337: AND nvl(csh.intra_day_flag, 'N') = 'N');

Line 334: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

330: (SELECT bank_account_num
331: FROM ce_bank_accts_gt_v
332: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
333: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
334: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
335: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
336: 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'))
337: AND nvl(csh.intra_day_flag, 'N') = 'N');
338:

Line 335: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

331: FROM ce_bank_accts_gt_v
332: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
333: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
334: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
335: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
336: 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'))
337: AND nvl(csh.intra_day_flag, 'N') = 'N');
338:
339: IF( G_Archive_Purge_Option = 'Restore' ) THEN

Line 336: 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'))

332: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
333: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
334: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
335: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
336: 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'))
337: AND nvl(csh.intra_day_flag, 'N') = 'N');
338:
339: IF( G_Archive_Purge_Option = 'Restore' ) THEN
340: G_Purge_Inf_Headers := SQL%ROWCOUNT;

Line 356: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)

352: FROM ce_intra_stmt_headers csh , CE_ARCH_INTRA_HEADERS cah
353: WHERE cah.statement_number = csh.statement_number
354: AND cah.org_id = csh.org_id
355: AND cah.bank_account_id = csh.bank_account_id
356: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)
357: AND csh.bank_account_id IN
358: (SELECT bank_account_id
359: FROM ce_bank_accts_gt_v ba
360: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

Line 360: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

356: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)
357: AND csh.bank_account_id IN
358: (SELECT bank_account_id
359: FROM ce_bank_accts_gt_v ba
360: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
361: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
362: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));
363:
364: IF( G_Archive_Purge_Option = 'Restore' ) THEN

Line 361: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

357: AND csh.bank_account_id IN
358: (SELECT bank_account_id
359: FROM ce_bank_accts_gt_v ba
360: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
361: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
362: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));
363:
364: IF( G_Archive_Purge_Option = 'Restore' ) THEN
365: G_Purge_Stat_Lines2 := SQL%ROWCOUNT;

Line 362: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));

358: (SELECT bank_account_id
359: FROM ce_bank_accts_gt_v ba
360: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
361: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
362: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));
363:
364: IF( G_Archive_Purge_Option = 'Restore' ) THEN
365: G_Purge_Stat_Lines2 := SQL%ROWCOUNT;
366: END IF;

Line 375: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)

371: FROM ce_intra_stmt_headers csh , CE_ARCH_INTRA_HEADERS cah
372: WHERE cah.statement_number = csh.statement_number
373: AND cah.org_id = csh.org_id
374: AND cah.bank_account_id = csh.bank_account_id
375: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)
376: AND csh.bank_account_id IN
377: (SELECT bank_account_id
378: FROM ce_bank_accts_gt_v ba
379: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

Line 379: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

375: AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)
376: AND csh.bank_account_id IN
377: (SELECT bank_account_id
378: FROM ce_bank_accts_gt_v ba
379: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
380: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
381: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));
382:
383: IF( G_Archive_Purge_Option = 'Restore' ) THEN

Line 380: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

376: AND csh.bank_account_id IN
377: (SELECT bank_account_id
378: FROM ce_bank_accts_gt_v ba
379: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
380: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
381: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));
382:
383: IF( G_Archive_Purge_Option = 'Restore' ) THEN
384: G_Purge_Stat_Headers2 := SQL%ROWCOUNT;

Line 381: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));

377: (SELECT bank_account_id
378: FROM ce_bank_accts_gt_v ba
379: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
380: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
381: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));
382:
383: IF( G_Archive_Purge_Option = 'Restore' ) THEN
384: G_Purge_Stat_Headers2 := SQL%ROWCOUNT;
385: END IF;

Line 396: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (

392: DELETE FROM CE_ARCH_INTERFACE_LINES cal
393: WHERE cal.statement_number ||'-'|| cal.bank_account_num IN
394: ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
395: FROM ce_statement_headers_int csh
396: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (
397: SELECT bb.bank_branch_name
398: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
399: WHERE bb.branch_party_id = ba.bank_branch_id
400: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

Line 400: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

396: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (
397: SELECT bb.bank_branch_name
398: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
399: WHERE bb.branch_party_id = ba.bank_branch_id
400: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
401: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
402: AND csh.bank_account_num IN
403: (SELECT bank_account_num
404: FROM ce_bank_accts_gt_v

Line 401: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

397: SELECT bb.bank_branch_name
398: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
399: WHERE bb.branch_party_id = ba.bank_branch_id
400: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
401: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
402: AND csh.bank_account_num IN
403: (SELECT bank_account_num
404: FROM ce_bank_accts_gt_v
405: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 405: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

401: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
402: AND csh.bank_account_num IN
403: (SELECT bank_account_num
404: FROM ce_bank_accts_gt_v
405: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
406: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
407: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
408: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
409: 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'))

Line 406: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

402: AND csh.bank_account_num IN
403: (SELECT bank_account_num
404: FROM ce_bank_accts_gt_v
405: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
406: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
407: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
408: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
409: 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'))
410: AND csh.intra_day_flag = 'Y');

Line 407: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

403: (SELECT bank_account_num
404: FROM ce_bank_accts_gt_v
405: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
406: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
407: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
408: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
409: 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'))
410: AND csh.intra_day_flag = 'Y');
411:

Line 408: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

404: FROM ce_bank_accts_gt_v
405: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
406: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
407: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
408: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
409: 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'))
410: AND csh.intra_day_flag = 'Y');
411:
412: IF( G_Archive_Purge_Option = 'Restore' ) THEN

Line 409: 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'))

405: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
406: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
407: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
408: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
409: 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'))
410: AND csh.intra_day_flag = 'Y');
411:
412: IF( G_Archive_Purge_Option = 'Restore' ) THEN
413: G_Purge_Inf_Lines2 := SQL%ROWCOUNT;

Line 421: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN

417: DELETE FROM CE_ARCH_INTERFACE_HEADERS cah
418: WHERE cah.statement_number ||'-'|| cah.bank_account_num IN
419: (SELECT csh.statement_number ||'-'|| csh.bank_account_num
420: FROM ce_statement_headers_int csh
421: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
422: (SELECT bb.bank_branch_name
423: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
424: WHERE bb.branch_party_id = ba.bank_branch_id
425: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

Line 425: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

421: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
422: (SELECT bb.bank_branch_name
423: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
424: WHERE bb.branch_party_id = ba.bank_branch_id
425: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
426: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
427: AND csh.bank_account_num IN
428: (SELECT bank_account_num
429: FROM ce_bank_accts_gt_v

Line 426: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

422: (SELECT bb.bank_branch_name
423: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
424: WHERE bb.branch_party_id = ba.bank_branch_id
425: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
426: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
427: AND csh.bank_account_num IN
428: (SELECT bank_account_num
429: FROM ce_bank_accts_gt_v
430: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 430: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

426: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
427: AND csh.bank_account_num IN
428: (SELECT bank_account_num
429: FROM ce_bank_accts_gt_v
430: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
431: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
432: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
433: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
434: 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'))

Line 431: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

427: AND csh.bank_account_num IN
428: (SELECT bank_account_num
429: FROM ce_bank_accts_gt_v
430: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
431: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
432: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
433: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
434: 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'))
435: AND csh.intra_day_flag = 'Y');

Line 432: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

428: (SELECT bank_account_num
429: FROM ce_bank_accts_gt_v
430: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
431: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
432: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
433: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
434: 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'))
435: AND csh.intra_day_flag = 'Y');
436:

Line 433: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

429: FROM ce_bank_accts_gt_v
430: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
431: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
432: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
433: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
434: 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'))
435: AND csh.intra_day_flag = 'Y');
436:
437: IF( G_Archive_Purge_Option = 'Restore' ) THEN

Line 434: 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'))

430: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
431: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
432: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
433: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
434: 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'))
435: AND csh.intra_day_flag = 'Y');
436:
437: IF( G_Archive_Purge_Option = 'Restore' ) THEN
438: G_Purge_Inf_Headers2 := SQL%ROWCOUNT;

Line 540: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_ACCOUNT_ID: '||CE_PURGE.G_BANK_ACCOUNT_ID);

536:
537: PROCEDURE arch_ce_statements
538: IS
539: BEGIN
540: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_ACCOUNT_ID: '||CE_PURGE.G_BANK_ACCOUNT_ID);
541: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_BRANCH_ID: '||CE_PURGE.G_BANK_BRANCH_ID);
542:
543:
544: IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL) THEN

Line 541: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_BRANCH_ID: '||CE_PURGE.G_BANK_BRANCH_ID);

537: PROCEDURE arch_ce_statements
538: IS
539: BEGIN
540: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_ACCOUNT_ID: '||CE_PURGE.G_BANK_ACCOUNT_ID);
541: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_BRANCH_ID: '||CE_PURGE.G_BANK_BRANCH_ID);
542:
543:
544: IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL) THEN
545: fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL ');

Line 544: IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL) THEN

540: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_ACCOUNT_ID: '||CE_PURGE.G_BANK_ACCOUNT_ID);
541: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_BRANCH_ID: '||CE_PURGE.G_BANK_BRANCH_ID);
542:
543:
544: IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL) THEN
545: fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL ');
546:
547:
548: -- bug 3676745 in order to archive/purge stmts, the user

Line 545: fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL ');

541: fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_BRANCH_ID: '||CE_PURGE.G_BANK_BRANCH_ID);
542:
543:
544: IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL) THEN
545: fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL ');
546:
547:
548: -- bug 3676745 in order to archive/purge stmts, the user
549: -- must have access to all the reconciled trx's

Line 643: WHERE csh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID

639: CREATION_DATE,
640: LAST_UPDATED_BY,
641: LAST_UPDATE_DATE
642: FROM ce_statement_headers csh
643: WHERE csh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID
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)

Line 644: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)

640: LAST_UPDATED_BY,
641: LAST_UPDATE_DATE
642: FROM ce_statement_headers csh
643: WHERE csh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID
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

Line 645: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)

641: LAST_UPDATE_DATE
642: FROM ce_statement_headers csh
643: WHERE csh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID
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

Line 652: and sh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID

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
653: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
654: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
655: and sr.CURRENT_RECORD_FLAG = 'Y'
656: and sr.STATUS_FLAG = 'M'

Line 653: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)

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
653: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
654: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
655: and sr.CURRENT_RECORD_FLAG = 'Y'
656: and sr.STATUS_FLAG = 'M'
657: and (sr.org_id is not null or sr.legal_entity_id is not null)

Line 654: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)

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
653: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
654: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
655: and sr.CURRENT_RECORD_FLAG = 'Y'
656: and sr.STATUS_FLAG = 'M'
657: and (sr.org_id is not null or sr.legal_entity_id is not null)
658: and not exists

Line 662: AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/

658: and not exists
659: (select 1 from ce_security_profiles_gt lbg
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

Line 671: ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/

667: AND csr.CURRENT_RECORD_FLAG = 'Y'
668: and csr.STATUS_FLAG = 'M'
669: )
670: )
671: ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/
672:
673:
674: G_Archive_Stat_Headers := SQL%ROWCOUNT;
675:

Line 676: ELSIF (CE_PURGE.G_BANK_BRANCH_ID IS NOT NULL) THEN

672:
673:
674: G_Archive_Stat_Headers := SQL%ROWCOUNT;
675:
676: ELSIF (CE_PURGE.G_BANK_BRANCH_ID IS NOT NULL) THEN
677:
678: fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_BRANCH_ID IS NOT NULL ');
679:
680: INSERT INTO ce_arch_headers (

Line 678: fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_BRANCH_ID IS NOT NULL ');

674: G_Archive_Stat_Headers := SQL%ROWCOUNT;
675:
676: ELSIF (CE_PURGE.G_BANK_BRANCH_ID IS NOT NULL) THEN
677:
678: fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_BRANCH_ID IS NOT NULL ');
679:
680: INSERT INTO ce_arch_headers (
681: STATEMENT_COMPLETE_FLAG,
682: --ORG_ID,

Line 771: WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)

767: LAST_UPDATE_DATE
768: FROM ce_statement_headers csh
769: WHERE csh.bank_account_id IN (SELECT aba.bank_account_id
770: FROM CE_BANK_ACCTS_GT_V aba
771: WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)
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)

Line 772: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)

768: FROM ce_statement_headers csh
769: WHERE csh.bank_account_id IN (SELECT aba.bank_account_id
770: FROM CE_BANK_ACCTS_GT_V aba
771: WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)
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

Line 773: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)

769: WHERE csh.bank_account_id IN (SELECT aba.bank_account_id
770: FROM CE_BANK_ACCTS_GT_V aba
771: WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)
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

Line 782: WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_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
781: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
782: WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)
783: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
784: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
785: and sr.CURRENT_RECORD_FLAG = 'Y'
786: and sr.STATUS_FLAG = 'M'

Line 783: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)

779: and sh.statement_header_id = csh.statement_header_id
780: and sh.bank_account_id IN (SELECT aba.bank_account_id
781: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
782: WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)
783: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
784: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
785: and sr.CURRENT_RECORD_FLAG = 'Y'
786: and sr.STATUS_FLAG = 'M'
787: and (sr.org_id is not null or sr.legal_entity_id is not null)

Line 784: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)

780: and sh.bank_account_id IN (SELECT aba.bank_account_id
781: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
782: WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)
783: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
784: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
785: and sr.CURRENT_RECORD_FLAG = 'Y'
786: and sr.STATUS_FLAG = 'M'
787: and (sr.org_id is not null or sr.legal_entity_id is not null)
788: and not exists

Line 792: ) AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/

788: and not exists
789: (select 1 from ce_security_profiles_gt lbg
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

Line 801: ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/

797: AND csr.CURRENT_RECORD_FLAG = 'Y'
798: and csr.STATUS_FLAG = 'M'
799: )
800: )
801: ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/
802:
803: G_Archive_Stat_Headers := SQL%ROWCOUNT;
804: ELSE
805: INSERT INTO ce_arch_headers (

Line 894: WHERE csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)

890: CREATION_DATE,
891: LAST_UPDATED_BY,
892: LAST_UPDATE_DATE
893: FROM ce_statement_headers csh
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

Line 895: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)

891: LAST_UPDATED_BY,
892: LAST_UPDATE_DATE
893: FROM ce_statement_headers csh
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

Line 902: and sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)

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)
903: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
904: and sr.CURRENT_RECORD_FLAG = 'Y'
905: and sr.STATUS_FLAG = 'M'
906: and (sr.org_id is not null or sr.legal_entity_id is not null)

Line 903: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)

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)
903: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
904: and sr.CURRENT_RECORD_FLAG = 'Y'
905: and sr.STATUS_FLAG = 'M'
906: and (sr.org_id is not null or sr.legal_entity_id is not null)
907: and not exists

Line 910: ) AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/

906: and (sr.org_id is not null or sr.legal_entity_id is not null)
907: and not exists
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

Line 919: ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/

915: AND csr.CURRENT_RECORD_FLAG = 'Y'
916: and csr.STATUS_FLAG = 'M'
917: )
918: )
919: ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/
920:
921: G_Archive_Stat_Headers := SQL%ROWCOUNT;
922: END IF;
923:

Line 1189: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name

1185: LAST_UPDATE_DATE,
1186: INTRA_DAY_FLAG
1187: --ORG_ID
1188: FROM ce_statement_headers_int csh
1189: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1190: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1191: WHERE bb.branch_party_id = ba.bank_branch_id
1192: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1193: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

Line 1192: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

1188: FROM ce_statement_headers_int csh
1189: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1190: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1191: WHERE bb.branch_party_id = ba.bank_branch_id
1192: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1193: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1194: AND csh.bank_account_num IN (SELECT bank_account_num
1195: FROM ce_bank_accts_gt_v
1196: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 1193: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

1189: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1190: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1191: WHERE bb.branch_party_id = ba.bank_branch_id
1192: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1193: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1194: AND csh.bank_account_num IN (SELECT bank_account_num
1195: FROM ce_bank_accts_gt_v
1196: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1197: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

Line 1196: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

1192: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1193: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1194: AND csh.bank_account_num IN (SELECT bank_account_num
1195: FROM ce_bank_accts_gt_v
1196: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1197: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1198: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1199: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1200: 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'))

Line 1197: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

1193: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1194: AND csh.bank_account_num IN (SELECT bank_account_num
1195: FROM ce_bank_accts_gt_v
1196: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1197: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1198: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1199: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1200: 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'))
1201: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag ;

Line 1198: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

1194: AND csh.bank_account_num IN (SELECT bank_account_num
1195: FROM ce_bank_accts_gt_v
1196: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1197: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1198: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1199: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1200: 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'))
1201: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag ;
1202:

Line 1199: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

1195: FROM ce_bank_accts_gt_v
1196: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1197: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1198: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1199: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1200: 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'))
1201: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag ;
1202:
1203: IF( p_intra_day_flag = 'N' ) THEN

Line 1200: 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'))

1196: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1197: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1198: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1199: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1200: 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'))
1201: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag ;
1202:
1203: IF( p_intra_day_flag = 'N' ) THEN
1204: G_Archive_Inf_Headers := SQL%ROWCOUNT;

Line 1290: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name

1286: FROM ce_statement_lines_interface csl
1287: WHERE csl.statement_number ||'-'|| csl.bank_account_num IN (
1288: SELECT csh.statement_number ||'-'|| csh.bank_account_num
1289: FROM ce_statement_headers_int csh
1290: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1291: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1292: WHERE bb.branch_party_id = ba.bank_branch_id
1293: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1294: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

Line 1293: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

1289: FROM ce_statement_headers_int csh
1290: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1291: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1292: WHERE bb.branch_party_id = ba.bank_branch_id
1293: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1294: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1295: AND csh.bank_account_num IN (SELECT cv.bank_account_num
1296: FROM ce_bank_accts_gt_v cv
1297: WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)

Line 1294: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

1290: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1291: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1292: WHERE bb.branch_party_id = ba.bank_branch_id
1293: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1294: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1295: AND csh.bank_account_num IN (SELECT cv.bank_account_num
1296: FROM ce_bank_accts_gt_v cv
1297: WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)
1298: AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))

Line 1297: WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)

1293: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1294: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1295: AND csh.bank_account_num IN (SELECT cv.bank_account_num
1296: FROM ce_bank_accts_gt_v cv
1297: WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)
1298: AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))
1299: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1300: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1301: 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'))

Line 1298: AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))

1294: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1295: AND csh.bank_account_num IN (SELECT cv.bank_account_num
1296: FROM ce_bank_accts_gt_v cv
1297: WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)
1298: AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))
1299: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1300: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1301: 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'))
1302: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag);

Line 1299: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

1295: AND csh.bank_account_num IN (SELECT cv.bank_account_num
1296: FROM ce_bank_accts_gt_v cv
1297: WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)
1298: AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))
1299: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1300: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1301: 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'))
1302: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag);
1303:

Line 1300: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

1296: FROM ce_bank_accts_gt_v cv
1297: WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)
1298: AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))
1299: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1300: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1301: 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'))
1302: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag);
1303:
1304: IF( p_intra_day_flag = 'N' ) THEN

Line 1301: 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'))

1297: WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)
1298: AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))
1299: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1300: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1301: 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'))
1302: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag);
1303:
1304: IF( p_intra_day_flag = 'N' ) THEN
1305: G_Archive_Inf_Lines := SQL%ROWCOUNT;

Line 1758: WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

1754: -- Cursor to purge the statement Headers
1755: CURSOR C_PURGE_HEADERS IS
1756: SELECT csh.statement_header_id
1757: FROM ce_statement_headers csh
1758: WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1759: AND csh.bank_account_id IN
1760: (SELECT bank_account_id
1761: FROM ce_bank_accts_gt_v ba
1762: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

Line 1762: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

1758: WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1759: AND csh.bank_account_id IN
1760: (SELECT bank_account_id
1761: FROM ce_bank_accts_gt_v ba
1762: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
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)

Line 1763: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

1759: AND csh.bank_account_id IN
1760: (SELECT bank_account_id
1761: FROM ce_bank_accts_gt_v ba
1762: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
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

Line 1764: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

1760: (SELECT bank_account_id
1761: FROM ce_bank_accts_gt_v ba
1762: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
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

Line 1772: and sh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

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
1772: and sh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1773: AND sh.bank_account_id IN
1774: (SELECT bank_account_id
1775: FROM ce_bank_accounts ba --ce_bank_accts_gt_v ba
1776: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

Line 1776: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

1772: and sh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1773: AND sh.bank_account_id IN
1774: (SELECT bank_account_id
1775: FROM ce_bank_accounts ba --ce_bank_accts_gt_v ba
1776: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1777: AND trunc(sh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(sh.statement_date))
1778: AND trunc(sh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(sh.statement_date))
1779: and sr.CURRENT_RECORD_FLAG = 'Y'
1780: and sr.STATUS_FLAG = 'M'

Line 1777: AND trunc(sh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(sh.statement_date))

1773: AND sh.bank_account_id IN
1774: (SELECT bank_account_id
1775: FROM ce_bank_accounts ba --ce_bank_accts_gt_v ba
1776: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1777: AND trunc(sh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(sh.statement_date))
1778: AND trunc(sh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(sh.statement_date))
1779: and sr.CURRENT_RECORD_FLAG = 'Y'
1780: and sr.STATUS_FLAG = 'M'
1781: and (sr.org_id is not null or sr.legal_entity_id is not null)

Line 1778: AND trunc(sh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(sh.statement_date))

1774: (SELECT bank_account_id
1775: FROM ce_bank_accounts ba --ce_bank_accts_gt_v ba
1776: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1777: AND trunc(sh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(sh.statement_date))
1778: AND trunc(sh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(sh.statement_date))
1779: and sr.CURRENT_RECORD_FLAG = 'Y'
1780: and sr.STATUS_FLAG = 'M'
1781: and (sr.org_id is not null or sr.legal_entity_id is not null)
1782: AND not exists

Line 1786: AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /* 10400780 start*/

1782: AND not exists
1783: (select 1 from ce_security_profiles_gt lbg
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

Line 1795: ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/

1791: AND csr.CURRENT_RECORD_FLAG = 'Y'
1792: and csr.STATUS_FLAG = 'M'
1793: )
1794: )
1795: ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/
1796:
1797:
1798: -- Cursor to purge the statement Lines
1799: CURSOR C_PURGE_LINES IS

Line 1808: WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

1804: -- Cursor to purge the Intra day statement Headers
1805: CURSOR C_PURGE_INTRA_HEADERS IS
1806: SELECT csh.statement_header_id
1807: FROM ce_intra_stmt_headers csh
1808: WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1809: AND csh.bank_account_id IN
1810: (SELECT bank_account_id
1811: FROM ce_bank_accts_gt_v ba
1812: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

Line 1812: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )

1808: WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1809: AND csh.bank_account_id IN
1810: (SELECT bank_account_id
1811: FROM ce_bank_accts_gt_v ba
1812: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1813: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1814: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date));
1815:
1816: -- Cursor to purge the Intra day statement lines

Line 1813: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

1809: AND csh.bank_account_id IN
1810: (SELECT bank_account_id
1811: FROM ce_bank_accts_gt_v ba
1812: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1813: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1814: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date));
1815:
1816: -- Cursor to purge the Intra day statement lines
1817: CURSOR C_PURGE_INTRA_LINES IS

Line 1814: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date));

1810: (SELECT bank_account_id
1811: FROM ce_bank_accts_gt_v ba
1812: WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1813: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1814: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date));
1815:
1816: -- Cursor to purge the Intra day statement lines
1817: CURSOR C_PURGE_INTRA_LINES IS
1818: SELECT csl.statement_line_id

Line 1829: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'DELETE') THEN

1825: -- First we check that the archive tables are empty.
1826: -- They need to be emply, if the user has selected the delete option
1827:
1828: --
1829: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'DELETE') THEN
1830: delete_archive_tables;
1831: ELSIF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'APPEND') THEN
1832: delete_duplicate_data;
1833: END IF;

Line 1831: ELSIF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'APPEND') THEN

1827:
1828: --
1829: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'DELETE') THEN
1830: delete_archive_tables;
1831: ELSIF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'APPEND') THEN
1832: delete_duplicate_data;
1833: END IF;
1834:
1835: -- Perform the archive/purge based on the user parameters

Line 1838: IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')) THEN

1834:
1835: -- Perform the archive/purge based on the user parameters
1836: --
1837:
1838: IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')) THEN
1839: IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN
1840: IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN
1841: -- Archives the Ce statement Headers lines along with reconcile history and reconcile errors
1842: arch_ce_statements;

Line 1839: IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN

1835: -- Perform the archive/purge based on the user parameters
1836: --
1837:
1838: IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')) THEN
1839: IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN
1840: IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN
1841: -- Archives the Ce statement Headers lines along with reconcile history and reconcile errors
1842: arch_ce_statements;
1843: END IF;

Line 1840: IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN

1836: --
1837:
1838: IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')) THEN
1839: IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN
1840: IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN
1841: -- Archives the Ce statement Headers lines along with reconcile history and reconcile errors
1842: arch_ce_statements;
1843: END IF;
1844: --

Line 1887: CE_PURGE.G_Purge_Stat_Lines := purge_lines;

1883: FETCH C_PURGE_HEADERS INTO X_header_id;
1884: END LOOP; -- End of Headers Cursor
1885: CLOSE C_PURGE_HEADERS;
1886:
1887: CE_PURGE.G_Purge_Stat_Lines := purge_lines;
1888: CE_PURGE.G_Purge_Stat_Headers := purge_headers;
1889: CE_PURGE.G_Purge_Stat_Rec := purge_recons;
1890: CE_PURGE.G_Purge_Stat_Errors := purge_recon_errors;
1891:

Line 1888: CE_PURGE.G_Purge_Stat_Headers := purge_headers;

1884: END LOOP; -- End of Headers Cursor
1885: CLOSE C_PURGE_HEADERS;
1886:
1887: CE_PURGE.G_Purge_Stat_Lines := purge_lines;
1888: CE_PURGE.G_Purge_Stat_Headers := purge_headers;
1889: CE_PURGE.G_Purge_Stat_Rec := purge_recons;
1890: CE_PURGE.G_Purge_Stat_Errors := purge_recon_errors;
1891:
1892: END IF; /* IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */

Line 1889: CE_PURGE.G_Purge_Stat_Rec := purge_recons;

1885: CLOSE C_PURGE_HEADERS;
1886:
1887: CE_PURGE.G_Purge_Stat_Lines := purge_lines;
1888: CE_PURGE.G_Purge_Stat_Headers := purge_headers;
1889: CE_PURGE.G_Purge_Stat_Rec := purge_recons;
1890: CE_PURGE.G_Purge_Stat_Errors := purge_recon_errors;
1891:
1892: END IF; /* IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1893:

Line 1890: CE_PURGE.G_Purge_Stat_Errors := purge_recon_errors;

1886:
1887: CE_PURGE.G_Purge_Stat_Lines := purge_lines;
1888: CE_PURGE.G_Purge_Stat_Headers := purge_headers;
1889: CE_PURGE.G_Purge_Stat_Rec := purge_recons;
1890: CE_PURGE.G_Purge_Stat_Errors := purge_recon_errors;
1891:
1892: END IF; /* IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1893:
1894: IF (G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN

Line 1892: END IF; /* IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */

1888: CE_PURGE.G_Purge_Stat_Headers := purge_headers;
1889: CE_PURGE.G_Purge_Stat_Rec := purge_recons;
1890: CE_PURGE.G_Purge_Stat_Errors := purge_recon_errors;
1891:
1892: END IF; /* IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1893:
1894: IF (G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
1895:
1896: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH')THEN

Line 1896: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH')THEN

1892: END IF; /* IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1893:
1894: IF (G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
1895:
1896: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH')THEN
1897: arch_ce_interface('N');
1898: END IF;
1899:
1900: DELETE FROM ce_statement_lines_interface csl

Line 1904: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name

1900: DELETE FROM ce_statement_lines_interface csl
1901: WHERE csl.statement_number ||'-'|| csl.bank_account_num IN
1902: ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
1903: FROM ce_statement_headers_int csh
1904: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1905: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1906: WHERE bb.branch_party_id = ba.bank_branch_id
1907: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1908: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

Line 1907: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

1903: FROM ce_statement_headers_int csh
1904: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1905: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1906: WHERE bb.branch_party_id = ba.bank_branch_id
1907: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1908: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1909: AND csh.bank_account_num IN (SELECT bank_account_num
1910: FROM ce_bank_accts_gt_v
1911: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 1908: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

1904: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1905: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1906: WHERE bb.branch_party_id = ba.bank_branch_id
1907: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1908: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1909: AND csh.bank_account_num IN (SELECT bank_account_num
1910: FROM ce_bank_accts_gt_v
1911: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1912: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

Line 1911: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

1907: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1908: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1909: AND csh.bank_account_num IN (SELECT bank_account_num
1910: FROM ce_bank_accts_gt_v
1911: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1912: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1913: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1914: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1915: 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'))

Line 1912: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

1908: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1909: AND csh.bank_account_num IN (SELECT bank_account_num
1910: FROM ce_bank_accts_gt_v
1911: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1912: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1913: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1914: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1915: 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'))
1916: AND nvl(csh.intra_day_flag, 'N') = 'N');

Line 1913: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

1909: AND csh.bank_account_num IN (SELECT bank_account_num
1910: FROM ce_bank_accts_gt_v
1911: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1912: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1913: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1914: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1915: 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'))
1916: AND nvl(csh.intra_day_flag, 'N') = 'N');
1917:

Line 1914: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

1910: FROM ce_bank_accts_gt_v
1911: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1912: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1913: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1914: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1915: 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'))
1916: AND nvl(csh.intra_day_flag, 'N') = 'N');
1917:
1918: CE_PURGE.G_Purge_Inf_Lines := SQL%ROWCOUNT;

Line 1915: 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'))

1911: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1912: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1913: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1914: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1915: 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'))
1916: AND nvl(csh.intra_day_flag, 'N') = 'N');
1917:
1918: CE_PURGE.G_Purge_Inf_Lines := SQL%ROWCOUNT;
1919:

Line 1918: CE_PURGE.G_Purge_Inf_Lines := SQL%ROWCOUNT;

1914: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1915: 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'))
1916: AND nvl(csh.intra_day_flag, 'N') = 'N');
1917:
1918: CE_PURGE.G_Purge_Inf_Lines := SQL%ROWCOUNT;
1919:
1920: DELETE FROM ce_statement_headers_int csh
1921: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
1922: (SELECT bb.bank_branch_name

Line 1921: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN

1917:
1918: CE_PURGE.G_Purge_Inf_Lines := SQL%ROWCOUNT;
1919:
1920: DELETE FROM ce_statement_headers_int csh
1921: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
1922: (SELECT bb.bank_branch_name
1923: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1924: WHERE bb.branch_party_id = ba.bank_branch_id
1925: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

Line 1925: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

1921: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
1922: (SELECT bb.bank_branch_name
1923: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1924: WHERE bb.branch_party_id = ba.bank_branch_id
1925: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1926: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1927: AND csh.bank_account_num IN
1928: (SELECT bank_account_num
1929: FROM ce_bank_accts_gt_v

Line 1926: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

1922: (SELECT bb.bank_branch_name
1923: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1924: WHERE bb.branch_party_id = ba.bank_branch_id
1925: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1926: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1927: AND csh.bank_account_num IN
1928: (SELECT bank_account_num
1929: FROM ce_bank_accts_gt_v
1930: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 1930: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

1926: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1927: AND csh.bank_account_num IN
1928: (SELECT bank_account_num
1929: FROM ce_bank_accts_gt_v
1930: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1931: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1932: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1933: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1934: 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'))

Line 1931: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

1927: AND csh.bank_account_num IN
1928: (SELECT bank_account_num
1929: FROM ce_bank_accts_gt_v
1930: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1931: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1932: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1933: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1934: 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'))
1935: AND nvl(csh.intra_day_flag, 'N') = 'N';

Line 1932: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

1928: (SELECT bank_account_num
1929: FROM ce_bank_accts_gt_v
1930: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1931: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1932: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1933: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1934: 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'))
1935: AND nvl(csh.intra_day_flag, 'N') = 'N';
1936:

Line 1933: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

1929: FROM ce_bank_accts_gt_v
1930: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1931: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1932: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1933: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1934: 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'))
1935: AND nvl(csh.intra_day_flag, 'N') = 'N';
1936:
1937: CE_PURGE.G_Purge_Inf_Headers := SQL%ROWCOUNT;

Line 1934: 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'))

1930: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1931: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1932: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1933: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1934: 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'))
1935: AND nvl(csh.intra_day_flag, 'N') = 'N';
1936:
1937: CE_PURGE.G_Purge_Inf_Headers := SQL%ROWCOUNT;
1938:

Line 1937: CE_PURGE.G_Purge_Inf_Headers := SQL%ROWCOUNT;

1933: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1934: 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'))
1935: AND nvl(csh.intra_day_flag, 'N') = 'N';
1936:
1937: CE_PURGE.G_Purge_Inf_Headers := SQL%ROWCOUNT;
1938:
1939: END IF; /* IF (G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN */
1940:
1941: END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')

Line 1941: END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')

1937: CE_PURGE.G_Purge_Inf_Headers := SQL%ROWCOUNT;
1938:
1939: END IF; /* IF (G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN */
1940:
1941: END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')
1942:
1943:
1944: -------------------------------------------------
1945:

Line 1946: IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')) THEN

1942:
1943:
1944: -------------------------------------------------
1945:
1946: IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')) THEN
1947: IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN
1948:
1949: IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN
1950: arch_ce_intra_statements;

Line 1947: IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN

1943:
1944: -------------------------------------------------
1945:
1946: IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')) THEN
1947: IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN
1948:
1949: IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN
1950: arch_ce_intra_statements;
1951: END IF;

Line 1949: IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN

1945:
1946: IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')) THEN
1947: IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN
1948:
1949: IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN
1950: arch_ce_intra_statements;
1951: END IF;
1952: --
1953: -- bug# 924584

Line 1982: CE_PURGE.G_Purge_Stat_Lines2 := purge_lines;

1978: FETCH C_PURGE_INTRA_HEADERS INTO X_header_id;
1979: END LOOP;
1980: CLOSE C_PURGE_INTRA_HEADERS;
1981:
1982: CE_PURGE.G_Purge_Stat_Lines2 := purge_lines;
1983: CE_PURGE.G_Purge_Stat_Headers2 := purge_headers;
1984:
1985:
1986: END IF; /*IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */

Line 1983: CE_PURGE.G_Purge_Stat_Headers2 := purge_headers;

1979: END LOOP;
1980: CLOSE C_PURGE_INTRA_HEADERS;
1981:
1982: CE_PURGE.G_Purge_Stat_Lines2 := purge_lines;
1983: CE_PURGE.G_Purge_Stat_Headers2 := purge_headers;
1984:
1985:
1986: END IF; /*IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1987:

Line 1986: END IF; /*IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */

1982: CE_PURGE.G_Purge_Stat_Lines2 := purge_lines;
1983: CE_PURGE.G_Purge_Stat_Headers2 := purge_headers;
1984:
1985:
1986: END IF; /*IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1987:
1988:
1989:
1990: IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN

Line 1990: IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN

1986: END IF; /*IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1987:
1988:
1989:
1990: IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
1991: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH')THEN
1992: arch_ce_interface('Y');
1993: END IF;
1994:

Line 1991: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH')THEN

1987:
1988:
1989:
1990: IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
1991: IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH')THEN
1992: arch_ce_interface('Y');
1993: END IF;
1994:
1995: DELETE FROM ce_statement_lines_interface csl

Line 1999: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name

1995: DELETE FROM ce_statement_lines_interface csl
1996: WHERE csl.statement_number ||'-'|| csl.bank_account_num IN
1997: ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
1998: FROM ce_statement_headers_int csh
1999: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
2000: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2001: WHERE bb.branch_party_id = ba.bank_branch_id
2002: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2003: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

Line 2002: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

1998: FROM ce_statement_headers_int csh
1999: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
2000: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2001: WHERE bb.branch_party_id = ba.bank_branch_id
2002: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2003: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2004: AND csh.bank_account_num IN
2005: (SELECT bank_account_num
2006: FROM ce_bank_accts_gt_v

Line 2003: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

1999: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
2000: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2001: WHERE bb.branch_party_id = ba.bank_branch_id
2002: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2003: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2004: AND csh.bank_account_num IN
2005: (SELECT bank_account_num
2006: FROM ce_bank_accts_gt_v
2007: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 2007: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

2003: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2004: AND csh.bank_account_num IN
2005: (SELECT bank_account_num
2006: FROM ce_bank_accts_gt_v
2007: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2008: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2009: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2010: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2011: 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'))

Line 2008: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

2004: AND csh.bank_account_num IN
2005: (SELECT bank_account_num
2006: FROM ce_bank_accts_gt_v
2007: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2008: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2009: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2010: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2011: 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'))
2012: AND csh.intra_day_flag = 'Y');

Line 2009: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

2005: (SELECT bank_account_num
2006: FROM ce_bank_accts_gt_v
2007: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2008: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2009: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2010: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2011: 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'))
2012: AND csh.intra_day_flag = 'Y');
2013:

Line 2010: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

2006: FROM ce_bank_accts_gt_v
2007: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2008: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2009: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2010: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2011: 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'))
2012: AND csh.intra_day_flag = 'Y');
2013:
2014: CE_PURGE.G_Purge_Inf_Lines2 := SQL%ROWCOUNT;

Line 2011: 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'))

2007: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2008: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2009: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2010: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2011: 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'))
2012: AND csh.intra_day_flag = 'Y');
2013:
2014: CE_PURGE.G_Purge_Inf_Lines2 := SQL%ROWCOUNT;
2015:

Line 2014: CE_PURGE.G_Purge_Inf_Lines2 := SQL%ROWCOUNT;

2010: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2011: 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'))
2012: AND csh.intra_day_flag = 'Y');
2013:
2014: CE_PURGE.G_Purge_Inf_Lines2 := SQL%ROWCOUNT;
2015:
2016: DELETE FROM ce_statement_headers_int csh
2017: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
2018: (SELECT bb.bank_branch_name

Line 2017: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN

2013:
2014: CE_PURGE.G_Purge_Inf_Lines2 := SQL%ROWCOUNT;
2015:
2016: DELETE FROM ce_statement_headers_int csh
2017: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
2018: (SELECT bb.bank_branch_name
2019: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2020: WHERE bb.branch_party_id = ba.bank_branch_id
2021: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

Line 2021: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

2017: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
2018: (SELECT bb.bank_branch_name
2019: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2020: WHERE bb.branch_party_id = ba.bank_branch_id
2021: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2022: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2023: AND csh.bank_account_num IN
2024: (SELECT bank_account_num
2025: FROM ce_bank_accts_gt_v

Line 2022: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

2018: (SELECT bb.bank_branch_name
2019: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2020: WHERE bb.branch_party_id = ba.bank_branch_id
2021: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2022: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2023: AND csh.bank_account_num IN
2024: (SELECT bank_account_num
2025: FROM ce_bank_accts_gt_v
2026: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 2026: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

2022: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2023: AND csh.bank_account_num IN
2024: (SELECT bank_account_num
2025: FROM ce_bank_accts_gt_v
2026: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2027: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2028: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2029: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2030: 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'))

Line 2027: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

2023: AND csh.bank_account_num IN
2024: (SELECT bank_account_num
2025: FROM ce_bank_accts_gt_v
2026: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2027: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2028: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2029: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2030: 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'))
2031: AND csh.intra_day_flag = 'Y';

Line 2028: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

2024: (SELECT bank_account_num
2025: FROM ce_bank_accts_gt_v
2026: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2027: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2028: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2029: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2030: 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'))
2031: AND csh.intra_day_flag = 'Y';
2032:

Line 2029: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

2025: FROM ce_bank_accts_gt_v
2026: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2027: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2028: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2029: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2030: 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'))
2031: AND csh.intra_day_flag = 'Y';
2032:
2033: CE_PURGE.G_Purge_Inf_Headers2 := SQL%ROWCOUNT;

Line 2030: 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'))

2026: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2027: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2028: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2029: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2030: 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'))
2031: AND csh.intra_day_flag = 'Y';
2032:
2033: CE_PURGE.G_Purge_Inf_Headers2 := SQL%ROWCOUNT;
2034:

Line 2033: CE_PURGE.G_Purge_Inf_Headers2 := SQL%ROWCOUNT;

2029: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2030: 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'))
2031: AND csh.intra_day_flag = 'Y';
2032:
2033: CE_PURGE.G_Purge_Inf_Headers2 := SQL%ROWCOUNT;
2034:
2035: END IF; --IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
2036:
2037: END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')

Line 2035: END IF; --IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN

2031: AND csh.intra_day_flag = 'Y';
2032:
2033: CE_PURGE.G_Purge_Inf_Headers2 := SQL%ROWCOUNT;
2034:
2035: END IF; --IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
2036:
2037: END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')
2038:
2039: END purge_process;

Line 2037: END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')

2033: CE_PURGE.G_Purge_Inf_Headers2 := SQL%ROWCOUNT;
2034:
2035: END IF; --IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
2036:
2037: END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')
2038:
2039: END purge_process;
2040:
2041: /* ---------------------------------------------------------------------

Line 2104: WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)

2100: CREATION_DATE,
2101: LAST_UPDATED_BY,
2102: LAST_UPDATE_DATE
2103: FROM ce_arch_headers csh
2104: WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2105: OR
2106: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2107: AND (csh.bank_account_id in (SELECT aba.bank_account_id
2108: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba

Line 2106: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL

2102: LAST_UPDATE_DATE
2103: FROM ce_arch_headers csh
2104: WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2105: OR
2106: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2107: AND (csh.bank_account_id in (SELECT aba.bank_account_id
2108: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2109: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2110: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)

Line 2109: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))

2105: OR
2106: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2107: AND (csh.bank_account_id in (SELECT aba.bank_account_id
2108: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2109: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2110: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
2111: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
2112: AND not exists
2113: (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)

Line 2110: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)

2106: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2107: AND (csh.bank_account_id in (SELECT aba.bank_account_id
2108: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2109: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2110: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
2111: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
2112: AND not exists
2113: (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
2114: from ce_arch_headers sh, ce_arch_lines sl , ce_arch_reconciliations_all sr

Line 2111: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)

2107: AND (csh.bank_account_id in (SELECT aba.bank_account_id
2108: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2109: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2110: AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
2111: AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
2112: AND not exists
2113: (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
2114: from ce_arch_headers sh, ce_arch_lines sl , ce_arch_reconciliations_all sr
2115: where sh.statement_header_id = sl.statement_header_id

Line 2118: and ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)

2114: from ce_arch_headers sh, ce_arch_lines sl , ce_arch_reconciliations_all sr
2115: where sh.statement_header_id = sl.statement_header_id
2116: and sl.statement_line_id= sr.statement_line_id
2117: and sh.statement_header_id= csh.statement_header_id
2118: and ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2119: OR
2120: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2121: AND (sh.bank_account_id in (SELECT aba.bank_account_id
2122: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba

Line 2120: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL

2116: and sl.statement_line_id= sr.statement_line_id
2117: and sh.statement_header_id= csh.statement_header_id
2118: and ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2119: OR
2120: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2121: AND (sh.bank_account_id in (SELECT aba.bank_account_id
2122: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2123: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2124: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)

Line 2123: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))

2119: OR
2120: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2121: AND (sh.bank_account_id in (SELECT aba.bank_account_id
2122: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2123: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2124: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
2125: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
2126: and sr.CURRENT_RECORD_FLAG = 'Y'
2127: and sr.STATUS_FLAG = 'M'

Line 2124: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)

2120: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2121: AND (sh.bank_account_id in (SELECT aba.bank_account_id
2122: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2123: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2124: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
2125: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
2126: and sr.CURRENT_RECORD_FLAG = 'Y'
2127: and sr.STATUS_FLAG = 'M'
2128: and (sr.org_id is not null or sr.legal_entity_id is not null)

Line 2125: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)

2121: AND (sh.bank_account_id in (SELECT aba.bank_account_id
2122: FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2123: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2124: AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
2125: AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
2126: and sr.CURRENT_RECORD_FLAG = 'Y'
2127: and sr.STATUS_FLAG = 'M'
2128: and (sr.org_id is not null or sr.legal_entity_id is not null)
2129: and not exists

Line 2142: cep_standard.debug('>>CE_PURGE.restore_statements ');

2138: l_No_data_flag VARCHAR2(1):='Y';
2139:
2140: BEGIN
2141:
2142: cep_standard.debug('>>CE_PURGE.restore_statements ');
2143:
2144: put_starttag('Statement');
2145:
2146: FOR r_stmt IN C_restore_stmts LOOP

Line 2147: cep_standard.debug('>>CE_PURGE.restore_statements C_restore_stmts: '||G_Archive_Stat_Headers);

2143:
2144: put_starttag('Statement');
2145:
2146: FOR r_stmt IN C_restore_stmts LOOP
2147: cep_standard.debug('>>CE_PURGE.restore_statements C_restore_stmts: '||G_Archive_Stat_Headers);
2148: G_Archive_Stat_Lines:=0;
2149: G_Archive_Stat_Rec:=0;
2150: G_Archive_Stat_Errors:=0;
2151:

Line 2251: cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Headers: '||G_Archive_Stat_Headers);

2247: r_stmt.LAST_UPDATE_DATE);
2248:
2249: G_Archive_Stat_Headers := G_Archive_Stat_Headers + SQL%ROWCOUNT;
2250:
2251: cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Headers: '||G_Archive_Stat_Headers);
2252:
2253: INSERT INTO ce_statement_lines
2254: (STATEMENT_LINE_ID,
2255: STATEMENT_HEADER_ID,

Line 2343: cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Lines: '||G_Archive_Stat_Lines);

2339:
2340:
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,

Line 2395: cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Rec: '||G_Archive_Stat_Rec);

2391: WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID );
2392:
2393: G_Archive_Stat_Rec := SQL%ROWCOUNT;
2394:
2395: cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Rec: '||G_Archive_Stat_Rec);
2396:
2397: INSERT INTO ce_reconciliation_errors
2398: (STATEMENT_LINE_ID,
2399: MESSAGE_NAME,

Line 2416: cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Errors: '||G_Archive_Stat_Errors);

2412: WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID;
2413:
2414: G_Archive_Stat_Errors := SQL%ROWCOUNT;
2415:
2416: cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Errors: '||G_Archive_Stat_Errors);
2417:
2418: put_starttag('StmtRestored') ;
2419: put_element('BANK_ACCOUNT_NUM',l_bank_account_num);
2420: put_element('BANK_ACCOUNT_NAME',l_bank_account_name);

Line 2534: WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)

2530: CREATION_DATE,
2531: LAST_UPDATED_BY,
2532: LAST_UPDATE_DATE
2533: FROM ce_arch_intra_headers csh
2534: WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2535: OR
2536: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2537: AND csh.bank_account_id IN (SELECT aba.bank_account_id
2538: FROM ce_BANK_ACCOUNTS aba

Line 2536: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL

2532: LAST_UPDATE_DATE
2533: FROM ce_arch_intra_headers csh
2534: WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2535: OR
2536: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2537: AND csh.bank_account_id IN (SELECT aba.bank_account_id
2538: FROM ce_BANK_ACCOUNTS aba
2539: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_BRANCH_ID,aba.bank_branch_id))))
2540: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

Line 2539: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_BRANCH_ID,aba.bank_branch_id))))

2535: OR
2536: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2537: AND csh.bank_account_id IN (SELECT aba.bank_account_id
2538: FROM ce_BANK_ACCOUNTS aba
2539: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_BRANCH_ID,aba.bank_branch_id))))
2540: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2541: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2542: FOR update;
2543:

Line 2540: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

2536: (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2537: AND csh.bank_account_id IN (SELECT aba.bank_account_id
2538: FROM ce_BANK_ACCOUNTS aba
2539: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_BRANCH_ID,aba.bank_branch_id))))
2540: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2541: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2542: FOR update;
2543:
2544: l_bank_account_name ce_bank_accounts.Bank_Account_Name%TYPE;

Line 2541: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

2537: AND csh.bank_account_id IN (SELECT aba.bank_account_id
2538: FROM ce_BANK_ACCOUNTS aba
2539: WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_BRANCH_ID,aba.bank_branch_id))))
2540: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2541: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2542: FOR update;
2543:
2544: l_bank_account_name ce_bank_accounts.Bank_Account_Name%TYPE;
2545: l_bank_account_num CE_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE;

Line 2860: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name

2856: LAST_UPDATE_DATE,
2857: intra_day_flag,
2858: ORG_ID
2859: FROM ce_arch_interface_headers csh
2860: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
2861: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2862: WHERE bb.branch_party_id = ba.bank_branch_id
2863: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2864: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

Line 2863: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)

2859: FROM ce_arch_interface_headers csh
2860: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
2861: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2862: WHERE bb.branch_party_id = ba.bank_branch_id
2863: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2864: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2865: AND csh.bank_account_num IN (SELECT bank_account_num
2866: FROM ce_bank_accts_gt_v
2867: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

Line 2864: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))

2860: WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
2861: FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2862: WHERE bb.branch_party_id = ba.bank_branch_id
2863: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2864: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2865: AND csh.bank_account_num IN (SELECT bank_account_num
2866: FROM ce_bank_accts_gt_v
2867: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2868: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

Line 2867: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)

2863: AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2864: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2865: AND csh.bank_account_num IN (SELECT bank_account_num
2866: FROM ce_bank_accts_gt_v
2867: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2868: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2869: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2870: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2871: 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'))

Line 2868: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))

2864: AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2865: AND csh.bank_account_num IN (SELECT bank_account_num
2866: FROM ce_bank_accts_gt_v
2867: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2868: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2869: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2870: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2871: 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'))
2872: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag

Line 2869: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))

2865: AND csh.bank_account_num IN (SELECT bank_account_num
2866: FROM ce_bank_accts_gt_v
2867: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2868: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2869: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2870: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2871: 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'))
2872: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag
2873: FOR update;

Line 2870: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))

2866: FROM ce_bank_accts_gt_v
2867: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2868: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2869: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2870: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2871: 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'))
2872: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag
2873: FOR update;
2874:

Line 2871: 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'))

2867: WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2868: AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2869: AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2870: AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2871: 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'))
2872: AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag
2873: FOR update;
2874:
2875: l_bank_account_name VARCHAR2(500);

Line 2880: cep_standard.debug('>>CE_PURGE.restore_interface '||p_intra_day_flag);

2876: l_No_data_flag VARCHAR2(1):='Y';
2877: BEGIN
2878:
2879: IF l_DEBUG in ('Y', 'C') THEN
2880: cep_standard.debug('>>CE_PURGE.restore_interface '||p_intra_day_flag);
2881: END IF;
2882:
2883: IF ( p_intra_day_flag = 'Y' ) THEN
2884: put_starttag('INTERFACE_INTRADAY');

Line 3140: cep_standard.debug('<

3136:
3137: EXCEPTION
3138: WHEN Dup_Val_On_Index THEN
3139: IF l_DEBUG in ('Y', 'C') THEN
3140: cep_standard.debug('< 3141: END IF;
3142:
3143: IF ( p_intra_day_flag = 'Y' ) THEN
3144: put_starttag('IntraInfErrors');

Line 3179: cep_standard.debug('<

3175: put_endtag('INTERFACE_PREVIOUS');
3176: END IF;
3177:
3178: IF l_DEBUG in ('Y', 'C') THEN
3179: cep_standard.debug('< 3180: END IF;
3181: END restore_interface;
3182:
3183: /* ---------------------------------------------------------------------

Line 3219: cep_standard.debug('>>CE_PURGE.restore_stmts');

3215:
3216: BEGIN
3217:
3218: IF l_DEBUG in ('Y', 'C') THEN
3219: cep_standard.debug('>>CE_PURGE.restore_stmts');
3220: END IF;
3221:
3222: set_arch_purge_counts;
3223: CEP_STANDARD.init_security;

Line 3255: cep_standard.debug('>>CE_PURGE.restore_stmts G_Statement_type: '||G_Statement_type);

3251: G_Statement_type := 'BOTH';
3252: END IF;
3253:
3254: IF l_DEBUG in ('Y', 'C') THEN
3255: cep_standard.debug('>>CE_PURGE.restore_stmts G_Statement_type: '||G_Statement_type);
3256: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_ACCOUNT_ID: '||G_BANK_ACCOUNT_ID);
3257: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_BRANCH_ID: '||G_BANK_BRANCH_ID||' p_BANK_BRANCH_ID: '||p_BANK_BRANCH_ID);
3258: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_FROM: '||G_STATEMENT_DATE_FROM);
3259: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_TO: '||G_STATEMENT_DATE_TO);

Line 3256: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_ACCOUNT_ID: '||G_BANK_ACCOUNT_ID);

3252: END IF;
3253:
3254: IF l_DEBUG in ('Y', 'C') THEN
3255: cep_standard.debug('>>CE_PURGE.restore_stmts G_Statement_type: '||G_Statement_type);
3256: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_ACCOUNT_ID: '||G_BANK_ACCOUNT_ID);
3257: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_BRANCH_ID: '||G_BANK_BRANCH_ID||' p_BANK_BRANCH_ID: '||p_BANK_BRANCH_ID);
3258: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_FROM: '||G_STATEMENT_DATE_FROM);
3259: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_TO: '||G_STATEMENT_DATE_TO);
3260: cep_standard.debug('>>CE_PURGE.restore_stmts G_hdr_int_status: '||G_hdr_int_status);

Line 3257: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_BRANCH_ID: '||G_BANK_BRANCH_ID||' p_BANK_BRANCH_ID: '||p_BANK_BRANCH_ID);

3253:
3254: IF l_DEBUG in ('Y', 'C') THEN
3255: cep_standard.debug('>>CE_PURGE.restore_stmts G_Statement_type: '||G_Statement_type);
3256: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_ACCOUNT_ID: '||G_BANK_ACCOUNT_ID);
3257: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_BRANCH_ID: '||G_BANK_BRANCH_ID||' p_BANK_BRANCH_ID: '||p_BANK_BRANCH_ID);
3258: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_FROM: '||G_STATEMENT_DATE_FROM);
3259: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_TO: '||G_STATEMENT_DATE_TO);
3260: cep_standard.debug('>>CE_PURGE.restore_stmts G_hdr_int_status: '||G_hdr_int_status);
3261: END IF;

Line 3258: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_FROM: '||G_STATEMENT_DATE_FROM);

3254: IF l_DEBUG in ('Y', 'C') THEN
3255: cep_standard.debug('>>CE_PURGE.restore_stmts G_Statement_type: '||G_Statement_type);
3256: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_ACCOUNT_ID: '||G_BANK_ACCOUNT_ID);
3257: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_BRANCH_ID: '||G_BANK_BRANCH_ID||' p_BANK_BRANCH_ID: '||p_BANK_BRANCH_ID);
3258: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_FROM: '||G_STATEMENT_DATE_FROM);
3259: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_TO: '||G_STATEMENT_DATE_TO);
3260: cep_standard.debug('>>CE_PURGE.restore_stmts G_hdr_int_status: '||G_hdr_int_status);
3261: END IF;
3262:

Line 3259: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_TO: '||G_STATEMENT_DATE_TO);

3255: cep_standard.debug('>>CE_PURGE.restore_stmts G_Statement_type: '||G_Statement_type);
3256: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_ACCOUNT_ID: '||G_BANK_ACCOUNT_ID);
3257: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_BRANCH_ID: '||G_BANK_BRANCH_ID||' p_BANK_BRANCH_ID: '||p_BANK_BRANCH_ID);
3258: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_FROM: '||G_STATEMENT_DATE_FROM);
3259: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_TO: '||G_STATEMENT_DATE_TO);
3260: cep_standard.debug('>>CE_PURGE.restore_stmts G_hdr_int_status: '||G_hdr_int_status);
3261: END IF;
3262:
3263: IF (G_BANK_ACCOUNT_ID IS NOT NULL) THEN

Line 3260: cep_standard.debug('>>CE_PURGE.restore_stmts G_hdr_int_status: '||G_hdr_int_status);

3256: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_ACCOUNT_ID: '||G_BANK_ACCOUNT_ID);
3257: cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_BRANCH_ID: '||G_BANK_BRANCH_ID||' p_BANK_BRANCH_ID: '||p_BANK_BRANCH_ID);
3258: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_FROM: '||G_STATEMENT_DATE_FROM);
3259: cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_TO: '||G_STATEMENT_DATE_TO);
3260: cep_standard.debug('>>CE_PURGE.restore_stmts G_hdr_int_status: '||G_hdr_int_status);
3261: END IF;
3262:
3263: IF (G_BANK_ACCOUNT_ID IS NOT NULL) THEN
3264: SELECT bb.bank_name,

Line 3303: FND_MESSAGE.set_name('CE','CE_PURGE_NO_SOB');

3299: l.lookup_code = 'ALL' and rownum = 1;
3300:
3301: EXCEPTION
3302: WHEN NO_DATA_FOUND THEN
3303: FND_MESSAGE.set_name('CE','CE_PURGE_NO_SOB');
3304: error_found := TRUE;
3305: null;
3306: END;
3307:

Line 3315: FND_MESSAGE.set_name('CE','CE_PURGE_BRANCH_ACCOUNT');

3311: FROM CE_BANK_ACCTS_GT_V
3312: WHERE BANK_ACCOUNT_ID = G_BANK_ACCOUNT_ID;
3313:
3314: IF (l_bank_branch_id <> G_BANK_BRANCH_ID) THEN
3315: FND_MESSAGE.set_name('CE','CE_PURGE_BRANCH_ACCOUNT');
3316: error_found := TRUE;
3317: END IF;
3318: END IF;
3319:

Line 3364: cep_standard.debug('<

3360: ROLLBACK;
3361: END IF;
3362:
3363: IF l_DEBUG in ('Y', 'C') THEN
3364: cep_standard.debug('< 3365: END IF;
3366:
3367: EXCEPTION WHEN OTHERS THEN
3368: IF l_DEBUG in ('Y', 'C') THEN

Line 3369: cep_standard.debug('<

3365: END IF;
3366:
3367: EXCEPTION WHEN OTHERS THEN
3368: IF l_DEBUG in ('Y', 'C') THEN
3369: cep_standard.debug('< 3370: END IF;
3371:
3372: put_endtag('RESTORE_REPORT');
3373: END restore_Process;

Line 3375: END CE_PURGE;

3371:
3372: put_endtag('RESTORE_REPORT');
3373: END restore_Process;
3374:
3375: END CE_PURGE;