[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_values_record
(p_pol_asset_policy_id IN NUMBER,
px_indexation_id IN OUT NOCOPY NUMBER,
p_pol_vendor_id IN NUMBER,
p_pol_policy_number IN VARCHAR2,
p_pol_asset_id IN NUMBER,
p_year IN NUMBER,
p_last_period_closed_date IN DATE,
p_pol_price_index_id IN NUMBER,
p_pol_price_index_value IN NUMBER,
p_cal_insurance_value IN NUMBER,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
);
PROCEDURE update_policies_record
(p_pol_asset_policy_id IN NUMBER,
p_pol_policy_number IN VARCHAR2,
p_pol_asset_id IN NUMBER,
p_cal_insurance_value IN NUMBER,
p_indexation_id IN NUMBER,
p_new_price_index_value IN NUMBER,
p_pol_retirement_value IN NUMBER,
p_last_period_closed_date IN DATE,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
);
/* Cursor to select policy details entered via FA Insurance form - FAIS */
CURSOR Policy ( P_Asset_start VARCHAR2,
P_Asset_end VARCHAR2,
P_Ins_company_id NUMBER,
P_Asset_book VARCHAR2,
year_date_end DATE
) IS
SELECT pol.asset_policy_id,
mpol.vendor_id,
pol.policy_number,
pol.asset_id,
fad.asset_number,
pol.swiss_building,
mpol.calculation_method,
pol.last_indexation_id,
TO_CHAR(pol.base_index_date,'YYYY') pol_base_index_year,
pol.base_index_date,
nvl(pol.current_insurance_value,
pol.base_insurance_value),
pol.last_indexation_date,
pol.last_indexation_date + 1,
pol.current_price_index_id,
pol.value_before_retirement,
nvl(pol.last_price_index_value,pii.price_index_value),
bks.period_counter_fully_reserved
FROM fa_additions fad,
fa_books bks,
fa_ins_policies pol,
fa_ins_mst_pols mpol,
fa_price_index_values pii
WHERE fad.asset_number BETWEEN NVL(p_asset_start, fad.asset_number)
AND NVL(p_asset_end,fad.asset_number)
AND pol.asset_policy_id = mpol.asset_policy_id
AND pol.asset_id = bks.asset_id
AND bks.book_type_code = p_asset_book
AND bks.period_counter_fully_retired is null
AND bks.date_ineffective is null
AND bks.transaction_header_id_out is null
AND fad.asset_id = pol.asset_id
AND pol.book_type_code = p_asset_book
AND mpol.vendor_id = NVL(p_ins_company_id,mpol.vendor_id)
AND pii.price_index_id(+) = pol.current_price_index_id
AND NVL(pol.last_indexation_date,pol.base_index_date)
BETWEEN pii.from_date(+) AND pii.to_date(+)
ORDER BY pol.asset_id,pol.asset_policy_id
FOR UPDATE OF pol.last_indexation_id, pol.current_insurance_value;
/* Cursor to select Reirement details */
CURSOR Get_Transactions (P_Asset_book VARCHAR2,
pol_asset_id NUMBER,
pol_day_after_indexation DATE,
last_period_closed_date DATE) IS
SELECT DECODE(fth.transaction_type_code,
'PARTIAL RETIREMENT', 'P',
'FULL RETIREMENT', 'F',
'REINSTATEMENT', 'R', 'A'),
fth.transaction_header_id,
fth.transaction_date_entered,
bks.cost
FROM fa_transaction_headers fth,
fa_books bks
WHERE bks.date_ineffective is not null
AND fth.transaction_date_entered BETWEEN
nvl(pol_day_after_indexation,fth.transaction_date_entered)
AND last_period_closed_date
AND bks.book_type_code = fth.book_type_code
AND bks.asset_id = fth.asset_id
AND bks.asset_id = pol_asset_id
AND fth.book_type_code = p_asset_book
AND fth.transaction_header_id = bks.transaction_header_id_out
AND fth.transaction_type_code IN
('FULL RETIREMENT','PARTIAL RETIREMENT', 'REINSTATEMENT',
'ADJUSTMENT', 'CIP ADJUSTMENT')
ORDER BY fth.transaction_header_id;
SELECT DECODE(adj.DEBIT_CREDIT_FLAG ,
'CR', -1 * nvl(adj.adjustment_amount,0),
NVL(adj.adjustment_amount,0))
FROM fa_adjustments adj,
fa_transaction_headers fth,
fa_books bks
WHERE bks.date_ineffective is not null
AND fth.transaction_date_entered BETWEEN
nvl(pol_day_after_indexation,fth.transaction_date_entered)
AND last_period_closed_date
AND fth.transaction_header_id = adj.transaction_header_id
AND bks.transaction_header_id_out = fth.transaction_header_id
AND bks.book_type_code = fth.book_type_code
AND bks.asset_id = fth.asset_id
AND bks.asset_id = pol_asset_id
AND fth.book_type_code = p_asset_book
AND fth.transaction_type_code = 'ADJUSTMENT'
AND adj.source_type_code = 'ADJUSTMENT'
AND adj.adjustment_type = 'COST'
AND adj.book_type_code = p_asset_book
AND adj.asset_id = pol_asset_id;
Select count(*)
Into num_loops
FROM fa_transaction_headers fth,
fa_books bks
WHERE bks.date_ineffective is not null
AND fth.transaction_date_entered BETWEEN
nvl(pol_day_after_indexation,fth.transaction_date_entered)
AND last_period_closed_date
AND bks.book_type_code = fth.book_type_code
AND bks.asset_id = fth.asset_id
AND bks.asset_id = pol_asset_id
AND fth.book_type_code = p_asset_book
AND fth.transaction_header_id = bks.transaction_header_id_out
AND fth.transaction_type_code IN
('FULL RETIREMENT','PARTIAL RETIREMENT', 'REINSTATEMENT',
'ADJUSTMENT', 'CIP ADJUSTMENT');
insert_values_record ( pol_asset_policy_id,
indexation_id,
pol_vendor_id,
pol_policy_number,
pol_asset_id,
p_year,
last_period_closed_date,
pol_price_index_id,
pol_price_index_value,
cal_insurance_value);
update_policies_record (pol_asset_policy_id,
pol_policy_number,
pol_asset_id,
cal_insurance_value,
indexation_id,
new_price_index_value,
pol_retirement_value,
last_period_closed_date);
SELECT bks.cost- fdd.deprn_reserve,
fdd.ytd_deprn
FROM fa_books bks,
fa_deprn_summary fdd
WHERE bks.book_type_code = fdd.book_type_code
AND bks.asset_id = fdd.asset_id
AND fdd.asset_id = pol_asset_id
AND fdd.deprn_source_code = 'DEPRN'
AND fdd.period_counter = last_period_closed
AND date_ineffective is null;
SELECT bks.life_in_months asset_total_life,
bks.life_in_months -
floor(months_between(fdp.calendar_period_close_date,
bks.date_placed_in_service))
asset_remaining_life
FROM fa_books bks,
fa_deprn_periods fdp
WHERE bks.book_type_code = P_asset_book
AND fdp.book_type_code = P_asset_book
AND bks.book_type_code = fdp.book_type_code
AND bks.asset_id = pol_asset_id
AND bks.date_ineffective is null
AND fdp.period_close_date is null;
SELECT (bks.life_in_months -
floor(months_between(fdp.calendar_period_close_date,
bks.date_placed_in_service)+1))
FROM fa_books bks,
fa_deprn_periods fdp
WHERE bks.book_type_code = P_asset_book
AND fdp.book_type_code = P_asset_book
AND bks.asset_id = pol_asset_id
AND pol_indexation_date between
fdp.calendar_period_open_date and fdp.calendar_period_close_date;
SELECT fdp1.calendar_period_open_date,
fdp1.period_counter,
fdp2.calendar_period_close_date,
fdp2.period_counter,
fdp2.period_close_date,
fdp1.period_open_date,
fdp1.calendar_period_open_date - 1
FROM fa_Deprn_periods fdp1,
fa_Deprn_periods fdp2
WHERE fdp1.period_counter =
(SELECT MIN(x.period_counter)
FROM fa_Deprn_periods x
WHERE x.fiscal_year = p_year
AND x.book_type_code = p_asset_book
)
AND fdp2.period_counter =
(SELECT MAX(x.period_counter)
FROM fa_Deprn_periods x
WHERE x.fiscal_year = p_year
AND x.book_type_code = p_asset_book
)
AND fdp2.book_type_code = fdp1.book_type_code
AND fdp2.fiscal_year = fdp1.fiscal_year
AND fdp1.fiscal_year = p_year
AND fdp1.book_type_code = p_asset_book;
SELECT fdp1.CALENDAR_PERIOD_CLOSE_DATE,
fdp1.period_counter
FROM fa_deprn_periods fdp1
WHERE fdp1.book_type_code = p_asset_book
AND fdp1.fiscal_year = p_year
AND fdp1.period_counter =
(SELECT MAX(fdp.period_counter)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = p_asset_book
AND fdp.fiscal_year = p_year
AND fdp.period_close_date IS NOT NULL
);
SELECT pii.price_index_id,
pii.price_index_value
FROM fa_price_index_values pii
WHERE pii.price_index_id = nvl(p_pol_price_index_id,0)
AND year_date_end BETWEEN pii.from_date AND pii.to_date;
SELECT pii.price_index_id,
pii.price_index_value
FROM fa_price_index_values pii
WHERE pii.price_index_id = nvl(p_pol_price_index_id,0)
AND pii.to_date = (SELECT max(pii2.to_date)
FROM fa_price_index_values pii2
WHERE pii2.price_index_id = pii.price_index_id);
SELECT SUM(DECODE(adj.DEBIT_CREDIT_FLAG ,
'CR', -1 * nvl(adj.adjustment_amount,0),
NVL(adj.adjustment_amount,0)))
FROM fa_adjustments adj
WHERE adj.transaction_header_id = transaction_id
AND adj.source_type_code = 'ADJUSTMENT'
AND adj.adjustment_type = 'COST'
AND adj.book_type_code = p_book_type_code
AND adj.asset_id = p_asset_id;
SELECT nvl(ret.cost_retired,0)
FROM fa_retirements ret
WHERE ret.transaction_header_id_in (+) = transaction_id
;
SELECT DECODE(fth.transaction_type_code, 'FULL_RETIREMENT','F',
'PARTIAL RETIREMENT','P','R'),
nvl(ret.cost_retired,0),
nvl(bks.cost,0)
FROM fa_retirements ret,
fa_transaction_headers fth,
fa_books bks
WHERE ret.transaction_header_id_out = transaction_id
AND ret.transaction_header_id_in = fth.transaction_header_id
AND bks.transaction_header_id_out = fth.transaction_header_id
;
PROCEDURE update_policies_record
(p_pol_asset_policy_id IN NUMBER,
p_pol_policy_number IN VARCHAR2,
p_pol_asset_id IN NUMBER,
p_cal_insurance_value IN NUMBER,
p_indexation_id IN NUMBER,
p_new_price_index_value IN NUMBER,
p_pol_retirement_value IN NUMBER,
p_last_period_closed_date IN DATE,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
) IS
BEGIN
UPDATE fa_ins_policies pol
SET pol.current_insurance_value = round(p_cal_insurance_value,2),
pol.last_indexation_id = p_indexation_id,
pol.last_price_index_value = nvl(p_new_price_index_value,1),
pol.value_before_retirement = nvl(p_pol_retirement_value,-1),
pol.last_indexation_date = p_last_period_closed_date
WHERE pol.asset_policy_id = p_pol_asset_policy_id
AND pol.policy_number = p_pol_policy_number
AND pol.asset_id = p_pol_asset_id;
END update_policies_record;
PROCEDURE insert_values_record
(p_pol_asset_policy_id IN NUMBER,
px_indexation_id IN OUT NOCOPY NUMBER,
p_pol_vendor_id IN NUMBER,
p_pol_policy_number IN VARCHAR2,
p_pol_asset_id IN NUMBER,
p_year IN NUMBER,
p_last_period_closed_date IN DATE,
p_pol_price_index_id IN NUMBER,
p_pol_price_index_value IN NUMBER,
p_cal_insurance_value IN NUMBER,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
) IS
BEGIN
SELECT FA_INDEXATION_S.nextval
INTO px_indexation_id
FROM dual;
INSERT INTO fa_ins_values
( asset_policy_id,
indexation_id,
vendor_id,
policy_number,
asset_id,
indexation_year,
indexation_date,
price_index_id,
last_price_index_value,
insurance_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) VALUES (
p_pol_asset_policy_id,
px_indexation_id,
p_pol_vendor_id,
p_pol_policy_number,
p_pol_asset_id,
p_year,
p_last_period_closed_date,
p_pol_price_index_id,
p_pol_price_index_value,
round(p_cal_insurance_value,2),
TO_NUMBER(FND_PROFILE.Value('USER_ID')),
SYSDATE,
TO_NUMBER(FND_PROFILE.Value('USER_ID')),
SYSDATE,
TO_NUMBER(FND_PROFILE.Value('LOGIN_ID')),
TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')),
TO_NUMBER(FND_PROFILE.Value('CONC_PROGRAM_APPLICATION_ID')),
TO_NUMBER(FND_PROFILE.Value('CONC_PROGRAM_ID')),
SYSDATE);
END insert_values_record;