SELECT
AJ.BOOK_TYPE_CODE BOOK_TYPE,
--&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,
DP.FISCAL_YEAR FISCAL_YEAR,
CB.DEPRN_RESERVE_ACCT RESERVE,
AD.ASSET_NUMBER ASSET_NUMBER,
--&CAT_FLEX_ALL_SEG ASSET_CATEGORY,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY1,
AD.DESCRIPTION DESCRIPTION,
ROUND(sum(decode(aj.debit_credit_flag, 'CR',aj.adjustment_amount,aj.adjustment_amount
*(-1))* nvl(dh.units_assigned,ah.units) / ah.units),:PRECISION) adjustment,
TH.DATE_EFFECTIVE TRANSACTION_DATE,
TH.TRANSACTION_HEADER_ID TRX_HEADER
/* ,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_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY*/
FROM
FA_DEPRN_PERIODS DP,
FA_ADJUSTMENTS AJ,
FA_TRANSACTION_HEADERS TH,
FA_ADDITIONS AD,
FA_ASSET_HISTORY AH,
FA_CATEGORIES CAT,
FA_CATEGORY_BOOKS CB,
fa_books books,
fa_distribution_history dh,
gl_code_combinations dhcc
WHERE
AJ.book_type_code = :P_BOOK AND
AJ.source_type_code = 'TAX' AND
AJ.adjustment_type = 'RESERVE' AND
AJ.transaction_header_id = TH.transaction_header_id
AND
TH.date_effective >= :PERIOD1_POD AND
TH.date_effective <= nvl(:PERIOD2_PCD,sysdate)
AND
TH.book_type_code = :P_BOOK AND
TH.asset_id = AJ.asset_id AND
TH.transaction_type_code = 'TAX'
AND
AD.asset_id = TH.asset_id AND
AH.category_id = CAT.category_id
AND
AH.ASSET_ID = AD.ASSET_ID AND
AH.DATE_EFFECTIVE <= TH.DATE_EFFECTIVE AND
nvl(AH.DATE_INEFFECTIVE,sysdate+1) > TH.DATE_EFFECTIVE
AND
DP.book_type_code = TH.book_type_code AND
DP.period_counter = AJ.period_counter_adjusted
AND
CB.category_id = CAT.category_id AND
CB.book_type_code = TH.book_type_code
and
dhcc.code_combination_id = dh.code_combination_id
and
books.book_type_code = th.book_type_code and
books.asset_id = th.asset_id and
th.date_effective > books.date_effective and
th.date_effective <= nvl(books.date_ineffective,
th.date_effective)
and
dh.book_type_code(+) = :DISTRIBUTION_SOURCE_BOOK and
dh.asset_id(+) = th.asset_id and
th.date_effective > dh.date_effective and
th.date_effective <= nvl(dh.date_ineffective,
th.date_effective)
GROUP BY
AJ.BOOK_TYPE_CODE,
--&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'),
DP.FISCAL_YEAR,
CB.DEPRN_RESERVE_ACCT,
AD.ASSET_NUMBER,
--&CAT_FLEX_ALL_SEG,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
AD.DESCRIPTION,
TH.DATE_EFFECTIVE,
TH.TRANSACTION_HEADER_ID,
TH.transaction_date_entered
ORDER BY
DP.FISCAL_YEAR,
CB.DEPRN_RESERVE_ACCT,
AD.ASSET_NUMBER,
TH.transaction_date_entered |