How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM)

Dear Friends , 

Below Query can be useful to find the Unit cost of Item at costing Module .

We can have 

FOR Unit cost OF Material under specified Val Unit code
	AND Cost Book

SELECT TO_CHAR(CICT.total_cost, 'fm99999999d00') UNIT_COST
FROM CST_ITEM_COSTS_V CICT
	,CST_TRANSACTIONS CT
WHERE 1 = 1
	AND CICT.ITEM_NUMBER = :P_Item_Number
	AND CICT.TRANSACTION_ID = CT.TRANSACTION_ID
	AND TRUNC(CICT.PREVIOUS_COST, 6) = (
		SELECT TRUNC(UNIT_COST_AVERAGE, 6)
		FROM cst_perpavg_cost
		WHERE PERPAVG_COST_ID = (
				SELECT MIN(PERPAVG_COST_ID)
				FROM cst_perpavg_cost
				WHERE TRANSACTION_ID = CICT.TRANSACTION_ID
				)
		)
	AND CICT.VAL_UNIT_CODE LIKE 'XXXXXX'
	AND TO_CHAR(CT.TRANSACTION_DATE, 'DD-MM-YYYY') BETWEEN TO_CHAR(:P_START_DATE, 'DD-MM-YYYY')
		AND TO_CHAR(:P_END_DATE, 'DD-MM-YYYY')

And If you want to have the 

Query to fetch Current Item cost for all items based on inventory Organization/Cost Book , for info we are using Average costing.
 

SELECT c.name Cost_org
	,c.COST_BOOK_DESC cost_book
	,a.inventory_item_id item_id
	,d.item_number
	,e.val_unit_code val_unit
	,unit_cost_average unit_cost
FROM fusion.cst_perpavg_cost b
	,(
		SELECT max(perpavg_cost_id) perpavg_cost_id
			,cost_org_id
			,cost_book_id
			,inventory_item_id
			,val_unit_id
		FROM fusion.cst_perpavg_cost
		GROUP BY cost_org_id
			,cost_book_id
			,inventory_item_id
			,val_unit_id
		) a
	,(
		SELECT cb.cost_book_id
			,cost_book_desc
			,cob.cost_org_id
			,org.name
		FROM fusion.cst_cost_books_tl cb
			,fusion.cst_cost_org_books cob
			,fusion.hr_organization_units_f_tl org
		WHERE cb.cost_book_id = cob.cost_book_id
			AND cob.cost_org_id = org.organization_id
			AND cb.LANGUAGE = org.LANGUAGE
			AND cb.LANGUAGE = 'US'
		) c
	,(
		SELECT DISTINCT inventory_item_id
			,item_number
		FROM fusion.egp_system_items
		) d
	,(
		SELECT val_unit_id
			,val_unit_code
		FROM fusion.cst_val_units_b
		) e
WHERE b.perpavg_cost_id = a.perpavg_cost_id
	AND a.cost_org_id = c.cost_org_id
	AND b.cost_org_id = c.cost_org_id
	AND a.cost_book_id = c.cost_book_id
	AND b.cost_book_id = c.cost_book_id
	AND a.inventory_item_id = d.inventory_item_id
	AND b.inventory_item_id = d.inventory_item_id
	AND a.val_unit_id = e.val_unit_id
	AND b.val_unit_id = e.val_unit_id;

Happy Leaning !!!