1 PACKAGE BODY PAY_PYCAROEP_XMLP_PKG AS
2 /* $Header: PYCAROEPB.pls 120.0 2007/12/28 06:48:37 srikrish noship $ */
3
4 function BeforeReport return boolean is
5
6 CURSOR csr_get_trace_param is
7 select upper(parameter_value)
8 from pay_action_parameters
9 where parameter_name='TRACE';
10
11 CURSOR csr_get_person(p_person_id number) is
12 select full_name
13 from per_all_people_f
14 where person_id=p_person_id;
15
16 CURSOR csr_get_sel_type(p_selection_type varchar2) is
17 select meaning
18 from hr_lookups
19 where lookup_type = 'PAY_CA_ROE_SELECTION_TYPE'
20 and lookup_code = p_selection_type;
21
22 l_person varchar2(320);
23 l_trace varchar2(30);
24 l_asg_set varchar2(50);
25 l_selection_type varchar2(80);
26
27
28 CURSOR csr_get_asg_set(p_assignment_set number) is
29 select assignment_set_name
30 from hr_assignment_sets
31 where assignment_set_id = p_assignment_set;
32
33 begin
34 --hr_standard.event('BEFORE REPORT');
35 null;
36 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
37
38
39 If fnd_number.canonical_to_number(P_PERSON_ID) is not NULL then
40
41 Open csr_get_person(fnd_number.canonical_to_number(P_PERSON_ID));
42 Fetch csr_get_person into l_person;
43 If csr_get_person%notfound then
44 close csr_get_person;
45 End if;
46
47 CP_PERSON := l_person;
48
49 End if;
50
51 IF fnd_number.canonical_to_number(P_ASSIGNMENT_SET) is not NULL then
52
53 open csr_get_asg_set(fnd_number.canonical_to_number(P_ASSIGNMENT_SET));
54 Fetch csr_get_asg_set into l_asg_set;
55 If csr_get_asg_set%notfound then
56 close csr_get_asg_set;
57 Else
58 close csr_get_asg_set;
59 End if;
60
61 CP_assignment_set_name := l_asg_set;
62
63 End if;
64
65 IF p_selection_type is not null then
66
67 open csr_get_sel_type(p_selection_type);
68 Fetch csr_get_sel_type into l_selection_type;
69 If csr_get_sel_type%notfound then
70 close csr_get_sel_type;
71 Else
72 close csr_get_sel_type;
73 End if;
74
75 CP_selection_type := l_selection_type;
76
77 End if;
78
79 CP_start_date := fnd_date.canonical_to_date(p_start_date);
80 CP_end_date := fnd_date.canonical_to_date(p_end_date);
81
82
83 Open csr_get_trace_param;
84 Fetch csr_get_trace_param into l_trace;
85 If l_trace = 'Y' then
86 /*srw.do_sql('Alter session set SQL_TRACE true');*/null;
87 --Added during DT Fix
88 execute immediate('Alter session set SQL_TRACE true') ;
89 --End of DT Fix
90 End if;
91
92 return (TRUE);
93 end;
94
95 function AfterPForm return boolean is
96 l_person varchar2(30);
97 begin
98
99
100 If ((P_START_DATE is not NULL) and (P_END_DATE is not NULL)) then
101 begin
102 If P_PERSON_ID IS NOT NULL then
103 LP_DATE_OR_PERSON := ' AND fnd_date.canonical_to_date(pcrv.roe_date) BETWEEN fnd_date.canonical_TO_DATE(:P_START_DATE) '||
104 ' AND fnd_date.canonical_TO_DATE(:P_END_DATE)'||
105 ' AND paf.person_id=to_number(:P_PERSON_ID)';
106
107 Elsif P_ASSIGNMENT_SET is NOT NULL then
108 LP_ASSIGNMENT_SET := ', HR_ASSIGNMENT_SET_AMENDMENTS haa';
109 LP_DATE_OR_PERSON := ' AND fnd_date.canonical_to_date(pcrv.roe_date) BETWEEN fnd_date.canonical_to_DATE(:P_START_DATE) AND fnd_date.canonical_TO_DATE(:P_END_DATE)'||
110 ' AND haa.assignment_id = paf.assignment_id AND haa.include_or_exclude = ''I''' ||
111 ' AND haa.assignment_set_id = to_number(:P_ASSIGNMENT_SET)';
112
113 Else
114 LP_DATE_OR_PERSON := ' AND fnd_date.canonical_to_date(pcrv.roe_date) BETWEEN fnd_date.canonical_to_DATE(:P_START_DATE) AND fnd_date.canonical_TO_DATE(:P_END_DATE)';
115 End if;
116 end;
117 Elsif P_PERSON_ID is not NULL then
118 LP_DATE_OR_PERSON := 'AND paf.person_id=to_number(:P_PERSON_ID)';
119 Elsif P_ASSIGNMENT_SET is NOT NULL then
120 LP_ASSIGNMENT_SET := ', HR_ASSIGNMENT_SET_AMENDMENTS haa';
121 LP_DATE_OR_PERSON := ' AND haa.assignment_id = paf.assignment_id AND haa.include_or_exclude = ''I''' ||
122 ' AND haa.assignment_set_id = to_number(:P_ASSIGNMENT_SET)';
123 End if;
124
125 --Added during DT Fix
126 if LP_ASSIGNMENT_SET is null
127 then
128 LP_ASSIGNMENT_SET := ' ';
129 end if;
130
131 if LP_DATE_OR_PERSON is null
132 then
133 LP_DATE_OR_PERSON := ' ';
134 end if;
135 --End of DT Fix
136
137 return (TRUE);
138 end;
139
140 function cf_languageformula(ROE_PER_CORRESPONDENCE_LANG1 in varchar2, ROE_FINAL_PAY_PERIOD_END_DATE in varchar2) return char is
141 l_language varchar2(25);
142
143 cursor cur_lookup is select
144 meaning from hr_lookups
145 where lookup_type = 'PAY_CA_CORRESPONDENCE_LANGUAGE'
146 and lookup_code = ROE_PER_CORRESPONDENCE_LANG1;
147 begin
148 open cur_lookup;
149 fetch cur_lookup
150 into l_language;
151 close cur_lookup;
152
153 CP_EFFECTIVE_DATE := ROE_FINAL_PAY_PERIOD_END_DATE;
154
155 return(l_language);
156
157 Exception
158 When no_data_found then
159 return(' ');
160
161 end;
162
163 function cf_roe_pay_period_typeformula(ROE_PAY_PERIOD_TYPE1 in varchar2) return char is
164 l_temp per_time_period_types_tl.display_period_type%TYPE;
165
166 cursor cur_display_period_type is
167 select
168 ptpv.DISPLAY_PERIOD_TYPE
169 from
170 per_time_period_types_vl ptpv
171 where
172 ptpv.period_type = ROE_PAY_PERIOD_TYPE1;
173
174 begin
175
176 OPEN cur_display_period_type;
177 FETCH cur_display_period_type
178 INTO l_temp;
179 CLOSE cur_display_period_type;
180
181 RETURN l_temp;
182 end;
183
184 function AfterReport return boolean is
185 begin
186 --hr_standard.event('AFTER REPORT');
187 return (TRUE);
188 end;
189
190 --Functions to refer Oracle report placeholders--
191
192 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
193 Begin
194 return C_BUSINESS_GROUP_NAME;
195 END;
196 Function C_REPORT_SUBTITLE_p return varchar2 is
197 Begin
198 return C_REPORT_SUBTITLE;
199 END;
200 Function CP_PERSON_p return varchar2 is
201 Begin
202 return CP_PERSON;
203 END;
204 Function CP_Effective_date_p return date is
205 Begin
206 return CP_Effective_date;
207 END;
208 Function CP_Assignment_set_name_p return varchar2 is
209 Begin
210 return CP_Assignment_set_name;
211 END;
212 Function CP_selection_type_p return varchar2 is
213 Begin
214 return CP_selection_type;
215 END;
216 Function CP_start_date_p return date is
217 Begin
218 return CP_start_date;
219 END;
220 Function CP_end_date_p return date is
221 Begin
222 return CP_end_date;
223 END;
224 END PAY_PYCAROEP_XMLP_PKG ;
[Home] [Help]