EGP_SYSTEM_ITEMS_B: If you have implemented inventory module in Oracle Fusion Finance then one of the most important table you’ll need to query is EGP_SYSTEM_ITEMS_B. EGP_SYSTEM_ITEMS_B is the master table for items. In this post we’ll describe 2 aspects of EGP_SYSTEM_ITEMS_B:
1. Table Columns
2. Indexes
But before that a few points which might help you:
As you know EGP_SYSTEM_ITEMS_B table is item master table, so all the items defined in the system will have an entry in this table. But then, items are defined at inventory org level, so it translates into 1 entry of each item for each inventory it is defined in. Or in other words, combination of item number and Organization id will give you a unique row in EGP_SYSTEM_ITEMS_B table.
Looking to query important fields of item? Click here to see the query.
Also most probably you’ll have to query the item for assigned catalog and category. And to get the query, you can refer to Item Catalog Category Relationship.
Also one one the important aspect of the item is calculating reserved and on hand quantity, which you are bound to calculate at some point of time. Refer this post to get the query.
One of the related tables is egp_systems_items_tl which is used to query the description and long description of the item present in egp_system_items_b table.
Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports. Try them only when nothing else works.. kind of SOS...
EGP_SYSTEM_ITEMS_B Table Columns:
| COLUMN NAME | DATA TYPE | DATA LENGTH | NULLABLE |
| INVENTORY_ITEM_ID | NUMBER | 22 | N |
| ORGANIZATION_ID | NUMBER | 22 | N |
| LAST_UPDATE_DATE | TIMESTAMP(6) | 11 | N |
| CREATION_DATE | TIMESTAMP(6) | 11 | N |
| LAST_UPDATE_LOGIN | VARCHAR2 | 128 | Y |
| SUMMARY_FLAG | VARCHAR2 | 4 | Y |
| ENABLED_FLAG | VARCHAR2 | 4 | Y |
| START_DATE_ACTIVE | DATE | 7 | Y |
| END_DATE_ACTIVE | DATE | 7 | Y |
| BUYER_ID | NUMBER | 22 | Y |
| ACCOUNTING_RULE_ID | NUMBER | 22 | Y |
| INVOICING_RULE_ID | NUMBER | 22 | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2 | 120 | Y |
| ATTRIBUTE1 | VARCHAR2 | 960 | Y |
| ATTRIBUTE2 | VARCHAR2 | 960 | Y |
| ATTRIBUTE3 | VARCHAR2 | 960 | Y |
| ATTRIBUTE4 | VARCHAR2 | 960 | Y |
| ATTRIBUTE5 | VARCHAR2 | 960 | Y |
| ATTRIBUTE6 | VARCHAR2 | 960 | Y |
| ATTRIBUTE7 | VARCHAR2 | 960 | Y |
| ATTRIBUTE8 | VARCHAR2 | 960 | Y |
| ATTRIBUTE9 | VARCHAR2 | 960 | Y |
| ATTRIBUTE10 | VARCHAR2 | 960 | Y |
| ATTRIBUTE11 | VARCHAR2 | 960 | Y |
| ATTRIBUTE12 | VARCHAR2 | 960 | Y |
| ATTRIBUTE13 | VARCHAR2 | 960 | Y |
| ATTRIBUTE14 | VARCHAR2 | 960 | Y |
| ATTRIBUTE15 | VARCHAR2 | 960 | Y |
| PURCHASING_ITEM_FLAG | VARCHAR2 | 4 | Y |
| SHIPPABLE_ITEM_FLAG | VARCHAR2 | 4 | Y |
| CUSTOMER_ORDER_FLAG | VARCHAR2 | 4 | Y |
| INTERNAL_ORDER_FLAG | VARCHAR2 | 4 | Y |
| INVENTORY_ITEM_FLAG | VARCHAR2 | 4 | Y |
| INVENTORY_ASSET_FLAG | VARCHAR2 | 4 | Y |
| PURCHASING_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| CUSTOMER_ORDER_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| INTERNAL_ORDER_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| SO_TRANSACTIONS_FLAG | VARCHAR2 | 4 | Y |
| MTL_TRANSACTIONS_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| STOCK_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| BUILD_IN_WIP_FLAG | VARCHAR2 | 4 | Y |
| REVISION_QTY_CONTROL_CODE | NUMBER | 22 | Y |
| ITEM_CATALOG_GROUP_ID | NUMBER | 22 | Y |
| RETURNABLE_FLAG | VARCHAR2 | 4 | Y |
| DEFAULT_SHIPPING_ORG | NUMBER | 22 | Y |
| COLLATERAL_FLAG | VARCHAR2 | 4 | Y |
| TAXABLE_FLAG | VARCHAR2 | 4 | Y |
| QTY_RCV_EXCEPTION_CODE | VARCHAR2 | 100 | Y |
| ALLOW_ITEM_DESC_UPDATE_FLAG | VARCHAR2 | 4 | Y |
| MARKET_PRICE | NUMBER | 22 | Y |
| HAZARD_CLASS_ID | NUMBER | 22 | Y |
| NEGOTIATION_REQUIRED_FLAG | VARCHAR2 | 4 | Y |
| QTY_RCV_TOLERANCE | NUMBER | 22 | Y |
| LIST_PRICE_PER_UNIT | NUMBER | 22 | Y |
| UN_NUMBER_ID | NUMBER | 22 | Y |
| PRICE_TOLERANCE_PERCENT | NUMBER | 22 | Y |
| ROUNDING_FACTOR | NUMBER | 22 | Y |
| UNIT_OF_ISSUE | VARCHAR2 | 100 | Y |
| ENFORCE_SHIP_TO_LOCATION_CODE | VARCHAR2 | 100 | Y |
| ALLOW_SUBSTITUTE_RECEIPTS_FLAG | VARCHAR2 | 4 | Y |
| ALLOW_UNORDERED_RECEIPTS_FLAG | VARCHAR2 | 4 | Y |
| ALLOW_EXPRESS_DELIVERY_FLAG | VARCHAR2 | 4 | Y |
| DAYS_EARLY_RECEIPT_ALLOWED | NUMBER | 22 | Y |
| DAYS_LATE_RECEIPT_ALLOWED | NUMBER | 22 | Y |
| RECEIPT_DAYS_EXCEPTION_CODE | VARCHAR2 | 100 | Y |
| RECEIVING_ROUTING_ID | NUMBER | 22 | Y |
| INVOICE_CLOSE_TOLERANCE | NUMBER | 22 | Y |
| RECEIVE_CLOSE_TOLERANCE | NUMBER | 22 | Y |
| AUTO_LOT_ALPHA_PREFIX | VARCHAR2 | 320 | Y |
| START_AUTO_LOT_NUMBER | VARCHAR2 | 320 | Y |
| LOT_CONTROL_CODE | NUMBER | 22 | Y |
| SHELF_LIFE_CODE | NUMBER | 22 | Y |
| SHELF_LIFE_DAYS | NUMBER | 22 | Y |
| SERIAL_NUMBER_CONTROL_CODE | NUMBER | 22 | Y |
| START_AUTO_SERIAL_NUMBER | VARCHAR2 | 320 | Y |
| AUTO_SERIAL_ALPHA_PREFIX | VARCHAR2 | 320 | Y |
| SOURCE_TYPE | NUMBER | 22 | Y |
| SOURCE_ORGANIZATION_ID | NUMBER | 22 | Y |
| SOURCE_SUBINVENTORY | VARCHAR2 | 40 | Y |
| RESTRICT_SUBINVENTORIES_CODE | NUMBER | 22 | Y |
| UNIT_WEIGHT | NUMBER | 22 | Y |
| WEIGHT_UOM_CODE | VARCHAR2 | 12 | Y |
| VOLUME_UOM_CODE | VARCHAR2 | 12 | Y |
| UNIT_VOLUME | NUMBER | 22 | Y |
| RESTRICT_LOCATORS_CODE | NUMBER | 22 | Y |
| LOCATION_CONTROL_CODE | NUMBER | 22 | Y |
| SHRINKAGE_RATE | NUMBER | 22 | Y |
| ACCEPTABLE_EARLY_DAYS | NUMBER | 22 | Y |
| PLANNING_TIME_FENCE_CODE | NUMBER | 22 | Y |
| DEMAND_TIME_FENCE_CODE | NUMBER | 22 | Y |
| LEAD_TIME_LOT_SIZE | NUMBER | 22 | Y |
| STD_LOT_SIZE | NUMBER | 22 | Y |
| CUM_MANUFACTURING_LEAD_TIME | NUMBER | 22 | Y |
| OVERRUN_PERCENTAGE | NUMBER | 22 | Y |
| MRP_CALCULATE_ATP_FLAG | VARCHAR2 | 4 | Y |
| ACCEPTABLE_RATE_INCREASE | NUMBER | 22 | Y |
| ACCEPTABLE_RATE_DECREASE | NUMBER | 22 | Y |
| CUMULATIVE_TOTAL_LEAD_TIME | NUMBER | 22 | Y |
| PLANNING_TIME_FENCE_DAYS | NUMBER | 22 | Y |
| DEMAND_TIME_FENCE_DAYS | NUMBER | 22 | Y |
| END_ASSEMBLY_PEGGING_FLAG | VARCHAR2 | 4 | Y |
| REPETITIVE_PLANNING_FLAG | VARCHAR2 | 4 | Y |
| PLANNING_EXCEPTION_SET | VARCHAR2 | 40 | Y |
| BOM_ITEM_TYPE | NUMBER | 22 | Y |
| PICK_COMPONENTS_FLAG | VARCHAR2 | 4 | Y |
| REPLENISH_TO_ORDER_FLAG | VARCHAR2 | 4 | Y |
| BASE_ITEM_ID | NUMBER | 22 | Y |
| ATP_COMPONENTS_FLAG | VARCHAR2 | 4 | Y |
| ATP_FLAG | VARCHAR2 | 4 | Y |
| FIXED_LEAD_TIME | NUMBER | 22 | Y |
| VARIABLE_LEAD_TIME | NUMBER | 22 | Y |
| WIP_SUPPLY_LOCATOR_ID | NUMBER | 22 | Y |
| WIP_SUPPLY_TYPE | NUMBER | 22 | Y |
| WIP_SUPPLY_SUBINVENTORY | VARCHAR2 | 40 | Y |
| PRIMARY_UOM_CODE | VARCHAR2 | 12 | Y |
| ALLOWED_UNITS_LOOKUP_CODE | NUMBER | 22 | Y |
| DEFAULT_INCLUDE_IN_ROLLUP_FLAG | VARCHAR2 | 4 | Y |
| INVENTORY_ITEM_STATUS_CODE | VARCHAR2 | 40 | Y |
| INVENTORY_PLANNING_CODE | NUMBER | 22 | Y |
| PLANNER_CODE | VARCHAR2 | 40 | Y |
| PLANNING_MAKE_BUY_CODE | NUMBER | 22 | Y |
| FIXED_LOT_MULTIPLIER | NUMBER | 22 | Y |
| ROUNDING_CONTROL_TYPE | NUMBER | 22 | Y |
| CARRYING_COST | NUMBER | 22 | Y |
| POSTPROCESSING_LEAD_TIME | NUMBER | 22 | Y |
| PREPROCESSING_LEAD_TIME | NUMBER | 22 | Y |
| FULL_LEAD_TIME | NUMBER | 22 | Y |
| ORDER_COST | NUMBER | 22 | Y |
| MRP_SAFETY_STOCK_PERCENT | NUMBER | 22 | Y |
| MRP_SAFETY_STOCK_CODE | NUMBER | 22 | Y |
| MIN_MINMAX_QUANTITY | NUMBER | 22 | Y |
| MAX_MINMAX_QUANTITY | NUMBER | 22 | Y |
| MINIMUM_ORDER_QUANTITY | NUMBER | 22 | Y |
| FIXED_ORDER_QUANTITY | NUMBER | 22 | Y |
| FIXED_DAYS_SUPPLY | NUMBER | 22 | Y |
| MAXIMUM_ORDER_QUANTITY | NUMBER | 22 | Y |
| ATP_RULE_ID | NUMBER | 22 | Y |
| PICKING_RULE_ID | NUMBER | 22 | Y |
| RESERVABLE_TYPE | NUMBER | 22 | Y |
| POSITIVE_MEASUREMENT_ERROR | NUMBER | 22 | Y |
| NEGATIVE_MEASUREMENT_ERROR | NUMBER | 22 | Y |
| ENGINEERING_ITEM_ID | NUMBER | 22 | Y |
| SERVICE_STARTING_DELAY | NUMBER | 22 | Y |
| SERVICEABLE_PRODUCT_FLAG | VARCHAR2 | 4 | Y |
| PAYMENT_TERMS_ID | NUMBER | 22 | Y |
| MATERIAL_BILLABLE_FLAG | VARCHAR2 | 120 | Y |
| COVERAGE_SCHEDULE_ID | NUMBER | 22 | Y |
| SERVICE_DURATION_PERIOD_CODE | VARCHAR2 | 40 | Y |
| SERVICE_DURATION | NUMBER | 22 | Y |
| INVOICEABLE_ITEM_FLAG | VARCHAR2 | 4 | Y |
| TAX_CODE | VARCHAR2 | 200 | Y |
| INVOICE_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| MUST_USE_APPROVED_VENDOR_FLAG | VARCHAR2 | 4 | Y |
| REQUEST_ID | NUMBER | 22 | Y |
| OUTSIDE_OPERATION_FLAG | VARCHAR2 | 4 | Y |
| OUTSIDE_OPERATION_UOM_TYPE | VARCHAR2 | 100 | Y |
| SAFETY_STOCK_BUCKET_DAYS | NUMBER | 22 | Y |
| AUTO_REDUCE_MPS | NUMBER | 22 | Y |
| COSTING_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| AUTO_CREATED_CONFIG_FLAG | VARCHAR2 | 4 | Y |
| CYCLE_COUNT_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| ITEM_TYPE | VARCHAR2 | 120 | Y |
| SHIP_MODEL_COMPLETE_FLAG | VARCHAR2 | 4 | Y |
| MRP_PLANNING_CODE | NUMBER | 22 | Y |
| RETURN_INSPECTION_REQUIREMENT | NUMBER | 22 | Y |
| ATO_FORECAST_CONTROL | NUMBER | 22 | Y |
| RELEASE_TIME_FENCE_CODE | NUMBER | 22 | Y |
| RELEASE_TIME_FENCE_DAYS | NUMBER | 22 | Y |
| CONTAINER_ITEM_FLAG | VARCHAR2 | 4 | Y |
| VEHICLE_ITEM_FLAG | VARCHAR2 | 4 | Y |
| MAXIMUM_LOAD_WEIGHT | NUMBER | 22 | Y |
| MINIMUM_FILL_PERCENT | NUMBER | 22 | Y |
| CONTAINER_TYPE_CODE | VARCHAR2 | 120 | Y |
| INTERNAL_VOLUME | NUMBER | 22 | Y |
| WH_UPDATE_DATE | DATE | 7 | Y |
| PRODUCT_FAMILY_ITEM_ID | NUMBER | 22 | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE1 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE2 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE3 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE4 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE5 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE6 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE7 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE8 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE9 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE10 | VARCHAR2 | 600 | Y |
| PURCHASING_TAX_CODE | VARCHAR2 | 200 | Y |
| OVERCOMPLETION_TOLERANCE_TYPE | NUMBER | 22 | Y |
| OVERCOMPLETION_TOLERANCE_VALUE | NUMBER | 22 | Y |
| EFFECTIVITY_CONTROL | NUMBER | 22 | Y |
| CHECK_SHORTAGES_FLAG | VARCHAR2 | 4 | Y |
| OVER_SHIPMENT_TOLERANCE | NUMBER | 22 | Y |
| UNDER_SHIPMENT_TOLERANCE | NUMBER | 22 | Y |
| OVER_RETURN_TOLERANCE | NUMBER | 22 | Y |
| UNDER_RETURN_TOLERANCE | NUMBER | 22 | Y |
| EQUIPMENT_TYPE | NUMBER | 22 | Y |
| RECOVERED_PART_DISP_CODE | VARCHAR2 | 120 | Y |
| DEFECT_TRACKING_ON_FLAG | VARCHAR2 | 4 | Y |
| EVENT_FLAG | VARCHAR2 | 4 | Y |
| ELECTRONIC_FLAG | VARCHAR2 | 4 | Y |
| DOWNLOADABLE_FLAG | VARCHAR2 | 4 | Y |
| COMMS_NL_TRACKABLE_FLAG | VARCHAR2 | 4 | Y |
| ASSET_CREATION_CODE | VARCHAR2 | 120 | Y |
| COMMS_ACTIVATION_REQD_FLAG | VARCHAR2 | 4 | Y |
| ORDERABLE_ON_WEB_FLAG | VARCHAR2 | 4 | Y |
| BACK_ORDERABLE_FLAG | VARCHAR2 | 4 | Y |
| WEB_STATUS | VARCHAR2 | 120 | Y |
| INDIVISIBLE_FLAG | VARCHAR2 | 4 | Y |
| DIMENSION_UOM_CODE | VARCHAR2 | 12 | Y |
| UNIT_LENGTH | NUMBER | 22 | Y |
| UNIT_WIDTH | NUMBER | 22 | Y |
| UNIT_HEIGHT | NUMBER | 22 | Y |
| BULK_PICKED_FLAG | VARCHAR2 | 4 | Y |
| LOT_STATUS_ENABLED | VARCHAR2 | 4 | Y |
| DEFAULT_LOT_STATUS_ID | NUMBER | 22 | Y |
| SERIAL_STATUS_ENABLED | VARCHAR2 | 4 | Y |
| DEFAULT_SERIAL_STATUS_ID | NUMBER | 22 | Y |
| LOT_SPLIT_ENABLED | VARCHAR2 | 4 | Y |
| LOT_MERGE_ENABLED | VARCHAR2 | 4 | Y |
| INVENTORY_CARRY_PENALTY | NUMBER | 22 | Y |
| OPERATION_SLACK_PENALTY | NUMBER | 22 | Y |
| FINANCING_ALLOWED_FLAG | VARCHAR2 | 4 | Y |
| EAM_ITEM_TYPE | NUMBER | 22 | Y |
| EAM_ACTIVITY_TYPE_CODE | VARCHAR2 | 120 | Y |
| EAM_ACTIVITY_CAUSE_CODE | VARCHAR2 | 120 | Y |
| EAM_ACT_NOTIFICATION_FLAG | VARCHAR2 | 4 | Y |
| EAM_ACT_SHUTDOWN_STATUS | VARCHAR2 | 120 | Y |
| DUAL_UOM_CONTROL | NUMBER | 22 | Y |
| SECONDARY_UOM_CODE | VARCHAR2 | 12 | Y |
| DUAL_UOM_DEVIATION_HIGH | NUMBER | 22 | Y |
| DUAL_UOM_DEVIATION_LOW | NUMBER | 22 | Y |
| CONTRACT_ITEM_TYPE_CODE | VARCHAR2 | 120 | Y |
| SERV_REQ_ENABLED_CODE | VARCHAR2 | 120 | Y |
| SERV_BILLING_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| PLANNED_INV_POINT_FLAG | VARCHAR2 | 4 | Y |
| LOT_TRANSLATE_ENABLED | VARCHAR2 | 4 | Y |
| DEFAULT_SO_SOURCE_TYPE | VARCHAR2 | 120 | Y |
| CREATE_SUPPLY_FLAG | VARCHAR2 | 4 | Y |
| SUBSTITUTION_WINDOW_CODE | NUMBER | 22 | Y |
| SUBSTITUTION_WINDOW_DAYS | NUMBER | 22 | Y |
| IB_ITEM_INSTANCE_CLASS | VARCHAR2 | 120 | Y |
| CONFIG_MODEL_TYPE | VARCHAR2 | 120 | Y |
| LOT_SUBSTITUTION_ENABLED | VARCHAR2 | 4 | Y |
| MINIMUM_LICENSE_QUANTITY | NUMBER | 22 | Y |
| EAM_ACTIVITY_SOURCE_CODE | VARCHAR2 | 120 | Y |
| CURRENT_PHASE_ID | NUMBER | 22 | Y |
| OBJECT_VERSION_NUMBER | NUMBER | 22 | N |
| TRACKING_QUANTITY_IND | VARCHAR2 | 120 | Y |
| ONT_PRICING_QTY_SOURCE | VARCHAR2 | 120 | Y |
| SECONDARY_DEFAULT_IND | VARCHAR2 | 120 | Y |
| OPTION_SPECIFIC_SOURCED | NUMBER | 22 | Y |
| APPROVAL_STATUS | VARCHAR2 | 120 | Y |
| VMI_MINIMUM_UNITS | NUMBER | 22 | Y |
| VMI_MINIMUM_DAYS | NUMBER | 22 | Y |
| VMI_MAXIMUM_UNITS | NUMBER | 22 | Y |
| VMI_MAXIMUM_DAYS | NUMBER | 22 | Y |
| VMI_FIXED_ORDER_QUANTITY | NUMBER | 22 | Y |
| SO_AUTHORIZATION_FLAG | NUMBER | 22 | Y |
| CONSIGNED_FLAG | NUMBER | 22 | Y |
| ASN_AUTOEXPIRE_FLAG | NUMBER | 22 | Y |
| VMI_FORECAST_TYPE | NUMBER | 22 | Y |
| FORECAST_HORIZON | NUMBER | 22 | Y |
| EXCLUDE_FROM_BUDGET_FLAG | NUMBER | 22 | Y |
| DAYS_TGT_INV_SUPPLY | NUMBER | 22 | Y |
| DAYS_TGT_INV_WINDOW | NUMBER | 22 | Y |
| DAYS_MAX_INV_SUPPLY | NUMBER | 22 | Y |
| DAYS_MAX_INV_WINDOW | NUMBER | 22 | Y |
| DRP_PLANNED_FLAG | NUMBER | 22 | Y |
| CRITICAL_COMPONENT_FLAG | NUMBER | 22 | Y |
| CONTINOUS_TRANSFER | NUMBER | 22 | Y |
| CONVERGENCE | NUMBER | 22 | Y |
| DIVERGENCE | NUMBER | 22 | Y |
| CONFIG_ORGS | VARCHAR2 | 120 | Y |
| CONFIG_MATCH | VARCHAR2 | 120 | Y |
| ATTRIBUTE16 | VARCHAR2 | 960 | Y |
| ATTRIBUTE17 | VARCHAR2 | 960 | Y |
| ATTRIBUTE18 | VARCHAR2 | 960 | Y |
| ATTRIBUTE19 | VARCHAR2 | 960 | Y |
| ATTRIBUTE20 | VARCHAR2 | 960 | Y |
| ATTRIBUTE21 | VARCHAR2 | 960 | Y |
| ATTRIBUTE22 | VARCHAR2 | 960 | Y |
| ATTRIBUTE23 | VARCHAR2 | 960 | Y |
| ATTRIBUTE24 | VARCHAR2 | 960 | Y |
| ATTRIBUTE25 | VARCHAR2 | 960 | Y |
| ATTRIBUTE26 | VARCHAR2 | 960 | Y |
| ATTRIBUTE27 | VARCHAR2 | 960 | Y |
| ATTRIBUTE28 | VARCHAR2 | 960 | Y |
| ATTRIBUTE29 | VARCHAR2 | 960 | Y |
| ATTRIBUTE30 | VARCHAR2 | 960 | Y |
| CAS_NUMBER | VARCHAR2 | 120 | Y |
| CHILD_LOT_FLAG | VARCHAR2 | 4 | Y |
| CHILD_LOT_PREFIX | VARCHAR2 | 120 | Y |
| CHILD_LOT_STARTING_NUMBER | NUMBER | 22 | Y |
| CHILD_LOT_VALIDATION_FLAG | VARCHAR2 | 4 | Y |
| COPY_LOT_ATTRIBUTE_FLAG | VARCHAR2 | 4 | Y |
| DEFAULT_GRADE | VARCHAR2 | 600 | Y |
| EXPIRATION_ACTION_CODE | VARCHAR2 | 128 | Y |
| EXPIRATION_ACTION_INTERVAL | NUMBER | 22 | Y |
| GRADE_CONTROL_FLAG | VARCHAR2 | 4 | Y |
| HAZARDOUS_MATERIAL_FLAG | VARCHAR2 | 4 | Y |
| HOLD_DAYS | NUMBER | 22 | Y |
| LOT_DIVISIBLE_FLAG | VARCHAR2 | 4 | Y |
| MATURITY_DAYS | NUMBER | 22 | Y |
| PARENT_CHILD_GENERATION_FLAG | VARCHAR2 | 4 | Y |
| PROCESS_COSTING_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| PROCESS_EXECUTION_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| PROCESS_QUALITY_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| PROCESS_SUPPLY_LOCATOR_ID | NUMBER | 22 | Y |
| PROCESS_SUPPLY_SUBINVENTORY | VARCHAR2 | 40 | Y |
| PROCESS_YIELD_LOCATOR_ID | NUMBER | 22 | Y |
| PROCESS_YIELD_SUBINVENTORY | VARCHAR2 | 40 | Y |
| RECIPE_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| RETEST_INTERVAL | NUMBER | 22 | Y |
| CHARGE_PERIODICITY_CODE | VARCHAR2 | 12 | Y |
| REPAIR_LEADTIME | NUMBER | 22 | Y |
| REPAIR_YIELD | NUMBER | 22 | Y |
| PREPOSITION_POINT | VARCHAR2 | 4 | Y |
| REPAIR_PROGRAM | NUMBER | 22 | Y |
| SUBCONTRACTING_COMPONENT | NUMBER | 22 | Y |
| OUTSOURCED_ASSEMBLY | NUMBER | 22 | Y |
| EGO_MASTER_ITEMS_DFF_CTX | VARCHAR2 | 4 | Y |
| GDSN_OUTBOUND_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| TRADE_ITEM_DESCRIPTOR | VARCHAR2 | 140 | Y |
| ASSET_TRACKED_FLAG | VARCHAR2 | 4 | Y |
| ASSET_CLASS | VARCHAR2 | 120 | Y |
| SERVICE_DURATION_TYPE_CODE | VARCHAR2 | 12 | Y |
| SERVICE_START_TYPE_CODE | VARCHAR2 | 12 | Y |
| START_UPON_MILESTONE_CODE | VARCHAR2 | 12 | Y |
| SERVICE_START_DELAY | NUMBER | 22 | Y |
| ALLOW_SUSPEND_FLAG | VARCHAR2 | 4 | Y |
| ALLOW_TERMINATE_FLAG | VARCHAR2 | 4 | Y |
| REQUIRES_FULFILLMENT_LOC_FLAG | VARCHAR2 | 4 | Y |
| REQUIRES_ITM_ASSOCIATION_FLAG | VARCHAR2 | 4 | Y |
| STYLE_ITEM_ID | NUMBER | 22 | Y |
| STYLE_ITEM_FLAG | VARCHAR2 | 4 | Y |
| LAST_SUBMITTED_NIR_ID | NUMBER | 22 | Y |
| PROGRAM_NAME | VARCHAR2 | 120 | Y |
| PROGRAM_APP_NAME | VARCHAR2 | 200 | Y |
| ELIGIBILITY_COMPATIBILITY_RULE | VARCHAR2 | 4 | Y |
| JOB_DEFINITION_NAME | VARCHAR2 | 400 | Y |
| JOB_DEFINITION_PACKAGE | VARCHAR2 | 3600 | Y |
| ACD_TYPE | VARCHAR2 | 120 | N |
| CHANGE_LINE_ID | NUMBER | 22 | N |
| VERSION_ID | NUMBER | 22 | N |
| MASTER_ITEM_VERSION_ID | NUMBER | 22 | Y |
| VERSION_START_DATE | DATE | 7 | N |
| VERSION_END_DATE | DATE | 7 | Y |
| IMPLEMENTATION_DATE | DATE | 7 | Y |
| TEMPLATE_ITEM_FLAG | VARCHAR2 | 4 | Y |
| QSC_SALES_PRODUCT_TYPE | VARCHAR2 | 80 | Y |
| MATCH_APPROVAL_LEVEL | NUMBER | 22 | Y |
| INVOICE_MATCH_OPTION | NUMBER | 22 | Y |
| DEFAULT_TEMPLATE_FLAG | VARCHAR2 | 4 | Y |
| INSPECTION_REQUIRED_FLAG | VARCHAR2 | 4 | Y |
| RECEIPT_REQUIRED_FLAG | VARCHAR2 | 4 | Y |
| ASSET_CATEGORY_ID | NUMBER | 22 | Y |
| EXPENSE_ACCOUNT | NUMBER | 22 | Y |
| SALES_ACCOUNT | NUMBER | 22 | Y |
| SALES_PRODUCT_TYPE | VARCHAR2 | 120 | Y |
| SALES_PRODUCT_SUB_TYPE | VARCHAR2 | 12 | Y |
| ALT_ITEM_CODE | VARCHAR2 | 160 | Y |
| CHANGE_BIT_MAP | VARCHAR2 | 1600 | N |
| CURRENT_PHASE_CODE | VARCHAR2 | 480 | Y |
| INVENTORY_ORGANIZATION_ID | NUMBER | 22 | Y |
| ITEM_NUMBER | VARCHAR2 | 1200 | Y |
| CREATED_BY | VARCHAR2 | 256 | N |
| LAST_UPDATED_BY | VARCHAR2 | 256 | N |
| DQ_SEMANTIC_KEY | VARCHAR2 | 4000 | Y |
| DQ_REQUIRED_FLAG | VARCHAR2 | 4 | Y |
| THREAD_ID | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER1 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER2 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER3 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER4 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER5 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER6 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER7 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER8 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER9 | NUMBER | 22 | Y |
| ATTRIBUTE_NUMBER10 | NUMBER | 22 | Y |
| ATTRIBUTE_DATE1 | DATE | 7 | Y |
| ATTRIBUTE_DATE2 | DATE | 7 | Y |
| ATTRIBUTE_DATE3 | DATE | 7 | Y |
| ATTRIBUTE_DATE4 | DATE | 7 | Y |
| ATTRIBUTE_DATE5 | DATE | 7 | Y |
| ATTRIBUTE_TIMESTAMP1 | TIMESTAMP(6) | 11 | Y |
| ATTRIBUTE_TIMESTAMP2 | TIMESTAMP(6) | 11 | Y |
| ATTRIBUTE_TIMESTAMP3 | TIMESTAMP(6) | 11 | Y |
| ATTRIBUTE_TIMESTAMP4 | TIMESTAMP(6) | 11 | Y |
| ATTRIBUTE_TIMESTAMP5 | TIMESTAMP(6) | 11 | Y |
| GLOBAL_ATTRIBUTE11 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE12 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE13 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE14 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE15 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE16 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE17 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE18 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE19 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE20 | VARCHAR2 | 600 | Y |
| GLOBAL_ATTRIBUTE_NUMBER1 | NUMBER | 22 | Y |
| GLOBAL_ATTRIBUTE_NUMBER2 | NUMBER | 22 | Y |
| GLOBAL_ATTRIBUTE_NUMBER3 | NUMBER | 22 | Y |
| GLOBAL_ATTRIBUTE_NUMBER4 | NUMBER | 22 | Y |
| GLOBAL_ATTRIBUTE_NUMBER5 | NUMBER | 22 | Y |
| GLOBAL_ATTRIBUTE_DATE1 | DATE | 7 | Y |
| GLOBAL_ATTRIBUTE_DATE2 | DATE | 7 | Y |
| GLOBAL_ATTRIBUTE_DATE3 | DATE | 7 | Y |
| GLOBAL_ATTRIBUTE_DATE4 | DATE | 7 | Y |
| GLOBAL_ATTRIBUTE_DATE5 | DATE | 7 | Y |
| BACK_TO_BACK_ENABLED | VARCHAR2 | 4 | Y |
| CONTRACT_MANUFACTURING | VARCHAR2 | 4 | Y |
| SAFETY_STOCK_PLANNING_METHOD | VARCHAR2 | 120 | Y |
| DEMAND_PERIOD | NUMBER | 22 | Y |
| DAYS_OF_COVER | NUMBER | 22 | Y |
| MASTER_ORG_ID | NUMBER | 22 | Y |
| OUTSIDE_PROCESS_SERVICE_FLAG | VARCHAR2 | 4 | Y |
| ALLOW_MAINTENANCE_ASSET_FLAG | VARCHAR2 | 4 | Y |
| ENABLE_GENEALOGY_TRACKING_FLAG | VARCHAR2 | 4 | Y |
| COMPLETENESS_SCORE | NUMBER | 22 | Y |
| CSS_ENABLED_FLAG | VARCHAR2 | 4 | Y |
| ENGINEERED_ITEM_FLAG | VARCHAR2 | 4 | Y |
| FORCE_PURCHASE_LEAD_TIME_FLAG | VARCHAR2 | 4 | Y |
| REPLACEMENT_TYPE | VARCHAR2 | 120 | Y |
| HARD_PEGGING_LEVEL | VARCHAR2 | 100 | Y |
| COMN_SUPPLY_PRJ_DEMAND_FLAG | VARCHAR2 | 4 | Y |
| ENABLE_IOT_FLAG | VARCHAR2 | 4 | Y |
| EXPENDITURE_TYPE_ID | NUMBER | 22 | Y |
EGP_SYSTEM_ITEMS_B Indexes
| INDEX NAME | INDEX TYPE | UNIQUENESS | COLUMN NAME |
| EGP_SYSTEM_ITEMS_B_N1 | NORMAL | NONUNIQUE | ITEM_NUMBER |
| EGP_SYSTEM_ITEMS_B_N10 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N10 | NORMAL | NONUNIQUE | PLANNER_CODE |
| EGP_SYSTEM_ITEMS_B_N11 | NORMAL | NONUNIQUE | WEB_STATUS |
| EGP_SYSTEM_ITEMS_B_N11 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N13 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N13 | NORMAL | NONUNIQUE | WIP_SUPPLY_LOCATOR_ID |
| EGP_SYSTEM_ITEMS_B_N14 | NORMAL | NONUNIQUE | BASE_ITEM_ID |
| EGP_SYSTEM_ITEMS_B_N14 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N15 | NORMAL | NONUNIQUE | STYLE_ITEM_ID |
| EGP_SYSTEM_ITEMS_B_N15 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N16 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00424$ |
| EGP_SYSTEM_ITEMS_B_N16 | FUNCTION-BASED NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N18 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N18 | NORMAL | NONUNIQUE | DQ_REQUIRED_FLAG |
| EGP_SYSTEM_ITEMS_B_N18 | NORMAL | NONUNIQUE | THREAD_ID |
| EGP_SYSTEM_ITEMS_B_N19 | NORMAL | NONUNIQUE | TEMPLATE_ITEM_FLAG |
| EGP_SYSTEM_ITEMS_B_N19 | NORMAL | NONUNIQUE | ITEM_CATALOG_GROUP_ID |
| EGP_SYSTEM_ITEMS_B_N20 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00425$ |
| EGP_SYSTEM_ITEMS_B_N20 | FUNCTION-BASED NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N21 | NORMAL | NONUNIQUE | STYLE_ITEM_FLAG |
| EGP_SYSTEM_ITEMS_B_N22 | NORMAL | NONUNIQUE | TRADE_ITEM_DESCRIPTOR |
| EGP_SYSTEM_ITEMS_B_N23 | NORMAL | NONUNIQUE | LAST_UPDATE_DATE |
| EGP_SYSTEM_ITEMS_B_N24 | FUNCTION-BASED NORMAL | NONUNIQUE | SYS_NC00432$ |
| EGP_SYSTEM_ITEMS_B_N25 | NORMAL | NONUNIQUE | CURRENT_PHASE_CODE |
| EGP_SYSTEM_ITEMS_B_N25 | NORMAL | NONUNIQUE | ITEM_CATALOG_GROUP_ID |
| EGP_SYSTEM_ITEMS_B_N26 | NORMAL | NONUNIQUE | CREATION_DATE |
| EGP_SYSTEM_ITEMS_B_N27 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N27 | NORMAL | NONUNIQUE | SALES_PRODUCT_TYPE |
| EGP_SYSTEM_ITEMS_B_N28 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N28 | NORMAL | NONUNIQUE | SERVICEABLE_PRODUCT_FLAG |
| EGP_SYSTEM_ITEMS_B_N29 | NORMAL | NONUNIQUE | INVENTORY_ITEM_ID |
| EGP_SYSTEM_ITEMS_B_N29 | NORMAL | NONUNIQUE | INVENTORY_ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N29 | NORMAL | NONUNIQUE | VERSION_ID |
| EGP_SYSTEM_ITEMS_B_N29 | NORMAL | NONUNIQUE | CHANGE_LINE_ID |
| EGP_SYSTEM_ITEMS_B_N29 | NORMAL | NONUNIQUE | ACD_TYPE |
| EGP_SYSTEM_ITEMS_B_N3 | NORMAL | NONUNIQUE | INVENTORY_ITEM_STATUS_CODE |
| EGP_SYSTEM_ITEMS_B_N30 | NORMAL | NONUNIQUE | REQUEST_ID |
| EGP_SYSTEM_ITEMS_B_N31 | NORMAL | NONUNIQUE | INVENTORY_ITEM_ID |
| EGP_SYSTEM_ITEMS_B_N31 | NORMAL | NONUNIQUE | STYLE_ITEM_ID |
| EGP_SYSTEM_ITEMS_B_N31 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N4 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N4 | NORMAL | NONUNIQUE | AUTO_CREATED_CONFIG_FLAG |
| EGP_SYSTEM_ITEMS_B_N5 | NORMAL | NONUNIQUE | WH_UPDATE_DATE |
| EGP_SYSTEM_ITEMS_B_N6 | NORMAL | NONUNIQUE | ITEM_CATALOG_GROUP_ID |
| EGP_SYSTEM_ITEMS_B_N6 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N6 | NORMAL | NONUNIQUE | INVENTORY_ITEM_ID |
| EGP_SYSTEM_ITEMS_B_N6 | NORMAL | NONUNIQUE | TEMPLATE_ITEM_FLAG |
| EGP_SYSTEM_ITEMS_B_N6 | NORMAL | NONUNIQUE | APPROVAL_STATUS |
| EGP_SYSTEM_ITEMS_B_N7 | NORMAL | NONUNIQUE | PRODUCT_FAMILY_ITEM_ID |
| EGP_SYSTEM_ITEMS_B_N7 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N8 | NORMAL | NONUNIQUE | COMMS_NL_TRACKABLE_FLAG |
| EGP_SYSTEM_ITEMS_B_N8 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N9 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_N9 | NORMAL | NONUNIQUE | BUYER_ID |
| EGP_SYSTEM_ITEMS_B_U1 | NORMAL | UNIQUE | INVENTORY_ITEM_ID |
| EGP_SYSTEM_ITEMS_B_U1 | NORMAL | UNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_U3 | NORMAL | UNIQUE | ORGANIZATION_ID |
| EGP_SYSTEM_ITEMS_B_U3 | NORMAL | UNIQUE | ITEM_NUMBER |
Related Posts
Item Detail Query
Onhand & Available Quantity Calculation
egp_system_items_tl Table
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com