http://portal.bwdb.gov.bd:9001/forms/frmservlet?config=bwdb

http://portal.bwdb.gov.bd:7777/forms/frmservlet?config=bwdb

 SELECT o.office_code,
         o.control_office,
         INITCAP (co.office_name) control_office_name,
         p.office,
         INITCAP (o.office_name||' ('||o.office_code||')') office_name,
         p.control_post,
         INITCAP (cp.post_name) control_post_name,
        INITCAP ( ' '||p.post_name) post_name,
         p.head_of_office,
         p.create_order_date,
         p.abolish_order_date,
         r.no_of_post,
         g.employee, 
         INITCAP (e.employee_name) employee_name,
         p.post,
         p.designation,
         INITCAP (d.designation_name) designation_name,
         g.joining_date,
         g.release_date,
         p.post_type,
         INITCAP(t.post_type_name),
         DECODE(g.employee, NULL, 'Y', 'N') vacant 
    FROM hr.hr_organogram p
         full outer join (hr.hr_posting g join hr.hr_employee e on g.employee = e.employee) on p.POST = g.POST,
         (select office||post_name office_post, count(*) no_of_post from hr.hr_organogram where post_type='R' group by office, post_name) r,
         hr.hr_office o,
         hr.hr_organogram cp,
         hr.hr_office co,
         hr.hr_designation d, 
         hr.hr_post_type t
   WHERE     p.post_type = t.post_type
         AND p.office||p.post_name = r.office_post
         AND p.office = o.office
         AND p.control_post = cp.POST
         AND NVL(o.control_office,o.office) = co.office
         AND p.designation = d.designation
         AND NVL (o.control_office,0)                   LIKE NVL (:p_control_office, '%')
         AND NVL (p.office,0)                           LIKE NVL (:p_office, '%')
         AND NVL (o.office_level,'0')                   LIKE NVL (:p_level,'%')
         AND NVL (o.office_type,'0')                    LIKE NVL (:p_type,'%')
         AND NVL (o.district_gcode,'0')                 LIKE NVL (:p_district,'%')
         AND NVL (o.region,'0')                         LIKE NVL (:p_region,'%')
         AND NVL (o.zone,'0')                           LIKE NVL (:p_zone,'%')
         AND NVL (o.circle,'0')                         LIKE NVL (:p_circle,'%')
         AND NVL (o.division,'0')                       LIKE NVL (:p_division,'%')
         AND NVL (o.subdivision,'0')                    LIKE NVL (:p_subdivision,'%')
         AND NVL (p.control_post, 0)                    LIKE NVL (:p_control_post, '%')
         AND NVL (p.post, 0)                            LIKE NVL (:p_post, '%')
         AND NVL (p.post_name, '0')                     LIKE NVL (:p_post_name, '%')
         AND NVL (p.designation, '0')                   LIKE NVL (:p_designation, '%')
         AND NVL (p.head_of_office, '0')                LIKE NVL (:p_head, '%')
         AND NVL (p.post_type, '0')                     LIKE NVL (:p_post_type, '%')
         AND DECODE (p.abolish_order_date,NULL,'C','E') LIKE NVL (:p_current, '%')
         AND NVL (p.create_order_date, '01-JAN-1951')   BETWEEN TO_DATE (NVL (:p_create_from, '01/01/1951'), 'DD/MM/RRRR')
                                                            AND TO_DATE (NVL (:p_create_upto, TO_CHAR (SYSDATE, 'DD/MM/RRRR')), 'DD/MM/RRRR')
         AND NVL (p.abolish_order_date, TRUNC(SYSDATE)) BETWEEN TO_DATE (NVL (:p_abolish_from, '01/01/1951'), 'DD/MM/RRRR')
                                                            AND TO_DATE (NVL (:p_abolish_upto, TO_CHAR (SYSDATE, 'DD/MM/RRRR')), 'DD/MM/RRRR')
         AND NVL (g.employee, '0')                      LIKE NVL (:p_employee, '%')
         AND NVL (e.employee_name, '0')                 LIKE NVL (:p_employee_name, '%')
         AND NVL (g.posting_order, '0')                 LIKE NVL (:p_post_order, '%')
         AND DECODE (g.employee, NULL, 'Y', 'N')        LIKE NVL (:p_vacant,'%')
         AND NVL (g.joining_date, '01-JAN-1951')        BETWEEN TO_DATE (NVL (:p_join_from, '01/01/1951'), 'DD/MM/RRRR')
                                                            AND TO_DATE (NVL (:p_join_upto, TO_CHAR (SYSDATE, 'DD/MM/RRRR')), 'DD/MM/RRRR')
         AND NVL (g.release_date, TRUNC (SYSDATE))      BETWEEN TO_DATE (NVL (:p_release_from, '01/01/1951'), 'DD/MM/RRRR')
                                                            AND TO_DATE (NVL (:p_release_upto, TO_CHAR (SYSDATE, 'DD/MM/RRRR')), 'DD/MM/RRRR')
ORDER BY o.office_code,
         o.control_office,
         p.office,
         p.head_of_office DESC,
         p.control_post,
         p.post,
         p.designation,
         g.employee