1 package body GMD_RECIPES_MLS as
2 /* $Header: GMDRMLSB.pls 120.1.12010000.2 2008/11/12 18:43:22 rnalla ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_RECIPE_ID in NUMBER,
6 X_OWNER_ID in NUMBER,
7 X_OWNER_LAB_TYPE in VARCHAR2,
8 X_DELETE_MARK in NUMBER,
9 X_TEXT_CODE in NUMBER,
10 X_RECIPE_NO in VARCHAR2,
11 X_RECIPE_VERSION in NUMBER,
12 X_OWNER_ORGANIZATION_ID in NUMBER,
13 X_CREATION_ORGANIZATION_ID in NUMBER,
14 X_FORMULA_ID in NUMBER,
15 X_ROUTING_ID in NUMBER,
16 X_PROJECT_ID in NUMBER,
17 X_RECIPE_STATUS in VARCHAR2,
18 X_CALCULATE_STEP_QUANTITY in NUMBER,
19 X_PLANNED_PROCESS_LOSS in NUMBER,
20 X_CONTIGUOUS_IND IN NUMBER,
21 X_ENHANCED_PI_IND IN VARCHAR2,
22 X_RECIPE_TYPE IN NUMBER,
23 X_RECIPE_DESCRIPTION in VARCHAR2,
24 X_ATTRIBUTE_CATEGORY in VARCHAR2,
25 X_ATTRIBUTE1 in VARCHAR2,
26 X_ATTRIBUTE2 in VARCHAR2,
27 X_ATTRIBUTE3 in VARCHAR2,
28 X_ATTRIBUTE4 in VARCHAR2,
29 X_ATTRIBUTE5 in VARCHAR2,
30 X_ATTRIBUTE6 in VARCHAR2,
31 X_ATTRIBUTE7 in VARCHAR2,
32 X_ATTRIBUTE8 in VARCHAR2,
33 X_ATTRIBUTE9 in VARCHAR2,
34 X_ATTRIBUTE10 in VARCHAR2,
35 X_ATTRIBUTE11 in VARCHAR2,
36 X_ATTRIBUTE12 in VARCHAR2,
37 X_ATTRIBUTE13 in VARCHAR2,
38 X_ATTRIBUTE14 in VARCHAR2,
39 X_ATTRIBUTE15 in VARCHAR2,
40 X_ATTRIBUTE16 in VARCHAR2,
41 X_ATTRIBUTE17 in VARCHAR2,
42 X_ATTRIBUTE18 in VARCHAR2,
43 X_ATTRIBUTE19 in VARCHAR2,
44 X_ATTRIBUTE20 in VARCHAR2,
45 X_ATTRIBUTE21 in VARCHAR2,
46 X_ATTRIBUTE22 in VARCHAR2,
47 X_ATTRIBUTE23 in VARCHAR2,
48 X_ATTRIBUTE24 in VARCHAR2,
49 X_ATTRIBUTE25 in VARCHAR2,
50 X_ATTRIBUTE26 in VARCHAR2,
51 X_ATTRIBUTE27 in VARCHAR2,
52 X_ATTRIBUTE28 in VARCHAR2,
53 X_ATTRIBUTE29 in VARCHAR2,
54 X_ATTRIBUTE30 in VARCHAR2,
55 X_CREATION_DATE in DATE,
56 X_CREATED_BY in NUMBER,
57 X_LAST_UPDATE_DATE in DATE,
58 X_LAST_UPDATED_BY in NUMBER,
59 X_LAST_UPDATE_LOGIN in NUMBER,
60 X_FIXED_PROCESS_LOSS in NUMBER, /* 6811759*/
61 X_FIXED_PROCESS_LOSS_UOM in VARCHAR2
62 ) is
63 cursor C is select ROWID from GMD_RECIPES_B
64 where RECIPE_ID = X_RECIPE_ID
65 ;
66 begin
67 insert into GMD_RECIPES_B (
68 ATTRIBUTE26,
69 ATTRIBUTE27,
70 ATTRIBUTE28,
71 ATTRIBUTE29,
72 ATTRIBUTE30,
73 OWNER_ID,
74 OWNER_LAB_TYPE,
75 ATTRIBUTE12,
76 ATTRIBUTE13,
77 ATTRIBUTE14,
78 ATTRIBUTE15,
79 ATTRIBUTE16,
80 ATTRIBUTE17,
81 ATTRIBUTE18,
82 ATTRIBUTE19,
83 ATTRIBUTE20,
84 ATTRIBUTE21,
85 ATTRIBUTE22,
86 ATTRIBUTE23,
87 ATTRIBUTE24,
88 ATTRIBUTE25,
89 DELETE_MARK,
90 ATTRIBUTE_CATEGORY,
91 ATTRIBUTE1,
92 ATTRIBUTE2,
93 ATTRIBUTE3,
94 ATTRIBUTE4,
95 ATTRIBUTE5,
96 ATTRIBUTE6,
97 ATTRIBUTE7,
98 ATTRIBUTE8,
99 ATTRIBUTE9,
100 ATTRIBUTE10,
101 ATTRIBUTE11,
102 TEXT_CODE,
103 RECIPE_ID,
104 RECIPE_NO,
105 RECIPE_VERSION,
106 OWNER_ORGANIZATION_ID,
107 CREATION_ORGANIZATION_ID,
108 FORMULA_ID,
109 ROUTING_ID,
110 PROJECT_ID,
111 RECIPE_STATUS,
112 CALCULATE_STEP_QUANTITY ,
113 PLANNED_PROCESS_LOSS,
114 CONTIGUOUS_IND,
115 ENHANCED_PI_IND,
116 RECIPE_TYPE,
117 CREATION_DATE,
118 CREATED_BY,
119 LAST_UPDATE_DATE,
120 LAST_UPDATED_BY,
121 LAST_UPDATE_LOGIN,
122 FIXED_PROCESS_LOSS, /* 6811759*/
123 FIXED_PROCESS_LOSS_UOM
124 ) values (
125 X_ATTRIBUTE26,
126 X_ATTRIBUTE27,
127 X_ATTRIBUTE28,
128 X_ATTRIBUTE29,
129 X_ATTRIBUTE30,
130 X_OWNER_ID,
131 X_OWNER_LAB_TYPE,
132 X_ATTRIBUTE12,
133 X_ATTRIBUTE13,
134 X_ATTRIBUTE14,
135 X_ATTRIBUTE15,
136 X_ATTRIBUTE16,
137 X_ATTRIBUTE17,
138 X_ATTRIBUTE18,
139 X_ATTRIBUTE19,
140 X_ATTRIBUTE20,
141 X_ATTRIBUTE21,
142 X_ATTRIBUTE22,
143 X_ATTRIBUTE23,
144 X_ATTRIBUTE24,
145 X_ATTRIBUTE25,
146 X_DELETE_MARK,
147 X_ATTRIBUTE_CATEGORY,
148 X_ATTRIBUTE1,
149 X_ATTRIBUTE2,
150 X_ATTRIBUTE3,
151 X_ATTRIBUTE4,
152 X_ATTRIBUTE5,
153 X_ATTRIBUTE6,
154 X_ATTRIBUTE7,
155 X_ATTRIBUTE8,
156 X_ATTRIBUTE9,
157 X_ATTRIBUTE10,
158 X_ATTRIBUTE11,
159 X_TEXT_CODE,
160 X_RECIPE_ID,
161 X_RECIPE_NO,
162 X_RECIPE_VERSION,
163 X_OWNER_ORGANIZATION_ID,
164 X_CREATION_ORGANIZATION_ID,
165 X_FORMULA_ID,
166 X_ROUTING_ID,
167 X_PROJECT_ID,
168 X_RECIPE_STATUS,
169 X_CALCULATE_STEP_QUANTITY,
170 X_PLANNED_PROCESS_LOSS,
171 X_CONTIGUOUS_IND,
172 X_ENHANCED_PI_IND,
173 X_RECIPE_TYPE,
174 X_CREATION_DATE,
175 X_CREATED_BY,
176 X_LAST_UPDATE_DATE,
177 X_LAST_UPDATED_BY,
178 X_LAST_UPDATE_LOGIN,
179 X_FIXED_PROCESS_LOSS , /* 6811759*/
180 X_FIXED_PROCESS_LOSS_UOM
181 );
182
183
184 insert into GMD_RECIPES_TL (
185 RECIPE_ID,
186 RECIPE_DESCRIPTION,
187 SOURCE_LANG,
188 CREATED_BY,
189 CREATION_DATE,
190 LAST_UPDATED_BY,
191 LAST_UPDATE_DATE,
192 LAST_UPDATE_LOGIN,
193 LANGUAGE
194 ) select
195 X_RECIPE_ID,
196 X_RECIPE_DESCRIPTION,
197 userenv('LANG'),
198 X_CREATED_BY,
199 X_CREATION_DATE,
200 X_LAST_UPDATED_BY,
201 X_LAST_UPDATE_DATE,
202 X_LAST_UPDATE_LOGIN,
203 L.LANGUAGE_CODE
204 from FND_LANGUAGES L
205 where L.INSTALLED_FLAG in ('I', 'B')
206 and not exists
207 (select NULL
208 from GMD_RECIPES_TL T
209 where T.RECIPE_ID = X_RECIPE_ID
210 and T.LANGUAGE = L.LANGUAGE_CODE);
211
212 open c;
213 fetch c into X_ROWID;
214 if (c%notfound) then
215 close c;
216 raise no_data_found;
217 end if;
218 close c;
219 end INSERT_ROW;
220
221 procedure LOCK_ROW (
222 X_RECIPE_ID in NUMBER,
223 X_ATTRIBUTE26 in VARCHAR2,
224 X_ATTRIBUTE27 in VARCHAR2,
225 X_ATTRIBUTE28 in VARCHAR2,
226 X_ATTRIBUTE29 in VARCHAR2,
227 X_ATTRIBUTE30 in VARCHAR2,
228 X_OWNER_ID in NUMBER,
229 X_OWNER_LAB_TYPE in VARCHAR2,
230 X_ATTRIBUTE12 in VARCHAR2,
231 X_ATTRIBUTE13 in VARCHAR2,
232 X_ATTRIBUTE14 in VARCHAR2,
233 X_ATTRIBUTE15 in VARCHAR2,
234 X_ATTRIBUTE16 in VARCHAR2,
235 X_ATTRIBUTE17 in VARCHAR2,
236 X_ATTRIBUTE18 in VARCHAR2,
237 X_ATTRIBUTE19 in VARCHAR2,
238 X_ATTRIBUTE20 in VARCHAR2,
239 X_ATTRIBUTE21 in VARCHAR2,
240 X_ATTRIBUTE22 in VARCHAR2,
241 X_ATTRIBUTE23 in VARCHAR2,
242 X_ATTRIBUTE24 in VARCHAR2,
243 X_ATTRIBUTE25 in VARCHAR2,
244 X_DELETE_MARK in NUMBER,
245 X_ATTRIBUTE_CATEGORY in VARCHAR2,
246 X_ATTRIBUTE1 in VARCHAR2,
247 X_ATTRIBUTE2 in VARCHAR2,
248 X_ATTRIBUTE3 in VARCHAR2,
249 X_ATTRIBUTE4 in VARCHAR2,
250 X_ATTRIBUTE5 in VARCHAR2,
251 X_ATTRIBUTE6 in VARCHAR2,
252 X_ATTRIBUTE7 in VARCHAR2,
253 X_ATTRIBUTE8 in VARCHAR2,
254 X_ATTRIBUTE9 in VARCHAR2,
255 X_ATTRIBUTE10 in VARCHAR2,
256 X_ATTRIBUTE11 in VARCHAR2,
257 X_TEXT_CODE in NUMBER,
258 X_RECIPE_NO in VARCHAR2,
259 X_RECIPE_VERSION in NUMBER,
260 X_OWNER_ORGANIZATION_ID in NUMBER,
261 X_CREATION_ORGANIZATION_ID in NUMBER,
262 X_FORMULA_ID in NUMBER,
263 X_ROUTING_ID in NUMBER,
264 X_PROJECT_ID in NUMBER,
265 X_RECIPE_STATUS in VARCHAR2,
266 X_CALCULATE_STEP_QUANTITY in NUMBER,
267 X_PLANNED_PROCESS_LOSS in NUMBER,
268 X_CONTIGUOUS_IND IN NUMBER,
269 X_ENHANCED_PI_IND IN VARCHAR2,
270 X_RECIPE_TYPE IN NUMBER,
271 X_RECIPE_DESCRIPTION in VARCHAR2,
272 X_FIXED_PROCESS_LOSS in NUMBER, /* 6811759*/
273 X_FIXED_PROCESS_LOSS_UOM in VARCHAR2
274 ) is
275 cursor c is select
276 ATTRIBUTE26,
277 ATTRIBUTE27,
278 ATTRIBUTE28,
279 ATTRIBUTE29,
280 ATTRIBUTE30,
281 OWNER_ID,
282 OWNER_LAB_TYPE,
283 ATTRIBUTE12,
284 ATTRIBUTE13,
285 ATTRIBUTE14,
286 ATTRIBUTE15,
287 ATTRIBUTE16,
288 ATTRIBUTE17,
289 ATTRIBUTE18,
290 ATTRIBUTE19,
291 ATTRIBUTE20,
292 ATTRIBUTE21,
293 ATTRIBUTE22,
294 ATTRIBUTE23,
295 ATTRIBUTE24,
296 ATTRIBUTE25,
297 DELETE_MARK,
298 ATTRIBUTE_CATEGORY,
299 ATTRIBUTE1,
300 ATTRIBUTE2,
301 ATTRIBUTE3,
302 ATTRIBUTE4,
303 ATTRIBUTE5,
304 ATTRIBUTE6,
305 ATTRIBUTE7,
306 ATTRIBUTE8,
307 ATTRIBUTE9,
308 ATTRIBUTE10,
309 ATTRIBUTE11,
310 TEXT_CODE,
311 RECIPE_NO,
312 RECIPE_VERSION,
313 OWNER_ORGANIZATION_ID,
314 CREATION_ORGANIZATION_ID,
315 FORMULA_ID,
316 ROUTING_ID,
317 PROJECT_ID,
318 RECIPE_STATUS,
319 CALCULATE_STEP_QUANTITY,
320 PLANNED_PROCESS_LOSS,
321 CONTIGUOUS_IND,
322 ENHANCED_PI_IND,
323 RECIPE_TYPE,
324 FIXED_PROCESS_LOSS, /* 6811759*/
325 FIXED_PROCESS_LOSS_UOM
326 from GMD_RECIPES_B
327 where RECIPE_ID = X_RECIPE_ID
328 for update of RECIPE_ID nowait;
329 recinfo c%rowtype;
330
331 cursor c1 is select
332 RECIPE_DESCRIPTION,
333 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
334 from GMD_RECIPES_TL
335 where RECIPE_ID = X_RECIPE_ID
336 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
337 for update of RECIPE_ID nowait;
338 begin
339 open c;
340 fetch c into recinfo;
341 if (c%notfound) then
342 close c;
343 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
344 app_exception.raise_exception;
345 end if;
346 close c;
347 if ( ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
348 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
349 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
350 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
351 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
352 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
353 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
354 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
355 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
356 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
357 AND (recinfo.OWNER_ID = X_OWNER_ID)
358 AND ((recinfo.OWNER_LAB_TYPE = X_OWNER_LAB_TYPE)
359 OR ((recinfo.OWNER_LAB_TYPE is null) AND (X_OWNER_LAB_TYPE is null)))
360 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
361 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
362 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
363 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
364 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
365 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
366 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
367 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
368 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
369 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
370 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
371 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
372 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
373 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
374 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
375 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
376 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
377 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
378 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
382 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
379 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
380 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
381 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
383 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
384 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
385 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
386 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
387 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
388 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
389 AND ((recinfo.CALCULATE_STEP_QUANTITY = X_CALCULATE_STEP_QUANTITY)
390 OR ((recinfo.CALCULATE_STEP_QUANTITY is null) AND (X_CALCULATE_STEP_QUANTITY is null)))
391 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
392 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
393 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
394 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
395 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
396 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
397 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
398 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
399 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
400 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
401 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
402 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
403 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
404 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
405 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
406 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
407 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
408 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
409 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
410 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
411 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
412 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
413 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
414 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
415 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
416 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
417 AND (recinfo.RECIPE_NO = X_RECIPE_NO)
418 AND (recinfo.RECIPE_VERSION = X_RECIPE_VERSION)
419 AND (recinfo.OWNER_ORGANIZATION_ID = X_OWNER_ORGANIZATION_ID)
420 AND (recinfo.CREATION_ORGANIZATION_ID = X_CREATION_ORGANIZATION_ID)
421 AND (recinfo.FORMULA_ID = X_FORMULA_ID)
422 AND ((recinfo.ROUTING_ID = X_ROUTING_ID)
423 OR ((recinfo.ROUTING_ID is null) AND (X_ROUTING_ID is null)))
424 AND ((recinfo.PROJECT_ID = X_PROJECT_ID)
425 OR ((recinfo.PROJECT_ID is null) AND (X_PROJECT_ID is null)))
426 AND (recinfo.RECIPE_STATUS = X_RECIPE_STATUS)
427 AND ((recinfo.CONTIGUOUS_IND = X_CONTIGUOUS_IND)
428 OR ((recinfo.CONTIGUOUS_IND is null) AND (X_CONTIGUOUS_IND is null)))
429 AND ((recinfo.ENHANCED_PI_IND = X_ENHANCED_PI_IND)
430 OR ((recinfo.ENHANCED_PI_IND is null) AND (X_ENHANCED_PI_IND is null)))
431 AND ((recinfo.RECIPE_TYPE = X_RECIPE_TYPE)
432 OR ((recinfo.RECIPE_TYPE is null) AND (X_RECIPE_TYPE is null)))
433 AND ((recinfo.PLANNED_PROCESS_LOSS = X_PLANNED_PROCESS_LOSS)
434 OR ((recinfo.PLANNED_PROCESS_LOSS is null) AND (X_PLANNED_PROCESS_LOSS is null)))
435 AND ((recinfo.FIXED_PROCESS_LOSS = X_FIXED_PROCESS_LOSS) /* 6811759*/
436 OR ((recinfo.FIXED_PROCESS_LOSS is null) AND (X_FIXED_PROCESS_LOSS is null)))
437 AND ((recinfo.FIXED_PROCESS_LOSS_UOM = X_FIXED_PROCESS_LOSS_UOM)
438 OR ((recinfo.FIXED_PROCESS_LOSS_UOM is null) AND (X_FIXED_PROCESS_LOSS_UOM is null)))
439 ) then
440 null;
441 else
442 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
443 app_exception.raise_exception;
444 end if;
445
446 for tlinfo in c1 loop
447 if (tlinfo.BASELANG = 'Y') then
448 if ( (tlinfo.RECIPE_DESCRIPTION = X_RECIPE_DESCRIPTION)
449 ) then
450 null;
451 else
452 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
453 app_exception.raise_exception;
454 end if;
455 end if;
456 end loop;
457 return;
458 end LOCK_ROW;
459
460 procedure UPDATE_ROW (
461 X_RECIPE_ID in NUMBER,
462 X_OWNER_ID in NUMBER,
463 X_OWNER_LAB_TYPE in VARCHAR2,
464 X_DELETE_MARK in NUMBER,
465 X_RECIPE_NO in VARCHAR2,
466 X_RECIPE_VERSION in NUMBER,
467 X_OWNER_ORGANIZATION_ID in NUMBER,
468 X_CREATION_ORGANIZATION_ID in NUMBER,
469 X_FORMULA_ID in NUMBER,
470 X_ROUTING_ID in NUMBER,
471 X_PROJECT_ID in NUMBER,
472 X_RECIPE_STATUS in VARCHAR2,
473 X_CALCULATE_STEP_QUANTITY in NUMBER,
474 X_PLANNED_PROCESS_LOSS in NUMBER,
475 X_CONTIGUOUS_IND IN NUMBER,
476 X_ENHANCED_PI_IND IN VARCHAR2,
477 X_RECIPE_TYPE IN NUMBER,
478 X_RECIPE_DESCRIPTION in VARCHAR2,
479 X_ATTRIBUTE_CATEGORY in VARCHAR2,
480 X_ATTRIBUTE1 in VARCHAR2,
481 X_ATTRIBUTE2 in VARCHAR2,
482 X_ATTRIBUTE3 in VARCHAR2,
483 X_ATTRIBUTE4 in VARCHAR2,
487 X_ATTRIBUTE8 in VARCHAR2,
484 X_ATTRIBUTE5 in VARCHAR2,
485 X_ATTRIBUTE6 in VARCHAR2,
486 X_ATTRIBUTE7 in VARCHAR2,
488 X_ATTRIBUTE9 in VARCHAR2,
489 X_ATTRIBUTE10 in VARCHAR2,
490 X_ATTRIBUTE11 in VARCHAR2,
491 X_ATTRIBUTE12 in VARCHAR2,
492 X_ATTRIBUTE13 in VARCHAR2,
493 X_ATTRIBUTE14 in VARCHAR2,
494 X_ATTRIBUTE15 in VARCHAR2,
495 X_ATTRIBUTE16 in VARCHAR2,
496 X_ATTRIBUTE17 in VARCHAR2,
497 X_ATTRIBUTE18 in VARCHAR2,
498 X_ATTRIBUTE19 in VARCHAR2,
499 X_ATTRIBUTE20 in VARCHAR2,
500 X_ATTRIBUTE21 in VARCHAR2,
501 X_ATTRIBUTE22 in VARCHAR2,
502 X_ATTRIBUTE23 in VARCHAR2,
503 X_ATTRIBUTE24 in VARCHAR2,
504 X_ATTRIBUTE25 in VARCHAR2,
505 X_ATTRIBUTE26 in VARCHAR2,
506 X_ATTRIBUTE27 in VARCHAR2,
507 X_ATTRIBUTE28 in VARCHAR2,
508 X_ATTRIBUTE29 in VARCHAR2,
509 X_ATTRIBUTE30 in VARCHAR2,
510 X_TEXT_CODE in NUMBER,
511 X_LAST_UPDATE_DATE in DATE,
512 X_LAST_UPDATED_BY in NUMBER,
513 X_LAST_UPDATE_LOGIN in NUMBER,
514 X_FIXED_PROCESS_LOSS IN NUMBER, /* 6811759*/
515 X_FIXED_PROCESS_LOSS_UOM IN VARCHAR2
516 ) is
517 begin
518
519 update GMD_RECIPES_B set
520 ATTRIBUTE26 = X_ATTRIBUTE26,
521 ATTRIBUTE27 = X_ATTRIBUTE27,
522 ATTRIBUTE28 = X_ATTRIBUTE28,
523 ATTRIBUTE29 = X_ATTRIBUTE29,
524 ATTRIBUTE30 = X_ATTRIBUTE30,
525 OWNER_ID = X_OWNER_ID,
526 OWNER_LAB_TYPE = X_OWNER_LAB_TYPE,
527 ATTRIBUTE12 = X_ATTRIBUTE12,
528 ATTRIBUTE13 = X_ATTRIBUTE13,
529 ATTRIBUTE14 = X_ATTRIBUTE14,
530 ATTRIBUTE15 = X_ATTRIBUTE15,
531 ATTRIBUTE16 = X_ATTRIBUTE16,
532 ATTRIBUTE17 = X_ATTRIBUTE17,
533 ATTRIBUTE18 = X_ATTRIBUTE18,
534 ATTRIBUTE19 = X_ATTRIBUTE19,
535 ATTRIBUTE20 = X_ATTRIBUTE20,
536 ATTRIBUTE21 = X_ATTRIBUTE21,
537 ATTRIBUTE22 = X_ATTRIBUTE22,
538 ATTRIBUTE23 = X_ATTRIBUTE23,
539 ATTRIBUTE24 = X_ATTRIBUTE24,
540 ATTRIBUTE25 = X_ATTRIBUTE25,
541 DELETE_MARK = X_DELETE_MARK,
542 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
543 ATTRIBUTE1 = X_ATTRIBUTE1,
544 ATTRIBUTE2 = X_ATTRIBUTE2,
545 ATTRIBUTE3 = X_ATTRIBUTE3,
546 ATTRIBUTE4 = X_ATTRIBUTE4,
547 ATTRIBUTE5 = X_ATTRIBUTE5,
548 ATTRIBUTE6 = X_ATTRIBUTE6,
549 ATTRIBUTE7 = X_ATTRIBUTE7,
550 ATTRIBUTE8 = X_ATTRIBUTE8,
551 ATTRIBUTE9 = X_ATTRIBUTE9,
552 ATTRIBUTE10 = X_ATTRIBUTE10,
553 ATTRIBUTE11 = X_ATTRIBUTE11,
554 TEXT_CODE = X_TEXT_CODE,
555 RECIPE_NO = X_RECIPE_NO,
556 RECIPE_VERSION = X_RECIPE_VERSION,
557 OWNER_ORGANIZATION_ID = X_OWNER_ORGANIZATION_ID,
558 CREATION_ORGANIZATION_ID = X_CREATION_ORGANIZATION_ID,
559 FORMULA_ID = X_FORMULA_ID,
560 ROUTING_ID = X_ROUTING_ID,
561 PROJECT_ID = X_PROJECT_ID,
562 RECIPE_STATUS = X_RECIPE_STATUS,
563 CALCULATE_STEP_QUANTITY = X_CALCULATE_STEP_QUANTITY,
564 PLANNED_PROCESS_LOSS = X_PLANNED_PROCESS_LOSS,
565 CONTIGUOUS_IND = X_CONTIGUOUS_IND,
566 ENHANCED_PI_IND = X_ENHANCED_PI_IND,
567 RECIPE_TYPE = X_RECIPE_TYPE,
568 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
569 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
570 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
571 FIXED_PROCESS_LOSS = X_FIXED_PROCESS_LOSS, /* 6811759*/
572 FIXED_PROCESS_LOSS_UOM = X_FIXED_PROCESS_LOSS_UOM
573 where RECIPE_ID = X_RECIPE_ID;
574
575 if (sql%notfound) then
576 raise no_data_found;
577 end if;
578
579 update GMD_RECIPES_TL set
580 RECIPE_DESCRIPTION = X_RECIPE_DESCRIPTION,
581 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
582 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
583 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
584 SOURCE_LANG = userenv('LANG')
585 where RECIPE_ID = X_RECIPE_ID
586 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
587
588 if (sql%notfound) then
589 raise no_data_found;
590 end if;
591 end UPDATE_ROW;
592
593 procedure DELETE_ROW (
594 X_RECIPE_ID in NUMBER
595 ) is
596 begin
597 delete from GMD_RECIPES_TL
598 where RECIPE_ID = X_RECIPE_ID;
599
600 if (sql%notfound) then
601 raise no_data_found;
602 end if;
603
604 delete from GMD_RECIPES_B
605 where RECIPE_ID = X_RECIPE_ID;
606
607 if (sql%notfound) then
608 raise no_data_found;
609 end if;
610 end DELETE_ROW;
611
612 procedure ADD_LANGUAGE
613 is
614 begin
615 delete from GMD_RECIPES_TL T
616 where not exists
617 (select NULL
618 from GMD_RECIPES_B B
619 where B.RECIPE_ID = T.RECIPE_ID
620 );
621
622 update GMD_RECIPES_TL T set (
623 RECIPE_DESCRIPTION
624 ) = (select
625 B.RECIPE_DESCRIPTION
626 from GMD_RECIPES_TL B
627 where B.RECIPE_ID = T.RECIPE_ID
628 and B.LANGUAGE = T.SOURCE_LANG)
629 where (
630 T.RECIPE_ID,
631 T.LANGUAGE
632 ) in (select
633 SUBT.RECIPE_ID,
634 SUBT.LANGUAGE
635 from GMD_RECIPES_TL SUBB, GMD_RECIPES_TL SUBT
636 where SUBB.RECIPE_ID = SUBT.RECIPE_ID
637 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
638 and (SUBB.RECIPE_DESCRIPTION <> SUBT.RECIPE_DESCRIPTION
639 ));
640
641 insert into GMD_RECIPES_TL (
642 RECIPE_ID,
643 RECIPE_DESCRIPTION,
644 SOURCE_LANG,
645 CREATED_BY,
646 CREATION_DATE,
647 LAST_UPDATED_BY,
648 LAST_UPDATE_DATE,
649 LAST_UPDATE_LOGIN,
650 LANGUAGE
651 ) select
652 B.RECIPE_ID,
653 B.RECIPE_DESCRIPTION,
654 B.SOURCE_LANG,
655 B.CREATED_BY,
656 B.CREATION_DATE,
657 B.LAST_UPDATED_BY,
658 B.LAST_UPDATE_DATE,
659 B.LAST_UPDATE_LOGIN,
660 L.LANGUAGE_CODE
661 from GMD_RECIPES_TL B, FND_LANGUAGES L
662 where L.INSTALLED_FLAG in ('I', 'B')
663 and B.LANGUAGE = userenv('LANG')
664 and not exists
665 (select NULL
666 from GMD_RECIPES_TL T
667 where T.RECIPE_ID = B.RECIPE_ID
668 and T.LANGUAGE = L.LANGUAGE_CODE);
669 end ADD_LANGUAGE;
670
671 end GMD_RECIPES_MLS;
[Home] [Help]