|
1
|
SELECT TO_NUMBER (d.civil_id) civil,
|
|
2
|
sal_employee_data.get_emp_name (civil_id) nam
|
|
3
|
FROM nmp_licnfile_v lfv, nmp_delegate d
|
|
4
|
WHERE d.lf_recid_file_type = 1
|
|
5
|
AND lfv.fle_recid = lf_recid_file
|
|
6
|
and lfv.LF_RECID = :LF_RECID
|
|
7
|
AND :p_applicant_type = 9
|
|
8
|
UNION
|
|
9
|
SELECT TO_NUMBER (d.civil_id) civil,
|
|
10
|
sal_employee_data.get_emp_name (civil_id) nam
|
|
11
|
FROM nmp_licnfile_v lfv, nmp_delegate d
|
|
12
|
WHERE d.lf_recid_file_type = 2
|
|
13
|
AND lfv.lf_recid = lf_recid_file
|
|
14
|
and lfv.LF_RECID = :LF_RECID
|
|
15
|
AND lfv.lf_type = 1
|
|
16
|
AND :p_applicant_type = 9
|
|
17
|
UNION
|
|
18
|
SELECT TO_NUMBER (d.civil_id) civil,
|
|
19
|
sal_employee_data.get_emp_name (civil_id) nam
|
|
20
|
FROM nmp_licnfile_v lfv, nmp_delegate d
|
|
21
|
WHERE
|
|
22
|
d.lf_recid_file_type = 3
|
|
23
|
AND lfv.lf_recid = d.lf_recid_file
|
|
24
|
and lfv.LF_RECID = :LF_RECID
|
|
25
|
AND lfv.lf_type = 2
|
|
26
|
AND :p_applicant_type = 9
|
|
27
|
UNION
|
|
28
|
SELECT TO_NUMBER (l.civil_id) civil,
|
|
29
|
nvl( sal_employee_data.get_emp_name (civil_id) ,l.COMPANY_NAME) nam
|
|
30
|
FROM pmp_licn_req l , NMP_LICNFILE LF
|
|
31
|
WHERE L.LICN_REQ_RECID = LF.LIC_RECID
|
|
32
|
and L.LICN_STAGE >= 12
|
|
33
|
and lf.LF_RECID = :LF_RECID
|
|
34
|
AND :p_applicant_type = 10
|
|
35
|
UNION
|
|
36
|
SELECT TO_NUMBER (s.civil_id) civil,
|
|
37
|
sal_employee_data.get_emp_name (civil_id) nam
|
|
38
|
FROM pmp_author_signer s, nmp_licnfile_v lfv
|
|
39
|
WHERE s.active = 1
|
|
40
|
AND s.legality = 'AUTHORIZED'
|
|
41
|
AND form_status NOT IN (3)
|
|
42
|
AND s.migr_found_flag = 1
|
|
43
|
AND (record_status = 3 OR form_status = 2)
|
|
44
|
AND lf_recid_file_type = 1
|
|
45
|
AND s.lf_recid_file = lfv.fle_recid
|
|
46
|
--AND lfv.licn_con_no = :LICN_INFO.LICN_CIVIL_ID
|
|
47
|
and lfv.LF_RECID = :LF_RECID
|
|
48
|
AND :p_applicant_type = 14
|
|
49
|
UNION
|
|
50
|
|
|
51
|
SELECT TO_NUMBER (s.civil_id) civil,
|
|
52
|
sal_employee_data.get_emp_name (civil_id) nam
|
|
53
|
FROM pmp_author_signer s, nmp_licnfile_v lfv
|
|
54
|
WHERE s.active = 1
|
|
55
|
AND s.legality = 'AUTHORIZED'
|
|
56
|
AND form_status NOT IN (3)
|
|
57
|
AND s.migr_found_flag = 1
|
|
58
|
AND (record_status = 3 OR form_status = 2)
|
|
59
|
AND lf_recid_file_type = 2
|
|
60
|
AND s.lf_recid_file = lfv.lf_recid
|
|
61
|
AND lfv.lf_type = 1
|
|
62
|
and lfv.LF_RECID = :LF_RECID
|
|
63
|
AND :p_applicant_type = 14
|
|
64
|
UNION
|
|
65
|
SELECT TO_NUMBER (s.civil_id) civil,
|
|
66
|
sal_employee_data.get_emp_name (civil_id) nam
|
|
67
|
FROM pmp_author_signer s, nmp_licnfile_v lfv
|
|
68
|
WHERE s.active = 1
|
|
69
|
AND s.legality = 'AUTHORIZED'
|
|
70
|
AND form_status NOT IN (3)
|
|
71
|
AND s.migr_found_flag = 1
|
|
72
|
AND (record_status = 3 OR form_status = 2)
|
|
73
|
AND lf_recid_file_type = 3
|
|
74
|
AND s.lf_recid_file = lfv.lf_recid
|
|
75
|
AND lfv.lf_type = 2
|
|
76
|
and lfv.LF_RECID = :LF_RECID
|
|
77
|
AND :p_applicant_type = 14
|