[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO exist
FROM sys.dual
WHERE EXISTS
(SELECT 1
FROM cn_not_trx
WHERE source_trx_id = hid
AND org_id = p_org_id);
SELECT collected_flag
INTO col_flag
FROM cn_not_trx_all a
WHERE a.source_trx_id = hid
AND a.source_trx_line_id = lid
AND a.org_id = x_org_id
AND a.not_trx_id = ( SELECT max(b.not_trx_id)
FROM cn_not_trx_all b
WHERE b.source_trx_id = hid
AND b.source_trx_line_id = lid
AND b.org_id = a.org_id );
CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = x_org_id;
cn_process_audits_pkg.insert_row
( l_rowid, l_proc_audit_id, NULL, 'NOT', 'Notification run',
NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
INSERT INTO cn_not_trx_all (
org_id,
not_trx_id,
batch_id,
notified_date,
processed_date,
notification_run_id,
collected_flag,
row_id,
source_trx_id,
source_trx_line_id,
source_doc_type,
adjusted_flag,
event_id)
SELECT
asoh.org_id,
cn_not_trx_s.NEXTVAL,
FLOOR(cn_not_trx_s.CURRVAL/NVL(l_sys_batch_size,200)),
SYSDATE,
asoh.booked_date,
l_proc_audit_id,
'N',
asoh.rowid,
asoh.header_id,
asol.line_id,
g_source_doc_type,
p_adj_flag,
cn_global.ord_event_id
FROM
aso_i_oe_order_headers_v asoh,
aso_i_oe_order_lines_v asol
WHERE
asoh.header_id = p_header_id
AND asoh.booked_flag = 'Y' -- only interested in status of booked
-- NOTE: asoh.header_id is a primary key, so no need to
-- have an org filter for the join to asol
AND asol.header_id = asoh.header_id
AND asol.line_id = p_line_id
AND asol.org_id = x_org_id -- R12 MOAC Changes
AND asoh.org_id = asol.org_id -- R12 MOAC Changes
-- also collect 'RETURN's AND asol.line_category_code = 'ORDER' -- only collect 'Order' lines
AND EXISTS
(SELECT 1
FROM mtl_system_items mtl
WHERE
--+
-- Because this procedure is looking at orders for any
-- org_id, we have to get the inventory organization for
-- the org_id of the order, by passing that org_id into
-- our call to OE_PROFILE.VALUE.
--+
NVL(mtl.organization_id,NVL(oe_profile.value('OE_ORGANIZATION_ID',asoh.org_id),-99)) =
NVL(oe_profile.value('OE_ORGANIZATION_ID',asoh.org_id),-99)
AND mtl.inventory_item_id = asol.inventory_item_id
AND mtl.invoiceable_item_flag = 'Y'); -- only want invoiceable items
PROCEDURE notify_deleted_line (
p_header_id NUMBER,
p_line_id NUMBER,
p_org_id NUMBER) IS
l_proc_audit_id NUMBER;
CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = p_org_id;
cn_message_pkg.debug('notify: adjust: entering notify_deleted_line (lid = '||p_line_id||')');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: entering notify_deleted_line (lid = '||p_line_id||')');
cn_process_audits_pkg.insert_row
( l_rowid, l_proc_audit_id, NULL, 'NOT', 'Notification run',
NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, p_org_id);
INSERT INTO cn_not_trx_all (
org_id,
not_trx_id,
batch_id,
notified_date,
processed_date,
notification_run_id,
collected_flag,
row_id,
source_trx_id,
source_trx_line_id,
source_doc_type,
adjusted_flag,
event_id)
SELECT
asoh.org_id,
cn_not_trx_s.NEXTVAL,
FLOOR(cn_not_trx_s.CURRVAL/NVL(l_sys_batch_size,200)),
SYSDATE,
asoh.booked_date,
l_proc_audit_id,
'N',
asoh.rowid,
asoh.header_id,
p_line_id,
g_source_doc_type,
'Y',
cn_global.ord_event_id
FROM
aso_i_oe_order_headers_v asoh
WHERE
asoh.header_id = p_header_id
AND asoh.booked_flag = 'Y'
AND asoh.org_id = l_org_id; -- only interested in status of booked
cn_message_pkg.debug('exit from notify_deleted_line (lid = '||p_line_id||')');
fnd_file.put_line(fnd_file.Log, 'exit from notify_deleted_line (lid = '||p_line_id||')');
END notify_deleted_line;
SELECT top_model_line_id, service_reference_line_id
FROM aso_i_oe_order_lines_v
WHERE line_id = cp_lid
AND org_id = l_org_id;
cn_process_audits_pkg.insert_row
( l_rowid, l_proc_audit_id, NULL, 'NOT', 'Notification run',
NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, p_org_id);
(SELECT line_id
FROM aso_i_oe_order_lines_v asol
WHERE asol.header_id = p_header_id
AND asol.service_reference_line_id IS NULL
AND asol.org_id = l_org_id
AND (asol.top_model_line_id IS NULL OR
asol.top_model_line_id = asol.line_id)
AND NOT EXISTS
(SELECT 1
FROM aso_i_oe_sales_credits_v assc
WHERE assc.line_id = asol.line_id))
LOOP
notify_affected_lines(p_header_id, rec.line_id, l_org_id);
(SELECT header_id, line_id
FROM aso_i_oe_order_lines_v asol
WHERE asol.service_reference_line_id = p_line_id
AND asol.org_id = p_org_id
AND NOT EXISTS
(SELECT 1
FROM aso_i_oe_sales_credits_v assc
WHERE assc.line_id = asol.line_id))
LOOP
notify_line(rec.header_id, rec.line_id,x_org_id => l_org_id);
(SELECT line_id
FROM aso_i_oe_order_lines_v asol
WHERE asol.top_model_line_id = p_line_id
AND asol.org_id = p_org_id
AND asol.header_id = p_header_id -- makes use of index
AND NOT EXISTS
(SELECT 1
FROM aso_i_oe_sales_credits_v assc
WHERE assc.line_id = asol.line_id)
AND asol.line_id <> p_line_id) -- don't re-collect ourself
LOOP
notify_line(p_header_id, rec.line_id,x_org_id => l_org_id);
SELECT header_id, line_id
FROM aso_i_oe_order_lines_v
WHERE header_id = cp_hid
AND org_id = x_org_id; -- R12 MOAC Change
SELECT header_id, line_id
FROM aso_i_oe_order_lines_v
WHERE service_reference_line_id = cp_lid
AND header_id <> cp_hid
AND org_id = x_org_id;
cn_message_pkg.debug('notify: Got update information from Order Capture Feedback Queue for an adjusted order.');
fnd_file.put_line(fnd_file.Log, 'notify: Got update information from Order Capture Feedback Queue for an adjusted order.');
SAVEPOINT Update_Headers;
cn_message_pkg.debug('notify: adjust: Deleted Lines? - Line_Tbl');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: Deleted Lines? - Line_Tbl');
IF p_line_tbl(i).operation = 'DELETE' THEN
notify_deleted_line
(p_line_tbl(i).header_id,
p_line_tbl(i).line_id,
p_org_id => l_org_id);
cn_message_pkg.debug('notify: adjust: Header Insert/Update?');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: Header Insert/Update?');
ELSIF p_header_rec.operation = 'UPDATE' THEN
IF unequal(p_header_rec.invoice_to_org_id , p_old_header_rec.invoice_to_org_id) OR
unequal(p_header_rec.invoice_to_contact_id , p_old_header_rec.invoice_to_contact_id) OR
unequal(p_header_rec.ship_to_org_id , p_old_header_rec.ship_to_org_id) OR
unequal(p_header_rec.order_number , p_old_header_rec.order_number) OR
unequal(p_header_rec.booked_flag , p_old_header_rec.booked_flag) OR
unequal(p_header_rec.transactional_curr_code , p_old_header_rec.transactional_curr_code) OR
unequal(p_header_rec.conversion_rate , p_old_header_rec.conversion_rate)
THEN
l_order_changed := TRUE;
cn_message_pkg.debug('notify: adjust: . update of interest');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: . update of interest');
cn_message_pkg.debug('notify: adjust: Line Insert/Update? - Line_Tbl');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: Line Insert/Update? - Line_Tbl');
ELSIF p_line_tbl(i).operation = 'UPDATE' THEN
--+
-- Locate the Before Image of the line
--+
l_found := FALSE;
(p_line_tbl(i).operation='UPDATE' AND p_old_line_tbl(l_idx).operation ='CREATE') -- Added for Main Line Placeholder Bug 4665116
THEN
cn_message_pkg.debug('notify: adjust: . update of interest');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: . update of interest');
IF p_header_scredit_tbl(i).operation = 'DELETE' THEN
cn_notify_orders.notify_affected_lines
(p_old_header_scredit_tbl(i).header_id,
p_old_header_scredit_tbl(i).line_id, p_org_id => l_org_id); -- this should be NULL
cn_message_pkg.debug('notify: adjust: Header Sales Credit Insert/Update? - Header_Scredit_Tbl');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: Header Sales Credit Insert/Update? - Header_Scredit_Tbl');
ELSIF p_header_scredit_tbl(i).operation = 'UPDATE' THEN
--+
-- Locate the Before Image of the header_scredit
--+
l_found := FALSE;
cn_message_pkg.debug('notify: adjust: . update of interest');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: . update of interest');
IF p_line_scredit_tbl(i).operation = 'DELETE' THEN
cn_notify_orders.notify_affected_lines
(p_old_line_scredit_tbl(i).header_id,
p_old_line_scredit_tbl(i).line_id,p_org_id => l_org_id);
cn_message_pkg.debug('notify: adjust: Line Sales Credit Insert/Update? - Line_Scredit_Tbl');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: Line Sales Credit Insert/Update? - Line_Scredit_Tbl');
ELSIF p_line_scredit_tbl(i).operation = 'UPDATE' THEN
--+
-- Locate the Before Image of the line_scredit
--+
l_found := FALSE;
cn_message_pkg.debug('notify: adjust: . update of interest');
fnd_file.put_line(fnd_file.Log, 'notify: adjust: . update of interest');
ROLLBACK TO Update_Headers;
ROLLBACK TO Update_Headers;
ROLLBACK TO Update_Headers;
SELECT booked_flag
FROM aso_i_oe_order_lines_v
WHERE header_id = cp_hid
AND org_id = x_org_id; -- R12 MOAC Change
SAVEPOINT Update_Headers_Savepoint;
-- ROLLBACK TO Update_Headers_Savepoint;
CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = x_org_id;
cn_message_pkg.debug('notify: Getting any pending order updates off of the Order Capture Feedback Queue');
fnd_file.put_line(fnd_file.Log, 'notify: Getting any pending order updates off of the Order Capture Feedback Queue');
cn_process_audits_pkg.insert_row
( l_rowid, l_proc_audit_id, NULL, 'NOT', 'Notification run',
NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
( 'notify: Inserting records into CN_NOT_TRX from period '
|| l_start_date ||' to period '|| l_end_date ||'.');
fnd_file.put_line(fnd_file.Log, 'notify: Inserting records into CN_NOT_TRX from period '
|| l_start_date ||' to period '|| l_end_date ||'.');
INSERT INTO cn_not_trx (
not_trx_id,
batch_id,
notified_date,
processed_date,
notification_run_id,
collected_flag,
row_id,
source_trx_id,
source_trx_line_id,
source_doc_type,
adjusted_flag,
event_id,
org_id)
SELECT
cn_not_trx_s.NEXTVAL,
FLOOR(cn_not_trx_s.CURRVAL/l_sys_batch_size),
SYSDATE,
asoh.booked_date,
l_proc_audit_id,
'N',
asoh.rowid,
asoh.header_id,
asol.line_id,
g_source_doc_type,
x_adj_flag,
cn_global.ord_event_id,
l_client_org_id
FROM
aso_i_oe_order_headers_v asoh,
aso_i_oe_order_lines_v asol
WHERE
-- Multi_org filter, see comment in procedure header
-- NOTE: asoh.header_id is a primary key, so no need to
-- have an org filter for the join to asol
--+
NVL(asoh.org_id,l_client_org_id) = l_client_org_id
AND asol.org_id = asoh.org_id
--+
AND asoh.booked_flag = 'Y' -- only interested in status of booked
AND asol.header_id = asoh.header_id
-- also collect 'RETURN's AND asol.line_category_code = 'ORDER' -- only collect 'Order' lines
AND TRUNC(asoh.booked_date)
BETWEEN TRUNC(nvl(l_start_date,asoh.booked_date))
AND TRUNC(nvl(l_end_date,asoh.booked_date))
AND EXISTS
(SELECT 1
FROM mtl_system_items mtl
WHERE NVL(mtl.organization_id,l_so_org_id) = l_so_org_id
AND mtl.inventory_item_id = asol.inventory_item_id
AND mtl.invoiceable_item_flag = 'Y') -- only want invoiceable items
AND NOT EXISTS
(SELECT 1
FROM cn_not_trx
WHERE source_trx_id = asoh.header_id
AND source_trx_line_id = asol.line_id
AND event_id= cn_global.ord_event_id
AND org_id = l_client_org_id) ;
cn_process_audits_pkg.update_row(l_proc_audit_id, NULL, SYSDATE, 0,
'Finished notification run: Notified ' || l_trx_count || ' orders.');
cn_message_pkg.debug('notify: No rows inserted into CN_NOT_TRX. Possible reason: Order transactions may have already been collected.');
fnd_file.put_line(fnd_file.Log, 'notify: No rows inserted into CN_NOT_TRX. Possible reason: Order transactions may have already been collected.');
cn_process_audits_pkg.update_row(l_proc_audit_id, NULL, SYSDATE, SQLCODE,
SQLERRM);