[Home] [Help]
PACKAGE BODY: APPS.PA_UOM
Source
1 PACKAGE BODY pa_uom AS
2 /* $Header: PATXUOMB.pls 120.3 2008/02/14 06:59:56 anuragar 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
354 FROM Mtl_Units_Of_Measure_TL mtl_uom
355 WHERE /* language = userenv('LANG') */
356 mtl_uom.uom_code = p_uom_code
357 AND EXISTS
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
375 );
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
376
377 CURSOR cur_chg_uom_meaning IS/*cursor to select records where different UOM code exists for the same meaning */
378 SELECT language
379 ,uom_code
380 ,disable_date
381 ,unit_of_measure_tl
382 ,description
383 ,source_lang
384 ,attribute_category
385 ,attribute1
386 ,attribute2
387 ,attribute3
388 ,attribute4
389 ,attribute5
390 ,attribute6
391 ,attribute7
392 ,attribute8
393 ,attribute9
394 ,attribute10
395 ,attribute11
396 ,attribute12
397 ,attribute13
398 ,attribute14
399 ,attribute15
400 FROM Mtl_Units_Of_Measure_TL mtl_uom
401 WHERE /* language = userenv('LANG') */
402 EXISTS (SELECT UOM_CODE FROM MTL_UOM_CONVERSIONS MUC_UOM
403 WHERE MUC_UOM.UOM_CODE = MTL_UOM.UOM_CODE
404 )
405 AND EXISTS
406 (SELECT lookup_code
407 FROM fnd_lookup_values flv
408 WHERE flv.lookup_type = 'UNIT'
409 AND flv.meaning = mtl_uom.unit_of_measure_tl
410 AND flv.lookup_code <> mtl_uom.uom_code
411 AND flv.view_application_id = 275
412 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
413 AND flv.language = mtl_uom.language
414 )
415 AND NOT EXISTS
416 (SELECT 1
417 FROM fnd_lookup_values flv1
418 WHERE flv1.lookup_type = 'UNIT'
419 AND flv1.lookup_code = mtl_uom.uom_code
420 AND flv1.view_application_id = 275
421 AND flv1.security_group_id = fnd_global.lookup_security_group('UNIT',275)
422 AND flv1.language = mtl_uom.language
423 );
424
425 /* Removed check for bug 4130638
426 AND EXISTS
427 ( SELECT 1
428 FROM pa_transaction_interface_all pa
429 WHERE pa.unit_of_measure = mtl_uom.uom_code
430 ) ;
431 */
432
433
434 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 */
435 SELECT language
436 ,uom_code
437 ,disable_date
438 ,unit_of_measure_tl
439 ,description
440 ,source_lang
441 ,attribute_category
442 ,attribute1
443 ,attribute2
444 ,attribute3
445 ,attribute4
446 ,attribute5
447 ,attribute6
448 ,attribute7
449 ,attribute8
450 ,attribute9
451 ,attribute10
452 ,attribute11
453 ,attribute12
454 ,attribute13
455 ,attribute14
456 ,attribute15
457 FROM Mtl_Units_Of_Measure_TL mtl_uom
458 WHERE mtl_uom.uom_code = p_uom_code
459 /* AND language = userenv('LANG') */
460 AND NOT EXISTS
461 (SELECT lookup_code
462 FROM fnd_lookup_values flv
463 WHERE flv.lookup_type = 'UNIT'
464 AND (flv.meaning = mtl_uom.unit_of_measure_tl OR flv.lookup_code = mtl_uom.uom_code)
465 AND flv.view_application_id = 275
466 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
467 AND flv.language = mtl_uom.language
468 )
469 AND rowid = (
470 SELECT max(rowid) from Mtl_Units_Of_Measure_TL mtl_uom_2
471 WHERE mtl_uom.unit_of_measure_tl = mtl_uom_2.unit_of_measure_tl
472 AND mtl_uom.language = mtl_uom_2.language
473 ); /*Bug 6740153 added the rowid condition to fetch unique record only */
474 CURSOR cur_new_uom IS /* cursor to select new unit of measure codes which are not present in pa_lookup */
475 SELECT language
476 ,uom_code
477 ,disable_date
478 ,unit_of_measure_tl
479 ,description
480 ,source_lang
481 ,attribute_category
482 ,attribute1
483 ,attribute2
484 ,attribute3
485 ,attribute4
486 ,attribute5
487 ,attribute6
488 ,attribute7
489 ,attribute8
490 ,attribute9
491 ,attribute10
492 ,attribute11
493 ,attribute12
494 ,attribute13
495 ,attribute14
496 ,attribute15
497 FROM Mtl_Units_Of_Measure_TL mtl_uom
498 WHERE /* language = userenv('LANG') */
499 EXISTS (SELECT UOM_CODE FROM MTL_UOM_CONVERSIONS MUC_UOM
500 WHERE MUC_UOM.UOM_CODE = MTL_UOM.UOM_CODE
501 )
502 AND NOT EXISTS
503 (SELECT lookup_code
504 FROM fnd_lookup_values flv
505 WHERE flv.lookup_type = 'UNIT'
506 AND (flv.meaning = mtl_uom.unit_of_measure_tl OR flv.lookup_code = mtl_uom.uom_code)
507 AND flv.view_application_id = 275
508 AND flv.security_group_id = fnd_global.lookup_security_group('UNIT',275)
509 AND flv.language = mtl_uom.language
510 )
511 AND rowid = (
512 SELECT max(rowid) from Mtl_Units_Of_Measure_TL mtl_uom_2
516 /* Removed check for bug 4130638
513 WHERE mtl_uom.unit_of_measure_tl = mtl_uom_2.unit_of_measure_tl
514 AND mtl_uom.language = mtl_uom_2.language
515 ); /*Bug 6740153 added the rowid condition to fetch unique record only */
517 AND EXISTS
518 ( SELECT 1
519 FROM pa_transaction_interface_all pa
520 WHERE pa.unit_of_measure = mtl_uom.uom_code
521 ) ;
522 */
523 BEGIN --Function Get_UOM Begins
524
525 l_conc_login_id := fnd_global.CONC_LOGIN_ID;
526 l_security_group_id := fnd_global.lookup_security_group('UNIT',275);
527 l_return_str := 'S';
528
529 /* Bug 6740153 Changed the logic so that new UOMS defined in MTL are inserted first
530 (only one record per meaning) so that the next record is updated with @meaning */
531 -------------------------------------------------------------------------------
532 ---------------------------- Insert New UOMs ----------------------------
533 -------------------------------------------------------------------------------
534
535 If p_uom_code IS NOT NULL then -- When PJF team calls they pass p_uom_code
536 FOR rec_new_uom_1 IN cur_new_uom_1 ( p_uom_code )
537 LOOP
538 fnd_lookup_values_pkg.insert_row (
539 x_rowid => l_rowid,
540 x_lookup_type => 'UNIT',
541 x_security_group_id => l_security_group_id,
542 x_view_application_id => 275,
543 x_lookup_code => rec_new_uom_1.Uom_Code,
544 x_tag => NULL,
545 x_attribute_category => rec_new_uom_1.Attribute_Category,
546 x_attribute1 => rec_new_uom_1.Attribute1,
547 x_attribute2 => rec_new_uom_1.Attribute2,
548 x_attribute3 => rec_new_uom_1.Attribute3,
549 x_attribute4 => rec_new_uom_1.Attribute4,
550 x_enabled_flag => 'Y',
551 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
552 x_end_date_active => rec_new_uom_1.disable_date,
553 x_territory_code => NULL,
554 x_attribute5 => rec_new_uom_1.Attribute5,
555 x_attribute6 => rec_new_uom_1.Attribute6,
556 x_attribute7 => rec_new_uom_1.Attribute7,
557 x_attribute8 => rec_new_uom_1.Attribute8,
558 x_attribute9 => rec_new_uom_1.Attribute9,
559 x_attribute10 => rec_new_uom_1.Attribute10,
560 x_attribute11 => rec_new_uom_1.Attribute11,
561 x_attribute12 => rec_new_uom_1.Attribute12,
562 x_attribute13 => rec_new_uom_1.Attribute13,
563 x_attribute14 => rec_new_uom_1.Attribute14,
564 x_attribute15 => rec_new_uom_1.Attribute15,
565 x_meaning => rec_new_uom_1.unit_of_measure_tl,
566 x_description => rec_new_uom_1.description,
567 x_creation_date => SYSDATE,
568 x_created_by => P_user_id,
569 x_last_update_date => SYSDATE,
570 x_last_updated_by => P_user_id,
571 x_last_update_login => l_conc_login_id);
572 END LOOP ;
573 else -- When PA calls p_uom_code will be NULL
574 FOR rec_new_uom IN cur_new_uom
575 LOOP
576 fnd_lookup_values_pkg.insert_row (
577 x_rowid => l_rowid,
578 x_lookup_type => 'UNIT',
579 x_security_group_id => l_security_group_id,
580 x_view_application_id => 275,
581 x_lookup_code => rec_new_uom.Uom_Code,
582 x_tag => NULL,
583 x_attribute_category => rec_new_uom.Attribute_Category,
584 x_attribute1 => rec_new_uom.Attribute1,
585 x_attribute2 => rec_new_uom.Attribute2,
586 x_attribute3 => rec_new_uom.Attribute3,
587 x_attribute4 => rec_new_uom.Attribute4,
588 x_enabled_flag => 'Y',
589 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
590 x_end_date_active => rec_new_uom.disable_date,
591 x_territory_code => NULL,
592 x_attribute5 => rec_new_uom.Attribute5,
593 x_attribute6 => rec_new_uom.Attribute6,
594 x_attribute7 => rec_new_uom.Attribute7,
595 x_attribute8 => rec_new_uom.Attribute8,
596 x_attribute9 => rec_new_uom.Attribute9,
597 x_attribute10 => rec_new_uom.Attribute10,
598 x_attribute11 => rec_new_uom.Attribute11,
599 x_attribute12 => rec_new_uom.Attribute12,
600 x_attribute13 => rec_new_uom.Attribute13,
601 x_attribute14 => rec_new_uom.Attribute14,
602 x_attribute15 => rec_new_uom.Attribute15,
603 x_meaning => rec_new_uom.unit_of_measure_tl,
604 x_description => rec_new_uom.description,
605 x_creation_date => SYSDATE,
606 x_created_by => P_user_id,
607 x_last_update_date => SYSDATE,
608 x_last_updated_by => P_user_id,
609 x_last_update_login => l_conc_login_id);
610 END LOOP ;
611
612 end if ;
613
614 -------------------------------------------------------------------------------
615 --------------------------- Update Existing UOMs ----------------------------
616 -------------------------------------------------------------------------------
617
618
619 /***** Update UOM Code in FND LOOKUP VALUES from PLSQL Tables. Here all ****/
620 /***** the UOM Codes where meaning is changed in MTL, will be updated to ****/
621 /***** Reflect the same in Lookups ****/
622 If p_uom_code IS NOT NULL then -- When PJF team calls they pass p_uom_code
623 FOR rec_chg_uom_code_1 IN cur_chg_uom_code_1(p_uom_code )
624 LOOP
625 fnd_lookup_values_pkg.update_row (
626 x_lookup_type => 'UNIT',
627 x_security_group_id => l_security_group_id,
628 x_view_application_id => 275,
629 x_lookup_code => rec_chg_uom_code_1.Uom_Code,
630 x_tag => NULL,
631 x_attribute_category => rec_chg_uom_code_1.Attribute_Category,
632 x_attribute1 => rec_chg_uom_code_1.Attribute1,
636 x_enabled_flag => 'Y',
633 x_attribute2 => rec_chg_uom_code_1.Attribute2,
634 x_attribute3 => rec_chg_uom_code_1.Attribute3,
635 x_attribute4 => rec_chg_uom_code_1.Attribute4,
637 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
638 x_end_date_active => rec_chg_uom_code_1.disable_date,
639 x_territory_code => NULL,
640 x_attribute5 => rec_chg_uom_code_1.Attribute5,
641 x_attribute6 => rec_chg_uom_code_1.Attribute6,
642 x_attribute7 => rec_chg_uom_code_1.Attribute7,
643 x_attribute8 => rec_chg_uom_code_1.Attribute8,
644 x_attribute9 => rec_chg_uom_code_1.Attribute9,
645 x_attribute10 => rec_chg_uom_code_1.Attribute10,
646 x_attribute11 => rec_chg_uom_code_1.Attribute11,
647 x_attribute12 => rec_chg_uom_code_1.Attribute12,
648 x_attribute13 => rec_chg_uom_code_1.Attribute13,
649 x_attribute14 => rec_chg_uom_code_1.Attribute14,
650 x_attribute15 => rec_chg_uom_code_1.Attribute15,
651 x_meaning => rec_chg_uom_code_1.unit_of_measure_tl,
652 x_description => rec_chg_uom_code_1.description,
653 x_last_update_date => SYSDATE,
654 x_last_updated_by => P_user_id,
655 x_last_update_login => l_conc_login_id
656 ) ;
657 END LOOP;
658 else -- When PA calls p_uom_code will be NULL
659 FOR rec_chg_uom_code IN cur_chg_uom_code
660 LOOP
661
662 /* commented for bug 5624048
663 fnd_lookup_values_pkg.update_row (
664 x_lookup_type => 'UNIT',
665 x_security_group_id => l_security_group_id,
666 x_view_application_id => 275,
667 x_lookup_code => rec_chg_uom_code.Uom_Code,
668 x_tag => NULL,
669 x_attribute_category => rec_chg_uom_code.Attribute_Category,
670 x_attribute1 => rec_chg_uom_code.Attribute1,
671 x_attribute2 => rec_chg_uom_code.Attribute2,
672 x_attribute3 => rec_chg_uom_code.Attribute3,
673 x_attribute4 => rec_chg_uom_code.Attribute4,
674 x_enabled_flag => 'Y',
675 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
676 x_end_date_active => rec_chg_uom_code.disable_date,
677 x_territory_code => NULL,
678 x_attribute5 => rec_chg_uom_code.Attribute5,
679 x_attribute6 => rec_chg_uom_code.Attribute6,
680 x_attribute7 => rec_chg_uom_code.Attribute7,
681 x_attribute8 => rec_chg_uom_code.Attribute8,
682 x_attribute9 => rec_chg_uom_code.Attribute9,
683 x_attribute10 => rec_chg_uom_code.Attribute10,
684 x_attribute11 => rec_chg_uom_code.Attribute11,
685 x_attribute12 => rec_chg_uom_code.Attribute12,
686 x_attribute13 => rec_chg_uom_code.Attribute13,
687 x_attribute14 => rec_chg_uom_code.Attribute14,
688 x_attribute15 => rec_chg_uom_code.Attribute15,
689 x_meaning => rec_chg_uom_code.unit_of_measure_tl,
690 x_description => rec_chg_uom_code.description,
691 x_last_update_date => SYSDATE,
692 x_last_updated_by => P_user_id,
693 x_last_update_login => l_conc_login_id
694 ) ; */
695
696 /* changed the call for bug 5624048 */
697 update_fnd_lookup_values (
698 x_lookup_type => 'UNIT',
699 x_security_group_id => l_security_group_id,
700 x_view_application_id => 275,
701 x_lookup_code => rec_chg_uom_code.Uom_Code,
702 x_language => rec_chg_uom_code.language,
703 x_tag => NULL,
704 x_attribute_category => rec_chg_uom_code.Attribute_Category,
705 x_attribute1 => rec_chg_uom_code.Attribute1,
706 x_attribute2 => rec_chg_uom_code.Attribute2,
707 x_attribute3 => rec_chg_uom_code.Attribute3,
708 x_attribute4 => rec_chg_uom_code.Attribute4,
709 x_enabled_flag => 'Y',
710 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
711 x_end_date_active => rec_chg_uom_code.disable_date,
712 x_territory_code => NULL,
713 x_attribute5 => rec_chg_uom_code.Attribute5,
714 x_attribute6 => rec_chg_uom_code.Attribute6,
715 x_attribute7 => rec_chg_uom_code.Attribute7,
716 x_attribute8 => rec_chg_uom_code.Attribute8,
717 x_attribute9 => rec_chg_uom_code.Attribute9,
718 x_attribute10 => rec_chg_uom_code.Attribute10,
719 x_attribute11 => rec_chg_uom_code.Attribute11,
720 x_attribute12 => rec_chg_uom_code.Attribute12,
721 x_attribute13 => rec_chg_uom_code.Attribute13,
722 x_attribute14 => rec_chg_uom_code.Attribute14,
723 x_attribute15 => rec_chg_uom_code.Attribute15,
724 x_meaning => rec_chg_uom_code.unit_of_measure_tl,
725 x_description => rec_chg_uom_code.description,
726 x_last_update_date => SYSDATE,
727 x_last_updated_by => P_user_id,
728 x_last_update_login => l_conc_login_id
729 ) ;
730
731 END LOOP;
732
733 end if ;
734
735 If p_uom_code IS NOT NULL then -- When PJF team calls they pass p_uom_code
736 FOR rec_chg_uom_code_y IN cur_chg_uom_code_y(p_uom_code )
737 LOOP
738 fnd_lookup_values_pkg.update_row (
739 x_lookup_type => 'UNIT',
740 x_security_group_id => l_security_group_id,
741 x_view_application_id => 275,
742 x_lookup_code => rec_chg_uom_code_y.Uom_Code,
743 x_tag => NULL,
744 x_attribute_category => rec_chg_uom_code_y.Attribute_Category,
745 x_attribute1 => rec_chg_uom_code_y.Attribute1,
746 x_attribute2 => rec_chg_uom_code_y.Attribute2,
747 x_attribute3 => rec_chg_uom_code_y.Attribute3,
748 x_attribute4 => rec_chg_uom_code_y.Attribute4,
749 x_enabled_flag => 'Y',
750 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
751 x_end_date_active => rec_chg_uom_code_y.disable_date,
752 x_territory_code => NULL,
753 x_attribute5 => rec_chg_uom_code_y.Attribute5,
754 x_attribute6 => rec_chg_uom_code_y.Attribute6,
755 x_attribute7 => rec_chg_uom_code_y.Attribute7,
756 x_attribute8 => rec_chg_uom_code_y.Attribute8,
757 x_attribute9 => rec_chg_uom_code_y.Attribute9,
758 x_attribute10 => rec_chg_uom_code_y.Attribute10,
759 x_attribute11 => rec_chg_uom_code_y.Attribute11,
760 x_attribute12 => rec_chg_uom_code_y.Attribute12,
761 x_attribute13 => rec_chg_uom_code_y.Attribute13,
762 x_attribute14 => rec_chg_uom_code_y.Attribute14,
763 x_attribute15 => rec_chg_uom_code_y.Attribute15,
764 x_meaning => '@'||rec_chg_uom_code_y.unit_of_measure_tl,
765 x_description => rec_chg_uom_code_y.description,
766 x_last_update_date => SYSDATE,
767 x_last_updated_by => P_user_id,
768 x_last_update_login => l_conc_login_id
769 ) ;
770 END LOOP;
771 else -- When PA calls p_uom_code will be NULL
772 FOR rec_chg_uom_code IN cur_chg_uom_code_x
773 LOOP
774 /* changed the call for bug 5624048 */
775 update_fnd_lookup_values (
776 x_lookup_type => 'UNIT',
777 x_security_group_id => l_security_group_id,
778 x_view_application_id => 275,
779 x_lookup_code => rec_chg_uom_code.Uom_Code,
780 x_language => rec_chg_uom_code.language,
781 x_tag => NULL,
782 x_attribute_category => rec_chg_uom_code.Attribute_Category,
783 x_attribute1 => rec_chg_uom_code.Attribute1,
784 x_attribute2 => rec_chg_uom_code.Attribute2,
785 x_attribute3 => rec_chg_uom_code.Attribute3,
786 x_attribute4 => rec_chg_uom_code.Attribute4,
787 x_enabled_flag => 'Y',
788 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
789 x_end_date_active => rec_chg_uom_code.disable_date,
790 x_territory_code => NULL,
791 x_attribute5 => rec_chg_uom_code.Attribute5,
792 x_attribute6 => rec_chg_uom_code.Attribute6,
793 x_attribute7 => rec_chg_uom_code.Attribute7,
794 x_attribute8 => rec_chg_uom_code.Attribute8,
795 x_attribute9 => rec_chg_uom_code.Attribute9,
796 x_attribute10 => rec_chg_uom_code.Attribute10,
797 x_attribute11 => rec_chg_uom_code.Attribute11,
798 x_attribute12 => rec_chg_uom_code.Attribute12,
799 x_attribute13 => rec_chg_uom_code.Attribute13,
800 x_attribute14 => rec_chg_uom_code.Attribute14,
801 x_attribute15 => rec_chg_uom_code.Attribute15,
802 x_meaning => '@'||rec_chg_uom_code.unit_of_measure_tl,
803 x_description => rec_chg_uom_code.description,
804 x_last_update_date => SYSDATE,
805 x_last_updated_by => P_user_id,
806 x_last_update_login => l_conc_login_id
807 ) ;
808 END LOOP;
809
810 end if;
811
812 /***** Insert New UOM Code in FND LOOKUP VALUES from PLSQL Tables. Here ****/
813 /***** all the UOMs in MTL where Meaning meaning exists in Lookups but ****/
814 /***** corresponding Code is different, will be inserted to Reflect the ****/
815 /***** same in Lookups ****/
816
817 If p_uom_code IS NOT NULL then -- When PJF team calls they pass p_uom_code
818 FOR rec_chg_uom_meaning_1 IN cur_chg_uom_meaning_1(p_uom_code )
819 LOOP
820 fnd_lookup_values_pkg.insert_row (
821 x_rowid => l_rowid,
822 x_lookup_type => 'UNIT',
823 x_security_group_id => l_security_group_id,
824 x_view_application_id => 275,
825 x_lookup_code => rec_chg_uom_meaning_1.Uom_Code,
826 x_tag => NULL,
827 x_attribute_category => rec_chg_uom_meaning_1.Attribute_Category,
828 x_attribute1 => rec_chg_uom_meaning_1.Attribute1,
829 x_attribute2 => rec_chg_uom_meaning_1.Attribute2,
830 x_attribute3 => rec_chg_uom_meaning_1.Attribute3,
831 x_attribute4 => rec_chg_uom_meaning_1.Attribute4,
832 x_enabled_flag => 'Y',
833 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
834 x_end_date_active => rec_chg_uom_meaning_1.disable_date,
835 x_territory_code => NULL,
836 x_attribute5 => rec_chg_uom_meaning_1.Attribute5,
837 x_attribute6 => rec_chg_uom_meaning_1.Attribute6,
838 x_attribute7 => rec_chg_uom_meaning_1.Attribute7,
839 x_attribute8 => rec_chg_uom_meaning_1.Attribute8,
840 x_attribute9 => rec_chg_uom_meaning_1.Attribute9,
841 x_attribute10 => rec_chg_uom_meaning_1.Attribute10,
842 x_attribute11 => rec_chg_uom_meaning_1.Attribute11,
843 x_attribute12 => rec_chg_uom_meaning_1.Attribute12,
844 x_attribute13 => rec_chg_uom_meaning_1.Attribute13,
845 x_attribute14 => rec_chg_uom_meaning_1.Attribute14,
846 x_attribute15 => rec_chg_uom_meaning_1.Attribute15,
847 x_meaning => '@'||rec_chg_uom_meaning_1.unit_of_measure_tl,
848 x_description => rec_chg_uom_meaning_1.description,
849 x_creation_date => SYSDATE,
850 x_created_by => P_user_id,
851 x_last_update_date => SYSDATE,
852 x_last_updated_by => P_user_id,
853 x_last_update_login => l_conc_login_id);
854 END LOOP;
855 else -- When PA calls p_uom_code will be NULL
856 FOR rec_chg_uom_meaning IN cur_chg_uom_meaning
857 LOOP
858
859 fnd_lookup_values_pkg.insert_row (
860 x_rowid => l_rowid,
861 x_lookup_type => 'UNIT',
862 x_security_group_id => l_security_group_id,
863 x_view_application_id => 275,
864 x_lookup_code => rec_chg_uom_meaning.Uom_Code,
865 x_tag => NULL,
866 x_attribute_category => rec_chg_uom_meaning.Attribute_Category,
867 x_attribute1 => rec_chg_uom_meaning.Attribute1,
868 x_attribute2 => rec_chg_uom_meaning.Attribute2,
869 x_attribute3 => rec_chg_uom_meaning.Attribute3,
870 x_attribute4 => rec_chg_uom_meaning.Attribute4,
871 x_enabled_flag => 'Y',
872 x_start_date_active => to_date('01/01/1951','DD/MM/YYYY'),
873 x_end_date_active => rec_chg_uom_meaning.disable_date,
874 x_territory_code => NULL,
875 x_attribute5 => rec_chg_uom_meaning.Attribute5,
876 x_attribute6 => rec_chg_uom_meaning.Attribute6,
877 x_attribute7 => rec_chg_uom_meaning.Attribute7,
878 x_attribute8 => rec_chg_uom_meaning.Attribute8,
879 x_attribute9 => rec_chg_uom_meaning.Attribute9,
880 x_attribute10 => rec_chg_uom_meaning.Attribute10,
881 x_attribute11 => rec_chg_uom_meaning.Attribute11,
882 x_attribute12 => rec_chg_uom_meaning.Attribute12,
883 x_attribute13 => rec_chg_uom_meaning.Attribute13,
884 x_attribute14 => rec_chg_uom_meaning.Attribute14,
885 x_attribute15 => rec_chg_uom_meaning.Attribute15,
886 x_meaning => '@'||rec_chg_uom_meaning.unit_of_measure_tl,
887 x_description => rec_chg_uom_meaning.description,
888 x_creation_date => SYSDATE,
889 x_created_by => P_user_id,
890 x_last_update_date => SYSDATE,
891 x_last_updated_by => P_user_id,
892 x_last_update_login => l_conc_login_id);
893 END LOOP;
894
895 end if;
896
897
898
899 RETURN l_return_str;
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 l_return_str := 'UNEXPECTED_ERR : '||SQLERRM;
904 return l_return_str;
905 END get_uom;
906
907 END pa_uom;