Bill Of Material - Routing query in oracle -R12


A routing defines the step-by-step operations you perform to manufacture a product. Each routing can have any number of operations. For each operation you specify a department that determines the resources you may use for that operation.

Here sharing query to get routing,item and its uom(unit of measure) information :

SELECT distinct bor.line_code,
                mtlp.organization_code,
                msi.segment1 parent_item_code,
                bor.alternate_routing_designator Alternate_routing,
                msi.description item_desc,
                msi.primary_uom_code uom,
                bos.operation_seq_num,
                operation_code,
                bos.operation_description,
                dep.department_code,
                borv.RESOURCE_SEQ_NUM,
                borv.resource_code,
                br.DESCRIPTION,
                crc.resource_rate,
                borv.usage_rate_or_amount,
                borv.usage_rate_or_amount_inverse,
                borv.ASSIGNED_UNITS
  FROM apps.bom_operational_routings_v bor,
       apps.bom_operation_sequences    bos,
       apps.mtl_system_items_b         msi,
       apps.mtl_parameters             mtlp,
       apps.bom_departments            dep,
       apps.bom_standard_operations    bso,
       apps.bom_operation_resources_v  borv,
       apps.bom_resources              br,
       apps.cst_resource_costs         crc
 WHERE bor.routing_sequence_id = bos.routing_sequence_id
   AND msi.organization_id(+) = bor.organization_id
   AND msi.inventory_item_id(+) = bor.assembly_item_id
   AND msi.organization_id = mtlp.organization_id
   AND bos.department_id = dep.department_id
   AND bso.standard_operation_id(+) = bos.standard_operation_id
   AND borv.operation_sequence_id(+) = bos.operation_sequence_id
   AND crc.resource_id = borv.resource_id
   AND br.resource_id = borv.resource_id
   AND br.organization_id = crc.organization_id
   and crc.COST_TYPE_ID = 1
   AND bos.disable_date is null
   AND bor.cfm_routing_flag != 1
   and msi.organization_id=P_organization_id

List of tables based on module and schema in oracle Apps R-12


Tables related to order management module
1.Blanket tables
BLANKET
ONT.OE_BLANKET_HEADERS_ALL
BLANKET
ONT.OE_BLANKET_HEADERS_EXT
BLANKET
ONT.OE_BLANKET_LINES_ALL
BLANKET
ONT.OE_BLANKET_LINES_ALL
BLANKET
ONT.OE_BLANKET_LINES_EXT
2.BOM(Bill of Materials) tables
BOM
BOM.BOM_COMPONENTS_B
BOM
BOM.BOM_DEPARTMENTS
BOM
BOM.BOM_OPERATIONAL_ROUTINGS
BOM
BOM.BOM_OPERATION_RESOURCES
BOM
BOM.BOM_OPERATION_SEQUENCES
BOM
BOM.BOM_RESOURCES
BOM
BOM.BOM_STANDARD_OPERATIONS
BOM
BOM.BOM_STRUCTURES_B
CUSTROUT
BOM.BOM_CALENDARS
CUSTROUT
BOM.BOM_CALENDAR_DATES
CUSTROUT
BOM.BOM_CALENDAR_EXCEPTIONS
CUSTROUT
BOM.BOM_CALENDAR_SHIFTS
CUSTROUT
BOM.BOM_CAL_WEEK_START_DATES
CUSTROUT
BOM.BOM_CAL_YEAR_START_DATES
CUSTROUT
BOM.BOM_EXCEPTION_SETS
CUSTROUT
BOM.BOM_EXCEPTION_SET_DATES
CUSTROUT
BOM.BOM_PERIOD_START_DATES
CUSTROUT
BOM.BOM_SHIFT_DATES
CUSTROUT
BOM.BOM_SHIFT_EXCEPTIONS
CUSTROUT
BOM.BOM_SHIFT_TIMES
CUSTROUT
BOM.BOM_WORKDAY_PATTERNS
CUSTROUT
WSH.WSH_CALENDAR_ASSIGNMENTS
Sales order base tables
ORDER
ONT.OE_ORDER_HEADERS_ALL
ORDER
ONT.OE_ORDER_LINES_ALL
ORDER
ONT.OE_ORDER_LINES_ALL
Tables related Delivery
ORDER
WSH.WSH_DELIVERY_ASSIGNMENTS
ORDER
WSH.WSH_DELIVERY_DETAILS
ORDER
WSH.WSH_DELIVERY_LEGS
ORDER
WSH.WSH_NEW_DELIVERIES
ORDER
last_sale_info
Tables related to contract module
CONTRACT
OKC.OKC_K_GROUPS_B
CONTRACT
OKC.OKC_K_GROUPS_TL
CONTRACT
OKC.OKC_K_GRPINGS
CONTRACT
OKC.OKC_K_HEADERS_ALL_B
CONTRACT
OKC.OKC_K_ITEMS
CONTRACT
OKC.OKC_LINE_STYLES
CONTRACT
OKC.OKC_LINE_STYLES_TL
CONTRACT
OKS.OKS_BILL_CONT_LINES
CONTRACT
OKS.OKS_BILL_TRANSACTIONS
CONTRACT
OKS.OKS_BILL_TXN_LINES
CONTRACT
OKS.OKS_K_HEADERS_B
CONTRACT
OKS.OKS_K_LINES_B
CONTRACT
OKS.OKS_K_LINES_TL
CONTRACT
OKS.OKS_SUBSCR_HEADER_B
CONTRACT
OKS.OKS_SUBSCR_HEADER_TL
Tables related to AR module (TCA acrctitucture)
CUSTOMER
APPLSYS.FND_DOCUMENTS_SHORT_TEXT
CUSTOMER
APPS.FND_DOCUMENTS_VL
CUSTOMER
AR.AR_CONS_BILL_CYCLES_B
CUSTOMER
AR.AR_CONS_BILL_CYCLES_TL
CUSTOMER
AR.AR_STATEMENT_CYCLES
CUSTOMER
AR.HZ_CONTACT_POINTS
CUSTOMER
AR.HZ_CUSTOMER_PROFILES
CUSTOMER
AR.HZ_CUST_ACCOUNTS
CUSTOMER
AR.HZ_CUST_ACCOUNT_ROLES
CUSTOMER
AR.HZ_CUST_ACCT_SITES_ALL
CUSTOMER
AR.HZ_CUST_PROFILE_AMTS
CUSTOMER
AR.HZ_CUST_SITE_USES_ALL
CUSTOMER
AR.HZ_LOCATIONS
CUSTOMER
AR.HZ_ORG_CONTACTS
CUSTOMER
AR.HZ_PARTIES
CUSTOMER
AR.HZ_PARTY_SITES
CUSTOMER
AR.HZ_RELATIONSHIPS
CUSTOMER
AR.HZ_RELATIONSHIP_TYPES
CUSTOMER
AR.HZ_ROLE_RESPONSIBILITY
CUSTOMER
AR.RA_RULES
CUSTOMER
AR.RA_TERMS_B
CUSTOMER
AR.RA_TERMS_TL
CUSTOMER
AR.RA_TERRITORIES
CUSTOMER
GL.GL_LEDGERS
Tables related to organizations :
CUSTOMER
HR.HR_ALL_ORGANIZATION_UNITS
CUSTOMER
HR.HR_ALL_ORGANIZATION_UNITS_TL
CUSTOMER
HR.HR_ORGANIZATION_INFORMATION
Tables related to  CRM:
CUSTOMER
JTF.JTF_RS_RESOURCE_EXTNS
CUSTOMER
JTF.JTF_RS_RESOURCE_EXTNS_TL
CUSTOMER
JTF.JTF_RS_SALESREPS
Tables related to  iSTORE:
CUSTOMER
istore_Accounts
CUSTOMER
istore_Enrollments
CUSTOMER
istore_Roles
FND Tables:
FND
APPLSYS.FND_APPLICATION
FND
APPLSYS.FND_APPLICATION_TL
FND
APPLSYS.FND_LOOKUP_VALUES
FND
APPLSYS.FND_TERRITORIES
FND
APPLSYS.FND_TERRITORIES_TL
FND
APPLSYS.FND_TIMEZONES_B
FND
APPLSYS.FND_TIMEZONES_TL
ITEM
APPLSYS.FND_DOCUMENTS_SHORT_TEXT
ITEM
APPS.FND_DOCUMENTS_VL
Tables related to Item :
ITEM
BOM.CST_ITEM_COSTS
ITEM
GL.GL_CODE_COMBINATIONS
ITEM
HR.HR_LOCATIONS_ALL
ITEM
HR.HR_LOCATIONS_ALL_TL
ITEM
HR.PER_ALL_PEOPLE_F
ITEM
INV.MTL_ATP_RULES
ITEM
INV.MTL_CATEGORIES_B
ITEM
INV.MTL_CATEGORIES_TL
ITEM
INV.MTL_CATEGORY_SETS_B
ITEM
INV.MTL_CATEGORY_SETS_TL
ITEM
INV.MTL_COMMODITY_CODES
ITEM
INV.MTL_CUSTOMER_ITEMS
ITEM
INV.MTL_CUSTOMER_ITEM_XREFS
ITEM
INV.MTL_ITEM_CATEGORIES
ITEM
INV.MTL_ITEM_LOCATIONS
ITEM
INV.MTL_ONHAND_QUANTITIES_DETAIL
ITEM
INV.MTL_PARAMETERS
ITEM
INV.MTL_SYSTEM_ITEMS_B
ITEM
INV.MTL_SYSTEM_ITEMS_B
ITEM
INV.MTL_SYSTEM_ITEMS_TL
ITEM
INV.MTL_UNITS_OF_MEASURE_TL
ITEM
APPLSYS.FND_DOCUMENTS_SHORT_TEXT
ITEM
APPS.FND_DOCUMENTS_VL
ITEM
MRP.MRP_ASSIGNMENT_SETS
ITEM
MRP.MRP_SOURCING_RULES
ITEM
MRP.MRP_SR_ASSIGNMENTS
ITEM
MRP.MRP_SR_RECEIPT_ORG
ITEM
MRP.MRP_SR_SOURCE_ORG
ITEM
PO.PO_HAZARD_CLASSES_B
ITEM
PO.PO_HAZARD_CLASSES_TL
ITEM
PO.PO_UN_NUMBERS_B
ITEM
PO.PO_UN_NUMBERS_TL
Oracle Installed Base Tables:
OAT
CSI.CSI_IEA_VALUES
OAT
CSI.CSI_II_RELATIONSHIPS
OAT
CSI.CSI_INSTANCE_STATUSES
OAT
CSI.CSI_IPA_RELATION_TYPES
OAT
CSI.CSI_IP_ACCOUNTS
OAT
CSI.CSI_ITEM_INSTANCES
OAT
CSI.CSI_ITEM_INSTANCES_H
OAT
CSI.CSI_I_ASSETS
OAT
CSI.CSI_I_ORG_ASSIGNMENTS
OAT
CSI.CSI_I_PARTIES
OAT
CSI.CSI_I_VERSION_LABELS
OAT
CSI.CSI_I_VERSION_LABELS_H
OAT
CSI.CSI_T_PARTY_ACCOUNTS
OAT
CSI.CSI_T_PARTY_DETAILS
OAT
CSI.CSI_T_TXN_LINE_DETAILS
Tables related to PO(Purchase order):
PO
HR.PER_ALL_PEOPLE_F
PO
PO.PO_AGENTS
PO
PO.PO_DISTRIBUTIONS_ALL
PO
PO.PO_HEADERS_ALL
PO
PO.PO_LINES_ALL
PO
PO.PO_LINE_LOCATIONS_ALL
PO
PO.PO_LINE_TYPES_B
PO
PO.PO_REQUISITION_HEADERS_ALL
PO
PO.PO_REQUISITION_LINES_ALL
PO
PO.PO_REQ_DISTRIBUTIONS_ALL
PO
PO.RCV_SHIPMENT_HEADERS
PO
PO.RCV_SHIPMENT_LINES
PO
PO.RCV_TRANSACTIONS
Tables related to Price list:
PRICE
QP.QP_LIST_HEADERS_B
PRICE
QP.QP_LIST_HEADERS_TL
PRICE
QP.QP_LIST_LINES
PRICE
QP.QP_PRICING_ATTRIBUTES
PRICE
QP.QP_QUALIFIERS
Tables related to AP:
VENDOR
AP.AP_SUPPLIERS
VENDOR
AP.AP_SUPPLIER_SITES_ALL
VENDOR
AR.HZ_PARTY_USG_ASSIGNMENTS
VENDOR
GL.GL_CODE_COMBINATIONS
VENDOR
IBY.IBY_ACCOUNT_OWNERS
VENDOR
IBY.IBY_EXTERNAL_PAYEES_ALL
VENDOR
IBY.IBY_EXT_BANK_ACCOUNTS