AP Supplier Invoices query - FUSION

AP Supplier Invoices query - FUSION

SELECT gp.period_name,
       (SELECT    'Q'
               || quarter_num
               || '-WK'
               || LPAD (TO_CHAR (DECODE (period_num,
                                         53, 14,
                                         DECODE (MOD (period_num, 13),
                                                 0, 13,
                                                 MOD (period_num, 13)
                                                )
                                        )
                                ),
                        2,
                        '0'
                       ) planning_week
          FROM gl_periods gp
         WHERE 1 = 1
           AND dist.accounting_date BETWEEN start_date AND end_date
           AND adjustment_period_flag = 'N'
           AND gp.period_set_name = gl.period_set_name) planning_week,
       (SELECT fv.description
          FROM fnd_flex_values_vl fv,
               fnd_flex_value_sets fvs
         WHERE fv.flex_value_set_id = fvs.flex_value_set_id
           AND fvs.flex_value_set_name = 'Department'
           AND fv.flex_value = gcc.segment3) expenditure_organization,
       inv.project_id project_id,
       NVL ((SELECT p.segment1
               FROM pjf_projects_all_b p
              WHERE p.project_id = inv.project_id), 'N/A') project_number,
       NVL ((SELECT p.NAME
               FROM pjf_projects_all_tl p
              WHERE project_id(+) = inv.project_id), 'N/A') project_name,
       NULL project_type_class_code, hp.party_name employee_vendor,
       DECODE (TO_CHAR (dist.accounting_date, 'DAY'),
               5, dist.accounting_date,
               NEXT_DAY (dist.accounting_date, 6)
              ) week_ending,
       dist.accounting_date expenditure_date,
       inv.invoice_currency_code entered_currency_code,
       dist.amount entered_amount,
       DECODE (inv.invoice_currency_code,
               gl.currency_code, NVL (dist.amount, 0),
               NVL (dist.amount, 0) * NVL (dist.exchange_rate, 0)
              ) accounted_amount,
       dist.exchange_rate accounted_exchange_rate,
       dist.exchange_rate_type accounted_exchange_type,
       (  DECODE (inv.invoice_currency_code,
                  gl.currency_code, NVL (dist.amount, 0),
                  NVL (dist.amount, 0) * NVL (dist.exchange_rate, 1)
                 )
        * NVL ((SELECT gdr.conversion_rate
                  FROM gl_daily_rates gdr
                 WHERE gdr.conversion_type = 'Corporate'
                   AND gdr.to_currency = 'USD'
                   AND gdr.from_currency = gl.currency_code
                   AND gdr.conversion_date = dist.accounting_date),
               1
              )
       ) usd_amount,
       NVL ((SELECT gdr.conversion_rate
               FROM gl_daily_rates gdr
              WHERE gdr.conversion_type = 'Corporate'
                AND gdr.to_currency = 'USD'
                AND gdr.from_currency = gl.currency_code
                AND gdr.conversion_date = dist.accounting_date),
            1
           ) usd_conversion_rate,
       dist.accounting_date conversion_date, gl.NAME set_of_books_name,
       gl.currency_code book_currency_code, gcc.segment1 company,
       gcc.segment2 ACCOUNT, gcc.segment3 department, gcc.segment4 region,
       gcc.segment5 FUNCTION, gcc.segment6 intercompany,
       dist.description comments, inv.invoice_num ap_invoice_number,
       gcc.segment1,
       (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 = inv.project_id) project_region_code
  FROM ap_invoice_distributions_all dist,
       ap_invoice_lines_all line,
       ap_invoices_all inv,
       gl_code_combinations gcc,
       poz_suppliers ps,
       hz_parties hp,
       gl_ledgers gl,
       gl_periods gp
 WHERE dist.invoice_id = line.invoice_id
   AND dist.invoice_line_number = line.line_number
   AND line.invoice_id = inv.invoice_id
   AND dist.dist_code_combination_id = gcc.code_combination_id
   AND inv.vendor_id = ps.vendor_id
   AND hp.party_id = ps.party_id
   AND inv.set_of_books_id = gl.ledger_id
   AND gp.period_set_name = gl.period_set_name
   AND dist.accounting_date BETWEEN gp.start_date AND gp.end_date
   AND gp.adjustment_period_flag = 'N'