1 PACKAGE BODY AP_AUTOMATIC_PROPAGATION_PKG AS
2 /* $Header: apautprb.pls 120.6 2011/02/23 12:12:34 ankuprad noship $ */
3
4 FUNCTION Get_Affected_Invoices_Count(
5 P_external_bank_account_id iby_ext_bank_accounts.ext_bank_account_id%TYPE,
6 P_vendor_id ap_suppliers.vendor_id%TYPE,
7 P_vendor_site_id ap_supplier_sites.vendor_site_id%TYPE DEFAULT NULL,
8 P_party_Site_Id ap_supplier_sites.party_site_id%TYPE DEFAULT NULL,
9 P_org_id ap_invoices.org_id%TYPE DEFAULT NULL
10 ) RETURN NUMBER IS
11 l_affected_invoices NUMBER := 0;
12 BEGIN
13 BEGIN
14 --Address + OU level checking
15 IF (P_party_Site_Id IS NOT NULL and P_org_Id IS NOT NULL) THEN
16 SELECT COUNT(DISTINCT ai.invoice_id)
17 INTO l_affected_invoices
18 FROM ap_invoices_all ai, ap_payment_schedules_all aps
19 WHERE aps.external_bank_account_id = p_external_bank_account_id
20 AND ai.invoice_id = aps.invoice_id
21 -- AND ai.payment_status_flag IN ('N','P') /* Bug 11783234 Exclude 'partial paid payment schedules' status */
22 AND ai.payment_status_flag = 'N'
23 AND ai.cancelled_date IS NULL
24 AND ai.vendor_id = p_vendor_id
25 AND ai.org_id = p_org_id
26 AND (ai.party_site_id = p_party_site_id
27 or ( ai.party_site_id is null
28 and ai.vendor_site_id in (select vendor_site_id
29 from ap_supplier_sites ass
30 where ass.party_site_id = p_party_site_id
31 and ass.vendor_id = p_vendor_id)
32 )
33 )
34 AND aps.checkrun_id IS NULL; /* Bug 11783234 Added condition to remove selected invoices from this API */
35
36 --Address level checking
37 ELSIF (P_party_Site_Id IS NOT NULL) THEN
38 SELECT COUNT(DISTINCT ai.invoice_id)
39 INTO l_affected_invoices
40 FROM ap_invoices_all ai, ap_payment_schedules_all aps
41 WHERE aps.external_bank_account_id = p_external_bank_account_id
42 AND ai.invoice_id = aps.invoice_id
43 -- AND ai.payment_status_flag IN ('N','P') /* Bug 11783234 Exclude 'partial paid payment schedules' status */
44 AND ai.payment_status_flag = 'N'
45 AND ai.cancelled_date IS NULL
46 AND ai.vendor_id = p_vendor_id
47 AND (ai.party_site_id = p_party_site_id
48 or ( ai.party_site_id is null
49 and ai.vendor_site_id in (select vendor_site_id
50 from ap_supplier_sites ass
51 where ass.party_site_id = p_party_site_id
52 and ass.vendor_id = p_vendor_id)
53 )
54 )
55 AND aps.checkrun_id IS NULL; /* Bug 11783234 Added condition to remove selected invoices from this API */
56
57 --Supplier Site level checking
58 ELSIF p_vendor_site_id IS NOT NULL THEN
59 SELECT COUNT(DISTINCT ai.invoice_id)
60 INTO l_affected_invoices
61 FROM ap_invoices_all ai, ap_payment_schedules_all aps
62 WHERE aps.external_bank_account_id = p_external_bank_account_id
63 AND ai.invoice_id = aps.invoice_id
64 -- AND ai.payment_status_flag IN ('N','P') /* Bug 11783234 Exclude 'partial paid payment schedules' status */
65 AND ai.payment_status_flag = 'N'
66 AND ai.cancelled_date IS NULL
67 AND ai.vendor_id = p_vendor_id
68 AND ai.vendor_site_id = p_vendor_site_id
69 AND aps.checkrun_id IS NULL; /* Bug 11783234 Added condition to remove selected invoices from this API */
70 --Supplier level checking
71 ELSE
72 SELECT COUNT(DISTINCT ai.invoice_id)
73 INTO l_affected_invoices
74 FROM ap_invoices_all ai, ap_payment_schedules_all aps
75 WHERE aps.external_bank_account_id = p_external_bank_account_id
76 AND ai.invoice_id = aps.invoice_id
77 -- AND ai.payment_status_flag IN ('N','P') /* Bug 11783234 Exclude 'partial paid payment schedules' status */
78 AND ai.payment_status_flag = 'N'
79 AND ai.cancelled_date IS NULL
80 AND ai.vendor_id = p_vendor_id
81 AND aps.checkrun_id IS NULL; /* Bug 11783234 Added condition to remove selected invoices from this API */
82 END IF;
83
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 l_affected_invoices := 0;
87 END;
88 RETURN l_affected_invoices;
89 END Get_Affected_Invoices_Count;
90
91 /* Bug 10634847 Added API to return selected invoices for payments count */
92 FUNCTION Get_Selected_Invoices_Count(
93 P_external_bank_account_id iby_ext_bank_accounts.ext_bank_account_id%TYPE,
94 P_vendor_id ap_suppliers.vendor_id%TYPE,
95 P_vendor_site_id ap_supplier_sites.vendor_site_id%TYPE DEFAULT NULL,
96 P_party_Site_Id ap_supplier_sites.party_site_id%TYPE DEFAULT NULL,
97 P_org_id ap_invoices.org_id%TYPE DEFAULT NULL
98 ) RETURN NUMBER IS
99 l_affected_invoices NUMBER := 0;
100
101 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
102 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
103 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP_AUTOMATIC_PROPAGATION_PKG';
104
105 l_api_name VARCHAR2(100) := 'Get_Selected_Invoices_Count';
106 l_debug_info varchar2(3000);
107
108 BEGIN
109 -- Added debug messages for bug 10634847
110 l_debug_info := 'ENTER Get_Selected_Invoices_Count';
114
111 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
112 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
113 END IF;
115 l_debug_info := 'P_org_Id :'||P_org_Id;
116 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
117 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
118 END IF;
119
120 l_debug_info := 'p_vendor_id :'||p_vendor_id;
121 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
122 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
123 END IF;
124
125
126 l_debug_info := 'P_party_Site_Id :'||P_party_Site_Id;
127 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
128 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
129 END IF;
130
131 BEGIN
132 --Address + OU level checking
133 IF (P_party_Site_Id IS NOT NULL and P_org_Id IS NOT NULL) THEN
134 SELECT COUNT(DISTINCT ai.invoice_id)
135 INTO l_affected_invoices
136 FROM ap_invoices_all ai, ap_payment_schedules_all aps
137 WHERE aps.external_bank_account_id = p_external_bank_account_id
138 AND ai.invoice_id = aps.invoice_id
139 AND ai.payment_status_flag IN ('N','P')
140 AND ai.cancelled_date IS NULL
141 AND ai.vendor_id = p_vendor_id
142 AND ai.org_id = p_org_id
143 AND (ai.party_site_id = p_party_site_id
144 or ( ai.party_site_id is null
145 and ai.vendor_site_id in (select vendor_site_id
146 from ap_supplier_sites ass
147 where ass.party_site_id = p_party_site_id
148 and ass.vendor_id = p_vendor_id)
149 )
150 )
151 AND aps.checkrun_id IS NOT NULL;
152
153 --Address level checking
154 ELSIF (P_party_Site_Id IS NOT NULL) THEN
155 SELECT COUNT(DISTINCT ai.invoice_id)
156 INTO l_affected_invoices
157 FROM ap_invoices_all ai, ap_payment_schedules_all aps
158 WHERE aps.external_bank_account_id = p_external_bank_account_id
159 AND ai.invoice_id = aps.invoice_id
160 AND ai.payment_status_flag IN ('N','P')
161 AND ai.cancelled_date IS NULL
162 AND ai.vendor_id = p_vendor_id
163 AND (ai.party_site_id = p_party_site_id
164 or ( ai.party_site_id is null
165 and ai.vendor_site_id in (select vendor_site_id
166 from ap_supplier_sites ass
167 where ass.party_site_id = p_party_site_id
168 and ass.vendor_id = p_vendor_id)
169 )
170 )
171 AND aps.checkrun_id IS NOT NULL;
172
173 --Supplier Site level checking
174 ELSIF p_vendor_site_id IS NOT NULL THEN
175 SELECT COUNT(DISTINCT ai.invoice_id)
176 INTO l_affected_invoices
177 FROM ap_invoices_all ai, ap_payment_schedules_all aps
178 WHERE aps.external_bank_account_id = p_external_bank_account_id
179 AND ai.invoice_id = aps.invoice_id
180 AND ai.payment_status_flag IN ('N','P')
181 AND ai.cancelled_date IS NULL
182 AND ai.vendor_id = p_vendor_id
183 AND ai.vendor_site_id = p_vendor_site_id
184 AND aps.checkrun_id IS NOT NULL;
185 --Supplier level checking
186 ELSE
187 SELECT COUNT(DISTINCT ai.invoice_id)
188 INTO l_affected_invoices
189 FROM ap_invoices_all ai, ap_payment_schedules_all aps
190 WHERE aps.external_bank_account_id = p_external_bank_account_id
191 AND ai.invoice_id = aps.invoice_id
192 AND ai.payment_status_flag IN ('N','P')
193 AND ai.cancelled_date IS NULL
194 AND ai.vendor_id = p_vendor_id
195 AND aps.checkrun_id IS NOT NULL;
196 END IF;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 l_affected_invoices := 0;
201 l_debug_info := 'Exception occured in Get_Selected_Invoices_Count :'||SQLERRM;
202 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
203 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
204 END IF;
205 END;
206 RETURN l_affected_invoices;
207 END Get_Selected_Invoices_Count;
208
209 PROCEDURE Update_Payment_Schedules (
210 p_from_bank_account_id iby_ext_bank_accounts.ext_bank_account_id%TYPE,
211 p_to_bank_account_id iby_ext_bank_accounts.ext_bank_account_id%TYPE,
212 p_vendor_id ap_suppliers.vendor_id%TYPE,
213 P_vendor_site_id ap_supplier_sites.vendor_site_id%TYPE DEFAULT NULL,
214 P_party_Site_Id ap_supplier_sites.party_site_id%TYPE DEFAULT NULL,
215 P_org_id ap_invoices.org_id%TYPE DEFAULT NULL,
216 P_party_id ap_suppliers.party_id%TYPE DEFAULT NULL -- Added for bug 9410719
217 ) IS
218
219 -- Added for bug 9410719
220 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
221 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
222 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'AP_AUTOMATIC_PROPAGATION_PKG';
223
224 l_api_name VARCHAR2(100) := 'Update_Payment_Schedules';
225 l_debug_info varchar2(3000);
226
227 l_to_bank_account_id iby_ext_bank_accounts.ext_bank_account_id%TYPE;
228 P_org_type IBY_EXTERNAL_PAYEES_ALL.org_type%TYPE := 'OPERATING_UNIT';
229 P_pmt_function IBY_EXTERNAL_PAYEES_ALL.payment_function%TYPE := 'PAYABLES_DISB';
230
231 BEGIN
235 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
232 -- Added debug messages for bug 9410719
233 l_debug_info := 'ENTER Update_Payment_Schedules';
234 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
236 END IF;
237
238 l_debug_info := 'P_org_Id :'||P_org_Id;
239 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
240 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
241 END IF;
242
243 l_debug_info := 'P_from_bank_account_id :'||p_from_bank_account_id;
244 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
245 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
246 END IF;
247
248 l_debug_info := 'p_vendor_id :'||p_vendor_id;
249 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
251 END IF;
252
253 l_debug_info := 'p_party_id :'||p_party_id;
254 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
255 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
256 END IF;
257
258 l_debug_info := 'P_party_Site_Id :'||P_party_Site_Id;
259 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
260 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
261 END IF;
262
263 l_debug_info := 'Calling IBY_DISBURSE_UI_API_PUB_PKG.intialize';
264 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
265 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
266 END IF;
267
268 IBY_DISBURSE_UI_API_PUB_PKG.initialize;
269
270 IF (P_party_Site_Id IS NOT NULL and P_org_Id IS NOT NULL) THEN
271 -- Added For Loop/IBY Call for bug 9410719
272 FOR x IN (SELECT aps.invoice_id, aps.payment_num, ai.payment_currency_code, ai.vendor_site_id
273 FROM ap_payment_schedules_all aps,ap_invoices_all ai
274 WHERE aps.invoice_id = ai.invoice_id
275 AND aps.external_bank_account_id = p_from_bank_account_id
276 -- Added the below two condition for the bug 10401158
277 AND aps.checkrun_id is NULL
278 AND nvl(aps.payment_status_flag , 'N') = 'N'
279 AND ai.payment_status_flag IN ('N','P')
280 AND ai.cancelled_date IS NULL
281 AND ai.vendor_id = p_vendor_id
282 AND ai.org_id = p_org_id
283 AND (ai.party_site_id = p_party_site_id
284 OR (ai.party_site_id is NULL
285 AND ai.vendor_site_id in (SELECT vendor_site_id
286 FROM ap_supplier_sites ass
287 WHERE ass.party_site_id = p_party_site_id
288 AND ass.vendor_id = p_vendor_id)
289 )
290 )
291 )
292 LOOP
293 BEGIN
294 IBY_DISBURSE_UI_API_PUB_PKG.get_default_bank_acct(x.payment_currency_code,
295 P_party_id,
296 P_party_Site_Id,
297 x.vendor_site_id,
298 P_org_id,
299 P_org_type,
300 P_pmt_function,
301 p_from_bank_account_id,
302 l_to_bank_account_id
303 );
304
305 l_debug_info := '1. l_to_bank_account_id returned :'||p_to_bank_account_id;
306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
308 END IF;
309
310
311 UPDATE ap_payment_schedules_all aps
312 SET aps.external_bank_account_id = l_to_bank_account_id,
313 last_update_date = SYSDATE,
314 last_updated_by = FND_GLOBAL.user_id,
315 last_update_login = FND_GLOBAL.login_id
316 WHERE aps.invoice_id = x.invoice_id -- Added and commented below code for bug 9410719
317 AND aps.payment_num = x.payment_num;
318
319 --Bug10065192: Update the bank at invoice header level, so recalculation of
320 --payment schedules does not reinstate the inactive bank
321 UPDATE ap_invoices_all ai
322 SET ai.external_bank_account_id = l_to_bank_account_id,
323 ai.last_update_date = SYSDATE,
324 ai.last_updated_by = FND_GLOBAL.user_id,
325 ai.last_update_login = FND_GLOBAL.login_id
326 WHERE ai.invoice_id = x.invoice_id;
327
328 /* WHERE aps.invoice_id IN
329 (SELECT DISTINCT ai.invoice_id
330 FROM ap_invoices_all ai, ap_payment_schedules_all aps
331 WHERE aps.external_bank_account_id = p_from_bank_account_id
332 AND ai.invoice_id = aps.invoice_id
333 AND ai.payment_status_flag IN ('N','P')
334 AND ai.cancelled_date IS NULL
335 AND ai.vendor_id = p_vendor_id
336 AND ai.org_id = p_org_id
337 AND (ai.party_site_id = p_party_site_id
338 or ( ai.party_site_id is null
339 and ai.vendor_site_id in (select vendor_site_id
340 from ap_supplier_sites ass
341 where ass.party_site_id = p_party_site_id
342 and ass.vendor_id = p_vendor_id)
343 )
344 )
345 );
346 */
350 l_debug_info := 'Exception occured when party_site_id and org_id is not null :'||SQLERRM;
347 -- Added Exception handling for above code for bug 9410719
348 EXCEPTION
349 WHEN OTHERS THEN
351 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
352 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
353 END IF;
354 END;
355 END LOOP;
356
357 ELSIF (P_party_Site_Id IS NOT NULL) THEN
358 -- Added For Loop/IBY Call for bug 9410719
359 FOR x IN (SELECT aps.invoice_id, aps.payment_num, ai.payment_currency_code, ai.vendor_site_id, ai.org_id
360 FROM ap_payment_schedules_all aps,ap_invoices_all ai
361 WHERE aps.invoice_id = ai.invoice_id
362 AND aps.external_bank_account_id = p_from_bank_account_id
363 -- Added the below two condition for the bug 10401158
364 AND aps.checkrun_id is NULL
365 AND nvl(aps.payment_status_flag , 'N') = 'N'
366 AND ai.payment_status_flag IN ('N','P')
367 AND ai.cancelled_date IS NULL
368 AND ai.vendor_id = p_vendor_id
369 AND (ai.party_site_id = p_party_site_id
370 OR (ai.party_site_id IS NULL
371 AND ai.vendor_site_id IN (SELECT vendor_site_id
372 FROM ap_supplier_sites ass
373 WHERE ass.party_site_id = p_party_site_id
374 AND ass.vendor_id = p_vendor_id)
375 )
376 )
377 )
378 LOOP
379 BEGIN
380 IBY_DISBURSE_UI_API_PUB_PKG.get_default_bank_acct(x.payment_currency_code,
381 P_party_id,
382 P_party_Site_Id,
383 x.vendor_site_id,
384 x.org_id,
385 P_org_type,
386 P_pmt_function,
387 p_from_bank_account_id,
388 l_to_bank_account_id
389 );
390
391 l_debug_info := '2. l_to_bank_account_id returned :'||p_to_bank_account_id;
392 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
393 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
394 END IF;
395
396 UPDATE ap_payment_schedules_all aps
397 SET aps.external_bank_account_id = l_to_bank_account_id,
398 last_update_date = SYSDATE,
399 last_updated_by = FND_GLOBAL.user_id,
400 last_update_login = FND_GLOBAL.login_id
401 WHERE aps.invoice_id = x.invoice_id -- Added and commented below code for bug 9410719
402 AND aps.payment_num = x.payment_num;
403
404 --Bug10065192: Update the bank at invoice header level, so recalculation of
405 --payment schedules does not reinstate the inactive bank
406 UPDATE ap_invoices_all ai
407 SET ai.external_bank_account_id = l_to_bank_account_id,
408 ai.last_update_date = SYSDATE,
409 ai.last_updated_by = FND_GLOBAL.user_id,
410 ai.last_update_login = FND_GLOBAL.login_id
411 WHERE ai.invoice_id = x.invoice_id;
412
413 /* WHERE aps.invoice_id IN
414 (SELECT DISTINCT ai.invoice_id
415 FROM ap_invoices_all ai, ap_payment_schedules_all aps
416 WHERE aps.external_bank_account_id = p_from_bank_account_id
417 AND ai.invoice_id = aps.invoice_id
418 AND ai.payment_status_flag IN ('N','P')
419 AND ai.cancelled_date IS NULL
420 AND ai.vendor_id = p_vendor_id
421 AND (ai.party_site_id = p_party_site_id
422 or ( ai.party_site_id is null
423 and ai.vendor_site_id in (select vendor_site_id
424 from ap_supplier_sites ass
425 where ass.party_site_id = p_party_site_id
426 and ass.vendor_id = p_vendor_id)
427 )
428 )
429 );
430 */
431 -- Added Exception handling for above code for bug 9410719
432 EXCEPTION
433 WHEN OTHERS THEN
434 l_debug_info := 'Exception occured when party_site_id is not null :'||SQLERRM;
435 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
436 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
437 END IF;
438 END;
439 END LOOP;
440
441 ELSIF (p_vendor_site_id IS NOT NULL) THEN
442 -- Added For Loop/IBY Call for bug 9410719
443 FOR x in (SELECT aps.invoice_id, aps.payment_num, ai.payment_currency_code, ai.party_site_id, ai.org_id
444 FROM ap_payment_schedules_all aps,ap_invoices_all ai
445 WHERE aps.invoice_id = ai.invoice_id
446 AND aps.external_bank_account_id = p_from_bank_account_id
447 -- Added the below two condition for the bug 10401158
448 AND aps.checkrun_id is NULL
449 AND nvl(aps.payment_status_flag , 'N') = 'N'
450 AND ai.payment_status_flag IN ('N','P')
451 AND ai.cancelled_date IS NULL
452 AND ai.vendor_id = p_vendor_id
453 AND ai.vendor_site_id = p_vendor_site_id
454 )
455 LOOP
456 BEGIN
460 P_vendor_site_id,
457 IBY_DISBURSE_UI_API_PUB_PKG.get_default_bank_acct(x.payment_currency_code,
458 P_party_id,
459 x.party_Site_Id,
461 x.org_id,
462 P_org_type,
463 P_pmt_function,
464 p_from_bank_account_id,
465 l_to_bank_account_id
466 );
467
468 l_debug_info := '3. l_to_bank_account_id returned :'||p_to_bank_account_id;
469 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
470 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
471 END IF;
472
473 UPDATE ap_payment_schedules_all aps
474 SET aps.external_bank_account_id = l_to_bank_account_id,
475 last_update_date = SYSDATE,
476 last_updated_by = FND_GLOBAL.user_id,
477 last_update_login = FND_GLOBAL.login_id
478 WHERE aps.invoice_id = x.invoice_id -- Added and commented below code for bug 9410719
479 AND aps.payment_num = x.payment_num;
480
481 --Bug10065192: Update the bank at invoice header level, so recalculation of
482 --payment schedules does not reinstate the inactive bank
483 UPDATE ap_invoices_all ai
484 SET ai.external_bank_account_id = l_to_bank_account_id,
485 ai.last_update_date = SYSDATE,
486 ai.last_updated_by = FND_GLOBAL.user_id,
487 ai.last_update_login = FND_GLOBAL.login_id
488 WHERE ai.invoice_id = x.invoice_id;
489
490 /* WHERE aps.invoice_id IN
491 (SELECT DISTINCT ai.invoice_id
492 FROM ap_invoices_all ai, ap_payment_schedules_all aps
493 WHERE aps.external_bank_account_id = p_from_bank_account_id
494 AND ai.invoice_id = aps.invoice_id
495 AND ai.payment_status_flag IN ('N','P')
496 AND ai.cancelled_date IS NULL
497 AND ai.vendor_id = p_vendor_id
498 AND ai.vendor_site_id = p_vendor_site_id);
499 */
500 -- Added Exception handling for above code for bug 9410719
501 EXCEPTION
502 WHEN OTHERS THEN
503 l_debug_info := 'Exception occured when vendor_site_id is not null :'||SQLERRM;
504 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
505 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
506 END IF;
507 END;
508 END LOOP;
509
510 ELSE
511 -- Added For Loop/IBY Call for bug 9410719
512 FOR x in (SELECT aps.invoice_id, aps.payment_num, ai.payment_currency_code, ai.party_site_id, ai.org_id, ai.vendor_site_id
513 FROM ap_payment_schedules_all aps,ap_invoices_all ai
514 WHERE aps.invoice_id = ai.invoice_id
515 AND aps.external_bank_account_id = p_from_bank_account_id
516 -- Added the below two condition for the bug 10401158
517 AND aps.checkrun_id is NULL
518 AND nvl(aps.payment_status_flag , 'N') = 'N'
519 AND ai.payment_status_flag IN ('N','P')
520 AND ai.cancelled_date IS NULL
521 AND ai.vendor_id = p_vendor_id
522 )
523 LOOP
524 BEGIN
525 IBY_DISBURSE_UI_API_PUB_PKG.get_default_bank_acct(x.payment_currency_code,
526 P_party_id,
527 x.party_Site_Id,
528 x.vendor_site_id,
529 x.org_id,
530 P_org_type,
531 P_pmt_function,
532 p_from_bank_account_id,
533 l_to_bank_account_id
534 );
535
536 l_debug_info := '4. l_to_bank_account_id returned :'||p_to_bank_account_id;
537 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
538 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
539 END IF;
540
541
542 UPDATE ap_payment_schedules_all aps
543 SET aps.external_bank_account_id = l_to_bank_account_id,
544 last_update_date = SYSDATE,
545 last_updated_by = FND_GLOBAL.user_id,
546 last_update_login = FND_GLOBAL.login_id
547 WHERE aps.invoice_id = x.invoice_id -- Added and commented below code for bug 9410719
548 AND aps.payment_num = x.payment_num;
549
550 --Bug10065192: Update the bank at invoice header level, so recalculation of
551 --payment schedules does not reinstate the inactive bank
552 UPDATE ap_invoices_all ai
553 SET ai.external_bank_account_id = l_to_bank_account_id,
554 ai.last_update_date = SYSDATE,
555 ai.last_updated_by = FND_GLOBAL.user_id,
556 ai.last_update_login = FND_GLOBAL.login_id
557 WHERE ai.invoice_id = x.invoice_id;
558
559 /* WHERE aps.invoice_id IN
560 (SELECT DISTINCT ai.invoice_id
561 FROM ap_invoices_all ai, ap_payment_schedules_all aps
562 WHERE aps.external_bank_account_id = p_from_bank_account_id
563 AND ai.invoice_id = aps.invoice_id
564 AND ai.payment_status_flag IN ('N','P')
565 AND ai.cancelled_date IS NULL
566 AND ai.vendor_id = p_vendor_id)
567 */
568 EXCEPTION
569 WHEN OTHERS THEN
570 l_debug_info := 'Exception occured when vendor_id is not null :'||SQLERRM;
571 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
572 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
573 END IF;
574 END;
575 END LOOP;
576 END IF;
577 EXCEPTION WHEN OTHERS THEN
578 l_debug_info := 'Exception occured :'||SQLERRM;
579 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
580 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
581 END IF;
582
583 END Update_Payment_Schedules;
584
585 END AP_AUTOMATIC_PROPAGATION_PKG;
[Home] [Help]