Supervisor Hierarchy and Approval Limits in Oracle EBS R12
Supervisor Hierarchy query in Oracle apps R12
Approval Limits query in Oracle apps R12
--Supervisor Hierarchy of the employee
SELECT LEVEL,
e.*
FROM
(SELECT DISTINCT papf.person_id,
papf.employee_number,
papf.full_name "EMPLOYEE_FULL_NAME",
paaf.supervisor_id,
papf1.employee_number "SUPERVISOR_EMP_NUMBER",
papf1.full_name "SUPERVISOR_FULL_NAME",
pj.name job_name
FROM apps.per_all_people_f papf,
apps.per_all_assignments_f paaf,
apps.per_jobs pj,
apps.per_all_people_f papf1,
apps.per_person_types ppt
WHERE papf.person_id = paaf.person_id
AND papf1.person_id = paaf.supervisor_id
AND papf.business_group_id = 0
AND papf.business_group_id = paaf.business_group_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppt.person_type_id = papf.person_type_id
AND paaf.job_id=pj.job_id(+)
AND ppt.user_person_type 'Ex-employee') e CONNECT BY
PRIOR supervisor_id=person_id
START WITH person_id = 123456;
--Approval Limits
SELECT d.full_name,
e.job_id,
g.control_group_name,
h.object_code,
h.amount_limit,
h.*
FROM per_all_people_f d,
per_all_assignments_f e,
po_position_controls_all f,
po_control_groups_all g,
po_control_rules h,
apps.po_control_functions pcf
WHERE 1=1
AND d.effective_end_date > SYSDATE
AND d.person_id = e.person_id
AND e.effective_end_date > SYSDATE
AND e.job_id = f.job_id
AND f.control_group_id = g.control_group_id
AND g.control_group_id = h.control_group_id
AND pcf.control_function_name = 'Approve Purchase Requisitions'
AND f.control_function_id = pcf.control_function_id
AND g.org_id = 100
AND d.person_id=123456;
Related posts:
- Register a test concurrent program from backend in Oracle EBS
- Supervisor Hierarchy and Approval Limits in Oracle EBS R12
- Query to find vacation rules in Oracle R12
- Purchase Order Receipt details query in Oracle apps
- Oracle apps query to find approval limits
- View java class source in oracle database
- Load Java source code in oracle database
- Query to find all APIs of Oracle Apps modules
- OAF customizations queries
- Oracle apps list of concurrent programs in a Request Set
- Query to get Organization Hierarchy in Oracle apps
- Oracle apps query to find incompatible programs blocking a concurrent request
- Form function attached to which responsibility and user
- Purchase Requisition Vertex debug xml query in Oracle EBS
- Purchase Order Vertex debug xml query in Oracle EBS
- Query to find the responsibility attached to user in oracle apps
- How to enable debug log in Oracle apps EBS
- How to add AME role and responsibility to user
- Oracle form attached to which responsibility
- Query to find serial number material transactions details
- SQL query to get MIME type for file extension
- Query to get concurrent program and executable details
- Query to get responsibility level profile option values
- Query to validate migration of XML/BI Publisher Template and Data Definition
- Query to get details of XML/BI Publisher Template and Data Definition
- Query to get Site level and Organization level Profile Option Value
- Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
- PL-SQL code to reset application user password
- PL-SQL Procedure to send email with attachment
- PL-SQL to delete Concurrent Program and Executable
- Query to get the details of the lock in the database
- PL-SQL code to delete XML/BI Publisher template and Data Definition
- Query to find Purchase Order and Invoice Details
- Query to find AP Invoices matched with receipts
- Query to find Form Personalization
- Query to find Supplier Bank Details
- Query to find Credit Memos on an AP invoice
- Query to find the AP invoices that are applied to multiple Purchase Orders
- Query to find profile option values
- Query to get details of concurrent program parameters
- PL-SQL Code to find Menu Hierarchy of a responsibility
- Query to find details of Flex value sets
- Query to find concurrent program Responsibility and Request Group
- Concurrent program SQL statement currently running in background
- Query to find scheduled concurrent programs
- Query to kill database session for a concurrent request
- Query to get DFF (Descriptive Flex Field) Details