[Home] [Help]
PACKAGE BODY: APPS.PA_UOM
Source
1 PACKAGE BODY pa_uom AS
2 /* $Header: PATXUOMB.pls 120.3.12020000.3 2013/03/06 09:47:26 admarath ship $ */
3
4
5 PROCEDURE update_fnd_lookup_values(
6 X_LOOKUP_TYPE in VARCHAR2,
7 X_SECURITY_GROUP_ID in NUMBER default NULL,
8 X_VIEW_APPLICATION_ID in NUMBER,
9 X_LOOKUP_CODE in VARCHAR2,
10 X_LANGUAGE in VARCHAR2,
11 X_TAG in VARCHAR2,
12 X_ATTRIBUTE_CATEGORY in VARCHAR2,
13 X_ATTRIBUTE1 in VARCHAR2,
14 X_ATTRIBUTE2 in VARCHAR2,
15 X_ATTRIBUTE3 in VARCHAR2,
16 X_ATTRIBUTE4 in VARCHAR2,
17 X_ENABLED_FLAG in VARCHAR2,
18 X_START_DATE_ACTIVE in DATE,
19 X_END_DATE_ACTIVE in DATE,
20 X_TERRITORY_CODE in VARCHAR2,
21 X_ATTRIBUTE5 in VARCHAR2,
22 X_ATTRIBUTE6 in VARCHAR2,
23 X_ATTRIBUTE7 in VARCHAR2,
24 X_ATTRIBUTE8 in VARCHAR2,
25 X_ATTRIBUTE9 in VARCHAR2,
26 X_ATTRIBUTE10 in VARCHAR2,
27 X_ATTRIBUTE11 in VARCHAR2,
28 X_ATTRIBUTE12 in VARCHAR2,
29 X_ATTRIBUTE13 in VARCHAR2,
30 X_ATTRIBUTE14 in VARCHAR2,
31 X_ATTRIBUTE15 in VARCHAR2,
32 X_MEANING in VARCHAR2,
33 X_DESCRIPTION in VARCHAR2,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER
37 ) is
38 sgid NUMBER ;
39
40 begin
41
42 if (X_SECURITY_GROUP_ID is NULL) then
43 sgid:= FND_GLOBAL.SECURITY_GROUP_ID;
44 else
45 sgid := X_SECURITY_GROUP_ID;
46 end if;
47
48 -- Update "non-translated" values in all languages
49 update FND_LOOKUP_VALUES set
50 TAG = X_TAG,
51 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
52 ATTRIBUTE1 = X_ATTRIBUTE1,
53 ATTRIBUTE2 = X_ATTRIBUTE2,
54 ATTRIBUTE3 = X_ATTRIBUTE3,
55 ATTRIBUTE4 = X_ATTRIBUTE4,
56 ENABLED_FLAG = X_ENABLED_FLAG,
57 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
58 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
59 TERRITORY_CODE = X_TERRITORY_CODE,
60 ATTRIBUTE5 = X_ATTRIBUTE5,
61 ATTRIBUTE6 = X_ATTRIBUTE6,
62 ATTRIBUTE7 = X_ATTRIBUTE7,
63 ATTRIBUTE8 = X_ATTRIBUTE8,
64 ATTRIBUTE9 = X_ATTRIBUTE9,
65 ATTRIBUTE10 = X_ATTRIBUTE10,
66 ATTRIBUTE11 = X_ATTRIBUTE11,
67 ATTRIBUTE12 = X_ATTRIBUTE12,
68 ATTRIBUTE13 = X_ATTRIBUTE13,
69 ATTRIBUTE14 = X_ATTRIBUTE14,
70 ATTRIBUTE15 = X_ATTRIBUTE15,
71 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
72 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
74 where LOOKUP_TYPE = X_LOOKUP_TYPE
75 and SECURITY_GROUP_ID = sgid
76 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
77 and LOOKUP_CODE = X_LOOKUP_CODE
78 and LANGUAGE= X_LANGUAGE;
79
80
81 -- Update "translated" values in current language
82 update FND_LOOKUP_VALUES set
83 MEANING = X_MEANING,
84 DESCRIPTION = X_DESCRIPTION,
85 SOURCE_LANG = userenv('LANG')
86 where LOOKUP_TYPE = X_LOOKUP_TYPE
87 and SECURITY_GROUP_ID = sgid
88 and VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
89 and LOOKUP_CODE = X_LOOKUP_CODE
90 and LANGUAGE=X_LANGUAGE;
91
92
93 if (sql%notfound) then
94 raise no_data_found;
95 end if;
96
97
98 exception
99 when others then
100 raise;
101 end update_fnd_lookup_values;
102
103 /******************************************************************************/
104 ------------------------------ Function Get_UOM ------------------------------
105 /******************************************************************************/
106
107 FUNCTION get_uom(P_user_id IN number , P_uom_code IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2 IS
108
109 /*****************************************************************************/
110 /***** This procedure will first Identify any Upadted UOM in MTL. And *****/
111 /***** reflect the same in FND LOOKUP VALUES. Once Updated existing UOM *****/
112 /***** it will Insert any newly introduced UOMs in MTL to FND LOOKUP *****/
113 /***** VALUES. This is done using BulkInsertion. *****/
114 /*****************************************************************************/
115
116 l_conc_login_id NUMBER ;
117 l_security_group_id NUMBER ;
118 l_return_str VARCHAR2(2000);
119 l_rowid VARCHAR2(2000);
120
121 CURSOR cur_chg_uom_code_1(p_uom_code IN VARCHAR2) IS /*cursor to select records whose meaning have been updated by the users in the mtl table */
122 SELECT language
123 ,uom_code
124 ,disable_date
125 ,unit_of_measure_tl
126 ,description
127 ,source_lang
128 ,attribute_category
129 ,attribute1
130 ,attribute2
131 ,attribute3
132 ,attribute4
133 ,attribute5
134 ,attribute6
135 ,attribute7
136 ,attribute8
137 ,attribute9
138 ,attribute10
139 ,attribute11
140 ,attribute12
141 ,attribute13
142 ,attribute14
143 ,attribute15
144 FROM Mtl_Units_Of_Measure_TL mtl_uom
145 WHERE mtl_uom.uom_code = p_uom_code
146 /* AND language = userenv('LANG') */
147 AND EXISTS
148 (SELECT lookup_code
149 FROM fnd_lookup_values flv
150 WHERE flv.lookup_type = 'UNIT'
151 AND flv.meaning <> mtl_uom.unit_of_measure_tl
152 AND flv.lookup_code = mtl_uom.uom_code
153 AND flv.view_application_id = 275
154 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
155 AND flv.language = mtl_uom.language
156 )
157 AND NOT EXISTS
158 (SELECT 1
159 FROM fnd_lookup_values flv1
160 WHERE flv1.lookup_type = 'UNIT'
161 AND flv1.meaning = mtl_uom.unit_of_measure_tl
162 AND flv1.view_application_id = 275
163 AND flv1.security_group_id = fnd_global.lookup_security_group('UNIT',275)
164 AND flv1.language = mtl_uom.language
165 )
166 AND rowid = (
167 SELECT max(rowid) from Mtl_Units_Of_Measure_TL mtl_uom_2
168 WHERE mtl_uom.unit_of_measure_tl = mtl_uom_2.unit_of_measure_tl
169 AND mtl_uom.language = mtl_uom_2.language
170 ); /*Bug 6740153 added the rowid condition to fetch unique record only */
171 CURSOR cur_chg_uom_code IS /*cursor to select records whose meaning have been updated by the users in the mtl table */
172 SELECT language
173 ,uom_code
174 ,disable_date
175 ,unit_of_measure_tl
176 ,description
177 ,source_lang
178 ,attribute_category
179 ,attribute1
180 ,attribute2
181 ,attribute3
182 ,attribute4
183 ,attribute5
184 ,attribute6
185 ,attribute7
186 ,attribute8
187 ,attribute9
188 ,attribute10
189 ,attribute11
190 ,attribute12
191 ,attribute13
192 ,attribute14
193 ,attribute15
194 FROM Mtl_Units_Of_Measure_TL mtl_uom
195 WHERE /* language = userenv('LANG') */
196 EXISTS (SELECT UOM_CODE FROM MTL_UOM_CONVERSIONS MUC_UOM
197 WHERE MUC_UOM.UOM_CODE = MTL_UOM.UOM_CODE
198 )
199 AND EXISTS
200 (SELECT lookup_code
201 FROM fnd_lookup_values flv
202 WHERE flv.lookup_type = 'UNIT'
203 AND flv.meaning <> mtl_uom.unit_of_measure_tl
204 AND flv.lookup_code = mtl_uom.uom_code
205 AND flv.view_application_id = 275
206 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
207 AND flv.language = mtl_uom.language
208 )
209 AND NOT EXISTS
210 (SELECT 1
211 FROM fnd_lookup_values flv1
212 WHERE flv1.lookup_type = 'UNIT'
213 AND flv1.meaning = mtl_uom.unit_of_measure_tl
214 AND flv1.view_application_id = 275
215 AND flv1.security_group_id = fnd_global.lookup_security_group('UNIT',275)
216 AND flv1.language = mtl_uom.language
217 )
218 AND rowid = (
219 SELECT max(rowid) from Mtl_Units_Of_Measure_TL mtl_uom_2
220 WHERE mtl_uom.unit_of_measure_tl = mtl_uom_2.unit_of_measure_tl
221 AND mtl_uom.language = mtl_uom_2.language
222 ); /*Bug 6740153 added the rowid condition to fetch unique record only */
223 /* Removed check for bug 4130638
224 AND EXISTS
225 ( SELECT 1
226 FROM pa_transaction_interface_all pa
227 WHERE pa.unit_of_measure = mtl_uom.uom_code
228 ) ;
229 */
230
231 CURSOR cur_chg_uom_code_y(p_uom_code IN VARCHAR2 ) IS /*cursor to update records with @meaning where duplication for meaning exists*/
232 SELECT language
233 ,uom_code
234 ,disable_date
235 ,unit_of_measure_tl
236 ,description
237 ,source_lang
238 ,attribute_category
239 ,attribute1
240 ,attribute2
241 ,attribute3
242 ,attribute4
243 ,attribute5
244 ,attribute6
245 ,attribute7
246 ,attribute8
247 ,attribute9
248 ,attribute10
249 ,attribute11
250 ,attribute12
251 ,attribute13
252 ,attribute14
253 ,attribute15
254 FROM Mtl_Units_Of_Measure_TL mtl_uom
255 WHERE mtl_uom.uom_code = p_uom_code
256 /* AND language = userenv('LANG') */
257 AND EXISTS
258 (SELECT lookup_code
259 FROM fnd_lookup_values flv
260 WHERE flv.lookup_type = 'UNIT'
261 AND flv.meaning <> mtl_uom.unit_of_measure_tl
262 AND flv.lookup_code = mtl_uom.uom_code
263 AND flv.view_application_id = 275
264 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
265 AND flv.language = mtl_uom.language
266 )
267 AND NOT EXISTS
268 (SELECT 1
269 FROM fnd_lookup_values flv1
270 WHERE flv1.lookup_type = 'UNIT'
271 AND flv1.meaning = '@'||mtl_uom.unit_of_measure_tl
272 AND flv1.view_application_id = 275
273 AND flv1.security_group_id = fnd_global.lookup_security_group('UNIT',275)
274 AND flv1.language = mtl_uom.language
275 );
276 CURSOR cur_chg_uom_code_x IS /*cursor to update records with @meaning where duplication for meaning exists*/
277 SELECT language
278 ,uom_code
279 ,disable_date
280 ,unit_of_measure_tl
281 ,description
282 ,source_lang
283 ,attribute_category
284 ,attribute1
285 ,attribute2
286 ,attribute3
287 ,attribute4
288 ,attribute5
289 ,attribute6
290 ,attribute7
291 ,attribute8
292 ,attribute9
293 ,attribute10
294 ,attribute11
295 ,attribute12
296 ,attribute13
297 ,attribute14
298 ,attribute15
299 FROM Mtl_Units_Of_Measure_TL mtl_uom
300 WHERE /* language = userenv('LANG') */
301 EXISTS (SELECT UOM_CODE FROM MTL_UOM_CONVERSIONS MUC_UOM
302 WHERE MUC_UOM.UOM_CODE = MTL_UOM.UOM_CODE
303 )
304 AND EXISTS
305 (SELECT lookup_code
306 FROM fnd_lookup_values flv
307 WHERE flv.lookup_type = 'UNIT'
308 AND flv.meaning <> mtl_uom.unit_of_measure_tl
309 AND flv.lookup_code = mtl_uom.uom_code
310 AND flv.view_application_id = 275
311 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
312 AND flv.language = mtl_uom.language
313 )
314 AND NOT EXISTS
315 (SELECT 1
316 FROM fnd_lookup_values flv1
317 WHERE flv1.lookup_type = 'UNIT'
318 AND flv1.meaning = '@'||mtl_uom.unit_of_measure_tl
319 AND flv1.view_application_id = 275
320 AND flv1.security_group_id = fnd_global.lookup_security_group('UNIT',275)
321 AND flv1.language = mtl_uom.language
322 );
323 /* Removed check for bug 4130638
324 AND EXISTS
325 ( SELECT 1
326 FROM pa_transaction_interface_all pa
327 WHERE pa.unit_of_measure = mtl_uom.uom_code
328 ) ;
329 */
330
331 CURSOR cur_chg_uom_meaning_1 (p_uom_code IN VARCHAR2) IS/*cursor to select records where different UOM code exists for the same meaning */
332 SELECT language
333 ,uom_code
334 ,disable_date
335 ,unit_of_measure_tl
336 ,description
337 ,source_lang
338 ,attribute_category
339 ,attribute1
340 ,attribute2
341 ,attribute3
342 ,attribute4
343 ,attribute5
344 ,attribute6
345 ,attribute7
346 ,attribute8
347 ,attribute9
348 ,attribute10
349 ,attribute11
350 ,attribute12
351 ,attribute13
352 ,attribute14
353 ,attribute15
357 AND EXISTS
354 FROM Mtl_Units_Of_Measure_TL mtl_uom
355 WHERE /* language = userenv('LANG') */
356 mtl_uom.uom_code = p_uom_code
358 (SELECT lookup_code
359 FROM fnd_lookup_values flv
360 WHERE flv.lookup_type = 'UNIT'
361 AND flv.meaning = mtl_uom.unit_of_measure_tl
362 AND flv.lookup_code <> mtl_uom.uom_code
363 AND flv.view_application_id = 275
364 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
365 AND flv.language = mtl_uom.language
366 )
367 AND NOT EXISTS
368 (SELECT 1
369 FROM fnd_lookup_values flv1
370 WHERE flv1.lookup_type = 'UNIT'
371 AND flv1.lookup_code = mtl_uom.uom_code
372 AND flv1.view_application_id = 275
373 AND flv1.security_group_id = fnd_global.lookup_security_group('UNIT',275)
374 AND flv1.language = mtl_uom.language
375 )
376 AND rowid = ( /*Added this condition for the bug 13539202 */
377 SELECT max(rowid) from Mtl_Units_Of_Measure_TL mtl_uom_2
378 WHERE mtl_uom.unit_of_measure_tl = mtl_uom_2.unit_of_measure_tl
379 AND mtl_uom.language = mtl_uom_2.language
380 );
381
382 CURSOR cur_chg_uom_meaning IS/*cursor to select records where different UOM code exists for the same meaning */
383 SELECT language
384 ,uom_code
385 ,disable_date
386 ,unit_of_measure_tl
387 ,description
388 ,source_lang
389 ,attribute_category
390 ,attribute1
391 ,attribute2
392 ,attribute3
393 ,attribute4
394 ,attribute5
395 ,attribute6
396 ,attribute7
397 ,attribute8
398 ,attribute9
399 ,attribute10
400 ,attribute11
401 ,attribute12
402 ,attribute13
403 ,attribute14
404 ,attribute15
405 FROM Mtl_Units_Of_Measure_TL mtl_uom
406 WHERE /* language = userenv('LANG') */
407 EXISTS (SELECT UOM_CODE FROM MTL_UOM_CONVERSIONS MUC_UOM
408 WHERE MUC_UOM.UOM_CODE = MTL_UOM.UOM_CODE
409 )
410 AND EXISTS
411 (SELECT lookup_code
412 FROM fnd_lookup_values flv
413 WHERE flv.lookup_type = 'UNIT'
414 AND flv.meaning = mtl_uom.unit_of_measure_tl
415 AND flv.lookup_code <> mtl_uom.uom_code
416 AND flv.view_application_id = 275
417 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
418 AND flv.language = mtl_uom.language
419 )
420 AND NOT EXISTS
421 (SELECT 1
422 FROM fnd_lookup_values flv1
423 WHERE flv1.lookup_type = 'UNIT'
424 AND flv1.lookup_code = mtl_uom.uom_code
425 AND flv1.view_application_id = 275
426 AND flv1.security_group_id = fnd_global.lookup_security_group('UNIT',275)
427 AND flv1.language = mtl_uom.language
428 )
429 AND rowid = ( /*Added this condition for the bug 13539202 */
430 SELECT max(rowid) from Mtl_Units_Of_Measure_TL mtl_uom_2
431 WHERE mtl_uom.unit_of_measure_tl = mtl_uom_2.unit_of_measure_tl
432 AND mtl_uom.language = mtl_uom_2.language
433 );
434
435 /* Removed check for bug 4130638
436 AND EXISTS
437 ( SELECT 1
438 FROM pa_transaction_interface_all pa
439 WHERE pa.unit_of_measure = mtl_uom.uom_code
440 ) ;
441 */
442
443
444 CURSOR cur_new_uom_1(p_uom_code IN VARCHAR2 ) IS /* cursor to select new unit of measure codes which are not present in pa_lookup */
445 SELECT language
446 ,uom_code
447 ,disable_date
448 ,unit_of_measure_tl
449 ,description
450 ,source_lang
451 ,attribute_category
452 ,attribute1
453 ,attribute2
454 ,attribute3
455 ,attribute4
456 ,attribute5
457 ,attribute6
458 ,attribute7
459 ,attribute8
460 ,attribute9
461 ,attribute10
462 ,attribute11
463 ,attribute12
464 ,attribute13
465 ,attribute14
466 ,attribute15
467 FROM Mtl_Units_Of_Measure_TL mtl_uom
468 WHERE mtl_uom.uom_code = p_uom_code
469 /* AND language = userenv('LANG') */
470 AND NOT EXISTS
471 (SELECT lookup_code
472 FROM fnd_lookup_values flv
473 WHERE flv.lookup_type = 'UNIT'
474 AND (flv.meaning = mtl_uom.unit_of_measure_tl OR flv.lookup_code = mtl_uom.uom_code)
475 AND flv.view_application_id = 275
476 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
477 AND flv.language = mtl_uom.language
478 )
479 AND rowid = (
480 SELECT max(rowid) from Mtl_Units_Of_Measure_TL mtl_uom_2
481 WHERE mtl_uom.unit_of_measure_tl = mtl_uom_2.unit_of_measure_tl
482 AND mtl_uom.language = mtl_uom_2.language
483 ); /*Bug 6740153 added the rowid condition to fetch unique record only */
484 CURSOR cur_new_uom IS /* cursor to select new unit of measure codes which are not present in pa_lookup */
485 SELECT language
486 ,uom_code
487 ,disable_date
488 ,unit_of_measure_tl
489 ,description
490 ,source_lang
491 ,attribute_category
492 ,attribute1
493 ,attribute2
494 ,attribute3
495 ,attribute4
496 ,attribute5
497 ,attribute6
498 ,attribute7
499 ,attribute8
500 ,attribute9
501 ,attribute10
502 ,attribute11
506 ,attribute15
503 ,attribute12
504 ,attribute13
505 ,attribute14
507 FROM Mtl_Units_Of_Measure_TL mtl_uom
508 WHERE /* language = userenv('LANG') */
509 EXISTS (SELECT UOM_CODE FROM MTL_UOM_CONVERSIONS MUC_UOM
510 WHERE MUC_UOM.UOM_CODE = MTL_UOM.UOM_CODE
511 )
512 AND NOT EXISTS
513 (SELECT lookup_code
514 FROM fnd_lookup_values flv
515 WHERE flv.lookup_type = 'UNIT'
516 AND (flv.meaning = mtl_uom.unit_of_measure_tl OR flv.lookup_code = mtl_uom.uom_code)
517 AND flv.view_application_id = 275
518 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
519 --AND flv.language = mtl_uom.language /*Bug 13899806 Commented the condition*/
520 )
521 AND rowid = (
522 SELECT max(rowid) from Mtl_Units_Of_Measure_TL mtl_uom_2
523 WHERE mtl_uom.unit_of_measure_tl = mtl_uom_2.unit_of_measure_tl
524 AND mtl_uom.language = mtl_uom_2.language
525 ); /*Bug 6740153 added the rowid condition to fetch unique record only */
526 /* Removed check for bug 4130638
527 AND EXISTS
528 ( SELECT 1
529 FROM pa_transaction_interface_all pa
530 WHERE pa.unit_of_measure = mtl_uom.uom_code
531 ) ;
532 */
533 BEGIN --Function Get_UOM Begins
534
535 l_conc_login_id := fnd_global.CONC_LOGIN_ID;
536 l_security_group_id := fnd_global.lookup_security_group('UNIT',275);
537 l_return_str := 'S';
538
539 /* Bug 6740153 Changed the logic so that new UOMS defined in MTL are inserted first
540 (only one record per meaning) so that the next record is updated with @meaning */
541 -------------------------------------------------------------------------------
542 ---------------------------- Insert New UOMs ----------------------------
543 -------------------------------------------------------------------------------
544
545 If p_uom_code IS NOT NULL then -- When PJF team calls they pass p_uom_code
546 FOR rec_new_uom_1 IN cur_new_uom_1 ( p_uom_code )
547 LOOP
548 fnd_lookup_values_pkg.insert_row (
549 x_rowid => l_rowid,
550 x_lookup_type => 'UNIT',
551 x_security_group_id => l_security_group_id,
552 x_view_application_id => 275,
553 x_lookup_code => rec_new_uom_1.Uom_Code,
554 x_tag => NULL,
555 x_attribute_category => rec_new_uom_1.Attribute_Category,
556 x_attribute1 => rec_new_uom_1.Attribute1,
557 x_attribute2 => rec_new_uom_1.Attribute2,
558 x_attribute3 => rec_new_uom_1.Attribute3,
559 x_attribute4 => rec_new_uom_1.Attribute4,
560 x_enabled_flag => 'Y',
561 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
562 x_end_date_active => rec_new_uom_1.disable_date,
563 x_territory_code => NULL,
564 x_attribute5 => rec_new_uom_1.Attribute5,
565 x_attribute6 => rec_new_uom_1.Attribute6,
566 x_attribute7 => rec_new_uom_1.Attribute7,
567 x_attribute8 => rec_new_uom_1.Attribute8,
568 x_attribute9 => rec_new_uom_1.Attribute9,
569 x_attribute10 => rec_new_uom_1.Attribute10,
570 x_attribute11 => rec_new_uom_1.Attribute11,
571 x_attribute12 => rec_new_uom_1.Attribute12,
572 x_attribute13 => rec_new_uom_1.Attribute13,
573 x_attribute14 => rec_new_uom_1.Attribute14,
574 x_attribute15 => rec_new_uom_1.Attribute15,
575 x_meaning => rec_new_uom_1.unit_of_measure_tl,
576 x_description => rec_new_uom_1.description,
577 x_creation_date => SYSDATE,
578 x_created_by => P_user_id,
579 x_last_update_date => SYSDATE,
580 x_last_updated_by => P_user_id,
581 x_last_update_login => l_conc_login_id);
582 END LOOP ;
583 else -- When PA calls p_uom_code will be NULL
584 FOR rec_new_uom IN cur_new_uom
585 LOOP
586 fnd_lookup_values_pkg.insert_row (
587 x_rowid => l_rowid,
588 x_lookup_type => 'UNIT',
589 x_security_group_id => l_security_group_id,
590 x_view_application_id => 275,
591 x_lookup_code => rec_new_uom.Uom_Code,
592 x_tag => NULL,
593 x_attribute_category => rec_new_uom.Attribute_Category,
594 x_attribute1 => rec_new_uom.Attribute1,
595 x_attribute2 => rec_new_uom.Attribute2,
596 x_attribute3 => rec_new_uom.Attribute3,
597 x_attribute4 => rec_new_uom.Attribute4,
598 x_enabled_flag => 'Y',
599 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
600 x_end_date_active => rec_new_uom.disable_date,
601 x_territory_code => NULL,
602 x_attribute5 => rec_new_uom.Attribute5,
603 x_attribute6 => rec_new_uom.Attribute6,
604 x_attribute7 => rec_new_uom.Attribute7,
605 x_attribute8 => rec_new_uom.Attribute8,
606 x_attribute9 => rec_new_uom.Attribute9,
607 x_attribute10 => rec_new_uom.Attribute10,
608 x_attribute11 => rec_new_uom.Attribute11,
609 x_attribute12 => rec_new_uom.Attribute12,
610 x_attribute13 => rec_new_uom.Attribute13,
611 x_attribute14 => rec_new_uom.Attribute14,
612 x_attribute15 => rec_new_uom.Attribute15,
613 x_meaning => rec_new_uom.unit_of_measure_tl,
614 x_description => rec_new_uom.description,
615 x_creation_date => SYSDATE,
616 x_created_by => P_user_id,
617 x_last_update_date => SYSDATE,
618 x_last_updated_by => P_user_id,
619 x_last_update_login => l_conc_login_id);
620 END LOOP ;
621
622 end if ;
623
624 -------------------------------------------------------------------------------
625 --------------------------- Update Existing UOMs ----------------------------
626 -------------------------------------------------------------------------------
627
628
629 /***** Update UOM Code in FND LOOKUP VALUES from PLSQL Tables. Here all ****/
630 /***** the UOM Codes where meaning is changed in MTL, will be updated to ****/
631 /***** Reflect the same in Lookups ****/
632 If p_uom_code IS NOT NULL then -- When PJF team calls they pass p_uom_code
636 x_lookup_type => 'UNIT',
633 FOR rec_chg_uom_code_1 IN cur_chg_uom_code_1(p_uom_code )
634 LOOP
635 fnd_lookup_values_pkg.update_row (
637 x_security_group_id => l_security_group_id,
638 x_view_application_id => 275,
639 x_lookup_code => rec_chg_uom_code_1.Uom_Code,
640 x_tag => NULL,
641 x_attribute_category => rec_chg_uom_code_1.Attribute_Category,
642 x_attribute1 => rec_chg_uom_code_1.Attribute1,
643 x_attribute2 => rec_chg_uom_code_1.Attribute2,
644 x_attribute3 => rec_chg_uom_code_1.Attribute3,
645 x_attribute4 => rec_chg_uom_code_1.Attribute4,
646 x_enabled_flag => 'Y',
647 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
648 x_end_date_active => rec_chg_uom_code_1.disable_date,
649 x_territory_code => NULL,
650 x_attribute5 => rec_chg_uom_code_1.Attribute5,
651 x_attribute6 => rec_chg_uom_code_1.Attribute6,
652 x_attribute7 => rec_chg_uom_code_1.Attribute7,
653 x_attribute8 => rec_chg_uom_code_1.Attribute8,
654 x_attribute9 => rec_chg_uom_code_1.Attribute9,
655 x_attribute10 => rec_chg_uom_code_1.Attribute10,
656 x_attribute11 => rec_chg_uom_code_1.Attribute11,
657 x_attribute12 => rec_chg_uom_code_1.Attribute12,
658 x_attribute13 => rec_chg_uom_code_1.Attribute13,
659 x_attribute14 => rec_chg_uom_code_1.Attribute14,
660 x_attribute15 => rec_chg_uom_code_1.Attribute15,
661 x_meaning => rec_chg_uom_code_1.unit_of_measure_tl,
662 x_description => rec_chg_uom_code_1.description,
663 x_last_update_date => SYSDATE,
664 x_last_updated_by => P_user_id,
665 x_last_update_login => l_conc_login_id
666 ) ;
667 END LOOP;
668 else -- When PA calls p_uom_code will be NULL
669 FOR rec_chg_uom_code IN cur_chg_uom_code
670 LOOP
671
672 /* commented for bug 5624048
673 fnd_lookup_values_pkg.update_row (
674 x_lookup_type => 'UNIT',
675 x_security_group_id => l_security_group_id,
676 x_view_application_id => 275,
677 x_lookup_code => rec_chg_uom_code.Uom_Code,
678 x_tag => NULL,
679 x_attribute_category => rec_chg_uom_code.Attribute_Category,
680 x_attribute1 => rec_chg_uom_code.Attribute1,
681 x_attribute2 => rec_chg_uom_code.Attribute2,
682 x_attribute3 => rec_chg_uom_code.Attribute3,
683 x_attribute4 => rec_chg_uom_code.Attribute4,
684 x_enabled_flag => 'Y',
685 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
686 x_end_date_active => rec_chg_uom_code.disable_date,
687 x_territory_code => NULL,
688 x_attribute5 => rec_chg_uom_code.Attribute5,
689 x_attribute6 => rec_chg_uom_code.Attribute6,
690 x_attribute7 => rec_chg_uom_code.Attribute7,
691 x_attribute8 => rec_chg_uom_code.Attribute8,
692 x_attribute9 => rec_chg_uom_code.Attribute9,
693 x_attribute10 => rec_chg_uom_code.Attribute10,
694 x_attribute11 => rec_chg_uom_code.Attribute11,
695 x_attribute12 => rec_chg_uom_code.Attribute12,
696 x_attribute13 => rec_chg_uom_code.Attribute13,
697 x_attribute14 => rec_chg_uom_code.Attribute14,
698 x_attribute15 => rec_chg_uom_code.Attribute15,
699 x_meaning => rec_chg_uom_code.unit_of_measure_tl,
700 x_description => rec_chg_uom_code.description,
701 x_last_update_date => SYSDATE,
702 x_last_updated_by => P_user_id,
703 x_last_update_login => l_conc_login_id
704 ) ; */
705
706 /* changed the call for bug 5624048 */
707 update_fnd_lookup_values (
708 x_lookup_type => 'UNIT',
709 x_security_group_id => l_security_group_id,
710 x_view_application_id => 275,
711 x_lookup_code => rec_chg_uom_code.Uom_Code,
712 x_language => rec_chg_uom_code.language,
713 x_tag => NULL,
714 x_attribute_category => rec_chg_uom_code.Attribute_Category,
715 x_attribute1 => rec_chg_uom_code.Attribute1,
716 x_attribute2 => rec_chg_uom_code.Attribute2,
717 x_attribute3 => rec_chg_uom_code.Attribute3,
718 x_attribute4 => rec_chg_uom_code.Attribute4,
719 x_enabled_flag => 'Y',
720 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
721 x_end_date_active => rec_chg_uom_code.disable_date,
722 x_territory_code => NULL,
723 x_attribute5 => rec_chg_uom_code.Attribute5,
724 x_attribute6 => rec_chg_uom_code.Attribute6,
725 x_attribute7 => rec_chg_uom_code.Attribute7,
726 x_attribute8 => rec_chg_uom_code.Attribute8,
727 x_attribute9 => rec_chg_uom_code.Attribute9,
728 x_attribute10 => rec_chg_uom_code.Attribute10,
729 x_attribute11 => rec_chg_uom_code.Attribute11,
730 x_attribute12 => rec_chg_uom_code.Attribute12,
731 x_attribute13 => rec_chg_uom_code.Attribute13,
732 x_attribute14 => rec_chg_uom_code.Attribute14,
733 x_attribute15 => rec_chg_uom_code.Attribute15,
734 x_meaning => rec_chg_uom_code.unit_of_measure_tl,
735 x_description => rec_chg_uom_code.description,
736 x_last_update_date => SYSDATE,
737 x_last_updated_by => P_user_id,
738 x_last_update_login => l_conc_login_id
739 ) ;
740
741 END LOOP;
742
743 end if ;
744
745 If p_uom_code IS NOT NULL then -- When PJF team calls they pass p_uom_code
746 FOR rec_chg_uom_code_y IN cur_chg_uom_code_y(p_uom_code )
747 LOOP
748 fnd_lookup_values_pkg.update_row (
749 x_lookup_type => 'UNIT',
750 x_security_group_id => l_security_group_id,
751 x_view_application_id => 275,
752 x_lookup_code => rec_chg_uom_code_y.Uom_Code,
753 x_tag => NULL,
754 x_attribute_category => rec_chg_uom_code_y.Attribute_Category,
755 x_attribute1 => rec_chg_uom_code_y.Attribute1,
756 x_attribute2 => rec_chg_uom_code_y.Attribute2,
757 x_attribute3 => rec_chg_uom_code_y.Attribute3,
758 x_attribute4 => rec_chg_uom_code_y.Attribute4,
759 x_enabled_flag => 'Y',
760 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
761 x_end_date_active => rec_chg_uom_code_y.disable_date,
762 x_territory_code => NULL,
766 x_attribute8 => rec_chg_uom_code_y.Attribute8,
763 x_attribute5 => rec_chg_uom_code_y.Attribute5,
764 x_attribute6 => rec_chg_uom_code_y.Attribute6,
765 x_attribute7 => rec_chg_uom_code_y.Attribute7,
767 x_attribute9 => rec_chg_uom_code_y.Attribute9,
768 x_attribute10 => rec_chg_uom_code_y.Attribute10,
769 x_attribute11 => rec_chg_uom_code_y.Attribute11,
770 x_attribute12 => rec_chg_uom_code_y.Attribute12,
771 x_attribute13 => rec_chg_uom_code_y.Attribute13,
772 x_attribute14 => rec_chg_uom_code_y.Attribute14,
773 x_attribute15 => rec_chg_uom_code_y.Attribute15,
774 x_meaning => '@'||rec_chg_uom_code_y.unit_of_measure_tl,
775 x_description => rec_chg_uom_code_y.description,
776 x_last_update_date => SYSDATE,
777 x_last_updated_by => P_user_id,
778 x_last_update_login => l_conc_login_id
779 ) ;
780 END LOOP;
781 else -- When PA calls p_uom_code will be NULL
782 FOR rec_chg_uom_code IN cur_chg_uom_code_x
783 LOOP
784 /* changed the call for bug 5624048 */
785 update_fnd_lookup_values (
786 x_lookup_type => 'UNIT',
787 x_security_group_id => l_security_group_id,
788 x_view_application_id => 275,
789 x_lookup_code => rec_chg_uom_code.Uom_Code,
790 x_language => rec_chg_uom_code.language,
791 x_tag => NULL,
792 x_attribute_category => rec_chg_uom_code.Attribute_Category,
793 x_attribute1 => rec_chg_uom_code.Attribute1,
794 x_attribute2 => rec_chg_uom_code.Attribute2,
795 x_attribute3 => rec_chg_uom_code.Attribute3,
796 x_attribute4 => rec_chg_uom_code.Attribute4,
797 x_enabled_flag => 'Y',
798 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
799 x_end_date_active => rec_chg_uom_code.disable_date,
800 x_territory_code => NULL,
801 x_attribute5 => rec_chg_uom_code.Attribute5,
802 x_attribute6 => rec_chg_uom_code.Attribute6,
803 x_attribute7 => rec_chg_uom_code.Attribute7,
804 x_attribute8 => rec_chg_uom_code.Attribute8,
805 x_attribute9 => rec_chg_uom_code.Attribute9,
806 x_attribute10 => rec_chg_uom_code.Attribute10,
807 x_attribute11 => rec_chg_uom_code.Attribute11,
808 x_attribute12 => rec_chg_uom_code.Attribute12,
809 x_attribute13 => rec_chg_uom_code.Attribute13,
810 x_attribute14 => rec_chg_uom_code.Attribute14,
811 x_attribute15 => rec_chg_uom_code.Attribute15,
812 x_meaning => '@'||rec_chg_uom_code.unit_of_measure_tl,
813 x_description => rec_chg_uom_code.description,
814 x_last_update_date => SYSDATE,
815 x_last_updated_by => P_user_id,
816 x_last_update_login => l_conc_login_id
817 ) ;
818 END LOOP;
819
820 end if;
821
822 /***** Insert New UOM Code in FND LOOKUP VALUES from PLSQL Tables. Here ****/
823 /***** all the UOMs in MTL where Meaning meaning exists in Lookups but ****/
824 /***** corresponding Code is different, will be inserted to Reflect the ****/
825 /***** same in Lookups ****/
826
827 If p_uom_code IS NOT NULL then -- When PJF team calls they pass p_uom_code
828 FOR rec_chg_uom_meaning_1 IN cur_chg_uom_meaning_1(p_uom_code )
829 LOOP
830 fnd_lookup_values_pkg.insert_row (
831 x_rowid => l_rowid,
832 x_lookup_type => 'UNIT',
833 x_security_group_id => l_security_group_id,
834 x_view_application_id => 275,
835 x_lookup_code => rec_chg_uom_meaning_1.Uom_Code,
836 x_tag => NULL,
837 x_attribute_category => rec_chg_uom_meaning_1.Attribute_Category,
838 x_attribute1 => rec_chg_uom_meaning_1.Attribute1,
839 x_attribute2 => rec_chg_uom_meaning_1.Attribute2,
840 x_attribute3 => rec_chg_uom_meaning_1.Attribute3,
841 x_attribute4 => rec_chg_uom_meaning_1.Attribute4,
842 x_enabled_flag => 'Y',
843 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
844 x_end_date_active => rec_chg_uom_meaning_1.disable_date,
845 x_territory_code => NULL,
846 x_attribute5 => rec_chg_uom_meaning_1.Attribute5,
847 x_attribute6 => rec_chg_uom_meaning_1.Attribute6,
848 x_attribute7 => rec_chg_uom_meaning_1.Attribute7,
849 x_attribute8 => rec_chg_uom_meaning_1.Attribute8,
850 x_attribute9 => rec_chg_uom_meaning_1.Attribute9,
851 x_attribute10 => rec_chg_uom_meaning_1.Attribute10,
852 x_attribute11 => rec_chg_uom_meaning_1.Attribute11,
853 x_attribute12 => rec_chg_uom_meaning_1.Attribute12,
854 x_attribute13 => rec_chg_uom_meaning_1.Attribute13,
855 x_attribute14 => rec_chg_uom_meaning_1.Attribute14,
856 x_attribute15 => rec_chg_uom_meaning_1.Attribute15,
857 x_meaning => '@'||rec_chg_uom_meaning_1.unit_of_measure_tl,
858 x_description => rec_chg_uom_meaning_1.description,
859 x_creation_date => SYSDATE,
860 x_created_by => P_user_id,
861 x_last_update_date => SYSDATE,
862 x_last_updated_by => P_user_id,
863 x_last_update_login => l_conc_login_id);
864 END LOOP;
865 else -- When PA calls p_uom_code will be NULL
866 FOR rec_chg_uom_meaning IN cur_chg_uom_meaning
867 LOOP
868
869 fnd_lookup_values_pkg.insert_row (
870 x_rowid => l_rowid,
871 x_lookup_type => 'UNIT',
872 x_security_group_id => l_security_group_id,
873 x_view_application_id => 275,
874 x_lookup_code => rec_chg_uom_meaning.Uom_Code,
875 x_tag => NULL,
876 x_attribute_category => rec_chg_uom_meaning.Attribute_Category,
877 x_attribute1 => rec_chg_uom_meaning.Attribute1,
878 x_attribute2 => rec_chg_uom_meaning.Attribute2,
879 x_attribute3 => rec_chg_uom_meaning.Attribute3,
880 x_attribute4 => rec_chg_uom_meaning.Attribute4,
881 x_enabled_flag => 'Y',
882 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
883 x_end_date_active => rec_chg_uom_meaning.disable_date,
884 x_territory_code => NULL,
885 x_attribute5 => rec_chg_uom_meaning.Attribute5,
886 x_attribute6 => rec_chg_uom_meaning.Attribute6,
887 x_attribute7 => rec_chg_uom_meaning.Attribute7,
888 x_attribute8 => rec_chg_uom_meaning.Attribute8,
892 x_attribute12 => rec_chg_uom_meaning.Attribute12,
889 x_attribute9 => rec_chg_uom_meaning.Attribute9,
890 x_attribute10 => rec_chg_uom_meaning.Attribute10,
891 x_attribute11 => rec_chg_uom_meaning.Attribute11,
893 x_attribute13 => rec_chg_uom_meaning.Attribute13,
894 x_attribute14 => rec_chg_uom_meaning.Attribute14,
895 x_attribute15 => rec_chg_uom_meaning.Attribute15,
896 x_meaning => '@'||rec_chg_uom_meaning.unit_of_measure_tl,
897 x_description => rec_chg_uom_meaning.description,
898 x_creation_date => SYSDATE,
899 x_created_by => P_user_id,
900 x_last_update_date => SYSDATE,
901 x_last_updated_by => P_user_id,
902 x_last_update_login => l_conc_login_id);
903 END LOOP;
904
905 end if;
906
907
908
909 RETURN l_return_str;
910
911 EXCEPTION
912
913
914
915 WHEN DUP_VAL_ON_INDEX THEN /*Added Exception for Bug 14483087*/
916 return l_return_str; /* Bug 14483087*/
917
918 WHEN OTHERS THEN
919 l_return_str := 'UNEXPECTED_ERR : '||SQLERRM;
920 return l_return_str;
921 END get_uom;
922
923 END pa_uom;