MSC_AP_EGP_SYSTEM_ITEMS_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

ORGANIZATION_ID ORGANIZATION_CODE INVENTORY_ITEM_ID ITEM_NUMBER LOTS_EXPIRATION LOT_CONTROL_CODE SHRINKAGE_RATE FIXED_DAYS_SUPPLY FIXED_ORDER_QUANTITY FIXED_LOT_MULTIPLIER MINIMUM_ORDER_QUANTITY MAXIMUM_ORDER_QUANTITY CRITICAL_COMPONENT_FLAG ROUNDING_CONTROL_TYPE PLANNING_TIME_FENCE_CODE PLANNING_TIME_FENCE_DAYS DEMAND_TIME_FENCE_DAYS DEMAND_TIME_FENCE_CODE DESCRIPTION RELEASE_TIME_FENCE_CODE RELEASE_TIME_FENCE_DAYS MRP_PLANNING_CODE FIXED_LEAD_TIME VARIABLE_LEAD_TIME PREPROCESSING_LEAD_TIME POSTPROCESSING_LEAD_TIME FULL_LEAD_TIME CUMULATIVE_TOTAL_LEAD_TIME CUM_MANUFACTURING_LEAD_TIME UOM_CODE UNIT_WEIGHT WEIGHT_UOM_CODE UNIT_VOLUME VOLUME_UOM_CODE PRODUCT_FAMILY_ITEM_ID ATP_COMPONENTS_FLAG BUILD_IN_WIP_FLAG PURCHASING_ENABLED_FLAG PLANNING_MAKE_BUY_CODE STANDARD_COST CARRYING_COST ORDER_COST MATERIAL_COST RESOURCE_COST LIST_PRICE_PER_UNIT AVERAGE_DISCOUNT WIP_SUPPLY_TYPE SAFETY_STOCK_CODE SAFETY_STOCK_PERCENT SAFETY_STOCK_BUCKET_DAYS PLANNER_CODE BOM_ITEM_TYPE ATO_FORECAST_CONTROL EFFECTIVITY_CONTROL ACCEPTABLE_EARLY_DELIVERY CALCULATE_ATP INVENTORY_ITEM_FLAG SOURCE_ORGANIZATION_ID BASE_ITEM_ID ATP_FLAG PICK_COMPONENTS_FLAG REPLENISH_TO_ORDER_FLAG CREATE_SUPPLY_FLAG VMI_FIXED_ORDER_QUANTITY LIFE_CYCLE_PHASE SHIPPABLE_ITEM_FLAG CUSTOMER_ORDER_FLAG SHIP_MODEL_COMPLETE_FLAG BACK_TO_BACK_FLAG ENGINEERING_ITEM_FLAG MIN_SHELF_LIFE_DAYS AUTO_CREATED_CONFIG_FLAG OUTSIDE_PROCESS_SERVICE_FLAG BUYER_NAME ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_NUMBER6 ATTRIBUTE_NUMBER7 ATTRIBUTE_NUMBER8 ATTRIBUTE_NUMBER9 ATTRIBUTE_NUMBER10 ATTRIBUTE_TIMESTAMP1 ATTRIBUTE_TIMESTAMP2 ATTRIBUTE_TIMESTAMP3 ATTRIBUTE_TIMESTAMP4 ATTRIBUTE_TIMESTAMP5 GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE_DATE1 GLOBAL_ATTRIBUTE_DATE2 GLOBAL_ATTRIBUTE_DATE3 GLOBAL_ATTRIBUTE_DATE4 GLOBAL_ATTRIBUTE_DATE5 GLOBAL_ATTRIBUTE_NUMBER1 GLOBAL_ATTRIBUTE_NUMBER2 GLOBAL_ATTRIBUTE_NUMBER3 GLOBAL_ATTRIBUTE_NUMBER4 GLOBAL_ATTRIBUTE_NUMBER5 INDIVISIBLE_FLAG ENFORCE_PURCHASE_LT HARD_PEGGING_LEVEL COMN_SUPPLY_PRJ_DEMAND_FLAG LEAD_TIME_LOT_SIZE ASSET_TRACKED_FLAG CREATE_SUPPLY_AFTER_DATE SERIAL_CONTROL_ENABLED_FLAG ORDER_MODIFIER_TO_START_QTY

Query

SQL_Statement

SELECT esib.ORGANIZATION_ID, esib.ORGANIZATION_CODE, esib.INVENTORY_ITEM_ID, esib.ITEM_NUMBER, decode(nvl(esib.LOT_CONTROL_CODE,1),1,1, nvl(esib.SHELF_LIFE_CODE,1)) LOTS_EXPIRATION, NVL(esib.LOT_CONTROL_CODE,1) LOT_CONTROL_CODE, esib.SHRINKAGE_RATE, esib.FIXED_DAYS_SUPPLY, esib.FIXED_ORDER_QUANTITY, esib.FIXED_LOT_MULTIPLIER, esib.MINIMUM_ORDER_QUANTITY, esib.MAXIMUM_ORDER_QUANTITY, esib.critical_component_flag, decode(esib.ROUNDING_CONTROL_TYPE,1,1,2) ROUNDING_CONTROL_TYPE, esib.PLANNING_TIME_FENCE_CODE, esib.PLANNING_TIME_FENCE_DAYS, esib.DEMAND_TIME_FENCE_DAYS, esib.DEMAND_TIME_FENCE_CODE, esib.DESCRIPTION, esib.RELEASE_TIME_FENCE_CODE, esib.RELEASE_TIME_FENCE_DAYS, NVL(esib.MRP_PLANNING_CODE, 6) MRP_PLANNING_CODE, esib.FIXED_LEAD_TIME, esib.VARIABLE_LEAD_TIME, esib.PREPROCESSING_LEAD_TIME, esib.POSTPROCESSING_LEAD_TIME, esib.FULL_LEAD_TIME, esib.CUMULATIVE_TOTAL_LEAD_TIME, esib.CUM_MANUFACTURING_LEAD_TIME, esib.PRIMARY_UOM_CODE UOM_CODE, esib.UNIT_WEIGHT, esib.WEIGHT_UOM_CODE, esib.UNIT_VOLUME, esib.VOLUME_UOM_CODE, esib.PRODUCT_FAMILY_ITEM_ID, decode(esib.ATP_COMPONENTS_FLAG,'Y',1,2) ATP_COMPONENTS_FLAG, decode(esib.BUILD_IN_WIP_FLAG,'Y',1,2) BUILD_IN_WIP_FLAG, decode(esib.PURCHASING_ENABLED_FLAG,'Y',1,2) PURCHASING_ENABLED_FLAG, NVL(esib.PLANNING_MAKE_BUY_CODE, 1) PLANNING_MAKE_BUY_CODE, 0 STANDARD_COST, esib.CARRYING_COST, esib.ORDER_COST, null MATERIAL_COST, null RESOURCE_COST, esib.LIST_PRICE_PER_UNIT, null AVERAGE_DISCOUNT, esib.WIP_SUPPLY_TYPE, TO_NUMBER(DECODE(esib.SAFETY_STOCK_PLANNING_METHOD, 'DAYS_OF_COVER','2', 'NON_MRP_PLANNED', '1', '3')) SAFETY_STOCK_CODE, esib.DAYS_OF_COVER SAFETY_STOCK_PERCENT, esib.DEMAND_PERIOD SAFETY_STOCK_BUCKET_DAYS, esib.PLANNER_CODE, esib.BOM_ITEM_TYPE, esib.ATO_FORECAST_CONTROL, NVL(esib.EFFECTIVITY_CONTROL, 1) EFFECTIVITY_CONTROL, esib.ACCEPTABLE_EARLY_DAYS ACCEPTABLE_EARLY_DELIVERY, DECODE( esib.bom_item_type,1, 1,2, 1,DECODE( esib.mrp_calculate_atp_flag, 'Y', 1, 2)) CALCULATE_ATP, decode(esib.INVENTORY_ITEM_FLAG,'Y',1,2) INVENTORY_ITEM_FLAG, esib.SOURCE_ORGANIZATION_ID, esib.BASE_ITEM_ID, decode(esib.ATP_FLAG,'Y',1,'S',9,2) ATP_FLAG, decode(esib.PICK_COMPONENTS_FLAG,'Y',1,2) PICK_COMPONENTS_FLAG, decode(esib.REPLENISH_TO_ORDER_FLAG,'Y',1,2) REPLENISH_TO_ORDER_FLAG, decode(esib.CREATE_SUPPLY_FLAG,'N',2,1) CREATE_SUPPLY_FLAG, esib.VMI_FIXED_ORDER_QUANTITY, esib.CURRENT_PHASE_CODE LIFE_CYCLE_PHASE, decode(esib.SHIPPABLE_ITEM_FLAG,'Y',1,2) SHIPPABLE_ITEM_FLAG, decode(esib.CUSTOMER_ORDER_FLAG,'Y',1,2) CUSTOMER_ORDER_FLAG, decode(esib.SHIP_MODEL_COMPLETE_FLAG,'Y',1,2) SHIP_MODEL_COMPLETE_FLAG, decode(esib.BACK_TO_BACK_ENABLED,'Y',1,2) BACK_TO_BACK_FLAG, decode(esib.ENGINEERING_ITEM_ID,'Y',1,2) ENGINEERING_ITEM_FLAG, esib.SHELF_LIFE_DAYS MIN_SHELF_LIFE_DAYS, decode (esib.AUTO_CREATED_CONFIG_FLAG,'Y',1,2) AUTO_CREATED_CONFIG_FLAG, decode (esib.OUTSIDE_PROCESS_SERVICE_FLAG,'Y',1,2) OUTSIDE_PROCESS_SERVICE_FLAG, PersonNameDPEO.list_name buyer_name, esib.ATTRIBUTE1 , esib.ATTRIBUTE2 , esib.ATTRIBUTE3 , esib.ATTRIBUTE4 , esib.ATTRIBUTE5 , esib.ATTRIBUTE6 , esib.ATTRIBUTE7 , esib.ATTRIBUTE8 , esib.ATTRIBUTE9 , esib.ATTRIBUTE10 , esib.ATTRIBUTE11 , esib.ATTRIBUTE12 , esib.ATTRIBUTE13 , esib.ATTRIBUTE14 , esib.ATTRIBUTE15 , esib.ATTRIBUTE16 , esib.ATTRIBUTE17 , esib.ATTRIBUTE18 , esib.ATTRIBUTE19 , esib.ATTRIBUTE20 , esib.ATTRIBUTE21 , esib.ATTRIBUTE22 , esib.ATTRIBUTE23 , esib.ATTRIBUTE24 , esib.ATTRIBUTE25 , esib.ATTRIBUTE26 , esib.ATTRIBUTE27 , esib.ATTRIBUTE28 , esib.ATTRIBUTE29 , esib.ATTRIBUTE30 , esib.ATTRIBUTE_DATE1 , esib.ATTRIBUTE_DATE2 , esib.ATTRIBUTE_DATE3 , esib.ATTRIBUTE_DATE4 , esib.ATTRIBUTE_DATE5 , esib.ATTRIBUTE_NUMBER1 , esib.ATTRIBUTE_NUMBER2 , esib.ATTRIBUTE_NUMBER3 , esib.ATTRIBUTE_NUMBER4 , esib.ATTRIBUTE_NUMBER5 , esib.ATTRIBUTE_NUMBER6 , esib.ATTRIBUTE_NUMBER7 , esib.ATTRIBUTE_NUMBER8 , esib.ATTRIBUTE_NUMBER9 , esib.ATTRIBUTE_NUMBER10 , esib.ATTRIBUTE_TIMESTAMP1 , esib.ATTRIBUTE_TIMESTAMP2 , esib.ATTRIBUTE_TIMESTAMP3 , esib.ATTRIBUTE_TIMESTAMP4 , esib.ATTRIBUTE_TIMESTAMP5 , esib.GLOBAL_ATTRIBUTE1 , esib.GLOBAL_ATTRIBUTE2 , esib.GLOBAL_ATTRIBUTE3 , esib.GLOBAL_ATTRIBUTE4 , esib.GLOBAL_ATTRIBUTE5 , esib.GLOBAL_ATTRIBUTE6 , esib.GLOBAL_ATTRIBUTE7 , esib.GLOBAL_ATTRIBUTE8 , esib.GLOBAL_ATTRIBUTE9 , esib.GLOBAL_ATTRIBUTE10 , esib.GLOBAL_ATTRIBUTE11 , esib.GLOBAL_ATTRIBUTE12 , esib.GLOBAL_ATTRIBUTE13 , esib.GLOBAL_ATTRIBUTE14 , esib.GLOBAL_ATTRIBUTE15 , esib.GLOBAL_ATTRIBUTE16 , esib.GLOBAL_ATTRIBUTE17 , esib.GLOBAL_ATTRIBUTE18 , esib.GLOBAL_ATTRIBUTE19 , esib.GLOBAL_ATTRIBUTE20 , esib.GLOBAL_ATTRIBUTE_DATE1 , esib.GLOBAL_ATTRIBUTE_DATE2 , esib.GLOBAL_ATTRIBUTE_DATE3 , esib.GLOBAL_ATTRIBUTE_DATE4 , esib.GLOBAL_ATTRIBUTE_DATE5 , esib.GLOBAL_ATTRIBUTE_NUMBER1 , esib.GLOBAL_ATTRIBUTE_NUMBER2 , esib.GLOBAL_ATTRIBUTE_NUMBER3 , esib.GLOBAL_ATTRIBUTE_NUMBER4 , esib.GLOBAL_ATTRIBUTE_NUMBER5 , DECODE(esib.indivisible_flag, 'Y', 1, 'N', 2) INDIVISIBLE_FLAG, DECODE(esib.FORCE_PURCHASE_LEAD_TIME_FLAG, 'N', 2, 1) ENFORCE_PURCHASE_LT, DECODE(esib.HARD_PEGGING_LEVEL, 'PROJECT_AND_TASK', 1, 'PROJECT', 2, 'PROJECT_GROUP', 3, 4) HARD_PEGGING_LEVEL, TO_NUMBER(DECODE(esib.COMN_SUPPLY_PRJ_DEMAND_FLAG, 'Y', 1, 'N', 2, NULL) ) COMN_SUPPLY_PRJ_DEMAND_FLAG, esib.lead_time_lot_size, DECODE(esib.ASSET_TRACKED_FLAG, 'Y', 1, 'C', 1,'N', 2, 2) ASSET_TRACKED_FLAG, esib.create_supply_after_date, DECODE(esib.SERIAL_NUMBER_CONTROL_CODE,1,2,NULL,2,1) SERIAL_CONTROL_ENABLED_FLAG, TO_NUMBER(DECODE(esib.ORDER_MODIFIER_START_QTY_FLAG, 'Y', 1, 'N', 2, NULL) ) ORDER_MODIFIER_TO_START_QTY FROM EGP_SYSTEM_ITEMS_VL esib, PO_AGENT_ACCESSES BuyerPEO, PER_PERSON_NAMES_F_V PersonNameDPEO WHERE BuyerPEO.assignment_id(+) = esib.buyer_id AND BuyerPEO.ACCESS_ACTION_CODE(+) = 'MANAGE_REQUISITIONS' AND (BuyerPEO.AGENT_ID = PersonNameDPEO.PERSON_ID(+) AND trunc(sysdate) BETWEEN PersonNameDPEO.EFFECTIVE_START_DATE(+) AND PersonNameDPEO.EFFECTIVE_END_DATE(+)) AND esib.BOM_ITEM_TYPE NOT IN (3, 5) AND ( ( ( (esib.mrp_planning_code IS NOT NULL AND esib.mrp_planning_code NOT IN (6,7,8,9)) OR (esib.PICK_COMPONENTS_FLAG = 'Y' OR esib.BOM_ITEM_TYPE = 3) ) AND esib.inventory_item_flag = 'Y' AND esib.planning_make_buy_code IN (1,2) AND esib.primary_uom_code IS NOT NULL ) OR esib.ATP_FLAG NOT IN ('N','S') OR esib.ATP_COMPONENTS_FLAG <> 'N' OR esib.OUTSIDE_PROCESS_SERVICE_FLAG = 'Y' )