[Home] [Help]
PACKAGE BODY: APPS.PER_ZA_WSP_ARCHIVE_PKG
Source
1 package body per_za_wsp_archive_pkg as
2 /* $Header: perzawspa.pkb 120.0.12000000.2 2007/06/20 11:48:10 amahanty noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 2001 Oracle Corporation Redwood Shores, California, USA|
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : PER_ZA_WSP_ARCHIVE_PKG
9 Package File Name : perzawspa.pkb
10 Description : This sql script seeds the Package Body that
11 creates the WSP Archive code.
12
13 Change List : pyzawspa.pkb
14 ------------
15 Name Date Version Bug Text
16 ------------- ------------- ------- ------- ------------------------------
17 A. Mahanty 11-DEC-2006 115.0 First created
18 A. Mahanty 19-Feb-2007 115.2 All valid learning interventions
19 for a person are archived as
20 completed.
21 A. Mahanty 23-Feb-2007 115.3 Modified set_wsp_atr_tables
22 A. Mahanty 06-Jun-2007 115.5 6116743 Moved the setup for the pl/sql
23 tables and archiving of data for
24 A2 and B2 to range_cursor
25 A. Mahanty 11-Jun-2007 115.6 6121907 range_code modified and
26 6121877 globals are set in action_creation
27 A. Mahanty 12-Jun-2007 115.7 global flag added. Pl/sql table are
28 reset and populated if not already done.
29 ========================================================================*/
30 --
31 -- Global Variables
32 --
33
34 g_package constant varchar2(31) := 'per_za_wsp_archive_pkg.';
35 g_debug boolean;
36
37 g_sql_range varchar2(4000);
38
39 g_bg_id number(30);
40 g_legal_entity_id number(30);
41 g_archive_effective_date date;
42 g_pactid number;
43
44 g_cat_flex pay_user_column_instances_f.value%type := null;
45 g_cat_segment pay_user_column_instances_f.value%type := null;
46
47 g_plan_year number(10);
48
49 g_wsp_start_date date;
50 g_wsp_end_date date;
51
52 g_atr_start_date date;
53 g_atr_end_date date;
54
55 g_attribute_category constant varchar2(100) := 'ZA_WSP_SKILLS_PRIORITIES';
56 g_wsp_comp_lookup constant varchar2(40) := 'ZA_WSP_COMPETENCIES'; -- Not seeded. Only used in this package
57 g_wsp_lpath_lookup constant varchar2(40) := 'ZA_WSP_LEARNING_PATHS';
58 g_wsp_courses_lookup constant varchar2(40) := 'ZA_WSP_COURSES';
59 g_wsp_cert_lookup constant varchar2(40) := 'ZA_WSP_CERTIFICATIONS';
60 g_atr_lpath_lookup constant varchar2(40) := 'ZA_ATR_LEARNING_PATHS';
61 g_atr_courses_lookup constant varchar2(40) := 'ZA_ATR_COURSES';
62 g_atr_cert_lookup constant varchar2(40) := 'ZA_ATR_CERTIFICATIONS';
63 g_atr_comp_lookup constant varchar2(40) := 'ZA_ATR_COMPETENCIES';
64 g_atr_qual_lookup constant varchar2(40) := 'ZA_ATR_QUALIFICATIONS';
65 g_priority_udt_name constant varchar2(40) := 'ZA_WSP_SKILLS_PRIORITIES';
66
67 --flags
68 g_pl_tab_start varchar2(10) := 'N';
69 g_pl_tab_end varchar2(10) := 'N';
70
71 type wsp_map_tab is record
72 ( id number
73 , Attribute1 number
74 , Attribute2 number
75 , Attribute3 number
76 , Attribute4 number
77 , Attribute5 number
78 , Attribute6 number
79 , Attribute7 number
80 , Attribute8 number
81 , Attribute9 number
82 , Attribute10 number
83 , Attribute11 number
84 , Attribute12 number
85 , Attribute13 number
86 , Attribute14 number
87 , Attribute15 number
88 );
89 --
90 type wsp_priority_map_rec is record
91 ( skills_priority_id number --user_row_id
92 , skills_priority_name varchar2(80)
93 , trng_event_id number
94 , legal_entity_id number(15)
95 , level_number number(10)
96 , saqa_id varchar2(150)
97 , year varchar2(20)
98 );
99 --
100
101 type wsp_pri_final_tab_rec is record
102 ( skills_priority_id number --
103 , skills_priority_num number
104 , skills_priority_name varchar2(80)
105 , legal_entity_id number
106 , year varchar2(20)
107 , Level_1 number
108 , Level_2 number
109 , Level_3 number
110 , Level_4 number
111 , Level_5 number
112 , Level_6 number
113 , Level_7 number
114 , Level_8 number
115 , Unknown number
116 , SAQA_Registered number
117 , Not_Registered number
118 , SAQA_Ids varchar2(1000)
119 );
120
121 --
122 type priority_le_rec is record
123 ( skills_priority_id number
124 , legal_entity_id number
125 );
126 --
127 type trng_event_priority_rec is record
128 (trng_event_id number
129 ,priority_id number
130 ,legal_entity_id number
131 );
132 --
133 type t_wsp_priority_map_tab is table of wsp_priority_map_rec
134 index by binary_integer;
135 --
136 type t_wsp_map_tab is table of wsp_map_tab index by varchar2(100);
137 --
138 type t_wsp_pri_final_tab is table of wsp_pri_final_tab_rec index by varchar2(30);
139 --
140 type t_trng_priority_tab is table of trng_event_priority_rec index by varchar2(100);
141 --
142 type skills_priority_num_rec is record
143 (skills_priority_num number);
144
145 type t_skills_priority_num is table of skills_priority_num_rec index by varchar2(50);
146
147 --global pl/sql tables for trng events. index by trng event id
148 g_wsp_courses_tab t_wsp_map_tab;
149 g_wsp_l_paths_tab t_wsp_map_tab;
150 g_wsp_certifications_tab t_wsp_map_tab;
151 g_atr_courses_tab t_wsp_map_tab;
152 g_atr_l_paths_tab t_wsp_map_tab;
153 g_atr_certifications_tab t_wsp_map_tab;
154 g_atr_competences_tab t_wsp_map_tab;
155 g_atr_qualifications_tab t_wsp_map_tab;
156 --
157 g_wsp_priority_tab t_wsp_priority_map_tab;
158 g_atr_priority_tab t_wsp_priority_map_tab;
159 -- for A2 and B2 . index by legal_entity + skills priority id
160 g_wsp_pri_final_tab t_wsp_pri_final_tab;
161 g_atr_pri_final_tab t_wsp_pri_final_tab;
162 -- index by Skills priority id + Competence/Course Id
163 g_wsp_compt_pri_tab t_trng_priority_tab;
164 g_wsp_course_pri_tab t_trng_priority_tab;
165
166 g_atr_compt_pri_tab t_trng_priority_tab;
167 g_atr_course_pri_tab t_trng_priority_tab;
168
169 g_wsp_index binary_integer ;
170 g_atr_index binary_integer ;
171 g_wsp_skills_pri_num number;
172 g_atr_skills_pri_num number;
173 --
174 g_wsp_skills_priority_num t_skills_priority_num;
175 g_atr_skills_priority_num t_skills_priority_num;
176 --
177 cursor g_csr_le is
178 select distinct to_number(substr(puc1.user_column_name,1, instr(puc1.user_column_name,'_')-1)) organization_id
179 from pay_user_tables put
180 , pay_user_columns puc
181 , pay_user_columns puc1
182 , pay_user_rows_f pur
183 , pay_user_column_instances_f puci
184 where put.user_table_name = g_priority_udt_name
185 and puc.user_table_id = put.user_table_id
186 and pur.user_table_id = put.user_table_id
187 and pur.business_group_id = g_bg_id
188 and puci.user_row_id = pur.user_row_id
189 and puci.user_column_id = puc1.user_column_id;
190 ---
191
192 /*--------------------------------------------------------------------------
193 Name : reset_tables
194 Purpose : Reset global tables.
195 Arguments : None
196 --------------------------------------------------------------------------*/
197 procedure reset_tables is
198
199 l_proc constant varchar2(60) := g_package || 'reset_tables';
200
201 begin
202 hr_utility.set_location('Entering ' || l_proc, 10);
203 g_pl_tab_start := 'Y';
204
205 g_wsp_courses_tab.delete;
206 g_wsp_l_paths_tab.delete;
207 g_wsp_certifications_tab.delete;
208 g_atr_courses_tab.delete;
209 g_atr_l_paths_tab.delete;
210 g_atr_certifications_tab.delete;
211
212 g_atr_competences_tab.delete;
213 g_atr_qualifications_tab.delete;
214
215 g_wsp_priority_tab.delete;
216 g_atr_priority_tab.delete;
217
218 g_wsp_pri_final_tab.delete;
219 g_atr_pri_final_tab.delete;
220
221 g_wsp_index := 0;
222 g_atr_index := 0;
223 g_wsp_skills_pri_num := 0;
224 g_atr_skills_pri_num := 0;
225
226 hr_utility.set_location('Leaving ' || l_proc, 30);
227 end reset_tables;
228 --
229 /* Procedure to set global tables
230 with p_id
231 where p_id are course_id, Learning_path_id etc
232 */
233
234 Procedure set_wsp_atr_tables
235 ( p_id varchar2
236 , lookup_type varchar2
237 , p_effective_date date
238 ) is
239
240 cursor csr_get_priority ( p_lookup_type varchar2
241 , p_year varchar2) is
242 select Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,
243 Attribute7,Attribute8,Attribute9,Attribute10, Attribute11,
244 Attribute12, Attribute13, Attribute14, Attribute15,
245 substr(lookup_code,5) lookup_code
246 from fnd_lookup_values
247 where lookup_type = p_lookup_type
248 and lookup_code like p_year || '%'
249 and security_group_id = fnd_global.lookup_security_group(p_lookup_type,3)
250 and attribute_category = G_ATTRIBUTE_CATEGORY
251 and (
252 Attribute1 || Attribute2 || Attribute3 || Attribute4 || Attribute5 ||
253 Attribute6 || Attribute7 || Attribute8 || Attribute9 || Attribute10 ||
254 Attribute11|| Attribute12|| Attribute13|| Attribute14 || Attribute15
255 ) is not null;
256
257 l_index varchar2(30);
258 l_proc constant varchar2(60) := g_package || 'set_wsp_atr_tables';
259 begin
260 hr_utility.trace('Entering ' || l_proc);
261 l_index := p_id;
262 if lookup_type = g_wsp_courses_lookup then
263 for rec_get_priority in csr_get_priority(lookup_type, to_char(p_effective_date,'YYYY')) -- chk again
264 loop
265 g_wsp_courses_tab(l_index).id := p_id;
266 g_wsp_courses_tab(l_index).Attribute1 := rec_get_priority.Attribute1 ;
267 g_wsp_courses_tab(l_index).Attribute2 := rec_get_priority.Attribute2 ;
268 g_wsp_courses_tab(l_index).Attribute3 := rec_get_priority.Attribute3 ;
269 g_wsp_courses_tab(l_index).Attribute4 := rec_get_priority.Attribute4 ;
270 g_wsp_courses_tab(l_index).Attribute5 := rec_get_priority.Attribute5 ;
271 g_wsp_courses_tab(l_index).Attribute6 := rec_get_priority.Attribute6 ;
272 g_wsp_courses_tab(l_index).Attribute7 := rec_get_priority.Attribute7 ;
273 g_wsp_courses_tab(l_index).Attribute8 := rec_get_priority.Attribute8 ;
274 g_wsp_courses_tab(l_index).Attribute9 := rec_get_priority.Attribute9 ;
275 g_wsp_courses_tab(l_index).Attribute10 := rec_get_priority.Attribute10 ;
276 g_wsp_courses_tab(l_index).Attribute11 := rec_get_priority.Attribute11 ;
277 g_wsp_courses_tab(l_index).Attribute12 := rec_get_priority.Attribute12 ;
278 g_wsp_courses_tab(l_index).Attribute13 := rec_get_priority.Attribute13 ;
279 g_wsp_courses_tab(l_index).Attribute14 := rec_get_priority.Attribute14 ;
280 g_wsp_courses_tab(l_index).Attribute15 := rec_get_priority.Attribute15 ;
281 end loop;
282 hr_utility.set_location('g_wsp_courses_tab.count : '||g_wsp_courses_tab.count,10);
283 elsif lookup_type = g_wsp_lpath_lookup then
284 for rec_get_priority in csr_get_priority(lookup_type, to_char(p_effective_date,'YYYY')) -- chk again
285 loop
286 g_wsp_l_paths_tab(l_index).id := p_id;
287 g_wsp_l_paths_tab(l_index).Attribute1 := rec_get_priority.Attribute1 ;
288 g_wsp_l_paths_tab(l_index).Attribute2 := rec_get_priority.Attribute2 ;
289 g_wsp_l_paths_tab(l_index).Attribute3 := rec_get_priority.Attribute3 ;
290 g_wsp_l_paths_tab(l_index).Attribute4 := rec_get_priority.Attribute4 ;
291 g_wsp_l_paths_tab(l_index).Attribute5 := rec_get_priority.Attribute5 ;
292 g_wsp_l_paths_tab(l_index).Attribute6 := rec_get_priority.Attribute6 ;
293 g_wsp_l_paths_tab(l_index).Attribute7 := rec_get_priority.Attribute7 ;
294 g_wsp_l_paths_tab(l_index).Attribute8 := rec_get_priority.Attribute8 ;
295 g_wsp_l_paths_tab(l_index).Attribute9 := rec_get_priority.Attribute9 ;
296 g_wsp_l_paths_tab(l_index).Attribute10 := rec_get_priority.Attribute10 ;
297 g_wsp_l_paths_tab(l_index).Attribute11 := rec_get_priority.Attribute11 ;
298 g_wsp_l_paths_tab(l_index).Attribute12 := rec_get_priority.Attribute12 ;
299 g_wsp_l_paths_tab(l_index).Attribute13 := rec_get_priority.Attribute13 ;
300 g_wsp_l_paths_tab(l_index).Attribute14 := rec_get_priority.Attribute14 ;
301 g_wsp_l_paths_tab(l_index).Attribute15 := rec_get_priority.Attribute15 ;
302 end loop;
303 hr_utility.set_location('g_wsp_l_paths_tab.count : '||g_wsp_l_paths_tab.count,10);
304 elsif lookup_type = g_wsp_cert_lookup then
305 for rec_get_priority in csr_get_priority(lookup_type, to_char(p_effective_date,'YYYY')) -- chk again
306 loop
307 g_wsp_certifications_tab(l_index).id := p_id;
308 g_wsp_certifications_tab(l_index).Attribute1 := rec_get_priority.Attribute1 ;
309 g_wsp_certifications_tab(l_index).Attribute2 := rec_get_priority.Attribute2 ;
310 g_wsp_certifications_tab(l_index).Attribute3 := rec_get_priority.Attribute3 ;
311 g_wsp_certifications_tab(l_index).Attribute4 := rec_get_priority.Attribute4 ;
312 g_wsp_certifications_tab(l_index).Attribute5 := rec_get_priority.Attribute5 ;
313 g_wsp_certifications_tab(l_index).Attribute6 := rec_get_priority.Attribute6 ;
314 g_wsp_certifications_tab(l_index).Attribute7 := rec_get_priority.Attribute7 ;
315 g_wsp_certifications_tab(l_index).Attribute8 := rec_get_priority.Attribute8 ;
316 g_wsp_certifications_tab(l_index).Attribute9 := rec_get_priority.Attribute9 ;
317 g_wsp_certifications_tab(l_index).Attribute10 := rec_get_priority.Attribute10 ;
318 g_wsp_certifications_tab(l_index).Attribute11 := rec_get_priority.Attribute11 ;
319 g_wsp_certifications_tab(l_index).Attribute12 := rec_get_priority.Attribute12 ;
320 g_wsp_certifications_tab(l_index).Attribute13 := rec_get_priority.Attribute13 ;
321 g_wsp_certifications_tab(l_index).Attribute14 := rec_get_priority.Attribute14 ;
322 g_wsp_certifications_tab(l_index).Attribute15 := rec_get_priority.Attribute15 ;
323 end loop;
324 hr_utility.set_location('g_wsp_certifications_tab.count : '||g_wsp_certifications_tab.count,10);
325 /* -- Not being used anymore
326 elsif lookup_type = g_atr_courses_lookup then
327 for rec_get_priority in csr_get_priority(lookup_type, to_char(p_effective_date,'YYYY')) -- chk again
328 loop
329 g_atr_courses_tab(l_index).id := p_id;
330 g_atr_courses_tab(l_index).Attribute1 := rec_get_priority.Attribute1 ;
331 g_atr_courses_tab(l_index).Attribute2 := rec_get_priority.Attribute2 ;
332 g_atr_courses_tab(l_index).Attribute3 := rec_get_priority.Attribute3 ;
333 g_atr_courses_tab(l_index).Attribute4 := rec_get_priority.Attribute4 ;
334 g_atr_courses_tab(l_index).Attribute5 := rec_get_priority.Attribute5 ;
335 g_atr_courses_tab(l_index).Attribute6 := rec_get_priority.Attribute6 ;
336 g_atr_courses_tab(l_index).Attribute7 := rec_get_priority.Attribute7 ;
337 g_atr_courses_tab(l_index).Attribute8 := rec_get_priority.Attribute8 ;
338 g_atr_courses_tab(l_index).Attribute9 := rec_get_priority.Attribute9 ;
339 g_atr_courses_tab(l_index).Attribute10 := rec_get_priority.Attribute10 ;
340 g_atr_courses_tab(l_index).Attribute11 := rec_get_priority.Attribute11 ;
341 g_atr_courses_tab(l_index).Attribute12 := rec_get_priority.Attribute12 ;
342 g_atr_courses_tab(l_index).Attribute13 := rec_get_priority.Attribute13 ;
343 g_atr_courses_tab(l_index).Attribute14 := rec_get_priority.Attribute14 ;
344 g_atr_courses_tab(l_index).Attribute15 := rec_get_priority.Attribute15 ;
345 end loop;
346 */
347 elsif lookup_type = g_atr_lpath_lookup then
348 for rec_get_priority in csr_get_priority(lookup_type, to_char(p_effective_date,'YYYY')) -- chk again
349 loop
350 g_atr_l_paths_tab(l_index).id := p_id;
351 g_atr_l_paths_tab(l_index).Attribute1 := rec_get_priority.Attribute1 ;
352 g_atr_l_paths_tab(l_index).Attribute2 := rec_get_priority.Attribute2 ;
353 g_atr_l_paths_tab(l_index).Attribute3 := rec_get_priority.Attribute3 ;
354 g_atr_l_paths_tab(l_index).Attribute4 := rec_get_priority.Attribute4 ;
355 g_atr_l_paths_tab(l_index).Attribute5 := rec_get_priority.Attribute5 ;
356 g_atr_l_paths_tab(l_index).Attribute6 := rec_get_priority.Attribute6 ;
357 g_atr_l_paths_tab(l_index).Attribute7 := rec_get_priority.Attribute7 ;
358 g_atr_l_paths_tab(l_index).Attribute8 := rec_get_priority.Attribute8 ;
359 g_atr_l_paths_tab(l_index).Attribute9 := rec_get_priority.Attribute9 ;
360 g_atr_l_paths_tab(l_index).Attribute10 := rec_get_priority.Attribute10;
361 g_atr_l_paths_tab(l_index).Attribute11 := rec_get_priority.Attribute11;
362 g_atr_l_paths_tab(l_index).Attribute12 := rec_get_priority.Attribute12;
363 g_atr_l_paths_tab(l_index).Attribute13 := rec_get_priority.Attribute13;
364 g_atr_l_paths_tab(l_index).Attribute14 := rec_get_priority.Attribute14;
365 g_atr_l_paths_tab(l_index).Attribute15 := rec_get_priority.Attribute15;
366 end loop;
367 hr_utility.set_location('g_atr_l_paths_tab.count : '||g_atr_l_paths_tab.count,10);
368 elsif lookup_type = g_atr_cert_lookup then
369 for rec_get_priority in csr_get_priority(lookup_type, to_char(p_effective_date,'YYYY')) -- chk again
370 loop
371 g_atr_certifications_tab(l_index).id := p_id;
372 g_atr_certifications_tab(l_index).Attribute1 := rec_get_priority.Attribute1 ;
373 g_atr_certifications_tab(l_index).Attribute2 := rec_get_priority.Attribute2 ;
374 g_atr_certifications_tab(l_index).Attribute3 := rec_get_priority.Attribute3 ;
375 g_atr_certifications_tab(l_index).Attribute4 := rec_get_priority.Attribute4 ;
376 g_atr_certifications_tab(l_index).Attribute5 := rec_get_priority.Attribute5 ;
377 g_atr_certifications_tab(l_index).Attribute6 := rec_get_priority.Attribute6 ;
378 g_atr_certifications_tab(l_index).Attribute7 := rec_get_priority.Attribute7 ;
379 g_atr_certifications_tab(l_index).Attribute8 := rec_get_priority.Attribute8 ;
380 g_atr_certifications_tab(l_index).Attribute9 := rec_get_priority.Attribute9 ;
381 g_atr_certifications_tab(l_index).Attribute10 := rec_get_priority.Attribute10;
382 g_atr_certifications_tab(l_index).Attribute11 := rec_get_priority.Attribute11;
383 g_atr_certifications_tab(l_index).Attribute12 := rec_get_priority.Attribute12;
384 g_atr_certifications_tab(l_index).Attribute13 := rec_get_priority.Attribute13;
385 g_atr_certifications_tab(l_index).Attribute14 := rec_get_priority.Attribute14;
386 g_atr_certifications_tab(l_index).Attribute15 := rec_get_priority.Attribute15;
387 end loop;
388 hr_utility.set_location('g_atr_certifications_tab.count : '||g_atr_certifications_tab.count,10);
389 /* --Not being used anymore
390 elsif lookup_type = g_atr_comp_lookup then
391 for rec_get_priority in csr_get_priority(lookup_type, to_char(p_effective_date,'YYYY')) -- chk again
392 loop
393 g_atr_competences_tab(l_index).id := p_id;
394 g_atr_competences_tab(l_index).Attribute1 := rec_get_priority.Attribute1 ;
395 g_atr_competences_tab(l_index).Attribute2 := rec_get_priority.Attribute2 ;
396 g_atr_competences_tab(l_index).Attribute3 := rec_get_priority.Attribute3 ;
397 g_atr_competences_tab(l_index).Attribute4 := rec_get_priority.Attribute4 ;
398 g_atr_competences_tab(l_index).Attribute5 := rec_get_priority.Attribute5 ;
399 g_atr_competences_tab(l_index).Attribute6 := rec_get_priority.Attribute6 ;
400 g_atr_competences_tab(l_index).Attribute7 := rec_get_priority.Attribute7 ;
401 g_atr_competences_tab(l_index).Attribute8 := rec_get_priority.Attribute8 ;
402 g_atr_competences_tab(l_index).Attribute9 := rec_get_priority.Attribute9 ;
403 g_atr_competences_tab(l_index).Attribute10 := rec_get_priority.Attribute10 ;
404 g_atr_competences_tab(l_index).Attribute11 := rec_get_priority.Attribute11 ;
405 g_atr_competences_tab(l_index).Attribute12 := rec_get_priority.Attribute12 ;
406 g_atr_competences_tab(l_index).Attribute13 := rec_get_priority.Attribute13 ;
407 g_atr_competences_tab(l_index).Attribute14 := rec_get_priority.Attribute14 ;
408 g_atr_competences_tab(l_index).Attribute15 := rec_get_priority.Attribute15 ;
409 end loop;
410 */
411 elsif lookup_type = g_atr_qual_lookup then
412 for rec_get_priority in csr_get_priority(lookup_type, to_char(p_effective_date,'YYYY')) -- chk again
413 loop
414 g_atr_qualifications_tab(l_index).id := p_id;
415 g_atr_qualifications_tab(l_index).Attribute1 := rec_get_priority.Attribute1 ;
416 g_atr_qualifications_tab(l_index).Attribute2 := rec_get_priority.Attribute2 ;
417 g_atr_qualifications_tab(l_index).Attribute3 := rec_get_priority.Attribute3 ;
418 g_atr_qualifications_tab(l_index).Attribute4 := rec_get_priority.Attribute4 ;
419 g_atr_qualifications_tab(l_index).Attribute5 := rec_get_priority.Attribute5 ;
420 g_atr_qualifications_tab(l_index).Attribute6 := rec_get_priority.Attribute6 ;
421 g_atr_qualifications_tab(l_index).Attribute7 := rec_get_priority.Attribute7 ;
422 g_atr_qualifications_tab(l_index).Attribute8 := rec_get_priority.Attribute8 ;
423 g_atr_qualifications_tab(l_index).Attribute9 := rec_get_priority.Attribute9 ;
424 g_atr_qualifications_tab(l_index).Attribute10 := rec_get_priority.Attribute10 ;
425 g_atr_qualifications_tab(l_index).Attribute11 := rec_get_priority.Attribute11 ;
426 g_atr_qualifications_tab(l_index).Attribute12 := rec_get_priority.Attribute12 ;
427 g_atr_qualifications_tab(l_index).Attribute13 := rec_get_priority.Attribute13 ;
428 g_atr_qualifications_tab(l_index).Attribute14 := rec_get_priority.Attribute14 ;
429 g_atr_qualifications_tab(l_index).Attribute15 := rec_get_priority.Attribute15 ;
430 end loop;
431 hr_utility.set_location('g_atr_qualifications_tab.count : '||g_atr_qualifications_tab.count,10);
432 end if;
433 hr_utility.trace('Leaving ' || l_proc);
434 end set_wsp_atr_tables;
435 --
436 /*--------------------------------------------------------------------------
437 Name : set_wsp_atr_pri_tabs
438 Purpose : Procedure to set global tables for the skills priorities and
439 training events, their level and SAQA ID.
440 --------------------------------------------------------------------------*/
441
442 Procedure set_wsp_atr_pri_tabs
443 ( p_user_row_id number
444 , p_trng_event_id varchar2
445 , p_lookup_type varchar2
446 , p_effective_date date
447 ) is
448
449 cursor csr_get_pri_name(p_csr_user_row_id number) is
450 select row_low_range_or_name --, effective_start_date, effective_end_date
451 from pay_user_rows_f
452 where user_row_id = p_csr_user_row_id
453 and p_effective_date between effective_start_date and effective_end_date;
454
455 cursor csr_get_legal_entity (p_csr_user_row_id number) is
456 select distinct substr(puc.user_column_name,1, instr(puc.user_column_name,'_')-1) organization_id
457 from pay_user_column_instances_f puci
458 , pay_user_columns_tl puc
459 where puci.user_row_id = p_csr_user_row_id
460 and puc.user_column_id = puci.user_column_id
461 and p_effective_date between puci.effective_start_date and puci.effective_end_date;
462
463 cursor csr_get_comp_info(p_csr_trng_event_id number) is
464 select level_number, unit_standard_id --saqa_id
465 from per_competences
466 where competence_id = p_csr_trng_event_id;
467
468 cursor csr_get_qual_info(p_csr_trng_event_id number) is
469 select level_number, qual_framework_id --saqa_id
470 from per_qualification_types
471 where qualification_type_id = p_csr_trng_event_id;
472 --variables
473 l_skills_priority_id number;
474 l_skills_priority_name varchar2(80);
475 l_level_num number;
476 l_saqa_id varchar2(150);
477 l_year varchar2(20);
478 l_index varchar2(30);
479 l_proc constant varchar2(60) := g_package || 'set_wsp_atr_pri_tabs';
480 l_pri_index varchar2(60);
481 --
482 l_count number;
483 --
484 begin
485 hr_utility.trace('Entering ' || l_proc);
486
487 if p_user_row_id is not null then
488 open csr_get_pri_name(p_user_row_id);
489 fetch csr_get_pri_name into l_skills_priority_name;
490 close csr_get_pri_name;
491
492 if (p_lookup_type = g_atr_comp_lookup OR p_lookup_type = g_wsp_comp_lookup) then
493 open csr_get_comp_info(p_trng_event_id);
494 fetch csr_get_comp_info into l_level_num, l_saqa_id;
495 close csr_get_comp_info;
496 elsif p_lookup_type = g_atr_qual_lookup then
497 open csr_get_qual_info(p_trng_event_id);
498 fetch csr_get_qual_info into l_level_num, l_saqa_id;
499 close csr_get_qual_info;
500 end if;
501 end if;
502 hr_utility.set_location('p_user_row_id '||p_user_row_id ,10);
503 hr_utility.set_location('p_trng_event_id '||p_trng_event_id ,10);
504 hr_utility.set_location('p_lookup_type '||p_lookup_type ,10);
505 hr_utility.set_location('p_effective_date'||to_char(p_effective_date,'DD-MM-YYYY') ,10);
506 hr_utility.set_location('l_skills_priority_name'||l_skills_priority_name,10);
507
508
509 if (p_user_row_id is not null AND instr(p_lookup_type,'WSP') > 0) then
510 -- For the Planned Training Event and Priority Map . To stop duplication
511 if p_lookup_type = g_wsp_comp_lookup then
512 l_pri_index := p_user_row_id ||'_' || p_trng_event_id;
513 if NOT g_wsp_compt_pri_tab.exists(l_pri_index) then
514 g_wsp_compt_pri_tab(l_pri_index).trng_event_id := p_trng_event_id;
515 g_wsp_compt_pri_tab(l_pri_index).priority_id := p_user_row_id;
516 end if;
517 elsif p_lookup_type = g_wsp_courses_lookup then
518 l_pri_index := p_user_row_id ||'_' || p_trng_event_id;
519 if NOT g_wsp_compt_pri_tab.exists(l_pri_index) then
520 g_wsp_course_pri_tab(l_pri_index).trng_event_id := p_trng_event_id;
521 g_wsp_course_pri_tab(l_pri_index).priority_id := p_user_row_id;
522 end if;
523 end if;
524 hr_utility.set_location('g_wsp_compt_pri_tab.count: '||g_wsp_compt_pri_tab.count,10);
525 hr_utility.set_location('g_wsp_course_pri_tab.count: '||g_wsp_course_pri_tab.count,10);
526 --
527 for rec_legal_entity in csr_get_legal_entity(p_user_row_id)
528 loop
529 hr_utility.set_location('Entering rec_legal_entity',20);
530 g_wsp_index := g_wsp_index + 1;
531 g_wsp_priority_tab(g_wsp_index).skills_priority_id := p_user_row_id;
532 g_wsp_priority_tab(g_wsp_index).skills_priority_name := l_skills_priority_name;
533 g_wsp_priority_tab(g_wsp_index).trng_event_id := p_trng_event_id;
534 g_wsp_priority_tab(g_wsp_index).legal_entity_id := fnd_number.canonical_to_number(rec_legal_entity.organization_id);
535 g_wsp_priority_tab(g_wsp_index).level_number := l_level_num;
536 g_wsp_priority_tab(g_wsp_index).saqa_id := l_saqa_id;
537 g_wsp_priority_tab(g_wsp_index).year := to_char(p_effective_date,'YYYY');
538
539 --
540 hr_utility.set_location(' level_number: '||l_level_num,20);
541 hr_utility.set_location(' l_saqa_id: '||l_saqa_id,20);
542 hr_utility.set_location(' year : '||to_char(p_effective_date,'YYYY'),20);
543 end loop;
544 for rec_legal_entity2 in csr_get_legal_entity(p_user_row_id)
545 loop
546 hr_utility.set_location('Entering rec_legal_entity2',20);
547 l_index := rpad(rec_legal_entity2.organization_id,15,0) || p_user_row_id;
548 hr_utility.set_location('l_index '||l_index,20);
549 if g_wsp_pri_final_tab.exists(l_index) then
550 hr_utility.set_location('Record exists',20);
551 else
552 hr_utility.set_location('New Record in g_wsp_pri_final_tab',20);
553 --g_wsp_skills_pri_num := g_wsp_skills_pri_num + 1;
554 if g_wsp_skills_priority_num.exists(fnd_number.canonical_to_number(rec_legal_entity2.organization_id)) then
555 g_wsp_skills_priority_num(fnd_number.canonical_to_number(rec_legal_entity2.organization_id)).skills_priority_num := g_wsp_skills_priority_num(fnd_number.canonical_to_number(rec_legal_entity2.organization_id)).skills_priority_num + 1;
556 else
557 g_wsp_skills_priority_num(fnd_number.canonical_to_number(rec_legal_entity2.organization_id)).skills_priority_num := 1;
558 end if;
559
560 g_wsp_pri_final_tab(l_index).legal_entity_id := fnd_number.canonical_to_number(rec_legal_entity2.organization_id);
561 g_wsp_pri_final_tab(l_index).skills_priority_id := p_user_row_id;
562 g_wsp_pri_final_tab(l_index).skills_priority_num := g_wsp_skills_priority_num(fnd_number.canonical_to_number(rec_legal_entity2.organization_id)).skills_priority_num;
563 g_wsp_pri_final_tab(l_index).skills_priority_name := l_skills_priority_name;
564 g_wsp_pri_final_tab(l_index).year := to_char(p_effective_date,'YYYY');
565 g_wsp_pri_final_tab(l_index).Level_1 := 0;
566 g_wsp_pri_final_tab(l_index).Level_2 := 0;
567 g_wsp_pri_final_tab(l_index).Level_3 := 0;
568 g_wsp_pri_final_tab(l_index).Level_4 := 0;
569 g_wsp_pri_final_tab(l_index).Level_5 := 0;
570 g_wsp_pri_final_tab(l_index).Level_6 := 0;
571 g_wsp_pri_final_tab(l_index).Level_7 := 0;
572 g_wsp_pri_final_tab(l_index).Level_8 := 0;
573 g_wsp_pri_final_tab(l_index).Unknown := 0;
574 g_wsp_pri_final_tab(l_index).SAQA_Registered := 0;
575 g_wsp_pri_final_tab(l_index).Not_Registered := 0;
576 --
577 hr_utility.set_location(' skills_priority_num : '||g_wsp_skills_pri_num,20) ;
578 hr_utility.set_location(' skills_priority_name: '||l_skills_priority_name,20);
579 hr_utility.set_location(' l_index : '||l_index,20);
580 hr_utility.set_location('g_wsp_pri_final_tab(l_index).legal_entity_id : '||g_wsp_pri_final_tab(l_index).legal_entity_id,20);
581 hr_utility.set_location('rec_legal_entity2.organization_id: '||fnd_number.canonical_to_number(rec_legal_entity2.organization_id),20);
582 --
583 end if;
584 end loop;
585 elsif (p_user_row_id is not null AND instr(p_lookup_type,'ATR') > 0) then
586
587 -- For the Completed Training Event and Priority Map . To stop duplication
588 if p_lookup_type = g_atr_comp_lookup then
589 l_pri_index := p_user_row_id ||'_' || p_trng_event_id;
590 if NOT g_atr_compt_pri_tab.exists(l_pri_index) then
591 g_atr_compt_pri_tab(l_pri_index).trng_event_id := p_trng_event_id;
592 g_atr_compt_pri_tab(l_pri_index).priority_id := p_user_row_id;
593 end if;
594 elsif p_lookup_type = g_atr_courses_lookup then
595 l_pri_index := p_user_row_id ||'_' || p_trng_event_id;
596 if NOT g_atr_course_pri_tab.exists(l_pri_index) then
597 g_atr_course_pri_tab(l_pri_index).trng_event_id := p_trng_event_id;
598 g_atr_course_pri_tab(l_pri_index).priority_id := p_user_row_id;
599 end if;
600 end if;
601 hr_utility.set_location('g_atr_compt_pri_tab.count : '||g_atr_compt_pri_tab.count,30);
602 hr_utility.set_location('g_atr_course_pri_tab.count : '||g_atr_course_pri_tab.count,30);
603 --
604 for rec_legal_entity in csr_get_legal_entity(p_user_row_id)
605 loop
606 hr_utility.set_location('Entering rec_legal_entity',30);
607 g_atr_index := g_atr_index + 1;
608 g_atr_priority_tab(g_atr_index).skills_priority_id := p_user_row_id;
609 g_atr_priority_tab(g_atr_index).skills_priority_name := l_skills_priority_name;
610 g_atr_priority_tab(g_atr_index).trng_event_id := p_trng_event_id;
611 g_atr_priority_tab(g_atr_index).legal_entity_id := rec_legal_entity.organization_id;
612 g_atr_priority_tab(g_atr_index).level_number := l_level_num;
613 g_atr_priority_tab(g_atr_index).saqa_id := l_saqa_id;
614 g_atr_priority_tab(g_atr_index).year := to_char(p_effective_date,'YYYY');
615 hr_utility.set_location('Checking',30);
616 hr_utility.set_location('skills_priority_id: '||p_user_row_id,30);
617 hr_utility.set_location('trng_event_id: '||p_trng_event_id,30);
618 hr_utility.set_location('legal_entity_id: '||rec_legal_entity.organization_id,30);
619 end loop;
620 for rec_legal_entity3 in csr_get_legal_entity(p_user_row_id)
621 loop
622 l_index := rpad(rec_legal_entity3.organization_id,15,0) || p_user_row_id;
623 hr_utility.set_location('Entering rec_legal_entity3-l_index: '||l_index,30);
624 if g_atr_pri_final_tab.exists(l_index) then
625 hr_utility.set_location('record exists',30);
626 else
627 hr_utility.set_location('New Record in g_atr_pri_final_tab',30);
628 --g_atr_skills_pri_num := g_atr_skills_pri_num + 1;
629 if g_atr_skills_priority_num.exists(fnd_number.canonical_to_number(rec_legal_entity3.organization_id)) then
630 g_atr_skills_priority_num(fnd_number.canonical_to_number(rec_legal_entity3.organization_id)).skills_priority_num := g_atr_skills_priority_num(fnd_number.canonical_to_number(rec_legal_entity3.organization_id)).skills_priority_num + 1;
631 else
632 g_atr_skills_priority_num(fnd_number.canonical_to_number(rec_legal_entity3.organization_id)).skills_priority_num := 1;
633 end if;
634 g_atr_pri_final_tab(l_index).legal_entity_id := fnd_number.canonical_to_number(rec_legal_entity3.organization_id);
635 g_atr_pri_final_tab(l_index).skills_priority_id := p_user_row_id;
636 g_atr_pri_final_tab(l_index).skills_priority_num := g_atr_skills_priority_num(fnd_number.canonical_to_number(rec_legal_entity3.organization_id)).skills_priority_num;
637 g_atr_pri_final_tab(l_index).skills_priority_name := l_skills_priority_name;
638 g_atr_pri_final_tab(l_index).year := to_char(p_effective_date,'YYYY');
639 g_atr_pri_final_tab(l_index).Level_1 := 0;
640 g_atr_pri_final_tab(l_index).Level_2 := 0;
641 g_atr_pri_final_tab(l_index).Level_3 := 0;
642 g_atr_pri_final_tab(l_index).Level_4 := 0;
643 g_atr_pri_final_tab(l_index).Level_5 := 0;
644 g_atr_pri_final_tab(l_index).Level_6 := 0;
645 g_atr_pri_final_tab(l_index).Level_7 := 0;
646 g_atr_pri_final_tab(l_index).Level_8 := 0;
647 g_atr_pri_final_tab(l_index).Unknown := 0;
648 g_atr_pri_final_tab(l_index).SAQA_Registered := 0;
649 g_atr_pri_final_tab(l_index).Not_Registered := 0;
650 --
651 hr_utility.set_location(' skills_priority_num : '||g_atr_skills_pri_num ,30) ;
652 hr_utility.set_location(' skills_priority_name: '||l_skills_priority_name,30);
653 hr_utility.set_location(' year : '||to_char(p_effective_date,'YYYY'),30);
654
655 end if;
656 end loop;
657 end if;
658
659
660 hr_utility.set_location('Leaving ' || l_proc, 30);
661 end set_wsp_atr_pri_tabs;
662
663 /*--------------------------------------------------------------------------
664 Name : set_wsp_atr_final_tabs
665 Purpose : Procedure to set global tables for the A2 and B2 Sections
666 --------------------------------------------------------------------------*/
667 Procedure set_wsp_atr_final_tabs is
668 --variables
669 l_skills_pri_id varchar2(30);
670 l_proc constant varchar2(60) := g_package || 'set_wsp_atr_final_tabs';
671 l_count number;
672 l_index varchar2(100);
673 begin
674
675 hr_utility.trace('Entering ' || l_proc);
676 hr_utility.set_location('g_wsp_pri_final_tab.COUNT : '||g_wsp_pri_final_tab.COUNT,10);
677 hr_utility.set_location('g_wsp_pri_final_tab.FIRST : '||g_wsp_pri_final_tab.FIRST,10);
678 -- WSP tables
679 l_count := g_wsp_pri_final_tab.COUNT;
680
681 --AM
682 l_count := g_wsp_pri_final_tab.COUNT;
683 if l_count > 0 then
684 l_index := g_wsp_pri_final_tab.FIRST;
685 WHILE l_index IS NOT NULL
686 LOOP
687 hr_utility.set_location('g_wsp_pri_final_tab(l_index).legal_entity_id '||g_wsp_pri_final_tab(l_index).legal_entity_id,40);
688 hr_utility.set_location('skills_priority_name'||g_wsp_pri_final_tab(l_index).skills_priority_name,40);
689 hr_utility.set_location('year '||g_wsp_pri_final_tab(l_index).year,40);
690 hr_utility.set_location('set_wsp_atr_final_tabs: l_index:' ||l_index,40);
691 l_index := g_wsp_pri_final_tab.NEXT(l_index); -- get subscript of next element
692 END LOOP;
693 end if;
694 --AM
695
696 if l_count > 0 then
697 l_index := g_wsp_pri_final_tab.FIRST;
698 for i in 1..l_count
699 loop
700 select substr(l_index,16,15) into l_skills_pri_id from dual;
701
702 hr_utility.set_location('l_skills_pri_id ' || l_skills_pri_id, 10);
703 --
704 for j in g_wsp_priority_tab.FIRST..g_wsp_priority_tab.LAST
705 loop
706 hr_utility.set_location('Step 1', 10);
707 if (l_skills_pri_id = g_wsp_priority_tab(j).skills_priority_id
708 and g_wsp_pri_final_tab(l_index).legal_entity_id = g_wsp_priority_tab(j).legal_entity_id) then
709 hr_utility.set_location('Inside Loop :'||j,10);
710 if g_wsp_priority_tab(j).level_number is null then
711 g_wsp_pri_final_tab(l_index).Unknown := g_wsp_pri_final_tab(l_index).Unknown + 1;
712 else
713 CASE g_wsp_priority_tab(j).level_number
714 WHEN '1' THEN g_wsp_pri_final_tab(l_index).Level_1 := g_wsp_pri_final_tab(l_index).Level_1 + 1;
715 WHEN '2' THEN g_wsp_pri_final_tab(l_index).Level_2 := g_wsp_pri_final_tab(l_index).Level_2 + 1;
716 WHEN '3' THEN g_wsp_pri_final_tab(l_index).Level_3 := g_wsp_pri_final_tab(l_index).Level_3 + 1;
717 WHEN '4' THEN g_wsp_pri_final_tab(l_index).Level_4 := g_wsp_pri_final_tab(l_index).Level_4 + 1;
718 WHEN '5' THEN g_wsp_pri_final_tab(l_index).Level_5 := g_wsp_pri_final_tab(l_index).Level_5 + 1;
719 WHEN '6' THEN g_wsp_pri_final_tab(l_index).Level_6 := g_wsp_pri_final_tab(l_index).Level_6 + 1;
720 WHEN '7' THEN g_wsp_pri_final_tab(l_index).Level_7 := g_wsp_pri_final_tab(l_index).Level_7 + 1;
721 WHEN '8' THEN g_wsp_pri_final_tab(l_index).Level_8 := g_wsp_pri_final_tab(l_index).Level_8 + 1;
722 END CASE;
723 end if;
724 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Unknown: '||g_wsp_pri_final_tab(l_index).Unknown,20);
725 if g_wsp_priority_tab(j).saqa_id is not null then
726 g_wsp_pri_final_tab(l_index).SAQA_Registered := g_wsp_pri_final_tab(l_index).SAQA_Registered + 1;
727 if g_wsp_pri_final_tab(l_index).SAQA_Ids is not null then
728 g_wsp_pri_final_tab(l_index).SAQA_Ids := g_wsp_pri_final_tab(l_index).SAQA_Ids || ','
729 || g_wsp_priority_tab(j).saqa_id;
730 else -- for first saqa id
731 g_wsp_pri_final_tab(l_index).SAQA_Ids := g_wsp_priority_tab(j).saqa_id;
732 end if;
733 else
734 g_wsp_pri_final_tab(l_index).Not_Registered := g_wsp_pri_final_tab(l_index).Not_Registered + 1;
735 end if;
736 end if;
737 end loop;
738 if i < l_count then
739 l_index := g_wsp_pri_final_tab.NEXT(l_index);
740 end if;
741 end loop;
742 end if;
743 --ATR tables
744 hr_utility.set_location('g_atr_pri_final_tab.COUNT : '||g_atr_pri_final_tab.COUNT,20);
745 hr_utility.set_location('g_atr_pri_final_tab.FIRST : '||g_atr_pri_final_tab.FIRST,20);
746 hr_utility.set_location('g_atr_pri_final_tab.LAST : '||g_atr_pri_final_tab.LAST,20);
747 l_count := g_atr_pri_final_tab.COUNT;
748 if l_count > 0 then
749 l_index := g_atr_pri_final_tab.FIRST;
750 for i in 1..l_count
751 loop
752 hr_utility.set_location('Step 0', 20);
753 select substr(l_index,16,15) into l_skills_pri_id from dual;
754 hr_utility.set_location('l_skills_pri_id ' || l_skills_pri_id, 20);
755 for j in g_atr_priority_tab.FIRST..g_atr_priority_tab.LAST
756 loop
757 hr_utility.set_location('Step 1: g_atr_priority_tab : '||j, 20);
758 if (l_skills_pri_id = g_atr_priority_tab(j).skills_priority_id
759 and g_atr_pri_final_tab(l_index).legal_entity_id = g_atr_priority_tab(j).legal_entity_id) then
760 if g_atr_priority_tab(j).level_number is null then
761 g_atr_pri_final_tab(l_index).Unknown := g_atr_pri_final_tab(l_index).Unknown + 1;
762 else
763 CASE g_atr_priority_tab(j).level_number
764 WHEN '1' THEN g_atr_pri_final_tab(l_index).Level_1 := g_atr_pri_final_tab(l_index).Level_1 + 1;
765 WHEN '2' THEN g_atr_pri_final_tab(l_index).Level_2 := g_atr_pri_final_tab(l_index).Level_2 + 1;
766 WHEN '3' THEN g_atr_pri_final_tab(l_index).Level_3 := g_atr_pri_final_tab(l_index).Level_3 + 1;
767 WHEN '4' THEN g_atr_pri_final_tab(l_index).Level_4 := g_atr_pri_final_tab(l_index).Level_4 + 1;
768 WHEN '5' THEN g_atr_pri_final_tab(l_index).Level_5 := g_atr_pri_final_tab(l_index).Level_5 + 1;
769 WHEN '6' THEN g_atr_pri_final_tab(l_index).Level_6 := g_atr_pri_final_tab(l_index).Level_6 + 1;
770 WHEN '7' THEN g_atr_pri_final_tab(l_index).Level_7 := g_atr_pri_final_tab(l_index).Level_7 + 1;
771 WHEN '8' THEN g_atr_pri_final_tab(l_index).Level_8 := g_atr_pri_final_tab(l_index).Level_8 + 1;
772 END CASE;
773 end if;
774 if g_atr_priority_tab(j).saqa_id is not null then
775 g_atr_pri_final_tab(l_index).SAQA_Registered := g_atr_pri_final_tab(l_index).SAQA_Registered + 1;
776 if g_atr_pri_final_tab(l_index).SAQA_Ids is not null then
777 g_atr_pri_final_tab(l_index).SAQA_Ids := g_atr_pri_final_tab(l_index).SAQA_Ids || ','
778 || g_atr_priority_tab(j).saqa_id;
779 else
780 g_atr_pri_final_tab(l_index).SAQA_Ids := g_atr_priority_tab(j).saqa_id; -- for first saqa id
781 end if;
782 else
783 hr_utility.set_location('Not Registered '||g_atr_pri_final_tab(l_index).Not_Registered,20);
784 g_atr_pri_final_tab(l_index).Not_Registered := g_atr_pri_final_tab(l_index).Not_Registered + 1;
785 hr_utility.set_location('Not Registered '||g_atr_pri_final_tab(l_index).Not_Registered,20);
786 end if;
787 end if;
788 hr_utility.set_location('g_atr_pri_final_tab(l_index).Unknown: '||g_atr_pri_final_tab(l_index).Unknown,20);
789 end loop;
790 if i < l_count then
791 l_index := g_atr_pri_final_tab.NEXT(l_index);
792 end if;
793 end loop;
794 end if;
795 -- start For debug only
796 hr_utility.trace('Print all WSP Competences and Priority Combinations');
797 l_index := g_wsp_compt_pri_tab.first;
798 WHILE l_index IS NOT NULL LOOP
799 hr_utility.set_location(l_index,30);
800 l_index := g_wsp_compt_pri_tab.NEXT(l_index);
801 END LOOP;
802 --
803 hr_utility.trace('Print all ATR Competences and Priority Combinations');
804 l_index := g_atr_compt_pri_tab.first;
805 WHILE l_index IS NOT NULL LOOP
806 hr_utility.set_location(l_index,30);
807 l_index := g_atr_compt_pri_tab.NEXT(l_index);
808 END LOOP;
809 --
810 hr_utility.trace('Print all WSP Courses and Priority Combinations');
811 l_index := g_wsp_course_pri_tab.first;
812 WHILE l_index IS NOT NULL LOOP
813 hr_utility.set_location(l_index,30);
814 l_index := g_wsp_course_pri_tab.NEXT(l_index);
815 END LOOP;
816 --
817 hr_utility.trace('Print all ATR Courses and Priority Combinations');
818 l_index := g_atr_course_pri_tab.first;
819 WHILE l_index IS NOT NULL LOOP
820 hr_utility.set_location(l_index,30);
821 l_index := g_atr_course_pri_tab.NEXT(l_index);
822 END LOOP;
823
824 -- end For debug only
825 hr_utility.trace('Leaving ' || l_proc);
826
827 end set_wsp_atr_final_tabs;
828
829 /*--------------------------------------------------------------------------
830 Procedure to set global_table
831 is it called from init
832 --------------------------------------------------------------------------*/
833
834 procedure set_global_tables is
835
836 cursor csr_get_priority(p_lookup_type varchar2
837 , p_year varchar2) is
838 select Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,
839 Attribute7,Attribute8,Attribute9,Attribute10, Attribute11,
840 Attribute12, Attribute13, Attribute14, Attribute15,
841 substr(lookup_code,5) lookup_code -- trng event id
842 from fnd_lookup_values
843 where lookup_type = p_lookup_type
844 and lookup_code like p_year || '%'
845 and security_group_id = fnd_global.lookup_security_group(p_lookup_type,3)
846 and attribute_category = G_ATTRIBUTE_CATEGORY
847 and (
848 Attribute1 || Attribute2 || Attribute3 || Attribute4 || Attribute5 ||
849 Attribute6 || Attribute7 || Attribute8 || Attribute9 || Attribute10 ||
850 Attribute11|| Attribute12|| Attribute13|| Attribute14 || Attribute15
851 ) is not null;
852
853 -- Get the Competences Linked to a Course
854 cursor csr_get_course_compts(p_course_id number) is
855 select pce.competence_id
856 from per_competence_elements pce
857 where pce.type = 'DELIVERY'
858 and pce.activity_version_id = p_course_id
859 and pce.business_group_id = g_bg_id;
860
861 -- Get the Competences Linked to a Learning Path
862 cursor csr_get_lp_compts(p_lp_id number) is
863 select pce.competence_id
864 from per_competence_elements pce
865 where pce.type = 'OTA_LEARNING_PATH'
866 and pce.object_id = p_lp_id
867 and pce.business_group_id = g_bg_id;
868
869 -- Get the Competences Linked to a Certification
870 cursor csr_get_cert_compts(p_cert_id number) is
871 select pce.competence_id
872 from per_competence_elements pce
873 where pce.type = 'OTA_CERTIFICATION'
874 and pce.object_id = p_cert_id
875 and pce.business_group_id = g_bg_id;
876
877 -- Get the Courses Linked to a Learning Path
878 cursor csr_get_lp_courses(p_learning_path_id number) is
879 select olpm.activity_version_id
880 from ota_learning_paths olp
881 , ota_lp_sections olps
882 , ota_learning_path_members olpm
883 where olp.learning_path_id = p_learning_path_id
884 and olp.learning_path_id = olps.learning_path_id
885 and olps.learning_path_section_id = olpm.learning_path_section_id;
886
887 -- Get the Courses Linked to a Certification
888 cursor csr_get_cert_courses(p_cert_id number) is
889 select ocm.object_id
890 from OTA_CERTIFICATION_MEMBERS ocm
891 , OTA_CERTIFICATIONS_B oc
892 where oc.certification_id = p_cert_id
893 and ocm.certification_id = oc.certification_id
894 and ocm.object_type = 'H';
895 --
896 -- Exists cursors
897 --
898 cursor csr_exists_course_compts(p_course_id number) is
899 select count(pce.competence_id)
900 from per_competence_elements pce
901 where pce.type = 'DELIVERY'
902 and pce.activity_version_id = p_course_id
903 and pce.business_group_id = g_bg_id;
904 --
905 --
906 type lookup_table is table of varchar2(80) index by BINARY_INTEGER ;
907
908 lookup_list lookup_table;
909 --
910 i number;
911 l_index varchar2(40);
912 l_year number;
913 l_effective_date date;
914 l_proc constant varchar2(60) := g_package || 'set_global_tables';
915 l_trng_event_id number;
916 l_trng_event_cat varchar2(80);
917 l_trng_links_flag boolean;
918 l_exists_course_compt number;
919 --AM
920 l_count number;
921 --AM
922 begin
923 hr_utility.trace('Entering '||l_proc );
924 -- Reset all the pl/sql tables
925 reset_tables;
926 --
927 lookup_list(1):= g_wsp_courses_lookup;
928 lookup_list(2):= g_wsp_lpath_lookup;
929 lookup_list(3):= g_wsp_cert_lookup;
930 lookup_list(4):= g_atr_comp_lookup;
931 lookup_list(5):= g_atr_qual_lookup;
932 lookup_list(6):= g_atr_courses_lookup;
933 lookup_list(7):= g_atr_lpath_lookup;
934 lookup_list(8):= g_atr_cert_lookup;
935
936 for i in 1.. 8
937 loop
938 hr_utility.set_location('Entering loop 1' ,10);
939 /* Condition to assign plan year/ trained year */
940 if instr(lookup_list(i),'WSP') > 0 then
941 l_year := to_char(g_wsp_end_date,'YYYY');
942 l_effective_date := g_wsp_end_date;
943 elsif instr(lookup_list(i),'ATR') > 0 then
944 l_year := to_char(g_atr_end_date,'YYYY');
945 l_effective_date := g_atr_end_date;
946 else
947 l_year := null;
948 end if;
949 for rec_priority in csr_get_priority
950 ( lookup_list(i)
951 , l_year
952 )
953 loop
954 hr_utility.set_location('Entering rec_priority - lookup_code : '|| rec_priority.lookup_code,10);
955 hr_utility.set_location('lookup_list(i) = '|| lookup_list(i),10);
956 hr_utility.set_location('l_effective_date = '|| to_char(l_effective_date,'DD-MON-YYYY'),10);
957 --
958 set_wsp_atr_tables(rec_priority.lookup_code,lookup_list(i),l_effective_date);
959 end loop;
960 hr_utility.set_location('Exiting loop 1' ,10);
961 end loop;
962 --
963 -- For priorities and trng event mapping ( A2 and B2)
964 i := 0;
965 for i in 1.. 8
966 loop
967 hr_utility.set_location('Entering loop 2' ,20);
968 /* Condition to assign plan year/ trained year */
969 if instr(lookup_list(i),'WSP') > 0 then
970 l_year := to_char(g_wsp_end_date,'YYYY');
971 l_effective_date := g_wsp_end_date;
972 elsif instr(lookup_list(i),'ATR') > 0 then
973 l_year := to_char(g_atr_end_date,'YYYY');
974 l_effective_date := g_atr_end_date;
975 else
976 l_year := null;
977 end if;
978 for rec_priority in csr_get_priority
979 ( lookup_list(i)
980 , l_year
981 )
982 loop
983 hr_utility.set_location('Entering rec_priority',20);
984 l_trng_links_flag := FALSE;
985 l_trng_event_id := rec_priority.lookup_code;
986 l_trng_event_cat := lookup_list(i);
987 -- can remove these trace statements later
988 hr_utility.set_location('Initial l_trng_event_id : '||l_trng_event_id,20);
989 hr_utility.set_location('Initial l_trng_event_cat : '||l_trng_event_cat,20);
990 hr_utility.set_location('Initial rec_priority.Attribute2 : '||rec_priority.Attribute2,20);
991
992 -- Start: ATR and WSP Courses which have competencies linked to them
993 if lookup_list(i) = g_wsp_courses_lookup then
994 for rec_get_course_compt in csr_get_course_compts(rec_priority.lookup_code)
995 loop
996 --set the flag to true
997 l_trng_links_flag := TRUE;
998 l_trng_event_id := rec_get_course_compt.competence_id;
999 l_trng_event_cat := g_wsp_comp_lookup;
1000 --
1001 hr_utility.set_location('Entering WSP rec_get_course_compt :',30);
1002 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1003 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1004 end if;
1005 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1006 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1007 end if;
1008 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1009 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1010 end if;
1011 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1012 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1013 end if;
1014 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1015 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1016 end if;
1017 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1018 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1019 end if;
1020 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1021 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1022 end if;
1023 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1024 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1025 end if;
1026 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1027 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1028 end if;
1029 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1030 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1031 end if;
1032 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1033 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1034 end if;
1035 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1036 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1037 end if;
1038 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1039 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1040 end if;
1041 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1042 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1043 end if;
1044 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1045 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1046 end if;
1047 end loop;
1048 elsif lookup_list(i) = g_atr_courses_lookup then
1049 for rec_get_course_compt in csr_get_course_compts(rec_priority.lookup_code)
1050 loop
1051 --set the flag to true
1052 hr_utility.set_location('Entering ATR rec_get_course_compt :',40);
1053 l_trng_links_flag := TRUE;
1054 l_trng_event_id := rec_get_course_compt.competence_id;
1055 l_trng_event_cat := g_atr_comp_lookup;
1056 hr_utility.set_location('l_trng_event_id :'||l_trng_event_id,40);
1057 hr_utility.set_location('rec_priority.Attribute1 :'||rec_priority.Attribute1,40);
1058 hr_utility.set_location('rec_priority.Attribute2 :'||rec_priority.Attribute2,40);
1059 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1060 hr_utility.set_location('g_atr_compt_pri_tab.count : '||g_atr_compt_pri_tab.count,401);
1061 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1062 end if;
1063 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1064 hr_utility.set_location('g_atr_compt_pri_tab.count : '||g_atr_compt_pri_tab.count,402);
1065 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1066 end if;
1067 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1068 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1069 end if;
1070 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1071 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1072 end if;
1073 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1074 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1075 end if;
1076 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1077 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1078 end if;
1079 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1080 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1081 end if;
1082 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1083 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1084 end if;
1085 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1086 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1087 end if;
1088 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1089 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1090 end if;
1091 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1092 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1093 end if;
1094 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1095 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1096 end if;
1097 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1098 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1099 end if;
1100 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1101 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1102 end if;
1103 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1104 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1105 end if;
1106 end loop;
1107 end if;
1108 -- End: WSP and ATR Courses which have competencies linked to them
1109 --
1110 -- Start: WSP and ATR Learning Paths which have Courses/Competences linked to them
1111 if lookup_list(i) = g_wsp_lpath_lookup then
1112 for rec_get_lp_compt in csr_get_lp_compts(rec_priority.lookup_code)
1113 loop
1114 hr_utility.set_location('Entering WSP rec_get_lp_compt :',50);
1115 --set the flag to true
1116 l_trng_links_flag := TRUE;
1117 l_trng_event_id := rec_get_lp_compt.competence_id;
1118 l_trng_event_cat := g_wsp_comp_lookup;
1119 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1120 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1121 end if;
1122 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1123 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1124 end if;
1125 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1126 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1127 end if;
1128 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1129 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1130 end if;
1131 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1132 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1133 end if;
1134 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1135 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1136 end if;
1137 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1138 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1139 end if;
1140 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1141 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1142 end if;
1143 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1144 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1145 end if;
1146 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1147 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1148 end if;
1149 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1150 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1151 end if;
1152 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1153 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1154 end if;
1155 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1156 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1157 end if;
1158 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1159 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1160 end if;
1161 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1162 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1163 end if;
1164 end loop;
1165 --
1166 -- loop through each course that is attached to the LP
1167 for rec_get_lp_courses in csr_get_lp_courses(rec_priority.lookup_code)
1168 loop
1169 hr_utility.set_location('Entering WSP rec_get_lp_courses :',50);
1170 --set the flag to true
1171 l_trng_links_flag := TRUE;
1172 --check if the course has any competences linked to it
1173 l_exists_course_compt := 0;
1174 open csr_exists_course_compts(rec_get_lp_courses.activity_version_id);
1175 fetch csr_exists_course_compts into l_exists_course_compt;
1176 close csr_exists_course_compts;
1177 --
1178 if l_exists_course_compt > 0 then
1179 for rec_get_course_compt in csr_get_course_compts(rec_get_lp_courses.activity_version_id)
1180 loop
1181 l_trng_event_id := rec_get_course_compt.competence_id;
1182 l_trng_event_cat := g_wsp_comp_lookup;
1183 --
1184 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1185 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1186 end if;
1187 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1188 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1189 end if;
1190 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1191 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1192 end if;
1193 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1194 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1195 end if;
1196 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1197 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1198 end if;
1199 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1200 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1201 end if;
1202 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1203 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1204 end if;
1205 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1206 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1207 end if;
1208 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1209 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1210 end if;
1211 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1212 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1213 end if;
1214 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1215 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1216 end if;
1217 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1218 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1219 end if;
1220 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1221 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1222 end if;
1223 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1224 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1225 end if;
1226 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1227 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1228 end if;
1229 end loop;
1230 else
1231 --Cater for the non-NQF aligned courses which are attached to the LP
1232 hr_utility.set_location('Entering WSP non-NQF aligned linked to LP :',50);
1233 l_trng_event_id := rec_get_lp_courses.activity_version_id;
1234 l_trng_event_cat := g_wsp_courses_lookup;
1235 --
1236 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1237 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1238 end if;
1239 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1240 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1241 end if;
1242 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1243 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1244 end if;
1245 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1246 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1247 end if;
1248 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1249 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1250 end if;
1251 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1252 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1253 end if;
1254 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1255 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1256 end if;
1257 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1258 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1259 end if;
1260 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1261 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1262 end if;
1263 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1264 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1265 end if;
1266 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1267 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1268 end if;
1269 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1270 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1271 end if;
1272 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1273 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1274 end if;
1275 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1276 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1277 end if;
1278 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1279 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1280 end if;
1281 end if;
1282 end loop;
1283 end if;
1284 -- WSP part ends : LP
1285 -- ATR part Starts :LP
1286 if lookup_list(i) = g_atr_lpath_lookup then
1287 -- delete the record from lp pl/sql table
1288 --
1289 for rec_get_lp_compt in csr_get_lp_compts(rec_priority.lookup_code)
1290 loop
1291 hr_utility.set_location('Entering ATR rec_get_lp_compt :',60);
1292 --set the flag to true
1293 l_trng_links_flag := TRUE;
1294 l_trng_event_id := rec_get_lp_compt.competence_id;
1295 l_trng_event_cat := g_atr_comp_lookup;
1296 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1297 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1298 end if;
1299 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1300 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1301 end if;
1302 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1303 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1304 end if;
1305 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1306 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1307 end if;
1308 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1309 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1310 end if;
1311 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1312 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1313 end if;
1314 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1315 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1316 end if;
1317 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1318 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1319 end if;
1320 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1321 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1322 end if;
1323 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1324 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1325 end if;
1326 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1327 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1328 end if;
1329 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1330 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1331 end if;
1332 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1333 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1334 end if;
1335 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1336 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1337 end if;
1338 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1339 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1340 end if;
1341 end loop;
1342 --
1343 -- loop through each course that is attached to the LP
1344 for rec_get_lp_courses in csr_get_lp_courses(rec_priority.lookup_code)
1345 loop
1346 hr_utility.set_location('Entering ATR rec_get_lp_courses :',60);
1347 --set the flag to true
1348 l_trng_links_flag := TRUE;
1349 --check if the course has any competences linked to it
1350 l_exists_course_compt := 0;
1351 open csr_exists_course_compts(rec_get_lp_courses.activity_version_id);
1352 fetch csr_exists_course_compts into l_exists_course_compt;
1353 close csr_exists_course_compts;
1354 --
1355 if l_exists_course_compt > 0 then
1356 for rec_get_course_compt in csr_get_course_compts(rec_get_lp_courses.activity_version_id)
1357 loop
1358 l_trng_event_id := rec_get_course_compt.competence_id;
1359 l_trng_event_cat := g_atr_comp_lookup;
1360 --
1361 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1362 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1363 end if;
1364 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1365 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1366 end if;
1367 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1368 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1369 end if;
1370 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1371 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1372 end if;
1373 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1374 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1375 end if;
1376 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1377 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1378 end if;
1379 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1380 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1381 end if;
1382 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1383 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1384 end if;
1385 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1386 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1387 end if;
1388 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1389 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1390 end if;
1391 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1392 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1393 end if;
1394 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1395 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1396 end if;
1397 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1398 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1399 end if;
1400 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1401 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1402 end if;
1403 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1404 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1405 end if;
1406 end loop;
1407 else
1408 --Cater for the non-NQF aligned courses which are attached to the LP
1409 hr_utility.set_location('Entering ATR non-NQF aligned courses linked to LP :',60);
1410 l_trng_event_id := rec_get_lp_courses.activity_version_id;
1411 l_trng_event_cat := g_atr_courses_lookup;
1412 --
1413 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1414 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1415 end if;
1416 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1417 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1418 end if;
1419 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1420 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1421 end if;
1422 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1423 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1424 end if;
1425 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1426 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1427 end if;
1428 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1429 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1430 end if;
1431 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1432 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1433 end if;
1434 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1435 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1436 end if;
1437 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1438 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1439 end if;
1440 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1441 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1442 end if;
1443 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1444 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1445 end if;
1446 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1447 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1448 end if;
1449 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1450 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1451 end if;
1452 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1453 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1454 end if;
1455 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1456 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1457 end if;
1458 end if;
1459 end loop;
1460 -- remove the lp from the LP pl/sql table
1461 if l_trng_links_flag = TRUE and g_atr_l_paths_tab.exists(rec_priority.lookup_code) then
1462 g_atr_l_paths_tab.delete(rec_priority.lookup_code);
1463 end if;
1464 end if;
1465 -- ATR Part ends : LP
1466 -- End : WSP and ATR Learning Paths which have Courses/Competences linked to them
1467 --
1468 -- Start: WSP and ATR Certifications which have Courses/Competences linked to them
1469 if lookup_list(i) = g_wsp_cert_lookup then
1470 -- delete the record from lp pl/sql table
1471 --
1472 for rec_get_cert_compt in csr_get_cert_compts(rec_priority.lookup_code)
1473 loop
1474 hr_utility.set_location('Entering WSP rec_get_cert_compt :',70);
1475 -- set the flag to true
1476 l_trng_links_flag := TRUE;
1477 l_trng_event_id := rec_get_cert_compt.competence_id;
1478 l_trng_event_cat := g_wsp_comp_lookup;
1479 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1480 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1481 end if;
1482 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1483 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1484 end if;
1485 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1486 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1487 end if;
1488 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1489 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1490 end if;
1491 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1492 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1493 end if;
1494 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1495 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1496 end if;
1497 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1498 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1499 end if;
1500 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1501 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1502 end if;
1503 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1504 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1505 end if;
1506 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1507 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1508 end if;
1509 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1510 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1511 end if;
1512 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1513 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1514 end if;
1515 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1516 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1517 end if;
1518 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1519 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1520 end if;
1521 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1522 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1523 end if;
1524 end loop; -- end loop for csr_get_cert_compts()
1525 --
1526 -- loop through each course that is attached to the Certification
1527 for rec_get_cert_courses in csr_get_cert_courses(rec_priority.lookup_code)
1528 loop
1529 hr_utility.set_location('Entering WSP rec_get_cert_courses :',70);
1530 -- set the flag to true
1531 l_trng_links_flag := TRUE;
1532 --check if the course has any competences linked to it
1533 l_exists_course_compt := 0;
1534 open csr_exists_course_compts(rec_get_cert_courses.object_id);
1535 fetch csr_exists_course_compts into l_exists_course_compt;
1536 close csr_exists_course_compts;
1537 --
1538 if l_exists_course_compt > 0 then
1539 for rec_get_course_compt in csr_get_course_compts(rec_get_cert_courses.object_id)
1540 loop
1541 l_trng_event_id := rec_get_course_compt.competence_id;
1542 l_trng_event_cat := g_wsp_comp_lookup;
1543 --
1544 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1545 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1546 end if;
1547 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1548 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1549 end if;
1550 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1551 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1552 end if;
1553 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1554 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1555 end if;
1556 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1557 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1558 end if;
1559 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1560 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1561 end if;
1562 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1563 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1564 end if;
1565 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1566 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1567 end if;
1568 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1569 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1570 end if;
1571 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1572 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1573 end if;
1574 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1575 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1576 end if;
1577 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1578 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1579 end if;
1580 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1581 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1582 end if;
1583 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1584 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1585 end if;
1586 if NOT(g_wsp_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1587 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1588 end if;
1589 end loop; -- end loop for csr_get_course_compts()
1590 else
1591 --Cater for the non-NQF aligned courses which are attached to the Certification
1592 hr_utility.set_location('Entering WSP non-NQF aligned courses linked to Cert :',70);
1593 l_trng_event_id := rec_get_cert_courses.object_id;
1594 l_trng_event_cat := g_wsp_courses_lookup;
1595 --
1596 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1597 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1598 end if;
1599 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1600 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1601 end if;
1602 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1603 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1604 end if;
1605 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1606 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1607 end if;
1608 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1609 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1610 end if;
1611 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1612 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1613 end if;
1614 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1615 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1616 end if;
1617 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1618 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1619 end if;
1620 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1621 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1622 end if;
1623 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1624 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1625 end if;
1626 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1627 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1628 end if;
1629 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1630 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1631 end if;
1632 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1633 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1634 end if;
1635 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1636 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1637 end if;
1638 if NOT(g_wsp_course_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1639 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1640 end if;
1641 end if;
1642 end loop; -- end loop for csr_get_cert_courses()
1643 end if;
1644 -- WSP part ends : Cert
1645 -- ATR part Starts : Cert
1646 if lookup_list(i) = g_atr_cert_lookup then
1647 -- delete the record from lp pl/sql table
1648 --
1649 for rec_get_cert_compt in csr_get_cert_compts(rec_priority.lookup_code)
1650 loop
1651 hr_utility.set_location('Entering ATR rec_get_cert_compt :',80);
1652 -- set the flag to true
1653 l_trng_links_flag := TRUE;
1654 l_trng_event_id := rec_get_cert_compt.competence_id;
1655 l_trng_event_cat := g_atr_comp_lookup;
1656 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1657 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1658 end if;
1659 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1660 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1661 end if;
1662 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1663 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1664 end if;
1665 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1666 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1667 end if;
1668 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1669 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1670 end if;
1671 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1672 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1673 end if;
1674 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1675 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1676 end if;
1677 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1678 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1679 end if;
1680 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1681 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1682 end if;
1683 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1684 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1685 end if;
1686 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1687 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1688 end if;
1689 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1690 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1691 end if;
1692 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1693 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1694 end if;
1695 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1696 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1697 end if;
1698 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1699 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1700 end if;
1701 end loop; -- end loop for csr_get_cert_compts()
1702 --
1703 -- loop through each course that is attached to the Certification
1704 for rec_get_cert_courses in csr_get_cert_courses(rec_priority.lookup_code)
1705 loop
1706 hr_utility.set_location('Entering ATR rec_get_cert_courses :',80);
1707 -- set the flag to true
1708 l_trng_links_flag := TRUE;
1709 --check if the course has any competences linked to it
1710 l_exists_course_compt := 0;
1711 open csr_exists_course_compts(rec_get_cert_courses.object_id);
1712 fetch csr_exists_course_compts into l_exists_course_compt;
1713 close csr_exists_course_compts;
1714 --
1715 if l_exists_course_compt > 0 then
1716 for rec_get_course_compt in csr_get_course_compts(rec_get_cert_courses.object_id)
1717 loop
1718 l_trng_event_id := rec_get_course_compt.competence_id;
1719 l_trng_event_cat := g_atr_comp_lookup;
1720 --
1721 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1722 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1723 end if;
1724 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1725 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1726 end if;
1727 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1728 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1729 end if;
1730 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1731 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1732 end if;
1733 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1734 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1735 end if;
1736 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1737 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1738 end if;
1739 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1740 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1741 end if;
1742 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1743 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1744 end if;
1745 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1746 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1747 end if;
1748 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1749 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1750 end if;
1751 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1752 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1753 end if;
1754 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1755 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1756 end if;
1757 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1758 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1759 end if;
1760 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1761 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1762 end if;
1763 if NOT(g_atr_compt_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1764 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1765 end if;
1766 end loop; -- end loop for csr_get_course_compts()
1767 else
1768 --Cater for the non-NQF aligned courses which are attached to the Certification
1769 hr_utility.set_location('Entering ATR non-NQF aligned courses linked to Cert :',80);
1770 l_trng_event_id := rec_get_cert_courses.object_id;
1771 l_trng_event_cat := g_atr_courses_lookup;
1772 --
1773 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute1||'_'||l_trng_event_id)) then
1774 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1775 end if;
1776 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute2||'_'||l_trng_event_id)) then
1777 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1778 end if;
1779 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute3||'_'||l_trng_event_id)) then
1780 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1781 end if;
1782 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute4||'_'||l_trng_event_id)) then
1783 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1784 end if;
1785 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute5||'_'||l_trng_event_id)) then
1786 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1787 end if;
1788 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute6||'_'||l_trng_event_id)) then
1789 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1790 end if;
1791 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute7||'_'||l_trng_event_id)) then
1792 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1793 end if;
1794 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute8||'_'||l_trng_event_id)) then
1795 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1796 end if;
1797 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute9||'_'||l_trng_event_id)) then
1798 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1799 end if;
1800 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute10||'_'||l_trng_event_id)) then
1801 set_wsp_atr_pri_tabs(rec_priority.Attribute10 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1802 end if;
1803 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute11||'_'||l_trng_event_id)) then
1804 set_wsp_atr_pri_tabs(rec_priority.Attribute11 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1805 end if;
1806 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute12||'_'||l_trng_event_id)) then
1807 set_wsp_atr_pri_tabs(rec_priority.Attribute12 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1808 end if;
1809 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute13||'_'||l_trng_event_id)) then
1810 set_wsp_atr_pri_tabs(rec_priority.Attribute13 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1811 end if;
1812 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute14||'_'||l_trng_event_id)) then
1813 set_wsp_atr_pri_tabs(rec_priority.Attribute14 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1814 end if;
1815 if NOT(g_atr_course_pri_tab.exists(rec_priority.Attribute15||'_'||l_trng_event_id)) then
1816 set_wsp_atr_pri_tabs(rec_priority.Attribute15 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1817 end if;
1818 end if;
1819 end loop; -- end loop for csr_get_cert_courses()
1820 if l_trng_links_flag = TRUE and g_atr_certifications_tab.exists(rec_priority.lookup_code) then
1821 g_atr_certifications_tab.delete(rec_priority.lookup_code);
1822 end if;
1823 end if;
1824 -- ATR part ends : Cert
1825 -- End: WSP and ATR Certifications which have Courses/Competences linked to them
1826 --
1827 -- caters for all the cases not already covered above
1828 hr_utility.set_location('Before Entering Others ',85);
1829 hr_utility.set_location('lookup_list(i) : '||lookup_list(i) ,85);
1830 if ( lookup_list(i) = g_atr_comp_lookup OR lookup_list(i) = g_atr_qual_lookup OR l_trng_links_flag = FALSE) then
1831 --
1832 hr_utility.set_location('Entering Others ',90);
1833 hr_utility.set_location('lookup_list(i) : '||lookup_list(i),90);
1834 --
1835 set_wsp_atr_pri_tabs(rec_priority.Attribute1 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1836 set_wsp_atr_pri_tabs(rec_priority.Attribute2 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1837 set_wsp_atr_pri_tabs(rec_priority.Attribute3 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1838 set_wsp_atr_pri_tabs(rec_priority.Attribute4 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1839 set_wsp_atr_pri_tabs(rec_priority.Attribute5 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1840 set_wsp_atr_pri_tabs(rec_priority.Attribute6 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1841 set_wsp_atr_pri_tabs(rec_priority.Attribute7 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1842 set_wsp_atr_pri_tabs(rec_priority.Attribute8 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1843 set_wsp_atr_pri_tabs(rec_priority.Attribute9 ,l_trng_event_id,l_trng_event_cat,l_effective_date);
1844 set_wsp_atr_pri_tabs(rec_priority.Attribute10,l_trng_event_id,l_trng_event_cat,l_effective_date);
1845 set_wsp_atr_pri_tabs(rec_priority.Attribute11,l_trng_event_id,l_trng_event_cat,l_effective_date);
1846 set_wsp_atr_pri_tabs(rec_priority.Attribute12,l_trng_event_id,l_trng_event_cat,l_effective_date);
1847 set_wsp_atr_pri_tabs(rec_priority.Attribute13,l_trng_event_id,l_trng_event_cat,l_effective_date);
1848 set_wsp_atr_pri_tabs(rec_priority.Attribute14,l_trng_event_id,l_trng_event_cat,l_effective_date);
1849 set_wsp_atr_pri_tabs(rec_priority.Attribute15,l_trng_event_id,l_trng_event_cat,l_effective_date);
1850 end if;
1851 end loop;
1852 hr_utility.set_location('Exiting loop 2' ,20);
1853 end loop;
1854 -- Setup the tables for A2 and B2
1855 set_wsp_atr_final_tabs;
1856 --
1857 g_wsp_courses_tab.delete;
1858 --g_wsp_l_paths_tab.delete;
1859 --g_wsp_certifications_tab.delete;
1860 g_atr_courses_tab.delete;
1861 --g_atr_l_paths_tab.delete;
1862 --g_atr_certifications_tab.delete;
1863 g_atr_competences_tab.delete;
1864 --g_atr_qualifications_tab.delete;
1865 --
1866 g_wsp_priority_tab.delete;
1867 g_atr_priority_tab.delete;
1868
1869 g_pl_tab_end := 'Y';
1870 --
1871 /*
1872 -- debug
1873 l_count := g_wsp_pri_final_tab.COUNT;
1874 if l_count > 0 then
1875 for i in 1..l_count
1876 loop
1877 l_index := g_wsp_pri_final_tab.FIRST;
1878 hr_utility.set_location('g_wsp_pri_final_tab(l_index).legal_entity_id '||g_wsp_pri_final_tab(l_index).legal_entity_id,40);
1879 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_name'||g_wsp_pri_final_tab(l_index).skills_priority_name,40);
1880 hr_utility.set_location('g_wsp_pri_final_tab(l_index).year '||g_wsp_pri_final_tab(l_index).year,40);
1881 if i < l_count then
1882 l_index := g_wsp_pri_final_tab.NEXT(l_index);
1883 end if;
1884 end loop;
1885 end if;
1886 -- debug
1887 */
1888 hr_utility.trace('Leaving '||l_proc);
1889 /*exception
1890 when no_data_found then
1891 hr_utility.trace('No Data Found: Please Run the Workplace Skills Plan Set Up and Maintenance and do the necessary setup' ||
1892 to_char(sqlcode));
1893 hr_utility.raise_error;*/
1894 end set_global_tables;
1895
1896 --
1897 --
1898 /*--------------------------------------------------------------------------
1899 Name : get_parameter
1900 Purpose : Returns a legislative parameter
1901 Arguments :
1902 Notes : The legislative parameter field must be of the form:
1903 PARAMETER_NAME=PARAMETER_VALUE. No spaces is allowed in either
1904 the PARAMETER_NAME or the PARAMETER_VALUE.
1905 --------------------------------------------------------------------------*/
1906 function get_parameter
1907 (
1908 name in varchar2,
1909 parameter_list in varchar2
1910 ) return varchar2 is
1911
1912 start_ptr number;
1913 end_ptr number;
1914 token_val pay_payroll_actions.legislative_parameters%type;
1915 par_value pay_payroll_actions.legislative_parameters%type;
1916 l_proc constant varchar2(60) := g_package || 'get_parameter';
1917
1918 begin
1919 hr_utility.trace('Entering '|| l_proc);
1920
1921 token_val := name || '=';
1922
1923 start_ptr := instr(parameter_list, token_val) + length(token_val);
1924 end_ptr := instr(parameter_list, ' ', start_ptr);
1925
1926 /* if there is no spaces, then use the length of the string */
1927 if end_ptr = 0 then
1928 end_ptr := length(parameter_list) + 1;
1929 end if;
1930
1931 /* Did we find the token */
1932 if instr(parameter_list, token_val) = 0 then
1933 par_value := NULL;
1934 else
1935 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1936 end if;
1937
1938 return par_value;
1939 hr_utility.trace('Leaving ' || l_proc);
1940 end get_parameter;
1941
1942 --
1943 /*--------------------------------------------------------------------------
1944 Name : range_cursor
1945 Purpose : This returns the select statement that is used to created the
1946 range rows.
1947 Arguments :
1948 Notes : The range cursor determines which people should be processed.
1949 The normal practice is to include everyone, and then limit
1950 the list during the assignment action creation.
1951 --------------------------------------------------------------------------*/
1952 procedure range_cursor
1953 (
1954 pactid in number,
1955 sqlstr out nocopy varchar2
1956 ) is
1957
1958 l_proc varchar2(100);
1959 l_legal_entity_id varchar2(60);
1960 l_leg_param varchar2(1000);
1961 l_tmp_char varchar2(30);
1962
1963
1964 l_ovn number;
1965 l_action_info_id number;
1966 l_tmp_flag boolean;
1967 l_seta_name varchar2(150);
1968 l_sic_code varchar2(150);
1969
1970
1971 l_index varchar(60);
1972 l_count number;
1973 l_count_temp number;
1974
1975 --
1976 -- To fetch employer contact details
1977 cursor csr_le_contact_info(p_legal_entity_id in number) is
1978 Select hoi1.org_information1 org_name -- A(1).1 Organization Name
1979 , hoi.org_information4 post_add_line_1 -- A(1).2 Postal Address
1980 , hoi.org_information5 post_add_line_2
1981 , hoi.org_information6 post_add_line_3
1982 , hoi.org_information8 post_town_or_city
1983 , hoi.org_information7 post_postal_code
1984 , hoi.org_information9 post_province
1985 , hoi.org_information10 phy_add_line_1 -- A(1).3 Physical Address
1986 , hoi.org_information11 phy_add_line_2
1987 , hoi.org_information12 phy_add_line_3
1988 , hoi.org_information14 phy_town_or_city
1989 , hoi.org_information13 phy_postal_code
1990 , hoi.org_information15 phy_province
1991 , hoi.org_information1 tel_no -- A(1).5 Telephone number
1992 , hoi.org_information2 fax_no -- A(1).6 Fax number
1993 , hoi.org_information3 email_add -- A(1).7 E-mail Address
1994 From hr_all_organization_units haou
1995 , hr_organization_information hoi
1996 , hr_organization_information hoi1
1997 Where haou.business_group_id = g_bg_id
1998 and haou.organization_id = p_legal_entity_id
1999 and haou.organization_id = hoi1.organization_id
2000 and hoi.org_information_context(+) = 'ZA_LEGAL_ENTITY_CONTACT_INFO'
2001 and hoi.organization_id(+) = haou.organization_id
2002 and hoi1.org_information_context = 'ZA_LEGAL_ENTITY';
2003
2004 cursor csr_seta_info(p_legal_entity_id in number) is
2005 select hoi.org_information1 SETA_Name
2006 , hoi.org_information3 activity_name
2007 from hr_all_organization_units haou
2008 , hr_organization_information hoi
2009 where haou.business_group_id = g_bg_id
2010 and haou.organization_id = p_legal_entity_id
2011 and haou.organization_id = hoi.organization_id
2012 and hoi.org_information_context = 'ZA_NQF_SETA_INFO';
2013 /*
2014 -- To fetch employer banking and other details
2015 cursor csr_le_bank_info is
2016 Select 'sdl_number' sdl_num -- A(1).4 Skills Development Levy Number
2017 , 'bank_name' bank_name -- A(1).8 Banking details
2018 , 'bank_add_line_1' bank_add_line_1
2019 , 'bank_add_line_2' bank_add_line_2
2020 , 'bank_add_line_3' bank_add_line_3
2021 , 'bank_town_or_city' bank_town_or_city
2022 , 'bank_postal_code' bank_postal_code
2023 , 'bank_province' bank_province
2024 , 'sic_code' sic_code -- A(1).9 Main business activity
2025 , 'total_employement' tot_emp -- A(1).10 Total Employment
2026 , 'Tot Ann Payroll' tot_prev_ann_pay -- A(1).11 Total prev fin-year annual payroll
2027 From dual;
2028 */
2029 -- To fetch SDF details
2030 cursor csr_sdf_info(p_legal_entity_id in number) is
2031 select hoi.org_information1 sdf_name
2032 , hoi.org_information2 sdf_add_line_1
2033 , hoi.org_information3 sdf_add_line_2
2034 , hoi.org_information4 sdf_add_line_3
2035 , hoi.org_information5 sdf_add_line_4
2036 , hoi.org_information6 sdf_town_or_city
2037 , hoi.org_information7 sdf_province
2038 , hoi.org_information8 sdf_postal_code
2039 , hoi.org_information9 sdf_telephone_no
2040 , hoi.org_information10 sdf_fax_no
2041 , hoi.org_information11 sdf_email_id
2042 , hoi.org_information12 sdf_mobile_no
2043 from hr_all_organization_units haou
2044 , hr_organization_information hoi
2045 where haou.business_group_id = g_bg_id
2046 and haou.organization_id = p_legal_entity_id
2047 and haou.organization_id = hoi.organization_id
2048 and hoi.org_information_context = 'ZA_SDF_INFO';
2049 --
2050 begin
2051 --hr_utility.trace_on(null,'ZAWSP');
2052 l_proc := g_package || 'range_cursor';
2053 hr_utility.trace ('Entering '||l_proc);
2054 --
2055 hr_utility.set_location('payroll_action_id = '||pactid,10);
2056 --
2057 -- set the global legislative parameters
2058 Select business_group_id
2059 Into g_bg_id -- Business Group Id
2060 From pay_payroll_actions
2061 Where payroll_action_id = pactid ;
2062
2063 select ppa.legislative_parameters
2064 into l_leg_param
2065 from pay_payroll_actions ppa
2066 where payroll_action_id = pactid;
2067
2068 l_tmp_char := get_parameter('PLAN_YEAR', l_leg_param);
2069 l_legal_entity_id := get_parameter('LEGAL_ENTITY_ID', l_leg_param);
2070
2071 -- Get the effective date of the payroll action
2072 Select effective_date
2073 Into g_archive_effective_date
2074 From pay_payroll_actions
2075 Where payroll_action_id = pactid;
2076
2077 -- get_parameters(pactid, 'LEGAL_ENTITY_ID', l_tmp_char);
2078 if (l_legal_entity_id is null) then
2079 g_legal_entity_id := null;
2080 else
2081 g_legal_entity_id := to_number(l_legal_entity_id); -- Type cast to number
2082 end if;
2083
2084
2085 -- set the plan and training year start and end dates
2086 g_plan_year := fnd_number.canonical_to_number(l_tmp_char);
2087
2088 g_wsp_start_date := to_date('01-04-'||(g_plan_year-1) , 'DD-MM-YYYY');
2089 g_wsp_end_date := to_date('31-03-'||g_plan_year , 'DD-MM-YYYY');
2090
2091 g_atr_start_date := to_date('01-04-'||(g_plan_year-2) , 'DD-MM-YYYY');
2092 g_atr_end_date := to_date('31-03-'||(g_plan_year-1) , 'DD-MM-YYYY');
2093
2094 hr_utility.set_location('g_legal_entity_id = '||g_legal_entity_id,10);
2095 hr_utility.set_location('g_archive_effective_date = '||g_archive_effective_date,10);
2096 hr_utility.set_location('g_wsp_start_date = '||g_wsp_start_date,10);
2097 hr_utility.set_location('g_wsp_end_date = '||g_wsp_end_date,10);
2098 hr_utility.set_location('g_atr_start_date = '||g_atr_start_date,10);
2099 hr_utility.set_location('g_atr_end_date = '||g_atr_end_date,10);
2100
2101 -- set the payroll_action_id
2102 g_pactid := pactid;
2103 -----------------
2104 -- calling to set the global tables
2105 set_global_tables;
2106 --
2107
2108 -- debug
2109 l_count := g_wsp_pri_final_tab.COUNT;
2110 if l_count > 0 then
2111 l_index := g_wsp_pri_final_tab.FIRST;
2112 WHILE l_index IS NOT NULL
2113 LOOP
2114 hr_utility.set_location('g_wsp_pri_final_tab(l_index).legal_entity_id '||g_wsp_pri_final_tab(l_index).legal_entity_id,40);
2115 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_name'||g_wsp_pri_final_tab(l_index).skills_priority_name,40);
2116 hr_utility.set_location('g_wsp_pri_final_tab(l_index).year '||g_wsp_pri_final_tab(l_index).year,40);
2117 hr_utility.set_location('range_cursor: l_index:' ||l_index,40);
2118 l_index := g_wsp_pri_final_tab.NEXT(l_index); -- get subscript of next element
2119 END LOOP;
2120 end if;
2121 -- debug
2122
2123 --Archive the Legal Entity Contact Details
2124 l_tmp_flag := true;
2125 if g_legal_entity_id is not null then
2126 hr_utility.set_location('g_legal_entity_id = '||g_legal_entity_id,10);
2127 for le_info_rec1 in csr_le_contact_info(g_legal_entity_id)
2128 loop
2129 hr_utility.set_location('Archiving ZA WSP Employer Contact Details', 10);
2130 l_tmp_flag := false;
2131 open csr_seta_info(g_legal_entity_id);
2132 fetch csr_seta_info into l_seta_name,l_sic_code;
2133 close csr_seta_info;
2134 hr_utility.set_location('g_archive_effective_date = '||to_char(g_archive_effective_date,'DD-MON-YYYY'),10);
2135 hr_utility.set_location('le_info_rec1.org_name = '|| le_info_rec1.org_name,10);
2136 hr_utility.set_location('le_info_rec1.post_add_line_1 = '||le_info_rec1.post_add_line_1 ,10);
2137 hr_utility.set_location('le_info_rec1.post_add_line_2 = '||le_info_rec1.post_add_line_2 ,10);
2138 hr_utility.set_location('le_info_rec1.post_add_line_3 = '||le_info_rec1.post_add_line_3 ,10);
2139 -- hr_utility.set_location('le_info_rec1.post_add_line_4 = '||le_info_rec1.post_add_line_4 ,10);
2140 hr_utility.set_location('le_info_rec1.post_town_or_city = '||le_info_rec1.post_town_or_city ,10);
2141 hr_utility.set_location('le_info_rec1.post_postal_code = '||le_info_rec1.post_postal_code ,10);
2142 hr_utility.set_location('le_info_rec1.post_province = '||le_info_rec1.post_province ,10);
2143 hr_utility.set_location('le_info_rec1.phy_add_line_1 = '||le_info_rec1.phy_add_line_1 ,10);
2144 hr_utility.set_location('le_info_rec1.phy_add_line_2 = '||le_info_rec1.phy_add_line_2 ,10);
2145 hr_utility.set_location('le_info_rec1.phy_add_line_3 = '||le_info_rec1.phy_add_line_3 ,10);
2146 -- hr_utility.set_location('le_info_rec1.phy_add_line_4 = '||le_info_rec1.phy_add_line_4 ,10);
2147 hr_utility.set_location('le_info_rec1.phy_town_or_city = '||le_info_rec1.phy_town_or_city ,10);
2148 hr_utility.set_location('le_info_rec1.phy_postal_code = '||le_info_rec1.phy_postal_code ,10);
2149 hr_utility.set_location('le_info_rec1.phy_province = '||le_info_rec1.phy_province ,10);
2150 hr_utility.set_location('le_info_rec1.tel_no = '||le_info_rec1.tel_no ,10);
2151 hr_utility.set_location('le_info_rec1.fax_no = '||le_info_rec1.fax_no ,10);
2152 hr_utility.set_location('le_info_rec1.email_add = '||le_info_rec1.email_add ,10);
2153 hr_utility.set_location('l_seta_name = '||l_seta_name ,10);
2154 hr_utility.set_location('l_sic_code = '||l_sic_code ,10);
2155
2156
2157 -- Archive 'ZA WSP EMPLOYER DETAILS'
2158 pay_action_information_api.create_action_information
2159 (
2160 p_action_information_id => l_action_info_id
2161 , p_action_context_id => pactid
2162 , p_action_context_type => 'PA'
2163 , p_object_version_number => l_ovn
2164 , p_effective_date => g_archive_effective_date
2165 , p_action_information_category => 'ZA WSP EMPLOYER DETAILS'
2166 , p_action_information1 => g_bg_id -- Business GROUP Id
2167 , p_action_information2 => g_legal_entity_id -- Legal Entity Id
2168 , p_action_information3 => le_info_rec1.org_name -- A(1).1 Organization Name
2169 , p_action_information4 => le_info_rec1.post_add_line_1 -- A(1).2 Postal Address
2170 , p_action_information5 => le_info_rec1.post_add_line_2
2171 , p_action_information6 => le_info_rec1.post_add_line_3
2172 -- , p_action_information7 => le_info_rec1.post_add_line_4 -- Reserved for Address line 4
2173 , p_action_information8 => le_info_rec1.post_town_or_city
2174 , p_action_information9 => le_info_rec1.post_postal_code
2175 , p_action_information10 => le_info_rec1.post_province
2176 , p_action_information11 => le_info_rec1.phy_add_line_1 -- A(1).3 Physical Address
2177 , p_action_information12 => le_info_rec1.phy_add_line_2
2178 , p_action_information13 => le_info_rec1.phy_add_line_3
2179 -- , p_action_information14 => le_info_rec1.phy_add_line_4 -- Reserved for Address line 4
2180 , p_action_information15 => le_info_rec1.phy_town_or_city
2181 , p_action_information16 => le_info_rec1.phy_postal_code
2182 , p_action_information17 => le_info_rec1.phy_province
2183 , p_action_information18 => le_info_rec1.tel_no -- A(1).5 Telephone number
2184 , p_action_information19 => le_info_rec1.fax_no -- A(1).6 Fax number
2185 , p_action_information20 => le_info_rec1.email_add -- A(1).7 E-mail Address
2186 , p_action_information21 => l_seta_name -- Seta Name
2187 , p_action_information22 => l_sic_code
2188 );
2189 end loop;
2190 else
2191 for rec_le_id in g_csr_le
2192 loop
2193 for le_info_rec1 in csr_le_contact_info(rec_le_id.organization_id)
2194 loop
2195 hr_utility.set_location('Archiving ZA WSP Employer Contact Details', 20);
2196 l_tmp_flag := false;
2197 open csr_seta_info(rec_le_id.organization_id);
2198 fetch csr_seta_info into l_seta_name,l_sic_code;
2199 close csr_seta_info;
2200
2201 -- Archive 'ZA WSP EMPLOYER DETAILS'
2202 pay_action_information_api.create_action_information
2203 (
2204 p_action_information_id => l_action_info_id
2205 , p_action_context_id => pactid
2206 , p_action_context_type => 'PA'
2207 , p_object_version_number => l_ovn
2208 , p_effective_date => g_archive_effective_date
2209 , p_action_information_category => 'ZA WSP EMPLOYER DETAILS'
2210 , p_action_information1 => g_bg_id -- Business GROUP Id
2211 , p_action_information2 => rec_le_id.organization_id -- Legal Entity Id
2212 , p_action_information3 => le_info_rec1.org_name -- A(1).1 Organization Name
2213 , p_action_information4 => le_info_rec1.post_add_line_1 -- A(1).2 Postal Address
2214 , p_action_information5 => le_info_rec1.post_add_line_2
2215 , p_action_information6 => le_info_rec1.post_add_line_3
2216 -- , p_action_information7 => le_info_rec1.post_add_line_4 -- Reserved for Address line 4
2217 , p_action_information8 => le_info_rec1.post_town_or_city
2218 , p_action_information9 => le_info_rec1.post_postal_code
2219 , p_action_information10 => le_info_rec1.post_province
2220 , p_action_information11 => le_info_rec1.phy_add_line_1 -- A(1).3 Physical Address
2221 , p_action_information12 => le_info_rec1.phy_add_line_2
2222 , p_action_information13 => le_info_rec1.phy_add_line_3
2223 -- , p_action_information14 => le_info_rec1.phy_add_line_4 -- Reserved for Address line 4
2224 , p_action_information15 => le_info_rec1.phy_town_or_city
2225 , p_action_information16 => le_info_rec1.phy_postal_code
2226 , p_action_information17 => le_info_rec1.phy_province
2227 , p_action_information18 => le_info_rec1.tel_no -- A(1).5 Telephone number
2228 , p_action_information19 => le_info_rec1.fax_no -- A(1).6 Fax number
2229 , p_action_information20 => le_info_rec1.email_add -- A(1).7 E-mail Address
2230 , p_action_information21 => l_seta_name -- Seta Name
2231 , p_action_information22 => l_sic_code
2232 );
2233 end loop;
2234 end loop;
2235 end if;
2236 --
2237 if l_tmp_flag = true
2238 then
2239 hr_utility.set_location('ZA WSP EMPLOYER CONTACT DETAILS does not exist', 20);
2240 end if;
2241 --
2242 -- BANK DETAILS WILL NOT BE REPORTED .... CUSTOMERS NEED TO FILL IT MANUALLY
2243 -- Archive Skills Development Facilitator(SDF) details
2244 l_tmp_flag := true;
2245 if g_legal_entity_id is not null then
2246 for le_sdf_rec3 in csr_sdf_info(g_legal_entity_id)
2247 loop
2248 hr_utility.set_location('Archiving ZA WSP SDF Details', 20);
2249 l_tmp_flag := false;
2250 hr_utility.set_location('g_archive_effective_date = '||to_char(g_archive_effective_date,'DD-MON-YYYY'),20);
2251 hr_utility.set_location('le_sdf_rec3.sdf_name = '||le_sdf_rec3.sdf_name ,20);
2252 hr_utility.set_location('le_sdf_rec3.sdf_add_line_1 = '||le_sdf_rec3.sdf_add_line_1 ,20);
2253 hr_utility.set_location('le_sdf_rec3.sdf_add_line_2 = '||le_sdf_rec3.sdf_add_line_2 ,20);
2254 hr_utility.set_location('le_sdf_rec3.sdf_add_line_3 = '||le_sdf_rec3.sdf_add_line_3 ,20);
2255 hr_utility.set_location('le_sdf_rec3.sdf_add_line_4 = '||le_sdf_rec3.sdf_add_line_4 ,20);
2256 hr_utility.set_location('le_sdf_rec3.sdf_town_or_city = '||le_sdf_rec3.sdf_town_or_city ,20);
2257 hr_utility.set_location('le_sdf_rec3.sdf_province = '||le_sdf_rec3.sdf_province ,20);
2258 hr_utility.set_location('le_sdf_rec3.sdf_postal_code = '||le_sdf_rec3.sdf_postal_code ,20);
2259 hr_utility.set_location('le_sdf_rec3.sdf_telephone_no = '||le_sdf_rec3.sdf_telephone_no ,20);
2260 hr_utility.set_location('le_sdf_rec3.sdf_fax_no = '||le_sdf_rec3.sdf_fax_no ,20);
2261 hr_utility.set_location('le_sdf_rec3.sdf_email_id = '||le_sdf_rec3.sdf_email_id ,20);
2262 hr_utility.set_location('le_sdf_rec3.sdf_mobile_no = '||le_sdf_rec3.sdf_mobile_no ,20);
2263
2264
2265 -- Archive 'ZA WSP SDF DETAILS'
2266 pay_action_information_api.create_action_information
2267 (
2268 p_action_information_id => l_action_info_id
2269 , p_action_context_id => pactid
2270 , p_action_context_type => 'PA'
2271 , p_object_version_number => l_ovn
2272 , p_effective_date => g_archive_effective_date
2273 , p_action_information_category => 'ZA WSP SDF DETAILS'
2274 , p_action_information1 => g_bg_id -- Business GROUP Id
2275 , p_action_information2 => g_legal_entity_id -- Legal Entity Id
2276 , p_action_information3 => le_sdf_rec3.sdf_name -- A(1).12 SDF Name
2277 , p_action_information4 => le_sdf_rec3.sdf_add_line_1 -- A(1).13 SDF Address
2278 , p_action_information5 => le_sdf_rec3.sdf_add_line_2
2279 , p_action_information6 => le_sdf_rec3.sdf_add_line_3
2280 , p_action_information7 => le_sdf_rec3.sdf_add_line_4
2281 , p_action_information8 => le_sdf_rec3.sdf_town_or_city
2282 , p_action_information9 => le_sdf_rec3.sdf_province
2283 , p_action_information10 => le_sdf_rec3.sdf_postal_code
2284 , p_action_information11 => le_sdf_rec3.sdf_telephone_no -- A(1).14 SDF contact details
2285 , p_action_information12 => le_sdf_rec3.sdf_fax_no
2286 , p_action_information13 => le_sdf_rec3.sdf_email_id
2287 , p_action_information14 => le_sdf_rec3.sdf_mobile_no
2288 );
2289 end loop;
2290 else
2291 for rec_le_id in g_csr_le
2292 loop
2293 for le_sdf_rec3 in csr_sdf_info(rec_le_id.organization_id)
2294 loop
2295 hr_utility.set_location('Archiving ZA WSP SDF Details', 30);
2296 l_tmp_flag := false;
2297 -- Archive 'ZA WSP SDF DETAILS'
2298 pay_action_information_api.create_action_information
2299 (
2300 p_action_information_id => l_action_info_id
2301 , p_action_context_id => pactid
2302 , p_action_context_type => 'PA'
2303 , p_object_version_number => l_ovn
2304 , p_effective_date => g_archive_effective_date
2305 , p_action_information_category => 'ZA WSP SDF DETAILS'
2306 , p_action_information1 => g_bg_id -- Business GROUP Id
2307 , p_action_information2 => rec_le_id.organization_id -- Legal Entity Id
2308 , p_action_information3 => le_sdf_rec3.sdf_name -- A(1).12 SDF Name
2309 , p_action_information4 => le_sdf_rec3.sdf_add_line_1 -- A(1).13 SDF Address
2310 , p_action_information5 => le_sdf_rec3.sdf_add_line_2
2311 , p_action_information6 => le_sdf_rec3.sdf_add_line_3
2312 , p_action_information7 => le_sdf_rec3.sdf_add_line_4
2313 , p_action_information8 => le_sdf_rec3.sdf_town_or_city
2314 , p_action_information9 => le_sdf_rec3.sdf_province
2315 , p_action_information10 => le_sdf_rec3.sdf_postal_code
2316 , p_action_information11 => le_sdf_rec3.sdf_telephone_no -- A(1).14 SDF contact details
2317 , p_action_information12 => le_sdf_rec3.sdf_fax_no
2318 , p_action_information13 => le_sdf_rec3.sdf_email_id
2319 , p_action_information14 => le_sdf_rec3.sdf_mobile_no
2320 );
2321 end loop;
2322 end loop;
2323 end if;
2324
2325 if l_tmp_flag = true
2326 then
2327 hr_utility.set_location('ZA WSP SDF DETAILS does not exist', 30);
2328 end if;
2329 --
2330 -- Archive Annual Education and Training Programs Provided
2331 --WSP
2332 hr_utility.set_location('Archive WSP Training Programs Provided',40);
2333 l_count := g_wsp_pri_final_tab.COUNT;
2334 hr_utility.set_location('l_count: '||l_count,40);
2335 if l_count > 0 then
2336 l_index := g_wsp_pri_final_tab.FIRST;
2337 WHILE l_index IS NOT NULL
2338 LOOP
2339 if g_legal_entity_id is not null then
2340 hr_utility.set_location('g_wsp_pri_final_tab(l_index).legal_entity_id: '||g_wsp_pri_final_tab(l_index).legal_entity_id,40);
2341 if g_wsp_pri_final_tab(l_index).legal_entity_id = g_legal_entity_id then
2342 hr_utility.set_location('g_bg_id = '||g_bg_id ,40);
2343 hr_utility.set_location('g_wsp_pri_final_tab(l_index).legal_entity_id = '||g_wsp_pri_final_tab(l_index).legal_entity_id ,40);
2344 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_num = '||g_wsp_pri_final_tab(l_index).skills_priority_num ,40);
2345 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_name = '||g_wsp_pri_final_tab(l_index).skills_priority_name,40);
2346 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_1 = '||g_wsp_pri_final_tab(l_index).Level_1 ,40);
2347 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_2 = '||g_wsp_pri_final_tab(l_index).Level_2 ,40);
2348 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_3 = '||g_wsp_pri_final_tab(l_index).Level_3 ,40);
2349 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_4 = '||g_wsp_pri_final_tab(l_index).Level_4 ,40);
2350 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_5 = '||g_wsp_pri_final_tab(l_index).Level_5 ,40);
2351 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_6 = '||g_wsp_pri_final_tab(l_index).Level_6 ,40);
2352 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_7 = '||g_wsp_pri_final_tab(l_index).Level_7 ,40);
2353 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_8 = '||g_wsp_pri_final_tab(l_index).Level_8 ,40);
2354 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Unknown = '||g_wsp_pri_final_tab(l_index).Unknown ,40);
2355 hr_utility.set_location('g_wsp_pri_final_tab(l_index).SAQA_Registered = '||g_wsp_pri_final_tab(l_index).SAQA_Registered ,40);
2356 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Not_Registered = '||g_wsp_pri_final_tab(l_index).Not_Registered ,40);
2357 hr_utility.set_location('g_wsp_pri_final_tab(l_index).SAQA_Ids = '||g_wsp_pri_final_tab(l_index).SAQA_Ids ,40);
2358 hr_utility.set_location('g_wsp_pri_final_tab(l_index).year = '||g_wsp_pri_final_tab(l_index).year ,40);
2359
2360 pay_action_information_api.create_action_information
2361 (
2362 p_action_information_id => l_action_info_id
2363 , p_action_context_id => pactid
2364 , p_action_context_type => 'PA'
2365 , p_object_version_number => l_ovn
2366 , p_effective_date => g_archive_effective_date
2367 , p_action_information_category => 'ZA WSP TRAINING PROGRAMS'
2368 , p_action_information1 => g_bg_id
2369 , p_action_information2 => g_wsp_pri_final_tab(l_index).legal_entity_id
2370 , p_action_information3 => g_wsp_pri_final_tab(l_index).skills_priority_num --Skills Priority Number
2371 , p_action_information4 => g_wsp_pri_final_tab(l_index).skills_priority_name --Skills Priority Name
2372 , p_action_information5 => g_wsp_pri_final_tab(l_index).Level_1 --Level 1
2373 , p_action_information6 => g_wsp_pri_final_tab(l_index).Level_2 --Level 2
2374 , p_action_information7 => g_wsp_pri_final_tab(l_index).Level_3 --Level 3
2375 , p_action_information8 => g_wsp_pri_final_tab(l_index).Level_4 --Level 4
2376 , p_action_information9 => g_wsp_pri_final_tab(l_index).Level_5 --Level 5
2377 , p_action_information10 => g_wsp_pri_final_tab(l_index).Level_6 --Level 6
2378 , p_action_information11 => g_wsp_pri_final_tab(l_index).Level_7 --Level 7
2379 , p_action_information12 => g_wsp_pri_final_tab(l_index).Level_8 --Level 8
2380 , p_action_information13 => g_wsp_pri_final_tab(l_index).Unknown --Unknown
2381 , p_action_information14 => g_wsp_pri_final_tab(l_index).SAQA_Registered --SAQA_Registered
2382 , p_action_information15 => g_wsp_pri_final_tab(l_index).Not_Registered --Not Registered
2383 , p_action_information16 => g_wsp_pri_final_tab(l_index).SAQA_Ids --SAQA Ids
2384 , p_action_information17 => g_wsp_pri_final_tab(l_index).year
2385 );
2386 end if;
2387 l_index := g_wsp_pri_final_tab.NEXT(l_index); -- get subscript of next element
2388 else
2389 hr_utility.set_location('g_bg_id = '||g_bg_id ,45);
2390 hr_utility.set_location('g_wsp_pri_final_tab(l_index).legal_entity_id = '||g_wsp_pri_final_tab(l_index).legal_entity_id ,45);
2391 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_num = '||g_wsp_pri_final_tab(l_index).skills_priority_num ,45);
2392 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_name = '||g_wsp_pri_final_tab(l_index).skills_priority_name,45);
2393 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_1 = '||g_wsp_pri_final_tab(l_index).Level_1 ,45);
2394 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_2 = '||g_wsp_pri_final_tab(l_index).Level_2 ,45);
2395 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_3 = '||g_wsp_pri_final_tab(l_index).Level_3 ,45);
2396 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_4 = '||g_wsp_pri_final_tab(l_index).Level_4 ,45);
2397 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_5 = '||g_wsp_pri_final_tab(l_index).Level_5 ,45);
2398 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_6 = '||g_wsp_pri_final_tab(l_index).Level_6 ,45);
2399 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_7 = '||g_wsp_pri_final_tab(l_index).Level_7 ,45);
2400 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Level_8 = '||g_wsp_pri_final_tab(l_index).Level_8 ,45);
2401 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Unknown = '||g_wsp_pri_final_tab(l_index).Unknown ,45);
2402 hr_utility.set_location('g_wsp_pri_final_tab(l_index).SAQA_Registered = '||g_wsp_pri_final_tab(l_index).SAQA_Registered ,45);
2403 hr_utility.set_location('g_wsp_pri_final_tab(l_index).Not_Registered = '||g_wsp_pri_final_tab(l_index).Not_Registered ,45);
2404 hr_utility.set_location('g_wsp_pri_final_tab(l_index).SAQA_Ids = '||g_wsp_pri_final_tab(l_index).SAQA_Ids ,45);
2405 hr_utility.set_location('g_wsp_pri_final_tab(l_index).year = '||g_wsp_pri_final_tab(l_index).year ,45);
2406
2407 pay_action_information_api.create_action_information
2408 (
2409 p_action_information_id => l_action_info_id
2410 , p_action_context_id => pactid
2411 , p_action_context_type => 'PA'
2412 , p_object_version_number => l_ovn
2413 , p_effective_date => g_archive_effective_date
2414 , p_action_information_category => 'ZA WSP TRAINING PROGRAMS'
2415 , p_action_information1 => g_bg_id
2416 , p_action_information2 => g_wsp_pri_final_tab(l_index).legal_entity_id
2417 , p_action_information3 => g_wsp_pri_final_tab(l_index).skills_priority_num --Skills Priority Number
2418 , p_action_information4 => g_wsp_pri_final_tab(l_index).skills_priority_name --Skills Priority Name
2419 , p_action_information5 => g_wsp_pri_final_tab(l_index).Level_1 --Level 1
2420 , p_action_information6 => g_wsp_pri_final_tab(l_index).Level_2 --Level 2
2421 , p_action_information7 => g_wsp_pri_final_tab(l_index).Level_3 --Level 3
2422 , p_action_information8 => g_wsp_pri_final_tab(l_index).Level_4 --Level 4
2423 , p_action_information9 => g_wsp_pri_final_tab(l_index).Level_5 --Level 5
2424 , p_action_information10 => g_wsp_pri_final_tab(l_index).Level_6 --Level 6
2425 , p_action_information11 => g_wsp_pri_final_tab(l_index).Level_7 --Level 7
2426 , p_action_information12 => g_wsp_pri_final_tab(l_index).Level_8 --Level 8
2427 , p_action_information13 => g_wsp_pri_final_tab(l_index).Unknown --Unknown
2428 , p_action_information14 => g_wsp_pri_final_tab(l_index).SAQA_Registered --SAQA_Registered
2429 , p_action_information15 => g_wsp_pri_final_tab(l_index).Not_Registered --Not Registered
2430 , p_action_information16 => g_wsp_pri_final_tab(l_index).SAQA_Ids --SAQA Ids
2431 , p_action_information17 => g_wsp_pri_final_tab(l_index).year
2432 );
2433
2434 l_index := g_wsp_pri_final_tab.NEXT(l_index); -- get subscript of next element
2435 end if;
2436 end loop;
2437 end if;
2438 --ATR
2439 hr_utility.set_location('Archive ATR Training Programs Provided',50);
2440 l_count := g_atr_pri_final_tab.COUNT;
2441 if l_count > 0 then
2442 l_index := g_atr_pri_final_tab.FIRST;
2443 hr_utility.set_location('l_index: '||l_index,50);
2444 WHILE l_index IS NOT NULL
2445 LOOP
2446 if g_legal_entity_id is not null then
2447 if g_atr_pri_final_tab(l_index).legal_entity_id = g_legal_entity_id then
2448 hr_utility.set_location('g_bg_id = '||g_bg_id ,50);
2449 hr_utility.set_location('g_atr_pri_final_tab(l_index).legal_entity_id = '||g_atr_pri_final_tab(l_index).legal_entity_id ,50);
2450 hr_utility.set_location('g_atr_pri_final_tab(l_index).skills_priority_num = '||g_atr_pri_final_tab(l_index).skills_priority_num ,50);
2451 hr_utility.set_location('g_atr_pri_final_tab(l_index).skills_priority_name = '||g_atr_pri_final_tab(l_index).skills_priority_name,50);
2452 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_1 = '||g_atr_pri_final_tab(l_index).Level_1 ,50);
2453 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_2 = '||g_atr_pri_final_tab(l_index).Level_2 ,50);
2454 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_3 = '||g_atr_pri_final_tab(l_index).Level_3 ,50);
2455 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_4 = '||g_atr_pri_final_tab(l_index).Level_4 ,50);
2456 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_5 = '||g_atr_pri_final_tab(l_index).Level_5 ,50);
2457 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_6 = '||g_atr_pri_final_tab(l_index).Level_6 ,50);
2458 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_7 = '||g_atr_pri_final_tab(l_index).Level_7 ,50);
2459 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_8 = '||g_atr_pri_final_tab(l_index).Level_8 ,50);
2460 hr_utility.set_location('g_atr_pri_final_tab(l_index).Unknown = '||g_atr_pri_final_tab(l_index).Unknown ,50);
2461 hr_utility.set_location('g_atr_pri_final_tab(l_index).SAQA_Registered = '||g_atr_pri_final_tab(l_index).SAQA_Registered ,50);
2462 hr_utility.set_location('g_atr_pri_final_tab(l_index).Not_Registered = '||g_atr_pri_final_tab(l_index).Not_Registered ,50);
2463 hr_utility.set_location('g_atr_pri_final_tab(l_index).SAQA_Ids = '||g_atr_pri_final_tab(l_index).SAQA_Ids ,50);
2464 hr_utility.set_location('g_atr_pri_final_tab(l_index).year = '||g_atr_pri_final_tab(l_index).year ,50);
2465
2466
2467 pay_action_information_api.create_action_information
2468 (
2469 p_action_information_id => l_action_info_id
2470 , p_action_context_id => pactid
2471 , p_action_context_type => 'PA'
2472 , p_object_version_number => l_ovn
2473 , p_effective_date => g_archive_effective_date
2474 , p_action_information_category => 'ZA ATR TRAINING PROGRAMS'
2475 , p_action_information1 => g_bg_id
2476 , p_action_information2 => g_atr_pri_final_tab(l_index).legal_entity_id
2477 , p_action_information3 => g_atr_pri_final_tab(l_index).skills_priority_num --Skills Priority Number
2478 , p_action_information4 => g_atr_pri_final_tab(l_index).skills_priority_name --Skills Priority Name
2479 , p_action_information5 => g_atr_pri_final_tab(l_index).Level_1 --Level 1
2480 , p_action_information6 => g_atr_pri_final_tab(l_index).Level_2 --Level 2
2481 , p_action_information7 => g_atr_pri_final_tab(l_index).Level_3 --Level 3
2482 , p_action_information8 => g_atr_pri_final_tab(l_index).Level_4 --Level 4
2483 , p_action_information9 => g_atr_pri_final_tab(l_index).Level_5 --Level 5
2484 , p_action_information10 => g_atr_pri_final_tab(l_index).Level_6 --Level 6
2485 , p_action_information11 => g_atr_pri_final_tab(l_index).Level_7 --Level 7
2486 , p_action_information12 => g_atr_pri_final_tab(l_index).Level_8 --Level 8
2487 , p_action_information13 => g_atr_pri_final_tab(l_index).Unknown --Unknown
2488 , p_action_information14 => g_atr_pri_final_tab(l_index).SAQA_Registered --SAQA_Registered
2489 , p_action_information15 => g_atr_pri_final_tab(l_index).Not_Registered --Not Registered
2490 , p_action_information16 => g_atr_pri_final_tab(l_index).SAQA_Ids --SAQA Ids
2491 , p_action_information17 => g_atr_pri_final_tab(l_index).year
2492 );
2493
2494 end if;
2495 l_index := g_atr_pri_final_tab.NEXT(l_index); -- get subscript of next element
2496 else
2497 hr_utility.set_location('g_bg_id = '||g_bg_id ,60);
2498 hr_utility.set_location('g_atr_pri_final_tab(l_index).legal_entity_id = '||g_atr_pri_final_tab(l_index).legal_entity_id ,60);
2499 hr_utility.set_location('g_atr_pri_final_tab(l_index).skills_priority_num = '||g_atr_pri_final_tab(l_index).skills_priority_num ,60);
2500 hr_utility.set_location('g_atr_pri_final_tab(l_index).skills_priority_name = '||g_atr_pri_final_tab(l_index).skills_priority_name,60);
2501 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_1 = '||g_atr_pri_final_tab(l_index).Level_1 ,60);
2502 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_2 = '||g_atr_pri_final_tab(l_index).Level_2 ,60);
2503 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_3 = '||g_atr_pri_final_tab(l_index).Level_3 ,60);
2504 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_4 = '||g_atr_pri_final_tab(l_index).Level_4 ,60);
2505 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_5 = '||g_atr_pri_final_tab(l_index).Level_5 ,60);
2506 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_6 = '||g_atr_pri_final_tab(l_index).Level_6 ,60);
2507 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_7 = '||g_atr_pri_final_tab(l_index).Level_7 ,60);
2508 hr_utility.set_location('g_atr_pri_final_tab(l_index).Level_8 = '||g_atr_pri_final_tab(l_index).Level_8 ,60);
2509 hr_utility.set_location('g_atr_pri_final_tab(l_index).Unknown = '||g_atr_pri_final_tab(l_index).Unknown ,60);
2510 hr_utility.set_location('g_atr_pri_final_tab(l_index).SAQA_Registered = '||g_atr_pri_final_tab(l_index).SAQA_Registered ,60);
2511 hr_utility.set_location('g_atr_pri_final_tab(l_index).Not_Registered = '||g_atr_pri_final_tab(l_index).Not_Registered ,60);
2512 hr_utility.set_location('g_atr_pri_final_tab(l_index).SAQA_Ids = '||g_atr_pri_final_tab(l_index).SAQA_Ids ,60);
2513 hr_utility.set_location('g_atr_pri_final_tab(l_index).year = '||g_atr_pri_final_tab(l_index).year ,60);
2514
2515
2516 pay_action_information_api.create_action_information
2517 (
2518 p_action_information_id => l_action_info_id
2519 , p_action_context_id => pactid
2520 , p_action_context_type => 'PA'
2521 , p_object_version_number => l_ovn
2522 , p_effective_date => g_archive_effective_date
2523 , p_action_information_category => 'ZA ATR TRAINING PROGRAMS'
2524 , p_action_information1 => g_bg_id
2525 , p_action_information2 => g_atr_pri_final_tab(l_index).legal_entity_id
2526 , p_action_information3 => g_atr_pri_final_tab(l_index).skills_priority_num --Skills Priority Number
2527 , p_action_information4 => g_atr_pri_final_tab(l_index).skills_priority_name --Skills Priority Name
2528 , p_action_information5 => g_atr_pri_final_tab(l_index).Level_1 --Level 1
2529 , p_action_information6 => g_atr_pri_final_tab(l_index).Level_2 --Level 2
2530 , p_action_information7 => g_atr_pri_final_tab(l_index).Level_3 --Level 3
2531 , p_action_information8 => g_atr_pri_final_tab(l_index).Level_4 --Level 4
2532 , p_action_information9 => g_atr_pri_final_tab(l_index).Level_5 --Level 5
2533 , p_action_information10 => g_atr_pri_final_tab(l_index).Level_6 --Level 6
2534 , p_action_information11 => g_atr_pri_final_tab(l_index).Level_7 --Level 7
2535 , p_action_information12 => g_atr_pri_final_tab(l_index).Level_8 --Level 8
2536 , p_action_information13 => g_atr_pri_final_tab(l_index).Unknown --Unknown
2537 , p_action_information14 => g_atr_pri_final_tab(l_index).SAQA_Registered --SAQA_Registered
2538 , p_action_information15 => g_atr_pri_final_tab(l_index).Not_Registered --Not Registered
2539 , p_action_information16 => g_atr_pri_final_tab(l_index).SAQA_Ids --SAQA Ids
2540 , p_action_information17 => g_atr_pri_final_tab(l_index).year
2541 );
2542
2543 l_index := g_atr_pri_final_tab.NEXT(l_index); -- get subscript of next element
2544 end if;
2545 end loop;
2546 end if;
2547 --
2548 --delete all pl/sql tables
2549 reset_tables;
2550 -----------------
2551 g_sql_range :=
2552 'select distinct asg.person_id
2553 from per_assignments_f asg,
2554 pay_payroll_actions ppa
2555 where ppa.payroll_action_id = :payroll_action_id
2556 and asg.business_group_id = ppa.business_group_id
2557 and asg.assignment_type = ''E''
2558 order by asg.person_id';
2559
2560 sqlstr := g_sql_range;
2561
2562 hr_utility.trace('Leaving ' || l_proc);
2563
2564 --hr_utility.trace_off;
2565 end range_cursor;
2566 --
2567
2568 /****************************************************************************
2569 Name : action_creation
2570 Arguments : p_payroll_action_id
2571 p_start_person_id
2572 p_end_person_id
2573 p_chunk_number
2574 Description : This procedure creates assignment actions for the
2575 payroll_action_id passed as parameter for a specific chunk.
2576 *****************************************************************************/
2577 procedure action_creation
2578 (
2579 pactid number,
2580 stperson number,
2581 endperson number,
2582 chunk number
2583 ) as
2584
2585 -- cursor to get all the valid assignments
2586 -- pick up only the primary assignment for a person
2587 --
2588 cursor csr_get_pri_asg (p_pactid number
2589 , p_stperson number
2590 , p_endperson number
2591 , p_plan_year_end_date date
2592 , p_legal_entity_id number) is
2593 select ppf.person_id
2594 , paa.assignment_id
2595 from per_all_people_f ppf
2596 , per_all_assignments_f paa
2597 , per_assignment_extra_info paei
2598 , pay_payroll_actions ppa_arch
2599 , per_periods_of_service pps
2600 where paa.business_group_id = g_bg_id
2601 and paa.person_id = ppf.person_id
2602 and ppf.person_id between p_stperson and p_endperson
2603 and paa.period_of_service_id = pps.period_of_service_id
2604 and paei.assignment_id = paa.assignment_id
2605 and paa.assignment_type = 'E'
2606 and paa.primary_flag = 'Y'
2607 -- and ppa_arch.payroll_id = paa.payroll_id -- payroll id isnt populated in R12
2608 and ppa_arch.payroll_action_id = p_pactid
2609 and paei.aei_information_category = 'ZA_SPECIFIC_INFO'
2610 and paei.aei_information7 = p_legal_entity_id -- support archive for one or all legal entities in that bg
2611 -- check if the person is active within the training and plan year
2612 and ppf.effective_start_date = ( select max(effective_start_date)
2613 from per_all_people_f ppf1
2614 where ppf1.person_id = ppf.person_id
2615 and ppf1.effective_start_date <= g_wsp_end_date
2616 and ppf1.effective_end_date >= g_atr_start_date
2617 )
2618 -- check if the asg is active within the training and plan year
2619 and paa.effective_start_date = ( select max(paa1.effective_start_date)
2620 from per_all_assignments_f paa1
2621 where paa1.assignment_id = paa.assignment_id
2622 and paa1.effective_start_date <= g_wsp_end_date
2623 and paa1.effective_end_date >= g_atr_start_date
2624 );
2625
2626 --
2627 l_proc varchar2(60);
2628 l_person_id number;
2629 lockingactid number;
2630 --
2631 --
2632 l_legal_entity_id varchar2(60);
2633 l_leg_param varchar2(1000);
2634 l_tmp_char varchar2(30);
2635 --
2636 begin
2637 --
2638 --
2639 --hr_utility.trace_on(null,'ZAWSP');
2640
2641 l_proc := g_package || 'action_creation';
2642 hr_utility.trace('Entering '||l_proc);
2643 ---
2644 -- set the global legislative parameters
2645 Select business_group_id
2646 Into g_bg_id -- Business Group Id
2647 From pay_payroll_actions
2648 Where payroll_action_id = pactid ;
2649
2650 select ppa.legislative_parameters
2651 into l_leg_param
2652 from pay_payroll_actions ppa
2653 where payroll_action_id = pactid;
2654
2655 l_tmp_char := get_parameter('PLAN_YEAR', l_leg_param);
2656 l_legal_entity_id := get_parameter('LEGAL_ENTITY_ID', l_leg_param);
2657
2658 -- Get the effective date of the payroll action
2659 Select effective_date
2660 Into g_archive_effective_date
2661 From pay_payroll_actions
2662 Where payroll_action_id = pactid;
2663
2664 -- get_parameters(pactid, 'LEGAL_ENTITY_ID', l_tmp_char);
2665 if (l_legal_entity_id is null) then
2666 g_legal_entity_id := null;
2667 else
2668 g_legal_entity_id := to_number(l_legal_entity_id); -- Type cast to number
2669 end if;
2670
2671
2672 -- set the plan and training year start and end dates
2673 g_plan_year := fnd_number.canonical_to_number(l_tmp_char);
2674
2675 g_wsp_start_date := to_date('01-04-'||(g_plan_year-1) , 'DD-MM-YYYY');
2676 g_wsp_end_date := to_date('31-03-'||g_plan_year , 'DD-MM-YYYY');
2677
2678 g_atr_start_date := to_date('01-04-'||(g_plan_year-2) , 'DD-MM-YYYY');
2679 g_atr_end_date := to_date('31-03-'||(g_plan_year-1) , 'DD-MM-YYYY');
2680
2681 hr_utility.set_location('g_legal_entity_id = '||g_legal_entity_id,10);
2682 hr_utility.set_location('g_archive_effective_date = '||g_archive_effective_date,10);
2683 hr_utility.set_location('g_wsp_start_date = '||g_wsp_start_date,10);
2684 hr_utility.set_location('g_wsp_end_date = '||g_wsp_end_date,10);
2685 hr_utility.set_location('g_atr_start_date = '||g_atr_start_date,10);
2686 hr_utility.set_location('g_atr_end_date = '||g_atr_end_date,10);
2687
2688 ---
2689 hr_utility.set_location('pactid = '|| pactid,10);
2690 hr_utility.set_location('stperson = '|| stperson,10);
2691 hr_utility.set_location('endperson = '|| endperson,10);
2692 hr_utility.set_location('chunk = '||chunk,10);
2693 hr_utility.set_location('g_wsp_end_date :'||to_char(g_wsp_end_date,'DD-MM-YYYY'),10);
2694 hr_utility.set_location('g_atr_start_date :'||to_char(g_atr_start_date,'DD-MM-YYYY'),10);
2695 --
2696 --
2697 if g_legal_entity_id is not null then
2698 for asgrec in csr_get_pri_asg (pactid, stperson, endperson, g_wsp_end_date, g_legal_entity_id )
2699 loop
2700 hr_utility.set_location('Entering asgrec',10);
2701 -- create an assignment action for the primary assignment
2702 select pay_assignment_actions_s.nextval
2703 into lockingactid
2704 from dual;
2705
2706 -- Insert assignment into pay_assignment_actions
2707 hr_nonrun_asact.insact
2708 (
2709 lockingactid,
2710 asgrec.assignment_id,
2711 pactid,
2712 chunk,
2713 null
2714 );
2715 end loop;
2716 else
2717 for rec_le_id in g_csr_le
2718 loop
2719 for asgrec in csr_get_pri_asg (pactid, stperson, endperson, g_wsp_end_date, rec_le_id.organization_id )
2720 loop
2721 hr_utility.set_location('Entering asgrec ',20);
2722 -- create an assignment action for the primary assignment
2723 select pay_assignment_actions_s.nextval
2724 into lockingactid
2725 from dual;
2726
2727 -- Insert assignment into pay_assignment_actions
2728 hr_nonrun_asact.insact
2729 (
2730 lockingactid,
2731 asgrec.assignment_id,
2732 pactid,
2733 chunk,
2734 null
2735 );
2736 end loop;
2737 end loop;
2738 end if;
2739 --
2740 hr_utility.trace('Leaving ' || l_proc);
2741 -- hr_utility.trace_off;
2742 --
2743 end action_creation;
2744
2745
2746 /****************************************************************************
2747 Name : archive_init
2748 Description : * Initialize global variables
2749 * Populate the eight global pl/sql tables for each
2750 category of prioritised training events.
2751 * Archive Company and SDF details
2752 Bank Details will NOT be archived and reported
2753 * Populate pl/sql for training event priorites
2754 * Archive A2 and B2.
2755 *****************************************************************************/
2756 procedure archive_init (pactid in number)
2757 is
2758
2759 l_proc varchar2(100);
2760 l_legal_entity_id varchar2(60);
2761 l_leg_param varchar2(1000);
2762 l_tmp_char varchar2(30);
2763
2764
2765 l_ovn number;
2766 l_action_info_id number;
2767 l_tmp_flag boolean;
2768 l_seta_name varchar2(150);
2769 l_sic_code varchar2(150);
2770
2771
2772 l_index varchar(60);
2773 l_count number;
2774 l_count_temp number;
2775
2776
2777 --
2778 begin
2779 -- hr_utility.trace_on(null,'ZAWSP');
2780 l_proc := g_package || 'archive_init';
2781 hr_utility.trace ('Entering '||l_proc);
2782 hr_utility.trace ('pactid '||pactid);
2783
2784
2785 -- set the global legislative parameters
2786 Select business_group_id
2787 Into g_bg_id -- Business Group Id
2788 From pay_payroll_actions
2789 Where payroll_action_id = pactid ;
2790
2791 select ppa.legislative_parameters
2792 into l_leg_param
2793 from pay_payroll_actions ppa
2794 where payroll_action_id = pactid;
2795
2796 l_tmp_char := get_parameter('PLAN_YEAR', l_leg_param);
2797 l_legal_entity_id := get_parameter('LEGAL_ENTITY_ID', l_leg_param);
2798
2799 -- Get the effective date of the payroll action
2800 Select effective_date
2801 Into g_archive_effective_date
2802 From pay_payroll_actions
2803 Where payroll_action_id = pactid;
2804
2805 -- get_parameters(pactid, 'LEGAL_ENTITY_ID', l_tmp_char);
2806 if (l_legal_entity_id is null) then
2807 g_legal_entity_id := null;
2808 else
2809 g_legal_entity_id := to_number(l_legal_entity_id); -- Type cast to number
2810 end if;
2811
2812 -- set the plan and training year start and end dates
2813 g_plan_year := fnd_number.canonical_to_number(l_tmp_char);
2814
2815 g_wsp_start_date := to_date('01-04-'||(g_plan_year-1) , 'DD-MM-YYYY');
2816 g_wsp_end_date := to_date('31-03-'||g_plan_year , 'DD-MM-YYYY');
2817
2818 g_atr_start_date := to_date('01-04-'||(g_plan_year-2) , 'DD-MM-YYYY');
2819 g_atr_end_date := to_date('31-03-'||(g_plan_year-1) , 'DD-MM-YYYY');
2820
2821 --set pl/sql table
2822 hr_utility.set_location('g_pl_tab_start : '||g_pl_tab_start,10);
2823 hr_utility.set_location('g_pl_tab_end : '||g_pl_tab_end,10);
2824 hr_utility.set_location('fnd_global.conc_request_id : '||fnd_global.conc_request_id,10);
2825
2826 if g_pl_tab_start <> 'Y' and g_pl_tab_end <> 'Y' then
2827 set_global_tables;
2828 end if;
2829 hr_utility.set_location('g_pl_tab_start : '||g_pl_tab_start,10);
2830 hr_utility.set_location('g_pl_tab_end : '||g_pl_tab_end,10);
2831
2832
2833 hr_utility.set_location('g_legal_entity_id = '||g_legal_entity_id,10);
2834 hr_utility.set_location('g_archive_effective_date = '||g_archive_effective_date,10);
2835 hr_utility.set_location('g_wsp_start_date = '||g_wsp_start_date,10);
2836 hr_utility.set_location('g_wsp_end_date = '||g_wsp_end_date,10);
2837 hr_utility.set_location('g_atr_start_date = '||g_atr_start_date,10);
2838 hr_utility.set_location('g_atr_end_date = '||g_atr_end_date,10);
2839
2840 hr_utility.set_location('g_wsp_pri_final_tab.count = '||g_wsp_pri_final_tab.count,10);
2841 hr_utility.set_location('g_atr_pri_final_tab.count = '||g_atr_pri_final_tab.count,10);
2842 hr_utility.set_location('g_wsp_compt_pri_tab.count = '||g_wsp_compt_pri_tab.count,10);
2843 hr_utility.set_location('g_wsp_certifications_tab.count = '||g_wsp_certifications_tab.count,10);
2844
2845 hr_utility.trace('Leaving '||l_proc);
2846 -- hr_utility.trace_off;
2847 end archive_init;
2848 --
2849 --
2850 /****************************************************************************
2851 Name : archive_wsp_data2
2852 Description : Archive person level WSP related data.
2853 *****************************************************************************/
2854 procedure archive_wsp_data2( assactid in number
2855 , p_person_id in per_all_assignments_f.person_id%type
2856 , p_assignment_id in per_all_assignments_f.assignment_id%type
2857 , p_race in per_all_people_f.per_information4%type
2858 , p_sex in per_all_people_f.sex%type
2859 , p_ass_cat_name in hr_lookups.meaning%type
2860 , p_skills_pri_id in number
2861 , p_trng_event_id in number
2862 , p_trng_event_name in varchar2
2863 , p_trng_event_lookup in varchar2
2864 , p_legal_entity_id in number
2865 , p_disability in varchar2
2866 ) is
2867 --variables
2868 l_index varchar2(40);
2869 l_action_info_id number;
2870 l_ovn number;
2871 l_proc varchar2(50);
2872 l_legal_entity_id varchar2(30);
2873 begin
2874 l_proc := g_package ||'archive_wsp_data2';
2875 hr_utility.set_location('Entering '||l_proc,10);
2876 select rpad(p_legal_entity_id,15,0) into l_legal_entity_id from dual;
2877 l_index := l_legal_entity_id ||p_skills_pri_id;
2878
2879 if g_wsp_pri_final_tab.exists(l_index) then
2880 hr_utility.set_location('skills_priority_name '||g_wsp_pri_final_tab(l_index).skills_priority_name,20);
2881 hr_utility.set_location('archive for person_id '||p_person_id,20);
2882 hr_utility.set_location('assactid : '||assactid,10);
2883 hr_utility.set_location('p_legal_entity_id : '||p_legal_entity_id,10);
2884 hr_utility.set_location('p_person_id : '||p_person_id,10);
2885 hr_utility.set_location('p_race : '||p_race,10);
2886 hr_utility.set_location('p_sex : '||p_sex,10);
2887 hr_utility.set_location('p_ass_cat_name : '||p_ass_cat_name,10);
2888 hr_utility.set_location('p_disability : '||p_disability,10);
2889 hr_utility.set_location('p_trng_event_id : '||p_trng_event_id,10);
2890 hr_utility.set_location('p_trng_event_name : '||p_trng_event_name,10);
2891 hr_utility.set_location('p_trng_event_lookup : '||p_trng_event_lookup,10);
2892 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_num : '||g_wsp_pri_final_tab(l_index).skills_priority_num,10);
2893 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_id : '||g_wsp_pri_final_tab(l_index).skills_priority_id,10);
2894 hr_utility.set_location('g_wsp_pri_final_tab(l_index).skills_priority_name: '||g_wsp_pri_final_tab(l_index).skills_priority_name,10);
2895 --hr_utility.set_location('p_status : '||p_status,10);
2896
2897
2898 pay_action_information_api.create_action_information
2899 (
2900 p_action_information_id => l_action_info_id
2901 , p_assignment_id => p_assignment_id
2902 , p_action_context_id => assactid
2903 , p_action_context_type => 'AAP'
2904 , p_object_version_number => l_ovn
2905 , p_effective_date => g_archive_effective_date
2906 , p_action_information_category => 'ZA WSP PERSON DETAILS'
2907 , p_action_information1 => g_bg_id -- Business GROUP Id
2908 , p_action_information2 => p_legal_entity_id
2909 , p_action_information3 => p_person_id
2910 , p_action_information4 => p_race
2911 , p_action_information5 => p_sex
2912 , p_action_information6 => p_ass_cat_name -- Occupation category
2913 , p_action_information7 => p_disability
2914 , p_action_information8 => p_trng_event_id -- trng event id course/competence/qualification/learningpath/certification
2915 , p_action_information9 => p_trng_event_name -- trng event Name
2916 , p_action_information10 => p_trng_event_lookup -- trng event category
2917 , p_action_information11 => g_wsp_pri_final_tab(l_index).skills_priority_num
2918 , p_action_information12 => g_wsp_pri_final_tab(l_index).skills_priority_id
2919 , p_action_information13 => g_wsp_pri_final_tab(l_index).skills_priority_name
2920 -- , p_action_information14 => null --Attended/Completed
2921 );
2922 end if;
2923 hr_utility.set_location('Leaving '||l_proc,10);
2924
2925 end archive_wsp_data2;
2926 --
2927 --
2928 /****************************************************************************
2929 Name : archive_wsp_data
2930 Description : Archive person level WSP related data.
2931 *****************************************************************************/
2932 procedure archive_wsp_data( assactid in number
2933 , p_person_id in per_all_assignments_f.person_id%type
2934 , p_assignment_id in per_all_assignments_f.assignment_id%type
2935 , p_race in per_all_people_f.per_information4%type
2936 , p_sex in per_all_people_f.sex%type
2937 , p_ass_cat_name in hr_lookups.meaning%type
2938 , p_legal_entity_id in number
2939 , p_disability in varchar2
2940 ) is
2941 --
2942 l_proc varchar2(50);
2943 l_ovn number;
2944 l_action_info_id number;
2945 l_index varchar2(30);
2946 l_legal_entity_id varchar2(15);
2947 l_exists_compts number;
2948 l_exists_courses number;
2949 -- index by Skills priority id + Competence/Course Id
2950 l_per_compt_pri_tab t_trng_priority_tab;
2951 l_per_courses_pri_tab t_trng_priority_tab;
2952 --
2953 -- Learning Paths
2954 cursor csr_wsp_lp(p_person_id number) is
2955 select olp.learning_path_id, olp_tl.name
2956 from ota_learning_paths olp
2957 ,ota_learning_paths_tl olp_tl
2958 ,ota_lp_enrollments ole
2959 where ole.person_id = p_person_id
2960 and ole.learning_path_id = olp.learning_path_id
2961 and ole.path_status_code <> 'CANCELLED'
2962 --and ole.completion_target_date between g_wsp_start_date and g_wsp_end_date --changed
2963 and ole.creation_date between g_wsp_start_date and g_wsp_end_date
2964 and olp.learning_path_id = olp_tl.learning_path_id
2965 and olp_tl.language = userenv('LANG');
2966 --
2967 -- Certifications
2968 cursor csr_wsp_cert(p_person_id number) is
2969 select oc.certification_id, oc_tl.name
2970 from ota_certifications_b oc
2971 ,ota_certifications_tl oc_tl
2972 ,ota_cert_enrollments oce
2973 where oce.person_id = p_person_id
2974 and oce.certification_id = oc.certification_id
2975 and oc.certification_id = oc_tl.certification_id
2976 and oc_tl.language = userenv('LANG')
2977 and oc.start_date_active <= g_wsp_end_date
2978 and (oc.end_date_active >= g_wsp_start_date or oc.end_date_active is null)
2979 and oce.certification_status_code not in ('CANCELLED','EXPIRED'); --AWAITING_APPROVAL,CANCELLED,CERTIFIED,ENROLLED,EXPIRED,REJECTED
2980 --
2981 -- Courses
2982 cursor csr_wsp_courses(p_person_id number) is
2983 select oav.activity_version_id, oav.version_name
2984 from ota_events oe
2985 ,ota_activity_versions oav
2986 ,ota_delegate_bookings odb
2987 ,ota_booking_status_types obst
2988 where odb.delegate_person_id = p_person_id
2989 and odb.event_id = oe.event_id
2990 and oe.event_type in ( 'SCHEDULED', 'SELFPACED')
2991 and oe.activity_version_id = oav.activity_version_id
2992 and oe.course_start_date <= g_wsp_end_date
2993 and nvl(oe.course_end_date, g_wsp_start_date) >= g_wsp_start_date
2994 and obst.booking_status_type_id = odb.booking_status_type_id
2995 and obst.type <> 'C'; -- include all status except the cancelled
2996 --
2997 -- Get the Competences Linked to a Course
2998 cursor csr_get_course_compts(p_course_id number) is
2999 select pce.competence_id ,pc.name
3000 from per_competence_elements pce
3001 , per_competences pc
3002 where pce.type = 'DELIVERY'
3003 and pce.activity_version_id = p_course_id
3004 and pce.business_group_id = g_bg_id
3005 and pce.competence_id = pc.competence_id;
3006 --
3007 -- Get the Competences Linked to a Learning Path
3008 cursor csr_get_lp_compts(p_lp_id number) is
3009 select pce.competence_id,pc.name
3010 from per_competence_elements pce
3011 , per_competences pc
3012 where pce.type = 'OTA_LEARNING_PATH'
3013 and pce.object_id = p_lp_id
3014 and pce.business_group_id = g_bg_id
3015 and pce.competence_id = pc.competence_id;
3016 --
3017 -- Get the Competences Linked to a Certification
3018 cursor csr_get_cert_compts(p_cert_id number) is
3019 select pce.competence_id,pc.name
3020 from per_competence_elements pce
3021 , per_competences pc
3022 where pce.type = 'OTA_CERTIFICATION'
3023 and pce.object_id = p_cert_id
3024 and pce.business_group_id = g_bg_id
3025 and pce.competence_id = pc.competence_id;
3026 --
3027 -- Get the Courses Linked to a Learning Path
3028 cursor csr_get_lp_courses(p_learning_path_id number) is
3029 select olpm.activity_version_id
3030 from ota_learning_paths olp
3031 , ota_lp_sections olps
3032 , ota_learning_path_members olpm
3033 where olp.learning_path_id = p_learning_path_id
3034 and olp.learning_path_id = olps.learning_path_id
3035 and olps.learning_path_section_id = olpm.learning_path_section_id;
3036 --
3037 -- Get the Courses Linked to a Certification
3038 cursor csr_get_cert_courses(p_cert_id number) is
3039 select ocm.object_id
3040 from OTA_CERTIFICATION_MEMBERS ocm
3041 , OTA_CERTIFICATIONS_B oc
3042 where oc.certification_id = p_cert_id
3043 and ocm.certification_id = oc.certification_id
3044 and ocm.object_type = 'H';
3045 --
3046 -- Exists cursors
3047 --
3048 cursor csr_exists_course_compts(p_course_id number) is
3049 select count(pce.competence_id)
3050 from per_competence_elements pce
3051 where pce.type = 'DELIVERY'
3052 and pce.activity_version_id = p_course_id
3053 and pce.business_group_id = g_bg_id;
3054 --
3055 cursor csr_exists_lp_compts(p_lp_id number) is
3056 select count(pce.competence_id)
3057 from per_competence_elements pce
3058 where pce.type = 'OTA_LEARNING_PATH'
3059 and pce.object_id = p_lp_id
3060 and pce.business_group_id = g_bg_id;
3061 --
3062 cursor csr_exists_lp_courses(p_learning_path_id number) is
3063 select count(olpm.activity_version_id)
3064 from ota_learning_paths olp
3065 , ota_lp_sections olps
3066 , ota_learning_path_members olpm
3067 where olp.learning_path_id = p_learning_path_id
3068 and olp.learning_path_id = olps.learning_path_id
3069 and olps.learning_path_section_id = olpm.learning_path_section_id;
3070 --
3071 cursor csr_exists_cert_compts(p_cert_id number) is
3072 select count(pce.competence_id)
3073 from per_competence_elements pce
3074 where pce.type = 'OTA_CERTIFICATION'
3075 and pce.object_id = p_cert_id
3076 and pce.business_group_id = g_bg_id;
3077 --
3078 cursor csr_exists_cert_courses(p_cert_id number) is
3079 select count(ocm.object_id)
3080 from OTA_CERTIFICATION_MEMBERS ocm
3081 , OTA_CERTIFICATIONS_B oc
3082 where oc.certification_id = p_cert_id
3083 and ocm.certification_id = oc.certification_id
3084 and ocm.object_type = 'H';
3085 --
3086 --
3087 -- Get the Valid Priorities defined for a specific Legal Entity
3088 cursor csr_get_all_pri_le is
3089 select puci.user_row_id
3090 from pay_user_tables put
3091 , pay_user_rows_f pur
3092 , pay_user_column_instances_f puci
3093 , pay_user_columns puc
3094 where put.user_table_name = 'ZA_WSP_SKILLS_PRIORITIES'
3095 and put.user_table_id = puc.user_table_id
3096 and puc.user_column_name like p_legal_entity_id || '%'
3097 and put.user_table_id = pur.user_table_id
3098 and puci.user_row_id = pur.user_row_id
3099 and puci.effective_start_date <= g_wsp_end_date
3100 and nvl(puci.effective_end_date,g_wsp_start_date) >= g_wsp_start_date
3101 and puci.user_column_id = puc.user_column_id;
3102 --
3103 begin
3104 l_proc := g_package || 'archive_wsp_data';
3105 hr_utility.trace ('Entering '||l_proc);
3106 hr_utility.set_location('l_legal_entity_id: '||l_legal_entity_id,10);
3107 hr_utility.set_location('assactid : '||assactid,10);
3108 hr_utility.set_location('p_person_id : '||p_person_id,10);
3109 hr_utility.set_location('p_assignment_id : '||p_assignment_id,10);
3110 -- initialize the pl/sql table
3111 l_per_compt_pri_tab.delete;
3112 l_per_courses_pri_tab.delete;
3113 --
3114 select rpad(p_legal_entity_id,15,0) into l_legal_entity_id from dual;
3115 --
3116 hr_utility.set_location('l_legal_entity_id: '||l_legal_entity_id,10);
3117 hr_utility.set_location('assactid : '||assactid,10);
3118 hr_utility.set_location('p_person_id : '||p_person_id,10);
3119 hr_utility.set_location('p_assignment_id : '||p_assignment_id,10);
3120 --
3121 -- Courses : start
3122 for rec_courses in csr_wsp_courses(p_person_id)
3123 loop
3124 hr_utility.set_location('Entering rec_courses : '||rec_courses.activity_version_id,20);
3125 hr_utility.set_location('g_wsp_course_pri_tab.COUNT : '||g_wsp_course_pri_tab.COUNT,20);
3126 hr_utility.set_location('g_wsp_compt_pri_tab.COUNT : '||g_wsp_compt_pri_tab.COUNT,20);
3127 -- Check if the Course has competences linked to it
3128 open csr_exists_course_compts(rec_courses.activity_version_id);
3129 fetch csr_exists_course_compts into l_exists_compts;
3130 close csr_exists_course_compts;
3131 --
3132 hr_utility.set_location('l_exists_compts : '||l_exists_compts,20);
3133 --
3134 if l_exists_compts > 0 then
3135 for rec_get_course_compts in csr_get_course_compts(rec_courses.activity_version_id)
3136 loop
3137 hr_utility.set_location('Entering rec_get_course_compts : '||rec_get_course_compts.competence_id,20);
3138 for rec_get_all_pri_le in csr_get_all_pri_le
3139 loop
3140 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_get_course_compts.competence_id;
3141 hr_utility.set_location('Entering rec_get_all_pri_le l_index : '||l_index,20);
3142 if NOT l_per_compt_pri_tab.exists(l_index) then -- check if this priority+competence is archived
3143 hr_utility.set_location('Not archived yet : l_per_compt_pri_tab.COUNT : '||l_per_compt_pri_tab.COUNT,20);
3144 if g_wsp_compt_pri_tab.exists(l_index) then
3145 hr_utility.set_location('l_index Exists',20);
3146 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,rec_get_all_pri_le.user_row_id, rec_get_course_compts.competence_id, rec_get_course_compts.name, g_wsp_comp_lookup, p_legal_entity_id, p_disability);
3147 -- populate the pl/sql table for the person
3148 l_per_compt_pri_tab(l_index).priority_id := rec_get_all_pri_le.user_row_id;
3149 l_per_compt_pri_tab(l_index).trng_event_id := rec_get_course_compts.competence_id;
3150 end if;
3151 end if;
3152 end loop;
3153 end loop;
3154 else
3155 for rec_get_all_pri_le in csr_get_all_pri_le
3156 loop
3157 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_courses.activity_version_id;
3158 hr_utility.set_location('ELSE Entering rec_get_all_pri_le l_index : '||l_index,20);
3159 if NOT l_per_courses_pri_tab.exists(l_index) then -- check if this priority+course is archived
3160 hr_utility.set_location('ELSE Not archived yet : l_per_courses_pri_tab.COUNT : '||l_per_courses_pri_tab.COUNT,20);
3161 if g_wsp_course_pri_tab.exists(l_index) then
3162 hr_utility.set_location('ELSE l_index Exists',20);
3163 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,rec_get_all_pri_le.user_row_id, rec_courses.activity_version_id, rec_courses.version_name, g_wsp_courses_lookup, p_legal_entity_id, p_disability);
3164 -- populate the pl/sql table for the person
3165 l_per_courses_pri_tab(l_index).priority_id := rec_get_all_pri_le.user_row_id;
3166 l_per_courses_pri_tab(l_index).trng_event_id := rec_courses.activity_version_id;
3167 end if;
3168 end if;
3169 end loop;
3170 end if;
3171 end loop;
3172 -- Courses : end
3173 --
3174 -- Learning Paths : start
3175 for rec_learning_paths in csr_wsp_lp(p_person_id)
3176 loop
3177 hr_utility.set_location('Entering rec_learning_paths : '||rec_learning_paths.learning_path_id,30);
3178 hr_utility.set_location('g_wsp_l_paths_tab.COUNT : '||g_wsp_l_paths_tab.COUNT,30);
3179 if g_wsp_l_paths_tab.exists(rec_learning_paths.learning_path_id) then
3180 --
3181 open csr_exists_lp_compts(rec_learning_paths.learning_path_id);
3182 fetch csr_exists_lp_compts into l_exists_compts;
3183 close csr_exists_lp_compts;
3184 --
3185 open csr_exists_lp_courses(rec_learning_paths.learning_path_id);
3186 fetch csr_exists_lp_courses into l_exists_courses;
3187 close csr_exists_lp_courses;
3188 hr_utility.set_location('LP Exists : '||'Compt:'||l_exists_compts||' Course:'||l_exists_courses,30);
3189
3190 if (l_exists_compts > 0 OR l_exists_courses > 0) then
3191 if l_exists_compts > 0 then
3192 for rec_get_lp_compts in csr_get_lp_compts(rec_learning_paths.learning_path_id)
3193 loop
3194 hr_utility.set_location('Entering rec_get_lp_compts: ',30);
3195 for rec_get_all_pri_le in csr_get_all_pri_le
3196 loop
3197 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_get_lp_compts.competence_id;
3198 hr_utility.set_location('Entering rec_get_all_pri_le - l_index: '||l_index, 30);
3199 if NOT l_per_compt_pri_tab.exists(l_index) then -- check if this priority+competence is archived
3200 hr_utility.set_location('Not archived yet : l_per_compt_pri_tab.COUNT : '||l_per_compt_pri_tab.COUNT,30);
3201 if g_wsp_compt_pri_tab.exists(l_index) then
3202 hr_utility.set_location('l_index Exists',30);
3203 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,rec_get_all_pri_le.user_row_id, rec_get_lp_compts.competence_id, rec_get_lp_compts.name, g_wsp_comp_lookup, p_legal_entity_id, p_disability);
3204 -- populate the pl/sql table for the person
3205 l_per_compt_pri_tab(l_index).priority_id := rec_get_all_pri_le.user_row_id;
3206 l_per_compt_pri_tab(l_index).trng_event_id := rec_get_lp_compts.competence_id;
3207 end if;
3208 end if;
3209 end loop;
3210 end loop;
3211 end if;
3212 if l_exists_courses > 0 then
3213 for rec_get_lp_courses in csr_get_lp_courses(rec_learning_paths.learning_path_id)
3214 loop
3215 hr_utility.set_location('Entering rec_get_lp_courses: ',30);
3216 -- Check if the Course has competences linked to it
3217 open csr_exists_course_compts(rec_get_lp_courses.activity_version_id);
3218 fetch csr_exists_course_compts into l_exists_compts;
3219 close csr_exists_course_compts;
3220
3221 if l_exists_compts > 0 then
3222 for rec_get_course_compts in csr_get_course_compts(rec_get_lp_courses.activity_version_id)
3223 loop
3224 hr_utility.set_location('Entering rec_get_course_compts : '||rec_get_course_compts.competence_id,20);
3225 for rec_get_all_pri_le in csr_get_all_pri_le
3226 loop
3227 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_get_course_compts.competence_id;
3228 hr_utility.set_location('Entering rec_get_all_pri_le l_index : '||l_index,30);
3229 if NOT l_per_compt_pri_tab.exists(l_index) then -- check if this priority+competence is archived
3230 hr_utility.set_location('Not archived yet : l_per_compt_pri_tab.COUNT : '||l_per_compt_pri_tab.COUNT,30);
3231 if g_wsp_compt_pri_tab.exists(l_index) then
3232 hr_utility.set_location('l_index Exists',30);
3233 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3234 rec_get_all_pri_le.user_row_id, rec_get_course_compts.competence_id,
3235 rec_get_course_compts.name, g_wsp_comp_lookup, p_legal_entity_id,
3236 p_disability);
3237 -- populate the pl/sql table for the person
3238 l_per_compt_pri_tab(l_index).priority_id := rec_get_all_pri_le.user_row_id;
3239 l_per_compt_pri_tab(l_index).trng_event_id := rec_get_course_compts.competence_id;
3240 end if;
3241 end if;
3242 end loop;
3243 end loop;
3244 else
3245 for rec_get_all_pri_le in csr_get_all_pri_le
3246 loop
3247 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_get_lp_courses.activity_version_id;
3248 hr_utility.set_location('Entering rec_get_all_pri_le - l_index: '||l_index, 30);
3249 if NOT l_per_courses_pri_tab.exists(l_index) then -- check if this priority+course is archived
3250 hr_utility.set_location('Not archived yet : l_per_courses_pri_tab.COUNT : '||l_per_courses_pri_tab.COUNT,30);
3251 if g_wsp_course_pri_tab.exists(l_index) then
3252 hr_utility.set_location('l_index Exists',30);
3253 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,rec_get_all_pri_le.user_row_id,
3254 rec_get_lp_courses.activity_version_id, rec_get_lp_courses.activity_version_id, g_wsp_courses_lookup,
3255 p_legal_entity_id, p_disability);
3256 -- populate the pl/sql table for the person
3257 l_per_courses_pri_tab(l_index).priority_id := rec_get_all_pri_le.user_row_id;
3258 l_per_courses_pri_tab(l_index).trng_event_id := rec_get_lp_courses.activity_version_id;
3259 end if;
3260 end if;
3261 end loop;
3262 end if;
3263 end loop;
3264 end if;
3265 else
3266 l_index := rec_learning_paths.learning_path_id;
3267 hr_utility.set_location('No Linked Course or Compt - l_index'||l_index,30);
3268 if (g_wsp_l_paths_tab(l_index).Attribute1 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute1)) then
3269 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute1, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3270 end if;
3271 if (g_wsp_l_paths_tab(l_index).Attribute2 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute2)) then
3272 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute2, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3273 end if;
3274 if (g_wsp_l_paths_tab(l_index).Attribute3 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute3)) then
3275 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute3, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3276 end if;
3277 if (g_wsp_l_paths_tab(l_index).Attribute4 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute4)) then
3278 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute4, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3279 end if;
3280 if (g_wsp_l_paths_tab(l_index).Attribute5 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute5)) then
3281 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute5, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3282 end if;
3283 if (g_wsp_l_paths_tab(l_index).Attribute6 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute6)) then
3284 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute6, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3285 end if;
3286 if (g_wsp_l_paths_tab(l_index).Attribute7 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute7)) then
3287 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute7, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3288 end if;
3289 if (g_wsp_l_paths_tab(l_index).Attribute8 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute8)) then
3290 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute8, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3291 end if;
3292 if (g_wsp_l_paths_tab(l_index).Attribute9 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute9)) then
3293 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute9, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3294 end if;
3295 if (g_wsp_l_paths_tab(l_index).Attribute10 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute10)) then
3296 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute10, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3297 end if;
3298 if (g_wsp_l_paths_tab(l_index).Attribute11 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute11)) then
3299 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute11, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3300 end if;
3301 if (g_wsp_l_paths_tab(l_index).Attribute12 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute12)) then
3302 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute12, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3303 end if;
3304 if (g_wsp_l_paths_tab(l_index).Attribute13 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute13)) then
3305 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute13, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3306 end if;
3307 if (g_wsp_l_paths_tab(l_index).Attribute14 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute14)) then
3308 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute14, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3309 end if;
3310 if (g_wsp_l_paths_tab(l_index).Attribute15 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_l_paths_tab(l_index).Attribute15)) then
3311 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_l_paths_tab(l_index).Attribute15, rec_learning_paths.learning_path_id, rec_learning_paths.name, g_wsp_lpath_lookup, p_legal_entity_id, p_disability);
3312 end if;
3313 end if;
3314 end if ;
3315 end loop;
3316 -- Learning Path : end
3317 --
3318 -- Certifications : start
3319 for rec_certifications in csr_wsp_cert(p_person_id)
3320 loop
3321 hr_utility.set_location('Entering rec_certifications : '||rec_certifications.certification_id,40);
3322 hr_utility.set_location('g_wsp_certifications_tab.COUNT : '||g_wsp_certifications_tab.COUNT,40);
3323 hr_utility.set_location('g_wsp_certifications_tab.first : '||g_wsp_certifications_tab.first,40);
3324 if g_wsp_certifications_tab.exists(rec_certifications.certification_id) then
3325 --
3326 open csr_exists_cert_compts(rec_certifications.certification_id);
3327 fetch csr_exists_cert_compts into l_exists_compts;
3328 close csr_exists_cert_compts;
3329 --
3330 open csr_exists_cert_courses(rec_certifications.certification_id);
3331 fetch csr_exists_cert_courses into l_exists_courses;
3332 close csr_exists_cert_courses;
3333 --
3334 hr_utility.set_location('Cert Exists : '||'Compt:'||l_exists_compts||' Course:'||l_exists_courses,40);
3335
3336 if ( l_exists_compts > 0 OR l_exists_courses > 0 ) then
3337 if l_exists_compts > 0 then
3338 for rec_get_cert_compts in csr_get_cert_compts(rec_certifications.certification_id)
3339 loop
3340 hr_utility.set_location('Entering rec_get_cert_compts: ',40);
3341 for rec_get_all_pri_le in csr_get_all_pri_le
3342 loop
3343 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_get_cert_compts.competence_id;
3344 hr_utility.set_location('Entering rec_get_all_pri_le - l_index : '||l_index,40);
3345 if NOT l_per_compt_pri_tab.exists(l_index) then -- check if this priority + competence is archived
3346 hr_utility.set_location('Not archived yet : l_per_compt_pri_tab.COUNT : '||l_per_compt_pri_tab.COUNT,40);
3347 if g_wsp_compt_pri_tab.exists(l_index) then
3348 hr_utility.set_location('l_index Exists',40);
3349 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,rec_get_all_pri_le.user_row_id, rec_get_cert_compts.competence_id, rec_get_cert_compts.name, g_wsp_comp_lookup, p_legal_entity_id, p_disability);
3350 -- populate the pl/sql table for the person
3351 l_per_compt_pri_tab(l_index).priority_id := rec_get_all_pri_le.user_row_id;
3352 l_per_compt_pri_tab(l_index).trng_event_id := rec_get_cert_compts.competence_id;
3353 end if;
3354 end if;
3355 end loop;
3356 end loop;
3357 end if;
3358 if l_exists_courses > 0 then
3359 for rec_get_cert_courses in csr_get_cert_courses(rec_certifications.certification_id)
3360 loop
3361 hr_utility.set_location('Entering rec_get_cert_courses: ',40);
3362 -- Check if the Course has competences linked to it
3363 open csr_exists_course_compts(rec_get_cert_courses.object_id);
3364 fetch csr_exists_course_compts into l_exists_compts;
3365 close csr_exists_course_compts;
3366
3367 if l_exists_compts > 0 then
3368 for rec_get_course_compts in csr_get_course_compts(rec_get_cert_courses.object_id)
3369 loop
3370 hr_utility.set_location('Entering rec_get_course_compts : '||rec_get_course_compts.competence_id,20);
3371 for rec_get_all_pri_le in csr_get_all_pri_le
3372 loop
3373 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_get_course_compts.competence_id;
3374 hr_utility.set_location('Entering rec_get_all_pri_le l_index : '||l_index,40);
3375 if NOT l_per_compt_pri_tab.exists(l_index) then -- check if this priority+competence is archived
3376 hr_utility.set_location('Not archived yet : l_per_compt_pri_tab.COUNT : '||l_per_compt_pri_tab.COUNT,20);
3377 if g_wsp_compt_pri_tab.exists(l_index) then
3378 hr_utility.set_location('l_index Exists',40);
3379 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,rec_get_all_pri_le.user_row_id, rec_get_course_compts.competence_id, rec_get_course_compts.name, g_wsp_comp_lookup, p_legal_entity_id, p_disability);
3380 -- populate the pl/sql table for the person
3381 l_per_compt_pri_tab(l_index).priority_id := rec_get_all_pri_le.user_row_id;
3382 l_per_compt_pri_tab(l_index).trng_event_id := rec_get_course_compts.competence_id;
3383 end if;
3384 end if;
3385 end loop;
3386 end loop;
3387 else
3388 for rec_get_all_pri_le in csr_get_all_pri_le
3389 loop
3390 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_get_cert_courses.object_id;
3391 hr_utility.set_location('Entering rec_get_all_pri_le - l_index: '||l_index, 40);
3392 if NOT l_per_courses_pri_tab.exists(l_index) then -- check if this priority+course is archived
3393 hr_utility.set_location('Not archived yet : l_per_courses_pri_tab.COUNT : '||l_per_courses_pri_tab.COUNT,40);
3394 if g_wsp_course_pri_tab.exists(l_index) then
3395 hr_utility.set_location('l_index Exists',40);
3396 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,rec_get_all_pri_le.user_row_id, rec_get_cert_courses.object_id, rec_get_cert_courses.object_id, g_wsp_courses_lookup, p_legal_entity_id, p_disability);
3397 -- populate the pl/sql table for the person
3398 l_per_courses_pri_tab(l_index).priority_id := rec_get_all_pri_le.user_row_id;
3399 l_per_courses_pri_tab(l_index).trng_event_id := rec_get_cert_courses.object_id;
3400 end if;
3401 end if;
3402 end loop;
3403 end if;
3404 end loop;
3405 end if; -- l_exists_courses > 0
3406 else
3407 l_index := rec_certifications.certification_id;
3408 hr_utility.set_location('No Linked Course or Compt - l_index'||l_index,40);
3409 if g_wsp_certifications_tab(l_index).Attribute1 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute1) then
3410 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute1, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3411 end if;
3412 if g_wsp_certifications_tab(l_index).Attribute2 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute2) then
3413 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute2, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3414 end if;
3415 if g_wsp_certifications_tab(l_index).Attribute3 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute3) then
3416 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute3, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3417 end if;
3418 if g_wsp_certifications_tab(l_index).Attribute4 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute4) then
3419 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute4, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3420 end if;
3421 if g_wsp_certifications_tab(l_index).Attribute5 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute5) then
3422 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute5, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3423 end if;
3424 if g_wsp_certifications_tab(l_index).Attribute6 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute6) then
3425 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute6, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3426 end if;
3427 if g_wsp_certifications_tab(l_index).Attribute7 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute7) then
3428 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute7, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3429 end if;
3430 if g_wsp_certifications_tab(l_index).Attribute8 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute8) then
3431 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute8, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3432 end if;
3433 if g_wsp_certifications_tab(l_index).Attribute9 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute9) then
3434 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute9, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3435 end if;
3436 if g_wsp_certifications_tab(l_index).Attribute10 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute10) then
3437 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute10, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3438 end if;
3439 if g_wsp_certifications_tab(l_index).Attribute11 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute11) then
3440 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute11, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3441 end if;
3442 if g_wsp_certifications_tab(l_index).Attribute12 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute12) then
3443 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute12, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3444 end if;
3445 if g_wsp_certifications_tab(l_index).Attribute13 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute13) then
3446 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute13, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3447 end if;
3448 if g_wsp_certifications_tab(l_index).Attribute14 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute14) then
3449 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute14, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3450 end if;
3451 if g_wsp_certifications_tab(l_index).Attribute15 is not null and g_wsp_pri_final_tab.exists(l_legal_entity_id||g_wsp_certifications_tab(l_index).Attribute15) then
3452 archive_wsp_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_wsp_certifications_tab(l_index).Attribute15, rec_certifications.certification_id, rec_certifications.name, g_wsp_cert_lookup, p_legal_entity_id, p_disability);
3453 end if;
3454 end if;
3455 end if;
3456 end loop;
3457 -- Certification : end
3458 --
3459 l_per_compt_pri_tab.delete;
3460 l_per_courses_pri_tab.delete;
3461 hr_utility.trace('Leaving '||l_proc);
3462 end archive_wsp_data;
3463 --
3464 --
3465 /****************************************************************************
3466 Name : archive_atr_data2
3467 Description : Archive person level ATR related data.
3468 *****************************************************************************/
3469 procedure archive_atr_data2( assactid in number
3470 , p_person_id in per_all_assignments_f.person_id%type
3471 , p_assignment_id in per_all_assignments_f.assignment_id%type
3472 , p_race in per_all_people_f.per_information4%type
3473 , p_sex in per_all_people_f.sex%type
3474 , p_ass_cat_name in hr_lookups.meaning%type
3475 , p_skills_pri_id in number
3476 , p_trng_event_id in number
3477 , p_trng_event_name in varchar2
3478 , p_trng_event_lookup in varchar2
3479 , p_status in varchar2
3480 , p_legal_entity_id in number
3481 , p_disability in varchar2
3482 ) is
3483 --variables
3484 l_index varchar2(40);
3485 l_action_info_id number;
3486 l_ovn number;
3487 l_legal_entity_id varchar2(30);
3488 l_proc varchar2(100);
3489 begin
3490 --
3491 l_proc := g_package || 'archive_atr_data2';
3492 hr_utility.trace('Entering '||l_proc);
3493 --
3494 select rpad(p_legal_entity_id,15,0) into l_legal_entity_id from dual;
3495 l_index := l_legal_entity_id ||p_skills_pri_id;
3496 --
3497 hr_utility.set_location('g_atr_pri_final_tab.COUNT: '||g_atr_pri_final_tab.COUNT,10);
3498 hr_utility.set_location('l_index :' ||l_index,10);
3499 --
3500 if g_atr_pri_final_tab.exists(l_index) then
3501 hr_utility.set_location('assactid : '||assactid,10);
3502 hr_utility.set_location('p_legal_entity_id : '||p_legal_entity_id,10);
3503 hr_utility.set_location('p_person_id : '||p_person_id,10);
3504 hr_utility.set_location('p_race : '||p_race,10);
3505 hr_utility.set_location('p_sex : '||p_sex,10);
3506 hr_utility.set_location('p_ass_cat_name : '||p_ass_cat_name,10);
3507 hr_utility.set_location('p_disability : '||p_disability,10);
3508 hr_utility.set_location('p_trng_event_id : '||p_trng_event_id,10);
3509 hr_utility.set_location('p_trng_event_name : '||p_trng_event_name,10);
3510 hr_utility.set_location('p_trng_event_lookup : '||p_trng_event_lookup,10);
3511 hr_utility.set_location('g_atr_pri_final_tab(l_index).skills_priority_num : '||g_atr_pri_final_tab(l_index).skills_priority_num,10);
3512 hr_utility.set_location('g_atr_pri_final_tab(l_index).skills_priority_id : '||g_atr_pri_final_tab(l_index).skills_priority_id,10);
3513 hr_utility.set_location('g_atr_pri_final_tab(l_index).skills_priority_name: '||g_atr_pri_final_tab(l_index).skills_priority_name,10);
3514 hr_utility.set_location('p_status : '||p_status,10);
3515
3516
3517 pay_action_information_api.create_action_information
3518 (
3519 p_action_information_id => l_action_info_id
3520 , p_assignment_id => p_assignment_id
3521 , p_action_context_id => assactid
3522 , p_action_context_type => 'AAP'
3523 , p_object_version_number => l_ovn
3524 , p_effective_date => g_archive_effective_date
3525 , p_action_information_category => 'ZA ATR PERSON DETAILS'
3526 , p_action_information1 => g_bg_id -- Business GROUP Id
3527 , p_action_information2 => p_legal_entity_id
3528 , p_action_information3 => p_person_id
3529 , p_action_information4 => p_race
3530 , p_action_information5 => p_sex
3531 , p_action_information6 => p_ass_cat_name -- Occupation category
3532 , p_action_information7 => p_disability
3533 , p_action_information8 => p_trng_event_id -- trng event id course/competence/qualification/learningpath/certification
3534 , p_action_information9 => p_trng_event_name -- trng event Name
3535 , p_action_information10 => p_trng_event_lookup -- trng event category
3536 , p_action_information11 => g_atr_pri_final_tab(l_index).skills_priority_num
3537 , p_action_information12 => g_atr_pri_final_tab(l_index).skills_priority_id
3538 , p_action_information13 => g_atr_pri_final_tab(l_index).skills_priority_name
3539 , p_action_information14 => p_status --Attended/Completed
3540 );
3541 end if;
3542 --
3543 hr_utility.trace('Leaving '||l_proc);
3544 --
3545 end archive_atr_data2;
3546 --
3547 --
3548 /****************************************************************************
3549 Name : archive_atr_data
3550 Description : Archive person level ATR related data. All learning intervention
3551 are archived with the status of completed
3552 *****************************************************************************/
3553 procedure archive_atr_data( assactid in number
3554 , p_person_id in per_all_assignments_f.person_id%type
3555 , p_assignment_id in per_all_assignments_f.assignment_id%type
3556 , p_race in per_all_people_f.per_information4%type
3557 , p_sex in per_all_people_f.sex%type
3558 , p_ass_cat_name in hr_lookups.meaning%type
3559 , p_legal_entity_id in number
3560 , p_disability in varchar2
3561 ) is
3562 l_proc varchar2(50);
3563 l_status varchar2(50);
3564 l_index varchar2(60);
3565 l_legal_entity_id varchar2(15);
3566
3567 -- Qualifications
3568 cursor csr_atr_qual(p_person_id number) is -- Caters for both the two types of Qualifications(Award/Class)
3569 select pqt.qualification_type_id, pqt.name, pqa.awarded_date, pqa.status--event id event name
3570 from per_qualifications pqa
3571 ,per_qualification_types pqt
3572 ,per_establishment_attendances pea
3573 where (pqa.person_id = p_person_id or pea.person_id = p_person_id)
3574 and pqa.start_date <= g_atr_end_date
3575 and nvl(pqa.end_date,g_atr_start_date) >= g_atr_start_date
3576 and pqa.awarded_date between g_atr_start_date and g_atr_end_date
3577 and pqa.qualification_type_id = pqt.qualification_type_id
3578 and pqa.attendance_id = pea.attendance_id(+);
3579
3580 -- Competencies
3581 cursor csr_atr_competency(p_person_id number) is
3582 select pc.competence_id, pc.name , pce.achieved_date , pce.status
3583 from per_competences pc
3584 , per_competence_elements pce
3585 where pce.person_id = p_person_id
3586 and pce.competence_id = pc.competence_id
3587 and pce.effective_date_from between g_atr_start_date and g_atr_end_date
3588 and pce.type = 'PERSONAL';
3589
3590 --
3591 -- Learning Paths
3592 cursor csr_atr_lp(p_person_id number) is
3593 select olp.learning_path_id, olp_tl.name, ole.completion_date, ole.path_status_code
3594 from ota_learning_paths olp
3595 ,ota_learning_paths_tl olp_tl
3596 ,ota_lp_enrollments ole
3597 where ole.person_id = p_person_id
3598 and ole.learning_path_id = olp.learning_path_id
3599 and ole.path_status_code = 'COMPLETED'
3600 and ole.completion_date between g_atr_start_date and g_atr_end_date
3601 and olp.learning_path_id = olp_tl.learning_path_id
3602 and olp_tl.language = userenv('LANG');
3603
3604 --
3605 -- Certifications
3606 cursor csr_atr_cert(p_person_id number) is
3607 select oc.certification_id, oc_tl.name,oce.completion_date, oce.certification_status_code
3608 from ota_certifications_b oc
3609 ,ota_certifications_tl oc_tl
3610 ,ota_cert_enrollments oce
3611 where oce.person_id = p_person_id
3612 and oce.certification_id = oc.certification_id
3613 and oc.certification_id = oc_tl.certification_id
3614 and oc_tl.language = userenv('LANG')
3615 and oce.completion_date between g_atr_start_date and g_atr_end_date
3616 and oce.certification_status_code in ('CERTIFIED'); --AWAITING_APPROVAL,CANCELLED,CERTIFIED,ENROLLED,EXPIRED,REJECTED
3617
3618 -- Courses
3619 cursor csr_atr_courses(p_person_id number) is
3620 select oav.activity_version_id, oav.version_name, odb.date_status_changed ,obst.name "status"
3621 from ota_events oe
3622 ,ota_activity_versions oav
3623 ,ota_delegate_bookings odb
3624 ,ota_booking_status_types obst
3625 where odb.delegate_person_id = p_person_id
3626 and odb.event_id = oe.event_id
3627 and oe.event_type in ( 'SCHEDULED', 'SELFPACED')
3628 and oe.activity_version_id = oav.activity_version_id
3629 and oe.course_start_date <= g_atr_end_date
3630 and nvl(oe.course_end_date, g_atr_start_date) >= g_atr_start_date
3631 and obst.booking_status_type_id = odb.booking_status_type_id
3632 and obst.type = 'A' -- Attended
3633 and odb.date_status_changed between g_atr_start_date and g_atr_end_date;
3634 --
3635 -- Get the Valid Priorities defined for a specific Legal Entity
3636 cursor csr_get_all_pri_le is
3637 select puci.user_row_id
3638 from pay_user_tables put
3639 , pay_user_rows_f pur
3640 , pay_user_column_instances_f puci
3641 , pay_user_columns puc
3642 where put.user_table_name = 'ZA_WSP_SKILLS_PRIORITIES'
3643 and put.user_table_id = puc.user_table_id
3644 and puc.user_column_name like to_char(p_legal_entity_id) || '%'
3645 and put.user_table_id = pur.user_table_id
3646 and puci.user_row_id = pur.user_row_id
3647 and puci.effective_start_date <= g_atr_end_date
3648 and nvl(puci.effective_end_date,g_atr_start_date) >= g_atr_start_date
3649 and puci.user_column_id = puc.user_column_id;
3650
3651 --
3652 begin
3653 select rpad(p_legal_entity_id,15,0) into l_legal_entity_id from dual;
3654 l_proc := g_package || 'archive_atr_data';
3655 hr_utility.trace('Entering '||l_proc);
3656 hr_utility.set_location('l_legal_entity_id: '||l_legal_entity_id,10);
3657 hr_utility.set_location('assactid : '||assactid,10);
3658 hr_utility.set_location('p_person_id : '||p_person_id,10);
3659 hr_utility.set_location('p_assignment_id : '||p_assignment_id,10);
3660 hr_utility.set_location('p_race : '||p_race,10);
3661 hr_utility.set_location('p_sex : '||p_sex,10);
3662 hr_utility.set_location('p_ass_cat_name : '||p_ass_cat_name,10);
3663 hr_utility.set_location('p_legal_entity_id: '||p_legal_entity_id,10);
3664 hr_utility.set_location('p_disability : '||p_disability,10);
3665
3666 --Identify attended/completed trng events
3667 --
3668 -- for Qualifications
3669 for rec_qualifications in csr_atr_qual(p_person_id)
3670 loop
3671 hr_utility.set_location('Entering rec_qualifications : '||rec_qualifications.qualification_type_id,10);
3672 hr_utility.set_location('g_atr_qualifications_tab.COUNT'||g_atr_qualifications_tab.COUNT,10);
3673 if g_atr_qualifications_tab.exists(rec_qualifications.qualification_type_id) then
3674 hr_utility.set_location('Qual Exists : '||rec_qualifications.qualification_type_id,10);
3675 --
3676 l_status := 'COMPLETED'; --
3677 l_index := rec_qualifications.qualification_type_id;
3678 if g_atr_qualifications_tab(l_index).Attribute1 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute1) then
3679 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute1, rec_qualifications.qualification_type_id, rec_qualifications.name,
3680 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3681 end if;
3682 if g_atr_qualifications_tab(l_index).Attribute2 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute2) then
3683 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute2, rec_qualifications.qualification_type_id, rec_qualifications.name,
3684 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3685 end if;
3686 if g_atr_qualifications_tab(l_index).Attribute3 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute3) then
3687 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute3, rec_qualifications.qualification_type_id, rec_qualifications.name,
3688 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3689 end if;
3690 if g_atr_qualifications_tab(l_index).Attribute4 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute4) then
3691 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute4, rec_qualifications.qualification_type_id, rec_qualifications.name,
3692 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3693 end if;
3694 if g_atr_qualifications_tab(l_index).Attribute5 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute5) then
3695 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute5, rec_qualifications.qualification_type_id, rec_qualifications.name,
3696 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3697 end if;
3698 if g_atr_qualifications_tab(l_index).Attribute6 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute6) then
3699 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute6, rec_qualifications.qualification_type_id, rec_qualifications.name,
3700 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3701 end if;
3702 if g_atr_qualifications_tab(l_index).Attribute7 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute7) then
3703 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute7, rec_qualifications.qualification_type_id, rec_qualifications.name,
3704 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3705 end if;
3706 if g_atr_qualifications_tab(l_index).Attribute8 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute8) then
3707 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute8, rec_qualifications.qualification_type_id, rec_qualifications.name,
3708 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3709 end if;
3710 if g_atr_qualifications_tab(l_index).Attribute9 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute9) then
3711 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute9, rec_qualifications.qualification_type_id, rec_qualifications.name,
3712 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3713 end if;
3714 if g_atr_qualifications_tab(l_index).Attribute10 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute10) then
3715 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute10, rec_qualifications.qualification_type_id, rec_qualifications.name,
3716 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3717 end if;
3718 if g_atr_qualifications_tab(l_index).Attribute11 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute11) then
3719 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute11, rec_qualifications.qualification_type_id, rec_qualifications.name,
3720 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3721 end if;
3722 if g_atr_qualifications_tab(l_index).Attribute12 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute12) then
3723 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute12, rec_qualifications.qualification_type_id, rec_qualifications.name,
3724 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3725 end if;
3726 if g_atr_qualifications_tab(l_index).Attribute13 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute13) then
3727 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute13, rec_qualifications.qualification_type_id, rec_qualifications.name,
3728 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3729 end if;
3730 if g_atr_qualifications_tab(l_index).Attribute14 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute14) then
3731 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute14, rec_qualifications.qualification_type_id, rec_qualifications.name,
3732 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3733 end if;
3734 if g_atr_qualifications_tab(l_index).Attribute15 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_qualifications_tab(l_index).Attribute15) then
3735 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_qualifications_tab(l_index).Attribute15, rec_qualifications.qualification_type_id, rec_qualifications.name,
3736 g_atr_qual_lookup, l_status, p_legal_entity_id, p_disability);
3737 end if;
3738 end if;
3739 end loop;
3740 --
3741 -- Competencies
3742 for rec_competencies in csr_atr_competency(p_person_id)
3743 loop
3744 hr_utility.set_location('Entering rec_competencies : '||rec_competencies.competence_id,20);
3745 hr_utility.set_location('g_atr_compt_pri_tab.COUNT : '||g_atr_compt_pri_tab.COUNT,20);
3746 for rec_get_all_pri_le in csr_get_all_pri_le
3747 loop
3748 hr_utility.set_location('Entering rec_get_all_pri_le ',20);
3749 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_competencies.competence_id;
3750 if g_atr_compt_pri_tab.exists(l_index) then
3751 hr_utility.set_location('l_index Exists: '||l_index,20);
3752 l_status := 'COMPLETED';
3753 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3754 g_atr_compt_pri_tab(l_index).priority_id, rec_competencies.competence_id,
3755 rec_competencies.name, g_atr_comp_lookup, l_status, p_legal_entity_id,
3756 p_disability);
3757 end if;
3758 end loop;
3759 end loop;
3760 --
3761 -- Courses
3762 for rec_courses in csr_atr_courses(p_person_id)
3763 loop
3764 hr_utility.set_location('Entering rec_courses : '||rec_courses.activity_version_id,30);
3765 hr_utility.set_location('g_atr_course_pri_tab.COUNT : '||g_atr_course_pri_tab.COUNT,30);
3766 for rec_get_all_pri_le in csr_get_all_pri_le
3767 loop
3768 hr_utility.set_location('Entering rec_get_all_pri_le ',30);
3769 l_index := rec_get_all_pri_le.user_row_id||'_'||rec_courses.activity_version_id;
3770 if g_atr_course_pri_tab.exists(l_index) then
3771 hr_utility.set_location('l_index Exists: '||l_index,30);
3772 l_status := 'COMPLETED';
3773 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3774 g_atr_course_pri_tab(l_index).priority_id, rec_courses.activity_version_id,
3775 rec_courses.version_name, g_atr_courses_lookup, l_status, p_legal_entity_id,
3776 p_disability);
3777 end if;
3778 end loop;
3779 end loop;
3780 --
3781 -- Learning Paths
3782 for rec_learning_paths in csr_atr_lp(p_person_id)
3783 loop
3784 hr_utility.set_location('Entering rec_learning_paths : '||rec_learning_paths.learning_path_id,40);
3785 hr_utility.set_location('g_atr_l_paths_tab.COUNT'||g_atr_l_paths_tab.COUNT,40);
3786 if g_atr_l_paths_tab.exists(rec_learning_paths.learning_path_id) then
3787 hr_utility.set_location('LP Exists : '||rec_learning_paths.learning_path_id,40);
3788 l_status := 'COMPLETED';
3789 l_index := rec_learning_paths.learning_path_id;
3790 if g_atr_l_paths_tab(l_index).Attribute1 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute1) then
3791 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute1,
3792 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3793 end if;
3794 if g_atr_l_paths_tab(l_index).Attribute2 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute2) then
3795 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute2,
3796 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3797 end if;
3798 if g_atr_l_paths_tab(l_index).Attribute3 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute3) then
3799 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute3,
3800 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3801 end if;
3802 if g_atr_l_paths_tab(l_index).Attribute4 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute4) then
3803 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute4,
3804 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3805 end if;
3806 if g_atr_l_paths_tab(l_index).Attribute5 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute5) then
3807 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute5,
3808 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3809 end if;
3810 if g_atr_l_paths_tab(l_index).Attribute6 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute6) then
3811 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute6,
3812 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3813 end if;
3814 if g_atr_l_paths_tab(l_index).Attribute7 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute7) then
3815 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute7,
3816 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3817 end if;
3818 if g_atr_l_paths_tab(l_index).Attribute8 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute8) then
3819 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute8,
3820 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3821 end if;
3822 if g_atr_l_paths_tab(l_index).Attribute9 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute9) then
3823 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute9,
3824 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3825 end if;
3826 if g_atr_l_paths_tab(l_index).Attribute10 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute10) then
3827 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute10,
3828 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3829 end if;
3830 if g_atr_l_paths_tab(l_index).Attribute11 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute11) then
3831 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute11,
3832 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3833 end if;
3834 if g_atr_l_paths_tab(l_index).Attribute12 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute12) then
3835 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute12,
3836 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3837 end if;
3838 if g_atr_l_paths_tab(l_index).Attribute13 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute13) then
3839 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute13,
3840 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3841 end if;
3842 if g_atr_l_paths_tab(l_index).Attribute14 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute14) then
3843 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute14,
3844 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3845 end if;
3846 if g_atr_l_paths_tab(l_index).Attribute15 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_l_paths_tab(l_index).Attribute15) then
3847 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_l_paths_tab(l_index).Attribute15,
3848 rec_learning_paths.learning_path_id, rec_learning_paths.name, g_atr_lpath_lookup, l_status, p_legal_entity_id, p_disability);
3849 end if;
3850 end if;
3851 end loop;
3852 --
3853 -- Certifications
3854 for rec_certifications in csr_atr_cert(p_person_id)
3855 loop
3856 hr_utility.set_location('Entering rec_certifications : '||rec_certifications.certification_id,50);
3857 hr_utility.set_location('g_atr_certifications_tab.COUNT'||g_atr_certifications_tab.COUNT,50);
3858 if g_atr_certifications_tab.exists(rec_certifications.certification_id) then
3859 hr_utility.set_location('Certification Exists : '||rec_certifications.certification_id,50);
3860 l_status := 'COMPLETED';
3861 l_index := rec_certifications.certification_id;
3862 if g_atr_certifications_tab(l_index).Attribute1 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute1) then
3863 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,g_atr_certifications_tab(l_index).Attribute1,
3864 rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3865 end if;
3866 if g_atr_certifications_tab(l_index).Attribute2 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute2) then
3867 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3868 g_atr_certifications_tab(l_index).Attribute2, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3869 end if;
3870 if g_atr_certifications_tab(l_index).Attribute3 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute3) then
3871 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3872 g_atr_certifications_tab(l_index).Attribute3, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3873 end if;
3874 if g_atr_certifications_tab(l_index).Attribute4 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute4) then
3875 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3876 g_atr_certifications_tab(l_index).Attribute4, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3877 end if;
3878 if g_atr_certifications_tab(l_index).Attribute5 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute5) then
3879 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3880 g_atr_certifications_tab(l_index).Attribute5, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3881 end if;
3882 if g_atr_certifications_tab(l_index).Attribute6 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute6) then
3883 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3884 g_atr_certifications_tab(l_index).Attribute6, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3885 end if;
3886 if g_atr_certifications_tab(l_index).Attribute7 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute7) then
3887 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3888 g_atr_certifications_tab(l_index).Attribute7, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3889 end if;
3890 if g_atr_certifications_tab(l_index).Attribute8 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute8) then
3891 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3892 g_atr_certifications_tab(l_index).Attribute8, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3893 end if;
3894 if g_atr_certifications_tab(l_index).Attribute9 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute9) then
3895 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3896 g_atr_certifications_tab(l_index).Attribute9, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3897 end if;
3898 if g_atr_certifications_tab(l_index).Attribute10 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute10) then
3899 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3900 g_atr_certifications_tab(l_index).Attribute10, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3901 end if;
3902 if g_atr_certifications_tab(l_index).Attribute11 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute11) then
3903 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3904 g_atr_certifications_tab(l_index).Attribute11, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3905 end if;
3906 if g_atr_certifications_tab(l_index).Attribute12 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute12) then
3907 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3908 g_atr_certifications_tab(l_index).Attribute12, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3909 end if;
3910 if g_atr_certifications_tab(l_index).Attribute13 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute13) then
3911 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3912 g_atr_certifications_tab(l_index).Attribute13, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3913 end if;
3914 if g_atr_certifications_tab(l_index).Attribute14 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute14) then
3915 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3916 g_atr_certifications_tab(l_index).Attribute14, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3917 end if;
3918 if g_atr_certifications_tab(l_index).Attribute15 is not null and g_atr_pri_final_tab.exists(l_legal_entity_id||g_atr_certifications_tab(l_index).Attribute15) then
3919 archive_atr_data2(assactid,p_person_id,p_assignment_id,p_race,p_sex,p_ass_cat_name,
3920 g_atr_certifications_tab(l_index).Attribute15, rec_certifications.certification_id, rec_certifications.name, g_atr_cert_lookup, l_status, p_legal_entity_id, p_disability);
3921 end if;
3922 end if;
3923 end loop;
3924 --
3925 hr_utility.trace('Leaving '||l_proc);
3926 --
3927 end archive_atr_data;
3928 --
3929 --
3930 -- This procedure caches the location of the occupational category and level data.
3931 procedure cache_occupational_location
3932 (
3933 p_report_date in date,
3934 p_business_group_id in per_all_assignments_f.business_group_id%type
3935 ) is
3936
3937 l_user_table_id pay_user_tables.user_table_id%type;
3938 l_user_column_id_flex pay_user_columns.user_column_id%type;
3939 l_user_column_id_seg pay_user_columns.user_column_id%type;
3940 l_user_row_id_cat pay_user_rows_f.user_row_id%type;
3941 l_user_row_id_lev pay_user_rows_f.user_row_id%type;
3942 l_user_row_id_func pay_user_rows_f.user_row_id%type;
3943 l_temp varchar2(9);
3944
3945 begin
3946
3947 select user_table_id
3948 into l_user_table_id
3949 from pay_user_tables
3950 where user_table_name = 'ZA_OCCUPATIONAL_TYPES'
3951 and business_group_id is null
3952 and legislation_code = 'ZA';
3953
3954 select user_column_id
3955 into l_user_column_id_flex
3956 from pay_user_columns
3957 where user_table_id = l_user_table_id
3958 and business_group_id is null
3959 and legislation_code = 'ZA'
3960 and user_column_name = 'Flexfield';
3961
3962 select user_column_id
3963 into l_user_column_id_seg
3964 from pay_user_columns
3965 where user_table_id = l_user_table_id
3966 and business_group_id is null
3967 and legislation_code = 'ZA'
3968 and user_column_name = 'Segment';
3969
3970 select user_row_id
3971 into l_user_row_id_cat
3972 from pay_user_rows_f
3973 where user_table_id = l_user_table_id
3974 and row_low_range_or_name = 'Occupational Categories'
3975 and p_report_date between effective_start_date and effective_end_date;
3976
3977 select user_row_id
3978 into l_user_row_id_lev
3979 from pay_user_rows_f
3980 where user_table_id = l_user_table_id
3981 and row_low_range_or_name = 'Occupational Levels'
3982 and p_report_date between effective_start_date and effective_end_date;
3983
3984 select user_row_id
3985 into l_user_row_id_func
3986 from pay_user_rows_f
3987 where user_table_id = l_user_table_id
3988 and row_low_range_or_name = 'Function Type'
3989 and p_report_date between effective_start_date and effective_end_date;
3990
3991
3992 select value
3993 into g_cat_flex
3994 from pay_user_column_instances_f
3995 where user_row_id = l_user_row_id_cat
3996 and user_column_id = l_user_column_id_flex
3997 and business_group_id = p_business_group_id
3998 and p_report_date between effective_start_date and effective_end_date;
3999
4000 select value
4001 into g_cat_segment
4002 from pay_user_column_instances_f
4003 where user_row_id = l_user_row_id_cat
4004 and user_column_id = l_user_column_id_seg
4005 and business_group_id = p_business_group_id
4006 and p_report_date between effective_start_date and effective_end_date;
4007
4008 -- Verify the validity of the segments
4009 begin
4010 l_temp := substr(g_cat_segment, 8);
4011 if substr(g_cat_segment, 1, 7) <> 'SEGMENT' or to_number(l_temp) < 1 or to_number(l_temp) > 30 then
4012 raise_application_error(-20003, 'The Occupational data in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Segment.');
4013 end if;
4014 exception
4015 when invalid_number then
4016 raise_application_error(-20003, 'The Occupational data in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Segment.');
4017 end;
4018
4019 exception
4020 when no_data_found then
4021 raise_application_error(-20001, 'The Occupational data does not exist in the User Table ZA_OCCUPATIONAL_TYPES.');
4022
4023 end cache_occupational_location;
4024 --
4025 -- This function retrieves the occupational data via dynamic sql from the appropriate flexfield segment
4026 function get_occupational_data
4027 (
4028 p_type in varchar2,
4029 p_flex in varchar2,
4030 p_segment in varchar2,
4031 p_job_id in per_all_assignments_f.job_id%type,
4032 p_grade_id in per_all_assignments_f.grade_id%type,
4033 p_position_id in per_all_assignments_f.position_id%type
4034 ) return varchar2 is
4035
4036 l_sql varchar2(32767);
4037 l_name hr_lookups.meaning%type;
4038
4039
4040 begin
4041 hr_utility.set_location('In side get_occupational_data',1);
4042 hr_utility.set_location('p_flex : ' || p_flex,2);
4043 if p_flex = upper('Job') then
4044 begin
4045 if p_job_id is not null then
4046 l_sql := 'select hl.meaning from hr_lookups hl, per_job_definitions pjd, per_jobs pj where pj.job_id = '
4047 || to_char(p_job_id)
4048 || ' and pjd.job_definition_id = pj.job_definition_id and hl.application_id = 800 and hl.lookup_type = '''
4049 || p_type || ''' and hl.lookup_code = pjd.' || p_segment;
4050 execute immediate l_sql into l_name;
4051 else
4052 l_name := null;
4053 end if;
4054 exception
4055 when no_data_found then
4056 l_name := null;
4057 end;
4058 elsif p_flex = upper('Grade') then
4059 begin
4060 if p_grade_id is not null then
4061 l_sql := 'select hl.meaning from hr_lookups hl, per_grade_definitions pgd, per_grades pg where pg.grade_id = '
4062 || to_char(p_grade_id)
4063 || ' and pgd.grade_definition_id = pg.grade_definition_id and hl.application_id = 800 and hl.lookup_type = '''
4064 || p_type ||''' and hl.lookup_code = pgd.' || p_segment;
4065 execute immediate l_sql into l_name;
4066 else
4067 l_name := null;
4068 end if;
4069 exception
4070 when no_data_found then
4071 l_name := null;
4072 end;
4073 elsif p_flex = upper('Position') then
4074 begin
4075 if p_position_id is not null then
4076 l_sql := 'select hl.meaning from hr_lookups hl, per_position_definitions ppd, per_all_positions pap where pap.position_id = '
4077 || to_char(p_position_id)
4078 || ' and ppd.position_definition_id = pap.position_definition_id and hl.application_id = 800 and hl.lookup_type = '''
4079 || p_type || ''' and hl.lookup_code = ppd.' || p_segment;
4080 execute immediate l_sql into l_name;
4081 else
4082 l_name := null;
4083 end if;
4084 exception
4085 when no_data_found then
4086 l_name := null;
4087 end;
4088 else
4089 raise_application_error(-20002, 'The Occupational data in the User Table ZA_OCCUPATIONAL_TYPES refers to an invalid Flexfield.');
4090 end if;
4091 hr_utility.set_location('l_name : ' ||l_name,10);
4092 return l_name;
4093
4094 end get_occupational_data;
4095 --
4096 -- This function returns the occupational category from the common lookups table.
4097 function get_occupational_category
4098 (
4099 p_report_date in per_all_assignments_f.effective_end_date%type,
4100 p_assignment_id in per_all_assignments_f.assignment_id%type,
4101 p_job_id in per_all_assignments_f.job_id%type,
4102 p_grade_id in per_all_assignments_f.grade_id%type,
4103 p_position_id in per_all_assignments_f.position_id%type,
4104 p_business_group_id in per_all_assignments_f.business_group_id%type
4105 ) return varchar2 is
4106 l_cat_name hr_lookups.meaning%type;
4107 begin
4108 -- Check whether we have cached the location of Occupational data
4109 hr_utility.set_location('Entering get_occupational_category', 10);
4110 hr_utility.set_location('p_assignment_id : '||p_assignment_id, 10);
4111 hr_utility.set_location('p_job_id: '||p_job_id, 10);
4112 hr_utility.set_location('p_grade_id: '||p_grade_id, 10);
4113 hr_utility.set_location('p_position_id: '||p_position_id, 10);
4114
4115 if g_cat_flex is null then
4116 cache_occupational_location(p_report_date, p_business_group_id);
4117 end if;
4118 --
4119 l_cat_name := get_occupational_data
4120 (
4121 p_type => 'ZA_WSP_OCCUPATIONAL_CATEGORIES',
4122 p_flex => upper(g_cat_flex),
4123 p_segment => g_cat_segment,
4124 p_job_id => p_job_id,
4125 p_grade_id => p_grade_id,
4126 p_position_id => p_position_id
4127 );
4128 return l_cat_name;
4129 --
4130 end get_occupational_category;
4131 --
4132 --
4133 /****************************************************************************
4134 Name : archive_code
4135 Description : Archive person level WSP and ATR related data.
4136 *****************************************************************************/
4137 procedure archive_data ( p_assactid in number
4138 , p_effective_date in date
4139 ) is
4140
4141 l_proc varchar2(50);
4142 l_assignment_id per_all_assignments_f.assignment_id%type;
4143 l_person_id per_all_assignments_f.person_id%type;
4144 l_ass_cat_name hr_lookups.meaning%type;
4145 l_race per_all_people_f.per_information4%type;
4146 l_sex per_all_people_f.sex%type;
4147 l_legal_entity_id number(30);
4148 l_disability varchar2(30);
4149
4150 begin
4151 -- hr_utility.trace_on(null,'ZAWSP');
4152 l_proc := g_package || 'archive_code';
4153 hr_utility.trace ('Entering '||l_proc);
4154 hr_utility.trace('p_assactid = '|| p_assactid);
4155 hr_utility.trace('p_effective_date = '|| to_char(p_effective_date, 'DD-MON-YYYY'));
4156
4157 -- Occupation category for current person : Emp Equity report setup
4158 -- pick all persons for whom assgn actn id is created : AM
4159 Select paaf.person_id
4160 , paaf.assignment_id
4161 , perf.per_information4 -- Race
4162 , perf.sex
4163 , perf.registered_disabled_flag
4164 , paei.aei_information7 --legal_entity_id
4165 , per_za_wsp_archive_pkg.get_occupational_category( p_effective_date
4166 , paaf.assignment_id
4167 , paaf.job_id
4168 , paaf.grade_id
4169 , paaf.position_id
4170 , paaf.business_group_id)
4171 Into l_person_id
4172 , l_assignment_id
4173 , l_race
4174 , l_sex
4175 , l_disability
4176 , l_legal_entity_id
4177 , l_ass_cat_name
4178 From per_all_assignments_f paaf
4179 , pay_assignment_actions paa
4180 , per_all_people_f perf
4181 , per_assignment_extra_info paei
4182 Where paa.assignment_action_id = p_assactid
4183 and paa.assignment_id = paaf.assignment_id
4184 and paaf.person_id = perf.person_id
4185 and p_effective_date between perf.effective_start_date
4186 and perf.effective_end_date
4187 and p_effective_date between paaf.effective_start_date
4188 and paaf.effective_end_date
4189 and paaf.assignment_id = paei.assignment_id
4190 and paei.aei_information_category = 'ZA_SPECIFIC_INFO';
4191
4192 hr_utility.set_location('g_wsp_certifications_tab.COUNT : '||g_wsp_certifications_tab.COUNT ,10);
4193 if l_ass_cat_name is not null then
4194 -- Archive WSP data
4195 archive_wsp_data(p_assactid, l_person_id, l_assignment_id, l_race, l_sex, l_ass_cat_name, l_legal_entity_id, l_disability);
4196
4197 -- Archive ATR data
4198 archive_atr_data(p_assactid, l_person_id, l_assignment_id, l_race, l_sex, l_ass_cat_name, l_legal_entity_id, l_disability);
4199 end if;
4200
4201 --Reset the pl/sql tables
4202 --hr_utility.trace ('Flush data from all the pl/sql tables ');
4203 --reset_tables;
4204 --
4205 hr_utility.trace('Leaving '||l_proc);
4206 -- hr_utility.trace_off;
4207 end archive_data;
4208 --
4209
4210 end per_za_wsp_archive_pkg;
4211 --
4212