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  

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

Query to Find Projects Not Accruing Revenue in Oracle Fusion

--**** Project Not Accruing Revenue Due to Expenditure Exception ****--

SELECT PE.accounting_period AS GL_PERIOD
      ,OKH.contract_number
      ,OKL.line_number
      ,PPA.segment1 AS project_number
      ,PPA.name AS project_name
      ,PT.task_number
      ,PT.task_name
      ,PEI.expenditure_item_id trx_number
      ,PEC.expenditure_category_name
      ,NVL(PPN.full_name, PS.vendor_name) AS full_name    
      ,NVL(PPF.person_number, PS.segment1) AS person_number    
      ,DECODE(PEI.incurred_by_person_id, NULL, 'Supplier'
                                       ,(SELECT FL.meaning FROM fnd_lookups FL
                                          WHERE FL.lookup_type = 'PJC_PERSON_TYPE'
                                            AND FL.lookup_code = PEI.person_type)
              ) AS PERSON_TYPE
      ,TRUNC(PEI.expenditure_item_date) expenditure_item_date
      ,DECODE(PEC.expenditure_category_name, 'Expenses', NULL, 'Material', NULL,
              ROUND((PE.unrec_rev_amount/((PEI.quantity/100) * (100 - NVL(PEI.revenue_recog_percentage, 0)))), 2)) AS BILL_RATE
      ,(SELECT FM.message_text FROM fnd_messages FM
         WHERE FM.message_name = PE.error_code) AS EXCEPTION
      ,ROUND(((PEI.quantity * DECODE(PEI.denom_currency_code, PE.currency_code, 1
                              ,(SELECT gdr.conversion_rate
                                  FROM gl_daily_rates gdr
                                 WHERE gdr.conversion_type = 'Corporate'
                                   AND gdr.to_currency = PE.currency_code
                                   AND gdr.from_currency = PEI.denom_currency_code
                                   AND gdr.conversion_date = PEI.expenditure_item_date))
              )/100) * ROUND(100 - NVL(PEI.revenue_recog_percentage, 0), 2)
             ,2 ) AS Quantity
      ,PE.currency_code
      ,PE.unrec_rev_amount AS amount
      ,HAOU1.name exp_organization_name
      ,HAOU3.name contract_org 
  FROM (SELECT PER.expenditure_item_id, PER.contract_id, PER.contract_line_id, PER.major_version
              ,PER.currency_code, PER.unrec_rev_amount, PER.error_code, PER.accounting_period, PER.error_id
          FROM pjb_errors PER
         WHERE PER.erroring_process      = 'REVENUE_GEN'
           AND PER.transaction_type_code = 'EI'
           AND PER.billing_type_code     = 'EX'
           AND PER.unrec_rev_amount <> 0
           AND PER.txn_date <= :P_AS_OF_DATE       
         ) PE
      ,pjc_exp_items_all PEI
      ,pjf_projects_all_vl PPA
      ,pjf_tasks_v PT
      ,okc_k_headers_all_b OKH
      ,okc_k_lines_b OKL
      ,pjf_txn_sources_vl PTS
      ,hr_operating_units HOU
      ,pjf_exp_types_vl PET
      ,pjf_exp_categories_vl PEC
      ,per_person_names_f PPN
      ,per_all_people_f PPF
      ,poz_suppliers_v PS
      ,hr_all_organization_units HAOU1
      ,hr_all_organization_units HAOU3
 WHERE PE.expenditure_item_id      = PEI.expenditure_item_id
   AND PEI.project_id              = PPA.project_id
   AND PPA.project_status_code     <> 'CLOSED'                                    -->    Other than closed Projects
   AND PEI.task_id                 = PT.task_id
   AND PE.contract_id              = OKH.id
   AND PE.major_version            = OKH.major_version
   AND :P_AS_OF_DATE BETWEEN OKH.start_date AND NVL(OKH.end_date, SYSDATE)
   AND PE.contract_line_id         = OKL.id
   AND PE.major_version            = OKL.major_version
   AND :P_AS_OF_DATE BETWEEN OKL.start_date AND NVL(OKL.end_date, SYSDATE)
   AND OKH.sts_code                <> 'EXPIRED'                                    -->    Other than closed Contracts 
   AND NVL(PEI.revenue_recognized_flag, 'X') <> 'F'                                    -->    Not Fully Recognized
   AND NVL(PEI.billable_flag, 'X')           = 'Y'                                                -->    Only Billable Transactions 
   AND PEI.expenditure_item_date  <= :P_AS_OF_DATE
   AND PEI.transaction_source_id   = PTS.transaction_source_id 
   AND OKH.org_id                  = HOU.organization_id
   AND PEI.expenditure_type_id     = PET.expenditure_type_id
   AND PET.expenditure_category_id = PEC.expenditure_category_id
   AND PEI.incurred_by_person_id   = PPN.person_id (+)
   AND PEI.incurred_by_person_id   = PPF.person_id (+)
   AND PPN.name_type (+)           = 'GLOBAL'
   AND PEI.expenditure_item_date BETWEEN NVL(PPN.effective_start_date, PEI.expenditure_item_date) AND NVL (PPN.effective_end_date, PEI.expenditure_item_date)
   AND PEI.expenditure_item_date BETWEEN NVL(PPF.effective_start_date, PEI.expenditure_item_date) AND NVL (PPF.effective_end_date, PEI.expenditure_item_date)
   AND PEI.vendor_id               = PS.vendor_id (+)
   AND PEI.expenditure_organization_id  = HAOU1.organization_id
   AND OKH.owning_org_id                = HAOU3.organization_id 
   AND (PE.error_id, OKH.major_version) IN (SELECT MAX(PE2.error_id),MAX(PE2.major_version) FROM pjb_errors PE2
                       WHERE PE2.expenditure_item_id = PE.expenditure_item_id
                         AND PE2.erroring_process      = 'REVENUE_GEN'
                         AND PE2.transaction_type_code = 'EI'
                         AND PE2.billing_type_code     = 'EX'
                         AND PE2.unrec_rev_amount      <> 0
                         AND PE2.txn_date              <= :P_AS_OF_DATE
                         )

--**** Project Not Accruing Revenue Due to Event Exception ****--

SELECT (SELECT PERIOD_NAME
          FROM gl_periods GP
              ,gl_ledgers GL
         WHERE GP.period_set_name = GL.period_set_name
           AND GL.ledger_id       = HOU.set_of_books_id
           AND GP.adjustment_period_flag = 'N'
           AND PEI.completion_date BETWEEN GP.start_date AND GP.end_date) AS GL_PERIOD
      ,HOU.name
      ,OKH.contract_number
      ,OKL.line_number
      ,PPA.segment1 AS project_number
      ,PPA.name AS project_name
      ,PT.task_number
      ,PT.task_name
      ,PEI.event_num
      ,TRUNC(PEI.completion_date) expenditure_item_date
      ,(SELECT FM.message_text FROM fnd_messages FM
         WHERE FM.message_name = PE.error_code) AS EXCEPTION
      ,PE.currency_code
      ,((PE.unrec_rev_amount/(100-NVL(PEI.revenue_recognzd_percentage, 0))) * 100) AS ORIG_AMOUNT
      ,PE.unrec_rev_amount AS amount
      ,HAOU3.name contract_org                 
  FROM pjb_errors PE
      ,pjb_billing_events PEI
      ,pjf_projects_all_vl PPA
      ,pjf_tasks_v PT
      ,okc_k_headers_all_b OKH
      ,okc_k_lines_b OKL    
      ,hr_operating_units HOU                
      ,hr_all_organization_units HAOU1
      ,hr_all_organization_units HAOU3
      ,pjf_event_types_vl PET
 WHERE PE.event_id              = PEI.event_id
   AND PE.erroring_process      = 'REVENUE_GEN'
   AND PE.transaction_type_code = 'EVT'
   AND PEI.project_id           = PPA.project_id (+)
   AND PPA.project_status_code  <> 'CLOSED'                                    -->    Other than closed Projects
   AND PEI.task_id              = PT.task_id (+)
   AND PE.contract_id           = OKH.id
   AND PE.major_version         = OKH.major_version
   AND :P_AS_OF_DATE BETWEEN OKH.start_date AND NVL(OKH.end_date, SYSDATE)
   AND PE.contract_line_id         = OKL.id
   AND PE.major_version            = OKL.major_version
   AND :P_AS_OF_DATE BETWEEN OKL.start_date AND NVL(OKL.end_date, SYSDATE)
   AND OKH.sts_code                <> 'EXPIRED'                                    -->    Other than closed Contracts 
   AND NVL(PEI.revenue_recognzd_flag, 'X')   <> 'F'                                    -->    Not Fully Recognized 
   AND NVL(PEI.event_type_code, 'X')         = 'R'
   AND PE.billing_type_code                  = 'EX'                                          --> External Billing Type
   AND PEI.completion_date                   <= :P_AS_OF_DATE
   AND PEI.event_type_id                     = PET.event_type_id    
   AND OKH.org_id                            = HOU.organization_id
   AND PEI.organization_id                   = HAOU1.organization_id
   AND OKH.owning_org_id                     = HAOU3.organization_id

EBS to FUSION PA (Project Accounting) Table Changes

EBS Tables -----------------------------------------	FUSION Tables -------------------------------------------
PA_PROJECTS_ALL -----------------------------------------	PJF_PROJECTS_ALL_B PJF_PROJECTS_ALL_TL -------------------------------------------
PA_PROJECT_TYPES_ALL -----------------------------------------	PJF_PROJECT_TYPES_B PJF_PROJECT_TYPES_TL -------------------------------------------
PA_PROJECT_STATUSES -----------------------------------------	PJF_PROJECT_STATUSES_B PJF_PROJECT_STATUSES_TL -------------------------------------------
PA_PROJECT_STATUS_CONTROLS -----------------------------------------	PJF_PROJ_STATUS_CONTROLS -------------------------------------------
PA_PROJECT_ACCUM_ACTUALS -----------------------------------------	PJO_PROJECT_PLAN_ACTUALS -------------------------------------------
PA_TASKS -----------------------------------------	PJF_PROJ_ELEMENTS_B PJF_PROJ_ELEMENTS_TL PJF_TASKS_V -------------------------------------------
PA_BUDGET_TYPES -----------------------------------------	PJO_PLAN_TYPES_B -------------------------------------------
PA_EXPENDITURE_TYPES -----------------------------------------	PJF_EXP_TYPES_B PJF_EXP_TYPES_B_ST PJF_EXP_TYPES_TL PJF_EXP_CATEGORIES_B  -------------------------------------------
PA_EXPENDITURE_ITEMS_ALL -----------------------------------------	PJC_EXP_ITEMS_ALL -------------------------------------------
PA_COST_DISTRIBUTION_LINES_ALL  -----------------------------------------	PJC_COST_DIST_LINES_ALL -------------------------------------------
PA_TRANSACTION_SOURCES -----------------------------------------	PJF_TXN_SOURCES_B PJF_TXN_SOURCES_TL -------------------------------------------
PA_IMPLEMENTATIONS_ALL -----------------------------------------	PJF_BU_IMPL_ALL -------------------------------------------
PA_EXPENDITURE_COMMENTS -----------------------------------------	PJC_EXP_COMMENTS -------------------------------------------
PA_CUST_REV_DIST_LINES_ALL -----------------------------------------	PJB_REV_DISTRIBUTIONS -------------------------------------------
PA_TASKS -----------------------------------------	PJF_TASKS_V -------------------------------------------
PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURES_ALL -----------------------------------------	PJC_EXP_ITEMS_ALL -------------------------------------------
PA_EXPENDITURE_TYPES -----------------------------------------	PJF_EXP_TYPES_B PJF_EXP_TYPES_B_ST PJF_EXP_TYPES_TL -------------------------------------------
PA_EVENTS -----------------------------------------	PJB_BILLING_EVENTS -------------------------------------------
PA_PROJECT_PARTIES -----------------------------------------	PJF_PROJECT_PARTIES -------------------------------------------
PA_PROJECT_ROLE_TYPES_B -----------------------------------------	PJF_PROJ_ROLE_TYPES_B -------------------------------------------