1. Tables and Views for SCM
  2. MSC_AP_EGP_SYSTEM_ITEMS_V

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

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

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'

)