Oracle Pricing Table list

QP_LIST_HEADERS_B
QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions.

QP_LIST_LINES
QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B. This table stores all types of list lines; price list lines, all types of modifiers including price modifier list lines used to derive factors. The different types of list lines are based on Lookup Type, 'LIST_LINE_TYPE_CODE'.

QP_PRICING_ATTRIBUTES
QP_PRICING_ATTRIBUTES stores product information and pricing attributes. The PRODUCT_ATTRIBUTE and PRODUCT_ATTRIBUTE_VALUE columns identify the product or level in the item hierarchy, i.e item context at which the price or modifier is set. The PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE and PRICING_ATTR_VALUE_FROM columns store the pricing attributes which further define what is being priced. If the PRICING_ATTRIBUTE_CONTEXT is VOLUME the pricing attributes column stores the break unit attribute, e.g. item quantity and both the PRICING_ATTR_VALUE_FROM and PRICING_ATTR_VALUE_TO columns can be populated. The Product information is repeated for all pricing attributes. A record is always created for the VOLUME context which may or may not have an attribute defined.
Tables 

Qp_interface_list_header  
qp_interface_list_lines  
qp_interface_qualifiers  
qp_interface_pricing_attribs  
qp_interface_errors  
qp_list_headers_b  
qp_list_headers_tl  
qp_list_lines  
qp_qualifiers  
qp_pricing_attributes  
qp_rltd_modifiers

Relationship

QP_LIST_HEADERS_B  <---LIST_HEADER_ID ---> QP_LIST_LINES

QP_LIST_LINES <---LIST_HEADER_ID,LIST_LINE_ID ---> QP_PRICING_ATTRIBUTES

QP_PRICING_ATTRIBUTES <---PRODUCT_ATTR_VALUE <> TO_CHAR(MSI.INVENTORY_ITEM_ID)---> mtl_system_items_b

Useful Queries

 

    /* Formatted on 7/18/2014 11:02:10 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b
WHERE list_header_id IN (SELECT list_header_id
                         FROM qp_list_headers_tl
                         WHERE name = 'Corporate');           --Price List Name
    
/* Formatted on 7/18/2014 11:02:04 AM (QP5 v5.115.810.9015) */
SELECT line.*
FROM qp_list_lines line, qp_list_headers_b header
WHERE line.list_header_id = header.list_header_id
      AND line.list_header_id IN (SELECT list_header_id
                                  FROM qp_list_headers_tl
                                  WHERE name = 'Corporate'); --Price List Name
    
    
/* Formatted on 7/18/2014 11:01:59 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE     spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.list_header_id IN (SELECT list_header_id
                                 FROM qp_list_headers_tl
                                 WHERE name = 'Corporate');--Price List Name
    
/* Formatted on 7/18/2014 11:01:53 AM (QP5 v5.115.810.9015) */
SELECT qpa.*
FROM qp_list_headers_b spl,
     qp_list_lines spll,
     qp_pricing_attributes qpa,
     mtl_system_items_b msi
WHERE     msi.organization_id = 244
      AND msi.inventory_item_id = 434257
      AND spl.list_header_id = 164075
      AND spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.product_attribute_context = 'ITEM'
      AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
      AND qpa.product_attr_value = TO_CHAR (msi.inventory_item_id)
      AND qpa.product_uom_code = msi.primary_uom_code
      AND qpa.pricing_attribute_context IS NULL
      AND qpa.excluder_flag = 'N'
      AND qpa.pricing_phase_id = 1;
    /* Formatted on 7/18/2014 11:01:45 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b
WHERE list_header_id IN (SELECT list_header_id
                         FROM qp_list_headers_tl
                         WHERE name = 'Corporate');                --Price List Name
    
/* Formatted on 7/18/2014 11:01:35 AM (QP5 v5.115.810.9015) */
SELECT line.*
FROM qp_list_lines line, qp_list_headers_b header
WHERE line.list_header_id = header.list_header_id
      AND line.list_header_id IN (SELECT list_header_id
                                  FROM qp_list_headers_tl
                                  WHERE name = 'Corporate'); --Price List Name
    
    
/* Formatted on 7/18/2014 11:01:29 AM (QP5 v5.115.810.9015) */
SELECT *
FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
WHERE     spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.list_header_id IN (SELECT list_header_id
                                 FROM qp_list_headers_tl
                                 WHERE name = 'Corporate');       --Price List Name

  /* Formatted on 7/18/2014 11:01:23 AM (QP5 v5.115.810.9015) */
SELECT qpa.*
FROM qp_list_headers_b spl,
     qp_list_lines spll,
     qp_pricing_attributes qpa,
     mtl_system_items_b msi
WHERE     msi.organization_id = 244
      AND msi.inventory_item_id = 434257
      AND spl.list_header_id = 164075
      AND spll.list_header_id = spl.list_header_id
      AND qpa.list_header_id = spl.list_header_id
      AND spll.list_line_id = qpa.list_line_id
      AND qpa.product_attribute_context = 'ITEM'
      AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
      AND qpa.product_attr_value = TO_CHAR (msi.inventory_item_id)
      AND qpa.product_uom_code = msi.primary_uom_code
      AND qpa.pricing_attribute_context IS NULL
      AND qpa.excluder_flag = 'N'
      AND qpa.pricing_phase_id = 1;

Forms Personalization in Oracle Apps R12 Example: Zoom Functionality

Forms Personalization in Oracle Apps R12 Example: Zoom Functionality

Forms Personalization in Oracle Apps R12 Example: Zoom Functionality

In this post, I tried to explain the example implementation of a Zoom Functionality in R12

Instance: R12.1.1

Requirement:  

1.       Add an option named “Responsibility Details” to Tools Menu of the “APPLICATION USERS” Form.

2.       By clicking the option said in requirement 1, the user must be able view the complete details of the selected responsibility via  “DEFINE: RESPONSIBILITIES” forms

3.       Independent  Functionalities  of “USERS” and “RESPONSIBILITIES” FORM should not get impacted

Implementation Steps:

Step1: Open the “Define User” Form from “SYSTEM ADMINISTRATOR” Responsibility

Step2: Open the personalization form by following the navigation shown below

  

Step3: Add a Menu Entry under “Tools” Option with the name “Responsibility Details”.

Step 3.1: Add a new Form rule named “Add and Enable a Special Menu under Tools Menu” with the triggering event as “WHEN-NEW-FORM-INSTANCE” as shown below,

Step 3.2: Add an action to create an entry under Tools menu

Step 4:  Create and Initialize the GLOBAL_VARIABLE.XX_RESP_NAME with the “Responsibility name” and call the function corresponding to “Define: Responsibilities” Form i.e. FND_FNDSCRSP

Step 4.1: Add a rule with the triggering event “SPECIAL1” with the condition “:USER_RESP.RESPONSIBILITY_NAME IS NOT NULL” as given below

Step 4.2: Add an action to create and initialize a global variable with the Responsibility name i.e. Like “Application Developer”

Step 4.3: Add an action to launch the function corresponding to “Define: Responsibilities” form as below

Step 5: Invoke the Personalization form of “Responsibilities” form and perform the action to initialize “INITIAL VALUE” of the global variable “XX_RESP_NAME” as NULL. This action is to maintain the normal functionality of “Responsibilities Form”.

STEP 5.1: Invoke personalization form of “Responsibilities Form” as below

Step 5.2: Add a rule to create a global variable if not exists and assign a NULL to its “INITIAL VALUE” 

  Step 5.3: Add an action to navigate to the block “RESPONSIBILITY”

  

STEP 6: Add a rule to perform the below actions with the triggering event as “WHEN-NEW-BLOCK-INSTANCE” of “RESPONSIBILITY” block and triggering condition as “:GLOBAL.XX_RESP_NAME IS NOT NULL”.

1. Change the “Default where” Property of the responsibility to restrict the query to single responsibility
2. Run the built in “EXECUTE_QUERY”
3. Nullify the global variable “XX_RESP_NAME”  

  STEP 6.1: Change the “Default where” Property of the responsibility to restrict the query to single responsibility

 Step 6.2: Run the built in “EXECUTE_QUERY” to perform auto query

 Step 6.3: Create a action to nullify the global variable

 Testing of Our Implementation:

 Navigation:

Testcase 1: “Responsibility Details” Option Disabled during Enter Query Mode

 Test case 2: “Responsibility Details” Option Disabled When cursor is not in the block displaying “Responsibility” Details

                              

 Test case 3: “Responsibility Details” Option enabled When cursor is in the block displaying “Responsibility” Details

 Test case 4: By clicking the added option “Responsibility Details” the user must be able to see the responsibility details via responsibility form

For example: The cursor is in the field Responsibility with the value “Application Developer” in the user form as shown in the above screenshot. So if the user clicks the  “Responsibility Details” option, it should auto navigate to responsibilities form and auto query for the responsibility name “Application Developer”

 Test case 5: Normal Functionality of the responsibilities form should not got impacted like “Running a Query” , “Creating a Responsibility” etc  because of this forms personalization

I hope, I am done with my explanation... 

Forms Personalization in Oracle Apps Overview

Forms Personalization in Oracle Apps Overview

Personalization form automatically queries the function, form and pre-definedpersonalization rules, if any exists for the specific form. For example, the form name is INVTOMAI i.e. Move Order form on which the personalization form is opened.

Personalization Form

The Personalization form mainly contains four sections.

• Rules
• Conditions
• Context
• Actions

For each function (a form running in a particular context based on parameters passed to it), we can specify one or more Rules. Each Rule consists of an Event, an optional Condition, the Scope for which it applies, and one or more Actions to perform.

Rules

Rules administer the personalization needs to be implemented on the form. Each rule contains a sequence number, description and level (Rules may now be specified as acting either at the Function level (the default) or at the Form level). The rule can be activated or de-activated using the “Enabled” checkbox. The rule can be deleted when no longer needed.

Defining rules doesn’t identify, when the rule should get evaluated or applied. For each rule, there should be conditions attached, which power the execution of the rule.

Conditions

Conditions decide the event the rule to be executed. Each condition mainly contains three sections i.e. Trigger Event, Trigger Object and Condition.

Condition Tab with Trigger events

Trigger Event specifies the occurrence where the rule should be executed.

Trigger Object is the object on the form to determine at what level the rule should be executed.  The value can be “” or “”

Condition is an optional SQL code fragment that is evaluated when the Event occurs; if it evaluates to TRUE then the Actions are processed.

Processing Mode is a pop-list which controls when the Rule should be processed i.e., Rules can be applied while not in Enter-Query mode (the default), only in Enter-Query mode, or in both modes.

Context

Context manages to whom the personalization should apply. This is similar to the concept of using profile options in Oracle Applications. The various levels are Site, Responsibility, Industry and User. During runtime, the values provided in the context are evaluated andpersonalization rules will be applied. Usage of context is very vital in implementing thepersonalization to prevent the inappropriate users accessing these customizations of the form.

Context levels

While saving a Rule, if no Context rows have been entered the form will automatically create a row at the Site level.

Actions

Actions decide the exact operation to be performed when the event occurs and the condition evaluates to true during the runtime.

Each Action consists of one of the following:

• setting a Property
• displaying a Message
• executing a Builtin
• enabling a Menu/Special entry

Action Types

Each action contains a sequence number, description and language. Actions can be enabled, disabled or deleted. Based on the action type selected, the fields on the right sector of the actions tab will be refreshed, where the action parameters are entered.

Insert 'Get' Expression… button can be used to automatically construct the expression.

Insert Item Value… button can be used to consider the item name along with colon (:)

Example :TOMAI_MAIN_HEADER_BLK.REQUEST_NUMBER

Validate used to test if the syntax of your string is valid. If the evaluation fails, the processing engine will return an ORA error as if the string had been part of a SQL expression. Otherwise, it will display the text exactly as it would appear at runtime in the current context.

Apply Now used to apply the changes immediately to the target form to test its effect

Property

The action type “Property” is used to set the properties of the objects. The various objects include “Item, Window, Block, Global Variable etc”.

Property Type 

Select By Text button used to select an object based on text appearing on the screen atthe point in time, the Personalization form is invoked, including any changes that current rules might have performed.  Target Object can be selected from the list.

Target Object, either name defined in the form or variable name should be entered depending on the object type selected.

Property Name is the property to be changed.

Value is the new desired value for the property.

The current value of the property will be displayed when clicked on the Get Valuebutton.

SQL functions and operators can be used to assign value. Any value started with “=” operator will be evaluated at runtime otherwise the value is treated as is entered in the value field.

Message

The action type “Message” is used to display custom messages during runtime. MessageType and Message Text should be entered after selecting the action type as “Message”. The available message types are “Show, Hint, Error, Debug and Warn”.

 Message Type

Builtin

The action type “Builtin” is used to execute the form and AOL functions. Depending on the builtin type selected, the arguments should be entered.

Builtin Type

Add Parameter… list of Values displays currently used parameters. Applies to the builtin ‘Launch a Function’ only.

Menu

The action type “Menu” is used to activate the available special menus on the Tools menu.  Oracle Applications provide 45 special menus under Tools menu which can be used by customers based on their requirements.  The menus are arranged in 3 sets of 15 each under the Tools, Reports and Actions pull-down menus. If you select a menu that the base form is already using, your functionality will override the form's functionality. 

Select the Menu Entry which is not used by the form.

Menu Label is the prompt which appears to the users when Tools menu is invoked.

Icon name is the .ico file name.

Enabled in Block(s) specifies the blocks for which the special menu should beactivated.

Menu Type

A separator can be created above the activated special menu by selecting the “Render line before menu” checkbox.

This property can be used only in WHEN-NEW-FORM-INSTANCE event.

Calling Concurrent Program from Form Personalization

Run Concurrent Program from Form: Launch SRS Built In

Form Personalization to run Concurrent Program from Form 

Built-in : Launch SRS Program.

Example :

Our Requirement is to Run Production Batch Sample Label from Production Batch Form.

We could completed this requirement through Form Personalization in below steps.

A)-Define 1 Menu : Special Menu for the Report, that shall show in tools Menu bar.

B)-Attach Concurrent Program menu with this Special Menu (defined in the above step).

C)- Store batch id ( For parameter to run your concurrent) in one Global variable.

D)- In Concurrent Definition window , Assign Global variable value to the Parameter for Batch id.

Here are the screen shots.

Requirement is like Below.

Goto Help --> Diagnostics --> Custom Code --> Personalize.

Add 1 New Sequence for You personalization, and Select Event as When-New-Form-Instance.

Goto Actions, and Choose any 1 special Menu.

Once menu is define, we need to attach Concurrent program to this menu.

Create new Sequence, for attaching The concurrent program with special menu defined in above step.

Goto Actions, here we need to define 2 Actions

1)- Global Variable to store Batch ID for Parameter.

2)- Attach Concurrent program to the Special Menu.

Global Variable name should be Logical and unique, as this needs to be pass to your concurrent program.

Save your work, Till here personalization is done for Concurrent program is attached and run, now Concurrent program will be run , but we have to assign Parameters manually, it will not pick any parameter automatically.

Step-3, To pass parameters from Batch form automatically.

GO TOà System adminà applicationà Concurrentà define

Now we have to attach Global variable from above steps to Concurrent Program.

Go to Parameters, and then pass global variable from the earlier form i.e, Batch Form.

Select Batch id parameter.

Its Default Type Should be SQL Statement.

Then Pass the 

“select :GLOBAL.XX_BATCH_ID from dual”

Now try to run report from Batch form,

It will automatically pass your current (Batch opened at form) to the report.