[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_SUB_BATCHES_PKG
Source
1 PACKAGE BODY CN_CALC_SUB_BATCHES_PKG AS
2 /* $Header: cnsbbatb.pls 120.2 2006/02/17 11:55:22 ymao noship $ */
3
4 G_LAST_UPDATE_DATE DATE := sysdate;
5 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
6 G_CREATION_DATE DATE := sysdate;
7 G_CREATED_BY NUMBER := fnd_global.user_id;
8 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
9 --
10 --
11 --
12 -- This Procedure is called to
13 -- 1. Insert
14 -- 2. Update
15 -- 3. Delete
16 -- Records into Table cn_calc_submission_batches
17 --
18 --
19
20
21 Procedure Insert_row ( p_calc_sub_batch_id NUMBER,
22 p_name VARCHAR2,
23 p_start_date DATE,
24 p_end_date DATE,
25 p_intelligent_flag VARCHAR2,
26 p_hierarchy_flag VARCHAR2,
27 p_salesrep_option VARCHAR2,
28 p_concurrent_flag VARCHAR2,
29 p_log_name VARCHAR2,
30 p_status VARCHAR2,
31 p_logical_batch_id NUMBER,
32 p_calc_type VARCHAR2,
33 p_interval_type_id NUMBER,
34 p_org_id NUMBER,
35 P_ATTRIBUTE_CATEGORY VARCHAR2,
36 P_ATTRIBUTE1 VARCHAR2,
37 P_ATTRIBUTE2 VARCHAR2,
38 P_ATTRIBUTE3 VARCHAR2,
39 P_ATTRIBUTE4 VARCHAR2,
40 P_ATTRIBUTE5 VARCHAR2,
41 P_ATTRIBUTE6 VARCHAR2,
42 P_ATTRIBUTE7 VARCHAR2,
43 P_ATTRIBUTE8 VARCHAR2,
44 P_ATTRIBUTE9 VARCHAR2,
45 P_ATTRIBUTE10 VARCHAR2,
46 P_ATTRIBUTE11 VARCHAR2,
47 P_ATTRIBUTE12 VARCHAR2,
48 P_ATTRIBUTE13 VARCHAR2,
49 P_ATTRIBUTE14 VARCHAR2,
50 P_ATTRIBUTE15 VARCHAR2,
51 P_CREATED_BY NUMBER ,
52 P_CREATION_DATE DATE ,
53 P_LAST_UPDATE_LOGIN NUMBER ,
54 P_LAST_UPDATE_DATE DATE ,
55 P_LAST_UPDATED_BY NUMBER
56 ) IS
57 l_calc_sub_batch_id NUMBER(15);
58 BEGIN
59
60 IF p_calc_sub_batch_id IS NOT NULL THEN
61 l_calc_sub_batch_id := p_calc_sub_batch_id;
62 ELSE
63 SELECT cn_calc_submission_batches_s1.NEXTVAL
64 INTO l_calc_sub_batch_id
65 FROM dual;
66 END IF;
67
68 INSERT INTO cn_calc_submission_batches_all
69 ( calc_sub_batch_id,
70 name,
71 start_date,
72 end_date,
73 intelligent_flag,
74 hierarchy_flag,
75 salesrep_option,
76 concurrent_flag,
77 log_name,
78 status,
79 logical_batch_id,
80 calc_type,
81 interval_type_id,
82 org_id
83 ,attribute_category
84 ,attribute1
85 ,attribute2
86 ,attribute3
87 ,attribute4
88 ,attribute5
89 ,attribute6
90 ,attribute7
91 ,attribute8
92 ,attribute9
93 ,attribute10
94 ,attribute11
95 ,attribute12
96 ,attribute13
97 ,attribute14
98 ,attribute15
99 ,created_by
100 ,creation_date
101 ,last_update_login
102 ,last_update_date
103 ,last_updated_by
104 )
105 VALUES ( l_calc_sub_batch_id,
106 p_name,
107 p_start_date,
108 p_end_date,
109 p_intelligent_flag,
110 p_hierarchy_flag,
111 p_salesrep_option,
112 p_concurrent_flag,
113 p_log_name,
114 p_status,
115 p_logical_batch_id,
116 p_calc_type,
117 p_interval_type_id,
118 p_org_id
119 ,p_attribute_category
120 ,p_attribute1
121 ,p_attribute2
122 ,p_attribute3
123 ,p_attribute4
124 ,p_attribute5
125 ,p_attribute6
126 ,p_attribute7
127 ,p_attribute8
128 ,p_attribute9
129 ,p_attribute10
130 ,p_attribute11
131 ,p_attribute12
132 ,p_attribute13
133 ,p_attribute14
134 ,p_attribute15
135 ,Nvl( p_created_by,g_created_by)
136 ,Nvl( p_creation_date,g_creation_date )
137 ,Nvl( p_last_update_login, g_last_update_login )
138 ,Nvl( p_last_update_date, g_last_update_date )
139 ,Nvl( p_last_updated_by, g_last_updated_by )
140 ) ;
141
142
143 END insert_row;
144
145
146 Procedure Update_row ( p_calc_sub_batch_id NUMBER,
147 p_name VARCHAR2,
148 p_start_date DATE,
149 p_end_date DATE,
150 p_intelligent_flag VARCHAR2,
151 p_hierarchy_flag VARCHAR2,
152 p_salesrep_option VARCHAR2,
153 p_concurrent_flag VARCHAR2,
154 p_log_name VARCHAR2,
155 p_status VARCHAR2,
156 p_logical_batch_id NUMBER,
157 p_calc_type VARCHAR2,
158 p_interval_type_id NUMBER,
159 P_ATTRIBUTE_CATEGORY VARCHAR2,
160 P_ATTRIBUTE1 VARCHAR2,
161 P_ATTRIBUTE2 VARCHAR2,
162 P_ATTRIBUTE3 VARCHAR2,
163 P_ATTRIBUTE4 VARCHAR2,
164 P_ATTRIBUTE5 VARCHAR2,
165 P_ATTRIBUTE6 VARCHAR2,
166 P_ATTRIBUTE7 VARCHAR2,
167 P_ATTRIBUTE8 VARCHAR2,
168 P_ATTRIBUTE9 VARCHAR2,
169 P_ATTRIBUTE10 VARCHAR2,
170 P_ATTRIBUTE11 VARCHAR2,
171 P_ATTRIBUTE12 VARCHAR2,
172 P_ATTRIBUTE13 VARCHAR2,
173 P_ATTRIBUTE14 VARCHAR2,
174 P_ATTRIBUTE15 VARCHAR2,
175 P_CREATED_BY NUMBER ,
176 P_CREATION_DATE DATE ,
177 P_LAST_UPDATE_LOGIN NUMBER ,
178 P_LAST_UPDATE_DATE DATE ,
179 P_LAST_UPDATED_BY NUMBER
180 ) IS
181
182 BEGIN
183
184 UPDATE cn_calc_submission_batches_all SET
185 calc_sub_batch_id = p_calc_sub_batch_id,
186 name = p_name,
187 start_date = p_start_date,
188 end_date = p_end_date,
189 intelligent_flag = p_intelligent_flag,
190 hierarchy_flag = p_hierarchy_flag,
191 salesrep_option = p_salesrep_option ,
192 concurrent_flag = p_concurrent_flag ,
193 log_name = p_log_name,
194 status = p_status ,
195 logical_batch_id = p_logical_batch_id,
196 calc_type = p_calc_type,
197 interval_type_id = p_interval_type_id
198 ,attribute_category = p_attribute_category
199 ,attribute1 = p_attribute1
200 ,attribute2 = p_attribute2
201 ,attribute3 = p_attribute3
202 ,attribute4 = p_attribute4
203 ,attribute5 = p_attribute5
204 ,attribute6 = p_attribute6
205 ,attribute7 = p_attribute7
206 ,attribute8 = p_attribute8
207 ,attribute9 = p_attribute9
208 ,attribute10 = p_attribute10
209 ,attribute11 = p_attribute11
210 ,attribute12 = p_attribute12
211 ,attribute13 = p_attribute13
212 ,attribute14 = p_attribute14
213 ,attribute15 = p_attribute15
214 ,created_by = Nvl( p_created_by,g_created_by)
215 ,creation_date = Nvl( p_creation_date,g_creation_date )
216 ,last_update_login = Nvl( p_last_update_login, g_last_update_login )
217 ,last_update_date = Nvl( p_last_update_date, g_last_update_date )
218 ,last_updated_by = Nvl( p_last_updated_by, g_last_updated_by )
219
220 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
221
222 END update_row;
223
224 Procedure delete_row ( p_calc_sub_batch_id NUMBER ) IS
225
226 BEGIN
227 DELETE cn_calc_submission_batches_all
228 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
229
230 IF (sql%notfound) THEN
231 raise no_data_found;
232 END IF;
233
234 DELETE cn_calc_submission_entries_all
235 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
236
237 DELETE cn_calc_sub_quotas_all
238 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
239
240 END delete_row;
241
242 Procedure lock_row ( p_calc_sub_batch_id NUMBER,
243 p_name VARCHAR2,
244 p_start_date DATE,
245 p_end_date DATE,
246 p_intelligent_flag VARCHAR2,
247 p_hierarchy_flag VARCHAR2,
248 p_salesrep_option VARCHAR2,
249 p_concurrent_flag VARCHAR2,
250 p_log_name VARCHAR2,
251 p_status VARCHAR2,
252 p_logical_batch_id NUMBER,
253 p_calc_type VARCHAR2,
254 p_interval_type_id NUMBER,
255 P_ATTRIBUTE_CATEGORY VARCHAR2,
256 P_ATTRIBUTE1 VARCHAR2,
257 P_ATTRIBUTE2 VARCHAR2,
258 P_ATTRIBUTE3 VARCHAR2,
259 P_ATTRIBUTE4 VARCHAR2,
260 P_ATTRIBUTE5 VARCHAR2,
261 P_ATTRIBUTE6 VARCHAR2,
262 P_ATTRIBUTE7 VARCHAR2,
263 P_ATTRIBUTE8 VARCHAR2,
264 P_ATTRIBUTE9 VARCHAR2,
265 P_ATTRIBUTE10 VARCHAR2,
266 P_ATTRIBUTE11 VARCHAR2,
267 P_ATTRIBUTE12 VARCHAR2,
268 P_ATTRIBUTE13 VARCHAR2,
269 P_ATTRIBUTE14 VARCHAR2,
270 P_ATTRIBUTE15 VARCHAR2,
271 P_CREATED_BY NUMBER ,
272 P_CREATION_DATE DATE ,
273 P_LAST_UPDATE_LOGIN NUMBER ,
274 P_LAST_UPDATE_DATE DATE ,
275 P_LAST_UPDATED_BY NUMBER
276 ) IS
277 CURSOR C IS
278 SELECT * FROM cn_calc_submission_batches_all
279 WHERE calc_sub_batch_id = p_calc_sub_batch_id
280 FOR UPDATE OF calc_sub_batch_id NOWAIT;
281
282 Recinfo C%ROWTYPE;
283
284 BEGIN
285 OPEN C;
286 FETCH C INTO Recinfo;
287
288 IF C%NOTFOUND THEN
289 CLOSE C;
290 fnd_message.set_name('FND','FORM_RECORD_DELETED');
291 app_exception.raise_exception;
292 END IF;
293 CLOSE C;
294
295 IF ( recinfo.calc_sub_batch_id = p_calc_sub_batch_id
296 OR ( recinfo.calc_sub_batch_id IS NULL AND p_calc_sub_batch_id IS NULL) )
297 AND ( recinfo.name = p_name
298 OR ( recinfo.name IS NULL AND p_name IS NULL) )
299 AND ( recinfo.start_date = p_start_date
300 OR ( recinfo.start_date IS NULL AND
301 p_start_date IS NULL) )
302 AND ( recinfo.end_date = p_end_date
303 OR ( recinfo.end_date IS NULL AND
304 p_end_date IS NULL) )
305 AND ( recinfo.intelligent_flag = p_intelligent_flag
306 OR ( recinfo.intelligent_flag IS NULL AND
307 p_intelligent_flag IS NULL) )
308 AND ( recinfo.hierarchy_flag = p_hierarchy_flag
309 OR ( recinfo.hierarchy_flag IS NULL AND
310 p_hierarchy_flag IS NULL) )
311 AND ( recinfo.salesrep_option = p_salesrep_option
312 OR ( recinfo.salesrep_option IS NULL AND
313 p_salesrep_option IS NULL) )
314 AND ( recinfo.concurrent_flag = p_concurrent_flag
315 OR ( recinfo.concurrent_flag IS NULL AND
316 p_concurrent_flag IS NULL) )
317 AND ( recinfo.log_name = p_log_name
318 OR ( recinfo.log_name IS NULL AND
319 p_log_name IS NULL) )
320 AND ( recinfo.status = p_status
321 OR ( recinfo.status IS NULL AND
322 p_status IS NULL) )
323 AND ( recinfo.logical_batch_id = p_logical_batch_id
324 OR ( recinfo.logical_batch_id IS NULL AND
325 p_logical_batch_id IS NULL) )
326 AND ( recinfo.calc_type = p_calc_type
327 OR ( recinfo.calc_type IS NULL AND p_calc_type IS NULL) )
328 AND ( recinfo.interval_type_id = p_interval_type_id
329 OR ( recinfo.interval_type_id IS NULL AND
330 p_interval_type_id IS NULL) )
331 AND ( recinfo.attribute_category = p_attribute_category
332 OR ( recinfo.attribute_category IS NULL AND
333 p_attribute_category IS NULL) )
334 AND ( recinfo.attribute1 = p_attribute1
335 OR ( recinfo.attribute1 IS NULL AND p_attribute1 IS NULL) )
336 AND ( recinfo.attribute2 = p_attribute2
337 OR ( recinfo.attribute2 IS NULL AND p_attribute2 IS NULL) )
338 AND ( recinfo.attribute3 = p_attribute3
339 OR ( recinfo.attribute3 IS NULL AND p_attribute3 IS NULL) )
340
341 AND ( recinfo.attribute4 = p_attribute4
342 OR ( recinfo.attribute4 IS NULL AND p_attribute4 IS NULL) )
343
344 AND ( recinfo.attribute5 = p_attribute5
345 OR ( recinfo.attribute5 IS NULL AND p_attribute5 IS NULL) )
346
347 AND ( recinfo.attribute6 = p_attribute6
348 OR ( recinfo.attribute6 IS NULL AND p_attribute6 IS NULL) )
349
350 AND ( recinfo.attribute7 = p_attribute7
351 OR ( recinfo.attribute7 IS NULL AND p_attribute7 IS NULL) )
352
353 AND ( recinfo.attribute8 = p_attribute8
354 OR ( recinfo.attribute8 IS NULL AND p_attribute8 IS NULL) )
355
356 AND ( recinfo.attribute9 = p_attribute9
357 OR ( recinfo.attribute9 IS NULL AND p_attribute9 IS NULL) )
358
359 AND ( recinfo.attribute10 = p_attribute10
360 OR ( recinfo.attribute10 IS NULL AND p_attribute10 IS NULL) )
361
362 AND ( recinfo.attribute11 = p_attribute11
363 OR ( recinfo.attribute11 IS NULL AND p_attribute11 IS NULL) )
364
365 AND ( recinfo.attribute12 = p_attribute12
366 OR ( recinfo.attribute12 IS NULL AND p_attribute12 IS NULL) )
367
368 AND ( recinfo.attribute13 = p_attribute13
369 OR ( recinfo.attribute13 IS NULL AND p_attribute13 IS NULL) )
370
371 AND ( recinfo.attribute14 = p_attribute14
372 OR ( recinfo.attribute14 IS NULL AND p_attribute14 IS NULL) )
373
374 AND ( recinfo.attribute15 = p_attribute15
375 OR ( recinfo.attribute15 IS NULL AND p_attribute15 IS NULL) )
376
377 THEN
378 RETURN;
379 ELSE
380 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
381 app_exception.raise_exception;
382 END IF;
383
384 END lock_row;
385
386 --
387 Procedure Begin_Record ( P_OPERATION VARCHAR2,
388 p_calc_sub_batch_id NUMBER := NULL,
389 p_name VARCHAR2 := NULL,
390 p_start_date DATE := NULL,
391 p_end_date DATE := NULL,
392 p_intelligent_flag VARCHAR2 := NULL,
393 p_hierarchy_flag VARCHAR2 := NULL,
394 p_salesrep_option VARCHAR2 := NULL,
395 p_concurrent_flag VARCHAR2 := NULL,
396 p_status VARCHAR2 := NULL,
397 p_logical_batch_id NUMBER := NULL,
398 p_calc_type VARCHAR2 := NULL,
399 p_interval_type_id NUMBER := NULL,
400 p_org_id NUMBER,
401 p_log_name VARCHAR2 := NULL,
402 P_ATTRIBUTE_CATEGORY VARCHAR2 := NULL,
403 P_ATTRIBUTE1 VARCHAR2 := NULL,
404 P_ATTRIBUTE2 VARCHAR2 := NULL,
405 P_ATTRIBUTE3 VARCHAR2 := NULL,
406 P_ATTRIBUTE4 VARCHAR2 := NULL,
407 P_ATTRIBUTE5 VARCHAR2 := NULL,
408 P_ATTRIBUTE6 VARCHAR2 := NULL,
409 P_ATTRIBUTE7 VARCHAR2 := NULL,
410 P_ATTRIBUTE8 VARCHAR2 := NULL,
411 P_ATTRIBUTE9 VARCHAR2 := NULL,
412 P_ATTRIBUTE10 VARCHAR2 := NULL,
413 P_ATTRIBUTE11 VARCHAR2 := NULL,
414 P_ATTRIBUTE12 VARCHAR2 := NULL,
415 P_ATTRIBUTE13 VARCHAR2 := NULL,
416 P_ATTRIBUTE14 VARCHAR2 := NULL,
417 P_ATTRIBUTE15 VARCHAR2 := NULL,
418 P_CREATED_BY NUMBER := NULL,
419 P_CREATION_DATE DATE := NULL,
420 P_LAST_UPDATE_LOGIN NUMBER := NULL,
421 P_LAST_UPDATE_DATE DATE := NULL,
422 P_LAST_UPDATED_BY NUMBER := NULL
423 ) IS
424 BEGIN
425 IF p_operation = 'INSERT' THEN
426 insert_row ( p_calc_sub_batch_id,
427 p_name,
428 p_start_date,
429 p_end_date,
430 p_intelligent_flag,
431 p_hierarchy_flag,
432 p_salesrep_option,
433 p_concurrent_flag,
434 p_log_name,
435 p_status,
436 p_logical_batch_id,
437 p_calc_type,
438 p_interval_type_id,
439 p_org_id
440 ,p_attribute_category
441 ,p_attribute1
442 ,p_attribute2
443 ,p_attribute3
444 ,p_attribute4
445 ,p_attribute5
446 ,p_attribute6
447 ,p_attribute7
448 ,p_attribute8
449 ,p_attribute9
450 ,p_attribute10
451 ,p_attribute11
452 ,p_attribute12
453 ,p_attribute13
454 ,p_attribute14
455 ,p_attribute15
456 ,p_created_by
457 ,p_creation_date
458 ,p_last_update_login
459 ,p_last_update_date
460 ,p_last_updated_by ) ;
461 ELSIF p_operation = 'UPDATE' THEN
462 update_row ( p_calc_sub_batch_id,
463 p_name,
464 p_start_date,
465 p_end_date,
466 p_intelligent_flag,
467 p_hierarchy_flag,
468 p_salesrep_option,
469 p_concurrent_flag,
470 p_log_name,
471 p_status,
472 p_logical_batch_id,
473 p_calc_type,
474 p_interval_type_id
475 ,p_attribute_category
476 ,p_attribute1
477 ,p_attribute2
478 ,p_attribute3
479 ,p_attribute4
480 ,p_attribute5
481 ,p_attribute6
482 ,p_attribute7
483 ,p_attribute8
484 ,p_attribute9
485 ,p_attribute10
486 ,p_attribute11
487 ,p_attribute12
488 ,p_attribute13
489 ,p_attribute14
490 ,p_attribute15
491 ,p_created_by
492 ,p_creation_date
493 ,p_last_update_login
494 ,p_last_update_date
495 ,p_last_updated_by ) ;
496 ELSIF p_operation = 'DELETE' THEN
497 delete_row ( p_calc_sub_batch_id );
498 ELSIF p_operation = 'LOCK' THEN
499 lock_row ( p_calc_sub_batch_id,
500 p_name,
501 p_start_date,
502 p_end_date,
503 p_intelligent_flag,
504 p_hierarchy_flag,
505 p_salesrep_option,
506 p_concurrent_flag,
507 p_log_name,
508 p_status,
509 p_logical_batch_id,
510 p_calc_type,
511 p_interval_type_id
512 ,p_attribute_category
513 ,p_attribute1
514 ,p_attribute2
515 ,p_attribute3
516 ,p_attribute4
517 ,p_attribute5
518 ,p_attribute6
519 ,p_attribute7
520 ,p_attribute8
521 ,p_attribute9
522 ,p_attribute10
523 ,p_attribute11
524 ,p_attribute12
525 ,p_attribute13
526 ,p_attribute14
527 ,p_attribute15
528 ,p_created_by
529 ,p_creation_date
530 ,p_last_update_login
531 ,p_last_update_date
532 ,p_last_updated_by );
533 END IF;
534
535 END begin_record;
536
537 --+
538 -- Procedure Name
539 -- get_calc_sub_batch
540 -- Scope
541 -- public
542 -- Purpose
543 -- get the calc_submission for this physical batch
544 -- state.
545 -- History
546 -- 10-JUL-98 Richard Jin Created
547 --+
548 PROCEDURE get_calc_sub_batch ( p_physical_batch_id NUMBER,
549 x_calc_sub_batch_rec OUT NOCOPY calc_sub_batch_rec_type ) IS
550
551 BEGIN
552 SELECT calc_sub_batch_id,
553 name,
554 intelligent_flag,
555 hierarchy_flag,
556 salesrep_option,
557 logical_batch_id,
558 start_date,
559 end_date,
560 calc_type,
561 interval_type_id
562 INTO
563 x_calc_sub_batch_rec.calc_sub_batch_id,
564 x_calc_sub_batch_rec.name,
565 x_calc_sub_batch_rec.intelligent_flag,
566 x_calc_sub_batch_rec.hierarchy_flag,
567 x_calc_sub_batch_rec.salesrep_option,
568 x_calc_sub_batch_rec.logical_batch_id,
569 x_calc_sub_batch_rec.start_date,
570 x_calc_sub_batch_rec.end_date,
571 x_calc_sub_batch_rec.calc_type,
572 x_calc_sub_batch_rec.interval_type_id
573 FROM cn_calc_submission_batches_all csb
574 WHERE csb.logical_batch_id = (SELECT pb.logical_batch_id
575 FROM cn_process_batches_all pb
576 WHERE pb.physical_batch_id = p_physical_batch_id
577 AND rownum = 1);
578
579 END get_calc_sub_batch;
580 --+
581 -- Procedure Name
582 -- get_intel_calc_flag
583 -- Scope
584 -- Local to cn_calc_sub_batches_pkg
585 -- Purpose
586 -- get the intelligent_flag for this physical batch
587 -- state.
588 -- History
589 -- 10-JUL-98 Richard Jin Created
590 --+
591 FUNCTION get_intel_calc_flag (p_calc_batch_id NUMBER) RETURN VARCHAR2 IS
592 x_return VARCHAR2(1);
593 BEGIN
594 select intelligent_flag
595 into x_return
596 from cn_calc_submission_batches_all csb
597 where csb.logical_batch_id = (select logical_batch_id
598 from cn_process_batches_all pb
599 where pb.physical_batch_id = p_calc_batch_id
600 and rownum = 1);
601
602 RETURN x_return;
603
604 END get_intel_calc_flag;
605
606 --+
607 -- Procedure Name
608 -- get_forecast_flag
609 -- Scope
610 -- Local to cn_calc_sub_batches_pkg
611 -- Purpose
612 -- get the intelligent_flag for this physical batch
613 -- state.
614 -- History
615 -- 10-JUL-98 Richard Jin Created
616 --+
617
618 FUNCTION get_forecast_flag (p_calc_batch_id NUMBER) RETURN VARCHAR2 IS
619 x_return VARCHAR2(1);
620 l_calc_type VARCHAR2(30);
621 BEGIN
622 select calc_type
623 into l_calc_type
624 from cn_calc_submission_batches_all csb
625 where csb.logical_batch_id = (select logical_batch_id
626 from cn_process_batches_all pb
627 where pb.physical_batch_id = p_calc_batch_id
628 and rownum = 1);
629
630 IF l_calc_type = 'FORECAST' THEN
631 x_return := 'Y';
632 ELSE
633 x_return := 'N';
634 END IF;
635
636 RETURN x_return;
637
638 END get_forecast_flag;
639
640 --+
641 -- Procedure Name
642 -- get_calc_type
643 -- Scope
644 -- Local to cn_calc_sub_batches_pkg
645 -- Purpose
646 -- get the calculation type for this physical batch
647 -- state.
648 -- History
649 -- 10-JUL-98 Richard Jin Created
650 --+
651
652 FUNCTION get_calc_type (p_calc_batch_id NUMBER) RETURN VARCHAR2 IS
653 x_return VARCHAR2(30);
654 BEGIN
655 select calc_type
656 into x_return
657 from cn_calc_submission_batches_all csb
658 where csb.logical_batch_id = (select logical_batch_id
659 from cn_process_batches_all pb
660 where pb.physical_batch_id = p_calc_batch_id
661 and rownum = 1);
662
663 RETURN x_return;
664
665 END get_calc_type;
666
667 --+
668 -- Procedure Name
669 -- get_salesrep_option
670 -- Scope
671 -- Local to cn_calc_sub_batches_pkg
672 -- Purpose
673 -- get the calculation type for this physical batch
674 -- state.
675 -- History
676 -- 10-JUL-98 Richard Jin Created
677 --+
678
679 FUNCTION get_salesrep_option (p_calc_batch_id NUMBER) RETURN VARCHAR2 IS
680 x_return VARCHAR2(30);
681 BEGIN
682 select salesrep_option
683 into x_return
684 from cn_calc_submission_batches_all csb
685 where csb.logical_batch_id = (select logical_batch_id
686 from cn_process_batches_all pb
687 where pb.physical_batch_id = p_calc_batch_id
688 and rownum = 1);
689
690 RETURN x_return;
691
692 END get_salesrep_option;
693
694 --+
695 -- Procedure Name
696 -- get_concurrent_flag
697 -- Scope
698 -- public
699 -- Purpose
700 -- get the concurrent flag for the calc_submission batch
701 -- state.
702 -- History
703 -- 10-JUL-98 Richard Jin Created
704 --+
705 FUNCTION get_concurrent_flag (p_calc_sub_batch_id NUMBER) RETURN VARCHAR2 IS
706 l_concurrent_flag VARCHAR2(1);
707 BEGIN
708 SELECT concurrent_flag
709 INTO l_concurrent_flag
710 FROM cn_calc_submission_batches_all
711 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
712
713 RETURN l_concurrent_flag;
714 END get_concurrent_flag;
715
716 --+
717 -- Procedure Name
718 -- get_calc_sub_batch_id
719 -- Scope
720 -- public
721 -- Purpose
722 -- get the calculation type for this physical batch
723 -- state.
724 -- History
725 -- 10-JUL-98 Richard Jin Created
726 --+
727 FUNCTION get_calc_sub_batch_id RETURN NUMBER IS
728 l_calc_sub_batch_id NUMBER(15);
729 BEGIN
730 SELECT cn_calc_submission_batches_s1.nextval
731 INTO l_calc_sub_batch_id
732 FROM sys.dual;
733
734 RETURN l_calc_sub_batch_id;
735 END get_calc_sub_batch_id;
736
737 --+
738 -- Procedure Name
739 -- delete_calc_sub_batch
740 -- Scope
741 -- public
742 -- Purpose
743 -- delete a calc submission batch
744 -- state.
745 -- History
746 -- 10-JUL-98 Richard Jin Created
747 --+
748 PROCEDURE delete_calc_sub_batch (p_calc_sub_batch_id NUMBER) IS
749 BEGIN
750 DELETE cn_calc_submission_entries_all
751 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
752
753 DELETE cn_calc_sub_quotas_all
754 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
755
756 END delete_calc_sub_batch;
757
758
759 --+
760 -- Procedure Name
761 -- update_calc_sub_batch
762 -- Scope
763 -- public
764 -- Purpose
765 -- update status of a calc submission batch
766 -- state.
767 -- History
768 -- 10-JUL-98 Richard Jin Created
769 --+
770 PROCEDURE update_calc_sub_batch (p_logical_batch_id NUMBER,
771 p_status VARCHAR2) IS
772 l_salesrep_option varchar2(30);
773 l_from_period_id number(15);
774 l_to_period_id number(15);
775 l_start_date DATE;
776 l_end_date DATE;
777
778 l_intel_flag VARCHAR2(1);
779 l_calc_type VARCHAR2(30);
780 l_org_id number;
781
782 BEGIN
783 UPDATE cn_calc_submission_batches_all
784 SET status = p_status
785 WHERE logical_batch_id = p_logical_batch_id;
786
787 IF p_status = 'COMPLETE' THEN
788 SELECT salesrep_option, intelligent_flag, calc_type,
789 start_date, end_date, org_id
790 INTO l_salesrep_option, l_intel_flag, l_calc_type,
791 l_start_date, l_end_date, l_org_id
792 FROM cn_calc_submission_batches_all
793 WHERE logical_batch_id = p_logical_batch_id;
794
795 IF l_calc_type = 'COMMISSION' THEN
796 IF l_salesrep_option = 'ALL_REPS'
797 OR l_salesrep_option = 'REPS_IN_NOTIFY_LOG' THEN
798
799 l_from_period_id := cn_api.get_acc_period_id( l_start_date, l_org_id);
800 l_to_period_id := cn_api.get_acc_period_id( l_end_date, l_org_id);
801
802 UPDATE cn_notify_log_all
803 SET status = 'COMPLETE'
804 WHERE status = 'INCOMPLETE'
805 AND org_id = l_org_id
806 AND period_id between l_from_period_id and l_to_period_id
807 AND ( l_intel_flag = 'Y'
808 OR (l_intel_flag = 'N' AND start_date >= l_start_date ) );
809 END IF;
810 END IF;
811 END IF;
812 END update_calc_sub_batch;
813 --
814 --
815 --
816 END cn_calc_sub_batches_pkg;