[Home] [Help]
27: CURSOR c_cat_assign_exists (cp_item_id NUMBER,
28: cp_org_id NUMBER,
29: cp_cat_set_id NUMBER)
30: IS
31: SELECT category_id FROM mtl_item_categories
32: WHERE inventory_item_id = cp_item_id
33: AND organization_id = cp_org_id
34: AND category_set_id = cp_cat_set_id;
35:
74: , p_transaction_id => p_transaction_id
75: , p_column_name => l_column_name
76: );
77:
78: UPDATE mtl_item_categories_interface
79: SET process_flag = 3
80: WHERE rowid = p_row_id;
81:
82: ELSE
79: SET process_flag = 3
80: WHERE rowid = p_row_id;
81:
82: ELSE
83: UPDATE mtl_item_categories_interface
84: SET old_category_id = l_category_id,
85: transaction_type = 'UPDATE'
86: WHERE rowid = p_row_id;
87:
88: l_transaction_type := 'UPDATE';
89: l_old_category_id := l_category_id;
90: END IF;
91: ELSE
92: UPDATE mtl_item_categories_interface
93: SET transaction_type = 'CREATE'
94: WHERE rowid = p_row_id;
95:
96: l_transaction_type := 'CREATE';
138: , mici.category_set_id, mici.category_id
139: , mici.organization_code, mici.item_number
140: , mici.category_set_name, mici.category_name
141: FROM
142: mtl_item_categories_interface mici
143: WHERE
144: set_process_id = g_xset_id
145: AND process_flag = 1
146: AND ( organization_id IS NULL
171: , mici.created_by
172: , mici.set_process_id,mici.source_system_reference -- Added for Bug 9305193 Fix
173: , mici.source_system_id
174: FROM
175: mtl_item_categories_interface mici
176: , mtl_parameters mp
177: WHERE
178: mici.set_process_id = g_xset_id
179: AND mici.organization_id = mp.organization_id
223: IS
224: -- SELECT DISTINCT item_number, organization_id
225: SELECT rowid, transaction_id
226: , item_number, organization_id
227: FROM mtl_item_categories_interface
228: WHERE set_process_id = g_xset_id
229: AND inventory_item_id IS NULL
230: AND item_number IS NOT NULL
231: AND category_set_id IS NOT NULL
236: CURSOR miss_category_id_csr
237: IS
238: SELECT rowid, transaction_id
239: , category_name, organization_id, category_set_id
240: FROM mtl_item_categories_interface
241: WHERE set_process_id = g_xset_id
242: AND category_id IS NULL
243: AND category_name IS NOT NULL
244: AND category_set_id IS NOT NULL
251: -- (not used)
252: /*
253: CURSOR miss_id_csr IS
254: SELECT i.transaction_id, i.organization_id
255: FROM mtl_item_categories_interface i
256: WHERE i.process_flag = l_process_flag_2
257: AND set_process_id = g_xset_id
258: AND ( i.organization_id = org_id OR all_org = l_All_Org )
259: AND (
291: ,mic.CREATED_BY
292: ,mic.LAST_UPDATE_DATE
293: ,mic.LAST_UPDATED_BY
294: ,mic.LAST_UPDATE_LOGIN
295: FROM MTL_ITEM_CATEGORIES_INTERFACE mic
296: WHERE REQUEST_ID = cp_request_id
297: AND set_process_id = cp_set_id
298: AND process_flag = 7;
299:
357: -- IF NVL(fnd_global.conc_program_id,-1) <> -1 THEN Bug:3547401
358: IF NVL(p_prog_id,-1) <> -1 AND p_gather_stats = 1 THEN /* p_gather_stats Added for Bug 8532728 */
359:
360: SELECT count(*) INTO l_records
361: FROM mtl_item_categories_interface
362: WHERE set_process_id = p_rec_set_id
363: AND process_flag IN (1,2,4); --R12C
364:
365: -- Bug 6983407 Collect statistics only if the no. of records is bigger than the profile
365: -- Bug 6983407 Collect statistics only if the no. of records is bigger than the profile
366: -- option threshold
367: IF l_records > nvl(fnd_profile.value('EGO_GATHER_STATS'),100) AND FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema) THEN
368: IF l_schema IS NOT NULL THEN
369: FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_ITEM_CATEGORIES_INTERFACE');
370: END IF;
371: END IF;
372: END IF;
373: --End 2698140 : Gather stats before running
416: g_Prog_appid := NVL(p_prog_appid, FND_GLOBAL.prog_appl_id );
417: g_Prog_id := NVL(p_prog_id, FND_GLOBAL.conc_program_id );
418: g_Request_id := NVL(p_request_id, FND_GLOBAL.conc_request_id );
419:
420: INV_ITEM_MSG.g_Table_Name := 'MTL_ITEM_CATEGORIES_INTERFACE';
421:
422: INV_ITEM_MSG.g_User_id := g_User_id;
423: INV_ITEM_MSG.g_Login_id := g_Login_id;
424: INV_ITEM_MSG.g_Prog_appid := g_Prog_appid;
444: IF (l_debug = 1) THEN
445: INV_ITEM_MSG.Debug(Mctx, 'assign all missing organization_id');
446: END IF;
447:
448: UPDATE mtl_item_categories_interface mici
449: SET
450: ( mici.organization_id
451: , process_flag
452: ) =
473: SELECT mtl_system_items_interface_s.NEXTVAL
474: INTO l_transaction_id
475: FROM dual;
476:
477: UPDATE mtl_item_categories_interface
478: SET
479: -- transaction_id = mtl_system_items_interface_s.NEXTVAL
480: transaction_id = l_transaction_id
481: , request_id = g_request_id
1312: IF (l_debug = 1) THEN
1313: INV_ITEM_MSG.Debug(Mctx, 'update interface record');
1314: END IF;
1315:
1316: UPDATE mtl_item_categories_interface
1317: SET
1318: transaction_id = l_transaction_id
1319: , transaction_type = l_transaction_type
1320: , process_flag = l_process_flag
1337: /* Bug 4527222*/
1338: BEGIN
1339: SELECT 'Y'
1340: INTO l_records_updated
1341: FROM mtl_item_categories_interface mici
1342: WHERE mici.set_process_id = g_xset_id
1343: AND mici.process_flag = 7
1344: AND ROWNUM = 1;
1345:
1563: -- Initialize API return status to success
1564: x_return_status := FND_API.g_RET_STS_SUCCESS;
1565:
1566: LOOP
1567: DELETE FROM mtl_item_categories_interface
1568: WHERE set_process_id = p_rec_set_id
1569: AND process_flag = l_del_process_flag
1570: AND rownum < G_ROWS_TO_COMMIT;
1571: