[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT transaction_source
FROM cn_sca_headers_interface_gtt
WHERE sca_batch_id = x_batch_id;
SELECT ROUND(MAX(NVL(csad.rev_split_pct,0)) - SUM(NVL(l.allocation_percentage,0)),4),
MIN(l.sca_lines_output_id) sca_lines_output_id,
w.sca_headers_interface_id
FROM cn_sca_headers_interface_gtt cshi,
cn_sca_winning_rules_gtt w,
cn_sca_lines_output_gtt l,
cn_sca_alloc_details csad,
cn_sca_allocations csa
WHERE cshi.sca_headers_interface_id = w.sca_headers_interface_id
AND w.sca_headers_interface_id = l.sca_headers_interface_id
AND w.sca_credit_rule_id = csa.sca_credit_rule_id
AND csad.sca_allocation_id = csa.sca_allocation_id
AND w.sca_batch_id = x_batch_id
AND csad.role_id = l.role_id
AND l.revenue_type = 'REVENUE'
AND cshi.processed_date BETWEEN csa.start_date AND NVL(end_date,cshi.processed_date)
HAVING ROUND(MAX(NVL(csad.rev_split_pct,0)) - SUM(NVL(l.allocation_percentage,0)),4) <> 0
GROUP BY w.sca_headers_interface_id,l.role_id;
SELECT ROUND(MAX(NVL(csad.nonrev_split_pct,0)) - SUM(NVL(l.allocation_percentage,0)),4),
MIN(l.sca_lines_output_id) sca_lines_output_id,
w.sca_headers_interface_id
FROM cn_sca_headers_interface_gtt cshi,
cn_sca_winning_rules_gtt w,
cn_sca_lines_output_gtt l,
cn_sca_alloc_details csad,
cn_sca_allocations csa
WHERE cshi.sca_headers_interface_id = w.sca_headers_interface_id
AND w.sca_headers_interface_id = l.sca_headers_interface_id
AND w.sca_credit_rule_id = csa.sca_credit_rule_id
AND csad.sca_allocation_id = csa.sca_allocation_id
AND w.sca_batch_id = x_batch_id
AND csad.role_id = l.role_id
AND l.revenue_type = 'NONREVENUE'
AND NVL(csad.nrev_credit_split,'N') = 'Y'
AND cshi.processed_date BETWEEN csa.start_date AND NVL(end_date,cshi.processed_date)
HAVING ROUND(MAX(NVL(csad.nonrev_split_pct,0)) - SUM(NVL(l.allocation_percentage,0)),4) <> 0
GROUP BY w.sca_headers_interface_id,l.role_id;
SELECT org_id INTO l_org_id FROM cn_repositories;
'INSERT INTO cn_sca_lines_output_gtt
(
sca_lines_output_id ,
sca_batch_id,
sca_headers_interface_id ,
processed_date ,
status ,
source_trx_id ,
resource_id ,
role_id ,
revenue_type ,
allocation_percentage
)
SELECT cn_sca_lines_output_gtt_s.nextval,
batch_id,
interface_id,
processed_date,
status,
src_trx_id,
resource_id,
role_id,
revenue_type,
allocation
FROM
(select
x.batch_id,
x.interface_id,
x.processed_date,
x.status,
x.src_trx_id,
x.resource_id,
x.role_id,
y.revenue_type,
decode(y.revenue_type, ''REVENUE'', x.rev_value, x.non_rev_value) allocation
from
(SELECT :x_batch_id1 batch_id,
csli.sca_headers_interface_id interface_id,
cshig.processed_date processed_date,
''ALLOCATED'' status,
csli.source_trx_id src_trx_id,
csli.resource_id resource_id,
csli.role_id role_id,
ROUND(csad.rev_split_pct/nvl(crc.count_of_resources,1),4) rev_value,
DECODE(csad.nrev_credit_split,''Y'',
ROUND(csad.nonrev_split_pct/NVL(crc.count_of_resources,1),4),
csad.nonrev_split_pct) non_rev_value
FROM cn_sca_alloc_details csad,
cn_sca_allocations csa,
(SELECT min( sca_credit_rule_id) sca_credit_rule_id,
sca_headers_interface_id
FROM cn_sca_winning_rules_gtt
WHERE sca_batch_id = :x_batch_id2
GROUP BY SCA_HEADERS_INTERFACE_ID ) cswrg,
cn_sca_headers_interface_gtt cshig,
(SELECT count(distinct RESOURCE_ID) count_of_resources ,
role_id,
sca_headers_interface_id
FROM cn_sca_lines_interface_gtt cslig
WHERE cslig.sca_batch_id =:x_batch_id3
GROUP BY sca_headers_interface_id,
role_id) crc,
cn_sca_lines_interface_gtt csli
WHERE cshig.sca_batch_id = :x_batch_id4
AND csli.sca_batch_id =cshig.sca_batch_id
AND cswrg.sca_headers_interface_id = cshig.sca_headers_interface_id
AND crc.sca_headers_interface_id = cshig.sca_headers_interface_id
AND csli.sca_headers_interface_id = cshig.sca_headers_interface_id
AND csa.sca_credit_rule_id = cswrg.sca_credit_rule_id
AND csad.ROLE_ID = csli.role_id
AND crc.ROLE_ID = csli.role_id
AND csad.sca_allocation_id = csa.sca_allocation_id
AND cshig.processed_date
BETWEEN csa.start_date AND NVL(end_date,cshig.processed_date)
) x,
(select ''REVENUE'' revenue_type from dual
union all
select ''NONREVENUE'' revenue_type from dual) y) result1 WHERE allocation > 0';
UPDATE cn_sca_lines_output_gtt l
SET l.allocation_percentage = l.allocation_percentage +
l_rounding_tbl_rec.rounding_tbl(indx)
WHERE l.sca_headers_interface_id = l_rounding_tbl_rec.interface_id_tbl(indx)
AND l.sca_lines_output_id = l_rounding_tbl_rec.lines_output_id_tbl(indx);
UPDATE cn_sca_lines_output_gtt l
SET l.allocation_percentage = l.allocation_percentage +
l_rounding_tbl_rec.rounding_tbl(indx)
WHERE l.sca_headers_interface_id = l_rounding_tbl_rec.interface_id_tbl(indx)
AND l.sca_lines_output_id = l_rounding_tbl_rec.lines_output_id_tbl(indx);
UPDATE cn_sca_lines_output_gtt set status = 'REV NOT 100'
WHERE sca_batch_id =x_batch_id
AND revenue_type = 'REVENUE'
AND sca_headers_interface_id in
(SELECT sca_headers_interface_id
FROM cn_sca_lines_output_gtt
WHERE sca_batch_id = x_batch_id
AND revenue_type = 'REVENUE'
GROUP BY sca_headers_interface_id
HAVING SUM(allocation_percentage) <> 100);
UPDATE cn_sca_headers_interface_gtt cshig set
PROCESS_STATUS = (SELECT distinct status
FROM cn_sca_lines_output_gtt cslog
WHERE cslog.sca_headers_interface_id = cshig.sca_headers_interface_id
AND sca_batch_id =x_batch_id
AND revenue_type = 'REVENUE'),
CREDIT_RULE_ID = (SELECT min( sca_credit_rule_id)
FROM cn_sca_winning_rules_gtt cswr
WHERE sca_batch_id = x_batch_id
and cswr.sca_headers_interface_id = cshig.sca_headers_interface_id)
WHERE sca_batch_id = x_batch_id ;
UPDATE cn_sca_headers_interface_gtt cshig set
PROCESS_STATUS = 'NOT ALLOCATED'
WHERE sca_batch_id = x_batch_id
AND NOT EXISTS (SELECT 'X'
FROM cn_sca_lines_output_gtt cslog
WHERE cslog.sca_headers_interface_id = cshig.sca_headers_interface_id
AND revenue_type = 'REVENUE');
UPDATE cn_sca_headers_interface_gtt cshig set
PROCESS_STATUS = 'NO RULE'
WHERE sca_batch_id = x_batch_id
AND NOT EXISTS (SELECT 'X'
FROM cn_sca_winning_rules_gtt cswr
WHERE sca_batch_id = x_batch_id
AND cswr.sca_headers_interface_id = cshig.sca_headers_interface_id);