Forum Stats

  • 3,751,259 Users
  • 2,250,338 Discussions
  • 7,867,365 Comments

Discussions

Linking xla.xla_transaction_entities to Subledgers

Miranga Ariyaratne
Miranga Ariyaratne Member Posts: 68
edited Jul 23, 2013 4:49AM in SQL & PL/SQL

Hi all,

Im trying to link xla.xla_transaction_entities to the source transactions through an sql query.

I've been able to link ENTITY CODES AP_INVOICES, AP_PAYMENTS, TRANSACTIONS, and RECEIPTS to the corresponding source tables. My knowledge on OM, INV and OPM Financials is not that great. How do i link INVENTORY, PURCHASING, REVALUATIONS, ORDER_MANAGEMENT and CE_CASHFLOWS to their source tables?

MIranga

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,312 Red Diamond

    This sounds like a product specific question rathere than an SQL and PL/SQL question.

    Please ask your question in the correct forum for your product.  I'm not familiar with it myself... is it or perhaps the within that?

  • Faraz Anwar
    Faraz Anwar Member Posts: 43

    For Inventory following sql will help. Though its not thru transaction entities rather xla_distribution_link is used for linking tables.

    SELECT

        /*+ PARALLEL (xdl, 5) PARALLEL (gir, 5) */

        gjh.je_source source,

        gjh.je_category category,

        gjb.name journal,

        gjl.je_line_num line_num,

        gjh.actual_flag actual_flag,

        gjh.period_name period,

        gjl.effective_date gl_date,

        gcc.segment1 fund,

        gcc.segment2 FUNCTION,

        gcc.segment3 object,

        gcc.segment4 sub_object,

        gcc.segment5 org,

        gcc.segment6 yr,

        gcc.segment7 program,

        gjl.entered_dr debit,

        gjl.entered_cr credit,

        mtt.transaction_type_name type ,

        DECODE(mmt.transaction_source_type_id, 1,pha.segment1, 2,'Req-'

        ||oha.orig_sys_document_ref

        ||'  '

        ||mso.segment2

        ||'-'

        ||mso.segment1, 3,NULL, 4,mtrh.request_number , 5,wip.wip_entity_name, 6,

        NULL, 7,porh.segment1, 8,'Req-'

        ||oha.orig_sys_document_ref

        ||'  '

        ||mso.segment2

        ||'-'

        ||mso.segment1, 9,NULL, 10,NULL, 11,NULL, 12,mso.segment1

        ||'-'

        ||mso.segment2, mmt.transaction_source_name) purchase_document_num,

        to_number(NULL) purchase_line_num,

        to_number(NULL) shipment_num,

        to_number(NULL) purchase_distribution_num,

        NULL vendor,

        NULL invoice_num,

        to_number(NULL) invoice_distribution_num,

        to_number(NULL) check_num,

        NULL award,

        NULL project,

        NULL task,

        NULL expenditure_type,

        NULL expenditure_org,

        mmt.transaction_date trx_date,

        mmt.subinventory_code inv_whse,

        msi.segment1 inv_item,

        mmt.transaction_quantity trx_qty,

        SIGN(NVL(gjl.accounted_dr,0)                                  - NVL(gjl.accounted_cr,0)) * DECODE(

        gcc.account_type,'A',1,'E',1,                                 -1)* DECODE (gjh.actual_flag, 'E',ROUND(

        mmt.encumbrance_amount,2) , ABS(ROUND((NVL(mmt.actual_cost,0) * NVL(

        mmt.primary_quantity,0) ),2))) trx_Cost,

        gjh.je_batch_id je_batch_id,

        gjl.je_header_id je_header_id,

        mmt.transaction_id distribution_id,

        gjl.code_combination_id ccid

        /*

        mmt.transaction_id,

        xdl.ae_header_id,

        xal.gl_sl_link_table,

        xal.gl_sl_link_id,

        gjl.accounted_dr,

        gjl.accounted_cr

        */

      FROM

        xla_distribution_links xdl,

        mtl_transaction_accounts xta,

        mtl_material_transactions mmt,

        xla_ae_lines xal,

        gl_import_references gir,

        gl_je_lines gjl,

        gl_je_headers gjh,

        gl_je_batches gjb,

        gl_code_combinations gcc,

        mtl_transaction_types mtt,

        po_headers_all pha,

        oe_order_headers_all oha,

        mtl_sales_orders mso,

        wip_entities wip,

        po_requisition_headers_all porh,

        mtl_txn_request_headers mtrh,

        mtl_system_items_b msi

      where   1= 1

      AND mmt.transaction_id                 = xta.transaction_id

      AND xdl.source_distribution_type     = 'MTL_TRANSACTION_ACCOUNTS'

      AND xdl.source_distribution_id_num_1 = xta.inv_sub_ledger_id

      AND xal.ae_header_id                 = xdl.ae_header_id

      AND xal.ae_line_num                  = xdl.ae_line_num

      AND gir.gl_sl_link_id                = xal.gl_sl_link_id

      AND gir.gl_sl_link_table             = 'MTA'

      AND gir.je_header_id                 = gjl.je_header_id

      AND gir.je_line_num                  = gjl.je_line_num

      AND gjl.code_combination_id          = gcc.code_combination_id

      AND gir.je_header_id                 = gjh.je_header_id

      AND gjh.period_name LIKE '%'

      AND gjh.je_category           = 'MTL'

      AND gjh.je_source             = 'Cost Management'

      AND gjh.je_batch_id           = gjb.je_batch_id

      AND gjh.status                = 'P'

      AND mmt.transaction_type_id   = mtt.transaction_type_id

      AND mmt.transaction_source_id = pha.po_header_id

      AND mmt.transaction_source_id = mso.sales_order_id

      AND mmt.transaction_source_id = wip.wip_entity_id

      AND mmt.transaction_source_id = porh.requisition_header_id

      AND mmt.transaction_source_id = mtrh.header_id(+)

      AND mmt.inventory_item_id     = msi.inventory_item_id(+)

      AND mmt.organization_id       = msi.organization_id(+)

      AND mso.segment1              = oha.order_number(+);

    For PO receiving following sql will help.

    SELECT

          gjh.je_source source,

          gjh.je_category category,

          gjb.name journal,

          gjl.je_line_num line_num,

          gjh.actual_flag actual_flag,

          gjh.period_name period,

          gjl.effective_date gl_date,

          glcc.segment1 fund,

          glcc.segment2 FUNCTION,

          glcc.segment3 object,

          glcc.segment4 sub_object,

          glcc.segment5 org,

          glcc.segment6 yr,

          glcc.segment7 program,

          gjl.entered_dr debit,

          gjl.entered_cr credit,

          rt.transaction_type type,

          pha.segment1 purchase_document_num,

          to_number(pl.line_num) purchase_line_num,

          to_number(pll.shipment_num) shipment_num,

          to_number(pda.distribution_num) purchase_distribution_num,

          pv.vendor_name vendor,

          NULL invoice_num,

          to_number(NULL) invoice_distribution_num,

          to_number(NULL) check_num,

          NULL award,

          NULL project,

          NULL task,

          NULL expenditure_type,

          NULL expenditure_org,

          rt.transaction_date trx_date,

          NULL inv_whse,

          NULL inv_item,

          to_number(NULL) trx_qty,

          (NVL(rrsl.accounted_dr,0)     - NVL(rrsl.accounted_cr,0)) * DECODE(

          glcc.account_type,'A',1,'E',1,-1) trx_cost,

          gjh.je_batch_id je_batch_id,

          gjl.je_header_id je_header_id,

          to_number(NULL) distribution_id,

          gjl.code_combination_id ccid

        FROM

          --xla_gl_ledgers_v xgl,

          fnd_application_vl app,

          gl_je_batches gjb,

          gl_je_headers gjh,

          gl_je_lines gjl,

          gl_code_combinations glcc,

          gl_import_references gir,

          xla_ae_lines xal,

          xla_ae_headers xah,

          xla_distribution_links xdl,

          rcv_receiving_sub_ledger rrsl,

          rcv_transactions rt,

          po_headers_all pha,

          po_line_locations_all pll,

          po_distributions_all pda,

          po_lines_all pl,

          po_vendors pv

        WHERE

          1                                             =1

        AND gjh.period_name                             = 'NOV-12'

        AND gjh.je_source                              IN ('Purchasing', 'Cost Management')

        AND gjh.je_category                             = 'Receiving'

        AND gjh.je_header_id                            = gjl.je_header_id

        AND gjb.je_batch_id                             = gjh.je_batch_id

        AND gir.je_header_id                            = gjl.je_header_id

        AND gir.je_line_num                             = gjl.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.application_id                          = app.application_id

        AND xal.ae_header_id                            = xah.ae_header_id

        AND app.application_id                          = xah.application_id

        AND glcc.code_combination_id                    = gjl.code_combination_id

        AND xdl.application_id                          = app.application_id

        AND NVL(xdl.SOURCE_DISTRIBUTION_ID_NUM_2,(-99)) = -99

        AND xdl.source_distribution_type                =

          'RCV_RECEIVING_SUB_LEDGER'

        AND xdl.ae_header_id                 = xal.ae_header_id

        AND xdl.ae_line_num                  = xal.ae_line_num

        AND xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id

        AND rrsl.rcv_transaction_id          = rt.transaction_id

        AND pl.po_line_id                 = rt.po_line_id

        AND pha.po_header_id              = rt.po_header_id

        AND pll.line_location_id          = rt.po_line_location_id

        AND pda.po_distribution_id        = rt.po_distribution_id

        AND pv.vendor_id                  = pha.vendor_id

      );

    Following notes will help:

    R12: FAQ on Transfer to GL in R12 [ID 876190.1]

    SLA Data Flow and Table Links [ID 802966.1]

    R12 Mapping Between Subledger Tables, SLA and GL Tables [ID 871622.1]

  • ngherbert
    ngherbert Member Posts: 2

    Hi Miranga,

    Hereby I try to help with this kind of query which is transaction entities link to material transactions :

    SELECT

                    /*+ INDEX_DESC(XLK) */

                    XAH.PERIOD_NAME

                    , MMT.INVENTORY_ITEM_ID

                    , MTT.ATTRIBUTE1 TRANSACTION_TYPE

                    , MTT.TRANSACTION_TYPE_NAME

                    , MMT.SUBINVENTORY_CODE

                    , FND_FLEX_EXT.GET_SEGS ('SQLGL',

                                              'GL#',

                                              50368,

                                              XAL.CODE_COMBINATION_ID) ACCOUNT_NO

                    , MMT.TRANSACTION_QUANTITY

                    , XAL.ACCOUNTED_DR

                    , XAL.ACCOUNTED_CR

                FROM

                    XLA.XLA_AE_HEADERS XAH

                    , XLA.XLA_AE_LINES XAL

                    , XLA.XLA_EVENTS XE

                    , APPS.XLA_LOOKUPS XLK

                    , XLA_EVENT_TYPES_TL XETL

                    , XLA_EVENT_CLASSES_TL XECL

                    , XLA_TRANSACTION_ENTITIES_UPG XTEU

                    , MTL_MATERIAL_TRANSACTIONS MMT

                    , MTL_SYSTEM_ITEMS_B MSIB

                    , MTL_TRANSACTION_TYPES MTT

                    , MTL_PARAMETERS MP

                WHERE XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

                    AND XAH.APPLICATION_ID = XAL.APPLICATION_ID

                    AND XE.EVENT_ID = XAH.EVENT_ID

                    AND XE.ENTITY_ID = XAH.ENTITY_ID  

                    AND XAL.ACCOUNTING_CLASS_CODE = XLK.LOOKUP_CODE

                    AND 'XLA_ACCOUNTING_CLASS' = XLK.LOOKUP_TYPE

                    AND XETL.APPLICATION_ID = XE.APPLICATION_ID

                    AND XETL.EVENT_TYPE_CODE = XE.EVENT_TYPE_CODE

                    AND XETL.LANGUAGE = USERENV ('LANG')

                    AND XECL.APPLICATION_ID = XETL.APPLICATION_ID

                    AND XECL.ENTITY_CODE = XETL.ENTITY_CODE

                    AND XECL.EVENT_CLASS_CODE = XETL.EVENT_CLASS_CODE

                    AND XECL.LANGUAGE = USERENV ('LANG')

                    AND XAH.ENTITY_ID = XTEU.ENTITY_ID

                    AND XTEU.TRANSACTION_NUMBER = TO_CHAR(MMT.TRANSACTION_ID)

                    AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID

                    AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID

                    AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID

                    AND MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID

                    AND MP.ORGANIZATION_CODE = 'STU'

                    AND XAH.PERIOD_NAME = P_PERIOD

                    AND XLK.MEANING = 'Inventory Valuation'

                    AND FND_FLEX_EXT.GET_SEGS ('SQLGL',

                                              'GL#',

                                              50368,

                                              XAL.CODE_COMBINATION_ID) = NVL(P_ACCOUNT,FND_FLEX_EXT.GET_SEGS ('SQLGL',

                                              'GL#',

                                              50368,

                                              XAL.CODE_COMBINATION_ID))

    and link to manufacturing :

    SELECT XE1.EVENT_TYPE_CODE,

                   SUB1.TRANSACTION_ID,

                   SUB1.ITEM_GROUP,

                   SUB1.ITEM_CODE,

                   SUB1.IO,

                   TO_CHAR(XAL1.ACCOUNTING_DATE, 'MON-RR') PERIOD,

                   GCC1.SEGMENT1 || '.' || GCC1.SEGMENT2 || '.' || GCC1.SEGMENT3 || '.' || GCC1.SEGMENT4 || '.' || GCC1.SEGMENT5 || '.' || GCC1.SEGMENT6 CODE,

                   --GCC1.SEGMENT3 NATURAL_ACCOUNT,

                   GCC1.SEGMENT4 NATURAL_ACCOUNT,

                   XAL1.ACCOUNTED_DR,

                   XAL1.ACCOUNTED_CR,

                   NVL(XAL1.ACCOUNTED_DR, 0) - NVL(XAL1.ACCOUNTED_CR, 0) ACCOUNTED_DR_CR,

                   SUB1.BATCH_NO,

                   SUB1.DO_NUMBER,

                   SUB1.FORMULA_NO

              FROM XLA.XLA_EVENTS XE1,

                   XLA.XLA_AE_HEADERS XAH1,

                   XLA.XLA_AE_LINES XAL1,

                   GL.GL_CODE_COMBINATIONS GCC1,

                   APPS.XLA_GL_LEDGERS_V XGL1,

                   APPS.XLA_LOOKUPS XLK1,

                   (SELECT /*+ USE_HASH(HAOU1,MSIB1,MMT,GXEH1) */ GXEH1.EVENT_ID,

                           GXEH1.TRANSACTION_ID,

                           MSIB1.SEGMENT1 ITEM_GROUP,

                           MSIB1.SEGMENT1 ITEM_CODE,

                           HAOU1.NAME IO,

                           MMT.SHIPMENT_NUMBER DO_NUMBER,

                           GBH.BATCH_NO,

                           FM.FORMULA_NO

                      FROM GMF.GMF_XLA_EXTRACT_HEADERS GXEH1,

                           INV.MTL_MATERIAL_TRANSACTIONS MMT,

                           INV.MTL_SYSTEM_ITEMS_B MSIB1,

                           HR.HR_ALL_ORGANIZATION_UNITS HAOU1,

                           GME.GME_BATCH_HEADER GBH,

                           APPS.FM_FORM_MST FM

                     WHERE GXEH1.TRANSACTION_ID = MMT.TRANSACTION_ID

                       AND MMT.INVENTORY_ITEM_ID = MSIB1.INVENTORY_ITEM_ID

                       AND MMT.ORGANIZATION_ID = MSIB1.ORGANIZATION_ID

                       AND MMT.ORGANIZATION_ID = HAOU1.ORGANIZATION_ID

                       AND MSIB1.ORGANIZATION_ID = HAOU1.ORGANIZATION_ID

                       AND MMT.TRANSACTION_SOURCE_ID = GBH.BATCH_ID(+)

                       AND GBH.FORMULA_ID = FM.FORMULA_ID(+)) SUB1

             WHERE XE1.EVENT_ID = XAH1.EVENT_ID

               AND XE1.ENTITY_ID = XAH1.ENTITY_ID

               AND XAH1.AE_HEADER_ID = XAL1.AE_HEADER_ID

               AND XAH1.APPLICATION_ID = XAL1.APPLICATION_ID

               AND XAH1.LEDGER_ID = XGL1.LEDGER_ID

               AND XAL1.ACCOUNTING_CLASS_CODE = XLK1.LOOKUP_CODE

               AND 'XLA_ACCOUNTING_CLASS' = XLK1.LOOKUP_TYPE

               AND XAL1.CODE_COMBINATION_ID = GCC1.CODE_COMBINATION_ID

               AND 'Y' = GCC1.ENABLED_FLAG

               AND XE1.EVENT_TYPE_CODE NOT IN ('ACTCOSTADJ', 'CLOS', 'GLCOSTALOC', 'INVOICE_ERV_ADJ', 'INVOICE_IPV_ADJ', 'LC_ADJUST_DELIVER', 'LC_ADJUST_RECEIVE', 'LC_ADJUST_VALUATION', 'RECEIVE', 'STEP')

               AND XE1.EVENT_ID = SUB1.EVENT_ID

               AND :P_PERIOD = TO_CHAR(XAL1.ACCOUNTING_DATE, 'MON-RR')

               AND SUB1.ITEM_GROUP = NVL(:P_IG, SUB1.ITEM_GROUP)

               AND GCC1.SEGMENT3 = NVL(:P_NA, GCC1.SEGMENT3)

               AND XAH1.EVENT_ID = SUB1.EVENT_ID

    This just some kind of examples. Maybe you can modify it by yourself.

This discussion has been closed.