SELECT jb.span_id,
             jb.maintenancezonecode,
             jbp.job_progress_id                                   AS req_id,
             jb.reofferflag,
             modified_by                                           AS
             last_update_by
             ,
             sm.status_name,
             To_date(SYSDATE) - To_date(jbp.ums_group_ass_to_date) AS
             pending_days
      FROM   tbl_fiber_inv_jobs jb
             inner join tbl_fiber_inv_job_progress jbp
                     ON jb.job_id = jbp.job_id
             inner join tbl_fiber_inv_status_master sm
                     ON jbp.status_id = sm.status_id
      WHERE  jb.maintenancezonecode = pmaintzonecode
             AND ums_group_ass_to_name = pusertype
             AND jb.span_type = pspantype
             AND jbp.job_progress_flag = 1
      ORDER  BY jbp.job_progress_id DESC


UNION ALL
  
            SELECT
                                      TO_CHAR(TRIM(rj_span_id)) AS SPAN_ID, TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE
                                       FROM NE.MV_SPAN@ne
                                   WHERE
                                        Length(Trim(rj_span_id)) = 21
                 AND Regexp_like (Trim (rj_span_id),
  'SP(N|Q|R|S).*+_(AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$'
  , 'i')
  AND NOT Regexp_like (Nvl (rj_intracity_link_id, '-'), '_(9)', 'i')
  AND inventory_status_code = 'IPL'
  AND rj_maintenance_zone_code = pmaintzonecode;