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