SELECT TO_NUMBER (d.civil_id) civil,  
       sal_employee_data.get_emp_name (civil_id) nam 
  FROM nmp_licnfile_v lfv, nmp_delegate d 
 WHERE d.lf_recid_file_type = 1 
   AND lfv.fle_recid = lf_recid_file  
   and   lfv.LF_RECID = :LF_RECID 
    AND :p_applicant_type = 9 
UNION 
SELECT TO_NUMBER (d.civil_id) civil, 
       sal_employee_data.get_emp_name (civil_id) nam 
  FROM nmp_licnfile_v lfv, nmp_delegate d 
 WHERE d.lf_recid_file_type = 2 
   AND lfv.lf_recid = lf_recid_file 
     and   lfv.LF_RECID = :LF_RECID 
   AND lfv.lf_type = 1 
   AND :p_applicant_type = 9 
UNION 
SELECT TO_NUMBER (d.civil_id) civil, 
       sal_employee_data.get_emp_name (civil_id) nam 
  FROM nmp_licnfile_v lfv, nmp_delegate d 
 WHERE  
   d.lf_recid_file_type = 3 
   AND lfv.lf_recid = d.lf_recid_file 
    and   lfv.LF_RECID = :LF_RECID 
   AND lfv.lf_type = 2 
   AND :p_applicant_type = 9 
UNION 
  SELECT TO_NUMBER (l.civil_id) civil, 
       nvl( sal_employee_data.get_emp_name (civil_id) ,l.COMPANY_NAME) nam 
  FROM pmp_licn_req l , NMP_LICNFILE LF  
  WHERE       L.LICN_REQ_RECID = LF.LIC_RECID 
  and      L.LICN_STAGE >= 12  
  and   lf.LF_RECID = :LF_RECID 
  AND :p_applicant_type = 10 
UNION 
SELECT TO_NUMBER (s.civil_id) civil, 
       sal_employee_data.get_emp_name (civil_id) nam 
  FROM pmp_author_signer s, nmp_licnfile_v lfv 
 WHERE s.active = 1 
  AND s.legality = 'AUTHORIZED' 
   AND form_status NOT IN (3) 
   AND s.migr_found_flag = 1 
   AND (record_status = 3 OR form_status = 2) 
   AND lf_recid_file_type = 1 
   AND s.lf_recid_file = lfv.fle_recid 
   --AND lfv.licn_con_no = :LICN_INFO.LICN_CIVIL_ID 
   and   lfv.LF_RECID = :LF_RECID 
   AND :p_applicant_type = 14 
UNION 
 
SELECT TO_NUMBER (s.civil_id) civil, 
       sal_employee_data.get_emp_name (civil_id) nam 
  FROM pmp_author_signer s, nmp_licnfile_v lfv 
 WHERE s.active = 1 
   AND s.legality = 'AUTHORIZED' 
   AND form_status NOT IN (3) 
   AND s.migr_found_flag = 1 
   AND (record_status = 3 OR form_status = 2) 
   AND lf_recid_file_type = 2 
   AND s.lf_recid_file = lfv.lf_recid 
   AND lfv.lf_type = 1 
     and   lfv.LF_RECID = :LF_RECID 
   AND :p_applicant_type = 14 
UNION 
SELECT TO_NUMBER (s.civil_id) civil, 
       sal_employee_data.get_emp_name (civil_id) nam 
  FROM pmp_author_signer s, nmp_licnfile_v lfv 
 WHERE s.active = 1 
   AND s.legality = 'AUTHORIZED' 
   AND form_status NOT IN (3) 
   AND s.migr_found_flag = 1 
   AND (record_status = 3 OR form_status = 2) 
   AND lf_recid_file_type = 3 
   AND s.lf_recid_file = lfv.lf_recid 
   AND lfv.lf_type = 2 
    and   lfv.LF_RECID = :LF_RECID 
   AND :p_applicant_type = 14