Oracle Apps Adapter 6.0 | webMethods Oracle Applications Adapter Documentation | webMethods Oracle Applications Adapter 12.2.7 Predefined Transaction Services Documentation | Manufacturing Predefined Transaction Services | Query BOM Routing Service
 
Query BOM Routing Service
This service is in the WmOAMFG1227 package and has the following fully-qualified service name: billsOfMaterial1227.queryOA.BOMRouting:queryBOMRouting.
This service retrieves information on the routing of parent, component, substitute items, and routing revisions to Bills of Material, based on querying the following parameters:
*ORGANIZATION_NAME: Name of the organization.
*ITEM_NUMBER: Item number.
*REVISION: Revision of the parent item.
*EFFECTIVITY_DATE_FROM: Beginning effective date.
*EFFECTIVITY_DATE_TO: Ending effective date.
If none of the above mentioned parameters are entered, the query retrieves all rows from the Oracle Applications database.
Database Scripts
This service uses the following database scripts:
Script
Description
wm_install_from_bomrouting.sql
Runs the scripts listed below, except the uninstall script.
wm_from_bomrouting_vw.sql
Creates the following required view components for BOM Routing transactions:
*WM_BOM_OP_ROUTING_QRY_VW
*WM_MTL_RTG_ITEM_REVS_QRY_VW
*WM_BOM_OP_SEQUENCES_QRY_VW
*WM_BOM_OP_RESOURCES_QRY_VW
wm_drop_from_bomrouting.sql
Uninstalls all components created by wm_install_from_bomrouting.sql.
For more information about using database scripts, see Database Scripts.
Supporting Transaction Definitions
This service uses the following transaction definition:
*queryBOMRouting1227.txp
For information about using the transaction definition files to customize this service, see Transaction Definitions.
Flow Control
The main flow queryBOMRouting executes as follows:
*specifyDefaultSettings specifies the default parameter settings required for service execution. You should change these settings accordingly.
*queryBOMRoutingTxn queries the Oracle Applications database for any BOM Routings information matching the parameter values. The parameters are defined as the input to this service.
For more details on query transactions, see Using Query Services.
Business Document Structure
This service uses the following business document structure:
*1.0. BOM_OP_ROUTINGS
*1.1. MTL_RTG_ITEM_REVS
*1.2. BOM_OP_SEQUENCES
*1.2.1. BOM_OP_RESOURCES
1.0. BOM_PO_ROUTINGS
Document Field
Oracle Applications Table/View Name
Column Name
Description
WEB_
TRANSACTION_ID
DOCUMENT_TYPE
Use BOM_ROUTING.
DOCUMENT_
STATUS
Use QUERY.
ROUTING_
SEQUENCE_ID
BOM_
OPERATIONAL_
ROUTINGS
ROUTING_
SEQUENCE_ID
Unique routing identifier.
ORGANIZATION_
NAME
HR_ALL_
ORGANIZATION_
UNITS
NAME
ASSEMBLY_ITEM_
NUMBER
MTL_SYSTEM_
ITEMS_B_KFV
CONCATENATED_
SEGMENTS
Material item number.
ALTERNATE_
ROUTING_
DESIGNATOR
BOM_
OPERATIONAL_
ROUTINGS
ALTERNATE_
ROUTING_
DESIGNATOR
Alternate routing designator.
ROUTING_TYPE
BOM_
OPERATIONAL_
ROUTINGS
ROUTING_TYPE
Required for Insert. Use:
*1 for Manufacturing.
*2 for Engineering.
COMMON_
ITEM_
NUMBER
MTL_SYSTEM_
ITEMS_B_KFV
CONCATENATED_
SEGMENTS
Concatenated item segments of the common routing.
COMMON_
ORG_
NAME
HR_ALL_
ORGANIZATION_
UNITS
NAME
Derived from columns
COMMON_ITEM_NUMBER,
COMMON_ORG_NAME,
and ALTERNATE_
ROUTING_DESIGNATOR.
ROUTING_
COMMENT
BOM_
OPERATIONAL_
ROUTINGS
ROUTING_
COMMENT
Specific comment about routing.
COMPLETION_
SUBINVENTORY
BOM_
OPERATIONAL_
ROUTINGS
COMPLETION_
SUBINVEN TORY
Destination subinventory for assembly.
LOCATION_NAME
MTL_ITEM_
LOCATIONS
DESCRIPTION
Material item locations.
TRANSACTION_
TYPE
Not required for outbound transactions.
LINE_CODE
WIP_LINES
LINE_CODE
WIP line code.
MIXED_MODEL_
MAP_FLAG
BOM_
OPERATIONAL_
ROUTINGS
MIXED_MODEL_
MAP_FLAG
Indicates mixed model. Use:
*1 for Yes.
*2 for No.
PRIORITY
BOM_
OPERATIONAL_
ROUTINGS
PRIORITY
CFM_ROUTING_
FLAG
BOM_
OPERATIONAL_
ROUTINGS
CFM_ROUTING_
FLAG
Indicates continuous flow or traditional routing. Use:
*1 for Yes.
*2 for No.
TOTAL_
PRODUCT_
CYCLE_TIME
BOM_
OPERATIONAL_
ROUTINGS
TOTAL_
PRODUCT_
CYCLE_TIME
Total time that an assembly takes along the primary path in the operation network calculated by Flow Manufacturing.
CTP_FLAG
BOM_
OPERATIONAL_
ROUTINGS
CTP_FLAG
Indicates capacity must be checked when item is ordered.
1.1 MTL_RTG_ITEM_REVS
Document Field
Oracle Applications Table/View Name
Column Name
Description
ROUTING_
SEQUENCE_ID
BOM_
OPERATIONAL_
ROUTINGS
ROUTING_
SEQUENCE_ID
Unique routing identifier.
PROCESS_
REVISION
MTL_RTG_
ITEM_REVISIONS
PROCESS_
REVISION
Routing revision code.
CHANGE_NOTICE
MTL_RTG_
ITEM_REVISIONS
CHANGE_NOTICE
Engineering change order number.
ECN_
INITIATION_
DATE
MTL_RTG_
ITEM_REVISIONS
ECN_
INITIATION_
DATE
Engineering Change order initiation date.
IMPLEMENTATION_
DATE
MTL_RTG_
ITEM_REVISIONS
IMPLEMENTA
TION_DATE
Engineering change order implementation date.
EFFECTIVITY_
DATE
MTL_RTG_
ITEM_REVISIONS
EFFECTIVITY_
DATE
Revision effective date.
INVENTORY_I
TEM_NUMBER
MTL_SYSTEM _
ITEMS_B_KFV
CONCATENATED_
SEGMENTS
Concatenated item segments.
ORGANIZATION_
NAME
HR_ALL_
ORGANIZATION_
UNITS
NAME
TRANSACTION_
TYPE
MTL_RTG_
ITEM_REVISIONS
TRANSACTION_
TYPE
Not required for outbound transactions.
1.2 BOM_OP_SEQUENCES
Document Field
Oracle Applications Table/View Name
Column Name
Description
ROUTING_
SEQUENCE_ID
BOM_
OPERATION_
SEQUENCES
ROUTING_
SEQUENCE_ID
Unique routing identifier.
OPERATION_
SEQUENCE_ID
BOM_
OPERATION_
SEQUENCES
OPERATION_
SEQUENCE_ID
Unique operation identifier.
OPERATION_
SEQ_NUM
BOM_
OPERATION_
SEQUENCES
OPERATION_
SEQ_NUM
Operation sequence number.
OPERATION_
LEAD_TIME_
PERCENT
BOM_
OPERATION_
SEQUENCES
OPERATION_
LEAD_TIME_
PERCENT
Indicates the amount of
overlap its lead time has with the
parent's lead time.
MINIMUM_
TRANSFER_
QUANTITY
BOM_
OPERATION_
SEQUENCES
MINIMUM_
TRANSFER_
QUANTITY
Minimum operation transfer quantity.
COUNT_POINT_TYPE
BOM_
OPERATION_
SEQUENCES
COUNT_
POINT_TYPE
Use:
*Yes for auto charge.
*No for direct charge.
OPERATION_
DESCRIPTION
BOM_
OPERATION_
SEQUENCES
OPERATION_
DESCRIPTION
EFFECTIVITY_
DATE
BOM_
OPERATION_
SEQUENCES
EFFECTIVITY_
DATE
Date operation is effective.
DISABLE_DATE
BOM_
OPERATION_
SEQUENCES
DISABLE_DATE
Date operation is no longer effective.
BACKFLUSH_
FLAG
BOM_
OPERATION_
SEQUENCES
BACKFLUSH_
FLAG
Indicates whether operation requires backflushing. Use:
*1 for Yes.
*2 for No.
OPTION_
DEPENDENT_
FLAG
BOM_
OPERATION_
SEQUENCES
OPTION_
DEPENDENT_
FLAG
Indicates whether to use this operation in all configuration routings, even if no components of the configuration are in this routing. Use:
*1 for Yes.
*2 for No.
ALTERNATE_
ROUTING_
DESIGNATOR
BOM_
OPERATION_
SEQUENCES
ALTERNATE_
ROUTING_
DESIGNATOR
ORGANIZATION_
NAME
HR_ALL_
ORGANIZATION_
UNITS
NAME
Use HR_ALL_ORGANIZATION_
UNITS to derive ORGANIZATION_ID.
ASSEMBLY_
ITEM_
NUMBER
MTL_SYSTEM_
ITEMS_B_KFV
CONCATENATED_
SEGMENTS
Concatenated item segments.
DEPARTMENT_
CODE
BOM_
DEPARTMENTS
DEPARTMENT_
CODE
Required for Insert.
OPERATION_
CODE
BOM_
STANDARD_
OPERATIONS
OPERATION_
CODE
Standard operation code.
OPERATION_
TYPE
BOM_
OPERATION_
SEQUENCES
OPERATION_
TYPE
Use Process, Line Operation, or Event.
REFERENCE_FLAG
BOM_
OPERATION_
SEQUENCES
REFERENCE_
FLAG
If the standard operation is referenced or copied, then the operation cannot be updated. Use:
*1 for Yes.
*2 for No.
PROCESS_OP_
SEQ_ID
BOM_
OPERATION_
SEQUENCES
PROCESS_OP_
SEQ_ID
Use only for events. Operation sequence identifier of parent process.
LINE_OP_
SEQ_ID
BOM_
OPERATION_
SEQUENCES
LINE_OP_
SEQ_ID
Use only for events. Operation sequence identifier of the parent line operation.
YIELD
BOM_
OPERATION_
SEQUENCES
YIELD
Process yields at this operation.
REVERSE_
CUMULATIVE_
YIELD
BOM_
OPERATION_
SEQUENCES
REVERSE_
CUMULATIVE_
YIELD
Cumulative process yield from end of routing to comparable operation.
LABOR_TIME_
CALC
BOM_
OPERATION_
SEQUENCES
LABOR_TIME_
CALC
System-calculated run time for labor.
MACHINE_TIME_
CALC
BOM_
OPERATION_
SEQUENCES
MACHINE_TIME_
CALC
System-calculated run time for machines.
TOTAL_TIME_
CALC
BOM_
OPERATION_
SEQUENCES
TOTAL_TIME_
CALC
System-calculated run time for labor and machines.
LABOR_TIME_
USER
BOM_
OPERATION_
SEQUENCES
LABOR_TIME_
USER
User-calculated run time for labor.
MACHINE_
TIME_USER
BOM_
OPERATION_
SEQUENCES
MACHINE_
TIME_USER
User-calculated run time for machines.
TOTAL_
TIME_
USER
BOM_
OPERATION_
SEQUENCES
TOTAL_TIME_
USER
User-calculated run time for labor and machines.
NET_
PLANNING_
PERCENT
BOM_
OPERATION_
SEQUENCES
NET_
PLANNING_
PERCENT
Cumulative planning percent derived from the operation network.
INCLUDE_IN_
ROLLUP
BOM_
OPERATION_
SEQUENCES
INCLUDE_IN_
ROLLUP
Indicates whether operation yield is considered in cost rollup.
OPERATION_
YIELD_
ENABLED
BOM_
OPERATION_
SEQUENCES
OPERATION_
YIELD_
ENABLED
Indicates whether operation yield is considered during costing.
1.2.1. BOM_OP_RESOURCES
Document Field
Oracle Applications Table View Name
Column Name
Description
OPERATION_
SEQUENCE_ID
BOM_
OPERATION_
RESOURCES
OPERATION_
SEQUENCE_ID
RESOURCE_
SEQ_NUM
BOM_
OPERATION_
RESOURCES
RESOURCE_
SEQ_NUM
Resource sequence number.
STANDARD_
RATE_FLAG
BOM_
OPERATION_
RESOURCES
STANDARD_
RATE_FLAG
Indicates standard rate for shop floor transactions. Use:
*1 for Yes.
*2 for No.
ASSIGNED_
UNITS
BOM_
OPERATION_
RESOURCES
ASSIGNED_
UNITS
Resource units assigned.
USAGE_RATE_
OR_AMOUNT
BOM_
OPERATION_
RESOURCES
USAGE_RATE_
OR_AMOUNT
Resource usage rate.
USAGE_RATE_
OR_AMOUNT_
INVERSE
BOM_
OPERATION_
RESOURCES
USAGE_RATE_
OR_AMOUNT_
INVERSE
Resource usage rate inverse.
BASIS_TYPE
BOM_
OPERATION_
RESOURCES
BASIS_TYPE
Use:
*1 for Item.
*2 for Lot.
*3 for Resource Units.
*4 for Resource Value.
*5 for Total Value.
*6 for Activity.
SCHEDULE_
FLAG
BOM_
OPERATION_
RESOURCES
SCHEDULE_
FLAG
Indicates whether to schedule the resource. Use:
*1 for Yes.
*2 for No.
*3 for Prior.
*4 for Next.
RESOURCE_
OFFSET_PERCENT
BOM_
OPERATION_
RESOURCES
RESOURCE_
OFFSET_
PERCENT
Resource offset percent from the start of the routing.
AUTOCHARGE_
TYPE
BOM_
OPERATION_
RESOURCES
AUTOCHARGE_
TYPE
Autocharge type for shopfloor moves. Use:
*1 for WIP move.
*2 for Manual.
*3 for PO receipt.
*4 for PO move.
ALTERNATE_
ROUTING_
DESIGNATOR
BOM_
OPERATIONAL_
ROUTINGS
ALTERNATE_
ROUTING_
DESIGNATOR
Alternate designator code.
OPERATION_
SEQ_NUM
BOM_
OPERATION_
SEQUENCES
OPERATION_
SEQ_NUM
Operation sequence number.
EFFECTIVITY_
DATE
BOM_
OPERATION_
SEQUENCES
EFFECTIVITY_
DATE
Effective date.
ORGANIZATION_
NAME
HR_ALL_
ORGANIZATION_
UNITS
NAME
Use
HR_ALL_ORGANIZATION_
UNITS to derive ORGANIZATION_ID.
ASSEMBLY_
ITEM_NUMBER
MTL_SYSTEM_
ITEMS_B_KFV
CONCATENATED_
SEGMENTS
Concatenated item segments.
RESOURCE_
CODE
BOM_
RESOURCES
RESOURCE_
CODE
Resource name.
ACTIVITY
CST_ACTIVITIES
ACTIVITY
Activity name.
TRANSACTION_
TYPE
Not used for outbound transactions.
NEW_
RESOURCE_
SEQ_NUM
Not used for outbound transactions.