The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_last_update_date DATE;
X_last_updated_by NUMBER;
X_last_update_login NUMBER;
X_bind := 'JGRX_FAREG.fa_' || p_type || '_bind' || '(:CURSOR_SELECT);';
X_last_update_date := SYSDATE;
X_last_updated_by := FND_GLOBAL.user_id;
X_last_update_login := FND_GLOBAL.login_id;
' AND (BO.date_effective,transaction_header_id_in) = (SELECT MAX(date_effective),'||
' max(transaction_header_id_in)' ||
' FROM fa_books' ||
' WHERE date_placed_in_service <= :b_period_to_date' ||
-- ' WHERE date_effective < :b_period_to_date' || // Date_effective -> Date_placed_in_service
' AND book_type_code = bo.book_type_code' ||
' AND asset_id = BO.asset_id)' ||
' AND AD.asset_type = ''CAPITALIZED''' ||
' AND ((BO.period_counter_fully_retired IS NULL)'||
' OR ((BO.period_counter_fully_retired IS NOT NULL)'||
' AND BO.TRANSACTION_HEADER_ID_IN ='||
' ( SELECT RE.TRANSACTION_HEADER_ID_IN'||
' FROM FA_RETIREMENTS RE'||
' WHERE BO.ASSET_ID = RE.ASSET_ID'||
' AND BO.TRANSACTION_HEADER_ID_IN = RE.TRANSACTION_HEADER_ID_IN'||
' AND RE.DATE_RETIRED >=:b_fiscal_year_start_date)))'||
' AND CB.category_id = CA.category_id' ||
' AND CB.book_type_code = BO.book_type_code' ||
' AND BO.deprn_method_code = ME.method_code' ||
' AND NVL(BO.life_in_months,-99) = NVL(ME.life_in_months,-99)';
' AND BO.date_effective = (SELECT MAX(date_effective)' ||
' FROM fa_books' ||
' WHERE TO_CHAR(date_effective,''DD-MON-YYYY HH:MI:SS'') < ' ||
' TO_CHAR(:b_period_to_date,''DD-MON-YYYY HH:MI:SS'')' ||
' AND book_type_code = BO.book_type_code' ||
' AND asset_id = BO.asset_id) '||
' AND RE.date_effective = (SELECT MAX(date_effective)' ||
' FROM fa_retirements' ||
' WHERE TO_CHAR(date_effective,''DD-MON-YYYY HH:MI:SS'') < ' ||
' TO_CHAR(:b_period_to_date,''DD-MON-YYYY HH:MI:SS'')' ||
' AND book_type_code = BO.book_type_code' ||
' AND asset_id = BO.asset_id)';
select sob.chart_of_accounts_id
into X_id_flex_num
from gl_sets_of_books sob,
fa_book_controls bkc
where bkc.book_type_code = PARM.p_Book_type_code and
bkc.set_of_books_id = sob.set_of_books_id;
SELECT calendar_period_open_date,
calendar_period_close_date,
period_counter,
fiscal_year -- fiscal year for periods
INTO PARM.p_begin_period_from_date,
PARM.p_begin_period_to_date,
PARM.p_begin_period_counter,
X_fiscal_year
FROM fa_deprn_periods
WHERE book_type_code = PARM.p_book_type_code
AND period_counter > (SELECT MIN(DP2.period_counter)
FROM fa_deprn_periods DP2
WHERE DP2.book_type_code = PARM.p_book_type_code)
AND period_name = PARM.p_period_from;
SELECT calendar_period_open_date,
calendar_period_close_date,
period_counter
INTO PARM.p_end_period_from_date,
PARM.p_end_period_to_date,
PARM.p_end_period_counter
FROM fa_deprn_periods
WHERE book_type_code = PARM.p_book_type_code
AND period_counter > (SELECT MIN(DP2.period_counter)
FROM fa_deprn_periods DP2
WHERE DP2.book_type_code = PARM.p_book_type_code)
AND period_name = PARM.p_period_to;
SELECT SUM((DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0)))
INTO JGRX_FAREG.var.deprn_reserve
FROM fa_adjustments AD
WHERE AD.source_type_code in ('RETIREMENT')
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.debit_credit_flag = 'DR'
AND transaction_header_id = JGRX_FAREG.var.transaction_header_id;
SELECT SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0))
INTO JGRX_FAREG.var.bonus_reserve
FROM fa_adjustments AD
WHERE AD.source_type_code in ('RETIREMENT')
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.debit_credit_flag = 'DR'
AND transaction_header_id = JGRX_FAREG.var.transaction_header_id;
SELECT FY.fiscal_year
INTO X_ret
FROM fa_fiscal_year FY,
fa_convention_types CT,
fa_books BO
WHERE CT.prorate_convention_code = BO.prorate_convention_code
AND FY.fiscal_year_name = CT.fiscal_year_name
AND BO.date_ineffective IS NULL
AND BO.date_placed_in_service BETWEEN FY.start_date
AND FY.end_date
AND BO.asset_id = JGRX_FAREG.var.asset_id
AND BO.book_type_code = PARM.p_book_type_code;
SELECT number_per_fiscal_year
INTO X_number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = X_deprn_calendar; -- X_deprn_calendar is retrieved in Get_fiscal_year_date()
SELECT (ROUND(MONTHS_BETWEEN(PARM.p_end_period_to_date,JGRX_FAREG.var.date_placed_in_service)/
X_number_per_fiscal_year))+1
INTO X_life_of_asset
FROM dual;
SELECT rate
INTO JGRX_FAREG.var.deprn_rate
FROM fa_rates
WHERE method_id = JGRX_FAREG.var.method_id
AND year = X_life_of_asset
AND period_placed_in_service = (SELECT period_num
FROM fa_calendar_periods
WHERE calendar_type = X_deprn_calendar -- X_deprn_calendar is retrieved in Get_fiscal_year_date()
AND JGRX_FAREG.var.prorate_date BETWEEN start_date -- X_prorate_date is retrieved in Get_fiscal_year_date()
AND end_date);
v_Selectstmnt varchar2(20000);
v_Selectstmnt1 varchar2(10000);
v_selectstmnt2 varchar2(10000);
v_selectstmnt3 varchar2(10000);
v_Selectstmnt1 :=
'select '||X_account_segment||' from '||
' fa_distribution_history dih,'||
' gl_code_combinations gcc'||
' where dih.asset_id ='||JGRX_FAREG.var.asset_id || 'and '||
' dih.book_type_code = '||''''||PARM.p_book_type_code ||''''||' and'||
' gcc.code_combination_id = dih.code_combination_id'||
' and dih.transaction_header_id_in =';
v_selectstmnt2 := '(select to_char(MAX(transaction_heaDer_id))'||
' from fa_transaction_headers trh,'||
' fa_distribution_history dih1'||
' where dih1.asset_id= dih.asset_id and'||
' dih1.book_type_code =dih.book_type_code and'||
' dih1.transaction_header_id_in = trh.transaction_header_id and';
v_selectstmnt3 := ' transaction_date_entered <= '||''''||PARM.p_end_period_to_date||''''||')';
DBMS_SQL.PARSE(V_cursorId,v_selectstmnt1||v_selectstmnt2||v_selectstmnt3,DBMS_SQL.V7);
select invoice_number,vendor_name
from fa_asset_invoices ai,po_vendors ve,fa_invoice_transactions IT
where ai.po_vendor_id= ve.vendor_id and
ai.asset_id = JGRX_FAREG.var.asset_id and
ai.invoice_transaction_id_in = IT.invoice_transaction_id and
IT.book_type_code = PARM.p_book_type_code;
select invoice_number,vendor_name
into JGRX_FAREG.var.invoice_number,
JGRX_FAREG.var.supplier_name
from fa_asset_invoices ai,po_vendors ve
where ai.po_vendor_id= ve.vendor_id and
ai.asset_invoice_id= (SELECT MIN(asset_invoice_id)
FROM fa_asset_invoices AI1,fa_invoice_transactions IT
WHERE AI1.asset_id = JGRX_FAREG.var.asset_id
AND AI1.invoice_transaction_id_in = IT.invoice_transaction_id
AND IT.book_type_code = PARM.p_book_type_code);
SELECT asset_number
INTO X_parent_asset_number
FROM fa_additions
WHERE asset_id = JGRX_FAREG.var.parent_asset_id;
SELECT company_name
INTO JGRX_FAREG.var.organization_name
FROM fa_system_controls;
SELECT currency_code
INTO JGRX_FAREG.var.functional_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
SELECT count(*)
INTO X_capitalized
FROM fa_books BO
WHERE BO.book_type_code = PARM.p_book_type_code
AND BO.asset_id = JGRX_FAREG.var.asset_id
AND BO.period_counter_capitalized BETWEEN PARM.p_begin_period_counter AND
PARM.p_end_period_counter;
SELECT cost,
transaction_header_id_in
INTO JGRX_FAREG.var.asset_cost_initial,
X_transaction_id_initial
FROM fa_books BO
WHERE BO.book_type_code = PARM.p_book_type_code
AND BO.asset_id = JGRX_FAREG.var.asset_id
AND (TO_CHAR(BO.date_effective, 'DD-MON-YYYY HH:MI:SS'),transaction_header_id_in) =
(SELECT TO_CHAR(MAX(BO1.date_effective), 'DD-MON-YYYY HH:MI:SS'),
max(transaction_header_id_in)
FROM fa_books BO1, fa_transaction_headers TRH
WHERE BO1.book_type_code = BO.book_type_code
AND BO1.asset_id = BO.asset_id
AND TRH.transaction_header_id= BO1.transaction_header_id_in
AND TRH.transaction_date_entered < PARM.p_begin_period_from_date);
SELECT cost,
transaction_header_id_in
INTO JGRX_FAREG.var.asset_cost_final,
X_transaction_id_final
FROM fa_books BO
WHERE BO.book_type_code = PARM.p_book_type_code
AND BO.asset_id = JGRX_FAREG.var.asset_id
AND (TO_CHAR(BO.date_effective, 'DD-MON-YYYY HH:MI:SS'),transaction_header_id_in) =
(SELECT TO_CHAR(MAX(BO1.date_effective), 'DD-MON-YYYY HH:MI:SS'),
max(transaction_header_id_in)
FROM fa_books BO1,FA_TRANSACTION_HEADERS TRH
WHERE BO1.book_type_code = BO.book_type_code
AND BO1.asset_id = BO.asset_id
AND TRH.transaction_header_id= BO1.transaction_header_id_in
AND TRH.transaction_date_entered <= PARM.p_end_period_to_date);
SELECT SUM(AD.adjustment_amount)
INTO X_partial_addition
FROM fa_books BO,
fa_transaction_headers TH,
fa_adjustments AD
WHERE BO.book_type_code = PARM.p_book_type_code
AND BO.asset_id = JGRX_FAREG.var.asset_id
AND BO.transaction_header_id_in <> NVL(X_transaction_id_initial, 0)
AND BO.book_type_code = TH.book_type_code
AND BO.asset_id = TH.asset_id
AND BO.transaction_header_id_in = TH.transaction_header_id
AND TH.transaction_type_code = 'ADDITION'
AND AD.transaction_header_id = TH.transaction_header_id
AND AD.source_type_code = TH.TRANSACTION_TYPE_CODE
AND AD.book_type_code = TH.book_type_code
AND AD.asset_id = TH.asset_id
AND AD.adjustment_type = 'COST'
AND AD.debit_credit_flag = 'DR'
AND AD.adjustment_amount > 0
AND TH.transaction_date_entered BETWEEN (PARM.p_begin_period_from_date)
AND (PARM.p_end_period_to_date);
SELECT SUM(AD.adjustment_amount)
INTO X_manual_adjustment_plus
FROM fa_transaction_headers TH,
fa_adjustments AD
WHERE AD.transaction_header_id = TH.transaction_header_id
AND AD.book_type_code = TH.book_type_code
AND AD.asset_id = TH.asset_id
AND TH.transaction_type_code = 'ADJUSTMENT'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND AD.adjustment_type = 'COST'
AND AD.debit_credit_flag = 'DR'
AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
AND PARM.p_end_period_to_date;
SELECT SUM(AD.adjustment_amount)
INTO X_revaluation_plus
FROM fa_transaction_headers TH,
fa_adjustments AD
WHERE AD.transaction_header_id = TH.transaction_header_id
AND AD.book_type_code = TH.book_type_code
AND AD.asset_id = TH.asset_id
AND TH.transaction_type_code = 'REVALUATION'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND AD.adjustment_type = 'COST'
AND AD.debit_credit_flag = 'DR'
AND AD.adjustment_amount > 0
AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
AND PARM.p_end_period_to_date;
SELECT SUM(RE.cost_retired)
INTO X_reinstatements
FROM fa_transaction_headers TH,
fa_retirements RE
WHERE RE.transaction_header_id_out = TH.transaction_header_id
AND RE.book_type_code = TH.book_type_code
AND RE.asset_id = TH.asset_id
AND TH.transaction_type_code = 'REINSTATEMENT'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
AND PARM.p_end_period_to_date;
SELECT SUM(AD.adjustment_amount)
INTO X_credit_memos
FROM fa_books BO,
fa_transaction_headers TH,
fa_adjustments AD
WHERE BO.book_type_code = PARM.p_book_type_code
AND BO.asset_id = JGRX_FAREG.var.asset_id
AND BO.transaction_header_id_in <> NVL(X_transaction_id_initial, 0)
AND BO.book_type_code = TH.book_type_code
AND BO.asset_id = TH.asset_id
AND BO.transaction_header_id_in = TH.transaction_header_id
AND TH.TRANSACTION_TYPE_CODE = 'ADDITION'
AND AD.transaction_header_id = TH.transaction_header_id
AND AD.source_type_code = TH.TRANSACTION_TYPE_CODE
AND AD.book_type_code = TH.book_type_code
AND AD.asset_id = TH.asset_id
AND AD.adjustment_type = 'COST'
AND AD.debit_credit_flag = 'CR'
AND AD.adjustment_amount < 0
AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
AND PARM.p_end_period_to_date;
SELECT SUM(AD.adjustment_amount)
INTO X_manual_adjustment_minus
FROM fa_transaction_headers TH,
fa_adjustments AD
WHERE AD.transaction_header_id = TH.transaction_header_id
AND AD.book_type_code = TH.book_type_code
AND AD.asset_id = TH.asset_id
AND TH.transaction_type_code = 'ADJUSTMENT'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND AD.adjustment_type = 'COST'
AND AD.debit_credit_flag = 'CR'
AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
AND PARM.p_end_period_to_date;
SELECT SUM(AD.adjustment_amount)
INTO X_revaluation_minus
FROM fa_transaction_headers TH,
fa_adjustments AD
WHERE AD.transaction_header_id = TH.transaction_header_id
AND AD.book_type_code = TH.book_type_code
AND AD.asset_id = TH.asset_id
AND TH.transaction_type_code = 'REVALUATION'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND AD.adjustment_type = 'COST'
AND AD.debit_credit_flag = 'CR'
AND TH.transaction_date_entered BETWEEN PARM.p_begin_period_from_date
AND PARM.p_end_period_to_date;
SELECT SUM(RE.cost_retired)
INTO X_retirements
FROM fa_transaction_headers TH,
fa_retirements RE
WHERE RE.transaction_header_id_in = TH.transaction_header_id
AND RE.book_type_code = TH.book_type_code
AND RE.asset_id = TH.asset_id
AND (TH.TRANSACTION_TYPE_CODE = 'PARTIAL RETIREMENT' OR
TH.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT' )
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND RE.date_retired BETWEEN PARM.p_begin_period_from_date /* changed Transaction Date to Retirement Date */
AND PARM.p_end_period_to_date;
SELECT SUM(DECODE(debit_credit_flag, 'DR', adjustment_amount, 0)) -
SUM(DECODE(debit_credit_flag, 'CR', adjustment_amount, 0))
INTO JGRX_FAREG.var.revaluation_initial
FROM fa_adjustments
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JGRX_FAREG.var.asset_id
AND source_type_code = 'REVALUATION'
AND adjustment_type = 'COST'
AND period_counter_adjusted <= (PARM.p_begin_period_counter -1);
SELECT SUM(DECODE(debit_credit_flag, 'DR', adjustment_amount, 0)) -
SUM(DECODE(debit_credit_flag, 'CR', adjustment_amount, 0))
INTO JGRX_FAREG.var.revaluation_final
FROM fa_adjustments
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JGRX_FAREG.var.asset_id
AND source_type_code = 'REVALUATION'
AND adjustment_type = 'COST'
AND period_counter_adjusted <= PARM.p_end_period_counter;
SELECT SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',AD.adjustment_amount,0)),
SUM(DECODE(DEBIT_CREDIT_FLAG,'CR',AD.adjustment_amount,0))
INTO JGRX_FAREG.var.revaluation_increase,JGRX_FAREG.var.revaluation_decrease
FROM fa_adjustments AD
WHERE AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.source_type_code = 'REVALUATION'
AND AD.adjustment_type = 'COST'
AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter ;
SELECT (NVL(deprn_reserve,0) - NVL(bonus_deprn_reserve,0))
INTO JGRX_FAREG.var.deprn_reserve_initial
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JGRX_FAREG.var.asset_id
AND period_counter = (select max(period_counter)
from fa_deprn_summary
where period_counter <= (PARM.p_begin_period_counter-1)
and asset_id= JGRX_FAREG.var.asset_id
and book_type_code = PARM.p_book_type_code );
SELECT (NVL(deprn_reserve,0) - NVL(bonus_deprn_reserve,0))
INTO JGRX_FAREG.var.deprn_reserve_final
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JGRX_FAREG.var.asset_id
AND period_counter =
(select max(period_counter)
from fa_deprn_summary
where period_counter <= PARM.p_end_period_counter
and asset_id= JGRX_FAREG.var.asset_id
and book_type_code = PARM.p_book_type_code );
SELECT SUM((NVL(deprn_amount,0) - NVL(bonus_deprn_amount,0)))
INTO X_ord_deprn
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JGRX_FAREG.var.asset_id
AND period_counter BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT abs(SUM(DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0)))
INTO X_reinstatements
FROM fa_adjustments AD
WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.debit_credit_flag = 'CR'
AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT SUM(adjustment_amount)
INTO X_tax_re_adjustment_plus
FROM fa_adjustments AD
WHERE AD.source_type_code = 'TAX'
AND AD.adjustment_type = 'RESERVE'
AND AD.debit_credit_flag = 'CR'
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.period_counter_created BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT abs(SUM(DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0)))
INTO X_retirements
FROM fa_adjustments AD
WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.debit_credit_flag = 'DR'
AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT abs(SUM(DECODE(adjustment_type,'EXPENSE',adjustment_amount,0)) -
SUM(DECODE(adjustment_type,'BONUS EXPENSE',adjustment_amount,0)))
INTO X_financ_adjustment_minus
FROM fa_adjustments AD
WHERE AD.source_type_code = 'DEPRECIATION'
AND AD.adjustment_amount < 0
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT SUM(adjustment_amount)
INTO X_tax_re_adjustment_minus
FROM fa_adjustments AD
WHERE AD.source_type_code = 'TAX'
AND AD.adjustment_type = 'RESERVE'
AND AD.debit_credit_flag = 'DR'
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.period_counter_created BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT NVL(bonus_deprn_reserve,0)
INTO JGRX_FAREG.var.bonus_reserve_initial
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JGRX_FAREG.var.asset_id
AND period_counter =
(SELECT max(period_counter)
FROM fa_deprn_summary
WHERE period_counter <= (PARM.p_begin_period_counter-1)
AND asset_id= JGRX_FAREG.var.asset_id
AND book_type_code = PARM.p_book_type_code );
SELECT NVL(bonus_deprn_reserve,0)
INTO JGRX_FAREG.var.bonus_reserve_final
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JGRX_FAREG.var.asset_id
AND period_counter =
(SELECT max(period_counter)
FROM fa_deprn_summary
WHERE period_counter <= PARM.p_end_period_counter
AND asset_id= JGRX_FAREG.var.asset_id
AND book_type_code = PARM.p_book_type_code );
SELECT sum(bonus_deprn_amount)
INTO X_bonus_deprn
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JGRX_FAREG.var.asset_id
AND period_counter BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT SUM(adjustment_amount)
INTO X_reinstatements
FROM fa_adjustments AD
WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.adjustment_type = 'BONUS RESERVE'
AND AD.debit_credit_flag = 'CR'
AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT ABS(SUM(adjustment_amount))
INTO X_retirements
FROM fa_adjustments AD
WHERE AD.source_type_code in ('RETIREMENT','REVALUATION')
AND AD.adjustment_type = 'BONUS RESERVE'
AND AD.debit_credit_flag = 'DR'
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT ABS(SUM(adjustment_amount))
INTO X_financ_adjustment_minus
FROM fa_adjustments AD
WHERE AD.source_type_code = 'DEPRECIATION'
AND AD.adjustment_type = 'BONUS EXPENSE'
AND AD.adjustment_amount < 0
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JGRX_FAREG.var.asset_id
AND AD.period_counter_adjusted BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT start_date,
end_date,
deprn_calendar,
prorate_calendar
INTO X_fiscal_year_start_date,
X_fiscal_year_end_date,
X_deprn_calendar,
X_prorate_calendar
FROM fa_fiscal_year FY,
fa_book_controls BC
WHERE FY.fiscal_year = X_fiscal_year
AND FY.fiscal_year_name = BC.fiscal_year_name
AND BC.book_type_code = PARM.p_book_type_code;
SELECT (bonus_rate*100)
INTO JGRX_FAREG.var.bonus_rate
FROM fa_bonus_rates
WHERE bonus_rule = JGRX_FAREG.var.bonus_rule
AND X_year BETWEEN start_year
AND end_year;
SELECT TH.transaction_type_code,
TH.transaction_header_id,
BO.date_placed_in_service,
BO.cost
FROM fa_transaction_headers TH,
fa_books BO
WHERE BO.transaction_header_id_in = TH.transaction_header_id
AND BO.book_type_code = TH.book_type_code
AND BO.asset_id = TH.asset_id
AND TH.transaction_type_code = 'ADDITION'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
AND PARM.p_end_period_to_date;
Insert_transaction(p_transaction_date => c_addition.date_placed_in_service,
p_transaction_number => c_addition.transaction_header_id,
p_transaction_code => c_addition.transaction_type_code,
p_transaction_amount => c_addition.cost);
SELECT TH.transaction_type_code,
TH.transaction_header_id,
TH.transaction_date_entered,
decode(debit_credit_flag,'CR',(-1*AD.ADJUSTMENT_AMOUNT),AD.ADJUSTMENT_AMOUNT) ADJUSTMENT_AMOUNT
FROM fa_transaction_headers TH,
fa_ADJUSTMENTS AD
WHERE AD.transaction_header_id = TH.transaction_header_id
AND AD.book_type_code = TH.book_type_code
AND AD.asset_id = TH.asset_id
AND TH.transaction_type_code = 'ADJUSTMENT'
AND AD.source_type_code = TH.transaction_type_code
AND AD.adjustment_type = 'COST'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
AND PARM.p_end_period_to_date;
Insert_transaction(p_transaction_date => c_adjustment.transaction_date_entered,
p_transaction_number => c_adjustment.transaction_header_id,
p_transaction_code => c_adjustment.transaction_type_code,
p_transaction_amount => c_adjustment.adjustment_amount);
SELECT TH.transaction_type_code,
TH.transaction_header_id,
th.transaction_date_entered,
RE.cost_retired
FROM fa_transaction_headers TH,
fa_retirements RE
WHERE (RE.transaction_header_id_in = TH.transaction_header_id
OR RE.transaction_header_id_out = TH.transaction_header_id)
AND RE.book_type_code = TH.book_type_code
AND RE.asset_id = TH.asset_id
AND TH.TRANSACTION_TYPE_CODE IN ('PARTIAL RETIREMENT','FULL RETIREMENT','REINSTATEMENT') -- AND RE.STATUS = 'PROCESSED'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND th.transaction_date_entered BETWEEN X_fiscal_year_start_date
AND PARM.p_end_period_to_date;
Insert_transaction(p_transaction_date => c_retirement.transaction_date_entered,
p_transaction_number => c_retirement.transaction_header_id,
p_transaction_code => c_retirement.transaction_type_code,
p_transaction_amount => c_retirement.cost_retired);
SELECT TH.transaction_type_code,
TH.transaction_header_id,
TH.transaction_date_entered,
decode(debit_credit_flag,'CR',(-1*sum(AD.ADJUSTMENT_AMOUNT)),sum(AD.ADJUSTMENT_AMOUNT)) ADJUSTMENT_AMOUNT
-- SUM(AD.adjustment_amount) adjustment_amount
FROM fa_transaction_headers TH,
fa_adjustments AD
WHERE AD.transaction_header_id = TH.transaction_header_id
AND AD.book_type_code = TH.book_type_code
AND AD.asset_id = TH.asset_id
AND AD.adjustment_type = 'COST'
AND TH.TRANSACTION_TYPE_CODE = 'REVALUATION'
AND TH.book_type_code = PARM.p_book_type_code
AND TH.asset_id = JGRX_FAREG.var.asset_id
AND TH.transaction_date_entered BETWEEN X_fiscal_year_start_date
AND PARM.p_end_period_to_date
GROUP BY TH.transaction_type_code,
TH.transaction_header_id,
TH.transaction_date_entered,debit_credit_flag;
Insert_transaction(p_transaction_date => c_revaluation.transaction_date_entered,
p_transaction_number => c_revaluation.transaction_header_id,
p_transaction_code => c_revaluation.transaction_type_code,
p_transaction_amount => c_revaluation.adjustment_amount);
| Insert_transaction |
+====================================================================*/
PROCEDURE Insert_transaction( p_transaction_date DATE,
p_transaction_number NUMBER,
p_transaction_code VARCHAR2,
p_transaction_amount NUMBER)
IS
BEGIN
FA_RX_UTIL_PKG.debug('JGRX_FAREG.Insert_transaction()+');
INSERT INTO jg_zz_fa_reg_itf(
request_id,
organization_name,
functional_currency_code,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
major_category,
minor_category,
deprn_rate,
starting_deprn_year,
date_placed_in_service,
asset_heading,
asset_number,
description,
parent_asset_id,
parent_asset_number,
asset_cost_orig,
bonus_rate,
invoice_number,
supplier_name,
cost_account,
expense_account,
reserve_account,
bonus_deprn_account,
bonus_reserve_account,
asset_cost_initial,
asset_cost_increase,
asset_cost_decrease,
asset_cost_final,
revaluation_initial,
revaluation_increase,
revaluation_decrease,
revaluation_final,
deprn_reserve_initial,
deprn_reserve_increase,
deprn_reserve_decrease,
deprn_reserve_final,
bonus_reserve_initial,
bonus_reserve_increase,
bonus_reserve_decrease,
bonus_reserve_final,
net_book_value_initial,
net_book_value_increase,
net_book_value_decrease,
net_book_value_final,
transaction_date,
transaction_number,
transaction_code,
transaction_amount,
sales_amount,
cost_retired,
deprn_reserve,
bonus_reserve,
net_book_value,
gain_loss,
date_retired,
initial_heading, -- 09/08/00 AFERRARA
variation_heading, -- 09/08/00 AFERRARA
final_heading, -- 09/08/00 AFERRARA
asset_variation, -- 09/08/00 AFERRARA
reval_variation, -- 09/08/00 AFERRARA
deprn_variation, -- 09/08/00 AFERRARA
bonus_variation, -- 09/08/00 AFERRARA
netbo_variation, -- 09/08/00 AFERRARA
revaluation_total -- 09/08/00 AFERRARA
)
VALUES(
X_request_id,
JGRX_FAREG.var.organization_name,
JGRX_FAREG.var.functional_currency_code,
X_last_update_date,
X_last_updated_by,
X_last_update_login,
X_creation_date,
X_created_by,
JGRX_FAREG.var.major_category,
JGRX_FAREG.var.minor_category,
JGRX_FAREG.var.deprn_rate,
JGRX_FAREG.var.starting_deprn_year,
JGRX_FAREG.var.date_placed_in_service,
JGRX_FAREG.var.asset_heading,
JGRX_FAREG.var.asset_number,
JGRX_FAREG.var.description,
JGRX_FAREG.var.parent_asset_id,
JGRX_FAREG.var.parent_asset_number,
JGRX_FAREG.var.asset_cost_orig,
JGRX_FAREG.var.bonus_rate,
JGRX_FAREG.var.invoice_number,
JGRX_FAREG.var.supplier_name,
JGRX_FAREG.var.cost_account,
JGRX_FAREG.var.expense_account,
JGRX_FAREG.var.reserve_account,
JGRX_FAREG.var.bonus_deprn_account,
JGRX_FAREG.var.bonus_reserve_account,
JGRX_FAREG.var.asset_cost_initial,
JGRX_FAREG.var.asset_cost_increase,
JGRX_FAREG.var.asset_cost_decrease,
JGRX_FAREG.var.asset_cost_final,
JGRX_FAREG.var.revaluation_initial,
JGRX_FAREG.var.revaluation_increase,
JGRX_FAREG.var.revaluation_decrease,
JGRX_FAREG.var.revaluation_final,
JGRX_FAREG.var.deprn_reserve_initial,
JGRX_FAREG.var.deprn_reserve_increase,
JGRX_FAREG.var.deprn_reserve_decrease,
JGRX_FAREG.var.deprn_reserve_final,
JGRX_FAREG.var.bonus_reserve_initial,
JGRX_FAREG.var.bonus_reserve_increase,
JGRX_FAREG.var.bonus_reserve_decrease,
JGRX_FAREG.var.bonus_reserve_final,
JGRX_FAREG.var.net_book_value_initial,
JGRX_FAREG.var.net_book_value_increase,
JGRX_FAREG.var.net_book_value_decrease,
JGRX_FAREG.var.net_book_value_final,
p_transaction_date,
p_transaction_number,
p_transaction_code,
p_transaction_amount,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
JGRX_FAREG.var.initial_heading, -- 09/08/00 AFERRARA
JGRX_FAREG.var.variation_heading, -- 09/08/00 AFERRARA
JGRX_FAREG.var.final_heading, -- 09/08/00 AFERRARA
JGRX_FAREG.var.asset_variation, -- 09/08/00 AFERRARA
JGRX_FAREG.var.reval_variation, -- 09/08/00 AFERRARA
JGRX_FAREG.var.deprn_variation, -- 09/08/00 AFERRARA
JGRX_FAREG.var.bonus_variation, -- 09/08/00 AFERRARA
JGRX_FAREG.var.netbo_variation, -- 09/08/00 AFERRARA
JGRX_FAREG.var.revaluation_total -- 09/08/00 AFERRARA
);
FA_RX_UTIL_PKG.debug('JGRX_FAREG.Insert_transaction()-');
END Insert_transaction;