SELECT
&ACCT_FLEX_BAL_SEG COMP_CODE,
UPS.MEANING ASSET_TYPE,
DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
CB.ASSET_COST_ACCT) ASSET_ACCOUNT,
&ACCT_FLEX_COST_SEG COST_CENTER,
ad.asset_number ASSET,
TH.TRANSACTION_HEADER_ID THID,
&CAT_FLEX_ALL_SEG CATEGORY,
ad.description DESCR,
round(sum(books_old.cost * nvl(dh.units_assigned,ah.units)/
ah.units), :PRECISION) OLD_COST,
round(sum(books_new.cost * nvl(dh.units_assigned,ah.units)/
ah.units), :PRECISION) NEW_COST,
round(sum((books_new.cost - books_old.cost)
* nvl(dh.units_assigned,ah.units)/ah.units), :PRECISION) CHANGE,
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_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR,
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_ASSET_HISTORY AH,
FA_ADDITIONS AD,
FA_CATEGORIES CAT,
FA_CATEGORY_BOOKS CB,
&LP_FA_BOOKS BOOKS_OLD, &LP_FA_BOOKS BOOKS_NEW, FA_LOOKUPS UPS,
&LP_FA_DEPRN_PERIODS DP, FA_DISTRIBUTION_HISTORY DH,
GL_CODE_COMBINATIONS DHCC,
FA_TRANSACTION_HEADERS TH
WHERE
DP.BOOK_TYPE_CODE = :P_BOOK AND
DP.PERIOD_COUNTER >= :PERIOD1_PC AND
DP.PERIOD_COUNTER <= NVL(:PERIOD2_PC, DP.PERIOD_COUNTER)
AND
TH.BOOK_TYPE_CODE = DP.BOOK_TYPE_CODE AND
TH.DATE_EFFECTIVE BETWEEN DP.PERIOD_OPEN_DATE AND
NVL(DP.PERIOD_CLOSE_DATE, SYSDATE) AND
TH.TRANSACTION_TYPE_CODE IN ('ADJUSTMENT','CIP ADJUSTMENT')
AND
BOOKS_OLD.TRANSACTION_HEADER_ID_OUT = TH.TRANSACTION_HEADER_ID AND
BOOKS_OLD.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
AND
BOOKS_NEW.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND
BOOKS_NEW.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
AND
AD.ASSET_ID = TH.ASSET_ID
AND
UPS.LOOKUP_TYPE = 'ASSET TYPE'
AND
CB.CATEGORY_ID = AH.CATEGORY_ID AND
CB.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
AND
CAT.CATEGORY_ID = CB.CATEGORY_ID
AND
AH.ASSET_ID = AD.ASSET_ID AND
AH.ASSET_TYPE = UPS.LOOKUP_CODE AND
TH.TRANSACTION_HEADER_ID >= AH.TRANSACTION_HEADER_ID_IN AND
TH.TRANSACTION_HEADER_ID < NVL(AH.TRANSACTION_HEADER_ID_OUT, TH.TRANSACTION_HEADER_ID + 1)
AND
th.asset_id = dh.asset_id and
:DISTRIBUTION_SOURCE_BOOK = dh.book_type_code and
TH.TRANSACTION_HEADER_ID >= DH.TRANSACTION_HEADER_ID_IN AND
TH.TRANSACTION_HEADER_ID < NVL(DH.TRANSACTION_HEADER_ID_OUT, TH.TRANSACTION_HEADER_ID + 1)
and
dh.code_combination_id = dhcc.code_combination_id
HAVING
round(sum(books_old.cost * nvl(dh.units_assigned,ah.units)/ah.units), :PRECISION)
!= round(sum(books_new.cost * nvl(dh.units_assigned,ah.units)/ah.units), :PRECISION)
GROUP BY
UPS.MEANING,
&ACCT_FLEX_BAL_SEG,
&ACCT_FLEX_COST_SEG,
DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
CB.ASSET_COST_ACCT),
ad.asset_number,
TH.TRANSACTION_HEADER_ID,
&CAT_FLEX_ALL_SEG,
ad.description,
--added
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') ,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', '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,5,6
ORDER BY D_COMP_CODE,ASSET_TYPE,ASSET_ACCOUNT,D_COST_CTR,ASSET,THID,D_CATEGORY,
DESCR,OLD_COST,NEW_COST,CHANGE |
