WMS Consolidation

Description
Categories: BI Publisher, Logistics
Application: Warehouse Management
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
Check LPN Multiple Association
 
LOV Oracle
Customer Name
 
LOV Oracle
Sales Order Number
 
LOV Oracle
Sales Order Type
 
LOV Oracle
Delivery
 
LOV Oracle
Trip
 
LOV Oracle
Oragnization Code
 
LOV Oracle