DBA Data[Home] [Help]

APPS.WSH_FC_INTERFACE_PKG SQL Statements

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

Line: 46

SELECT   wdd.delivery_detail_id,
   -- bug 3935583
   wdd.inventory_item_id,
   wdd.requested_quantity,
   wdd.shipped_quantity,
   wdd.requested_quantity_uom,
   wdd.net_weight,
   wdd.weight_uom_code,
   wdd.volume,
   wdd.volume_uom_code,
   wdd.container_flag
FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
WHERE wda.parent_delivery_detail_id = c_container_id  and
      wda.delivery_detail_id = wdd.delivery_detail_id and
      wdd.oe_interfaced_flag = 'N' and
      wdd.released_status = 'C' and
      NVL(wdd.shipped_quantity , 0) > 0 ;
Line: 65

SELECT   dd.delivery_detail_id,
   -- bug 3935583
   dd.inventory_item_id,
   dd.requested_quantity,
   dd.shipped_quantity,
   dd.requested_quantity_uom,
   dd.net_weight,
   dd.weight_uom_code,
   dd.volume,
   dd.volume_uom_code
FROM wsh_delivery_details dd,
     wsh_delivery_assignments_v da,
     wsh_new_deliveries nd,
     oe_order_lines_all ol
WHERE      dd.delivery_detail_id = da.delivery_detail_id AND
         da.delivery_id = nd.delivery_id AND
         da.delivery_id IS NOT NULL AND
           ol.line_id = dd.source_line_id AND
           dd.source_code = 'OE' and
           nd.delivery_id = c_delivery_id AND
           dd.container_flag = 'N' AND
           dd.oe_interfaced_flag = 'N' and
           dd.released_status = 'C' and
           NVL(dd.shipped_quantity, 0) > 0;
Line: 92

SELECT dg.delivery_id
FROM   wsh_delivery_legs dg,
       wsh_new_deliveries dl,
       wsh_trip_stops st
WHERE    st.stop_id = dg.pick_up_stop_id AND
         st.stop_id = c_stop_id AND
         st.stop_location_id = dl.initial_pickup_location_id AND
         dg.delivery_id = dl.delivery_id;
Line: 102

SELECT stop_id
FROM   wsh_trip_stops
WHERE  trip_id = c_trip_id
AND    nvl(shipments_type_flag,'O') IN ('O','M');
Line: 2079

SELECT distinct st.stop_id
FROM wsh_trip_stops st,
     wsh_new_deliveries nd,
     wsh_delivery_legs  dl,
     wsh_delivery_assignments_v da,
     wsh_delivery_details dd
WHERE
     st.trip_id = p_trip_id and
     st.stop_id = dl.pick_up_stop_id and
     dl.delivery_id = nd.delivery_id and
     st.stop_location_id = nd.initial_pickup_location_id and
     da.delivery_detail_id = dd.delivery_detail_id and
     dd.shipped_quantity > 0 and
     dd.container_flag = 'N' and
     da.delivery_id IS NOT NULL and
     da.delivery_id = nd.delivery_id and
     nvl(dd.line_direction,'O') IN ('O','IO');
Line: 2155

select count(distinct st.stop_id) into l_number_of_stops
from wsh_trip_stops st,
     wsh_new_deliveries nd,
     wsh_delivery_legs  dl,
     wsh_delivery_assignments_v da,
     wsh_delivery_details dd
where
     st.trip_id = p_trip_id and
     st.stop_id = dl.pick_up_stop_id and
     dl.delivery_id = nd.delivery_id and
     st.stop_location_id = nd.initial_pickup_location_id and
     da.delivery_detail_id = dd.delivery_detail_id and
     dd.shipped_quantity > 0 and
     dd.container_flag = 'N' and
     da.delivery_id IS NOT NULL and
     da.delivery_id = nd.delivery_id and
     nvl(dd.line_direction,'O') IN ('O','IO');
Line: 2315

   SELECT precision, nvl(minimum_accountable_unit, 0)
   INTO l_precision, l_minimum_accountable_unit
   FROM fnd_currencies
   WHERE currency_code = p_Currency_Code;
Line: 2384

SELECT h.transactional_curr_code,
       h.conversion_type_code,
       h.conversion_rate_date,
       h.conversion_rate,
       l.line_id,
       l.header_id
FROM oe_order_headers_all h, oe_order_lines_all l, wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = c_delivery_detail_id AND
      wdd.source_line_id = l.line_id AND
      wdd.source_code = 'OE' and
      l.header_id = h.header_id;
Line: 2400

SELECT actual_departure_date
FROM WSH_TRIP_STOPS
WHERE stop_id = p_stop_id;
Line: 2960

SELECT a.trip_id, a.freight_cost_id, a.unit_amount, a.currency_code,
       c.freight_cost_type_code
       , a.conversion_type_code, a.conversion_rate
FROM wsh_freight_costs a,
     wsh_trip_stops b,
     wsh_freight_cost_types c
WHERE a.trip_id = b.trip_id and
      b.stop_id = p_stop_id and
      c.freight_cost_type_id = a.freight_cost_type_id and
      /* H projects: pricing integration csun
         only line_type_code NULL, PRICE, CHARGE should be
         interfaced to OM
       */
      NVL(a.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE')
      AND    nvl(b.shipments_type_flag,'O') IN ('O','M')
      AND a.unit_amount   IS NOT NULL  -- bug 5447870: skip NULL records
      AND a.currency_code IS NOT NULL;
Line: 2983

SELECT a.stop_id, a.freight_cost_id, a.unit_amount, a.currency_code,
       b.freight_cost_type_code
       , a.conversion_type_code, a.conversion_rate
FROM wsh_freight_costs a,
     wsh_freight_cost_types b
WHERE a.stop_id = p_stop_id and
      a.freight_cost_type_id = b.freight_cost_type_id and
      /* H projects: pricing integration csun
         only line_type_code NULL, PRICE, CHARGE should be
         interfaced to OM
       */
      NVL(a.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE')
      AND a.unit_amount   IS NOT NULL  -- bug 5447870: skip NULL records
      AND a.currency_code IS NOT NULL;
Line: 3003

SELECT  wnd.delivery_id, wfc.freight_cost_id,
        wfc.unit_amount, wfc.currency_code,
        wft.freight_cost_type_code
        , wfc.conversion_type_code, wfc.conversion_rate
FROM    wsh_freight_costs wfc,
        wsh_freight_cost_types wft,
        wsh_delivery_legs wdl,
        wsh_new_deliveries wnd,
        wsh_trip_stops wts
WHERE   wts.stop_id = p_stop_id and
        wts.stop_id = wdl.pick_up_stop_id and
        wts.stop_location_id = wnd.initial_pickup_location_id and
        wdl.delivery_id = wnd.delivery_id and
        wfc.delivery_id = wdl.delivery_id and
        wfc.freight_cost_type_id = wft.freight_cost_type_id and
        /* H projects: pricing integration csun
           pricing engine calculated freight costs are always at delivery detail level,
           however those lines also have delivery id populated, so those lines should be
           excluded from delivery level FC calculation
         */
        NVL(wfc.line_type_code, 'CHARGE') in ('PRICE', 'CHARGE') and
        NVL(wfc.charge_source_code, 'MANUAL')= 'MANUAL' and
        nvl(wnd.shipment_direction,'O') IN ('O','IO')
        AND wfc.unit_amount   IS NOT NULL  -- bug 5447870: skip NULL records
        AND wfc.currency_code IS NOT NULL
        order by wnd.delivery_id;
Line: 3034

SELECT   distinct
        wfc.delivery_detail_id,
        wfc.freight_cost_id,
        wfc.unit_amount,
        wfc.currency_code,
        wft.freight_cost_type_code,
        wfc.conversion_type_code,
        wfc.conversion_rate
FROM  wsh_delivery_assignments_v wda,
   wsh_delivery_details wdd,
   wsh_freight_costs wfc,
   wsh_freight_cost_types wft,
   wsh_delivery_legs wdl,
   wsh_new_deliveries wnd,
   wsh_trip_stops wts
WHERE wts.stop_id = wdl.pick_up_stop_id and
        wts.stop_id = p_stop_id and
   wts.stop_location_id = wnd.initial_pickup_location_id and
        wdl.delivery_id = wnd.delivery_id and
   wda.delivery_id = wdl.delivery_id and
   wda.delivery_id IS NOT NULL and
   wda.parent_delivery_detail_id = wdd.delivery_detail_id and
   wdd.container_flag = 'Y' and
   wdd.source_code='WSH' and
   wfc.delivery_detail_id = wdd.delivery_detail_id  and
        wfc.freight_cost_type_id = wft.freight_cost_type_id and
        NVL(wfc.line_type_code, 'CHARGE') in ('PRICE' ,'CHARGE','TLPRICE','TLCHARGE') and
   nvl(wdd.line_direction,'O') IN ('O','IO')
   AND wfc.unit_amount   IS NOT NULL  -- bug 5447870: skip NULL records
   AND wfc.currency_code IS NOT NULL;
Line: 3070

SELECT
   wfc.delivery_detail_id,
   wfc.freight_cost_id,
   wfc.unit_amount,
   wfc.currency_code,
   wft.freight_cost_type_code
   , wfc.conversion_type_code, wfc.conversion_rate
FROM  wsh_delivery_assignments_v wda,
   wsh_delivery_details wdd,
   oe_order_lines_all ol,
   wsh_freight_costs wfc,
   wsh_freight_cost_types wft,
   wsh_delivery_legs wdl,
   wsh_new_deliveries wnd,
   wsh_trip_stops wts
WHERE wts.stop_id = wdl.pick_up_stop_id and
        wts.stop_id = p_stop_id and
   wts.stop_location_id = wnd.initial_pickup_location_id and
        wdl.delivery_id = wnd.delivery_id and
   wda.delivery_id = wdl.delivery_id and
   wda.delivery_id IS NOT NULL and
   wda.delivery_detail_id = wdd.delivery_detail_id and
   wdd.container_flag = 'N' and
   ol.line_id = wdd.source_line_id and
   wdd.source_code = 'OE' and
   wfc.delivery_detail_id = wdd.delivery_detail_id  and
        wfc.freight_cost_type_id = wft.freight_cost_type_id and
   wdd.container_flag = 'N' and
        wdd.oe_interfaced_flag = 'N' and
        wdd.released_status = 'C' and
        NVL(wdd.shipped_quantity, 0) > 0 and
        NVL(wfc.line_type_code, 'CHARGE') in ('PRICE' ,'CHARGE','TLPRICE','TLCHARGE') --TKT
   AND wfc.unit_amount   IS NOT NULL  -- bug 5447870: skip NULL records
   AND wfc.currency_code IS NOT NULL;
Line: 3287

         g_container_relationship.delete;
Line: 3675

SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE source_line_id = p_source_line_id
   AND source_code = 'OE'
   AND container_flag = 'N';
Line: 3726

   SELECT wsh.source_line_id
   INTO l_line_id
   FROM wsh_delivery_details wsh, oe_order_lines_all l
   WHERE wsh.delivery_detail_id = p_delivery_detail_id AND
         wsh.source_line_id = l.line_id and
         wsh.source_code = 'OE';
Line: 3733

   SELECT COUNT(*)
   INTO l_check
   FROM oe_charge_lines_v
   WHERE line_id = l_line_id AND charge_id = p_charge_id;
Line: 3743

   SELECT COUNT(*)
   INTO l_number_details_assigned
   FROM wsh_delivery_details
   WHERE source_line_id = l_line_id and
         source_code = 'OE';
Line: 3754

   SELECT requested_quantity_uom
   INTO l_standard_uom
   FROM wsh_delivery_details
   WHERE delivery_detail_id = l_detail_id;
Line: 3764

      SELECT shipped_quantity, requested_quantity_uom, inventory_item_id
      INTO l_unit, l_uom , l_inventory_item_id
      FROM wsh_delivery_details
      WHERE delivery_detail_id = l_detail_id;
Line: 3786

   SELECT charge_amount, currency_code
   INTO l_amount, l_currency_code
   FROM oe_charge_lines_v
   WHERE charge_id = p_charge_id;
Line: 3791

   SELECT shipped_quantity, requested_quantity_uom , inventory_item_id
   INTO l_unit, l_uom , l_inventory_item_id
   FROM wsh_delivery_details
   WHERE delivery_detail_id = p_delivery_detail_id;