Important Queries for oracle receivable, Payables

Important Queries for oracle receivable, Payables

Below are the few important queries which are used in oracle

Important Queries

1.Below query will fetch all the responsibilities attached to a user

Select   fu.user_name, 
            fr.responsibility_name, 
            furg.START_DATE, furg.END_DATE
from   fnd_user_resp_groups_direct furg, fnd_user fu,                                  fnd_responsibility_tl fr
where  fu.user_name = :user_name
and      furg.user_id = fu.user_id
and      furg.responsibility_id = fr.responsibility_id
and      fr.language = userenv('LANG')

2.Query to find all the users which get updated in last one month 

select vs.name,a.*
from(
SELECT
     fuser.description
    ,fuser.USER_NAME USER_NAME
    , fuser.employee_id
    ,fuser.start_date
    ,fuser.end_date
    ,papf.full_name
    --,frt.RESPONSIBILITY_ID RESPONSIBILITY_ID
    --,frt.RESPONSIBILITY_NAME RESPONSIBILITY
    ,fuser.creation_date creation_date
    ,fuser.last_update_date last_updated
    ,(select count(1) from FND_USER_RESP_GROUPS furg, FND_RESPONSIBILITY_TL frt where frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID and fuser.USER_ID = furg.USER_ID AND frt.LANGUAGE = 'US') resp_count
    ,(select count(1) from wf_user_role_assignments_v furg, FND_RESPONSIBILITY_TL frt where 'FND_RESP'||frt.application_id||':'||frt.RESPONSIBILITY_ID = furg.ROLE_name AND frt.LANGUAGE = 'US' and fuser.USER_name = furg.USER_name and furg.creation_date>=sysdate-30) resp_added_in_last_month_count
FROM
    FND_USER fuser
    --, FND_USER_RESP_GROUPS furg
    --, FND_RESPONSIBILITY_TL frt
    , (select full_name,person_id from per_all_people_f papf1 where sysdate between papf1.effective_start_date and papf1.effective_end_date) papf
    --,wf_user_role_assignments_v wura
WHERE
    1=1
    --and wura.user_name=fuser.user_name
    --fuser.USER_ID = furg.USER_ID
    --AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
    --AND frt.LANGUAGE = 'US'
    --and fuser.last_update_date>=sysdate-30
    and fuser.last_update_date>=sysdate-30
    and papf.person_id = fuser.employee_id(+)
    -- AND frt.LANGUAGE = 'US'   
) a, v$database vs
where a.resp_added_in_last_month_count <> 0 or a.end_date is not null
order by a.user_name

3.Query to find the count( number of ) invoices created between particular date and time 

select trunc(creation_date) start_date,
       to_char(creation_date, 'hh24:mm') START_TIME,
       count(*) total,
       sum(case when nvl(attribute15,'PAPER') = 'EDI'  and trx_number not like '%CR' then 1 else 0 end) EDI_INVOICES,
       sum(case when nvl(attribute15,'PAPER') = 'PAPER' and trx_number not like '%CR' then 1 else 0 end) PAPER_INVOICES,
       sum(case when trx_number like '%CR' then 1 else 0 end) CREDIT_MEMO
from ra_customer_trx_all 
where creation_date between to_date('2016-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2016-05-02 23:59:59','YYYY-MM-DD HH24:MI:SS')
group by creation_date,to_char(creation_date, 'hh24:mm:ss')
order by start_time