1 PACKAGE BODY HR_DU_DO_ENTITIES AS
2 /* $Header: perduent.pkb 120.0 2005/05/31 17:20:24 appldev noship $ */
3
4
5
6 -- --------------------------- CREATE_DEFAULT_EMPLOYEE --------------------------
7 -- Description: It has been separated for the Employee API creates a
8 -- USER_KEY for assignment it adds 'ASG' to the end of the user key. Apart
9 -- from that its practically identical to DEFALULT_API
10 --
11 -- Input Parameters
12 -- p_upload_id - HR_DU_UPLOAD_ID to be used
13 --
14 -- p_batch_id - PUMP_BATCH_HEADER_ID
15 --
16 -- p_api_module_id - API_MODULE_ID
17 --
18 -- p_process_order - Number to tell data pump the order in which
19 -- to process the record
20 --
21 -- p_upload_line_id - ID of the line in the HR_DU_UPLOAD_LINES
22 -- table that holds all the data to be
23 -- pushed into the HR_PUMP_BATCH_LINES
24 --
25 -- p_pump_batch_line_id - Identifies the Pump batch line to be used
26 -- ------------------------------------------------------------------------
27 PROCEDURE CREATE_DEFAULT_EMPLOYEE(
28 p_values_table IN hr_du_do_datapump.R_INSERT_STATEMENT_TYPE
29 ,p_upload_id IN NUMBER
30 ,p_batch_id IN NUMBER
31 ,p_api_module_id IN NUMBER
32 ,p_process_order IN NUMBER
33 ,p_upload_line_id IN NUMBER
34 ,p_api_name IN VARCHAR2
35 ,p_pump_batch_line_id IN NUMBER)
36 IS
37
38 e_fatal_error EXCEPTION;
39 l_fatal_error_message VARCHAR2(2000);
40 l_parent_user_key VARCHAR2(2000);
41 l_foreign_user_key VARCHAR2(50);
42 l_parent_user_key_2 VARCHAR2(2000);
43 l_foreign_user_key_2 VARCHAR2(50);
44 l_row_id NUMBER;
45 l_number_refs NUMBER;
46 l_pval_parent_line_id VARCHAR2(2000);
47 l_parent_api_module_number VARCHAR2(2000);
48 l_pval_api_module_number VARCHAR2(2000);
49 l_temp_id VARCHAR2(2000);
50 l_temp_api_module VARCHAR2(2000);
51 l_insert_statement VARCHAR2(32767);
52 l_cursor_handle INT;
53 l_rows_processed INT;
54
55 CURSOR csr_line_id IS
56 SELECT PVAl001
57 FROM hr_du_upload_lines
58 WHERE UPLOAD_LINE_ID = p_upload_line_id;
59
60
61 BEGIN
62 --
63 hr_du_utility.message('ROUT','entry:hr_du_do_entities.create_default_employee',
64 5);
65 hr_du_utility.message('PARA', '(p_values_table - ** Record Structure** ' ||
66 ')(p_upload_id - ' || p_upload_id ||
67 ')(p_batch_id - ' || p_batch_id ||
68 ')(p_api_module_id - ' || p_api_module_id ||
69 ')(p_process_order - ' || p_process_order ||
70 ')(p_upload_line_id - ' || p_upload_line_id || ')'
71 , 10);
72 --
73 --Statement extracts the ID number for the particular upload_line_id
74 --that was passed in.
75
76 hr_du_utility.message('INFO','p_upload_line_id : ' || p_upload_line_id, 15);
77
78 OPEN csr_line_id;
79 --
80 FETCH csr_line_id INTO l_row_id;
81 IF csr_line_id%NOTFOUND THEN
82 l_fatal_error_message := ' Unable to retrieve the ID';
83 RAISE e_fatal_error;
84 END IF;
85 --
86 CLOSE csr_line_id;
87
88 l_parent_user_key := hr_du_do_datapump.RETURN_CREATED_USER_KEY_2(l_row_id,
89 p_values_table.r_api_id, p_upload_line_id,
90 l_foreign_user_key);
91
92 hr_du_utility.message('INFO','l_row_id : ' || l_row_id , 25);
93 hr_du_utility.message('INFO','l_parent_user_key : ' || l_parent_user_key , 30);
94 hr_du_utility.message('INFO','l_foreign_user_key : '||l_foreign_user_key , 35);
95
96 hr_du_di_insert.g_current_delimiter := ',';
97
98 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
99 p_values_table.r_pval_parent_line_id);
100
101 -- *********************************************************************************************************************
102 /*
103 l_number_refs := hr_du_di_insert.WORDS_ON_LINE(
104 p_values_table.r_pval_parent_line_id);
105
106 hr_du_utility.message('INFO','r_pval_parent_line_id : ' ||
107 p_values_table.r_pval_parent_line_id , 40);
108 hr_du_utility.message('INFO','l_number_refs : ' || l_number_refs , 45);
109
110 --check to see if this api_module has any columns that may contain data to
111 --indicate that it has been called by another api_module
112
113 IF l_number_refs > 0 THEN
114 FOR j IN 1..l_number_refs LOOP
115 --values must be set to null here or else floating API's with no
116 --references will take the last values in the loop
117 l_parent_user_key_2 := null;
118 l_foreign_user_key_2 := null;
119
120 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
121 p_values_table.r_pval_parent_line_id );
122
123 l_pval_parent_line_id := hr_du_di_insert.Return_Word(
124 p_values_table.r_pval_parent_line_id , j);
125
126
127 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
128 p_values_table.r_parent_api_module_number);
129
130 l_parent_api_module_number := hr_du_di_insert.Return_Word(
131 p_values_table.r_parent_api_module_number , j);
132
133 hr_du_utility.message('INFO','l_pval_parent_line_id : ' ||
134 l_pval_parent_line_id , 50);
135 hr_du_utility.message('INFO','l_parent_api_module_number : ' ||
136 l_parent_api_module_number , 55);
137
138
139 --check to see if referencing column is a generic reference. If null
140 --then it is
141 IF l_parent_api_module_number IS NOT NULL THEN
142 --want to extract the value in the id column
143 l_temp_id := hr_du_dp_pc_conversion.return_field_value
144 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
145 'UPLOAD_LINE_ID', l_pval_parent_line_id);
146
147 hr_du_utility.message('INFO','l_temp_id 1 : ' || l_temp_id , 65);
148
149 --Check to see if there's a value with in the reference column of
150 --the api_module
151 IF l_temp_id IS NOT NULL THEN
152 l_parent_user_key_2 := hr_du_do_datapump.RETURN_CREATED_USER_KEY(
153 l_parent_api_module_number, l_temp_id,
154 p_upload_id, l_foreign_user_key_2);
155
156 hr_du_utility.message('INFO', 'l_temp_id : ' || l_temp_id , 70);
157 hr_du_utility.message('INFO', 'l_parent_api_module_number : ' ||
158 l_parent_api_module_number , 75);
159 hr_du_utility.message('INFO', 'l_parent_user_key_2 : ' ||
160 l_parent_user_key_2 , 80);
161 hr_du_utility.message('INFO', 'l_foreign_user_key_2 : ' ||
162 l_foreign_user_key_2 , 85);
163 EXIT;
164 END IF;
165 --generic column
166 ELSE
167 --want to extract both the table and id values
168 l_temp_id := hr_du_dp_pc_conversion.return_field_value
169 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
170 'UPLOAD_LINE_ID', l_pval_parent_line_id);
171
172 hr_du_utility.message('INFO','l_temp_id 2 : ' || l_temp_id , 90);
173
174 -- Check to see if there's a value with in the reference column
175 --of that api_module
176 IF l_temp_id IS NOT NULL THEN
177 l_temp_api_module := hr_du_dp_pc_conversion.return_field_value
178 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
179 'UPLOAD_LINE_ID', l_pval_api_module_number);
180
181 hr_du_utility.message('INFO', 'l_temp_api_module : ' || l_temp_api_module , 95);
182
183 l_parent_user_key_2 := hr_du_do_datapump.RETURN_CREATED_USER_KEY(
184 l_temp_api_module, l_temp_id,
185 p_upload_id, l_foreign_user_key_2);
186
187 hr_du_utility.message('INFO','l_temp_api_module : ' ||l_temp_api_module , 100);
188 hr_du_utility.message('INFO','l_parent_user_key_2 : ' ||
189 l_parent_user_key_2 , 105);
190 hr_du_utility.message('INFO','l_foreign_user_key_2 : ' ||
191 l_foreign_user_key_2 , 110);
192
193 END IF;
194 END IF;
195 END LOOP;
196 END IF;
197 */
198 --********************************************************************************************************************
199
200
201 --its here that I have to write the insert statement that will be general
202 --enough to take in API's that have no references and ones that do
203 l_insert_statement := 'insert into HRDPV_' || p_api_name || '(' ||
204 'BATCH_ID, BATCH_LINE_ID, '||
205 'API_MODULE_ID, LINE_STATUS, '||
206 'USER_SEQUENCE, LINK_VALUE, '||
207 l_foreign_user_key || ',';
208
209 l_insert_statement := l_insert_statement || 'p_assignment_user_key, ' ;
210
211 l_insert_statement := l_insert_statement || p_values_table.r_insert_string;
212
213 --add the closing bracket to the insert statement
214 l_insert_statement := l_insert_statement || ')';
215
216 --begin creating the values string which will be pushed into the stated
217 -- places
218 l_insert_statement := l_insert_statement || 'select ' ||
219 p_batch_id || ',' || p_pump_batch_line_id ||','||
220 p_api_module_id || ', ''U'',' ||
221 p_process_order || ',' || '1' || ',' ||
222 '''' ||l_parent_user_key || ''',' ||
223 '''' ||l_parent_user_key || ':ASG' || ''',';
224
225 l_insert_statement := l_insert_statement || p_values_table.r_PVAL_string;
226 l_insert_statement := l_insert_statement || ' FROM HR_DU_UPLOAD_LINES ' ||
227 'WHERE UPLOAD_LINE_ID = ' || p_upload_line_id;
228
229 hr_du_utility.message('INFO','l_foreign_user_key : ' || l_foreign_user_key , 115);
230
231 hr_du_utility.message('INFO', 'l_insert_statement - ' || l_insert_statement,35);
232
233 hr_du_utility.dynamic_sql(l_insert_statement);
234
235 --
236 hr_du_utility.message('ROUT','exit:hr_du_do_entities.create_default_employee', 115);
237 --
238
239 EXCEPTION
240 WHEN e_fatal_error THEN
241 hr_du_utility.error(SQLCODE,'hr_du_do_entities.create_default_employee'
242 ,l_fatal_error_message, 'R');
243 RAISE;
244 WHEN OTHERS THEN
245 hr_du_utility.error(SQLCODE, 'hr_du_do_entities.create_default_employee'
246 ,'(none)', 'R');
247 RAISE;
248
249 --
250 END CREATE_DEFAULT_EMPLOYEE;
251
252 -- --------------------------- DEFAULT_API ------------------------------
253 -- Description: This is the default procedure that handles those APIs
254 -- that fall in to the general catogory of been referenced and referencing
255 -- others.
256 --
257 -- Input Parameters
258 -- p_upload_id - HR_DU_UPLOAD_ID to be used
259 --
260 -- p_batch_id - PUMP_BATCH_HEADER_ID
261 --
262 -- p_api_module_id - API_MODULE_ID
263 --
264 -- p_process_order - Number to tell data pump the order in which
265 -- to process the record
266 --
267 -- p_upload_line_id - ID of the line in the HR_DU_UPLOAD_LINES
268 -- table that holds all the data to be
269 -- pushed into the HR_PUMP_BATCH_LINES
270 --
271 -- p_pump_batch_line_id - Identifies the Pump batch line to be used
272 -- ------------------------------------------------------------------------
273 PROCEDURE DEFAULT_API(
274 p_values_table IN hr_du_do_datapump.R_INSERT_STATEMENT_TYPE
275 ,p_upload_id IN NUMBER
276 ,p_batch_id IN NUMBER
277 ,p_api_module_id IN NUMBER
278 ,p_process_order IN NUMBER
279 ,p_upload_line_id IN NUMBER
280 ,p_api_name IN VARCHAR2
281 ,p_pump_batch_line_id IN NUMBER)
282 IS
283
284 e_fatal_error EXCEPTION;
285 l_fatal_error_message VARCHAR2(2000);
286 l_parent_user_key VARCHAR2(2000);
287 l_foreign_user_key VARCHAR2(50);
288 l_parent_user_key_2 VARCHAR2(2000);
289 l_foreign_user_key_2 VARCHAR2(50);
290 l_row_id NUMBER;
291 l_number_refs NUMBER;
292 l_pval_parent_line_id VARCHAR2(2000);
293 l_parent_api_module_number VARCHAR2(2000);
294 l_pval_api_module_number VARCHAR2(2000);
295 l_temp_id VARCHAR2(2000);
296 l_temp_api_module VARCHAR2(2000);
297 l_insert_statement VARCHAR2(32767);
298 l_cursor_handle INT;
299 l_rows_processed INT;
300
301 CURSOR csr_line_id IS
302 SELECT PVAl001
303 FROM hr_du_upload_lines
304 WHERE UPLOAD_LINE_ID = p_upload_line_id;
305
306 BEGIN
307
308 --
309 hr_du_utility.message('ROUT','entry:hr_du_do_entities.default_api', 5);
310 hr_du_utility.message('PARA', '(p_values_table - ** Record Structure** ' ||
311 ')(p_upload_id - ' || p_upload_id ||
312 ')(p_batch_id - ' || p_batch_id ||
313 ')(p_api_module_id - ' || p_api_module_id ||
314 ')(p_process_order - ' || p_process_order ||
315 ')(p_upload_line_id - ' || p_upload_line_id || ')'
316 , 10);
317 --
318
319 --Statement extracts the ID number for the particular upload_line_id
320 --that was passed in.
321
322 OPEN csr_line_id;
323 --
324 FETCH csr_line_id INTO l_row_id;
325 IF csr_line_id%NOTFOUND THEN
326 l_fatal_error_message := ' Unable to retrieve the ID';
327 RAISE e_fatal_error;
328 END IF;
329 --
330 CLOSE csr_line_id;
331
332 l_parent_user_key := hr_du_do_datapump.RETURN_CREATED_USER_KEY_2(l_row_id,
333 p_values_table.r_api_id, p_upload_line_id,
334 l_foreign_user_key);
335
336
337 hr_du_utility.message('INFO', 'l_row_id : ' || l_row_id , 15);
338 hr_du_utility.message('INFO', 'l_parent_user_key : ' ||
339 l_parent_user_key , 20);
340 hr_du_utility.message('INFO', 'l_foreign_user_key : ' ||
341 l_foreign_user_key , 25);
342
343 hr_du_di_insert.g_current_delimiter := ',';
344
345 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
346 p_values_table.r_pval_parent_line_id);
347
348 l_number_refs := hr_du_di_insert.WORDS_ON_LINE(
349 p_values_table.r_pval_parent_line_id);
350
351 hr_du_utility.message('INFO', 'r_pval_parent_line_id : ' ||
352 p_values_table.r_pval_parent_line_id , 30);
353 hr_du_utility.message('INFO', 'l_number_refs : ' || l_number_refs , 35);
354
355
356 --check to see if this api_module has any columns that may contain data to
357 --indicate that it has been called by another api_module
358 IF l_number_refs > 0 THEN
359 FOR j IN 1..l_number_refs LOOP
360 --values must be set to null here or else floating API's with no
361 --references will take the last values in the loop
362 l_parent_user_key_2 := null;
363 l_foreign_user_key_2 := null;
364
365 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
366 p_values_table.r_pval_parent_line_id);
367
368 l_pval_parent_line_id := hr_du_di_insert.Return_Word(
369 p_values_table.r_pval_parent_line_id , j);
370
371
375 l_parent_api_module_number := hr_du_di_insert.Return_Word(
372 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
373 p_values_table.r_parent_api_module_number);
374
376 p_values_table.r_parent_api_module_number, j);
377
378
379 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
380 p_values_table.r_pval_api_module_number);
381
382 l_pval_api_module_number := hr_du_di_insert.Return_Word(
383 p_values_table.r_pval_api_module_number , j);
384
385 hr_du_utility.message('INFO','l_pval_parent_line_id : ' ||
386 l_pval_parent_line_id , 40);
387 hr_du_utility.message('INFO','l_parent_api_module_number : ' ||
388 l_parent_api_module_number , 45);
389
390 --check to see if referencing column is a generic reference. If null
391 --then it is
392
393 IF l_parent_api_module_number IS NOT NULL THEN
394 --want to extract the value in the id column
395 l_temp_id := hr_du_dp_pc_conversion.return_field_value
396 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
397 'UPLOAD_LINE_ID', l_pval_parent_line_id);
398
399 --Check to see if there's a value with in the reference column of
400 --the api_module
401 IF l_temp_id IS NOT NULL THEN
402 l_parent_user_key_2 := hr_du_do_datapump.RETURN_CREATED_USER_KEY(
403 l_parent_api_module_number, l_temp_id,
404 p_upload_id, l_foreign_user_key_2);
405
406 hr_du_utility.message('INFO','l_temp_id : ' || l_temp_id , 60);
407 hr_du_utility.message('INFO','l_parent_api_module_number : ' ||
408 l_parent_api_module_number , 65);
409 hr_du_utility.message('INFO','l_parent_user_key_2 : ' ||
410 l_parent_user_key_2 , 70);
411 hr_du_utility.message('INFO','l_foreign_user_key_2 : ' ||
412 l_foreign_user_key_2 , 75);
413
414 EXIT;
415 END IF;
416
417 --generic column
418 ELSE
419 --want to extract both the table and id values
420 l_temp_id := hr_du_dp_pc_conversion.return_field_value
421 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
422 'UPLOAD_LINE_ID', l_pval_parent_line_id);
423
424 -- Check to see if there's a value with in the reference column
425 --of that api_module
426 IF l_temp_id IS NOT NULL THEN
427 l_temp_api_module := hr_du_dp_pc_conversion.return_field_value
428 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
429 'UPLOAD_LINE_ID', l_pval_api_module_number);
430
431
432 l_parent_user_key_2 := hr_du_do_datapump.RETURN_CREATED_USER_KEY(
433 l_temp_api_module, l_temp_id,
434 p_upload_id, l_foreign_user_key_2);
435
436 hr_du_utility.message('INFO', 'l_temp_id : ' || l_temp_id, 85);
437 hr_du_utility.message('INFO', 'l_temp_api_module : ' || l_temp_api_module, 90);
438 hr_du_utility.message('INFO', 'l_parent_user_key_2 : ' ||
439 l_parent_user_key_2, 95);
440 hr_du_utility.message('INFO', 'l_foreign_user_key_2 : ' ||
441 l_foreign_user_key_2, 100);
442 END IF;
443 END IF;
444 END LOOP;
445 END IF;
446
447 --its here that I have to write the insert statement that will be general
448 --enough to take in API's that have no references and ones that do
449 l_insert_statement := 'insert into HRDPV_' || p_api_name || '(' ||
450 'BATCH_ID, BATCH_LINE_ID, '||
451 'API_MODULE_ID, LINE_STATUS, '||
452 'USER_SEQUENCE, LINK_VALUE, '||
453 l_foreign_user_key || ',';
454
455 --Glues on to the string the extra information if the api_module has a reference
456 --to another api_module
457 IF l_foreign_user_key_2 IS NOT NULL THEN
458 l_insert_statement := l_insert_statement || l_foreign_user_key_2 || ',';
459 END IF;
460
461 l_insert_statement := l_insert_statement || p_values_table.r_insert_string;
462
463 --add the closing bracket to the insert statement
464 l_insert_statement := l_insert_statement || ')';
465
466 --begin creating the values string which will be pushed into the stated
467 --places
468 l_insert_statement := l_insert_statement || 'select ' ||
469 p_batch_id || ',' || p_pump_batch_line_id ||','||
470 p_api_module_id || ', ''U'',' ||
471 p_process_order || ',' || '1' || ',' ||
472 '''' ||l_parent_user_key || ''',';
473
474 IF l_foreign_user_key_2 IS NOT NULL THEN
475 l_insert_statement := l_insert_statement || '''' || l_parent_user_key_2 || ''',';
476 END IF;
477
478 l_insert_statement := l_insert_statement || p_values_table.r_PVAL_string;
479
480 l_insert_statement := l_insert_statement || ' FROM HR_DU_UPLOAD_LINES ' ||
481 'WHERE UPLOAD_LINE_ID = ' || p_upload_line_id;
482
486
483 hr_du_utility.message('INFO','l_foreign_user_key : ' || l_foreign_user_key , 115);
484
485 hr_du_utility.message('INFO', 'l_insert_statement - ' || l_insert_statement,35);
487 hr_du_utility.dynamic_sql(l_insert_statement);
488
489 --
490 hr_du_utility.message('ROUT','exit:hr_du_do_entities.default_api', 105);
491 --
492
493 EXCEPTION
494 WHEN e_fatal_error THEN
495 hr_du_utility.error(SQLCODE,'hr_du_do_entities.default_api'
496 ,l_fatal_error_message, 'R');
497 RAISE;
498 WHEN OTHERS THEN
499 hr_du_utility.error(SQLCODE, 'hr_du_do_entities.default_api','(none)', 'R');
500 RAISE;
501 --
502 END DEFAULT_API;
503
504
505 -- ------------------------ UPDATE_EMP_ASG_CRITERIA ------------------------
506 -- Description: Deals specifically with the Assignment api_module, the reason
507 -- it has it's own procedure isn't like the others for they have a
508 -- specified USER_KEY. ASSIGNMENT_USER_KEY has already been created with
509 -- the person api_module so I need retrieve the USER_KEY from the person.
510 -- This Procedure is only supported currently to be run with the person api_module
511 --
512 -- Input Parameters
513 -- p_upload_id - HR_DU_UPLOAD_ID to be used
514 --
515 -- p_batch_id - PUMP_BATCH_HEADER_ID
516 --
517 -- p_api_module_id - API_MODULE_ID
518 --
519 -- p_process_order - Number to tell data pump the order in which
520 -- to process the record
521 --
522 -- p_upload_line_id - ID of the line in the HR_DU_UPLOAD_LINES
523 -- table that holds all the data to be
524 -- pushed into the HR_PUMP_BATCH_LINES
525 --
526 -- p_pump_batch_line_id - Identifies the Pump batch line to be used
527 --
528 -- ** This Procedure is currently supported with only the person api_module **
529 -- ------------------------------------------------------------------------
530 PROCEDURE UPDATE_EMP_ASG_CRITERIA(
531 p_values_table IN hr_du_do_datapump.R_INSERT_STATEMENT_TYPE
532 ,p_upload_id IN NUMBER
533 ,p_batch_id IN NUMBER
534 ,p_api_module_id IN NUMBER
535 ,p_process_order IN NUMBER
536 ,p_upload_line_id IN NUMBER
537 ,p_api_name IN VARCHAR2
538 ,p_pump_batch_line_id IN NUMBER)
539 IS
540
541 e_fatal_error EXCEPTION;
542 l_fatal_error_message VARCHAR2(2000);
543 l_parent_user_key VARCHAR2(2000);
544 l_foreign_user_key VARCHAR2(50);
545 l_parent_user_key_2 VARCHAR2(2000);
546 l_foreign_user_key_2 VARCHAR2(50);
547 l_row_id NUMBER;
548 l_number_refs NUMBER;
549 l_pval_parent_line_id VARCHAR2(2000);
550 l_parent_api_module_number VARCHAR2(2000);
551 l_pval_api_module_number VARCHAR2(2000);
552 l_temp_id VARCHAR2(2000);
553 l_temp_api_module VARCHAR2(2000);
554 l_insert_statement VARCHAR2(32767);
555 l_cursor_handle INT;
556 l_rows_processed INT;
557
558 CURSOR csr_line_id IS
559 SELECT PVAl001
560 FROM hr_du_upload_lines
561 WHERE UPLOAD_LINE_ID = p_upload_line_id;
562
563 BEGIN
564
565 --
566 hr_du_utility.message('ROUT',
567 'entry:hr_du_do_entities.update_emp_asg_criteria', 5);
568 hr_du_utility.message('PARA', '(p_values_table - ** Record Structure** ' ||
569 ')(p_upload_id - ' || p_upload_id ||
570 ')(p_batch_id - ' || p_batch_id ||
571 ')(p_api_module_id - ' || p_api_module_id ||
572 ')(p_process_order - ' || p_process_order ||
573 ')(p_upload_line_id - ' || p_upload_line_id || ')'
574 , 10);
575 --
576 --Statement extracts the ID number for the particular upload_line_id
577 --that was passed in.
578
579 OPEN csr_line_id;
580 --
581 FETCH csr_line_id INTO l_row_id;
582 IF csr_line_id%NOTFOUND THEN
583 l_fatal_error_message := ' Unable to retrieve the ID';
584 RAISE e_fatal_error;
585 END IF;
586 --
587 CLOSE csr_line_id;
588
589 l_parent_user_key := hr_du_do_datapump.RETURN_CREATED_USER_KEY_2(l_row_id,
590 p_values_table.r_api_id, p_upload_line_id,
591 l_foreign_user_key);
592
593 hr_du_utility.message('INFO','l_row_id : ' || l_row_id , 15);
594 hr_du_utility.message('INFO', 'l_parent_user_key : ' || l_parent_user_key , 20);
595 hr_du_utility.message('INFO', 'l_foreign_user_key : ' || l_foreign_user_key, 25);
596
597 hr_du_di_insert.g_current_delimiter := ',';
598
599 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
600 p_values_table.r_pval_parent_line_id);
601
602 l_number_refs := hr_du_di_insert.WORDS_ON_LINE(
603 p_values_table.r_pval_parent_line_id);
604
605 hr_du_utility.message('INFO','r_pval_parent_line_id : ' ||
606 p_values_table.r_pval_parent_line_id , 30);
607 hr_du_utility.message('INFO', 'l_number_refs : ' || l_number_refs , 35);
608
612 IF l_number_refs > 0 THEN
609 --check to see if this api_module has any columns that may contain
610 --data to indicate that it has been called by another api_module
611
613 FOR j IN 1..l_number_refs LOOP
614 --values must be set to null here or else floating API's with no
615 --references will take the last values in the loop
616 l_parent_user_key_2 := null;
617 l_foreign_user_key_2 := null;
618
619 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
620 p_values_table.r_pval_parent_line_id);
621
622 l_pval_parent_line_id :=hr_du_di_insert.Return_Word(
623 p_values_table.r_pval_parent_line_id , j);
624
625
626 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
627 p_values_table.r_parent_api_module_number);
628
629 l_parent_api_module_number := hr_du_di_insert.Return_Word(
630 p_values_table.r_parent_api_module_number, j);
631
632
633 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
634 p_values_table.r_pval_api_module_number);
635
636 l_pval_api_module_number := hr_du_di_insert.Return_Word(
637 p_values_table.r_pval_api_module_number , j);
638
639 hr_du_utility.message('INFO','l_pval_parent_line_id : ' ||
640 l_pval_parent_line_id , 40);
641 hr_du_utility.message('INFO','l_parent_api_module_number : ' ||
642 l_parent_api_module_number , 45);
643 hr_du_utility.message('INFO','l_pval_api_module_number : ' ||
644 l_pval_api_module_number , 50);
645
646 --check to see if referencing column is a generic reference. If null
647 --then it is
648 IF l_parent_api_module_number IS NOT NULL THEN
649 --want to extract the value in the id column
650 l_temp_id := hr_du_dp_pc_conversion.return_field_value
651 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
652 'UPLOAD_LINE_ID', l_pval_parent_line_id);
653
654 --Check to see if there's a value with in the reference column of
655 --the api_module
656 IF l_temp_id IS NOT NULL THEN
657 l_parent_user_key_2 := hr_du_do_datapump.RETURN_CREATED_USER_KEY(
658 l_parent_api_module_number, l_temp_id,
659 p_upload_id, l_foreign_user_key_2);
660
661 hr_du_utility.message('INFO', 'l_temp_id : ' || l_temp_id , 55);
662 hr_du_utility.message('INFO', 'l_parent_api_module_number : ' ||
663 l_parent_api_module_number , 60);
664 hr_du_utility.message('INFO', 'l_parent_user_key_2 : ' ||
665 l_parent_user_key_2 , 65);
666 hr_du_utility.message('INFO', 'l_foreign_user_key_2 : ' ||
667 l_foreign_user_key_2 , 70);
668 EXIT;
669 END IF;
670 --generic column
671 ELSE
672 --want to extract both the table and id values
673 l_temp_id := hr_du_dp_pc_conversion.return_field_value
674 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
675 'UPLOAD_LINE_ID', l_pval_parent_line_id);
676
677 --Check to see if there's a value with in the reference column
678 --of that api_module
679 IF l_temp_id IS NOT NULL THEN
680 l_temp_api_module := hr_du_dp_pc_conversion.return_field_value
681 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
682 'UPLOAD_LINE_ID', l_pval_api_module_number);
683
684 hr_du_utility.message('INFO', 'l_temp_api_module : ' || l_temp_api_module , 75);
685
686 l_parent_user_key_2 := hr_du_do_datapump.RETURN_CREATED_USER_KEY(
687 l_temp_api_module, l_temp_id,
688 p_upload_id, l_foreign_user_key_2);
689
690 hr_du_utility.message('INFO','l_temp_id : ' || l_temp_id , 80);
691 hr_du_utility.message('INFO','l_temp_api_module : ' || l_temp_api_module , 85);
692 hr_du_utility.message('INFO','l_parent_user_key_2 : ' ||
693 l_parent_user_key_2 , 90);
694 hr_du_utility.message('INFO','l_foreign_user_key_2 : ' ||
695 l_foreign_user_key_2 , 95);
696 END IF;
697 END IF;
698 END LOOP;
699 END IF;
700
701 -- to get it working...
702 l_foreign_user_key := 'P_ASSIGNMENT_USER_KEY';
703
704
705 --its here that I have to write the insert statement that will be general
706 --enough to take in API's that have no references and ones that do
707 l_insert_statement := 'insert into HRDPV_' || p_api_name || '(' ||
708 'BATCH_ID, BATCH_LINE_ID, '||
709 'API_MODULE_ID, LINE_STATUS, '||
710 'USER_SEQUENCE, LINK_VALUE, '||
711 l_foreign_user_key || ',';
712
713 l_insert_statement := l_insert_statement || p_values_table.r_insert_string;
714
715 --add the closing bracket to the insert statement
716 l_insert_statement := l_insert_statement || ')';
717
721 p_batch_id || ',' || p_pump_batch_line_id || ',' ||
718 --begin creating the values string which will be pushed into the
719 --stated places
720 l_insert_statement := l_insert_statement || 'select ' ||
722 p_api_module_id || ', ''U'',' ||
723 p_process_order || ',' || '1' || ',' ||
724 '''' ||l_parent_user_key_2 || ':ASG' || ''',';
725
726 l_insert_statement := l_insert_statement || p_values_table.r_PVAL_string;
727 l_insert_statement := l_insert_statement || ' FROM HR_DU_UPLOAD_LINES ' ||
728 'WHERE UPLOAD_LINE_ID = ' || p_upload_line_id;
729
730 hr_du_utility.message('INFO','l_foreign_user_key : ' || l_foreign_user_key , 115);
731 hr_du_utility.message('INFO', 'l_insert_statement - ' || l_insert_statement,35);
732
733 hr_du_utility.dynamic_sql(l_insert_statement);
734
735 --
736 hr_du_utility.message('ROUT','exit:hr_du_do_entities.update_emp_asg_criteria', 100);
737 --
738
739 EXCEPTION
740 WHEN e_fatal_error THEN
741 hr_du_utility.error(SQLCODE,'hr_du_do_entities.update_emp_asg_criteria'
742 ,l_fatal_error_message, 'R');
743 RAISE;
744 WHEN OTHERS THEN
745 hr_du_utility.error(SQLCODE, 'hr_du_do_entities.update_emp_asg_criteria',
746 '(none)', 'R');
747 RAISE;
748
749 --
750 END UPDATE_EMP_ASG_CRITERIA;
751
752
753 -- --------------------------- DEFAULT_API_NULL ------------------------------
754 -- Description: This is the default procedure that handles those APIs
755 -- that don't specify a user key in their flat file
756 --
757 -- Input Parameters
758 -- p_upload_id - HR_DU_UPLOAD_ID to be used
759 --
760 -- p_batch_id - PUMP_BATCH_HEADER_ID
761 --
762 -- p_api_module_id - API_MODULE_ID
763 --
764 -- p_process_order - Number to tell data pump the order in which
765 -- to process the record
766 --
767 -- p_upload_line_id - ID of the line in the HR_DU_UPLOAD_LINES
768 -- table that holds all the data to be
769 -- pushed into the HR_PUMP_BATCH_LINES
770 --
771 -- p_pump_batch_line_id - Identifies the Pump batch line to be used
772 -- ------------------------------------------------------------------------
773 PROCEDURE DEFAULT_API_NULL(
774 p_values_table IN hr_du_do_datapump.R_INSERT_STATEMENT_TYPE
775 ,p_upload_id IN NUMBER
776 ,p_batch_id IN NUMBER
777 ,p_api_module_id IN NUMBER
778 ,p_process_order IN NUMBER
779 ,p_upload_line_id IN NUMBER
780 ,p_api_name IN VARCHAR2
781 ,p_pump_batch_line_id IN NUMBER)
782 IS
783
784 e_fatal_error EXCEPTION;
785 l_fatal_error_message VARCHAR2(2000);
786 l_parent_user_key VARCHAR2(2000);
787 l_foreign_user_key VARCHAR2(50);
788 l_parent_user_key_2 VARCHAR2(2000);
789 l_foreign_user_key_2 VARCHAR2(50);
790 l_row_id NUMBER;
791 l_number_refs NUMBER;
792 l_pval_parent_line_id VARCHAR2(2000);
793 l_parent_api_module_number VARCHAR2(2000);
794 l_pval_api_module_number VARCHAR2(2000);
795 l_temp_id VARCHAR2(2000);
796 l_temp_api_module VARCHAR2(2000);
797 l_insert_statement VARCHAR2(32767);
798 l_cursor_handle INT;
799 l_rows_processed INT;
800
801 CURSOR csr_line_id IS
802 SELECT PVAl001
803 FROM hr_du_upload_lines
804 WHERE UPLOAD_LINE_ID = p_upload_line_id;
805
806
807 BEGIN
808
809 --
810 hr_du_utility.message('ROUT','entry:hr_du_do_entities.default_api_null', 5);
811 hr_du_utility.message('PARA', '(p_values_table - ** Record Structure** ' ||
812 ')(p_upload_id - ' || p_upload_id ||
813 ')(p_batch_id - ' || p_batch_id ||
814 ')(p_api_module_id - ' || p_api_module_id ||
815 ')(p_process_order - ' || p_process_order ||
816 ')(p_upload_line_id - ' || p_upload_line_id || ')'
817 , 10);
818 --
819
820 --Statement extracts the ID number for the particular upload_line_id
821 --that was passed in.
822
823 OPEN csr_line_id;
824 --
825 FETCH csr_line_id INTO l_row_id;
826 IF csr_line_id%NOTFOUND THEN
827 l_fatal_error_message := ' Unable to retrieve the ID';
828 RAISE e_fatal_error;
829 END IF;
830 --
831 CLOSE csr_line_id;
832
833 hr_du_utility.message('INFO', 'l_row_id : ' || l_row_id , 15);
834
835 hr_du_di_insert.g_current_delimiter := ',';
836
837 -- no delimeters as we have 'none' as the user key
838 hr_du_di_insert.g_delimiter_count := 0;
839
840 --
841 l_number_refs := hr_du_di_insert.WORDS_ON_LINE(
842 p_values_table.r_pval_parent_line_id);
843
844 hr_du_utility.message('INFO', 'r_pval_parent_line_id : ' ||
845 p_values_table.r_pval_parent_line_id , 30);
846 hr_du_utility.message('INFO', 'l_number_refs : ' || l_number_refs , 35);
847
851 IF l_number_refs > 0 THEN
848 --check to see if this api_module has any columns that may contain data to
849 --indicate that it has been called by another api_module
850
852 FOR j IN 1..l_number_refs LOOP
853 --values must be set to null here or else floating API's with no
854 --references will take the last values in the loop
855 l_parent_user_key_2 := null;
856 l_foreign_user_key_2 := null;
857
858 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
859 p_values_table.r_pval_parent_line_id);
860
861 l_pval_parent_line_id := hr_du_di_insert.Return_Word(
862 p_values_table.r_pval_parent_line_id , j);
863
864 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
865 p_values_table.r_parent_api_module_number);
866 l_parent_api_module_number := hr_du_di_insert.Return_Word(
867 p_values_table.r_parent_api_module_number, j);
868
869 hr_du_di_insert.g_delimiter_count := hr_du_di_insert.Num_Delimiters(
870 p_values_table.r_pval_api_module_number);
871
872 l_pval_api_module_number := hr_du_di_insert.Return_Word(
873 p_values_table.r_pval_api_module_number , j);
874
875 hr_du_utility.message('INFO','l_pval_parent_line_id : ' ||
876 l_pval_parent_line_id , 40);
877 hr_du_utility.message('INFO','l_parent_api_module_number : ' ||
878 l_parent_api_module_number , 45);
879 hr_du_utility.message('INFO','l_pval_api_module_number : ' ||
880 l_pval_api_module_number , 50);
881
882 --check to see if referencing column is a generic reference. If null
883 --then it is
884 IF l_parent_api_module_number IS NOT NULL THEN
885 --want to extract the value in the id column
886 l_temp_id := hr_du_dp_pc_conversion.return_field_value
887 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
888 'UPLOAD_LINE_ID', l_pval_parent_line_id);
889
890 hr_du_utility.message('INFO','l_temp_id 1 : ' || l_temp_id , 55);
891
892 --Check to see if there's a value with in the reference column of
893 --the api_module
894 IF l_temp_id IS NOT NULL THEN
895 l_parent_user_key_2 := hr_du_do_datapump.RETURN_CREATED_USER_KEY(
896 l_parent_api_module_number, l_temp_id,
897 p_upload_id, l_foreign_user_key_2);
898
899 hr_du_utility.message('INFO','l_temp_id : ' || l_temp_id , 60);
900 hr_du_utility.message('INFO','l_parent_api_module_number : ' ||
901 l_parent_api_module_number , 65);
902 hr_du_utility.message('INFO','l_parent_user_key_2 : ' ||
903 l_parent_user_key_2 , 70);
904 hr_du_utility.message('INFO','l_foreign_user_key_2 : ' ||
905 l_foreign_user_key_2 , 75);
906
907 EXIT;
908 END IF;
909 --generic column
910 ELSE
911
912 --want to extract both the table and id values
913 l_temp_id := hr_du_dp_pc_conversion.return_field_value
914 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
915 'UPLOAD_LINE_ID', l_pval_parent_line_id);
916
917 -- Check to see if there's a value with in the reference column
918 --of that api_module
919 IF l_temp_id IS NOT NULL THEN
920 l_temp_api_module := hr_du_dp_pc_conversion.return_field_value
921 ('HR_DU_UPLOAD_LINES', p_upload_line_id,
922 'UPLOAD_LINE_ID', l_pval_api_module_number);
923
924 hr_du_utility.message('INFO', 'l_temp_api_module : ' || l_temp_api_module,
925 80);
926
927 l_parent_user_key_2 := hr_du_do_datapump.RETURN_CREATED_USER_KEY(
928 l_temp_api_module, l_temp_id,
929 p_upload_id, l_foreign_user_key_2);
930
931 hr_du_utility.message('INFO', 'l_temp_id : ' || l_temp_id , 85);
932 hr_du_utility.message('INFO', 'l_temp_api_module : ' || l_temp_api_module , 90);
933 hr_du_utility.message('INFO', 'l_parent_user_key_2 : ' ||
934 l_parent_user_key_2 , 95);
935 hr_du_utility.message('INFO', 'l_foreign_user_key_2 : ' ||
936 l_foreign_user_key_2 , 100);
937
938 END IF;
939 END IF;
940 END LOOP;
941 END IF;
942
943 --its here that I have to write the insert statement that will be general
944 --enough to take in API's that have no references and ones that do
945 l_insert_statement := 'insert into HRDPV_' || p_api_name || '(' ||
946 'BATCH_ID, BATCH_LINE_ID, '||
947 'API_MODULE_ID, LINE_STATUS, '||
948 'USER_SEQUENCE, LINK_VALUE, ';
949
950 --Glues on to the string the extra information if the api_module has a reference
951 --to another api_module
952 IF l_foreign_user_key_2 IS NOT NULL THEN
953 l_insert_statement := l_insert_statement || l_foreign_user_key_2 || ',';
954 END IF;
955
959 l_insert_statement := l_insert_statement || ')';
956 l_insert_statement := l_insert_statement || p_values_table.r_insert_string;
957
958 --add the closing bracket to the insert statement
960
961 --begin creating the values string which will be pushed into the stated
962 --places
963 l_insert_statement := l_insert_statement || 'select ' ||
964 p_batch_id || ',' || p_pump_batch_line_id ||','||
965 p_api_module_id || ', ''U'',' ||
966 p_process_order || ',' || '1' || ',';
967
968 IF l_foreign_user_key_2 IS NOT NULL THEN
969 l_insert_statement := l_insert_statement || '''' || l_parent_user_key_2 || ''',';
970 END IF;
971
972 l_insert_statement := l_insert_statement || p_values_table.r_PVAL_string;
973
974 l_insert_statement := l_insert_statement || ' FROM HR_DU_UPLOAD_LINES ' ||
975 'WHERE UPLOAD_LINE_ID = ' || p_upload_line_id;
976
977 hr_du_utility.message('INFO', 'l_insert_statement - ' || l_insert_statement,35);
978
979 hr_du_utility.message('INFO','l_foreign_user_key : ' || l_foreign_user_key , 115);
980
981 hr_du_utility.dynamic_sql(l_insert_statement);
982
983 --
984 hr_du_utility.message('ROUT','exit:hr_du_do_entities.default_api_null', 105);
985 --
986
987 EXCEPTION
988 WHEN e_fatal_error THEN
989 hr_du_utility.error(SQLCODE,'hr_du_do_entities.default_api_null'
990 ,l_fatal_error_message, 'R');
991 RAISE;
992 WHEN OTHERS THEN
993 hr_du_utility.error(SQLCODE, 'hr_du_do_entities.default_api_null','(none)', 'R');
994 RAISE;
995 --
996 END DEFAULT_API_NULL;
997
998 END HR_DU_DO_ENTITIES;
[Home] [Help]