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