[Home] [Help]
PACKAGE BODY: APPS.PA_MC_BILLING_PUB
Source
1 PACKAGE BODY pa_mc_billing_pub AS
2 /* $Header: PAMCPUBB.pls 120.3 2005/08/07 21:31:35 lveerubh noship $ */
3
4
5 PROCEDURE get_budget_amount(
6 p_project_id IN NUMBER,
7 p_project_number IN VARCHAR2 DEFAULT NULL ,
8 p_task_id IN NUMBER DEFAULT NULL,
9 p_task_number IN VARCHAR2 DEFAULT NULL,
10 p_psob_id IN NUMBER,
11 p_rsob_id IN NUMBER,
12 p_billing_extension_id IN NUMBER,
13 p_billing_extension_name IN VARCHAR2 DEFAULT NULL,
14 p_cost_budget_type_code IN VARCHAR2 DEFAULT NULL,
15 p_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
16 x_revenue_amount OUT NOCOPY NUMBER,
17 x_cost_amount OUT NOCOPY NUMBER,
18 x_cost_budget_type_code OUT NOCOPY VARCHAR2,
19 x_rev_budget_type_code OUT NOCOPY VARCHAR2,
20 x_error_message OUT NOCOPY VARCHAR2,
21 x_status OUT NOCOPY NUMBER)
22
23 IS
24
25
26 l_project_id NUMBER;
27 l_task_id NUMBER;
28 l_billing_extension_id NUMBER;
29
30 x_return_status VARCHAR2(30);
31 x_msg_count NUMBER;
32 x_msg_data VARCHAR2(240);
33
34 l_revenue_amount NUMBER;
35 l_cost_amount NUMBER;
36 l_cost_budget_type_code VARCHAR2(2000);
37 l_rev_budget_type_code VARCHAR2(2000);
38 l_error_message VARCHAR2(2000);
39 l_status NUMBER;
40
41 BEGIN
42
43 /* -----------------------------------------------------------
44 Initialize the Output Variables
45 ----------------------------------------------------------- */
46
47 l_error_message := null;
48 l_status := 0;
49
50
51
52 l_project_id := p_project_id;
53
54
55 IF (l_project_id IS NULL) AND (p_project_number IS NOT NULL) THEN
56
57
58 SELECT project_id
59 INTO l_project_id
60 FROM pa_projects_all
61 WHERE segment1 = p_project_number;
62
63
64 END IF;
65
66
67 l_task_id := p_task_id;
68
69
70 IF (l_task_id IS NULL) AND (p_task_number IS NOT NULL) THEN
71
72 SELECT task_id
73 INTO l_task_id
74 FROM pa_tasks
75 WHERE task_number = p_task_number
76 AND Project_ID = l_project_id; -- Added for Bug 2675566
77
78 END IF;
79
80
81
82 l_billing_extension_id := p_billing_extension_id;
83
84
85 IF (l_billing_extension_id IS NULL) AND (p_billing_extension_name IS NOT NULL) THEN
86
87 SELECT billing_extension_id
88 INTO l_billing_extension_id
89 FROM pa_billing_extensions
90 WHERE name = p_billing_extension_name ;
91
92 END IF;
93
94
95 /* dbms_output.put_line('Before calling the get_budget_amount API ......... '); */
96
97
98 pa_mc_billing_pvt.get_budget_amount(
99 l_project_id ,
100 l_task_id ,
101 p_psob_id ,
102 p_rsob_id ,
103 l_billing_extension_id,
104 p_cost_budget_type_code,
105 p_rev_budget_type_code,
106 l_revenue_amount ,
107 l_cost_amount ,
108 l_cost_budget_type_code ,
109 l_rev_budget_type_code ,
110 x_return_status ,
111 x_msg_count ,
112 x_msg_data );
113
114
115 IF (x_return_status <> 'S') THEN
116
117
118 IF substr(x_msg_data, 1,3) = 'ORA' THEN
119
120 l_error_message := x_msg_data;
121
122 ELSE
123
124 l_error_message := pa_billing_values.get_message(x_msg_data);
125
126 END IF;
127
128
129 l_status := x_msg_count ;
130
131 END IF;
132
133 x_status := l_status ;
134 x_error_message := l_error_message;
135 x_revenue_amount := l_revenue_amount;
136 x_cost_amount := l_cost_amount;
137 x_cost_budget_type_code := l_cost_budget_type_code;
138 x_rev_budget_type_code := l_rev_budget_type_code;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 x_error_message := SUBSTR(SQLERRM, 1, 240);
143 x_status := sqlcode;
144 x_revenue_amount := NULL;
145 x_cost_amount := NULL;
146 x_cost_budget_type_code := NULL;
147 x_rev_budget_type_code := NULL;
148 END get_budget_amount;
149
150
151
152 PROCEDURE get_cost_amount(
153 p_project_id IN NUMBER ,
154 p_project_number IN VARCHAR2 DEFAULT NULL,
155 p_task_id IN NUMBER DEFAULT NULL,
156 P_task_number IN VARCHAR2 DEFAULT NULL,
157 p_psob_id IN NUMBER DEFAULT NULL,
158 p_rsob_id IN NUMBER ,
159 p_accrue_through_date IN DATE DEFAULT NULL,
160 x_cost_amount OUT NOCOPY NUMBER ,
161 x_error_message OUT NOCOPY VARCHAR2,
162 x_status OUT NOCOPY NUMBER)
163 IS
164
165 l_project_id NUMBER;
166 l_task_id NUMBER;
167
168 x_return_status VARCHAR2(30);
169 x_msg_count NUMBER;
170 x_msg_data VARCHAR2(240);
171
172 --NOCOPY Changes
173 l_cost_amount NUMBER;
174 l_status NUMBER;
175 l_error_message VARCHAR2(2000);
176
177 BEGIN
178
179
180 /* -----------------------------------------------------------
181 Initialize the Output Variables
182 ----------------------------------------------------------- */
183 l_error_message := null;
184 l_status := 0;
185
186
187
188 l_project_id := p_project_id;
189
190
191 IF (l_project_id IS NULL) AND (p_project_number IS NOT NULL) THEN
192
193 SELECT project_id
194 INTO l_project_id
195 FROM pa_projects_all
196 WHERE segment1 = p_project_number;
197
198 END IF;
199
200
201 l_task_id := p_task_id;
202
203
204 IF (l_task_id IS NULL) AND (p_task_number IS NOT NULL) THEN
205
206 SELECT task_id
207 INTO l_task_id
208 FROM pa_tasks
209 WHERE task_number = p_task_number
210 AND Project_ID = l_project_id; -- Added for Bug 2675566
211
212 END IF;
213
214
215 pa_mc_billing_pvt.get_cost_amount(
216 l_project_id ,
217 l_task_id ,
218 p_psob_id ,
219 p_rsob_id ,
220 p_accrue_through_date ,
221 l_cost_amount ,
222 x_return_status ,
223 x_msg_count ,
224 x_msg_data );
225
226
227 IF (x_return_status <> 'S') THEN
228
229 IF substr(x_msg_data, 1,3) = 'ORA' THEN
230
231 l_error_message := x_msg_data;
232
233 ELSE
234
235 l_error_message := pa_billing_values.get_message(x_msg_data);
236
237 END IF;
238
239 l_status := x_msg_count ;
240
241 END IF;
242
243 --NOCOPY Changes
244 x_status := l_status;
245 x_error_message := l_error_message;
246 x_cost_amount := l_cost_amount;
247
248 EXCEPTION
249 WHEN OTHERS THEN
250 x_error_message := SUBSTR(SQLERRM, 1, 240);
251 x_status := sqlcode;
252 x_cost_amount := NULL;
253 END get_cost_amount;
254
255
256
257
258 PROCEDURE get_pot_event_amount(
259 p_project_id IN NUMBER,
260 p_project_number IN VARCHAR2 DEFAULT NULL,
261 p_task_id IN NUMBER DEFAULT NULL,
262 P_task_number IN VARCHAR2 DEFAULT NULL,
263 p_psob_id IN NUMBER DEFAULT NULL,
264 p_rsob_id IN NUMBER,
265 p_event_id IN NUMBER,
266 p_accrue_through_date IN DATE DEFAULT NULL,
267 x_event_amount OUT NOCOPY NUMBER,
268 x_error_message OUT NOCOPY VARCHAR2,
269 x_status OUT NOCOPY NUMBER)
270 IS
271
272 l_project_id NUMBER;
273 l_task_id NUMBER;
274
275
276 x_return_status VARCHAR2(30);
277 x_msg_count NUMBER;
278 x_msg_data VARCHAR2(240);
279
280 l_event_amount NUMBER;
281 l_error_message VARCHAR2(2000);
282 l_status NUMBER;
283 BEGIN
284
285
286 /* -----------------------------------------------------------
287 Initialize the Output Variables
288 ----------------------------------------------------------- */
289
290 l_error_message := null;
291 l_status := 0;
292
293
294 l_project_id := p_project_id;
295
296
297 IF (l_project_id IS NULL) AND (p_project_number IS NOT NULL) THEN
298
299 SELECT project_id
300 INTO l_project_id
301 FROM pa_projects_all
302 WHERE segment1 = p_project_number;
303
304 END IF;
305
306
307
308 l_task_id := p_task_id;
309
310
311 IF (l_task_id IS NULL) AND (p_task_number IS NOT NULL) THEN
312
313 SELECT task_id
314 INTO l_task_id
315 FROM pa_tasks
316 WHERE task_number = p_task_number
317 AND Project_ID = l_project_id; -- Added for Bug 2675566
318
319 END IF;
320
321
322
323 pa_mc_billing_pvt.get_pot_event_amount(
324 l_project_id ,
325 l_task_id ,
326 p_psob_id ,
327 p_rsob_id ,
328 p_event_id,
329 p_accrue_through_date ,
330 l_event_amount ,
331 x_return_status ,
332 x_msg_count ,
333 x_msg_data );
334
335
336 IF (x_return_status <> 'S') THEN
337
338 IF substr(x_msg_data, 1,3) = 'ORA' THEN
339
340 l_error_message := x_msg_data;
341
342 ELSE
343
344 l_error_message := pa_billing_values.get_message(x_msg_data);
345
346 END IF;
347
348 l_status := x_msg_count ;
349
350 END IF;
351
352 x_status := l_status;
353 x_error_message := l_error_message;
354 x_event_amount := l_event_amount;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 x_error_message := SUBSTR(SQLERRM, 1, 240);
359 x_status := sqlcode;
360 x_event_amount := NULL;
361 END get_pot_event_amount;
362
363
364
365
366 PROCEDURE get_Lowest_amount_left(
367 p_project_id IN NUMBER,
368 p_project_number IN VARCHAR2 DEFAULT NULL,
369 p_task_id IN NUMBER DEFAULT NULL,
370 P_task_number IN VARCHAR2 DEFAULT NULL,
371 p_psob_id IN NUMBER DEFAULT NULL,
372 p_rsob_id IN NUMBER,
373 p_event_id IN NUMBER,
374 x_funding_amount OUT NOCOPY NUMBER,
375 x_error_message OUT NOCOPY VARCHAR2,
376 x_status OUT NOCOPY NUMBER)
377 IS
378
379 l_project_id NUMBER;
380 l_task_id NUMBER;
381
382
383 x_return_status VARCHAR2(30);
384 x_msg_count NUMBER;
385 x_msg_data VARCHAR2(240);
386
387 l_funding_amount NUMBER;
388 l_error_message VARCHAR2(2000);
389 l_status NUMBER;
390
391
392 BEGIN
393
394
395 /* -----------------------------------------------------------
396 Initialize the Output Variables
397 ----------------------------------------------------------- */
398
399 l_error_message := null;
400 l_status := 0;
401
402
403 l_project_id := p_project_id;
404
405
406 IF (l_project_id IS NULL) AND (p_project_number IS NOT NULL) THEN
407
408 SELECT project_id
409 INTO l_project_id
410 FROM pa_projects_all
411 WHERE segment1 = p_project_number;
412
413 END IF;
414
415
416
417 l_task_id := p_task_id;
418
419 IF (l_task_id IS NULL) AND (p_task_number IS NOT NULL) THEN
420
421 SELECT task_id
422 INTO l_task_id
423 FROM pa_tasks
424 WHERE task_number = p_task_number
425 AND Project_ID = l_project_id; -- Added for Bug 2675566
426
427 END IF;
428
429
430
431
432 pa_mc_billing_pvt.get_Lowest_amount_left(
433 l_project_id ,
434 l_task_id ,
435 p_psob_id ,
436 p_rsob_id ,
437 p_event_id,
438 l_funding_amount ,
439 x_return_status ,
440 x_msg_count ,
441 x_msg_data );
442
443
444 IF (x_return_status <> 'S') THEN
445
446
447 IF substr(x_msg_data, 1,3) = 'ORA' THEN
448
449 l_error_message := x_msg_data;
450
451 ELSE
452
453 l_error_message := pa_billing_values.get_message(x_msg_data);
454
455 END IF;
456
457 l_status := x_msg_count ;
458
459 END IF;
460
461 x_status := l_status;
462 x_error_message := l_error_message;
463 x_funding_amount := l_funding_amount;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 x_error_message := SUBSTR(SQLERRM, 1, 240);
468 x_status := sqlcode;
469 x_funding_amount := NULL;
470 END get_Lowest_amount_left;
471
472
473
474
475 PROCEDURE get_revenue_amount(
476 p_project_id IN NUMBER,
477 p_project_number IN VARCHAR2 DEFAULT NULL,
478 p_task_id IN NUMBER DEFAULT NULL,
479 P_task_number IN VARCHAR2 DEFAULT NULL,
480 p_psob_id IN NUMBER DEFAULT NULL,
481 p_rsob_id IN NUMBER,
482 p_event_id IN NUMBER,
483 x_revenue_amount OUT NOCOPY NUMBER,
484 x_error_message OUT NOCOPY VARCHAR2,
485 x_status OUT NOCOPY NUMBER)
486 IS
487
488
489 l_project_id NUMBER;
490 l_task_id NUMBER;
491
492 x_return_status VARCHAR2(30);
493 x_msg_count NUMBER;
494 x_msg_data VARCHAR2(240);
495
496 l_revenue_amount NUMBER;
497 l_error_message VARCHAR2(2000);
498 l_status NUMBER;
499
500 BEGIN
501
502
503 /* -----------------------------------------------------------
504 Initialize the Output Variables
505 ----------------------------------------------------------- */
506
507 l_error_message := null;
508 l_status := 0;
509
510
511 l_project_id := p_project_id;
512
513
514 IF (l_project_id IS NULL) AND (p_project_number IS NOT NULL) THEN
515
516 SELECT project_id
517 INTO l_project_id
518 FROM pa_projects_all
519 WHERE segment1 = p_project_number;
520
521 END IF;
522
523
524 l_task_id := p_task_id;
525
526
527 IF (l_task_id IS NULL) AND (p_task_number IS NOT NULL) THEN
528
529 SELECT task_id
530 INTO l_task_id
531 FROM pa_tasks
532 WHERE task_number = p_task_number
533 AND Project_ID = l_project_id; -- Added for Bug 2675566
534
535 END IF;
536
537
538
539
540 pa_mc_billing_pvt.get_revenue_amount(
541 p_project_id ,
542 l_task_id ,
543 p_psob_id ,
544 p_rsob_id ,
545 p_event_id,
546 l_revenue_amount ,
547 x_return_status ,
548 x_msg_count ,
549 x_msg_data );
550
551
552 IF (x_return_status <> 'S') THEN
553
554 IF substr(x_msg_data, 1,3) = 'ORA' THEN
555
556 l_error_message := x_msg_data;
557
558 ELSE
559
560 l_error_message := pa_billing_values.get_message(x_msg_data);
561
562 END IF;
563
564 l_status := x_msg_count ;
565
566 END IF;
567
568 x_revenue_amount := l_revenue_amount;
569 x_status := l_status;
570 x_error_message := l_error_message;
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 x_error_message := SUBSTR(SQLERRM, 1, 240);
575 x_status := sqlcode;
576 x_revenue_amount := NULL;
577 END get_revenue_amount;
578
579
580
581 END pa_mc_billing_pub;