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_CODE,
th.book_type_code book,
th.transaction_type_code tt,
th.asset_id asset,
fy.fiscal_year fiscal_year,
cb.asset_cost_acct account,
ad.asset_number || ' - ' || ad.description no,
books.date_placed_in_service dt,
ret.date_retired dr,
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)
nbv,
Round(sum(decode(ret.units, NULL,
ret.proceeds_of_sale * (dh.units_assigned /ah.units),
nvl(ret.proceeds_of_sale, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
proceeds,
Round(sum(decode(ret.units, NULL,
nvl(ret.itc_recaptured, 0.00) * (dh.units_assigned /ah.units),
nvl(ret.itc_recaptured, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
itc,
Round(sum(decode(ret.units, NULL,
nvl(ret.gain_loss_amount,0.00)* (dh.units_assigned /ah.units),
nvl(ret.gain_loss_amount, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
gl,
decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
'*', NULL)
code
FROM
fa_asset_history ah,
fa_retirements ret,
fa_additions ad,
fa_books books,
fa_category_books cb,
fa_fiscal_year fy,
fa_transaction_headers th,
fa_distribution_history dh,
gl_code_combinations dhcc
WHERE
th.date_effective >= :PERIOD1_POD AND
th.date_effective <= :PERIOD2_PCD AND
th.book_type_code = UPPER(:P_BOOK) AND
th.transaction_type_code = 'FULL RETIREMENT'
AND
th.transaction_header_id = ret.transaction_header_id_in
AND
ad.asset_id = th.asset_id
AND
books.transaction_header_id_out = th.transaction_header_id AND
books.book_type_code = UPPER(:P_BOOK)AND books.asset_id = th.asset_id
AND
cb.category_id = ah.category_id AND
cb.book_type_code = UPPER(:P_BOOK) AND
ah.asset_id = ad.asset_id AND
ah.date_effective <= th.date_effective AND
(ah.date_ineffective is null OR
ah.date_ineffective > th.date_effective)
AND
books.date_placed_in_service between fy.start_date and fy.end_date
AND
fy.fiscal_year_name = :Fiscal_Year_Name AND
dh.book_type_code = :Distribution_Source_Book AND
dh.asset_id = th.asset_id 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
&ACCT_FLEX_BAL_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'),
th.book_type_code,
th.transaction_type_code,
th.asset_id,
fy.fiscal_year,
cb.asset_cost_acct,
ad.asset_number,
ad.description,
books.date_placed_in_service,
ret.date_retired,
decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
'*', NULL)
union
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_CODE,
th.book_type_code book,
th.transaction_type_code tt,
th.asset_id asset,
fy.fiscal_year fiscal_year,
cb.asset_cost_acct account,
ad.asset_number || ' - ' || ad.description no,
books.date_placed_in_service dt,
ret.date_retired dr,
ROUND(sum(decode(ret.units, NULL,
(-1) * ret.cost_retired * (dh.units_assigned /ah.units),
(-1) * ret.cost_retired * abs(dh.transaction_units) / ret.units)), :PRECISION)
cost,
ROUND(sum(decode(ret.units, NULL,
(-1) * ret.nbv_retired * (dh.units_assigned /ah.units),
(-1) * ret.nbv_retired * abs(dh.transaction_units) / ret.units)), :PRECISION)
nbv,
ROUND(sum(decode(ret.units, NULL,
(-1) * ret.proceeds_of_sale * (dh.units_assigned /ah.units),
(-1) * nvl(ret.proceeds_of_sale, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
proceeds,
ROUND(sum(decode(ret.units, NULL,
(-1) * nvl(ret.itc_recaptured, 0.00) * (dh.units_assigned /ah.units),
(-1) * nvl(ret.itc_recaptured, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
itc,
ROUND(sum(decode(ret.units, NULL,
(-1) * nvl(ret.gain_loss_amount,0.00)
* (dh.units_assigned /ah.units),
(-1) * nvl(ret.gain_loss_amount, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
gl,
decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
'*', NULL)
code
FROM
fa_asset_history ah,
fa_retirements ret,
fa_additions ad,
fa_books books,
fa_category_books cb,
fa_fiscal_year fy,
fa_transaction_headers th,
fa_distribution_history dh,
gl_code_combinations dhcc
WHERE
th.date_effective >= :PERIOD1_POD AND
th.date_effective <= :PERIOD2_PCD AND
th.book_type_code = UPPER(:P_BOOK) AND
th.transaction_type_code = 'REINSTATEMENT'
AND
th.transaction_header_id = ret.transaction_header_id_out
AND
ad.asset_id = th.asset_id
AND
books.transaction_header_id_out = th.transaction_header_id AND
books.book_type_code = UPPER(:P_BOOK)
AND books.asset_id = th.asset_id
AND
cb.category_id = ah.category_id AND
cb.book_type_code = UPPER(:P_BOOK)AND
ah.asset_id = ad.asset_id AND
ah.date_effective <= th.date_effective AND
(ah.date_ineffective is null OR
ah.date_ineffective > th.date_effective)
AND
books.date_placed_in_service between
fy.start_date and
fy.end_date
AND
fy.fiscal_year_name =:Fiscal_Year_Name AND
dh.book_type_code = :Distribution_Source_Book AND
dh.asset_id = th.asset_id 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
&ACCT_FLEX_BAL_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'),
th.book_type_code,
th.transaction_type_code,
th.asset_id,
fy.fiscal_year,
cb.asset_cost_acct,
ad.asset_number,
ad.description,
books.date_placed_in_service,
ret.date_retired,
decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
'*', NULL)
union
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_CODE,
th.book_type_code book,
th.transaction_type_code tt,
th.asset_id asset,
fy.fiscal_year fiscal_year,
cb.asset_cost_acct account,
ad.asset_number || ' - ' || ad.description no,
books.date_placed_in_service dt,
ret.date_retired dr,
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)
nbv,
ROUND(sum(decode(ret.units, NULL,
ret.proceeds_of_sale * (dh.units_assigned /ah.units),
nvl(ret.proceeds_of_sale, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
proceeds,
ROUND(sum(decode(ret.units, NULL,
nvl(ret.itc_recaptured, 0.00) * (dh.units_assigned /ah.units),
nvl(ret.itc_recaptured, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
itc,
ROUND(sum(decode(ret.units, NULL,
nvl(ret.gain_loss_amount,0.00)* (dh.units_assigned /ah.units),
nvl(ret.gain_loss_amount, 0.00) *
abs(dh.transaction_units) / ret.units)), :PRECISION)
gl,
decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT',
'*', NULL)
code
FROM
fa_asset_history ah,
fa_retirements ret,
fa_additions ad,
fa_books books,
fa_category_books cb,
fa_fiscal_year fy,
fa_transaction_headers th,
fa_distribution_history dh,
gl_code_combinations dhcc
WHERE
th.date_effective >= :PERIOD1_POD AND
th.date_effective <= :PERIOD2_PCD AND
th.book_type_code= UPPER(:P_BOOK) AND
th.transaction_type_code = 'PARTIAL RETIREMENT'
AND
th.transaction_header_id = ret.transaction_header_id_in
AND
ad.asset_id = th.asset_id
AND
books.transaction_header_id_out = th.transaction_header_id AND
books.book_type_code =UPPER(:P_BOOK)AND
books.asset_id = th.asset_id
AND
cb.category_id = ah.category_id AND
cb.book_type_code = UPPER(:P_BOOK)
AND
ah.asset_id = ad.asset_id AND
ah.date_effective <= th.date_effective AND
(ah.date_ineffective is null OR
ah.date_ineffective > th.date_effective)
AND
books.date_placed_in_service between
fy.start_date and
fy.end_date
AND
fy.fiscal_year_name =:Fiscal_Year_Name
AND dh.book_type_code = :Distribution_Source_Book and
dh.asset_id = th.asset_id 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
&ACCT_FLEX_BAL_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') ,
th.book_type_code,
th.transaction_type_code,
th.asset_id,
fy.fiscal_year,
cb.asset_cost_acct,
ad.asset_number,
ad.description,
books.date_placed_in_service,
ret.date_retired,
decode(TH.TRANSACTION_TYPE_CODE, 'REINSTATEMENT','*', NULL)
ORDER BY 1,2,6,7,8,9,10,11,12,13,14,15,16,3,4,5
--1,5,6,7 |
