[Home] [Help]
PACKAGE BODY: APPS.CN_PERIODS_API
Source
1 PACKAGE BODY cn_periods_api AS
2 -- $Header: cnsyprb.pls 120.9.12000000.2 2007/08/06 22:10:29 jxsingh ship $
3
4
5 --Package Body Name
6 -- cn_periods_api
7 --Purpose
8 -- API to access information related to cn_periods.
9 --
10 --History:
11 -- 02-27-95 V Young Created by converting some procedure from
12 -- cn_general_utils package, and added some other
13 -- procedures to support new API.
14 -- 07-26-95 A Erickson cn_periods.period_name colum name update.
15 -- 07-24-95 A Erickson check_cn_period_record. set repository_status = F.
16 -- 08-08-95 A Erickson init srp periods when new cn period created.
17 -- 11-28-95 A Erickson Support WHO columns in GL_PERIOD_STATUSES update.
18 -- 01-26-96 A Erickson Added Set_Dates routine for Collection code.
19 -- 03-13-96 A Erickson Remove reference to system start date.
20 -- 26-MAR-03 (gasriniv) updated to fix bug#2804029
21 -- 02-02-07 jxsingh Bug#5707688 + Open Period Log Messages Added.
22
23 payee_pop_failed EXCEPTION;
24 srp_pop_failed EXCEPTION;
25 abort EXCEPTION;
26 conc_fail EXCEPTION;
27
28 TYPE requests IS TABLE of NUMBER(15) INDEX BY BINARY_INTEGER;
29
30 --
31
32 /* ----------------------------------------------------------------------- */
33 --
34 -- Procedure Name
35 -- Update_GL_Status
36 -- Purpose
37 -- Update status in GL_PERIOD_STATUSES
38 --
39
40 PROCEDURE update_gl_status ( x_org_id NUMBER,
41 x_period_name VARCHAR2,
42 x_closing_status VARCHAR2,
43 x_forecast_flag VARCHAR2,
44 x_application_id NUMBER,
45 x_set_of_books_id NUMBER,
46 x_freeze_flag VARCHAR2,
47 x_last_update_date DATE,
48 x_last_update_login NUMBER,
49 x_last_updated_by NUMBER)
50 IS
51 CURSOR c IS
52 SELECT gl.closing_status, gl.start_date, gl.end_date,
53 gl.quarter_num, gl.period_year
54 FROM GL_PERIOD_STATUSES GL
55 WHERE gl.application_id = x_application_id
56 AND gl.adjustment_period_flag = 'N'
57 and gl.set_of_books_id = x_set_of_books_id
58 and gl.period_name = x_period_name;
59
60 l_closing_status VARCHAR2(1);
61 l_start_date DATE;
62 l_end_date DATE;
63 l_period_quota_id NUMBER(15);
64 l_quarter_num NUMBER(15);
65 l_period_year NUMBER(15);
66
67 -- Added as part of bug fix bug#2804029
68 CURSOR cn_period_info(p_period_name cn_period_statuses.period_name%TYPE,
69 p_period_year cn_period_statuses.period_year%TYPE) IS
70 SELECT cn.period_status
71 FROM cn_period_statuses cn
72 WHERE cn.period_name = p_period_name
73 AND cn.period_year = p_period_year
74 AND cn.org_id = x_org_id;
75
76 -- for API calls
77 l_return_status varchar2(1);
78 l_msg_count number;
79 l_msg_data VARCHAR2(2000);
80 l_loading_status varchar2(30);
81
82 BEGIN
83
84 OPEN c;
85 FETCH c INTO
86 l_closing_status,
87 l_start_date,
88 l_end_date,
89 l_quarter_num,
90 l_period_year;
91 CLOSE c;
92
93 -- Added as part of bug fix bug#2804029
94 OPEN cn_period_info(x_period_name,l_period_year);
95 FETCH cn_period_info INTO l_closing_status;
96 IF cn_period_info%NOTFOUND THEN
97 l_closing_status := 'N';
98 END IF;
99 CLOSE cn_period_info;
100 -- End add as part of bug fix bug#2804029
101
102
103 UPDATE gl_period_statuses
104 SET closing_status = x_closing_status,
105 last_update_date = x_last_update_date,
106 last_update_login = x_last_update_login,
107 last_updated_by = x_last_updated_by
108 WHERE period_name = x_period_name
109 AND application_id = x_application_id
110 AND set_of_books_id = x_set_of_books_id ;
111
112 -- 1979768
113 IF (x_closing_status = 'O' OR x_closing_status = 'F') THEN
114 UPDATE cn_period_statuses
115 SET period_status = x_closing_status,
116 forecast_flag = x_forecast_flag,
117 freeze_flag = x_freeze_flag,
118 last_update_date = x_last_update_date,
119 last_update_login = x_last_update_login,
120 last_updated_by = x_last_updated_by,
121 object_version_number = object_version_number + 1,
122 processing_status_code = 'PROCESSING'
123 WHERE period_name = x_period_name
124 AND org_id = x_org_id;
125 ELSE
126 UPDATE cn_period_statuses
127 SET period_status = x_closing_status,
128 forecast_flag = x_forecast_flag,
129 freeze_flag = x_freeze_flag,
130 last_update_date = x_last_update_date,
131 last_update_login = x_last_update_login,
132 last_updated_by = x_last_updated_by,
133 object_version_number = object_version_number + 1
134 WHERE period_name = x_period_name
135 AND org_id = x_org_id ;
136 END IF;
137
138 -- bug 1979768 : populate_srp_tables moved to concurrent program
139
140 END update_gl_status ;
141
142 -- Add concurrency to the open periods process
143 PROCEDURE update_error (x_physical_batch_id NUMBER) IS
144 l_user_id NUMBER(15) := fnd_global.user_id;
145 l_resp_id NUMBER(15) := fnd_global.resp_id;
146 l_login_id NUMBER(15) := fnd_global.login_id;
147 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
148 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
149 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
150 BEGIN
151 -- Giving the batch an 'ERROR' status prevents subsequent
152 -- physical processes picking it up.
153 UPDATE cn_process_batches
154 SET status_code = 'ERROR'
155 ,last_update_date = sysdate
156 ,last_update_login = l_login_id
157 ,last_updated_by = l_user_id
158 ,request_id = l_conc_request_id
159 ,program_application_id = l_prog_appl_id
160 ,program_id = l_conc_prog_id
161 ,program_update_date = sysdate
162 WHERE physical_batch_id = x_physical_batch_id;
163 END update_error;
164
165 -- this is called from within a single-org context
166 procedure conc_dispatch
167 (p_parent_proc_audit_id NUMBER,
168 p_logical_batch_id NUMBER,
169 p_org_id NUMBER)
170 is
171 l_primary_request_stack REQUESTS;
172 l_primary_batch_stack REQUESTS;
173 g_batch_total NUMBER := 0;
174 l_temp_id NUMBER := 0;
175 l_new_status VARCHAR2(30) := NULL;
176 l_curr_status VARCHAR2(30) := NULL;
177 l_temp_phys_batch_id NUMBER;
178 primary_ptr NUMBER := 1;
179 l_dev_phase VARCHAR2(80);
180 l_dev_status VARCHAR2(80);
181 l_request_id NUMBER ;
182
183 l_completed_batch_count NUMBER :=0 ;
184 l_call_status BOOLEAN ;
185
186 l_dummy1 VARCHAR2(500);
187 l_dummy2 varchar2(500);
188 l_dummy3 varchar2(500);
189 unfinished BOOLEAN := TRUE;
190
191 l_sleep_time number := 30;
192 l_sleep_time_char VARCHAR2(30);
193 l_failed_request_id NUMBER;
194
195 CURSOR physical_batches IS
196 SELECT DISTINCT physical_batch_id
197 FROM cn_process_batches
198 WHERE logical_batch_id = p_logical_batch_id;
199 BEGIN
200 cn_message_pkg.flush;
201
202 l_sleep_time_char := fnd_profile.value('CN_SLEEP_TIME');
203 IF l_sleep_time_char IS NOT NULL THEN
204 l_sleep_time := to_number(l_sleep_time_char);
205 END IF;
206
207 WHILE unfinished LOOP
208 l_primary_request_stack.delete;
209 l_primary_batch_stack.delete;
210 primary_ptr := 1;
211 l_completed_batch_count := 0;
212 g_batch_total := 0;
213 ---------------------------------------------------------------------
214 fnd_file.put_line(fnd_file.Log, 'Step 5 : Parallel Process Starts');
215 ---------------------------------------------------------------------
216 FOR physical_rec IN physical_batches LOOP
217 fnd_request.set_org_id(p_org_id);
218 l_temp_id := fnd_request.submit_request
219 (application => 'CN',
220 program => 'POPULATE_NEW_PERIODS',
221 description => NULL,
222 start_time => NULL,
223 sub_request => NULL,
224 argument1 => physical_rec.physical_batch_id,
225 argument2 => p_parent_proc_audit_id
226 );
227
228 cn_message_pkg.debug('Submitted Request: ' || l_temp_id);
229 commit;
230
231 g_batch_total := g_batch_total+1;
232 l_primary_request_stack(g_batch_total) := l_temp_id;
233 l_primary_batch_stack(g_batch_total) := physical_rec.physical_batch_id;
234
235 -- If submission failed update the batch record and fail
236 IF l_temp_id = 0 THEN
237 l_temp_phys_batch_id := physical_rec.physical_batch_id;
238 l_failed_request_id := l_temp_id;
239 raise conc_fail;
240 END IF;
241 END LOOP;
242
243 IF (g_batch_total = 0) THEN
244 RAISE no_data_found;
245 END IF;
246
247 cn_message_pkg.debug('Number of batches submitted: ' || g_batch_total);
248 ------------------------------------------------------------------------------------------
249 fnd_file.put_line(fnd_file.Log, 'Step 6 : Total Batches Submitted => ' || g_batch_total);
250 ------------------------------------------------------------------------------------------
251 dbms_lock.sleep(l_sleep_time);
252
253 WHILE l_completed_batch_count <= g_batch_total LOOP
254 IF l_primary_request_stack(primary_ptr) IS NOT NULL THEN
255 l_call_status := fnd_concurrent.get_request_status
256 ( request_id => l_primary_request_stack(primary_ptr),
257 phase => l_dummy1,
258 status => l_dummy2,
259 dev_phase => l_dev_phase,
260 dev_status => l_dev_status,
261 message => l_dummy3);
262 IF (NOT l_call_status) THEN
263 l_failed_request_id := l_primary_request_stack(primary_ptr);
264 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
265 raise conc_fail;
266 END IF;
267
268 IF l_dev_phase = 'COMPLETE' THEN
269 l_failed_request_id := l_primary_request_stack(primary_ptr);
270 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
271
272 l_primary_batch_stack(primary_ptr) := null;
273 l_primary_request_stack(primary_ptr) := null;
274 l_completed_batch_count := l_completed_batch_count +1;
275
276 IF l_dev_status = 'ERROR' THEN
277 raise conc_fail;
278 END IF;
279 END IF;
280 END IF;
281
282 primary_ptr := primary_ptr+1;
283
284 IF l_completed_batch_count = g_batch_total THEN
285 l_completed_batch_count := l_completed_batch_count+1;
286
287 unfinished := FALSE;
288 ELSE
289 IF primary_ptr > g_batch_total THEN
290 dbms_lock.sleep(l_sleep_time);
291 primary_ptr := 1;
292 END IF;
293 END IF;
294 END LOOP;
295 END LOOP;
296 EXCEPTION
297 WHEN no_data_found THEN
298 cn_message_pkg.debug('Conc_dispatch: no physical batches to process');
299 ---------------------------------------------------------------------------
300 fnd_file.put_line(fnd_file.Log, 'Step 7 : No Physical Batches to process');
301 ---------------------------------------------------------------------------
302 WHEN conc_fail THEN
303 update_error(l_temp_phys_batch_id);
304 cn_message_pkg.debug('Concurrent program fails: ' || l_temp_phys_batch_id);
305 --------------------------------------------------------------------------------------------------------------------
306 fnd_file.put_line(fnd_file.Log, 'Step 7 : Concurrent Program (Physical Batch) Failed => ' || l_temp_phys_batch_id);
307 --------------------------------------------------------------------------------------------------------------------
308 WHEN others THEN
309 cn_message_pkg.debug(sqlerrm);
310 cn_message_pkg.rollback_errormsg_commit('Exception in conc_dispatch');
311 ---------------------------------------------------------------------------------
312 fnd_file.put_line(fnd_file.Log, 'Step 7 : Some Other Exception in Conc Dispatch');
313 ---------------------------------------------------------------------------------
314 RAISE;
315 END conc_dispatch;
316
317 -- this routine will be called from within a single-org context
318 PROCEDURE populate_srp_tables(errbuf OUT NOCOPY VARCHAR2,
319 retcode OUT NOCOPY NUMBER)
320 IS
321 l_logical_batch_id NUMBER(15);
322 l_start_period_id number(15);
323 l_end_period_id number(15);
324 l_start_date date;
325 l_end_date date;
326 l_proc_audit_id number;
327 l_reps_total number;
328 l_temp number;
329 l_physical_batch_id number;
330 l_period_quota_id number;
331 l_org_id NUMBER;
332
333 CURSOR pending_periods IS
334 SELECT
335 period_id,
336 start_date,
337 end_date,
338 quarter_num,
339 period_year,
340 period_status
341 FROM cn_period_statuses
342 WHERE processing_status_code = 'PROCESSING'
343 AND period_id between l_start_period_id and l_end_period_id
344 AND request_id is null
345 ORDER BY period_id;
346
347 CURSOR affected_quotas IS
348 SELECT quota_id
349 FROM cn_quotas
350 WHERE (end_date IS NULL OR end_date >= l_start_date)
351 AND start_date <= l_end_date;
352
353 BEGIN
354 retcode := 0; -- success = 0, warning = 1, fail = 2
355
356 -- get current working org ID
357 l_org_id := mo_global.get_current_org_id;
358 IF l_org_id IS NULL THEN
359 -- org ID is not set... raise error
360 RAISE fnd_api.g_exc_unexpected_error;
361 END IF;
362
363 cn_message_pkg.begin_batch(x_process_type => 'OPEN_PERIODS',
364 x_process_audit_id => l_proc_audit_id,
365 x_parent_proc_audit_id => l_proc_audit_id,
366 x_request_id => fnd_global.conc_request_id,
367 p_org_id => l_org_id);
368
369 cn_message_pkg.debug('Beginning of open periods process.');
370 ---------------------------------------------------------------
371 fnd_file.put_line(fnd_file.Log, 'Open Period Process - Start');
372 ---------------------------------------------------------------
373 --initialize message list
374 fnd_msg_pub.initialize;
375
376 -- insert people into cn_process_batches
377 select cn_process_batches_s2.nextval into l_logical_batch_id from dual;
378
379 -- determine the periods to process
380 select min(period_id), max(period_id), min(start_date), max(end_date)
381 into l_start_period_id, l_end_period_id, l_start_date, l_end_date
382 from cn_period_statuses
383 where processing_status_code = 'PROCESSING';
384
385 ----------------------------------------------------------------------------------
386 fnd_file.put_line(fnd_file.Log, 'Step 1 : Period Information');
387 fnd_file.put_line(fnd_file.Log, ' : Min Period Id => '||l_start_period_id);
388 fnd_file.put_line(fnd_file.Log, ' : Max Period Id => '||l_end_period_id);
389 ----------------------------------------------------------------------------------
390
391 insert into cn_process_batches
392 (process_batch_id,
393 logical_batch_id,
394 srp_period_id,
395 period_id,
396 end_period_id,
397 start_date,
398 end_date,
399 salesrep_id,
400 sales_lines_total,
401 status_code,
402 process_batch_type,
403 creation_date,
404 created_by,
405 org_id)
406 select cn_process_batches_s1.nextval,
407 l_logical_batch_id,
408 1,
409 cps.period_id,
410 cps.period_id,
411 cps.start_date,
412 cps.end_date,
413 v.salesrep_id,
414 0,
415 'IN_USE',
416 'OPENING_PERIODS',
417 sysdate,
418 fnd_global.user_id,
419 l_org_id
420 from (select distinct s.salesrep_id
421 from jtf_rs_role_relations rr,
422 cn_rs_salesreps s,
423 jtf_rs_roles_b r
424 where rr.role_resource_id = s.resource_id
425 and rr.role_resource_type = 'RS_INDIVIDUAL'
426 and rr.delete_flag = 'N'
427 and rr.role_id = r.role_id
428 and r.role_type_code = 'SALES_COMP'
429 and (rr.end_date_active IS NULL OR rr.end_date_active >= l_start_date)
430 and rr.start_date_active <= l_end_date) v,
431 cn_period_statuses cps
432 where cps.processing_status_code = 'PROCESSING'
433 and cps.period_id between l_start_period_id and l_end_period_id
434 and not exists
435 (select 1
436 from cn_process_batches
437 where logical_batch_id = (select logical_batch_id
438 from cn_period_statuses
439 where period_id = cps.period_id)
440 and salesrep_id = v.salesrep_id
441 and period_id = cps.period_id
442 and sales_lines_total = 1);
443
444 l_reps_total := SQL%rowcount;
445
446 l_temp := cn_global_var.get_salesrep_batch_size(l_org_id);
447
448 ----------------------------------------------------------------------------
449 fnd_file.put_line(fnd_file.Log, 'Step 2 : Salesrep Batch Size => '||l_temp);
450 fnd_file.put_line(fnd_file.Log, ' : Logical Batch Id => '||l_logical_batch_id);
451 ----------------------------------------------------------------------------
452
453 loop
454 select cn_process_batches_s3.nextval into l_physical_batch_id from dual;
455 ----------------------------------------------------------------------------
456 fnd_file.put_line(fnd_file.Log, ' : Physical Batch Id => '||l_physical_batch_id);
457 ----------------------------------------------------------------------------
458 update cn_process_batches
459 set physical_batch_id = l_physical_batch_id
460 where logical_batch_id = l_logical_batch_id
461 and physical_batch_id is null
462 and rownum <= l_temp;
463
464 if (SQL%notfound) then
465 exit;
466 end if;
467 end loop;
468
469 update cn_process_batches pb
470 set pb.physical_batch_id = (select min(physical_batch_id)
471 from cn_process_batches
472 where logical_batch_id = pb.logical_batch_id
473 and salesrep_id = pb.salesrep_id)
474 where pb.logical_batch_id = l_logical_batch_id;
475
476 -- process affected quotas
477 FOR pending_period IN pending_periods LOOP
478 FOR affected_quota IN affected_quotas LOOP
479 -- populate cn_period_quotas on as needed basis
480 -- keep this API call since not at the salesrep level, plus some
481 -- complicated logic
482 cn_period_quotas_pkg.begin_record
483 (x_operation => 'INSERT',
484 x_period_quota_id => l_period_quota_id,
485 x_period_id => pending_period.period_id,
486 x_quota_id => affected_quota.quota_id,
487 x_period_target => 0,
488 x_itd_target => null, -- will be populated in table handler
489 x_period_payment => 0,
490 x_itd_payment => null, -- will be populated in table handler
491 x_quarter_num => pending_period.quarter_num,
492 x_period_year => pending_period.period_year,
493 x_creation_date => sysdate,
494 x_last_update_date => sysdate,
495 x_last_update_login => fnd_global.login_id,
496 x_last_updated_by => fnd_global.user_id,
497 x_created_by => fnd_global.user_id,
498 x_period_type_code => null, -- not used
499 x_performance_goal => 0
500 );
501 END LOOP;
502 END LOOP;
503
504 -- if affected quotas are updated successfully, then mark the periods with logical_batch_id
505 UPDATE cn_period_statuses
506 SET request_id = l_logical_batch_id
507 WHERE processing_status_code = 'PROCESSING'
508 AND period_id between l_start_period_id AND l_end_period_id;
509
510 -----------------------------------------------------------------------------
511 fnd_file.put_line(fnd_file.Log, 'Step 3 : Quotas are updated successfully');
512 -----------------------------------------------------------------------------
513
514 -----------------------------------------------------------------------------
515 fnd_file.put_line(fnd_file.Log, 'Step 4 : Processing Status => PROCESSING ');
516 -----------------------------------------------------------------------------
517
518 commit;
519
520 conc_dispatch(p_parent_proc_audit_id => l_proc_audit_id,
521 p_logical_batch_id => l_logical_batch_id,
522 p_org_id => l_org_id);
523
524 BEGIN
525 SELECT 1 INTO l_temp FROM dual
526 WHERE NOT exists (SELECT 1
527 FROM cn_process_batches
528 WHERE logical_batch_id = l_logical_batch_id AND status_code = 'ERROR');
529 EXCEPTION
530 WHEN no_data_found THEN
531 cn_message_pkg.debug('There is some physical batch that failed.');
532 RAISE;
533 END;
534
535 ----------------------------------------------------------------------------------------
536 fnd_file.put_line(fnd_file.Log, ' : No Exceptions found in Step 7');
537 fnd_file.put_line(fnd_file.Log, 'Step 8 : All Physical Batches completed successfully');
538 ----------------------------------------------------------------------------------------
539
540 -------------------------------------------------------------------
541 fnd_file.put_line(fnd_file.Log, 'Step 9 : Parallel Process Ends');
542 -------------------------------------------------------------------
543
544 UPDATE cn_period_statuses
545 SET processing_status_code = 'CLEAN'
546 WHERE processing_status_code = 'PROCESSING'
547 AND period_id between l_start_period_id AND l_end_period_id;
548
549 ------------------------------------------------------------------------
550 fnd_file.put_line(fnd_file.Log, 'Step 10 : Processing Status => CLEAN ');
551 ------------------------------------------------------------------------
552
553 cn_message_pkg.debug('End of open periods process.');
554 COMMIT;
555
556 -------------------------------------------------------------
557 fnd_file.put_line(fnd_file.Log, 'Open Period Process - End');
558 -------------------------------------------------------------
559
560 cn_message_pkg.end_batch(l_proc_audit_id);
561 EXCEPTION
562 WHEN OTHERS THEN
563 rollback;
564 retcode := 2;
565 errbuf := SQLCODE||' '||Sqlerrm;
566
567 -- set status to ERROR
568 UPDATE cn_period_statuses
569 SET processing_status_code = 'FAILED'
570 WHERE processing_status_code = 'PROCESSING';
571
572 IF l_org_id IS NOT NULL THEN
573 cn_message_pkg.end_batch(l_proc_audit_id);
574 END IF;
575
576 -- commit status change
577 COMMIT;
578 END populate_srp_tables;
579
580 /* ----------------------------------------------------------------------- */
581 --
582 -- Procedure Name
583 -- Populate_srp_table_runner
584 -- Purpose
585 -- Concurrent program runner to populate srp tables once
586 -- a period is being opened.
587 -- History
588 -- 18-SEP-2001 hlchen created
589 -- 16-feb-2005 mblum modified
590
591 -- this routine will be called from within a single-org context
592 PROCEDURE populate_srp_tables_runner(errbuf OUT nocopy VARCHAR2,
593 retcode OUT nocopy NUMBER,
594 p_physical_batch_id NUMBER,
595 p_parent_proc_audit_id NUMBER) IS
596
597 l_period_id NUMBER(15);
598 l_start_date DATE;
599 l_end_date DATE;
600 l_period_status VARCHAR2(30);
601 l_org_id NUMBER;
602
603 -- bug 4135215 for telecom italia
604 CURSOR affected_srps IS
605 SELECT salesrep_id, period_id, org_id
606 FROM cn_process_batches
607 WHERE physical_batch_id = p_physical_batch_id
608 AND sales_lines_total = 0
609 ORDER BY period_id;
610
611 CURSOR srp_plan_info(l_salesrep_id IN NUMBER, l_start_date IN DATE, l_end_date IN DATE) IS
612 SELECT srp_plan_assign_id, role_id, comp_plan_id,
613 start_date, end_date, salesrep_id
614 FROM cn_srp_plan_assigns
615 WHERE salesrep_id = l_salesrep_id
616 AND role_id IS NOT null
617 AND start_date <= l_start_date AND end_date >= l_end_date;
618
619 CURSOR get_credit_types(l_comp_plan_id IN NUMBER) IS
620 select distinct q.credit_type_id
621 from cn_quota_assigns qa, cn_quotas q
622 where qa.comp_plan_id = l_comp_plan_id
623 and qa.quota_id = q.quota_id;
624
625 CURSOR get_pd_info(l_period_id IN NUMBER) IS
626 SELECT start_date, end_date, period_status
627 FROM cn_period_statuses
628 WHERE period_id = l_period_id;
629
630 CURSOR srp_payee_info(l_salesrep_id in number) IS
631 SELECT spay.quota_id, spay.start_date, spay.end_date, spa.comp_plan_id
632 from cn_srp_payee_assigns spay, cn_srp_plan_assigns spa,
633 cn_srp_quota_assigns sqa
634 where spay.payee_id = l_salesrep_id
635 and (spay.end_date IS NULL OR spay.end_date >= l_start_date)
636 AND spay.start_date <= l_end_date
637 and spay.srp_quota_assign_id = sqa.srp_quota_assign_id
638 and sqa.srp_plan_assign_id = spa.srp_plan_assign_id;
639
640 -- for API calls
641 l_return_status varchar2(1);
642 l_msg_count number;
643 l_msg_data VARCHAR2(2000);
644 l_loading_status varchar2(30);
645
646 l_proc_audit_id NUMBER := NULL;
647 dummy_num NUMBER ;
648
649 l_err_srp_id number:=0;
650 l_err_quota_id number:=0;
651 l_orig_pd_id NUMBER := -1;
652 l_user_id NUMBER := fnd_global.user_id;
653 l_login_id NUMBER := fnd_global.login_id;
654 l_curr_srp_id NUMBER;
655 l_curr_pd_id NUMBER;
656 l_count NUMBER;
657
658 BEGIN
659 --SAVEPOINT populate_srp_tables_runner; -- obsolete now since we commit for each rep
660 retcode := 0; -- success = 0, warning = 1, fail = 2
661
662 -- get current working org ID
663 l_org_id := mo_global.get_current_org_id;
664 IF l_org_id IS NULL THEN
665 -- org ID is not set... raise error
666 RAISE fnd_api.g_exc_unexpected_error;
667 END IF;
668
669 cn_message_pkg.begin_batch
670 (
671 x_parent_proc_audit_id => p_parent_proc_audit_id
672 ,x_process_audit_id => l_proc_audit_id
673 ,x_request_id => fnd_global.conc_request_id
674 ,x_process_type => 'OPEN_PERIODS_RUNNER'
675 ,p_org_id => l_org_id);
676
677 cn_message_pkg.debug('Populate SRP tables runner');
678
679 FOR srp IN affected_srps LOOP
680 IF srp.period_id <> l_orig_pd_id THEN
681 l_orig_pd_id := srp.period_id;
682 OPEN get_pd_info(srp.period_id);
683 FETCH get_pd_info INTO l_start_date, l_end_date, l_period_status;
684 CLOSE get_pd_info;
685 END IF;
686
687 l_curr_srp_id := srp.salesrep_id;
688 l_curr_pd_id := srp.period_id;
689
690 IF l_period_status IN ('O', 'F') THEN
691 -- Populate cn_srp_intel_periods
692 l_err_srp_id := srp.salesrep_id;
693
694 SELECT COUNT(1) INTO l_count
695 FROM cn_srp_intel_periods
696 WHERE period_id = srp.period_id
697 AND salesrep_id = srp.salesrep_id
698 AND org_id = srp.org_id;
699
700 IF l_count = 0 THEN
701 INSERT INTO cn_srp_intel_periods
702 (srp_intel_period_id,
703 salesrep_id,
704 org_id,
705 period_id,
706 processing_status_code,
707 process_all_flag,
708 creation_date,
709 created_by,
710 last_update_date,
711 last_updated_by,
712 last_update_login,
713 start_date,
714 end_date
715 ) VALUES
716 (cn_srp_intel_periods_s.NEXTVAL,
717 srp.salesrep_id,
718 l_org_id,
719 srp.period_id,
720 'CLEAN',
721 'Y',
722 Sysdate,
723 l_user_id,
724 Sysdate,
725 l_user_id,
726 l_login_id,
727 l_start_date,
728 l_end_date);
729 END IF; -- if rec not exist
730 END IF; -- if status = O
731
732 -- check to see if current rep is a payee
733 SELECT COUNT(1) INTO l_count
734 FROM cn_srp_roles
735 WHERE salesrep_id = srp.salesrep_id
736 AND role_id = 54
737 AND org_id = srp.org_id;
738
739 IF l_count > 0 THEN
740 -- rep is a payee
741 for p in srp_payee_info(srp.salesrep_id) LOOP
742 -- populate cn_srp_periods
743 CN_SRP_PERIODS_PVT.Create_Srp_Periods_per_Quota
744 (p_api_version => 1.0,
745 x_return_status => l_return_status,
746 x_msg_count => l_msg_count,
747 x_msg_data => l_msg_data,
748 p_role_id => 54,
749 p_quota_id => p.quota_id,
750 p_comp_plan_id => p.comp_plan_id,
751 p_salesrep_id => srp.salesrep_id,
752 p_start_date => p.start_date,
753 p_end_date => p.end_date,
754 x_loading_status => l_loading_status);
755
756 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
757 RAISE payee_pop_failed;
758 END IF;
759 end loop;
760 END IF;
761
762 for c in srp_plan_info(srp.salesrep_id, l_start_date, l_end_date) LOOP
763 -- populate cn_srp_periods
764 -- complicated logic so keep API call
765 CN_SRP_PERIODS_PVT.Create_Srp_Periods_Per_Quota
766 (p_api_version => 1.0,
767 x_return_status => l_return_status,
768 x_msg_count => l_msg_count,
769 x_msg_data => l_msg_data,
770 p_role_id => c.role_id,
771 p_comp_plan_id => c.comp_plan_id,
772 p_salesrep_id => srp.salesrep_id,
773 p_start_date => c.start_date,
774 p_end_date => c.end_date,
775 p_quota_id => NULL, -- do all quotas
776 p_sync_flag => FND_API.G_FALSE, -- don't sync right here
777 x_loading_status => l_loading_status);
778
779 fnd_file.put_line(fnd_file.Log, l_loading_status);
780 fnd_file.put_line(fnd_file.Log, l_msg_data);
781 fnd_file.put_line(fnd_file.Log, 'role ' || c.role_id);
782 fnd_file.put_line(fnd_file.Log, 'plan ' || c.comp_plan_id);
783 fnd_file.put_line(fnd_file.Log, 'dates ' || c.start_date || ', ' || c.end_date);
784
785
786 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
787 RAISE srp_pop_failed;
788 END IF;
789
790 -- sync accum bals for last period
791 -- complicated logic so keep API call
792 FOR ct IN get_credit_types(c.comp_plan_id) loop
793 cn_srp_periods_pvt.Sync_Accum_Balances_Start_Pd
794 (p_salesrep_id => c.salesrep_id,
795 p_org_id => l_org_id,
796 p_credit_type_id => ct.credit_type_id,
797 p_role_id => c.role_id,
798 p_start_period_id => srp.period_id);
799 END LOOP;
800
801 -- populate cn_srp_period_quotas
802 -- complicated logic so keep API call
803 cn_srp_period_quotas_pkg.insert_record
804 (
805 x_srp_plan_assign_id => c.srp_plan_assign_id
806 ,x_quota_id => NULL
807 ,x_start_period_id => srp.period_id
808 ,x_end_period_id => srp.period_id
809 ,x_start_date => l_start_date
810 ,x_end_date => l_end_date );
811
812 -- populate cn_srp_per_quota_rc_pkg
813 /* cn_srp_per_quota_rc_pkg.insert_record
814 (
815 x_srp_plan_assign_id => c.srp_plan_assign_id
816 ,x_quota_id => null
817 ,x_revenue_class_id => null
818 ,x_start_date => l_start_date
819 ,x_end_date => l_end_date );*/
820 -- denormalizing API call
821 INSERT INTO cn_srp_per_quota_rc
822 ( srp_per_quota_rc_id
823 ,srp_period_quota_id
824 ,srp_plan_assign_id
825 ,salesrep_id
826 ,org_id
827 ,period_id
828 ,quota_id
829 ,revenue_class_id
830 ,target_amount
831 ,year_to_date
832 ,period_to_date
833 ,quarter_to_date)
834 SELECT
835 cn_srp_per_quota_rc_s.nextval
836 ,pq.srp_period_quota_id
837 ,pq.srp_plan_assign_id
838 ,pq.salesrep_id
839 ,l_org_id
840 ,pq.period_id
841 ,pq.quota_id
842 ,qr.revenue_class_id
843 ,0 -- target amount
844 ,0 -- ytd
845 ,0 -- ptd
846 ,0 -- qtd
847 FROM cn_srp_period_quotas pq -- periods that rep/plan uses quota
848 ,cn_quota_rules qr
849 ,cn_quotas q
850 WHERE pq.srp_plan_assign_id = c.srp_plan_assign_id
851 AND pq.quota_id = qr.quota_id
852 AND qr.quota_id = q.quota_id
853 AND q.quota_type_code IN ('EXTERNAL','FORMULA')
854 AND l_period_status in ('O','F')
855 AND NOT EXISTS (SELECT 'srp_period_quota_rc already exists'
856 FROM cn_srp_per_quota_rc spqr
857 WHERE spqr.srp_period_quota_id = pq.srp_period_quota_id
858 AND spqr.srp_plan_assign_id = pq.srp_plan_assign_id
859 AND spqr.revenue_class_id = qr.revenue_class_id)
860 ;
861
862 END LOOP; -- srp_plan_info
863
864 -- mark that this rep, period IS done
865 UPDATE cn_process_batches
866 SET sales_lines_total = 1
867 WHERE physical_batch_id = p_physical_batch_id
868 AND salesrep_id = srp.salesrep_id
869 AND period_id = srp.period_id;
870
871 COMMIT; -- commit for each srp and period
872 END LOOP; -- main srp loop
873
874 cn_message_pkg.debug('Update cn_period_statuses.processing_status_code');
875 cn_message_pkg.debug('Populate SRP tables runner <<');
876 cn_message_pkg.end_batch(l_proc_audit_id);
877 EXCEPTION
878 WHEN payee_pop_failed THEN
879 --ROLLBACK TO populate_srp_tables;
880 retcode := 2;
881
882 -- change status to FAILED
883 UPDATE cn_period_statuses
884 SET processing_status_code = 'FAILED'
885 WHERE processing_status_code = 'PROCESSING'
886 AND period_id = l_curr_pd_id;
887 -- commit status change
888 COMMIT;
889
890 fnd_file.put_line(fnd_file.Log, 'Payee Population Failed');
891 fnd_file.put_line(fnd_file.Log, 'salesrep id'||l_err_srp_id);
892 fnd_file.put_line(fnd_file.Log, 'period id'||l_period_id);
893 fnd_file.put_line(fnd_file.Log, 'quota id'||l_err_quota_id);
894 WHEN srp_pop_failed THEN
895 --ROLLBACK TO populate_srp_tables_runner;
896 retcode := 2;
897
898 -- change status to FAILED
899 UPDATE cn_period_statuses
900 SET processing_status_code = 'FAILED'
901 WHERE processing_status_code = 'PROCESSING'
902 AND period_id = l_curr_pd_id;
903 -- commit status change
904 COMMIT;
905
906 fnd_file.put_line(fnd_file.Log, 'Srp Periods Population Failed');
907 fnd_file.put_line(fnd_file.Log, 'salesrep id'||l_curr_srp_id);
908 fnd_file.put_line(fnd_file.Log, 'period id'||l_curr_pd_id);
909 WHEN OTHERS THEN
910 --ROLLBACK TO populate_srp_tables_runner;
911 errbuf := substr(sqlerrm,1,250);
912 retcode := 2;
913
914 -- change status to FAILED
915 UPDATE cn_period_statuses
916 SET processing_status_code = 'FAILED'
917 WHERE processing_status_code = 'PROCESSING'
918 AND period_id = l_curr_pd_id;
919 -- commit status change
920 COMMIT;
921
922 fnd_file.put_line(fnd_file.Log, 'Unknown Failure');
923 fnd_file.put_line(fnd_file.Log, 'salesrep id'||l_curr_srp_id);
924 fnd_file.put_line(fnd_file.Log, 'period id'||l_curr_pd_id);
925 fnd_file.put_line(fnd_file.Log, 'errbuf'||errbuf);
926 END populate_srp_tables_runner;
927
928
929 /* ----------------------------------------------------------------------- */
930 --
931 -- Procedure Name
932 -- Check_CN_Period_Record
933 -- Purpose
934 -- Create a CN status record in CN_PERIOD_STATUSES if it doesn't exist.
935 --
936
937 PROCEDURE Check_CN_Period_Record (x_org_id NUMBER,
938 x_period_name VARCHAR2,
939 x_closing_status VARCHAR2,
940 x_period_type VARCHAR2,
941 x_period_year NUMBER,
942 x_quarter_num NUMBER,
943 x_period_num NUMBER,
944 x_period_set_name VARCHAR2,
945 x_start_date DATE,
946 x_end_date DATE,
947 x_freeze_flag VARCHAR2,
948 x_repository_id NUMBER) IS
949
950 x_period_id NUMBER(15);
951 x_period_type_id NUMBER(15);
952 x_period_set_id NUMBER(15);
953 x_dummy NUMBER(15);
954
955 l_return_status VARCHAR2(30);
956 l_loading_status VARCHAR2(30);
957 l_msg_count NUMBER;
958 l_msg_data VARCHAR2(2000);
959
960 CURSOR c1 IS
961 SELECT period_type_id
962 FROM cn_period_types
963 WHERE period_type = x_period_type
964 AND org_id = x_org_id;
965 CURSOR c2 IS
966 SELECT period_set_id
967 FROM cn_period_sets
968 WHERE period_set_name = x_period_set_name
969 AND org_id = x_org_id;
970
971 l_period_set_id NUMBER;
972 l_period_type_id NUMBER;
973 l_cal_per_int_type_id NUMBER;
974 l_interval_number NUMBER;
975 l_period_quota_id number(15);
976
977 CURSOR repository IS
978 SELECT period_set_id, period_type_id
979 FROM cn_repositories
980 WHERE org_id = x_org_id;
981 CURSOR interval_types IS
982 SELECT interval_type_id
983 FROM cn_interval_types
984 WHERE org_id = x_org_id;
985 CURSOR interval_number (p_interval_type_id NUMBER) IS
986 SELECT interval_number
987 FROM cn_cal_per_int_types
988 WHERE interval_type_id = p_interval_type_id
989 AND org_id = x_org_id
990 ORDER BY Abs(cal_period_id - x_period_id);
991
992 BEGIN
993
994 -- get the period_type_id from cn_period_types,
995 -- if there is no matching record, create one
996 OPEN c1;
997 FETCH c1 INTO x_period_type_id;
998
999 IF (c1%NOTFOUND) THEN
1000 SELECT cn_period_types_s.NEXTVAL
1001 INTO x_period_type_id
1002 FROM dual;
1003 INSERT INTO cn_period_types
1004 (period_type_id,
1005 period_type,
1006 org_id)
1007 VALUES
1008 (x_period_type_id,
1009 x_period_type,
1010 x_org_id);
1011 END IF;
1012 CLOSE c1;
1013
1014 -- get the period_set_id from cn_period_sets.
1015 -- if there is no matching record, create one
1016 OPEN c2;
1017 FETCH c2 INTO x_period_set_id;
1018
1019 IF (c2%NOTFOUND) THEN
1020 SELECT cn_period_sets_s.NEXTVAL
1021 INTO x_period_set_id
1022 FROM dual;
1023 INSERT INTO cn_period_sets
1024 (period_set_id,
1025 period_set_name,
1026 org_id)
1027 VALUES
1028 (x_period_set_id,
1029 x_period_set_name,
1030 x_org_id);
1031 END IF;
1032 CLOSE c2;
1033
1034 x_period_id := 10000000000 * x_period_set_id +
1035 10000000 * x_period_type_id +
1036 1000 * x_period_year + x_period_num;
1037
1038 SELECT period_id
1039 INTO x_dummy
1040 FROM cn_period_statuses
1041 WHERE period_id = x_period_id
1042 AND org_id = x_org_id;
1043
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046
1047 -- the following code is obsolete and commented out
1048
1049 -- if x_period_type = 'Year' then
1050 -- x_period_type_id := 1 ;
1051 -- elsif x_period_type = 'Quarter' then
1052 -- x_period_type_id := 2 ;
1053 -- elsif x_period_type = 'Month' then
1054 -- x_period_type_id := 3 ;
1055 -- else x_period_type_id := 0 ; -- Bug 397015. Xinyang Fan
1056 -- end if;
1057
1058
1059 INSERT INTO cn_period_statuses
1060 (period_id,
1061 period_name,
1062 period_type_id,
1063 period_status,
1064 period_type,
1065 period_year,
1066 quarter_num,
1067 start_date,
1068 end_date,
1069 forecast_flag,
1070 period_set_name,
1071 period_set_id,
1072 freeze_flag,
1073 processing_status_code,
1074 org_id)
1075 VALUES (x_period_id,
1076 x_period_name,
1077 x_period_type_id,
1078 x_closing_status,
1079 x_period_type,
1080 x_period_year,
1081 x_quarter_num,
1082 x_start_date,
1083 x_end_date,
1084 'N',
1085 x_period_set_name,
1086 x_period_set_id,
1087 x_freeze_flag,
1088 Decode(x_closing_status,'O','PROCESSING','F','PROCESSING','CLEAN'), --1979768
1089 x_org_id);
1090
1091
1092 -- bug 1979768 : populate_srp_tables moved to concurrent program
1093
1094
1095 -- In addition, the newly activated period should be inserted into cn_calc_per_int_types
1096 -- for the relevant interval types.
1097 OPEN repository;
1098 FETCH repository INTO l_period_set_id, l_period_type_id;
1099 CLOSE repository;
1100
1101 IF (x_period_set_id = l_period_set_id AND x_period_type_id = l_period_type_id) THEN
1102 FOR interval_type IN interval_types LOOP
1103 IF (interval_type.interval_type_id <> -1003) THEN
1104 l_cal_per_int_type_id := NULL;
1105 l_interval_number := 1; --default value
1106
1107 OPEN interval_number(interval_type.interval_type_id);
1108 FETCH interval_number INTO l_interval_number;
1109 CLOSE interval_number;
1110
1111 cn_int_assign_pkg.insert_row
1112 (x_cal_per_int_type_id => l_cal_per_int_type_id,
1113 x_interval_type_id => interval_type.interval_type_id,
1114 x_cal_period_id => x_period_id,
1115 x_org_id => x_org_id,
1116 x_interval_number => l_interval_number,
1117 x_last_update_date => sysdate,
1118 x_last_updated_by => fnd_global.user_id,
1119 x_creation_date => sysdate,
1120 x_created_by => fnd_global.user_id,
1121 x_last_update_login => fnd_global.login_id
1122 );
1123 END IF;
1124 END LOOP;
1125 END IF;
1126
1127 -- Set repository status to F = Frozen. Per WFRIEND. 07-21-95.
1128 -- Removed. Per WFRIEND. 08-03-95.
1129 -- UPDATE cn_repositories
1130 -- SET status = 'F'
1131 -- WHERE repository_id = x_repository_id ;
1132
1133 END Check_CN_Period_Record;
1134
1135 --
1136 -- Procedure Name
1137 -- set_dates
1138 -- Purpose
1139 -- Set start and end dates from start and end periods.
1140 -- History
1141 -- 01-26-96 A. Erickson Created
1142 -- 03-13-96 A. Erickson Updated.
1143 --
1144
1145 PROCEDURE set_dates (x_start_period_id cn_period_statuses.period_id%TYPE,
1146 x_end_period_id cn_period_statuses.period_id%TYPE,
1147 x_org_id cn_period_statuses.org_id%TYPE,
1148 x_start_date OUT nocopy DATE,
1149 x_end_date OUT nocopy DATE) IS
1150
1151 CURSOR get_dates (p_period_id NUMBER) is
1152 SELECT cn.start_date, cn.end_date
1153 FROM CN_PERIOD_STATUSES CN, CN_REPOSITORIES_ALL RP
1154 WHERE rp.period_set_id = cn.period_set_id
1155 AND rp.period_type_id = cn.period_type_id
1156 AND cn.org_id = rp.org_id
1157 AND cn.org_id = x_org_id
1158 AND period_id = p_period_id;
1159
1160 date_rec get_dates%ROWTYPE;
1161
1162 CURSOR get_min_date IS
1163 SELECT MIN(cn.start_date)
1164 FROM CN_PERIOD_STATUSES CN, CN_REPOSITORIES_ALL RP
1165 WHERE rp.period_set_id = cn.period_set_id
1166 AND rp.period_type_id = cn.period_type_id
1167 AND cn.org_id = rp.org_id
1168 AND cn.org_id = x_org_id;
1169
1170 BEGIN
1171 IF (x_start_period_id IS NOT NULL) THEN
1172 OPEN get_dates(x_start_period_id);
1173 FETCH get_dates INTO date_rec;
1174 CLOSE get_dates;
1175 x_start_date := date_rec.start_date;
1176 ELSE
1177 OPEN get_min_date;
1178 FETCH get_min_date INTO x_start_date;
1179 CLOSE get_min_date;
1180 END IF;
1181
1182 IF (x_end_period_id IS NOT NULL) THEN
1183 OPEN get_dates(x_end_period_id);
1184 FETCH get_dates INTO date_rec;
1185 CLOSE get_dates;
1186 x_end_date := date_rec.end_date;
1187 ELSE
1188 x_end_date := SYSDATE;
1189 END IF;
1190 END set_dates;
1191
1192
1193 END cn_periods_api;