OA Framework Tutorials Training

Hi all,

Based on my friend's request, i am sharing one of the best Training docs from apps2fusion.com .(Credit goes to Anil passi and i am just sharing for the knowledge sharing )

URL : http://apps2fusion.com/apps-training/oa-framework-training 

Thanks

Regards

Prasad

To find supervisor of the user and the authorization limit of the user and supervisor

For 11.5.0

SELECT   a.user_name user_id,
         b.full_name user_full_name,
         c.d_supervisor_id supervisor_user_id,
         d.authorization_limit User_auth_limit,
         e.authorization_limit Supervisor_auth_limit,
         e.set_of_books_id,
         (SELECT   name
            FROM   gl_sets_of_books
           WHERE   set_of_books_id = e.set_of_books_id)
            SOB_NAME
  FROM   fnd_user a,
         per_all_people_f b,
         PER_ASSIGNMENTS_V7 c,
         gl_authorization_limits_v d,
         gl_authorization_limits_v e
 WHERE       a.employee_id = b.person_id
         AND b.person_id = c.person_id(+)
         AND a.employee_id = d.employee_id(+)
         AND c.supervisor_id = e.employee_id(+)
         AND a.user_name = '&preparer name';

For R12

SELECT   a.user_name user_id,
         b.full_name user_full_name,
         c.d_supervisor_id supervisor_user_id,
         d.authorization_limit User_auth_limit,
         e.authorization_limit Supervisor_auth_limit,
         e.set_of_books_id,
         (SELECT   name
            FROM   gl_sets_of_books
           WHERE   set_of_books_id = e.set_of_books_id)
            SOB_NAME
  FROM   fnd_user a,
         per_all_people_f b,
         PER_ASSIGNMENTS_V7 c,
         gl_authorization_limits_v d,
         gl_authorization_limits_v e
 WHERE       a.employee_id = b.person_id
         AND b.person_id = c.person_id(+)
         AND a.employee_id = d.employee_id(+)
         AND c.supervisor_id = e.employee_id(+)
         AND a.user_name = '&preparar name';

SQL Query to know the Fiscal Month End close status for Financials (AP,AR and GL)

This query will help to find the Fiscal month end close status for the Oracle Financials modules (AP,AR and GL)

SELECT   glps.period_name,
           fa.application_name "Application Name",
           gsob.name,
           DECODE (closing_status, 'N', 'N/a', fu.user_name) "Updated By",
           DECODE (closing_status,
                   'N', 'Never Opened',
                   'C', 'Closed',
                   'O', 'Open',
                   closing_status)
              "Closing Status"
    FROM   apps.gl_period_statuses glps,
           apps.fnd_application_tl fa,
           apps.fnd_user fu,
           apps.gl_sets_of_books gsob
   WHERE       fa.application_id = glps.application_id
           AND glps.last_updated_by = fu.user_id
           AND glps.set_of_books_id = gsob.set_of_books_id
           AND glps.period_name LIKE 'Mar-11'    ----- Provide the month name as of your choice
           AND glps.closing_status NOT IN ('N', 'F')
           AND fa.application_id IN (101, 140, 200, 201, 222) ---- Provide the Application ID as of your oracle setup
ORDER BY   5 DESC, 2, 3;

Thanks

Regards
Prasad

How to find the invoice validation process stuck in deferred queue

How to find the invoice validation process stuck in deferred queue

1.       Log in to oracle application

2.       Go to  workflow administrator  web (new) responsibility

Supplier and Site in AP

select distinct 
       pv.vendor_id,
       pv.segment1,
       pv.attribute1 header_id,
       pv.vendor_name,
       pv.start_date_active ,
       pv.end_date_active,  
       ps.vendor_site_code,
       ps.inactive_date,
       ps.ADDRESS_LINE1,
       ps.ADDRESS_LINE2,
       ps.org_id,
       pv.creation_date v_created,
       ps.creation_date s_created,
--       pv.created_by,
--       ps.created_by,
       ps.ADDRESS_LINE3,
       ps.ADDRESS_LINE4,
       ps.city,
       ps.state,
       ps.zip
  from po_vendors pv, po_vendor_sites_all ps
 where pv.vendor_id = ps.vendor_id
   and pv.vendor_name like '%'

Query to find the patch status what we will apply to fix some bugs in the applications.

----Give the patch_name here to find whether patch has been applied in the instance or not.
select
faav.application_short_name,
faav.application_name,
aap.patch_name,
aap.patch_type,
aap.source_code,
aap.creation_date,
aap.created_by,
aap.last_update_date,
aap.last_updated_by
from
AD_APPLIED_PATCHES aap,
apps.fnd_application_all_view faav
where
aap.applied_patch_id=faav.application_id and
aap.patch_name='5117525';----give the patch name or number here as per your request.

Query to Find Patches which are loaded in oracle apps instance based on the Application Name level

Hi All,

Based on my friend's request i am adding the updated query with the following requirement. 

This will help you to fetch the details of the patch based on the application level. Thanks my friend for your request.

SELECT   SUBSTR (a.application_name, 1, 60) "Application Name",
         SUBSTR (i.product_version, 1, 4) "Version",
         i.patch_level "Patch Level", i.application_id "Application ID",
         i.last_update_date "Last Update"
    FROM apps.fnd_product_installations i, apps.fnd_application_all_view a
   WHERE i.application_id = a.application_id
   and a.application_name='Payables'  ----- Add the application name here as per your request
ORDER BY a.application_name;

Regards

Prasad