1 PACKAGE BODY fv_ap_prepay_pkg AS
2 -- $Header: FVAPIPPB.pls 120.9 2005/09/13 18:52:01 ksriniva ship $
3 g_module_name VARCHAR2(100) := 'fv.plsql.FV_AP_PREPAY_PKG.';
4 PROCEDURE Funds_Reserve(p_invoice_id IN NUMBER,
5 p_unique_packet_id_per IN VARCHAR2,
6 p_set_of_books_id IN NUMBER,
7 p_base_currency_code IN VARCHAR2,
8 p_inv_enc_type_id IN NUMBER,
9 p_purch_enc_type_id IN NUMBER,
10 p_conc_flag IN VARCHAR2,
11 p_system_user IN NUMBER,
12 p_ussgl_option IN VARCHAR2,
13 p_holds IN OUT NOCOPY AP_APPROVAL_PKG.HOLDSARRAY,
14 p_hold_count IN OUT NOCOPY AP_APPROVAL_PKG.COUNTARRAY,
15 p_release_count IN OUT NOCOPY AP_APPROVAL_PKG.COUNTARRAY,
16 p_calling_sequence IN VARCHAR2) IS
17
18 BEGIN
19 null;
20 END FUNDS_RESERVE;
21 ------------------------------------------------------------------------------------
22 -- Purpose :
23 -- This procedure is called when matching a prepayment invoice to a purchase
24 -- order. If the invoice amount plus the tolerance (setup on the Define
25 -- Federal Options form) is greater than the remaining amount on the purchase
26 -- order, then the transaction is not allowed.
27 --
28 -- History
29 --
30 -- Date Name Comments
31
32 -- 15-JUL-2003 Shiva Nama Created
33 -------------------------------------------------------------------------------
34
35 PROCEDURE tolerance_check(p_line_location_id IN NUMBER,
36 p_match_amount IN NUMBER,
37 p_min_acc_unit IN NUMBER,
38 p_precision IN NUMBER,
39 p_tolerance_check_status OUT NOCOPY VARCHAR2) IS
40
41
42 l_module_name VARCHAR2(200) := g_module_name || 'tolerance_check';
43 l_errbuf VARCHAR2(1024);
44 l_prepay_tolerance_flag VARCHAR2(1);
45 l_prepay_tolerance NUMBER;
46 l_billed_amount NUMBER;
47 l_total_amount NUMBER;
48 l_remain_amount NUMBER;
49
50 BEGIN
51
52 p_tolerance_check_status := 'Y';
53
54 SELECT NVL(prepayment_tolerance_flag, 'N'), prepayment_tolerance
55 INTO l_prepay_tolerance_flag, l_prepay_tolerance
56 FROM fv_operating_units;
57
58 IF l_prepay_tolerance IS NOT NULL THEN
59
60 SELECT DECODE(MATCHING_BASIS , 'AMOUNT' ,
61 NVL(ll.amount,0)-nvl(ll.amount_cancelled,0),
62 (NVL(ll.quantity,0) - NVL(ll.quantity_cancelled,0))* ll.price_override) total_amount,
63 decode(matching_basis , 'AMOUNT' , NVL(ll.amount_financed,0) , NVL(ll.quantity_financed,0) * ll.price_override) billed_amount
64 INTO l_total_amount , l_billed_amount
65 FROM po_line_locations_all ll
66 WHERE ll.line_location_id = p_line_location_id;
67
68
69 /* ---------------------- commented out as part of PO-uptake -----------------------
70 SELECT (ll.quantity - ll.quantity_cancelled) * l.unit_price total_amount,
71 (ll.quantity_billed * l.unit_price) remain_amount
72 INTO l_total_amount , l_billed_amount
73 FROM po_line_locations_all ll,
74 po_lines_all l
75 WHERE ll.line_location_id = p_line_location_id
76 AND l.po_line_id = ll.po_line_id;
77
78 ---------------------- commented out as part of PO-uptake ----------------------- */
79
80 l_remain_amount := ( (l_total_amount * ( (100+l_prepay_tolerance)/100) ) - l_billed_amount );
81
82
83 IF (p_min_acc_unit IS NULL) THEN
84 l_remain_amount := ROUND(l_remain_amount, p_precision);
85 ELSE
86 l_remain_amount := ROUND(l_remain_amount / p_min_acc_unit) * (p_min_acc_unit);
87 END IF;
88
89 IF l_prepay_tolerance_flag = 'Y' THEN
90 IF p_match_amount > l_remain_amount THEN
91 p_tolerance_check_status := 'N';
92 END IF;
93 ELSE
94 IF p_match_amount > l_remain_amount THEN
95 p_tolerance_check_status := 'W';
96 END IF;
97 END IF;
98
99 END IF;
100
101 EXCEPTION
102
103 WHEN NO_DATA_FOUND THEN
104 -- No OPTION defined for this org so pass the check
105 NULL;
106
107 WHEN OTHERS THEN
108 p_tolerance_check_status := 'N';
109 l_errbuf := SQLERRM;
110 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
111
112
113 END tolerance_check;
114 -----------------------------------------------------------------------
115 -- PROCEDURE Get_Prorated_Amount
116 -- Gets the prorated prepayment amount per distribution
117 -- Parameters
118 ----------
119 -- Distribution_Table List of Invoice Distribution Identifiers
120 -- Invoice_ID Invoice Identifier
121 -- Prorated_Amount_Table: OUT NOCOPY contains invoice_dist_id, amount and
122 -- ussgl_transaction_code
123 -----------------------------------------------------------------------------
124
125 PROCEDURE get_prorated_amount
126 (P_Invoice_ID IN NUMBER
127 ,P_Dist_Tab IN dist_tab
128 ,P_Prorated_Amt OUT NOCOPY prorate_amt_tab
129 ,P_calling_sequence IN VARCHAR2 DEFAULT NULL
130 ,p_status OUT NOCOPY NUMBER
131 )
132 IS
133
134 BEGIN
135 null;
136 END get_prorated_amount;
137 -----------------------------------------------------------------------
138
139 PROCEDURE create_prepay_lines(p_packet_id IN NUMBER,
140 p_status OUT NOCOPY NUMBER) IS
141
142 BEGIN
143 null;
144 END create_prepay_lines;
145 -----------------------------------------------------------------------
146 END fv_ap_prepay_pkg;