Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    11

    Question Unanswered: Getting the organization ID

    I am writing a web site in ASP .NET (Oracle 9.2.4) and it is going to be used in 3 different organizations within our company. I need to get the org_id from the person's username so I can display only the data for their organization. I'm not sure I am looking at the correct table(s). My current query is:

    Code:
    SELECT loc.inventory_organization_id FROM 
    hr_locations_all loc, 
    per_all_people_f pap, 
    per_all_assignments_f paa, 
    fnd_user fu 
    WHERE (loc.location_id = paa.location_id) AND 
    (pap.person_id = paa.person_id) AND 
    (pap.person_id = fu.employee_id) AND 
    (fu.user_name = '" + userName + "');
    The problem with this is I get a value from my test instance but when I run it in the production instance the inventory_organization_id field is not filled in so I am wondering if I am in the right place. Any insight for me? Thank you.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Could the inventory_organization_field be NULL? Are you sure you're getting back a NULL record, vs no record at all?

    In order to solve your problem, try slowly building your SQL one table at a time, so see if you spot the flaw in your logic, and hard code the username until you do:

    Code:
    SELECT fu.user_name FROM 
    fnd_user fu 
    WHERE (fu.user_name = 'SMITHD');
    then

    Code:
    SELECT fu.user_name, pap.person_id FROM 
    per_all_assignments_f paa, 
    fnd_user fu 
    WHERE (pap.person_id = fu.employee_id) AND 
    (fu.user_name = 'SMITHD');
    and so on ...

  3. #3
    Join Date
    Mar 2008
    Posts
    11
    Yes I am getting back a null record, and I was wondering if that was the correct place to look in for the Org_ID but no one filled it in, or if I should be looking in another table. So that's my real question, from what table does one determine organization id?

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Guessing here, but because of your equi-joins, you are coming back with a NULL record. If it's not that, then there's no username which matches the one you passed in.

    Deconstructing the SQL statement, and adding back in one table at a time will help you figure out which of the 2 above scenarios are possible. If it's the former (equi-joins), at first you'll see records returned until you finally add the table and it's equi-join which foils the request. If it's the latter (no user name), then you'll know it immediately when you run

    Code:
    SELECT fu.user_name FROM 
    fnd_user fu 
    WHERE (fu.user_name = 'SMITHD');
    Then, you can decide what to do, use outer-joins, a union all, etc.

    -=cf

  5. #5
    Join Date
    Mar 2008
    Posts
    11
    Well the user absolutely exists, because it is me. :-) Plus that query works in my test instance, so I'm sure the query is not the problem.

    I am using Toad and I can see in the table (in the new instance) that the inventory_organization_id column contains no values, so I don't know if someone didn't fill it in correctly or if I am looking in the wrong place. And long story short, I don't have anyone in my company that I can ask, so I am trying to find out for myself if I need to somehow populate this column or if I am just barking up the wrong tree to begin with.

    Thank you for your responses though.

  6. #6
    Join Date
    Oct 2004
    Posts
    8
    Hi, dreamaway. It seems that the data in the test database is not the same as the data in the production database. For example, the inventory_organization_id field of the hr_locations_all table of the test database contain data but the inventory_organization_id field of the hr_locations_all table of the production database does not contain data. It may help if you make the data in the test database the same as or a subset of the data in the production database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •