FA Mass Additions Invoice Merge - draft

SELECT  
ad.asset_number				Asset_Number,
decode(ai.merged_code,'MP',ad.description,  'MC',ai.description)		Description,
ve.segment1||' - '||ve.vendor_name	Vendor,
ai.invoice_number			Invoice_Number,
 ai.invoice_line_number||' - '|| ai.ap_distribution_line_number     Line,
&ACCT_FLEX_ACCT_SEG                     Payables_Account,
cb.asset_cost_acct			Assets_Account,
nvl(ai.payables_cost,0.00)		Payables_Cost,
decode(ai.merged_code,'MC',0,books.cost) Assets_Cost,
--ADDED
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_payables_account', 'SQLGL', 'GL#', glcc.CHART_OF_ACCOUNTS_ID, NULL, glcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') D_PAYABLES_ACCOUNT
FROM	
fa_transaction_headers		th,	fa_asset_invoices			ai,
fa_additions			ad,
fa_books				books,
fa_asset_history			ah,
fa_category_books			cb,
po_vendors			ve,
gl_code_combinations		glcc
WHERE 	
th.date_effective >= :PERIOD1_POD AND
th.date_effective <= NVL(:PERIOD1_PCD,sysdate) AND
th.book_type_code = :P_BOOK AND
th.transaction_type_code  in ('ADDITION','CIP ADDITION')
AND         ai.asset_id = th.asset_id				AND	ai.merged_code in ('MP','MC')   
AND	ai.split_code is null
AND	ad.asset_id = ai.asset_id
AND	books.transaction_header_id_in = th.transaction_header_id
AND	ve.vendor_id = ai.po_vendor_id
AND	ah.asset_id = ai.asset_id		
AND	ah.date_effective <= nvl(:PERIOD1_PCD,sysdate)
aND nvl(ah.date_ineffective, sysdate+1) > nvl(:PERIOD1_PCD,sysdate)
AND	cb.category_id		  = ah.category_id		AND	cb.book_type_code	  = th.book_type_code
AND	ai.payables_code_combination_id = glcc.code_combination_id
ORDER BY 
ad.asset_number,
decode(ai.merged_code,'MP',1,0) desc,
ai.invoice_number,
ai.invoice_line_number|| ' - '||ai.ap_distribution_line_number