eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_CL_ITEM_ODS_LOAD dependencies on MSC_UTIL

Line 7: -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed

3:
4: v_sql_stmt VARCHAR2(32767);
5: lv_sql_stmt1 VARCHAR2(32767);
6: v_sub_str VARCHAR2(32767);
7: -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed
8:
9: -- G_COLLECT_SRP_DATA VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSC_SRP_ENABLED'),'N');
10: -- To collect SRP Data when this profile is set to Yes neds to be deleted
11: -- v_is_cont_refresh BOOLEAN; -- 2 be changed

Line 53: IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN

49: lv_inventory_item_id Number;
50: lv_organization_id Number ;
51: lv_min_last_item_coll_date DATE;
52: BEGIN
53: IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN
54: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
55: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);
56: else
57: lv_table_name:= 'MSC_SYSTEM_ITEMS';

Line 55: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);

51: lv_min_last_item_coll_date DATE;
52: BEGIN
53: IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN
54: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
55: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);
56: else
57: lv_table_name:= 'MSC_SYSTEM_ITEMS';
58: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);
59: End if;

Line 58: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);

54: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
55: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);
56: else
57: lv_table_name:= 'MSC_SYSTEM_ITEMS';
58: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in ADD_NEW_IMPL_ITEM_ASL is ' || lv_table_name);
59: End if;
60:
61: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ITEM_REF_TIME,SYSDATE-365000))'
62: ||' From msc_instance_orgs '

Line 64: ||' And organization_id '|| MSC_UTIL.v_in_org_str;

60:
61: v_sql_stmt := 'Select min (nvl(LAST_SUCC_ITEM_REF_TIME,SYSDATE-365000))'
62: ||' From msc_instance_orgs '
63: ||' Where sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
64: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
65:
66: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
67:
68: EXECUTE IMMEDIATE v_sql_stmt into lv_min_last_item_coll_date;

Line 66: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);

62: ||' From msc_instance_orgs '
63: ||' Where sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
64: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
65:
66: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
67:
68: EXECUTE IMMEDIATE v_sql_stmt into lv_min_last_item_coll_date;
69:
70: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);

Line 70: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);

66: -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
67:
68: EXECUTE IMMEDIATE v_sql_stmt into lv_min_last_item_coll_date;
69:
70: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
71: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'minimum of last successful ITEM Collection refresh time is '||lv_min_last_item_coll_date);
72:
73: lv_sql_stmt := 'select x.inventory_item_id , x.organization_id '
74: ||' FROM ' || lv_table_name ||' x , MSC_INSTANCE_ORGS mio'

Line 71: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'minimum of last successful ITEM Collection refresh time is '||lv_min_last_item_coll_date);

67:
68: EXECUTE IMMEDIATE v_sql_stmt into lv_min_last_item_coll_date;
69:
70: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'the sql statement is ' || v_sql_stmt);
71: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'minimum of last successful ITEM Collection refresh time is '||lv_min_last_item_coll_date);
72:
73: lv_sql_stmt := 'select x.inventory_item_id , x.organization_id '
74: ||' FROM ' || lv_table_name ||' x , MSC_INSTANCE_ORGS mio'
75: ||' WHERE '

Line 78: ||' AND x.organization_id '|| MSC_UTIL.v_in_org_str

74: ||' FROM ' || lv_table_name ||' x , MSC_INSTANCE_ORGS mio'
75: ||' WHERE '
76: ||' x.organization_id = mio.organization_id '
77: ||' AND x.sr_instance_id = mio.sr_instance_id '
78: ||' AND x.organization_id '|| MSC_UTIL.v_in_org_str
79: ||' AND x.item_creation_date > nvl(mio.LAST_SUCC_ITEM_REF_TIME, SYSDATE-365000)'
80: ||' AND x.item_creation_date>:lv_min_last_item_coll_date'
81: ||' AND x.sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
82: ||' AND x.plan_id =-1 ';

Line 84: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'the sql statement is ' || lv_sql_stmt);

80: ||' AND x.item_creation_date>:lv_min_last_item_coll_date'
81: ||' AND x.sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
82: ||' AND x.plan_id =-1 ';
83:
84: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'the sql statement is ' || lv_sql_stmt);
85:
86: Open c1 for lv_sql_stmt using lv_min_last_item_coll_date ;
87: IF (c1%ISOPEN ) THEN
88: LOOP

Line 189: || ' AND ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str || ' ) a ';

185: || ' WHERE SR_INSTANCE_ID =' || MSC_CL_COLLECTION.v_instance_id
186: || ' AND PLAN_ID =-1 '
187: || ' AND USING_ORGANIZATION_ID =-1 '
188: || ' AND INVENTORY_ITEM_ID =' ||lv_inventory_item_id
189: || ' AND ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str || ' ) a ';
190:
191: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'sql in inser_Asl ' || lv_sql_stmt1);
192:
193: EXECUTE IMMEDIATE lv_sql_stmt1

Line 191: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'sql in inser_Asl ' || lv_sql_stmt1);

187: || ' AND USING_ORGANIZATION_ID =-1 '
188: || ' AND INVENTORY_ITEM_ID =' ||lv_inventory_item_id
189: || ' AND ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str || ' ) a ';
190:
191: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'sql in inser_Asl ' || lv_sql_stmt1);
192:
193: EXECUTE IMMEDIATE lv_sql_stmt1
194: USING lv_organization_id ,MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
195:

Line 196: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows inserted in ADD_NEW_IMPL_ITEM_ASL ' || SQL%ROWCOUNT);

192:
193: EXECUTE IMMEDIATE lv_sql_stmt1
194: USING lv_organization_id ,MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
195:
196: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows inserted in ADD_NEW_IMPL_ITEM_ASL ' || SQL%ROWCOUNT);
197: commit ;
198: END LOOP;
199:
200: END IF ;

Line 206: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

202: CLOSE c1 ;
203: EXCEPTION
204:
205: WHEN OTHERS THEN
206: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
207:
208: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
209: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
210: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'ADD_NEW_IMPL_ITEM_ASL');

Line 208: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

204:
205: WHEN OTHERS THEN
206: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
207:
208: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
209: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
210: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'ADD_NEW_IMPL_ITEM_ASL');
211: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
212: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 212: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

208: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
209: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
210: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'ADD_NEW_IMPL_ITEM_ASL');
211: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
212: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
213:
214: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
215: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
216: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lv_inventory_item_id));

Line 217: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

213:
214: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
215: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
216: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lv_inventory_item_id));
217: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
218:
219: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
220: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
221: FND_MESSAGE.SET_TOKEN('VALUE',

Line 224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

220: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
221: FND_MESSAGE.SET_TOKEN('VALUE',
222: MSC_GET_NAME.ORG_CODE( lv_organization_id,
223: MSC_CL_COLLECTION.v_instance_id));
224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
225:
226: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
227:
228: END ADD_NEW_IMPL_ITEM_ASL;

Line 226: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

222: MSC_GET_NAME.ORG_CODE( lv_organization_id,
223: MSC_CL_COLLECTION.v_instance_id));
224: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
225:
226: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
227:
228: END ADD_NEW_IMPL_ITEM_ASL;
229:
230: FUNCTION ITEM_NAME ( p_item_id IN NUMBER)

Line 289: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');

285:
286: EXCEPTION
287: WHEN OTHERS THEN
288: ROLLBACK;
289: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
291:
292: END UPDATE_LEADTIME;
293:

Line 290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

286: EXCEPTION
287: WHEN OTHERS THEN
288: ROLLBACK;
289: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'An error has occurred.');
290: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
291:
292: END UPDATE_LEADTIME;
293:
294: PROCEDURE LOAD_ABC_CLASSES IS

Line 300: OR (MSC_CL_COLLECTION.v_is_incremental_refresh AND MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS120)) THEN

296: lv_sql_stmt VARCHAR2(7500);
297: BEGIN
298:
299: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
300: OR (MSC_CL_COLLECTION.v_is_incremental_refresh AND MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS120)) THEN
301:
302: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', MSC_CL_COLLECTION.v_instance_id,NULL );
304: ELSE

Line 302: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

298:
299: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
300: OR (MSC_CL_COLLECTION.v_is_incremental_refresh AND MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS120)) THEN
301:
302: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', MSC_CL_COLLECTION.v_instance_id,NULL );
304: ELSE
305: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
306: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);

Line 305: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

301:
302: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
303: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', MSC_CL_COLLECTION.v_instance_id,NULL );
304: ELSE
305: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
306: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
307: END IF;
308: /*changed for bug:4765403*/
309: IF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS120 THEN

Line 309: IF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS120 THEN

305: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
306: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ABC_CLASSES', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
307: END IF;
308: /*changed for bug:4765403*/
309: IF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS120 THEN
310:
311: lv_temp_sql_stmt :=
312: ' SELECT'
313: ||' msa.abc_class_id,'

Line 369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

365: EXCEPTION
366: WHEN OTHERS THEN
367: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
368:
369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
370: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
371: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ABC_CLASSES');
372: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ABC_CLASSES');
373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

369: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
370: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
371: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ABC_CLASSES');
372: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ABC_CLASSES');
373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
374:
375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
376: RAISE;
377:

Line 375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

371: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ABC_CLASSES');
372: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ABC_CLASSES');
373: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
374:
375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
376: RAISE;
377:
378: ELSE
379: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

Line 379: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
376: RAISE;
377:
378: ELSE
379: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
380: null;
381:
382: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
383: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');

Line 382: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

378: ELSE
379: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
380: null;
381:
382: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
383: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
384: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ABC_CLASSES');
385: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ABC_CLASSES');
386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

382: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
383: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
384: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ABC_CLASSES');
385: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ABC_CLASSES');
386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
387:
388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
389: END IF;
390:

Line 388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

384: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ABC_CLASSES');
385: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ABC_CLASSES');
386: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
387:
388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
389: END IF;
390:
391: END;
392: END IF;

Line 439: AND msis.deleted_flag=MSC_UTIL.SYS_NO;

435: AND tps.SR_INSTANCE_ID(+)= msis.SR_INSTANCE_ID
436: AND tps.PARTNER_TYPE(+)= 2
437: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
438: AND msis.relationship_type=2
439: AND msis.deleted_flag=MSC_UTIL.SYS_NO;
440:
441: CURSOR c2 IS
442: SELECT
443: t1.INVENTORY_ITEM_ID HIGHER_ITEM_ID,

Line 463: AND msis.deleted_flag=MSC_UTIL.SYS_NO;

459: AND t2.SR_INVENTORY_ITEM_ID = msis.LOWER_ITEM_ID
460: AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
461: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
462: AND msis.relationship_type in (5,8,18)
463: AND msis.deleted_flag=MSC_UTIL.SYS_NO;
464:
465: TYPE CharTblTyp IS TABLE OF VARCHAR2(70);
466: TYPE NumTblTyp IS TABLE OF NUMBER;
467: TYPE dateTblTyp IS TABLE OF DATE;

Line 487: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN

483:
484: BEGIN
485:
486:
487: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
488: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
489: ELSE
490: lv_tbl:= ' MSC_ITEM_SUBSTITUTES ';
491: END IF;

Line 641: ||' AND nvl(msis.deleted_flag,2)='||MSC_UTIL.SYS_NO;

637: ||' AND tps.SR_INSTANCE_ID(+)= msis.SR_INSTANCE_ID'
638: ||' AND tps.PARTNER_TYPE(+)= 2'
639: ||' AND msis.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
640: ||' AND msis. RELATIONSHIP_TYPE =2'
641: ||' AND nvl(msis.deleted_flag,2)='||MSC_UTIL.SYS_NO;
642:
643:
644: lv_cursor_stmt1:=
645: 'INSERT INTO '||lv_tbl

Line 693: ||' AND nvl(msis.deleted_flag,2)='||MSC_UTIL.SYS_NO;

689: ||' AND t2.SR_INVENTORY_ITEM_ID = msis.LOWER_ITEM_ID'
690: ||' AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID'
691: ||' AND msis.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
692: ||' AND msis. RELATIONSHIP_TYPE in (5,8,18)'
693: ||' AND nvl(msis.deleted_flag,2)='||MSC_UTIL.SYS_NO;
694:
695: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
696: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
697: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

Line 696: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN

692: ||' AND msis. RELATIONSHIP_TYPE in (5,8,18)'
693: ||' AND nvl(msis.deleted_flag,2)='||MSC_UTIL.SYS_NO;
694:
695: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
696: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
697: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
698: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
699: ELSE
700: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

Line 697: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

693: ||' AND nvl(msis.deleted_flag,2)='||MSC_UTIL.SYS_NO;
694:
695: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
696: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
697: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
698: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
699: ELSE
700: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
701: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line 700: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

696: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
697: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
698: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
699: ELSE
700: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
701: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
702: END IF;
703: END IF;
704:

Line 720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

716:
717: EXCEPTION
718: WHEN OTHERS THEN
719: --LOG_MESSAGE(SQLERRM);
720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
721: IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
722:
723: end;
724: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) THEN

Line 724: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) THEN

720: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
721: IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
722:
723: end;
724: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
725:
726: begin
727: EXECUTE IMMEDIATE lv_cursor_stmt1
728: USING MSC_CL_COLLECTION.v_last_collection_id,

Line 738: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

734:
735: EXCEPTION
736: WHEN OTHERS THEN
737: -- LOG_MESSAGE(SQLERRM);
738: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
739: IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
740: end;
741:
742: END IF ;

Line 855: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

851: MSC_CL_COLLECTION.v_current_user;
852: EXCEPTION
853: WHEN OTHERS THEN
854: -- LOG_MESSAGE(SQLERRM);
855: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
856: IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
857: end;
858: END IF;
859:

Line 875: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 OR MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN

871:
872: lb_FetchComplete := FALSE;
873:
874:
875: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 OR MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
876: Open c2;
877:
878: IF (c2%ISOPEN) THEN
879: LOOP

Line 948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

944:
945: EXCEPTION
946: WHEN OTHERS THEN
947: -- LOG_MESSAGE(SQLERRM);
948: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
949: IF NOT Handle_Exception(SQLCODE) THEN RAISE; END IF;
950: end;
951: END IF;
952:

Line 969: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN

965: CLOSE c2;
966: END IF; -- End of Net Change
967:
968:
969: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
970:
971: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
972:
973: lv_sql_stmt:=

Line 974: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl

970:
971: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
972:
973: lv_sql_stmt:=
974: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
975: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_SUBSTITUTES'
976: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
977: ||' AND plan_id = -1 '
978: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;

Line 975: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_SUBSTITUTES'

971: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
972:
973: lv_sql_stmt:=
974: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
975: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_SUBSTITUTES'
976: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
977: ||' AND plan_id = -1 '
978: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
979:

Line 978: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;

974: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
975: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_SUBSTITUTES'
976: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
977: ||' AND plan_id = -1 '
978: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
979:
980: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
981: EXECUTE IMMEDIATE lv_sql_stmt;
982:

Line 980: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);

976: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
977: ||' AND plan_id = -1 '
978: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
979:
980: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
981: EXECUTE IMMEDIATE lv_sql_stmt;
982:
983: COMMIT;
984:

Line 987: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN

983: COMMIT;
984:
985: END IF;
986:
987: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
988: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
989: lv_retcode,
990: 'MSC_ITEM_SUBSTITUTES',
991: MSC_CL_COLLECTION.v_INSTANCE_CODE,

Line 992: MSC_UTIL.G_WARNING

988: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
989: lv_retcode,
990: 'MSC_ITEM_SUBSTITUTES',
991: MSC_CL_COLLECTION.v_INSTANCE_CODE,
992: MSC_UTIL.G_WARNING
993: );
994:
995:
996: IF lv_retcode = MSC_UTIL.G_WARNING THEN

Line 996: IF lv_retcode = MSC_UTIL.G_WARNING THEN

992: MSC_UTIL.G_WARNING
993: );
994:
995:
996: IF lv_retcode = MSC_UTIL.G_WARNING THEN
997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
998: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
999: END IF;
1000:

Line 997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);

993: );
994:
995:
996: IF lv_retcode = MSC_UTIL.G_WARNING THEN
997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
998: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
999: END IF;
1000:
1001: END IF;

Line 998: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

994:
995:
996: IF lv_retcode = MSC_UTIL.G_WARNING THEN
997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
998: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
999: END IF;
1000:
1001: END IF;
1002:

Line 1008: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1004: EXCEPTION
1005: WHEN OTHERS THEN
1006: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1007:
1008: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1009: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1010: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1011: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1012: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1012: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1008: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1009: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1010: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1011: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1012: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1013:
1014: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1015: RAISE;
1016:

Line 1014: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1010: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1011: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1012: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1013:
1014: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1015: RAISE;
1016:
1017: ELSE
1018:

Line 1019: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

1015: RAISE;
1016:
1017: ELSE
1018:
1019: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1020:
1021: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1022: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1023: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');

Line 1021: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1017: ELSE
1018:
1019: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1020:
1021: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1022: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1023: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1024: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1021: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1022: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1023: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1024: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1026:
1027: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1028: FND_MESSAGE.SET_TOKEN('COLUMN', 'HIGHER_ITEM_ID');
1029: -- FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.HIGHER_ITEM_ID) );

Line 1030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1026:
1027: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1028: FND_MESSAGE.SET_TOKEN('COLUMN', 'HIGHER_ITEM_ID');
1029: -- FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.HIGHER_ITEM_ID) );
1030: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1031:
1032: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1033: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOWER_ITEM_ID');
1034: -- FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.LOWER_ITEM_ID) );

Line 1035: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1031:
1032: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1033: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOWER_ITEM_ID');
1034: -- FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.LOWER_ITEM_ID) );
1035: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1036:
1037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1038: END IF;
1039:

Line 1037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1033: FND_MESSAGE.SET_TOKEN('COLUMN', 'LOWER_ITEM_ID');
1034: -- FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(c_rec.LOWER_ITEM_ID) );
1035: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1036:
1037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1038: END IF;
1039:
1040:
1041: COMMIT;

Line 1070: AND msic.DELETED_FLAG= MSC_UTIL.SYS_NO;

1066: AND t1.sr_instance_id= msic.sr_instance_id
1067: AND mcsil.SR_Category_Set_ID= msic.SR_Category_Set_ID
1068: AND mcsil.SR_Instance_ID= msic.SR_Instance_ID
1069: AND msic.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1070: AND msic.DELETED_FLAG= MSC_UTIL.SYS_NO;
1071:
1072: CURSOR c1_d IS
1073: SELECT
1074: msic.ORGANIZATION_ID,

Line 1087: AND msic.DELETED_FLAG= MSC_UTIL.SYS_YES;

1083: AND t1.sr_instance_id= msic.sr_instance_id
1084: AND mcsil.SR_Category_Set_ID= msic.SR_Category_Set_ID
1085: AND mcsil.SR_Instance_ID= msic.SR_Instance_ID
1086: AND msic.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1087: AND msic.DELETED_FLAG= MSC_UTIL.SYS_YES;
1088:
1089: c_count NUMBER:= 0;
1090: lv_tbl VARCHAR2(30);
1091: lv_sql_stmt VARCHAR2(5000);

Line 1121: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then

1117:
1118: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1119: -- We want to delete all CATEGORY related data and get new stuff.
1120:
1121: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1122: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123:
1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);

Line 1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

1120:
1121: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1122: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123:
1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1126: ELSE
1127: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1128: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);

Line 1127: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

1123:
1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1126: ELSE
1127: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1128: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);
1129: END IF;
1130: end if;
1131:

Line 1134: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN

1130: end if;
1131:
1132: END IF;
1133:
1134: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1135: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1136: ELSE
1137: lv_tbl:= 'MSC_ITEM_CATEGORIES';
1138: END IF;

Line 1198: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1194: commit;
1195: EXCEPTION
1196: WHEN OTHERS THEN
1197:
1198: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1199: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1200: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1201: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1198: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1199: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1200: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1201: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1203:
1204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1205: RAISE;
1206:

Line 1204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1200: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1201: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1202: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1203:
1204: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1205: RAISE;
1206:
1207: END;
1208: ELSE

Line 1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1367: WHEN OTHERS THEN
1368:
1369: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1370:
1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1372: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1373: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1374: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1371: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1372: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1373: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1374: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1376:
1377: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1378: RAISE;
1379:

Line 1377: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1373: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1374: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1375: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1376:
1377: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1378: RAISE;
1379:
1380: ELSE
1381:

Line 1382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

1378: RAISE;
1379:
1380: ELSE
1381:
1382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1383:
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');

Line 1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1380: ELSE
1381:
1382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1383:
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1387: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1387: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_CATEGORIES');
1388: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1389:
1390: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1391: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1392: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lb_inventory_item_id(J)));

Line 1393: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1389:
1390: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1391: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1392: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lb_inventory_item_id(J)));
1393: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1394:
1395: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1396: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1397: FND_MESSAGE.SET_TOKEN('VALUE',

Line 1400: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1396: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1397: FND_MESSAGE.SET_TOKEN('VALUE',
1398: MSC_GET_NAME.ORG_CODE( lb_organization_id(j),
1399: MSC_CL_COLLECTION.v_instance_id));
1400: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1401:
1402: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1403: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_NAME');
1404: FND_MESSAGE.SET_TOKEN('VALUE', lb_category_name(j));

Line 1405: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1401:
1402: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1403: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_NAME');
1404: FND_MESSAGE.SET_TOKEN('VALUE', lb_category_name(j));
1405: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1406:
1407: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1408: END IF;
1409:

Line 1407: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1403: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_NAME');
1404: FND_MESSAGE.SET_TOKEN('VALUE', lb_category_name(j));
1405: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1406:
1407: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1408: END IF;
1409:
1410: END;
1411:

Line 1421: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN

1417: COMMIT;
1418:
1419: BEGIN
1420:
1421: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1422:
1423: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1424:
1425: lv_sql_stmt:=

Line 1426: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl

1422:
1423: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1424:
1425: lv_sql_stmt:=
1426: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1427: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_CATEGORIES'
1428: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1429: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1430:

Line 1427: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_CATEGORIES'

1423: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1424:
1425: lv_sql_stmt:=
1426: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1427: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_CATEGORIES'
1428: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1429: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1430:
1431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);

Line 1429: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;

1425: lv_sql_stmt:=
1426: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1427: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_CATEGORIES'
1428: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1429: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1430:
1431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1432: EXECUTE IMMEDIATE lv_sql_stmt;
1433:

Line 1431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);

1427: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_CATEGORIES'
1428: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1429: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1430:
1431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1432: EXECUTE IMMEDIATE lv_sql_stmt;
1433:
1434: COMMIT;
1435:

Line 1438: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN

1434: COMMIT;
1435:
1436: END IF;
1437:
1438: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1439: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1440: lv_retcode,
1441: 'MSC_ITEM_CATEGORIES',
1442: MSC_CL_COLLECTION.v_INSTANCE_CODE,

Line 1443: MSC_UTIL.G_ERROR

1439: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1440: lv_retcode,
1441: 'MSC_ITEM_CATEGORIES',
1442: MSC_CL_COLLECTION.v_INSTANCE_CODE,
1443: MSC_UTIL.G_ERROR
1444: );
1445:
1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);

Line 1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN

1442: MSC_CL_COLLECTION.v_INSTANCE_CODE,
1443: MSC_UTIL.G_ERROR
1444: );
1445:
1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1448: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1449: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1450: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

Line 1447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);

1443: MSC_UTIL.G_ERROR
1444: );
1445:
1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1448: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1449: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1450: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1451: END IF;

Line 1449: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN

1445:
1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1448: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1449: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1450: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1451: END IF;
1452:
1453: END IF;

Line 1450: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1448: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1449: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1450: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1451: END IF;
1452:
1453: END IF;
1454:

Line 1458: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1454:
1455: EXCEPTION
1456: WHEN OTHERS THEN
1457:
1458: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1459: RAISE;
1460: END;
1461:
1462: END LOAD_CATEGORY;

Line 1523: lv_items_stat_stale NUMBER := MSC_UTIL.SYS_NO;

1519: lv_control_flag NUMBER;
1520:
1521: lv_item_id_count NUMBER := 0;
1522: lv_cat_id_count NUMBER := 0;
1523: lv_items_stat_stale NUMBER := MSC_UTIL.SYS_NO;
1524: lv_cat_stat_stale NUMBER := MSC_UTIL.SYS_NO;
1525: lv_ins_records NUMBER := 0;
1526:
1527: BEGIN

Line 1524: lv_cat_stat_stale NUMBER := MSC_UTIL.SYS_NO;

1520:
1521: lv_item_id_count NUMBER := 0;
1522: lv_cat_id_count NUMBER := 0;
1523: lv_items_stat_stale NUMBER := MSC_UTIL.SYS_NO;
1524: lv_cat_stat_stale NUMBER := MSC_UTIL.SYS_NO;
1525: lv_ins_records NUMBER := 0;
1526:
1527: BEGIN
1528:

Line 1539: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )

1535: INTO lv_control_flag
1536: FROM dual;
1537:
1538: begin
1539: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
1540: into lv_item_id_count, lv_items_stat_stale
1541: from dba_TAB_STATISTICS
1542: where table_name = 'MSC_ITEM_ID_LID';
1543: exception when no_data_found then

Line 1544: lv_items_stat_stale := MSC_UTIL.SYS_YES ;

1540: into lv_item_id_count, lv_items_stat_stale
1541: from dba_TAB_STATISTICS
1542: where table_name = 'MSC_ITEM_ID_LID';
1543: exception when no_data_found then
1544: lv_items_stat_stale := MSC_UTIL.SYS_YES ;
1545: end;
1546:
1547: begin
1548: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )

Line 1548: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )

1544: lv_items_stat_stale := MSC_UTIL.SYS_YES ;
1545: end;
1546:
1547: begin
1548: select num_rows,decode (stale_stats,'NO', MSC_UTIL.SYS_NO, MSC_UTIL.SYS_YES )
1549: into lv_cat_id_count, lv_cat_stat_stale
1550: from dba_TAB_STATISTICS
1551: where table_name ='MSC_CATEGORY_SET_ID_LID';
1552: exception when no_data_found then

Line 1553: lv_cat_stat_stale := MSC_UTIL.SYS_YES ;

1549: into lv_cat_id_count, lv_cat_stat_stale
1550: from dba_TAB_STATISTICS
1551: where table_name ='MSC_CATEGORY_SET_ID_LID';
1552: exception when no_data_found then
1553: lv_cat_stat_stale := MSC_UTIL.SYS_YES ;
1554: end;
1555:
1556:
1557: /* if complete refresh, regen the key mapping data */

Line 1562: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

1558: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1559:
1560: IF lv_control_flag = 2 THEN
1561:
1562: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1563: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1564: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1565: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1566: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;

Line 1567: lv_items_stat_stale := MSC_UTIL.SYS_YES;

1563: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1564: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1565: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1566: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1567: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1568: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1569: END IF;
1570:
1571: ELSE

Line 1568: lv_cat_stat_stale := MSC_UTIL.SYS_YES;

1564: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1565: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1566: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1567: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1568: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1569: END IF;
1570:
1571: ELSE
1572:

Line 1573: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

1569: END IF;
1570:
1571: ELSE
1572:
1573: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1574: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1575: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1576: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1577: lv_cat_stat_stale := MSC_UTIL.SYS_YES;

Line 1576: lv_items_stat_stale := MSC_UTIL.SYS_YES;

1572:
1573: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1574: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1575: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1576: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1577: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1578: END IF;
1579:
1580: END IF;

Line 1577: lv_cat_stat_stale := MSC_UTIL.SYS_YES;

1573: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1574: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1575: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1576: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1577: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1578: END IF;
1579:
1580: END IF;
1581:

Line 1588: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.org_group_flag =MSC_UTIL.G_ALL_ORGANIZATIONS ) THEN

1584: /*************** PREPLACE CHANGE START *****************/
1585:
1586: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
1587:
1588: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.org_group_flag =MSC_UTIL.G_ALL_ORGANIZATIONS ) THEN
1589:
1590: IF lv_control_flag = 2 THEN
1591: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1592: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;

Line 1595: lv_items_stat_stale := MSC_UTIL.SYS_YES;

1591: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1592: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1593: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1594: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1595: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1596: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1597: ELSE
1598: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1599: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');

Line 1596: lv_cat_stat_stale := MSC_UTIL.SYS_YES;

1592: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1593: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1594: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1595: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1596: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1597: ELSE
1598: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1599: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1600: lv_items_stat_stale := MSC_UTIL.SYS_YES;

Line 1600: lv_items_stat_stale := MSC_UTIL.SYS_YES;

1596: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1597: ELSE
1598: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1599: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1600: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1601: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1602: END IF;
1603:
1604: END IF;

Line 1601: lv_cat_stat_stale := MSC_UTIL.SYS_YES;

1597: ELSE
1598: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1599: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1600: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1601: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1602: END IF;
1603:
1604: END IF;
1605:

Line 1613: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and

1609:
1610: --agmcont
1611: IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1612:
1613: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1614: (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN
1615:
1616: IF lv_control_flag = 2 THEN
1617: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;

Line 1614: (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN

1610: --agmcont
1611: IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1612:
1613: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1614: (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN
1615:
1616: IF lv_control_flag = 2 THEN
1617: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1618: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;

Line 1621: lv_items_stat_stale := MSC_UTIL.SYS_YES;

1617: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1618: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1619: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1620: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1621: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1622: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1623: ELSE
1624: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1625: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');

Line 1622: lv_cat_stat_stale := MSC_UTIL.SYS_YES;

1618: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1619: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1620: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1621: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1622: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1623: ELSE
1624: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1625: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1626: lv_items_stat_stale := MSC_UTIL.SYS_YES;

Line 1626: lv_items_stat_stale := MSC_UTIL.SYS_YES;

1622: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1623: ELSE
1624: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1625: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1626: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1627: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1628: END IF;
1629:
1630: END IF;

Line 1627: lv_cat_stat_stale := MSC_UTIL.SYS_YES;

1623: ELSE
1624: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1625: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1626: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1627: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1628: END IF;
1629:
1630: END IF;
1631:

Line 1664: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1660: NULL;
1661:
1662: WHEN OTHERS THEN
1663:
1664: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1665: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1666: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1667: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEMS');
1668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1664: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1665: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1666: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1667: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEMS');
1668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1669:
1670: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1671: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1672: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ITEM_NAME);

Line 1673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1669:
1670: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1671: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1672: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ITEM_NAME);
1673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1674:
1675: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1676:
1677: ERRBUF := sqlerrm;

Line 1675: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1671: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1672: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.ITEM_NAME);
1673: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1674:
1675: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1676:
1677: ERRBUF := sqlerrm;
1678: RETCODE := MSC_UTIL.G_ERROR;
1679: RAISE;

Line 1678: RETCODE := MSC_UTIL.G_ERROR;

1674:
1675: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1676:
1677: ERRBUF := sqlerrm;
1678: RETCODE := MSC_UTIL.G_ERROR;
1679: RAISE;
1680:
1681: END;
1682:

Line 1710: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1706: WHEN OTHERS THEN
1707:
1708: IF SQLCODE IN (-1653,-1654) THEN
1709:
1710: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1711: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1712: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1713: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_ID_LID');
1714: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1714: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1710: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1711: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1712: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1713: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_ID_LID');
1714: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1715:
1716: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1717: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1718: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));

Line 1719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1715:
1716: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1717: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1718: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1720:
1721: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1722: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
1723: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_INVENTORY_ITEM_ID));

Line 1724: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1720:
1721: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1722: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
1723: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_INVENTORY_ITEM_ID));
1724: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1725:
1726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1727:
1728: ERRBUF := sqlerrm;

Line 1726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1722: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
1723: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_INVENTORY_ITEM_ID));
1724: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1725:
1726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1727:
1728: ERRBUF := sqlerrm;
1729: RETCODE := MSC_UTIL.G_ERROR;
1730: RAISE;

Line 1729: RETCODE := MSC_UTIL.G_ERROR;

1725:
1726: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1727:
1728: ERRBUF := sqlerrm;
1729: RETCODE := MSC_UTIL.G_ERROR;
1730: RAISE;
1731:
1732: ELSE
1733:

Line 1734: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

1730: RAISE;
1731:
1732: ELSE
1733:
1734: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1735:
1736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');

Line 1736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1732: ELSE
1733:
1734: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1735:
1736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1739: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_ID_LID');
1740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1739: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_ID_LID');
1740: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1741:
1742: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1743: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1744: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));

Line 1745: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1741:
1742: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1743: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1744: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1745: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1746:
1747: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1748: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
1749: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_INVENTORY_ITEM_ID));

Line 1750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1746:
1747: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1748: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
1749: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_INVENTORY_ITEM_ID));
1750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1751:
1752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1753:
1754: ERRBUF := sqlerrm;

Line 1752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1748: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
1749: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_INVENTORY_ITEM_ID));
1750: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1751:
1752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1753:
1754: ERRBUF := sqlerrm;
1755: RETCODE := MSC_UTIL.G_WARNING;
1756:

Line 1755: RETCODE := MSC_UTIL.G_WARNING;

1751:
1752: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1753:
1754: ERRBUF := sqlerrm;
1755: RETCODE := MSC_UTIL.G_WARNING;
1756:
1757: END IF;
1758:
1759: END;

Line 1766: IF lv_items_stat_stale = MSC_UTIL.SYS_NO AND lv_ins_records > lv_item_id_count * 0.2 THEN

1762:
1763: COMMIT;
1764:
1765: /* Bug 7653761 - If inserted records are more than 20% */
1766: IF lv_items_stat_stale = MSC_UTIL.SYS_NO AND lv_ins_records > lv_item_id_count * 0.2 THEN
1767: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1768: END IF;
1769:
1770: --========== CATEGORY SET ==========

Line 1767: lv_items_stat_stale := MSC_UTIL.SYS_YES;

1763: COMMIT;
1764:
1765: /* Bug 7653761 - If inserted records are more than 20% */
1766: IF lv_items_stat_stale = MSC_UTIL.SYS_NO AND lv_ins_records > lv_item_id_count * 0.2 THEN
1767: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1768: END IF;
1769:
1770: --========== CATEGORY SET ==========
1771:

Line 1807: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1803: NULL;
1804:
1805: WHEN OTHERS THEN
1806:
1807: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1808: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1809: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1810: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1807: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1808: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1809: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1810: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1812:
1813: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1814: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1815: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);

Line 1816: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1812:
1813: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1814: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1815: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1816: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1817:
1818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1819:
1820: ERRBUF := sqlerrm;

Line 1818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1814: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1815: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1816: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1817:
1818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1819:
1820: ERRBUF := sqlerrm;
1821: RETCODE := MSC_UTIL.G_ERROR;
1822: RAISE;

Line 1821: RETCODE := MSC_UTIL.G_ERROR;

1817:
1818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1819:
1820: ERRBUF := sqlerrm;
1821: RETCODE := MSC_UTIL.G_ERROR;
1822: RAISE;
1823:
1824: END;
1825:

Line 1852: IF lv_cat_stat_stale = MSC_UTIL.SYS_NO AND lv_ins_records > lv_cat_id_count * 0.2 THEN

1848:
1849: COMMIT;
1850:
1851: /* Bug 7653761 - If inserted records are more than 20%*/
1852: IF lv_cat_stat_stale = MSC_UTIL.SYS_NO AND lv_ins_records > lv_cat_id_count * 0.2 THEN
1853: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1854: END IF;
1855:
1856: /* in order to set default flag, access the lock on the table first*/

Line 1853: lv_cat_stat_stale := MSC_UTIL.SYS_YES;

1849: COMMIT;
1850:
1851: /* Bug 7653761 - If inserted records are more than 20%*/
1852: IF lv_cat_stat_stale = MSC_UTIL.SYS_NO AND lv_ins_records > lv_cat_id_count * 0.2 THEN
1853: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1854: END IF;
1855:
1856: /* in order to set default flag, access the lock on the table first*/
1857: LOCK TABLE MSC_CATEGORY_SETS IN EXCLUSIVE MODE;

Line 1887: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1883: WHEN OTHERS THEN
1884:
1885: IF SQLCODE IN (-1653,-1654) THEN
1886:
1887: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1888: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1889: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1890: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1891: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1891: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1887: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1888: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1889: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1890: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1891: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1892:
1893: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1894: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1895: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);

Line 1896: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1892:
1893: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1894: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1895: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1896: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1897:
1898: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1899:
1900: ERRBUF := sqlerrm;

Line 1898: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1894: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1895: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1896: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1897:
1898: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1899:
1900: ERRBUF := sqlerrm;
1901: RETCODE := MSC_UTIL.G_ERROR;
1902: RAISE;

Line 1901: RETCODE := MSC_UTIL.G_ERROR;

1897:
1898: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1899:
1900: ERRBUF := sqlerrm;
1901: RETCODE := MSC_UTIL.G_ERROR;
1902: RAISE;
1903:
1904: ELSE
1905:

Line 1906: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

1902: RAISE;
1903:
1904: ELSE
1905:
1906: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1907:
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1909: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1910: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');

Line 1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

1904: ELSE
1905:
1906: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1907:
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1909: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1910: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1911: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1912: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 1912: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1909: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1910: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1911: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_CATEGORY_SETS');
1912: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1913:
1914: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1915: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1916: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);

Line 1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

1913:
1914: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1915: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1916: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1918:
1919: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1920:
1921: ERRBUF := sqlerrm;

Line 1919: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1915: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_SET_NAME');
1916: FND_MESSAGE.SET_TOKEN('VALUE', c_rec.CATEGORY_SET_NAME);
1917: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1918:
1919: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1920:
1921: ERRBUF := sqlerrm;
1922: RETCODE := MSC_UTIL.G_WARNING;
1923:

Line 1922: RETCODE := MSC_UTIL.G_WARNING;

1918:
1919: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1920:
1921: ERRBUF := sqlerrm;
1922: RETCODE := MSC_UTIL.G_WARNING;
1923:
1924: END IF;
1925:
1926: END;

Line 1942: IF MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES THEN

1938:
1939: COMMIT;
1940:
1941: /* analyse the key mapping tables */
1942: IF MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES THEN
1943: IF lv_items_stat_stale = MSC_UTIL.SYS_YES THEN
1944: msc_analyse_tables_pk.analyse_table( 'MSC_ITEM_ID_LID');
1945: END IF;
1946: IF lv_cat_stat_stale = MSC_UTIL.SYS_YES THEN

Line 1943: IF lv_items_stat_stale = MSC_UTIL.SYS_YES THEN

1939: COMMIT;
1940:
1941: /* analyse the key mapping tables */
1942: IF MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES THEN
1943: IF lv_items_stat_stale = MSC_UTIL.SYS_YES THEN
1944: msc_analyse_tables_pk.analyse_table( 'MSC_ITEM_ID_LID');
1945: END IF;
1946: IF lv_cat_stat_stale = MSC_UTIL.SYS_YES THEN
1947: msc_analyse_tables_pk.analyse_table( 'MSC_CATEGORY_SET_ID_LID');

Line 1946: IF lv_cat_stat_stale = MSC_UTIL.SYS_YES THEN

1942: IF MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES THEN
1943: IF lv_items_stat_stale = MSC_UTIL.SYS_YES THEN
1944: msc_analyse_tables_pk.analyse_table( 'MSC_ITEM_ID_LID');
1945: END IF;
1946: IF lv_cat_stat_stale = MSC_UTIL.SYS_YES THEN
1947: msc_analyse_tables_pk.analyse_table( 'MSC_CATEGORY_SET_ID_LID');
1948: END IF;
1949: END IF;
1950:

Line 1955: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

1951: EXCEPTION
1952:
1953: WHEN OTHERS THEN
1954:
1955: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1956:
1957: ERRBUF := sqlerrm;
1958: RETCODE := MSC_UTIL.G_ERROR;
1959: RAISE;

Line 1958: RETCODE := MSC_UTIL.G_ERROR;

1954:
1955: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1956:
1957: ERRBUF := sqlerrm;
1958: RETCODE := MSC_UTIL.G_ERROR;
1959: RAISE;
1960:
1961: END GENERATE_ITEM_KEYS;
1962:

Line 2018: AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR

2014: AND mtsil.SR_INSTANCE_ID(+)= msis.SR_Instance_ID
2015: AND mtsil.Partner_Type(+)= 1
2016: /* SCE Change starts */
2017: -- Pull only valid records
2018: AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2019: -- Make a join with company_id
2020: -- If company_id is null then it means the record is owned by the Application
2021: -- owner company.
2022: AND nvl(msis.company_id, -1) = nvl(mtil.sr_company_id, -1)

Line 2055: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO

2051: AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2052: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2053: AND mtsil.Partner_Type(+)= 1
2054: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2055: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2056: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2057: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2058: from msc_supplier_capacities msc1
2059: where msc1.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID

Line 2056: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,

2052: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2053: AND mtsil.Partner_Type(+)= 1
2054: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2055: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2056: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2057: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2058: from msc_supplier_capacities msc1
2059: where msc1.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2060: and msc1.ORGANIZATION_ID = mssc.ORGANIZATION_ID

Line 2057: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0

2053: AND mtsil.Partner_Type(+)= 1
2054: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2055: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2056: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2057: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2058: from msc_supplier_capacities msc1
2059: where msc1.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2060: and msc1.ORGANIZATION_ID = mssc.ORGANIZATION_ID
2061: and msc1.INVENTORY_ITEM_ID = t1.INVENTORY_ITEM_ID

Line 2089: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_YES;

2085: AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2086: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2087: AND mtsil.Partner_Type(+)= 1
2088: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2089: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_YES;
2090:
2091: CURSOR c5d IS
2092: SELECT mssc.ORGANIZATION_ID,
2093: mssc.USING_ORGANIZATION_ID,

Line 2119: AND decode(MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag , MSC_UTIL.ASL_YES,1,0)= 1;

2115: AND msc1.organization_id = mssc.organization_id
2116: AND msc1.sr_instance_id = mssc.sr_instance_id
2117: AND mssc.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2118: AND msc1.collected_flag = 3
2119: AND decode(MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag , MSC_UTIL.ASL_YES,1,0)= 1;
2120:
2121: CURSOR c3 IS
2122: SELECT
2123: mtil.TP_ID SUPPLIER_ID,

Line 2161: AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR

2157: AND mtil.PARTNER_TYPE = 1
2158: AND mtsil.SR_TP_SITE_ID (+)= msis.Supplier_Site_ID
2159: AND mtsil.SR_INSTANCE_ID (+)= msis.SR_Instance_ID
2160: AND mtsil.Partner_Type (+)= 1
2161: AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2162: AND nvl(msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2163: AND nvl(msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2164: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2165: And msis.deleted_flag=MSC_UTIL.SYS_YES

Line 2165: And msis.deleted_flag=MSC_UTIL.SYS_YES

2161: AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2162: AND nvl(msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2163: AND nvl(msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2164: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2165: And msis.deleted_flag=MSC_UTIL.SYS_YES
2166: ;
2167:
2168:
2169: CURSOR c4 IS

Line 2217: AND nvl (msis.process_flag, -99) <> MSC_UTIL.G_ERROR

2213: AND mtil.PARTNER_TYPE = 1
2214: AND mtsil.SR_TP_SITE_ID (+)= msis.Supplier_Site_ID
2215: AND mtsil.SR_INSTANCE_ID (+)= msis.SR_Instance_ID
2216: AND mtsil.Partner_Type (+)= 1
2217: AND nvl (msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2218: AND nvl (msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2219: AND nvl (msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2220: AND nvl (msis.purchasing_unit_of_measure, '-99') = muom.unit_of_measure (+)
2221: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id

Line 2222: AND msis.deleted_flag=MSC_UTIL.SYS_NO ;

2218: AND nvl (msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2219: AND nvl (msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2220: AND nvl (msis.purchasing_unit_of_measure, '-99') = muom.unit_of_measure (+)
2221: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2222: AND msis.deleted_flag=MSC_UTIL.SYS_NO ;
2223:
2224: lv_table_name VARCHAR2(100);
2225: lv_sql_stmt VARCHAR2(5000);
2226: lv_last_asl_collection_date DATE;

Line 2235: IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN

2231:
2232: BEGIN
2233:
2234: /*ASL */
2235: IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN
2236: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
2237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2238: else
2239: lv_table_name:= 'MSC_SYSTEM_ITEMS';

Line 2237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);

2233:
2234: /*ASL */
2235: IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN
2236: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
2237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2238: else
2239: lv_table_name:= 'MSC_SYSTEM_ITEMS';
2240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2241: End if;

Line 2240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);

2236: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
2237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2238: else
2239: lv_table_name:= 'MSC_SYSTEM_ITEMS';
2240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2241: End if;
2242:
2243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name is ' || lv_table_name);
2244:

Line 2243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name is ' || lv_table_name);

2239: lv_table_name:= 'MSC_SYSTEM_ITEMS';
2240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2241: End if;
2242:
2243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name is ' || lv_table_name);
2244:
2245: /*ASL */
2246:
2247: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN

Line 2252: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

2248:
2249:
2250: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2251:
2252: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2253: v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2254: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2255: ELSE
2256: v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'

Line 2257: ||' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;

2253: v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2254: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2255: ELSE
2256: v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'
2257: ||' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2258: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2259: END IF;
2260:
2261: END IF;

Line 2315: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=

2311: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2312: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2313: AND USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID
2314: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2315: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2316: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2317:
2318: END IF;
2319:

Line 2316: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);

2312: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2313: AND USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID
2314: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2315: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2316: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2317:
2318: END IF;
2319:
2320: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN

Line 2422: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

2418: WHEN OTHERS THEN
2419:
2420: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2421:
2422: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2423: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2424: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2425: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 2426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2422: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2423: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2424: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2425: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2427:
2428: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2429: RAISE;
2430:

Line 2428: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

2424: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2425: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2426: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2427:
2428: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2429: RAISE;
2430:
2431: ELSE
2432: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

Line 2432: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

2428: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2429: RAISE;
2430:
2431: ELSE
2432: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2433:
2434: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2435: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2436: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');

Line 2434: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

2430:
2431: ELSE
2432: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2433:
2434: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2435: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2436: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2437: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2438: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 2438: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2434: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2435: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2436: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2437: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2438: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2439:
2440: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2441: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
2442: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));

Line 2443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2439:
2440: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2441: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
2442: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
2443: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2444:
2445: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2446: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2447: FND_MESSAGE.SET_TOKEN('VALUE',

Line 2450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2446: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2447: FND_MESSAGE.SET_TOKEN('VALUE',
2448: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2449: MSC_CL_COLLECTION.v_instance_id));
2450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2451:
2452: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2453: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
2454: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));

Line 2455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2451:
2452: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2453: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
2454: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));
2455: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2456:
2457: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2458: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
2459: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));

Line 2460: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2456:
2457: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2458: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
2459: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));
2460: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2461:
2462: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2463: END IF;
2464:

Line 2462: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

2458: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
2459: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));
2460: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2461:
2462: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2463: END IF;
2464:
2465: END;
2466:

Line 2475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');

2471:
2472: /* ASL */
2473: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and not MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2474:
2475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');
2476: /*NOT REQUIRED
2477: IF (MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag=MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.item_flag=MSC_UTIL.SYS_YES AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM LOAD_SUPPLIER_CAPACITY PROCEDURE ');
2479: ADD_NEW_IMPL_ITEM_ASL;

Line 2477: IF (MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag=MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.item_flag=MSC_UTIL.SYS_YES AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN

2473: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and not MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2474:
2475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');
2476: /*NOT REQUIRED
2477: IF (MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag=MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.item_flag=MSC_UTIL.SYS_YES AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM LOAD_SUPPLIER_CAPACITY PROCEDURE ');
2479: ADD_NEW_IMPL_ITEM_ASL;
2480: END IF ;
2481: */

Line 2478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM LOAD_SUPPLIER_CAPACITY PROCEDURE ');

2474:
2475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');
2476: /*NOT REQUIRED
2477: IF (MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag=MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.item_flag=MSC_UTIL.SYS_YES AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM LOAD_SUPPLIER_CAPACITY PROCEDURE ');
2479: ADD_NEW_IMPL_ITEM_ASL;
2480: END IF ;
2481: */
2482:

Line 2486: ||' And organization_id '|| MSC_UTIL.v_in_org_str;

2482:
2483: lv_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
2484: ||' From msc_instance_orgs '
2485: ||' Where sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2486: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
2487:
2488: EXECUTE IMMEDIATE lv_sql_stmt into lv_last_asl_collection_date;
2489: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'last successful ASL Collection refresh time is '||lv_last_asl_collection_date);
2490: c_count:= 0;

Line 2489: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'last successful ASL Collection refresh time is '||lv_last_asl_collection_date);

2485: ||' Where sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2486: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
2487:
2488: EXECUTE IMMEDIATE lv_sql_stmt into lv_last_asl_collection_date;
2489: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'last successful ASL Collection refresh time is '||lv_last_asl_collection_date);
2490: c_count:= 0;
2491: FOR del_rec in del_asl LOOP
2492: BEGIN
2493: lv_sql_stmt:= 'Delete MSC_ITEM_SUPPLIERS mis'

Line 2500: ||' AND mis.ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str;

2496: ||' AND mis.SUPPLIER_ID = ' || del_rec.SUPPLIER_ID
2497: ||' AND nvl(mis.SUPPLIER_SITE_ID, -1) = nvl( :SUPPLIER_SITE_ID , -1)'
2498: ||' AND mis.sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2499: ||' AND mis.plan_id = -1 '
2500: ||' AND mis.ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str;
2501:
2502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'delete query is ' || lv_sql_stmt);
2503:
2504: EXECUTE IMMEDIATE lv_sql_stmt using del_rec.SUPPLIER_SITE_ID;

Line 2502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'delete query is ' || lv_sql_stmt);

2498: ||' AND mis.sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2499: ||' AND mis.plan_id = -1 '
2500: ||' AND mis.ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str;
2501:
2502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'delete query is ' || lv_sql_stmt);
2503:
2504: EXECUTE IMMEDIATE lv_sql_stmt using del_rec.SUPPLIER_SITE_ID;
2505:
2506: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'the number of rows deleted '|| SQL%ROWCOUNT);

Line 2506: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'the number of rows deleted '|| SQL%ROWCOUNT);

2502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'delete query is ' || lv_sql_stmt);
2503:
2504: EXECUTE IMMEDIATE lv_sql_stmt using del_rec.SUPPLIER_SITE_ID;
2505:
2506: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'the number of rows deleted '|| SQL%ROWCOUNT);
2507: EXCEPTION
2508: WHEN OTHERS THEN
2509:
2510: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN

Line 2512: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

2508: WHEN OTHERS THEN
2509:
2510: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2511:
2512: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2513: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2514: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2515: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2512: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2513: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2514: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2515: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2517:
2518: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2519: RAISE;
2520:

Line 2518: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

2514: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2515: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2516: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2517:
2518: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2519: RAISE;
2520:
2521: ELSE
2522: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

Line 2522: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

2518: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2519: RAISE;
2520:
2521: ELSE
2522: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2523:
2524: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2525: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2526: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');

Line 2524: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

2520:
2521: ELSE
2522: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2523:
2524: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2525: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2526: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2527: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2528: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 2528: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2524: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2525: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2526: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2527: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2528: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2529:
2530: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2531: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
2532: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( del_rec.INVENTORY_ITEM_ID));

Line 2533: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2529:
2530: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2531: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
2532: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( del_rec.INVENTORY_ITEM_ID));
2533: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2534:
2535: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2536: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
2537: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(del_rec.SUPPLIER_ID));

Line 2538: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2534:
2535: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2536: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
2537: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(del_rec.SUPPLIER_ID));
2538: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2539:
2540: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2541: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
2542: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(del_rec.SUPPLIER_SITE_ID));

Line 2543: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2539:
2540: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2541: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
2542: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(del_rec.SUPPLIER_SITE_ID));
2543: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2544:
2545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2546: END IF ;
2547: END ;

Line 2545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

2541: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
2542: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(del_rec.SUPPLIER_SITE_ID));
2543: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2544:
2545: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2546: END IF ;
2547: END ;
2548:
2549: END LOOP;

Line 2550: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'number of row deleted ' || c_count);

2546: END IF ;
2547: END ;
2548:
2549: END LOOP;
2550: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'number of row deleted ' || c_count);
2551: COMMIT;
2552:
2553: /*Insert Logic */
2554: FOR c_rec in c4 LOOP

Line 2594: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=

2590: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2591: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2592: AND USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID
2593: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2594: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2595: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2596:
2597: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW UPDATED ' || SQL%ROWCOUNT);
2598:

Line 2595: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);

2591: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2592: AND USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID
2593: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2594: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2595: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2596:
2597: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW UPDATED ' || SQL%ROWCOUNT);
2598:
2599: IF SQL%NOTFOUND THEN

Line 2597: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW UPDATED ' || SQL%ROWCOUNT);

2593: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2594: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2595: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2596:
2597: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW UPDATED ' || SQL%ROWCOUNT);
2598:
2599: IF SQL%NOTFOUND THEN
2600: IF c_rec.using_organization_id =-1 THEN
2601: lv_sql_stmt:= 'INSERT INTO MSC_ITEM_SUPPLIERS'

Line 2680: ||' and msi.organization_id ' || MSC_UTIL.v_in_org_str

2676: ||' :v_current_user '
2677: ||' FROM '
2678: || lv_table_name ||' msi '
2679: ||' WHERE msi.inventory_item_id = '|| c_rec.INVENTORY_ITEM_ID
2680: ||' and msi.organization_id ' || MSC_UTIL.v_in_org_str
2681: ||' and msi.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
2682: ||' and msi.plan_id =-1 ' ;
2683:
2684: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'the insert statement is ' || lv_sql_stmt );

Line 2684: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'the insert statement is ' || lv_sql_stmt );

2680: ||' and msi.organization_id ' || MSC_UTIL.v_in_org_str
2681: ||' and msi.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
2682: ||' and msi.plan_id =-1 ' ;
2683:
2684: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'the insert statement is ' || lv_sql_stmt );
2685:
2686: execute immediate lv_sql_stmt using c_rec.USING_ORGANIZATION_ID,
2687: c_rec.SUPPLIER_ID,
2688: c_rec.SUPPLIER_SITE_ID,

Line 2719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING ORG -1 ' || SQL%ROWCOUNT);

2715: c_rec.FORECAST_HORIZON,
2716: c_rec.FIXED_ORDER_QUANTITY,
2717: MSC_CL_COLLECTION.v_current_user ;
2718:
2719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING ORG -1 ' || SQL%ROWCOUNT);
2720:
2721: ELSE
2722: INSERT INTO MSC_ITEM_SUPPLIERS
2723: ( PLAN_ID,

Line 2803: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING org id not -1 ' || SQL%ROWCOUNT);

2799: c_rec.FIXED_ORDER_QUANTITY,
2800: 1,
2801: MSC_CL_COLLECTION.v_current_user );
2802:
2803: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING org id not -1 ' || SQL%ROWCOUNT);
2804:
2805: END IF ;
2806: END IF ;
2807:

Line 2808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the item id ' || c_rec.INVENTORY_ITEM_ID);

2804:
2805: END IF ;
2806: END IF ;
2807:
2808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the item id ' || c_rec.INVENTORY_ITEM_ID);
2809: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the using organization_id is ' || c_rec.USING_ORGANIZATION_ID);
2810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier id is ' || c_rec.SUPPLIER_ID);
2811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier site id is ' || c_rec.SUPPLIER_SITE_ID);
2812:

Line 2809: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the using organization_id is ' || c_rec.USING_ORGANIZATION_ID);

2805: END IF ;
2806: END IF ;
2807:
2808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the item id ' || c_rec.INVENTORY_ITEM_ID);
2809: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the using organization_id is ' || c_rec.USING_ORGANIZATION_ID);
2810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier id is ' || c_rec.SUPPLIER_ID);
2811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier site id is ' || c_rec.SUPPLIER_SITE_ID);
2812:
2813: EXCEPTION

Line 2810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier id is ' || c_rec.SUPPLIER_ID);

2806: END IF ;
2807:
2808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the item id ' || c_rec.INVENTORY_ITEM_ID);
2809: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the using organization_id is ' || c_rec.USING_ORGANIZATION_ID);
2810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier id is ' || c_rec.SUPPLIER_ID);
2811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier site id is ' || c_rec.SUPPLIER_SITE_ID);
2812:
2813: EXCEPTION
2814: WHEN OTHERS THEN

Line 2811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier site id is ' || c_rec.SUPPLIER_SITE_ID);

2807:
2808: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the item id ' || c_rec.INVENTORY_ITEM_ID);
2809: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the using organization_id is ' || c_rec.USING_ORGANIZATION_ID);
2810: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier id is ' || c_rec.SUPPLIER_ID);
2811: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' the supplier site id is ' || c_rec.SUPPLIER_SITE_ID);
2812:
2813: EXCEPTION
2814: WHEN OTHERS THEN
2815:

Line 2818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

2814: WHEN OTHERS THEN
2815:
2816: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2817:
2818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2819: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2820: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2821: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 2822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2818: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2819: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2820: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2821: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2823:
2824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2825: RAISE;
2826:

Line 2824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

2820: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2821: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2822: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2823:
2824: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2825: RAISE;
2826:
2827: ELSE
2828:

Line 2829: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

2825: RAISE;
2826:
2827: ELSE
2828:
2829: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2830:
2831: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2832: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2833: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');

Line 2831: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

2827: ELSE
2828:
2829: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2830:
2831: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2832: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2833: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2834: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 2835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2831: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2832: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2833: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2834: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2836:
2837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2838: END IF;-- exception end
2839: END ; -- begin end

Line 2837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

2833: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2834: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUPPLIERS');
2835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2836:
2837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2838: END IF;-- exception end
2839: END ; -- begin end
2840: END LOOP; -- loop insert or update
2841: COMMIT ;

Line 2847: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND

2843: END IF ; -- v_is_incremental
2844:
2845: /*ASL */
2846:
2847: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2848: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2849:
2850: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2851:

Line 2848: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN

2844:
2845: /*ASL */
2846:
2847: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2848: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2849:
2850: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2851:
2852: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);

Line 2854: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

2850: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2851:
2852: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2853:
2854: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2855: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2856: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2857: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2858: else

Line 2855: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN

2851:
2852: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2853:
2854: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2855: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2856: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2857: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2858: else
2859: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);

Line 2862: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN

2858: else
2859: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2860: end if ;
2861: ELSE
2862: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2863: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2864: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2865: else
2866: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

Line 2863: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

2859: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2860: end if ;
2861: ELSE
2862: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2863: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2864: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2865: else
2866: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2867: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line 2866: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

2862: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2863: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2864: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2865: else
2866: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2867: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2868: end if ;
2869: END IF;
2870:

Line 2891: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=

2887: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2888: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2889: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2890: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2891: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2892: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2893: AND FROM_DATE= c_rec.FROM_DATE
2894: AND COLLECTED_FLAG=1;
2895:

Line 2892: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)

2888: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2889: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2890: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2891: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2892: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2893: AND FROM_DATE= c_rec.FROM_DATE
2894: AND COLLECTED_FLAG=1;
2895:
2896: END LOOP;

Line 2906: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=

2902: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2903: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2904: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2905: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2906: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2907: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2908: AND COLLECTED_FLAG=3;
2909:
2910: END LOOP;

Line 2907: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)

2903: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2904: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2905: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2906: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2907: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2908: AND COLLECTED_FLAG=3;
2909:
2910: END LOOP;
2911:

Line 2938: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=

2934: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2935: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2936: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2937: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2938: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2939: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2940: AND FROM_DATE= c_rec.FROM_DATE
2941: AND COLLECTED_FLAG=1;
2942:

Line 2939: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)

2935: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2936: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2937: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
2938: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
2939: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
2940: AND FROM_DATE= c_rec.FROM_DATE
2941: AND COLLECTED_FLAG=1;
2942:
2943: END IF;

Line 2999: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

2995: WHEN OTHERS THEN
2996:
2997: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2998:
2999: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3000: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3001: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3002: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3003: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 3003: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

2999: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3000: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3001: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3002: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3003: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3004:
3005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3006: RAISE;
3007:

Line 3005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

3001: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3002: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3003: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3004:
3005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3006: RAISE;
3007:
3008: ELSE
3009: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

Line 3009: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

3005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3006: RAISE;
3007:
3008: ELSE
3009: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3010:
3011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3012: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3013: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');

Line 3011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

3007:
3008: ELSE
3009: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3010:
3011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3012: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3013: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3014: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3015: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 3015: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3012: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3013: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3014: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3015: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3016:
3017: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3018: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3019: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));

Line 3020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3016:
3017: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3018: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3019: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3020: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3021:
3022: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3023: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3024: FND_MESSAGE.SET_TOKEN('VALUE',

Line 3027: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3023: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3024: FND_MESSAGE.SET_TOKEN('VALUE',
3025: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3026: MSC_CL_COLLECTION.v_instance_id));
3027: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3028:
3029: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3030: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3031: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));

Line 3032: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3028:
3029: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3030: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3031: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));
3032: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3033:
3034: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3035: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
3036: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));

Line 3037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3033:
3034: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3035: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
3036: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));
3037: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3038:
3039: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3040: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_DATE');
3041: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_DATE));

Line 3042: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3038:
3039: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3040: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_DATE');
3041: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_DATE));
3042: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3043:
3044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3045: END IF;
3046:

Line 3044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

3040: FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_DATE');
3041: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FROM_DATE));
3042: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3043:
3044: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3045: END IF;
3046:
3047: END;
3048:

Line 3057: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

3053: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3054:
3055: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3056:
3057: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3058: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3059: ELSE
3060: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3061: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line 3060: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

3056:
3057: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3058: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3059: ELSE
3060: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3061: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3062: END IF;
3063:
3064: END IF;

Line 3086: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=

3082: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3083: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3084: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3085: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
3086: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
3087: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
3088: AND FENCE_DAYS= c_rec.FENCE_DAYS;
3089:
3090: END IF;

Line 3087: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)

3083: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3084: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3085: AND SUPPLIER_ID= c_rec.SUPPLIER_ID
3086: AND NVL(SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)=
3087: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE)
3088: AND FENCE_DAYS= c_rec.FENCE_DAYS;
3089:
3090: END IF;
3091:

Line 3142: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

3138: WHEN OTHERS THEN
3139:
3140: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3141:
3142: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3143: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3144: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3145: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_FLEX_FENCES');
3146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 3146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3142: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3143: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3144: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3145: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_FLEX_FENCES');
3146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3147:
3148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3149: RAISE;
3150:

Line 3148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

3144: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3145: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_FLEX_FENCES');
3146: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3147:
3148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3149: RAISE;
3150:
3151: ELSE
3152: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

Line 3152: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

3148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3149: RAISE;
3150:
3151: ELSE
3152: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3153:
3154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3155: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3156: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');

Line 3154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

3150:
3151: ELSE
3152: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3153:
3154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3155: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3156: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3157: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_FLEX_FENCES');
3158: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 3158: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3155: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3156: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3157: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_FLEX_FENCES');
3158: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3159:
3160: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3161: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3162: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));

Line 3163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3159:
3160: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3161: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3162: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3163: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3164:
3165: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3166: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3167: FND_MESSAGE.SET_TOKEN('VALUE',

Line 3170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3166: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3167: FND_MESSAGE.SET_TOKEN('VALUE',
3168: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3169: MSC_CL_COLLECTION.v_instance_id));
3170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3171:
3172: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3173: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3174: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));

Line 3175: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3171:
3172: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3173: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3174: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_ID));
3175: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3176:
3177: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3178: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
3179: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));

Line 3180: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3176:
3177: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3178: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_SITE_ID');
3179: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SUPPLIER_SITE_ID));
3180: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3181:
3182: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3183: FND_MESSAGE.SET_TOKEN('COLUMN', 'FENCE_DAYS');
3184: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FENCE_DAYS));

Line 3185: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

3181:
3182: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3183: FND_MESSAGE.SET_TOKEN('COLUMN', 'FENCE_DAYS');
3184: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FENCE_DAYS));
3185: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3186:
3187: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3188: END IF;
3189:

Line 3187: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

3183: FND_MESSAGE.SET_TOKEN('COLUMN', 'FENCE_DAYS');
3184: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.FENCE_DAYS));
3185: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3186:
3187: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3188: END IF;
3189:
3190: END;
3191:

Line 3383: lv_MSC_CONFIGURATION VARCHAR2(10) := nvl(fnd_profile.value('MSC_X_CONFIGURATION'), MSC_UTIL.G_CONF_APS);

3379: lv_sql_stmt VARCHAR2(7500);
3380: lv_sql_ins vARCHAR2(8000);
3381: lb_FetchComplete Boolean;
3382: ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
3383: lv_MSC_CONFIGURATION VARCHAR2(10) := nvl(fnd_profile.value('MSC_X_CONFIGURATION'), MSC_UTIL.G_CONF_APS);
3384:
3385: TYPE CharTblTyp IS TABLE OF VARCHAR2(270);
3386: TYPE NumTblTyp IS TABLE OF NUMBER;
3387: TYPE dateTblTyp IS TABLE OF DATE;

Line 3528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN

3524: lv_retcode NUMBER;
3525:
3526: BEGIN
3527:
3528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3529: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
3530: ELSE
3531: lv_tbl:= 'MSC_SYSTEM_ITEMS';
3532: END IF;

Line 3816: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN

3812: ||' :PEGGING_DEMAND_WINDOW_DAYS,'
3813: ||' :PEGGING_SUPPLY_WINDOW_DAYS )';
3814:
3815: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3816: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3817:
3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3819: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3820: ELSE

Line 3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN

3814:
3815: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3816: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3817:
3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3819: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3820: ELSE
3821: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3822: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);

Line 3821: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;

3817:
3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3819: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3820: ELSE
3821: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3822: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3823: END IF;
3824: END IF;
3825:

Line 4132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

4128: commit;
4129: EXCEPTION
4130: WHEN OTHERS THEN
4131:
4132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4133: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4134: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4135: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 4136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

4132: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4133: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4134: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4135: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4137:
4138: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4139: RAISE;
4140:

Line 4138: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

4134: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4135: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4136: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4137:
4138: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4139: RAISE;
4140:
4141: END;
4142: END IF;

Line 4145: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR

4141: END;
4142: END IF;
4143:
4144: IF (MSC_CL_COLLECTION.v_is_incremental_refresh OR
4145: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR
4146: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
4147: c_count :=0;
4148:
4149: OPEN c1;

Line 4146: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN

4142: END IF;
4143:
4144: IF (MSC_CL_COLLECTION.v_is_incremental_refresh OR
4145: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR
4146: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
4147: c_count :=0;
4148:
4149: OPEN c1;
4150: IF (c1%ISOPEN) THEN

Line 4601: IF ( lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR

4597: lb_PEGGING_SUPPLY_WINDOW_DAYS(j);
4598:
4599: END IF;
4600: END IF;
4601: IF ( lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR
4602: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
4603: UPDATE MSC_ITEMS
4604: SET description= lb_DESCRIPTION(j)
4605: WHERE inventory_item_id = lb_INVENTORY_ITEM_ID(j);

Line 4602: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN

4598:
4599: END IF;
4600: END IF;
4601: IF ( lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR
4602: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
4603: UPDATE MSC_ITEMS
4604: SET description= lb_DESCRIPTION(j)
4605: WHERE inventory_item_id = lb_INVENTORY_ITEM_ID(j);
4606: END IF;

Line 4621: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

4617: EXCEPTION
4618: WHEN OTHERS THEN
4619: IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
4620:
4621: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4622: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4623: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4624: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4625: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 4625: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

4621: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4622: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4623: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4624: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4625: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4626:
4627: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4628: RAISE;
4629:

Line 4627: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

4623: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4624: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4625: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4626:
4627: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4628: RAISE;
4629:
4630: ELSE
4631:

Line 4632: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

4628: RAISE;
4629:
4630: ELSE
4631:
4632: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4633:
4634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4635: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4636: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');

Line 4634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');

4630: ELSE
4631:
4632: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4633:
4634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4635: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4636: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4637: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4638: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

Line 4638: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

4634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4635: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4636: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4637: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SYSTEM_ITEMS');
4638: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4639:
4640: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4641: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
4642: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lb_INVENTORY_ITEM_ID(j)));

Line 4643: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

4639:
4640: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4641: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
4642: FND_MESSAGE.SET_TOKEN('VALUE', ITEM_NAME( lb_INVENTORY_ITEM_ID(j)));
4643: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4644:
4645: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4646: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
4647: FND_MESSAGE.SET_TOKEN('VALUE',

Line 4650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

4646: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
4647: FND_MESSAGE.SET_TOKEN('VALUE',
4648: MSC_GET_NAME.ORG_CODE( lb_ORGANIZATION_ID(j),
4649: MSC_CL_COLLECTION.v_instance_id));
4650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4651:
4652: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4653: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
4654: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_SR_INVENTORY_ITEM_ID(j)));

Line 4655: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);

4651:
4652: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4653: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
4654: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_SR_INVENTORY_ITEM_ID(j)));
4655: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4656:
4657: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4658: END IF;
4659:

Line 4657: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

4653: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
4654: FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_SR_INVENTORY_ITEM_ID(j)));
4655: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4656:
4657: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4658: END IF;
4659:
4660: END;
4661: END LOOP;

Line 4671: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN

4667: END IF;
4668:
4669: BEGIN
4670:
4671: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
4672:
4673: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
4674:
4675: lv_sql_stmt:=

Line 4676: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl

4672:
4673: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
4674:
4675: lv_sql_stmt:=
4676: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
4677: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SYSTEM_ITEMS'
4678: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4679: ||' AND plan_id = -1 '
4680: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;

Line 4677: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SYSTEM_ITEMS'

4673: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
4674:
4675: lv_sql_stmt:=
4676: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
4677: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SYSTEM_ITEMS'
4678: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4679: ||' AND plan_id = -1 '
4680: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4681:

Line 4680: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;

4676: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
4677: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SYSTEM_ITEMS'
4678: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4679: ||' AND plan_id = -1 '
4680: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4681:
4682: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4683: EXECUTE IMMEDIATE lv_sql_stmt;
4684:

Line 4682: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);

4678: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4679: ||' AND plan_id = -1 '
4680: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4681:
4682: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4683: EXECUTE IMMEDIATE lv_sql_stmt;
4684:
4685: COMMIT;
4686:

Line 4689: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN

4685: COMMIT;
4686:
4687: END IF;
4688:
4689: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4690: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4691: lv_retcode,
4692: 'MSC_SYSTEM_ITEMS',
4693: MSC_CL_COLLECTION.v_INSTANCE_CODE,

Line 4694: MSC_UTIL.G_ERROR

4690: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4691: lv_retcode,
4692: 'MSC_SYSTEM_ITEMS',
4693: MSC_CL_COLLECTION.v_INSTANCE_CODE,
4694: MSC_UTIL.G_ERROR
4695: );
4696:
4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);

Line 4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN

4693: MSC_CL_COLLECTION.v_INSTANCE_CODE,
4694: MSC_UTIL.G_ERROR
4695: );
4696:
4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4699: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4700: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4701: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

Line 4698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);

4694: MSC_UTIL.G_ERROR
4695: );
4696:
4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4699: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4700: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4701: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4702: END IF;

Line 4700: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN

4696:
4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4699: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4700: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4701: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4702: END IF;
4703:
4704: END IF;

Line 4701: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;

4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4699: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4700: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4701: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4702: END IF;
4703:
4704: END IF;
4705:

Line 4708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM ITEM PROCEDURE ');

4704: END IF;
4705:
4706: /*call to insert ASL */
4707: IF ( NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
4708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM ITEM PROCEDURE ');
4709: ADD_NEW_IMPL_ITEM_ASL;
4710: END IF ;
4711:
4712: EXCEPTION

Line 4715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);

4711:
4712: EXCEPTION
4713: WHEN OTHERS THEN
4714:
4715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4716: RAISE;
4717: END;
4718: COMMIT;
4719: