[Home] [Help]
1756: order by spq.srp_plan_assign_id, qa.quota_sequence;
1757:
1758: CURSOR l_check_calc_entry_csr (l_salesrep_id NUMBER, l_period_id NUMBER ) IS
1759: SELECT 1
1760: FROM cn_notify_log_all
1761: WHERE (salesrep_id = l_salesrep_id OR salesrep_id = -1000)
1762: AND period_id = l_period_id
1763: AND revert_state = 'CALC'
1764: AND status = 'INCOMPLETE'
1771: CURSOR l_notify_log_csr (l_salesrep_id NUMBER,
1772: l_period_id NUMBER,
1773: l_quota_id number ) IS
1774: SELECT 1
1775: FROM cn_notify_log_all nlog
1776: WHERE nlog.salesrep_id = l_salesrep_id
1777: AND nlog.period_id = l_period_id
1778: AND nlog.status = 'INCOMPLETE'
1779: AND nlog.quota_id = l_quota_id
2096:
2097: commit;
2098:
2099: -- obsolete the entries in cn_notify_log for single salesrep_id, for all salesrep entries, those are handled in processor
2100: /* UPDATE cn_notify_log_all Log
2101: SET Log.status = 'COMPLETE'
2102: WHERE Log.notify_log_id IN
2103: (SELECT log2.notify_log_id
2104: FROM cn_notify_log_all log2,
2100: /* UPDATE cn_notify_log_all Log
2101: SET Log.status = 'COMPLETE'
2102: WHERE Log.notify_log_id IN
2103: (SELECT log2.notify_log_id
2104: FROM cn_notify_log_all log2,
2105: cn_process_batches_all batch
2106: WHERE batch.physical_batch_id = p_physical_batch_id
2107: AND log2.org_id = batch.org_id
2108: AND log2.salesrep_id = batch.salesrep_id
2110: AND log2.status = 'INCOMPLETE'
2111: AND log2.start_date >= batch.start_date);
2112: */
2113: --fix for the Bug 10011406
2114: MERGE INTO cn_notify_log_all log
2115: USING cn_process_batches_all batch
2116: ON ( batch.physical_batch_id = p_physical_batch_id
2117: AND batch.org_id = log.org_id
2118: AND batch.salesrep_id = log.salesrep_id
2126:
2127: commit;
2128:
2129: -- Update all records in notify log that are related to Hierarchical changes.
2130: UPDATE cn_notify_log_all Log
2131: SET Log.status = 'COMPLETE'
2132: WHERE Log.notify_log_id IN (
2133: SELECT event.notify_log_id
2134: FROM cn_notify_log_all event
2130: UPDATE cn_notify_log_all Log
2131: SET Log.status = 'COMPLETE'
2132: WHERE Log.notify_log_id IN (
2133: SELECT event.notify_log_id
2134: FROM cn_notify_log_all event
2135: WHERE event.physical_batch_id = p_physical_batch_id
2136: AND event.action IN ('SOURCE_CLS', 'XROLL', 'ROLL_PULL', 'DELETE_ROLL_PULL')
2137: AND event.status = 'INCOMPLETE'
2138: UNION
2136: AND event.action IN ('SOURCE_CLS', 'XROLL', 'ROLL_PULL', 'DELETE_ROLL_PULL')
2137: AND event.status = 'INCOMPLETE'
2138: UNION
2139: SELECT event.notify_log_id
2140: FROM cn_notify_log_all event, cn_process_batches_all batch
2141: WHERE batch.physical_batch_id = p_physical_batch_id
2142: AND batch.salesrep_id = event.salesrep_id
2143: AND event.org_id = batch.org_id
2144: AND event.period_id between batch.period_id and batch.end_period_id
2487: Log.revert_state, Log.comp_group_id,
2488: Log.base_salesrep_id, Log.base_comp_group_id,
2489: Log.role_id, Log.action
2490: FROM cn_process_batches_all batch,
2491: cn_notify_log_all Log
2492: WHERE batch.physical_batch_id = p_physical_batch_id
2493: AND log.org_id = batch.org_id
2494: AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
2495: AND Log.salesrep_id = batch.salesrep_id
2504: Log.revert_state, Log.comp_group_id,
2505: Log.base_salesrep_id, Log.base_comp_group_id,
2506: Log.quota_id
2507: FROM cn_process_batches_all batch,
2508: cn_notify_log_all Log
2509: WHERE batch.physical_batch_id = p_physical_batch_id
2510: AND log.org_id = batch.org_id
2511: AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
2512: AND ( Log.salesrep_id = batch.salesrep_id
2716: WHERE org_id = l_org_id
2717: AND ( salesrep_id, period_id ) IN
2718: ( SELECT DISTINCT batch.salesrep_id, Log.period_id
2719: FROM cn_process_batches_all batch,
2720: cn_notify_log_all Log
2721: WHERE batch.physical_batch_id = p_physical_batch_id
2722: AND log.org_id = batch.org_id
2723: AND Log.period_id BETWEEN batch.period_id AND batch.end_period_id
2724: AND ( Log.salesrep_id = batch.salesrep_id