$FLEX$ is a special bind variable that can be used to base a parameter value on the other parameters (dependent parameters)
Syntax 1 — :$FLEX$.Value_ Set_Name
Value_Set_Name is the name of value set for a prior parameter in the same parameter window that you want your parameter to depend on.
Syntax 2 — :$FLEX$.Paramter_Name
Paramter_Name is the name of prior parameter in the same parameter window that you want your parameter to depend on.
created the ValueSet : XXCDM_ORGS
Query :
select ORGANIZATION_NAME,ORGANIZATION_CODE,ORGANIZATION_ID from org_organization_definitions where organization_id in (7882,3535,
3536,
6646,
7884,
7881,
7880)
ORGANIZATION_NAME
|
ORGANIZATION_CODE
|
ORGANIZATION_ID
|
Vision Taiwan
|
VTW
|
3535
|
Vision Thailand
|
VTH
|
3536
|
Progress BE
|
BE1
|
6646
|
Central Warehouse - Field Service
|
CWH
|
7880
|
Regional Warehouse - Field Service
|
RWH
|
7881
|
Field Service Technicians - Defective Parts
|
FTD
|
7882
|
E4W Netherlands WMS
|
E4W
|
7884
|
Make this Valueset is dependent to Input Parameters
Parameters P_ORG1, P_ORG2, P_ORG3 are using the valueset XXCDM_ORGS
Parameters P_ORG1, P_ORG2, P_ORG3 are using the valueset XXCDM_ORGS
Parameters P_ITEM is using the valueset XXCDM_ITEM_VS
Table type Valueset : XXCDM_ITEM_VS
Definitions:
Table Name : (select distinct segment1,DESCRIPTION,INVENTORY_ITEM_ID from XXCDM_ITEMS_V) MSI
Value : segment1
Meaning : DESCRIPTION
where clause :
EXAMPLE VALUESET DEPENDENT
Objective of this paramter is, Item should be extract based on three paramters ORG1,ORG2,ORG3 so the valueset is dependent to ITEM paramters
We are selected three Orgs in three parameters
But ITEM parameter always should refer the immediate preceding dependent valueset values, it will not refer ORG1, ORG2 paramters values, so that here got item count is 5 for the ORG3 : VTH
Table type Valueset : XXCDM_ITEM_VS
Definitions:
CREATE OR REPLACE FORCE VIEW
XXCDM_ITEMS_V
(
SEGMENT1,
DESCRIPTION,
INVENTORY_ITEM_ID,
ORGANIZATION_ID
)
AS
SELECT DISTINCT msi.SEGMENT1,
msi.DESCRIPTION,
msi.INVENTORY_ITEM_ID,
msi.organization_id
FROM
APPS.MTL_SYSTEM_ITEMS_B msi, APPS.MTL_PARAMETERS mpTable Name : (select distinct segment1,DESCRIPTION,INVENTORY_ITEM_ID from XXCDM_ITEMS_V) MSI
Value : segment1
Meaning : DESCRIPTION
where clause :
MSI.SEGMENT1 IN
(SELECT MSI1.SEGMENT1
FROM XXCDM_ITEMS_V MSI1
WHERE
((ORGANIZATION_ID = (CASE WHEN :$FLEX$.XXCDM_ORGS:NULL IS NULL AND :$FLEX$.XXCDM_ORGS:NULL IS NULL AND :$FLEX$.XXCDM_ORGS:NULL IS NULL THEN ORGANIZATION_ID
ELSE -1
END) ) OR ORGANIZATION_ID IN(:$FLEX$.XXCDM_ORGS:NULL,:$FLEX$.XXCDM_ORGS:NULL,:$FLEX$.XXCDM_ORGS:NULL)
)
)
EXAMPLE VALUESET DEPENDENT
Objective of this paramter is, Item should be extract based on three paramters ORG1,ORG2,ORG3 so the valueset is dependent to ITEM paramters
We are selected three Orgs in three parameters
But ITEM parameter always should refer the immediate preceding dependent valueset values, it will not refer ORG1, ORG2 paramters values, so that here got item count is 5 for the ORG3 : VTH
EXAMPLE PARAMETER DEPENDENT
Objective of this paramter is, Item should be extract based on three paramters ORG1,ORG2,ORG3 so the valueset is dependent to ITEM paramters
Change the valueset XXCDM_ITEM_VS, where clause to
MSI.SEGMENT1 IN
(SELECT MSI1.SEGMENT1
FROM XXCDM_ITEMS_V MSI1
WHERE
((ORGANIZATION_ID = (CASE WHEN :$FLEX$.P_ORG1:NULL IS NULL AND :$FLEX$.P_ORG2:NULL IS NULL AND :$FLEX$.P_ORG3:NULL IS NULL THEN ORGANIZATION_ID
ELSE -1
END) ) OR ORGANIZATION_ID IN(:$FLEX$.P_ORG1:NULL,:$FLEX$.P_ORG2:NULL,:$FLEX$.P_ORG3:NULL)
)
)
here P_ORG1, P_ORG2, P_ORG3 are parameter names
we got 8 item details for selected three ORGS.
Very informative thanks, I have created valuesets to use for parameters in a concurrent program. I have a parameter that is disabled and will not let me select it unless a value from the parameter above it has a value. How can I turn this off.
ReplyDelete