GME OPM Outside Processing

            SELECT gbh.batch_id batch_id,
                   gbh.batch_no batch_no,
                   gl.meaning status,
                   gbh.plan_start_date sched_start_date,
                   gbh.plan_cmplt_date sched_cmplt_date,
                   msib.concatenated_segments product,
                   gmd.dtl_um uom,
                   gmd.plan_qty scheduled_qty,
                   gmd.actual_qty qty_completed       
              FROM gme_batch_header gbh,
                   gme_material_details gmd,
                   wip_entities we,
                   mtl_system_items_kfv msib,
                   gem_lookups gl
             WHERE (:p_batch_from IS NULL OR
                    (:p_batch_from IS NOT NULL AND 
                    gbh.batch_no >= :p_batch_from))
               AND (:p_batch_to IS NULL OR
                   (:p_batch_to IS NOT NULL AND 
                    gbh.batch_no <= :p_batch_to))
               AND gbh.organization_id = :p_organization_id     
               AND gbh.batch_id = gmd.batch_id
               AND (:p_product_from IS NULL OR
                    (:p_product_from IS NOT NULL AND 
                     msib.concatenated_segments >= :p_product_from))
               AND (:p_product_to IS NULL OR
                   (:p_product_to IS NOT NULL AND 
                    msib.concatenated_segments <= :p_product_to))
               AND  msib.organization_id = :p_organization_id     
               AND (:p_batch_status IS NULL OR
                   (:p_batch_status IS NOT NULL AND
                    gbh.batch_status = :p_batch_status))
               AND gbh.batch_id = we.wip_entity_id
               AND we.primary_item_id = gmd.inventory_item_id
               AND msib.inventory_item_id = gmd.inventory_item_id
               AND msib.organization_id = gbh.organization_id
               AND gmd.line_type = 1
               AND gl.lookup_type = 'GME_BATCH_STATUS'
               AND gbh.batch_status = gl.lookup_code
               AND (NVL(:p_open_po_flag,'N') = 'N' OR
                    (:p_open_po_flag = 'Y' AND
                     EXISTS (   SELECT 'PO/REQ Linked'
                                  FROM po_releases_all pr,
                                       po_headers_all ph,
                                       po_distributions_all pd,
                                       po_line_locations_all pll
                                 WHERE pd.po_line_id IS NOT NULL
                                   AND pd.line_location_id IS NOT NULL
                                   AND pd.wip_entity_id = gbh.batch_id
                                   AND pd.destination_organization_id = gbh.organization_id
                                   AND ph.po_header_id = pd.po_header_id
                                   AND pll.line_location_id = pd.line_location_id
                                   AND pr.po_release_id (+) = pd.po_release_id
                                   AND (pll.cancel_flag IS NULL OR
                                        pll.cancel_flag = 'N')
                                   AND (pll.quantity_received < (pll.quantity-pll.quantity_cancelled))
                                   AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED'
                                UNION ALL
                                SELECT 'PO/REQ Linked'
                                  FROM po_requisition_lines_all prl
                                 WHERE prl.wip_entity_id = gbh.batch_id
                                   AND prl.destination_organization_id = gbh.organization_id
                                   AND nvl(prl.cancel_flag, 'N') = 'N'
                                   AND prl.line_location_id IS NULL
                                UNION ALL
                                SELECT 'PO/REQ Linked'
                                  FROM po_requisitions_interface_all pri
                                 WHERE pri.wip_entity_id = gbh.batch_id
                                   AND pri.destination_organization_id = gbh.organization_id)))
             ORDER BY gbh.batch_id