[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_COMP_AMT_PKG
Source
1 PACKAGE BODY IGC_CC_COMP_AMT_PKG AS
2 /*$Header: IGCCCBAB.pls 120.5.12000000.1 2007/08/20 12:11:21 mbremkum ship $*/
3
4 FUNCTION COMPUTE_ACCT_BILLED_AMT_CURR(p_cc_acct_line_id NUMBER)
5 RETURN NUMBER
6 IS
7 l_func_billed_amount NUMBER := 0;
8 l_cc_num igc_cc_headers.cc_num%TYPE;
9 l_cc_type igc_cc_headers.cc_type%TYPE;
10 l_org_id igc_cc_headers.org_id%TYPE;
11 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
12 l_cc_acct_line_num igc_cc_acct_lines.cc_acct_line_num%TYPE;
13 BEGIN
14
15 SELECT cc_acct_line_num, cc_header_id
16 INTO l_cc_acct_line_num, l_cc_header_id
17 FROM igc_cc_acct_lines
18 WHERE cc_acct_line_id = p_cc_acct_line_id;
19
20 SELECT cc_num, org_id, cc_type
21 INTO l_cc_num, l_org_id, l_cc_type
22 FROM igc_cc_headers
23 WHERE cc_header_id = l_cc_header_id;
24
25 IF ( (l_cc_type = 'S') OR (l_cc_type = 'R') )
26 THEN
27 BEGIN
28
29 SELECT NVL(SUM(DECODE(apid.base_amount,NULL,apid.amount,apid.base_amount)),0)
30 INTO l_func_billed_amount
31 FROM
32 ap_invoice_distributions_all apid,
33 po_distributions_all pod,
34 po_line_locations_all pll,
35 po_lines_all pol,
36 po_headers_all poh
37 WHERE
38 apid.po_distribution_id = pod.po_distribution_id AND
39 apid.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND
40 poh.segment1 = l_cc_num AND
41 poh.type_lookup_code = 'STANDARD' AND
42 poh.org_id = l_org_id AND
43 pol.po_header_id = poh.po_header_id AND
44 pol.line_num = l_cc_acct_line_num AND
45 pll.po_line_id = pol.po_line_id AND
46 pll.po_header_id = pol.po_header_id AND
47 pod.po_header_id = pll.po_header_id AND
48 pod.po_line_id = pll.po_line_id AND
49 pod.line_location_id = pll.line_location_id ;
50 EXCEPTION
51 WHEN NO_DATA_FOUND
52 THEN
53 l_func_billed_amount := 0;
54 END;
55
56 ELSIF (l_cc_type = 'C')
57 THEN
58 BEGIN
59 -- Performance fixes. Replaced the following sql
60 -- with the one below.
61 /*
62 SELECT NVL(SUM(NVL(cc_acct_func_billed_amt,0)),0)
63 INTO l_func_billed_amount
64 FROM igc_cc_acct_lines
65 WHERE parent_acct_line_id = p_cc_acct_line_id;
66 */
67 SELECT NVL(SUM(NVL(IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id),0)),0)
68 INTO l_func_billed_amount
69 FROM igc_cc_acct_lines ccal
70 WHERE ccal.parent_acct_line_id = p_cc_acct_line_id;
71 EXCEPTION
72 WHEN NO_DATA_FOUND
73 THEN
74 l_func_billed_amount := 0;
75 END;
76 END IF;
77
78 RETURN(l_func_billed_amount);
79
80 END COMPUTE_ACCT_BILLED_AMT_CURR;
81
82 FUNCTION COMPUTE_PF_BILL_AMT_CURR(p_cc_det_pf_line_id NUMBER,
83 p_cc_det_pf_line_num NUMBER,
84 p_cc_acct_line_id NUMBER)
85 RETURN NUMBER
86 IS
87 l_func_billed_amount NUMBER := 0;
88 l_cc_num igc_cc_headers.cc_num%TYPE;
89 l_cc_type igc_cc_headers.cc_type%TYPE;
90 l_org_id igc_cc_headers.org_id%TYPE;
91 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
92 l_cc_acct_line_num igc_cc_acct_lines.cc_acct_line_num%TYPE;
93 BEGIN
94
95 SELECT cc_acct_line_num, cc_header_id
96 INTO l_cc_acct_line_num, l_cc_header_id
97 FROM igc_cc_acct_lines
98 WHERE cc_acct_line_id = p_cc_acct_line_id;
99
100 SELECT cc_num, org_id, cc_type
101 INTO l_cc_num, l_org_id, l_cc_type
102 FROM igc_cc_headers
103 WHERE cc_header_id = l_cc_header_id;
104
105 IF ( (l_cc_type = 'S') OR (l_cc_type = 'R') )
106 THEN
107
108 BEGIN
109
110 SELECT NVL(SUM(DECODE(apid.base_amount,NULL,apid.amount,apid.base_amount)),0)
111 INTO l_func_billed_amount
112 FROM
113 ap_invoice_distributions_all apid,
114 po_distributions_all pod,
115 po_line_locations_all pll,
116 po_lines_all pol,
117 po_headers_all poh
118 WHERE
119 apid.po_distribution_id = pod.po_distribution_id AND
120 apid.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND
121 poh.segment1 = l_cc_num AND
122 poh.type_lookup_code = 'STANDARD' AND
123 poh.org_id = l_org_id AND
124 pol.po_header_id = poh.po_header_id AND
125 pol.line_num = l_cc_acct_line_num AND
126 pll.po_line_id = pol.po_line_id AND
127 pll.po_header_id = pol.po_header_id AND
128 pod.po_header_id = pll.po_header_id AND
129 pod.po_line_id = pll.po_line_id AND
130 pod.line_location_id = pll.line_location_id AND
131 pod.distribution_num = p_cc_det_pf_line_num;
132 EXCEPTION
133 WHEN NO_DATA_FOUND
134 THEN
135 l_func_billed_amount := 0;
136 END;
137 ELSIF (l_cc_type = 'C')
138 THEN
139 BEGIN
140 SELECT NVL(SUM(NVL(cc_det_pf_func_billed_amt,0)),0)
141 INTO l_func_billed_amount
142 FROM igc_cc_det_pf_v
143 WHERE parent_det_pf_line_id = p_cc_det_pf_line_id;
144 EXCEPTION
145 WHEN NO_DATA_FOUND
146 THEN
147 l_func_billed_amount := 0;
148 END;
149 END IF;
150
151 RETURN(l_func_billed_amount);
152
153 END COMPUTE_PF_BILL_AMT_CURR;
154
155 FUNCTION COMPUTE_PF_BILLED_AMT(p_cc_det_pf_line_id NUMBER,
156 p_cc_det_pf_line_num NUMBER,
157 p_cc_acct_line_id NUMBER)
158 RETURN NUMBER
159 IS
160 l_billed_amount NUMBER := 0;
161 l_cc_num igc_cc_headers.cc_num%TYPE;
162 l_cc_type igc_cc_headers.cc_type%TYPE;
163 l_org_id igc_cc_headers.org_id%TYPE;
164 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
165 l_cc_acct_line_num igc_cc_acct_lines.cc_acct_line_num%TYPE;
166 l_sob_id igc_cc_headers.set_of_books_id%TYPE;
167 l_currency_code igc_cc_headers.currency_code%TYPE;
168 l_func_currency_code igc_cc_headers.currency_code%TYPE;
169 BEGIN
170
171 SELECT cc_acct_line_num, cc_header_id
172 INTO l_cc_acct_line_num, l_cc_header_id
173 FROM igc_cc_acct_lines
174 WHERE cc_acct_line_id = p_cc_acct_line_id;
175
176 SELECT cc_num, org_id, cc_type, set_of_books_id, currency_code
177 INTO l_cc_num, l_org_id, l_cc_type, l_sob_id, l_currency_code
178 FROM igc_cc_headers
179 WHERE cc_header_id = l_cc_header_id;
180
181 SELECT currency_code
182 INTO l_func_currency_code
183 FROM gl_sets_of_books
184 WHERE set_of_books_id = l_sob_id;
185
186 IF ( (l_cc_type = 'S') OR (l_cc_type = 'R') )
187 THEN
188
189 BEGIN
190
191 SELECT NVL(pod.amount_billed,0)
192 INTO l_billed_amount
193 FROM
194 po_headers_all poh,
195 po_lines_all pol,
196 po_line_locations_all pll,
197 po_distributions_all pod
198 WHERE
199 poh.segment1 = l_cc_num AND
200 poh.type_lookup_code = 'STANDARD' AND
201 poh.org_id = l_org_id AND
202 pol.po_header_id = poh.po_header_id AND
203 pol.line_num = l_cc_acct_line_num AND
204 pll.po_line_id = pol.po_line_id AND
205 pll.po_header_id = pol.po_header_id AND
206 pod.po_header_id = pll.po_header_id AND
207 pod.po_line_id = pll.po_line_id AND
208 pod.line_location_id = pll.line_location_id AND
209 pod.distribution_num = p_cc_det_pf_line_num;
210 EXCEPTION
211 WHEN NO_DATA_FOUND
212 THEN
213 l_billed_amount := 0;
214 END;
215 ELSIF (l_cc_type = 'C')
216 THEN
217
218 -- Bug # 1459569, 1520481.
219
220 l_billed_amount := 0;
221
222 IF (l_currency_code <> l_func_currency_code)
223 THEN
224
225 BEGIN
226 SELECT NVL(SUM(NVL(cc_det_pf_billed_amt,0)),0)
227 INTO l_billed_amount
228 FROM igc_cc_det_pf_v
229 WHERE parent_det_pf_line_id = p_cc_det_pf_line_id;
230 EXCEPTION
231 WHEN NO_DATA_FOUND
232 THEN
233 l_billed_amount := 0;
234 END;
235
236 END IF;
237
238 IF (l_currency_code = l_func_currency_code)
239 THEN
240
241 BEGIN
242 SELECT NVL(SUM(NVL(COMPUTE_PF_BILL_AMT_CURR(det.cc_det_pf_line_id,
243 det.cc_det_pf_line_num,det.cc_acct_line_id),0)),0)
244 INTO l_billed_amount
245 FROM igc_cc_det_pf det
246 where parent_det_pf_line_id=p_cc_det_pf_line_id;
247 EXCEPTION
248 WHEN NO_DATA_FOUND
249 THEN
250 l_billed_amount := 0;
251 END;
252
253 END IF;
254
255
256 END IF;
257
258 RETURN(l_billed_amount);
259
260 END COMPUTE_PF_BILLED_AMT;
261
262 FUNCTION COMPUTE_ACCT_BILLED_AMT(p_cc_acct_line_id NUMBER)
263 RETURN NUMBER
264 IS
265 l_billed_amount NUMBER := 0;
266 l_cc_num igc_cc_headers.cc_num%TYPE;
267 l_cc_type igc_cc_headers.cc_type%TYPE;
268 l_org_id igc_cc_headers.org_id%TYPE;
269 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
270 l_cc_acct_line_num igc_cc_acct_lines.cc_acct_line_num%TYPE;
271 l_sob_id igc_cc_headers.set_of_books_id%TYPE;
272 l_currency_code igc_cc_headers.currency_code%TYPE;
273 l_func_currency_code igc_cc_headers.currency_code%TYPE;
274 BEGIN
275
276 SELECT cc_acct_line_num, cc_header_id
277 INTO l_cc_acct_line_num, l_cc_header_id
278 FROM igc_cc_acct_lines
279 WHERE cc_acct_line_id = p_cc_acct_line_id;
280
281 SELECT cc_num, org_id, cc_type, set_of_books_id, currency_code
282 INTO l_cc_num, l_org_id, l_cc_type, l_sob_id, l_currency_code
283 FROM igc_cc_headers
284 WHERE cc_header_id = l_cc_header_id;
285
286 SELECT currency_code
287 INTO l_func_currency_code
288 FROM gl_sets_of_books
289 WHERE set_of_books_id = l_sob_id;
290
291
292 IF ( (l_cc_type = 'S') OR (l_cc_type = 'R') )
293 THEN
294 BEGIN
295
296 SELECT NVL(SUM(NVL(pod.amount_billed,0)),0)
297 INTO l_billed_amount
298 FROM
299 po_headers_all poh,
300 po_lines_all pol,
301 po_line_locations_all pll,
302 po_distributions_all pod
303 WHERE
304 poh.segment1 = l_cc_num AND
305 poh.type_lookup_code = 'STANDARD' AND
306 poh.org_id = l_org_id AND
307 pol.po_header_id = poh.po_header_id AND
308 pol.line_num = l_cc_acct_line_num AND
309 pll.po_line_id = pol.po_line_id AND
310 pll.po_header_id = pol.po_header_id AND
311 pod.po_header_id = pll.po_header_id AND
312 pod.po_line_id = pll.po_line_id AND
313 pod.line_location_id = pll.line_location_id ;
314 EXCEPTION
315 WHEN NO_DATA_FOUND
316 THEN
317 l_billed_amount := 0;
318 END;
319
320 ELSIF (l_cc_type = 'C')
321 THEN
322
323 -- Bug # 1459569, 1520481.
324
325 l_billed_amount := 0;
326
327 IF (l_currency_code <> l_func_currency_code)
328 THEN
329 BEGIN
330 -- Performance Tuning, replaced the following
331 -- sql with a direct select from the table
332 /*
333 SELECT NVL(SUM(NVL(cc_acct_billed_amt,0)),0)
334 INTO l_billed_amount
335 FROM igc_cc_acct_lines_v
336 WHERE parent_acct_line_id = p_cc_acct_line_id;
337 */
338 SELECT NVL(SUM(NVL(IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id),0)),0)
339 INTO l_billed_amount
340 FROM igc_cc_acct_lines ccal
341 WHERE ccal.parent_acct_line_id = p_cc_acct_line_id;
342 EXCEPTION
343 WHEN NO_DATA_FOUND
344 THEN
345 l_billed_amount := 0;
346 END;
347 END IF;
348
349 IF (l_currency_code = l_func_currency_code)
350 THEN
351 BEGIN
352 -- Performance Tuning. Replaced the following
353 -- sql with the one below.
354 /*
355 SELECT NVL(SUM(NVL(cc_acct_func_billed_amt,0)),0)
356 INTO l_billed_amount
357 FROM igc_cc_acct_lines_v
358 WHERE parent_acct_line_id = p_cc_acct_line_id;
359 */
360 SELECT NVL(SUM(NVL(IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT_CURR( ccal.cc_acct_line_id),0)),0)
361 INTO l_billed_amount
362 FROM igc_cc_acct_lines ccal
363 WHERE ccal.parent_acct_line_id = p_cc_acct_line_id;
364 EXCEPTION
365 WHEN NO_DATA_FOUND
366 THEN
367 l_billed_amount := 0;
368 END;
369 END IF;
370
371 END IF;
372
373 RETURN(l_billed_amount);
374
375 END COMPUTE_ACCT_BILLED_AMT;
376
377 FUNCTION COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id NUMBER, p_cc_func_amt NUMBER)
378 RETURN NUMBER
379 IS
380 l_cc_conversion_rate igc_cc_headers.conversion_rate%TYPE;
381 l_cc_func_amt igc_cc_acct_lines.cc_acct_func_amt%TYPE;
382 BEGIN
383 BEGIN
384 SELECT cc.conversion_rate
385 INTO l_cc_conversion_rate
386 FROM igc_cc_headers cc
387 WHERE cc.cc_header_id = p_cc_header_id;
388 EXCEPTION
389 WHEN NO_DATA_FOUND THEN
390 l_cc_conversion_rate := 1;
391 END;
392
393 l_cc_func_amt := NVL(p_cc_func_amt,0) * NVL(l_cc_conversion_rate,1);
394
395 RETURN(l_cc_func_amt);
396
397 END COMPUTE_FUNCTIONAL_AMT;
398
399 FUNCTION COMPUTE_PF_FUNC_BILLED_AMT(p_cc_det_pf_line_id NUMBER,
400 p_cc_det_pf_line_num NUMBER,
401 p_cc_acct_line_id NUMBER)
402 RETURN NUMBER
403 IS
404 l_func_billed_amount NUMBER := 0;
405 l_cc_num igc_cc_headers.cc_num%TYPE;
406 l_cc_type igc_cc_headers.cc_type%TYPE;
407 l_org_id igc_cc_headers.org_id%TYPE;
408 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
409 l_cc_acct_line_num igc_cc_acct_lines.cc_acct_line_num%TYPE;
410 BEGIN
411
412 SELECT cc_acct_line_num, cc_header_id
413 INTO l_cc_acct_line_num, l_cc_header_id
414 FROM igc_cc_acct_lines
415 WHERE cc_acct_line_id = p_cc_acct_line_id;
416
417 SELECT cc_num, org_id, cc_type
418 INTO l_cc_num, l_org_id, l_cc_type
419 FROM igc_cc_headers
420 WHERE cc_header_id = l_cc_header_id;
421
422 IF ( (l_cc_type = 'S') OR (l_cc_type = 'R') )
423 THEN
424
425 BEGIN
426
427 SELECT NVL(SUM(DECODE(apid.base_amount,NULL,apid.amount,apid.base_amount)),0)
428 INTO l_func_billed_amount
429 FROM
430 ap_invoice_distributions_all apid,
431 po_distributions_all pod,
432 po_line_locations_all pll,
433 po_lines_all pol,
434 po_headers_all poh
435 WHERE
436 apid.po_distribution_id = pod.po_distribution_id AND
437 poh.segment1 = l_cc_num AND
438 poh.type_lookup_code = 'STANDARD' AND
439 poh.org_id = l_org_id AND
440 pol.po_header_id = poh.po_header_id AND
441 pol.line_num = l_cc_acct_line_num AND
442 pll.po_line_id = pol.po_line_id AND
443 pll.po_header_id = pol.po_header_id AND
444 pod.po_header_id = pll.po_header_id AND
445 pod.po_line_id = pll.po_line_id AND
446 pod.line_location_id = pll.line_location_id AND
447 pod.distribution_num = p_cc_det_pf_line_num;
448 EXCEPTION
449 WHEN NO_DATA_FOUND
450 THEN
451 l_func_billed_amount := 0;
452 END;
453 ELSIF (l_cc_type = 'C')
454 THEN
455 BEGIN
456 SELECT NVL(SUM(NVL(cc_det_pf_func_billed_amt,0)),0)
457 INTO l_func_billed_amount
458 FROM igc_cc_det_pf_v
459 WHERE parent_det_pf_line_id = p_cc_det_pf_line_id;
460 EXCEPTION
461 WHEN NO_DATA_FOUND
462 THEN
463 l_func_billed_amount := 0;
464 END;
465 END IF;
466
467 RETURN(l_func_billed_amount);
468
469 END COMPUTE_PF_FUNC_BILLED_AMT;
470
471 FUNCTION COMPUTE_ACCT_FUNC_BILLED_AMT(p_cc_acct_line_id NUMBER)
472 RETURN NUMBER
473 IS
474 l_func_billed_amount NUMBER := 0;
475 l_cc_num igc_cc_headers.cc_num%TYPE;
476 l_cc_type igc_cc_headers.cc_type%TYPE;
477 l_org_id igc_cc_headers.org_id%TYPE;
478 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
479 l_cc_acct_line_num igc_cc_acct_lines.cc_acct_line_num%TYPE;
480 BEGIN
481
482 SELECT cc_acct_line_num, cc_header_id
483 INTO l_cc_acct_line_num, l_cc_header_id
484 FROM igc_cc_acct_lines
485 WHERE cc_acct_line_id = p_cc_acct_line_id;
486
487 SELECT cc_num, org_id, cc_type
488 INTO l_cc_num, l_org_id, l_cc_type
489 FROM igc_cc_headers
490 WHERE cc_header_id = l_cc_header_id;
491
492 IF ( (l_cc_type = 'S') OR (l_cc_type = 'R') )
493 THEN
494 BEGIN
495
496 SELECT NVL(SUM(DECODE(apid.base_amount,NULL,apid.amount,apid.base_amount)),0)
497 INTO l_func_billed_amount
498 FROM
499 ap_invoice_distributions_all apid,
500 po_distributions_all pod,
501 po_line_locations_all pll,
502 po_lines_all pol,
503 po_headers_all poh
504 WHERE
505 apid.po_distribution_id = pod.po_distribution_id AND
506 poh.segment1 = l_cc_num AND
507 poh.type_lookup_code = 'STANDARD' AND
508 poh.org_id = l_org_id AND
509 pol.po_header_id = poh.po_header_id AND
510 pol.line_num = l_cc_acct_line_num AND
511 pll.po_line_id = pol.po_line_id AND
512 pll.po_header_id = pol.po_header_id AND
513 pod.po_header_id = pll.po_header_id AND
514 pod.po_line_id = pll.po_line_id AND
515 pod.line_location_id = pll.line_location_id ;
516 EXCEPTION
517 WHEN NO_DATA_FOUND
518 THEN
519 l_func_billed_amount := 0;
520 END;
521
522 ELSIF (l_cc_type = 'C')
523 THEN
524 BEGIN
525 -- Performance fixes. Replaced the following sql
526 -- with the one below.
527 /*
528 SELECT NVL(SUM(NVL(cc_acct_func_billed_amt,0)),0)
529 INTO l_func_billed_amount
530 FROM igc_cc_acct_lines
531 WHERE parent_acct_line_id = p_cc_acct_line_id;
532 */
533 SELECT NVL(SUM(NVL(IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id),0)),0)
534 INTO l_func_billed_amount
535 FROM igc_cc_acct_lines ccal
536 WHERE ccal.parent_acct_line_id = p_cc_acct_line_id;
537 EXCEPTION
538 WHEN NO_DATA_FOUND
539 THEN
540 l_func_billed_amount := 0;
541 END;
542 END IF;
543
544 RETURN(l_func_billed_amount);
545
546 END COMPUTE_ACCT_FUNC_BILLED_AMT;
547
548
549
550 END IGC_CC_COMP_AMT_PKG;