[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
SELECT deprn_calendar,
initial_period_counter,
current_fiscal_year,
deprn_status,
book_class
INTO x_deprn_calendar,
x_initial_period_counter,
x_current_fiscal_year,
x_deprn_status,
x_book_class
FROM fa_book_controls
WHERE book_type_code = p_book;
SELECT fiscal_year
INTO x_initial_fiscal_year
FROM fa_deprn_periods
WHERE book_type_code = p_book
AND period_counter = x_initial_period_counter;
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code = p_book
AND fiscal_year = p_fiscal_year
AND status IN ( 'RESTORED', 'PURGED');
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code = p_book
AND fiscal_year = (p_fiscal_year -1)
AND status IN ('PURGED', 'ARCHIVED', 'RESTORE');
IF (p_option = 'DELETE') THEN
---------------------------------------------------------
-- Stop the program if row exists for the couple --
-- book-year in jl_co_fa_purge and their status is --
-- different from 'RESTORED' or 'ARCHAIVED' --
---------------------------------------------------------
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code = p_book
AND fiscal_year = p_fiscal_year
AND status IN ('ARCHIVED', 'RESTORED');
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code = p_book
AND fiscal_year = (p_fiscal_year -1)
AND status IN ('PURGED');
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code = p_book
AND fiscal_year = p_fiscal_year
AND status = 'PURGED';
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code = p_book
AND fiscal_year = (p_fiscal_year +1)
ANd status = 'PURGED';
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code = p_book
AND fiscal_year = p_fiscal_year;
INSERT INTO jl_co_fa_purge(
purge_id,
book_type_code,
fiscal_year,
status,
appraisals_rows_archived,
appraisals_check_sum,
asset_apprs_rows_archived,
asset_apprs_check_sum,
adjustments_rows_archived,
adjustments_check_sum,
appraisal_books_rows_archived,
appraisal_books_check_sum,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (jl_co_fa_purge_s.NEXTVAL,
p_book,
p_fiscal_year,
'NEW',
0,
0,
0,
0,
0,
0,
0,
0,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
SELECT purge_id
INTO x_index
FROM jl_co_fa_purge
WHERE book_type_code = p_book
AND fiscal_year = p_fiscal_year;
SELECT u.oracle_username
INTO x_oracle_username
FROM fnd_oracle_userid u,
fnd_product_installations p,
fnd_application a
WHERE a.application_short_name = 'JL'
AND p.application_id = a.application_id
AND p.oracle_id = u.oracle_id;
IF (p_option IN ('ARCHIVE', 'DELETE')) THEN
---------------------------------------------------------
-- Get the value of start period counter and end period--
-- counter for the fiscal year --
---------------------------------------------------------
/* SELECT number_per_fiscal_year
INTO x_period_number
FROM fa_calendar_types
WHERE calendar_type = x_deprn_calendar;
SELECT period_counter
INTO x_start_period_counter
FROM fa_deprn_periods
WHERE book_type_code = p_book
AND fiscal_year = p_fiscal_year
AND period_num = 1;
SELECT MIN(period_counter),
MAX(period_counter)
INTO x_start_period_counter,
x_end_period_counter
FROM fa_deprn_periods
WHERE book_type_code = p_book
AND fiscal_year = p_fiscal_year;
SELECT count(*)
INTO x_count
FROM jl_co_fa_adjustments
WHERE book_type_code = p_book
AND posting_flag <> 'C'
AND period_counter_created >= x_start_period_counter
AND period_counter_created <= x_end_period_counter;
SELECT count(*),
NVL(SUM(adjustment_amount), 0)
INTO x_adjustments_rows,
x_adjustments_amount
FROM jl_co_fa_adjustments
WHERE book_type_code = p_book
AND period_counter_adjusted >= x_start_period_counter
AND period_counter_adjusted <= x_end_period_counter;
SELECT count(*)
INTO x_count
FROM all_tables
WHERE table_name = x_adj_table
AND owner = x_oracle_username;
' as select * from jl_co_fa_adjustments'||
' where book_type_code ='||''''||p_book||''''||
' and period_counter_adjusted >= '||to_char(x_start_period_counter)||
' and period_counter_adjusted <= '||to_char(x_end_period_counter);
UPDATE jl_co_fa_purge SET
adjustments_rows_archived = x_adjustments_rows,
adjustments_check_sum = x_adjustments_amount,
status = 'ARCHIVED',
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
program_update_date = x_sysdate
WHERE purge_id = x_index;
fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
SELECT COUNT(*)
INTO x_count
FROM jl_co_fa_purge
WHERE purge_id = x_index
AND adjustments_rows_archived = x_adjustments_rows
AND adjustments_check_sum = x_adjustments_amount;
DELETE FROM jl_co_fa_adjustments
WHERE book_type_code = p_book
AND period_counter_adjusted >= x_start_period_counter
AND period_counter_adjusted <= x_end_period_counter;
fnd_file.put_line( 1, 'Deleted JL_CO_FA_ADJUSTMENTS');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_ADJUSTMENTS');
UPDATE jl_co_fa_purge
SET status = 'PURGED',
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
program_update_date = x_sysdate
WHERE purge_id = x_index;
fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
x_string := 'insert into jl_co_fa_adjustments select * from '||x_oracle_username||'.'||ltrim(rtrim(x_adj_table));
UPDATE jl_co_fa_purge
SET status = 'RESTORED',
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
program_update_date = x_sysdate
WHERE purge_id = x_index;
fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
fnd_file.put_line( 1, 'Delete erroneous appraisals PARAM:'||p_del_unproc_app);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Delete erroneous appraisals PARAM:'||p_del_unproc_app);
fnd_file.put_line( 1, 'Delete erroneous appraisals VAR :'||x_del_unproc_app);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Delete erroneous appraisals VAR :'||x_del_unproc_app);
SELECT count(*)
INTO x_count
FROM jl_co_fa_appraisals
WHERE fiscal_year = p_fiscal_year
AND appraisal_status <> 'P';
DELETE FROM jl_co_fa_asset_apprs
WHERE appraisal_id IN (select appraisal_id
from jl_co_fa_appraisals
where fiscal_year = p_fiscal_year
and appraisal_status <> 'P');
DELETE FROM jl_co_fa_appraisal_books
WHERE appraisal_id IN (select appraisal_id
from jl_co_fa_appraisals
where fiscal_year = p_fiscal_year
and appraisal_status <> 'P');
fnd_message.set_name('JL', 'JL_CO_FA_DELETED_APPRS');
DELETE FROM jl_co_fa_appraisals
WHERE fiscal_year = p_fiscal_year
AND appraisal_status <> 'P';
SELECT count(*)
INTO x_count
FROM jl_co_fa_appraisals
WHERE fiscal_year = p_fiscal_year;
SELECT count(*)
INTO x_counT
FROM jl_co_fa_purge
WHERE book_type_code IS NULL
AND fiscal_year = p_fiscal_year
AND status IN ( 'RESTORED', 'PURGED');
SELECT count(*)
INTO x_count
FROM jl_co_fa_appraisals
WHERE fiscal_year = (p_fiscal_year - 1);
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code IS NULL
AND fiscal_year = (p_fiscal_year -1)
AND status IN ('PURGED', 'ARCHIVED', 'RESTORE');
IF (p_option = 'DELETE') THEN
---------------------------------------------------------
-- Stop the program if row exists for the couple --
-- book-year in jl_co_fa_purge and their status is --
-- different from 'RESTORED' or 'ARCHAIVED' --
---------------------------------------------------------
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code IS NULL
AND fiscal_year = p_fiscal_year
AND status IN ('ARCHIVED', 'RESTORED');
SELECT count(*)
INTO x_count
FROM jl_co_fa_appraisals
WHERE fiscal_year = (p_fiscal_year - 1);
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code IS NULL
AND fiscal_year = (p_fiscal_year -1)
AND status IN ('PURGED');
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code IS NULL
AND fiscal_year = p_fiscal_year
AND status = 'PURGED';
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code IS NULL
AND fiscal_year = (p_fiscal_year +1)
ANd status = 'PURGED';
SELECT count(*)
INTO x_count
FROM jl_co_fa_purge
WHERE book_type_code IS NULL
AND fiscal_year = p_fiscal_year;
fnd_file.put_line( 1, 'Inserting row into JL_CO_FA_PURGE');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserting row into JL_CO_FA_PURGE');
INSERT INTO jl_co_fa_purge(
purge_id,
fiscal_year,
status,
appraisals_rows_archived,
appraisals_check_sum,
asset_apprs_rows_archived,
asset_apprs_check_sum,
adjustments_rows_archived,
adjustments_check_sum,
appraisal_books_rows_archived,
appraisal_books_check_sum,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (jl_co_fa_purge_s.NEXTVAL,
p_fiscal_year,
'NEW',
0,
0,
0,
0,
0,
0,
0,
0,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
fnd_file.put_line( 1, 'Inserted row into JL_CO_FA_PURGE');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserted row into JL_CO_FA_PURGE');
SELECT purge_id
INTO x_index
FROM jl_co_fa_purge
WHERE book_type_code IS NULL
AND fiscal_year = p_fiscal_year;
SELECT u.oracle_username
INTO x_oracle_username
FROM fnd_oracle_userid u,
fnd_product_installations p,
fnd_application a
WHERE a.application_short_name = 'JL'
AND p.application_id = a.application_id
AND p.oracle_id = u.oracle_id;
IF (p_option IN ('ARCHIVE', 'DELETE')) THEN
---------------------------------------------------------
-- Get no of row to be processed and sum of amount for --
-- future check --
---------------------------------------------------------
SELECT count(*),
SUM(NVL(appraisal_id, 0))
INTO x_appraisal_rows,
x_appraisal_amount
FROM jl_co_fa_appraisals
WHERE fiscal_year = p_fiscal_year;
SELECT count(*),
SUM(NVL(appraisal_value, 0))
INTO x_asset_rows,
x_asset_amount
FROM jl_co_fa_asset_apprs
WHERE appraisal_id IN (select appraisal_id
from jl_co_fa_appraisals
where fiscal_year = p_fiscal_year);
SELECT count(*),
SUM(NVL(appraisal_id, 0))
INTO x_book_rows,
x_book_amount
FROM jl_co_fa_appraisal_books
WHERE appraisal_id IN (select appraisal_id
from jl_co_fa_appraisals
where fiscal_year = p_fiscal_year);
SELECT count(*)
INTO x_count
FROM all_tables
WHERE table_name = x_appr_table
AND owner = x_oracle_username;
SELECT count(*)
INTO x_count
FROM all_tables
WHERE table_name = x_asset_table
AND owner = x_oracle_username;
SELECT count(*)
INTO x_count
FROM all_tables
WHERE table_name = x_book_table
AND owner = x_oracle_username;
' as select * from jl_co_fa_appraisals'||
' where fiscal_year = '||p_fiscal_year;
' as select * from jl_co_fa_asset_apprs'||
' where appraisal_id in (select appraisal_id from jl_co_fa_appraisals'||
' where fiscal_year = '||p_fiscal_year||')';
'as select * from jl_co_fa_appraisal_books'||
' where appraisal_id in (select appraisal_id from jl_co_fa_appraisals'||
' where fiscal_year='||p_fiscal_year||')';
UPDATE jl_co_fa_purge SET
appraisals_rows_archived = x_appraisal_rows,
appraisals_check_sum = x_appraisal_amount,
asset_apprs_rows_archived = x_asset_rows,
asset_apprs_check_sum = x_asset_amount,
appraisal_books_rows_archived = x_book_rows,
appraisal_books_check_sum = x_book_amount,
status = 'ARCHIVED',
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
program_update_date = x_sysdate
WHERE purge_id = x_index;
fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
SELECT COUNT(*)
INTO x_count
FROM jl_co_fa_purge
WHERE purge_id = x_index
AND appraisals_rows_archived = x_appraisal_rows
AND appraisals_check_sum = x_appraisal_amount
AND asset_apprs_rows_archived = x_asset_rows
AND asset_apprs_check_sum = x_asset_amount
AND appraisal_books_rows_archived = x_book_rows
AND appraisal_books_check_sum = x_book_amount;
DELETE FROM jl_co_fa_asset_apprs
WHERE appraisal_id IN (select appraisal_id
from jl_co_fa_appraisals
where fiscal_year = p_fiscal_year);
fnd_file.put_line( 1, 'Deleted JL_CO_FA_APPRAISALS');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_ASSET_APPRS');
DELETE FROM jl_co_fa_appraisal_books
WHERE appraisal_id IN (select appraisal_id
from jl_co_fa_appraisals
where fiscal_year = p_fiscal_year);
DELETE FROM jl_co_fa_appraisals
WHERE fiscal_year = p_fiscal_year;
fnd_file.put_line( 1, 'Deleted JL_CO_FA_Appraisals');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Deleted JL_CO_FA_Appraisals');
UPDATE jl_co_fa_purge
SET status = 'PURGED',
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
program_update_date = x_sysdate
WHERE purge_id = x_index;
fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
x_string := 'insert into jl_co_fa_appraisals select * from '||x_oracle_username||'.'||ltrim(rtrim(x_appr_table));
x_string := 'insert into jl_co_fa_asset_apprs select * from '||x_oracle_username||'.'||ltrim(rtrim(x_asset_table));
x_string := 'insert into jl_co_fa_appraisal_books select * from '||x_oracle_username||'.'||ltrim(rtrim(x_book_table));
UPDATE jl_co_fa_purge
SET status = 'RESTORED',
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
program_update_date = x_sysdate
WHERE purge_id = x_index;
fnd_file.put_line( 1, 'Updated JL_CO_FA_PURGE');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_PURGE');
x_last_updated_by := fnd_global.user_id;
x_last_update_login := fnd_global.login_id;
fnd_file.put_line( 1, 'last_update_login:'||to_char(x_last_update_login));
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_update_login:'||to_char(x_last_update_login));
fnd_file.put_line( 1, 'last_updated_by:'||to_char(x_last_updated_by));
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_updated_by:'||to_char(x_last_updated_by));
SELECT column_name
FROM sys.all_tab_columns
WHERE table_name = UPPER(p_table_name);
select col.column_name
from user_synonyms syn, all_tab_columns col
where syn.synonym_name = UPPER(p_table_name)
and col.owner = syn.table_owner
and col.table_name = syn.table_name
order by col.column_id;
x_statement(1) := 'SELECT ';
SELECT apprs.appraisal_id appraisal_id,
fl.meaning appraisal_status,
apprs.appraiser_name appraiser_name,
apprs.appraisal_date appraisal_date
FROM jl_co_fa_appraisals apprs, fnd_lookups fl
WHERE apprs.fiscal_year = p_fiscal_year
AND apprs.appraisal_status <> 'P'
AND apprs.appraisal_status = fl.lookup_code
AND fl.lookup_type = 'JLCO_FA_ASSET_APPRAISAL_STATUS'
ORDER BY apprs.appraisal_id;