Find Order number for given pick release rule

Select order number for pick release rule :

  SELECT DISTINCT wdd.source_header_number,

                  otl.name,

                  SUBSTRB (party.party_name, 1, 50) customer_name,

                  wdd.source_header_id,

                  wdd.source_header_type_id,

                  cust_acct.cust_account_id customer_id

    FROM wsh_delivery_details wdd,

         hz_parties party,

         hz_cust_accounts cust_acct,

         WSH_PICKING_RULES wpr,

         oe_transaction_types_tl otl

   WHERE     wdd.customer_id = cust_acct.cust_account_id

         AND cust_acct.party_id = party.party_id

         AND wdd.released_status IN ('B', 'R', 'X')

         AND wdd.organization_id = wpr.organization_id

         AND wdd.subinventory = NVL (wpr.rel_subinventory, wdd.subinventory)

         AND otl.language = USERENV ('LANG')

         AND otl.transaction_type_id = wdd.source_header_type_id

         AND otl.name = 'Order type'

         AND wpr.name = NVL ( :RELEASE.PICKING_RULE, wpr.name)

ORDER BY 2, 1