4 Replies Latest reply on Jul 2, 2013 5:39 PM by 1021998

    Reference for 'Source' field in Material Transactions -> Locations Tab

    707018
      Hi All,

      In the Material Transactions screen --> Locations Tab, there is a field as 'Source' which is mapped to the field 'LOC_TRX_SOURCE_NAME'.
      This field when i fetch from the table MTL_MATERIAL_TRANSACTIONS (TRANSACTION_SOURCE_NAME) is blank, but on the form it displays some value.
      Can anyone please help me find out what is the source for this field. Is there any specific table to be referred to find out the value of this field.

      Regards,
      Shruti
        • 1. Re: Reference for 'Source' field in Material Transactions -> Locations Tab
          707018
          Hi,

          Can anyone please reply to me for the above query.

          Regards,
          Shruti
          • 2. Re: Reference for 'Source' field in Material Transactions -> Locations Tab
            user11986822
            Here you go.

            Select mso.segment1||'.'||mso.segment2||'.'||mso.segment3 source
            from apps.mtl_material_transactions mmt
            , apps.mtl_sales_orders mso
            where mso.sales_order_id = mmt.TRANSACTION_SOURCE_ID;

            Hope it helps
            • 3. Re: Reference for 'Source' field in Material Transactions -> Locations Tab
              user8962719
              Here you go.

              SELECT
              a.*,
              b.description LOC_TRX_SOURCE_NAME
              FROM
              MTL_MATERIAL_TRANSACTIONS A,
              MTL_GENERIC_DISPOSITIONS b
              WHERE
              b.disposition_id=A.transaction_source_id
              AND b.organization_id =A.organization_id ;

              Hope it helps

              Edited by: user8962719 on 17-may-2012 8:36
              • 4. Re: Reference for 'Source' field in Material Transactions -> Locations Tab
                1021998

                After being unable to find this anywhere on the internet I was able to put this together.

                 

                SELECT mtst.transaction_source_type_name,

                       DECODE (

                           mtst.transaction_source_type_name,

                           'Account', (SELECT DECODE (

                                                  gcc.segment1,

                                                  NULL, ' ',

                                                     gcc.segment1

                                                  || '.'

                                                  || gcc.segment2

                                                  || '.'

                                                  || gcc.segment3

                                                  || '.'

                                                  || gcc.segment4

                                                  || '.'

                                                  || gcc.segment11

                                                  || '.'

                                                  || gcc.segment8

                                                  || '.'

                                                  || gcc.segment9

                                                  || '.'

                                                  || gcc.segment10)

                                         FROM gl.gl_code_combinations gcc

                                        WHERE gcc.code_combination_id =

                                                  mmt.distribution_account_id),

                           'Account alias', (SELECT mgd.segment1

                                               FROM inv.mtl_generic_dispositions mgd

                                              WHERE mgd.disposition_id =

                                                        mmt.transaction_source_id),

                           'Inventory', mmt.transaction_source_name,

                           'Job or Schedule', (SELECT we.wip_entity_name

                                                 FROM wip.wip_entities we

                                                WHERE we.wip_entity_id =

                                                          mmt.transaction_source_id),

                           'Move order', mmt.transaction_source_id,

                           'Physical Inventory', (SELECT mpi.physical_inventory_name

                                                    FROM inv.mtl_physical_inventories mpi

                                                   WHERE mpi.physical_inventory_id =

                                                             mmt.transaction_source_id),

                           'Purchase order', (SELECT pha.segment1

                                                FROM po.po_headers_all pha

                                               WHERE pha.po_header_id =

                                                         mmt.transaction_source_id),

                           'RMA', (SELECT    mso.segment1

                                          || '.'

                                          || mso.segment2

                                          || '.'

                                          || mso.segment3

                                     FROM inv.mtl_sales_orders mso

                                    WHERE mso.sales_order_id = mmt.transaction_source_id),

                           'Sales order', (SELECT    mso.segment1

                                                  || '.'

                                                  || mso.segment2

                                                  || '.'

                                                  || mso.segment3

                                             FROM inv.mtl_sales_orders mso

                                            WHERE mso.sales_order_id =

                                                      mmt.transaction_source_id),

                           mmt.transaction_source_name)

                           source

                  FROM inv.mtl_material_transactions mmt, inv.mtl_txn_source_types mtst

                WHERE mtst.transaction_source_type_id = mmt.transaction_source_type_id