[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 ruleset_id,
Greatest(start_date, l_min_start_date) start_date,
Least(Nvl(end_date,l_max_end_date), Nvl(l_max_end_date, end_date)) end_date
FROM cn_rulesets_all
WHERE ((start_date < l_min_start_date AND (end_date IS NULL OR end_date >= l_min_start_date )) OR
start_date BETWEEN l_min_start_date AND l_max_end_date)
AND module_type = 'REVCLS'
AND org_id = g_org_id
ORDER BY start_date;
SELECT MIN(start_date), MAX(end_date)
INTO l_min_start_date, l_max_end_date
FROM cn_process_batches_all
WHERE physical_batch_id = p_physical_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 ch.commission_header_id,
ch.pre_processed_code,
ch.revenue_class_id
FROM cn_commission_headers_all ch
WHERE ch.direct_salesrep_id IN (SELECT salesrep_id
FROM cn_process_batches_all pb
WHERE pb.physical_batch_id = p_physical_batch_id)
AND ch.processed_date BETWEEN l_ruleset.start_date AND l_ruleset.end_date
AND ch.org_id = g_org_id
AND (( l_calc_type = 'COMMISSION'
AND ch.trx_type NOT IN ('FORECAST', 'GRP', 'BONUS') )
OR (l_calc_type = 'FORECAST' AND ch.trx_type = 'FORECAST' ) )
AND ch.status IN ('COL') ;
SELECT ch.commission_header_id,
ch.pre_processed_code,
ch.revenue_class_id
FROM cn_commission_headers_all ch
WHERE ch.direct_salesrep_id
IN ( SELECT salesrep_id
FROM cn_process_batches_all pb
WHERE pb.physical_batch_id = p_physical_batch_id)
AND ch.processed_date BETWEEN l_ruleset.start_date AND l_ruleset.end_date
AND ch.org_id = g_org_id
AND exists (SELECT 1
FROM cn_notify_log_all notify
WHERE notify.period_id = ch.processed_period_id
AND notify.status = 'INCOMPLETE'
AND revert_state = 'COL'
AND org_id = g_org_id
AND salesrep_id = -1000)
AND (( l_calc_type = 'COMMISSION'
AND ch.trx_type NOT IN ('FORECAST', 'GRP', 'BONUS') )
OR (l_calc_type = 'FORECAST' AND ch.trx_type = 'FORECAST' ) )
AND ch.status IN ('CLS', 'XCLS')
AND substrb(ch.pre_processed_code,1,1) = 'C';
SELECT ch.commission_header_id,
ch.pre_processed_code,
ch.revenue_class_id
FROM cn_commission_headers_all ch
WHERE ch.direct_salesrep_id IN (SELECT salesrep_id
FROM cn_process_batches_all pb
WHERE pb.physical_batch_id = p_physical_batch_id)
AND ch.processed_date BETWEEN l_ruleset.start_date AND l_ruleset.end_date
AND ch.org_id = g_org_id
AND exists (SELECT 1
FROM cn_notify_log_all notify
WHERE notify.period_id = ch.processed_period_id
AND notify.status = 'INCOMPLETE'
AND revert_state = 'COL'
AND org_id = g_org_id
AND salesrep_id = -1000)
AND (( l_calc_type = 'COMMISSION'
AND ch.trx_type NOT IN ('FORECAST', 'GRP', 'BONUS') )
OR (l_calc_type = 'FORECAST' AND ch.trx_type = 'FORECAST' ) )
AND ch.status IN ('ROLL')
AND (ch.parent_header_id IS NULL OR ch.parent_header_id <> -1)
AND substrb(ch.pre_processed_code,1,1) = 'C';
UPDATE cn_commission_headers_all
SET status = 'CLS',
revenue_class_id = l_revenue_class_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 = 'XCLS',
revenue_class_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;
UPDATE cn_commission_headers_all
SET status = 'ROLL',
revenue_class_id = l_revenue_class_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_lines_all
SET revenue_class_id = l_revenue_class_id
WHERE commission_header_id = eachtrx.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'XCLS',
revenue_class_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;
UPDATE cn_commission_headers_all
SET status = 'CLS',
revenue_class_id = l_revenue_class_id,
last_update_date = sysdate,
last_updated_by = G_LAST_UPDATED_BY,
last_update_login = G_LAST_UPDATE_LOGIN
WHERE commission_header_id = l_transaction.commission_header_id;
UPDATE cn_commission_headers_all
SET status = 'XCLS',
revenue_class_id = NULL,
last_update_date = sysdate,
last_updated_by = G_LAST_UPDATED_BY,
last_update_login = G_LAST_UPDATE_LOGIN
WHERE commission_header_id = l_transaction.commission_header_id;
select ruleset_id, ruleset_status
from cn_rulesets_all
where org_id = l_org_id
and module_type = 'REVCLS'
and l_proc_date between start_date and nvl(end_date,l_proc_date);
p_transaction_rec.program_update_date,
p_transaction_rec.type,
p_transaction_rec.sales_channel,
p_transaction_rec.object_version_number,
p_transaction_rec.split_pct,
p_transaction_rec.split_status,
p_transaction_rec.security_group_id,
p_transaction_rec.parent_header_id,
p_transaction_rec.trx_type,
p_transaction_rec.status,
p_transaction_rec.pre_processed_code,
p_transaction_rec.comm_lines_api_id,
p_transaction_rec.source_trx_number,
p_transaction_rec.quota_id,
p_transaction_rec.srp_plan_assign_id,
p_transaction_rec.revenue_class_id,
p_transaction_rec.role_id,
p_transaction_rec.comp_group_id,
p_transaction_rec.commission_amount,
p_transaction_rec.trx_batch_id,
p_transaction_rec.reversal_flag,
p_transaction_rec.reversal_header_id,
p_transaction_rec.reason_code,
p_transaction_rec.comments,
p_transaction_rec.attribute_category,
p_transaction_rec.attribute1,
p_transaction_rec.attribute2,
p_transaction_rec.attribute3,
p_transaction_rec.attribute4,
p_transaction_rec.attribute5,
p_transaction_rec.attribute6,
p_transaction_rec.attribute7,
p_transaction_rec.attribute8,
p_transaction_rec.attribute9,
p_transaction_rec.attribute10,
p_transaction_rec.attribute11,
p_transaction_rec.attribute12,
p_transaction_rec.attribute13,
p_transaction_rec.attribute14,
p_transaction_rec.attribute15,
p_transaction_rec.attribute16,
p_transaction_rec.attribute17,
p_transaction_rec.attribute18,
p_transaction_rec.attribute19,
p_transaction_rec.attribute20,
p_transaction_rec.attribute21,
p_transaction_rec.attribute22,
p_transaction_rec.attribute23,
p_transaction_rec.attribute24,
p_transaction_rec.attribute25,
p_transaction_rec.attribute26,
p_transaction_rec.attribute27,
p_transaction_rec.attribute28,
p_transaction_rec.attribute29,
p_transaction_rec.attribute30,
p_transaction_rec.attribute31,
p_transaction_rec.attribute32,
p_transaction_rec.attribute33,
p_transaction_rec.attribute34,
p_transaction_rec.attribute35,
p_transaction_rec.attribute36,
p_transaction_rec.attribute37,
p_transaction_rec.attribute38,
p_transaction_rec.attribute39,
p_transaction_rec.attribute40,
p_transaction_rec.attribute41,
p_transaction_rec.attribute42,
p_transaction_rec.attribute43,
p_transaction_rec.attribute44,
p_transaction_rec.attribute45,
p_transaction_rec.attribute46,
p_transaction_rec.attribute47,
p_transaction_rec.attribute48,
p_transaction_rec.attribute49,
p_transaction_rec.attribute51,
p_transaction_rec.attribute52,
p_transaction_rec.attribute53,
p_transaction_rec.attribute54,
p_transaction_rec.attribute55,
p_transaction_rec.attribute56,
p_transaction_rec.attribute57,
p_transaction_rec.attribute58,
p_transaction_rec.attribute59,
p_transaction_rec.attribute60,
p_transaction_rec.attribute61,
p_transaction_rec.attribute62,
p_transaction_rec.attribute63,
p_transaction_rec.attribute64,
p_transaction_rec.attribute65,
p_transaction_rec.attribute66,
p_transaction_rec.attribute67,
p_transaction_rec.attribute68,
p_transaction_rec.attribute69,
p_transaction_rec.attribute70,
p_transaction_rec.attribute71,
p_transaction_rec.attribute72,
p_transaction_rec.attribute74,
p_transaction_rec.attribute75,
p_transaction_rec.attribute76,
p_transaction_rec.attribute77,
p_transaction_rec.attribute78,
p_transaction_rec.attribute79,
p_transaction_rec.attribute80,
p_transaction_rec.attribute81,
p_transaction_rec.attribute82,
p_transaction_rec.attribute83,
p_transaction_rec.attribute84,
p_transaction_rec.attribute85,
p_transaction_rec.attribute86,
p_transaction_rec.attribute88,
p_transaction_rec.attribute89,
p_transaction_rec.attribute90,
p_transaction_rec.attribute91,
p_transaction_rec.attribute92,
p_transaction_rec.attribute93,
p_transaction_rec.attribute94,
p_transaction_rec.attribute95,
p_transaction_rec.attribute96,
p_transaction_rec.attribute97,
p_transaction_rec.attribute98,
p_transaction_rec.attribute99,
p_transaction_rec.attribute100,
p_transaction_rec.last_update_date,
p_transaction_rec.last_updated_by,
p_transaction_rec.last_update_login,
p_transaction_rec.creation_date,
p_transaction_rec.created_by,
p_transaction_rec.org_id,
p_transaction_rec.exchange_rate,
p_transaction_rec.commission_header_id,
p_transaction_rec.direct_salesrep_id,
p_transaction_rec.processed_date,
p_transaction_rec.processed_period_id,
p_transaction_rec.rollup_date,
p_transaction_rec.transaction_amount,
p_transaction_rec.quantity,
p_transaction_rec.discount_percentage,
p_transaction_rec.margin_percentage,
p_transaction_rec.orig_currency_code,
p_transaction_rec.transaction_amount_orig;