Tuesday 14 May 2013

Inventory Lot Transaction Query

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

No comments:

Post a Comment