[Home] [Help]
868: -- HISTORY: --
869: -- 08/21/98 Sujit Dalai Created --
870: -- 05/28/99 Sujit Dalai Changed to provide storage criteria --
871: -- for backup tables and purge table --
872: -- JL_CO_FA_APPRAISAL_BOOKS. Also changed --
873: -- give message if any of appraisal infor --
874: -- mation to be purged has not been --
875: -- revalued. --
876: ----------------------------------------------------------------------------
958: WHERE appraisal_id IN (select appraisal_id
959: from jl_co_fa_appraisals
960: where fiscal_year = p_fiscal_year
961: and appraisal_status <> 'P');
962: DELETE FROM jl_co_fa_appraisal_books
963: WHERE appraisal_id IN (select appraisal_id
964: from jl_co_fa_appraisals
965: where fiscal_year = p_fiscal_year
966: and appraisal_status <> 'P');
1223: AND fiscal_year = p_fiscal_year;
1224:
1225: x_appr_table := 'JL_CO_FA_APPRAISALS'||TO_CHAR(x_index);
1226: x_asset_table := 'JL_CO_FA_ASSET_APPRS'||TO_CHAR(x_index);
1227: x_book_table := 'JL_CO_FA_APPRAISAL_BOOKS'||TO_CHAR(x_index);
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);
1296: SELECT count(*),
1297: SUM(NVL(appraisal_id, 0))
1298: INTO x_book_rows,
1299: x_book_amount
1300: FROM jl_co_fa_appraisal_books
1301: WHERE appraisal_id IN (select appraisal_id
1302: from jl_co_fa_appraisals
1303: where fiscal_year = p_fiscal_year);
1304:
1379: END IF;
1380:
1381: ---------------------------------------------------------
1382: -- Check weather backup tables for books is exists. --
1383: -- Its name is jl_co_fa_appraisal_books with extension --
1384: -- purge_id --
1385: ---------------------------------------------------------
1386: SELECT count(*)
1387: INTO x_count
1461: IF NOT (do_sql(x_string)) THEN
1462: RAISE UNABLE_TO_DO_SQL;
1463: END IF;
1464:
1465: IF NOT (storage_factor('JL_CO_FA_APPRAISAL_BOOKS',
1466: x_book_rows,
1467: x_storage_factor)) THEN
1468:
1469: RAISE UNABLE_TO_DO_SQL;
1473: x_string := 'create table '||x_oracle_username||'.'||ltrim(rtrim(x_book_table))||
1474: ' STORAGE( INITIAL '||TO_CHAR(ceil(x_storage_factor))||'K '||
1475: 'NEXT '||TO_CHAR(ceil(x_storage_factor/2))||'K '||
1476: 'MINEXTENTS 1 MAXEXTENTS 20 PCTINCREASE 100) '||
1477: 'as select * from jl_co_fa_appraisal_books'||
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);
1543: ELSE
1544:
1545: ---------------------------------------------------------
1546: -- Delete the rows from jl_co_fa_adjustments, --
1547: -- jl_co_fa_appraisals and jl_co_fa_appraisal_books --
1548: -- and change the status in jl_co_fa_purge to 'PURGED' --
1549: ---------------------------------------------------------
1550:
1551: 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
1568: from jl_co_fa_appraisals
1569: where fiscal_year = p_fiscal_year);
1570:
1623: IF NOT (do_sql(x_string)) THEN
1624: RAISE UNABLE_TO_DO_SQL;
1625: END IF;
1626:
1627: x_string := 'insert into jl_co_fa_appraisal_books select * from '||x_oracle_username||'.'||ltrim(rtrim(x_book_table));
1628: IF NOT (do_sql(x_string)) THEN
1629: RAISE UNABLE_TO_DO_SQL;
1630: END IF;
1631: