UnClosed Production Batch(OPM) in Oracle EBS r12

   

 SELECT   locations || '_XX' locations, org_code, org_name,

            COUNT (batch_id) numberofbatch, batch_status

       FROM (SELECT SUBSTR (o.organization_name, 1, 3) locations,

                    o.organization_code org_code,

                    o.organization_name org_name, bh.batch_id, bh.batch_no,

                    bh.actual_start_date, bh.actual_cmplt_date,

                    bh.batch_close_date,

                    DECODE (batch_status,

                            2, 'WIP',

                            3, 'Completed'

                           ) batch_status

               FROM gme_batch_header bh, org_organization_definitions o

              WHERE bh.batch_status IN (2, 3)

                AND bh.organization_id = o.organization_id

                AND TRUNC (bh.actual_start_date) > '31-DEC-2002')

   GROUP BY locations, org_code, org_name, batch_status

   ORDER BY 1;