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