[Home] [Help]
PACKAGE BODY: APPS.PO_AME_SETUP_PVT
Source
1 PACKAGE BODY PO_AME_SETUP_PVT AS
2 /* $Header: POXAMESB.pls 120.8.12020000.3 2013/02/10 23:53:43 vegajula ship $*/
3
4 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --------------------------------------------------------------------------------
7 --Start of Comments
8 --Name: get_function_currency
9 --Pre-reqs:
10 -- None.
11 --Modifies:
12 -- None.
13 --Locks:
14 -- None.
15 --Function:
16 -- Returns the function currency of requisition preparing org
17 --Parameters:
18 --IN:
19 --reqHeaderId
20 -- Requisition Header ID
21 --OUT:
22 --
23 --Testing:
24 --
25 --End of Comments
26 -------------------------------------------------------------------------------
27 FUNCTION get_function_currency(reqHeaderId IN NUMBER)
28 RETURN VARCHAR2 IS
29 l_currency_code gl_sets_of_books.currency_code%TYPE;
30
31 BEGIN
32 SELECT gls.currency_code
33 INTO l_currency_code
34 FROM financials_system_params_all fsp,
35 gl_sets_of_books gls,
36 po_requisition_headers_all prh
37 WHERE fsp.set_of_books_id = gls.set_of_books_id and
38 fsp.org_id = prh.org_id and -- <R12 MOAC>
39 prh.requisition_header_id = reqHeaderId;
40 RETURN l_currency_code;
41 EXCEPTION
42 when others then
43 raise;
44 END;
45
46 --------------------------------------------------------------------------------
47 --Start of Comments
48 --Name: get_rate_type
49 --Pre-reqs:
50 -- None.
51 --Modifies:
52 -- None.
53 --Locks:
54 -- None.
55 --Function:
56 -- Returns the default rate type of requisition preprarer.
57 --Parameters:
58 --IN:
59 --reqHeaderId
60 -- Requisition Header ID
61 --OUT:
62 --
63 --Testing:
64 --
65 --End of Comments
66 -------------------------------------------------------------------------------
67 FUNCTION get_rate_type(reqHeaderId IN NUMBER)
68 RETURN VARCHAR2 IS
69 l_user_id fnd_user.user_id%TYPE;
70 l_rate_type fnd_profile_option_values.profile_option_value%TYPE;
71
72 BEGIN
73 BEGIN
74 SELECT fu.user_id
75 INTO l_user_id
76 FROM fnd_user fu, po_requisition_headers_all prh
77 WHERE prh.requisition_header_id = reqHeaderId and
78 prh.preparer_id = fu.employee_id;
79 EXCEPTION
80 when others then
81 fnd_profile.get('POR_DEFAULT_RATE_TYPE', l_rate_type);
82 RETURN l_rate_type;
83 END;
84 l_rate_type := fnd_profile.value_specific('POR_DEFAULT_RATE_TYPE', l_user_id);
85 RETURN l_rate_type;
86 EXCEPTION
87 when others then
88 raise;
89 END;
90
91 --------------------------------------------------------------------------------
92 --Start of Comments
93 --Name: get_accounting_flex
94 --Pre-reqs:
95 -- None.
96 --Modifies:
97 -- None.
98 --Locks:
99 -- None.
100 --Function:
101 -- Returns the accounting flex segment's value for a distribution segment
102 --Parameters:
103 --IN:
104 --segmentName
105 -- Segment name
106 --distributionId
107 -- Requisition distribution ID
108 --OUT:
109 --Testing:
110 --
111 --End of Comments
112 -------------------------------------------------------------------------------
113 FUNCTION get_accounting_flex(segmentName IN VARCHAR2, distributionId IN NUMBER)
114 RETURN VARCHAR2 IS
115
116 l_segments FND_FLEX_EXT.SEGMENTARRAY;
117 l_result BOOLEAN;
118 l_chart_of_accounts_id NUMBER;
119 l_num_segments NUMBER;
120 l_segment_num NUMBER;
121 l_segment_delimiter VARCHAR2(1);
122 l_seg_val VARCHAR2(50);
123 l_ccid NUMBER;
124 l_sob NUMBER;
125
126
127 cursor c1(l_distribution_id in NUMBER) is SELECT code_combination_id, set_of_books_id
128 FROM po_req_distributions_all
129 WHERE distribution_id= l_distribution_id;
130
131 cursor c2(l_distribution_id in NUMBER) is SELECT code_combination_id, set_of_books_id
132 FROM po_distributions_merge_v
133 WHERE po_distribution_id= l_distribution_id;
134
135 BEGIN
136
137 /* find set of book id and code combination id from distribution */
138
139 open c1(distributionId);
140 fetch c1 into l_ccid, l_sob;
141 if c1%NOTFOUND then
142 open c2(distributionId);
143 fetch c2 into l_ccid, l_sob;
144 close c2;
145 end if;
146 close c1;
147
148 /* find chart of account id from set of book */
149 SELECT chart_of_accounts_id
150 INTO l_chart_of_accounts_id
151 FROM gl_sets_of_books
152 WHERE set_of_books_id = l_sob;
153
154 /* get the all the segment array */
155 l_result := FND_FLEX_EXT.GET_SEGMENTS(
156 'SQLGL',
157 'GL#',
158 l_chart_of_accounts_id,
159 l_ccid,
160 l_num_segments,
161 l_segments);
162
163 IF (NOT l_result) THEN
164 APP_EXCEPTION.RAISE_EXCEPTION;
165 END IF;
166
167 /* get the segment number for the given segment name */
168 l_result := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
169 101,
170 'GL#',
171 l_chart_of_accounts_id,
172 segmentName,
173 l_segment_num);
174
175 IF (NOT l_result) THEN
176 APP_EXCEPTION.RAISE_EXCEPTION;
177 END IF;
178
179 l_seg_val := l_segments(l_segment_num);
180
181 RETURN l_seg_val;
182 EXCEPTION
183 when others then
184 -- TODO: log error
185 raise;
186 END;
187
188 --------------------------------------------------------------------------------
189 --Start of Comments
190 --Name: get_accounting_flex_po
191 --Pre-reqs:
192 -- None.
193 --Modifies:
194 -- None.
195 --Locks:
196 -- None.
197 --Function:
198 -- Returns the accounting flex segment's value for a distribution segment
199 --Parameters:
200 --IN:
201 --segmentName
202 -- Segment name
203 --distributionId
204 -- Requisition distribution ID
205 --OUT:
206 --Testing:
207 --
208 --End of Comments
209 -------------------------------------------------------------------------------
210 FUNCTION get_accounting_flex_po(segmentName IN VARCHAR2, distributionId IN NUMBER, draftId IN NUMBER)
211 RETURN VARCHAR2 IS
212
213 l_segments FND_FLEX_EXT.SEGMENTARRAY;
214 l_result BOOLEAN;
215 l_chart_of_accounts_id NUMBER;
216 l_num_segments NUMBER;
217 l_segment_num NUMBER;
218 l_segment_delimiter VARCHAR2(1);
219 l_seg_val VARCHAR2(50);
220 l_ccid NUMBER;
221 l_sob NUMBER;
222
223
224 cursor c2(l_distribution_id in NUMBER, l_draft_id in NUMBER) is SELECT code_combination_id, set_of_books_id
225 FROM po_distributions_merge_v
226 WHERE po_distribution_id= l_distribution_id
227 AND draft_id = l_draft_id;
228
229 BEGIN
230
231 /* find set of book id and code combination id from distribution */
232
233 open c2(distributionId, draftId);
234 fetch c2 into l_ccid, l_sob;
235 close c2;
236
237 /* find chart of account id from set of book */
238 SELECT chart_of_accounts_id
239 INTO l_chart_of_accounts_id
240 FROM gl_sets_of_books
241 WHERE set_of_books_id = l_sob;
242
243 /* get the all the segment array */
244 l_result := FND_FLEX_EXT.GET_SEGMENTS(
245 'SQLGL',
246 'GL#',
247 l_chart_of_accounts_id,
248 l_ccid,
249 l_num_segments,
250 l_segments);
251
252 IF (NOT l_result) THEN
253 APP_EXCEPTION.RAISE_EXCEPTION;
254 END IF;
255
256 /* get the segment number for the given segment name */
257 l_result := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
258 101,
259 'GL#',
260 l_chart_of_accounts_id,
261 segmentName,
262 l_segment_num);
263
264 IF (NOT l_result) THEN
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 END IF;
267
268 l_seg_val := l_segments(l_segment_num);
269
270 RETURN l_seg_val;
271 EXCEPTION
272 when others then
273 -- TODO: log error
274 raise;
275 END;
276
277 --------------------------------------------------------------------------------
278 --Start of Comments
279 --Name: get_changed_req_total
280 --Pre-reqs:
281 -- None.
282 --Modifies:
283 -- None.
284 --Locks:
285 -- None.
286 --Function:
287 -- Returns the requisition total, which includes requester changes.
288 --Parameters:
289 --IN:
290 --reqHeaderId
291 -- Requisition Header ID
292 --OUT:
293 --Testing:
294 --
295 --End of Comments
296 -------------------------------------------------------------------------------
297 FUNCTION get_changed_req_total(ReqHeaderId IN NUMBER)
298 RETURN NUMBER IS
299 l_req_total NUMBER := 0;
300 l_org_id po_requisition_headers_all.org_id%TYPE;
301
302 BEGIN
303 select org_id
304 into l_org_id
305 from po_requisition_headers_all
306 where requisition_header_id = ReqHeaderId;
307
308 IF l_org_id is NOT NULL THEN
309 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
310 END IF;
311
312 SELECT sum(po_calculatereqtotal_pvt.get_req_distribution_total(
313 PORL.requisition_header_id,PORL.requisition_line_id,
314 PORD.distribution_id))
315 into l_req_total
316 FROM
317 PO_REQ_DISTRIBUTIONS_ALL PORD, -- <R12 MOAC>
318 PO_REQUISITION_LINES PORL
319 WHERE PORL.requisition_header_id = ReqHeaderId
320 AND PORL.requisition_line_id = PORD.requisition_line_id
321 AND nvl(PORL.cancel_flag, 'N') = 'N'
322 AND nvl(PORL.modified_by_agent_flag, 'N') = 'N';
323
324 return l_req_total;
325 END;
326
327 --------------------------------------------------------------------------------
328 --Start of Comments
329 --Name: get_new_req_header_id
330 --Pre-reqs:
331 -- None.
332 --Modifies:
333 -- None.
334 --Locks:
335 -- None.
336 --Function:
337 -- Given a requisition header ID, this function first identifies if there a working copy for this requisition
338 -- If yes then return the working copy's requisition header ID
339 -- Otherewise, return the input requisition header ID.
340 --Parameters:
341 --IN:
342 --oldReqHeaderId
343 -- Requisition Header ID
344 --OUT:
345 --Testing:
346 --
347 --End of Comments
348 -------------------------------------------------------------------------------
349 function get_new_req_header_id (oldReqHeaderId IN NUMBER) return number IS
350
351 l_api_name varchar2(50):= 'get_new_req_header_id';
352 reqNumber po_requisition_headers.segment1%TYPE;
353 orgId po_requisition_headers.org_id%TYPE;
354 newReqHeaderId po_requisition_headers.requisition_header_id%TYPE;
355 newReqNumber po_requisition_headers.segment1%TYPE;
356
357 begin
358 select segment1, org_id into reqNumber, orgId
359 from po_requisition_headers_all
360 where requisition_header_id = oldReqHeaderId;
361
362 newReqNumber := '##' || reqNumber;
363
364 begin
365 select requisition_header_id into newReqHeaderId
366 from po_requisition_headers_all
367 where segment1 = newReqNumber
368 and org_id = orgId ; -- <R12 MOAC>
369 exception
370 when NO_DATA_FOUND then
371 return oldReqHeaderId;
372 end;
373 return newReqHeaderId;
374
375 exception
376 when others then
377 if g_fnd_debug = 'Y' then
378 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
379 FND_LOG.string(FND_LOG.level_unexpected, 'icx.plsql.PO_AME_SETUP_PVT' ||
380 l_api_name || '.others_exception', sqlerrm);
381 END IF;
382 end if;
383 raise;
384 end;
385
386 --------------------------------------------------------------------------------
387 --Start of Comments
388 --Name: is_system_approver_mandatory
389 --Pre-reqs:
390 -- None.
391 --Modifies:
392 -- None.
393 --Locks:
394 -- None.
395 --Function:
396 -- Returns value based on profile POR_SYS_GENERATED_APPROVERS_MANDATORY.
397 --Parameters:
398 --IN:
399 --reqHeaderId
400 -- Requisition Header ID
401 --OUT:
402 --
403 --Testing:
404 --
405 --End of Comments
406 -------------------------------------------------------------------------------
407 FUNCTION is_system_approver_mandatory(reqHeaderId IN NUMBER)
408 RETURN VARCHAR2 IS
409 l_user_id fnd_user.user_id%TYPE;
410 l_option_value fnd_profile_option_values.profile_option_value%TYPE;
411 BEGIN
412
413 l_option_value:= fnd_profile.VALUE_SPECIFIC('POR_SYS_GENERATED_APPROVERS_SUPPRESS');
414
415 if g_fnd_debug = 'Y' then
416 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
417 FND_LOG.string(FND_LOG.level_statement, 'icx.plsql.PO_AME_SETUP_PVT.is_system_approver_mandatory','suppress l_option_value=' ||
418 l_option_value );
419 END IF;
420 END IF;
421
422 if l_option_value = 'Y' then
423 RETURN 'N';
424 end if;
425
426 BEGIN
427 SELECT fu.user_id
428 INTO l_user_id
429 FROM fnd_user fu, po_requisition_headers_all prh
430 WHERE prh.requisition_header_id = reqHeaderId and
431 prh.preparer_id = fu.employee_id;
432 EXCEPTION
433 when others then
434 fnd_profile.get('POR_SYS_GENERATED_APPROVERS_MANDATORY', l_option_value);
435 if g_fnd_debug = 'Y' then
436 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
437 FND_LOG.string(FND_LOG.level_statement, 'icx.plsql.PO_AME_SETUP_PVT.is_system_approver_mandatory',' l_option_value=' ||
438 l_option_value );
439 END IF;
440 END IF;
441
442 RETURN l_option_value;
443
444 END;
445 l_option_value := fnd_profile.value_specific('POR_SYS_GENERATED_APPROVERS_MANDATORY', l_user_id);
446 if g_fnd_debug = 'Y' then
447 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
448 FND_LOG.string(FND_LOG.level_statement, '2 icx.plsql.PO_AME_SETUP_PVT.is_system_approver_mandatory',' l_option_value=' ||
449 l_option_value );
450 END IF;
451 END IF;
452 RETURN l_option_value;
453
454 EXCEPTION
455 when others then
456 RETURN 'Y';
457 END;
458
459
460 --------------------------------------------------------------------------------
461 --Start of Comments
462 --Name: can_preparer_approve
463 --Pre-reqs:
464 -- None.
465 --Modifies:
466 -- None.
467 --Locks:
468 -- None.
469 --Function:
470 -- Returns 'Y' if preparer can approve requisition;
471 -- Returns 'N' if preparer cannot approve requisition;
472 -- based on po document set up.
473 --Parameters:
474 --IN:
475 --reqHeaderId
476 -- Requisition Header ID
477 --OUT:
478 --
479 --Testing:
480 --
481 --End of Comments
482 -------------------------------------------------------------------------------
483 FUNCTION can_preparer_approve(reqHeaderId IN NUMBER)
484 RETURN VARCHAR2 IS
485 l_can_approve po_document_types_all_b.can_preparer_approve_flag%TYPE;
486
487 BEGIN
488 SELECT NVL(pdt.can_preparer_approve_flag, 'N')
489 INTO l_can_approve
490 FROM po_document_types_all_b pdt, po_requisition_headers_all prh
491 WHERE prh.requisition_header_id = reqHeaderId and
492 pdt.org_id = prh.org_id and -- <R12 MOAC>
493 prh.type_lookup_code = pdt.document_subtype and
494 pdt.DOCUMENT_TYPE_CODE='REQUISITION';
495
496 RETURN l_can_approve;
497 EXCEPTION
498 when others then
499 RETURN 'N';
500 END;
501
502 /* Code start for CLM */
503 --------------------------------------------------------------------------------
504 --Start of Comments
505 --Name: can_preparer_approve_po
506 --Pre-reqs:
507 -- None.
508 --Modifies:
509 -- None.
510 --Locks:
511 -- None.
512 --Function:
513 -- Returns 'Y' if preparer can approve PO;
514 -- Returns 'N' if preparer cannot approve PO;
515 -- based on po document set up.
516 --Parameters:
517 --IN:
518 --ameApprovalId
519 -- AME Approval ID
520 --OUT:
521 --
522 --Testing:
523 --
524 --End of Comments
525 -------------------------------------------------------------------------------
526 FUNCTION can_preparer_approve_po(ameApprovalId IN NUMBER)
527 RETURN VARCHAR2 IS
528 l_can_approve po_document_types_all_b.can_preparer_approve_flag%TYPE;
529
530 BEGIN
531 SELECT NVL(pdt.can_preparer_approve_flag, 'N')
532 INTO l_can_approve
533 FROM po_document_types_all_b pdt, po_headers_merge_v phm
534 WHERE phm.ame_approval_id = ameApprovalId and
535 pdt.org_id = phm.org_id and -- <R12 MOAC>
536 phm.type_lookup_code = pdt.document_subtype and
537 pdt.DOCUMENT_TYPE_CODE in ('PO','PA');
538
539 RETURN l_can_approve;
540 EXCEPTION
541 when others then
542 RETURN 'N';
543 END;
544
545 --------------------------------------------------------------------------------
546 --Start of Comments
547 --Name: is_system_approver_mandatory_for_po
548 --Pre-reqs:
549 -- None.
550 --Modifies:
551 -- None.
552 --Locks:
553 -- None.
554 --Function:
555 -- Returns value based on profile POR_SYS_GENERATED_APPROVERS_MANDATORY.
556 --Parameters:
557 --IN:
558 --ameApprovalId
559 -- AME Approval ID
560 --OUT:
561 --
562 --Testing:
563 --
564 --End of Comments
565 -------------------------------------------------------------------------------
566 FUNCTION is_system_app_mandatory_po(ameApprovalId IN NUMBER)
567 RETURN VARCHAR2 IS
568 l_user_id fnd_user.user_id%TYPE;
569 l_option_value fnd_profile_option_values.profile_option_value%TYPE;
570
571 BEGIN
572
573 -- AME Project
574 l_option_value:= fnd_profile.VALUE_SPECIFIC('PO_SYS_GENERATED_APPROVERS_SUPPRESS');
575
576 if l_option_value = 'Y' then
577 RETURN 'N';
578 end if;
579
580 BEGIN
581 SELECT fu.user_id
582 INTO l_user_id
583 FROM fnd_user fu, po_headers_merge_v phm
584 WHERE phm.ame_approval_id = ameApprovalId and
585 phm.agent_id = fu.employee_id;
586 EXCEPTION
587 when others then
588 fnd_profile.get('PO_SYS_GENERATED_APPROVERS_MANDATORY', l_option_value);
589 RETURN l_option_value;
590
591 END;
592 l_option_value := fnd_profile.value_specific('PO_SYS_GENERATED_APPROVERS_MANDATORY', l_user_id);
593
594 RETURN l_option_value;
595
596 EXCEPTION
597 when others then
598 RETURN 'Y';
599 END;
600
601 --------------------------------------------------------------------------------
602 --Start of Comments
603 --Name: get_function_currency_po
604 --Pre-reqs:
605 -- None.
606 --Modifies:
607 -- None.
608 --Locks:
609 -- None.
610 --Function:
611 -- Returns the function currency of PO preparing org
612 --Parameters:
613 --IN:
614 --ameApprovalId
615 -- AME Approval ID
616 --OUT:
617 --
618 --Testing:
619 --
620 --End of Comments
621 -------------------------------------------------------------------------------
622 FUNCTION get_function_currency_po(ameApprovalId IN NUMBER)
623 RETURN VARCHAR2 IS
624 l_currency_code gl_sets_of_books.currency_code%TYPE;
625
626 BEGIN
627 SELECT gls.currency_code
628 INTO l_currency_code
629 FROM financials_system_params_all fsp,
630 gl_sets_of_books gls,
631 po_headers_merge_v phm
632 WHERE fsp.set_of_books_id = gls.set_of_books_id and
633 fsp.org_id = phm.org_id and -- <R12 MOAC>
634 phm.ame_approval_id = ameApprovalId;
635 RETURN l_currency_code;
636 EXCEPTION
637 when others then
638 raise;
639 END;
640
641 --------------------------------------------------------------------------------
642 --Start of Comments
643 --Name: get_rate_type_po
644 --Pre-reqs:
645 -- None.
646 --Modifies:
647 -- None.
648 --Locks:
649 -- None.
650 --Function:
651 -- Returns the default rate type of purchase order preprarer.
652 --Parameters:
653 --IN:
654 --ameApprovalId
655 -- AME Approval ID
656 --OUT:
657 --
658 --Testing:
659 --
660 --End of Comments
661 -------------------------------------------------------------------------------
662 FUNCTION get_rate_type_po(ameApprovalId IN NUMBER)
663 RETURN VARCHAR2 IS
664 l_user_id fnd_user.user_id%TYPE;
665 l_rate_type fnd_profile_option_values.profile_option_value%TYPE;
666
667 BEGIN
668 BEGIN
669 SELECT fu.user_id
670 INTO l_user_id
671 FROM fnd_user fu, po_headers_merge_v phm
672 WHERE phm.ame_approval_id = ameApprovalId and
673 phm.agent_id = fu.employee_id;
674 EXCEPTION
675 when others then
676 fnd_profile.get('POR_DEFAULT_RATE_TYPE', l_rate_type);
677 RETURN l_rate_type;
678 END;
679 l_rate_type := fnd_profile.value_specific('POR_DEFAULT_RATE_TYPE', l_user_id);
680 RETURN l_rate_type;
681 EXCEPTION
682 when others then
683 raise;
684 END;
685
686 --------------------------------------------------------------------------------
687 --Start of Comments
688 --Name: get_modification_amount
689 --Pre-reqs:
690 -- None.
691 --Modifies:
692 -- None.
693 --Locks:
694 -- None.
695 --Function:
696 -- Returns the amount modified through the modification document
697 --Parameters:
698 --IN:
699 --ameApprovalId
700 -- AME Approval ID
701 --OUT:
702 --
703 --Testing:
704 --
705 --End of Comments
706 -------------------------------------------------------------------------------
707
708 FUNCTION get_modification_amount(ameApprovalId IN NUMBER)
709 RETURN NUMBER IS
710 l_original_amount NUMBER;
711 l_changed_amount NUMBER;
712 l_modification_amount NUMBER;
713
714 BEGIN
715 BEGIN
716 select nvl(SUM(decode(plm.order_type_lookup_code, 'RATE', plm.amount, 'FIXED PRICE', plm.amount, plm.quantity * plm.unit_price)), 0)
717 into l_original_amount
718 from po_lines_all plm, po_headers_merge_v phm
719 where plm.po_header_id = phm.po_header_id
720 and phm.ame_approval_id = ameApprovalId;
721 EXCEPTION
722 WHEN no_data_found THEN
723 null;
724 END;
725
726 BEGIN
727 select nvl(SUM(decode(plm.order_type_lookup_code, 'RATE', plm.amount, 'FIXED PRICE', plm.amount, plm.quantity * plm.unit_price)), 0)
728 into l_changed_amount
729 from po_lines_merge_v plm,
730 po_headers_merge_v phm
731 where plm.po_header_id = phm.po_header_id
732 and phm.draft_id = plm.draft_id
733 and phm.ame_approval_id = ameApprovalId;
734
735 EXCEPTION
736 WHEN no_data_found THEN
737 null;
738 END;
739 IF l_original_amount <> l_changed_amount AND l_changed_amount > 0 THEN
740 l_modification_amount := ABS(l_original_amount - l_changed_amount);
741 ELSE
742 l_modification_amount := 0;
743 END IF;
744 return l_modification_amount;
745
746 EXCEPTION
747 when others then
748 raise;
749 END;
750
751 --------------------------------------------------------------------------------
752 --Start of Comments
753 --Name: Sum of the absolute values of the line change amounts for the modification.
754 --Pre-reqs:
755 -- None.
756 --Modifies:
757 -- None.
758 --Locks:
759 -- None.
760 --Function:
761 -- Returns Sum of the absolute values of the line change amounts for the modification.
762 --Parameters:
763 --IN:
764 -- po_header_id
765 --OUT:
766 --
767 --Testing:
768 --
769 --End of Comments
770 -------------------------------------------------------------------------------
771
772 FUNCTION get_mod_amount_for_warrant(p_po_header_id IN NUMBER, p_draft_id NUMBER)
773 RETURN NUMBER IS
774 l_changed_amount NUMBER;
775
776 BEGIN
777 select nvl(SUM(
778 ABS((decode(pld.order_type_lookup_code, 'RATE', pld.amount, 'FIXED PRICE', pld.amount, pld.quantity * pld.unit_price)) -
779 NVL((decode(pl.order_type_lookup_code, 'RATE', pl.amount, 'FIXED PRICE', pl.amount, pl.quantity * pl.unit_price)), 0) )
780 ), 0)
781 into l_changed_amount
782 from po_lines_draft_all pld, po_lines_all pl
783 where pld.po_header_id = p_po_header_id
784 and pld.draft_id = p_draft_id
785 and pl.po_line_id(+) = pld.po_line_id;
786
787 return l_changed_amount;
788
789 EXCEPTION
790 when others then
791 return 0;
792 END;
793 END PO_AME_SETUP_PVT;