[Home] [Help]
2054: AND mssc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2055: AND mssc.DELETED_FLAG= MSC_UTIL.SYS_NO
2056: AND NVL(decode( MSC_CL_COLLECTION.v_coll_prec.app_supp_cap_flag, MSC_UTIL.ASL_YES, 1,
2057: MSC_UTIL.ASL_YES_RETAIN_CP, (select distinct 0
2058: from msc_supplier_capacities msc1
2059: where msc1.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
2060: and msc1.ORGANIZATION_ID = mssc.ORGANIZATION_ID
2061: and msc1.INVENTORY_ITEM_ID = t1.INVENTORY_ITEM_ID
2062: and msc1.SUPPLIER_ID = mtil.TP_ID
2099: FROM MSC_TP_ID_LID mtil,
2100: MSC_TP_SITE_ID_LID mtsil,
2101: MSC_ITEM_ID_LID t1,
2102: MSC_ST_SUPPLIER_CAPACITIES mssc,
2103: MSC_SUPPLIER_CAPACITIES msc1
2104: WHERE t1.SR_INVENTORY_ITEM_ID= mssc.inventory_item_id
2105: AND t1.sr_instance_id= mssc.sr_instance_id
2106: AND mtil.SR_TP_ID = mssc.SUPPLIER_ID
2107: AND mtil.SR_INSTANCE_ID = mssc.SR_INSTANCE_ID
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';
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;
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;
2875: IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2876:
2877: FOR c_rec IN c2d LOOP
2878:
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,
2896: END LOOP;
2897:
2898: FOR c_rec IN c5d LOOP
2899:
2900: DELETE FROM MSC_SUPPLIER_CAPACITIES
2901: WHERE PLAN_ID= -1
2902: AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2903: AND INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID
2904: AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
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,
2928: TO_DATE= c_rec.TO_DATE,
2929: CAPACITY= c_rec.CAPACITY,
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,
2950: ORGANIZATION_ID,
2951: USING_ORGANIZATION_ID,
2962: LAST_UPDATED_BY,
2963: CREATION_DATE,
2964: CREATED_BY)
2965: VALUES
2966: ( MSC_SUPPLIER_CAPACITIES_S.NEXTVAL,
2967: -1,
2968: c_rec.ORGANIZATION_ID,
2969: c_rec.USING_ORGANIZATION_ID,
2970: c_rec.SUPPLIER_ID,
2998:
2999: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3000: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3001: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3002: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3003: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3004:
3005: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3006: RAISE;
3010:
3011: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3012: FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3013: FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLIER_CAPACITY');
3014: FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIER_CAPACITIES');
3015: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3016:
3017: FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3018: FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');