The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE BATCH_POST_DIST_UPDATE (
p_start_header_id IN number,
p_end_header_id IN number,
p_user_id IN number,
p_login_id IN number)
IS
BEGIN
UPDATE cn_sca_lines_output lines
SET allocation_percentage =
(SELECT ROUND(lines.allocation_percentage +
(100 - SUM(g_lines.allocation_percentage)),4)
FROM cn_sca_headers_interface g_headers,
cn_sca_lines_output g_lines
WHERE g_headers.sca_headers_interface_id =
lines.sca_headers_interface_id
AND g_headers.sca_headers_interface_id =
g_lines.sca_headers_interface_id
AND g_lines.revenue_type = G_REVENUE
AND g_headers.process_status = G_REV_NOT_100
GROUP BY g_headers.sca_headers_interface_id),
last_updated_by = p_user_id,
last_update_date = SYSDATE,
last_update_login = p_login_id
WHERE lines.revenue_type = G_REVENUE
AND lines.sca_headers_interface_id
BETWEEN p_start_header_id AND p_end_header_id
AND EXISTS (SELECT 1
FROM cn_sca_headers_interface headers
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_headers_interface_id =
lines.sca_headers_interface_id)
AND lines.sca_lines_output_id = (SELECT MIN(sca_lines_output_id)
FROM cn_sca_lines_output g_lines
WHERE lines.sca_headers_interface_id =
g_lines.sca_headers_interface_id
AND g_lines.revenue_type = G_REVENUE);
END BATCH_POST_DIST_UPDATE;
PROCEDURE ONLINE_POST_DIST_UPDATE (
p_sca_batch_id IN number)
IS
BEGIN
UPDATE cn_sca_lines_output_gtt lines
SET allocation_percentage =
(SELECT ROUND(lines.allocation_percentage +
(100 - SUM(g_lines.allocation_percentage)),4)
FROM cn_sca_headers_interface_gtt g_headers,
cn_sca_lines_output_gtt g_lines
WHERE g_headers.sca_headers_interface_id =
lines.sca_headers_interface_id
AND g_headers.sca_headers_interface_id =
g_lines.sca_headers_interface_id
AND g_lines.revenue_type = G_REVENUE
AND g_headers.process_status = G_REV_NOT_100
GROUP BY g_headers.sca_headers_interface_id)
WHERE lines.revenue_type = G_REVENUE
AND lines.sca_batch_id = p_sca_batch_id
AND EXISTS (SELECT 1
FROM cn_sca_headers_interface_gtt headers
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_headers_interface_id =
lines.sca_headers_interface_id)
AND lines.sca_lines_output_id = (SELECT MIN(sca_lines_output_id)
FROM cn_sca_lines_output_gtt g_lines
WHERE lines.sca_headers_interface_id =
g_lines.sca_headers_interface_id
AND g_lines.revenue_type = G_REVENUE);
END ONLINE_POST_DIST_UPDATE;
UPDATE cn_sca_lines_output_gtt lines
SET allocation_percentage =
(SELECT ROUND(lines.allocation_percentage +
(100 - SUM(g_lines.allocation_percentage))
/ COUNT(*),4)
FROM cn_sca_headers_interface_gtt g_headers,
cn_sca_lines_output_gtt g_lines
WHERE g_headers.sca_headers_interface_id =
lines.sca_headers_interface_id
AND g_headers.sca_headers_interface_id =
g_lines.sca_headers_interface_id
AND g_lines.revenue_type = G_REVENUE
AND g_headers.process_status = G_REV_NOT_100
GROUP BY g_headers.sca_headers_interface_id)
WHERE lines.revenue_type = G_REVENUE
AND lines.sca_batch_id = p_sca_batch_id
AND EXISTS (SELECT 1
FROM cn_sca_headers_interface_gtt headers
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_headers_interface_id =
lines.sca_headers_interface_id);
ONLINE_POST_DIST_UPDATE(p_sca_batch_id => p_sca_batch_id);
UPDATE cn_sca_headers_interface_gtt headers
SET process_status = G_ALLOCATED
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_batch_id = p_sca_batch_id;
UPDATE cn_sca_lines_output lines
SET allocation_percentage =
(SELECT ROUND(lines.allocation_percentage +
(100 - SUM(g_lines.allocation_percentage))
/ COUNT(*),4)
FROM cn_sca_headers_interface g_headers,
cn_sca_lines_output g_lines
WHERE g_headers.sca_headers_interface_id =
lines.sca_headers_interface_id
AND g_headers.sca_headers_interface_id =
g_lines.sca_headers_interface_id
AND g_lines.revenue_type = G_REVENUE
AND g_headers.process_status = G_REV_NOT_100
GROUP BY g_headers.sca_headers_interface_id),
last_updated_by = l_user_id,
last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE lines.revenue_type = G_REVENUE
AND lines.sca_headers_interface_id
BETWEEN p_start_header_id AND p_end_header_id
AND EXISTS (SELECT 1
FROM cn_sca_headers_interface headers
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_headers_interface_id =
lines.sca_headers_interface_id);
BATCH_POST_DIST_UPDATE(p_start_header_id => p_start_header_id,
p_end_header_id => p_end_header_id,
p_user_id => l_user_id,
p_login_id => l_login_id);
UPDATE cn_sca_headers_interface headers
SET process_status = G_ALLOCATED,
last_updated_by = l_user_id,
last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_headers_interface_id
BETWEEN p_start_header_id AND p_end_header_id;
UPDATE cn_sca_lines_output_gtt lines
SET allocation_percentage =
(SELECT ROUND(
lines.allocation_percentage + lines.allocation_percentage *
(100 - SUM(g_lines.allocation_percentage)) /
SUM(g_lines.allocation_percentage),4)
FROM cn_sca_headers_interface_gtt g_headers,
cn_sca_lines_output_gtt g_lines
WHERE g_headers.sca_headers_interface_id =
lines.sca_headers_interface_id
AND g_headers.sca_headers_interface_id =
g_lines.sca_headers_interface_id
AND g_lines.revenue_type = G_REVENUE
AND g_headers.process_status = G_REV_NOT_100
GROUP BY g_headers.sca_headers_interface_id)
WHERE lines.revenue_type = G_REVENUE
AND lines.sca_batch_id = p_sca_batch_id
AND EXISTS (SELECT 1
FROM cn_sca_headers_interface_gtt headers
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_headers_interface_id =
lines.sca_headers_interface_id);
ONLINE_POST_DIST_UPDATE(p_sca_batch_id => p_sca_batch_id);
UPDATE cn_sca_headers_interface_gtt headers
SET process_status = G_ALLOCATED
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_batch_id = p_sca_batch_id;
UPDATE cn_sca_lines_output lines
SET allocation_percentage =
(SELECT ROUND(
lines.allocation_percentage + lines.allocation_percentage *
(100 - SUM(g_lines.allocation_percentage)) /
SUM(g_lines.allocation_percentage),4)
FROM cn_sca_headers_interface g_headers,
cn_sca_lines_output g_lines
WHERE g_headers.sca_headers_interface_id =
lines.sca_headers_interface_id
AND g_headers.sca_headers_interface_id =
g_lines.sca_headers_interface_id
AND g_lines.revenue_type = G_REVENUE
AND g_headers.process_status = G_REV_NOT_100
GROUP BY g_headers.sca_headers_interface_id),
last_updated_by = l_user_id,
last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE lines.revenue_type = G_REVENUE
AND lines.sca_headers_interface_id
BETWEEN p_start_header_id AND p_end_header_id
AND EXISTS (SELECT 1
FROM cn_sca_headers_interface headers
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_headers_interface_id =
lines.sca_headers_interface_id);
BATCH_POST_DIST_UPDATE(p_start_header_id => p_start_header_id,
p_end_header_id => p_end_header_id,
p_user_id => l_user_id,
p_login_id => l_login_id);
UPDATE cn_sca_headers_interface headers
SET process_status = G_ALLOCATED,
last_updated_by = l_user_id,
last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE headers.process_status = G_REV_NOT_100
AND headers.sca_headers_interface_id
BETWEEN p_start_header_id AND p_end_header_id;
SELECT shig.transaction_source
INTO l_trxSrc
FROM cn_sca_headers_interface_gtt shig
WHERE shig.sca_batch_id = p_sca_batch_id
AND ROWNUM = 1;
SELECT fa.application_id
INTO l_appId
FROM fnd_application fa
WHERE fa.application_short_name = l_trxSrc;
SELECT fa.application_id
INTO l_appId
FROM fnd_application fa
WHERE fa.application_short_name = UPPER(TRIM(p_trx_source));
PROCEDURE SELECTOR (
itemType IN varchar2,
itemKey IN varchar2,
actId IN number,
funcMode IN varchar2,
resultOut OUT NOCOPY varchar2)
IS
BEGIN
-- RUN mode: Normal process execution
IF (funcMode = 'RUN') THEN
IF UPPER(itemType) = 'CNSCARPR' THEN
resultOut := 'CN_SCA_REV_DIST_PR';
'SELECTOR',
itemType,
itemKey,
TO_CHAR(actId),
funcMode);
END SELECTOR;
PROCEDURE REV_DIST_SELECT (
itemType IN varchar2,
itemKey IN varchar2,
actId IN number,
funcMode IN varchar2,
resultOut OUT NOCOPY varchar2)
IS
-- Local Variables
l_revFunc varchar2(30);
'REV_DIST_SELECT',
itemType,
itemKey,
TO_CHAR(actId),
funcMode);
END REV_DIST_SELECT;
PROCEDURE TRX_LOAD_SELECT (
itemType IN varchar2,
itemKey IN varchar2,
actId IN number,
funcMode IN varchar2,
resultOut OUT NOCOPY varchar2)
IS
-- Local Variables
l_trxSrc varchar2(30);
'TRX_LOAD_SELECT',
itemType,
itemKey,
TO_CHAR(actId),
funcMode);
END TRX_LOAD_SELECT;