[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_POSITIONS_PROC
Source
1 PACKAGE BODY INV_MGD_POSITIONS_PROC AS
2 -- $Header: INVSPOSB.pls 120.2 2005/09/01 22:36:51 nesoni ship $
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVSPOSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Inventory Position View and Export Processor |
13 --| |
14 --| HISTORY |
15 --| 09/11/2000 Paolo Juvara Created |
16 --| 11/21/2002 Vivian Ma Performance: modify code to print to |
17 --| log only if debug profile option is |
18 --| enabled |
19 --| 19/AUG/2005 Neelam Soni Modified for bug 4357322 |
20 --+=======================================================================+
21
22 --===================
23 -- CONSTANTS
24 --===================
25 G_PKG_NAME CONSTANT VARCHAR2(30):= 'INV_MGD_POSITIONS_PROC';
26
27 --===================
28 -- GLOBAL VARIABLES
29 --===================
30 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
31
32 --===================
33 -- TYPES
34 --===================
35 TYPE g_context_rec_type IS RECORD
36 ( data_set_name VARCHAR2(80)
37 , hierarchy_id NUMBER
38 , hierarchy_name VARCHAR2(30)
39 , hierarchy_version_id NUMBER
40 , parent_organization_id NUMBER
41 , parent_organization_code VARCHAR2(3)
42 );
43
44 TYPE g_organization_tbl_type IS TABLE OF NUMBER
45 INDEX BY BINARY_INTEGER;
46
47 --===================
48 -- PROCEDURES AND FUNCTIONS
49 --===================
50
51 --========================================================================
52 -- FUNCTION : Data_Set_Exists PRIVATE
53 -- PARAMETERS: p_data_set_name data set name
54 -- COMMENT : TRUE if the data set exists
55 --========================================================================
56 FUNCTION Data_Set_Exists(p_data_set_name IN VARCHAR2) RETURN BOOLEAN
57 IS
58 l_count NUMBER;
59 BEGIN
60
61 IF G_DEBUG = 'Y' THEN
62 INV_MGD_POS_UTIL.Log
63 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
64 , p_msg => '> '||G_PKG_NAME||'.'||'Data_Set_Exists'
65 );
66 END IF;
67
68 SELECT COUNT(*)
69 INTO l_count
70 FROM mtl_mgd_inventory_positions
71 WHERE data_set_name = p_data_set_name;
72
73 IF G_DEBUG = 'Y' THEN
74 INV_MGD_POS_UTIL.Log
75 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
76 , p_msg => '< '||G_PKG_NAME||'.'||'Data_Set_Exists'
77 );
78 END IF;
79
80 IF l_count = 0 THEN
81 RETURN FALSE;
82 ELSE
83 RETURN TRUE;
84 END IF;
85
86 END Data_Set_Exists;
87
88
89 --========================================================================
90 -- PROCEDURE : Get_Context PRIVATE
91 -- PARAMETERS: p_data_set_name data_set_name
92 -- p_hierarchy_id hierarchy id
93 -- p_parent_org_code parent organizaton code (hier. level)
94 -- x_context_rec context information
95 -- COMMENT : retrieves context information
96 --========================================================================
97 PROCEDURE Get_Context
98 ( p_data_set_name IN VARCHAR2
99 , p_hierarchy_id IN NUMBER
100 , p_parent_org_code IN VARCHAR2
101 , x_context_rec OUT NOCOPY g_context_rec_type
102 )
103 IS
104 BEGIN
105
106 IF G_DEBUG = 'Y' THEN
107 INV_MGD_POS_UTIL.Log
108 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
109 , p_msg => '> '||G_PKG_NAME||'.'||'Get_Context'
110 );
111 END IF;
112
113 x_context_rec.data_set_name := p_data_set_name;
114 x_context_rec.hierarchy_id := p_hierarchy_id;
115 x_context_rec.parent_organization_code := p_parent_org_code;
116
117 -- get hierarchy_name
118 SELECT
119 name
120 INTO
121 x_context_rec.hierarchy_name
122 FROM per_organization_structures
123 WHERE organization_structure_id = p_hierarchy_id;
124
125 -- get hierarchy version
126 SELECT
127 org_structure_version_id
128 INTO
129 x_context_rec.hierarchy_version_id
130 FROM per_org_structure_versions
131 WHERE organization_structure_id = p_hierarchy_id
132 AND SYSDATE BETWEEN date_from AND NVL(date_to, SYSDATE);
133
134 -- get parent organization code
135 SELECT
136 organization_id
137 INTO
138 x_context_rec.parent_organization_id
139 FROM mtl_parameters
140 WHERE organization_code = p_parent_org_code;
141
142 IF G_DEBUG = 'Y' THEN
143 INV_MGD_POS_UTIL.Log
144 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
145 , p_msg => '< '||G_PKG_NAME||'.'||'Get_Context'
146 );
147 END IF;
148
149 END Get_Context;
150
151
152 --========================================================================
153 -- PROCEDURE : Reserve_Data_Set_Name PRIVATE
154 -- PARAMETERS: p_data_set_name data_set_name
155 -- x_return_status return status
156 -- COMMENT : this procedures checks that the data set name is unique and
157 -- reserves it to prevent parallel requests from interfering
158 -- with each other; it returs FND_API.G_RET_STS_SUCCESS if the
159 -- reservation is successful; FND_API.G_RET_STS_ERROR otherwise;
160 -- the reservation is achieved by:
161 -- - locking the table
162 -- - checking for the existance of rows with the same data set
163 -- name
164 -- - if a row exists, FND_API.G_RET_STS_ERROR is returned
165 -- - otherwise, a dummy row is created and committed (which
166 -- releases the lock
167 -- the dummy row created by the reservation needs to be removed
168 -- at the end of the process by calling Release_Data_Set_Name.
169 --========================================================================
170 PROCEDURE Reserve_Data_Set_Name
171 ( p_data_set_name IN VARCHAR2
172 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
173 )
174 IS
175 PRAGMA AUTONOMOUS_TRANSACTION;
176 BEGIN
177
178 IF G_DEBUG = 'Y' THEN
179 INV_MGD_POS_UTIL.Log
180 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
181 , p_msg => '> '||G_PKG_NAME||'.'||'Reserve_Data_Set_Name'
182 );
183 END IF;
184
185 LOCK TABLE mtl_mgd_inventory_positions IN SHARE ROW EXCLUSIVE MODE;
186
187 IF Data_Set_Exists(p_data_set_name) THEN
188
189 ROLLBACK;
190 x_return_status := FND_API.G_RET_STS_ERROR;
191
192 ELSE
193
194 MTL_MGD_INV_POSITIONS_PKG.Insert_Row
195 ( p_data_set_name => p_data_set_name
196 , p_bucket_name => 'LOCK'
197 , p_organization_code => 'LCK'
198 , p_inventory_item_code => 'LOCK'
199 , p_hierarchy_id => -1
200 , p_hierarchy_name => -1
201 , p_parent_organization_code => 'LCK'
202 , p_parent_organization_id => -1
203 , p_bucket_size_code => 'LOCK'
204 , p_bucket_start_date => SYSDATE
205 , p_bucket_end_date => SYSDATE
206 , p_inventory_item_id => -1
207 , p_organization_id => -1
208 , p_hierarchy_delta_qty => 0
209 , p_hierarchy_end_on_hand_qty => 0
210 , p_org_received_qty => 0
211 , p_org_issued_qty => 0
212 , p_org_delta_qty => 0
213 , p_org_end_on_hand_qty => 0
214 );
215 COMMIT;
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217
218 END IF;
219
220 IF G_DEBUG = 'Y' THEN
221 INV_MGD_POS_UTIL.Log
222 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
223 , p_msg => '< '||G_PKG_NAME||'.'||'Reserve_Data_Set_Name'
224 );
225 END IF;
226
227 END Reserve_Data_Set_Name;
228
229
230
231 --========================================================================
232 -- PROCEDURE : Release_Data_Set_Name PRIVATE
233 -- PARAMETERS: p_data_set_name data_set_name
234 -- COMMENT : releases the reservation made by the Reserve_Data_Set_Name
235 -- procedure
236 --========================================================================
237 PROCEDURE Release_Data_Set_Name
238 ( p_data_set_name IN VARCHAR2
239 )
240 IS
241 PRAGMA AUTONOMOUS_TRANSACTION;
242 BEGIN
243
244 IF G_DEBUG = 'Y' THEN
245 INV_MGD_POS_UTIL.Log
246 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
247 , p_msg => '> '||G_PKG_NAME||'.'||'Release_Data_Set_Name'
248 );
249 END IF;
250
251 DELETE FROM mtl_mgd_inventory_positions
252 WHERE data_set_name = p_data_set_name
253 AND organization_id = -1
254 AND bucket_name = 'LOCK'
255 AND inventory_item_id = -1;
256 COMMIT;
257
258 IF G_DEBUG = 'Y' THEN
259 INV_MGD_POS_UTIL.Log
260 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
261 , p_msg => '< '||G_PKG_NAME||'.'||'Release_Data_Set_Name'
262 );
263 END IF;
264
265 END Release_Data_Set_Name;
266
267 --========================================================================
268 -- FUNCTION : Get_Master_Org PRIVATE
269 -- PARAMETERS: p_hierarchy_level hierarchy level
270 -- COMMENT : Retrieve the master organization
271 --========================================================================
272 FUNCTION Get_Master_Org(p_hierarchy_level IN NUMBER) RETURN NUMBER
273 IS
274 l_master_org_id NUMBER;
275 BEGIN
276
277 IF G_DEBUG = 'Y' THEN
278 INV_MGD_POS_UTIL.Log
279 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
280 , p_msg => '> '||G_PKG_NAME||'.'||'Get_Master_Org'
281 );
282 END IF;
283
284 SELECT master_organization_id
285 INTO l_master_org_id
286 FROM mtl_parameters
287 WHERE organization_id = p_hierarchy_level;
288
289 IF G_DEBUG = 'Y' THEN
290 INV_MGD_POS_UTIL.Log
291 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
292 , p_msg => '< '||G_PKG_NAME||'.'||'Get_Master_Org'
293 );
294 END IF;
295
296 RETURN l_master_org_id;
297
298 END Get_Master_Org;
299
300
301 --========================================================================
302 -- PROCEDURE : Get_Child_Organizations PRIVATE
303 -- PARAMETERS: p_context_rec context
304 -- p_organization_id organization
305 -- x_child_org_tbl list of organizations under
306 -- p_organization_id
307 -- COMMENT : retrieves the list of organizations in under p_organization_id
308 -- in the hierarchy
309 --========================================================================
310 PROCEDURE Get_Child_Organizations
311 ( p_context_rec IN g_context_rec_type
312 , p_organization_id IN NUMBER
313 , x_child_organizations OUT NOCOPY g_organization_tbl_type
314 )
315 IS
316 CURSOR l_org_children_crsr
317 ( p_hierarchy_version_id NUMBER
318 , p_organization_id NUMBER
319 )
320 IS
321 SELECT organization_id_child
322 FROM per_org_structure_elements
323 WHERE org_structure_version_id = p_hierarchy_version_id
324 AND organization_id_parent = p_organization_id;
325 BEGIN
326
327 IF G_DEBUG = 'Y' THEN
328 INV_MGD_POS_UTIL.Log
329 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
330 , p_msg => '> '||G_PKG_NAME||'.'||'Get_Child_Organizations'
331 );
332 END IF;
333
334 OPEN l_org_children_crsr
335 ( p_hierarchy_version_id => p_context_rec.hierarchy_version_id
336 , p_organization_id => p_organization_id
337 );
338
339 LOOP
340 FETCH l_org_children_crsr
341 INTO x_child_organizations(x_child_organizations.COUNT + 1);
342 EXIT WHEN l_org_children_crsr%NOTFOUND;
343 END LOOP;
344
345 CLOSE l_org_children_crsr;
346
347 IF G_DEBUG = 'Y' THEN
348 INV_MGD_POS_UTIL.Log
349 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
350 , p_msg => '< '||G_PKG_NAME||'.'||'Get_Child_Organizations'
351 );
352 END IF;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 IF G_DEBUG = 'Y' THEN
357 INV_MGD_POS_UTIL.Log
358 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
359 , p_msg => 'Exception: '||SQLERRM
360 );
361 END IF;
362 IF l_org_children_crsr%ISOPEN THEN
363 CLOSE l_org_children_crsr;
364 END IF;
365 RAISE;
366 END Get_Child_Organizations;
367
368 --========================================================================
369 -- FUNCTION : All_Completed PRIVATE
370 -- PARAMETERS: p_organization_tbl list of organization
371 -- p_child_organizations list of organizations to check
372 -- COMMENT : TRUE if all the organizations in p_child_organizations are
373 -- marked as completed in p_organization_tbl; FALSE otherwise
374 --========================================================================
375 FUNCTION All_Completed
376 ( p_organization_tbl IN INV_MGD_POS_UTIL.organization_tbl_type
377 , p_child_organizations IN g_organization_tbl_type
378 ) RETURN BOOLEAN
379 IS
380 l_completed BOOLEAN;
381 BEGIN
382
383 IF G_DEBUG = 'Y' THEN
384 INV_MGD_POS_UTIL.Log
385 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
386 , p_msg => '> '||G_PKG_NAME||'.'||'All_Completed'
387 );
388 END IF;
389
390 l_completed := TRUE;
391
392 FOR l_Idx1 IN 1..p_child_organizations.COUNT LOOP
393
394 FOR l_Idx2 IN 1..p_organization_tbl.COUNT LOOP
395 IF p_organization_tbl(l_Idx2).id = p_child_organizations(l_Idx1) THEN
396 l_completed := p_organization_tbl(l_Idx2).complete_flag;
397 EXIT;
398 END IF;
399 END LOOP;
400
401 EXIT WHEN NOT l_completed;
402
403 END LOOP;
404
405 IF G_DEBUG = 'Y' THEN
406 INV_MGD_POS_UTIL.Log
407 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
408 , p_msg => '< '||G_PKG_NAME||'.'||'All_Completed'
409 );
410 END IF;
411
412 RETURN l_completed;
413
414 END All_Completed;
415
416 --========================================================================
417 -- PROCEDURE : Calc_Item_Begin_Qty PRIVATE
418 -- PARAMETERS: p_organization_id organization
419 -- p_item_id item
420 -- p_date date
421 -- x_quantity quantity on hand
422 -- COMMENT : calculates beginning quantity on hand for an item in one
423 -- organization as specified by the given date
427 , p_item_id IN NUMBER
424 --========================================================================
425 PROCEDURE Calc_Item_Begin_Qty
426 ( p_organization_id IN NUMBER
428 , p_date IN DATE
429 , x_quantity OUT NOCOPY /* file.sql.39 change */ NUMBER
430 )
431 IS
432 l_base_period_id NUMBER;
433 l_base_period_end_date DATE;
434 l_base_qty NUMBER;
435 l_begin_qty NUMBER;
436 l_current_qty NUMBER;
437 l_rollback_qty NUMBER;
438
439 BEGIN
440
441 IF G_DEBUG = 'Y' THEN
442 INV_MGD_POS_UTIL.Log
443 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
444 , p_msg => '> '||G_PKG_NAME||'.'||'Calc_Item_Begin_Qty'
445 );
446 END IF;
447
448 -- get offset quantity
449 /*2872802*/
450
451 SELECT NVL(SUM(primary_transaction_quantity),0)
452 INTO
453 l_current_qty
454 FROM mtl_onhand_quantities_detail
455 WHERE organization_id = p_organization_id
456 AND inventory_item_id = p_item_id;
457
458 -- yawang fix bug 2195443, filter OUT NOCOPY /* file.sql.39 change */ transaction action id 24 and 30
459 -- this 24 and 30 info is from Material Transaction form
460 SELECT
461 NVL(SUM(primary_quantity), 0)
462 INTO
463 l_rollback_qty
464 FROM mtl_material_transactions
465 WHERE organization_id = p_organization_id
466 AND inventory_item_id = p_item_id
467 AND transaction_date >= p_date
468 AND transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
469
470 l_begin_qty := l_current_qty - l_rollback_qty;
471
472 IF G_DEBUG = 'Y' THEN
473 INV_MGD_POS_UTIL.Log
474 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
475 , p_msg => 'l_begin_qty:'||TO_CHAR(l_begin_qty)
476 );
477 END IF;
478
479 x_quantity := l_begin_qty;
480
481 IF G_DEBUG = 'Y' THEN
482 INV_MGD_POS_UTIL.Log
483 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
484 , p_msg => '< '||G_PKG_NAME||'.'||'Calc_Item_Begin_Qty'
485 );
486 END IF;
487 END Calc_Item_Begin_Qty;
488
489
490 --========================================================================
491 -- PROCEDURE : Process_Bucket PRIVATE
492 -- PARAMETERS: p_context_rec context information
493 -- p_item_rec item
494 -- p_bucket_rec bucket
495 -- p_begin_qty begin quantity
496 -- x_end_qty end quantity
497 -- COMMENT : processes a bucket for an item by populating an entry in
498 -- MTL_MGD_INVENTORY_POSITIONS with data for the current
499 -- organization; passes the end quantity OUT NOCOPY /* file.sql.39 change */ as begin quantity
500 -- for the next bucket
501 --========================================================================
502 PROCEDURE Process_Bucket
503 ( p_context_rec IN g_context_rec_type
504 , p_item_rec IN INV_MGD_POS_UTIL.item_rec_type
505 , p_bucket_rec IN INV_MGD_POS_UTIL.bucket_rec_type
506 , p_begin_qty IN NUMBER
507 , x_end_qty OUT NOCOPY /* file.sql.39 change */ NUMBER
508 )
509 IS
510 l_org_received_qty NUMBER;
511 l_org_issued_qty NUMBER;
512 BEGIN
513
514 IF G_DEBUG = 'Y' THEN
515 INV_MGD_POS_UTIL.Log
516 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
517 , p_msg => '> '||G_PKG_NAME||'.'||'Process_Bucket'
518 );
519 END IF;
520
521
522 -- yawang fix bug 2195443, filter OUT NOCOPY /* file.sql.39 change */ transaction action id 24 and 30
523 -- this 24 and 30 info is from Material Transaction form
524 -- get received quantity
525 SELECT
526 NVL(SUM(primary_quantity), 0)
527 INTO
528 l_org_received_qty
529 FROM mtl_material_transactions
530 WHERE organization_id = p_item_rec.organization_id
531 AND inventory_item_id = p_item_rec.item_id
532 AND transaction_date >= p_bucket_rec.start_date
533 AND transaction_date < p_bucket_rec.end_date
534 AND transaction_quantity > 0
535 AND transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
536
537 -- get issued quantity
538 SELECT
539 NVL(SUM(-primary_quantity), 0)
540 INTO
541 l_org_issued_qty
542 FROM mtl_material_transactions
543 WHERE organization_id = p_item_rec.organization_id
544 AND inventory_item_id = p_item_rec.item_id
545 AND transaction_date >= p_bucket_rec.start_date
546 AND transaction_date < p_bucket_rec.end_date
547 AND transaction_quantity < 0
548 AND transaction_action_id NOT IN (24,30,50,51,52); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */
549
550 MTL_MGD_INV_POSITIONS_PKG.Insert_Row
551 ( p_data_set_name => p_context_rec.data_set_name
552 , p_bucket_name => p_bucket_rec.name
553 , p_organization_code => p_item_rec.organization_code
554 , p_inventory_item_code => p_item_rec.item_code
558 , p_parent_organization_id => p_context_rec.parent_organization_id
555 , p_hierarchy_id => p_context_rec.hierarchy_id
556 , p_hierarchy_name => p_context_rec.hierarchy_name
557 , p_parent_organization_code => p_context_rec.parent_organization_code
559 , p_bucket_size_code => p_bucket_rec.bucket_size
560 , p_bucket_start_date => p_bucket_rec.start_date
561 , p_bucket_end_date => p_bucket_rec.end_date
562 , p_inventory_item_id => p_item_rec.item_id
563 , p_organization_id => p_item_rec.organization_id
564 , p_hierarchy_delta_qty => 0
565 , p_hierarchy_end_on_hand_qty => 0
566 , p_org_received_qty => l_org_received_qty
567 , p_org_issued_qty => l_org_issued_qty
568 , p_org_delta_qty => l_org_received_qty - l_org_issued_qty
569 , p_org_end_on_hand_qty => NVL(p_begin_qty,0) +
570 l_org_received_qty - l_org_issued_qty
571 );
572
573 x_end_qty := NVL(p_begin_qty,0) + l_org_received_qty - l_org_issued_qty;
574
575 IF G_DEBUG = 'Y' THEN
576 INV_MGD_POS_UTIL.Log
577 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
578 , p_msg => '< '||G_PKG_NAME||'.'||'Process_Bucket'
579 );
580 END IF;
581 END Process_Bucket;
582
583
584 --========================================================================
585 -- PROCEDURE : Calc_Org_Level_Info PRIVATE
586 -- PARAMETERS: p_context_rec context information
587 -- p_item_rec item
588 -- p_bucket_tbl bucket list
589 -- COMMENT : calculates the data for a given item in a given organization
590 -- across the list of buckets
591 --========================================================================
592 PROCEDURE Calc_Org_Level_Info
593 ( p_context_rec IN g_context_rec_type
594 , p_item_rec IN INV_MGD_POS_UTIL.item_rec_type
595 , p_bucket_tbl IN INV_MGD_POS_UTIL.bucket_tbl_type
596 )
597 IS
598 l_begin_qty NUMBER;
599
600 -- Following variable added. Bug:4357322
601 l_end_qty NUMBER;
602 BEGIN
603
604 IF G_DEBUG = 'Y' THEN
605 INV_MGD_POS_UTIL.Log
606 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
607 , p_msg => '> '||G_PKG_NAME||'.'||'Calc_Org_Level_Info'
608 );
609 END IF;
610
611 Calc_Item_Begin_Qty
612 ( p_organization_id => p_item_rec.organization_id
613 , p_item_id => p_item_rec.item_id
614 , p_date => p_bucket_tbl(1).start_date
615 , x_quantity => l_begin_qty
616 );
617
618 IF G_DEBUG = 'Y' THEN
619 INV_MGD_POS_UTIL.Log
620 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
621 , p_msg => 'Looping on buckets'
622 );
623 END IF;
624 FOR l_Idx IN 1..p_bucket_tbl.COUNT
625 LOOP
626 IF G_DEBUG = 'Y' THEN
627 INV_MGD_POS_UTIL.Log
628 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
629 , p_msg => 'Bucket: '||p_bucket_tbl(l_Idx).name
630 );
631 END IF;
632
633 -- l_end_qty is passed in place of l_begin_qty. Bug:4357322
634 Process_bucket
635 ( p_context_rec => p_context_rec
636 , p_item_rec => p_item_rec
637 , p_bucket_rec => p_bucket_tbl(l_Idx)
638 , p_begin_qty => l_begin_qty
639 , x_end_qty => l_end_qty
640 );
641 l_begin_qty := l_end_qty;
642 END LOOP;
643
644 IF G_DEBUG = 'Y' THEN
645 INV_MGD_POS_UTIL.Log
646 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
647 , p_msg => '< '||G_PKG_NAME||'.'||'Calc_Org_Level_Info'
648 );
649 END IF;
650
651 END Calc_Org_Level_Info;
652
653 --========================================================================
654 -- PROCEDURE : Get_Hierarchy_Level_Data PRIVATE
655 -- PARAMETERS: p_context_rec context
656 -- p_child_organizations organization
657 -- p_item_rec item
658 -- p_bucket_rec bucket
659 -- x_hier_delta_qty delta qty at hierarchy level
660 -- x_hier_end_on_hand_qty end bucket on hand qty at hier. level
661 -- COMMENT : retrieves the hierarchy level data for a given organization
662 --========================================================================
663 PROCEDURE Get_Hierarchy_Level_Data
664 ( p_context_rec IN g_context_rec_type
665 , p_organization_id IN NUMBER
666 , p_child_organizations IN g_organization_tbl_type
667 , p_item_rec IN INV_MGD_POS_UTIL.item_rec_type
668 , p_bucket_rec IN INV_MGD_POS_UTIL.bucket_rec_type
669 , x_hier_delta_qty OUT NOCOPY NUMBER
670 , x_hier_end_on_hand_qty OUT NOCOPY NUMBER
671 )
672 IS
673 -- Following variables added. Bug:4357322
674 l_child_organization g_organization_tbl_type;
675 l_sub_child_organization g_organization_tbl_type;
676 l_sub_delta_qty NUMBER;
677 l_sub_on_hand_qty NUMBER;
678
679 l_delta_qty NUMBER;
680 l_on_hand_qty NUMBER;
681 l_old_delta_qty NUMBER;
682 l_old_on_hand_qty NUMBER;
683 CURSOR l_org_data_crsr
684 ( p_data_set_name VARCHAR2
688 )
685 , p_organization_id NUMBER
686 , p_bucket_name VARCHAR2
687 , p_inventory_item_id NUMBER
689 IS
690 SELECT
691 org_delta_qty
692 , org_end_on_hand_qty
693 FROM mtl_mgd_inventory_positions
694 WHERE data_set_name = p_data_set_name
695 AND organization_id = p_organization_id
696 AND bucket_name = p_bucket_name
697 AND inventory_item_id = p_inventory_item_id;
698 CURSOR l_hier_data_crsr
699 ( p_data_set_name VARCHAR2
700 , p_organization_id NUMBER
701 , p_bucket_name VARCHAR2
702 , p_inventory_item_id NUMBER
703 )
704 IS
705 SELECT
706 hierarchy_delta_qty
707 , hierarchy_end_on_hand_qty
708 FROM mtl_mgd_inventory_positions
709 WHERE data_set_name = p_data_set_name
710 AND organization_id = p_organization_id
711 AND bucket_name = p_bucket_name
712 AND inventory_item_id = p_inventory_item_id;
713 BEGIN
714
715 IF G_DEBUG = 'Y' THEN
716 INV_MGD_POS_UTIL.Log
717 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
718 , p_msg => '> '||G_PKG_NAME||'.'||'Get_Hierarchy_Level_Data'
719 );
720 END IF;
721
722 OPEN l_org_data_crsr
723 ( p_data_set_name => p_context_rec.data_set_name
724 , p_organization_id => p_organization_id
725 , p_bucket_name => p_bucket_rec.name
726 , p_inventory_item_id => p_item_rec.item_id
727 );
728 FETCH l_org_data_crsr INTO l_old_delta_qty, l_old_on_hand_qty;
729 IF l_org_data_crsr%NOTFOUND THEN
730 l_old_delta_qty := 0;
731 l_old_on_hand_qty := 0;
732 END IF;
733 CLOSE l_org_data_crsr;
734
735 IF G_DEBUG = 'Y' THEN
736 INV_MGD_POS_UTIL.Log
737 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
738 , p_msg => 'hierarchy origin on hand qty :'||l_old_on_hand_qty
739 );
740 END IF;
741
742 FOR l_Idx IN 1..p_child_organizations.COUNT
743 LOOP
744
745 OPEN l_hier_data_crsr
746 ( p_data_set_name => p_context_rec.data_set_name
747 , p_organization_id => p_child_organizations(l_Idx)
748 , p_bucket_name => p_bucket_rec.name
749 , p_inventory_item_id => p_item_rec.item_id
750 );
751
752 -- Following assignment added for bug:4357322
753 l_delta_qty := 0;
754 l_on_hand_qty := 0;
755
756 FETCH l_hier_data_crsr INTO l_delta_qty, l_on_hand_qty;
757 IF l_hier_data_crsr%NOTFOUND THEN
758 -- Following block modified for bug:4357322
759 Get_Child_Organizations(p_context_rec,p_child_organizations(l_Idx), l_child_organization);
760
761 IF (l_child_organization IS NOT NULL AND l_child_organization.COUNT > 0)THEN
762 IF G_DEBUG = 'Y' THEN
763 INV_MGD_POS_UTIL.Log
764 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
765 , p_msg => 'child org Id : '||p_child_organizations(l_Idx)
766 );
767 INV_MGD_POS_UTIL.Log
768 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
769 , p_msg => 'Number of children : '||l_child_organization.COUNT
770 );
771 END IF ;
772 FOR l_index IN 1..l_child_organization.COUNT
773 LOOP
774 IF G_DEBUG = 'Y' THEN
775 INV_MGD_POS_UTIL.Log
776 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
777 , p_msg => 'Sub child organization Id :'||l_child_organization(l_index)
778 );
779 END IF;
780 Get_Child_Organizations(p_context_rec,l_child_organization(l_index), l_sub_child_organization);
781 Get_Hierarchy_Level_Data(p_context_rec,l_child_organization(l_index), l_sub_child_organization
782 ,p_item_rec, p_bucket_rec, l_sub_delta_qty,l_sub_on_hand_qty);
783
784 l_delta_qty := l_delta_qty + l_sub_delta_qty;
785 l_on_hand_qty := l_on_hand_qty + l_sub_on_hand_qty;
786 IF G_DEBUG = 'Y' THEN
787 INV_MGD_POS_UTIL.Log
788 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
789 , p_msg => 'Delta Qty:'||l_delta_qty
790 );
791 INV_MGD_POS_UTIL.Log
792 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
793 , p_msg => 'Onhand Qty :'||l_on_hand_qty
794 );
795 INV_MGD_POS_UTIL.Log
796 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
797 , p_msg => 'Sub Delta Qty :'||l_sub_delta_qty
798 );
799 INV_MGD_POS_UTIL.Log
800 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
801 , p_msg => 'Sub Onhand Qty :'||l_sub_on_hand_qty
802 );
803 END IF;
804 END LOOP;
805 END IF;
806 -- End of bug 4357322 fix
807 END IF;
808 CLOSE l_hier_data_crsr;
809
810 IF G_DEBUG = 'Y' THEN
811 INV_MGD_POS_UTIL.Log
812 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
813 , p_msg => 'child org on hand qty :'||l_on_hand_qty
814 );
815 END IF;
816
817 l_old_delta_qty := l_old_delta_qty + l_delta_qty;
818 l_old_on_hand_qty := l_old_on_hand_qty + l_on_hand_qty;
819
820 END LOOP;
821
822 IF G_DEBUG = 'Y' THEN
823 INV_MGD_POS_UTIL.Log
824 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
828
825 , p_msg => 'hierarchy on hand qty :'||l_old_on_hand_qty
826 );
827 END IF;
829 x_hier_delta_qty := l_old_delta_qty;
830 x_hier_end_on_hand_qty := l_old_on_hand_qty;
831
832 IF G_DEBUG = 'Y' THEN
833 INV_MGD_POS_UTIL.Log
834 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
835 , p_msg => '< '||G_PKG_NAME||'.'||'Get_Hierarchy_Level_Data'
836 );
837 END IF;
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 IF G_DEBUG = 'Y' THEN
842 INV_MGD_POS_UTIL.Log
843 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
844 , p_msg => 'Exception: '||SQLERRM
845 );
846 END IF;
847 IF l_org_data_crsr%ISOPEN THEN
848 CLOSE l_org_data_crsr;
849 END IF;
850 IF l_hier_data_crsr%ISOPEN THEN
851 CLOSE l_hier_data_crsr;
852 END IF;
853 RAISE;
854 END Get_Hierarchy_Level_Data;
855
856 --========================================================================
857 -- PROCEDURE : Calc_Hier_Level_Info PRIVATE
858 -- PARAMETERS: p_context_rec context
859 -- p_organization_tbl organization list
860 -- p_orgnaization_idx index of the give organization in
861 -- p_organization_tbl
862 -- p_item_tbl item list
863 -- p_bucket_tbl bucket list
864 -- COMMENT : calculates the hierarchy level data for a given organization
865 --========================================================================
866 PROCEDURE Calc_Hier_Level_Info
867 ( p_context_rec IN g_context_rec_type
868 , p_organization_tbl IN INV_MGD_POS_UTIL.organization_tbl_type
869 , p_organization_idx IN NUMBER
870 , p_item_tbl IN INV_MGD_POS_UTIL.item_tbl_type
871 , p_bucket_tbl IN INV_MGD_POS_UTIL.bucket_tbl_type
872 , x_completed_flag OUT NOCOPY /* file.sql.39 change */ BOOLEAN
873 )
874 IS
875 l_child_org_tbl g_organization_tbl_type;
876 l_delta_qty NUMBER;
877 l_end_on_hand_qty NUMBER;
878 BEGIN
879
880 IF G_DEBUG = 'Y' THEN
881 INV_MGD_POS_UTIL.Log
882 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
883 , p_msg => '> '||G_PKG_NAME||'.'||'Calc_Hier_Level_Info'
884 );
885 END IF;
886
887 Get_Child_Organizations
888 ( p_context_rec => p_context_rec
889 , p_organization_id => p_organization_tbl(p_organization_idx).id
890 , x_child_organizations => l_child_org_tbl
891 );
892
893 IF All_Completed
894 ( p_organization_tbl => p_organization_tbl
895 , p_child_organizations => l_child_org_tbl
896 )
897 THEN
898
899 FOR l_itm_Idx IN 1..p_item_tbl.COUNT
900 LOOP
901
902 IF G_DEBUG = 'Y' THEN
903 INV_MGD_POS_UTIL.Log
904 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
905 , p_msg => 'itm_idx: '||l_itm_idx
906 );
907 END IF;
908
909 IF p_item_tbl(l_itm_idx).organization_id =
910 p_organization_tbl(p_organization_idx).id
911 THEN
912
913 IF G_DEBUG = 'Y' THEN
914 INV_MGD_POS_UTIL.Log
915 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
916 , p_msg => 'item: '||p_item_tbl(l_itm_idx).item_id
917 );
918 END IF;
919
920 --IF Clause added for bug 4357322
921 IF (p_item_tbl(l_itm_idx).item_id > 0) THEN
922 FOR l_Bkt_Idx IN 1..p_bucket_tbl.COUNT
923 LOOP
924
925 IF G_DEBUG = 'Y' THEN
926 INV_MGD_POS_UTIL.Log
927 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
928 , p_msg => 'bucket: '||p_bucket_tbl(l_bkt_idx).name
929 );
930 END IF;
931
932 Get_Hierarchy_Level_Data
933 ( p_context_rec => p_context_rec
934 , p_organization_id => p_organization_tbl(p_organization_idx).id
935 , p_child_organizations => l_child_org_tbl
936 , p_item_rec => p_item_tbl(l_itm_idx)
937 , p_bucket_rec => p_bucket_tbl(l_bkt_idx)
938 , x_hier_delta_qty => l_delta_qty
939 , x_hier_end_on_hand_qty => l_end_on_hand_qty
940 );
941
942 IF G_DEBUG = 'Y' THEN
943 INV_MGD_POS_UTIL.Log
944 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
945 , p_msg => 'delta qty: '||l_delta_qty
946 );
947 INV_MGD_POS_UTIL.Log
948 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
949 , p_msg => 'end on hand qty: '||l_end_on_hand_qty
950 );
951 END IF;
952
953 MTL_MGD_INV_POSITIONS_PKG.Update_Hierarchy_Data
954 ( p_data_set_name => p_context_rec.data_set_name
955 , p_bucket_name => p_bucket_tbl(l_bkt_idx).name
956 , p_organization_id => p_item_tbl(l_itm_idx).organization_id
957 , p_inventory_item_id => p_item_tbl(l_itm_idx).item_id
958 , p_hierarchy_delta_qty => l_delta_qty
962 END LOOP; -- p_bucket_tbl
959 , p_hierarchy_end_on_hand_qty => l_end_on_hand_qty
960 );
961
963 END IF;
964 END IF;
965
966 END LOOP; -- p_item_tbl
967
968 x_completed_flag := TRUE;
969
970 ELSE
971
972 x_completed_flag := FALSE;
973
974 END IF;
975
976 IF G_DEBUG = 'Y' THEN
977 INV_MGD_POS_UTIL.Log
978 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
979 , p_msg => '< '||G_PKG_NAME||'.'||'Calc_Hier_Level_Info'
980 );
981 END IF;
982
983 END Calc_Hier_Level_Info;
984
985 --========================================================================
986 -- PROCEDURE : Fetch_Data PRIVATE
987 -- PARAMETERS: p_context_rec context information
988 -- p_organization_tbl list of organizations
989 -- p_item_tbl list of items
990 -- p_bucket_tbl list of buckets
991 -- COMMENT : Retrieve the data from the transaction tables and build the
992 -- data set in the temporary table
993 --========================================================================
994 PROCEDURE Fetch_Data
995 ( p_context_rec IN g_context_rec_type
996 , x_organization_tbl IN OUT NOCOPY INV_MGD_POS_UTIL.organization_tbl_type
997 , p_item_tbl IN INV_MGD_POS_UTIL.item_tbl_type
998 , p_bucket_tbl IN INV_MGD_POS_UTIL.bucket_tbl_type
999 )
1000 IS
1001 l_all_org_completed BOOLEAN;
1002 BEGIN
1003
1004 IF G_DEBUG = 'Y' THEN
1005 INV_MGD_POS_UTIL.Log
1006 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
1007 , p_msg => '> '||G_PKG_NAME||'.'||'Fetch_Data'
1008 );
1009
1010 INV_MGD_POS_UTIL.Log
1011 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
1012 , p_msg => 'Looping on items'
1013 );
1014 END IF;
1015
1016 FOR l_Idx IN 1..p_item_tbl.COUNT
1017 LOOP
1018
1019 IF G_DEBUG = 'Y' THEN
1020 INV_MGD_POS_UTIL.Log
1021 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
1022 , p_msg => 'item '||p_item_tbl(l_Idx).item_id ||
1023 ' org '||p_item_tbl(l_Idx).organization_id
1024 );
1025 END IF;
1026
1027 Calc_Org_Level_Info
1028 ( p_context_rec => p_context_rec
1029 , p_item_rec => p_item_tbl(l_Idx)
1030 , p_bucket_tbl => p_bucket_tbl
1031 );
1032
1033 END LOOP;
1034
1035 IF G_DEBUG = 'Y' THEN
1036 INV_MGD_POS_UTIL.Log
1037 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
1038 , p_msg => 'Looping while orgs are not completed'
1039 );
1040 END IF;
1041 LOOP
1042
1043 l_all_org_completed := TRUE;
1044
1045 IF G_DEBUG = 'Y' THEN
1046 INV_MGD_POS_UTIL.Log
1047 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
1048 , p_msg => 'Looping on organizations'
1049 );
1050 END IF;
1051 FOR l_Idx IN 1..x_organization_tbl.COUNT
1052 LOOP
1053 IF G_DEBUG = 'Y' THEN
1054 INV_MGD_POS_UTIL.Log
1055 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
1056 , p_msg => 'org: '||x_organization_tbl(l_Idx).id
1057 );
1058 END IF;
1059 IF NOT x_organization_tbl(l_Idx).complete_flag THEN
1060
1061 l_all_org_completed := FALSE;
1062
1063 Calc_Hier_Level_Info
1064 ( p_context_rec => p_context_rec
1065 , p_organization_tbl => x_organization_tbl
1066 , p_organization_idx => l_Idx
1067 , p_item_tbl => p_item_tbl
1068 , p_bucket_tbl => p_bucket_tbl
1069 , x_completed_flag => x_organization_tbl(l_Idx).complete_flag
1070 );
1071
1072 END IF;
1073
1074 END LOOP;
1075
1076 EXIT WHEN l_all_org_completed;
1077
1078 END LOOP;
1079
1080 IF G_DEBUG = 'Y' THEN
1081 INV_MGD_POS_UTIL.Log
1082 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
1083 , p_msg => '< '||G_PKG_NAME||'.'||'Fetch_Data'
1084 );
1085 END IF;
1086
1087 END Fetch_Data;
1088
1089
1090 --========================================================================
1091 -- PROCEDURE : Build PUBLIC
1092 -- PARAMETERS: p_init_msg_list standard API parameter
1093 -- x_return_status standard API parameter
1094 -- x_msg_count standard API parameter
1095 -- x_msg_data standard API parameter
1096 -- p_data_set_name data set name
1097 -- p_hierarchy_id organization hierarchy
1098 -- p_hierarchy_level hierarchy level
1099 -- p_item_from item range from
1100 -- p_item_to item range to
1101 -- p_category_id item category
1102 -- p_date_from date range from (in canonical frmt)
1103 -- p_date_to date range to (in canonical frmt)
1104 -- p_bucket_size bucket size
1105 -- COMMENT : Inventory Position Build processor
1106 -- PRE-COND : all organization in hierarchy share same item master
1110 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1107 --========================================================================
1108 PROCEDURE Build
1109 ( p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1111 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1112 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1113 , p_data_set_name IN VARCHAR2
1114 , p_hierarchy_id IN NUMBER
1115 , p_hierarchy_level IN VARCHAR2
1116 , p_item_from IN VARCHAR2
1117 , p_item_to IN VARCHAR2
1118 , p_category_id IN NUMBER
1119 , p_date_from IN VARCHAR2
1120 , p_date_to IN VARCHAR2
1121 , p_bucket_size IN VARCHAR2
1122 )
1123 IS
1124
1125 l_api_name CONSTANT VARCHAR2(30):= 'Build';
1126 l_return_status VARCHAR2(1);
1127 l_master_org_id NUMBER;
1128 l_context_rec g_context_rec_type;
1129 l_organization_tbl INV_MGD_POS_UTIL.organization_tbl_type;
1130 l_item_tbl INV_MGD_POS_UTIL.item_tbl_type;
1131 l_bucket_tbl INV_MGD_POS_UTIL.bucket_tbl_type;
1132 l_dupl_data_set_name EXCEPTION;
1133 l_empty_data_set EXCEPTION;
1134
1135 BEGIN
1136
1137 IF G_DEBUG = 'Y' THEN
1138 INV_MGD_POS_UTIL.Log
1139 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
1140 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
1141 );
1142 END IF;
1143
1144 -- Initialize message stack if required
1145 IF FND_API.to_Boolean(p_init_msg_list)
1146 THEN
1147 FND_MSG_PUB.initialize;
1148 END IF;
1149
1150 -- reserve data set name
1151 Reserve_Data_Set_Name
1152 ( p_data_set_name => p_data_set_name
1153 , x_return_status => l_return_status
1154 );
1155 IF NOT l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1156 RAISE l_dupl_data_set_name;
1157 END IF;
1158
1159 -- Get context
1160 Get_Context
1161 ( p_data_set_name => p_data_set_name
1162 , p_hierarchy_id => p_hierarchy_id
1163 , p_parent_org_code => p_hierarchy_level
1164 , x_context_rec => l_context_rec
1165 );
1166
1167 -- retrieve master organization ID
1168 l_master_org_id := Get_Master_Org
1169 ( p_hierarchy_level=>l_context_rec.parent_organization_id
1170 );
1171
1172 -- build list of organizations
1173 INV_MGD_POS_ORGANIZATION_MDTR.Build_Organization_List
1174 ( p_hierarchy_id => l_context_rec.hierarchy_id
1175 , p_hierarchy_level_id => l_context_rec.parent_organization_id
1176 , x_organization_tbl => l_organization_tbl
1177 );
1178
1179 -- build list of items
1180 INV_MGD_POS_ITEM_MDTR.Build_Item_List
1181 ( p_organization_tbl => l_organization_tbl
1182 , p_master_org_id => l_master_org_id
1183 , p_item_from => p_item_from
1184 , p_item_to => p_item_to
1185 , p_category_id => p_category_id
1186 , x_item_tbl => l_item_tbl
1187 );
1188
1189 -- build list of buckets
1190 INV_MGD_POS_BUCKET_MDTR.Build_Bucket_List
1191 ( p_organization_id => l_master_org_id
1192 , p_date_from => FND_DATE.canonical_to_date(p_date_from)
1193 , p_date_to => FND_DATE.canonical_to_date(p_date_to)
1194 , p_bucket_size => p_bucket_size
1195 , x_bucket_tbl => l_bucket_tbl
1196 );
1197
1198 -- fetch data into temporary table
1199 Fetch_Data
1200 ( p_context_rec => l_context_rec
1201 , x_organization_tbl => l_organization_tbl
1202 , p_item_tbl => l_item_tbl
1203 , p_bucket_tbl => l_bucket_tbl
1204 );
1205
1206 -- releases the reservation on the data set name
1207 Release_Data_Set_Name(p_data_set_name => p_data_set_name);
1208
1209 -- Verify that data set created data
1210 IF NOT Data_Set_Exists(p_data_set_name => p_data_set_name) THEN
1211 RAISE l_empty_data_set;
1212 END IF;
1213
1214 x_return_status := FND_API.G_RET_STS_SUCCESS;
1215 -- Get message count and data
1216 FND_MSG_PUB.Count_And_Get
1217 ( p_encoded => FND_API.G_FALSE
1218 , p_count => x_msg_count
1219 , p_data => x_msg_data
1220 );
1221
1222 -- calculate hierarchy level information
1223 IF G_DEBUG = 'Y' THEN
1224 INV_MGD_POS_UTIL.Log
1225 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
1226 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
1227 );
1228 END IF;
1229
1230 EXCEPTION
1231
1232 WHEN l_empty_data_set THEN
1233 x_return_status := FND_API.G_RET_STS_ERROR;
1234 -- generate proper error message
1235 FND_MESSAGE.set_name
1236 ( application => 'INV'
1237 , name => 'INV_MGD_IPBD_EMPTY_DATASET'
1238 );
1239 FND_MSG_PUB.Add;
1240 FND_MSG_PUB.Count_And_Get
1241 ( p_encoded => FND_API.G_FALSE
1242 , p_count => x_msg_count
1243 , p_data => x_msg_data
1244 );
1245
1246 WHEN l_dupl_data_set_name THEN
1247 x_return_status := FND_API.G_RET_STS_ERROR;
1248 -- generate proper error message
1249 FND_MESSAGE.set_name
1250 ( application => 'INV'
1251 , name => 'INV_MGD_IPBD_DUPL_DATASET'
1252 );
1253 FND_MSG_PUB.Add;
1254 FND_MSG_PUB.Count_And_Get
1255 ( p_encoded => FND_API.G_FALSE
1256 , p_count => x_msg_count
1257 , p_data => x_msg_data
1258 );
1259
1260 WHEN FND_API.G_EXC_ERROR THEN
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262 -- Get message count and data
1263 FND_MSG_PUB.Count_And_Get
1264 ( p_encoded => FND_API.G_FALSE
1265 , p_count => x_msg_count
1266 , p_data => x_msg_data
1267 );
1268
1269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1271 -- Get message count and data
1272 FND_MSG_PUB.Count_And_Get
1273 ( p_encoded => FND_API.G_FALSE
1274 , p_count => x_msg_count
1275 , p_data => x_msg_data
1276 );
1277
1278 WHEN OTHERS THEN
1279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1280 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1281 THEN
1282 FND_MSG_PUB.Add_Exc_Msg
1283 ( G_PKG_NAME
1284 , 'Build_Organization_List'
1285 );
1286 END IF;
1287 -- Get message count and data
1288 FND_MSG_PUB.Count_And_Get
1289 ( p_encoded => FND_API.G_FALSE
1290 , p_count => x_msg_count
1291 , p_data => x_msg_data
1292 );
1293
1294 END Build;
1295
1296
1297 --========================================================================
1298 -- PROCEDURE : Purge PUBLIC
1299 -- PARAMETERS: p_init_msg_list standard API parameter
1300 -- x_return_status standard API parameter
1301 -- x_msg_count standard API parameter
1302 -- x_msg_data standard API parameter
1303 -- p_purge_all Y to purge all, N otherwise
1304 -- p_created_by purge data set for specific user ID
1305 -- p_creation_date purge data set created before date
1306 -- p_data_set_name purge specific data set name
1307 -- COMMENT : Inventory Position Purge concurrent program; p_purge_all takes
1308 -- priority over other parameters
1309 --========================================================================
1310 PROCEDURE Purge
1311 ( p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1312 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1313 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1314 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1315 , p_purge_all IN VARCHAR2
1316 , p_data_set_name IN VARCHAR2
1317 , p_created_by IN VARCHAR2
1318 , p_creation_date IN VARCHAR2
1319 )
1320 IS
1321
1322 l_api_name CONSTANT VARCHAR2(30):= 'Purge';
1323
1324 BEGIN
1325
1326 IF G_DEBUG = 'Y' THEN
1327 INV_MGD_POS_UTIL.Log
1328 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
1329 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
1330 );
1331 END IF;
1332
1333 IF p_purge_all = 'Y' THEN
1334
1335 MTL_MGD_INV_POSITIONS_PKG.Delete_All;
1336
1337 ELSE
1338
1339 MTL_MGD_INV_POSITIONS_PKG.Delete
1340 ( p_data_set_name => p_data_set_name
1341 , p_created_by => p_created_by
1342 , p_creation_date => FND_DATE.canonical_to_date(p_creation_date)
1343 );
1344
1345 END IF;
1346
1347 x_return_status := FND_API.G_RET_STS_SUCCESS;
1348 -- Get message count and data
1349 FND_MSG_PUB.Count_And_Get
1350 ( p_encoded => FND_API.G_FALSE
1351 , p_count => x_msg_count
1352 , p_data => x_msg_data
1353 );
1354
1355 IF G_DEBUG = 'Y' THEN
1356 INV_MGD_POS_UTIL.Log
1357 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
1358 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
1359 );
1360 END IF;
1361
1362 EXCEPTION
1363
1364 WHEN FND_API.G_EXC_ERROR THEN
1365 x_return_status := FND_API.G_RET_STS_ERROR;
1366 -- Get message count and data
1367 FND_MSG_PUB.Count_And_Get
1368 ( p_encoded => FND_API.G_FALSE
1369 , p_count => x_msg_count
1370 , p_data => x_msg_data
1371 );
1372
1373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1375 -- Get message count and data
1376 FND_MSG_PUB.Count_And_Get
1377 ( p_encoded => FND_API.G_FALSE
1378 , p_count => x_msg_count
1379 , p_data => x_msg_data
1380 );
1381
1382 WHEN OTHERS THEN
1383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1384 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1385 THEN
1386 FND_MSG_PUB.Add_Exc_Msg
1387 ( G_PKG_NAME
1388 , l_api_name
1389 );
1390 END IF;
1391 -- Get message count and data
1392 FND_MSG_PUB.Count_And_Get
1393 ( p_encoded => FND_API.G_FALSE
1394 , p_count => x_msg_count
1398 END Purge;
1395 , p_data => x_msg_data
1396 );
1397
1399
1400
1401
1402 END INV_MGD_POSITIONS_PROC;