1 PACKAGE BODY PSP_PSPRCPGD_XMLP_PKG AS
2 /* $Header: PSPRCPGDB.pls 120.4 2007/10/29 07:27:29 amakrish noship $ */
3
4 --function cf_assignment_numberformula(assignment_id in number) return varchar2 is
5 function cf_assignment_numberformula(v_assignment_id in number) return varchar2 is
6 x_assignment_number VARCHAR2(30);
7 begin
8
9
10 select assignment_number
11 into x_assignment_number
12 from per_assignments_f
13 -- where assignment_id = assignment_id
14 where assignment_id = v_assignment_id
15 AND assignment_type ='E' and rownum < 2; return(x_assignment_number);
16
17
18 RETURN NULL; exception
19 when no_data_found then
20 return('no_data_found');
21 when too_many_rows then
22 return('too many rows');
23 when others then
24 return('error');
25 end;
26
27 --function cf_person_nameformula(person_id in number) return varchar2 is
28 function cf_person_nameformula(v_person_id in number) return varchar2 is
29 x_person_name VARCHAR2(240);
30 x_end_date DATE;
31 begin
32 select end_date into x_end_date
33 from per_time_periods
34 where time_period_id = p_time_period_id;
35
36 select full_name into x_person_name
37 from per_people_f
38 -- where person_id = person_id
39 where person_id = v_person_id
40 and x_end_date BETWEEN effective_start_date and effective_end_date;
41
42 return(x_person_name);
43 RETURN NULL; exception
44 when no_data_found then
45 return('no data found');
46 when too_many_rows then
47 return('too many rows');
48 end;
49
50 function AfterPForm return boolean is
51 begin
52 -- orientation := 'LANDSCAPE';
53
54
55
56 select start_date into p_start_date
57 from per_time_periods
58 where time_period_id = p_time_period_id;
59 return (TRUE);
60 RETURN NULL; exception when no_data_found then
61 /*srw.message(1,'Start Date not found for the selected time period id');*/null;
62
63 return (FALSE);
64 when too_many_rows then
65 /*srw.message(2,'Too many rows found for the selected time period id');*/null;
66
67 return (FALSE);
68 when others then
69 /*srw.message(3,'Others exception raised');*/null;
70
71 return (FALSE);
72
73
74
75 end;
76
77 function cf_mismatch_eltformula(cf_amt_dl_d in number, sl_debit_amount in number, cf_amt_sl_c in number) return varchar2 is
78 begin
79
80
81 if NVL(cf_amt_dl_d,0) <> NVL(sl_debit_amount,0)
82 or NVL(cp_credit_amount_pgdl,0) <> NVL(cf_amt_sl_c,0)
83 then
84 return('Mismatch');
85 end if;
86
87 RETURN NULL;
88 end;
89
90 function cf_mismatch_assgformula(sum_dl_d_assg in number, sum_sl_d_assg in number, sum_dl_c_assg in number, sum_sl_c_assg in number) return varchar2 is
91 begin
92 if NVL(sum_dl_d_assg,0) <> NVL(sum_sl_d_assg,0) or NVL(sum_dl_c_assg,0) <> NVL(sum_sl_c_assg,0) then
93 return('Mismatch');
94 end if;
95 RETURN NULL; end;
96
97 function cf_mismatch_personformula(sum_dl_d_person in number, sum_sl_d_person in number, sum_dl_c_person in number, sum_sl_c_person in number) return varchar2 is
98 begin
99 if NVL(sum_dl_d_person,0) <> NVL(sum_sl_d_person,0) or NVL(sum_dl_c_person,0) <> NVL(sum_sl_c_person,0) then
100 return('Mismatch');
101 end if;
102 RETURN NULL; end;
103
104 function cf_mismatch_reportformula(sum_dl_d_total in number, sum_sl_d_total in number, sum_dl_c_total in number, sum_sl_c_total in number) return varchar2 is
105 begin
106 if NVL(sum_dl_d_total,0) <> NVL(sum_sl_d_total,0) or NVL(sum_dl_c_total,0) <> NVL(sum_sl_c_total,0) then
107 return('Mismatch');
108 end if;
109 RETURN NULL; end;
110
111 function cf_amt_sl_cformula(gl_code_combination_id in number, sl_credit_amount in number) return number is
112 x_amount_summary NUMBER ; begin
113
114
115 IF gl_code_combination_id IS NULL THEN
116 x_amount_summary := ABS(sl_credit_amount);
117 ELSE
118 x_amount_summary := sl_credit_amount;
119 END IF;
120 return(x_amount_summary);
121
122
123
124
125
126
127
128
129
130 RETURN NULL; exception
131 when no_data_found then
132 return(null);
133 end;
134
135 --function cf_amt_dl_dformula(gl_code_combination_id in number) return number is
136 function cf_amt_dl_dformula(v_person_id in number, v_assignment_id in number, v_gl_code_combination_id in number, v_project_id in number, v_task_id in number,
137 v_award_id in number, v_expenditure_type in varchar2, v_expenditure_organization_id in number) return number is
138 v_debit_amount_pgdl NUMBER := 0 ;
139 v_suspense_code VARCHAR2(500) :='';
140 v_cr NUMBER := 0 ; v_dr NUMBER := 0; i PLS_INTEGER := 0;
141
142 CURSOR c_suspense_gl
143 IS
144 SELECT sum(decode(a.dr_cr_flag, 'C',b.distribution_amount,0)) pgdl_Credit_Amount,
145 sum(decode(a.dr_cr_flag, 'D',b.distribution_amount,0)) pgdl_Debit_Amount,
146 B.suspense_reason_code FROM PSP_SUMMARY_LINES A,
147 PSP_PRE_GEN_DIST_LINES_HISTORY B
148 WHERE A.SUMMARY_LINE_ID = B.SUMMARY_LINE_ID
149 AND A.STATUS_CODE = 'A' AND B.STATUS_CODE = 'A'
150 AND a.source_type = p_source_type
151 and a.source_code = p_source_code
152 and a.time_period_id = p_time_period_id
153 and (a.interface_batch_name = p_batch_name or a.interface_batch_name IS NULL)
154 -- and a.person_id = person_id
155 -- and a.assignment_id = assignment_id
156 -- and a.gl_code_combination_id = gl_code_combination_id
157 and a.person_id = v_person_id
158 and a.assignment_id = v_assignment_id
159 and a.gl_code_combination_id = v_gl_code_combination_id
160 GROUP BY B.suspense_reason_code;
161
162
163 CURSOR c_suspense_poeta
164 IS
165 SELECT sum(decode(a.dr_cr_flag, 'C',b.distribution_amount,0)) pgdl_Credit_Amount,
166 sum(decode(a.dr_cr_flag, 'D',b.distribution_amount,0)) pgdl_Debit_Amount,
167 B.suspense_reason_code FROM PSP_SUMMARY_LINES A,
168 PSP_PRE_GEN_DIST_LINES_HISTORY B
169 WHERE A.SUMMARY_LINE_ID = B.SUMMARY_LINE_ID
170 AND A.STATUS_CODE = 'A' AND B.STATUS_CODE = 'A'
171 AND a.source_type = p_source_type
172 and a.source_code = p_source_code
173 and a.time_period_id = p_time_period_id
174 and (a.interface_batch_name = p_batch_name or a.interface_batch_name IS NULL)
175 -- and a.person_id = person_id
176 -- and a.assignment_id = assignment_id
177 -- and A.project_id = project_id
178 -- and a.task_id = task_id
179 -- and ( a.award_id = award_id
180 -- OR (a.award_id IS NULL AND award_id IS NULL))
181 -- and a.expenditure_type = expenditure_type
182 -- and a.expenditure_organization_id = expenditure_organization_id
183 and a.person_id = v_person_id
184 and a.assignment_id = v_assignment_id
185 and A.project_id = v_project_id
186 and a.task_id = v_task_id
187 and ( a.award_id = v_award_id
188 -- OR (a.award_id IS NULL AND award_id IS NULL))
189 OR (a.award_id IS NULL AND v_award_id IS NULL))
190 and a.expenditure_type = v_expenditure_type
191 and a.expenditure_organization_id = v_expenditure_organization_id
192 GROUP BY B.suspense_reason_code;
193
194
195 BEGIN
196
197
198 cp_credit_amount_pgdl := 0;
199 cp_suspense := '';
200
201
202 -- IF gl_code_combination_id IS NOT NULL THEN
203 IF v_gl_code_combination_id IS NOT NULL THEN
204
205 OPEN c_suspense_gl;
206 LOOP
207
208 FETCH c_suspense_gl
209 INTO v_cr, v_dr, v_suspense_code;
210
211 EXIT WHEN c_suspense_gl%NOTFOUND;
212
213 cp_credit_amount_pgdl := cp_credit_amount_pgdl + v_cr;
214 v_debit_amount_pgdl := v_debit_amount_pgdl + v_dr;
215
216 IF v_suspense_code IS NOT NULL
217 THEN
218 i := i+1;
219 IF i = 1
220 THEN
221 cp_suspense := 'Suspense Reason: '||v_suspense_code;
222 ELSE
223 cp_suspense := cp_suspense||', '||v_suspense_code;
224 END IF;
225 END IF;
226
227 END LOOP;
228 CLOSE c_suspense_gl;
229
230 ELSE
231 OPEN c_suspense_poeta;
232 LOOP
233
234 FETCH c_suspense_poeta
235 INTO v_cr, v_dr, v_suspense_code;
236
237 EXIT WHEN c_suspense_poeta%NOTFOUND;
238
239 cp_credit_amount_pgdl := cp_credit_amount_pgdl + v_cr;
240 v_debit_amount_pgdl := v_debit_amount_pgdl + v_dr;
241
242 IF v_suspense_code IS NOT NULL
243 THEN
244 i := i + 1;
245
246 IF i = 1
247 THEN
248 cp_suspense := 'Suspense Reason: '||v_suspense_code;
249 ELSE
250 cp_suspense := cp_suspense||', '||v_suspense_code;
251 END IF;
252 END IF;
253
254 END LOOP;
255 CLOSE c_suspense_poeta;
256
257 END IF;
258
259 return(v_debit_amount_pgdl);
260 RETURN NULL;
261
262 EXCEPTION
263 WHEN NO_DATA_FOUND THEN
264 return(null);
265
266 END;
267
268 function CF_org_reportFormula return VARCHAR2 is
269 x_org_name hr_all_organization_units_tl.name%TYPE; x_org_id varchar2(15);
270 begin
271 fnd_profile.get('PSP_ORG_REPORT', x_org_id);
272 if x_org_id is not null then
273 select name into x_org_name from hr_organization_units where organization_id = TO_NUMBER(x_org_id);
274 return(x_org_name);
275 end if;
276
277 RETURN NULL;
278
279
280 EXCEPTION
281 WHEN NO_DATA_FOUND
282 THEN
283 RETURN('Organization Defined in Profile Not Found');
284
285
286 end;
287
288 function BeforeReport return boolean is
289 begin
290
291 -- hr_standard.event('BEFORE REPORT');
292 return (TRUE);
293 end;
294
295 function CF_source_typeFormula return VARCHAR2 is
296 x_source_type varchar2(80);
297 begin
298 select meaning into x_source_type from psp_lookups
299 where lookup_type = 'PSP_SOURCE_TYPE' and lookup_code = p_source_type;
300 return(x_source_type);
301 end;
302
303 function CF_time_periodFormula return VARCHAR2 is
304 x_time_period varchar2(35);
305 begin
306 if p_time_period_id is not null then
307 select period_name into x_time_period from per_time_periods
308 where time_period_id = p_time_period_id;
309 return(x_time_period);
310 end if;
311 RETURN NULL; end;
312
313 --function cf_charging_instructionsformu(project_id in number, task_id in number, award_id in number, expenditure_organization_id in number, gl_code_combination_id in number) return char is
314 function cf_charging_instructionsformu(v_project_id in number, v_task_id in number, v_award_id in number, v_expenditure_organization_id in number, v_gl_code_combination_id in number, v_expenditure_type in varchar2) return char is
315 v_retcode NUMBER;
316 l_project_name VARCHAR2(30);
317 l_award_number VARCHAR2(15);
318 l_task_number VARCHAR2(25);
319 l_org_name hr_all_organization_units_tl.name%TYPE; l_poeta VARCHAR2(360); l_gl_flex_values VARCHAR2(2000);
320
321 CURSOR c_project_name
322 IS
323 SELECT ppa.name
324 FROM pa_projects_all ppa
325 -- WHERE ppa.project_id = project_id;
326 WHERE ppa.project_id = v_project_id;
327
328 CURSOR c_task_number
329 IS
330 SELECT pt.task_number
331 FROM pa_tasks pt
332 -- WHERE pt.task_id = task_id;
333 WHERE pt.task_id = v_task_id;
334
335 CURSOR c_award_number
336 IS
337 SELECT gma.award_number
338 FROM gms_awards_all gma
339 -- WHERE gma.award_id = award_id;
340 WHERE gma.award_id = v_award_id;
341
342 CURSOR c_org_name
343 IS
344 SELECT haou.name
345 FROM hr_all_organization_units haou
346 -- WHERE haou.organization_id = expenditure_organization_id;
347 WHERE haou.organization_id = v_expenditure_organization_id;
348
349 BEGIN
350
351 -- IF gl_code_combination_id IS NOT NULL
352 IF v_gl_code_combination_id IS NOT NULL
353 THEN
354 -- l_gl_flex_values := psp_general.get_gl_values(to_number(p_set_of_books_id),gl_code_combination_id);
355 l_gl_flex_values := psp_general.get_gl_values(to_number(p_set_of_books_id),v_gl_code_combination_id);
356 RETURN (l_gl_flex_values);
357
358 ELSE
359
360 -- IF project_id IS NOT NULL
361 IF v_project_id IS NOT NULL
362 THEN
363
364 OPEN c_project_name;
365 FETCH c_project_name INTO l_project_name;
366 CLOSE c_project_name;
367
368 OPEN c_task_number;
369 FETCH c_task_number INTO l_task_number;
370 CLOSE c_task_number;
371
372
373 -- IF award_id IS NOT NULL
374 IF v_award_id IS NOT NULL
375 THEN
376 OPEN c_award_number;
377 FETCH c_award_number INTO l_award_number;
378 CLOSE c_award_number;
379 ELSE
380 l_award_number := '';
381 END IF;
382
383 OPEN c_org_name;
384 FETCH c_org_name INTO l_org_name;
385 CLOSE c_org_name;
386
387
388 -- l_poeta := l_project_name||' '||l_task_number||' '||l_award_number||' '||l_org_name||' '||expenditure_type;
389 l_poeta := l_project_name||' '||l_task_number||' '||l_award_number||' '||l_org_name||' '||v_expenditure_type;
390
391 ELSE
392 l_poeta := '';
393 END IF;
394
395 RETURN(l_poeta);
396 END IF;
397 END;
398
399 function cf_currency_codeformula(currency_code in varchar2) return char is
400 begin
401
402 /*srw.reference(currency_code);*/null;
403
404 RETURN('(' || currency_code || ')');
405
406
407 end;
408
409 function cf_currency_formatformula(currency_code in varchar2) return char is
410 begin
411
412 /*srw.reference(currency_code);*/null;
413
414 RETURN(fnd_currency.get_format_mask(currency_code,30));
415
416 end;
417
418 function cf_sum_sl_d_total_dspformula(cs_sum_sl_d_total in number, cf_currency_format in varchar2) return char is
419 begin
420
421 /*srw.reference(cs_sum_sl_d_total);*/null;
422
423 /*srw.reference(cf_currency_format);*/null;
424
425 RETURN(TO_CHAR(cs_sum_sl_d_total,cf_currency_format));
426
427
428 end;
429
430 function cf_sum_sl_c_total_dspformula(cs_sum_sl_c_total in number, cf_currency_format in varchar2) return char is
431 begin
432
433 /*srw.reference(cs_sum_sl_c_total);*/null;
434
435 /*srw.reference(cf_currency_format);*/null;
436
437 RETURN(TO_CHAR(cs_sum_sl_c_total,cf_currency_format));
438
439 end;
440
441 function cf_sum_dl_d_total_dspformula(cs_sum_dl_d_total in number, cf_currency_format in varchar2) return char is
442 begin
443
444 /*srw.reference(cs_sum_dl_d_total);*/null;
445
446 /*srw.reference(cf_currency_format);*/null;
447
448 RETURN(TO_CHAR(cs_sum_dl_d_total,cf_currency_format));
449
450
451 end;
452
453 function cf_sum_dl_c_total_dspformula(cs_sum_dl_c_total in number, cf_currency_format in varchar2) return char is
454 begin
455
456 /*srw.reference(cs_sum_dl_c_total);*/null;
457
458 /*srw.reference(cf_currency_format);*/null;
459
460 RETURN(TO_CHAR(cs_sum_dl_c_total,cf_currency_format));
461
462
463 end;
464
465 function cf_mismatch_currency_totalform(cs_sum_dl_d_total in number, cs_sum_sl_d_total in number, cs_sum_dl_c_total in number, cs_sum_sl_c_total in number) return char is
466 begin
467
468 if NVL(cs_sum_dl_d_total,0) <> NVL(cs_sum_sl_d_total,0) or NVL(cs_sum_dl_c_total,0) <> NVL(cs_sum_sl_c_total,0) then
469 return('Mismatch');
470 end if;
471 RETURN NULL;
472
473 end;
474
475 function cf_sum_sl_d_person_dspformula(sum_sl_d_person in number, cf_currency_format in varchar2) return char is
476 begin
477
478 /*srw.reference(sum_sl_d_person);*/null;
479
480 /*srw.reference(cf_currency_format);*/null;
481
482 RETURN(TO_CHAR(sum_sl_d_person,cf_currency_format));
483
484 end;
485
486 function cf_sum_sl_c_person_dspformula(sum_sl_c_person in number, cf_currency_format in varchar2) return char is
487 begin
488
489 /*srw.reference(sum_sl_c_person);*/null;
490
491 /*srw.reference(cf_currency_format);*/null;
492
493 RETURN(TO_CHAR(sum_sl_c_person,cf_currency_format));
494
495 end;
496
497 function cf_sum_dl_d_person_dspformula(sum_dl_d_person in number, cf_currency_format in varchar2) return char is
498 begin
499
500 /*srw.reference(sum_dl_d_person);*/null;
501
502 /*srw.reference(cf_currency_format);*/null;
503
504 RETURN(TO_CHAR(sum_dl_d_person,cf_currency_format));
505
506 end;
507
508 function cf_sum_dl_c_person_dspformula(sum_dl_c_person in number, cf_currency_format in varchar2) return char is
509 begin
510
511 /*srw.reference(sum_dl_c_person);*/null;
512
513 /*srw.reference(cf_currency_format);*/null;
514
515 RETURN(TO_CHAR(sum_dl_c_person,cf_currency_format));
516
517 end;
518
519 function cf_sum_sl_d_assg_dspformula(sum_sl_d_assg in number, cf_currency_format in varchar2) return char is
520 begin
521
522 /*srw.reference(sum_sl_d_assg);*/null;
523
524 /*srw.reference(cf_currency_format);*/null;
525
526 RETURN(TO_CHAR(sum_sl_d_assg,cf_currency_format));
527
528 end;
529
530 function cf_sum_sl_c_assg_dspformula(sum_sl_c_assg in number, cf_currency_format in varchar2) return char is
531 begin
532
533 /*srw.reference(sum_sl_c_assg);*/null;
534
535 /*srw.reference(cf_currency_format);*/null;
536
537 RETURN(TO_CHAR(sum_sl_c_assg,cf_currency_format));
538
539 end;
540
541 function cf_sum_dl_d_assg_dspformula(sum_dl_d_assg in number, cf_currency_format in varchar2) return char is
542 begin
543
544 /*srw.reference(sum_dl_d_assg);*/null;
545
546 /*srw.reference(cf_currency_format);*/null;
547
548 RETURN(TO_CHAR(sum_dl_d_assg,cf_currency_format));
549
550 end;
551
552 function cf_sum_dl_c_assg_dspformula(sum_dl_c_assg in number, cf_currency_format in varchar2) return char is
553 begin
554
555 /*srw.reference(sum_dl_c_assg);*/null;
556
557 /*srw.reference(cf_currency_format);*/null;
558
559 RETURN(TO_CHAR(sum_dl_c_assg,cf_currency_format));
560
561 end;
562
563 function cf_amt_dl_d_dspformula(cf_amt_dl_d in number, cf_currency_format in varchar2) return char is
564 begin
565
566 /*srw.reference(cf_amt_dl_d);*/null;
567
568 /*srw.reference(cf_currency_format);*/null;
569
570 RETURN(TO_CHAR(cf_amt_dl_d,cf_currency_format));
571
572 end;
573
574 function cf_sl_debit_amount_dspformula(sl_debit_amount in number, cf_currency_format in varchar2) return char is
575 begin
576
577 /*srw.reference(sl_debit_amount);*/null;
578
579 /*srw.reference(cf_currency_format);*/null;
580
581 RETURN(TO_CHAR(sl_debit_amount,cf_currency_format));
582
583 end;
584
585 function cf_amt_sl_c_dspformula(cf_amt_sl_c in number, cf_currency_format in varchar2) return char is
586 begin
587
588 /*srw.reference(cf_amt_sl_c);*/null;
589
590 /*srw.reference(cf_currency_format);*/null;
591
592 RETURN(TO_CHAR(cf_amt_sl_c,cf_currency_format));
593
594 end;
595
596 function cf_credit_amount_pgdl_dspformu(cf_currency_format in varchar2) return char is
597 begin
598
599 /*srw.reference(cp_credit_amount_pgdl);*/null;
600
601 /*srw.reference(cf_currency_format);*/null;
602
603 RETURN(TO_CHAR(cp_credit_amount_pgdl,cf_currency_format));
604
605 end;
606
607 function AfterReport return boolean is
608 begin
609 -- hr_standard.event('AFTER REPORT');
610 return (TRUE);
611 end;
612
613 --Functions to refer Oracle report placeholders--
614
615 Function CP_suspense_p return varchar2 is
616 Begin
617 return CP_suspense;
618 END;
619 Function CP_credit_amount_pgdl_p return number is
620 Begin
621 return CP_credit_amount_pgdl;
622 END;
623 END PSP_PSPRCPGD_XMLP_PKG ;
[Home] [Help]