1 Package Body pqh_ss_print as
2 /* $Header: pqprtswi.pkb 120.9 2006/09/26 11:36:46 krajarat noship $ */
3
4 -- Declaring global variables.
5 --
6 g_session_id NUMBER;
7 g_transaction_id NUMBER;
8 g_debug boolean := hr_utility.debug_enabled;
9 g_package varchar2(72) := 'pqh_ss_print';
10 g_effective_date DATE;
11
12 --
13 -- This procedure will be called from RefreshAMImpl, to set
14 -- g_effective_date in order to get proper values for
15 -- get_change_reason,get_employement_category etc for refresh
16 -- attributes functionality
17 --
18 PROCEDURE set_eff_dte_for_refresh_atts is
19 cursor c_session_date is
20 select effective_date
21 from fnd_sessions
22 where session_id = userenv('sessionid');
23 begin
24
25 open c_session_date;
26 fetch c_session_date into g_effective_date;
27 close c_session_date;
28 end;
29
30 --
31 -- This function will be invoked from Fast Formula , Example :PQH_DOCUMENTS_FASTFORMULA
32 -- Print_document_data / set_document_data
33 --
34 -- If tag_value is null,
35 function get_session_details (p_txn_id OUT NOCOPY NUMBER, p_session_id OUT NOCOPY NUMBER, p_effective_date OUT NOCOPY DATE)
36 return number
37 IS
38 BEGIN
39
40 select userenv('sessionid') into p_session_id from dual;
41 select trunc(sysdate) into p_effective_date from dual;
42 p_txn_id :=g_transaction_id;
43
44 return 0;
45
46 END;
47
48
49 --
50 FUNCTION set_document_data(p_tag_name IN VARCHAR2,
51 p_tag_value IN VARCHAR2)
52 RETURN NUMBER IS
53 --
54 -- Declaring cursors here.
55 --
56
57 -- this cursor checks for given tag present in pqh_ss_print_data or not.
58 --
59 Cursor csr_check_tag_exists(p_tag_name VARCHAR2,p_session_id number) IS
60 Select 'X'
61 From pqh_ss_print_data
62 Where name = p_tag_name -- no upper case change
63 and session_id = p_session_id
64 and transaction_id =g_transaction_id;
65
66
67 --
68 -- Local variable declartion goes here.
69 --
70 l_result VARCHAR2(100);
71 l_session_id NUMBER;
72 l_transaction_id NUMBER;
73
74 BEGIN
75 -- Get session_id into l_session_id, this session_id will be inserted into
76 -- temp table.
77 -- If p_transaction_id is null then make use of g_transaction_id else
78 -- g_transaction_id gets p_transaction_id value.
79
80
81 --
82 l_transaction_id := g_transaction_id;
83 --
84
85
86
87 SELECT userenv('sessionid') into l_session_id from dual;
88
89 OPEN csr_check_tag_exists(p_tag_name,l_session_id);
90
91 FETCH csr_check_tag_exists into l_result;
92
93 CLOSE csr_check_tag_exists;
94 IF (l_result IS NULL ) THEN -- i.e. l_result is null , no record with current tag,
95 --
96 INSERT INTO PQH_SS_PRINT_DATA(session_id,transaction_id,name,value)
97 VALUES (l_session_id, l_transaction_id,p_tag_name,p_tag_value);
98
99 ELSE
100
101 UPDATE PQH_SS_PRINT_DATA
102 SET value = p_tag_value
103 WHERE session_id = l_session_id
104 AND name = p_tag_name
105 AND transaction_id = l_transaction_id;
106
107 END IF;
108
109 l_result := null;
110
111
112 RETURN 0;
113
114 EXCEPTION
115 WHEN NO_DATA_FOUND THEN
116 RETURN 0;
117 END;
118
119 ----
120 ---
121 ---
122 FUNCTION get_function_parameter_value(
123 p_parameter_name IN VARCHAR2,
124 p_transaction_id IN VARCHAR2,
125 p_type_code IN VARCHAR2 default 'PRE',
126 p_effective_date IN VARCHAR2)
127 RETURN VARCHAR2 IS
128 --
129 -- Defining cursors
130 --
131 Cursor csr_parameter_value IS
132 SELECT decode(SemiProcessedName ,'N/A','N/A', nvl(substr(SemiProcessedName , 1, instr(SemiProcessedName,'&')-1),SemiProcessedName)) ShortName
133 From
134 (
135 SELECT decode(instr(parameters,p_parameter_name),null,'N/A',0 ,'N/A',
136 substr(parameters,instr(parameters,p_parameter_name)+length(p_parameter_name)+1)) SemiProcessedName
137 FROM fnd_form_functions FFF,
138 hr_api_transactions HAT
139 WHERE HAT.transaction_id = p_transaction_id
140 AND HAT.function_id = FFF.function_id
141 );
142 --
143 --
144 Cursor csr_check_grp_existency(p_short_name varchar2, p_eff_date date) IS
145 Select count(*)
146 from pqh_transaction_categories cats,
147 pqh_txn_category_documents catDocs,
148 pqh_documents_f docs
149 where form_name like 'PQH_GROUPS'
150 and cats.short_name like p_short_name
151 and cats.transaction_category_id = catDocs.transaction_category_id
152 and docs.document_id = catDocs.document_id
153 and nvl(p_eff_date,trunc(sysdate)) between effective_start_date and effective_end_date;
154 --
155 --
156 Cursor csr_valid_grp_documents(p_short_name varchar2) IS
157 Select count(*)
158 from pqh_txn_category_documents
159 where transaction_category_id = (
160 Select transaction_category_id
161 from pqh_transaction_categories
162 where short_name =p_short_name)
163 and type_code in ( p_type_code, 'BOTH');
164 --
165 --
166 -- Defining local variables.
167 --
168 l_parameter_value varchar2(100):=null;
169 l_effective_date date := null;
170 l_pdf_in_table varchar2(10);
171 --
172 BEGIN
173
174 --
175 OPEN csr_parameter_value;
176 Fetch csr_parameter_value into l_parameter_value;
177 CLOSE csr_parameter_value;
178 --
179 -- Set the date
180 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
181
182 If (l_effective_date is null) then
183 l_effective_date := trunc(sysdate);
184 End if;
185
186 hr_util_misc_ss.setEffectiveDate(l_effective_date);
187
188 --
189 -- Check this short_name is valid name as on that date or not.
190 --
191 IF l_parameter_value <> 'N/A' and p_parameter_name='pGroupName' THEN
192 --
193 OPEN csr_check_grp_existency(l_parameter_value , l_effective_date);
194 Fetch csr_check_grp_existency into l_pdf_in_table;
195 CLOSE csr_check_grp_existency;
196
197 IF l_pdf_in_table = 0 THEN
198 --If Group Not Exists
199 l_parameter_value := 'INVALID';
200 --
201 Else
202 -- If Group is Present
203 -- Check any PRE/BOTH type of Document s attched to group or Not
204 --
205 OPEN csr_valid_grp_documents(l_parameter_value);
206 FETCH csr_valid_grp_documents into l_pdf_in_table;
207 CLOSE csr_valid_grp_documents;
208 IF l_pdf_in_table = 0 THEN
209 --
210 l_parameter_value := 'INVALID';
211 --
212 END IF;
213 --
214 END IF;
215 --
216 END IF;
217
218
219 return l_parameter_value;
220 --
221 END get_function_parameter_value;
222
223 --
224 --This procedure Populate a pl/sql table which stores the parameters of a where clasue
225 --
226 PROCEDURE populate_params_table(p_Transaction_Id IN VARCHAR2,
227 p_effective_date IN DATE)
228 IS
229 --This cursor obtain the values of hte parameters used in where clause of different Table_routes
230 -- 'P_SELECTED_EMP_ID, P_PASSED_ASSIGNMENT_ID removed from list as current
231 --manager not obtained from base table but from transaction table.
232 Cursor obtain_param_values is
233 SELECT atv.name column_name,atv.datatype column_type,atv.varchar2_value|| Atv.Number_value||FND_DATE.date_to_canonical(Atv.date_value) column_value
234 FROM hr_api_transaction_values atv
235 WHERE atv.transaction_step_id in (Select max(transaction_step_id)
236 From hr_api_transaction_steps
237 where transaction_id = p_Transaction_Id
238 group by api_name
239 )
240 AND atv.name in ('P_PERSON_ID','P_ASSIGNMENT_ID','P_QUALIFICATION_ID','P_SELECTED_PERSON_ID');
241 l_proc varchar2(72) := g_package||'populate_params_table';
242
243 BEGIN
244 --Setting the where clause params in params table
245 params_table(1).param_name := 'p_effective_date';
246 params_table(1).param_value := fnd_date.date_to_canonical(p_effective_date);
247 params_table(1).param_data_type := 'D';
248 params_table(2).param_name := 'p_assignment_id';
249 params_table(2).param_value := 'NULL';
250 params_table(2).param_data_type := 'N';
251 params_table(3).param_name := 'p_person_id';
252 params_table(3).param_value := 'NULL';
253 params_table(3).param_data_type := 'N';
254 params_table(4).param_name := 'p_qualification_id';
255 params_table(4).param_value := 'NULL';
256 params_table(4).param_data_type := 'N';
257 --Execute the obtain_param_values cursor an populate the params_table with the values
258 FOR where_clause_param_values IN obtain_param_values Loop
259 IF UPPER(where_clause_param_values.column_name)='P_SELECTED_PERSON_ID' THEN
260 params_table(3).param_value := where_clause_param_values.column_value;
261 ELSIF NVL(params_table.COUNT,0) <> 0 THEN
262 -- loop thru the params table and set the value
263 FOR i IN NVL(params_table.FIRST,0)..NVL(params_table.LAST,-1) LOOP
264 IF UPPER(params_table(i).param_name) = UPPER(where_clause_param_values.column_name) THEN
265 params_table(i).param_value := where_clause_param_values.column_value;
266 --hr_utility.set_location('Name ' || params_table(i).param_name || ' value ' || params_table(i).param_value,8);
267 EXIT;
268 END IF;
269 END LOOP;
270 END IF;
271 End Loop;
272 END populate_params_table;
273
274 --
275 --
276 -- Conversion functions are added here
277 --
278 --
279 --
280 --
281 FUNCTION decode_value(p_lookup_code varchar2) RETURN VARCHAR2
282 IS
283 CURSOR csr_meaning IS
284 Select Meaning from hr_lookups
285 where lookup_type ='PQH_CURRENT_PROPOSED'
286 AND enabled_flag = 'Y'
287 AND lookup_code = p_lookup_code;
288
289 l_meaning hr_lookups.meaning%type := null;
290
291 BEGIN
292
293 OPEN csr_meaning;
294 FETCH csr_meaning into l_meaning;
295 CLOSE csr_meaning;
296
297 return l_meaning;
298 --
299 END decode_value;
300 --
301 --
302 --
303 FUNCTION get_salary(p_assignment_id IN per_assignments_f.assignment_type%TYPE)
304 RETURN VARCHAR2 IS
305 CURSOR salary IS
306 Select proposed_salary_n
307 From per_pay_proposals ppp
308 Where ppp.assignment_id = p_assignment_id
309 and ppp.change_date = ( select max(change_date)
310 from per_pay_proposals
311 where change_date <= g_effective_date
312 and assignment_id =
313 p_assignment_id
314 and approved = 'Y' );
315 l_salary per_pay_proposals.PROPOSED_SALARY_N%Type;
316 BEGIN
317 IF p_assignment_id IS NOT null then
318 --
319 OPEN salary;
320 FETCH salary into l_salary;
321 CLOSE salary;
322 --
323 END IF;
324 RETURN l_salary;
325 Exception
326 when others then
327 null;
328 END;
329 --
330 --
331 --
332 FUNCTION get_currency(p_pay_basis_id per_pay_bases.pay_basis_id%TYPE) RETURN VARCHAR2
333 IS
334 Cursor currency is
335 Select petf.input_currency_code
336 From per_pay_bases ppb,
337 pay_input_values_f pivf,
338 pay_element_types_f petf
339 Where ppb.pay_basis_id=p_pay_basis_id
340 And ppb.input_value_id = pivf.input_value_id
341 AND pivf.element_type_id = petf.element_type_id;
342
343 l_currency_code pay_element_types_f.input_currency_code%Type;
344 l_currency fnd_currencies.description%Type;
345
346 BEGIN
347
348 IF p_pay_basis_id IS not null Then
349 Open currency;
350 Fetch currency into l_currency_code;
351 Close currency;
352 IF l_currency_code is not null Then
353 l_currency := get_currency_meaning(l_currency_code);
354 End If;
355 End If;
356 Return l_currency;
357 Exception
358 when others then
359 null;
360 END;
361 --
362 --
363 --
364 FUNCTION get_change_amount(p_pay_proposal_id per_pay_proposals.pay_proposal_id%Type)
365 Return Varchar2
366 IS
367 CURSOR is_multiple_comp is
368 Select 1
369 From per_pay_proposals ppp
370 where ppp.pay_proposal_id = p_pay_proposal_id
371 AND ppp.MULTIPLE_COMPONENTS = 'Y';
372
373 CURSOR change_amount_cur is
374 Select CHANGE_AMOUNT
375 From per_pay_proposal_components pppc
376 where pppc.pay_proposal_id = p_pay_proposal_id;
377
378 l_change_amount per_pay_proposal_components.change_amount%Type;
379 l_multiple_comp number;
380 BEGIN
381 IF p_pay_proposal_id is not null then
382 OPEN is_multiple_comp;
383 FETCH is_multiple_comp into l_multiple_comp;
384 CLOSE is_multiple_comp;
385 IF l_multiple_comp is not null then
386 l_change_amount := null;
387 ElSE
388 OPEN change_amount_cur;
389 FETCH change_amount_cur into l_change_amount;
390 CLOSE change_amount_cur;
391 END IF;
392 END IF;
393 Return l_change_amount;
394 END;
395 --
396 --
397 --
398 FUNCTION get_change_percent(p_pay_proposal_id per_pay_proposals.pay_proposal_id%Type)
399 Return Varchar2
400 IS
401 CURSOR is_multiple_comp is
402 Select 1
403 From per_pay_proposals ppp
404 where ppp.pay_proposal_id = p_pay_proposal_id
405 AND ppp.MULTIPLE_COMPONENTS = 'Y';
406
407 CURSOR change_percent_cur is
408 Select CHANGE_PERCENTAGE
409 From per_pay_proposal_components pppc
410 where pppc.pay_proposal_id = p_pay_proposal_id;
411
412 l_change_percent per_pay_proposal_components.change_percentage%Type;
413 l_multiple_comp number;
414 BEGIN
415 IF p_pay_proposal_id is not null then
416 OPEN is_multiple_comp;
417 FETCH is_multiple_comp into l_multiple_comp;
418 CLOSE is_multiple_comp;
419 IF l_multiple_comp is not null then
420 l_change_percent := null;
421 ElSE
422 OPEN change_percent_cur;
423 FETCH change_percent_cur into l_change_percent;
424 CLOSE change_percent_cur;
425 END IF;
426 END IF;
427 Return l_change_percent;
428 END;
429 --
430 --
431 --
432 FUNCTION get_tenure_status (p_lookup_code varchar2) RETURN VARCHAR2 IS
433
434 l_meaning hr_lookups.meaning%type :=null;
435 BEGIN
436
437 SELECT hr_general.decode_lookup('PQH_TENURE_STATUS',p_lookup_code) into l_meaning
438 FROM DUAL;
439
440 return l_meaning;
441
442 END get_tenure_status;
443 --
444 --
445 FUNCTION get_qualification (p_qualification_type_id varchar2) RETURN VARCHAR2 IS
446
447 l_meaning per_qualification_types.name%type := null;
448
449 BEGIN
450
451 Select name into l_meaning
452 from per_qualification_types
453 where qualification_type_id = p_qualification_type_id;
454
455 return l_meaning;
456
457 END get_qualification;
458 --
459 --
460 FUNCTION get_award_status (p_award_id varchar2) RETURN VARCHAR2 IS
461
462 l_meaning hr_lookups.meaning%type :=null;
463
464 Cursor get_award_meaning IS
465 select meaning
466 from hr_leg_lookups
467 where lookup_type = 'PER_SUBJECT_STATUSES' and
468 lookup_code = p_award_id and
469 g_effective_date between nvl(start_date_active, g_effective_date)
470 and nvl(end_date_active, g_effective_date) and enabled_flag = 'Y'
471 order by lookup_code,meaning;
472
473 BEGIN
474
475 Open get_award_meaning;
476
477 Fetch get_award_meaning into l_meaning;
478
479 close get_award_meaning;
480
481 return l_meaning;
482
483
484 END get_award_status;
485 --
486 --
487 FUNCTION get_tuition_method (p_tuition_id varchar2) RETURN VARCHAR2 IS
488
489 Cursor cur_get_tuition_meaning is
490 SELECT
491 meaning
492 FROM
493 hr_leg_lookups
494 WHERE
495 lookup_type = 'PER_TUITION_METHODS'
496 AND lookup_code = p_tuition_id
497 AND g_effective_date BETWEEN NVL(start_date_active, g_effective_date)
498 AND NVL(end_date_active, g_effective_date)
499 AND enabled_flag = 'Y';
500
501 l_meaning hr_leg_lookups.meaning%type := null;
502 BEGIN
503
504 OPEN cur_get_tuition_meaning;
505 FETCH cur_get_tuition_meaning into l_meaning;
506 CLOSE cur_get_tuition_meaning;
507
508 return l_meaning;
509
510 END get_tuition_method;
511
512 --
513 --
514 FUNCTION get_currency_meaning(p_currency_code varchar2) RETURN VARCHAR2 IS
515
516 CURSOR csr_get_currency IS
517 SELECT
518 description
519 FROM
520 fnd_currencies_vl
521 WHERE
522 enabled_flag = 'Y'
523 AND currency_code = p_currency_code
524 AND currency_flag = 'Y'
525 AND sysdate >= NVL(start_date_active, sysdate)
526 AND sysdate <= NVL(end_date_active, sysdate);
527
528 l_meaning fnd_currencies.description%type := null;
529
530 BEGIN
531
532 OPEN csr_get_currency;
533 FETCH csr_get_currency into l_meaning;
534 CLOSE csr_get_currency;
535
536 return l_meaning;
537 --
538 END get_currency_meaning;
539 --
540 --
541 FUNCTION get_person_title(p_title_code varchar2) RETURN VARCHAR2 IS
542
543 cursor csr_get_title IS
544 select hl.meaning
545 from hr_lookups hl
546 where hl.lookup_type = 'TITLE'
547 and hl.lookup_code = p_title_code
548 and hl.enabled_flag = 'Y'
549 and trunc(g_effective_date) between trunc(nvl(hl.start_date_active,g_effective_date))
550 and trunc(nvl(hl.end_date_active,g_effective_date));
551
552 l_meaning hr_lookups.meaning%type := null;
553
554 BEGIN
555
556 OPEN csr_get_title;
557 FETCH csr_get_title into l_meaning;
558 CLOSE csr_get_title;
559
560 return l_meaning;
561 --
562 END get_person_title;
563 --
564 --
565 FUNCTION get_gender (p_gender_code varchar2) RETURN VARCHAR2 IS
566
567 CURSOR csr_gender IS
568 select hrl.meaning
569 from hr_lookups hrl where
570 hrl.lookup_type = 'SEX' and
571 hrl.Lookup_Code = p_gender_code and
572 hrl.enabled_flag = 'Y' and
573 trunc(SYSDATE) between trunc(nvl(hrl.start_date_active,SYSDATE))
574 and trunc(nvl(hrl.end_date_active,SYSDATE));
575
576 l_meaning hr_lookups.meaning%type := null;
577
578 BEGIN
579
580 OPEN csr_gender;
581
582 FETCH csr_gender into l_meaning;
583 CLOSE csr_gender;
584
585 return l_meaning;
586 --
587 END get_gender;
588 --
589 --
590 FUNCTION get_marital_status (p_marital_code varchar2) RETURN VARCHAR2 IS
591
592 CURSOR csr_marital_status IS
593 select hl.meaning
594 from hr_lookups hl
595 where hl.lookup_type = 'MAR_STATUS'
596 and hl.lookup_code = p_marital_code
597 and hl.enabled_flag = 'Y'
598 and g_effective_date between nvl(start_date_active, g_effective_date - 1)
599 and nvl(end_date_active, g_effective_date);
600
601 l_meaning hr_lookups.meaning%type := null;
602
603 BEGIN
604
605 OPEN csr_marital_status;
606 FETCH csr_marital_status into l_meaning;
607 CLOSE csr_marital_status;
608
609 return l_meaning;
610
611 END get_marital_status;
612 --
613 --
614 FUNCTION get_termination_reason (p_termination_code varchar2) RETURN VARCHAR2
615 IS
616 CURSOR csr_reason IS
617 SELECT meaning
618 FROM hr_leg_lookups
619 WHERE lookup_type = 'LEAV_REAS'
620 AND lookup_code = p_termination_code
621 AND enabled_flag = 'Y';
622
623 l_meaning hr_lookups.meaning%type := null;
624
625 BEGIN
626
627
628 OPEN csr_reason;
629
630 FETCH csr_reason into l_meaning;
631
632 CLOSE csr_reason;
633
634
635 return l_meaning;
636
637 END get_termination_reason;
638 --
639 --
640 FUNCTION get_work_schedule_frequency (p_freq_code varchar2) RETURN VARCHAR2
641 IS
642
643 CURSOR csr_frequency IS
644 select hl.meaning
645 from hr_leg_lookups hl
646 where hl.lookup_type = 'FREQUENCY'
647 and hl.lookup_code = p_freq_code
648 and hl.enabled_flag = 'Y'
649 and trunc(g_effective_date) BETWEEN nvl(hl.start_date_active, trunc(g_effective_date))
650 AND nvl(hl.end_date_active, trunc(g_effective_date));
651
652 l_meaning hr_lookups.meaning%type := null;
653 BEGIN
654
655 OPEN csr_frequency ;
656
657 FETCH csr_frequency into l_meaning;
658
659 CLOSE csr_frequency;
660
661 return l_meaning;
662
663 END get_work_schedule_frequency;
664 --
665 --
666 FUNCTION get_employee_category (p_category_code varchar2) RETURN VARCHAR2
667 IS
668
669 CURSOR csr_category IS
670 select hl.meaning
671 from hr_leg_lookups hl
672 where hl.lookup_type = 'EMPLOYEE_CATG'
673 and hl.lookup_code = p_category_code
674 and hl.enabled_flag = 'Y'
675 and trunc(g_effective_date) between nvl(start_date_active, trunc(g_effective_date))
676 and nvl(end_date_active, trunc(g_effective_date));
677 l_meaning hr_lookups.meaning%type := null;
678 BEGIN
679
680 OPEN csr_category;
681 FETCH csr_category into l_meaning;
682 CLOSE csr_category;
683
684 return l_meaning;
685
686 END get_employee_category;
687 --
688 --
689 FUNCTION get_employment_category (p_category_code varchar2) RETURN VARCHAR2
690 IS
691
692 CURSOR csr_category IS
693 select hl.meaning
694 from hr_leg_lookups hl
695 where hl.lookup_type = 'EMP_CAT'
696 and hl.lookup_code = p_category_code
697 and hl.enabled_flag = 'Y'
698 and trunc(g_effective_date) between nvl(start_date_active, trunc(g_effective_date))
699 and nvl(end_date_active, trunc(g_effective_date));
700 l_meaning hr_lookups.meaning%type := null;
701 BEGIN
702
703 OPEN csr_category;
704 FETCH csr_category into l_meaning;
705 CLOSE csr_category;
706
707 return l_meaning;
708
709 END get_employment_category;
710 --
711 --
712 FUNCTION get_yes_no (p_lookup_code varchar2) RETURN VARCHAR2
713 IS
714 CURSOR csr_meaning IS
715 Select Meaning from hr_lookups
716 where lookup_type ='YES_NO'
717 AND enabled_flag = 'Y'
718 AND lookup_code = p_lookup_code;
719
720 l_meaning hr_lookups.meaning%type := null;
721
722 BEGIN
723
724 OPEN csr_meaning;
725 FETCH csr_meaning into l_meaning;
726 CLOSE csr_meaning;
727
728 return l_meaning;
729 --
730 END get_yes_no;
731
732 --
733 --
734
735 FUNCTION get_establishment (p_establishment_id varchar2) RETURN VARCHAR2
736 IS
737 CURSOR csr_establishment IS
738 SELECT
739 NAME SCHOOL
740 FROM
741 PER_ESTABLISHMENTS
742 Where ESTABLISHMENT_ID = p_establishment_id;
743
744 l_meaning hr_lookups.meaning%type := null;
745
746 BEGIN
747
748 OPEN csr_establishment;
749 FETCH csr_establishment into l_meaning;
750 CLOSE csr_establishment;
751
752 return l_meaning;
753 --
754 END get_establishment;
755 --
756 --
757
758 FUNCTION get_person_latest_name (p_person_id varchar2) RETURN VARCHAR2 --Not Used for Now
759 IS
760
761 l_full_name varchar2(240);
762 --
763
764 BEGIN
765
766 if p_person_id is not null then
767 --
768 l_full_name := hr_person_name.get_person_name(p_person_id,trunc(sysdate));
769 --
770 end if;
771 return l_full_name;
772 --
773 END get_person_latest_name;
774 --
775 --
776 FUNCTION get_person_brief_name (p_person_id varchar2) RETURN VARCHAR2 IS
777 l_brief_name varchar2(240);
778 --
779 cursor csr_brief_name is
780 select first_name||' '||last_name||' '||suffix brief_name
781 from per_all_people_f ppf
782 where ppf.person_id = p_person_id
783 order by effective_end_date desc;
784
785
786 BEGIN
787
788 if p_person_id is not null then
789 --
790 open csr_brief_name;
791 fetch csr_brief_name into l_brief_name;
792 close csr_brief_name;
793 --
794 end if;
795 return l_brief_name;
796 --
797
798 END get_person_brief_name;
799 --
800 --
801 FUNCTION decode_payroll_latest_name (p_payroll_id varchar2) RETURN VARCHAR2
802 IS
803 l_payroll_name pay_all_payrolls_f.payroll_name%type;
804 Cursor csr_payroll_name IS
805 select payroll_name
806 from pay_all_payrolls_f pay
807 where payroll_id = p_payroll_id
808 and g_effective_date between
809 pay.effective_start_date and pay.effective_end_date
810 order by effective_start_date desc;
811
812 BEGIN
813
814
815 OPEN csr_payroll_name;
816 FETCH csr_payroll_name into l_payroll_name;
817 CLOSE csr_payroll_name;
818
819 return l_payroll_name;
820
821 END decode_payroll_latest_name;
822 --
823 --
824 FUNCTION decode_bargaining_unit_code (p_bargaining_unit_code IN VARCHAR2)
825 RETURN VARCHAR2 IS
826 --
827 BEGIN
828 return (hr_general.decode_lookup ('BARGAINING_UNIT_CODE',p_bargaining_unit_code));
829 END decode_bargaining_unit_code;
830 --
831 FUNCTION decode_collective_agreement(p_collective_agreement_id IN NUMBER)
832 RETURN VARCHAR2 IS
833 --
834 CURSOR csr_collective_agreement(p_collective_agreement_id NUMBER) IS
835 SELECT name
836 FROM per_collective_agreements
837 WHERE collective_agreement_id = p_collective_agreement_id;
838 --
839 l_name per_collective_agreements.name%TYPE := null;
840 --
841 BEGIN
842 --
843 IF p_collective_agreement_id IS NOT NULL THEN
844 --
845 OPEN csr_collective_agreement(p_collective_agreement_id);
846 FETCH csr_collective_agreement INTO l_name;
847 CLOSE csr_collective_agreement;
848 --
849 END IF;
850
851 RETURN l_name;
852 --
853 END decode_collective_agreement;
854 --
855 --
856 FUNCTION decode_contract(p_contract_id IN NUMBER)
857 RETURN VARCHAR2 IS
858 --
859 CURSOR csr_contract(p_contract_id NUMBER) IS
860 SELECT reference
861 FROM per_contracts_f
862 WHERE contract_id = p_contract_id
863 AND g_effective_date between effective_start_date and effective_end_date;
864 --
865 l_reference per_contracts_f.reference%TYPE := null;
866 --
867 BEGIN
868 --
869 IF p_contract_id IS NOT NULL THEN
870 --
871 OPEN csr_contract(p_contract_id);
872 FETCH csr_contract INTO l_reference;
873 CLOSE csr_contract;
874 --
875 END IF;
876
877 RETURN l_reference;
878 --
879 END decode_contract;
880 --
881 --
882 FUNCTION get_table_route_id(p_table_alias IN varchar2 ) RETURN VARCHAR2
883 IS
884 --
885 CURSOR csr_table_route IS
886 Select TABLE_ROUTE_ID
887 from pqh_table_route
888 where table_alias like 'PQH_SS%';
889
890 l_table_route_id pqh_table_route.table_route_id%type := null;
891 BEGIN
892 --
893 if p_table_alias is not null then
894 --
895 open csr_table_route ;
896 fetch csr_table_route into l_table_route_id;
897 close csr_table_route;
898 --
899 end if;
900
901 return l_table_route_id;
902 --
903 END get_table_route_id;
904 --
905 --
906 FUNCTION get_user_status(p_assignment_status_type_id IN NUMBER) RETURN VARCHAR2
907 IS
908 CURSOR csr_user_status IS
909 select tl.user_status
910 from per_assignment_status_types asg,
911 per_assignment_status_types_tl tl
912 where asg.assignment_status_type_id = p_assignment_status_type_id
913 and asg.assignment_status_type_id = tl.assignment_status_type_id
914 and tl.language=USERENV('LANG');
915 --
916 l_user_status per_assignment_status_types_tl.user_status%type :=null;
917 --
918 BEGIN
919
920 open csr_user_status ;
921 FETCH csr_user_status into l_user_status;
922 close csr_user_status;
923 --
924 return l_user_status;
925
926 END get_user_status;
927 --
928 --
929 FUNCTION get_change_reason(p_reason_code IN VARCHAR2) RETURN VARCHAR2
930 IS
931 CURSOR csr_change_reason IS
932 select meaning
933 from hr_leg_lookups
934 where lookup_type = 'EMP_ASSIGN_REASON'
935 and enabled_flag = 'Y'
936 AND trunc(g_effective_date) BETWEEN NVL(start_date_active, trunc(g_effective_date))
937 AND NVL(end_date_active, trunc(g_effective_date))
938 and lookup_code =p_reason_code
939 UNION
940 select meaning
941 from hr_leg_lookups
942 where lookup_type = 'CWK_ASSIGN_REASON'
943 and enabled_flag = 'Y'
944 AND trunc(g_effective_date) BETWEEN NVL(start_date_active,
945 trunc(g_effective_date))
946 AND NVL(end_date_active, trunc(g_effective_date))
947 and lookup_code =p_reason_code;
948 --
949 l_reason varchar2(100):=null;
950 l_proc VARCHAR2(72):= 'get_change_reason';
951 --
952 BEGIN
953 hr_utility.set_location('Leaving: ' || l_proc,10);
954 --
955 open csr_change_reason ;
956 FETCH csr_change_reason into l_reason;
957 close csr_change_reason;
958 --
959 hr_utility.set_location('Leaving: ' || l_proc,20);
960
961 return l_reason;
962 END get_change_reason;
963 --
964 --
965 --
966 PROCEDURE purge_temp_data(p_effective_date date default trunc(sysdate))
967 IS
968 PRAGMA AUTONOMOUS_TRANSACTION;
969 --
970 l_proc VARCHAR2(72);
971 --
972 Begin
973 --
974 l_proc := g_package || 'purge_temp_data';
975
976 hr_utility.set_location('Leaving: ' || l_proc,10);
977
978 Delete
979 from pqh_ss_print_label_temp
980 where trunc(creation_date) < p_effective_date;
981 -- Issuing autonomous commit
982 COMMIT;
983 hr_utility.set_location('Leaving: ' || l_proc,20);
984 --
985 EXCEPTION
986 WHEN OTHERS THEN ROLLBACK;
987 --
988 End purge_temp_data;
989 --
990
991 FUNCTION get_value_from_params_table( p_column_name IN pqh_attributes.column_name%TYPE)
992 RETURN VARCHAR2 IS
993 -- local variables
994 --
995 l_proc varchar2(72) := g_package||'get_value_from_array';
996 l_col_val VARCHAR2(8000) := null;
997 l_col_type VARCHAR2(1) := null;
998 BEGIN
999
1000 hr_utility.set_location('Entering:'||l_proc, 5);
1001 hr_utility.set_location('Col Name : '||p_column_name, 6);
1002
1003 IF NVL(params_table.COUNT,0) <> 0 THEN
1004 -- loop thru the params table and get the value
1005 FOR i IN NVL(params_table.FIRST,0)..NVL(params_table.LAST,-1) LOOP
1006 IF UPPER(params_table(i).param_name) = UPPER(p_column_name) THEN
1007 l_col_val := params_table(i).param_value;
1008 l_col_type := params_table(i).param_data_type;
1009 IF l_col_type = 'D' THEN
1010 l_col_val := ' fnd_date.canonical_to_date(''' || l_col_val || ''')';
1011 ELSIF l_col_type = 'V' THEN
1012 l_col_val := '''' || l_col_val || '''';
1013 END IF;
1014 hr_utility.set_location('Name ' || p_column_name || ' value ' || l_col_val,8);
1015 EXIT;
1016 -- exit the loop as the column is found
1017 END IF;
1018 END LOOP;
1019 END IF;
1020 RETURN l_col_val;
1021 END get_value_from_params_table;
1022
1023 PROCEDURE replace_where_clause_params(p_where_clause_in IN PQH_TABLE_ROUTE.where_clause%TYPE,
1024 p_where_clause_out OUT NOCOPY PQH_TABLE_ROUTE.where_clause%TYPE)
1025 IS
1026 l_proc varchar2(72) := g_package||'replace_where_clause_params';
1027 l_atoms_tab PQH_REFRESH_DATA.atoms_tabtype;
1028 -- to hold the where_clause atoms
1029 l_no_atoms number;
1030 l_key_column pqh_attributes.column_name%TYPE;
1031 l_key_val VARCHAR2(8000);
1032 l_where_out pqh_table_route.where_clause%TYPE;
1033 l_atom_length number;
1034 BEGIN
1035 hr_utility.set_location('Entering:'||l_proc, 5);
1036 PQH_REFRESH_DATA.parse_string(
1037 p_string_in => p_where_clause_in,
1038 p_atomics_list_out => l_atoms_tab,
1039 p_num_atomics_out => l_no_atoms
1040 );
1041
1042 -- loop thru the PL/SQL table and replace params
1043 FOR table_row IN NVL(l_atoms_tab.FIRST,0)..NVL(l_atoms_tab.LAST,-1) LOOP
1044 l_atom_length := LENGTH(TRIM(l_atoms_tab(table_row)));
1045 IF substr(NVL (l_atoms_tab(table_row), 'NULL') ,1,1) = '<' and l_atom_length > 2 THEN
1046 l_key_column := substr(l_atoms_tab(table_row),2,(l_atom_length - 2)) ;
1047 l_key_val := get_value_from_params_table(p_column_name => l_key_column);
1048 hr_utility.set_location(l_key_column||' = '||l_key_val,15);
1049 -- if value is null pass it null
1050 -- replace the param with the actual value
1051 l_atoms_tab(table_row) := l_key_val;
1052 END IF;
1053 END LOOP;
1054
1055 -- build the where clause again
1056 l_where_out := '';
1057 FOR table_row IN NVL(l_atoms_tab.FIRST,0)..NVL(l_atoms_tab.LAST,-1)
1058 LOOP
1059 l_where_out := l_where_out||nvl(l_atoms_tab(table_row),' ');
1060 END LOOP;
1061
1062 -- assign the out parameter the final where string
1063 p_where_clause_out := l_where_out;
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 p_where_clause_out := null;
1068 END replace_where_clause_params;
1069
1070
1071 /*----------------------------------------------------------------------------------
1072 POPULATE_CWB_PARAMS: Populate the global parameters table with the values
1073 that will be bound to the where clause later.
1074 ------------------------------------------------------------------------------------*/
1075 procedure populate_cwb_params (
1076 p_group_per_in_ler_id in number,
1077 p_group_plan_id in number, p_lf_evt_ocrd_dt
1078 in date)is
1079 begin
1080 hr_utility.set_location('Entering populate_cwb_params',1);
1081 --Setting the where clause params in params table
1082 params_table(1).param_name := 'P_GROUP_PER_IN_LER_ID';
1083 params_table(1).param_value := p_group_per_in_ler_id;
1084 params_table(1).param_data_type := 'N';
1085
1086 params_table(2).param_name := 'P_GROUP_PLAN_ID';
1087 params_table(2).param_value := p_group_plan_id;
1088 params_table(2).param_data_type := 'N';
1089
1090 params_table(3).param_name := 'P_LIFE_EVENT_DATE';
1091 params_table(3).param_value := p_lf_evt_ocrd_dt;
1092 params_table(3).param_data_type := 'V';
1093 hr_utility.set_location('Leaving populate_cwb_params',2);
1094 end populate_cwb_params;
1095
1096 /*----------------------------------------------------------------------------------
1097 POPULATE_DATA_FROM_FF: Common routine to execute the fast formula defined for the
1098 document. The input values are bound before executing the same.
1099
1100 For SSHR Following inputs are available:
1101 P_EFFECTIVE_DATE
1102 P_TRANS_ID
1103
1104 For CWB
1105 P_GROUP_PER_IN_LER_ID
1106 P_GROUP_PLAN_ID
1107 P_PLAN_ID
1108 P_LIFE_EVENT_DATE
1109 ------------------------------------------------------------------------------------*/
1110 procedure populate_data_from_ff (
1111 p_doc_short_name in varchar2,
1112 p_effective_date in date,
1113 p_transaction_id in number default null,
1114 p_group_per_in_ler_id in number default null,
1115 p_group_plan_id in number default null,
1116 p_lf_evt_ocrd_dt in date default null) is
1117
1118 --Fast Formula cursors
1119 Cursor get_fastformula(p_short_name varchar2) is
1120 Select formula_id
1121 from pqh_documents_f
1122 where p_effective_date between effective_start_date and effective_end_date
1123 and short_name = p_short_name;
1124
1125 Cursor cur_chk_forumual_effective(p_eff_date date , p_formula_id number) is
1126 Select 'X', userenv('sessionid')
1127 from ff_formulas_f
1128 where formula_id = p_formula_id
1129 and p_eff_date between effective_start_date and effective_end_date;
1130 --
1131 -- Defining local variables.
1132 --
1133 l_inputs FF_EXEC.INPUTS_T;
1134 l_outputs FF_EXEC.OUTPUTS_T;
1135
1136 l_session_id NUMBER;
1137 l_formula_id NUMBER;
1138 l_formula_existance VARCHAR2(10);
1139 BEGIN
1140
1141 OPEN get_fastformula(p_doc_short_name);
1142 FETCH get_fastformula into l_formula_id;
1143 CLOSE get_fastformula;
1144
1145 IF l_formula_id IS NOT NULL THEN
1146 --
1147 Open cur_chk_forumual_effective(p_effective_date , l_formula_id);
1148 Fetch cur_chk_forumual_effective into l_formula_existance, l_session_id;
1149 Close cur_chk_forumual_effective;
1150
1151 If l_formula_existance = 'X' then
1152 FF_EXEC.INIT_FORMULA(l_formula_id, p_effective_date,l_inputs,l_outputs);
1153
1154 for l_in_cnt in 1..l_inputs.COUNT loop
1155 if(l_inputs(l_in_cnt).name = 'P_SESSION_ID') then
1156 l_inputs(l_in_cnt).value:= l_session_id;
1157 --
1158 elsif(l_inputs(l_in_cnt).name = 'P_TRANS_ID') then
1159 l_inputs(l_in_cnt).value:=p_transaction_id;
1160 --
1161 elsif(l_inputs(l_in_cnt).name = 'P_EFFECTIVE_DATE') then
1162 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(p_effective_date);
1163 l_inputs(l_in_cnt).datatype := 'DATE';
1164 --
1165 elsif(l_inputs(l_in_cnt).name = 'P_GROUP_PER_IN_LER_ID') then
1166 l_inputs(l_in_cnt).value := p_group_per_in_ler_id;
1167 --
1168 elsif(l_inputs(l_in_cnt).name = 'P_GROUP_PLAN_ID') then
1169 l_inputs(l_in_cnt).value := p_group_plan_id;
1170 --
1171 elsif(l_inputs(l_in_cnt).name = 'P_LIFE_EVENT_DATE') then
1172 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(p_lf_evt_ocrd_dt);
1173 l_inputs(l_in_cnt).datatype := 'DATE';
1174 --
1175 end if;
1176 end loop;
1177 g_transaction_id := p_transaction_id;
1178 FF_EXEC.RUN_FORMULA(l_inputs,l_outputs);
1179 --
1180 End If; -- formula existance
1181 END IF ;-- formula id not null
1182 END populate_data_from_ff;
1183
1184 /*----------------------------------------------------------------------------------
1185 POPULATE_DATA_FROM_QUERY: Common routine to build the dynamic query using the
1186 from & where clause from pqh_table_route, bind the variables in where clause
1187 and execute the query.
1188 ------------------------------------------------------------------------------------*/
1189 procedure populate_data_from_query (
1190 p_doc_short_name in varchar2,
1191 p_effective_date in date,
1192 p_transaction_id in number )
1193 is
1194 l_no_cols integer :=0;
1195 p integer :=0;
1196 columnval varchar2(32000) :=null;
1197 sql_query varchar2(32000) :=null;
1198 columntag varchar2(32000) :=null;
1199 l_statement varchar2(32000) ;
1200
1201 l_where_clause_out PQH_TABLE_ROUTE.where_clause%TYPE;
1202
1203 /* Removed the RECORD TYPE current_values_record in version 120.5 as selecting into this record type was limiting the attributes to 42. */
1204
1205 /*Bug fix 4722431*/
1206
1207 --this cursor decides what are the table_route_id for which query need to be done depending on the
1208 --It also selects the from_clause and the where clause for the table_route_id
1209 cursor table_routes is
1210 select distinct att.master_table_route_id table_rt_id,
1211 ptr.from_clause from_clause,
1212 ptr.where_clause where_clause,
1213 doc.document_category
1214 from pqh_documents_f doc,
1215 pqh_document_attributes_f doa,
1216 pqh_attributes att,
1217 pqh_table_route ptr
1218 where doc.short_name = p_doc_short_name
1219 AND doa.document_id = doc.document_id
1220 AND p_effective_date between doc.effective_start_date and doc.effective_end_date
1221 AND p_effective_date between doa.effective_start_date and doa.effective_end_date
1222 AND att.attribute_id = doa.attribute_id
1223 AND att.master_table_route_id=ptr.table_route_id
1224 AND ptr.where_clause is not null;
1225 -- This curosr selects the column names and tag anme that need to be queried to form a dynamic sql query
1226 cursor column_tag_names(p_table_route_id pqh_table_route.table_route_id %TYPE) is
1227 select att.column_name att_column,doa.tag_name doc_tag,att.enable_flag flag
1228 from pqh_documents_f doc,
1229 pqh_document_attributes_f doa,
1230 pqh_attributes att
1231 where doc.short_name = p_doc_short_name
1232 AND doa.document_id = doc.document_id
1233 AND p_effective_date between doc.effective_start_date and doc.effective_end_date
1234 AND p_effective_date between doa.effective_start_date and doa.effective_end_date
1235 AND att.attribute_id = doa.attribute_id
1236 AND att.master_table_route_id=p_table_route_id
1237 order by att.attribute_id;
1238
1239 begin
1240 hr_utility.set_location('Entering populate_data_from_query',1);
1241 -- populate_params_table(p_Transaction_Id ,p_effective_date );
1242 --Loop starts for each table_route
1243 For table_route_rslt IN table_routes LOOP
1244 -- columns are retrieved corresponding to a document tags for the table_route_id
1245 hr_utility.set_location('Table Route Id'||table_route_rslt.table_rt_id,11);
1246 For column_name_r IN column_tag_names(table_route_rslt.table_rt_id) Loop
1247 IF l_no_cols > 0 THEN
1248 columnval:=columnval||',' ;
1249 columntag:=columntag||',' ;
1250 END IF;
1251 l_no_cols :=l_no_cols+1;
1252 columnval:=columnval || column_name_r.att_column ||' Val'||l_no_cols ;
1253 columntag:=columntag ||''''|| column_name_r.doc_tag ||''' Tag'||l_no_cols ;
1254 End Loop;
1255
1256 replace_where_clause_params(table_route_rslt.where_clause,l_where_clause_out);
1257
1258 sql_query := 'Select '|| columntag||','||columnval ||' From ' || table_route_rslt.from_clause ||' Where ' || l_where_clause_out ;
1259
1260
1261 --insert into y values (sql_query);commit;
1262 /*Bug fix 4722431*/
1263
1264 /* Added dynamic cursor to fix the issue with being able to get values for only 42 attributes */
1265
1266 --DYNAMIC CURSOR BEGIN
1267 Begin
1268 for i in 1.. l_no_cols
1269 Loop
1270 l_statement := '
1271 DECLARE
1272 cursor dyn_cur is '||sql_query||';
1273 l_rec dyn_cur%rowType;
1274 BEGIN
1275 open dyn_cur;
1276 fetch dyn_cur into l_rec;
1277 close dyn_cur;
1278
1279 INSERT INTO pqh_ss_print_data(session_id,transaction_id,name,value) values(userenv(''sessionid''),'||p_transaction_id||',l_rec.tag'||i||',l_rec.val'||i||');
1280 END;';
1281 EXECUTE IMMEDIATE l_statement;
1282 End Loop;
1283
1284 Exception
1285 --All the exceptions have been supressed so that exception in the
1286 --query execution of one table route does not prevent the query execution of other
1287 --table routes
1288 WHEN others then
1289 l_no_cols:=0;
1290 columnval := null;
1291 columntag := null;
1292 sql_query := null;
1293 l_statement := null;
1294 l_where_clause_out := null;
1295 hr_utility.set_location('Query execution generated Error: '||sqlerrm,10);
1296
1297 End;
1298 END Loop;
1299 --
1300 hr_utility.set_location('Leaving populate_data_from_query',1);
1301 end;
1302
1303
1304 /* ----------------------------------------------------------------------------------
1305 POPULATE_TEMP_DATA: Procedure to populate the temporary table with SSHR data
1306 ---------------------------------------------------------------------------------- */
1307 --
1308 -- The following procedure used to populate temporary table data.
1309 --
1310 PROCEDURE populate_temp_data(
1311 p_Transaction_Id IN VARCHAR2,
1312 p_session_id OUT NOCOPY VARCHAR2,
1313 p_effective_date IN DATE,
1314 p_doc_short_name IN VARCHAR2 ) IS
1315
1316 --
1317 -- Defining local variables.
1318 --
1319 l_pdf_short_name VARCHAR2(100);
1320 l_session_id NUMBER;
1321 l_approval_type VARCHAR2(10);
1322 l_proc VARCHAR2(100) := ' populate_temp_data';
1323 l_document_category VARCHAR2(30);
1324
1325 tag_no integer :=0;
1326 p integer :=0;
1327 l_no_cols integer :=0;
1328
1329 l_where_clause_out PQH_TABLE_ROUTE.where_clause%TYPE;
1330
1331 BEGIN
1332 --
1333 -- Create a save point
1334 --
1335 hr_utility.set_location('Entering: ' || l_proc,10);
1336 SAVEPOINT POPULATE_TEMP_DATA;
1337 g_effective_date := p_effective_date;
1338 g_transaction_id := p_transaction_id;
1339
1340 l_approval_type := get_function_parameter_value(
1341 p_parameter_name=>'TYPE',
1342 p_transaction_id =>p_transaction_id,
1343 p_effective_date=>fnd_date.date_to_canonical(p_effective_date));
1344
1345
1346 --dt_fndate.set_effective_date(fnd_date.canonical_to_date(p_effective_date));
1347
1348 hr_util_misc_ss.setEffectiveDate(p_effective_date);
1349 l_pdf_short_name := p_doc_short_name;
1350
1351
1352
1353 hr_utility.set_location('Effective Date' || p_effective_date,12);
1354 hr_utility.set_location('Document Short Name' || l_pdf_short_name,12);
1355
1356 select userenv('sessionid') into p_session_id from dual;
1357
1358
1359 IF l_pdf_short_name <> 'N/A' THEN
1360
1361
1362 IF l_approval_type = 'PRE' or l_approval_type = 'N/A' THEN
1363
1364 hr_utility.set_location('Pre approval type data insertion',15);
1365 --
1366 -- Retrieve data from transaction tables
1367 --
1368 BEGIN
1369 INSERT INTO
1370 pqh_ss_print_data(session_id,transaction_id,name,value,enable_flag)
1371 SELECT userenv('sessionid'),p_transaction_id,
1372 doa.tag_name NAME,
1373 decode(att.decode_function_name,null,atv.varchar2_value|| Atv.Number_value||Atv.date_value,
1374 pqh_ss_utility.get_desc (
1375 decode(atv.varchar2_value|| Atv.Number_value||FND_DATE.date_to_canonical(Atv.date_value),
1376 null,null,
1377 att.decode_function_name||'('''||atv.varchar2_value|| Atv.Number_value
1378 ||FND_DATE.date_to_canonical(Atv.date_value)||''')' ) )) value,
1379 att.enable_flag
1380 FROM hr_api_transaction_steps steps
1381 , hr_api_transaction_values atv,
1382 pqh_documents_f doc,
1383 pqh_document_attributes_f doa,
1384 pqh_attributes att,
1385 pqh_table_route ptr
1386 -- Bug Fix 2945716,Added Select Stmt to retrieve max step_id
1387 WHERE atv.transaction_step_id in (Select max(transaction_step_id)
1388 From hr_api_transaction_steps
1389 where transaction_id = p_Transaction_Id
1390 group by api_name
1391 )
1392 AND atv.transaction_step_id = steps.transaction_step_id
1393 AND doc.short_name = l_pdf_short_name --'TP'
1394 AND doa.document_id = doc.document_id
1395 AND p_effective_date between doc.effective_start_date and doc.effective_end_date
1396 AND p_effective_date between doa.effective_start_date and doa.effective_end_date
1397 AND att.attribute_id = doa.attribute_id
1398 AND att.enable_flag='Y'
1399 AND att.master_table_route_id=ptr.table_route_id
1400 AND att.COLUMN_NAME = atv.name
1401 AND ptr.from_clause=steps.api_name;
1402 hr_utility.set_location('Data Inserted',18);
1403 Exception
1404 when others then
1405 null;
1406 End;
1407 --
1408 -- Else check the l_approval_type
1409 --
1410 ELSIF l_approval_type = 'POST' THEN
1411 --
1412 -- Retrieve data from History Tables
1413 --
1414 hr_utility.set_location('Post approval type data insertion',15);
1415 BEGIN
1416 INSERT INTO
1417 pqh_ss_print_data(session_id,transaction_id,name,value,enable_flag)
1418 SELECT userenv('sessionid'),p_transaction_id,
1419 doa.tag_name NAME,
1420 decode(att.decode_function_name,null,atv.value,
1421 pqh_ss_utility.get_desc (
1422 decode(atv.value,null,null,
1423 att.decode_function_name||'('''||atv.value||''')' ) )) value,
1424 att.enable_flag
1425 FROM pqh_ss_step_history steps
1426 , pqh_ss_value_history atv,
1427 pqh_documents_f doc,
1428 pqh_document_attributes_f doa,
1429 pqh_attributes att,
1430 pqh_table_route ptr
1431 WHERE steps.step_history_id = atv.step_history_id
1432 AND steps.approval_history_id = atv.approval_history_id
1433 AND (atv.step_history_id,atv.approval_history_id) in
1434 (
1435 Select step_history_id, approval_history_id
1436 from pqh_ss_step_history
1437 where transaction_history_id=p_transaction_id
1438 and approval_history_id = (
1439 Select max(approval_history_id) from pqh_ss_step_history
1440 where transaction_history_id=p_transaction_id)
1441 )
1442 AND steps.transaction_history_id =p_transaction_id
1443 AND doc.short_name = l_pdf_short_name
1444 AND doa.document_id = doc.document_id
1445 AND p_effective_date between doc.effective_start_date and doc.effective_end_date
1446 AND p_effective_date between doa.effective_start_date and doa.effective_end_date
1447 AND att.attribute_id = doa.attribute_id
1448 AND att.enable_flag='Y'
1449 AND att.master_table_route_id =ptr.table_route_id
1450 AND att.COLUMN_NAME = atv.name
1451 AND ptr.from_clause=steps.api_name ;
1452 hr_utility.set_location('Data Inserted',18);
1453 Exception
1454 when others then
1455 null;
1456 End;
1457 --
1458 --
1459 END IF;
1460
1461 --
1462 --Fetching Proposed values from Transaction Table and Populating pqh_ss_print_data ENDS
1463 --
1464 --
1465 --This section Fetches data from base table and populates the pq_ss_print_data table
1466 --
1467
1468 --This procedure will populate the global params_table with the where_clause_params values
1469 populate_params_table(p_Transaction_Id ,p_effective_date );
1470
1471 populate_data_from_query (
1472 p_doc_short_name => p_doc_short_name,
1473 p_effective_date => p_effective_date,
1474 p_transaction_id => p_transaction_id );
1475 --
1476 END IF;
1477
1478 populate_data_from_ff (
1479 p_doc_short_name => p_doc_short_name,
1480 p_effective_date => p_effective_date,
1481 p_transaction_id => p_transaction_id );
1482 --
1483 -- Check is there any record in pqh_ss_print_data , if not insert one dummy row , so that
1484 -- while opening pdf file , xdo will not error out .
1485 --
1486 hr_utility.set_location('Leaving: '|| l_proc ,20);
1487 EXCEPTION
1488
1489 WHEN DUP_VAL_ON_INDEX THEN
1490 NULL;
1491 WHEN OTHERS THEN
1492 NULL;
1493 hr_utility.set_location('Leaving with Other exception '|| l_proc ,20);
1494 --
1495 End populate_temp_data;
1496
1497 /* ----------------------------------------------------------------------------------
1498 POPULATE_CWB_DATA: Procedure to populate the temporary table with CWB specific
1499 data for the specified context.
1500 ---------------------------------------------------------------------------------- */
1501 procedure populate_cwb_data(
1502 p_group_per_in_ler_id in number,
1503 p_group_plan_id in number,
1504 p_lf_evt_ocrd_dt in date,
1505 p_doc_short_name in varchar2,
1506 p_session_id out nocopy varchar2,
1507 p_effective_date in date default sysdate) is
1508
1509 begin
1510 --
1511 SAVEPOINT POPULATE_TEMP_DATA;
1512 --
1513 populate_cwb_params(p_group_per_in_ler_id, p_group_plan_id, p_lf_evt_ocrd_dt);
1514 --
1515 populate_data_from_query(p_doc_short_name,p_effective_date, p_group_per_in_ler_id);
1516 --
1517 populate_data_from_ff (
1518 p_doc_short_name => p_doc_short_name,
1519 p_effective_date => p_effective_date,
1520 p_transaction_id => p_group_per_in_ler_id,
1521 p_group_per_in_ler_id => p_group_per_in_ler_id,
1522 p_group_plan_id => p_group_plan_id,
1523 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt );
1524 --
1525 p_session_id := userenv('sessionid');
1526 --
1527 end populate_cwb_data;
1528 --
1529
1530 END ;
[Home] [Help]