8 Replies Latest reply on Nov 12, 2015 7:15 PM by Miguel B

    Join between mtl_material_transactions and po_lines_all pla

    Miguel B

      Hey there! i hope you can help me.

      i'm doing a query to join po_headers_all, po_lines_all, mtl_transaction_accounts and mtl_material_transactios.

      But, when i add the po_lines_all table it make me a cross join with mtl_material_transactions.

      i have one purchase (i.e 12015) order with two lines, and 2 receptions on inventory.

      the first move was the the PO 12015 had one line and we made the reception on inventory.

      later, for another stage, we add one more line to 12015 PO and then we made the respective reception and for that PO we have two receptions, but when i join po_lines_all it make me 2 receptions cross 2 and the result is 4 rows.

      i don't know what i'm doing wrong, if you can help me, that will be appreciated.

      This is my query:

       

      SELECT poh.revision_num,
             mmt.transaction_id,
             mmt.transaction_quantity cantidad_recepcion,
             mmt.organization_id,
             MSI.PRIMARY_UOM_CODE uom_recepcion,
             DECODE (
                MMT.TRANSACTION_TYPE_ID,
                24, NULL,
                80, NULL,
                DECODE (
                   MTA.PRIMARY_QUANTITY,
                   0, NULL,
                   NULL, NULL,
                   DECODE (MTA.ACCOUNTING_LINE_TYPE,
                           1, MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                           14, MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                           3, MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                           ABS (MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY))))
                unit_price_recepcion,
             mmt.inventory_item_id,
             mmt.transaction_source_id,
             poh.po_header_id,
             poh.segment1,
             pol.item_id,
             pol.UNIT_MEAS_LOOKUP_CODE uom_OC,
             (SELECT uom_code
                FROM mtl_uom_conversions
               WHERE     1 = 1
                     AND inventory_item_id = 0
                     AND NVL (disable_date, SYSDATE) >= SYSDATE
                     AND unit_of_measure = pol.UNIT_MEAS_LOOKUP_CODE)
                UOM_CODE_OC,
             pol.unit_price unit_price_OC,
             pol.quantity cantidad_OC
        FROM mtl_transaction_accounts mta,
             MTL_SYSTEM_ITEMS_VL MSI,
             mtl_material_transactions mmt,
             po_headers_all poh,
             po_lines_all pol
       WHERE     1 = 1
             AND mta.transaction_id = mmt.transaction_id
             AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
             AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
             AND poh.po_header_id = pol.po_header_id
             AND poh.po_header_id = mmt.transaction_source_id
             AND mmt.INVENTORY_ITEM_ID = pol.ITEM_ID
             AND mta.inventory_item_id = mmt.inventory_item_id
             AND mmt.transaction_source_id = 3502169
             AND mta.accounting_line_type = 5;
      

       

       

      the line  "AND mta.accounting_line_type = 5" line is because i want only the type "Receiving Inspection".

       

      Thanks a lot.