[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_POPULATE_MI_PVT
Source
1 PACKAGE BODY ICX_CAT_POPULATE_MI_PVT AS
2 /* $Header: ICXVPPMB.pls 120.8.12010000.2 2008/08/02 14:39:28 kkram ship $*/
3
4 --Will have the MI cursors
5 --for pre-upgrade, upgrade and online populate
6
7 --Both will call icx_cat_populate_item_pvt.populatePODocs
8
9 -- Constants
10 G_PKG_NAME CONSTANT VARCHAR2(30) :='ICX_CAT_POPULATE_MI_PVT';
11
12 g_upgrade_last_run_date DATE;
13 g_online_mode VARCHAR2(15) := null;
14 g_onlineUpdate_mode VARCHAR2(15) := 'OnlineUpdate';
15 g_bulkUpdate_mode VARCHAR2(15) := 'BulkUpdate';
16 g_catgItemUpdate_mode VARCHAR2(15) := 'CatgItemUpdate';
17 g_mtl_category_id NUMBER;
18 g_inventory_item_id NUMBER;
19 g_organization_id NUMBER;
20 g_request_id NUMBER;
21 g_entity_type mtl_item_bulkload_recs.entity_type%TYPE;
22 g_start_rowid ROWID;
23 g_end_rowid ROWID;
24
25 PROCEDURE openOnlineItemCatgDeleteCursor
26 ( P_INVENTORY_ITEM_ID IN NUMBER ,
27 P_ORGANIZATION_ID IN NUMBER
28 )
29 IS
30 l_api_name CONSTANT VARCHAR2(30) := 'openOnlineItemCatgDeleteCursor';
31 l_err_loc PLS_INTEGER;
32 l_masterItem_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
33 BEGIN
34 l_err_loc := 100;
35 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
36 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
37 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
38 'Processing cursor:' || l_api_name ||
39 ', g_NULL_NUMBER:' || ICX_CAT_UTIL_PVT.g_NULL_NUMBER ||
40 ', g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id ||
41 ', P_INVENTORY_ITEM_ID:' || P_INVENTORY_ITEM_ID ||
42 ', P_ORGANIZATION_ID:' || P_ORGANIZATION_ID );
43 END IF;
44
45 l_err_loc := 150;
46 --First close the cursor
47 IF (l_masterItem_csr%ISOPEN) THEN
48 l_err_loc := 200;
49 CLOSE l_masterItem_csr;
50 END IF;
51
52 -- Have a different cursor instead of using the cursor for openOnlineItemChangeCursor
53 -- because since the category assignment has deleted, in this case we need to do an
54 -- outer join with mtl_item_categories and check if the po_category_id is null
55 -- and the row is present in icx_cat_items_ctx_hdrs_tlpm then we need to remove these
56 -- items from icx_cat_items_ctx_hdrs_tlp and icx_cat_items_ctx_dtls_tlp
57
58 l_err_loc := 300;
59 OPEN l_masterItem_csr FOR
60 SELECT /*+ LEADING(doc) */
61 doc.*,
62 nvl(ic1.rt_category_id, -2) ip_category_id,
63 ic1.category_name ip_category_name,
64 ctx.inventory_item_id ctx_inventory_item_id,
65 ctx.source_type ctx_source_type,
66 ctx.item_type ctx_item_type,
67 ctx.purchasing_org_id ctx_purchasing_org_id,
68 ctx.supplier_id ctx_supplier_id,
69 ctx.supplier_site_id ctx_supplier_site_id,
70 ctx.supplier_part_num ctx_supplier_part_num,
71 ctx.supplier_part_auxid ctx_supplier_part_auxid,
72 ctx.ip_category_id ctx_ip_category_id,
73 ctx.po_category_id ctx_po_category_id,
74 ctx.ip_category_name ctx_ip_category_name,
75 ROWIDTOCHAR(ctx.rowid) ctx_rowid
76 FROM
77 (
78 SELECT mi.inventory_item_id inventory_item_id,
79 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
80 TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
81 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_line_num,
82 NVL(fsp.org_id, -2) org_id,
83 mitl.language,
84 'MASTER_ITEM' source_type,
85 NVL(fsp.org_id, -2) purchasing_org_id,
86 mic.category_id po_category_id,
87 catMap.category_key category_key,
88 mi.internal_order_enabled_flag,
89 mi.purchasing_enabled_flag,
90 mi.outside_operation_flag,
91 muom.unit_of_measure unit_meas_lookup_code,
92 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
93 mi.rfq_required_flag,
94 mitl.description,
95 substr(mitl.long_description,1,2000),
96 mparams.organization_id,
97 mparams.master_organization_id
98 FROM mtl_system_items_b mi,
99 mtl_parameters mparams,
100 mtl_system_items_tl mitl,
101 mtl_item_categories mic,
102 mtl_units_of_measure muom,
103 financials_system_params_all fsp,
104 icx_por_category_data_sources catMap
105 WHERE mi.inventory_item_id = p_inventory_item_id
106 AND mi.organization_id = mparams.organization_id
107 AND (mparams.organization_id = p_organization_id
108 OR mparams.master_organization_id = p_organization_id)
109 AND mi.inventory_item_id = mitl.inventory_item_id
110 AND mi.organization_id = mitl.organization_id
111 AND mitl.language = mitl.source_lang
112 AND mic.inventory_item_id (+) = mi.inventory_item_id
113 AND mic.organization_id (+) = mi.organization_id
114 AND mic.category_set_id (+) = ICX_CAT_UTIL_PVT.g_category_set_id
115 AND muom.uom_code = mi.primary_uom_code
116 AND NOT (mi.replenish_to_order_flag = 'Y'
117 AND mi.base_item_id IS NOT NULL
118 AND mi.auto_created_config_flag = 'Y')
119 AND mi.organization_id = fsp.inventory_organization_id
120 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
121 AND catMap.external_source (+) = 'Oracle'
122 ) doc,
123 icx_cat_categories_tl ic1,
124 icx_cat_items_ctx_hdrs_tlp ctx
125 WHERE ic1.key (+) = doc.category_key
126 AND ic1.type (+) = 2
127 AND ic1.language (+) = doc.language
128 AND doc.inventory_item_id = ctx.inventory_item_id (+)
129 AND doc.po_line_id = ctx.po_line_id (+)
130 AND doc.req_template_name = ctx.req_template_name (+)
131 AND doc.req_template_line_num = ctx.req_template_line_num (+)
132 AND doc.org_id = ctx.org_id (+)
133 AND doc.language = ctx.language (+)
134 AND doc.source_type = ctx.source_type (+) ;
135
136 l_err_loc := 500;
137 populateMIs(l_masterItem_csr, ICX_CAT_UTIL_PVT.g_online_const);
138
139 l_err_loc := 600;
140 CLOSE l_masterItem_csr;
141
142 l_err_loc := 700;
143 EXCEPTION
144 WHEN OTHERS THEN
145 ICX_CAT_UTIL_PVT.logUnexpectedException(
146 G_PKG_NAME, l_api_name,
147 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
148 RAISE;
149 END openOnlineItemCatgDeleteCursor;
150
151 PROCEDURE openOnlineItemChangeCursor
152 ( P_INVENTORY_ITEM_ID IN NUMBER ,
153 P_ORGANIZATION_ID IN NUMBER
154 )
155 IS
156 l_api_name CONSTANT VARCHAR2(30) := 'openOnlineItemChangeCursor';
157 l_err_loc PLS_INTEGER;
158 l_masterItem_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
159 BEGIN
160 l_err_loc := 100;
161 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
162 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
163 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
164 'Processing cursor:' || l_api_name ||
165 ', g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id ||
166 ', g_NULL_NUMBER:' || ICX_CAT_UTIL_PVT.g_NULL_NUMBER ||
167 ', P_INVENTORY_ITEM_ID:' || P_INVENTORY_ITEM_ID ||
168 ', P_ORGANIZATION_ID:' || P_ORGANIZATION_ID );
169 END IF;
170
171 l_err_loc := 150;
172 --First close the cursor
173 IF (l_masterItem_csr%ISOPEN) THEN
174 l_err_loc := 200;
175 CLOSE l_masterItem_csr;
176 END IF;
177
178 -- Need to consider the following
179 -- 1. populateItemChange with and without master org flag
180 -- Will use one cursor by joining with mtl_parameters and adding the following where clause
181 /*
182 AND mi.organization_id = mparams.organization_id
183 AND mi.inventory_item_id = p_inventory_item_id
184 AND (mparams.organization_id = p_organization_id
185 OR mparams.master_organization_id = p_organization_id)
186 */
187
188 l_err_loc := 300;
189 OPEN l_masterItem_csr FOR
190 SELECT /*+ LEADING(doc) */
191 doc.*,
192 nvl(ic1.rt_category_id, -2) ip_category_id,
193 ic1.category_name ip_category_name,
194 ctx.inventory_item_id ctx_inventory_item_id,
195 ctx.source_type ctx_source_type,
196 ctx.item_type ctx_item_type,
197 ctx.purchasing_org_id ctx_purchasing_org_id,
198 ctx.supplier_id ctx_supplier_id,
199 ctx.supplier_site_id ctx_supplier_site_id,
200 ctx.supplier_part_num ctx_supplier_part_num,
201 ctx.supplier_part_auxid ctx_supplier_part_auxid,
202 ctx.ip_category_id ctx_ip_category_id,
203 ctx.po_category_id ctx_po_category_id,
204 ctx.ip_category_name ctx_ip_category_name,
205 ROWIDTOCHAR(ctx.rowid) ctx_rowid
206 FROM
207 (
208 SELECT mi.inventory_item_id inventory_item_id,
209 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
210 TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
211 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_line_num,
212 NVL(fsp.org_id, -2) org_id,
213 mitl.language,
214 'MASTER_ITEM' source_type,
215 NVL(fsp.org_id, -2) purchasing_org_id,
216 mic.category_id po_category_id,
217 catMap.category_key category_key,
218 mi.internal_order_enabled_flag,
219 mi.purchasing_enabled_flag,
220 mi.outside_operation_flag,
221 muom.unit_of_measure unit_meas_lookup_code,
222 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
223 mi.rfq_required_flag,
224 mitl.description,
225 substr(mitl.long_description,1,2000),
226 mparams.organization_id,
227 mparams.master_organization_id
228 FROM mtl_system_items_b mi,
229 mtl_parameters mparams,
230 mtl_system_items_tl mitl,
231 mtl_item_categories mic,
232 mtl_units_of_measure muom,
233 financials_system_params_all fsp,
234 icx_por_category_data_sources catMap
235 WHERE mi.inventory_item_id = p_inventory_item_id
236 AND mi.organization_id = mparams.organization_id
237 AND (mparams.organization_id = p_organization_id
238 OR mparams.master_organization_id = p_organization_id)
239 AND mi.inventory_item_id = mitl.inventory_item_id
240 AND mi.organization_id = mitl.organization_id
241 AND mitl.language = mitl.source_lang
242 AND mic.inventory_item_id = mi.inventory_item_id
243 AND mic.organization_id = mi.organization_id
244 AND mic.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
245 AND muom.uom_code = mi.primary_uom_code
246 AND NOT (mi.replenish_to_order_flag = 'Y'
247 AND mi.base_item_id IS NOT NULL
248 AND mi.auto_created_config_flag = 'Y')
249 AND mi.organization_id = fsp.inventory_organization_id
250 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
251 AND catMap.external_source (+) = 'Oracle'
252 ) doc,
253 icx_cat_categories_tl ic1,
254 icx_cat_items_ctx_hdrs_tlp ctx
255 WHERE ic1.key (+) = doc.category_key
256 AND ic1.type (+) = 2
257 AND ic1.language (+) = doc.language
258 AND doc.inventory_item_id = ctx.inventory_item_id (+)
259 AND doc.po_line_id = ctx.po_line_id (+)
260 AND doc.req_template_name = ctx.req_template_name (+)
261 AND doc.req_template_line_num = ctx.req_template_line_num (+)
262 AND doc.org_id = ctx.org_id (+)
263 AND doc.language = ctx.language (+)
264 AND doc.source_type = ctx.source_type (+) ;
265
266 l_err_loc := 500;
267 populateMIs(l_masterItem_csr, ICX_CAT_UTIL_PVT.g_online_const);
268
269 l_err_loc := 600;
270 CLOSE l_masterItem_csr;
271
272 l_err_loc := 700;
273 EXCEPTION
274 WHEN OTHERS THEN
275 ICX_CAT_UTIL_PVT.logUnexpectedException(
276 G_PKG_NAME, l_api_name,
277 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
278 RAISE;
279 END openOnlineItemChangeCursor;
280
281 PROCEDURE openBulkItemChangeCursor
282 ( P_INVENTORY_ITEM_ID IN NUMBER ,
283 P_REQUEST_ID IN NUMBER ,
284 P_ENTITY_TYPE IN VARCHAR2
285 ) IS
286 l_api_name CONSTANT VARCHAR2(30) := 'openBulkItemChangeCursor';
287 l_err_loc PLS_INTEGER;
288 l_masterItem_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
289 BEGIN
290 l_err_loc := 100;
291 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
292 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
293 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
294 'Processing cursor:' || l_api_name ||
295 ', g_NULL_NUMBER:' || ICX_CAT_UTIL_PVT.g_NULL_NUMBER ||
296 ', g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id ||
297 ', P_INVENTORY_ITEM_ID:' || P_INVENTORY_ITEM_ID ||
298 ', P_REQUEST_ID:' || P_REQUEST_ID ||
299 ', P_ENTITY_TYPE:' || P_ENTITY_TYPE );
300 END IF;
301
302 l_err_loc := 150;
303 --First close the cursor
304 IF (l_masterItem_csr%ISOPEN) THEN
305 l_err_loc := 200;
306 CLOSE l_masterItem_csr;
307 END IF;
308
309 -- Need to consider the following
310 -- 1. populateBulkItemChange when bulk updation done to item / category
311 -- Join with MTL_ITEM_BULKLOAD_RECS to get the changed
312 -- inventory_item_id and organization_id for the request_id passed in
313
314 l_err_loc := 300;
315 IF (P_ENTITY_TYPE = 'ITEM') THEN
316 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
317 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
318 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
319 'Inside if of P_ENTITY_TYPE = ITEM');
320 END IF;
321
322 OPEN l_masterItem_csr FOR
323 SELECT /*+ LEADING(doc) */
324 doc.*,
325 nvl(ic1.rt_category_id, -2) ip_category_id,
326 ic1.category_name ip_category_name,
327 ctx.inventory_item_id ctx_inventory_item_id,
328 ctx.source_type ctx_source_type,
329 ctx.item_type ctx_item_type,
330 ctx.purchasing_org_id ctx_purchasing_org_id,
331 ctx.supplier_id ctx_supplier_id,
332 ctx.supplier_site_id ctx_supplier_site_id,
333 ctx.supplier_part_num ctx_supplier_part_num,
334 ctx.supplier_part_auxid ctx_supplier_part_auxid,
335 ctx.ip_category_id ctx_ip_category_id,
336 ctx.po_category_id ctx_po_category_id,
337 ctx.ip_category_name ctx_ip_category_name,
338 ROWIDTOCHAR(ctx.rowid) ctx_rowid
339 FROM
340 (
341 SELECT mi.inventory_item_id inventory_item_id,
342 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
343 TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
344 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_line_num,
345 NVL(fsp.org_id, -2) org_id,
346 mitl.language,
347 'MASTER_ITEM' source_type,
348 NVL(fsp.org_id, -2) purchasing_org_id,
349 mic.category_id po_category_id,
350 catMap.category_key category_key,
351 mi.internal_order_enabled_flag,
352 mi.purchasing_enabled_flag,
353 mi.outside_operation_flag,
354 muom.unit_of_measure unit_meas_lookup_code,
355 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
356 mi.rfq_required_flag,
357 mitl.description,
358 substr(mitl.long_description,1,2000),
359 mparams.organization_id,
360 mparams.master_organization_id
361 FROM mtl_system_items_b mi,
362 mtl_parameters mparams,
363 mtl_item_bulkload_recs mbulk,
364 mtl_system_items_tl mitl,
365 mtl_item_categories mic,
366 mtl_units_of_measure muom,
367 financials_system_params_all fsp,
368 icx_por_category_data_sources catMap
369 WHERE mbulk.request_id = p_request_id
370 AND mbulk.entity_type = p_entity_type
371 AND mbulk.inventory_item_id >= p_inventory_item_id
372 AND mbulk.inventory_item_id = mi.inventory_item_id
373 AND mbulk.organization_id = mi.organization_id
374 AND mi.organization_id = mparams.organization_id
375 AND mi.inventory_item_id = mitl.inventory_item_id
376 AND mi.organization_id = mitl.organization_id
377 AND mitl.language = mitl.source_lang
378 AND mic.inventory_item_id = mi.inventory_item_id
379 AND mic.organization_id = mi.organization_id
380 AND mic.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
381 AND muom.uom_code = mi.primary_uom_code
382 AND NOT (mi.replenish_to_order_flag = 'Y'
383 AND mi.base_item_id IS NOT NULL
384 AND mi.auto_created_config_flag = 'Y')
385 AND mi.organization_id = fsp.inventory_organization_id
386 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
387 AND catMap.external_source (+) = 'Oracle'
388 ) doc,
389 icx_cat_categories_tl ic1,
390 icx_cat_items_ctx_hdrs_tlp ctx
391 WHERE ic1.key (+) = doc.category_key
392 AND ic1.type (+) = 2
393 AND ic1.language (+) = doc.language
394 AND doc.inventory_item_id = ctx.inventory_item_id (+)
395 AND doc.po_line_id = ctx.po_line_id (+)
396 AND doc.req_template_name = ctx.req_template_name (+)
397 AND doc.req_template_line_num = ctx.req_template_line_num (+)
398 AND doc.org_id = ctx.org_id (+)
399 AND doc.language = ctx.language (+)
400 AND doc.source_type = ctx.source_type (+)
401 ORDER by doc.inventory_item_id;
402
403 /* Bug 6900901: During item import, when the category set are controlled at
404 * master org level, mtl_item_bulkload_recs is only one record corresponding to
405 * master org, even if items are imported for all orgs. so, the inventory org is derived
406 * from master_org using the following condition.
407 * mbulk.category_set_id = msets.category_set_id
408 * AND msets.control_level = 1 -- Controlled at Master org level
409 * AND mbulk.organization_id = mparams.master_organization_id
410 * AND mparams.organization_id = mi.organization_id
411 * AND mi.organization_id = fsp.inventory_organization_id
412 */
413 ELSIF (P_ENTITY_TYPE = 'ITEM_CATEGORY') THEN
414 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
415 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
416 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
417 'Inside else of P_ENTITY_TYPE = ITEM_CATEGORY');
418 END IF;
419 OPEN l_masterItem_csr FOR
420 SELECT * FROM (
421 SELECT /*+ LEADING(doc) */
422 doc.*,
423 nvl(ic1.rt_category_id, -2) ip_category_id,
424 ic1.category_name ip_category_name,
425 ctx.inventory_item_id ctx_inventory_item_id,
426 ctx.source_type ctx_source_type,
427 ctx.item_type ctx_item_type,
428 ctx.purchasing_org_id ctx_purchasing_org_id,
429 ctx.supplier_id ctx_supplier_id,
430 ctx.supplier_site_id ctx_supplier_site_id,
431 ctx.supplier_part_num ctx_supplier_part_num,
432 ctx.supplier_part_auxid ctx_supplier_part_auxid,
433 ctx.ip_category_id ctx_ip_category_id,
434 ctx.po_category_id ctx_po_category_id,
435 ctx.ip_category_name ctx_ip_category_name,
436 ROWIDTOCHAR(ctx.rowid) ctx_rowid
437 FROM
438 (
439 SELECT mi.inventory_item_id inventory_item_id,
440 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
441 TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
442 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_line_num,
443 NVL(fsp.org_id, -2) org_id,
444 mitl.language,
445 'MASTER_ITEM' source_type,
446 NVL(fsp.org_id, -2) purchasing_org_id,
447 mic.category_id po_category_id,
448 catMap.category_key category_key,
449 mi.internal_order_enabled_flag,
450 mi.purchasing_enabled_flag,
451 mi.outside_operation_flag,
452 muom.unit_of_measure unit_meas_lookup_code,
453 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
454 mi.rfq_required_flag,
455 mitl.description,
456 substr(mitl.long_description,1,2000),
457 mparams.organization_id,
458 mparams.master_organization_id
459 FROM mtl_system_items_b mi,
460 mtl_parameters mparams,
461 mtl_item_bulkload_recs mbulk,
462 mtl_system_items_tl mitl,
463 mtl_item_categories mic,
464 mtl_units_of_measure muom,
465 mtl_category_sets msets,
466 financials_system_params_all fsp,
467 icx_por_category_data_sources catMap
468 WHERE mbulk.request_id = p_request_id
469 AND mbulk.entity_type = p_entity_type
470 AND mbulk.inventory_item_id >= p_inventory_item_id
471 AND mbulk.inventory_item_id = mi.inventory_item_id
472 AND mbulk.category_set_id = msets.category_set_id
473 AND msets.control_level = 1 -- Controlled at Master org level
474 AND mbulk.organization_id = mparams.master_organization_id
475 AND mparams.organization_id = mi.organization_id
476 AND mi.inventory_item_id = mitl.inventory_item_id
477 AND mi.organization_id = mitl.organization_id
478 AND mitl.language = mitl.source_lang
479 AND mic.inventory_item_id = mi.inventory_item_id
480 AND mic.organization_id = mi.organization_id
481 AND mic.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
482 AND muom.uom_code = mi.primary_uom_code
483 AND NOT (mi.replenish_to_order_flag = 'Y'
484 AND mi.base_item_id IS NOT NULL
485 AND mi.auto_created_config_flag = 'Y')
486 AND mi.organization_id = fsp.inventory_organization_id
487 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
488 AND catMap.external_source (+) = 'Oracle'
489 ) doc,
490 icx_cat_categories_tl ic1,
491 icx_cat_items_ctx_hdrs_tlp ctx
492 WHERE ic1.key (+) = doc.category_key
493 AND ic1.type (+) = 2
494 AND ic1.language (+) = doc.language
495 AND doc.inventory_item_id = ctx.inventory_item_id (+)
496 AND doc.po_line_id = ctx.po_line_id (+)
497 AND doc.req_template_name = ctx.req_template_name (+)
498 AND doc.req_template_line_num = ctx.req_template_line_num (+)
499 AND doc.org_id = ctx.org_id (+)
500 AND doc.language = ctx.language (+)
501 AND doc.source_type = ctx.source_type (+)
502 UNION ALL
503 SELECT /*+ LEADING(doc) */
504 doc.*,
505 nvl(ic1.rt_category_id, -2) ip_category_id,
506 ic1.category_name ip_category_name,
507 ctx.inventory_item_id ctx_inventory_item_id,
508 ctx.source_type ctx_source_type,
509 ctx.item_type ctx_item_type,
510 ctx.purchasing_org_id ctx_purchasing_org_id,
511 ctx.supplier_id ctx_supplier_id,
512 ctx.supplier_site_id ctx_supplier_site_id,
513 ctx.supplier_part_num ctx_supplier_part_num,
514 ctx.supplier_part_auxid ctx_supplier_part_auxid,
515 ctx.ip_category_id ctx_ip_category_id,
516 ctx.po_category_id ctx_po_category_id,
517 ctx.ip_category_name ctx_ip_category_name,
518 ROWIDTOCHAR(ctx.rowid) ctx_rowid
519 FROM
520 (
521 SELECT mi.inventory_item_id inventory_item_id,
522 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
523 TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
524 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_line_num,
525 NVL(fsp.org_id, -2) org_id,
526 mitl.language,
527 'MASTER_ITEM' source_type,
528 NVL(fsp.org_id, -2) purchasing_org_id,
529 mic.category_id po_category_id,
530 catMap.category_key category_key,
531 mi.internal_order_enabled_flag,
532 mi.purchasing_enabled_flag,
533 mi.outside_operation_flag,
534 muom.unit_of_measure unit_meas_lookup_code,
535 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
536 mi.rfq_required_flag,
537 mitl.description,
538 substr(mitl.long_description,1,2000),
539 mparams.organization_id,
540 mparams.master_organization_id
541 FROM mtl_system_items_b mi,
542 mtl_parameters mparams,
543 mtl_item_bulkload_recs mbulk,
544 mtl_system_items_tl mitl,
545 mtl_item_categories mic,
546 mtl_units_of_measure muom,
547 mtl_category_sets msets,
548 financials_system_params_all fsp,
549 icx_por_category_data_sources catMap
550 WHERE mbulk.request_id = p_request_id
551 AND mbulk.entity_type = p_entity_type
552 AND mbulk.inventory_item_id >= p_inventory_item_id
553 AND mbulk.inventory_item_id = mi.inventory_item_id
554 AND mbulk.category_set_id = msets.category_set_id
555 AND msets.control_level =2 -- Controlled at item org level
556 AND mbulk.organization_id = mi.organization_id
557 AND mi.organization_id = mparams.organization_id
558 AND mi.inventory_item_id = mitl.inventory_item_id
559 AND mi.organization_id = mitl.organization_id
560 AND mitl.language = mitl.source_lang
561 AND mic.inventory_item_id = mi.inventory_item_id
562 AND mic.organization_id = mi.organization_id
563 AND mic.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
564 AND muom.uom_code = mi.primary_uom_code
565 AND NOT (mi.replenish_to_order_flag = 'Y'
566 AND mi.base_item_id IS NOT NULL
567 AND mi.auto_created_config_flag = 'Y')
568 AND mi.organization_id = fsp.inventory_organization_id
569 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
570 AND catMap.external_source (+) = 'Oracle'
571 ) doc,
572 icx_cat_categories_tl ic1,
573 icx_cat_items_ctx_hdrs_tlp ctx
574 WHERE ic1.key (+) = doc.category_key
575 AND ic1.type (+) = 2
576 AND ic1.language (+) = doc.language
577 AND doc.inventory_item_id = ctx.inventory_item_id (+)
578 AND doc.po_line_id = ctx.po_line_id (+)
579 AND doc.req_template_name = ctx.req_template_name (+)
580 AND doc.req_template_line_num = ctx.req_template_line_num (+)
581 AND doc.org_id = ctx.org_id (+)
582 AND doc.language = ctx.language (+)
583 AND doc.source_type = ctx.source_type (+)
584 ) doc1 ORDER by doc1.inventory_item_id;
585 END IF;
586 l_err_loc := 500;
587 populateMIs(l_masterItem_csr, ICX_CAT_UTIL_PVT.g_online_const);
588
589 l_err_loc := 600;
590 CLOSE l_masterItem_csr;
591
592 l_err_loc := 700;
593 EXCEPTION
594 WHEN OTHERS THEN
595 ICX_CAT_UTIL_PVT.logUnexpectedException(
596 G_PKG_NAME, l_api_name,
597 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
598 RAISE;
599 END openBulkItemChangeCursor;
600
601 PROCEDURE openCategoryItemsCursor
602 ( p_mtl_category_id IN NUMBER ,
603 p_inventory_item_id IN NUMBER
604 )
605 IS
606 l_api_name CONSTANT VARCHAR2(30) := 'openCategoryItemsCursor';
607 l_err_loc PLS_INTEGER;
608 l_masterItem_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
609 BEGIN
610 l_err_loc := 100;
611 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
612 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
613 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
614 'Processing cursor:' || l_api_name ||
615 ', g_NULL_NUMBER:' || ICX_CAT_UTIL_PVT.g_NULL_NUMBER ||
616 ', g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id ||
617 ', P_INVENTORY_ITEM_ID:' || P_INVENTORY_ITEM_ID ||
618 ', p_mtl_category_id:' || p_mtl_category_id );
619 END IF;
620
621 l_err_loc := 150;
622 --First close the cursor
623 IF (l_masterItem_csr%ISOPEN) THEN
624 l_err_loc := 200;
625 CLOSE l_masterItem_csr;
626 END IF;
627
628 l_err_loc := 300;
629 OPEN l_masterItem_csr FOR
630 SELECT /*+ LEADING(doc) */
631 doc.*,
632 nvl(ic1.rt_category_id, -2) ip_category_id,
633 ic1.category_name ip_category_name,
634 ctx.inventory_item_id ctx_inventory_item_id,
635 ctx.source_type ctx_source_type,
636 ctx.item_type ctx_item_type,
637 ctx.purchasing_org_id ctx_purchasing_org_id,
638 ctx.supplier_id ctx_supplier_id,
639 ctx.supplier_site_id ctx_supplier_site_id,
640 ctx.supplier_part_num ctx_supplier_part_num,
641 ctx.supplier_part_auxid ctx_supplier_part_auxid,
642 ctx.ip_category_id ctx_ip_category_id,
643 ctx.po_category_id ctx_po_category_id,
644 ctx.ip_category_name ctx_ip_category_name,
645 ROWIDTOCHAR(ctx.rowid) ctx_rowid
646 FROM
647 (
648 SELECT mi.inventory_item_id inventory_item_id,
649 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
650 TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
651 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_line_num,
652 NVL(fsp.org_id, -2) org_id,
653 mitl.language,
654 'MASTER_ITEM' source_type,
655 NVL(fsp.org_id, -2) purchasing_org_id,
656 mic.category_id po_category_id,
657 catMap.category_key category_key,
658 mi.internal_order_enabled_flag,
659 mi.purchasing_enabled_flag,
660 mi.outside_operation_flag,
661 muom.unit_of_measure unit_meas_lookup_code,
662 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
663 mi.rfq_required_flag,
664 mitl.description,
665 substr(mitl.long_description,1,2000),
666 mparams.organization_id,
667 mparams.master_organization_id
668 FROM mtl_system_items_b mi,
669 mtl_parameters mparams,
670 mtl_system_items_tl mitl,
671 mtl_item_categories mic,
672 mtl_units_of_measure muom,
673 financials_system_params_all fsp,
674 icx_por_category_data_sources catMap
675 WHERE mi.inventory_item_id = mitl.inventory_item_id
676 AND mi.organization_id = mparams.organization_id
677 AND mi.organization_id = mitl.organization_id
678 AND mitl.language = mitl.source_lang
679 AND mic.inventory_item_id = mi.inventory_item_id
680 AND mic.organization_id = mi.organization_id
681 AND mic.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
682 AND mic.category_id = p_mtl_category_id
683 AND mi.inventory_item_id >= p_inventory_item_id
684 AND muom.uom_code = mi.primary_uom_code
685 AND NOT (mi.replenish_to_order_flag = 'Y'
686 AND mi.base_item_id IS NOT NULL
687 AND mi.auto_created_config_flag = 'Y')
688 AND mi.organization_id = fsp.inventory_organization_id
689 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
690 AND catMap.external_source (+) = 'Oracle'
691 ) doc,
692 icx_cat_categories_tl ic1,
693 icx_cat_items_ctx_hdrs_tlp ctx
694 WHERE ic1.key (+) = doc.category_key
695 AND ic1.type (+) = 2
696 AND ic1.language (+) = doc.language
697 AND doc.inventory_item_id = ctx.inventory_item_id (+)
698 AND doc.po_line_id = ctx.po_line_id (+)
699 AND doc.req_template_name = ctx.req_template_name (+)
700 AND doc.req_template_line_num = ctx.req_template_line_num (+)
701 AND doc.org_id = ctx.org_id (+)
702 AND doc.language = ctx.language (+)
703 AND doc.source_type = ctx.source_type (+)
704 ORDER by doc.inventory_item_id;
705
706 l_err_loc := 500;
707 populateMIs(l_masterItem_csr, ICX_CAT_UTIL_PVT.g_online_const);
708
709 l_err_loc := 600;
710 CLOSE l_masterItem_csr;
711
712 l_err_loc := 700;
713 EXCEPTION
714 WHEN OTHERS THEN
715 ICX_CAT_UTIL_PVT.logUnexpectedException(
716 G_PKG_NAME, l_api_name,
717 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
718 RAISE;
719 END openCategoryItemsCursor;
720
721 PROCEDURE openR12UpgradeMICursor
722 IS
723 l_api_name CONSTANT VARCHAR2(30) := 'openR12UpgradeMICursor';
724 l_err_loc PLS_INTEGER;
725 l_masterItem_csr ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type;
726 BEGIN
727 l_err_loc := 100;
728 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
729 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
730 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
731 'Processing cursor:' || l_api_name ||
732 ', g_upgrade_last_run_date:' || g_upgrade_last_run_date ||
733 ', g_start_rowid:' || g_start_rowid ||
734 ', g_end_rowid:' || g_end_rowid ||
735 ', g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id ||
736 ', g_NULL_NUMBER:' || ICX_CAT_UTIL_PVT.g_NULL_NUMBER );
737 END IF;
738
739 l_err_loc := 150;
740 --First close the cursor
741 IF (l_masterItem_csr%ISOPEN) THEN
742 l_err_loc := 200;
743 CLOSE l_masterItem_csr;
744 END IF;
745
746 l_err_loc := 300;
747 IF (g_upgrade_last_run_date) IS NULL THEN
748 l_err_loc := 400;
749 OPEN l_masterItem_csr FOR
750 SELECT /*+ LEADING(doc) use_nl(ic1,ctx) */
751 doc.*,
752 nvl(ic1.rt_category_id, -2) ip_category_id,
753 ic1.category_name ip_category_name,
754 ctx.inventory_item_id ctx_inventory_item_id,
755 ctx.source_type ctx_source_type,
756 ctx.item_type ctx_item_type,
757 ctx.purchasing_org_id ctx_purchasing_org_id,
758 ctx.supplier_id ctx_supplier_id,
759 ctx.supplier_site_id ctx_supplier_site_id,
760 ctx.supplier_part_num ctx_supplier_part_num,
761 ctx.supplier_part_auxid ctx_supplier_part_auxid,
762 ctx.ip_category_id ctx_ip_category_id,
763 ctx.po_category_id ctx_po_category_id,
764 ctx.ip_category_name ctx_ip_category_name,
765 ROWIDTOCHAR(ctx.rowid) ctx_rowid
766 FROM
767 (
768 SELECT /*+ ROWID(mi) use_nl(mitl,mic,catMap) */
769 mi.inventory_item_id inventory_item_id,
770 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
771 TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
772 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_line_num,
773 NVL(fsp.org_id, -2) org_id,
774 mitl.language,
775 'MASTER_ITEM' source_type,
776 NVL(fsp.org_id, -2) purchasing_org_id,
777 mic.category_id po_category_id,
778 catMap.category_key category_key,
779 mi.internal_order_enabled_flag,
780 mi.purchasing_enabled_flag,
781 mi.outside_operation_flag,
782 muom.unit_of_measure unit_meas_lookup_code,
783 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
784 mi.rfq_required_flag,
785 mitl.description,
786 substr(mitl.long_description,1,2000),
787 mparams.organization_id,
788 mparams.master_organization_id
789 FROM mtl_system_items_b mi,
790 mtl_parameters mparams,
791 mtl_system_items_tl mitl,
792 mtl_item_categories mic,
793 mtl_units_of_measure muom,
794 financials_system_params_all fsp,
795 icx_por_category_data_sources catMap
796 WHERE mi.inventory_item_id = mitl.inventory_item_id
797 AND mi.organization_id = mparams.organization_id
798 AND mi.organization_id = mitl.organization_id
799 AND mitl.language = mitl.source_lang
800 AND mic.inventory_item_id = mi.inventory_item_id
801 AND mic.organization_id = mi.organization_id
802 AND mic.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
803 AND muom.uom_code = mi.primary_uom_code
804 AND NOT (mi.replenish_to_order_flag = 'Y'
805 AND mi.base_item_id IS NOT NULL
806 AND mi.auto_created_config_flag = 'Y')
807 AND mi.organization_id = fsp.inventory_organization_id
808 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
809 AND catMap.external_source (+) = 'Oracle'
810 AND mi.rowid BETWEEN g_start_rowid and g_end_rowid
811 ) doc,
812 icx_cat_categories_tl ic1,
813 icx_cat_items_ctx_hdrs_tlp ctx
814 WHERE ic1.key (+) = doc.category_key
815 AND ic1.type (+) = 2
816 AND ic1.language (+) = doc.language
817 AND doc.inventory_item_id = ctx.inventory_item_id (+)
818 AND doc.po_line_id = ctx.po_line_id (+)
819 AND doc.req_template_name = ctx.req_template_name (+)
820 AND doc.req_template_line_num = ctx.req_template_line_num (+)
821 AND doc.org_id = ctx.org_id (+)
822 AND doc.language = ctx.language (+)
823 AND doc.source_type = ctx.source_type (+);
824 ELSE
825 l_err_loc := 500;
826 OPEN l_masterItem_csr FOR
827 SELECT /*+ LEADING(doc) use_nl(ic1,ctx) */
828 doc.*,
829 nvl(ic1.rt_category_id, -2) ip_category_id,
830 ic1.category_name ip_category_name,
831 ctx.inventory_item_id ctx_inventory_item_id,
832 ctx.source_type ctx_source_type,
833 ctx.item_type ctx_item_type,
834 ctx.purchasing_org_id ctx_purchasing_org_id,
835 ctx.supplier_id ctx_supplier_id,
836 ctx.supplier_site_id ctx_supplier_site_id,
837 ctx.supplier_part_num ctx_supplier_part_num,
838 ctx.supplier_part_auxid ctx_supplier_part_auxid,
839 ctx.ip_category_id ctx_ip_category_id,
840 ctx.po_category_id ctx_po_category_id,
841 ctx.ip_category_name ctx_ip_category_name,
842 ROWIDTOCHAR(ctx.rowid) ctx_rowid
843 FROM
844 (
845 SELECT /*+ ROWID(mi) use_nl(mitl,mic,catMap) */
846 mi.inventory_item_id inventory_item_id,
847 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) po_line_id,
848 TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_name,
849 TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER) req_template_line_num,
850 NVL(fsp.org_id, -2) org_id,
851 mitl.language,
852 'MASTER_ITEM' source_type,
853 NVL(fsp.org_id, -2) purchasing_org_id,
854 mic.category_id po_category_id,
855 catMap.category_key category_key,
856 mi.internal_order_enabled_flag,
857 mi.purchasing_enabled_flag,
858 mi.outside_operation_flag,
859 muom.unit_of_measure unit_meas_lookup_code,
860 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
861 mi.rfq_required_flag,
862 mitl.description,
863 substr(mitl.long_description,1,2000),
864 mparams.organization_id,
865 mparams.master_organization_id
866 FROM mtl_system_items_b mi,
867 mtl_parameters mparams,
868 mtl_system_items_tl mitl,
869 mtl_item_categories mic,
870 mtl_units_of_measure muom,
871 financials_system_params_all fsp,
872 icx_por_category_data_sources catMap
873 WHERE mi.inventory_item_id = mitl.inventory_item_id
874 AND mi.organization_id = mparams.organization_id
875 AND mi.organization_id = mitl.organization_id
876 AND mitl.language = mitl.source_lang
877 AND mic.inventory_item_id = mi.inventory_item_id
878 AND mic.organization_id = mi.organization_id
879 AND mic.category_set_id = ICX_CAT_UTIL_PVT.g_category_set_id
880 AND muom.uom_code = mi.primary_uom_code
881 AND NOT (mi.replenish_to_order_flag = 'Y'
882 AND mi.base_item_id IS NOT NULL
883 AND mi.auto_created_config_flag = 'Y')
884 AND mi.organization_id = fsp.inventory_organization_id
885 AND (mi.last_update_date > g_upgrade_last_run_date
886 OR mitl.last_update_date > g_upgrade_last_run_date
887 OR mic.last_update_date > g_upgrade_last_run_date)
888 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
889 AND catMap.external_source (+) = 'Oracle'
890 AND mi.rowid BETWEEN g_start_rowid and g_end_rowid
891 ) doc,
892 icx_cat_categories_tl ic1,
893 icx_cat_items_ctx_hdrs_tlp ctx
894 WHERE ic1.key (+) = doc.category_key
895 AND ic1.type (+) = 2
896 AND ic1.language (+) = doc.language
897 AND doc.inventory_item_id = ctx.inventory_item_id (+)
898 AND doc.po_line_id = ctx.po_line_id (+)
899 AND doc.req_template_name = ctx.req_template_name (+)
900 AND doc.req_template_line_num = ctx.req_template_line_num (+)
901 AND doc.org_id = ctx.org_id (+)
902 AND doc.language = ctx.language (+)
903 AND doc.source_type = ctx.source_type (+);
904 END IF;
905
906 l_err_loc := 700;
907 populateMIs(l_masterItem_csr, ICX_CAT_UTIL_PVT.g_upgrade_const);
908
909 l_err_loc := 800;
910 CLOSE l_masterItem_csr;
911
912 l_err_loc := 900;
913
914 EXCEPTION
915 WHEN OTHERS THEN
916 ICX_CAT_UTIL_PVT.logUnexpectedException(
917 G_PKG_NAME, l_api_name,
918 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
919 RAISE;
920 END openR12UpgradeMICursor;
921
922 -- p_masterItem_csr, ICX_CAT_UTIL_PVT.g_upgrade_const
923 PROCEDURE populateMIs
924 ( p_masterItem_csr IN ICX_CAT_POPULATE_ITEM_PVT.g_item_csr_type ,
925 p_current_mode IN VARCHAR2
926 )
927 IS
928
929 l_api_name CONSTANT VARCHAR2(30) := 'populateMIs';
930 l_err_loc PLS_INTEGER;
931 l_start_inv_item_id NUMBER;
932 l_err_string VARCHAR2(4000);
933 l_batch_count PLS_INTEGER;
934 l_row_count PLS_INTEGER;
935 l_count PLS_INTEGER;
936 l_inv_item_status PLS_INTEGER;
937 l_item_type VARCHAR2(8);
938 l_current_ctx_item_rec ICX_CAT_POPULATE_ITEM_PVT.g_ctx_item_rec_type;
939
940 ----- Start of declaring columns selected in the cursor -----
941 l_inv_item_id_tbl DBMS_SQL.NUMBER_TABLE;
942 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
943 l_req_template_name_tbl DBMS_SQL.VARCHAR2_TABLE;
944 l_req_template_line_num_tbl DBMS_SQL.NUMBER_TABLE;
945 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
946 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
947 l_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
948 l_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
949 l_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
950 l_category_key_tbl DBMS_SQL.VARCHAR2_TABLE;
951 l_intrnl_order_enbld_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
952 l_purchasing_enabled_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
953 l_outside_operation_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
954 l_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
955 l_ip_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
956 l_unit_meas_lookup_code_tbl DBMS_SQL.VARCHAR2_TABLE;
957 l_unit_price_tbl DBMS_SQL.NUMBER_TABLE;
958 l_rfq_required_flag_tbl DBMS_SQL.VARCHAR2_TABLE;
959 l_description_tbl DBMS_SQL.VARCHAR2_TABLE;
960 --Bug6599217
961 l_long_description_tbl DBMS_SQL.VARCHAR2_TABLE;
962 l_organization_id_tbl DBMS_SQL.NUMBER_TABLE;
963 l_master_organization_id_tbl DBMS_SQL.NUMBER_TABLE;
964 l_ctx_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
965 l_ctx_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
966 l_ctx_item_type_tbl DBMS_SQL.VARCHAR2_TABLE;
967 l_ctx_purchasing_org_id_tbl DBMS_SQL.NUMBER_TABLE;
968 l_ctx_supplier_id_tbl DBMS_SQL.NUMBER_TABLE;
969 l_ctx_supplier_site_id_tbl DBMS_SQL.NUMBER_TABLE;
970 l_ctx_supplier_part_num_tbl DBMS_SQL.VARCHAR2_TABLE;
971 l_ctx_supplier_part_auxid_tbl DBMS_SQL.VARCHAR2_TABLE;
972 l_ctx_ip_category_id_tbl DBMS_SQL.NUMBER_TABLE;
973 l_ctx_po_category_id_tbl DBMS_SQL.NUMBER_TABLE;
974 l_ctx_ip_category_name_tbl DBMS_SQL.VARCHAR2_TABLE;
975 l_ctx_rowid_tbl DBMS_SQL.UROWID_TABLE;
976 ------ End of declaring columns selected in the cursor ------
977
978 BEGIN
979 l_err_loc := 100;
980 l_batch_count := 0;
981 l_row_count := 0;
982 l_count := 0;
983 LOOP
984 l_err_loc := 200;
985 l_inv_item_id_tbl.DELETE;
986 l_po_line_id_tbl.DELETE;
987 l_req_template_name_tbl.DELETE;
988 l_req_template_line_num_tbl.DELETE;
989 l_org_id_tbl.DELETE;
990 l_language_tbl.DELETE;
991 l_source_type_tbl.DELETE;
992 l_purchasing_org_id_tbl.DELETE;
993 l_po_category_id_tbl.DELETE;
994 l_category_key_tbl.DELETE;
995 l_intrnl_order_enbld_flag_tbl.DELETE;
996 l_purchasing_enabled_flag_tbl.DELETE;
997 l_outside_operation_flag_tbl.DELETE;
998 l_ip_category_id_tbl.DELETE;
999 l_ip_category_name_tbl.DELETE;
1000 l_unit_meas_lookup_code_tbl.DELETE;
1001 l_unit_price_tbl.DELETE;
1002 l_rfq_required_flag_tbl.DELETE;
1003 l_description_tbl.DELETE;
1004 l_long_description_tbl.DELETE;
1005 l_organization_id_tbl.DELETE;
1006 l_master_organization_id_tbl.DELETE;
1007 l_ctx_inventory_item_id_tbl.DELETE;
1008 l_ctx_source_type_tbl.DELETE;
1009 l_ctx_item_type_tbl.DELETE;
1010 l_ctx_purchasing_org_id_tbl.DELETE;
1011 l_ctx_supplier_id_tbl.DELETE;
1012 l_ctx_supplier_site_id_tbl.DELETE;
1013 l_ctx_supplier_part_num_tbl.DELETE;
1014 l_ctx_supplier_part_auxid_tbl.DELETE;
1015 l_ctx_ip_category_id_tbl.DELETE;
1016 l_ctx_po_category_id_tbl.DELETE;
1017 l_ctx_ip_category_name_tbl.DELETE;
1018 l_ctx_rowid_tbl.DELETE;
1019
1020 BEGIN
1021 l_err_loc := 300;
1022 FETCH p_masterItem_csr BULK COLLECT INTO
1023 l_inv_item_id_tbl,
1024 l_po_line_id_tbl,
1025 l_req_template_name_tbl,
1026 l_req_template_line_num_tbl,
1027 l_org_id_tbl,
1028 l_language_tbl,
1029 l_source_type_tbl,
1030 l_purchasing_org_id_tbl,
1031 l_po_category_id_tbl,
1032 l_category_key_tbl,
1033 l_intrnl_order_enbld_flag_tbl,
1034 l_purchasing_enabled_flag_tbl,
1035 l_outside_operation_flag_tbl,
1036 l_unit_meas_lookup_code_tbl,
1037 l_unit_price_tbl,
1038 l_rfq_required_flag_tbl,
1039 l_description_tbl,
1040 l_long_description_tbl,
1041 l_organization_id_tbl,
1042 l_master_organization_id_tbl,
1043 l_ip_category_id_tbl,
1044 l_ip_category_name_tbl,
1045 l_ctx_inventory_item_id_tbl,
1046 l_ctx_source_type_tbl,
1047 l_ctx_item_type_tbl,
1048 l_ctx_purchasing_org_id_tbl,
1049 l_ctx_supplier_id_tbl,
1050 l_ctx_supplier_site_id_tbl,
1051 l_ctx_supplier_part_num_tbl,
1052 l_ctx_supplier_part_auxid_tbl,
1053 l_ctx_ip_category_id_tbl,
1054 l_ctx_po_category_id_tbl,
1055 l_ctx_ip_category_name_tbl,
1056 l_ctx_rowid_tbl
1057 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
1058 l_err_loc := 400;
1059 EXIT WHEN l_inv_item_id_tbl.COUNT = 0;
1060
1061 l_err_loc := 500;
1062 l_batch_count := l_batch_count + 1;
1063
1064 l_err_loc := 600;
1065 l_count := l_inv_item_id_tbl.COUNT;
1066 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1067 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1068 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1069 'Num. of rows returned from the cursor:' || l_count);
1070 END IF;
1071
1072 --Save the last inventory_item_id processed, so that re-open of cursor will start from the saved id.
1073 l_start_inv_item_id := l_inv_item_id_tbl(l_count);
1074
1075 l_row_count := l_row_count + l_count;
1076
1077 FOR i in 1..l_inv_item_id_tbl.COUNT LOOP
1078 l_err_loc := 700;
1079 -- First get the status and item_type of the current inventory item line
1080 ICX_CAT_POPULATE_STATUS_PVT.getMasterItemStatusAndType
1081 (l_intrnl_order_enbld_flag_tbl(i), l_outside_operation_flag_tbl(i), l_unit_price_tbl(i),
1082 l_inv_item_status, l_item_type);
1083 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1084 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1085 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1086 'l_inv_item_status: ' || l_inv_item_status ||
1087 'l_item_type :' || l_item_type);
1088 END IF;
1089 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1090 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1091 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1092 'Values for Variables in populateMIs ' ||
1093 'l_inv_item_id: ' || l_inv_item_id_tbl(i) ||
1094 ',l_po_line_id: ' || l_po_line_id_tbl(i) ||
1095 ',l_req_template_name: ' || l_req_template_name_tbl(i) ||
1096 ',l_req_template_line_num: ' || l_req_template_line_num_tbl(i) ||
1097 ',l_org_id: ' || l_org_id_tbl(i) ||
1098 ',l_language: ' || l_language_tbl(i) ||
1099 ',l_source_type: ' || l_source_type_tbl(i) ||
1100 ',l_purchasing_org_id: ' || l_purchasing_org_id_tbl(i) ||
1101 ',l_po_category_id: ' || l_po_category_id_tbl(i) ||
1102 ',l_category_key: ' || l_category_key_tbl(i) ||
1103 ',l_intrnl_order_enbld_flag: ' || l_intrnl_order_enbld_flag_tbl(i) ||
1104 ',l_purchasing_enabled_flag: ' || l_purchasing_enabled_flag_tbl(i) ||
1105 ',l_outside_operation_flag: ' || l_outside_operation_flag_tbl(i) ||
1106 ',l_unit_meas_lookup_code: ' || l_unit_meas_lookup_code_tbl(i) ||
1107 ',l_unit_price: ' || l_unit_price_tbl(i) ||
1108 ',l_rfq_required_flag: ' || l_rfq_required_flag_tbl(i) ||
1109 ',l_description: ' || l_description_tbl(i) ||
1110 ',l_long_description: ' || l_long_description_tbl(i) ||
1111 ',l_organization_id: ' || l_organization_id_tbl(i) ||
1112 ',l_master_organization_id: ' || l_master_organization_id_tbl(i) ||
1113 ',l_ip_category_id: ' || l_ip_category_id_tbl(i) ||
1114 ',l_ip_category_name: ' || l_ip_category_name_tbl(i) ||
1115 ',l_ctx_inventory_item_id: ' || l_ctx_inventory_item_id_tbl(i) ||
1116 ',l_ctx_source_type: ' || l_ctx_source_type_tbl(i) ||
1117 ',l_ctx_item_type: ' || l_ctx_item_type_tbl(i) ||
1118 ',l_ctx_purchasing_org_id: ' || l_ctx_purchasing_org_id_tbl(i) ||
1119 ',l_ctx_supplier_id: ' || l_ctx_supplier_id_tbl(i) ||
1120 ',l_ctx_supplier_site_id: ' || l_ctx_supplier_site_id_tbl(i) ||
1121 ',l_ctx_supplier_part_num: ' || l_ctx_supplier_part_num_tbl(i) ||
1122 ',l_ctx_supplier_part_auxid: ' || l_ctx_supplier_part_auxid_tbl(i) ||
1123 ',l_ctx_ip_category_id: ' || l_ctx_ip_category_id_tbl(i) ||
1124 ',l_ctx_po_category_id: ' || l_ctx_po_category_id_tbl(i) ||
1125 ',l_ctx_ip_category_name: ' || l_ctx_ip_category_name_tbl(i));
1126 END IF;
1127
1128
1129
1130 l_err_loc := 800;
1131 -- For category assignment delete
1132 IF (l_po_category_id_tbl(i) IS NULL) THEN
1133 l_err_loc := 900;
1134 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1135 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1136 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1137 'null l_po_category_id:' || l_po_category_id_tbl(i) ||
1138 'for l_inv_item_id_tbl:' || l_inv_item_id_tbl(i) ||
1139 ', l_org_id_tbl:' || l_org_id_tbl(i));
1140 END IF;
1141 l_inv_item_status := ICX_CAT_POPULATE_STATUS_PVT.INVALID_ITEM_CATG_ASIGNMNT;
1142 END IF;
1143
1144 l_err_loc := 1000;
1145 --BUG 6599217: commented to allow the updations on ctx tables via call processCurrentCtxItemRow(
1146 IF (--l_ctx_rowid_tbl(i) IS NOT NULL OR
1147 l_inv_item_status = ICX_CAT_POPULATE_STATUS_PVT.VALID_FOR_POPULATE)
1148 THEN
1149 l_err_loc := 1100;
1150 l_current_ctx_item_rec.inventory_item_id := l_inv_item_id_tbl(i);
1151 l_current_ctx_item_rec.po_line_id := l_po_line_id_tbl(i);
1152 l_current_ctx_item_rec.req_template_name := l_req_template_name_tbl(i);
1153 l_current_ctx_item_rec.req_template_line_num := l_req_template_line_num_tbl(i);
1154 l_current_ctx_item_rec.org_id := l_org_id_tbl(i);
1155 l_current_ctx_item_rec.language := l_language_tbl(i);
1156 l_current_ctx_item_rec.source_type := l_source_type_tbl(i);
1157 l_current_ctx_item_rec.item_type := l_item_type;
1158 l_current_ctx_item_rec.purchasing_org_id := l_purchasing_org_id_tbl(i);
1159 l_current_ctx_item_rec.owning_org_id := l_org_id_tbl(i);
1160 l_current_ctx_item_rec.supplier_id := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
1161 l_current_ctx_item_rec.supplier_part_num := '##NULL##';
1162 l_current_ctx_item_rec.supplier_part_auxid := '##NULL##';
1163 l_current_ctx_item_rec.supplier_site_id := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
1164 l_current_ctx_item_rec.status := l_inv_item_status;
1165 -- Ignore category changes due to mapping if this was online item update
1166 -- Honor category changes due to mapping during upgrade, online item category change
1167 -- and online item create.
1168 IF (l_ctx_rowid_tbl(i) IS NOT NULL
1169 AND p_current_mode = ICX_CAT_UTIL_PVT.g_online_const
1170 AND NOT ICX_CAT_UTIL_PVT.g_ItemCatgChange_const)
1171 THEN
1172 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1173 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1174 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1175 'Not changing the po category and ip category for' ||
1176 ', l_inv_item_id_tbl:' || l_inv_item_id_tbl(i) ||
1177 ', l_org_id_tbl:' || l_org_id_tbl(i) ||
1178 ', l_ctx_rowid_tbl:' || l_ctx_rowid_tbl(i) ||
1179 ', p_current_mode:' || p_current_mode );
1180 END IF;
1181 l_current_ctx_item_rec.po_category_id := l_ctx_po_category_id_tbl(i);
1182 l_current_ctx_item_rec.ip_category_id := l_ctx_ip_category_id_tbl(i);
1183 l_current_ctx_item_rec.ip_category_name := l_ctx_ip_category_name_tbl(i);
1184 ELSE
1185 l_current_ctx_item_rec.po_category_id := l_po_category_id_tbl(i);
1186 l_current_ctx_item_rec.ip_category_id := l_ip_category_id_tbl(i);
1187 l_current_ctx_item_rec.ip_category_name := l_ip_category_name_tbl(i);
1188 END IF;
1189 l_current_ctx_item_rec.req_template_po_line_id := NULL;
1190 l_current_ctx_item_rec.item_revision := '-2';
1191 l_current_ctx_item_rec.po_header_id := NULL;
1192 l_current_ctx_item_rec.document_number := NULL;
1193 l_current_ctx_item_rec.line_num := NULL;
1194 l_current_ctx_item_rec.allow_price_override_flag := NULL;
1195 l_current_ctx_item_rec.not_to_exceed_price := NULL;
1196 l_current_ctx_item_rec.line_type_id := NULL;
1197 l_current_ctx_item_rec.unit_meas_lookup_code := l_unit_meas_lookup_code_tbl(i);
1198 l_current_ctx_item_rec.suggested_quantity := NULL;
1199 l_current_ctx_item_rec.unit_price := l_unit_price_tbl(i);
1200 l_current_ctx_item_rec.amount := NULL;
1201 l_current_ctx_item_rec.currency_code := NULL;
1202 l_current_ctx_item_rec.rate_type := NULL;
1203 l_current_ctx_item_rec.rate_date := NULL;
1204 l_current_ctx_item_rec.rate := NULL;
1205 l_current_ctx_item_rec.buyer_id := NULL;
1206 l_current_ctx_item_rec.supplier_contact_id := NULL;
1207 l_current_ctx_item_rec.rfq_required_flag := l_rfq_required_flag_tbl(i);
1208 l_current_ctx_item_rec.negotiated_by_preparer_flag := 'N';
1209 l_current_ctx_item_rec.description := l_description_tbl(i);
1210 l_current_ctx_item_rec.long_description := l_long_description_tbl(i);
1211 l_current_ctx_item_rec.organization_id := l_organization_id_tbl(i);
1212 l_current_ctx_item_rec.master_organization_id := l_master_organization_id_tbl(i);
1213 l_current_ctx_item_rec.order_type_lookup_code := 'QUANTITY';
1214 l_current_ctx_item_rec.supplier := NULL;
1215 l_current_ctx_item_rec.global_agreement_flag := 'N';
1216 l_current_ctx_item_rec.merged_source_type := 'MASTER_ITEM';
1217 l_current_ctx_item_rec.ctx_inventory_item_id := l_ctx_inventory_item_id_tbl(i);
1218 l_current_ctx_item_rec.ctx_source_type := l_ctx_source_type_tbl(i);
1219 l_current_ctx_item_rec.ctx_item_type := l_ctx_item_type_tbl(i);
1220 l_current_ctx_item_rec.ctx_purchasing_org_id := l_ctx_purchasing_org_id_tbl(i);
1221 l_current_ctx_item_rec.ctx_supplier_id := l_ctx_supplier_id_tbl(i);
1222 l_current_ctx_item_rec.ctx_supplier_site_id := l_ctx_supplier_site_id_tbl(i);
1223 l_current_ctx_item_rec.ctx_supplier_part_num := l_ctx_supplier_part_num_tbl(i);
1224 l_current_ctx_item_rec.ctx_supplier_part_auxid := l_ctx_supplier_part_auxid_tbl(i);
1225 l_current_ctx_item_rec.ctx_ip_category_id := l_ctx_ip_category_id_tbl(i);
1226 l_current_ctx_item_rec.ctx_po_category_id := l_ctx_po_category_id_tbl(i);
1227 l_current_ctx_item_rec.ctx_rowid := l_ctx_rowid_tbl(i);
1228
1229 l_err_loc := 1200;
1230 ICX_CAT_POPULATE_ITEM_PVT.processCurrentCtxItemRow(l_current_ctx_item_rec, null, p_current_mode);
1231
1232 l_err_loc := 1300;
1233 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('INLOOP', ICX_CAT_UTIL_PVT.g_MasterItemCsr_const);
1234 ELSE
1235 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
1236 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1237 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1238 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1239 'Row #:' || i ||
1240 ', item:( ' || l_inv_item_id_tbl(i) ||
1241 ', ' || l_org_id_tbl(i) || '), Not processed' ||
1242 ', internal_order_enabled_flag:' || l_intrnl_order_enbld_flag_tbl(i) ||
1243 ', purchasing_enabled_flag:' || l_purchasing_enabled_flag_tbl(i) ||
1244 ', outside_operation_flag:' || l_outside_operation_flag_tbl(i) ||
1245 ', list_price_per_unit:' || l_unit_price_tbl(i) ||
1246 ', po_category_id:' || l_po_category_id_tbl(i) ||
1247 ', status:' || l_inv_item_status);
1248 END IF;
1249 ELSE
1250 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1251 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1252 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1253 'Row #:' || i ||
1254 ', item:( ' || l_inv_item_id_tbl(i) ||
1255 ', ' || l_org_id_tbl(i) || '), Not processed' ||
1256 ', internal_order_enabled_flag:' || l_intrnl_order_enbld_flag_tbl(i) ||
1257 ', purchasing_enabled_flag:' || l_purchasing_enabled_flag_tbl(i) ||
1258 ', outside_operation_flag:' || l_outside_operation_flag_tbl(i) ||
1259 ', list_price_per_unit:' || l_unit_price_tbl(i) ||
1260 ', po_category_id:' || l_po_category_id_tbl(i) ||
1261 ', status:' || l_inv_item_status);
1262 END IF;
1263 END IF;
1264 END IF;
1265 END LOOP; --FOR LOOP of l_inv_item_id_tbl
1266
1267 l_err_loc := 1400;
1268 EXIT WHEN l_inv_item_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
1269 EXCEPTION
1270 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
1271 l_err_string := 'ICX_CAT_POPULATE_MI_PVT.populateMIs' ||l_err_loc
1272 ||', Total processeded batches:' ||l_batch_count
1273 ||', Cursor will be reopened with inventory_item_id:' ||l_start_inv_item_id;
1274 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
1275 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
1276 --Closing and reopen of cursor will be done by called procedures
1277 l_err_loc := 1500;
1278 IF (p_current_mode = ICX_CAT_UTIL_PVT.g_upgrade_const) THEN
1279 l_err_loc := 1600;
1280 openR12UpgradeMICursor;
1281 ELSE
1282 l_err_loc := 1700;
1283 IF (g_online_mode = g_bulkUpdate_mode) THEN
1284 l_err_loc := 1800;
1285 openBulkItemChangeCursor(l_start_inv_item_id, G_REQUEST_ID, G_ENTITY_TYPE);
1286 ELSIF (g_online_mode = g_catgItemUpdate_mode) THEN
1287 l_err_loc := 1800;
1288 openCategoryItemsCursor(g_mtl_category_id, l_start_inv_item_id);
1289 ELSE
1290 l_err_loc := 1900;
1291 -- Online case should not throw snapshot too old error.
1292 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1293 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1294 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1295 'Online case throwing snap shot too old error');
1296 END IF;
1297 END IF;
1298 END IF;
1299 ELSE
1300 RAISE;
1301 END IF;
1302 END;
1303 END LOOP; --Cursor loop
1304
1305 l_err_loc := 2000;
1306 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('OUTLOOP', ICX_CAT_UTIL_PVT.g_MasterItemCsr_const);
1307
1308 l_err_loc := 2100;
1309 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1310 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1311 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1312 'populateMIs in mode:'|| p_current_mode ||' done; '||
1313 'Total num. of batches processed:' ||l_batch_count ||
1314 ', Total num. of rows processed:' ||l_row_count);
1315 END IF;
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318 ICX_CAT_UTIL_PVT.logUnexpectedException(
1319 G_PKG_NAME, l_api_name,
1320 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1321 RAISE;
1322 END populateMIs;
1323
1324 PROCEDURE upgradeR12MIs
1325 ( p_upgrade_last_run_date IN DATE ,
1326 p_start_rowid IN ROWID ,
1327 p_end_rowid IN ROWID
1328 )
1329 IS
1330 l_api_name CONSTANT VARCHAR2(30) := 'upgradeR12MIs';
1331 l_err_loc PLS_INTEGER;
1332 BEGIN
1333 l_err_loc := 100;
1334 g_upgrade_last_run_date := p_upgrade_last_run_date;
1335
1336 l_err_loc := 200;
1337 g_start_rowid := p_start_rowid;
1338 g_end_rowid := p_end_rowid;
1339
1340 l_err_loc := 300;
1341 openR12UpgradeMICursor;
1342
1343 l_err_loc := 400;
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 ICX_CAT_UTIL_PVT.logUnexpectedException(
1347 G_PKG_NAME, l_api_name,
1348 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1349 ICX_CAT_UTIL_PVT.g_job_mi_complete_date := NULL;
1350 ICX_CAT_UTIL_PVT.g_job_complete_date := NULL;
1351 ICX_CAT_UTIL_PVT.g_job_current_status := ICX_CAT_UTIL_PVT.g_job_failed_status;
1352 RAISE;
1353 END upgradeR12MIs;
1354
1355 PROCEDURE populateItemChange
1356 ( P_INVENTORY_ITEM_ID IN NUMBER ,
1357 P_ORGANIZATION_ID IN NUMBER ,
1358 P_REQUEST_ID IN NUMBER ,
1359 P_ENTITY_TYPE IN VARCHAR2
1360 )
1361 IS
1362 l_api_name CONSTANT VARCHAR2(30) := 'populateItemChange';
1363 l_err_loc PLS_INTEGER;
1364 BEGIN
1365 l_err_loc := 100;
1366 g_inventory_item_id := P_INVENTORY_ITEM_ID;
1367 g_organization_id := P_ORGANIZATION_ID;
1368 g_request_id := P_REQUEST_ID;
1369 g_entity_type := P_ENTITY_TYPE;
1370
1371 l_err_loc := 300;
1372 -- Set the batch_size for the online case
1373 ICX_CAT_UTIL_PVT.setBatchSize;
1374
1375 l_err_loc := 400;
1376 ICX_CAT_UTIL_PVT.setWhoColumns(p_request_id);
1377
1378 l_err_loc := 500;
1379 -- Initialize the purchasing category set info.
1380 -- If coming from populateItemCatgChange,
1381 -- this will already be set in ICX_CAT_POPULATE_MI_GRP.populateItemCategoryChange.
1382 IF (ICX_CAT_UTIL_PVT.g_category_set_id IS NULL) THEN
1383 l_err_loc := 600;
1384 ICX_CAT_UTIL_PVT.getPurchasingCategorySetInfo;
1385 ELSE
1386 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1387 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1388 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1389 'Purchasing category set info:' ||
1390 ', g_category_set_id:' || ICX_CAT_UTIL_PVT.g_category_set_id ||
1391 ', g_validate_flag:' || ICX_CAT_UTIL_PVT.g_validate_flag ||
1392 ', g_structure_id:' || ICX_CAT_UTIL_PVT.g_structure_id);
1393 END IF;
1394 END IF;
1395
1396 l_err_loc := 700;
1397 IF (P_REQUEST_ID IS NULL) THEN
1398 g_online_mode := g_onlineUpdate_mode;
1399 l_err_loc := 800;
1400 openOnlineItemChangeCursor(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
1401 ELSE
1402 l_err_loc := 900;
1403 g_online_mode := g_bulkUpdate_mode;
1404 l_err_loc := 1000;
1405 openBulkItemChangeCursor(0, P_REQUEST_ID, P_ENTITY_TYPE);
1406 END IF;
1407
1408 l_err_loc := 1100;
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 ICX_CAT_UTIL_PVT.logUnexpectedException(
1412 G_PKG_NAME, l_api_name,
1413 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1414 RAISE;
1415 END populateItemChange;
1416
1417 PROCEDURE populateItemDelete
1418 ( P_INVENTORY_ITEM_ID IN NUMBER ,
1419 P_ORGANIZATION_ID IN NUMBER
1420 )
1421 IS
1422 CURSOR masterItemsToBeDeletedCsr(P_INVENTORY_ITEM_ID NUMBER,
1423 P_ORGANIZATION_ID NUMBER) IS
1424 SELECT ctx.inventory_item_id,
1425 ctx.org_id,
1426 ctx.language
1427 FROM icx_cat_items_ctx_hdrs_tlp ctx,
1428 financials_system_params_all fsp,
1429 mtl_parameters mparams
1430 WHERE ctx.inventory_item_id = P_INVENTORY_ITEM_ID
1431 AND ctx.source_type = 'MASTER_ITEM'
1432 AND (mparams.master_organization_id = P_ORGANIZATION_ID
1433 OR mparams.organization_id = P_ORGANIZATION_ID)
1434 AND fsp.inventory_organization_id = mparams.organization_id
1435 AND fsp.org_id = ctx.org_id;
1436
1437 ----- Start of declaring columns selected in the cursor -----
1438 l_inv_item_id_tbl DBMS_SQL.NUMBER_TABLE;
1439 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1440 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
1441 ------ End of declaring columns selected in the cursor ------
1442
1443 l_api_name CONSTANT VARCHAR2(30) := 'populateItemDelete';
1444 l_err_loc PLS_INTEGER;
1445 l_batch_count PLS_INTEGER;
1446 l_current_ctx_item_rec ICX_CAT_POPULATE_ITEM_PVT.g_ctx_item_rec_type;
1447 l_err_string VARCHAR2(4000);
1448
1449 BEGIN
1450 l_err_loc := 100;
1451 l_batch_count := 0;
1452
1453 l_err_loc := 200;
1454 -- Set the batch_size for the online case
1455 ICX_CAT_UTIL_PVT.setBatchSize;
1456
1457 l_err_loc := 300;
1458 ICX_CAT_UTIL_PVT.setWhoColumns(null);
1459
1460 l_err_loc := 400;
1461 OPEN masterItemsToBeDeletedCsr(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
1462
1463 LOOP
1464 l_err_loc := 500;
1465 l_inv_item_id_tbl.DELETE;
1466 l_org_id_tbl.DELETE;
1467 l_language_tbl.DELETE;
1468
1469 BEGIN
1470 l_err_loc := 600;
1471 FETCH masterItemsToBeDeletedCsr BULK COLLECT INTO
1472 l_inv_item_id_tbl, l_org_id_tbl, l_language_tbl
1473 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
1474 l_err_loc := 700;
1475 EXIT WHEN l_inv_item_id_tbl.COUNT = 0;
1476
1477 l_err_loc := 800;
1478 l_batch_count := l_batch_count + 1;
1479
1480 FOR i in 1..l_inv_item_id_tbl.COUNT LOOP
1481 l_err_loc := 900;
1482 l_current_ctx_item_rec.ctx_inventory_item_id := l_inv_item_id_tbl(i);
1483 l_current_ctx_item_rec.po_line_id := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
1484 l_current_ctx_item_rec.req_template_name := TO_CHAR(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
1485 l_current_ctx_item_rec.req_template_line_num := TO_NUMBER(ICX_CAT_UTIL_PVT.g_NULL_NUMBER);
1486 l_current_ctx_item_rec.org_id := l_org_id_tbl(i);
1487 l_current_ctx_item_rec.language := l_language_tbl(i);
1488
1489 l_err_loc := 1000;
1490 ICX_CAT_POPULATE_ITEM_PVT.deleteItemCtxHdrsTLP(l_current_ctx_item_rec);
1491
1492 l_err_loc := 1100;
1493 ICX_CAT_POPULATE_ITEM_PVT.deleteItemCtxDtlsTLP(l_current_ctx_item_rec);
1494
1495 l_err_loc := 1200;
1496 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('INLOOP', ICX_CAT_UTIL_PVT.g_MasterItemCsr_const);
1497 END LOOP; --FOR LOOP of l_inv_item_id_tbl
1498
1499 l_err_loc := 1300;
1500 EXIT WHEN l_inv_item_id_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
1501 EXCEPTION
1502 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
1503 l_err_string := 'ICX_CAT_POPULATE_MI_PVT.populateItemDelete' ||l_err_loc
1504 ||', Total processeded batches:' ||l_batch_count
1505 ||', Cursor will be reopened;';
1506 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
1507 l_err_loc := 1400;
1508 IF (masterItemsToBeDeletedCsr%ISOPEN) THEN
1509 l_err_loc := 1500;
1510 CLOSE masterItemsToBeDeletedCsr;
1511 l_err_loc := 1600;
1512 OPEN masterItemsToBeDeletedCsr(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
1513 END IF;
1514 END;
1515 END LOOP; --Cursor loop
1516
1517 l_err_loc := 1700;
1518 ICX_CAT_POPULATE_ITEM_PVT.populateItemCtxTables('OUTLOOP', ICX_CAT_UTIL_PVT.g_MasterItemCsr_const);
1519
1520 l_err_loc := 1800;
1521 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1522 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1523 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1524 'populateItemDelete done; Total num. of batches processed:' || l_batch_count);
1525 END IF;
1526
1527 l_err_loc := 1900;
1528 IF (masterItemsToBeDeletedCsr%ISOPEN) THEN
1529 l_err_loc := 2000;
1530 CLOSE masterItemsToBeDeletedCsr;
1531 END IF;
1532
1533 l_err_loc := 2100;
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 ICX_CAT_UTIL_PVT.logUnexpectedException(
1537 G_PKG_NAME, l_api_name,
1538 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1539 RAISE;
1540 END populateItemDelete;
1541
1542 PROCEDURE populateItemCatgChange
1543 ( P_INVENTORY_ITEM_ID IN NUMBER ,
1544 P_ORGANIZATION_ID IN NUMBER ,
1545 P_CATEGORY_ID IN NUMBER ,
1546 P_REQUEST_ID IN NUMBER ,
1547 P_ENTITY_TYPE IN VARCHAR2
1548 )
1549 IS
1550 l_api_name CONSTANT VARCHAR2(30) := 'populateItemCatgChange';
1551 l_err_loc PLS_INTEGER;
1552 BEGIN
1553 l_err_loc := 100;
1554 -- Call populateItemChange because it internally checks for category change.
1555 populateItemChange(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID, P_REQUEST_ID, P_ENTITY_TYPE);
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 ICX_CAT_UTIL_PVT.logUnexpectedException(
1559 G_PKG_NAME, l_api_name,
1560 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1561 RAISE;
1562 END populateItemCatgChange;
1563
1564 PROCEDURE populateItemCatgDelete
1565 ( P_INVENTORY_ITEM_ID IN NUMBER ,
1566 P_ORGANIZATION_ID IN NUMBER
1567 )
1568 IS
1569 l_api_name CONSTANT VARCHAR2(30) := 'populateItemCatgDelete';
1570 l_err_loc PLS_INTEGER;
1571 BEGIN
1572 l_err_loc := 100;
1573 g_inventory_item_id := P_INVENTORY_ITEM_ID;
1574 g_organization_id := P_ORGANIZATION_ID;
1575 g_online_mode := g_onlineUpdate_mode;
1576
1577 l_err_loc := 200;
1578 -- Set the batch_size for the online case
1579 ICX_CAT_UTIL_PVT.setBatchSize;
1580
1581 l_err_loc := 300;
1582 ICX_CAT_UTIL_PVT.setWhoColumns(null);
1583
1584 l_err_loc := 400;
1585 openOnlineItemCatgDeleteCursor(P_INVENTORY_ITEM_ID, P_ORGANIZATION_ID);
1586
1587 l_err_loc := 500;
1588 EXCEPTION
1589 WHEN OTHERS THEN
1590 ICX_CAT_UTIL_PVT.logUnexpectedException(
1591 G_PKG_NAME, l_api_name,
1592 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1593 RAISE;
1594 END populateItemCatgDelete;
1595
1596 PROCEDURE populateCategoryItems
1597 ( P_MTL_CATEGORY_ID_TBL IN DBMS_SQL.NUMBER_TABLE
1598 )
1599 IS
1600 l_api_name CONSTANT VARCHAR2(30) := 'populateCategoryItems';
1601 l_err_loc PLS_INTEGER;
1602 l_start_date DATE;
1603 l_end_date DATE;
1604 l_log_string VARCHAR2(2000);
1605 BEGIN
1606 l_err_loc := 100;
1607 l_start_date := sysdate;
1608
1609 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1610 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
1611 ', P_MTL_CATEGORY_ID_TBL.COUNT:' || P_MTL_CATEGORY_ID_TBL.COUNT;
1612 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1613 END IF;
1614
1615 g_online_mode := g_catgItemUpdate_mode;
1616 FOR i IN 1..P_MTL_CATEGORY_ID_TBL.COUNT LOOP
1617 g_mtl_category_id := P_MTL_CATEGORY_ID_TBL(i);
1618 l_err_loc := 200;
1619 openCategoryItemsCursor(P_MTL_CATEGORY_ID_TBL(i), 0);
1620 END LOOP;
1621
1622 l_err_loc := 900;
1623 l_end_date := sysdate;
1624 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1625 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1626 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1627 END IF;
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 ICX_CAT_UTIL_PVT.logUnexpectedException(
1631 G_PKG_NAME, l_api_name,
1632 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1633 RAISE;
1634 END populateCategoryItems;
1635
1636 PROCEDURE buildCtxSqlForMIs
1637 ( p_special_ctx_sql_tbl IN OUT NOCOPY ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type,
1638 p_regular_ctx_sql_tbl IN OUT NOCOPY ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type
1639 )
1640 IS
1641 l_api_name CONSTANT VARCHAR2(30) := 'buildCtxSqlForMIs';
1642 l_err_loc PLS_INTEGER;
1643 BEGIN
1644 l_err_loc := 100;
1645 IF (NOT ICX_CAT_POPULATE_MI_PVT.g_metadataTblFormed) THEN
1646 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1647 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1648 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1649 'about to call buildmetadatinfo');
1650 END IF;
1651
1652 l_err_loc := 200;
1653 ICX_CAT_BUILD_CTX_SQL_PVT.buildMetadataInfo
1654 (0, g_special_metadata_tbl, g_regular_nontl_metadata_tbl, g_regular_tl_metadata_tbl);
1655 l_err_loc := 300;
1656 ICX_CAT_POPULATE_MI_PVT.g_metadataTblFormed := TRUE;
1657 END IF;
1658
1659 l_err_loc := 400;
1660 IF (NOT ICX_CAT_POPULATE_MI_PVT.g_CtxSqlForMIsFormed) THEN
1661 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1662 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1663 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1664 'about to call buildctxsql');
1665 END IF;
1666 l_err_loc := 500;
1667 ICX_CAT_BUILD_CTX_SQL_PVT.buildCtxSql
1668 (0, ICX_CAT_UTIL_PVT.g_MasterItemCsr_const, 'NOTROWID', g_special_metadata_tbl,
1669 g_regular_nontl_metadata_tbl, g_regular_tl_metadata_tbl,
1670 g_all_ctx_sql_tbl, g_special_ctx_sql_tbl, g_regular_ctx_sql_tbl);
1671 l_err_loc := 600;
1672 ICX_CAT_POPULATE_MI_PVT.g_CtxSqlForMIsFormed := TRUE;
1673 END IF;
1674
1675 l_err_loc := 700;
1676 p_special_ctx_sql_tbl := g_special_ctx_sql_tbl;
1677 p_regular_ctx_sql_tbl := g_regular_ctx_sql_tbl;
1678
1679 l_err_loc := 800;
1680 EXCEPTION
1681 WHEN OTHERS THEN
1682 ICX_CAT_UTIL_PVT.logUnexpectedException(
1683 G_PKG_NAME, l_api_name,
1684 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1685 RAISE;
1686 END buildCtxSqlForMIs;
1687
1688 END ICX_CAT_POPULATE_MI_PVT;