[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from dual
where exists ( select 'x'
from fa_mass_update_batch_headers
where status_code IN ('P', 'E', 'R', 'N', 'IP')
and book_type_code = x_book_type_code
and ( x_conc_request_id is null OR
nvl(concurrent_request_id,0) <> x_conc_request_id ) );
select 1
from dual
where exists ( select 'x'
from fa_mass_update_batch_headers hdr
where hdr.status_code IN ('P', 'E', 'R', 'N', 'IP')
and hdr.book_type_code = x_book_type_code
and ( x_conc_request_id is null OR
nvl(hdr.concurrent_request_id,0) <> x_conc_request_id )
and ( hdr.event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
'HR_MASS_TRANSFER')
OR ( hdr.event_code in ( 'CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE')
and ( x_event_code IN ('CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE',
'CHANGE_ASSET_CATEGORY' ) and
to_number(hdr.source_entity_key_value) = x_category_id )
or (x_event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
'CHANGE_ASSET_PARENT', 'HR_MASS_TRANSFER' ) )
)
OR ( hdr.event_code IN ( 'CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE','CHANGE_ASSET_CATEGORY')
and (( x_event_code IN ( 'CHANGE_ASSET_PARENT', 'CHANGE_ASSET_LEASE',
'CHANGE_ASSET_CATEGORY') and
to_number(hdr.source_entity_key_value) = x_asset_id )
OR x_event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
'CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE',
'HR_MASS_TRANSFER' )
) )
)
);
select 1
from dual
where exists ( select 'x'
from fa_mass_update_batch_headers a
where a.status_code IN ('P', 'E', 'R', 'N', 'IP')
and a.book_type_code = x_book_type_code
and a.event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
'HR_MASS_TRANSFER', 'CHANGE_CATEGORY_LIFE',
'CHANGE_CATEGORY_LIFE_END_DATE') );
select 1
from dual
where exists ( select 'x'
from fa_mass_update_batch_headers a
where a.status_code IN ('P', 'E', 'R', 'N', 'IP')
and a.book_type_code = x_book_type_code
and ( a.event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
'HR_MASS_TRANSFER') or
(a.event_code IN ( 'CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE') and
to_number(a.source_entity_key_value) = x_category_id ) or
(a.event_code IN ( 'CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE',
'CHANGE_ASSET_CATEGORY') and
to_number(a.source_entity_key_value) = x_asset_id )
) );
select 1
from dual
where exists ( select 'x'
from fa_mass_update_batch_headers a
where a.status_code IN ('P', 'E', 'R', 'N', 'IP')
and a.book_type_code = x_book_type_code
and ( a.event_code IN ( 'CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
'HR_MASS_TRANSFER', 'CHANGE_CATEGORY_LIFE',
'CHANGE_CATEGORY_LIFE_END_DATE') or
( a.event_code IN ( 'CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE',
'CHANGE_ASSET_CATEGORY') and
to_number(a.source_entity_key_value) = x_asset_id )
) );
select 1
from dual
where exists ( select 'x'
from fa_mass_update_batch_headers a
where a.status_code IN ('P', 'R')
AND EXISTS ( select 'x'
from fa_mass_update_batch_details b
where a.batch_id = b.batch_id )
AND ( ( (source_entity_key_value = x_node_id AND
event_code = 'CHANGE_NODE_ATTRIBUTE')
OR (source_attribute_old_id = x_node_id AND
event_code = 'CHANGE_NODE_PARENT')
)
OR ( event_code IN ( 'CHANGE_CATEGORY_LIFE', 'CHANGE_CATEGORY_LIFE_END_DATE') AND
to_number(a.source_entity_key_value) = (select asset_category_id
from fa_additions
where asset_id = x_asset_id )
)
)
);
select 1
from dual
where exists ( select 'X'
from fa_hr_retirement_details
-- where status_code = 'P' -- msiddiqu 15-feb-2001
where status_code IN ('P', 'IP')
and asset_id = nvl(x_asset_id, asset_id)
and book_type_code = x_book_type_code
and ( x_conc_request_id is null OR
nvl(concurrent_request_id,0) <> x_conc_request_id ) );
select 1
into v_dummy
from dual
where exists
(
select 'X'
from fa_mass_update_batch_headers a,
fa_mass_update_batch_details b
where a.status_code <> 'C'
and a.event_code <> 'HR_REINSTATEMENT' -- bugfix for 891822 msiddiqu 25-APR-2001
and a.batch_id = b.batch_id
and b.status_code in ('P','R') -- uncommented for bugfix 1613882
-- where x_attribute IS NULL
-- where status_code = 'P' -- commented for bugfix 1613882
and b.asset_id = nvl(x_asset_id, b.asset_id)
and b.book_type_code = x_book_type_code
);
select 1
into v_dummy
from dual
where exists
(
select 'X'
from fa_mass_update_batch_headers a,
fa_mass_update_batch_details b
-- where x_attribute IS NOT NULL
-- where x_attribute = 'ASSET_KEY'
where a.status_code <> 'C'
and a.batch_id = b.batch_id
and b.attribute_name IN ('ASSET_KEY', 'CATEGORY')
and b.status_code in ( 'P', 'R') -- bugfix 1613882
-- and status_code = 'P'
and b.asset_id = nvl(x_asset_id, b.asset_id)
and b.book_type_code = x_book_type_code
);
select 1
into v_dummy
from dual
where exists
(
select 'X'
from fa_mass_update_batch_headers a,
fa_mass_update_batch_details b
-- where x_attribute IS NOT NULL
-- where x_attribute = 'DISTRIBUTION'
where a.status_code <> 'C'
and a.batch_id = b.batch_id
and b.attribute_name IN ('DISTRIBUTION', 'CATEGORY')
and b.asset_id = nvl(x_asset_id, b.asset_id)
and b.book_type_code = x_book_type_code
);
select 1
into v_dummy
from dual
where exists
(
select 'X'
from fa_mass_update_batch_headers a,
fa_mass_update_batch_details b
-- where x_attribute IS NOT NULL
-- where x_attribute = 'LEASE_NUMBER'
where a.status_code <> 'C'
and a.batch_id = b.batch_id
and b.attribute_name IN ('LEASE_NUMBER', 'CATEGORY')
-- and status_code = 'P' -- bugfix 1613882
and b.status_code in ( 'P', 'R')
and b.asset_id = nvl(x_asset_id, b.asset_id)
and b.book_type_code = x_book_type_code
);
select 1
into v_dummy
from dual
where exists
(
select 'X'
from fa_mass_update_batch_headers a,
fa_mass_update_batch_details b
-- where x_attribute IS NOT NULL
-- where x_attribute = 'LIFE_END_DATE'
where a.status_code <> 'C'
and a.batch_id = b.batch_id
and b.attribute_name IN ('CATEGORY', 'LEASE_NUMBER', 'LIFE_END_DATE')
-- and status_code = 'P' -- bugfix 1613882
and b.status_code in ( 'P', 'R')
and b.asset_id = nvl(x_asset_id, b.asset_id)
and b.book_type_code = x_book_type_code
);
select 1
into v_dummy
from dual
where exists
(
select 'X'
from fa_mass_update_batch_headers a,
fa_mass_update_batch_details b
-- where x_attribute IS NOT NULL
-- where x_attribute = 'CATEGORY'
-- if category check for all attributes
where -- status_code = 'P' -- bugfix 1613882
a.status_code <> 'C'
and a.batch_id = b.batch_id
and b.status_code in ( 'P', 'R')
and b.asset_id = nvl(x_asset_id, b.asset_id)
and b.book_type_code = x_book_type_code
);
PROCEDURE insert_hr_retirement_hdrs(
x_event_code IN VARCHAR2
, x_book_type_code IN VARCHAR2
, x_status IN VARCHAR2
, x_node_entity_id IN NUMBER
, x_rejection_reason_code IN VARCHAR2
, x_retirement_method IN VARCHAR2
, x_retirement_type_code IN VARCHAR2
, x_proceeds_of_sale IN NUMBER
, x_cost_of_removal IN NUMBER
, x_retire_date IN DATE
, x_prorate_by IN VARCHAR2
, x_retire_by IN VARCHAR2
, x_retirement_amount IN NUMBER
, x_retirement_percent IN NUMBER
, x_allow_partial_retire_flg IN VARCHAR2
, x_retire_units_flg IN VARCHAR2
, x_created_by IN NUMBER
, x_creation_date IN DATE
, x_last_updated_by IN NUMBER
, x_last_update_date IN DATE
, x_last_update_login IN NUMBER
, x_concurrent_request_id IN NUMBER
, x_batch_id IN OUT NOCOPY NUMBER
, x_transaction_name IN VARCHAR2
, x_attribute_category IN VARCHAR2
, x_attribute1 IN VARCHAR2
, x_attribute2 IN VARCHAR2
, x_attribute3 IN VARCHAR2
, x_attribute4 IN VARCHAR2
, x_attribute5 IN VARCHAR2
, x_attribute6 IN VARCHAR2
, x_attribute7 IN VARCHAR2
, x_attribute8 IN VARCHAR2
, x_attribute9 IN VARCHAR2
, x_attribute10 IN VARCHAR2
, x_attribute11 IN VARCHAR2
, x_attribute12 IN VARCHAR2
, x_attribute13 IN VARCHAR2
, x_attribute14 IN VARCHAR2
, x_attribute15 IN VARCHAR2
, TH_attribute_category IN VARCHAR2
, TH_attribute1 IN VARCHAR2
, TH_attribute2 IN VARCHAR2
, TH_attribute3 IN VARCHAR2
, TH_attribute4 IN VARCHAR2
, TH_attribute5 IN VARCHAR2
, TH_attribute6 IN VARCHAR2
, TH_attribute7 IN VARCHAR2
, TH_attribute8 IN VARCHAR2
, TH_attribute9 IN VARCHAR2
, TH_attribute10 IN VARCHAR2
, TH_attribute11 IN VARCHAR2
, TH_attribute12 IN VARCHAR2
, TH_attribute13 IN VARCHAR2
, TH_attribute14 IN VARCHAR2
, TH_attribute15 IN VARCHAR2
, x_err_code IN OUT NOCOPY VARCHAR2
, x_err_stage IN OUT NOCOPY VARCHAR2
, x_err_stack IN OUT NOCOPY VARCHAR2 ) IS
CURSOR C1 IS
select fa_hr_retirement_hdrs_s.nextval
from dual;
x_err_stack:= x_err_stack||'-> Insert_hr_retirement_hdrs';
x_err_stage:= 'Inserting retirement_headers';
Insert into fa_hr_retirement_headers(
event_code
, book_type_code
, status_code
, asset_hierarchy_id
, rejection_reason_code
, retirement_method
, retirement_type_code
, retire_date
, prorate_by
, retire_by
, retirement_amount
, retirement_percent
, allow_partial_retire_flag
, retire_units_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, concurrent_request_id
, batch_id
, transaction_name
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, th_attribute_category
, th_attribute1
, th_attribute2
, th_attribute3
, th_attribute4
, th_attribute5
, th_attribute6
, th_attribute7
, th_attribute8
, th_attribute9
, th_attribute10
, th_attribute11
, th_attribute12
, th_attribute13
, th_attribute14
, th_attribute15
, proceeds_of_sale
, cost_of_removal
)
values(
x_event_code
, x_book_type_code
, x_status
, x_node_entity_id
, x_rejection_reason_code
, x_retirement_method
, x_retirement_type_code
, x_retire_date
, x_prorate_by
, x_retire_by
, x_retirement_amount
, x_retirement_percent
, x_allow_partial_retire_flg
, x_retire_units_flg
, x_created_by
, x_creation_date
, x_last_updated_by
, x_last_update_date
, x_last_update_login
, x_concurrent_request_id
, x_batch_id
, x_transaction_name
, x_attribute_category
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, TH_attribute_category
, TH_attribute1
, TH_attribute2
, TH_attribute3
, TH_attribute4
, TH_attribute5
, TH_attribute6
, TH_attribute7
, TH_attribute8
, TH_attribute9
, TH_attribute10
, TH_attribute11
, TH_attribute12
, TH_attribute13
, TH_attribute14
, TH_attribute15
, x_proceeds_of_sale
, x_cost_of_removal );
END insert_hr_retirement_hdrs;
PROCEDURE insert_hr_retirement_dtls(
x_batch_id IN NUMBER
, x_book_type_code IN VARCHAR2
, x_asset_id IN NUMBER
, x_date_placed_in_service IN DATE
, x_current_cost IN NUMBER
, x_cost_retired IN NUMBER
, x_current_units IN NUMBER
, x_units_retired IN NUMBER
, x_prorate_percent IN NUMBER
, x_retirement_convention_code IN VARCHAR2
, x_status_code IN VARCHAR2
, x_rejection_reason IN VARCHAR2
, x_proceeds_of_sale IN NUMBER
, x_cost_of_removal IN NUMBER
, x_created_by IN NUMBER
, x_creation_date IN DATE
, x_last_updated_by IN NUMBER
, x_last_update_date IN DATE
, x_last_update_login IN NUMBER
, x_concurrent_request_id IN NUMBER
, x_err_code IN OUT NOCOPY VARCHAR2
, x_err_stage IN OUT NOCOPY VARCHAR2
, x_err_stack IN OUT NOCOPY VARCHAR2 ) IS
v_old_err_stack VARCHAr2(640);
x_err_stack := x_err_stack||'->'||'insert_fa_hr_retirement_dtls';
insert into fa_hr_retirement_details(
batch_id
, book_type_code
, asset_id
, date_placed_in_service
, current_cost
, cost_retired
, current_units
, units_retired
, prorate_percent
, retirement_convention_code
, status_code
, rejection_reason
, proceeds_of_sale
, cost_of_removal
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, concurrent_request_id )
values (
x_batch_id
, x_book_type_code
, x_asset_id
, x_date_placed_in_service
, x_current_cost
, x_cost_retired
, x_current_units
, x_units_retired
, x_prorate_percent
, x_retirement_convention_code
, x_status_code
, x_rejection_reason
, x_proceeds_of_sale
, x_cost_of_removal
, x_created_by
, x_creation_date
, x_last_updated_by
, x_last_update_date
, x_last_update_login
, x_concurrent_request_id );
END insert_hr_retirement_dtls;
v_last_update_login NUMBER;
v_last_updated_by NUMBER;
select sum(fab.cost) total_cost
, sum(fah.units) total_units
from ( select asset_id
from fa_asset_hierarchy
where asset_id IS NOT NULL
start with asset_hierarchy_id = x_node_entity_id
connect by prior asset_hierarchy_id = parent_hierarchy_id ) hr
, fa_asset_history fah
, fa_category_book_defaults fcbd
, fa_books fab
, fa_additions faa
where hr.asset_id = faa.asset_id
AND faa.asset_id = fab.asset_id
AND fab.book_type_code = x_book_type_code
-- AND fab.cost > 0
AND faa.asset_id = fah.asset_id
AND fah.date_ineffective IS NULL
AND faa.asset_category_id = fcbd.category_id
AND fab.book_type_code = fcbd.book_type_code
AND fab.date_placed_in_service
BETWEEN fcbd.start_dpis
AND nvl(TO_DATE(fcbd.end_dpis, 'DD-MM-YYYY'),
TO_DATE('31-12-4712', 'DD-MM-YYYY'))
AND EXISTS (SELECT 'X'
FROM FA_TRANSACTION_HEADERS fth
WHERE fth.asset_id = fab.asset_id
AND fth.book_type_code = fab.book_type_code
AND (fth.transaction_date_entered <= x_Retire_Date
AND fth.transaction_type_code not in ('FULL RETIREMENT',
'REINSTATEMENT')))
AND EXISTS ( SELECT 'X'
FROM fa_distribution_history fad
, gl_code_combinations gcc
WHERE fad.asset_id = faa.asset_id
AND fad.code_combination_id = gcc.code_combination_id
AND fad.date_ineffective IS NULL )
AND NOT EXISTS ( select 'X' --'PROCESSED RETIREMENT'
from fa_retirements frt,
fa_books fb
where frt.asset_id = fab.asset_id
AND frt.asset_id = fb.asset_id
AND frt.transaction_header_id_out is NULL
AND frt.status = 'PROCESSED'
AND frt.book_type_code = fb.book_type_code
AND fb.period_counter_fully_retired is NOT NULL
AND fb.transaction_header_id_in =
frt.transaction_header_id_in
AND fb.date_ineffective IS NULL )
AND faa.asset_type IN ('CIP', 'CAPITALIZED', 'EXPENSED')
AND fab.date_ineffective IS NULL ;
select faa.asset_id
, faa.asset_number
, fab.cost
, fab.date_placed_in_service
, fcbd.retirement_prorate_convention ret_conv
, fah.units
, fab.itc_amount
, fab.itc_amount_id
from ( select asset_id
from fa_asset_hierarchy
where asset_id IS NOT NULL
start with asset_hierarchy_id = x_node_entity_id
connect by prior asset_hierarchy_id = parent_hierarchy_id ) hr
, fa_asset_history fah
, fa_category_book_defaults fcbd
, fa_books fab
, fa_additions faa
where hr.asset_id = faa.asset_id
AND faa.asset_id = fab.asset_id
AND fab.book_type_code = x_book_type_code
-- AND fab.cost > 0
AND faa.asset_id = fah.asset_id
AND fah.date_ineffective IS NULL
AND faa.asset_category_id = fcbd.category_id
AND fab.book_type_code = fcbd.book_type_code
AND fab.date_placed_in_service
BETWEEN fcbd.start_dpis
AND nvl(TO_DATE(fcbd.end_dpis, 'DD-MM-YYYY'),
TO_DATE('31-12-4712', 'DD-MM-YYYY'))
AND EXISTS (SELECT 'X'
FROM FA_TRANSACTION_HEADERS fth
WHERE fth.asset_id = fab.asset_id
AND fth.book_type_code = fab.book_type_code
AND (fth.transaction_date_entered <= x_Retire_Date
AND fth.transaction_type_code not in ('FULL RETIREMENT',
'REINSTATEMENT')))
AND EXISTS ( SELECT 'X'
FROM fa_distribution_history fad
, gl_code_combinations gcc
WHERE fad.asset_id = faa.asset_id
AND fad.code_combination_id = gcc.code_combination_id
AND fad.date_ineffective IS NULL )
AND NOT EXISTS ( select 'X' --'PROCESSED RETIREMENT'
from fa_retirements frt,
fa_books fb
where frt.asset_id = fab.asset_id
AND frt.asset_id = fb.asset_id
AND frt.transaction_header_id_out is NULL
AND frt.status = 'PROCESSED'
AND frt.book_type_code = fb.book_type_code
AND fb.period_counter_fully_retired is NOT NULL
AND fb.transaction_header_id_in =
frt.transaction_header_id_in
AND fb.date_ineffective IS NULL )
AND faa.asset_type IN ('CIP', 'CAPITALIZED', 'EXPENSED')
AND fab.date_ineffective IS NULL
ORDER BY 4 asc;
select --sob.currency_code
fc.precision
--, fc.extended_precision
--, fc.minimum_accountable_unit
from gl_sets_of_books sob,
fa_book_controls fbc,
fnd_currencies fc
where fc.currency_code = sob.currency_code
and fc.enabled_flag = 'Y'
and fbc.book_type_code = x_book_type_code
and fbc.set_of_books_id = sob.set_of_books_id;
v_last_updated_by:= v_created_by;
v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
x_err_stack:= x_err_stack||'->'||'Insert_hr_retirement_hdrs';
insert_hr_retirement_hdrs (
x_event_code
, x_book_type_code
, 'IP'
, x_node_entity_id
, v_rejection_reason_code
, x_retirement_method
, x_retirement_type_code
, x_proceeds_of_sale
, x_cost_of_removal
, x_retire_date
, x_prorate_by
, x_retire_by
, x_retirement_amount
, x_retirement_percent
, x_allow_partial_retire
, x_retire_units
, v_created_by
, v_sysdate -- creation_date
, v_last_updated_by
, v_sysdate -- last_update_date
, v_last_update_login
, v_conc_request_id
, x_batch_id
, x_transaction_name
, x_attribute_category
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, TH_attribute_category
, TH_attribute1
, TH_attribute2
, TH_attribute3
, TH_attribute4
, TH_attribute5
, TH_attribute6
, TH_attribute7
, TH_attribute8
, TH_attribute9
, TH_attribute10
, TH_attribute11
, TH_attribute12
, TH_attribute13
, TH_attribute14
, TH_attribute15
, x_err_code
, x_err_stage
, x_err_stack );
x_err_stack:= x_err_stack||'->'||'Insert_hr_retirement_dtls';
insert_hr_retirement_dtls(
x_batch_id
, x_book_type_code
, ret_tab(i).asset_id
, ret_tab(i).dpis
, ret_tab(i).cost
, ret_tab(i).cost_retired
, ret_tab(i).units
, ret_tab(i).units_retired
, ret_tab(i).prorate_percent
, ret_tab(i).ret_prorate_conv
, 'IP' --x_status_code
, v_rejection_reason_code
, ret_tab(i).proceeds_of_sale
, ret_tab(i).cost_of_removal
, v_created_by
, v_sysdate --v_creation_date
, v_last_updated_by
, v_sysdate --v_last_update_date
, v_last_update_login
, v_conc_request_id
, x_err_code
, x_err_stage
, x_err_stack );
x_err_stack:= x_err_stack||'->'||'Insert_hr_retirement_dtls';
insert_hr_retirement_dtls(
x_batch_id
, x_book_type_code
, qualified_asset_rec.asset_id
, qualified_asset_rec.date_placed_in_service
, nvl(qualified_asset_rec.cost, 0)
, nvl(v_cost_retired, 0)
, nvl(qualified_asset_rec.units, 0)
, nvl(v_units_retired, 0)
, nvl(v_prorate_percent, 0)
, qualified_asset_rec.retirement_prorate_convention
, 'IP' --x_status_code
, v_rejection_reason_code
, nvl(v_asset_pos, 0)
, nvl(v_asset_cor, 0)
, v_created_by
, v_sysdate --v_creation_date
, v_last_updated_by
, v_sysdate --v_last_update_date
, v_last_update_login
, v_conc_request_id
, x_err_code
, x_err_stage
, x_err_stack );
select *
from fa_hr_retirement_headers
where batch_id >= nvl(x_from_batch_num, batch_id )
AND batch_id <= nvl(x_to_batch_num, batch_id)
AND status_code IN ('IP', 'P', 'RC')
order by creation_date
FOR UPDATE NOWAIT;
update fa_hr_retirement_headers
set status_code = 'IP'
where batch_id = hrh_rec.batch_id;
update fa_hr_retirement_details
set status_code = 'IP'
where batch_id = hrh_rec.batch_id;
select 'Y'
into v_dummy
from fa_hr_retirement_details
where batch_id = hrh_rec.batch_id
and status_code in ('IP','R')
and rownum = 1;
update fa_hr_retirement_headers
set status_code = 'R' -- Rejected Processed
, concurrent_request_id = v_conc_request_ID
, last_updated_by = fnd_global.login_id
, last_update_date = sysdate
, last_update_login = fnd_global.login_id
where batch_id = hrh_rec.batch_id;
Update fa_hr_retirement_details
set status_code = 'P'
where status_code <> 'R'
and batch_id = hrh_rec.batch_id;
update fa_hr_retirement_headers
set status_code = 'CP' -- Completetly Processed
, concurrent_request_id = v_conc_request_ID
, last_updated_by = fnd_global.login_id
, last_update_date = sysdate
, last_update_login = fnd_global.login_id
where batch_id = hrh_rec.batch_id;
select batch_id,
asset_id,
cost_retired,
current_cost,
current_units,
units_retired,
book_type_code,
status_code,
retirement_convention_code
from fa_hr_retirement_details
where batch_id = x_batch_id
order by date_placed_in_service asc;
select distribution_id
, code_combination_id
, units_assigned
, location_id
, assigned_to
, date_effective
, transaction_header_id_in
from fa_distribution_history
where book_type_code = x_book_type_code
and asset_id = x_asset_id
and date_ineffective IS NULL;
v_last_update_login NUMBER;
v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
update fa_hr_retirement_details
set retirement_id = v_retirement_id
where batch_id = hrd_rec.batch_id
and asset_id = hrd_rec.asset_id;
UPDATE fa_hr_retirement_details
SET status_code = 'R'
, rejection_reason = v_error_tab(hrd_rec.asset_id).rejection_reason
, concurrent_request_id = x_conc_request_id
, last_updated_by = v_user
, last_update_date = v_sysdate
, last_update_login = v_last_update_login
WHERE asset_id = hrd_rec.asset_id
and batch_id = x_batch_id;
UPDATE fa_hr_retirement_details
SET status_code = 'A'
, rejection_reason = null
, concurrent_request_id = x_conc_request_id
-- , retirement_id = v_retirement_id
, last_updated_by = v_user
, last_update_date = v_sysdate
, last_update_login = v_last_update_login
WHERE batch_id = x_batch_id;