[Home] [Help]
PACKAGE BODY: APPS.HR_DU_DO_DATAPUMP
Source
1 PACKAGE BODY HR_DU_DO_DATAPUMP AS
2 /* $Header: perdudp.pkb 115.29 2002/11/28 15:25:47 apholt noship $ */
3
4 -- ------------------------- STORE_COLUMN_HEADINGS ------------------------
5 -- Description: This procedure extracts the column headings for the given
6 -- line and caches them into a table to save on the number of select
7 -- statements used in the code.
8 --
9 -- Input Parameters
10 -- p_line_id - Identifies the UPLOAD_LINE to be used
11 -- ------------------------------------------------------------------------
12 PROCEDURE STORE_COLUMN_HEADINGS (p_line_id IN NUMBER)
13 IS
14
15 BEGIN
16 --
17 hr_du_utility.message('ROUT','entry:hr_du_do_datapump.
18 store_column_headings', 5);
19 hr_du_utility.message('PARA', '(p_line_id - ' || p_line_id || ')'
20 , 10);
21
22 hr_du_utility.message('INFO','Select Statement Start ' , 15);
23
24 SELECT
25 PVAL001, PVAL002, PVAL003, PVAL004 , PVAL005 , PVAL006,
26 PVAL007, PVAL008, PVAL009, PVAL010 , PVAL011, PVAL012,
27 PVAL013, PVAL014, PVAL015, PVAL016 , PVAL017, PVAL018,
28 PVAL019, PVAL020, PVAL021, PVAL022, PVAL023, PVAL024,
29 PVAL025, PVAL026, PVAL027, PVAL028, PVAL029, PVAL030,
30 PVAL031, PVAL032, PVAL033, PVAL034, PVAL035, PVAL036,
31 PVAL037, PVAL038, PVAL039, PVAL040, PVAL041, PVAL042,
32 PVAL043, PVAL044, PVAL045, PVAL046, PVAL047, PVAL048,
33 PVAL049, PVAL050, PVAL051, PVAL052, PVAL053, PVAL054,
34 PVAL055, PVAL056, PVAL057, PVAL058, PVAL059, PVAL060,
35 PVAL061, PVAL062, PVAL063, PVAL064, PVAL065, PVAL066,
36 PVAL067, PVAL068, PVAL069, PVAL070, PVAL071, PVAL072,
37 PVAL073, PVAL074, PVAL075, PVAL076, PVAL077, PVAL078,
38 PVAL079, PVAL080, PVAL081, PVAL082, PVAL083, PVAL084,
39 PVAL085, PVAL086, PVAL087, PVAL088, PVAL089, PVAL090,
40 PVAL091, PVAL092, PVAL093, PVAL094, PVAL095, PVAL096,
41 PVAL097, PVAL098, PVAL099, PVAL100, PVAL101, PVAL102,
42 PVAL103, PVAL104, PVAL105,PVAL106, PVAL107, PVAL108,
43 PVAL109, PVAL110, PVAL111,PVAL112, PVAL113, PVAL114,
44 PVAL115, PVAL116, PVAL117,PVAL118, PVAL119, PVAL120,
45 PVAL121, PVAL122, PVAL123,PVAL124, PVAL125, PVAL126,
46 PVAL127, PVAL128, PVAL129,PVAL130, PVAL131, PVAL132,
47 PVAL133, PVAL134, PVAL135,PVAL136, PVAL137, PVAL138,
48 PVAL139, PVAL140, PVAL141,PVAL142, PVAL143, PVAL144,
49 PVAL145, PVAL146, PVAL147,PVAL148, PVAL149, PVAL150,
50 PVAL151, PVAL152, PVAL153,PVAL154, PVAL155, PVAL156,
51 PVAL157, PVAL158, PVAL159,PVAL160, PVAL161, PVAL162,
52 PVAL163, PVAL164, PVAL165,PVAL166, PVAL167, PVAL168,
53 PVAL169, PVAL170, PVAL171,PVAL172, PVAL173, PVAL174,
54 PVAL175, PVAL176, PVAL177,PVAL178, PVAL179, PVAL180,
55 PVAL181, PVAL182, PVAL183,PVAL184, PVAL185, PVAL186,
56 PVAL187, PVAL188, PVAL189,PVAL190, PVAL191, PVAL192,
57 PVAL193, PVAL194, PVAL195,PVAL196, PVAL197, PVAL198,
58 PVAL199, PVAL200, PVAL201,PVAL202, PVAL203, PVAL204,
59 PVAL205, PVAL206, PVAL207,PVAL208, PVAL209, PVAL210,
60 PVAL211, PVAL212, PVAL213,PVAL214, PVAL215, PVAL216,
61 PVAL217, PVAL218, PVAL219,PVAL220, PVAL221, PVAL222,
62 PVAL223, PVAL224, PVAL225,PVAL226, PVAL227, PVAL228,
63 PVAL229, PVAL230
64 INTO
65 g_column_headings(1), g_column_headings(2), g_column_headings(3),
66 g_column_headings(4), g_column_headings(5), g_column_headings(6),
67 g_column_headings(7), g_column_headings(8), g_column_headings(9),
68 g_column_headings(10), g_column_headings(11), g_column_headings(12),
69 g_column_headings(13), g_column_headings(14), g_column_headings(15),
70 g_column_headings(16), g_column_headings(17), g_column_headings(18),
71 g_column_headings(19), g_column_headings(20), g_column_headings(21),
72 g_column_headings(22), g_column_headings(23), g_column_headings(24),
73 g_column_headings(25), g_column_headings(26), g_column_headings(27),
74 g_column_headings(28), g_column_headings(29), g_column_headings(30),
75 g_column_headings(31), g_column_headings(32), g_column_headings(33),
76 g_column_headings(34), g_column_headings(35), g_column_headings(36),
77 g_column_headings(37), g_column_headings(38), g_column_headings(39),
78 g_column_headings(40), g_column_headings(41), g_column_headings(42),
79 g_column_headings(43), g_column_headings(44), g_column_headings(45),
80 g_column_headings(46), g_column_headings(47), g_column_headings(48),
81 g_column_headings(49), g_column_headings(50), g_column_headings(51),
82 g_column_headings(52), g_column_headings(53), g_column_headings(54),
83 g_column_headings(55), g_column_headings(56), g_column_headings(57),
84 g_column_headings(58), g_column_headings(59), g_column_headings(60),
85 g_column_headings(61), g_column_headings(62), g_column_headings(63),
86 g_column_headings(64), g_column_headings(65), g_column_headings(66),
87 g_column_headings(67), g_column_headings(68), g_column_headings(69),
88 g_column_headings(70), g_column_headings(71), g_column_headings(72),
89 g_column_headings(73), g_column_headings(74), g_column_headings(75),
90 g_column_headings(76), g_column_headings(77), g_column_headings(78),
91 g_column_headings(79), g_column_headings(80), g_column_headings(81),
92 g_column_headings(82), g_column_headings(83), g_column_headings(84),
93 g_column_headings(85), g_column_headings(86), g_column_headings(87),
94 g_column_headings(88), g_column_headings(89), g_column_headings(90),
95 g_column_headings(91), g_column_headings(92), g_column_headings(93),
96 g_column_headings(94), g_column_headings(95), g_column_headings(96),
97 g_column_headings(97), g_column_headings(98), g_column_headings(99),
98 g_column_headings(100),g_column_headings(101),g_column_headings(102),
99 g_column_headings(103),g_column_headings(104),g_column_headings(105),
100 g_column_headings(106),g_column_headings(107),g_column_headings(108),
101 g_column_headings(109),g_column_headings(110),g_column_headings(111),
102 g_column_headings(112),g_column_headings(113),g_column_headings(114),
103 g_column_headings(115),g_column_headings(116),g_column_headings(117),
104 g_column_headings(118),g_column_headings(119),g_column_headings(120),
105 g_column_headings(121),g_column_headings(122),g_column_headings(123),
106 g_column_headings(124),g_column_headings(125),g_column_headings(126),
107 g_column_headings(127),g_column_headings(128),g_column_headings(129),
108 g_column_headings(130),g_column_headings(131),g_column_headings(132),
109 g_column_headings(133),g_column_headings(134),g_column_headings(135),
110 g_column_headings(136),g_column_headings(137),g_column_headings(138),
111 g_column_headings(139),g_column_headings(140),g_column_headings(141),
112 g_column_headings(142),g_column_headings(143),g_column_headings(144),
113 g_column_headings(145),g_column_headings(146),g_column_headings(147),
114 g_column_headings(148),g_column_headings(149),g_column_headings(150),
115 g_column_headings(151),g_column_headings(152),g_column_headings(153),
116 g_column_headings(154),g_column_headings(155),g_column_headings(156),
117 g_column_headings(157),g_column_headings(158),g_column_headings(159),
118 g_column_headings(160),g_column_headings(161),g_column_headings(162),
119 g_column_headings(163),g_column_headings(164),g_column_headings(165),
120 g_column_headings(166),g_column_headings(167),g_column_headings(168),
121 g_column_headings(169),g_column_headings(170),g_column_headings(171),
122 g_column_headings(172),g_column_headings(173),g_column_headings(174),
123 g_column_headings(175),g_column_headings(176),g_column_headings(177),
124 g_column_headings(178),g_column_headings(179),g_column_headings(180),
125 g_column_headings(181),g_column_headings(182),g_column_headings(183),
126 g_column_headings(184),g_column_headings(185),g_column_headings(186),
127 g_column_headings(187),g_column_headings(188),g_column_headings(189),
128 g_column_headings(190),g_column_headings(191),g_column_headings(192),
129 g_column_headings(193),g_column_headings(194),g_column_headings(195),
130 g_column_headings(196),g_column_headings(197),g_column_headings(198),
131 g_column_headings(199),g_column_headings(200),g_column_headings(201),
132 g_column_headings(202),g_column_headings(203),g_column_headings(204),
133 g_column_headings(205),g_column_headings(206),g_column_headings(207),
134 g_column_headings(208),g_column_headings(209),g_column_headings(210),
135 g_column_headings(211),g_column_headings(212),g_column_headings(213),
136 g_column_headings(214),g_column_headings(215),g_column_headings(216),
137 g_column_headings(217),g_column_headings(218),g_column_headings(219),
138 g_column_headings(220),g_column_headings(221),g_column_headings(222),
139 g_column_headings(223),g_column_headings(224),g_column_headings(225),
140 g_column_headings(226),g_column_headings(227),g_column_headings(228),
141 g_column_headings(229),g_column_headings(230)
142 FROM HR_DU_UPLOAD_LINES
143 WHERE UPLOAD_LINE_ID = p_line_id;
144
145 hr_du_utility.message('INFO','Select Statement Ends ' , 20);
146
147 --
148 hr_du_utility.message('ROUT','exit:hr_du_do_datapump.' ||
149 ' store_column_headings', 25);
150 --
151 EXCEPTION
152 WHEN OTHERS THEN
153 hr_du_utility.error(SQLCODE, 'hr_du_do_datapump.
154 store_column_headings',
155 '(none)', 'R');
156 RAISE;
157 --
158 END STORE_COLUMN_HEADINGS;
159
160
161
162 -- ---------------------- FIND_USER_KEY_FROM_MAPPINGS ----------------------
163 -- Description: Simply uses a Cursor to retrieve any column from
164 -- COLUMN_MPAPPINGS that has a mapping type of U. For example the
165 -- mapped_to_name retrieved for the CREATE_US_EMPLOYEE would be i.e.
166 -- 'p_employee_user_key'.
167 --
168 -- ------------------------------------------------------------------------
169 FUNCTION FIND_USER_KEY_FROM_MAPPINGS(p_api_module_id IN NUMBER)
170 RETURN VARCHAR2
171 IS
172
173 e_fatal_error EXCEPTION;
174 l_fatal_error_message VARCHAR2(2000);
175 l_user_key VARCHAR2(2000);
176
177 CURSOR csr_user_key IS
178 SELECT mapped_to_name
179 FROM hr_du_column_mappings
180 WHERE API_MODULE_ID = p_api_module_id
181 AND MAPPING_TYPE = 'U';
182
183 BEGIN
184 --
185 hr_du_utility.message('ROUT',
186 'entry:hr_du_do_datapump.find_user_key_from_mappings', 5);
187 hr_du_utility.message('PARA', '(p_api_module_id - ' ||
188 p_api_module_id || ')' , 10);
189 --
190
191 OPEN csr_user_key;
192 --
193 FETCH csr_user_key INTO l_user_key;
194 IF csr_user_key%NOTFOUND THEN
195 l_fatal_error_message := ' Unable to retrieve the user key';
196 RAISE e_fatal_error;
197 END IF;
198 --
199 CLOSE csr_user_key;
200
201 --
202 hr_du_utility.message('ROUT',
203 'exit:hr_du_do_datapump.find_user_key_from_mappings', 15);
204 hr_du_utility.message('PARA', '(l_user_key - ' || l_user_key || ')' , 20);
205 --
206
207 RETURN l_user_key;
208
209 EXCEPTION
210 WHEN e_fatal_error THEN
211 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.
212 find_user_key_from_mappings',l_fatal_error_message, 'R');
213 RAISE;
214 WHEN OTHERS THEN
215 hr_du_utility.error(SQLCODE,
216 'hr_du_do_datapump.find_user_key_from_mappings','(none)', 'R');
217 RAISE;
218 --
219 END FIND_USER_KEY_FROM_MAPPINGS;
220
221
222
223 -- ------------------------ API_ID_TO_PROCESS_ORDER -----------------------
224 -- Description: Cursor is run to work out the Process order of a file from
225 -- it's given API_module_Id.
226 --
227 -- ------------------------------------------------------------------------
228 FUNCTION API_ID_TO_PROCESS_ORDER(p_api_module_id IN NUMBER,
229 p_upload_id IN NUMBER)
230 RETURN NUMBER
231 IS
232
233 e_fatal_error EXCEPTION;
234 l_fatal_error_message VARCHAR2(2000);
235 l_process_order NUMBER;
236
237
238 CURSOR csr_process_order IS
239 SELECT des2.value
240 FROM hr_du_descriptors des1,
241 hr_du_descriptors des2,
242 hr_api_modules api
243 WHERE des1.upload_id = p_upload_id
244 and upper(des1.descriptor) = 'API'
245 and upper(des2.descriptor) = 'PROCESS ORDER'
246 and des2.descriptor_type = 'D'
247 and des1.upload_header_id = des2.upload_header_id
248 and api.api_module_id = p_api_module_id
249 and upper(des1.value) = api.module_name;
250
251 BEGIN
252 --
253 hr_du_utility.message('ROUT',
254 'entry:hr_du_do_datapump.api_id_to_process_order', 5);
255 hr_du_utility.message('PARA', '(p_api_module_id - ' || p_api_module_id ||
256 ')(p_upload_id - ' || p_upload_id || ')'
257 , 10);
258 --
259
260 OPEN csr_process_order;
261 --
262 FETCH csr_process_order INTO l_process_order;
263 IF csr_process_order%NOTFOUND THEN
264 l_fatal_error_message := ' Unable to retrieve the process order';
265 RAISE e_fatal_error;
266 END IF;
267 --
268 CLOSE csr_process_order;
269
270 --
271 hr_du_utility.message('ROUT',
272 'exit:hr_du_do_datapump.api_id_to_process_order', 15);
273 hr_du_utility.message('PARA', '(l_process_order - ' || l_process_order
274 || ')' , 20);
275 --
276
277 RETURN l_process_order;
278
279 EXCEPTION
280 WHEN e_fatal_error THEN
281 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.api_id_to_process_order'
282 ,l_fatal_error_message, 'R');
283 RAISE;
284 WHEN OTHERS THEN
285 hr_du_utility.error(SQLCODE,
286 'hr_du_do_datapump.api_id_to_process_order','(none)', 'R');
287 RAISE;
288 --
289 END API_ID_TO_PROCESS_ORDER;
290
291
292 -- ----------------------- ANY_EXTRA_REFERENCES -------------------------
293 -- Description: Special case to catch those API's that have user keys
294 -- which need to be complete for the API and aren't mentioned up to this
295 -- point. Such an example is p_assignment_user_key in the person module.
296 --
297 -- Input Parameters
298 --
299 -- p_user_key - This is the calling API's p_user_key. To be matched
300 -- to API's in the column_mappings's column_name
301 --
302 -- p_api_module_id - The specific api_module id.
303 --
304 --
305 -- Output Parameters
306 --
307 -- p_number_of_keys - The number of extra references encountered
308 --
309 -- l_extra_user_keys - String with the p_foreign_user_keys joined together
310 -- separated by commas
311 --
312 -- ------------------------------------------------------------------------
313 FUNCTION ANY_EXTRA_REFERENCES(p_user_key IN VARCHAR2,
314 p_api_module_id IN NUMBER,
315 p_number_of_keys OUT NOCOPY INTEGER)
316 RETURN VARCHAR2
317 IS
318
319 CURSOR csr_references IS
320 SELECT mapped_to_name
321 FROM hr_du_column_mappings
322 WHERE api_module_id = p_api_module_id
323 AND mapping_type = 'D'
324 AND column_name = p_user_key;
325
326 l_extra_user_keys VARCHAR2(2000) := NULL;
327 l_extra_name VARCHAR2(2000);
328 l_length NUMBER;
329 l_string_length NUMBER;
330
331 BEGIN
332 --
333 hr_du_utility.message('ROUT',
334 'entry:hr_du_do_datapump.any_extra_references', 5);
335 hr_du_utility.message('PARA', '(p_user_key - ' || p_user_key ||
336 ')(p_api_module_id - ' || p_api_module_id ||
337 ')(p_number_of_keys - ' || p_number_of_keys || ')'
338 , 10);
339 --
340 p_number_of_keys := 0;
341
342 OPEN csr_references;
343 --
344 FETCH csr_references INTO l_extra_name;
345 IF csr_references%FOUND THEN
349 --
346 l_extra_user_keys := l_extra_user_keys || l_extra_name || ',' ;
347 p_number_of_keys := p_number_of_keys + 1;
348 END IF;
350 CLOSE csr_references;
351
352 IF p_number_of_keys > 0 THEN
353 --removes the last ',' at the end of the string
354 l_length := LENGTHB(',');
355 l_string_length := LENGTHB(l_extra_user_keys);
356 l_extra_user_keys := SUBSTRB(l_extra_user_keys,1,
357 (l_string_length - l_length));
358 END IF;
359
360 --
361 hr_du_utility.message('ROUT',
362 'exit:hr_du_do_datapump.any_extra_references', 15);
363 hr_du_utility.message('PARA', '(l_extra_user_keys - ' || l_extra_user_keys
364 || ')' , 20);
365 --
366
367 RETURN l_extra_user_keys;
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 hr_du_utility.error(SQLCODE,
372 'hr_du_do_datapump.any_extra_references','(none)', 'R');
373 RAISE;
374 --
375 END ANY_EXTRA_REFERENCES;
376
377
378 -- ----------------------- GENERAL_REFERENCING_COLUMN ----------------------
379 -- Description: Checks to see whether the column name passed is a
380 -- referencing or a datapump column, depending on the variables passed
381 --
382 -- Input Parameters
383 --
384 -- p_pval_field - The name of which column the information
385 -- should be in.
386 --
387 -- p_api_module_id - api_module id relating to HR_API_MODULES
388 --
389 -- p_mapping_type - The output target i.e. 'D' (datapump) or
390 -- 'R' (referencing)
391 --
392 -- Output Parameters
393 --
394 -- l_mapped_name - The name of the field MAPPED_TO_NAME in
395 -- HR_DU_COLUMN_MAPPINGS if a match is found.
396 -- ------------------------------------------------------------------------
397 FUNCTION GENERAL_REFERENCING_COLUMN(p_pval_field IN VARCHAR2,
398 p_api_module_id IN NUMBER,
399 p_mapping_type IN VARCHAR2)
400 RETURN VARCHAR2
401 IS
402
403 l_mapped_name VARCHAR2(30);
404
405 CURSOR csr_ref_col IS
406 SELECT mapped_to_name
407 FROM hr_du_column_mappings
408 WHERE api_module_id = p_api_module_id
409 AND mapping_type = p_mapping_type
410 AND column_name = p_pval_field
411 AND PARENT_api_module_ID IS NULL
412 AND PARENT_TABLE IS NULL;
413
414 BEGIN
415 --
416 hr_du_utility.message('PARA', '(p_pval_field- ' || p_pval_field ||
417 ')(p_api_module_id - ' || p_api_module_id ||
418 ')(p_mapping_type - ' || p_mapping_type || ')'
419 , 5);
420 --
421 OPEN csr_ref_col;
422 --
423 FETCH csr_ref_col INTO l_mapped_name;
424 IF csr_ref_col%NOTFOUND THEN
425 l_mapped_name := null;
426 END IF;
427 --
428 CLOSE csr_ref_col;
429 --
430 hr_du_utility.message('PARA', '(l_mapped_name - ' || l_mapped_name || ')'
431 , 10);
432 --
433
434 RETURN l_mapped_name;
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 hr_du_utility.error(SQLCODE,
439 'hr_du_do_datapump.general_referencing_column','(none)', 'R');
440 RAISE;
441 --
442 END GENERAL_REFERENCING_COLUMN;
443
444
445 -- ----------------------------- SET_STATUS --------------------------------
446 -- Description: Sets all the lines that will be read during the execution
447 -- of this package.
448 --
449 -- Input Parameters
450 -- p_upload_id - Link to the HR_DU_UPLOADS table that will allow all
451 -- the relevant HR_DU_UPLOAD_LINES to be identified
452 -- -------------------------------------------------------------------------
453 PROCEDURE SET_STATUS(p_upload_id IN NUMBER) IS
454
455 BEGIN
456
457 --
458 hr_du_utility.message('ROUT',
459 'entry:hr_du_do_datapump.set_status', 5);
460 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')', 10);
461 --
462
463 UPDATE hr_du_upload_lines
464 SET status = 'NS'
465 WHERE UPLOAD_HEADER_ID IN (SELECT upload_header_id
466 FROM hr_du_upload_headers
467 WHERE upload_id = p_upload_id)
468 AND (REFERENCE_TYPE = 'CP' OR LINE_TYPE = 'C');
469 COMMIT;
470
471 --
472 hr_du_utility.message('ROUT',
473 'exit:hr_du_do_datapump.set_status', 15);
474 --
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.set_status'
479 ,'(none)', 'R');
480 RAISE;
481 --
482 END SET_STATUS;
483
484
485 -- -------------------- RETURN_CREATED_USER_KEY ---------------------------
486 -- Description: This is called from HR_DU_ENTITIES and the user key is
487 -- passed containing its PVAL's and the strings. This function makes up the
488 -- appropriate for the user key for the particular HR_DU_UPLOAD_LINE
489 --
490 -- Input Parameters
491 -- p_api_module_id - ID identifying the correct api_module
492 --
496 -- p_upload_id - Identifies the correct HR_DU_UPLOAD
493 -- p_column_id - The ID of the column that the function will be
494 -- working on.
495 --
497 --
498 -- Output Parameters
499 -- p_user_key - Returns the column name from HR_DU_COLUMN_MAPPINGS
500 -- to what the user key is mapped to
501 --
502 -- l_actual_user_key - This is the user key that uniquely identifies a
503 -- record
504 -- ------------------------------------------------------------------------
505 FUNCTION RETURN_CREATED_USER_KEY(p_api_module_id IN NUMBER,
506 p_column_id IN NUMBER,
507 p_upload_id IN NUMBER,
508 p_user_key OUT NOCOPY VARCHAR2)
509 RETURN VARCHAR2 IS
510
511
512 e_fatal_error EXCEPTION;
513 l_fatal_error_message VARCHAR2(2000);
514 l_table_size NUMBER;
515 l_array_pos NUMBER := null;
516 l_dynamic_string VARCHAR2(2000);
517 l_upload_line_id NUMBER;
518 l_number_keys NUMBER;
519 l_field_value VARCHAR2(2000);
520 l_actual_user_key VARCHAR2(2000) := null;
521 l_length NUMBER;
522 l_string_length NUMBER;
523 l_single_key VARCHAR2(2000);
524 l_position NUMBER;
525 l_user_key_table_size NUMBER;
526 l_found_id_value BOOLEAN;
527
528
529 CURSOR csr_upload_line_id IS
530 SELECT line.UPLOAD_LINE_ID
531 FROM hr_du_upload_headers head,
532 hr_du_upload_lines line
533 WHERE head.upload_id = p_upload_id
534 AND head.api_module_id = p_api_module_id
535 AND line.upload_header_id = head.upload_header_id
536 AND line.PVAL001 = to_char(p_column_id);
537
538 BEGIN
539 --
540 hr_du_utility.message('ROUT',
541 'entry:hr_du_do_datapump.return_created_user_key', 5);
542 hr_du_utility.message('PARA', '(p_api_module_id - ' || p_api_module_id ||
543 ')(p_column_id - ' || p_column_id ||
544 ')(p_upload_id - ' || p_upload_id || ')' ,
545 10);
546 --
547 l_table_size := g_values_table.count;
548 FOR i IN 1..l_table_size LOOP
549 IF g_values_table(i).r_api_id = p_api_module_id THEN
550 l_array_pos := i;
551 EXIT;
552 END IF;
553 END LOOP;
554
555 IF l_array_pos IS NULL THEN
556 l_fatal_error_message := 'Unable to match api_module_ID to PL/SQL ' ||
557 'table values';
558 RAISE e_fatal_error;
559 END IF;
560
561
562 --Returns the table size
563 l_user_key_table_size := g_user_key_table.count;
564 l_found_id_value := FALSE;
565
566 FOR k IN 1..l_user_key_table_size LOOP
567 --
568 IF (g_user_key_table(k).r_api_module_id = p_api_module_id) AND
569 (g_user_key_table(k).r_column_id = p_column_id) THEN
570 p_user_key := g_user_key_table(k).r_user_key;
571 l_actual_user_key := g_user_key_table(k).r_actual_user_key;
572 l_found_id_value := TRUE;
573 EXIT;
574 END IF;
575 --
576 END LOOP;
577
578 IF l_found_id_value = FALSE THEN
579 --
580 --Selects the line id for a given line who has the matching ID value which
581 --is stored in the position of the id column for the particular api_module
582 OPEN csr_upload_line_id;
583 --
584 FETCH csr_upload_line_id INTO l_upload_line_id;
585 IF csr_upload_line_id%NOTFOUND THEN
586 l_fatal_error_message := ' Unable to fine ID ' || p_column_id ||
587 ' in API ' || p_api_module_id ||
588 '. Referencing column in other file has this invalid reference';
589 RAISE e_fatal_error;
590 END IF;
591 --
592 CLOSE csr_upload_line_id;
593
594 hr_du_utility.message('INFO', 'l_upload_line_id : ' ||
595 l_upload_line_id , 20);
596
597 -- now work out the size of the user key separated by : loop around
598 -- getting it and then glue them on to a string
599
600 hr_du_di_insert.g_current_delimiter := ':';
601
602 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
603 g_values_table(l_array_pos).r_user_key_pval);
604
605 l_number_keys := hr_du_di_insert.WORDS_ON_LINE(
606 g_values_table(l_array_pos).r_user_key_pval);
607
608 IF g_values_table(l_array_pos).r_user_key_pval IS NOT NULL THEN
609 --
610 FOR j IN 1..l_number_keys LOOP
611
612 l_single_key := hr_du_di_insert.Return_Word(
613 g_values_table(l_array_pos).r_user_key_pval, j);
614
615 l_position := INSTRB( l_single_key, '%');
616
617 IF l_position > 0 THEN
618 --adds user stated strings to the user_key
619 hr_du_di_insert.g_current_delimiter := '%';
620
621 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.
622 Num_Delimiters(l_single_key);
623 l_single_key := hr_du_di_insert.Return_Word(l_single_key, 2);
624 l_actual_user_key := l_actual_user_key || ':' || '''' ||
625 l_single_key || '''';
626 ELSE
630 ('HR_DU_UPLOAD_LINES', l_upload_line_id,
627 --adds column values to the user_key
628
629 l_field_value := hr_du_dp_pc_conversion.return_field_value
631 'upload_line_id', l_single_key);
632
633 l_actual_user_key := l_actual_user_key || ':' || l_field_value;
634 END IF;
635 END LOOP;
636
637 l_length := LENGTHB(':');
638 l_string_length := LENGTHB(l_actual_user_key);
639 l_actual_user_key := SUBSTRB(l_actual_user_key, l_length + 1);
640
641 --this value is returned in OUT parameters
642 p_user_key := FIND_USER_KEY_FROM_MAPPINGS(g_values_table(l_array_pos).
643 r_api_id);
644 --
645 END IF;
646
647 -- the user key can be a maximum of 240 characters long
648 IF (length(l_actual_user_key) > 240) THEN
649 l_fatal_error_message := 'The generated user key, ' || l_actual_user_key
650 || ', is over 240 characters long, which is the'
651 || ' maximum size for Datapump.';
652 RAISE e_fatal_error;
653 END IF;
654
655 l_user_key_table_size := g_user_key_table.count;
656 --Add the information to the table
657 g_user_key_table(l_user_key_table_size + 1).r_api_module_id :=
658 p_api_module_id;
659 g_user_key_table(l_user_key_table_size + 1).r_column_id :=
660 p_column_id;
661 g_user_key_table(l_user_key_table_size + 1).r_user_key :=
662 p_user_key;
663 g_user_key_table(l_user_key_table_size + 1).r_actual_user_key :=
664 l_actual_user_key;
665 --
666 END IF;
667
668 --
669 hr_du_utility.message('ROUT',
670 'exit:hr_du_do_datapump.return_created_user_key', 15);
671 hr_du_utility.message('PARA', '(l_actual_user_key - ' || l_actual_user_key
672 || ')' , 20);
673 --
674
675 RETURN l_actual_user_key;
676
677 EXCEPTION
678 WHEN e_fatal_error THEN
679 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.return_created_user_key'
680 ,l_fatal_error_message, 'R');
681 RAISE;
682 WHEN OTHERS THEN
683 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.return_created_user_key'
684 ,'(none)', 'R');
685 RAISE;
686 --
687 END RETURN_CREATED_USER_KEY;
688
689
690 -- -------------------- RETURN_CREATED_USER_KEY_2 --------------------------
691 -- Description: This is called from HR_DU_ENTITIES. The way that it differs
692 -- from RETURN_CREATED_USER_KEY is that it's called by an upload_line
693 -- to work out its own user key and not a referencing user key.
694 --
695 -- Input Parameters
696 -- p_api_module_id - ID identifying the correct api_module
697 --
698 -- p_upload_line_id - Identifies the UPLOAD_LINE_ID
699 --
700 -- Output Parameters
701 -- p_user_key - Returns the column name from HR_DU_COLUMN_MAPPINGS
702 -- to what the user key is mapped to
703 --
704 -- l_actual_user_key - This is the user key that uniquely identifies a
705 -- record
706 -- ------------------------------------------------------------------------
707 FUNCTION RETURN_CREATED_USER_KEY_2(
708 p_column_id IN NUMBER,
709 p_api_module_id IN NUMBER,
710 p_upload_line_id IN NUMBER,
711 p_user_key OUT NOCOPY VARCHAR2)
712 RETURN VARCHAR2 IS
713
714 e_fatal_error EXCEPTION;
715 l_fatal_error_message VARCHAR2(2000);
716 l_table_size NUMBER;
717 l_array_pos NUMBER := null;
718 l_number_keys NUMBER;
719 l_field_value VARCHAR2(2000);
720 l_actual_user_key VARCHAR2(2000) := null;
721 l_length NUMBER;
722 l_string_length NUMBER;
723 l_single_key VARCHAR2(2000);
724 l_position NUMBER;
725 l_user_key_table_size NUMBER;
726
727 BEGIN
728
729 --
730 hr_du_utility.message('ROUT',
731 'entry:hr_du_do_datapump.return_created_user_key_2', 5);
732 hr_du_utility.message('PARA', '(p_column_id - ' || p_column_id ||
733 ')(p_api_module_id - ' || p_api_module_id ||
734 ')(p_upload_line_id - ' || p_upload_line_id
735 || ')' , 10);
736 --
737
738 l_table_size := g_values_table.count;
739 FOR i IN 1..l_table_size LOOP
740 IF g_values_table(i).r_api_id = p_api_module_id THEN
741 l_array_pos := i;
742 EXIT;
743 END IF;
744 END LOOP;
745
746 IF l_array_pos IS NULL THEN
747 l_fatal_error_message := 'Unable to match api_module_ID to PL/SQL ' ||
748 'table values';
749 RAISE e_fatal_error;
750 END IF;
751
752 -- now work out the size of the user key separated by : loop around
753 -- getting it and then glue them on to a string
754
755 hr_du_di_insert.g_current_delimiter := ':';
756
757 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
761 g_values_table(l_array_pos).r_user_key_pval);
758 g_values_table(l_array_pos).r_user_key_pval);
759
760 l_number_keys := hr_du_di_insert.WORDS_ON_LINE(
762
763 IF g_values_table(l_array_pos).r_user_key_pval IS NOT NULL THEN
764 --
765 FOR j IN 1..l_number_keys LOOP
766
767 l_single_key := hr_du_di_insert.Return_Word(
768 g_values_table(l_array_pos).r_user_key_pval, j);
769
770 l_position := INSTRB( l_single_key, '%');
771
772 IF l_position > 0 THEN
773 --adds user stated strings to the user_key
774 hr_du_di_insert.g_current_delimiter := '%';
775
776 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.
777 Num_Delimiters(l_single_key);
778
779 l_single_key := hr_du_di_insert.Return_Word(l_single_key, 2);
780 l_actual_user_key := l_actual_user_key || ':' || '''' ||
781 l_single_key || '''';
782 ELSE
783 --adds column values to the user_key
784
785 l_field_value := hr_du_dp_pc_conversion.RETURN_FIELD_VALUE
786 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
787 'upload_line_id', l_single_key);
788
789 l_actual_user_key := l_actual_user_key || ':' || l_field_value;
790 END IF;
791 END LOOP;
792
793 l_length := LENGTHB(':');
794 l_string_length := LENGTHB(l_actual_user_key);
795 l_actual_user_key := SUBSTRB(l_actual_user_key, l_length + 1);
796
797 --this value is returned in OUT parameters
798 p_user_key := FIND_USER_KEY_FROM_MAPPINGS(g_values_table(l_array_pos).
799 r_api_id);
800 --
801 END IF;
802
803 -- the user key can be a maximum of 240 characters long
804 IF (length(l_actual_user_key) > 240) THEN
805 l_fatal_error_message := 'The generated user key, ' || l_actual_user_key
806 || ', is over 240 characters long, which is the'
807 || ' maximum size for Datapump.';
808 RAISE e_fatal_error;
809 END IF;
810
811 l_user_key_table_size := g_user_key_table.count;
812
813 --Add the information to the table
814 g_user_key_table(l_user_key_table_size + 1).r_api_module_id := p_api_module_id;
815 g_user_key_table(l_user_key_table_size + 1).r_column_id := p_column_id;
816 g_user_key_table(l_user_key_table_size + 1).r_user_key := p_user_key;
817 g_user_key_table(l_user_key_table_size + 1).r_actual_user_key := l_actual_user_key;
818
819 --
820 hr_du_utility.message('ROUT',
821 'exit:hr_du_do_datapump.return_created_user_key_2', 15);
822 hr_du_utility.message('PARA', '(l_actual_user_key - ' || l_actual_user_key
823 || ')' , 20);
824 --
825
826 RETURN l_actual_user_key;
827
828 EXCEPTION
829 WHEN e_fatal_error THEN
830 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.return_created_user_key_2'
831 ,l_fatal_error_message, 'R');
832 RAISE;
833 WHEN OTHERS THEN
834 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.return_created_user_key_2'
835 ,'(none)', 'R');
836 RAISE;
837 --
838 END RETURN_CREATED_USER_KEY_2;
839
840
841 -- ------------------------- RETURN_PVAL ---------------------------------
842 -- Description: The function takes a mapped_to_name and tires to match it
843 -- to an entry in the R_INSERT_STRING row of the PL/SQL table. If the match
844 -- is found the the corresponding entry in the R_PVAL_STRING is returned.
845 --
846 -- Input Parameters
847 -- p_mapped_name - The name your looking for in R_INSERT_STRING
848 --
849 -- p_table_pos - The numerical position in the PL/SQL table
850 --
851 -- Output Parameters
852 -- l_pval - The corresponding PVAL*** in the R_PVAL_STRING
853 --
854 -- ------------------------------------------------------------------------
855 FUNCTION RETURN_PVAL(p_mapped_name IN VARCHAR2,
856 p_table_pos IN NUMBER)
857 RETURN VARCHAR2 IS
858
859 l_number_names NUMBER;
860 l_pval VARCHAR2(50) := null;
861 l_single_name VARCHAR2(50);
862
863 BEGIN
864 --
865 hr_du_utility.message('ROUT',
866 'entry:hr_du_do_datapump.return_pval', 5);
867 hr_du_utility.message('PARA', '(p_mapped_name - ' || p_mapped_name ||
868 ')(p_table_pos - ' || p_table_pos || ')' ,
869 10);
870 --
871
872
873 hr_du_di_insert.g_current_delimiter := ',';
874
875 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
876 g_values_table(p_table_pos).r_insert_string);
877
878 l_number_names := hr_du_di_insert.WORDS_ON_LINE(
879 g_values_table(p_table_pos).r_insert_string);
880
881 FOR j IN 1..l_number_names LOOP
882 --
883 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
884 g_values_table(p_table_pos).r_insert_string);
885
886 l_single_name := hr_du_di_insert.Return_Word(
887 g_values_table(p_table_pos).r_insert_string, j);
888
889 IF l_single_name = p_mapped_name THEN
890 --
894 l_pval:= hr_du_di_insert.Return_Word(
891 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
892 g_values_table(p_table_pos).r_PVAL_string);
893
895 g_values_table(p_table_pos).r_PVAL_string, j);
896 EXIT;
897 --
898 END IF;
899 --
900 END LOOP;
901 --
902 hr_du_utility.message('ROUT',
903 'exit:hr_du_do_datapump.return_pval', 30);
904 hr_du_utility.message('PARA', '(l_pval - ' || l_pval || ')' , 20);
905 --
906 RETURN l_pval;
907
908 EXCEPTION
909 WHEN OTHERS THEN
910 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.return_pval'
911 ,'(none)', 'R');
912 RAISE;
913 --
914 END RETURN_PVAL;
915
916
917 -- ---------------------- EXTRACT_BUSINESS_GROUP --------------------------
918 -- Description: Simple cursor that is run to find the business group which
919 -- is stored within the header file with the Descriptor name of
920 -- BUSINESS GROUP
921 --
922 -- Input Parameters
923 -- p_upload_id - The upload id to associate the procedure with
924 -- correct table
925 --
926 -- ------------------------------------------------------------------------
927 FUNCTION EXTRACT_BUSINESS_GROUP(p_upload_id IN NUMBER)
928 RETURN VARCHAR2
929 IS
930
931 e_fatal_error EXCEPTION;
932 l_fatal_error_message VARCHAR2(2000);
933 l_business_group VARCHAR2(2000);
934
935 BEGIN
936
937 --
938 hr_du_utility.message('ROUT',
939 'entry:hr_du_do_datapump.extract_business_group', 5);
940 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')', 10);
941 --
942
943 --Retrieve the business group for the appropriate HR_DU_UPLOADS
944 BEGIN
945 SELECT VALUE
946 INTO l_business_group
947 FROM HR_DU_DESCRIPTORS
948 WHERE DESCRIPTOR = 'BUSINESS GROUP'
949 AND upload_id = p_upload_id;
950 EXCEPTION
951 WHEN OTHERS THEN
952 l_fatal_error_message := 'Error occured while trying to retrieve the' ||
953 ' business group from HR_DU_UPLOADS with the'||
954 ' upload_id of : ' || p_upload_id ;
955 RAISE e_fatal_error;
956 END;
957
958 --
959 hr_du_utility.message('ROUT',
960 'exit:hr_du_do_datapump.extract_business_group', 15);
961 --
962
963 RETURN l_business_group;
964
965 EXCEPTION
966 WHEN e_fatal_error THEN
967 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.extract_business_group'
968 ,l_fatal_error_message, 'R');
969 RAISE;
970 WHEN OTHERS THEN
971 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.extract_business_group'
972 ,'(none)', 'R');
973 RAISE;
974 --
975 END EXTRACT_BUSINESS_GROUP;
976
977
978 -- ------------------------ EXTRACT_USER_KEY ------------------------------
979 -- Description: This function removes the user_key that is stored within
980 -- the HR_DU_DESCRIPTORS table. It is the exact string that was found in
981 -- the flat file in the descriptor block.
982 --
983 -- Input Parameters
984 -- p_upload_id - The upload id to associate the procedure with
985 -- correct table
986 --
987 -- p_table_id - Position in the PL/SQL table that the information
988 -- is held about the API
989 -- ------------------------------------------------------------------------
990 FUNCTION EXTRACT_USER_KEY(p_upload_id IN NUMBER,
991 p_table_id IN NUMBER)
992 RETURN VARCHAR2 IS
993
994 CURSOR csr_user_key IS
995 SELECT descr.VALUE
996 FROM HR_DU_DESCRIPTORS descr,
997 HR_DU_UPLOAD_HEADERS head
998 WHERE head.upload_id = p_upload_id
999 AND head.api_module_id = g_values_table(p_table_id).r_api_id
1000 AND head.upload_header_id = descr.upload_header_id
1001 AND descr.DESCRIPTOR = 'USER KEY';
1002
1003 l_user_key VARCHAR2(2000);
1004
1005 BEGIN
1006
1007 --
1008 hr_du_utility.message('ROUT',
1009 'entry:hr_du_do_datapump.extract_user_key', 5);
1010 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id ||
1011 ')(p_table_id - ' || p_table_id || ')' ,
1012 10);
1013 --
1014 OPEN csr_user_key;
1015 --
1016 FETCH csr_user_key INTO l_user_key;
1017 IF csr_user_key%NOTFOUND THEN
1018 l_user_key := null;
1019 END IF;
1020 --
1021 CLOSE csr_user_key;
1022
1023 --
1024 hr_du_utility.message('ROUT',
1025 'exit:hr_du_do_datapump.extract_user_key', 15);
1026 hr_du_utility.message('PARA', '(l_user_key - ' || l_user_key|| ')' , 20);
1027 --
1028
1029 RETURN l_user_key;
1030
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.extract_user_key'
1034 ,'(none)', 'R');
1035 RAISE;
1036 --
1037 END EXTRACT_USER_KEY;
1038
1039
1040
1044 -- PL/SQL table.
1041 -- ------------------------- CREATE_USER_KEY_STRING -----------------------
1042 -- Description: Stores the PVAL positions of the appropriate user key
1043 -- columns stated by the user. Into a string R_USER_KEY_PVAL within the
1045 --
1046 -- Input Parameters
1047 -- p_upload_id - Identifies this particular Upload over other
1048 -- simular ones.
1049 --
1050 -- p_table_size - Size of the PL/SQL table
1051 -- ------------------------------------------------------------------------
1052 PROCEDURE CREATE_USER_KEY_STRING(p_upload_id IN VARCHAR2,
1053 p_table_size IN NUMBER) IS
1054
1055
1056 e_fatal_error EXCEPTION;
1057 l_fatal_error_message VARCHAR2(2000);
1058 l_user_key VARCHAR2(2000);
1059 l_number_keys NUMBER;
1060 l_single_key VARCHAR2(2000);
1061 l_mapped_name VARCHAR2(50);
1062 l_key_pval VARCHAR2(50);
1063 l_key_pval_string VARCHAR2(2000) := null;
1064 l_length NUMBER;
1065 l_length2 NUMBER;
1066 l_counter NUMBER;
1067 l_position NUMBER;
1068 l_temp VARCHAR2(2000);
1069 l_api_id NUMBER;
1070 l_referencing VARCHAR2(200);
1071 l_upload_header_id NUMBER;
1072 l_starting_bool VARCHAR2(50);
1073
1074
1075 --Cursor compares the user key word to HR_DU_COLUMN_MAPPINGS
1076 --in the main header for all API's
1077 CURSOR csr_dollar_key IS
1078 SELECT des.VALUE
1079 FROM hr_du_descriptors des,
1080 hr_du_uploads uplo
1081 WHERE uplo.upload_id = p_upload_id
1082 AND uplo.upload_id = des.upload_id
1083 AND des.upload_header_id IS NULL
1084 AND upper(des.descriptor) = upper(l_single_key);
1085
1086 --Cursor compares the user key word to HR_DU_COLUMN_MAPPINGS
1087 --in the specific API header
1088 CURSOR csr_dollar_key_api IS
1089 SELECT des.VALUE
1090 FROM hr_du_descriptors des,
1091 hr_du_upload_headers head
1092 WHERE head.api_module_id = l_api_id
1093 AND head.upload_id = p_upload_id
1094 AND head.upload_header_id = des.upload_header_id
1095 AND upper(des.descriptor) = upper(l_single_key);
1096
1097 --Extracts the referencing type to checks for PC referencing
1098 CURSOR csr_referencing IS
1099 SELECT VALUE
1100 FROM hr_du_descriptors
1101 WHERE upload_header_id = l_upload_header_id
1102 AND upper(descriptor) = 'REFERENCING';
1103
1104 --Extracts the referencing type to checks for a Starting point
1105 CURSOR csr_starting_point IS
1106 SELECT VALUE
1107 FROM hr_du_descriptors
1108 WHERE upload_header_id = l_upload_header_id
1109 AND upper(descriptor) = 'STARTING POINT';
1110
1111
1112 BEGIN
1113
1114 --
1115 hr_du_utility.message('ROUT',
1116 'entry:hr_du_do_datapump.create_user_key_string', 5);
1117 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id ||
1118 ')(p_table_size - ' || p_table_size || ')', 10);
1119
1120 --
1121 FOR l_counter IN 1..p_table_size LOOP
1122 --
1123 l_key_pval_string := null;
1124
1125 --Extracts the user key defined by the user within the flat file
1126 l_user_key := EXTRACT_USER_KEY(p_upload_id, l_counter);
1127 hr_du_utility.message('INFO', 'API Module id : ' ||
1128 g_values_table(l_counter).r_api_id, 25);
1129
1130 hr_du_di_insert.g_current_delimiter := ':';
1131
1132 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.
1133 Num_Delimiters(l_user_key);
1134 l_number_keys := hr_du_di_insert.WORDS_ON_LINE(l_user_key);
1135
1136 FOR j IN 1..l_number_keys LOOP
1137 l_key_pval := null;
1138 hr_du_di_insert.g_current_delimiter := ':';
1139
1140 l_single_key := hr_du_di_insert.Return_Word(l_user_key, j);
1141
1142 l_position := INSTRB(l_single_key, '%');
1143
1144 --No comments so compared to the column mappings
1145 IF l_position = 0 THEN
1146 l_mapped_name := hr_du_dp_pc_conversion.general_referencing_column
1147 (l_single_key, g_values_table(l_counter).
1148 r_api_id,'D');
1149 IF l_mapped_name IS NULL THEN
1150 l_fatal_error_message := 'Error occured trying to map part of the '
1151 || 'user key : ' || l_single_key || ' ' ||
1152 'to a mapped_to_name in ' ||
1153 'HR_DU_COLUMN_MAPPINGS';
1154 RAISE e_fatal_error;
1155 END IF;
1156
1157 l_key_pval := RETURN_PVAL(l_mapped_name, l_counter);
1158
1159 IF l_key_pval IS NULL THEN
1160 l_fatal_error_message := 'Error occured trying to map part of the '
1161 || 'user key : ' || l_mapped_name || ' ' ||
1162 'to a PVAL column in HR_DU_UPLOAD_LINES';
1163 RAISE e_fatal_error;
1164 END IF;
1165 l_key_pval_string := l_key_pval_string || ':' || l_key_pval;
1166
1167 --
1168 --Comments exist so it's a special case user key
1169 ELSE
1170 hr_du_di_insert.g_current_delimiter := '%';
1171 hr_du_di_insert.g_delimiter_count :=
1172 hr_du_di_insert.Num_Delimiters(l_single_key);
1176 l_position := INSTRB(l_single_key, '$');
1173 l_single_key := hr_du_di_insert.Return_Word(l_single_key, 2);
1174
1175 --Checks begin to see if there are any pointers to DESCRIPTORS
1177 --
1178 IF l_position = 1 THEN
1179 --
1180 hr_du_di_insert.g_current_delimiter := '$';
1181 hr_du_di_insert.g_delimiter_count :=
1182 hr_du_di_insert.Num_Delimiters(l_single_key);
1183
1184 l_single_key := hr_du_di_insert.Return_Word(l_single_key, 2);
1185
1186 OPEN csr_dollar_key;
1187 FETCH csr_dollar_key INTO l_temp;
1188 IF csr_dollar_key%NOTFOUND THEN
1189 --
1190 l_api_id := g_values_table(l_counter).r_api_id;
1191 --this checks the specific headers for the API
1192 OPEN csr_dollar_key_api;
1193 FETCH csr_dollar_key_api INTO l_temp;
1194 IF csr_dollar_key_api%NOTFOUND THEN
1195 l_fatal_error_message := 'User key $ is not a valid ' ||
1196 'descriptor';
1197 RAISE e_fatal_error;
1198 ELSE
1199 l_key_pval_string := l_key_pval_string || ':' || '''' ||
1200 l_temp|| '''';
1201 END IF;
1202 CLOSE csr_dollar_key_api;
1203 ELSE
1204 l_key_pval_string := l_key_pval_string || ':' || '''' ||
1205 l_temp|| '''';
1206 END IF;
1207 --
1208 CLOSE csr_dollar_key;
1209 --
1210 ELSE
1211 IF upper(l_single_key) = 'NONE' THEN
1212 l_key_pval_string := null;
1213 EXIT;
1214 ELSE
1215 l_key_pval_string := l_key_pval_string || ':' || '''' ||
1216 l_single_key || '''';
1217 END IF;
1218 END IF;
1219 END IF;
1220 END LOOP;
1221 --
1222 l_upload_header_id := g_values_table(l_counter).r_upload_header_id;
1223
1224 OPEN csr_referencing;
1225 FETCH csr_referencing INTO l_referencing;
1226 IF csr_referencing%NOTFOUND THEN
1227 l_fatal_error_message := 'Referencing descriptor no found';
1228 RAISE e_fatal_error;
1229 ELSIF upper(l_referencing) = 'PC' THEN
1230 --
1231 --Checks to see if the PC is the starting point if so there
1232 --is no reason to attach an id value to the user key.
1233
1234 OPEN csr_starting_point;
1235 FETCH csr_starting_point INTO l_starting_bool;
1236 IF csr_starting_point%NOTFOUND THEN
1237 --
1238 l_fatal_error_message := 'No starting point value has been ' ||
1239 'found in the descriptors table. ';
1240 RAISE e_fatal_error;
1241 ELSIF l_starting_bool = 'YES' THEN
1242 --
1243 l_length := LENGTHB(':');
1244 l_length2 := LENGTHB(l_key_pval_string);
1245 l_key_pval_string := SUBSTRB(l_key_pval_string, l_length + 1);
1246 ELSE
1247 --The user key has has their ID attached to it to make it unique
1248 l_key_pval_string := 'PVAL001' || l_key_pval_string;
1249 --
1250 END IF;
1251 CLOSE csr_starting_point;
1252
1253 --
1254 ELSIF upper(l_referencing) = 'CP' THEN
1255 --
1256 l_length := LENGTHB(':');
1257 l_length2 := LENGTHB(l_key_pval_string);
1258 l_key_pval_string := SUBSTRB(l_key_pval_string, l_length + 1);
1259 --
1260 ELSE
1261 l_fatal_error_message := 'Referencing value is not of the correct ' ||
1262 'format PC / CP';
1263 RAISE e_fatal_error;
1264 END IF;
1265 CLOSE csr_referencing;
1266
1267 --insert the string in to R_USER_KEY_PVAL
1268 hr_du_utility.message('INFO', 'l_key_pval_string : ' ||
1269 l_key_pval_string, 25);
1270
1271 g_values_table(l_counter).r_user_key_pval := l_key_pval_string;
1272 --
1273 END LOOP;
1274
1275 --
1276 hr_du_utility.message('ROUT',
1277 'exit:hr_du_do_datapump.create_user_key_string', 30);
1278 --
1279
1280 EXCEPTION
1281 WHEN e_fatal_error THEN
1282 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.create_user_key_string'
1283 ,l_fatal_error_message, 'R');
1284 RAISE;
1285 WHEN OTHERS THEN
1286 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.create_user_key_string'
1287 ,'(none)', 'R');
1288 RAISE;
1289 --
1290 END CREATE_USER_KEY_STRING;
1291
1292
1293 -- ------------------------ CREATE_P_STRINGS ----------------------------
1294 -- Description: Creates two strings, one of column names R_INSERT_STRING.
1295 -- And the other of PVAL's R_PVAL_STRING, they are then placed into a
1296 -- PL/SQL table in their appropriate row depending on their API
1297 --
1298 -- Input Parameters
1299 --
1300 -- p_api_module_id - Identifies the api_module
1301 --
1302 -- p_upload_id - Identifies the correct upload record
1303 --
1304 -- p_array_pos - The position within the global table
1305 --
1306 -- ------------------------------------------------------------------------
1310 IS
1307 PROCEDURE CREATE_P_STRINGS(p_api_module_id IN NUMBER,
1308 p_upload_id IN NUMBER,
1309 p_array_pos IN NUMBER)
1311
1312 --Returns the line_id for the column names in HR_DU_UPLOAD_LINES
1313 CURSOR csr_line_id IS
1314 SELECT line.UPLOAD_LINE_ID
1315 FROM hr_du_upload_headers head,
1316 hr_du_upload_lines line
1317 WHERE head.upload_id = p_upload_id
1318 AND head.api_module_id = p_api_module_id
1319 AND line.upload_header_id = head.upload_header_id
1320 AND line.LINE_TYPE = 'C';
1321
1322 e_fatal_error EXCEPTION;
1323 l_fatal_error_message VARCHAR2(2000);
1324 l_pval_string VARCHAR2(32767) := null ;
1325 l_pvalues_string VARCHAR2(32767) := null ;
1326 l_current_pval VARCHAR2(10);
1327 l_line_id NUMBER(15);
1328 l_pval_field VARCHAR2(50);
1329 l_mapped_name VARCHAR2(50);
1330 l_length NUMBER;
1331 l_string_length NUMBER;
1332
1333 BEGIN
1334 --
1335 hr_du_utility.message('ROUT',
1336 'entry:hr_du_do_datapump.create_p_strings', 5);
1337 hr_du_utility.message('PARA', '(p_api_module_id - ' || p_api_module_id ||
1338 ')(p_upload_id - ' || p_upload_id ||
1339 ')(p_array_pos - ' || p_array_pos || ')' ,
1340 10);
1341 --
1342 OPEN csr_line_id;
1343 FETCH csr_line_id INTO l_line_id;
1344 IF csr_line_id%NOTFOUND THEN
1345 l_fatal_error_message := 'No appropriate column title row exists in '||
1346 'the HR_DU_UPLOAD_LINES for the api_module '||
1347 'passed';
1348 RAISE e_fatal_error;
1349 END IF;
1350 CLOSE csr_line_id;
1351
1352 STORE_COLUMN_HEADINGS (l_line_id);
1353
1354 --loops around all the columns within the upload_line
1355 FOR i IN 1..230 LOOP
1356 --
1357 l_current_pval := LPAD(i,3,'0');
1358 l_current_pval := 'PVAL' || l_current_pval;
1359 l_pval_field := g_column_headings(i);
1360
1361 l_mapped_name := general_referencing_column(l_pval_field,
1362 p_api_module_id, 'D');
1363
1364 IF l_mapped_name IS NOT NULL THEN
1365 l_pvalues_string := l_pvalues_string || l_mapped_name || ',' ;
1366 l_pval_string := l_pval_string || l_current_pval || ',' ;
1367 END IF;
1368 --
1369 END LOOP;
1370
1371 l_length := LENGTHB(',');
1372 l_string_length := LENGTHB(l_pvalues_string);
1373 IF l_string_length > 0 THEN
1374 l_pvalues_string := SUBSTRB(l_pvalues_string,1,
1375 (l_string_length - l_length));
1376 l_string_length := LENGTHB(l_pval_string);
1377 l_pval_string := SUBSTRB(l_pval_string,1,
1378 (l_string_length - l_length));
1379 END IF;
1380
1381 g_values_table(p_array_pos).r_insert_string := l_pvalues_string;
1382 g_values_table(p_array_pos).r_PVAL_string := l_pval_string;
1383
1384 --
1385 hr_du_utility.message('ROUT',
1386 'exit:hr_du_do_datapump.create_p_strings', 15);
1387 --
1388
1389 EXCEPTION
1390 WHEN e_fatal_error THEN
1391 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.create_p_strings'
1392 ,l_fatal_error_message, 'R');
1393 RAISE;
1394 WHEN OTHERS THEN
1395 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.create_p_strings'
1396 ,'(none)', 'R');
1397 RAISE;
1398 --
1399 END CREATE_P_STRINGS;
1400
1401
1402
1403 -- ------------------------- PRODUCE_TABLE ---------------------------------
1404 -- Description: The PL/SQL table is first created here and fill with the
1405 -- initial values of api_module_ids and their corresponding upload_header_ids
1406 --
1407 -- Input Parameters
1408 -- p_upload_id - The upload id to associate the procedure with
1409 -- correct table
1410 --
1411 -- ------------------------------------------------------------------------
1412 PROCEDURE PRODUCE_TABLE(p_upload_id IN NUMBER)
1413 IS
1414
1415 CURSOR csr_apis IS
1416 SELECT api.api_module_id, des1.upload_header_id
1417 FROM hr_du_descriptors des2,
1418 hr_api_modules api,
1419 hr_du_descriptors des1
1420 WHERE des2.upload_id = p_upload_id
1421 AND upper(des2.descriptor) = 'PROCESS ORDER'
1422 AND des2.DESCRIPTOR_TYPE = 'D'
1423 AND upper(api.module_name) = upper(des1.VALUE)
1424 AND des1.DESCRIPTOR_TYPE = 'D'
1425 AND des1.upload_header_id = des2.upload_header_id
1426 AND upper(api.module_name) = upper(des1.VALUE)
1427 ORDER BY des2.value;
1428
1429 --exception to raise
1430 e_fatal_error EXCEPTION;
1431 --string to input the error message
1432 l_fatal_error_message VARCHAR2(2000);
1433 l_api_module_id NUMBER;
1434 l_counter NUMBER :=1;
1435 l_upload_header_id NUMBER;
1436
1437 BEGIN
1438 --
1439 hr_du_utility.message('ROUT',
1440 'entry:hr_du_do_datapump.produce_table', 5);
1441 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')' , 10);
1442 --
1443 OPEN csr_apis;
1444 LOOP
1445 --
1446 FETCH csr_apis INTO l_api_module_id, l_upload_header_id;
1447 EXIT WHEN csr_apis%NOTFOUND;
1448 g_values_table(l_counter).r_api_id := l_api_module_id;
1452 --
1449 g_values_table(l_counter).r_upload_header_id := l_upload_header_id;
1450 create_p_strings(l_api_module_id, p_upload_id, l_counter);
1451 l_counter := l_counter + 1;
1453 END LOOP;
1454 IF l_counter = 1 THEN
1455 l_fatal_error_message := 'No Data found to produce the API table ' ||
1456 'with the upload_id provided ' ||
1457 '( p_upload_id : ' || p_upload_id || ' )';
1458 RAISE e_fatal_error;
1459 END IF;
1460 CLOSE csr_apis;
1461
1462 --
1463 hr_du_utility.message('ROUT',
1464 'exit:hr_du_do_datapump.produce_table', 15);
1465 --
1466
1467 EXCEPTION
1468 WHEN e_fatal_error THEN
1469 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.produce_table'
1470 ,l_fatal_error_message, 'R');
1471 RAISE;
1472 WHEN OTHERS THEN
1473 hr_du_utility.error(SQLCODE, 'hr_du_do_datapump.produce_table',
1474 '(none)', 'R');
1475 RAISE;
1476 --
1477 END PRODUCE_TABLE;
1478
1479
1480 -- ------------------------- REFERENCING_COLUMNS -----------------------
1481 -- Description: Builds up the strings L_STRING_APIS, L_API_PVALS and
1482 -- L_GENERIC_PVAL in the PL/SQL table. Loops around the column headings
1483 -- for each API and checks them against the cursor constraints, if they
1484 -- meet the requirements then they are placed into the strings.
1485 --
1486 -- Input Parameters
1487 --
1488 -- p_line_id - Identifies the upload_line_id for the column
1489 -- heading line in question.
1490 --
1491 -- p_api_module_id - Holds the id of the API to which the column
1492 -- heading is related to in the HR_API_MODULES table.
1493 --
1494 -- p_upload_id - Holds the upload id to destinguish between
1495 -- entries in the HR_DU_UPLOADS table.
1496 --
1497 -- p_array_pos - the array position in the PL/SQL table that is
1498 -- currently being used.
1499 --
1500 -- ------------------------------------------------------------------------
1501 PROCEDURE REFERENCING_COLUMNS(p_line_id IN NUMBER,
1502 p_api_module_id IN NUMBER,
1503 p_upload_id IN NUMBER,
1504 p_array_pos IN NUMBER)
1505 IS
1506
1507 l_string_apis VARCHAR2(100);
1508 l_api_PVALS VARCHAR2(300);
1509 l_generic_pval VARCHAR2(30);
1510 l_current_pval VARCHAR2(10);
1511 l_inner_pval VARCHAR2(10);
1512 l_pval_field VARCHAR2(50);
1513 l_inner_field VARCHAR2(50);
1514 l_parent_api_module_id NUMBER;
1515 l_parent_table VARCHAR2(35);
1516 l_length NUMBER;
1517 l_string_length NUMBER;
1518
1519 --Checks the column name to see if it has the properties of holding the
1520 --calling api_module's id (parent's id) in that column.
1521
1522 CURSOR csr_parent_api_id IS
1523 SELECT parent_api_module_id
1524 FROM hr_du_column_mappings
1525 WHERE mapping_type = 'D'
1526 AND parent_api_module_id IS NOT null
1527 AND column_name = l_pval_field;
1528
1529 --Check to see if the column heading has the properties of a generic
1530 --column. Due to some API's have two columns specifing both a column
1531 --to store the api_module id and the line id.
1532
1533 CURSOR csr_parent_table_column IS
1534 SELECT parent_table
1535 FROM hr_du_column_mappings
1536 WHERE mapping_type = 'D'
1537 AND parent_table is not null
1538 AND column_name = l_pval_field;
1539
1540 BEGIN
1541 --
1542 hr_du_utility.message('ROUT',
1543 'entry:hr_du_do_datapump.referencing_columns',
1544 5);
1545 hr_du_utility.message('PARA', '(p_line_id - ' || p_line_id ||
1546 ')(p_api_module_id - ' || p_api_module_id ||
1547 ')(p_upload_id - ' || p_upload_id ||
1548 ')(p_array_pos - ' || p_array_pos || ')'
1549 , 10);
1550 --
1551 l_string_apis := null;
1552 l_api_PVALS := null;
1553 l_generic_pval := null;
1554
1555 --cache the column headings
1556 STORE_COLUMN_HEADINGS (p_line_id);
1557
1558
1559 --loops around all the column headings within the upload_line
1560 FOR i IN 1..230 LOOP
1561 --
1562 l_current_pval := LPAD(i,3,'0');
1563 l_current_pval := 'PVAL' || l_current_pval;
1564 --fetch the heading stored within the specified upload line
1565 l_pval_field := g_column_headings(i);
1566 OPEN csr_parent_api_id;
1567 --
1568 FETCH csr_parent_api_id INTO l_parent_api_module_id;
1569 IF csr_parent_api_id%NOTFOUND THEN
1570 --no match on normal case so trying generic case
1571 OPEN csr_parent_table_column;
1572 --
1573 FETCH csr_parent_table_column INTO l_parent_table;
1574 IF csr_parent_table_column%FOUND THEN
1575 --loop through the column headings again to search for the
1576 --position in the line of where the api_module id will be stored
1577 hr_du_utility.message('INFO', l_parent_table, 15);
1578 FOR j IN 1..230 LOOP
1579 --
1580 l_inner_pval := LPAD(j,3,'0');
1581 l_inner_pval := 'PVAL' || l_inner_pval;
1582
1583 l_inner_field := g_column_headings(j);
1584
1588 --found the exact position in the line where the api_module id
1585 hr_du_utility.message('INFO', l_inner_field, 20);
1586
1587 IF l_parent_table = l_inner_field THEN
1589 --from the calling table will be stored (l_inner_pval).
1590 --
1591 --storing a null in l_string_apis will signal later on
1592 --that a generic column has been found
1593 l_string_apis := l_string_apis || null || ',';
1594 l_api_PVALS := l_api_PVALS || l_current_pval || ',';
1595 l_generic_pval := l_generic_pval || l_inner_pval || ',';
1596 EXIT;
1597 --
1598 END IF;
1599 --
1600 END LOOP;
1601 END IF;
1602 --
1603 CLOSE csr_parent_table_column;
1604 --
1605 ELSE
1606 --
1607 hr_du_utility.message('INFO', l_parent_api_module_id , 25);
1608 l_string_apis := l_string_apis || l_parent_api_module_id || ',';
1609 l_api_PVALS := l_api_PVALS || l_current_pval || ',';
1610 l_generic_pval := l_generic_pval || null || ',';
1611 --
1612 END IF;
1613 --
1614 CLOSE csr_parent_api_id;
1615 END LOOP;
1616
1617 l_length := LENGTHB(',');
1618 l_string_length := LENGTHB(l_string_apis);
1619 IF l_string_length > 0 THEN
1620 l_string_apis := SUBSTRB(l_string_apis,1,
1621 (l_string_length - l_length));
1622 l_string_length := LENGTHB(l_api_PVALS);
1623 l_api_PVALS := SUBSTRB(l_api_PVALS,1,
1624 (l_string_length - l_length));
1625 l_string_length := LENGTHB(l_generic_pval);
1626 l_generic_pval := SUBSTRB(l_generic_pval,1,
1627 (l_string_length - l_length));
1628 END IF;
1629
1630 --The commas are not removed from the strings for this causes errors
1631 --later on in the function PROCESS_LINE
1632 g_values_table(p_array_pos).r_parent_api_module_number := l_string_apis;
1633 g_values_table(p_array_pos).r_pval_parent_line_id := l_api_PVALS;
1634 g_values_table(p_array_pos).r_pval_api_module_number := l_generic_pval;
1635
1636 hr_du_utility.message('INFO', l_api_PVALS , 35);
1637 hr_du_utility.message('INFO', l_string_apis , 30);
1638 hr_du_utility.message('INFO', l_generic_pval , 40);
1639
1640 --
1641 hr_du_utility.message('ROUT',
1642 'exit:hr_du_do_datapump.referencing_columns',
1643 45);
1644 --
1645 EXCEPTION
1646 WHEN OTHERS THEN
1647 hr_du_utility.error(SQLCODE,
1648 'hr_du_do_datapump.referencing_columns',
1649 '(none)', 'R');
1650 RAISE;
1651 --
1652 END REFERENCING_COLUMNS;
1653
1654
1655
1656 -- --------------------- CREATE_REFERENCING_STRINGS -----------------------
1657 -- Description: Simple procedure that finds the line id which holds
1658 -- the column headings, it then calls the relevant procedures.
1659 --
1660 -- Input Parameters
1661 -- p_upload_id - Identifies the upload over uploads simular
1662 -- to itself
1663 --
1664 -- p_table_position - The row with in the PL/SQL table
1665 --
1666 -- p_api_module_id - Identifies the api module
1667 -- ------------------------------------------------------------------------
1668 PROCEDURE CREATE_REFERENCING_STRINGS(p_upload_id IN VARCHAR2,
1669 p_table_position IN NUMBER,
1670 p_api_module_id IN NUMBER)
1671 IS
1672
1673 CURSOR csr_line_id IS
1674 SELECT line.UPLOAD_LINE_ID
1675 FROM hr_du_upload_headers head,
1676 hr_du_upload_lines line
1677 WHERE head.upload_id = p_upload_id
1678 AND head.api_module_id = p_api_module_id
1679 AND line.upload_header_id = head.upload_header_id
1680 AND line.LINE_TYPE = 'C';
1681
1682 e_fatal_error EXCEPTION;
1683 l_fatal_error_message VARCHAR2(2000);
1684 l_line_id NUMBER;
1685
1686 BEGIN
1687
1688 --
1689 hr_du_utility.message('ROUT',
1690 'entry:hr_du_do_datapump.create_referencing_strings', 5);
1691 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id ||
1692 ')(p_table_position - ' || p_table_position ||
1693 ')(p_api_module_id - ' || p_api_module_id || ')'
1694 , 10);
1695 --
1696
1697 OPEN csr_line_id;
1698 FETCH csr_line_id INTO l_line_id;
1699 IF csr_line_id%NOTFOUND THEN
1700 l_fatal_error_message := 'No appropriate column title row exists in '||
1701 'the HR_DU_UPLOAD_LINES for the api_module passed';
1702 RAISE e_fatal_error;
1703 END IF;
1704 CLOSE csr_line_id;
1705
1706 hr_du_utility.message('INFO', 'l_line_id : ' || l_line_id, 15);
1707
1708 REFERENCING_COLUMNS(l_line_id, p_api_module_id, p_upload_id,
1709 p_table_position);
1710
1711
1712 --
1713 hr_du_utility.message('ROUT',
1714 'exit:hr_du_do_datapump.create_referencing_strings', 30);
1715 --
1716
1717 EXCEPTION
1718 WHEN e_fatal_error THEN
1719 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.main'
1723 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.create_referencing_strings'
1720 ,l_fatal_error_message, 'R');
1721 RAISE;
1722 WHEN OTHERS THEN
1724 ,'(none)', 'R');
1725 RAISE;
1726 --
1727 END CREATE_REFERENCING_STRINGS;
1728
1729
1730 -- ------------------------- VALIDATE -----------------------------------
1731 -- Description:
1732 --
1733 -- Input Parameters
1734 -- p_upload_id - The upload id to associate the procedure with
1735 -- correct table
1736 --
1737 -- ------------------------------------------------------------------------
1738 PROCEDURE VALIDATE(p_upload_id IN NUMBER) IS
1739
1740 CURSOR csr_validate_data IS
1741 SELECT line.UPLOAD_LINE_ID
1742 FROM hr_du_upload_headers head,
1743 hr_du_upload_lines line
1744 WHERE head.upload_id = p_upload_id
1745 AND line.upload_header_id = head.upload_header_id
1746 AND line.REFERENCE_TYPE = 'CP';
1747
1748 e_fatal_error EXCEPTION;
1749 l_fatal_error_message VARCHAR2(2000);
1750 l_upload_line_id NUMBER;
1751
1752 BEGIN
1753 --
1754 hr_du_utility.message('ROUT','entry:hr_du_do_datapump.validate', 5);
1755 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')' , 10);
1756 --
1757
1758 OPEN csr_validate_data;
1759 FETCH csr_validate_data INTO l_upload_line_id;
1760 IF csr_validate_data%NOTFOUND THEN
1761 l_fatal_error_message := 'Data is in an incorrect format to be ' ||
1762 'taken into Data Pump. There are no Child '||
1763 '- Parent references at all.';
1764 RAISE e_fatal_error;
1765 END IF;
1766 CLOSE csr_validate_data;
1767
1768
1769 --
1770 hr_du_utility.message('ROUT','exit:hr_du_do_datapump.validate', 15);
1771 --
1772
1773 --
1774 EXCEPTION
1775 WHEN e_fatal_error THEN
1776 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.validate'
1777 ,l_fatal_error_message, 'R');
1778 RAISE;
1779 WHEN OTHERS THEN
1780 hr_du_utility.error(SQLCODE, 'hr_du_do_datapump.validate',
1781 '(none)', 'R');
1782 RAISE;
1783 --
1784 END VALIDATE;
1785
1786
1787 -- ------------------------- ROLLBACK -----------------------------------
1788 -- Description: This procedure is called when an error has occured so that
1789 -- the database tables can be cleaned up to restart the Data Output module
1790 -- again
1791 --
1792 -- Input Parameters
1793 -- p_upload_id - The upload id to associate the procedure with
1794 -- correct table
1795 --
1796 -- ------------------------------------------------------------------------
1797 PROCEDURE ROLLBACK(p_upload_id IN NUMBER) IS
1798
1799 l_temp VARCHAR2(20);
1800 l_batch_id NUMBER;
1801 l_batch_line NUMBER;
1802 l_line_status VARCHAR2(1);
1803
1804 CURSOR csr_batch_lines IS
1805 SELECT batch_line_id, line_status
1806 FROM hr_pump_batch_lines
1807 WHERE batch_id = l_batch_id;
1808
1809 BEGIN
1810 --
1811 hr_du_utility.message('ROUT','entry:hr_du_do_datapump.rollback', 5);
1812 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')' , 10);
1813 --
1814
1815
1816 UPDATE hr_du_upload_lines
1817 SET status = 'NS'
1818 WHERE UPLOAD_HEADER_ID IN (SELECT upload_header_id
1819 FROM hr_du_upload_headers
1820 WHERE upload_id = p_upload_id)
1821 AND status = 'S';
1822
1823 SELECT BATCH_ID
1824 INTO l_batch_id
1825 FROM hr_du_uploads
1826 WHERE upload_id = p_upload_id;
1827
1828 --deletes the header from the batch exception
1829 DELETE FROM hr_pump_batch_exceptions
1830 WHERE source_id = l_batch_id
1831 AND source_type = 'BATCH_HEADER';
1832
1833 OPEN csr_batch_lines;
1834 LOOP
1835 --
1836 FETCH csr_batch_lines INTO l_batch_line, l_line_status;
1837 EXIT WHEN csr_batch_lines%NOTFOUND;
1838
1839 --act upon the previous statement to delete approiate exceptions
1840 if l_line_status = 'E' Then
1841 DELETE FROM hr_pump_batch_exceptions
1842 WHERE source_id = l_batch_line
1843 AND source_type = 'BATCH_LINE';
1844 END IF;
1845 --insert extra code here to remove data from user keys in the future
1846 DELETE FROM HR_PUMP_BATCH_LINE_USER_KEYS
1847 where BATCH_LINE_ID = l_batch_line;
1848 --
1849 END LOOP;
1850 CLOSE csr_batch_lines;
1851
1852 DELETE FROM hr_pump_batch_lines
1853 WHERE batch_id = l_batch_id;
1854
1855 --deletes the data held within the pump ranges
1856 DELETE FROM hr_pump_ranges
1857 where batch_id = l_batch_id;
1858
1859 --deletes the data held within the pump requsts
1860 DELETE FROM hr_pump_requests
1861 WHERE BATCH_ID = l_batch_id;
1862
1863 --include this statement here so that no foreign keys are violated
1864 UPDATE hr_du_uploads
1865 SET batch_id = null
1866 WHERE upload_id = p_upload_id;
1867
1868 -- deletes the info in the batch header
1869 DELETE FROM hr_pump_batch_headers
1873 --
1870 WHERE BATCH_ID = l_batch_id;
1871 Commit;
1872
1874 hr_du_utility.message('ROUT','exit:hr_du_do_datapump.rollback', 15);
1875 --
1876
1877 --
1878 EXCEPTION
1879 WHEN OTHERS THEN
1880 hr_du_utility.error(SQLCODE, 'hr_du_do_datapump.rollback',
1881 '(none)', 'R');
1882 RAISE;
1883 --
1884 END ROLLBACK;
1885
1886
1887 -- -------------------------------- MAIN ----------------------------------
1888 -- Description: This procedure controls the flow of both
1889 -- procedure and function calls to produce the output to Data Pump
1890 --
1891 -- Input Parameters
1892 -- p_upload_id - HR_DU_UPLOAD_ID to be used
1893 --
1894 -- ------------------------------------------------------------------------
1895 PROCEDURE MAIN(p_upload_id IN NUMBER)
1896 IS
1897
1898 e_fatal_error EXCEPTION;
1899 l_fatal_error_message VARCHAR2(2000);
1900 l_business_group VARCHAR2(2000);
1901 l_table_size NUMBER;
1902 l_api_name VARCHAR2(100);
1903 l_api_name_thirty VARCHAR2(100);
1904 l_process_order NUMBER;
1905 l_batch_id NUMBER;
1906 l_batch_name VARCHAR2(2000);
1907 l_upload_line_id NUMBER;
1908 l_upload_header_id NUMBER;
1909 l_string_length NUMBER;
1910 l_api_module_id NUMBER;
1911 l_string_api VARCHAR2(2000);
1912 l_upload_id VARCHAR2(2000);
1913 l_length NUMBER;
1914 l_cursor_handle INT;
1915 l_rows_processed INT;
1916 l_chunk_size_master NUMBER;
1917 l_chunk_size_slave NUMBER;
1918 l_pump_batch_line_id NUMBER;
1919
1920
1921 --This cursor extracts the batch name from the descriptors table
1922 CURSOR csr_batch_name IS
1923 SELECT VALUE
1924 FROM hr_du_descriptors
1925 WHERE upper(DESCRIPTOR) = 'BATCH NAME'
1926 AND UPLOAD_ID = p_upload_id;
1927
1928 --Returns the upload line and header to be placed in Pump lines
1929 CURSOR csr_upload_line_id IS
1930 SELECT line.upload_line_id, line.upload_header_id
1931 FROM hr_du_upload_lines line,
1932 hr_du_upload_headers head
1933 WHERE head.upload_id = p_upload_id
1934 AND head.api_module_id = l_api_module_id
1935 AND line.upload_header_id = head.upload_header_id
1936 AND line.status = 'NS'
1937 AND line.reference_type = 'CP'
1938 AND line.line_type = 'D';
1939
1940 BEGIN
1941
1942 --
1943 hr_du_utility.message('ROUT','entry:hr_du_do_datapump.main', 5);
1944 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')' , 10);
1945 --
1946
1947 SET_STATUS(p_upload_id);
1948
1949 -- clear globals
1950 g_values_table.DELETE;
1951 g_column_headings.DELETE;
1952 g_user_key_table.DELETE;
1953
1954
1955 --Places the data into the PL/SQL table in the correct processing order
1956 PRODUCE_TABLE(p_upload_id);
1957
1958 --Returns the overall business group related to the HR_DU_UPLOAD.ID
1959 l_business_group := EXTRACT_BUSINESS_GROUP(p_upload_id);
1960 hr_du_utility.message('INFO','l_business_group : ' || l_business_group, 15);
1961
1962
1963 OPEN csr_batch_name;
1964 FETCH csr_batch_name INTO l_upload_id;
1965 IF csr_batch_name%NOTFOUND THEN
1966 l_fatal_error_message := 'Error BATCH NAME value not found in file';
1967 RAISE e_fatal_error;
1968 ELSE
1969 l_batch_name := SUBSTRB(l_upload_id,1,80);
1970 END IF;
1971 CLOSE csr_batch_name;
1972
1973
1974
1975 --setting the batch name glueing the Batch Name, upload_id and the business
1976 --group
1977 l_length := LENGTHB(p_upload_id);
1978 IF l_length > 5 THEN
1979 l_upload_id := SUBSTRB(p_upload_id, (l_length - 4) ,l_length);
1980 ELSE
1981 l_upload_id := p_upload_id;
1982 END IF;
1983
1984
1985
1986 l_batch_name := l_batch_name || '-' || l_upload_id;
1987 l_batch_name := SUBSTRB(l_batch_name, 1, 80);
1988
1989 --find out the size of the PL/SQL table
1990 l_table_size := g_values_table.count;
1991
1992 --create batch header storing the id
1993 l_batch_id := hr_pump_utils.create_batch_header
1994 (l_batch_name,l_business_group);
1995
1996 -- set fnd_conc_global.set_req_globals
1997 -- for GL sync use
1998
1999 fnd_conc_global.set_req_globals(request_data => to_char(l_batch_id));
2000
2001
2002 hr_du_utility.message('INFO','l_batch_id : ' || l_batch_id, 20);
2003
2004
2005 UPDATE hr_du_uploads
2006 SET batch_id = l_batch_id
2007 WHERE upload_id = p_upload_id;
2011 --
2008 COMMIT;
2009
2010 FOR i IN 1..l_table_size LOOP
2012 hr_du_utility.message('INFO','api_module id : ' ||
2013 g_values_table(i).r_api_id, 25);
2014
2015 --Procedure call to fill in R_PVAL_PARENT_LINE_ID,
2016 --R_PARENT_api_module_NUMBER, R_PVAL_api_module_NUMBER
2017 CREATE_REFERENCING_STRINGS(p_upload_id, i, g_values_table(i).r_api_id);
2018
2019 hr_du_utility.message('INFO','r_parent_api_module_number : ' ||
2020 g_values_table(i).r_parent_api_module_number , 15);
2021 hr_du_utility.message('INFO','r_pval_parent_line_id : ' ||
2022 g_values_table(i).r_pval_parent_line_id , 15);
2023 hr_du_utility.message('INFO','r_pval_api_module_number : ' ||
2024 g_values_table(i).r_pval_api_module_number , 15);
2025
2026 --
2027 END LOOP;
2028
2029 --procedure call to insert the appropriate values into R_USER_KEY_PVAL
2030 CREATE_USER_KEY_STRING(p_upload_id, l_table_size);
2031
2032
2033 FOR i IN 1..l_table_size LOOP
2034 l_api_name := hr_du_dp_pc_conversion.return_field_value
2035 ('HR_API_MODULES', g_values_table(i).r_api_id,
2036 'API_MODULE_ID', 'MODULE_NAME');
2037
2038 l_api_module_id := g_values_table(i).r_api_id;
2039
2040 --this is here for the user_sequence value
2041 l_process_order := API_id_to_process_order(l_api_module_id,
2042 p_upload_id);
2043
2044 hr_du_utility.message('INFO','l_api_name : ' || l_api_name , 20);
2045 hr_du_utility.message('INFO','l_process_order : ' || l_process_order, 20);
2046
2047
2048 --Making sure that the api name is the correct length for when the Meta
2049 --Mapper has been run it's rounded to 30 characters including HRDPV_
2050
2051 l_length := LENGTHB(l_api_name);
2052 IF l_length > 24 THEN
2053 l_api_name_thirty := SUBSTRB(l_api_name, 1 , 24);
2054 ELSE
2055 l_api_name_thirty := l_api_name;
2056 END IF;
2057
2058 l_chunk_size_master := hr_du_utility.chunk_size;
2059 l_chunk_size_slave := l_chunk_size_master;
2060
2061
2062 OPEN csr_upload_line_id;
2063 LOOP
2064 BEGIN
2065 FETCH csr_upload_line_id INTO l_upload_line_id, l_upload_header_id;
2066 IF csr_upload_line_id%NOTFOUND THEN
2067 --No lines left to process so EXIT's out of the loop
2068 EXIT;
2069 END IF;
2070 END;
2071
2072 Select hr_pump_batch_lines_s.nextval
2073 INTO l_pump_batch_line_id
2074 FROM dual;
2075
2076 --change the status of the PC row to show we're processing this
2077 UPDATE hr_du_upload_lines
2078 SET status = 'S'
2079 WHERE upload_line_id = l_upload_line_id;
2080
2081 --If statements inserted here to call the appropriate procedure within
2082 --HR_DU_DO_ENTITIES so that the insert statements are build up correctly
2083
2084 hr_du_utility.message('INFO',upper(l_api_name), 20);
2085
2086 IF upper(l_api_name) = 'CREATE_US_EMPLOYEE' OR
2087 upper(l_api_name) = 'CREATE_GB_EMPLOYEE' THEN
2088 hr_du_do_entities.CREATE_DEFAULT_EMPLOYEE(g_values_table(i), p_upload_id,
2089 l_batch_id, l_api_module_id, l_process_order,
2090 l_upload_line_id, l_api_name_thirty, l_pump_batch_line_id);
2091 ELSIF upper(l_api_name) = 'UPDATE_EMP_ASG_CRITERIA' THEN
2092 hr_du_do_entities.UPDATE_EMP_ASG_CRITERIA(g_values_table(i),
2093 p_upload_id, l_batch_id, l_api_module_id,
2094 l_process_order, l_upload_line_id, l_api_name_thirty,
2095 l_pump_batch_line_id);
2096 ELSIF g_values_table(i).r_user_key_pval IS NULL THEN
2097 hr_du_do_entities.DEFAULT_API_NULL(g_values_table(i), p_upload_id,
2098 l_batch_id, l_api_module_id, l_process_order,
2099 l_upload_line_id, l_api_name_thirty,l_pump_batch_line_id);
2100 ELSE
2101 hr_du_do_entities.DEFAULT_API(g_values_table(i), p_upload_id,
2102 l_batch_id, l_api_module_id, l_process_order,
2103 l_upload_line_id, l_api_name_thirty,l_pump_batch_line_id);
2104 END IF;
2105
2106 --change the status of the PC row to show we're processing this
2107 --and connect the HR_DU_UPLOAD_LINE to the HR_PUMP_BATCH_LINE
2108 UPDATE hr_du_upload_lines
2109 SET status = 'C',
2110 batch_line_id = l_pump_batch_line_id
2111 WHERE upload_line_id = l_upload_line_id;
2112
2113 --statement to commit every <CHUNK_SIZE>
2114 IF l_chunk_size_slave = 0 THEN
2115 COMMIT;
2116 l_chunk_size_slave := l_chunk_size_master;
2117 ELSE
2118 l_chunk_size_slave := l_chunk_size_slave - 1;
2119 END IF;
2120
2121 END LOOP;
2122 CLOSE csr_upload_line_id;
2123 COMMIT;
2124 --
2125 END LOOP;
2126
2127 --
2128 hr_du_utility.message('ROUT','exit:hr_du_do_datapump.main', 20);
2129 --
2130
2131 EXCEPTION
2132 WHEN e_fatal_error THEN
2133 hr_du_utility.error(SQLCODE,'hr_du_do_datapump.main'
2134 ,l_fatal_error_message, 'R');
2135 RAISE;
2136 WHEN OTHERS THEN
2137 hr_du_utility.error(SQLCODE, 'hr_du_do_datapump.main','(none)', 'R');
2138 RAISE;
2139 --
2140 END MAIN;
2141
2142
2143 END HR_DU_DO_DATAPUMP;