APPS.WSH_CONTAINER_UTILITIES SQL Statements

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

Line: 13

		SELECT statements that need to use the master container id.
------------------------------------------------------------------------------
*/


--
G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CONTAINER_UTILITIES';

Line: 26

 SELECT delivery_detail_id
 FROM wsh_delivery_assignments
 WHERE parent_delivery_detail_id IS NULL
 AND  NVL(type, 'S') in ('S', 'C')
 START WITH delivery_detail_id = v_cont_inst_id
 AND  NVL(type, 'S') in ('S', 'C')
 CONNECT BY PRIOR parent_delivery_detail_id = delivery_detail_id;

Line: 79

 SELECT container_name
 FROM WSH_DELIVERY_DETAILS
 WHERE delivery_detail_id = v_cont_inst_id
 AND container_flag  in ('Y', 'C');

Line: 268

SELECT max_load_quantity, container_item_id
FROM WSH_CONTAINER_ITEMS
WHERE container_item_id = NVL(v_cont_id, container_item_id)
AND load_item_id = v_inv_item_id
AND master_organization_id = p_in_record.organization_id
AND preferred_flag = DECODE(nvl(v_cont_id,-99),-99,'Y',preferred_flag);

Line: 276

SELECT indivisible_flag
  FROM mtl_system_items
 WHERE organization_id = v_org_id
  AND inventory_item_id  = v_inv_item_id;

Line: 282

SELECT delivery_detail_id, inventory_item_id, item_description,
       nvl(shipped_quantity,
           NVL(picked_quantity, requested_quantity)) packed_qty,
       requested_quantity_uom,
       master_container_item_id, detail_container_item_id, hold_code,
       load_seq_number, net_weight,
       weight_uom_code, volume, volume_uom_code, organization_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_del_detail_id
AND container_flag = 'N';

Line: 294

SELECT delivery_detail_id container_instance_id, container_name lpn,
       inventory_item_id container_item_id, item_description,
       gross_weight, net_weight, (gross_weight - net_weight), weight_uom_code,
       volume, volume_uom_code, fill_percent, maximum_load_weight,
       maximum_volume, minimum_fill_percent
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_cont_instance_id
AND organization_id = v_org_id
AND  container_flag  in ('Y', 'C');

Line: 305

SELECT maximum_load_weight, internal_volume, weight_uom_code, volume_uom_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = v_cont_item_id
AND organization_id = v_org_id;

Line: 311

SELECT percent_fill_basis_flag
FROM WSH_SHIPPING_PARAMETERS
WHERE organization_id = v_org_id;

Line: 316

SELECT sum(nvl(wdd.shipped_quantity,
           NVL(wdd.picked_quantity, wdd.requested_quantity))) packed_qty
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id IN
   (SELECT wda.delivery_detail_id
      FROM wsh_delivery_assignments_v wda
     WHERE wda.parent_delivery_detail_id IS NOT NULL
       AND wda.parent_delivery_detail_id = v_container_instance_id)
 AND wdd.container_flag ='N';

Line: 1049

SELECT max_load_quantity, container_item_id
FROM WSH_CONTAINER_ITEMS
WHERE container_item_id = NVL(v_cont_id, container_item_id)
AND load_item_id = v_inv_item_id
AND master_organization_id = p_organization_id
AND preferred_flag = DECODE(nvl(v_cont_id,-99),-99,'Y',preferred_flag);

Line: 1057

SELECT delivery_detail_id, inventory_item_id, item_description,
       nvl(shipped_quantity,
           NVL(picked_quantity, requested_quantity)) packed_qty,
       requested_quantity_uom,
       master_container_item_id, detail_container_item_id, hold_code,
       load_seq_number, net_weight,
       weight_uom_code, volume, volume_uom_code, organization_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_del_detail_id
AND container_flag = 'N';

Line: 1069

SELECT delivery_detail_id container_instance_id, container_name lpn,
       inventory_item_id container_item_id, item_description,
       gross_weight, net_weight, (gross_weight - net_weight), weight_uom_code,
       volume, volume_uom_code, fill_percent, maximum_load_weight,
       maximum_volume, minimum_fill_percent
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_cont_instance_id
AND organization_id = v_org_id
AND  container_flag  in ('Y', 'C');

Line: 1080

SELECT maximum_load_weight, internal_volume, weight_uom_code, volume_uom_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = v_cont_item_id
AND organization_id = v_org_id;

Line: 1086

SELECT percent_fill_basis_flag
FROM WSH_SHIPPING_PARAMETERS
WHERE organization_id = v_org_id;

Line: 1717

SELECT max_load_quantity, container_item_id
FROM WSH_CONTAINER_ITEMS
WHERE container_item_id = NVL(v_cont_id, container_item_id)
AND load_item_id = v_inv_item_id
AND master_organization_id = p_organization_id
AND preferred_flag = DECODE(nvl(v_cont_id,-99),-99,'Y',preferred_flag);

Line: 1730

SELECT delivery_detail_id container_instance_id, container_name lpn,
       inventory_item_id container_item_id, item_description,
       requested_quantity,requested_quantity_uom,
       gross_weight, net_weight, (gross_weight - net_weight), weight_uom_code,
       volume, volume_uom_code, fill_percent,
       minimum_fill_percent, organization_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_cont_instance_id
AND organization_id = nvl(v_org_id, organization_id)
AND  container_flag  in ('Y', 'C');

Line: 1742

SELECT maximum_load_weight, internal_volume, weight_uom_code, volume_uom_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = v_cont_item_id
AND organization_id = v_org_id;

Line: 1748

SELECT percent_fill_basis_flag
FROM WSH_SHIPPING_PARAMETERS
WHERE organization_id = v_org_id;

Line: 2230

SELECT delivery_detail_id
FROM wsh_delivery_assignments
WHERE parent_delivery_detail_id IS NULL
AND        NVL(type, 'S')       in ('S', 'C')
START WITH delivery_detail_id = v_cont_inst_id
AND        NVL(type, 'S')       in ('S', 'C')
CONNECT BY PRIOR parent_delivery_detail_id = delivery_detail_id;

Line: 2239

SELECT master_serial_number, delivery_detail_id, container_flag
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = v_detail_id;

Line: 2374

   PROCEDURE  : Update Child Containers
   PARAMETERS : p_container_instance_id - instance id for the container
		x_master_cont_instance_id - master container of the container
		x_master_serial_number - serial number of the master container
		x_return_status - return status of API
  DESCRIPTION : This procedure updates the master container instance id and
		master serial number of all the child containers. When the
		master serial number and master container instance id is
		changed on the master container, all the child containers are
		updated with the new values using this API.
------------------------------------------------------------------------------
*/


PROCEDURE Update_Child_Containers (
   p_container_instance_id IN NUMBER,
   p_master_cont_instance_id IN NUMBER,
   p_master_serial_number IN VARCHAR2,
   x_return_status OUT NOCOPY  VARCHAR2) IS

   CURSOR Get_Child_Containers(v_cont_instance_id NUMBER) IS
   SELECT delivery_detail_id
   FROM wsh_delivery_assignments_v
   START WITH parent_delivery_detail_id = v_cont_instance_id
   CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;

Line: 2401

   SELECT container_flag, nvl(line_direction, 'O'), organization_id
   from   wsh_delivery_details
   where  delivery_detail_id = p_detail_id;

Line: 2423

l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CHILD_CONTAINERS';

Line: 2454

     UPDATE WSH_DELIVERY_DETAILS
     SET    master_serial_number = p_master_serial_number
     WHERE  delivery_detail_id = p_container_instance_id
     RETURNING container_flag, NVL(line_direction, 'O'), organization_id
     INTO l_cnt_flag, l_line_direction, l_organization_id;

Line: 2461

       WSH_DEBUG_SV.log(l_module_name, 'Master serial number is updated on this container.', p_container_instance_id);

Line: 2478

         l_sync_tmp_rec.operation_type     := 'UPDATE';

Line: 2558

                l_sync_tmp_rec.operation_type := 'UPDATE';

Line: 2590

	UPDATE WSH_DELIVERY_DETAILS
	  SET master_serial_number = p_master_serial_number
	  WHERE delivery_detail_id = det.delivery_detail_id;

Line: 2626

	WSH_UTIL_CORE.Default_Handler('WSH_CONTAINER_UTILITIES.Update_Child_Containers');

Line: 2637

END Update_Child_Containers;

Line: 2729

  SELECT master_serial_number
  FROM WSH_DELIVERY_DETAILS
  WHERE delivery_detail_id = v_cont_instance_id;

Line: 2837

SELECT wda.delivery_detail_id, wda.delivery_id
FROM wsh_delivery_assignments_v wda,
     WSH_DELIVERY_DETAILS wdd
WHERE wda.parent_delivery_detail_id = v_cont_instance_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.container_flag = 'N'
AND rownum < 2
AND wda.parent_delivery_detail_id IS NOT NULL;

Line: 2945

  SELECT 1
    FROM wsh_delivery_details
   WHERE delivery_detail_id in (
         SELECT delivery_detail_id
           FROM wsh_delivery_assignments_v
          START WITH delivery_detail_id = v_delivery_detail_id
          CONNECT BY prior delivery_detail_id = parent_delivery_detail_id)
     AND container_flag = 'N';

Line: 3043

   SELECT fill_percent
   FROM WSH_DELIVERY_DETAILS
   WHERE delivery_detail_id = v_cont_instance_id
   AND  container_flag  in ('Y', 'C');

Line: 3157

SELECT wda.delivery_id, wnd.status_code
FROM wsh_delivery_assignments_v wda, WSH_NEW_DELIVERIES wnd, WSH_DELIVERY_DETAILS wdd
WHERE wda.delivery_detail_id = v_cont_instance_id
AND wda.delivery_id = wnd.delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.container_flag in ('Y', 'C');

Line: 3258

		container that needs to be updated.
		x_return_status - return status of API
  DESCRIPTION : This procedure takes in the container name and existing
		container id (detail id) and checks to see if the container
		that is being updated is assigned to a closed, confirmed or
		in-transit delivery. If it is, no update is allowed - if not,
		only the container name can be updated if the name is not a
		duplicate of an existing container.
------------------------------------------------------------------------------
*/


PROCEDURE Validate_Container (
  p_container_name IN VARCHAR2,
  p_container_instance_id IN NUMBER,
  x_return_status OUT NOCOPY  VARCHAR2) IS

CURSOR Check_Dup_Cont IS
SELECT delivery_detail_id
FROM WSH_DELIVERY_DETAILS
WHERE container_name = p_container_name
  AND container_flag in ('Y', 'C');