[Home] [Help]
2050: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2051: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2052: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2053: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2054: from msc_supplier_capacities msc1
2055: where msc1.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2056: and msc1.ORGANIZATION_ID = mssc.ORGANIZATION_ID
2057: and msc1.INVENTORY_ITEM_ID = t1.INVENTORY_ITEM_ID
2058: and msc1.SUPPLIER_ID = mtil.TP_ID
2095: FROM MSC_TP_ID_LID mtil,
2096: MSC_TP_SITE_ID_LID mtsil,
2097: MSC_ITEM_ID_LID t1,
2098: MSC_ST_SUPPLIER_CAPACITIES mssc,
2099: MSC_SUPPLIER_CAPACITIES msc1
2100: WHERE t1.SR_INVENTORY_ITEM_ID= mssc.inventory_item_id
2101: AND t1.sr_instance_id= mssc.sr_instance_id
2102: AND mtil.SR_TP_ID = mssc.SUPPLIER_ID
2103: AND mtil.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2844: MSC_CL_COLLECTION.v_apps_ver <> MSC_UTIL.G_APPS110 THEN
2845:
2846: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2847:
2848: --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2849:
2850: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2851: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2849:
2850: IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2851: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2853: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2854: else
2855: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2856: end if ;
2857: ELSE
2851: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2852: v_sub_str :=' AND COLLECTED_FLAG <> 3';
2853: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2854: else
2855: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1);
2856: end if ;
2857: ELSE
2858: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2859: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2856: end if ;
2857: ELSE
2858: if MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag= MSC_UTIL.ASL_YES_RETAIN_CP THEN
2859: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2860: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2861: else
2862: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2863: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2864: end if ;
2859: v_sub_str :=' AND COLLECTED_FLAG <> 3 AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2860: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2861: else
2862: v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2863: MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SUPPLIER_CAPACITIES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2864: end if ;
2865: END IF;
2866:
2867: END IF;
2871: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2872:
2873: FOR c_rec IN c2d LOOP
2874:
2875: UPDATE MSC_SUPPLIER_CAPACITIES
2876: SET
2877: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2878: CAPACITY= NULL,
2879: REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2892: END LOOP;
2893:
2894: FOR c_rec IN c5d LOOP
2895:
2896: DELETE FROM MSC_SUPPLIER_CAPACITIES
2897: WHERE PLAN_ID= -1
2898: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2899: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2900: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2917: BEGIN
2918:
2919: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2920:
2921: UPDATE MSC_SUPPLIER_CAPACITIES
2922: SET
2923: USING_ORGANIZATION_ID= c_rec.USING_ORGANIZATION_ID,
2924: TO_DATE= c_rec.TO_DATE,
2925: CAPACITY= c_rec.CAPACITY,
2939: END IF;
2940:
2941: IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2942:
2943: INSERT INTO MSC_SUPPLIER_CAPACITIES
2944: ( TRANSACTION_ID,
2945: PLAN_ID,
2946: ORGANIZATION_ID,
2947: USING_ORGANIZATION_ID,
2958: LAST_UPDATED_BY,
2959: CREATION_DATE,
2960: CREATED_BY)
2961: VALUES
2962: ( MSC_SUPPLIER_CAPACITIES_S.NEXTVAL,
2963: -1,
2964: c_rec.ORGANIZATION_ID,
2965: c_rec.USING_ORGANIZATION_ID,
2966: c_rec.SUPPLIER_ID,
2994:
2995: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2996: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2997: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
2998: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
2999: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3000:
3001: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3002: RAISE;
3006:
3007: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3008: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3009: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3010: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3012:
3013: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3014: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');