1 PACKAGE BODY hr_du_dp_pc_conversion AS
2 /* $Header: perdupc.pkb 115.21 2002/11/28 16:53:55 apholt noship $ */
3
4
5 /*--------------------------- GLOBAL VARIABLES ----------------------------*/
6
7 g_insert_table INSERT_TABLE_TYPE;
8 g_column_headings COLUMN_HEADINGS_TABLE;
9 g_column_mapped_to COLUMN_MAPPED_TO_TABLE;
10 g_space VARCHAR2(100) := hr_du_utility.local_CHR(32);
11 g_start_table STARTING_POINT_TABLE;
12
13 /*-------------------------------------------------------------------------*/
14
15
16 -- ------------------------- STORE_COLUMN_MAPPINGS ------------------------
17 -- Description: This Caches the mapped_to_names and the mapping type
18 -- into a SQL table to cut down on the number of select statements used
19 --
20 -- Input Parameters
21 -- p_api_module_id - Identify the api being used
22 -- ------------------------------------------------------------------------
23 PROCEDURE STORE_COLUMN_MAPPINGS (p_api_module_id IN NUMBER)
24 IS
25
26 l_mapped_name R_MAPPED_TYPE;
27 l_counter NUMBER := 1 ;
28
29 CURSOR csr_mapped_to_name IS
30 SELECT mapping_type, mapped_to_name, column_name
31 FROM hr_du_column_mappings
32 WHERE api_module_id = p_api_module_id;
33
34 BEGIN
35 --
36 hr_du_utility.message('ROUT','entry:hr_du_dp_pc_conversion.
37 store_column_mappings', 5);
38 hr_du_utility.message('PARA', '(p_api_module_id - ' || p_api_module_id ||
39 ')' , 10);
40
41 OPEN csr_mapped_to_name;
42 --
43 LOOP
44 FETCH csr_mapped_to_name INTO l_mapped_name;
45 IF csr_mapped_to_name%NOTFOUND THEN
46 EXIT;
47 ELSE
48 g_column_mapped_to(l_counter).r_mapping_type :=
49 l_mapped_name.r_mapping_type;
50 g_column_mapped_to(l_counter).r_mapped_to_name :=
51 l_mapped_name.r_mapped_to_name;
52 g_column_mapped_to(l_counter).r_mapped_name :=
53 l_mapped_name.r_mapped_name;
54 l_counter := l_counter + 1;
55 END IF;
56 END LOOP;
57 --
58 CLOSE csr_mapped_to_name;
59
60 --
61 hr_du_utility.message('ROUT','exit:hr_du_dp_pc_conversion. ' ||
62 'store_column_mappings', 15);
63 --
64 EXCEPTION
65 WHEN OTHERS THEN
66 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.
67 store_column_mappings', '(none)', 'R');
68 RAISE;
69 --
70 END STORE_COLUMN_MAPPINGS;
71
72
73 -- ------------------------- STORE_COLUMN_HEADINGS ------------------------
74 -- Description: This procedure extracts the column headings for the given
75 -- line and caches them into a table to save on the number of select
76 -- statements used in the code.
77 --
78 -- Input Parameters
79 -- p_line_id - Identifies the UPLOAD_LINE to be used
80 -- ------------------------------------------------------------------------
81 PROCEDURE STORE_COLUMN_HEADINGS (p_line_id IN NUMBER)
82 IS
83
84 BEGIN
85 --
86 hr_du_utility.message('ROUT','entry:hr_du_dp_pc_conversion.
87 store_column_headings', 5);
88 hr_du_utility.message('PARA', '(p_line_id - ' || p_line_id || ')'
89 , 10);
90
91 hr_du_utility.message('INFO','Select Statement Start ' , 15);
92
93 SELECT
94 PVAL001, PVAL002, PVAL003, PVAL004 , PVAL005 , PVAL006,
95 PVAL007, PVAL008, PVAL009, PVAL010 , PVAL011, PVAL012,
96 PVAL013, PVAL014, PVAL015, PVAL016 , PVAL017, PVAL018,
97 PVAL019, PVAL020, PVAL021, PVAL022, PVAL023, PVAL024,
98 PVAL025, PVAL026, PVAL027, PVAL028, PVAL029, PVAL030,
99 PVAL031, PVAL032, PVAL033, PVAL034, PVAL035, PVAL036,
100 PVAL037, PVAL038, PVAL039, PVAL040, PVAL041, PVAL042,
101 PVAL043, PVAL044, PVAL045, PVAL046, PVAL047, PVAL048,
102 PVAL049, PVAL050, PVAL051, PVAL052, PVAL053, PVAL054,
103 PVAL055, PVAL056, PVAL057, PVAL058, PVAL059, PVAL060,
104 PVAL061, PVAL062, PVAL063, PVAL064, PVAL065, PVAL066,
105 PVAL067, PVAL068, PVAL069, PVAL070, PVAL071, PVAL072,
106 PVAL073, PVAL074, PVAL075, PVAL076, PVAL077, PVAL078,
107 PVAL079, PVAL080, PVAL081, PVAL082, PVAL083, PVAL084,
108 PVAL085, PVAL086, PVAL087, PVAL088, PVAL089, PVAL090,
109 PVAL091, PVAL092, PVAL093, PVAL094, PVAL095, PVAL096,
110 PVAL097, PVAL098, PVAL099, PVAL100, PVAL101, PVAL102,
111 PVAL103, PVAL104, PVAL105,PVAL106, PVAL107, PVAL108,
112 PVAL109, PVAL110, PVAL111,PVAL112, PVAL113, PVAL114,
113 PVAL115, PVAL116, PVAL117,PVAL118, PVAL119, PVAL120,
114 PVAL121, PVAL122, PVAL123,PVAL124, PVAL125, PVAL126,
115 PVAL127, PVAL128, PVAL129,PVAL130, PVAL131, PVAL132,
116 PVAL133, PVAL134, PVAL135,PVAL136, PVAL137, PVAL138,
117 PVAL139, PVAL140, PVAL141,PVAL142, PVAL143, PVAL144,
118 PVAL145, PVAL146, PVAL147,PVAL148, PVAL149, PVAL150,
119 PVAL151, PVAL152, PVAL153,PVAL154, PVAL155, PVAL156,
120 PVAL157, PVAL158, PVAL159,PVAL160, PVAL161, PVAL162,
121 PVAL163, PVAL164, PVAL165,PVAL166, PVAL167, PVAL168,
122 PVAL169, PVAL170, PVAL171,PVAL172, PVAL173, PVAL174,
123 PVAL175, PVAL176, PVAL177,PVAL178, PVAL179, PVAL180,
124 PVAL181, PVAL182, PVAL183,PVAL184, PVAL185, PVAL186,
125 PVAL187, PVAL188, PVAL189,PVAL190, PVAL191, PVAL192,
126 PVAL193, PVAL194, PVAL195,PVAL196, PVAL197, PVAL198,
127 PVAL199, PVAL200, PVAL201,PVAL202, PVAL203, PVAL204,
128 PVAL205, PVAL206, PVAL207,PVAL208, PVAL209, PVAL210,
129 PVAL211, PVAL212, PVAL213,PVAL214, PVAL215, PVAL216,
130 PVAL217, PVAL218, PVAL219,PVAL220, PVAL221, PVAL222,
131 PVAL223, PVAL224, PVAL225,PVAL226, PVAL227, PVAL228,
132 PVAL229, PVAL230
133 INTO
134 g_column_headings(1), g_column_headings(2), g_column_headings(3),
135 g_column_headings(4), g_column_headings(5), g_column_headings(6),
136 g_column_headings(7), g_column_headings(8), g_column_headings(9),
137 g_column_headings(10), g_column_headings(11), g_column_headings(12),
138 g_column_headings(13), g_column_headings(14), g_column_headings(15),
139 g_column_headings(16), g_column_headings(17), g_column_headings(18),
140 g_column_headings(19), g_column_headings(20), g_column_headings(21),
141 g_column_headings(22), g_column_headings(23), g_column_headings(24),
142 g_column_headings(25), g_column_headings(26), g_column_headings(27),
143 g_column_headings(28), g_column_headings(29), g_column_headings(30),
144 g_column_headings(31), g_column_headings(32), g_column_headings(33),
145 g_column_headings(34), g_column_headings(35), g_column_headings(36),
146 g_column_headings(37), g_column_headings(38), g_column_headings(39),
147 g_column_headings(40), g_column_headings(41), g_column_headings(42),
148 g_column_headings(43), g_column_headings(44), g_column_headings(45),
149 g_column_headings(46), g_column_headings(47), g_column_headings(48),
150 g_column_headings(49), g_column_headings(50), g_column_headings(51),
151 g_column_headings(52), g_column_headings(53), g_column_headings(54),
152 g_column_headings(55), g_column_headings(56), g_column_headings(57),
153 g_column_headings(58), g_column_headings(59), g_column_headings(60),
154 g_column_headings(61), g_column_headings(62), g_column_headings(63),
155 g_column_headings(64), g_column_headings(65), g_column_headings(66),
156 g_column_headings(67), g_column_headings(68), g_column_headings(69),
157 g_column_headings(70), g_column_headings(71), g_column_headings(72),
158 g_column_headings(73), g_column_headings(74), g_column_headings(75),
159 g_column_headings(76), g_column_headings(77), g_column_headings(78),
160 g_column_headings(79), g_column_headings(80), g_column_headings(81),
161 g_column_headings(82), g_column_headings(83), g_column_headings(84),
162 g_column_headings(85), g_column_headings(86), g_column_headings(87),
163 g_column_headings(88), g_column_headings(89), g_column_headings(90),
164 g_column_headings(91), g_column_headings(92), g_column_headings(93),
165 g_column_headings(94), g_column_headings(95), g_column_headings(96),
166 g_column_headings(97), g_column_headings(98), g_column_headings(99),
167 g_column_headings(100),g_column_headings(101),g_column_headings(102),
168 g_column_headings(103),g_column_headings(104),g_column_headings(105),
169 g_column_headings(106),g_column_headings(107),g_column_headings(108),
170 g_column_headings(109),g_column_headings(110),g_column_headings(111),
171 g_column_headings(112),g_column_headings(113),g_column_headings(114),
172 g_column_headings(115),g_column_headings(116),g_column_headings(117),
173 g_column_headings(118),g_column_headings(119),g_column_headings(120),
174 g_column_headings(121),g_column_headings(122),g_column_headings(123),
175 g_column_headings(124),g_column_headings(125),g_column_headings(126),
176 g_column_headings(127),g_column_headings(128),g_column_headings(129),
177 g_column_headings(130),g_column_headings(131),g_column_headings(132),
178 g_column_headings(133),g_column_headings(134),g_column_headings(135),
179 g_column_headings(136),g_column_headings(137),g_column_headings(138),
180 g_column_headings(139),g_column_headings(140),g_column_headings(141),
181 g_column_headings(142),g_column_headings(143),g_column_headings(144),
182 g_column_headings(145),g_column_headings(146),g_column_headings(147),
183 g_column_headings(148),g_column_headings(149),g_column_headings(150),
184 g_column_headings(151),g_column_headings(152),g_column_headings(153),
185 g_column_headings(154),g_column_headings(155),g_column_headings(156),
186 g_column_headings(157),g_column_headings(158),g_column_headings(159),
187 g_column_headings(160),g_column_headings(161),g_column_headings(162),
188 g_column_headings(163),g_column_headings(164),g_column_headings(165),
189 g_column_headings(166),g_column_headings(167),g_column_headings(168),
190 g_column_headings(169),g_column_headings(170),g_column_headings(171),
191 g_column_headings(172),g_column_headings(173),g_column_headings(174),
192 g_column_headings(175),g_column_headings(176),g_column_headings(177),
193 g_column_headings(178),g_column_headings(179),g_column_headings(180),
194 g_column_headings(181),g_column_headings(182),g_column_headings(183),
195 g_column_headings(184),g_column_headings(185),g_column_headings(186),
196 g_column_headings(187),g_column_headings(188),g_column_headings(189),
197 g_column_headings(190),g_column_headings(191),g_column_headings(192),
198 g_column_headings(193),g_column_headings(194),g_column_headings(195),
199 g_column_headings(196),g_column_headings(197),g_column_headings(198),
200 g_column_headings(199),g_column_headings(200),g_column_headings(201),
201 g_column_headings(202),g_column_headings(203),g_column_headings(204),
202 g_column_headings(205),g_column_headings(206),g_column_headings(207),
203 g_column_headings(208),g_column_headings(209),g_column_headings(210),
204 g_column_headings(211),g_column_headings(212),g_column_headings(213),
205 g_column_headings(214),g_column_headings(215),g_column_headings(216),
206 g_column_headings(217),g_column_headings(218),g_column_headings(219),
207 g_column_headings(220),g_column_headings(221),g_column_headings(222),
208 g_column_headings(223),g_column_headings(224),g_column_headings(225),
209 g_column_headings(226),g_column_headings(227),g_column_headings(228),
210 g_column_headings(229),g_column_headings(230)
211 FROM HR_DU_UPLOAD_LINES
212 WHERE UPLOAD_LINE_ID = p_line_id;
213
214 hr_du_utility.message('INFO','Select Statement Ends ' , 20);
215
216 --
217 hr_du_utility.message('ROUT','exit:hr_du_dp_pc_conversion.' ||
218 ' store_column_headings', 25);
219 --
220 EXCEPTION
221 WHEN OTHERS THEN
222 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.
223 store_column_headings',
224 '(none)', 'R');
225 RAISE;
226 --
227 END STORE_COLUMN_HEADINGS;
228
229
230 -- ------------------------- VERIFY_API_ATTACHED ---------------------------
231 -- Description: This Procedure simply checks that the referencing columns
232 -- with in the HR_DU_UPLOAD_LINES have thier appropriate file attached. i.e.
233 -- in the person api an error would be raised if they had an address
234 -- and the address flat file as not present.
235 --
236 -- Input Parameters
237 -- p_mapped_name - This is the name that your looking for when
238 -- running the cursor and comparing to mapped_to_name
239 --
240 -- p_upload_header_id - Identifies the upload_header in the upload
241 --
242 -- p_api_module_id - Identifies the API modules from th others
243 -- ------------------------------------------------------------------------
244 PROCEDURE VERIFY_API_ATTACHED (p_mapped_name IN VARCHAR2,
245 p_upload_header_id IN NUMBER,
246 p_api_module_id IN NUMBER)
247 IS
248
249 l_parent_api_module_id NUMBER;
250 l_descriptor_value VARCHAR2(2000);
251 e_fatal_error EXCEPTION;
252 l_fatal_error_message VARCHAR2(2000);
253
254 CURSOR csr_api_id IS
255 SELECT parent_api_module_id
256 FROM hr_du_column_mappings
257 WHERE api_module_id = p_api_module_id
258 AND mapped_to_name = p_mapped_name;
259
260 CURSOR csr_api_file IS
261 SELECT des.descriptor
262 FROM hr_api_modules api,
263 hr_du_descriptors des,
264 hr_du_upload_headers head
265 WHERE api.api_module_id = l_parent_api_module_id
266 AND head.upload_header_id = p_upload_header_id
267 AND head.upload_id = des.upload_id
268 AND upper(api.module_name) = upper(des.descriptor);
269
270
271 BEGIN
272 --
273 hr_du_utility.message('ROUT','entry:hr_du_dp_pc_conversion.
274 verify_api_attached', 5);
275 hr_du_utility.message('PARA', '(p_mapped_name - ' || p_mapped_name ||
276 ')(p_api_module_id - ' || p_api_module_id ||
277 ')(p_upload_header_id - ' || p_upload_header_id ||')'
278 , 10);
279 --
280 OPEN csr_api_id;
281 --
282 FETCH csr_api_id INTO l_parent_api_module_id;
283 IF csr_api_id%NOTFOUND THEN
284 l_fatal_error_message := 'Unable to retrieve the parent_api_module_id'
285 || ' for referencing column ' ||
286 p_mapped_name;
287 RAISE e_fatal_error;
288 END IF;
289 --
290 CLOSE csr_api_id;
291
292 OPEN csr_api_file;
293 --
294 FETCH csr_api_file INTO l_descriptor_value;
295 IF csr_api_file%NOTFOUND THEN
296 l_fatal_error_message := 'API file is not attached to handle the ' ||
297 'referencing column ' || p_mapped_name;
298 RAISE e_fatal_error;
299 END IF;
300 --
301 CLOSE csr_api_file;
302
303 --
304 hr_du_utility.message('ROUT','exit:hr_du_dp_pc_conversion.' ||
305 ' verify_api_attached', 15);
306 --
307 EXCEPTION
308 WHEN e_fatal_error THEN
309 hr_du_utility.error(SQLCODE,'hr_du_dp_pc_conversion.verify_api_attached'
310 ,l_fatal_error_message, 'R');
311 RAISE;
312 WHEN OTHERS THEN
313 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.verify_api_attached',
314 '(none)', 'R');
315 RAISE;
316 --
317 END VERIFY_API_ATTACHED;
318
319
320 -- --------------------------- REMOVE_SPACES --------------------------------
321 -- Description: Leading and Trailing spaces (if they exist) are removed
322 -- from the varchar2 that is passed in P_WORD. A new varchar with no spaces
323 -- is then returned along side a boolean stating if it had spaces or not.
324 --
325 -- Output Parameters
326 -- p_word - This is the varchar passed in to see
327 --
328 -- p_spaces - the delimiter to be used
329 --
330 -- ------------------------------------------------------------------------
331 PROCEDURE REMOVE_SPACES (p_word IN OUT NOCOPY VARCHAR2, p_spaces OUT NOCOPY BOOLEAN)
332 IS
333
334 l_word_length NUMBER;
335 l_temp_word VARCHAR2(200);
336 l_exit_1 BOOLEAN := FALSE;
337 l_exit_2 BOOLEAN := FALSE;
338 l_exit_3 BOOLEAN := FALSE;
339 l_new_word VARCHAR2(2000);
340
341 BEGIN
342
343 --hr_du_utility.message('ROUT',
344 -- 'entry:hr_du_dp_pc_conversion.remove_spaces', 5);
345 --hr_du_utility.message('PARA',
346 -- '(p_word - ' || p_word || ')' , 10);
347
348 p_spaces := FALSE;
349 l_word_length := LENGTHB(p_word);
350
351 IF l_word_length IS NULL THEN
352 l_word_length := 0;
353 END IF;
354
355 --hr_du_utility.message('INFO', 'l_word_length - ' || l_word_length, 99);
356
357 IF l_word_length = 1 AND p_word = g_space THEN
358 p_word := NULL;
359 l_exit_1 := TRUE;
360 ELSE
361 l_new_word := p_word;
362 FOR i IN 1..l_word_length LOOP
363 --
364 l_temp_word := SUBSTRB(p_word, i, 1);
365 IF l_temp_word <> g_space THEN
366 EXIT;
367 ELSE
368 l_new_word := SUBSTRB(p_word, i + 1);
369 l_exit_2 := TRUE;
370 END IF;
371 --
372 END LOOP;
373 END IF;
374
375 IF l_exit_1 = FALSE THEN
376 p_word := l_new_word;
377 FOR j IN REVERSE 1..l_word_length LOOP
378 --
379 l_temp_word := SUBSTRB(p_word, j, 1);
380 IF l_temp_word <> g_space THEN
381 EXIT;
382 ELSE
383 l_new_word := SUBSTRB(p_word, 1, j - 1 );
384 l_exit_3 := TRUE;
385 END IF;
386 --
387 END LOOP;
388 END IF;
389
390 IF (l_exit_1 = TRUE) OR (l_exit_2 = TRUE) OR (l_exit_3 = TRUE) THEN
391 p_spaces := TRUE;
392 END IF;
393
394 p_word := l_new_word;
395
396 --
397 EXCEPTION
398 WHEN OTHERS THEN
399 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.remove_spaces',
400 '(none)', 'R');
401 RAISE;
402 --
403 END REMOVE_SPACES;
404
405
406 -- ------------------------- CP_REFERENCING_COLUMNS -----------------------
407 -- Description: Builds up the strings R_STRING_APIS, R_api_PVALS and
408 -- R_GENERIC_PVAL in the PL/SQL table. Loops around the column headings
409 -- for each api and checks them against the cursor constraints, if they
410 -- meet the requirements then they are placed into the strings.
411 --
412 -- Input Parameters
413 --
414 -- p_array_pos - the array position in the PL/SQL table that is
415 -- currently being used.
416 --
417 -- ------------------------------------------------------------------------
418 PROCEDURE CP_REFERENCING_COLUMNS(p_array_pos IN NUMBER)
419 IS
420
421 l_string_apis VARCHAR2(100);
422 l_api_PVALS VARCHAR2(300);
423 l_generic_pval VARCHAR2(30);
424 l_current_pval VARCHAR2(10);
425 l_inner_pval VARCHAR2(10);
426 l_pval_field VARCHAR2(50);
427 l_inner_field VARCHAR2(50);
428 l_parent_api_module_id NUMBER;
429 l_parent_table VARCHAR2(35);
430 l_length NUMBER;
431 l_string_length NUMBER;
432
433 --Checks the column name to see if it has the properties of holding the
434 --calling api_modules id (parent's id) in that column.
435
436 CURSOR csr_parent_api_module_id IS
437 SELECT parent_api_module_id
438 FROM hr_du_column_mappings
439 WHERE mapping_type = 'D'
440 AND parent_api_module_id IS NOT null
441 AND column_name = l_pval_field;
442
443 --Check to see if the column heading has the properties of a generic
444 --column. Due to some api modules having two columns specifing both a column
445 --to store the api module id and the line id.
446
447 CURSOR csr_parent_table_column IS
448 SELECT parent_table
449 FROM hr_du_column_mappings
450 WHERE mapping_type = 'D'
451 AND parent_table is not null
452 AND column_name = l_pval_field;
453
454 BEGIN
455 --
456 hr_du_utility.message('ROUT',
457 'entry:hr_du_dp_pc_conversion.cp_referencing_columns',
458 5);
459 hr_du_utility.message('PARA', '(p_array_pos - ' || p_array_pos || ')'
460 , 10);
461 --
462 l_string_apis := null;
463 l_api_PVALS := null;
464 l_generic_pval := null;
465
466 --loops around all the column headings within the upload_line
467 FOR i IN 1..230 LOOP
468 --
469 l_current_pval := LPAD(i,3,'0');
470 l_current_pval := 'PVAL' || l_current_pval;
471 --fetch the heading stored within the specified upload line
472 l_pval_field := g_column_headings(i);
473
474 OPEN csr_parent_api_module_id;
475 --
476 FETCH csr_parent_api_module_id INTO l_parent_api_module_id;
477 IF csr_parent_api_module_id%NOTFOUND THEN
478 --no match on normal case so trying generic case
479 OPEN csr_parent_table_column;
480 --
481 FETCH csr_parent_table_column INTO l_parent_table;
482 IF csr_parent_table_column%FOUND THEN
483 --loop through the column headings again to search for the
484 --position in the line of where the api module id will be stored
485 hr_du_utility.message('INFO', l_parent_table, 15);
486 FOR j IN 1..230 LOOP
487 --
488 l_inner_pval := LPAD(j,3,'0');
489 l_inner_pval := 'PVAL' || l_inner_pval;
490
491 l_inner_field := g_column_headings(j);
492
493 hr_du_utility.message('INFO', l_inner_field, 20);
494
495 IF l_parent_table = l_inner_field THEN
496 --found the exact position in the line where the api id
497 --from the calling table will be stored (l_inner_pval).
498 --
499 --storing a null in l_string_apis will signal later on
500 --that a generic column has been found
501 l_string_apis := l_string_apis || null || ',';
502 l_api_PVALS := l_api_PVALS || l_current_pval || ',';
503 l_generic_pval := l_generic_pval || l_inner_pval || ',';
504 EXIT;
505 --
506 END IF;
507 --
508 END LOOP;
509 END IF;
510 --
511 CLOSE csr_parent_table_column;
512 --
513 ELSE
514 --
515 hr_du_utility.message('INFO', l_parent_api_module_id , 25);
516 l_string_apis := l_string_apis || l_parent_api_module_id || ',';
517 l_api_PVALS := l_api_PVALS || l_current_pval || ',';
518 --
519 END IF;
520 --
521 CLOSE csr_parent_api_module_id;
522 END LOOP;
523
524 --The commas are left in at this section for this causes problems
525 --later on in the function PROCESS_LINE where 'null,' which is a check
526 --with the ',' removed would be 'null' for the line.
527 g_insert_table(p_array_pos).r_string_apis := l_string_apis;
528 g_insert_table(p_array_pos).r_api_PVALS := l_api_PVALS;
529 g_insert_table(p_array_pos).r_generic_pval := l_generic_pval;
530
531 hr_du_utility.message('INFO', 'l_string_apis : ' || l_string_apis , 30);
532 hr_du_utility.message('INFO', 'l_api_PVALS : ' || l_api_PVALS , 35);
533 hr_du_utility.message('INFO', 'l_generic_pval : ' || l_generic_pval , 40);
534
535 --
536 hr_du_utility.message('ROUT',
537 'exit:hr_du_dp_pc_conversion.cp_referencing_columns', 45);
538 --
539 EXCEPTION
540 WHEN OTHERS THEN
541 hr_du_utility.error(SQLCODE,
542 'hr_du_dp_pc_conversion.cp_referencing_columns', '(none)', 'R');
543 RAISE;
544 --
545 END CP_REFERENCING_COLUMNS;
546
547
548
549 -- ----------------------- API_MODULE_ID_TO_TABLE_ID ----------------------
550 -- Description: Works through the column R_REF_COL_APIS in the PL/SQL
551 -- table changing the string of the actual api module id's from the
552 -- HR_API_MODULES table, to a string of the PL/SQL row id's. Which in turn
553 -- relate to the same API.
554 -- ------------------------------------------------------------------------
555 PROCEDURE API_MODULE_ID_TO_TABLE_ID IS
556
557 l_size NUMBER;
558 l_number_references NUMBER;
559 l_reference_pval VARCHAR2(200);
560 l_new_string VARCHAR2(300) := null;
561 l_length NUMBER;
562 l_string_length NUMBER;
563
564 BEGIN
565 --
566 hr_du_utility.message('ROUT',
567 'entry:hr_du_dp_pc_conversion.API_MODULE_id_to_table_id', 5);
568 --
569
570
571 --find out the size of the PL/SQL table
572 l_size := g_insert_table.count;
573 --Loop through the rows in the table to find the one associated with the
574 --parent_API_MODULE_id
575 FOR i IN 1..l_size LOOP
576 IF g_insert_table(i).r_ref_Col_apis IS NOT NULL THEN
577 --The next step is to see if there are any referencing columns
578 --associated with the api (in R_REF_COL_APIS)
579 l_new_string := null;
580
581 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
582 g_insert_table(i).r_ref_Col_apis);
583
584 l_number_references :=
585 hr_du_di_insert.WORDS_ON_LINE(g_insert_table(i).r_ref_Col_apis);
586
587 hr_du_utility.message('INFO', g_insert_table(i).r_ref_Col_apis , 10);
588
589 --loop around for each reference trying to match its api id with the
590 --table id
591 FOR j IN 1..l_number_references LOOP
592 l_reference_pval := null;
593
594 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
595 g_insert_table(i).r_ref_Col_apis);
596
597 l_reference_pval := hr_du_di_insert.Return_Word(
598 g_insert_table(i).r_ref_Col_apis, j);
599
600 hr_du_utility.message('INFO', l_reference_pval , 15);
601
602 --create a string with the corresponding PL/SQL table id's
603 FOR k IN 1..l_size LOOP
604 IF l_reference_pval = g_insert_table(k).r_api_id THEN
605 l_new_string := l_new_string || k || ',';
606 EXIT;
607 END IF;
608 END LOOP;
609 END LOOP;
610 l_length := LENGTHB(',');
611 l_string_length := LENGTHB(l_new_string);
612 l_new_string := SUBSTRB(l_new_string,1, (l_string_length - l_length ));
613 hr_du_utility.message('INFO', 'l_new_string : ' || l_new_string , 20);
614
615 --replace the old R_REF_COL_APIS with the new_string
616 g_insert_table(i).r_ref_Col_apis := l_new_string;
617 END IF;
618 END LOOP;
619
620 --
621 hr_du_utility.message('ROUT',
622 'exit:hr_du_dp_pc_conversion.
623 API_MODULE_id_to_table_id', 25);
624 --
625 EXCEPTION
626 WHEN OTHERS THEN
627 hr_du_utility.error(SQLCODE,
628 'hr_du_dp_pc_conversion.API_MODULE_id_to_table_id', '(none)', 'R');
629 RAISE;
630 --
631 END API_MODULE_ID_TO_TABLE_ID;
632
633
634 -- --------------------- RETURN_PARENT_API_MODULE_ID ----------------------
635 -- Description: Returns the parent_api_module_id from HR_DU_COLUMN_MAPPINGS
636 -- where the p_reference_string matches an entry in HR_DU_COLUMN_MAPPINGS
637 -- field mapped_to_name.
638 --
639 -- Input Parameters
640 -- p_api_module_id - Identifies a specific api in the
641 -- HR_API_MODULES
642 --
643 -- p_reference_string - String which holds the value to be compared
644 -- to the column mapped_to_name within the
645 -- HR_DU_COLUMN_MAPPINGS table
646 -- Output Parameters
647 --
648 -- l_parent_api_module_id - Parent_api_module_id from the
649 -- HR_DU_COLUMN_MAPPINGS table if match found
650 --
651 -- ------------------------------------------------------------------------
652 FUNCTION RETURN_PARENT_API_MODULE_ID (p_api_module_id IN NUMBER,
653 p_reference_string IN VARCHAR2) RETURN NUMBER
654 IS
655
656 e_fatal_error EXCEPTION;
657 l_fatal_error_message VARCHAR2(2000);
658 l_parent_api_module_id NUMBER;
659
660 CURSOR csr_parent_id IS
661 SELECT parent_api_module_id
662 FROM hr_du_column_mappings
663 WHERE api_module_id = p_api_module_id
664 AND mapped_to_name = p_reference_string;
665
666
667 BEGIN
668 --
669 hr_du_utility.message('ROUT',
670 'entry:hr_du_dp_pc_conversion.return_parent_api_module_id', 5);
671 hr_du_utility.message('PARA', '(p_api_module_id - ' || p_api_module_id ||
672 ')(p_reference_string - ' || p_reference_string || ')' , 10);
673 --
674 OPEN csr_parent_id;
675 --
676 FETCH csr_parent_id INTO l_parent_api_module_id;
677 IF csr_parent_id%NOTFOUND THEN
678 l_fatal_error_message := 'No PARENT_API_MODULE_ID found with the ' ||
679 ' api module id and the mapped_to_name provided ' ||
680 '( p_api_module_id : ' || p_api_module_id ||
681 ' p_reference_string : ' || p_reference_string || ' )';
682 RAISE e_fatal_error;
683 END IF;
684 --
685 CLOSE csr_parent_id;
686
687 --
688 hr_du_utility.message('ROUT',
689 'exit:hr_du_dp_pc_conversion.return_parent_api_module_id', 15);
690 hr_du_utility.message('PARA', '(l_parent_api_module_id - ' ||
691 l_parent_api_module_id || ')' , 20);
692 --
693 RETURN l_parent_api_module_id;
694
695 EXCEPTION
696 WHEN e_fatal_error THEN
697 hr_du_utility.error(SQLCODE,
698 'hr_du_dp_pc_conversion.return_parent_api_module_id',
699 l_fatal_error_message, 'R');
700 RAISE;
701 WHEN OTHERS THEN
702 hr_du_utility.error(SQLCODE,
703 'hr_du_dp_pc_conversion.return_parent_api_module_id', '(none)', 'R');
704 RAISE;
705 --
706 END RETURN_PARENT_API_MODULE_ID;
707
708
709 -- ------------------------- RETURN_FIELD_VALUE ----------------------------
710 -- Description: Dynamic SQL statement contained in a Function. Simply
711 -- performs the following :-
712 --
713 -- SELECT p_field_name
714 -- FROM p_table
715 -- WHERE p_field_pk = p_record_id;
716 --
717 -- Input Parameters
718 --
719 -- p_table - The table name of where the info is held.
720 --
721 -- p_record_id - The specific record identifier within the table.
722 --
723 -- p_field_pk - Column name where p_record_id will be contained.
724 --
725 -- p_field_name - Column value to be extracted.
726 --
727 -- Output Parameters
728 --
729 -- l_field_value - The value contained within that field.
730 -- ------------------------------------------------------------------------
731 FUNCTION RETURN_FIELD_VALUE (p_table IN VARCHAR2, p_record_id IN NUMBER,
732 p_field_pk IN VARCHAR2, p_field_name IN VARCHAR2)
733 RETURN VARCHAR2
734 IS
735 l_dyn_sql VARCHAR2(2000);
736 l_field_value VARCHAR2(2000);
737 l_cursor_handle INT;
738 l_rows_processed INT;
739
740 BEGIN
741 --
742 hr_du_utility.message('ROUT',
743 'entry:hr_du_dp_pc_conversion.return_field_value', 5);
744 hr_du_utility.message('PARA', '(p_table - ' || p_table ||
745 ')(p_record_id - ' || p_record_id ||
746 ')(p_field_pk - ' || p_field_pk ||
747 ')(p_field_name - ' || p_field_name || ')'
748 , 10);
749 --
750
751 l_dyn_sql := 'SELECT ' || p_field_name ||' FROM ' || p_table ||
752 ' WHERE ' || p_field_pk || ' = ' || p_record_id;
753
754 hr_du_utility.message('INFO', l_dyn_sql , 15);
755
756 hr_du_utility.dynamic_sql_str(l_dyn_sql, l_field_value, 2000);
757
758 --
759 hr_du_utility.message('ROUT',
760 'exit:hr_du_dp_pc_conversion.return_field_value', 20);
761 hr_du_utility.message('PARA', '(l_field_value - ' || l_field_value || ')',
762 25);
763 --
764
765 RETURN l_field_value;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.return_field_value',
770 '(none)', 'R');
771 RAISE;
772 --
773 END RETURN_FIELD_VALUE;
774
775
776
777 -- ------------------------- MAX_ID_VALUE --------------------------------
778 -- Description: Returns the maximum value stored with in the specified
779 -- column of an HR_DU_UPLOAD_LINES (p_id_pval).
780 --
781 -- Input Parameters
782 --
783 -- p_upload_line_id - Allows the select statement to be confined
784 -- to the one upload line
785 --
786 -- Output Parameters
787 --
788 -- l_max_number - The maximumn value within the column
789 --
790 -- ------------------------------------------------------------------------
791 FUNCTION MAX_ID_VALUE (p_upload_line_id IN NUMBER)
792 RETURN NUMBER
793 IS
794
795 e_fatal_error EXCEPTION;
796 l_fatal_error_message VARCHAR2(2000);
797 l_max_number NUMBER;
798
799 CURSOR csr_max_id IS
800 SELECT MAX(to_number(PVAL001))
801 FROM hr_du_upload_lines
802 WHERE UPLOAD_HEADER_ID IN (SELECT upload_header_id
803 FROM hr_du_upload_lines
804 WHERE upload_line_id = p_upload_line_id)
805 AND LINE_TYPE = 'D';
806
807 BEGIN
808 --
809 hr_du_utility.message('ROUT',
810 'entry:hr_du_dp_pc_conversion.max_id_value', 5);
811 hr_du_utility.message('PARA', '(p_upload_line_id - ' || p_upload_line_id ||
812 ')' ,10);
813 --
814
815 --
816 OPEN csr_max_id;
817 --
818 FETCH csr_max_id INTO l_max_number;
819 IF csr_max_id%NOTFOUND THEN
820 l_fatal_error_message := 'Trying to retrieve the max ID';
821 RAISE e_fatal_error;
822 END IF;
823 --
824 CLOSE csr_max_id;
825
826 --
827 hr_du_utility.message('ROUT',
828 'exit:hr_du_dp_pc_conversion.max_id_value', 20);
829 hr_du_utility.message('PARA', '(l_max_number - ' || l_max_number || ')'
830 , 25);
831 --
832
833 RETURN l_max_number;
834
835 EXCEPTION
836 WHEN e_fatal_error THEN
837 hr_du_utility.error(SQLCODE,
838 'hr_du_dp_pc_conversion.max_id_value',
839 l_fatal_error_message, 'R');
840 RAISE;
841 WHEN OTHERS THEN
842 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.max_id_value',
843 '(none)', 'R');
844 RAISE;
845 --
846 END MAX_ID_VALUE;
847
848
849 -- ----------------------- GENERAL_REFERENCING_COLUMN ----------------------
850 -- Description: Checks to see whether the column name passed is a
851 -- referencing or a datapump column, depending on the variables passed
852 --
853 -- Input Parameters
854 --
855 -- p_pval_field - The name of which column the information
856 -- should be in.
857 --
858 -- p_api_module_id - API Module id relating to HR_API_MODULES
859 --
860 -- p_mapping_type - The output target i.e. 'D' (datapump) or
861 -- 'R' (referencing)
862 --
863 -- Output Parameters
864 --
865 -- l_mapped_name - The name of the field MAPPED_TO_NAME in
866 -- HR_DU_COLUMN_MAPPINGS if a match is found.
867 -- ------------------------------------------------------------------------
868 FUNCTION GENERAL_REFERENCING_COLUMN(p_pval_field IN VARCHAR2,
869 p_api_module_id IN NUMBER,
870 p_mapping_type IN VARCHAR2)
871 RETURN VARCHAR2
872 IS
873
874 l_mapped_name VARCHAR2(30);
875
876 CURSOR csr_ref_col IS
877 SELECT mapped_to_name
878 FROM hr_du_column_mappings
879 WHERE api_module_id = p_api_module_id
880 AND mapping_type = p_mapping_type
881 AND upper(column_name) = upper(p_pval_field);
882
883 BEGIN
884 --
885 hr_du_utility.message('ROUT',
886 'entry:hr_du_dp_pc_conversion.general_referencing_column', 5);
887 hr_du_utility.message('PARA', '(p_pval_field- ' || p_pval_field ||
888 ')(p_api_module_id - ' || p_api_module_id ||
889 ')(p_mapping_type - ' || p_mapping_type || ')'
890 , 10);
891 --
892 OPEN csr_ref_col;
893 --
894 FETCH csr_ref_col INTO l_mapped_name;
895 IF csr_ref_col%NOTFOUND THEN
896 l_mapped_name := null;
897 END IF;
898 --
899 CLOSE csr_ref_col;
900 --
901 hr_du_utility.message('ROUT',
902 'exit:hr_du_dp_pc_conversion.general_referencing_column', 15);
903 hr_du_utility.message('PARA', '(l_mapped_name - ' || l_mapped_name || ')'
904 , 20);
905 --
906
907 RETURN l_mapped_name;
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 hr_du_utility.error(SQLCODE,
912 'hr_du_dp_pc_conversion.general_referencing_column','(none)', 'R');
913 RAISE;
914 --
915 END GENERAL_REFERENCING_COLUMN;
916
917 -- ----------------------- GENERAL_REFERENCING_COLUMN_2 ---------------------
918 -- Description: Checks to see whether the column name passed is a
919 -- referencing or a datapump column, depending on the variables passed
920 --
921 -- Input Parameters
922 --
923 -- p_pval_field - The name of which column the information
924 -- should be in.
925 --
926 -- p_mapping_type - The output target i.e. 'D' (datapump) or
927 -- 'R' (referencing)
928 --
929 -- Output Parameters
930 --
931 -- l_mapped_name - The name of the field MAPPED_TO_NAME in
932 -- HR_DU_COLUMN_MAPPINGS if a match is found.
933 -- ------------------------------------------------------------------------
934 FUNCTION GENERAL_REFERENCING_COLUMN_2(p_pval_field IN VARCHAR2,
935 p_mapping_type IN VARCHAR2) RETURN VARCHAR2
936 IS
937
938 l_counter NUMBER;
939 l_mapped_name VARCHAR2(50) := NULL;
940
941 BEGIN
942
943 l_counter := g_column_mapped_to.COUNT;
944 FOR i IN 1..l_counter LOOP
945 IF (g_column_mapped_to(i).r_mapping_type = p_mapping_type) AND
946 (upper(g_column_mapped_to(i).r_mapped_name) = upper(p_pval_field)) THEN
947 l_mapped_name := g_column_mapped_to(i).r_mapped_to_name;
948 EXIT;
949 END IF;
950 END LOOP;
951
952 RETURN l_mapped_name;
953
954 EXCEPTION
955 WHEN OTHERS THEN
956 hr_du_utility.error(SQLCODE,
957 'hr_du_dp_pc_conversion.general_referencing_column_2','(none)', 'R');
958 RAISE;
959 --
960 END GENERAL_REFERENCING_COLUMN_2;
961
962
963
964 -- ------------------------- INSERT_API_MODULE_IDS -------------------------
965 -- Description: The R_API_ID column of the PL/SQL table is populated with
966 -- API Module id's from HR_API_MODULES table. The id's are ordered by
967 -- PROCESS_ORDER.
968 --
969 -- Input Parameters
970 --
971 -- p_upload_id - Identifies the entry in the upload table that the
972 -- referencing will be applied to.
973 --
974 -- ------------------------------------------------------------------------
975 PROCEDURE INSERT_API_MODULE_IDS(p_upload_id IN NUMBER)
976 IS
977
978 CURSOR csr_apis IS
979 SELECT api.api_module_id, des2.upload_header_id
980 FROM hr_du_descriptors des1,
981 hr_du_descriptors des2,
982 hr_du_descriptors des3,
983 hr_api_modules api
984 WHERE des1.upload_id = p_upload_id
985 and upper(des1.descriptor) = 'API'
986 and des1.descriptor_type = 'D'
987 and des1.value IS NOT null
988 and upper(des2.descriptor) = 'PROCESS ORDER'
989 and des2.descriptor_type = 'D'
990 and upper(des3.descriptor) = 'REFERENCING'
991 and des3.value = 'PC'
992 and des1.upload_header_id = des2.upload_header_id
993 and des1.upload_header_id = des3.upload_header_id
994 and upper(des1.value) = api.module_name
995 ORDER BY des2.value;
996
997
998 --cursor checks to see if there are any files to be processed
999 --meaning there maybe CP files.
1000 CURSOR csr_any_files IS
1001 SELECT value
1002 FROM hr_du_descriptors
1003 WHERE upload_id = p_upload_id
1004 and upper(descriptor) = 'API'
1005 and descriptor_type = 'D';
1006
1007
1008 e_fatal_error EXCEPTION;
1009 l_fatal_error_message VARCHAR2(2000);
1010 l_api_module_id NUMBER;
1011 l_upload_header_id NUMBER;
1012 l_counter NUMBER :=1;
1013 l_temp_varchar VARCHAR2(2000);
1014
1015 BEGIN
1016 --
1017 hr_du_utility.message('ROUT',
1018 'entry:hr_du_dp_pc_conversion.insert_api_module_ids', 5);
1019 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')' , 10);
1020 --
1021
1022 --sets the delimiter through out the whole package to be a comma
1023 hr_du_di_insert.g_current_delimiter := ',';
1024
1025 OPEN csr_apis;
1026 LOOP
1027 --
1028 FETCH CSR_APIS INTO l_api_module_id, l_upload_header_id;
1029 EXIT WHEN CSR_APIS%NOTFOUND;
1030 g_insert_table(l_counter).r_api_id := l_api_module_id;
1031 create_insert_string(l_api_module_id, l_upload_header_id, l_counter);
1032 l_counter := l_counter + 1;
1033 --
1034 END LOOP;
1035
1036 IF l_counter = 1 THEN
1037 OPEN csr_any_files;
1038 FETCH CSR_ANY_FILES INTO l_temp_varchar;
1039 IF CSR_ANY_FILES%NOTFOUND THEN
1040 l_fatal_error_message := 'No Data found with the upload_id provided ' ||
1041 '( p_upload_id : ' || p_upload_id || ' )';
1042 RAISE e_fatal_error;
1043 END IF;
1044 CLOSE csr_any_files;
1045 ELSE
1046 --Call to procedure to begin the first stages of the conversion
1047 SWITCH_REFERENCING_INITIAL(p_upload_id);
1048 END IF;
1049
1050 --
1051 hr_du_utility.message('ROUT',
1052 'exit:hr_du_dp_pc_conversion.insert_api_module_ids', 15);
1053 --
1054
1055 EXCEPTION
1056 WHEN e_fatal_error THEN
1057 hr_du_utility.error(SQLCODE,'hr_du_dp_pc_conversion.insert_api_module_ids'
1058 ,l_fatal_error_message, 'R');
1059 RAISE;
1060 WHEN OTHERS THEN
1061 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.insert_api_module_ids'
1062 ,'(none)', 'R');
1063 RAISE;
1064 --
1065 END INSERT_API_MODULE_IDS;
1066
1067
1068 -- ------------------------ CREATE_INSERT_STRING ----------------------------
1069 -- Description: The columns R_NONE_REF_PVAL, R_REF_PVAL,
1070 -- R_ID_CURVAL, R_REF_COL_NAMES and R_REF_COL_APIS in the PL/SQL table
1071 -- are all populated for each particular api.
1072 --
1073 -- Input Parameters
1074 --
1075 -- p_api_module_id - Identifies the API
1076 --
1077 -- p_upload_header_id - Identifies the correct upload record
1078 --
1079 -- p_array_pos - The position within the global table
1080 --
1081 -- ------------------------------------------------------------------------
1082 PROCEDURE CREATE_INSERT_STRING(p_api_module_id IN NUMBER,
1083 p_upload_header_id IN NUMBER,
1084 p_array_pos IN NUMBER)
1085 IS
1086
1087 e_fatal_error EXCEPTION;
1088 l_fatal_error_message VARCHAR2(2000);
1089 --holds the PVAL*** of the none refencing columns
1090 l_pval_string VARCHAR2(32767) := null;
1091 --holds the PVAL*** of the refencing columns
1092 l_pval_reference VARCHAR2(32767) := null;
1093 --holds the string representation of the column names
1094 l_reference_string VARCHAR2(32767) := null;
1095 l_current_pval VARCHAR2(10);
1096 l_line_id NUMBER;
1097 l_di_line_number NUMBER;
1098 l_pval_field VARCHAR2(50);
1099 l_mapped_name VARCHAR2(50);
1100 l_length NUMBER;
1101 l_string_length NUMBER;
1102 l_api_name VARCHAR2(20);
1103 l_id_currval NUMBER := null;
1104 l_api_module_id NUMBER;
1105 l_api_module_id_string VARCHAR2(200) := null;
1106 l_spaces BOOLEAN := FALSE;
1107 l_spreadsheet_cell VARCHAR2(10);
1108
1109 --Returns the upload_line_id from HR_DU_UPLOAD_LINES where the header id,
1110 --and the LINE_TYPE match i.e. the column headers 'C'.
1111 CURSOR csr_line_id IS
1112 SELECT UPLOAD_LINE_ID
1113 FROM hr_du_upload_lines
1114 WHERE upload_header_id = p_upload_header_id
1115 AND LINE_TYPE = 'C';
1116
1117 CURSOR csr_DI_LINE_NUMBER IS
1118 SELECT DI_LINE_NUMBER
1119 FROM hr_du_upload_lines
1120 WHERE upload_line_id = l_line_id;
1121
1122 CURSOR csr_api_file IS
1123 SELECT des.value
1124 FROM hr_api_modules api,
1125 hr_du_descriptors des,
1126 hr_du_upload_headers head
1127 WHERE api.api_module_id = p_api_module_id
1128 AND head.upload_header_id = p_upload_header_id
1129 AND head.upload_id = des.upload_id
1130 AND upper(api.module_name) = upper(des.descriptor);
1131
1132 BEGIN
1133 --
1134 hr_du_utility.message('ROUT',
1135 'entry:hr_du_dp_pc_conversion.create_insert_string', 5);
1136 hr_du_utility.message('PARA', '(p_api_module_id - ' || p_api_module_id ||
1137 ')(p_upload_header_id - ' || p_upload_header_id ||
1138 ')(p_array_pos - ' || p_array_pos || ')' ,
1139 10);
1140 --
1141 OPEN csr_line_id;
1142 FETCH csr_line_id INTO l_line_id;
1143 IF csr_line_id%NOTFOUND THEN
1144 l_fatal_error_message := 'No appropriate column title row exists in '||
1145 'the HR_DU_UPLOAD_LINES for the api passed';
1146 RAISE e_fatal_error;
1147 END IF;
1148 CLOSE csr_line_id;
1149
1150 --change the status of the PC row column heading to reflect that processing
1151 --on that line has started.
1152 UPDATE hr_du_upload_lines
1153 SET status = 'S'
1154 WHERE upload_line_id = l_line_id;
1155
1156 COMMIT;
1157
1158 --CACHE COLUMN HEADINGS
1159 STORE_COLUMN_HEADINGS(l_line_id);
1160
1161 --CACHE MAPPED_TO_NAMES
1162 STORE_COLUMN_MAPPINGS (p_api_module_id);
1163
1164 --Called to handle all of the data associated with the fields
1165 --R_STRING_APIS, R_API_PVALS, R_GENERIC_PVAL in the PL/SQL table
1166 CP_REFERENCING_COLUMNS(p_array_pos);
1167
1168 l_pval_string := null;
1169 l_pval_reference := null;
1170
1171 --loops around all the columns within the upload_line
1172 FOR i IN 1..230 LOOP
1173 --
1174 l_current_pval := LPAD(i,3,'0');
1175 l_current_pval := 'PVAL' || l_current_pval;
1176 --returns the value at the specified field
1177 l_pval_field := g_column_headings(i);
1178 --
1179 REMOVE_SPACES (l_pval_field, l_spaces);
1180 IF l_spaces = TRUE THEN
1181 hr_du_utility.message('INFO', 'l_pval_field (with spaces removed) : '
1182 || l_pval_field , 20);
1183 END IF;
1184
1185 --checks to see if the l_pval_field is a ('D') datapump column, if it
1186 --isn't null will be returned
1187 l_mapped_name := general_referencing_column_2(l_pval_field, 'D');
1188 IF l_mapped_name IS NOT NULL THEN
1189 --builds up string of PVAL*** for the datapump column headings
1190 l_pval_string := l_pval_string || l_current_pval || ',';
1191 ELSE
1192 --catches all referencing columns ('R')
1193 l_mapped_name := general_referencing_column_2(l_pval_field, 'R');
1194 IF l_mapped_name IS NOT NULL THEN
1195 VERIFY_API_ATTACHED(l_mapped_name, p_upload_header_id,
1196 p_api_module_id);
1197 l_reference_string := l_reference_string || l_mapped_name || ',';
1198 l_pval_reference := l_pval_reference || l_current_pval || ',';
1199 l_api_module_id := return_parent_api_module_id(p_api_module_id,
1200 l_mapped_name);
1201 --amends the api_module id to the string which the current api will
1202 --reference
1203 l_api_module_id_string := l_api_module_id_string || l_api_module_id
1204 || ',';
1205 --check to see if the end of the row has been reached null
1206 --presumes the end of the row
1207
1208 --this statement looks for ID columns, assumption only one column
1209 ELSIF l_pval_field = 'ID' THEN
1210 NULL;
1211 ELSIF l_pval_field IS NULL THEN
1212 exit;
1213 ELSE
1214 --deals with column names that don't match any data within
1215 --HR_DU_COLUMN_MAPPINGS
1216 --the line number of the HR_DU_UPLOAD_LINE relating to the position
1217 --it was in within the spreadsheet is extracted
1218 OPEN csr_DI_LINE_NUMBER;
1219 FETCH csr_DI_LINE_NUMBER INTO l_di_line_number;
1220 CLOSE csr_DI_LINE_NUMBER;
1221
1222 --cursor returns the file name to display to the user the file
1223 --where the error has occured
1224 OPEN csr_api_file;
1225 FETCH csr_api_file INTO l_api_name;
1226 CLOSE csr_api_file;
1227
1228 l_spreadsheet_cell := hr_du_utility.Return_Spreadsheet_row(i);
1229 l_fatal_error_message := 'In ' || l_api_name ||
1230 ' there is an invalid column name within cell ' ||
1231 l_spreadsheet_cell || l_di_line_number;
1232 RAISE e_fatal_error;
1233 END IF;
1234 END IF;
1235 --
1236 END LOOP;
1237
1238 --removes the last ',' at the end of the string
1239 l_length := LENGTHB(',');
1240 l_string_length := LENGTHB(l_pval_string);
1241 l_pval_string := SUBSTRB(l_pval_string,1, (l_string_length - l_length));
1242
1243 --check to see if any value is within the referencing strings
1244 l_string_length := LENGTHB(l_pval_reference);
1245 IF l_string_length > 0 THEN
1246 l_pval_reference := SUBSTRB(l_pval_reference,1,
1247 (l_string_length - l_length));
1248 l_string_length := LENGTHB(l_reference_string);
1249 l_reference_string := SUBSTRB(l_reference_string,1,
1250 (l_string_length - l_length));
1251 l_string_length := LENGTHB(l_api_module_id_string);
1252 l_api_module_id_string := SUBSTRB(l_api_module_id_string,1,
1253 (l_string_length - l_length));
1254 END IF;
1255
1256 --works out the maximum value in the ID column of the header
1257 l_id_currval := MAX_ID_VALUE(l_line_id);
1258
1259 --insert the values into the appropriate table
1260 g_insert_table(p_array_pos).r_none_ref_PVAL := l_pval_string;
1261 g_insert_table(p_array_pos).r_ref_PVAL := l_pval_reference;
1262 g_insert_table(p_array_pos).r_id_curval := l_id_currval;
1263 g_insert_table(p_array_pos).r_ref_Col_Names := l_reference_string;
1264 g_insert_table(p_array_pos).r_ref_Col_apis := l_api_module_id_string;
1265
1266 hr_du_utility.message('INFO', l_pval_string, 15);
1267 hr_du_utility.message('INFO', l_pval_reference, 20);
1268 hr_du_utility.message('INFO', l_id_currval, 30);
1269 hr_du_utility.message('INFO', l_reference_string, 35);
1270 hr_du_utility.message('INFO', l_api_module_id_string, 45);
1271
1272
1273 --change the status of the PC row column heading to show that
1274 --I've completed
1275 UPDATE hr_du_upload_lines
1276 SET status = 'C'
1277 WHERE upload_line_id = l_line_id;
1278
1279 --
1280 hr_du_utility.message('ROUT',
1281 'exit:hr_du_dp_pc_conversion.create_insert_string', 50);
1282 --
1283
1284 EXCEPTION
1285 WHEN e_fatal_error THEN
1286 hr_du_utility.error(SQLCODE,'hr_du_dp_pc_conversion.create_insert_string'
1287 ,l_fatal_error_message, 'R');
1288 RAISE;
1289 WHEN OTHERS THEN
1290 hr_du_utility.error(SQLCODE,'hr_du_dp_pc_conversion.create_insert_string'
1291 ,'(none)', 'R');
1292 RAISE;
1293 --
1294 END CREATE_INSERT_STRING;
1295
1296
1297 -- ------------------------- SWITCH_REFERENCING_INITIAL -------------------
1298 -- Description: This is the first stage of the actual reference change. The
1299 -- procedure selects one line from the highest process_ordered upload_line
1300 -- and passes this to PROCESS_LINE.
1301 --
1302 -- Input Parameters
1303 --
1304 -- p_upload_id - Identify the correct HR_DU_UPLOADS
1305 --
1306 -- ------------------------------------------------------------------------
1307 PROCEDURE SWITCH_REFERENCING_INITIAL(p_upload_id IN NUMBER)
1308 IS
1309
1310 e_fatal_error EXCEPTION;
1311 l_fatal_error_message VARCHAR2(2000);
1312 l_upload_line_id NUMBER;
1313 l_upload_header_id NUMBER;
1314 l_api_module_id NUMBER;
1315 l_row_id NUMBER;
1316 l_table_size NUMBER;
1317 l_file_name VARCHAR2(30);
1318 l_temp_upload_header NUMBER;
1319 l_temp_api_module_id NUMBER;
1320 l_counter NUMBER :=1;
1321 l_size NUMBER :=0;
1322
1323
1324 CURSOR csr_starting_point IS
1325 SELECT desc1.UPLOAD_HEADER_ID
1326 FROM hr_du_descriptors desc1,
1327 hr_du_descriptors desc2,
1328 hr_du_descriptors desc3
1329 WHERE upper(desc1.DESCRIPTOR) = 'STARTING POINT'
1330 AND upper(desc1.VALUE) = 'YES'
1331 AND desc1.upload_id = p_upload_id
1332 AND upper(desc2.DESCRIPTOR) = 'REFERENCING'
1333 AND upper(desc2.VALUE) = 'PC'
1334 AND upper(desc3.DESCRIPTOR) = 'API'
1335 AND upper(desc3.VALUE) IS NOT NULL
1336 AND desc2.upload_header_id = desc1.upload_header_id
1337 AND desc3.upload_header_id = desc1.upload_header_id;
1338
1339 CURSOR csr_header_to_api IS
1340 SELECT api_module_id
1341 FROM hr_du_upload_headers
1342 WHERE upload_header_id = l_temp_upload_header;
1343
1344
1345 CURSOR csr_field_value IS
1346 SELECT to_number(PVAL001)
1347 FROM hr_du_upload_lines
1348 WHERE upload_line_id = l_upload_line_id;
1349
1350 BEGIN
1351 --
1352 hr_du_utility.message('ROUT',
1353 'entry:hr_du_dp_pc_conversion.switch_referencing_initial', 5);
1354 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')' , 10);
1355 --
1356
1357 --call the procedure to convert all api_module id's held within
1358 --R_REF_COL_APIS into table_id that reference other positions
1359 --within the PL/SQl table.
1360 api_module_id_to_table_id;
1361
1362 l_table_size := g_insert_table.count;
1363
1364 --this cursor populates g_start_table with all the starting point
1365 --upload_header_ids
1366 OPEN csr_starting_point;
1367 --
1368 LOOP
1369 FETCH csr_starting_point INTO l_temp_upload_header;
1370 IF csr_starting_point%NOTFOUND THEN
1371 IF l_Counter = 1 THEN
1372 l_fatal_error_message := 'No Starting point included on any of ' ||
1373 'the PC files';
1374 RAISE e_fatal_error;
1375 ELSE
1376 EXIT;
1377 END IF;
1378 ELSE
1379 g_start_table(l_counter) := l_temp_upload_header;
1380 l_counter := l_counter + 1;
1381 END IF;
1382 END LOOP;
1383
1384 --
1385 CLOSE csr_starting_point;
1386 --
1387 --the outer loop deals with lines that have not been handled after the first
1388 --pass of the highest ordered api_modules, meaning those with the starting
1389 --points. Meaning any line not referenced by will be passed in this way.
1390
1391 FOR i IN 1..l_counter LOOP
1392 --
1393 OPEN csr_header_to_api;
1394 --
1395 FETCH csr_header_to_api INTO l_temp_api_module_id;
1396 IF csr_header_to_api%NOTFOUND THEN
1397 l_fatal_error_message := 'Unable to match header id to api module id';
1398 RAISE e_fatal_error;
1399 ELSE
1400 l_api_module_id := g_insert_table(i).r_api_id;
1401 IF (l_size = 0) and (l_api_module_id <> l_temp_api_module_id) THEN
1402 l_fatal_error_message := 'The highest Process order with a ' ||
1403 'referencing type of PC is not flagged as a starting point. ' ||
1404 'Also make sure all processing order IDs are unique.';
1405 RAISE e_fatal_error;
1406 ELSE
1407 l_size := l_size + 1;
1408 l_api_module_id := l_temp_api_module_id;
1409 LOOP
1410 BEGIN
1411 --
1412 SELECT line.upload_line_id, line.upload_header_id
1413 INTO l_upload_line_id, l_upload_header_id
1414 FROM hr_du_upload_lines line,
1415 hr_du_upload_headers head
1416 WHERE head.upload_id = p_upload_id
1417 AND head.api_module_id = l_api_module_id
1418 AND line.upload_header_id = head.upload_header_id
1419 AND line.status = 'NS'
1420 AND line.reference_type = 'PC'
1421 AND line.line_type = 'D'
1422 AND rownum < 2; --only gets one row
1423 EXCEPTION
1424 WHEN no_data_found THEN
1425 EXIT;
1426 --
1427 END;
1428
1429 --Statement extracts the ID number for the particular
1430 --upload_line_id that was passed in.
1431
1432 OPEN csr_field_value;
1433 --
1434 FETCH csr_field_value INTO l_row_id;
1435 IF csr_field_value%NOTFOUND THEN
1436 l_fatal_error_message := 'Unable to retrieve PVAL001 value';
1437 RAISE e_fatal_error;
1438 END IF;
1439 --
1440 CLOSE csr_field_value;
1441
1442 hr_du_utility.message('INFO','ID value of the upload_line ' ||
1443 l_row_id, 15);
1444 hr_du_utility.message('INFO','api_module ID associated with ' ||
1445 'the ID value ' || 1, 20);
1446
1447 --recursive function PROCESS_LINE is initiated on the chosen
1448 --row_id
1449 PROCESS_LINE(null, null, l_row_id, i, l_upload_header_id,
1450 p_upload_id);
1451 END LOOP;
1452 --
1453 END IF;
1454 END IF;
1455 CLOSE csr_header_to_api;
1456 END LOOP;
1457 --
1458
1459 --checks for any lines that haven't been processed
1460 FOR j IN 1..l_table_size LOOP
1461 --
1462 BEGIN
1463 l_api_module_id := g_insert_table(j).r_api_id;
1464
1465 l_upload_line_id := NULL;
1466 l_upload_header_id := NULL;
1467
1468 SELECT line.upload_line_id, line.upload_header_id
1469 INTO l_upload_line_id, l_upload_header_id
1470 FROM hr_du_upload_lines line,
1471 hr_du_upload_headers head
1472 WHERE head.upload_id = p_upload_id
1473 AND head.api_module_id = l_api_module_id
1474 AND line.upload_header_id = head.upload_header_id
1475 AND line.status = 'NS'
1476 AND line.reference_type = 'PC'
1477 AND line.line_type = 'D'
1478 AND rownum < 2; --only gets one row
1479 EXCEPTION
1480 WHEN no_data_found THEN
1481 null;
1482 END;
1483
1484 IF l_upload_line_id IS NOT NULL THEN
1485 l_file_name := hr_du_rules.RETURN_UPLOAD_HEADER_FILE(l_upload_header_id);
1486 OPEN csr_field_value;
1487 --
1488 FETCH csr_field_value INTO l_row_id;
1489 IF csr_field_value%NOTFOUND THEN
1490 l_fatal_error_message := ' ID ' || l_row_id || ' in ' ||
1491 l_file_name || ' has not been referenced.';
1492 RAISE e_fatal_error;
1493 END IF;
1494 --
1495 CLOSE csr_field_value;
1496 --
1497 END IF;
1498 --
1499 END LOOP;
1500
1501 --
1502 hr_du_utility.message('ROUT',
1503 'exit:hr_du_dp_pc_conversion.switch_referencing_initial', 15);
1504 --
1505
1506 EXCEPTION
1507 WHEN e_fatal_error THEN
1508 hr_du_utility.error(SQLCODE,
1509 'hr_du_dp_pc_conversion.switch_referencing_initial'
1510 ,l_fatal_error_message, 'R');
1511 RAISE;
1512 WHEN OTHERS THEN
1513 hr_du_utility.error(SQLCODE,
1514 'hr_du_dp_pc_conversion.switch_referencing_initial','(none)', 'R');
1515 RAISE;
1516 --
1517 END SWITCH_REFERENCING_INITIAL;
1518
1519
1520 -- ------------------------- PROCESS_LINE -----------------------------------
1521 -- Description: Each line passed here is duplicated and changed accordingly
1522 -- to CP referencing, any dependants on that line are in turn passed into
1523 -- this procedure to be processed.
1524 --
1525 -- Input Parameters
1526 --
1527 -- p_prev_upload_line_id - The name of the previous line ID which called
1528 -- this function.
1529 -- p_prev_table_number - The number of the previous array position which
1530 -- holds all of the relevant data for the
1531 -- conversion
1532 -- p_target_ID - The ID line that you are going to be working on
1533 -- (not the HR_DU_UPLOAD_LINE.id)
1534 -- p_target_api_module - The number of the current array position
1535 -- that holds all of the relevant data for the
1536 -- conversion
1537 -- p_upload_header_id - maintains a solid link to the
1538 -- HR_DU_UPLOADS table of the target line, so not
1539 -- just the ID values are relied on.
1540 -- p_upload_id - Link to HR_DU_UPLOADS
1541 -- ------------------------------------------------------------------------
1542 PROCEDURE PROCESS_LINE(p_prev_upload_line_id IN NUMBER, p_prev_table_number
1543 IN NUMBER, p_target_ID IN NUMBER, p_target_api_module in NUMBER,
1544 p_upload_header_id IN NUMBER, p_upload_id IN NUMBER) IS
1545
1546 e_fatal_error EXCEPTION;
1547 l_fatal_error_message VARCHAR2(2000);
1548 --id of the PC line passed in
1549 l_upload_line_id NUMBER;
1550 l_number_references NUMBER;
1551 l_built_up_string1 VARCHAR2(32767);
1552 l_built_up_string2 VARCHAR2(20);
1553 l_built_up_string3 VARCHAR2(32767);
1554 l_date_string VARCHAR2(50);
1555 --newly created CP line id
1556 l_line_id NUMBER;
1557 l_reference_pval VARCHAR2(50);
1558 l_cell_value VARCHAR2(200);
1559 l_target_api_module NUMBER;
1560 l_upload_header_id NUMBER;
1561 l_single_api_module VARCHAR2(10);
1562 l_single_pval VARCHAR2(10);
1563 l_generic_pval VARCHAR2(10);
1564 l_found_flag BOOLEAN :=FALSE;
1565 l_spaces BOOLEAN :=FALSE;
1566 l_cursor_handle INT;
1567 l_rows_processed INT;
1568 l_table_size NUMBER;
1569 l_temp_header_id NUMBER;
1570 l_original_upload_header_id NUMBER;
1571
1572
1573 CURSOR csr_line_id IS
1574 SELECT UPLOAD_LINE_ID
1575 FROM hr_du_upload_lines
1576 WHERE upload_header_id = p_upload_header_id
1577 AND PVAL001 = to_char(p_target_ID)
1578 AND REFERENCE_TYPE = 'PC';
1579
1580
1581
1582 BEGIN
1583 --
1584 hr_du_utility.message('ROUT','entry:hr_du_dp_pc_conversion.process_line',
1585 5);
1586 hr_du_utility.message('PARA', '(p_prev_upload_line_id - ' ||
1587 p_prev_upload_line_id ||
1588 ')(p_prev_table_number - ' || p_prev_table_number ||
1589 ')(p_target_ID - ' || p_target_ID ||
1590 ')(p_upload_header_id - ' || p_upload_header_id ||
1591 ')(p_target_api_module - ' || p_target_api_module || ')' ,
1592 10);
1593 --
1594
1595 --Finds out if a starting point points to another looks through all headers with a starting point
1596 IF (p_prev_upload_line_id IS NOT NULL) AND
1597 (p_prev_table_number IS NOT NULL) THEN
1598 l_table_size := g_start_table.count;
1599
1600 FOR i IN 1..l_table_size LOOP
1601 --
1602 l_temp_header_id := g_start_table(i);
1603 IF l_temp_header_id = p_upload_header_id THEN
1604 l_fatal_error_message := 'Starting point is unable to reference '||
1605 'other starting point';
1606 RAISE e_fatal_error;
1607 END IF;
1608 --
1609 END LOOP;
1610 END IF;
1611
1612 BEGIN
1613
1614 OPEN csr_line_id;
1615 --
1616 FETCH csr_line_id INTO l_upload_line_id;
1617 IF csr_line_id%NOTFOUND THEN
1618 l_fatal_error_message := ' Unable to fine ID ' || p_target_ID ||
1619 ' in API ' || g_insert_table(p_target_api_module).r_api_id ||
1620 '. Referencing column in other file has this invalid reference';
1621 RAISE e_fatal_error;
1622 END IF;
1623 --
1624 CLOSE csr_line_id;
1625
1626 EXCEPTION
1627 WHEN OTHERS THEN
1628 l_fatal_error_message := 'Error has occured searching for the ' ||
1629 'upload_line_id associated with the upload_header_id of ' ||
1630 p_upload_header_id || ' and ID column number of ' ||
1631 p_target_ID;
1632 RAISE e_fatal_error;
1633 END;
1634
1635 hr_du_utility.message('INFO', 'l_upload_line_id : ' || l_upload_line_id, 15);
1636
1637 --change the status of the PC row to show we're processing this
1638 UPDATE hr_du_upload_lines
1639 SET status = 'S'
1640 WHERE upload_line_id = l_upload_line_id;
1641
1642 Select HR_DU_UPLOAD_LINES_S.nextval
1643 INTO l_line_id
1644 FROM dual;
1645
1646 Select ORIGINAL_UPLOAD_HEADER_ID
1647 INTO l_original_upload_header_id
1648 FROM hr_du_upload_lines
1649 WHERE upload_line_id = l_upload_line_id;
1650
1651 --String being built up simply creates a new UPLOAD_LINE with all of the
1652 --none referencing columns' data being copied across
1653 l_built_up_string1 := 'INSERT INTO hr_du_upload_lines( ' ||
1654 ' UPLOAD_LINE_ID, UPLOAD_HEADER_ID, BATCH_LINE_ID, ' ||
1655 ' STATUS, REFERENCE_TYPE, LINE_TYPE, LAST_UPDATE_DATE, ' ||
1656 ' LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, ' ||
1657 ' ORIGINAL_UPLOAD_HEADER_ID, PVAL001,' ||
1658 g_insert_table(p_target_api_module).r_none_ref_PVAL || ' ) SELECT '
1659 || l_line_id || ',' ||
1660 p_upload_header_id || ',' ||
1661 'null,' ||
1662 '''S'',' ||
1663 '''CP'',' ||
1664 '''D'',';
1665 l_built_up_string2 := ', 1, 1, 1,' ;
1666 l_built_up_string3 := ' ,' || l_original_upload_header_id || ' ,' ||
1667 ( g_insert_table(p_target_api_module).r_id_curval + 1) ||
1668 ',' || g_insert_table(p_target_api_module).r_none_ref_PVAL ||
1669 ' FROM hr_du_upload_lines ' ||
1670 ' WHERE upload_line_id = ' || l_upload_line_id;
1671
1672 --grab the sysdate and formate it to the correct style to be executed later
1673 SELECT ' to_date(''' || to_char(sysdate, 'YYYY/MM/DD') ||
1674 ''' , ''YYYY/MM/DD'' ) '
1675 INTO l_date_string
1676 FROM dual;
1677
1678 --Increment the maximum current value R_ID_CURVAL for the api_module stored
1679 --in the PL/SQL table
1680 g_insert_table(p_target_api_module).r_id_curval :=
1681 g_insert_table(p_target_api_module).r_id_curval + 1;
1682
1683 hr_du_utility.message('INFO', l_built_up_string1 || l_date_string ||
1684 l_built_up_string2 || l_date_string || l_built_up_string3, 20);
1685
1686 hr_du_utility.dynamic_sql(l_built_up_string1 || l_date_string ||
1687 l_built_up_string2 || l_date_string || l_built_up_string3);
1688
1689 --Next step is to see if there are any referencing columns
1690 --associated with the api_module
1691
1692 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters
1693 (g_insert_table(p_target_api_module).r_ref_Col_apis);
1694
1695 l_number_references :=
1696 hr_du_di_insert.WORDS_ON_LINE(g_insert_table
1697 (p_target_api_module).r_ref_Col_apis);
1698
1699 --if l_number_references is zero then it won't enter the loop
1700 FOR i IN 1..l_number_references LOOP
1701 --
1702 --Find the appropriate column in the old PC row where the referencing
1703 --details are held
1704 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
1705 g_insert_table(p_target_api_module).r_ref_PVAL);
1706
1707 l_reference_pval := hr_du_di_insert.Return_Word(
1708 g_insert_table(p_target_api_module).r_ref_PVAL, i);
1709
1710 hr_du_utility.message('INFO', 'l_reference_pval : '
1711 || l_reference_pval, 25);
1712
1713 --identify the target row in the PL/SQL table that holds all of the
1714 --information on that particular api_module
1715 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
1716 g_insert_table(p_target_api_module).r_ref_Col_apis);
1717
1718 l_target_api_module := hr_du_di_insert.Return_Word(
1719 g_insert_table(p_target_api_module).r_ref_Col_apis, i);
1720
1721 hr_du_utility.message('INFO', 'l_target_api_module : '
1722 || l_target_api_module, 30);
1723
1724 --Target the value of the cell with the appropriate PVAL*** supplied
1725 --by l_reference_pval
1726 l_cell_value := RETURN_FIELD_VALUE ('HR_DU_UPLOAD_LINES',
1727 l_upload_line_id, 'upload_line_id', l_reference_pval);
1728
1729 --removes leading and trailing spaces from the reference number
1730 REMOVE_SPACES (l_cell_value, l_spaces);
1731 IF l_spaces = TRUE THEN
1732 hr_du_utility.message('INFO', 'l_cell_value (with spaces removed) : '
1733 || l_cell_value , 20);
1734 END IF;
1735 --
1736
1737 hr_du_utility.message('INFO', 'l_cell_value : ' || l_cell_value, 35);
1738
1739 IF l_cell_value IS NOT NULL THEN
1740 --
1741 --To find out the upload_header_id I extract the upload_id and then
1742 --search for a header with the appropriate target api_module and the
1743 --correct upload_id
1744
1745 -- *************************************************************************************************
1746 -- *************************************************************************************************
1747
1748
1749 l_built_up_string1 := 'SELECT head.upload_header_id '||
1750 'FROM hr_du_upload_headers head, ' ||
1751 'hr_du_descriptors des '||
1752 'WHERE head.upload_id = ' || p_upload_id ||
1753 ' AND head.api_module_id = ' ||
1754 g_insert_table(l_target_api_module).r_api_id ||
1755 'AND des.upload_header_id = head.upload_header_id ' ||
1756 'AND des.descriptor = ''API'' ' ||
1757 'AND des.value IS NOT NULL';
1758
1759 hr_du_utility.message('INFO', l_built_up_string1, 35);
1760
1761 hr_du_utility.dynamic_sql_num(l_built_up_string1, l_upload_header_id);
1762
1763 hr_du_utility.message('INFO',
1764 'Entering call to Process Line.' , 40);
1765
1766 PROCESS_LINE(g_insert_table(p_target_api_module).r_id_curval,
1767 p_target_api_module, l_cell_value, l_target_api_module,
1768 l_upload_header_id, p_upload_id);
1769
1770 hr_du_utility.message('INFO',
1771 'Returned from call to Process Line.' || l_cell_value, 45);
1772 --
1773 END IF;
1774 --
1775 END LOOP;
1776
1777 --check to see whether I have to add information to the current line
1778 --to hold details about the calling line
1779 IF p_prev_upload_line_id IS NOT NULL THEN
1780
1781 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
1782 g_insert_table(p_target_api_module).r_string_apis);
1783 l_number_references := hr_du_di_insert.WORDS_ON_LINE(
1784 g_insert_table(p_target_api_module).r_string_apis);
1785
1786 hr_du_utility.message('INFO', 'r_string_apis : ' ||
1787 g_insert_table(p_target_api_module).r_string_apis ,50);
1788 hr_du_utility.message('INFO', l_number_references ,55);
1789
1790 --this loop catches all occurrences
1791 FOR j IN 1..l_number_references LOOP
1792 --
1793 --Extracts the first api_module id from R_STRING_APIS
1794 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
1795 g_insert_table(p_target_api_module).r_string_apis);
1796 l_single_api_module := hr_du_di_insert.Return_Word(
1797 g_insert_table(p_target_api_module).r_string_apis, j);
1798
1799 --checks for a match with api_module id's in R_STRING_APIS
1800 --and the old api_module id from the calling line (p_prev_table_number)
1801 IF l_single_api_module =
1802 g_insert_table(p_prev_table_number).r_api_id THEN
1803 --
1804 --Recalls the pval*** in the PL/SQL table so the data can be stored
1805 --in the correct position in the UPLOAD_LINE
1806 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
1807 g_insert_table(p_target_api_module).R_API_PVALS);
1808 l_single_pval := hr_du_di_insert.Return_Word(
1809 g_insert_table(p_target_api_module).R_API_PVALS, j);
1810
1811 --modifies the line with the extra CP referencing information
1812 l_built_up_string1 := 'update HR_DU_UPLOAD_LINES SET ' ||
1813 l_single_pval || ' = ''' || p_prev_upload_line_id ||
1814 ''' WHERE upload_line_id = ' || l_line_id;
1815
1816 hr_du_utility.message('INFO', l_built_up_string1 ,50);
1817
1818 hr_du_utility.dynamic_sql(l_built_up_string1);
1819
1820 --Set to true for api_module id matches has precedence over generic
1821 --matches. (this is handled in the next loop)
1822 l_found_flag := TRUE;
1823 EXIT;
1824 --
1825 END IF;
1826 END LOOP;
1827
1828 --Searching for generic matches, if you find a null it just takes the
1829 --first one it comes across
1830 IF l_found_flag = FALSE THEN
1831 FOR j IN 1..l_number_references LOOP
1832 --
1833 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
1834 g_insert_table(p_target_api_module).r_string_apis);
1835 l_single_api_module := hr_du_di_insert.Return_Word(
1836 g_insert_table(p_target_api_module).r_string_apis, j);
1837
1838 IF l_single_api_module IS NULL THEN
1839 --
1840 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
1841 g_insert_table(p_target_api_module).R_API_PVALS);
1842 l_single_pval := hr_du_di_insert.Return_Word(
1843 g_insert_table(p_target_api_module).R_API_PVALS, j);
1844
1845 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
1846 g_insert_table(p_target_api_module).r_generic_pval);
1847 l_generic_pval := hr_du_di_insert.Return_Word(
1848 g_insert_table(p_target_api_module).r_generic_pval, j);
1849
1850 l_built_up_string3 := 'update HR_DU_UPLOAD_LINES SET ' ||
1851 l_single_pval || ' = ''' || p_prev_upload_line_id
1852 || ''',' || l_generic_pval || ' = ''' ||
1853 g_insert_table(p_prev_table_number).r_api_id ||
1854 ''' WHERE upload_line_id = ' || l_line_id;
1855
1856 hr_du_utility.message('INFO', l_built_up_string3 ,55);
1857
1858 hr_du_utility.dynamic_sql(l_built_up_string3);
1859
1860 EXIT;
1861 --
1862 ELSE
1863 null;
1864 END IF;
1865 --
1866 END LOOP;
1867 END IF;
1868 END IF;
1869
1870 --change the status of the PC row to show we've completed the process
1871 UPDATE hr_du_upload_lines
1872 SET status = 'C'
1873 WHERE upload_line_id = l_upload_line_id;
1874
1875 --change the status of the CP row to show we've completed the process
1876 UPDATE hr_du_upload_lines
1877 SET status = 'C'
1878 WHERE upload_line_id = l_line_id;
1879
1880 COMMIT;
1881
1882 --
1883 hr_du_utility.message('ROUT','exit:hr_du_dp_pc_conversion.process_line', 60);
1884 --
1885
1886 --
1887 EXCEPTION
1888 WHEN e_fatal_error THEN
1889 hr_du_utility.error(SQLCODE,'hr_du_dp_pc_conversion.process_line'
1890 ,l_fatal_error_message, 'R');
1891 RAISE;
1892 WHEN OTHERS THEN
1893 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.process_line',
1894 '(none)', 'R');
1895 RAISE;
1896 --
1897 END PROCESS_LINE;
1898
1899
1900 -- ------------------------- VALIDATE -----------------------------------
1901 -- Description: The validation procedure is a pre check and insures that
1902 -- the module is not being run on tables that are already in the CP
1903 -- format.
1904 --
1905 -- Input Parameters
1906 -- p_upload_id - The upload id to associate the procedure with
1907 -- correct table
1908 --
1909 -- ------------------------------------------------------------------------
1910 PROCEDURE VALIDATE(p_upload_id IN NUMBER) IS
1911
1912 e_fatal_error EXCEPTION;
1913 l_fatal_error_message VARCHAR2(2000);
1914 l_source_reference_type VARCHAR2(30);
1915
1916 BEGIN
1917 --
1918 hr_du_utility.message('ROUT','entry:hr_du_dp_pc_conversion.validate', 5);
1919 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')' , 10);
1920 --
1921
1922 l_source_reference_type := NULL;
1923
1924 --
1925 hr_du_utility.message('ROUT','exit:hr_du_dp_pc_conversion.validate', 15);
1926 --
1927
1928 --
1929 EXCEPTION
1930 WHEN e_fatal_error THEN
1931 hr_du_utility.error(SQLCODE,'hr_du_dp_pc_conversion.validate'
1932 ,l_fatal_error_message, 'R');
1933 RAISE;
1934 WHEN OTHERS THEN
1935 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.validate',
1936 '(none)', 'R');
1937 RAISE;
1938 --
1939 END VALIDATE;
1940
1941
1942 -- ------------------------- ROLLBACK -----------------------------------
1943 -- Description: This procedure is called when an error has occured so that
1944 -- the database tables can be cleaned up to restart the processing module
1945 -- again
1946 --
1947 -- Input Parameters
1948 -- p_upload_id - The upload id to associate the procedure with
1949 -- correct table
1950 --
1951 -- ------------------------------------------------------------------------
1952 PROCEDURE ROLLBACK(p_upload_id IN NUMBER) IS
1953
1954
1955 BEGIN
1956 --
1957 hr_du_utility.message('ROUT','entry:hr_du_dp_pc_conversion.rollback', 5);
1958 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')' , 10);
1959 --
1960
1961
1962 DELETE FROM HR_DU_UPLOAD_LINES
1963 WHERE UPLOAD_HEADER_ID IN (SELECT head.upload_header_id
1964 FROM hr_du_upload_headers head,
1965 hr_du_descriptors des
1966 WHERE head.upload_id = p_upload_id
1967 AND head.upload_header_id = des.upload_header_id
1968 AND upper(des.descriptor) = 'REFERENCING'
1969 AND upper(des.value) = 'PC')
1970 AND REFERENCE_TYPE = 'CP';
1971 COMMIT;
1972
1973 UPDATE hr_du_upload_lines
1974 SET status = 'NS'
1975 WHERE UPLOAD_HEADER_ID IN (SELECT upload_header_id
1976 FROM hr_du_upload_headers
1977 WHERE upload_id = p_upload_id)
1978 AND status <> 'NS';
1979 COMMIT;
1980
1981 --
1982 hr_du_utility.message('ROUT','exit:hr_du_dp_pc_conversion.rollback', 15);
1983 --
1984
1985 --
1986 EXCEPTION
1987 WHEN OTHERS THEN
1988 hr_du_utility.error(SQLCODE, 'hr_du_dp_pc_conversion.rollback',
1989 '(none)', 'R');
1990 RAISE;
1991 --
1992 END ROLLBACK;
1993
1994 END HR_DU_DP_PC_CONVERSION;
[Home] [Help]