How to obtain value_from and value_from_meaning from oracle canned applications' query

Posted on 2012-08-28
Last Modified: 2012-09-14
Hey experts.

I hope you are doing great.

I am working in an oracle forms builder's form, QPXPRMLS.fmb to be more specific. There are several queries (views) involved in this form. There is a canvas called QUALIFIER_FIXED, which shows several columns. I intend to leverage these queries for creating a report of the price modifiers existing in the database, but whe it comes to showing the QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE, VALUE_FROM as well as the VALUE_FROM_MEANING, I have no clue where those values come from, as they are related to some LOV. I hope you can provide me with an easy query to extract these values from the tables they are stored.

I have resolved the part for the HEADERS, the MODIFIER LINES, the PRICE_BREAK attributes and so far, I have this query for the QUALIFIERS:

SELECT qualifier_grouping_no "Grouping Number", qualifier_context "Qualifier Context"
from qp_qualifiers_v where list_header_id = 8225 order by qualifier_grouping_no ;  (8225 is just hardcoded for testing purposes)

For example, the query is returning the qualifier context as shown in the embedded image, but I need it to be Modifier List instead of MODLIST. I assume this is extracted from somewhere in the database, and wouldn't want to have to hardcode it.

Please ask for any clarification as required.

Example result obtained from the described query.
I also know how to obtain the value_from and the value_from_meaning but it is returning me values like QUALIFIERATTRIBUTE4, QUALIFIERATTRIBUTE2. I hope you can provide me a query to translate these values into more readable data.

Thank you very much in advance for your attention and interest.
Question by:mhas01
    LVL 20

    Accepted Solution

    qp_qualifiers_v  is either a table, a view of synonym

    select object_type from all_objects where object_name = 'QP_QUALIFIERS_V'

    if object_type is TABLE  you can update the row to 'Modifier List' , but be careful the value of MODLIST could be used in many other places and thus changing it might result in troubles within other functionality.

    if object_type is VIEW

    select TEXT from all_views where view_name = 'QP_QUALIFIERS_V'
    you should be able to determine the views or tables this view is based on

    if object_type is SYNONYM

    select * from all_synonyms where synonym_name = 'QP_QUALIFIERS_V'
    the result is showing what view or table the synonym refers to

    search  in forms pl/sql for "value_from" (or in the record_groups)  and check where the result is stored: anothers query problably uses that value to get meaningful results

    Author Comment

    Regarding your suggestion on MODLIST, what I mean is that in the form where it is captured it actually says MODIFIER LIST, but in the view that I am querying it says MODLIST, so what I want is to know where to pull the MODIFIER LIST value from. By the way, MODLIST and CUSTOMER are only example codes, since there are lots of other codes, I want to find the relationship between these codes and the actual value.

    On the other side, actually I know that QP_QUALIFIERS_V is a VIEW, but the base tables don't expose this value either. I think it is based in a relationship stated in the form somehow, and that is actually why I am asking this question.

    Searching in the form I was not able to successfully find this value, and that is other reason why I asked in this forum. Don't get me wrong please, I really appreciate your question but it has not leaded me to the resolution. Thank you anyway. Please don't hesitate to reply back if my comments make any sense or not, to clarify. I really appreciate it.

    Author Comment

    Other consideration, this form is part of the E-business suite of ORACLE, and also part of the ADVANCED PRICING module, so it you are familiar with this module, it might be easier. Thanks.
    LVL 20

    Assisted Solution

    That would indeed be easier and if anyone can make a better assist : go for it.

    Until then some other hints :
    If 'Modifier List' is shown in the LOV itself then it should come from the record-group it is linked with (check pl/sql code for Set_LOV_Property(...,GROUP_NAME,... for runtime changes of that group)
    else there has to be a translation
    Did you  track the destination items of  'Qualifier Context' in the LOV ?
    It is in the 'column mapping properties' of the LOV  .
    Once shown : click on the column  'Qualifier Context'  the return item gives you the item 'MODLIST' is assigned to, same for column 'Grouping number'
    Check for the names of both  items in the pl/sql code to find the code piece for the translation.
    (Apart from a when_validate_item or post_change trigger on those items , I expect the translation also to be part of the post_query trigger on the block)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now