1 PACKAGE BODY PAY_PAYUSRRS_XMLP_PKG AS
2 /* $Header: PAYUSRRSB.pls 120.0 2007/12/28 06:47:31 srikrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6 declare
7 trace varchar2(30) := '';
8 begin
9
10 --hr_standard.event('BEFORE REPORT');
11
12 null;
13
14 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
15
16
17 if p_asg_set_id is not null then
18
19 select ASSIGNMENT_SET_NAME into CP_asg_set_name
20 from hr_assignment_sets
21 where assignment_set_id = p_asg_set_id;
22
23 end if;
24
25
26 if p_tax_unit_id IS NOT NULL then
27
28 Select name into cp_gre_name
29 from hr_organization_units
30 where organization_id = p_tax_unit_id;
31 end if;
32
33 if p_person_id IS NOT NULL then
34
35 Select distinct ppf.full_name into c_person_name
36 from per_people_f ppf
37 where ppf.person_id = p_person_id;
38 end if;
39 if p_assignment_number IS NOT NULL then
40
41 Select ppf.full_name into c_person_name
42 from per_people_f ppf,
43 per_all_assignments_f paa
44 where paa.person_id = ppf.person_id
45 and paa.assignment_number = p_assignment_number;
46 end if;
47
48 if p_classification_id IS NOT NULL then
49
50 Select pec.classification_name into c_classification_name
51 from pay_element_classifications pec
52 where pec.classification_id = p_classification_id;
53 end if;
54
55
56
57 Select upper(parameter_value)
58 into trace
59 from pay_action_parameters
60 where parameter_name = 'TRACE';
61
62 If trace <> 'N' then
63 /*srw.do_sql('alter session set SQL_TRACE TRUE');null;*/
64 execute immediate 'alter session set SQL_TRACE TRUE';
65
66 end if;
67
68 Exception When Others then
69 /*srw.message(1,'Some parameters are NULL ...No Data Found');*/null;
70
71
72 LP_END_DATE := P_END_DATE;
73 LP_START_DATE:=P_START_DATE;
74 end;
75 return (TRUE);
76 end;
77
78 function element_categoryformula(element_information_category in varchar2, element_information1 in varchar2) return varchar2 is
79
80 CURSOR ecat_cur IS
81 SELECT meaning
82 FROM fnd_common_lookups
83 WHERE lookup_type=element_information_category
84 AND lookup_code=element_information1;
85 ecatc ecat_cur%ROWTYPE;
86
87 begin
88
89 OPEN ecat_cur;
90 FETCH ecat_cur INTO ecatc;
91 CLOSE ecat_cur;
92 RETURN ecatc.meaning;
93
94 end;
95
96 FUNCTION GET_BUSINESS_GROUP_NAME(fp_business_group_id IN NUMBER) RETURN VARCHAR2 IS
97
98 CURSOR bg_cur IS
99 SELECT name
100 FROM hr_organization_units
101 WHERE organization_id = fp_business_group_id;
102 bgc bg_cur%ROWTYPE;
103
104 BEGIN
105
106 OPEN bg_cur;
107 FETCH bg_cur into bgc;
108 CLOSE bg_cur;
109 return (bgc.name);
110
111 END;
112
113 function gre_nameformula(TAX_UNIT_ID in number) return varchar2 is
114 begin
115
116 --return(get_business_group_name() (TAX_UNIT_ID));
117 return(get_business_group_name(TAX_UNIT_ID));
118
119 end;
120
121 function CF_con_dateFormula return VARCHAR2 is
122 begin
123 return (to_char(p_start_date,'DD-MON-YYYY') || ' To: ' || to_char(p_end_date,'DD-MON-YYYY') );
124 end;
125
126 function AfterReport return boolean is
127 begin
128 --hr_standard.event('AFTER REPORT');
129 return (TRUE);
130 end;
131
132 function AfterPForm return boolean is
133 begin
134
135 p_where_clause := null;
136 p_hint_clause := null;
137 --p_from_clause := null;
138 p_from_clause := ' ';
139
140 if p_asg_set_id is not null then
141 p_hint_clause := '';
142
143 p_from_clause := ' , hr_assignment_set_amendments hasa ';
144
145 p_where_clause := p_where_clause ||
146 ' AND hasa.assignment_set_id = '||p_asg_set_id ||
147 ' AND paf.assignment_id = hasa.assignment_id
148 AND upper(hasa.include_or_exclude) = '||''''||'I'||'''';
149
150 end if;
151
152
153 if p_tax_unit_id IS NOT NULL then
154
155 p_where_clause := p_where_clause ||
156 ' AND paa.TAX_UNIT_ID = '||p_tax_unit_id;
157
158 end if;
159
160 if p_person_id IS NOT NULL then
161
162 p_hint_clause := '';
163
164 p_where_clause := p_where_clause ||
165 ' AND peo.PERSON_ID = '||p_person_id;
166
167
168 end if;
169 if p_assignment_number IS NOT NULL then
170
171 p_hint_clause := '';
172
173 p_where_clause := p_where_clause ||
174 ' AND paf.ASSIGNMENT_NUMBER = '||''''||
175 p_assignment_number||'''';
176
177 end if;
178
179 if p_classification_id IS NOT NULL then
180
181 p_where_clause := p_where_clause ||
182 ' AND pec.CLASSIFICATION_ID = '||p_classification_id;
183
184
185 end if;
186 return (TRUE);
187 end;
188
189 --Functions to refer Oracle report placeholders--
190
191 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
192 Begin
193 return C_BUSINESS_GROUP_NAME;
194 END;
195 Function C_REPORT_SUBTITLE_p return varchar2 is
196 Begin
197 return C_REPORT_SUBTITLE;
198 END;
199 Function C_PERSON_NAME_p return varchar2 is
200 Begin
201 return C_PERSON_NAME;
202 END;
203 Function C_CLASSIFICATION_NAME_p return varchar2 is
204 Begin
205 return C_CLASSIFICATION_NAME;
206 END;
207 Function CP_GRE_NAME_p return varchar2 is
208 Begin
209 return CP_GRE_NAME;
210 END;
211 Function CP_asg_set_name_p return varchar2 is
212 Begin
213 return CP_asg_set_name;
214 END;
215 /*Added as a fix*/
216 function RVALUEFormula(RESULT_VALUE VARCHAR2) return Number is
217 begin
218 return to_number(RESULT_VALUE);
219 end;
220 END PAY_PAYUSRRS_XMLP_PKG ;