1 PACKAGE BODY PSP_PSPRCLSL_XMLP_PKG AS
2 /* $Header: PSPRCLSLB.pls 120.4 2007/10/29 07:27:12 amakrish noship $ */
3
4 function cf_assignment_numberformula(assignment_id_1 in number) return varchar2 is
5 x_assignment_number VARCHAR2(30);
6 begin
7
8
9
10 select assignment_number
11 into x_assignment_number
12 from per_assignments_f
13 where assignment_id = assignment_id_1
14 AND assignment_type ='E' and rownum < 2 ; return(x_assignment_number);
15
16
17 RETURN NULL; exception
18 when no_data_found then
19 return('no_data_found');
20 when too_many_rows then
21 return('too many rows');
22 when others then
23 return('error');
24 end;
25
26 function CF_element_nameFormula return VARCHAR2 is
27 begin
28 return(null);
29 end;
30
31 function cf_person_nameformula(person_id_1 in number) return varchar2 is
32 x_person_name VARCHAR2(240);
33 x_end_date DATE;
34 begin
35 select end_date into x_end_date
36 from per_time_periods
37 where time_period_id = p_time_period_id;
38
39 select full_name into x_person_name
40 from per_people_f
41 where person_id = person_id_1
42 and x_end_date BETWEEN effective_start_date and effective_end_date;
43
44 return(x_person_name);
45 RETURN NULL; exception
46 when no_data_found then
47 return('no data found');
48 when too_many_rows then
49 return('too many rows');
50 end;
51
52 function cf_amt_sl_dformula(person_id_1 in number, assignment_id_1 in number, element_type_id_1 in number, currency_code_1 in varchar2) return number is
53 v_debit_amount_sl NUMBER; begin
54
55
56
57 SELECT sum(decode(ppl.dr_cr_flag, 'C',ppsl.pay_amount,0)) sl_Credit_Amount,
58 sum(decode(ppl.dr_cr_flag, 'D',ppsl.pay_amount,0)) sl_Debit_Amount
59 INTO cp_credit_amount_sl,
60 v_debit_amount_sl
61 FROM psp_payroll_lines ppl,
62 psp_payroll_controls ppc,
63 psp_payroll_sub_lines ppsl
64 WHERE ppl.payroll_control_id = ppc.payroll_control_id
65 AND ppl.payroll_line_id = ppsl.payroll_line_id
66 AND ppc.source_type = p_source_type
67 and ppc.payroll_source_code = p_source_code
68 and ppc.time_period_id = p_time_period_id
69 and (ppc.batch_name = p_batch_name or ppc.batch_name IS NULL)
70 and ppl.person_id = person_id_1
71 and ppl.assignment_id = assignment_id_1
72 and ppc.currency_code = currency_code_1
73 and ppl.element_type_id = element_type_id_1;
74
75
76
77
78 RETURN(v_debit_amount_sl);
79 RETURN NULL; exception
80 when no_data_found then
81 return(null);
82 end;
83
84 function AfterPForm return boolean is
85 begin
86 --orientation := 'LANDSCAPE';
87
88
89 select start_date into p_start_date
90 from per_time_periods
91 where time_period_id = p_time_period_id;
92 return (TRUE);
93 RETURN NULL; exception when no_data_found then
94 /*srw.message(1,'Start Date not found for the selected time period id');*/null;
95
96 return (FALSE);
97 when too_many_rows then
98 /*srw.message(2,'Too many rows found for the selected time period id');*/null;
99
100 return (FALSE);
101 when others then
102 /*srw.message(3,'Others exception raised');*/null;
103
104 return (FALSE);
105
106
107 end;
108
109 function cf_mismatch_eltformula(l_debit_amount in number, cf_amt_sl_d in number, l_credit_amount in number) return varchar2 is
110 begin
111 if l_debit_amount <> cf_amt_sl_d or l_credit_amount <> cp_credit_amount_sl then
112 return('Mismatch');
113 end if;
114 RETURN NULL; end;
115
116 function cf_mismatch_assgformula(sum_l_d_assg in number, sum_sl_d_assg in number, sum_l_c_assg in number, sum_sl_c_assg in number) return varchar2 is
117 begin
118 if sum_l_d_assg <> sum_sl_d_assg or sum_l_c_assg <> sum_sl_c_assg then
119 return('Mismatch');
120 end if;
121 RETURN NULL; end;
122
123 function cf_mismatch_personformula(sum_l_d_person in number, sum_sl_d_person in number, sum_l_c_person in number, sum_sl_c_person in number) return varchar2 is
124 begin
125 if sum_l_d_person <> sum_sl_d_person or sum_l_c_person <> sum_sl_c_person then
126 return('Mismatch');
127 end if;
128 RETURN NULL; end;
129
130 function cf_mismatch_reportformula(sum_l_d_total in number, sum_sl_d_total in number, sum_l_c_total in number, sum_sl_c_total in number) return varchar2 is
131 begin
132
133 if sum_l_d_total <> sum_sl_d_total or sum_l_c_total <> sum_sl_c_total then
134 return('Mismatch');
135 end if;
136 RETURN NULL; end;
137
138 function CF_amt_sl_cFormula return Number is
139 begin
140
141
142
143 RETURN NULL; exception
144 when no_data_found then
145 return(null);
146 end;
147
148 function CF_amt_l_cFormula return Number is
149 begin
150
151
152
153
154 RETURN NULL; exception
155 when no_data_found then
156 return(null);
157 end;
158
159 function CF_amt_l_dFormula return Number is
160
161 begin
162
163
164
165 RETURN NULL; exception
166 when no_data_found then
167 return(null);
168 end;
169
170 function CF_orgFormula return VARCHAR2 is
171 x_org_name hr_all_organization_units_tl.name%TYPE; x_org_id varchar2(15);
172 begin
173 fnd_profile.get('PSP_ORG_REPORT', x_org_id);
174
175 if x_org_id is not null then
176 select name into x_org_name from hr_organization_units where organization_id = TO_NUMBER(x_org_id);
177 return(x_org_name);
178 end if;
179
180 RETURN NULL;
181
182
183 EXCEPTION
184 WHEN NO_DATA_FOUND
185 THEN
186 RETURN('Organization Defined in Profile Not Found');
187
188
189 end;
190
191 function CF_source_typeFormula return VARCHAR2 is
192 x_source_type varchar2(80);
193 begin
194 select meaning into x_source_type from psp_lookups
195 where lookup_type = 'PSP_SOURCE_TYPE' and lookup_code = p_source_type;
196 return(x_source_type);
197 end;
198
199 function CF_time_periodFormula return VARCHAR2 is
200 x_time_period varchar2(35);
201 begin
202 if p_time_period_id is not null then
203 select period_name into x_time_period from per_time_periods
204 where time_period_id = p_time_period_id;
205 return(x_time_period);
206 end if;
207 RETURN NULL; end;
208
209 function BeforeReport return boolean is
210 begin
211
212 --hr_standard.event('BEFORE REPORT');
213 return (TRUE);
214 end;
215
216 function cf_currency_formatformula(currency_code in varchar2) return char is
217 begin
218 /*srw.reference(currency_code);*/null;
219
220 RETURN(fnd_currency.get_format_mask(currency_code, 30));
221
222 end;
223
224 function cf_currency_codeformula(currency_code in varchar2) return char is
225 begin
226 /*srw.reference(currency_code);*/null;
227
228 RETURN('(' || currency_code || ')');
229 end;
230
231 function cf_l_debit_amount_dspformula(l_debit_amount in number, cf_currency_format in varchar2) return char is
232 begin
233
234 /*srw.reference(l_debit_amount);*/null;
235
236 /*srw.reference(cf_currency_format);*/null;
237
238 RETURN(TO_CHAR(l_debit_amount,cf_currency_format));
239
240 end;
241
242 function cf_l_credit_amount_dspformula(l_credit_amount in number, cf_currency_format in varchar2) return char is
243 begin
244
245 /*srw.reference(l_credit_amount);*/null;
246
247 /*srw.reference(cf_currency_format);*/null;
248
249 RETURN(TO_CHAR(l_credit_amount,cf_currency_format));
250
251 end;
252
253 function cf_mismatch_currencyformula(cs_sum_l_d_total in number, cs_sum_sl_d_total in number, cs_sum_l_c_total in number, cs_sum_sl_c_total in number) return char is
254 begin
255
256 if cs_sum_l_d_total <> cs_sum_sl_d_total or cs_sum_l_c_total <> cs_sum_sl_c_total then
257 return('Mismatch');
258 end if;
259 RETURN NULL;
260 end;
261
262 function CP_credit_amount_slFormula return Number is
263 begin
264 null;
265 return CP_CREDIT_AMOUNT_SL;
266 end;
267
268 function cf_amt_sl_d_dspformula(cf_amt_sl_d in number, cf_currency_format in varchar2) return char is
269 begin
270
271 /*srw.reference(cf_amt_sl_d);*/null;
272
273 /*srw.reference(cf_currency_format);*/null;
274
275 RETURN(TO_CHAR(cf_amt_sl_d,cf_currency_format));
276
277 end;
278
279 function cf_credit_amount_sl_dspformula(cf_currency_format in varchar2) return char is
280 begin
281
282 /*srw.reference(cp_credit_amount_sl);*/null;
283
284 /*srw.reference(cf_currency_format);*/null;
285
286 RETURN(TO_CHAR(cp_credit_amount_sl,cf_currency_format));
287
288 end;
289
290 function cf_sum_l_d_assg_dspformula(sum_l_d_assg in number, cf_currency_format in varchar2) return char is
291 begin
292
293 /*srw.reference(sum_l_d_assg);*/null;
294
295 /*srw.reference(cf_currency_format);*/null;
296
297 RETURN(TO_CHAR(sum_l_d_assg,cf_currency_format));
298
299 end;
300
301 function cf_sum_l_c_assg_dspformula(sum_l_c_assg in number, cf_currency_format in varchar2) return char is
302 begin
303
304 /*srw.reference(sum_l_c_assg);*/null;
305
306 /*srw.reference(cf_currency_format);*/null;
307
308 RETURN(TO_CHAR(sum_l_c_assg,cf_currency_format));
309
310 end;
311
312 function cf_sum_sl_d_assg_dspformula(sum_sl_d_assg in number, cf_currency_format in varchar2) return char is
313 begin
314
315 /*srw.reference(sum_sl_d_assg);*/null;
316
317 /*srw.reference(cf_currency_format);*/null;
318
319 RETURN(TO_CHAR(sum_sl_d_assg,cf_currency_format));
320
321 end;
322
323 function cf_sum_sl_c_assg_dspformula(sum_sl_c_assg in number, cf_currency_format in varchar2) return char is
324 begin
325
326 /*srw.reference(sum_sl_c_assg);*/null;
327
328 /*srw.reference(cf_currency_format);*/null;
329
330 RETURN(TO_CHAR(sum_sl_c_assg,cf_currency_format));
331
332 end;
333
334 function cf_sum_l_d_person_dspformula(sum_l_d_person in number, cf_currency_format in varchar2) return char is
335 begin
336 /*srw.reference(sum_l_d_person);*/null;
337
338 /*srw.reference(cf_currency_format);*/null;
339
340 RETURN(TO_CHAR(sum_l_d_person,cf_currency_format));
341 end;
342
343 function cf_sum_l_c_person_dspformula(sum_l_c_person in number, cf_currency_format in varchar2) return char is
344 begin
345
346 /*srw.reference(sum_l_c_person);*/null;
347
348 /*srw.reference(cf_currency_format);*/null;
349
350 RETURN(TO_CHAR(sum_l_c_person,cf_currency_format));
351
352 end;
353
354 function cf_sum_sl_d_person_dspformula(sum_sl_d_person in number, cf_currency_format in varchar2) return char is
355 begin
356
357 /*srw.reference(sum_sl_d_person);*/null;
358
359 /*srw.reference(cf_currency_format);*/null;
360
361 RETURN(TO_CHAR(sum_sl_d_person,cf_currency_format));
362
363 end;
364
365 function cf_sum_sl_c_person_dspformula(sum_sl_c_person in number, cf_currency_format in varchar2) return char is
366 begin
367
368 /*srw.reference(sum_sl_c_person);*/null;
369
370 /*srw.reference(cf_currency_format);*/null;
371
372 RETURN(TO_CHAR(sum_sl_c_person,cf_currency_format));
373
374
375 end;
376
377 function cf_sum_l_d_total_dspformula(cs_sum_l_d_total in number, cf_currency_format in varchar2) return char is
378 begin
379
380 /*srw.reference(cs_sum_l_d_total);*/null;
381
382 /*srw.reference(cf_currency_format);*/null;
383
384 RETURN(TO_CHAR(cs_sum_l_d_total,cf_currency_format));
385
386 end;
387
388 function cf_sum_sl_d_total_dspformula(cs_sum_sl_d_total in number, cf_currency_format in varchar2) return char is
389 begin
390
391 /*srw.reference(cs_sum_sl_d_total);*/null;
392
393 /*srw.reference(cf_currency_format);*/null;
394
395 RETURN(TO_CHAR(cs_sum_sl_d_total,cf_currency_format));
396
397
398 end;
399
400 function cf_sum_sl_c_total_dspformula(cs_sum_sl_c_total in number, cf_currency_format in varchar2) return char is
401 begin
402
403 /*srw.reference(cs_sum_sl_c_total);*/null;
404
405 /*srw.reference(cf_currency_format);*/null;
406
407 RETURN(TO_CHAR(cs_sum_sl_c_total,cf_currency_format));
408
409
410 end;
411
412 function cf_sum_l_c_total_dspformula(cs_sum_l_c_total in number, cf_currency_format in varchar2) return char is
413 begin
414
415 /*srw.reference(cs_sum_l_c_total);*/null;
416
417 /*srw.reference(cf_currency_format);*/null;
418
419 RETURN(TO_CHAR(cs_sum_l_c_total,cf_currency_format));
420
421 end;
422
423 function AfterReport return boolean is
424 begin
425 --hr_standard.event('AFTER REPORT');
426 return (TRUE);
427 end;
428
429 --Functions to refer Oracle report placeholders--
430
431 Function CP_credit_amount_sl_p return number is
432 Begin
433 return CP_credit_amount_sl;
434 END;
435 END PSP_PSPRCLSL_XMLP_PKG ;
[Home] [Help]