1 PACKAGE BODY WSM_SPLIT_PERC_PVT AS
2 /* $Header: WSMCOSPB.pls 115.3 2004/05/27 07:49:02 mprathap noship $ */
3
4 /*---------------------------------------------------------------------------+
5 | Procedure to insert a row in the split percentages table for a given |
6 | (co-product,co product group, effective date, disable date) |
7 +---------------------------------------------------------------------------*/
8 PROCEDURE insert_row(x_err_code OUT NOCOPY NUMBER,
9 x_err_msg OUT NOCOPY VARCHAR2,
10 p_co_product_id IN NUMBER,
11 p_co_product_group_id IN NUMBER,
12 p_organization_id IN NUMBER,
13 p_revision IN VARCHAR2,
14 p_split IN NUMBER,
15 p_primary_flag IN VARCHAR2,
16 p_effectivity_date IN DATE,
17 p_disable_date IN DATE,
18 p_creation_date IN DATE,
19 p_created_by IN NUMBER,
20 p_last_update_date IN DATE,
21 p_last_updated_by IN NUMBER,
22 p_last_update_login IN NUMBER DEFAULT NULL,
23 p_attribute_category IN VARCHAR2 DEFAULT NULL,
24 p_attribute1 IN VARCHAR2 DEFAULT NULL,
25 p_attribute2 IN VARCHAR2 DEFAULT NULL,
26 p_attribute3 IN VARCHAR2 DEFAULT NULL,
27 p_attribute4 IN VARCHAR2 DEFAULT NULL,
28 p_attribute5 IN VARCHAR2 DEFAULT NULL,
29 p_attribute6 IN VARCHAR2 DEFAULT NULL,
30 p_attribute7 IN VARCHAR2 DEFAULT NULL,
31 p_attribute8 IN VARCHAR2 DEFAULT NULL,
32 p_attribute9 IN VARCHAR2 DEFAULT NULL,
33 p_attribute10 IN VARCHAR2 DEFAULT NULL,
34 p_attribute11 IN VARCHAR2 DEFAULT NULL,
35 p_attribute12 IN VARCHAR2 DEFAULT NULL,
36 p_attribute13 IN VARCHAR2 DEFAULT NULL,
37 p_attribute14 IN VARCHAR2 DEFAULT NULL,
38 p_attribute15 IN VARCHAR2 DEFAULT NULL,
39 p_request_id IN NUMBER DEFAULT NULL,
40 p_program_application_id IN NUMBER DEFAULT NULL,
41 p_program_id IN NUMBER DEFAULT NULL,
42 p_program_update_date IN DATE DEFAULT NULL
43 ) IS
44
45 BEGIN
46
47 x_err_code := 0;
48 x_err_msg := NULL;
49
50 insert into WSM_COPRODUCT_SPLIT_PERC
51 (
52 CO_PRODUCT_GROUP_ID,
53 CO_PRODUCT_ID,
54 ORGANIZATION_ID,
55 REVISION,
56 SPLIT,
57 PRIMARY_FLAG,
58 EFFECTIVITY_DATE,
59 DISABLE_DATE,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_LOGIN,
65 ATTRIBUTE_CATEGORY,
66 ATTRIBUTE1,
67 ATTRIBUTE2,
68 ATTRIBUTE3,
69 ATTRIBUTE4,
70 ATTRIBUTE5,
71 ATTRIBUTE6,
72 ATTRIBUTE7,
73 ATTRIBUTE8,
74 ATTRIBUTE9,
75 ATTRIBUTE10,
76 ATTRIBUTE11,
77 ATTRIBUTE12,
78 ATTRIBUTE13,
79 ATTRIBUTE14,
80 ATTRIBUTE15,
81 REQUEST_ID,
82 PROGRAM_APPLICATION_ID,
83 PROGRAM_ID,
84 PROGRAM_UPDATE_DATE
85 )
86 values
87 (
88 p_co_product_group_id,
89 p_co_product_id,
90 p_organization_id,
91 p_revision,
92 p_split,
93 p_primary_flag,
94 p_effectivity_date,
95 p_disable_date,
96 p_creation_date,
97 p_created_by,
98 p_last_update_date,
99 p_last_updated_by ,
100 p_last_update_login,
101 p_attribute_category,
102 p_attribute1,
103 p_attribute2,
104 p_attribute3,
105 p_attribute4,
106 p_attribute5,
107 p_attribute6,
108 p_attribute7,
109 p_attribute8,
110 p_attribute9,
111 p_attribute10,
112 p_attribute11,
113 p_attribute12,
114 p_attribute13,
115 p_attribute14,
116 p_attribute15,
117 p_request_id,
118 p_program_application_id,
119 p_program_id,
120 p_program_update_date
121 );
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 x_err_code:= -1;
126 x_err_msg := 'WSM_SPLIT_PERC_PVT.insert_row :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
127 RETURN;
128 END insert_row;
129
130 /*---------------------------------------------------------------------------+
131 | Procedure to update a row in the split percentages table |
132 +---------------------------------------------------------------------------*/
133
134 PROCEDURE update_row(x_err_code OUT NOCOPY NUMBER,
135 x_err_msg OUT NOCOPY VARCHAR2,
136 p_rowid IN VARCHAR2,
137 p_co_product_id IN NUMBER,
138 p_co_product_group_id IN NUMBER,
139 p_organization_id IN NUMBER,
140 p_revision IN VARCHAR2,
141 p_split IN NUMBER,
142 p_primary_flag IN VARCHAR2,
143 p_effectivity_date IN DATE,
144 p_disable_date IN DATE,
145 p_creation_date IN DATE,
146 p_created_by IN NUMBER,
147 p_last_update_date IN DATE,
148 p_last_updated_by IN NUMBER,
149 p_last_update_login IN NUMBER,
150 p_attribute_category IN VARCHAR2,
151 p_attribute1 IN VARCHAR2,
152 p_attribute2 IN VARCHAR2,
153 p_attribute3 IN VARCHAR2,
154 p_attribute4 IN VARCHAR2,
155 p_attribute5 IN VARCHAR2,
156 p_attribute6 IN VARCHAR2,
157 p_attribute7 IN VARCHAR2,
158 p_attribute8 IN VARCHAR2,
159 p_attribute9 IN VARCHAR2,
160 p_attribute10 IN VARCHAR2,
161 p_attribute11 IN VARCHAR2,
162 p_attribute12 IN VARCHAR2,
163 p_attribute13 IN VARCHAR2,
164 p_attribute14 IN VARCHAR2,
165 p_attribute15 IN VARCHAR2,
166 p_request_id IN NUMBER,
167 p_program_application_id IN NUMBER,
168 p_program_id IN NUMBER,
169 p_program_update_date IN DATE
170 ) IS
171 BEGIN
172
173 x_err_code := 0;
174 x_err_msg := NULL;
175
176 UPDATE WSM_COPRODUCT_SPLIT_PERC
177 SET
178 CO_PRODUCT_GROUP_ID = p_co_product_group_id,
179 CO_PRODUCT_ID = p_co_product_id,
180 ORGANIZATION_ID = p_organization_id,
181 REVISION = p_revision,
182 SPLIT = p_split,
183 PRIMARY_FLAG = p_primary_flag,
184 EFFECTIVITY_DATE = p_effectivity_date,
185 DISABLE_DATE = p_disable_date,
186 CREATION_DATE = p_creation_date,
187 CREATED_BY = p_created_by,
188 LAST_UPDATE_DATE = p_last_update_date,
189 LAST_UPDATED_BY = p_last_updated_by,
190 LAST_UPDATE_LOGIN = p_last_update_login,
191 ATTRIBUTE_CATEGORY = p_attribute_category,
192 ATTRIBUTE1 = p_attribute1,
193 ATTRIBUTE2 = p_attribute2,
194 ATTRIBUTE3 = p_attribute3,
195 ATTRIBUTE4 = p_attribute4,
196 ATTRIBUTE5 = p_attribute5,
197 ATTRIBUTE6 = p_attribute6,
198 ATTRIBUTE7 = p_attribute7,
199 ATTRIBUTE8 = p_attribute8,
200 ATTRIBUTE9 = p_attribute9,
201 ATTRIBUTE10 = p_attribute10,
202 ATTRIBUTE11 = p_attribute11,
203 ATTRIBUTE12 = p_attribute12,
204 ATTRIBUTE13 = p_attribute13,
205 ATTRIBUTE14 = p_attribute14,
206 ATTRIBUTE15 = p_attribute15,
207 REQUEST_ID = p_request_id,
208 PROGRAM_APPLICATION_ID = p_program_application_id,
209 PROGRAM_ID = p_program_id,
210 PROGRAM_UPDATE_DATE = p_program_update_date
211 WHERE rowid = p_rowid;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 x_err_code := -1;
216 x_err_msg := 'WSM_SPLIT_PERC_PVT.update_row :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
217 RETURN;
218
219 END update_row;
220
221 /*---------------------------------------------------------------------------+
222 | Lock row procedure |
223 | |
224 +---------------------------------------------------------------------------*/
225
226 PROCEDURE lock_row( x_err_code OUT NOCOPY NUMBER,
227 x_err_msg OUT NOCOPY VARCHAR2,
228 p_rowid IN VARCHAR2,
229 p_co_product_id IN NUMBER,
230 p_co_product_group_id IN NUMBER,
231 p_organization_id IN NUMBER,
232 p_revision IN VARCHAR2,
233 p_split IN NUMBER,
234 p_primary_flag IN VARCHAR2,
235 p_effectivity_date IN DATE,
236 p_disable_date IN DATE,
237 p_attribute_category IN VARCHAR2,
238 p_attribute1 IN VARCHAR2,
239 p_attribute2 IN VARCHAR2,
240 p_attribute3 IN VARCHAR2,
241 p_attribute4 IN VARCHAR2,
242 p_attribute5 IN VARCHAR2,
243 p_attribute6 IN VARCHAR2,
244 p_attribute7 IN VARCHAR2,
245 p_attribute8 IN VARCHAR2,
246 p_attribute9 IN VARCHAR2,
247 p_attribute10 IN VARCHAR2,
248 p_attribute11 IN VARCHAR2,
249 p_attribute12 IN VARCHAR2,
250 p_attribute13 IN VARCHAR2,
251 p_attribute14 IN VARCHAR2,
252 p_attribute15 IN VARCHAR2
253 ) IS
254
255 CURSOR C IS
256 SELECT *
257 FROM WSM_COPRODUCT_SPLIT_PERC
258 WHERE rowid = p_rowid
259 FOR UPDATE of co_product_id NOWAIT;
260
261 l_rec C%ROWTYPE;
262
263 BEGIN
264
265 x_err_code := 0;
266 x_err_msg := NULL;
267
268 OPEN C;
269 FETCH C INTO l_rec;
270
271 IF (C%NOTFOUND) THEN
272
273 CLOSE C;
274 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
275 x_err_code := -1;
276 x_err_msg := FND_MESSAGE.GET;
277 RETURN;
278
279 END IF;
280 CLOSE C;
281
282 IF ( (l_rec.co_product_group_id = p_co_product_group_id)
283 AND (l_rec.co_product_id = p_co_product_id)
284 AND (l_rec.organization_id = p_organization_id)
285 AND (l_rec.primary_flag = p_primary_flag)
286 AND (l_rec.split = p_split)
287 AND (l_rec.revision = p_revision)
288 AND ( (l_rec.attribute_category = p_Attribute_Category)
289 OR ( (l_rec.attribute_category IS NULL)
290 AND (p_Attribute_Category IS NULL)))
291 AND ( (l_rec.attribute1 = p_Attribute1)
292 OR ( (l_rec.attribute1 IS NULL)
293 AND (p_Attribute1 IS NULL)))
294 AND ( (l_rec.attribute2 = p_Attribute2)
295 OR ( (l_rec.attribute2 IS NULL)
296 AND (p_Attribute2 IS NULL)))
297 AND ( (l_rec.attribute3 = p_Attribute3)
298 OR ( (l_rec.attribute3 IS NULL)
299 AND (p_Attribute3 IS NULL)))
300 AND ( (l_rec.attribute4 = p_Attribute4)
301 OR ( (l_rec.attribute4 IS NULL)
302 AND (p_Attribute4 IS NULL)))
303 AND ( (l_rec.attribute5 = p_Attribute5)
304 OR ( (l_rec.attribute5 IS NULL)
305 AND (p_Attribute5 IS NULL)))
306 AND ( (l_rec.attribute6 = p_Attribute6)
307 OR ( (l_rec.attribute6 IS NULL)
308 AND (p_Attribute6 IS NULL)))
309 AND ( (l_rec.attribute7 = p_Attribute7)
310 OR ( (l_rec.attribute7 IS NULL)
311 AND (p_Attribute7 IS NULL)))
312 AND ( (l_rec.attribute8 = p_Attribute8)
313 OR ( (l_rec.attribute8 IS NULL)
314 AND (p_Attribute8 IS NULL)))
315 AND ( (l_rec.attribute9 = p_Attribute9)
316 OR ( (l_rec.attribute9 IS NULL)
317 AND (p_Attribute9 IS NULL)))
318 AND ( (l_rec.attribute10 = p_Attribute10)
319 OR ( (l_rec.attribute10 IS NULL)
320 AND (p_Attribute10 IS NULL)))
321 AND ( (l_rec.attribute11 = p_Attribute11)
322 OR ( (l_rec.attribute11 IS NULL)
326 AND (p_Attribute12 IS NULL)))
323 AND (p_Attribute11 IS NULL)))
324 AND ( (l_rec.attribute12 = p_Attribute12)
325 OR ( (l_rec.attribute12 IS NULL)
327 AND ( (l_rec.attribute13 = p_Attribute13)
328 OR ( (l_rec.attribute13 IS NULL)
329 AND (p_Attribute13 IS NULL)))
330 AND ( (l_rec.attribute14 = p_Attribute14)
331 OR ( (l_rec.attribute14 IS NULL)
332 AND (p_Attribute14 IS NULL)))
333 AND ( (l_rec.attribute15 = p_Attribute15)
334 OR ( (l_rec.attribute15 IS NULL)
335 AND (p_Attribute15 IS NULL)))
336 ) THEN
337 RETURN;
338 ELSE
339 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
340 x_err_code := -1;
341 x_err_msg := FND_MESSAGE.GET;
342 RETURN;
343 END IF;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 x_err_code := -1;
348 x_err_msg := 'WSM_SPLIT_PERC_PVT.lock_row :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
349 RETURN;
350 END lock_row;
351
352 /*---------------------------------------------------------------------------+
353 | Procedure to delete all the entries corresponding to a (co product id, |
354 | co product group id) pair in the split percentages table |
355 +---------------------------------------------------------------------------*/
356
357 PROCEDURE delete_row( x_err_code OUT NOCOPY NUMBER,
358 x_err_msg OUT NOCOPY VARCHAR2,
359 p_co_product_id IN NUMBER,
360 p_co_product_group_id IN NUMBER,
361 p_organization_id IN NUMBER
362 ) IS
363 BEGIN
364
365 x_err_code := 0;
366 x_err_msg := NULL;
367
368 DELETE FROM WSM_COPRODUCT_SPLIT_PERC
369 WHERE co_product_id = p_co_product_id
370 AND co_product_group_id = p_co_product_group_id
371 AND organization_id = p_organization_id;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 x_err_code:= -1;
376 x_err_msg := 'WSM_SPLIT_PERC_PVT.delete_row :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
377 RETURN;
378 END delete_row;
379
380 /*---------------------------------------------------------------------------+
381 | Procedure to delete all the records pertaining to a co product group id in |
382 | the split percentages table |
383 +---------------------------------------------------------------------------*/
384 PROCEDURE delete_all_range(x_err_code OUT NOCOPY NUMBER,
385 x_err_msg OUT NOCOPY VARCHAR2,
386 p_organization_id IN NUMBER,
387 p_co_product_group_id IN NUMBER) IS
388 BEGIN
389
390 x_err_code := 0;
391 x_err_msg := NULL;
392
393 DELETE FROM WSM_COPRODUCT_SPLIT_PERC
394 WHERE co_product_group_id = p_co_product_group_id
395 AND organization_id = p_organization_id;
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 x_err_code:= -1;
400 x_err_msg := 'WSM_SPLIT_PERC_PVT.delete_all_range :' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
401 RETURN;
402 END delete_all_range;
403
404
405 /*---------------------------------------------------------------------------+
406 | Procedure to ensure that that no two ranges are overlapping in the time |
407 | frame. Called immediately after inserting a new split eff. range |
408 +---------------------------------------------------------------------------*/
409 PROCEDURE process_records ( l_co_product_gr_id IN NUMBER,
410 from_eff_dt IN DATE,
411 to_eff_dt IN DATE,
412 x_err_code OUT NOCOPY NUMBER,
413 x_err_msg OUT NOCOPY VARCHAR2) IS
414 h_eff_date DATE; /*Effective date of the range in which from_eff_dt falls*/
415 h_disable_date DATE; /*Disable date of the range in which to_eff_dt falls*/
416 intersect_exists NUMBER := 0;
417 stmt_num NUMBER;
418
419 /* for debug purposes..*/
420 l_debug varchar2(200);
421
422 BEGIN
423
424 x_err_code := 0;
425 x_err_msg := NULL;
426
427 delete from wsm_coproduct_split_perc
428 where co_product_group_id = l_co_product_gr_id
429 and ( (effectivity_date >= from_eff_dt and
430 ((disable_date is not null and disable_date < nvl(to_eff_dt,disable_date+1))
431 OR
432 ((disable_date is NULL) and (to_eff_dt is NULL) and effectivity_date > from_eff_dt))
433 )
434 OR
435 (effectivity_date > from_eff_dt and
436 (disable_date is not null and disable_date <= nvl(to_eff_dt,disable_date+1))
437 )
438 );
439
440 /*Look for following 2 mutually exclusive possibilities:
441 1.New range entered is a sub set of existing effective range.
442 2.New range is intersecting 1/2 existing eff ranges.*/
443
444
445 stmt_num := 10;
446
447 select max(effectivity_date)
448 into h_eff_date
449 from wsm_coproduct_split_perc
450 where co_product_group_id = l_co_product_gr_id
451 and effectivity_date <= from_eff_dt
452 and not ( effectivity_date = from_eff_dt
453 and
454 (
455 (disable_date is null and to_eff_dt is null)
456 or
457 (disable_date=nvl(to_eff_dt,disable_date + 1))
458 )
459 );
460
461
462 h_disable_date := null;
463 intersect_exists := 0;
464
465 IF to_eff_dt IS NOT NULL THEN
466
467 /* D1------------------------> D10
471 select min(disable_date)
468 D5------------------> D10 ( new record .. no overlap ) */
469
470 stmt_num := 20;
472 into h_disable_date
473 from wsm_coproduct_split_perc
474 where co_product_group_id = l_co_product_gr_id
475 and nvl(disable_date,to_eff_dt+1) >= to_eff_dt
476 and not (effectivity_date = from_eff_dt and nvl(disable_date,to_eff_dt+1) = to_eff_dt);
477
478
479 --If the following SQL selects a record, it is Possibility 1
480 -- Existing range is D10 --------- D30 -----------D40
481 -- new range is D15-- D25
482
483 BEGIN
484
485 /* if D10-------------> D20 ------> D22-----> D24 -----------> D25 --------------------> null
486 D20 --------------------------------------------> D35 ( again no intersect)
487 D15 ------------------------------------------> D25 ( again no intersect )
488 D15 ---------------------------------------------------------------------> null ( NO INTERSECT )
489 D35 --------------> NULL ( NO INTERSECT )
490
491 D21 --------------------------------------------> D35 ( NO intersect )
492 D21 ----------------------> D27 ( NO INTERSECT )
493
494 D35 -------> D50 ( INTERSECT )
495 D5 --------> D12 ( INTERSECT )
496 */
497
498
499 stmt_num := 30;
500
501 select 1
502 into intersect_exists
503 from wsm_coproduct_split_perc
504 where co_product_group_id = l_co_product_gr_id
505 and effectivity_date < from_eff_dt
506 and effectivity_date <= h_eff_date
507 and (( (disable_date is not null )
508 and
509 ( disable_date = nvl(h_disable_date,disable_date+1) )
510 )
511 OR
512 (
513 disable_date is null and h_disable_date is null and to_eff_dt is not null
514 )
515 );
516
517
518 EXCEPTION
519 when NO_DATA_FOUND then
520 --Partial Intersection exists
521 intersect_exists := 0;
522 WHEN TOO_MANY_ROWS THEN
523 intersect_exists := 1;
524 END;
525
526 END IF;
527
528
529 if intersect_exists = 1 then
530 -- Exists D1-D20. Insert D5-D10.
531
532 IF h_eff_date <> from_eff_dt THEN
533
534 stmt_num := 40;
535
536 insert into wsm_coproduct_split_perc (
537 co_product_group_id,
538 co_product_id,
539 ORGANIZATION_ID,
540 CREATION_DATE,
541 CREATED_BY ,
542 LAST_UPDATE_DATE,
543 LAST_UPDATED_BY,
544 LAST_UPDATE_LOGIN,
545 REVISION,
546 SPLIT ,
547 EFFECTIVITY_DATE,
548 DISABLE_DATE,
549 PRIMARY_FLAG)
550 (select co_product_group_id,
551 co_product_id,
552 ORGANIZATION_ID,
553 CREATION_DATE,
554 CREATED_BY,
555 LAST_UPDATE_DATE,
556 LAST_UPDATED_BY,
557 LAST_UPDATE_LOGIN,
558 REVISION,
559 SPLIT,
560 h_eff_date,
561 from_eff_dt,
562 PRIMARY_FLAG
563 from wsm_coproduct_split_perc
564 where co_product_group_id = l_co_product_gr_id
565 and effectivity_date = h_eff_date
566 and ((disable_date IS NULL and h_disable_date IS NULL) OR (disable_date IS NOT NULL AND (disable_date = nvl(h_disable_date,disable_date+1)))));
567 end if;
568
569 if (nvl(h_disable_date,to_eff_dt+1) <> to_eff_dt) THEN
570
571 stmt_num := 50;
572
573 insert into wsm_coproduct_split_perc (
574 co_product_group_id,
575 co_product_id,
576 ORGANIZATION_ID,
577 CREATION_DATE,
578 CREATED_BY ,
579 LAST_UPDATE_DATE,
580 LAST_UPDATED_BY,
581 LAST_UPDATE_LOGIN,
582 REVISION,
583 SPLIT ,
584 EFFECTIVITY_DATE,
585 DISABLE_DATE,
586 PRIMARY_FLAG)
587 (select co_product_group_id,
588 co_product_id,
589 ORGANIZATION_ID,
590 CREATION_DATE,
591 CREATED_BY ,
592 LAST_UPDATE_DATE,
593 LAST_UPDATED_BY,
594 LAST_UPDATE_LOGIN,
595 REVISION,
596 SPLIT,
597 to_eff_dt,
598 h_disable_date,
599 PRIMARY_FLAG
600 from wsm_coproduct_split_perc
601 where co_product_group_id = l_co_product_gr_id
602 and ((disable_date IS NULL and h_disable_date IS NULL) OR (disable_date IS NOT NULL AND (disable_date = nvl(h_disable_date,disable_date+1))))
603 and not(effectivity_date = from_eff_dt));
604
605
606 end if;
607
608 stmt_num := 60;
609
610 delete from wsm_coproduct_split_perc
611 where co_product_group_id = l_co_product_gr_id
612 and effectivity_date = h_eff_date
613 and ((disable_date IS NULL and h_disable_date IS NULL) OR (disable_date IS NOT NULL AND (disable_date = nvl(h_disable_date,disable_date+1))));
614 --End of possibility 1.
615
616 return;
617
618 end if;
619
620 /* Now, need to update D15-D20 period as D16-D20*/
621 --Possibility 2
622 stmt_num := 70;
623
624 IF to_eff_dt is not null THEN
625
626 update wsm_coproduct_split_perc
627 set effectivity_date = to_eff_dt
628 where co_product_group_id = l_co_product_gr_id
629 and ((disable_date IS NULL and h_disable_date IS NULL) OR (disable_date IS NOT NULL AND (disable_date = nvl(h_disable_date,disable_date+1))));
630
631 end if;
632
633
634 /* Suppose we changed dates from D7-D16 instead of D5-D16, we need to
638
635 update D5-D10 to D5-D7*/
636
637 stmt_num := 80;
639 update wsm_coproduct_split_perc
640 set disable_date = from_eff_dt
641 where co_product_group_id = l_co_product_gr_id
642 and effectivity_date = h_eff_date;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 x_err_code := -1;
647 x_err_msg := 'WSM_SPLIT_PERC_PVT .process_records : stmt : ' || stmt_num || ' ' || SQLCODE || ' :' || substr(SQLERRM,1,1000);
648 RETURN;
649 end process_records;
650
651 /*---------------------------------------------------------------------------+
652 | Procdure to check if the update of comp. eff/ disable date will cause |
653 | the deletion of any existent ranges |
654 +---------------------------------------------------------------------------*/
655
656 /* This procedure is not used as comp. eff./diable date will not be related to the
657 co product eff/disable dates */
658
659 FUNCTION validate_range (p_co_product_group_id IN NUMBER,
660 p_organization_id IN NUMBER,
661 p_effectivity_date IN DATE,
662 p_disable_date IN DATE) RETURN NUMBER IS
663
664 l_retval NUMBER:=0;
665 l_num NUMBER:=0;
666
667 BEGIN
668 -- Determine if any of the ranges have a lesser effectivity dates.
669
670 BEGIN
671 select 1
672 into l_num
673 from WSM_COPRODUCT_SPLIT_PERC
674 where organization_id = p_organization_id
675 and co_product_group_id = p_co_product_group_id
676 and disable_date is not NULL
677 and disable_date <= p_effectivity_date;
678
679
680 l_retval:=l_retval+1;
681 EXCEPTION
682 WHEN NO_DATA_FOUND THEN
683 null;
684 WHEN TOO_MANY_ROWS THEN
685 l_num := 1;
686 l_retval := l_retval + 1;
687 END;
688
689 -- Determine if any of the ranges has a higher effectivity dates.
690
691 BEGIN
692 IF p_disable_date is NOT NULL THEN
693 select 1
694 into l_num
695 from WSM_COPRODUCT_SPLIT_PERC
696 where organization_id = p_organization_id
697 and co_product_group_id = p_co_product_group_id
698 and effectivity_date >= p_disable_date;
699
700 l_retval:=l_retval+2;
701 END IF;
702
703 EXCEPTION
704 WHEN NO_DATA_FOUND THEN
705 null;
706 WHEN TOO_MANY_ROWS THEN
707 l_num := 1;
708 l_retval:=l_retval+2;
709 END;
710
711 RETURN l_retval;
712 END validate_range;
713
714 /*---------------------------------------------------------------------------+
715 | Procdure to update/delete any existent ranges that would be affected by the|
716 | the update of comp. eff. date/ disable date |
717 +---------------------------------------------------------------------------*/
718
719 /* This procedure is not used as comp. eff./diable date will not be related to the
720 co product eff/disable dates */
721
722 PROCEDURE update_split_range(x_err_code OUT NOCOPY NUMBER,
723 x_err_msg OUT NOCOPY VARCHAR2,
724 p_organization_id IN NUMBER,
725 p_co_product_group_id IN NUMBER,
726 p_effectivity_date IN DATE,
727 p_disable_date IN DATE,
728 p_update_range IN NUMBER
729 ) IS
730
731 l_num NUMBER:=0;
732 BEGIN
733
734 -- Indicates that effe. date will result in some deletion
735 IF p_update_range IN (1,3) THEN
736 -- delete any range which has the disable date less than or equal to the
737 -- new effectivity date
738 DELETE FROM WSM_COPRODUCT_SPLIT_PERC
739 WHERE organization_id = p_organization_id
740 AND co_product_group_id = p_co_product_group_id
741 AND disable_date is NOT NULL
742 AND disable_date <= p_effectivity_date;
743 END IF;
744
745 -- Update the effect. date of the range that has the effec. date less than the
746 -- new effectivity date
747 UPDATE WSM_COPRODUCT_SPLIT_PERC
748 SET effectivity_date = p_effectivity_date
749 WHERE organization_id = p_organization_id
750 AND co_product_group_id = p_co_product_group_id
751 AND effectivity_date < p_effectivity_date;
752
753 -- Indicates that some range has to be deleted due to the new disable date..
754 IF p_update_range IN (2,3) THEN
755 -- delete any range which has the effective date greater than or equal
756 -- to the new disable date..
757 DELETE
758 FROM WSM_COPRODUCT_SPLIT_PERC
759 WHERE organization_id = p_organization_id
760 AND co_product_group_id = p_co_product_group_id
761 AND effectivity_date >= NVL(p_disable_date,effectivity_date-1);
762
763 END IF;
764
765 BEGIN
766 -- check if any NULL disable date exist..
767 l_num := 0;
768
769 select 1
770 into l_num
771 from WSM_COPRODUCT_SPLIT_PERC
772 WHERE organization_id = p_organization_id
773 AND co_product_group_id = p_co_product_group_id
774 AND disable_date IS NULL;
775
776 EXCEPTION
777 WHEN NO_DATA_FOUND THEN
778 l_num := 0;
779
780 WHEN TOO_MANY_ROWS THEN
781 l_num := 1;
782 END;
783
784 IF l_num = 1 THEN
785 -- disable date has been changed from NULL to a non-null value...
786 -- so update all records with NULL disable date.
787 IF p_disable_date IS NOT NULL THEN
788 UPDATE WSM_COPRODUCT_SPLIT_PERC
789 SET disable_date = p_disable_date
790 WHERE organization_id = p_organization_id
791 AND co_product_group_id = p_co_product_group_id
792 AND disable_date is NULL;
793
794 END IF;
795 ELSIF l_num = 0 THEN
796
800 SET disable_date = p_disable_date
797 -- No records exist with NULL disable date
798
799 UPDATE WSM_COPRODUCT_SPLIT_PERC
801 WHERE organization_id = p_organization_id
802 AND co_product_group_id = p_co_product_group_id
803 AND disable_date IN ( SELECT MAX(disable_date)
804 FROM WSM_COPRODUCT_SPLIT_PERC
805 WHERE organization_id = p_organization_id
806 AND co_product_group_id = p_co_product_group_id);
807 END IF;
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 x_err_code := -1;
812 x_err_msg := 'WSM_SPLIT_PERC_PVT.update_split_range : ' || SQLCODE || substr(SQLERRM,1,1000);
813 RETURN;
814 END update_split_range;
815
816 /*---------------------------------------------------------------------------+
817 | Procedure to insert a co-product in all ranges of a co-product group id |
818 | with split perc 0% in case of sec. co-product and 100% in case of |
819 | primary co-product |
820 +---------------------------------------------------------------------------*/
821
822 PROCEDURE insert_co_product_range(x_err_code OUT NOCOPY NUMBER,
823 x_err_msg OUT NOCOPY VARCHAR2,
824 p_co_product_group_id IN NUMBER,
825 p_co_product_id IN NUMBER,
826 p_revision IN VARCHAR2,
827 p_split IN NUMBER,
828 p_primary_flag IN VARCHAR2,
829 p_organization_id IN NUMBER,
830 p_effectivity_date IN DATE,
831 p_disable_date IN DATE,
832 p_creation_date IN DATE,
833 p_created_by IN NUMBER,
834 p_last_update_date IN DATE,
835 p_last_updated_by IN NUMBER
836 ) IS
837 CURSOR range_cursor IS select distinct effectivity_date,disable_date
838 from WSM_COPRODUCT_SPLIT_PERC
839 where co_product_group_id= p_co_product_group_id;
840
841 l_num NUMBER:=0;
842
843 l_err_code NUMBER:=0;
844 l_err_msg VARCHAR2(2000);
845
846
847 BEGIN
848 /*---------------------------------------------------------------+
849 | first check if any range exist for this co-product-group_id |
850 +---------------------------------------------------------------*/
851
852 BEGIN
853 select 1
854 into l_num
855 from WSM_COPRODUCT_SPLIT_PERC
856 WHERE co_product_group_id = p_co_product_group_id ;
857
858 EXCEPTION
859 WHEN NO_DATA_FOUND THEN
860 l_num :=0;
861 WHEN TOO_MANY_ROWS THEN
862 l_num := 1;
863 END;
864
865 /* If the value of l_num = 0 then no records exist.. use the
866 value of p_effectivity_date and disable date passed... */
867
868 IF (l_num=0) THEN
869 -- insert using the date data passed
870 WSM_SPLIT_PERC_PVT.insert_row(x_err_code => l_err_code,
871 x_err_msg => l_err_msg,
872 p_co_product_id => p_co_product_id,
873 p_co_product_group_id => p_co_product_group_id,
874 p_organization_id => p_organization_id,
875 p_revision => p_revision,
876 p_split => p_split,
877 p_primary_flag => p_primary_flag,
878 p_effectivity_date => p_effectivity_date,
879 p_disable_date => p_disable_date,
880 p_creation_date => p_creation_date,
881 p_created_by => p_created_by,
882 p_last_update_date => p_last_update_date,
883 p_last_updated_by => p_last_updated_by );
884 IF l_err_code <> 0 THEN
885 -- indicates that some error has occured....
886 x_err_code := l_err_code;
887 x_err_msg := l_err_msg;
888 RETURN;
889 END IF;
890 ELSIF l_num=1 THEN
891 -- Indicates that the information resides in the db for this co-product...
892 -- so open the cursor and insert this data...
893 SAVEPOINT date_save;
894 FOR date_rec IN range_cursor LOOP
895 -- get the data and call the insert procedure....
896 WSM_SPLIT_PERC_PVT.insert_row(x_err_code => l_err_code,
897 x_err_msg => l_err_msg,
898 p_co_product_id => p_co_product_id,
899 p_co_product_group_id => p_co_product_group_id,
900 p_organization_id => p_organization_id,
901 p_revision => p_revision,
902 p_split => p_split,
903 p_primary_flag => p_primary_flag,
904 p_effectivity_date => date_rec.effectivity_date,
905 p_disable_date => date_rec.disable_date,
906 p_creation_date => p_creation_date,
907 p_created_by => p_created_by,
908 p_last_update_date => p_last_update_date,
909 p_last_updated_by => p_last_updated_by );
910 IF l_err_code <> 0 THEN
911 -- indicates that some error has occured....
912 ROLLBACK TO date_save;
913 x_err_code := l_err_code;
914 x_err_msg := l_err_msg;
915 RETURN;
916 END IF;
917 END LOOP;
918 -- end of the cursor code...
919 END IF;
920 EXCEPTION
921 WHEN OTHERS THEN
922 x_err_code := -1;
923 x_err_msg := 'WSM_SPLIT_PERC_PVT.insert_co_product_range : ' || SQLCODE || substr(SQLERRM,1,1000);
924 RETURN;
925 END insert_co_product_range;
926
927 /*---------------------------------------------------------------------------+
928 | Procedure to check if there is atleast one co product of a co-prod group |
929 | in the range passed that has a zero split percentage.. |
930 +--------------------------------------------------------------------------*/
931
932 FUNCTION check_split_perc_exists(x_err_code OUT NOCOPY NUMBER,
936 p_effectivity_date IN DATE,
933 x_err_msg OUT NOCOPY VARCHAR2,
934 p_co_product_group_id IN NUMBER,
935 p_organization_id IN NUMBER,
937 p_disable_date IN DATE) RETURN BOOLEAN IS
938 l_num NUMBER:=0;
939 l_count NUMBER:=0;
940 BEGIN
941 x_err_code := 0;
942 x_err_msg := null;
943
944 BEGIN
945
946 select 1
947 into l_num
948 from WSM_COPRODUCT_SPLIT_PERC
949 where organization_id = p_organization_id
950 and co_product_group_id = p_co_product_group_id
951 and effectivity_date = p_effectivity_date
952 and ((p_disable_date is NULL and disable_date is NULL) OR (p_disable_date = disable_date))
953 and split = 0;
954
955 RETURN TRUE;
956
957 EXCEPTION
958
959 WHEN NO_DATA_FOUND THEN
960 RETURN FALSE;
961
962 WHEN TOO_MANY_ROWS THEN
963 RETURN TRUE;
964 END;
965 EXCEPTION
966 WHEN OTHERS THEN
967 x_err_code := -1;
968 x_err_msg := 'WSM_SPLIT_PERC_PVT.check_split_perc_exists : ' || SQLCODE || substr(SQLERRM,1,1000);
969 RETURN TRUE;
970
971 END check_split_perc_exists;
972
973 /*---------------------------------------------------------------------------+
974 | Procedure to check if there is atleast one range in wich the co product |
975 | passed has a non-zero split percentage.. |
976 +---------------------------------------------------------------------------*/
977
978 FUNCTION check_split_perc_exists(x_err_code OUT NOCOPY NUMBER,
979 x_err_msg OUT NOCOPY VARCHAR2,
980 p_co_product_id IN NUMBER,
981 p_co_product_group_id IN NUMBER,
982 p_organization_id IN NUMBER ) RETURN BOOLEAN IS
983 l_num NUMBER:=0;
984 l_count NUMBER:=0;
985 BEGIN
986 x_err_code := 0;
987 x_err_msg := null;
988
989 IF p_co_product_id IS NOT NULL THEN
990
991 BEGIN
992 /* one more check to be done here...
993 check if it is the only one left and it is the primary.. */
994
995 SELECT 1
996 INTO l_num
997 from WSM_COPRODUCT_SPLIT_PERC
998 where organization_id = p_organization_id
999 and co_product_group_id = p_co_product_group_id
1000 and co_product_id <> p_co_product_id;
1001
1002 EXCEPTION
1003 WHEN NO_DATA_FOUND THEN
1004
1005 -- even though split may be more than 0..
1006 -- since this is the only co-product left can go ahead with the deletion.
1007 RETURN FALSE;
1008
1009 WHEN TOO_MANY_ROWS THEN
1010 NULL;
1011 END;
1012
1013 BEGIN
1014
1015 SELECT 1
1016 INTO l_num
1017 from WSM_COPRODUCT_SPLIT_PERC
1018 where organization_id = p_organization_id
1019 and co_product_group_id = p_co_product_group_id
1020 and co_product_id = p_co_product_id
1021 and split > 0 ;
1022
1023 RETURN TRUE;
1024
1025 EXCEPTION
1026 WHEN NO_DATA_FOUND THEN
1027 RETURN FALSE;
1028
1029 WHEN TOO_MANY_ROWS THEN
1030 RETURN TRUE;
1031 END;
1032 ELSE
1033 -- Indicates that the check is for the full co-product-group-id
1034 --- selects the max. no. of ranges in which any co-product of the
1035 -- given co_product_group_id has 0%
1036 /*Bug 3647337
1037 select distinct max(count(*))
1038 into l_num
1039 from WSM_COPRODUCT_SPLIT_PERC
1040 where organization_id = p_organization_id
1041 and co_product_group_id = p_co_product_group_id
1042 and split = 0
1043 group by co_product_id;
1044 */
1045
1046 -- selects the totla no. of ranges for a co_product_group_id
1047 /*3647337
1048 select count(*)
1049 into l_count
1050 from WSM_COPRODUCT_SPLIT_DATES_V
1051 where organization_id = p_organization_id
1052 and co_product_group_id = p_co_product_group_id;
1053 Bug 3647337*/
1054
1055 --Following SQL is added for 3647337
1056 select min(sum(split))
1057 into l_num
1058 from WSM_COPRODUCT_SPLIT_PERC
1059 where organization_id = p_organization_id
1060 and co_product_group_id = p_co_product_group_id
1061 group by co_product_id;
1062 -- If equal then atleast one co-product has 0% split in all the ranges..
1063 --IF l_num=l_count THEN Bug 3647337
1064 IF l_num=0 THEN --Bug 3647337
1065 -- Indicates that atleast one record exists with 0 in all the ranges.
1066 RETURN TRUE;
1067 ELSE
1068 RETURN FALSE;
1069 END IF;
1070 END IF;
1071
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 x_err_code := -1;
1075 x_err_msg := 'WSM_SPLIT_PERC_PVT.check_split_perc_exists : ' || SQLCODE || substr(SQLERRM,1,1000);
1076 RETURN TRUE;
1077
1078 END check_split_perc_exists;
1079
1080 /*---------------------------------------------------------------------------+
1081 | Procedure to check if a co-product group id had got only one split |
1082 | effectivity range |
1083 +---------------------------------------------------------------------------*/
1084
1085 FUNCTION check_unique_range(x_err_code OUT NOCOPY NUMBER,
1086 x_err_msg OUT NOCOPY VARCHAR2,
1087 p_co_product_group_id IN NUMBER,
1088 p_organization_id IN NUMBER ) RETURN BOOLEAN IS
1089
1090 l_num NUMBER := 0;
1091 BEGIN
1092 x_err_code := 0;
1093 x_err_msg := null;
1094
1095 /*3647337
1096 SELECT count(*)
1097 INTO l_num
1098 FROM WSM_COPRODUCT_SPLIT_DATES_V
1099 where organization_id = p_organization_id
1100 and co_product_group_id = p_co_product_group_id;
1101 3647337*/
1102 --Following SQL is added for 3647337
1103 SELECT count(*)
1104 INTO l_num
1105 FROM WSM_COPRODUCT_SPLIT_PERC
1106 where organization_id = p_organization_id
1107 and co_product_group_id = p_co_product_group_id
1108 group by EFFECTIVITY_DATE;
1109
1110 IF l_num > 1 THEN
1111 RETURN TRUE;
1112 ELSE
1113 RETURN FALSE;
1114 END IF;
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 x_err_code := -1;
1118 x_err_msg := 'WSM_SPLIT_PERC_PVT.check_unique_range : ' || SQLCODE || substr(SQLERRM,1,1000);
1119 RETURN TRUE;
1120 END check_unique_range;
1121
1122 /*---------------------------------------------------------------------------+
1123 | Procedure to check if the new eff. range ( eff date/ disable date ) |
1124 | will cause any existing ranges to be deleted |
1125 +---------------------------------------------------------------------------*/
1126
1127 FUNCTION check_any_del_range ( p_co_product_group_id IN NUMBER,
1128 p_organization_id IN NUMBER,
1129 p_effectivity_date IN DATE,
1130 p_disable_date IN DATE) RETURN NUMBER IS
1131
1132 l_retval NUMBER:=0;
1133 l_num NUMBER:=0;
1134
1135 BEGIN
1136
1137 -- Determine if any of the ranges has a higher effectivity dates.
1138 BEGIN
1139 IF p_disable_date IS NOT NULL THEN
1140
1141 /* This deals with situations like
1142 D10 ---------> D30--------------> NULL
1143 and the user is entering
1144 D5 -------------> D35
1145 */
1146 SELECT 1
1147 INTO l_num
1148 FROM WSM_COPRODUCT_SPLIT_PERC
1149 where organization_id = p_organization_id
1150 and co_product_group_id = p_co_product_group_id
1151 and disable_date is NOT NULL
1152 and effectivity_date >= p_effectivity_date
1153 and disable_date<=p_disable_date;
1154
1155
1156 l_retval:=1;
1157 ELSE
1158
1159 /* This deals with situations like
1160 D10 ---------> D30
1161 and the user is entering
1162 D5 -------------> NULL
1163 */
1164
1165 SELECT 1
1166 INTO l_num
1167 FROM WSM_COPRODUCT_SPLIT_PERC
1168 WHERE organization_id = p_organization_id
1169 and co_product_group_id = p_co_product_group_id
1170 and disable_date is NOT NULL
1171 and effectivity_date >= p_effectivity_date;
1172
1173 l_retval := 1;
1174
1175 END IF;
1176
1177 EXCEPTION
1178 WHEN NO_DATA_FOUND THEN
1179 null;
1180 WHEN TOO_MANY_ROWS THEN
1181 l_retval := 1;
1182 END;
1183
1184 RETURN l_retval;
1185 END check_any_del_range;
1186
1187
1188 /*---------------------------------------------------------------------------+
1189 | Procedure to check if a range is preexisting |
1190 +---------------------------------------------------------------------------*/
1191
1192 FUNCTION check_unique(x_err_code OUT NOCOPY NUMBER,
1193 x_err_msg OUT NOCOPY VARCHAR2,
1194 p_co_product_group_id IN NUMBER,
1195 p_organization_id IN NUMBER,
1196 p_effectivity_date IN DATE,
1197 p_disable_date IN DATE) RETURN BOOLEAN IS
1198
1199 l_num NUMBER := 0;
1200
1201 BEGIN
1202 x_err_code := 0;
1203 x_err_msg := null;
1204
1205 BEGIN
1206 IF p_disable_date is NULL THEN
1207
1208 SELECT 1
1209 INTO l_num
1210 FROM WSM_COPRODUCT_SPLIT_PERC
1211 WHERE organization_id = p_organization_id
1212 AND co_product_group_id = p_co_product_group_id
1213 AND effectivity_date = p_effectivity_date
1214 AND disable_date IS NULL;
1215
1216 RETURN FALSE;
1217
1218 ELSE
1219
1220 SELECT 1
1221 INTO l_num
1222 FROM WSM_COPRODUCT_SPLIT_PERC
1223 WHERE organization_id = p_organization_id
1224 AND co_product_group_id = p_co_product_group_id
1225 AND effectivity_date = p_effectivity_date
1226 AND disable_date = p_disable_date;
1227
1228 RETURN FALSE;
1229
1230 END IF;
1231
1232 EXCEPTION
1233 WHEN TOO_MANY_ROWS THEN
1234 RETURN FALSE; /* when multiple co-products exist in def. */
1235
1236 WHEN NO_DATA_FOUND THEN
1237 RETURN TRUE;
1238 END;
1239
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 x_err_code := -1;
1243 x_err_msg := 'WSM_SPLIT_PERC_PVT.check_unique : ' || SQLCODE || substr(SQLERRM,1,1000);
1244 RETURN TRUE;
1245
1246 END check_unique;
1247
1248 END WSM_SPLIT_PERC_PVT;
[Home] [Help]