[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(w.sca_headers_interface_id) sca_headers_interface_id,
MAX(w.sca_credit_rule_id) sca_credit_rule_id,
DECODE(SUM(NVL(l.allocation_percentage,0)),100,'ALLOCATED','REV NOT 100')
process_status
FROM cn_sca_winners w,
cn_sca_lines_output l
WHERE w.sca_headers_interface_id = l.sca_headers_interface_id
AND w.sca_headers_interface_id BETWEEN l_start_id AND l_end_id
AND w.role_id = l.role_id
AND l.revenue_type = 'REVENUE'
AND w.org_id = l.org_id
and w.org_id = p_org_id
GROUP BY w.sca_headers_interface_id,w.sca_credit_rule_id;
SELECT start_id, end_id
INTO l_start_id, l_end_id
FROM cn_sca_process_batches
WHERE sca_process_batch_id = p_physical_batch_id;
SELECT org_id
INTO l_org_id
FROM cn_repositories; */
'INSERT /*+ APPEND */ INTO cn_sca_winners( '||
' sca_credit_rule_id, '||
' sca_headers_interface_id, '||
' process_date, '||
' rank, '||
' calculated_rank, '||
' role_id, '||
' role_count, '||
' rev_split_pct, '||
' adj_rev_split_pct, '||
' nonrev_split_pct, '||
' adj_nonrev_split_pct, '||
' nrev_credit_split, '||
' created_by, '||
' creation_date, '||
' last_updated_by, '||
' last_update_date, '||
' last_update_login, '||
' org_id) '||
'SELECT '||
' m.sca_credit_rule_id, '||
' l.sca_headers_interface_id, '||
' m.process_date, '||
' m.rank, '||
' m.calculated_rank, '||
' l.role_id, '||
' l.role_count, '||
' a.rev_split_pct, '||
' ROUND(NVL(a.rev_split_pct,0)/NVL(l.role_count,1),4) rev_net_split, '||
' a.nonrev_split_pct, '||
' DECODE(NVL(a.nrev_credit_split,''N''),''Y'', '||
' ROUND(NVL(a.nonrev_split_pct,0)/NVL(l.role_count,1),4), '||
' a.nonrev_split_pct) nrev_net_split, '||
' a.nrev_credit_split, :l_user_id, :l_created_date, :l_user_id, '||
' :l_last_update_date, :l_login_id, m.org_id '||
' FROM '||
' (SELECT sca_headers_interface_id, '||
' role_id, org_id, '|| -- added org_id here by raramasa
' count(1) role_count '||
' FROM cn_sca_lines_interface a '||
' WHERE a.org_id = :p_org_id and '||
' a.sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id '||
' GROUP BY sca_headers_interface_id,role_id,org_id) l, '|| -- added org_id here
' (SELECT sca_headers_interface_id,process_date,sca_credit_rule_id, '||
' rank,calculated_rank, '||
' rule_rank,org_id '||
' FROM '||
' (SELECT sca_headers_interface_id, '||
' process_date, '||
' sca_credit_rule_id, '||
' calculated_rank, '||
' rank, org_id, '|| -- added org_id here
' rank() over(partition by sca_headers_interface_id '||
' order by calculated_rank desc, '||
' sca_credit_rule_id desc) as rule_rank '||
' FROM cn_sca_matches '||
' WHERE org_id = :p_org_id and '|| -- added org_id here
' sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id) '||
' WHERE rule_rank = 1 '||
' ) m, '||
' (SELECT a.sca_credit_rule_id,b.role_id, '||
' b.rev_split_pct,b.nonrev_split_pct, '||
' b.nrev_credit_split, '||
' a.start_date,a.end_date '||
' FROM cn_sca_allocations a, '||
' cn_sca_alloc_details b '||
' WHERE a.org_id = :p_org_id and '|| -- added org_id here by raramasa
' a.sca_allocation_id = b.sca_allocation_id) a '||
'WHERE l.sca_headers_interface_id = m.sca_headers_interface_id '||
' AND m.sca_credit_rule_id = a.sca_credit_rule_id '||
' AND l.role_id = a.role_id '||
' AND m.process_date BETWEEN a.start_date AND NVL(a.end_date,m.process_date) ';
DELETE cn_sca_lines_output a
WHERE a.sca_headers_interface_id BETWEEN l_start_id AND l_end_id;
debugmsg('Process Batch Rules: Trx deleted from cn_sca_lines_output :'||SQL%ROWCOUNT);
debugmsg('Process Batch Rules: Inserting Records Into cn_sca_lines_output');
'INSERT /*+ APPEND */ INTO cn_sca_lines_output( '||
' sca_lines_output_id, '||
' sca_headers_interface_id, '||
' source_trx_id, '||
' resource_id, '||
' role_id, '||
' revenue_type, '||
' allocation_percentage, '||
' object_version_number, '||
' created_by, '||
' creation_date, '||
' last_updated_by, '||
' last_update_date, '||
' last_update_login, '||
' org_id) '||
'SELECT cn_sca_lines_output_s.NEXTVAL, '||
' sca_headers_interface_id, '||
' source_trx_id, '||
' resource_id, '||
' role_id, '||
' revenue_type, '||
' DECODE(revenue_type,''REVENUE'', '||
' (alloc_pct - '||
' LAG(alloc_pct, 1, 0) OVER ( '||
' PARTITION BY sca_headers_interface_id, role_id, revenue_type '||
' ORDER BY rn)), '||
' ''NONREVENUE'', '||
' DECODE(nrev_credit_split,''Y'', '||
' (alloc_pct - '||
' LAG(alloc_pct, 1, 0) OVER ( '||
' PARTITION BY sca_headers_interface_id, role_id, revenue_type '||
' ORDER BY rn)),alloc_pct)) allocation_percentage, '||
' 1, :l_user_id, :l_created_date, :l_user_id, :l_last_updated_date, :l_login_id, '||
' org_id '||
' FROM (SELECT a.sca_headers_interface_id, '||
' b.source_trx_id, '||
' b.resource_id, '||
' b.role_id, '||
' c.revenue_type, '||
' a.nrev_credit_split, '||
' DECODE(c.revenue_type,''REVENUE'', '||
' ROUND(a.rev_split_pct * '||
' CUME_DIST() OVER ( '||
' PARTITION BY a.sca_headers_interface_id, b.role_id, '||
' c.revenue_type '||
' ORDER BY b.resource_id), 4), '||
' ''NONREVENUE'', '||
' DECODE(a.nrev_credit_split,''Y'', '||
' ROUND(a.nonrev_split_pct * '||
' CUME_DIST() OVER ( '||
' PARTITION BY a.sca_headers_interface_id, b.role_id, '||
' c.revenue_type '||
' ORDER BY b.resource_id), 4), '||
' ''N'',a.nonrev_split_pct)) alloc_pct, '||
' ROW_NUMBER() OVER ( '||
' PARTITION BY a.sca_headers_interface_id, b.role_id, '||
' c.revenue_type '||
' ORDER BY b.resource_id) rn, '||
' a.ORG_ID '||
' FROM cn_sca_winners a, '||
' cn_sca_lines_interface b, '||
' (SELECT ''REVENUE'' revenue_type FROM dual '||
' UNION ALL '||
' SELECT ''NONREVENUE'' revenue_type FROM dual)c '||
' WHERE a.org_id = :p_org_id and a.org_id = b.org_id AND '||
' a.sca_headers_interface_id = b.sca_headers_interface_id '||
' AND a.sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id '||
' AND a.role_id = b.role_id ) result '||
' WHERE result.alloc_pct > 0 ';
debugmsg('Process Batch Rules: Error while inserting into cn_sca_lines_output'||SQLERRM);
UPDATE cn_sca_headers_interface h
SET credit_rule_id = l_sca_winners_tbl_rec.credit_rule_id_tbl(indx),
process_status = l_sca_winners_tbl_rec.process_status_tbl(indx)
WHERE h.sca_headers_interface_id = l_sca_winners_tbl_rec.interface_id_tbl(indx);
'UPDATE cn_sca_headers_interface h '||
' SET (credit_rule_id,process_status) = ( '||
' SELECT b.sca_credit_rule_id, '||
' ''NOT ALLOCATED'' '||
' FROM (SELECT sca_headers_interface_id,sca_credit_rule_id, '||
' rank, '||
' calculated_rank, '||
' rule_rank '||
' FROM (SELECT sca_headers_interface_id, '||
' sca_credit_rule_id, '||
' calculated_rank, '||
' rank, '||
' rank() over(partition by sca_headers_interface_id '||
' order by calculated_rank desc, '||
' sca_credit_rule_id desc) as rule_rank '||
' FROM cn_sca_matches '||
' WHERE org_id = :p_org_id AND '|| -- added org_id here
' sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id) '||
' WHERE rule_rank = 1 '||
' ) b '||
' WHERE h.sca_headers_interface_id = b.sca_headers_interface_id '||
' AND NOT EXISTS ( '||
' SELECT ''X'' '||
' FROM cn_sca_winners c '||
' WHERE h.sca_headers_interface_id = c.sca_headers_interface_id)) '||
' WHERE h.credit_rule_id IS NULL '||
' AND h.process_status = ''SCA_UNPROCESSED'' '||
' AND h.org_id = :p_org_id AND '|| -- added org_id here
' h.sca_headers_interface_id BETWEEN :l_start_id AND :l_end_id '||
-- Perf: Do I need to add this condition
' AND h.processed_date BETWEEN :p_start_date AND NVL(:p_end_date,h.processed_date) ';
UPDATE cn_sca_headers_interface h
SET process_status = 'NO RULE'
WHERE h.credit_rule_id IS NULL
AND h.process_status IS NULL
AND h.org_id = p_org_id -- added org_id here
AND h.sca_headers_interface_id BETWEEN l_start_id AND l_end_id
AND h.processed_date BETWEEN p_start_date AND NVL(p_end_date,h.processed_date);