current allocated/future/released '&' IN PL-SQL

 

select    *
  from (select sub_user_pro.emp_id,
               sub_user_pro.emp_name,
               er.role_name,
               ep.project_name,
               ep.project_won,
               eup.project_id, -- NO NEED TO SHOWN
               to_char(eup.join_date, 'MM/DD/YYYY') as join_date,
               to_char(eup.expected_release_date, 'MM/DD/YYYY') as expected_release_date,
               to_char(ep.project_start_date, 'MM/DD/YYYY') as project_start_date,
               to_char(ep.project_end_date, 'MM/DD/YYYY') as project_end_date,
               sub_user_pro.status,
               eu_pm.first_name || ' ' || eu_pm.last_name as pm_name,
               eu_am.first_name || ' ' || eu_am.last_name as am_name
          from eis_user_projects eup,
               eis_projects ep,
               eis_roles er,
               eis_users eu_pm,
               eis_users eu_am,
               (select eu.id as emp_id,
                       eu.first_name || ' ' || eu.last_name emp_name,
                       decode(sub_current.status,
                              null,
                              decode(sub_future.status,
                                     null,
                                     sub_past.status,
                                     sub_future.status),
                              sub_current.status) as status,
                       decode(sub_current.eup_Id,
                              null,
                              decode(sub_future.eup_Id,
                                     null,
                                     sub_past.eup_Id,
                                     sub_future.eup_Id),
                              sub_current.eup_Id) as eup_Id
                  from eis_users eu,
                       (select eup.id as eup_Id,
                               eup.emp_id,
                               
                               (case
                                 when (EXTRACT(year FROM expected_release_date) =
                                      EXTRACT(year FROM sysdate)) and
                                      (EXTRACT(month FROM expected_release_date) =
                                      EXTRACT(month FROM sysdate)) then
                                 ----------------------------------------------------------------------------------------------------------------------
                                  case
                                 when (EXTRACT(DAY FROM expected_release_date) =
                                      to_number( TO_CHAR( LAST_DAY(sysdate) ,'dd')) ) then
                                  'Month-end Released'
                                 else
                                  'Released before Month end'
                               end
                               --
                               
                                else 'Allocated'
                               
                                end) as status
                          from eis_user_projects eup
                         where to_date(to_char(sysdate, 'dd/mm/yyyy'),
                                       'dd/mm/yyyy') BETWEEN eup.join_date AND
                               EUP.EXPECTED_RELEASE_DATE) sub_current,
                       
                       (select eup.id as eup_Id,
                               eup.emp_id,
                               'Future Allocated' STATUS
                          from eis_user_projects eup,
                               (select eup.emp_id, min(eup.join_date) join_date
                                  from eis_user_projects eup
                                 where to_date(to_char(sysdate, 'dd/mm/yyyy'),
                                               'dd/mm/yyyy') < eup.join_date
                                 group by eup.emp_id) latest_future_pro
                        
                         where eup.emp_id = latest_future_pro.emp_id
                           and eup.join_date = latest_future_pro.join_date) sub_future,
                       
                       (select eup.id as eup_Id,
                               eup.emp_id,
                               'UnAllocated' STATUS
                          from eis_user_projects eup,
                               (select eup.emp_id, max(eup.join_date) join_date
                                  from eis_user_projects eup
                                 where to_date(to_char(sysdate, 'dd/mm/yyyy'),
                                               'dd/mm/yyyy') >
                                       eup.expected_release_date
                                 group by eup.emp_id) latest_past_pro
                        
                         where eup.emp_id = latest_past_pro.emp_id
                           and eup.join_date = latest_past_pro.join_date) sub_past
                 where eu.id = sub_past.emp_id(+)
                   and eu.id = sub_current.emp_id(+)
                   and eu.id = sub_future.emp_id(+)
                      --and eu.Id <>801660
                   and eu.active_status = 'Y') sub_user_pro
         where sub_user_pro.eup_id = eup.id
           and eup.project_id = ep.id
           and eup.user_role_id = er.id
           and sub_user_pro.emp_id = eup.emp_id
           and eu_pm.id = ep.pm_emp_id
           and eu_am.id = ep.am_emp_id
              ---------- AM PM SM
           and (('&in_role_abbrv' = 'AM' and ep.am_emp_id = '&in_emp_id') or
               ('&in_role_abbrv' = 'PM' and ep.pm_emp_id = '&in_emp_id')
               
               or ('&in_role_abbrv' = 'SM'))
        -----------
        --and ep.pm_emp_id = IN_EMP_iD
        --and ep.am_emp_id = 801660
        ) sub_no_condition
 where (('&in_searchEmp_id' is null) or
       ('&in_searchEmp_id' is not null and
       sub_no_condition.emp_id = '&in_searchEmp_id'))
   and (('&in_status' is null) or ('&in_status' is not null and
       sub_no_condition.status = '&in_status'))

 --ORDER BY emp_id

/*) SUB_RESULT
               WHERE ROWNUM <= v_last_record)
       WHERE RANK >= v_first_record;*/