APPS.WSH_WSHRDBOL_XMLP_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 18

        LP_DATE_RANGE := 'AND wbrv.delivery_leg_id IN (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date <= :P_DELIVERY_DATE_HIGH_V))';

Line: 21

        LP_DATE_RANGE := 'AND wbrv.delivery_leg_id IN (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date >= :P_DELIVERY_DATE_LOW))';

Line: 24

	--(select stop_id from wsh_trip_stops where planned_departure_date between :P_DELIVERY_DATE_LOW and :P_DELIVERY_DATE_HIGH))';

Line: 25

        LP_DATE_RANGE := 'AND wbrv.delivery_leg_id in (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in
	(select stop_id from wsh_trip_stops where planned_departure_date between :P_DELIVERY_DATE_LOW and :P_DELIVERY_DATE_HIGH_V))';

Line: 34

                                                 exists (select delivery_id from wsh_delivery_legs wdl1
                                                        where wdl1.delivery_id = wbrv.delivery_id
                                                          and delivery_leg_id in
                                                            (select parent_delivery_leg_id from wsh_delivery_legs
                                                                    where delivery_id = :P_DELIVERY_ID))
                                                 or
                                                exists ( select delivery_id from wsh_delivery_legs wdl2
                                                        where wdl2.delivery_id = wbrv.delivery_id
                                                          and parent_delivery_leg_id in
                                                            (select delivery_leg_id from wsh_delivery_legs
                                                              where delivery_id = :P_DELIVERY_ID))
                                                       )';

Line: 50

      LP_DELIVERY_ID := 'AND (exists (select delivery_id from wsh_delivery_legs wdl1
                                                        where wdl1.delivery_id = wbrv.delivery_id
                                                          and delivery_leg_id in
                                                            (select parent_delivery_leg_id from wsh_delivery_legs
                                                                    where rownum=1))
                                                 or
                                                exists ( select delivery_id from wsh_delivery_legs wdl2
                                                        where wdl2.delivery_id = wbrv.delivery_id
                                                          and parent_delivery_leg_id is not null)
                                                       )';

Line: 67

      LP_TRIP_ID := 'AND wbrv.delivery_leg_id in (select distinct delivery_leg_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where trip_id  = :P_TRIP_ID ))';

Line: 143

      SELECT
        DESCRIPTION
      FROM
        MTL_SYSTEM_ITEMS_VL
      WHERE INVENTORY_ITEM_ID = ID
        AND ORGANIZATION_ID = ORG_ID;

Line: 227

      SELECT
        NUMBER_OF_LPN
      FROM
        WSH_NEW_DELIVERIES
      WHERE DELIVERY_ID = DELIVERY_ID3;

Line: 278

      SELECT
        DISTINCT
        ( WDD.CUSTOMER_DOCK_CODE )
      FROM
        WSH_DELIVERY_DETAILS WDD,
        WSH_DELIVERY_ASSIGNMENTS_V WDA
      WHERE WDA.DELIVERY_ID = DELIVERY_ID3
        AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
        AND NVL(WDD.CONTAINER_FLAG
         ,'N') = 'N'
        AND WDD.CUSTOMER_DOCK_CODE is not null
      GROUP BY
        WDD.CUSTOMER_DOCK_CODE;

Line: 325

    SELECT
      CARRIER_ID
    INTO L_CARRIER_ID
    FROM
      WSH_CARRIER_SERVICES
    WHERE SHIP_METHOD_CODE = SHIP_METHOD;

Line: 331

    SELECT
      PARTY_NAME
    INTO L_CARRIER_NAME
    FROM
      HZ_PARTIES
    WHERE PARTY_ID = L_CARRIER_ID;

Line: 346

    SELECT
      SEQUENCE_NUMBER
    INTO L_MBOL_NUMBER
    FROM
      WSH_DOCUMENT_INSTANCES
    WHERE ENTITY_ID = TRIP_ID1
      AND ENTITY_NAME = 'WSH_TRIPS';

Line: 366

    SELECT
      PARTY_NAME,
      NVL(PERSON_PRE_NAME_ADJUNCT
         ,PERSON_TITLE) TITLE
    INTO L_CUSTOMER_NAME,L_PERSON_TITLE
    FROM
      HZ_PARTIES
    WHERE PARTY_NAME = CUSTOMER_NAME;

Line: 378

        SELECT
          MEANING || ' ' || L_CUSTOMER_NAME
        INTO L_CUSTOMER_NAME
        FROM
          AR_LOOKUPS
        WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
          AND LOOKUP_TYPE = L_LOOKUP_TYPE;

Line: 400

      SELECT
        CONCATENATED_SEGMENTS COMM_CLASS
      FROM
        MTL_CATEGORIES_KFV MC,
        MTL_ITEM_CATEGORIES MIC,
        MTL_CATEGORY_SETS_VL MCSTL
      WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
        AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
        AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
        AND MC.CATEGORY_ID = MIC.CATEGORY_ID
        AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
      ORDER BY
        MC.CATEGORY_ID;

Line: 432

      SELECT
        MC.SEGMENT1 || '-' || MC.SEGMENT2 || '-' || MC.SEGMENT3 COMM_CLASS
      FROM
        MTL_CATEGORIES MC,
        MTL_ITEM_CATEGORIES MIC,
        MTL_CATEGORY_SETS_VL MCSTL
      WHERE MIC.INVENTORY_ITEM_ID = C_INV_ITEM_ID
        AND MIC.ORGANIZATION_ID = C_INV_ORG_ID
        AND MIC.CATEGORY_SET_ID = MCSTL.CATEGORY_SET_ID
        AND MC.CATEGORY_ID = MIC.CATEGORY_ID
        AND MCSTL.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
      ORDER BY
        MC.CATEGORY_ID;

Line: 446

      SELECT
        DISTINCT
        INVENTORY_ITEM_ID,
        ORGANIZATION_ID
      FROM
        WSH_DELIVERY_DETAILS WDD
      WHERE WDD.DELIVERY_DETAIL_ID IN (
        SELECT
          WDA.DELIVERY_DETAIL_ID
        FROM
          WSH_DELIVERY_ASSIGNMENTS WDA
        START WITH WDA.DELIVERY_DETAIL_ID = C_DELIVERY_DETAIL_ID
        CONNECT BY prior WDA.DELIVERY_DETAIL_ID = WDA.PARENT_DELIVERY_DETAIL_ID )
        AND WDD.CONTAINER_FLAG = 'N';

Line: 484

      SELECT
        WDD.DELIVERY_DETAIL_ID,
        WDD.SOURCE_HEADER_ID,
        WDD.SOURCE_LINE_ID,
        WDD.TOP_MODEL_LINE_ID
      FROM
        WSH_DELIVERY_DETAILS WDD,
        WSH_DELIVERY_ASSIGNMENTS WDA
      WHERE WDA.DELIVERY_ID = C_DELIVERY_ID
        AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
        AND WDD.INVENTORY_ITEM_ID = C_INV_ITEM_ID
        AND WDD.ORGANIZATION_ID = C_INV_ORG_ID
        AND WDD.TOP_MODEL_LINE_ID IS NOT NULL
        AND ROWNUM = 1;