[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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
0
Olugbnega Oyeneye
Asked:
Olugbnega Oyeneye
  • 3
  • 3
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now