[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.physical_batch_id
FROM cn_process_batches_all a
, (SELECT MAX(physical_batch_id) physical_batch_id
, SUM(sales_lines_total) + 1 rec_total
FROM cn_process_batches_all
WHERE logical_batch_id = g_logical_batch_id) b
WHERE a.logical_batch_id = g_logical_batch_id AND a.status_code = 'IN_USE'
GROUP BY a.physical_batch_id
ORDER BY SUM(DECODE(a.physical_batch_id, b.physical_batch_id, b.rec_total, a.sales_lines_total)) DESC;
SELECT a.physical_batch_id
FROM cn_process_batches_all a
, (SELECT MAX(physical_batch_id) physical_batch_id
, SUM(commission_headers_count) + 1 rec_total
FROM cn_process_batches_all
WHERE logical_batch_id = g_logical_batch_id) b
WHERE a.logical_batch_id = g_logical_batch_id AND a.status_code = 'IN_USE'
GROUP BY a.physical_batch_id
ORDER BY SUM(
DECODE(
a.physical_batch_id
, b.physical_batch_id, b.rec_total
, commission_headers_count
)
) DESC;
SELECT start_date
, end_date
FROM cn_calc_submission_batches_all
WHERE logical_batch_id = g_logical_batch_id;
SELECT dim_hierarchy_id
FROM cn_dim_hierarchies_all
WHERE header_dim_hierarchy_id = (SELECT rev_class_hierarchy_id
FROM cn_repositories_all
WHERE org_id = g_org_id)
AND org_id = g_org_id
AND (
(start_date < l_start_date AND(end_date IS NULL OR l_start_date <= end_date))
OR (start_date BETWEEN l_start_date AND l_end_date)
);
SELECT revenue_class_id
, NAME
FROM cn_revenue_classes_all rc
WHERE org_id = g_org_id
AND NOT EXISTS(
SELECT 1
FROM cn_dim_explosion_all
WHERE dim_hierarchy_id = x_dim_hierarchy
AND value_external_id = rc.revenue_class_id
AND ancestor_external_id = rc.revenue_class_id);
UPDATE cn_hierarchy_nodes_all
SET external_id = CLASS.revenue_class_id
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE external_id IS NULL
AND NAME = CLASS.NAME
AND dim_hierarchy_id = x_dim_hierarchy
AND org_id = g_org_id;
INSERT INTO cn_hierarchy_nodes_all
(
dim_hierarchy_id
, value_id
, external_id
, NAME
, ref_count
, hierarchy_level
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, org_id
)
(SELECT x_dim_hierarchy
, cn_hierarchy_nodes_s.NEXTVAL
, CLASS.revenue_class_id
, CLASS.NAME
, 0
, 1
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, l_conc_request_id
, l_prog_appl_id
, l_conc_prog_id
, SYSDATE
, g_org_id
FROM SYS.DUAL
WHERE NOT EXISTS(
SELECT 1
FROM cn_hierarchy_nodes_all
WHERE dim_hierarchy_id = x_dim_hierarchy
AND external_id = CLASS.revenue_class_id
AND org_id = g_org_id));
UPDATE cn_dim_explosion_all
SET ancestor_external_id = CLASS.revenue_class_id
WHERE ancestor_id IN(
SELECT value_id
FROM cn_hierarchy_nodes_all
WHERE dim_hierarchy_id = x_dim_hierarchy
AND external_id = CLASS.revenue_class_id
AND org_id = g_org_id)
AND dim_hierarchy_id = x_dim_hierarchy
AND ancestor_external_id IS NULL
AND org_id = g_org_id;
UPDATE cn_dim_explosion_all
SET value_external_id = CLASS.revenue_class_id
WHERE value_id IN(
SELECT value_id
FROM cn_hierarchy_nodes_all
WHERE dim_hierarchy_id = x_dim_hierarchy
AND external_id = CLASS.revenue_class_id
AND org_id = g_org_id)
AND dim_hierarchy_id = x_dim_hierarchy
AND value_external_id IS NULL
AND org_id = g_org_id;
SELECT org_id
INTO g_org_id
FROM cn_calc_submission_batches_all
WHERE logical_batch_id = p_logical_batch_id;
INSERT INTO cn_process_batches_all
(
process_batch_id
, logical_batch_id
, srp_period_id
, period_id
, end_period_id
, start_date
, end_date
, salesrep_id
, status_code
, process_batch_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, org_id
)
SELECT cn_process_batches_s1.NEXTVAL
, g_logical_batch_id
, 1 /* use a dummy value since this is a not null column */
, p_start_period_id
, p_end_period_id
, p_start_date
, p_end_date
, l_descendant_tbl(l_counter).salesrep_id
, 'IN_USE'
, 'TO_REVERT_BASE_REP'
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, l_conc_request_id
, l_prog_appl_id
, l_conc_prog_id
, SYSDATE
, g_org_id
FROM DUAL
WHERE NOT EXISTS(
SELECT 1
FROM cn_process_batches_all
WHERE logical_batch_id = g_logical_batch_id
AND salesrep_id = l_descendant_tbl(l_counter).salesrep_id
AND period_id = p_start_period_id
AND end_period_id = p_end_period_id
AND start_date = p_start_date
AND end_date = p_end_date);
INSERT INTO cn_process_batches_all
(
process_batch_id
, logical_batch_id
, srp_period_id
, period_id
, end_period_id
, start_date
, end_date
, salesrep_id
, status_code
, process_batch_type
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, org_id
)
SELECT cn_process_batches_s1.NEXTVAL
, g_logical_batch_id
, 1 /* use a dummy value since this is a not null column */
, p_start_period_id
, p_end_period_id
, p_start_date
, p_end_date
, p_salesrep_id
, 'IN_USE'
, 'TO_REVERT_BASE_REP'
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, l_conc_request_id
, l_prog_appl_id
, l_conc_prog_id
, SYSDATE
, g_org_id
FROM DUAL
WHERE NOT EXISTS(
SELECT 1
FROM cn_process_batches_all
WHERE logical_batch_id = g_logical_batch_id
AND salesrep_id = p_salesrep_id
AND period_id = p_start_period_id
AND end_period_id = p_end_period_id
AND start_date = p_start_date
AND end_date = p_end_date);
SELECT period.period_id
, period.start_date
, period.end_date
FROM cn_period_statuses_all period
WHERE l_date BETWEEN period.start_date AND period.end_date AND org_id = p_org_id;
SELECT cn_process_batches_s2.NEXTVAL
INTO g_logical_batch_id
FROM DUAL;
UPDATE cn_calc_submission_batches_all
SET logical_batch_id = g_logical_batch_id
WHERE calc_sub_batch_id = p_calc_sub_batch_id
RETURNING org_id
INTO g_org_id;
SELECT start_date
, end_date
, salesrep_option
, org_id
FROM cn_calc_submission_batches_all
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
SELECT 'Y'
FROM cn_notify_log_all
WHERE org_id = l_org_id
AND salesrep_id = -1000
AND period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND status = 'INCOMPLETE'
AND revert_state <> 'NCALC';
SELECT comp_plan_id
FROM cn_calc_sub_validations_all
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
SELECT status_code
FROM cn_comp_plans_all
WHERE comp_plan_id = p_comp_plan_id;
DELETE FROM cn_calc_sub_validations_all
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
INSERT INTO cn_calc_sub_validations_all
(
org_id
, calc_sub_batch_id
, comp_plan_id
, affected_reps
, created_by
, creation_date
)
SELECT l_org_id
, p_calc_sub_batch_id
, v.comp_plan_id
, v.num_of_affected_reps
, l_created_by
, l_creation_date
FROM (SELECT PLAN.comp_plan_id
, COUNT(DISTINCT spa.salesrep_id) num_of_affected_reps
FROM cn_srp_plan_assigns_all spa
, cn_srp_role_dtls_all srd
, cn_comp_plans_all PLAN
WHERE PLAN.org_id = l_org_id
AND PLAN.status_code = 'INCOMPLETE'
AND spa.comp_plan_id = PLAN.comp_plan_id
AND GREATEST(spa.start_date, l_start_date_adj) <=
LEAST(NVL(spa.end_date, l_end_date_adj), l_end_date_adj)
AND srd.srp_role_id(+) = spa.srp_role_id
AND (
srd.plan_activate_status = 'PUSHED'
OR srd.plan_activate_status IS NULL
OR srd.org_code = 'EMPTY'
)
GROUP BY PLAN.comp_plan_id) v;
INSERT INTO cn_calc_sub_validations_all
(
org_id
, calc_sub_batch_id
, comp_plan_id
, affected_reps
, created_by
, creation_date
)
SELECT l_org_id
, p_calc_sub_batch_id
, v.comp_plan_id
, v.num_of_affected_reps
, l_created_by
, l_creation_date
FROM (SELECT PLAN.comp_plan_id
, COUNT(DISTINCT spa.salesrep_id) num_of_affected_reps
FROM cn_calc_submission_entries cse
, cn_srp_plan_assigns_all spa
, cn_srp_role_dtls_all srd
, cn_comp_plans_all PLAN
WHERE cse.calc_sub_batch_id = p_calc_sub_batch_id
AND spa.salesrep_id = cse.salesrep_id
AND PLAN.org_id = l_org_id
AND PLAN.status_code = 'INCOMPLETE'
AND PLAN.comp_plan_id = spa.comp_plan_id
AND GREATEST(spa.start_date, l_start_date_adj) <=
LEAST(NVL(spa.end_date, l_end_date_adj), l_end_date_adj)
AND srd.srp_role_id(+) = spa.srp_role_id
AND (
srd.plan_activate_status = 'PUSHED'
OR srd.plan_activate_status IS NULL
OR srd.org_code = 'EMPTY'
)
GROUP BY PLAN.comp_plan_id) v;
INSERT INTO cn_calc_sub_validations_all
(
org_id
, calc_sub_batch_id
, comp_plan_id
, affected_reps
, created_by
, creation_date
)
SELECT l_org_id
, p_calc_sub_batch_id
, v.comp_plan_id
, v.num_of_affected_reps
, l_created_by
, l_creation_date
FROM (SELECT PLAN.comp_plan_id
, COUNT(DISTINCT spa.salesrep_id) num_of_affected_reps
FROM cn_srp_plan_assigns_all spa
, cn_srp_role_dtls_all srd
, cn_comp_plans_all PLAN
WHERE PLAN.org_id = l_org_id
AND PLAN.status_code = 'INCOMPLETE'
AND spa.comp_plan_id = PLAN.comp_plan_id
AND GREATEST(spa.start_date, l_start_date_orig) <=
LEAST(NVL(spa.end_date, l_end_date_orig), l_end_date_orig)
AND srd.srp_role_id(+) = spa.srp_role_id
AND (
srd.plan_activate_status = 'PUSHED'
OR srd.plan_activate_status IS NULL
OR srd.org_code = 'EMPTY'
)
GROUP BY PLAN.comp_plan_id) v;
INSERT INTO cn_calc_sub_validations_all
(
org_id
, calc_sub_batch_id
, comp_plan_id
, affected_reps
, created_by
, creation_date
)
SELECT l_org_id
, p_calc_sub_batch_id
, v.comp_plan_id
, v.num_of_affected_reps
, l_created_by
, l_creation_date
FROM (SELECT PLAN.comp_plan_id
, COUNT(DISTINCT spa.salesrep_id) num_of_affected_reps
FROM cn_srp_plan_assigns_all spa
, cn_srp_role_dtls_all srd
, cn_comp_plans_all PLAN
WHERE PLAN.org_id = l_org_id
AND PLAN.status_code = 'INCOMPLETE'
AND spa.comp_plan_id = PLAN.comp_plan_id
AND GREATEST(spa.start_date, l_start_date_orig) <=
LEAST(NVL(spa.end_date, l_end_date_orig), l_end_date_orig)
AND srd.srp_role_id(+) = spa.srp_role_id
AND (
srd.plan_activate_status = 'PUSHED'
OR srd.plan_activate_status IS NULL
OR srd.org_code = 'EMPTY'
)
AND EXISTS(
SELECT 1
FROM cn_notify_log_all
WHERE salesrep_id = spa.salesrep_id
AND org_id = l_org_id
AND period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND status = 'INCOMPLETE'
AND revert_state <> 'NCALC')
GROUP BY PLAN.comp_plan_id) v;
DELETE FROM cn_calc_sub_validations_all
WHERE calc_sub_batch_id = p_calc_sub_batch_id
AND comp_plan_id = invalid_plan.comp_plan_id;
SELECT start_date
, end_date
, intelligent_flag
, NVL(hierarchy_flag, 'N')
, salesrep_option
, org_id
FROM cn_calc_submission_batches_all
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
SELECT DISTINCT intel.salesrep_id
FROM cn_srp_intel_periods_all intel
WHERE org_id = l_org_id
AND intel.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND (
EXISTS(
SELECT 1
FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
WHERE spa.salesrep_id = intel.salesrep_id
AND spa.org_id = intel.org_id
AND (
(
spa.start_date < l_start_date
AND (
spa.end_date IS NULL
OR l_start_date <= spa.end_date
)
)
OR (spa.start_date BETWEEN l_start_date AND l_end_date)
)
AND spa.comp_plan_id = PLAN.comp_plan_id
AND PLAN.status_code = 'COMPLETE')
OR EXISTS(
SELECT 1
FROM cn_commission_lines_all
WHERE credited_salesrep_id = intel.salesrep_id
AND processed_period_id BETWEEN l_calc_from_period_id
AND l_calc_to_period_id
AND org_id = intel.org_id
AND processed_date BETWEEN l_start_date AND l_end_date)
OR EXISTS(
SELECT 1
FROM cn_commission_headers_all
WHERE direct_salesrep_id = intel.salesrep_id
AND org_id = intel.org_id
AND processed_date BETWEEN l_start_date AND l_end_date)
);
SELECT cse.salesrep_id
, NVL(cse.hierarchy_flag, 'N') hierarchy_flag
FROM cn_calc_submission_entries_all cse
WHERE cse.calc_sub_batch_id = p_calc_sub_batch_id
AND (
(
EXISTS(
SELECT 1
FROM cn_notify_log_all LOG
WHERE LOG.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND LOG.status = 'INCOMPLETE'
AND LOG.org_id = cse.org_id
AND (LOG.salesrep_id = -1000 OR LOG.salesrep_id = cse.salesrep_id))
)
OR (
EXISTS(
SELECT 1
FROM cn_commission_lines_all
WHERE credited_salesrep_id = cse.salesrep_id
AND processed_period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND status <> 'OBSOLETE'
AND org_id = cse.org_id)
)
OR (
EXISTS(
SELECT 1
FROM cn_commission_headers_all
WHERE direct_salesrep_id = cse.salesrep_id
AND processed_period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND status <> 'OBSOLETE'
AND org_id = cse.org_id)
)
OR (
EXISTS -- salesrep has an active complete plan within the date range
(
SELECT 1
FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
WHERE spa.salesrep_id = cse.salesrep_id
AND spa.org_id = cse.org_id
AND (
(
spa.start_date < l_start_date
AND (spa.end_date IS NULL OR l_start_date <= spa.end_date)
)
OR (spa.start_date BETWEEN l_start_date AND l_end_date)
)
AND spa.comp_plan_id = PLAN.comp_plan_id
AND PLAN.status_code = 'COMPLETE')
)
);
SELECT DISTINCT ch.direct_salesrep_id
, ch.processed_period_id
, ch.processed_date
, NVL(ch.rollup_date, ch.processed_date) rollup_date
FROM cn_commission_headers_all ch
WHERE ch.direct_salesrep_id IN(
SELECT salesrep_id
FROM cn_srp_intel_periods_all
WHERE period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND org_id = g_org_id)
AND ch.org_id = g_org_id
AND ch.processed_date BETWEEN l_start_date_adj AND l_end_date_adj
AND ch.status IN('COL', 'CLS');
SELECT DISTINCT cl.credited_salesrep_id
, p.start_date
, p.end_date
, p.period_id
FROM cn_commission_lines_all cl, cn_period_statuses_all p
WHERE cl.processed_period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND cl.status IN('ROLL', 'POP')
AND cl.org_id = g_org_id
AND cl.processed_period_id = p.period_id
AND p.org_id = g_org_id;
SELECT DISTINCT LOG.salesrep_id
, period.start_date
, period.end_date
, period.period_id
FROM cn_notify_log_all LOG, cn_period_statuses_all period
WHERE period.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND period.org_id = g_org_id
AND LOG.period_id = period.period_id
AND LOG.org_id = g_org_id
AND LOG.status = 'INCOMPLETE'
AND LOG.salesrep_id <> -1000
AND LOG.revert_state <> 'NCALC'
UNION
SELECT DISTINCT intel.salesrep_id
, period.start_date
, period.end_date
, period.period_id
FROM cn_period_statuses_all period
, cn_notify_log_all LOG
, cn_srp_intel_periods_all intel
WHERE period.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND period.org_id = g_org_id
AND LOG.period_id = period.period_id
AND LOG.org_id = g_org_id
AND LOG.salesrep_id = -1000
AND LOG.status = 'INCOMPLETE'
AND LOG.revert_state <> 'NCALC'
AND intel.period_id = period.period_id
AND intel.org_id = g_org_id;
SELECT NVL(srp_rollup_flag, 'N')
INTO l_system_rollup_flag
FROM cn_repositories_all
WHERE org_id = g_org_id;
l_active_group.DELETE;
l_srp_group_ancestor.DELETE;
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'COMPLETE');
SELECT status
, concurrent_flag
, logical_batch_id
, calc_type
, start_date
, end_date
, org_id
FROM cn_calc_submission_batches_all
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
UPDATE cn_calc_submission_batches_all
SET process_audit_id = x_process_audit_id
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
DELETE cn_process_batches_all
WHERE logical_batch_id = l_batch_rec.logical_batch_id;
SELECT status
INTO l_status
FROM cn_calc_submission_batches_all
WHERE logical_batch_id = g_logical_batch_id;
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
SELECT cn_process_batches_s3.NEXTVAL
INTO x_physical_batch_id
FROM SYS.DUAL;
UPDATE cn_process_batches_all
SET status_code = 'VOID'
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE logical_batch_id = g_logical_batch_id;
UPDATE cn_process_batches_all
SET status_code = 'VOID'
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE physical_batch_id = x_physical_batch_id;
SELECT NAME
, calc_type
, intelligent_flag
, NVL(hierarchy_flag, 'N')
, salesrep_option
, start_date
, end_date
, org_id
FROM cn_calc_submission_batches_all
WHERE logical_batch_id = g_logical_batch_id;
SELECT MAX(logical_batch_id)
FROM cn_calc_submission_batches_all
WHERE logical_batch_id >(g_logical_batch_id - 1000)
AND logical_batch_id < g_logical_batch_id
AND salesrep_option = 'ALL_REPS'
AND calc_type = 'COMMISSION'
AND NVL(hierarchy_flag, 'N') = 'N'
AND intelligent_flag = l_batch_info.intelligent_flag
AND start_date = l_batch_info.start_date
AND end_date = l_batch_info.end_date
AND org_id = l_batch_info.org_id;
SELECT salesrep_id
, DECODE(sales_lines_total, 0, commission_headers_count, sales_lines_total)
FROM cn_process_batches
WHERE logical_batch_id = g_logical_batch_id AND status_code = 'IN_USE'
ORDER BY salesrep_id DESC;
SELECT COUNT(1)
FROM cn_commission_lines_all line, cn_process_batches_all batch
WHERE batch.logical_batch_id = g_logical_batch_id
AND batch.salesrep_id = p_salesrep_id
AND batch.status_code = 'IN_USE'
AND line.credited_salesrep_id = p_salesrep_id
AND line.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
AND line.processed_date BETWEEN batch.start_date AND batch.end_date
AND line.org_id = batch.org_id;
SELECT DISTINCT action_link_id
FROM cn_process_batches_all batch, cn_notify_log_all LOG
WHERE batch.logical_batch_id = g_logical_batch_id
AND batch.status_code = 'IN_USE'
AND LOG.salesrep_id = batch.salesrep_id
AND LOG.period_id BETWEEN batch.period_id AND batch.end_period_id
AND LOG.status = 'INCOMPLETE'
AND LOG.action_link_id IS NOT NULL
AND LOG.org_id = batch.org_id;
UPDATE cn_process_batches_all a
SET (a.sales_lines_total, a.commission_headers_count) =
(SELECT sales_lines_total
, commission_headers_count
FROM cn_process_batches_all
WHERE logical_batch_id = l_pre_logical_batch_id AND salesrep_id = a.salesrep_id)
WHERE logical_batch_id = g_logical_batch_id;
UPDATE cn_process_batches_all a
SET a.sales_lines_total =
(SELECT COUNT(1)
FROM cn_commission_lines_all
WHERE credited_salesrep_id = a.salesrep_id
AND org_id = a.org_id
AND processed_period_id BETWEEN a.period_id AND a.end_period_id
AND processed_date BETWEEN a.start_date AND a.end_date)
, a.commission_headers_count =
(SELECT COUNT(1)
FROM cn_commission_headers_all
WHERE direct_salesrep_id = a.salesrep_id
AND org_id = a.org_id
AND processed_period_id BETWEEN a.period_id AND a.end_period_id
AND processed_date BETWEEN a.start_date AND a.end_date)
WHERE a.logical_batch_id = g_logical_batch_id AND a.sales_lines_total IS NULL;
UPDATE cn_process_batches_all a
SET a.sales_lines_total =
(SELECT COUNT(1)
FROM cn_commission_lines_all
WHERE credited_salesrep_id = a.salesrep_id
AND org_id = a.org_id
AND processed_period_id BETWEEN a.period_id AND a.end_period_id
AND processed_date BETWEEN a.start_date AND a.end_date)
, a.commission_headers_count =
(SELECT COUNT(1)
FROM cn_commission_headers_all
WHERE direct_salesrep_id = a.salesrep_id
AND org_id = a.org_id
AND processed_period_id BETWEEN a.period_id AND a.end_period_id
AND processed_date BETWEEN a.start_date AND a.end_date)
WHERE a.logical_batch_id = g_logical_batch_id;
UPDATE cn_process_batches_all
SET physical_batch_id = bids_tbl(i)
,
--sales_lines_total = nums_tbl(i),
last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE salesrep_id = reps_tbl(i) AND logical_batch_id = g_logical_batch_id;
UPDATE cn_notify_log_all
SET physical_batch_id = x_physical_batch_id
WHERE notify_log_id = action_link.action_link_id AND status = 'INCOMPLETE';
UPDATE cn_notify_log_all
SET physical_batch_id = x_physical_batch_id
WHERE action_link_id = action_link.action_link_id AND status = 'INCOMPLETE';
PROCEDURE update_error(x_physical_batch_id NUMBER) IS
l_user_id NUMBER(15) := fnd_global.user_id;
UPDATE cn_process_batches_all
SET status_code = 'ERROR'
, last_update_date = SYSDATE
, last_update_login = l_login_id
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_prog_id
, program_update_date = SYSDATE
WHERE physical_batch_id = x_physical_batch_id;
END update_error;
SELECT intelligent_flag, start_date, end_date
INTO l_intelligent_flag, l_start_date, l_end_date
FROM cn_calc_submission_batches
WHERE logical_batch_id = g_logical_batch_id;
SELECT COUNT(*)
INTO l_payee_count
FROM cn_srp_payee_assigns a
WHERE a.start_date <= l_end_date AND(a.end_date IS NULL OR a.end_date >= l_start_date);
UPDATE cn_process_batches_all
SET trx_batch_id = l_primary_batch_stack(x_batch_total)
WHERE physical_batch_id = l_primary_batch_stack(x_batch_total);
IF l_dev_status IN('ERROR', 'TERMINATING', 'TERMINATED', 'DELETED') THEN
l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
update_error(l_temp_phys_batch_id);
SELECT MAX(fcr.parent_request_id)
FROM fnd_concurrent_requests fcr
WHERE fcr.program_application_id = 283
AND fcr.concurrent_program_id =
(SELECT concurrent_program_id
FROM fnd_concurrent_programs
WHERE application_id = 283 AND concurrent_program_name = 'BATCH_RUNNER')
AND fcr.phase_code = 'C'
AND fcr.status_code <> 'C'
AND EXISTS(
SELECT 1
FROM cn_process_batches
WHERE logical_batch_id = g_logical_batch_id
AND physical_batch_id = fcr.argument4);
SELECT fcr.argument3 phase
FROM fnd_concurrent_requests fcr
WHERE fcr.parent_request_id = l_parent_request_id
AND fcr.phase_code = 'C'
AND fcr.status_code = 'C'
AND argument4 = p_physical_batch_id
ORDER BY request_id DESC;
SELECT DISTINCT physical_batch_id
FROM cn_process_batches_all
WHERE logical_batch_id = g_logical_batch_id
ORDER BY physical_batch_id DESC;
SELECT intelligent_flag, start_date, end_date
INTO l_intelligent_flag, l_start_date, l_end_date
FROM cn_calc_submission_batches_all
WHERE logical_batch_id = g_logical_batch_id;
SELECT COUNT(*)
INTO l_payee_count
FROM cn_srp_payee_assigns a
WHERE a.start_date <= l_end_date AND(a.end_date IS NULL OR a.end_date >= l_start_date);
UPDATE cn_process_batches_all
SET trx_batch_id = l_primary_batch_stack(g_batch_total)
WHERE physical_batch_id = l_primary_batch_stack(g_batch_total);
IF l_dev_status IN('ERROR', 'TERMINATING', 'TERMINATED', 'DELETED') THEN
l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
update_error(l_temp_phys_batch_id);
SELECT COUNT(DISTINCT physical_batch_id)
FROM cn_process_batches_all
WHERE logical_batch_id = g_logical_batch_id AND status_code = 'IN_USE';
UPDATE cn_process_batches_all
SET trx_batch_id = physical_rec.physical_batch_id
WHERE physical_batch_id = physical_rec.physical_batch_id;
SELECT org_id
INTO l_org_id
FROM cn_calc_submission_batches
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
UPDATE cn_calc_submission_batches_all
SET process_audit_id = l_process_audit_id
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
SELECT 1
FROM DUAL
WHERE EXISTS(SELECT 1
FROM cn_acc_period_statuses_v
WHERE period_status = 'O' AND org_id = l_org_id AND p_start_date >= start_date);
SELECT 1
FROM DUAL
WHERE EXISTS(SELECT 1
FROM cn_acc_period_statuses_v
WHERE period_status = 'O' AND org_id = l_org_id AND p_end_date <= end_date);
SELECT 1
FROM DUAL
WHERE EXISTS(SELECT 1
FROM cn_calc_submission_batches_all
WHERE NAME = p_batch_name AND org_id = l_org_id);
p_operation => 'INSERT'
, p_calc_sub_batch_id => l_calc_sub_batch_id
, p_name => p_batch_name
, p_start_date => p_start_date
, p_end_date => p_end_date
, p_calc_type => p_calc_type
, p_salesrep_option => p_salesrep_option
, p_hierarchy_flag => 'N'
, --p_hierarchy_flag,
p_concurrent_flag => 'Y'
, -- always not on-line, so concurrently
p_intelligent_flag => p_intelligent_flag
, p_status => 'INCOMPLETE'
, p_interval_type_id => p_interval_type_id
, p_org_id => l_org_id
);
p_operation => 'INSERT'
, p_calc_sub_batch_id => l_calc_sub_batch_id
, p_calc_sub_entry_id => l_calc_sub_entry_id
, p_salesrep_id => p_salesrep_id
, p_hierarchy_flag => p_hierarchy_flag
, p_org_id => l_org_id
);
SELECT org_id
INTO l_org_id
FROM cn_process_batches_all
WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1;
NULL; -- do not update processing_status_code
SELECT period_set_id
, period_type_id
INTO l_period_set_id
, l_period_type_id
FROM cn_repositories_all
WHERE org_id = l_org_id;
SELECT MAX(period_id)
, MAX(end_period_id)
INTO l_calc_from_period_id
, l_calc_to_period_id
FROM cn_process_batches_all
WHERE physical_batch_id = p_physical_batch_id;
UPDATE cn_srp_intel_periods_all
SET processing_status_code =
DECODE(
p_physical_process
, g_revert, g_reverted
, g_classification, g_classified
, g_rollup, g_rolled_up
, g_population, g_populated
, g_calculation, g_calculated
, g_unclassified
)
WHERE (salesrep_id, period_id) IN(
SELECT batch.salesrep_id
, per.period_id
FROM cn_process_batches_all batch, cn_period_statuses_all per
WHERE batch.physical_batch_id = p_physical_batch_id
AND per.period_id BETWEEN batch.period_id AND batch.end_period_id
AND per.org_id = batch.org_id
AND per.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
AND per.period_set_id = l_period_set_id
AND per.period_type_id = l_period_type_id);
update_error(p_physical_batch_id);
update_error(p_physical_batch_id);
SELECT ruleset_id
, start_date
, NVL(end_date, p_end_date) end_date
FROM cn_rulesets_all_b
WHERE org_id = p_org_id
AND (
(start_date <= p_start_date AND NVL(end_date, p_start_date) >= p_start_date)
OR (start_date BETWEEN p_start_date AND p_end_date)
)
ORDER BY start_date;
SELECT ruleset_id
, NAME
, ruleset_status
, start_date
, end_date
FROM cn_rulesets_all_vl
WHERE org_id = p_org_id
AND start_date <= p_end_date
AND p_start_date <= NVL(end_date, p_end_date)
AND module_type = 'REVCLS';
SELECT COUNT(*)
FROM user_objects ob
WHERE ob.object_name = l_pkg_name AND ob.object_type = 'PACKAGE BODY';
SELECT calc_type
, org_id
INTO g_calc_type
, g_org_id
FROM cn_calc_submission_batches_all
WHERE logical_batch_id = g_logical_batch_id;
SELECT physical_batch_id
INTO l_temp
FROM cn_process_batches_all
WHERE logical_batch_id = g_logical_batch_id AND ROWNUM = 1;
UPDATE cn_calc_submission_batches_all
SET --ledger_je_batch_id = l_ledger_je_batch_id,
process_audit_id = l_paid
WHERE logical_batch_id = g_logical_batch_id;
SELECT 1
INTO l_temp
FROM SYS.DUAL
WHERE NOT EXISTS(SELECT 1
FROM cn_process_batches_all
WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'COMPLETE');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'PROCESSING');
UPDATE cn_calc_submission_batches_all
SET process_audit_id = x_process_audit_id
WHERE logical_batch_id = p_logical_batch_id;
SELECT 1
INTO l_temp
FROM SYS.DUAL
WHERE NOT EXISTS(SELECT 1
FROM cn_process_batches_all
WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'COMPLETE');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
SELECT period_name
INTO x_period_name
FROM cn_period_statuses_all
WHERE period_id = x_period_id AND org_id = p_org_id;
SELECT NAME
, employee_number
INTO x_name
, x_num
FROM cn_salesreps
WHERE salesrep_id = x_salesrep_id AND org_id = p_org_id;
SELECT MAX(ps2.period_id)
INTO l_end_period_id
FROM cn_period_statuses_all ps1, cn_period_statuses_all ps2
WHERE ps1.org_id = p_org_id
AND ps1.period_id = p_period_id
AND ps2.period_set_id = ps1.period_set_id
AND ps2.period_type_id = ps1.period_type_id
AND ps2.period_year = ps1.period_year
AND ps2.org_id = ps1.org_id
AND (
(p_interval_type_id = -1001 AND ps2.quarter_num = ps1.quarter_num) -- quarter interval
OR p_interval_type_id = -1002
); -- year interval
SELECT 1
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
WHERE spa.salesrep_id = p_salesrep_id
AND spa.org_id = p_org_id
AND (
(
spa.end_date IS NOT NULL
AND p_end_date BETWEEN spa.start_date AND spa.end_date
)
OR (p_end_date >= spa.start_date AND spa.end_date IS NULL)
)
AND spa.comp_plan_id = PLAN.comp_plan_id
AND PLAN.status_code = 'COMPLETE')
OR EXISTS -- comp_plan is active between period start and end date AND a plan element has the salesreps_enddated_flag set to "Y"
(
SELECT 1
FROM cn_srp_plan_assigns_all spa, cn_quota_assigns_all qa, cn_quotas_all pe
WHERE spa.salesrep_id = p_salesrep_id
AND spa.org_id = p_org_id
AND spa.end_date >= p_start_date
AND spa.end_date < p_end_date
AND qa.comp_plan_id = spa.comp_plan_id
AND qa.quota_id = pe.quota_id
AND pe.incentive_type_code = 'BONUS'
AND pe.salesreps_enddated_flag = 'Y'
AND pe.interval_type_id = p_interval_type_id
-- plan element is effective on spa.end_date
AND (
(
pe.end_date IS NOT NULL
AND spa.end_date BETWEEN pe.start_date AND pe.end_date
)
OR (spa.end_date >= pe.start_date AND pe.end_date IS NULL)
)
-- check if in cn_calc_sub_quotas if that exists
AND (
(0 = (SELECT COUNT(*)
FROM cn_calc_sub_quotas
WHERE calc_sub_batch_id = p_calc_sub_batch_id))
OR (pe.quota_id IN(SELECT csq.quota_id
FROM cn_calc_sub_quotas csq
WHERE csq.calc_sub_batch_id = p_calc_sub_batch_id))
));
SELECT DISTINCT spa.salesrep_id
FROM cn_srp_plan_assigns_all spa, cn_calc_submission_batches_all bat
WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
AND spa.org_id = bat.org_id
AND spa.start_date <= bat.end_date
AND (spa.end_date IS NULL OR spa.end_date >= bat.start_date)
--code added for forwardport bug 6600074
AND EXISTS(SELECT 1
FROM cn_comp_plans
WHERE comp_plan_id = spa.comp_plan_id AND status_code = 'COMPLETE')
AND EXISTS(
SELECT 1
FROM cn_quota_assigns a, cn_quotas b
WHERE a.comp_plan_id = spa.comp_plan_id
AND a.quota_id = b.quota_id
AND b.incentive_type_code = 'BONUS'
AND GREATEST(bat.start_date, b.start_date) <=
LEAST(bat.end_date, NVL(b.end_date, bat.end_date)))
--end of code added for forwardport bug 6600074
UNION
SELECT salesrep_id
FROM cn_srp_intel_periods_all sip
WHERE period_id BETWEEN l_start_period_id AND l_end_period_id
AND org_id = l_org_id
AND processing_status_code <> 'CLEAN'
AND NOT EXISTS(
SELECT 1
FROM cn_srp_plan_assigns_all
WHERE salesrep_id = sip.salesrep_id
AND org_id = sip.org_id
AND start_date <= sip.end_date
AND NVL(end_date, sip.start_date) >= sip.start_date)
AND EXISTS(
SELECT 1
FROM cn_commission_headers_all h
WHERE h.direct_salesrep_id = sip.salesrep_id
AND h.org_id = sip.org_id
AND h.processed_date BETWEEN sip.start_date AND sip.end_date
AND h.trx_type = 'BONUS');
SELECT cse.salesrep_id
, NVL(cse.hierarchy_flag, 'N') hierarchy_flag
FROM cn_calc_submission_entries_all cse
WHERE cse.calc_sub_batch_id = p_calc_sub_batch_id
AND (
EXISTS(
SELECT 1
FROM cn_srp_plan_assigns_all spa
, cn_calc_submission_batches_all bat
,
--code added for forwardport bug 6600074
cn_comp_plans PLAN
, cn_quota_assigns a
, cn_quotas b
--end of code added for forwardport bug 6600074
WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
AND spa.salesrep_id = cse.salesrep_id
AND spa.org_id = bat.org_id
AND spa.start_date <= bat.end_date
AND (spa.end_date IS NULL OR spa.end_date >= bat.start_date)
--code added for forwardport bug 6600074
AND spa.comp_plan_id = PLAN.comp_plan_id
AND PLAN.status_code = 'COMPLETE'
AND a.comp_plan_id = spa.comp_plan_id
AND a.quota_id = b.quota_id
AND b.incentive_type_code = 'BONUS'
AND GREATEST(bat.start_date, b.start_date) <=
LEAST(bat.end_date, NVL(b.end_date, bat.end_date))
--end of code added for forwardport bug 6600074
)
OR EXISTS(
SELECT 1
FROM cn_commission_headers_all h
WHERE h.direct_salesrep_id = cse.salesrep_id
AND h.processed_date BETWEEN l_start_date AND l_end_date
AND h.org_id = cse.org_id
AND h.trx_type = 'BONUS')
);
SELECT period.period_id
, period.start_date
, period.end_date
FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
AND period.org_id = bat.org_id
AND (period.period_set_id, period.period_type_id) = (SELECT period_set_id
, period_type_id
FROM cn_repositories_all
WHERE org_id = bat.org_id)
AND period.end_date BETWEEN bat.start_date AND bat.end_date
AND (
EXISTS -- on period.end_date there is an active comp_plan
(
SELECT 1
FROM cn_srp_plan_assigns_all spa
WHERE spa.salesrep_id = p_salesrep_id
AND spa.org_id = bat.org_id
AND (
(
spa.end_date IS NOT NULL
AND period.end_date BETWEEN spa.start_date AND spa.end_date
)
OR (period.end_date >= spa.start_date AND spa.end_date IS NULL)
))
OR EXISTS -- comp_plan is active between period start and end date AND a plan element has the salesreps_enddated_flag set to "Y"
(
SELECT 1
FROM cn_srp_plan_assigns_all spa, cn_quota_assigns_all qa, cn_quotas_all pe
WHERE spa.salesrep_id = p_salesrep_id
AND spa.org_id = bat.org_id
AND spa.end_date >= period.start_date
AND spa.end_date < period.end_date
AND qa.comp_plan_id = spa.comp_plan_id
AND qa.quota_id = pe.quota_id
AND pe.incentive_type_code = 'BONUS'
AND pe.salesreps_enddated_flag = 'Y'
AND (
(p_interval_type_id = -1000 AND pe.interval_type_id = -1000)
OR (p_interval_type_id = -1001 AND pe.interval_type_id = -1001)
OR (p_interval_type_id = -1002 AND pe.interval_type_id = -1002)
OR (
p_interval_type_id = -1003
AND pe.interval_type_id IN(-1000, -1001, -1002)
)
)
-- plan element is effective on spa.end_date
AND (
(
pe.end_date IS NOT NULL
AND spa.end_date BETWEEN pe.start_date AND pe.end_date
)
OR (spa.end_date >= pe.start_date AND pe.end_date IS NULL)
)
-- check if in cn_calc_sub_quotas if that exists
AND (
(0 = (SELECT COUNT(*)
FROM cn_calc_sub_quotas
WHERE calc_sub_batch_id = p_calc_sub_batch_id))
OR (pe.quota_id IN(SELECT csq.quota_id
FROM cn_calc_sub_quotas csq
WHERE csq.calc_sub_batch_id = p_calc_sub_batch_id))
))
OR EXISTS(
SELECT 1
FROM cn_commission_headers_all
WHERE direct_salesrep_id = p_salesrep_id
AND org_id = bat.org_id
AND processed_date BETWEEN period.start_date AND period.end_date
AND trx_type = 'BONUS')
);
SELECT MIN(period.period_id) min_period_id
, MAX(period.period_id) max_period_id
FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
AND period.org_id = bat.org_id
AND period.end_date BETWEEN bat.start_date AND bat.end_date
AND (period.period_set_id, period.period_type_id) =
(SELECT period_set_id
, period_type_id
FROM cn_repositories_all
WHERE org_id = bat.org_id)
GROUP BY period.quarter_num;
SELECT MIN(period.period_id) min_period_id
, MAX(period.period_id) max_period_id
FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
AND period.org_id = bat.org_id
AND period.end_date BETWEEN bat.start_date AND bat.end_date
AND (period.period_set_id, period.period_type_id) =
(SELECT period_set_id
, period_type_id
FROM cn_repositories_all
WHERE org_id = bat.org_id)
GROUP BY period.period_year;
SELECT period.period_id
, period.start_date
, period.end_date
, period.period_set_id
, period.period_type_id
, period.period_year
, period.quarter_num
FROM cn_period_statuses_all period
WHERE period.period_id = l_period_id AND org_id = g_org_id;
SELECT NVL(hierarchy_flag, 'N')
, salesrep_option
, interval_type_id
, start_date
, end_date
, org_id
FROM cn_calc_submission_batches_all
WHERE calc_sub_batch_id = p_calc_sub_batch_id;
SELECT period_id
INTO l_start_period_id
FROM cn_period_statuses_all
WHERE l_start_date BETWEEN start_date AND end_date
AND org_id = l_org_id
AND (period_set_id, period_type_id) = (SELECT period_set_id
, period_type_id
FROM cn_repositories_all
WHERE org_id = l_org_id);
SELECT period_id
INTO l_end_period_id
FROM cn_period_statuses_all
WHERE l_end_date BETWEEN start_date AND end_date
AND org_id = l_org_id
AND (period_set_id, period_type_id) = (SELECT period_set_id
, period_type_id
FROM cn_repositories_all
WHERE org_id = l_org_id);