1 PACKAGE BODY AP_ACCOUNTING_UTILITIES_PKG as
2 /* $Header: apslautb.pls 115.5 2004/04/02 18:49:06 schitlap noship $ */
3
4 /*============================================================================
5 | FUNCTION - Get_PO_REVERSED_ENCUMB_AMOUNT
6 |
7 | DESCRIPTION
8 | fetch the amount of PO encumbrance reversed against the given PO
9 | distribution from all invoices for a given date range in functional
10 | currency. Calculation includes PO encumbrance which are in GL only.
11 | In case Invoice encumbrance type is the same as PO encumbrance, we
12 | need to exclude the variance.
13 | it returns actual amount or 0 if there is po reversed encumbrance
14 | line existing, otherwise returns NULL.
15 |
16 | PARAMETERS
17 | P_Po_distribution_id - po_distribution_id (in)
18 | P_Start_date - Start gl date (in)
19 | P_End_date - End gl date (in)
20 | P_Calling_Sequence - debug usage
21 |
22 | KNOWN ISSUES:
23 |
24 | NOTES:
25 |
26 | 1. In case user changes the purchase order encumbrance
27 | type or Invoice encumbrance type after invoice is
28 | validated, this API might not return a valid value.
29 |
30 | MODIFICATION HISTORY
31 | Date Author Description of Change
32 |
33 *==========================================================================*/
34
35 FUNCTION Get_PO_Reversed_Encumb_Amount(
36 P_Po_Distribution_Id IN NUMBER,
37 P_Start_gl_Date IN DATE,
38 P_End_gl_Date IN DATE,
39 P_Calling_Sequence IN VARCHAR2 DEFAULT NULL)
40
41 RETURN NUMBER
42 IS
43
44 l_current_calling_sequence VARCHAR2(2000);
45 l_debug_info VARCHAR2(100);
46 l_debug_loc VARCHAR2(50) :='Get_PO_Reversed_Encumb_Amount';
47
48 l_unencumbered_amount NUMBER;
49 l_upg_unencumbered_amount NUMBER;
50 l_total_unencumbered_amount NUMBER;
51
52 CURSOR po_enc_reversed_cur IS
53 SELECT sum(nvl(ael.accounted_cr,0) - nvl(ael.accounted_dr,0))
54 FROM AP_INVOICE_DISTRIBUTIONS aid,
55 AP_ENCUMBRANCE_LINES ael,
56 financials_system_parameters fsp
57 WHERE aid.po_distribution_id = P_po_distribution_id
58 AND aid.invoice_distribution_id = ael.invoice_distribution_id
59 AND ( ( p_start_gl_date is not null
60 and p_start_gl_date <= ael.accounting_date ) or
61 ( p_start_gl_date is null ) )
62 AND ( (p_end_gl_date is not null
63 and p_end_gl_date >= ael.accounting_date ) or
64 (p_end_gl_date is null ) )
65 AND ael.encumbrance_line_type not in ('IPV', 'ERV', 'QV')
66 AND ( (ael.ae_header_id is null and
67 aid.encumbered_flag = 'Y' and
68 FSP.purch_encumbrance_type_id <> FSP.inv_encumbrance_type_id) or
69 (ael.ae_header_id is not null and
70 FSP.purch_encumbrance_type_id = FSP.inv_encumbrance_type_id and
71 'Y' = ( select gl_transfer_flag
72 from ap_ae_headers aeh
73 where aeh.ae_header_id = ael.ae_header_id ) )
74 )
75 AND nvl(aid.org_id,-1) = nvl(fsp.org_id,-1)
76 AND ael.encumbrance_type_id = fsp.purch_encumbrance_type_id;
77
78
79
80 --3133103, added this cursor as well as logic below that adds its value to the
81 --value of the cursor above.
82 CURSOR upgraded_po_enc_rev_cur IS
83 SELECT sum (nvl(nvl(aid.base_amount,aid.amount),0) -
84 nvl(aid.base_invoice_price_variance ,0) -
85 nvl(aid.exchange_rate_variance,0) -
86 nvl(aid.base_quantity_variance,0))
87 FROM ap_invoice_distributions aid,
88 po_distributions pd,
89 financials_system_parameters fs
90 where aid.po_distribution_id = p_po_distribution_id
91 and aid.po_distribution_id = pd.po_distribution_id
92 and nvl(aid.org_id,-1) = nvl(fs.org_id,-1)
93 and fs.inv_encumbrance_type_id <> fs.purch_encumbrance_type_id
94 and NVL(PD.accrue_on_receipt_flag,'N') = 'N'
95 AND AID.po_distribution_id is not null
96 AND nvl(aid.match_status_flag, 'N') = 'A'
97 AND nvl(aid.encumbered_flag, 'N') = 'Y'
98 AND (aid.accrual_posted_flag = 'Y' or aid.cash_posted_flag = 'Y')
99 AND (( p_start_gl_date is not null and p_start_gl_date <= aid.accounting_date) or (p_start_gl_date is null))
100 AND ((p_end_gl_date is not null and p_end_gl_date >= aid.accounting_date) or (p_end_gl_date is null))
101 AND NOT EXISTS (SELECT 'release 11.5 encumbrance'
102 from ap_encumbrance_lines_all ael
103 where ael.invoice_distribution_id = aid.invoice_distribution_id);
104
105
106
107
108 BEGIN
109
110 l_current_calling_sequence := 'AP_FUNDS_CONTROL_PKG.'
111 || 'Get_PO_Reversed_Encumb_Amount<-'
112 || P_calling_sequence;
113
114 OPEN po_enc_reversed_cur;
115 -----------------------------------------------------------
116 l_debug_info := l_debug_loc || 'Open the po_encumbrance_cur' ;
117 -------------------------------------------------------------
118 FETCH po_enc_reversed_cur INTO
119 l_unencumbered_amount;
120
121 IF (po_enc_reversed_cur%NOTFOUND) THEN
122 ------------------------------------------------------------
123 l_debug_info := l_debug_loc || 'NO encumbrance line exists';
124 ------------------------------------------------------------
125 l_unencumbered_amount := NULL;
126 END IF;
127
128 CLOSE po_enc_reversed_cur;
129
130 -- Bug 3503864: Added the l_unencumbered_amount check for
131 -- opening the upgrade cursor
132
133 IF l_unencumbered_amount IS NULL THEN
134
135 OPEN upgraded_po_enc_rev_cur;
136 -----------------------------------------------------------
137 l_debug_info := l_debug_loc || 'Open upgraded_po_enc_rev_cur ' ;
138 -------------------------------------------------------------
139 FETCH upgraded_po_enc_rev_cur INTO
140 l_upg_unencumbered_amount;
141
142 IF (upgraded_po_enc_rev_cur%NOTFOUND) THEN
143 ------------------------------------------------------------
144 l_debug_info := l_debug_loc || 'NO upgraded encumbrance reversals exist ';
145 ------------------------------------------------------------
146 l_upg_unencumbered_amount := NULL;
147 END IF;
148
149 CLOSE upgraded_po_enc_rev_cur;
150
151 END IF;
152
153
154 IF (l_unencumbered_amount is not null or l_upg_unencumbered_amount is not null) THEN
155 l_total_unencumbered_amount := nvl(l_unencumbered_amount,0) + nvl(l_upg_unencumbered_amount,0);
156 ELSE
157 l_total_unencumbered_amount := NULL;
158 END IF;
159
160 RETURN (l_total_unencumbered_amount);
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 --
165 -- Bug 3546586
166 -- Commented this portion as FND_MESSAGE is not compliant with the pragma
167 -- restriction specified in the package spec. Let the calling program
168 -- handle the exception in this particular case.
169 --
170 /*
171 IF (SQLCODE <> -20001) THEN
172 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
173 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
174 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_current_calling_sequence);
175 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'po distribution id = '
176 || p_po_distribution_id );
177 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
178 END IF;
179 */
180 IF ( po_enc_reversed_cur%ISOPEN ) THEN
181 CLOSE po_enc_reversed_cur;
182 END IF;
183
184 RAISE;
185 END Get_PO_Reversed_Encumb_Amount;
186
187
188 END AP_ACCOUNTING_UTILITIES_PKG;
189
[Home] [Help]