Saturday 28 February 2015

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

Saturday 14 February 2015

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

Tuesday 10 February 2015

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)'