Monday 24 September 2018

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'

2 comments:

  1. Optimize your website for SEO purposes and include the relevant keywords in your meta titles and listing descriptions. Keyword research is important. tax invoice template NZ

    ReplyDelete
  2. As the previous discussion on occasion-segmentation revealed, a consumer's choice of a casino visit can sometimes compete with other entertainment/leisure time activities, including dining out. demo pragmatic play

    ReplyDelete