SELECT
   tid,
   productSerialNumber,
   labelSerialNumber,
    NVL(formatId, 'N/A') formatId,
   NVL(formatIdDesc, 'N/A') formatIdDesc,
   NVL(labelPartNumber, 'N/A') labelPartNumber,
   stageDesc,
              to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss') timestamp,
              userId,
              cnt
   FROM (SELECT ProductData.*,
                row_number() over(ORDER BY
               CASE
              WHEN :orderDirection like '%asc%' THEN
               CASE
                    WHEN :orderBy='productSerialNumber' THEN productSerialNumber
                    WHEN :orderBy='labelSerialNumber' THEN labelSerialNumber
                    WHEN :orderBy='formatId' THEN formatId
                    WHEN :orderBy='formatIdDesc' THEN formatIdDesc
                    WHEN :orderBy='labelPartNumber' THEN labelPartNumber
      WHEN :orderBy='prodDesc' THEN prodDesc
                    WHEN :orderBy='timestamp' THEN to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss')
                    WHEN :orderBy='userId' THEN userId
                    ELSE to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss')
                END

          END ASC,
          CASE
              WHEN :orderDirection like '%desc%' THEN
               CASE
                    WHEN :orderBy='productSerialNumber' THEN productSerialNumber
                    WHEN :orderBy='labelSerialNumber' THEN labelSerialNumber
                    WHEN :orderBy='formatId' THEN formatId
                    WHEN :orderBy='formatIdDesc' THEN formatIdDesc
                    WHEN :orderBy='labelPartNumber' THEN labelPartNumber
                    WHEN :orderBy='stageDesc' THEN stageDesc
                    WHEN :orderBy='timestamp' THEN to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss')
                    WHEN :orderBy='userId' THEN userId
                    ELSE to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss')
               END

          END DESC
               , tid ASC) line_number
           FROM (select ProductData_inner.*, COUNT(1) OVER() cnt
                   from (select pd_tran.tid,
                                pd_res.engine_serial_number productSerialNumber,
                                pd_res.machine_serial_number labelSerialNumber,
                                lf.format_id formatId,
                                lf.format_id_desc formatIdDesc,
                                NULLIF(pd_tran.format, 'DSEL Labels') labelPartNumber,
                                stagelu.stage_desc stageDesc,
                                pd_res.created_timestamp timestamp,
                                pd_res.cws_id userId
                           from pd_RESULTS pd_res
                           left outer join pd_TRANSACTION pd_tran on pd_tran.results_id = pd_res.result_id
                           left outer join pd_TRANSACTION_FIELDS pd_tran_flds on pd_tran.tid =
                                                                                     pd_tran_flds.tid
                                                                                 and (pd_tran_flds.field_name =
                                                                                     '-CERT_AR-' or
                                                                                     pd_tran_flds.field_name =
                                                                                     'cert_arrangement')
                           left outer join LABEL_FORMAT lf on lf.label_part_num = pd_tran.format
                           inner join stage_lu stagelu on stagelu.stage_id=pd_res.stage_id
                          where (:productSerialNumber is null or
                                pd_res.engine_serial_number =
                                :productSerialNumber)
                            and (:labelSerialNumber is null or
                                pd_res.machine_serial_number = :labelSerialNumber)
                            and ('nullValue' = :formatDescFlag or
                                TRIM(lf.format_id_desc) in (:formatDesc))
                            and (:formatId is null or
                                lf.format_id = :formatId)
                            and (:fromDate is null or
                                pd_res.created_timestamp >=
                                to_date(:fromDate, 'mm/dd/yyyy'))
                            and (:todate is null or
                                pd_res.created_timestamp <
                                (to_date(:todate, 'mm/dd/yyyy') + 1))) ProductData_inner) ProductData)
    WHERE line_number > :iDisplayStart
    AND line_number <=
       (
             CASE
             WHEN ((:exportToExcelrequest is not null) and cnt>50000) THEN 50000
       WHEN ((:exportToExcelrequest is not null) and cnt<=50000) THEN cnt
          ELSE :iDisplayEnd
       END
     )