[Home] [Help]
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';
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';
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 '
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:
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 ';
83:
84: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'the sql statement is ' || lv_sql_stmt);
85:
181: || ' MAX_MINMAX_DAYS,'
182: || ' FORECAST_HORIZON,'
183: || ' FIXED_ORDER_QUANTITY'
184: || ' FROM MSC_ITEM_SUPPLIERS '
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
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;
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');
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',
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:
255: update MSC_SYSTEM_ITEMS
256: set VARIABLE_LEAD_TIME=0,
257: FIXED_LEAD_TIME=0
258: where PLAN_ID=-1
259: and SR_INSTANCE_ID=MSC_CL_COLLECTION.v_instance_id
260: and (INVENTORY_ITEM_ID,organization_id) in (
261: select current_item.inventory_item_id,current_item.organization_id
262: from
263: msc_routings model_routing,
269: and model_routing.PLAN_ID=-1
270: and option_class_routing.PLAN_ID=-1
271: and model_item.PLAN_ID=-1
272: and current_item.PLAN_ID=-1
273: and current_item.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
274: and model_routing.SR_INSTANCE_ID=current_item.SR_INSTANCE_ID
275: and option_class_routing.SR_INSTANCE_ID=current_item.SR_INSTANCE_ID
276: and model_item.SR_INSTANCE_ID=current_item.SR_INSTANCE_ID
277: and model_routing.ORGANIZATION_ID = current_item.ORGANIZATION_ID
295: lv_temp_sql_stmt VARCHAR2(2000);
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 );
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
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);
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);
307: END IF;
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
310:
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,'
319: ||' :v_current_date,'
320: ||' :v_current_user,'
321: ||' msa.sr_assignment_group_id'
322: ||' FROM MSC_ST_ABC_CLASSES msa'
323: ||' WHERE msa.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id;
324:
325: ELSE
326:
327: lv_temp_sql_stmt :=
335: ||' :v_current_date,'
336: ||' :v_current_user,'
337: ||' NULL'
338: ||' FROM MSC_ST_SYSTEM_ITEMS msi'
339: ||' WHERE msi.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
340: ||' AND msi.abc_class_id is not null'
341: ||' AND msi.abc_class_name is not null';
342:
343: END IF;
356: ||' CREATED_BY,'
357: ||' SR_ASSIGNMENT_GROUP_ID)'
358: ||lv_temp_sql_stmt;
359:
360: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
361:
362:
363: COMMIT;
364:
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');
433: AND tp.PARTNER_TYPE(+) = 2
434: AND tps.SR_TP_SITE_ID(+)= msis.CUSTOMER_SITE_ID
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
457: WHERE t1.SR_INVENTORY_ITEM_ID= msis.HIGHER_ITEM_ID
458: AND t1.SR_INSTANCE_ID= msis.SR_INSTANCE_ID
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);
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;
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;
492:
635: ||' AND tp.PARTNER_TYPE(+) = 2'
636: ||' AND tps.SR_TP_SITE_ID(+)= msis.CUSTOMER_SITE_ID'
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:
687: ||' WHERE t1.SR_INVENTORY_ITEM_ID= msis.HIGHER_ITEM_ID'
688: ||' AND t1.SR_INSTANCE_ID= msis.SR_INSTANCE_ID'
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
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
698: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
699: ELSE
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;
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);
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);
702: END IF;
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:
705: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
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:
705: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
706:
707: begin
708:
709: EXECUTE IMMEDIATE lv_cursor_stmt
706:
707: begin
708:
709: EXECUTE IMMEDIATE lv_cursor_stmt
710: USING MSC_CL_COLLECTION.v_last_collection_id,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
707: begin
708:
709: EXECUTE IMMEDIATE lv_cursor_stmt
710: USING MSC_CL_COLLECTION.v_last_collection_id,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user;
708:
709: EXECUTE IMMEDIATE lv_cursor_stmt
710: USING MSC_CL_COLLECTION.v_last_collection_id,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user;
716:
709: EXECUTE IMMEDIATE lv_cursor_stmt
710: USING MSC_CL_COLLECTION.v_last_collection_id,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user;
716:
717: EXCEPTION
710: USING MSC_CL_COLLECTION.v_last_collection_id,
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user;
716:
717: EXCEPTION
718: WHEN OTHERS THEN
711: MSC_CL_COLLECTION.v_current_date,
712: MSC_CL_COLLECTION.v_current_user,
713: MSC_CL_COLLECTION.v_current_user,
714: MSC_CL_COLLECTION.v_current_date,
715: MSC_CL_COLLECTION.v_current_user;
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
725:
726: begin
727: EXECUTE IMMEDIATE lv_cursor_stmt1
728: USING MSC_CL_COLLECTION.v_last_collection_id,
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,
729: MSC_CL_COLLECTION.v_current_date,
730: MSC_CL_COLLECTION.v_current_user,
731: MSC_CL_COLLECTION.v_current_user,
732: MSC_CL_COLLECTION.v_current_date,
725:
726: begin
727: EXECUTE IMMEDIATE lv_cursor_stmt1
728: USING MSC_CL_COLLECTION.v_last_collection_id,
729: MSC_CL_COLLECTION.v_current_date,
730: MSC_CL_COLLECTION.v_current_user,
731: MSC_CL_COLLECTION.v_current_user,
732: MSC_CL_COLLECTION.v_current_date,
733: MSC_CL_COLLECTION.v_current_user;
726: begin
727: EXECUTE IMMEDIATE lv_cursor_stmt1
728: USING MSC_CL_COLLECTION.v_last_collection_id,
729: MSC_CL_COLLECTION.v_current_date,
730: MSC_CL_COLLECTION.v_current_user,
731: MSC_CL_COLLECTION.v_current_user,
732: MSC_CL_COLLECTION.v_current_date,
733: MSC_CL_COLLECTION.v_current_user;
734:
727: EXECUTE IMMEDIATE lv_cursor_stmt1
728: USING MSC_CL_COLLECTION.v_last_collection_id,
729: MSC_CL_COLLECTION.v_current_date,
730: MSC_CL_COLLECTION.v_current_user,
731: MSC_CL_COLLECTION.v_current_user,
732: MSC_CL_COLLECTION.v_current_date,
733: MSC_CL_COLLECTION.v_current_user;
734:
735: EXCEPTION
728: USING MSC_CL_COLLECTION.v_last_collection_id,
729: MSC_CL_COLLECTION.v_current_date,
730: MSC_CL_COLLECTION.v_current_user,
731: MSC_CL_COLLECTION.v_current_user,
732: MSC_CL_COLLECTION.v_current_date,
733: MSC_CL_COLLECTION.v_current_user;
734:
735: EXCEPTION
736: WHEN OTHERS THEN
729: MSC_CL_COLLECTION.v_current_date,
730: MSC_CL_COLLECTION.v_current_user,
731: MSC_CL_COLLECTION.v_current_user,
732: MSC_CL_COLLECTION.v_current_date,
733: MSC_CL_COLLECTION.v_current_user;
734:
735: EXCEPTION
736: WHEN OTHERS THEN
737: -- LOG_MESSAGE(SQLERRM);
742: END IF ;
743:
744: END IF;
745:
746: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
747: --Net Change
748: LOOP
749: DELETE FROM MSC_ITEM_SUBSTITUTES
750: WHERE ( HIGHER_ITEM_ID, LOWER_ITEM_ID, ORGANIZATION_ID, RELATIONSHIP_TYPE) in
757: AND t1.SR_INSTANCE_ID= msis.SR_INSTANCE_ID
758: AND t2.SR_INVENTORY_ITEM_ID = msis.LOWER_ITEM_ID
759: --AND plan_id = -1
760: AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
761: AND msis.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
762: AND DELETED_FLAG =1 )
763: AND PLAN_ID =-1
764: AND SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
765: AND ROWNUM <1000;
760: AND t2.SR_INSTANCE_ID = msis.SR_INSTANCE_ID
761: AND msis.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
762: AND DELETED_FLAG =1 )
763: AND PLAN_ID =-1
764: AND SR_INSTANCE_ID =MSC_CL_COLLECTION.v_instance_id
765: AND ROWNUM <1000;
766:
767: COMMIT;
768: EXIT WHEN SQL%ROWCOUNT =0 ;
811: CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
812: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
813: DISABLE_DATE = lb_DISABLE_DATE(j) ,
814: PARTIAL_FULFILLMENT_FLAG = lb_PARTIAL_FULFILLMENT_FLAG(j),
815: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
816: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
817: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
818: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
819: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
812: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
813: DISABLE_DATE = lb_DISABLE_DATE(j) ,
814: PARTIAL_FULFILLMENT_FLAG = lb_PARTIAL_FULFILLMENT_FLAG(j),
815: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
816: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
817: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
818: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
819: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
820: WHERE
813: DISABLE_DATE = lb_DISABLE_DATE(j) ,
814: PARTIAL_FULFILLMENT_FLAG = lb_PARTIAL_FULFILLMENT_FLAG(j),
815: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
816: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
817: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
818: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
819: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
820: WHERE
821: PLAN_ID = -1
814: PARTIAL_FULFILLMENT_FLAG = lb_PARTIAL_FULFILLMENT_FLAG(j),
815: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
816: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
817: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
818: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
819: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
820: WHERE
821: PLAN_ID = -1
822: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
815: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
816: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
817: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
818: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
819: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
820: WHERE
821: PLAN_ID = -1
822: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
823: AND HIGHER_ITEM_ID = lb_HIGHER_ITEM_ID(j)
818: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
819: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
820: WHERE
821: PLAN_ID = -1
822: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
823: AND HIGHER_ITEM_ID = lb_HIGHER_ITEM_ID(j)
824: AND LOWER_ITEM_ID = lb_LOWER_ITEM_ID(j)
825: AND ORGANIZATION_ID = lb_ORGANIZATION_ID(j)
826: AND RELATIONSHIP_TYPE = lb_RELATIONSHIP_TYPE(j);
837: lb_EFFECTIVE_DATE(j),
838: lb_DISABLE_DATE(j),
839: lb_RELATIONSHIP_TYPE(j),
840: lb_PARTIAL_FULFILLMENT_FLAG(j),
841: MSC_CL_COLLECTION.v_last_collection_id,
842: MSC_CL_COLLECTION.v_instance_id,
843: lb_ORGANIZATION_ID(j),
844: MSC_CL_COLLECTION.v_current_date,
845: MSC_CL_COLLECTION.v_current_user,
838: lb_DISABLE_DATE(j),
839: lb_RELATIONSHIP_TYPE(j),
840: lb_PARTIAL_FULFILLMENT_FLAG(j),
841: MSC_CL_COLLECTION.v_last_collection_id,
842: MSC_CL_COLLECTION.v_instance_id,
843: lb_ORGANIZATION_ID(j),
844: MSC_CL_COLLECTION.v_current_date,
845: MSC_CL_COLLECTION.v_current_user,
846: MSC_CL_COLLECTION.v_current_user,
840: lb_PARTIAL_FULFILLMENT_FLAG(j),
841: MSC_CL_COLLECTION.v_last_collection_id,
842: MSC_CL_COLLECTION.v_instance_id,
843: lb_ORGANIZATION_ID(j),
844: MSC_CL_COLLECTION.v_current_date,
845: MSC_CL_COLLECTION.v_current_user,
846: MSC_CL_COLLECTION.v_current_user,
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user;
841: MSC_CL_COLLECTION.v_last_collection_id,
842: MSC_CL_COLLECTION.v_instance_id,
843: lb_ORGANIZATION_ID(j),
844: MSC_CL_COLLECTION.v_current_date,
845: MSC_CL_COLLECTION.v_current_user,
846: MSC_CL_COLLECTION.v_current_user,
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user;
849: EXCEPTION
842: MSC_CL_COLLECTION.v_instance_id,
843: lb_ORGANIZATION_ID(j),
844: MSC_CL_COLLECTION.v_current_date,
845: MSC_CL_COLLECTION.v_current_user,
846: MSC_CL_COLLECTION.v_current_user,
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user;
849: EXCEPTION
850: WHEN OTHERS THEN
843: lb_ORGANIZATION_ID(j),
844: MSC_CL_COLLECTION.v_current_date,
845: MSC_CL_COLLECTION.v_current_user,
846: MSC_CL_COLLECTION.v_current_user,
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user;
849: EXCEPTION
850: WHEN OTHERS THEN
851: -- LOG_MESSAGE(SQLERRM);
844: MSC_CL_COLLECTION.v_current_date,
845: MSC_CL_COLLECTION.v_current_user,
846: MSC_CL_COLLECTION.v_current_user,
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user;
849: EXCEPTION
850: WHEN OTHERS THEN
851: -- LOG_MESSAGE(SQLERRM);
852: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
854: end;
855: END IF;
856:
857: c_count:= c_count+1;
858: IF c_count > MSC_CL_COLLECTION.PBS THEN
859: COMMIT;
860: -- LOG_MESSAGE('The total record count inserted is '||TO_CHAR(c_count));
861: c_count:= 0;
862: END IF;
868:
869: lb_FetchComplete := FALSE;
870:
871:
872: 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
873: Open c2;
874:
875: IF (c2%ISOPEN) THEN
876: LOOP
904: SET
905: RECIPROCAL_FLAG = lb_RECIPROCAL_FLAG(j),
906: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
907: DISABLE_DATE = lb_DISABLE_DATE(j),
908: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
909: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
910: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
911: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
912: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
905: RECIPROCAL_FLAG = lb_RECIPROCAL_FLAG(j),
906: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
907: DISABLE_DATE = lb_DISABLE_DATE(j),
908: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
909: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
910: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
911: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
912: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
913: WHERE
906: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
907: DISABLE_DATE = lb_DISABLE_DATE(j),
908: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
909: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
910: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
911: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
912: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
913: WHERE
914: PLAN_ID = -1
907: DISABLE_DATE = lb_DISABLE_DATE(j),
908: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
909: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
910: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
911: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
912: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
913: WHERE
914: PLAN_ID = -1
915: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
908: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
909: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
910: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
911: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
912: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
913: WHERE
914: PLAN_ID = -1
915: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
916: AND HIGHER_ITEM_ID = lb_HIGHER_ITEM_ID(j)
911: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
912: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
913: WHERE
914: PLAN_ID = -1
915: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
916: AND HIGHER_ITEM_ID = lb_HIGHER_ITEM_ID(j)
917: AND LOWER_ITEM_ID = lb_LOWER_ITEM_ID(j)
918: AND ORGANIZATION_ID = lb_ORGANIZATION_ID(j)
919: AND RELATIONSHIP_TYPE = lb_RELATIONSHIP_TYPE(j);
929: lb_EFFECTIVE_DATE(j),
930: lb_DISABLE_DATE(j),
931: lb_RELATIONSHIP_TYPE(j),
932: lb_PARTIAL_FULFILLMENT_FLAG(j),
933: MSC_CL_COLLECTION.v_last_collection_id,
934: MSC_CL_COLLECTION.v_instance_id,
935: lb_ORGANIZATION_ID(j),
936: MSC_CL_COLLECTION.v_current_date,
937: MSC_CL_COLLECTION.v_current_user,
930: lb_DISABLE_DATE(j),
931: lb_RELATIONSHIP_TYPE(j),
932: lb_PARTIAL_FULFILLMENT_FLAG(j),
933: MSC_CL_COLLECTION.v_last_collection_id,
934: MSC_CL_COLLECTION.v_instance_id,
935: lb_ORGANIZATION_ID(j),
936: MSC_CL_COLLECTION.v_current_date,
937: MSC_CL_COLLECTION.v_current_user,
938: MSC_CL_COLLECTION.v_current_user,
932: lb_PARTIAL_FULFILLMENT_FLAG(j),
933: MSC_CL_COLLECTION.v_last_collection_id,
934: MSC_CL_COLLECTION.v_instance_id,
935: lb_ORGANIZATION_ID(j),
936: MSC_CL_COLLECTION.v_current_date,
937: MSC_CL_COLLECTION.v_current_user,
938: MSC_CL_COLLECTION.v_current_user,
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user;
933: MSC_CL_COLLECTION.v_last_collection_id,
934: MSC_CL_COLLECTION.v_instance_id,
935: lb_ORGANIZATION_ID(j),
936: MSC_CL_COLLECTION.v_current_date,
937: MSC_CL_COLLECTION.v_current_user,
938: MSC_CL_COLLECTION.v_current_user,
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user;
941:
934: MSC_CL_COLLECTION.v_instance_id,
935: lb_ORGANIZATION_ID(j),
936: MSC_CL_COLLECTION.v_current_date,
937: MSC_CL_COLLECTION.v_current_user,
938: MSC_CL_COLLECTION.v_current_user,
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user;
941:
942: EXCEPTION
935: lb_ORGANIZATION_ID(j),
936: MSC_CL_COLLECTION.v_current_date,
937: MSC_CL_COLLECTION.v_current_user,
938: MSC_CL_COLLECTION.v_current_user,
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user;
941:
942: EXCEPTION
943: WHEN OTHERS THEN
936: MSC_CL_COLLECTION.v_current_date,
937: MSC_CL_COLLECTION.v_current_user,
938: MSC_CL_COLLECTION.v_current_user,
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user;
941:
942: EXCEPTION
943: WHEN OTHERS THEN
944: -- LOG_MESSAGE(SQLERRM);
947: end;
948: END IF;
949:
950: c_count:= c_count+1;
951: IF c_count > MSC_CL_COLLECTION.PBS THEN
952: COMMIT;
953: -- LOG_MESSAGE('The total record count inserted is '||TO_CHAR(c_count));
954: c_count:= 0;
955: END IF;
962: CLOSE c2;
963: END IF; -- End of Net Change
964:
965:
966: 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
967:
968: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
969:
970: lv_sql_stmt:=
964:
965:
966: 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
967:
968: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
969:
970: lv_sql_stmt:=
971: 'INSERT INTO '||lv_tbl
972: ||' SELECT * from MSC_ITEM_SUBSTITUTES'
969:
970: lv_sql_stmt:=
971: 'INSERT INTO '||lv_tbl
972: ||' SELECT * from MSC_ITEM_SUBSTITUTES'
973: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
974: ||' AND plan_id = -1 '
975: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
976:
977: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
980: COMMIT;
981:
982: END IF;
983:
984: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
985: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
986: lv_retcode,
987: 'MSC_ITEM_SUBSTITUTES',
988: MSC_CL_COLLECTION.v_INSTANCE_CODE,
981:
982: END IF;
983:
984: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
985: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
986: lv_retcode,
987: 'MSC_ITEM_SUBSTITUTES',
988: MSC_CL_COLLECTION.v_INSTANCE_CODE,
989: MSC_UTIL.G_WARNING
984: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
985: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
986: lv_retcode,
987: 'MSC_ITEM_SUBSTITUTES',
988: MSC_CL_COLLECTION.v_INSTANCE_CODE,
989: MSC_UTIL.G_WARNING
990: );
991:
992:
991:
992:
993: IF lv_retcode = MSC_UTIL.G_WARNING THEN
994: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
995: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
996: END IF;
997:
998: END IF;
999:
1012: RAISE;
1013:
1014: ELSE
1015:
1016: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1017:
1018: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1019: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1020: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1062: WHERE t1.SR_INVENTORY_ITEM_ID= msic.inventory_item_id
1063: AND t1.sr_instance_id= msic.sr_instance_id
1064: AND mcsil.SR_Category_Set_ID= msic.SR_Category_Set_ID
1065: AND mcsil.SR_Instance_ID= msic.SR_Instance_ID
1066: AND msic.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1067: AND msic.DELETED_FLAG= MSC_UTIL.SYS_NO;
1068:
1069: CURSOR c1_d IS
1070: SELECT
1079: WHERE t1.SR_INVENTORY_ITEM_ID= msic.inventory_item_id
1080: AND t1.sr_instance_id= msic.sr_instance_id
1081: AND mcsil.SR_Category_Set_ID= msic.SR_Category_Set_ID
1082: AND mcsil.SR_Instance_ID= msic.SR_Instance_ID
1083: AND msic.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1084: AND msic.DELETED_FLAG= MSC_UTIL.SYS_YES;
1085:
1086: c_count NUMBER:= 0;
1087: lv_tbl VARCHAR2(30);
1111: lv_retcode NUMBER;
1112:
1113: BEGIN
1114:
1115: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1116: -- We want to delete all CATEGORY related data and get new stuff.
1117:
1118: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1119: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1114:
1115: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1116: -- We want to delete all CATEGORY related data and get new stuff.
1117:
1118: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1119: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1120:
1121: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1122: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1115: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1116: -- We want to delete all CATEGORY related data and get new stuff.
1117:
1118: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1119: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1120:
1121: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1122: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123: ELSE
1117:
1118: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1119: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1120:
1121: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1122: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123: ELSE
1124: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);
1118: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1119: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1120:
1121: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1122: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123: ELSE
1124: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);
1126: END IF;
1121: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1122: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123: ELSE
1124: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);
1126: END IF;
1127: end if;
1128:
1129: END IF;
1127: end if;
1128:
1129: END IF;
1130:
1131: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1132: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1133: ELSE
1134: lv_tbl:= 'MSC_ITEM_CATEGORIES';
1135: END IF;
1128:
1129: END IF;
1130:
1131: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1132: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1133: ELSE
1134: lv_tbl:= 'MSC_ITEM_CATEGORIES';
1135: END IF;
1136:
1133: ELSE
1134: lv_tbl:= 'MSC_ITEM_CATEGORIES';
1135: END IF;
1136:
1137: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1138:
1139: BEGIN
1140:
1141: lv_sql_ins :=
1182: ||' WHERE t1.SR_INVENTORY_ITEM_ID = msic.inventory_item_id '
1183: ||' AND t1.sr_instance_id = msic.sr_instance_id '
1184: ||' AND mcsil.SR_Category_Set_ID = msic.SR_Category_Set_ID '
1185: ||' AND mcsil.SR_Instance_ID = msic.SR_Instance_ID '
1186: ||' AND msic.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
1187:
1188: EXECUTE IMMEDIATE lv_sql_ins
1189: USING 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;
1190:
1185: ||' AND mcsil.SR_Instance_ID = msic.SR_Instance_ID '
1186: ||' AND msic.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
1187:
1188: EXECUTE IMMEDIATE lv_sql_ins
1189: USING 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;
1190:
1191: commit;
1192: EXCEPTION
1193: WHEN OTHERS THEN
1299: FOR j IN 1..lb_inventory_item_id.COUNT LOOP
1300:
1301: BEGIN
1302:
1303: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1304:
1305: UPDATE MSC_ITEM_CATEGORIES
1306: SET
1307: CATEGORY_NAME = lb_category_name(j),
1314: /* SR_CATEGORY_ID= lb_sr_category_id(j), --If the item is assigned to more than 1 category in the same category-set then,
1315: only one row of that category set will be repeatedly updated and the other
1316: item categories will not be inserted. Moving this into the WHERE clause.
1317: */
1318: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1319: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1320: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1321: WHERE ORGANIZATION_ID = lb_organization_id(j)
1322: AND INVENTORY_ITEM_ID = lb_inventory_item_id(J)
1315: only one row of that category set will be repeatedly updated and the other
1316: item categories will not be inserted. Moving this into the WHERE clause.
1317: */
1318: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1319: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1320: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1321: WHERE ORGANIZATION_ID = lb_organization_id(j)
1322: AND INVENTORY_ITEM_ID = lb_inventory_item_id(J)
1323: AND CATEGORY_SET_ID = lb_category_set_id(j)
1316: item categories will not be inserted. Moving this into the WHERE clause.
1317: */
1318: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1319: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1320: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1321: WHERE ORGANIZATION_ID = lb_organization_id(j)
1322: AND INVENTORY_ITEM_ID = lb_inventory_item_id(J)
1323: AND CATEGORY_SET_ID = lb_category_set_id(j)
1324: AND SR_INSTANCE_ID = lb_sr_instance_id(j)
1340: lb_start_date_active(j),
1341: lb_end_date_active(J),
1342: lb_sr_instance_id(j),
1343: lb_sr_category_id(j),
1344: MSC_CL_COLLECTION.v_last_collection_id,
1345: MSC_CL_COLLECTION.v_current_date,
1346: MSC_CL_COLLECTION.v_current_user,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user ;
1341: lb_end_date_active(J),
1342: lb_sr_instance_id(j),
1343: lb_sr_category_id(j),
1344: MSC_CL_COLLECTION.v_last_collection_id,
1345: MSC_CL_COLLECTION.v_current_date,
1346: MSC_CL_COLLECTION.v_current_user,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user ;
1349:
1342: lb_sr_instance_id(j),
1343: lb_sr_category_id(j),
1344: MSC_CL_COLLECTION.v_last_collection_id,
1345: MSC_CL_COLLECTION.v_current_date,
1346: MSC_CL_COLLECTION.v_current_user,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user ;
1349:
1350: END IF;
1343: lb_sr_category_id(j),
1344: MSC_CL_COLLECTION.v_last_collection_id,
1345: MSC_CL_COLLECTION.v_current_date,
1346: MSC_CL_COLLECTION.v_current_user,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user ;
1349:
1350: END IF;
1351:
1344: MSC_CL_COLLECTION.v_last_collection_id,
1345: MSC_CL_COLLECTION.v_current_date,
1346: MSC_CL_COLLECTION.v_current_user,
1347: MSC_CL_COLLECTION.v_current_date,
1348: MSC_CL_COLLECTION.v_current_user ;
1349:
1350: END IF;
1351:
1352: c_count:= c_count+1;
1350: END IF;
1351:
1352: c_count:= c_count+1;
1353:
1354: IF c_count>MSC_CL_COLLECTION.PBS THEN
1355:
1356: COMMIT;
1357:
1358: c_count:= 0;
1375: RAISE;
1376:
1377: ELSE
1378:
1379: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1380:
1381: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1382: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1383: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1392: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1393: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1394: FND_MESSAGE.SET_TOKEN('VALUE',
1395: MSC_GET_NAME.ORG_CODE( lb_organization_id(j),
1396: MSC_CL_COLLECTION.v_instance_id));
1397: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1398:
1399: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1400: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_NAME');
1414: COMMIT;
1415:
1416: BEGIN
1417:
1418: 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
1419:
1420: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1421:
1422: lv_sql_stmt:=
1416: BEGIN
1417:
1418: 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
1419:
1420: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1421:
1422: lv_sql_stmt:=
1423: 'INSERT INTO '||lv_tbl
1424: ||' SELECT * from MSC_ITEM_CATEGORIES'
1421:
1422: lv_sql_stmt:=
1423: 'INSERT INTO '||lv_tbl
1424: ||' SELECT * from MSC_ITEM_CATEGORIES'
1425: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1426: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1427:
1428: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1429: EXECUTE IMMEDIATE lv_sql_stmt;
1431: COMMIT;
1432:
1433: END IF;
1434:
1435: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1436: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1437: lv_retcode,
1438: 'MSC_ITEM_CATEGORIES',
1439: MSC_CL_COLLECTION.v_INSTANCE_CODE,
1432:
1433: END IF;
1434:
1435: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1436: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1437: lv_retcode,
1438: 'MSC_ITEM_CATEGORIES',
1439: MSC_CL_COLLECTION.v_INSTANCE_CODE,
1440: MSC_UTIL.G_ERROR
1435: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1436: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1437: lv_retcode,
1438: 'MSC_ITEM_CATEGORIES',
1439: MSC_CL_COLLECTION.v_INSTANCE_CODE,
1440: MSC_UTIL.G_ERROR
1441: );
1442:
1443: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1441: );
1442:
1443: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1445: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1446: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1447: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1448: END IF;
1449:
1443: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1444: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1445: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1446: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1447: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1448: END IF;
1449:
1450: END IF;
1451:
1475: FROM MSC_CATEGORY_SET_ID_LID mcsil,
1476: MSC_ST_CATEGORY_SETS mscs
1477: WHERE mcsil.SR_Category_Set_ID= mscs.SR_Category_Set_ID
1478: AND mcsil.SR_Instance_ID= mscs.SR_Instance_ID
1479: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1480: ORDER BY
1481: mcsil.Category_Set_ID;
1482:
1483: Cursor c4 IS
1485: FROM MSC_ST_SYSTEM_ITEMS msi
1486: WHERE NOT EXISTS ( select 1
1487: from MSC_ITEMS mi
1488: where mi.Item_Name= msi.Item_Name)
1489: AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1490: ORDER BY msi.Item_Name; -- using ORDER BY to avoid dead lock
1491:
1492: Cursor c8 IS
1493: SELECT DISTINCT
1495: FROM MSC_ST_CATEGORY_SETS mscs
1496: WHERE NOT EXISTS ( select 1
1497: from MSC_Category_Sets mcs
1498: where mscs.Category_Set_Name= mcs.Category_Set_Name)
1499: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1500: ORDER BY mscs.Category_Set_Name; -- using ORDER BY to avoid dead lock
1501:
1502: CURSOR c11 IS
1503: SELECT distinct
1509: where msi.SR_INVENTORY_ITEM_ID=
1510: miil.SR_INVENTORY_ITEM_ID
1511: and msi.SR_INSTANCE_ID= miil.SR_INSTANCE_ID)
1512: AND msi.ITEM_NAME= mi.ITEM_NAME
1513: AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1514:
1515: lv_default_category_set_id NUMBER;
1516: lv_control_flag NUMBER;
1517:
1524: BEGIN
1525:
1526: -- MSC_CL_SETUP_COLLECTION.GET_COLL_PARAM (pINSTANCE_ID); -- 2 be changed
1527:
1528: MSC_CL_COLLECTION.INITIALIZE( pINSTANCE_ID);
1529:
1530: SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
1531: INTO lv_control_flag
1532: FROM dual;
1550: end;
1551:
1552:
1553: /* if complete refresh, regen the key mapping data */
1554: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1555:
1556: IF lv_control_flag = 2 THEN
1557:
1558: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1554: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1555:
1556: IF lv_control_flag = 2 THEN
1557:
1558: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1559: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1560: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1561: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1562: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1555:
1556: IF lv_control_flag = 2 THEN
1557:
1558: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1559: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1560: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1561: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1562: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1563: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1556: IF lv_control_flag = 2 THEN
1557:
1558: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1559: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1560: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1561: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1562: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1563: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1564: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1565: END IF;
1566:
1567: ELSE
1568:
1569: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1570: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1571: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1572: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1573: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1566:
1567: ELSE
1568:
1569: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1570: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1571: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1572: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1573: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1574: END IF;
1567: ELSE
1568:
1569: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1570: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1571: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1572: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1573: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1574: END IF;
1575:
1578: END IF;
1579:
1580: /*************** PREPLACE CHANGE START *****************/
1581:
1582: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
1583:
1584: 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
1585:
1586: IF lv_control_flag = 2 THEN
1580: /*************** PREPLACE CHANGE START *****************/
1581:
1582: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
1583:
1584: 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
1585:
1586: IF lv_control_flag = 2 THEN
1587: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1588: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1583:
1584: 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
1585:
1586: IF lv_control_flag = 2 THEN
1587: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1588: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1589: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1590: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1591: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1584: 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
1585:
1586: IF lv_control_flag = 2 THEN
1587: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1588: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1589: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1590: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1591: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1592: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1590: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1591: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1592: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1593: ELSE
1594: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1595: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1596: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1597: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1598: END IF;
1591: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1592: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1593: ELSE
1594: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1595: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1596: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1597: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1598: END IF;
1599:
1603:
1604: /*************** PREPLACE CHANGE END *****************/
1605:
1606: --agmcont
1607: IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1608:
1609: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1610: (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
1611:
1605:
1606: --agmcont
1607: IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1608:
1609: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1610: (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
1611:
1612: IF lv_control_flag = 2 THEN
1613: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1606: --agmcont
1607: IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1608:
1609: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1610: (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
1611:
1612: IF lv_control_flag = 2 THEN
1613: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1614: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1609: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1610: (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
1611:
1612: IF lv_control_flag = 2 THEN
1613: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1614: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1615: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1616: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1617: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1610: (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
1611:
1612: IF lv_control_flag = 2 THEN
1613: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1614: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1615: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1616: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1617: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1618: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1616: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1617: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1618: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1619: ELSE
1620: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1621: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1622: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1623: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1624: END IF;
1617: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1618: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1619: ELSE
1620: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1621: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1622: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1623: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1624: END IF;
1625:
1643: CREATED_BY)
1644: VALUES
1645: ( c_rec.Item_Name,
1646: MSC_Items_S.NEXTVAL,
1647: MSC_CL_COLLECTION.v_current_date,
1648: MSC_CL_COLLECTION.v_current_user,
1649: MSC_CL_COLLECTION.v_current_date,
1650: MSC_CL_COLLECTION.v_current_user);
1651:
1644: VALUES
1645: ( c_rec.Item_Name,
1646: MSC_Items_S.NEXTVAL,
1647: MSC_CL_COLLECTION.v_current_date,
1648: MSC_CL_COLLECTION.v_current_user,
1649: MSC_CL_COLLECTION.v_current_date,
1650: MSC_CL_COLLECTION.v_current_user);
1651:
1652: EXCEPTION
1645: ( c_rec.Item_Name,
1646: MSC_Items_S.NEXTVAL,
1647: MSC_CL_COLLECTION.v_current_date,
1648: MSC_CL_COLLECTION.v_current_user,
1649: MSC_CL_COLLECTION.v_current_date,
1650: MSC_CL_COLLECTION.v_current_user);
1651:
1652: EXCEPTION
1653:
1646: MSC_Items_S.NEXTVAL,
1647: MSC_CL_COLLECTION.v_current_date,
1648: MSC_CL_COLLECTION.v_current_user,
1649: MSC_CL_COLLECTION.v_current_date,
1650: MSC_CL_COLLECTION.v_current_user);
1651:
1652: EXCEPTION
1653:
1654: WHEN DUP_VAL_ON_INDEX THEN
1726: RAISE;
1727:
1728: ELSE
1729:
1730: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1731:
1732: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1733: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1734: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1785: c_rec.Category_Set_Name,
1786: -1,
1787: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1788: -1,
1789: MSC_CL_COLLECTION.v_last_collection_id,
1790: MSC_CL_COLLECTION.v_current_date,
1791: MSC_CL_COLLECTION.v_current_user,
1792: MSC_CL_COLLECTION.v_current_date,
1793: MSC_CL_COLLECTION.v_current_user);
1786: -1,
1787: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1788: -1,
1789: MSC_CL_COLLECTION.v_last_collection_id,
1790: MSC_CL_COLLECTION.v_current_date,
1791: MSC_CL_COLLECTION.v_current_user,
1792: MSC_CL_COLLECTION.v_current_date,
1793: MSC_CL_COLLECTION.v_current_user);
1794:
1787: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1788: -1,
1789: MSC_CL_COLLECTION.v_last_collection_id,
1790: MSC_CL_COLLECTION.v_current_date,
1791: MSC_CL_COLLECTION.v_current_user,
1792: MSC_CL_COLLECTION.v_current_date,
1793: MSC_CL_COLLECTION.v_current_user);
1794:
1795: EXCEPTION
1788: -1,
1789: MSC_CL_COLLECTION.v_last_collection_id,
1790: MSC_CL_COLLECTION.v_current_date,
1791: MSC_CL_COLLECTION.v_current_user,
1792: MSC_CL_COLLECTION.v_current_date,
1793: MSC_CL_COLLECTION.v_current_user);
1794:
1795: EXCEPTION
1796:
1789: MSC_CL_COLLECTION.v_last_collection_id,
1790: MSC_CL_COLLECTION.v_current_date,
1791: MSC_CL_COLLECTION.v_current_user,
1792: MSC_CL_COLLECTION.v_current_date,
1793: MSC_CL_COLLECTION.v_current_user);
1794:
1795: EXCEPTION
1796:
1797: WHEN DUP_VAL_ON_INDEX THEN
1837: from MSC_CATEGORY_SET_ID_LID mcsil
1838: where mscs.SR_Category_Set_ID= mcsil.SR_Category_Set_ID
1839: and mscs.SR_INSTANCE_ID= mcsil.SR_INSTANCE_ID)
1840: AND mscs.Category_Set_NAME= mcs.Category_Set_NAME
1841: AND mscs.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
1842:
1843: lv_ins_records := SQL%ROWCOUNT;
1844:
1845: COMMIT;
1862: SET mcs.DESCRIPTION= c_rec.Description,
1863: mcs.CONTROL_LEVEL= c_rec.Control_Level,
1864: mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1865: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1866: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1867: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1868: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1869: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1870: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1863: mcs.CONTROL_LEVEL= c_rec.Control_Level,
1864: mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1865: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1866: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1867: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1868: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1869: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1870: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1871: WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1864: mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1865: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1866: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1867: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1868: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1869: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1870: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1871: WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1872:
1865: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1866: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1867: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1868: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1869: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1870: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1871: WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1872:
1873: IF c_rec.DEFAULT_FLAG= 1 THEN
1866: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1867: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1868: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1869: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1870: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1871: WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1872:
1873: IF c_rec.DEFAULT_FLAG= 1 THEN
1874: lv_default_category_set_id:= c_rec.Category_Set_ID;
1898: RAISE;
1899:
1900: ELSE
1901:
1902: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1903:
1904: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1905: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1906: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1934:
1935: COMMIT;
1936:
1937: /* analyse the key mapping tables */
1938: IF MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES THEN
1939: IF lv_items_stat_stale = MSC_UTIL.SYS_YES THEN
1940: msc_analyse_tables_pk.analyse_table( 'MSC_ITEM_ID_LID');
1941: END IF;
1942: IF lv_cat_stat_stale = MSC_UTIL.SYS_YES THEN
2021: AND nvl(msis.purchasing_unit_of_measure, '-99') = muom.unit_of_measure (+)
2022: -- Join to get vmi_uom_code
2023: -- AND nvl(msis.vmi_unit_of_measure, '-99') = muom1.unit_of_measure (+)
2024: /* SCE change ends */
2025: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2026:
2027: CURSOR c2 IS
2028: SELECT
2029: mssc.ORGANIZATION_ID,
2046: AND mtil.PARTNER_TYPE = 1
2047: AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2048: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2049: AND mtsil.Partner_Type(+)= 1
2050: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2051: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2052: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2053: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2054: from msc_supplier_capacities msc1
2048: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2049: AND mtsil.Partner_Type(+)= 1
2050: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2051: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2052: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2053: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2054: from msc_supplier_capacities msc1
2055: where msc1.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2056: and msc1.ORGANIZATION_ID = mssc.ORGANIZATION_ID
2080: AND mtil.PARTNER_TYPE = 1
2081: AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2082: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2083: AND mtsil.Partner_Type(+)= 1
2084: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2085: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_YES;
2086:
2087: CURSOR c5d IS
2088: SELECT mssc.ORGANIZATION_ID,
2109: AND msc1.supplier_site_id(+) = mtsil.tp_site_id
2110: AND msc1.inventory_item_id = t1.inventory_item_id
2111: AND msc1.organization_id = mssc.organization_id
2112: AND msc1.sr_instance_id = mssc.sr_instance_id
2113: AND mssc.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2114: AND msc1.collected_flag = 3
2115: AND decode(MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag , MSC_UTIL.ASL_YES,1,0)= 1;
2116:
2117: CURSOR c3 IS
2111: AND msc1.organization_id = mssc.organization_id
2112: AND msc1.sr_instance_id = mssc.sr_instance_id
2113: AND mssc.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2114: AND msc1.collected_flag = 3
2115: AND decode(MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag , MSC_UTIL.ASL_YES,1,0)= 1;
2116:
2117: CURSOR c3 IS
2118: SELECT
2119: mtil.TP_ID SUPPLIER_ID,
2135: AND mtil.PARTNER_TYPE = 1
2136: AND mtsil.SR_TP_SITE_ID(+)= mssfe.Supplier_Site_ID
2137: AND mtsil.SR_INSTANCE_ID(+)= mssfe.SR_Instance_ID
2138: AND mtsil.Partner_Type(+)= 1
2139: AND mssfe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2140:
2141: /*ASL */
2142: Cursor del_asl is
2143: SELECT T1.INVENTORY_ITEM_ID, msis.USING_ORGANIZATION_ID,
2156: AND mtsil.Partner_Type (+)= 1
2157: AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2158: AND nvl(msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2159: AND nvl(msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2160: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2161: And msis.deleted_flag=MSC_UTIL.SYS_YES
2162: ;
2163:
2164:
2213: AND nvl (msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2214: AND nvl (msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2215: AND nvl (msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2216: AND nvl (msis.purchasing_unit_of_measure, '-99') = muom.unit_of_measure (+)
2217: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2218: AND msis.deleted_flag=MSC_UTIL.SYS_NO ;
2219:
2220: lv_table_name VARCHAR2(100);
2221: lv_sql_stmt VARCHAR2(5000);
2227:
2228: BEGIN
2229:
2230: /*ASL */
2231: 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
2232: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
2233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2234: else
2235: lv_table_name:= 'MSC_SYSTEM_ITEMS';
2228: BEGIN
2229:
2230: /*ASL */
2231: 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
2232: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
2233: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2234: else
2235: lv_table_name:= 'MSC_SYSTEM_ITEMS';
2236: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2239: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name is ' || lv_table_name);
2240:
2241: /*ASL */
2242:
2243: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2244:
2245:
2246: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2247:
2242:
2243: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2244:
2245:
2246: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2247:
2248: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2249: v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2250: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2244:
2245:
2246: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2247:
2248: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2249: v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2250: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2251: ELSE
2252: v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'
2246: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2247:
2248: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2249: v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2250: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2251: ELSE
2252: v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'
2253: ||' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2254: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2250: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2251: ELSE
2252: v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'
2253: ||' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2254: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2255: END IF;
2256:
2257: END IF;
2258:
2257: END IF;
2258:
2259: c_count:= 0;
2260:
2261: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh or MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2262: FOR c_rec IN c1 LOOP
2263:
2264: -- Always setting VMI_REFRESH_FLAG to 1 incase of update/inserts
2265: -- to identify records for whom the ASL has changed since the last
2266: -- time VMI ran in CP and redo the VMI calculations.
2267:
2268: BEGIN
2269:
2270: IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2271:
2272: UPDATE MSC_ITEM_SUPPLIERS
2273: SET
2274: PROCESSING_LEAD_TIME= c_rec.PROCESSING_LEAD_TIME,
2278: SUPPLIER_CAP_OVER_UTIL_COST= c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2279: PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2280: SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2281: ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2282: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2283: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2284: /* SCE Change Starts */
2285: /* Pull Supplier Item Name, company_id and company_site_id */
2286: SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2279: PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2280: SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2281: ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2282: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2283: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2284: /* SCE Change Starts */
2285: /* Pull Supplier Item Name, company_id and company_site_id */
2286: SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2287: PLANNER_CODE = c_rec.planner_code,
2300: MAX_MINMAX_DAYS = c_rec.MAX_MINMAX_DAYS,
2301: FORECAST_HORIZON = c_rec.FORECAST_HORIZON,
2302: FIXED_ORDER_QUANTITY = c_rec.FIXED_ORDER_QUANTITY,
2303: VMI_REFRESH_FLAG=1,
2304: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2305: WHERE PLAN_ID= -1
2306: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2307: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2308: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2312: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2313:
2314: END IF;
2315:
2316: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2317:
2318: INSERT INTO MSC_ITEM_SUPPLIERS
2319: ( PLAN_ID,
2320: ORGANIZATION_ID,
2370: c_rec.PURCHASING_UNIT_OF_MEASURE,
2371: c_rec.SR_INSTANCE_ID2,
2372: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2373: c_rec.SR_INSTANCE_ID,
2374: MSC_CL_COLLECTION.v_last_collection_id,
2375: MSC_CL_COLLECTION.v_current_date,
2376: MSC_CL_COLLECTION.v_current_user,
2377: MSC_CL_COLLECTION.v_current_date,
2378: /* SCE Change Starts */
2371: c_rec.SR_INSTANCE_ID2,
2372: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2373: c_rec.SR_INSTANCE_ID,
2374: MSC_CL_COLLECTION.v_last_collection_id,
2375: MSC_CL_COLLECTION.v_current_date,
2376: MSC_CL_COLLECTION.v_current_user,
2377: MSC_CL_COLLECTION.v_current_date,
2378: /* SCE Change Starts */
2379: c_rec.SUPPLIER_ITEM_NAME,
2372: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2373: c_rec.SR_INSTANCE_ID,
2374: MSC_CL_COLLECTION.v_last_collection_id,
2375: MSC_CL_COLLECTION.v_current_date,
2376: MSC_CL_COLLECTION.v_current_user,
2377: MSC_CL_COLLECTION.v_current_date,
2378: /* SCE Change Starts */
2379: c_rec.SUPPLIER_ITEM_NAME,
2380: c_rec.planner_code,
2373: c_rec.SR_INSTANCE_ID,
2374: MSC_CL_COLLECTION.v_last_collection_id,
2375: MSC_CL_COLLECTION.v_current_date,
2376: MSC_CL_COLLECTION.v_current_user,
2377: MSC_CL_COLLECTION.v_current_date,
2378: /* SCE Change Starts */
2379: c_rec.SUPPLIER_ITEM_NAME,
2380: c_rec.planner_code,
2381: c_rec.vmi_flag,
2393: c_rec.MAX_MINMAX_DAYS,
2394: c_rec.FORECAST_HORIZON,
2395: c_rec.FIXED_ORDER_QUANTITY,
2396: 1,
2397: MSC_CL_COLLECTION.v_current_user );
2398:
2399: END IF;
2400:
2401: c_count:= c_count+1;
2399: END IF;
2400:
2401: c_count:= c_count+1;
2402:
2403: IF c_count> MSC_CL_COLLECTION.PBS THEN
2404: COMMIT;
2405: c_count:= 0;
2406: END IF;
2407:
2424: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2425: RAISE;
2426:
2427: ELSE
2428: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2429:
2430: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2431: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2432: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2441: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2442: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2443: FND_MESSAGE.SET_TOKEN('VALUE',
2444: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2445: MSC_CL_COLLECTION.v_instance_id));
2446: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2447:
2448: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2449: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
2465: COMMIT;
2466: END IF ;
2467:
2468: /* ASL */
2469: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and not MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2470:
2471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');
2472: /*NOT REQUIRED
2473: 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
2469: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and not MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2470:
2471: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');
2472: /*NOT REQUIRED
2473: 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
2474: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM LOAD_SUPPLIER_CAPACITY PROCEDURE ');
2475: ADD_NEW_IMPL_ITEM_ASL;
2476: END IF ;
2477: */
2477: */
2478:
2479: lv_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
2480: ||' From msc_instance_orgs '
2481: ||' Where sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2482: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
2483:
2484: EXECUTE IMMEDIATE lv_sql_stmt into lv_last_asl_collection_date;
2485: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'last successful ASL Collection refresh time is '||lv_last_asl_collection_date);
2490: ||' WHERE mis.inventory_item_id =' || del_rec.inventory_item_id
2491: ||' AND mis.USING_ORGANIZATION_ID=' || del_rec.USING_ORGANIZATION_ID
2492: ||' AND mis.SUPPLIER_ID = ' || del_rec.SUPPLIER_ID
2493: ||' AND nvl(mis.SUPPLIER_SITE_ID, -1) = nvl( :SUPPLIER_SITE_ID , -1)'
2494: ||' AND mis.sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2495: ||' AND mis.plan_id = -1 '
2496: ||' AND mis.ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str;
2497:
2498: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'delete query is ' || lv_sql_stmt);
2514: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2515: RAISE;
2516:
2517: ELSE
2518: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2519:
2520: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2521: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2522: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2558: SUPPLIER_CAP_OVER_UTIL_COST= c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2559: PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2560: SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2561: ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2562: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2563: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2564: /* SCE Change Starts */
2565: /* Pull Supplier Item Name, company_id and company_site_id */
2566: SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2559: PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2560: SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2561: ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2562: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2563: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2564: /* SCE Change Starts */
2565: /* Pull Supplier Item Name, company_id and company_site_id */
2566: SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2567: PLANNER_CODE = c_rec.planner_code,
2580: MAX_MINMAX_DAYS = c_rec.MAX_MINMAX_DAYS,
2581: FORECAST_HORIZON = c_rec.FORECAST_HORIZON,
2582: FIXED_ORDER_QUANTITY = c_rec.FIXED_ORDER_QUANTITY,
2583: VMI_REFRESH_FLAG=1,
2584: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2585: WHERE PLAN_ID= -1
2586: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2587: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2588: AND USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID
2673: ||' FROM '
2674: || lv_table_name ||' msi '
2675: ||' WHERE msi.inventory_item_id = '|| c_rec.INVENTORY_ITEM_ID
2676: ||' and msi.organization_id ' || MSC_UTIL.v_in_org_str
2677: ||' and msi.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
2678: ||' and msi.plan_id =-1 ' ;
2679:
2680: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'the insert statement is ' || lv_sql_stmt );
2681:
2691: c_rec.PURCHASING_UNIT_OF_MEASURE,
2692: c_rec.SR_INSTANCE_ID2,
2693: c_rec.ITEM_PRICE,
2694: c_rec.SR_INSTANCE_ID,
2695: MSC_CL_COLLECTION.v_last_collection_id,
2696: MSC_CL_COLLECTION.v_current_date,
2697: MSC_CL_COLLECTION.v_current_user,
2698: MSC_CL_COLLECTION.v_current_date,
2699: c_rec.SUPPLIER_ITEM_NAME,
2692: c_rec.SR_INSTANCE_ID2,
2693: c_rec.ITEM_PRICE,
2694: c_rec.SR_INSTANCE_ID,
2695: MSC_CL_COLLECTION.v_last_collection_id,
2696: MSC_CL_COLLECTION.v_current_date,
2697: MSC_CL_COLLECTION.v_current_user,
2698: MSC_CL_COLLECTION.v_current_date,
2699: c_rec.SUPPLIER_ITEM_NAME,
2700: c_rec.planner_code,
2693: c_rec.ITEM_PRICE,
2694: c_rec.SR_INSTANCE_ID,
2695: MSC_CL_COLLECTION.v_last_collection_id,
2696: MSC_CL_COLLECTION.v_current_date,
2697: MSC_CL_COLLECTION.v_current_user,
2698: MSC_CL_COLLECTION.v_current_date,
2699: c_rec.SUPPLIER_ITEM_NAME,
2700: c_rec.planner_code,
2701: c_rec.vmi_flag,
2694: c_rec.SR_INSTANCE_ID,
2695: MSC_CL_COLLECTION.v_last_collection_id,
2696: MSC_CL_COLLECTION.v_current_date,
2697: MSC_CL_COLLECTION.v_current_user,
2698: MSC_CL_COLLECTION.v_current_date,
2699: c_rec.SUPPLIER_ITEM_NAME,
2700: c_rec.planner_code,
2701: c_rec.vmi_flag,
2702: c_rec.min_minmax_quantity,
2709: c_rec.MIN_MINMAX_DAYS,
2710: c_rec.MAX_MINMAX_DAYS,
2711: c_rec.FORECAST_HORIZON,
2712: c_rec.FIXED_ORDER_QUANTITY,
2713: MSC_CL_COLLECTION.v_current_user ;
2714:
2715: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING ORG -1 ' || SQL%ROWCOUNT);
2716:
2717: ELSE
2770: c_rec.PURCHASING_UNIT_OF_MEASURE,
2771: c_rec.SR_INSTANCE_ID2,
2772: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2773: c_rec.SR_INSTANCE_ID,
2774: MSC_CL_COLLECTION.v_last_collection_id,
2775: MSC_CL_COLLECTION.v_current_date,
2776: MSC_CL_COLLECTION.v_current_user,
2777: MSC_CL_COLLECTION.v_current_date,
2778: /* SCE Change Starts */
2771: c_rec.SR_INSTANCE_ID2,
2772: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2773: c_rec.SR_INSTANCE_ID,
2774: MSC_CL_COLLECTION.v_last_collection_id,
2775: MSC_CL_COLLECTION.v_current_date,
2776: MSC_CL_COLLECTION.v_current_user,
2777: MSC_CL_COLLECTION.v_current_date,
2778: /* SCE Change Starts */
2779: c_rec.SUPPLIER_ITEM_NAME,
2772: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2773: c_rec.SR_INSTANCE_ID,
2774: MSC_CL_COLLECTION.v_last_collection_id,
2775: MSC_CL_COLLECTION.v_current_date,
2776: MSC_CL_COLLECTION.v_current_user,
2777: MSC_CL_COLLECTION.v_current_date,
2778: /* SCE Change Starts */
2779: c_rec.SUPPLIER_ITEM_NAME,
2780: c_rec.planner_code,
2773: c_rec.SR_INSTANCE_ID,
2774: MSC_CL_COLLECTION.v_last_collection_id,
2775: MSC_CL_COLLECTION.v_current_date,
2776: MSC_CL_COLLECTION.v_current_user,
2777: MSC_CL_COLLECTION.v_current_date,
2778: /* SCE Change Starts */
2779: c_rec.SUPPLIER_ITEM_NAME,
2780: c_rec.planner_code,
2781: c_rec.vmi_flag,
2793: c_rec.MAX_MINMAX_DAYS,
2794: c_rec.FORECAST_HORIZON,
2795: c_rec.FIXED_ORDER_QUANTITY,
2796: 1,
2797: MSC_CL_COLLECTION.v_current_user );
2798:
2799: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING org id not -1 ' || SQL%ROWCOUNT);
2800:
2801: END IF ;
2821: RAISE;
2822:
2823: ELSE
2824:
2825: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2826:
2827: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2828: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2829: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2839: END IF ; -- v_is_incremental
2840:
2841: /*ASL */
2842:
2843: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2844: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2845:
2846: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2847:
2840:
2841: /*ASL */
2842:
2843: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2844: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2845:
2846: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2847:
2848: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2842:
2843: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2844: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2845:
2846: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2847:
2848: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2849:
2850: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2844: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2845:
2846: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2847:
2848: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2849:
2850: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2851: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2846: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2847:
2848: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2849:
2850: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2851: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2853: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2854: else
2847:
2848: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2849:
2850: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2851: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2853: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2854: else
2855: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2849:
2850: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2851: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2853: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2854: else
2855: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2856: end if ;
2857: ELSE
2851: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2853: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2854: else
2855: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2856: end if ;
2857: ELSE
2858: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2859: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2854: else
2855: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2856: end if ;
2857: ELSE
2858: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2859: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2860: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2861: else
2862: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2856: end if ;
2857: ELSE
2858: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2859: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2860: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2861: else
2862: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2863: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2864: end if ;
2859: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2860: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2861: else
2862: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2863: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2864: end if ;
2865: END IF;
2866:
2867: END IF;
2867: END IF;
2868:
2869: --============ Incremental Refresh for DELETE =========
2870:
2871: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2872:
2873: FOR c_rec IN c2d LOOP
2874:
2875: UPDATE MSC_SUPPLIER_CAPACITIES
2875: UPDATE MSC_SUPPLIER_CAPACITIES
2876: SET
2877: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2878: CAPACITY= NULL,
2879: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2880: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2881: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2882: WHERE PLAN_ID= -1
2883: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2876: SET
2877: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2878: CAPACITY= NULL,
2879: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2880: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2881: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2882: WHERE PLAN_ID= -1
2883: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2884: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2877: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2878: CAPACITY= NULL,
2879: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2880: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2881: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2882: WHERE PLAN_ID= -1
2883: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2884: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2885: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2915: FOR c_rec IN c2 LOOP
2916:
2917: BEGIN
2918:
2919: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2920:
2921: UPDATE MSC_SUPPLIER_CAPACITIES
2922: SET
2923: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2922: SET
2923: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2924: TO_DATE= c_rec.TO_DATE,
2925: CAPACITY= c_rec.CAPACITY,
2926: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2927: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2928: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2929: WHERE PLAN_ID= -1
2930: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2923: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2924: TO_DATE= c_rec.TO_DATE,
2925: CAPACITY= c_rec.CAPACITY,
2926: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2927: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2928: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2929: WHERE PLAN_ID= -1
2930: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2931: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2924: TO_DATE= c_rec.TO_DATE,
2925: CAPACITY= c_rec.CAPACITY,
2926: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2927: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2928: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2929: WHERE PLAN_ID= -1
2930: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2931: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2932: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2937: AND COLLECTED_FLAG=1;
2938:
2939: END IF;
2940:
2941: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2942:
2943: INSERT INTO MSC_SUPPLIER_CAPACITIES
2944: ( TRANSACTION_ID,
2945: PLAN_ID,
2970: c_rec.TO_DATE,
2971: c_rec.CAPACITY,
2972: c_rec.SR_INSTANCE_ID,
2973: 1,
2974: MSC_CL_COLLECTION.v_last_collection_id,
2975: MSC_CL_COLLECTION.v_current_date,
2976: MSC_CL_COLLECTION.v_current_user,
2977: MSC_CL_COLLECTION.v_current_date,
2978: MSC_CL_COLLECTION.v_current_user );
2971: c_rec.CAPACITY,
2972: c_rec.SR_INSTANCE_ID,
2973: 1,
2974: MSC_CL_COLLECTION.v_last_collection_id,
2975: MSC_CL_COLLECTION.v_current_date,
2976: MSC_CL_COLLECTION.v_current_user,
2977: MSC_CL_COLLECTION.v_current_date,
2978: MSC_CL_COLLECTION.v_current_user );
2979:
2972: c_rec.SR_INSTANCE_ID,
2973: 1,
2974: MSC_CL_COLLECTION.v_last_collection_id,
2975: MSC_CL_COLLECTION.v_current_date,
2976: MSC_CL_COLLECTION.v_current_user,
2977: MSC_CL_COLLECTION.v_current_date,
2978: MSC_CL_COLLECTION.v_current_user );
2979:
2980: END IF;
2973: 1,
2974: MSC_CL_COLLECTION.v_last_collection_id,
2975: MSC_CL_COLLECTION.v_current_date,
2976: MSC_CL_COLLECTION.v_current_user,
2977: MSC_CL_COLLECTION.v_current_date,
2978: MSC_CL_COLLECTION.v_current_user );
2979:
2980: END IF;
2981:
2974: MSC_CL_COLLECTION.v_last_collection_id,
2975: MSC_CL_COLLECTION.v_current_date,
2976: MSC_CL_COLLECTION.v_current_user,
2977: MSC_CL_COLLECTION.v_current_date,
2978: MSC_CL_COLLECTION.v_current_user );
2979:
2980: END IF;
2981:
2982: c_count:= c_count+1;
2980: END IF;
2981:
2982: c_count:= c_count+1;
2983:
2984: IF c_count> MSC_CL_COLLECTION.PBS THEN
2985: COMMIT;
2986: c_count:= 0;
2987: END IF;
2988:
3001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3002: RAISE;
3003:
3004: ELSE
3005: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3006:
3007: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3008: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3009: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3018: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3019: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3020: FND_MESSAGE.SET_TOKEN('VALUE',
3021: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3022: MSC_CL_COLLECTION.v_instance_id));
3023: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3024:
3025: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3026: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3045: END LOOP;
3046:
3047: COMMIT;
3048:
3049: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3050:
3051: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3052:
3053: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3047: COMMIT;
3048:
3049: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3050:
3051: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3052:
3053: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3054: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3055: ELSE
3049: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3050:
3051: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3052:
3053: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3054: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3055: ELSE
3056: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3057: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3050:
3051: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3052:
3053: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3054: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3055: ELSE
3056: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3057: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3058: END IF;
3053: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3054: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3055: ELSE
3056: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3057: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3058: END IF;
3059:
3060: END IF;
3061:
3064: FOR c_rec IN c3 LOOP
3065:
3066: BEGIN
3067:
3068: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3069:
3070: UPDATE MSC_SUPPLIER_FLEX_FENCES
3071: SET
3072: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3070: UPDATE MSC_SUPPLIER_FLEX_FENCES
3071: SET
3072: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3073: TOLERANCE_PERCENTAGE= c_rec.TOLERANCE_PERCENTAGE,
3074: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3075: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3076: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3077: WHERE PLAN_ID= -1
3078: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3071: SET
3072: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3073: TOLERANCE_PERCENTAGE= c_rec.TOLERANCE_PERCENTAGE,
3074: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3075: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3076: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3077: WHERE PLAN_ID= -1
3078: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3079: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3072: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3073: TOLERANCE_PERCENTAGE= c_rec.TOLERANCE_PERCENTAGE,
3074: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3075: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3076: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3077: WHERE PLAN_ID= -1
3078: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3079: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3080: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3084: AND FENCE_DAYS= c_rec.FENCE_DAYS;
3085:
3086: END IF;
3087:
3088: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
3089:
3090: INSERT INTO MSC_SUPPLIER_FLEX_FENCES
3091: ( TRANSACTION_ID,
3092: PLAN_ID,
3113: c_rec.INVENTORY_ITEM_ID,
3114: c_rec.FENCE_DAYS,
3115: c_rec.TOLERANCE_PERCENTAGE,
3116: c_rec.SR_INSTANCE_ID,
3117: MSC_CL_COLLECTION.v_last_collection_id,
3118: MSC_CL_COLLECTION.v_current_date,
3119: MSC_CL_COLLECTION.v_current_user,
3120: MSC_CL_COLLECTION.v_current_date,
3121: MSC_CL_COLLECTION.v_current_user );
3114: c_rec.FENCE_DAYS,
3115: c_rec.TOLERANCE_PERCENTAGE,
3116: c_rec.SR_INSTANCE_ID,
3117: MSC_CL_COLLECTION.v_last_collection_id,
3118: MSC_CL_COLLECTION.v_current_date,
3119: MSC_CL_COLLECTION.v_current_user,
3120: MSC_CL_COLLECTION.v_current_date,
3121: MSC_CL_COLLECTION.v_current_user );
3122:
3115: c_rec.TOLERANCE_PERCENTAGE,
3116: c_rec.SR_INSTANCE_ID,
3117: MSC_CL_COLLECTION.v_last_collection_id,
3118: MSC_CL_COLLECTION.v_current_date,
3119: MSC_CL_COLLECTION.v_current_user,
3120: MSC_CL_COLLECTION.v_current_date,
3121: MSC_CL_COLLECTION.v_current_user );
3122:
3123: END IF;
3116: c_rec.SR_INSTANCE_ID,
3117: MSC_CL_COLLECTION.v_last_collection_id,
3118: MSC_CL_COLLECTION.v_current_date,
3119: MSC_CL_COLLECTION.v_current_user,
3120: MSC_CL_COLLECTION.v_current_date,
3121: MSC_CL_COLLECTION.v_current_user );
3122:
3123: END IF;
3124:
3117: MSC_CL_COLLECTION.v_last_collection_id,
3118: MSC_CL_COLLECTION.v_current_date,
3119: MSC_CL_COLLECTION.v_current_user,
3120: MSC_CL_COLLECTION.v_current_date,
3121: MSC_CL_COLLECTION.v_current_user );
3122:
3123: END IF;
3124:
3125: c_count:= c_count+1;
3123: END IF;
3124:
3125: c_count:= c_count+1;
3126:
3127: IF c_count> MSC_CL_COLLECTION.PBS THEN
3128: COMMIT;
3129: c_count:= 0;
3130: END IF;
3131:
3144: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3145: RAISE;
3146:
3147: ELSE
3148: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3149:
3150: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3151: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3152: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3161: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3162: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3163: FND_MESSAGE.SET_TOKEN('VALUE',
3164: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3165: MSC_CL_COLLECTION.v_instance_id));
3166: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3167:
3168: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3169: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3188: END LOOP;
3189:
3190: COMMIT;
3191:
3192: END IF; -- MSC_CL_COLLECTION.v_apps_ver
3193:
3194: END LOAD_SUPPLIER_CAPACITY;
3195:
3196: --==================================================================
3352: AND t3.sr_instance_id(+)= msi.sr_instance_id
3353: AND mtp.sr_tp_id(+)= msi.organization_id
3354: AND mtp.partner_type(+) = 3
3355: AND mtp.sr_instance_id(+)= msi.sr_instance_id
3356: AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3357:
3358: /*
3359: CURSOR c2 IS
3360: SELECT distinct
3362: msi.abc_class_name,
3363: msi.sr_instance_id,
3364: msi.organization_id
3365: FROM MSC_ST_SYSTEM_ITEMS msi
3366: WHERE msi.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3367: AND msi.abc_class_id is not null
3368: AND msi.abc_class_name is not null;*/
3369:
3370: c_count NUMBER:=0;
3514: lv_retcode NUMBER;
3515:
3516: BEGIN
3517:
3518: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3519: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
3520: ELSE
3521: lv_tbl:= 'MSC_SYSTEM_ITEMS';
3522: END IF;
3515:
3516: BEGIN
3517:
3518: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3519: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
3520: ELSE
3521: lv_tbl:= 'MSC_SYSTEM_ITEMS';
3522: END IF;
3523:
3796: ||' :v_current_user,'
3797: ||' :PEGGING_DEMAND_WINDOW_DAYS,'
3798: ||' :PEGGING_SUPPLY_WINDOW_DAYS )';
3799:
3800: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3801: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3802:
3803: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3804: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3797: ||' :PEGGING_DEMAND_WINDOW_DAYS,'
3798: ||' :PEGGING_SUPPLY_WINDOW_DAYS )';
3799:
3800: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3801: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3802:
3803: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3804: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3805: ELSE
3799:
3800: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3801: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3802:
3803: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3804: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3805: ELSE
3806: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3807: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3800: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3801: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3802:
3803: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3804: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3805: ELSE
3806: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3807: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3808: END IF;
3803: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3804: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3805: ELSE
3806: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3807: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3808: END IF;
3809: END IF;
3810:
3811: -- delete is not supported
3808: END IF;
3809: END IF;
3810:
3811: -- delete is not supported
3812: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3813: BEGIN
3814: lv_sql_ins :=
3815: ' INSERT /*+ append */ '
3816: || ' INTO '||lv_tbl
4098: ||' AND t3.sr_instance_id(+) = msi.sr_instance_id '
4099: ||' AND mtp.sr_tp_id(+) = msi.organization_id '
4100: ||' AND mtp.partner_type(+) = 3 '
4101: ||' AND mtp.sr_instance_id(+) = msi.sr_instance_id '
4102: ||' AND msi.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
4103:
4104: EXECUTE IMMEDIATE lv_sql_ins
4105: USING v_chr9, 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;
4106:
4101: ||' AND mtp.sr_instance_id(+) = msi.sr_instance_id '
4102: ||' AND msi.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
4103:
4104: EXECUTE IMMEDIATE lv_sql_ins
4105: USING v_chr9, 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;
4106:
4107: commit;
4108: EXCEPTION
4109: WHEN OTHERS THEN
4119:
4120: END;
4121: END IF;
4122:
4123: IF (MSC_CL_COLLECTION.v_is_incremental_refresh OR
4124: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR
4125: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
4126: c_count :=0;
4127:
4288:
4289:
4290: BEGIN
4291:
4292: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4293:
4294: UPDATE MSC_SYSTEM_ITEMS
4295: SET
4296: ITEM_NAME= lb_ITEM_NAME(j),
4411: CREATE_SUPPLY_FLAG = lb_CREATE_SUPPLY_FLAG(j),
4412: SERIAL_NUMBER_CONTROL_CODE = lb_SERIAL_NUMBER_CONTROL_CODE(j),
4413: SR_INVENTORY_ITEM_ID= lb_SR_INVENTORY_ITEM_ID(j),
4414: EAM_ITEM_TYPE = lb_EAM_ITEM_TYPE(j), /* ds change change */
4415: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4416: /* ATP SUMMARY CHANGES Added the Refresh_number */
4417: REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4418: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
4419: REPAIR_LEAD_TIME= lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4413: SR_INVENTORY_ITEM_ID= lb_SR_INVENTORY_ITEM_ID(j),
4414: EAM_ITEM_TYPE = lb_EAM_ITEM_TYPE(j), /* ds change change */
4415: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4416: /* ATP SUMMARY CHANGES Added the Refresh_number */
4417: REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4418: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
4419: REPAIR_LEAD_TIME= lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4420: PREPOSITION_POINT = lb_PRE_POSITIONING_POINT(j),
4421: REPAIR_PROGRAM = lb_REPAIR_PROGRAM(j),
4414: EAM_ITEM_TYPE = lb_EAM_ITEM_TYPE(j), /* ds change change */
4415: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4416: /* ATP SUMMARY CHANGES Added the Refresh_number */
4417: REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4418: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
4419: REPAIR_LEAD_TIME= lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4420: PREPOSITION_POINT = lb_PRE_POSITIONING_POINT(j),
4421: REPAIR_PROGRAM = lb_REPAIR_PROGRAM(j),
4422: REPAIR_YIELD = lb_REPAIR_YIELD(j),
4557: lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4558: lb_PRE_POSITIONING_POINT(j),
4559: lb_REPAIR_YIELD(j),
4560: lb_REPAIR_PROGRAM(j),
4561: MSC_CL_COLLECTION.v_last_collection_id,
4562: MSC_CL_COLLECTION.v_current_date,
4563: MSC_CL_COLLECTION.v_current_user,
4564: MSC_CL_COLLECTION.v_current_date,
4565: MSC_CL_COLLECTION.v_current_user,
4558: lb_PRE_POSITIONING_POINT(j),
4559: lb_REPAIR_YIELD(j),
4560: lb_REPAIR_PROGRAM(j),
4561: MSC_CL_COLLECTION.v_last_collection_id,
4562: MSC_CL_COLLECTION.v_current_date,
4563: MSC_CL_COLLECTION.v_current_user,
4564: MSC_CL_COLLECTION.v_current_date,
4565: MSC_CL_COLLECTION.v_current_user,
4566: lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4559: lb_REPAIR_YIELD(j),
4560: lb_REPAIR_PROGRAM(j),
4561: MSC_CL_COLLECTION.v_last_collection_id,
4562: MSC_CL_COLLECTION.v_current_date,
4563: MSC_CL_COLLECTION.v_current_user,
4564: MSC_CL_COLLECTION.v_current_date,
4565: MSC_CL_COLLECTION.v_current_user,
4566: lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4567: lb_PEGGING_SUPPLY_WINDOW_DAYS(j);
4560: lb_REPAIR_PROGRAM(j),
4561: MSC_CL_COLLECTION.v_last_collection_id,
4562: MSC_CL_COLLECTION.v_current_date,
4563: MSC_CL_COLLECTION.v_current_user,
4564: MSC_CL_COLLECTION.v_current_date,
4565: MSC_CL_COLLECTION.v_current_user,
4566: lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4567: lb_PEGGING_SUPPLY_WINDOW_DAYS(j);
4568:
4561: MSC_CL_COLLECTION.v_last_collection_id,
4562: MSC_CL_COLLECTION.v_current_date,
4563: MSC_CL_COLLECTION.v_current_user,
4564: MSC_CL_COLLECTION.v_current_date,
4565: MSC_CL_COLLECTION.v_current_user,
4566: lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4567: lb_PEGGING_SUPPLY_WINDOW_DAYS(j);
4568:
4569: END IF;
4575: WHERE inventory_item_id = lb_INVENTORY_ITEM_ID(j);
4576: END IF;
4577: c_count:= c_count+1;
4578:
4579: IF c_count>MSC_CL_COLLECTION.PBS THEN
4580:
4581: COMMIT;
4582:
4583: c_count:= 0;
4598: RAISE;
4599:
4600: ELSE
4601:
4602: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4603:
4604: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4605: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4606: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4615: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4616: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
4617: FND_MESSAGE.SET_TOKEN('VALUE',
4618: MSC_GET_NAME.ORG_CODE( lb_ORGANIZATION_ID(j),
4619: MSC_CL_COLLECTION.v_instance_id));
4620: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4621:
4622: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4623: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
4637: END IF;
4638:
4639: BEGIN
4640:
4641: 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
4642:
4643: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
4644:
4645: lv_sql_stmt:=
4639: BEGIN
4640:
4641: 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
4642:
4643: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
4644:
4645: lv_sql_stmt:=
4646: 'INSERT INTO '||lv_tbl
4647: ||' SELECT * from MSC_SYSTEM_ITEMS'
4644:
4645: lv_sql_stmt:=
4646: 'INSERT INTO '||lv_tbl
4647: ||' SELECT * from MSC_SYSTEM_ITEMS'
4648: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4649: ||' AND plan_id = -1 '
4650: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4651:
4652: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4655: COMMIT;
4656:
4657: END IF;
4658:
4659: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4660: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4661: lv_retcode,
4662: 'MSC_SYSTEM_ITEMS',
4663: MSC_CL_COLLECTION.v_INSTANCE_CODE,
4656:
4657: END IF;
4658:
4659: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4660: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4661: lv_retcode,
4662: 'MSC_SYSTEM_ITEMS',
4663: MSC_CL_COLLECTION.v_INSTANCE_CODE,
4664: MSC_UTIL.G_ERROR
4659: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4660: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4661: lv_retcode,
4662: 'MSC_SYSTEM_ITEMS',
4663: MSC_CL_COLLECTION.v_INSTANCE_CODE,
4664: MSC_UTIL.G_ERROR
4665: );
4666:
4667: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4665: );
4666:
4667: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4669: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4670: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4671: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4672: END IF;
4673:
4667: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4669: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4670: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4671: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4672: END IF;
4673:
4674: END IF;
4675:
4673:
4674: END IF;
4675:
4676: /*call to insert ASL */
4677: IF ( NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
4678: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM ITEM PROCEDURE ');
4679: ADD_NEW_IMPL_ITEM_ASL;
4680: END IF ;
4681: