Reporting and Analytics for ERP

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Kunal Daya
    SQL Link between GL and Projects
    Topic posted April 4, 2019 by Kunal DayaRed Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, Fusion Project reporting, OBIEE Answers, Reports 
    644 Views, 4 Comments
    Title:
    SQL Link between GL and Projects
    Summary:
    Report which shows project transaction that have flown through to GL
    Content:

    Hi

    Does anyone know the links/joins between GL and Projects, we are trying to build a report however not all transactions are coming through?

    Your assistance is appreciated

    Thanks

    Code Snippet:

    Comment

     

    • Manish Pesswani

      Refer below doc id

      How To Link Projects Transactions To Subledger Accounting (SLA) (Doc ID 1274575.1)

    • Prasanna Reka

      See if below Query Helps:

      SELECT gld.name "LEDGER NAME",
             gjh.period_name "PERIOD NAME",
             gjb.name "JOURNAL BATCH",
                   gjh.name "JOURNAL NAME",
              TO_CHAR (gjh.default_effective_date, 'MM/DD/YYYY') "NATURALING DATE",
                gjs.user_je_source_name "JOURNAL SOURCE",
             gjc.user_je_category_name "JOURNAL CATEGORY",
              gjh.status "JOURNAL HDR STATUS",
             gjh.accrual_rev_flag "ACCRUAL REV FLAG",
             TO_CHAR (gjh.posted_date, 'MM/DD/YYYY') "POSTED DATE",

             gjl.je_line_num "JOURNAL LINE NUM",
             SUBSTR (NVL (REPLACE (xal.description, ' ', ''), gjl.description),
                     1,
                     160)
                "JOURNAL LINE DESC"   ,
             CC.segment1 "COM CODE",
             (SELECT VVS.description
                FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
               WHERE     1 = 1
                     AND CC.segment1 = VVS.VALUE
                     AND VVS.value_set_id = VSET.value_set_id
                     AND VSET.value_set_code = 'CHART_COM')
                "COM NAME",
             CC.segment2 "REGION CODE",
             (SELECT VVS.description
                FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
               WHERE     1 = 1
                     AND CC.segment2 = VVS.VALUE
                     AND VVS.value_set_id = VSET.value_set_id
                     AND VSET.value_set_code = 'CHART_AREA')
                "AREA NAME",
             CC.segment3 "DEPT CODE",
             (SELECT VVS.description
                FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
               WHERE     1 = 1
                     AND CC.segment3 = VVS.VALUE
                     AND VVS.value_set_id = VSET.value_set_id
                     AND VSET.value_set_code = 'CHART_COST_CENT')
                "DEPT NAME",
             CC.segment4 "NATURAL CODE",
             (SELECT VVS.description
                FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
               WHERE     1 = 1
                     AND CC.segment4 = VVS.VALUE
                     AND VVS.value_set_id = VSET.value_set_id
                     AND VSET.value_set_code = 'CHART_NATURAL')
                "NATURAL NAME",
             CC.segment5 "REGION CODE",
             (SELECT VVS.description
                FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
               WHERE     1 = 1
                     AND CC.segment5 = VVS.VALUE
                     AND VVS.value_set_id = VSET.value_set_id
                     AND VSET.value_set_code = 'CHART_REGION')
                "REGION NAME",
             CC.segment6 "IC CODE",
             (SELECT VVS.description
                FROM fnd_vs_values_vl VVS, fnd_vs_value_sets VSET
               WHERE     1 = 1
                     AND CC.segment6 = VVS.VALUE
                     AND VVS.value_set_id = VSET.value_set_id
                     AND VSET.value_set_code = 'CHART_INTERCOM')
                "INT COM NAME",
             CC.segment7 "ENTITY CODE",
             CC.segment9 "F1",
             ROUND (XDL.UNROUNDED_ENTERED_DR, 2) "ENTERED Dr",
             ROUND (XDL.UNROUNDED_ENTERED_CR, 2) "ENTERED Cr",
             ROUND (
                  NVL (XDL.UNROUNDED_ENTERED_DR, 0)
                - NVL (XDL.UNROUNDED_ENTERED_CR, 0),
                2)
                "NET ENTERED",
             ROUND (XDL.UNROUNDED_NATURALED_DR, 2) "NATURALED Dr",
             ROUND (XDL.UNROUNDED_NATURALED_CR, 2) "NATURALED Cr",
             ROUND (
                  NVL (XDL.UNROUNDED_NATURALED_DR, 0)
                - NVL (XDL.UNROUNDED_NATURALED_CR, 0),
                2)
                "NET NATURALED",
             gir.gl_sl_link_id "GL SL LINK ID",
             (SELECT transaction_number
                FROM xla_transaction_entities xte
               WHERE 1 = 1 AND xah.entity_id = xte.entity_id)
                "TRANSACTION NUMBER",
             --xe.reference_char_1 "REFERENCE_VALUE",
             xe.event_type_code "EVENT_TYPE_CODE",
              -- pei.project_id,
             ppb.segment1 "proj Num",
             ppa.name "proj Name",
             pet.expenditure_type_name "Exp Type Name",
             TO_CHAR (pei.expenditure_item_date, 'MM/DD/YYYY') "Exp Item Date",
             pec.expenditure_comment "Exp Comment",
             hro.name "Exp Organization",
             pt.element_number "Task Lowest",
             (SELECT element_number
                FROM pjf_proj_elements_b pt1
               WHERE     object_type = 'PJF_TASKS'
                     AND pt.denorm_parent_element_id = pt1.proj_element_id)
                "Task Level2",
             (SELECT element_number
                FROM pjf_proj_elements_b pt2
               WHERE     object_type = 'PJF_TASKS'
                     AND pt.denorm_top_element_id = pt2.proj_element_id)
                "Task Level3",
             NULL "Party Name",
             NULL "Party Number",
             NVL (gjl.currency_code, gjh.currency_code) "Currency Code",
             NULL "Transaction Type",
             gjh.CREATED_BY "Created By",
             NULL "Sales Order Number"
        FROM gl_ledgers gld,
             gl_je_batches gjb,
             gl_je_headers gjh,
             gl_je_lines gjl,
             gl_code_combinations gcc,
             gl_import_references gir,
             xla_ae_headers xah,
             xla_ae_lines xal,
             xla_distribution_links xdl,
             xla_transaction_entities xte,
             xla_events xe,
             pjc_exp_items_all pei,
             pjf_projects_all_b ppb,
             pjf_projects_all_tl ppa,
             pjf_exp_types_tl pet,
             pjc_exp_comments pec,
             hr_all_organization_units hro,
             pjf_proj_elements_b pt,
             gl_je_sources gjs,
             gl_je_categories gjc
        WHERE     gld.ledger_id = gjh.ledger_id
             AND gjb.je_batch_id = gjh.je_batch_id
             AND gjh.je_header_id = gjl.je_header_id
             AND gjl.code_combination_id = gcc.code_combination_id
             AND gjb.je_batch_id = gir.je_batch_id
             AND gjh.je_header_id = gir.je_header_id
             AND gjl.je_line_num = gir.je_line_num
             AND gir.gl_sl_link_id = xal.gl_sl_link_id
             AND xal.code_combination_id = gcc.code_combination_id
             AND gir.reference_7 = xal.ae_header_id
             AND xal.ae_header_id = xah.ae_header_id
             AND gir.reference_5 = xah.entity_id
             AND xal.ae_header_id = xdl.ae_header_id
             AND xal.ae_line_num = xdl.ae_line_num
             AND xah.entity_id = xte.entity_id
             AND gir.reference_6 = xah.event_id
             AND xah.event_id = xe.event_id
             AND XDL.SOURCE_DISTRIBUTION_TYPE = 'R'
             AND xdl.source_distribution_id_num_1 = pei.expenditure_item_id
             AND pei.project_id = ppb.project_id
             AND ppb.project_id = ppa.project_id
             AND pei.project_id = ppa.project_id
             AND ppa.LANGUAGE = 'US'
             AND pet.LANGUAGE = 'US'
             AND pei.expenditure_type_id = pet.expenditure_type_id
             AND pei.expenditure_item_id = pec.expenditure_item_id(+)
             AND pei.expenditure_organization_id = hro.organization_id
             AND pei.task_id = pt.proj_element_id
             AND pt.object_type = 'PJF_TASKS'
            -- AND pt.chargeable_flag = 'Y'
             AND gjh.je_source = gjs.JE_SOURCE_NAME
             AND gjh.je_category = gjc.JE_CATEGORY_NAME
             AND gjh.je_from_sla_flag IS NOT NULL
             AND gld.name IN (:LEDGER_NAME)
             AND gjh.period_name IN (:PERIOD_NAME)
             AND gcc.segment1 IN (:COM)
             AND gcc.segment2 IN (:AREA)
             AND gcc.segment3 IN (:COST CENTER)
             AND gcc.segment4 IN (:NATURAL)
             AND gcc.segment5 IN (:REGION)
             AND gcc.segment6 IN (:INTERCOM)
             AND gcc.segment7 IN (:ENTITY)
             AND gcc.segment8 IN (:LEGAL)
             AND gcc.segment9 IN (:F1)
             AND gjs.user_je_source_name IN (:JE_SOURCE)
             AND gjc.user_je_category_name IN (:JE_CATEGORY)
             AND gjh.status IN (:POSTING_STATUS)
             AND (   gjh.accrual_rev_status IN (:REVERSAL_STATUS)
                  OR gjh.accrual_rev_status IS NULL)

      • Meredith Bickell

        I copied the SQL to create a new data model and I received a "missing right parenthesis" error. I haven't been able to identify a missing parenthesis.

    • 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')

     

    community forum