[dreamaway]

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:

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.

[chuck_forbes]

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:

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

then

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 ...

[dreamaway]

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?

[chuck_forbes]

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

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

[dreamaway]

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.

[lwms]

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.