DBA Data[Home] [Help]

APPS.WSH_AUTO_MANIFEST SQL Statements

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

Line: 184

   SELECT distinct wnd.organization_id
   ,wnd.delivery_id
   ,wnd.carrier_id
   ,wnd.customer_id
   ,wnd.ultimate_dropoff_location_id ship_to_id
   FROM    wsh_new_deliveries wnd
   , wsh_transactions_history wth
   , wsh_delivery_assignments_v wdav
   , wsh_delivery_details wdd
   WHERE   nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO')
   AND  wdav.delivery_id = wnd.delivery_id
   AND  wdd.delivery_detail_id = wdav.delivery_detail_id
   AND  ((p_src_header_num_from IS NOT NULL
          AND wdd.source_header_number >= p_src_header_num_from)
         OR (p_src_header_num_from IS NULL))
   AND  ((p_src_header_num_to IS NOT NULL
          AND wdd.source_header_number <= p_src_header_num_to)
         OR (p_src_header_num_to IS NULL))
   AND    wnd.status_code in ('CL', 'IT', 'CO')
   AND    wnd.DELIVERY_TYPE <> 'CONSOLIDATION'
   AND ( ( p_organization_id   is not null
   AND  wnd.organization_id = p_organization_id)
       OR  (P_organization_id is null))
   AND  (( p_carrier_id is null )
       OR ( p_carrier_id is not null AND wnd.carrier_id = p_carrier_id))
   AND   ((p_customer_id IS NULL)
       OR ((p_customer_id IS NOT NULL) AND (wnd.customer_id = p_customer_id)))
   AND    wnd.ultimate_dropoff_location_id =
                   nvl(p_customer_ship_to_id,ultimate_dropoff_location_id)
   AND  ((p_scheduled_from_date IS NULL )
       OR  (wnd.confirm_date >=  p_scheduled_from_date))
   AND ((p_scheduled_to_date IS NULL)
       OR (wnd.confirm_date  <= p_scheduled_to_date))
   AND wth.entity_number =  wnd.name
   AND wth.document_direction = 'I'
   AND wth.document_type = 'SR'
   AND wth.entity_type = 'DLVY'
   AND NOT EXISTS (
                   SELECT entity_number
                   from wsh_transactions_history wth2
                   WHERE wth2.entity_number = wnd.name
                   AND wth2.document_type = 'SA'
                  ) ;
Line: 229

   SELECT distinct wnd.organization_id
   ,wnd.delivery_id
   ,wnd.carrier_id
   ,wnd.customer_id
   ,wnd.ultimate_dropoff_location_id ship_to_id
   FROM   wsh_delivery_details wdd,
   wsh_delivery_assignments_v wda,
   wsh_new_deliveries wnd,
   mtl_parameters mtl,
   wsh_carriers  wc
   WHERE  wdd.delivery_detail_id = wda.delivery_detail_id
   AND    wda.delivery_id = wnd.delivery_id
   AND    wda.delivery_detail_id = wdd.delivery_detail_id
   AND  ((p_src_header_num_from IS NOT NULL
          AND wdd.source_header_number >= p_src_header_num_from)
         OR (p_src_header_num_from IS NULL))
   AND  ((p_src_header_num_to IS NOT NULL
          AND wdd.source_header_number <= p_src_header_num_to)
         OR (p_src_header_num_to IS NULL))
   AND    nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO')
   AND    wdd.container_flag = 'N'
   AND    wnd.organization_id = NVL(p_organization_id , wnd.organization_id)
   AND    wnd.organization_id= mtl.organization_id
   AND    wnd.DELIVERY_TYPE <> 'CONSOLIDATION'
   AND    mtl.CARRIER_MANIFESTING_FLAG = 'Y'
   AND    wdd.released_status in ('X','Y')
   AND    wnd.status_code='OP'
   AND    wc.carrier_id = wnd.carrier_id
   AND    wc.MANIFESTING_ENABLED_FLAG = 'Y'
   AND    NVL(p_carrier_id, wnd.carrier_id ) = wnd.carrier_id
   AND    wnd.customer_id = nvl(p_customer_id,wnd.customer_id)
   AND    wnd.ultimate_dropoff_location_id =
                  nvl(p_customer_ship_to_id,ultimate_dropoff_location_id)
   AND    wda.delivery_id IS NOT NULL
   AND  ((p_scheduled_from_date IS NULL )
      OR  (wnd.initial_pickup_date >=  p_scheduled_from_date))
   AND ((p_scheduled_to_date IS NULL)
      OR ( wnd.initial_pickup_date  <= p_scheduled_to_date)) ;
Line: 269

   SELECT distinct wnd.organization_id,
    wnd.delivery_id
   ,wnd.carrier_id
   ,wnd.customer_id
   ,wnd.ultimate_dropoff_location_id ship_to_id
   FROM   wsh_delivery_details wdd,
   wsh_delivery_assignments_v wda,
   wsh_new_deliveries wnd,
   mtl_parameters mtl
   WHERE  wdd.delivery_detail_id = wda.delivery_detail_id
   AND    wda.delivery_id = wnd.delivery_id
   AND    nvl(wnd.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO')
   AND    wdd.container_flag = 'N'
   AND  ((p_src_header_num_from IS NOT NULL
          AND wdd.source_header_number >= p_src_header_num_from)
         OR (p_src_header_num_from IS NULL))
   AND  ((p_src_header_num_to IS NOT NULL
          AND wdd.source_header_number <= p_src_header_num_to)
         OR (p_src_header_num_to IS NULL))
   AND    wnd.organization_id = NVL(p_organization_id , wnd.organization_id)
   AND    wnd.organization_id= mtl.organization_id
   AND    mtl.DISTRIBUTED_ORGANIZATION_FLAG ='Y'
   AND    wdd.released_status in ('X','R','B')
   AND    wnd.status_code='OP'
   AND    wnd.DELIVERY_TYPE <> 'CONSOLIDATION'
   AND    (  ( p_carrier_id is null)
           OR  (p_carrier_id IS NOT NULL AND wnd.carrier_id = p_carrier_id))
   AND    wnd.customer_id = nvl(p_customer_id,wnd.customer_id)
   AND    wnd.ultimate_dropoff_location_id =
              nvl(p_customer_ship_to_id,ultimate_dropoff_location_id)
   AND    wda.delivery_id IS NOT NULL
   AND  ((p_scheduled_from_date IS NULL )
          OR  (wnd.initial_pickup_date >=  p_scheduled_from_date))
   AND ((p_scheduled_to_date IS NULL)
          OR ( wnd.initial_pickup_date  <= p_scheduled_to_date));
Line: 306

  SELECT party_name
  FROM   wsh_carriers, hz_parties
  WHERE  carrier_id =party_id (+)
  AND    carrier_id= pc_carrier_id;
Line: 312

  SELECT HP.PARTY_NAME
  FROM   HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP
  WHERE  HP.PARTY_ID = HCA.PARTY_ID
  AND    HP.PARTY_ID = pc_customer_id;
Line: 320

  SELECT wclv.ui_location_code
  FROM
         wsh_customer_locations_v wclv
  WHERE  wclv.wsh_location_id = pc_location_id
  AND    wclv.customer_status = 'A'
  AND    wclv.cust_acct_site_status = 'A'
  AND    wclv.site_use_status = 'A'
  AND    wclv.site_use_code  = 'SHIP_TO';
Line: 650

          wsh_debug_sv.log(l_module_name,'All Deliveries selected are having error');
Line: 928

  SELECT       wnd.delivery_id, wnd.status_code
   FROM         wsh_delivery_details wdd,
                wsh_delivery_assignments_v wda,
                wsh_new_deliveries wnd
   WHERE        wdd.delivery_detail_id = wda.delivery_detail_id
   AND          wda.delivery_id = wnd.delivery_id
   AND          wdd.container_flag = 'N'
   AND          wdd.released_status in ('X','Y')
   AND          wnd.status_code='OP'
   AND          wnd.delivery_id=p_delivery_id
   AND		wda.delivery_id IS NOT NULL
   FOR UPDATE NOWAIT;
Line: 1004

 SELECT count(*)
      FROM   wsh_delivery_details wdd,
             wsh_delivery_assignments_v wda
      WHERE wdd.delivery_detail_id = wda.delivery_detail_id
      AND   wda.delivery_id = p_delivery_id
      AND   wdd.container_flag = 'N'
      AND   wdd.released_status in ('X','Y')
      AND   wda.delivery_id IS NOT NULL
      AND   trunc(wdd.DATE_SCHEDULED) between trunc(p_scheduled_from_date) AND trunc(p_scheduled_to_date);
Line: 1015

 SELECT count(*)
      FROM   wsh_delivery_details wdd,
             wsh_delivery_assignments_v wda
      WHERE wdd.delivery_detail_id = wda.delivery_detail_id
      AND   wda.delivery_id = p_delivery_id
      AND   wdd.container_flag = 'N'
      AND   wdd.released_status in ('X','Y')
      AND   wda.delivery_id IS NOT NULL
      AND   trunc(wdd.DATE_SCHEDULED) >= trunc(p_scheduled_from_date);
Line: 1026

 SELECT count(*)
      FROM   wsh_delivery_details wdd,
             wsh_delivery_assignments_v wda
      WHERE wdd.delivery_detail_id = wda.delivery_detail_id
      AND   wda.delivery_id = p_delivery_id
      AND   wdd.container_flag = 'N'
      AND   wdd.released_status in ('X','Y')
      AND   wda.delivery_id IS NOT NULL
      AND   trunc(wdd.DATE_SCHEDULED) <= trunc(p_scheduled_to_date);