SELECT
--&ACCT_FLEX_BAL_SEG comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1,
falu.meaning asset_type,
decode (ah.asset_type,
'CIP', cb.cip_cost_acct,
cb.asset_cost_acct) account,
--&ACCT_FLEX_COST_SEG cost_center,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER1,
ad.asset_number,
ret.date_retired,
ad.asset_number || ' - ' || ad.description asset_num_desc,
th.transaction_type_code,
th.asset_id,
books.date_placed_in_service,
sum(decode(aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0) *
decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) cost,
sum(decode(aj.adjustment_type, 'NBV RETIRED', -1, 0) *
decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) nbv,
sum(decode(aj.adjustment_type, 'PROCEEDS CLR', 1, 'PROCEEDS', 1, 0) *
decode(aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0) *
aj.adjustment_amount) proceeds,
sum(decode(aj.adjustment_type, 'REMOVALCOST', -1, 0) *
decode(aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0) *
aj.adjustment_amount) removal,
sum(decode(aj.adjustment_type,'REVAL RSV RET',1,0)*
decode(aj.debit_credit_flag, 'DR',-1,'CR',1,0)*
aj.adjustment_amount) reval_rsv_ret,
th.transaction_header_id,
decode (th.transaction_type_code,
'REINSTATEMENT', '*','PARTIAL RETIREMENT','P',
to_char(null)) code,
/* fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, */
FA_FAS440_XMLP_PKG.gain_lossformula(sum ( decode ( aj.adjustment_type , 'NBV RETIRED' , - 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'PROCEEDS CLR' , 1 , 'PROCEEDS' , 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , 1 , 'CR' , - 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'REMOVALCOST' , - 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount ), sum ( decode ( aj.adjustment_type , 'REVAL RSV RET' , 1 , 0 ) * decode ( aj.debit_credit_flag , 'DR' , - 1 , 'CR' , 1 , 0 ) * aj.adjustment_amount )) Gain_Loss
FROM
fa_transaction_headers th,
fa_additions ad,
&lp_fa_books books,
&lp_fa_retirements ret,
&lp_fa_adjustments aj,
fa_distribution_history dh,
gl_code_combinations dhcc,
fa_asset_history ah,
fa_category_books cb,
fa_lookups falu
WHERE
th.date_effective >= :PERIOD1_POD AND
th.date_effective <= :PERIOD2_PCD AND
th.book_type_code = :P_BOOK AND
th.transaction_key = 'R'
AND
ret.book_type_code = :P_BOOK AND
ret.asset_id = books.asset_id AND
decode (th.transaction_type_code,
'REINSTATEMENT', ret.transaction_header_id_out,
ret.transaction_header_id_in) = th.transaction_header_id
AND
ad.asset_id = th.asset_id
AND
aj.asset_id = ret.asset_id AND
aj.book_type_code = :P_BOOK
and aj.adjustment_type not in (select 'PROCEEDS' from &lp_fa_adjustments aj1
where aj1.book_type_code = aj.book_type_code
and aj1.asset_id = aj.asset_id
and aj1.transaction_header_id = aj.transaction_header_id
and aj1.adjustment_type = 'PROCEEDS CLR')
AND aj.transaction_header_id = th.transaction_header_id
AND
ah.asset_id = ad.asset_id AND
ah.date_effective <= th.date_effective AND
nvl(ah.date_ineffective, th.date_effective+1)
> th.date_effective
AND
falu.lookup_code = ah.asset_type AND
falu.lookup_type = 'ASSET TYPE'
AND
books.transaction_header_id_out
= th.transaction_header_id AND
books.book_type_code = :P_BOOK AND
books.asset_id = ad.asset_id
AND
cb.category_id = ah.category_id AND
cb.book_type_code = :P_BOOK AND
dh.distribution_id = aj.distribution_id
AND th.asset_id = dh.asset_id
AND
dhcc.code_combination_id = dh.code_combination_id
GROUP BY
falu.meaning,
/*&ACCT_FLEX_BAL_SEG,
&ACCT_FLEX_COST_SEG,*/
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
th.transaction_type_code,
th.asset_id,
cb.asset_cost_acct,
cb.cip_cost_acct,
ad.asset_number,
ad.description,
books.date_placed_in_service,
ret.date_retired,
th.transaction_header_id,
ah.asset_type,
ret.gain_loss_amount
--ORDER BY 1,2,3,4,5,6
ORDER BY 1,2,3,4,8, 9, 5, 7, 10, 6, 11, 12, 13, 14, 15, 16, 17 |
