R12 – How to Handle NULL for :$FLEX$.VALUE_SET_NAME In Oracle ERP

:$FLEX$.VALUE_SET_NAME to set up value sets where one segment depends on a prior segment that itself depends on a prior segment (“cascading dependencies”)

WHERE CLAUSE

WHERE EXISTS (SELECT 1
FROM PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
MTL_SYSTEM_ITEMS_B MSI
WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND PRHA.AUTHORIZATION_STATUS = ‘APPROVED’
AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND PRLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PRLA.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND (PRLA.LINE_LOCATION_ID IS NULL
OR PRLA.AUCTION_HEADER_ID IS NULL)
AND PRDA.PROJECT_ID = :$FLEX$.XX_SECURED_PROJECTS_ID
AND SUBSTR(MSI.SEGMENT1,1,4) = ITM.ITEM_GROUP
AND NVL(PRLA.SUGGESTED_BUYER_ID,0) = NVL(:$FLEX$.PO_SRS_BUYER_ID:NULL, NVL(PRLA.SUGGESTED_BUYER_ID,0))
AND PRHA.SEGMENT1 BETWEEN NVL(:$FLEX$.P_PR_FROM:NULL, PRHA.SEGMENT1) AND NVL(:$FLEX$.P_PR_TO:NULL, PRHA.SEGMENT1)
AND TRUNC(APPROVED_DATE) BETWEEN fnd_date.canonical_to_date(:$FLEX$.P_FROM_DATE)
AND fnd_date.canonical_to_date(:$FLEX$.P_TO_DATE))