1 PACKAGE BODY PA_PAXPRCON_XMLP_PKG AS
2 /* $Header: PAXPRCONB.pls 120.0 2008/01/02 11:49:39 krreddy noship $ */
3 FUNCTION get_cover_page_values RETURN BOOLEAN IS
4 BEGIN
5 RETURN(TRUE);
6 EXCEPTION
7 WHEN OTHERS THEN
8 RETURN(FALSE);
9 END;
10 function BeforeReport return boolean is
11 begin
12 Declare
13 init_failure exception;
14 p_name VARCHAR2(30);
15 p_number VARCHAR2(30);
16 p_ptype VARCHAR2(20);
17 p_bill_id NUMBER;
18 p_bill_name VARCHAR2(30);
19 BEGIN
20 IF (no_data_found_func <> TRUE) THEN
21 RAISE init_failure;
22 END IF;
23 /*srw.user_exit('FND SRWINIT');*/null;
24 /*IF p_mrcsobtype = 'R'
25 THEN
26 fnd_client_info.set_currency_context(p_ca_set_of_books_id);
27 END IF;*/
28 Select Decode ( pa_install.is_billing_licensed (), 'Y','N','Y')
29 Into p_costing
30 From Dual;
31 /*srw.user_exit('FND GETPROFILE
32 NAME="PA_DEBUG_MODE"
33 FIELD=":p_debug_mode"
34 PRINT_ERROR="N"');*/null;
35 /*srw.user_exit('FND GETPROFILE
36 NAME="PA_RULE_BASED_OPTIMIZER"
37 FIELD=":p_rule_optimizer"
38 PRINT_ERROR="N"');*/null;
39 SELECT name, segment1, project_type INTO p_name, p_number, p_ptype
40 FROM pa_projects
41 WHERE project_id = p_project_id;
42 c_project_name := p_name;
43 c_project_number := p_number;
44 SELECT billing_cycle_id INTO p_bill_id FROM pa_project_types
45 WHERE project_type = p_ptype ;
46 IF p_bill_id IS NOT NULL THEN
47 SELECT billing_cycle_name INTO p_bill_name
48 FROM pa_billing_cycles
49 WHERE billing_cycle_id = p_bill_id ;
50 END IF;
51 cp_bill_name := p_bill_name ;
52 IF (get_company_name <> TRUE) THEN RAISE init_failure;
53 END IF;
54 IF (no_data_found_func <> TRUE) THEN
55 RAISE init_failure;
56 END IF;
57 EXCEPTION
58 WHEN OTHERS THEN
59 null;
60 END; return (TRUE);
61 end;
62 FUNCTION get_company_name RETURN BOOLEAN IS
63 l_name gl_sets_of_books.name%TYPE;
64 BEGIN
65 SELECT name
66 INTO l_name
67 FROM gl_sets_of_books
68 WHERE set_of_books_id = (SELECT set_of_books_id
69 FROM pa_implementations_all
70 WHERE org_id = (SELECT org_id
71 FROM pa_projects_all
72 WHERE project_id = p_project_id ));
73 c_company_name_header := l_name;
74 RETURN (TRUE);
75 EXCEPTION
76 WHEN OTHERS THEN
77 RETURN (FALSE);
78 END;
79 Function NO_DATA_FOUND_FUNC RETURN BOOLEAN IS
80 message_name VARCHAR2(80);
81 BEGIN
82 select
83 meaning
84 into
85 message_name
86 from
87 pa_lookups
88 where
89 lookup_type = 'MESSAGE'
90 and lookup_code = 'NO_DATA_FOUND';
91 c_no_data_found := replace(message_name,'*','');
92 RETURN(TRUE);
93 EXCEPTION
94 when others then
95 RETURN(FALSE);
96 END;
97 function AfterReport return boolean is
98 begin
99 BEGIN
100 /*srw.user_exit('FND SRWEXIT');*/null;
101 END;
102 return (TRUE);
103 end;
104 function g_rev_billgroupfilter(ptcc in varchar2) return boolean is
105 begin
106 IF (ptcc = 'CONTRACT') then
107 return(TRUE);
108 ELSE
109 return(FALSE);
110 END IF; return (TRUE);
111 end;
112 function g_job_bill_ratesgroupfilter(ptcc in varchar2) return boolean is
113 begin
114 IF (ptcc = 'CONTRACT') then
115 return(TRUE);
116 ELSE
117 return(FALSE);
118 END IF; return (TRUE);
119 end;
120 function g_emp_bill_ratesgroupfilter(ptcc in varchar2) return boolean is
121 begin
122 IF (ptcc = 'CONTRACT') then
123 return(TRUE);
124 ELSE
125 return(FALSE);
126 END IF; return (TRUE);
127 end;
128 function g_nl_bill_ratesgroupfilter(ptcc in varchar2) return boolean is
129 begin
130 IF (ptcc = 'CONTRACT') then
131 return(TRUE);
132 ELSE
133 return(FALSE);
134 END IF; return (TRUE);
135 end;
136 function g_job_title_orgroupfilter(ptcc in varchar2) return boolean is
137 begin
138 IF (ptcc = 'CONTRACT') then
139 return(TRUE);
140 ELSE
141 return(FALSE);
142 END IF; return (TRUE);
143 end;
144 function g_job_assgn_orgroupfilter(ptcc in varchar2) return boolean is
145 begin
146 IF (ptcc = 'CONTRACT') then
147 return(TRUE);
148 ELSE
149 return(FALSE);
150 END IF; return (TRUE);
151 end;
152 function g_labor_multipliersgroupfilter(ptcc in varchar2) return boolean is
153 begin
154 IF (ptcc = 'CONTRACT') then
155 return(TRUE);
156 ELSE
157 return(FALSE);
158 END IF; return (TRUE);
159 end;
160 function G_customerGroupFilter return boolean is
161 begin
162 return (TRUE);
163 end;
164 function G_contactsGroupFilter return boolean is
165 begin
166 return (TRUE);
167 end;
168 function g_project_assetgroupfilter(ptcc in varchar2) return boolean is
169 begin
170 if ptcc = 'CAPITAL' then
171 return true;
172 else
173 return false;
174 end if; return (TRUE);
175 end;
176 function cf_baselineformula(baseline_funding_flag in varchar2) return char is
177 tmp_over_flag VARCHAR2(80);
178 begin
179 IF baseline_funding_flag IS NOT NULL THEN
180 SELECT meaning INTO tmp_over_flag
181 FROM fnd_lookups
182 WHERE lookup_type = 'YES_NO' AND lookup_code = baseline_funding_flag ;
183 RETURN tmp_over_flag;
184 ELSE
185 RETURN NULL;
186 END IF;
187 EXCEPTION
188 WHEN OTHERS THEN
189 RAISE;
190 end;
191 function cf_revaluateformula(revaluate_funding_flag in varchar2) return char is
192 tmp_over_flag VARCHAR2(80);
193 begin
194 IF revaluate_funding_flag IS NOT NULL THEN
195 SELECT meaning INTO tmp_over_flag
196 FROM fnd_lookups
197 WHERE lookup_type = 'YES_NO' AND lookup_code = revaluate_funding_flag ;
198 RETURN tmp_over_flag;
199 ELSE
200 RETURN NULL;
201 END IF;
202 EXCEPTION
203 WHEN OTHERS THEN
204 RAISE;
205 end;
206 function cf_includeformula(include_gains_losses_flag in varchar2) return char is
207 tmp_over_flag VARCHAR2(80);
208 begin
209 IF include_gains_losses_flag IS NOT NULL THEN
210 SELECT meaning INTO tmp_over_flag
211 FROM fnd_lookups
212 WHERE lookup_type = 'YES_NO' AND lookup_code = include_gains_losses_flag ;
213 RETURN tmp_over_flag;
214 ELSE
215 RETURN NULL;
216 END IF;
217 EXCEPTION
218 WHEN OTHERS THEN
219 RAISE;
220 end;
221 function cf_emp_reasonformula(emp_disc_reason in varchar2) return char is
222 l_reason pa_lookups.meaning%TYPE;
223 Begin
224 select meaning
225 into l_reason
226 from pa_lookups
227 where lookup_type ='RATE AND DISCOUNT REASON'
228 and lookup_code= emp_disc_reason;
229 return(l_reason);
230 EXCEPTION
231 when others then
232 l_reason:=NULL;
233 return(l_reason);
234 end;
235 function cf_nl_reasonformula(nl_disc_reason in varchar2) return char is
236 l_reason pa_lookups.meaning%TYPE;
237 Begin
238 select meaning
239 into l_reason
240 from pa_lookups
241 where lookup_type ='RATE AND DISCOUNT REASON'
242 and lookup_code= nl_disc_reason;
243 return(l_reason);
244 EXCEPTION
245 when others then
246 l_reason:=NULL;
247 return(l_reason);
248 end;
249 function cf_job_reasonformula(job_disc_reason in varchar2) return char is
250 l_reason pa_lookups.meaning%TYPE;
251 Begin
252 select meaning
253 into l_reason
254 from pa_lookups
255 where lookup_type ='RATE AND DISCOUNT REASON'
256 and lookup_code= job_disc_reason;
257 return(l_reason);
258 EXCEPTION
259 when others then
260 l_reason:=NULL;
261 return(l_reason);
262 end;
263 function AfterPForm return boolean is
264 begin
265 BEGIN
266 select decode(mrc_sob_type_code,'R','R','P')
267 into p_mrcsobtype
268 from gl_sets_of_books
269 where set_of_books_id = (SELECT set_of_books_id
270 FROM pa_implementations_all
271 WHERE org_id = (SELECT org_id
272 FROM pa_projects_all
273 WHERE project_id = p_project_id )
274 );
275 EXCEPTION
276 WHEN OTHERS THEN
277 p_mrcsobtype := 'P';
278 END;
279 IF p_mrcsobtype = 'R'
280 THEN
281 lp_pa_events := 'PA_EVENTS_MRC_V';
282 ELSE
283 lp_pa_events := 'PA_EVENTS';
284 END IF;
285 return (TRUE);
286 end;
287 function cf_bill_to_customerformula(bill_to_customer_id in number) return char is
288 tmp_flag VARCHAR2(80);
289 begin
290 IF bill_to_customer_id IS NOT NULL THEN
291 select substr(party.party_name,1,50) into tmp_flag
292 from
293 hz_parties party,
294 hz_cust_accounts cust_acct
295 where
296 party.party_id = cust_acct.party_id
297 and cust_acct.cust_account_id = bill_to_customer_id;
298 RETURN tmp_flag;
299 ELSE
300 RETURN NULL;
301 END IF;
302 EXCEPTION
303 WHEN OTHERS THEN
304 RAISE;
305 end;
306 function cf_ship_to_customerformula(ship_to_customer_id in number) return char is
307 tmp_flag VARCHAR2(80);
308 begin
309 IF ship_to_customer_id IS NOT NULL THEN
310 SELECT substrb(party.party_name,1,50) INTO tmp_flag
311 FROM
312 hz_parties party,
313 hz_cust_accounts cust_acct
314 WHERE
315 party.party_id = cust_acct.party_id
316 and cust_acct.cust_account_id = ship_to_customer_id;
317 RETURN tmp_flag;
318 ELSE
319 RETURN NULL;
320 END IF;
321 EXCEPTION
322 WHEN OTHERS THEN
323 RAISE;
324 end;
325 function cf_bill_to_cust_noformula(bill_to_customer_id in number) return char is
326 l_cust_no varchar2(100);begin
327 IF bill_to_customer_id IS NOT NULL THEN
328 SELECT cust_acct.account_number INTO l_cust_no
329 FROM
330 hz_parties party,
331 hz_cust_accounts cust_acct
332 WHERE
333 party.party_id = cust_acct.party_id
334 and cust_acct.cust_account_id = bill_to_customer_id;
335 RETURN l_cust_no;
336 ELSE
337 RETURN NULL;
338 END IF;
339 EXCEPTION
340 WHEN OTHERS THEN
341 RAISE;
342 end;
343 function cf_ship_to_cust_noformula(ship_to_customer_id in number) return char is
344 l_cust_no varchar2(100);begin
345 IF ship_to_customer_id IS NOT NULL THEN
346 SELECT cust_acct.account_number INTO l_cust_no
347 FROM
348 hz_parties party,
349 hz_cust_accounts cust_acct
350 WHERE
351 party.party_id = cust_acct.party_id
352 and cust_acct.cust_account_id = ship_to_customer_id;
353 RETURN l_cust_no;
354 ELSE
355 RETURN NULL;
356 END IF;
357 EXCEPTION
358 WHEN OTHERS THEN
359 RAISE;
360 end;
361 function cf_customerformula(enable_top_task_customer_flag in varchar2, project_id_1 in number) return char is
362 tmp_flag VARCHAR2(80);
363 begin
364 IF enable_top_task_customer_flag IS NOT NULL THEN
365 SELECT meaning INTO tmp_flag
366 FROM pa_lookups lkp, pa_projects prj
367 WHERE lkp.lookup_type = 'YES_NO'
368 AND lkp.lookup_code = prj.enable_top_task_customer_flag
369 -- AND prj.project_id = project_id;
370 AND prj.project_id = project_id_1;
371 RETURN tmp_flag;
372 ELSE
373 RETURN NULL;
374 END IF;
375 EXCEPTION
376 WHEN OTHERS THEN
377 RAISE;
378 end;
379 function cf_inv_methodformula(enable_top_task_inv_mth_flag in varchar2, project_id_1 in number) return char is
380 tmp_flag VARCHAR2(80);
381 begin
382 IF enable_top_task_inv_mth_flag IS NOT NULL THEN
383 SELECT meaning INTO tmp_flag
384 FROM pa_lookups lkp, pa_projects prj
385 WHERE lkp.lookup_type = 'YES_NO'
386 AND lkp.lookup_code = prj.enable_top_task_inv_mth_flag
387 -- AND prj.project_id = project_id;
388 AND prj.project_id = project_id_1;
389 RETURN tmp_flag;
390 ELSE
391 RETURN NULL;
392 END IF;
393 EXCEPTION
394 WHEN OTHERS THEN
395 RAISE;
396 end;
397 function cf_rev_acc_mthformula(revenue_accrual_method in varchar2, project_id_1 in number) return char is
398 tmp_flag VARCHAR2(80);
399 begin
400 IF revenue_accrual_method IS NOT NULL THEN
401 SELECT meaning INTO tmp_flag
402 FROM pa_lookups lkp, pa_projects prj
403 WHERE lkp.lookup_type = 'REVENUE ACCRUAL METHOD'
404 AND lkp.lookup_code = prj.revenue_accrual_method
405 -- AND prj.project_id = project_id;
406 AND prj.project_id = project_id_1;
407 RETURN tmp_flag;
408 ELSE
409 RETURN NULL;
410 END IF;
411 EXCEPTION
412 WHEN OTHERS THEN
413 RAISE;
414 end;
415 function cf_inv_mthformula(invoice_method in varchar2, project_id_1 in number) return char is
416 tmp_flag VARCHAR2(80);
417 begin
418 IF invoice_method IS NOT NULL THEN
419 SELECT meaning INTO tmp_flag
420 FROM pa_lookups lkp, pa_projects prj
421 WHERE lkp.lookup_type = 'INVOICE METHOD'
422 AND lkp.lookup_code = prj.invoice_method
423 -- AND prj.project_id = project_id;
424 AND prj.project_id = project_id_1;
425 RETURN tmp_flag;
426 ELSE
427 RETURN NULL;
428 END IF;
429 EXCEPTION
430 WHEN OTHERS THEN
431 RAISE;
432 end;
433 --Functions to refer Oracle report placeholders--
434 Function C_COMPANY_NAME_HEADER_p return varchar2 is
435 Begin
436 return C_COMPANY_NAME_HEADER;
437 END;
438 Function C_project_name_p return varchar2 is
439 Begin
440 return C_project_name;
441 END;
442 Function C_project_number_p return varchar2 is
443 Begin
444 return C_project_number;
445 END;
446 Function C_no_data_found_p return varchar2 is
447 Begin
448 return C_no_data_found;
449 END;
450 Function CP_bill_name_p return varchar2 is
451 Begin
452 return CP_bill_name;
453 END;
454 END PA_PAXPRCON_XMLP_PKG ;
455
[Home] [Help]