Comments

  • 1-3 of 3
  • Michelle Haynes

    Hi,

    That is exactly what I'm looking to do. Thanks!

  • Michelle Haynes

    Unfortunately not, the finance team is posting receipt accounting after the GL period is already opened, so the reversal is not getting picked up (since it was posted after the period is opened). I was hoping to schedule it to catch these, so the users don't have to remember to run it manually to reverse RA.

  • Michelle Haynes

    Here's one for project adjustments, you can remove that condition to pull all project costs.

    SELECT
           pxc.project_number       as project_num
         , peia.expenditure_item_id as trx_num
         , pxc.task_number          as task_num
         , pe.name                  as task_name
         , pxc.expenditure_type --, pxc.expenditure_type_class --mhaynes updated 3.12.19 pull expenditure_type instead of expenditure_type_class per Kevin G
         , pxc.exp_organization_name as expenditure_org
         , CASE
                  WHEN pcdl.capitalizable_flag = 'Y'
                         THEN 'CAPEX'
                         ELSE 'OPEX'
           END                                              capex_opex
         , glp.fiscal_year_number                           as fiscal_year
         , glp.fiscal_period_name                           as acct_period
         , to_char(aca.check_date,'MM/DD/YYYY')             as paid_date
         , to_char(peia.creation_date,'MM/DD/YYYY')         as exp_item_create_date
         , to_char(peia.expenditure_item_date,'MM/DD/YYYY') as exp_item_date
           --, NVL(gcc.segment1, gcc_adj_dr.segment1)           as gl_company     --mhaynes added 3.12.19
         , (
                  SELECT
                         ffvt.description
                  FROM
                         fnd_flex_value_sets fvs
                       , fnd_flex_values     ffv
                       , fnd_flex_values_tl  ffvt
                  WHERE
                         fvs.flex_value_set_id       = ffv.flex_value_set_id
                         AND ffv.flex_value_id       = ffvt.flex_value_id
                         AND fvs.flex_value_set_name = 'Company BCBS COA'
                         AND ffv.flex_value          = NVL(gcc.segment1, gcc_adj_dr.segment1)
           )
                                                  as gl_company
         , NVL(gcc.segment2, gcc_adj_dr.segment2) as gl_acct        --if adjustment then ap inv distribution will be null go to pjc distribution
         , NVL(gcc.segment3, gcc_adj_dr.segment3) as gl_cost_center --if adjustment then ap inv distribution will be null go to pjc distribution
         , aia.invoice_num
         , NVL(supp.vendor_name, ppn.full_name) as vendor_name
         , (
                  SELECT
                         ppf.full_name
                  FROM
                         po_distributions_all pod
                       , per_employees_x      ppf
                  WHERE
                         pod.po_distribution_id       = dist.po_distribution_id --link to main query
                         AND pod.deliver_to_person_id = ppf.person_id
                         --AND dist.invoice_distribution_id = 276942 --cannot use there was no requester info on this line
                         AND rownum = 1
           )
                                 as employee_name
         , pcdl.project_raw_cost as amount_paid --mhaynes renamed 3.12.19 per Kevin G
           --, aia.amount_paid --mhaynes removed 3.12.19 per Kevin G
         , CASE
                  WHEN pxc.expenditure_type_class = 'Miscellaneous Transaction'
                         THEN 'Y'
                         ELSE 'N'
           END adjustment
         , pts.user_transaction_source
         , pxc.expenditure_type_class
    FROM
           pjc_exp_items_all            peia
         , pjc_cost_dist_lines_all      pcdl
         , pjc_xla_cost_v               pxc
         , pjf_proj_elements_vl         pe
         , gl_fiscal_period_v           glp
         , ap_invoices_all              aia
         , ap_invoice_lines_all         ala
         , ap_invoice_distributions_all dist
         , poz_suppliers_v              supp
         , ap_invoice_payments_all      aipa
         , ap_checks_all                aca
         , gl_code_combinations         gcc
         , gl_code_combinations         gcc_adj_cr
         , gl_code_combinations         gcc_adj_dr
         , per_person_names_f           ppn
         , pjf_txn_sources_vl           pts
    WHERE
           peia.expenditure_item_id     = pcdl.expenditure_item_id
           AND peia.expenditure_item_id = pxc.expenditure_item_id
           AND pcdl.task_id             = pe.proj_element_id
           AND trunc(pcdl.prvdr_gl_date) between fiscal_period_start_date and fiscal_period_end_date
           AND peia.original_header_id       = aia.invoice_id (+)
           AND aia.invoice_id                = ala.invoice_id (+)
           AND peia.original_line_number     = ala.line_number (+)
           AND peia.original_dist_id         = dist.invoice_distribution_id (+)
           AND dist.dist_code_combination_id = gcc.code_combination_id (+)
           AND pcdl.raw_cost_cr_ccid         = gcc_adj_cr.code_combination_id (+) --adjustments
           AND pcdl.raw_cost_dr_ccid         = gcc_adj_dr.code_combination_id (+) --adjustments
           AND gcc_adj_cr.account_type (+) IN ('E'
                                             ,'R'
                                             ,'A')
           AND gcc_adj_dr.account_type (+) IN ('E'
                                             ,'R'
                                             ,'A')
           AND aia.vendor_id                    = supp.vendor_id (+)
           AND aia.invoice_id                   = aipa.invoice_id (+)
           AND aipa.check_id                    = aca.check_id (+)
           AND NVL(aipa.reversal_flag,'N')     != 'Y'
           AND NVL(aca.status_lookup_code,'N') != 'VOIDED'
           AND peia.incurred_by_person_id       = ppn.person_id (+)
           AND pxc.transaction_source           = pts.transaction_source --mhaynes added 3.27.19 requested by Brent S
           AND ppn.name_type (+)                = 'US'
           AND pxc.document_entry_name         != 'Nonrecoverable Tax'
           AND pxc.expenditure_type_class       = 'Miscellaneous Transaction' --mhaynes added 3.13.19 need to get proj exp creation date
           AND glp.adjustment_period_flag      != 'Y'
           AND peia.creation_date between NVL(ppn.effective_start_date,'1900-01-01') and NVL(ppn.effective_end_date, '4712-01-01')
           --AND aia.invoice_id             = (NVL(:p_inv_num,aia.invoice_id) OR aia.invoice_id is null)
           AND pxc.project_number        = NVL(:p_pj_num, pxc.project_number )
           AND glp.fiscal_period_name    = NVL(:p_period, glp.fiscal_period_name)
           AND peia.expenditure_item_id  = NVL(:p_trx_num,peia.expenditure_item_id)
           AND pxc.exp_organization_name = NVL(:p_exp_org, pxc.exp_organization_name) --mhaynes added 3.12.19 requested by Kevin G
           AND(NVL(TO_CHAR(peia.creation_date,'YYYY-MM-DD'),'1900-01-01') >= NVL(NVL(:p_pay_date_from, TO_CHAR(peia.creation_date,'YYYY-MM-DD')),'1900-01-01'))
           AND(NVL(TO_CHAR(peia.creation_date,'YYYY-MM-DD'),'4712-01-01') <= NVL(NVL(:p_pay_date_to, TO_CHAR(peia.creation_date,'YYYY-MM-DD')),'4712-01-01'))
           AND 'Y' = NVL(:p_inv_pay_status, 'Y')