[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_SETUP_MDTR
Source
1 PACKAGE BODY INV_MGD_MVT_SETUP_MDTR AS
2 /* $Header: INVUSGSB.pls 120.1.12000000.2 2007/04/17 06:37:41 nesoni ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| MGDUSGSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of INV_MGD_MVT_SETUP_MDTR |
13 --| |
14 --| PROCEDURE LIST |
15 --| Get_Setup_Context |
16 --| Get_Invoice_Context |
17 --| Process_Setup_Context |
18 --| Get_Movement_Stat_Usages |
19 --| Get_Reference_Context |
20 --| |
21 --| HISTORY |
22 --| 12/04/2000 pseshadr Created |
23 --| 06/16/00 ksaini Added Get_Movement_Stat_Usages Procedure |
24 --| 07/06/00 ksaini Added 2 columns for validation rules to |
25 --| Get_Movement_Stat_Usages Procedure |
26 --| 04/01/02 pseshadr Added Get_Reference_Context procedure |
27 --| 16/04/2007 Neelam Soni Bug 5920143. Added support for Include |
28 --| Establishments. |
29 --+=======================================================================
30
31 --===================
32 -- CONSTANTS
33 --===================
34 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_MVT_SETUP_MDTR';
35 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_SETUP_MDTR.';
36
37 --========================================================================
38 -- PROCEDURE : Get_Reference_Context PRIVATE
39 -- PARAMETERS:
40 -- x_return_status return status
41 -- p_legal_entity_id Legal Entity ID
42 -- p_start_date Transaction start date
43 -- p_end_date Transaction end date
44 -- p_transaction type Transaction type (SO,PO etc)
45 -- COMMENT :
46 -- This processes all the transaction for the specified legal
47 -- entity that have a transaction date within the specified
48 -- date range.
49 --========================================================================
50
51 PROCEDURE Get_Reference_Context
52 ( p_legal_entity_id IN NUMBER
53 , p_start_date IN DATE
54 , p_end_date IN DATE
55 , p_transaction_type IN VARCHAR2
56 , p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
57 , x_return_status OUT NOCOPY VARCHAR2
58 , ref_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.setupCurTyp
59 )
60 IS
61 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
62 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
63 l_return_status VARCHAR2(1);
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(100);
66 l_transaction_date DATE;
67 l_reference_date DATE;
68 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Reference_Context';
69
70 BEGIN
71
72 INV_MGD_MVT_UTILS_PKG.Log_Initialize;
73
74 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
75 THEN
76 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
77 , G_MODULE_NAME || l_procedure_name || '.begin'
78 ,'enter procedure'
79 );
80 END IF;
81
82 x_return_status := 'Y';
83 l_transaction_date := p_movement_transaction.transaction_date;
84 l_reference_date := p_movement_transaction.reference_date;
85
86 IF ref_crsr%ISOPEN THEN
87 CLOSE ref_crsr;
88 END IF;
89
90 --Bug: 5920143. New column include_establishments has beed added in
91 -- select clause
92 OPEN ref_crsr FOR
93 SELECT
94 mstat.zone_code
95 , UPPER(mstat.usage_type)
96 , UPPER(mstat.stat_type )
97 , mstat.start_period_name
98 , mstat.end_period_name
99 , mstat.period_set_name
100 , mstat.period_type
101 , mstat.weight_uom_code
102 , mstat.conversion_type
103 , mstat.attribute_rule_set_code
104 , mstat.alt_uom_rule_set_code
105 , glp.start_date
106 , DECODE(mstat.end_period_name,NULL,NULL,glp1.end_date)
107 , mstat.category_set_id
108 , gllv.period_set_name
109 , gllv.currency_code
110 , gllv.currency_code
111 , mstat.conversion_option
112 , mstat.triangulation_mode
113 , mstat.reference_period_rule
114 , mstat.pending_invoice_days
115 , mstat.prior_invoice_days
116 , mstat.returns_processing
117 , mstat.kit_method
118 , nvl(mstat.include_establishments,'N')
119 FROM
120 GL_PERIODS glp
121 , GL_PERIODS glp1
122 , gl_ledger_le_v gllv
123 , MTL_STAT_TYPE_USAGES mstat
124 WHERE glp.period_set_name = mstat.period_set_name
125 AND glp1.period_set_name = mstat.period_set_name
126 AND glp.period_name = mstat.start_period_name
127 AND glp1.period_name = NVL(mstat.end_period_name,
128 mstat.start_period_name)
129 AND gllv.legal_entity_id = mstat.legal_entity_id
130 AND ledger_category_code = 'PRIMARY'
131 AND mstat.legal_entity_id = p_legal_entity_id
132 AND mstat.zone_code = p_movement_transaction.zone_code
133 AND mstat.usage_type = p_movement_transaction.usage_type
134 AND mstat.stat_type = p_movement_transaction.stat_type
135 AND trunc(l_transaction_date) BETWEEN trunc(glp.start_date) AND
136 TRUNC(DECODE(mstat.end_period_name,NULL,
137 (l_transaction_date+1),glp1.end_date));
138
139
140 -- RETURN setup_crsr;
141
142 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
143 THEN
144 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
145 , G_MODULE_NAME || l_procedure_name || '.end'
146 ,'exit procedure'
147 );
148 END IF;
149
150 EXCEPTION
151
152 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
153 RAISE;
154
155 WHEN NO_DATA_FOUND THEN
156 x_return_status := 'N';
157
158 FND_MESSAGE.Set_Name('INV', 'INV_MGD_MVT_GET_TRANS_CP');
159 FND_MSG_PUB.Add;
160
161 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
162 THEN
163 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
164 , 'Get_Reference_Context'
165 );
166 END IF;
167 RAISE ;
168
169
170 WHEN OTHERS THEN
171 x_return_status := 'N';
172 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
173 THEN
174 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
175 , 'Get_Reference_Context'
176 );
177 END IF;
178 RAISE;
179
180
181 END Get_Reference_Context;
182
183 --========================================================================
184 -- PROCEDURE : Get_Setup_Context
185 -- PARAMETERS:
186 -- x_return_status return status
187 -- p_legal_entity_id Legal Entity ID
188 -- p_movement_transaction Movement Transaction record Type
189 -- setup_crsr Cursr
190 -- COMMENT :
191 -- This processes all the transaction for the specified legal
192 -- entity that is set up in the parameters table.
193 --========================================================================
194
195 PROCEDURE Get_Setup_Context
196 ( p_legal_entity_id IN NUMBER
197 , p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
198 , x_return_status OUT NOCOPY VARCHAR2
199 , setup_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.setupCurTyp
200 )
201 IS
202 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
203 l_return_status VARCHAR2(1);
204 l_msg_count NUMBER;
205 l_msg_data VARCHAR2(100);
206 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Setup_Context';
207
208 BEGIN
209 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
210 THEN
211 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
212 , G_MODULE_NAME || l_procedure_name || '.begin'
213 ,'enter procedure'
214 );
215 END IF;
216
217 x_return_status := 'Y';
218
219 IF setup_crsr%ISOPEN THEN
220 CLOSE setup_crsr;
221 END IF;
222
223 OPEN setup_crsr FOR
224 SELECT
225 mstat.zone_code
226 , UPPER(mstat.usage_type)
227 , UPPER(mstat.stat_type )
228 , mstat.reference_period_rule
229 , mstat.pending_invoice_days
230 , mstat.prior_invoice_days
231 , mstat.triangulation_mode
232 FROM
233 MTL_STAT_TYPE_USAGES mstat
234 WHERE mstat.legal_entity_id = p_legal_entity_id;
235
236 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
237 THEN
238 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
239 , G_MODULE_NAME || l_procedure_name || '.end'
240 ,'exit procedure'
241 );
242 END IF;
243 EXCEPTION
244
245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
246 RAISE;
247
248 WHEN NO_DATA_FOUND THEN
249 x_return_status := 'N';
250
251 FND_MESSAGE.Set_Name('INV', 'INV_MGD_MVT_GET_TRANS_CP');
252 FND_MSG_PUB.Add;
253
254 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
255 THEN
256 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
257 , 'Get_Setup_Context'
258 );
259 END IF;
260 RAISE ;
261
262
263 WHEN OTHERS THEN
264 x_return_status := 'N';
265 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
266 THEN
267 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
268 , 'Get_Setup_Context'
269 );
270 END IF;
271 RAISE;
272
273 END Get_Setup_Context;
274
275
276
277 --========================================================================
278 -- PROCEDURE : Get_Invoice_Context PRIVATE
279 -- PARAMETERS:
280 -- x_return_status return status
281 -- p_legal_entity_id Legal Entity ID
282 -- p_start_date Transaction start date
283 -- p_end_date Transaction end date
284 -- p_transaction type Transaction type (SO,PO etc)
285 -- COMMENT :
286 --========================================================================
287
288 PROCEDURE Get_Invoice_Context
289 ( p_legal_entity_id IN NUMBER
290 , p_start_date IN DATE
291 , p_end_date IN DATE
292 , p_transaction_type IN VARCHAR2
293 , p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
294 , x_return_status OUT NOCOPY VARCHAR2
295 , setup_crsr IN OUT NOCOPY INV_MGD_MVT_DATA_STR.setupCurTyp
296 )
297 IS
298 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
299 l_stat_typ_transaction INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type;
300 l_return_status VARCHAR2(1);
301 l_msg_count NUMBER;
302 l_msg_data VARCHAR2(100);
303 l_transaction_date DATE;
304 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Invoice_Context';
305
306 BEGIN
307 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
308 THEN
309 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
310 , G_MODULE_NAME || l_procedure_name || '.begin'
311 ,'enter procedure'
312 );
313 END IF;
314
315 x_return_status := 'Y';
316 l_transaction_date := p_movement_transaction.transaction_date;
317
318 IF setup_crsr%ISOPEN THEN
319 CLOSE setup_crsr;
320 END IF;
321
322 OPEN setup_crsr FOR
323 SELECT
324 mstat.start_period_name
325 , mstat.end_period_name
326 , mstat.period_set_name
327 , mstat.period_type
328 , mstat.weight_uom_code
329 , mstat.conversion_type
330 , mstat.attribute_rule_set_code
331 , mstat.alt_uom_rule_set_code
332 , glp.start_date
333 , DECODE(mstat.end_period_name,NULL,NULL,glp1.end_date)
334 , mstat.category_set_id
335 , gllv.currency_code
336 , gllv.currency_code
337 , mstat.conversion_option
338 , mstat.triangulation_mode
339 , mstat.reference_period_rule
340 , mstat.pending_invoice_days
341 , mstat.prior_invoice_days
342 , mstat.returns_processing
343 FROM
344 GL_PERIODS glp
345 , GL_PERIODS glp1
346 , gl_ledger_le_v gllv
347 , MTL_STAT_TYPE_USAGES mstat
348 WHERE glp.period_set_name = glp1.period_set_name
349 AND glp.period_set_name = mstat.period_set_name
350 AND glp1.period_set_name = mstat.period_set_name
351 AND glp.period_type = mstat.period_type
352 AND glp1.period_type = mstat.period_type
353 AND glp.period_name = mstat.start_period_name
354 AND glp1.period_name = NVL(mstat.end_period_name,
355 mstat.start_period_name)
356 --AND glb.period_set_name = glp.period_set_name fix bug2203762,3723698
357 AND gllv.legal_entity_id = mstat.legal_entity_id
358 AND gllv.ledger_category_code = 'PRIMARY'
359 AND mstat.legal_entity_id = p_legal_entity_id
360 AND mstat.zone_code = p_movement_transaction.zone_code
361 AND mstat.usage_type = p_movement_transaction.usage_type
362 AND mstat.stat_type = p_movement_transaction.stat_type;
363
364 -- RETURN setup_crsr;
365
366 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
367 THEN
368 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
369 , G_MODULE_NAME || l_procedure_name || '.end'
370 ,'exit procedure'
371 );
372 END IF;
373
374 /*
375 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
377 ELSE
378 -- report success
379 null;
380 END IF;
381 */
382
383 EXCEPTION
384
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386 RAISE;
387
388 WHEN NO_DATA_FOUND THEN
389 x_return_status := 'N';
390
391 FND_MESSAGE.Set_Name('INV', 'INV_MGD_MVT_GET_TRANS_CP');
392 FND_MSG_PUB.Add;
393
394 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
395 THEN
396 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
397 , 'Get_Invoice_Context'
398 );
399 END IF;
400 RAISE ;
401
402
403 WHEN OTHERS THEN
404 x_return_status := 'N';
405 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
406 THEN
407 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
408 , 'Get_Invoice_Context'
409 );
410 END IF;
411 RAISE;
412
413 END Get_Invoice_Context;
414
415 --========================================================================
416 -- FUNCTION : Process_Setup_Context PRIVATE
417 -- PARAMETERS: p_movement_transaction Movement transaction record
418 -- COMMENT : This function validates and checks to see if the transaction
419 -- is to be inserted into the mvt stats table
420 --========================================================================
421
422 FUNCTION Process_Setup_Context
423 ( p_movement_transaction IN INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type
424 )RETURN VARCHAR2
425 IS
426 l_movement_transaction INV_MGD_MVT_DATA_STR.Movement_Transaction_Rec_Type;
427 l_insert_flag VARCHAR2(1);
428 l_ship_from_loc VARCHAR2(10);
429 l_ship_to_loc VARCHAR2(10);
430 l_procedure_name CONSTANT VARCHAR2(30) := 'Process_Setup_Context';
431
432 BEGIN
433 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
434 THEN
435 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
436 , G_MODULE_NAME || l_procedure_name || '.begin'
437 ,'enter procedure'
438 );
439 END IF;
440
441 l_movement_transaction := p_movement_transaction;
442
443 IF (l_movement_transaction.dispatch_territory_code =
444 l_movement_transaction.destination_territory_code)
445 OR
446 (l_movement_transaction.dispatch_territory_code IS NULL)
447 OR
448 (l_movement_transaction.destination_territory_code IS NULL)
449
450 THEN
451 l_insert_flag := 'N';
452 l_ship_from_loc := null;
453 l_ship_to_loc := null;
454
455 ELSE
456
457 -- Based on the dispatch and destination territory codes determine
458 -- the zone that the territory codes are part of.
459
460 l_ship_from_loc :=
461 INV_MGD_MVT_UTILS_PKG.Get_Zone_Code
462 ( p_territory_code => l_movement_transaction.dispatch_territory_code
463 , p_zone_code => l_movement_transaction.zone_code
464 , p_trans_date => l_movement_Transaction.transaction_date
465 );
466
467 l_ship_to_loc :=
468 INV_MGD_MVT_UTILS_PKG.Get_Zone_Code
469 ( p_territory_code => l_movement_transaction.destination_territory_code
470 , p_zone_code => l_movement_transaction.zone_code
471 , p_trans_date => l_movement_Transaction.transaction_date
472 );
473
474 END IF;
475
476 -- If the dispatch and destination territory codes are within the same
477 -- economic zones then the transaction is of usage_type INTERNAL.
478 -- Check if there is an entry in the MTL_STAT_TYPE_USAGES table for
479 -- usage_type of INTERNAL, if there is then we go ahead and process
480 -- the transaction, otherwise get the next record from the c_shp loop.
481
482 IF l_movement_transaction.usage_type = 'INTERNAL'
483 THEN
484
485 IF (l_ship_from_loc IS NOT NULL)
486 AND (l_ship_to_loc IS NOT NULL)
487 AND (l_ship_from_loc = l_ship_to_loc)
488 THEN
489 l_insert_flag := 'Y';
490 ELSE
491 l_insert_flag := 'N';
492 END IF;
493
494 -- If the dispatch and destination territory codes are in different
495 -- economic zones then the transaction is of usage_type EXTERNAL.
496 -- Check if there is an entry in the MTL_STAT_TYPE_USAGES table for
497 -- usage_type of EXTERNAL, if there is then we go ahead and process
498 -- the transaction, otherwise get the next record from the c_shp loop.
499
500 ELSIF l_movement_transaction.usage_type = 'EXTERNAL'
501 THEN
502 IF (l_ship_from_loc IS NULL)
503 AND (l_ship_to_loc IS NULL)
504 THEN
505 l_insert_flag := 'N';
506 ELSIF (l_ship_from_loc IS NULL)
507 OR (l_ship_to_loc IS NULL)
508 AND (NVL(l_ship_from_loc,'NONE') <> NVL(l_ship_to_loc,'NONE'))
509 THEN
510 l_insert_flag := 'Y';
511 ELSE
512 l_insert_flag := 'N';
513 END IF;
514 ELSE
515 l_insert_flag := 'N';
516 END IF;
517
518 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
519 THEN
520 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
521 , G_MODULE_NAME || l_procedure_name || '.end'
522 ,'exit procedure'
523 );
524 END IF;
525
526 RETURN l_insert_flag;
527
528 END Process_Setup_Context;
529
530 --========================================================================
531 -- PROCEDURE : Get_Movement_Stat_Usages PRIVATE
532 -- PARAMETERS:
533 -- x_return_status OUT return status
534 -- x_msg_count OUT number of messages in the list
535 -- x_msg_data OUT message text
536 -- p_legal_entity_id IN legal_entity
537 -- p_economic_zone_code IN economic zone
538 -- p_usage_type IN usage type
539 -- p_stat_type IN stat_type
540 -- x_movement_stat_usages_rec OUT Stat type Usages record
541 -- VERSION : current version 1.0
542 -- initial version 1.0
543
544 --=======================================================================--
545 PROCEDURE Get_Movement_Stat_Usages
546 ( x_return_status OUT NOCOPY VARCHAR2
547 , x_msg_count OUT NOCOPY NUMBER
548 , x_msg_data OUT NOCOPY VARCHAR2
549 , p_legal_entity_id IN NUMBER
550 , p_economic_zone_code IN VARCHAR2
551 , p_usage_type IN VARCHAR2
552 , p_stat_type IN VARCHAR2
553 , x_movement_stat_usages_rec OUT NOCOPY
554 INV_MGD_MVT_DATA_STR.Movement_Stat_Usages_Rec_Type
555 )
556 IS
557 l_api_version_number CONSTANT NUMBER := 1.0;
558 l_procedure_name CONSTANT VARCHAR2(30):= 'Get_Movement_Stat_Usages';
559 BEGIN
560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
561 THEN
562 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
563 , G_MODULE_NAME || l_procedure_name || '.begin'
564 ,'enter procedure'
565 );
566 END IF;
567
568 -- Initialize message stack if required
569 --IF FND_API.to_Boolean(p_init_msg_list)
570 --THEN
571 -- FND_MSG_PUB.initialize;
572 --END IF;
573
574 x_movement_stat_usages_rec.legal_entity_id := p_legal_entity_id;
575 x_movement_stat_usages_rec.zone_code := p_economic_zone_code;
576 x_movement_stat_usages_rec.usage_type := p_usage_type;
577 x_movement_stat_usages_rec.stat_type := p_stat_type;
578
579 SELECT conversion_option
580 , conversion_type
581 , category_set_id
582 , start_period_name
583 , end_period_name
584 , weight_uom_code
585 , period_set_name
586 , attribute_rule_set_code
587 , alt_uom_rule_set_code
588 , returns_processing
589 INTO x_movement_stat_usages_rec.conversion_option
590 , x_movement_stat_usages_rec.conversion_type
591 , x_movement_stat_usages_rec.category_set_id
592 , x_movement_stat_usages_rec.start_period_name
593 , x_movement_stat_usages_rec.end_period_name
594 , x_movement_stat_usages_rec.weight_uom_code
595 , x_movement_stat_usages_rec.period_set_name
596 , x_movement_stat_usages_rec.attribute_rule_set_code
597 , x_movement_stat_usages_rec.alt_uom_rule_set_code
598 , x_movement_stat_usages_rec.returns_processing
599 FROM mtl_stat_type_usages
600 WHERE legal_entity_id = p_legal_entity_id
601 AND zone_code = p_economic_zone_code
602 AND usage_type = p_usage_type
603 AND stat_type = p_stat_type;
604
605 SELECT ledger_id
606 , currency_code
607 INTO x_movement_stat_usages_rec.gl_set_of_books_id
608 , x_movement_stat_usages_rec.gl_currency_code
609 FROM gl_ledger_le_v
610 WHERE legal_entity_id = p_legal_entity_id
611 AND ledger_category_code = 'PRIMARY';
612
613 -- report success
614 x_return_status := FND_API.G_RET_STS_SUCCESS;
615 FND_MSG_PUB.Count_And_Get
616 ( p_count => x_msg_count
617 , p_data => x_msg_data
618 );
619
620 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
621 THEN
622 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
623 , G_MODULE_NAME || l_procedure_name || '.end'
624 ,'exit procedure'
625 );
626 END IF;
627
628 EXCEPTION
629
630 WHEN OTHERS THEN
631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
632 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
633 THEN
634 FND_MSG_PUB.Add_Exc_Msg
635 ( G_PKG_NAME
636 , 'Get_Movement_Stat_Usages'
637 );
638 END IF;
639 -- Get message count and data
640 FND_MSG_PUB.Count_And_Get
641 ( p_count => x_msg_count
642 , p_data => x_msg_data
643 );
644
645 END Get_Movement_Stat_Usages;
646
647 END INV_MGD_MVT_SETUP_MDTR;
648