SGA Details - JROD COPY

/******************************************************************************************************
 This section provides details from AP for NON-Employee related invoices.
*******************************************************************************************************/
SELECT (SELECT period_name
          FROM gl_periods
         WHERE apd.accounting_date BETWEEN start_date AND end_date
           AND period_set_name = 'THG 4/4/5'
           AND period_type = '4_4_57130283831'
           AND adjustment_period_flag = 'N') gl_period,
       (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
         WHERE period_type = '4_4_57130283831'
           --'22'                         -- PA Periods
           AND TRUNC (apd.accounting_date) BETWEEN start_date AND end_date
           AND adjustment_period_flag = 'N') planning_week,
       fv.description expenditure_organization, ai.project_id project_id,
       NVL ((SELECT p.segment1
               FROM pjf_projects_all_vl p
              WHERE p.project_id(+) = ai.project_id), 'N/A') project_number,
       NVL ((SELECT p.NAME
               FROM pjf_projects_all_vl p
              WHERE project_id(+) = ai.project_id), 'N/A') project_name,
       NVL ((SELECT pt.project_type
               FROM pjf_project_types_tl pt, pjf_projects_all_vl p
              WHERE p.project_id(+) = ai.project_id
                AND p.project_type_id = pt.project_type_id),
            'N/A'
           ) project_type_class_code,
       NVL (pozs.vendor_name, 'N/A') employee_vendor,
       NVL
          ((SELECT per.person_number
              FROM per_all_people_f per
             WHERE per.person_id = pozs.party_id
               AND apd.accounting_date BETWEEN per.effective_start_date
                                           AND per.effective_end_date),
           'N/A'
          ) employee_number,
       'AP Invoice' task_name, 'AP Invoice' expenditure_category,
       'AP Invoice' expenditure_type,
                                              /*DECODE (TO_CHAR (apd.accounting_date, 'DAY'),
                                                      'FRIDAY   ', apd.accounting_date,
                                                      NEXT_DAY (apd.accounting_date, 'Friday')
                                                     ) week_ending,*/
        NULL week_ending,
                                     -- Spaces added to account for padding done by DAY formatting option
                                     apd.accounting_date expenditure_date,
       ai.invoice_currency_code denom_currency_code, apd.amount,
       DECODE (ai.invoice_currency_code,
               sob.currency_code, NVL (apd.amount, 0),
               NVL (apd.amount, 0) * NVL (apd.exchange_rate, 1)
              ) accounted_amount,                          -- accounted_amount
       apd.exchange_rate accounted_exchange_rate,
       apd.exchange_rate_type accounted_exchange_type,
         DECODE (ai.invoice_currency_code,
                 sob.currency_code, NVL (apd.amount, 0),
                 NVL (apd.amount, 0) * NVL (apd.exchange_rate, 1)
                )
       * DECODE
               (sob.currency_code,
                'USD', 1,
                (SELECT NVL (avg_rate, 0)
                   FROM gl_translation_rates
                  WHERE set_of_books_id = sob.set_of_books_id
                    AND period_name =
                           (SELECT period_name
                              FROM gl_periods
                             WHERE apd.accounting_date BETWEEN start_date
                                                           AND end_date
                               AND period_set_name = 'THG 4/4/5'
                               AND period_type = '4_4_57130283831'
                               AND adjustment_period_flag = 'N')
                    AND to_currency_code = 'USD')
               ) usd_amount,
       (SELECT NVL (avg_rate, 0)
          FROM gl_translation_rates
         WHERE set_of_books_id = sob.set_of_books_id
           AND period_name =
                  (SELECT period_name
                     FROM gl_periods
                    WHERE apd.accounting_date BETWEEN start_date AND end_date
                      AND period_set_name = 'THG 4/4/5'
                      AND period_type = '4_4_57130283831'
                      AND adjustment_period_flag = 'N')
           AND to_currency_code = 'USD') usd_conversion_rate,
       apd.accounting_date conversion_date, sob.NAME set_of_books_name,
       sob.currency_code book_currency_code, gcc.segment1 company,
       gcc.segment2 ACCOUNT, gcc.segment3 department, gcc.segment4 region,
       gcc.segment5 FUNCTION, gcc.segment6 intercompany,
       apd.description comments, ai.invoice_num ap_invoice_number,
       'AP' subledger_name
  FROM ap_invoices_all ai,
       ap_invoice_distributions_all apd,
       poz_suppliers_v pozs,
       gl_code_combinations gcc,
       gl_sets_of_books sob,
       fnd_flex_values_vl fv,
       --FND_VS_VALUES_TL fv,
       fnd_vs_value_sets fvs
 WHERE 1 = 1
   AND ai.invoice_id = apd.invoice_id
   AND ai.vendor_id = pozs.vendor_id
   AND apd.dist_code_combination_id = gcc.code_combination_id
   AND fv.flex_value_set_id = fvs.value_set_id
   AND fvs.description = 'Department THG'               --'ANSR_GL_DEPARTMENT'
   AND fv.flex_value = gcc.segment3
   AND pozs.vendor_type_lookup_code != 'EMPLOYEE'
   AND sob.chart_of_accounts_id = gcc.chart_of_accounts_id
   AND ai.set_of_books_id = sob.set_of_books_id
UNION ALL
/******************************************************************************************************
            This section provides details from AP where the invoice is for an Employee but have not been coded to
            a project.  This is usually an entry error.
*******************************************************************************************************/
SELECT (SELECT period_name
          FROM gl_periods
         WHERE apd.accounting_date BETWEEN start_date AND end_date
           AND period_set_name = 'THG 4/4/5'
           AND period_type = '4_4_57130283831'
           AND adjustment_period_flag = 'N') gl_period,
       (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
         WHERE period_type = '4_4_57130283831'
           --'22'                         -- PA Periods
           AND TRUNC (apd.accounting_date) BETWEEN start_date AND end_date
           AND adjustment_period_flag = 'N') planning_week,
       fv.description expenditure_organization, ai.project_id project_id,
       NVL ((SELECT p.segment1
               FROM pjf_projects_all_vl p
              WHERE p.project_id(+) = ai.project_id), 'N/A') project_number,
       NVL ((SELECT p.NAME
               FROM pjf_projects_all_vl p
              WHERE project_id(+) = ai.project_id), 'N/A') project_name,
       NULL project_type_class_code,
       pozs.vendor_name employee_vendor,
       NVL
          ((SELECT per.person_number
              FROM per_all_people_f per
             WHERE per.person_id = pozs.party_id
               AND apd.accounting_date BETWEEN per.effective_start_date
                                           AND per.effective_end_date),
           'N/A'
          ) employee_number,
       'AP Invoice' task_name, 'AP Invoice' expenditure_category,
       'AP Invoice' expenditure_type,
                                              /*DECODE (TO_CHAR (apd.accounting_date, 'DAY'),
                                                      'FRIDAY   ', apd.accounting_date,
                                                      NEXT_DAY (apd.accounting_date, 'Friday')
                                                     ) week_ending,*/
        NULL week_ending,
                                     -- Spaces added to account for padding done by DAY formatting option
                                     apd.accounting_date expenditure_date,
       ai.invoice_currency_code denom_currency_code, apd.amount,
       DECODE (ai.invoice_currency_code,
               sob.currency_code, NVL (apd.amount, 0),
               NVL (apd.amount, 0) * NVL (apd.exchange_rate, 1)
              ) accounted_amount,                          -- accounted_amount
       apd.exchange_rate accounted_exchange_rate,
       apd.exchange_rate_type accounted_exchange_type,
         DECODE (ai.invoice_currency_code,
                 sob.currency_code, NVL (apd.amount, 0),
                 NVL (apd.amount, 0) * NVL (apd.exchange_rate, 1)
                )
       * DECODE
               (sob.currency_code,
                'USD', 1,
                (SELECT NVL (avg_rate, 0)
                   FROM gl_translation_rates
                  WHERE set_of_books_id = sob.set_of_books_id
                    AND period_name =
                           (SELECT period_name
                              FROM gl_periods
                             WHERE apd.accounting_date BETWEEN start_date
                                                           AND end_date
                               AND period_set_name = 'THG 4/4/5'
                               AND period_type = '4_4_57130283831'
                               AND adjustment_period_flag = 'N')
                    AND to_currency_code = 'USD')
               ) usd_amount,
       (SELECT NVL (avg_rate, 0)
          FROM gl_translation_rates
         WHERE set_of_books_id = sob.set_of_books_id
           AND period_name =
                  (SELECT period_name
                     FROM gl_periods
                    WHERE apd.accounting_date BETWEEN start_date AND end_date
                      AND period_set_name = 'THG 4/4/5'
                      AND period_type = '4_4_57130283831'
                      AND adjustment_period_flag = 'N')
           AND to_currency_code = 'USD') usd_conversion_rate,
       apd.accounting_date conversion_date, sob.NAME set_of_books_name,
       sob.currency_code book_currency_code, gcc.segment1 company,
       gcc.segment2 ACCOUNT, gcc.segment3 department, gcc.segment4 region,
       gcc.segment5 FUNCTION, gcc.segment6 intercompany,
       apd.description comments, ai.invoice_num ap_invoice_number,
       'AP' subledger_name
  FROM ap_invoices_all ai,
       ap_invoice_distributions_all apd,
       poz_suppliers_v pozs,
       gl_code_combinations gcc,
       gl_sets_of_books sob,
       fnd_flex_values_vl fv,
       --FND_VS_VALUES_TL fv,
       fnd_vs_value_sets fvs
 WHERE 1 = 1
   AND ai.invoice_id = apd.invoice_id
   AND ai.vendor_id = pozs.vendor_id
   AND apd.dist_code_combination_id = gcc.code_combination_id
   AND fv.flex_value_set_id = fvs.value_set_id
   AND fvs.description = 'Department THG'               --'ANSR_GL_DEPARTMENT'
   AND fv.flex_value = gcc.segment3
   AND pozs.vendor_type_lookup_code = 'EMPLOYEE'
   AND apd.project_id IS NULL
   AND sob.chart_of_accounts_id = gcc.chart_of_accounts_id
   AND ai.set_of_books_id = sob.set_of_books_id
UNION ALL
           /******************************************************************************************************
            This section provides details from GL for journals entered mainly manually.  Explicitly excludes
            Payables, Projects, and Consolidation sources to avoid double counting.
           *******************************************************************************************************/
(SELECT jl.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'
                       )
          FROM gl_periods
         WHERE period_type = '4_4_57130283831'
           --'22'                         -- PA Periods
           AND TRUNC (jl.effective_date) BETWEEN start_date AND end_date
           AND adjustment_period_flag = 'N') planning_week,
                   fv.description expenditure_organization, 0 project_id,
                   'N/A' project_number, 'N/A' project_name,
                   'N/A' project_type_class_code, 'N/A' employee_vendor,
                   'N/A' employee_number, 'N/A' task_name, jh.NAME,
                   jl.description,
                   DECODE (TO_CHAR (jl.effective_date, 'DAY'),
                           'FRIDAY   ', jl.effective_date,
                           NEXT_DAY (jl.effective_date, 'Friday')
                          ) week_ending,
          -- Spaces added to account for padding done by DAY formatting option
                   jl.effective_date, jh.currency_code,
                   NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0) amount,
                     NVL (jl.accounted_dr, 0)
                   - NVL (jl.accounted_cr, 0) accounted_amount,
                   jh.currency_conversion_rate accounted_exchange_rate,
                   jh.currency_conversion_type accounted_exchange_type,
                   DECODE
                      (sob.currency_code,                  --jh.currency_code,
                       'USD', (  NVL (jl.accounted_dr, 0)
                               - NVL (jl.accounted_cr, 0)),
                       NVL
                          ((  (  NVL (jl.accounted_dr, 0)
                               - NVL (jl.accounted_cr, 0)
                              )
                            * (SELECT NVL (avg_rate, 0)
                   FROM gl_translation_rates
                  WHERE set_of_books_id = sob.set_of_books_id
                    AND period_name =
                           (SELECT period_name
                              FROM gl_periods
                             WHERE jl.effective_date BETWEEN start_date
                                                           AND end_date
                               AND period_set_name = 'THG 4/4/5'
                               AND period_type = '4_4_57130283831'
                               AND adjustment_period_flag = 'N')
                    AND to_currency_code = 'USD'
                              )
                           ),
                           0
                          )
                      ) usd_amount,
                   (SELECT NVL (avg_rate, 0)
          FROM gl_translation_rates
         WHERE set_of_books_id = sob.set_of_books_id
           AND period_name =
                  (SELECT period_name
                     FROM gl_periods
                    WHERE jl.effective_date BETWEEN start_date AND end_date
                      AND period_set_name = 'THG 4/4/5'
                      AND period_type = '4_4_57130283831'
                      AND adjustment_period_flag = 'N')
           AND to_currency_code = 'USD') usd_conversion_rate,
                   jl.effective_date conversion_date,
                   sob.NAME set_of_books_name,
                   sob.currency_code book_currency_code, gl.segment1 company,
                   gl.segment2 ACCOUNT, gl.segment3 department,
                   gl.segment4 region, gl.segment5 FUNCTION,
                   gl.segment6 intercompany, jl.description,
                   NULL ap_invoice_number, 'GL' subledger_name
              FROM gl_je_headers jh,
                   gl_je_lines jl,
                   gl_code_combinations gl,
                   gl_sets_of_books sob,
                   fnd_flex_values_vl fv,
   --FND_VS_VALUES_TL fv,
   fnd_vs_value_sets fvs
             WHERE jl.je_header_id = jh.je_header_id
               AND fv.flex_value_set_id = fvs.value_set_id
   AND fvs.description = 'Department THG'               --'ANSR_GL_DEPARTMENT'
   AND fv.flex_value = gl.segment3
               AND jh.je_source NOT IN
                                    ('Consolidation', 'Payables', 'Projects')
               AND jl.code_combination_id = gl.code_combination_id
               AND sob.chart_of_accounts_id = gl.chart_of_accounts_id
               AND jh.ledger_id = sob.set_of_books_id
               AND jh.period_name = jl.period_name)