APPS.WSH_SC_DELIVERY_PVT SQL Statements

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

Line: 76

  select sysdate into close_date from sys.dual;

Line: 77

  UPDATE wsh_deliveries
  SET status_code = 'CL',
      date_closed = close_date
  WHERE delivery_id = del_id;

Line: 88

  SELECT actual_departure_id
	 ,organization_id
	 ,freight_carrier_code
	 ,weight_uom_code
	 ,volume_uom_code
	 ,gross_weight
	 ,volume
	 ,waybill
  INTO  dep_id
	,org_id
	,freight_carrier
	,weight_uom
	,volume_uom
	,weight_of_delivery
	,volume_of_delivery
	,x_waybill
  FROM wsh_deliveries
  WHERE delivery_id = del_id;

Line: 108

    SELECT freight_carrier_code
      INTO dep_freight_carrier
      FROM wsh_departures
     WHERE departure_id = dep_id;

Line: 113

    UPDATE wsh_deliveries
    SET freight_carrier_code = dep_freight_carrier
    WHERE delivery_id = del_id;

Line: 139

      UPDATE wsh_deliveries
      SET actual_departure_id = dep_id,
      freight_carrier_code = NVL(default_fcc, freight_carrier)
      WHERE delivery_id = del_id;

Line: 153

        update_flag       => 'Y',
        menu_flag         => 'N',
        dpw_pack_flag     => 'N',
        master_weight_uom => weight_uom,
        net_weight        => X_net_weight,
        tare_weight       => X_tare_weight,
        master_volume_uom => volume_uom,
        volume            => X_volume,
        status            => X_status);

Line: 164

        update wsh_departures
        set net_weight = X_net_weight,
        fill_percent = 0
        where departure_id = dep_id;

Line: 196

    UPDATE so_picking_headers_all
    SET waybill_num = x_waybill,
        STATUS_CODE = 'PENDING'
    WHERE delivery_id = del_id;

Line: 201

    UPDATE so_picking_headers_all
    SET waybill_num = x_waybill
    WHERE delivery_id = del_id;

Line: 207

  UPDATE so_picking_line_details
  SET departure_id = dep_id
  WHERE delivery_id = del_id;

Line: 212

  wsh_sc_pld_pkg.delete_from_msnt(del_id);

Line: 244

  SELECT report_set_id, organization_id
  FROM wsh_deliveries
  WHERE delivery_id = x_del_id;

Line: 315

    SELECT actual_departure_id
    FROM wsh_deliveries
    WHERE delivery_id = x_del_id;

Line: 324

  wsh_sc_del_lines.update_shp_qty (del_id, 'BACKORDER_ALL');

Line: 327

  delete from so_freight_charges
  where delivery_id = del_id;

Line: 331

  delete from wsh_packed_containers
  where delivery_id = del_id;

Line: 340

  UPDATE so_picking_line_details
  SET departure_id = dep_id,
      container_id = NULL,
      shipped_quantity = 0
  WHERE delivery_id = del_id;

Line: 348

  wsh_sc_del_lines.update_unrel_lines(del_id);

Line: 358

  UPDATE so_picking_headers_all
  SET status_code = 'PENDING'
  WHERE picking_header_id in
   (SELECT distinct pl.picking_header_id
    FROM so_picking_lines_all pl, so_picking_line_details pld
    WHERE pld.delivery_id = del_id
    AND	 pl.picking_line_id = pld.picking_line_id
    AND   pl.picking_header_id+0 > 0);

Line: 367

  update wsh_deliveries
  set status_code = 'CB'
  where delivery_id = del_id;

Line: 409

  UPDATE so_picking_line_details
  SET container_id = null
  WHERE delivery_id = Del_Id;

Line: 414

  SELECT source_code
  INTO Source
  FROM wsh_deliveries
  WHERE delivery_id = Del_Id;

Line: 422

    UPDATE wsh_deliveries
    SET status_code = 'PL'
    WHERE delivery_id = Del_Id;

Line: 426

    UPDATE wsh_deliveries
    SET status_code = 'OP'
    WHERE delivery_id = Del_Id;

Line: 457

PROCEDURE Update_Del_Status(    Del_Id          IN      NUMBER,
                                Del_Status_Code IN      VARCHAR2) IS
BEGIN

  --just a simple and stupid statement to update the delivery status
  UPDATE wsh_deliveries
  SET status_code = del_status_code
  WHERE delivery_id = Del_Id;

Line: 466

END Update_Del_Status;

Line: 480

PROCEDURE Insert_Ph_Row (       Ph_Id           IN      NUMBER,
                                New_Ph_Id       IN      NUMBER) IS
BEGIN

  --Go ahead and insert a record in so_picking_headers_all
  INSERT INTO so_picking_headers_all
    ( PICKING_HEADER_ID
    ,CREATION_DATE
    ,CREATED_BY
    ,LAST_UPDATE_DATE
    ,LAST_UPDATED_BY
    ,LAST_UPDATE_LOGIN
    ,PROGRAM_APPLICATION_ID
    ,PROGRAM_ID
    ,PROGRAM_UPDATE_DATE
    ,REQUEST_ID
    ,BATCH_ID
    ,ORDER_HEADER_ID
    ,WAREHOUSE_ID
    ,SHIP_TO_SITE_USE_ID
    ,STATUS_CODE
    ,PICK_SLIP_NUMBER
    ,WAYBILL_NUM
    ,PICKED_BY_ID
    ,PACKED_BY_ID
    ,WEIGHT
    ,WEIGHT_UNIT_CODE
    ,NUMBER_OF_BOXES
    ,SHIP_METHOD_CODE
    ,DATE_RELEASED
    ,DATE_SHIPPED
    ,DATE_CONFIRMED
    ,CONTEXT
    ,ATTRIBUTE1
    ,ATTRIBUTE2
    ,ATTRIBUTE3
    ,ATTRIBUTE4
    ,ATTRIBUTE5
    ,ATTRIBUTE6
    ,ATTRIBUTE7
    ,ATTRIBUTE8
    ,ATTRIBUTE9
    ,ATTRIBUTE10
    ,ATTRIBUTE11
    ,ATTRIBUTE12
    ,ATTRIBUTE13
    ,ATTRIBUTE14
    ,ATTRIBUTE15
    ,EXPECTED_ARRIVAL_DATE
    ,ORG_ID
    ,SHIP_NOTICE_SENT_DATE
    ,SHIP_NOTICE_SENT_FLAG
    ,DELIVERY_ID
    ,ARRIVED_FLAG )
  SELECT
    new_ph_id
    ,SYSDATE
    ,fnd_global.user_id
    ,SYSDATE
    ,fnd_global.user_id
    ,fnd_global.login_id
    ,PROGRAM_APPLICATION_ID
    ,PROGRAM_ID
    ,PROGRAM_UPDATE_DATE
    ,REQUEST_ID
    ,BATCH_ID
    ,ORDER_HEADER_ID
    ,WAREHOUSE_ID
    ,SHIP_TO_SITE_USE_ID
    ,STATUS_CODE
    ,PICK_SLIP_NUMBER
    ,WAYBILL_NUM
    ,PICKED_BY_ID
    ,PACKED_BY_ID
    ,WEIGHT
    ,WEIGHT_UNIT_CODE
    ,NUMBER_OF_BOXES
    ,SHIP_METHOD_CODE
    ,DATE_RELEASED
    ,DATE_SHIPPED
    ,DATE_CONFIRMED
    ,CONTEXT
    ,ATTRIBUTE1
    ,ATTRIBUTE2
    ,ATTRIBUTE3
    ,ATTRIBUTE4
    ,ATTRIBUTE5
    ,ATTRIBUTE6
    ,ATTRIBUTE7
    ,ATTRIBUTE8
    ,ATTRIBUTE9
    ,ATTRIBUTE10
    ,ATTRIBUTE11
    ,ATTRIBUTE12
    ,ATTRIBUTE13
    ,ATTRIBUTE14
    ,ATTRIBUTE15
    ,EXPECTED_ARRIVAL_DATE
    ,ORG_ID
    ,SHIP_NOTICE_SENT_DATE
    ,SHIP_NOTICE_SENT_FLAG
    ,DELIVERY_ID
    ,ARRIVED_FLAG
  FROM so_picking_headers_all
  WHERE picking_header_id = Ph_Id;

Line: 586

END Insert_Ph_Row;

Line: 601

PROCEDURE Insert_Pl_Row (       Pl_Id           IN      NUMBER,
                                New_Pl_Id       IN      NUMBER,
                                New_Ph_Id       IN      NUMBER) IS
BEGIN

  --Insert a row in so_picking_lines_all
  INSERT INTO so_picking_lines_all
  ( PICKING_LINE_ID
  ,CREATION_DATE
  ,CREATED_BY
  ,LAST_UPDATE_DATE
  ,LAST_UPDATED_BY
  ,LAST_UPDATE_LOGIN
  ,PROGRAM_APPLICATION_ID
  ,PROGRAM_ID
  ,PROGRAM_UPDATE_DATE
  ,REQUEST_ID
  ,PICKING_HEADER_ID
  ,SEQUENCE_NUMBER
  ,ORDER_LINE_ID
  ,COMPONENT_CODE
  ,LINE_DETAIL_ID
  ,COMPONENT_RATIO
  ,REQUESTED_QUANTITY
  ,INVENTORY_ITEM_ID
  ,INCLUDED_ITEM_FLAG
  ,DATE_REQUESTED
  ,ORIGINAL_REQUESTED_QUANTITY
  ,WAREHOUSE_ID
  ,SHIPPED_QUANTITY
  ,CANCELLED_QUANTITY
  ,SHIP_TO_SITE_USE_ID
  ,SHIP_TO_CONTACT_ID
  ,SHIPMENT_PRIORITY_CODE
  ,SHIP_METHOD_CODE
  ,DATE_CONFIRMED
  ,RA_INTERFACE_STATUS
  ,INVOICED_QUANTITY
  ,INVENTORY_STATUS
  ,UNIT_CODE
  ,CONTEXT
  ,ATTRIBUTE1
  ,ATTRIBUTE2
  ,ATTRIBUTE3
  ,ATTRIBUTE4
  ,ATTRIBUTE5
  ,ATTRIBUTE6
  ,ATTRIBUTE7
  ,ATTRIBUTE8
  ,ATTRIBUTE9
  ,ATTRIBUTE10
  ,ATTRIBUTE11
  ,ATTRIBUTE12
  ,ATTRIBUTE13
  ,ATTRIBUTE14
  ,ATTRIBUTE15
  ,SCHEDULE_DATE
  ,DEMAND_CLASS_CODE
  ,COMPONENT_SEQUENCE_ID
  ,CONFIGURATION_ITEM_FLAG
  ,LATEST_ACCEPTABLE_DATE
  ,MOVEMENT_ID
  ,ORG_ID
  ,TRANSACTION_HEADER_ID
  ,SERVICE_INTERFACE_STATUS
  ,BO_PICKING_LINE_ID
  ,DEP_PLAN_REQUIRED_FLAG
  ,CUSTOMER_ITEM_ID )
  SELECT
  new_pl_id
  ,SYSDATE
  ,fnd_global.user_id
  ,SYSDATE
  ,fnd_global.user_id
  ,fnd_global.login_id
  ,PROGRAM_APPLICATION_ID
  ,PROGRAM_ID
  ,PROGRAM_UPDATE_DATE
  ,REQUEST_ID
  ,new_ph_id
  ,SEQUENCE_NUMBER
  ,ORDER_LINE_ID
  ,COMPONENT_CODE
  ,LINE_DETAIL_ID
  ,COMPONENT_RATIO
  ,REQUESTED_QUANTITY
  ,INVENTORY_ITEM_ID
  ,INCLUDED_ITEM_FLAG
  ,DATE_REQUESTED
  ,ORIGINAL_REQUESTED_QUANTITY
  ,WAREHOUSE_ID
  ,SHIPPED_QUANTITY
  ,CANCELLED_QUANTITY
  ,SHIP_TO_SITE_USE_ID
  ,SHIP_TO_CONTACT_ID
  ,SHIPMENT_PRIORITY_CODE
  ,SHIP_METHOD_CODE
  ,DATE_CONFIRMED
  ,RA_INTERFACE_STATUS
  ,INVOICED_QUANTITY
  ,INVENTORY_STATUS
  ,UNIT_CODE
  ,CONTEXT
  ,ATTRIBUTE1
  ,ATTRIBUTE2
  ,ATTRIBUTE3
  ,ATTRIBUTE4
  ,ATTRIBUTE5
  ,ATTRIBUTE6
  ,ATTRIBUTE7
  ,ATTRIBUTE8
  ,ATTRIBUTE9
  ,ATTRIBUTE10
  ,ATTRIBUTE11
  ,ATTRIBUTE12
  ,ATTRIBUTE13
  ,ATTRIBUTE14
  ,ATTRIBUTE15
  ,SCHEDULE_DATE
  ,DEMAND_CLASS_CODE
  ,COMPONENT_SEQUENCE_ID
  ,CONFIGURATION_ITEM_FLAG
  ,LATEST_ACCEPTABLE_DATE
  ,MOVEMENT_ID
  ,ORG_ID
  ,TRANSACTION_HEADER_ID
  ,SERVICE_INTERFACE_STATUS
  ,BO_PICKING_LINE_ID
  ,DEP_PLAN_REQUIRED_FLAG
  ,CUSTOMER_ITEM_ID
  FROM so_picking_lines_all
  WHERE picking_line_id = Pl_Id;

Line: 734

END Insert_Pl_Row;

Line: 758

  SELECT distinct pl.picking_header_id picking_header_id
  FROM so_picking_lines_all pl, so_picking_line_details pld
  WHERE	pld.delivery_id = del_id
  AND	pld.picking_line_id = pl.picking_line_id
  AND	pl.picking_header_id > 0;

Line: 766

  SELECT picking_line_id
  FROM so_picking_lines_all
  WHERE picking_header_id = ph_id;

Line: 782

    UPDATE so_picking_headers_all
    SET delivery_id = del_id
    WHERE picking_header_id = ph_id;

Line: 786

    SELECT COUNT(*)
    into num_found
    FROM so_picking_line_details pld, so_picking_lines_all pl
    WHERE pl.picking_header_id = ph_id
    AND   pl.picking_line_id = pld.picking_line_id
    AND   ( pld.delivery_id <> del_id  OR
	   pld.delivery_id IS NULL ) ;

Line: 799

      select so_picking_headers_s.nextval
      into new_ph_id
      from dual;

Line: 803

      insert_ph_row (ph_id, new_ph_id);

Line: 805

      UPDATE so_picking_headers_all
      set delivery_id = NULL
      where picking_header_id = ph_id;

Line: 815

  	SELECT COUNT(*)
        INTO num_found
        FROM so_picking_line_details
        WHERE picking_line_id = pl_id
        AND   ( delivery_id <> del_id OR
	 	delivery_id IS NULL ) ;

Line: 824

  	  SELECT COUNT(*)
  	  INTO num_found
  	  FROM so_picking_line_details
  	  WHERE picking_line_id = pl_id
  	  AND delivery_id = del_id;

Line: 832

  	    --insert a new row into so_picking_lines_all by using the row with
  	    --picking_line_id = pl_id and ph_id = new ph id (except the
  	    --requested_quantity, shipped_quantity, and who info).

  	    --get the new picking line id
	    select so_picking_lines_s.nextval
  	    into new_pl_id
	    from dual;

Line: 841

	    insert_pl_row (pl_id, new_pl_id, new_ph_id);

Line: 844

  	    UPDATE so_picking_line_details
  	    SET picking_line_id = new_pl_id
  	    WHERE picking_line_id = pl_id
  	    AND   delivery_id = del_id;

Line: 849

	    -- now update the shipped and requested quantities on this new picking line
  	    SELECT sum(nvl(shipped_quantity,0)), sum(nvl(requested_quantity,0))
  	    INTO shp_qty, req_qty
  	    FROM so_picking_line_details
  	    WHERE picking_line_id = new_pl_id
  	    GROUP BY picking_line_id;

Line: 856

  	    UPDATE so_picking_lines_all
  	    SET   shipped_quantity = shp_qty,
  	    requested_quantity = req_qty,
            original_requested_quantity = req_qty
  	    where picking_line_id = new_pl_id;

Line: 862

	    -- also update the shipped and requested quantities on the old picking line
  	    SELECT sum(nvl(shipped_quantity,0)), sum(nvl(requested_quantity,0))
  	    INTO shp_qty, req_qty
  	    FROM so_picking_line_details
  	    WHERE picking_line_id = pl_id
  	    GROUP BY picking_line_id;

Line: 869

  	    UPDATE so_picking_lines_all
  	    SET   shipped_quantity = shp_qty,
  	    requested_quantity = req_qty,
            original_requested_quantity = req_qty
  	    where picking_line_id = pl_id;

Line: 885

            UPDATE so_picking_lines_all
            SET picking_header_id = new_ph_id
            WHERE picking_line_id = pl_id;

Line: 951

  select WSH_DEPARTURES_S.nextval into dep_id
  FROM DUAL;

Line: 965

  INSERT INTO WSH_DEPARTURES (
  ORGANIZATION_ID
  ,DEPARTURE_ID
  ,NAME
  ,SOURCE_CODE
  ,ARRIVE_AFTER_DEPARTURE_ID
  ,STATUS_CODE
  ,REPORT_SET_ID
  ,DATE_CLOSED
  ,VEHICLE_ITEM_ID
  ,VEHICLE_NUMBER
  ,FREIGHT_CARRIER_CODE
  ,PLANNED_DEPARTURE_DATE
  ,ACTUAL_DEPARTURE_DATE
  ,BILL_OF_LADING
  ,GROSS_WEIGHT
  ,NET_WEIGHT
  ,WEIGHT_UOM_CODE
  ,VOLUME
  ,VOLUME_UOM_CODE
  ,FILL_PERCENT
  ,SEAL_CODE
  ,ROUTING_INSTRUCTIONS
  ,ATTRIBUTE_CATEGORY
  ,ATTRIBUTE1
  ,ATTRIBUTE2
  ,ATTRIBUTE3
  ,ATTRIBUTE4
  ,ATTRIBUTE5
  ,ATTRIBUTE6
  ,ATTRIBUTE7
  ,ATTRIBUTE8
  ,ATTRIBUTE9
  ,ATTRIBUTE10
  ,ATTRIBUTE11
  ,ATTRIBUTE12
  ,ATTRIBUTE13
  ,ATTRIBUTE14
  ,ATTRIBUTE15
  ,CREATION_DATE
  ,CREATED_BY
  ,LAST_UPDATE_DATE
  ,LAST_UPDATED_BY
  ,LAST_UPDATE_LOGIN
  ,PROGRAM_APPLICATION_ID
  ,PROGRAM_ID
  ,PROGRAM_UPDATE_DATE
  ,REQUEST_ID )
  VALUES
  (
  Org_Id /*ORGANIZATION_ID */
  ,dep_id /* DEPARTURE_ID */
  ,NVL( dep_name, to_char(dep_id)) /* NAME */
  ,'S' /* SOURCE_CODE */
  ,NULL /* ARRIVE_AFTER_DEPARTURE_ID */
  ,'CL' /* STATUS_CODE */
  ,rep_id /* REPORT_SET_ID */
  ,SYSDATE /* DATE_CLOSED */
  ,p_vehicle_item_id /* VEHICLE_ITEM_ID */
  ,p_vehicle_number /* VEHICLE_NUMBER */
  ,Freight_Carrier /* FREIGHT_CARRIER_CODE */
  ,SYSDATE /* PLANNED_DEPARTURE_DATE */
  ,NVL( actual_date, SYSDATE) /* ACTUAL_DEPARTURE_DATE */
  ,x_bol
  ,Weight_of_Delivery /* GROSS_WEIGHT */
  ,Weight_of_delivery - p_tare_wt /* NET_WEIGHT */
  ,Weight_UOM /* WEIGHT_UOM_CODE */
  ,Volume_of_Delivery /* VOLUME */
  ,Volume_UOM /* VOLUME_UOM_CODE */
  ,NULL /* FILL_PERCENT */
  ,p_seal_code /* SEAL_CODE */
  ,p_pack_instr /* ROUTING_INSTRUCTIONS */
  ,NULL /* ATTRIBUTE_CATEGORY */
  ,NULL /* ATTRIBUTE1 */
  ,NULL /* ATTRIBUTE2 */
  ,NULL /* ATTRIBUTE3 */
  ,NULL /* ATTRIBUTE4 */
  ,NULL /* ATTRIBUTE5 */
  ,NULL /* ATTRIBUTE6 */
  ,NULL /* ATTRIBUTE7 */
  ,NULL /* ATTRIBUTE8 */
  ,NULL /* ATTRIBUTE9 */
  ,NULL /* ATTRIBUTE10 */
  ,NULL /* ATTRIBUTE11 */
  ,NULL /* ATTRIBUTE12 */
  ,NULL /* ATTRIBUTE13 */
  ,NULL /* ATTRIBUTE14 */
  ,NULL /* ATTRIBUTE15 */
  ,SYSDATE /* CREATION_DATE */
  ,FND_GLOBAL.User_Id /* CREATED_BY */
  ,SYSDATE /* LAST_UPDATE_DATE */
  ,FND_GLOBAL.User_Id /* LAST_UPDATED_BY */
  ,FND_GLOBAL.Login_Id /* LAST_UPDATE_LOGIN */
  ,300 /* PROGRAM_APPLICATION_ID  - 300 for order entry */
  ,NULL /* PROGRAM_ID */
  ,SYSDATE /* PROGRAM_UPDATE_DATE */
  ,NULL /* REQUEST_ID */
  );

Line: 1088

FUNCTION Delete_Container_Contents( x_container_id      IN NUMBER)
RETURN BOOLEAN IS

    CURSOR c1( x_cid    NUMBER) IS
    SELECT container_id FROM so_picking_line_details
    WHERE container_id = x_cid
    FOR UPDATE OF picking_line_detail_id NOWAIT;

Line: 1104

    UPDATE so_picking_line_details
    SET container_id = NULL
    WHERE container_id = x_container_id;

Line: 1117

END Delete_Container_Contents;