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!

Table Value Sets

Discussion in 'SQL PL/SQL' started by kiran.marla, Jul 20, 2010.

  1. kiran.marla

    kiran.marla Forum Genius

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

    My requirement is to pull data between two dates in Oracle Apps.

    Ex: I want to display invoice data between date1 and date2.

    for that i created table type value set for date1 and date2.
    date1 contains all the dates of invoice_date of ap_invoices_all, and the thing is that date1 contains duplicate values.
    date2 contains dates less than date1, by default is disabled, is enabled when date1 is selected.


    problem is that when i run the concurrent program with using these value sets as parameters, these dates contains duplicate values and displaying a message date is not unique.

    How to get unique values in the table type value set or
    How to solve this problem.

    Thanks in advance
    Kiran Marla
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Better you give us the test case so the requirement will be well-explained.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Ok.

    Take GL_JE_HEADERS table from GL. Let say columns JOURNAL_NAME, CURRENCY_CODE, SET_OF_BOOKS_ID.
    Requirement is to display the JOURNAL_NAME within the date range. i.e FROM_DATE to TO_DATE. There is no columns like FROM_DATE and TO_DATE in GL_JE_HEADERS. So take DEFAULT_EFFECTIVE_DATE as parameter. i.e. FROM_DATE to TO_DATE. Create 2 table value sets for FROM_DATE and TO_DATE taking DEFAULT_EFFECTIVE_DATE as source.
    Retrieve the JOURNAL_NAME with in the date range.

    parameters:-
    SET_OF_BOOKS_NAME optional (This we can achieve from GL_SETS_OF_BOOKS table)
    FROM DATE mandatory
    TO DATE mandatory

    when i run the concurrent program with using these value sets as parameters, these dates contains duplicate values and displaying a message "DATE IS NOT UNIQUE".

    That is my requirement.

    Thanks in Advance
    Kiran Marla
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Then why not creating the table for date1 set using UNIQUE invoice dates ?
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Invoice is the different case.

    I gave GL_JE_HEADERS for the concept.
    any way, the concept is that can we create table type value sets which is containing duplicate values and use the same in concurrent program.
    i created the table. but i cannot use that, and saying date is not unique.

    Kiran Marla
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Can yoy please give test case with the following ?
    • create table scripts
    • insert script
    • simplified Code you tried
    • Output you get
    • Desired output
     
  7. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    1. Desired out put is to pull Journal Data between two dates.
    2. Table name is GL_JE_HEADERS.
    3. Required columns,
    -> Journal Name
    -> Category
    -> Source
    4. Parameters sob name , date1 and date2 (for date1 and date2 i took DEFAULT_EFFECTIVE_DATE From GL_JE_HEADERS).
    5. For that i created table value set sob name which has no problem.
    6. for date1 and date2 i created seperate value sets date1vsn and date2vsn.
    7. When i am running concurrent program i can select sob name from list of values and i can move for next value to pick.
    8. for date1 i select one value, that date value is repeated in table, and displaying DATE IS NOT UNIQUE and not allowing me to move further.
    9. My doubt is that can we not pick values from value set if value set contains duplicate values.
     
  8. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Raj, what she is referring to are standard tables from the GL module of Oracle Apps. So unless u have access to an Oracle Apps instance you wont be able to test what she is requiring.
     
  9. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    You are correct Sadik, i want to access the GL module's GL_JE_HEADERS data between two dates. in which from_date and to_date are parameters. for dates i created value sets taking source as DEFAULT_EFFECTIVE_DATE from GL_JE_HEADERS. Thank you

    Small Correction , I am not She , I am He :). Good Day
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hmhm,.. Then this thread has to be moved to Apps Forum. .. :)