1 PACKAGE BODY PQH_RBC_VALIDATE AS
2 /* $Header: pqrbcval.pkb 120.5 2006/03/23 13:28 srajakum noship $ */
3
4 function matrix_has_criteria(p_copy_entity_txn_id in number) return varchar2 is
5 l_copy_entity_result_id number;
6
7 cursor c1 is select copy_entity_result_id from ben_copy_entity_results
8 where copy_entity_txn_id = p_copy_entity_txn_id
9 and table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE';
10 BEGIN
11 open c1;
12 fetch c1 into l_copy_entity_result_id;
13 if c1%notfound then
14 RETURN 'NO';
15 else
16 RETURN 'YES';
17 end if;
18 close c1;
19 END matrix_has_criteria;
20
21
22 function matrix_has_rates(p_copy_entity_txn_id in number) return varchar2 is
23 l_copy_entity_result_id number;
24
25 cursor c1 is select copy_entity_result_id from ben_copy_entity_results
26 where copy_entity_txn_id = p_copy_entity_txn_id
27 and table_alias = 'RMR' and dml_operation <> 'DELETE'
28 and Information297 is not null
29 and Information297 <> 0;
30 BEGIN
31 open c1;
32 fetch c1 into l_copy_entity_result_id;
33 if c1%notfound then
34 RETURN 'NO';
35 else
36 RETURN 'YES';
37 end if;
38 close c1;
39 END matrix_has_rates;
40
41 function matrix_has_criteria_values(p_copy_entity_txn_id in number) return varchar2 is
42 l_copy_entity_result_id number;
43
44 cursor c1 is select copy_entity_result_id from ben_copy_entity_results
45 where copy_entity_txn_id = p_copy_entity_txn_id
46 and table_alias = 'RMV' and dml_operation <> 'DELETE';
47 BEGIN
48 open c1;
49 fetch c1 into l_copy_entity_result_id;
50 if c1%notfound then
51 RETURN 'NO';
52 else
53 RETURN 'YES';
54 end if;
55 close c1;
56 END matrix_has_criteria_values;
57
58
59 function matrix_has_criteria_nodes(p_copy_entity_txn_id in number) return varchar2 is
60 l_status varchar2(30);
61 l_short_code varchar(30);
62 l_name varchar(240);
63 l_level_number number;
64 l_copy_entity_result_id number;
65
66 cursor c1 is select INFORMATION13,INFORMATION160 from ben_copy_entity_results
67 where copy_entity_txn_id = p_copy_entity_txn_id
68 and table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE';
69
70 cursor c2(l_sh_code varchar,l_level_num number)
71 is select copy_entity_result_id from ben_copy_entity_results
72 where copy_entity_txn_id = p_copy_entity_txn_id
73 and table_alias = 'RMN' and dml_operation <> 'DELETE'
74 and INFORMATION13 = l_sh_code and INFORMATION160 = l_level_num;
75
76 cursor c3(l_sh_code varchar)
77 is select name from ben_eligy_criteria
78 where short_code = l_sh_code;
79
80 BEGIN
81 l_status := 'YES';
82 open c1;
83 loop
84 fetch c1 into l_short_code,l_level_number;
85 exit when c1%notfound;
86
87 open c3(l_short_code);
88 fetch c3 into l_name;
89 if c3%notfound then
90 l_status := 'NO';
91 hr_utility.set_message(8302,'PQH_RBC_CRIT_DOESNT_EXIST');
92 hr_utility.set_message_token('SHORT_CODE',l_short_code);
93 hr_multi_message.add;
94 end if;
95 close c3;
96
97 open c2(l_short_code,l_level_number);
98 fetch c2 into l_copy_entity_result_id;
99 if c2%notfound then
100 l_status := 'NO';
101 hr_utility.set_message(8302,'PQH_RBC_NODES_ADD_ONE');
102 hr_utility.set_message_token('CRIT_NAME',l_name);
103 hr_multi_message.add;
104 end if;
105 close c2;
106 end loop;
107 close c1;
108
109 return l_status;
110 END matrix_has_criteria_nodes;
111
112
113
114 function matrix_has_rate_type(p_copy_entity_txn_id in number) return varchar2 is
115 l_crit_rate_defn_id number;
116
117 cursor c1 is Select information1 from ben_copy_entity_results
118 Where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RCR';
119
120 BEGIN
121 open c1;
122 fetch c1 into l_crit_rate_defn_id;
123 if c1%notfound then
124 RETURN 'NO';
125 else
126 RETURN 'YES';
127 end if;
128 close c1;
129 END matrix_has_rate_type;
130
131 function matrix_has_criteria_dup(p_copy_entity_txn_id in number) return varchar2 is
132 l_crit_rate_defn_id number;
133 l_return_val varchar2(3);
134 cursor c1 is select count(information13) from ben_copy_entity_results
135 where table_alias = 'RBC_CRIT' and dml_operation <> 'DELETE' and copy_entity_txn_id = p_copy_entity_txn_id
136 group by information13;
137
138 BEGIN
139 l_return_val := 'NO';
140 open c1;
141 loop
142 fetch c1 into l_crit_rate_defn_id;
143 exit when c1%notfound;
144 if(l_crit_rate_defn_id > 1) then
145 l_return_val := 'YES';
146 end if;
147 end loop;
148 close c1;
149 RETURN l_return_val;
150 END matrix_has_criteria_dup;
151
152 function plan_name_exists(l_pl_id in number,p_name in varchar2, p_business_group_id in number) return varchar2 is
153 l_name varchar2(240);
154 cursor c1 is select name from ben_pl_f
155 where name = p_name
156 and pl_id <> l_pl_id
157 and business_group_id = p_business_group_id;
158
159 BEGIN
160 open c1;
161 fetch c1 into l_name;
162 if c1%notfound then
163 RETURN 'NO';
164 else
165 --dbms_output.put_line('name already exists'||l_name);
166 hr_utility.set_message(8302,'PQH_RBC_MATRIX_NAME_EXISTS');
167 hr_utility.set_message_token('MATRIX_NAME',l_name);
168 hr_multi_message.add;
169 RETURN 'YES';
170 end if;
171 close c1;
172 END plan_name_exists;
173
174 --
175
176 function plan_short_code_exists(l_pl_id in number,p_short_code in varchar2, p_business_group_id in number) return varchar2 is
177 l_short_code varchar2(240);
178 cursor c1 is select short_code from ben_pl_f
179 where short_code = p_short_code
180 and pl_id <> l_pl_id
181 and business_group_id = p_business_group_id;
182
183 BEGIN
184 open c1;
185 fetch c1 into l_short_code;
186 if c1%notfound then
187 RETURN 'NO';
188 else
189 --dbms_output.put_line('short code already exists'||p_short_code);
190 hr_utility.set_message(8302,'PQH_RBC_PLN_SHORT_CODE_EXISTS');
191 hr_utility.set_message_token('SHORT_CODE',l_short_code);
192 hr_multi_message.add;
193 RETURN 'YES';
194 end if;
195 close c1;
196 END plan_short_code_exists;
197 --
198 function plan_short_name_exists(l_pl_id in number,p_short_name in varchar2, p_business_group_id in number) return varchar2 is
199 l_short_name varchar2(240);
200 cursor c1 is select short_name from ben_pl_f
201 where short_name = p_short_name
202 and pl_id <> l_pl_id
203 and business_group_id = p_business_group_id;
204
205 BEGIN
206 open c1;
207 fetch c1 into l_short_name;
208 if c1%notfound then
209 RETURN 'NO';
210 else
211 --dbms_output.put_line('short name already exists'||p_short_name);
212 hr_utility.set_message(8302,'PQH_RBC_PLN_SHORT_NAME_EXISTS');
213 hr_utility.set_message_token('SHORT_NAME',l_short_name);
214 hr_multi_message.add;
215 RETURN 'YES';
216 end if;
217 close c1;
218 END plan_short_name_exists;
219
220
221 --
222 function check_plan_duplicate(p_copy_entity_txn_id in number) return varchar2 is
223 l_plan_name varchar2(240);
224 l_short_code varchar2(30);
225 l_short_name varchar2(30);
226 l_pl_id number;
227 l_business_group_id number;
228 l_status varchar2(10);
229 cursor c1 is select information1,information170, information93, information94, information4
230 from ben_copy_entity_results
231 where copy_entity_txn_id = p_copy_entity_txn_id
232 and table_alias = 'PLN';
233 begin
234 l_status := 'NO';
235 open c1;
236 fetch c1 into l_pl_id,l_plan_name, l_short_code, l_short_name, l_business_group_id;
237
238 if(c1%notfound) then
239 hr_utility.set_message(8302,'PQH_RBC_INVALID_ENTITY_TXN');
240 hr_multi_message.add;
241 l_status := 'YES';
242 else
243 if(plan_name_exists(l_pl_id,l_plan_name,l_business_group_id) = 'YES') then
244 l_status := 'YES';
245 end if;
246 if(plan_short_code_exists(l_pl_id,l_short_code,l_business_group_id) = 'YES') then
247 l_status := 'YES';
248 end if;
249 if(plan_short_name_exists(l_pl_id,l_short_name,l_business_group_id) = 'YES') then
250 l_status := 'YES';
251 end if;
252 end if;
253 close c1;
254
255 return l_status;
256 end check_plan_duplicate;
257 ---
258
259 function check_critval_dup_in_rmn(p_copy_entity_result_id_node number,p_copy_entity_result_id_val number) return varchar2 is
260
261 l_copy_entity_result_id number;
262 l_status varchar(10);
263 --get all children of node except the current input val row we have to compare
264 cursor c1 is select copy_entity_result_id
265 from ben_copy_entity_results
266 where gs_parent_entity_result_id = p_copy_entity_result_id_node
267 and table_alias = 'RMV' and dml_operation <> 'DELETE'
268 and copy_entity_result_id <> p_copy_entity_result_id_val;
269
270
271 begin
272 --dbms_output.put_line('Checking check_critval_dup_in_rmn: RMN Node id:'||p_copy_entity_result_id_node||' RMV value to check in RMN:'|| p_copy_entity_result_id_val );
273 l_status := 'NO';
274 open c1; --get all RMV's and iterate 1 by 1
275 loop
276 fetch c1 into l_copy_entity_result_id;
277 exit when c1%notfound;
278 --dbms_output.put_line('Value row got in node to compare:'||l_copy_entity_result_id);
279 --check if both are duplicate rows
280 if(check_critval_row(l_copy_entity_result_id,p_copy_entity_result_id_val) = 'YES')then
281 --dbms_output.put_line('matrix has duplicate criteria values ITER:'||l_copy_entity_result_id || 'AND BASE:' || p_copy_entity_result_id_val );
282 l_status := 'YES';
283 -- add exception (name value already exists)
284 end if;
285
286 end loop;
287 close c1;
288 return l_status;
289 end check_critval_dup_in_rmn;
290
291 ---
292
293 function check_critval_dup_in_txn(p_copy_entity_txn_id number) return varchar2 is
294 l_status varchar2(10);
295 l_copy_entity_result_id1 number;
296 l_node_id1 number;
297
298 l_copy_entity_result_id2 number;
299 l_copy_entity_result_id3 number;
300
301 --get all values in the transaction
302 cursor c1 is select copy_entity_result_id,gs_parent_entity_result_id
303 from ben_copy_entity_results
304 where copy_entity_txn_id = p_copy_entity_txn_id
305 and table_alias = 'RMV' and dml_operation <> 'DELETE';
306
307 --get parent node id of rmn itself
308 cursor c2(node_id number) is select gs_parent_entity_result_id
309 from ben_copy_entity_results
310 where copy_entity_result_id = node_id
311 and table_alias = 'RMN' and dml_operation <> 'DELETE';
312
313 --get all children nodes of a parent node
314 cursor c3(parent_node_id number,exclude_node_id number) is select copy_entity_result_id
315 from ben_copy_entity_results
316 where gs_parent_entity_result_id = parent_node_id
317 and table_alias = 'RMN' and dml_operation <> 'DELETE'
318 and copy_entity_result_id <> exclude_node_id;
319
320 begin
321 l_status := 'NO';
322
323 open c1; --get all RMV's in staging and iterate 1 by 1
324 loop
325 fetch c1 into l_copy_entity_result_id1, l_node_id1;
326 exit when c1%notfound;
327
328 --First level: check values with in direct parent
329 --dbms_output.put_line('First level is being done at same level for node:'||l_node_id1||' And value:'|| l_copy_entity_result_id1);
330 if(check_critval_dup_in_rmn(l_node_id1,l_copy_entity_result_id1) = 'YES') then
331 --dbms_output.put_line('duplicate values found at same level Warning !!!!!!!!!!');
332 l_status := 'YES';
333 end if;
334
335 --dbms_output.put_line('Second level is being done:');
336 --Second level: get parent node id of node id and check with its values;
337 open c2(l_node_id1); --get parent node of the rmn node
338 fetch c2 into l_copy_entity_result_id2;
339
340 if(l_copy_entity_result_id2 is not null) then
341 --dbms_output.put_line('Got parent node:'||l_copy_entity_result_id2 ||' For node:'||l_node_id1);
342 open c3(l_copy_entity_result_id2,l_node_id1); --get all RMV's in staging and iterate 1 by 1 exclude node itself
343 loop
344 fetch c3 into l_copy_entity_result_id3;
345 exit when c3%notfound;
346 --dbms_output.put_line('Comparing children of Main parent row got (After excluding self node_id):'|| l_copy_entity_result_id3 );
347 if(check_critval_dup_in_rmn(l_copy_entity_result_id3,l_copy_entity_result_id1) = 'YES') then
348 l_status := 'YES';
349 --dbms_output.put_line('Found duplicate in this comparision Warning !!!!!!!!!!!!');
350 end if;
351 end loop;
352 close c3;
353
354 end if;
355
356 close c2;
357 end loop;
358 close c1;
359
360 return l_status;
361
362 end check_critval_dup_in_txn;
363
364
365
366
367 function check_critval_row (p_copy_entity_result_id_row1 number
368 ,p_copy_entity_result_id_row2 number
369 ) return varchar2 is
370
371 p_row1_char1 varchar2(255);
372 p_row1_char2 varchar2(255);
373 p_row1_char3 varchar2(255);
374 p_row1_char4 varchar2(255);
375 p_row1_num1 number;
376 p_row1_num2 number;
377 p_row1_num3 number;
378 p_row1_num4 number;
379 p_row1_date1 date;
380 p_row1_date2 date;
381 p_row1_date3 date;
382 p_row1_date4 date;
383 p_row2_char1 varchar2(255);
384 p_row2_char2 varchar2(255);
385 p_row2_char3 varchar2(255);
386 p_row2_char4 varchar2(255);
387 p_row2_num1 number;
388 p_row2_num2 number;
389 p_row2_num3 number;
390 p_row2_num4 number;
391 p_row2_date1 date;
392 p_row2_date2 date;
393 p_row2_date3 date;
394 p_row2_date4 date;
395
396 p_row1_org_hier_id number;
397 p_row1_starting_org_id number;
398
399 p_row1_pos_hier_id number;
400 p_row1_starting_pos_id number;
401
402 p_row2_org_hier_id number;
403 p_row2_starting_org_id number;
404
405 p_row2_pos_hier_id number;
406 p_row2_starting_pos_id number;
407
408 cursor c1(p_copy_entity_result_id number) is select
409 information13,information14,information15,information16,
410 information169,information174,information221,information222,
411 information166,information167,information306,information307,
412 information223,information224,information225,information226
413 from ben_copy_entity_results
414 where copy_entity_result_id = p_copy_entity_result_id
415 and table_alias = 'RMV' and dml_operation <> 'DELETE';
416
417 begin
418 --same row not compare
419 if(p_copy_entity_result_id_row1 = p_copy_entity_result_id_row2) then
420
421 return 'NO';
422
423 else
424
425 --dbms_output.put_line('compare rows row1:'||p_copy_entity_result_id_row1||' row2:'|| p_copy_entity_result_id_row2);
426 open c1(p_copy_entity_result_id_row1);
427 fetch c1 into p_row1_char1,p_row1_char2,p_row1_char3,p_row1_char4,
428 p_row1_num1,p_row1_num2,p_row1_num3,p_row1_num4,
429 p_row1_date1,p_row1_date2,p_row1_date3,p_row1_date4,
430 p_row1_org_hier_id,p_row1_starting_org_id,p_row1_pos_hier_id,p_row1_starting_pos_id;
431
432
433 close c1;
434
435 /*
436 dbms_output.put_line('ROW1:'||p_row1_char1||p_row1_char2||p_row1_char3||p_row1_char4||
437 p_row1_num1||p_row1_num2||p_row1_num3||p_row1_num4||
438 p_row1_date1||p_row1_date2||p_row1_date3||p_row1_date4||
439 p_row1_org_hier_id||p_row1_starting_org_id||p_row1_pos_hier_id||p_row1_starting_pos_id);
440
441 */
442 open c1(p_copy_entity_result_id_row2);
443 fetch c1 into p_row2_char1,p_row1_char2,p_row2_char3,p_row2_char4,
444 p_row2_num1,p_row2_num2,p_row2_num3,p_row2_num4,
445 p_row2_date1,p_row2_date2,p_row2_date3,p_row2_date4,
446 p_row2_org_hier_id,p_row2_starting_org_id,p_row2_pos_hier_id,p_row2_starting_pos_id;
447
448 close c1;
449 /*
450 dbms_output.put_line('ROW2:'||p_row2_char1||p_row2_char2||p_row2_char3||p_row2_char4||
451 p_row2_num1||p_row2_num2||p_row2_num3||p_row2_num4||
452 p_row2_date1||p_row2_date2||p_row2_date3||p_row2_date4||
453 p_row2_org_hier_id||p_row2_starting_org_id||p_row2_pos_hier_id||p_row2_starting_pos_id);
454 */
455
456 if ( (p_row1_char1 is NULL or p_row1_char1 = p_row2_char1)
457 and (p_row1_char2 is NULL or p_row1_char2 = p_row2_char2)
458 and (p_row1_char3 is NULL or p_row1_char3 = p_row2_char3)
459 and (p_row1_char4 is NULL or p_row1_char4 = p_row2_char4)
460
461 and (p_row1_num1 is NULL or p_row1_num1 = p_row2_num1)
462 and (p_row1_num2 is NULL or p_row1_num2 = p_row2_num2)
463 and (p_row1_num3 is NULL or p_row1_num3 = p_row2_num3)
464 and (p_row1_num4 is NULL or p_row1_num4 = p_row2_num4)
465
466 and (p_row1_date1 is NULL or p_row1_date1 = p_row2_date1)
467 and (p_row1_date2 is NULL or p_row1_date2 = p_row2_date2)
468 and (p_row1_date3 is NULL or p_row1_date3 = p_row2_date3)
469 and (p_row1_date4 is NULL or p_row1_date4 = p_row2_date4)
470
471 and (p_row1_org_hier_id is NULL or p_row1_org_hier_id = p_row2_org_hier_id)
472 and (p_row1_starting_org_id is NULL or p_row1_starting_org_id = p_row2_starting_org_id)
473 and (p_row1_pos_hier_id is NULL or p_row1_pos_hier_id = p_row2_pos_hier_id)
474 and (p_row1_starting_pos_id is NULL or p_row1_starting_pos_id = p_row2_starting_pos_id)
475
476
477 ) then
478 --dbms_output.put_line('This two rows are same Warning !!!!!!!!!!!!! ');
479 return 'YES';
480
481 end if;
482
483 end if;
484 --dbms_output.put_line('This two rows are not same -- success -- ');
485
486 return 'NO';
487
488 end check_critval_row;
489
490
491 function matrix_has_ratetype_dup(p_copy_entity_txn_id in number) return varchar2 is
492 l_crit_rate_defn_id number;
493 l_return_val varchar2(3);
494
495 cursor c1 is Select count(information1) from ben_copy_entity_results
496 Where copy_entity_txn_id = p_copy_entity_txn_id and table_alias = 'RCR'
497 group by information1;
498
499 BEGIN
500 l_return_val := 'NO';
501 open c1;
502 loop
503 fetch c1 into l_crit_rate_defn_id;
504 exit when c1%notfound;
505 if(l_crit_rate_defn_id > 1) then
506 l_return_val := 'YES';
507 end if;
508 end loop;
509 close c1;
510 RETURN l_return_val;
511 END matrix_has_ratetype_dup;
512
513
514 procedure validate_matrix(p_copy_entity_txn_id in number,p_status out nocopy varchar2)is
515 l_status varchar2(10);
516 begin
517 l_status := 'YES';
518
519 hr_multi_message.enable_message_list;
520
521 --/*
522 --dbms_output.put_line('matrix_has_rates:');
523 if( matrix_has_rates(p_copy_entity_txn_id) = 'NO') then
524 -- add exception(matrix has no rates added)
525 hr_utility.set_message(8302,'PQH_RBC_RATES_ADD_ONE');
526 hr_multi_message.add;
527 l_status := 'NO';
528 end if;
529 --*/
530
531 --/*
532 --dbms_output.put_line('check_plan_duplicate:');
533 if( check_plan_duplicate(p_copy_entity_txn_id) = 'YES') then
534 --dbms_output.put_line('plan already exist');
535 -- add exception(matrix plan is duplicated)
536 hr_utility.set_message(8302,'PQH_RBC_REENTER_PLAN_INFO');
537 hr_multi_message.add;
538 l_status := 'NO';
539 end if;
540 --*/
541
542 --/*
543 --dbms_output.put_line('matrix_has_criteria:');
544 if( matrix_has_criteria(p_copy_entity_txn_id) = 'NO') then
545 --dbms_output.put_line('matrix has no criteria added and criteria dup not being checked');
546 -- add exception(matrix has no criteria added)
547 hr_utility.set_message(8302,'PQH_RBC_CRIT_ADD_ONE');
548 hr_multi_message.add;
549 l_status := 'NO';
550 else
551 if( matrix_has_criteria_dup(p_copy_entity_txn_id) = 'YES') then
552 --dbms_output.put_line('matrix has duplicate criteria added');
553 -- add exception(matrix has duplicate criteria added)
554 hr_utility.set_message(8302,'PQH_RBC_MATRIX_CRITERIA_DUP');
555 hr_multi_message.add;
556 l_status := 'NO';
557 end if;
558 end if;
559
560 --*/
561
562 --/*
563 --dbms_output.put_line('matrix_has_rate_type:');
564 if( matrix_has_rate_type(p_copy_entity_txn_id) = 'NO') then
565 --dbms_output.put_line('matrix has no rate type added and not checking dup for it');
566 -- add exception(matrix has no rate type added)
567 hr_utility.set_message(8302,'PQH_RBC_RATE_TYPE_ADD_ONE');
568 hr_multi_message.add;
569 l_status := 'NO';
570 else
571 if( matrix_has_ratetype_dup(p_copy_entity_txn_id) = 'YES') then
572 --dbms_output.put_line('matrix has duplicate rate type');
573 -- add exception(matrix has duplicate rate type)
574 hr_utility.set_message(8302,'PQH_RBC_MATRIX_RATE_TYPE_DUP');
575 hr_multi_message.add;
576 l_status := 'NO';
577 end if;
578 end if;
579 --*/
580
581 --/*
582 --dbms_output.put_line('matrix_has_criteria_nodes:');
583 if( matrix_has_criteria_nodes(p_copy_entity_txn_id) = 'NO') then
584 l_status := 'NO';
585 end if;
586 --*/
587
588 --/*
589 --dbms_output.put_line('matrix_has_criteria_values:');
590 if( matrix_has_criteria_values(p_copy_entity_txn_id) = 'NO') then
591 --dbms_output.put_line('matrix has no criteria values and not checking dup for it');
592 -- add exception(matrix has no criteria values)
593 hr_utility.set_message(8302,'PQH_RBC_CRIT_VAL_ADD_ONE');
594 hr_multi_message.add;
595 l_status := 'NO';
596 else
597 -- matrix has criteria values so proceed to check duplicates
598 --dbms_output.put_line('matrix_has_criteria_values and checking duplicates:');
599 if( check_critval_dup_in_txn(p_copy_entity_txn_id) = 'YES') then
600 --dbms_output.put_line('matrix has duplicate criteria values');
601 -- add exception(matrix has duplicate criteria values)
602 hr_utility.set_message(8302,'PQH_RBC_CRIT_VAL_DUP_IN_PAGE');
603 hr_multi_message.add;
604 l_status := 'NO';
605 end if;
606 end if;
607 --*/
608
609 p_status := l_status ;
610
611 end validate_matrix;
612
613
614
615
616 procedure pre_validate_matrix(p_copy_entity_txn_id in number,p_status out nocopy varchar2)is
617 l_status varchar2(10);
618 begin
619 l_status := 'YES';
620 FND_MSG_PUB.initialize;
621 hr_multi_message.enable_message_list;
622
623
624 --/*
625 --dbms_output.put_line('matrix_has_criteria:');
626 if( matrix_has_criteria(p_copy_entity_txn_id) = 'NO') then
627 --dbms_output.put_line('matrix has no criteria added and criteria dup not being checked');
628 -- add exception(matrix has no criteria added)
629 hr_utility.set_message(8302,'PQH_RBC_CRIT_ADD_ONE');
630 hr_multi_message.add;
631 l_status := 'NO';
632 end if;
633
634 --*/
635
636 --/*
637 --dbms_output.put_line('matrix_has_rate_type:');
638 if( matrix_has_rate_type(p_copy_entity_txn_id) = 'NO') then
639 --dbms_output.put_line('matrix has no rate type added and not checking dup for it');
640 -- add exception(matrix has no rate type added)
641 hr_utility.set_message(8302,'PQH_RBC_RATE_TYPE_ADD_ONE');
642 hr_multi_message.add;
643 l_status := 'NO';
644 end if;
645 --*/
646
647 --/*
648 --dbms_output.put_line('matrix_has_criteria_nodes:');
649 if( matrix_has_criteria_nodes(p_copy_entity_txn_id) = 'NO') then
650 l_status := 'NO';
651 end if;
652 --*/
653
654 --/*
655 --dbms_output.put_line('matrix_has_criteria_values:');
656 if( matrix_has_criteria_values(p_copy_entity_txn_id) = 'NO') then
657 --dbms_output.put_line('matrix has no criteria values and not checking dup for it');
658 -- add exception(matrix has no criteria values)
659 hr_utility.set_message(8302,'PQH_RBC_CRIT_VAL_ADD_ONE');
660 hr_multi_message.add;
661 l_status := 'NO';
662 end if;
663
664 --/*
665 --dbms_output.put_line('matrix_has_rates:');
666 if( matrix_has_rates(p_copy_entity_txn_id) = 'NO') then
667 -- add exception(matrix has no rates added)
668 hr_utility.set_message(8302,'PQH_RBC_RATES_ADD_ONE');
669 hr_multi_message.add;
670 l_status := 'NO';
671 end if;
672 --*/
673
674
675 p_status := l_status ;
676
677 end pre_validate_matrix;
678
679 procedure check_warnings(p_copy_entity_txn_id in number,p_status out nocopy varchar2,p_warning_message out nocopy varchar2)is
680 l_status varchar2(10);
681 l_count number;
682 l_criteria_rate_defn number;
683 l_crit_rate_defn_name varchar2(240);
684 l_crit_rate_defn_names varchar2(2400);
685 l_num number;
686 cursor c1 is select Information1
687 from ben_copy_entity_results
688 where
689 table_alias = 'RCR' and dml_operation <> 'DELETE'
690 and copy_entity_txn_id = p_copy_entity_txn_id;
691
692 begin
693 l_status := 'YES';
694 l_crit_rate_defn_names := '';
695 l_num := 0;
696 FND_MSG_PUB.initialize;
697
698 -- for each criteria rate defn added we have one RCR row created
699 -- check for each rcr row created if you have atleast one rmr row created
700 open c1; --get all RCR's and iterate 1 by 1
701 loop
702 fetch c1 into l_criteria_rate_defn;
703 exit when c1%notfound;
704
705 select name into l_crit_rate_defn_name from pqh_criteria_rate_defn_vl where criteria_rate_defn_id = l_criteria_rate_defn;
706 l_count := 0;
707 select count(*) into l_count from ben_copy_entity_results
708 where
709 table_alias = 'RMR' and dml_operation <> 'DELETE'
710 and copy_entity_txn_id = p_copy_entity_txn_id
711 and Information162 = l_criteria_rate_defn;
712
713 if l_count = 0 then
714 l_status := 'NO';
715 if l_num <> 0 then
716 l_crit_rate_defn_names := concat(l_crit_rate_defn_names,', ');
717 l_num := l_num+1;
718 end if;
719 l_crit_rate_defn_names := concat(l_crit_rate_defn_names,l_crit_rate_defn_name);
720 end if;
721
722 end loop;
723 close c1;
724
725 if l_status = 'NO' then
726 hr_utility.set_message(8302,'PQH_RBC_RATE_DEFN_RATES_WARN');
727 hr_utility.set_message_token('NAME',l_crit_rate_defn_names);
728 p_warning_message := hr_utility.get_message;
729 else
730 p_warning_message := '';
731 end if;
732
733 p_status := l_status ;
734
735 end check_warnings;
736
737
738
739 procedure on_validate_matrix(p_copy_entity_txn_id in number,p_status out nocopy varchar2)is
740 l_status varchar2(10);
741 begin
742 l_status := 'YES';
743 FND_MSG_PUB.initialize;
744 hr_multi_message.enable_message_list;
745 --/*
746 --dbms_output.put_line('check_plan_duplicate:');
747 if( check_plan_duplicate(p_copy_entity_txn_id) = 'YES') then
748 --dbms_output.put_line('plan already exist');
749 -- add exception(matrix plan is duplicated)
750 hr_utility.set_message(8302,'PQH_RBC_REENTER_PLAN_INFO');
751 hr_multi_message.add;
752 l_status := 'NO';
753 end if;
754 --*/
755
756 --/*
757 -- --dbms_output.put_line('matrix_has_criteria:');
758 -- if( matrix_has_criteria(p_copy_entity_txn_id) = 'NO') then
759 -- --dbms_output.put_line('matrix has no criteria added and criteria dup not being checked');
760 -- -- add exception(matrix has no criteria added)
761 -- hr_utility.set_message(8302,'PQH_RBC_CRIT_ADD_ONE');
762 -- hr_multi_message.add;
763 -- l_status := 'NO';
764 -- else
765 if( matrix_has_criteria_dup(p_copy_entity_txn_id) = 'YES') then
766 --dbms_output.put_line('matrix has duplicate criteria added');
767 -- add exception(matrix has duplicate criteria added)
768 hr_utility.set_message(8302,'PQH_RBC_MATRIX_CRITERIA_DUP');
769 hr_multi_message.add;
770 l_status := 'NO';
771 end if;
772 -- end if;
773
774 --*/
775
776 --/*
777 -- --dbms_output.put_line('matrix_has_rate_type:');
778 -- if( matrix_has_rate_type(p_copy_entity_txn_id) = 'NO') then
779 -- --dbms_output.put_line('matrix has no rate type added and not checking dup for it');
780 -- -- add exception(matrix has no rate type added)
781 -- hr_utility.set_message(8302,'PQH_RBC_RATE_TYPE_ADD_ONE');
782 -- hr_multi_message.add;
783 -- l_status := 'NO';
784 -- else
785 if( matrix_has_ratetype_dup(p_copy_entity_txn_id) = 'YES') then
786 --dbms_output.put_line('matrix has duplicate rate type');
787 -- add exception(matrix has duplicate rate type)
788 hr_utility.set_message(8302,'PQH_RBC_MATRIX_RATE_TYPE_DUP');
789 hr_multi_message.add;
790 l_status := 'NO';
791 end if;
792 -- end if;
793 --*/
794
795
796 --/* We may not need to check duplicate criteria values because we do in pages
797 -- --dbms_output.put_line('matrix_has_criteria_values:');
798 -- if( matrix_has_criteria_values(p_copy_entity_txn_id) = 'NO') then
799 -- --dbms_output.put_line('matrix has no criteria values and not checking dup for it');
800 -- -- add exception(matrix has no criteria values)
801 -- hr_utility.set_message(8302,'PQH_RBC_CRIT_VAL_ADD_ONE');
802 -- hr_multi_message.add;
803 -- l_status := 'NO';
804 -- else
805 -- We may not need to check duplicate criteria values because we do in pages
806 -- dbms_output.put_line('matrix_has_criteria_values and checking duplicates:');
807 -- if( check_critval_dup_in_txn(p_copy_entity_txn_id) = 'YES') then
808 -- dbms_output.put_line('matrix has duplicate criteria values');
809 -- add exception(matrix has duplicate criteria values)
810 -- hr_utility.set_message(8302,'PQH_RBC_CRIT_VAL_DUP_IN_PAGE');
811 -- hr_multi_message.add;
812 -- l_status := 'NO';
813 -- end if;
814 -- end if;
815 --*/
816
817 p_status := l_status ;
818
819 end on_validate_matrix;
820
821
822
823
824 end PQH_RBC_VALIDATE;
[Home] [Help]