Query To Find Contracts With Revenue on Hold in Oracle Fusion

SELECT PPA.segment1 project_number
    ,OCTV.name contracty_type
    ,PPA.name project_name
    ,HOU.name organization_name
    ,PPA.project_currency_code
    ,TO_CHAR (PPA.completion_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') completion_date
    ,ROUND((NVL((INV.cont_curr_billed_amt), 0)), 2) project_to_date_Invoiced_sum
    ,ROUND((NVL((INV_UNRECOG.cont_curr_billed_amt), 0)), 2) Draft_invoice_amount
    ,ROUND(NVL((SELECT SUM(PNAR_AMOUNT)
                  FROM (
                        SELECT ((PEI.DENOM_RAW_COST/100) * (100 - NVL(PEI.REVENUE_RECOG_PERCENTAGE, 0))) PNAR_AMOUNT
                          FROM PJC_EXP_ITEMS_ALL PEI
                         WHERE PEI.project_id = PPA.project_id
                           AND PEI.REVENUE_RECOGNIZED_FLAG <> 'F'
                        UNION ALL
                        SELECT ((PBE.BILL_TRNS_AMOUNT/100) * (100 - NVL(PBE.REVENUE_RECOGNZD_PERCENTAGE, 0))) PNAR_AMOUNT
                          FROM PJB_BILLING_EVENTS PBE
                         WHERE PBE.project_id = PPA.project_id
                           AND PBE.REVENUE_RECOGNZD_FLAG <> 'F'
                        )
     ), 0), 2) PNAR_AMOUNT
FROM pjf_projects_all_vl PPA
,pjb_cntrct_proj_links PCPL
,okc_k_lines_b OKL
,okc_k_headers_all_b OKH
,okc_contract_types_vl OCTV
,pjf_project_types_vl PPT
,hr_all_organization_units HOU
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
        ,PILD.contract_id, PILD.contract_line_id
    FROM pjb_inv_line_dists PILD
        ,pjb_invoice_headers PIH
   WHERE 1=1
     AND PILD.invoice_id = PIH.invoice_id
     AND PIH.transfer_status_code = 'A'
     AND PIH.gl_date <= :P_AS_OF_DATE
   GROUP BY PILD.contract_id, PILD.contract_line_id
     ) INV
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
        ,PILD.contract_id, PILD.contract_line_id
    FROM pjb_inv_line_dists PILD
        ,pjb_invoice_headers PIH
   WHERE 1=1
     AND PILD.invoice_id = PIH.invoice_id
     AND PIH.transfer_status_code <> 'A'
     AND PIH.gl_date <= :P_AS_OF_DATE
   GROUP BY PILD.contract_id, PILD.contract_line_id
     ) INV_UNRECOG
WHERE 1=1
and PPA.project_id = PCPL.project_id
AND PCPL.version_type = 'C' -- Current
AND PCPL.contract_line_id = OKL.id
AND PCPL.major_version = OKL.major_version
AND OKL.chr_id = OKH.id
AND OKL.major_version = OKH.major_version
AND OKL.version_type = 'C' -- Current
AND OKH.version_type = 'C' -- Current
AND OKH.contract_type_id = OCTV.contract_type_id
AND PPA.project_type_id = PPT.project_type_id
AND HOU.organization_id = PPA.carrying_out_organization_id
AND OKH.sts_code <> 'DRAFT'
AND PPT.project_type NOT IN ('INTERCOMPANY', 'Intercompany')
AND INV.contract_id (+) = OKH.id
AND INV.contract_line_id (+) = OKL.id
AND INV_UNRECOG.contract_id (+) = OKH.id
AND INV_UNRECOG.contract_line_id (+) = OKL.id
AND EXISTS (SELECT 1 FROM pjb_bill_plans_vl PBP
                         ,pjb_billing_methods_b PBM
             WHERE PBP.contract_id  = OKH.id
               AND PBP.bill_method_id = PBM.bill_method_id
               AND PBM.bill_method_flag = 'R'
               AND PBP.version_type = 'C'
               AND PBP.on_hold_Flag = 'Y')

Function To Get Bill Rate Of Employee in Specific Project in Oracle Fusion

FUNCTION get_bill_rate (
      p_project_id IN NUMBER,    
      p_person_id  IN NUMBER
   )
RETURN NUMBER
AS
  ln_bill_rate NUMBER;
BEGIN 

  SELECT DISTINCT bro.rate
    INTO ln_bill_rate
    FROM pjb_bill_plans_b bpb,
         pjb_bill_rate_ovrrds bro,
         per_all_people_f_v ppn,
         (SELECT DISTINCT pcb.contract_id, pcb.project_id, ppb.segment1 AS project_number
            FROM pjb_cntrct_proj_links pcb, pjf_projects_all_b ppb
           WHERE pcb.project_id = ppb.project_id) proj
  WHERE bpb.bill_plan_id = bro.bill_plan_id
    AND bro.person_id = ppn.person_id
    AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date
    AND TRUNC (SYSDATE) BETWEEN bro.start_date_active AND bro.end_date_active
    AND bpb.contract_id = proj.contract_id
    AND proj.project_id = p_project_id
    AND ppn.person_id = p_person_id
    AND bpb.version_type = 'C'
    AND bro.version_type = 'C';

    RETURN ln_bill_rate;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;

Query to Find AP Reimbursable Expenses in Oracle Fusion

WITH GET_PERIOD_AVG_RATE
        AS (  SELECT gper.avg_rate,
                     gsob.CURRENCY_CODE AS functional_currency,
                     gper.period_name,
                     gper.to_currency_code
                FROM GL_LOOKUPS lk,
                     GL_TRANSLATION_RATES gper,
                     gl_sets_of_books gsob
               WHERE     lk.lookup_type = 'TRANSLATION_BAL_TYPE'
                     AND gper.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID
                     AND lk.lookup_code = gper.actual_flag
            GROUP BY gper.avg_rate,
                     gsob.CURRENCY_CODE,
                     gper.period_name,
                     gper.to_currency_code)
SELECT ppat.segment1 project_num,
       ppat.name project_name,
       hou.name project_org,
       hou1.name exp_org,
       TO_CHAR (gjl.effective_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS gl_date,
       gjh.period_name,
       aid.amount invoice_amount,
       gjh.currency_code journal_currency,
       TO_CHAR (aid.pjc_expenditure_item_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS expenditure_item_date,
       gcc.segment2 ACCT,
       TO_CHAR (gjh.CURRENCY_CONVERSION_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') AS CURRENCY_CONVERSION_DATE,
       gcc.segment1 Company,
       gcc.segment3 Department,
       gcc.segment4 Region,
       aid.RECEIPT_CURRENCY_CODE,
       gjh.je_category je_category,
       HP.party_name Supplier_name,
       (SELECT PPNF.full_name
          FROM per_person_names_f PPNF
         WHERE PPNF.person_id = EI.incurred_by_person_id
               AND PPNF.name_type = 'GLOBAL'
               AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND  NVL ( PPNF.EFFECTIVE_END_DATE, TRUNC (SYSDATE) + 1))
          emp_name,
       gjl.description journal_description,
       gjl.attribute1 Journal_line_DFF,
       DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate) usd_conversion_rate,
       ROUND ( xal.accounted_dr * DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate), 2) us_accounted_dr,
       ROUND ( xal.accounted_cr * DECODE (sob.currency_code, 'USD', 1, gpavg.avg_rate), 2) us_accounted_cr,
       ei.project_currency_code,
       ei.project_burdened_cost,
  FROM ap_invoice_distributions_all AID,
       ap_invoices_all aia,
       ap_invoice_lines_all ail,
       hz_parties HP,
       PJF_PROJECTS_ALL_vl ppat,
       hr_all_organization_units hou,
       hr_all_organization_units hou1,
       xla_transaction_entities xte,
       xla_events xe,
       xla_ae_headers xah,
       xla_ae_lines xal,
       gl_import_references gir,
       gl_je_headers gjh,
       gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_sets_of_books sob,
       PJC_EXP_ITEMS_ALL ei,
       GET_PERIOD_AVG_RATE gpavg
 WHERE aia.invoice_id = ail.invoice_id
   AND aid.invoice_line_number = ail.line_number
   AND ail.invoice_id = aid.invoice_id
   AND aid.PJC_PROJECT_ID = ppat.project_id
   AND hou.organization_id = ppat.carrying_out_organization_id
   AND hou1.organization_id = aid.PJC_ORGANIZATION_ID
   AND aia.invoice_id = NVL ("SOURCE_ID_INT_1", (-99)) --19004(invoice_id)
   AND xe.entity_id = xah.entity_id
   AND aid.amount = NVL (xal.accounted_dr, xal.accounted_cr)
   AND xte.entity_code = 'AP_INVOICES'
   AND xte.application_id = 200
   AND xe.event_id = aid.ACCOUNTING_EVENT_ID
   AND xte.entity_id = xe.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND UPPER (xal.ACCOUNTING_CLASS_CODE) IN ('ITEM EXPENSE')
   AND xte.application_id = xah.application_id
    AND gcc.code_combination_id = xal.code_combination_id
   AND aid.LINE_TYPE_LOOKUP_CODE = 'ITEM'
   AND xal.gl_sl_link_id = gir.gl_sl_link_id
   AND xal.gl_sl_link_table = gir.gl_sl_link_table
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjl.je_header_id = gjh.je_header_id
   AND sob.set_of_books_id = gjh.ledger_id
   AND gjh.currency_code = gpavg.functional_currency(+)
   AND gjh.period_name = gpavg.period_name(+)
   AND gpavg.to_currency_code (+) = 'USD'
   AND aia.party_id = HP.party_id
   AND ei.vendor_id = aia.vendor_id
   AND ei.project_id = aid.pjc_project_id
   AND ei.task_id = aid.pjc_task_id
   AND aid.org_id = ei.org_id
   AND aid.amount = ei.quantity
   AND aid.invoice_id = aia.invoice_id
   AND aid.LINE_TYPE_LOOKUP_CODE = 'ITEM'
   AND ei.ORIG_TRANSACTION_REFERENCE = aid.INVOICE_DISTRIBUTION_ID  

Fusion Query to get Revenue and billable hours of a Employee

SELECT pcdl.org_id employee_cost_org_id, pcdl.prvdr_gl_period_name gl_period,
       pcdl.prvdr_pa_period_name,
       NVL (pei.override_to_organization_id,
            pei.incurred_by_organization_id
           ) expenditure_org_id,
       trx_org.NAME expenditure_org, fnbu.bu_name AS exp_bu_name,
       prb.carrying_out_organization_id project_owning_org_id,
       (SELECT NAME
          FROM hr_organization_units
         WHERE organization_id =
                          prb.carrying_out_organization_id)
                                                           project_owning_org,
       prb.project_id, prb.segment1 project_number, prl.NAME project_name,
       (SELECT s.project_status_name
          FROM pjf_project_statuses_tl s
         WHERE prb.project_status_code = s.project_status_code)
                                                               project_status,
       ptv.task_id, ptv.task_name, NVL (ptv.billable_flag, 'N') billable_task,
       NVL (pei.billable_flag, 'N') billable_expenditure_item,
       pei.expenditure_item_id, pei.expenditure_item_date,
       pei.expenditure_ending_date,
       pet.expenditure_type_name expenditure_type,
       pec.expenditure_category_name expenditure_category,
       pei.incurred_by_person_id, ppn.full_name employee_name,
       papf.person_number employee_number, rev_det.usd_revenue_amount,
       NVL (pei.project_currency_code,
            prb.project_currency_code
           ) project_currency_code,
       rev_det.revenue_curr_amt project_revenue_amount,
       NVL (pei.projfunc_currency_code,
            prb.projfunc_currency_code
           ) projfunc_currency_code,
       rev_det.ledger_curr_revenue_amt projfunc_revenue_amount,
       gcc.segment1 company, gcc.segment2 ACCOUNT, gcc.segment3 department,
       gcc.segment4 region, gcc.segment5 FUNCTION, gcc.segment6 intercompany,
       pei.bill_trans_currency_code bill_transaction_currency,
       rev_det.trns_curr_revenue_amt bill_transaction_currency_rev,
       rev_det.usd_daily_revenue_amount, pei.bill_hold_flag,
       DECODE ((SELECT '1'
                  FROM pjb_bill_trxs a
                 WHERE pei.expenditure_item_id = a.transaction_id
                   AND NVL (inv_curr_billed_amt, 0) <> 0
                   AND ROWNUM = 1),
               '1', 'Y',
               'N'
              ) billed_flag,
       prb.attribute4,
       (SELECT project_type
          FROM pjf_project_types_tl ppt
         WHERE ppt.project_type_id = prb.project_type_id) project_type,
       (SELECT class_code
          FROM pjf_project_classes prc, pjf_class_codes_tl pct
         WHERE prc.class_code_id = pct.class_code_id
           AND prc.project_id = prb.project_id) region_code,
       pcdl.org_id, rev_det.ledger_curr_revenue_amt functrevenu,
       rev_det.contract_org, rev_det.contract_status, rev_det.contract_number,
       rev_det.contract_type, rev_det.contract_currency_code,
       rev_det.cont_curr_revenue_amt, rev_det.revenue_currency_code,
       rev_det.ledger_currency_code ledger_currency_code,
       rev_det.project_curr_revenue_amt, rev_det.revenue_curr_amt,
       rev_det.ledger_curr_revenue_amt, pei.revenue_hold_flag,
       pei.person_type,
       (SELECT NAME
          FROM per_jobs_f_vl pjb
         WHERE pjb.job_id = pei.person_job_id
           AND SYSDATE BETWEEN NVL (pjb.effective_start_date, SYSDATE - 1)
                           AND NVL (pjb.effective_end_date, SYSDATE + 1)
           AND active_status = 'A') job_name,
       pei.expenditure_type_id, rev_det.bill_rate,
       pei.project_raw_cost project_currency_amount, pei.quantity,
       pei.org_id recvr_org_id, pei.burden_cost_rate, rev_det.rev_bu_name,
       (SELECT hourly_cost_rate
          FROM pjf_comp_details_all pcd
         WHERE pei.person_job_id = pcd.person_job_id(+)
           AND pcd.person_id = pei.incurred_by_person_id
           AND pei.incurred_by_organization_id = pcd.organization_id(+)
           AND SYSDATE BETWEEN NVL (pcd.start_date_active, SYSDATE - 1)
                           AND NVL (pcd.end_date_active, SYSDATE + 1))
                                                      subcontractor_cost_rate,
       (SELECT fnbu.bu_name
          FROM fun_names_business_units_v fnbu
         WHERE fnbu.bu_id = prb.org_id) project_bu_name
  FROM pjc_cost_dist_lines_all pcdl,
       pjc_exp_items_all pei,
       pjf_tasks_v ptv,
       pjf_projects_all_b prb,
       pjf_projects_all_tl prl,
       pjc_exp_comments pec1,
       pjf_exp_types_vl pet,
       pjf_exp_categories_tl pec,
       fun_names_business_units_v fnbu,
       hr_all_organization_units_tl trx_org,
       gl_ledgers gl,
       gl_code_combinations gcc,
       per_person_names_f ppn,
       per_all_people_f papf,
       (SELECT xda2.event_id, xda2.source_distribution_id_num_1,
               xda2.rounding_class_code, xda2.source_distribution_id_num_2,
               xda2.ae_line_num, xda2.ae_header_id
          FROM xla_distribution_links xda2
         WHERE xda2.ref_ae_line_num IS NULL
           AND xda2.rounding_class_code = 'RAW_COST_CLEARING'
           AND NOT EXISTS (
                  SELECT 1
                    FROM xla_distribution_links xda1
                   WHERE xda1.ref_ae_line_num = xda2.ae_line_num
                     AND xda1.ref_ae_header_id = xda2.ae_header_id)) xda,
       xla_ae_lines xal,
       pjf_txn_sources_vl ts,
       (WITH gl_translation_rates_cte AS
             (SELECT DISTINCT UPPER (r.period_name) period_name,
                              r.to_currency_code to_currency_code,
                              r.avg_rate avg_rate, r.eop_rate eop_rate,
                              s.currency_code functional_currency
                         FROM gl_translation_rates r, gl_sets_of_books s
                        WHERE s.set_of_books_id = r.set_of_books_id
                          AND r.to_currency_code = 'USD')
        SELECT   SUM
                    (DECODE
                           (prd1.ledger_currency_code,
                            'USD', NVL (prd1.ledger_curr_revenue_amt, 0),
                            (  NVL
                                  (gtrc.avg_rate,
                                   NVL
                                      ((SELECT gdr.conversion_rate
                                          FROM gl_daily_rates gdr
                                         WHERE gdr.conversion_type =
                                                                   'Corporate'
                                           AND gdr.to_currency = 'USD'
                                           AND gdr.from_currency =
                                                    prd1.revenue_currency_code
                                           AND gdr.conversion_date =
                                                                  prd1.gl_date),
                                       1
                                      )
                                  )
                             * NVL (prd1.ledger_curr_revenue_amt, 0)
                            )
                           )
                    ) usd_revenue_amount,
                 SUM
                    (DECODE ('USD',
                             prd1.revenue_currency_code, prd1.revenue_curr_amt,
                             prd1.ledger_currency_code, prd1.ledger_curr_revenue_amt,
                             prd1.contract_currency_code, prd1.cont_curr_revenue_amt,
                             prd1.project_currency_code, prd1.project_curr_revenue_amt,
                               prd1.revenue_curr_amt
                             * NVL ((SELECT gdr.conversion_rate
                                       FROM gl_daily_rates gdr
                                      WHERE gdr.conversion_type = 'Corporate'
                                        AND gdr.to_currency = 'USD'
                                        AND gdr.from_currency =
                                                    prd1.revenue_currency_code
                                        AND gdr.conversion_date = prd1.gl_date),
                                    1
                                   )
                            )
                    ) usd_daily_revenue_amount,
                 SUM (prd1.revenue_curr_amt) revenue_curr_amt,
                 SUM (prd1.ledger_curr_revenue_amt) ledger_curr_revenue_amt,
                 SUM (prd1.trns_curr_revenue_amt) trns_curr_revenue_amt,
                 SUM (prd1.cont_curr_revenue_amt) cont_curr_revenue_amt,
                 SUM (prd1.project_curr_revenue_amt) project_curr_revenue_amt,
                 prd1.bill_rate, prd1.revenue_currency_code,
                 cont_org.NAME contract_org, okh.sts_code contract_status,
                 okh.contract_number contract_number, oct.NAME contract_type,
                 prd1.contract_currency_code, prd1.trns_currency_code,
                 prd1.ledger_currency_code,
                 UPPER (prd1.gl_period_name) gl_period_name,
                 prd1.transaction_id,
                 (SELECT fnbu.bu_name
                    FROM fun_names_business_units_v fnbu
                   WHERE fnbu.bu_id = prd1.org_id) rev_bu_name
            FROM pjb_rev_distributions prd1,
                 gl_translation_rates_cte gtrc,
                 okc_k_headers_all_b okh,
                 hr_all_organization_units cont_org,
                 okc_contract_types_vl oct
           WHERE 1 = 1
             AND okh.ID = prd1.contract_id
             AND okh.version_type = 'C'
             AND okh.owning_org_id = cont_org.organization_id
             AND okh.contract_type_id = oct.contract_type_id
             AND prd1.ledger_currency_code = gtrc.functional_currency(+)
             AND UPPER (prd1.gl_period_name) = gtrc.period_name(+)
        GROUP BY prd1.trns_currency_code,
                 cont_org.NAME,
                 okh.sts_code,
                 okh.contract_number,
                 oct.NAME,
                 prd1.contract_currency_code,
                 prd1.revenue_currency_code,
                 prd1.bill_rate,
                 UPPER (prd1.gl_period_name),
                 prd1.transaction_id,
                 prd1.ledger_currency_code,
                 prd1.org_id) rev_det
 WHERE 1 = 1
   AND pei.expenditure_item_id = rev_det.transaction_id(+)
   AND UPPER (pcdl.prvdr_gl_period_name) = rev_det.gl_period_name(+)
   AND pei.transaction_source_id = ts.transaction_source_id
   AND pcdl.expenditure_item_id = pei.expenditure_item_id
   AND pei.project_id = ptv.project_id(+)
   AND pei.task_id = ptv.task_id(+)
   AND pei.project_id = prb.project_id
   AND pei.project_id = prl.project_id
   AND pei.expenditure_item_id = pec1.expenditure_item_id(+)
   AND pei.expenditure_type_id = pet.expenditure_type_id
   AND pec.expenditure_category_id = pet.expenditure_category_id
   AND fnbu.bu_id = pei.org_id
   AND fnbu.primary_ledger_id = gl.ledger_id
   AND pcdl.acct_event_id = xda.event_id(+)
   AND pcdl.expenditure_item_id = xda.source_distribution_id_num_1(+)
   AND pcdl.line_num = xda.source_distribution_id_num_2(+)
   AND xda.ae_header_id = xal.ae_header_id(+)
   AND xda.ae_line_num = xal.ae_line_num(+)
   AND gcc.code_combination_id =
          NVL (NVL (xal.code_combination_id,
                    NVL (pcdl.raw_cost_dr_ccid, pcdl.raw_cost_cr_ccid)
                   ),
               (SELECT pas.default_code_comb_id
                  FROM per_all_assignments_m pas
                 WHERE SYSDATE BETWEEN NVL (pas.effective_start_date,
                                            SYSDATE - 1
                                           )
                                   AND NVL (pas.effective_end_date,
                                            SYSDATE + 1
                                           )
                   AND pas.person_id = pei.incurred_by_person_id
                   AND pas.assignment_id = pei.hcm_assignment_id)
              )
   AND trx_org.organization_id =
          NVL (pei.override_to_organization_id,
               pei.incurred_by_organization_id
              )
   AND pcdl.line_num_reversed IS NULL
   AND pcdl.reversed_flag IS NULL
   AND ppn.person_id = pei.incurred_by_person_id
   AND SYSDATE BETWEEN NVL (ppn.effective_start_date, SYSDATE - 1)
                   AND NVL (ppn.effective_end_date, SYSDATE + 1)
   AND ppn.name_type(+) = 'GLOBAL'
   AND papf.person_id(+) = pei.incurred_by_person_id
   AND SYSDATE BETWEEN NVL (papf.effective_start_date, SYSDATE - 1)
                   AND NVL (papf.effective_end_date, SYSDATE + 1)
   AND UPPER (pcdl.prvdr_gl_period_name) IN (:p_period_name)
   AND pec.expenditure_category_name IN ('Labor')

Query to Find NonBillable Expense in Oracle Fusion

SELECT pcdl.prvdr_gl_period_name gl_period,
       trx_org.NAME expenditure_organization,
       prb.project_id,
       NVL (prb.segment1, 'N/A') project_number,
       NVL (prl.NAME, 'N/A') project_name,
       NULL project_type_class_code,
       ppn.full_name employee_vendor,
       papf.person_number employee_number,
       ptv.task_name,
       pec.expenditure_category_name expenditure_category,
       pet.expenditure_type_name expenditure_type,
       pcdl.prvdr_gl_date week_ending,
       pei.expenditure_item_date expenditure_date,
       pcdl.denom_currency_code entered_currency_code,
       NVL (pcdl.denom_raw_cost, 0) entered_amount,
       NVL (pcdl.acct_raw_cost, 0) accounted_amount,
       NVL (pcdl.acct_exchange_rate, 1) accounted_exchange_rate,
       NVL (pcdl.acct_rate_type, 'FIXED') accounted_exchange_type,
       pcdl.prvdr_gl_date conversion_date,
       gl.NAME set_of_books_name,
       gl.currency_code book_currency_code,
       pec1.expenditure_comment comments,
       pei.orig_transaction_reference ap_invoice_number,
       'PA' subledger_name,
  FROM pjc_cost_dist_lines_all pcdl,
       pjc_exp_items_all pei,
       pjf_tasks_v ptv,
       pjf_projects_all_b prb,
       pjf_projects_all_tl prl,
       pjc_exp_comments pec1,
       pjf_exp_types_vl pet,
       pjf_exp_categories_tl pec,
       fun_names_business_units_v fnbu,
       gl_ledgers gl,
       gl_code_combinations gcc,
       XLA_DISTRIBUTION_LINKS XDA,
       xla_ae_lines xal,
       hr_all_organization_units_tl trx_org,
       per_all_people_f_v papf,
       per_person_names_f ppn
 WHERE pcdl.expenditure_item_id = pei.expenditure_item_id                
   AND ptv.project_id = pei.project_id
   AND ptv.task_id = pei.task_id
   AND pei.project_id = prb.project_id
   AND pei.project_id = prl.project_id
   AND pei.expenditure_item_id = pec1.expenditure_item_id(+)
   AND pei.expenditure_type_id = pet.expenditure_type_id
   AND pec.expenditure_category_id = pet.expenditure_category_id
   AND fnbu.bu_id = pei.org_id   
   AND fnbu.primary_ledger_id = gl.ledger_id
   AND pcdl.expenditure_item_id = XDA.SOURCE_DISTRIBUTION_ID_NUM_1(+)
   AND xda.ae_line_num(+) = 2
   AND xda.ae_header_id = xal.ae_header_id(+)
   AND xda.ae_line_num = xal.ae_line_num(+)
   AND pcdl.line_num_reversed IS NULL
   AND pcdl.reversed_flag IS NULL
   AND pcdl.RAW_LINE_NUM_REVERSED IS NULL 
   AND ppn.NAME_TYPE = 'GLOBAL'
   AND NVL (pei.override_to_organization_id, pei.incurred_by_organization_id) = trx_org.organization_id
   AND pec.expenditure_category_name IN ('Expenses', 'Material')
   AND pei.incurred_by_person_id = papf.person_id
   AND pei.incurred_by_person_id = ppn.person_id
   AND SYSDATE BETWEEN NVL (papf.effective_start_date, SYSDATE - 1) AND  NVL (papf.effective_end_date, SYSDATE + 1)
   AND SYSDATE BETWEEN NVL (ppn.effective_start_date, SYSDATE - 1) AND  NVL (ppn.effective_end_date, SYSDATE + 1)

Query to Find Project Estimation To Complete in Oracle Fusion

SELECT A.PROJECT_STATUS,
       A.OPERATING_UNIT_NAME,
       A.AGREEMENT_TYPE,
       A.PROJECT_ORGANIZATION_NAME,
       A.PROJECT_NUMBER,
       A.PROJECT_NAME,
       A.START_DATE,
       A.COMPLETION_DATE,
       A.TASK_NUMBER,
       A.TASK_NAME,
       A.PROJECT_CURRENCY,
       ROUND(A.BUDGETED_REV_PROJ_CURR) BUDGETED_REV_PROJ_CURR,
       ROUND(A.ACTUAL_REV_ITD_PRJ_CURR) ACTUAL_REV_ITD_PRJ_CURR,
       ROUND(A.BILLABLE_HOURS_ACTUAL_ITD) BILLABLE_HOURS_ACTUAL_ITD,
       ROUND(A.TOTAL_HOURS_ACTUAL_ITD) TOTAL_HOURS_ACTUAL_ITD,
       A.FUNC_CURRENCY,
       ROUND(A.ACTUAL_REV_ITD_FUNC_CURR) ACTUAL_REV_ITD_FUNC_CURR,
       (ROUND (     ( (A.ACTUAL_REV_ITD_PRJ_CURR / (CASE
                                                   WHEN A.BUDGETED_REV_PROJ_CURR = 0 THEN 0.1
                                                   ELSE A.BUDGETED_REV_PROJ_CURR
                                                END)
                   ) * 100)) || ' %') AS PERC_COMPLETE_PROJ_CURR,
       (ROUND ( ( (A.ACTUAL_REV_ITD_FUNC_CURR
                 / (CASE
                       WHEN (TRUNC ( (TRUNC (NVL(GDR.conversion_rate, 1), 4) * a.Budgeted_Rev_Proj_Curr), 2)) = 0
                       THEN
                          0.1
                       ELSE
                          (TRUNC ( (TRUNC (NVL(GDR.conversion_rate, 1), 4) * a.Budgeted_Rev_Proj_Curr), 2))
                    END)) * 100))
        || ' %') AS PERC_COMPLETE_FUNC_CURR
       ,A.Contract_Status
       ,A.contract_number
       ,A.line_number                                                                      
  FROM gl_daily_rates GDR,
       (SELECT PPA.PROJECT_STATUS_CODE AS Project_Status,
               FUNB.BU_NAME AS Operating_Unit_Name,
               OTYPE.NAME AS Agreement_Type,
               haou.NAME AS project_organization_name,
               ppa.segment1 AS project_number,
               PPA.NAME AS project_name,
               TO_CHAR (ppa.start_date,
                        'DD-MON-YYYY',
                        'NLS_DATE_LANGUAGE = american')
                  AS start_date,
               TO_CHAR (ppa.completion_date,
                        'DD-MON-YYYY',
                        'NLS_DATE_LANGUAGE = american')
                  AS completion_date
              ,PT.TASK_NUMBER
              ,PT.TASK_NAME
               ,OKCH.CURRENCY_CODE AS Project_currency,
               okcl.line_amount AS Budgeted_Rev_Proj_Curr             
              ,(SELECT NVL(SUM(NVL(PRD.project_curr_revenue_amt, 0)),0) project_curr_revenue_amt
                     FROM pjb_rev_distributions PRD
                    WHERE 1=1
                      AND PRD.contract_id = OKCH.id
                      AND PRD.contract_line_id = OKCL.id) Actual_Rev_ITD_Prj_Curr
              ,(SELECT NVL (SUM (projectcostdistributionpeo.quantity), 0)
                  FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
                       pjc_exp_items_all expenditureitempeo,
                       pjf_exp_types_vl pet
                 WHERE (projectcostdistributionpeo.expenditure_item_id =
                           expenditureitempeo.expenditure_item_id
                        AND expenditureitempeo.expenditure_type_id =
                              pet.expenditure_type_id
                        AND projectcostdistributionpeo.TASK_ID = NVL(LINK.proj_element_id, projectcostdistributionpeo.TASK_ID)
                        AND pet.expenditure_type_name IN
                                 ('Professional Labor',
                                  'Contract Professional Labor')
                        AND projectcostdistributionpeo.BILLABLE_FLAG = 'Y')
                       AND expenditureitempeo.project_id = ppa.project_id)
                  AS Billable_Hours_Actual_ITD,
               (SELECT NVL (SUM (projectcostdistributionpeo.quantity), 0)
                  FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
                       pjc_exp_items_all expenditureitempeo,
                       pjf_exp_types_vl pet
                 WHERE (projectcostdistributionpeo.expenditure_item_id =
                           expenditureitempeo.expenditure_item_id
                        AND expenditureitempeo.expenditure_type_id =
                              pet.expenditure_type_id
                        AND projectcostdistributionpeo.TASK_ID = NVL(LINK.proj_element_id, projectcostdistributionpeo.TASK_ID)
                        AND pet.expenditure_type_name IN
                                 ('Professional Labor',
                                  'Contract Professional Labor'))
                       AND expenditureitempeo.project_id = ppa.project_id)
                  AS Total_Hours_Actual_ITD,
               link.proj_element_id
              ,(SELECT LEDGER_CURRENCY_CODE
                  FROM pjb_rev_distributions
                 WHERE linked_project_id = ppa.project_id AND ROWNUM < 2)
                  AS Func_Currency
                 ,(SELECT NVL(SUM(NVL(PRD.ledger_curr_revenue_amt, 0)), 0)
                     FROM pjb_rev_distributions PRD
                    WHERE 1=1
                      AND PRD.contract_id = OKCH.id
                      AND PRD.contract_line_id = OKCL.id) Actual_Rev_ITD_Func_curr     
              ,OKCL.REVENUE_IMPACT_DATE
              ,OKCH.sts_code Contract_Status                                             
              ,OKCH.contract_number
              ,PPA.project_id
              ,PPA.created_from_project_id
              ,OKCL.line_number                                                          
          FROM okc_k_headers_all_b OKCH,
               okc_k_lines_b OKCL,
               okc_contract_types_tl OTYPE,
               pjb_cntrct_proj_links LINK,
               pjf_projects_all_vl PPA,
               pjf_tasks_v PT,
               fun_all_business_units_v FUNB,
               hr_all_organization_units HAOU
         WHERE     1 = 1
           AND OKCH.id                           = OKCL.chr_id
           AND OKCH.version_type                 = 'C'
           AND OKCL.version_type                 = 'C'         
           AND OKCH.sts_code                     <> 'EXPIRED'             --= 'ACTIVE' 
           AND OKCH.id                           = LINK.contract_id
           AND OKCL.id                           = LINK.contract_line_id
           AND LINK.version_type                 = 'C'
           AND LINK.project_id                   = PPA.project_id
           AND LINK.proj_element_id              = PT.task_id (+)            
           AND PPA.carrying_out_organization_id  = HAOU.organization_id                  
           AND FUNB.BU_ID                        = OKCH.org_id
           AND OTYPE.language                    = 'US'
           AND OTYPE.contract_type_id            = OKCH.contract_type_id
           AND PPA.PROJECT_STATUS_CODE <> 'CLOSED'   ) A
 WHERE     GDR.from_currency (+)  = a.project_currency
       AND GDR.to_currency (+)  = a.Func_Currency
       AND UPPER (GDR.conversion_type (+)) = 'CORPORATE'
       AND TRUNC(GDR.conversion_date (+)) = TRUNC(a.revenue_impact_date)      
ORDER BY A.project_number, A.contract_number, A.line_number, A.task_number

Fusion Query to find Revenue Exceptions details for Expenditures

SELECT   x.ei_latest_gl_period_name, x.r_company,
         x.ei_revenue_distributed_flag, x.ei_project_name,
         x.ei_project_number, x.ei_expenditure_type,
         x.ei_expenditure_category, x.ei_employee_name,
         x.ei_expenditure_item_date, x.ei_receipt_currency_code,
         x.ei_project_currency_code, x.ei_expenditure_org_name,
         x.ei_prvdr_org_name, x.project_owning_org,
         x.billed_flag, x.task_number, x.rejection_code,
         transaction_number, billable_flag, invoice_status, revenue_status,
         employee_job, unit_of_measure,
         SUM (x.ei_project_raw_revenue) ei_project_raw_revenue,
         (SELECT SUM (prd.project_curr_revenue_amt)
            FROM pjb_rev_distributions prd
           WHERE prd.transaction_id =
                          x.transaction_number
             AND prd.line_num_reversed IS NULL
             AND prd.reversed_flag IS NULL) r_project_revenue_amount,
         SUM (x.ei_quantity) ei_quantity,
         (SELECT   prd.bill_rate
              FROM pjb_rev_distributions prd
             WHERE prd.transaction_id =
                                      x.transaction_number
               AND prd.line_num_reversed IS NULL
               AND prd.reversed_flag IS NULL
          GROUP BY prd.bill_rate) ei_bill_rate,
         contract_type_name, contract_number, contract_rev_hold_flag,
         revenue_exception_flag,contract_line_num
    FROM (
    --1. Expendituere Revenue Exception query
    SELECT pcdl.prvdr_gl_period_name ei_latest_gl_period_name,
                 gcc.segment1 r_company,
                 peia.revenue_recognized_flag ei_revenue_distributed_flag,
                 ppat.NAME ei_project_name, ppab.segment1 ei_project_number,
                 pet.expenditure_type_name ei_expenditure_type,
                 pec.expenditure_category_name ei_expenditure_category,
                 (SELECT ppn.full_name
                    FROM per_person_names_f ppn
                   WHERE ppn.person_id =
                                  peia.incurred_by_person_id
                     AND SYSDATE BETWEEN NVL (ppn.effective_start_date,
                                              SYSDATE - 1
                                             )
                                     AND NVL (ppn.effective_end_date,
                                              SYSDATE + 1
                                             )
                     AND ppn.name_type = 'GLOBAL') ei_employee_name,
                 TRUNC (peia.expenditure_item_date) ei_expenditure_item_date,
                 peia.receipt_currency_code ei_receipt_currency_code,
                 peia.project_currency_code ei_project_currency_code,
                 hr.NAME ei_expenditure_org_name, op.NAME ei_prvdr_org_name,
                 hr1.NAME project_owning_org,
                 CASE
                    WHEN peia.bill_trans_curr_rev_amt = 0
                       THEN 'N'
                    ELSE 'Y'
                 END billed_flag,
                 ptv.task_number,
                 (SELECT MESSAGE_TEXT
                    FROM fnd_messages
                   WHERE message_name = pe.ERROR_CODE) rejection_code,
                 pcdl.project_raw_cost ei_project_raw_revenue,
                 pcdl.quantity ei_quantity,
                 peia.expenditure_item_id transaction_number,
                 peia.billable_flag,
                 (SELECT flv.meaning
                    FROM fnd_lookup_values flv
                   WHERE flv.lookup_type =
                                       'PJB_EVT_INVOICED_FLAG'
                     AND lookup_code = peia.invoiced_flag) invoice_status,
                 (SELECT flv1.meaning
                    FROM fnd_lookup_values flv1
                   WHERE flv1.lookup_type =
                                    'PJB_EVT_REVENUE_RECOGNZD'
                     AND flv1.lookup_code = peia.revenue_recognized_flag)
                                                               revenue_status,
                 (SELECT pj.NAME
                    FROM per_jobs pj
                   WHERE pj.job_id = peia.person_job_id) employee_job,
                 peia.unit_of_measure,
                 pjbp.on_hold_flag contract_rev_hold_flag,
                 okct.NAME contract_type_name, okh.contract_number,
                 peia.revenue_exception_flag,
                 okl.line_number contract_line_num
            FROM pjc_exp_items_all peia,
                 pjf_exp_types_vl pet,
                 pjf_tasks_v ptv,
                 pjf_projects_all_b ppab,
                 pjf_projects_all_tl ppat,
                 hr_all_organization_units hr,
                 hr_all_organization_units hr1,
                 hr_operating_units op,
                 pjc_cost_dist_lines_all pcdl,
                 pjf_exp_categories_tl pec,
                 gl_code_combinations gcc,
                 per_person_names_f ppn,
                 --pjb_rev_distributions prd,
                 pjb_errors pe,
                 okc_k_headers_all_b okh,
                 okc_k_lines_b okl,
                 pjb_bill_plans_vl pjbp,
                 okc_contract_types_tl okct,
                 xla_distribution_links xda,
                 xla_ae_lines xal
           WHERE pet.expenditure_type_id = peia.expenditure_type_id
             AND peia.revenue_exception_flag IN ('E', 'W')
             AND pcdl.acct_source_code <> 'AP_INV'
             AND pe.contract_id = okh.ID
             AND okh.version_type = 'C'
             AND pe.contract_line_id = okl.ID
             AND okl.version_type = 'C'
             AND okl.revenue_plan_id = pjbp.bill_plan_id
             AND pjbp.version_type = 'C'
             AND okh.contract_type_id = okct.contract_type_id
             AND peia.task_id = ptv.task_id
             AND peia.project_id = ppab.project_id
             AND ppab.project_id = ppat.project_id
             AND NVL (peia.override_to_organization_id,
                      peia.incurred_by_organization_id
                     ) = hr.organization_id
             AND hr1.organization_id = ppab.carrying_out_organization_id
             AND peia.org_id = op.organization_id
             AND peia.expenditure_item_id = pcdl.expenditure_item_id
             AND pcdl.line_num_reversed IS NULL
             AND pcdl.reversed_flag IS NULL
             AND pcdl.acct_event_id = xda.event_id(+)
             AND pcdl.expenditure_item_id = xda.source_distribution_id_num_1(+)
             AND pcdl.line_num = xda.source_distribution_id_num_2(+)
             AND (   xda.rounding_class_code NOT LIKE '%CLEARING'
                  OR xda.rounding_class_code IS NULL
                 )
             AND xda.ae_header_id = xal.ae_header_id(+)
             AND xda.ae_line_num = xal.ae_line_num(+)
             AND NVL (NVL (pcdl.raw_cost_dr_ccid, pcdl.raw_cost_cr_ccid),
                      xal.code_combination_id
                     ) = gcc.code_combination_id(+)
             AND pec.expenditure_category_id = pet.expenditure_category_id
             AND ppn.person_id = peia.incurred_by_person_id
             AND SYSDATE BETWEEN NVL (ppn.effective_start_date, SYSDATE - 1)
                             AND NVL (ppn.effective_end_date, SYSDATE + 1)
             AND ppn.name_type = 'GLOBAL'
             AND pe.expenditure_item_id = peia.expenditure_item_id
             AND ppab.project_status_code = 'ACTIVE'
             AND okh.sts_code = 'ACTIVE'
             AND (   peia.revenue_recognized_flag IN ('P', 'U')
                  OR (    revenue_exception_flag = 'W'
                      AND peia.revenue_recognized_flag = 'F'
                     )
                 )
             AND peia.billable_flag = 'Y'
             AND pe.request_id IN (
                    SELECT MAX (pe1.request_id)
                      FROM pjb_errors pe1
                     WHERE pe1.expenditure_item_id = pe.expenditure_item_id
                       AND pe1.erroring_process = 'REVENUE_GEN')
          UNION ALL
          --1. Expendituere coming from AP Revenue Exception query
          SELECT pcdl.prvdr_gl_period_name ei_latest_gl_period_name,
                 gcc.segment1 r_company,
                 peia.revenue_recognized_flag ei_revenue_distributed_flag,
                 ppat.NAME ei_project_name, ppab.segment1 ei_project_number,
                 pet.expenditure_type_name ei_expenditure_type,
                 pec.expenditure_category_name ei_expenditure_category,
                 NVL
                    ((SELECT ppn.full_name
                        FROM per_person_names_f ppn
                       WHERE ppn.person_id = peia.incurred_by_person_id
                         AND SYSDATE BETWEEN NVL (ppn.effective_start_date,
                                                  SYSDATE - 1
                                                 )
                                         AND NVL (ppn.effective_end_date,
                                                  SYSDATE + 1
                                                 )
                         AND ppn.name_type = 'GLOBAL'),
                     (SELECT party_name
                        FROM hz_parties hp1, ap_invoices_all api
                       WHERE hp1.party_id = api.party_id
                         AND api.invoice_id = apd.invoice_id)
                    ) ei_employee_name,
                 TRUNC (peia.expenditure_item_date) ei_expenditure_item_date,
                 peia.receipt_currency_code ei_receipt_currency_code,
                 peia.project_currency_code ei_project_currency_code,
                 hr.NAME ei_expenditure_org_name, op.NAME ei_prvdr_org_name,
                 hr1.NAME project_owning_org,
                 CASE
                    WHEN peia.bill_trans_curr_rev_amt = 0
                       THEN 'N'
                    ELSE 'Y'
                 END billed_flag,
                 ptv.task_number,
                 (SELECT MESSAGE_TEXT
                    FROM fnd_messages
                   WHERE message_name = pe.ERROR_CODE) rejection_code,
                 pcdl.project_raw_cost ei_project_raw_revenue,
                 pcdl.quantity ei_quantity,
                 peia.expenditure_item_id transaction_number,
                 peia.billable_flag,
                 (SELECT flv.meaning
                    FROM fnd_lookup_values flv
                   WHERE flv.lookup_type =
                                       'PJB_EVT_INVOICED_FLAG'
                     AND lookup_code = peia.invoiced_flag) invoice_status,
                 (SELECT flv1.meaning
                    FROM fnd_lookup_values flv1
                   WHERE flv1.lookup_type =
                                    'PJB_EVT_REVENUE_RECOGNZD'
                     AND flv1.lookup_code = peia.revenue_recognized_flag)
                                                               revenue_status,
                 (SELECT pj.NAME
                    FROM per_jobs pj
                   WHERE pj.job_id = peia.person_job_id) employee_job,
                 peia.unit_of_measure,
                 pjbp.on_hold_flag contract_rev_hold_flag,
                 okct.NAME contract_type_name, okh.contract_number,
                 peia.revenue_exception_flag
                 ,okl.line_number contract_line_num
            FROM pjc_exp_items_all peia,
                 pjf_exp_types_vl pet,
                 pjf_tasks_v ptv,
                 pjf_projects_all_b ppab,
                 pjf_projects_all_tl ppat,
                 hr_all_organization_units hr,
                 hr_all_organization_units hr1,
                 hr_operating_units op,
                 pjc_cost_dist_lines_all pcdl,
                 pjf_exp_categories_tl pec,
                 gl_code_combinations gcc,
                 pjb_errors pe,
                 okc_k_headers_all_b okh,
                 okc_k_lines_b okl,
                 pjb_bill_plans_vl pjbp,
                 okc_contract_types_tl okct,
                 xla_distribution_links xda,
                 xla_ae_lines xal,
                 ap_invoice_distributions_all apd
           WHERE pet.expenditure_type_id = peia.expenditure_type_id
             AND peia.revenue_exception_flag IN ('E', 'W')
             AND apd.invoice_distribution_id = peia.original_dist_id
             AND pe.contract_id = okh.ID
             AND okh.version_type = 'C'
             AND pe.contract_line_id = okl.ID
             AND okl.version_type = 'C'
             AND okl.revenue_plan_id = pjbp.bill_plan_id
             AND pjbp.version_type = 'C'
             AND okh.contract_type_id = okct.contract_type_id
             AND pcdl.acct_source_code = 'AP_INV'
             AND peia.task_id = ptv.task_id
             AND peia.project_id = ppab.project_id
             AND ppab.project_id = ppat.project_id
             AND NVL (peia.override_to_organization_id,
                      peia.incurred_by_organization_id
                     ) = hr.organization_id
             AND hr1.organization_id = ppab.carrying_out_organization_id
             AND peia.org_id = op.organization_id
             AND peia.expenditure_item_id = pcdl.expenditure_item_id
             AND pcdl.line_num_reversed IS NULL
             AND pcdl.reversed_flag IS NULL
             AND pcdl.acct_event_id = xda.event_id(+)
             AND pcdl.expenditure_item_id = xda.source_distribution_id_num_1(+)
             AND pcdl.line_num = xda.source_distribution_id_num_2(+)
             AND (   xda.rounding_class_code NOT LIKE '%CLEARING'
                  OR xda.rounding_class_code IS NULL
                 )
             AND xda.ae_header_id = xal.ae_header_id(+)
             AND xda.ae_line_num = xal.ae_line_num(+)
             AND NVL (apd.dist_code_combination_id, xal.code_combination_id) =
                                                                           gcc.code_combination_id(+)
             AND pec.expenditure_category_id = pet.expenditure_category_id
             AND pe.expenditure_item_id = peia.expenditure_item_id
             AND ppab.project_status_code = 'ACTIVE'
             AND okh.sts_code = 'ACTIVE'
             AND (   peia.revenue_recognized_flag IN ('P', 'U')
                  OR (    revenue_exception_flag = 'W'
                      AND peia.revenue_recognized_flag = 'F'
                     )
                 )
             AND peia.billable_flag = 'Y'
             AND pe.request_id IN (
                    SELECT MAX (pe1.request_id)
                      FROM pjb_errors pe1
                     WHERE pe1.expenditure_item_id = pe.expenditure_item_id
                       AND pe1.erroring_process = 'REVENUE_GEN')) x
GROUP BY x.ei_latest_gl_period_name,
         x.r_company,
         x.ei_revenue_distributed_flag,
         x.ei_project_name,
         x.ei_project_number,
         x.ei_expenditure_type,
         x.ei_expenditure_category,
         x.ei_employee_name,
         x.ei_expenditure_item_date,
         x.ei_receipt_currency_code,
         x.ei_project_currency_code,
         x.ei_expenditure_org_name,
         x.ei_prvdr_org_name,
         x.project_owning_org,
         x.billed_flag,
         x.task_number,
         x.rejection_code,
         transaction_number,
         billable_flag,
         invoice_status,
         revenue_status,
         employee_job,
         unit_of_measure,
         contract_rev_hold_flag,
         contract_type_name,
         contract_number,
         revenue_exception_flag
         ,contract_line_num