Query Product Catalog Service

Oracle Apps Adapter 6.0 | webMethods Oracle Applications Adapter Documentation | webMethods Oracle Applications Adapter 12.2.7 Predefined Transaction Services Documentation | Procurement Predefined Transaction Services | Query Product Catalog Service

This service is in the WmOAPRC1227 package and has the following fully-qualified service name: purchasing1227.queryOA.productCatalog:queryProductCatalog.

This service retrieves Supplier Product/Item Catalog information to locate items and their source information to create purchase order and requisition lines.

You can use the following parameters to query the Product Catalog:

ITEM_CATEGORY: Item category.

VENDOR_NAME: Vendor name.

VENDOR_SITE: Vendor site code for an item.

COMMODITY: Unique identifier for an item.

ITEM_REVISION: Item revision code.

ORGANIZATION_NAME: Organization name.

Database Scripts

This service uses the following database scripts:

Script	Description
wm_install_from_productcatalog.sql	Runs the scripts listed below, except the uninstall script.
wm_from_productcatalog_vw.sql	Creates the following required view components: WM_PO_PRODUCT_CATALOG_QRY_VW WM_PO_SOURCING_DOCS_VW WM_PO_SOURCING_RULES_VW WM_PO_PRIOR_PURCHASES_VW WM_PO_NEGOTIATED_SOURCES_VW
wm_drop_from_productcatalog.sql	Uninstalls all components created by wm_install_from_productcatalog.sql.

For more information about using database scripts, see Database Scripts.

Supporting Transaction Definitions

This service uses the following transaction definition:

queryProductCatalogTxn1227.txp

For information about using the transaction definition files to customize this service, see Transaction Definitions.

Flow Control

The main flow queryProductCatalog executes as follows:

specifyDefaultSettings specifies the default parameter settings required for service execution. You should change these settings accordingly.

queryProductCatalogTxn service queries the Oracle Applications database for any Product Catalog 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. SUPPLIER_ITEMS

1.1. NEGOTIATED_SOURCES

1.2. PRIOR_PURCHASES

1.3. SOURCING_RULES

1.3.1. SOURCING_DOCUMENTS

1.0. SUPPLIER_ITEMS

Document Field	Oracle Applications Table/View Name	Column Name	Description
WEB_TRANSACTION_ ID		Not used	Populated from a sequence and used internally in the IS Flow. Will contain NULL value for Queried Product Catalog data.
DOCUMENT_TYPE		Not used	Use PRODUCTCAT.
DOCUMENT_STATUS		Not used	Use QUERY.
ORG_ID			Organization ID.
ORGNIZATION_NAME
ITEM_CATEGORY	MTL_ CATEGORIES	SEGMENT1\|\|’.’ \|\|SEGMENT2	The Category ID stored in the PO_LINES_ALL table joins with Category ID in MTL_CATEGORIES table.
ITEM_NUM	MTL_SYSTEM_ ITEMS_B_KFV	CONCATENATED_ SEGMENTS	The Item ID stored in the PO_LINES_ALL table joins with Item ID in MTL_SYSTEM_ITEMS_B_KFV table.
VENDOR_NAME	AP_SUPPLIERS	VENDOR_NAME	Supplier name.
VENDOR_SITE	AP_SUPPLIER_ SITES_ALL	VENDOR_SITE_ CODE	Supplier site.
ITEM_DESCRIPTION	PO_LINES_ALL	ITEM_DESCRIPTION
ITEM_REVISION	PO_LINES_ALL	ITEM_REVISION	Stored in PO_LINES_ALL.
SUPPLIER_ITEM	PO_LINES_ALL	VENDOR_ PRODUCT_ NUM	Supplier product number.

1.1 NEGOTIATED_SOURCES

Document Field	Oracle Applications Table/View Name	Column Name	Description
VENDOR_NAME	AP_SUPPLIERS	VENDOR_NAME	Supplier name.
VENDOR_SITE	AP_SUPPLIER_ SITES_ALL	VENDOR_SITE_ CODE	Supplier site.
ITEM_NUM	MTL_SYSTEM_ ITEMS_B_KFV	CONCATENATED_ SEGMENTS	The Item ID stored in the PO_LINES_ALL table joins with Item ID in MTL_SYSTEM_ITEMS_B_KFV table.
ITEM_CATEGORY	MTL_ CATEGORIES	SEGMENT1\|\|’ .’\|\|SEGMENT 2	The Category ID stored in the PO_LINES_ALL table joins with Category ID in MTL_CATEGORIES table.
ITEM_DESCRIPTION	PO_LINES_ALL	ITEM_DESCRIPTION
ITEM_REVISION	PO_LINES_ALL	ITEM_REVISION	Stored in PO_LINES_ALL.
LINE_UOM	PO_LINES_ALL	UNIT_MEAS_ LOOKUP_CODE	Unit of measure for the line.
LINE_PRICE	PO_LINES_ALL	UNIT_PRICE	Unit price for the line.
SUPPLIER_ITEM	PO_LINES_ALL	VENDOR_PRODUCT_ NUM	Supplier product number.
BREAK_QUANTITY	PO_LINE_ LOCATIONS_ ALL	QUANTITY - NVL (QUANTITY_ CANCELLED,0)	Quantity ordered or break quantity for blanket purchase orders, Requests For Quotations (RFQs), and quotations minus quantity cancelled.
BREAK_PRICE	PO_LINE_ LOCATIONS_ALL	PRICE_OVERRIDE	Order shipment price or break price for blanket purchase orders, RFQs, and quotations.

1.2 PRIOR_PURCHASES

Document Field	Oracle Applications Table/View Name	Column Name	Description
ORDER_ DATE	PO_HEADERS_ALL PO_RELEASES_ALL	CREATION_DATE	IF TYPE_LOOKUP_CODE = Standard, use CREATION_DATE from the PO_HEADERS_ALL table.
			If TYPE_LOOKUP_CODE = Planned or Blanket, use CREATION_DATE in the table PO_RELEASES_ALL.
VENDOR_ NAME	AP_SUPPLIERS	VENDOR_NAME	Supplier name.
VENDOR_ SITE	AP_SUPPLIER_ SITES_ALL	VENDOR_SITE_ CODE	Supplier site.
ITEM_ NUM	MTL_SYSTEM_ ITEMS_B_KFV	CONCATENATED_ SEGMENTS	The Item ID stored in the PO_LINES_ALL table joins with Item ID in MTL_SYSTEM_ITEMS_B_KFV table.
ITEM_ CATEGORY	MTL_CATEGORIES	SEGMENT1 \|\| ’.’ \|\| SEGMENT 2	The Category ID stored in the PO_LINES_ALL table joins with Category ID in MTL_CATEGORIES table.
ITEM_ DESCRIPTION	PO_LINES_ALL	ITEM_DESCRIPTION
UOM	PO_LINES_ALL	UNIT_MEAS_ LOOKUP_CODE	Unit of measure.
SUPPLIER_ ITEM	PO_LINES_ALL	VENDOR_PRODUCT_ NUM	Supplier product number.
QUANTITY	PO_ LINE_ LOCATIONS_ ALL	QUANTITY - NVL (QUANTITY_ CANCELLED,0)	Quantity ordered or break quantity for blanket purchase orders, RFQ, and quotations minus quantity cancelled.
PRICE	PO_ LINES_ ALL / PO_ LINE_ LOCATIONS_ ALL	PO_LINES_ ALL. UNIT_PRICE / PO_LINE_ LOCATIONS_ ALL.PRICE_ OVERRIDE	Unit price for standard PO, or the price override for a planned or blanket PO.

1.3 SOURCING_RULES

Document Field	Oracle Applications Table/View Name	Column Name	Description
ORG_ID	MRP_ SOURCES_V	ORGANIZATION_ID	Organization identifier.
RULE	MRP_ SOURCES_V	SOURCING_ RULE_NAME	Rule name.
RULE_ID	MRP_ SOURCES_V	SOURCING_ RULE_ID	Rule identifier.
ITEM_NUM	MTL_SYSTEM_ ITEMS_B_KFV	CONCATENATED_ SEGMENTS	The item ID stored in the PO_LINES_ALL table joins with Item ID in MTL_SYSTEM_ITEMS_B_KFV table.
ITEM_DESCRIPTION	PO_LINES_ALL	ITEM_DESCRIPTION
FROM_ DATE	MRP_ SOURCES_V	EFFECTIVE_ DATE
TO_DATE	MRP_SOURCES_V	DISABLE_DATE
VENDOR_ NAME	AP_SUPPLIER S	VENDOR_NAME	Supplier name.
VENDOR_ SITE	AP_SUPPLIER_ SITES_ALL	VENDOR_ SITE_CODE	Supplier site.
ORGANIZATION_ CODE	MTL_ PARAMETERS	ORGANIZATION_ CODE
VENDOR_ID	AP_SUPPLIERS	VENDOR_ID	Vendor identifier.

1.3.1 SOURCING_DOCUMENTS

Document Field	Oracle Applications Table/View Name	Column Name	Description
ITEM_NUM	MTL_SYSTEM_ ITEMS_B_KFV	CONCATENATED_ SEGMENTS	Item number.
ITEM_CATEGORY	MTL_ CATEGORIES	SEGMENT1 \|\| ’ .’ \|\| SEGMENT 2	The Category ID stored in the PO_LINES_ALL table joins with Category ID in MTL_CATEGORIES table.
VENDOR_ID	AP_SUPPLIERS	VENDOR_ID	Vendor identifier.
RULE_ID	PO_ASL_ DOCUMENTS	ASL_ID	Rule identifier.
SEQUENCE_ NUM	PO_ASL_ DOCUMENTS	SEQUENCE_ NUM	Sequence number.
VENDOR_ PRODUCT_NUM	PO_LINES_ALL	VENDOR_ PRODUCT_ NUM	Vendor Product Number.
LINE_UOM	PO_LINES_ALL	UNIT_MEAS_ LOOKUP_CODE	Unit of measure.
LINE_PRICE	PO_LINES_ALL	UNIT_PRICE	Unit price for the line.
BREAK_ QUANTITY	PO_LINE_ LOCATIONS_ALL	QUANTITY - NVL (QUANTITY_CANCELLED,0)	Quantity ordered or break quantity for blanket purchase orders, RFQ and quotations minus quantity cancelled.
BREAK_PRICE	PO_LINE_ LOCATIONS_ALL	PRICE_OVERRIDE	Order shipment price or break price for blanket purchase orders, Requests For Quotations (RFQs), and quotations.