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