This ASCP Item Attributes query retrieves detailed item attribute information from the inventory system, including planning parameters, order controls, shipping flags, sourcing details, and organizational context. It joins item master data with lookup and organization tables to provide a comprehensive view of item setup across different planning and operational dimensions.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query retrieves item attributes with planning, sourcing, and organizational setup details.
HAOU.NAME ORGANIZATION_NAME,
ESI.WIP_SUPPLY_SUBINVENTORY,
ESI.WIP_SUPPLY_TYPE,
ESI.ITEM_NUMBER,
ESI.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS,
ESI.DESCRIPTION ITEM_DESCRIPTION,
ESI.ITEM_TYPE,
ESI.MIN_MINMAX_QUANTITY MIN_MINMAX_QTY,
ESI.MAX_MINMAX_QUANTITY MAX_MINMAX_QTY,
ESI.MINIMUM_ORDER_QUANTITY MIN_ORDER_QTY,
ESI.MAXIMUM_ORDER_QUANTITY MAX_ORDER_QTY,
ESI.FIXED_ORDER_QUANTITY,
ESI.ORDER_COST,
ESI.CARRYING_COST,
ESI.INVENTORY_PLANNING_CODE PLANNING_CODE,
RL.MEANING PLANNING_METHOD,
ESI.PLANNER_CODE PLANNER_CODE,
ESI.PLANNING_MAKE_BUY_CODE MAKE_BUY_CODE,
RL1.MEANING MAKE_OR_BUY,
ESI.FIXED_DAYS_SUPPLY,
ESI.FIXED_LOT_MULTIPLIER,
ESI.SAFETY_STOCK_BUCKET_DAYS,
ESI.MRP_SAFETY_STOCK_PERCENT,
ESI.INVENTORY_CARRY_PENALTY CARRY_PENALTY,
ESI.OPERATION_SLACK_PENALTY SLACK_PENALTY,
ESI.SOURCE_TYPE SOURCE_TYPE_CODE,
RL2.MEANING SOURCE_DESCRIPTION,
ESI.DEFAULT_SO_SOURCE_TYPE,
ESI.OVER_SHIPMENT_TOLERANCE,
ESI.UNDER_SHIPMENT_TOLERANCE,
ESI.OVER_RETURN_TOLERANCE,
ESI.UNDER_RETURN_TOLERANCE,
ESI.INVENTORY_ITEM_FLAG,
ESI.STOCK_ENABLED_FLAG IS_STOCKABLE, --INDICATES WHETHER THE ITEM IS ALLOWED TO BE STOCKED.
ESI.MTL_TRANSACTIONS_ENABLED_FLAG IS_TRANSACTABLE, --INDICATES WHETHER TRANSACTIONS ARE ALLOWED TO BE PERFORMED ON ITEM.
ESI.SUBCONTRACTING_COMPONENT IS_SUBCONTRACT_COMPONENT, --SUBCONTRACTING TYPE FOR COMPONENT USAGE.
ESI.RESERVABLE_TYPE, --ITEM ATTRIBUTE ENABLING CREATION OF MATERIAL RESERVATIONS FOR ITEM.
ESI.PURCHASING_ITEM_FLAG IS_PURCHASED, --INDICATES IF ITEM IS PURCHASABLE.
ESI.PURCHASING_ENABLED_FLAG IS_PURCHASABLE, --ENABLES ITEM FOR PURCHASING OPERATIONS.
ESI.INVOICEABLE_ITEM_FLAG IS_INVOICEABLE, -- DETERMINES ITEM ELIGIBILITY FOR PO.
ESI.INVOICE_ENABLED_FLAG IS_INVOICE_ENABLED, -- INDICATES IF ITEM IS ACTIVE FOR INVOICING IN ORACLE RECEIVABLES.
ESI.BUILD_IN_WIP_FLAG BUILD_IN_WIP_FLAG, --DETERMINES DISCRETE JOBS OR REPETITIVE ASSEMBLIES CREATION IN ORACLE WIP.
ESI.PICK_COMPONENTS_FLAG PICK_COMPONENTS_ENABLED, --INDICATES ITEM HAS BOM WITH OPTIONS, CLASSES, OR INCLUDED ITEMS.
ESI.SECONDARY_DEFAULT_IND IS_SECONDARY_DEFAULT, --CONTROLS HOW ITEM ATTRIBUTE DEFAULTS ARE APPLIED DURING ITEM CREATION.
ESI.DUAL_UOM_DEVIATION_HIGH UOM_DEV_HIGH, --ALLOWED UPPER DEVIATION FOR TRANSACTION QUANTITY FROM DEFAULT CONVERSION FACTOR.
ESI.DUAL_UOM_DEVIATION_LOW UOM_DEV_LOW, --ALLOWED LOWER DEVIATION FOR TRANSACTION QUANTITY FROM DEFAULT CONVERSION FACTOR.
ESI.CUSTOMER_ORDER_FLAG IS_CUSTOMER_ORDERED, --INDICATES IF ITEM CAN BE ORDERED BY EXTERNAL CUSTOMERS.
ESI.INTERNAL_ORDER_FLAG IS_INTERNAL_ORDERED, --INDICATES IF ITEM IS ALLOWED FOR INTERNAL TRANSFER.
ESI.CUSTOMER_ORDER_ENABLED_FLAG CUSTOMER_ORDERS_ENABLED, --INDICATES IF ITEM IS CURRENTLY AVAILABLE FOR CUSTOMER ORDERS.
ESI.INTERNAL_ORDER_ENABLED_FLAG INTERNAL_ORDERS_ENABLED, --INDICATES IF ITEM CAN BE ORDERED VIA INTERNAL REQUISITION.
ESI.SHIPPABLE_ITEM_FLAG IS_SHIPPABLE, --INDICATES WHETHER ITEM IS ALLOWED TO SHIP TO CUSTOMER.
ESI.SO_TRANSACTIONS_FLAG IS_OE_TRANSACTABLE, --INDICATES IF ORACLE ORDER MANAGEMENT CAN DEMAND AND SHIP ITEM.
ESI.REPLENISH_TO_ORDER_FLAG IS_ASSEMBLE_TO_ORDER, --INDICATES IF ITEM IS BUILT FROM SALES ORDER DEMAND WORK ORDER.
ESI.SHIP_MODEL_COMPLETE_FLAG SHIP_MODEL_COMPLETE, -- INDICATES IF CONFIGURATION SHIPS ONLY WHEN ALL COMPONENTS ARE AVAILABLE
ESI.RETURNABLE_FLAG IS_RETURNABLE, -- INDICATES IF ITEM IS ALLOWED TO BE RETURNED BY CUSTOMERS.
ESI.RETURN_INSPECTION_REQUIREMENT RMA_INSPECTION_REQUIRED, --INDICATES IF RETURNED ITEMS REQUIRE INSPECTION BEFORE INVENTORY TRANSFER.
DECODE(ESI.FINANCING_ALLOWED_FLAG, 'Y', 'YES', ESI.FINANCING_ALLOWED_FLAG) FINANCING_ALLOWED --INDICATES IF CUSTOMERS ARE ALLOWED TO FINANCE THIS ITEM.
FROM
EGP_SYSTEM_ITEMS ESI,
RCS_LOOKUPS RL,
RCS_LOOKUPS RL1,
RCS_LOOKUPS RL2,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND HAOU.ORGANIZATION_ID = ESI.ORGANIZATION_ID
AND ESI.INVENTORY_PLANNING_CODE = RL.LOOKUP_CODE (+)
AND RL.LOOKUP_TYPE (+) = 'MTL_MATERIAL_PLANNING'
AND ESI.PLANNING_MAKE_BUY_CODE = RL1.LOOKUP_CODE (+)
AND RL1.LOOKUP_TYPE (+) = 'MTL_PLANNING_MAKE_BUY'
AND ESI.SOURCE_TYPE = RL2.LOOKUP_CODE (+)
AND RL2.LOOKUP_TYPE (+) = 'MTL_SOURCE_TYPES'
AND ESI.ORGANIZATION_ID = NVL(:P_ORGID, ESI.ORGANIZATION_ID)