WSH Shipping Exceptions

select  &P_HINT_1 EXCEPTION_ID ,
                 LOGGED_AT_LOCATION_ID
                 ,LE.MEANING LOGGING_ENTITY_MEANING
                 ,EXCEPTION_NAME
                 ,EXCEPTION_LOCATION_ID
                 ,MESSAGE
                 ,SEV.MEANING SEVERITY_MEANING
                 ,MANUALLY_LOGGED
                 ,STA.MEANING STATUS_MEANING
                 ,we.TRIP_ID
                 ,TRIP_NAME
                 ,we.TRIP_STOP_ID
                 ,we.DELIVERY_ID
                 ,DELIVERY_NAME
                 ,we.DELIVERY_DETAIL_ID
                 ,DELIVERY_ASSIGNMENT_ID
                 ,we.CONTAINER_NAME
                 ,&P_ITEM_FLEXSQL   C_ITEM_FLEXDAT
                 ,we.INVENTORY_ITEM_ID
                 ,we.LOT_NUMBER
                 ,we.REVISION
                 ,we.SERIAL_NUMBER
                 ,UNIT_OF_MEASURE
                 ,UNIT_OF_MEASURE2
                 ,QUANTITY
                 ,QUANTITY2
                 ,we.SUBINVENTORY
                 ,we.LOCATOR_ID
                 ,null   C_LOCATOR_FLEXDAT
                 ,ARRIVAL_DATE
                 ,DEPARTURE_DATE
                 ,ERROR_MESSAGE
                 ,we.CREATION_DATE
                 ,we.REQUEST_ID
                 ,WND.NAME DEL_NAME
                 ,WT.NAME TRP_NAME,
                WSH_WSHRDXCP_XMLP_PKG.c_exception_locationformula(EXCEPTION_LOCATION_ID) C_Exception_Location,
                WSH_WSHRDXCP_XMLP_PKG.c_logged_locationformula(LOGGED_AT_LOCATION_ID) C_Logged_Location,
                WSH_WSHRDXCP_XMLP_PKG.cf_container_nameformula(we.CONTAINER_NAME, we.DELIVERY_DETAIL_ID) CF_container_name,
                WSH_WSHRDXCP_XMLP_PKG.cf_delivery_nameformula(we.DELIVERY_DETAIL_ID, WND.NAME) CF_delivery_name,
                WSH_WSHRDXCP_XMLP_PKG.CP_delivery_detail_id_p CP_delivery_detail_id,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('f_item_flexval', 'INV', 'MSTK', 101, SYS.ORGANIZATION_ID, SYS.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') F_Item_FlexVal,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('f_locator_value', 'INV', 'MTLL', 101, LOC.ORGANIZATION_ID, LOC.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') F_locator_Flexval
            from
                  wsh_exceptions we,
                  mtl_system_items sys,
                  mtl_item_locations loc,
                  &p_wsh_picking_batches_table
                  wsh_lookups sta,
                  wsh_lookups sev,
                  wsh_lookups le,
                  WSH_NEW_DELIVERIES WND,
                  WSH_TRIPS WT,
                  WSH_TRIP_STOPS WTS,
                  WSH_DELIVERY_DETAILS WDD
            where
                    sta.lookup_type = 'EXCEPTION_STATUS'
                  and sta.lookup_code = we.status
                  and sev.lookup_type IN ( 'EXCEPTION_SEVERITY','EXCEPTION_BEHAVIOR')
                  and sev.lookup_code = we.severity
                  and le.lookup_type = 'LOGGING_ENTITY'
                  and le.lookup_code = logging_entity
                  and sys.organization_id(+) = we.exception_location_id
                  and sys.inventory_item_id(+) = we.inventory_item_id
                  and loc.inventory_location_id(+) = we.locator_id
                  and loc.organization_id(+) = we.exception_location_id
                  &p_request
                  &p_exception
                  &p_logging_entity_location
                  &p_trip
                  &p_delivery
                  &p_severity_status
                  &p_we_wpb_outer_join
                  and (we.exception_name IS NULL OR we.exception_name not like 'WSH_IB%')
                  and we.DELIVERY_ID = WND.DELIVERY_ID(+)
                  and we.TRIP_ID = WT.TRIP_ID(+)
                  and we.TRIP_STOP_ID = WTS.STOP_ID (+)
                  and we.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID (+)
                  and  ( WND.SHIPMENT_DIRECTION is null or WND.SHIPMENT_DIRECTION IN ('O', 'IO') )
                              and ( WND.DELIVERY_TYPE is null or WND.DELIVERY_TYPE = 'STANDARD')
                  and  ( WT.SHIPMENTS_TYPE_FLAG is null or WT.SHIPMENTS_TYPE_FLAG IN ('O', 'M')   )
                  and  ( WTS.SHIPMENTS_TYPE_FLAG is null or WTS.SHIPMENTS_TYPE_FLAG  IN ('O', 'M')  )
                  and  ( WDD.LINE_DIRECTION is null or WDD.LINE_DIRECTION IN ('O', 'IO')  )
                              and (WDD.container_flag IS NULL or WDD.container_flag IN ('N', 'Y') )
                  &p_batches
                  &p_creation_update_date
                  &P_DEL_LINES_CONT , fnd_flex_xml_publisher_apis.process_kff_combination_1('f_item_flexval', 'INV', 'MSTK', 101, SYS.ORGANIZATION_ID, SYS.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') F_Item_FlexVal , fnd_flex_xml_publisher_apis.process_kff_combination_1('f_locator_value', 'INV', 'MTLL', 101, LOC.ORGANIZATION_ID, LOC.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') F_locator_Flexval
                  &P_DEL_LINES_CONT_1
                  &P_DEL_LINES_CONT , fnd_flex_xml_publisher_apis.process_kff_combination_1('f_item_flexval', 'INV', 'MSTK', 101, SYS.ORGANIZATION_ID, SYS.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') F_Item_FlexVal , fnd_flex_xml_publisher_apis.process_kff_combination_1('f_locator_value', 'INV', 'MTLL', 101, LOC.ORGANIZATION_ID, LOC.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') F_locator_Flexval
                  &P_DEL_LINES_CONT_2
            &p_order_by