[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct
cnt.not_trx_id,
ara.customer_trx_id,
ara.from_cust_trx_line_id,
ara.from_inventory_item_id,
ara.from_category_id,
ara.line_selection_mode
from cn_not_trx cnt, ar_revenue_adjustments ara
where
nvl(cnt.last_collected_date, cnt.notified_date) <= ara.application_date and
cnt.collected_flag = 'Y' and -- has been collected before
cnt.source_doc_type = 'AR' and -- AR collection
cnt.event_id = cn_global.inv_event_id and -- INV/CM/DM collection
cnt.source_trx_id = ara.customer_trx_id
-- cnt.processed_date between p_start_date and p_end_date -- within interested period
AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
AND ara.org_id = cnt.org_id -- MOAC Changes made by Ashley
AND trunc(ara.application_date) between p_start_date and p_end_date -- within interested period
order by ara.customer_trx_id, line_selection_mode;
CURSOR batch_size IS SELECT system_batch_size FROM cn_repositories WHERE org_id = x_org_id;
cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, NULL,'RAMADJ', 'Identify RAM adjustments', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
-- line_selection_mode = ('A','C','I','S')
-- line_selection_mode = 'A' (All lines)
-- Adjustment was applied to all lines of the transactions.
if (adj.line_selection_mode = 'A') then
update cn_trx_lines
set adjusted_flag = 'Y',
negated_flag = 'N',
collected_flag = 'N',
adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
where trx_line_id in (
select ctl.trx_line_id
from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl
where cnt.not_trx_id = adj.not_trx_id and
ct.not_trx_id = cnt.not_trx_id and
ctl.trx_id = ct.trx_id and
cnt.source_trx_id = adj.customer_trx_id and
ct.source_trx_id = cnt.source_trx_id
AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
)
AND org_id = x_org_id; -- MOAC Changes made by Ashley
else -- line_select_mode = 'C', 'I', or 'S'
if (adj.customer_trx_id = x_last_A_customer_trx_id) then
-- no need to handle S I C cases if A (all lines)
-- for the same customer_trx_id has been done before.
null;
-- line_selection_mode = 'S' (Specific line)
-- Adjustment was applied to a specific line only.
if (adj.line_selection_mode = 'S') then
update cn_trx_lines
set adjusted_flag = 'Y',
negated_flag = 'N',
collected_flag = 'N',
adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
where trx_line_id in (
select ctl.trx_line_id
from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl
where cnt.not_trx_id = adj.not_trx_id and
ct.not_trx_id = cnt.not_trx_id and
ctl.trx_id = ct.trx_id and
cnt.source_trx_id = adj.customer_trx_id and
ct.source_trx_id = cnt.source_trx_id and
ctl.source_trx_line_id = adj.from_cust_trx_line_id
AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
)
AND org_id = x_org_id; -- MOAC Changes made by Ashley
-- line_selection_mode = 'I' (Inventory item)
-- Adjustment was applied to all lines with a specific inventory item.
if (adj.line_selection_mode = 'I') then
update cn_trx_lines
set adjusted_flag = 'Y',
negated_flag = 'N',
collected_flag = 'N',
adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
where trx_line_id in (
select ctl.trx_line_id
from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl
where cnt.not_trx_id = adj.not_trx_id and
ct.not_trx_id = cnt.not_trx_id and
ctl.trx_id = ct.trx_id and
cnt.source_trx_id = adj.customer_trx_id and
ct.source_trx_id = cnt.source_trx_id and
ctl.inventory_id = adj.from_inventory_item_id
AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
)
AND org_id = x_org_id; -- MOAC Changes made by Ashley
-- line_selection_mode = 'C' (item Category)
-- Adjustment was applied to all lines with items that belong to a certain category.
if (adj.line_selection_mode = 'C') THEN
--
-- rewrite the update statement for performance issue
--
--old statement
update cn_trx_lines
set adjusted_flag = 'Y',
negated_flag = 'N',
collected_flag = 'N',
adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
where trx_line_id in (
select ctl.trx_line_id
from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl,
(select rctl.customer_trx_line_id
from ra_customer_trx_lines rctl
where rctl.customer_trx_id = adj.customer_trx_id and
exists
(select 1
from mtl_item_categories mic
where mic.category_id = adj.from_category_id and
mic.inventory_item_id = rctl.inventory_item_id)
) r
where cnt.not_trx_id = adj.not_trx_id and
ct.not_trx_id = cnt.not_trx_id and
ctl.trx_id = ct.trx_id and
cnt.source_trx_id = adj.customer_trx_id and
ct.source_trx_id = adj.customer_trx_id and
ctl.source_trx_line_id = r.customer_trx_line_id
AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
)
AND org_id = x_org_id;
update cn_trx_lines
set adjusted_flag = 'Y',
negated_flag = 'N',
collected_flag = 'N',
adj_batch_id = FLOOR(x_trx_identified_count/l_sys_batch_size)
where trx_line_id in (
select ctl.trx_line_id
from cn_not_trx cnt, cn_trx ct, cn_trx_lines ctl,
mtl_item_categories mic
where cnt.not_trx_id = adj.not_trx_id and
ct.not_trx_id = cnt.not_trx_id and
ctl.trx_id = ct.trx_id and
cnt.source_trx_id = adj.customer_trx_id and
ct.source_trx_id = cnt.source_trx_id and
ctl.inventory_id = mic.inventory_item_id and
nvl(ctl.org_id,-99) = nvl(mic.organization_id,-99) and
mic.category_id = adj.from_category_id
AND cnt.org_id = x_org_id -- MOAC Changes made by Ashley
AND ct.org_id = cnt.org_id -- MOAC Changes made by Ashley
AND ctl.org_id = ct.org_id -- MOAC Changes made by Ashley
)
AND org_id = x_org_id; */ -- MOAC Changes made by Ashley
end if; -- end if adj.line_selection_mode = 'A'
-- Update cn_not_trx.last_collected_date
update cn_not_trx
set last_collected_date = SYSDATE
where not_trx_id = adj.not_trx_id
AND org_id = x_org_id; --MOAC Changes made by Ashley
cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0,
'identify RAM adjustments: completed. ' || x_trx_identified_count || ' transactions identified.');
cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
SQLERRM);
select ctl.trx_line_id
from cn_trx_lines ctl
where ctl.adjusted_flag = 'Y' and
ctl.negated_flag = 'N' and
ctl.collected_flag = 'N' and
ctl.event_id = cn_global.inv_event_id
AND org_id = x_org_id;
select api.comm_lines_api_id
from cn_comm_lines_api api
where api.trx_line_id = p_trx_line_id and
api.source_doc_type = 'AR'
AND org_id = x_org_id;
cn_process_audits_pkg.insert_row(x_rowid, x_proc_audit_id, NULL,'RAMADJ', 'negate process', NULL, NULL, NULL, NULL, NULL, SYSDATE, NULL, x_org_id);
update cn_trx_lines
set negated_flag = 'Y'
where trx_line_id = nt.trx_line_id
AND org_id = x_org_id;
cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, 0,
'negate process: completed. ' || x_api_negated_count || ' OIC transactions negated.');
cn_process_audits_pkg.update_row(X_proc_audit_id, NULL, SYSDATE, SQLCODE,
SQLERRM);