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.