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