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_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. |