[Home] [Help]
PACKAGE BODY: APPS.PER_ZA_WSP_LOOKUP
Source
1 package body PER_ZA_WSP_LOOKUP as
2 /* $Header: perzawsp.pkb 120.3.12010000.2 2008/08/06 09:36:57 ubhat ship $ */
3 G_ATTRIBUTE_CATEGORY Constant varchar2(100) := 'ZA_WSP_SKILLS_PRIORITIES';
4 g_p_lpath_lookup_type Constant varchar2(40) := 'ZA_WSP_LEARNING_PATHS';
5 g_p_course_lookup_type Constant varchar2(40) := 'ZA_WSP_COURSES';
6 g_p_cert_lookup_type Constant varchar2(40) := 'ZA_WSP_CERTIFICATIONS';
7 g_t_lpath_lookup_type Constant varchar2(40) := 'ZA_ATR_LEARNING_PATHS';
8 g_t_course_lookup_type Constant varchar2(40) := 'ZA_ATR_COURSES';
9 g_t_cert_lookup_type Constant varchar2(40) := 'ZA_ATR_CERTIFICATIONS';
10 g_t_comp_lookup_type Constant varchar2(40) := 'ZA_ATR_COMPETENCIES';
11 g_t_qual_lookup_type Constant varchar2(40) := 'ZA_ATR_QUALIFICATIONS';
12 G_WSP_CAT_ATTRIBUTE_CATEGORY Constant varchar2(100) := 'ZA_WSP_OCC_CAT';
13 G_WSP_CAT_LOOKUP_TYPE Constant varchar2(80) := 'ZA_WSP_OCCUPATIONAL_CATEGORIES';
14
15
16 g_plan_year_start_date date;
17 g_plan_year_end_date date;
18 g_trnd_year_start_date date;
19 g_trnd_year_end_date date;
20 g_usr_tab_id number;
21
22 type t_unique_id is table of number index by varchar2(15);
23
24 tab_usr_row_ids t_unique_id;
25
26
27 /****************************************************************************
28 Name : set_wsp_cat_attr_cat
29 Description : called from wsp_lookup_values
30 set the attribute category in lookup_values for lookup_types
31 ZA_WSP_OCCUPATIONAL_CATEGORIES wher it is missing
32 *****************************************************************************/
33 PROCEDURE set_wsp_cat_attr_cat is
34
35 Begin
36 Update fnd_lookup_values
37 Set ATTRIBUTE_CATEGORY = G_WSP_CAT_ATTRIBUTE_CATEGORY
38 Where lookup_type = G_WSP_CAT_LOOKUP_TYPE
39 And ATTRIBUTE_CATEGORY IS null
40 AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
41 AND lookup_code NOT IN
42 ( Select lookup_code
43 FROM hr_lookups
44 Where lookup_type = 'ZA_EMP_EQ_OCCUPATIONAL_CAT'
45 );
46
47 END set_wsp_cat_attr_cat;
48
49
50 /****************************************************************************
51 Name : validate_lookup_meaning
52 Description : called from LOOKUP_VAL_INSERT_ROW
53 LOOKUP_VAL_INSERT_ROW creats row in the fnd_lookup_values
54 If the meaning column is duplicate it prefix
55 year and duplicate no to meaning
56 *****************************************************************************/
57
58 function validate_lookup_meaning
59 (
60 P_LOOKUP_TYPE in varchar2
61 , P_MEANING in varchar2
62 , p_lookup_code in number
63 ) return varchar2
64 Is
65 l_count number;
66 l_meaning varchar2(100);
67 begin
68 hr_utility.set_location('inside validate_lookup_meaning',1);
69 hr_utility.set_location('P_MEANING ' || P_MEANING,1);
70 select count(*)
71 into l_count
72 from
73 fnd_lookup_values
74 Where lookup_type = P_LOOKUP_TYPE
75 and MEANING = P_MEANING
76 and lookup_code <> p_lookup_code
77 AND security_group_id = fnd_global.lookup_security_group(P_LOOKUP_TYPE,3);
78
79 hr_utility.set_location('l_count ' || l_count,1);
80 hr_utility.set_location('fnd_global.lookup_security_group(P_LOOKUP_TYPE,3) ' || fnd_global.lookup_security_group(P_LOOKUP_TYPE,3),1);
81
82 if l_count = 0 then
83 l_MEANING := P_Meaning;
84 else
85 l_count := nvl(to_number(substr(P_MEANING,5,instr(P_MEANING,':')-5)),0) +1;
86 l_meaning := substr(p_meaning,1,4)||l_count||substr(P_MEANING,instr(P_MEANING,':'));
87 l_meaning := substr(l_meaning,1,80);
88 l_MEANING := validate_lookup_meaning
89 (
90 P_LOOKUP_TYPE => P_LOOKUP_TYPE
91 , P_MEANING => l_MEANING
92 , p_lookup_code => p_lookup_code
93 );
94
95 end if;
96 return l_meaning;
97
98 end validate_lookup_meaning;
99
100
101 /****************************************************************************
102 Name : LOOKUP_VAL_INSERT_ROW
103 Description : LOOKUP_VAL_INSERT_ROW creats row in the fnd_lookup_values
104 lookup_code will have the YEAR appneded with id
105 *****************************************************************************/
106
107 procedure LOOKUP_VAL_INSERT_ROW
108 ( P_LOOKUP_TYPE in varchar2
109 , P_LOOKUP_CODE in varchar2
110 , P_ATTRIBUTE1 in varchar2
111 , P_ATTRIBUTE2 in varchar2
112 , P_ATTRIBUTE3 in varchar2
113 , P_ATTRIBUTE4 in varchar2
114 , P_ATTRIBUTE5 in varchar2
115 , P_ATTRIBUTE6 in varchar2
116 , P_ATTRIBUTE7 in varchar2
117 , P_ATTRIBUTE8 in varchar2
118 , P_ATTRIBUTE9 in varchar2
119 , P_ATTRIBUTE10 in varchar2
120 , P_ATTRIBUTE11 in varchar2
121 , P_ATTRIBUTE12 in varchar2
122 , P_ATTRIBUTE13 in varchar2
123 , P_ATTRIBUTE14 in varchar2
124 , P_ATTRIBUTE15 in varchar2
125 , P_ENABLED_FLAG in varchar2
126 , P_MEANING in varchar2
127 , P_DESCRIPTION in varchar2
128 , P_START_DATE_ACTIVE in varchar2
129 , P_END_DATE_ACTIVE in varchar2
130 )
131 is
132 l_row_id varchar2(100);
133 l_count number(3);
134 l_meaning varchar2(100);
135 len_desc number(3);
136 lenb_desc number(3);
137 L_DESCRIPTION varchar2(300);
138 begin
139 Select count(*)
140 INTO l_count
141 From
142 FND_LOOKUP_values
143 where
144 lookup_type = P_LOOKUP_TYPE
145 and lookup_code = P_LOOKUP_CODE
146 AND security_group_id = fnd_global.lookup_security_group(P_LOOKUP_TYPE,3);
147
148 hr_utility.set_location('inside LOOKUP_VAL_INSERT_ROW',1);
149 hr_utility.set_location('P_LOOKUP_TYPE' || P_LOOKUP_TYPE,1);
150 hr_utility.set_location('P_LOOKUP_CODE' || P_LOOKUP_CODE,1);
151
152 hr_utility.set_location('P_START_DATE_ACTIVE' || P_START_DATE_ACTIVE,1);
153 hr_utility.set_location('P_END_DATE_ACTIVE' || P_END_DATE_ACTIVE,1);
154
155
156 if l_count = 0 then
157
158
159 l_meaning := validate_lookup_meaning
160 (
161 P_LOOKUP_TYPE => P_LOOKUP_TYPE
162 , P_MEANING => P_MEANING
163 , P_LOOKUP_CODE => P_LOOKUP_CODE
164 );
165 hr_utility.set_location('l_meaning' || l_meaning,1);
166
167 hr_utility.set_location('calling FND_LOOKUP_VALUES_PKG.INSERT_ROW',1);
168 hr_utility.set_location('fnd_global.lookup_security_group(P_LOOKUP_TYPE,3)'|| fnd_global.lookup_security_group(P_LOOKUP_TYPE,3),1);
169
170 /*Changes for Bug 6898734 */
171 len_desc:=length(P_DESCRIPTION);
172 SELECT vsize(P_DESCRIPTION) INTO lenb_desc FROM dual;
173
174 hr_utility.set_location('len_desription:'|| len_desc,1);
175 hr_utility.set_location('len_bytes_desription:'|| lenb_desc,1);
176
177 L_DESCRIPTION := P_DESCRIPTION;
178 hr_utility.set_location('Before Loop',1);
179
180 WHILE lenb_desc > 240
181 loop
182 len_desc:=length(L_DESCRIPTION);
183 L_DESCRIPTION:=substr(L_DESCRIPTION,1,len_desc-1);
184 SELECT vsize(L_DESCRIPTION) INTO lenb_desc FROM dual;
185 END loop;
186 hr_utility.set_location('After loop',1);
187
188 /* End changes for Bug 6898734 */
189
190 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
191 X_ROWID => l_row_id,
192 X_LOOKUP_TYPE => P_LOOKUP_TYPE,
193 X_SECURITY_GROUP_ID => fnd_global.lookup_security_group(P_LOOKUP_TYPE,3),
194 X_VIEW_APPLICATION_ID => 3,
195 X_LOOKUP_CODE => P_LOOKUP_CODE,
196 X_TAG => null,
197 X_ATTRIBUTE_CATEGORY => G_ATTRIBUTE_CATEGORY,
198 X_ATTRIBUTE1 => P_ATTRIBUTE1,
199 X_ATTRIBUTE2 => P_ATTRIBUTE2,
200 X_ATTRIBUTE3 => P_ATTRIBUTE3,
201 X_ATTRIBUTE4 => P_ATTRIBUTE4,
202 X_ATTRIBUTE5 => P_ATTRIBUTE5,
203 X_ATTRIBUTE6 => P_ATTRIBUTE6,
204 X_ATTRIBUTE7 => P_ATTRIBUTE7,
205 X_ATTRIBUTE8 => P_ATTRIBUTE8,
206 X_ATTRIBUTE9 => P_ATTRIBUTE9,
207 X_ATTRIBUTE10 => P_ATTRIBUTE10,
208 X_ATTRIBUTE11 => P_ATTRIBUTE11,
209 X_ATTRIBUTE12 => P_ATTRIBUTE12,
210 X_ATTRIBUTE13 => P_ATTRIBUTE13,
211 X_ATTRIBUTE14 => P_ATTRIBUTE14,
212 X_ATTRIBUTE15 => P_ATTRIBUTE15,
213 X_ENABLED_FLAG => P_ENABLED_FLAG,
214 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
215 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
216 X_TERRITORY_CODE => null,
217 X_MEANING => l_meaning,
218 X_DESCRIPTION => L_DESCRIPTION,
219 X_CREATION_DATE => trunc(sysdate),
220 X_CREATED_BY => 1,
221 X_LAST_UPDATE_DATE => trunc(sysdate),
222 X_LAST_UPDATED_BY => 1,
223 X_LAST_UPDATE_LOGIN => 0);
224 end if;
225 end LOOKUP_VAL_INSERT_ROW;
226
227
228 /****************************************************************************
229 Name : create_lookup_values
230 Description : If the parameter passed to the concurrent programe is create
231 this procedure will be called.
232 It deletes the existing lookup_values for the year and
233 create freshly.
234 *****************************************************************************/
235
236 Procedure create_lookup_values
237 (errbuf out nocopy varchar2,
238 retcode out nocopy number,
239 --p_business_group_id in number,
240 p_year in number,
241 p_plan_trng_ind in varchar2,
242 p_del_mode in varchar2)
243 is
244 -- Query for OLM plan for next year
245 -- Query for the Courses
246 Cursor csr_wsp_plan_courses
247 (p_start_date in date
248 , p_end_date in date
249 , p_year1 in number
250 )
251 is
252 select OAV.ACTIVITY_VERSION_ID LOOKUP_CODE,
253 substr(p_year1||':'||OAV_TL.VERSION_NAME ,1,80) MEANING,
254 substr(OAV_TL.DESCRIPTION,1,240) DESCRIPTION,
255 OAV_TL.LANGUAGE,
256 OAV_TL.Source_Lang
257 From OTA_ACTIVITY_VERSIONS OAV
258 , OTA_ACTIVITY_VERSIONS_TL OAV_TL
259 , HR_ORGANIZATION_INFORMATION org_i
260 Where OAV.BUSINESS_GROUP_ID = org_i.ORGANIZATION_ID
261 and org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
262 And org_i.ORG_INFORMATION9 = 'ZA'
263 and OAV.START_DATE <= p_end_date
264 and ( OAV.END_DATE >= p_start_date
265 OR
266 OAV.END_DATE is null)
267 and OAV.activity_version_id = OAV_TL.activity_version_id
268 and OAV_TL.LANGUAGE = userenv('LANG');
269
270
271 -- Query for Learning paths.
272 Cursor csr_wsp_plan_LP
273 (p_start_date in date
274 , p_end_date in date
275 , p_year1 in number
276 )
277 is
278 Select OLP.LEARNING_PATH_ID LOOKUP_CODE,
279 substr(p_year1||':'||OLP_TL.NAME,1,80) MEANING,
280 substr(OLP_TL.DESCRIPTION,1,240) DESCRIPTION,
281 OLP_TL.LANGUAGE,
282 OLP_TL.Source_Lang
283 From OTA_LEARNING_PATHS OLP
284 , OTA_LEARNING_PATHS_TL OLP_TL
285 , HR_ORGANIZATION_INFORMATION org_i
286 Where OLP.business_group_id = org_i.ORGANIZATION_ID
287 and org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
288 And org_i.ORG_INFORMATION9 = 'ZA'
289 And OLP.path_source_code = 'CATALOG' --Only for Bg level not at Mgr /Emp/Appriasal
290 And OLP.START_DATE_ACTIVE <= p_end_date
291 And ( OLP.END_DATE_ACTIVE >= p_start_date
292 OR
293 OLP.END_DATE_ACTIVE is null)
294 And OLP.LEARNING_PATH_ID = OLP_TL.LEARNING_PATH_ID
295 And OLP_TL.language = userenv('LANG') ;
296
297 -- Query for certifications
298 Cursor csr_wsp_plan_crt
299 (p_start_date in date
300 , p_end_date in date
301 , p_year1 in number
302 )
303 is
304 Select OC.CERTIFICATION_ID LOOKUP_CODE,
305 substr(p_year1||':'||OC_TL.NAME,1,80) MEANING,
306 substr(OC_TL.DESCRIPTION,1,240) DESCRIPTION,
307 OC_TL.LANGUAGE,
308 OC_TL.Source_Lang
309 From OTA_CERTIFICATIONS_B OC
310 , OTA_CERTIFICATIONS_TL OC_TL
311 , HR_ORGANIZATION_INFORMATION org_i
312 Where OC.business_group_id = org_i.ORGANIZATION_ID
313 and org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
314 And org_i.ORG_INFORMATION9 = 'ZA'
315 And OC.START_DATE_ACTIVE <= p_end_date
316 And ( OC.END_DATE_ACTIVE >= p_start_date
317 OR
318 OC.END_DATE_ACTIVE is null)
319 And OC.CERTIFICATION_ID = OC_TL.CERTIFICATION_ID
320 And OC_TL.language = userenv('LANG') ;
321
322
323 -- Competencies
324 -- Start Date can not be null
325 Cursor csr_wsp_comp
326 (p_start_date in date
327 , p_end_date in date
328 , p_year1 in number
329 )
330 is
331 Select PC.COMPETENCE_ID LOOKUP_CODE,
332 substr(p_year1||':'||PC_TL.NAME,1,80) MEANING,
333 substr(PC_TL.NAME,decode(sign(length(PC_TL.NAME)-240),1,-240,1)) DESCRIPTION,
334 PC_TL.LANGUAGE,
335 PC_TL.Source_Lang,
336 PC.business_group_id
337 From PER_COMPETENCES PC
338 , PER_COMPETENCES_TL PC_TL
339 Where PC.COMPETENCE_ID = PC_TL.COMPETENCE_ID
340 And PC_TL.language = userenv('LANG')
341 and PC.DATE_FROM <= p_end_date
342 and ( PC.DATE_TO >= p_start_date
343 OR PC.DATE_TO is null)
344 and (nvl(PC.business_group_id,0) =0
345 Or exists
346 ( Select 1 from
347 HR_ORGANIZATION_INFORMATION org_i
348 Where PC.business_group_id = org_i.ORGANIZATION_ID
349 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
350 And org_i.ORG_INFORMATION9 = 'ZA')
351 )
352 And exists
353 ( Select 1 from
354 Per_competence_elements pce
355 , HR_ORGANIZATION_INFORMATION org_i
356 , per_all_people_f pp
357 , per_all_assignments_f paa
358 Where pce.competence_id = pc.COMPETENCE_ID
359 And pce.type = 'PERSONAL'
360 And pce.person_id = pp.person_id
361 And pce.EFFECTIVE_DATE_FROM between pp.effective_start_date
362 and pp.effective_end_date
363 And paa.person_id = pp.person_id
364 And paa.assignment_type = 'E'
365 And paa.primary_flag = 'Y'
366 And pce.EFFECTIVE_DATE_FROM between paa.effective_start_date
367 and paa.effective_end_date
368 and pce.EFFECTIVE_DATE_FROM between g_trnd_year_start_date
369 And g_trnd_year_end_date
370 and pce.business_group_id = org_i.ORGANIZATION_ID
371 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
372 And org_i.ORG_INFORMATION9 = 'ZA'
373 );
374
375
376 -- Qualifications
377
378 -- BUSINESS_GROUP_ID can be 0 for global QUalification and can be null.
379 -- Start Date and End Date both can be null
380 Cursor csr_wsp_qual
381 (p_start_date in date
382 , p_end_date in date
383 , p_year1 in number
384 )
385 is
386 Select pqt.qualification_type_id LOOKUP_CODE
387 , substr(p_year1||':'||pqtl.NAME,1,80) MEANING
388 , pqtl.NAME DESCRIPTION
389 from
390 per_qualification_types pqt
391 , per_qualification_types_tl pqtl
392 Where pqt.qualification_type_id = pqtl.qualification_type_id
393 and pqtl.language = userenv('LANG')
394 and pqt.qualification_type_id in
395 ( Select pq.qualification_type_id
396 from
397 per_qualifications pq
398 , PER_ESTABLISHMENT_ATTENDANCES pea
399 , per_all_people_f pp
400 , per_all_assignments_f paa
401 , HR_ORGANIZATION_INFORMATION org_i
402 Where pqt.qualification_type_id = pq.qualification_type_id
403 And paa.person_id = pp.person_id
404 and paa.assignment_type = 'E'
405 and paa.primary_flag = 'Y'
406 and pq.AWARDED_DATE between paa.effective_start_date
407 and paa.effective_end_date
408 And pea.ATTENDANCE_ID (+) = pq.attendance_id
409 and pq.AWARDED_DATE between g_trnd_year_start_date
410 And g_trnd_year_end_date
411 and nvl(pea.person_id,pq.person_id) = pp.person_id
412 and pq.AWARDED_DATE between pp.effective_start_date
413 and pp.effective_end_date
414 And pp.business_group_id = org_i.ORGANIZATION_ID
415 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
416 And org_i.ORG_INFORMATION9 = 'ZA'
417 );
418
419 ---- End for trained/Completed
420 l_count number;
421
422 Begin
423
424 If p_plan_trng_ind = '10' OR p_plan_trng_ind = '20' then
425
426 /* Deleting the existing lookup_values from plan type for this year*/
427 If p_del_mode = 'Y' then
428 Delete from fnd_lookup_values
429 Where lookup_type in
430 ( g_p_lpath_lookup_type
431 , g_p_course_lookup_type
432 , g_p_cert_lookup_type
433 )
434 AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
435 And substr(lookup_code,1,4) = p_year;
436 end if;
437
438 /* Calling create looukp values for planed Learning paths */
439 for lp_rec in csr_wsp_plan_LP
440 ( g_plan_year_start_date
441 , g_plan_year_end_date
442 , p_year
443 )
444 loop
445 Select count(*)
446 into l_count
447 From
448 OTA_LP_ENROLLMENTS OLE
449 , HR_ORGANIZATION_INFORMATION org_i
450 , OTA_LP_MEMBER_ENROLLMENTS OLME
451 , per_all_people_f pp
452 , per_all_assignments_f paa
453 Where OLE.LEARNING_PATH_ID = lp_rec.LOOKUP_CODE
454 And OLME.LP_ENROLLMENT_ID = OLE.LP_ENROLLMENT_ID
455 And OLE.PATH_STATUS_CODE <> 'CANCELLED'
456 And ( OLE.COMPLETION_DATE between g_plan_year_start_date
457 And g_plan_year_end_date
458 OR
459 OLE.COMPLETION_DATE IS null)
460 AND pp.person_id = OLE.PERSON_ID
461 And paa.person_id = pp.person_id
462 and paa.assignment_type = 'E'
463 and paa.primary_flag = 'Y'
464 and OLE.business_group_id = paa.BUSINESS_GROUP_ID
465 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
466 And org_i.ORG_INFORMATION9 = 'ZA';
467
468 if l_count > 0 then
469
470 LOOKUP_VAL_INSERT_ROW
471 ( P_LOOKUP_TYPE => g_p_lpath_lookup_type
472 , P_LOOKUP_CODE => p_year||lp_rec.LOOKUP_CODE
473 , P_ATTRIBUTE1 => null
474 , P_ATTRIBUTE2 => null
475 , P_ATTRIBUTE3 => null
476 , P_ATTRIBUTE4 => null
477 , P_ATTRIBUTE5 => null
478 , P_ATTRIBUTE6 => null
479 , P_ATTRIBUTE7 => null
480 , P_ATTRIBUTE8 => null
481 , P_ATTRIBUTE9 => null
482 , P_ATTRIBUTE10 => null
483 , P_ATTRIBUTE11 => null
484 , P_ATTRIBUTE12 => null
485 , P_ATTRIBUTE13 => null
486 , P_ATTRIBUTE14 => null
487 , P_ATTRIBUTE15 => null
488 , P_ENABLED_FLAG => 'Y'
489 , P_MEANING => lp_rec.MEANING
490 , P_DESCRIPTION => lp_rec.DESCRIPTION
491 , P_START_DATE_ACTIVE => g_plan_year_start_date
492 , P_END_DATE_ACTIVE => g_plan_year_end_date
493 );
494 end if;
495
496 end loop;
497 /* Calling create looukp values for planed Courses */
498 for course_rec in csr_wsp_plan_courses
499 ( g_plan_year_start_date
500 , g_plan_year_end_date
501 , p_year
502 )
503 loop
504 Select count(*)
505 into l_count
506 from
507 OTA_EVENTS oe
508 , OTA_DELEGATE_BOOKINGS odb
509 , OTA_BOOKING_STATUS_TYPES obst
510 , HR_ORGANIZATION_INFORMATION org_i
511 , per_all_people_f pp
512 , per_all_assignments_f paa
513 wHERE ACTIVITY_VERSION_ID = course_rec.LOOKUP_CODE
514 aND oe.EVENT_TYPE in ( 'SCHEDULED', 'SELFPACED')
515 AND OE.course_START_DATE <= g_plan_year_end_date
516 AND NVL(OE.course_end_DATE, g_plan_year_start_date) >= g_plan_year_start_date
517 aND ODB.EVENT_ID = oe.EVENT_ID
518 And ODB.INTERNAL_BOOKING_FLAG = 'Y'
519 And paa.person_id = pp.person_id
520 and paa.assignment_type = 'E'
521 and paa.primary_flag = 'Y'
522 and ODB.DATE_BOOKING_PLACED between paa.effective_start_date
523 and paa.effective_end_date
524 And pp.person_id = ODB.DELEGATE_PERSON_ID
525 And ODB.DATE_BOOKING_PLACED between pp.effective_start_date
526 and pp.effective_end_date
527 And paa.business_group_id = org_i.ORGANIZATION_ID
528 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
529 And org_i.ORG_INFORMATION9 = 'ZA'
530 aND OBST.BOOKING_STATUS_TYPE_ID = odb.BOOKING_STATUS_TYPE_ID
531 AND obst.TYPE IN ('P','W','R','A'); -- 'C'' cANCELLED, 'P' Palced , 'W' Waitlisted, 'R' Requested, 'A' Attended
532
533
534 if l_count > 0 then
535 LOOKUP_VAL_INSERT_ROW
536 ( P_LOOKUP_TYPE => g_p_course_lookup_type
537 , P_LOOKUP_CODE => p_year||course_rec.LOOKUP_CODE
538 , P_ATTRIBUTE1 => null
539 , P_ATTRIBUTE2 => null
540 , P_ATTRIBUTE3 => null
541 , P_ATTRIBUTE4 => null
542 , P_ATTRIBUTE5 => null
543 , P_ATTRIBUTE6 => null
544 , P_ATTRIBUTE7 => null
545 , P_ATTRIBUTE8 => null
546 , P_ATTRIBUTE9 => null
547 , P_ATTRIBUTE10 => null
548 , P_ATTRIBUTE11 => null
549 , P_ATTRIBUTE12 => null
550 , P_ATTRIBUTE13 => null
551 , P_ATTRIBUTE14 => null
552 , P_ATTRIBUTE15 => null
553 , P_ENABLED_FLAG => 'Y'
554 , P_MEANING => course_rec.MEANING
555 , P_DESCRIPTION => course_rec.DESCRIPTION
556 , P_START_DATE_ACTIVE => g_plan_year_start_date
557 , P_END_DATE_ACTIVE => g_plan_year_end_date
558 );
559 end if;
560 end loop;
561 /* Calling create looukp values for planed Certifications */
562 for cert_rec in csr_wsp_plan_crt
563 ( g_plan_year_start_date
564 , g_plan_year_end_date
565 , p_year
566 )
567 loop
568 Select count(*)
569 into l_count
570 From
571 OTA_CERT_ENROLLMENTS OCE
572 , HR_ORGANIZATION_INFORMATION org_i
573 , per_all_people_f pp
574 , per_all_assignments_f paa
575 Where
576 OCE.CERTIFICATION_ID = cert_rec.LOOKUP_CODE
577 And OCE.PERSON_ID = PP.person_id
578 And paa.person_id = pp.person_id
579 and paa.assignment_type = 'E'
580 and paa.primary_flag = 'Y'
581 AND org_i.ORGANIZATION_ID = paa.business_group_id
582 And OCE.CERTIFICATION_STATUS_CODE = 'ENROLLED'
583 And ( OCE.COMPLETION_DATE Between g_plan_year_start_date
584 And g_plan_year_end_date
585 OR
586 OCE.COMPLETION_DATE IS null)
587 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
588 And org_i.ORG_INFORMATION9 = 'ZA';
589
590 if l_count > 0 then
591 LOOKUP_VAL_INSERT_ROW
592 ( P_LOOKUP_TYPE => g_p_cert_lookup_type
593 , P_LOOKUP_CODE => p_year||cert_rec.LOOKUP_CODE
594 , P_ATTRIBUTE1 => null
595 , P_ATTRIBUTE2 => null
596 , P_ATTRIBUTE3 => null
597 , P_ATTRIBUTE4 => null
598 , P_ATTRIBUTE5 => null
599 , P_ATTRIBUTE6 => null
600 , P_ATTRIBUTE7 => null
601 , P_ATTRIBUTE8 => null
602 , P_ATTRIBUTE9 => null
603 , P_ATTRIBUTE10 => null
604 , P_ATTRIBUTE11 => null
605 , P_ATTRIBUTE12 => null
606 , P_ATTRIBUTE13 => null
607 , P_ATTRIBUTE14 => null
608 , P_ATTRIBUTE15 => null
609 , P_ENABLED_FLAG => 'Y'
610 , P_MEANING => cert_rec.MEANING
611 , P_DESCRIPTION => cert_rec.DESCRIPTION
612 , P_START_DATE_ACTIVE => g_plan_year_start_date
613 , P_END_DATE_ACTIVE => g_plan_year_end_date
614 );
615 end if;
616 end loop;
617
618 end if;
619
620 If p_plan_trng_ind = '10' OR p_plan_trng_ind = '30' then
621 If p_del_mode = 'Y' then
622 Delete from fnd_lookup_values
623 Where lookup_type in
624 ( g_t_lpath_lookup_type
625 , g_t_course_lookup_type
626 , g_t_cert_lookup_type
627 , g_t_comp_lookup_type
628 , g_t_qual_lookup_type
629 )
630 AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
631 And substr(lookup_code,1,4) = p_year - 1;
632 end if;
633
634 /* Calling create looukp values for trained Learning paths */
635 for lp_rec in csr_wsp_plan_LP
636 ( g_trnd_year_start_date
637 , g_trnd_year_end_date
638 , p_year -1
639 )
640 loop
641 Select count(*)
642 into l_count
643 From
644 OTA_LP_ENROLLMENTS OLE
645 , HR_ORGANIZATION_INFORMATION org_i
646 , OTA_LP_MEMBER_ENROLLMENTS OLME
647 Where OLE.LEARNING_PATH_ID = lp_rec.LOOKUP_CODE
648 And OLE.PATH_STATUS_CODE = 'COMPLETED'
649 And OLME.LP_ENROLLMENT_ID = OLE.LP_ENROLLMENT_ID
650 And OLE.COMPLETION_DATE between g_trnd_year_start_date
651 And g_trnd_year_end_date
652 and OLE.business_group_id = org_i.ORGANIZATION_ID
653 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
654 And org_i.ORG_INFORMATION9 = 'ZA';
655
656 if l_count > 0 then
657 LOOKUP_VAL_INSERT_ROW
658 ( P_LOOKUP_TYPE => g_t_lpath_lookup_type
659 , P_LOOKUP_CODE => p_year -1||lp_rec.LOOKUP_CODE
660 , P_ATTRIBUTE1 => null
661 , P_ATTRIBUTE2 => null
662 , P_ATTRIBUTE3 => null
663 , P_ATTRIBUTE4 => null
664 , P_ATTRIBUTE5 => null
665 , P_ATTRIBUTE6 => null
666 , P_ATTRIBUTE7 => null
667 , P_ATTRIBUTE8 => null
668 , P_ATTRIBUTE9 => null
669 , P_ATTRIBUTE10 => null
670 , P_ATTRIBUTE11 => null
671 , P_ATTRIBUTE12 => null
672 , P_ATTRIBUTE13 => null
673 , P_ATTRIBUTE14 => null
674 , P_ATTRIBUTE15 => null
675 , P_ENABLED_FLAG => 'Y'
676 , P_MEANING => lp_rec.MEANING
677 , P_DESCRIPTION => lp_rec.DESCRIPTION
678 , P_START_DATE_ACTIVE => g_trnd_year_start_date
679 , P_END_DATE_ACTIVE => g_trnd_year_end_date
680 );
681 end if;
682 end loop;
683 /* Calling create looukp values for trained Courses */
684 for course_rec in csr_wsp_plan_courses
685 ( g_trnd_year_start_date
686 , g_trnd_year_end_date
687 , p_year -1
688 )
689 loop
690
691 Select count(*)
692 into l_count
693 from
694 OTA_EVENTS oe
695 , OTA_DELEGATE_BOOKINGS odb
696 , OTA_BOOKING_STATUS_TYPES obst
697 , HR_ORGANIZATION_INFORMATION org_i
698 , per_all_people_f pp
699 , per_all_assignments_f paa
700 wHERE ACTIVITY_VERSION_ID = course_rec.LOOKUP_CODE
701 aND oe.EVENT_TYPE in ( 'SCHEDULED', 'SELFPACED')
702 AND OE.course_START_DATE <= g_trnd_year_end_date
703 AND NVL(OE.course_end_DATE, g_trnd_year_start_date) >= g_trnd_year_start_date
704 aND ODB.EVENT_ID = oe.EVENT_ID
705 And ODB.INTERNAL_BOOKING_FLAG = 'Y'
706 And paa.person_id = pp.person_id
707 and paa.assignment_type = 'E'
708 and paa.primary_flag = 'Y'
709 AND odb.DATE_STATUS_CHANGED BETWEEN g_trnd_year_start_date
710 And g_trnd_year_end_date
711 and odb.DATE_STATUS_CHANGED between paa.effective_start_date
712 and paa.effective_end_date
713 And pp.person_id = odb.DELEGATE_PERSON_ID
714 And odb.DATE_STATUS_CHANGED between pp.effective_start_date
715 and pp.effective_end_date
716 And odb.business_group_id = org_i.ORGANIZATION_ID
717 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
718 And org_i.ORG_INFORMATION9 = 'ZA'
719 aND OBST.BOOKING_STATUS_TYPE_ID = odb.BOOKING_STATUS_TYPE_ID
720 AND obst.TYPE = 'A'; -- Attended
721
722 if l_count > 0 then
723 LOOKUP_VAL_INSERT_ROW
724 ( P_LOOKUP_TYPE => g_t_course_lookup_type
725 , P_LOOKUP_CODE => p_year -1||course_rec.LOOKUP_CODE
726 , P_ATTRIBUTE1 => null
727 , P_ATTRIBUTE2 => null
728 , P_ATTRIBUTE3 => null
729 , P_ATTRIBUTE4 => null
730 , P_ATTRIBUTE5 => null
731 , P_ATTRIBUTE6 => null
732 , P_ATTRIBUTE7 => null
733 , P_ATTRIBUTE8 => null
734 , P_ATTRIBUTE9 => null
735 , P_ATTRIBUTE10 => null
736 , P_ATTRIBUTE11 => null
737 , P_ATTRIBUTE12 => null
738 , P_ATTRIBUTE13 => null
739 , P_ATTRIBUTE14 => null
740 , P_ATTRIBUTE15 => null
741 , P_ENABLED_FLAG => 'Y'
742 , P_MEANING => course_rec.MEANING
743 , P_DESCRIPTION => course_rec.DESCRIPTION
744 , P_START_DATE_ACTIVE => g_trnd_year_start_date
745 , P_END_DATE_ACTIVE => g_trnd_year_end_date
746 );
747 end if;
748 end loop;
749 /* Calling create looukp values for Trained Certifications */
750 for cert_rec in csr_wsp_plan_crt
751 ( g_trnd_year_start_date
752 , g_trnd_year_end_date
753 , p_year -1
754 )
755 loop
756
757 Select count(*)
758 into l_count
759 From
760 OTA_CERT_ENROLLMENTS OCE
761 , HR_ORGANIZATION_INFORMATION org_i
762 Where
763 OCE.CERTIFICATION_ID = cert_rec.LOOKUP_CODE
764 And OCE.BUSINESS_GROUP_ID = org_i.ORGANIZATION_ID
765 And OCE.CERTIFICATION_STATUS_CODE = 'CERTIFIED'
766 And OCE.COMPLETION_DATE Between g_trnd_year_start_date
767 And g_trnd_year_end_date
768 And org_i.ORG_INFORMATION_CONTEXT = 'Business Group Information'
769 And org_i.ORG_INFORMATION9 = 'ZA';
770
771 if l_count > 0 then
772 LOOKUP_VAL_INSERT_ROW
773 ( P_LOOKUP_TYPE => g_t_cert_lookup_type
774 , P_LOOKUP_CODE => p_year -1||cert_rec.LOOKUP_CODE
775 , P_ATTRIBUTE1 => null
776 , P_ATTRIBUTE2 => null
777 , P_ATTRIBUTE3 => null
778 , P_ATTRIBUTE4 => null
779 , P_ATTRIBUTE5 => null
780 , P_ATTRIBUTE6 => null
781 , P_ATTRIBUTE7 => null
782 , P_ATTRIBUTE8 => null
783 , P_ATTRIBUTE9 => null
784 , P_ATTRIBUTE10 => null
785 , P_ATTRIBUTE11 => null
786 , P_ATTRIBUTE12 => null
787 , P_ATTRIBUTE13 => null
788 , P_ATTRIBUTE14 => null
789 , P_ATTRIBUTE15 => null
790 , P_ENABLED_FLAG => 'Y'
791 , P_MEANING => cert_rec.MEANING
792 , P_DESCRIPTION => cert_rec.DESCRIPTION
793 , P_START_DATE_ACTIVE => g_trnd_year_start_date
794 , P_END_DATE_ACTIVE => g_trnd_year_end_date
795 );
796 end if;
797 end loop;
798
799 for comp_rec in csr_wsp_comp
800 ( g_trnd_year_start_date
801 , g_trnd_year_end_date
802 , p_year -1
803 )
804 loop
805 LOOKUP_VAL_INSERT_ROW
806 ( P_LOOKUP_TYPE => g_t_comp_lookup_type
807 , P_LOOKUP_CODE => p_year -1||comp_rec.LOOKUP_CODE
808 , P_ATTRIBUTE1 => null
809 , P_ATTRIBUTE2 => null
810 , P_ATTRIBUTE3 => null
811 , P_ATTRIBUTE4 => null
812 , P_ATTRIBUTE5 => null
813 , P_ATTRIBUTE6 => null
814 , P_ATTRIBUTE7 => null
815 , P_ATTRIBUTE8 => null
816 , P_ATTRIBUTE9 => null
817 , P_ATTRIBUTE10 => null
818 , P_ATTRIBUTE11 => null
819 , P_ATTRIBUTE12 => null
820 , P_ATTRIBUTE13 => null
821 , P_ATTRIBUTE14 => null
822 , P_ATTRIBUTE15 => null
823 , P_ENABLED_FLAG => 'Y'
824 , P_MEANING => comp_rec.MEANING
825 , P_DESCRIPTION => comp_rec.DESCRIPTION
826 , P_START_DATE_ACTIVE => g_trnd_year_start_date
827 , P_END_DATE_ACTIVE => g_trnd_year_end_date
828 );
829
830 end loop;
831
832 for qual_rec in csr_wsp_qual
833 ( g_trnd_year_start_date
834 , g_trnd_year_end_date
835 , p_year -1
836 )
837 loop
838 LOOKUP_VAL_INSERT_ROW
839 ( P_LOOKUP_TYPE => g_t_qual_lookup_type
840 , P_LOOKUP_CODE => p_year -1||qual_rec.LOOKUP_CODE
841 , P_ATTRIBUTE1 => null
842 , P_ATTRIBUTE2 => null
843 , P_ATTRIBUTE3 => null
844 , P_ATTRIBUTE4 => null
845 , P_ATTRIBUTE5 => null
846 , P_ATTRIBUTE6 => null
847 , P_ATTRIBUTE7 => null
848 , P_ATTRIBUTE8 => null
849 , P_ATTRIBUTE9 => null
850 , P_ATTRIBUTE10 => null
851 , P_ATTRIBUTE11 => null
852 , P_ATTRIBUTE12 => null
853 , P_ATTRIBUTE13 => null
854 , P_ATTRIBUTE14 => null
855 , P_ATTRIBUTE15 => null
856 , P_ENABLED_FLAG => 'Y'
857 , P_MEANING => qual_rec.MEANING
858 , P_DESCRIPTION => qual_rec.DESCRIPTION
859 , P_START_DATE_ACTIVE => g_trnd_year_start_date
860 , P_END_DATE_ACTIVE => g_trnd_year_end_date
861 );
862
863 end loop;
864
865 end if;
866
867 end create_lookup_values;
868
869 Procedure refresh_lookup_values
870 (errbuf out nocopy varchar2,
871 retcode out nocopy number,
872 p_year in number,
873 p_plan_trng_ind in varchar2)
874 is
875 Begin
876 If p_plan_trng_ind = '10' OR p_plan_trng_ind = '20' then
877 Update fnd_lookup_values
878 Set ATTRIBUTE_CATEGORY = G_ATTRIBUTE_CATEGORY
879 Where lookup_type in
880 (
881 g_p_lpath_lookup_type
882 , g_p_course_lookup_type
883 , g_p_cert_lookup_type
884 )
885 And ATTRIBUTE_CATEGORY is null
886 And security_group_id = fnd_global.lookup_security_group(lookup_type,3);
887
888 end if;
889
890 If p_plan_trng_ind = '10' OR p_plan_trng_ind = '30' then
891 Update fnd_lookup_values
892 Set ATTRIBUTE_CATEGORY = G_ATTRIBUTE_CATEGORY
893 Where lookup_type in
894 (
895 g_t_lpath_lookup_type
896 , g_t_course_lookup_type
897 , g_t_cert_lookup_type
898 , g_t_comp_lookup_type
899 , g_t_qual_lookup_type
900 )
901 And security_group_id = fnd_global.lookup_security_group(lookup_type,3)
902 And ATTRIBUTE_CATEGORY is null;
903
904 end if;
905
906 create_lookup_values
907 (errbuf => errbuf
908 , retcode => retcode
909 , p_year => p_year
910 , p_plan_trng_ind => p_plan_trng_ind
911 , p_del_mode => 'N' -- N do not delete the existing lookup values
912 );
913
914 end refresh_lookup_values;
915
916 /****************************************************************************
917 Name : val_usr_row
918 Description : Validate the passed user_row
919 if the user row does not fall in current period
920 will create the user and
921
922 *****************************************************************************/
923
924 procedure val_usr_row
925 ( P_user_row_id in number
926 , p_year in number
927 , p_from_year in number
928 , errbuf out nocopy varchar2
929 , retcode out nocopy number
930 )
931 is
932 cursor csr_usr_row ( p_from_start_date date
933 , p_from_end_date date
934 , p_start_date date
935 )
936 is
937 select user_row_id,
938 user_table_id,
939 ROW_LOW_RANGE_OR_NAME,
940 DISPLAY_SEQUENCE,
941 business_group_id,
942 legislation_code,
943 row_high_range,
944 effective_end_date
945 from pay_user_rows_f usr
946 where usr.user_row_id = P_user_row_id
947 and usr.effective_end_date =
948 (
949 Select max(usr1.effective_end_date)
950 From pay_user_rows_f usr1
951 Where usr1.user_row_id = P_user_row_id
952 And usr.effective_end_date >= p_from_start_date
953 And usr.effective_start_date <= p_from_end_date
954 )
955 and usr.effective_end_date < p_start_date;
956
957 cursor csr_usr_val( p_from_start_date date
958 , p_from_end_date date
959 , p_start_date date
960 ) is
961 select USER_COLUMN_INSTANCE_ID
962 ,EFFECTIVE_START_DATE
963 ,EFFECTIVE_END_DATE
964 ,USER_ROW_ID
965 ,USER_COLUMN_ID
966 ,BUSINESS_GROUP_ID
967 ,LEGISLATION_CODE
968 ,LEGISLATION_SUBGROUP
969 ,VALUE
970 from pay_user_column_instances_f puv
971 Where puv.user_row_id = p_user_row_id
972 and puv.EFFECTIVE_END_DATE =
973 (
974 select max(puv1.EFFECTIVE_END_DATE)
975 From pay_user_column_instances_f puv1
976 Where puv1.EFFECTIVE_END_DATE >= p_from_start_date
977 and puv1.EFFECTIVE_START_DATE <= p_from_end_date
978 )
979 and puv.effective_end_date < p_start_date;
980
981 l_disable_range_overlap_check BOOLEAN DEFAULT TRUE;
982 l_count number;
983 l_range_overlapped BOOLEAN;
984 l_row_high_range number;
985 l_user_row_id number;
986 l_user_col_inst_id number;
987 l_obj_ver number;
988 l_from_start_date date;
989 l_from_end_date date;
990 l_start_date date;
991 l_end_date date;
992 l_year number;
993 l_from_year number;
994 begin
995 IF P_user_row_id IS NOT NULL then
996 l_from_year := p_from_year - 1;
997 l_year := p_year -1;
998 hr_utility.set_location('inside val_usr_row',1);
999 hr_utility.set_location('P_user_row_id '|| P_user_row_id,1);
1000 l_from_start_date := to_date(to_char(g_plan_year_start_date,'DD-MM-')||l_from_year,'DD-MM-YYYY');
1001 hr_utility.set_location('l_from_start_date '|| l_from_start_date,1);
1002 l_from_end_date := to_date(to_char(g_plan_year_end_date,'DD-MM-')||p_from_year,'DD-MM-YYYY');
1003 hr_utility.set_location('l_from_end_date '|| l_from_end_date,1);
1004
1005 l_start_date := to_date(to_char(g_plan_year_start_date,'DD-MM-')||l_year,'DD-MM-YYYY');
1006 hr_utility.set_location('l_start_date '|| l_start_date,1);
1007 l_end_date := to_date(to_char(g_plan_year_end_date,'DD-MM-')||p_year,'DD-MM-YYYY');
1008 hr_utility.set_location('l_end_date '|| l_end_date,1);
1009 if tab_usr_row_ids.exists(P_user_row_id) then
1010 hr_utility.set_location('tab_usr_row_ids.exists(P_user_row_id)',1);
1011 else
1012 for rec_usr_row in csr_usr_row
1013 ( l_from_start_date
1014 , l_from_end_date
1015 , l_start_date
1016 )
1017 loop
1018 Select count(*) into l_obj_ver
1019 From pay_user_rows_f usr1
1020 Where usr1.user_row_id = P_user_row_id
1021 And usr1.effective_end_date >= l_start_date
1022 And usr1.effective_start_date <= l_end_date;
1023 IF l_obj_ver > 0 THEN
1024 --fnd_message.set_name('PER','PER_34003_USER_ROW_OVERLAP');
1025 --fnd_message.error;
1026 hr_utility.set_location('PER_34003_USER_ROW_OVERLAP ',1);
1027 errbuf := substr(fnd_message.get_string('PER','PER_34003_USER_ROW_OVERLAP'),1,255);
1028 retcode := P_user_row_id;
1029 --exit;
1030 else
1031 l_user_row_id := P_user_row_id;
1032 l_obj_ver := 1;
1033 hr_utility.set_location('Calling pay_user_row_api.create_user_row ',1);
1034 pay_user_row_api.create_user_row(
1035 p_validate => FALSE
1036 ,p_effective_date => l_start_date
1037 ,p_user_table_id => rec_usr_row.user_table_id
1038 ,p_row_low_range_or_name => rec_usr_row.ROW_LOW_RANGE_OR_NAME
1039 ,p_display_sequence => rec_usr_row.DISPLAY_SEQUENCE
1040 ,p_business_group_id => rec_usr_row.business_group_id
1041 ,p_legislation_code => rec_usr_row.legislation_code
1042 ,p_disable_range_overlap_check => l_disable_range_overlap_check
1043 ,p_disable_units_check => TRUE
1044 ,p_row_high_range => rec_usr_row.row_high_range
1045 ,p_user_row_id => l_user_row_id
1046 ,p_object_version_number => l_obj_ver
1047 ,p_effective_start_date => l_start_date
1048 ,p_effective_end_date => l_end_date);
1049 for rec_usr_val in csr_usr_val
1050 ( l_from_start_date
1051 , l_from_end_date
1052 , l_start_date
1053 )
1054 loop
1055 l_user_col_inst_id := rec_usr_val.user_column_instance_id;
1056 hr_utility.set_location('Calling pay_user_column_instance_api.create_user_column_instance ',1);
1057 pay_user_column_instance_api.create_user_column_instance
1058 ( p_validate => FALSE
1059 , p_effective_date => l_start_date
1060 , p_user_row_id => l_user_row_id
1061 , p_user_column_id => rec_usr_val.user_column_id
1062 , p_value => rec_usr_val.value
1063 , p_business_group_id => rec_usr_val.business_group_id
1064 , p_legislation_code => rec_usr_val.legislation_code
1065 , p_user_column_instance_id => l_user_col_inst_id
1066 , p_object_version_number => l_obj_ver
1067 , p_effective_start_date => l_start_date
1068 , p_effective_end_date => l_end_date);
1069 end loop;
1070 END IF;
1071 end loop;
1072 hr_utility.set_location('setting in pl/sql table P_user_row_id ' || P_user_row_id,1);
1073 tab_usr_row_ids(P_user_row_id) := P_user_row_id;
1074 end if;
1075 END if;
1076 hr_utility.set_location('Back from val_usr_row ',1);
1077
1078 end val_usr_row;
1079
1080
1081 /****************************************************************************
1082 Name : copy_lookup_values
1083 Description : If the parameter passed to the concurrent programe is Copy
1084 this procedure will be called.
1085 It copies the lookup along with priorities from already
1086 created lookups.
1087 *****************************************************************************/
1088
1089
1090 Procedure copy_lookup_values
1091 (errbuf out nocopy varchar2,
1092 retcode out nocopy number,
1093 --p_business_group_id in number,
1094 p_year in number,
1095 P_from_year in number,
1096 p_plan_trng_ind in varchar2)
1097 is
1098 l_count number;
1099 l_plan_count number;
1100 l_trnd_count number;
1101 Cursor csr_cp_lookup_values
1102 ( l_lookup_type in varchar2
1103 , l_year in number
1104 , l_from_year in number
1105 ) is
1106 select l_year || substr(lookup_code,5) lookup_code
1107 , lookup_type
1108 , LANGUAGE
1109 , l_year || substr(MEANING,5) MEANING
1110 , DESCRIPTION
1111 , ENABLED_FLAG
1112 -- , to_date(to_char(START_DATE_ACTIVE,'DD-MM')||p_year -1,'DD-MM-YYYY') START_DATE_ACTIVE
1113 -- , to_date(to_char(END_DATE_ACTIVE,'DD-MM')||p_year,'DD-MM-YYYY') END_DATE_ACTIVE
1114 , SOURCE_LANG
1115 , SECURITY_GROUP_ID
1116 , VIEW_APPLICATION_ID
1117 , TERRITORY_CODE
1118 , ATTRIBUTE_CATEGORY
1119 , ATTRIBUTE1
1120 , ATTRIBUTE2
1121 , ATTRIBUTE3
1122 , ATTRIBUTE4
1123 , ATTRIBUTE5
1124 , ATTRIBUTE6
1125 , ATTRIBUTE7
1126 , ATTRIBUTE8
1127 , ATTRIBUTE9
1128 , ATTRIBUTE10
1129 , ATTRIBUTE11
1130 , ATTRIBUTE12
1131 , ATTRIBUTE13
1132 , ATTRIBUTE14
1133 , ATTRIBUTE15
1134 from fnd_lookup_values
1135 Where lookup_type = l_lookup_type
1136 and substr(lookup_code,1,4) = to_char(l_from_year)
1137 And security_group_id = fnd_global.lookup_security_group(l_lookup_type,3)
1138 and (ATTRIBUTE1 ||ATTRIBUTE2 ||ATTRIBUTE3 ||ATTRIBUTE4 ||
1139 ATTRIBUTE5 ||ATTRIBUTE6 ||ATTRIBUTE7 ||ATTRIBUTE8 ||
1140 ATTRIBUTE9 ||ATTRIBUTE10||ATTRIBUTE11||ATTRIBUTE12||
1141 ATTRIBUTE13||ATTRIBUTE14||ATTRIBUTE15) is not null;
1142
1143
1144 begin
1145
1146 hr_utility.SET_LOCATION('Inside copy_lookup_values',1);
1147 hr_utility.SET_LOCATION('p_year '||p_year,1);
1148 hr_utility.SET_LOCATION('P_from_year ' || P_from_year,1);
1149 hr_utility.SET_LOCATION('p_plan_trng_ind ' || p_plan_trng_ind,1);
1150
1151
1152 select count(*) into l_trnd_count
1153 from fnd_lookup_values
1154 Where lookup_type in
1155 (
1156 g_t_lpath_lookup_type
1157 , g_t_course_lookup_type
1158 , g_t_cert_lookup_type
1159 )
1160 AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
1161 and substr(lookup_code,1,4) = p_year-1;
1162
1163 hr_utility.SET_LOCATION('l_trnd_count ' || l_trnd_count,1);
1164
1165 select count(*) into l_plan_count
1166 from fnd_lookup_values
1167 Where lookup_type in
1168 (
1169 g_p_lpath_lookup_type
1170 , g_p_course_lookup_type
1171 , g_p_cert_lookup_type
1172 )
1173 AND security_group_id = fnd_global.lookup_security_group(lookup_type,3)
1174 and substr(lookup_code,1,4) = p_year;
1175
1176 hr_utility.SET_LOCATION('l_plan_count ' || l_plan_count,1);
1177
1178 l_count := l_trnd_count + l_plan_count;
1179
1180 if l_count = 0 then
1181 tab_usr_row_ids.delete;
1182 if p_plan_trng_ind = '20' or p_plan_trng_ind = '10' then
1183
1184 hr_utility.SET_LOCATION('calling Planned g_p_lpath_lookup_type ' ,1);
1185
1186 for csr_lp_rec in csr_cp_lookup_values
1187 ( g_p_lpath_lookup_type
1188 , p_year
1189 , p_from_year
1190 )
1191 loop
1192 -- Check if the priority copying is available in the period
1193
1194 val_usr_row(csr_lp_rec.ATTRIBUTE1,p_year,p_from_year,errbuf,retcode);
1195 val_usr_row(csr_lp_rec.ATTRIBUTE2,p_year,p_from_year,errbuf,retcode);
1196 val_usr_row(csr_lp_rec.ATTRIBUTE3,p_year,p_from_year,errbuf,retcode);
1197 val_usr_row(csr_lp_rec.ATTRIBUTE4,p_year,p_from_year,errbuf,retcode);
1198 val_usr_row(csr_lp_rec.ATTRIBUTE5,p_year,p_from_year,errbuf,retcode);
1199 val_usr_row(csr_lp_rec.ATTRIBUTE6,p_year,p_from_year,errbuf,retcode);
1200 val_usr_row(csr_lp_rec.ATTRIBUTE7,p_year,p_from_year,errbuf,retcode);
1201 val_usr_row(csr_lp_rec.ATTRIBUTE8,p_year,p_from_year,errbuf,retcode);
1202 val_usr_row(csr_lp_rec.ATTRIBUTE9,p_year,p_from_year,errbuf,retcode);
1203 val_usr_row(csr_lp_rec.ATTRIBUTE10,p_year,p_from_year,errbuf,retcode);
1204 val_usr_row(csr_lp_rec.ATTRIBUTE11,p_year,p_from_year,errbuf,retcode);
1205 val_usr_row(csr_lp_rec.ATTRIBUTE12,p_year,p_from_year,errbuf,retcode);
1206 val_usr_row(csr_lp_rec.ATTRIBUTE13,p_year,p_from_year,errbuf,retcode);
1207 val_usr_row(csr_lp_rec.ATTRIBUTE14,p_year,p_from_year,errbuf,retcode);
1208 val_usr_row(csr_lp_rec.ATTRIBUTE15,p_year,p_from_year,errbuf,retcode);
1209
1210 LOOKUP_VAL_INSERT_ROW
1211 ( P_LOOKUP_TYPE => csr_lp_rec.LOOKUP_TYPE
1212 , P_LOOKUP_CODE => csr_lp_rec.LOOKUP_CODE
1213 , P_ATTRIBUTE1 => csr_lp_rec.ATTRIBUTE1
1214 , P_ATTRIBUTE2 => csr_lp_rec.ATTRIBUTE2
1215 , P_ATTRIBUTE3 => csr_lp_rec.ATTRIBUTE3
1216 , P_ATTRIBUTE4 => csr_lp_rec.ATTRIBUTE4
1217 , P_ATTRIBUTE5 => csr_lp_rec.ATTRIBUTE5
1218 , P_ATTRIBUTE6 => csr_lp_rec.ATTRIBUTE6
1219 , P_ATTRIBUTE7 => csr_lp_rec.ATTRIBUTE7
1220 , P_ATTRIBUTE8 => csr_lp_rec.ATTRIBUTE8
1221 , P_ATTRIBUTE9 => csr_lp_rec.ATTRIBUTE9
1222 , P_ATTRIBUTE10 => csr_lp_rec.ATTRIBUTE10
1223 , P_ATTRIBUTE11 => csr_lp_rec.ATTRIBUTE11
1224 , P_ATTRIBUTE12 => csr_lp_rec.ATTRIBUTE12
1225 , P_ATTRIBUTE13 => csr_lp_rec.ATTRIBUTE13
1226 , P_ATTRIBUTE14 => csr_lp_rec.ATTRIBUTE14
1227 , P_ATTRIBUTE15 => csr_lp_rec.ATTRIBUTE15
1228 , P_ENABLED_FLAG => 'Y'
1229 , P_MEANING => csr_lp_rec.MEANING
1230 , P_DESCRIPTION => csr_lp_rec.DESCRIPTION
1231 , P_START_DATE_ACTIVE => g_plan_year_start_date
1232 , P_END_DATE_ACTIVE => g_plan_year_end_date
1233 );
1234 end loop;
1235
1236 hr_utility.SET_LOCATION('calling planned g_p_cert_lookup_type ' ,1);
1237 for csr_cert_rec in csr_cp_lookup_values
1238 ( g_p_cert_lookup_type
1239 , p_year
1240 , p_from_year
1241 )
1242 loop
1243 -- Check if the priority copying is available in the period
1244
1245 val_usr_row(csr_cert_rec.ATTRIBUTE1,p_year,p_from_year,errbuf,retcode);
1246 val_usr_row(csr_cert_rec.ATTRIBUTE2,p_year,p_from_year,errbuf,retcode);
1247 val_usr_row(csr_cert_rec.ATTRIBUTE3,p_year,p_from_year,errbuf,retcode);
1248 val_usr_row(csr_cert_rec.ATTRIBUTE4,p_year,p_from_year,errbuf,retcode);
1249 val_usr_row(csr_cert_rec.ATTRIBUTE5,p_year,p_from_year,errbuf,retcode);
1250 val_usr_row(csr_cert_rec.ATTRIBUTE6,p_year,p_from_year,errbuf,retcode);
1251 val_usr_row(csr_cert_rec.ATTRIBUTE7,p_year,p_from_year,errbuf,retcode);
1252 val_usr_row(csr_cert_rec.ATTRIBUTE8,p_year,p_from_year,errbuf,retcode);
1253 val_usr_row(csr_cert_rec.ATTRIBUTE9,p_year,p_from_year,errbuf,retcode);
1254 val_usr_row(csr_cert_rec.ATTRIBUTE10,p_year,p_from_year,errbuf,retcode);
1255 val_usr_row(csr_cert_rec.ATTRIBUTE11,p_year,p_from_year,errbuf,retcode);
1256 val_usr_row(csr_cert_rec.ATTRIBUTE12,p_year,p_from_year,errbuf,retcode);
1257 val_usr_row(csr_cert_rec.ATTRIBUTE13,p_year,p_from_year,errbuf,retcode);
1258 val_usr_row(csr_cert_rec.ATTRIBUTE14,p_year,p_from_year,errbuf,retcode);
1259 val_usr_row(csr_cert_rec.ATTRIBUTE15,p_year,p_from_year,errbuf,retcode);
1260
1261 LOOKUP_VAL_INSERT_ROW
1262 ( P_LOOKUP_TYPE => csr_cert_rec.LOOKUP_TYPE
1263 , P_LOOKUP_CODE => csr_cert_rec.LOOKUP_CODE
1264 , P_ATTRIBUTE1 => csr_cert_rec.ATTRIBUTE1
1265 , P_ATTRIBUTE2 => csr_cert_rec.ATTRIBUTE2
1266 , P_ATTRIBUTE3 => csr_cert_rec.ATTRIBUTE3
1267 , P_ATTRIBUTE4 => csr_cert_rec.ATTRIBUTE4
1268 , P_ATTRIBUTE5 => csr_cert_rec.ATTRIBUTE5
1269 , P_ATTRIBUTE6 => csr_cert_rec.ATTRIBUTE6
1270 , P_ATTRIBUTE7 => csr_cert_rec.ATTRIBUTE7
1271 , P_ATTRIBUTE8 => csr_cert_rec.ATTRIBUTE8
1272 , P_ATTRIBUTE9 => csr_cert_rec.ATTRIBUTE9
1273 , P_ATTRIBUTE10 => csr_cert_rec.ATTRIBUTE10
1274 , P_ATTRIBUTE11 => csr_cert_rec.ATTRIBUTE11
1275 , P_ATTRIBUTE12 => csr_cert_rec.ATTRIBUTE12
1276 , P_ATTRIBUTE13 => csr_cert_rec.ATTRIBUTE13
1277 , P_ATTRIBUTE14 => csr_cert_rec.ATTRIBUTE14
1278 , P_ATTRIBUTE15 => csr_cert_rec.ATTRIBUTE15
1279 , P_ENABLED_FLAG => 'Y'
1280 , P_MEANING => csr_cert_rec.MEANING
1281 , P_DESCRIPTION => csr_cert_rec.DESCRIPTION
1282 , P_START_DATE_ACTIVE => g_plan_year_start_date
1283 , P_END_DATE_ACTIVE => g_plan_year_end_date
1284 );
1285 end loop;
1286
1287 hr_utility.SET_LOCATION('calling planned g_p_course_lookup_type ' ,1);
1288
1289 for csr_crs_rec in csr_cp_lookup_values
1290 ( g_p_course_lookup_type
1291 , p_year
1292 , p_from_year
1293 )
1294 loop
1295
1296 val_usr_row(csr_crs_rec.ATTRIBUTE1,p_year,p_from_year,errbuf,retcode);
1297 val_usr_row(csr_crs_rec.ATTRIBUTE2,p_year,p_from_year,errbuf,retcode);
1298 val_usr_row(csr_crs_rec.ATTRIBUTE3,p_year,p_from_year,errbuf,retcode);
1299 val_usr_row(csr_crs_rec.ATTRIBUTE4,p_year,p_from_year,errbuf,retcode);
1300 val_usr_row(csr_crs_rec.ATTRIBUTE5,p_year,p_from_year,errbuf,retcode);
1301 val_usr_row(csr_crs_rec.ATTRIBUTE6,p_year,p_from_year,errbuf,retcode);
1302 val_usr_row(csr_crs_rec.ATTRIBUTE7,p_year,p_from_year,errbuf,retcode);
1303 val_usr_row(csr_crs_rec.ATTRIBUTE8,p_year,p_from_year,errbuf,retcode);
1304 val_usr_row(csr_crs_rec.ATTRIBUTE9,p_year,p_from_year,errbuf,retcode);
1305 val_usr_row(csr_crs_rec.ATTRIBUTE10,p_year,p_from_year,errbuf,retcode);
1306 val_usr_row(csr_crs_rec.ATTRIBUTE11,p_year,p_from_year,errbuf,retcode);
1307 val_usr_row(csr_crs_rec.ATTRIBUTE12,p_year,p_from_year,errbuf,retcode);
1308 val_usr_row(csr_crs_rec.ATTRIBUTE13,p_year,p_from_year,errbuf,retcode);
1309 val_usr_row(csr_crs_rec.ATTRIBUTE14,p_year,p_from_year,errbuf,retcode);
1310 val_usr_row(csr_crs_rec.ATTRIBUTE15,p_year,p_from_year,errbuf,retcode);
1311
1312 LOOKUP_VAL_INSERT_ROW
1313 ( P_LOOKUP_TYPE => csr_crs_rec.LOOKUP_TYPE
1314 , P_LOOKUP_CODE => csr_crs_rec.LOOKUP_CODE
1315 , P_ATTRIBUTE1 => csr_crs_rec.ATTRIBUTE1
1316 , P_ATTRIBUTE2 => csr_crs_rec.ATTRIBUTE2
1317 , P_ATTRIBUTE3 => csr_crs_rec.ATTRIBUTE3
1318 , P_ATTRIBUTE4 => csr_crs_rec.ATTRIBUTE4
1319 , P_ATTRIBUTE5 => csr_crs_rec.ATTRIBUTE5
1320 , P_ATTRIBUTE6 => csr_crs_rec.ATTRIBUTE6
1321 , P_ATTRIBUTE7 => csr_crs_rec.ATTRIBUTE7
1322 , P_ATTRIBUTE8 => csr_crs_rec.ATTRIBUTE8
1323 , P_ATTRIBUTE9 => csr_crs_rec.ATTRIBUTE9
1324 , P_ATTRIBUTE10 => csr_crs_rec.ATTRIBUTE10
1325 , P_ATTRIBUTE11 => csr_crs_rec.ATTRIBUTE11
1326 , P_ATTRIBUTE12 => csr_crs_rec.ATTRIBUTE12
1327 , P_ATTRIBUTE13 => csr_crs_rec.ATTRIBUTE13
1328 , P_ATTRIBUTE14 => csr_crs_rec.ATTRIBUTE14
1329 , P_ATTRIBUTE15 => csr_crs_rec.ATTRIBUTE15
1330 , P_ENABLED_FLAG => 'Y'
1331 , P_MEANING => csr_crs_rec.MEANING
1332 , P_DESCRIPTION => csr_crs_rec.DESCRIPTION
1333 , P_START_DATE_ACTIVE => g_plan_year_start_date
1334 , P_END_DATE_ACTIVE => g_plan_year_end_date
1335 );
1336 end loop;
1337 hr_utility.SET_LOCATION('After planned g_p_course_lookup_type ' ,1);
1338 hr_utility.SET_LOCATION('Calling planned create_lookup_values ' ,1);
1339 create_lookup_values
1340 (errbuf => errbuf
1341 , retcode => retcode
1342 , p_year => p_year
1343 , p_plan_trng_ind => '20'
1344 , p_del_mode => 'N' -- N do not delete the existing lookup values
1345 );
1346 end if;
1347 if p_plan_trng_ind = '30' OR p_plan_trng_ind = '10' then
1348
1349 tab_usr_row_ids.delete;
1350
1351 hr_utility.SET_LOCATION('calling Completed g_t_lpath_lookup_type ' ,1);
1352
1353 for csr_lp_rec in csr_cp_lookup_values
1354 ( g_t_lpath_lookup_type
1355 , p_year - 1
1356 , p_from_year - 1
1357 )
1358 loop
1359 val_usr_row(csr_lp_rec.ATTRIBUTE1,p_year-1,p_from_year-1,errbuf,retcode);
1360 val_usr_row(csr_lp_rec.ATTRIBUTE2,p_year-1,p_from_year-1,errbuf,retcode);
1361 val_usr_row(csr_lp_rec.ATTRIBUTE3,p_year-1,p_from_year-1,errbuf,retcode);
1362 val_usr_row(csr_lp_rec.ATTRIBUTE4,p_year-1,p_from_year-1,errbuf,retcode);
1363 val_usr_row(csr_lp_rec.ATTRIBUTE5,p_year-1,p_from_year-1,errbuf,retcode);
1364 val_usr_row(csr_lp_rec.ATTRIBUTE6,p_year-1,p_from_year-1,errbuf,retcode);
1365 val_usr_row(csr_lp_rec.ATTRIBUTE7,p_year-1,p_from_year-1,errbuf,retcode);
1366 val_usr_row(csr_lp_rec.ATTRIBUTE8,p_year-1,p_from_year-1,errbuf,retcode);
1367 val_usr_row(csr_lp_rec.ATTRIBUTE9,p_year-1,p_from_year-1,errbuf,retcode);
1368 val_usr_row(csr_lp_rec.ATTRIBUTE10,p_year-1,p_from_year-1,errbuf,retcode);
1369 val_usr_row(csr_lp_rec.ATTRIBUTE11,p_year-1,p_from_year-1,errbuf,retcode);
1370 val_usr_row(csr_lp_rec.ATTRIBUTE12,p_year-1,p_from_year-1,errbuf,retcode);
1371 val_usr_row(csr_lp_rec.ATTRIBUTE13,p_year-1,p_from_year-1,errbuf,retcode);
1372 val_usr_row(csr_lp_rec.ATTRIBUTE14,p_year-1,p_from_year-1,errbuf,retcode);
1373 val_usr_row(csr_lp_rec.ATTRIBUTE15,p_year-1,p_from_year-1,errbuf,retcode);
1374
1375 LOOKUP_VAL_INSERT_ROW
1376 ( P_LOOKUP_TYPE => csr_lp_rec.LOOKUP_TYPE
1377 , P_LOOKUP_CODE => csr_lp_rec.LOOKUP_CODE
1378 , P_ATTRIBUTE1 => csr_lp_rec.ATTRIBUTE1
1379 , P_ATTRIBUTE2 => csr_lp_rec.ATTRIBUTE2
1380 , P_ATTRIBUTE3 => csr_lp_rec.ATTRIBUTE3
1381 , P_ATTRIBUTE4 => csr_lp_rec.ATTRIBUTE4
1382 , P_ATTRIBUTE5 => csr_lp_rec.ATTRIBUTE5
1383 , P_ATTRIBUTE6 => csr_lp_rec.ATTRIBUTE6
1384 , P_ATTRIBUTE7 => csr_lp_rec.ATTRIBUTE7
1385 , P_ATTRIBUTE8 => csr_lp_rec.ATTRIBUTE8
1386 , P_ATTRIBUTE9 => csr_lp_rec.ATTRIBUTE9
1387 , P_ATTRIBUTE10 => csr_lp_rec.ATTRIBUTE10
1388 , P_ATTRIBUTE11 => csr_lp_rec.ATTRIBUTE11
1389 , P_ATTRIBUTE12 => csr_lp_rec.ATTRIBUTE12
1390 , P_ATTRIBUTE13 => csr_lp_rec.ATTRIBUTE13
1391 , P_ATTRIBUTE14 => csr_lp_rec.ATTRIBUTE14
1392 , P_ATTRIBUTE15 => csr_lp_rec.ATTRIBUTE15
1393 , P_ENABLED_FLAG => 'Y'
1394 , P_MEANING => csr_lp_rec.MEANING
1395 , P_DESCRIPTION => csr_lp_rec.DESCRIPTION
1396 , P_START_DATE_ACTIVE => g_trnd_year_start_date
1397 , P_END_DATE_ACTIVE => g_trnd_year_end_date
1398 );
1399 end loop;
1400
1401 hr_utility.SET_LOCATION('calling trained g_t_course_lookup_type ' ,1);
1402
1403 for csr_cert_rec in csr_cp_lookup_values
1404 ( g_t_course_lookup_type
1405 , p_year - 1
1406 , p_from_year - 1
1407 )
1408 loop
1409
1410 val_usr_row(csr_cert_rec.ATTRIBUTE1,p_year-1,p_from_year-1,errbuf,retcode);
1411 val_usr_row(csr_cert_rec.ATTRIBUTE2,p_year-1,p_from_year-1,errbuf,retcode);
1412 val_usr_row(csr_cert_rec.ATTRIBUTE3,p_year-1,p_from_year-1,errbuf,retcode);
1413 val_usr_row(csr_cert_rec.ATTRIBUTE4,p_year-1,p_from_year-1,errbuf,retcode);
1414 val_usr_row(csr_cert_rec.ATTRIBUTE5,p_year-1,p_from_year-1,errbuf,retcode);
1415 val_usr_row(csr_cert_rec.ATTRIBUTE6,p_year-1,p_from_year-1,errbuf,retcode);
1416 val_usr_row(csr_cert_rec.ATTRIBUTE7,p_year-1,p_from_year-1,errbuf,retcode);
1417 val_usr_row(csr_cert_rec.ATTRIBUTE8,p_year-1,p_from_year-1,errbuf,retcode);
1418 val_usr_row(csr_cert_rec.ATTRIBUTE9,p_year-1,p_from_year-1,errbuf,retcode);
1419 val_usr_row(csr_cert_rec.ATTRIBUTE10,p_year-1,p_from_year-1,errbuf,retcode);
1420 val_usr_row(csr_cert_rec.ATTRIBUTE11,p_year-1,p_from_year-1,errbuf,retcode);
1421 val_usr_row(csr_cert_rec.ATTRIBUTE12,p_year-1,p_from_year-1,errbuf,retcode);
1422 val_usr_row(csr_cert_rec.ATTRIBUTE13,p_year-1,p_from_year-1,errbuf,retcode);
1423 val_usr_row(csr_cert_rec.ATTRIBUTE14,p_year-1,p_from_year-1,errbuf,retcode);
1424 val_usr_row(csr_cert_rec.ATTRIBUTE15,p_year-1,p_from_year-1,errbuf,retcode);
1425
1426 hr_utility.SET_LOCATION('calling trained LOOKUP_VAL_INSERT_ROW ' ,1);
1427
1428 LOOKUP_VAL_INSERT_ROW
1429 ( P_LOOKUP_TYPE => csr_cert_rec.LOOKUP_TYPE
1430 , P_LOOKUP_CODE => csr_cert_rec.LOOKUP_CODE
1431 , P_ATTRIBUTE1 => csr_cert_rec.ATTRIBUTE1
1432 , P_ATTRIBUTE2 => csr_cert_rec.ATTRIBUTE2
1433 , P_ATTRIBUTE3 => csr_cert_rec.ATTRIBUTE3
1434 , P_ATTRIBUTE4 => csr_cert_rec.ATTRIBUTE4
1435 , P_ATTRIBUTE5 => csr_cert_rec.ATTRIBUTE5
1436 , P_ATTRIBUTE6 => csr_cert_rec.ATTRIBUTE6
1437 , P_ATTRIBUTE7 => csr_cert_rec.ATTRIBUTE7
1438 , P_ATTRIBUTE8 => csr_cert_rec.ATTRIBUTE8
1439 , P_ATTRIBUTE9 => csr_cert_rec.ATTRIBUTE9
1440 , P_ATTRIBUTE10 => csr_cert_rec.ATTRIBUTE10
1441 , P_ATTRIBUTE11 => csr_cert_rec.ATTRIBUTE11
1442 , P_ATTRIBUTE12 => csr_cert_rec.ATTRIBUTE12
1443 , P_ATTRIBUTE13 => csr_cert_rec.ATTRIBUTE13
1444 , P_ATTRIBUTE14 => csr_cert_rec.ATTRIBUTE14
1445 , P_ATTRIBUTE15 => csr_cert_rec.ATTRIBUTE15
1446 , P_ENABLED_FLAG => 'Y'
1447 , P_MEANING => csr_cert_rec.MEANING
1448 , P_DESCRIPTION => csr_cert_rec.DESCRIPTION
1449 , P_START_DATE_ACTIVE => g_trnd_year_start_date
1450 , P_END_DATE_ACTIVE => g_trnd_year_end_date
1451 );
1452 end loop;
1453
1454 hr_utility.SET_LOCATION('calling trained g_t_cert_lookup_type ' ,1);
1455
1456 for csr_crs_rec in csr_cp_lookup_values
1457 ( g_t_cert_lookup_type
1458 , p_year - 1
1459 , p_from_year - 1
1460 )
1461 loop
1462
1463 val_usr_row(csr_crs_rec.ATTRIBUTE1,p_year-1,p_from_year-1,errbuf,retcode);
1464 val_usr_row(csr_crs_rec.ATTRIBUTE2,p_year-1,p_from_year-1,errbuf,retcode);
1465 val_usr_row(csr_crs_rec.ATTRIBUTE3,p_year-1,p_from_year-1,errbuf,retcode);
1466 val_usr_row(csr_crs_rec.ATTRIBUTE4,p_year-1,p_from_year-1,errbuf,retcode);
1467 val_usr_row(csr_crs_rec.ATTRIBUTE5,p_year-1,p_from_year-1,errbuf,retcode);
1468 val_usr_row(csr_crs_rec.ATTRIBUTE6,p_year-1,p_from_year-1,errbuf,retcode);
1469 val_usr_row(csr_crs_rec.ATTRIBUTE7,p_year-1,p_from_year-1,errbuf,retcode);
1470 val_usr_row(csr_crs_rec.ATTRIBUTE8,p_year-1,p_from_year-1,errbuf,retcode);
1471 val_usr_row(csr_crs_rec.ATTRIBUTE9,p_year-1,p_from_year-1,errbuf,retcode);
1472 val_usr_row(csr_crs_rec.ATTRIBUTE10,p_year-1,p_from_year-1,errbuf,retcode);
1473 val_usr_row(csr_crs_rec.ATTRIBUTE11,p_year-1,p_from_year-1,errbuf,retcode);
1474 val_usr_row(csr_crs_rec.ATTRIBUTE12,p_year-1,p_from_year-1,errbuf,retcode);
1475 val_usr_row(csr_crs_rec.ATTRIBUTE13,p_year-1,p_from_year-1,errbuf,retcode);
1476 val_usr_row(csr_crs_rec.ATTRIBUTE14,p_year-1,p_from_year-1,errbuf,retcode);
1477 val_usr_row(csr_crs_rec.ATTRIBUTE15,p_year-1,p_from_year-1,errbuf,retcode);
1478
1479 LOOKUP_VAL_INSERT_ROW
1480 ( P_LOOKUP_TYPE => csr_crs_rec.LOOKUP_TYPE
1481 , P_LOOKUP_CODE => csr_crs_rec.LOOKUP_CODE
1482 , P_ATTRIBUTE1 => csr_crs_rec.ATTRIBUTE1
1483 , P_ATTRIBUTE2 => csr_crs_rec.ATTRIBUTE2
1484 , P_ATTRIBUTE3 => csr_crs_rec.ATTRIBUTE3
1485 , P_ATTRIBUTE4 => csr_crs_rec.ATTRIBUTE4
1486 , P_ATTRIBUTE5 => csr_crs_rec.ATTRIBUTE5
1487 , P_ATTRIBUTE6 => csr_crs_rec.ATTRIBUTE6
1488 , P_ATTRIBUTE7 => csr_crs_rec.ATTRIBUTE7
1489 , P_ATTRIBUTE8 => csr_crs_rec.ATTRIBUTE8
1490 , P_ATTRIBUTE9 => csr_crs_rec.ATTRIBUTE9
1491 , P_ATTRIBUTE10 => csr_crs_rec.ATTRIBUTE10
1492 , P_ATTRIBUTE11 => csr_crs_rec.ATTRIBUTE11
1493 , P_ATTRIBUTE12 => csr_crs_rec.ATTRIBUTE12
1494 , P_ATTRIBUTE13 => csr_crs_rec.ATTRIBUTE13
1495 , P_ATTRIBUTE14 => csr_crs_rec.ATTRIBUTE14
1496 , P_ATTRIBUTE15 => csr_crs_rec.ATTRIBUTE15
1497 , P_ENABLED_FLAG => 'Y'
1498 , P_MEANING => csr_crs_rec.MEANING
1499 , P_DESCRIPTION => csr_crs_rec.DESCRIPTION
1500 , P_START_DATE_ACTIVE => g_trnd_year_start_date
1501 , P_END_DATE_ACTIVE => g_trnd_year_end_date
1502 );
1503 end loop;
1504
1505 hr_utility.SET_LOCATION('calling trained g_t_comp_lookup_type ' ,1);
1506
1507 for csr_comp_rec in csr_cp_lookup_values
1508 ( g_t_comp_lookup_type
1509 , p_year - 1
1510 , p_from_year - 1
1511 )
1512 loop
1513
1514 val_usr_row(csr_comp_rec.ATTRIBUTE1,p_year-1,p_from_year-1,errbuf,retcode);
1515 val_usr_row(csr_comp_rec.ATTRIBUTE2,p_year-1,p_from_year-1,errbuf,retcode);
1516 val_usr_row(csr_comp_rec.ATTRIBUTE3,p_year-1,p_from_year-1,errbuf,retcode);
1517 val_usr_row(csr_comp_rec.ATTRIBUTE4,p_year-1,p_from_year-1,errbuf,retcode);
1518 val_usr_row(csr_comp_rec.ATTRIBUTE5,p_year-1,p_from_year-1,errbuf,retcode);
1519 val_usr_row(csr_comp_rec.ATTRIBUTE6,p_year-1,p_from_year-1,errbuf,retcode);
1520 val_usr_row(csr_comp_rec.ATTRIBUTE7,p_year-1,p_from_year-1,errbuf,retcode);
1521 val_usr_row(csr_comp_rec.ATTRIBUTE8,p_year-1,p_from_year-1,errbuf,retcode);
1522 val_usr_row(csr_comp_rec.ATTRIBUTE9,p_year-1,p_from_year-1,errbuf,retcode);
1523 val_usr_row(csr_comp_rec.ATTRIBUTE10,p_year-1,p_from_year-1,errbuf,retcode);
1524 val_usr_row(csr_comp_rec.ATTRIBUTE11,p_year-1,p_from_year-1,errbuf,retcode);
1525 val_usr_row(csr_comp_rec.ATTRIBUTE12,p_year-1,p_from_year-1,errbuf,retcode);
1526 val_usr_row(csr_comp_rec.ATTRIBUTE13,p_year-1,p_from_year-1,errbuf,retcode);
1527 val_usr_row(csr_comp_rec.ATTRIBUTE14,p_year-1,p_from_year-1,errbuf,retcode);
1528 val_usr_row(csr_comp_rec.ATTRIBUTE15,p_year-1,p_from_year-1,errbuf,retcode);
1529
1530 LOOKUP_VAL_INSERT_ROW
1531 ( P_LOOKUP_TYPE => csr_comp_rec.LOOKUP_TYPE
1532 , P_LOOKUP_CODE => csr_comp_rec.LOOKUP_CODE
1533 , P_ATTRIBUTE1 => csr_comp_rec.ATTRIBUTE1
1534 , P_ATTRIBUTE2 => csr_comp_rec.ATTRIBUTE2
1535 , P_ATTRIBUTE3 => csr_comp_rec.ATTRIBUTE3
1536 , P_ATTRIBUTE4 => csr_comp_rec.ATTRIBUTE4
1537 , P_ATTRIBUTE5 => csr_comp_rec.ATTRIBUTE5
1538 , P_ATTRIBUTE6 => csr_comp_rec.ATTRIBUTE6
1539 , P_ATTRIBUTE7 => csr_comp_rec.ATTRIBUTE7
1540 , P_ATTRIBUTE8 => csr_comp_rec.ATTRIBUTE8
1541 , P_ATTRIBUTE9 => csr_comp_rec.ATTRIBUTE9
1542 , P_ATTRIBUTE10 => csr_comp_rec.ATTRIBUTE10
1543 , P_ATTRIBUTE11 => csr_comp_rec.ATTRIBUTE11
1544 , P_ATTRIBUTE12 => csr_comp_rec.ATTRIBUTE12
1545 , P_ATTRIBUTE13 => csr_comp_rec.ATTRIBUTE13
1546 , P_ATTRIBUTE14 => csr_comp_rec.ATTRIBUTE14
1547 , P_ATTRIBUTE15 => csr_comp_rec.ATTRIBUTE15
1548 , P_ENABLED_FLAG => 'Y'
1549 , P_MEANING => csr_comp_rec.MEANING
1550 , P_DESCRIPTION => csr_comp_rec.DESCRIPTION
1551 , P_START_DATE_ACTIVE => g_trnd_year_start_date
1552 , P_END_DATE_ACTIVE => g_trnd_year_end_date
1553 );
1554 end loop;
1555
1556 hr_utility.SET_LOCATION('calling trained g_t_qual_lookup_type ' ,1);
1557
1558 for csr_qual_rec in csr_cp_lookup_values
1559 ( g_t_qual_lookup_type
1560 , p_year - 1
1561 , p_from_year - 1
1562 )
1563 loop
1564
1565 val_usr_row(csr_qual_rec.ATTRIBUTE1,p_year-1,p_from_year-1,errbuf,retcode);
1566 val_usr_row(csr_qual_rec.ATTRIBUTE2,p_year-1,p_from_year-1,errbuf,retcode);
1567 val_usr_row(csr_qual_rec.ATTRIBUTE3,p_year-1,p_from_year-1,errbuf,retcode);
1568 val_usr_row(csr_qual_rec.ATTRIBUTE4,p_year-1,p_from_year-1,errbuf,retcode);
1569 val_usr_row(csr_qual_rec.ATTRIBUTE5,p_year-1,p_from_year-1,errbuf,retcode);
1570 val_usr_row(csr_qual_rec.ATTRIBUTE6,p_year-1,p_from_year-1,errbuf,retcode);
1571 val_usr_row(csr_qual_rec.ATTRIBUTE7,p_year-1,p_from_year-1,errbuf,retcode);
1572 val_usr_row(csr_qual_rec.ATTRIBUTE8,p_year-1,p_from_year-1,errbuf,retcode);
1573 val_usr_row(csr_qual_rec.ATTRIBUTE9,p_year-1,p_from_year-1,errbuf,retcode);
1574 val_usr_row(csr_qual_rec.ATTRIBUTE10,p_year-1,p_from_year-1,errbuf,retcode);
1575 val_usr_row(csr_qual_rec.ATTRIBUTE11,p_year-1,p_from_year-1,errbuf,retcode);
1576 val_usr_row(csr_qual_rec.ATTRIBUTE12,p_year-1,p_from_year-1,errbuf,retcode);
1577 val_usr_row(csr_qual_rec.ATTRIBUTE13,p_year-1,p_from_year-1,errbuf,retcode);
1578 val_usr_row(csr_qual_rec.ATTRIBUTE14,p_year-1,p_from_year-1,errbuf,retcode);
1579 val_usr_row(csr_qual_rec.ATTRIBUTE15,p_year-1,p_from_year-1,errbuf,retcode);
1580
1581 LOOKUP_VAL_INSERT_ROW
1582 ( P_LOOKUP_TYPE => csr_qual_rec.LOOKUP_TYPE
1583 , P_LOOKUP_CODE => csr_qual_rec.LOOKUP_CODE
1584 , P_ATTRIBUTE1 => csr_qual_rec.ATTRIBUTE1
1585 , P_ATTRIBUTE2 => csr_qual_rec.ATTRIBUTE2
1586 , P_ATTRIBUTE3 => csr_qual_rec.ATTRIBUTE3
1587 , P_ATTRIBUTE4 => csr_qual_rec.ATTRIBUTE4
1588 , P_ATTRIBUTE5 => csr_qual_rec.ATTRIBUTE5
1589 , P_ATTRIBUTE6 => csr_qual_rec.ATTRIBUTE6
1590 , P_ATTRIBUTE7 => csr_qual_rec.ATTRIBUTE7
1591 , P_ATTRIBUTE8 => csr_qual_rec.ATTRIBUTE8
1592 , P_ATTRIBUTE9 => csr_qual_rec.ATTRIBUTE9
1593 , P_ATTRIBUTE10 => csr_qual_rec.ATTRIBUTE10
1594 , P_ATTRIBUTE11 => csr_qual_rec.ATTRIBUTE11
1595 , P_ATTRIBUTE12 => csr_qual_rec.ATTRIBUTE12
1596 , P_ATTRIBUTE13 => csr_qual_rec.ATTRIBUTE13
1597 , P_ATTRIBUTE14 => csr_qual_rec.ATTRIBUTE14
1598 , P_ATTRIBUTE15 => csr_qual_rec.ATTRIBUTE15
1599 , P_ENABLED_FLAG => 'Y'
1600 , P_MEANING => csr_qual_rec.MEANING
1601 , P_DESCRIPTION => csr_qual_rec.DESCRIPTION
1602 , P_START_DATE_ACTIVE => g_trnd_year_start_date
1603 , P_END_DATE_ACTIVE => g_trnd_year_end_date
1604 );
1605 end loop;
1606 create_lookup_values
1607 (errbuf => errbuf
1608 , retcode => retcode
1609 , p_year => p_year
1610 , p_plan_trng_ind => '30'
1611 , p_del_mode => 'N' -- N do not delete the existing lookup values
1612 );
1613 end if;
1614 end if;
1615 tab_usr_row_ids.delete;
1616 End copy_lookup_values;
1617
1618
1619 /****************************************************************************
1620 Name : copy_plan_2_trining
1621 Description : If the parameter passed to the concurrent programe is Copy
1622 plan to trained this procedure will be called.
1623 It copies the lookup along with priorities from already
1624 created plan lookups to trained one.
1625 *****************************************************************************/
1626
1627 Procedure copy_plan_2_trining
1628 (errbuf out nocopy varchar2,
1629 retcode out nocopy number,
1630 p_year in number,
1631 P_from_year in number)
1632 is
1633 l_count number;
1634 Cursor csr_cp_plan_2_train
1635 ( l_lookup_type in varchar2
1636 ) is
1637 select p_year -1 || substr(lookup_code,5) lookup_code
1638 , lookup_type
1639 , LANGUAGE
1640 , MEANING
1641 , DESCRIPTION
1642 , ENABLED_FLAG
1643 , g_trnd_year_start_date START_DATE_ACTIVE
1644 , g_trnd_year_end_date END_DATE_ACTIVE
1645 , SOURCE_LANG
1646 , SECURITY_GROUP_ID
1647 , VIEW_APPLICATION_ID
1648 , TERRITORY_CODE
1649 , ATTRIBUTE_CATEGORY
1650 , ATTRIBUTE1
1651 , ATTRIBUTE2
1652 , ATTRIBUTE3
1653 , ATTRIBUTE4
1654 , ATTRIBUTE5
1655 , ATTRIBUTE6
1656 , ATTRIBUTE7
1657 , ATTRIBUTE8
1658 , ATTRIBUTE9
1659 , ATTRIBUTE10
1660 , ATTRIBUTE11
1661 , ATTRIBUTE12
1662 , ATTRIBUTE13
1663 , ATTRIBUTE14
1664 , ATTRIBUTE15
1665 from fnd_lookup_values
1666 Where lookup_type = l_lookup_type
1667 and substr(lookup_code,1,4) = to_char(P_from_year)
1668 And security_group_id = fnd_global.lookup_security_group(l_lookup_type,3)
1669 and (ATTRIBUTE1 ||ATTRIBUTE2 ||ATTRIBUTE3 ||ATTRIBUTE4 ||
1670 ATTRIBUTE5 ||ATTRIBUTE6 ||ATTRIBUTE7 ||ATTRIBUTE8 ||
1671 ATTRIBUTE9 ||ATTRIBUTE10||ATTRIBUTE11||ATTRIBUTE12||
1672 ATTRIBUTE13||ATTRIBUTE14||ATTRIBUTE15) is not null;
1673
1674 begin
1675 hr_utility.set_location ('Inside copy_plan_2_trining ' ,1);
1676 hr_utility.set_location ('p_year ' || p_year,1);
1677 hr_utility.set_location ('P_from_year ' || P_from_year ,1);
1678
1679
1680 select count(*) into l_count
1681 from fnd_lookup_values
1682 Where lookup_type in
1683 (
1684 g_t_lpath_lookup_type
1685 , g_t_course_lookup_type
1686 , g_t_cert_lookup_type
1687 )
1688 And security_group_id = fnd_global.lookup_security_group(lookup_type,3)
1689 and substr(lookup_code,1,4) = to_char(p_year-1);
1690
1691 hr_utility.set_location ('l_count ' || l_count ,2);
1692
1693 if l_count = 0 then
1694 tab_usr_row_ids.delete;
1695 for plan_2_train_rec in csr_cp_plan_2_train
1696 (g_p_lpath_lookup_type
1697 )
1698 loop
1699 hr_utility.set_location ('Inside g_p_lpath_lookup_type ' ,1);
1700
1701 val_usr_row(plan_2_train_rec.ATTRIBUTE1,p_year - 1,p_from_year,errbuf,retcode);
1702 val_usr_row(plan_2_train_rec.ATTRIBUTE2,p_year - 1,p_from_year,errbuf,retcode);
1703 val_usr_row(plan_2_train_rec.ATTRIBUTE3,p_year - 1,p_from_year,errbuf,retcode);
1704 val_usr_row(plan_2_train_rec.ATTRIBUTE4,p_year - 1,p_from_year,errbuf,retcode);
1705 val_usr_row(plan_2_train_rec.ATTRIBUTE5,p_year - 1,p_from_year,errbuf,retcode);
1706 val_usr_row(plan_2_train_rec.ATTRIBUTE6,p_year - 1,p_from_year,errbuf,retcode);
1707 val_usr_row(plan_2_train_rec.ATTRIBUTE7,p_year - 1,p_from_year,errbuf,retcode);
1708 val_usr_row(plan_2_train_rec.ATTRIBUTE8,p_year - 1,p_from_year,errbuf,retcode);
1709 val_usr_row(plan_2_train_rec.ATTRIBUTE9,p_year - 1,p_from_year,errbuf,retcode);
1710 val_usr_row(plan_2_train_rec.ATTRIBUTE10,p_year - 1,p_from_year,errbuf,retcode);
1711 val_usr_row(plan_2_train_rec.ATTRIBUTE11,p_year - 1,p_from_year,errbuf,retcode);
1712 val_usr_row(plan_2_train_rec.ATTRIBUTE12,p_year - 1,p_from_year,errbuf,retcode);
1713 val_usr_row(plan_2_train_rec.ATTRIBUTE13,p_year - 1,p_from_year,errbuf,retcode);
1714 val_usr_row(plan_2_train_rec.ATTRIBUTE14,p_year - 1,p_from_year,errbuf,retcode);
1715 val_usr_row(plan_2_train_rec.ATTRIBUTE15,p_year - 1,p_from_year,errbuf,retcode);
1716
1717 LOOKUP_VAL_INSERT_ROW
1718 ( P_LOOKUP_TYPE => g_t_lpath_lookup_type
1719 , P_LOOKUP_CODE => plan_2_train_rec.LOOKUP_CODE
1720 , P_ATTRIBUTE1 => plan_2_train_rec.ATTRIBUTE1
1721 , P_ATTRIBUTE2 => plan_2_train_rec.ATTRIBUTE2
1722 , P_ATTRIBUTE3 => plan_2_train_rec.ATTRIBUTE3
1723 , P_ATTRIBUTE4 => plan_2_train_rec.ATTRIBUTE4
1724 , P_ATTRIBUTE5 => plan_2_train_rec.ATTRIBUTE5
1725 , P_ATTRIBUTE6 => plan_2_train_rec.ATTRIBUTE6
1726 , P_ATTRIBUTE7 => plan_2_train_rec.ATTRIBUTE7
1727 , P_ATTRIBUTE8 => plan_2_train_rec.ATTRIBUTE8
1728 , P_ATTRIBUTE9 => plan_2_train_rec.ATTRIBUTE9
1729 , P_ATTRIBUTE10 => plan_2_train_rec.ATTRIBUTE10
1730 , P_ATTRIBUTE11 => plan_2_train_rec.ATTRIBUTE11
1731 , P_ATTRIBUTE12 => plan_2_train_rec.ATTRIBUTE12
1732 , P_ATTRIBUTE13 => plan_2_train_rec.ATTRIBUTE13
1733 , P_ATTRIBUTE14 => plan_2_train_rec.ATTRIBUTE14
1734 , P_ATTRIBUTE15 => plan_2_train_rec.ATTRIBUTE15
1735 , P_ENABLED_FLAG => 'Y'
1736 , P_MEANING => plan_2_train_rec.MEANING
1737 , P_DESCRIPTION => plan_2_train_rec.DESCRIPTION
1738 , P_START_DATE_ACTIVE => plan_2_train_rec.START_DATE_ACTIVE
1739 , P_END_DATE_ACTIVE => plan_2_train_rec.END_DATE_ACTIVE
1740 );
1741
1742
1743 end loop;
1744
1745 for plan_2_train_rec in csr_cp_plan_2_train
1746 (g_p_course_lookup_type
1747 )
1748 loop
1749 hr_utility.set_location ('Inside g_p_course_lookup_type ' ,1);
1750 val_usr_row(plan_2_train_rec.ATTRIBUTE1,p_year - 1,p_from_year,errbuf,retcode);
1751 val_usr_row(plan_2_train_rec.ATTRIBUTE2,p_year - 1,p_from_year,errbuf,retcode);
1752 val_usr_row(plan_2_train_rec.ATTRIBUTE3,p_year - 1,p_from_year,errbuf,retcode);
1753 val_usr_row(plan_2_train_rec.ATTRIBUTE4,p_year - 1,p_from_year,errbuf,retcode);
1754 val_usr_row(plan_2_train_rec.ATTRIBUTE5,p_year - 1,p_from_year,errbuf,retcode);
1755 val_usr_row(plan_2_train_rec.ATTRIBUTE6,p_year - 1,p_from_year,errbuf,retcode);
1756 val_usr_row(plan_2_train_rec.ATTRIBUTE7,p_year - 1,p_from_year,errbuf,retcode);
1757 val_usr_row(plan_2_train_rec.ATTRIBUTE8,p_year - 1,p_from_year,errbuf,retcode);
1758 val_usr_row(plan_2_train_rec.ATTRIBUTE9,p_year - 1,p_from_year,errbuf,retcode);
1759 val_usr_row(plan_2_train_rec.ATTRIBUTE10,p_year - 1,p_from_year,errbuf,retcode);
1760 val_usr_row(plan_2_train_rec.ATTRIBUTE11,p_year - 1,p_from_year,errbuf,retcode);
1761 val_usr_row(plan_2_train_rec.ATTRIBUTE12,p_year - 1,p_from_year,errbuf,retcode);
1762 val_usr_row(plan_2_train_rec.ATTRIBUTE13,p_year - 1,p_from_year,errbuf,retcode);
1763 val_usr_row(plan_2_train_rec.ATTRIBUTE14,p_year - 1,p_from_year,errbuf,retcode);
1764 val_usr_row(plan_2_train_rec.ATTRIBUTE15,p_year - 1,p_from_year,errbuf,retcode);
1765
1766 LOOKUP_VAL_INSERT_ROW
1767 ( P_LOOKUP_TYPE => g_t_course_lookup_type
1768 , P_LOOKUP_CODE => plan_2_train_rec.LOOKUP_CODE
1769 , P_ATTRIBUTE1 => plan_2_train_rec.ATTRIBUTE1
1770 , P_ATTRIBUTE2 => plan_2_train_rec.ATTRIBUTE2
1771 , P_ATTRIBUTE3 => plan_2_train_rec.ATTRIBUTE3
1772 , P_ATTRIBUTE4 => plan_2_train_rec.ATTRIBUTE4
1773 , P_ATTRIBUTE5 => plan_2_train_rec.ATTRIBUTE5
1774 , P_ATTRIBUTE6 => plan_2_train_rec.ATTRIBUTE6
1775 , P_ATTRIBUTE7 => plan_2_train_rec.ATTRIBUTE7
1776 , P_ATTRIBUTE8 => plan_2_train_rec.ATTRIBUTE8
1777 , P_ATTRIBUTE9 => plan_2_train_rec.ATTRIBUTE9
1778 , P_ATTRIBUTE10 => plan_2_train_rec.ATTRIBUTE10
1779 , P_ATTRIBUTE11 => plan_2_train_rec.ATTRIBUTE11
1780 , P_ATTRIBUTE12 => plan_2_train_rec.ATTRIBUTE12
1781 , P_ATTRIBUTE13 => plan_2_train_rec.ATTRIBUTE13
1782 , P_ATTRIBUTE14 => plan_2_train_rec.ATTRIBUTE14
1783 , P_ATTRIBUTE15 => plan_2_train_rec.ATTRIBUTE15
1784 , P_ENABLED_FLAG => 'Y'
1785 , P_MEANING => plan_2_train_rec.MEANING
1786 , P_DESCRIPTION => plan_2_train_rec.DESCRIPTION
1787 , P_START_DATE_ACTIVE => plan_2_train_rec.START_DATE_ACTIVE
1788 , P_END_DATE_ACTIVE => plan_2_train_rec.END_DATE_ACTIVE
1789 );
1790
1791
1792 end loop;
1793 for plan_2_train_rec in csr_cp_plan_2_train
1794 (g_p_cert_lookup_type
1795 )
1796 loop
1797 hr_utility.set_location ('Inside g_p_cert_lookup_type ' ,1);
1798 val_usr_row(plan_2_train_rec.ATTRIBUTE1,p_year - 1,p_from_year,errbuf,retcode);
1799 val_usr_row(plan_2_train_rec.ATTRIBUTE2,p_year - 1,p_from_year,errbuf,retcode);
1800 val_usr_row(plan_2_train_rec.ATTRIBUTE3,p_year - 1,p_from_year,errbuf,retcode);
1801 val_usr_row(plan_2_train_rec.ATTRIBUTE4,p_year - 1,p_from_year,errbuf,retcode);
1802 val_usr_row(plan_2_train_rec.ATTRIBUTE5,p_year - 1,p_from_year,errbuf,retcode);
1803 val_usr_row(plan_2_train_rec.ATTRIBUTE6,p_year - 1,p_from_year,errbuf,retcode);
1804 val_usr_row(plan_2_train_rec.ATTRIBUTE7,p_year - 1,p_from_year,errbuf,retcode);
1805 val_usr_row(plan_2_train_rec.ATTRIBUTE8,p_year - 1,p_from_year,errbuf,retcode);
1806 val_usr_row(plan_2_train_rec.ATTRIBUTE9,p_year - 1,p_from_year,errbuf,retcode);
1807 val_usr_row(plan_2_train_rec.ATTRIBUTE10,p_year - 1,p_from_year,errbuf,retcode);
1808 val_usr_row(plan_2_train_rec.ATTRIBUTE11,p_year - 1,p_from_year,errbuf,retcode);
1809 val_usr_row(plan_2_train_rec.ATTRIBUTE12,p_year - 1,p_from_year,errbuf,retcode);
1810 val_usr_row(plan_2_train_rec.ATTRIBUTE13,p_year - 1,p_from_year,errbuf,retcode);
1811 val_usr_row(plan_2_train_rec.ATTRIBUTE14,p_year - 1,p_from_year,errbuf,retcode);
1812 val_usr_row(plan_2_train_rec.ATTRIBUTE15,p_year - 1,p_from_year,errbuf,retcode);
1813
1814 LOOKUP_VAL_INSERT_ROW
1815 ( P_LOOKUP_TYPE => g_t_cert_lookup_type
1816 , P_LOOKUP_CODE => plan_2_train_rec.LOOKUP_CODE
1817 , P_ATTRIBUTE1 => plan_2_train_rec.ATTRIBUTE1
1818 , P_ATTRIBUTE2 => plan_2_train_rec.ATTRIBUTE2
1819 , P_ATTRIBUTE3 => plan_2_train_rec.ATTRIBUTE3
1820 , P_ATTRIBUTE4 => plan_2_train_rec.ATTRIBUTE4
1821 , P_ATTRIBUTE5 => plan_2_train_rec.ATTRIBUTE5
1822 , P_ATTRIBUTE6 => plan_2_train_rec.ATTRIBUTE6
1823 , P_ATTRIBUTE7 => plan_2_train_rec.ATTRIBUTE7
1824 , P_ATTRIBUTE8 => plan_2_train_rec.ATTRIBUTE8
1825 , P_ATTRIBUTE9 => plan_2_train_rec.ATTRIBUTE9
1826 , P_ATTRIBUTE10 => plan_2_train_rec.ATTRIBUTE10
1827 , P_ATTRIBUTE11 => plan_2_train_rec.ATTRIBUTE11
1828 , P_ATTRIBUTE12 => plan_2_train_rec.ATTRIBUTE12
1829 , P_ATTRIBUTE13 => plan_2_train_rec.ATTRIBUTE13
1830 , P_ATTRIBUTE14 => plan_2_train_rec.ATTRIBUTE14
1831 , P_ATTRIBUTE15 => plan_2_train_rec.ATTRIBUTE15
1832 , P_ENABLED_FLAG => 'Y'
1833 , P_MEANING => plan_2_train_rec.MEANING
1834 , P_DESCRIPTION => plan_2_train_rec.DESCRIPTION
1835 , P_START_DATE_ACTIVE => plan_2_train_rec.START_DATE_ACTIVE
1836 , P_END_DATE_ACTIVE => plan_2_train_rec.END_DATE_ACTIVE
1837 );
1838
1839
1840 end loop;
1841 tab_usr_row_ids.delete;
1842 end if;
1843
1844 end copy_plan_2_trining;
1845
1846 /****************************************************************************
1847 Name : wsp_populate_udt
1848 Description : It create user columns in PAY_USER_COLUMNS each for
1849 South Africa specific Legal Entity.
1850
1851 *****************************************************************************/
1852 Procedure wsp_populate_udt is
1853
1854 Cursor cur_leg_entity is
1855 Select org_unit.organization_id
1856 , org_unit.organization_id || '_' || substr(org_unit_tl.NAME,1,79 - length(org_unit.organization_id)) user_column_name
1857 , org_unit.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
1858 , org_bg.ORG_INFORMATION9 legislation_code
1859 from hr_organization_information org_Legal_ent
1860 , hr_all_organization_units org_unit
1861 , hr_organization_information org_bg
1862 , hr_all_organization_units_tl org_unit_tl
1863 Where org_Legal_ent.ORG_INFORMATION_CONTEXT = 'CLASS'
1864 And org_Legal_ent.ORG_INFORMATION1 = 'HR_LEGAL'
1865 and org_Legal_ent.ORGANIZATION_ID = org_unit.ORGANIZATION_ID
1866 And org_bg.ORGANIZATION_ID = org_unit.BUSINESS_GROUP_ID
1867 And org_bg.ORG_INFORMATION_CONTEXT = 'Business Group Information'
1868 And org_bg.ORG_INFORMATION9 = 'ZA'
1869 And org_unit_tl.ORGANIZATION_ID = org_unit.ORGANIZATION_ID
1870 And org_unit_tl.LANGUAGE = userenv('LANG')
1871 And not exists
1872 ( Select 1
1873 from PAY_USER_TABLES PUT
1874 , PAY_USER_COLUMNS PUC
1875 , PAY_USER_COLUMNS_TL PUC_TL
1876 Where PUT.USER_TABLE_NAME = 'ZA_WSP_SKILLS_PRIORITIES'
1877 And PUT.legislation_code = 'ZA'
1878 And PUT.USER_TABLE_ID = PUC.USER_TABLE_ID
1879 And PUC_TL.USER_COLUMN_ID = PUC.USER_COLUMN_ID
1880 And PUC_TL.language = userenv('LANG')
1881 And PUC_TL.USER_COLUMN_NAME = org_unit.organization_id || '_' || substr(org_unit_tl.NAME,1,79 - length(org_unit.organization_id))
1882 );
1883 l_row_id varchar2(100);
1884 l_usr_col_id varchar2(100);
1885 l_user_table_id number;
1886 Begin
1887 Select PUT.USER_TABLE_ID
1888 into l_user_table_id
1889 From PAY_USER_TABLES PUT
1890 Where PUT.USER_TABLE_NAME = 'ZA_WSP_SKILLS_PRIORITIES'
1891 And PUT.legislation_code = 'ZA';
1892
1893 for legal_entity_rec in cur_leg_entity
1894 loop
1895
1896 pay_user_columns_pkg.insert_row (
1897 p_rowid => l_row_id,
1898 p_user_column_id => l_usr_col_id,
1899 p_user_table_id => l_user_table_id,
1900 p_business_group_id => legal_entity_rec.business_group_id,
1901 p_legislation_code => legal_entity_rec.legislation_code,
1902 p_legislation_subgroup => null,
1903 p_user_column_name => legal_entity_rec.user_column_name,
1904 p_formula_id => null ) ;
1905 end loop;
1906 End wsp_populate_udt;
1907
1908
1909 /* Main procedure
1910 It i getting called from concurrent programe
1911 */
1912
1913 /****************************************************************************
1914 Name : wsp_lookup_values
1915 Description : Main . It is getting called from Concurrent programe
1916
1917 *****************************************************************************/
1918
1919 Procedure wsp_lookup_values
1920 (errbuf out nocopy varchar2,
1921 retcode out nocopy number,
1922 p_syncronise in varchar2,
1923 p_year in number,
1924 P_mode in varchar2,
1925 p_from_year in number,
1926 p_plan_trng_ind in varchar2)
1927 Is
1928
1929 begin
1930 retcode := 0;
1931 -- hr_utility.trace_on(null,'ZAWSP');
1932 hr_utility.set_location('In wsp_lookup_values',10);
1933 hr_utility.set_location('p_syncronise :' || p_syncronise,20);
1934 hr_utility.set_location('p_year :' || p_year,20);
1935 hr_utility.set_location('P_mode :' || P_mode,20);
1936 hr_utility.set_location('p_from_year :' || p_from_year,20);
1937 hr_utility.set_location('p_plan_trng_ind :' || p_plan_trng_ind,20);
1938
1939
1940 if p_syncronise = '10' or p_syncronise = '40' then
1941 wsp_populate_udt;
1942 end if;
1943
1944 -- 30 Set the attribute
1945 if p_syncronise = '30' or p_syncronise = '40' then
1946 set_wsp_cat_attr_cat;
1947 end if;
1948
1949 if p_syncronise = '20' or p_syncronise = '40' then
1950 /* Initialising start and end date for plan and TRAINED */
1951 Select to_date('01-04-'||(p_year-1),'DD-MM-YYYY')
1952 , to_date('31-03-'|| p_year ,'DD-MM-YYYY')
1953 , to_date('01-04-'||(p_year-2),'DD-MM-YYYY')
1954 , to_date('31-03-'||(p_year-1),'DD-MM-YYYY')
1955 INTO
1956 g_plan_year_start_date
1957 , g_plan_year_end_date
1958 , g_trnd_year_start_date
1959 , g_trnd_year_end_date
1960 From Dual;
1961
1962 hr_utility.set_location('g_plan_year_start_date :' || g_plan_year_start_date,30);
1963 hr_utility.set_location('g_plan_year_end_date :' || g_plan_year_end_date,30);
1964 hr_utility.set_location('g_trnd_year_start_date :' || g_trnd_year_start_date,30);
1965 hr_utility.set_location('g_trnd_year_end_date :' || g_trnd_year_end_date,30);
1966
1967 if P_mode = '10' then -- Create
1968 /* create the look up values
1969 it will delete existing lookup values for passed year
1970 and create freash lookup values*/
1971 hr_utility.set_location('Calling create_lookup_values' ,40);
1972 create_lookup_values
1973 (errbuf => errbuf
1974 , retcode => retcode
1975 --, p_business_group_id => p_business_group_id
1976 , p_year => p_year
1977 , p_plan_trng_ind => p_plan_trng_ind
1978 , p_del_mode => 'Y' -- if values exists delete and re create
1979 );
1980 elsif P_mode = '20' then -- refresh
1981 hr_utility.set_location('Calling refresh_lookup_values' ,50);
1982 /* refresh the lookup values
1983 it will do the following
1984 1) add the Attribute_category where it is missing
1985 2) add new rows in lookup values if new Learning Path,
1986 Courses, Certification, Competencies and Qualifictios added
1987 */
1988 refresh_lookup_values
1989 (errbuf => errbuf
1990 , retcode => retcode
1991 --, p_business_group_id => p_business_group_id
1992 , p_year => p_year
1993 , p_plan_trng_ind => p_plan_trng_ind
1994 );
1995 elsif P_mode = '30' then -- Copy Plan to TRAINED
1996 /* Create the lookup values for plan as create
1997 and copy previous years plan to current years TRAINED
1998 */
1999 hr_utility.set_location('Calling copy_plan_2_trining' ,50);
2000 copy_plan_2_trining
2001 ( errbuf => errbuf
2002 , retcode => retcode
2003 , p_year => p_year
2004 , P_from_year => P_from_year
2005 );
2006
2007
2008 elsif p_mode = '40' then -- Copy Plan to Plan and TRained to Trained
2009 /* Create the lookup values for plan as create
2010 and copy previous years plan to current years TRAINED
2011 */
2012 hr_utility.set_location('Calling copy_lookup_values' ,60);
2013 copy_lookup_values
2014 (errbuf => errbuf
2015 ,retcode => retcode
2016 ,p_year => p_year
2017 ,P_from_year => P_from_year
2018 ,p_plan_trng_ind => p_plan_trng_ind
2019 );
2020
2021 else
2022 errbuf := 'Invalid mode option :' || p_mode || ':';
2023 retcode := -1;
2024
2025 end if;
2026 end if; -- End if p_syncronise = '10' or p_syncronise = '30'
2027 -- hr_utility.trace_off;
2028 EXCEPTION
2029 WHEN OTHERS then
2030 errbuf := substr(SQLERRM,1,255);
2031 retcode := sqlcode;
2032
2033 End wsp_lookup_values;
2034 /*
2035 valueset : valueset_wsp_copy_year
2036 */
2037 function vs_wsp_c_yr
2038 (
2039 p_lookup_code in varchar2,
2040 p_lookup_type in varchar2
2041 ) return varchar2
2042 Is
2043 wsp_copy_year varchar2(100);
2044 wsp_lookup_type varchar2(10);
2045 begin
2046 if (p_lookup_type = 'ZA_WSP_LEARNING_PATHS' OR p_lookup_type = 'ZA_WSP_COURSES' OR p_lookup_type = 'ZA_WSP_CERTIFICATIONS' ) then
2047 wsp_lookup_type := 'WSP';
2048 else if (p_lookup_type = 'ZA_ATR_LEARNING_PATHS' OR p_lookup_type = 'ZA_ATR_COURSES' OR p_lookup_type = 'ZA_ATR_CERTIFICATIONS' OR p_lookup_type = 'ZA_ATR_QUALIFICATIONS') then
2049 wsp_lookup_type := 'ATR';
2050 else
2051 wsp_lookup_type := 'NONE';
2052 end if;
2053 end if;
2054 --
2055 --
2056 if wsp_lookup_type <> 'NONE' then
2057 select decode(instr(wsp_lookup_type,'ATR'),0,substr(p_lookup_code,1,4),substr(p_lookup_code,1,4)+1)
2058 into wsp_copy_year
2059 from dual;
2060 end if;
2061
2062
2063 return wsp_copy_year;
2064
2065 EXCEPTION
2066 WHEN OTHERS then
2067 null;
2068
2069 end vs_wsp_c_yr;
2070
2071
2072 end PER_ZA_WSP_LOOKUP;