Identifying Duplicate Shipments SQL Query sql query to find duplicate shipments

Identifying Duplicate Shipments SQL Query

/* Formatted on 7/4/2014 1:26:24 PM (QP5 v5.115.810.9015) */
SELECT rsl.shipment_header_id, rsl.shipment_line_id, rsh.shipment_num
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
WHERE rsl.shipment_header_id = rsh.shipment_header_id
      AND rsh.creation_date >=
            TO_DATE ('&from_date', 'DD-MON-YYYY HH24:MI:SS')
      AND rsh.creation_date <= TO_DATE ('&to_date', 'DD-MON-YYYY HH24:MI:SS')
      AND rsl.mmt_transaction_id IS NOT NULL
      AND rsh.receipt_source_code = 'INVENTORY'
      AND EXISTS (SELECT 1
                  FROM mtl_parameters mp
                  WHERE mp.organization_id = rsh.organization_id)
      AND NOT EXISTS
            (SELECT 1
             FROM mtl_material_transactions mmt
             WHERE     mmt.transaction_id = rsl.mmt_transaction_id
                   AND mmt.transaction_source_type_id = 13
                   AND mmt.transaction_action_id IN (3, 21))
      AND NOT EXISTS
            (SELECT 1
             FROM mtl_material_transactions mmt, rcv_transactions rt
             WHERE     mmt.rcv_transaction_id = rt.transaction_id
                   AND rt.shipment_line_id = rsl.shipment_line_id
                   AND rt.transaction_type = 'DELIVER')
UNION
SELECT rsl.shipment_header_id, rsl.shipment_line_id, rsh.shipment_num
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
WHERE rsl.shipment_header_id = rsh.shipment_header_id
      AND rsh.creation_date >=
            TO_DATE ('&from_date', 'DD-MON-YYYY HH24:MI:SS')
      AND rsh.creation_date <= TO_DATE ('&to_date', 'DD-MON-YYYY HH24:MI:SS')
      AND rsl.mmt_transaction_id IS NOT NULL
      AND rsh.receipt_source_code = 'INTERNAL ORDER'
      AND EXISTS (SELECT 1
                  FROM mtl_parameters mp
                  WHERE mp.organization_id = rsh.organization_id)
      AND NOT EXISTS
            (SELECT 1
             FROM mtl_material_transactions mmt
             WHERE     mmt.transaction_id = rsl.mmt_transaction_id
                   AND mmt.transaction_source_type_id = 8
                   AND mmt.transaction_action_id IN (1, 3, 21, 2))
      AND NOT EXISTS
            (SELECT 1
             FROM mtl_material_transactions mmt, rcv_transactions rt
             WHERE     mmt.rcv_transaction_id = rt.transaction_id
                   AND rt.shipment_line_id = rsl.shipment_line_id
                   AND rt.transaction_type = 'DELIVER')
ORDER BY 1;