google analytics

meta

adsense

Monday, March 26, 2012

Oracle Applications - Query to get all responsibilities assigned to all active users


select  distinct responsibility_name ,y.user_name
       ,y.description
      , z.employee_number
      , z.full_name,
       z.first_name,
       z.last_name
        from apps.fnd_user_resp_groups x , fnd_user y , per_people_f z ,fnd_responsibility_tl a
              where x.responsibility_id = a.responsibility_id    
    and  x.start_date <=sysdate and (x.end_date is null OR x.end_date > sysdate)
    and y.user_id = x.user_id
    and  y.start_date <=sysdate and (y.end_date is null OR y.end_date > sysdate)
    and z.employee_number = y.user_name
    and  z.effective_start_date <=sysdate
    and (z.effective_end_date  > sysdate or z.effective_end_date is NULL)
    and language = 'US'
    order by employee_number nulls first

Thursday, March 22, 2012

Copying data along with the Column headers from Data grid in Toad

When you need to copy data from the Toad Data grid, CTRL+V will not copy the column headers. If you need the Column headers to be copied, press CTRL+INSERT to copy the headers as well.


Tuesday, March 6, 2012

SQL to get the Next or previous Weekend Date - NEXT_DAY

The below SQL gives the next Saturday and the next Sunday dates.


SELECT NEXT_DAY('06-MAR-2012', 'SATURDAY') "next saturday" 
,NEXT_DAY('06-MAR-2012', 'SUNDAY') "next sunday"
FROM dual;






If the requirement is to find the previous weekend dates, just subtract the dates by 7.


SELECT NEXT_DAY('06-MAR-2012', 'SATURDAY')-7 "next saturday" 
,NEXT_DAY('06-MAR-2012', 'SUNDAY')-7 "next sunday" 
FROM dual;

LinkWithin

Related Posts Plugin for WordPress, Blogger...