Reports2017-11-18T12:27:27+00:00

WSH Shipped Delivery Lines

Description
Categories: BI Publisher, Logistics
Application: Shipping Execution
Source: Shipped Delivery Lines Report (XML)
Short Name: WSHRDSDL_XML
DB package: WSH_WSHRDSDL_XMLP_PKG
SELECT
                   SUBSTRB(PARTY.PARTY_NAME,1,50)                                      AS CUSTOMER_NAME,
                   CUST_ACCT.ACCOUNT_NUMBER                                              AS CUSTOMER_NUMBER,
                   WDD.INVENTORY_ITEM_ID                                                          AS INVENTORY_ITEM_ID,
                   WSH_UTIL_CORE.GET_ITEM_NAME(WDD.INVENTORY_ITEM_ID,:P_ORGANIZATION_ID,:P_ITEM_FLEX_CODE,:LP_STRUCTURE_NUM) as ITEM_SEG,
                   WDL.PICK_UP_STOP_ID  AS STOP_ID,
                   WDD.SHIPPED_QUANTITY                                                         AS SHIPPED_QUANTITY,
                   NVL(FLV_WT.MEANING, FLV_WDD.MEANING)   AS SHIP_METHOD,
                   WND.WAYBILL                                                                              AS WAYBILL,
                   WDD.SOURCE_HEADER_NUMBER                                                  AS ORDER_NUMBER,
                   TO_CHAR(WDD.DATE_SCHEDULED,'DD-MON-RRRR')               AS SCHEDULED_SHIP_DATE,
                   WND.NAME                                                                                   AS DELIVERY,
                   WDD.DELIVERY_DETAIL_ID                                                       AS DELIVERY_LINE,
                   WDD.ORGANIZATION_ID                                                            AS SHIP_FROM_ORGANIZATION,
                   WDD.SOURCE_LINE_NUMBER                                                                        AS ORDER_LINE ,
                WDD.SOURCE_HEADER_ID                                                              AS SOURCE_HEADER_ID,
                WSH_WSHRDSDL_XMLP_PKG.cf_ship_from_organization_name(WDD.ORGANIZATION_ID) CF_SHIP_FROM_ORGANIZATION_NAME,
                WSH_WSHRDSDL_XMLP_PKG.cf_released_quantityformula(WDD.DELIVERY_DETAIL_ID, WDD.SOURCE_HEADER_ID) CF_RELEASED_QUANTITY,
                WSH_WSHRDSDL_XMLP_PKG.cf_ship_dateformula(WDL.PICK_UP_STOP_ID) CF_SHIP_DATE,
                WSH_WSHRDSDL_XMLP_PKG.cf_item_displayformula(WDD.INVENTORY_ITEM_ID, WDD.ORGANIZATION_ID) CF_ITEM_DISPLAY
            FROM
                   HZ_PARTIES                                  PARTY,
                   (select CUST_ACCOUNT_ID, 
                          'C' CUST_TYPE, 
                           PARTY_ID, 
                           ACCOUNT_NUMBER 
                    from   HZ_CUST_ACCOUNTS
                    UNION
                    select VENDOR_ID CUST_ACCOUNT_ID, 
                           'V' CUST_TYPE, 
                           PARTY_ID, 
                           SEGMENT1 ACCOUNT_NUMBER 
                    from PO_VENDORS)  CUST_ACCT,
                   &LP_TABLE_NAME,
                   WSH_TRIPS                                     WT,
                   WSH_DELIVERY_DETAILS             WDD,
                   WSH_DELIVERY_ASSIGNMENTS_V  WDA,
                   WSH_DELIVERY_LEGS                   WDL,
                   WSH_NEW_DELIVERIES                WND,
                    ORG_ORGANIZATION_DEFINITIONS OOD,
                    FND_LOOKUP_VALUES_VL FLV_WT,
                    FND_LOOKUP_VALUES_VL FLV_WDD
            WHERE
                  WDD.ORGANIZATION_ID            =     OOD.ORGANIZATION_ID
            AND   OOD.OPERATING_UNIT              =     :P_ORGANIZATION_ID
            AND   WDD.DELIVERY_DETAIL_ID        =      WDA.DELIVERY_DETAIL_ID
            AND   WDA.DELIVERY_ID                      =      WND.DELIVERY_ID
            AND   WDA.DELIVERY_ID                      =      WDL.DELIVERY_ID
            AND   nvl(wnd.shipment_direction, 'O') IN ('O', 'IO')
            AND  WND.DELIVERY_TYPE = 'STANDARD'
             &LP_WHERE_CLAUSE
            AND   CUST_ACCT.PARTY_ID           =       PARTY.PARTY_ID
            AND   WDD.CUSTOMER_ID                   =      CUST_ACCT.CUST_ACCOUNT_ID
            AND   NVL(WDD.CONSIGNEE_FLAG,'C')       =      CUST_ACCT.CUST_TYPE
            AND  WDA.DELIVERY_ID IS NOT NULL
            AND  WDD.RELEASED_STATUS           IN ( 'C','I')
            AND  WDD.CONTAINER_FLAG             =     'N'
            AND  WDD.SOURCE_CODE    =  :P_SOURCE_SYSTEM
            AND  FLV_WT.LOOKUP_TYPE (+) = 'SHIP_METHOD'
            AND  FLV_WT.LOOKUP_CODE (+) = WT.SHIP_METHOD_CODE
            AND  FLV_WDD.LOOKUP_TYPE (+) = 'SHIP_METHOD'
            AND  FLV_WDD.LOOKUP_CODE (+) = WDD.SHIP_METHOD_CODE
            &LP_CUSTOMER_NAME
            &LP_DELIVERY
            &LP_ORDER_NUMBER
            &LP_SHIP_DATE
            &LP_SHIP_FROM_ORG_ID
            &LP_SHIP_METHOD
            &LP_ITEM
            &LP_ORDER_TYPE
            ORDER BY
             &LP_ORDER_BY
Parameter Name SQL text Validation
P_ORGANIZATION_ID
 
Sort By 3
 
LOV Oracle
Sort By 2
 
LOV Oracle
Sort By 1
 
LOV Oracle
Ship Method
 
LOV Oracle
Ship Date To
 
DateTime
Ship Date From
 
DateTime
Customer To
 
LOV Oracle
Customer From
 
LOV Oracle
Item Display
 
LOV Oracle
Item
 
Delivery To
 
LOV Oracle
Delivery From
 
LOV Oracle
Source Number To
 
LOV Oracle
Source Number From
 
LOV Oracle
Source Type
 
LOV Oracle
Source System
 
LOV Oracle
Ship From Organization
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept