1 PACKAGE BODY GMD_FORMULATION_SPECS_PKG AS
2 /* $Header: GMDFSTHB.pls 120.3 2006/02/07 02:50:48 srsriran noship $ */
3
4 /* Formulation Specification - Table Handlers */
5
6 PROCEDURE INSERT_FORMULATION_SPEC(
7 X_ROWID OUT NOCOPY VARCHAR2 ,
8 X_FORMULATION_SPEC_ID IN NUMBER ,
9 X_SPEC_VERS IN NUMBER ,
10 X_PRODUCT_ID IN NUMBER ,
11 X_OWNER_ORGANIZATION_ID IN NUMBER ,
12 X_SPEC_STATUS IN VARCHAR2 ,
13 X_STD_QTY IN NUMBER ,
14 X_STD_UOM IN VARCHAR2 ,
15 X_PROCESS_LOSS IN NUMBER ,
16 X_START_DATE IN DATE ,
17 X_END_DATE IN DATE ,
18 X_MIN_INGREDS IN NUMBER ,
19 X_MAX_INGREDS IN NUMBER ,
20 X_INGRED_PICK_BASE_IND IN VARCHAR2 ,
21 X_PICK_LOT_STRATEGY IN VARCHAR2 ,
22 X_TECH_PARM_ID IN NUMBER ,
23 X_OBJECTIVE_IND IN NUMBER ,
24 X_ROUTING_ID IN NUMBER ,
25 X_SPEC_NAME IN VARCHAR2 ,
26 X_TEXT_CODE IN VARCHAR2 ,
27 X_DELETE_MARK IN NUMBER ,
28 X_CREATION_DATE IN DATE ,
29 X_CREATED_BY IN NUMBER ,
30 X_LAST_UPDATE_DATE IN DATE ,
31 X_LAST_UPDATED_BY IN NUMBER ,
32 X_LAST_UPDATE_LOGIN IN NUMBER
33 ) IS
34
35 CURSOR C IS
36 SELECT ROWID
37 FROM GMD_FORMULATION_SPECS
38 WHERE FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID;
39
40 BEGIN
41 INSERT INTO GMD_FORMULATION_SPECS (
42 FORMULATION_SPEC_ID,
43 SPEC_VERS,
44 SPEC_NAME,
45 PRODUCT_ID,
46 OWNER_ORGANIZATION_ID,
47 SPEC_STATUS,
48 STD_QTY,
49 STD_UOM,
50 PROCESS_LOSS,
51 START_DATE,
52 END_DATE,
53 MIN_INGREDS,
54 MAX_INGREDS,
55 INGRED_PICK_BASE_IND,
56 PICK_LOT_STRATEGY,
57 TECH_PARM_ID,
58 OBJECTIVE_IND,
59 ROUTING_ID,
60 TEXT_CODE,
61 DELETE_MARK,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) VALUES (
68 X_FORMULATION_SPEC_ID,
69 X_SPEC_VERS,
70 X_SPEC_NAME,
71 X_PRODUCT_ID,
72 X_OWNER_ORGANIZATION_ID,
73 X_SPEC_STATUS,
74 X_STD_QTY,
75 X_STD_UOM,
76 X_PROCESS_LOSS,
77 X_START_DATE,
78 X_END_DATE,
79 X_MIN_INGREDS,
80 X_MAX_INGREDS,
81 X_INGRED_PICK_BASE_IND,
82 X_PICK_LOT_STRATEGY,
83 X_TECH_PARM_ID,
84 X_OBJECTIVE_IND,
85 X_ROUTING_ID,
86 X_TEXT_CODE,
87 X_DELETE_MARK,
88 X_CREATION_DATE,
89 X_CREATED_BY,
90 X_LAST_UPDATE_DATE,
91 X_LAST_UPDATED_BY,
92 X_LAST_UPDATE_LOGIN
93 );
94
95 OPEN C;
96 FETCH C INTO X_ROWID;
97 IF (C%NOTFOUND) THEN
98 CLOSE C;
99 RAISE NO_DATA_FOUND;
100 END IF;
101 CLOSE C;
102
103 END INSERT_FORMULATION_SPEC;
104
105
106 PROCEDURE LOCK_FORMULATION_SPEC (
107 X_FORMULATION_SPEC_ID IN NUMBER ,
108 X_SPEC_VERS IN NUMBER ,
109 X_PRODUCT_ID IN NUMBER ,
110 X_OWNER_ORGANIZATION_ID IN NUMBER ,
111 X_SPEC_STATUS IN VARCHAR2 ,
112 X_STD_QTY IN NUMBER ,
113 X_STD_UOM IN VARCHAR2 ,
114 X_PROCESS_LOSS IN NUMBER ,
115 X_START_DATE IN DATE ,
116 X_END_DATE IN DATE ,
117 X_MIN_INGREDS IN NUMBER ,
118 X_MAX_INGREDS IN NUMBER ,
119 X_INGRED_PICK_BASE_IND IN VARCHAR2 ,
120 X_PICK_LOT_STRATEGY IN VARCHAR2 ,
121 X_TECH_PARM_ID IN NUMBER ,
122 X_OBJECTIVE_IND IN NUMBER ,
123 X_TEXT_CODE IN VARCHAR2 ,
124 X_DELETE_MARK IN NUMBER ,
125 X_SPEC_NAME IN VARCHAR2
126 ) IS
127 CURSOR C IS
128 SELECT
129 SPEC_VERS,
130 PRODUCT_ID,
131 OWNER_ORGANIZATION_ID,
132 SPEC_STATUS,
133 STD_QTY,
134 STD_UOM,
135 PROCESS_LOSS,
136 START_DATE,
137 END_DATE,
138 MIN_INGREDS,
139 MAX_INGREDS,
140 INGRED_PICK_BASE_IND,
141 PICK_LOT_STRATEGY,
142 TECH_PARM_ID,
143 OBJECTIVE_IND,
144 TEXT_CODE,
145 DELETE_MARK
146 FROM GMD_FORMULATION_SPECS
147 WHERE FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID
148 FOR UPDATE OF FORMULATION_SPEC_ID NOWAIT;
149 RECINFO C%ROWTYPE;
150
151 BEGIN
152
153 OPEN C;
154 FETCH C INTO RECINFO;
155 IF (C%NOTFOUND) THEN
156 CLOSE C;
157 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
158 APP_EXCEPTION.RAISE_EXCEPTION;
159 END IF;
160 CLOSE C;
161 IF ( (RECINFO.SPEC_VERS = X_SPEC_VERS)
162 AND (RECINFO.PRODUCT_ID = X_PRODUCT_ID)
163 AND (RECINFO.OWNER_ORGANIZATION_ID = X_OWNER_ORGANIZATION_ID)
164 AND (RECINFO.SPEC_STATUS = X_SPEC_STATUS)
165 AND (RECINFO.STD_QTY = X_STD_QTY)
166 AND (RECINFO.STD_UOM = X_STD_UOM)
167 AND ((RECINFO.PROCESS_LOSS = X_PROCESS_LOSS)
168 OR ((RECINFO.PROCESS_LOSS IS NULL) AND (X_PROCESS_LOSS IS NULL)))
169 AND (RECINFO.START_DATE = X_START_DATE)
170 AND ((RECINFO.END_DATE = X_END_DATE)
171 OR ((RECINFO.END_DATE IS NULL) AND (X_END_DATE IS NULL)))
172 AND ((RECINFO.MIN_INGREDS = X_MIN_INGREDS)
173 OR ((RECINFO.MIN_INGREDS IS NULL) AND (X_MIN_INGREDS IS NULL)))
174 AND ((RECINFO.MAX_INGREDS = X_MAX_INGREDS)
175 OR ((RECINFO.MAX_INGREDS IS NULL) AND (X_MAX_INGREDS IS NULL)))
176 AND (RECINFO.INGRED_PICK_BASE_IND = X_INGRED_PICK_BASE_IND)
177 AND ((RECINFO.PICK_LOT_STRATEGY = X_PICK_LOT_STRATEGY)
178 OR ((RECINFO.PICK_LOT_STRATEGY IS NULL) AND (X_PICK_LOT_STRATEGY IS NULL)))
179 AND ((RECINFO.DELETE_MARK = X_DELETE_MARK)
180 OR ((RECINFO.DELETE_MARK IS NULL) AND (X_DELETE_MARK IS NULL)))
181 AND ((RECINFO.TEXT_CODE = X_TEXT_CODE)
182 OR ((RECINFO.TEXT_CODE IS NULL) AND (X_TEXT_CODE IS NULL)))
183 AND (RECINFO.TECH_PARM_ID = X_TECH_PARM_ID)
184 AND (RECINFO.OBJECTIVE_IND = X_OBJECTIVE_IND)
185 ) THEN
186 NULL;
187 ELSE
188 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
189 APP_EXCEPTION.RAISE_EXCEPTION;
190 END IF;
191 RETURN;
192 END LOCK_FORMULATION_SPEC;
193
194 PROCEDURE UPDATE_FORMULATION_SPEC (
195 X_FORMULATION_SPEC_ID IN NUMBER ,
196 X_PRODUCT_ID IN NUMBER ,
197 X_OWNER_ORGANIZATION_ID IN NUMBER ,
198 X_SPEC_STATUS IN VARCHAR2 ,
199 X_STD_QTY IN NUMBER ,
200 X_STD_UOM IN VARCHAR2 ,
201 X_PROCESS_LOSS IN NUMBER ,
202 X_START_DATE IN DATE ,
203 X_END_DATE IN DATE ,
204 X_MIN_INGREDS IN NUMBER ,
205 X_MAX_INGREDS IN NUMBER ,
206 X_INGRED_PICK_BASE_IND IN VARCHAR2 ,
207 X_PICK_LOT_STRATEGY IN VARCHAR2 ,
208 X_TECH_PARM_ID IN NUMBER ,
209 X_OBJECTIVE_IND IN NUMBER ,
210 X_TEXT_CODE IN VARCHAR2 ,
211 X_DELETE_MARK IN NUMBER ,
212 X_LAST_UPDATE_DATE IN DATE ,
213 X_LAST_UPDATED_BY IN NUMBER ,
214 X_LAST_UPDATE_LOGIN IN NUMBER
215 ) IS
216
217 BEGIN
218
219 UPDATE GMD_FORMULATION_SPECS
220 SET
221 PRODUCT_ID = X_PRODUCT_ID,
222 OWNER_ORGANIZATION_ID = X_OWNER_ORGANIZATION_ID,
223 SPEC_STATUS = X_SPEC_STATUS,
224 STD_QTY = X_STD_QTY,
225 STD_UOM = X_STD_UOM,
226 PROCESS_LOSS = X_PROCESS_LOSS,
227 START_DATE = X_START_DATE,
228 END_DATE = X_END_DATE,
229 MIN_INGREDS = X_MIN_INGREDS,
230 MAX_INGREDS = X_MAX_INGREDS,
231 INGRED_PICK_BASE_IND = X_INGRED_PICK_BASE_IND,
232 PICK_LOT_STRATEGY = X_PICK_LOT_STRATEGY,
233 TECH_PARM_ID = X_TECH_PARM_ID,
234 OBJECTIVE_IND = X_OBJECTIVE_IND,
235 DELETE_MARK = X_DELETE_MARK,
236 TEXT_CODE = X_TEXT_CODE,
237 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
238 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
239 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
240 WHERE FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID;
241
242 IF (SQL%NOTFOUND) THEN
243 RAISE NO_DATA_FOUND;
244 END IF;
245
246 END UPDATE_FORMULATION_SPEC;
247
248 PROCEDURE DELETE_FORMULATION_SPEC (
249 X_FORMULATION_SPEC_ID IN NUMBER
250 ) IS
251 BEGIN
252
253 DELETE FROM GMD_FORMULATION_SPECS
254 WHERE FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID;
255
256 IF (SQL%NOTFOUND) THEN
257 RAISE NO_DATA_FOUND;
258 END IF;
259
260 END DELETE_FORMULATION_SPEC;
261
262 /* END - Formulation Specification - Table Handlers */
263
264
265 /* Material Req - Table Handlers */
266
267 PROCEDURE INSERT_MATERIAL_REQ (
268 X_ROWID OUT NOCOPY VARCHAR2 ,
269 X_MATL_REQ_ID IN NUMBER ,
270 X_FORMULATION_SPEC_ID IN NUMBER ,
271 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
272 X_LINE_NO IN NUMBER ,
273 X_INVENTORY_ITEM_ID IN NUMBER ,
274 X_ITEM_UOM IN VARCHAR2 ,
275 X_MIN_QTY IN NUMBER ,
276 X_MAX_QTY IN NUMBER ,
277 X_RANGE_TYPE IN NUMBER ,
278 X_CREATION_DATE IN DATE ,
279 X_CREATED_BY IN NUMBER ,
280 X_LAST_UPDATE_DATE IN DATE ,
281 X_LAST_UPDATED_BY IN NUMBER ,
282 X_LAST_UPDATE_LOGIN IN NUMBER
283 ) IS
284
285 CURSOR C IS
286 SELECT ROWID
287 FROM GMD_MATERIAL_REQS
288 WHERE MATL_REQ_ID = X_MATL_REQ_ID;
289
290 BEGIN
291
292 INSERT INTO GMD_MATERIAL_REQS (
293 FORMULATION_SPEC_ID,
294 MATL_REQ_ID,
295 SPEC_ATTRIBUTE_ID,
296 LINE_NO,
297 INVENTORY_ITEM_ID,
298 ITEM_UOM,
299 MIN_QTY,
300 MAX_QTY,
301 RANGE_TYPE,
302 CREATION_DATE,
303 CREATED_BY,
304 LAST_UPDATE_DATE,
305 LAST_UPDATED_BY,
306 LAST_UPDATE_LOGIN
307 ) VALUES (
308 X_FORMULATION_SPEC_ID,
309 X_MATL_REQ_ID,
310 X_SPEC_ATTRIBUTE_ID,
311 X_LINE_NO,
312 X_INVENTORY_ITEM_ID,
313 X_ITEM_UOM,
314 X_MIN_QTY,
315 X_MAX_QTY,
316 X_RANGE_TYPE,
317 X_CREATION_DATE,
318 X_CREATED_BY,
319 X_LAST_UPDATE_DATE,
320 X_LAST_UPDATED_BY,
321 X_LAST_UPDATE_LOGIN
322 );
323
324 OPEN C;
325 FETCH C INTO X_ROWID;
326 IF (C%NOTFOUND) THEN
327 CLOSE C;
328 RAISE NO_DATA_FOUND;
329 END IF;
330 CLOSE C;
331
332 END INSERT_MATERIAL_REQ;
333
334 PROCEDURE LOCK_MATERIAL_REQ (
335 X_MATL_REQ_ID IN NUMBER ,
336 X_FORMULATION_SPEC_ID IN NUMBER ,
337 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
338 X_LINE_NO IN NUMBER ,
339 X_INVENTORY_ITEM_ID IN NUMBER ,
340 X_ITEM_UOM IN VARCHAR2 ,
341 X_MIN_QTY IN NUMBER ,
342 X_MAX_QTY IN NUMBER ,
343 X_RANGE_TYPE IN NUMBER
344 ) IS
345
346 CURSOR C IS
347 SELECT
348 FORMULATION_SPEC_ID,
349 SPEC_ATTRIBUTE_ID,
350 LINE_NO,
351 INVENTORY_ITEM_ID,
352 ITEM_UOM,
353 MIN_QTY,
354 MAX_QTY
355 FROM GMD_MATERIAL_REQS
356 WHERE MATL_REQ_ID = X_MATL_REQ_ID
357 FOR UPDATE OF MATL_REQ_ID NOWAIT;
358 RECINFO C%ROWTYPE;
359
360 BEGIN
361
362 OPEN C;
363 FETCH C INTO RECINFO;
364 IF (C%NOTFOUND) THEN
365 CLOSE C;
366 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
367 APP_EXCEPTION.RAISE_EXCEPTION;
368 END IF;
369 CLOSE C;
370 IF ( (RECINFO.FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID)
371 AND (RECINFO.SPEC_ATTRIBUTE_ID = X_SPEC_ATTRIBUTE_ID)
372 AND (RECINFO.LINE_NO = X_LINE_NO)
373 AND (RECINFO.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
374 AND ((RECINFO.ITEM_UOM = X_ITEM_UOM)
375 OR ((RECINFO.ITEM_UOM IS NULL) AND (X_ITEM_UOM IS NULL)))
376 AND ((RECINFO.MIN_QTY = X_MIN_QTY)
377 OR ((RECINFO.MIN_QTY IS NULL) AND (X_MIN_QTY IS NULL)))
378 AND ((RECINFO.MAX_QTY = X_MAX_QTY)
379 OR ((RECINFO.MAX_QTY IS NULL) AND (X_MAX_QTY IS NULL)))
380 ) THEN
381 NULL;
382 ELSE
383 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
384 APP_EXCEPTION.RAISE_EXCEPTION;
385 END IF;
386
387 RETURN;
388 END LOCK_MATERIAL_REQ;
389
390 PROCEDURE UPDATE_MATERIAL_REQ (
391 X_MATL_REQ_ID IN NUMBER ,
392 X_FORMULATION_SPEC_ID IN NUMBER ,
393 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
394 X_LINE_NO IN NUMBER ,
395 X_INVENTORY_ITEM_ID IN NUMBER ,
396 X_ITEM_UOM IN VARCHAR2 ,
397 X_MIN_QTY IN NUMBER ,
398 X_MAX_QTY IN NUMBER ,
399 X_RANGE_TYPE IN NUMBER ,
400 X_LAST_UPDATE_DATE IN DATE ,
401 X_LAST_UPDATED_BY IN NUMBER ,
402 X_LAST_UPDATE_LOGIN IN NUMBER
403 ) IS
404
405 BEGIN
406
407 UPDATE GMD_MATERIAL_REQS
408 SET
409 FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID ,
410 SPEC_ATTRIBUTE_ID = X_SPEC_ATTRIBUTE_ID ,
411 LINE_NO = X_LINE_NO ,
412 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID ,
413 ITEM_UOM = X_ITEM_UOM ,
414 MIN_QTY = X_MIN_QTY ,
415 MAX_QTY = X_MAX_QTY ,
416 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE ,
417 LAST_UPDATED_BY = X_LAST_UPDATED_BY ,
418 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
419 WHERE MATL_REQ_ID = X_MATL_REQ_ID;
420
421 IF (SQL%NOTFOUND) THEN
422 RAISE NO_DATA_FOUND;
423 END IF;
424
425 END UPDATE_MATERIAL_REQ;
426
427 PROCEDURE DELETE_MATERIAL_REQ (
428 X_MATL_REQ_ID IN NUMBER
429 ) IS
430 BEGIN
431
432 DELETE FROM GMD_MATERIAL_REQS
433 WHERE MATL_REQ_ID = X_MATL_REQ_ID;
434
435 IF (SQL%NOTFOUND) THEN
436 RAISE NO_DATA_FOUND;
437 END IF;
438 END DELETE_MATERIAL_REQ;
439
440 /* END - Material Req - Table Handlers */
441
442
443 /* Compositional Req - Table Handlers */
444
445 PROCEDURE INSERT_COMPOSITIONAL_REQ (
446 X_ROWID OUT NOCOPY VARCHAR2 ,
447 X_COMP_REQ_ID IN NUMBER ,
448 X_FORMULATION_SPEC_ID IN NUMBER ,
449 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
450 X_MIN_PCT IN NUMBER ,
451 X_MAX_PCT IN NUMBER ,
452 X_CATEGORY_SET_ID IN NUMBER ,
453 X_CATEGORY_ID IN NUMBER ,
454 X_PLANNED_PCT IN NUMBER ,
455 X_ORDER_NO IN NUMBER ,
456 X_CREATION_DATE IN DATE ,
457 X_CREATED_BY IN NUMBER ,
458 X_LAST_UPDATE_DATE IN DATE ,
459 X_LAST_UPDATED_BY IN NUMBER ,
460 X_LAST_UPDATE_LOGIN IN NUMBER
461 ) IS
462
463 CURSOR C IS
464 SELECT ROWID
465 FROM GMD_COMPOSITIONAL_REQS
466 WHERE COMP_REQ_ID = X_COMP_REQ_ID;
467
468 BEGIN
469 INSERT INTO GMD_COMPOSITIONAL_REQS (
470 FORMULATION_SPEC_ID,
471 COMP_REQ_ID,
472 SPEC_ATTRIBUTE_ID,
473 ORDER_NO,
474 MIN_PCT,
475 MAX_PCT,
476 CATEGORY_SET_ID,
477 CATEGORY_ID,
478 PLANNED_PCT,
479 CREATION_DATE,
480 CREATED_BY,
481 LAST_UPDATE_DATE,
482 LAST_UPDATED_BY,
483 LAST_UPDATE_LOGIN
484 ) VALUES (
485 X_FORMULATION_SPEC_ID,
486 X_COMP_REQ_ID,
487 X_SPEC_ATTRIBUTE_ID,
488 X_ORDER_NO,
489 X_MIN_PCT,
490 X_MAX_PCT,
491 X_CATEGORY_SET_ID,
492 X_CATEGORY_ID,
493 X_PLANNED_PCT,
494 X_CREATION_DATE,
495 X_CREATED_BY,
496 X_LAST_UPDATE_DATE,
497 X_LAST_UPDATED_BY,
498 X_LAST_UPDATE_LOGIN
499 );
500
501 OPEN C;
502 FETCH C INTO X_ROWID;
503 IF (C%NOTFOUND) THEN
504 CLOSE C;
505 RAISE NO_DATA_FOUND;
506 END IF;
507 CLOSE C;
508
509 END INSERT_COMPOSITIONAL_REQ;
510
511 PROCEDURE LOCK_COMPOSITIONAL_REQ (
512 X_COMP_REQ_ID IN NUMBER ,
513 X_FORMULATION_SPEC_ID IN NUMBER ,
514 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
515 X_MIN_PCT IN NUMBER ,
516 X_MAX_PCT IN NUMBER ,
517 X_CATEGORY_SET_ID IN NUMBER ,
518 X_CATEGORY_ID IN NUMBER ,
519 X_PLANNED_PCT IN NUMBER ,
520 X_ORDER_NO IN NUMBER
521 ) IS
522 CURSOR C IS SELECT
523 FORMULATION_SPEC_ID,
524 SPEC_ATTRIBUTE_ID,
525 MIN_PCT,
526 MAX_PCT,
527 CATEGORY_SET_ID,
528 CATEGORY_ID,
529 PLANNED_PCT
530 FROM GMD_COMPOSITIONAL_REQS
531 WHERE COMP_REQ_ID = X_COMP_REQ_ID
532 FOR UPDATE OF COMP_REQ_ID NOWAIT;
533 RECINFO C%ROWTYPE;
534
535 BEGIN
536 OPEN C;
537 FETCH C INTO RECINFO;
538 IF (C%NOTFOUND) THEN
539 CLOSE C;
540 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
541 APP_EXCEPTION.RAISE_EXCEPTION;
542 END IF;
543 CLOSE C;
544 IF ( (RECINFO.FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID)
545 AND (RECINFO.SPEC_ATTRIBUTE_ID = X_SPEC_ATTRIBUTE_ID)
546 AND ((RECINFO.MIN_PCT = X_MIN_PCT)
547 OR ((RECINFO.MIN_PCT IS NULL) AND (X_MIN_PCT IS NULL)))
548 AND ((RECINFO.MAX_PCT = X_MAX_PCT)
549 OR ((RECINFO.MAX_PCT IS NULL) AND (X_MAX_PCT IS NULL)))
550 AND (RECINFO.CATEGORY_SET_ID = X_CATEGORY_SET_ID)
551 AND (RECINFO.CATEGORY_ID = X_CATEGORY_ID)
552 AND ((RECINFO.PLANNED_PCT = X_PLANNED_PCT)
553 OR ((RECINFO.PLANNED_PCT IS NULL) AND (X_PLANNED_PCT IS NULL)))
554 ) THEN
555 NULL;
556 ELSE
557 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
558 APP_EXCEPTION.RAISE_EXCEPTION;
559 END IF;
560
561 RETURN;
562 END LOCK_COMPOSITIONAL_REQ;
563
564 PROCEDURE UPDATE_COMPOSITIONAL_REQ (
565 X_COMP_REQ_ID IN NUMBER ,
566 X_FORMULATION_SPEC_ID IN NUMBER ,
567 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
568 X_MIN_PCT IN NUMBER ,
569 X_MAX_PCT IN NUMBER ,
570 X_CATEGORY_SET_ID IN NUMBER ,
571 X_CATEGORY_ID IN NUMBER ,
572 X_PLANNED_PCT IN NUMBER ,
573 X_ORDER_NO IN NUMBER ,
574 X_LAST_UPDATE_DATE IN DATE ,
575 X_LAST_UPDATED_BY IN NUMBER ,
576 X_LAST_UPDATE_LOGIN IN NUMBER
577 ) IS
578
579 BEGIN
580
581 UPDATE GMD_COMPOSITIONAL_REQS SET
582 FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID ,
583 SPEC_ATTRIBUTE_ID = X_SPEC_ATTRIBUTE_ID ,
584 MIN_PCT = X_MIN_PCT ,
585 MAX_PCT = X_MAX_PCT ,
586 CATEGORY_SET_ID = X_CATEGORY_SET_ID ,
587 CATEGORY_ID = X_CATEGORY_ID ,
588 PLANNED_PCT = X_PLANNED_PCT ,
589 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE ,
590 LAST_UPDATED_BY = X_LAST_UPDATED_BY ,
591 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
592 WHERE COMP_REQ_ID = X_COMP_REQ_ID;
593
594 IF (SQL%NOTFOUND) THEN
595 RAISE NO_DATA_FOUND;
596 END IF;
597
598 END UPDATE_COMPOSITIONAL_REQ;
599
600 PROCEDURE DELETE_COMPOSITIONAL_REQ (
601 X_COMP_REQ_ID IN NUMBER
602 ) IS
603 BEGIN
604
605 DELETE FROM GMD_COMPOSITIONAL_REQS
606 WHERE COMP_REQ_ID = X_COMP_REQ_ID;
607
608 IF (SQL%NOTFOUND) THEN
609 RAISE NO_DATA_FOUND;
610 END IF;
611
612 END DELETE_COMPOSITIONAL_REQ;
613
614 /* END - Compositional Req - Table Handlers */
615
616
617 /* Technical Req - Table Handlers */
618
619 PROCEDURE INSERT_TECHNICAL_REQ (
620 X_ROWID OUT NOCOPY VARCHAR2 ,
621 X_TECH_REQ_ID IN NUMBER ,
622 X_FORMULATION_SPEC_ID IN NUMBER ,
623 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
624 X_TECH_PARM_ID IN NUMBER ,
625 X_MIN_VALUE IN NUMBER ,
626 X_MAX_VALUE IN NUMBER ,
627 X_CREATION_DATE IN DATE ,
628 X_CREATED_BY IN NUMBER ,
629 X_LAST_UPDATE_DATE IN DATE ,
630 X_LAST_UPDATED_BY IN NUMBER ,
631 X_LAST_UPDATE_LOGIN IN NUMBER
632 ) IS
633
634 CURSOR C IS
635 SELECT ROWID
636 FROM GMD_TECHNICAL_REQS
637 WHERE TECH_REQ_ID = X_TECH_REQ_ID;
638
639 BEGIN
640
641 INSERT INTO GMD_TECHNICAL_REQS (
642 FORMULATION_SPEC_ID,
643 SPEC_ATTRIBUTE_ID,
644 TECH_PARM_ID,
645 TECH_REQ_ID,
646 MIN_VALUE,
647 MAX_VALUE,
648 CREATION_DATE,
649 CREATED_BY,
650 LAST_UPDATE_DATE,
651 LAST_UPDATED_BY,
652 LAST_UPDATE_LOGIN
653 ) VALUES (
654 X_FORMULATION_SPEC_ID,
655 X_SPEC_ATTRIBUTE_ID,
656 X_TECH_PARM_ID,
657 X_TECH_REQ_ID,
658 X_MIN_VALUE,
659 X_MAX_VALUE,
660 X_CREATION_DATE,
661 X_CREATED_BY,
662 X_LAST_UPDATE_DATE,
663 X_LAST_UPDATED_BY,
664 X_LAST_UPDATE_LOGIN
665 );
666
667 OPEN C;
668 FETCH C INTO X_ROWID;
669 IF (C%NOTFOUND) THEN
670 CLOSE C;
671 RAISE NO_DATA_FOUND;
672 END IF;
673 CLOSE C;
674
675 END INSERT_TECHNICAL_REQ;
676
677
678 PROCEDURE LOCK_TECHNICAL_REQ (
679 X_TECH_REQ_ID IN NUMBER ,
680 X_FORMULATION_SPEC_ID IN NUMBER ,
681 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
682 X_TECH_PARM_ID IN NUMBER ,
683 X_MIN_VALUE IN NUMBER ,
684 X_MAX_VALUE IN NUMBER
685 ) IS
686 CURSOR C IS SELECT
687 FORMULATION_SPEC_ID,
688 SPEC_ATTRIBUTE_ID,
689 TECH_PARM_ID,
690 MIN_VALUE,
691 MAX_VALUE
692 FROM GMD_TECHNICAL_REQS
693 WHERE TECH_REQ_ID = X_TECH_REQ_ID
694 FOR UPDATE OF TECH_REQ_ID NOWAIT;
695 RECINFO C%ROWTYPE;
696
697 BEGIN
698 OPEN C;
699 FETCH C INTO RECINFO;
700 IF (C%NOTFOUND) THEN
701 CLOSE C;
702 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
703 APP_EXCEPTION.RAISE_EXCEPTION;
704 END IF;
705 CLOSE C;
706 IF ( (RECINFO.FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID)
707 AND (RECINFO.SPEC_ATTRIBUTE_ID = X_SPEC_ATTRIBUTE_ID)
708 AND (RECINFO.TECH_PARM_ID = X_TECH_PARM_ID)
709 AND ((RECINFO.MIN_VALUE = X_MIN_VALUE)
710 OR ((RECINFO.MIN_VALUE IS NULL) AND (X_MIN_VALUE IS NULL)))
711 AND ((RECINFO.MAX_VALUE = X_MAX_VALUE)
712 OR ((RECINFO.MAX_VALUE IS NULL) AND (X_MAX_VALUE IS NULL)))
713 ) THEN
714 NULL;
715 ELSE
716 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
717 APP_EXCEPTION.RAISE_EXCEPTION;
718 END IF;
719
720 RETURN;
721 END LOCK_TECHNICAL_REQ;
722
723 PROCEDURE UPDATE_TECHNICAL_REQ (
724 X_TECH_REQ_ID IN NUMBER ,
725 X_FORMULATION_SPEC_ID IN NUMBER ,
726 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
727 X_TECH_PARM_ID IN NUMBER ,
728 X_MIN_VALUE IN NUMBER ,
729 X_MAX_VALUE IN NUMBER ,
730 X_LAST_UPDATE_DATE IN DATE ,
731 X_LAST_UPDATED_BY IN NUMBER ,
732 X_LAST_UPDATE_LOGIN IN NUMBER
733 ) IS
734
735 BEGIN
736
737 UPDATE GMD_TECHNICAL_REQS
738 SET
739 FORMULATION_SPEC_ID = X_FORMULATION_SPEC_ID ,
740 SPEC_ATTRIBUTE_ID = X_SPEC_ATTRIBUTE_ID ,
741 TECH_PARM_ID = X_TECH_PARM_ID ,
742 MIN_VALUE = X_MIN_VALUE ,
743 MAX_VALUE = X_MAX_VALUE ,
744 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE ,
745 LAST_UPDATED_BY = X_LAST_UPDATED_BY ,
746 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
747 WHERE TECH_REQ_ID = X_TECH_REQ_ID;
748
749 IF (SQL%NOTFOUND) THEN
750 RAISE NO_DATA_FOUND;
751 END IF;
752
753 END UPDATE_TECHNICAL_REQ;
754
755 PROCEDURE DELETE_TECHNICAL_REQ (
756 X_TECH_REQ_ID IN NUMBER
757 ) IS
758 BEGIN
759
760 DELETE FROM GMD_TECHNICAL_REQS
761 WHERE TECH_REQ_ID = X_TECH_REQ_ID;
762
763 IF (SQL%NOTFOUND) THEN
764 RAISE NO_DATA_FOUND;
765 END IF;
766
767 END DELETE_TECHNICAL_REQ;
768
769 /* END - Technical Req - Table Handlers */
770
771
772 /* Specification Attributes - Table Handlers */
773
774 PROCEDURE INSERT_SPEC_ATTRIBUTE (
775 X_ROWID OUT NOCOPY VARCHAR2 ,
776 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
777 X_FORMULATION_SPEC_ID IN NUMBER ,
778 X_LOOKUP_TYPE IN VARCHAR2 ,
779 X_LOOKUP_CODE IN VARCHAR2 ,
780 X_CREATION_DATE IN DATE ,
781 X_CREATED_BY IN NUMBER ,
782 X_LAST_UPDATE_DATE IN DATE ,
783 X_LAST_UPDATED_BY IN NUMBER ,
784 X_LAST_UPDATE_LOGIN IN NUMBER) IS
785
786 CURSOR C IS
787 SELECT ROWID
788 FROM GMD_SPECIFICATION_ATTRIBUTES
789 WHERE SPEC_ATTRIBUTE_ID = X_SPEC_ATTRIBUTE_ID;
790
791 BEGIN
792
793 INSERT INTO GMD_SPECIFICATION_ATTRIBUTES (
794 FORMULATION_SPEC_ID,
795 SPEC_ATTRIBUTE_ID,
796 LOOKUP_TYPE,
797 LOOKUP_CODE,
798 CREATION_DATE,
799 CREATED_BY,
800 LAST_UPDATED_BY,
801 LAST_UPDATE_DATE,
802 LAST_UPDATE_LOGIN
803 ) VALUES (
804 X_FORMULATION_SPEC_ID,
805 X_SPEC_ATTRIBUTE_ID,
806 X_LOOKUP_TYPE,
807 X_LOOKUP_CODE,
808 X_CREATION_DATE,
809 X_CREATED_BY,
810 X_LAST_UPDATED_BY,
811 X_LAST_UPDATE_DATE,
812 X_LAST_UPDATE_LOGIN
813 );
814
815 OPEN C;
816 FETCH C INTO X_ROWID;
817 IF (C%NOTFOUND) THEN
818 CLOSE C;
819 RAISE NO_DATA_FOUND;
820 END IF;
821 CLOSE C;
822
823 END INSERT_SPEC_ATTRIBUTE;
824
825 PROCEDURE DELETE_SPEC_ATTRIBUTE (
826 X_SPEC_ATTRIBUTE_ID IN NUMBER
827 ) IS
828
829 BEGIN
830
831 DELETE FROM GMD_SPECIFICATION_ATTRIBUTES
832 WHERE SPEC_ATTRIBUTE_ID = X_SPEC_ATTRIBUTE_ID;
833
834 IF (SQL%NOTFOUND) THEN
835 RAISE NO_DATA_FOUND;
836 END IF;
837
838 END DELETE_SPEC_ATTRIBUTE;
839
840 /* END - Specification Attributes - Table Handlers */
841
842
843 /*-------------------------------------------------------------------
844 -- NAME
845 -- Get_specifications
846 --
847 -- SYNOPSIS
848 -- Procedure Get_specifications
849 --
850 -- DESCRIPTION
851 -- This procedure is called to fetch specifications based on search
852 -- condition passed to the API
853 --
854 --
855 -- HISTORY
856 -- Sriram 9/05/2005 Created for LCF Build
857 --------------------------------------------------------------------*/
858
859 PROCEDURE Get_specifications( p_spec_no IN VARCHAR2 DEFAULT NULL ,
860 p_spec_vers IN NUMBER DEFAULT NULL ,
861 p_spec_status IN NUMBER DEFAULT NULL ,
862 p_product IN VARCHAR2 DEFAULT NULL ,
863 p_product_id IN NUMBER DEFAULT NULL ,
864 p_routing IN VARCHAR2 DEFAULT NULL ,
865 p_routing_id IN NUMBER DEFAULT NULL ,
866 p_tech_parm_name IN VARCHAR2 DEFAULT NULL ,
867 p_tech_parm_id IN NUMBER DEFAULT NULL ,
868 p_spec_organization IN VARCHAR2 DEFAULT NULL ,
869 p_start_date IN VARCHAR2 DEFAULT NULL ,
870 p_end_date IN VARCHAR2 DEFAULT NULL ,
871 p_min_ingreds IN NUMBER DEFAULT NULL ,
872 p_max_ingreds IN NUMBER DEFAULT NULL ,
873 p_process_loss IN NUMBER DEFAULT NULL ,
874 p_obj_ind IN NUMBER DEFAULT NULL ,
875 p_ingr_pick_base IN VARCHAR2 DEFAULT NULL ,
876 p_lot_pick_strategy IN VARCHAR2 DEFAULT NULL ,
877 p_std_qty IN NUMBER DEFAULT NULL ,
878 p_std_uom IN VARCHAR2 DEFAULT NULL ,
879 x_search_clause IN OUT NOCOPY VARCHAR2 ,
880 x_spec_rec OUT NOCOPY GMD_FORMULATION_SPECS_PKG.l_spec_table
881 ) IS
882
883
884 TYPE dyn_cursor IS REF CURSOR;
885 Cur_get_spec dyn_cursor;
886
887 l_where VARCHAR2(3000) := ' 1 = 1 ';
888
889 i NUMBER;
890
891 BEGIN
892
893 -- Assign the where clause passed to the local where clause being built here
894 l_where := l_where || x_search_clause;
895
896 /* Construct the WHERE clause */
897 IF p_spec_no IS NOT NULL THEN
898 IF INSTRB(p_spec_no,'%') > 0 THEN
899 l_where := l_where || ' AND fs.SPEC_NAME like '''|| p_spec_no||'''';
900 ELSE
901 l_where := l_where || ' AND fs.SPEC_NAME = '''||p_spec_no||'''';
902 END IF;
903 END IF;
904
905 IF p_spec_vers IS NOT NULL THEN
906 l_where := l_where || ' AND fs.SPEC_VERS = '||p_spec_vers;
907 END IF;
908
909 IF p_spec_status IS NOT NULL THEN
910 l_where := l_where || ' AND fs.SPEC_STATUS = '||p_spec_status;
911 END IF;
912
913 IF (p_product IS NOT NULL) THEN
914 IF INSTRB(p_product,'%') > 0 THEN
915 l_where := l_where || ' and PRODUCT_ID in '
916 ||'(select i.inventory_item_id from mtl_system_items_kfv i '
917 ||' where i.organization_id = fs.owner_organization_id and '
918 ||' i.concatenated_segments like '''||p_product|| ''''||' )';
919 ELSE
920 l_where := l_where || ' and PRODUCT_ID in '
921 ||'(select i.inventory_item_id from mtl_system_items_kfv i '
922 ||' where i.organization_id = fs.owner_organization_id and '
923 ||' i.concatenated_segments = '''||p_product||''''||' )';
924 END IF;
925 END IF;
926
927 IF (p_start_date IS NOT NULL) THEN
928 IF INSTR(p_start_date, ' ', -1) = 0 THEN
929 l_where := l_where || ' AND NVL(TRUNC(fs.START_DATE),TRUNC(TO_DATE('''||p_start_date||''',''DD-MON-YYYY''))) >= TRUNC(TO_DATE('''||p_start_date||''',''DD-MON-YYYY''))';
930 NULL;
931 ELSE
932 l_where := l_where || ' AND NVL(TRUNC(fs.START_DATE),TRUNC(TO_DATE('''||p_start_date||''',''DD-MON-YYYY''))) >= TRUNC(TO_DATE('''||p_start_date||''',''DD-MON-YYYY''))';
933 NULL;
934 END IF;
935 END IF;
936
937 IF (p_end_date IS NOT NULL) THEN
938 IF INSTR(p_end_date, ' ', -1) = 0 THEN
939 l_where := l_where || ' AND NVL(TRUNC(fs.END_DATE),TRUNC(TO_DATE('''||p_end_date||''',''DD-MON-YYYY''))) <= TRUNC(TO_DATE('''||p_end_date||''',''DD-MON-YYYY''))';
940
941 ELSE
942 l_where := l_where || ' AND NVL(TRUNC(fs.END_DATE),TRUNC(TO_DATE('''||p_end_date||''',''DD-MON-YYYY''))) <= TRUNC(TO_DATE('''||p_end_date||''', ''DD-MON-YYYY''))';
943 NULL;
944 END IF;
945 END IF;
946
947 IF p_obj_ind IS NOT NULL THEN
948 l_where := l_where || ' AND fs.OBJECTIVE_IND = '||p_obj_ind;
949 END IF;
950
951 IF p_ingr_pick_base IS NOT NULL THEN
952 IF INSTRB(p_ingr_pick_base,'%') > 0 THEN
953 l_where := l_where || ' AND fs.INGRED_PICK_BASE_IND like '''|| p_ingr_pick_base||'''';
954 ELSE
955 l_where := l_where || ' AND fs.INGRED_PICK_BASE_IND = '''||p_ingr_pick_base||'''';
956 END IF;
957 END IF;
958
959 IF p_lot_pick_strategy IS NOT NULL THEN
960 IF INSTRB(p_lot_pick_strategy,'%') > 0 THEN
961 l_where := l_where || ' AND fs.PICK_LOT_STRATEGY like '''|| p_lot_pick_strategy||'''';
962 ELSE
963 l_where := l_where || ' AND fs.PICK_LOT_STRATEGY = '''||p_lot_pick_strategy||'''';
964 END IF;
965 END IF;
966
967 IF p_std_qty IS NOT NULL THEN
968 IF INSTRB(p_std_qty,'%') > 0 THEN
969 l_where := l_where || ' AND fs.std_qty like '||p_std_qty;
970 ELSE
971 l_where := l_where || ' AND fs.std_qty = '||p_std_qty;
972 END IF;
973 END IF;
974
975
976 IF p_std_uom IS NOT NULL THEN
977 IF INSTRB(p_std_uom,'%') > 0 THEN
978 l_where := l_where || ' AND Upper(fs.std_uom) like '''||p_std_uom||'''';
979 ELSE
980 l_where := l_where || ' AND Upper(fs.std_uom) = '''||p_std_uom||'''';
981 END IF;
982 END IF;
983
984 IF p_max_ingreds IS NOT NULL THEN
985 IF INSTRB(p_max_ingreds,'%') > 0 THEN
986 l_where := l_where || ' AND fs.max_ingreds like '||p_max_ingreds;
987 ELSE
988 l_where := l_where || ' AND fs.max_ingreds = '||p_max_ingreds;
989 END IF;
990 END IF;
991
992 IF p_min_ingreds IS NOT NULL THEN
993 IF INSTRB(p_min_ingreds,'%') > 0 THEN
994 l_where := l_where || ' AND fs.min_ingreds like '||p_min_ingreds;
995 ELSE
996 l_where := l_where || ' AND fs.min_ingreds = '||p_min_ingreds;
997 END IF;
998 END IF;
999
1000 IF (p_spec_organization IS NOT NULL) THEN
1001 IF (instr(p_spec_organization,'%') > 0) THEN
1002 l_where := l_where||' AND fs.owner_organization_id IN ' || '(SELECT organization_id FROM ORG_ACCESS_VIEW '||
1003 'WHERE ORGANIZATION_CODE LIKE '||''''||p_spec_organization||''''||' )';
1004 ELSE
1005 l_where := l_where||' AND fs.owner_organization_id IN ' || '(SELECT organization_id FROM ORG_ACCESS_VIEW '||
1006 'WHERE ORGANIZATION_CODE = '||''''||p_spec_organization||''''||')';
1007 END IF;
1008 END IF;
1009
1010 IF p_process_loss IS NOT NULL THEN
1011 IF INSTRB(p_process_loss,'%') > 0 THEN
1012 l_where := l_where || ' AND fs.process_loss like '||p_process_loss;
1013 ELSE
1014 l_where := l_where || ' AND fs.process_loss = '||p_process_loss;
1015 END IF;
1016 END IF;
1017
1018 /* Fetch the specifications */
1019 OPEN Cur_get_spec FOR
1020 'SELECT *
1021 FROM gmd_formulation_specs fs
1022 WHERE ' || NVL (l_where, '1 = 1');
1023
1024 i := 1;
1025 LOOP
1026 FETCH Cur_get_spec INTO x_spec_rec(i);
1027 i := i + 1;
1028 EXIT WHEN Cur_get_spec%NOTFOUND;
1029 END LOOP;
1030
1031 CLOSE Cur_get_spec;
1032
1033 /* Return the WHERE clause so that it can be used to create shortcuts on the WB if
1034 the user wants to save the search condition */
1035 x_search_clause := 'SELECT * FROM gmd_formulation_specs fs WHERE ' || NVL (l_where, '1 = 1') || ' ORDER BY spec_name, spec_vers';
1036
1037 END Get_specifications;
1038
1039 END GMD_FORMULATION_SPECS_PKG;
1040
[Home] [Help]