1 PACKAGE BODY PA_PAXINGEN_XMLP_PKG AS
2 /* $Header: PAXINGENB.pls 120.0.12010000.3 2008/12/12 09:32:48 dbudhwar ship $ */
3
4 FUNCTION get_cover_page_values RETURN BOOLEAN IS
5
6 BEGIN
7
8 RETURN(TRUE);
9
10 EXCEPTION
11 WHEN OTHERS THEN
12 RETURN(FALSE);
13
14 END;
15
16 function BeforeReport return boolean is
17 begin
18
19 DECLARE
20 init_failure exception;
21 org_name hr_organization_units.name%TYPE;
22 member_name VARCHAR2(40);
23 role_type VARCHAR2(40);
24 enter_param VARCHAR2(80);
25 inv_status VARCHAR2(30);
26 prj_status VARCHAR2(100);
27 pca_date date;
28 draft_inv VARCHAR2(30);
29 disp_details VARCHAR2(30);
30 disp_unbilled VARCHAR2(30);
31 p_number VARCHAR2(30);
32 p_name VARCHAR2(30);
33
34 BEGIN
35
36
37 /*srw.user_exit('FND SRWINIT');*/null;
38
39
40
41 /*srw.user_exit('FND GETPROFILE
42 NAME="PA_RULE_BASED_OPTIMIZER"
43 FIELD=":p_rule_optimizer"
44 PRINT_ERROR="N"');*/null;
45
46
47
48
49
50
51
52
53 /*srw.user_exit('FND GETPROFILE
54 NAME="PA_DEBUG_MODE"
55 FIELD=":p_debug_mode"
56 PRINT_ERROR="N"');*/null;
57
58
59
60
61
62
63
64
65 /*srw.user_exit('FND GETPROFILE
66 NAME="CURRENCY:MIXED_PRECISION"
67 FIELD=":p_min_precision"
68 PRINT_ERROR="N"');*/null;
69
70
71
72
73 IF (p_start_organization_id is null and
74 project_member is null and
75 project_id is null) then
76 BEGIN
77
78
79
80
81 select start_organization_id into p_start_organization_id
82 from pa_implementations;
83
84 exception
85 when no_data_found then
86 null;
87 when others then
88 /*srw.message(2,'select start_organization_id in before_report' || sqlerrm);*/null;
89
90
91 END;
92 END IF;
93
94
95 IF p_start_organization_id is not null then
96 begin
97 select substr(name, 1, 40) into org_name from
98 hr_organization_units where
99 organization_id = p_start_organization_id;
100 exception
101 when no_data_found then
102 null;
103 when others then
104 /*srw.message(2,'Org ID ' || sqlerrm);*/null;
105
106 raise_application_error(-20101,null);/*srw.program_abort;*/null;
107
108 end;
109 END IF;
110 c_start_org := org_name;
111
112 IF project_member is not null then
113 begin
114
115 select substr(full_name, 1, 40) into member_name from
116 per_people_f where
117 person_id = project_member
118 and sysdate between effective_start_date
119 and nvl(effective_end_date,sysdate + 1)
120 and (Current_NPW_Flag='Y' OR Current_Employee_Flag='Y')
121 and Decode(Current_NPW_Flag,'Y',NPW_Number,employee_number) IS NOT NULL ;
122 exception
123 when no_data_found then
124 null;
125 when others then
126 /*srw.message(2,'Project Member ' || sqlerrm);*/null;
127
128 raise_application_error(-20101,null);/*srw.program_abort;*/null;
129 end;
130 END IF;
131 c_project_member := member_name;
132
133 IF project_role_type is not null then
134 begin
135 select substr(meaning,1,40) into role_type
136 from pa_project_role_types where
137 project_role_type = PA_PAXINGEN_XMLP_PKG.project_role_type;
138 exception
139 when no_data_found then
140 /*srw.message(2,'Role Type ' || sqlerrm);*/null;
141
142 null;
143 when others then
144 /*srw.message(2,'Role Type ' || sqlerrm);*/null;
145
146 raise_application_error(-20101,null);/*srw.program_abort;*/null;
147 end;
148 END IF;
149 c_role_type := role_type;
150
151 IF invoice_status is not null then
152 BEGIN
153 select
154 substr(meaning,1,30) into inv_status
155 from
156 pa_lookups
157 where
158 lookup_type = 'INVOICE STATUS'
159 and lookup_code = invoice_status;
160 exception
161 when no_data_found then
162 null;
163 when others then
164 /*srw.message(2,'Invoice Status ' || sqlerrm);*/null;
165
166 raise_application_error(-20101,null);/*srw.program_abort;*/null;
167 end;
168 END IF;
169 c_invoice_status := inv_status;
170
171 /* added for bug 7115649 */
172 IF project_closed_after is not null then
173 BEGIN
174 select
175 TO_CHAR(project_closed_after, 'DD-Mon-RRRR') INTO pca_date
176 from
177 dual ;
178 exception
179 when no_data_found then
180 null;
181 when others then
182 /*srw.message(2,'Project closed after ' || sqlerrm);*/null;
183
184 raise_application_error(-20101,null);/*srw.program_abort;*/null;
185 end;
186 END IF;
187 c_pca_date := pca_date ;
188
189 IF project_status is not null then
190 BEGIN
191 select
192 project_status_name into prj_status
193 from
194 pa_proj_statuses_v
195 where
196 project_status_code = project_status;
197 exception
198 when no_data_found then
199 null;
200 when others then
201 /*srw.message(2,'Project Status ' || sqlerrm);*/null;
202
203 raise_application_error(-20101,null);/*srw.program_abort;*/null;
204 end;
205 END IF;
206 c_project_status := prj_status;
207 /* added for bug 7115649 */
208
209 IF project_id is not null then
210 begin
211 select segment1,name
212 into p_number,p_name
213 from pa_projects
214 where project_id = PA_PAXINGEN_XMLP_PKG.project_id;
215 exception
216 when no_data_found then
217 null;
218 when others then
219 /*srw.message(2,'Project Number ' || sqlerrm);*/null;
220
221 raise_application_error(-20101,null);/*srw.program_abort;*/null;
222
223 end;
224 END IF;
225 c_project_num := p_number;
226 c_project_name := p_name;
227 IF display_details is not null then
228 begin
229 select substr(meaning,1,30) into disp_details
230 from fnd_lookups
231 where
232 lookup_type = 'YES_NO'
233 and lookup_code = display_details;
234 exception
235 when no_data_found then
236 null;
237 when others then
238 /*srw.message(2,'Display Details ' || sqlerrm);*/null;
239
240 raise_application_error(-20101,null);/*srw.program_abort;*/null;
241
242 END;
243
244 END IF;
245 C_display_details := disp_details;
246 IF display_unbilled_items is not null then
247 begin
248 select substr(meaning,1,30) into disp_unbilled
249 from fnd_lookups
250 where
251 lookup_type = 'YES_NO'
252 and lookup_code = display_unbilled_items;
253 exception
254 when no_data_found then
255 null;
256 when others then
257 /*srw.message(2,'Display Unbilled Items ' || sqlerrm);*/null;
258
259 raise_application_error(-20101,null);/*srw.program_abort;*/null;
260
261
262 end;
263 END IF;
264 C_display_unbilled := disp_unbilled;
265 IF draft_invoice is not null then
266 C_draft_invoice := draft_invoice;
267 END IF;
268
269 /* Added for bug 7115649 */
270 IF project_id is null Then
271 IF from_project_number is null then
272 begin
273 select min(p.segment1) into from_project_number
274 from pa_projects_all p, pa_project_types_all pt
275 where p.project_type = pt.project_type
276 and pt.project_type_class_code = 'CONTRACT';
277 exception
278 when no_data_found then
279 null;
280 when others then
281 raise_application_error(-20101,null);
282 end;
283 END IF;
284
285
286 IF to_project_number is null then
287 begin
288 select max(p.segment1) into to_project_number
289 from pa_projects_all p, pa_project_types_all pt
290 where p.project_type = pt.project_type
291 and pt.project_type_class_code = 'CONTRACT';
292 exception
293 when no_data_found then
294 null;
295 when others then
296 raise_application_error(-20101,null);
297 end;
298 END IF;
299 END IF;
300 /* End of code for bug 7115649 */
301
302 IF (get_company_name <> TRUE) THEN /*srw.message(2,'Company Name ' || sqlerrm);*/null;
303
304 RAISE init_failure;
305 END IF;
306 IF (project_id is null and
307 project_member is null and
308 p_start_organization_id is null) THEN
309 C_enter := enter_param;
310 ELSE
311 IF (get_start_org <> TRUE) THEN
312 /*srw.message(2,'call org ' || sqlerrm);*/null;
313
314 RAISE init_failure;
315 END IF;
316 END IF;
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 null;
320 WHEN OTHERS THEN
321 /*srw.message(2,' Global ' || sqlerrm);*/null;
322 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
323
324 END; return (TRUE);
325 end;
326
327 FUNCTION get_company_name RETURN BOOLEAN IS
328 l_name gl_sets_of_books.name%TYPE;
329 b_res boolean:=false;
330 BEGIN
331 begin
332 SELECT gl.name
333 INTO l_name
334 FROM gl_sets_of_books gl,pa_implementations pi
335 WHERE gl.set_of_books_id = pi.set_of_books_id;
336
337 c_company_name_header := l_name;
338
339 b_res :=true;
340
341
342 EXCEPTION
343 WHEN NO_DATA_FOUND THEN
344 /*srw.message(2,'Company Name missing');*/null;
345
346 b_res := false;
347
348 WHEN OTHERS THEN
349 /*srw.message(2,'Company Name Function ' || sqlerrm);*/null;
350
351 b_res := false;
352 end;
353
354 RETURN (b_res);
355
356 END;
357
358 FUNCTION get_start_org RETURN BOOLEAN IS
359 c_start_organization_id number;
360 c_project_organization_id number;
361 b_res boolean :=true;
362 BEGIN
363
364 BEGIN
365 select
366 decode(p_start_organization_id,null,
367 start_organization_id,p_start_organization_id)
368 into C_start_organization_id
369 from pa_implementations;
370 exception
371 when no_data_found then
372 null;
373 when others then
374 /*srw.message(2,'Fn Get Start Org ID ' || sqlerrm);*/null;
375
376 b_res := false;
377 END;
378
379 IF project_id is null then
380
381 begin
382
383 insert into
384 pa_org_reporting_sessions
385 (start_organization_id,session_id)
386 values
387 (c_start_organization_id,userenv('SESSIONID'));
388 exception
389 when no_data_found then
390 null;
391 when others then
392 /*srw.message(2,'Fn Get Start Org Id ' || sqlerrm);*/null;
393
394 b_res := false;
395 end;
396
397 ELSIF
398 project_id is not null then
399
400 begin
401 insert into
402 pa_org_reporting_sessions
403 (start_organization_id,session_id)
404 values
405 (null,userenv('SESSIONID'));
406 exception
407 when no_data_found then
408 null;
409 when others then
410 /*srw.message(2,'Get Start Org ID ' || sqlerrm);*/null;
411
412 b_res := false;
413 end;
414 END IF;
415 return(b_res);
416 END;
417
418 function G_project_hdrGroupFilter return boolean is
419 begin
420
421 IF (p_start_organization_id is null and
422 project_member is null and
423 project_id is null) then
424 RETURN(FALSE);
425 ELSE
426 return(TRUE);
427 end if; return (TRUE);
428 end;
429
430 function g_item_infogroupfilter(invoice_amount in number) return boolean is
431 begin
432
433 If invoice_amount <> 0 then
434 return(TRUE);
435 else
436 return(FALSE);
437 end if; return (TRUE);
438 end;
439
440 function g_item_detailsgroupfilter(bill_amount in number) return boolean is
441 begin
442
443 If (bill_amount <> 0) then
444 return(TRUE);
445 else
446 return(FALSE);
447 end if; return (TRUE);
448 end;
449
450 function g_unbilled_detailsgroupfilter(items_unbilled in number) return boolean is
451 begin
452
453 IF (items_unbilled >= 1) then
454 RETURN(TRUE);
455 else
456 return(FALSE);
457 end if; return (TRUE);
458 end;
459
460 function g_unbilled_eventsgroupfilter(event_amount_unbilled in number) return boolean is
461 begin
462
463 IF event_amount_unbilled <> 0 then
464 RETURN(TRUE);
465 else
466 RETURN(FALSE);
467 end if; return (TRUE);
468 end;
469
470 function g_invoicegroupfilter(invoice_amount in number) return boolean is
471 begin
472
473 If invoice_amount <> 0 then
474 return(TRUE);
475 else
476 return(FALSE);
477 end if; return (TRUE);
478 end;
479
480 function g_unbilled_infogroupfilter(items_unbilled in number, event_amount_unbilled in number) return boolean is
481 begin
482
483 IF (items_unbilled > 0 OR event_amount_unbilled > 0) then
484 return(TRUE);
485 ELSE
486 return(FALSE);
487 END IF; return (TRUE);
488 end;
489
490 function AfterReport return boolean is
491 begin
492
493 Begin
494 Rollback;
495 END;
496
497 /*srw.user_exit('FND SRWEXIT') ;*/null;
498
499 return (TRUE);
500 end;
501
502 function CF_CURENCY_CODEFormula return VARCHAR2 is
503 begin
504 return pa_multi_currency.get_acct_currency_code;
505 end;
506
507 function cf_cc_proj_labelformula(cc_project_number in varchar2) return char is
508 begin
509 IF cc_project_number IS NOT NULL THEN
510 return('Cross Charged Project Number: ');
511 ELSE
512 return(' ');
513 END IF;
514 end;
515
516 function g_retn_invoicegroupfilter(retention_invoice in varchar2) return boolean is
517 begin
518 if ( retention_invoice = 'Yes' ) then
519 return (TRUE);
520 else
521 return (FALSE);
522 end if ;
523 end;
524
525 function c_invproc_curr_typeformula(invproc_currency_type in varchar2) return char is
526 begin
527 /*SRW.REFERENCE(invproc_currency_type);*/null;
528
529 return(rtrim(invproc_currency_type));
530 end;
531
532 function c_credit_memo_reasonformula(credit_memo_reason_code in varchar2, invoice_date in date) return char is
533 l_reason varchar2(80);
534 begin
535 select meaning
536 into l_reason
537 from fnd_lookup_values_vl
538 where lookup_type='CREDIT_MEMO_REASON'
539 and enabled_flag='Y'
540 and lookup_code=credit_memo_reason_code
541 and invoice_date between start_date_active and nvl(end_date_active,invoice_date);
542 return(l_reason);
543 exception
544 when others then
545 return(null);
546
547 end;
548
549 function c_ubr_uerformula(unbilled_receivable in number) return number is
550 begin
551 RETURN(ABS(unbilled_receivable));
552 end;
553
554 function CF_PROJECT_CURRENCYFormula(project_id2 number) return VARCHAR2 is
555 begin
556 return pa_multi_currency_txn.get_proj_curr_code_sql(project_id2);
557 end;
558
559 --Functions to refer Oracle report placeholders--
560
561 Function C_ubr_uer_label_p return varchar2 is
562 Begin
563 return C_ubr_uer_label;
564 END;
565 Function C_COMPANY_NAME_HEADER_p return varchar2 is
566 Begin
567 return C_COMPANY_NAME_HEADER;
568 END;
569 Function C_start_org_p return varchar2 is
570 Begin
571 return C_start_org;
572 END;
573 Function C_project_member_p return varchar2 is
574 Begin
575 return C_project_member;
576 END;
577 Function C_role_type_p return varchar2 is
578 Begin
579 return C_role_type;
580 END;
581 Function C_enter_p return varchar2 is
582 Begin
583 return C_enter;
584 END;
585 Function C_invoice_status_p return varchar2 is
586 Begin
587 return C_invoice_status;
588 END;
589 /* added for bug 7115649 */
590 Function C_project_status_p return varchar2 is
591 Begin
592 return C_project_status;
593 END;
594 Function C_pca_date_p return date is
595 Begin
596 return C_pca_date;
597 END;
598 /* added for bug 7115649 */
599 Function C_project_num_p return varchar2 is
600 Begin
601 return C_project_num;
602 END;
603 Function C_project_name_p return varchar2 is
604 Begin
605 return C_project_name;
606 END;
607 Function C_display_details_p return varchar2 is
608 Begin
609 return C_display_details;
610 END;
611 Function C_display_unbilled_p return varchar2 is
612 Begin
613 return C_display_unbilled;
614 END;
615 Function C_draft_invoice_p return varchar2 is
616 Begin
617 return C_draft_invoice;
618 END;
619 END PA_PAXINGEN_XMLP_PKG ;
620
621
[Home] [Help]