Monday, 4 March 2019

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)

1 comment: