1: PACKAGE BODY jl_co_fa_purge_pkg AS
2: /* $Header: jlcoftpb.pls 120.7 2006/09/20 17:04:30 abuissa ship $ */
3:
4: /* ======================================================================*
5: | FND Logging infrastructure |
6: * ======================================================================*/
7: G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_CO_FA_PURGE_PKG';
8: G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9: G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
4: /* ======================================================================*
5: | FND Logging infrastructure |
6: * ======================================================================*/
7: G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_CO_FA_PURGE_PKG';
8: G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9: G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10: G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
5: | FND Logging infrastructure |
6: * ======================================================================*/
7: G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_CO_FA_PURGE_PKG';
8: G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9: G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10: G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13: G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6: * ======================================================================*/
7: G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_CO_FA_PURGE_PKG';
8: G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9: G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10: G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13: G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14: G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7: G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_CO_FA_PURGE_PKG';
8: G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9: G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10: G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13: G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14: G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15: G_MODULE_NAME CONSTANT VARCHAR2(80) := 'JL.PLSQL.JL_CO_FA_PURGE_PKG.';
8: G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9: G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10: G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13: G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14: G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15: G_MODULE_NAME CONSTANT VARCHAR2(80) := 'JL.PLSQL.JL_CO_FA_PURGE_PKG.';
16:
9: G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10: G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13: G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14: G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15: G_MODULE_NAME CONSTANT VARCHAR2(80) := 'JL.PLSQL.JL_CO_FA_PURGE_PKG.';
16:
17: TYPE varchar2s is table of VARCHAR2(256) INDEX BY BINARY_INTEGER;
10: G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11: G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12: G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13: G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14: G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15: G_MODULE_NAME CONSTANT VARCHAR2(80) := 'JL.PLSQL.JL_CO_FA_PURGE_PKG.';
16:
17: TYPE varchar2s is table of VARCHAR2(256) INDEX BY BINARY_INTEGER;
18: x_last_updated_by NUMBER(15);
101:
102: BEGIN
103:
104: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
105: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
106: END IF;
107:
108: fnd_message.set_name('JL', 'JL_CO_FA_PARAMETER');
109: fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
142: WHERE book_type_code = p_book;
143:
144: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
145: fnd_file.put_line( 1, 'Deprn calendar :'||x_deprn_calendar);
146: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn calendar :'||x_deprn_calendar);
147: fnd_file.put_line( 1, 'Initial period Counter :'||to_char(x_initial_period_counter));
148: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Initial period Counter :'||to_char(x_initial_period_counter));
149: fnd_file.put_line( 1, 'Current fiscal year :'||x_current_fiscal_year);
150: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Current fiscal year :'||x_current_fiscal_year);
144: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
145: fnd_file.put_line( 1, 'Deprn calendar :'||x_deprn_calendar);
146: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn calendar :'||x_deprn_calendar);
147: fnd_file.put_line( 1, 'Initial period Counter :'||to_char(x_initial_period_counter));
148: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Initial period Counter :'||to_char(x_initial_period_counter));
149: fnd_file.put_line( 1, 'Current fiscal year :'||x_current_fiscal_year);
150: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Current fiscal year :'||x_current_fiscal_year);
151: fnd_file.put_line( 1, 'Deprn Status :'||x_deprn_status);
152: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn Status :'||x_deprn_status);
146: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn calendar :'||x_deprn_calendar);
147: fnd_file.put_line( 1, 'Initial period Counter :'||to_char(x_initial_period_counter));
148: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Initial period Counter :'||to_char(x_initial_period_counter));
149: fnd_file.put_line( 1, 'Current fiscal year :'||x_current_fiscal_year);
150: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Current fiscal year :'||x_current_fiscal_year);
151: fnd_file.put_line( 1, 'Deprn Status :'||x_deprn_status);
152: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn Status :'||x_deprn_status);
153: fnd_file.put_line( 1, 'book class :'||x_book_class);
154: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'book class :'||x_book_class);
148: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Initial period Counter :'||to_char(x_initial_period_counter));
149: fnd_file.put_line( 1, 'Current fiscal year :'||x_current_fiscal_year);
150: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Current fiscal year :'||x_current_fiscal_year);
151: fnd_file.put_line( 1, 'Deprn Status :'||x_deprn_status);
152: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn Status :'||x_deprn_status);
153: fnd_file.put_line( 1, 'book class :'||x_book_class);
154: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'book class :'||x_book_class);
155:
156: END IF;
150: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Current fiscal year :'||x_current_fiscal_year);
151: fnd_file.put_line( 1, 'Deprn Status :'||x_deprn_status);
152: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deprn Status :'||x_deprn_status);
153: fnd_file.put_line( 1, 'book class :'||x_book_class);
154: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'book class :'||x_book_class);
155:
156: END IF;
157:
158:
177: AND period_counter = x_initial_period_counter;
178:
179: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
180: fnd_file.put_line( 1, 'Initial Fiscal year :'||x_initial_fiscal_year);
181: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Initial Fiscal year :'||x_initial_fiscal_year);
182: END IF;
183:
184: ---------------------------------------------------------
185: -- Show the error conditions and finish the procedure --
464: AND fiscal_year = p_fiscal_year;
465:
466: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
467: fnd_file.put_line( 1, 'Start period Counter :' || to_char(x_start_period_counter));
468: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Start period Counter :' || to_char(x_start_period_counter));
469: fnd_file.put_line( 1, 'Start end Counter :' || to_char(x_end_period_counter));
470: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Start end Counter :' || to_char(x_end_period_counter));
471: END IF;
472:
466: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
467: fnd_file.put_line( 1, 'Start period Counter :' || to_char(x_start_period_counter));
468: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Start period Counter :' || to_char(x_start_period_counter));
469: fnd_file.put_line( 1, 'Start end Counter :' || to_char(x_end_period_counter));
470: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Start end Counter :' || to_char(x_end_period_counter));
471: END IF;
472:
473:
474: ---------------------------------------------------------
504: AND period_counter_adjusted <= x_end_period_counter;
505:
506: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
507: fnd_file.put_line( 1, 'adjustment rows :' || to_char(x_adjustments_rows));
508: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'adjustment rows :' || to_char(x_adjustments_rows));
509: fnd_file.put_line( 1, 'adjustment amount :' || to_char(x_adjustments_amount));
510: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'adjustment amount :' || to_char(x_adjustments_amount));
511: END IF;
512:
506: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
507: fnd_file.put_line( 1, 'adjustment rows :' || to_char(x_adjustments_rows));
508: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'adjustment rows :' || to_char(x_adjustments_rows));
509: fnd_file.put_line( 1, 'adjustment amount :' || to_char(x_adjustments_amount));
510: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'adjustment amount :' || to_char(x_adjustments_amount));
511: END IF;
512:
513:
514:
545: x_string := 'drop table '||x_oracle_username||'.'||ltrim(rtrim(x_adj_table));
546:
547: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
548: fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
549: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQl statement to be processed :'||x_string);
550: END IF;
551:
552: IF NOT (do_sql(x_string)) THEN
553: RAISE UNABLE_TO_DO_SQL;
578: ' and period_counter_adjusted <= '||to_char(x_end_period_counter);
579:
580: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
581: fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
582: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQl statement to be processed :'||x_string);
583: END IF;
584:
585: IF NOT (do_sql(x_string)) THEN
586: RAISE UNABLE_TO_DO_SQL;
592: -- change the status in jl_co_fa_purge to 'ARCHIVE' --
593: ---------------------------------------------------------
594: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
595: fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
596: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
597: END IF;
598:
599:
600: UPDATE jl_co_fa_purge SET
608: WHERE purge_id = x_index;
609:
610: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
611: fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
612: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
613: END IF;
614:
615:
616: ELSE
642: -- change the status in jl_co_fa_purge to 'PURGED' --
643: ---------------------------------------------------------
644: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
645: fnd_file.put_line( 1, 'Deleting JL_CO_FA_ADJUSTMENTS');
646: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleting JL_CO_FA_ADJUSTMENTS');
647: END IF;
648:
649: DELETE FROM jl_co_fa_adjustments
650: WHERE book_type_code = p_book
652: AND period_counter_adjusted <= x_end_period_counter;
653:
654: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
655: fnd_file.put_line( 1, 'Deleted JL_CO_FA_ADJUSTMENTS');
656: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_ADJUSTMENTS');
657: END IF;
658:
659:
660: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
658:
659:
660: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
661: fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
662: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
663: END IF;
664:
665:
666: UPDATE jl_co_fa_purge
672: WHERE purge_id = x_index;
673:
674: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
675: fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
676: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
677: END IF;
678:
679: END IF;
680: END IF;
690:
691: x_string := 'insert into jl_co_fa_adjustments select * from '||x_oracle_username||'.'||ltrim(rtrim(x_adj_table));
692: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
693: fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
694: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQl statement to be processed :'||x_string);
695: END IF;
696:
697: IF NOT (do_sql(x_string)) THEN
698: RAISE UNABLE_TO_DO_SQL;
699: END IF;
700:
701: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
702: fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
703: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
704: END IF;
705:
706:
707: UPDATE jl_co_fa_purge
713: WHERE purge_id = x_index;
714:
715: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
716: fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
717: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
718: END IF;
719:
720: END IF;
721: commit;
720: END IF;
721: commit;
722:
723: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
724: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
725: END IF;
726:
727: EXCEPTION
728:
915:
916: BEGIN
917:
918: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
919: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
920: END IF;
921:
922: fnd_message.set_name('JL', 'JL_CO_FA_PARAMETER');
923: fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
940: ---------------------------------------------------------
941: x_del_unproc_app := nvl(p_del_unproc_app, 'N');
942: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
943: fnd_file.put_line( 1, 'Delete erroneous appraisals PARAM:'||p_del_unproc_app);
944: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Delete erroneous appraisals PARAM:'||p_del_unproc_app);
945: fnd_file.put_line( 1, 'Delete erroneous appraisals VAR :'||x_del_unproc_app);
946: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Delete erroneous appraisals VAR :'||x_del_unproc_app);
947: END IF;
948:
942: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
943: fnd_file.put_line( 1, 'Delete erroneous appraisals PARAM:'||p_del_unproc_app);
944: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Delete erroneous appraisals PARAM:'||p_del_unproc_app);
945: fnd_file.put_line( 1, 'Delete erroneous appraisals VAR :'||x_del_unproc_app);
946: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Delete erroneous appraisals VAR :'||x_del_unproc_app);
947: END IF;
948:
949: SELECT count(*)
950: INTO x_count
1157: IF x_count = 0 THEN
1158:
1159: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1160: fnd_file.put_line( 1, 'Inserting row into JL_CO_FA_PURGE');
1161: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserting row into JL_CO_FA_PURGE');
1162: END IF;
1163:
1164: INSERT INTO jl_co_fa_purge(
1165: purge_id,
1204: x_sysdate);
1205:
1206: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1207: fnd_file.put_line( 1, 'Inserted row into JL_CO_FA_PURGE');
1208: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserted row into JL_CO_FA_PURGE');
1209: END IF;
1210:
1211:
1212:
1228:
1229:
1230: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1231: fnd_file.put_line( 1, 'Backup table for JL_CO_FA_APPRAISALS :'||x_appr_table);
1232: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Backup table for JL_CO_FA_APPRAISALS :'||x_appr_table);
1233: fnd_file.put_line( 1, 'Backup table for JL_CO_FA_ASSET_APPRS :'||x_asset_table);
1234: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Backup table for JL_CO_FA_ASSET_APPRS :'||x_asset_table);
1235: END IF;
1236:
1230: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1231: fnd_file.put_line( 1, 'Backup table for JL_CO_FA_APPRAISALS :'||x_appr_table);
1232: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Backup table for JL_CO_FA_APPRAISALS :'||x_appr_table);
1233: fnd_file.put_line( 1, 'Backup table for JL_CO_FA_ASSET_APPRS :'||x_asset_table);
1234: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Backup table for JL_CO_FA_ASSET_APPRS :'||x_asset_table);
1235: END IF;
1236:
1237: ---------------------------------------------------------
1238: -- Get Oracle username --
1270: WHERE fiscal_year = p_fiscal_year;
1271:
1272: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1273: fnd_file.put_line( 1, 'No of appraisal rows :'||to_char(x_appraisal_rows));
1274: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of appraisal rows :'||to_char(x_appraisal_rows));
1275: fnd_file.put_line( 1, 'Appraisal Amount :'||to_char(x_appraisal_amount));
1276: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal Amount :'||to_char(x_appraisal_amount));
1277: END IF;
1278:
1272: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1273: fnd_file.put_line( 1, 'No of appraisal rows :'||to_char(x_appraisal_rows));
1274: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of appraisal rows :'||to_char(x_appraisal_rows));
1275: fnd_file.put_line( 1, 'Appraisal Amount :'||to_char(x_appraisal_amount));
1276: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal Amount :'||to_char(x_appraisal_amount));
1277: END IF;
1278:
1279:
1280: SELECT count(*),
1287: where fiscal_year = p_fiscal_year);
1288:
1289: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1290: fnd_file.put_line( 1, 'No of asset rows :'||to_char(x_asset_rows));
1291: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of asset rows :'||to_char(x_asset_rows));
1292: fnd_file.put_line( 1, 'Asset Amount :'||to_char(x_asset_amount));
1293: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Asset Amount :'||to_char(x_asset_amount));
1294: END IF;
1295:
1289: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1290: fnd_file.put_line( 1, 'No of asset rows :'||to_char(x_asset_rows));
1291: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of asset rows :'||to_char(x_asset_rows));
1292: fnd_file.put_line( 1, 'Asset Amount :'||to_char(x_asset_amount));
1293: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Asset Amount :'||to_char(x_asset_amount));
1294: END IF;
1295:
1296: SELECT count(*),
1297: SUM(NVL(appraisal_id, 0))
1303: where fiscal_year = p_fiscal_year);
1304:
1305: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1306: fnd_file.put_line( 1, 'No of appraisal_book rows :'||to_char(x_book_rows));
1307: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of appraisal_book rows :'||to_char(x_book_rows));
1308: fnd_file.put_line( 1, 'Appraisal_book Amount :'||to_char(x_book_amount));
1309: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal_book Amount :'||to_char(x_book_amount));
1310: END IF;
1311:
1305: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1306: fnd_file.put_line( 1, 'No of appraisal_book rows :'||to_char(x_book_rows));
1307: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'No of appraisal_book rows :'||to_char(x_book_rows));
1308: fnd_file.put_line( 1, 'Appraisal_book Amount :'||to_char(x_book_amount));
1309: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal_book Amount :'||to_char(x_book_amount));
1310: END IF;
1311:
1312:
1313:
1340: IF x_count <> 0 THEN
1341: x_string := 'drop table '||x_oracle_username||'.'||ltrim(rtrim(x_appr_table));
1342: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1343: fnd_file.put_line( 1, 'SQL statement to be processed :'||x_string);
1344: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1345: END IF;
1346:
1347: IF NOT (do_sql(x_string)) THEN
1348: RAISE UNABLE_TO_DO_SQL;
1368: IF x_count <> 0 THEN
1369: x_string := 'Drop table '||x_oracle_username||'.'||ltrim(rtrim(x_asset_table));
1370: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1371: fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1372: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1373: END IF;
1374:
1375: IF NOT (do_sql(x_string)) THEN
1376: RAISE UNABLE_TO_DO_SQL;
1397: IF x_count <> 0 THEN
1398: x_string := 'Drop table '||x_oracle_username||'.'||ltrim(rtrim(x_book_table));
1399: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1400: fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1401: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1402: END IF;
1403:
1404: IF NOT (do_sql(x_string)) THEN
1405: RAISE UNABLE_TO_DO_SQL;
1430: ' where fiscal_year = '||p_fiscal_year;
1431:
1432: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1433: fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1434: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1435: END IF;
1436:
1437: IF NOT (do_sql(x_string)) THEN
1438: RAISE UNABLE_TO_DO_SQL;
1454: ' where appraisal_id in (select appraisal_id from jl_co_fa_appraisals'||
1455: ' where fiscal_year = '||p_fiscal_year||')';
1456: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1457: fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1458: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1459: END IF;
1460:
1461: IF NOT (do_sql(x_string)) THEN
1462: RAISE UNABLE_TO_DO_SQL;
1478: ' where appraisal_id in (select appraisal_id from jl_co_fa_appraisals'||
1479: ' where fiscal_year='||p_fiscal_year||')';
1480: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1481: fnd_file.put_line( 1, 'SQl statement to be processed :'||x_string);
1482: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'SQL statement to be processed :'||x_string);
1483: END IF;
1484:
1485: IF NOT (do_sql(x_string)) THEN
1486: RAISE UNABLE_TO_DO_SQL;
1491: -- change the status in jl_co_fa_purge to 'ARCHIVE' --
1492: ---------------------------------------------------------
1493: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1494: fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
1495: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
1496: END IF;
1497:
1498:
1499: UPDATE jl_co_fa_purge SET
1511: WHERE purge_id = x_index;
1512:
1513: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1514: fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
1515: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
1516: END IF;
1517:
1518: ELSE
1519:
1549: ---------------------------------------------------------
1550:
1551: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1552: fnd_file.put_line( 1, 'Deleting JL_CO_FA_APPRAISALS');
1553: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleting JL_CO_FA_ASSET_APPRS');
1554: END IF;
1555:
1556: DELETE FROM jl_co_fa_asset_apprs
1557: WHERE appraisal_id IN (select appraisal_id
1559: where fiscal_year = p_fiscal_year);
1560:
1561: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1562: fnd_file.put_line( 1, 'Deleted JL_CO_FA_APPRAISALS');
1563: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_ASSET_APPRS');
1564: END IF;
1565:
1566: DELETE FROM jl_co_fa_appraisal_books
1567: WHERE appraisal_id IN (select appraisal_id
1570:
1571:
1572: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1573: fnd_file.put_line( 1, 'Deleting JL_CO_FA_Appraisals');
1574: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleting JL_CO_FA_Appraisals');
1575: END IF;
1576:
1577:
1578:
1580: WHERE fiscal_year = p_fiscal_year;
1581:
1582: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1583: fnd_file.put_line( 1, 'Deleted JL_CO_FA_Appraisals');
1584: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_Appraisals');
1585: END IF;
1586: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1587: fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
1588: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
1584: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_Appraisals');
1585: END IF;
1586: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1587: fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
1588: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
1589: END IF;
1590:
1591:
1592: UPDATE jl_co_fa_purge
1598: WHERE purge_id = x_index;
1599:
1600: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1601: fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
1602: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
1603: END IF;
1604:
1605: END IF;
1606: END IF;
1630: END IF;
1631:
1632: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1633: fnd_file.put_line( 1, 'Updating JL_CO_FA_PURGE');
1634: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_PURGE');
1635: END IF;
1636:
1637: UPDATE jl_co_fa_purge
1638: SET status = 'RESTORED',
1643: WHERE purge_id = x_index;
1644:
1645: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1646: fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
1647: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
1648: END IF;
1649:
1650: END IF;
1651:
1651:
1652: commit;
1653:
1654: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1655: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1656: END IF;
1657:
1658: EXCEPTION
1659:
1787:
1788: BEGIN
1789:
1790: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1791: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1792: END IF;
1793:
1794: x_last_updated_by := fnd_global.user_id;
1795: x_last_update_login := fnd_global.login_id;
1800:
1801:
1802: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1803: fnd_file.put_line( 1, 'last_update_login:'||to_char(x_last_update_login));
1804: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_update_login:'||to_char(x_last_update_login));
1805: fnd_file.put_line( 1, 'last_updated_by:'||to_char(x_last_updated_by));
1806: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_updated_by:'||to_char(x_last_updated_by));
1807: fnd_file.put_line( 1, 'last_request_id:'||to_char(x_request_id));
1808: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_request_id:'||to_char(x_request_id));
1802: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1803: fnd_file.put_line( 1, 'last_update_login:'||to_char(x_last_update_login));
1804: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_update_login:'||to_char(x_last_update_login));
1805: fnd_file.put_line( 1, 'last_updated_by:'||to_char(x_last_updated_by));
1806: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_updated_by:'||to_char(x_last_updated_by));
1807: fnd_file.put_line( 1, 'last_request_id:'||to_char(x_request_id));
1808: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_request_id:'||to_char(x_request_id));
1809: fnd_file.put_line( 1, 'x_program_application_id :'||to_char(x_program_application_id ));
1810: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_application_id :'||to_char(x_program_application_id ));
1804: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_update_login:'||to_char(x_last_update_login));
1805: fnd_file.put_line( 1, 'last_updated_by:'||to_char(x_last_updated_by));
1806: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_updated_by:'||to_char(x_last_updated_by));
1807: fnd_file.put_line( 1, 'last_request_id:'||to_char(x_request_id));
1808: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_request_id:'||to_char(x_request_id));
1809: fnd_file.put_line( 1, 'x_program_application_id :'||to_char(x_program_application_id ));
1810: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_application_id :'||to_char(x_program_application_id ));
1811: fnd_file.put_line( 1, 'x_program_id :'||to_char(x_program_id ));
1812: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_id :'||to_char(x_program_id ));
1806: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_updated_by:'||to_char(x_last_updated_by));
1807: fnd_file.put_line( 1, 'last_request_id:'||to_char(x_request_id));
1808: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_request_id:'||to_char(x_request_id));
1809: fnd_file.put_line( 1, 'x_program_application_id :'||to_char(x_program_application_id ));
1810: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_application_id :'||to_char(x_program_application_id ));
1811: fnd_file.put_line( 1, 'x_program_id :'||to_char(x_program_id ));
1812: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_id :'||to_char(x_program_id ));
1813: fnd_file.put_line( 1, 'x_sysdate :'||to_char(x_sysdate ));
1814: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_sysdate :'||to_char(x_sysdate ));
1808: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_request_id:'||to_char(x_request_id));
1809: fnd_file.put_line( 1, 'x_program_application_id :'||to_char(x_program_application_id ));
1810: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_application_id :'||to_char(x_program_application_id ));
1811: fnd_file.put_line( 1, 'x_program_id :'||to_char(x_program_id ));
1812: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_id :'||to_char(x_program_id ));
1813: fnd_file.put_line( 1, 'x_sysdate :'||to_char(x_sysdate ));
1814: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_sysdate :'||to_char(x_sysdate ));
1815:
1816: END IF;
1810: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_application_id :'||to_char(x_program_application_id ));
1811: fnd_file.put_line( 1, 'x_program_id :'||to_char(x_program_id ));
1812: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_id :'||to_char(x_program_id ));
1813: fnd_file.put_line( 1, 'x_sysdate :'||to_char(x_sysdate ));
1814: FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_sysdate :'||to_char(x_sysdate ));
1815:
1816: END IF;
1817:
1818: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1815:
1816: END IF;
1817:
1818: IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1819: FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1820: END IF;
1821:
1822: END find_who_columns;
1823: