[Home] [Help]
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:
493: /* below statement will be used to insert new recs in case of net change */
494: lv_sql_stmt :=
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
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
751: ( select t1.INVENTORY_ITEM_ID, t2.INVENTORY_ITEM_ID, msis.ORGANIZATION_ID, msis.RELATIONSHIP_TYPE
752: from MSC_ST_ITEM_SUBSTITUTES msis,
753: MSC_ITEM_ID_LID t1,
802: FOR j IN 1..lb_HIGHER_ITEM_ID.COUNT LOOP
803:
804:
805:
806: UPDATE MSC_ITEM_SUBSTITUTES
807: SET
808: RECIPROCAL_FLAG = lb_RECIPROCAL_FLAG(j),
809: SUBSTITUTION_SET = lb_SUBSTITUTION_SET(j),
810: CUSTOMER_ID = lb_CUSTOMER_ID(j),
902: END IF;
903:
904: FOR j IN 1..lb_HIGHER_ITEM_ID.COUNT LOOP
905:
906: UPDATE MSC_ITEM_SUBSTITUTES
907: SET
908: RECIPROCAL_FLAG = lb_RECIPROCAL_FLAG(j),
909: EFFECTIVE_DATE = lb_EFFECTIVE_DATE(j),
910: DISABLE_DATE = lb_DISABLE_DATE(j),
971: lv_tbl:= 'ITEM_SUBSTITUTES_'||MSC_CL_COLLECTION.v_INSTANCE_CODE;
972:
973: lv_sql_stmt:=
974: 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
975: ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ITEM_SUBSTITUTES'
976: ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
977: ||' AND plan_id = -1 '
978: ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
979:
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
993: );
994:
1007:
1008: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1009: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1010: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1011: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1012: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1013:
1014: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1015: RAISE;
1020:
1021: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1022: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1023: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_SUBSTITUTES');
1024: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ITEM_SUBSTITUTES');
1025: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1026:
1027: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1028: FND_MESSAGE.SET_TOKEN('COLUMN', 'HIGHER_ITEM_ID');