[Home] [Help]
PACKAGE BODY: APPS.CN_PERIODS_API
Source
1 PACKAGE BODY cn_periods_api AS
2 -- $Header: cnsyprb.pls 120.10.12020000.2 2012/07/19 07:57:41 swpoddar 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 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
603
604 -- bug 4135215 for telecom italia
605 CURSOR affected_srps IS
606 SELECT salesrep_id, period_id, org_id
607 FROM cn_process_batches
608 WHERE physical_batch_id = p_physical_batch_id
609 AND sales_lines_total = 0
610 ORDER BY period_id;
611
612 CURSOR srp_plan_info(l_salesrep_id IN NUMBER, l_start_date IN DATE, l_end_date IN DATE) IS
613 SELECT srp_plan_assign_id, role_id, comp_plan_id,
614 start_date, end_date, salesrep_id
615 FROM cn_srp_plan_assigns
616 WHERE salesrep_id = l_salesrep_id
617 AND role_id IS NOT null
618 --AND start_date <= l_start_date AND end_date >= l_end_date
619 AND (start_date <= l_start_date OR
620 trunc(start_date) between trunc(l_start_date) and trunc(l_end_date))
621 AND nvl(end_date,l_null_date) >= l_start_date;
622
623 CURSOR get_credit_types(l_comp_plan_id IN NUMBER) IS
624 select distinct q.credit_type_id
625 from cn_quota_assigns qa, cn_quotas q
626 where qa.comp_plan_id = l_comp_plan_id
627 and qa.quota_id = q.quota_id;
628
629 CURSOR get_pd_info(l_period_id IN NUMBER) IS
630 SELECT start_date, end_date, period_status
631 FROM cn_period_statuses
632 WHERE period_id = l_period_id;
633
634 CURSOR srp_payee_info(l_salesrep_id in number) IS
635 SELECT spay.quota_id, spay.start_date, spay.end_date, spa.comp_plan_id
636 from cn_srp_payee_assigns spay, cn_srp_plan_assigns spa,
637 cn_srp_quota_assigns sqa
638 where spay.payee_id = l_salesrep_id
639 and (spay.end_date IS NULL OR spay.end_date >= l_start_date)
640 AND spay.start_date <= l_end_date
641 and spay.srp_quota_assign_id = sqa.srp_quota_assign_id
642 and sqa.srp_plan_assign_id = spa.srp_plan_assign_id;
643
644 -- for API calls
645 l_return_status varchar2(1);
646 l_msg_count number;
647 l_msg_data VARCHAR2(2000);
648 l_loading_status varchar2(30);
649
650 l_proc_audit_id NUMBER := NULL;
651 dummy_num NUMBER ;
652
653 l_err_srp_id number:=0;
654 l_err_quota_id number:=0;
655 l_orig_pd_id NUMBER := -1;
656 l_user_id NUMBER := fnd_global.user_id;
657 l_login_id NUMBER := fnd_global.login_id;
658 l_curr_srp_id NUMBER;
659 l_curr_pd_id NUMBER;
660 l_count NUMBER;
661
662 BEGIN
663 --SAVEPOINT populate_srp_tables_runner; -- obsolete now since we commit for each rep
664 retcode := 0; -- success = 0, warning = 1, fail = 2
665
666 -- get current working org ID
667 l_org_id := mo_global.get_current_org_id;
668 IF l_org_id IS NULL THEN
669 -- org ID is not set... raise error
670 RAISE fnd_api.g_exc_unexpected_error;
671 END IF;
672
673 cn_message_pkg.begin_batch
674 (
675 x_parent_proc_audit_id => p_parent_proc_audit_id
676 ,x_process_audit_id => l_proc_audit_id
677 ,x_request_id => fnd_global.conc_request_id
678 ,x_process_type => 'OPEN_PERIODS_RUNNER'
679 ,p_org_id => l_org_id);
680
681 cn_message_pkg.debug('Populate SRP tables runner');
682
683 FOR srp IN affected_srps LOOP
684 IF srp.period_id <> l_orig_pd_id THEN
685 l_orig_pd_id := srp.period_id;
686 OPEN get_pd_info(srp.period_id);
687 FETCH get_pd_info INTO l_start_date, l_end_date, l_period_status;
688 CLOSE get_pd_info;
689 END IF;
690
691 l_curr_srp_id := srp.salesrep_id;
692 l_curr_pd_id := srp.period_id;
693
694 IF l_period_status IN ('O', 'F') THEN
695 -- Populate cn_srp_intel_periods
696 l_err_srp_id := srp.salesrep_id;
697
698 SELECT COUNT(1) INTO l_count
699 FROM cn_srp_intel_periods
700 WHERE period_id = srp.period_id
701 AND salesrep_id = srp.salesrep_id
702 AND org_id = srp.org_id;
703
704 IF l_count = 0 THEN
705 INSERT INTO cn_srp_intel_periods
706 (srp_intel_period_id,
707 salesrep_id,
708 org_id,
709 period_id,
710 processing_status_code,
711 process_all_flag,
712 creation_date,
713 created_by,
714 last_update_date,
715 last_updated_by,
716 last_update_login,
717 start_date,
718 end_date
719 ) VALUES
720 (cn_srp_intel_periods_s.NEXTVAL,
721 srp.salesrep_id,
722 l_org_id,
723 srp.period_id,
724 'CLEAN',
725 'Y',
726 Sysdate,
727 l_user_id,
728 Sysdate,
729 l_user_id,
730 l_login_id,
731 l_start_date,
732 l_end_date);
733 END IF; -- if rec not exist
734 END IF; -- if status = O
735
736 -- check to see if current rep is a payee
737 SELECT COUNT(1) INTO l_count
738 FROM cn_srp_roles
739 WHERE salesrep_id = srp.salesrep_id
740 AND role_id = 54
741 AND org_id = srp.org_id;
742
743 IF l_count > 0 THEN
744 -- rep is a payee
745 for p in srp_payee_info(srp.salesrep_id) LOOP
746 -- populate cn_srp_periods
747 CN_SRP_PERIODS_PVT.Create_Srp_Periods_per_Quota
748 (p_api_version => 1.0,
749 x_return_status => l_return_status,
750 x_msg_count => l_msg_count,
751 x_msg_data => l_msg_data,
752 p_role_id => 54,
753 p_quota_id => p.quota_id,
754 p_comp_plan_id => p.comp_plan_id,
755 p_salesrep_id => srp.salesrep_id,
756 p_start_date => p.start_date,
757 p_end_date => p.end_date,
758 x_loading_status => l_loading_status);
759
760 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
761 RAISE payee_pop_failed;
762 END IF;
763 end loop;
764 END IF;
765
766 for c in srp_plan_info(srp.salesrep_id, l_start_date, l_end_date) LOOP
767 -- populate cn_srp_periods
768 -- complicated logic so keep API call
769 CN_SRP_PERIODS_PVT.Create_Srp_Periods_Per_Quota
770 (p_api_version => 1.0,
771 x_return_status => l_return_status,
772 x_msg_count => l_msg_count,
773 x_msg_data => l_msg_data,
774 p_role_id => c.role_id,
775 p_comp_plan_id => c.comp_plan_id,
776 p_salesrep_id => srp.salesrep_id,
777 p_start_date => c.start_date,
778 p_end_date => c.end_date,
779 p_quota_id => NULL, -- do all quotas
780 p_sync_flag => FND_API.G_FALSE, -- don't sync right here
781 x_loading_status => l_loading_status);
782
783 fnd_file.put_line(fnd_file.Log, l_loading_status);
784 fnd_file.put_line(fnd_file.Log, l_msg_data);
785 fnd_file.put_line(fnd_file.Log, 'role ' || c.role_id);
786 fnd_file.put_line(fnd_file.Log, 'plan ' || c.comp_plan_id);
787 fnd_file.put_line(fnd_file.Log, 'dates ' || c.start_date || ', ' || c.end_date);
788
789
790 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
791 RAISE srp_pop_failed;
792 END IF;
793
794 -- sync accum bals for last period
795 -- complicated logic so keep API call
796 FOR ct IN get_credit_types(c.comp_plan_id) loop
797 cn_srp_periods_pvt.Sync_Accum_Balances_Start_Pd
798 (p_salesrep_id => c.salesrep_id,
799 p_org_id => l_org_id,
800 p_credit_type_id => ct.credit_type_id,
801 p_role_id => c.role_id,
802 p_start_period_id => srp.period_id);
803 END LOOP;
804
805 -- populate cn_srp_period_quotas
806 -- complicated logic so keep API call
807 cn_srp_period_quotas_pkg.insert_record
808 (
809 x_srp_plan_assign_id => c.srp_plan_assign_id
810 ,x_quota_id => NULL
811 ,x_start_period_id => srp.period_id
812 ,x_end_period_id => srp.period_id
813 ,x_start_date => l_start_date
814 ,x_end_date => l_end_date );
815
816 -- populate cn_srp_per_quota_rc_pkg
817 /* cn_srp_per_quota_rc_pkg.insert_record
818 (
819 x_srp_plan_assign_id => c.srp_plan_assign_id
820 ,x_quota_id => null
821 ,x_revenue_class_id => null
822 ,x_start_date => l_start_date
823 ,x_end_date => l_end_date );*/
824 -- denormalizing API call
825 INSERT INTO cn_srp_per_quota_rc
826 ( srp_per_quota_rc_id
827 ,srp_period_quota_id
828 ,srp_plan_assign_id
829 ,salesrep_id
830 ,org_id
831 ,period_id
832 ,quota_id
833 ,revenue_class_id
834 ,target_amount
835 ,year_to_date
836 ,period_to_date
837 ,quarter_to_date)
838 SELECT
839 cn_srp_per_quota_rc_s.nextval
840 ,pq.srp_period_quota_id
841 ,pq.srp_plan_assign_id
842 ,pq.salesrep_id
843 ,l_org_id
844 ,pq.period_id
845 ,pq.quota_id
846 ,qr.revenue_class_id
847 ,0 -- target amount
848 ,0 -- ytd
849 ,0 -- ptd
850 ,0 -- qtd
851 FROM cn_srp_period_quotas pq -- periods that rep/plan uses quota
852 ,cn_quota_rules qr
853 ,cn_quotas q
854 WHERE pq.srp_plan_assign_id = c.srp_plan_assign_id
855 AND pq.quota_id = qr.quota_id
856 AND qr.quota_id = q.quota_id
857 AND q.quota_type_code IN ('EXTERNAL','FORMULA')
858 AND l_period_status in ('O','F')
859 AND NOT EXISTS (SELECT 'srp_period_quota_rc already exists'
860 FROM cn_srp_per_quota_rc spqr
861 WHERE spqr.srp_period_quota_id = pq.srp_period_quota_id
862 AND spqr.srp_plan_assign_id = pq.srp_plan_assign_id
863 AND spqr.revenue_class_id = qr.revenue_class_id)
864 ;
865
866 END LOOP; -- srp_plan_info
867
868 -- mark that this rep, period IS done
869 UPDATE cn_process_batches
870 SET sales_lines_total = 1
871 WHERE physical_batch_id = p_physical_batch_id
872 AND salesrep_id = srp.salesrep_id
873 AND period_id = srp.period_id;
874
875 COMMIT; -- commit for each srp and period
876 END LOOP; -- main srp loop
877
878 cn_message_pkg.debug('Update cn_period_statuses.processing_status_code');
879 cn_message_pkg.debug('Populate SRP tables runner <<');
880 cn_message_pkg.end_batch(l_proc_audit_id);
881 EXCEPTION
882 WHEN payee_pop_failed THEN
883 --ROLLBACK TO populate_srp_tables;
884 retcode := 2;
885
886 -- change status to FAILED
887 UPDATE cn_period_statuses
888 SET processing_status_code = 'FAILED'
889 WHERE processing_status_code = 'PROCESSING'
890 AND period_id = l_curr_pd_id;
891 -- commit status change
892 COMMIT;
893
894 fnd_file.put_line(fnd_file.Log, 'Payee Population Failed');
895 fnd_file.put_line(fnd_file.Log, 'salesrep id'||l_err_srp_id);
896 fnd_file.put_line(fnd_file.Log, 'period id'||l_period_id);
897 fnd_file.put_line(fnd_file.Log, 'quota id'||l_err_quota_id);
898 WHEN srp_pop_failed THEN
899 --ROLLBACK TO populate_srp_tables_runner;
900 retcode := 2;
901
902 -- change status to FAILED
903 UPDATE cn_period_statuses
904 SET processing_status_code = 'FAILED'
905 WHERE processing_status_code = 'PROCESSING'
906 AND period_id = l_curr_pd_id;
907 -- commit status change
908 COMMIT;
909
910 fnd_file.put_line(fnd_file.Log, 'Srp Periods Population Failed');
911 fnd_file.put_line(fnd_file.Log, 'salesrep id'||l_curr_srp_id);
912 fnd_file.put_line(fnd_file.Log, 'period id'||l_curr_pd_id);
913 WHEN OTHERS THEN
914 --ROLLBACK TO populate_srp_tables_runner;
915 errbuf := substr(sqlerrm,1,250);
916 retcode := 2;
917
918 -- change status to FAILED
919 UPDATE cn_period_statuses
920 SET processing_status_code = 'FAILED'
921 WHERE processing_status_code = 'PROCESSING'
922 AND period_id = l_curr_pd_id;
923 -- commit status change
924 COMMIT;
925
926 fnd_file.put_line(fnd_file.Log, 'Unknown Failure');
927 fnd_file.put_line(fnd_file.Log, 'salesrep id'||l_curr_srp_id);
928 fnd_file.put_line(fnd_file.Log, 'period id'||l_curr_pd_id);
929 fnd_file.put_line(fnd_file.Log, 'errbuf'||errbuf);
930 END populate_srp_tables_runner;
931
932
933 /* ----------------------------------------------------------------------- */
934 --
935 -- Procedure Name
936 -- Check_CN_Period_Record
937 -- Purpose
938 -- Create a CN status record in CN_PERIOD_STATUSES if it doesn't exist.
939 --
940
941 PROCEDURE Check_CN_Period_Record (x_org_id NUMBER,
942 x_period_name VARCHAR2,
943 x_closing_status VARCHAR2,
944 x_period_type VARCHAR2,
945 x_period_year NUMBER,
946 x_quarter_num NUMBER,
947 x_period_num NUMBER,
948 x_period_set_name VARCHAR2,
949 x_start_date DATE,
950 x_end_date DATE,
951 x_freeze_flag VARCHAR2,
952 x_repository_id NUMBER) IS
953
954 x_period_id NUMBER(15);
955 x_period_type_id NUMBER(15);
956 x_period_set_id NUMBER(15);
957 x_dummy NUMBER(15);
958
959 l_return_status VARCHAR2(30);
960 l_loading_status VARCHAR2(30);
961 l_msg_count NUMBER;
962 l_msg_data VARCHAR2(2000);
963
964 CURSOR c1 IS
965 SELECT period_type_id
966 FROM cn_period_types
967 WHERE period_type = x_period_type
968 AND org_id = x_org_id;
969 CURSOR c2 IS
970 SELECT period_set_id
971 FROM cn_period_sets
972 WHERE period_set_name = x_period_set_name
973 AND org_id = x_org_id;
974
975 l_period_set_id NUMBER;
976 l_period_type_id NUMBER;
977 l_cal_per_int_type_id NUMBER;
978 l_interval_number NUMBER;
979 l_period_quota_id number(15);
980
981 CURSOR repository IS
982 SELECT period_set_id, period_type_id
983 FROM cn_repositories
984 WHERE org_id = x_org_id;
985 CURSOR interval_types IS
986 SELECT interval_type_id
987 FROM cn_interval_types
988 WHERE org_id = x_org_id;
989 CURSOR interval_number (p_interval_type_id NUMBER) IS
990 SELECT interval_number
991 FROM cn_cal_per_int_types
992 WHERE interval_type_id = p_interval_type_id
993 AND org_id = x_org_id
994 ORDER BY Abs(cal_period_id - x_period_id);
995
996 BEGIN
997
998 -- get the period_type_id from cn_period_types,
999 -- if there is no matching record, create one
1000 OPEN c1;
1001 FETCH c1 INTO x_period_type_id;
1002
1003 IF (c1%NOTFOUND) THEN
1004 SELECT cn_period_types_s.NEXTVAL
1005 INTO x_period_type_id
1006 FROM dual;
1007 INSERT INTO cn_period_types
1008 (period_type_id,
1009 period_type,
1010 org_id)
1011 VALUES
1012 (x_period_type_id,
1013 x_period_type,
1014 x_org_id);
1015 END IF;
1016 CLOSE c1;
1017
1018 -- get the period_set_id from cn_period_sets.
1019 -- if there is no matching record, create one
1020 OPEN c2;
1021 FETCH c2 INTO x_period_set_id;
1022
1023 IF (c2%NOTFOUND) THEN
1024 SELECT cn_period_sets_s.NEXTVAL
1025 INTO x_period_set_id
1026 FROM dual;
1027 INSERT INTO cn_period_sets
1028 (period_set_id,
1029 period_set_name,
1030 org_id)
1031 VALUES
1032 (x_period_set_id,
1033 x_period_set_name,
1034 x_org_id);
1035 END IF;
1036 CLOSE c2;
1037
1038 x_period_id := 10000000000 * x_period_set_id +
1039 10000000 * x_period_type_id +
1040 1000 * x_period_year + x_period_num;
1041
1042 SELECT period_id
1043 INTO x_dummy
1044 FROM cn_period_statuses
1045 WHERE period_id = x_period_id
1046 AND org_id = x_org_id;
1047
1048 EXCEPTION
1049 WHEN NO_DATA_FOUND THEN
1050
1051 -- the following code is obsolete and commented out
1052
1053 -- if x_period_type = 'Year' then
1054 -- x_period_type_id := 1 ;
1055 -- elsif x_period_type = 'Quarter' then
1056 -- x_period_type_id := 2 ;
1057 -- elsif x_period_type = 'Month' then
1058 -- x_period_type_id := 3 ;
1059 -- else x_period_type_id := 0 ; -- Bug 397015. Xinyang Fan
1060 -- end if;
1061
1062
1063 INSERT INTO cn_period_statuses
1064 (period_id,
1065 period_name,
1066 period_type_id,
1067 period_status,
1068 period_type,
1069 period_year,
1070 quarter_num,
1071 start_date,
1072 end_date,
1073 forecast_flag,
1074 period_set_name,
1075 period_set_id,
1076 freeze_flag,
1077 processing_status_code,
1078 org_id)
1079 VALUES (x_period_id,
1080 x_period_name,
1081 x_period_type_id,
1082 x_closing_status,
1083 x_period_type,
1084 x_period_year,
1085 x_quarter_num,
1086 x_start_date,
1087 x_end_date,
1088 'N',
1089 x_period_set_name,
1090 x_period_set_id,
1091 x_freeze_flag,
1092 Decode(x_closing_status,'O','PROCESSING','F','PROCESSING','CLEAN'), --1979768
1093 x_org_id);
1094
1095
1096 -- bug 1979768 : populate_srp_tables moved to concurrent program
1097
1098
1099 -- In addition, the newly activated period should be inserted into cn_calc_per_int_types
1100 -- for the relevant interval types.
1101 OPEN repository;
1102 FETCH repository INTO l_period_set_id, l_period_type_id;
1103 CLOSE repository;
1104
1105 IF (x_period_set_id = l_period_set_id AND x_period_type_id = l_period_type_id) THEN
1106 FOR interval_type IN interval_types LOOP
1107 IF (interval_type.interval_type_id <> -1003) THEN
1108 l_cal_per_int_type_id := NULL;
1109 l_interval_number := 1; --default value
1110
1111 OPEN interval_number(interval_type.interval_type_id);
1112 FETCH interval_number INTO l_interval_number;
1113 CLOSE interval_number;
1114
1115 cn_int_assign_pkg.insert_row
1116 (x_cal_per_int_type_id => l_cal_per_int_type_id,
1117 x_interval_type_id => interval_type.interval_type_id,
1118 x_cal_period_id => x_period_id,
1119 x_org_id => x_org_id,
1120 x_interval_number => l_interval_number,
1121 x_last_update_date => sysdate,
1122 x_last_updated_by => fnd_global.user_id,
1123 x_creation_date => sysdate,
1124 x_created_by => fnd_global.user_id,
1125 x_last_update_login => fnd_global.login_id
1126 );
1127 END IF;
1128 END LOOP;
1129 END IF;
1130
1131 -- Set repository status to F = Frozen. Per WFRIEND. 07-21-95.
1132 -- Removed. Per WFRIEND. 08-03-95.
1133 -- UPDATE cn_repositories
1134 -- SET status = 'F'
1135 -- WHERE repository_id = x_repository_id ;
1136
1137 END Check_CN_Period_Record;
1138
1139 --
1140 -- Procedure Name
1141 -- set_dates
1142 -- Purpose
1143 -- Set start and end dates from start and end periods.
1144 -- History
1145 -- 01-26-96 A. Erickson Created
1146 -- 03-13-96 A. Erickson Updated.
1147 --
1148
1149 PROCEDURE set_dates (x_start_period_id cn_period_statuses.period_id%TYPE,
1150 x_end_period_id cn_period_statuses.period_id%TYPE,
1151 x_org_id cn_period_statuses.org_id%TYPE,
1152 x_start_date OUT nocopy DATE,
1153 x_end_date OUT nocopy DATE) IS
1154
1155 CURSOR get_dates (p_period_id NUMBER) is
1156 SELECT cn.start_date, cn.end_date
1157 FROM CN_PERIOD_STATUSES CN, CN_REPOSITORIES_ALL RP
1158 WHERE rp.period_set_id = cn.period_set_id
1159 AND rp.period_type_id = cn.period_type_id
1160 AND cn.org_id = rp.org_id
1161 AND cn.org_id = x_org_id
1162 AND period_id = p_period_id;
1163
1164 date_rec get_dates%ROWTYPE;
1165
1166 CURSOR get_min_date IS
1167 SELECT MIN(cn.start_date)
1168 FROM CN_PERIOD_STATUSES CN, CN_REPOSITORIES_ALL RP
1169 WHERE rp.period_set_id = cn.period_set_id
1170 AND rp.period_type_id = cn.period_type_id
1171 AND cn.org_id = rp.org_id
1172 AND cn.org_id = x_org_id;
1173
1174 BEGIN
1175 IF (x_start_period_id IS NOT NULL) THEN
1176 OPEN get_dates(x_start_period_id);
1177 FETCH get_dates INTO date_rec;
1178 CLOSE get_dates;
1179 x_start_date := date_rec.start_date;
1180 ELSE
1181 OPEN get_min_date;
1182 FETCH get_min_date INTO x_start_date;
1183 CLOSE get_min_date;
1184 END IF;
1185
1186 IF (x_end_period_id IS NOT NULL) THEN
1187 OPEN get_dates(x_end_period_id);
1188 FETCH get_dates INTO date_rec;
1189 CLOSE get_dates;
1190 x_end_date := date_rec.end_date;
1191 ELSE
1192 x_end_date := SYSDATE;
1193 END IF;
1194 END set_dates;
1195
1196
1197 END cn_periods_api;