[Home] [Help]
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 := 'JG_RX_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 = METH.method_code' ||
' AND NVL(BO.life_in_months,-99) = NVL(METH.life_in_months,-99)';
SELECT number_per_fiscal_year
INTO X_number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = X_deprn_calendar;
SELECT trim(padded_concatenated_segments)
INTO JG_RX_FAREG.var.asset_key_flexfield
FROM FA_ASSET_KEYWORDS_KFV
WHERE CODE_COMBINATION_ID=JG_RX_FAREG.var.asset_key_ccid;
' 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 number_per_fiscal_year
INTO X_number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = X_deprn_calendar;
SELECT trim(padded_concatenated_segments)
INTO JG_RX_FAREG.var.asset_key_flexfield
FROM FA_ASSET_KEYWORDS_KFV
WHERE CODE_COMBINATION_ID=JG_RX_FAREG.var.asset_key_ccid;
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',decode(debit_credit_flag,'DR',adjustment_amount),0))
-(DECODE(adjustment_type,'BONUS RESERVE',decode(debit_credit_flag,'DR',adjustment_amount),0)))
- SUM((DECODE(adjustment_type,'RESERVE',decode(debit_credit_flag,'CR',adjustment_amount),0))
-(DECODE(adjustment_type,'BONUS RESERVE',decode(debit_credit_flag,'CR',adjustment_amount),0)))
INTO JG_RX_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 = JG_RX_FAREG.var.asset_id
--AND AD.debit_credit_flag = 'DR'
AND transaction_header_id = JG_RX_FAREG.var.transaction_header_id;
SELECT SUM((DECODE(adjustment_type,'BONUS RESERVE',decode(debit_credit_flag,'DR',adjustment_amount),0))
-(DECODE(adjustment_type,'BONUS RESERVE',decode(debit_credit_flag,'CR',adjustment_amount),0)))
INTO JG_RX_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 = JG_RX_FAREG.var.asset_id
--AND AD.debit_credit_flag = 'DR'
AND transaction_header_id = JG_RX_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 = JG_RX_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 method_id
into X_METHOD_ID1
from fa_methods
where method_code = JG_RX_FAREG.var.deprn_method_code
and nvl (life_in_months, -999) = nvl (JG_RX_FAREG.var.Life_In_Months, -999);
SELECT ROUND(JG_RX_FAREG.var.life_in_months/12)
INTO X_life_of_asset
FROM dual;
SELECT fiscal_year
INTO X_current_fiscal_year
FROM fa_deprn_periods
WHERE book_type_code = PARM.p_book_type_code
AND period_name = PARM.p_period_from;
SELECT fy.fiscal_year
INTO X_prorate_fiscal_year
FROM fa_calendar_periods cp,
fa_fiscal_year fy,
fa_book_controls bc
WHERE fy.fiscal_year_name = bc.fiscal_year_name
AND cp.calendar_type = bc.prorate_calendar
AND bc.book_type_code = PARM.p_book_type_code
AND JG_RX_FAREG.var.prorate_date between
fy.start_date and fy.end_date
AND cp.start_date between fy.start_date and fy.end_date
AND cp.end_date between fy.start_date and fy.end_date
AND JG_RX_FAREG.var.prorate_date between
cp.start_date and cp.end_date;
SELECT rate
INTO JG_RX_FAREG.var.deprn_rate
FROM fa_rates
WHERE method_id = JG_RX_FAREG.var.method_id
AND year = X_year_of_life
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 JG_RX_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 distinct '||X_account_segment||' from '||
' fa_distribution_history dih,'||
' gl_code_combinations gcc'||
' where dih.asset_id ='||JG_RX_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 substr(invoice_number,1,79) ||' ' invoice_number,
substr(vendor_name,1,79) ||' ' vendor_name
from fa_asset_invoices ai,po_vendors ve,fa_invoice_transactions IT,
fa_book_controls fabc
where ai.po_vendor_id= ve.vendor_id and
ai.asset_id = JG_RX_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 asset_number
INTO X_parent_asset_number
FROM fa_additions
WHERE asset_id = JG_RX_FAREG.var.parent_asset_id;
SELECT company_name
INTO JG_RX_FAREG.var.organization_name
FROM fa_system_controls;
SELECT currency_code
INTO JG_RX_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 = JG_RX_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 JG_RX_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 = JG_RX_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 JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 JG_RX_FAREG.var.revaluation_initial
FROM fa_adjustments
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JG_RX_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 JG_RX_FAREG.var.revaluation_final
FROM fa_adjustments
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JG_RX_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 JG_RX_FAREG.var.revaluation_increase,JG_RX_FAREG.var.revaluation_decrease
FROM fa_adjustments AD
WHERE AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JG_RX_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 JG_RX_FAREG.var.deprn_reserve_initial
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JG_RX_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= JG_RX_FAREG.var.asset_id
and book_type_code = PARM.p_book_type_code );
SELECT (NVL(deprn_reserve,0) - NVL(bonus_deprn_reserve,0)),
ytd_deprn
INTO JG_RX_FAREG.var.deprn_reserve_final
, JG_RX_FAREG.var.ytd_deprn
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JG_RX_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= JG_RX_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 = JG_RX_FAREG.var.asset_id
AND period_counter BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT nvl(abs(SUM(DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0))),0)
INTO X_revaluation_cr
FROM fa_adjustments AD
WHERE AD.source_type_code = 'REVALUATION' -- bug 5208066 removed reinstatement
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JG_RX_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 nvl(abs(SUM(DECODE(adjustment_type,'RESERVE', DECODE(debit_credit_flag , 'CR' ,adjustment_amount, 'DR', (-1*adjustment_amount),0),0)) -
SUM(DECODE(adjustment_type,'BONUS RESERVE',DECODE(debit_credit_flag , 'CR' ,adjustment_amount, 'DR', (-1*adjustment_amount),0),0))),0)
INTO X_reinstatements
FROM fa_adjustments AD, fa_transaction_headers ft
WHERE AD.source_type_code = 'RETIREMENT'
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JG_RX_FAREG.var.asset_id
and ft.asset_id = ad.asset_id
and ft.book_type_code = ad.book_type_code
and ft.transaction_header_id = ad.transaction_header_id
and ft.transaction_type_code in ('REINSTATEMENT')
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 = JG_RX_FAREG.var.asset_id
AND AD.period_counter_created BETWEEN PARM.p_begin_period_counter
AND PARM.p_end_period_counter;
SELECT nvl(abs(SUM(DECODE(adjustment_type,'RESERVE',adjustment_amount,0)) -
SUM(DECODE(adjustment_type,'BONUS RESERVE',adjustment_amount,0))),0)
INTO X_revaluation_dr
FROM fa_adjustments AD
WHERE AD.source_type_code = 'REVALUATION' --bug 5208066 removed retirement
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JG_RX_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 nvl(abs(SUM(DECODE(adjustment_type,'RESERVE', DECODE(debit_credit_flag , 'DR' ,adjustment_amount, 'CR', (-1*adjustment_amount),0),0)) -
SUM(DECODE(adjustment_type,'BONUS RESERVE',DECODE(debit_credit_flag , 'DR' ,adjustment_amount, 'CR', (-1*adjustment_amount),0),0))),0)
INTO X_retirements
FROM fa_adjustments AD, fa_transaction_headers ft
WHERE AD.source_type_code = 'RETIREMENT'
AND AD.book_type_code = PARM.p_book_type_code
AND AD.asset_id = JG_RX_FAREG.var.asset_id
and ft.asset_id = ad.asset_id
and ft.book_type_code = ad.book_type_code
and ft.transaction_header_id = ad.transaction_header_id
and ft.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
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 = JG_RX_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 = JG_RX_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 JG_RX_FAREG.var.bonus_reserve_initial
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JG_RX_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= JG_RX_FAREG.var.asset_id
AND book_type_code = PARM.p_book_type_code );
SELECT NVL(bonus_deprn_reserve,0)
INTO JG_RX_FAREG.var.bonus_reserve_final
FROM fa_deprn_summary
WHERE book_type_code = PARM.p_book_type_code
AND asset_id = JG_RX_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= JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 number_per_fiscal_year
INTO X_number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = X_deprn_calendar;
SELECT (bonus_rate*100)
INTO JG_RX_FAREG.var.bonus_rate
FROM fa_bonus_rates
WHERE bonus_rule = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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 = JG_RX_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('JG_RX_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
--- Added for ECE Project
,depreciation_method
,life_in_months
,life_in_years
,prorate_convention
,tag_number
,serial_number
,asset_key_flexfield
-- Added to fix bug 3240485
,ytd_deprn
-- Added above to fix bug 3240485
)
VALUES(
X_request_id,
JG_RX_FAREG.var.organization_name,
JG_RX_FAREG.var.functional_currency_code,
X_last_update_date,
X_last_updated_by,
X_last_update_login,
X_creation_date,
X_created_by,
JG_RX_FAREG.var.major_category,
JG_RX_FAREG.var.minor_category,
JG_RX_FAREG.var.deprn_rate,
JG_RX_FAREG.var.starting_deprn_year,
JG_RX_FAREG.var.date_placed_in_service,
JG_RX_FAREG.var.asset_heading,
JG_RX_FAREG.var.asset_number,
JG_RX_FAREG.var.description,
JG_RX_FAREG.var.parent_asset_id,
JG_RX_FAREG.var.parent_asset_number,
JG_RX_FAREG.var.asset_cost_orig,
JG_RX_FAREG.var.bonus_rate,
JG_RX_FAREG.var.invoice_number,
JG_RX_FAREG.var.supplier_name,
JG_RX_FAREG.var.cost_account,
JG_RX_FAREG.var.expense_account,
JG_RX_FAREG.var.reserve_account,
JG_RX_FAREG.var.bonus_deprn_account,
JG_RX_FAREG.var.bonus_reserve_account,
JG_RX_FAREG.var.asset_cost_initial,
JG_RX_FAREG.var.asset_cost_increase,
JG_RX_FAREG.var.asset_cost_decrease,
JG_RX_FAREG.var.asset_cost_final,
JG_RX_FAREG.var.revaluation_initial,
JG_RX_FAREG.var.revaluation_increase,
JG_RX_FAREG.var.revaluation_decrease,
JG_RX_FAREG.var.revaluation_final,
JG_RX_FAREG.var.deprn_reserve_initial,
JG_RX_FAREG.var.deprn_reserve_increase,
JG_RX_FAREG.var.deprn_reserve_decrease,
JG_RX_FAREG.var.deprn_reserve_final,
JG_RX_FAREG.var.bonus_reserve_initial,
JG_RX_FAREG.var.bonus_reserve_increase,
JG_RX_FAREG.var.bonus_reserve_decrease,
JG_RX_FAREG.var.bonus_reserve_final,
JG_RX_FAREG.var.net_book_value_initial,
JG_RX_FAREG.var.net_book_value_increase,
JG_RX_FAREG.var.net_book_value_decrease,
JG_RX_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,
JG_RX_FAREG.var.initial_heading, -- 09/08/00 AFERRARA
JG_RX_FAREG.var.variation_heading, -- 09/08/00 AFERRARA
JG_RX_FAREG.var.final_heading, -- 09/08/00 AFERRARA
JG_RX_FAREG.var.asset_variation, -- 09/08/00 AFERRARA
JG_RX_FAREG.var.reval_variation, -- 09/08/00 AFERRARA
JG_RX_FAREG.var.deprn_variation, -- 09/08/00 AFERRARA
JG_RX_FAREG.var.bonus_variation, -- 09/08/00 AFERRARA
JG_RX_FAREG.var.netbo_variation, -- 09/08/00 AFERRARA
JG_RX_FAREG.var.revaluation_total -- 09/08/00 AFERRARA
-- Added for ECE Project
,JG_RX_FAREG.var.depriciation_method
,JG_RX_FAREG.var.life_in_months
,JG_RX_FAREG.var.life_in_years
,JG_RX_FAREG.var.prorate_convention
,JG_RX_FAREG.var.tag_number
,JG_RX_FAREG.var.serial_number
,JG_RX_FAREG.var.asset_key_flexfield
--Added to fix bug 3240485
,JG_RX_FAREG.var.ytd_deprn
-- Added above to fix bug 3240485
);
FA_RX_UTIL_PKG.debug('JG_RX_FAREG.Insert_transaction()-');
END Insert_transaction;