SELECT
NVL(&ACCT_FLEX_BAL_SEG,'NONE') COMP_CODE,
AD.ASSET_TYPE ASSET_TYPE,
&CAT_FLEX_ALL_SEG CATEGORY,
AD.ASSET_NUMBER ASSET_NUMBER,
AD.DESCRIPTION DESCRIPTION,
AD.SERIAL_NUMBER SERIAL_NUMBER,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
ROUND(SUM(BOOKS.COST *
NVL(DH.UNITS_ASSIGNED,AH.UNITS)/AH.UNITS), :PRECISION) COST,
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_DSP,
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_BOOK_CONTROLS BC, FA_BOOKS BOOKS,
FA_ADDITIONS AD,
FA_ASSET_HISTORY AH,
FA_CATEGORIES CAT,
FA_SYSTEM_CONTROLS SC,
FA_DISTRIBUTION_HISTORY DH,
GL_CODE_COMBINATIONS DHCC
WHERE
BOOKS.BOOK_TYPE_CODE = :P_BOOK AND
BOOKS.TRANSACTION_HEADER_ID_OUT IS NULL AND
BOOKS.CAPITALIZE_FLAG = 'YES' AND
BOOKS.DEPRECIATE_FLAG = 'NO' AND
BOOKS.PERIOD_COUNTER_FULLY_RETIRED is NULL
AND
AD.ASSET_ID = BOOKS.ASSET_ID
AND
CAT.CATEGORY_ID = AD.ASSET_CATEGORY_ID
AND BOOKS.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE AND BC.DISTRIBUTION_SOURCE_BOOK = DH.BOOK_TYPE_CODE
AND
BOOKS.ASSET_ID = DH.ASSET_ID(+) AND
DH.DATE_INEFFECTIVE IS NULL
AND
DHCC.CODE_COMBINATION_ID(+) = DH.CODE_COMBINATION_ID
AND
AH.ASSET_ID = BOOKS.ASSET_ID
AND
AH.DATE_INEFFECTIVE IS NULL
GROUP BY
NVL(&ACCT_FLEX_BAL_SEG,'NONE'),
AD.ASSET_TYPE,
&CAT_FLEX_ALL_SEG,
AD.ASSET_NUMBER,
AD.DESCRIPTION,
AD.SERIAL_NUMBER,
BOOKS.DATE_PLACED_IN_SERVICE
--added
,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') ,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
--ORDER BY 1,2,3,4
ORDER BY COMP_CODE_DSP,2,D_CATEGORY,4,5,6,7,8 |
