[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-28
4
Medium Priority
?
1,124 Views
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.
0
Comment
Question by:mhas01
  • 2
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
flow01 earned 1500 total points
ID: 38342194
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


QUALIFIERATTRIBUTE
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
0
 

Author Comment

by:mhas01
ID: 38348174
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.
0
 

Author Comment

by:mhas01
ID: 38348176
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.
0
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 1500 total points
ID: 38351068
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)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month18 days, 5 hours left to enroll

829 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