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 On-Hand Quantity Service
 
Query On-Hand Quantity Service
This service is in the WmOAMFG1227 package and has the following fully-qualified service name: inventory1227.queryOA.onHandQuantity:queryOnHandQuantity.
This service retrieves the on-hand quantities for a particular item, based on the following parameters:
*ITEM_NUMBER: Part number of the item as defined in Oracle Applications.
*REVISION: Revision number of the item.
*ORGANIZATION_NAME: Name of the inventory organization.
*SUBINVENTORY_CODE: Subinventory code.
*LOCATOR: Locator code.
Database Scripts
This service uses the following database scripts:
Script
Description
wm_install_from_onhandquantity.sql
Runs the scripts listed below, except the uninstall script.
wm_from_onhandquantity_vw.sql
Creates the following required view component:
WM_INV_ONHAND_QRY_VW
wm_drop_from_onhandquantity.sql
Uninstalls all components created by wm_install_from_onhandquantity.sql.
For more information about using database scripts, see Database Scripts.
Supporting Transaction Definitions
This service uses following transaction definition:
*queryOnHandQuantityTxn1227.txp
For information about using the transaction definition files to customize this service, see Transaction Definitions.
Flow Control
The main flow queryOnHandQuantity executes as follows. If no input parameters are specified, the query returns all rows.
*specifyDefaultSettings specifies the default parameter settings required for service execution. You should change these settings accordingly.
*queryOnHandQuantityTxn queries the Oracle Applications database for any Inventory- On Hand records 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:
*ON_HAND_QUANTITY
ON_HAND_QUANTITY
Document Field
Oracle Applications Table/View Name
Column Name
Description
WEB_
TRANSACTION_ID
Not used
Not used for query outbound transactions.
DOCUMENT_
TYPE
INVONHAND will be populated in this field.
DOCUMENT_
STATUS
QUERY will be populated in this field.
INVENTORY_
ITEM_ID
MTL_ONHAND_
QUANTITIES_
DETAIL
INVENTORY_
ITEM_ID
ORGANIZATION_
ID
MTL_ONHAND_
QUANTITIES_
DETAIL
ORGANIZATION_
ID
ITEM_NUMBER
MTL_SYSTEM_
ITEMS_B_
KFV
CONCATENATED_
SEGMENTS
Concatenated segments.
REVISION
MTL_ONHAND_
QUANTITIES_
DETAIL
REVISION
TOTAL_QOH
MTL_ONHAND_
QUANTITIES_
DETAIL
PRIMARY_
TRANSACTION_
QUANTITY
Transaction quantity.
SUBINVENTORY_
CODE
MTL_ONHAND_
QUANTITIES
SUBINVENTORY_
CODE
LOCATOR
MTL_ITEM_
LOCATIONS_
B_KFV
CONCATENATED_
SEGMENTS
Concatenated Locator.
ITEM_
DESCRIPTION
MTL_SYSTEM_
ITEMS_B_KFV
DESCRIPTION
PRIMARY_
UOM_CODE
MTL_SYSTEM_
ITEMS_B_KFV
PRIMARY_
UOM_CODE
Primary unit of measure code.
ORGANIZATION_
CODE
MTL_
PARAMETERS
ORGANIZATION_
CODE
ORGANIZATION_
NAME
HR_
ORGANIZATION_
UNITS
NAME
LOCATOR_TYPE
MTL_
SECONDARY_
INVENTORIES
LOCATOR_TYPE
ITEM_REV_
CONTROL
MTL_SYSTEM_
ITEMS_B_KFV
REVISION_
QTY_
CONTROL_
CODE
ITEM_LOCATOR_
CONTROL
MTL_SYSTEM_
ITEMS_B_KFV
LOCATION_
CONTROL_
CODE
ITEM_
LOT_CONTROL
MTL_SYSTEM_
ITEMS_B_KFV
LOT_
CONTROL_
CODE
ITEM_SERIAL_
CONTROL
MTL_SYSTEM_
ITEMS_B_KFV
SERIAL_
NUMBER_
CONTROL_
CODE