asked on

Dynamically return Value in a WHERE clause at runtime

I have a report 'XXCBNC2T.rdf' that i want to modify, so that it can Dynamically return Value in a WHERE clause of the query at runtime

Part to be corrected: 'AND ITM.SEGMENT1 like :p_curr_flag ||'%' --VMS Master Org'

----------------Begin---------------
An attempt is made here but its not working yet:
AND 'IF' :p_curr_flag = TRUE THEN AND ITM.SEGMENT1 like 'NGN%'
ELSIF AND ITM.SEGMENT1 NOT LIKE 'NGN%'
-----------------End------------

SELECT SUBSTR(ORG.NAME,1,INSTR(ORG.NAME,'BRANCH',1) -1) BRANCH_NAME
	   			, ITM.SEGMENT1 item_name
	, ITM.INVENTORY_ITEM_ID
	, ITM.ORGANIZATION_ID
	, (XXCBN_VMS_PKG.GET_TXN_TOTAL_QTY(IMB.INVENTORY_ITEM_ID, IMB.ORGANIZATION_ID, to_date(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'), to_date		
	(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'),:P_TRANSACTION_TYPE_ID1) 
	+ XXCBN_VMS_PKG.GET_TXN_TOTAL_QTY(IMB.INVENTORY_ITEM_ID, IMB.ORGANIZATION_ID, to_date(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'), to_date		(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'),:P_TRANSACTION_TYPE_ID2))  
* NVL(CST.ITEM_COST,0) TXN_QTY
	, CST.ITEM_COST ITEM_COST
FROM MTL_SYSTEM_ITEMS_B ITM
	 		, MTL_SYSTEM_ITEMS_B IMB
			, HR_ALL_ORGANIZATION_UNITS ORG
			, CST_ITEM_COSTS CST
WHERE CST.ORGANIZATION_ID = ITM.ORGANIZATION_ID  
AND IMB.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND IMB.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND CST.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = 105	
AND ITM.SEGMENT1  like :p_curr_flag	||'%'	 --VMS Master Org
AND IMB.ORGANIZATION_ID != 105
AND IMB.ORGANIZATION_ID != 104
AND CST.COST_TYPE_ID = 1
AND ITM.INVENTORY_ITEM_STATUS_CODE != 'Inactive'
&L_CURRENCY
ORDER BY ORG.NAME ASC, CST.ITEM_COST DESC

Open in new window

Report-XXCBNC2T.doc

Jinesh Kamdar

Try this.

SELECT SUBSTR(ORG.NAME,1,INSTR(ORG.NAME,'BRANCH',1) -1) BRANCH_NAME
	   			, ITM.SEGMENT1 item_name
	, ITM.INVENTORY_ITEM_ID
	, ITM.ORGANIZATION_ID
	, (XXCBN_VMS_PKG.GET_TXN_TOTAL_QTY(IMB.INVENTORY_ITEM_ID, IMB.ORGANIZATION_ID, to_date(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'), to_date		
	(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'),:P_TRANSACTION_TYPE_ID1) 
	+ XXCBN_VMS_PKG.GET_TXN_TOTAL_QTY(IMB.INVENTORY_ITEM_ID, IMB.ORGANIZATION_ID, to_date(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'), to_date		(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'),:P_TRANSACTION_TYPE_ID2))  
* NVL(CST.ITEM_COST,0) TXN_QTY
	, CST.ITEM_COST ITEM_COST
FROM MTL_SYSTEM_ITEMS_B ITM
	 		, MTL_SYSTEM_ITEMS_B IMB
			, HR_ALL_ORGANIZATION_UNITS ORG
			, CST_ITEM_COSTS CST
WHERE CST.ORGANIZATION_ID = ITM.ORGANIZATION_ID  
AND IMB.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND IMB.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND CST.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = 105
AND ((:p_curr_flag = TRUE  AND ITM.SEGMENT1 LIKE 'NGN%')
  OR (:p_curr_flag = FALSE AND ITM.SEGMENT1 NOT LIKE 'NGN%')) --VMS Master Org
AND IMB.ORGANIZATION_ID != 105
AND IMB.ORGANIZATION_ID != 104
AND CST.COST_TYPE_ID = 1
AND ITM.INVENTORY_ITEM_STATUS_CODE != 'Inactive'
&L_CURRENCY --- I'm not sure what is this line for ???
ORDER BY ORG.NAME ASC, CST.ITEM_COST DESC

Open in new window

Olugbnega Oyeneye

ASKER

Changes made only on 2 rows:
========Begin Changes made=======
AND ((:p_curr_flag = 'Y' AND ITM.SEGMENT1 LIKE 'NGN%') --*updated
OR (:p_curr_flag = 'N' AND ITM.SEGMENT1 NOT LIKE 'NGN%')) --VMS Master Org --*updated
========End Changes Made======

========Begin Final Code=========
SELECT SUBSTR(ORG.NAME,1,INSTR(ORG.NAME,'BRANCH',1) -1) BRANCH_NAME
, ITM.SEGMENT1 item_name
, ITM.INVENTORY_ITEM_ID
, ITM.ORGANIZATION_ID
, (XXCBN_VMS_PKG.GET_TXN_TOTAL_QTY(IMB.INVENTORY_ITEM_ID, IMB.ORGANIZATION_ID,
to_date(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'), to_date
(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'),:P_TRANSACTION_TYPE_ID1)
+ XXCBN_VMS_PKG.GET_TXN_TOTAL_QTY(IMB.INVENTORY_ITEM_ID, IMB.ORGANIZATION_ID,
to_date(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'), to_date (:P_TO_DATE,'YYYY/MM/DD
HH24:MI:SS'),:P_TRANSACTION_TYPE_ID2))
* NVL(CST.ITEM_COST,0) TXN_QTY
, CST.ITEM_COST ITEM_COST
FROM MTL_SYSTEM_ITEMS_B ITM
, MTL_SYSTEM_ITEMS_B IMB
, HR_ALL_ORGANIZATION_UNITS ORG
, CST_ITEM_COSTS CST
WHERE CST.ORGANIZATION_ID = ITM.ORGANIZATION_ID
AND IMB.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND IMB.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND CST.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = 105

AND ((:p_curr_flag = 'Y' AND ITM.SEGMENT1 LIKE 'NGN%') --*updated
OR (:p_curr_flag = 'N' AND ITM.SEGMENT1 NOT LIKE 'NGN%')) --VMS Master Org --*updated

AND IMB.ORGANIZATION_ID != 105
AND IMB.ORGANIZATION_ID != 104
AND CST.COST_TYPE_ID = 1
AND ITM.INVENTORY_ITEM_STATUS_CODE != 'Inactive'
&L_CURRENCY
ORDER BY ORG.NAME ASC, CST.ITEM_COST DESC
======End Final Code========

ASKER CERTIFIED SOLUTION

Jinesh Kamdar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olugbnega Oyeneye

ASKER

Thank you very much. You are most helpful. Cheers

Jinesh Kamdar

Glad to be of help :)

Olugbnega Oyeneye

ASKER

It worked for me. Thank you.