[Home] [Help]
PACKAGE BODY: APPS.PSB_VALIDATE_DATA_EXTRACT_PVT
Source
1 PACKAGE BODY PSB_VALIDATE_DATA_EXTRACT_PVT AS
2 /* $Header: PSBPPVHB.pls 120.3 2005/03/02 17:39:26 viraghun ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_VALIDATE_DATA_EXTRACT_PVT';
5 g_dbug VARCHAR2(2000);
6
7 TYPE TokNameArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
8
9 -- TokValArray contains values for all tokens
10
11 TYPE TokValArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
12
13 -- Number of Message Tokens
14 no_msg_tokens NUMBER := 0;
15
16 -- Message Token Name
17 msg_tok_names TokNameArray;
18
19 -- Message Token Value
20 msg_tok_val TokValArray;
21
22 PROCEDURE message_token
23 ( tokname IN VARCHAR2,
24 tokval IN VARCHAR2
25 );
26
27 PROCEDURE add_message
28 ( appname IN VARCHAR2,
29 msgname IN VARCHAR2
30 );
31
32 /* ----------------------------------------------------------------------- */
33
34 PROCEDURE Data_Extract_Summary
35 ( p_api_version IN NUMBER,
36 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
37 p_commit IN VARCHAR2 := FND_API.G_FALSE,
38 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
39 p_return_status OUT NOCOPY VARCHAR2,
40 p_msg_count OUT NOCOPY NUMBER,
41 p_msg_data OUT NOCOPY VARCHAR2,
42 p_extract_method IN VARCHAR2,
43 p_data_extract_id IN NUMBER
44 )
45 AS
46 l_positions number;
47 l_vacant_positions number;
48 l_employee_cnt number;
49 l_salary_cnt number;
50 l_cost_cnt number;
51 l_attr_cnt number;
52 l_emp_assign_cnt number;
53 l_msg_buf varchar2(1000);
54 l_msg_count number;
55 l_reqid number;
56 l_userid number;
57 l_restart_id number;
58 l_status varchar2(1);
59 l_return_status varchar2(1);
60 l_msg_data varchar2(1000);
61
62 l_api_name CONSTANT VARCHAR2(30) := 'Data_Extract_Summary';
63 l_api_version CONSTANT NUMBER := 1.0;
64
65 BEGIN
66
67 -- Standard Start of API savepoint
68 SAVEPOINT Data_Extract_Summary;
69
70 -- Standard call to check for call compatibility.
71 if not FND_API.Compatible_API_Call (l_api_version,
72 p_api_version,
73 l_api_name,
74 G_PKG_NAME)
75 then
76 raise FND_API.G_EXC_UNEXPECTED_ERROR;
77 end if;
78
79 -- Initialize message list if p_init_msg_list is set to TRUE.
80
81 if FND_API.to_Boolean (p_init_msg_list) then
82 FND_MSG_PUB.initialize;
83 end if;
84
85 -- Initialize API return status to success
86
87 p_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 Select count(*)
90 into l_positions
91 from psb_positions_i
92 where data_extract_id = p_data_extract_id
93 and hr_employee_id is not null;
94
95 add_message('PSB','PSB_DE_SUMMARY_HEADER');
96 message_token('POSITION_COUNT',l_positions );
97 add_message('PSB', 'PSB_ASSIGNED_POSITIONS_COUNT');
98
99 Select count(*)
100 into l_vacant_positions
101 from psb_positions_i pp
102 where pp.data_extract_id = p_data_extract_id
103 and hr_employee_id is null;
104
105 message_token('VACANT_POSITION',l_vacant_positions );
106 add_message('PSB', 'PSB_VACANT_POSITIONS_COUNT');
107
108 Select count(*)
109 into l_employee_cnt
110 from psb_employees_i
111 where data_extract_id = p_data_extract_id;
112
113 message_token('EMPLOYEE_COUNT',l_employee_cnt );
114 add_message('PSB', 'PSB_EMPLOYEES_COUNT');
115
116 Select count(*)
117 into l_salary_cnt
118 from psb_salary_i
119 where data_extract_id = p_data_extract_id;
120
121 message_token('SALARY_COUNT',l_salary_cnt );
122 add_message('PSB', 'PSB_SALARY_COUNT');
123
124 Select count(*)
125 into l_cost_cnt
126 from psb_cost_distributions_i
127 where data_extract_id = p_data_extract_id;
128
129 message_token('COST_COUNT',l_cost_cnt );
130 add_message('PSB', 'PSB_COST_DISTRIBUTION_COUNT');
131
132 Select count(*)
133 into l_attr_cnt
134 from psb_attribute_values_i
135 where data_extract_id = p_data_extract_id;
136
137 message_token('ATTRIBUTE_COUNT',l_attr_cnt );
138 add_message('PSB', 'PSB_ATTRIBUTE_VALUE_COUNT');
139
140 Select count(*)
141 into l_emp_assign_cnt
142 from psb_employee_assignments_i
143 where data_extract_id = p_data_extract_id;
144
145 message_token('EMP_ASSIGN_COUNT',l_emp_assign_cnt );
146 add_message('PSB', 'PSB_EMP_ASSIGN_COUNT');
147
148 /* delete from PSB_ERROR_MESSAGES
149 where process_id = p_data_extract_id;
150
151 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
152 l_userid := FND_GLOBAL.USER_ID;
153
154 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
155 p_data => l_msg_data );
156 IF l_msg_count > 0 THEN
157
158 l_msg_data := FND_MSG_PUB.Get
159 (p_msg_index => FND_MSG_PUB.G_NEXT,
160 p_encoded => FND_API.G_FALSE);
161
162 PSB_MESSAGE_S.INSERT_ERROR
163 (p_source_process => 'DATA_EXTRACT_VALIDATION',
164 p_process_id => p_data_extract_id,
165 p_msg_count => l_msg_count,
166 p_msg_data => l_msg_data);
167
168 end if; */
169
170 PSB_HR_EXTRACT_DATA_PVT.Reentrant_Process
171 ( p_api_version => 1.0 ,
172 p_return_status => l_return_status,
173 p_msg_count => l_msg_count,
174 p_msg_data => l_msg_data,
175 p_data_extract_id => p_data_extract_id,
176 p_extract_method => p_extract_method,
177 p_process => 'Data Extract Summary'
178 );
179
180 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
181 raise FND_API.G_EXC_ERROR;
182 end if;
183
184 -- End of API body.
185
186 -- Standard check of p_commit.
187
188 if FND_API.to_Boolean (p_commit) then
189 commit work;
190 end if;
191
192 -- Standard call to get message count and if count is 1, get message info.
193
194 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
195 p_data => p_msg_data);
196
197 EXCEPTION
198
199 when FND_API.G_EXC_ERROR then
200
201 rollback to Data_Extract_Summary;
202
203 p_return_status := FND_API.G_RET_STS_ERROR;
204
205 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
206 p_data => p_msg_data);
207
208
209 when FND_API.G_EXC_UNEXPECTED_ERROR then
210
211 rollback to Data_Extract_Summary;
212
213 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
214
215 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
216 p_data => p_msg_data);
217
218
219 when OTHERS then
220
221 rollback to Data_Extract_Summary;
222
223 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224
225 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
226
227 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
228 l_api_name);
229 end if;
230
231 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
232 p_data => p_msg_data);
233
234 END Data_Extract_Summary;
235 /*---------------------------------------------------------------------------*/
236
237
238
239 /*===========================================================================+
240 | PROCEDURE Validate_Data_Extract |
241 +===========================================================================*/
242 PROCEDURE Validate_Data_Extract
243 ( p_api_version IN NUMBER,
244 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
245 p_commit IN VARCHAR2 := FND_API.G_FALSE,
246 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
247 p_return_status OUT NOCOPY VARCHAR2,
248 p_msg_count OUT NOCOPY NUMBER,
249 p_msg_data OUT NOCOPY VARCHAR2,
250 p_extract_method IN VARCHAR2,
251 p_data_extract_id IN NUMBER,
252 p_business_group_id IN NUMBER
253 )
254 IS
255 --
256 l_api_name CONSTANT VARCHAR2(30) := 'Validata_Data_Extract';
257 l_api_version CONSTANT NUMBER := 1.0;
258 --
259 l_return_status varchar2(1);
260 l_msg_count number;
261 l_msg_data varchar2(1000);
262 --
263 /* Bug#3256987: This validation is no more required.
264 l_position_id number;
265 l_attribute_name varchar2(30);
266 l_tmp varchar2(1);
267 --
268 Cursor C_Emp_Assign is
269 Select 'x'
270 from Psb_employee_assignments_i
271 where hr_position_id = l_position_id
272 and attribute_name = l_attribute_name
273 and data_extract_id + 0 = p_data_extract_id;
274 */
275 --
276
277 /* start bug no 4170600 */
278 CURSOR l_msg_csr
279 IS
280 SELECT description
281 FROM psb_error_messages
282 WHERE concurrent_request_id = -4712;
283 /* end bug no 4170600 */
284
285 BEGIN
286 -- Standard Start of API savepoint
287 SAVEPOINT Validate_Data_Extract;
288
289 -- Standard call to check for call compatibility.
290 if not FND_API.Compatible_API_Call (l_api_version,
291 p_api_version,
292 l_api_name,
293 G_PKG_NAME)
294 then
295 raise FND_API.G_EXC_UNEXPECTED_ERROR;
296 end if;
297
298 -- Initialize message list if p_init_msg_list is set to TRUE.
299 if FND_API.to_Boolean (p_init_msg_list) then
300 FND_MSG_PUB.initialize;
301 end if;
302
303 -- Initialize API return status to success
304 p_return_status := FND_API.G_RET_STS_SUCCESS;
305
306 add_message('PSB','PSB_DE_VALIDATION_HEADER');
307
308 For C_grade_val_rec in
309 (
310 Select pei.hr_position_id, pp.hr_position_name,
311 pei.hr_employee_id, pei.first_name||' '||pei.last_name name,
312 pei.assignment_id,
313 pei.employee_number,pei.pay_basis,
314 pei.salary_type,pei.rate_or_payscale_id,
315 pei.grade_id,pei.grade_step,
316 pei.sequence_number,pei.element_value,
317 pei.proposed_salary
318 from psb_employees_i pei,
319 psb_positions_i pp
320 where pei.data_extract_id = p_data_extract_id
321 and pp.data_extract_id = p_data_extract_id
322 and pei.hr_position_id = pp.hr_position_id
323 and pei.hr_employee_id = pp.hr_employee_id
324 order by pei.last_name
325 )
326 Loop
327
328 --l_position_id := C_grade_val_rec.hr_position_id;
329 if (C_grade_val_rec.grade_id <> 0 OR C_grade_val_rec.grade_id is not null)
330 then
331 --
332 if ( (C_grade_val_rec.salary_type = 'STEP')
333 and
334 ( C_grade_val_rec.grade_step = 0 or
335 C_grade_val_rec.grade_step is null
336 )
337 )
338 then
339 message_token('POSITION_NAME', C_grade_val_rec.hr_position_name);
340 message_token('EMPLOYEE_NAME',C_grade_val_rec.name);
341 message_token('EMPLOYEE_NUMBER',C_grade_val_rec.employee_number);
342 message_token('GRADE_ID',C_grade_val_rec.grade_id);
343 add_message('PSB', 'PSB_INVALID_GRADE_STEP');
344 end if;
345 --
346 end if;
347
348 /*
349 Bug#3256987: Flag required_for_import_flag corresponds to "Use in Default
350 Rules" option for attributes. This validation is not required.
351 --
352 For C_Attr_Rec in
353 ( Select attribute_id,name
354 from psb_attributes_VL
355 where business_group_id = p_business_group_id
356 and required_for_import_flag = 'Y'
357 )
358 Loop
359 --
360 l_attribute_name := C_Attr_Rec.name;
361 Open C_Emp_Assign;
362 Fetch C_Emp_Assign into l_tmp;
363 Close C_Emp_Assign;
364 --
365 if (l_tmp is null) then
366 message_token('POSITION_NAME', C_grade_val_rec.hr_position_name);
367 message_token('ATTRIBUTE', C_Attr_Rec.name);
368 add_message ('PSB', 'PSB_ATTRIBUTE_MISSING');
369 end if;
370 --
371 End Loop;
372 --
373 */
374
375 End Loop;
376
377 /* start bug no 4170600 */
378 FOR l_msg_rec IN l_msg_csr
379 LOOP
380 message_token('ERROR_MESSAGE', l_msg_rec.description);
381 add_message ('PSB', 'PSB_DATA_EXTRACT_ERR_MSG');
382 END LOOP;
383
384 DELETE FROM PSB_ERROR_MESSAGES
385 WHERE concurrent_request_id = -4712;
386 /* end bug no 4170600 */
387
388
389 PSB_HR_EXTRACT_DATA_PVT.Reentrant_Process
390 ( p_api_version => 1.0 ,
391 p_return_status => l_return_status,
392 p_msg_count => l_msg_count,
393 p_msg_data => l_msg_data,
394 p_data_extract_id => p_data_extract_id,
395 p_extract_method => p_extract_method,
396 p_process => 'Validate Data Extract'
397 ) ;
398 --
399 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
400 raise FND_API.G_EXC_ERROR;
401 end if;
402
403 -- Standard check of p_commit.
404 if FND_API.to_Boolean (p_commit) then
405 commit work;
406 end if;
407
408 -- Standard call to get message count and if count is 1, get message info.
409 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
410 p_data => p_msg_data);
411 EXCEPTION
412 --
413 when FND_API.G_EXC_ERROR then
414 rollback to Validate_Data_Extract;
415 p_return_status := FND_API.G_RET_STS_ERROR;
416 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
417 p_data => p_msg_data);
418 --
419 when FND_API.G_EXC_UNEXPECTED_ERROR then
420 rollback to Validate_Data_Extract;
421 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
423 p_data => p_msg_data);
424 --
425 when OTHERS then
426 rollback to Validate_Data_Extract;
427 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
429 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
430 l_api_name);
431 end if;
432 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
433 p_data => p_msg_data);
434 --
435 END Validate_Data_Extract;
436 /* ----------------------------------------------------------------------- */
437
438
439 -- Add Token and Value to the Message Token array
440 PROCEDURE message_token(tokname IN VARCHAR2,
441 tokval IN VARCHAR2) AS
442
443 BEGIN
444
445 if no_msg_tokens is null then
446 no_msg_tokens := 1;
447 else
448 no_msg_tokens := no_msg_tokens + 1;
449 end if;
450
451 msg_tok_names(no_msg_tokens) := tokname;
452 msg_tok_val(no_msg_tokens) := tokval;
453
454 END message_token;
455
456 /* ----------------------------------------------------------------------- */
457
458 -- Define a Message Token with a Value and set the Message Name
459
460 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
461 -- retrieved by the calling program.
462
463 PROCEDURE add_message(appname IN VARCHAR2,
464 msgname IN VARCHAR2) AS
465
466 i BINARY_INTEGER;
467
468 BEGIN
469
470 if ((appname is not null) and
471 (msgname is not null)) then
472
473 FND_MESSAGE.SET_NAME(appname, msgname);
474
475 if no_msg_tokens is not null then
476 for i in 1..no_msg_tokens loop
477 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
478 end loop;
479 end if;
480
481 FND_MSG_PUB.Add;
482
483 end if;
484
485 -- Clear Message Token stack
486
487 no_msg_tokens := 0;
488
489 END add_message;
490
491 /* ----------------------------------------------------------------------- */
492 FUNCTION get_debug RETURN VARCHAR2 AS
493 BEGIN
494 return(g_dbug);
495 END get_debug;
496 /* ----------------------------------------------------------------------- */
497
498 END PSB_VALIDATE_DATA_EXTRACT_PVT;