WSH Shipping Exceptions

Description
Categories: BI Publisher, Logistics
Application: Shipping Execution
Source: Shipping Exceptions Report (XML)
Short Name: WSHRDXCP_XML
DB package: WSH_WSHRDXCP_XMLP_PKG
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
Parameter Name SQL text Validation
WSH_SRS_ORGANIZATIONS
 
LOV Oracle
Sort By
 
LOV Oracle
Move Order Number (High)
 
LOV Oracle
Move Order Number (Low)
 
LOV Oracle
Delivery To
 
LOV Oracle
Delivery From
 
LOV Oracle
Trip To
 
LOV Oracle
Trip From
 
LOV Oracle
Status
 
LOV Oracle
Severity
 
LOV Oracle
Last Update Date (High)
 
Date
Last Update Date (Low)
 
Date
Creation Date (High)
 
Date
Creation Date (Low)
 
Date
Ship From Location Name
 
LOV Oracle
Exception Location Name
 
LOV Oracle
Logging Entity
 
LOV Oracle
Request Id
 
Number
Exception Name
 
LOV Oracle