[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_UTILS_PKG
Source
1 PACKAGE BODY INV_MGD_MVT_UTILS_PKG AS
2 /* $Header: INVUINTB.pls 120.16 2011/08/10 12:48:13 abhissri ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVUINTB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| |
14 --| PROCEDURE LIST |
15 --| Mvt_Stats_Util_Info |
16 --| Calc_Unit_Weight |
17 --| Calc_Total_Weight |
18 --| Convert_Territory_Code |
19 --| Get_Commodity_Info |
20 --| Get_Category_Id |
21 --| Get_Site_Location |
22 --| Get_Org_Location |
23 --| Get_Vendor_Location |
24 --| Get_Zone_Code |
25 --| Get_Subinv_Location |
26 --| Get_SO_Legal_Entity |
27 --| Get_Vendor_Info |
28 --| Get_Cust_VAT_Number |
29 --| Get_Org_VAT_Number |
30 --| Get_Shipping_Legal_Entity |
31 --| Get_LE_Currency |
32 --| Get_LE_Location |
33 --| Get_Weight_Precision |
34 --| Round_Number |
35 --| Get_Org_From_Le |
36 --| |
37 --| HISTORY |
38 --| 11/17/98 pseshadr Created |
39 --| 10/29/99 pjuvara, ssui revised Update_Mtl_Movement_Statistics |
40 --| to correct row who columns |
41 --| 11/26/02 yawang add function get_subinv_location |
42 --| 12/16/02 yawang add function get_so_legal_entity and |
43 --| get_shipping_legal_entity |
44 --| 12/02/04 vma Fix bug 3869825 |
45 --| 24/04/07 mkarra Bug 5984760 Modified Calc_Unit_Weight
46 --| function to call INV UOM CONVERSION APIs |
47 --+========================================================================
48
49 --===================
50 -- GLOBALS
51 --===================
52
53 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_UTILS_PKG.';
54 g_too_many_transactions_exc EXCEPTION;
55 g_no_data_transaction_exc EXCEPTION;
56 g_period_name_not_found_exc EXCEPTION;
57 g_log_level NUMBER;
58 g_log_mode VARCHAR2(3); -- possible values: OFF, SQL, SRS
59
60 --========================================================================
61 -- PROCEDURE : Mvt_Stats_Util_Info PUBLIC
62 -- PARAMETERS: p_api_version_number known api version
63 -- p_init_msg_list FND_API.G_TRUE to reset list
64 -- x_return_status return status
65 -- x_msg_count number of messages in the list
66 -- x_msg_data message text
67 -- p_stat_typ_transaction IN Stat type Usages record
68 -- x_movement_transaction IN OUT Movement Statistics Record
69 -- VERSION : current version 1.0
70 -- initial version 1.0
71 -- COMMENT : Utility procedure that calculates invoice info,
72 -- weight info, this procedure inturns calls the
73 -- functions and procedures described above.
74 --=========================================================================
75
76 PROCEDURE Mvt_Stats_Util_Info
77 ( p_stat_typ_transaction IN
78 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
79 , x_movement_transaction IN OUT NOCOPY
80 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
81 , x_return_status OUT NOCOPY VARCHAR2
82 )
83 IS
84 l_api_version_number CONSTANT NUMBER := 1.0;
85 l_api_name CONSTANT VARCHAR2(30) := 'Mvt_Stats_Util_Info';
86 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
87 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
88 l_uom_code VARCHAR2(15);
89 l_procedure_name CONSTANT VARCHAR2(30) := 'Mvt_Stats_Util_Info';
90 l_weight_precision NUMBER;
91 l_rounding_method VARCHAR2(30);
92 l_total_weight NUMBER;
93
94 BEGIN
95 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
96 THEN
97 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
98 , G_MODULE_NAME || l_procedure_name || '.begin'
99 ,'enter procedure'
100 );
101 END IF;
102
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104
105 l_movement_transaction := x_movement_transaction;
106 l_stat_typ_transaction := p_stat_typ_transaction;
107
108 IF x_movement_transaction.primary_quantity IS NULL
109 THEN
110 x_movement_transaction.primary_quantity := INV_CONVERT.INV_UM_CONVERT
111 ( x_movement_transaction.inventory_item_id
112 , 5
113 , x_movement_transaction.transaction_quantity
114 , x_movement_transaction.transaction_uom_code
115 , x_movement_transaction.primary_uom_code
116 , null
117 , null
118 );
119 END IF;
120
121 x_movement_transaction.category_id := Get_Category_Id
122 ( p_movement_transaction => x_movement_transaction
123 , p_stat_typ_transaction => l_stat_typ_transaction
124 );
125
126 IF (x_movement_transaction.category_id IS NOT NULL)
127 THEN
128 Get_Commodity_Info(x_movement_transaction => x_movement_transaction);
129 END IF;
130
131 -- If there is an invoice then get all the info from the invoice
132 x_movement_transaction.period_name :=
133 INV_MGD_MVT_FIN_MDTR.Get_Period_Name
134 ( p_movement_transaction => x_movement_transaction
135 , p_stat_typ_transaction => l_stat_typ_transaction
136 );
137
138 -- Fix Bug 3869825: Movement Statistics Processor should fail
139 -- if the period is not defined in GL
140 IF (x_movement_transaction.period_name IS NULL)
141 THEN
142 RAISE g_period_name_not_found_exc;
143 END IF;
144
145 -- move this condition to INVFMDRB.pls so that all the places
146 --where call calc_invoice_info will also filter out following conditions
147 INV_MGD_MVT_FIN_MDTR.Calc_Invoice_Info
148 ( p_stat_typ_transaction => l_stat_typ_transaction
149 , x_movement_transaction => x_movement_transaction
150 );
151
152 x_movement_transaction.movement_amount :=
153 INV_MGD_MVT_FIN_MDTR.Calc_Movement_Amount
154 (p_movement_transaction => x_movement_transaction);
155
156 --Calculate freight charge and include in statistics value
157 x_movement_transaction.stat_ext_value :=
158 INV_MGD_MVT_FIN_MDTR.Calc_Statistics_Value
159 (p_movement_transaction => x_movement_transaction);
160
161 /* Bug: 5291257. Call to function INV_MGD_MVT_FIN_MDTR.Get_Set_Of_Books_Period
162 is modified becasue p_period_type is no more required. */
163 x_movement_transaction.set_of_books_period :=
164 INV_MGD_MVT_FIN_MDTR.Get_Set_Of_Books_Period
165 ( p_legal_entity_id => x_movement_transaction.entity_org_id
166 , p_period_date => NVL(x_movement_transaction.invoice_date_reference,
167 x_movement_transaction.transaction_date)
168 --, p_period_type => NVL(l_stat_typ_transaction.period_type,'Month')
169 );
170
171 IF (x_movement_transaction.transaction_quantity IS NOT NULL)
172 AND (x_movement_transaction.transaction_uom_code IS NOT NULL)
173 THEN
174 x_movement_transaction.unit_weight := Calc_Unit_Weight
175 ( p_inventory_item_id => x_movement_transaction.inventory_item_id
176 , p_organization_id => x_movement_transaction.organization_id
177 , p_stat_typ_uom_code => l_stat_typ_transaction.weight_uom_code
178 , p_tranx_uom_code => x_movement_transaction.transaction_uom_code
179 );
180
181 --Fix bug 4866967 and 5203245 get weight precision and rounding method
182 Get_Weight_Precision
183 (p_legal_entity_id => x_movement_transaction.entity_org_id
184 , p_zone_code => x_movement_transaction.zone_code
185 , p_usage_type => x_movement_transaction.usage_type
186 , p_stat_type => x_movement_transaction.stat_type
187 , x_weight_precision => l_weight_precision
188 , x_rep_rounding => l_rounding_method);
189
190 IF x_movement_transaction.unit_weight IS NOT NULL
191 THEN
192 l_total_weight := x_movement_transaction.unit_weight *
193 x_movement_transaction.transaction_quantity;
194
195 x_movement_transaction.total_weight := Round_Number
196 ( p_number => l_total_weight
197 , p_precision => l_weight_precision
198 , p_rounding_method => l_rounding_method
199 );
200 ELSE
201 x_movement_transaction.total_weight := NULL;
202 END IF;
203
204 -- If there is an alternate uom we need to convert quantity to this
205 -- alternate uom
206 IF (l_stat_typ_transaction.alt_uom_rule_set_code IS NOT NULL)
207 THEN
208 x_movement_transaction.alternate_uom_code := Get_Alternate_UOM
209 ( p_category_set_id => l_stat_typ_transaction.category_set_id
210 , p_alt_uom_rule_set_code => l_stat_typ_transaction.alt_uom_rule_set_code
211 , p_commodity_code => x_movement_transaction.commodity_code
212 );
213
214 IF (x_movement_transaction.alternate_uom_code IS NOT NULL)
215 THEN
216 x_movement_transaction.alternate_quantity := Convert_alternate_Quantity
217 ( p_transaction_quantity => x_movement_transaction.transaction_quantity
218 , p_alternate_uom_code => x_movement_transaction.alternate_uom_code
219 , p_transaction_uom_code => x_movement_transaction.transaction_uom_code
220 , p_inventory_item_id => x_movement_transaction.inventory_item_id
221 );
222 ELSE
223 x_movement_transaction.alternate_quantity := NULL;
224 END IF;
225 ELSE
226 x_movement_transaction.alternate_quantity := NULL;
227 x_movement_transaction.alternate_uom_code := NULL;
228 END IF;
229 ELSE
230 x_movement_transaction.total_weight := null;
231 x_movement_transaction.unit_weight := null;
232 END IF;
233
234 IF (x_movement_transaction.origin_territory_code IS NOT NULL)
235 THEN
236 x_movement_transaction.origin_territory_eu_code :=
237 Convert_Territory_Code (x_movement_transaction.origin_territory_code);
238 END IF;
239
240 IF (x_movement_transaction.dispatch_territory_code IS NOT NULL)
241 THEN
242 x_movement_transaction.dispatch_territory_eu_code :=
243 Convert_Territory_Code (x_movement_transaction.dispatch_territory_code);
244 END IF;
245
246 IF (x_movement_transaction.destination_territory_code IS NOT NULL)
247 THEN
248 x_movement_transaction.destination_territory_eu_code :=
249 Convert_Territory_Code (x_movement_transaction.destination_territory_code);
250 END IF;
251
252 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
253 THEN
254 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
255 , G_MODULE_NAME || l_procedure_name || '.end'
256 ,'exit procedure'
257 );
258 END IF;
259 EXCEPTION
260 WHEN g_period_name_not_found_exc THEN
261
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263
264 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
265 THEN
266 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
267 , G_MODULE_NAME || l_procedure_name
268 ,'Mvt_Stats_Util_Info: GL Period is not defined. '
269 || 'Please define the GL Period for the transaction date '
270 || x_movement_transaction.transaction_date
271 || ' in the Period Set '
272 || l_stat_typ_transaction.period_set_name
273 || ' and the Period Type '
274 || l_stat_typ_transaction.period_type
275 || '.'
276 );
277 END IF;
278
279 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
280 THEN
281 FND_MSG_PUB.Add_Exc_Msg
282 ('INV_MGD_MVT_UTILS_PKG'
283 , 'Mvt_Stats_Util_Info: GL Period is not defined. '
284 || 'Please define the GL Period for the transaction date '
285 || x_movement_transaction.transaction_date
286 || ' in the Period Set '
287 || l_stat_typ_transaction.period_set_name
288 || ' and the Period Type '
289 || l_stat_typ_transaction.period_type
290 || '.'
291 );
292 END IF;
293
294 x_movement_transaction := l_movement_transaction;
295
296 WHEN NO_DATA_FOUND THEN
297 x_movement_transaction := l_movement_transaction;
298 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
299 THEN
300 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
301 , G_MODULE_NAME || l_procedure_name||'.No data found exception'
302 , 'Exception'
303 );
304 END IF;
305
306 WHEN TOO_MANY_ROWS THEN
307 x_movement_transaction := l_movement_transaction;
308 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
309 THEN
310 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
311 , G_MODULE_NAME || l_procedure_name||'.too many rows exception'
312 , 'Exception'
313 );
314 END IF;
315
316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
318 x_movement_transaction := l_movement_transaction;
319 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
320 THEN
321 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
322 , G_MODULE_NAME || l_procedure_name||'.unexpected exception'
323 , 'Exception'
324 );
325 END IF;
326
327 WHEN OTHERS THEN
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
329 x_movement_transaction := l_movement_transaction;
330
331 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
332 THEN
333 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
334 , G_MODULE_NAME || l_procedure_name||'.Others exception'
335 ,'Exception'
336 );
337 END IF;
338
339 /*IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
340 THEN
341 FND_MSG_PUB.Add_Exc_Msg
342 ( G_PKG_NAME
343 , 'INV_MGD_MVT_UTILS_PKG'
344 );
345 END IF;*/
346
347 END Mvt_Stats_Util_Info;
348
349
350 --========================================================================
351 -- FUNCTION : Get_Conversion_Rate Private
352 -- PARAMETERS:
353 -- p_item_id Inventory Item
354 -- p_uom_code UOM code
355 -- COMMENT : Returns the conversion rate between the passing in UOM and
356 -- the base UOM of the same class
357 --=======================================================================
358 FUNCTION Get_Conversion_Rate
359 ( p_item_id NUMBER
360 , p_uom_code VARCHAR2
361 )
362 RETURN NUMBER
363 IS
364 l_rate NUMBER;
365 BEGIN
366 --Get rate for this specific item if there is defined
367 BEGIN
368 SELECT
369 conversion_rate
370 INTO
371 l_rate
372 FROM
373 mtl_uom_conversions
374 WHERE uom_code = p_uom_code
375 AND inventory_item_id = p_item_id;
376
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379 l_rate := NULL;
380 END;
381
382 --If there is no special conversion for this item, get standard rate
383 IF l_rate IS NULL
384 THEN
385 SELECT
386 conversion_rate
387 INTO
388 l_rate
389 FROM
390 mtl_uom_conversions
391 WHERE uom_code = p_uom_code
392 AND inventory_item_id = 0;
393 END IF;
394
395 RETURN (l_rate);
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
400 THEN
401 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
402 , G_MODULE_NAME || 'Get_Conversion_Rate'||'.Others exception'
403 , 'Exception'
404 );
405 END IF;
406 l_rate := NULL;
407 RETURN (l_rate);
408 END Get_Conversion_Rate;
409
410
411 --========================================================================
412 -- FUNCTION : Get_Rate_Two_Uom Private
413 -- PARAMETERS:
414 -- p_item_id Inventory Item
415 -- p_uom1 UOM1
416 -- p_uom2 UOM2
417 -- COMMENT : Returns the conversion rate between the two passing in uoms
418 --=======================================================================
419
420 /* Bug 5984760 - This function will no longer be used and will be obsoleted*/
421 FUNCTION Get_Rate_Two_Uom
422 ( p_item_id NUMBER
423 , p_uom1 VARCHAR2
424 , p_uom2 VARCHAR2
425 )
426 RETURN NUMBER
427 IS
428 l_rate1 NUMBER;
429 l_rate2 NUMBER;
430 l_conversion_rate NUMBER;
431 BEGIN
432 --Get conversion rate between uom1 and it's base uom
433 l_rate1 := Get_Conversion_Rate
434 ( p_item_id => p_item_id
435 , p_uom_code => p_uom1
436 );
437 --Get conversion rate between uom2 and it's base uom
438 l_rate2 := Get_Conversion_Rate
439 ( p_item_id => p_item_id
440 , p_uom_code => p_uom2
441 );
442
443 --Calculate 1 of uom1 equals to how much of uom2
444 IF (l_rate1 IS NOT NULL
445 AND l_rate2 IS NOT NULL
446 AND l_rate2 <> 0)
447 THEN
448 l_conversion_rate := l_rate1/l_rate2;
449 ELSE
450 l_conversion_rate := null;
451 END IF;
452
453 RETURN (l_conversion_rate);
454
455 EXCEPTION
456 WHEN OTHERS THEN
457 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
458 THEN
459 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
460 , G_MODULE_NAME || 'Get_Rate_Two_Uom'||'.Others exception'
461 , 'Exception'
462 );
463 END IF;
464 l_conversion_rate := NULL;
465 RETURN (l_conversion_rate);
466 END Get_Rate_Two_Uom;
467
468 --========================================================================
469 -- FUNCTION : Calc_Unit_Weight PUBLIC
470 -- PARAMETERS:
471 -- p_inventory_item_id Inventory Item
472 -- p_organization_id Organization_id
473 -- p_stat_typ_uom_code UOM defined by stat_type_usages
474 -- p_tranx_uom_code Transaction UOM
475 -- COMMENT : Returns the unit weight of an item
476 --=======================================================================
477 FUNCTION Calc_Unit_Weight
478 ( p_inventory_item_id NUMBER
479 , p_organization_id NUMBER
480 , p_stat_typ_uom_code VARCHAR2
481 , p_tranx_uom_code VARCHAR2
482 )
483 RETURN NUMBER
484 IS
485 l_unit_weight NUMBER;
486 l_item_unit_weight NUMBER;
487 l_weight_uom_code VARCHAR2(3);
488 l_primary_uom_code VARCHAR2(3);
489 l_conversion_rate NUMBER;
490 l_uom_class VARCHAR2(10);
491
492 l_tranx_uom_class VARCHAR2(10);
493 l_stat_typ_uom_class VARCHAR2(10);
494 l_rate1 NUMBER;
495 l_rate2 NUMBER;
496 l_procedure_name CONSTANT VARCHAR2(30) := 'Calc_Unit_Weight';
497 BEGIN
498 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
499 THEN
500 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
501 , G_MODULE_NAME || l_procedure_name || '.begin'
502 ,'enter procedure'
503 );
504 END IF;
505
506 IF (p_inventory_item_id IS NOT NULL
507 AND p_tranx_uom_code IS NOT NULL)
508 THEN
509 --yawang fix bug 2326128, re-design the way we calculate the unit weight
510 --Find the class of transaction uom
511 SELECT uom_class
512 INTO l_tranx_uom_class
513 FROM mtl_units_of_measure_vl
514 WHERE uom_code = p_tranx_uom_code;
515
516 --Find the class of movement uom defined on movement statistics parameter form
517 SELECT uom_class
518 INTO l_stat_typ_uom_class
519 FROM mtl_units_of_measure_vl
520 WHERE uom_code = p_stat_typ_uom_code;
521
522 --Scenario 1, the transaction uom is of weight class
523 --we will do intra-class conversion if the transaction uom is different
524 --from movement weight uom
525 IF l_tranx_uom_class = l_stat_typ_uom_class
526 THEN
527 IF p_tranx_uom_code = p_stat_typ_uom_code
528 THEN
529 l_unit_weight := 1;
530 ELSE
531
532 /* Bug 5984760 - Start */
533 /* INV_CONVERT standard conversion APIs are called to get the conversion
534 rates. Get_Rate_Two_UOM will no longer be uses */
535
536
537 --Get conversion rate between transaction uom and movement uom
538 /*l_conversion_rate := Get_Rate_Two_Uom
539 ( p_item_id => p_inventory_item_id
540 , p_uom1 => p_tranx_uom_code
541 , p_uom2 => p_stat_typ_uom_code
542 ); */
543
544 INV_CONVERT.inv_um_conversion(
545 from_unit => p_tranx_uom_code
546 , to_unit => p_stat_typ_uom_code
547 , item_id => p_inventory_item_id
548 , uom_rate => l_conversion_rate);
549
550
551 --Calculate unit weight for 1 of transaction uom
552 --Unit weight should not be static as defined on the item master
553 --It should be calculated against transaction uom
554 --ex: unit weight for each TON or for each KG or for each Lbs
555 l_unit_weight := 1 * l_conversion_rate;
556 END IF;
557 ELSE
558 --Scenario 2, the transaction uom is not of weight class
559 -- retrieve item unit weight from master item
560 SELECT
561 unit_weight
562 , weight_uom_code
563 , primary_uom_code
564 INTO
565 l_item_unit_weight
566 , l_weight_uom_code
567 , l_primary_uom_code
568 FROM
569 MTL_SYSTEM_ITEMS muc
570 WHERE muc.inventory_item_id = p_inventory_item_id
571 AND muc.organization_id = p_organization_id;
572
573 IF (l_primary_uom_code IS NOT NULL
574 AND l_weight_uom_code IS NOT NULL)
575 THEN
576 --Get conversion rate between transaction uom and primary uom
577 /* l_rate1 := Get_Rate_Two_Uom
578 ( p_item_id => p_inventory_item_id
579 , p_uom1 => p_tranx_uom_code
580 , p_uom2 => l_primary_uom_code
581 ); */
582
583 INV_CONVERT.inv_um_conversion(
584 from_unit => p_tranx_uom_code
585 , to_unit => l_primary_uom_code
586 , item_id => p_inventory_item_id
587 , uom_rate => l_rate1);
588
589
590 --Get conversion rate between item master unit weight uom
591 --and movement uom
592 /* l_rate2 := Get_Rate_Two_Uom
593 ( p_item_id => p_inventory_item_id
594 , p_uom1 => l_weight_uom_code
595 , p_uom2 => p_stat_typ_uom_code
596 ); */
597
598 INV_CONVERT.inv_um_conversion(
599 from_unit => l_weight_uom_code
600 , to_unit => p_stat_typ_uom_code
601 , item_id => p_inventory_item_id
602 , uom_rate => l_rate2);
603
604 /* Bug 5984760 - End */
605
606
607 --Calculate unit weight for 1 of transaction uom
608 --Unit weight should not be static as defined on the item master
609 --It should be calculated against transaction uom
610 --ex: unit weight for each Dozen or for each Box
611 l_unit_weight := l_item_unit_weight * l_rate1 * l_rate2;
612 END IF;
613 END IF;
614 ELSE
615 l_unit_weight := null;
616 END IF;
617
618 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
619 THEN
620 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
621 , G_MODULE_NAME || l_procedure_name || '.end'
622 ,'exit procedure'
623 );
624 END IF;
625
626 RETURN round(l_unit_weight,10); -- Fix bug 4197941
627
628 EXCEPTION
629 WHEN OTHERS THEN
630 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
631 THEN
632 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
633 , G_MODULE_NAME || l_procedure_name||'.Others exception'
634 , 'Exception'
635 );
636 END IF;
637
638 l_unit_weight := null;
639 RETURN (l_unit_weight);
640
641 END Calc_Unit_Weight;
642
643 /*
644 --========================================================================
645 -- FUNCTION : Calc_Total_Weight PUBLIC
646 -- PARAMETERS:
647 -- p_inventory_item_id Inventory Item
648 -- p_organization_id Organization_id
649 -- p_weight_uom_code UOM
650 -- p_weight_precision rounding decimal digits
651 -- p_transaction_quantity Quantity
652 -- p_transaction_uom_code Transaction UOM
653 -- COMMENT : Total weight in the UOM that is defined in
654 -- the set-up form in mtl stat type usages.
655 -- The weight is defined in the UOm that is defined
656 -- by the authorities for reporting.
657 --=======================================================================
658
659 FUNCTION Calc_Total_Weight
660 ( p_inventory_item_id NUMBER
661 , p_organization_id NUMBER
662 , p_weight_uom_code VARCHAR2
663 , p_weight_precision NUMBER
664 , p_transaction_quantity NUMBER
665 , p_transaction_uom_code VARCHAR2
666 , p_unit_weight NUMBER
667 )
668 RETURN NUMBER
669 IS
670
671 l_conversion_rate NUMBER;
672 l_weight NUMBER;
673 l_unit_weight NUMBER;
674
675 BEGIN
676
677 l_unit_weight := p_unit_weight;
678
679 IF l_unit_weight IS NOT NULL AND
680 p_transaction_quantity IS NOT NULL
681 THEN
682
683 l_weight := l_unit_weight * p_transaction_quantity;
684 l_weight := round(l_weight,NVL(p_weight_precision,0)); --Fix bug 4866967
685 ELSE
686 l_weight := NULL;
687
688 END IF;
689
690 -- Weight rounded up for all the EEC countries
691 -- except Portugal who need 3 decimals
692 --if P_FORMAT_TYPE = 'PT'
693 -- then l_weight := round(l_weight,3);
694 --else l_weight := ceil(l_weight);
695 --END IF;
696
697
698 RETURN(l_weight);
699
700 EXCEPTION
701 WHEN OTHERS THEN
702 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
703 THEN
704 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
705 , G_MODULE_NAME ||'Calc_Total_Weight'||'.Others exception'
706 , 'Exception'
707 );
708 END IF;
709
710 l_conversion_rate := null;
711 l_weight := null;
712 RETURN l_weight;
713
714 END Calc_Total_Weight;
715 */
716
717 --========================================================================
718 -- FUNCTION : Convert_alternate_Quantity PUBLIC
719 -- PARAMETERS:
720 -- p_inventory_item_id Inventory Item
721 -- p_organization_id Organization_id
722 -- p_stat_typ_uom_code UOM defined by stat_type_usages
723 -- COMMENT : Returns the unit weight of an item
724 --=======================================================================
725 FUNCTION Convert_alternate_Quantity
726 ( p_transaction_quantity NUMBER
727 , p_alternate_uom_code VARCHAR2
728 , p_inventory_item_id NUMBER
729 , p_transaction_uom_code VARCHAR2
730 )
731 RETURN NUMBER
732 IS
733 l_conv_rate NUMBER;
734 l_alternate_quantity NUMBER;
735 l_procedure_name CONSTANT VARCHAR2(30) := 'Convert_alternate_Quantity';
736
737 BEGIN
738 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
739 THEN
740 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
741 , G_MODULE_NAME || l_procedure_name || '.begin'
742 ,'enter procedure'
743 );
744 END IF;
745
746 INV_CONVERT.inv_um_conversion(
747 from_unit => p_transaction_uom_code
748 , to_unit => p_alternate_uom_code
749 , item_id => p_inventory_item_id
750 , uom_rate => l_conv_rate);
751
752 -- Calculate alternate quantity
753 l_alternate_quantity :=
754 p_transaction_quantity * round(l_conv_rate,3);
755
756 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
757 THEN
758 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
759 , G_MODULE_NAME || l_procedure_name || '.end'
760 ,'exit procedure'
761 );
762 END IF;
763
764 RETURN l_alternate_quantity;
765
766 EXCEPTION
767 WHEN NO_DATA_FOUND THEN
768 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
769 THEN
770 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
771 , G_MODULE_NAME ||l_procedure_name||'.No data found exception'
772 , 'Exception'
773 );
774 END IF;
775 l_alternate_quantity := null;
776 RETURN (l_alternate_quantity);
777
778 WHEN OTHERS THEN
779 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
780 THEN
781 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
782 , G_MODULE_NAME ||l_procedure_name||'.Others exception'
783 , 'Exception'
784 );
785 END IF;
786
787 l_alternate_quantity := null;
788 RETURN (l_alternate_quantity);
789
790 END Convert_alternate_Quantity;
791
792 --========================================================================
793 -- FUNCTION : Get_Alternate_UOM PUBLIC
794 -- PARAMETERS:
795 -- category set_id Category set in stat type usages
796 -- alt_uom_rule_set_code alternate rule set code
797 -- commodity code Transaction commodity code
798 -- COMMENT : Returns the alternate UOM
799 --=======================================================================
800 FUNCTION Get_Alternate_UOM
801 ( p_category_set_id NUMBER
802 , p_alt_uom_rule_set_code VARCHAR2
803 , p_commodity_code VARCHAR2
804 )
805 RETURN VARCHAR2
806 IS
807 l_alt_uom_code VARCHAR2(50);
808 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Alternate_UOM';
809 BEGIN
810 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
811 THEN
812 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
813 , G_MODULE_NAME || l_procedure_name || '.begin'
814 ,'enter procedure'
815 );
816 END IF;
817
818 SELECT attribute_code INTO l_alt_uom_code
819 FROM MTL_MVT_STATS_RULES R
820 , MTL_MVT_STATS_RULE_SETS_B RS
821 WHERE R.rule_set_code = RS.rule_set_code
822 AND R.COMMODITY_CODE = p_commodity_code
823 AND RS.category_set_id = p_category_set_id
824 AND RS.rule_set_type = 'ALTERNATE_UOM'
825 AND R.rule_set_code = p_alt_uom_rule_set_code;
826
827 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
828 THEN
829 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
830 , G_MODULE_NAME || l_procedure_name || '.end'
831 ,'exit procedure'
832 );
833 END IF;
834
835 RETURN l_alt_uom_code;
836
837 EXCEPTION
838 WHEN NO_DATA_FOUND THEN
839 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
840 THEN
841 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
842 , G_MODULE_NAME ||l_procedure_name||'.No data found exception'
843 , 'Exception'
844 );
845 END IF;
846 l_alt_uom_code := null;
847 RETURN (l_alt_uom_code);
848
849 WHEN OTHERS THEN
850 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
851 THEN
852 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
853 , G_MODULE_NAME ||l_procedure_name||'.Others exception'
854 , 'Exception'
855 );
856 END IF;
857
858 l_alt_uom_code := null;
859 RETURN (l_alt_uom_code);
860
861 END Get_Alternate_UOM;
862
863
864 --========================================================================
865 -- FUNCTION : Convert_Territory_Code PUBLIC
866 -- PARAMETERS:
867 -- l_iso_code varchar2
868 -- COMMENT : Calculates and returns the ISO code given the territory code
869 --=======================================================================
870
871 FUNCTION Convert_Territory_Code (l_iso_code VARCHAR2)
872 RETURN VARCHAR2
873 IS
874 l_code VARCHAR2(3);
875
876 CURSOR l_eu IS
877 SELECT
878 fnd.eu_code
879 FROM
880 FND_TERRITORIES fnd
881 WHERE
882 territory_code = l_iso_code;
883
884 BEGIN
885
886 OPEN l_eu;
887 FETCH l_eu INTO
888 l_code;
889
890 IF l_eu%NOTFOUND THEN
891 CLOSE l_eu;
892 l_code := null;
893 RETURN(l_code);
894 END IF;
895
896 CLOSE l_eu;
897 RETURN(l_code);
898
899 EXCEPTION
900 WHEN NO_DATA_FOUND THEN
901 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
902 THEN
903 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
904 , G_MODULE_NAME ||'Convert_Territory_Code'||'.No data found exception'
905 , 'Exception'
906 );
907 END IF;
908 l_code := null;
909 RETURN(l_code);
910
911 WHEN OTHERS THEN
912 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
913 THEN
914 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
915 , G_MODULE_NAME ||'Convert_Territory_Code'||'.Others exception'
916 , 'Exception'
917 );
918 END IF;
919 l_code := null;
920 RETURN(l_code);
921
922 END Convert_Territory_Code;
923
924
925 --========================================================================
926 -- FUNCTION : Get_Category_Id PUBLIC
927 -- PARAMETERS: p_movement_transaction IN Movement Statistics Record
928 -- p_stat_typ_transaction IN Stat type Usages record
929 -- COMMENT : Function that returns the category id for an item
930 --=========================================================================
931
932 FUNCTION Get_Category_Id
933 ( p_movement_transaction IN
934 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
935 , p_stat_typ_transaction IN
936 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
937 )
938 RETURN NUMBER
939 IS
940 l_category_id NUMBER;
941 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Category_Id';
942
943 CURSOR c_ccode
944 IS
945 SELECT
946 sic.category_id
947 FROM
948 MTL_SYSTEM_ITEMS si
949 , MTL_ITEM_CATEGORIES sic
950 WHERE si.inventory_item_id = sic.inventory_item_id
951 AND si.organization_id = sic.organization_id
952 AND si.inventory_item_id = p_movement_Transaction.inventory_item_id
953 AND si.organization_id = p_movement_Transaction.organization_id
954 AND sic.category_set_id = p_stat_typ_transaction.category_set_id;
955
956 BEGIN
957 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
958 THEN
959 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
960 , G_MODULE_NAME || l_procedure_name || '.begin'
961 ,'enter procedure'
962 );
963 END IF;
964
965 OPEN c_ccode;
966 FETCH c_ccode
967 INTO
968 l_category_id;
969
970 IF c_ccode%NOTFOUND THEN
971 CLOSE c_ccode;
972 RAISE NO_DATA_FOUND;
973 END IF;
974
975 CLOSE c_ccode;
976
977 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
978 THEN
979 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
980 , G_MODULE_NAME || l_procedure_name || '.end'
981 ,'exit procedure'
982 );
983 END IF;
984
985 RETURN(l_category_id);
986
987 EXCEPTION
988 WHEN NO_DATA_FOUND THEN
989 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
990 THEN
991 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
992 , G_MODULE_NAME || l_procedure_name||'.No data found exception'
993 , 'Exception'
994 );
995 END IF;
996 l_category_id := null;
997 RETURN(l_category_id);
998
999 WHEN TOO_MANY_ROWS
1000 THEN
1001 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1002 THEN
1003 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1004 , G_MODULE_NAME || l_procedure_name||'.too many rows exception'
1005 , 'Exception'
1006 );
1007 END IF;
1008 l_category_id := null;
1009 RETURN(l_category_id);
1010
1011 WHEN OTHERS THEN
1012 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1013 THEN
1014 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1015 , G_MODULE_NAME || l_procedure_name||'.Others exception'
1016 , 'Exception'
1017 );
1018 END IF;
1019 l_category_id := null;
1020 RETURN(l_category_id);
1021 END Get_Category_Id;
1022
1023
1024
1025 --========================================================================
1026 -- PROCEDURE : Get_Commodity_Info PUBLIC
1027 -- PARAMETERS: x_movement_transaction IN OUT Movement Statistics Record
1028 -- x_movement_transaction OUT Movement Statistics Record
1029 -- COMMENT : Procedure to populate the commoddity information for the item
1030 --=========================================================================
1031
1032 PROCEDURE Get_Commodity_Info
1033 ( x_movement_transaction IN OUT NOCOPY
1034 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1035 )
1036 IS
1037 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1038 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Commodity_Info';
1039
1040 CURSOR l_com
1041 IS
1042 SELECT
1043 substrb(mkv.concatenated_segments,1,230)
1044 , substrb(mic.description,1,230)
1045 FROM
1046 MTL_CATEGORIES mic
1047 , MTL_CATEGORIES_KFV mkv
1048 WHERE mic.category_id = mkv.category_id
1049 AND mic.category_id = x_movement_transaction.category_id;
1050
1051 BEGIN
1052 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1053 THEN
1054 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1055 , G_MODULE_NAME || l_procedure_name || '.begin'
1056 ,'enter procedure'
1057 );
1058 END IF;
1059
1060 l_movement_transaction := x_movement_transaction;
1061
1062 OPEN l_com;
1063 FETCH l_com
1064 INTO
1065 x_movement_transaction.commodity_code
1066 , x_movement_transaction.commodity_description;
1067 CLOSE l_com;
1068
1069 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1070 THEN
1071 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1072 , G_MODULE_NAME || l_procedure_name || '.end'
1073 ,'exit procedure'
1074 );
1075 END IF;
1076 EXCEPTION
1077 WHEN NO_DATA_FOUND THEN
1078 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1079 THEN
1080 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1081 , G_MODULE_NAME ||l_procedure_name||'.No data found exception'
1082 , 'Exception'
1083 );
1084 END IF;
1085 x_movement_transaction := l_movement_transaction;
1086
1087 WHEN OTHERS THEN
1088 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1089 THEN
1090 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1091 , G_MODULE_NAME ||l_procedure_name||'.Others exception'
1092 , 'Exception'
1093 );
1094 END IF;
1095 x_movement_transaction := l_movement_transaction;
1096
1097 END Get_Commodity_Info;
1098
1099 --========================================================================
1100 -- PROCEDURE : Get_Order_Number PUBLIC
1101 -- PARAMETERS: x_movement_transaction IN OUT Movement Statistics Record
1102 --
1103 -- COMMENT : Procedure to populate the Order Number
1104 --=========================================================================
1105
1106 PROCEDURE Get_Order_Number
1107 ( x_movement_transaction IN OUT NOCOPY
1108 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1109 )
1110 IS
1111 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1112 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Order_Number';
1113
1114 CURSOR l_on
1115 IS
1116 SELECT
1117 oh.order_number
1118 , oh.org_id
1119 FROM
1120 OE_ORDER_HEADERS_ALL oh
1121 WHERE oh.header_id = x_movement_transaction.order_header_id;
1122
1123 BEGIN
1124 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1125 THEN
1126 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1127 , G_MODULE_NAME || l_procedure_name || '.begin'
1128 ,'enter procedure'
1129 );
1130 END IF;
1131
1132 l_movement_transaction := x_movement_transaction;
1133
1134 OPEN l_on;
1135 FETCH l_on
1136 INTO
1137 x_movement_transaction.order_number
1138 , x_movement_transaction.org_id;
1139 CLOSE l_on;
1140
1141 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1142 THEN
1143 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1144 , G_MODULE_NAME || l_procedure_name || '.end'
1145 ,'exit procedure'
1146 );
1147 END IF;
1148 EXCEPTION
1149 WHEN NO_DATA_FOUND THEN
1150 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1151 THEN
1152 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1153 , G_MODULE_NAME ||l_procedure_name||'.No data found exception'
1154 , 'Exception'
1155 );
1156 END IF;
1157 x_movement_transaction := l_movement_transaction;
1158 WHEN OTHERS THEN
1159 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1160 THEN
1161 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1162 , G_MODULE_NAME ||l_procedure_name||'.Others exception'
1163 , 'Exception'
1164 );
1165 END IF;
1166 x_movement_transaction := l_movement_transaction;
1167
1168 END Get_Order_Number;
1169
1170 --========================================================================
1171 -- FUNCTION : Get_Site_Location
1172 -- PARAMETERS: p_site_use_id Site id
1173 -- COMMENT : Function that returns the territory code where the site
1174 -- is located.
1175 --=========================================================================
1176
1177 FUNCTION Get_Site_Location
1178 ( p_site_use_id IN NUMBER
1179 )
1180 RETURN VARCHAR2
1181 IS
1182 l_site_location VARCHAR2(80);
1183 l_short_name VARCHAR2(80);
1184
1185 CURSOR l_ra_site
1186 IS
1187 SELECT
1188 hzl.country
1189 FROM
1190 HZ_CUST_ACCT_SITES_ALL ras
1191 , HZ_CUST_SITE_USES_ALL raa
1192 , HZ_LOCATIONS hzl
1193 , HZ_PARTY_SITES hzp
1194 WHERE ras.cust_acct_site_id = raa.cust_acct_site_id
1195 AND NVL(ras.org_id, -1) = NVL(raa.org_id, -1) --fix bug 4015171
1196 AND ras.party_site_id = hzp.party_site_id
1197 AND hzl.location_id = hzp.location_id
1198 AND raa.site_use_id = p_site_use_id;
1199
1200
1201 CURSOR l_fnd_cy
1202 IS
1203 SELECT
1204 DISTINCT territory_code
1205 FROM
1206 FND_TERRITORIES_TL --fix bug 4165090
1207 WHERE
1208 territory_short_name = l_short_name;
1209
1210 BEGIN
1211 OPEN l_ra_site;
1212 FETCH l_ra_site INTO
1213 l_site_location;
1214 CLOSE l_ra_site;
1215
1216 IF length(l_site_location) > 3 THEN
1217 l_short_name := l_site_location;
1218 OPEN l_fnd_cy;
1219 FETCH l_fnd_cy INTO
1220 l_site_location;
1221 CLOSE l_fnd_cy;
1222
1223 END IF;
1224
1225 RETURN l_site_location;
1226
1227 EXCEPTION
1228 WHEN NO_DATA_FOUND THEN
1229 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1230 THEN
1231 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1232 , G_MODULE_NAME ||'Get_Site_Location'||'.No data found exception'
1233 , 'Exception'
1234 );
1235 END IF;
1236 RETURN null;
1237 WHEN TOO_MANY_ROWS
1238 THEN
1239 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1240 THEN
1241 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1242 , G_MODULE_NAME ||'Get_Site_Location'||'.too many rows exception'
1243 , 'Exception'
1244 );
1245 END IF;
1246 RETURN null;
1247 WHEN OTHERS THEN
1248 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1249 THEN
1250 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1251 , G_MODULE_NAME ||'Get_Site_Location'||'.Others exception'
1252 , 'Exception'
1253 );
1254 END IF;
1255 RETURN null;
1256 END Get_Site_Location;
1257
1258
1259 --========================================================================
1260 -- FUNCTION : Get_Org_Location
1261 -- PARAMETERS: p_warehouse_id warehouse id
1262 -- COMMENT : Function that returns the territory code where the warehouse
1263 -- is located.
1264 --=========================================================================
1265
1266 FUNCTION Get_Org_Location
1267 ( p_warehouse_id IN NUMBER
1268 )
1269 RETURN VARCHAR2
1270 IS
1271 l_org_location VARCHAR2(80);
1272 l_short_name VARCHAR2(80);
1273
1274 CURSOR l_org
1275 IS
1276 SELECT
1277 hrl.country
1278 FROM
1279 HR_ALL_ORGANIZATION_UNITS hr
1280 , HR_LOCATIONS_ALL hrl
1281 WHERE hr.location_id = hrl.location_id
1282 AND hr.organization_id = p_warehouse_id;
1283
1284 CURSOR l_fnd_cy
1285 IS
1286 SELECT
1287 DISTINCT territory_code
1288 FROM
1289 FND_TERRITORIES_TL --fix bug 4165090
1290 WHERE
1291 territory_short_name = l_short_name;
1292
1293 BEGIN
1294
1295 OPEN l_org;
1296 FETCH l_org INTO
1297 l_org_location;
1298 CLOSE l_org;
1299
1300 IF length(l_org_location) > 3 THEN
1301 l_short_name := l_org_location;
1302 OPEN l_fnd_cy;
1303 FETCH l_fnd_cy INTO
1304 l_org_location;
1305 CLOSE l_fnd_cy;
1306 END IF;
1307
1308 RETURN l_org_location;
1309
1310 EXCEPTION
1311 WHEN NO_DATA_FOUND THEN
1312 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1313 THEN
1314 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1315 , G_MODULE_NAME ||'Get_Org_Location'||'.No data found exception'
1316 , 'Exception'
1317 );
1318 END IF;
1319 RETURN null;
1320 WHEN TOO_MANY_ROWS
1321 THEN
1322 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1323 THEN
1324 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1325 , G_MODULE_NAME ||'Get_Org_Location'||'.too many rows exception'
1326 , 'Exception'
1327 );
1328 END IF;
1329 RETURN null;
1330 WHEN OTHERS THEN
1331 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1332 THEN
1333 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1334 , G_MODULE_NAME ||'Get_Org_Location'||'.Others exception'
1335 , 'Exception'
1336 );
1337 END IF;
1338 RETURN null;
1339 END Get_Org_Location;
1340
1341 --========================================================================
1342 -- FUNCTION : Get_Subinv_Location
1343 -- PARAMETERS: p_warehouse_id warehouse id
1344 -- p_subinv_code the subinventory code
1345 -- COMMENT : Function that returns the territory code where the subinventory
1346 -- is located.
1347 --=========================================================================
1348
1349 FUNCTION Get_Subinv_Location
1350 ( p_warehouse_id IN NUMBER
1351 , p_subinv_code IN VARCHAR2
1352 )
1353 RETURN VARCHAR2
1354 IS
1355 l_subinv_location VARCHAR2(80);
1356 l_short_name VARCHAR2(80);
1357
1358 CURSOR l_country
1359 IS
1360 SELECT
1361 hrl.country
1362 FROM
1363 mtl_secondary_inventories msi
1364 , HR_LOCATIONS_ALL hrl
1365 WHERE hrl.location_id = msi.location_id
1366 AND msi.organization_id = p_warehouse_id
1367 AND msi.secondary_inventory_name = p_subinv_code;
1368
1369 CURSOR l_fnd_cy
1370 IS
1371 SELECT
1372 DISTINCT territory_code
1373 FROM
1374 FND_TERRITORIES_TL --fix bug 4165090
1375 WHERE
1376 territory_short_name = l_short_name;
1377 BEGIN
1378 OPEN l_country;
1379 FETCH l_country INTO
1380 l_subinv_location;
1381
1382 IF l_country%NOTFOUND
1383 THEN
1384 RETURN null;
1385 CLOSE l_country;
1386 ELSE
1387 IF length(l_subinv_location) > 3
1388 THEN
1389 l_short_name := l_subinv_location;
1390
1391 OPEN l_fnd_cy;
1392 FETCH l_fnd_cy INTO
1393 l_subinv_location;
1394 CLOSE l_fnd_cy;
1395 END IF;
1396
1397 CLOSE l_country;
1398 END IF;
1399
1400 RETURN l_subinv_location;
1401 EXCEPTION
1402 WHEN NO_DATA_FOUND THEN
1403 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1404 THEN
1405 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1406 , G_MODULE_NAME ||'Get_subinv_Location'||'.No data found exception'
1407 , 'Exception'
1408 );
1409 END IF;
1410 RETURN null;
1411 WHEN TOO_MANY_ROWS
1412 THEN
1413 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1414 THEN
1415 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1416 , G_MODULE_NAME ||'Get_subinv_Location'||'.too many rows exception'
1417 , 'Exception'
1418 );
1419 END IF;
1420 RETURN null;
1421 WHEN OTHERS THEN
1422 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1423 THEN
1424 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1425 , G_MODULE_NAME ||'Get_subinv_Location'||'.Others exception'
1426 , 'Exception'
1427 );
1428 END IF;
1429 RETURN null;
1430 END Get_subinv_Location;
1431
1432 --========================================================================
1433 -- FUNCTION : Get_Vendor_Location
1434 -- PARAMETERS: p_vendor_site_id Vendor Site
1435 -- COMMENT : Function that returns the territory code where the vendor site
1436 -- is located.
1437 --=========================================================================
1438
1439 FUNCTION Get_vendor_Location
1440 ( p_vendor_site_id IN NUMBER
1441 )
1442 RETURN VARCHAR2
1443 IS
1444 l_vendor_location VARCHAR2(150);
1445
1446 CURSOR l_vendor
1447 IS
1448 SELECT
1449 pov.country
1450 FROM
1451 PO_VENDOR_SITES_ALL pov
1452 WHERE pov.vendor_site_id = p_vendor_site_id;
1453
1454 BEGIN
1455
1456 OPEN l_vendor;
1457 FETCH l_vendor INTO
1458 l_vendor_location;
1459 CLOSE l_vendor;
1460
1461 RETURN l_vendor_location;
1462
1463 EXCEPTION
1464 WHEN NO_DATA_FOUND THEN
1465 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1466 THEN
1467 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1468 , G_MODULE_NAME ||'Get_Vendor_Location'||'.No data found exception'
1469 , 'Exception'
1470 );
1471 END IF;
1472 RETURN null;
1473 WHEN TOO_MANY_ROWS
1474 THEN
1475 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1476 THEN
1477 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1478 , G_MODULE_NAME ||'Get_Vendor_Location'||'.too many rows exception'
1479 , 'Exception'
1480 );
1481 END IF;
1482 RETURN null;
1483 WHEN OTHERS THEN
1484 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1485 THEN
1486 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1487 , G_MODULE_NAME ||'Get_Vendor_Location'||'.Others exception'
1488 , 'Exception'
1489 );
1490 END IF;
1491 RETURN null;
1492 END Get_Vendor_Location;
1493
1494
1495 --========================================================================
1496 -- FUNCTION : Get_Zone_Code
1497 -- PARAMETERS: p_territory_code territory code
1498 -- p_zone_code zone code
1499 -- p_trans_date transaction date
1500 -- COMMENT : Function that returns the zone code if the zone code
1501 -- and territory code matches and entry in country assignments
1502 --=========================================================================
1503
1504
1505 FUNCTION Get_Zone_Code
1506 ( p_territory_code IN VARCHAR2
1507 , p_zone_code IN VARCHAR2
1508 , p_trans_date IN VARCHAR2
1509 )
1510 RETURN VARCHAR2
1511 IS
1512 l_zone_code VARCHAR2(10);
1513 -- cursor to get the zone so that we can determine if the
1514 -- transaction is an Intrastat or an extrastat
1515
1516 CURSOR c_zone IS
1517 SELECT
1518 zone_code
1519 FROM
1520 MTL_COUNTRY_ASSIGNMENTS
1521 WHERE territory_code = p_territory_code
1522 AND zone_code = p_zone_code
1523 AND p_trans_date BETWEEN (start_date) and (NVL(end_date,p_trans_date));
1524
1525 BEGIN
1526
1527 OPEN c_zone;
1528 FETCH c_zone
1529 INTO l_zone_code;
1530
1531 IF c_zone%NOTFOUND THEN
1532 l_zone_code:= null;
1533 END IF;
1534
1535 CLOSE c_zone;
1536
1537 RETURN l_zone_code;
1538
1539 EXCEPTION
1540 WHEN NO_DATA_FOUND THEN
1541 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1542 THEN
1543 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1544 , G_MODULE_NAME ||'Get_Zone_Code'||'.No data found exception'
1545 , 'Exception'
1546 );
1547 END IF;
1548 RETURN null;
1549 WHEN TOO_MANY_ROWS
1550 THEN
1551 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1552 THEN
1553 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1554 , G_MODULE_NAME ||'Get_Zone_Code'||'.too many rows exception'
1555 , 'Exception'
1556 );
1557 END IF;
1558 RETURN null;
1559 WHEN OTHERS THEN
1560 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1561 THEN
1562 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1563 , G_MODULE_NAME ||'Get_Zone_Code'||'.Others exception'
1564 , 'Exception'
1565 );
1566 END IF;
1567 RETURN null;
1568 END Get_Zone_Code;
1569
1570
1571 --========================================================================
1572 -- PROCEDURE : Get_Vendor_Info PUBLIC
1573 -- PARAMETERS: x_movement_transaction IN OUT Movement Statistics Record
1574 --
1575 -- COMMENT : Procedure to populate the vendor info
1576 --=========================================================================
1577
1578 PROCEDURE Get_Vendor_Info
1579 ( x_movement_transaction IN OUT NOCOPY
1580 INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
1581 )
1582 IS
1583 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
1584
1585 CURSOR l_ven
1586 IS
1587 SELECT
1588 pov.vendor_name
1589 , pov.segment1
1590 , povs.vendor_site_code
1591 , povs.province
1592 , povs.vat_registration_num
1593 FROM
1594 PO_VENDORS pov
1595 , PO_VENDOR_SITES_ALL povs
1596 WHERE pov.vendor_id = povs.vendor_id
1597 AND pov.vendor_id = x_movement_transaction.vendor_id
1598 AND povs.vendor_site_id = x_movement_transaction.vendor_site_id;
1599
1600 BEGIN
1601
1602 l_movement_transaction := x_movement_transaction;
1603
1604 OPEN l_ven;
1605 FETCH l_ven
1606 INTO
1607 x_movement_transaction.vendor_name
1608 , x_movement_transaction.vendor_number
1609 , x_movement_transaction.vendor_site
1610 , x_movement_transaction.area
1611 , x_movement_transaction.customer_vat_number;
1612 CLOSE l_ven;
1613
1614 EXCEPTION
1615 WHEN NO_DATA_FOUND THEN
1616 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1617 THEN
1618 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1619 , G_MODULE_NAME ||'Get_Vendor_Info'||'.No data found exception'
1620 , 'Exception'
1621 );
1622 END IF;
1623 x_movement_transaction := l_movement_transaction;
1624
1625 WHEN OTHERS THEN
1626 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1627 THEN
1628 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1629 , G_MODULE_NAME ||'Get_Vendor_Info'||'.Others exception'
1630 , 'Exception'
1631 );
1632 END IF;
1633 x_movement_transaction := l_movement_transaction;
1634 END Get_Vendor_Info;
1635
1636 --========================================================================
1637 -- FUNCTION : Get_Cust_VAT_Number
1638 -- PARAMETERS: p_site_use_id Site id
1639 -- COMMENT : Function that returns the vat number for SO
1640 --=========================================================================
1641
1642 FUNCTION Get_Cust_VAT_Number
1643 ( p_site_use_id IN NUMBER
1644 )
1645 RETURN VARCHAR2
1646 IS
1647 l_vat_number VARCHAR2(50);
1648
1649 CURSOR l_ra_vat
1650 IS
1651 SELECT
1652 raa.tax_reference
1653 FROM
1654 HZ_CUST_SITE_USES_ALL raa
1655 WHERE raa.site_use_id = p_site_use_id;
1656
1657
1658 BEGIN
1659 OPEN l_ra_vat;
1660 FETCH l_ra_vat INTO
1661 l_vat_number;
1662 CLOSE l_ra_vat;
1663
1664
1665 RETURN l_vat_number;
1666
1667 EXCEPTION
1668 WHEN NO_DATA_FOUND THEN
1669 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1670 THEN
1671 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1672 , G_MODULE_NAME ||'Get_Cust_VAT_Number'||'.No data found exception'
1673 , 'Exception'
1674 );
1675 END IF;
1676 RETURN null;
1677 WHEN TOO_MANY_ROWS
1678 THEN
1679 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1680 THEN
1681 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1682 , G_MODULE_NAME ||'Get_Cust_VAT_Number'||'.too many rows exception'
1683 , 'Exception'
1684 );
1685 END IF;
1686 RETURN null;
1687 WHEN OTHERS THEN
1688 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1689 THEN
1690 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1691 , G_MODULE_NAME ||'Get_Cust_VAT_Number'||'.Others exception'
1692 , 'Exception'
1693 );
1694 END IF;
1695 RETURN null;
1696 END Get_Cust_VAT_Number;
1697
1698 --========================================================================
1699 -- FUNCTION : Get_Org_VAT_Number
1700 -- PARAMETERS: p_entity_org_id legal entity id
1701 -- COMMENT : Function that returns the vat number for legal entity used
1702 -- in inter-org transfer
1703 --=========================================================================
1704
1705 FUNCTION Get_Org_VAT_Number
1706 ( p_entity_org_id IN NUMBER
1707 , p_date IN DATE
1708 )
1709 RETURN VARCHAR2
1710 IS
1711 l_vat_number VARCHAR2(1000);
1712 l_return_status VARCHAR2(1);
1713 l_msg_count NUMBER;
1714 l_msg_data VARCHAR2(300);
1715 l_effective_date DATE;
1716
1717 BEGIN
1718 --Call XLE package for VAT number:
1719 XLE_UTILITIES_GRP.Get_FP_VATRegistration_LEID
1720 ( p_api_version => 1.0
1721 , p_init_msg_list => FND_API.G_FALSE
1722 , p_commit => FND_API.G_FALSE
1723 , p_effective_date => p_date
1724 , x_return_status => l_return_status
1725 , x_msg_count => l_msg_count
1726 , x_msg_data => l_msg_data
1727 , p_legal_entity_id => p_entity_org_id
1728 , x_registration_number => l_vat_number
1729 );
1730
1731 RETURN SUBSTR(l_vat_number,1,50);
1732
1733 EXCEPTION
1734 WHEN NO_DATA_FOUND THEN
1735 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1736 THEN
1737 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1738 , G_MODULE_NAME ||'Get_Org_VAT_Number'||'.No data found exception'
1739 , 'Exception'
1740 );
1741 END IF;
1742 RETURN null;
1743 WHEN TOO_MANY_ROWS
1744 THEN
1745 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1746 THEN
1747 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1748 , G_MODULE_NAME ||'Get_Org_VAT_Number'||'.too many rows exception'
1749 , 'Exception'
1750 );
1751 END IF;
1752 RETURN null;
1753 WHEN OTHERS THEN
1754 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1755 THEN
1756 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1757 , G_MODULE_NAME ||'Get_Org_VAT_Number'||'.Others exception'
1758 , 'Exception'
1759 );
1760 END IF;
1761 RETURN null;
1762 END Get_Org_VAT_Number;
1763
1764 --========================================================================
1765 -- FUNCTION : Get_SO_Legal_Entity PUBLIC
1766 -- PARAMETERS: p_order_line_id order line id
1767 --
1768 -- VERSION : current version 1.0
1769 -- initial version 1.0
1770 -- COMMENT : Function that returns the legal entity where this sales order
1771 -- is created.
1772 --=======================================================================--
1773
1774 FUNCTION Get_SO_Legal_Entity
1775 ( p_order_line_id IN NUMBER
1776 )
1777 RETURN NUMBER
1778 IS
1779 l_sold_to_org_id NUMBER;
1780 l_so_ou_id NUMBER;
1781 l_so_le_id NUMBER;
1782
1783 --Fix bug 5437773, replace sold_from_org_id with org_id
1784 --org_id is the correct column to get operating unit
1785 CURSOR l_so_ou IS
1786 SELECT
1787 sold_to_org_id
1788 , org_id
1789 FROM
1790 oe_order_lines_all
1791 WHERE line_id = p_order_line_id;
1792
1793
1794 BEGIN
1795 OPEN l_so_ou;
1796 FETCH l_so_ou INTO
1797 l_sold_to_org_id
1798 , l_so_ou_id;
1799
1800 IF l_so_ou%NOTFOUND
1801 THEN
1802 l_so_le_id:= null;
1803 ELSE
1804 /* bug 8467743 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info was always returning default legal entity*/
1805 /* l_so_le_id := XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info
1806 ( p_customer_type => 'SOLD_TO'
1807 , p_customer_id => l_sold_to_org_id
1808 , p_operating_unit_id => l_so_ou_id);*/
1809 SELECT To_Number(NVL(O3.ORG_INFORMATION2,-1))
1810 INTO l_so_le_id
1811 FROM HR_ALL_ORGANIZATION_UNITS O
1812 , HR_ORGANIZATION_INFORMATION O2
1813 , HR_ORGANIZATION_INFORMATION O3
1814 WHERE O.ORGANIZATION_ID = O2.ORGANIZATION_ID
1815 AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
1816 AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
1817 AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
1818 AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
1819 AND O2.ORG_INFORMATION2 = 'Y'
1820 AND O.ORGANIZATION_ID = l_so_ou_id;
1821
1822 END IF;
1823
1824 CLOSE l_so_ou;
1825
1826 RETURN l_so_le_id;
1827 EXCEPTION
1828 WHEN NO_DATA_FOUND THEN
1829 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1830 THEN
1831 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1832 , G_MODULE_NAME ||'Get_SO_Legal_Entity'||'.No data found exception'
1833 , 'Exception'
1834 );
1835 END IF;
1836 RETURN null;
1837 WHEN TOO_MANY_ROWS
1838 THEN
1839 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1840 THEN
1841 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1842 , G_MODULE_NAME ||'Get_SO_Legal_Entity'||'.too many rows exception'
1843 , 'Exception'
1844 );
1845 END IF;
1846 RETURN null;
1847 WHEN OTHERS THEN
1848 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1849 THEN
1850 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1851 , G_MODULE_NAME ||'Get_SO_Legal_Entity'||'.Others exception'
1852 , 'Exception'
1853 );
1854 END IF;
1855 RETURN null;
1856 END Get_SO_Legal_Entity;
1857
1858 --========================================================================
1859 -- FUNCTION : Get_Shipping_Legal_Entity PUBLIC
1860 -- PARAMETERS: p_warehouse_id warehouse id
1861 --
1862 -- VERSION : current version 1.0
1863 -- initial version 1.0
1864 -- COMMENT : Function that returns the legal entity where this sales order
1865 -- is ship released.
1866 --=======================================================================--
1867
1868 FUNCTION Get_Shipping_Legal_Entity
1869 ( p_warehouse_id IN NUMBER
1870 )
1871 RETURN NUMBER
1872 IS
1873 l_shipping_le_id NUMBER;
1874
1875 CURSOR l_shipping_le IS
1876 SELECT
1877 TO_NUMBER(org_information2)
1878 FROM
1879 hr_organization_information
1880 WHERE org_information_context = 'Accounting Information'
1881 AND organization_id = p_warehouse_id;
1882
1883 BEGIN
1884 OPEN l_shipping_le;
1885 FETCH l_shipping_le
1886 INTO l_shipping_le_id;
1887
1888 IF l_shipping_le%NOTFOUND
1889 THEN
1890 l_shipping_le_id:= null;
1891 END IF;
1892
1893 CLOSE l_shipping_le;
1894
1895 RETURN l_shipping_le_id;
1896 EXCEPTION
1897 WHEN NO_DATA_FOUND THEN
1898 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1899 THEN
1900 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1901 , G_MODULE_NAME ||'Get_Shipping_Legal_Entity'||'.No data found exception'
1902 , 'Exception'
1903 );
1904 END IF;
1905 RETURN null;
1906 WHEN TOO_MANY_ROWS
1907 THEN
1908 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1909 THEN
1910 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1911 , G_MODULE_NAME ||'Get_Shipping_Legal_Entity'||'.too many rows exception'
1912 , 'Exception'
1913 );
1914 END IF;
1915 RETURN null;
1916 WHEN OTHERS THEN
1917 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1918 THEN
1919 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1920 , G_MODULE_NAME ||'Get_Shipping_Legal_Entity'||'.Others exception'
1921 , 'Exception'
1922 );
1923 END IF;
1924 RETURN null;
1925 END Get_Shipping_Legal_Entity;
1926
1927 --========================================================================
1928 -- FUNCTION : Get_LE_Currency PUBLIC
1929 -- PARAMETERS: p_le_id legal entity id
1930 --
1931 -- VERSION : current version 1.0
1932 -- initial version 1.0
1933 -- COMMENT : Function that returns the functional currency of a given
1934 -- legal entity.
1935 --=======================================================================--
1936
1937 FUNCTION Get_LE_Currency
1938 ( p_le_id IN NUMBER
1939 )
1940 RETURN VARCHAR2
1941 IS
1942 l_currency_code VARCHAR2(3);
1943
1944 CURSOR c_currency IS
1945 SELECT
1946 currency_code
1947 FROM
1948 gl_ledger_le_v
1949 WHERE legal_entity_id = p_le_id
1950 AND ledger_category_code = 'PRIMARY';
1951 BEGIN
1952 OPEN c_currency;
1953 FETCH c_currency
1954 INTO l_currency_code;
1955
1956 IF c_currency%NOTFOUND
1957 THEN
1958 l_currency_code:= null;
1959 END IF;
1960
1961 CLOSE c_currency;
1962
1963 RETURN l_currency_code;
1964
1965 EXCEPTION
1966 WHEN NO_DATA_FOUND THEN
1967 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1968 THEN
1969 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1970 , G_MODULE_NAME ||'Get_LE_Currency'||'.No data found exception'
1971 , 'Exception'
1972 );
1973 END IF;
1974 RETURN null;
1975 WHEN TOO_MANY_ROWS
1976 THEN
1977 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1978 THEN
1979 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1980 , G_MODULE_NAME ||'Get_LE_Currency'||'.too many rows exception'
1981 , 'Exception'
1982 );
1983 END IF;
1984 RETURN null;
1985 WHEN OTHERS THEN
1986 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1987 THEN
1988 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1989 , G_MODULE_NAME ||'Get_LE_Currency'||'.Others exception'
1990 , 'Exception'
1991 );
1992 END IF;
1993 RETURN null;
1994 END Get_LE_Currency;
1995
1996 --========================================================================
1997 -- FUNCTION : Get_LE_Location PUBLIC
1998 -- PARAMETERS: p_le_id legal entity id
1999 --
2000 -- VERSION : current version 1.0
2001 -- initial version 1.0
2002 -- COMMENT : Function that returns the country location of a given
2003 -- legal entity.
2004 --=======================================================================--
2005
2006 FUNCTION Get_LE_Location
2007 ( p_le_id IN NUMBER
2008 )
2009 RETURN VARCHAR2
2010 IS
2011 l_country hr_locations_all.country%TYPE;
2012 l_short_name hr_locations_all.country%TYPE;
2013
2014 CURSOR c_country IS
2015 SELECT
2016 country
2017 FROM
2018 xle_firstparty_information_v
2019 WHERE legal_entity_id = p_le_id;
2020
2021 CURSOR c_terr_code
2022 IS
2023 SELECT
2024 DISTINCT territory_code
2025 FROM
2026 FND_TERRITORIES_TL --fix bug 4165090
2027 WHERE
2028 territory_short_name = l_short_name;
2029 BEGIN
2030 OPEN c_country;
2031 FETCH c_country INTO
2032 l_country;
2033 CLOSE c_country;
2034
2035 IF length(l_country) > 3
2036 THEN
2037 l_short_name := l_country;
2038
2039 OPEN c_terr_code;
2040 FETCH c_terr_code INTO
2041 l_country;
2042 CLOSE c_terr_code;
2043 END IF;
2044
2045 RETURN l_country;
2046
2047 EXCEPTION
2048 WHEN NO_DATA_FOUND THEN
2049 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2050 THEN
2051 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2052 , G_MODULE_NAME ||'Get_LE_Location'||'.No data found exception'
2053 , 'Exception'
2054 );
2055 END IF;
2056 RETURN null;
2057 WHEN TOO_MANY_ROWS
2058 THEN
2059 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2060 THEN
2061 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2062 , G_MODULE_NAME ||'Get_LE_Location'||'.too many rows exception'
2063 , 'Exception'
2064 );
2065 END IF;
2066 RETURN null;
2067 WHEN OTHERS THEN
2068 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2069 THEN
2070 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2071 , G_MODULE_NAME ||'Get_LE_Location'||'.Others exception'
2072 , 'Exception'
2073 );
2074 END IF;
2075 RETURN null;
2076 END Get_LE_Location;
2077
2078
2079 --========================================================================
2080 -- PROCEDURE : Get_Weight_Precision PUBLIC
2081 -- PARAMETERS: p_legal_entity_id IN legal entity id
2082 -- p_zone_code IN zone code
2083 -- p_usage_type IN usage type
2084 -- p_stat_type IN stat type
2085 -- x_weight_precision OUT weight precision
2086 -- x_rep_rounding OUT reporting rounding method
2087 --
2088 -- VERSION : current version 1.0
2089 -- initial version 1.0
2090 -- COMMENT : Function that returns the weight precision defined on
2091 -- parameter form
2092 --=======================================================================--
2093
2094 PROCEDURE Get_Weight_Precision
2095 ( p_legal_entity_id IN NUMBER
2096 , p_zone_code IN VARCHAR2
2097 , p_usage_type IN VARCHAR2
2098 , p_stat_type IN VARCHAR2
2099 , x_weight_precision OUT NOCOPY NUMBER
2100 , x_rep_rounding OUT NOCOPY VARCHAR2
2101 )
2102 IS
2103
2104 CURSOR l_prec_rounding IS
2105 SELECT
2106 weight_precision
2107 , reporting_rounding
2108 FROM
2109 mtl_stat_type_usages
2110 WHERE legal_entity_id = p_legal_entity_id
2111 AND zone_code = p_zone_code
2112 AND usage_type = p_usage_type
2113 AND stat_type = p_stat_type;
2114 BEGIN
2115 OPEN l_prec_rounding;
2116 FETCH l_prec_rounding
2117 INTO x_weight_precision
2118 , x_rep_rounding;
2119
2120 IF l_prec_rounding%NOTFOUND
2121 THEN
2122 x_weight_precision := 0;
2123 x_rep_rounding := 'NORMAL';
2124 END IF;
2125
2126 CLOSE l_prec_rounding;
2127
2128 EXCEPTION
2129 WHEN NO_DATA_FOUND THEN
2130 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2131 THEN
2132 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2133 , G_MODULE_NAME ||'Get_Weight_Precision'||'.No data found exception'
2134 , 'Exception'
2135 );
2136 END IF;
2137 WHEN TOO_MANY_ROWS
2138 THEN
2139 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2140 THEN
2141 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2142 , G_MODULE_NAME ||'Get_Weight_Precision'||'.too many rows exception'
2143 , 'Exception'
2144 );
2145 END IF;
2146 WHEN OTHERS THEN
2147 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2148 THEN
2149 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2150 , G_MODULE_NAME ||'Get_Weight_Precision'||'.Others exception'
2151 , 'Exception'
2152 );
2153 END IF;
2154 END Get_Weight_Precision;
2155
2156 --========================================================================
2157 -- FUNCTION : Round_Number PUBLIC
2158 -- PARAMETERS: p_number number to be rounded
2159 -- p_precision the precision to be rounded to
2160 -- p_rounding_method rounding method
2161 --
2162 -- VERSION : current version 1.0
2163 -- initial version 1.0
2164 -- COMMENT : Function that returns a rounded number
2165 --=======================================================================--
2166 FUNCTION Round_Number
2167 ( p_number IN NUMBER
2168 , p_precision IN NUMBER
2169 , p_rounding_method IN VARCHAR2
2170 )
2171 RETURN NUMBER
2172 IS
2173 l_number NUMBER;
2174
2175 BEGIN
2176 IF p_rounding_method = 'NORMAL'
2177 THEN
2178 l_number := ROUND(p_number, p_precision);
2179 ELSIF p_rounding_method = 'TRUNCATE'
2180 THEN
2181 l_number := TRUNC(p_number, p_precision);
2182 ELSIF p_rounding_method = 'UP'
2183 THEN
2184 SELECT CEIL(p_number * POWER(10,p_precision))/POWER(10,p_precision)
2185 INTO l_number
2186 FROM dual;
2187 ELSE
2188 l_number := ROUND(p_number, p_precision);
2189 END IF;
2190
2191 RETURN l_NUMBER;
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2195 THEN
2196 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2197 , G_MODULE_NAME ||'Round_Number'
2198 ||'.Others exception'
2199 , 'Exception'
2200 );
2201 END IF;
2202 RETURN null;
2203 END Round_Number;
2204
2205 --========================================================================
2206 -- FUNCTION : Get_Org_From_Le PUBLIC
2207 -- PARAMETERS: p_le_id legal entity id
2208 --
2209 -- VERSION : current version 1.0
2210 -- initial version 1.0
2211 -- COMMENT : Function that returns a rounded number
2212 --=======================================================================--
2213 FUNCTION Get_Org_From_Le
2214 ( p_le_id IN NUMBER
2215 )
2216 RETURN NUMBER
2217 IS
2218 l_organization_id NUMBER;
2219
2220 --
2221 -- bug 9891237
2222 -- modified the query to avoid the invalid number error
2223 --
2224 CURSOR c_org_id
2225 IS
2226 SELECT
2227 organization_id
2228 FROM hr_organization_information
2229 WHERE org_information_context = 'Accounting Information'
2230 --AND to_number(org_information2) = p_le_id
2231 AND org_information2 = to_char(p_le_id)
2232 AND rownum = 1;
2233 BEGIN
2234 OPEN c_org_id;
2235 FETCH c_org_id INTO
2236 l_organization_id;
2237
2238 IF c_org_id%NOTFOUND
2239 THEN
2240 l_organization_id := null;
2241 END IF;
2242
2243 CLOSE c_org_id;
2244
2245 RETURN l_organization_id;
2246 EXCEPTION
2247 WHEN OTHERS THEN
2248 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2249 THEN
2250 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2251 , G_MODULE_NAME ||'Get_Org_From_Le'
2252 ||'.Others exception'
2253 , 'Exception'
2254 );
2255 END IF;
2256 RETURN null;
2257 END Get_Org_From_Le;
2258
2259
2260 --========================================================================
2261 -- PROCEDURE : Log_Initialize PUBLIC
2262 -- COMMENT : Initializes the log facility. It should be called from
2263 -- the top level procedure of each concurrent program
2264 --=======================================================================--
2265 PROCEDURE Log_Initialize
2266 IS
2267 BEGIN
2268 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
2269 IF g_log_level IS NULL THEN
2270 g_log_mode := 'OFF';
2271 ELSE
2272 IF (TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')) <> 0) THEN
2273 g_log_mode := 'SRS';
2274 ELSE
2275 g_log_mode := 'SQL';
2276 END IF;
2277 END IF;
2278
2279 END Log_Initialize;
2280
2281
2282 --========================================================================
2283 -- PROCEDURE : Log PUBLIC
2284 -- PARAMETERS: p_level IN priority of the message - from
2285 -- highest to lowest:
2286 -- -- G_LOG_ERROR
2287 -- -- G_LOG_EXCEPTION
2288 -- -- G_LOG_EVENT
2289 -- -- G_LOG_PROCEDURE
2290 -- -- G_LOG_STATEMENT
2291 -- p_msg IN message to be print on the log
2292 -- file
2293 -- COMMENT : Add an entry to the log
2294 --=======================================================================--
2295 PROCEDURE Log
2296 ( p_priority IN NUMBER
2297 , p_msg IN VARCHAR2
2298 )
2299 IS
2300 BEGIN
2301 IF ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level))
2302 THEN
2303 IF g_log_mode = 'SQL'
2304 THEN
2305 -- SQL*Plus session: uncomment the next line during unit test
2306 -- DBMS_OUTPUT.put_line(p_msg);
2307 NULL;
2308 ELSE
2309 -- Concurrent request
2310 FND_FILE.put_line
2311 ( FND_FILE.log
2312 , p_msg
2313 );
2314 END IF;
2315 END IF;
2316 EXCEPTION
2317 WHEN OTHERS THEN
2318 NULL;
2319 END Log;
2320
2321 --
2322 -- bug 12844667
2323 --
2324 --========================================================================
2325 -- FUNCTION : Is_Process_Org PUBLIC
2326 -- PARAMETERS: p_org_id Organization Id
2327 --
2328 -- VERSION : current version 1.0
2329 -- initial version 1.0
2330 -- COMMENT : Function that checks if the org thats passed is a process org
2331 --=======================================================================--
2332 FUNCTION Is_Process_Org
2333 ( p_org_id IN NUMBER
2334 )
2335 RETURN BOOLEAN IS
2336 CURSOR get_process_enabled_flag IS
2337 SELECT NVL(process_enabled_flag, 'N')
2338 FROM mtl_parameters
2339 WHERE organization_id = p_org_id;
2340
2341 l_process_enabled_flag VARCHAR2(1) := 'N';
2342 BEGIN
2343 OPEN get_process_enabled_flag;
2344 FETCH get_process_enabled_flag INTO l_process_enabled_flag;
2345 CLOSE get_process_enabled_flag;
2346
2347 IF (l_process_enabled_flag = 'Y') THEN
2348 RETURN TRUE;
2349 ELSE
2350 RETURN FALSE;
2351 END IF;
2352 END Is_Process_Org;
2353
2354 --
2355 -- bug 12844667
2356 --
2357 --========================================================================
2358 -- FUNCTION : Get_Process_Item_Cost PUBLIC
2359 -- PARAMETERS: p_org_id Organization Id
2360 -- p_item_id Inventory Item Id
2361 -- p_transaction_date Transaction Date
2362 --
2363 -- VERSION : current version 1.0
2364 -- initial version 1.0
2365 -- COMMENT : Function that computes the cost of a Process Item
2366 --=======================================================================--
2367 FUNCTION Get_Process_Item_Cost
2368 ( p_org_id IN NUMBER,
2369 p_item_id IN NUMBER,
2370 p_transaction_date IN DATE
2371 )
2372 RETURN NUMBER IS
2373 l_result_code VARCHAR2(30);
2374 l_return_status VARCHAR2(30);
2375 l_msg_count NUMBER;
2376 l_msg_data VARCHAR2(2000);
2377 l_cost_mthd VARCHAR2(15);
2378 l_cmpntcls NUMBER;
2379 l_analysis_code VARCHAR2(15);
2380 l_no_of_rows NUMBER;
2381 l_item_cost NUMBER := 0;
2382 BEGIN
2383 l_result_code := GMF_CMCOMMON.Get_Process_Item_Cost
2384 ( p_api_version => 1
2385 , p_init_msg_list => 'F'
2386 , x_return_status => l_return_status
2387 , x_msg_count => l_msg_count
2388 , x_msg_data => l_msg_data
2389 , p_inventory_item_id => p_item_id
2390 , p_organization_id => p_org_id
2391 , p_transaction_date => p_transaction_date /* Cost as on date */
2392 , p_detail_flag => 1 /* 1 = total cost, 2 = details; 3 = cost for a specific component class/analysis code, etc. */
2393 , p_cost_method => l_cost_mthd /* OPM Cost Method */
2394 , p_cost_component_class_id => l_cmpntcls
2395 , p_cost_analysis_code => l_analysis_code
2396 , x_total_cost => l_item_cost /* total cost */
2397 , x_no_of_rows => l_no_of_rows /* number of detail rows retrieved */
2398 );
2399
2400 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2401 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2402 THEN
2403 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2404 , G_MODULE_NAME || 'Get_Process_Item_Cost:'
2405 ,'Error in Get_Process_Item_Cost. Setting item_cost to zero.'
2406 );
2407 END IF;
2408
2409 l_item_cost := 0;
2410 END IF;
2411
2412 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2413 THEN
2414 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2415 , G_MODULE_NAME || 'Get_Process_Item_Cost:'
2416 ,'item_cost:' || l_item_cost
2417 );
2418 END IF;
2419
2420 RETURN l_item_cost ;
2421 EXCEPTION
2422 WHEN OTHERS THEN
2423 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2424 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
2425 , G_MODULE_NAME ||'Get_Process_Item_Cost'
2426 ||'.Others exception'
2427 , 'Exception'
2428 );
2429 END IF;
2430 l_item_cost := 0;
2431 RETURN l_item_cost ;
2432 END Get_Process_Item_Cost;
2433
2434
2435 END INV_MGD_MVT_UTILS_PKG;