[Home] [Help]
PACKAGE BODY: APPS.CN_PURGE_TABLES_PVT
Source
1 PACKAGE BODY CN_PURGE_TABLES_PVT AS
2 /* $Header: CNVTPRGB.pls 120.3 2011/04/12 00:58:06 rnagaraj noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PURGE_TABLES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'CNVTPRGB.pls';
6 g_cn_debug VARCHAR2(1) := fnd_profile.value('CN_DEBUG');
7 g_error_msg VARCHAR2(100) := ' is a required field. Please enter the value for it.';
8 g_script_name CONSTANT VARCHAR2(30) := 'CNVTPRGBT4.0';
9
10 -- API name : insert_archive
11 -- Type : private
12 -- Pre-reqs :
13 PROCEDURE insert_archive
14 (
15 table_id NUMBER,
16 seq_num NUMBER,
17 table_name VARCHAR2,
18 row_count NUMBER,
19 any_rows_to_process varchar2)
20 IS
21 pragma autonomous_transaction;
22
23 BEGIN
24 INSERT
25 INTO cn_arc_audit_desc_all
26 (TABLE_ID,ARCHIVE_PURGE_ID,TABLES_NAME,TABLE_AP_ROWS,ARCHIVE_PURGE_DATE,ANY_ROWS_TO_PROCESS_FLAG,ATTRIBUTE1,
27 ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
28 VALUES
29 (
30 table_id ,
31 seq_num ,
32 table_name ,
33 row_count ,
34 sysdate ,
35 any_rows_to_process,
36 NULL ,
37 NULL ,
38 NULL ,
39 NULL ,
40 NULL ,
41 fnd_global.user_id ,
42 sysdate ,
43 fnd_global.user_id ,
44 sysdate ,
45 fnd_global.user_id
46 );
47 COMMIT;
48 END;
49
50 PROCEDURE debugmsg
51 (
52 msg VARCHAR2
53 )
54 IS
55 BEGIN
56 --g_cn_debug := 'Y';
57 IF g_cn_debug = 'Y' THEN
58 cn_message_pkg.debug
59 (
60 SUBSTR(msg,1,254)
61 )
62 ;
63 fnd_file.put_line
64 (
65 fnd_file.Log, msg
66 )
67 ; -- Bug fix 5125980
68 END IF;
69 -- comment out dbms_output before checking in file
70 -- dbms_output.put_line(substr(msg,1,254));
71 END debugmsg;
72
73 -- API name : delete_table
74 -- Type : private
75 -- Pre-reqs :
76 PROCEDURE delete_table
77 (
78 p_start_period_id IN NUMBER,
79 p_end_period_id IN NUMBER,
80 x_start_date IN DATE,
81 x_end_date IN DATE,
82 p_worker_id IN NUMBER,
83 p_no_of_workers IN NUMBER,
84 p_batch_size IN NUMBER,
85 p_table_owner IN VARCHAR2,
86 p_table_name IN VARCHAR2,
87 p_script_name IN VARCHAR2,
88 p_addnl_para IN VARCHAR2,
89 x_row_to_process_flag OUT nocopy VARCHAR2,
90 x_return_status OUT nocopy VARCHAR2
91 )
92 IS
93 l_start_rowid rowid;
94 l_end_rowid rowid;
95 l_rows_processed NUMBER;
96 l_time VARCHAR2(20);
97 l_sql varchar2(1000);
98 l_any_rows_to_process BOOLEAN;
99 BEGIN
100 x_return_status := 'S';
101 debugmsg
102 (
103 'CN_PURGE_TABLES_PVT.delete_table:Start '
104 )
105 ;
106 debugmsg
107 (
108 'CN_PURGE_TABLES_PVT.delete_table:p_table_name ' || p_table_name
109 )
110 ;
111 debugmsg
112 (
113 'CN_PURGE_TABLES_PVT.delete_table:p_table_owner ' || p_table_owner
114 )
115 ;
116 debugmsg
117 (
118 'CN_PURGE_TABLES_PVT.delete_table:p_script_name ' || p_script_name
119 )
120 ;
121 debugmsg
122 (
123 'CN_PURGE_TABLES_PVT.delete_table:p_worker_id ' || p_worker_id
124 )
125 ;
126 debugmsg
127 (
128 'CN_PURGE_TABLES_PVT.delete_table:p_no_of_workers ' || p_no_of_workers
129 )
130 ;
131 debugmsg
132 (
133 'CN_PURGE_TABLES_PVT.delete_table:p_batch_size ' || p_batch_size
134 )
135 ;
136 SELECT TO_CHAR(sysdate,'dd-mm-rr:hh:mi:ss') INTO l_time FROM dual;
137
138 debugmsg('CN_PURGE_TABLES_PVT.delete_table: delete start l_time ' || l_time );
139
140 ad_parallel_updates_pkg.initialize_rowid_range( ad_parallel_updates_pkg.ROWID_RANGE, p_table_owner, p_table_name, p_script_name, p_worker_id, p_no_of_workers, p_batch_size, 0);
141
142 debugmsg('CN_PURGE_TABLES_PVT.delete_table:after ad_parallel_updates_pkg.initialize_rowid_range ' );
143
144 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid, l_END_rowid, l_any_rows_to_process, p_batch_size, TRUE);
145
146 debugmsg('CN_PURGE_TABLES_PVT.delete_table:after ad_parallel_updates_pkg.get_rowid_range l_any_rows_to_process ' );
147
148 IF (l_any_rows_to_process) THEN
149 --dbms_output.put_line('ROWS Still LEFT For Processing');
150 x_row_to_process_flag := 'Y';
151 debugmsg('CN_PURGE_TABLES_PVT.delete_table: before loop l_any_rows_to_process is true ' );
152 ELSE
153 --dbms_output.put_line('NO ROWS LEFT For Processing');
154 x_row_to_process_flag := 'N';
155 debugmsg('CN_PURGE_TABLES_PVT.delete_table: before loop l_any_rows_to_process is false ' );
156 END IF;
157
158
159 WHILE (l_any_rows_to_process = TRUE)
160 LOOP
161 --dbms_output.put_line('start rowid '||l_start_rowid||' end_rowid '||l_end_rowid||' batch_size '||l_batch_size);
162 -- debugmsg('CN_PURGE_TABLES_PVT.delete_table:after ad_parallel_updates_pkg.get_rowid_range l_any_rows_to_process True' );
163 BEGIN
164 IF (p_table_name = 'CN_PAYMENT_API_ALL') THEN
165 DELETE
166 /*+ ROWID (cnh) */
167 FROM CN_PAYMENT_API_ALL cnh
168 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id
169 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
170 elsif (p_table_name = 'CN_POSTING_DETAILS_ALL') THEN
171 DELETE
172 /*+ ROWID (cnh) */
173 FROM cn_posting_details_all cnh
174 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id
175 AND paid_flag = 'Y'
176 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
177 elsif (p_table_name = 'CN_PAYMENT_TRANSACTIONS_ALL') THEN
178 --debugmsg('CN_PURGE_TABLES_PVT.delete_table:before deleting cn_payment_transactions_all ' );
179 DELETE
180 /*+ ROWID (cnh) */
181 FROM cn_payment_transactions_all cnh
182 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id
183 AND paid_flag = 'Y'
184 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
185 elsif (p_table_name = 'CN_COMMISSION_LINES_ALL') THEN
186 --debugmsg('CN_PURGE_TABLES_PVT.delete_table:before deleting cn_commission_lines_all ' );
187 DELETE
188 /*+ ROWID (cnh) */
189 FROM cn_commission_lines_all cnh
190 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id
191 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
192 -- DELETE FROM cn_commission_lines_all
193 -- WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id;
194 elsif (p_table_name = 'CN_COMMISSION_HEADERS_ALL') THEN
195 DELETE
196 /*+ ROWID (cnh) */
197 FROM cn_commission_headers_all cnh
198 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id
199 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
200 elsif (p_table_name = 'CN_TRX_SALES_LINES_ALL') THEN
201 DELETE
202 /*+ ROWID (cnh) */
203 FROM cn_trx_sales_lines_all cnh
204 WHERE processed_date BETWEEN x_start_date AND x_end_date
205 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
206 elsif (p_table_name = 'CN_TRX_LINES_ALL') THEN
207 DELETE
208 /*+ ROWID (t1) */
209 FROM cn_trx_lines_all tl
210 WHERE tl.TRX_ID IN
211 (SELECT DISTINCT t.TRX_ID
212 FROM cn_trx_all t
213 WHERE TRUNC(t.processed_date) BETWEEN x_start_date AND x_end_date
214 );
215 elsif (p_table_name = 'CN_TRX_ALL') THEN
216 DELETE
217 /*+ ROWID (cnh) */
218 FROM cn_trx_all cnh
219 WHERE processed_date BETWEEN x_start_date AND x_end_date
220 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
221 elsif (p_table_name = 'CN_NOT_TRX_ALL') THEN
222 DELETE
223 /*+ ROWID (cnh) */
224 FROM cn_not_trx_all cnh
225 WHERE processed_date BETWEEN x_start_date AND x_end_date
226 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
227 elsif (p_table_name = 'CN_INVOICE_CHANGES_ALL') THEN
228 DELETE
229 /*+ ROWID (i) */
230 FROM cn_invoice_changes_all i
231 WHERE i.comm_lines_api_id IN
232 (SELECT DISTINCT c.comm_lines_api_id
233 FROM cn_comm_lines_api_all c
234 WHERE c.processed_period_id BETWEEN p_start_period_id AND p_end_period_id
235 )
236 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
237 elsif (p_table_name = 'CN_IMP_HEADERS') THEN
238 l_sql := 'DELETE
239 /*+ ROWID (ih) */
240 FROM cn_imp_headers ih
241 WHERE ih.imp_header_id IN ' || p_addnl_para ||
242 ' AND rowid BETWEEN ' || l_start_rowid || ' AND ' || l_end_rowid;
243 EXECUTE IMMEDIATE l_sql;
244 elsif (p_table_name = 'CN_IMP_LINES') THEN
245 l_sql := 'DELETE
246 /*+ ROWID (cnh) */
247 FROM cn_imp_lines cnh
248 WHERE import_type_code = ''TRXAPI''
249 AND imp_header_id IN ' || p_addnl_para ||
250 ' AND rowid BETWEEN ' || l_start_rowid || ' AND ' || l_end_rowid;
251 EXECUTE IMMEDIATE l_sql;
252 elsif (p_table_name = 'CN_COMM_LINES_API_ALL') THEN
253 DELETE
254 /*+ ROWID (cnh) */
255 FROM cn_comm_lines_api_all cnh
256 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id
257 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
258 elsif (p_table_name = 'CN_SRP_QUOTA_ASSIGNS_ALL') THEN
259 DELETE
260 /*+ ROWID (qa) */
261 FROM cn_srp_quota_assigns_all qa
262 WHERE qa.srp_plan_assign_id IN
263 (SELECT DISTINCT pl.srp_plan_assign_id
264 FROM cn_srp_plan_assigns_all pl
265 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
266 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
267 )
268 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
269 elsif (p_table_name = 'CN_SRP_RATE_ASSIGNS_ALL') THEN
270 DELETE
271 /*+ ROWID (ra) */
272 FROM cn_srp_rate_assigns_all ra
273 WHERE ra.srp_plan_assign_id IN
274 (SELECT DISTINCT pl.srp_plan_assign_id
275 FROM cn_srp_plan_assigns_all pl
276 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
277 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
278 )
279 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
280 elsif (p_table_name = 'CN_SRP_RULE_UPLIFTS_ALL') THEN
281 DELETE
282 /*+ ROWID (ru) */
283 FROM cn_srp_rule_uplifts_all ru
284 WHERE ru.srp_quota_rule_id IN
285 (SELECT DISTINCT qr.srp_quota_rule_id
286 FROM cn_srp_quota_rules_all qr
287 WHERE qr.srp_plan_assign_id IN
288 (SELECT DISTINCT pl.srp_plan_assign_id
289 FROM cn_srp_plan_assigns_all pl
290 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
291 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
292 )
293 )
294 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
295 elsif (p_table_name = 'CN_SRP_QUOTA_RULES_ALL') THEN
296 DELETE
297 /*+ ROWID (ra) */
298 FROM cn_srp_quota_rules_all ra
299 WHERE ra.srp_plan_assign_id IN
300 (SELECT DISTINCT pl.srp_plan_assign_id
301 FROM cn_srp_plan_assigns_all pl
302 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
303 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
304 )
305 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
306 elsif (p_table_name = 'CN_SRP_PLAN_ASSIGNS_ALL') THEN
307 DELETE
308 /*+ ROWID (cnh) */
309 FROM cn_srp_plan_assigns_all cnh
310 WHERE ((start_date BETWEEN x_start_date AND x_end_date)
311 AND (end_date BETWEEN x_start_date AND x_end_date))
312 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
313 elsif (p_table_name = 'CN_SRP_PERIOD_QUOTAS_EXT_ALL') THEN
314 DELETE
315 /*+ ROWID (qe) */
316 FROM cn_srp_period_quotas_ext_all qe
317 WHERE qe.srp_period_quota_id IN
318 (SELECT DISTINCT qa.srp_period_quota_id
319 FROM cn_srp_period_quotas_all qa
320 WHERE qa.period_id BETWEEN p_start_period_id AND p_end_period_id
321 )
322 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
323 elsif (p_table_name = 'CN_SRP_PER_QUOTA_RC_ALL') THEN
324 DELETE
325 /*+ ROWID (cnh) */
326 FROM cn_srp_per_quota_rc_all cnh
327 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id
328 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
329 elsif (p_table_name = 'CN_SRP_PERIOD_QUOTAS_ALL') THEN
330 DELETE
331 /*+ ROWID (cnh) */
332 FROM cn_srp_period_quotas_all cnh
333 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id
334 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
335 elsif (p_table_name = 'CN_PAY_APPROVAL_FLOW_ALL') THEN
336 DELETE
337 /*+ ROWID (af) */
338 FROM cn_pay_approval_flow_all af
339 WHERE af.payrun_id IN
340 (SELECT DISTINCT pa.payrun_id
341 FROM cn_payruns_all pa
342 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
343 )
344 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
345 elsif (p_table_name = 'CN_WORKSHEET_QG_DTLS_ALL') THEN
346 DELETE
347 /*+ ROWID (cnh) */
348 FROM cn_worksheet_qg_dtls_all cnh
349 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id
350 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
351 elsif (p_table_name = 'CN_PAYMENT_WORKSHEETS_ALL') THEN
352 DELETE
353 /*+ ROWID (pw) */
354 FROM cn_payment_worksheets_all pw
355 WHERE pw.payrun_id IN
356 (SELECT DISTINCT pa.payrun_id
357 FROM cn_payruns_all pa
358 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
359 )
360 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
361 elsif (p_table_name = 'CN_LEDGER_JOURNAL_ENTRIES_ALL') THEN
362 DELETE
363 /*+ ROWID (je) */
364 FROM cn_ledger_journal_entries_all je
365 WHERE je.srp_period_id IN
366 (SELECT DISTINCT sp.srp_period_id
367 FROM cn_srp_periods_all sp
368 WHERE sp.period_id BETWEEN p_start_period_id AND p_end_period_id
369 )
370 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
371 elsif (p_table_name = 'CN_POSTING_DETAILS_SUM_ALL') THEN
372 DELETE
373 /*+ ROWID (cnh) */
374 FROM cn_posting_details_sum_all cnh
375 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id
376 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
377 elsif (p_table_name = 'CN_WORKSHEET_BONUSES_ALL') THEN
378 DELETE
379 /*+ ROWID (wb) */
380 FROM cn_worksheet_bonuses_all wb
381 WHERE wb.payrun_id IN
382 (SELECT DISTINCT pa.payrun_id
383 FROM cn_payruns_all pa
384 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
385 )
386 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
387 elsif (p_table_name = 'CN_PAYMENT_WORKSHEETS_ALL') THEN
388 DELETE
389 /*+ ROWID (pw) */
390 FROM cn_payment_worksheets_all pw
391 WHERE pw.payrun_id IN
392 (SELECT DISTINCT pa.payrun_id
393 FROM cn_payruns_all pa
394 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
395 )
396 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
397 elsif (p_table_name = 'CN_PAYRUNS_ALL') THEN
398 DELETE
399 /*+ ROWID (cnh) */
400 FROM cn_payruns_all cnh
401 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id
402 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
403 elsif (p_table_name = 'CN_SRP_PERIODS_ALL') THEN
404 DELETE
405 /*+ ROWID (cnh) */
406 FROM cn_srp_periods_all cnh
407 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id
408 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
409 elsif (p_table_name = 'CN_PROCESS_AUDITS_ALL') THEN
410 --debugmsg('CN_PURGE_TABLES_PVT.delete_table:before deleting CN_PROCESS_AUDITS_ALL ' );
411 DELETE
412 /*+ ROWID (cnh) */
413 FROM cn_process_audits_all cnh
414 WHERE rowid BETWEEN l_start_rowid AND l_end_rowid;
415 elsif (p_table_name = 'CN_PROCESS_AUDIT_LINES_ALL') THEN
416 --debugmsg('CN_PURGE_TABLES_PVT.delete_table:before deleting CN_PROCESS_AUDIT_LINES_ALL ' );
417 DELETE
418 /*+ ROWID (cnh) */
419 FROM cn_process_audit_lines_all cnh
420 WHERE rowid BETWEEN l_start_rowid AND l_end_rowid;
421 elsif (p_table_name = 'CN_PROCESS_BATCHES_ALL') THEN
422 --debugmsg('CN_PURGE_TABLES_PVT.delete_table:before deleting CN_PROCESS_BATCHES_ALL ' );
423 DELETE
424 /*+ ROWID (cnh) */
425 FROM cn_process_batches_all cnh
426 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id
427 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
428 elsif (p_table_name = 'CN_SRP_INTEL_PERIODS_ALL') THEN
429 --debugmsg('CN_PURGE_TABLES_PVT.delete_table:before deleting cn_srp_intel_periods_all ' );
430 DELETE
431 /*+ ROWID (cnh) */
432 FROM cn_srp_intel_periods_all cnh
433 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id
434 AND rowid BETWEEN l_start_rowid AND l_end_rowid;
435 END IF;
436 l_rows_processed := SQL%ROWCOUNT;
437 EXCEPTION
438 WHEN value_error THEN
439 x_return_status := 'F';
440 debugmsg('Before ad_parallel_updates_pkg.processed_rowid_range value_error - ' || sqlerrm);
441 WHEN OTHERS THEN
442 x_return_status := 'F';
443 debugmsg('Before ad_parallel_updates_pkg.processed_rowid_range error - ' || sqlerrm);
444 END;
445 --debugmsg('Before ad_parallel_updates_pkg : rollback before processed_rowid_range - ');
446 --rollback;
447 ad_parallel_updates_pkg.processed_rowid_range( l_rows_processed, l_END_rowid);
448
449 --
450 -- get new range of rowids
451 --
452 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid, l_end_rowid, l_any_rows_to_process, p_batch_size, FALSE);
453
454 END LOOP;
455
456 SELECT TO_CHAR(sysdate,'dd-mm-rr:hh:mi:ss') INTO l_time FROM dual;
457 debugmsg('CN_PURGE_TABLES_PVT.delete_table: delete end l_time ' || l_time );
458 debugmsg('CN_PURGE_TABLES_PVT.delete_table:end final ' );
459 EXCEPTION
460 WHEN OTHERS THEN
461
462 --
463 x_return_status := 'F';
464 debugmsg('Exception After get rowid range');
465 FND_MESSAGE.SET_NAME('CN','CN_PURGE_ERROR');
466 FND_MESSAGE.SET_TOKEN('ERRORMSG', SQLERRM);
467 FND_MSG_PUB.Add;
468 END delete_table;
469
470 -- API name : purge_cn_tables_transactions
471 -- Type : private.
472 -- Pre-reqs :
473 PROCEDURE purge_cn_tables_transactions
474 (
475 p_start_period_id IN NUMBER,
476 p_end_period_id IN NUMBER,
477 x_start_date IN DATE,
478 x_end_date IN DATE,
479 p_cn_archive_all_s IN NUMBER,
480 p_tot_rows_count IN OUT nocopy NUMBER,
481 p_worker_id IN NUMBER,
482 p_no_of_workers IN NUMBER,
483 p_batch_size IN NUMBER,
484 x_msg_count IN OUT nocopy NUMBER,
485 x_msg_data IN OUT nocopy VARCHAR2,
486 x_return_status OUT nocopy VARCHAR2 )
487 IS
488 CURSOR c_overlaping_header_records
489 IS
490 SELECT DISTINCT h.imp_header_id
491 FROM cn_imp_headers h
492 WHERE exists (select distinct l.imp_header_id from cn_imp_lines l where h.imp_header_id = l.imp_header_id
493 and to_date(l.col3, 'dd-mm-rr') between x_start_date AND x_end_date
494 and h.imp_header_id not in (select distinct l2.imp_header_id from cn_imp_lines l2 where
495 to_date(l2.col3, 'dd-mm-rr') not between x_start_date AND x_end_date
496 ));
497
498 l_imp_header_id number := 0;
499 l_imp_header_id_list varchar2(2000) := '';
500 l_imp_header_id_count NUMBER := 0;
501 l_row_count NUMBER;
502 l_table_name VARCHAR2(30);
503 l_table_owner VARCHAR2(30);
504 l_product VARCHAR2(30) := 'CN';
505 l_status VARCHAR2(30);
506 l_industry VARCHAR2(30);
507 l_retstatus BOOLEAN;
508 l_any_rows_to_process varchar2(1);
509 l_sql varchar2(1000);
510
511 BEGIN
512 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_transactions:Start ' );
513
514 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
515
516 IF ( ( l_retstatus = FALSE ) OR ( l_table_owner IS NULL ) ) THEN
517 debugmsg('Cannot get schema name for product :'||l_product);
518 RAISE_APPLICATION_ERROR(-20001,'Cannot get schema name for product :'||l_product);
519 END IF;
520
521 x_return_status := 'S';
522
523 l_table_name := 'cn_payment_api_all';
524 delete_table ( p_start_period_id => p_start_period_id,
525 p_end_period_id => p_end_period_id,
526 x_start_date => x_start_date,
527 x_end_date => x_end_date,
528 p_worker_id => p_worker_id,
529 p_no_of_workers => p_no_of_workers,
530 p_batch_size => p_batch_size,
531 p_table_owner => l_table_owner,
532 p_table_name => upper(l_table_name),
533 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
534 p_addnl_para => '',
535 x_row_to_process_flag => l_any_rows_to_process,
536 x_return_status => x_return_status );
537
538 l_table_name := 'cn_posting_details_all';
539
540 delete_table ( p_start_period_id => p_start_period_id,
541 p_end_period_id => p_end_period_id,
542 x_start_date => x_start_date,
543 x_end_date => x_end_date,
544 p_worker_id => p_worker_id,
545 p_no_of_workers => p_no_of_workers,
546 p_batch_size => p_batch_size,
547 p_table_owner => l_table_owner,
548 p_table_name => upper(l_table_name),
549 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
550 p_addnl_para => '',
551 x_row_to_process_flag => l_any_rows_to_process,
552 x_return_status => x_return_status );
553
554 l_table_name := 'cn_payment_transactions_all';
555
556 delete_table ( p_start_period_id => p_start_period_id,
557 p_end_period_id => p_end_period_id,
558 x_start_date => x_start_date,
559 x_end_date => x_end_date,
560 p_worker_id => p_worker_id,
561 p_no_of_workers => p_no_of_workers,
562 p_batch_size => p_batch_size,
563 p_table_owner => l_table_owner,
564 p_table_name => upper(l_table_name),
565 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
566 p_addnl_para => '',
567 x_row_to_process_flag => l_any_rows_to_process,
568 x_return_status => x_return_status );
569
570 l_table_name := 'cn_commission_lines_all';
571
572 delete_table ( p_start_period_id => p_start_period_id,
573 p_end_period_id => p_end_period_id,
574 x_start_date => x_start_date,
575 x_end_date => x_end_date,
576 p_worker_id => p_worker_id,
577 p_no_of_workers => p_no_of_workers,
578 p_batch_size => p_batch_size,
579 p_table_owner => l_table_owner,
580 p_table_name => upper(l_table_name),
581 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
582 p_addnl_para => '',
583 x_row_to_process_flag => l_any_rows_to_process,
584 x_return_status => x_return_status );
585
586 l_table_name := 'cn_commission_headers_all';
587
588 delete_table ( p_start_period_id => p_start_period_id,
589 p_end_period_id => p_end_period_id,
590 x_start_date => x_start_date,
591 x_end_date => x_end_date,
592 p_worker_id => p_worker_id,
593 p_no_of_workers => p_no_of_workers,
594 p_batch_size => p_batch_size,
595 p_table_owner => l_table_owner,
596 p_table_name => upper(l_table_name),
597 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
598 p_addnl_para => '',
599 x_row_to_process_flag => l_any_rows_to_process,
600 x_return_status => x_return_status );
601
602
603 l_table_name := 'cn_trx_sales_lines_all';
604
605 delete_table ( p_start_period_id => p_start_period_id,
606 p_end_period_id => p_end_period_id,
607 x_start_date => x_start_date,
608 x_end_date => x_end_date,
609 p_worker_id => p_worker_id,
610 p_no_of_workers => p_no_of_workers,
611 p_batch_size => p_batch_size,
612 p_table_owner => l_table_owner,
613 p_table_name => upper(l_table_name),
614 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
615 p_addnl_para => '',
616 x_row_to_process_flag => l_any_rows_to_process,
617 x_return_status => x_return_status );
618
619
620 l_table_name := 'cn_trx_lines_all';
621
622 delete_table ( p_start_period_id => p_start_period_id,
623 p_end_period_id => p_end_period_id,
624 x_start_date => x_start_date,
625 x_end_date => x_end_date,
626 p_worker_id => p_worker_id,
627 p_no_of_workers => p_no_of_workers,
628 p_batch_size => p_batch_size,
629 p_table_owner => l_table_owner,
630 p_table_name => upper(l_table_name),
631 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
632 p_addnl_para => '',
633 x_row_to_process_flag => l_any_rows_to_process,
634 x_return_status => x_return_status );
635
636
637 l_table_name := 'cn_trx_all';
638
639 delete_table ( p_start_period_id => p_start_period_id,
640 p_end_period_id => p_end_period_id,
641 x_start_date => x_start_date,
642 x_end_date => x_end_date,
643 p_worker_id => p_worker_id,
644 p_no_of_workers => p_no_of_workers,
645 p_batch_size => p_batch_size,
646 p_table_owner => l_table_owner,
647 p_table_name => upper(l_table_name),
648 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
649 p_addnl_para => '',
650 x_row_to_process_flag => l_any_rows_to_process,
651 x_return_status => x_return_status );
652
653 l_table_name := 'cn_not_trx_all';
654
655 delete_table ( p_start_period_id => p_start_period_id,
656 p_end_period_id => p_end_period_id,
657 x_start_date => x_start_date,
658 x_end_date => x_end_date,
659 p_worker_id => p_worker_id,
660 p_no_of_workers => p_no_of_workers,
661 p_batch_size => p_batch_size,
662 p_table_owner => l_table_owner,
663 p_table_name => upper(l_table_name),
664 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
665 p_addnl_para => '',
666 x_row_to_process_flag => l_any_rows_to_process,
667 x_return_status => x_return_status );
668
669 l_table_name := 'cn_invoice_changes_all';
670
671 delete_table ( p_start_period_id => p_start_period_id,
672 p_end_period_id => p_end_period_id,
673 x_start_date => x_start_date,
674 x_end_date => x_end_date,
675 p_worker_id => p_worker_id,
676 p_no_of_workers => p_no_of_workers,
677 p_batch_size => p_batch_size,
678 p_table_owner => l_table_owner,
679 p_table_name => upper(l_table_name),
680 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
681 p_addnl_para => '',
682 x_row_to_process_flag => l_any_rows_to_process,
683 x_return_status => x_return_status );
684
685 BEGIN
686
687 OPEN c_overlaping_header_records;
688 LOOP
689 FETCH c_overlaping_header_records INTO l_imp_header_id;
690 EXIT WHEN c_overlaping_header_records%NOTFOUND;
691 if(l_imp_header_id_count = 0) THEN
692 l_imp_header_id_list := l_imp_header_id_list || l_imp_header_id;
693 else
694 l_imp_header_id_list := l_imp_header_id_list || l_imp_header_id || ',';
695 end if;
696 l_imp_header_id_count := l_imp_header_id_count + 1;
697
698 END LOOP;
699 CLOSE c_overlaping_header_records;
700
701 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_transactions:11.2 l_imp_header_id_count ' || l_imp_header_id_count);
702
703 if(l_imp_header_id_count > 1 ) Then
704 l_imp_header_id_list := '(' || l_imp_header_id_list || '-999' || ')';
705 elsif(l_imp_header_id_count = 0 ) Then
706 l_imp_header_id_list := '(-999)';
707 else
708 l_imp_header_id_list := '(' || l_imp_header_id_list || ')';
709 end if;
710 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_transactions:11.4 l_imp_header_id_list ' || l_imp_header_id_list);
711 l_table_name := 'cn_imp_lines';
712
713 delete_table ( p_start_period_id => p_start_period_id,
714 p_end_period_id => p_end_period_id,
715 x_start_date => x_start_date,
716 x_end_date => x_end_date,
717 p_worker_id => p_worker_id,
718 p_no_of_workers => p_no_of_workers,
719 p_batch_size => p_batch_size,
720 p_table_owner => l_table_owner,
721 p_table_name => upper(l_table_name),
722 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
723 p_addnl_para => l_imp_header_id_list,
724 x_row_to_process_flag => l_any_rows_to_process,
725 x_return_status => x_return_status );
726
727 l_table_name := 'cn_imp_headers';
728
729 delete_table ( p_start_period_id => p_start_period_id,
730 p_end_period_id => p_end_period_id,
731 x_start_date => x_start_date,
732 x_end_date => x_end_date,
733 p_worker_id => p_worker_id,
734 p_no_of_workers => p_no_of_workers,
735 p_batch_size => p_batch_size,
736 p_table_owner => l_table_owner,
737 p_table_name => upper(l_table_name),
738 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
739 p_addnl_para => l_imp_header_id_list,
740 x_row_to_process_flag => l_any_rows_to_process,
741 x_return_status => x_return_status );
742
743
744 EXCEPTION
745 WHEN OTHERS THEN
746 --x_return_status := 'F';
747 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_transactions Error (possible error may be cn_imp_lines for col3 date format iisue - ' || sqlerrm);
748
749 END;
750
751 l_table_name := 'cn_comm_lines_api_all';
752
753 delete_table ( p_start_period_id => p_start_period_id,
754 p_end_period_id => p_end_period_id,
755 x_start_date => x_start_date,
756 x_end_date => x_end_date,
757 p_worker_id => p_worker_id,
758 p_no_of_workers => p_no_of_workers,
759 p_batch_size => p_batch_size,
760 p_table_owner => l_table_owner,
761 p_table_name => upper(l_table_name),
762 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
763 p_addnl_para => '',
764 x_row_to_process_flag => l_any_rows_to_process,
765 x_return_status => x_return_status );
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 x_return_status := 'F';
770 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_transactions:exception others: ' || SQLERRM(SQLCODE()) );
771 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
772 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
773 RAISE FND_API.G_EXC_ERROR;
774 END purge_cn_tables_transactions;
775
776 -- API name : audit_purge_cn_transactions
777 -- Type : private.
778 -- Pre-reqs :
779 PROCEDURE audit_purge_cn_transactions
780 (
781 p_start_period_id IN NUMBER,
782 p_end_period_id IN NUMBER,
783 x_start_date IN DATE,
784 x_end_date IN DATE,
785 p_cn_archive_all_s IN NUMBER,
786 p_tot_rows_count IN OUT nocopy NUMBER,
787 p_worker_id IN NUMBER,
788 p_no_of_workers IN NUMBER,
789 p_batch_size IN NUMBER,
790 x_msg_count IN OUT nocopy NUMBER,
791 x_msg_data IN OUT nocopy VARCHAR2,
792 x_return_status OUT nocopy VARCHAR2 )
793 IS
794 CURSOR c_overlaping_header_records
795 IS
796 SELECT DISTINCT h.imp_header_id
797 FROM cn_imp_headers h
798 WHERE exists (select distinct l.imp_header_id from cn_imp_lines l where h.imp_header_id = l.imp_header_id
799 and to_date(l.col3, 'dd-mm-rr') between x_start_date AND x_end_date
800 and h.imp_header_id not in (select distinct l2.imp_header_id from cn_imp_lines l2 where
801 to_date(l2.col3, 'dd-mm-rr') not between x_start_date AND x_end_date
802 ));
803
804 l_imp_header_id number := 0;
805 l_imp_header_id_list varchar2(2000) := '';
806 l_imp_header_id_count NUMBER := 0;
807 l_row_count NUMBER;
808 l_table_name VARCHAR2(30);
809 l_table_owner VARCHAR2(30);
810 l_product VARCHAR2(30) := 'CN';
811 l_status VARCHAR2(30);
812 l_industry VARCHAR2(30);
813 l_retstatus BOOLEAN;
814 l_any_rows_to_process varchar2(1);
815 l_sql varchar2(1000);
816
817 BEGIN
818 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:Start ' );
819
820 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
821
822 IF ( ( l_retstatus = FALSE ) OR ( l_table_owner IS NULL ) ) THEN
823 debugmsg('Cannot get schema name for product :'||l_product);
824 RAISE_APPLICATION_ERROR(-20001,'Cannot get schema name for product :'||l_product);
825 END IF;
826
827 x_return_status := 'S';
828 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:1 x_msg_data ' || x_msg_data);
829
830 l_table_name := 'cn_payment_api_all';
831 SELECT COUNT(*)
832 INTO l_row_count
833 FROM cn_payment_api_all
834 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
835 x_msg_data := x_msg_data || 'cn_payment_api_all count ' || l_row_count || ' : ';
836 x_msg_count := x_msg_count + 1;
837 p_tot_rows_count := p_tot_rows_count + l_row_count;
838 insert_archive(cn_payment_api_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
839 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:2 x_msg_data ' || x_msg_data);
840
841 l_table_name := 'cn_posting_details_all';
842 SELECT COUNT(*)
843 INTO l_row_count
844 FROM cn_posting_details_all
845 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id
846 AND paid_flag = 'Y';
847 x_msg_data := x_msg_data || 'cn_posting_details_all count ' || l_row_count || ' : ';
848 x_msg_count := x_msg_count + 1;
849 p_tot_rows_count := p_tot_rows_count + l_row_count;
850 insert_archive(cn_posting_details_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
851 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:3 x_msg_data ' || x_msg_data);
852
853 l_table_name := 'cn_payment_transactions_all';
854 SELECT COUNT(*)
855 INTO l_row_count
856 FROM cn_payment_transactions_all
857 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id
858 AND paid_flag = 'Y';
859 x_msg_data := x_msg_data || 'cn_payment_transactions_all count ' || l_row_count || ' : ';
860 x_msg_count := x_msg_count + 1;
861 p_tot_rows_count := p_tot_rows_count + l_row_count;
862 insert_archive(cn_payment_transactions_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
863 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:4 x_msg_data ' || x_msg_data);
864
865 l_table_name := 'cn_commission_lines_all';
866 SELECT COUNT(*)
867 INTO l_row_count
868 FROM cn_commission_lines_all
869 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id;
870
871 x_msg_data := x_msg_data || 'cn_commission_lines_all count ' || l_row_count || ' : ';
872 x_msg_count := x_msg_count + 1;
873 p_tot_rows_count := p_tot_rows_count + l_row_count;
874 insert_archive(cn_commission_lines_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
875 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:5 x_msg_data ' || x_msg_data);
876
877 l_table_name := 'cn_commission_headers_all';
878 SELECT COUNT(*)
879 INTO l_row_count
880 FROM cn_commission_headers_all
881 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id;
882
883 x_msg_data := x_msg_data || 'cn_commission_headers_all count ' || l_row_count || ' : ';
884 x_msg_count := x_msg_count + 1;
885 p_tot_rows_count := p_tot_rows_count + l_row_count;
886 insert_archive(cn_commission_headers_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
887 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:6 x_msg_data ' || x_msg_data);
888
889 l_table_name := 'cn_trx_sales_lines_all';
890 SELECT COUNT(*)
891 INTO l_row_count
892 FROM cn_trx_sales_lines_all
893 WHERE TRUNC(processed_date) BETWEEN x_start_date AND x_end_date;
894
895 x_msg_data := x_msg_data || 'cn_trx_sales_lines_all count ' || l_row_count || ' : ';
896 x_msg_count := x_msg_count + 1;
897 p_tot_rows_count := p_tot_rows_count + l_row_count;
898 insert_archive(cn_trx_sales_lines_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
899 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:7 x_msg_data ' || x_msg_data);
900
901 l_table_name := 'cn_trx_lines_all';
902 SELECT COUNT(*)
903 INTO l_row_count
904 FROM cn_trx_lines_all tl
905 WHERE tl.TRX_ID IN
906 (SELECT DISTINCT t.TRX_ID
907 FROM cn_trx_all t
908 WHERE TRUNC(t.processed_date) BETWEEN x_start_date AND x_end_date
909 );
910
911 x_msg_data := x_msg_data || 'cn_trx_lines_all count ' || l_row_count || ' : ';
912 x_msg_count := x_msg_count + 1;
913 p_tot_rows_count := p_tot_rows_count + l_row_count;
914 insert_archive(cn_trx_lines_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
915 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:8 x_msg_data ' || x_msg_data);
916
917 l_table_name := 'cn_trx_all';
918 SELECT COUNT(*)
919 INTO l_row_count
920 FROM cn_trx_all
921 WHERE TRUNC(processed_date) BETWEEN x_start_date AND x_end_date;
922
923 x_msg_data := x_msg_data || 'cn_trx_all count ' || l_row_count || ' : ';
924 x_msg_count := x_msg_count + 1;
925 p_tot_rows_count := p_tot_rows_count + l_row_count;
926 insert_archive(cn_trx_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
927 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:9 x_msg_data ' || x_msg_data);
928
929 l_table_name := 'cn_not_trx_all';
930 SELECT COUNT(*)
931 INTO l_row_count
932 FROM cn_not_trx_all
933 WHERE processed_date BETWEEN x_start_date AND x_end_date;
934
935 x_msg_data := x_msg_data || 'cn_not_trx_all count ' || l_row_count || ' : ';
936 x_msg_count := x_msg_count + 1;
937 p_tot_rows_count := p_tot_rows_count + l_row_count;
938 insert_archive(cn_not_trx_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
939 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:10 x_msg_data ' || x_msg_data);
940
941 l_table_name := 'cn_invoice_changes_all';
942 SELECT COUNT(*)
943 INTO l_row_count
944 FROM cn_invoice_changes_all i
945 WHERE i.comm_lines_api_id IN
946 (SELECT DISTINCT c.comm_lines_api_id
947 FROM cn_comm_lines_api_all c
948 WHERE c.processed_period_id BETWEEN p_start_period_id AND p_end_period_id
949 );
950
951 x_msg_data := x_msg_data || 'cn_invoice_changes_all count ' || l_row_count || ' : ';
952 x_msg_count := x_msg_count + 1;
953 p_tot_rows_count := p_tot_rows_count + l_row_count;
954 insert_archive(cn_invoice_changes_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
955 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:11 x_msg_data ' || x_msg_data);
956
957 BEGIN
958
959 OPEN c_overlaping_header_records;
960 LOOP
961 FETCH c_overlaping_header_records INTO l_imp_header_id;
962 EXIT WHEN c_overlaping_header_records%NOTFOUND;
963 if(l_imp_header_id_count = 0) THEN
964 l_imp_header_id_list := l_imp_header_id_list || l_imp_header_id;
965 else
966 l_imp_header_id_list := l_imp_header_id_list || l_imp_header_id || ',';
967 end if;
968 l_imp_header_id_count := l_imp_header_id_count + 1;
969
970 END LOOP;
971 CLOSE c_overlaping_header_records;
972
973 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:11.2 l_imp_header_id_count ' || l_imp_header_id_count);
974
975 if(l_imp_header_id_count > 1 ) Then
976 l_imp_header_id_list := '(' || l_imp_header_id_list || '-999' || ')';
977 elsif(l_imp_header_id_count = 0 ) Then
978 l_imp_header_id_list := '(-999)';
979 else
980 l_imp_header_id_list := '(' || l_imp_header_id_list || ')';
981 end if;
982 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:11.4 l_imp_header_id_list ' || l_imp_header_id_list);
983 l_table_name := 'cn_imp_lines';
984 l_sql := 'SELECT COUNT(*) FROM cn_imp_lines WHERE import_type_code = ''TRXAPI'' AND imp_header_id in ' || l_imp_header_id_list;
985
986 EXECUTE IMMEDIATE l_sql INTO l_row_count;
987
988 x_msg_data := x_msg_data || 'cn_imp_lines count ' || l_row_count || ' : ';
989 x_msg_count := x_msg_count + 1;
990 p_tot_rows_count := p_tot_rows_count + l_row_count;
991 insert_archive(cn_imp_lines_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
992 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:13 x_msg_data ' || x_msg_data);
993
994
995 l_table_name := 'cn_imp_headers';
996 l_sql := 'SELECT COUNT(*) FROM cn_imp_headers ih WHERE ih.imp_header_id IN ' || l_imp_header_id_list;
997
998 EXECUTE IMMEDIATE l_sql INTO l_row_count;
999
1000 x_msg_data := x_msg_data || 'cn_imp_headers count ' || l_row_count || ' : ';
1001 x_msg_count := x_msg_count + 1;
1002 p_tot_rows_count := p_tot_rows_count + l_row_count;
1003 insert_archive(cn_imp_headers_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1004 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:12 x_msg_data ' || x_msg_data);
1005
1006
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 --x_return_status := 'F';
1010 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions Error (possible error may be cn_imp_lines for col3 date format iisue - ' || sqlerrm);
1011
1012 END;
1013
1014 l_table_name := 'cn_comm_lines_api_all';
1015 SELECT COUNT(*)
1016 INTO l_row_count
1017 FROM cn_comm_lines_api_all
1018 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id;
1019
1020 x_msg_data := x_msg_data || 'cn_comm_lines_api_all count ' || l_row_count || ' : ';
1021 x_msg_count := x_msg_count + 1;
1022 p_tot_rows_count := p_tot_rows_count + l_row_count;
1023 insert_archive(cn_comm_lines_api_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1024 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:14 x_msg_data ' || x_msg_data);
1025
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 x_return_status := 'F';
1029 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_transactions:exception others: ' || SQLERRM(SQLCODE()) );
1030 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
1031 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
1032 RAISE FND_API.G_EXC_ERROR;
1033 END audit_purge_cn_transactions;
1034
1035 -- API name : audit_purge_cn_subledgers
1036 -- Type : Private.
1037 -- Pre-reqs :
1038 PROCEDURE purge_cn_tables_subledgers
1039 (
1040 p_start_period_id IN NUMBER,
1041 p_end_period_id IN NUMBER,
1042 x_start_date IN DATE,
1043 x_end_date IN DATE,
1044 p_cn_archive_all_s IN NUMBER,
1045 p_tot_rows_count IN OUT nocopy NUMBER,
1046 p_worker_id IN NUMBER,
1047 p_no_of_workers IN NUMBER,
1048 p_batch_size IN NUMBER,
1049 x_msg_count IN OUT nocopy NUMBER,
1050 x_msg_data IN OUT nocopy VARCHAR2,
1051 x_return_status OUT nocopy VARCHAR2 )
1052 IS
1053 l_row_count NUMBER;
1054 l_table_name VARCHAR2(30);
1055 l_table_owner VARCHAR2(30);
1056 l_product VARCHAR2(30) := 'CN';
1057 l_status VARCHAR2(30);
1058 l_industry VARCHAR2(30);
1059 l_retstatus BOOLEAN;
1060 l_any_rows_to_process varchar2(1);
1061
1062 BEGIN
1063 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers: start ');
1064
1065 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
1066
1067 IF ( ( l_retstatus = FALSE ) OR ( l_table_owner IS NULL ) ) THEN
1068 debugmsg('Cannot get schema name for product :'||l_product);
1069 RAISE_APPLICATION_ERROR(-20001,'Cannot get schema name for product :'||l_product);
1070 END IF;
1071
1072 x_return_status := 'S';
1073
1074 l_table_name := 'cn_srp_period_quotas_ext_all';
1075
1076 delete_table ( p_start_period_id => p_start_period_id,
1077 p_end_period_id => p_end_period_id,
1078 x_start_date => x_start_date,
1079 x_end_date => x_end_date,
1080 p_worker_id => p_worker_id,
1081 p_no_of_workers => p_no_of_workers,
1082 p_batch_size => p_batch_size,
1083 p_table_owner => l_table_owner,
1084 p_table_name => upper(l_table_name),
1085 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1086 p_addnl_para => '',
1087 x_row_to_process_flag => l_any_rows_to_process,
1088 x_return_status => x_return_status );
1089
1090
1091 l_table_name := 'cn_srp_per_quota_rc_all';
1092
1093 delete_table ( p_start_period_id => p_start_period_id,
1094 p_end_period_id => p_end_period_id,
1095 x_start_date => x_start_date,
1096 x_end_date => x_end_date,
1097 p_worker_id => p_worker_id,
1098 p_no_of_workers => p_no_of_workers,
1099 p_batch_size => p_batch_size,
1100 p_table_owner => l_table_owner,
1101 p_table_name => upper(l_table_name),
1102 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1103 p_addnl_para => '',
1104 x_row_to_process_flag => l_any_rows_to_process,
1105 x_return_status => x_return_status );
1106
1107 l_table_name := 'cn_srp_period_quotas_all';
1108
1109 delete_table ( p_start_period_id => p_start_period_id,
1110 p_end_period_id => p_end_period_id,
1111 x_start_date => x_start_date,
1112 x_end_date => x_end_date,
1113 p_worker_id => p_worker_id,
1114 p_no_of_workers => p_no_of_workers,
1115 p_batch_size => p_batch_size,
1116 p_table_owner => l_table_owner,
1117 p_table_name => upper(l_table_name),
1118 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1119 p_addnl_para => '',
1120 x_row_to_process_flag => l_any_rows_to_process,
1121 x_return_status => x_return_status );
1122
1123
1124 l_table_name := 'cn_pay_approval_flow_all';
1125
1126 delete_table ( p_start_period_id => p_start_period_id,
1127 p_end_period_id => p_end_period_id,
1128 x_start_date => x_start_date,
1129 x_end_date => x_end_date,
1130 p_worker_id => p_worker_id,
1131 p_no_of_workers => p_no_of_workers,
1132 p_batch_size => p_batch_size,
1133 p_table_owner => l_table_owner,
1134 p_table_name => upper(l_table_name),
1135 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1136 p_addnl_para => '',
1137 x_row_to_process_flag => l_any_rows_to_process,
1138 x_return_status => x_return_status );
1139
1140 l_table_name := 'cn_worksheet_qg_dtls_all';
1141
1142 delete_table ( p_start_period_id => p_start_period_id,
1143 p_end_period_id => p_end_period_id,
1144 x_start_date => x_start_date,
1145 x_end_date => x_end_date,
1146 p_worker_id => p_worker_id,
1147 p_no_of_workers => p_no_of_workers,
1148 p_batch_size => p_batch_size,
1149 p_table_owner => l_table_owner,
1150 p_table_name => upper(l_table_name),
1151 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1152 p_addnl_para => '',
1153 x_row_to_process_flag => l_any_rows_to_process,
1154 x_return_status => x_return_status );
1155
1156 l_table_name := 'cn_payment_worksheets_all';
1157
1158 delete_table ( p_start_period_id => p_start_period_id,
1159 p_end_period_id => p_end_period_id,
1160 x_start_date => x_start_date,
1161 x_end_date => x_end_date,
1162 p_worker_id => p_worker_id,
1163 p_no_of_workers => p_no_of_workers,
1164 p_batch_size => p_batch_size,
1165 p_table_owner => l_table_owner,
1166 p_table_name => upper(l_table_name),
1167 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1168 p_addnl_para => '',
1169 x_row_to_process_flag => l_any_rows_to_process,
1170 x_return_status => x_return_status );
1171
1172
1173 l_table_name := 'cn_ledger_journal_entries_all';
1174
1175 delete_table ( p_start_period_id => p_start_period_id,
1176 p_end_period_id => p_end_period_id,
1177 x_start_date => x_start_date,
1178 x_end_date => x_end_date,
1179 p_worker_id => p_worker_id,
1180 p_no_of_workers => p_no_of_workers,
1181 p_batch_size => p_batch_size,
1182 p_table_owner => l_table_owner,
1183 p_table_name => upper(l_table_name),
1184 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1185 p_addnl_para => '',
1186 x_row_to_process_flag => l_any_rows_to_process,
1187 x_return_status => x_return_status );
1188
1189 l_table_name := 'cn_posting_details_sum_all';
1190
1191 delete_table ( p_start_period_id => p_start_period_id,
1192 p_end_period_id => p_end_period_id,
1193 x_start_date => x_start_date,
1194 x_end_date => x_end_date,
1195 p_worker_id => p_worker_id,
1196 p_no_of_workers => p_no_of_workers,
1197 p_batch_size => p_batch_size,
1198 p_table_owner => l_table_owner,
1199 p_table_name => upper(l_table_name),
1200 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1201 p_addnl_para => '',
1202 x_row_to_process_flag => l_any_rows_to_process,
1203 x_return_status => x_return_status );
1204
1205 l_table_name := 'cn_worksheet_bonuses_all';
1206
1207 delete_table ( p_start_period_id => p_start_period_id,
1208 p_end_period_id => p_end_period_id,
1209 x_start_date => x_start_date,
1210 x_end_date => x_end_date,
1211 p_worker_id => p_worker_id,
1212 p_no_of_workers => p_no_of_workers,
1213 p_batch_size => p_batch_size,
1214 p_table_owner => l_table_owner,
1215 p_table_name => upper(l_table_name),
1216 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1217 p_addnl_para => '',
1218 x_row_to_process_flag => l_any_rows_to_process,
1219 x_return_status => x_return_status );
1220
1221 l_table_name := 'cn_payruns_all';
1222
1223 delete_table ( p_start_period_id => p_start_period_id,
1224 p_end_period_id => p_end_period_id,
1225 x_start_date => x_start_date,
1226 x_end_date => x_end_date,
1227 p_worker_id => p_worker_id,
1228 p_no_of_workers => p_no_of_workers,
1229 p_batch_size => p_batch_size,
1230 p_table_owner => l_table_owner,
1231 p_table_name => upper(l_table_name),
1232 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1233 p_addnl_para => '',
1234 x_row_to_process_flag => l_any_rows_to_process,
1235 x_return_status => x_return_status );
1236
1237 l_table_name := 'cn_srp_periods_all';
1238
1239 delete_table ( p_start_period_id => p_start_period_id,
1240 p_end_period_id => p_end_period_id,
1241 x_start_date => x_start_date,
1242 x_end_date => x_end_date,
1243 p_worker_id => p_worker_id,
1244 p_no_of_workers => p_no_of_workers,
1245 p_batch_size => p_batch_size,
1246 p_table_owner => l_table_owner,
1247 p_table_name => upper(l_table_name),
1248 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1249 p_addnl_para => '',
1250 x_row_to_process_flag => l_any_rows_to_process,
1251 x_return_status => x_return_status );
1252
1253 EXCEPTION
1254 WHEN OTHERS THEN
1255 x_return_status := 'F';
1256 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_subledgers:exception others: ' || SQLERRM(SQLCODE()) );
1257 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
1258 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
1259 RAISE FND_API.G_EXC_ERROR;
1260 END purge_cn_tables_subledgers;
1261
1262 -- API name : audit_purge_cn_subledgers
1263 -- Type : Private.
1264 -- Pre-reqs :
1265 PROCEDURE audit_purge_cn_subledgers
1266 (
1267 p_start_period_id IN NUMBER,
1268 p_end_period_id IN NUMBER,
1269 x_start_date IN DATE,
1270 x_end_date IN DATE,
1271 p_cn_archive_all_s IN NUMBER,
1272 p_tot_rows_count IN OUT nocopy NUMBER,
1273 p_worker_id IN NUMBER,
1274 p_no_of_workers IN NUMBER,
1275 p_batch_size IN NUMBER,
1276 x_msg_count IN OUT nocopy NUMBER,
1277 x_msg_data IN OUT nocopy VARCHAR2,
1278 x_return_status OUT nocopy VARCHAR2 )
1279 IS
1280 l_row_count NUMBER;
1281 l_table_name VARCHAR2(30);
1282 l_table_owner VARCHAR2(30);
1283 l_product VARCHAR2(30) := 'CN';
1284 l_status VARCHAR2(30);
1285 l_industry VARCHAR2(30);
1286 l_retstatus BOOLEAN;
1287 l_any_rows_to_process varchar2(1);
1288
1289 BEGIN
1290 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers: start ');
1291
1292 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
1293
1294 IF ( ( l_retstatus = FALSE ) OR ( l_table_owner IS NULL ) ) THEN
1295 debugmsg('Cannot get schema name for product :'||l_product);
1296 RAISE_APPLICATION_ERROR(-20001,'Cannot get schema name for product :'||l_product);
1297 END IF;
1298
1299 x_return_status := 'S';
1300 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:0 x_msg_data ' || x_msg_data);
1301
1302 l_table_name := 'cn_srp_period_quotas_ext_all';
1303 SELECT COUNT(*)
1304 INTO l_row_count
1305 FROM cn_srp_period_quotas_ext_all qe
1306 WHERE qe.srp_period_quota_id IN
1307 (SELECT DISTINCT qa.srp_period_quota_id
1308 FROM cn_srp_period_quotas_all qa
1309 WHERE qa.period_id BETWEEN p_start_period_id AND p_end_period_id
1310 );
1311
1312 x_msg_data := x_msg_data || 'cn_srp_period_quotas_ext_all count ' || l_row_count || ' : ';
1313
1314 x_msg_count := x_msg_count + 1;
1315 p_tot_rows_count := p_tot_rows_count + l_row_count;
1316 insert_archive(cn_srp_period_quotas_ext_all_i,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1317 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:1 x_msg_data ' || x_msg_data);
1318
1319 l_table_name := 'cn_srp_per_quota_rc_all';
1320 SELECT COUNT(*)
1321 INTO l_row_count
1322 FROM cn_srp_per_quota_rc_all
1323 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
1324
1325 x_msg_data := x_msg_data || 'cn_srp_per_quota_rc_all count ' || l_row_count || ' : ';
1326
1327 x_msg_count := x_msg_count + 1;
1328 p_tot_rows_count := p_tot_rows_count + l_row_count;
1329 insert_archive(cn_srp_per_quota_rc_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1330 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:2 x_msg_data ' || x_msg_data);
1331
1332 l_table_name := 'cn_srp_period_quotas_all';
1333 SELECT COUNT(*)
1334 INTO l_row_count
1335 FROM cn_srp_period_quotas_all
1336 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
1337
1338 x_msg_data := x_msg_data || 'cn_srp_period_quotas_all count ' || l_row_count || ' : ';
1339
1340 x_msg_count := x_msg_count + 1;
1341 p_tot_rows_count := p_tot_rows_count + l_row_count;
1342 insert_archive(cn_srp_period_quotas_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1343 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:3 x_msg_data ' || x_msg_data);
1344
1345 l_table_name := 'cn_pay_approval_flow_all';
1346 SELECT COUNT(*)
1347 INTO l_row_count
1348 FROM cn_pay_approval_flow_all af
1349 WHERE af.payrun_id IN
1350 (SELECT DISTINCT pa.payrun_id
1351 FROM cn_payruns_all pa
1352 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
1353 );
1354
1355 x_msg_data := x_msg_data || 'cn_pay_approval_flow_all count ' || l_row_count || ' : ';
1356
1357 x_msg_count := x_msg_count + 1;
1358 p_tot_rows_count := p_tot_rows_count + l_row_count;
1359 insert_archive(cn_pay_approval_flow_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1360 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:4 x_msg_data ' || x_msg_data);
1361
1362 l_table_name := 'cn_worksheet_qg_dtls_all';
1363 SELECT COUNT(*)
1364 INTO l_row_count
1365 FROM cn_worksheet_qg_dtls_all
1366 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
1367
1368 x_msg_data := x_msg_data || 'cn_worksheet_qg_dtls_all count ' || l_row_count || ' : ';
1369
1370 x_msg_count := x_msg_count + 1;
1371 p_tot_rows_count := p_tot_rows_count + l_row_count;
1372 insert_archive(cn_worksheet_qg_dtls_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1373 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:5 x_msg_data ' || x_msg_data);
1374
1375 l_table_name := 'cn_payment_worksheets_all';
1376 SELECT COUNT(*)
1377 INTO l_row_count
1378 FROM cn_payment_worksheets_all pw
1379 WHERE pw.payrun_id IN
1380 (SELECT DISTINCT pa.payrun_id
1381 FROM cn_payruns_all pa
1382 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
1383 );
1384
1385 x_msg_data := x_msg_data || 'cn_payment_worksheets_all count ' || l_row_count || ' : ';
1386
1387 x_msg_count := x_msg_count + 1;
1388 p_tot_rows_count := p_tot_rows_count + l_row_count;
1389 insert_archive(cn_payment_worksheets_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1390 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:6 x_msg_data ' || x_msg_data);
1391
1392 l_table_name := 'cn_ledger_journal_entries_all';
1393 SELECT COUNT(*)
1394 INTO l_row_count
1395 FROM cn_ledger_journal_entries_all je
1396 WHERE je.srp_period_id IN
1397 (SELECT DISTINCT sp.srp_period_id
1398 FROM cn_srp_periods_all sp
1399 WHERE sp.period_id BETWEEN p_start_period_id AND p_end_period_id
1400 );
1401
1402 x_msg_data := x_msg_data || 'cn_ledger_journal_entries_all count ' || l_row_count || ' : ';
1403
1404 x_msg_count := x_msg_count + 1;
1405 p_tot_rows_count := p_tot_rows_count + l_row_count;
1406 insert_archive(cn_ledger_journal_entries_alli,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1407 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:7 x_msg_data ' || x_msg_data);
1408
1409 l_table_name := 'cn_posting_details_sum_all';
1410 SELECT COUNT(*)
1411 INTO l_row_count
1412 FROM cn_posting_details_sum_all
1413 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id;
1414
1415 x_msg_data := x_msg_data || 'cn_posting_details_sum_all count ' || l_row_count || ' : ';
1416
1417 x_msg_count := x_msg_count + 1;
1418 p_tot_rows_count := p_tot_rows_count + l_row_count;
1419 insert_archive(cn_posting_details_sum_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1420 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:8 x_msg_data ' || x_msg_data);
1421
1422 l_table_name := 'cn_worksheet_bonuses_all';
1423 SELECT COUNT(*)
1424 INTO l_row_count
1425 FROM cn_worksheet_bonuses_all wb
1426 WHERE wb.payrun_id IN
1427 (SELECT DISTINCT pa.payrun_id
1428 FROM cn_payruns_all pa
1429 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
1430 );
1431
1432 x_msg_data := x_msg_data || 'cn_worksheet_bonuses_all count ' || l_row_count || ' : ';
1433
1434 x_msg_count := x_msg_count + 1;
1435 p_tot_rows_count := p_tot_rows_count + l_row_count;
1436 insert_archive(cn_worksheet_bonuses_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1437 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:9 x_msg_data ' || x_msg_data);
1438
1439 l_table_name := 'cn_payruns_all';
1440 SELECT COUNT(*)
1441 INTO l_row_count
1442 FROM cn_payruns_all
1443 WHERE PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id;
1444
1445 x_msg_data := x_msg_data || 'cn_payruns_all count ' || l_row_count || ' : ';
1446
1447 x_msg_count := x_msg_count + 1;
1448 p_tot_rows_count := p_tot_rows_count + l_row_count;
1449 insert_archive(cn_payruns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1450 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:10 x_msg_data ' || x_msg_data);
1451
1452 l_table_name := 'cn_srp_periods_all';
1453 SELECT COUNT(*)
1454 INTO l_row_count
1455 FROM cn_srp_periods_all
1456 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
1457
1458 x_msg_data := x_msg_data || 'cn_srp_periods_all count ' || l_row_count || ' : ';
1459
1460 x_msg_count := x_msg_count + 1;
1461 p_tot_rows_count := p_tot_rows_count + l_row_count;
1462 insert_archive(cn_srp_periods_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1463 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:11 x_msg_data ' || x_msg_data);
1464
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 x_return_status := 'F';
1468 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_subledgers:exception others: ' || SQLERRM(SQLCODE()) );
1469 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
1470 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
1471 RAISE FND_API.G_EXC_ERROR;
1472 END audit_purge_cn_subledgers;
1473
1474 -- API name : purge_cn_tables_refrences
1475 -- Type : private.
1476 -- Pre-reqs :
1477 PROCEDURE purge_cn_tables_refrences
1478 (
1479 p_start_period_id IN NUMBER,
1480 p_end_period_id IN NUMBER,
1481 x_start_date IN DATE,
1482 x_end_date IN DATE,
1483 p_cn_archive_all_s IN NUMBER,
1484 p_tot_rows_count IN OUT nocopy NUMBER,
1485 p_worker_id IN NUMBER,
1486 p_no_of_workers IN NUMBER,
1487 p_batch_size IN NUMBER,
1488 x_msg_count IN OUT nocopy NUMBER,
1489 x_msg_data IN OUT nocopy VARCHAR2,
1490 x_return_status OUT nocopy VARCHAR2 )
1491 IS
1492 l_row_count NUMBER;
1493 l_table_name VARCHAR2(30);
1494 l_table_owner VARCHAR2(30);
1495 l_product VARCHAR2(30) := 'CN';
1496 l_status VARCHAR2(30);
1497 l_industry VARCHAR2(30);
1498 l_retstatus BOOLEAN;
1499 l_any_rows_to_process varchar2(1);
1500
1501 BEGIN
1502 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_refrences: start ');
1503
1504 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
1505
1506 IF ( ( l_retstatus = FALSE ) OR ( l_table_owner IS NULL ) ) THEN
1507 debugmsg('Cannot get schema name for product :'||l_product);
1508 RAISE_APPLICATION_ERROR(-20001,'Cannot get schema name for product :'||l_product);
1509 END IF;
1510
1511 x_return_status := 'S';
1512
1513 l_table_name := 'cn_srp_payee_assigns_all';
1514 delete_table ( p_start_period_id => p_start_period_id,
1515 p_end_period_id => p_end_period_id,
1516 x_start_date => x_start_date,
1517 x_end_date => x_end_date,
1518 p_worker_id => p_worker_id,
1519 p_no_of_workers => p_no_of_workers,
1520 p_batch_size => p_batch_size,
1521 p_table_owner => l_table_owner,
1522 p_table_name => upper(l_table_name),
1523 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1524 p_addnl_para => '',
1525 x_row_to_process_flag => l_any_rows_to_process,
1526 x_return_status => x_return_status );
1527
1528
1529 l_table_name := 'cn_srp_quota_assigns_all';
1530
1531 delete_table ( p_start_period_id => p_start_period_id,
1532 p_end_period_id => p_end_period_id,
1533 x_start_date => x_start_date,
1534 x_end_date => x_end_date,
1535 p_worker_id => p_worker_id,
1536 p_no_of_workers => p_no_of_workers,
1537 p_batch_size => p_batch_size,
1538 p_table_owner => l_table_owner,
1539 p_table_name => upper(l_table_name),
1540 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1541 p_addnl_para => '',
1542 x_row_to_process_flag => l_any_rows_to_process,
1543 x_return_status => x_return_status );
1544
1545 l_table_name := 'cn_srp_rate_assigns_all';
1546
1547 delete_table ( p_start_period_id => p_start_period_id,
1548 p_end_period_id => p_end_period_id,
1549 x_start_date => x_start_date,
1550 x_end_date => x_end_date,
1551 p_worker_id => p_worker_id,
1552 p_no_of_workers => p_no_of_workers,
1553 p_batch_size => p_batch_size,
1554 p_table_owner => l_table_owner,
1555 p_table_name => upper(l_table_name),
1556 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1557 p_addnl_para => '',
1558 x_row_to_process_flag => l_any_rows_to_process,
1559 x_return_status => x_return_status );
1560
1561 l_table_name := 'cn_srp_rule_uplifts_all';
1562
1563 delete_table ( p_start_period_id => p_start_period_id,
1564 p_end_period_id => p_end_period_id,
1565 x_start_date => x_start_date,
1566 x_end_date => x_end_date,
1567 p_worker_id => p_worker_id,
1568 p_no_of_workers => p_no_of_workers,
1569 p_batch_size => p_batch_size,
1570 p_table_owner => l_table_owner,
1571 p_table_name => upper(l_table_name),
1572 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1573 p_addnl_para => '',
1574 x_row_to_process_flag => l_any_rows_to_process,
1575 x_return_status => x_return_status );
1576
1577
1578 l_table_name := 'cn_srp_quota_rules_all';
1579
1580 delete_table ( p_start_period_id => p_start_period_id,
1581 p_end_period_id => p_end_period_id,
1582 x_start_date => x_start_date,
1583 x_end_date => x_end_date,
1584 p_worker_id => p_worker_id,
1585 p_no_of_workers => p_no_of_workers,
1586 p_batch_size => p_batch_size,
1587 p_table_owner => l_table_owner,
1588 p_table_name => upper(l_table_name),
1589 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1590 p_addnl_para => '',
1591 x_row_to_process_flag => l_any_rows_to_process,
1592 x_return_status => x_return_status );
1593
1594 l_table_name := 'cn_srp_plan_assigns_all';
1595
1596 delete_table ( p_start_period_id => p_start_period_id,
1597 p_end_period_id => p_end_period_id,
1598 x_start_date => x_start_date,
1599 x_end_date => x_end_date,
1600 p_worker_id => p_worker_id,
1601 p_no_of_workers => p_no_of_workers,
1602 p_batch_size => p_batch_size,
1603 p_table_owner => l_table_owner,
1604 p_table_name => upper(l_table_name),
1605 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1606 p_addnl_para => '',
1607 x_row_to_process_flag => l_any_rows_to_process,
1608 x_return_status => x_return_status );
1609
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 x_return_status := 'F';
1614 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_transactions:exception others: ' || SQLERRM(SQLCODE()) );
1615 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
1616 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
1617 RAISE FND_API.G_EXC_ERROR;
1618 END purge_cn_tables_refrences;
1619
1620 -- API name : purge_cn_tables_refrences
1621 -- Type : private.
1622 -- Pre-reqs :
1623 PROCEDURE audit_purge_cn_refrences
1624 (
1625 p_start_period_id IN NUMBER,
1626 p_end_period_id IN NUMBER,
1627 x_start_date IN DATE,
1628 x_end_date IN DATE,
1629 p_cn_archive_all_s IN NUMBER,
1630 p_tot_rows_count IN OUT nocopy NUMBER,
1631 p_worker_id IN NUMBER,
1632 p_no_of_workers IN NUMBER,
1633 p_batch_size IN NUMBER,
1634 x_msg_count IN OUT nocopy NUMBER,
1635 x_msg_data IN OUT nocopy VARCHAR2,
1636 x_return_status OUT nocopy VARCHAR2 )
1637 IS
1638 l_row_count NUMBER;
1639 l_table_name VARCHAR2(30);
1640 l_table_owner VARCHAR2(30);
1641 l_product VARCHAR2(30) := 'CN';
1642 l_status VARCHAR2(30);
1643 l_industry VARCHAR2(30);
1644 l_retstatus BOOLEAN;
1645 l_any_rows_to_process varchar2(1);
1646
1647 BEGIN
1648 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences: start ');
1649
1650 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
1651
1652 IF ( ( l_retstatus = FALSE ) OR ( l_table_owner IS NULL ) ) THEN
1653 debugmsg('Cannot get schema name for product :'||l_product);
1654 RAISE_APPLICATION_ERROR(-20001,'Cannot get schema name for product :'||l_product);
1655 END IF;
1656
1657 x_return_status := 'S';
1658 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences:0 x_msg_data ' || x_msg_data);
1659
1660 l_table_name := 'cn_srp_payee_assigns_all';
1661 SELECT COUNT(*)
1662 INTO l_row_count
1663 FROM cn_srp_payee_assigns_all pa
1664 WHERE pa.srp_quota_assign_id IN
1665 (SELECT DISTINCT qa.srp_quota_assign_id
1666 FROM cn_srp_period_quotas_all qa
1667 WHERE qa.srp_plan_assign_id IN
1668 (SELECT DISTINCT pl.srp_plan_assign_id
1669 FROM cn_srp_plan_assigns_all pl
1670 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
1671 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
1672 )
1673 );
1674
1675 x_msg_data := x_msg_data || 'cn_srp_payee_assigns_all count ' || l_row_count || ' : ';
1676
1677 x_msg_count := x_msg_count + 1;
1678 p_tot_rows_count := p_tot_rows_count + l_row_count;
1679 insert_archive(cn_srp_payee_assigns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1680 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences:1 x_msg_data ' || x_msg_data);
1681
1682 l_table_name := 'cn_srp_quota_assigns_all';
1683 SELECT COUNT(*)
1684 INTO l_row_count
1685 FROM cn_srp_quota_assigns_all qa
1686 WHERE qa.srp_plan_assign_id IN
1687 (SELECT DISTINCT pl.srp_plan_assign_id
1688 FROM cn_srp_plan_assigns_all pl
1689 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
1690 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
1691 );
1692
1693 x_msg_data := x_msg_data || 'cn_srp_quota_assigns_all count ' || l_row_count || ' : ';
1694
1695 x_msg_count := x_msg_count + 1;
1696 p_tot_rows_count := p_tot_rows_count + l_row_count;
1697 insert_archive(cn_srp_quota_assigns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1698 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences:2 x_msg_data ' || x_msg_data);
1699
1700 l_table_name := 'cn_srp_rate_assigns_all';
1701 SELECT COUNT(*)
1702 INTO l_row_count
1703 FROM cn_srp_rate_assigns_all ra
1704 WHERE ra.srp_plan_assign_id IN
1705 (SELECT DISTINCT pl.srp_plan_assign_id
1706 FROM cn_srp_plan_assigns_all pl
1707 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
1708 AND pl.end_date BETWEEN x_start_date AND x_end_date)
1709 );
1710
1711 x_msg_data := x_msg_data || 'cn_srp_rate_assigns_all count ' || l_row_count || ' : ';
1712
1713 x_msg_count := x_msg_count + 1;
1714 p_tot_rows_count := p_tot_rows_count + l_row_count;
1715 insert_archive(cn_srp_rate_assigns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1716 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences:3 x_msg_data ' || x_msg_data);
1717
1718 l_table_name := 'cn_srp_rule_uplifts_all';
1719 SELECT COUNT(*)
1720 INTO l_row_count
1721 FROM cn_srp_rule_uplifts_all ru
1722 WHERE ru.srp_quota_rule_id IN
1723 (SELECT DISTINCT qr.srp_quota_rule_id
1724 FROM cn_srp_quota_rules_all qr
1725 WHERE qr.srp_plan_assign_id IN
1726 (SELECT DISTINCT pl.srp_plan_assign_id
1727 FROM cn_srp_plan_assigns_all pl
1728 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
1729 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
1730 )
1731 );
1732
1733 x_msg_data := x_msg_data || 'cn_srp_rule_uplifts_all count ' || l_row_count || ' : ';
1734
1735 x_msg_count := x_msg_count + 1;
1736 p_tot_rows_count := p_tot_rows_count + l_row_count;
1737 insert_archive(cn_srp_rule_uplifts_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1738 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences:4 x_msg_data ' || x_msg_data);
1739
1740 l_table_name := 'cn_srp_quota_rules_all';
1741 SELECT COUNT(*)
1742 INTO l_row_count
1743 FROM cn_srp_quota_rules_all ra
1744 WHERE ra.srp_plan_assign_id IN
1745 (SELECT DISTINCT pl.srp_plan_assign_id
1746 FROM cn_srp_plan_assigns_all pl
1747 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
1748 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
1749 );
1750
1751 x_msg_data := x_msg_data || 'cn_srp_quota_rules_all count ' || l_row_count || ' : ';
1752
1753 x_msg_count := x_msg_count + 1;
1754 p_tot_rows_count := p_tot_rows_count + l_row_count;
1755 insert_archive(cn_srp_quota_rules_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1756 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences:5 x_msg_data ' || x_msg_data);
1757
1758 l_table_name := 'cn_srp_plan_assigns_all';
1759 SELECT COUNT(*)
1760 INTO l_row_count
1761 FROM cn_srp_plan_assigns_all
1762 WHERE ((start_date BETWEEN x_start_date AND x_end_date)
1763 AND (end_date BETWEEN x_start_date AND x_end_date));
1764
1765 x_msg_data := x_msg_data || 'cn_srp_plan_assigns_all count ' || l_row_count || ' : ';
1766
1767 x_msg_count := x_msg_count + 1;
1768 p_tot_rows_count := p_tot_rows_count + l_row_count;
1769 insert_archive(cn_srp_plan_assigns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1770 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences:6 x_msg_data ' || x_msg_data);
1771
1772 EXCEPTION
1773 WHEN OTHERS THEN
1774 x_return_status := 'F';
1775 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_refrences:exception others: ' || SQLERRM(SQLCODE()) );
1776 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
1777 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
1778 RAISE FND_API.G_EXC_ERROR;
1779 END audit_purge_cn_refrences;
1780
1781 -- API name : purge_cn_tables_processing
1782 -- Type : private.
1783 -- Pre-reqs :
1784 PROCEDURE purge_cn_tables_processing
1785 (
1786 p_start_period_id IN NUMBER,
1787 p_end_period_id IN NUMBER,
1788 x_start_date IN DATE,
1789 x_end_date IN DATE,
1790 p_cn_archive_all_s IN NUMBER,
1791 p_tot_rows_count IN OUT nocopy NUMBER,
1792 p_worker_id IN NUMBER,
1793 p_no_of_workers IN NUMBER,
1794 p_batch_size IN NUMBER,
1795 x_msg_count IN OUT nocopy NUMBER,
1796 x_msg_data IN OUT nocopy VARCHAR2,
1797 x_return_status OUT nocopy VARCHAR2 )
1798 IS
1799 l_row_count NUMBER;
1800 l_table_name VARCHAR2(30);
1801 l_table_owner VARCHAR2(30);
1802 l_product VARCHAR2(30) := 'CN';
1803 l_status VARCHAR2(30);
1804 l_industry VARCHAR2(30);
1805 l_retstatus BOOLEAN;
1806 l_any_rows_to_process varchar2(1);
1807 BEGIN
1808 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_processing: start ' );
1809
1810 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
1811
1812 IF ( ( l_retstatus = FALSE ) OR ( l_table_owner IS NULL ) ) THEN
1813 debugmsg('Cannot get schema name for product :'||l_product);
1814 RAISE_APPLICATION_ERROR(-20001,'Cannot get schema name for product :'||l_product);
1815 END IF;
1816
1817 x_return_status := 'S';
1818
1819 l_table_name := 'cn_process_audit_lines_all';
1820
1821 delete_table ( p_start_period_id => p_start_period_id,
1822 p_end_period_id => p_end_period_id,
1823 x_start_date => x_start_date,
1824 x_end_date => x_end_date,
1825 p_worker_id => p_worker_id,
1826 p_no_of_workers => p_no_of_workers,
1827 p_batch_size => p_batch_size,
1828 p_table_owner => l_table_owner,
1829 p_table_name => upper(l_table_name),
1830 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1831 p_addnl_para => '',
1832 x_row_to_process_flag => l_any_rows_to_process,
1833 x_return_status => x_return_status );
1834
1835
1836 l_table_name := 'cn_process_audits_all';
1837
1838 delete_table ( p_start_period_id => p_start_period_id,
1839 p_end_period_id => p_end_period_id,
1840 x_start_date => x_start_date,
1841 x_end_date => x_end_date,
1842 p_worker_id => p_worker_id,
1843 p_no_of_workers => p_no_of_workers,
1844 p_batch_size => p_batch_size,
1845 p_table_owner => l_table_owner,
1846 p_table_name => upper(l_table_name),
1847 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1848 p_addnl_para => '',
1849 x_row_to_process_flag => l_any_rows_to_process,
1850 x_return_status => x_return_status );
1851
1852 l_table_name := 'cn_process_batches_all';
1853
1854 delete_table ( p_start_period_id => p_start_period_id,
1855 p_end_period_id => p_end_period_id,
1856 x_start_date => x_start_date,
1857 x_end_date => x_end_date,
1858 p_worker_id => p_worker_id,
1859 p_no_of_workers => p_no_of_workers,
1860 p_batch_size => p_batch_size,
1861 p_table_owner => l_table_owner,
1862 p_table_name => upper(l_table_name),
1863 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1864 p_addnl_para => '',
1865 x_row_to_process_flag => l_any_rows_to_process,
1866 x_return_status => x_return_status );
1867
1868 l_table_name := 'cn_srp_intel_periods_all';
1869
1870 delete_table ( p_start_period_id => p_start_period_id,
1871 p_end_period_id => p_end_period_id,
1872 x_start_date => x_start_date,
1873 x_end_date => x_end_date,
1874 p_worker_id => p_worker_id,
1875 p_no_of_workers => p_no_of_workers,
1876 p_batch_size => p_batch_size,
1877 p_table_owner => l_table_owner,
1878 p_table_name => upper(l_table_name),
1879 p_script_name => g_script_name || '_' || p_cn_archive_all_s,
1880 p_addnl_para => '',
1881 x_row_to_process_flag => l_any_rows_to_process,
1882 x_return_status => x_return_status );
1883
1884 x_return_status := 'S';
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 x_return_status := 'F';
1888 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables_transactions:exception others: ' || SQLERRM(SQLCODE()) );
1889 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
1890 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
1891 RAISE FND_API.G_EXC_ERROR;
1892 END purge_cn_tables_processing;
1893
1894 -- API name : purge_cn_tables_processing
1895 -- Type : private.
1896 -- Pre-reqs :
1897 PROCEDURE audit_purge_cn_processing
1898 (
1899 p_start_period_id IN NUMBER,
1900 p_end_period_id IN NUMBER,
1901 x_start_date IN DATE,
1902 x_end_date IN DATE,
1903 p_cn_archive_all_s IN NUMBER,
1904 p_tot_rows_count IN OUT nocopy NUMBER,
1905 p_worker_id IN NUMBER,
1906 p_no_of_workers IN NUMBER,
1907 p_batch_size IN NUMBER,
1908 x_msg_count IN OUT nocopy NUMBER,
1909 x_msg_data IN OUT nocopy VARCHAR2,
1910 x_return_status OUT nocopy VARCHAR2 )
1911 IS
1912 l_row_count NUMBER;
1913 l_table_name VARCHAR2(30);
1914 l_table_owner VARCHAR2(30);
1915 l_product VARCHAR2(30) := 'CN';
1916 l_status VARCHAR2(30);
1917 l_industry VARCHAR2(30);
1918 l_retstatus BOOLEAN;
1919 l_any_rows_to_process varchar2(1);
1920 BEGIN
1921 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_processing: start ' );
1922
1923 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner);
1924
1925 IF ( ( l_retstatus = FALSE ) OR ( l_table_owner IS NULL ) ) THEN
1926 debugmsg('Cannot get schema name for product :'||l_product);
1927 RAISE_APPLICATION_ERROR(-20001,'Cannot get schema name for product :'||l_product);
1928 END IF;
1929
1930 x_return_status := 'S';
1931 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_processing:1 x_msg_data ' || x_msg_data);
1932
1933 l_table_name := 'cn_process_audit_lines_all';
1934 SELECT COUNT(*) INTO l_row_count FROM cn_process_audit_lines_all;
1935
1936 x_msg_data := x_msg_data || 'cn_process_audit_lines_all count ' || l_row_count || ' : ';
1937
1938 x_msg_count := x_msg_count + 1;
1939 p_tot_rows_count := p_tot_rows_count + l_row_count;
1940 insert_archive(cn_process_audit_lines_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1941 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_processing:2 x_msg_data ' || x_msg_data);
1942
1943 l_table_name := 'cn_process_audits_all';
1944 SELECT COUNT(*) INTO l_row_count FROM cn_process_audits_all;
1945
1946 x_msg_data := x_msg_data || 'cn_process_audits_all count ' || l_row_count || ' : ';
1947
1948 x_msg_count := x_msg_count + 1;
1949 p_tot_rows_count := p_tot_rows_count + l_row_count;
1950 insert_archive(cn_process_audits_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1951 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_processing:3 x_msg_data ' || x_msg_data);
1952
1953 l_table_name := 'cn_process_batches_all';
1954 SELECT COUNT(*)
1955 INTO l_row_count
1956 FROM cn_process_batches_all
1957 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
1958
1959 x_msg_data := x_msg_data || 'cn_process_batches_all count ' || l_row_count || ' : ';
1960
1961 x_msg_count := x_msg_count + 1;
1962 p_tot_rows_count := p_tot_rows_count + l_row_count;
1963 insert_archive(cn_process_batches_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1964 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_processing:4 x_msg_data ' || x_msg_data);
1965
1966 l_table_name := 'cn_srp_intel_periods_all';
1967 SELECT COUNT(*)
1968 INTO l_row_count
1969 FROM cn_srp_intel_periods_all
1970 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
1971
1972 x_msg_data := x_msg_data || 'cn_srp_intel_periods_all count ' || l_row_count || ' : ';
1973
1974 x_msg_count := x_msg_count + 1;
1975 p_tot_rows_count := p_tot_rows_count + l_row_count;
1976 insert_archive(cn_srp_intel_periods_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
1977 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_processing:5 x_msg_data ' || x_msg_data);
1978 x_return_status := 'S';
1979 EXCEPTION
1980 WHEN OTHERS THEN
1981 x_return_status := 'F';
1982 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_processing:exception others: ' || SQLERRM(SQLCODE()) );
1983 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
1984 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
1985 RAISE FND_API.G_EXC_ERROR;
1986 END audit_purge_cn_processing;
1987
1988
1989 -- API name : archive_cn_tables_transactions
1990 -- Type : private.
1991 -- Pre-reqs :
1992 PROCEDURE archive_cn_tables_transactions
1993 (
1994 p_start_period_id IN NUMBER,
1995 p_end_period_id IN NUMBER,
1996 x_start_date IN DATE,
1997 x_end_date IN DATE,
1998 p_table_space IN VARCHAR2,
1999 p_cn_archive_all_s IN NUMBER,
2000 p_tot_rows_count IN OUT nocopy NUMBER,
2001 x_msg_count IN OUT nocopy NUMBER,
2002 x_msg_data IN OUT nocopy VARCHAR2,
2003 x_return_status OUT nocopy VARCHAR2 )
2004 IS
2005 CURSOR c_overlaping_header_records
2006 IS
2007 SELECT DISTINCT h.imp_header_id
2008 FROM cn_imp_headers h
2009 WHERE exists (select distinct l.imp_header_id from cn_imp_lines l where h.imp_header_id = l.imp_header_id
2010 and to_date(l.col3, 'dd-mm-rr') between x_start_date AND x_end_date
2011 and h.imp_header_id not in (select distinct l2.imp_header_id from cn_imp_lines l2 where
2012 to_date(l2.col3, 'dd-mm-rr') not between x_start_date AND x_end_date
2013 ));
2014
2015 l_imp_header_id number := 0;
2016 l_imp_header_id_list varchar2(2000) := '';
2017 l_imp_header_id_count NUMBER := 0;
2018
2019 l_sql VARCHAR2(1500);
2020 l_row_count NUMBER;
2021 l_table_name VARCHAR2(30);
2022 l_any_rows_to_process varchar2(1);
2023 BEGIN
2024 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions: start ');
2025 x_return_status := 'S';
2026 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:1 x_msg_data ' || x_msg_data);
2027 l_any_rows_to_process := 'N';
2028 l_table_name := 'CN_PAYMENT_API' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2029 l_sql := 'Create table ' || l_table_name ;
2030 if(p_table_space is not null) Then
2031 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2032 end if;
2033 l_sql := l_sql || ' as select * from CN_PAYMENT_API_ALL where period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2034 EXECUTE immediate l_sql;
2035
2036 SELECT COUNT(*)
2037 INTO l_row_count
2038 FROM cn_payment_api_all
2039 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
2040 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2041 x_msg_count := x_msg_count + 1;
2042 p_tot_rows_count := p_tot_rows_count + l_row_count;
2043 insert_archive(cn_payment_api_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2044
2045 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:2 x_msg_data ' || x_msg_data);
2046 l_table_name := 'cn_post_details' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2047 l_sql := 'Create table ' || l_table_name ;
2048 --l_sql := 'Create table cn_posting_details_arc ';
2049 if(p_table_space is not null) Then
2050 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2051 end if;
2052 l_sql := l_sql || ' as select * from cn_posting_details_all where pay_period_id between ' || p_start_period_id || ' and ' || p_end_period_id || ' and paid_flag = ''Y''';
2053 EXECUTE immediate l_sql;
2054
2055 SELECT COUNT(*)
2056 INTO l_row_count
2057 FROM cn_posting_details_all
2058 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id;
2059 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2060 x_msg_count := x_msg_count + 1;
2061 p_tot_rows_count := p_tot_rows_count + l_row_count;
2062 insert_archive(cn_posting_details_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2063
2064 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:3 x_msg_data ' || x_msg_data);
2065 l_table_name := 'cn_payment_tran' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2066 l_sql := 'Create table ' || l_table_name ;
2067 if(p_table_space is not null) Then
2068 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2069 end if;
2070 l_sql := l_sql || ' as select * from cn_payment_transactions_all where pay_period_id between ' || p_start_period_id || ' and ' || p_end_period_id || ' and paid_flag = ''Y''';
2071 EXECUTE immediate l_sql;
2072
2073 SELECT COUNT(*)
2074 INTO l_row_count
2075 FROM cn_payment_transactions_all
2076 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id
2077 AND paid_flag = 'Y';
2078 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2079 x_msg_count := x_msg_count + 1;
2080 p_tot_rows_count := p_tot_rows_count + l_row_count;
2081 insert_archive(cn_payment_transactions_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2082
2083 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:4 x_msg_data ' || x_msg_data);
2084 l_table_name := 'cn_commsn_lines' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2085 l_sql := 'Create table ' || l_table_name ;
2086 if(p_table_space is not null) Then
2087 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2088 end if;
2089 l_sql := l_sql || ' as select * from cn_commission_lines_all where processed_period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2090 EXECUTE immediate l_sql;
2091
2092 SELECT COUNT(*)
2093 INTO l_row_count
2094 FROM cn_commission_lines_all
2095 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id;
2096 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2097 x_msg_count := x_msg_count + 1;
2098 p_tot_rows_count := p_tot_rows_count + l_row_count;
2099 insert_archive(cn_commission_lines_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2100
2101 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:5 x_msg_data ' || x_msg_data);
2102 l_table_name := 'cn_comsn_header' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2103 l_sql := 'Create table ' || l_table_name ;
2104 if(p_table_space is not null) Then
2105 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2106 end if;
2107 l_sql := l_sql || ' as select * from cn_commission_headers_all where processed_period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2108 EXECUTE immediate l_sql;
2109
2110 SELECT COUNT(*)
2111 INTO l_row_count
2112 FROM cn_commission_headers_all
2113 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id;
2114 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2115 x_msg_count := x_msg_count + 1;
2116 p_tot_rows_count := p_tot_rows_count + l_row_count;
2117 insert_archive(cn_commission_headers_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2118
2119 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:6 x_msg_data ' || x_msg_data);
2120 l_table_name := 'cn_trx_sale_lin' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2121 l_sql := 'Create table ' || l_table_name ;
2122 if(p_table_space is not null) Then
2123 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2124 end if;
2125 l_sql := l_sql || ' as select * from cn_trx_sales_lines_all where TRUNC(processed_date) between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''';
2126 EXECUTE immediate l_sql;
2127
2128 SELECT COUNT(*)
2129 INTO l_row_count
2130 FROM cn_trx_sales_lines_all
2131 WHERE TRUNC(processed_date) BETWEEN x_start_date AND x_end_date;
2132 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2133 x_msg_count := x_msg_count + 1;
2134 p_tot_rows_count := p_tot_rows_count + l_row_count;
2135 insert_archive(cn_trx_sales_lines_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2136
2137 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:6.5 x_msg_data ' || x_msg_data);
2138 l_table_name := 'cn_trx_lines' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2139 l_sql := 'Create table ' || l_table_name ;
2140 if(p_table_space is not null) Then
2141 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2142 end if;
2143 l_sql := l_sql || ' as select * from cn_trx_lines_all tl where tl.TRX_ID in (Select distinct t.TRX_ID from cn_trx_all t where TRUNC(t.processed_date) between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''' || ' ) ';
2144 EXECUTE immediate l_sql;
2145
2146 SELECT COUNT(*)
2147 INTO l_row_count
2148 FROM cn_trx_lines_all tl
2149 WHERE tl.TRX_ID IN
2150 (SELECT DISTINCT t.TRX_ID
2151 FROM cn_trx_all t
2152 WHERE TRUNC(t.processed_date) BETWEEN x_start_date AND x_end_date
2153 );
2154 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2155 x_msg_count := x_msg_count + 1;
2156 p_tot_rows_count := p_tot_rows_count + l_row_count;
2157 insert_archive(cn_trx_lines_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2158
2159 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:7 x_msg_data ' || x_msg_data);
2160 l_table_name := 'cn_trx' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2161 l_sql := 'Create table ' || l_table_name ;
2162 if(p_table_space is not null) Then
2163 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2164 end if;
2165 l_sql := l_sql || ' as select * from cn_trx_all where TRUNC(processed_date) between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''';
2166 EXECUTE immediate l_sql;
2167
2168 SELECT COUNT(*)
2169 INTO l_row_count
2170 FROM cn_trx_all
2171 WHERE TRUNC(processed_date) BETWEEN x_start_date AND x_end_date;
2172 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2173 x_msg_count := x_msg_count + 1;
2174 p_tot_rows_count := p_tot_rows_count + l_row_count;
2175 insert_archive(cn_trx_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2176
2177 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:8 x_msg_data ' || x_msg_data);
2178 l_table_name := 'cn_not_trx' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2179 l_sql := 'Create table ' || l_table_name ;
2180 if(p_table_space is not null) Then
2181 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2182 end if;
2183 l_sql := l_sql || ' as select * from cn_not_trx_all where processed_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''';
2184 EXECUTE immediate l_sql;
2185
2186 SELECT COUNT(*)
2187 INTO l_row_count
2188 FROM cn_not_trx_all
2189 WHERE processed_date BETWEEN x_start_date AND x_end_date;
2190 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2191 x_msg_count := x_msg_count + 1;
2192 p_tot_rows_count := p_tot_rows_count + l_row_count;
2193 insert_archive(cn_not_trx_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2194
2195 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:9 x_msg_data ' || x_msg_data);
2196 l_table_name := 'cn_invoice_chng' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2197 l_sql := 'Create table ' || l_table_name ;
2198 if(p_table_space is not null) Then
2199 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2200 end if;
2201 l_sql := l_sql || ' as select * from cn_invoice_changes_all i where i.comm_lines_api_id in ' ||
2202 ' (select distinct c.comm_lines_api_id from cn_comm_lines_api_all c where c.processed_period_id between ' ||
2203 p_start_period_id || ' and ' || p_end_period_id || ' ) ';
2204 EXECUTE immediate l_sql;
2205
2206 SELECT COUNT(*)
2207 INTO l_row_count
2208 FROM cn_invoice_changes_all i
2209 WHERE i.comm_lines_api_id IN
2210 (SELECT DISTINCT c.comm_lines_api_id
2211 FROM cn_comm_lines_api_all c
2212 WHERE c.processed_period_id BETWEEN p_start_period_id AND p_end_period_id );
2213 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2214 x_msg_count := x_msg_count + 1;
2215 p_tot_rows_count := p_tot_rows_count + l_row_count;
2216 insert_archive(cn_invoice_changes_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2217
2218 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:10 x_msg_data ' || x_msg_data);
2219
2220 BEGIN
2221
2222 OPEN c_overlaping_header_records;
2223 LOOP
2224 FETCH c_overlaping_header_records INTO l_imp_header_id;
2225 EXIT WHEN c_overlaping_header_records%NOTFOUND;
2226 if(l_imp_header_id_count = 0) THEN
2227 l_imp_header_id_list := l_imp_header_id_list || l_imp_header_id;
2228 else
2229 l_imp_header_id_list := l_imp_header_id_list || l_imp_header_id || ',';
2230 end if;
2231 l_imp_header_id_count := l_imp_header_id_count + 1;
2232
2233 END LOOP;
2234 CLOSE c_overlaping_header_records;
2235
2236 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:10.2 l_imp_header_id_count ' || l_imp_header_id_count);
2237
2238
2239
2240 if(l_imp_header_id_count > 1 ) Then
2241 l_imp_header_id_list := '(' || l_imp_header_id_list || '-999' || ')';
2242 elsif(l_imp_header_id_count = 0) Then
2243 l_imp_header_id_list := '(-999)';
2244 else
2245 l_imp_header_id_list := '(' || l_imp_header_id_list || ')';
2246 end if;
2247
2248 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:10.4 l_imp_header_id_list ' || l_imp_header_id_list);
2249 l_table_name := 'cn_imp_lines' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2250 l_sql := 'Create table ' || l_table_name ;
2251 if(p_table_space is not null) Then
2252 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2253 end if;
2254 l_sql := l_sql || ' as select * from cn_imp_lines il where il.import_type_code = ''TRXAPI'' and il.imp_header_id in '
2255 || l_imp_header_id_list;
2256
2257 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:10.6 l_sql for cn_imp_lines : ' || l_sql);
2258
2259 EXECUTE immediate l_sql;
2260 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:10.7');
2261 l_sql := 'SELECT COUNT(*) FROM cn_imp_lines WHERE import_type_code = ''TRXAPI'' AND imp_header_id in ' || l_imp_header_id_list;
2262 EXECUTE immediate l_sql INTO l_row_count;
2263 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2264 x_msg_count := x_msg_count + 1;
2265 p_tot_rows_count := p_tot_rows_count + l_row_count;
2266 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:10.9 x_msg_data ' || x_msg_data);
2267
2268 insert_archive(cn_imp_lines_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2269
2270 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:11 x_msg_data ' || x_msg_data);
2271
2272 l_table_name := 'cn_imp_headers' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2273 l_sql := 'Create table ' || l_table_name ;
2274 if(p_table_space is not null) Then
2275 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2276 end if;
2277 l_sql := l_sql || ' as select * from cn_imp_headers ih where ih.imp_header_id in '
2278 || l_imp_header_id_list;
2279
2280 EXECUTE immediate l_sql;
2281
2282 l_sql := 'SELECT COUNT(*) FROM cn_imp_headers ih WHERE ih.imp_header_id IN ' || l_imp_header_id_list;
2283
2284 EXECUTE IMMEDIATE l_sql INTO l_row_count;
2285 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2286 x_msg_count := x_msg_count + 1;
2287 p_tot_rows_count := p_tot_rows_count + l_row_count;
2288 insert_archive(cn_imp_headers_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2289
2290 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:12 x_msg_data ' || x_msg_data);
2291
2292
2293 EXCEPTION
2294 WHEN OTHERS THEN
2295 -- x_return_status := 'F';
2296 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions Error in cn_imp_lines (possible reason may be col3 date format issue) - ' || sqlerrm);
2297 END;
2298
2299 l_table_name := 'cn_comm_lin_api' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2300 l_sql := 'Create table ' || l_table_name ;
2301 if(p_table_space is not null) Then
2302 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2303 end if;
2304 l_sql := l_sql || ' as select * from cn_comm_lines_api_all where processed_period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2305 EXECUTE immediate l_sql;
2306
2307 SELECT COUNT(*)
2308 INTO l_row_count
2309 FROM cn_comm_lines_api_all
2310 WHERE processed_period_id BETWEEN p_start_period_id AND p_end_period_id;
2311 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2312 x_msg_count := x_msg_count + 1;
2313 p_tot_rows_count := p_tot_rows_count + l_row_count;
2314 insert_archive(cn_comm_lines_api_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2315 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:13 x_msg_data ' || x_msg_data);
2316 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:14 x_return_status ' || x_return_status);
2317 EXCEPTION
2318 WHEN OTHERS THEN
2319 x_return_status := 'F';
2320 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_transactions:exception others: ' || SQLERRM(SQLCODE()) );
2321 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
2322 --DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
2323 RAISE FND_API.G_EXC_ERROR;
2324 END archive_cn_tables_transactions;
2325
2326 -- API name : archive_cn_tables_subledgers
2327 -- Type : private.
2328 -- Pre-reqs :
2329 PROCEDURE archive_cn_tables_subledgers
2330 (
2331 p_start_period_id IN NUMBER,
2332 p_end_period_id IN NUMBER,
2333 x_start_date IN DATE,
2334 x_end_date IN DATE,
2335 p_table_space IN VARCHAR2,
2336 p_cn_archive_all_s IN NUMBER,
2337 p_tot_rows_count IN OUT nocopy NUMBER,
2338 x_msg_count IN OUT nocopy NUMBER,
2339 x_msg_data IN OUT nocopy VARCHAR2,
2340 x_return_status OUT nocopy VARCHAR2 )
2341 IS
2342 l_sql VARCHAR2(1500);
2343 l_row_count NUMBER;
2344 l_table_name VARCHAR2(30);
2345 l_any_rows_to_process varchar2(1);
2346
2347 BEGIN
2348 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers: start ');
2349 x_return_status := 'S';
2350 l_any_rows_to_process := 'N';
2351
2352 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:1 x_msg_data ' || x_msg_data);
2353 l_table_name := 'cn_srp_prd_qt_e' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2354 l_sql := 'Create table ' || l_table_name ;
2355 if(p_table_space is not null) Then
2356 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2357 end if;
2358 l_sql := l_sql || ' as select * from cn_srp_period_quotas_ext_all qe where ' ||
2359 ' qe.srp_period_quota_id in (Select distinct qa.srp_period_quota_id from cn_srp_period_quotas_all qa where qa.period_id between ' ||
2360 p_start_period_id || ' and ' || p_end_period_id || ' ) ';
2361 EXECUTE immediate l_sql;
2362
2363 SELECT COUNT(*)
2364 INTO l_row_count
2365 FROM cn_srp_period_quotas_ext_all qe
2366 WHERE qe.srp_period_quota_id IN
2367 (SELECT DISTINCT qa.srp_period_quota_id
2368 FROM cn_srp_period_quotas_all qa
2369 WHERE qa.period_id BETWEEN p_start_period_id AND p_end_period_id
2370 );
2371 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2372 x_msg_count := x_msg_count + 1;
2373 p_tot_rows_count := p_tot_rows_count + l_row_count;
2374 insert_archive(cn_srp_period_quotas_ext_all_i,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2375 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:2 x_msg_data ' || x_msg_data);
2376 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:2 x_return_status ' || x_return_status);
2377
2378 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:2 x_msg_data ' || x_msg_data);
2379 l_table_name := 'cn_srp_pe_qt_rc' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2380 l_sql := 'Create table ' || l_table_name ;
2381 if(p_table_space is not null) Then
2382 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2383 end if;
2384 l_sql := l_sql || ' as select * from cn_srp_per_quota_rc_all where period_id between ' || p_start_period_id || ' and ' || p_end_period_id ;
2385 EXECUTE immediate l_sql;
2386
2387 SELECT COUNT(*)
2388 INTO l_row_count
2389 FROM cn_srp_per_quota_rc_all
2390 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
2391 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2392 x_msg_count := x_msg_count + 1;
2393 p_tot_rows_count := p_tot_rows_count + l_row_count;
2394 insert_archive(cn_srp_per_quota_rc_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2395 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:3 x_msg_data ' || x_msg_data);
2396 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:3 x_return_status ' || x_return_status);
2397
2398 l_table_name := 'cn_srp_prd_quta' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2399 l_sql := 'Create table ' || l_table_name ;
2400 if(p_table_space is not null) Then
2401 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2402 end if;
2403 l_sql := l_sql || ' as select * from cn_srp_period_quotas_all where period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2404 EXECUTE immediate l_sql;
2405
2406 SELECT COUNT(*)
2407 INTO l_row_count
2408 FROM cn_srp_period_quotas_all
2409 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
2410 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2411 x_msg_count := x_msg_count + 1;
2412 p_tot_rows_count := p_tot_rows_count + l_row_count;
2413 insert_archive(cn_srp_period_quotas_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2414 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:4 x_msg_data ' || x_msg_data);
2415 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:4 x_return_status ' || x_return_status);
2416
2417 l_table_name := 'cn_pay_aprv_flw' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2418 l_sql := 'Create table ' || l_table_name ;
2419 if(p_table_space is not null) Then
2420 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2421 end if;
2422 l_sql := l_sql || ' as select * from cn_pay_approval_flow_all af where af.payrun_id in (Select distinct pa.payrun_id from cn_payruns_all pa where pa.PAY_PERIOD_ID between ' || p_start_period_id || ' and ' || p_end_period_id || ' ) ';
2423 EXECUTE immediate l_sql;
2424
2425 SELECT COUNT(*)
2426 INTO l_row_count
2427 FROM cn_pay_approval_flow_all af
2428 WHERE af.payrun_id IN
2429 (SELECT DISTINCT pa.payrun_id
2430 FROM cn_payruns_all pa
2431 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
2432 );
2433 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2434 x_msg_count := x_msg_count + 1;
2435 p_tot_rows_count := p_tot_rows_count + l_row_count;
2436 insert_archive(cn_pay_approval_flow_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2437 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:5 x_msg_data ' || x_msg_data);
2438 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:5 x_return_status ' || x_return_status);
2439
2440 l_table_name := 'cn_wksht_qg_dtl' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2441 l_sql := 'Create table ' || l_table_name ;
2442 if(p_table_space is not null) Then
2443 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2444 end if;
2445 l_sql := l_sql || ' as select * from cn_worksheet_qg_dtls_all where period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2446 EXECUTE immediate l_sql;
2447
2448 SELECT COUNT(*)
2449 INTO l_row_count
2450 FROM cn_worksheet_qg_dtls_all
2451 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
2452 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2453 x_msg_count := x_msg_count + 1;
2454 p_tot_rows_count := p_tot_rows_count + l_row_count;
2455 insert_archive(cn_worksheet_qg_dtls_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2456 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:6 x_msg_data ' || x_msg_data);
2457 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:6 x_return_status ' || x_return_status);
2458
2459 l_table_name := 'cn_pymnt_wkshts' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2460 l_sql := 'Create table ' || l_table_name ;
2461 if(p_table_space is not null) Then
2462 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2463 end if;
2464 l_sql := l_sql || ' as select * from cn_payment_worksheets_all pw where pw.payrun_id in (Select distinct pa.payrun_id from cn_payruns_all pa where pa.PAY_PERIOD_ID between ' || p_start_period_id || ' and ' || p_end_period_id || ' ) ';
2465 EXECUTE immediate l_sql;
2466
2467 SELECT COUNT(*)
2468 INTO l_row_count
2469 FROM cn_payment_worksheets_all pw
2470 WHERE pw.payrun_id IN
2471 (SELECT DISTINCT pa.payrun_id
2472 FROM cn_payruns_all pa
2473 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
2474 );
2475 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2476 x_msg_count := x_msg_count + 1;
2477 p_tot_rows_count := p_tot_rows_count + l_row_count;
2478 insert_archive(cn_payment_worksheets_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2479 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:7 x_msg_data ' || x_msg_data);
2480 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:7 x_return_status ' || x_return_status);
2481
2482 l_table_name := 'cn_ldgr_jrnl_en' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2483 l_sql := 'Create table ' || l_table_name ;
2484 if(p_table_space is not null) Then
2485 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2486 end if;
2487 l_sql := l_sql || ' as select * from cn_ledger_journal_entries_all je where je.srp_period_id in (Select distinct sp.srp_period_id from cn_srp_periods_all sp where sp.period_id between ' || p_start_period_id || ' and ' || p_end_period_id || ' ) ';
2488 EXECUTE immediate l_sql;
2489
2490 SELECT COUNT(*)
2491 INTO l_row_count
2492 FROM cn_ledger_journal_entries_all je
2493 WHERE je.srp_period_id IN
2494 (SELECT DISTINCT sp.srp_period_id
2495 FROM cn_srp_periods_all sp
2496 WHERE sp.period_id BETWEEN p_start_period_id AND p_end_period_id
2497 );
2498 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2499 x_msg_count := x_msg_count + 1;
2500 p_tot_rows_count := p_tot_rows_count + l_row_count;
2501 insert_archive(cn_ledger_journal_entries_alli,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2502 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:8 x_msg_data ' || x_msg_data);
2503 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:8 x_return_status ' || x_return_status);
2504
2505 l_table_name := 'cn_post_dtl_sum' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2506 l_sql := 'Create table ' || l_table_name ;
2507 if(p_table_space is not null) Then
2508 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2509 end if;
2510 l_sql := l_sql || ' as select * from cn_posting_details_sum_all where pay_period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2511 EXECUTE immediate l_sql;
2512
2513 SELECT COUNT(*)
2514 INTO l_row_count
2515 FROM cn_posting_details_sum_all
2516 WHERE pay_period_id BETWEEN p_start_period_id AND p_end_period_id;
2517 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2518 x_msg_count := x_msg_count + 1;
2519 p_tot_rows_count := p_tot_rows_count + l_row_count;
2520 insert_archive(cn_posting_details_sum_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2521 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:9 x_msg_data ' || x_msg_data);
2522 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:9 x_return_status ' || x_return_status);
2523
2524 l_table_name := 'cn_wksht_bonuse' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2525 l_sql := 'Create table ' || l_table_name ;
2526 if(p_table_space is not null) Then
2527 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2528 end if;
2529 l_sql := l_sql || ' as select * from cn_worksheet_bonuses_all wb where wb.payrun_id in (Select distinct pa.payrun_id from cn_payruns_all pa where pa.PAY_PERIOD_ID between ' || p_start_period_id || ' and ' || p_end_period_id || ' ) ';
2530 EXECUTE immediate l_sql;
2531
2532 SELECT COUNT(*)
2533 INTO l_row_count
2534 FROM cn_worksheet_bonuses_all wb
2535 WHERE wb.payrun_id IN
2536 (SELECT DISTINCT pa.payrun_id
2537 FROM cn_payruns_all pa
2538 WHERE pa.PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id
2539 );
2540 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2541 x_msg_count := x_msg_count + 1;
2542 p_tot_rows_count := p_tot_rows_count + l_row_count;
2543 insert_archive(cn_worksheet_bonuses_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2544 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:10 x_msg_data ' || x_msg_data);
2545 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:10 x_return_status ' || x_return_status);
2546
2547 l_table_name := 'cn_payruns' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2548 l_sql := 'Create table ' || l_table_name ;
2549 if(p_table_space is not null) Then
2550 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2551 end if;
2552 l_sql := l_sql || ' as select * from cn_payruns_all where PAY_PERIOD_ID between ' || p_start_period_id || ' and ' || p_end_period_id;
2553 EXECUTE immediate l_sql;
2554
2555 SELECT COUNT(*)
2556 INTO l_row_count
2557 FROM cn_payruns_all
2558 WHERE PAY_PERIOD_ID BETWEEN p_start_period_id AND p_end_period_id;
2559 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2560 x_msg_count := x_msg_count + 1;
2561 p_tot_rows_count := p_tot_rows_count + l_row_count;
2562 insert_archive(cn_payruns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2563 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:11 x_msg_data ' || x_msg_data);
2564 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:11 x_return_status ' || x_return_status);
2565
2566 l_table_name := 'cn_srp_periods' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2567 l_sql := 'Create table ' || l_table_name ;
2568 if(p_table_space is not null) Then
2569 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2570 end if;
2571 l_sql := l_sql || ' as select * from cn_srp_periods_all where period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2572 EXECUTE immediate l_sql;
2573
2574 SELECT COUNT(*)
2575 INTO l_row_count
2576 FROM cn_srp_periods_all
2577 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
2578 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2579 x_msg_count := x_msg_count + 1;
2580 p_tot_rows_count := p_tot_rows_count + l_row_count;
2581 insert_archive(cn_srp_periods_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2582 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:12 x_msg_data ' || x_msg_data);
2583 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:12 x_return_status ' || x_return_status);
2584
2585 EXCEPTION
2586 WHEN OTHERS THEN
2587 x_return_status := 'F';
2588 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_subledgers:exception others: ' || SQLERRM(SQLCODE()) );
2589 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
2590 --DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
2591 RAISE FND_API.G_EXC_ERROR;
2592 END archive_cn_tables_subledgers;
2593
2594 -- API name : archive_cn_tables_references
2595 -- Type : private.
2596 -- Pre-reqs :
2597 PROCEDURE archive_cn_tables_references
2598 (
2599 p_start_period_id IN NUMBER,
2600 p_end_period_id IN NUMBER,
2601 x_start_date IN DATE,
2602 x_end_date IN DATE,
2603 p_table_space IN VARCHAR2,
2604 p_cn_archive_all_s IN NUMBER,
2605 p_tot_rows_count IN OUT nocopy NUMBER,
2606 x_msg_count IN OUT nocopy NUMBER,
2607 x_msg_data IN OUT nocopy VARCHAR2,
2608 x_return_status OUT nocopy VARCHAR2 )
2609 IS
2610 l_sql VARCHAR2(1500);
2611 l_row_count NUMBER;
2612 l_table_name VARCHAR2(30);
2613 l_any_rows_to_process varchar2(1);
2614
2615 BEGIN
2616 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references: start ');
2617 x_return_status := 'S';
2618 l_any_rows_to_process := 'N';
2619
2620 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:1 x_msg_data ' || x_msg_data);
2621 l_table_name := 'cn_srp_paye_asn' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2622 l_sql := 'Create table ' || l_table_name ;
2623 if(p_table_space is not null) Then
2624 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2625 end if;
2626 l_sql := l_sql || ' as select * from cn_srp_payee_assigns_all pa where pa.srp_quota_assign_id in (Select distinct qa.srp_quota_assign_id from cn_srp_period_quotas_all qa where qa.srp_plan_assign_id in (Select distinct pl.srp_plan_assign_id
2627 from cn_srp_plan_assigns_all pl where ((pl.start_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||''''
2628 || ' ) and (pl.end_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''' || '))))';
2629 EXECUTE immediate l_sql;
2630
2631 SELECT COUNT(*)
2632 INTO l_row_count
2633 FROM cn_srp_payee_assigns_all pa
2634 WHERE pa.srp_quota_assign_id IN
2635 (SELECT DISTINCT qa.srp_quota_assign_id
2636 FROM cn_srp_period_quotas_all qa
2637 WHERE qa.srp_plan_assign_id IN
2638 (SELECT DISTINCT pl.srp_plan_assign_id
2639 FROM cn_srp_plan_assigns_all pl
2640 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
2641 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
2642 )
2643 );
2644 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2645 x_msg_count := x_msg_count + 1;
2646 p_tot_rows_count := p_tot_rows_count + l_row_count;
2647 insert_archive(cn_srp_payee_assigns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2648 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:2 x_msg_data ' || x_msg_data);
2649 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:2 x_return_status ' || x_return_status);
2650
2651 l_table_name := 'cn_srp_quta_asn' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2652 l_sql := 'Create table ' || l_table_name ;
2653 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:2 l_sql ' || l_sql);
2654 if(p_table_space is not null) Then
2655 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2656 end if;
2657 l_sql := l_sql || ' as select * from cn_srp_quota_assigns_all qa where qa.srp_plan_assign_id in (Select distinct pl.srp_plan_assign_id from cn_srp_plan_assigns_all pl
2658 where ((pl.start_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||''''
2659 || ' ) and (pl.end_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''' || ')))';
2660 EXECUTE immediate l_sql;
2661
2662 SELECT COUNT(*)
2663 INTO l_row_count
2664 FROM cn_srp_quota_assigns_all qa
2665 WHERE qa.srp_plan_assign_id IN
2666 (SELECT DISTINCT pl.srp_plan_assign_id
2667 FROM cn_srp_plan_assigns_all pl
2668 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
2669 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
2670 );
2671 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2672 x_msg_count := x_msg_count + 1;
2673 p_tot_rows_count := p_tot_rows_count + l_row_count;
2674 insert_archive(cn_srp_quota_assigns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2675 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:2 x_msg_data ' || x_msg_data);
2676 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:2 x_return_status ' || x_return_status);
2677
2678 l_table_name := 'cn_srp_rate_asn' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2679 l_sql := 'Create table ' || l_table_name ;
2680 if(p_table_space is not null) Then
2681 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2682 end if;
2683 l_sql := l_sql || ' as select * from cn_srp_rate_assigns_all ra where ra.srp_plan_assign_id in (Select distinct pl.srp_plan_assign_id from cn_srp_plan_assigns_all pl
2684 where ((pl.start_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||''''
2685 || ' ) and pl.end_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''' || '))';
2686 EXECUTE immediate l_sql;
2687
2688 SELECT COUNT(*)
2689 INTO l_row_count
2690 FROM cn_srp_rate_assigns_all ra
2691 WHERE ra.srp_plan_assign_id IN
2692 (SELECT DISTINCT pl.srp_plan_assign_id
2693 FROM cn_srp_plan_assigns_all pl
2694 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
2695 AND pl.end_date BETWEEN x_start_date AND x_end_date)
2696 );
2697 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2698 x_msg_count := x_msg_count + 1;
2699 p_tot_rows_count := p_tot_rows_count + l_row_count;
2700 insert_archive(cn_srp_rate_assigns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2701 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:3 x_msg_data ' || x_msg_data);
2702 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:3 x_return_status ' || x_return_status);
2703
2704 l_table_name := 'cn_srp_rl_uplft' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2705 l_sql := 'Create table ' || l_table_name ;
2706 if(p_table_space is not null) Then
2707 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2708 end if;
2709 l_sql := l_sql || ' as select * from cn_srp_rule_uplifts_all ru ' ||
2710 ' where ru.srp_quota_rule_id in (Select distinct qr.srp_quota_rule_id from cn_srp_quota_rules_all qr ' ||
2711 ' where qr.srp_plan_assign_id in (Select distinct pl.srp_plan_assign_id from cn_srp_plan_assigns_all pl ' ||
2712 ' where ((pl.start_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||''''
2713 || ' ) and (pl.end_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''' || '))))';
2714 EXECUTE immediate l_sql;
2715
2716 SELECT COUNT(*)
2717 INTO l_row_count
2718 FROM cn_srp_rule_uplifts_all ru
2719 WHERE ru.srp_quota_rule_id IN
2720 (SELECT DISTINCT qr.srp_quota_rule_id
2721 FROM cn_srp_quota_rules_all qr
2722 WHERE qr.srp_plan_assign_id IN
2723 (SELECT DISTINCT pl.srp_plan_assign_id
2724 FROM cn_srp_plan_assigns_all pl
2725 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
2726 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
2727 )
2728 );
2729 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2730 x_msg_count := x_msg_count + 1;
2731 p_tot_rows_count := p_tot_rows_count + l_row_count;
2732 insert_archive(cn_srp_rule_uplifts_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2733 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:4 x_msg_data ' || x_msg_data);
2734 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:4 x_return_status ' || x_return_status);
2735
2736 l_table_name := 'cn_srp_quota_rl' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2737 l_sql := 'Create table ' || l_table_name ;
2738 if(p_table_space is not null) Then
2739 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2740 end if;
2741 l_sql := l_sql || ' as select * from cn_srp_quota_rules_all ra where ra.srp_plan_assign_id in (Select distinct pl.srp_plan_assign_id from cn_srp_plan_assigns_all pl
2742 where ((pl.start_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||''''
2743 || ' ) and (pl.end_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''' || ')))';
2744 EXECUTE immediate l_sql;
2745
2746 SELECT COUNT(*)
2747 INTO l_row_count
2748 FROM cn_srp_quota_rules_all ra
2749 WHERE ra.srp_plan_assign_id IN
2750 (SELECT DISTINCT pl.srp_plan_assign_id
2751 FROM cn_srp_plan_assigns_all pl
2752 WHERE ((pl.start_date BETWEEN x_start_date AND x_end_date)
2753 AND (pl.end_date BETWEEN x_start_date AND x_end_date))
2754 );
2755 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2756 x_msg_count := x_msg_count + 1;
2757 p_tot_rows_count := p_tot_rows_count + l_row_count;
2758 insert_archive(cn_srp_quota_rules_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2759 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:5 x_msg_data ' || x_msg_data);
2760 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:5 x_return_status ' || x_return_status);
2761
2762 l_table_name := 'cn_srp_plan_asn' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2763 l_sql := 'Create table ' || l_table_name ;
2764 if(p_table_space is not null) Then
2765 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2766 end if;
2767 l_sql := l_sql || ' as select * from cn_srp_plan_assigns_all where ((start_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''' || ') and (end_date between ''' || x_start_date ||'''' || ' and ''' || x_end_date ||'''' || ' ))';
2768 EXECUTE immediate l_sql;
2769
2770 SELECT COUNT(*)
2771 INTO l_row_count
2772 FROM cn_srp_plan_assigns_all
2773 WHERE ((start_date BETWEEN x_start_date AND x_end_date)
2774 AND (end_date BETWEEN x_start_date AND x_end_date));
2775 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2776 x_msg_count := x_msg_count + 1;
2777 p_tot_rows_count := p_tot_rows_count + l_row_count;
2778 insert_archive(cn_srp_plan_assigns_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2779 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:6 x_msg_data ' || x_msg_data);
2780 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:6 x_return_status ' || x_return_status);
2781
2782 EXCEPTION
2783 WHEN OTHERS THEN
2784 x_return_status := 'F';
2785 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_references:exception others: ' || SQLERRM(SQLCODE()) );
2786 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
2787 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
2788 RAISE FND_API.G_EXC_ERROR;
2789 END archive_cn_tables_references;
2790
2791 -- API name : archive_cn_tables_processing
2792 -- Type : private.
2793 -- Pre-reqs :
2794 PROCEDURE archive_cn_tables_processing
2795 (
2796 p_start_period_id IN NUMBER,
2797 p_end_period_id IN NUMBER,
2798 x_start_date IN DATE,
2799 x_end_date IN DATE,
2800 p_table_space IN VARCHAR2,
2801 p_cn_archive_all_s IN NUMBER,
2802 p_tot_rows_count IN OUT nocopy NUMBER,
2803 x_msg_count IN OUT nocopy NUMBER,
2804 x_msg_data IN OUT nocopy VARCHAR2,
2805 x_return_status OUT nocopy VARCHAR2 )
2806 IS
2807 l_sql VARCHAR2(1500);
2808 l_row_count NUMBER;
2809 l_table_name VARCHAR2(30);
2810 l_any_rows_to_process varchar2(1);
2811
2812 BEGIN
2813 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_processing: start ');
2814 x_return_status := 'S';
2815 l_any_rows_to_process := 'N';
2816
2817 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_processing:1 x_msg_data ' || x_msg_data);
2818 l_table_name := 'cn_proces_batch' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2819 l_sql := 'Create table ' || l_table_name ;
2820 if(p_table_space is not null) Then
2821 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2822 end if;
2823 l_sql := l_sql || ' as select * from cn_process_batches_all where period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2824 EXECUTE immediate l_sql;
2825
2826 SELECT COUNT(*)
2827 INTO l_row_count
2828 FROM cn_process_batches_all
2829 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
2830 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2831 x_msg_count := x_msg_count + 1;
2832 p_tot_rows_count := p_tot_rows_count + l_row_count;
2833 insert_archive(cn_process_batches_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2834 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_processing:2 x_msg_data ' || x_msg_data);
2835 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_processing:2 x_return_status ' || x_return_status);
2836
2837 l_table_name := 'cn_srp_intl_prd' || '_' || to_char(sysdate, 'DDMMYYYYHH24MI');
2838 l_sql := 'Create table ' || l_table_name ;
2839 if(p_table_space is not null) Then
2840 l_sql := l_sql || ' TABLESPACE "' || p_table_space || '"';
2841 end if;
2842 l_sql := l_sql || ' as select * from cn_srp_intel_periods_all where period_id between ' || p_start_period_id || ' and ' || p_end_period_id;
2843 EXECUTE immediate l_sql;
2844
2845 SELECT COUNT(*)
2846 INTO l_row_count
2847 FROM cn_srp_intel_periods_all
2848 WHERE period_id BETWEEN p_start_period_id AND p_end_period_id;
2849 x_msg_data := x_msg_data || l_table_name || ' count ' || l_row_count || ' : ';
2850 x_msg_count := x_msg_count + 1;
2851 p_tot_rows_count := p_tot_rows_count + l_row_count;
2852 insert_archive(cn_srp_intel_periods_all_id,p_cn_archive_all_s,upper(l_table_name),l_row_count,l_any_rows_to_process);
2853 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_processing:3 x_msg_data ' || x_msg_data);
2854 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_processing:3 x_return_status ' || x_return_status);
2855
2856 EXCEPTION
2857 WHEN OTHERS THEN
2858 x_return_status := 'F';
2859 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables_processing:exception others: ' || SQLERRM(SQLCODE()) );
2860 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
2861 --DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
2862 RAISE FND_API.G_EXC_ERROR;
2863 END archive_cn_tables_processing;
2864
2865 -- API name : archive_cn_tables
2866 -- Type : private.
2867 -- Pre-reqs :
2868 PROCEDURE archive_cn_tables
2869 (
2870 p_run_mode IN VARCHAR2,
2871 p_start_period_id IN NUMBER,
2872 p_end_period_id IN NUMBER,
2873 x_start_date IN DATE,
2874 x_end_date IN DATE,
2875 p_table_space IN VARCHAR2,
2876 p_org_id IN NUMBER,
2877 x_msg_count IN OUT nocopy NUMBER,
2878 x_msg_data IN OUT nocopy VARCHAR2,
2879 x_return_status OUT nocopy VARCHAR2 )
2880 IS
2881 l_cn_archive_all_s NUMBER;
2882 l_tot_rows_count NUMBER;
2883 l_run_mode varchar2(15);
2884
2885 BEGIN
2886 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables: start ');
2887 l_tot_rows_count := 0;
2888 x_msg_count := 0;
2889
2890 if(p_run_mode = 'A') Then
2891 l_run_mode := 'ARCHIVE';
2892 elsif (p_run_mode = 'P') Then
2893 l_run_mode := 'PURGE';
2894 end if;
2895
2896 SELECT CN_ARC_AUDIT_ALL_S.nextval INTO l_cn_archive_all_s FROM dual;
2897 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: l_cn_archive_all_s : ' || l_cn_archive_all_s);
2898 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables before archive_cn_tables_transactions ');
2899
2900 archive_cn_tables_transactions ( p_start_period_id => p_start_period_id,
2901 p_end_period_id => p_end_period_id,
2902 x_start_date => x_start_date,
2903 x_end_date => x_end_date,
2904 p_table_space => p_table_space,
2905 p_cn_archive_all_s => l_cn_archive_all_s,
2906 p_tot_rows_count => l_tot_rows_count,
2907 x_msg_count => x_msg_count,
2908 x_msg_data => x_msg_data,
2909 x_return_status => x_return_status );
2910 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables after archive_cn_tables_transactions x_return_status ' || x_return_status);
2911 IF(x_return_status <> 'S') THEN
2912 ROLLBACK;
2913 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2914 END IF;
2915
2916 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables before archive_cn_tables_references ');
2917 archive_cn_tables_references ( p_start_period_id => p_start_period_id,
2918 p_end_period_id => p_end_period_id,
2919 x_start_date => x_start_date,
2920 x_end_date => x_end_date,
2921 p_table_space => p_table_space,
2922 p_cn_archive_all_s => l_cn_archive_all_s,
2923 p_tot_rows_count => l_tot_rows_count,
2924 x_msg_count => x_msg_count,
2925 x_msg_data => x_msg_data,
2926 x_return_status => x_return_status );
2927 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables after archive_cn_tables_references x_return_status ' || x_return_status);
2928 IF(x_return_status <> 'S') THEN
2929 ROLLBACK;
2930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2931 END IF;
2932
2933 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables before archive_cn_tables_subledgers ');
2934 archive_cn_tables_subledgers ( p_start_period_id => p_start_period_id,
2935 p_end_period_id => p_end_period_id,
2936 x_start_date => x_start_date,
2937 x_end_date => x_end_date,
2938 p_table_space => p_table_space,
2939 p_cn_archive_all_s => l_cn_archive_all_s,
2940 p_tot_rows_count => l_tot_rows_count,
2941 x_msg_count => x_msg_count,
2942 x_msg_data => x_msg_data,
2943 x_return_status => x_return_status );
2944 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables after archive_cn_tables_subledgers x_return_status ' || x_return_status);
2945 IF(x_return_status <> 'S') THEN
2946 ROLLBACK;
2947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2948 END IF;
2949
2950 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables before archive_cn_tables_processing ');
2951 archive_cn_tables_processing ( p_start_period_id => p_start_period_id,
2952 p_end_period_id => p_end_period_id,
2953 x_start_date => x_start_date,
2954 x_end_date => x_end_date,
2955 p_table_space => p_table_space,
2956 p_cn_archive_all_s => l_cn_archive_all_s,
2957 p_tot_rows_count => l_tot_rows_count,
2958 x_msg_count => x_msg_count,
2959 x_msg_data => x_msg_data,
2960 x_return_status => x_return_status );
2961 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables after archive_cn_tables_processing x_return_status ' || x_return_status);
2962 IF(x_return_status <> 'S') THEN
2963 ROLLBACK;
2964 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2965 END IF;
2966
2967
2968 INSERT
2969 INTO cn_arc_audit_all
2970 (ARCHIVE_PURGE_ID,TOT_AP_TABLES_COUNT,TOT_AP_ROWS,ARCHIVE_PURGE_DATE,START_PERIOD_ID,END_PERIOD_ID,ORG_ID,RUN_MODE,
2971 ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2972 VALUES
2973 (
2974 l_cn_archive_all_s,
2975 x_msg_count ,
2976 l_tot_rows_count ,
2977 sysdate ,
2978 p_start_period_id ,
2979 p_end_period_id ,
2980 p_org_id ,
2981 l_run_mode ,
2982 NULL ,
2983 NULL ,
2984 NULL ,
2985 NULL ,
2986 NULL ,
2987 fnd_global.user_id,
2988 sysdate ,
2989 fnd_global.user_id,
2990 sysdate ,
2991 fnd_global.user_id
2992 );
2993 COMMIT;
2994 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables: end : ');
2995
2996 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables x_return_status ' || x_return_status);
2997 EXCEPTION
2998 WHEN OTHERS THEN
2999 ROLLBACK;
3000 x_return_status := 'F';
3001 debugmsg('CN_PURGE_TABLES_PVT.archive_cn_tables:exception others: ' || SQLERRM(SQLCODE()) );
3002 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
3003 --DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
3004 RAISE FND_API.G_EXC_ERROR;
3005 END archive_cn_tables;
3006
3007 -- API name : purge_cn_tables
3008 -- Type : private.
3009 -- Pre-reqs :
3010 PROCEDURE purge_cn_tables
3011 (
3012 p_run_mode IN VARCHAR2,
3013 p_start_period_id IN NUMBER,
3014 p_end_period_id IN NUMBER,
3015 x_start_date IN DATE,
3016 x_end_date IN DATE,
3017 p_org_id IN NUMBER,
3018 p_worker_id IN NUMBER,
3019 p_no_of_workers IN NUMBER,
3020 p_batch_size IN NUMBER,
3021 x_msg_count OUT nocopy NUMBER,
3022 x_msg_data OUT nocopy VARCHAR2,
3023 x_return_status OUT nocopy VARCHAR2 )
3024 IS
3025 l_tot_rows_count NUMBER;
3026 l_cn_archive_all_s NUMBER;
3027 l_run_mode varchar2(15);
3028 BEGIN
3029 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: start: ');
3030 l_tot_rows_count := 0;
3031 x_msg_count := 0;
3032
3033 if(p_run_mode = 'A') Then
3034 l_run_mode := 'ARCHIVE';
3035 elsif (p_run_mode = 'P') Then
3036 l_run_mode := 'PURGE';
3037 end if;
3038
3039 SELECT CN_ARC_AUDIT_ALL_S.nextval INTO l_cn_archive_all_s FROM dual;
3040
3041 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: l_cn_archive_all_s : ' || l_cn_archive_all_s);
3042
3043
3044 purge_cn_tables_transactions ( p_start_period_id => p_start_period_id,
3045 p_end_period_id => p_end_period_id,
3046 x_start_date => x_start_date,
3047 x_end_date => x_end_date,
3048 p_cn_archive_all_s => l_cn_archive_all_s,
3049 p_tot_rows_count => l_tot_rows_count,
3050 p_worker_id => p_worker_id,
3051 p_no_of_workers => p_no_of_workers,
3052 p_batch_size => p_batch_size,
3053 x_msg_count => x_msg_count,
3054 x_msg_data => x_msg_data,
3055 x_return_status => x_return_status);
3056 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: after purge_cn_tables_transactions x_return_status : ' || x_return_status);
3057 IF(x_return_status <> 'S') THEN
3058 ROLLBACK;
3059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3060 END IF;
3061
3062 purge_cn_tables_refrences ( p_start_period_id => p_start_period_id,
3063 p_end_period_id => p_end_period_id,
3064 x_start_date => x_start_date,
3065 x_end_date => x_end_date,
3066 p_cn_archive_all_s => l_cn_archive_all_s,
3067 p_tot_rows_count => l_tot_rows_count,
3068 p_worker_id => p_worker_id,
3069 p_no_of_workers => p_no_of_workers,
3070 p_batch_size => p_batch_size,
3071 x_msg_count => x_msg_count,
3072 x_msg_data => x_msg_data,
3073 x_return_status => x_return_status);
3074 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: after purge_cn_tables_refrences x_return_status : ' || x_return_status);
3075 IF(x_return_status <> 'S') THEN
3076 ROLLBACK;
3077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3078 END IF;
3079
3080 purge_cn_tables_subledgers ( p_start_period_id => p_start_period_id,
3081 p_end_period_id => p_end_period_id,
3082 x_start_date => x_start_date,
3083 x_end_date => x_end_date,
3084 p_cn_archive_all_s => l_cn_archive_all_s,
3085 p_tot_rows_count => l_tot_rows_count,
3086 p_worker_id => p_worker_id,
3087 p_no_of_workers => p_no_of_workers,
3088 p_batch_size => p_batch_size,
3089 x_msg_count => x_msg_count,
3090 x_msg_data => x_msg_data,
3091 x_return_status => x_return_status);
3092 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: after purge_cn_tables_subledgers x_return_status : ' || x_return_status);
3093 IF(x_return_status <> 'S') THEN
3094 ROLLBACK;
3095 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3096 END IF;
3097
3098 purge_cn_tables_processing ( p_start_period_id => p_start_period_id,
3099 p_end_period_id => p_end_period_id,
3100 x_start_date => x_start_date,
3101 x_end_date => x_end_date,
3102 p_cn_archive_all_s => l_cn_archive_all_s,
3103 p_tot_rows_count => l_tot_rows_count,
3104 p_worker_id => p_worker_id,
3105 p_no_of_workers => p_no_of_workers,
3106 p_batch_size => p_batch_size,
3107 x_msg_count => x_msg_count,
3108 x_msg_data => x_msg_data,
3109 x_return_status => x_return_status);
3110 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: after purge_cn_tables_refrences x_return_status : ' || x_return_status);
3111 IF(x_return_status <> 'S') THEN
3112 ROLLBACK;
3113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3114 END IF;
3115
3116 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: x_return_status : ' || x_return_status);
3117
3118
3119 COMMIT;
3120 debugmsg('CN_PURGE_TABLES_PVT.purge_cn_tables: end : ');
3121
3122 EXCEPTION
3123 WHEN OTHERS THEN
3124 ROLLBACK;
3125 x_return_status := 'F';
3126 debugmsg
3127 (
3128 'CN_PURGE_TABLES_PVT.purge_cn_tables:exception others: ' || SQLERRM(SQLCODE())
3129 )
3130 ;
3131 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
3132 RAISE FND_API.G_EXC_ERROR;
3133 --DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
3134 END purge_cn_tables;
3135
3136 -- API name : purge_cn_tables
3137 -- Type : private.
3138 -- Pre-reqs :
3139 PROCEDURE audit_purge_cn_tables
3140 (
3141 p_run_mode IN VARCHAR2,
3142 p_start_period_id IN NUMBER,
3143 p_end_period_id IN NUMBER,
3144 p_org_id IN NUMBER,
3145 p_worker_id IN NUMBER,
3146 p_no_of_workers IN NUMBER,
3147 p_batch_size IN NUMBER,
3148 x_msg_count OUT nocopy NUMBER,
3149 x_msg_data OUT nocopy VARCHAR2,
3150 x_return_status OUT nocopy VARCHAR2 )
3151 IS
3152 l_tot_rows_count NUMBER;
3153 l_cn_archive_all_s NUMBER;
3154 l_run_mode varchar2(15);
3155 x_start_date DATE;
3156 x_end_date DATE;
3157 BEGIN
3158 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: start: ');
3159 l_tot_rows_count := 0;
3160 x_msg_count := 0;
3161
3162 if(p_run_mode = 'A') Then
3163 l_run_mode := 'ARCHIVE';
3164 elsif (p_run_mode = 'P') Then
3165 l_run_mode := 'PURGE';
3166 end if;
3167
3168 cn_periods_api.set_dates(p_start_period_id, p_end_period_id, p_org_id, x_start_date, x_end_date);
3169
3170 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: x_start_date: ' || x_start_date);
3171 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: x_end_date: ' || x_end_date);
3172
3173 SELECT CN_ARC_AUDIT_ALL_S.nextval INTO l_cn_archive_all_s FROM dual;
3174
3175 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: l_cn_archive_all_s : ' || l_cn_archive_all_s);
3176
3177
3178 audit_purge_cn_transactions ( p_start_period_id => p_start_period_id,
3179 p_end_period_id => p_end_period_id,
3180 x_start_date => x_start_date,
3181 x_end_date => x_end_date,
3182 p_cn_archive_all_s => l_cn_archive_all_s,
3183 p_tot_rows_count => l_tot_rows_count,
3184 p_worker_id => p_worker_id,
3185 p_no_of_workers => p_no_of_workers,
3186 p_batch_size => p_batch_size,
3187 x_msg_count => x_msg_count,
3188 x_msg_data => x_msg_data,
3189 x_return_status => x_return_status);
3190 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: after purge_cn_tables_transactions x_return_status : ' || x_return_status);
3191 IF(x_return_status <> 'S') THEN
3192 ROLLBACK;
3193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3194 END IF;
3195
3196 audit_purge_cn_refrences ( p_start_period_id => p_start_period_id,
3197 p_end_period_id => p_end_period_id,
3198 x_start_date => x_start_date,
3199 x_end_date => x_end_date,
3200 p_cn_archive_all_s => l_cn_archive_all_s,
3201 p_tot_rows_count => l_tot_rows_count,
3202 p_worker_id => p_worker_id,
3203 p_no_of_workers => p_no_of_workers,
3204 p_batch_size => p_batch_size,
3205 x_msg_count => x_msg_count,
3206 x_msg_data => x_msg_data,
3207 x_return_status => x_return_status);
3208 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: after purge_cn_tables_refrences x_return_status : ' || x_return_status);
3209 IF(x_return_status <> 'S') THEN
3210 ROLLBACK;
3211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3212 END IF;
3213
3214 audit_purge_cn_subledgers ( p_start_period_id => p_start_period_id,
3215 p_end_period_id => p_end_period_id,
3216 x_start_date => x_start_date,
3217 x_end_date => x_end_date,
3218 p_cn_archive_all_s => l_cn_archive_all_s,
3219 p_tot_rows_count => l_tot_rows_count,
3220 p_worker_id => p_worker_id,
3221 p_no_of_workers => p_no_of_workers,
3222 p_batch_size => p_batch_size,
3223 x_msg_count => x_msg_count,
3224 x_msg_data => x_msg_data,
3225 x_return_status => x_return_status);
3226 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: after purge_cn_tables_refrences x_return_status : ' || x_return_status);
3227 IF(x_return_status <> 'S') THEN
3228 ROLLBACK;
3229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3230 END IF;
3231
3232 audit_purge_cn_processing ( p_start_period_id => p_start_period_id,
3233 p_end_period_id => p_end_period_id,
3234 x_start_date => x_start_date,
3235 x_end_date => x_end_date,
3236 p_cn_archive_all_s => l_cn_archive_all_s,
3237 p_tot_rows_count => l_tot_rows_count,
3238 p_worker_id => p_worker_id,
3239 p_no_of_workers => p_no_of_workers,
3240 p_batch_size => p_batch_size,
3241 x_msg_count => x_msg_count,
3242 x_msg_data => x_msg_data,
3243 x_return_status => x_return_status);
3244 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: after purge_cn_tables_refrences x_return_status : ' || x_return_status);
3245 IF(x_return_status <> 'S') THEN
3246 ROLLBACK;
3247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3248 END IF;
3249
3250 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: x_return_status : ' || x_return_status);
3251
3252
3253 INSERT
3254 INTO cn_arc_audit_all
3255 (ARCHIVE_PURGE_ID,TOT_AP_TABLES_COUNT,TOT_AP_ROWS,ARCHIVE_PURGE_DATE,START_PERIOD_ID,END_PERIOD_ID,ORG_ID,RUN_MODE,
3256 ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
3257 VALUES
3258 (
3259 l_cn_archive_all_s,
3260 x_msg_count ,
3261 l_tot_rows_count ,
3262 sysdate ,
3263 p_start_period_id ,
3264 p_end_period_id ,
3265 p_org_id ,
3266 l_run_mode ,
3267 NULL ,
3268 NULL ,
3269 NULL ,
3270 NULL ,
3271 NULL ,
3272 fnd_global.user_id,
3273 sysdate ,
3274 fnd_global.user_id,
3275 sysdate ,
3276 fnd_global.user_id
3277 );
3278 COMMIT;
3279 debugmsg('CN_PURGE_TABLES_PVT.audit_purge_cn_tables: end : ');
3280
3281 EXCEPTION
3282 WHEN OTHERS THEN
3283 ROLLBACK;
3284 x_return_status := 'F';
3285 debugmsg
3286 (
3287 'CN_PURGE_TABLES_PVT.audit_purge_cn_tables:exception others: ' || SQLERRM(SQLCODE())
3288 )
3289 ;
3290 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
3291 RAISE FND_API.G_EXC_ERROR;
3292 --DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
3293 END audit_purge_cn_tables;
3294
3295 -- API name : archive_purge_cn_tables
3296 -- Type : public.
3297 -- Pre-reqs :
3298 PROCEDURE archive_purge_cn_tables
3299 (
3300 errbuf OUT NOCOPY VARCHAR2,
3301 retcode OUT NOCOPY VARCHAR2,
3302 p_run_mode IN VARCHAR2,
3303 p_start_period_id IN NUMBER,
3304 p_end_period_id IN NUMBER,
3305 p_no_of_workers IN NUMBER,
3306 p_org_id IN NUMBER,
3307 p_table_space IN VARCHAR2,
3308 p_worker_id IN NUMBER,
3309 p_batch_size IN NUMBER,
3310 p_request_id IN NUMBER
3311 )
3312 IS
3313
3314 x_start_date DATE;
3315 x_end_date DATE;
3316 x_msg_count NUMBER;
3317 x_msg_data VARCHAR2(2000);
3318 x_return_status VARCHAR2(1);
3319 l_time VARCHAR2(20);
3320
3321 BEGIN
3322 x_msg_count := 0;
3323 x_msg_data := ':';
3324 x_return_status := 'S';
3325
3326 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: x_org_id: ' || p_org_id);
3327 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: x_start_period_name: ' || p_start_period_id);
3328 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: x_end_period_name: ' || p_end_period_id);
3329 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: p_run_mode: ' || p_run_mode);
3330 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: p_no_of_workers: ' || p_no_of_workers);
3331 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: p_worker_id: ' || p_worker_id);
3332 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: p_batch_size: ' || p_batch_size);
3333 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: p_table_space: ' || p_table_space);
3334 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: g_cn_debug: ' || g_cn_debug);
3335
3336
3337 cn_periods_api.set_dates(p_start_period_id, p_end_period_id, p_org_id, x_start_date, x_end_date);
3338
3339 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: x_start_date: ' || x_start_date);
3340 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: x_end_date: ' || x_end_date);
3341
3342 IF(p_run_mode = 'A') THEN
3343 archive_cn_tables ( p_run_mode => p_run_mode,
3344 p_start_period_id => p_start_period_id,
3345 p_end_period_id => p_end_period_id,
3346 x_start_date => x_start_date,
3347 x_end_date => x_end_date,
3348 p_table_space => p_table_space,
3349 p_org_id => p_org_id,
3350 x_msg_count => x_msg_count,
3351 x_msg_data => x_msg_data,
3352 x_return_status => x_return_status );
3353 elsif (p_run_mode = 'P' ) THEN
3354 purge_cn_tables ( p_run_mode => p_run_mode,
3355 p_start_period_id => p_start_period_id,
3356 p_end_period_id => p_end_period_id,
3357 x_start_date => x_start_date,
3358 x_end_date => x_end_date,
3359 p_org_id => p_org_id,
3360 p_worker_id => p_worker_id,
3361 p_no_of_workers => p_no_of_workers,
3362 p_batch_size => p_batch_size,
3363 x_msg_count => x_msg_count,
3364 x_msg_data => x_msg_data,
3365 x_return_status => x_return_status );
3366 end if;
3367 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: x_msg_count: ' || x_msg_count);
3368 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: x_msg_data: ' || x_msg_data);
3369 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables: x_return_status: ' || x_return_status);
3370
3371 IF(x_return_status <> 'S') THEN
3372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3373 END IF;
3374
3375 EXCEPTION
3376 WHEN OTHERS THEN
3377 ROLLBACK;
3378 x_return_status := 'F';
3379 debugmsg('CN_PURGE_TABLES_PVT.archive_purge_cn_tables:exception others: ' || SQLERRM(SQLCODE()) );
3380 x_msg_data := x_msg_data || ' : ' || SQLERRM(SQLCODE());
3381 retcode := 2;
3382 errbuf := 'Unexpected Error : ' || SQLERRM(SQLCODE());
3383 RAISE FND_API.G_EXC_ERROR;
3384 END archive_purge_cn_tables;
3385
3386 END CN_PURGE_TABLES_PVT;