lot number tables in oracle apps

lot number tables in oracle apps

 

In this post , We will discuss about lot numbers tables in oracle apps. These table helps to store the lot number information’s in oracle apps. Here below is the complete list of lot number tables in oracle apps.

mtl_transaction_lot_numbers

mtl_unit_transactions

Sql Query Using the lot number tables in oracle apps

 

SELECT   msi.concatenated_segments item,

            REPLACE (msi.description, '~', '-') item_description,

            mtl.lot_number lot_number,

            mmt.subinventory_code subinventory,

            TRUNC (mtl.transaction_date) transaction_date,

            mmt.transaction_id transaction_number,

            mmt.transaction_set_id transaction_set,

            mmt.transfer_transaction_id transfer_transaction_number,

            mtl.creation_date creation_date,

            oap.period_name gl_period,

            oap.period_start_date gl_period_start_date,

            DECODE (mmt.transaction_source_type_id,

                    11, mmt.new_cost - mmt.prior_cost,

                    mmt.actual_cost)

               item_cost,

            mmt.revision item_revision,

            milt.concatenated_segments transfer_stock_locators,

            ppa.NAME project_name,

            ppa.segment1 project_number,

            DECODE (

               mut.serial_number,

               NULL,

               DECODE (

                  mtl.lot_number,

                  NULL,

                  DECODE (mmt.transaction_source_type_id,

                          11, mmt.quantity_adjusted,

                          mmt.primary_quantity),

                  mtl.primary_quantity

               ),

               1

            )

               quantity,

            mtr.reason_name reason,

            mut.serial_number serial_number,

            pt.task_name task_name,

            pt.task_number task_number,

           DECODE (

               mut.serial_number,

               NULL,

               DECODE (

                  mtl.lot_number,

                  NULL,

                  DECODE (mmt.transaction_source_type_id,

                          11, mmt.quantity_adjusted,

                          mmt.transaction_quantity),

                  mtl.transaction_quantity

               ),

               1

            )

               transaction_quantity,

            mmt.transaction_reference transaction_reference,

            mtl.transaction_source_name transaction_source_name,

            mts.transaction_source_type_name,

            mtt.transaction_type_name transaction_type,

            mmt.transaction_uom transaction_unit_of_measure,

            mmt.transfer_subinventory transfer_subinventory,

            ood.organization_name transfer_to_from,

            msi.primary_unit_of_measure unit_of_measure,

            DECODE (mmt.costed_flag, 'N', 'No', NVL (mmt.costed_flag, 'Yes'))

               valued_flag,

            mtt.transaction_type_id,

            milt.inventory_location_id TRANSFER_INV_LOCATION_ID,

            mil.inventory_location_id,

            mil.concatenated_segments stock_locator,

            oap.acct_period_id,

            pt.task_id,

            ppa.project_id,

            msi.inventory_item_id,

            mtr.reason_id,

            mtl.transaction_source_id,

            mtl.serial_transaction_id,

            mtl.vendor_name,

            mtl.supplier_lot_number,

            msi.organization_id,

            haou.NAME inv_org_name,

            mmt.transaction_source_type_id,

            mmt.transaction_action_id,

            mmt.department_id,

            mmt.error_explanation,

            mmt.vendor_lot_number supplier_lot,

            mmt.source_line_id,

            mmt.parent_transaction_id,

            mmt.shipment_number shipment_number,

            mmt.waybill_airbill waybill_airbill,

            mmt.freight_code freight_code,

            mmt.number_of_containers,

            mmt.rcv_transaction_id,

            mmt.move_transaction_id,

            mmt.completion_transaction_id,

            mmt.operation_seq_num opertion_sequence,

            mmt.expenditure_type,

            mmt.transaction_set_id,

            mmt.transaction_uom,

            mmt.transfer_transaction_id,

            ROUND ( (mmt.primary_quantity * mmt.actual_cost),

                   fnd_profile.VALUE ('REPORT_QUANTITY_PRECISION'))

               VALUE,

            MIL.ORGANIZATION_ID MIL_ORGANIZATION_ID,

            MILT.ORGANIZATION_ID MILT_ORGANIZATION_ID,

            ood.organization_id OOD_ORGANIZATION_ID

     FROM   mtl_transaction_types mtt,

            mtl_item_locations_kfv milt,

            mtl_item_locations_kfv mil,

            org_acct_periods oap,

            pa_tasks pt,

            pa_projects ppa,

            mtl_system_items_kfv msi,

            mtl_material_transactions mmt,

            mtl_unit_transactions mut,

            mtl_transaction_lot_numbers mtl,

            mtl_parameters mp,

            mtl_transaction_reasons mtr,

            hr_all_organization_units haou,

            org_organization_definitions ood,

            mtl_txn_source_types mts

    WHERE       msi.organization_id = mp.organization_id

            AND mmt.transaction_id = mtl.transaction_id

            AND mmt.inventory_item_id = mtl.inventory_item_id

            AND mmt.organization_id = mtl.organization_id

            AND mtl.serial_transaction_id = mut.transaction_id(+)

            AND mmt.organization_id = mp.organization_id

            AND mmt.inventory_item_id = msi.inventory_item_id

            AND oap.organization_id(+) = mmt.organization_id

            AND oap.acct_period_id(+) = mmt.acct_period_id

            AND mmt.project_id = ppa.project_id(+)

            AND mmt.task_id = pt.task_id(+)

            AND mmt.locator_id = mil.inventory_location_id(+)

            AND mil.organization_id(+) = mmt.organization_id

            AND mmt.transfer_locator_id = milt.inventory_location_id(+)

            AND milt.organization_id(+) = mmt.organization_id

            AND mmt.transaction_type_id = mtt.transaction_type_id(+)

            AND mtr.reason_id(+) = mmt.reason_id

            AND haou.organization_id = msi.organization_id

            AND mmt.transfer_organization_id = ood.organization_id(+)

            AND mts.transaction_source_type_id =

                  mmt.transaction_source_type_idntory;