1 PACKAGE BODY INV_MGD_POS_ITEM_MDTR AS
2 /* $Header: INVMPITB.pls 120.2.12020000.2 2012/07/09 08:09:22 asugandh ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVMPITS.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Inventory Position View and Export: Item Mediator |
13 --| HISTORY |
14 --| 09/05/2000 Paolo Juvara Created |
15 --| 11/21/2002 Vivian Ma Performance: modify code to print to |
16 --| log only if debug profile option is |
17 --| enabled |
18 --| 19/AUG/2005 Neelam Soni Modified for bug 4357322 |
19 --| 09/MAR/2006 Neelam Soni Modified for bug 4951736 |
20 --+======================================================================*/
21
22 --===================
23 -- CONSTANTS
24 --===================
25 G_PKG_NAME CONSTANT VARCHAR2(30):= 'INV_MGD_POS_ITEM_MDTR';
26
27
28 --===================
29 -- GLOBAL VARIABLES
30 --===================
31 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
32
33 --===================
34 -- PROCEDURES AND FUNCTIONS
35 --===================
36
37 --========================================================================
38 -- PROCEDURE : Build_Item_List PUBLIC
39 -- PARAMETERS: p_organization_tbl list of organization
40 -- p_master_org_id master item organization
41 -- p_item_from item from range
42 -- p_item_to item to range
43 -- p_category_id category id
44 -- x_item_tbl item list
45 -- COMMENT : Builds the list of items to view
46 -- PRE-COND : p_organization_tbl is not empty
47 -- POST-COND : x_item_tbl is not empty
48 --========================================================================
49 PROCEDURE Build_Item_List
50 ( p_organization_tbl IN INV_MGD_POS_UTIL.organization_tbl_type
51 , p_master_org_id IN NUMBER DEFAULT NULL
52 , p_item_from IN VARCHAR2 DEFAULT NULL
53 , p_item_to IN VARCHAR2 DEFAULT NULL
54 , p_category_id IN NUMBER DEFAULT NULL
55 , x_item_tbl IN OUT NOCOPY INV_MGD_POS_UTIL.item_tbl_type
56 )
57 IS
58
59 l_api_name CONSTANT VARCHAR2(30):= 'Build_Item_List';
60 -- Following usused variable commented during bug 4357322
61 --l_master_organization_id NUMBER;
62 l_category_set_id NUMBER;
63 l_func_area_id_1 NUMBER := 1; -- Bug 8926294
64 CURSOR l_item1_crsr
65 ( p_organization_id NUMBER
66 , p_item_from VARCHAR2
67 , p_item_to VARCHAR2
68 ) IS
69 /* yawang fix bug 2210524 use kfv view to replace the base table to get all the segments
70 SELECT organization_id
71 , inventory_item_id
72 , segment1
73 FROM mtl_system_items_b msi
74 WHERE organization_id = p_organization_id
75 AND segment1
76 BETWEEN NVL(p_item_from,segment1) AND NVL(p_item_to,segment1);
77 */
78 /* UNION part from query is deleted for better performance. Bug 4951736
79 SELECT organization_id
80 , inventory_item_id
81 FROM mtl_system_items_kfv msik
82 WHERE organization_id = p_organization_id
83 AND concatenated_segments
84 BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments)
85 -- Following union clause added for bug 4357322
86 UNION
87 SELECT p_organization_id, -1
88 FROM dual where NOT EXISTS
89 ( SELECT 1 FROM mtl_system_items_kfv
90 WHERE organization_id = p_organization_id
91 AND concatenated_segments
92 BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments)
93 ) ;
94 */
95 SELECT organization_id
96 , inventory_item_id
97 FROM mtl_system_items_kfv msik
98 WHERE organization_id = p_organization_id
99 AND concatenated_segments
100 BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments);
101
102 CURSOR l_item2_crsr
103 ( p_organization_id NUMBER
104 , p_item_from VARCHAR2
105 , p_item_to VARCHAR2
106 --, p_mstr_organization_id NUMBER yawang fix bug 2210154
107 , p_category_set_id NUMBER
108 , p_category_id NUMBER
109 ) IS
110 SELECT msik.organization_id
111 , msik.inventory_item_id
112 FROM mtl_item_categories mic
113 , mtl_system_items_kfv msik
114 WHERE msik.organization_id = p_organization_id
115 AND msik.concatenated_segments
116 BETWEEN NVL(p_item_from, msik.concatenated_segments)
117 AND NVL(p_item_to,msik.concatenated_segments)
118 AND mic.inventory_item_id = msik.inventory_item_id
119 AND mic.organization_id = msik.organization_id --p_mstr_organization_id,
120 --fix bug 2210154 yawang
121 AND mic.category_set_id = p_category_set_id
122 AND mic.category_id = p_category_id
123 -- Following union clause added for bug 4357322
124 UNION
125 SELECT p_organization_id, -1
126 FROM dual where NOT EXISTS
127 ( SELECT 1 FROM mtl_system_items_kfv msik, mtl_item_categories mic
128 WHERE msik.organization_id = p_organization_id
129 AND msik.concatenated_segments
130 BETWEEN NVL(p_item_from,msik.concatenated_segments)
131 AND NVL(p_item_to,msik.concatenated_segments)
132 AND mic.inventory_item_id = msik.inventory_item_id
133 AND mic.organization_id = msik.organization_id
134 AND mic.category_set_id = p_category_set_id
135 AND mic.category_id = p_category_id
136 )
137 ;
138
139 /* yawang fix bug 2210524 use kfv view to replace the base table to get all the segments
140 CURSOR l_item2_crsr
141 ( p_organization_id NUMBER
142 , p_item_from VARCHAR2
143 , p_item_to VARCHAR2
144 --, p_mstr_organization_id NUMBER yawang fix bug 2210154
145 , p_category_set_id NUMBER
146 , p_category_id NUMBER
147 ) IS
148 SELECT msi.organization_id
149 , msi.inventory_item_id
150 , msi.segment1
151 FROM mtl_item_categories mic
152 , mtl_system_items_b msi
153 WHERE msi.organization_id = p_organization_id
154 AND msi.segment1
155 BETWEEN NVL(p_item_from, msi.segment1) AND NVL(p_item_to,msi.segment1)
156 AND mic.inventory_item_id = msi.inventory_item_id
157 AND mic.organization_id = msi.organization_id --p_mstr_organization_id,fix bug 2210154 yawang
158 AND mic.category_set_id = p_category_set_id
159 AND mic.category_id = p_category_id;
160 */
161 BEGIN
162
163 IF G_DEBUG = 'Y' THEN
164 INV_MGD_POS_UTIL.Log
165 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
166 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
167 );
168 END IF;
169
170 -- Initialize organization list
171 x_item_tbl.DELETE;
172
173 IF p_category_id IS NULL THEN
174
175 -- don't need to join to mtl_item_categories
176 IF G_DEBUG = 'Y' THEN
177 INV_MGD_POS_UTIL.Log
178 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
179 , p_msg => 'build item list without category filter'
180 );
181 END IF;
182
183 BEGIN
184 FOR l_Idx IN p_organization_tbl.FIRST..p_organization_tbl.LAST LOOP
185 OPEN l_item1_crsr
186 ( p_organization_tbl(l_Idx).id
187 , p_item_from
188 , p_item_to
189 );
190 LOOP
191 FETCH l_item1_crsr
192 INTO
193 x_item_tbl(x_item_tbl.COUNT + 1).organization_id
194 , x_item_tbl(x_item_tbl.COUNT + 1).item_id;
195 -- Added for bug 4951736 to avoid union in query
196 IF l_item1_crsr%NOTFOUND AND l_item1_crsr%ROWCOUNT = 0 THEN
197 x_item_tbl(x_item_tbl.COUNT + 1).organization_id :=
198 p_organization_tbl(l_Idx).id;
199 x_item_tbl(x_item_tbl.COUNT).item_id := -1;
200 x_item_tbl(x_item_tbl.COUNT).organization_code :=
201 p_organization_tbl(l_Idx).code;
202 END IF;
203
204 EXIT WHEN l_item1_crsr%NOTFOUND;
205 x_item_tbl(x_item_tbl.COUNT).organization_code :=
206 p_organization_tbl(l_Idx).code;
207 END LOOP;
208
209
210 CLOSE l_item1_crsr;
211 END LOOP;
212 EXCEPTION
213 WHEN OTHERS THEN
214 IF l_item1_crsr%ISOPEN THEN
215 CLOSE l_item1_crsr;
216 END IF;
217 RAISE;
218 END;
219
220 ELSE
221
222 -- need to join to mtl_item_categories;
223 IF G_DEBUG = 'Y' THEN
224 INV_MGD_POS_UTIL.Log
225 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
226 , p_msg => 'build item list with category filter'
227 );
228 END IF;
229
230 /*fix bug 2210154 yawang no need of master org
231 -- get the master org first if needed
232 IF p_master_org_id IS NULL THEN
233 SELECT master_organization_id
234 INTO l_master_organization_id
235 FROM mtl_parameters
236 WHERE organization_id = p_organization_tbl(1).id;
237 ELSE
238 l_master_organization_id := p_master_org_id;
239 END IF; */
240
241 -- get the default category set for Inventory
242 SELECT category_set_id
243 INTO l_category_set_id
244 FROM mtl_default_category_sets
245 WHERE functional_area_id = l_func_area_id_1; -- Bug 8926294
246
247 BEGIN
248 FOR l_Idx IN p_organization_tbl.FIRST..p_organization_tbl.LAST LOOP
249 OPEN l_item2_crsr
250 ( p_organization_tbl(l_Idx).id
251 , p_item_from
252 , p_item_to
253 --, l_master_organization_id yawang fix bug 2210154
254 , l_category_set_id
255 , p_category_id
256 );
257 LOOP
258 FETCH l_item2_crsr
259 INTO
260 x_item_tbl(x_item_tbl.COUNT + 1).organization_id
261 , x_item_tbl(x_item_tbl.COUNT + 1).item_id;
262 EXIT WHEN l_item2_crsr%NOTFOUND;
263 x_item_tbl(x_item_tbl.COUNT).organization_code :=
264 p_organization_tbl(l_Idx).code;
265 END LOOP;
266 CLOSE l_item2_crsr;
267 END LOOP;
268 EXCEPTION
269 WHEN OTHERS THEN
270 IF l_item2_crsr%ISOPEN THEN
271 CLOSE l_item2_crsr;
272 END IF;
273 RAISE;
274 END;
275
276 END IF;
277
278 IF G_DEBUG = 'Y' THEN
279 INV_MGD_POS_UTIL.Log
280 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
281 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
282 );
283 END IF;
284
285 END Build_Item_List;
286
287
288
289
290 END INV_MGD_POS_ITEM_MDTR;
[Home] [Help]