Generate report/output on the basis of "enquiry" responsibilities only 1

[Livia8]

Hi all,

I've asked this

https://www.tek-tips.com/viewthread.cfm?qid=1810077

in the Microsoft Office forum, but since it's data extracted from Oracle I was wondering whether it was possible to obtain what I'm looking for with sql (the thinking behind it being that it might be easier to manipulate the data at source). I'm afraid I'm still very much a beginner and out of my depth.

Many thanks.

 

[carp]

How are you relating users to responsibilities? You need to have at least one column in common between all_users and all_responsibilities - most likely an intersect table (all_user_responsibilities?).

 

[Livia8]

Hi Carp,

Thanks for replying. I've been extracting the data through Toad using this (and then exporting the output to Excel); I only changed the length of the fields to 90 because the names of the responsibilities were getting truncated.

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);

My problem now is that I've been asked to pick up not only users who only have "ENQUIRY" responsibilities, but "ENQUIRY" plus a few others, too.

Thanks again.

 

[carp]

Oh, you're using Oracle Apps! That helps a lot.

Add the following filter:

... AND UPPER(r.responsibility_name) LIKE '%ENQUIRY%'

 

[Livia8]

Close but no cigar, I'm afraid, Carp.

I'm sorry, I probably wasn't very clear: I need to find users who only have "ENQUIRY" responsibilities (AND others, like "REPORTING" or "ADMINISTRATOR" or whatever else I'm given), excluding those who have "ENQUIRY", "REPORTING", "ADMINISTRATOR", etc AND other responsibilities, e.g. "CASH".

Thanks.

 

[Andrzejek]

>but "ENQUIRY" plus a few others, too.

Then, to add to carp's code:[PRE]
... AND (UPPER(r.responsibility_name) LIKE '%ENQUIRY%'
OR UPPER(r.responsibility_name) LIKE '%FEW%'
OR UPPER(r.responsibility_name) LIKE '%OTHERS%')[/PRE]



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

Thanks Andy, but would that exclude those who have "ENQUIRY", "FEW", "OTHERS" and also "ENTRY", for instance?

 

[Andrzejek]

No, it wouldn't.
It would include records for anybody who have "ENQUIRY" or "FEW" or "OTHERS".
The best way to see what's going on is to actually run the SQL and see...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

Maybe this will make more sense (provided it's legible):

While the responsibilities in orange contain the keywords I'm looking for, those users also have responsibilities that I need to exclude from my list, so the whole lot has to be excluded. I need to get only those shown in green, who have ONLY responsibilities with the keywords I need but nothing else. The sql you and carp suggested (which will come in handy for something else) return both the orange and the green lines, whereas I'm only looking for the green lines i.e. ERODRIGUEZ and PMURPHY.

Thanks again.

 

[Andrzejek]

Then you cannot concentrate on a single word, you need an entire expression:

[pre]
... AND (UPPER(r.responsibility_name) = 'GREEN APPLES PAYABLES ENQUIRY'
OR UPPER(r.responsibility_name) = 'GREEN APPLES PAYABLES REPORTING'
OR UPPER(r.responsibility_name) = 'YELLOW PEARS PURCHASING ENQUIRY') [/pre]

Hopefully, you have a table for it, like a RespTable:
[pre]
ID RESP
1 GREEN PEARS CASH ENQUIRY
2 GREEN APPLES PAYABLES ENQUIRY
3 GREEN APPLES PAYABLES REPORTING
4 YELLOW PEARS PURCHASING ENQUIRY
5 GREEN APPLES ADMIN
6 ...[/PRE]

Which would make your SQL a LOT simpler:
[tt]
... AND RespTable.ID in (2, 3, 4)[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

>I need to get only those shown in green, who have ONLY responsibilities with the keywords I need but nothing else

Well, it looks to me that you may need 2 tables, unless you already have them.
1. a RespTable:[blue][pre]
ID RESP
1 GREEN PEARS CASH
2 GREEN APPLES PAYABLES
3 GREEN APPLES PAYABLES
4 YELLOW PEARS PURCHASING
5 GREEN APPLES
6 ... [/pre][/blue]
and 2. Level table[blue][pre]
ID LEVEL
1 ENQUIRY
2 REPORTING
3 ADMIN[/pre][/blue]
So any user would get 2 attributes: Responsibility and its Level

----
From your other post thread68-1810077 looks like you have one big table with All Responsibilities :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

Yep, I do only have one big table (at least, that's what I'm getting from the select above) with two columns, one with the user name, the other with the full name of the responsibility - if I were to split the table/responsibilty colum, how would I differentiate between the green apples at 2 and 3 in your post?

In my head, the "script" sounds like: count user if user resp has "enquiry" and/or "admin" and/or "reporting" and is user does not have any other responibility. Easier said than done, I know.

 

[Livia8]

I think I may have found a work-around in Excel: if I extract from the main report two lists, one with ONLY users with my parameters, and another with ONLY users without my parameters, I can then do a VLOOKUP and exclude the names on the the first list that match those on the second.

Is there anything similar that could be set up with sql script?

 

[Andrzejek]

My mistake.
This example would be:
[pre]
ID RESP
1 GREEN PEARS CASH
2 GREEN APPLES PAYABLES
3 YELLOW PEARS PURCHASING
4 GREEN APPLES
5 ... [/pre]

Actually, that's where the properly normalized referential data base design plays the role. In my book, if you need to repeat any data, this data belongs only ONCE in a table. So, since there is a LOT of repeated data in your table, I see it in multiple tables:

[pre] tblColors
ID COLOR
1 GREEN
2 RED
3 YELLOW

tblFruits
ID FRUIT
1 APPLES
2 PEARS

tblPrives
ID PRIV
1 ADMIN
2 CASH
3 OTHER
4 PAYABLES
5 PURCHASING
[/pre]
Etc.
This way you can assign any Color/Fruit/Priv/whatever to anybody, you can easily extend any table (Let's say you need to add PEACH and BANANA).


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

OK, let's try this:

SELECT UNIQUE u.user_id, 
              SUBSTR (u.user_name, 1, 30) user_name,
              SUBSTR (r.responsibility_name, 1, 60) responsiblity,
              SUBSTR (a.application_name, 1, 50) application
         FROM fnd_user u,
              fnd_user_resp_groups g,
              fnd_application_tl a,
              fnd_responsibility_tl r
        WHERE g.user_id = u.user_id
          AND g.responsibility_application_id = a.application_id
          AND a.application_id = r.application_id
          AND g.responsibility_id = r.responsibility_id
          AND UPPER(r.responsibility_name) LIKE '%ENQUIRY%'
          AND NOT EXISTS (SELECT 'x' 
                            FROM fnd_user ui,
                                 fnd_user_resp_groups gi,
                                 fnd_application_tl ai,
                                 fnd_responsibility_tl ri
                           WHERE gi.user_id = ui.user_id
                             AND gi.responsibility_application_id = ai.application_id
                             AND ai.application_id = ri.application_id
                             AND gi.responsibility_id = ri.responsibility_id
                           [i][/i]  AND UPPER(ri.responsibility_name) NOT LIKE '%ENQUIRY%'
                             AND ui.user_id = u.user_id)
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);

For efficiency, I took out the outer join, since there didn't seem to be a need to find a user with no responsibilities.

 

[carp]

After a little more thought, I think this will probably be more efficient:

SELECT u.user_id, SUBSTR(u.user_name, 1, 30) user_name,
       SUBSTR (r.responsibility_name, 1, 60) responsiblity,
       SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u
     INNER JOIN fnd_user_resp_groups g
        ON g.user_id = u.user_id
     INNER JOIN fnd_responsibility_tl r
        ON g.responsibility_id = r.responsibility_id
     INNER JOIN fnd_application_tl a
        ON a.application_id = g.responsibility_application_id
           AND a.application_id = r.application_id
     INNER JOIN (SELECT u.user_id
                   FROM fnd_user u
                        INNER JOIN fnd_user_resp_groups g
                           ON g.user_id = u.user_id
                        INNER JOIN fnd_responsibility_tl r
                           ON g.responsibility_id = r.responsibility_id
                  WHERE UPPER(r.responsibility_name) LIKE '%ENQUIRY%'
                 MINUS
                 SELECT u.user_id
                   FROM fnd_user u
                        INNER JOIN fnd_user_resp_groups g
                           ON g.user_id = u.user_id
                        INNER JOIN fnd_responsibility_tl r
                           ON g.responsibility_id = r.responsibility_id
                  WHERE UPPER(r.responsibility_name) NOT LIKE '%ENQUIRY%') v
        ON v.user_id = u.user_id
 ORDER BY 2,4,3;

 

[Livia8]

Thank you very much Carp and apologies for not answering sooner. Like I said on the other thread, I won't get a chance to go back to work on this before tomorrow at the earliest, but I will try all solutions offered and and report back. Much appreciated.