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
Olugbnega OyeneyeDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
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

0
Olugbnega OyeneyeDeveloperAuthor Commented:
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========
0
Jinesh KamdarCommented:
Right, those were the only lines that changed. DId it work for u?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Olugbnega OyeneyeDeveloperAuthor Commented:
Thank you very much. You are most helpful. Cheers
0
Jinesh KamdarCommented:
Glad to be of help :)
0
Olugbnega OyeneyeDeveloperAuthor Commented:
It worked for me. Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.