The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sysdate into close_date from sys.dual;
UPDATE wsh_deliveries
SET status_code = 'CL',
date_closed = close_date
WHERE delivery_id = del_id;
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;
SELECT freight_carrier_code
INTO dep_freight_carrier
FROM wsh_departures
WHERE departure_id = dep_id;
UPDATE wsh_deliveries
SET freight_carrier_code = dep_freight_carrier
WHERE delivery_id = del_id;
UPDATE wsh_deliveries
SET actual_departure_id = dep_id,
freight_carrier_code = NVL(default_fcc, freight_carrier)
WHERE delivery_id = del_id;
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);
update wsh_departures
set net_weight = X_net_weight,
fill_percent = 0
where departure_id = dep_id;
UPDATE so_picking_headers_all
SET waybill_num = x_waybill,
STATUS_CODE = 'PENDING'
WHERE delivery_id = del_id;
UPDATE so_picking_headers_all
SET waybill_num = x_waybill
WHERE delivery_id = del_id;
UPDATE so_picking_line_details
SET departure_id = dep_id
WHERE delivery_id = del_id;
wsh_sc_pld_pkg.delete_from_msnt(del_id);
SELECT report_set_id, organization_id
FROM wsh_deliveries
WHERE delivery_id = x_del_id;
SELECT actual_departure_id
FROM wsh_deliveries
WHERE delivery_id = x_del_id;
wsh_sc_del_lines.update_shp_qty (del_id, 'BACKORDER_ALL');
delete from so_freight_charges
where delivery_id = del_id;
delete from wsh_packed_containers
where delivery_id = del_id;
UPDATE so_picking_line_details
SET departure_id = dep_id,
container_id = NULL,
shipped_quantity = 0
WHERE delivery_id = del_id;
wsh_sc_del_lines.update_unrel_lines(del_id);
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);
update wsh_deliveries
set status_code = 'CB'
where delivery_id = del_id;
UPDATE so_picking_line_details
SET container_id = null
WHERE delivery_id = Del_Id;
SELECT source_code
INTO Source
FROM wsh_deliveries
WHERE delivery_id = Del_Id;
UPDATE wsh_deliveries
SET status_code = 'PL'
WHERE delivery_id = Del_Id;
UPDATE wsh_deliveries
SET status_code = 'OP'
WHERE delivery_id = Del_Id;
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;
END Update_Del_Status;
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;
END Insert_Ph_Row;
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;
END Insert_Pl_Row;
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;
SELECT picking_line_id
FROM so_picking_lines_all
WHERE picking_header_id = ph_id;
UPDATE so_picking_headers_all
SET delivery_id = del_id
WHERE picking_header_id = ph_id;
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 ) ;
select so_picking_headers_s.nextval
into new_ph_id
from dual;
insert_ph_row (ph_id, new_ph_id);
UPDATE so_picking_headers_all
set delivery_id = NULL
where picking_header_id = ph_id;
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 ) ;
SELECT COUNT(*)
INTO num_found
FROM so_picking_line_details
WHERE picking_line_id = pl_id
AND delivery_id = del_id;
--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;
insert_pl_row (pl_id, new_pl_id, new_ph_id);
UPDATE so_picking_line_details
SET picking_line_id = new_pl_id
WHERE picking_line_id = pl_id
AND delivery_id = del_id;
-- 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;
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;
-- 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;
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;
UPDATE so_picking_lines_all
SET picking_header_id = new_ph_id
WHERE picking_line_id = pl_id;
select WSH_DEPARTURES_S.nextval into dep_id
FROM DUAL;
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 */
);
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;
UPDATE so_picking_line_details
SET container_id = NULL
WHERE container_id = x_container_id;
END Delete_Container_Contents;