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