[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_date DATE := SYSDATE;
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
SELECT comp_group_id
FROM cn_srp_comp_groups_v
WHERE salesrep_id = p_salesrep_id
AND org_id = g_org_id
AND p_processed_date >= start_date_active
AND ( end_date_active IS NULL OR p_processed_date <= end_date_active )
AND ROWNUM = 1;
SELECT comp_group_id
FROM cn_srp_comp_groups_v
WHERE salesrep_id = p_salesrep_id
AND org_id = g_org_id
AND p_processed_date > start_date_active
AND ( end_date_active IS NULL OR p_processed_date <= end_date_active )
AND comp_group_id = p_comp_group_id;
SELECT NVL( intelligent_flag, 'N' )
FROM cn_calc_submission_batches_all
WHERE logical_batch_id = ( SELECT logical_batch_id
FROM cn_process_batches_all
WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1 );
SELECT ch.direct_salesrep_id, ch.processed_period_id, ch.processed_date
, NVL( ch.rollup_date, ch.processed_date ), ch.comp_group_id, ch.revenue_class_id, ch.trx_type
, SUM( ch.transaction_amount ), SUM( ch.quantity )
FROM cn_commission_headers_all ch, cn_process_batches_all pb
WHERE pb.physical_batch_id = p_physical_batch_id
AND ch.direct_salesrep_id = pb.salesrep_id
AND ch.processed_date BETWEEN pb.start_date AND pb.end_date
AND ch.org_id = pb.org_id
AND ( ( g_intel_calc_flag = 'N' AND ch.status = 'CLS' )
OR ( g_intel_calc_flag = 'Y' AND ch.status = 'CLS' AND ch.parent_header_id IS NULL ) )
GROUP BY ch.direct_salesrep_id
, ch.processed_period_id
, ch.processed_date
, NVL( ch.rollup_date, ch.processed_date )
, ch.comp_group_id
, ch.revenue_class_id
, ch.trx_type;
INSERT INTO cn_commission_headers_all
( commission_header_id, direct_salesrep_id, processed_date, processed_period_id
, trx_type, status, rollup_date, comp_group_id, revenue_class_id, transaction_amount
, quantity, pre_processed_code, parent_header_id, creation_date, created_by, org_id )
VALUES ( cn_commission_headers_s.NEXTVAL, rep_ids( i ), processed_dates( i ), period_ids( i )
, trx_types( i ), 'CLS_SUM', rollup_dates( i ), group_ids( i ), rev_class_ids( i ), amounts( i )
, units( i ), 'CRPC', -1, SYSDATE, g_created_by, g_org_id )
RETURNING commission_header_id
BULK COLLECT INTO header_ids;
UPDATE cn_commission_headers_all
SET parent_header_id = header_ids( i )
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE direct_salesrep_id = rep_ids( i )
AND processed_period_id = period_ids( i )
AND processed_date = processed_dates( i )
AND NVL( rollup_date, processed_date ) = rollup_dates( i )
AND NVL( comp_group_id, -999999 ) = NVL( group_ids( i ), -999999 )
AND revenue_class_id = rev_class_ids( i )
AND trx_type = trx_types( i )
AND org_id = g_org_id
AND ( ( g_intel_calc_flag = 'N' AND status = 'CLS' )
OR ( g_intel_calc_flag = 'Y' AND status = 'CLS' AND parent_header_id IS NULL ) );
SELECT NVL( compplans.sum_trx_flag, 'N' )
FROM cn_srp_plan_assigns srpplans, cn_comp_plans compplans
WHERE compplans.comp_plan_id = srpplans.comp_plan_id
AND p_processed_date BETWEEN srpplans.start_date AND NVL( srpplans.end_date, p_processed_date )
AND srpplans.salesrep_id = p_salesrep_id;
INSERT INTO cn_commission_lines_all
( commission_line_id, commission_header_id, direct_salesrep_id, pre_processed_code, revenue_class_id
, credited_salesrep_id, credited_comp_group_id, pending_status, pending_date, created_during, status
, processed_date, processed_period_id, trx_type, created_by, creation_date, rollup_level, org_id )
( SELECT cn_commission_lines_s.NEXTVAL, commission_header_id, direct_salesrep_id, pre_processed_code
, revenue_class_id, p_salesrep_id, p_credited_comp_group_id, l_pending_status
, DECODE( l_pending_status, 'Y', SYSDATE, NULL ), 'ROLL', 'ROLL', processed_date, processed_period_id
, trx_type, g_created_by, g_creation_date, p_rollup_level, org_id
FROM cn_commission_headers_all
WHERE commission_header_id = p_commission_header_id
AND ( NOT EXISTS(
SELECT 1
FROM cn_commission_lines_all
WHERE commission_header_id = p_commission_header_id
AND credited_salesrep_id = p_salesrep_id ) ) );
INSERT INTO cn_commission_lines_all
( commission_line_id, commission_header_id, direct_salesrep_id, pre_processed_code, revenue_class_id
, credited_salesrep_id, credited_comp_group_id, pending_status, pending_date, created_during, status
, processed_date, processed_period_id, trx_type, created_by, creation_date, rollup_level, org_id )
( SELECT cn_commission_lines_s.NEXTVAL, commission_header_id, direct_salesrep_id, pre_processed_code
, revenue_class_id, p_salesrep_id, p_credited_comp_group_id, l_pending_status
, DECODE( l_pending_status, 'Y', SYSDATE, NULL ), 'ROLL', 'ROLL', processed_date, processed_period_id
, trx_type, g_created_by, g_creation_date, p_rollup_level, org_id
FROM cn_commission_headers_all head
WHERE head.parent_header_id = p_commission_header_id
AND ( NOT EXISTS(
SELECT 1
FROM cn_commission_lines_all line
WHERE line.commission_header_id = head.commission_header_id
AND line.credited_salesrep_id = p_salesrep_id ) ) );
UPDATE cn_commission_headers_all
SET status = DECODE( parent_header_id, -1, 'CLS_SUM', 'CLS' )
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE direct_salesrep_id = p_salesrep_id
AND org_id = g_org_id
AND status <> 'OBSOLETE'
AND ( NVL( rollup_date, processed_date ) BETWEEN p_start_date AND p_end_date
OR processed_date BETWEEN p_start_date AND p_end_date );
UPDATE cn_commission_headers_all
SET status = DECODE( parent_header_id, -1, 'CLS_SUM', 'CLS' )
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE direct_salesrep_id = l_group_member( eachsrp ).salesrep_id
AND org_id = g_org_id
AND ( NVL( rollup_date, processed_date ) BETWEEN l_group_member( eachsrp ).start_date
AND l_group_member( eachsrp ).end_date
OR processed_date BETWEEN l_group_member( eachsrp ).start_date AND l_group_member( eachsrp ).end_date )
AND status <> 'OBSOLETE';
SELECT commission_line_id
FROM cn_commission_lines_all line
WHERE line.posting_status = 'POSTED'
AND line.status = 'CALC'
AND line.commission_header_id IN(
SELECT header.commission_header_id
FROM cn_commission_headers_all header
WHERE header.direct_salesrep_id = p_salesrep_id
AND header.processed_date = p_processed_date
AND header.org_id = g_org_id
AND NVL( header.parent_header_id, -1 ) = -1
AND NVL( header.rollup_date, header.processed_date ) = NVL( p_rollup_date, p_processed_date ) );
SELECT DISTINCT processed_date, rollup_date, processed_period_id
FROM cn_commission_headers_all
WHERE direct_salesrep_id = p_salesrep_id
AND org_id = g_org_id
-- AND comp_group_id = p_comp_group_id
AND ( NVL( rollup_date, processed_date ) BETWEEN p_start_date AND p_end_date
OR processed_date BETWEEN p_start_date AND p_end_date )
AND status <> 'OBSOLETE'
GROUP BY processed_date, rollup_date, processed_period_id;
UPDATE cn_commission_headers_all
SET status = NVL( l_status, DECODE( parent_header_id, -1, 'CLS_SUM', 'CLS' ) )
, comp_group_id = NULL
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE direct_salesrep_id = p_salesrep_id
AND org_id = g_org_id
AND status <> 'OBSOLETE'
AND processed_date = eachdate.processed_date
AND NVL( parent_header_id, -1 ) = -1
AND NVL( rollup_date, processed_date ) = NVL( eachdate.rollup_date, eachdate.processed_date );
UPDATE cn_srp_intel_periods_all
SET process_all_flag = 'Y'
WHERE period_id = eachdate.processed_period_id
AND org_id = g_org_id
AND salesrep_id IN(
SELECT DISTINCT line.credited_salesrep_id
FROM cn_commission_lines_all line, cn_commission_headers_all header
WHERE line.commission_header_id = header.commission_header_id
AND header.direct_salesrep_id = p_salesrep_id
AND header.processed_date = eachdate.processed_date
AND header.org_id = g_org_id
AND NVL( header.parent_header_id, -1 ) = -1
AND NVL( header.rollup_date, header.processed_date ) =
NVL( eachdate.rollup_date, eachdate.processed_date ) );
DELETE FROM cn_commission_lines_all line
WHERE line.commission_header_id IN(
SELECT header.commission_header_id
FROM cn_commission_headers_all header
WHERE header.direct_salesrep_id = p_salesrep_id
AND header.org_id = g_org_id
AND header.processed_date = eachdate.processed_date
AND NVL( header.parent_header_id, -1 ) = -1
AND NVL( header.rollup_date, header.processed_date ) =
NVL( eachdate.rollup_date, eachdate.processed_date ) );
SELECT commission_header_id, processed_date, processed_period_id
FROM cn_commission_headers_all
WHERE direct_salesrep_id = p_salesrep_id
AND org_id = g_org_id
-- AND comp_group_id = p_comp_group_id
AND g_system_rollup_flag = 'Y'
AND trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
AND SUBSTRB( pre_processed_code, 2, 1 ) = 'R'
AND status = 'ROLL'
AND NVL( rollup_date, processed_date ) BETWEEN p_start_date AND p_end_date;
SELECT manager_flag
FROM cn_srp_comp_groups_v
WHERE salesrep_id = x_salesrep_id
AND org_id = g_org_id
AND comp_group_id = x_comp_group_id
AND start_date_active <= x_start_date
AND NVL( end_date_active, x_end_date ) >= x_end_date;
SELECT commission_line_id
FROM cn_commission_lines_all
WHERE credited_salesrep_id = p_salesrep_id
AND org_id = g_org_id
AND status = 'CALC'
AND posting_status = 'POSTED'
AND commission_header_id = p_commission_header_id;
SELECT salesrep_id, comp_group_id GROUP_ID, MIN( start_date ) start_date, MAX( end_date ) end_date
FROM cn_notify_log_all
WHERE action_link_id = p_action_link_id AND org_id = g_org_id AND notify_log_id > p_action_link_id
GROUP BY salesrep_id, comp_group_id;
SELECT 1
FROM cn_srp_comp_groups_v a1
WHERE p_rollup_date BETWEEN start_date_active AND NVL( end_date_active, p_rollup_date )
AND salesrep_id = p_parent_salesrep_id
AND org_id = g_org_id
AND EXISTS(
SELECT 1
FROM cn_groups_denorm_v
WHERE parent_group_id = a1.comp_group_id
AND GROUP_ID IN(
SELECT comp_group_id
FROM cn_srp_comp_groups_v
WHERE p_rollup_date BETWEEN start_date_active AND NVL( end_date_active, p_rollup_date )
AND salesrep_id = p_child_salesrep_id
AND org_id = g_org_id )
AND p_rollup_date BETWEEN start_date_active AND NVL( end_date_active, p_rollup_date ) );
SELECT denorm_level
FROM cn_groups_denorm_v
WHERE parent_group_id = p_parent_group_id
AND GROUP_ID = p_child_group_id
AND p_rollup_date BETWEEN start_date_active AND NVL( end_date_active, p_rollup_date );
SELECT commission_header_id, processed_date, processed_period_id, NVL( rollup_date, processed_date ) rollup_date
FROM cn_commission_headers_all
WHERE direct_salesrep_id = l_salesrep_id
AND org_id = g_org_id
AND comp_group_id = l_comp_group_id
AND g_system_rollup_flag = 'Y'
AND trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
AND SUBSTRB( pre_processed_code, 2, 1 ) = 'R'
AND status = 'ROLL'
AND NVL( rollup_date, processed_date ) BETWEEN l_start_date AND l_end_date;
DELETE cn_commission_lines_all
WHERE credited_salesrep_id = l_ancestors_tbl( l_ancestor ).salesrep_id
AND commission_header_id IN (
SELECT commission_header_id FROM cn_commission_headers
WHERE eachtrx.commission_header_id IN (commission_header_id, parent_header_id)
);
UPDATE cn_srp_intel_periods_all
SET process_all_flag = 'Y'
WHERE salesrep_id = l_ancestors_tbl( l_ancestor ).salesrep_id
AND org_id = g_org_id
AND period_id = eachtrx.processed_period_id;
SELECT ch.commission_header_id commission_header_id, ch.direct_salesrep_id direct_salesrep_id, ch.comp_group_id
, NVL( ch.rollup_date, ch.processed_date ) rollup_date, ch.processed_date processed_date
, ch.processed_period_id
FROM cn_commission_headers_all ch
WHERE ch.direct_salesrep_id = p_salesrep_id
AND ch.org_id = g_org_id
AND ch.processed_date BETWEEN p_start_date AND p_end_date
AND ch.trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
AND ( ( g_roll_sum_trx_flag = 'N' AND ch.status = 'CLS' )
OR ( g_roll_sum_trx_flag = 'Y' AND ch.status = 'CLS_SUM' ) )
AND ( ( SUBSTRB( ch.pre_processed_code, 2, 1 ) = 'N' ) OR( g_system_rollup_flag = 'N' ) );
SELECT ch.commission_header_id commission_header_id, ch.direct_salesrep_id direct_salesrep_id
, ch.comp_group_id comp_group_id, NVL( ch.rollup_date, ch.processed_date ) rollup_date
, ch.processed_date processed_date, ch.processed_period_id, ch.trx_type, ch.revenue_class_id
, ch.pre_processed_code
FROM cn_commission_headers_all ch
WHERE ch.direct_salesrep_id = p_salesrep_id
AND ch.org_id = g_org_id
AND ch.processed_date BETWEEN p_start_date AND p_end_date
AND ch.trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
AND ( ( g_roll_sum_trx_flag = 'N' AND ch.status = 'CLS' )
OR ( g_roll_sum_trx_flag = 'Y' AND ch.status = 'CLS_SUM' ) )
AND SUBSTRB( ch.pre_processed_code, 2, 1 ) = 'R'
AND g_system_rollup_flag = 'Y'
ORDER BY ch.direct_salesrep_id, ch.comp_group_id, NVL( ch.rollup_date, ch.processed_date ), ch.processed_date;
SELECT DISTINCT credited_salesrep_id, credited_comp_group_id, rollup_level
FROM cn_commission_lines_all
WHERE commission_header_id = p_commission_header_id
UNION
SELECT DISTINCT lines.credited_salesrep_id, lines.credited_comp_group_id, lines.rollup_level
FROM cn_commission_headers_all head, cn_commission_lines_all lines
WHERE head.parent_header_id = p_commission_header_id
AND head.commission_header_id = lines.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'ROLL'
, comp_group_id = l_comp_group_id
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'XROLL'
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'ROLL'
, comp_group_id = l_comp_group_id
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'XROLL'
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
UPDATE cn_commission_headers_all
SET status = l_prev_status
, comp_group_id = DECODE( l_prev_status, 'ROLL', l_prev_comp_group_id, NULL )
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
l_active_group.DELETE;
UPDATE cn_commission_headers_all
SET status = 'XROLL'
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'XROLL'
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'XROLL'
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
DELETE FROM cn_commission_lines_all
WHERE commission_header_id = eachtrx.commission_header_id;
l_active_group.DELETE;
l_srp_group_ancestor.DELETE;
UPDATE cn_commission_headers_all
SET status = 'XROLL'
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
DELETE FROM cn_commission_lines_all
WHERE commission_header_id = eachtrx.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'ROLL'
, comp_group_id = l_comp_group_id
, last_update_date = sysdate
, last_updated_by = g_last_updated_by
, last_update_login = g_last_update_login
WHERE commission_header_id = eachtrx.commission_header_id;
SELECT cl.commission_header_id commission_header_id, cl.commission_line_id commission_line_id
, cl.credited_salesrep_id credited_salesrep_id
FROM cn_commission_lines_all cl, cn_process_batches_all pb
WHERE pb.physical_batch_id = p_physical_batch_id
AND pb.salesrep_id = cl.credited_salesrep_id
AND cl.org_id = g_org_id
AND cl.processed_period_id BETWEEN pb.period_id AND pb.end_period_id
AND cl.processed_date BETWEEN pb.start_date AND pb.end_date
AND cl.trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
AND cl.status IN( 'ROLL', 'POP', 'XPOP', 'CALC', 'XCALC' )
AND cl.pending_status = 'Y';
UPDATE cn_commission_lines_all
SET pending_status = 'N'
WHERE commission_line_id = eachtrx.commission_line_id;
SELECT *
FROM ( SELECT event.salesrep_id, event.comp_group_id, event.start_date
, NVL( event.end_date, g_end_of_time ) end_date, event.action, event.notify_log_id
FROM cn_notify_log_all event
WHERE event.physical_batch_id = p_physical_batch_id
AND event.action IN( 'SOURCE_CLS', 'XROLL', 'ROLL_PULL', 'DELETE_ROLL_PULL' )
AND event.status = 'INCOMPLETE'
UNION
SELECT event.salesrep_id, event.comp_group_id, event.start_date
, NVL( event.end_date, g_end_of_time ) end_date, event.action, event.notify_log_id
FROM cn_notify_log_all event, cn_process_batches_all batch
WHERE batch.physical_batch_id = p_physical_batch_id
AND batch.salesrep_id = event.salesrep_id
AND event.org_id = g_org_id
AND event.period_id BETWEEN batch.period_id AND batch.end_period_id
AND event.action IN( 'PULL', 'PULL_WITHIN', 'PULL_BELOW' )
AND event.status = 'INCOMPLETE' ) cur
ORDER BY cur.notify_log_id;
SELECT salesrep_id, start_date, end_date
FROM cn_process_batches_all
WHERE physical_batch_id = p_physical_batch_id
ORDER BY process_batch_id;
SELECT org_id
INTO g_org_id
FROM cn_process_batches_all
WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1;
SELECT NVL( srp_rollup_flag, 'N' )
INTO g_system_rollup_flag
FROM cn_repositories_all
WHERE org_id = g_org_id;
SELECT cb.logical_batch_id, cb.intelligent_flag
INTO l_log_batch_id, l_is_incremental
FROM cn_calc_submission_batches_all cb, cn_process_batches_all pb
WHERE cb.logical_batch_id = pb.logical_batch_id
AND pb.physical_batch_id = p_physical_batch_id
AND pb.org_id = cb.org_id
AND ROWNUM = 1;
ELSIF event.action IN( 'ROLL_PULL', 'DELETE_ROLL_PULL' ) THEN
roll_pull( p_comp_group_id => event.comp_group_id
, p_start_date => event.start_date
, p_end_date => NVL( event.end_date, g_end_of_time )
, p_action => event.action
, p_action_link_id => event.notify_log_id );