[Home] [Help]
PACKAGE BODY: APPS.PER_ZA_ASS_SHD
Source
1 Package Body per_za_ass_shd as
2 /* $Header: pezaassh.pkb 115.0 2001/02/04 22:30:58 pkm ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_za_ass_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 l_proc varchar2(72) := g_package||'return_api_dml_status';
16 --
17 Begin
18 hr_utility.set_location('Entering:'||l_proc, 5);
19 --
20 Return (nvl(g_api_dml, false));
21 --
22 hr_utility.set_location(' Leaving:'||l_proc, 10);
23 End return_api_dml_status;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |---------------------------< constraint_error >---------------------------|
27 -- ----------------------------------------------------------------------------
28 Procedure constraint_error
29 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
30 --
31 l_proc varchar2(72) := g_package||'constraint_error';
32 --
33 Begin
34 hr_utility.set_location('Entering:'||l_proc, 5);
35 --
36 If (p_constraint_name = 'PER_QUALIFICATIONS_FK1') Then
37 hr_utility.set_message(801, 'HR_51850_ass_ATT_ID_FK');
38 hr_utility.raise_error;
39 ElsIf (p_constraint_name = 'PER_QUALIFICATIONS_FK2') Then
40 hr_utility.set_message(801, 'HR_51851_ass_QUAL_TYPE_ID_FK');
41 hr_utility.raise_error;
42 ElsIf (p_constraint_name = 'PER_QUALIFICATIONS_FK3') Then
43 hr_utility.set_message(801, 'HR_51852_ass_BUS_GRP_ID_FK');
44 hr_utility.raise_error;
45 ElsIf (p_constraint_name = 'PER_QUALIFICATIONS_UK') Then
46 hr_utility.set_message(801,'HR_51847_ass_REC_EXISTS');
47 hr_utility.raise_error;
48 ElsIf (p_constraint_name = 'PER_ass_CHK_DATES') Then
49 hr_utility.set_message(801, 'HR_51853_ass_DATE_INV');
50 hr_utility.raise_error;
51 ElsIf (p_constraint_name = 'PER_QUALIFICATIONS_PK') Then
52 hr_utility.set_message(801, 'HR_51854_ass_QUAL_ID_PK');
53 hr_utility.raise_error;
54 Else
55 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
56 hr_utility.set_message_token('PROCEDURE', l_proc);
57 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
58 hr_utility.raise_error;
59 End If;
60 --
61 hr_utility.set_location(' Leaving:'||l_proc, 10);
62 End constraint_error;
63 -- ----------------------------------------------------------------------------
64 -- |---------------------------------< lck >----------------------------------|
65 -- ----------------------------------------------------------------------------
66 Procedure lck
67 (
68 p_assessment_id in number,
69 p_person_id in number
70 ) is
71 --
72 -- Cursor selects the 'current' row from the HR Schema
73 --
74 Cursor C_Sel1 is
75 select
76 assessment_id,
77 person_id,
78 level_id,
79 name_of_assessor,
80 institution,
81 credit,
82 date_of_assessment,
83 final_result,
84 assessment_number,
85 location_where_assessed,
86 field_of_learning,
87 sub_field,
88 assessment_start_date,
89 assessment_end_date,
90 competence_acquired,
91 ETQA_name,
92 certification_date,
93 certificate_number,
94 accredited_by,
95 date_of_accreditation,
96 certification_expiry_date
97 from
98 per_za_assessments
99 where
100 assessment_id = p_assessment_id
101 for update nowait;
102
103 l_proc varchar2(72) := g_package||'lck';
104 --
105 Begin
106 hr_utility.set_location('Entering:'||l_proc, 5);
107 --
108 -- Add any mandatory argument checking here:
109 -- Example:
110 -- hr_api.mandatory_arg_error
111 -- (p_api_name => l_proc,
112 -- p_argument => 'object_version_number',
113 -- p_argument_value => p_object_version_number);
114 --
115 hr_api.mandatory_arg_error
116 (p_api_name => l_proc,
117 p_argument => 'assessment_id',
118 p_argument_value => p_assessment_id);
119 Open C_Sel1;
120 Fetch C_Sel1 Into g_old_rec;
121 If C_Sel1%notfound then
122 Close C_Sel1;
123 --
124 -- The primary key is invalid therefore we must error
125 --
126 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
127 hr_utility.raise_error;
128 End If;
129 Close C_Sel1;
130 --
131 hr_utility.set_location(' Leaving:'||l_proc, 10);
132 --
133 -- We need to trap the ORA LOCK exception
134 --
135 Exception
136 When HR_Api.Object_Locked then
137 --
138 -- The object is locked therefore we need to supply a meaningful
139 -- error message.
140 --
141 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
142 hr_utility.set_message_token('TABLE_NAME', 'per_za_assessments');
143 hr_utility.raise_error;
144 End lck;
145 --
146 -- ----------------------------------------------------------------------------
147 -- |-----------------------------< convert_args >-----------------------------|
148 -- ----------------------------------------------------------------------------
149 Function convert_args(
150 p_assessment_id in number,
151 p_person_id in number,
152 p_level_id in number,
153 p_name_of_assessor in varchar2,
154 p_institution in varchar2,
155 p_credit in number,
156 p_date_of_assessment in date,
157 p_final_result in varchar2,
158 p_assessment_number in varchar2,
159 p_location_where_assessed in varchar2,
160 p_field_of_learning in varchar2,
161 p_sub_field in varchar2,
162 p_assessment_start_date in date,
163 p_assessment_end_date in date,
164 p_competence_acquired in varchar2,
165 p_ETQA_name in varchar2,
166 p_certification_date in date,
167 p_certificate_number in varchar2,
168 p_accredited_by in varchar2,
169 p_date_of_accreditation in date,
170 p_certification_expiry_date in date)
171 Return g_za_rec_type is
172 --
173 l_rec g_za_rec_type;
174 l_proc varchar2(72) := g_package||'convert_args';
175 --
176 Begin
177 --
178 hr_utility.set_location('Entering:'||l_proc, 5);
179 --
180 -- Convert arguments into local l_rec structure.
181 --
182 l_rec.assessment_id := p_assessment_id;
183 l_rec.person_id := p_person_id;
184 l_rec.level_id := p_level_id;
185 l_rec.name_of_assessor := p_name_of_assessor;
186 l_rec.institution := p_institution;
187 l_rec.credit := p_credit;
188 l_rec.date_of_assessment := p_date_of_assessment;
189 l_rec.final_result := p_final_result;
190 l_rec.assessment_number := p_assessment_number;
191 l_rec.location_where_assessed := p_location_where_assessed;
192 l_rec.field_of_learning := p_field_of_learning;
193 l_rec.sub_field := p_sub_field;
194 l_rec.assessment_start_date := p_assessment_start_date;
195 l_rec.assessment_end_date := p_assessment_end_date;
196 l_rec.competence_acquired := p_competence_acquired;
197 l_rec.ETQA_name := p_ETQA_name;
198 l_rec.certification_date := p_certification_date;
199 l_rec.certificate_number := p_certificate_number;
200 l_rec.accredited_by := p_accredited_by;
201 l_rec.date_of_accreditation := p_date_of_accreditation;
202 l_rec.certification_expiry_date := p_certification_expiry_date;
203
204 --
205 -- Return the plsql record structure.
206 --
207 hr_utility.set_location(' Leaving:'||l_proc, 10);
208 Return(l_rec);
209 --
210 End convert_args;
211 --
212 procedure validate_record(p_date_from in date default null,
213 p_date_to in date default null,
214 p_date_of_assessment in date default null,
215 p_certification_date in date default null,
216 p_certification_expiry_date in date default null,
217 p_date_of_accreditation in date default null) IS
218
219 l_proc varchar2(72) := g_package||'validate_record';
220 l_message varchar2(50);
221
222 begin
223 hr_utility.set_location(' Entering:'||l_proc, 10);
224
225 -- check that the date from is <= date to
226 if p_date_from is not null and p_date_to is not null and
227 p_date_from > p_date_to
228 then
229 hr_utility.set_location(' Entering:'||l_proc, 20);
230 l_message := fnd_message.get_string('PAY','PER_74516_DATE_FROM_DATE_TO');
231 fnd_message.set_encoded(l_message);
232 -- l_message := substr(l_message, 1, 30);
233 hr_utility.set_message(801, 'PER_74516_DATE_FROM_DATE_TO');
234 -- hr_utility.set_message(801,l_message);
235 hr_utility.raise_error;
236 end if;
237
238 -- check that the date from <= date of assessment
239 if p_date_from is not null and p_date_of_assessment is not null and
240 p_date_from > p_date_of_assessment
241 then
242 hr_utility.set_location(' Entering:'||l_proc, 30);
243 l_message := fnd_message.get_string('PAY','PER_74517_DATE_FROM_DATE_ASS');
244 hr_utility.set_message(801,l_message);
245 hr_utility.raise_error;
246 end if;
247
248 -- check that the date of assessment <= date to
249 if p_date_of_assessment is not null and p_date_to is not null and
250 p_date_of_assessment > p_date_to
251 then
252 hr_utility.set_location(' Entering:'||l_proc, 40);
253 l_message := fnd_message.get_string('PAY','PER_74518_DATE_ASS_DATE_TO');
254 hr_utility.set_message(801,l_message);
255 hr_utility.raise_error;
256 end if;
257
258 -- check that the certification date >= date of assessment
259 if p_certification_date is not null and p_date_of_assessment is not null and
260 p_certification_date < p_date_of_assessment
261 then
262 hr_utility.set_location(' Entering:'||l_proc, 50);
263 l_message := fnd_message.get_string('PAY','PER_74519_DATE_CERT_DATE_ASS');
264 hr_utility.set_message(801,l_message);
265 hr_utility.raise_error;
266 end if;
267
268 -- check that the date of accreditation <= certification date
269 if p_date_of_accreditation is not null and p_certification_date is not null and
270 p_date_of_accreditation < p_certification_date
271 then
272 hr_utility.set_location(' Entering:'||l_proc, 60);
273 l_message := fnd_message.get_string('PAY','PER_74520_DATE_ACCR_DATE_CERT');
274 hr_utility.set_message(801,l_message);
275 hr_utility.raise_error;
276 end if;
277
278 -- check that the certification date < certification date
279 if p_certification_expiry_date is not null and p_certification_date is not null and
280 p_certification_expiry_date >= p_certification_date
281 then
282 hr_utility.set_location(' Entering:'||l_proc, 70);
283 l_message := fnd_message.get_string('PAY','PER_74521_EXP_DATE_CERT_DATE');
284 hr_utility.set_message(801,l_message);
285 hr_utility.raise_error;
286 end if;
287
288 hr_utility.set_location(' Leaving:'||l_proc, 80);
289 end validate_record;
290
291 end per_za_ass_shd;