1 PACKAGE BODY INV_MGD_POS_ITEM_MDTR AS
2 /* $Header: INVMPITB.pls 120.2 2006/03/09 23:46:24 nesoni noship $ */
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 CURSOR l_item1_crsr
64 ( p_organization_id NUMBER
65 , p_item_from VARCHAR2
66 , p_item_to VARCHAR2
67 ) IS
68 /* yawang fix bug 2210524 use kfv view to replace the base table to get all the segments
69 SELECT organization_id
70 , inventory_item_id
71 , segment1
72 FROM mtl_system_items_b msi
73 WHERE organization_id = p_organization_id
74 AND segment1
75 BETWEEN NVL(p_item_from,segment1) AND NVL(p_item_to,segment1);
76 */
77 /* UNION part from query is deleted for better performance. Bug 4951736
78 SELECT organization_id
79 , inventory_item_id
80 FROM mtl_system_items_kfv msik
81 WHERE organization_id = p_organization_id
82 AND concatenated_segments
83 BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments)
84 -- Following union clause added for bug 4357322
85 UNION
86 SELECT p_organization_id, -1
87 FROM dual where NOT EXISTS
88 ( SELECT 1 FROM mtl_system_items_kfv
89 WHERE organization_id = p_organization_id
90 AND concatenated_segments
91 BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments)
92 ) ;
93 */
94 SELECT organization_id
95 , inventory_item_id
96 FROM mtl_system_items_kfv msik
97 WHERE organization_id = p_organization_id
98 AND concatenated_segments
99 BETWEEN NVL(p_item_from,concatenated_segments) AND NVL(p_item_to,concatenated_segments);
100
101 CURSOR l_item2_crsr
102 ( p_organization_id NUMBER
103 , p_item_from VARCHAR2
104 , p_item_to VARCHAR2
105 --, p_mstr_organization_id NUMBER yawang fix bug 2210154
106 , p_category_set_id NUMBER
107 , p_category_id NUMBER
108 ) IS
109 SELECT msik.organization_id
110 , msik.inventory_item_id
111 FROM mtl_item_categories mic
112 , mtl_system_items_kfv msik
113 WHERE msik.organization_id = p_organization_id
114 AND msik.concatenated_segments
115 BETWEEN NVL(p_item_from, msik.concatenated_segments)
116 AND NVL(p_item_to,msik.concatenated_segments)
117 AND mic.inventory_item_id = msik.inventory_item_id
118 AND mic.organization_id = msik.organization_id --p_mstr_organization_id,
119 --fix bug 2210154 yawang
120 AND mic.category_set_id = p_category_set_id
121 AND mic.category_id = p_category_id
122 -- Following union clause added for bug 4357322
123 UNION
124 SELECT p_organization_id, -1
125 FROM dual where NOT EXISTS
126 ( SELECT 1 FROM mtl_system_items_kfv msik, mtl_item_categories mic
127 WHERE msik.organization_id = p_organization_id
128 AND msik.concatenated_segments
129 BETWEEN NVL(p_item_from,msik.concatenated_segments)
130 AND NVL(p_item_to,msik.concatenated_segments)
131 AND mic.inventory_item_id = msik.inventory_item_id
132 AND mic.organization_id = msik.organization_id
133 AND mic.category_set_id = p_category_set_id
134 AND mic.category_id = p_category_id
135 )
136 ;
137
138 /* yawang fix bug 2210524 use kfv view to replace the base table to get all the segments
139 CURSOR l_item2_crsr
140 ( p_organization_id NUMBER
141 , p_item_from VARCHAR2
142 , p_item_to VARCHAR2
143 --, p_mstr_organization_id NUMBER yawang fix bug 2210154
144 , p_category_set_id NUMBER
145 , p_category_id NUMBER
146 ) IS
147 SELECT msi.organization_id
148 , msi.inventory_item_id
149 , msi.segment1
150 FROM mtl_item_categories mic
151 , mtl_system_items_b msi
152 WHERE msi.organization_id = p_organization_id
153 AND msi.segment1
154 BETWEEN NVL(p_item_from, msi.segment1) AND NVL(p_item_to,msi.segment1)
155 AND mic.inventory_item_id = msi.inventory_item_id
156 AND mic.organization_id = msi.organization_id --p_mstr_organization_id,fix bug 2210154 yawang
157 AND mic.category_set_id = p_category_set_id
158 AND mic.category_id = p_category_id;
159 */
160 BEGIN
161
162 IF G_DEBUG = 'Y' THEN
163 INV_MGD_POS_UTIL.Log
164 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
165 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
166 );
167 END IF;
168
169 -- Initialize organization list
170 x_item_tbl.DELETE;
171
172 IF p_category_id IS NULL THEN
173
174 -- don't need to join to mtl_item_categories
175 IF G_DEBUG = 'Y' THEN
176 INV_MGD_POS_UTIL.Log
177 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
178 , p_msg => 'build item list without category filter'
179 );
180 END IF;
181
182 BEGIN
183 FOR l_Idx IN p_organization_tbl.FIRST..p_organization_tbl.LAST LOOP
184 OPEN l_item1_crsr
185 ( p_organization_tbl(l_Idx).id
186 , p_item_from
187 , p_item_to
188 );
189 LOOP
190 FETCH l_item1_crsr
191 INTO
192 x_item_tbl(x_item_tbl.COUNT + 1).organization_id
193 , x_item_tbl(x_item_tbl.COUNT + 1).item_id;
194 -- Added for bug 4951736 to avoid union in query
195 IF l_item1_crsr%NOTFOUND AND l_item1_crsr%ROWCOUNT = 0 THEN
196 x_item_tbl(x_item_tbl.COUNT + 1).organization_id :=
197 p_organization_tbl(l_Idx).id;
198 x_item_tbl(x_item_tbl.COUNT).item_id := -1;
199 x_item_tbl(x_item_tbl.COUNT).organization_code :=
200 p_organization_tbl(l_Idx).code;
201 END IF;
202
203 EXIT WHEN l_item1_crsr%NOTFOUND;
204 x_item_tbl(x_item_tbl.COUNT).organization_code :=
205 p_organization_tbl(l_Idx).code;
206 END LOOP;
207
208
209 CLOSE l_item1_crsr;
210 END LOOP;
211 EXCEPTION
212 WHEN OTHERS THEN
213 IF l_item1_crsr%ISOPEN THEN
214 CLOSE l_item1_crsr;
215 END IF;
216 RAISE;
217 END;
218
219 ELSE
220
221 -- need to join to mtl_item_categories;
222 IF G_DEBUG = 'Y' THEN
223 INV_MGD_POS_UTIL.Log
224 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
225 , p_msg => 'build item list with category filter'
226 );
227 END IF;
228
229 /*fix bug 2210154 yawang no need of master org
230 -- get the master org first if needed
231 IF p_master_org_id IS NULL THEN
232 SELECT master_organization_id
233 INTO l_master_organization_id
234 FROM mtl_parameters
235 WHERE organization_id = p_organization_tbl(1).id;
236 ELSE
237 l_master_organization_id := p_master_org_id;
238 END IF; */
239
240 -- get the default category set for Inventory
241 SELECT category_set_id
242 INTO l_category_set_id
243 FROM mtl_default_category_sets
244 WHERE functional_area_id = 1;
245
246 BEGIN
247 FOR l_Idx IN p_organization_tbl.FIRST..p_organization_tbl.LAST LOOP
248 OPEN l_item2_crsr
249 ( p_organization_tbl(l_Idx).id
250 , p_item_from
251 , p_item_to
252 --, l_master_organization_id yawang fix bug 2210154
253 , l_category_set_id
254 , p_category_id
255 );
256 LOOP
257 FETCH l_item2_crsr
258 INTO
259 x_item_tbl(x_item_tbl.COUNT + 1).organization_id
260 , x_item_tbl(x_item_tbl.COUNT + 1).item_id;
261 EXIT WHEN l_item2_crsr%NOTFOUND;
262 x_item_tbl(x_item_tbl.COUNT).organization_code :=
263 p_organization_tbl(l_Idx).code;
264 END LOOP;
265 CLOSE l_item2_crsr;
266 END LOOP;
267 EXCEPTION
268 WHEN OTHERS THEN
269 IF l_item2_crsr%ISOPEN THEN
270 CLOSE l_item2_crsr;
271 END IF;
272 RAISE;
273 END;
274
275 END IF;
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||'.'||l_api_name
281 );
282 END IF;
283
284 END Build_Item_List;
285
286
287
288
289 END INV_MGD_POS_ITEM_MDTR;
[Home] [Help]