[Home] [Help]
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';
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';
99: BEGIN
100: x_return_status := 'S';
101: debugmsg
102: (
103: 'CN_PURGE_TABLES_PVT.delete_table:Start '
104: )
105: ;
106: debugmsg
107: (
104: )
105: ;
106: debugmsg
107: (
108: 'CN_PURGE_TABLES_PVT.delete_table:p_table_name ' || p_table_name
109: )
110: ;
111: debugmsg
112: (
109: )
110: ;
111: debugmsg
112: (
113: 'CN_PURGE_TABLES_PVT.delete_table:p_table_owner ' || p_table_owner
114: )
115: ;
116: debugmsg
117: (
114: )
115: ;
116: debugmsg
117: (
118: 'CN_PURGE_TABLES_PVT.delete_table:p_script_name ' || p_script_name
119: )
120: ;
121: debugmsg
122: (
119: )
120: ;
121: debugmsg
122: (
123: 'CN_PURGE_TABLES_PVT.delete_table:p_worker_id ' || p_worker_id
124: )
125: ;
126: debugmsg
127: (
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: (
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:
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 ' );
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 ' );
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';
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 ' );
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)
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) */
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
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
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;
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;
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
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
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:
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: --
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
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,
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';
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;
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
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
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
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
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
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
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
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
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
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
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
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;
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;
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;
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
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';
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';
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;
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
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;
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
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
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
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
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
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
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
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
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
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
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
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
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';
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;
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
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;
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
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
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
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
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
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
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
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';
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;
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
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;
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
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:
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:
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
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
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';
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;
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');
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
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
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 || '"';
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 || '"';
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 || '"';
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 || '"';
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 || '"';
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 || '"';
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 || '"';
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 || '"';
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;
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
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;
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;
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);
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
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
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 ;
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';
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()) );
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;
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);
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 || '"';
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');
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 ;
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 || '"';
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 ;
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
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 ;
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
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 ;
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
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 ;
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
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 ;
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
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 ;
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
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 ;
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
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 ;
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
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 ;
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
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
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';
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;
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);
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 || '"';
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 ;
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);
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
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 ;
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
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 ;
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
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 ;
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
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 ;
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
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
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';
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;
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);
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 || '"';
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 ;
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
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
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';
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;
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
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,
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,
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;
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,
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;
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,
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;
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,
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;
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
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';
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;
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
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,
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;
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;
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;
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;
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 : ');
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;
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;
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
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:
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);
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,
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;
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;
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;
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;
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
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;
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;
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);
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);
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);
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);
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);
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:
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:
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);
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:
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,
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,
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
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;
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;
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;
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;