Oracle Apps Adapter 6.0 | webMethods Oracle Applications Adapter Documentation | webMethods Oracle Applications Adapter 12.2.7 Predefined Transaction Services Documentation | Order Management Predefined Transaction Services | Query Price Request
 
Query Price Request
This service is in the WmOAOMG1227 package and has the following fully-qualified service name: advancedPricing1227.queryOA.priceRequest:queryPriceRequest
This service notifies and delivers purchase order (PO) changes. You can use the Query Price Request service to retrieve all new or changed approved POs. Since you cannot delete POs, the document status of DELETE does not apply.
The Oracle Applications Advanced Pricing Price Request Application Program Interface (API) is a public API that gets a base price and applies price adjustments, other benefits, and charges to a transaction.
Oracle Applications products request this service to price calculations. You can also request it from custom applications and legacy systems.
A pricing request consists of numerous price request lines, which mirror the transaction lines of the calling application, and can include a transaction header request line. Since it is PL/SQL based, the pricing request processes one pricing request per call.
To properly use the Price Request Application Program Interface, use all lines that need prices and that the pricing engine needs as part of the pricing request. For example, you freeze the price of one order line. If you include this line in the pricing request, the pricing engine might still be able to use the quantity on that line used to qualify the order to receive another discount based on quantities across multiple lines.
*Search engine: Uses qualifiers and pricing attributes passed from the calling application to select the price list lines and the modifier list lines that can apply to the pricing request. As part of this process, the search engine uses rules of eligibility, incompatibility, exclusivity, and precedence.
For each pricing phase the search engine executes the following functions:
*Selects eligible price list lines and modifier list lines using predefined pricing rules.
*Resolves incompatibilities among eligible benefits.
*Applies the eligible benefits to the pricing request.
*Calculation engine: For each pricing request line and its associated pricing request line details, it calculates the base price, adjusted price, and extended price.
You can call one or both of the engines by setting the calculate flag on the control record.
Database Scripts
This service uses the following database scripts:
Database Script
Description
wm_install_from_priceRequest.sql
Runs all the scripts listed below, except the uninstall script.
wm_from_priceRequest_tbl.sql
Creates the required tables:
*WM_LINE_TBL
*WM_QUAL_TBL
*WM_LINE_ATTR_TBL
*WM_LINE_DETAIL_TBL
*WM_LINE_DETAIL_QUAL_TBL
*WM_LINE_DETAIL_ATTR_TBL
*WM_RELATED_LINES_TBL
wm_from_priceRequest_syn.sql
Creates the required synonyms:
*WM_LINE_TBL
*WM_QUAL_TBL
*WM_LINE_ATTR_TBL
*WM_LINE_DETAIL_TBL
*WM_LINE_DETAIL_QUAL_TBL
*WM_LINE_DETAIL_ATTR_TBL
*WM_RELATED_LINES_TBL
wm_into_priceRequest_pkg.sql
Script to create the package procedures:
*Wm_Price_Request_Api
*Wm_Price_Request_del
wm_drop_from_priceRequest.sql
Uninstalls all components created by wm_install_from_priceRequest.sql.
For more information about using database scripts, see Database Scripts.
Supporting Transaction Definitions
This service uses the following transaction definitions:
*queryPriceRequestTxn1227.txp
*setPriceRequestTxn1227.txp
For information about using the transaction definition files to customize this service, see Transaction Definitions.
Flow Control
The main flow executes as follows:
*specifyDefaultSettings specifies the default parameter settings required for service execution. You should change these settings accordingly.
*specifyConcProgParams specifies the default parameter settings required for concurrent program execution. You should change these settings accordingly.
*bizDocMapping maps the incoming price request query parameters to the custom table structure required for the Price Request API. This service uses the following services internally to provide required the data feed during the mapping.
*convertToDateObject is a transformer in the business document mapping. It converts the text date format into object date format.
*setPriceRequestTxn inserts data into the price request custom tables. It takes IData structure data from the business document mapping service.
*execPriceRequestConcProg queries price request data based on query parameters provided and then inserts results into the custom Price Request tables.
*queryPriceRequestTxn queries the custom price request tables and returns the results as a business document sent to the trading partner.
*purgePriceRequestData. After the business document structure is formed, the custom Price Request tables purge.
For more details on query services, see Using Query Services.
Business Document Structure
The structure of priceRequestBizDoc is as follows:
*1.0 REQUEST_LINES
*1.1 LINE_QUALIFIERS
*1.2 LINE_PRICING_ATTRIBUTES
*1.3 LINE_RELATIONS
The structure of priceRequestOutput is as follows:
*1.0 REQUEST_LINES
*1.1 LINE_QUALIFIERS
*1.2 LINE_PRICING_ATTRIBUTES
*1.3 LINE_RELATIONS
*1.4 LINE_DETAILS
*1.4.1 DETAIL_QUALIFIERS
*1.4.2 DETAIL_PRICING_ATTRIBUTES
Note:
Some services use the same field names and column mappings.
Documents using the same tables
For These Documents...
Use the following table:
1.4.1 DETAIL_QUALIFIERS
1.1 LINE_QUALIFIERS
1.4.2 DETAIL_PRICING_ATTRIBUTES
1.2 LINE_PRICING_ATTRIBUTES (If Input, Maps to WM_LINE_TBL)
1.0 REQUEST_LINES
Document Field
In/Out
Oracle Applications Table/View
Column Name
Description
REQUEST_
TYPE_CODE
In/Out
WM_LINE_TBL
REQUEST_
TYPE_CODE
The transaction system that is making the pricing request. An example is ONT for Order Management Order.
HEADER_ID
In/Out
WM_LINE_TBL
HEADER_ID
Unique identifier of the request header in the calling application.
LINE_INDEX
In/Out
WM_LINE_TBL
LINE_INDEX
PL/SQL unique identifier for request line.
LINE_ID
In/Out
WM_LINE_TBL
LINE_ID
Unique identifier of the request line in the calling application.
LINE_
TYPE_CODE
In/Out
WM_LINE_TBL
LINE_
TYPE_CODE
Type of line within the request. Use ORDER or LINE.
PRICING_
EFFECTIVE_
DATE
In/Out
WM_LINE_TBL
PRICING_
EFFECTIVE_
DATE
Date for which the pricing engine calculates the prices.
ACTIVE_
DATE_FIRST
In/Out
WM_LINE_TBL
ACTIVE_
DATE_FIRST
Other than the pricing effective date, you can specify Ordered Date or Ship Date for the pricing engine used to qualify pricing entities.
ACTIVE_
DATE_FIRST_
TYPE
In/Out
WM_LINE_TBL
ACTIVE_
DATE_FIRST_
TYPE
The date type of ACTIVE_DATE_FIRST based on lookup type EFFECTIVE_DATE_TYPES. Examples are NO TYPE, ORD, or SHIP.
ACTIVE_
DATE_
SECOND
In/Out
WM_LINE_TBL
ACTIVE_
DATE_
SECOND
Other than the pricing effective date, you can specify Ordered Date or Ship Date for the pricing engine used to qualify pricing entities.
ACTIVE_
DATE_
SECOND_
TYPE
In/Out
WM_LINE_TBL
ACTIVE_
DATE_
SECOND_
TYPE
The date type of ACTIVE_DATE_FIRST based on lookup type EFFECTIVE_DATE_TYPES. Examples are NO TYPE, ORD, or SHIP.
LINE_
QUANTITY
In/Out
WM_LINE_TBL
LINE_
QUANTITY
Pricing request line quantity,
LINE_UOM_
CODE
In/Out
WM_LINE_TBL
LINE_UOM_
CODE
Pricing request line unit of measure,
UOM_
QUANTITY
In/Out
WM_LINE_TBL
UOM_
QUANTITY
Unit of measure quantity. For example, in service pricing, LINE_UOM_CODE is Months and UOM_QUANTITY is 2. Use this field for service item pricing.
PRICED_
QUANTITY
Out
WM_LINE_TBL
PRICED_
QUANTITY
Quantity of pricing request line that the pricing engine has priced.
PRICED_
UOM_CODE
Out
WM_LINE_TBL
PRICED_
UOM_CODE
Unit of measure that the pricing engine used.
CURRENCY_
CODE
Out
WM_LINE_TBL
CURRENCY_
CODE
Currency that the pricing engine used.
UNIT_PRICE
Out
WM_LINE_TBL
UNIT_PRICE
Base price of the item.
PERCENT_
PRICE
Out
WM_LINE_TBL
PERCENT_
PRICE
Price calculated as a percentage of another item’s price.
ADJUSTED_
UNIT_PRICE
Out
WM_LINE_TBL
ADJUSTED_
UNIT_PRICE
Price per unit after the pricing engine applies discounts and surcharges.
PARENT_
PRICE
Out
WM_LINE_TBL
PARENT_
PRICE
PARENT_
QUANTITY
Out
WM_LINE_TBL
PARENT_
QUANTITY
ROUNDING_
FACTOR
In/Out
WM_LINE_TBL
ROUNDING_
FACTOR
If ROUNDING_FLAG = Y and the pricing event excludes the base price phase, the pricing engine will use this rounding factor.
PARENT_
UOM_CODE
Out
WM_LINE_TBL
PARENT_
UOM_CODE
Unit of measure for the related item when the pricing engine determines the price of an item from another item’s price.
PRICING_
PHASE_ID
In
WM_LINE_TBL
PRICING_
PHASE_ID
Not used.
PRICE_FLAG
In/Out
WM_LINE_TBL
PRICE_FLAG
Indicates the degree to which the price is frozen. Valid values are based on lookup type CALCULATE_PRICE_FLAG. Use:
*Y (Calculate Price) to apply all prices and modifiers to the request line.
*N (Freeze Price. Do not apply any prices or modifiers to the request line. Consider the volume of the request line when processing LINEGROUP modifiers for other lines.
*P (Partial Price) to apply prices and modifiers in phases whose freeze override flag is Y.
PROCESSED_
CODE
Out
WM_LINE_TBL
PROCESSED_
CODE
Internal code that indicates the stage of engine processing when an error occurred.
STATUS_
CODE
In/Out
WM_LINE_TBL
STATUS_
CODE
Returned status. Use:
*N for new record created. Records are successful and returned from the pricing engine.
*X for unchanged. Default status when the line is passed to the pricing engine for processing)
*U for Updated.
*IPL (Invalid price list) for If the passed-in price list is not found, then an error is given.
*GSA for GSA violation.
*FER for error processing formula.
*OER for other error.
*CALC for Error in calculation engine.
*UOM for Failed to price using unit of measure
*INVALID_UOM for Invalid unit of measure.
*DUPLICATE_PRICE_LIST for duplicate price list.
*INVALID_UOM_CONV or Unit of measure conversion not found.
*INVALID_INCOMP for could not resolve incompatibility.
*INVALID_BEST_PRICE or could not resolve best price.
STATUS_
TEXT
Out
WM_LINE_TBL
STATUS_
TEXT
Returned message.
1.1 LINE_QUALIFIERS
Document Field
In/Out
Oracle Applications Table/View Name
Column Name
Description
LINE_INDEX
In/Out
WM_QUAL_TBL
LINE_INDEX
Unique identifier for request line or request line detail.
QUALIFIER_
CONTEXT
In/Out
WM_QUAL_TBL
QUALIFIER_
CONTEXT
Context for qualifier. Example: CUSTOMER: Customer.
QUALIFIER_
ATTRIBUTE
In/Out
WM_QUAL_TBL
QUALIFIER_
ATTRIBUTE
For example, QUALIFIER_ATTRIBUTE2: Customer Name.
QUALIFIER_
ATTR_VALUE_
FROM
In/Out
WM_QUAL_TBL
QUALIFIER_
ATTR_VALUE_
FROM
Value for qualifier attribute. Example: Use Customer ID if QUALIFIER_CONTEXT is CUSTOMER and QUALIFIER_ATTRIBUTE is QUALIFIER_ATTRIBUTE2.
QUALIFIER_
ATTR_VALUE_
TO
Out
WM_QUAL_TBL
QUALIFIER_
ATTR_VALUE_
TO
Return value for qualifier attribute. Populated when the pricing engine returns details of a volume break.
COMPARISON_
OPERATOR_
CODE
Out
WM_QUAL_TBL
COMPARISON_
OPERATOR_
CODE
The pricing engine creates qualifier attributes to indicate to the calling application which qualifier attribute is beneficial. Example: Order Amount > 1000 currency units (where > is the operator code).
VALIDATED_
FLAG
In/Out
WM_QUAL_TBL
VALIDATED_
FLAG
Indicates that a price list or modifier list is valid for the pricing request.
Applicable to price list and modifier list qualifiers; the pricing engine assumes that other qualifiers are valid.
STATUS_
CODE
In/Out
WM_QUAL_TBL
STATUS_
CODE
Return status.
STATUS_
TEXT
Out
WM_QUAL_TBL
STATUS_
TEXT
Return message.
1.2 LINE_PRICING_ATTRIBUTES (If Input, Maps to WM_LINE_TBL)
Field Name
In/Out
Oracle Applications Table/View Name
Column Name
Description
LINE_INDEX
In/Out
WM_LINE_
ATTR_TBL
LINE_INDEX
Unique identifier for request line or request line detail.
PRICING_
CONTEXT
In/Out
WM_LINE_
ATTR_TBL
PRICING_
CONTEXT
Context for a product or pricing attribute. An example is Product Hierarchy.
PRICING_
ATTRIBUTE
In/Out
WM_LINE_
ATTR_TBL
PRICING_
ATTRIBUTE
Product or pricing attribute. An example is PRICING_ATTRIBUTE11: Customer Item ID.
PRICING_
ATTR_VALUE_
FROM
In/Out
WM_LINE_
ATTR_TBL
PRICING_
ATTR_VALUE_
FROM
Value for product or pricing attribute.
PRICING_
ATTR_VALUE_
TO
Out
WM_LINE_
ATTR_TBL
PRICING_
ATTR_VALUE_
TO
Return value for pricing attribute. Populated when the pricing engine returns details of a volume break.
VALIDATED_
FLAG
WM_LINE_
ATTR_TBL
VALIDATED_
FLAG
Not used
STATUS_
CODE
In/Out
WM_LINE_
ATTR_TBL
STATUS_
CODE
Return status.
STATUS_
TEXT
Out
WM_LINE_
ATTR_TBL
STATUS_
TEXT
Return message.
1.3 LINE_RELATIONS (If Input, Maps to WM_RELATED_LINES_TBL)
Document Field
In/Out
Oracle Applications Table/View Name
Column Name
Description
LINE_INDEX
In/Out
WM_RELATED_
LINES_TBL
LINE_INDEX
PL/SQL unique identifier for request line.
RELATIONSHIP_
TYPE_CODE
In/Out
WM_RELATED_
LINES_TBL
RELATIONSHIP_
TYPE_CODE
Type of relationship between pricing lines.
RELATED_
LINE_INDEX
In/Out
WM_RELATED_
LINES_TBL
RELATED_
LINE_INDEX
PL/SQL identifier for related request line.
STATUS_
CODE
Out
WM_RELATED_
LINES_TBL
STATUS_
CODE
Return status code.
STATUS_
TEXT
Out
WM_RELATED_
LINES_TBL
STATUS_
TEXT
Return status text.
1.4 LINE_DETAILS
Document Field
In/Out
Oracle Applications Table/View Name
Column Name
Description
LINE_DETAIL_
INDEX
Out
WM_LINE_
DETAIL_TBL
LINE_DETAIL_
INDEX
PL/SQL unique identifier.
LINE_DETAIL_ID
WM_LINE_
DETAIL_TBL
LINE_DETAIL_ID
Not used.
LINE_DETAIL_
TYPE_CODE
Out
WM_LINE_
DETAIL_TBL
LINE_
DETAIL_
TYPE_CODE
LINE_INDEX
In/Out
WM_LINE_
DETAIL_TBL
LINE_INDEX
Identifier for parent request line.
LIST_HEADER_
ID
Out
WM_LINE_
DETAIL_TBL
LIST_
HEADER_ID
Identifier of the list header that creates or updates the pricing line.
LIST_LINE_ID
Out
WM_LINE_
DETAIL_TBL
LIST_LINE_ID
Identifier of the list line that creates or updates the pricing line.
LIST_LINE_
TYPE_CODE
Out
WM_LINE_
DETAIL_TBL
LIST_LINE_
TYPE_CODE
Line type of the list line that updates the pricing line. Valid values are in the lookup type LIST_LINE_TYPE_CODE from qp_lookups table.
SUBSTITUTION_
TYPE_CODE
WM_LINE_
DETAIL_TBL
SUBSTITUTION_
TYPE_CODE
Not used.
SUBSTITUTION_
FROM
WM_LINE_
DETAIL_TBL
SUBSTITUTION_
FROM
Not used.
SUBSTITUTION_TO
WM_LINE_
DETAIL_TBL
SUBSTITUTION_
TO
Value for terms substitution attribute. Not used.
AUTOMATIC_FLAG
Out
WM_LINE_
DETAIL_TBL
AUTOMATIC_
FLAG
Indicates if the pricing engine should automatically apply the request line detail to the request line. The engine derives the value from the list line.
OPERAND_
CALCULATION_
CODE
Out
WM_LINE_
DETAIL_TBL
OPERAND_
CALCULATION_
CODE
Type of operand. Use:
*Adjustment Percent for discounts.
*Adjustment Amount for discounts.
*Adjustment New Price for discounts.
*UNIT_PRICE for price lists.
*PERCENT_PRICE for price lists.
OPERAND_VALUE
Out
WM_LINE_
DETAIL_TBL
OPERAND_
VALUE
Value of pricing request detail line. An example is 10 currency unit list price with 3 per cent discount.
PRICING_GROUP_
SEQUENCE
Out
WM_LINE_
DETAIL_TBL
PRICING_
GROUP_
SEQUENCE
Indicates the pricing bucket in which the pricing engine applied this list line
PRICE_BREAK_
TYPE_CODE
Out
WM_LINE_
DETAIL_TBL
PRICE_BREAK_
TYPE_CODE
Based on lookup type PRICE_BREAK_TYPE_CODE. Valid values are POINT, RANGE, and RECURRING.
CREATED_FROM_
LIST_TYPE_CODE
Out
WM_LINE_
DETAIL_TBL
CREATED_
FROM_LIST_
TYPE_CODE
List type that creates or updates the pricing line. Use values in the lookup_type LIST_TYPE_CODE from the qp_lookups table.
PRICING_
PHASE_ID
Out
WM_LINE_
DETAIL_TBL
PRICING_
PHASE_ID
The pricing phase which created the request line detail.
LIST_PRICE
WM_LINE_
DETAIL_TBL
LIST_PRICE
Not used.
LINE_QUANTITY
Out
WM_LINE_
DETAIL_TBL
LINE_
QUANTITY
Quantity on the price break line. This field is used if the pricing engine derived the value of either the request line or the request line detail from a price break. This field is used to indicate that this particular break line was used in the calculation.
ADJUSTMENT_
AMOUNT
Out
WM_LINE_
DETAIL_TBL
ADJUSTMENT_
AMOUNT
The value of the bucketed adjusted amount for line types such as PLL, DIS, and SUR. For price break (PBH) child lines, the field is populated if the pricing engine derived the value of the request line or request line detail from a price break.
APPLIED_FLAG
Out
WM_LINE_
DETAIL_TBL
APPLIED_FLAG
The lists or list lines that this pricing event or a prior pricing event applied. Use:
*Yes: if the attribute context is a list or list line.
*No: if the attribute context is a list or list line.
*Null.
MODIFIER_
LEVEL_CODE
Out
WM_LINE_
DETAIL_TBL
MODIFIER_
LEVEL_CODE
The level for the list line that qualified for the transaction. Based on lookup type MODIFIER_LEVEL_CODE.
STATUS_CODE
Out
WM_LINE_
DETAIL_TBL
STATUS_
CODE
Returned status. Use the value N for New Record Created which returns all new records from the pricing engine as successful.
STATUS_TEXT
Out
WM_LINE_
DETAIL_TBL
STATUS_
TEXT
Returned message.
SUBSTITUTION_
ATTRIBUTE
Out
WM_LINE_
DETAIL_TBL
SUBSTITUTION_
ATTRIBUTE
Modifier details. The attribute in the TERMS context that the pricing engine substituted. An example is Payment Terms for Term Substitution-type modifiers.
ACCRUAL_FLAG
Out
WM_LINE_
DETAIL_TBL
ACCRUAL_
FLAG
Indicates whether the discount is an accrual.
LIST_LINE_NO
Out
WM_LINE_
DETAIL_TBL
LIST_
LINE_NO
Modifier number. This field applies when there is a Coupon Issue type of modifier line.
ESTIM_GL_VALUE
Out
WM_LINE_
DETAIL_TBL
ESTIM_GL_
VALUE
The discount or surcharge value of the modifier. Estimates the discount cost for non-monetary modifiers.
ACCRUAL_
CONVERSION_RATE
Out
WM_LINE_
DETAIL_TBL
ACCRUAL_
CONVERSION_
RATE
The rate to convert a non-monetary accrual to a monetary value.
OVERRIDE_FLAG
Out
WM_LINE_
DETAIL_TBL
OVERRIDE_
FLAG
Indicates whether a user in the calling application can override the modifier value.
PRINT_ON_
INVOICE_FLAG
WM_LINE_
DETAIL_TBL
PRINT_ON_
INVOICE_FLAG
Not used.
INVENTORY_
ITEM_ID
Out
WM_LINE_
DETAIL_TBL
INVENTORY_
ITEM_ID
Inventory item identifier in an item relationship. For list line type Item Upgrade.
ORGANIZATION_ID
Out
WM_LINE_
DETAIL_TBL
ORGANIZATION_
ID
Organization identifier in an item relationship. For list line type Item Upgrade.
RELATED_ITEM_ID
Out
WM_LINE_
DETAIL_TBL
RELATED_
ITEM_ID
Related inventory item identifier in an item relationship. For list line type Item Upgrade.
RELATIONSHIP_
TYPE_ID
Out
WM_LINE_
DETAIL_TBL
RELATIONSHIP_
TYPE_ID
Relationship type identifier in an item relationship. For list line type Item Upgrade.
ESTIM_ACCRUAL_
RATE
Out
WM_LINE_
DETAIL_TBL
ESTIM_
ACCRUAL_
RATE
Indicates the percentage to accrue and the expected rate of redemption of a coupon. The liability is ACCRUAL OR COUPON VALUE ESTIM_ACCRUAL_RATE. Default value: 100.
EXPIRATION_
DATE
Out
WM_LINE_
DETAIL_TBL
EXPIRATION_
DATE
The expiration date of the accrual or coupon.
BENEFIT_PRICE
_LIST_LINE_ID
Out
WM_LINE_
DETAIL_TBL
BENEFIT_
PRICE_LIST_
LINE_ID
The list price before promotional discount. For Promotional Goods-type modifiers when the pricing engine creates a new transaction line.
RECURRING_FLAG
WM_LINE_
DETAIL_TBL
RECURRING_
FLAG
Not used.
BENEFIT_LIMIT
WM_LINE_
DETAIL_TBL
BENEFIT_LIMIT
Not used.
CHARGE_
TYPE_CODE
Out
WM_LINE_
DETAIL_TBL
CHARGE_
TYPE_CODE
Indicates the type of charge based on lookup type FREIGHT_CHARGES_TYPE. Used for Freight or Special Charge-type modifiers.
CHARGE_
SUBTYPE_CODE
Out
WM_LINE_
DETAIL_TBL
CHARGE_
SUBTYPE_
CODE
Indicates the type of charge based on lookup type CHARGE_TYPE_CODE.
INCLUDE_ON_
RETURNS_FLAG
Out
WM_LINE_
DETAIL_TBL
INCLUDE_ON_
RETURNS_FLAG
Indicates whether the pricing engine should include the charge on a return transaction. For Freight or Special Charge-type modifiers.
BENEFIT_QTY
Out
WM_LINE_
DETAIL_TBL
BENEFIT_QTY
The accrual quantity for non-monetary accruals or item quantity for promotional goods.
BENEFIT_
UOM_CODE
Out
WM_LINE_
DETAIL_TBL
BENEFIT_
UOM_CODE
The accrual unit of measure for non- monetary accruals or item unit of measure for promotional goods.
PRORATION_
TYPE_CODE
WM_LINE_
DETAIL_TBL
PRORATION_
TYPE_CODE
Not used.
SOURCE_
SYSTEM_CODE
WM_LINE_
DETAIL_TBL
SOURCE_
SYSTEM_
CODE
Not used.
REBATE_
TRANSACTION_
TYPE_CODE
WM_LINE_
DETAIL_TBL
REBATE_
TRANSACTION_
TYPE_CODE
Not used.
SECONDARY_
PRICELIST_IND
Out
WM_LINE_
DETAIL_TBL
SECONDARY_
PRICELIST_
IND
Indicates that the pricing used a secondary price list instead of the calling application’s price list.