FA Mass Additions Posting - draft

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