The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT soh.header_id,
soh.order_type_id,
soh.customer_id,
soh.open_flag,
decode (soh.s1,
15, 'ENTERED',
5, 'PARTIAL',
1, 'BOOKED',
to_char(soh.s1)),
purchase_order_num
INTO
x_header_id,
x_order_type_id,
x_customer_id,
x_open_flag,
x_entry_status,
x_po_number
FROM
so_headers soh,
so_order_types sot
WHERE
sot.order_type_id = soh.order_type_id
AND sot.name = x_order_type_name
AND soh.order_number = x_order_number;
SELECT soh.order_number,
sot.name,
soh.open_flag,
decode (soh.s1,
15, 'ENTERED',
5, 'PARTIAL',
1, 'BOOKED',
to_char(soh.s1)),
soh.purchase_order_num
INTO
x_order_number,
x_order_type_name,
x_open_flag,
x_entry_status,
x_po_number
FROM
so_headers soh,
so_order_types sot
WHERE
sot.order_type_id = soh.order_type_id
AND soh.header_id = x_header_id;
PROCEDURE delete_interface_records
(
x_order_source_id IN NUMBER,
x_original_system_reference IN VARCHAR2,
x_request_id IN NUMBER Default NULL
) is
begin
delete
from so_lines_interface
where order_source_id = x_order_source_id
and original_system_reference = x_original_system_reference
and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
delete
from so_line_details_interface
where order_source_id = x_order_source_id
and original_system_reference = x_original_system_reference
and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
delete
from so_price_adjustments_interface
where order_source_id = x_order_source_id
and original_system_reference = x_original_system_reference
and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
delete
from so_sales_credits_interface
where order_source_id = x_order_source_id
and original_system_reference = x_original_system_reference
and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
delete
from so_service_details_interface
where order_source_id = x_order_source_id
and original_system_reference = x_original_system_reference
and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
delete
from so_headers_interface
where order_source_id = x_order_source_id
and original_system_reference = x_original_system_reference
and nvl(request_id, -99999) = nvl(x_request_id, nvl(request_id, -99999));
end delete_interface_records;
SELECT
lin.line_id,
det.line_detail_id
FROM
so_line_details det,
so_lines lin
WHERE
Nvl(det.released_flag, 'N') = 'N'
AND lin.line_id = det.line_id (+)
AND Decode(lin.s27, -- Manufacturing Release
NULL, 'LOCK', -- Lock if not reached
18, 'LOCK', -- Lock if eligible
8, 'LOCK', -- Lock if not applicable
'DONT_LOCK') = 'LOCK' -- Don't lock otherwise
AND lin.open_flag || '' = 'Y'
AND lin.demand_stream_id = p_demand_stream_id
FOR UPDATE OF lin.line_id, det.line_detail_id NOWAIT;
SELECT
pln.picking_line_id,
pld.picking_line_detail_id
FROM
so_picking_lines pln,
so_picking_line_details pld
WHERE
pln.picking_header_id = 0
AND nvl(pld.released_flag, 'N') = 'N'
AND pln.picking_line_id = pld.picking_line_id
AND pln.order_line_id = p_order_line_id
FOR UPDATE OF pln.picking_line_id, pld.picking_line_detail_id NOWAIT;
SELECT
Nvl(MAX(line_number), 0) + 1
INTO
x_line_number
FROM
so_lines
WHERE
header_id = x_header_id
AND shipment_schedule_line_id IS NULL
AND parent_line_id IS NULL
AND service_parent_line_id IS NULL;
SELECT original_system_source_code, original_system_reference
INTO l_original_system_source_code, l_original_system_reference
FROM
so_headers
WHERE
header_id = x_header_id
FOR UPDATE OF
original_system_source_code;
SELECT h.order_number,
t.name
INTO
l_order_number,
l_order_type
FROM
so_headers h,
so_order_types t
WHERE
h.order_type_id = t.order_type_id
AND h.header_id = x_header_id;
UPDATE so_headers
SET original_system_source_code = l_original_system_source_code,
original_system_reference = l_original_system_reference,
source_header_id = NULL
WHERE
header_id = x_header_id
AND not exists
(SELECT 'x'
FROM so_headers
WHERE original_system_reference = l_original_system_reference
AND original_system_source_code = l_original_system_source_code);
SELECT 'Y'
INTO l_scheduling_exists
FROM so_line_details
WHERE line_id in
(
SELECT line_id
FROM so_lines
WHERE (line_id = x_line_id
OR parent_line_id = x_line_id)
)
AND schedule_status_code is NOT NULL
AND rownum = 1;