SELECT
ma.asset_number ASSET_NUMBER ,
MA.DESCRIPTION DESCRIPTION,
'Additions' TRANS_TYPE,
&ACCT_FLEX_BAL_SEG COMPANY,
&ACCT_FLEX_COST_SEG COST_CTR,
decode(MA.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) ACCOUNT,
ma.mass_addition_id,
ma.sum_units,
ma.fixed_assets_units,
sum(MA.FIXED_ASSETS_COST * FAMA.UNITS) COST,
MA.REVIEWER_COMMENTS COMMENTS,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company', 'SQLGL', 'GL#', glcc.CHART_OF_ACCOUNTS_ID, NULL, glcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMPANY,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', glcc.CHART_OF_ACCOUNTS_ID, NULL, glcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR,
FA_FAS824_XMLP_PKG.new_costformula(ma.sum_units, sum ( MA.FIXED_ASSETS_COST * FAMA.UNITS ), ma.fixed_assets_units, ma.mass_addition_id) NEW_COST
FROM
GL_CODE_COMBINATIONS GLCC,
FA_CATEGORY_BOOKS CB,
FA_MASSADD_DISTRIBUTIONS FAMA,
FA_MASS_ADDITIONS MA2,
FA_MASS_ADDITIONS MA
WHERE
Decode(MA.Merged_Code, 'MC', MA.Merge_Parent_Mass_Additions_ID,
MA.Mass_Addition_ID) = MA2.Mass_Addition_ID AND
MA.BOOK_TYPE_CODE = :P_BOOK AND
MA.POSTING_STATUS = 'POSTED' AND
MA2.POSTING_STATUS = 'POSTED' AND
MA2.BOOK_TYPE_CODE = :P_BOOK AND
MA.POST_BATCH_ID = :LAST_MASS_ADDITIONS_POST AND
MA.ADD_TO_ASSET_ID IS NULL AND
FAMA.MASS_ADDITION_ID(+) = MA.MASS_ADDITION_ID AND
GLCC.CODE_COMBINATION_ID(+)= FAMA.DEPRN_EXPENSE_CCID AND
CB.CATEGORY_ID = Decode(MA.Merged_Code, 'MC',
MA2.ASSET_CATEGORY_ID,MA.ASSET_CATEGORY_ID) AND
CB.BOOK_TYPE_CODE = :P_BOOK
GROUP BY
ma.asset_number,
ma.description,
&ACCT_FLEX_BAL_SEG,
&ACCT_FLEX_COST_SEG,
decode(MA.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT),
MA.MASS_ADDITION_ID,
MA.SUM_UNITS,
MA.FIXED_ASSETS_UNITS,
ma.fixed_assets_cost,
ma.description,
ma.reviewer_comments,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company', 'SQLGL', 'GL#', glcc.CHART_OF_ACCOUNTS_ID, NULL, glcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') ,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', glcc.CHART_OF_ACCOUNTS_ID, NULL, glcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
union
SELECT
ad.asset_number ASSET_NUMBER ,
MA.DESCRIPTION DESCRIPTION,
'Adjustments' TRANS_TYPE,
NULL COMPANY,
NULL COST_CTR,
decode( AD.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) ACCOUNT,
MA.MASS_ADDITION_ID,
ma.sum_units,
ma.fixed_assets_units,
sum( MA.FIXED_ASSETS_COST * ma.fixed_assets_units) COST,
MA.REVIEWER_COMMENTS COMMENTS,
null D_COMPANY,
null D_COST_CTR,
FA_FAS824_XMLP_PKG.new_costformula(ma.sum_units, sum( MA.FIXED_ASSETS_COST * ma.fixed_assets_units), ma.fixed_assets_units, ma.mass_addition_id) NEW_COST
FROM
FA_CATEGORY_BOOKS CB,
FA_ADDITIONS AD,
FA_MASS_ADDITIONS MA2,
FA_MASS_ADDITIONS MA
WHERE
Decode(MA.Merged_Code, 'MC', MA.Merge_Parent_Mass_Additions_ID,
MA.Mass_Addition_ID) = MA2.Mass_Addition_ID AND
MA.BOOK_TYPE_CODE = :P_BOOK AND
MA.POSTING_STATUS = 'POSTED' AND
MA2.POSTING_STATUS = 'POSTED' AND
MA2.BOOK_TYPE_CODE = :P_BOOK AND
MA.POST_BATCH_ID = :LAST_MASS_ADDITIONS_POST AND
( MA2.ADD_TO_ASSET_ID = AD.ASSET_ID(+) AND
MA2.ADD_TO_ASSET_ID IS NOT NULL ) AND
CB.CATEGORY_ID = AD.ASSET_CATEGORY_ID AND
CB.BOOK_TYPE_CODE = :P_BOOK
GROUP BY
ad.asset_number,
ma.description,
decode( AD.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT),
MA.MASS_ADDITION_ID,
ma.sum_units,
ma.fixed_assets_units,
ma.fixed_assets_cost,
ma.reviewer_comments,
null ,
null
ORDER BY
3,1,4,5,6,7,2,8 |