Oracle Applications - Getting Journals posted after a cut off time - Oracle GL

select '="'||to_char(to_date(h.period_name,'MON-YY'),'MON-YY')||'"' as period_name
                           ,'="'||to_char(h.posted_date,'DD-MON-YYYY HH24:MI:SS')||'"' as posted_date
                          --,to_char(h.posted_date,v_date_format) as posted_date
                         -- ,to_char(h.posted_date,'DD-MON-YYYY HH:MI:SS') as posted_date
                           ,u2.user_name     as Posted_by
                          ,replace(u2.description,',',NULL)   as Posted_by_name
                          ,replace(replace(replace(b.name,',',NULL),chr(10),NULL),chr(13),NULL) as batch_name
                          ,h.name             as journal_name
                          ,replace(replace(replace(h.description,',',NULL),chr(10),NULL),chr(13),NULL) as description
                          ,h.je_source
                          ,h.je_category
                          --,h.date_created
                          ,'="'||to_char(h.date_created,'DD-MON-YYYY HH24:MI:SS')||'"' as date_created
                          ,u1.user_name     as Created_by
                          ,replace(u1.description,',',NULL)     as Created_by_name
                    from apps.gl_je_batches    b
                        ,apps.gl_je_headers    h
                        ,apps.gl_je_categories cat
                        ,fnd_user                u1
                        ,fnd_user                u2
                    where b.je_batch_id      = h.je_batch_id
                      and   h.created_by      = u1.user_id
                      and h.last_updated_by = u2.user_id
                      and h.je_category     = cat.je_category_name
                      and h.set_of_books_id = 1
                      --and h.period_name     in ('JUN-10')
                      and h.period_name     = 'APR-12'
                      and h.status             = 'P'
                      and h.posted_date >= to_date('4-MAY-2012 21:00:00','DD-MON-YYYY HH24:MI:SS')
        order by h.posted_date;

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

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.

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;