[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Mesg IS
BEGIN
insert into wsh_upgrade_log
( error_mesg )
values
( error_mesg
);
PROCEDURE Insert_Mesg( x_mesg IN VARCHAR2) IS
BEGIN
error_mesg := substr(x_mesg,1,2000);
insert into wsh_upgrade_log
( error_mesg )
values
( error_mesg
);
CURSOR C2 IS SELECT wsh_new_deliveries_s.nextval FROM sys.dual;
SELECT 1
FROM wsh_deliveries
WHERE delivery_id = l_delivery_id;
Insert_mesg;
CURSOR C2 IS SELECT wsh_trips_s.nextval FROM sys.dual;
SELECT 1
FROM wsh_departures
WHERE departure_id = l_departure_id;
Insert_mesg;
FUNCTION Insert_Row(
p_header_id IN BINARY_INTEGER,
p_departure_id IN OUT NOCOPY BINARY_INTEGER
)
RETURN BINARY_INTEGER IS
CURSOR get_picking_info(x_header_id IN BINARY_INTEGER) IS
SELECT order_header_id,
ship_to_site_use_id,
ship_method_code,
warehouse_id,
date_shipped,
date_confirmed,
waybill_num,
weight,
weight_unit_code,
picked_by_id,
packed_by_id,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_updated_by,
last_update_login,
last_update_date,
expected_arrival_date -- added: for bug 1413000
FROM SO_PICKING_HEADERS_ALL
WHERE picking_header_id = x_header_id;
SELECT NVL(CUSTOMER_ID,-1),
NVL(FOB_CODE, 'XX'),
NVL(FREIGHT_TERMS_CODE, 'XX'),
CURRENCY_CODE
FROM SO_HEADERS_ALL
WHERE HEADER_ID = x_header_id;
SELECT count(*)
FROM SO_PICKING_LINES_ALL
WHERE picking_header_id = x_header_id
AND nvl(shipped_quantity,0) > 0;
p_last_updated_by BINARY_INTEGER;
p_last_update_login BINARY_INTEGER;
p_last_update_date DATE;
p_last_updated_by,
p_last_update_login,
p_last_update_date,
p_expected_arrival_date;
p_date_shipped := p_last_update_date;
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,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id
) VALUES (
p_organization_id,
p_departure_id,
v_departure_name,
'S',
NULL,
'CL',
NULL,
p_date_confirmed,
NULL,
'',
p_ship_method_code,
p_date_shipped,
p_date_shipped,
NULL,
p_weight,
p_weight,
p_weight_unit_code,
NULL,
NULL,
NULL,
NULL,
NULL,
p_creation_date,
p_created_by,
SYSDATE,
p_last_updated_by,
p_last_update_login,
-999
);
INSERT INTO wsh_deliveries(
organization_id,
delivery_id,
name,
source_code,
planned_departure_id,
actual_departure_id,
status_code,
loading_order_flag,
date_closed,
report_set_id,
sequence_number,
customer_id,
ultimate_ship_to_id,
intermediate_ship_to_id,
pooled_ship_to_id,
waybill,
gross_weight,
weight_uom_code,
volume,
volume_uom_code,
picked_by_id,
packed_by_id,
expected_arrival_date,
asn_date_sent,
asn_seq_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
freight_carrier_code,
freight_terms_code,
currency_code,
fob_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id
) VALUES (
p_organization_id,
v_delivery_id,
v_delivery_name,
'S',
p_departure_id,
p_departure_id,
'CL',
NULL,
p_date_confirmed,
NULL,
NULL,
v_customer_id,
p_ship_to_site_use_id,
NULL,
NULL,
p_waybill_num,
p_weight,
p_weight_unit_code,
NULL,
NULL,
p_picked_by_id,
p_packed_by_id,
p_expected_arrival_date, -- added: for bug 1413000
NULL,
NULL,
p_context, -- attribute_category
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_ship_method_code,
v_freight_terms_code,
v_currency_code,
v_fob_code,
p_creation_date,
p_created_by,
SYSDATE,
p_last_updated_by,
p_last_update_login,
-999
);
Rollback; -- to savepoint before_insert;
Insert_mesg;
END Insert_Row;
SELECT picking_header_id
FROM SO_PICKING_HEADERS_ALL
WHERE
delivery_id is NULL
AND status_code = 'CLOSED'
AND picking_header_id BETWEEN l_min and l_max;
SELECT 1
FROM so_picking_headers_all WHERE
delivery_id is NULL and
picking_header_id > 0 and
status_code = 'CLOSED';
SELECT NVL(MIN(picking_header_id),0), NVL(MAX(picking_header_id),0)
INTO l_job_min, l_job_max
FROM SO_PICKING_HEADERS_ALL
WHERE picking_header_id between 1 and 9999
AND delivery_id IS NULL
AND status_code = 'CLOSED';
SELECT NVL(MIN(picking_header_id),0), NVL(MAX(picking_header_id),0)
INTO l_job_min, l_job_max
FROM SO_PICKING_HEADERS_ALL
WHERE picking_header_id between 10000 and 99999999
AND delivery_id IS NULL
AND status_code = 'CLOSED';
SELECT NVL(MIN(picking_header_id),0), NVL(MAX(picking_header_id),0)
INTO l_job_min, l_job_max
FROM SO_PICKING_HEADERS_ALL
WHERE picking_header_id >= 100000000
AND delivery_id IS NULL
AND status_code = 'CLOSED';
Insert_Mesg('Worker: ' || to_char(worker) || ' processing picking_header_id ' ||
to_char(l_worker_job_min) || '..' || to_char(l_worker_job_max));
Insert_Mesg('Upgrading picking header id:' || l_lower_limit ||' to '|| l_upper_limit);
u_delivery_id := Insert_Row( u_picking_header_id, u_departure_id);
Insert_Mesg;
UPDATE SO_FREIGHT_CHARGES
SET delivery_id = u_delivery_id
WHERE picking_header_id = u_picking_header_id;
UPDATE SO_PICKING_HEADERS_ALL PH
SET PH.delivery_id = u_delivery_id
WHERE PH.picking_header_id = u_picking_header_id;
UPDATE SO_PICKING_LINE_DETAILS
SET delivery_id = u_delivery_id,
departure_id = u_departure_id,
dpw_assigned_flag = NULL
WHERE picking_line_detail_id IN
( SELECT pld.picking_line_detail_id
FROM
so_picking_line_details pld,
so_picking_lines_all pl
WHERE pl.picking_header_id = u_picking_header_id
AND pld.picking_line_id = pl.picking_line_id );
Insert_Mesg('Long waybill count : ' || to_char(long_waybill_count));
Insert_Mesg('No closed row in table SO_PICKING_HEADERS_ALL, skipping Upgrade_Row');
Insert_Mesg;
Insert_Mesg;