WMS Consolidation
Description
Categories: BI Publisher
Application: Warehouse Management
Source: Consolidation Report (XML)
Short Name: WMSSTCON_XML
DB package: WMS_WMSSTCON_XMLP_PKG
Source: Consolidation Report (XML)
Short Name: WMSSTCON_XML
DB package: WMS_WMSSTCON_XMLP_PKG
SELECT wt.name Trip_Number, wnd.name Delivery_Number, oeh.order_number, PARTY.party_name customer_name, PARTY.party_number customer_number, lpn1.Lpn_id, lpn1.license_plate_number, lpn2.License_plate_number outermost_lpn, lpn1.Subinventory_code, lpn1.Locator_id, null c_loc_flexdata, lpn1.Outermost_lpn_id, wd2.inventory_item_id, null c_item_flexdata, wd2.requested_quantity_uom uom, wd2.requested_quantity Qty, wd2.revision, wd2.lot_number, wd2.serial_number, fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item_flex_field', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CF_Item_Flex_Field, fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_loc_flex_field', 'INV', 'MTLL', 101, mil.ORGANIZATION_ID, mil.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') CF_Loc_Flex_Field, WMS_WMSSTCON_XMLP_PKG.inmultiplesoformula(lpn1.Lpn_id) InMultipleSO, WMS_WMSSTCON_XMLP_PKG.inmultipledeliveryformula(lpn1.Lpn_id) InMultipleDelivery, WMS_WMSSTCON_XMLP_PKG.inmultipletripformula(lpn1.Lpn_id) InMultipleTrip FROM WMS_License_Plate_Numbers LPN1, WSH_DELIVERY_DETAILS WD1, WSH_DELIVERY_ASSIGNMENTS WDA, WSH_NEW_DELIVERIES WND, WSH_DELIVERY_LEGS WDL, WSH_TRIP_STOPS WTS, WSH_TRIPS WT, WMS_License_Plate_Numbers LPN2, WSH_DELIVERY_DETAILS WD2, OE_ORDER_HEADERS_ALL OEH, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT, MTL_SYSTEM_ITEMS MSI, MTL_ITEM_LOCATIONS MIL WHERE &p_where_clause and WND.Organization_id = :p_org_id and WND.delivery_id = WDA.delivery_id and WDA.parent_delivery_detail_id = WD1.delivery_detail_id (+) and WD1.lpn_id = LPN1.lpn_id (+) and LPN1.outermost_lpn_id = LPN2.lpn_id (+) and WD2.delivery_detail_id = WDA.delivery_detail_id and WD2.released_status = 'Y' and WND.delivery_id = WDL.delivery_id (+) and WDL.pick_up_stop_id = WTS.stop_id (+) and WTS.trip_id = WT.trip_id (+) and WD2.source_code = 'OE' and WD2.source_header_id = OEH.header_id and WD2.inventory_item_id = MSI.inventory_item_id and WD2.organization_id = MSI.organization_id and LPN1.locator_id = MIL.inventory_location_id and LPN1.organization_id = MIL.organization_id and PARTY.party_id = oeh.sold_to_org_id and CUST_ACCT.party_id = PARTY.party_id ORDER BY Trip_Number, Delivery_Number, Order_Number, lpn1.License_plate_number, lpn1.subinventory_code, MIL.SEGMENT1 || ',' || MIL.SEGMENT2 || ',' || MIL.SEGMENT3 || ',' || MIL.SEGMENT19 || ',' || MIL.SEGMENT20, MSI.SEGMENT1, wd2.lot_number, wd2.serial_number |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Oragnization Code |
|
LOV Oracle | |
Trip |
|
LOV Oracle | |
Delivery |
|
LOV Oracle | |
Sales Order Type |
|
LOV Oracle | |
Sales Order Number |
|
LOV Oracle | |
Customer Name |
|
LOV Oracle | |
Check LPN Multiple Association |
|
LOV Oracle |