1 package body PER_ES_CREATE_CWK as
2 /* $Header: peescwkp.pkb 120.3 2006/09/14 13:24:43 mgettins noship $ */
3 g_package VARCHAR2(30) := 'PER_ES_CREATE_CWK.';
4
5 -- p_first_last_name p_last_name
6 -- p_identifier_type p_per_information2
7 -- p_identifier_value p_per_information3
8
9 PROCEDURE create_es_cwk (p_last_name VARCHAR2
10 ,p_first_name VARCHAR2
11 ,p_national_identifier VARCHAR2
12 ,p_per_information1 VARCHAR2
13 ,p_per_information2 VARCHAR2
14 ,p_per_information3 VARCHAR2
15 ) is
16
17 Cursor get_lookup_type(p_per_information2 varchar2) is
18 select lookup_code from hr_lookups where
19 lookup_type='ES_IDENTIFIER_TYPE'
20 and lookup_code=p_per_information2;
21
22 l_identifier_type hr_lookups.lookup_code%TYPE;
23 l_identifier_value VARCHAR2(10);
24 l_proc VARCHAR2(72) := g_package||'CREATE_ES_CWK';
25 l_national_identifier per_all_people_f.national_identifier%TYPE;
26 BEGIN
27 --
28 -- Added for GSI Bug 5472781
29 --
30 IF hr_utility.chk_product_install('Oracle Human Resources', 'ES') THEN
31 --
32 hr_api.mandatory_arg_error
33 (p_api_name => l_proc,
34 p_argument => hr_general.decode_lookup('ES_FORM_LABELS','LAST_NAME_1'),
35 p_argument_value => p_last_name
36 );
37
38 IF p_per_information3 IS NOT NULL AND
39 p_per_information2 IS NULL THEN
40 hr_utility.set_message(800,'HR_ES_INVALID_VALUE');
41 hr_utility.set_message_token(800,'FIELD',hr_general.decode_lookup('ES_FORM_LABELS','IDENTIFIER_TYPE'));
42 hr_utility.raise_error;
43 ELSIF p_per_information2 IS NOT NULL AND
44 p_per_information3 IS NULL THEN
45 OPEN get_lookup_type(p_per_information2);
46 FETCH get_lookup_type into l_identifier_type;
47 IF get_lookup_type%NOTFOUND THEN
48 hr_utility.set_message(800,'HR_ES_INVALID_VALUE');
49 hr_utility.set_message_token(800,'FIELD',hr_general.decode_lookup('ES_FORM_LABELS','IDENTIFIER_TYPE'));
50 hr_utility.raise_error;
51 END IF;
52 CLOSE get_lookup_type;
53 hr_utility.set_message(800,'HR_ES_INVALID_VALUE');
54 hr_utility.set_message_token(800,'FIELD',hr_general.decode_lookup('ES_IDENTIFIER_TYPE',p_per_information2));
55 hr_utility.raise_error;
56 END IF;
57 --
58
59 IF FND_PROFILE.VALUE('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' THEN --- Bug No 4718049
60 IF p_national_identifier IS NULL THEN
61 IF p_per_information3 IS NULL THEN
62 hr_utility.set_message(800, 'HR_ES_REQ_NIF_IDT_MISSING');
63 hr_utility.raise_error;
64 END IF;
65 ELSE
66 l_national_identifier := hr_es_utility.check_NIF(p_national_identifier);
67 IF l_national_identifier = 'N' THEN ---- Bug No 4718049
68 hr_utility.set_message(800, 'HR_ES_INVALID_NIF');
69 hr_utility.raise_error;
70 END IF;
71 END IF;
72 END IF;
73
74
75 IF p_per_information2 IS NOT NULL THEN
76 OPEN get_lookup_type(p_per_information2);
77 FETCH get_lookup_type into l_identifier_type;
78 IF get_lookup_type%NOTFOUND THEN
79 hr_utility.set_message(800,'HR_ES_INVALID_VALUE');
80 hr_utility.set_message_token(800,'FIELD',hr_general.decode_lookup('ES_FORM_LABELS','IDENTIFIER_TYPE'));
81 hr_utility.raise_error;
82 ELSIF FND_PROFILE.VALUE('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' THEN ---- Bug No 4718049
83 l_identifier_value := hr_es_utility.validate_identifier(p_per_information2,p_per_information3);
84 END IF;
85 CLOSE get_lookup_type;
86 END IF;
87
88 IF FND_PROFILE.VALUE('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' AND ---- Bug No 4718049
89 p_per_information2 IS NOT NULL and p_per_information3 IS NOT NULL and p_national_identifier is not null THEN
90
91 OPEN get_lookup_type(p_per_information2);
92 FETCH get_lookup_type into l_identifier_type;
93
94 IF l_identifier_type='DNI' then
95 If substr(p_national_identifier,1,8) <> p_per_information3 then
96 hr_utility.set_message(800,'HR_ES_INVALID_DNI_NIF');
97 hr_utility.raise_error;
98 end if;
99 end if;
100
101 CLOSE get_lookup_type;
102 end if;
103
104 IF length(p_last_name)>40 THEN
105 hr_utility.set_message(800, 'HR_289712_UTF8_LENGTH_EXCEEDED');
106 hr_utility.set_message_token('COLUMN_NAME',hr_general.decode_lookup('ES_FORM_LABELS','LAST_NAME_1'));
107 hr_utility.set_message_token('COLUMN_VALUE',p_last_name);
108 hr_utility.set_message_token('MAX_LENGTH','40');
109 hr_utility.raise_error;
110 END IF;
111
112 IF length(p_first_name)>40 THEN
113 hr_utility.set_message(800, 'HR_289712_UTF8_LENGTH_EXCEEDED');
114 hr_utility.set_message_token('COLUMN_NAME',hr_general.decode_lookup('ES_FORM_LABELS','NAME'));
115 hr_utility.set_message_token('COLUMN_VALUE',p_first_name);
116 hr_utility.set_message_token('MAX_LENGTH','40');
117 hr_utility.raise_error;
118 END IF;
119
120 IF length(p_per_information1)>40 THEN
121 hr_utility.set_message(800, 'HR_289712_UTF8_LENGTH_EXCEEDED');
122 hr_utility.set_message_token('COLUMN_NAME',hr_general.decode_lookup('ES_FORM_LABELS','LAST_NAME_2'));
123 hr_utility.set_message_token('COLUMN_VALUE',p_per_information1);
124 hr_utility.set_message_token('MAX_LENGTH','40');
125 hr_utility.raise_error;
126 END IF;
127 END IF;
128 END create_es_cwk;
129
130 END PER_ES_CREATE_CWK;