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.