WMS Consolidation

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