Showing posts with label GL. Show all posts
Showing posts with label GL. Show all posts

Oracle EBS Key segments value set details Query

----Key segments value set details (Values/Effective date/Hierarchy/Qualifiers)
SELECT
    ffvs.flex_value_set_name                                                                        value_set_name,
    ffv.flex_value                                                                                  value,
    ffvt.description,
    ffv.start_date_active                                                                           from_date,
    ffv.end_date_active                                                                             to_date,
    decode(ffv.enabled_flag, 'N', 'No', 'Yes')                                                         enabled,
    decode(ffv.summary_flag, 'N', 'No', 'Yes')                                                         parent,
    ffhv.hierarchy_code                                                                             rollup_group,
        -- Qualifiers
    decode(substr(to_char(ffv.compiled_value_attributes), 1, 1), 'N', 'No', 'Yes')                       budgeting_allowed,
    decode(substr(to_char(ffv.compiled_value_attributes), 3, 1), 'N', 'No', 'Yes')                       posting_allowed,
    (
    SELECT    description
FROM
    fnd_lookup_values_vl
WHERE 1=1
    AND lookup_type = 'ACCOUNT TYPE'
    AND enabled_flag = 'Y'
    AND ( view_application_id = 101 )
    AND lookup_code  = substr(to_char(ffv.compiled_value_attributes), 5, 1)
    )                                                                                               account_type,
    decode(substr(to_char(ffv.compiled_value_attributes), 7, 1), 'N', 'No', 'Yes')                       third_party_control,
    decode(substr(to_char(ffv.compiled_value_attributes), 9, 1), 'N', 'No', 'Yes')                       reconcile
FROM
    fnd_flex_values          ffv,
    fnd_flex_values_tl       ffvt,
    fnd_flex_value_sets      ffvs,
    fnd_id_flex_segments     fifs,
    fnd_flex_hierarchies_vl  ffhv
WHERE
        ffv.flex_value_id = ffvt.flex_value_id
    AND ffv.flex_value_set_id = ffvs.flex_value_set_id
    AND fifs.flex_value_set_id = ffvs.flex_value_set_id
    AND ffvs.flex_value_set_name = 'Natural Account' -- example
    AND fifs.id_flex_code = 'GL#'
    AND ffv.structured_hierarchy_level = ffhv.hierarchy_id (+)
ORDER BY
    ffv.flex_value;

Oracle R12 EBS Account Analysis Query

-------Account Analysis Query 
------------------------------------
SELECT tab1.period_name,
           ledger_name,
           tab1.ledger_id,
           account,
           project,
           product_group,
           department,
           employee,
           emp_name,
           product,
           entity,
           inter_entity,
           spare1,
           spare2,
           combinations,
           je_source,
           je_category,
           party_name,
           party_number,
           transaction_type,
           transaction_currency,
           transaction_number,
           transaction_date,
           gl_date,
           subledger_doc_no,
           doc_sequence_value,
           transaction_description,
           entered_currency,
           entered_dr,
           entered_cr,
           accounted_currency,
           accounted_dr,
           accounted_cr,
           tab1.code_combination_id,
           xx.open_balance open_balance,
           xx.sum_debit sum_debit,
           xx.sum_credit sum_credit,
           xx.closing_balance closing_balance,
           gl_cr_date
      --  ROWNUM
      FROM (SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   pv.vendor_name party_name,
                   pv.segment1 party_number,
                   NULL transaction_type,
                   xal.currency_code transaction_currency,
                   xte.transaction_number,
                   TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY')
                      transaction_date,
                   xga.gl_date,
                   gir.subledger_doc_sequence_value subledger_doc_no,
                   xga.doc_sequence_value,
                   xal.description transaction_description,
                   xal.currency_code entered_currency,
                   NVL (xal.entered_dr, 0) entered_dr,
                   NVL (xal.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_dr, 0),
                           NVL (xal.accounted_dr, 0))
                      accounted_dr, 
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_cr, 0),
                           NVL (xal.accounted_cr, 0))
                      accounted_cr,
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   gl_import_references gir,
                   xla_ae_lines xal,
                   xla_ae_headers xah,
                   xla_events xe,
                   xla.xla_transaction_entities xte,
                   po_vendors pv
             WHERE     xga.je_batch_id = gir.je_batch_id
                   AND xga.je_header_id = gir.je_header_id
                   AND xal.ledger_id = xah.ledger_id
                   AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND xga.je_header_id = gir.je_header_id
                   AND xga.je_line_num = gir.je_line_num
                   AND gir.gl_sl_link_id = xal.gl_sl_link_id
                   AND gir.gl_sl_link_table = xal.gl_sl_link_table
                   AND xal.ae_header_id = xah.ae_header_id
                   AND xal.application_id = xah.application_id
                   AND xah.event_id = xe.event_id
                   AND xah.application_id = xe.application_id
                   AND xe.entity_id = xte.entity_id
                   AND xe.application_id = xte.application_id
                   AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
                   AND xal.party_id = pv.vendor_id(+)
                   AND UPPER (xga.je_source) = 'PAYABLES'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   hp.party_name party_name,
                   hca.account_number party_number,
                   NULL transaction_type,
                   xal.currency_code transaction_currency,
                   xte.transaction_number,
                   TO_CHAR (xe.transaction_date, 'DD-Mon-YYYY')
                      transaction_date,
                   xga.gl_date,
                   gir.subledger_doc_sequence_value subledger_doc_no,
                   xga.doc_sequence_value,
                   xal.description transaction_description,
                   xal.currency_code entered_currency,
                   NVL (xal.entered_dr, 0) entered_dr,
                   NVL (xal.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_dr, 0),
                           NVL (xal.accounted_dr, 0))
                      accounted_dr, 
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_cr, 0),
                           NVL (xal.accounted_cr, 0))
                      accounted_cr, 
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   gl_import_references gir,
                   xla_ae_lines xal,
                   xla_ae_headers xah,
                   xla_events xe,
                   xla.xla_transaction_entities xte,
                   hz_cust_accounts hca,
                   hz_parties hp
             WHERE     xga.je_batch_id = gir.je_batch_id
                   AND xga.je_header_id = gir.je_header_id
                   AND xal.ledger_id = xah.ledger_id
                   AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND xga.je_header_id = gir.je_header_id
                   AND xga.je_line_num = gir.je_line_num
                   AND gir.gl_sl_link_id = xal.gl_sl_link_id
                   AND gir.gl_sl_link_table = xal.gl_sl_link_table
                   AND xal.ae_header_id = xah.ae_header_id
                   AND xal.application_id = xah.application_id
                   AND xah.event_id = xe.event_id
                   AND xah.application_id = xe.application_id
                   AND xe.entity_id = xte.entity_id
                   AND xe.application_id = xte.application_id
                   AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
                   AND xal.party_id = hca.cust_account_id(+)
                   AND hca.party_id = hp.party_id(+)
                   AND UPPER (xga.je_source) = 'RECEIVABLES'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   pv.vendor_name party_name,
                   pv.segment1 party_number,
                   rt.transaction_type transaction_type,
                   rt.currency_code transaction_currency,
                   rsh.receipt_num transaction_number,
                   TO_CHAR (rt.transaction_date, 'DD-Mon-YYYY')   transaction_date,
                   xga.gl_date,
                   NULL subledger_doc_no,
                   xga.doc_sequence_value,
                   xal.description transaction_description,
                   xal.currency_code entered_currency,
                   NVL (xal.entered_dr, 0) entered_dr,
                   NVL (xal.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_dr, 0),
                           NVL (xal.accounted_dr, 0))
                      accounted_dr,
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_cr, 0),
                           NVL (xal.accounted_cr, 0))
                      accounted_cr,
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   gl_import_references gir,
                   xla_ae_lines xal,
                   xla_ae_headers xah,
                   xla_events xe,
                   xla.xla_transaction_entities xte,
                   rcv_transactions rt,
                   rcv_shipment_headers rsh,
                   po_vendors pv
             WHERE     xga.je_batch_id = gir.je_batch_id
                   AND xga.je_header_id = gir.je_header_id
                   AND xal.ledger_id = xah.ledger_id
                   AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND xga.je_header_id = gir.je_header_id
                   AND xga.je_line_num = gir.je_line_num
                   AND gir.gl_sl_link_id = xal.gl_sl_link_id
                   AND gir.gl_sl_link_table = xal.gl_sl_link_table
                   AND xal.ae_header_id = xah.ae_header_id
                   AND xal.application_id = xah.application_id
                   AND xah.event_id = xe.event_id
                   AND xah.application_id = xe.application_id
                   AND xe.entity_id = xte.entity_id
                   AND xe.application_id = xte.application_id
                   AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
                   AND xte.source_id_int_1 = rt.transaction_id(+)
                   AND rt.shipment_header_id = rsh.shipment_header_id(+)
                   AND rsh.vendor_id = pv.vendor_id(+)
                   AND UPPER (xga.je_source) = 'COST MANAGEMENT'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   cba.bank_account_name party_name,
                   cba.bank_account_num party_number,
                   ccf.cashflow_direction transaction_type,
                   ccf.cashflow_currency_code transaction_currency,
                   TO_CHAR (ccf.trxn_reference_number) transaction_number,
                   TO_CHAR (ccf.cashflow_date, 'DD-Mon-RRRR')
                      transaction_date,
                   xga.gl_date,
                   NULL subledger_doc_no,
                   xga.doc_sequence_value,
                   ccf.description transction_description,
                   xal.currency_code entered_currency,
                   NVL (xal.entered_dr, 0) entered_dr,
                   NVL (xal.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_dr, 0),
                           NVL (xal.accounted_dr, 0))
                      accounted_dr, 
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_cr, 0),
                           NVL (xal.accounted_cr, 0))
                      accounted_cr, 
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   gl_import_references gir,
                   xla_ae_lines xal,
                   xla_ae_headers xah,
                   xla_events xe,
                   xla.xla_transaction_entities xte,
                   ce_cashflows ccf,
                   ce_bank_accounts cba
             WHERE     xga.je_batch_id = gir.je_batch_id
                   AND xga.je_header_id = gir.je_header_id
                   AND xal.ledger_id = xah.ledger_id
                   AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND xga.je_header_id = gir.je_header_id
                   AND xga.je_line_num = gir.je_line_num
                   AND gir.gl_sl_link_id = xal.gl_sl_link_id
                   AND gir.gl_sl_link_table = xal.gl_sl_link_table
                   AND xal.ae_header_id = xah.ae_header_id
                   AND xal.application_id = xah.application_id
                   AND xah.event_id = xe.event_id
                   AND xah.application_id = xe.application_id
                   AND xe.entity_id = xte.entity_id
                   AND xe.application_id = xte.application_id
                   AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
                   AND xte.source_id_int_1 = ccf.cashflow_id(+)
                   AND ccf.cashflow_bank_account_id = cba.bank_account_id(+)
                   AND UPPER (xga.je_source) = 'CASH MANAGEMENT'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   ppa.name party_name,
                   NULL party_number,
                   ppa.project_rate_type transaction_type,
                   ppa.project_currency_code transaction_currency,
                   pei.project_number transaction_number,
                   TO_CHAR (ppa.start_date, 'DD-Mon-RRRR') transaction_date,
                   xga.gl_date,
                   gir.subledger_doc_sequence_value subledger_doc_no,
                   xga.doc_sequence_value,
                   NVL (pei.expenditure_comment, xal.description)
                      transction_description,
                   xal.currency_code entered_currency,
                   NVL (xal.entered_dr, 0) entered_dr,
                   NVL (xal.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_dr, 0),
                           NVL (xal.accounted_dr, 0))
                      accounted_dr, 
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_cr, 0),
                           NVL (xal.accounted_cr, 0))
                      accounted_cr, 
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   gl_import_references gir,
                   xla_ae_lines xal,
                   xla_ae_headers xah,
                   xla_events xe,
                   xla.xla_transaction_entities xte,
                   pa_expend_items_adjust2_v pei,
                   pa_projects_all ppa
             WHERE     xga.je_batch_id = gir.je_batch_id
                   AND xga.je_header_id = gir.je_header_id
                   AND xal.ledger_id = xah.ledger_id
                   AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND xga.je_header_id = gir.je_header_id
                   AND xga.je_line_num = gir.je_line_num
                   AND gir.gl_sl_link_id = xal.gl_sl_link_id
                   AND gir.gl_sl_link_table = xal.gl_sl_link_table
                   AND xal.ae_header_id = xah.ae_header_id
                   AND xal.application_id = xah.application_id
                   AND xah.event_id = xe.event_id
                   AND xah.application_id = xe.application_id
                   AND xe.entity_id = xte.entity_id
                   AND xe.application_id = xte.application_id
                   AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
                   AND xte.source_id_int_1 = pei.expenditure_item_id(+)
                   AND pei.project_id = ppa.project_id(+)
                   AND UPPER (xga.je_source) = 'PROJECT ACCOUNTING'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   (SELECT pv.vendor_name
      FROM apps.fa_invoice_details_v fid, apps.po_vendors pv
     WHERE fid.po_vendor_id = pv.vendor_id
       AND fid.asset_id = fth.asset_id)             party_name,
                  SELECT pv.segment1
      FROM apps.fa_invoice_details_v fid, apps.po_vendors pv
     WHERE fid.po_vendor_id = pv.vendor_id
       AND fid.asset_id = fth.asset_id)
                      party_number,
                   fth.transaction_type transaction_type,
                   xal.currency_code transaction_currency,
                   fth.asset_number transaction_number,
                   TO_CHAR (fth.date_effective, 'DD-Mon-RRRR')
                      transaction_date,
                   xga.gl_date,
                   NULL subledger_doc_no,
                   xga.doc_sequence_value,
                   fth.asset_number_desc transction_description,
                   xal.currency_code entered_currency,
                   NVL (xal.entered_dr, 0) entered_dr,
                   NVL (xal.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_dr, 0),
                           NVL (xal.accounted_dr, 0))
                      accounted_dr, 
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_cr, 0),
                           NVL (xal.accounted_cr, 0))
                      accounted_cr, 
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   gl_import_references gir,
                   xla_ae_lines xal,
                   xla_ae_headers xah,
                   xla_events xe,
                   xla_transaction_entities xte,
                   fa_transaction_history_trx_v fth
             WHERE     xga.je_batch_id = gir.je_batch_id
                   AND xga.je_header_id = gir.je_header_id
                   AND xal.ledger_id = xah.ledger_id
                   AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND xga.je_header_id = gir.je_header_id
                   AND xga.je_line_num = gir.je_line_num
                   AND gir.gl_sl_link_id = xal.gl_sl_link_id
                   AND gir.gl_sl_link_table = xal.gl_sl_link_table
                   AND xal.ae_header_id = xah.ae_header_id
                   AND xal.application_id = xah.application_id
                   AND xah.event_id = xe.event_id
                   AND xah.application_id = xe.application_id
                   AND xe.entity_id = xte.entity_id
                   AND xe.application_id = xte.application_id
                   AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
                   AND xte.source_id_int_1 = fth.transaction_header_id
                   AND UPPER (xga.je_source) = 'ASSETS'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   SELECT pv.vendor_name
      FROM apps.fa_invoice_details_v fid, apps.po_vendors pv
     WHERE fid.po_vendor_id = pv.vendor_id
       AND fid.asset_id =a.asset_id)                      party_name,
                   (SELECT pv.segment1
      FROM apps.fa_invoice_details_v fid, apps.po_vendors pv
     WHERE fid.po_vendor_id = pv.vendor_id
       AND fid.asset_id = fa.asset_id)                      party_number,
                   fa.asset_type transaction_type,
                   xal.currency_code transaction_currency,
                   fa.asset_number transaction_number,
                   TO_CHAR (xe.transaction_date, 'DD-Mon-RRRR')                transaction_date,
                   xga.gl_date,
                   NULL subledger_doc_no,
                   xga.doc_sequence_value,
                   fa.description transction_description,
                   xal.currency_code entered_currency,
                   NVL (xal.entered_dr, 0) entered_dr,
                   NVL (xal.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_dr, 0),
                           NVL (xal.accounted_dr, 0))
                      accounted_dr, 
                   DECODE (xga.ledger_id,
                           2425, NVL (xga.accounted_cr, 0),
                           NVL (xal.accounted_cr, 0))
                      accounted_cr, 
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   gl_import_references gir,
                   xla_ae_lines xal,
                   xla_ae_headers xah,
                   xla_events xe,
                   xla_transaction_entities xte,
                   fa_additions fa
             WHERE     xga.je_batch_id = gir.je_batch_id
                   AND xga.je_header_id = gir.je_header_id
                   AND xal.ledger_id = xah.ledger_id
                   AND (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND xga.je_header_id = gir.je_header_id
                   AND xga.je_line_num = gir.je_line_num
                   AND gir.gl_sl_link_id = xal.gl_sl_link_id
                   AND gir.gl_sl_link_table = xal.gl_sl_link_table
                   AND xal.ae_header_id = xah.ae_header_id
                   AND xal.application_id = xah.application_id
                   AND xah.event_id = xe.event_id
                   AND xah.application_id = xe.application_id
                   AND xe.entity_id = xte.entity_id
                   AND xe.application_id = xte.application_id
                   AND (xal.accounted_dr != 0 OR xal.accounted_cr != 0)
                   AND xte.source_id_int_1 = fa.asset_id
                   AND UPPER (xga.je_source) = 'ASSETS'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   ppf.full_name party_name,
                   ppf.employee_number party_number,
                   'Payroll' transaction_type,
                   pgi.currency_code transaction_currency,
                   TO_CHAR (paf.payroll_id) transaction_number,
                   xga.reference_4 transaction_date,
                   xga.gl_date,
                   paf.payroll_id subledger_doc_no,
                   xga.doc_sequence_value,
( SELECT DISTINCT pc.element_name
      FROM apps.pay_costs_v pc
     WHERE pc.cost_allocation_keyflex_id =  xga.reference_2)
                      transction_description,
                   pgi.currency_code entered_currency,
                   NVL (pgi.entered_dr, 0) entered_dr,
                   NVL (pgi.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   NVL (pgi.entered_dr, 0) accounted_dr,
                   NVL (pgi.entered_cr, 0) accounted_cr,
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   pay_assignment_actions paa,
                   pay_gl_interface pgi,
                   per_assignments_f paf,
                   per_people_f ppf,
                   apps.per_periods_of_service ppos
             WHERE     xga.accounted_dr != 0
                   AND pgi.entered_dr != 0
                   AND xga.reference_1 = paa.payroll_action_id
                   AND paa.assignment_action_id = pgi.assignment_action_id
                   AND xga.reference_2 = pgi.cost_allocation_keyflex_id
                   AND xga.reference_5 = pgi.run_payroll_action_id
                   AND paa.assignment_id = paf.assignment_id
                   AND ppf.person_id = paf.person_id
                   AND ppf.person_id = ppos.person_id
                   AND (   (    ppos.actual_termination_date IS NOT NULL
                            AND ppos.actual_termination_date BETWEEN ppf.effective_start_date
                                                                 AND ppf.effective_end_date
                            AND ppos.actual_termination_date BETWEEN paf.effective_start_date
                                                                 AND paf.effective_end_date)
                        OR (    ppos.actual_termination_date IS NULL
                            AND SYSDATE BETWEEN ppf.effective_start_date
                                            AND ppf.effective_end_date
                            AND SYSDATE BETWEEN paf.effective_start_date
                                            AND paf.effective_end_date))
                   AND UPPER (xga.je_source) = 'PAYROLL'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   NULL party_name,
                   NULL party_number,
                   'Payroll' transaction_type,
                   NULL transaction_currency,
                   NULL transaction_number,
                   xga.reference_4 transaction_date,
                   xga.gl_date,
                   NULL subledger_doc_no,
                   xga.doc_sequence_value,
 ( SELECT DISTINCT pc.element_name
      FROM apps.pay_costs_v pc
     WHERE pc.cost_allocation_keyflex_id = xga.reference_2)
                      transction_description,
                   NULL entered_currency,
                   entered_dr,
                   entered_cr,
                   xga.ledger_currency accounted_currency,
                   accounted_dr,
                   accounted_cr,
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga
             WHERE     (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND NOT EXISTS
                          (SELECT 1
                             FROM pay_gl_interface pgi
                            WHERE pgi.cost_allocation_keyflex_id =
                                     xga.reference_2)
                   AND UPPER (xga.je_source) = 'PAYROLL'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   ppf.full_name party_name,
                   ppf.employee_number party_number,
                   'Payroll' transaction_type,
                   pgi.currency_code transaction_currency,
                   TO_CHAR (paf.payroll_id) transaction_number,
                   xga.reference_4 transaction_date,
                   xga.gl_date,
                   paf.payroll_id subledger_doc_no,
                   xga.doc_sequence_value,
 ( SELECT DISTINCT pc.element_name
      FROM apps.pay_costs_v pc
     WHERE pc.cost_allocation_keyflex_id =   xga.reference_2)  transction_description,
                   pgi.currency_code entered_currency,
                   NVL (pgi.entered_dr, 0) entered_dr,
                   NVL (pgi.entered_cr, 0) entered_cr,
                   xga.ledger_currency accounted_currency,
                   NVL (pgi.entered_dr, 0) accounted_dr,
                   NVL (pgi.entered_cr, 0) accounted_cr,
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga,
                   pay_assignment_actions paa,
                   pay_gl_interface pgi,
                   per_assignments_f paf,
                   per_people_f ppf,
                   apps.per_periods_of_service ppos
             WHERE     xga.accounted_cr != 0
                   AND pgi.entered_cr != 0
                   AND xga.reference_1 = paa.payroll_action_id
                   AND paa.assignment_action_id = pgi.assignment_action_id
                   AND xga.reference_2 = pgi.cost_allocation_keyflex_id
                   AND xga.reference_5 = pgi.run_payroll_action_id
                   AND paa.assignment_id = paf.assignment_id
                   AND ppf.person_id = paf.person_id
                   AND ppf.person_id = ppos.person_id
                   AND (   (    ppos.actual_termination_date IS NOT NULL
                            AND ppos.actual_termination_date BETWEEN ppf.effective_start_date
                                                                 AND ppf.effective_end_date
                            AND ppos.actual_termination_date BETWEEN paf.effective_start_date
                                                                 AND paf.effective_end_date)
                        OR (    ppos.actual_termination_date IS NULL
                            AND SYSDATE BETWEEN ppf.effective_start_date
                                            AND ppf.effective_end_date
                            AND SYSDATE BETWEEN paf.effective_start_date
                                            AND paf.effective_end_date))
                   AND UPPER (xga.je_source) = 'PAYROLL'
            UNION ALL
            SELECT xga.period_name,
                   xga.ledger_name,
                   xga.ledger_id,
                   xga.account,
                   xga.project,
                   xga.product_group,
                   xga.department,
                   xga.employee,
                   xga.emp_name,
                   xga.product,
                   xga.entity,
                   xga.inter_entity,
                   xga.spare1,
                   xga.spare2,
                   xga.combinations,
                   xga.je_source,
                   xga.je_category,
                   NULL party_name,
                   NULL party_number,
                   NULL transaction_type,
                   NULL transaction_currency,
                   NULL transaction_number,
                   NULL transaction_date,
                   xga.gl_date,
                   NULL subledger_doc_no,
                   xga.doc_sequence_value,
                   xga.description transction_description,
                   xga.entered_currency entered_currency,
                   xga.entered_dr entered_dr,
                   xga.entered_cr entered_cr,
                   xga.ledger_currency accounted_currency,
                   accounted_dr,
                   accounted_cr,
                   xga.code_combination_id,
                   xga.gl_cr_date
              FROM XXJG_GL_ACC_V xga
             WHERE     (xga.accounted_dr != 0 OR xga.accounted_cr != 0)
                   AND UPPER (xga.je_source) NOT IN ('PAYABLES',
                                                     'RECEIVABLES',
                                                     'COST MANAGEMENT',
                                                     'CASH MANAGEMENT',
                                                     'PROJECT ACCOUNTING',
                                                     'ASSETS',
                                                     'PAYROLL',
                                                     '1')
            UNION ALL
            SELECT DISTINCT
                   gb.period_name,
                   gl.name ledger_name,
                   gb.ledger_id,
                   gcc.segment2 account,
                   gcc.segment6 project,
                   gcc.segment3 product_group,
                   gcc.segment4 department,
                   gcc.segment7 employee,
                   (SELECT ffl.DESCRIPTION
                      FROM FND_ID_FLEX_SEGMENTS_VL fil,
                           FND_FLEX_VALUES_VL ffl
                     WHERE     1 = 1
                           AND gcc.segment7 = ffl.flex_value
                           AND ffl.FLEX_VALUE_SET_ID = fil.FLEX_VALUE_SET_ID
                           AND fil.segment_name = 'Employee'
                           AND fil.ID_FLEX_CODE = 'GL#')
                      emp_name,
                   gcc.segment5 product,
                   gcc.segment1 entity,
                   gcc.segment8 inter_entity,
                   gcc.segment9 spare1,
                   gcc.segment10 spare2,
                      gcc.segment1
                   || '.'
                   || gcc.segment2
                   || '.'
                   || gcc.segment3
                   || '.'
                   || gcc.segment4
                   || '.'
                   || gcc.segment5
                   || '.'
                   || gcc.segment6
                   || '.'
                   || gcc.segment7
                   || '.'
                   || gcc.segment8
                   || '.'
                   || gcc.segment9
                   || '.'
                   || gcc.segment10
                      combinations,
                   NULL je_source,
                   NULL je_category,
                   NULL party_name,
                   NULL party_number,
                   NULL transaction_type,
                   NULL transaction_currency,
                   NULL transaction_number,
                   NULL transaction_date,
                   gps.start_date gl_date,
                   NULL subledger_doc_no,
                   NULL doc_sequence_value,
                   NULL transction_description,
                   NULL entered_currency,
                   NULL entered_dr,
                   NULL entered_cr,
                   gl.currency_code accounted_currency,
                   NULL accounted_dr,
                   NULL accounted_cr,
                   gb.code_combination_id,
                   gb.last_update_date gl_cr_date
              FROM gl_balances gb,
                   gl_code_combinations gcc,
                   gl_ledgers gl,
                   gl_period_statuses gps
             WHERE     gb.code_combination_id = gcc.code_combination_id
                   AND gb.ledger_id = gl.ledger_id
                   AND gps.application_id = 101
                   AND gb.period_name = gps.period_name
                   AND gl.ledger_id = gps.ledger_id
                   AND gb.code_combination_id NOT IN (SELECT DISTINCT
                                                             gjl.code_combination_id
                                                        FROM gl_ledgers gl,
                                                             gl_je_headers gjh,
                                                             gl_je_lines gjl,
                                                             gl_code_combinations gcc
                                                       WHERE     gl.ledger_id =   gjh.ledger_id
                                                             AND gjh.je_header_id =  gjl.je_header_id
                                                             AND gjh.actual_flag =    'A'
                                                             AND gjh.status =  'P'
                                                             AND gjl.code_combination_id =   gcc.code_combination_id
                                                             AND (   gjl.accounted_dr != 0
                                                                  OR gjl.accounted_cr != 0)
                                                             AND gjl.period_name =  gb.period_name
                                                             AND gjl.ledger_id = gb.ledger_id)
            -------------------------added by JG----------------
            /*UNION ALL
            SELECT gjl.period_name,
                   gl.name ledger_name,
                   gjl.ledger_id,
                   gcc.segment2 account,
                   gcc.segment6 project,
                   gcc.segment3 product_group,
                   gcc.segment4 department,
                   gcc.segment7 employee,
                   (SELECT ffl.DESCRIPTION
                      FROM FND_ID_FLEX_SEGMENTS_VL fil,
                           FND_FLEX_VALUES_VL ffl
                     WHERE     1 = 1
                           AND gcc.segment7 = ffl.flex_value
                           AND ffl.FLEX_VALUE_SET_ID = fil.FLEX_VALUE_SET_ID
                           AND fil.segment_name = 'Employee'
                           AND fil.ID_FLEX_CODE = 'GL#')
                      emp_name,
                   gcc.segment5 product,
                   gcc.segment1 entity,
                   gcc.segment8 inter_entity,
                   gcc.segment9 spare1,
                   gcc.segment10 spare2,
                      gcc.segment1
                   || '.'
                   || gcc.segment2
                   || '.'
                   || gcc.segment3
                   || '.'
                   || gcc.segment4
                   || '.'
                   || gcc.segment5
                   || '.'
                   || gcc.segment6
                   || '.'
                   || gcc.segment7
                   || '.'
                   || gcc.segment8
                   || '.'
                   || gcc.segment9
                   || '.'
                   || gcc.segment10
                      combinations,
                   gjh.Je_source,
                   gjh.je_category,
                   NULL party_name,
                   NULL party_number,
                   'Reversed' transaction_type,
                   NULL transaction_currency,
                   NULL transaction_number,
                   NULL transaction_date,
                   gjl.EFFECTIVE_DATE gl_date,
                   NULL subledger_doc_no,
                   NULL doc_sequence_value,
                   NULL transction_description,
                   gjh.CURRENCY_CODE entered_currency,
                   NVL (gjl.entered_dr, 0) entered_dr,
                   NVL (gjl.entered_cr, 0) entered_cr,
                   gjh.CURRENCY_CODE accounted_currency,
                   NVL (gjl.accounted_dr, 0) accounted_dr,
                   NVL (gjl.accounted_cr, 0) accounted_cr,
                   gjl.code_combination_id,
                   gjl.last_update_date gl_cr_date
              FROM gl_je_headers gjh,
                   gl_je_lines gjl,
                   gl_ledgers gl,
                   gl_code_combinations gcc
             WHERE     gjh.je_header_id = gjl.je_header_id
                   AND gjh.actual_flag = 'A'
                   AND gjh.status = 'P'
                   AND gjh.period_name = gjl.period_name
                   AND gjh.ledger_id = gjl.ledger_id
                   AND gl.ledger_id = gjh.ledger_id
                   AND gjl.code_combination_id = gcc.code_combination_id
                   AND gjh.reversed_je_header_id IS NOT NULL --Reversed data only
                                                      */      -------------added by JG----------------
           ) tab1,
           (  SELECT ledger_id,
                     period_name,
                     code_combination_id,
                     CURRENCY_CODE,
                     (  SUM (NVL (gb.begin_balance_dr, 0))
                      - SUM (NVL (gb.begin_balance_cr, 0)))
                        open_balance,
                     SUM (NVL (gb.period_net_dr_beq, 0)) sum_debit,
                     SUM (NVL (gb.period_net_cr_beq, 0)) sum_credit,
                     (  (  SUM (NVL (gb.begin_balance_dr, 0))
                         - SUM (NVL (gb.begin_balance_cr, 0)))
                      + (  SUM (NVL (gb.period_net_dr, 0))
                         - SUM (NVL (gb.period_net_cr, 0))))
                        closing_balance
                FROM apps.gl_balances gb
               WHERE 1 = 1
            GROUP BY ledger_id,
                     period_name,
                     code_combination_id,
                     CURRENCY_CODE) xx
     WHERE     tab1.ledger_id = xx.ledger_id
           AND tab1.accounted_currency = xx.currency_code
           AND tab1.period_name = xx.period_name
           AND tab1.code_combination_id = xx.code_combination_id

---End Query

--Below view 
-----------------------------------------------------------------------------------------
Create OR Replace View XXJG_GL_ACC_V
AS
SELECT   gjh.period_name,
            gl.name ledger_name,
            gl.ledger_id,
            gjh.je_source,
            gjh.je_category,
            gjh.default_effective_date gl_date,
            gjh.doc_sequence_value,
            gjl.code_combination_id,
            gjh.je_batch_id,
            gjl.je_line_num,
            gjh.posted_date gl_cr_date,
            gjh.je_header_id,
            gcc.segment1 entity,                                    --account,
            gcc.segment2 account,                                   --project,
            gcc.segment3 product_group,                        --resource_org,
            gcc.segment4 department,                          --work_location,
            gcc.segment5 product,                           --client_vertical,
            gcc.segment6 project,                                   --product,
            gcc.segment7 employee,                                   --entity,
            gcc.segment8 inter_entity,                         --inter_entity,
            gcc.segment9 spare1,                                     --spare1,
            gcc.segment10 spare2,                                    --spare2,
               gcc.segment1
            || '.'
            || gcc.segment2
            || '.'
            || gcc.segment3
            || '.'
            || gcc.segment4
            || '.'
            || gcc.segment5
            || '.'
            || gcc.segment6
            || '.'
            || gcc.segment7
            || '.'
            || gcc.segment8
            || '.'
            || gcc.segment9
            || '.'
            || gcc.segment10
               combinations,
            gjl.reference_2,
            gjl.reference_5,
            gjl.reference_1,
            gjl.reference_4,
            NVL (gjl.entered_dr, 0) entered_dr,
            NVL (gjl.entered_cr, 0) entered_cr,
            NVL (gjl.accounted_dr, 0) accounted_dr,
            NVL (gjl.accounted_cr, 0) accounted_cr,
            gl.currency_code ledger_currency,
            gjh.currency_code entered_currency,
            gjl.description
     FROM   gl_je_headers gjh,
            gl_je_lines gjl,
            gl_ledgers gl,
            gl_code_combinations gcc
    WHERE       gjh.je_header_id = gjl.je_header_id
            AND gjh.actual_flag = 'A'
            AND gjh.status = 'P'
            AND gjh.period_name = gjl.period_name
            AND gjh.ledger_id = gjl.ledger_id
            AND gl.ledger_id = gjh.ledger_id
            AND gjl.code_combination_id = gcc.code_combination_id