[Home] [Help]
105: ) IS
106: -- for marking CALC event
107: CURSOR l_chk_calc_lower_events_csr IS
108: SELECT 1
109: FROM cn_notify_log_all
110: WHERE period_id = p_period_id
111: AND org_id = p_org_id
112: AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
113: AND status = 'INCOMPLETE'
116: -- clku, bug 2769655, do not check for 'COL' anymore, need to mark event
117: -- even if there are -1000 'COL' records in notify log
118: CURSOR l_chk_lower_events_csr(l_revert_state VARCHAR2, l_start_date DATE, l_end_date DATE) IS
119: SELECT 1
120: FROM cn_notify_log_all
121: WHERE period_id = p_period_id
122: AND org_id = p_org_id
123: AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
124: AND status = 'INCOMPLETE'
131:
132: -- if there is already a 'CALC' or 'POP' entry for the same quota_id don't mark.
133: CURSOR l_check_calc_quota_entry_csr IS
134: SELECT 1
135: FROM cn_notify_log_all
136: WHERE salesrep_id = p_salesrep_id
137: AND org_id = p_org_id
138: AND period_id = p_period_id
139: AND revert_state IN('CALC', 'POP')
144: CURSOR l_pop_quota_entry_csr IS
145: SELECT notify_log_id
146: , start_date
147: , end_date
148: FROM cn_notify_log_all
149: WHERE salesrep_id = p_salesrep_id
150: AND org_id = p_org_id
151: AND period_id = p_period_id
152: AND revert_state = p_revert_to_state
156: CURSOR l_roll_entry_csr IS
157: SELECT notify_log_id
158: , start_date
159: , end_date
160: FROM cn_notify_log_all
161: WHERE (salesrep_id = -1000 OR salesrep_id = p_salesrep_id)
162: AND org_id = p_org_id
163: AND period_id = p_period_id
164: AND revert_state = p_revert_to_state
167: CURSOR l_roll_all_entry_csr IS
168: SELECT notify_log_id
169: , start_date
170: , end_date
171: FROM cn_notify_log_all
172: WHERE salesrep_id = -1000
173: AND org_id = p_org_id
174: AND period_id = p_period_id
175: AND revert_state = p_revert_to_state
178: CURSOR l_cls_all_entry_csr IS
179: SELECT notify_log_id
180: , start_date
181: , end_date
182: FROM cn_notify_log_all
183: WHERE salesrep_id = -1000
184: AND org_id = p_org_id
185: AND period_id = p_period_id
186: AND revert_state = p_revert_to_state
189: CURSOR l_col_all_entry_csr IS
190: SELECT notify_log_id
191: , start_date
192: , end_date
193: FROM cn_notify_log_all
194: WHERE salesrep_id = -1000
195: AND org_id = p_org_id
196: AND period_id = p_period_id
197: AND revert_state = p_revert_to_state
343: CLOSE l_col_all_entry_csr;
344: END IF;
345:
346: IF l_insert_flag THEN
347: INSERT INTO cn_notify_log_all
348: (
349: notify_log_id
350: , salesrep_id
351: , period_id
387:
388: IF l_update_flag THEN
389: IF l_start_date > p_start_date THEN
390: IF l_end_date < p_end_date THEN
391: UPDATE cn_notify_log_all
392: SET start_date = p_start_date
393: , end_date = p_end_date
394: WHERE notify_log_id = l_notify_log_id;
395: ELSE
407: IF l_update_flag OR l_insert_flag THEN
408: -- delete higher event entries with date range within date range for the current event
409: IF p_revert_to_state = 'COL' OR p_revert_to_state = 'CLS' OR p_revert_to_state = 'ROLL' THEN
410: IF p_salesrep_id = -1000 THEN
411: DELETE cn_notify_log_all
412: WHERE period_id = p_period_id
413: AND org_id = p_org_id
414: AND status = 'INCOMPLETE'
415: AND (
436: AND action IS NULL
437: AND action_link_id IS NULL;
438: ELSE
439: -- only 'ROLL', p_slearep_id <> -1000 comes here
440: DELETE cn_notify_log_all
441: WHERE period_id = p_period_id
442: AND org_id = p_org_id
443: AND salesrep_id = p_salesrep_id
444: AND status = 'INCOMPLETE'
456: ELSIF p_revert_to_state = 'CALC' AND p_quota_id IS NULL THEN
457: IF p_salesrep_id = -1000 THEN
458: -- delete 'CALC' with null quota for particular salesreps
459: -- and 'CALC' with quota_id
460: DELETE cn_notify_log_all
461: WHERE period_id = p_period_id
462: AND org_id = p_org_id
463: AND salesrep_id <> -1000
464: AND revert_state = p_revert_to_state
471: END IF;
472: ELSIF p_revert_to_state = 'POP' THEN
473: -- delete 'CALC' entries with the same quota_id
474: -- since a 'POP' entry ensures that the salesrep be picked up.
475: DELETE cn_notify_log_all
476: WHERE period_id = p_period_id
477: AND org_id = p_org_id
478: AND salesrep_id = p_salesrep_id
479: AND revert_state = 'CALC'
867: -- if no action/action_link_id is provided
868: -- check lower event or 'CALC' with no quota_id
869: CURSOR l_chk_calc_lower_events_csr IS
870: SELECT 1
871: FROM cn_notify_log_all
872: WHERE period_id = p_period_id
873: AND org_id = p_org_id
874: AND (salesrep_id = p_salesrep_id OR salesrep_id = -1000)
875: AND status = 'INCOMPLETE'
876: AND (revert_state IN('COL', 'CLS', 'ROLL') OR(revert_state = 'CALC' AND quota_id IS NULL));
877:
878: CURSOR l_existence_check_csr IS
879: SELECT 1
880: FROM cn_notify_log_all
881: WHERE period_id = p_period_id
882: AND org_id = p_org_id
883: AND salesrep_id = p_salesrep_id
884: AND status = 'INCOMPLETE'
944: INTO l_notify_log_id
945: FROM DUAL;
946:
947: -- insert into cn_notify_log
948: INSERT INTO cn_notify_log_all
949: (
950: notify_log_id
951: , salesrep_id
952: , period_id
999:
1000: -- need to mark subsequent periods when not 'NCALC'
1001: IF p_revert_to_state <> 'NCALC' THEN
1002: -- delete all 'CALC' entry with not null quota_id
1003: DELETE cn_notify_log_all
1004: WHERE salesrep_id = p_salesrep_id
1005: AND org_id = p_org_id
1006: AND period_id = p_period_id
1007: AND revert_state = p_revert_to_state