FA Form 4797 - Gain From Disposition of 1250 Property - draft
Description
Categories: BI Publisher
Application: Assets
Source: Form 4797 - Gain From Disposition of 1250 Property Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS445_XML
DB package: FA_FAS445_XMLP_PKG
Source: Form 4797 - Gain From Disposition of 1250 Property Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS445_XML
DB package: FA_FAS445_XMLP_PKG
SELECT --nvl(&ACCT_FLEX_BAL_SEG,'None') COMP_CODE, nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),'None') COMP_CODE_DSP1, th.book_type_code BOOK, ad.asset_number ASSET, ad.asset_id ASSET_ID, ROUND(sum(decode(ret.units, NULL, nvl(ret.proceeds_of_sale,0.00)*(dh.units_assigned/ah.units), nvl(ret.proceeds_of_sale,0.00)*(abs(dh.transaction_units)/ret.units))),:PRECISION) SALE, ROUND(sum(decode(ret.units, NULL, ret.cost_retired * (dh.units_assigned/ah.units), ret.cost_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION) COST, ROUND(sum(decode(ret.units, NULL, ret.nbv_retired * (dh.units_assigned/ah.units), ret.nbv_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION) NBVR, ROUND(sum(Decode(Sign(ret.gain_loss_amount), 1, decode(ret.units, NULL, ret.gain_loss_amount * (dh.units_assigned/ah.units), ret.gain_loss_amount * (abs(dh.transaction_units)/ret.units)), 0.00)),:PRECISION) GLA, ROUND(sum(Decode(Sign(Months_Between(ret.date_retired, bk.date_placed_in_service) - 12), -1, decode(ret.units, NULL, (ret.cost_retired - ret.nbv_retired) * (dh.units_assigned/ah.units), (ret.cost_retired - ret.nbv_retired) * (abs(dh.transaction_units)/ret.units)), decode(ret.units, NULL, ((ret.cost_retired - ret.nbv_retired) - ret.stl_deprn_amount) * (dh.units_assigned/ah.units), ((ret.cost_retired - ret.nbv_retired) - ret.stl_deprn_amount) * (abs(dh.transaction_units)/ret.units)))),:PRECISION) XCESS, ROUND(sum(Decode(Sign(ret.gain_loss_amount), 1, Decode (Sign(Months_Between(ret.date_retired, bk.date_placed_in_service)-12), -1, decode(ret.units, NULL, (ret.gain_loss_amount - Least(ret.gain_loss_amount, (ret.cost_retired-ret.nbv_retired))) * (dh.units_assigned/ah.units), (ret.gain_loss_amount - Least(ret.gain_loss_amount, (ret.cost_retired-ret.nbv_retired))) * (abs(dh.transaction_units)/ret.units)), decode(ret.units, NULL, (ret.gain_loss_amount - Least(ret.gain_loss_amount, ( (ret.cost_retired-ret.nbv_retired) - decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired, '0',ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) ) + ( (ret.cost_retired - ret.nbv_retired) - ((ret.cost_retired - ret.nbv_retired) - decode(ret.stl_deprn_amount, NULL,ret.cost_retired - ret.nbv_retired, '0', ret.cost_retired - ret.nbv_retired, ret.stl_deprn_amount))) *0.2 )) * (dh.units_assigned/ah.units), (ret.gain_loss_amount - Least(ret.gain_loss_amount, ((ret.cost_retired-ret.nbv_retired) - decode(ret.stl_deprn_amount,NULL,ret.cost_retired - ret.nbv_retired, '0', ret.cost_retired - ret.nbv_retired, ret.stl_deprn_amount) ))) * (abs(dh.transaction_units)/ret.units))), 0.00)),:PRECISION) CGAIN, ROUND(sum(Decode(Sign(gain_loss_amount), 1,Decode (Sign(Months_Between(ret.date_retired, bk.date_placed_in_service)-12), -1, decode(ret.units, NULL, Least(ret.gain_loss_amount,(ret.cost_retired-ret.nbv_retired)) * (dh.units_assigned/ah.units), Least(ret.gain_loss_amount,(ret.cost_retired-ret.nbv_retired)) * (abs(dh.transaction_units)/ret.units)), decode(ret.units, NULL, (Least(ret.gain_loss_amount, ( ((ret.cost_retired-ret.nbv_retired) - decode(ret.stl_deprn_amount,NULL, ret.cost_retired-ret.nbv_retired, '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) )+ ( (ret.cost_retired - ret.nbv_retired) - ( ret.cost_retired - ret.nbv_retired - decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired, '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) ) ) *.20) ) ) * (dh.units_assigned/ah.units), (Least(ret.gain_loss_amount, ((ret.cost_retired-ret.nbv_retired) - decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired, '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) ))+(( decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired, '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) )*.20)) * (abs(dh.transaction_units)/ret.units))), 0.00)),:PRECISION) ORD_INC, ROUND(sum(Decode (Sign(ret.gain_loss_amount), -1, decode(ret.units, NULL, ret.gain_loss_amount * (dh.units_assigned/ah.units), ret.gain_loss_amount * (abs(dh.transaction_units)/ret.units)), 0.00)),:PRECISION) CLOSS, Decode (Sign(ret.gain_loss_amount), -1, :LOSS_NLS, :GAIN_NLS) GAIN_LOSS --Added the below functions during DT Fix ,FA_FAS445_XMLP_PKG.d_excess_1969formula(th.book_type_code, ad.asset_id, :BOOK_CLASS, ROUND(sum(Decode(Sign(Months_Between(ret.date_retired, bk.date_placed_in_service) - 12), -1, decode(ret.units, NULL, (ret.cost_retired - ret.nbv_retired) * (dh.units_assigned/ah.units), (ret.cost_retired - ret.nbv_retired) * (abs(dh.transaction_units)/ret.units)), decode(ret.units, NULL, ((ret.cost_retired - ret.nbv_retired) - ret.stl_deprn_amount) * (dh.units_assigned/ah.units), ((ret.cost_retired - ret.nbv_retired) - ret.stl_deprn_amount) * (abs(dh.transaction_units)/ret.units)))),:PRECISION), ROUND(sum(decode(ret.units, NULL, ret.cost_retired * (dh.units_assigned/ah.units), ret.cost_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION), ROUND(sum(decode(ret.units, NULL, ret.nbv_retired * (dh.units_assigned/ah.units), ret.nbv_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION) ) D_EXCESS_1969 ,FA_FAS445_XMLP_PKG.d_excess_1962formula(th.book_type_code, ad.asset_id, :BOOK_CLASS, ROUND(sum(decode(ret.units, NULL, ret.cost_retired * (dh.units_assigned/ah.units), ret.cost_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION), ROUND(sum(decode(ret.units, NULL, ret.nbv_retired * (dh.units_assigned/ah.units), ret.nbv_retired * (abs(dh.transaction_units)/ret.units))),:PRECISION), ROUND(sum(Decode(Sign(ret.gain_loss_amount), 1, decode(ret.units, NULL, ret.gain_loss_amount * (dh.units_assigned/ah.units), ret.gain_loss_amount * (abs(dh.transaction_units)/ret.units)), 0.00)),:PRECISION), ROUND(sum(Decode(Sign(ret.gain_loss_amount), 1, Decode (Sign(Months_Between(ret.date_retired, bk.date_placed_in_service)-12), -1, decode(ret.units, NULL, (ret.gain_loss_amount - Least(ret.gain_loss_amount, (ret.cost_retired-ret.nbv_retired))) * (dh.units_assigned/ah.units), (ret.gain_loss_amount - Least(ret.gain_loss_amount, (ret.cost_retired-ret.nbv_retired))) * (abs(dh.transaction_units)/ret.units)), decode(ret.units, NULL, (ret.gain_loss_amount - Least(ret.gain_loss_amount, ( (ret.cost_retired-ret.nbv_retired) - decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired, '0',ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) ) + ( (ret.cost_retired - ret.nbv_retired) - ((ret.cost_retired - ret.nbv_retired) - decode(ret.stl_deprn_amount, NULL,ret.cost_retired - ret.nbv_retired, '0', ret.cost_retired - ret.nbv_retired, ret.stl_deprn_amount))) *0.2 )) * (dh.units_assigned/ah.units), (ret.gain_loss_amount - Least(ret.gain_loss_amount, ((ret.cost_retired-ret.nbv_retired) - decode(ret.stl_deprn_amount,NULL,ret.cost_retired - ret.nbv_retired, '0', ret.cost_retired - ret.nbv_retired, ret.stl_deprn_amount) ))) * (abs(dh.transaction_units)/ret.units))), 0.00)),:PRECISION), ROUND(sum(Decode(Sign(gain_loss_amount), 1,Decode (Sign(Months_Between(ret.date_retired, bk.date_placed_in_service)-12), -1, decode(ret.units, NULL, Least(ret.gain_loss_amount,(ret.cost_retired-ret.nbv_retired)) * (dh.units_assigned/ah.units), Least(ret.gain_loss_amount,(ret.cost_retired-ret.nbv_retired)) * (abs(dh.transaction_units)/ret.units)), decode(ret.units, NULL, (Least(ret.gain_loss_amount, ( ((ret.cost_retired-ret.nbv_retired) - decode(ret.stl_deprn_amount,NULL, ret.cost_retired-ret.nbv_retired, '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) )+ ( (ret.cost_retired - ret.nbv_retired) - ( ret.cost_retired - ret.nbv_retired - decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired, '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) ) ) *.20) ) ) * (dh.units_assigned/ah.units), (Least(ret.gain_loss_amount, ((ret.cost_retired-ret.nbv_retired) - decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired, '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) ))+(( decode(ret.stl_deprn_amount,NULL,ret.cost_retired-ret.nbv_retired, '0', ret.cost_retired-ret.nbv_retired, ret.stl_deprn_amount) )*.20)) * (abs(dh.transaction_units)/ret.units))), 0.00)),:PRECISION) ) D_EXCESS_1962 ,FA_FAS445_XMLP_PKG.P_SECTION_1231_GAIN_1962_p P_SECTION_1231_GAIN_1962 ,FA_FAS445_XMLP_PKG.P_ORDINARY_INCOME_1962_p P_ORDINARY_INCOME_1962 ,FA_FAS445_XMLP_PKG.P_EXCESS_1969_HIDE_p P_EXCESS_1969_HIDE ,FA_FAS445_XMLP_PKG.P_EXCESS_1969_p P_EXCESS_1969 --End of DT Fix FROM fa_additions ad, fa_books bk, fa_deprn_periods dp, fa_retirements ret, fa_system_controls sc, fa_transaction_headers th, fa_asset_history ah, fa_distribution_history dh, fa_category_book_defaults cbd, fa_book_controls bc, gl_code_combinations dhcc WHERE dp.book_type_code = UPPER(:P_BOOK) AND dp.period_counter >= :PERIOD1_PC AND dp.period_counter <= nvl(:PERIOD2_PC, dp.period_counter) AND th.date_effective >= dp.period_open_date AND th.date_effective <= nvl(dp.period_close_date,sysdate) AND th.book_type_code = UPPER(:P_BOOK) AND th.transaction_type_code in ('FULL RETIREMENT', 'PARTIAL RETIREMENT') AND ah.asset_id = th.asset_id AND ah.date_effective <= th.date_effective AND nvl(ah.date_ineffective,sysdate+1) > th.date_effective AND ret.transaction_header_id_in = th.transaction_header_id AND ((UPPER(ret.retirement_type_code) like '%SALE%') OR ((ret.retirement_type_code is NULL) AND (ret.proceeds_of_sale > 0))) AND ret.status = 'PROCESSED' AND bk.transaction_header_id_out = th.transaction_header_id AND bk.book_type_code = UPPER(:P_BOOK) AND cbd.category_id = ah.category_id AND cbd.book_type_code = th.book_type_code AND (bk.date_placed_in_service between cbd.start_dpis and nvl(cbd.end_dpis,sysdate)) AND months_between (ret.date_retired, bk.date_placed_in_service) > nvl(cbd.capital_gain_threshold,12) AND ad.asset_id = th.asset_id AND ad.property_1245_1250_code = '1250' AND dh.asset_id = th.asset_id AND dh.asset_id = th.asset_id AND dh.book_type_code = bc.book_type_code AND dh.book_type_code = bc.distribution_source_book AND (dh.retirement_id = ret.retirement_id OR (ret.date_effective >= dh.date_effective AND ret.date_effective < nvl(dh.date_ineffective,sysdate) AND ret.units is null)) AND dhcc.code_combination_id = dh.code_combination_id GROUP BY Sign(ret.gain_loss_amount), :Company_Name, :PERIOD1_PC , :PERIOD2_PC, --&ACCT_FLEX_BAL_SEG, fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), th.book_type_code, ad.asset_number, ad.asset_id ORDER BY /* nvl(&ACCT_FLEX_BAL_SEG,'None'), ad.asset_number*/ 2, 1, 13, 4, 3, 5, 6, 7, 8, 9, 10, 11, 12 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Book | LOV Oracle | ||
| From Period | LOV Oracle | ||
| To Period | LOV Oracle |