1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to extract Setup Fields

Discussion in 'Oracle Apps Technical' started by robin, Oct 18, 2012.

  1. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Hi Gurus,

    I am trying to extract fileds for the setups that are created.

    For example,
    SETUP NAME : Define Sales persons
    SETUP : OM>Setup>Sales>Salespersons

    I am navigating here(OM>Setup>Sales>Salespersons).Then I am quering on this and I have taken the view name from Help->Diagnostics->Examine.But I am not getting all the fields.I guess some fields must be from other tables or views.I would like to get those fields as well.I dont know which tables to look at.I checked the view definition in Sql developer,checked constraints for columns.I see only check constraints defined on some of the columns.I donot have TOAD.I am stuck here.Please throw light on this.I want to get all the fields.


    Thanks in advance,
    Robin
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Robin,

    Just look into the view for what tables they used. By that we can query all fields from those tables what we get from view. Meanwhile I will try to finish the query on that form for you and post it here.
     
  3. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Hi Bharat,

    I have already tried that.I have found the fields for some setups like this.But for some setups I was unable to extract all the fields using the tables of the view.By the way in some setups I have observed that quering using Help->Diagnostics->Examine and the record history shows different tables.What's the difference here ? I thought these two should point to the same table.
    And is there any way to find the primary key of a table by just looking at the form ?
    I mean if I am opening a setup lets say System Parameters can I know if any of the fields here has a primary key ?

    Thanks
    Robins
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    In the examine you just see what they look in front end. Sometimes they may match with database columns but not all the times. And more over in examine it won't show any table names there right? There in examine we can see just column name, that too for front end. But all the time it wont match the column names to backend.

    Its not easy to identify primary key of a table but just looking into the forms. If we are aware of those things just like if we are familiar with Sales order form then we can identify the primary key as header_id in oe_order_headers_all table.

    In that record history also it won't show all tables what form basically rely on. We need to search table names based on our field requirement. If we want to know Order type in sales order form then it wont show order type table in record history. It shows oe_order_headers_v table only. in that it contains type id. We need to search the table using fnd_tables and in that we need to find out appropriate one for our requirement. As said earlier for order type we can see this field in OE_TRANSACTION_TYPES_TL table.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Robin,

    It is rare that, Not all the fields are not fetched from the database. Some fields are calculated which their properties may be Non-database item. Of course It is very hard to identify from which table/view it was pulled, talk with functional consultant and understand the functionality of the field and work relatively.

    Cheers..
     
  6. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Bharat and Kiran,

    Thanks for your reply.

    When I looked into the view from Record History,I see lot of tables in that,perhaps all the tables that are used to get the value from backend.

    For example, the view OE_ORDER_LINES_V contains the following Tables :

    mtl_parameters ship_from_org,
    hz_cust_site_uses_all ship_su,
    hz_party_sites ship_ps,
    hz_locations ship_loc,
    hz_cust_acct_sites_all ship_cas,
    hz_cust_site_uses_all bill_su,
    hz_party_sites bill_ps,
    hz_locations bill_loc,
    hz_cust_acct_sites_all bill_cas,
    hz_parties party,
    hz_cust_accounts cust_acct,
    hz_cust_account_roles ship_roles,
    hz_parties ship_party,
    hz_org_contacts ship_cont,
    hz_relationships ship_rel,
    hz_cust_accounts ship_acct,
    ar_lookups ship_arl,
    hz_cust_account_roles invoice_roles,
    hz_parties invoice_party,
    hz_org_contacts invoice_cont,
    hz_relationships invoice_rel,
    hz_cust_accounts invoice_acct,
    ar_lookups invoice_arl,
    oe_order_headers_all h,
    oe_order_lines_all l,
    qp_list_headers_tl pl,
    qp_list_headers_b pl1,
    ra_rules accrule,
    ra_rules invrule,
    ra_terms_tl term,
    oe_transaction_types_tl lt


    And in examine,when you query like this :

    Block : SYSTEM
    Field : LAST_QUERY

    You will get an Sql query in the value field.When I queried that query on Sql developer,I got most of the fields .However,some sql queries(from the value field) donot display any data and some of them donot have any rows in them as well i.e count(*) is 0.This is what I observed.


    Anyways,I completed the task with the help of functional team.Any more information on this would be very helpful.




    Cheers,
    Robin
     
  7. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad

    The fnd_tables donot have any column that describes about the field.So how can we know the table we are looking for ?
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    In fnd_tables we have table_name field. By using this field we can search the tables what we have in our database.

    Code (Text):

    SQL> select table_id, table_name from fnd_tables where table_name like 'OE_ORDER%HEADER%';

    output: -
    TABLE_ID  TABLE_NAME
    --------- --------------------
    71209     OE_ORDER_HEADERS_ALL
    79275     OE_ORDER_HEADERS_ALL_EFC
    88015     OE_ORDER_HEADER_HISTORY

     
    This will returns the result with certain table names which are going to start with OE_ORDER and % tells there may contain any values and then continuously having HEADER and further with any values or not.

    By this we can search table names.

    If we want to search columns then the use the table fnd_columns where we can see columns.

    Code (Text):

    SQL> select table_id,column_id, column_name from fnd_columns where column_name like '%STATUS%COD%';

    output: -
    table_id column_id  column_name
    -------- ---------  -------------
    111055   812306     EVALUATION_SET_STATUS_CODE
    111092   813114     ASSESSMENT_STATUS_CODE
    69817    390441     PROCESSING_STATUS_CODE
    69817    390444     TRANSACTION_STATUS_CODE
    112991   855250     CODE_COMBINATION_STATUS_CODE
    112991   855256     ACCOUNTING_ENTRY_STATUS_CODE

    and so on

     
    This will return the results with certain column names which are going to start with any value and having STATUS continuously with them and then continuously may having some other values or not and so on as based on our searching criteria.

    Now based on column names also we can identify the tables as shown table_id in fnd_columns query. We can join the table_id from fnd_columns table to fnd_tables table.
    Code (Text):

    SQL> SELECT TABLE_NAME
    FROM FND_TABLES FT
        ,FND_COLUMNS FC
    WHERE FT.TABLE_ID = FC.TABLE_ID
      AND FC.COLUMN_NAME LIKE 'FLO%STATUS%COD%';

    output: -
    TABLE_NAME
    -----------------------------
    OE_LINE_ACKS
    OE_ORDER_HEADERS_ALL
    OE_ORDER_LINES_HISTORY
    MRP_ATP_SCHEDULE_TEMP
    OE_HEADER_ACKS
    OE_ORDER_LINES_ALL
    ASO_ER_ORD_HDRS_FACT
    OE_BLANKET_HEADERS_ALL
    OE_BLANKET_LINES_HIST
    OE_BLANKET_LINES_ALL
    OE_BLANKET_HEADERS_HIST
    OE_ORDER_HEADER_HISTORY
    CSD_FLOW_STATUSES_B
    OE_FLOW_STATUS_AIA_SYNC

     
    This is how we can query the required columns and tables from fnd_columns and fnd_tables.
     
  9. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Hi Bharath,

    Instead of using fnd_tables and fnd_columns you can use all_tab_cols to get the table name as well as column name.

    Code (SQL):

    SELECT TABLE_NAME,column_name FROM all_tab_cols WHERE LOWER(TABLE_NAME) LIKE '%oe_order_hea%';
     
    This will produce the following output :


    Code (SQL):


    OE_ORDER_HEADERS_ALL    ORDER_FIRMED_DATE
    OE_ORDER_HEADERS_ALL    IB_INSTALLED_AT_LOCATION
    OE_ORDER_HEADERS_ALL    IB_CURRENT_LOCATION
    OE_ORDER_HEADERS_ALL    IB_OWNER
    OE_ORDER_HEADERS_ALL    END_CUSTOMER_SITE_USE_ID
    OE_ORDER_HEADERS_ALL    END_CUSTOMER_CONTACT_ID
    OE_ORDER_HEADERS_ALL    END_CUSTOMER_ID
    OE_ORDER_HEADERS_ALL    MINISITE_ID
    OE_ORDER_HEADERS_ALL    CUSTOMER_SIGNATURE_DATE
    OE_ORDER_HEADERS_ALL    CUSTOMER_SIGNATURE
    OE_ORDER_HEADERS_ALL    SUPPLIER_SIGNATURE_DATE
    OE_ORDER_HEADERS_ALL    SUPPLIER_SIGNATURE
    OE_ORDER_HEADERS_ALL    SOLD_TO_SITE_USE_ID
    OE_ORDER_HEADERS_ALL    SOURCE_DOCUMENT_VERSION_NUMBER
    OE_ORDER_HEADERS_ALL    DRAFT_SUBMITTED_FLAG
    OE_ORDER_HEADERS_ALL    USER_STATUS_CODE
    OE_ORDER_HEADERS_ALL    QUOTE_DATE
    OE_ORDER_HEADERS_ALL    QUOTE_NUMBER
    OE_ORDER_HEADERS_ALL    SALES_DOCUMENT_NAME
    OE_ORDER_HEADERS_ALL    TRANSACTION_PHASE_CODE
    OE_ORDER_HEADERS_ALL    DEFAULT_FULFILLMENT_SET
    OE_ORDER_HEADERS_ALL    LINE_SET_NAME
    OE_ORDER_HEADERS_ALL    FULFILLMENT_SET_NAME
    OE_ORDER_HEADERS_ALL    SOLD_TO_PHONE_ID
    OE_ORDER_HEADERS_ALL    SALES_DOCUMENT_TYPE_CODE
    OE_ORDER_HEADERS_ALL    BLANKET_NUMBER
    OE_ORDER_HEADERS_ALL    ACCOUNTING_RULE_DURATION
    OE_ORDER_HEADERS_ALL    XML_MESSAGE_ID
    OE_ORDER_HEADERS_ALL    BATCH_ID
    OE_ORDER_HEADERS_ALL    PRICE_REQUEST_CODE
     


    Thanks,
    Robin
     
  10. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yes Robin, We can use all_tab_cols instead of them.