FA Cost Clearing Reconciliation - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Assets
Source: Cost Clearing Reconciliation Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASCC_XML
DB package: FA_FASCC_XMLP_PKG
Run FA Cost Clearing Reconciliation - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
select	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_bal_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')			comp_code,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')			clearing_acc,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_cost_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')			cost_center,
        	lu.meaning					tr_type_code,
	ad.asset_number||' - '||ad.description		asset_number,
	po_ai_in.vendor_name			po_vendor_name,
	ai_in.invoice_number			invoice_number,
	ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number		line_num,
	ai_in.description				description,
	1,
	round(sum(nvl(ai_in.payables_cost,0)) ,2)		cleared_cost
from
        	po_vendors		po_ai_in,
       	 fa_lookups		lu,
	gl_code_combinations    	glcc_ar,
	fa_additions		ad,
	fa_asset_history		ah,
	&lp_fa_asset_invoices	ai_in,
	fa_transaction_headers	th
where
	lu.lookup_code 	       = decode(ah.asset_type, 'CIP',
				'CIP ADDITION', 'ADDITION')		and
	lu.lookup_type         = 'FAXOLTRX'
and	th.date_effective between
		:PERIOD1_POD 	and
		:PERIOD1_PCD and 
	th.book_type_code  	= :P_BOOK  and
	th.transaction_type_code = 'TRANSFER IN'
and	ad.asset_id 		= th.asset_id
and	ah.asset_id		= th.asset_id			and
	ah.asset_type		<> 'EXPENSED' 			and
	ah.date_effective	<= 
		:PERIOD1_PCD  and
	nvl(ah.date_ineffective,sysdate) > 
		:PERIOD1_PCD
and	ai_in.asset_id         	= th.asset_id				and
	ai_in.date_effective    <= 
		:PERIOD1_PCD  and
	nvl(ai_in.date_ineffective,sysdate) >
		:PERIOD1_PCD
and	glcc_ar.code_combination_id = ai_in.payables_code_combination_id 
and 	nvl(ai_in.payables_cost, 0) != 0
and     	po_ai_in.vendor_id (+)	= ai_in.po_vendor_id
group by
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_bal_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_cost_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
        	lu.meaning,
	ad.asset_number||' - '||ad.description,
	po_ai_in.vendor_name,
	ai_in.invoice_number,
	ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
	ai_in.description
union
select	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_bal_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')			comp_code,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')			clearing_acc,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_cost_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')			cost_center,
       	lu.meaning				tr_type_code,
	ad.asset_number||' - '||ad.description		asset_number,
	po_ai_in.vendor_name			po_vendor_name,
	ai_in.invoice_number			invoice_number,
	ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number		line_num,
	ai_in.description				description,
	2,
	round(sum(nvl(ai_in.payables_cost,0)),2)		cleared_cost
from	po_vendors		po_ai_in,
        	fa_lookups		lu,
	gl_code_combinations    	glcc_ar,
	fa_additions		ad,
	fa_invoice_transactions	it,
	&lp_fa_asset_invoices		ai_in,
	fa_transaction_headers	tht,
	fa_transaction_headers	th
where	it.book_type_code      = :P_BOOK  and    
	it.invoice_transaction_id = th.invoice_transaction_id		and
	it.transaction_type	= 'MASS ADDITION'
and	lu.lookup_code 	       = decode(th.transaction_type_code,
				'ADDITION/VOID','ADDITION',
				th.transaction_type_code)		and
	lu.lookup_type         = 'FAXOLTRX'
and	th.date_effective between
		:PERIOD1_POD  and
		:PERIOD1_PCD  and 
	th.book_type_code  	= :P_BOOK  and
	th.transaction_type_code in 
		('CIP ADJUSTMENT', 'ADJUSTMENT', 'ADDITION','ADDITION/VOID')
and	tht.date_effective <
		:PERIOD1_POD  and
	tht.book_type_code  	= :P_BOOK  and
	tht.asset_id		= th.asset_id			and
	tht.transaction_type_code = 'TRANSFER IN'
and	ad.asset_id = th.asset_id					and
 	ad.asset_type 	<> 'EXPENSED'
and	ai_in.asset_id         = th.asset_id				and
	ai_in.payables_code_combination_id
		= glcc_ar.code_combination_id
and	ai_in.invoice_transaction_id_in = it.invoice_transaction_id
and     	ai_in.date_effective     <= th.date_effective				and
	nvl(ai_in.date_ineffective,sysdate) > th.date_effective
and 	nvl(ai_in.payables_cost, 0) != 0
and    	 ai_in.po_vendor_id     = po_ai_in.vendor_id(+)
group by
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_bal_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_cost_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
        	lu.meaning,
	ad.asset_number||' - '||ad.description,
	po_ai_in.vendor_name,
	ai_in.invoice_number,
	ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
	ai_in.description
union
select	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')			comp_code,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')			clearing_acc,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')			cost_center,
      	lu.meaning				tr_type_code,
	ad.asset_number||' - '||ad.description		asset_number_desc,
	po_ai_in.vendor_name			po_vendor_name,
	ai_in.invoice_number			invoice_number,
	ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number		line_num,
	ai_in.description				description,
	3,
	round(sum((decode(ai_in.deleted_flag, 'YES', 0,
		nvl(ai_in.fixed_assets_cost, 0)) -
			nvl(ai_in.payables_cost, 0))
	    * (nvl (dh.units_assigned, ah.units) /
			ah.units)) ,2)			cleared_cost
from	po_vendors				po_ai_in,
       	 fa_lookups		lu,
	fa_distribution_history	dh,
	gl_code_combinations    	glcc_fa,
	gl_code_combinations   	 glcc_ar,
	fa_additions		ad,
	fa_asset_history		ah,
	fa_category_books      	 cat_bk,
	&lp_fa_asset_invoices	 	ai_in,
	fa_transaction_headers	th
where
	lu.lookup_code 	       = decode(ah.asset_type, 'CIP',
				'CIP ADDITION', 'ADDITION')		and
	lu.lookup_type         = 'FAXOLTRX'
and	th.date_effective between
		:PERIOD1_POD  and
		:PERIOD1_PCD  and 
	th.book_type_code  	= :P_BOOK  and
	th.transaction_type_code = 'TRANSFER IN'
and	ad.asset_id 		= th.asset_id
and	ah.asset_id		= th.asset_id				and
	ah.date_effective	<= 
		:PERIOD1_PCD  and
	nvl(ah.date_ineffective,sysdate) > 
		:PERIOD1_PCD  and
	ah.asset_type		<> 'EXPENSED'
and     cat_bk.book_type_code	= :P_BOOK  and
	cat_bk.category_id	= ah.category_id
and	dh.book_type_code	= :P_BOOK  and
	dh.asset_id		= th.asset_id				and
     	dh.date_effective  <= 
		:PERIOD1_PCD and  
	nvl(dh.date_ineffective,sysdate) >
		:PERIOD1_PCD
and 	glcc_fa.code_combination_id = dh.code_combination_id
and	ai_in.asset_id         = th.asset_id				and
	ai_in.date_effective  <= 
		:PERIOD1_PCD and  
	nvl(ai_in.date_ineffective,sysdate) > 
		:PERIOD1_PCD  and
	nvl(ai_in.fixed_assets_cost,0) !=
		nvl(ai_in.payables_cost,0)
and     decode(ah.asset_type,'CIP',cat_bk.wip_clearing_account_ccid,
                                      cat_bk.asset_clearing_account_ccid)
		           = glcc_ar.code_combination_id
and     po_ai_in.vendor_id (+)	= ai_in.po_vendor_id
group by
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
        	lu.meaning,
	ad.asset_number||' - '||ad.description,
	po_ai_in.vendor_name,
	ai_in.invoice_number,
	ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
	ai_in.description
union
select	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')				comp_code,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')				clearing_acc,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')			cost_center,
        	lu.meaning					tr_type_code,
	ad.asset_number||' - '||ad.description		asset_number_desc,
	null		 				po_vendor_id,
	null						invoice_number,
	null				line_num,
	null						description,
	4, 
	round(sum((bk_in.cost - NVL(mc.cost,nvl(bk_out.cost,0))) *
		dh.units_assigned / ah.units),2)	cleared_cost
from	&lp_fa_books		bk_in,
                  fa_mc_books	mc,
	&lp_fa_books_bas	bk_out,
	fa_distribution_history    dh,
                  fa_lookups                          lu,
	gl_code_combinations    glcc_fa,
	gl_code_combinations    glcc_ar,
	fa_additions		ad,
                  fa_asset_history        ah,
	fa_category_books       cat_bk,
	fa_transaction_headers	th
where	
	th.book_type_code  = :P_BOOK  and
	th.invoice_transaction_id is null				and
	th.transaction_type_code in ('CIP ADDITION', 'CIP ADDITION/VOID',
	'ADDITION','ADDITION/VOID', 'ADJUSTMENT', 'CIP ADJUSTMENT') 	and
	th.date_effective between
		:PERIOD1_POD and
		:PERIOD1_PCD
and	lu.lookup_code 	       = decode(ah.asset_type, 'CIP',
				   decode(th.transaction_type_code,
					'CIP ADDITION/VOID','CIP ADDITION',
					'ADDITION/VOID','CIP ADDITION',
					th.transaction_type_code),
				   decode(th.transaction_type_code,
					'CIP ADDITION/VOID','ADDITION',
					'ADDITION/VOID','ADDITION',
					th.transaction_type_code)) and
     	lu.lookup_type		= 'FAXOLTRX'
and	ad.asset_id		= th.asset_id
and	ah.asset_id		= th.asset_id				and
	ah.date_effective <= 
 		decode(th.transaction_type_code,
		'CIP ADJUSTMENT', th.date_effective,
		'ADJUSTMENT', th.date_effective,
		:PERIOD1_PCD) and
	nvl(ah.date_ineffective,sysdate+1) > 
 		decode(th.transaction_type_code,
		'CIP ADJUSTMENT', th.date_effective,
		'ADJUSTMENT', th.date_effective,
		:PERIOD1_PCD) and
	ah.asset_type         <> 'EXPENSED'	
and	bk_in.transaction_header_id_in = th.transaction_header_id
and	bk_out.transaction_header_id_out(+) = th.transaction_header_id
and            mc.transaction_header_id_in(+) = DECODE(:mrc_sob_type_code,'R',bk_out.transaction_header_id_in,NULL)
and  	mc.set_of_books_id(+) = :set_of_books_id
and	dh.book_type_code	= :P_BOOK 
and	dh.asset_id		= th.asset_id 			
and     	dh.date_effective	<= 
 		decode(th.transaction_type_code,
		'CIP ADJUSTMENT', th.date_effective,
		'ADJUSTMENT', th.date_effective,
		:PERIOD1_PCD) and
	nvl(dh.date_ineffective,sysdate+1) >
 		decode(th.transaction_type_code,
		'CIP ADJUSTMENT', th.date_effective,
		'ADJUSTMENT', th.date_effective,
		:PERIOD1_PCD)
and	glcc_fa.code_combination_id	= dh.code_combination_id
and	cat_bk.category_id	= ah.category_id				and
	cat_bk.book_type_code	= :P_BOOK  and
     	decode(ah.asset_type,'CIP',cat_bk.wip_clearing_account_ccid,
                                     cat_bk.asset_clearing_account_ccid)
			       = glcc_ar.code_combination_id
and     	bk_in.cost       !=   nvl(bk_out.cost,0)
group by
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
        	lu.meaning,
	ad.asset_number||' - '||ad.description
union
select	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')			comp_code,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')			clearing_acc,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')			cost_center,
       	 lu.meaning				tr_type_code,
	ad.asset_number||' - '||ad.description		asset_number_desc,
	po_ai_in.vendor_name			po_vendor_name,
	ai_in.invoice_number			invoice_number,
	ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number		line_num,
	ai_in.description				description,
	5, 
	round(sum(decode(it.transaction_type,
	'INVOICE ADJUSTMENT',
		nvl(ai_in.fixed_assets_cost,0) -
		NVL(mc.fixed_assets_cost,nvl(ai_out.fixed_assets_cost,0)),
	'INVOICE DELETE',
		-nvl(ai_in.fixed_assets_cost,0),
	'INVOICE REINSTATE',
		nvl(ai_in.fixed_assets_cost,0),
	nvl(ai_in.fixed_assets_cost, 0) -
			nvl(ai_in.payables_cost, 0))
	    * (dh.units_assigned / ah.units)),2)		cleared_cost
from	fa_mc_asset_invoices	 mc,
	&lp_fa_asset_invoices_bas	ai_out,
        	po_vendors		po_ai_in,
       	 fa_lookups		lu,
	fa_distribution_history	dh,
	gl_code_combinations   	 glcc_fa,
	gl_code_combinations    	glcc_ar,
	fa_additions		ad,
       	 fa_asset_history        	ah,
	fa_category_books       	cat_bk,
	fa_transaction_headers	th,
	fa_invoice_transactions	it,
	&lp_fa_asset_invoices		ai_in
where	it.book_type_code	= :P_BOOK  and
	((it.transaction_type 	= 'MASS ADDITION' 	and
	 nvl(ai_in.fixed_assets_cost,0) !=
		nvl(ai_in.payables_cost,0)) 			or
	(it.transaction_type 	= 'INVOICE ADDITION' 		and
	 nvl(ai_in.fixed_assets_cost,0) != 0) 			or
	(it.transaction_type 	= 'INVOICE ADJUSTMENT' 		and
	 nvl(ai_in.fixed_assets_cost,0) !=
		nvl(ai_out.fixed_assets_cost,0)) 		or
	 (it.transaction_type = 'INVOICE DELETE' 		and
	 nvl(ai_in.fixed_assets_cost,0) != 0) 			or
	 (it.transaction_type = 'INVOICE REINSTATE' 		and
	 nvl(ai_in.fixed_assets_cost,0) != 0))
and	lu.lookup_code 	       = th.transaction_type_code 	and
	lu.lookup_type         = 'FAXOLTRX'
and	th.date_effective between
		:PERIOD1_POD  and
		:PERIOD1_PCD  and 
	th.invoice_transaction_id = it.invoice_transaction_id    	and
	th.transaction_type_code in ('ADJUSTMENT', 'CIP ADJUSTMENT')	and
	th.book_type_code  	= :P_BOOK  
and	ad.asset_id = th.asset_id  				 	and 
	ad.asset_id = ah.asset_id					
and     	ah.date_effective     <= th.date_effective			and
	nvl(ah.date_ineffective,sysdate) > th.date_effective		and
	ah.category_id         = cat_bk.category_id			and
	ah.asset_type		<> 'EXPENSED'
and     	cat_bk.book_type_code	= :P_BOOK and	dh.book_type_code	= :P_BOOK  and
	dh.asset_id		= th.asset_id		and
     	dh.date_effective	<= th.date_effective			and
	nvl(dh.date_ineffective,sysdate) > th.date_effective
and 
	dh.code_combination_id = glcc_fa.code_combination_id
and	ai_in.invoice_transaction_id_in = th.invoice_transaction_id	and
	ai_in.asset_id = th.asset_id				and
	ai_in.date_effective  <= th.date_effective			and
	nvl(ai_in.date_ineffective,sysdate) > th.date_effective
and     	decode(ah.asset_type,'CIP',cat_bk.wip_clearing_account_ccid,
                                       cat_bk.asset_clearing_account_ccid)
 		 	       = glcc_ar.code_combination_id
and	ai_out.invoice_transaction_id_out (+) 
			= ai_in.invoice_transaction_id_in		and
	ai_out.asset_id (+) = ai_in.asset_id				and
	ai_out.asset_invoice_id (+) = ai_in.asset_invoice_id
and            mc.asset_id(+) = DECODE(:mrc_sob_type_code,'R',ai_out.asset_id,NULL)
and         	mc.asset_invoice_id(+) = DECODE(:mrc_sob_type_code,'R',ai_out.asset_invoice_id,NULL)
and	mc.invoice_transaction_id_in(+) = DECODE(:mrc_sob_type_code,'R',ai_out.invoice_transaction_id_in,NULL)
and	mc.set_of_books_id(+) = :set_of_books_id
and	po_ai_in.vendor_id (+)	= ai_in.po_vendor_id
group by
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
        	lu.meaning,
	ad.asset_number||' - '||ad.description,
	po_ai_in.vendor_name,
	ai_in.invoice_number,
	ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
	ai_in.description
union
select	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')			comp_code,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')			clearing_acc,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')			cost_center,
       	 lu.meaning				tr_type_code,
	ad.asset_number||' - '||ad.description		asset_number_desc,
	po