Org Details

Select  distinct
a.operating_unit, b.name OU, a.set_of_books_id,d.name LEDGER,
a.legal_entity,c.name LE_NAME
From  apps. ORG_ORGANIZATION_DEFINITIONS a,
apps. HR_OPERATING_UNITS b,
apps. xle_entity_profiles c,
apps. gl_ledgers d
Where a.operating_unit=b.organization_id
AND c.legal_entity_id=a.legal_entity
AND d.ledger_id=a.set_of_books_id

Responsibility Query

SELECT aia.invoice_num,

AIA.INVOICE_DATE ,

AIA.Invoice_Amount,

aia.creation_date,

fu1.user_name,

pf1.full_name,

laa.TRANSACTION_TYPE,

laa.COLUMN_NAME,

LAA.OLD_VALUE ,

LAA.NEW_VALUE ,

LAA.LOGIN_USER_NAME ,

papf.full_name,

LAA.TRANSACTION_DATE ,

laa.RESPONSIBILITY_NAME

from

apps.laud_audit_transactions  LAA,

apps.ap_invoices_all aia, 

apps.fnd_user fu,

apps.per_all_people_f papf,

apps.fnd_user fu1,

apps.per_all_people_f pf1

WHERE 1 = 1

and laa.TABLE_NAME = 'AP_INVOICES_ALL'

and aia.created_by=fu1.user_id

and fu1.employee_id=pf1.person_id

AND TRUNC(SYSDATE) BETWEEN  PF1.EFFECTIVE_START_DATE AND PF1.EFFECTIVE_END_DATE

and laa.transaction_date >= sysdate-1

and (laa.RESPONSIBILITY_NAME like ('%Payables Superuser%') or laa.RESPONSIBILITY_NAME like ('%Payables Super User%'))

and LAa.pk_col1='INVOICE_ID'

and aia.invoice_id=laA.PK_VAL1_ID

and aia.invoice_amount>0

and fu.user_name = laa.login_user_name   and fu.employee_id = papf.PERSON_ID

AND TRUNC(SYSDATE) BETWEEN  PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

Responsibility Based Transaction in EBS master data

SELECT       fu.user_name,

             fd.responsibility_id,

             ftl.responsibility_name,

             fd.start_date,

             fd.end_date,

             aia.invoice_num,

             aia.invoice_date

      FROM fnd_user fu, fnd_user_resp_groups_direct fd,fnd_responsibility_tl ftl,ap_invoices_all aia

      WHERE     fu.user_id = fd.user_id

            AND fd.RESPONSIBILITY_ID = ftl.RESPONSIBILITY_ID

            AND fd.end_date IS NULL

            AND fu.user_id  =   :p_user_id

            AND (fu.user_id  =   aia.created_by

              OR fu.user_id  =   aia.last_updated_by) 

            AND  ftl.responsibility_name='Payables Super User (Process Operations)'