SELECT
--Start of DT Fix
--&ACCT_FLEX_BAL_SEG comp_code,
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') COMP_CODE_DSP1,
--End of DT Fix
LU.MEANING RETIREMENT_TYPE,
LU2.meaning term,
--Start of DT Fix
--&CAT_FLEX_ALL_SEG category,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY1,
--End of DT Fix
ad.asset_number || ' - ' || ad.description no,
books.date_placed_in_service dt,
ret.date_retired dr,
round(sum(decode(ret.units, NULL,
(decode(th.transaction_type_code, 'REINSTATEMENT',
(-1) * ret.cost_retired, ret.cost_retired)
* (dh.units_assigned /ah.units)),
(decode(th.transaction_type_code, 'REINSTATEMENT',
(-1) * ret.cost_retired, ret.cost_retired)
* abs(dh.transaction_units) / ret.units))),:PRECISION) cost,
--Start of DT Fix
--&LOC_FLEX_ALL_SEG location,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
--End of DT Fix
decode(th.transaction_type_code, 'REINSTATEMENT',
'*', null) code
FROM
fa_retirements ret,
fa_asset_history ah,
fa_additions ad,
fa_books books,
fa_transaction_headers th,
fa_distribution_history dh,
fa_lookups lu,
fa_lookups lu2,
fa_categories cat,
fa_locations loc,
gl_code_combinations dhcc
WHERE
books.book_type_code = :P_BOOK
AND books.transaction_header_id_out = th.transaction_header_id
AND books.asset_id = th.asset_id
AND cat.category_id = ah.category_id
AND ah.asset_id = ad.asset_id
AND ad.asset_id = th.asset_id
AND ah.date_effective <= th.date_effective
AND nvl(ah.date_ineffective, sysdate) > th.date_effective
AND loc.location_id = dh.location_id
AND dhcc.code_combination_id = dh.code_combination_id
AND (dh.retirement_id is null or :Distribution_Source_book != :P_book)
AND dh.book_type_code = :Distribution_Source_Book
AND (ret.date_effective >= dh.date_effective and
ret.date_effective < nvl(dh.date_ineffective,sysdate))
AND dh.asset_id = th.asset_id
AND ret.retirement_type_code = lu.lookup_code(+)
AND lu.lookup_type(+) = 'RETIREMENT'
AND ret.units is null
AND (lu2.lookup_type = 'ASSET TERM'
AND decode (sign (months_between (ret.date_retired,
books.date_placed_in_service)
- 12), 1, 'LONG', 'SHORT') = lu2.lookup_code)
AND decode(:P_RET_TYPE,null,nvl(ret.retirement_type_code,-1),:P_RET_TYPE) =
nvl(ret.retirement_type_code,-1)
AND th.transaction_header_id = ret.transaction_header_id_in
AND th.transaction_type_code like '%RETIREMENT'
AND th.date_effective >= :Period1_POD
AND th.date_effective <= :Period2_PCD
AND th.book_type_code = :P_BOOK
AND th.transaction_key = 'R'
group by
--&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'),
LU.MEANING,
LU2.MEANING,
th.transaction_type_code,
th.asset_id,
ad.asset_number,
ad.description,
books.date_placed_in_service,
ret.date_retired,
th.transaction_header_id,
decode(th.transaction_type_code, 'REINSTATEMENT', '*', null),
/*&CAT_FLEX_ALL_SEG,
&LOC_FLEX_ALL_SEG*/
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE')
UNION ALL
SELECT
--Start of DT Fix
--&ACCT_FLEX_BAL_SEG comp_code,
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') COMP_CODE_DSP1,
--End of DT Fix
LU.MEANING RETIREMENT_TYPE,
LU2.MEANING term,
--Start of DT Fix
--&CAT_FLEX_ALL_SEG category,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY1,
--End of DT Fix
ad.asset_number || ' - ' || ad.description no,
books.date_placed_in_service dt,
ret.date_retired dr,
round(sum(decode(ret.units, NULL,
(decode(th.transaction_type_code, 'REINSTATEMENT',
(-1) * ret.cost_retired, ret.cost_retired)
* (dh.units_assigned /ah.units)),
(decode(th.transaction_type_code, 'REINSTATEMENT',
(-1) * ret.cost_retired, ret.cost_retired)
* abs(dh.transaction_units) / ret.units))),:PRECISION) cost,
--Start of DT Fix
--&LOC_FLEX_ALL_SEG location,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
--End of DT Fix
decode(th.transaction_type_code, 'REINSTATEMENT',
'*', null) code
FROM
fa_retirements ret,
fa_asset_history ah,
fa_additions ad,
fa_lookups lu,
fa_lookups lu2,
fa_books books,
fa_transaction_headers th,
fa_distribution_history dh,
fa_categories cat,
fa_locations loc,
gl_code_combinations dhcc
WHERE
books.book_type_code = :P_BOOK
AND books.transaction_header_id_out = th.transaction_header_id
AND books.asset_id = th.asset_id
AND cat.category_id = ah.category_id
AND ah.asset_id = ad.asset_id
AND ad.asset_id = th.asset_id
AND ah.date_effective <= th.date_effective
AND nvl(ah.date_ineffective, sysdate) > th.date_effective
AND loc.location_id = dh.location_id
AND dhcc.code_combination_id = dh.code_combination_id
AND (dh.retirement_id is not null or :Distribution_Source_Book != :P_book)
AND dh.book_type_code = :Distribution_Source_Book
AND (ret.date_effective >= dh.date_effective and
ret.date_effective < nvl(dh.date_ineffective,sysdate))
AND dh.asset_id = th.asset_id
AND (ret.retirement_type_code = lu.lookup_code(+)
AND lu.lookup_type(+) = 'RETIREMENT')
AND (lu2.lookup_type = 'ASSET TERM'
AND decode (sign (months_between (ret.date_retired,
books.date_placed_in_service)
- 12), 1, 'LONG', 'SHORT') = lu2.lookup_code)
AND ret.units is not null
AND decode(:P_RET_TYPE,null,nvl(ret.retirement_type_code,-1),:P_RET_TYPE) =
nvl(ret.retirement_type_code,-1)
AND th.transaction_header_id = ret.transaction_header_id_out
AND th.transaction_type_code = 'REINSTATEMENT'
AND th.date_effective >= :Period1_POD
AND th.date_effective <= :Period2_PCD
AND th.book_type_code = :P_BOOK
AND th.transaction_key = 'R'
group by
--&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'),
LU.MEANING,
LU2.MEANING,
th.transaction_type_code,
th.asset_id,
ad.asset_number,
ad.description,
books.date_placed_in_service,
ret.date_retired,
th.transaction_header_id,
decode(th.transaction_type_code, 'REINSTATEMENT', '*', null),
/*&CAT_FLEX_ALL_SEG,
&LOC_FLEX_ALL_SEG*/
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE')
UNION ALL
SELECT
--Start of DT Fix
--&ACCT_FLEX_BAL_SEG comp_code,
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') COMP_CODE_DSP1,
--End of DT Fix
LU.MEANING RETIREMENT_TYPE,
LU2.MEANING term,
--Start of DT Fix
--&CAT_FLEX_ALL_SEG category,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY1,
--End of DT Fix
ad.asset_number || ' - ' || ad.description no,
books.date_placed_in_service dt,
ret.date_retired dr,
round(sum(decode(ret.units, NULL,
(decode(th.transaction_type_code, 'REINSTATEMENT',
(-1) * ret.cost_retired, ret.cost_retired)
* (dh.units_assigned /ah.units)),
(decode(th.transaction_type_code, 'REINSTATEMENT',
(-1) * ret.cost_retired, ret.cost_retired)
* abs(dh.transaction_units) / ret.units))),:PRECISION) cost,
--Start of DT Fix
--&LOC_FLEX_ALL_SEG location,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
--End of DT Fix
decode(th.transaction_type_code, 'REINSTATEMENT',
'*', null) code
FROM
fa_retirements ret,
fa_asset_history ah,
fa_additions ad,
fa_books books,
fa_lookups lu,
fa_lookups lu2,
fa_transaction_headers th,
fa_distribution_history dh,
fa_categories cat,
fa_locations loc,
gl_code_combinations dhcc
WHERE
books.book_type_code = :P_BOOK
AND books.transaction_header_id_out = th.transaction_header_id
AND books.asset_id = th.asset_id
AND cat.category_id = ah.category_id
AND ah.asset_id = ad.asset_id
AND ad.asset_id = th.asset_id
AND ah.date_effective <= th.date_effective
AND nvl(ah.date_ineffective, sysdate) > th.date_effective
AND loc.location_id = dh.location_id
AND dhcc.code_combination_id = dh.code_combination_id
AND dh.retirement_id is not null
AND dh.book_type_code = :Distribution_Source_Book
AND dh.retirement_id = ret.retirement_id
AND (ret.retirement_type_code = lu.lookup_code(+)
AND lu.lookup_type(+) = 'RETIREMENT')
AND (lu2.lookup_type = 'ASSET TERM'
AND decode (sign (months_between (ret.date_retired,
books.date_placed_in_service)
- 12), 1, 'LONG', 'SHORT') = lu2.lookup_code)
AND dh.asset_id = th.asset_id
AND ret.units is not null
AND decode(:P_RET_TYPE,null,nvl(ret.retirement_type_code,-1),:P_RET_TYPE) =
nvl(ret.retirement_type_code,-1)
AND th.transaction_header_id = ret.transaction_header_id_in
AND th.transaction_type_code like '%RETIREMENT'
AND th.date_effective >= :Period1_POD
AND th.date_effective <= :Period2_PCD
AND th.book_type_code = :P_BOOK
AND th.transaction_key = 'R'
group by
--&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'),
LU.MEANING,
LU2.MEANING,
th.transaction_type_code,
th.asset_id,
ad.asset_number,
ad.description,
books.date_placed_in_service,
ret.date_retired,
th.transaction_header_id,
decode(th.transaction_type_code, 'REINSTATEMENT', '*', null),
/*&CAT_FLEX_ALL_SEG,
&LOC_FLEX_ALL_SEG*/
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE')
ORDER BY
1,2,3,4,5 |