LOV Query

/* Formatted on 9/9/2014 11:07:05 AM (QP5 v5.115.810.9015) */
SELECT wsh.ship_method_meaning meaning,
       wsh.ship_method_code lookup_code,
       fl.description
FROM wsh_carrier_services wsh,
     wsh_org_carrier_services wsh_org,
     fnd_lookup_values_vl fl,
     hz_parties hp
WHERE     wsh_org.organization_id = :line.ship_from_org_id
      AND wsh_org.carrier_service_id = wsh.carrier_service_id
      AND fl.lookup_type = 'SHIP_METHOD'
      AND fl.lookup_code = wsh.ship_method_code
--      AND fl.view_application_id = 3
      AND wsh.carrier_id = hp.party_id
      AND hp.status = 'A'
      AND SYSDATE BETWEEN fl.start_date_active
                      AND  NVL (fl.end_date_active, SYSDATE)
--      AND fl.enabled_flag = 'Y'
      AND wsh_org.enabled_flag = 'Y'
      AND fl.lookup_code IN
               (SELECT b.ship_method
                FROM mtl_interorg_ship_methods b
                WHERE b.to_organization_id =
                         (SELECT organization_id
                          FROM po_location_associations_all
                          WHERE site_use_id = :line.ship_to_org_id)
                      AND b.from_organization_id = :line.ship_from_org_id)