[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_ROLLUP_PVT
Source
1 PACKAGE BODY cn_calc_rollup_pvt AS
2 -- $Header: cnvcrolb.pls 120.9.12010000.3 2008/09/22 13:41:19 rajukum ship $
3 g_pkg_name CONSTANT VARCHAR2( 30 ) := 'CN_CALC_ROLLUP_PVT';
4 g_file_name CONSTANT VARCHAR2( 12 ) := 'cnvcrolb.pls';
5 g_last_update_date DATE := SYSDATE;
6 g_last_updated_by NUMBER := fnd_global.user_id;
7 g_creation_date DATE := SYSDATE;
8 g_created_by NUMBER := fnd_global.user_id;
9 g_last_update_login NUMBER := fnd_global.login_id;
10 g_rowid VARCHAR2( 30 );
11 g_program_type VARCHAR2( 30 );
12 g_system_rollup_flag VARCHAR2( 1 );
13 g_roll_sum_trx_flag VARCHAR2( 1 );
14 g_custom_aggr_trx_flag VARCHAR2( 1 );
15 g_srp_validation_flag VARCHAR2( 1 );
16 g_mark_event_flag VARCHAR2( 1 );
17 g_multi_rollup_profile VARCHAR2( 30 );
18 g_mode VARCHAR2( 30 );
19 g_event_log_id NUMBER( 15 );
20 g_end_of_time DATE := TO_DATE( '12-31-9999', 'MM-DD-YYYY' );
21 g_org_id NUMBER( 15 );
22 user_aggregate_exception EXCEPTION;
23
24 TYPE num_tbl_type IS TABLE OF NUMBER
25 INDEX BY BINARY_INTEGER;
26
27 TYPE date_tbl_type IS TABLE OF DATE
28 INDEX BY BINARY_INTEGER;
29
30 TYPE str_tbl_type IS TABLE OF VARCHAR2( 30 )
31 INDEX BY BINARY_INTEGER;
32
33 CURSOR GROUP_ID( p_processed_date DATE, p_salesrep_id NUMBER ) IS
34 SELECT comp_group_id
35 FROM cn_srp_comp_groups_v
36 WHERE salesrep_id = p_salesrep_id
37 AND org_id = g_org_id
38 AND p_processed_date >= start_date_active
39 AND ( end_date_active IS NULL OR p_processed_date <= end_date_active )
40 AND ROWNUM = 1;
41
42 CURSOR verify_group( p_processed_date DATE, p_salesrep_id NUMBER, p_comp_group_id NUMBER ) IS
43 SELECT comp_group_id
44 FROM cn_srp_comp_groups_v
45 WHERE salesrep_id = p_salesrep_id
46 AND org_id = g_org_id
47 AND p_processed_date > start_date_active
48 AND ( end_date_active IS NULL OR p_processed_date <= end_date_active )
49 AND comp_group_id = p_comp_group_id;
50
51 -- 1. assume that the user wants to aggregate trxs within the date range specified in the calculation request
52 -- 2. it is complete calculation
53 -- 3. no trx skipping rollup phase
54 PROCEDURE aggregate_trx( p_physical_batch_id IN NUMBER ) IS
55 g_intel_calc_flag VARCHAR2( 1 );
56 rep_ids num_tbl_type;
57 header_ids num_tbl_type;
58 rollup_dates date_tbl_type;
59 group_ids num_tbl_type;
60 rev_class_ids num_tbl_type;
61 trx_types str_tbl_type;
62 amounts num_tbl_type;
63 units num_tbl_type;
64 processed_dates date_tbl_type;
65 period_ids num_tbl_type;
66 l_start_date DATE;
67 l_end_date DATE;
68 l_start_period_id NUMBER;
69
70 CURSOR intel_calc_flag IS
71 SELECT NVL( intelligent_flag, 'N' )
72 FROM cn_calc_submission_batches_all
73 WHERE logical_batch_id = ( SELECT logical_batch_id
74 FROM cn_process_batches_all
75 WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1 );
76
77 CURSOR sum_trxs IS
78 SELECT ch.direct_salesrep_id, ch.processed_period_id, ch.processed_date
79 , NVL( ch.rollup_date, ch.processed_date ), ch.comp_group_id, ch.revenue_class_id, ch.trx_type
80 , SUM( ch.transaction_amount ), SUM( ch.quantity )
81 FROM cn_commission_headers_all ch, cn_process_batches_all pb
82 WHERE pb.physical_batch_id = p_physical_batch_id
83 AND ch.direct_salesrep_id = pb.salesrep_id
84 AND ch.processed_date BETWEEN pb.start_date AND pb.end_date
85 AND ch.org_id = pb.org_id
86 AND ( ( g_intel_calc_flag = 'N' AND ch.status = 'CLS' )
87 OR ( g_intel_calc_flag = 'Y' AND ch.status = 'CLS' AND ch.parent_header_id IS NULL ) )
88 GROUP BY ch.direct_salesrep_id
89 , ch.processed_period_id
90 , ch.processed_date
91 , NVL( ch.rollup_date, ch.processed_date )
92 , ch.comp_group_id
93 , ch.revenue_class_id
94 , ch.trx_type;
95 BEGIN
96 IF ( fnd_log.level_procedure >= fnd_log.g_current_runtime_level ) THEN
97 fnd_log.STRING( fnd_log.level_procedure
98 , 'cn.plsql.cn_calc_rollup_pvt.aggregate_trx.begin'
99 , 'Beginning of aggregate_trx ...' );
100 END IF;
101
102 OPEN intel_calc_flag;
103 FETCH intel_calc_flag INTO g_intel_calc_flag;
104 CLOSE intel_calc_flag;
105
106 OPEN sum_trxs;
107 FETCH sum_trxs BULK COLLECT INTO rep_ids
108 , period_ids
109 , processed_dates
110 , rollup_dates
111 , group_ids
112 , rev_class_ids
113 , trx_types
114 , amounts
115 , units;
116 CLOSE sum_trxs;
117
118 IF rep_ids.COUNT > 0 THEN
119 FORALL i IN rep_ids.FIRST .. rep_ids.LAST
120 INSERT INTO cn_commission_headers_all
121 ( commission_header_id, direct_salesrep_id, processed_date, processed_period_id
122 , trx_type, status, rollup_date, comp_group_id, revenue_class_id, transaction_amount
123 , quantity, pre_processed_code, parent_header_id, creation_date, created_by, org_id )
124 VALUES ( cn_commission_headers_s.NEXTVAL, rep_ids( i ), processed_dates( i ), period_ids( i )
125 , trx_types( i ), 'CLS_SUM', rollup_dates( i ), group_ids( i ), rev_class_ids( i ), amounts( i )
126 , units( i ), 'CRPC', -1, SYSDATE, g_created_by, g_org_id )
127 RETURNING commission_header_id
128 BULK COLLECT INTO header_ids;
129 FORALL i IN rep_ids.FIRST .. rep_ids.LAST
130 UPDATE cn_commission_headers_all
131 SET parent_header_id = header_ids( i )
132 , last_update_date = sysdate
133 , last_updated_by = g_last_updated_by
134 , last_update_login = g_last_update_login
135 WHERE direct_salesrep_id = rep_ids( i )
136 AND processed_period_id = period_ids( i )
137 AND processed_date = processed_dates( i )
138 AND NVL( rollup_date, processed_date ) = rollup_dates( i )
139 AND NVL( comp_group_id, -999999 ) = NVL( group_ids( i ), -999999 )
140 AND revenue_class_id = rev_class_ids( i )
141 AND trx_type = trx_types( i )
142 AND org_id = g_org_id
143 AND ( ( g_intel_calc_flag = 'N' AND status = 'CLS' )
144 OR ( g_intel_calc_flag = 'Y' AND status = 'CLS' AND parent_header_id IS NULL ) );
145 END IF;
146
147 COMMIT;
148
149 IF ( fnd_log.level_procedure >= fnd_log.g_current_runtime_level ) THEN
150 fnd_log.STRING( fnd_log.level_procedure, 'cn.plsql.cn_calc_rollup_pvt.aggregate_trx.end'
151 , 'End of aggregate_trx.' );
152 END IF;
153 END aggregate_trx;
154
155 -- beginning of private procedures
156 PROCEDURE create_comm_line(
157 p_commission_header_id NUMBER
158 , p_salesrep_id NUMBER
159 , p_credited_comp_group_id NUMBER
160 , p_processed_date DATE
161 , p_processed_period_id NUMBER
162 , p_rollup_level NUMBER ) IS
163 l_srp_trx cn_srp_validation_pub.srp_trx_rec_type;
164 l_validation_status VARCHAR2( 1 );
165 l_pending_status VARCHAR2( 1 );
166 l_return_status VARCHAR2( 30 );
167 l_msg_count NUMBER;
168 l_msg_data VARCHAR2( 2000 );
169 l_srp_sum_flag VARCHAR2( 1 );
170
171 CURSOR l_sum_flag IS
172 SELECT NVL( compplans.sum_trx_flag, 'N' )
173 FROM cn_srp_plan_assigns srpplans, cn_comp_plans compplans
174 WHERE compplans.comp_plan_id = srpplans.comp_plan_id
175 AND p_processed_date BETWEEN srpplans.start_date AND NVL( srpplans.end_date, p_processed_date )
176 AND srpplans.salesrep_id = p_salesrep_id;
177 BEGIN
178 -- call marketer validation first if profile set to Yes
179 IF ( g_srp_validation_flag = 'Y' ) THEN
180 l_srp_trx.salesrep_id := p_salesrep_id;
181 l_srp_trx.commission_header_id := p_commission_header_id;
182 cn_srp_validation_pub.validate_trx( p_api_version => 1.0
183 , x_return_status => l_return_status
184 , x_msg_count => l_msg_count
185 , x_msg_data => l_msg_data
186 , p_srp_trx => l_srp_trx
187 , x_validation_status => l_validation_status );
188 ELSE
189 l_return_status := fnd_api.g_ret_sts_success;
190 l_validation_status := 'Y';
191 END IF;
192
193 l_pending_status := 'Y';
194
195 IF ( l_return_status = fnd_api.g_ret_sts_success ) THEN
196 IF ( l_validation_status = 'Y' ) THEN
197 l_pending_status := 'N';
198 END IF;
199 ELSE
200 NULL;
201 END IF;
202
203 OPEN l_sum_flag;
204 FETCH l_sum_flag INTO l_srp_sum_flag;
205 CLOSE l_sum_flag;
206
207 IF ( ( l_srp_sum_flag = 'Y' ) OR( g_roll_sum_trx_flag = 'N' ) ) THEN
208 INSERT INTO cn_commission_lines_all
209 ( commission_line_id, commission_header_id, direct_salesrep_id, pre_processed_code, revenue_class_id
210 , credited_salesrep_id, credited_comp_group_id, pending_status, pending_date, created_during, status
211 , processed_date, processed_period_id, trx_type, created_by, creation_date, rollup_level, org_id )
212 ( SELECT cn_commission_lines_s.NEXTVAL, commission_header_id, direct_salesrep_id, pre_processed_code
213 , revenue_class_id, p_salesrep_id, p_credited_comp_group_id, l_pending_status
214 , DECODE( l_pending_status, 'Y', SYSDATE, NULL ), 'ROLL', 'ROLL', processed_date, processed_period_id
215 , trx_type, g_created_by, g_creation_date, p_rollup_level, org_id
216 FROM cn_commission_headers_all
217 WHERE commission_header_id = p_commission_header_id
218 AND ( NOT EXISTS(
219 SELECT 1
220 FROM cn_commission_lines_all
221 WHERE commission_header_id = p_commission_header_id
222 AND credited_salesrep_id = p_salesrep_id ) ) );
223 ELSE
224 INSERT INTO cn_commission_lines_all
225 ( commission_line_id, commission_header_id, direct_salesrep_id, pre_processed_code, revenue_class_id
226 , credited_salesrep_id, credited_comp_group_id, pending_status, pending_date, created_during, status
227 , processed_date, processed_period_id, trx_type, created_by, creation_date, rollup_level, org_id )
228 ( SELECT cn_commission_lines_s.NEXTVAL, commission_header_id, direct_salesrep_id, pre_processed_code
229 , revenue_class_id, p_salesrep_id, p_credited_comp_group_id, l_pending_status
230 , DECODE( l_pending_status, 'Y', SYSDATE, NULL ), 'ROLL', 'ROLL', processed_date, processed_period_id
231 , trx_type, g_created_by, g_creation_date, p_rollup_level, org_id
232 FROM cn_commission_headers_all head
233 WHERE head.parent_header_id = p_commission_header_id
234 AND ( NOT EXISTS(
235 SELECT 1
236 FROM cn_commission_lines_all line
237 WHERE line.commission_header_id = head.commission_header_id
238 AND line.credited_salesrep_id = p_salesrep_id ) ) );
239 END IF;
240
241 IF ( SQL%FOUND ) THEN
242 IF ( g_mark_event_flag = 'Y' ) THEN
243 cn_mark_events_pkg.mark_notify( p_salesrep_id => p_salesrep_id
244 , p_period_id => p_processed_period_id
245 , p_start_date => p_processed_date
246 , p_end_date => p_processed_date
247 , p_quota_id => NULL
248 , p_revert_to_state => 'CALC'
249 , p_event_log_id => g_event_log_id
250 , p_org_id => g_org_id );
251 END IF;
252 END IF;
253 EXCEPTION
254 WHEN OTHERS THEN
255 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
256 fnd_log.STRING( fnd_log.level_unexpected
257 , 'cn.plsql.cn_calc_rollup_pvt.create_comm_line.exception'
258 , 'Failed to create commission line: ' || p_commission_header_id );
259 END IF;
260
261 cn_message_pkg.DEBUG( 'Exception occurs in creating commission lines: ' );
262 cn_message_pkg.DEBUG( SQLERRM );
263 fnd_file.put_line( fnd_file.LOG, SQLERRM );
264 RAISE;
265 END create_comm_line;
266
267 PROCEDURE xroll( p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_start_date DATE, p_end_date DATE ) IS
268 l_group cn_rollup_pvt.group_rec_type;
269 l_group_member cn_rollup_pvt.group_mem_tbl_type;
270 l_return_status VARCHAR2( 30 );
271 l_msg_count NUMBER;
272 l_msg_data VARCHAR2( 2000 );
273 BEGIN
274 IF p_salesrep_id IS NOT NULL THEN
275 -- This is for event: add a group member role
276 UPDATE cn_commission_headers_all
277 SET status = DECODE( parent_header_id, -1, 'CLS_SUM', 'CLS' )
278 , last_update_date = sysdate
279 , last_updated_by = g_last_updated_by
280 , last_update_login = g_last_update_login
281 WHERE direct_salesrep_id = p_salesrep_id
282 AND org_id = g_org_id
283 AND status <> 'OBSOLETE'
284 AND ( NVL( rollup_date, processed_date ) BETWEEN p_start_date AND p_end_date
285 OR processed_date BETWEEN p_start_date AND p_end_date );
286 ELSE
287 -- This is for event: add group usage
288 -- have to call API first to get all the active group member
289 l_group.GROUP_ID := p_comp_group_id;
290 l_group.start_date := p_start_date;
291 l_group.end_date := p_end_date;
292 cn_rollup_pvt.get_active_group_member( p_api_version => 1.0
293 , x_return_status => l_return_status
294 , x_msg_count => l_msg_count
295 , x_msg_data => l_msg_data
296 , p_group => l_group
297 , x_group_mem => l_group_member
298 , p_org_id => g_org_id );
299
300 IF l_group_member.COUNT > 0 THEN
301 FOR eachsrp IN l_group_member.FIRST .. l_group_member.LAST LOOP
302 UPDATE cn_commission_headers_all
303 SET status = DECODE( parent_header_id, -1, 'CLS_SUM', 'CLS' )
304 , last_update_date = sysdate
305 , last_updated_by = g_last_updated_by
306 , last_update_login = g_last_update_login
307 WHERE direct_salesrep_id = l_group_member( eachsrp ).salesrep_id
308 AND org_id = g_org_id
309 AND ( NVL( rollup_date, processed_date ) BETWEEN l_group_member( eachsrp ).start_date
310 AND l_group_member( eachsrp ).end_date
311 OR processed_date BETWEEN l_group_member( eachsrp ).start_date AND l_group_member( eachsrp ).end_date )
312 AND status <> 'OBSOLETE';
313 END LOOP; --end of eachsrp loop
314 END IF; -- end of echecking l_group_member.count > 0
315 END IF; -- end of checking salesrep_id is not null
316 EXCEPTION
317 WHEN OTHERS THEN
318 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
319 fnd_log.STRING( fnd_log.level_unexpected, 'cn.plsql.cn_calc_rollup_pvt.xroll.exception', SQLERRM );
320 END IF;
321
322 cn_message_pkg.DEBUG( 'Exception occurs in cn_calc_rollup_pvt.xroll:' );
323 cn_message_pkg.DEBUG( SQLERRM );
324 fnd_file.put_line( fnd_file.LOG, 'In cn_calc_rollup_pvt.xroll: ' || SQLERRM );
325 RAISE;
326 END xroll;
327
328 PROCEDURE source_cls( p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_start_date DATE, p_end_date DATE ) IS
329 CURSOR revert_lines( p_salesrep_id NUMBER, p_processed_date DATE, p_rollup_date DATE ) IS
330 SELECT commission_line_id
331 FROM cn_commission_lines_all line
332 WHERE line.posting_status = 'POSTED'
333 AND line.status = 'CALC'
334 AND line.commission_header_id IN(
335 SELECT header.commission_header_id
336 FROM cn_commission_headers_all header
337 WHERE header.direct_salesrep_id = p_salesrep_id
338 AND header.processed_date = p_processed_date
339 AND header.org_id = g_org_id
340 AND NVL( header.parent_header_id, -1 ) = -1
341 AND NVL( header.rollup_date, header.processed_date ) = NVL( p_rollup_date, p_processed_date ) );
342
343 CURSOR l_transaction_date IS
344 SELECT DISTINCT processed_date, rollup_date, processed_period_id
345 FROM cn_commission_headers_all
346 WHERE direct_salesrep_id = p_salesrep_id
347 AND org_id = g_org_id
348 -- AND comp_group_id = p_comp_group_id
349 AND ( NVL( rollup_date, processed_date ) BETWEEN p_start_date AND p_end_date
350 OR processed_date BETWEEN p_start_date AND p_end_date )
351 AND status <> 'OBSOLETE'
352 GROUP BY processed_date, rollup_date, processed_period_id;
353
354 l_srp cn_rollup_pvt.srp_rec_type;
355 l_active_group cn_rollup_pvt.active_group_tbl_type;
356 l_return_status VARCHAR2( 30 );
357 l_msg_count NUMBER;
358 l_msg_data VARCHAR2( 2000 );
359 l_status VARCHAR2( 30 );
360 BEGIN
361 FOR eachdate IN l_transaction_date LOOP
362 l_srp.salesrep_id := p_salesrep_id;
363 l_srp.start_date := eachdate.processed_date;
364 l_srp.end_date := eachdate.processed_date;
365 l_status := NULL;
366 cn_rollup_pvt.get_active_group( p_api_version => 1.0
367 , x_return_status => l_return_status
368 , x_msg_count => l_msg_count
369 , x_msg_data => l_msg_data
370 , p_org_id => g_org_id
371 , p_srp => l_srp
372 , x_active_group => l_active_group );
373
374 IF ( l_active_group.COUNT = 0 AND eachdate.rollup_date <> eachdate.processed_date ) THEN
375 l_srp.start_date := eachdate.rollup_date;
376 l_srp.end_date := eachdate.rollup_date;
377 cn_rollup_pvt.get_active_group( p_api_version => 1.0
378 , x_return_status => l_return_status
379 , x_msg_count => l_msg_count
380 , x_msg_data => l_msg_data
381 , p_org_id => g_org_id
382 , p_srp => l_srp
383 , x_active_group => l_active_group );
384 END IF;
385
386 -- if the current active group is not the same as the group specified in the notify log
387 IF ( ( l_active_group.COUNT = 0 )
388 OR ( l_active_group.COUNT > 0 AND l_active_group( 0 ).GROUP_ID <> p_comp_group_id ) ) THEN
389 -- no active role for this group memeber
390 -- need to remove all rolled up transaction
391 -- and set the source transactio status to 'XROLL'
392 IF ( l_active_group.COUNT = 0 ) THEN
393 l_status := 'XROLL';
394 END IF;
395
396 UPDATE cn_commission_headers_all
397 SET status = NVL( l_status, DECODE( parent_header_id, -1, 'CLS_SUM', 'CLS' ) )
398 , comp_group_id = NULL
399 , last_update_date = sysdate
400 , last_updated_by = g_last_updated_by
401 , last_update_login = g_last_update_login
402 WHERE direct_salesrep_id = p_salesrep_id
403 AND org_id = g_org_id
404 AND status <> 'OBSOLETE'
405 AND processed_date = eachdate.processed_date
406 AND NVL( parent_header_id, -1 ) = -1
407 AND NVL( rollup_date, processed_date ) = NVL( eachdate.rollup_date, eachdate.processed_date );
408
409 UPDATE cn_srp_intel_periods_all
410 SET process_all_flag = 'Y'
411 WHERE period_id = eachdate.processed_period_id
412 AND org_id = g_org_id
413 AND salesrep_id IN(
414 SELECT DISTINCT line.credited_salesrep_id
415 FROM cn_commission_lines_all line, cn_commission_headers_all header
416 WHERE line.commission_header_id = header.commission_header_id
417 AND header.direct_salesrep_id = p_salesrep_id
418 AND header.processed_date = eachdate.processed_date
419 AND header.org_id = g_org_id
420 AND NVL( header.parent_header_id, -1 ) = -1
421 AND NVL( header.rollup_date, header.processed_date ) =
422 NVL( eachdate.rollup_date, eachdate.processed_date ) );
423
424 FOR line IN revert_lines( p_salesrep_id, eachdate.processed_date, eachdate.rollup_date ) LOOP
425 cn_formula_common_pkg.revert_posting_line( line.commission_line_id );
426 END LOOP;
427
428 DELETE FROM cn_commission_lines_all line
429 WHERE line.commission_header_id IN(
430 SELECT header.commission_header_id
431 FROM cn_commission_headers_all header
432 WHERE header.direct_salesrep_id = p_salesrep_id
433 AND header.org_id = g_org_id
434 AND header.processed_date = eachdate.processed_date
435 AND NVL( header.parent_header_id, -1 ) = -1
436 AND NVL( header.rollup_date, header.processed_date ) =
437 NVL( eachdate.rollup_date, eachdate.processed_date ) );
438 END IF;
439 END LOOP;
440 EXCEPTION
441 WHEN OTHERS THEN
442 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
443 fnd_log.STRING( fnd_log.level_unexpected, 'cn.plsql.cn_calc_rollup_pvt.source_cls.exception', SQLERRM );
444 END IF;
445
446 cn_message_pkg.DEBUG( 'Exception occurs in cn_calc_rollup_pvt.source_cls:' );
447 cn_message_pkg.DEBUG( SQLERRM );
448 fnd_file.put_line( fnd_file.LOG, 'In cn_calc_rollup_pvt.source_cls: ' || SQLERRM );
449 RAISE;
450 END source_cls;
451
452 PROCEDURE pull( p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_start_date DATE, p_end_date DATE, p_action VARCHAR2 ) IS
453 l_group cn_rollup_pvt.group_rec_type;
454 l_group_tbl cn_rollup_pvt.group_tbl_type;
455 l_group_member cn_rollup_pvt.group_mem_tbl_type;
456 l_srp cn_rollup_pvt.srp_rec_type;
457 l_active_group cn_rollup_pvt.active_group_tbl_type;
458 l_srp_group cn_rollup_pvt.srp_group_rec_type;
459 l_srp_group_tbl cn_rollup_pvt.srp_group_tbl_type;
460 l_return_status VARCHAR2( 1 );
461 l_msg_count NUMBER( 15 );
462 l_msg_data VARCHAR2( 1000 );
463 l_comp_group_id NUMBER;
464 l_temp_counter NUMBER;
465
466 CURSOR l_transactions_cr( p_salesrep_id NUMBER, p_comp_group_id NUMBER, p_start_date DATE, p_end_date DATE ) IS
467 SELECT commission_header_id, processed_date, processed_period_id
468 FROM cn_commission_headers_all
469 WHERE direct_salesrep_id = p_salesrep_id
470 AND org_id = g_org_id
471 -- AND comp_group_id = p_comp_group_id
472 AND g_system_rollup_flag = 'Y'
473 AND trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
474 AND SUBSTRB( pre_processed_code, 2, 1 ) = 'R'
475 AND status = 'ROLL'
476 AND NVL( rollup_date, processed_date ) BETWEEN p_start_date AND p_end_date;
477
478 CURSOR role_mgr_flag_c( x_salesrep_id NUMBER, x_comp_group_id NUMBER, x_start_date DATE, x_end_date DATE ) IS
479 SELECT manager_flag
480 FROM cn_srp_comp_groups_v
481 WHERE salesrep_id = x_salesrep_id
482 AND org_id = g_org_id
483 AND comp_group_id = x_comp_group_id
484 AND start_date_active <= x_start_date
485 AND NVL( end_date_active, x_end_date ) >= x_end_date;
486
487 l_mgr_role_flag cn_srp_comp_groups_v.manager_flag%TYPE;
488 BEGIN
489 l_group.GROUP_ID := p_comp_group_id;
490 l_group.start_date := p_start_date;
491 l_group.end_date := p_end_date;
492
493 OPEN role_mgr_flag_c( p_salesrep_id, p_comp_group_id, p_start_date, p_end_date );
494 FETCH role_mgr_flag_c INTO l_mgr_role_flag;
495 CLOSE role_mgr_flag_c;
496
497 IF p_action = 'PULL_WITHIN' AND l_mgr_role_flag = 'Y' THEN
498 cn_rollup_pvt.get_active_group_member( p_api_version => 1.0
499 , x_return_status => l_return_status
500 , x_msg_count => l_msg_count
501 , x_msg_data => l_msg_data
502 , p_org_id => g_org_id
503 , p_group => l_group
504 , x_group_mem => l_group_member );
505
506 -- added the sales rep so that new recs in lines are created, if required
507 IF l_group_member.COUNT > 0 THEN
508 l_temp_counter := l_group_member.LAST + 1;
509 ELSE
510 l_temp_counter := 0;
511 END IF;
512
513 l_group_member( l_temp_counter ).salesrep_id := p_salesrep_id;
514 l_group_member( l_temp_counter ).start_date := p_start_date;
515 l_group_member( l_temp_counter ).end_date := p_end_date;
516
517 IF l_group_member.COUNT > 0 THEN
518 FOR i IN l_group_member.FIRST .. l_group_member.LAST LOOP
519 FOR eachtrx IN l_transactions_cr( l_group_member( i ).salesrep_id
520 , p_comp_group_id
521 , l_group_member( i ).start_date
522 , l_group_member( i ).end_date ) LOOP
523 l_comp_group_id := NULL;
524
525 IF ( eachtrx.processed_date > l_group_member( i ).end_date ) THEN
526 OPEN GROUP_ID( eachtrx.processed_date, l_group_member( i ).salesrep_id );
527 FETCH GROUP_ID INTO l_comp_group_id;
528 CLOSE GROUP_ID;
529 ELSE
530 l_comp_group_id := p_comp_group_id;
531 END IF;
532
533 IF ( l_comp_group_id IS NOT NULL ) THEN
534 create_comm_line( eachtrx.commission_header_id
535 , p_salesrep_id
536 , l_comp_group_id
537 , eachtrx.processed_date
538 , eachtrx.processed_period_id
539 , 0 );
540 END IF;
541 END LOOP; -- end of eachtrx
542 END LOOP; -- end of eachsrp
543 END IF; -- end of count check
544 END IF; -- end of action check
545
546 IF p_action IN( 'PULL', 'PULL_BELOW' ) THEN
547 -- get all descendants before p_end_date starting from any possible comp group
548 -- for each descendant, get his trxs with processed_date >= p_start_date and rollup_date <= end_date
549 -- if p_salesrep_id is not compensated for the trx (from the previous step), compensate him on this trx
550 l_srp.salesrep_id := p_salesrep_id;
551 l_srp.start_date := p_start_date - 3650;
552 l_srp.end_date := p_end_date;
553 cn_rollup_pvt.get_active_group( p_api_version => 1.0
554 , x_return_status => l_return_status
555 , x_msg_count => l_msg_count
556 , x_msg_data => l_msg_data
557 , p_org_id => g_org_id
558 , p_srp => l_srp
559 , x_active_group => l_active_group );
560
561 IF ( l_active_group.COUNT > 0 ) THEN
562 FOR k IN l_active_group.FIRST .. l_active_group.LAST LOOP
563 l_srp_group.salesrep_id := p_salesrep_id;
564 l_srp_group.GROUP_ID := l_active_group( k ).GROUP_ID;
565 l_srp_group.start_date := l_active_group( k ).start_date;
566 l_srp_group.end_date := l_active_group( k ).end_date;
567 cn_rollup_pvt.get_descendant_salesrep( p_api_version => 1.0
568 , x_return_status => l_return_status
569 , x_msg_count => l_msg_count
570 , x_msg_data => l_msg_data
571 , p_org_id => g_org_id
572 , p_srp => l_srp_group
573 , x_srp => l_srp_group_tbl );
574
575 --added the sales rep so that new recs in lines are created, if required
576 IF l_srp_group_tbl.COUNT > 0 THEN
577 l_temp_counter := l_srp_group_tbl.LAST + 1;
578 ELSE
579 l_temp_counter := 0;
580 END IF;
581
582 l_srp_group_tbl( l_temp_counter ).salesrep_id := p_salesrep_id;
583 l_srp_group_tbl( l_temp_counter ).GROUP_ID := l_active_group( k ).GROUP_ID;
584 l_srp_group_tbl( l_temp_counter ).start_date := l_active_group( k ).start_date;
585 l_srp_group_tbl( l_temp_counter ).end_date := l_active_group( k ).end_date;
586 l_srp_group_tbl( l_temp_counter ).LEVEL := 0;
587
588 IF l_srp_group_tbl.COUNT > 0 THEN
589 FOR i IN l_srp_group_tbl.FIRST .. l_srp_group_tbl.LAST LOOP
590 FOR eachtrx IN l_transactions_cr( l_srp_group_tbl( i ).salesrep_id
591 , l_srp_group_tbl( i ).GROUP_ID
592 , l_srp_group_tbl( i ).start_date
593 , l_srp_group_tbl( i ).end_date ) LOOP
594 l_comp_group_id := NULL;
595
596 OPEN GROUP_ID( eachtrx.processed_date, p_salesrep_id );
597 FETCH GROUP_ID INTO l_comp_group_id;
598 CLOSE GROUP_ID;
599
600 IF ( l_comp_group_id IS NOT NULL ) THEN
601 create_comm_line( eachtrx.commission_header_id
602 , p_salesrep_id
603 , l_comp_group_id
604 , eachtrx.processed_date
605 , eachtrx.processed_period_id
606 , l_srp_group_tbl( i ).LEVEL );
607 END IF;
608 END LOOP; -- end of eachtrx
609 END LOOP; -- end of eachsrp
610 END IF; -- end of count check
611 END LOOP; -- end active group loop
612 END IF; -- end check of the number of active groups
613 END IF; -- end of action check
614 EXCEPTION
615 WHEN OTHERS THEN
616 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
617 fnd_log.STRING( fnd_log.level_unexpected, 'cn.plsql.cn_calc_rollup_pvt.pull.exception', SQLERRM );
618 END IF;
619
620 cn_message_pkg.DEBUG( 'Exception occurs in cn_calc_rollup_pvt.pull:' );
621 cn_message_pkg.DEBUG( SQLERRM );
622 fnd_file.put_line( fnd_file.LOG, 'In cn_calc_rollup_pvt.pull: ' || SQLERRM );
623 RAISE;
624 END pull;
625
626 PROCEDURE roll_pull(
627 p_comp_group_id NUMBER
628 , p_start_date DATE
629 , p_end_date DATE
630 , p_action VARCHAR2
631 , p_action_link_id NUMBER ) IS
632 l_group cn_rollup_pvt.group_rec_type;
633 l_descendants_grp cn_rollup_pvt.group_tbl_type;
634 l_ancestors_grp cn_rollup_pvt.group_tbl_type;
635 l_ancestors_tbl cn_rollup_pvt.srp_group_tbl_type;
636 l_descendants_tbl cn_rollup_pvt.srp_group_tbl_type;
637 l_return_status VARCHAR2( 1 );
638 l_msg_count NUMBER( 15 );
639 l_msg_data VARCHAR2( 1000 );
640 l_start_date DATE;
641 l_end_date DATE;
642 l_date_range_tbl cn_api.date_range_tbl_type;
643 l_comp_group_id NUMBER;
644 i PLS_INTEGER := 0;
645 l_dummy NUMBER;
646 l_rollup_level PLS_INTEGER;
647
648 CURSOR revert_lines( p_salesrep_id NUMBER, p_commission_header_id NUMBER ) IS
649 SELECT commission_line_id
650 FROM cn_commission_lines_all
651 WHERE credited_salesrep_id = p_salesrep_id
652 AND org_id = g_org_id
653 AND status = 'CALC'
654 AND posting_status = 'POSTED'
655 AND commission_header_id = p_commission_header_id;
656
657 CURSOR ex_ancestors IS
658 SELECT salesrep_id, comp_group_id GROUP_ID, MIN( start_date ) start_date, MAX( end_date ) end_date
659 FROM cn_notify_log_all
660 WHERE action_link_id = p_action_link_id AND org_id = g_org_id AND notify_log_id > p_action_link_id
661 GROUP BY salesrep_id, comp_group_id;
662
663 CURSOR path_check( p_rollup_date DATE, p_parent_salesrep_id NUMBER, p_child_salesrep_id NUMBER ) IS
664 SELECT 1
665 FROM cn_srp_comp_groups_v a1
666 WHERE p_rollup_date BETWEEN start_date_active AND NVL( end_date_active, p_rollup_date )
667 AND salesrep_id = p_parent_salesrep_id
668 AND org_id = g_org_id
669 AND EXISTS(
670 SELECT 1
671 FROM cn_groups_denorm_v
672 WHERE parent_group_id = a1.comp_group_id
673 AND GROUP_ID IN(
674 SELECT comp_group_id
675 FROM cn_srp_comp_groups_v
676 WHERE p_rollup_date BETWEEN start_date_active AND NVL( end_date_active, p_rollup_date )
677 AND salesrep_id = p_child_salesrep_id
678 AND org_id = g_org_id )
679 AND p_rollup_date BETWEEN start_date_active AND NVL( end_date_active, p_rollup_date ) );
680
681 CURSOR rollup_level( p_parent_group_id NUMBER, p_child_group_id NUMBER, p_rollup_date DATE ) IS
682 SELECT denorm_level
683 FROM cn_groups_denorm_v
684 WHERE parent_group_id = p_parent_group_id
685 AND GROUP_ID = p_child_group_id
686 AND p_rollup_date BETWEEN start_date_active AND NVL( end_date_active, p_rollup_date );
687
688 CURSOR l_transactions_cr( l_salesrep_id NUMBER, l_comp_group_id NUMBER, l_start_date DATE, l_end_date DATE ) IS
689 SELECT commission_header_id, processed_date, processed_period_id, NVL( rollup_date, processed_date ) rollup_date
690 FROM cn_commission_headers_all
691 WHERE direct_salesrep_id = l_salesrep_id
692 AND org_id = g_org_id
693 AND comp_group_id = l_comp_group_id
694 AND g_system_rollup_flag = 'Y'
695 AND trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
696 AND SUBSTRB( pre_processed_code, 2, 1 ) = 'R'
697 AND status = 'ROLL'
698 AND NVL( rollup_date, processed_date ) BETWEEN l_start_date AND l_end_date;
699 BEGIN
700 -- Get ancestors
701 FOR ancestor IN ex_ancestors LOOP
702 l_ancestors_tbl( i ).salesrep_id := ancestor.salesrep_id;
703 l_ancestors_tbl( i ).GROUP_ID := ancestor.GROUP_ID;
704 l_ancestors_tbl( i ).start_date := ancestor.start_date;
705 l_ancestors_tbl( i ).end_date := ancestor.end_date;
706 i := i + 1;
707 END LOOP;
708
709 l_group.GROUP_ID := p_comp_group_id;
710 l_group.start_date := p_start_date;
711 l_group.end_date := p_end_date;
712 l_group.LEVEL := 0;
713
714 IF ( l_ancestors_tbl.COUNT = 0 ) THEN
715 RETURN;
716 END IF;
717
718 -- Get descendants
719 cn_rollup_pvt.get_descendant_group( p_api_version => 1.0
720 , x_return_status => l_return_status
721 , x_msg_count => l_msg_count
722 , x_msg_data => l_msg_data
723 , p_group => l_group
724 , x_group => l_descendants_grp );
725 l_descendants_grp( l_descendants_grp.COUNT ) := l_group;
726 cn_rollup_pvt.get_active_group_member( p_api_version => 1.0
727 , x_return_status => l_return_status
728 , x_msg_count => l_msg_count
729 , x_msg_data => l_msg_data
730 , p_org_id => g_org_id
731 , p_group => l_descendants_grp
732 , x_group_mem => l_descendants_tbl );
733
734 IF ( l_descendants_tbl.COUNT > 0 ) THEN
735 FOR l_ancestor IN l_ancestors_tbl.FIRST .. l_ancestors_tbl.LAST LOOP
736 FOR l_descendant IN l_descendants_tbl.FIRST .. l_descendants_tbl.LAST LOOP
737 -- Get the date range which are overlapping
738 cn_api.get_date_range_overlap( a_start_date => l_ancestors_tbl( l_ancestor ).start_date
739 , a_end_date => l_ancestors_tbl( l_ancestor ).end_date
740 , b_start_date => l_descendants_tbl( l_descendant ).start_date
741 , b_end_date => l_descendants_tbl( l_descendant ).end_date
742 , p_org_id => g_org_id
743 , x_date_range_tbl => l_date_range_tbl );
744
745 IF ( l_date_range_tbl.COUNT > 0 ) THEN
746 FOR eachrange IN l_date_range_tbl.FIRST .. l_date_range_tbl.LAST LOOP
747 l_start_date := l_date_range_tbl( eachrange ).start_date;
748 l_end_date := l_date_range_tbl( eachrange ).end_date;
749
750 FOR eachtrx IN l_transactions_cr( l_descendants_tbl( l_descendant ).salesrep_id
751 , l_descendants_tbl( l_descendant ).GROUP_ID
752 , l_start_date
753 , l_end_date ) LOOP
754 IF ( p_action = 'ROLL_PULL' ) THEN
755 l_comp_group_id := NULL;
756
757 IF ( eachtrx.processed_date > l_end_date ) THEN
758 OPEN GROUP_ID( eachtrx.processed_date, l_ancestors_tbl( l_ancestor ).salesrep_id );
759 FETCH GROUP_ID INTO l_comp_group_id;
760 CLOSE GROUP_ID;
761 ELSE
762 l_comp_group_id := l_ancestors_tbl( l_ancestor ).GROUP_ID;
763 END IF;
764
765 IF ( l_comp_group_id IS NOT NULL ) THEN
766 -- get the rollup level between direct_rep and credited_rep
767 l_rollup_level := 0;
768
769 OPEN rollup_level( l_ancestors_tbl( l_ancestor ).GROUP_ID
770 , l_descendants_tbl( l_descendant ).GROUP_ID
771 , eachtrx.rollup_date );
772
773 FETCH rollup_level INTO l_rollup_level;
774 CLOSE rollup_level;
775
776 create_comm_line( p_commission_header_id => eachtrx.commission_header_id
777 , p_salesrep_id => l_ancestors_tbl( l_ancestor ).salesrep_id
778 , p_credited_comp_group_id => l_comp_group_id
779 , p_processed_date => eachtrx.processed_date
780 , p_processed_period_id => eachtrx.processed_period_id
781 , p_rollup_level => l_rollup_level );
782 END IF;
783 ELSE
784 -- check whether there is other rollup path for this trx to be rolled up
785 l_dummy := 0;
786
787 OPEN path_check( eachtrx.rollup_date
788 , l_ancestors_tbl( l_ancestor ).salesrep_id
789 , l_descendants_tbl( l_descendant ).salesrep_id );
790
791 FETCH path_check INTO l_dummy;
792 CLOSE path_check;
793
794 IF ( l_dummy <> 1 ) THEN
795 FOR line IN revert_lines( l_ancestors_tbl( l_ancestor ).salesrep_id, eachtrx.commission_header_id ) LOOP
796 cn_formula_common_pkg.revert_posting_line( line.commission_line_id );
797 END LOOP;
798
799 DELETE cn_commission_lines_all
800 WHERE credited_salesrep_id = l_ancestors_tbl( l_ancestor ).salesrep_id
801 AND commission_header_id IN (
802 SELECT commission_header_id FROM cn_commission_headers
803 WHERE eachtrx.commission_header_id IN (commission_header_id, parent_header_id)
804 );
805
806 IF ( SQL%FOUND ) THEN
807 UPDATE cn_srp_intel_periods_all
808 SET process_all_flag = 'Y'
809 WHERE salesrep_id = l_ancestors_tbl( l_ancestor ).salesrep_id
810 AND org_id = g_org_id
811 AND period_id = eachtrx.processed_period_id;
812 END IF;
813 END IF;
814 END IF;
815 END LOOP; -- End of eachtrx
816 END LOOP; -- End of eachrange
817 END IF;
818 END LOOP; -- End of l_descendant
819 END LOOP; -- End of l_ancestor
820 END IF;
821 EXCEPTION
822 WHEN OTHERS THEN
823 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
824 fnd_log.STRING( fnd_log.level_unexpected, 'cn.plsql.cn_calc_rollup_pvt.roll_pull.exception', SQLERRM );
825 END IF;
826
827 cn_message_pkg.DEBUG( 'Exception occurs in cn_calc_rollup_pvt.roll_pull:' );
828 cn_message_pkg.DEBUG( SQLERRM );
829 fnd_file.put_line( fnd_file.LOG, 'In cn_calc_rollup_pvt.roll_pull: ' || SQLERRM );
830 RAISE;
831 END roll_pull;
832
833 PROCEDURE rollup_new_trx( p_salesrep_id NUMBER, p_start_date DATE, p_end_date DATE ) IS
834 l_role_id NUMBER( 15 );
835 l_current_salesrep_id NUMBER( 15 ) := 0;
836 l_current_comp_group_id NUMBER( 15 ) := 0;
837 l_current_rollup_date DATE;
838 l_current_processed_date DATE;
839 l_prev_commission_header_id NUMBER;
840 l_prev_comp_group_id NUMBER;
841 l_prev_status VARCHAR2( 30 );
842 l_return_status VARCHAR2( 30 );
843 l_msg_count NUMBER;
844 l_msg_data VARCHAR2( 2000 );
845 l_count NUMBER( 15 );
846 l_srp cn_rollup_pvt.srp_rec_type;
847 l_active_group cn_rollup_pvt.active_group_tbl_type;
848 l_mgr_comp_group_id NUMBER( 15 );
849 l_srp_group cn_rollup_pvt.srp_group_rec_type;
850 l_srp_group_ancestor cn_rollup_pvt.srp_group_tbl_type;
851 l_comp_group_id NUMBER( 15 );
852 l_iteration_count PLS_INTEGER;
853
854 CURSOR l_no_rollup_transactions_cr IS
855 SELECT ch.commission_header_id commission_header_id, ch.direct_salesrep_id direct_salesrep_id, ch.comp_group_id
856 , NVL( ch.rollup_date, ch.processed_date ) rollup_date, ch.processed_date processed_date
857 , ch.processed_period_id
858 FROM cn_commission_headers_all ch
859 WHERE ch.direct_salesrep_id = p_salesrep_id
860 AND ch.org_id = g_org_id
861 AND ch.processed_date BETWEEN p_start_date AND p_end_date
862 AND ch.trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
863 AND ( ( g_roll_sum_trx_flag = 'N' AND ch.status = 'CLS' )
864 OR ( g_roll_sum_trx_flag = 'Y' AND ch.status = 'CLS_SUM' ) )
865 AND ( ( SUBSTRB( ch.pre_processed_code, 2, 1 ) = 'N' ) OR( g_system_rollup_flag = 'N' ) );
866
867 CURSOR l_rollup_transactions_cr IS
868 SELECT ch.commission_header_id commission_header_id, ch.direct_salesrep_id direct_salesrep_id
869 , ch.comp_group_id comp_group_id, NVL( ch.rollup_date, ch.processed_date ) rollup_date
870 , ch.processed_date processed_date, ch.processed_period_id, ch.trx_type, ch.revenue_class_id
871 , ch.pre_processed_code
872 FROM cn_commission_headers_all ch
873 WHERE ch.direct_salesrep_id = p_salesrep_id
874 AND ch.org_id = g_org_id
875 AND ch.processed_date BETWEEN p_start_date AND p_end_date
876 AND ch.trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
877 AND ( ( g_roll_sum_trx_flag = 'N' AND ch.status = 'CLS' )
878 OR ( g_roll_sum_trx_flag = 'Y' AND ch.status = 'CLS_SUM' ) )
879 AND SUBSTRB( ch.pre_processed_code, 2, 1 ) = 'R'
880 AND g_system_rollup_flag = 'Y'
881 ORDER BY ch.direct_salesrep_id, ch.comp_group_id, NVL( ch.rollup_date, ch.processed_date ), ch.processed_date;
882
883 CURSOR rollup_lines( p_commission_header_id NUMBER ) IS
884 SELECT DISTINCT credited_salesrep_id, credited_comp_group_id, rollup_level
885 FROM cn_commission_lines_all
886 WHERE commission_header_id = p_commission_header_id
887 UNION
888 SELECT DISTINCT lines.credited_salesrep_id, lines.credited_comp_group_id, lines.rollup_level
889 FROM cn_commission_headers_all head, cn_commission_lines_all lines
890 WHERE head.parent_header_id = p_commission_header_id
891 AND head.commission_header_id = lines.commission_header_id;
892 BEGIN
893 -- Processing no rollup transaction
894 FOR eachtrx IN l_no_rollup_transactions_cr LOOP
895 IF eachtrx.comp_group_id IS NULL THEN
896 l_srp.salesrep_id := eachtrx.direct_salesrep_id;
897 l_srp.start_date := eachtrx.processed_date;
898 l_srp.end_date := eachtrx.processed_date;
899 cn_rollup_pvt.get_active_group( p_api_version => 1.0
900 , x_return_status => l_return_status
901 , x_msg_count => l_msg_count
902 , x_msg_data => l_msg_data
903 , p_org_id => g_org_id
904 , p_srp => l_srp
905 , x_active_group => l_active_group );
906
907 IF ( l_active_group.COUNT = 1 OR( l_active_group.COUNT > 1 AND g_multi_rollup_profile = 'Y' ) ) THEN
908 create_comm_line( p_commission_header_id => eachtrx.commission_header_id
909 , p_salesrep_id => eachtrx.direct_salesrep_id
910 , p_credited_comp_group_id => l_active_group( l_active_group.FIRST ).GROUP_ID
911 , p_processed_date => eachtrx.processed_date
912 , p_processed_period_id => eachtrx.processed_period_id
913 , p_rollup_level => 0 );
914 l_comp_group_id := l_active_group( l_active_group.FIRST ).GROUP_ID;
915
916 UPDATE cn_commission_headers_all
917 SET status = 'ROLL'
918 , comp_group_id = l_comp_group_id
919 , last_update_date = sysdate
920 , last_updated_by = g_last_updated_by
921 , last_update_login = g_last_update_login
922 WHERE commission_header_id = eachtrx.commission_header_id;
923 ELSE
924 -- No group information is available on transaction.
925 -- Change the status to 'XROLL'
926 UPDATE cn_commission_headers_all
927 SET status = 'XROLL'
928 , last_update_date = sysdate
929 , last_updated_by = g_last_updated_by
930 , last_update_login = g_last_update_login
931 WHERE commission_header_id = eachtrx.commission_header_id;
932 END IF; -- end of active_group = 1
933 ELSE
934 -- comp group info is given, create
935 -- create transaction for the direct salesrep
936
937 -- verify the given comp group
938 l_comp_group_id := NULL;
939
940 OPEN verify_group( eachtrx.processed_date, eachtrx.direct_salesrep_id, eachtrx.comp_group_id );
941 FETCH verify_group INTO l_comp_group_id;
942 CLOSE verify_group;
943
944 l_srp.salesrep_id := eachtrx.direct_salesrep_id;
945 l_srp.start_date := eachtrx.processed_date;
946 l_srp.end_date := eachtrx.processed_date;
947 cn_rollup_pvt.get_active_group( p_api_version => 1.0
948 , x_return_status => l_return_status
949 , x_msg_count => l_msg_count
950 , x_msg_data => l_msg_data
951 , p_org_id => g_org_id
952 , p_srp => l_srp
953 , x_active_group => l_active_group );
954
955 IF ( l_comp_group_id IS NULL AND l_active_group.COUNT >= 1 ) THEN
956 l_comp_group_id := l_active_group( l_active_group.FIRST ).GROUP_ID;
957 END IF;
958
959 IF ( l_comp_group_id IS NOT NULL
960 AND ( l_active_group.COUNT = 1 OR( l_active_group.COUNT > 1 AND g_multi_rollup_profile = 'Y' ) ) ) THEN
961 create_comm_line( p_commission_header_id => eachtrx.commission_header_id
962 , p_salesrep_id => eachtrx.direct_salesrep_id
963 , p_credited_comp_group_id => l_comp_group_id
964 , p_processed_date => eachtrx.processed_date
965 , p_processed_period_id => eachtrx.processed_period_id
966 , p_rollup_level => 0 );
967
968 UPDATE cn_commission_headers_all
969 SET status = 'ROLL'
970 , comp_group_id = l_comp_group_id
971 , last_update_date = sysdate
972 , last_updated_by = g_last_updated_by
973 , last_update_login = g_last_update_login
974 WHERE commission_header_id = eachtrx.commission_header_id;
975 ELSE
976 UPDATE cn_commission_headers_all
977 SET status = 'XROLL'
978 , last_update_date = sysdate
979 , last_updated_by = g_last_updated_by
980 , last_update_login = g_last_update_login
981 WHERE commission_header_id = eachtrx.commission_header_id;
982 END IF;
983 END IF; -- End of eechtrx.comp_group_id
984 END LOOP; -- End of eachtrx
985
986 FOR eachtrx IN l_rollup_transactions_cr LOOP
987 IF ( eachtrx.direct_salesrep_id = l_current_salesrep_id
988 AND ( eachtrx.comp_group_id IS NULL OR g_multi_rollup_profile = 'Y' )
989 AND NVL( eachtrx.rollup_date, eachtrx.processed_date ) = l_current_rollup_date
990 AND eachtrx.processed_date = l_current_processed_date ) THEN
991 UPDATE cn_commission_headers_all
992 SET status = l_prev_status
993 , comp_group_id = DECODE( l_prev_status, 'ROLL', l_prev_comp_group_id, NULL )
994 , last_update_date = sysdate
995 , last_updated_by = g_last_updated_by
996 , last_update_login = g_last_update_login
997 WHERE commission_header_id = eachtrx.commission_header_id;
998
999 -- copy the records created for the previous header trx.
1000 FOR roll_line IN rollup_lines( l_prev_commission_header_id ) LOOP
1001 create_comm_line( p_commission_header_id => eachtrx.commission_header_id
1002 , p_salesrep_id => roll_line.credited_salesrep_id
1003 , p_credited_comp_group_id => roll_line.credited_comp_group_id
1004 , p_processed_date => eachtrx.processed_date
1005 , p_processed_period_id => eachtrx.processed_period_id
1006 , p_rollup_level => roll_line.rollup_level );
1007 END LOOP;
1008
1009 GOTO end_of_loop;
1010 END IF;
1011
1012 -- need to be initialized when these are declared
1013 l_srp.salesrep_id := eachtrx.direct_salesrep_id;
1014 l_srp.start_date := eachtrx.processed_date;
1015 l_srp.end_date := eachtrx.processed_date;
1016 l_active_group.DELETE;
1017
1018 IF ( eachtrx.comp_group_id IS NULL OR g_multi_rollup_profile = 'Y' ) THEN
1019 cn_rollup_pvt.get_active_group( p_api_version => 1.0
1020 , x_return_status => l_return_status
1021 , x_msg_count => l_msg_count
1022 , x_msg_data => l_msg_data
1023 , p_org_id => g_org_id
1024 , p_srp => l_srp
1025 , x_active_group => l_active_group );
1026
1027 IF ( l_active_group.COUNT = 0 AND eachtrx.processed_date <> eachtrx.rollup_date ) THEN
1028 l_srp.start_date := eachtrx.rollup_date;
1029 l_srp.end_date := eachtrx.rollup_date;
1030 cn_rollup_pvt.get_active_group( p_api_version => 1.0
1031 , x_return_status => l_return_status
1032 , x_msg_count => l_msg_count
1033 , x_msg_data => l_msg_data
1034 , p_org_id => g_org_id
1035 , p_srp => l_srp
1036 , x_active_group => l_active_group );
1037 END IF;
1038 END IF;
1039
1040 IF ( l_active_group.COUNT = 0 AND( eachtrx.comp_group_id IS NULL OR g_multi_rollup_profile = 'Y' ) ) THEN
1041 UPDATE cn_commission_headers_all
1042 SET status = 'XROLL'
1043 , last_update_date = sysdate
1044 , last_updated_by = g_last_updated_by
1045 , last_update_login = g_last_update_login
1046 WHERE commission_header_id = eachtrx.commission_header_id;
1047
1048 l_prev_status := 'XROLL';
1049 ELSE
1050 l_count := l_active_group.FIRST;
1051 l_iteration_count := 1;
1052
1053 LOOP
1054 IF ( eachtrx.comp_group_id IS NOT NULL AND g_multi_rollup_profile <> 'Y' ) THEN
1055 l_comp_group_id := NULL;
1056
1057 OPEN verify_group( eachtrx.processed_date, eachtrx.direct_salesrep_id, eachtrx.comp_group_id );
1058 FETCH verify_group INTO l_comp_group_id;
1059 CLOSE verify_group;
1060
1061 IF ( l_comp_group_id IS NULL ) THEN
1062 OPEN GROUP_ID( eachtrx.processed_date, eachtrx.direct_salesrep_id );
1063 FETCH GROUP_ID INTO l_comp_group_id;
1064 CLOSE GROUP_ID;
1065 END IF;
1066
1067 IF ( l_comp_group_id IS NULL ) THEN
1068 UPDATE cn_commission_headers_all
1069 SET status = 'XROLL'
1070 , last_update_date = sysdate
1071 , last_updated_by = g_last_updated_by
1072 , last_update_login = g_last_update_login
1073 WHERE commission_header_id = eachtrx.commission_header_id;
1074
1075 l_prev_status := 'XROLL';
1076 EXIT;
1077 END IF;
1078 ELSE
1079 l_comp_group_id := l_active_group( l_count ).GROUP_ID;
1080
1081 IF ( g_multi_rollup_profile = 'Y' ) THEN
1082 NULL;
1083 ELSE
1084 IF ( l_active_group.COUNT > 1 ) THEN
1085 UPDATE cn_commission_headers_all
1086 SET status = 'XROLL'
1087 , last_update_date = sysdate
1088 , last_updated_by = g_last_updated_by
1089 , last_update_login = g_last_update_login
1090 WHERE commission_header_id = eachtrx.commission_header_id;
1091
1092 l_prev_status := 'XROLL';
1093
1094 IF ( l_iteration_count = 2 ) THEN
1095 DELETE FROM cn_commission_lines_all
1096 WHERE commission_header_id = eachtrx.commission_header_id;
1097 END IF;
1098
1099 EXIT;
1100 END IF;
1101 END IF;
1102 END IF;
1103
1104 -- refresh l_active_group if rollup_date <> processed_date and comp_group_id is not specified
1105 IF ( l_iteration_count = 1
1106 AND eachtrx.rollup_date <> l_srp.start_date
1107 AND ( eachtrx.comp_group_id IS NULL OR g_multi_rollup_profile = 'Y' ) ) THEN
1108 l_srp.salesrep_id := eachtrx.direct_salesrep_id;
1109 l_srp.start_date := eachtrx.rollup_date;
1110 l_srp.end_date := eachtrx.rollup_date;
1111 l_active_group.DELETE;
1112 cn_rollup_pvt.get_active_group( p_api_version => 1.0
1113 , x_return_status => l_return_status
1114 , x_msg_count => l_msg_count
1115 , x_msg_data => l_msg_data
1116 , p_org_id => g_org_id
1117 , p_srp => l_srp
1118 , x_active_group => l_active_group );
1119 l_iteration_count := 0;
1120 END IF;
1121
1122 IF ( l_iteration_count > 0
1123 AND ( ( l_current_salesrep_id <> eachtrx.direct_salesrep_id )
1124 OR ( l_current_comp_group_id <> l_comp_group_id )
1125 OR ( l_current_rollup_date <> eachtrx.rollup_date ) ) ) THEN
1126 -- Need to call rollup API again
1127 l_current_salesrep_id := eachtrx.direct_salesrep_id;
1128 l_current_comp_group_id := l_comp_group_id;
1129 l_current_rollup_date := eachtrx.rollup_date;
1130 l_current_processed_date := eachtrx.processed_date;
1131 l_prev_commission_header_id := eachtrx.commission_header_id;
1132 l_srp_group_ancestor.DELETE;
1133 l_srp_group.salesrep_id := eachtrx.direct_salesrep_id;
1134 l_srp_group.GROUP_ID := l_comp_group_id;
1135 l_srp_group.start_date := eachtrx.rollup_date;
1136 l_srp_group.end_date := eachtrx.rollup_date;
1137 cn_rollup_pvt.get_ancestor_salesrep( p_api_version => 1.0
1138 , x_return_status => l_return_status
1139 , x_msg_count => l_msg_count
1140 , x_msg_data => l_msg_data
1141 , p_org_id => g_org_id
1142 , p_srp => l_srp_group
1143 , x_srp => l_srp_group_ancestor );
1144
1145 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1146 UPDATE cn_commission_headers_all
1147 SET status = 'XROLL'
1148 , last_update_date = sysdate
1149 , last_updated_by = g_last_updated_by
1150 , last_update_login = g_last_update_login
1151 WHERE commission_header_id = eachtrx.commission_header_id;
1152
1153 DELETE FROM cn_commission_lines_all
1154 WHERE commission_header_id = eachtrx.commission_header_id;
1155
1156 l_prev_status := 'XROLL';
1157 RAISE fnd_api.g_exc_unexpected_error;
1158 END IF; -- end of check api return status
1159 END IF; -- end of check of whether to call API again
1160
1161 IF ( l_iteration_count < 2 ) THEN
1162 UPDATE cn_commission_headers_all
1163 SET status = 'ROLL'
1164 , comp_group_id = l_comp_group_id
1165 , last_update_date = sysdate
1166 , last_updated_by = g_last_updated_by
1167 , last_update_login = g_last_update_login
1168 WHERE commission_header_id = eachtrx.commission_header_id;
1169
1170 l_prev_comp_group_id := l_comp_group_id;
1171 l_prev_status := 'ROLL';
1172 END IF;
1173
1174 -- create transaction for base rep first
1175 create_comm_line( p_commission_header_id => eachtrx.commission_header_id
1176 , p_salesrep_id => eachtrx.direct_salesrep_id
1177 , p_credited_comp_group_id => l_comp_group_id
1178 , p_processed_date => eachtrx.processed_date
1179 , p_processed_period_id => eachtrx.processed_period_id
1180 , p_rollup_level => 0 );
1181
1182 -- create transactions for each manager
1183 BEGIN
1184 IF ( l_iteration_count > 0 AND l_srp_group_ancestor.COUNT > 0 ) THEN
1185 FOR eachsrp IN l_srp_group_ancestor.FIRST .. l_srp_group_ancestor.LAST LOOP
1186 -- get the comp group active on the processed_date for each manager
1187 IF ( eachtrx.processed_date <> eachtrx.rollup_date ) THEN
1188 l_mgr_comp_group_id := NULL;
1189
1190 OPEN GROUP_ID( eachtrx.processed_date, l_srp_group_ancestor( eachsrp ).salesrep_id );
1191 FETCH GROUP_ID INTO l_mgr_comp_group_id;
1192 CLOSE GROUP_ID;
1193
1194 IF ( l_mgr_comp_group_id IS NOT NULL ) THEN
1195 create_comm_line( p_commission_header_id => eachtrx.commission_header_id
1196 , p_salesrep_id => l_srp_group_ancestor( eachsrp ).salesrep_id
1197 , p_credited_comp_group_id => l_mgr_comp_group_id
1198 , p_processed_date => eachtrx.processed_date
1199 , p_processed_period_id => eachtrx.processed_period_id
1200 , p_rollup_level => l_srp_group_ancestor( eachsrp ).LEVEL );
1201 END IF;
1202 ELSE
1203 create_comm_line( p_commission_header_id => eachtrx.commission_header_id
1204 , p_salesrep_id => l_srp_group_ancestor( eachsrp ).salesrep_id
1205 , p_credited_comp_group_id => l_srp_group_ancestor( eachsrp ).GROUP_ID
1206 , p_processed_date => eachtrx.processed_date
1207 , p_processed_period_id => eachtrx.processed_period_id
1208 , p_rollup_level => l_srp_group_ancestor( eachsrp ).LEVEL );
1209 END IF;
1210 END LOOP; -- End of eachsrp
1211 END IF;
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
1215 fnd_log.STRING( fnd_log.level_unexpected
1216 , 'cn.plsql.cn_calc_rollup_pvt.rollup_new_trx.exception'
1217 , SQLERRM );
1218 END IF;
1219
1220 cn_message_pkg.DEBUG( 'Exception occurs in creating commission lines for managers:' );
1221 cn_message_pkg.DEBUG( SQLERRM );
1222 fnd_file.put_line( fnd_file.LOG, 'In rollup_new_trx creating transaction for managers: ' || SQLERRM );
1223 RAISE;
1224 END;
1225
1226 IF ( l_iteration_count > 0 ) THEN
1227 IF ( ( eachtrx.comp_group_id IS NOT NULL AND g_multi_rollup_profile <> 'Y' ) OR l_count = l_active_group.LAST ) THEN
1228 EXIT;
1229 ELSE
1230 l_count := l_active_group.NEXT( l_count );
1231 END IF;
1232
1233 l_iteration_count := 2;
1234 ELSE
1235 l_iteration_count := 2;
1236
1237 -- if there is no group active on rollup_date, then exit
1238 IF ( l_active_group.COUNT = 0 ) THEN
1239 EXIT;
1240 END IF;
1241 END IF;
1242 END LOOP;
1243 END IF;
1244
1245 <<end_of_loop>>
1246 NULL;
1247 END LOOP; -- End of each trx
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
1251 fnd_log.STRING( fnd_log.level_unexpected, 'cn.plsql.cn_calc_rollup_pvt.rollup_new_trx.exception', SQLERRM );
1252 END IF;
1253
1254 cn_message_pkg.DEBUG( 'Exception occurs in cn_calc_rollup_pvt.rollup_new_trx:' );
1255 cn_message_pkg.DEBUG( SQLERRM );
1256 fnd_file.put_line( fnd_file.LOG, 'In cn_calc_rollup_pvt.rollup_new_trx: ' || SQLERRM );
1257 RAISE;
1258 END rollup_new_trx;
1259
1260 PROCEDURE revalidation( p_physical_batch_id NUMBER ) IS
1261 l_srp_trx cn_srp_validation_pub.srp_trx_rec_type;
1262 l_return_status VARCHAR2( 30 );
1263 l_msg_count NUMBER;
1264 l_msg_data VARCHAR2( 2000 );
1265 l_validation_status VARCHAR2( 1 );
1266 l_pending_status VARCHAR2( 1 );
1267
1268 CURSOR l_transactions_cr IS
1269 SELECT cl.commission_header_id commission_header_id, cl.commission_line_id commission_line_id
1270 , cl.credited_salesrep_id credited_salesrep_id
1271 FROM cn_commission_lines_all cl, cn_process_batches_all pb
1272 WHERE pb.physical_batch_id = p_physical_batch_id
1273 AND pb.salesrep_id = cl.credited_salesrep_id
1274 AND cl.org_id = g_org_id
1275 AND cl.processed_period_id BETWEEN pb.period_id AND pb.end_period_id
1276 AND cl.processed_date BETWEEN pb.start_date AND pb.end_date
1277 AND cl.trx_type NOT IN( 'FORECAST', 'GRP', 'BONUS' )
1278 AND cl.status IN( 'ROLL', 'POP', 'XPOP', 'CALC', 'XCALC' )
1279 AND cl.pending_status = 'Y';
1280 BEGIN
1281 FOR eachtrx IN l_transactions_cr LOOP
1282 l_srp_trx.salesrep_id := eachtrx.credited_salesrep_id;
1283 l_srp_trx.commission_header_id := eachtrx.commission_header_id;
1284 cn_srp_validation_pub.validate_trx( p_api_version => 1.0
1285 , x_return_status => l_return_status
1286 , x_msg_count => l_msg_count
1287 , x_msg_data => l_msg_data
1288 , p_srp_trx => l_srp_trx
1289 , x_validation_status => l_validation_status );
1290 l_pending_status := 'Y';
1291
1292 IF ( l_return_status = fnd_api.g_ret_sts_success ) THEN
1293 IF ( l_validation_status = 'Y' ) THEN
1294 l_pending_status := 'N';
1295
1296 UPDATE cn_commission_lines_all
1297 SET pending_status = 'N'
1298 WHERE commission_line_id = eachtrx.commission_line_id;
1299 END IF;
1300 END IF;
1301 END LOOP;
1302 EXCEPTION
1303 WHEN OTHERS THEN
1304 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
1305 fnd_log.STRING( fnd_log.level_unexpected, 'cn.plsql.cn_calc_rollup_pvt.revaliation.exception', SQLERRM );
1306 END IF;
1307
1308 cn_message_pkg.DEBUG( 'Exception occurs in cn_calc_rollup_pvt.revaliation: ' );
1309 cn_message_pkg.DEBUG( SQLERRM );
1310 fnd_file.put_line( fnd_file.LOG, 'In cn_calc_rollup_pvt.revalidation: ' || SQLERRM );
1311 RAISE;
1312 END revalidation;
1313
1314 --+=========================================================================+
1315 --+ End of private procedures +
1316 --+=========================================================================+
1317
1318 -- API name : rollup_batch
1319 -- Type : Private.
1320 -- Pre-reqs :
1321 -- Usage :
1322 --
1323 -- Desc :
1324 --
1325 --
1326 --
1327 -- Parameters :
1328 -- IN : p_api_version NUMBER Require
1329 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
1330 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
1331 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
1332 -- OUT : x_return_status VARCHAR2(1)
1333 -- x_msg_count NUMBER
1334 -- x_msg_data VARCHAR2(2000)
1335 -- IN : p_physical_batch_id NUMBER(15) Require
1336 --
1337 --
1338 --
1339 --
1340 -- Version : Current version 1.0
1341 -- Initial version 1.0
1342 --
1343 -- Notes :
1344 --
1345 -- End of comments
1346 PROCEDURE rollup_batch(
1347 p_api_version IN NUMBER
1348 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1349 , p_commit IN VARCHAR2 := fnd_api.g_false
1350 , p_validation_level IN VARCHAR2 := fnd_api.g_valid_level_full
1351 , x_return_status OUT NOCOPY VARCHAR2
1352 , x_msg_count OUT NOCOPY NUMBER
1353 , x_msg_data OUT NOCOPY VARCHAR2
1354 , p_physical_batch_id IN NUMBER
1355 , p_mode IN VARCHAR2 := 'NORMAL'
1356 , p_event_log_id IN NUMBER := NULL ) IS
1357 l_api_name CONSTANT VARCHAR2( 30 ) := 'Rollup_batch';
1358 l_api_version CONSTANT NUMBER := 1.0;
1359 l_pay_period_id NUMBER( 15 );
1360 l_log_batch_id NUMBER;
1361 l_is_incremental VARCHAR2( 30 );
1362
1363 CURSOR l_notify_cr IS
1364 SELECT *
1365 FROM ( SELECT event.salesrep_id, event.comp_group_id, event.start_date
1366 , NVL( event.end_date, g_end_of_time ) end_date, event.action, event.notify_log_id
1367 FROM cn_notify_log_all event
1368 WHERE event.physical_batch_id = p_physical_batch_id
1369 AND event.action IN( 'SOURCE_CLS', 'XROLL', 'ROLL_PULL', 'DELETE_ROLL_PULL' )
1370 AND event.status = 'INCOMPLETE'
1371 UNION
1372 SELECT event.salesrep_id, event.comp_group_id, event.start_date
1373 , NVL( event.end_date, g_end_of_time ) end_date, event.action, event.notify_log_id
1374 FROM cn_notify_log_all event, cn_process_batches_all batch
1375 WHERE batch.physical_batch_id = p_physical_batch_id
1376 AND batch.salesrep_id = event.salesrep_id
1377 AND event.org_id = g_org_id
1378 AND event.period_id BETWEEN batch.period_id AND batch.end_period_id
1379 AND event.action IN( 'PULL', 'PULL_WITHIN', 'PULL_BELOW' )
1380 AND event.status = 'INCOMPLETE' ) cur
1381 ORDER BY cur.notify_log_id;
1382
1383 CURSOR l_roll_new_trx_cr IS
1384 SELECT salesrep_id, start_date, end_date
1385 FROM cn_process_batches_all
1386 WHERE physical_batch_id = p_physical_batch_id
1387 ORDER BY process_batch_id;
1388 BEGIN
1389 -- Standard Start of API savepoint
1390 SAVEPOINT rollup_batch;
1391
1392 -- Standard call to check for call compatibility.
1393 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, g_pkg_name ) THEN
1394 RAISE fnd_api.g_exc_unexpected_error;
1395 END IF;
1396
1397 -- Initialize message list if p_init_msg_list is set to TRUE.
1398 IF fnd_api.to_boolean( p_init_msg_list ) THEN
1399 fnd_msg_pub.initialize;
1400 END IF;
1401
1402 -- Initialize API return status to success
1403 x_return_status := fnd_api.g_ret_sts_success;
1404
1405 -- Codes start here
1406 SELECT org_id
1407 INTO g_org_id
1408 FROM cn_process_batches_all
1409 WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1;
1410
1411 g_mark_event_flag := NVL( fnd_profile.VALUE( 'CN_MARK_EVENTS' ), 'N' );
1412 g_srp_validation_flag := NVL( fnd_profile.VALUE( 'CN_SRP_VALIDATION' ), 'N' );
1413 g_roll_sum_trx_flag := NVL( cn_system_parameters.VALUE( 'CN_ROLL_SUM_TRX', g_org_id ), 'N' );
1414 g_custom_aggr_trx_flag := NVL( cn_system_parameters.VALUE( 'CN_CUSTOM_AGGR_TRX', g_org_id ), 'N' );
1415 g_multi_rollup_profile := NVL( fnd_profile.VALUE( 'CN_MULTI_ROLLUP_PATH' ), 'N' );
1416
1417 IF ( UPPER( g_multi_rollup_profile ) = 'YES' ) THEN
1418 g_multi_rollup_profile := 'Y';
1419 END IF;
1420
1421 g_mode := p_mode;
1422 g_event_log_id := p_event_log_id;
1423
1424 SELECT NVL( srp_rollup_flag, 'N' )
1425 INTO g_system_rollup_flag
1426 FROM cn_repositories_all
1427 WHERE org_id = g_org_id;
1428
1429 IF p_mode = 'NORMAL' THEN
1430 SELECT cb.logical_batch_id, cb.intelligent_flag
1431 INTO l_log_batch_id, l_is_incremental
1432 FROM cn_calc_submission_batches_all cb, cn_process_batches_all pb
1433 WHERE cb.logical_batch_id = pb.logical_batch_id
1434 AND pb.physical_batch_id = p_physical_batch_id
1435 AND pb.org_id = cb.org_id
1436 AND ROWNUM = 1;
1437
1438 IF l_is_incremental = 'Y' THEN
1439 FOR event IN l_notify_cr LOOP
1440 -- Perform the update after completing calculation
1441 -- UPDATE cn_notify_log SET status = 'COMPLETE' WHERE notify_log_id = event.notify_log_id;
1442 IF ( fnd_log.level_statement >= fnd_log.g_current_runtime_level ) THEN
1443 fnd_log.STRING( fnd_log.level_statement
1444 , 'cn.plsql.cn_calc_rollup_pvt.rollup_batch.notify_log'
1445 , 'Processing notify log record ID=' || event.notify_log_id );
1446 END IF;
1447
1448 cn_message_pkg.DEBUG('Processing the Notification Log#' || event.notify_log_id || ' : Action = ' || event.action);
1449
1450 IF event.action = 'SOURCE_CLS' THEN
1451 source_cls( p_salesrep_id => event.salesrep_id
1452 , p_comp_group_id => event.comp_group_id
1453 , p_start_date => event.start_date
1454 , p_end_date => NVL( event.end_date, g_end_of_time ) );
1455 ELSIF event.action = 'XROLL' THEN
1456 xroll( p_salesrep_id => event.salesrep_id
1457 , p_comp_group_id => event.comp_group_id
1458 , p_start_date => event.start_date
1459 , p_end_date => NVL( event.end_date, g_end_of_time ) );
1460 ELSIF event.action IN( 'ROLL_PULL', 'DELETE_ROLL_PULL' ) THEN
1461 roll_pull( p_comp_group_id => event.comp_group_id
1462 , p_start_date => event.start_date
1463 , p_end_date => NVL( event.end_date, g_end_of_time )
1464 , p_action => event.action
1465 , p_action_link_id => event.notify_log_id );
1466 ELSIF event.action IN( 'PULL', 'PULL_WITHIN', 'PULL_BELOW' ) THEN
1467 pull( p_salesrep_id => event.salesrep_id
1468 , p_comp_group_id => event.comp_group_id
1469 , p_start_date => event.start_date
1470 , p_end_date => NVL( event.end_date, g_end_of_time )
1471 , p_action => event.action );
1472 END IF;
1473 END LOOP;
1474 ELSE
1475 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
1476 fnd_file.put_line( fnd_file.LOG, 'Full Calc: Skipped the notify log pull calls.' );
1477 END IF;
1478
1479 cn_message_pkg.DEBUG( 'Full Calc: Skipped the notify log pull calls.' );
1480 END IF;
1481 END IF;
1482
1483 COMMIT;
1484
1485 IF ( g_roll_sum_trx_flag = 'Y' ) THEN
1486 IF ( g_custom_aggr_trx_flag = 'Y' ) THEN
1487 BEGIN
1488 cn_aggrt_trx_pkg.aggregate_trx( p_physical_batch_id );
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
1492 fnd_log.STRING( fnd_log.level_unexpected
1493 , 'cn.plsql.cn_calc_rollup_pvt.rollup_batch.exception'
1494 , 'Error in Custom Code: ' || SQLERRM );
1495 END IF;
1496
1497 fnd_file.put_line( fnd_file.LOG, SQLERRM );
1498 fnd_file.put_line( fnd_file.LOG, ' Error in Custom Code - cn_aggrt_trx_pkg.aggregate_trx()' );
1499 RAISE user_aggregate_exception;
1500 END;
1501 ELSE
1502 aggregate_trx( p_physical_batch_id );
1503 END IF;
1504 END IF;
1505
1506 COMMIT;
1507
1508 FOR eachsrp IN l_roll_new_trx_cr LOOP
1509 rollup_new_trx( p_salesrep_id => eachsrp.salesrep_id
1510 , p_start_date => eachsrp.start_date
1511 , p_end_date => eachsrp.end_date );
1512 COMMIT;
1513 END LOOP;
1514
1515 IF ( g_srp_validation_flag = 'Y' ) THEN
1516 revalidation( p_physical_batch_id );
1517 END IF;
1518
1519 -- Standard check of p_commit.
1520 IF fnd_api.to_boolean( p_commit ) THEN
1521 COMMIT WORK;
1522 END IF;
1523
1524 -- Standard call to get message count and if count is 1, get message info.
1525 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false );
1526 EXCEPTION
1527 WHEN fnd_api.g_exc_error THEN
1528 ROLLBACK TO rollup_batch;
1529 x_return_status := fnd_api.g_ret_sts_error;
1530 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false );
1531 WHEN fnd_api.g_exc_unexpected_error THEN
1532 ROLLBACK TO rollup_batch;
1533 x_return_status := fnd_api.g_ret_sts_unexp_error;
1534 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false );
1535 WHEN OTHERS THEN
1536 ROLLBACK TO rollup_batch;
1537 x_return_status := fnd_api.g_ret_sts_unexp_error;
1538
1539 IF fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_unexp_error ) THEN
1540 fnd_msg_pub.add_exc_msg( g_pkg_name, l_api_name );
1541 END IF;
1542
1543 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false );
1544
1545 IF ( fnd_log.level_unexpected >= fnd_log.g_current_runtime_level ) THEN
1546 fnd_log.STRING( fnd_log.level_unexpected, 'cn.plsql.cn_calc_rollup_pvt.rollup_batch.exception', SQLERRM );
1547 END IF;
1548
1549 fnd_file.put_line( fnd_file.LOG, SQLERRM );
1550 END rollup_batch;
1551 END cn_calc_rollup_pvt;