[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hsecs INTO l_start_time from v$timer;
SELECT hsecs INTO l_end_time from v$timer;
delete from oe_actions_interface
where (order_source_id, orig_sys_document_ref) IN
(select order_source_id, orig_sys_document_ref
from oe_headers_iface_all
where batch_id = p_batch_id
and nvl(error_flag,'N') = 'N');
delete from oe_price_adjs_interface
where (order_source_id, orig_sys_document_ref) IN
(select order_source_id, orig_sys_document_ref
from oe_headers_iface_all
where batch_id = p_batch_id
and nvl(error_flag,'N') = 'N');
delete from oe_lines_interface
where (order_source_id, orig_sys_document_ref) IN
(select order_source_id, orig_sys_document_ref
from oe_headers_iface_all
where batch_id = p_batch_id
and nvl(error_flag,'N') = 'N');
delete from oe_headers_iface_all
where batch_id = p_batch_id
and nvl(error_flag,'N') = 'N';
SELECT DISTINCT batch_id,org_id
FROM oe_headers_iface_all
WHERE request_id = l_request_id;
SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
USE_NL (a b) */
a.order_source_id
, a.original_sys_document_ref
, a.change_sequence
, a.original_sys_document_line_ref
, nvl(a.message_text, b.message_text)
FROM oe_processing_msgs a, oe_processing_msgs_tl b
WHERE a.request_id = l_request_id
AND a.transaction_id = b.transaction_id (+)
AND b.language (+) = oe_globals.g_lang
ORDER BY a.order_source_id, a.original_sys_document_ref, a.change_sequence;
SELECT /* MOAC_SQL_CHANGE */ h.order_source_id
, h.orig_sys_document_ref
, count(*) num_lines
, NULL request_id
, NULL batch_id
, h.org_id
FROM oe_headers_interface h, oe_lines_iface_all l, --bug 4685432
oe_sys_parameters_all sys
WHERE h.order_source_id = nvl(p_order_source_id,h.order_source_id)
AND h.orig_sys_document_ref = nvl(p_orig_sys_document_ref,h.orig_sys_document_ref)
AND h.org_id = l.org_id
AND sys.org_id(+) = h.org_id --bug 4685432, 5209313
AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
AND nvl(sys.parameter_value,'N') = 'N'
-- This phase of BULK supports only CREATION of complete orders
AND nvl(h.operation_code,'CREATE') IN ('INSERT','CREATE')
AND h.request_id IS NULL
AND h.order_source_id <> 20
AND nvl(h.error_flag,'N') = 'N'
AND nvl(h.ineligible_for_hvop, 'N')='N'
AND nvl(h.ready_flag,'Y') = 'Y'
AND nvl(h.rejected_flag,'N') = 'N'
AND nvl(h.force_apply_flag,'Y') = 'Y'
AND h.orig_sys_document_ref = l.orig_sys_document_ref(+)
AND h.order_source_id = l.order_source_id(+)
AND h.org_id = l.org_id(+)
AND nvl(h.change_sequence, FND_API.G_MISS_CHAR)
= nvl(l.change_sequence(+), FND_API.G_MISS_CHAR)
AND nvl(l.error_flag(+),'N') = 'N'
AND nvl(l.rejected_flag(+),'N') = 'N'
AND nvl (h.payment_type_code, ' ') <> 'CREDIT_CARD'
AND nvl(h.order_source_id,0) <> 10
AND h.customer_preference_set_code IS NULL
AND h.return_reason_code IS NULL
AND nvl(h.closed_flag ,'N') = 'N'
AND nvl(l.source_type_code,'INTERNAL') = 'INTERNAL'
AND l.arrival_set_name IS NULL
AND l.ship_set_name IS NULL
AND l.commitment_id IS NULL
AND l.return_reason_code IS NULL
AND l.override_atp_date_code IS NULL
AND h.org_id = nvl(p_operating_unit,h.org_id)
-- Do not process orders with manual sales credits, manual
-- pricing attributes or with action requests other than booking
AND NOT EXISTS (SELECT 'Y'
FROM oe_credits_iface_all sc
WHERE sc.order_source_id = h.order_source_id
AND sc.orig_sys_document_ref
= h.orig_sys_document_ref
AND sc.org_id = h.org_id)
AND NOT EXISTS (SELECT 'Y'
FROM oe_price_atts_iface_all pa
WHERE pa.order_source_id = h.order_source_id
AND pa.orig_sys_document_ref
= h.orig_sys_document_ref
AND h.org_id = pa.org_id)
AND NOT EXISTS (SELECT 'Y'
FROM oe_actions_iface_all a
WHERE a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref
= h.orig_sys_document_ref
AND operation_code <> 'BOOK_ORDER'
AND h.org_id = a.org_id)
GROUP BY h.org_id, h.order_source_id, h.orig_sys_document_ref, h.change_sequence
ORDER BY h.org_id, h.order_source_id, h.orig_sys_document_ref ;
SELECT /* MOAC_SQL_CHANGE */ order_source_id,
orig_sys_document_ref,
num_lines,
request_id,
batch_id,
org_id
FROM (
SELECT h.order_source_id
, h.orig_sys_document_ref
, count(*) num_lines
, NULL request_id
, NULL batch_id
, h.org_id org_id
FROM oe_headers_interface h, oe_lines_iface_all l, --bug 4685432
oe_sys_parameters_all sys
WHERE h.order_source_id = nvl(p_order_source_id,h.order_source_id)
AND h.orig_sys_document_ref = nvl(p_orig_sys_document_ref,h.orig_sys_document_ref)
AND sys.org_id(+) = h.org_id --bug 4685432, 5209313
AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
AND nvl(sys.parameter_value,'N') = 'N'
-- This phase of BULK supports only CREATION of complete orders
AND nvl(h.operation_code,'CREATE') IN ('INSERT','CREATE')
AND h.request_id IS NULL
AND h.order_source_id <> 20
AND nvl(h.error_flag,'N') = 'N'
AND nvl(h.ready_flag,'Y') = 'Y'
AND nvl(h.rejected_flag,'N') = 'N'
AND nvl(h.force_apply_flag,'Y') = 'Y'
AND h.orig_sys_document_ref = l.orig_sys_document_ref(+)
AND h.order_source_id = l.order_source_id(+)
AND h.org_id IS NOT NULL
AND h.org_id = l.org_id(+)
AND nvl(h.change_sequence, FND_API.G_MISS_CHAR)
= nvl(l.change_sequence(+), FND_API.G_MISS_CHAR)
AND nvl(l.error_flag(+),'N') = 'N'
AND nvl(l.rejected_flag(+),'N') = 'N'
AND h.org_id = nvl(p_operating_unit,h.org_id)
-- Do not process orders with manual sales credits, manual
-- pricing attributes or with action requests other than booking
AND NOT EXISTS (SELECT 'Y'
FROM oe_credits_iface_all sc
WHERE sc.order_source_id = h.order_source_id
AND sc.orig_sys_document_ref
= h.orig_sys_document_ref
AND h.org_id = sc.org_id)
AND NOT EXISTS (SELECT 'Y'
FROM oe_price_atts_iface_all pa
WHERE pa.order_source_id = h.order_source_id
AND pa.orig_sys_document_ref
= h.orig_sys_document_ref
AND h.org_id = pa.org_id)
AND NOT EXISTS (SELECT 'Y'
FROM oe_actions_iface_all a
WHERE a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref
= h.orig_sys_document_ref
AND h.org_id = a.org_id
AND operation_code <> 'BOOK_ORDER')
GROUP BY h.org_id, h.order_source_id, h.orig_sys_document_ref, h.change_sequence
UNION
SELECT h.order_source_id
, h.orig_sys_document_ref
, count(*) num_lines
, NULL request_id
, NULL batch_id
, l_default_org_id org_id
FROM oe_headers_iface_all h, oe_lines_iface_all l, --bug 4685432
oe_sys_parameters_all sys
WHERE h.order_source_id = nvl(p_order_source_id,h.order_source_id)
AND h.orig_sys_document_ref = nvl(p_orig_sys_document_ref,h.orig_sys_document_ref)
AND nvl(sys.org_id,l_default_org_id) = l_default_org_id --bug 4685432, 5209313
AND sys.org_id(+) = h.org_id
AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
AND nvl(sys.parameter_value,'N') = 'N'
-- This phase of BULK supports only CREATION of complete orders
AND nvl(h.operation_code,'CREATE') IN ('INSERT','CREATE')
AND h.request_id IS NULL
AND h.order_source_id <> 20
AND nvl(h.error_flag,'N') = 'N'
AND nvl(h.ready_flag,'Y') = 'Y'
AND nvl(h.rejected_flag,'N') = 'N'
AND nvl(h.force_apply_flag,'Y') = 'Y'
AND h.orig_sys_document_ref = l.orig_sys_document_ref(+)
AND h.order_source_id = l.order_source_id(+)
AND nvl(h.org_id, l_default_org_id) = nvl(l.org_id(+), l_default_org_id)
AND nvl(h.change_sequence, FND_API.G_MISS_CHAR)
= nvl(l.change_sequence(+), FND_API.G_MISS_CHAR)
AND nvl(l.error_flag(+),'N') = 'N'
AND nvl(l.rejected_flag(+),'N') = 'N'
AND (h.org_id is NULL AND
l_default_org_id = nvl(p_operating_unit,l_default_org_id))
-- Do not process orders with manual sales credits, manual
-- pricing attributes or with action requests other than booking
AND NOT EXISTS (SELECT 'Y'
FROM oe_credits_iface_all sc
WHERE sc.order_source_id = h.order_source_id
AND sc.orig_sys_document_ref
= h.orig_sys_document_ref
AND NVL(h.org_id, l_default_org_id) =
NVL(sc.org_id, l_default_org_id))
AND NOT EXISTS (SELECT 'Y'
FROM oe_price_atts_iface_all pa
WHERE pa.order_source_id = h.order_source_id
AND pa.orig_sys_document_ref
= h.orig_sys_document_ref
AND NVL(h.org_id, l_default_org_id) =
NVL(pa.org_id, l_default_org_id))
AND NOT EXISTS (SELECT 'Y'
FROM oe_actions_iface_all a
WHERE a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref
= h.orig_sys_document_ref
AND NVL(h.org_id, l_default_org_id) =
NVL(a.org_id, l_default_org_id)
AND operation_code <> 'BOOK_ORDER')
GROUP BY h.org_id, h.order_source_id, h.orig_sys_document_ref, h.change_sequence)
ORDER BY org_id, order_source_id, orig_sys_document_ref ;
SELECT count(org_id), org_id
FROM oe_sys_parameters_syn
WHERE parameter_code = 'ENABLE_FULFILLMENT_ACCEPTANCE'
AND nvl(parameter_value,'N') = 'Y'
AND ((p_operating_unit IS NULL) OR (org_id = p_operating_unit))
GROUP BY org_id
ORDER BY org_id ;
SELECT order_source_id, orig_sys_document_ref,org_id,request_id
FROM oe_headers_iface_all
WHERE request_id = p_request_id
ORDER BY order_source_id, orig_sys_document_ref, change_sequence;
SELECT name
INTO l_oper_unit_name
FROM hr_operating_units
WHERE organization_id = ca_enabled_rec.org_id ;
SELECT count(orig_sys_document_ref)
INTO l_row_count
FROM(
SELECT /* MOAC_SQL_CHANGE */
h.orig_sys_document_ref orig_sys_document_ref
FROM oe_headers_interface h, oe_order_sources os, --bug 4685432
oe_sys_parameters_all sys
WHERE request_id IS NULL
AND sys.org_id(+) = h.org_id --bug 4685432, 5209313
AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
AND nvl(sys.parameter_value,'N') = 'N'
AND os.order_source_id = nvl(p_order_source_id,os.order_source_id)
AND os.order_source_id <> 20
AND h.order_source_id = os.order_source_id
AND orig_sys_document_ref = nvl(p_orig_sys_document_ref,orig_sys_document_ref)
AND nvl(operation_code,'CREATE') IN ('INSERT','CREATE')
AND nvl(error_flag,'N') = 'N'
AND nvl(ready_flag,'Y') = 'Y'
AND nvl(rejected_flag,'N') = 'N'
AND nvl(force_apply_flag,'Y') = 'Y'
AND nvl(Ineligible_for_hvop, 'N')='N'
AND nvl (h.payment_type_code, ' ') <> 'CREDIT_CARD'
AND nvl(h.order_source_id,0) <> 10
AND h.customer_preference_set_code IS NULL
AND h.return_reason_code IS NULL
AND nvl(h.closed_flag ,'N') = 'N'
AND h.org_id = nvl(p_operating_unit,h.org_id)
AND NOT EXISTS
( SELECT orig_sys_line_ref
FROM oe_lines_iface_all l
WHERE h.orig_sys_document_ref = l.orig_sys_document_ref
AND h.order_source_id = l.order_source_id
AND (nvl(l.source_type_code,'INTERNAL') = 'EXTERNAL'
OR l.arrival_set_name IS NOT NULL
OR l.ship_set_name IS NOT NULL
OR l.commitment_id IS NOT NULL
OR l.return_reason_code IS NOT NULL
OR l.override_atp_date_code IS NOT NULL OR
(l.item_type_code IN ('MODEL', 'CLASS', 'OPTION') AND
l.top_model_line_ref is not null AND
p_process_configurations = 'N' )))
AND NOT EXISTS (SELECT 'Y'
FROM oe_credits_iface_all sc
WHERE sc.order_source_id = h.order_source_id
AND sc.orig_sys_document_ref
= h.orig_sys_document_ref
AND h.org_id = sc.org_id)
AND NOT EXISTS (SELECT 'Y'
FROM oe_price_atts_iface_all pa
WHERE pa.order_source_id = h.order_source_id
AND pa.orig_sys_document_ref
= h.orig_sys_document_ref
AND h.org_id = pa.org_id)
AND NOT EXISTS (SELECT 'Y'
FROM oe_actions_iface_all a
WHERE a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref
= h.orig_sys_document_ref
AND h.org_id = a.org_id
AND operation_code <> 'BOOK_ORDER')
UNION
SELECT h.orig_sys_document_ref orig_sys_document_ref
FROM oe_headers_iface_all h, oe_order_sources os, --bug 4685432
oe_sys_parameters_all sys
WHERE request_id IS NULL
AND nvl(sys.org_id,l_default_org_id) = l_default_org_id --bug 4685432, 5209313
AND sys.org_id(+) = h.org_id
AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
AND nvl(sys.parameter_value,'N') = 'N'
AND os.order_source_id = nvl(p_order_source_id,os.order_source_id)
AND os.order_source_id <> 20
AND h.order_source_id = os.order_source_id
AND orig_sys_document_ref = nvl(p_orig_sys_document_ref,orig_sys_document_ref)
AND nvl(operation_code,'CREATE') IN ('INSERT','CREATE')
AND nvl(error_flag,'N') = 'N'
AND nvl(ready_flag,'Y') = 'Y'
AND nvl(rejected_flag,'N') = 'N'
AND nvl(force_apply_flag,'Y') = 'Y'
AND (h.org_id is NULL AND
l_default_org_id = nvl(p_operating_unit,l_default_org_id))
AND NOT EXISTS (SELECT 'Y'
FROM oe_credits_iface_all sc
WHERE sc.order_source_id = h.order_source_id
AND sc.orig_sys_document_ref
= h.orig_sys_document_ref
AND NVL(h.org_id, l_default_org_id) =
NVL(sc.org_id, l_default_org_id))
AND NOT EXISTS (SELECT 'Y'
FROM oe_price_atts_iface_all pa
WHERE pa.order_source_id = h.order_source_id
AND pa.orig_sys_document_ref
= h.orig_sys_document_ref
AND NVL(h.org_id, l_default_org_id) =
NVL(pa.org_id, l_default_org_id))
AND NOT EXISTS (SELECT 'Y'
FROM oe_actions_iface_all a
WHERE a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref
= h.orig_sys_document_ref
AND NVL(h.org_id, l_default_org_id) =
NVL(a.org_id, l_default_org_id)
AND operation_code <> 'BOOK_ORDER'));
SELECT /* MOAC_SQL_CHANGE */ count(*)
INTO l_row_count
FROM oe_headers_interface h, oe_order_sources os, --bug 4685432
oe_sys_parameters_all sys
WHERE request_id IS NULL
AND sys.org_id(+) = h.org_id --bug 4685432, 5209313
AND sys.parameter_code(+) = 'ENABLE_FULFILLMENT_ACCEPTANCE'
AND nvl(sys.parameter_value,'N') = 'N'
AND os.order_source_id = nvl(p_order_source_id,os.order_source_id)
AND os.order_source_id <> 20
AND h.order_source_id = os.order_source_id
AND orig_sys_document_ref = nvl(p_orig_sys_document_ref,orig_sys_document_ref)
AND nvl(operation_code,'CREATE') IN ('INSERT','CREATE')
AND nvl(error_flag,'N') = 'N'
AND nvl(ready_flag,'Y') = 'Y'
AND nvl(rejected_flag,'N') = 'N'
AND nvl(force_apply_flag,'Y') = 'Y'
AND h.org_id = nvl(p_operating_unit,h.org_id)
AND NOT EXISTS (SELECT 'Y'
FROM oe_credits_iface_all sc
WHERE sc.order_source_id = h.order_source_id
AND sc.orig_sys_document_ref
= h.orig_sys_document_ref
AND sc.org_id = h.org_id)
AND NOT EXISTS (SELECT 'Y'
FROM oe_price_atts_iface_all pa
WHERE pa.order_source_id = h.order_source_id
AND pa.orig_sys_document_ref
= h.orig_sys_document_ref
AND pa.org_id = h.org_id )
AND NOT EXISTS (SELECT 'Y'
FROM oe_actions_iface_all a
WHERE a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref
= h.orig_sys_document_ref
AND a.org_id = h.org_id
AND operation_code <> 'BOOK_ORDER');
update oe_headers_iface_all
set error_flag='Y' --- did not update ineligible_for_hvop as per the TDD
where order_source_id = l_order_rec.order_source_id(l_index)
and orig_sys_document_ref = l_order_rec.orig_sys_document_ref(l_index)
and nvl(org_id,-99) = nvl(l_order_rec.org_id(l_index),-99);
SELECT oe_batch_id_s.nextval
INTO l_batch_id FROM DUAL;
UPDATE oe_headers_iface_all
SET request_id = l_order_rec.request_id(l_index)
, batch_id = l_order_rec.batch_id(l_index)
, org_id = l_order_rec.org_id(l_index) -- added for MOAC
WHERE order_source_id = l_order_rec.order_source_id(l_index)
AND orig_sys_document_ref
= l_order_rec.orig_sys_document_ref(l_index)
and nvl(org_id,l_default_org_id) = l_order_rec.org_id(l_index);
UPDATE oe_actions_iface_all
SET org_id = l_order_rec.org_id(l_index) -- added for MOAC
WHERE order_source_id = l_order_rec.order_source_id(l_index)
AND orig_sys_document_ref
= l_order_rec.orig_sys_document_ref(l_index)
AND org_id IS NULL
AND l_default_org_id = nvl(p_operating_unit,l_default_org_id);
UPDATE OE_PRICE_ADJS_IFACE_ALL
SET org_id = l_order_rec.org_id(l_index) -- added for MOAC
WHERE order_source_id = l_order_rec.order_source_id(l_index)
AND orig_sys_document_ref
= l_order_rec.orig_sys_document_ref(l_index)
AND org_id IS NULL
AND l_default_org_id = nvl(p_operating_unit,l_default_org_id);
UPDATE oe_headers_iface_all
SET header_id = oe_order_headers_s.nextval
WHERE order_source_id = c.order_source_id
AND orig_sys_document_ref = c.orig_sys_document_ref
AND nvl(org_id,-99) = nvl(c.org_id,-99)
AND request_id = c.request_id; -- Changed for MOAC
UPDATE oe_lines_iface_all
SET line_id = oe_order_lines_s.nextval,
request_id = l_instance_tbl(l_index).request_id,
org_id = c.org_id
WHERE order_source_id = c.order_source_id
AND orig_sys_document_ref = c.orig_sys_document_ref
AND nvl(org_id, l_default_org_id) = nvl(c.org_id,l_default_org_id); -- changed for MOAC
FND_FILE.PUT_LINE(FND_FILE.LOG,'Time to BULK UPDATE:'||to_char((v_end-v_start)/100));
working on these updates - the performance could be poor due to disk
contention on these tables.
BULK Processing APIs should use the global request ID (G_REQUEST_ID)
to identify current child request ID. Do not use request_id on the
interface tables.
UPDATE oe_lines_interface
SET request_id = l_request_id
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM oe_headers_iface_all
WHERE request_id = l_request_id);
UPDATE oe_price_adjs_interface
SET request_id = l_request_id
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM oe_headers_iface_all
WHERE request_id = l_request_id);
UPDATE oe_actions_interface
SET request_id = l_request_id
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM oe_headers_iface_all
WHERE request_id = l_request_id);
SELECT hsecs INTO l_start_total_time from v$timer;
UPDATE oe_headers_iface_all
SET error_flag = 'Y'
WHERE batch_id = l_batch_id;
SELECT count(*)
INTO l_batch_orders
FROM oe_headers_iface_all
WHERE batch_id = l_batch_id;
SELECT hsecs INTO l_end_total_time from v$timer;