• 1.  Oracle Awards in Projects and Grants

    Posted 08-20-2020 11:32 AM
    If I am on the AP Invoice form for invoice # 330-436845 then go to distributions I can see the award number = 602265, project 103465, task 08, Expenditure Type 531101 Operating supplies. when I do Record history I see the Table is a view AP_INVOICE_DISTRIBUTIONS_V

    so it is my understanding the awards are stored in gms_awards_all. Yet the below query will not return any rows from ams_awards_all. WHERE IS MY AWARD NUMBER STORED?

    select ind.award_id, awd.*
    from apps.ap_invoice_distributions_v ind,
    apps.gms_awards_all awd
    where ind.invoice_num = '330-436845'
    and ind.award_id = awd.award_id (+)

    WHERE is the AWARD really stored in GMS?    

    below is what another person said to me, but it is not logical....please help 

    select award_number,


    from apps.gms_awards_all

    where award_number in ('602265','602261','602208')


    here are the 'real award_id's


    Invoice # 330-436845's invoice_id = 1679270


    select award_id from apps.ap_invoice_distributions_v

    where invoice_id = 1679270



    The award_id stored on the ap_invoice_distributions table isn't the same award_id on the gms_awards_all table.


    Remember than report that Belinda wanted updated? And I mentioned in wasn't possible…. And then I tried to explain that maybe it was the 'alloca' thing because in that instant it did look like you could add the award #?...... well… here is really what I wanted to say… and explain.


    There are several tables in oracle that will have 'award_id' but the values in some of the tables aren't the 'real award_id.' 


    I researched it a million years ago and I even think I raised an SR.  You can probably get to the bottom of it.

    Anne Ristau Cooper
    Dekalb County Georgia
    Atlanta GA
    (404) 371-2588

  • 2.  RE: Oracle Awards in Projects and Grants

    Posted 08-21-2020 08:43 AM
    Hi Anne, 

    I believe that you will need to tie the gms_award_distributions.award_set_id to the invoice award_id. You can then tie the gms_award_distributions.award_id to the gms_award_all.award_id. 
    Your query would look something like this. 

    SELECT aidv.award_id, gaa.award_number,
    FROM ap_invoice_distributions_v aidv
    JOIN gms_award_distributions gad
    ON aidv.award_id = gad.award_set_id
    JOIN gms_awards_all gaa
    ON gad.award_id = gaa.award_id
    WHERE aidv.invoice_num = '330-436845'

    Dana Littell
    Ohio University
    Athens OH

  • 3.  RE: Oracle Awards in Projects and Grants

    Posted 08-21-2020 09:13 AM
    Thank you...yes that work!!!