[Home] [Help]
PACKAGE BODY: APPS.ITG_SYNCUOMINBOUND_PVT
Source
1 PACKAGE BODY ITG_SyncUOMInbound_PVT AS
2 /* ARCS: $Header: itgvsuib.pls 120.3 2005/12/22 04:07:35 bsaratna noship $
3 * CVS: itgvsuib.pls,v 1.14 2002/12/23 21:20:30 ecoe Exp
4 */
5
6 g_action VARCHAR2(200):= '';
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ITG_SyncUOMInbound_PVT';
9
10 FUNCTION check_size(
11 p_value IN VARCHAR2,
12 p_min IN NUMBER,
13 p_max IN NUMBER,
14 p_desc IN VARCHAR2
15 ) RETURN VARCHAR2 IS
16 BEGIN
17 IF LENGTHB(p_value) NOT BETWEEN p_min AND p_max THEN /* bug 4002567*/
18 ITG_Debug.msg('cs', 'Length check failed for field '||p_desc);
19 ITG_MSG.data_value_error(p_value, p_min, p_max);
20 RAISE FND_API.G_EXC_ERROR;
21 END IF;
22 RETURN p_value;
23 END check_size;
24
25 PROCEDURE insert_uom(
26 p_uom_rec IN mtl_units_of_measure%ROWTYPE
27 ) IS
28 l_count NUMBER := 0;
29 BEGIN
30 g_action := 'UOM parameters validation';
31 IF NVL(p_uom_rec.base_uom_flag, 'z') NOT IN ('Y', 'N') THEN
32 ITG_MSG.missing_element_value('BASEUOMFL', p_uom_rec.base_uom_flag);
33 RAISE FND_API.G_EXC_ERROR;
34 END IF;
35
36
37 -- this condition is not accounted in the NOT EXISTS clause of the insert
38 select count(*)
39 into l_count
40 from mtl_units_of_measure
41 where (uom_code = p_uom_rec.uom_code and unit_of_measure <> p_uom_rec.unit_of_measure)
42 or (uom_code <> p_uom_rec.uom_code and unit_of_measure = p_uom_rec.unit_of_measure);
43
44 if l_count > 0 then
45 itg_msg.dup_uom(p_uom_rec.uom_code,p_uom_rec.unit_of_measure);
46 raise FND_API.G_EXC_ERROR;
47 end if;
48
49 IF p_uom_rec.base_uom_flag = 'Y' THEN
50 SELECT COUNT(*)
51 INTO l_count
52 FROM mtl_units_of_measure
53 WHERE base_uom_flag = 'Y'
54 AND uom_class = p_uom_rec.uom_class;
55 IF l_count > 0 THEN
56 ITG_MSG.toomany_base_uom_flag;
57 RAISE FND_API.G_EXC_ERROR;
58 END IF;
59 END IF;
60
61 g_action := 'UOM insert';
62 INSERT INTO mtl_units_of_measure_tl (
63 unit_of_measure,
64 unit_of_measure_tl,
65 uom_code,
66 uom_class,
67 base_uom_flag,
68 disable_date,
69 description,
70 last_update_date,
71 last_updated_by,
72 creation_date,
73 created_by,
74 language,
75 source_lang
76 ) SELECT p_uom_rec.unit_of_measure,
77 p_uom_rec.unit_of_measure,
78 p_uom_rec.uom_code,
79 p_uom_rec.uom_class,
80 p_uom_rec.base_uom_flag,
81 p_uom_rec.disable_date,
82 p_uom_rec.description,
83 p_uom_rec.last_update_date,
84 p_uom_rec.last_updated_by,
85 p_uom_rec.creation_date,
86 p_uom_rec.created_by,
87 l.language_code,
88 USERENV('LANG')
89 FROM FND_LANGUAGES l
90 WHERE l.installed_flag IN ('I', 'B')
91 AND NOT EXISTS (
92 SELECT NULL
93 FROM mtl_units_of_measure_tl t
94 WHERE (t.unit_of_measure = p_uom_rec.unit_of_measure
95 OR t.uom_code = p_uom_rec.uom_code)
96 AND t.language = l.language_code);
97 END insert_uom;
98
99 PROCEDURE delete_uom(
100 p_uom_rec IN mtl_units_of_measure%ROWTYPE
101 ) IS
102 BEGIN
103 g_action := 'UOM disable';
104 IF p_uom_rec.disable_date IS NULL THEN
105 ITG_MSG.null_disable_date;
106 RAISE FND_API.G_EXC_ERROR;
107 END IF;
108
109 UPDATE mtl_units_of_measure_tl
110 SET disable_date = p_uom_rec.disable_date,
111 last_update_date = p_uom_rec.last_update_date,
112 last_updated_by = p_uom_rec.last_updated_by,
113 --unit_of_measure_tl = p_uom_rec.unit_of_measure_tl, /*null update fails?*/
114 source_lang = USERENV('LANG')
115 WHERE unit_of_measure = p_uom_rec.unit_of_measure
116 AND uom_code = p_uom_rec.uom_code
117 AND uom_class = p_uom_rec.uom_class
118 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
119
120 IF SQL%ROWCOUNT = 0 THEN
121 ITG_MSG.no_uom(p_uom_rec.unit_of_measure||'-'||p_uom_rec.uom_code||'-'||p_uom_rec.uom_class);
122 RAISE FND_API.G_EXC_ERROR;
123 END IF;
124 END delete_uom;
125
126 PROCEDURE update_uom(
127 p_uom_rec IN mtl_units_of_measure%ROWTYPE
128 ) IS
129 BEGIN
130 g_action := 'UOM update';
131 UPDATE mtl_units_of_measure_tl
132 SET description = p_uom_rec.description,
133 disable_date = p_uom_rec.disable_date,
134 --unit_of_measure_tl = p_uom_rec.unit_of_measure_tl, /*null update?*/
135 source_lang = USERENV('LANG'),
136 last_update_date = p_uom_rec.last_update_date,
137 last_updated_by = p_uom_rec.last_updated_by
138 WHERE unit_of_measure = p_uom_rec.unit_of_measure
139 AND uom_code = p_uom_rec.uom_code
140 AND uom_class = p_uom_rec.uom_class
141 AND USERENV('LANG') IN (language, source_lang);
142
143 IF SQL%ROWCOUNT = 0 THEN
144 ITG_MSG.no_uom(p_uom_rec.unit_of_measure||'-'||p_uom_rec.uom_code||'-'||p_uom_rec.uom_class);
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147 END update_uom;
148
149 PROCEDURE valid_uom_class(
150 p_uom_class IN VARCHAR2,
151 p_xns_date IN DATE
152 ) IS
153 l_dummy DATE;
154 l_found BOOLEAN := FALSE;
155
156 CURSOR uom_class_csr IS
157 SELECT disable_date
158 FROM mtl_uom_classes
159 WHERE uom_class = p_uom_class;
160 BEGIN
161 g_action := 'UOMClass validation';
162 IF p_uom_class IS NOT NULL THEN
163 OPEN uom_class_csr;
164 FETCH uom_class_csr INTO l_dummy;
165 l_found := uom_class_csr%FOUND;
166 CLOSE uom_class_csr;
167 IF l_found AND NVL(p_xns_date, l_dummy) <= l_dummy THEN
168 RETURN;
169 END IF;
170 END IF;
171
172 ITG_MSG.missing_element_value('UOMCLASS', p_uom_class);
173 RAISE FND_API.G_EXC_ERROR;
174 END valid_uom_class;
175
176 FUNCTION valid_number(
177 p_value IN VARCHAR2,
178 p_name IN VARCHAR2
179 ) RETURN NUMBER IS
180 BEGIN
181 RETURN TO_NUMBER(p_value);
182 EXCEPTION
183 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
184 ITG_MSG.missing_element_value(p_name, p_value);
185 RAISE FND_API.G_EXC_ERROR;
186 END valid_number;
187
188 FUNCTION reduce_conv_rate (
189 p_numerator IN VARCHAR2,
190 p_denominator IN VARCHAR2,
191 p_fr_conv_fact IN NUMBER := 1,
192 p_to_conv_fact IN NUMBER := 1
193 ) RETURN VARCHAR2 IS
194 l_fr_rate NUMBER := 0;
195 l_to_rate NUMBER := 1;
196 l_result NUMBER;
197 BEGIN
198 /* Validate and convert each factor, calculate fraction */
199 l_result := (valid_number(p_numerator, 'FROMFACTOR') * p_fr_conv_fact) /
200 (valid_number(p_denominator, 'TOFACTOR' ) * p_to_conv_fact);
201
202 /* Reduce the fraction to a decimal value */
203 RETURN SUBSTRB(TO_CHAR(l_result), 1, 40);/* bug 4002567*/
204 EXCEPTION
205 WHEN OTHERS THEN
206 itg_msg.uomconvrate_err;
207 RAISE FND_API.G_EXC_ERROR;
208 END reduce_conv_rate;
209
210 FUNCTION cross_validate(
211 p_uom IN VARCHAR2,
212 p_uom_code IN VARCHAR2,
213 p_uom_class IN VARCHAR2
214 ) RETURN VARCHAR2 IS
215 l_base mtl_units_of_measure.base_uom_flag%TYPE;
216 BEGIN
217 SELECT base_uom_flag
218 INTO l_base
219 FROM mtl_units_of_measure
220 WHERE unit_of_measure = p_uom
221 AND uom_code = p_uom_code
222 AND uom_class = p_uom_class;
223
224 RETURN l_base;
225
226 EXCEPTION
227 WHEN NO_DATA_FOUND THEN
228 ITG_MSG.bad_uom_crossval;
229 RAISE FND_API.G_EXC_ERROR;
230 END cross_validate;
231
232 FUNCTION get_default_conv_flag(
233 p_uom_code IN VARCHAR2,
234 p_uom_class IN VARCHAR2,
235 p_conv_rate IN VARCHAR2
236 ) RETURN VARCHAR2 IS
237 l_dummy NUMBER;
238 BEGIN
239 SELECT 1
240 INTO l_dummy
241 FROM mtl_uom_conversions
242 WHERE uom_code = p_uom_code
243 AND uom_class = p_uom_class
244 AND inventory_item_id = 0
245 AND conversion_rate = TO_NUMBER(p_conv_rate);
246 RETURN 'Y';
247
248 EXCEPTION
249 WHEN NO_DATA_FOUND THEN
250 RETURN 'N';
251 END get_default_conv_flag;
252
253 FUNCTION get_in_base(
254 p_uom_code IN VARCHAR2,
255 p_itemid IN VARCHAR2
256 ) RETURN NUMBER IS
257 l_conv_rate NUMBER;
258 BEGIN
259 SELECT conversion_rate
260 INTO l_conv_rate
261 FROM mtl_uom_conversions
262 WHERE uom_code = p_uom_code AND inventory_item_id = p_itemid;
263 RETURN l_conv_rate;
264
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 ITG_MSG.conv_not_found(p_uom_code);
268 RAISE FND_API.G_EXC_ERROR;
269 END get_in_base;
270
271 FUNCTION get_base_uom(
272 p_uom_code IN VARCHAR2
273 ) RETURN VARCHAR2 IS
274 l_base_uom_code mtl_units_of_measure.uom_code%TYPE;
275 BEGIN
276 SELECT m2.uom_code
277 INTO l_base_uom_code
278 FROM mtl_units_of_measure m1,
279 mtl_units_of_measure m2
280 WHERE m1.uom_code = p_uom_code
281 AND m1.uom_class = m2.uom_class
282 AND m2.base_uom_flag = 'Y';
283 RETURN l_base_uom_code;
284
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287 ITG_MSG.base_uom_not_found(p_uom_code);
288 RAISE FND_API.G_EXC_ERROR;
289 END get_base_uom;
290
291 PROCEDURE delete_uom_class(
292 p_cls_rec IN mtl_uom_classes%ROWTYPE
293 ) IS
294 BEGIN
295 g_action := 'UOMClass parameter validation';
296 IF p_cls_rec.disable_date IS NULL THEN
297 ITG_MSG.null_disable_date;
298 RAISE FND_API.G_EXC_ERROR;
299 END IF;
300 g_action := 'UOMClass disable';
301 UPDATE mtl_uom_classes_tl
302 SET disable_date = p_cls_rec.disable_date,
303 last_update_date = p_cls_rec.last_update_date,
304 last_updated_by = p_cls_rec.last_updated_by,
305 --uom_class_tl = p_cls_rec.uom_class_tl, /*null update fails*/
306 source_lang = USERENV('LANG')
307 WHERE uom_class = p_cls_rec.uom_class
308 AND USERENV('LANG') IN (language, source_lang);
309
310 IF SQL%ROWCOUNT = 0 THEN
311 ITG_MSG.no_uom_class(p_cls_rec.uom_class);
312 RAISE FND_API.G_EXC_ERROR;
313 END IF;
314 END delete_uom_class;
315
316 PROCEDURE update_uom_class(
317 p_cls_rec IN mtl_uom_classes%ROWTYPE
318 ) IS
319 BEGIN
320 g_action := 'UOMClass update';
321 UPDATE mtl_uom_classes_tl
322 SET description = p_cls_rec.description,
323 disable_date = p_cls_rec.disable_date,
324 last_update_date = p_cls_rec.last_update_date,
325 last_updated_by = p_cls_rec.last_updated_by,
326 --uom_class_tl = p_cls_rec.uom_class_tl, /*null update fails*/
327 source_lang = USERENV('LANG')
328 WHERE uom_class = p_cls_rec.uom_class
329 AND USERENV('LANG') IN (language, source_lang);
330
331 IF SQL%ROWCOUNT = 0 THEN
332 ITG_MSG.no_uom_class(p_cls_rec.uom_class);
333 RAISE FND_API.G_EXC_ERROR;
334 END IF;
335 END update_uom_class;
336
337 PROCEDURE insert_uom_class(
338 p_cls_rec IN mtl_uom_classes%ROWTYPE
339 ) IS
340 BEGIN
341 g_action := 'UOMClass insert';
342 INSERT INTO mtl_uom_classes_tl (
343 uom_class,
344 uom_class_tl,
345 disable_date,
346 description,
347 last_update_date,
348 last_updated_by,
349 creation_date,
350 created_by,
351 language,
352 source_lang
353 ) SELECT p_cls_rec.uom_class,
354 p_cls_rec.uom_class,
355 p_cls_rec.disable_date,
356 p_cls_rec.description,
357 p_cls_rec.last_update_date,
358 p_cls_rec.last_updated_by,
359 p_cls_rec.creation_date,
360 p_cls_rec.created_by,
361 l.language_code,
362 USERENV('LANG')
363 FROM fnd_languages l
364 WHERE l.installed_flag IN ('I', 'B')
365 AND NOT EXISTS
366 (SELECT NULL
367 FROM mtl_uom_classes_tl t
368 WHERE t.uom_class = p_cls_rec.uom_class
369 AND t.language = l.language_code);
370 END insert_uom_class;
371
372 PROCEDURE delete_uom_class_conv(
373 p_ccv_rec IN mtl_uom_class_conversions%ROWTYPE
374 ) IS
375 BEGIN
376 g_action := 'UOMClass-conversion parameter validation';
377 IF p_ccv_rec.disable_date IS NULL THEN
378 ITG_MSG.null_disable_date;
379 RAISE FND_API.G_EXC_ERROR;
380 END IF;
381
382 g_action := 'UOMClassConversion delete';
383 UPDATE mtl_uom_class_conversions
384 SET disable_date = p_ccv_rec.disable_date,
385 last_update_date = p_ccv_rec.last_update_date,
386 last_updated_by = p_ccv_rec.last_updated_by
387 WHERE from_unit_of_measure = p_ccv_rec.from_unit_of_measure
388 AND from_uom_code = p_ccv_rec.from_uom_code
389 AND from_uom_class = p_ccv_rec.from_uom_class
390 AND to_unit_of_measure = p_ccv_rec.to_unit_of_measure
391 AND to_uom_code = p_ccv_rec.to_uom_code
392 AND to_uom_class = p_ccv_rec.to_uom_class;
393
394 IF SQL%ROWCOUNT = 0 THEN
395 ITG_MSG.no_uomclass_conv;
396 RAISE FND_API.G_EXC_ERROR;
397 END IF;
398 END delete_uom_class_conv;
399
400 PROCEDURE update_uom_class_conv(
401 p_ccv_rec IN mtl_uom_class_conversions%ROWTYPE
402 ) IS
403 BEGIN
404 g_action := 'UOMClassConversion update';
405 UPDATE mtl_uom_class_conversions
406 SET conversion_rate = p_ccv_rec.conversion_rate,
407 disable_date = p_ccv_rec.disable_date,
408 last_update_date = p_ccv_rec.last_update_date,
409 last_updated_by = p_ccv_rec.last_updated_by
410 WHERE from_unit_of_measure = p_ccv_rec.from_unit_of_measure
411 AND from_uom_code = p_ccv_rec.from_uom_code
412 AND from_uom_class = p_ccv_rec.from_uom_class
413 AND to_unit_of_measure = p_ccv_rec.to_unit_of_measure
414 AND to_uom_code = p_ccv_rec.to_uom_code
415 AND to_uom_class = p_ccv_rec.to_uom_class;
416
417 IF SQL%ROWCOUNT = 0 THEN
418 ITG_MSG.no_uomclass_conv;
419 RAISE FND_API.G_EXC_ERROR;
420 END IF;
421 END update_uom_class_conv;
422
423
424 PROCEDURE insert_uom_class_conv(
425 p_ccv_rec IN mtl_uom_class_conversions%ROWTYPE
426 ) IS
427 l_count NUMBER;
428 BEGIN
429 g_action := 'UOMClass-conv insert';
430 l_count := 0;
431
432 SELECT count(*)
433 INTO l_count
434 FROM mtl_uom_class_conversions
435 WHERE inventory_item_id = p_ccv_rec.inventory_item_id AND
436 ( to_uom_code = p_ccv_rec.to_uom_code OR
437 to_uom_class = p_ccv_rec.to_uom_class OR
438 to_unit_of_measure = p_ccv_rec.to_unit_of_measure );
439
440 IF l_count > 0 THEN
441 itg_msg.dup_uomclass_conv;
442 RAISE FND_API.G_EXC_ERROR;
443 END IF;
444
445 INSERT INTO mtl_uom_class_conversions (
446 from_unit_of_measure,
447 from_uom_code,
448 from_uom_class,
449 to_unit_of_measure,
450 to_uom_code,
451 to_uom_class,
452 last_update_date,
453 last_updated_by,
454 creation_date,
455 created_by,
456 disable_date,
457 inventory_item_id,
458 conversion_rate
459 ) VALUES (
460 p_ccv_rec.from_unit_of_measure,
461 p_ccv_rec.from_uom_code,
462 p_ccv_rec.from_uom_class,
463 p_ccv_rec.to_unit_of_measure,
464 p_ccv_rec.to_uom_code,
465 p_ccv_rec.to_uom_class,
466 p_ccv_rec.last_update_date,
467 p_ccv_rec.last_updated_by,
468 p_ccv_rec.creation_date,
469 p_ccv_rec.created_by,
470 p_ccv_rec.disable_date,
471 p_ccv_rec.inventory_item_id,
472 p_ccv_rec.conversion_rate
473 );
474 END insert_uom_class_conv;
475
476 PROCEDURE delete_uom_conv(
477 p_con_rec IN mtl_uom_conversions%ROWTYPE
478 ) IS
479 BEGIN
480 g_action := 'UOM-conversion parameter validation';
481 IF p_con_rec.disable_date IS NULL THEN
482 ITG_MSG.null_disable_date;
483 RAISE FND_API.G_EXC_ERROR;
484 END IF;
485 g_action := 'UOM-conversion update';
486 UPDATE mtl_uom_conversions
487 SET disable_date = p_con_rec.disable_date,
488 last_update_date = p_con_rec.last_update_date,
489 last_updated_by = p_con_rec.last_updated_by
490 WHERE unit_of_measure = p_con_rec.unit_of_measure
491 AND uom_code = p_con_rec.uom_code
492 AND uom_class = p_con_rec.uom_class;
493
494 IF SQL%ROWCOUNT = 0 THEN
495 ITG_MSG.no_uom_conv;
496 RAISE FND_API.G_EXC_ERROR;
497 END IF;
498 END delete_uom_conv;
499
500 PROCEDURE update_uom_conv(
501 p_con_rec IN mtl_uom_conversions%ROWTYPE
502 ) IS
503 BEGIN
504 g_action := 'UOM-conversion update';
505 UPDATE mtl_uom_conversions
506 SET conversion_rate = p_con_rec.conversion_rate,
507 disable_date = p_con_rec.disable_date,
508 last_update_date = p_con_rec.last_update_date,
509 last_updated_by = p_con_rec.last_updated_by
510 WHERE unit_of_measure = p_con_rec.unit_of_measure
511 AND uom_code = p_con_rec.uom_code
512 AND uom_class = p_con_rec.uom_class;
513
514 IF SQL%ROWCOUNT = 0 THEN
515 ITG_MSG.no_uom_conv;
516 RAISE FND_API.G_EXC_ERROR;
517 END IF;
518 END update_uom_conv;
519
520 PROCEDURE insert_uom_conv(
521 p_con_rec IN mtl_uom_conversions%ROWTYPE
522 ) IS
523 l_flag_count NUMBER;
524 l_count NUMBER;
525 BEGIN
526 g_action := 'UOM-conversion parameter validation';
527 IF NVL(p_con_rec.default_conversion_flag, 'z') NOT IN ('Y', 'N') THEN
528 ITG_MSG.missing_element_value(
529 'CONVFLAG', p_con_rec.default_conversion_flag);
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532
533
534 select count(*)
535 into l_count
536 from mtl_uom_conversions
537 where inventory_item_id = p_con_rec.inventory_item_id
538 and ( unit_of_measure = p_con_rec.unit_of_measure
539 OR uom_code = p_con_rec.uom_code );
540
541 IF l_count > 0 then
542 itg_msg.dup_uom_conv(p_con_rec.inventory_item_id,
543 p_con_rec.uom_code||'-'||p_con_rec.unit_of_measure);
544 RAISE FND_API.G_EXC_ERROR;
545 end if;
546
547
548 /* Check for multiple flags */
549 IF p_con_rec.default_conversion_flag = 'Y' THEN
550 SELECT COUNT(*)
551 INTO l_flag_count
552 FROM mtl_uom_conversions
553 WHERE default_conversion_flag = 'Y'
554 AND uom_class = p_con_rec.uom_class;
555 IF l_flag_count > 1 THEN
556 ITG_MSG.toomany_default_conv_flag;
557 RAISE FND_API.G_EXC_ERROR;
558 END IF;
559 END IF;
560
561 g_action := 'UOM-conversion insert';
562
563 INSERT INTO mtl_uom_conversions (
564 unit_of_measure,
565 uom_code,
566 uom_class,
567 inventory_item_id,
568 conversion_rate,
569 default_conversion_flag,
570 last_update_date,
571 last_updated_by,
572 creation_date,
573 created_by,
574 disable_date
575 ) VALUES (
576 p_con_rec.unit_of_measure,
577 p_con_rec.uom_code,
578 p_con_rec.uom_class,
579 p_con_rec.inventory_item_id,
580 p_con_rec.conversion_rate,
581 p_con_rec.default_conversion_flag,
582 p_con_rec.last_update_date,
583 p_con_rec.last_updated_by,
584 p_con_rec.creation_date,
585 p_con_rec.created_by,
586 p_con_rec.disable_date
587 );
588
589 END insert_uom_conv;
590
591 /* Handle a task=UOMCLASS request
592 * This procedure is referenced in processUOM, below, and must be
593 * placed ahead of that procedure to avoid a forward reference error.
594 */
595 PROCEDURE process_uom_class (
596 p_syncind IN VARCHAR2,
597 p_uom IN VARCHAR2,
598 p_uomcode IN VARCHAR2,
599 p_uomclass IN VARCHAR2,
600 p_defconflg IN VARCHAR2,
601 p_description IN VARCHAR2,
602 p_dt_creation IN DATE,
603 p_dt_expiration IN DATE
604 ) IS
605 /* Working storage */
606 l_cls_rec mtl_uom_classes%ROWTYPE;
607 l_uom_rec mtl_units_of_measure%ROWTYPE;
608 l_con_rec mtl_uom_conversions%ROWTYPE;
609 l_ccv_rec mtl_uom_class_conversions%ROWTYPE;
610 l_param VARCHAR2(200);
611 l_value VARCHAR2(200);
612 BEGIN
613
614 g_action := 'UOMClass parameter validation';
615 l_param := null;
616
617 IF p_uom IS NULL THEN
618 l_param := 'UOM';
619 l_value := null;
620 ELSIF p_uomcode IS NULL THEN
621 l_param := 'NOTES';
622 l_value := null;
623 ELSIF p_uomclass IS NULL THEN
624 l_param := 'UOMGROUPID';
625 l_value := null;
626 ELSIF nvl(p_defconflg,'x') not in ('Y','N') THEN
627 l_param := 'ORACLEITG.DEFCONFLAG';
628 l_value := p_defconflg;
629 ELSIF nvl(p_syncind,'x') not in ('A','C','D') THEN
630 l_param := 'SYNCIND';
631 l_value := p_syncind;
632 END IF;
633
634 IF l_param IS NOT NULL THEN
635 itg_msg.missing_element_value(l_param,nvl(l_value,'NULL'));
636 RAISE FND_API.G_EXC_ERROR;
637 END IF;
638
639 g_action := 'UOMClass sync';
640
641 /* Get the records together */
642 IF p_syncind NOT IN ('A','C','D') THEN
643 itg_msg.missing_element_value('SYNCIND',p_syncind);
644 RAISE FND_API.G_EXC_ERROR;
645 END IF;
646 l_cls_rec.last_update_date := NVL(p_dt_creation, SYSDATE);
647 l_cls_rec.last_updated_by := FND_GLOBAL.user_id;
648 l_cls_rec.creation_date := l_cls_rec.last_update_date;
649 l_cls_rec.created_by := l_cls_rec.last_updated_by;
650
651 l_cls_rec.disable_date := p_dt_expiration;
652 l_cls_rec.description := check_size(p_description, 0, 50, 'Description');
653 l_cls_rec.uom_class := check_size(p_uomclass, 1, 10, 'UOM Class');
654 l_uom_rec.unit_of_measure := check_size(p_uom, 1, 25, 'Unit of Measure');
655 l_uom_rec.uom_code := check_size(p_uomcode, 1, 3, 'UOM Code');
656 l_uom_rec.base_uom_flag := 'Y';
657
658 l_con_rec.default_conversion_flag := check_size(p_defconflg, 1, 1, 'Default Conversion Flag');
659 l_con_rec.conversion_rate := 1;
660 l_con_rec.inventory_item_id := 0;
661
662 l_uom_rec.last_update_date := l_cls_rec.last_update_date;
663 l_uom_rec.last_updated_by := l_cls_rec.last_updated_by;
664 l_uom_rec.creation_date := l_cls_rec.creation_date;
665 l_uom_rec.created_by := l_cls_rec.created_by;
666 l_uom_rec.uom_class := l_cls_rec.uom_class;
667 l_uom_rec.Description := l_uom_rec.unit_of_measure;
668
669 l_con_rec.last_update_date := l_cls_rec.last_update_date;
670 l_con_rec.last_updated_by := l_cls_rec.last_updated_by;
671 l_con_rec.creation_date := l_cls_rec.creation_date;
672 l_con_rec.created_by := l_cls_rec.created_by;
673 l_con_rec.unit_of_measure := l_uom_rec.unit_of_measure;
674 l_con_rec.uom_class := l_cls_rec.uom_class;
675 l_con_rec.uom_code := l_uom_rec.uom_code;
676
677 l_ccv_rec.last_update_date := l_cls_rec.last_update_date;
678 l_ccv_rec.last_updated_by := l_cls_rec.last_updated_by;
679 l_ccv_rec.creation_date := l_cls_rec.creation_date;
680 l_ccv_rec.created_by := l_cls_rec.created_by;
681 l_ccv_rec.from_unit_of_measure := l_uom_rec.unit_of_measure;
682 l_ccv_rec.to_unit_of_measure := l_uom_rec.unit_of_measure;
683 l_ccv_rec.from_uom_class := l_cls_rec.uom_class;
684 l_ccv_rec.to_uom_class := l_cls_rec.uom_class;
685 l_ccv_rec.from_uom_code := l_uom_rec.uom_code;
686 l_ccv_rec.to_uom_code := l_uom_rec.uom_code;
687 l_ccv_rec.conversion_rate := l_con_rec.conversion_rate;
688 l_ccv_rec.inventory_item_id := l_con_rec.inventory_item_id;
689 g_action := 'UOMCLASS sync';
690
691 -- since date field are not coming from the XML
692 -- set disabledate to current date for delete_uom
693 IF p_syncind = 'D' then
694 l_cls_rec.disable_date := sysdate;
695 l_con_rec.disable_date := sysdate;
696 l_ccv_rec.disable_date := sysdate;
697 l_uom_rec.disable_date := sysdate;
698 END IF;
699
700 /* What are we doing? */
701 IF p_syncind = 'A' THEN
702 insert_uom_class (l_cls_rec);
703 insert_uom_conv (l_con_rec);
704 insert_uom_class_conv(l_ccv_rec);
705 insert_uom (l_uom_rec);
706 ELSIF p_syncind = 'C' THEN
707 update_uom_class (l_cls_rec);
708 update_uom_conv (l_con_rec);
709 update_uom_class_conv(l_ccv_rec);
710 update_uom (l_uom_rec);
711 ELSIF p_syncind = 'D' THEN
712 delete_uom_class (l_cls_rec);
713 delete_uom_conv (l_con_rec);
714 delete_uom_class_conv(l_ccv_rec);
715 delete_uom (l_uom_rec);
716 END IF;
717 END process_uom_class;
718
719 PROCEDURE process_uom(
720 p_syncind IN VARCHAR2,
721 p_uom IN VARCHAR2,
722 p_uomcode IN VARCHAR2,
723 p_uomclass IN VARCHAR2,
724 p_buomflag IN VARCHAR2,
725 p_description IN VARCHAR2,
726 p_dt_creation IN DATE,
727 p_dt_expiration IN DATE
728 ) IS
729 l_uom_rec mtl_units_of_measure%ROWTYPE;
730
731 CURSOR l_def_cls_csr IS
732 SELECT uom_class
733 FROM mtl_units_of_measure
734 WHERE unit_of_measure = l_uom_rec.unit_of_measure;
735 l_param VARCHAR2(200);
736 l_value VARCHAR2(200);
737 BEGIN
738
739 g_action := 'UOM parameter validation';
740 l_param := null;
741
742 IF p_uom IS NULL THEN
743 l_param := 'UOM';
744 l_value := null;
745 ELSIF p_uomcode IS NULL THEN
746 l_param := 'NOTES';
747 l_value := null;
748 ELSIF nvl(p_buomflag,'x') NOT IN ('Y','N') THEN
749 l_param := 'ORACLEITG.BASEUOMFLAG';
750 l_value := p_buomflag;
751 ELSIF nvl(p_syncind,'x') not in ('A','C','D') THEN
752 l_param := 'SYNCIND';
753 l_value := p_syncind;
754 END IF;
755
756 IF l_param IS NOT NULL THEN
757 itg_msg.missing_element_value(l_param,nvl(l_value,'NULL'));
758 RAISE FND_API.G_EXC_ERROR;
759 END IF;
760
761 /* Get the record together */
762 l_uom_rec.last_update_date := NVL(p_dt_creation, SYSDATE);
763 l_uom_rec.last_updated_by := FND_GLOBAL.user_id;
764 l_uom_rec.creation_date := l_uom_rec.last_update_date;
765 l_uom_rec.created_by := l_uom_rec.last_updated_by;
766
767 l_uom_rec.disable_date := p_dt_expiration;
768 l_uom_rec.description := check_size(p_description, 0, 50, 'Description');
769 l_uom_rec.unit_of_measure := check_size(p_uom, 1, 25, 'Unit of Measure');
770 l_uom_rec.uom_code := check_size(p_uomcode, 1, 3, 'UOM Code');
771 l_uom_rec.uom_class := check_size(p_uomclass, 1, 10, 'UOM Class');
772 l_uom_rec.base_uom_flag := check_size(p_buomflag, 1, 1, 'Base UOM Flag');
773
774 IF p_syncind = 'A' AND l_uom_rec.uom_class IS NULL THEN
775 /* This signals that what we are really doing is a processUomClass.
776 * Fill a field segment nested table accordingly, then call the
777 * UOM Class procedure.
778 */
779 g_action := 'UOMClass sync';
780 process_uom_class(
781 p_syncind => p_syncind,
782 p_uom => p_uom,
783 p_uomcode => p_uomcode,
784 p_uomclass => 'SAP'||p_uomcode,
785 p_defconflg => 'Y',
786 p_description => p_description,
787 p_dt_creation => p_dt_creation,
788 p_dt_expiration => p_dt_expiration
789 );
790 ELSE
791 IF l_uom_rec.uom_class IS NULL THEN
792 /* Not inserting a new UOM but no UOM Class passed in.
793 * Have only to look up the class that goes with the UOM,
794 * since the UOM exists in Oracle already.
795 *
796 * MUST set l_uom_rec.unit_of_measure first!
797 */
798 OPEN l_def_cls_csr;
799 FETCH l_def_cls_csr INTO l_uom_rec.uom_class;
800 CLOSE l_def_cls_csr;
801 END IF;
802 /* Make sure the UOM Class is valid */
803 valid_uom_class(l_uom_rec.uom_class, l_uom_rec.creation_date);
804 /* What are we doing? */
805 g_action := 'UOM sync';
806 -- since date field are not coming from the XML
807 -- set disabledate to current date for delete_uom
808 IF p_syncind = 'D' then
809 l_uom_rec.disable_date := sysdate;
810 END IF;
811
812 IF p_syncind = 'A' THEN insert_uom(l_uom_rec);
813 ELSIF p_syncind = 'C' THEN update_uom(l_uom_rec);
814 ELSIF p_syncind = 'D' THEN delete_uom(l_uom_rec);
815 END IF;
816 END IF;
817 END process_uom;
818
819 PROCEDURE process_uom_inter (
820 p_syncind IN VARCHAR2,
821 p_fruomcode IN VARCHAR2,
822 p_touomcode IN VARCHAR2,
823 p_itemid IN VARCHAR2,
824 p_conv_rate IN VARCHAR2,
825 p_dt_creation IN DATE,
826 p_dt_expiration IN DATE
827 ) IS
828 l_ccv_rec mtl_uom_class_conversions%ROWTYPE;
829 l_dummy mtl_units_of_measure.base_uom_flag%TYPE;
830
831 /* Get uom and default the value of uom_class. */
832 CURSOR l_uom_csr(l_uom_code VARCHAR2) IS
833 SELECT uom_class, unit_of_measure
834 FROM mtl_units_of_measure
835 WHERE uom_code = l_uom_code;
836
837 BEGIN
838 g_action := 'UOM conversion parameter validation';
839
840 IF p_syncind NOT IN ('A','C','D') THEN
841 itg_msg.missing_element_value('SYNCIND',p_syncind);
842 RAISE FND_API.G_EXC_ERROR;
843 END IF;
844
845 /* Get the record together */
846 l_ccv_rec.last_update_date := NVL(p_dt_creation, SYSDATE);
847 l_ccv_rec.last_updated_by := FND_GLOBAL.user_id;
848 l_ccv_rec.creation_date := l_ccv_rec.last_update_date;
849 l_ccv_rec.created_by := l_ccv_rec.last_updated_by;
850 l_ccv_rec.disable_date := p_dt_expiration;
851
852 /* Deal with the from_uom_class */
853 l_ccv_rec.from_uom_code :=
854 check_size(p_fruomcode, 1, 3,'From UOM Code');
855 OPEN l_uom_csr(l_ccv_rec.from_uom_code);
856 FETCH l_uom_csr INTO l_ccv_rec.from_uom_class,
857 l_ccv_rec.from_unit_of_measure;
858 CLOSE l_uom_csr;
859 valid_uom_class(l_ccv_rec.from_uom_class, l_ccv_rec.creation_date);
860
861 /* Deal with the to_uom_class */
862 l_ccv_rec.to_uom_code :=
863 check_size(p_touomcode, 1, 3, 'To UOM Code');
864 OPEN l_uom_csr(l_ccv_rec.to_uom_code);
865 FETCH l_uom_csr INTO l_ccv_rec.to_uom_class,
866 l_ccv_rec.to_unit_of_measure;
867 CLOSE l_uom_csr;
868 valid_uom_class(l_ccv_rec.to_uom_class, l_ccv_rec.creation_date);
869
870 l_ccv_rec.conversion_rate := valid_number(p_conv_rate, 'CONVRATE');
871 IF NVL(l_ccv_rec.conversion_rate, 0) <= 0 THEN
872 ITG_MSG.missing_element_value('CONVRATE', p_conv_rate);
873 RAISE FND_API.G_EXC_ERROR;
874 ELSIF p_itemid IS NULL THEN
875 ITG_MSG.missing_element_value('ITEMID', p_itemid);
876 RAISE FND_API.G_EXC_ERROR;
877 ELSE
878 /* l_ccv_rec.inventory_item_id :=
879 po_ip_oagxml_pkg.getItemId(p_itemid, NULL); */
880 NULL;
881 END IF;
882 l_ccv_rec.inventory_item_id := p_itemid;
883 l_dummy := cross_validate(
884 l_ccv_rec.from_unit_of_measure,
885 l_ccv_rec.from_uom_code,
886 l_ccv_rec.from_uom_class);
887
888 l_dummy := cross_validate(
889 l_ccv_rec.to_unit_of_measure,
890 l_ccv_rec.to_uom_code,
891 l_ccv_rec.to_uom_class);
892
893 IF p_syncind = 'D' THEN
894 l_ccv_rec.disable_date := sysdate;
895 END IF;
896
897 g_action := 'UOM conversion sync';
898
899 /* What are we doing? */
900 IF p_syncind = 'A' THEN insert_uom_class_conv(l_ccv_rec);
901 ELSIF p_syncind = 'C' THEN update_uom_class_conv(l_ccv_rec);
902 ELSIF p_syncind = 'D' THEN delete_uom_class_conv(l_ccv_rec);
903 END IF;
904 END process_uom_inter;
905
906 PROCEDURE process_uom_intra (
907 p_syncind IN VARCHAR2,
908 p_uom IN VARCHAR2,
909 p_uomcode IN VARCHAR2,
910 p_uomclass IN VARCHAR2,
911 p_itemid IN VARCHAR2,
912 p_conv_rate IN VARCHAR2,
913 p_def_conv IN VARCHAR2,
914 p_dt_creation IN DATE,
915 p_dt_expiration IN DATE
916 ) IS
917 /* Working storage */
918 l_con_rec mtl_uom_conversions%ROWTYPE;
919 l_base_flag mtl_units_of_measure.base_uom_flag%TYPE;
920
921 /* Default the value of uom_class */
922 CURSOR l_def_cls_csr IS
923 SELECT uom_class
924 FROM mtl_units_of_measure
925 WHERE unit_of_measure = l_con_rec.unit_of_measure;
926 BEGIN
927
928 g_action := 'UOM-conversion parameter validation';
929
930 IF p_syncind NOT IN ('A','C','D') THEN
931 itg_msg.missing_element_value('SYNCIND',p_syncind);
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934
935
936 /* Get the record together */
937 l_con_rec.last_update_date := NVL(p_dt_creation, SYSDATE);
938 l_con_rec.last_updated_by := FND_GLOBAL.user_id;
939 l_con_rec.creation_date := l_con_rec.last_update_date;
940 l_con_rec.created_by := l_con_rec.last_updated_by;
941 l_con_rec.disable_date := p_dt_expiration;
942 l_con_rec.unit_of_measure :=
943 check_size(p_uom, 1, 25, 'Unit of Measure');
944 l_con_rec.uom_code :=
945 check_size(p_uomcode, 1, 3, 'UOM Code');
946
947 /* UOM_CLASS lives in field segment 3 */
948 IF p_uomclass IS NULL THEN
949 /* MUST set l_con_rec.unit_of_measure first! */
950 OPEN l_def_cls_csr;
951 FETCH l_def_cls_csr INTO l_con_rec.uom_class;
952 CLOSE l_def_cls_csr;
953 ELSE
954 l_con_rec.uom_class := check_size(p_uomclass, 1, 10, 'UOM Class');
955 END IF;
956
957 valid_uom_class(l_con_rec.uom_class, l_con_rec.creation_date);
958
959 /* Validate uom with class and code, get the base flag in the process */
960 l_base_flag := cross_validate(
961 l_con_rec.unit_of_measure,
962 l_con_rec.uom_code,
963 l_con_rec.uom_class);
964
965 /* Validate the conversion factor
966 * (needs the uom, code, class and l_base_flag)
967 */
968 l_con_rec.conversion_rate := valid_number(p_conv_rate, 'CONVRATE');
969 IF NVL(l_con_rec.conversion_rate, 0) <= 0 THEN
970 ITG_MSG.neg_conv;
971 RAISE FND_API.G_EXC_ERROR;
972 ELSIF p_itemid IS NULL THEN
973 l_con_rec.inventory_item_id := 0;
974 IF l_base_flag = 'Y' THEN
975 IF NVL(l_con_rec.conversion_rate, 0) <> 1 THEN
976 ITG_MSG.missing_element_value('CONVRATE', p_conv_rate);
977 RAISE FND_API.G_EXC_ERROR;
978 END IF;
979 END IF;
980 ELSE
981 --TODO!. Need to enhance the XGM to allow item_seg + org
982 l_con_rec.inventory_item_id := p_itemid;
983 /* l_con_rec.inventory_item_id :=
984 po_ip_oagxml_pkg.getItemId(p_itemid, NULL); */
985 NULL;
986 END IF;
987
988 IF p_syncind = 'D' then
989 l_con_rec.disable_date := sysdate;
990 END IF;
991 g_action := 'UOM conversion sync';
992 /* set the value for default converstion flag */
993 l_con_rec.default_conversion_flag := p_def_conv;
994 /* What are we doing? */
995 IF p_syncind = 'A' THEN insert_uom_conv(l_con_rec);
996 ELSIF p_syncind = 'C' THEN update_uom_conv(l_con_rec);
997 ELSIF p_syncind = 'D' THEN delete_uom_conv(l_con_rec);
998 END IF;
999
1000 END process_uom_intra;
1001
1002 /* Handle a task=UOMCONV request by differentiating the type of conversion,
1003 * either a standard, interclass or intraclass, and filling a field nested
1004 * table object accordingly, then pass that object to the private procedure
1005 * that processes that conversion
1006 */
1007 PROCEDURE process_uom_conv (
1008 p_syncind IN VARCHAR2 ,
1009 p_fromcode IN VARCHAR2,
1010 p_touomcode IN VARCHAR2,
1011 p_itemid IN NUMBER,
1012 p_fromfactor IN VARCHAR2,
1013 p_tofactor IN VARCHAR2,
1014 p_dt_creation IN DATE,
1015 p_dt_expiration IN DATE
1016 ) IS
1017 l_fr_base_uom mtl_units_of_measure.uom_code%TYPE;
1018 l_to_base_uom mtl_units_of_measure.uom_code%TYPE;
1019
1020 /* Default the value of uom_class */
1021 CURSOR l_uom_csr(
1022 p_uom_code VARCHAR2
1023 ) IS
1024 SELECT uom_class, unit_of_measure, uom_code, base_uom_flag
1025 FROM mtl_units_of_measure
1026 WHERE uom_code = p_uom_code
1027 AND base_uom_flag LIKE '%';
1028
1029 l_fr_uom_csr_rec l_uom_csr%ROWTYPE;
1030 l_to_uom_csr_rec l_uom_csr%ROWTYPE;
1031 l_conv_rate VARCHAR2(40);
1032 l_fr_conv NUMBER;
1033 l_to_conv NUMBER;
1034 l_def_conv_flag VARCHAR2(4);
1035 l_tmp NUMBER;
1036 l_param VARCHAR2(20);
1037 l_value VARCHAR2(20);
1038 BEGIN
1039
1040 /* Look up/massage the necessary UOM data...
1041 * The touomcode ALWAYS gets passed in
1042 */
1043 OPEN l_uom_csr(p_touomcode);
1044 FETCH l_uom_csr INTO l_to_uom_csr_rec;
1045 CLOSE l_uom_csr;
1046
1047 IF l_to_uom_csr_rec.uom_code IS NULL THEN
1048 ITG_MSG.missing_element_value('UOMGRPDTL/UOM', p_touomcode);
1049 RAISE FND_API.G_EXC_ERROR;
1050 END IF;
1051
1052 /* Build an outField using the "in" field (the field parameter coming in,
1053 * that is) which we will pass along to the appropriate procedure,
1054 * processIntraConv or processInterConv.
1055 * item id is null, it is a standered conversion
1056 *
1057 * Reduce the conversion rates to a decimal value
1058 * this value is needed for all 3 types of conversions
1059 * There is no conversion factors when the units are same
1060 * for different UOM another call to this procede will be made.
1061 */
1062 BEGIN
1063 l_param := 'UOMGRPHDR/QUANTITY';
1064 l_value := p_fromfactor;
1065 l_tmp := to_number(p_fromfactor);
1066
1067 l_param := 'UOMGRPDTL/QUANTITY';
1068 l_value := p_tofactor;
1069 l_tmp := to_number(p_tofactor);
1070
1071 IF l_tmp = 0 THEN
1072 RAISE FND_API.G_EXC_ERROR;
1073 END IF;
1074
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 ITG_MSG.missing_element_value(l_param,l_value);
1078 RAISE FND_API.G_EXC_ERROR;
1079 END;
1080 l_conv_rate := reduce_conv_rate(p_fromfactor, p_tofactor);
1081
1082
1083 IF p_itemid IS NULL THEN
1084 /* Case 1 standard */
1085 /* No ITEM_ID passed to us - Build a Standard conversion field table */
1086 process_uom_intra(
1087 p_syncind => p_syncind,
1088 p_uom => l_to_uom_csr_rec.unit_of_measure,
1089 p_uomcode => l_to_uom_csr_rec.uom_code,
1090 p_uomclass => l_to_uom_csr_rec.uom_class,
1091 p_itemid => p_itemid,
1092 p_conv_rate => l_conv_rate,
1093 p_def_conv => 'N',
1094 p_dt_creation => p_dt_creation,
1095 p_dt_expiration => p_dt_expiration
1096 );
1097
1098 ELSE
1099 /* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1100 * For Intra conversion, when fruomcode is null
1101 * +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1102 */
1103
1104 IF p_fromcode IS NULL THEN
1105 /* case 2 Intra-class
1106 * need to make sure the item's uom class is the same as
1107 * what we are
1108 * Build an Intra-Class conversion field table
1109 */
1110 l_def_conv_flag := get_default_conv_flag(
1111 l_to_uom_csr_rec.uom_code, l_to_uom_csr_rec.uom_class, l_conv_rate);
1112
1113 /* Call the intra conversion procedure. */
1114 process_uom_intra(
1115 p_syncind => p_syncind,
1116 p_uom => l_to_uom_csr_rec.unit_of_measure,
1117 p_uomcode => l_to_uom_csr_rec.uom_code,
1118 p_uomclass => l_to_uom_csr_rec.uom_class,
1119 p_itemid => p_itemid,
1120 p_conv_rate => l_conv_rate,
1121 p_def_conv => l_def_conv_flag,
1122 p_dt_creation => p_dt_creation,
1123 p_dt_expiration => p_dt_expiration
1124 );
1125
1126 ELSE
1127
1128 OPEN l_uom_csr(p_fromcode);
1129 FETCH l_uom_csr INTO l_fr_uom_csr_rec;
1130 CLOSE l_uom_csr;
1131
1132 IF l_fr_uom_csr_rec.uom_code IS NULL THEN
1133 ITG_MSG.missing_element_value('UOMGRPHDR/UOM', p_fromcode);
1134 RAISE FND_API.G_EXC_ERROR;
1135 END IF;
1136
1137 /* Build an Inter-Class conversion field table
1138 * need to find the conversion rate based on the base UOM's.
1139 * Look up the fruomcode data...
1140 * convert to base uom
1141 */
1142 l_fr_conv := get_in_base(l_fr_uom_csr_rec.uom_code,nvl(p_itemid,0)) ;
1143 l_to_conv := get_in_base(l_to_uom_csr_rec.uom_code,nvl(p_itemid,0)) ;
1144
1145 /* get the base UOM for these codes */
1146 l_fr_base_uom := get_base_uom(l_fr_uom_csr_rec.uom_code);
1147 l_to_base_uom := get_base_uom(l_to_uom_csr_rec.uom_code);
1148
1149 l_conv_rate := reduce_conv_rate(
1150 p_fromfactor, p_tofactor, l_fr_conv, l_to_conv);
1151
1152 process_uom_inter(
1153 p_syncind => p_syncind,
1154 p_fruomcode => l_fr_base_uom,
1155 p_touomcode => l_to_base_uom,
1156 p_itemid => p_itemid,
1157 p_conv_rate => l_conv_rate,
1158 p_dt_creation => p_dt_creation,
1159 p_dt_expiration => p_dt_expiration
1160 );
1161
1162 END IF;
1163 END IF;
1164 END process_uom_conv;
1165
1166 /* Public functions */
1167
1168 /* Only one public function, depend in the value of the p_task, will call
1169 different APIs */
1170 PROCEDURE Sync_UOM_All(
1171 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
1172 x_msg_count OUT NOCOPY NUMBER,
1173 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
1174
1175 p_task IN VARCHAR2,
1176 p_syncind IN VARCHAR2,
1177 p_uom IN VARCHAR2 := NULL,
1178 p_uomcode IN VARCHAR2 := NULL,
1179 p_uomclass IN VARCHAR2 := NULL,
1180 p_buomflag IN VARCHAR2 := NULL,
1181 p_description IN VARCHAR2 := NULL,
1182 p_defconflg IN VARCHAR2 := NULL,
1183 p_fromcode IN VARCHAR2 := NULL,
1184 p_touomcode IN VARCHAR2 := NULL,
1185 p_itemid IN NUMBER := NULL,
1186 p_fromfactor IN VARCHAR2 := NULL,
1187 p_tofactor IN VARCHAR2 := NULL,
1188 p_dt_creation IN DATE := NULL,
1189 p_dt_expiration IN DATE := NULL
1190 ) IS
1191 l_api_name CONSTANT VARCHAR2(30) := 'Sync_UOM_ALL';
1192 l_api_version CONSTANT NUMBER := 1.0;
1193
1194 l_dt_creation DATE;
1195 l_dt_expiration DATE;
1196 BEGIN
1197 x_return_status := FND_API.G_RET_STS_SUCCESS;
1198
1199 g_action := 'UOM sync';
1200
1201 SAVEPOINT Sync_UOM_ALL;
1202 ITG_Debug.setup(
1203 p_reset => TRUE,
1204 p_pkg_name => G_PKG_NAME,
1205 p_proc_name => l_api_name);
1206
1207
1208 -- Now in wrapper FND_MSG_PUB.Initialize;
1209
1210 /* NOTE: Add more tracing, esp. called procedures */
1211 ITG_Debug.msg('SUA', 'Top of procedure.');
1212 ITG_Debug.msg('SUA', 'p_task', p_task);
1213 ITG_Debug.msg('SUA', 'p_syncind', p_syncind);
1214 ITG_Debug.msg('SUA', 'p_uom', p_uom);
1215 ITG_Debug.msg('SUA', 'p_uomcode', p_uomcode);
1216 ITG_Debug.msg('SUA', 'p_uomclass', p_uomclass);
1217 ITG_Debug.msg('SUA', 'p_buomflag', p_buomflag);
1218 ITG_Debug.msg('SUA', 'p_description', p_description);
1219 ITG_Debug.msg('SUA', 'p_defconflg', p_defconflg);
1220 ITG_Debug.msg('SUA', 'p_fromcode', p_fromcode, TRUE);
1221 ITG_Debug.msg('SUA', 'p_touomcode', p_touomcode, TRUE);
1222 ITG_Debug.msg('SUA', 'p_itemid', p_itemid);
1223 ITG_Debug.msg('SUA', 'p_fromfactor', p_fromfactor, TRUE);
1224 ITG_Debug.msg('SUA', 'p_tofactor', p_tofactor, TRUE);
1225 ITG_Debug.msg('SUA', 'p_dt_creation', p_dt_creation);
1226 ITG_Debug.msg('SUA', 'p_dt_expiration', p_dt_expiration);
1227
1228 l_dt_creation := NVL(p_dt_creation, SYSDATE);
1229 l_dt_expiration := NVL(p_dt_expiration,l_dt_creation+3650);
1230
1231 /* Here goes the switch */
1232 IF upper(p_task) = 'UOM' THEN
1233 g_action := 'synchronzing UOM';
1234 process_uom(
1235 p_syncind => upper(p_syncind),
1236 p_uom => p_uom,
1237 p_uomcode => p_uomcode,
1238 p_uomclass => p_uomclass,
1239 p_buomflag => p_buomflag,
1240 p_description => p_description,
1241 p_dt_creation => l_dt_creation,
1242 p_dt_expiration => l_dt_expiration
1243 );
1244
1245 ELSIF upper(p_task) = 'UOMCLASS' THEN
1246 g_action := 'UOMCLASS sync';
1247 process_uom_class(
1248 p_syncind => upper(p_syncind),
1249 p_uom => p_uom,
1250 p_uomcode => p_uomcode,
1251 p_uomclass => p_uomclass,
1252 p_defconflg => p_defconflg,
1253 p_description => p_description,
1254 p_dt_creation => l_dt_creation,
1255 p_dt_expiration => l_dt_expiration
1256 );
1257 ELSIF upper(p_task) = 'UOMCONV' THEN
1258 g_action := 'UOM conversion sync';
1259 process_uom_conv(
1260 p_syncind => upper(p_syncind),
1261 p_fromcode => p_fromcode,
1262 p_touomcode => p_touomcode,
1263 p_itemid => p_itemid,
1264 p_fromfactor => p_fromfactor,
1265 p_tofactor => p_tofactor,
1266 p_dt_creation => l_dt_creation,
1267 p_dt_expiration => l_dt_expiration
1268 );
1269 ELSE
1270 ITG_MSG.missing_element_value('P_TASK', p_task);
1271 END IF;
1272
1273 COMMIT WORK;
1274
1275 ITG_Debug.msg('SUA', 'Done.');
1276
1277 EXCEPTION
1278 WHEN FND_API.G_EXC_ERROR THEN
1279 ROLLBACK TO Sync_UOM_ALL;
1280 x_return_status := FND_API.G_RET_STS_ERROR;
1281 ITG_msg.checked_error(g_action);
1282
1283 WHEN OTHERS THEN
1284 ROLLBACK TO Sync_UOM_ALL;
1285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1286 ITG_msg.unexpected_error(g_action);
1287 itg_debug.msg('Unexpected error (UOMSync) - ' || substr(SQLERRM,1,255),true);
1288
1289 -- Removed FND_MSG_PUB.Count_And_Get
1290
1291 END Sync_UOM_ALL;
1292
1293 END ITG_SyncUOMInbound_PVT;