[Home] [Help]
PACKAGE BODY: APPS.QP_ITEM_RANGE_PVT
Source
1 PACKAGE BODY QP_ITEM_RANGE_PVT AS
2 /* $Header: QPXITRGB.pls 120.1 2005/06/14 05:22:17 appldev $ */
3
4 PROCEDURE items_in_range
5 (
6 p_segment1_lohi IN VARCHAR2,
7 p_segment2_lohi IN VARCHAR2,
8 p_segment3_lohi IN VARCHAR2,
9 p_segment4_lohi IN VARCHAR2,
10 p_segment5_lohi IN VARCHAR2,
11 p_segment6_lohi IN VARCHAR2,
12 p_segment7_lohi IN VARCHAR2,
13 p_segment8_lohi IN VARCHAR2,
14 p_segment9_lohi IN VARCHAR2,
15 p_segment10_lohi IN VARCHAR2,
16 p_segment11_lohi IN VARCHAR2,
17 p_segment12_lohi IN VARCHAR2,
18 p_segment13_lohi IN VARCHAR2,
19 p_segment14_lohi IN VARCHAR2,
20 p_segment15_lohi IN VARCHAR2,
21 p_segment16_lohi IN VARCHAR2,
22 p_segment17_lohi IN VARCHAR2,
23 p_segment18_lohi IN VARCHAR2,
24 p_segment19_lohi IN VARCHAR2,
25 p_segment20_lohi IN VARCHAR2,
26 p_org_id IN NUMBER,
27 p_category_set_id IN NUMBER,
28 p_category_id IN NUMBER,
29 p_status_code IN VARCHAR2,
30 p_item_tbl OUT NOCOPY /* file.sql.39 change */ l_tbl%TYPE
31 )
32 IS
33 l_items_tbl DBMS_SQL.VARCHAR2_TABLE;
34 l_rows_updated INTEGER;
35 l_cursor_id INTEGER;
36 l_batchsize CONSTANT INTEGER := 1;
37 l_update_stmt VARCHAR2(4000);
38 l_rows_fetched NUMBER;
39
40 l_orgflag BOOLEAN := FALSE;
41 l_setflag BOOLEAN := FALSE;
42 l_catflag BOOLEAN := FALSE;
43 l_statflag BOOLEAN := FALSE;
44 l_status VARCHAR2(1);
45 l_industry VARCHAR2(1);
46 l_application_id NUMBER := 660;
47 l_retval BOOLEAN;
48
49
50
51
52 BEGIN
53
54 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
55
56 l_update_stmt := 'SELECT m.inventory_item_id
57 FROM mtl_system_items m
58 WHERE m.organization_id = :l_org ';
59
60 l_retval := fnd_installation.get(l_application_id,l_application_id,
61 l_status,l_industry);
62 IF l_status = 'I' THEN
63 l_update_stmt := l_update_stmt || 'AND m.CUSTOMER_ORDER_FLAG = ''Y'' ';
64 END IF;
65
66
67
68 IF (p_segment1_lohi = ''''' AND ''''') AND
69 (p_segment2_lohi = ''''' AND ''''') AND
70 (p_segment3_lohi = ''''' AND ''''') AND
71 (p_segment4_lohi = ''''' AND ''''') AND
72 (p_segment5_lohi = ''''' AND ''''') AND
73 (p_segment6_lohi = ''''' AND ''''') AND
74 (p_segment7_lohi = ''''' AND ''''') AND
75 (p_segment8_lohi = ''''' AND ''''') AND
76 (p_segment9_lohi = ''''' AND ''''') AND
77 (p_segment10_lohi = ''''' AND ''''') AND
78 (p_segment11_lohi = ''''' AND ''''') AND
79 (p_segment12_lohi = ''''' AND ''''') AND
80 (p_segment13_lohi = ''''' AND ''''') AND
81 (p_segment14_lohi = ''''' AND ''''') AND
82 (p_segment15_lohi = ''''' AND ''''') AND
83 (p_segment16_lohi = ''''' AND ''''') AND
84 (p_segment17_lohi = ''''' AND ''''') AND
85 (p_segment18_lohi = ''''' AND ''''') AND
86 (p_segment19_lohi = ''''' AND ''''') AND
87 (p_segment20_lohi = ''''' AND ''''')
88 THEN
89 l_orgflag := TRUE;
90 ELSE
91
92 IF (p_segment1_lohi = ''''' AND ''''') THEN
93 l_update_stmt := l_update_stmt ||
94 'AND (m.segment1 IS NULL) ';
95 ELSE
96 l_update_stmt := l_update_stmt ||
97 'AND (m.segment1 BETWEEN ';
98 l_update_stmt := l_update_stmt ||p_segment1_lohi;
99 l_update_stmt := l_update_stmt ||' )';
100 END IF;
101
102 IF (p_segment2_lohi = ''''' AND ''''') THEN
103 l_update_stmt := l_update_stmt ||
104 'AND (m.segment2 IS NULL) ';
105 ELSE
106 l_update_stmt := l_update_stmt ||
107 'AND (m.segment2 BETWEEN ';
108 l_update_stmt := l_update_stmt ||p_segment2_lohi;
109 l_update_stmt := l_update_stmt ||' )';
110 END IF;
111
112 IF (p_segment3_lohi = ''''' AND ''''') THEN
113 l_update_stmt := l_update_stmt ||
114 'AND (m.segment3 IS NULL) ';
115 ELSE
116 l_update_stmt := l_update_stmt ||
117 'AND (m.segment3 BETWEEN ';
118 l_update_stmt := l_update_stmt ||p_segment3_lohi;
119 l_update_stmt := l_update_stmt ||' )';
120 END IF;
121
122 IF (p_segment4_lohi = ''''' AND ''''') THEN
123 l_update_stmt := l_update_stmt ||
124 'AND (m.segment4 IS NULL) ';
125 ELSE
126 l_update_stmt := l_update_stmt ||
127 'AND (m.segment4 BETWEEN ';
128 l_update_stmt := l_update_stmt ||p_segment4_lohi;
129 l_update_stmt := l_update_stmt ||' )';
130 END IF;
131
132 IF (p_segment5_lohi = ''''' AND ''''') THEN
133 l_update_stmt := l_update_stmt ||
134 'AND (m.segment5 IS NULL) ';
135 ELSE
136 l_update_stmt := l_update_stmt ||
137 'AND (m.segment5 BETWEEN ';
138 l_update_stmt := l_update_stmt ||p_segment5_lohi;
139 l_update_stmt := l_update_stmt ||' )';
140 END IF;
141
142 IF (p_segment6_lohi = ''''' AND ''''') THEN
143 l_update_stmt := l_update_stmt ||
144 'AND (m.segment6 IS NULL) ';
145 ELSE
146 l_update_stmt := l_update_stmt ||
147 'AND (m.segment6 BETWEEN ';
148 l_update_stmt := l_update_stmt ||p_segment6_lohi;
149 l_update_stmt := l_update_stmt ||' )';
150 END IF;
151
152 IF (p_segment7_lohi = ''''' AND ''''') THEN
153 l_update_stmt := l_update_stmt ||
154 'AND (m.segment7 IS NULL) ';
155 ELSE
156 l_update_stmt := l_update_stmt ||
157 'AND (m.segment7 BETWEEN ';
158 l_update_stmt := l_update_stmt ||p_segment7_lohi;
159 l_update_stmt := l_update_stmt ||' )';
160 END IF;
161
162 IF (p_segment8_lohi = ''''' AND ''''') THEN
163 l_update_stmt := l_update_stmt ||
164 'AND (m.segment8 IS NULL) ';
165 ELSE
166 l_update_stmt := l_update_stmt ||
167 'AND (m.segment8 BETWEEN ';
168 l_update_stmt := l_update_stmt ||p_segment8_lohi;
169 l_update_stmt := l_update_stmt ||' )';
170 END IF;
171
172 IF (p_segment9_lohi = ''''' AND ''''') THEN
173 l_update_stmt := l_update_stmt ||
174 'AND (m.segment9 IS NULL) ';
175 ELSE
176 l_update_stmt := l_update_stmt ||
177 'AND (m.segment9 BETWEEN ';
178 l_update_stmt := l_update_stmt ||p_segment9_lohi;
179 l_update_stmt := l_update_stmt ||' )';
180 END IF;
181
182 IF (p_segment10_lohi = ''''' AND ''''') THEN
183 l_update_stmt := l_update_stmt ||
184 'AND (m.segment10 IS NULL) ';
185 ELSE
186 l_update_stmt := l_update_stmt ||
187 'AND (m.segment10 BETWEEN ';
188 l_update_stmt := l_update_stmt ||p_segment10_lohi;
189 l_update_stmt := l_update_stmt ||' )';
190 END IF;
191
192 IF (p_segment11_lohi = ''''' AND ''''') THEN
193 l_update_stmt := l_update_stmt ||
194 'AND (m.segment11 IS NULL) ';
195 ELSE
196 l_update_stmt := l_update_stmt ||
197 'AND (m.segment11 BETWEEN ';
198 l_update_stmt := l_update_stmt ||p_segment11_lohi;
199 l_update_stmt := l_update_stmt ||' )';
200 END IF;
201
202 IF (p_segment12_lohi = ''''' AND ''''') THEN
203 l_update_stmt := l_update_stmt ||
204 'AND (m.segment12 IS NULL) ';
205 ELSE
206 l_update_stmt := l_update_stmt ||
207 'AND (m.segment12 BETWEEN ';
208 l_update_stmt := l_update_stmt ||p_segment12_lohi;
209 l_update_stmt := l_update_stmt ||' )';
210 END IF;
211
212 IF (p_segment13_lohi = ''''' AND ''''') THEN
213 l_update_stmt := l_update_stmt ||
214 'AND (m.segment13 IS NULL) ';
215 ELSE
216 l_update_stmt := l_update_stmt ||
217 'AND (m.segment13 BETWEEN ';
218 l_update_stmt := l_update_stmt ||p_segment13_lohi;
219 l_update_stmt := l_update_stmt ||' )';
220 END IF;
221
222 IF (p_segment14_lohi = ''''' AND ''''') THEN
223 l_update_stmt := l_update_stmt ||
224 'AND (m.segment14 IS NULL) ';
225 ELSE
226 l_update_stmt := l_update_stmt ||
227 'AND (m.segment14 BETWEEN ';
228 l_update_stmt := l_update_stmt ||p_segment14_lohi;
229 l_update_stmt := l_update_stmt ||' )';
230 END IF;
231
232 IF (p_segment15_lohi = ''''' AND ''''') THEN
233 l_update_stmt := l_update_stmt ||
234 'AND (m.segment15 IS NULL) ';
235 ELSE
236 l_update_stmt := l_update_stmt ||
237 'AND (m.segment15 BETWEEN ';
238 l_update_stmt := l_update_stmt ||p_segment15_lohi;
239 l_update_stmt := l_update_stmt ||' )';
240 END IF;
241
242 IF (p_segment16_lohi = ''''' AND ''''') THEN
243 l_update_stmt := l_update_stmt ||
244 'AND (m.segment16 IS NULL) ';
245 ELSE
246 l_update_stmt := l_update_stmt ||
247 'AND (m.segment16 BETWEEN ';
248 l_update_stmt := l_update_stmt ||p_segment16_lohi;
249 l_update_stmt := l_update_stmt ||' )';
250 END IF;
251
252 IF (p_segment17_lohi = ''''' AND ''''') THEN
253 l_update_stmt := l_update_stmt ||
254 'AND (m.segment17 IS NULL) ';
255 ELSE
256 l_update_stmt := l_update_stmt ||
257 'AND (m.segment17 BETWEEN ';
258 l_update_stmt := l_update_stmt ||p_segment17_lohi;
259 l_update_stmt := l_update_stmt ||' )';
260 END IF;
261
262 IF (p_segment18_lohi = ''''' AND ''''') THEN
263 l_update_stmt := l_update_stmt ||
264 'AND (m.segment18 IS NULL) ';
265 ELSE
266 l_update_stmt := l_update_stmt ||
267 'AND (m.segment18 BETWEEN ';
268 l_update_stmt := l_update_stmt ||p_segment18_lohi;
269 l_update_stmt := l_update_stmt ||' )';
270 END IF;
271
272 IF (p_segment19_lohi = ''''' AND ''''') THEN
273 l_update_stmt := l_update_stmt ||
274 'AND (m.segment19 IS NULL) ';
275 ELSE
276 l_update_stmt := l_update_stmt ||
277 'AND (m.segment19 BETWEEN ';
278 l_update_stmt := l_update_stmt ||p_segment19_lohi;
279 l_update_stmt := l_update_stmt ||' )';
280 END IF;
281
282 IF (p_segment20_lohi = ''''' AND ''''') THEN
283 l_update_stmt := l_update_stmt ||
284 'AND (m.segment20 IS NULL) ';
285 ELSE
286 l_update_stmt := l_update_stmt ||
287 'AND (m.segment20 BETWEEN ';
288 l_update_stmt := l_update_stmt ||p_segment20_lohi;
289 l_update_stmt := l_update_stmt ||' )';
290 END IF;
291
292 l_orgflag := TRUE;
293
294 END IF;
295
296
297 IF (p_status_code IS NOT NULL) THEN
298 l_update_stmt := l_update_stmt ||
299 'AND m.inventory_item_status_code = :l_stat ';
300 l_statflag := TRUE;
301 END IF;
302
303 /* Added by dhgupta for 2068915 */
304
305 IF p_category_set_id IS NULL THEN
306 l_update_stmt := l_update_stmt ||
307 'AND m.inventory_item_id IN
308 ( SELECT ic.inventory_item_id
309 FROM mtl_item_categories ic
310 WHERE ic.inventory_item_id = m.inventory_item_id
311 AND ic.organization_id = m.organization_id
312 AND ic.organization_id = :l_org )';
313 END IF;
314
315 /* Added by dhgupta for 2068915 */
316
317 IF p_category_set_id IS NOT NULL AND p_category_id IS NULL THEN
318 l_update_stmt := l_update_stmt ||
319 'AND m.inventory_item_id IN
320 ( SELECT ic.inventory_item_id
321 FROM mtl_item_categories ic
322 WHERE ic.inventory_item_id = m.inventory_item_id
323 AND ic.organization_id = m.organization_id
324 AND ic.organization_id = :l_org
325 AND ic.category_set_id = :l_set )';
326 l_setflag := TRUE;
327 END IF;
328
329 /* Added by dhgupta for 2068915 */
330
331 IF p_category_set_id IS NOT NULL AND p_category_id IS NOT NULL THEN
332 l_update_stmt := l_update_stmt ||
333 'AND m.inventory_item_id IN
334 ( SELECT ic.inventory_item_id
335 FROM mtl_item_categories ic
336 WHERE ic.inventory_item_id = m.inventory_item_id
337 AND ic.organization_id = m.organization_id
338 AND ic.organization_id = :l_org
339 AND ic.category_set_id = :l_set
340 AND ic.category_id = :l_cat )';
341 l_setflag := TRUE;
342 l_catflag := TRUE;
343 END IF;
344
345 /*
346 IF (p_category_id IS NOT NULL) THEN
347 l_update_stmt := l_update_stmt ||
348 'AND m.inventory_item_id IN
349 ( SELECT ic.inventory_item_id
350 FROM mtl_item_categories ic
351 WHERE ic.inventory_item_id = m.inventory_item_id
352 AND ic.organization_id = m.organization_id
353 AND ic.category_set_id = :l_set
354 AND ic.category_id = :l_cat )';
355 l_setflag := TRUE;
356 l_catflag := TRUE;
357 END IF;
358 */
359
360 --Parse the statement
361 DBMS_SQL.PARSE(l_cursor_id, l_update_stmt, DBMS_SQL.V7);
362
363 --Bind variables to the placeholders
364
365 IF l_orgflag = TRUE THEN
366 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':l_org' , p_org_id);
367 END IF;
368
369 IF l_statflag = TRUE THEN
370 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':l_stat' , p_status_code);
371 END IF;
372
373 IF l_setflag = TRUE THEN
374 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':l_set' , p_category_set_id);
375 END IF;
376
377 IF l_catflag = TRUE THEN
378 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':l_cat' , p_category_id);
379 END IF;
380
381 -- Define output variables for the select.
382
383 DBMS_SQL.DEFINE_ARRAY(l_cursor_id, 1, p_item_tbl, l_batchsize, 1);
384
385 --Execute the Statement
386 l_rows_updated := DBMS_SQL.EXECUTE(l_cursor_id);
387
388 --This is the fetch loop.
389
390 LOOP
391 l_rows_fetched := DBMS_SQL.FETCH_ROWS(l_cursor_id);
392 EXIT WHEN l_rows_fetched = 0;
393 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, p_item_tbl);
394 END LOOP;
395
396 --Close the cursor
397 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
402 RAISE;
403
404 END ITEMS_IN_RANGE;
405
406 END QP_ITEM_RANGE_PVT;