[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 NVL(CUSTOMER_ID, -23453) = NVL(lb_CUSTOMER_ID(j), -23453)
826: AND NVL(CUSTOMER_SITE_ID, -23453) =
840: lb_EFFECTIVE_DATE(j),
841: lb_DISABLE_DATE(j),
842: lb_RELATIONSHIP_TYPE(j),
843: lb_PARTIAL_FULFILLMENT_FLAG(j),
844: MSC_CL_COLLECTION.v_last_collection_id,
845: MSC_CL_COLLECTION.v_instance_id,
846: lb_ORGANIZATION_ID(j),
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user,
841: lb_DISABLE_DATE(j),
842: lb_RELATIONSHIP_TYPE(j),
843: lb_PARTIAL_FULFILLMENT_FLAG(j),
844: MSC_CL_COLLECTION.v_last_collection_id,
845: MSC_CL_COLLECTION.v_instance_id,
846: lb_ORGANIZATION_ID(j),
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user,
849: MSC_CL_COLLECTION.v_current_user,
843: lb_PARTIAL_FULFILLMENT_FLAG(j),
844: MSC_CL_COLLECTION.v_last_collection_id,
845: MSC_CL_COLLECTION.v_instance_id,
846: lb_ORGANIZATION_ID(j),
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user,
849: MSC_CL_COLLECTION.v_current_user,
850: MSC_CL_COLLECTION.v_current_date,
851: MSC_CL_COLLECTION.v_current_user;
844: MSC_CL_COLLECTION.v_last_collection_id,
845: MSC_CL_COLLECTION.v_instance_id,
846: lb_ORGANIZATION_ID(j),
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user,
849: MSC_CL_COLLECTION.v_current_user,
850: MSC_CL_COLLECTION.v_current_date,
851: MSC_CL_COLLECTION.v_current_user;
852: EXCEPTION
845: MSC_CL_COLLECTION.v_instance_id,
846: lb_ORGANIZATION_ID(j),
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user,
849: MSC_CL_COLLECTION.v_current_user,
850: MSC_CL_COLLECTION.v_current_date,
851: MSC_CL_COLLECTION.v_current_user;
852: EXCEPTION
853: WHEN OTHERS THEN
846: lb_ORGANIZATION_ID(j),
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user,
849: MSC_CL_COLLECTION.v_current_user,
850: MSC_CL_COLLECTION.v_current_date,
851: MSC_CL_COLLECTION.v_current_user;
852: EXCEPTION
853: WHEN OTHERS THEN
854: -- LOG_MESSAGE(SQLERRM);
847: MSC_CL_COLLECTION.v_current_date,
848: MSC_CL_COLLECTION.v_current_user,
849: MSC_CL_COLLECTION.v_current_user,
850: MSC_CL_COLLECTION.v_current_date,
851: MSC_CL_COLLECTION.v_current_user;
852: EXCEPTION
853: WHEN OTHERS THEN
854: -- LOG_MESSAGE(SQLERRM);
855: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
857: end;
858: END IF;
859:
860: c_count:= c_count+1;
861: IF c_count > MSC_CL_COLLECTION.PBS THEN
862: COMMIT;
863: -- LOG_MESSAGE('The total record count inserted is '||TO_CHAR(c_count));
864: c_count:= 0;
865: END IF;
871:
872: lb_FetchComplete := FALSE;
873:
874:
875: IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' AND (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 OR MSC_CL_COLLECTION.v_is_legacy_refresh)) THEN
876: Open c2;
877:
878: IF (c2%ISOPEN) THEN
879: LOOP
907: SET
908: RECIPROCAL_FLAG = lb_RECIPROCAL_FLAG(j),
909: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
910: DISABLE_DATE = lb_DISABLE_DATE(j),
911: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
912: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
913: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
914: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
915: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
908: RECIPROCAL_FLAG = lb_RECIPROCAL_FLAG(j),
909: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
910: DISABLE_DATE = lb_DISABLE_DATE(j),
911: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
912: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
913: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
914: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
915: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
916: WHERE
909: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
910: DISABLE_DATE = lb_DISABLE_DATE(j),
911: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
912: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
913: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
914: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
915: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
916: WHERE
917: PLAN_ID = -1
910: DISABLE_DATE = lb_DISABLE_DATE(j),
911: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
912: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
913: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
914: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
915: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
916: WHERE
917: PLAN_ID = -1
918: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
911: REFRESH_ID = MSC_CL_COLLECTION.v_last_collection_id,
912: LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
913: LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
914: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
915: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
916: WHERE
917: PLAN_ID = -1
918: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
919: AND HIGHER_ITEM_ID = lb_HIGHER_ITEM_ID(j)
914: LAST_UPDATE_LOGIN = MSC_CL_COLLECTION.v_current_user,
915: CREATION_DATE = MSC_CL_COLLECTION.v_current_date
916: WHERE
917: PLAN_ID = -1
918: AND SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
919: AND HIGHER_ITEM_ID = lb_HIGHER_ITEM_ID(j)
920: AND LOWER_ITEM_ID = lb_LOWER_ITEM_ID(j)
921: AND ORGANIZATION_ID = lb_ORGANIZATION_ID(j)
922: AND RELATIONSHIP_TYPE = lb_RELATIONSHIP_TYPE(j);
932: lb_EFFECTIVE_DATE(j),
933: lb_DISABLE_DATE(j),
934: lb_RELATIONSHIP_TYPE(j),
935: lb_PARTIAL_FULFILLMENT_FLAG(j),
936: MSC_CL_COLLECTION.v_last_collection_id,
937: MSC_CL_COLLECTION.v_instance_id,
938: lb_ORGANIZATION_ID(j),
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user,
933: lb_DISABLE_DATE(j),
934: lb_RELATIONSHIP_TYPE(j),
935: lb_PARTIAL_FULFILLMENT_FLAG(j),
936: MSC_CL_COLLECTION.v_last_collection_id,
937: MSC_CL_COLLECTION.v_instance_id,
938: lb_ORGANIZATION_ID(j),
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user,
941: MSC_CL_COLLECTION.v_current_user,
935: lb_PARTIAL_FULFILLMENT_FLAG(j),
936: MSC_CL_COLLECTION.v_last_collection_id,
937: MSC_CL_COLLECTION.v_instance_id,
938: lb_ORGANIZATION_ID(j),
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user,
941: MSC_CL_COLLECTION.v_current_user,
942: MSC_CL_COLLECTION.v_current_date,
943: MSC_CL_COLLECTION.v_current_user;
936: MSC_CL_COLLECTION.v_last_collection_id,
937: MSC_CL_COLLECTION.v_instance_id,
938: lb_ORGANIZATION_ID(j),
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user,
941: MSC_CL_COLLECTION.v_current_user,
942: MSC_CL_COLLECTION.v_current_date,
943: MSC_CL_COLLECTION.v_current_user;
944:
937: MSC_CL_COLLECTION.v_instance_id,
938: lb_ORGANIZATION_ID(j),
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user,
941: MSC_CL_COLLECTION.v_current_user,
942: MSC_CL_COLLECTION.v_current_date,
943: MSC_CL_COLLECTION.v_current_user;
944:
945: EXCEPTION
938: lb_ORGANIZATION_ID(j),
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user,
941: MSC_CL_COLLECTION.v_current_user,
942: MSC_CL_COLLECTION.v_current_date,
943: MSC_CL_COLLECTION.v_current_user;
944:
945: EXCEPTION
946: WHEN OTHERS THEN
939: MSC_CL_COLLECTION.v_current_date,
940: MSC_CL_COLLECTION.v_current_user,
941: MSC_CL_COLLECTION.v_current_user,
942: MSC_CL_COLLECTION.v_current_date,
943: MSC_CL_COLLECTION.v_current_user;
944:
945: EXCEPTION
946: WHEN OTHERS THEN
947: -- LOG_MESSAGE(SQLERRM);
950: end;
951: END IF;
952:
953: c_count:= c_count+1;
954: IF c_count > MSC_CL_COLLECTION.PBS THEN
955: COMMIT;
956: -- LOG_MESSAGE('The total record count inserted is '||TO_CHAR(c_count));
957: c_count:= 0;
958: END IF;
965: CLOSE c2;
966: END IF; -- End of Net Change
967:
968:
969: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
970:
971: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
972:
973: lv_sql_stmt:=
967:
968:
969: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
970:
971: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
972:
973: lv_sql_stmt:=
974: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
975: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_SUBSTITUTES'
972:
973: lv_sql_stmt:=
974: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
975: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_SUBSTITUTES'
976: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
977: ||' AND plan_id = -1 '
978: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
979:
980: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
983: COMMIT;
984:
985: END IF;
986:
987: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
988: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
989: lv_retcode,
990: 'MSC_ITEM_SUBSTITUTES',
991: MSC_CL_COLLECTION.v_INSTANCE_CODE,
984:
985: END IF;
986:
987: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
988: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
989: lv_retcode,
990: 'MSC_ITEM_SUBSTITUTES',
991: MSC_CL_COLLECTION.v_INSTANCE_CODE,
992: MSC_UTIL.G_WARNING
987: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
988: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
989: lv_retcode,
990: 'MSC_ITEM_SUBSTITUTES',
991: MSC_CL_COLLECTION.v_INSTANCE_CODE,
992: MSC_UTIL.G_WARNING
993: );
994:
995:
994:
995:
996: IF lv_retcode = MSC_UTIL.G_WARNING THEN
997: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
998: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
999: END IF;
1000:
1001: END IF;
1002:
1015: RAISE;
1016:
1017: ELSE
1018:
1019: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1020:
1021: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1022: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1023: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1065: WHERE t1.SR_INVENTORY_ITEM_ID= msic.inventory_item_id
1066: AND t1.sr_instance_id= msic.sr_instance_id
1067: AND mcsil.SR_Category_Set_ID= msic.SR_Category_Set_ID
1068: AND mcsil.SR_Instance_ID= msic.SR_Instance_ID
1069: AND msic.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1070: AND msic.DELETED_FLAG= MSC_UTIL.SYS_NO;
1071:
1072: CURSOR c1_d IS
1073: SELECT
1082: WHERE t1.SR_INVENTORY_ITEM_ID= msic.inventory_item_id
1083: AND t1.sr_instance_id= msic.sr_instance_id
1084: AND mcsil.SR_Category_Set_ID= msic.SR_Category_Set_ID
1085: AND mcsil.SR_Instance_ID= msic.SR_Instance_ID
1086: AND msic.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1087: AND msic.DELETED_FLAG= MSC_UTIL.SYS_YES;
1088:
1089: c_count NUMBER:= 0;
1090: lv_tbl VARCHAR2(30);
1114: lv_retcode NUMBER;
1115:
1116: BEGIN
1117:
1118: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1119: -- We want to delete all CATEGORY related data and get new stuff.
1120:
1121: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1122: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1117:
1118: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1119: -- We want to delete all CATEGORY related data and get new stuff.
1120:
1121: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1122: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123:
1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1118: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1119: -- We want to delete all CATEGORY related data and get new stuff.
1120:
1121: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1122: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123:
1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1126: ELSE
1120:
1121: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1122: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123:
1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1126: ELSE
1127: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1128: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);
1121: if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
1122: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1123:
1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1126: ELSE
1127: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1128: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);
1129: END IF;
1124: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1125: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL);
1126: ELSE
1127: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1128: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_CATEGORIES', MSC_CL_COLLECTION.v_instance_id, NULL,v_sub_str);
1129: END IF;
1130: end if;
1131:
1132: END IF;
1130: end if;
1131:
1132: END IF;
1133:
1134: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1135: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1136: ELSE
1137: lv_tbl:= 'MSC_ITEM_CATEGORIES';
1138: END IF;
1131:
1132: END IF;
1133:
1134: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1135: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1136: ELSE
1137: lv_tbl:= 'MSC_ITEM_CATEGORIES';
1138: END IF;
1139:
1136: ELSE
1137: lv_tbl:= 'MSC_ITEM_CATEGORIES';
1138: END IF;
1139:
1140: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1141:
1142: BEGIN
1143:
1144: lv_sql_ins :=
1185: ||' WHERE t1.SR_INVENTORY_ITEM_ID = msic.inventory_item_id '
1186: ||' AND t1.sr_instance_id = msic.sr_instance_id '
1187: ||' AND mcsil.SR_Category_Set_ID = msic.SR_Category_Set_ID '
1188: ||' AND mcsil.SR_Instance_ID = msic.SR_Instance_ID '
1189: ||' AND msic.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
1190:
1191: EXECUTE IMMEDIATE lv_sql_ins
1192: 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;
1193:
1188: ||' AND mcsil.SR_Instance_ID = msic.SR_Instance_ID '
1189: ||' AND msic.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
1190:
1191: EXECUTE IMMEDIATE lv_sql_ins
1192: 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;
1193:
1194: commit;
1195: EXCEPTION
1196: WHEN OTHERS THEN
1302: FOR j IN 1..lb_inventory_item_id.COUNT LOOP
1303:
1304: BEGIN
1305:
1306: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1307:
1308: UPDATE MSC_ITEM_CATEGORIES
1309: SET
1310: CATEGORY_NAME = lb_category_name(j),
1317: /* SR_CATEGORY_ID= lb_sr_category_id(j), --If the item is assigned to more than 1 category in the same category-set then,
1318: only one row of that category set will be repeatedly updated and the other
1319: item categories will not be inserted. Moving this into the WHERE clause.
1320: */
1321: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1322: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1323: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1324: WHERE ORGANIZATION_ID = lb_organization_id(j)
1325: AND INVENTORY_ITEM_ID = lb_inventory_item_id(J)
1318: only one row of that category set will be repeatedly updated and the other
1319: item categories will not be inserted. Moving this into the WHERE clause.
1320: */
1321: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1322: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1323: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1324: WHERE ORGANIZATION_ID = lb_organization_id(j)
1325: AND INVENTORY_ITEM_ID = lb_inventory_item_id(J)
1326: AND CATEGORY_SET_ID = lb_category_set_id(j)
1319: item categories will not be inserted. Moving this into the WHERE clause.
1320: */
1321: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1322: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1323: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1324: WHERE ORGANIZATION_ID = lb_organization_id(j)
1325: AND INVENTORY_ITEM_ID = lb_inventory_item_id(J)
1326: AND CATEGORY_SET_ID = lb_category_set_id(j)
1327: AND SR_INSTANCE_ID = lb_sr_instance_id(j)
1343: lb_start_date_active(j),
1344: lb_end_date_active(J),
1345: lb_sr_instance_id(j),
1346: lb_sr_category_id(j),
1347: MSC_CL_COLLECTION.v_last_collection_id,
1348: MSC_CL_COLLECTION.v_current_date,
1349: MSC_CL_COLLECTION.v_current_user,
1350: MSC_CL_COLLECTION.v_current_date,
1351: MSC_CL_COLLECTION.v_current_user ;
1344: lb_end_date_active(J),
1345: lb_sr_instance_id(j),
1346: lb_sr_category_id(j),
1347: MSC_CL_COLLECTION.v_last_collection_id,
1348: MSC_CL_COLLECTION.v_current_date,
1349: MSC_CL_COLLECTION.v_current_user,
1350: MSC_CL_COLLECTION.v_current_date,
1351: MSC_CL_COLLECTION.v_current_user ;
1352:
1345: lb_sr_instance_id(j),
1346: lb_sr_category_id(j),
1347: MSC_CL_COLLECTION.v_last_collection_id,
1348: MSC_CL_COLLECTION.v_current_date,
1349: MSC_CL_COLLECTION.v_current_user,
1350: MSC_CL_COLLECTION.v_current_date,
1351: MSC_CL_COLLECTION.v_current_user ;
1352:
1353: END IF;
1346: lb_sr_category_id(j),
1347: MSC_CL_COLLECTION.v_last_collection_id,
1348: MSC_CL_COLLECTION.v_current_date,
1349: MSC_CL_COLLECTION.v_current_user,
1350: MSC_CL_COLLECTION.v_current_date,
1351: MSC_CL_COLLECTION.v_current_user ;
1352:
1353: END IF;
1354:
1347: MSC_CL_COLLECTION.v_last_collection_id,
1348: MSC_CL_COLLECTION.v_current_date,
1349: MSC_CL_COLLECTION.v_current_user,
1350: MSC_CL_COLLECTION.v_current_date,
1351: MSC_CL_COLLECTION.v_current_user ;
1352:
1353: END IF;
1354:
1355: c_count:= c_count+1;
1353: END IF;
1354:
1355: c_count:= c_count+1;
1356:
1357: IF c_count>MSC_CL_COLLECTION.PBS THEN
1358:
1359: COMMIT;
1360:
1361: c_count:= 0;
1378: RAISE;
1379:
1380: ELSE
1381:
1382: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1383:
1384: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1385: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1386: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_CATEGORY');
1395: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1396: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1397: FND_MESSAGE.SET_TOKEN('VALUE',
1398: MSC_GET_NAME.ORG_CODE( lb_organization_id(j),
1399: MSC_CL_COLLECTION.v_instance_id));
1400: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1401:
1402: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1403: FND_MESSAGE.SET_TOKEN('COLUMN', 'CATEGORY_NAME');
1417: COMMIT;
1418:
1419: BEGIN
1420:
1421: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1422:
1423: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1424:
1425: lv_sql_stmt:=
1419: BEGIN
1420:
1421: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1422:
1423: lv_tbl:= 'ITEM_CATEGORIES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
1424:
1425: lv_sql_stmt:=
1426: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1427: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_CATEGORIES'
1424:
1425: lv_sql_stmt:=
1426: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1427: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_CATEGORIES'
1428: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1429: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1430:
1431: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1432: EXECUTE IMMEDIATE lv_sql_stmt;
1434: COMMIT;
1435:
1436: END IF;
1437:
1438: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1439: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1440: lv_retcode,
1441: 'MSC_ITEM_CATEGORIES',
1442: MSC_CL_COLLECTION.v_INSTANCE_CODE,
1435:
1436: END IF;
1437:
1438: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1439: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1440: lv_retcode,
1441: 'MSC_ITEM_CATEGORIES',
1442: MSC_CL_COLLECTION.v_INSTANCE_CODE,
1443: MSC_UTIL.G_ERROR
1438: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1439: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1440: lv_retcode,
1441: 'MSC_ITEM_CATEGORIES',
1442: MSC_CL_COLLECTION.v_INSTANCE_CODE,
1443: MSC_UTIL.G_ERROR
1444: );
1445:
1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1444: );
1445:
1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1448: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1449: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1450: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1451: END IF;
1452:
1446: IF lv_retcode = MSC_UTIL.G_ERROR THEN
1447: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1448: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1449: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1450: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1451: END IF;
1452:
1453: END IF;
1454:
1478: FROM MSC_CATEGORY_SET_ID_LID mcsil,
1479: MSC_ST_CATEGORY_SETS mscs
1480: WHERE mcsil.SR_Category_Set_ID= mscs.SR_Category_Set_ID
1481: AND mcsil.SR_Instance_ID= mscs.SR_Instance_ID
1482: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1483: ORDER BY
1484: mcsil.Category_Set_ID;
1485:
1486: Cursor c4 IS
1488: FROM MSC_ST_SYSTEM_ITEMS msi
1489: WHERE NOT EXISTS ( select 1
1490: from MSC_ITEMS mi
1491: where mi.Item_Name= msi.Item_Name)
1492: AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1493: ORDER BY msi.Item_Name; -- using ORDER BY to avoid dead lock
1494:
1495: Cursor c8 IS
1496: SELECT DISTINCT
1498: FROM MSC_ST_CATEGORY_SETS mscs
1499: WHERE NOT EXISTS ( select 1
1500: from MSC_Category_Sets mcs
1501: where mscs.Category_Set_Name= mcs.Category_Set_Name)
1502: AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1503: ORDER BY mscs.Category_Set_Name; -- using ORDER BY to avoid dead lock
1504:
1505: CURSOR c11 IS
1506: SELECT distinct
1512: where msi.SR_INVENTORY_ITEM_ID=
1513: miil.SR_INVENTORY_ITEM_ID
1514: and msi.SR_INSTANCE_ID= miil.SR_INSTANCE_ID)
1515: AND msi.ITEM_NAME= mi.ITEM_NAME
1516: AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1517:
1518: lv_default_category_set_id NUMBER;
1519: lv_control_flag NUMBER;
1520:
1528:
1529: MSC_CL_SETUP_ODS_LOAD.GET_COLL_PARAM (pINSTANCE_ID); -- 2 be changed
1530: /*uncommenting the above call -- bug12711539*/
1531:
1532: MSC_CL_COLLECTION.INITIALIZE( pINSTANCE_ID);
1533:
1534: SELECT decode(nvl(fnd_profile.value('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
1535: INTO lv_control_flag
1536: FROM dual;
1554: end;
1555:
1556:
1557: /* if complete refresh, regen the key mapping data */
1558: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1559:
1560: IF lv_control_flag = 2 THEN
1561:
1562: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1558: IF MSC_CL_COLLECTION.v_is_complete_refresh THEN
1559:
1560: IF lv_control_flag = 2 THEN
1561:
1562: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1563: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1564: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1565: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1566: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1559:
1560: IF lv_control_flag = 2 THEN
1561:
1562: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1563: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1564: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1565: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1566: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1567: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1560: IF lv_control_flag = 2 THEN
1561:
1562: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1563: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1564: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1565: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1566: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1567: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1568: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1569: END IF;
1570:
1571: ELSE
1572:
1573: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1574: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1575: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1576: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1577: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1570:
1571: ELSE
1572:
1573: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1574: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1575: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1576: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1577: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1578: END IF;
1571: ELSE
1572:
1573: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1574: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1575: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1576: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1577: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1578: END IF;
1579:
1582: END IF;
1583:
1584: /*************** PREPLACE CHANGE START *****************/
1585:
1586: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
1587:
1588: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.org_group_flag =MSC_UTIL.G_ALL_ORGANIZATIONS ) THEN
1589:
1590: IF lv_control_flag = 2 THEN
1584: /*************** PREPLACE CHANGE START *****************/
1585:
1586: IF MSC_CL_COLLECTION.v_is_partial_refresh THEN
1587:
1588: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.org_group_flag =MSC_UTIL.G_ALL_ORGANIZATIONS ) THEN
1589:
1590: IF lv_control_flag = 2 THEN
1591: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1592: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1587:
1588: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.org_group_flag =MSC_UTIL.G_ALL_ORGANIZATIONS ) THEN
1589:
1590: IF lv_control_flag = 2 THEN
1591: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1592: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1593: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1594: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1595: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1588: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.org_group_flag =MSC_UTIL.G_ALL_ORGANIZATIONS ) THEN
1589:
1590: IF lv_control_flag = 2 THEN
1591: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1592: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1593: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1594: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1595: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1596: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1594: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1595: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1596: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1597: ELSE
1598: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1599: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1600: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1601: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1602: END IF;
1595: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1596: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1597: ELSE
1598: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1599: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1600: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1601: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1602: END IF;
1603:
1607:
1608: /*************** PREPLACE CHANGE END *****************/
1609:
1610: --agmcont
1611: IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1612:
1613: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1614: (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN
1615:
1609:
1610: --agmcont
1611: IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1612:
1613: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1614: (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN
1615:
1616: IF lv_control_flag = 2 THEN
1617: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1610: --agmcont
1611: IF MSC_CL_COLLECTION.v_is_cont_refresh THEN
1612:
1613: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1614: (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN
1615:
1616: IF lv_control_flag = 2 THEN
1617: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1618: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1613: IF (MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES) and
1614: (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN
1615:
1616: IF lv_control_flag = 2 THEN
1617: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1618: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1619: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1620: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1621: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1614: (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) and (MSC_CL_COLLECTION.v_coll_prec.org_group_flag=MSC_UTIL.G_ALL_ORGANIZATIONS) THEN
1615:
1616: IF lv_control_flag = 2 THEN
1617: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1618: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1619: DELETE MSC_ITEM_ID_LID WHERE SR_INSTANCE_ID= -1;
1620: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1621: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1622: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1620: DELETE MSC_CATEGORY_SET_ID_LID WHERE SR_INSTANCE_ID= -1;
1621: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1622: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1623: ELSE
1624: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1625: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1626: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1627: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1628: END IF;
1621: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1622: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1623: ELSE
1624: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_ITEM_ID_LID');
1625: MSC_CL_COLLECTION.TRUNCATE_MSC_TABLE( 'MSC_CATEGORY_SET_ID_LID');
1626: lv_items_stat_stale := MSC_UTIL.SYS_YES;
1627: lv_cat_stat_stale := MSC_UTIL.SYS_YES;
1628: END IF;
1629:
1647: CREATED_BY)
1648: VALUES
1649: ( c_rec.Item_Name,
1650: MSC_Items_S.NEXTVAL,
1651: MSC_CL_COLLECTION.v_current_date,
1652: MSC_CL_COLLECTION.v_current_user,
1653: MSC_CL_COLLECTION.v_current_date,
1654: MSC_CL_COLLECTION.v_current_user);
1655:
1648: VALUES
1649: ( c_rec.Item_Name,
1650: MSC_Items_S.NEXTVAL,
1651: MSC_CL_COLLECTION.v_current_date,
1652: MSC_CL_COLLECTION.v_current_user,
1653: MSC_CL_COLLECTION.v_current_date,
1654: MSC_CL_COLLECTION.v_current_user);
1655:
1656: EXCEPTION
1649: ( c_rec.Item_Name,
1650: MSC_Items_S.NEXTVAL,
1651: MSC_CL_COLLECTION.v_current_date,
1652: MSC_CL_COLLECTION.v_current_user,
1653: MSC_CL_COLLECTION.v_current_date,
1654: MSC_CL_COLLECTION.v_current_user);
1655:
1656: EXCEPTION
1657:
1650: MSC_Items_S.NEXTVAL,
1651: MSC_CL_COLLECTION.v_current_date,
1652: MSC_CL_COLLECTION.v_current_user,
1653: MSC_CL_COLLECTION.v_current_date,
1654: MSC_CL_COLLECTION.v_current_user);
1655:
1656: EXCEPTION
1657:
1658: WHEN DUP_VAL_ON_INDEX THEN
1730: RAISE;
1731:
1732: ELSE
1733:
1734: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1735:
1736: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1737: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1738: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1789: c_rec.Category_Set_Name,
1790: -1,
1791: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1792: -1,
1793: MSC_CL_COLLECTION.v_last_collection_id,
1794: MSC_CL_COLLECTION.v_current_date,
1795: MSC_CL_COLLECTION.v_current_user,
1796: MSC_CL_COLLECTION.v_current_date,
1797: MSC_CL_COLLECTION.v_current_user);
1790: -1,
1791: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1792: -1,
1793: MSC_CL_COLLECTION.v_last_collection_id,
1794: MSC_CL_COLLECTION.v_current_date,
1795: MSC_CL_COLLECTION.v_current_user,
1796: MSC_CL_COLLECTION.v_current_date,
1797: MSC_CL_COLLECTION.v_current_user);
1798:
1791: MSC_Category_Sets_S.NEXTVAL, -- dummy value to satisfy the unique constraint
1792: -1,
1793: MSC_CL_COLLECTION.v_last_collection_id,
1794: MSC_CL_COLLECTION.v_current_date,
1795: MSC_CL_COLLECTION.v_current_user,
1796: MSC_CL_COLLECTION.v_current_date,
1797: MSC_CL_COLLECTION.v_current_user);
1798:
1799: EXCEPTION
1792: -1,
1793: MSC_CL_COLLECTION.v_last_collection_id,
1794: MSC_CL_COLLECTION.v_current_date,
1795: MSC_CL_COLLECTION.v_current_user,
1796: MSC_CL_COLLECTION.v_current_date,
1797: MSC_CL_COLLECTION.v_current_user);
1798:
1799: EXCEPTION
1800:
1793: MSC_CL_COLLECTION.v_last_collection_id,
1794: MSC_CL_COLLECTION.v_current_date,
1795: MSC_CL_COLLECTION.v_current_user,
1796: MSC_CL_COLLECTION.v_current_date,
1797: MSC_CL_COLLECTION.v_current_user);
1798:
1799: EXCEPTION
1800:
1801: WHEN DUP_VAL_ON_INDEX THEN
1841: from MSC_CATEGORY_SET_ID_LID mcsil
1842: where mscs.SR_Category_Set_ID= mcsil.SR_Category_Set_ID
1843: and mscs.SR_INSTANCE_ID= mcsil.SR_INSTANCE_ID)
1844: AND mscs.Category_Set_NAME= mcs.Category_Set_NAME
1845: AND mscs.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id;
1846:
1847: lv_ins_records := SQL%ROWCOUNT;
1848:
1849: COMMIT;
1866: SET mcs.DESCRIPTION= c_rec.Description,
1867: mcs.CONTROL_LEVEL= c_rec.Control_Level,
1868: mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1869: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1870: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1871: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1872: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1873: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1874: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1867: mcs.CONTROL_LEVEL= c_rec.Control_Level,
1868: mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1869: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1870: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1871: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1872: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1873: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1874: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1875: WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1868: mcs.DEFAULT_FLAG= c_rec.DEFAULT_FLAG,
1869: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1870: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1871: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1872: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1873: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1874: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1875: WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1876:
1869: mcs.SR_INSTANCE_ID= c_rec.SR_Instance_ID,
1870: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1871: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1872: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1873: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1874: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1875: WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1876:
1877: IF c_rec.DEFAULT_FLAG= 1 THEN
1870: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1871: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1872: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1873: CREATION_DATE= MSC_CL_COLLECTION.v_current_date,
1874: CREATED_BY= MSC_CL_COLLECTION.v_current_user
1875: WHERE mcs.Category_Set_ID= c_rec.Category_Set_ID;
1876:
1877: IF c_rec.DEFAULT_FLAG= 1 THEN
1878: lv_default_category_set_id:= c_rec.Category_Set_ID;
1902: RAISE;
1903:
1904: ELSE
1905:
1906: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1907:
1908: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1909: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1910: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'GENERATE_ITEM_KEYS');
1938:
1939: COMMIT;
1940:
1941: /* analyse the key mapping tables */
1942: IF MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES THEN
1943: IF lv_items_stat_stale = MSC_UTIL.SYS_YES THEN
1944: msc_analyse_tables_pk.analyse_table( 'MSC_ITEM_ID_LID');
1945: END IF;
1946: IF lv_cat_stat_stale = MSC_UTIL.SYS_YES THEN
2025: AND nvl(msis.purchasing_unit_of_measure, '-99') = muom.unit_of_measure (+)
2026: -- Join to get vmi_uom_code
2027: -- AND nvl(msis.vmi_unit_of_measure, '-99') = muom1.unit_of_measure (+)
2028: /* SCE change ends */
2029: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2030:
2031: CURSOR c2 IS
2032: SELECT
2033: mssc.ORGANIZATION_ID,
2050: AND mtil.PARTNER_TYPE = 1
2051: AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2052: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2053: AND mtsil.Partner_Type(+)= 1
2054: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2055: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2056: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2057: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2058: from msc_supplier_capacities msc1
2052: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2053: AND mtsil.Partner_Type(+)= 1
2054: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2055: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2056: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2057: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2058: from msc_supplier_capacities msc1
2059: where msc1.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2060: and msc1.ORGANIZATION_ID = mssc.ORGANIZATION_ID
2084: AND mtil.PARTNER_TYPE = 1
2085: AND mtsil.SR_TP_SITE_ID(+)= mssc.Supplier_Site_ID
2086: AND mtsil.SR_INSTANCE_ID(+)= mssc.SR_Instance_ID
2087: AND mtsil.Partner_Type(+)= 1
2088: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2089: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_YES;
2090:
2091: CURSOR c5d IS
2092: SELECT mssc.ORGANIZATION_ID,
2113: AND msc1.supplier_site_id(+) = mtsil.tp_site_id
2114: AND msc1.inventory_item_id = t1.inventory_item_id
2115: AND msc1.organization_id = mssc.organization_id
2116: AND msc1.sr_instance_id = mssc.sr_instance_id
2117: AND mssc.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2118: AND msc1.collected_flag = 3
2119: AND decode(MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag , MSC_UTIL.ASL_YES,1,0)= 1;
2120:
2121: CURSOR c3 IS
2115: AND msc1.organization_id = mssc.organization_id
2116: AND msc1.sr_instance_id = mssc.sr_instance_id
2117: AND mssc.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2118: AND msc1.collected_flag = 3
2119: AND decode(MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag , MSC_UTIL.ASL_YES,1,0)= 1;
2120:
2121: CURSOR c3 IS
2122: SELECT
2123: mtil.TP_ID SUPPLIER_ID,
2139: AND mtil.PARTNER_TYPE = 1
2140: AND mtsil.SR_TP_SITE_ID(+)= mssfe.Supplier_Site_ID
2141: AND mtsil.SR_INSTANCE_ID(+)= mssfe.SR_Instance_ID
2142: AND mtsil.Partner_Type(+)= 1
2143: AND mssfe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2144:
2145: /*ASL */
2146: Cursor del_asl is
2147: SELECT T1.INVENTORY_ITEM_ID, msis.USING_ORGANIZATION_ID,
2160: AND mtsil.Partner_Type (+)= 1
2161: AND nvl(msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2162: AND nvl(msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2163: AND nvl(msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2164: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2165: And msis.deleted_flag=MSC_UTIL.SYS_YES
2166: ;
2167:
2168:
2217: AND nvl (msis.process_flag, -99) <> MSC_UTIL.G_ERROR
2218: AND nvl (msis.company_id, -1) = nvl (mtil.sr_company_id, -1)
2219: AND nvl (msis.company_id, -1) = nvl (mtsil.sr_company_id, -1)
2220: AND nvl (msis.purchasing_unit_of_measure, '-99') = muom.unit_of_measure (+)
2221: AND msis.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2222: AND msis.deleted_flag=MSC_UTIL.SYS_NO ;
2223:
2224: lv_table_name VARCHAR2(100);
2225: lv_sql_stmt VARCHAR2(5000);
2231:
2232: BEGIN
2233:
2234: /*ASL */
2235: IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN
2236: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
2237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2238: else
2239: lv_table_name:= 'MSC_SYSTEM_ITEMS';
2232: BEGIN
2233:
2234: /*ASL */
2235: IF (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.item_flag = MSC_UTIL.SYS_YES ) THEN
2236: lv_table_name:= 'SYSTEM_ITEMS_' || MSC_CL_COLLECTION.v_INSTANCE_CODE;
2237: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2238: else
2239: lv_table_name:= 'MSC_SYSTEM_ITEMS';
2240: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name in LOAD_SUPPLIER_CAPACITY is ' || lv_table_name);
2243: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' table name is ' || lv_table_name);
2244:
2245: /*ASL */
2246:
2247: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2248:
2249:
2250: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2251:
2246:
2247: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2248:
2249:
2250: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2251:
2252: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2253: v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2254: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2248:
2249:
2250: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2251:
2252: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2253: v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2254: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2255: ELSE
2256: v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'
2250: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1, 'AND nvl(COLLECTED_FLAG,1) <> 2');
2251:
2252: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2253: v_sub_str := 'AND nvl(COLLECTED_FLAG,1) <> 2';
2254: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2255: ELSE
2256: v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'
2257: ||' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2258: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2254: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2255: ELSE
2256: v_sub_str := ' AND nvl(COLLECTED_FLAG,1) <> 2'
2257: ||' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2258: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ITEM_SUPPLIERS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2259: END IF;
2260:
2261: END IF;
2262:
2261: END IF;
2262:
2263: c_count:= 0;
2264:
2265: 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
2266: FOR c_rec IN c1 LOOP
2267:
2268: -- Always setting VMI_REFRESH_FLAG to 1 incase of update/inserts
2269: -- to identify records for whom the ASL has changed since the last
2270: -- time VMI ran in CP and redo the VMI calculations.
2271:
2272: BEGIN
2273:
2274: IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
2275:
2276: UPDATE MSC_ITEM_SUPPLIERS
2277: SET
2278: PROCESSING_LEAD_TIME= c_rec.PROCESSING_LEAD_TIME,
2282: SUPPLIER_CAP_OVER_UTIL_COST= c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2283: PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2284: SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2285: ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2286: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2287: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2288: /* SCE Change Starts */
2289: /* Pull Supplier Item Name, company_id and company_site_id */
2290: SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2283: PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2284: SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2285: ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2286: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2287: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2288: /* SCE Change Starts */
2289: /* Pull Supplier Item Name, company_id and company_site_id */
2290: SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2291: PLANNER_CODE = c_rec.planner_code,
2304: MAX_MINMAX_DAYS = c_rec.MAX_MINMAX_DAYS,
2305: FORECAST_HORIZON = c_rec.FORECAST_HORIZON,
2306: FIXED_ORDER_QUANTITY = c_rec.FIXED_ORDER_QUANTITY,
2307: VMI_REFRESH_FLAG=1,
2308: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2309: WHERE PLAN_ID= -1
2310: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2311: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2312: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2316: NVL(c_rec.SUPPLIER_SITE_ID,MSC_UTIL.NULL_VALUE);
2317:
2318: END IF;
2319:
2320: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2321:
2322: INSERT INTO MSC_ITEM_SUPPLIERS
2323: ( PLAN_ID,
2324: ORGANIZATION_ID,
2374: c_rec.PURCHASING_UNIT_OF_MEASURE,
2375: c_rec.SR_INSTANCE_ID2,
2376: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2377: c_rec.SR_INSTANCE_ID,
2378: MSC_CL_COLLECTION.v_last_collection_id,
2379: MSC_CL_COLLECTION.v_current_date,
2380: MSC_CL_COLLECTION.v_current_user,
2381: MSC_CL_COLLECTION.v_current_date,
2382: /* SCE Change Starts */
2375: c_rec.SR_INSTANCE_ID2,
2376: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2377: c_rec.SR_INSTANCE_ID,
2378: MSC_CL_COLLECTION.v_last_collection_id,
2379: MSC_CL_COLLECTION.v_current_date,
2380: MSC_CL_COLLECTION.v_current_user,
2381: MSC_CL_COLLECTION.v_current_date,
2382: /* SCE Change Starts */
2383: c_rec.SUPPLIER_ITEM_NAME,
2376: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2377: c_rec.SR_INSTANCE_ID,
2378: MSC_CL_COLLECTION.v_last_collection_id,
2379: MSC_CL_COLLECTION.v_current_date,
2380: MSC_CL_COLLECTION.v_current_user,
2381: MSC_CL_COLLECTION.v_current_date,
2382: /* SCE Change Starts */
2383: c_rec.SUPPLIER_ITEM_NAME,
2384: c_rec.planner_code,
2377: c_rec.SR_INSTANCE_ID,
2378: MSC_CL_COLLECTION.v_last_collection_id,
2379: MSC_CL_COLLECTION.v_current_date,
2380: MSC_CL_COLLECTION.v_current_user,
2381: MSC_CL_COLLECTION.v_current_date,
2382: /* SCE Change Starts */
2383: c_rec.SUPPLIER_ITEM_NAME,
2384: c_rec.planner_code,
2385: c_rec.vmi_flag,
2397: c_rec.MAX_MINMAX_DAYS,
2398: c_rec.FORECAST_HORIZON,
2399: c_rec.FIXED_ORDER_QUANTITY,
2400: 1,
2401: MSC_CL_COLLECTION.v_current_user );
2402:
2403: END IF;
2404:
2405: c_count:= c_count+1;
2403: END IF;
2404:
2405: c_count:= c_count+1;
2406:
2407: IF c_count> MSC_CL_COLLECTION.PBS THEN
2408: COMMIT;
2409: c_count:= 0;
2410: END IF;
2411:
2428: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2429: RAISE;
2430:
2431: ELSE
2432: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2433:
2434: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2435: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2436: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2445: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2446: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2447: FND_MESSAGE.SET_TOKEN('VALUE',
2448: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2449: MSC_CL_COLLECTION.v_instance_id));
2450: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2451:
2452: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2453: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
2469: COMMIT;
2470: END IF ;
2471:
2472: /* ASL */
2473: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and not MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2474:
2475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');
2476: /*NOT REQUIRED
2477: IF (MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag=MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.item_flag=MSC_UTIL.SYS_YES AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2473: IF (MSC_CL_COLLECTION.v_is_incremental_refresh and not MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2474:
2475: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INCREMENTAL ASL CODE START ');
2476: /*NOT REQUIRED
2477: IF (MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag=MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.item_flag=MSC_UTIL.SYS_YES AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2478: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM LOAD_SUPPLIER_CAPACITY PROCEDURE ');
2479: ADD_NEW_IMPL_ITEM_ASL;
2480: END IF ;
2481: */
2481: */
2482:
2483: lv_sql_stmt := 'Select min (nvl(LAST_SUCC_ASL_REF_TIME,SYSDATE-365000))'
2484: ||' From msc_instance_orgs '
2485: ||' Where sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2486: ||' And organization_id '|| MSC_UTIL.v_in_org_str;
2487:
2488: EXECUTE IMMEDIATE lv_sql_stmt into lv_last_asl_collection_date;
2489: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'last successful ASL Collection refresh time is '||lv_last_asl_collection_date);
2494: ||' WHERE mis.inventory_item_id =' || del_rec.inventory_item_id
2495: ||' AND mis.USING_ORGANIZATION_ID=' || del_rec.USING_ORGANIZATION_ID
2496: ||' AND mis.SUPPLIER_ID = ' || del_rec.SUPPLIER_ID
2497: ||' AND nvl(mis.SUPPLIER_SITE_ID, -1) = nvl( :SUPPLIER_SITE_ID , -1)'
2498: ||' AND mis.sr_instance_id = ' || MSC_CL_COLLECTION.v_instance_id
2499: ||' AND mis.plan_id = -1 '
2500: ||' AND mis.ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str;
2501:
2502: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'delete query is ' || lv_sql_stmt);
2518: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2519: RAISE;
2520:
2521: ELSE
2522: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2523:
2524: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2525: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2526: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2562: SUPPLIER_CAP_OVER_UTIL_COST= c_rec.SUPPLIER_CAP_OVER_UTIL_COST,
2563: PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2564: SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2565: ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2566: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2567: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2568: /* SCE Change Starts */
2569: /* Pull Supplier Item Name, company_id and company_site_id */
2570: SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2563: PURCHASING_UNIT_OF_MEASURE= c_rec.PURCHASING_UNIT_OF_MEASURE,
2564: SR_INSTANCE_ID2= c_rec.SR_INSTANCE_ID2,
2565: ITEM_PRICE= c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2566: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2567: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2568: /* SCE Change Starts */
2569: /* Pull Supplier Item Name, company_id and company_site_id */
2570: SUPPLIER_ITEM_NAME = c_rec.SUPPLIER_ITEM_NAME,
2571: PLANNER_CODE = c_rec.planner_code,
2584: MAX_MINMAX_DAYS = c_rec.MAX_MINMAX_DAYS,
2585: FORECAST_HORIZON = c_rec.FORECAST_HORIZON,
2586: FIXED_ORDER_QUANTITY = c_rec.FIXED_ORDER_QUANTITY,
2587: VMI_REFRESH_FLAG=1,
2588: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2589: WHERE PLAN_ID= -1
2590: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2591: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2592: AND USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID
2677: ||' FROM '
2678: || lv_table_name ||' msi '
2679: ||' WHERE msi.inventory_item_id = '|| c_rec.INVENTORY_ITEM_ID
2680: ||' and msi.organization_id ' || MSC_UTIL.v_in_org_str
2681: ||' and msi.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
2682: ||' and msi.plan_id =-1 ' ;
2683:
2684: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,'the insert statement is ' || lv_sql_stmt );
2685:
2695: c_rec.PURCHASING_UNIT_OF_MEASURE,
2696: c_rec.SR_INSTANCE_ID2,
2697: c_rec.ITEM_PRICE,
2698: c_rec.SR_INSTANCE_ID,
2699: MSC_CL_COLLECTION.v_last_collection_id,
2700: MSC_CL_COLLECTION.v_current_date,
2701: MSC_CL_COLLECTION.v_current_user,
2702: MSC_CL_COLLECTION.v_current_date,
2703: c_rec.SUPPLIER_ITEM_NAME,
2696: c_rec.SR_INSTANCE_ID2,
2697: c_rec.ITEM_PRICE,
2698: c_rec.SR_INSTANCE_ID,
2699: MSC_CL_COLLECTION.v_last_collection_id,
2700: MSC_CL_COLLECTION.v_current_date,
2701: MSC_CL_COLLECTION.v_current_user,
2702: MSC_CL_COLLECTION.v_current_date,
2703: c_rec.SUPPLIER_ITEM_NAME,
2704: c_rec.planner_code,
2697: c_rec.ITEM_PRICE,
2698: c_rec.SR_INSTANCE_ID,
2699: MSC_CL_COLLECTION.v_last_collection_id,
2700: MSC_CL_COLLECTION.v_current_date,
2701: MSC_CL_COLLECTION.v_current_user,
2702: MSC_CL_COLLECTION.v_current_date,
2703: c_rec.SUPPLIER_ITEM_NAME,
2704: c_rec.planner_code,
2705: c_rec.vmi_flag,
2698: c_rec.SR_INSTANCE_ID,
2699: MSC_CL_COLLECTION.v_last_collection_id,
2700: MSC_CL_COLLECTION.v_current_date,
2701: MSC_CL_COLLECTION.v_current_user,
2702: MSC_CL_COLLECTION.v_current_date,
2703: c_rec.SUPPLIER_ITEM_NAME,
2704: c_rec.planner_code,
2705: c_rec.vmi_flag,
2706: c_rec.min_minmax_quantity,
2713: c_rec.MIN_MINMAX_DAYS,
2714: c_rec.MAX_MINMAX_DAYS,
2715: c_rec.FORECAST_HORIZON,
2716: c_rec.FIXED_ORDER_QUANTITY,
2717: MSC_CL_COLLECTION.v_current_user ;
2718:
2719: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING ORG -1 ' || SQL%ROWCOUNT);
2720:
2721: ELSE
2774: c_rec.PURCHASING_UNIT_OF_MEASURE,
2775: c_rec.SR_INSTANCE_ID2,
2776: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2777: c_rec.SR_INSTANCE_ID,
2778: MSC_CL_COLLECTION.v_last_collection_id,
2779: MSC_CL_COLLECTION.v_current_date,
2780: MSC_CL_COLLECTION.v_current_user,
2781: MSC_CL_COLLECTION.v_current_date,
2782: /* SCE Change Starts */
2775: c_rec.SR_INSTANCE_ID2,
2776: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2777: c_rec.SR_INSTANCE_ID,
2778: MSC_CL_COLLECTION.v_last_collection_id,
2779: MSC_CL_COLLECTION.v_current_date,
2780: MSC_CL_COLLECTION.v_current_user,
2781: MSC_CL_COLLECTION.v_current_date,
2782: /* SCE Change Starts */
2783: c_rec.SUPPLIER_ITEM_NAME,
2776: c_rec.ITEM_PRICE, -- Item Price by Supplier Fix
2777: c_rec.SR_INSTANCE_ID,
2778: MSC_CL_COLLECTION.v_last_collection_id,
2779: MSC_CL_COLLECTION.v_current_date,
2780: MSC_CL_COLLECTION.v_current_user,
2781: MSC_CL_COLLECTION.v_current_date,
2782: /* SCE Change Starts */
2783: c_rec.SUPPLIER_ITEM_NAME,
2784: c_rec.planner_code,
2777: c_rec.SR_INSTANCE_ID,
2778: MSC_CL_COLLECTION.v_last_collection_id,
2779: MSC_CL_COLLECTION.v_current_date,
2780: MSC_CL_COLLECTION.v_current_user,
2781: MSC_CL_COLLECTION.v_current_date,
2782: /* SCE Change Starts */
2783: c_rec.SUPPLIER_ITEM_NAME,
2784: c_rec.planner_code,
2785: c_rec.vmi_flag,
2797: c_rec.MAX_MINMAX_DAYS,
2798: c_rec.FORECAST_HORIZON,
2799: c_rec.FIXED_ORDER_QUANTITY,
2800: 1,
2801: MSC_CL_COLLECTION.v_current_user );
2802:
2803: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROW INSERTED WITH USING org id not -1 ' || SQL%ROWCOUNT);
2804:
2805: END IF ;
2825: RAISE;
2826:
2827: ELSE
2828:
2829: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2830:
2831: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2832: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2833: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2843: END IF ; -- v_is_incremental
2844:
2845: /*ASL */
2846:
2847: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2848: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2849:
2850: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2851:
2844:
2845: /*ASL */
2846:
2847: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2848: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2849:
2850: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2851:
2852: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2846:
2847: IF MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS107 AND
2848: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2849:
2850: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2851:
2852: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2853:
2854: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2848: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2849:
2850: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2851:
2852: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2853:
2854: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2855: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2856: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2850: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2851:
2852: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2853:
2854: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2855: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2856: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2857: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2858: else
2851:
2852: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2853:
2854: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2855: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2856: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2857: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2858: else
2859: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2853:
2854: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2855: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2856: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2857: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2858: else
2859: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2860: end if ;
2861: ELSE
2855: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2856: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2857: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2858: else
2859: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2860: end if ;
2861: ELSE
2862: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2863: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2858: else
2859: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2860: end if ;
2861: ELSE
2862: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2863: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2864: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2865: else
2866: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2860: end if ;
2861: ELSE
2862: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2863: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2864: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2865: else
2866: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2867: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2868: end if ;
2863: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2864: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2865: else
2866: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2867: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2868: end if ;
2869: END IF;
2870:
2871: END IF;
2871: END IF;
2872:
2873: --============ Incremental Refresh for DELETE =========
2874:
2875: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2876:
2877: FOR c_rec IN c2d LOOP
2878:
2879: UPDATE MSC_SUPPLIER_CAPACITIES
2879: UPDATE MSC_SUPPLIER_CAPACITIES
2880: SET
2881: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2882: CAPACITY= NULL,
2883: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2884: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2885: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2886: WHERE PLAN_ID= -1
2887: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2880: SET
2881: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2882: CAPACITY= NULL,
2883: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2884: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2885: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2886: WHERE PLAN_ID= -1
2887: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2888: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2881: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2882: CAPACITY= NULL,
2883: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2884: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2885: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2886: WHERE PLAN_ID= -1
2887: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2888: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2889: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2919: FOR c_rec IN c2 LOOP
2920:
2921: BEGIN
2922:
2923: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2924:
2925: UPDATE MSC_SUPPLIER_CAPACITIES
2926: SET
2927: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2926: SET
2927: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2928: TO_DATE= c_rec.TO_DATE,
2929: CAPACITY= c_rec.CAPACITY,
2930: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2931: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2932: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2933: WHERE PLAN_ID= -1
2934: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2927: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2928: TO_DATE= c_rec.TO_DATE,
2929: CAPACITY= c_rec.CAPACITY,
2930: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2931: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2932: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2933: WHERE PLAN_ID= -1
2934: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2935: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2928: TO_DATE= c_rec.TO_DATE,
2929: CAPACITY= c_rec.CAPACITY,
2930: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2931: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2932: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2933: WHERE PLAN_ID= -1
2934: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2935: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2936: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2941: AND COLLECTED_FLAG=1;
2942:
2943: END IF;
2944:
2945: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2946:
2947: INSERT INTO MSC_SUPPLIER_CAPACITIES
2948: ( TRANSACTION_ID,
2949: PLAN_ID,
2974: c_rec.TO_DATE,
2975: c_rec.CAPACITY,
2976: c_rec.SR_INSTANCE_ID,
2977: 1,
2978: MSC_CL_COLLECTION.v_last_collection_id,
2979: MSC_CL_COLLECTION.v_current_date,
2980: MSC_CL_COLLECTION.v_current_user,
2981: MSC_CL_COLLECTION.v_current_date,
2982: MSC_CL_COLLECTION.v_current_user );
2975: c_rec.CAPACITY,
2976: c_rec.SR_INSTANCE_ID,
2977: 1,
2978: MSC_CL_COLLECTION.v_last_collection_id,
2979: MSC_CL_COLLECTION.v_current_date,
2980: MSC_CL_COLLECTION.v_current_user,
2981: MSC_CL_COLLECTION.v_current_date,
2982: MSC_CL_COLLECTION.v_current_user );
2983:
2976: c_rec.SR_INSTANCE_ID,
2977: 1,
2978: MSC_CL_COLLECTION.v_last_collection_id,
2979: MSC_CL_COLLECTION.v_current_date,
2980: MSC_CL_COLLECTION.v_current_user,
2981: MSC_CL_COLLECTION.v_current_date,
2982: MSC_CL_COLLECTION.v_current_user );
2983:
2984: END IF;
2977: 1,
2978: MSC_CL_COLLECTION.v_last_collection_id,
2979: MSC_CL_COLLECTION.v_current_date,
2980: MSC_CL_COLLECTION.v_current_user,
2981: MSC_CL_COLLECTION.v_current_date,
2982: MSC_CL_COLLECTION.v_current_user );
2983:
2984: END IF;
2985:
2978: MSC_CL_COLLECTION.v_last_collection_id,
2979: MSC_CL_COLLECTION.v_current_date,
2980: MSC_CL_COLLECTION.v_current_user,
2981: MSC_CL_COLLECTION.v_current_date,
2982: MSC_CL_COLLECTION.v_current_user );
2983:
2984: END IF;
2985:
2986: c_count:= c_count+1;
2984: END IF;
2985:
2986: c_count:= c_count+1;
2987:
2988: IF c_count> MSC_CL_COLLECTION.PBS THEN
2989: COMMIT;
2990: c_count:= 0;
2991: END IF;
2992:
3005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3006: RAISE;
3007:
3008: ELSE
3009: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3010:
3011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3012: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3013: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3022: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3023: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3024: FND_MESSAGE.SET_TOKEN('VALUE',
3025: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3026: MSC_CL_COLLECTION.v_instance_id));
3027: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3028:
3029: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3030: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3049: END LOOP;
3050:
3051: COMMIT;
3052:
3053: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3054:
3055: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3056:
3057: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3051: COMMIT;
3052:
3053: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3054:
3055: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3056:
3057: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3058: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3059: ELSE
3053: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3054:
3055: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3056:
3057: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3058: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3059: ELSE
3060: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3061: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3054:
3055: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3056:
3057: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3058: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3059: ELSE
3060: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3061: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3062: END IF;
3057: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3058: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1);
3059: ELSE
3060: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3061: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_FLEX_FENCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3062: END IF;
3063:
3064: END IF;
3065:
3068: FOR c_rec IN c3 LOOP
3069:
3070: BEGIN
3071:
3072: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3073:
3074: UPDATE MSC_SUPPLIER_FLEX_FENCES
3075: SET
3076: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3074: UPDATE MSC_SUPPLIER_FLEX_FENCES
3075: SET
3076: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3077: TOLERANCE_PERCENTAGE= c_rec.TOLERANCE_PERCENTAGE,
3078: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3079: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3080: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3081: WHERE PLAN_ID= -1
3082: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3075: SET
3076: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3077: TOLERANCE_PERCENTAGE= c_rec.TOLERANCE_PERCENTAGE,
3078: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3079: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3080: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3081: WHERE PLAN_ID= -1
3082: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3083: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3076: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
3077: TOLERANCE_PERCENTAGE= c_rec.TOLERANCE_PERCENTAGE,
3078: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3079: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3080: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3081: WHERE PLAN_ID= -1
3082: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3083: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
3084: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3088: AND FENCE_DAYS= c_rec.FENCE_DAYS;
3089:
3090: END IF;
3091:
3092: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
3093:
3094: INSERT INTO MSC_SUPPLIER_FLEX_FENCES
3095: ( TRANSACTION_ID,
3096: PLAN_ID,
3117: c_rec.INVENTORY_ITEM_ID,
3118: c_rec.FENCE_DAYS,
3119: c_rec.TOLERANCE_PERCENTAGE,
3120: c_rec.SR_INSTANCE_ID,
3121: MSC_CL_COLLECTION.v_last_collection_id,
3122: MSC_CL_COLLECTION.v_current_date,
3123: MSC_CL_COLLECTION.v_current_user,
3124: MSC_CL_COLLECTION.v_current_date,
3125: MSC_CL_COLLECTION.v_current_user );
3118: c_rec.FENCE_DAYS,
3119: c_rec.TOLERANCE_PERCENTAGE,
3120: c_rec.SR_INSTANCE_ID,
3121: MSC_CL_COLLECTION.v_last_collection_id,
3122: MSC_CL_COLLECTION.v_current_date,
3123: MSC_CL_COLLECTION.v_current_user,
3124: MSC_CL_COLLECTION.v_current_date,
3125: MSC_CL_COLLECTION.v_current_user );
3126:
3119: c_rec.TOLERANCE_PERCENTAGE,
3120: c_rec.SR_INSTANCE_ID,
3121: MSC_CL_COLLECTION.v_last_collection_id,
3122: MSC_CL_COLLECTION.v_current_date,
3123: MSC_CL_COLLECTION.v_current_user,
3124: MSC_CL_COLLECTION.v_current_date,
3125: MSC_CL_COLLECTION.v_current_user );
3126:
3127: END IF;
3120: c_rec.SR_INSTANCE_ID,
3121: MSC_CL_COLLECTION.v_last_collection_id,
3122: MSC_CL_COLLECTION.v_current_date,
3123: MSC_CL_COLLECTION.v_current_user,
3124: MSC_CL_COLLECTION.v_current_date,
3125: MSC_CL_COLLECTION.v_current_user );
3126:
3127: END IF;
3128:
3121: MSC_CL_COLLECTION.v_last_collection_id,
3122: MSC_CL_COLLECTION.v_current_date,
3123: MSC_CL_COLLECTION.v_current_user,
3124: MSC_CL_COLLECTION.v_current_date,
3125: MSC_CL_COLLECTION.v_current_user );
3126:
3127: END IF;
3128:
3129: c_count:= c_count+1;
3127: END IF;
3128:
3129: c_count:= c_count+1;
3130:
3131: IF c_count> MSC_CL_COLLECTION.PBS THEN
3132: COMMIT;
3133: c_count:= 0;
3134: END IF;
3135:
3148: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3149: RAISE;
3150:
3151: ELSE
3152: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3153:
3154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3155: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3156: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3165: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3166: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3167: FND_MESSAGE.SET_TOKEN('VALUE',
3168: MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3169: MSC_CL_COLLECTION.v_instance_id));
3170: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3171:
3172: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3173: FND_MESSAGE.SET_TOKEN('COLUMN', 'SUPPLIER_ID');
3192: END LOOP;
3193:
3194: COMMIT;
3195:
3196: END IF; -- MSC_CL_COLLECTION.v_apps_ver
3197:
3198: END LOAD_SUPPLIER_CAPACITY;
3199:
3200: --==================================================================
3359: AND t3.sr_instance_id(+)= msi.sr_instance_id
3360: AND mtp.sr_tp_id(+)= msi.organization_id
3361: AND mtp.partner_type(+) = 3
3362: AND mtp.sr_instance_id(+)= msi.sr_instance_id
3363: AND msi.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
3364:
3365: /*
3366: CURSOR c2 IS
3367: SELECT distinct
3369: msi.abc_class_name,
3370: msi.sr_instance_id,
3371: msi.organization_id
3372: FROM MSC_ST_SYSTEM_ITEMS msi
3373: WHERE msi.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
3374: AND msi.abc_class_id is not null
3375: AND msi.abc_class_name is not null;*/
3376:
3377: c_count NUMBER:=0;
3524: lv_retcode NUMBER;
3525:
3526: BEGIN
3527:
3528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3529: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
3530: ELSE
3531: lv_tbl:= 'MSC_SYSTEM_ITEMS';
3532: END IF;
3525:
3526: BEGIN
3527:
3528: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3529: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
3530: ELSE
3531: lv_tbl:= 'MSC_SYSTEM_ITEMS';
3532: END IF;
3533:
3811: ||' :v_current_user,'
3812: ||' :PEGGING_DEMAND_WINDOW_DAYS,'
3813: ||' :PEGGING_SUPPLY_WINDOW_DAYS )';
3814:
3815: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3816: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3817:
3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3819: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3812: ||' :PEGGING_DEMAND_WINDOW_DAYS,'
3813: ||' :PEGGING_SUPPLY_WINDOW_DAYS )';
3814:
3815: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3816: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3817:
3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3819: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3820: ELSE
3814:
3815: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3816: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3817:
3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3819: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3820: ELSE
3821: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3822: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3815: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND
3816: MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
3817:
3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3819: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3820: ELSE
3821: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3822: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3823: END IF;
3818: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3819: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1);
3820: ELSE
3821: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3822: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SYSTEM_ITEMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
3823: END IF;
3824: END IF;
3825:
3826: -- delete is not supported
3823: END IF;
3824: END IF;
3825:
3826: -- delete is not supported
3827: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3828: BEGIN
3829: lv_sql_ins :=
3830: ' INSERT /*+ append */ '
3831: || ' INTO '||lv_tbl
4119: ||' AND t3.sr_instance_id(+) = msi.sr_instance_id '
4120: ||' AND mtp.sr_tp_id(+) = msi.organization_id '
4121: ||' AND mtp.partner_type(+) = 3 '
4122: ||' AND mtp.sr_instance_id(+) = msi.sr_instance_id '
4123: ||' AND msi.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
4124:
4125: EXECUTE IMMEDIATE lv_sql_ins
4126: 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;
4127:
4122: ||' AND mtp.sr_instance_id(+) = msi.sr_instance_id '
4123: ||' AND msi.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id;
4124:
4125: EXECUTE IMMEDIATE lv_sql_ins
4126: 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;
4127:
4128: commit;
4129: EXCEPTION
4130: WHEN OTHERS THEN
4140:
4141: END;
4142: END IF;
4143:
4144: IF (MSC_CL_COLLECTION.v_is_incremental_refresh OR
4145: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_APS_SCE OR
4146: lv_MSC_CONFIGURATION = MSC_UTIL.G_CONF_SCE) THEN
4147: c_count :=0;
4148:
4312:
4313:
4314: BEGIN
4315:
4316: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
4317:
4318: UPDATE MSC_SYSTEM_ITEMS
4319: SET
4320: ITEM_NAME= lb_ITEM_NAME(j),
4435: CREATE_SUPPLY_FLAG = lb_CREATE_SUPPLY_FLAG(j),
4436: SERIAL_NUMBER_CONTROL_CODE = lb_SERIAL_NUMBER_CONTROL_CODE(j),
4437: SR_INVENTORY_ITEM_ID= lb_SR_INVENTORY_ITEM_ID(j),
4438: EAM_ITEM_TYPE = lb_EAM_ITEM_TYPE(j), /* ds change change */
4439: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4440: /* ATP SUMMARY CHANGES Added the Refresh_number */
4441: REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4442: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
4443: REPAIR_LEAD_TIME= lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4437: SR_INVENTORY_ITEM_ID= lb_SR_INVENTORY_ITEM_ID(j),
4438: EAM_ITEM_TYPE = lb_EAM_ITEM_TYPE(j), /* ds change change */
4439: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4440: /* ATP SUMMARY CHANGES Added the Refresh_number */
4441: REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4442: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
4443: REPAIR_LEAD_TIME= lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4444: PREPOSITION_POINT = lb_PRE_POSITIONING_POINT(j),
4445: REPAIR_PROGRAM = lb_REPAIR_PROGRAM(j),
4438: EAM_ITEM_TYPE = lb_EAM_ITEM_TYPE(j), /* ds change change */
4439: LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
4440: /* ATP SUMMARY CHANGES Added the Refresh_number */
4441: REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
4442: LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
4443: REPAIR_LEAD_TIME= lb_REPAIR_LEAD_TIME(j), --# For Bug 5606037 SRP Changes
4444: PREPOSITION_POINT = lb_PRE_POSITIONING_POINT(j),
4445: REPAIR_PROGRAM = lb_REPAIR_PROGRAM(j),
4446: REPAIR_YIELD = lb_REPAIR_YIELD(j),
4587: lb_REPAIR_PROGRAM(j),
4588: lb_UNSATISFIED_DEMAND_FACTOR(j),
4589: lb_MIN_SUP_DEM_PERCENT(j),
4590: lb_DMD_SATISFIED_PERCENT(j),
4591: MSC_CL_COLLECTION.v_last_collection_id,
4592: MSC_CL_COLLECTION.v_current_date,
4593: MSC_CL_COLLECTION.v_current_user,
4594: MSC_CL_COLLECTION.v_current_date,
4595: MSC_CL_COLLECTION.v_current_user,
4588: lb_UNSATISFIED_DEMAND_FACTOR(j),
4589: lb_MIN_SUP_DEM_PERCENT(j),
4590: lb_DMD_SATISFIED_PERCENT(j),
4591: MSC_CL_COLLECTION.v_last_collection_id,
4592: MSC_CL_COLLECTION.v_current_date,
4593: MSC_CL_COLLECTION.v_current_user,
4594: MSC_CL_COLLECTION.v_current_date,
4595: MSC_CL_COLLECTION.v_current_user,
4596: lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4589: lb_MIN_SUP_DEM_PERCENT(j),
4590: lb_DMD_SATISFIED_PERCENT(j),
4591: MSC_CL_COLLECTION.v_last_collection_id,
4592: MSC_CL_COLLECTION.v_current_date,
4593: MSC_CL_COLLECTION.v_current_user,
4594: MSC_CL_COLLECTION.v_current_date,
4595: MSC_CL_COLLECTION.v_current_user,
4596: lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4597: lb_PEGGING_SUPPLY_WINDOW_DAYS(j);
4590: lb_DMD_SATISFIED_PERCENT(j),
4591: MSC_CL_COLLECTION.v_last_collection_id,
4592: MSC_CL_COLLECTION.v_current_date,
4593: MSC_CL_COLLECTION.v_current_user,
4594: MSC_CL_COLLECTION.v_current_date,
4595: MSC_CL_COLLECTION.v_current_user,
4596: lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4597: lb_PEGGING_SUPPLY_WINDOW_DAYS(j);
4598:
4591: MSC_CL_COLLECTION.v_last_collection_id,
4592: MSC_CL_COLLECTION.v_current_date,
4593: MSC_CL_COLLECTION.v_current_user,
4594: MSC_CL_COLLECTION.v_current_date,
4595: MSC_CL_COLLECTION.v_current_user,
4596: lb_PEGGING_DEMAND_WINDOW_DAYS(j),
4597: lb_PEGGING_SUPPLY_WINDOW_DAYS(j);
4598:
4599: END IF;
4605: WHERE inventory_item_id = lb_INVENTORY_ITEM_ID(j);
4606: END IF;
4607: c_count:= c_count+1;
4608:
4609: IF c_count>MSC_CL_COLLECTION.PBS THEN
4610:
4611: COMMIT;
4612:
4613: c_count:= 0;
4628: RAISE;
4629:
4630: ELSE
4631:
4632: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4633:
4634: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
4635: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
4636: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM');
4645: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4646: FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
4647: FND_MESSAGE.SET_TOKEN('VALUE',
4648: MSC_GET_NAME.ORG_CODE( lb_ORGANIZATION_ID(j),
4649: MSC_CL_COLLECTION.v_instance_id));
4650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
4651:
4652: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
4653: FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_INVENTORY_ITEM_ID');
4667: END IF;
4668:
4669: BEGIN
4670:
4671: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
4672:
4673: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
4674:
4675: lv_sql_stmt:=
4669: BEGIN
4670:
4671: IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
4672:
4673: lv_tbl:= 'SYSTEM_ITEMS_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
4674:
4675: lv_sql_stmt:=
4676: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
4677: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SYSTEM_ITEMS'
4674:
4675: lv_sql_stmt:=
4676: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
4677: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_SYSTEM_ITEMS'
4678: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4679: ||' AND plan_id = -1 '
4680: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4681:
4682: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4685: COMMIT;
4686:
4687: END IF;
4688:
4689: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4690: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4691: lv_retcode,
4692: 'MSC_SYSTEM_ITEMS',
4693: MSC_CL_COLLECTION.v_INSTANCE_CODE,
4686:
4687: END IF;
4688:
4689: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4690: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4691: lv_retcode,
4692: 'MSC_SYSTEM_ITEMS',
4693: MSC_CL_COLLECTION.v_INSTANCE_CODE,
4694: MSC_UTIL.G_ERROR
4689: IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4690: MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4691: lv_retcode,
4692: 'MSC_SYSTEM_ITEMS',
4693: MSC_CL_COLLECTION.v_INSTANCE_CODE,
4694: MSC_UTIL.G_ERROR
4695: );
4696:
4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4695: );
4696:
4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4699: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4700: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4701: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4702: END IF;
4703:
4697: IF lv_retcode = MSC_UTIL.G_ERROR THEN
4698: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4699: RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4700: ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4701: MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4702: END IF;
4703:
4704: END IF;
4705:
4703:
4704: END IF;
4705:
4706: /*call to insert ASL */
4707: IF ( NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
4708: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ADD_NEW_IMPL_ITEM_ASL PROCEDURE BEING CALLED FROM ITEM PROCEDURE ');
4709: ADD_NEW_IMPL_ITEM_ASL;
4710: END IF ;
4711: