4 -----------------------------------------------------------------------
1 PACKAGE BODY AP_PA_API_PKG AS
2 /* $Header: appaapib.pls 120.11.12020000.2 2012/11/28 10:47:01 mkmeda ship $ */
3
5 -- Function get_org_id returns the org_id that the invoice belongs to.
6 -----------------------------------------------------------------------
7
8 Function get_org_id (P_Invoice_Id IN number) RETURN number
9 IS
10 l_org_Id number;
11 Cursor select_org_id is
12 SELECT org_id
13 FROM ap_invoices_all
14 WHERE invoice_id = P_Invoice_Id;
15 Begin
16 OPEN select_org_id;
17 FETCH select_org_id INTO l_org_Id;
18 close select_org_id;
19
20 Return (l_org_Id);
21 End get_org_id;
22
23 -----------------------------------------------------------------------
24 -- Function get_discount_pay_dists_flag returns 'Y' if there
25 -- are any payment distributions associated with an invoice
26 -- which are of type DISCOUNT.
27 -----------------------------------------------------------------------
28 FUNCTION get_discount_pay_dists_flag (P_invoice_id IN number)
29 RETURN varchar2
30 IS
31 l_flag varchar2(1) := 'N';
32 cursor payment_cursor is
33 SELECT 'Y'
34 FROM ap_invoice_payments_all
35 WHERE invoice_id = P_invoice_id
36 AND nvl(discount_taken,0) <> 0;
37
38 BEGIN
39
40 open payment_cursor;
41 fetch payment_cursor into l_flag;
42 close payment_cursor;
43
44 return(l_flag);
45
46 END get_discount_pay_dists_flag;
47
48 -----------------------------------------------------------------------
49 -- Function selected_for_payment_flag returns 'Y' if an invoice
50 -- has been selected for payment; function returns 'N' otherwise.
51 -----------------------------------------------------------------------
52 FUNCTION selected_for_payment_flag (P_invoice_id IN number) RETURN varchar2
53 IS
54 l_flag varchar2(1) := 'N';
55 cursor selected_for_payment_cursor is
56 SELECT 'Y'
57 FROM ap_selected_invoices_all
58 WHERE invoice_id = P_invoice_id;
59
60 BEGIN
61
62 open selected_for_payment_cursor;
63 fetch selected_for_payment_cursor into l_flag;
64 close selected_for_payment_cursor;
65
66 return(l_flag);
67
68 END selected_for_payment_flag;
69
70 -----------------------------------------------------------------------
71 -- Function get_payments_exist_flag returns 'Y' if an invoice
72 -- has corresponding records in ap_invoice_payments
73 -----------------------------------------------------------------------
74 FUNCTION get_payments_exist_flag (P_invoice_id IN number)
75 RETURN varchar2
76 IS
77 l_flag varchar2(1) := 'N';
78 cursor payments_exist_cursor is
79 SELECT 'Y'
80 FROM ap_invoice_payments_all
81 WHERE invoice_id = P_invoice_id;
82
83 BEGIN
84
85 open payments_exist_cursor;
86 fetch payments_exist_cursor into l_flag;
87 close payments_exist_cursor;
88
89 return(l_flag);
90
91 END get_payments_exist_flag;
92
93
94 -----------------------------------------------------------------------
95 -- Function get_invoice_status returns message_code as follows:
96 -- 1) PA_INV_CANCELLED: If the invoice has already been cancelled.
97 -- 2) PA_INV_PREPAY: If the prepayment associated with this item has
98 -- been partially or fully paid.
99 -- 3) PA_INV_AUTO_OFFSETS_PAID: If the invoice associated with this
100 -- item has been paid and the automatic offset method is used.
101 -- 4) PA_INV_DISC_PRORATE: If the invoice associated with
102 -- this item has been partially or fully paid and there are discount
103 -- payment distributions associated with the invoice.
104 -- 5) PA_INV_CASH: If you are running cash basis accounting.
105 -- 6) PA_INV_SEL_PAYMENT: If the invoice associated with this
106 -- item has been selected for payment.
107 -- 7) PA_INV_NOADJUST: If the invoice associated with this
108 -- item has been partially or fully paid and the payables options
109 -- setting does not allow you to adjust paid invoices.
110 -- 8) PA_INV_RECEIPT_ACCRU: If the invoice associated with
111 -- this item was matched to a PO distribution and the
112 -- PO distribution had Accrue on Receipt checked.
113 -- 9) PA_INV_CLOSE_PO: If the invoice associated with this item
114 -- was matched to a PO and the PO is now FINALLY CLOSED.
115 -- ELSE returns 'N'
116 -----------------------------------------------------------------------
117
118 FUNCTION get_invoice_status (X_Invoice_Id IN NUMBER,
122 var_cancelled_date DATE;
119 Status_Type IN VARCHAR2)
120 RETURN VARCHAR2 IS
121
123 var_cancelled_by NUMBER;
124 var_payment_status_flag VARCHAR2(1);
125 var_allow_paid_invoice_adjust VARCHAR2(1);
126 var_automatic_offsets_flag VARCHAR2(1);
127 var_invoice_type_lookup_code VARCHAR2(25);
128 status NUMBER := 0;
129 accrue_on_receipt_num NUMBER;
130 closed_po_num NUMBER;
131
132 BEGIN
133
134 SELECT CANCELLED_DATE,
135 CANCELLED_BY,
136 PAYMENT_STATUS_FLAG,
137 ALLOW_PAID_INVOICE_ADJUST,
138 INVOICE_TYPE_LOOKUP_CODE
139 INTO var_cancelled_date,
140 var_cancelled_by,
141 var_payment_status_flag,
142 var_allow_paid_invoice_adjust,
143 var_invoice_type_lookup_code
144 FROM ap_invoices_all, ap_system_parameters_all
145 WHERE nvl(ap_system_parameters_all.org_id,-999) = nvl(AP_PA_API_PKG.get_org_id(X_Invoice_Id),-999)
146 AND invoice_id = X_Invoice_Id;
147
148 SELECT count(*)
149 INTO accrue_on_receipt_num
150 FROM po_distributions_ap_v POD, ap_invoice_distributions AID
151 WHERE POD.po_distribution_id = AID.po_distribution_id
152 AND AID.invoice_id = X_invoice_id
153 AND POD.accrue_on_receipt_flag = 'Y';
154
155 SELECT count(*)
156 INTO closed_po_num
157 FROM po_headers POH, po_distributions_ap_v POD, ap_invoice_distributions AID
158 WHERE POH.po_header_id = POD.po_header_id
159 AND POD.po_distribution_id = AID.po_distribution_id
160 AND AID.invoice_id = X_invoice_id
161 AND POH.closed_code = 'FINALLY CLOSED';
162
166 Return('PA_INV_SEL_PAYMENT');
163 If (Status_Type='TRANSFER') THEN
164
165 If Selected_for_Payment_Flag(X_invoice_Id) = 'Y' THEN
167 Else
168 Return('N');
169 End If;
170 End If;
171
172
173 If (Status_Type='ADJUSTMENTS') THEN
174
175 If (var_cancelled_date IS NOT NULL AND var_cancelled_by IS NOT NULL) THEN
176 return('PA_INV_CANCELLED');
177 /* For bug2938770.Commented the line below for prepayment*/
178
179 Elsif --(var_payment_status_flag IN ('Y','P')) AND
180 var_invoice_type_lookup_code = 'PREPAYMENT' THEN
181 return('PA_INV_PREPAY');
182
183 Elsif (var_payment_status_flag in ('Y','P')) AND
184 (AP_PA_API_PKG.get_payments_exist_flag(X_invoice_Id) = 'Y') THEN
185 return('PA_INV_AUTO_OFFSETS_PAID');
186
187 Elsif Selected_for_Payment_Flag(X_invoice_Id) = 'Y' THEN
188 return('PA_INV_SEL_PAYMENT');
189
190 Elsif (var_payment_status_flag IN ('Y','P')) AND
191 var_allow_paid_invoice_adjust = 'N' THEN
192 return('PA_INV_NOADJUST');
193
194 Elsif accrue_on_receipt_num >0 THEN
195 return ('PA_INV_RECEIPT_ACCRU');
196
197 Elsif closed_po_num >0 THEN
198 return ('PA_INV_CLOSE_PO');
199
200 Else
201 return('N');
202 END If;
203 Else
204 return(null);
205 END If;
206
207 EXCEPTION WHEN others THEN
208 RAISE NO_DATA_FOUND;
209
210 END get_invoice_status;
211
212
213 FUNCTION get_asset_tracking_flag (p_ccid IN NUMBER) return VARCHAR2 is
214 l_account_type gl_code_combinations.account_type%type;
215 p_asset_tracking_flag varchar2(1);
216 BEGIN
217 SELECT account_type
218 INTO l_account_type
219 FROM gl_code_combinations
220 WHERE code_combination_id = p_ccid ;
221
222 IF l_account_type = 'E' THEN
223 p_asset_tracking_flag := 'N' ;
224 ELSIF l_account_type <> 'E' THEN
225 IF l_account_type = 'A' THEN
226 p_asset_tracking_flag := 'Y';
227 ELSE
228 p_asset_tracking_flag := 'N' ;
229 END IF;
230 END IF;
231
232 return p_asset_tracking_flag;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 return null;
237 END get_asset_tracking_flag ;
238
239
240 -------------------------------------------------------------------------------
241 -- PROCEDURE ap_pa_discount_info - removed for bug 4588031
242 -- Added this procedure to enable PA to retrieve discount information from AP.
243 -------------------------------------------------------------------------------
244
245 -- BUG 4390507 : Added the procedure
246 /*---------------------------------------------------------------
247 |Get_Inv_Amount_Var is the new procedure added after |
248 |Amount-Based Matching Project so that amount_variances are also|
249 |interfaced to Projects. |
250 |This procedure will return the amount_variance and |
251 |and base_amount_variance when provided with a invoice_id & |
252 |invoice_distribution_id |
253 ----------------------------------------------------------------*/
254 PROCEDURE Get_Inv_Amount_Var (P_Invoice_Id IN NUMBER,
255 P_Invoice_Distribution_Id IN NUMBER,
256 P_Amount_Variance OUT NOCOPY NUMBER,
257 P_Base_Amount_Variance OUT NOCOPY NUMBER) IS
258 l_column_name VARCHAR2(30) := NULL;
259 l_statement VARCHAR2(2000) ;
260
261 BEGIN
262
263
264 /* bug 15909344 - From the base version of R12, AMOUNT_VARIANCE column exists. so no need to have all_tab_columns check.
265 this excercise done for online patching check.As per online patching standards, we should get logical columns instead of physical columns. commenting the code.
266
267 BEGIN
268
269 SELECT ATC.column_name
270 INTO l_column_name
271 FROM sys.all_tab_columns ATC
272 WHERE ATC.table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
273 AND ATC.column_name = 'AMOUNT_VARIANCE';
274
275 EXCEPTION
276 --One of reasons control is here is due to NO_DATA_FOUND exception,
277 --which means the column doesn't exist, so the user is on a
278 --family pack prior to FIN-PF.E in which case we do not
279 --have amount_variance columns.
280 WHEN OTHERS THEN
281 p_amount_variance := 0;
282 p_base_amount_variance := 0;
283 END ;
284
285
286 --Proceed further only if the columns are there.
287 IF (l_column_name IS NOT NULL) THEN
288 */
289 l_statement :=
290 'SELECT NVL(amount_variance,0), NVL(base_amount_variance,0)
291 FROM ap_invoice_distributions_all
292 WHERE invoice_id ='|| p_invoice_id ||
293 'AND invoice_distribution_id ='|| p_invoice_distribution_id ;
294
295 EXECUTE IMMEDIATE l_statement INTO p_amount_variance,
296 p_base_amount_variance;
297
298 -- END IF; --bug 15909344
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 p_amount_variance := 0;
303 p_base_amount_variance := 0;
304
305 END Get_Inv_Amount_Var;
306
307 END AP_PA_API_PKG;
[Home] [Help]