SQL to Find Employee – Supervisor Hierarchy details

Background

The Purchase order or Requisitions are not valid unless it is approved. In any of the procurement process, most of the issues occur in the approval of the purchasing document due to the issues in approval hierarchy and related setups.

Hence we have built a query to show the approval hierarchy of the Requestor of the Requisition or Purchase Order along with other required details like Supervisor Name, User Name, Expense account details etc.

This will help the support person to easily identify the issues in the hierarchy in a single query output and to provide required resolution. 

Resolution:

Step 1: 

Please pass 'Employee number' of Requester in the below code. Run the script download the output

SELECT

fu.user_name "User Name",

haou.name "Employee BG",

fu.employee_id "Employee ID @User",

gl.name "Emp. Ledger Name",

ppx.full_name "Employee Name",

ppx.employee_number "Employee Number",

ppx.person_id "Employee ID @ Person",

pjv.name "Employee Job Name",

pjv.approval_authority "Job Level",

hl.location_code "Employee Location",

gcc.concatenated_segments "Default Expense Account",

ppx1.full_name "Supervisor Name",

ppx1.person_id "Supervisor Emp ID @ Person",

ppx1.employee_number "SuperVisor Emp Num @ Person",

haou1.name "Supervisor BG",

CONNECT_BY_ISCYCLE

FROM

apps.per_people_x ppx,

apps.per_people_x ppx1,

apps.per_assignments_x pax,

apps.per_jobs_v pjv,

apps.gl_code_combinations_kfv gcc,

apps.hr_locations hl,

apps.fnd_user fu,

apps.gl_ledgers gl,

apps.hr_all_organization_units haou

,apps.hr_all_organization_units haou1

WHERE 1=1

and ppx.person_id = pax.person_id

and ppx.person_id = fu.employee_id(+)

and pax.job_id = pjv.job_id

and pax.default_code_comb_id = gcc.code_combination_id(+)

and pax.location_id = hl.location_id

and pax.set_of_books_id = gl.ledger_id(+)

and ppx1.person_id = pax.supervisor_id

and ppx.business_group_id = haou.organization_id

and ppx1.business_group_id = haou1.organization_id

connect by nocycle prior pax.supervisor_id = pax.person_id

start with  ppx.employee_number =  '10484'; ------------------------------------------------------------ The above Query provides below results.

User Name	Employee BG	Employee ID @User	Emp. Ledger Name
XXXX.BLANXXXX	XX Business Group	34658	XX Ledger
YYYY.YYYEST	XX Business Group	34513	XX Ledger
ZZZZ.ZZZZERT	XX Business Group	34643	XX Ledger

Employee Name	Employee Number	Employee ID @ Person	Employee Job Name	Job Level
Blan, M	10484	34658	Responsable Achats	0
Est, D	10339	34513	Direct. Projet	2
ERT, A	10469	34643	Directeur Général	5

Employee Location	Default Expense Account
London	12345.6789012.00000.03420.9999999.00000.0000.0000.0000
London	12345.6789012.00000.03700.9999999.00000.0000.0000.0000
London	12345.6789012.00000.03100.9999999.00000.0000.0000.0000

Supervisor Name	Supervisor Emp ID @ Person	SuperVisor Emp Num @ Person	Supervisor BG	CONNECT_BY_ISCYCLE
David	34513	10339	XX Business Group	0
Arnaud	34643	10469	XX Business Group	0
Marshall	34718	10004	XX Business Group	1

Step 2: 

From Table # 1, find the  Employee number of  'Supervisor' for the last record. In the above example it is (10004)

Step 3: 

Add the output from Step 2 into the Table # 1

The Final Output would be as follows:

User Name	Employee BG	Employee ID @User	Emp. Ledger Name
XXXX.BLANXXXX	XX Business Group	34658	XX Ledger
YYYY.YYYEST	XX Business Group	34513	XX Ledger
ZZZZ.ZZZZERT	XX Business Group	34643	XXLedger
	XX Business Group		XX Ledger

Employee Name	Employee Number	Employee ID @ Person	Employee Job Name	Job Level
Blan, M	10484	34658	Responsable Achats	0
Est, D	10339	34513	Direct. Projet	2
ERT, A	10469	34643	Directeur Général	5
Marshall	10004	34718	GM Western	15

Employee Location	Default Expense Account
London	12345.6789012.00000.03420.9999999.00000.0000.0000.0000
London	12345.6789012.00000.03700.9999999.00000.0000.0000.0000
London	12345.6789012.00000.03100.9999999.00000.0000.0000.0000

Supervisor Name	Supervisor Emp ID @ Person	SuperVisor Emp Num @ Person	Supervisor BG	CONNECT_BY_ISCYCLE
David	34513	10339	XX Business Group	0
Arnaud	34643	10469	XX Business Group	0
Marshall	34718	10004	XX Business Group	1
Revert	34643	10469	XX Business Group	0

Conclusion

From the final table we can find out that user id for the employee "Marshall" is not mapped. Hence the approval generation issue has occurred.  

This issue will be resolved if the employee and user id of the Marshall is mapped.

Query for finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module

WITH PARAMETER AS (SELECT :PERIOD_NAME AS PERIOD_NAME FROM DUAL)
SELECT distinct prj.project_id,prj.segment1,
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period   =PARAMETER.PERIOD_NAME
    -- and organization_id=TXN.organization_id
  ) "MTD Cost",
  (SELECT ROUND(SUM(unbilled_receivable_dr),2)--round(SUM(tot_revenue),2)
  FROM apps.pa_draft_revenues_all
  WHERE project_id              =prj.project_id
  AND TO_CHAR(gl_date,'MON-YY') =PARAMETER.PERIOD_NAME
  ) "MTD Revenue",
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period  IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
    -- and organization_id=TXN.organization_id
  ) "YTD Cost",
  (SELECT ROUND(SUM(tot_revenue),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period  IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
     ) "YTD Revenue",
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM pa.pa_txn_accum
  WHERE project_id=prj.project_id
  ) "ITD Cost",
  (SELECT ROUND(SUM(tot_revenue),2)
  FROM pa.pa_txn_accum
  WHERE project_id=prj.project_id
  )"ITD Revenue",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id                   =prj.project_id
  AND TO_CHAR(creation_date,'MON-YY')=PARAMETER.PERIOD_NAME
  )"MTD Fee",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id                     =prj.project_id
  AND TO_CHAR(creation_date,'MON-YY') IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
  )"YTD Fee",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id=prj.project_id
  )"ITD Fee",
  (SELECT SUM(allocated_amount)
  FROM pa_project_fundings
  WHERE project_id=prj.project_id
  )"Funded Revenue",
  (SELECT SUM(a.amount) "Revenue To Date"
  FROM pa.PA_DRAFT_REVENUE_ITEMS# a,
    pa.PA_DRAFT_REVENUES_ALL# b,
    pa.pa_projects_all ppa
  WHERE a.project_id      = ppa.project_id
  AND a.DRAFT_REVENUE_NUM = b.DRAFT_REVENUE_NUM
  AND a.project_id        = b.project_id
  AND b.GL_DATE          <=
    (SELECT END_DATE
    FROM gl.gl_period_statuses
    WHERE end_date =
      (SELECT MAX(end_date)
      FROM gl.gl_period_statuses
      WHERE application_id       = 101
      AND set_of_books_id        = 2022
      AND closing_status         = 'C'
      AND adjustment_period_flag = 'N'
      )
    AND application_id         = 101
    AND set_of_books_id        = 2022
    AND adjustment_period_flag = 'N'
    )
  AND ppa.project_id=prj.project_id
  )"GL Close Revenue"
FROM
    PA.PA_PROJECTS_ALL# PRJ,
  PA.PA_PROJECT_TYPES_ALL# PRJT,
  PA.PA_TXN_ACCUM# TXN,PARAMETER
WHERE prj.project_id                              = txn.project_id
AND prj.project_status_code                     ='APPROVED'
AND prj.PROJECT_TYPE                            = PRJT.PROJECT_TYPE(+)
AND prjt.project_type_class_code                ='CONTRACT'
AND TO_CHAR(week_ending_date , 'MON-RR') = PARAMETER.PERIOD_NAME

GL Transactions for Payments

SELECT DISTINCT aca.payment_method_lookup_code trans_type,
                TO_CHAR (aca.check_number) invoice_num,
                TO_CHAR (aca.check_date) invoice_date,
                xle.event_type_code ponum_inv_linenum,
                ael.currency_code line_desc,
                apps.imie_remove_special_chars.remove_special_chars
                   (TO_CHAR (CASE
                                WHEN xle.event_type_code =
                                                        'PAYMENT CANCELLATION'
                                   THEN (aca.amount * -1)
                                ELSE aca.amount
                             END
                            )
                   ) requestor_qty_inv,
                NULL appr_date_unitprice, NULL doc_seq_revamt,
                NULL acct_class, NULL PERCENT,
                  DECODE (ael.entered_dr, NULL, 0, ael.entered_dr)
                - DECODE (ael.entered_cr, NULL, 0, ael.entered_cr) amount,
                  DECODE (ael.accounted_dr,
                          NULL, 0,
                          ael.accounted_dr
                         )
                - DECODE (ael.accounted_cr, NULL, 0, ael.accounted_cr)
                                                                     dist_amt
           FROM xla_ae_headers aeh,
                xla_ae_lines ael,
                xla_events xle,
                xla.xla_transaction_entities ent,              
                xla_distribution_links xdl,
                ap_checks_all aca,
                ap_payment_hist_dists aphd,
                ap_payment_history_all aph
          WHERE 1 = 1
            AND ael.application_id = aeh.application_id
            AND ael.ae_header_id = aeh.ae_header_id
            AND xle.application_id = aeh.application_id
            AND xle.event_id = aeh.event_id          
            AND ent.application_id = xle.application_id
            AND ent.entity_id = xle.entity_id
            AND xdl.ae_header_id = aeh.ae_header_id
            AND xdl.ae_line_num = ael.ae_line_num
            AND aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1
            AND xdl.source_distribution_type = 'AP_PMT_DIST'
            AND ent.transaction_number = aca.check_number
            AND xdl.application_id = 200
            AND aphd.payment_history_id = aph.payment_history_id
            AND aph.check_id = aca.check_id
            AND ael.ae_header_id = p_hdrid
            AND ael.ae_line_num = p_linenum;

GL Transactions for AR

SELECT rctt.description trans_type, TO_CHAR (rct.trx_number) invoice_num,
       TO_CHAR (rct.trx_date) invoice_date,
       TO_CHAR (rcl.line_number) ponum_inv_linenum, rcl.description line_desc,
       TO_CHAR (rcl.quantity_invoiced) requestor_qty_inv,
       TO_CHAR (rcl.unit_selling_price) appr_date_unitprice,
       TO_CHAR (rcl.revenue_amount) doc_seq_revamt,
       rctg.account_class acct_class, TO_CHAR (rctg.PERCENT) PERCENT,
       (rctg.amount * -1) amount, (rctg.acctd_amount * -1) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       ra_customer_trx_all rct,
       ra_customer_trx_lines_all rcl,
       ra_cust_trx_types_all rctt,
       ra_cust_trx_line_gl_dist_all rctg
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND rcl.customer_trx_line_id = rctg.customer_trx_line_id
   AND rct.customer_trx_id = rcl.customer_trx_id
   AND rctt.cust_trx_type_id = rct.cust_trx_type_id
   AND rct.trx_number = ent.transaction_number
   AND xdl.source_distribution_id_num_1 = rctg.cust_trx_line_gl_dist_id
   AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;