1 PACKAGE BODY po_partial_funding_pkg AS
2 /* $Header: PO_PARTIAL_FUNDING_PKG.plb 120.12.12020000.3 2013/04/08 09:13:46 dtoshniw ship $ */
3 d_pkg_name CONSTANT VARCHAR2(50) := po_log.Get_package_base('PO_PARTIAL_FUNDING_PKG');
4
5 /*=======================================================================+
6 | FILENAME
7 | PO_PARTIAL_FUNDING_PKG.plB
8 |
9 | DESCRIPTION
10 | PL/SQL body for package: PO_PARTIAL_FUNDING_PKG
11 |
12 | NOTES
13 | MODIFIED
14 *=====================================================================*/
15 FUNCTION Is_clm_document
16 (p_doc_type IN VARCHAR2,
17 p_doc_level_id IN NUMBER)
18 RETURN VARCHAR2
19 IS
20 d_api_name CONSTANT VARCHAR2(30) := 'Is_CLM_Document';
21 d_module CONSTANT VARCHAR2(100) := d_pkg_name
22 ||d_api_name;
23 d_progress NUMBER;
24 l_clm_document VARCHAR2(1) := 'N'; -- Y if its a CLM Document and N if its a Non-CLM Document
25
26 BEGIN
27 d_progress := 10;
28
29 IF (po_log.d_proc) THEN
30 po_log.Proc_begin(d_module);
31
32 po_log.Proc_begin(d_module,'p_Doc_type',p_doc_type);
33
34 po_log.Proc_begin(d_module,'p_Doc_Level_Id',p_doc_level_id);
35 END IF;
36
37 d_progress := 20;
38
39 IF p_doc_type = 'REQ' THEN
40 SELECT 'Y'
41 INTO l_clm_document
42 FROM po_requisition_headers_all
43 WHERE requisition_header_id = p_doc_level_id
44 AND Nvl(federal_flag,'N') = 'Y';
45 ELSE
46 SELECT 'Y'
47 INTO l_clm_document
48 FROM po_headers_all h,
49 po_doc_style_headers pdsh
50 WHERE h.po_header_id = p_doc_level_id
51 AND h.style_id = pdsh.style_id
52 AND Nvl(pdsh.clm_flag,'N') = 'Y';
53 END IF;
54
55 d_progress := 30;
56
57 IF po_log.d_stmt THEN
58 po_log.Stmt(d_module,d_progress,'l_Clm_Document: '
59 ||l_clm_document);
60 END IF;
61
62 RETURN l_clm_document;
63 EXCEPTION
64 WHEN no_data_found THEN
65 l_clm_document := 'N';
66
67 RETURN l_clm_document;
68 WHEN OTHERS THEN
69 IF po_log.d_stmt THEN
70 po_log.Stmt(d_module,d_progress,'Error is : ',sqlerrm);
71 END IF;
72 END;
73
74 FUNCTION Is_clm_enabled
75 RETURN VARCHAR2
76 IS
77 d_api_name CONSTANT VARCHAR2(30) := 'is_CLM_Enabled';
78 d_module CONSTANT VARCHAR2(100) := d_pkg_name
79 ||d_api_name;
80 d_progress NUMBER;
81 l_clm_enabled VARCHAR2(1) := 'N'; -- Y if its a CLM Installed and N if its a Non-CLM installed
82
83 BEGIN
84
85 l_clm_enabled := NVL(FND_PROFILE.VALUE('PO_CLM_INSTALLED'),'N');
86
87 RETURN l_clm_enabled;
88 EXCEPTION
89 WHEN OTHERS THEN
90 IF po_log.d_stmt THEN
91 po_log.Stmt(d_module,d_progress,'Error is : ',sqlerrm);
92 END IF;
93
94 RETURN l_clm_enabled;
95 END;
96
97 -- <<ACRN proj start>>
98 -- Added p_org_id as part of CLM Controls Project changes
99 FUNCTION Is_ACRN_enabled
100 (
101 p_org_id NUMBER
102 )
103 RETURN VARCHAR2
104 IS
105 d_api_name CONSTANT VARCHAR2(30) := 'is_ACRN_enabled';
106 d_module CONSTANT VARCHAR2(100) := d_pkg_name
107 ||d_api_name;
108 d_progress NUMBER;
109 l_CLM VARCHAR2(1) := 'N';
110 l_ACRN VARCHAR2(1) := 'N';
111 l_ACRN_enabled VARCHAR2(1) := 'N'; -- Y if its a ACRN enabled
112 BEGIN
113 --ACRN Refactoring Changes Begin
114 l_CLM := Is_clm_enabled;
115 --CLM Controls Project changes
116 l_ACRN := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
117 p_option_column => PO_CORE_S.g_ACRN_COL);
118 --NVL(FND_PROFILE.VALUE('PO_CLM_ENABLE_ACRN'),'N');
119 IF l_CLM <> 'N' AND l_ACRN <> 'N' THEN
120 l_ACRN_enabled := 'Y';
121 END IF;
122 --ACRN Refactoring Changes end.
123 RETURN l_ACRN_enabled;
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 IF po_log.d_stmt THEN
128 po_log.Stmt(d_module,d_progress,'Error is : ',sqlerrm);
129 END IF;
130
131 END;
132
133 -- <<ACRN proj end>>
134
135
136 /*
137 This Procedure should be able to recalculate the Partial Funding attributes.
138 */
139 PROCEDURE recalculate_pf_attributes
140 (p_doc_header_id IN NUMBER)
141 IS
142 d_api_name CONSTANT VARCHAR2(30) := 'recalculate_pf_attributes';
143 d_module CONSTANT VARCHAR2(100) := d_pkg_name
144 ||d_api_name;
145 d_progress NUMBER;
146 l_return_status VARCHAR2(30);
147 l_is_clm_enabled VARCHAR2(1);
148 l_style_id NUMBER;
149 l_po_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
150 BEGIN
151
152 d_progress := 10;
153
154 IF (po_log.d_proc) THEN
155 po_log.Proc_begin(d_module);
156 po_log.Proc_begin(d_module,'p_doc_header_id',p_doc_header_id);
157 END IF;
158
159 SELECT NVL(style_id,-1)
160 INTO l_style_id
161 FROM po_headers_all
162 WHERE po_header_id = p_doc_header_id;
163
164 l_is_clm_enabled := po_doc_style_helper.get_clm_flag(p_style_id => l_style_id);
165
166 SELECT NVL(purch_encumbrance_flag,'N')
167 INTO l_po_enc_flag
168 FROM financials_system_params_all fsp,
169 po_headers_all poh
170 WHERE poh.po_header_id = p_doc_header_id
171 AND fsp.org_id = poh.org_id;
172
173 IF (l_po_enc_flag = 'Y' AND l_is_clm_enabled = 'Y') THEN
174
175 Updt_funding_info(p_document_type => 'STANDARD',
176 p_doc_header_id => p_doc_header_id,
177 x_return_status => l_return_status);
178
179 END IF;
180
181 IF (po_log.d_proc) THEN
182 po_log.Proc_end(d_module,'p_doc_header_id',p_doc_header_id);
183 END IF;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 IF (po_log.d_exc) THEN
188 po_log.Exc(d_module,d_progress,SQLCODE
189 ||sqlerrm);
190 END IF;
191 END recalculate_pf_attributes;
192
193
194
195 /* <> */
196 /*
197 This function should be able to calculate and update Qty Funded and amt funded to
198 */
199 PROCEDURE Updt_funding_info
200 (x_return_status OUT NOCOPY VARCHAR2,
201 p_document_type IN VARCHAR2,
202 p_doc_header_id IN NUMBER,
203 p_draft_id IN NUMBER DEFAULT -1)
204 IS
205 d_api_name CONSTANT VARCHAR2(30) := 'Updt_Funding_Info';
206 d_module CONSTANT VARCHAR2(100) := d_pkg_name
207 ||d_api_name;
208 d_progress NUMBER;
209 l_dist_id_tbl PO_TBL_NUMBER;
210 l_price_tbl PO_TBL_NUMBER;
211 l_match_basis_tbl PO_TBL_VARCHAR20;
212 l_qty_ordered_tbl PO_TBL_NUMBER;
213 l_amt_ordered_tbl PO_TBL_NUMBER;
214 l_partial_flag_tbl PO_TBL_VARCHAR1;
215 l_fund_value_tbl PO_TBL_NUMBER;
216 l_nr_tax_tbl PO_TBL_NUMBER;
217 l_rate_tbl PO_TBL_NUMBER;
218 l_qty_funded_tbl PO_TBL_NUMBER;
219 l_amt_funded_tbl PO_TBL_NUMBER;
220 -- Bug 16475592: Changing data type of l_func_currency_tbl from table of number
221 -- to table of varchar as the data is inserted from GL_SETS_OF_BOOKS, currency
222 -- code column which has varchar2 data type.
223 l_func_currency_tbl PO_TBL_VARCHAR20;
224
225 x_precision NUMBER := NULL;
226 x_ext_precision NUMBER := NULL;
227 x_min_acct_unit NUMBER := NULL;
228
229 BEGIN
230 d_progress := 10;
231 IF Is_clm_document(p_document_type,p_doc_header_id) <> 'Y' THEN
232 RETURN;
233 END IF;
234
235 --13943396 Added currency_code to extract the precision value
236 IF p_document_type = 'REQUISITION' THEN
237 d_progress := 20;
238 SELECT d.distribution_id,
239 Nvl(l.unit_price,0),
240 Nvl(l.matching_basis,'QUANTITY'),
241 d.req_line_quantity,
242 d.req_line_amount,
243 Nvl(d.partial_funded_flag,'N'),
244 Nvl(d.funded_value,0),
245 Nvl(d.nonrecoverable_tax,0),
246 1,
247 d.quantity_funded,
248 d.amount_funded,
249 GLSOB.CURRENCY_CODE
250 BULK COLLECT INTO l_dist_id_tbl,l_price_tbl,l_match_basis_tbl,l_qty_ordered_tbl,
251 l_amt_ordered_tbl,l_partial_flag_tbl,l_fund_value_tbl,l_nr_tax_tbl,
252 l_rate_tbl,l_qty_funded_tbl,l_amt_funded_tbl,l_func_currency_tbl
253 FROM po_req_distributions_all d,
254 po_requisition_lines_all l,
255 GL_SETS_OF_BOOKS GLSOB
256 WHERE l.requisition_header_id = p_doc_header_id
257 AND l.requisition_line_id = d.requisition_line_id
258 AND Nvl(l.cancel_flag,'N') <> 'Y'
259 AND Nvl(l.closed_code,'OPEN') <> 'FINALLY CLOSED'
260 AND d.prevent_encumbrance_flag <> 'Y'
261 AND d.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID;
262 ELSE
263 d_progress := 30;
264 SELECT d.po_distribution_id,
265 Nvl(s.price_override,0),
266 Nvl(s.matching_basis,'QUANTITY'),
267 d.quantity_ordered,
268 d.amount_ordered,
269 Nvl(d.partial_funded_flag,'N'),
270 Nvl(d.funded_value,0),
271 Nvl(d.nonrecoverable_tax,0),
272 Nvl(d.rate,1),
273 d.quantity_funded,
274 d.amount_funded,
275 GLSOB.CURRENCY_CODE
276 BULK COLLECT INTO l_dist_id_tbl,l_price_tbl,l_match_basis_tbl,l_qty_ordered_tbl,
277 l_amt_ordered_tbl,l_partial_flag_tbl,l_fund_value_tbl,l_nr_tax_tbl,
278 l_rate_tbl,l_qty_funded_tbl,l_amt_funded_tbl,l_func_currency_tbl
279 FROM po_distributions_merge_v d,
280 po_line_locations_merge_v s,
281 GL_SETS_OF_BOOKS GLSOB
282 WHERE d.po_header_id = p_doc_header_id
283 AND d.draft_id = p_draft_id
284 AND s.draft_id = p_draft_id
285 AND s.line_location_id = d.line_location_id
286 AND Nvl(s.cancel_flag,'N') <> 'Y'
287 AND Nvl(s.closed_code,'OPEN') <> 'FINALLY CLOSED'
288 AND d.prevent_encumbrance_flag <> 'Y'
289 AND d.SET_OF_BOOKS_ID = GLSOB.SET_OF_BOOKS_ID;
290 END IF;
291 d_progress := 40;
292 IF l_dist_id_tbl.COUNT = 0 THEN
293 RETURN;
294 END IF;
295
296 d_progress := 50;
297 FOR i IN 1.. l_dist_id_tbl.COUNT LOOP
298 --13943396 Extract the precision value to round the Funded value
299 IF x_precision IS NULL THEN
300
301 fnd_currency.get_info(l_func_currency_tbl(i),
302 x_precision,
303 x_ext_precision,
304 x_min_acct_unit );
305
306 END IF;
307 IF L_partial_flag_tbl(i) = 'Y' THEN
308 IF L_match_basis_tbl(i) = 'AMOUNT' THEN
309 /* CLM Partial Funding Changes -- <<Bug#9898165>> */
310 IF (L_amt_funded_tbl(i) IS NOT NULL AND L_amt_funded_tbl(i) > L_amt_ordered_tbl(i)) THEN
311 L_fund_value_tbl(i) := Round(((L_amt_ordered_tbl(i) + L_nr_tax_tbl(i))*(L_rate_tbl(i))),x_precision);
312 END IF;
313 L_amt_funded_tbl(i) := Round((L_fund_value_tbl(i) * L_amt_ordered_tbl(i) / (L_rate_tbl(i) * (L_amt_ordered_tbl(i) + L_nr_tax_tbl(i)))),x_precision);
314 ELSE
315 /* CLM Partial Funding Changes -- <<Bug#9898165>> */
316 IF (L_qty_funded_tbl(i) IS NOT NULL AND L_qty_funded_tbl(i) > L_qty_ordered_tbl(i)) THEN
317 L_fund_value_tbl(i) := Round((((L_qty_ordered_tbl(i)*L_price_tbl(i)) + L_nr_tax_tbl(i))*(L_rate_tbl(i))),x_precision);
318 END IF;
319 L_qty_funded_tbl(i) := Round(((L_fund_value_tbl(i) * L_qty_ordered_tbl(i)) / (L_rate_tbl(i) * (L_price_tbl(i) * L_qty_ordered_tbl(i) + L_nr_tax_tbl(i)))),Nvl(FND_PROFILE.VALUE('PO_QUANTITY_PRECISION'),5));
320 END IF;
321 ELSE
322 L_qty_funded_tbl(i) := L_qty_ordered_tbl(i);
323 L_amt_funded_tbl(i) := L_amt_ordered_tbl(i);
324 IF L_match_basis_tbl(i) = 'AMOUNT' THEN
325 L_fund_value_tbl(i) := Round(((L_amt_ordered_tbl(i) + L_nr_tax_tbl(i))*(L_rate_tbl(i))),x_precision);
326 ELSE
327 L_fund_value_tbl(i) := Round((((L_qty_ordered_tbl(i)*L_price_tbl(i)) + L_nr_tax_tbl(i))*(L_rate_tbl(i))),x_precision);
328 END IF;
329 END IF;
330 END LOOP;
331
332 IF p_document_type = 'REQUISITION' THEN
333 d_progress := 70;
334 FORALL i IN 1..l_dist_id_tbl.COUNT
335 UPDATE po_req_distributions_all d
336 SET d.funded_value = L_fund_value_tbl(i),
337 d.quantity_funded = L_qty_funded_tbl(i),
338 d.amount_funded = L_amt_funded_tbl(i)
339 WHERE d.distribution_id = L_dist_id_tbl(i);
340 ELSE
341 IF p_draft_id <> -1 THEN
342 d_progress := 70;
343 FORALL i IN 1..l_dist_id_tbl.COUNT
344 UPDATE po_distributions_draft_all d
345 SET d.funded_value = L_fund_value_tbl(i),
346 d.quantity_funded = L_qty_funded_tbl(i),
347 d.amount_funded = L_amt_funded_tbl(i)
348 WHERE d.po_distribution_id = L_dist_id_tbl(i)
349 AND d.draft_id = p_draft_id;
350 ELSE
351 d_progress := 80;
352 FORALL i IN 1..l_dist_id_tbl.COUNT
353 UPDATE po_distributions_all d
354 SET d.funded_value = L_fund_value_tbl(i),
355 d.quantity_funded = L_qty_funded_tbl(i),
356 d.amount_funded = L_amt_funded_tbl(i),
357 d.change_in_funded_value = L_fund_value_tbl(i) -- <<Bug#9862575>>
358 WHERE d.po_distribution_id = L_dist_id_tbl(i)
359 AND Nvl(d.encumbered_flag,'N') <> 'Y'; -- <<Bug#10206681>>
360 /* Bug#10206681: The change_in_funded_value of the Award is getting updated
361 as part of recalculation of PF attributes of Modification,
362 immediately after tax calculation. This need to be prevented,
363 using the constraint Nvl(d.encumbered_flag,'N') <> 'Y'. */
364 END IF;
365 END IF;
366 d_progress := 90;
367 -- By default return status is SUCCESS if no exception occurs
368 x_return_status := fnd_api.g_ret_sts_success;
369 EXCEPTION
370 WHEN OTHERS THEN
371 x_return_status := fnd_api.g_ret_sts_unexp_error;
372
373 IF (po_log.d_exc) THEN
374 po_log.Exc(d_module,d_progress,SQLCODE
375 ||sqlerrm);
376
377 po_log.Proc_end(d_module,'x_return_status',x_return_status);
378 END IF;
379 END updt_funding_info;
380
381 /*
382 This package should be called after any change in the Funded Value on the Distribution, per distribution
383 */
384 PROCEDURE Get_change_in_funds
385 (x_return_status OUT NOCOPY VARCHAR2,
386 p_document_type IN VARCHAR2,
387 p_distribution_id IN NUMBER,
388 p_draft_id IN NUMBER DEFAULT -1,
389 p_conf_dist_id IN NUMBER DEFAULT NULL,
390 p_funded_value IN NUMBER,
391 x_chng_in_funded_value OUT NOCOPY NUMBER)
392 IS
393 d_api_name CONSTANT VARCHAR2(30) := 'Get_change_in_funds';
394 d_module CONSTANT VARCHAR2(100) := d_pkg_name
395 ||d_api_name;
396 d_progress NUMBER;
397 BEGIN
398 d_progress := 10;
399 IF p_document_type = 'REQUISITION'
400 AND p_conf_dist_id IS NOT NULL THEN
401 d_progress := 20;
402 SELECT Nvl(d.funded_value,0) - p_funded_value
403 INTO x_chng_in_funded_value
404 FROM po_req_distributions_all d
405 WHERE d.distribution_id = p_conf_dist_id;
406 ELSIF p_document_type = 'PO'
407 AND p_draft_id <> -1 THEN
408 d_progress := 30;
409 SELECT Nvl(d.funded_value,0) - p_funded_value
410 INTO x_chng_in_funded_value
411 FROM po_distributions_all d
412 WHERE d.po_distribution_id = p_distribution_id;
413 ELSE
414 d_progress := 40;
415 x_chng_in_funded_value := p_funded_value;
416 END IF;
417 d_progress := 50;
418 -- By default return status is SUCCESS if no exception occurs
419 x_return_status := fnd_api.g_ret_sts_success;
420 EXCEPTION
421 WHEN OTHERS THEN
422 x_return_status := fnd_api.g_ret_sts_unexp_error;
423
424 IF (po_log.d_exc) THEN
425 po_log.Exc(d_module,d_progress,SQLCODE
426 ||sqlerrm);
427
428 po_log.Proc_end(d_module,'x_return_status',x_return_status);
429 END IF;
430 END get_change_in_funds;
431
432 PROCEDURE Get_qty_amt_funded
433 (x_return_status OUT NOCOPY VARCHAR2,
434 p_document_type IN VARCHAR2,
435 p_partial_flag IN VARCHAR2,
436 p_fund_value IN NUMBER,
437 p_qty_ordered IN NUMBER,
438 p_amt_ordered IN NUMBER,
439 p_matchin_basis IN VARCHAR2,
440 p_price IN NUMBER DEFAULT 0,
441 p_rate IN NUMBER DEFAULT 1,
442 p_nr_tax IN NUMBER DEFAULT 0,
443 x_qty_funded OUT NOCOPY NUMBER,
444 x_amt_funded OUT NOCOPY NUMBER)
445 IS
446 d_api_name CONSTANT VARCHAR2(30) := 'Get_Qty_Amt_Funded';
447 d_module CONSTANT VARCHAR2(100) := d_pkg_name
448 ||d_api_name;
449 d_progress NUMBER;
450 BEGIN
451 d_progress := 10;
452 IF p_partial_flag = 'Y' THEN
453 IF p_matchin_basis = 'AMOUNT' THEN
454 d_progress := 20;
455 x_amt_funded := Round((p_fund_value * p_amt_ordered / p_rate * (p_amt_ordered + p_nr_tax)),15);
456 ELSE
457 d_progress := 30;
458 x_qty_funded := Round(((p_fund_value * p_qty_ordered) / p_rate * (p_price * p_qty_ordered + p_nr_tax)),15);
459 END IF;
460 ELSE
461 d_progress := 40;
462 x_qty_funded := p_qty_ordered;
463 x_amt_funded := p_amt_ordered;
464 END IF;
465 d_progress := 50;
466 -- By default return status is SUCCESS if no exception occurs
467 x_return_status := fnd_api.g_ret_sts_success;
468 EXCEPTION
469 WHEN OTHERS THEN
470 x_return_status := fnd_api.g_ret_sts_unexp_error;
471
472 IF (po_log.d_exc) THEN
473 po_log.Exc(d_module,d_progress,SQLCODE
474 ||sqlerrm);
475
476 po_log.Proc_end(d_module,'x_return_status',x_return_status);
477 END IF;
478 END get_qty_amt_funded;
479
480 PROCEDURE Updt_Funds_Split(x_return_status OUT NOCOPY VARCHAR2, p_new_Req_line_id in number)
481 IS
482 cursor Parent_Req_Info(p_distribution_id in number)
483 is
484 select
485 funded_value,
486 Req_Line_Quantity
487 from po_Req_Distributions_all
488 Where Distribution_Id = p_Distribution_Id;
489
490
491 cursor New_Req_line_Info is
492 SELECT
493 distribution_id,
494 partial_funded_flag,
495 req_line_quantity,
496 prl.Unit_Price,
497 Nvl(prd.nonrecoverable_tax,0),
498 source_req_distribution_id
499 FROM po_requisition_lines_all prl, po_req_distributions_all prd
500 WHERE prl.Requisition_Line_Id =p_new_Req_line_id
501 and prd.Requisition_Line_Id = prl.Requisition_Line_Id;
502
503
504
505 d_api_name CONSTANT VARCHAR2(30) := 'Updt_Funds_Split';
506 d_module CONSTANT VARCHAR2(100) := d_pkg_name
507 ||d_api_name;
508 d_progress NUMBER;
509 l_price NUMBER;
510
511 l_Distribution_id_tbl PO_TBL_NUMBER;
512 l_partial_flag_tbl PO_TBL_VARCHAR1;
513 l_req_Line_qty_tbl PO_TBL_NUMBER;
514 l_price_tbl PO_TBL_NUMBER;
515 l_nr_tax_tbl PO_TBL_NUMBER;
516
517 l_qty_funded_tbl PO_TBL_NUMBER;
518 l_old_fund_value number;
519 l_Old_Req_Line_Qty NUMBER;
520 l_funded_Value_tbl PO_TBL_NUMBER;
521 l_Chg_Fund_Value_Tbl PO_TBL_NUMBER;
522 l_parent_Dist_Id_tbl PO_Tbl_Number;
523
524 BEGIN
525 D_PROGRESS := 10;
526 --Clm_Split
527 l_qty_funded_tbl := PO_TBL_NUMBER();
528 L_FUNDED_VALUE_TBL := PO_TBL_NUMBER();
529 L_CHG_FUND_VALUE_TBL := PO_TBL_NUMBER();
530 --End
531 open New_Req_line_Info;
532 Fetch New_Req_line_Info
533 BULK COLLECT INTO
534 l_Distribution_id_tbl,
535 l_partial_flag_tbl,
536 l_req_Line_qty_tbl,
537 l_price_tbl,
538 l_nr_tax_tbl,
539 l_parent_Dist_Id_tbl;
540
541 For i in 1..l_Distribution_id_tbl.count
542 LOOP
543 --Clm_Split
544 L_QTY_FUNDED_TBL.EXTEND;
545 L_FUNDED_VALUE_TBL.EXTEND;
546 L_CHG_FUND_VALUE_TBL.EXTEND;
547 --End
548 If l_partial_flag_tbl(i) = 'N' Then
549 l_funded_Value_tbl(i) := l_req_Line_qty_tbl(i) * l_price_tbl(i) + l_nr_tax_tbl(i);
550 l_Qty_Funded_Tbl(i) := l_req_Line_qty_tbl(i);
551 Else
552 Open Parent_Req_Info(l_parent_Dist_Id_tbl(i));
553 Fetch Parent_Req_Info into l_Old_Fund_Value, l_Old_Req_Line_Qty;
554 close Parent_Req_Info;
555 l_funded_Value_tbl(i) := l_Old_Fund_Value * l_req_Line_qty_tbl(i)/l_Old_Req_Line_Qty;
556 l_Qty_Funded_Tbl(i) := (l_funded_Value_tbl(i) * l_req_Line_qty_tbl(i)) / (l_price_tbl(i) * l_req_Line_qty_tbl(i) +l_nr_tax_tbl(i));
557
558 End If;
559 l_Chg_Fund_Value_Tbl(i) := l_funded_Value_tbl(i);
560 End Loop;
561 forall i in 1..l_Distribution_id_tbl.count
562 Update po_Req_Distributions_all
563 Set funded_value = l_funded_Value_tbl(i),
564 Quantity_Funded = l_Qty_Funded_Tbl(i),
565 Change_In_Funded_Value = l_Chg_Fund_Value_Tbl(i)
566 Where Distribution_Id = l_Distribution_id_tbl(i);
567
568 END Updt_Funds_Split;
569
570 /*
571 Returns Total Funded Value for a given entity level id
572 + This function should be called only for clm document styles
573 + It returns the Funded Value for the lowest entity id passed to this function
574 + This returns the Funded Value in functional currency.
575 */
576 FUNCTION Get_funded_value
577 (l_doc_type IN VARCHAR2,
578 l_header_id IN NUMBER DEFAULT NULL,
579 l_line_id IN NUMBER DEFAULT NULL,
580 l_shipment_id IN NUMBER DEFAULT NULL,
581 l_distribution_id IN NUMBER DEFAULT NULL)
582 RETURN NUMBER
583 IS
584 l_funded_value NUMBER := 0;
585 d_api_name CONSTANT VARCHAR2(30) := 'get_funded_value';
586 d_module CONSTANT VARCHAR2(100) := d_pkg_name
587 ||d_api_name;
588 d_progress NUMBER;
589 BEGIN
590 d_progress := 10;
591
592 IF l_doc_type = 'REQ' THEN
593 d_progress := 15;
594
595 IF po_log.d_stmt THEN
596 po_log.Stmt(d_module,d_progress,'Document Type : '
597 ||l_doc_type);
598 END IF;
599
600 IF l_distribution_id IS NOT NULL THEN
601 d_progress := 20;
602
603 IF po_log.d_stmt THEN
604 po_log.Stmt(d_module,d_progress,'Distribution id is not null : '
605 ||l_distribution_id);
606 END IF;
607
608 SELECT Nvl(funded_value,0)
609 INTO l_funded_value
610 FROM po_req_distributions_all
611 WHERE distribution_id = l_distribution_id;
612 ELSIF l_line_id IS NOT NULL THEN
613 d_progress := 25;
614
615 IF po_log.d_stmt THEN
616 po_log.Stmt(d_module,d_progress,'Line id is not null : '
617 ||l_line_id);
618 END IF;
619
620 SELECT Sum(Nvl(funded_value,0))
621 INTO l_funded_value
622 FROM po_req_distributions_all d,
623 po_requisition_lines_all l
624 WHERE d.requisition_line_id = l.requisition_line_id
625 AND l.requisition_line_id = l_line_id;
626 ELSIF l_header_id IS NOT NULL THEN
627 d_progress := 30;
628
629 IF po_log.d_stmt THEN
630 po_log.Stmt(d_module,d_progress,'Header id is not null : '
631 ||l_header_id);
632 END IF;
633
634 SELECT Sum(Nvl(funded_value,0))
635 INTO l_funded_value
636 FROM po_req_distributions_all d,
637 po_requisition_lines_all l
638 WHERE d.requisition_line_id = l.requisition_line_id
639 AND l.requisition_header_id = l_header_id;
640 END IF;
641 ELSIF l_doc_type = 'PO' THEN
642 d_progress := 35;
643
644 IF po_log.d_stmt THEN
645 po_log.Stmt(d_module,d_progress,'Document Type is : '
646 ||l_doc_type);
647 END IF;
648
649 IF l_distribution_id IS NOT NULL THEN
650 d_progress := 40;
651
652 IF po_log.d_stmt THEN
653 po_log.Stmt(d_module,d_progress,'Distribution id is not null : '
654 ||l_distribution_id);
655 END IF;
656
657 SELECT Nvl(funded_value,0)
658 INTO l_funded_value
659 FROM po_distributions_all
660 WHERE po_distribution_id = l_distribution_id;
661 ELSIF l_shipment_id IS NOT NULL THEN
662 d_progress := 45;
663
664 IF po_log.d_stmt THEN
665 po_log.Stmt(d_module,d_progress,'Shipment id is not null : '
666 ||l_shipment_id);
667 END IF;
668
669 SELECT Sum(Nvl(funded_value,0))
670 INTO l_funded_value
671 FROM po_distributions_all d
672 WHERE d.line_location_id = l_shipment_id;
673 ELSIF l_line_id IS NOT NULL THEN
674 d_progress := 50;
675
676 IF po_log.d_stmt THEN
677 po_log.Stmt(d_module,d_progress,'Line id is not null : '
678 ||l_line_id);
679 END IF;
680
681 SELECT Sum(Nvl(funded_value,0))
682 INTO l_funded_value
683 FROM po_distributions_all d
684 WHERE d.po_line_id = l_line_id;
685 ELSIF l_header_id IS NOT NULL THEN
686 d_progress := 55;
687
688 IF po_log.d_stmt THEN
689 po_log.Stmt(d_module,d_progress,'Header id is not null : '
690 ||l_header_id);
691 END IF;
692
693 SELECT Sum(Nvl(funded_value,0))
694 INTO l_funded_value
695 FROM po_distributions_all d
696 WHERE d.po_header_id = l_header_id;
697 END IF;
698 END IF;
699
700 d_progress := 60;
701
702 IF po_log.d_stmt THEN
703 po_log.Stmt(d_module,d_progress,'Funded Value is : '
704 ||l_funded_value);
705 END IF;
706
707 RETURN l_funded_value;
708 EXCEPTION
709 WHEN OTHERS THEN
710 RETURN l_funded_value;
711
712 IF (po_log.d_exc) THEN
713 po_log.Exc(d_module,d_progress,SQLCODE
714 ||sqlerrm);
715 END IF;
716 END get_funded_value;
717
718 END po_partial_funding_pkg;
[Home] [Help]