[Home] [Help]
PACKAGE BODY: APPS.WSMPVCPD
Source
1 PACKAGE BODY WSMPVCPD AS
2 /* $Header: WSMVCPDB.pls 115.8 2003/12/17 19:00:14 sthangad ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: val_co_product_related
7
8 ===========================================================================*/
9
10 PROCEDURE val_co_product_related (x_bill_sequence_id IN NUMBER,
11 x_result IN OUT NOCOPY VARCHAR2,
12 x_error_code IN OUT NOCOPY NUMBER,
13 x_error_msg IN OUT NOCOPY VARCHAR2)
14 IS
15
16 x_progress VARCHAR2(3) := NULL;
17
18 BEGIN
19
20 x_progress := '010';
21
22 -- commented out by abedajna on 10/12/00 for perf. tuning
23
24 /*
25 ** SELECT 'Y'
26 ** INTO x_result
27 ** FROM sys.dual
28 ** WHERE EXISTS (SELECT 1
29 ** FROM wsm_co_products bcp
30 ** WHERE bcp.bill_sequence_id = x_bill_sequence_id
31 ** AND sysdate >= bcp.effectivity_date
32 ** AND (sysdate <= bcp.disable_date
33 ** OR bcp.disable_date is NULL));
34 ** x_error_code := 0;
35 **
36 **
37 ** EXCEPTION
38 **
39 ** WHEN NO_DATA_FOUND THEN
40 ** x_result := 'N';
41 ** x_error_code := 0;
42 **
43 ** WHEN OTHERS THEN
44 ** x_error_code := sqlcode;
45 ** x_error_msg := 'WSMPVCPD.val_co_product_related(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
46 */
47 -- modification begin for perf. tuning.. abedajna 10/12/00
48
49 SELECT 'Y'
50 INTO x_result
51 FROM wsm_co_products bcp
52 WHERE bcp.bill_sequence_id = x_bill_sequence_id
53 AND sysdate >= bcp.effectivity_date
54 AND (sysdate <= bcp.disable_date
55 OR bcp.disable_date is NULL);
56
57 x_error_code := 0;
58
59 EXCEPTION
60 WHEN NO_DATA_FOUND THEN
61 x_result := 'N';
62 x_error_code := 0;
63
64 WHEN TOO_MANY_ROWS THEN
65 x_result := 'Y';
66 x_error_code := 0;
67
68 WHEN OTHERS THEN
69 x_error_code := sqlcode;
70 x_error_msg := 'WSMPVCPD.val_co_product_related(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
71
72 -- modification end for perf. tuning.. abedajna 10/12/00
73
74
75 END val_co_product_related;
76
77
78 /* ==========================================================================
79
80 PROCEDURE NAME: val_co_product
81
82 =========================================================================== */
83
84 PROCEDURE val_co_product(x_rowid IN VARCHAR2,
85 x_co_product_group_id IN NUMBER,
86 x_co_product_id IN NUMBER,
87 x_error_code IN OUT NOCOPY NUMBER,
88 x_error_msg IN OUT NOCOPY VARCHAR2)
89 IS
90
91 x_progress VARCHAR2(3) := NULL;
92 e_val_co_product EXCEPTION;
93 e_bom_inequality EXCEPTION;
94 x_dummy NUMBER := NULL;
95 x1_dummy NUMBER; --abedajna
96
97 BEGIN
98
99 x_progress := '010';
100
101 /* Verify that the required arguments are being passed in. */
102
103 IF ((x_co_product_group_id is NULL) OR (x_co_product_id is NULL)) THEN
104 raise e_val_co_product;
105
106 END IF;
107
108 x_progress := '020';
109
110
111 SELECT 1
112 INTO x_dummy
113 FROM sys.dual
114 WHERE NOT EXISTS (SELECT 1
115 FROM wsm_co_products bcp
116 WHERE bcp.co_product_group_id = x_co_product_group_id
117 AND bcp.co_product_id = x_co_product_id
118 AND ((rowid <> X_Rowid) OR (X_Rowid IS NULL)));
119
120
121 x_progress := '030';
122
123 x_dummy := NULL;
124
125 BEGIN
126
127 -- commented out by abedajna on 10/12/00 for perf. tuning
128 /*
129 ** SELECT 1
130 ** INTO x_dummy
131 ** FROM sys.dual
132 ** WHERE NOT EXISTS (SELECT 1
133 ** FROM wsm_co_prod_comp_substitutes bcs
134 ** WHERE bcs.co_product_group_id = x_co_product_group_id
135 ** AND bcs.substitute_component_id = x_co_product_id);
136 **
137 ** EXCEPTION
138 ** WHEN NO_DATA_FOUND THEN
139 ** raise e_bom_inequality;
140 */
141
142 -- modification begin for perf. tuning.. abedajna 10/12/00
143
144 x1_dummy := 0;
145
146 SELECT 1
147 INTO x1_dummy
148 FROM wsm_co_prod_comp_substitutes bcs
149 WHERE bcs.co_product_group_id = x_co_product_group_id
150 AND bcs.substitute_component_id = x_co_product_id;
151
152 IF x1_dummy <> 0 THEN
153 raise e_bom_inequality;
154 END IF;
155
156
157 EXCEPTION
158 WHEN TOO_MANY_ROWS THEN
159 raise e_bom_inequality;
160
161 WHEN NO_DATA_FOUND THEN
162 NULL;
163
164
165 -- modification end for perf. tuning.. abedajna 10/12/00
166
167 END;
168
169 x_error_code := 0;
170
171 EXCEPTION
172 WHEN e_bom_inequality THEN
173 x_error_code := 3;
174 x_error_msg := 'Co Product Item may not be same as component''s substitute item.';
175 WHEN e_val_co_product THEN
176 x_error_code := 1;
177 x_error_msg := 'Insufficient arguments to WSMPVCPD.val_co_product';
178 WHEN NO_DATA_FOUND THEN
179 /* DEBUG: Replace with message from message dictionary. */
180 x_error_msg := 'This is a duplicate co-product';
181 x_error_code := 2;
182 WHEN OTHERS THEN
183 x_error_code := sqlcode;
184 x_error_msg := 'WSMPVCPD.val_co_product(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
185
186
187 END val_co_product;
188
189 /* ==========================================================================
190
191 PROCEDURE NAME: val_substitute_coproduct
192
193 =========================================================================== */
194
195 PROCEDURE val_substitute_coproduct(x_substitute_co_product_id IN NUMBER,
196 x_co_product_group_id IN NUMBER,
197 x_co_product_id IN NUMBER,
198 x_error_code IN OUT NOCOPY NUMBER,
199 x_error_msg IN OUT NOCOPY VARCHAR2)
200 IS
201
202 x_progress VARCHAR2(3) := NULL;
203 e_invalid_substitute EXCEPTION;
204 e_same_substitute EXCEPTION;
205
206 x_dummy NUMBER := NULL;
207
208 BEGIN
209
210 x_progress := '010';
211
212 IF (x_co_product_id = x_substitute_co_product_id) THEN
213 raise e_same_substitute;
214
215 END IF;
216
217 BEGIN
218
219 -- commented out by abedajna on 10/12/00 for perf. tuning
220 /*
221 ** SELECT 1
222 ** INTO x_dummy
223 ** FROM sys.dual
224 ** WHERE EXISTS (SELECT 1
225 ** FROM wsm_co_products bcp
226 ** WHERE bcp.co_product_group_id = x_co_product_group_id
227 ** AND bcp.co_product_id = x_substitute_co_product_id);
228 **
229 ** EXCEPTION
230 ** WHEN NO_DATA_FOUND THEN
231 ** raise e_invalid_substitute;
232 */
233 -- modification begin for perf. tuning.. abedajna 10/12/00
234
235 SELECT 1
236 INTO x_dummy
237 FROM wsm_co_products bcp
238 WHERE bcp.co_product_group_id = x_co_product_group_id
239 AND bcp.co_product_id = x_substitute_co_product_id;
240
241 EXCEPTION
242 WHEN NO_DATA_FOUND THEN
243 raise e_invalid_substitute;
244
245 WHEN TOO_MANY_ROWS THEN
246 x_dummy := 1;
247
248
249 -- modification end for perf. tuning.. abedajna 10/12/00
250
251
252 END;
253
254 x_error_code := 0;
255
256 EXCEPTION
257 WHEN e_same_substitute THEN
258 x_error_code := 2;
259 x_error_msg := 'Co-Product Substitute Item cannot be the same as the Co-Product Item.';
260 WHEN e_invalid_substitute THEN
261 x_error_code := 1;
262 x_error_msg := 'Co-Product Substitute Item does not exist in the Co-Product relationship.';
263 WHEN OTHERS THEN
264 x_error_code := sqlcode;
265 x_error_msg := 'WSMPVCPD.val_substitute_coproduct(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
266 END val_substitute_coproduct;
267
268
269 /*===========================================================================
270
271 PROCEDURE NAME: val_pre_commit
272
273 ===========================================================================*/
274
275 PROCEDURE val_pre_commit(x_co_product_group_id IN NUMBER,
276 x_error_code IN OUT NOCOPY NUMBER,
277 x_error_msg IN OUT NOCOPY VARCHAR2)
278 IS
279
280 x_progress VARCHAR2(3) := NULL;
281 e_proc_exception EXCEPTION;
282
283 BEGIN
284
285 x_progress := '010';
286
287 /*
288 Do not continue if the co_product_group_id
289 has not been provided.
290 */
291
292 IF (x_co_product_group_id is NULL) THEN
293 x_error_code := 0;
294 return;
295
296 END IF;
297
298 WSMPVCPD.val_primary_flag(x_co_product_group_id,
299 x_error_code,
300 x_error_msg);
301
302 IF (x_error_code = 2) THEN
303 return;
304
305 ELSIF (x_error_code <> 0) THEN
306 raise e_proc_exception;
307
308 END IF;
309
310
311 WSMPVCPD.val_split_total (x_co_product_group_id,
312 x_error_code,
313 x_error_msg);
314
315 IF (x_error_code = 2) THEN
316 return;
317
318 ELSIF (x_error_code <> 0) THEN
319 raise e_proc_exception;
320
321 END IF;
322
323 x_error_code := 0;
324
325 EXCEPTION
326 WHEN e_proc_exception THEN
327 x_error_msg := x_error_msg || ' - ' || 'WSMPVCPD.val_pre_commit('||x_progress||')';
328
329 WHEN OTHERS THEN
330 x_error_code := sqlcode;
331 x_error_msg := 'WSMPVCPD.val_pre_commit(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
332 END val_pre_commit;
333
334
335 /*===========================================================================
336
337 PROCEDURE NAME: val_primary_flag
338
339 ===========================================================================*/
340
341 PROCEDURE val_primary_flag(x_co_product_group_id IN NUMBER,
342 x_error_code IN OUT NOCOPY NUMBER,
343 x_error_msg IN OUT NOCOPY VARCHAR2)
344 IS
345
346 x_progress VARCHAR2(3) := NULL;
347 e_val_primary_flag EXCEPTION;
348 x_dummy NUMBER := NULL;
349
350 BEGIN
351
352 x_progress := '010';
353
354 /* Verify that the required arguments are being passed in. */
355 IF (x_co_product_group_id is NULL) THEN
356 raise e_val_primary_flag;
357
358 END IF;
359
360 x_progress := '020';
361
362 -- commented out by abedajna on 10/12/00 for perf. tuning
363 /*
364 ** SELECT 1
365 ** INTO x_dummy
366 ** FROM sys.dual
367 ** WHERE EXISTS (SELECT 1
368 ** FROM wsm_co_products bcp
369 ** WHERE bcp.co_product_group_id = x_co_product_group_id
370 ** AND bcp.primary_flag = 'Y'
371 ** AND bcp.co_product_id is not NULL);
372 **
373 ** x_error_code := 0;
374 **
375 **EXCEPTION
376 ** WHEN e_val_primary_flag THEN
377 ** x_error_code := 1;
378 ** x_error_msg := 'Insufficient arguments to WSMPVCPD.val_primary_flag';
379 ** WHEN NO_DATA_FOUND THEN */
380 /* DEBUG: Replace with message from message dictionary. */
381 /* x_error_msg := 'You must choose one of the co-products to be a primary co-product';
382 ** x_error_code := 2;
383 ** WHEN OTHERS THEN
384 ** x_error_code := sqlcode;
385 ** x_error_msg := 'WSMPVCPD.val_primary_flag(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
386 */
387
388 -- modification begin for perf. tuning.. abedajna 10/12/00
389
390 SELECT 1
391 INTO x_dummy
392 FROM wsm_co_products bcp
393 WHERE bcp.co_product_group_id = x_co_product_group_id
394 AND bcp.primary_flag = 'Y'
395 AND bcp.co_product_id is not NULL;
396
397 x_error_code := 0;
398
399 EXCEPTION
400
401 WHEN TOO_MANY_ROWS THEN
402 x_dummy := 1;
403 x_error_code := 0;
404
405 WHEN e_val_primary_flag THEN
406 x_error_code := 1;
407 x_error_msg := 'Insufficient arguments to WSMPVCPD.val_primary_flag';
408
409 WHEN NO_DATA_FOUND THEN
410 /* DEBUG: Replace with message from message dictionary. */
411 x_error_msg := 'You must choose one of the co-products to be a primary co-product';
412 x_error_code := 2;
413 WHEN OTHERS THEN
414 x_error_code := sqlcode;
415 x_error_msg := 'WSMPVCPD.val_primary_flag(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
416
417
418 -- modification end for perf. tuning.. abedajna 10/12/00
419
420
421 END val_primary_flag;
422
423 /* ===========================================================================
424
425 PROCEDURE NAME: val_split_total
426
427 =========================================================================== */
428
429 PROCEDURE val_split_total(x_co_product_group_id IN NUMBER,
430 x_error_code IN OUT NOCOPY NUMBER,
431 x_error_msg IN OUT NOCOPY VARCHAR2)
432 IS
433
434 x_progress VARCHAR2(3) := NULL;
435 e_val_split_total EXCEPTION;
436 e_invalid_split_total EXCEPTION;
437 x_split_total NUMBER := NULL;
438
439 CURSOR c_total is select distinct effectivity_date
440 from WSM_COPRODUCT_SPLIT_PERC
441 where co_product_group_id=x_co_product_group_id;
442
443 BEGIN
444
445 x_progress := '010';
446
447 /* Verify that the required arguments are being passed in. */
448
449 IF (x_co_product_group_id is NULL) THEN
450 raise e_val_split_total;
451 END IF;
452
453 x_progress := '020';
454
455 /* ST : coproducts time phased split enhancement begin */
456 /*SELECT sum (nvl(bcp.split,0))
457 INTO x_split_total
458 FROM wsm_co_products bcp
459 WHERE bcp.co_product_group_id = x_co_product_group_id
460 AND bcp.co_product_id is not NULL; */
461
462 /* Look into split percentages table to validate */
463 FOR e_rec IN c_total LOOP
464
465 SELECT sum (nvl(bcp.split,0))
466 INTO x_split_total
467 FROM wsm_coproduct_split_perc bcp
468 WHERE bcp.co_product_group_id = x_co_product_group_id
469 AND effectivity_date = e_rec.effectivity_date;
470
471 IF (x_split_total <> 100) THEN
472 raise e_invalid_split_total;
473 END IF;
474
475 END LOOP;
476
477 /* ST : coproducts time phased split enhancement end */
478
479 x_error_code := 0;
480
481 EXCEPTION
482 WHEN e_val_split_total THEN
483 x_error_code := 1;
484 x_error_msg := 'Insufficient arguments to WSMPVCPD.val_primary_flag';
485 WHEN e_invalid_split_total THEN
486 x_error_code := 2;
487 x_error_msg := 'The split percentage of the co-product lines should add up to 100.';
488 WHEN OTHERS THEN
489 x_error_code := sqlcode;
490 x_error_msg := 'WSMPVCPD.val_primary_flag(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
491 END val_split_total;
492
493 /* ===========================================================================
494
495 PROCEDURE NAME: val_add_to_bill
496
497 =========================================================================== */
498
499 PROCEDURE val_add_to_bill ( x_co_product_group_id IN NUMBER,
500 x_org_id IN NUMBER,
501 x_co_product_id IN NUMBER,
502 x_comm_bill_sequence_id IN NUMBER,
503 x_curr_bill_sequence_id IN NUMBER,
504 x_effectivity_date IN DATE,
505 x_disable_date IN DATE,
506 x_alternate_designator IN VARCHAR2,
507 x_error_code IN OUT NOCOPY NUMBER,
508 x_error_msg IN OUT NOCOPY VARCHAR2) IS
509
510 CURSOR C (x_bill_seq_id NUMBER) IS
511 SELECT 1
512 FROM sys.dual
513 WHERE EXISTS (SELECT 1
514 FROM bom_inventory_components bic
515 WHERE bic.bill_sequence_id = x_bill_seq_id
516 AND ((x_disable_date is NULL)
517 OR (x_disable_date > bic.effectivity_date))
518 AND ((x_effectivity_date < bic.disable_date)
519 OR (bic.disable_date IS NULL))
520 );
521
522 x_active_link NUMBER := NULL;
523 x_dummy NUMBER := NULL;
524 x_progress VARCHAR2(3) := NULL;
525
526 e_val_exception EXCEPTION;
527 e_proc_exception EXCEPTION;
528
529 BEGIN
530
531
532 IF (x_comm_bill_sequence_id <> x_curr_bill_sequence_id) THEN /* Common bill */
533 x_progress := '020';
534
535 SELECT count(1)
536 INTO x_active_link
537 FROM wsm_co_products bcp
538 WHERE bcp.bill_sequence_id = x_comm_bill_sequence_id
539 AND ( bcp.disable_date is NULL
540 OR bcp.disable_date > sysdate)
541 AND bcp.co_product_group_id <> x_co_product_group_id;
542
543 IF (x_active_link is NOT NULL) THEN
544 raise e_val_exception;
545
546 ELSE /* OK to update the common link */
547
548 /* -- Lock corresponding bill prior to update. */
549
550 WSMPPCPD.lock_bill (x_curr_bill_sequence_id,
551 x_error_code,
552 x_error_msg);
553
554 IF (x_error_code > 0) THEN
555 return;
556
557 ELSIF (x_error_code < 0) THEN
558 raise e_proc_exception;
559
560 END IF;
561
562 UPDATE bom_bill_of_materials
563 SET common_assembly_item_id = NULL,
564 common_organization_id = NULL,
565 common_bill_sequence_id = NULL
566 WHERE bill_sequence_id = x_curr_bill_sequence_id;
567
568 END IF;
569
570 ELSE /* Not a common bill */
571 x_progress := '040';
572
573 /* -- Verify that there aren't any overlapping
574 -- components. */
575
576 OPEN C (x_curr_bill_sequence_id);
577 FETCH C INTO x_dummy;
578 IF (C%NOTFOUND) THEN
579 NULL;
580
581 ELSE
582 raise e_val_exception;
583
584 END IF;
585 CLOSE C;
586
587 END IF;
588
589 x_error_code := 0;
590
591 EXCEPTION
592 WHEN e_proc_exception THEN
593 x_error_msg := x_error_msg || ' - ' || 'WSMPVCPD.val_add_to_bill('||x_progress||')';
594
595 WHEN e_val_exception THEN
596 x_error_code := 3;
597 IF (x_alternate_designator is NOT NULL) THEN
598 x_error_msg := 'Please provide another alternate designator. Cannot add co-product to this alternate bill.';
599 ELSE
600 x_error_msg := x_progress || 'This co-product may not be used as a primary co-product. Please use another co-product as the primary co-product.';
601 END IF;
602
603 WHEN OTHERS THEN
604 x_error_code := sqlcode;
605 x_error_msg := 'WSMPVCPD.val_add_to_bill(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
606 END val_add_to_bill;
607
608 /* ===========================================================================
609
610 PROCEDURE NAME: val_component_overlap
611
612 =========================================================================== */
613
614 PROCEDURE val_component_overlap (x_org_id IN NUMBER,
615 x_component_id IN NUMBER,
616 x_effectivity_date IN DATE,
617 x_disable_date IN DATE,
618 x_rowid IN VARCHAR2,
619 x_error_code IN OUT NOCOPY NUMBER,
620 x_error_msg IN OUT NOCOPY VARCHAR2)
621 IS
622
623 x_progress VARCHAR2(3) := NULL;
624 x_dummy NUMBER := NULL;
625 ex_dupl_comp EXCEPTION; -- abedajna
626
627 BEGIN
628
629 x_progress := '010';
630
631 x_dummy := 0;
632
633 SELECT 1
634 INTO x_dummy
635 FROM wsm_co_products bcp
636 WHERE bcp.organization_id = x_org_id
637 AND bcp.component_id = x_component_id
638 AND (x_disable_date is NULL
639 OR (x_disable_date > bcp.effectivity_date))
640 AND ((x_effectivity_date < bcp.disable_date)
641 OR bcp.disable_date is NULL)
642 AND ((bcp.rowid <> X_rowid) OR (X_rowid is NULL))
643 AND bcp.co_product_id is NULL;
644
645 IF x_dummy <> 0 THEN
646 RAISE ex_dupl_comp;
647 END IF;
648
649 x_error_code := 0;
650
651 EXCEPTION
652
653 WHEN NO_DATA_FOUND THEN
654 NULL;
655
656 WHEN ex_dupl_comp THEN
657 /* DEBUG: Replace with message from message dictionary. */
658 x_error_msg := 'This is a duplicate component. It failed the effectivity check';
659 x_error_code := 1;
660
661 WHEN TOO_MANY_ROWS THEN
662 /* DEBUG: Replace with message from message dictionary. */
663 x_error_msg := 'This is a duplicate component. It failed the effectivity check';
664 x_error_code := 1;
665
666 WHEN OTHERS THEN
667 x_error_code := sqlcode;
668 x_error_msg := 'WSMPVCPD.val_component_overlap(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
669
670
671 -- modification end for perf. tuning.. abedajna 10/12/00
672
673
674
675 END val_component_overlap;
676
677 END WSMPVCPD;