[Home] [Help]
PACKAGE BODY: APPS.IBY_SUPP_BANK_MERGE_PUB
Source
1 PACKAGE BODY IBY_SUPP_BANK_MERGE_PUB AS
2 /* $Header: ibybnkmergb.pls 120.4.12020000.2 2012/09/07 08:57:57 asarada ship $ */
3 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
5
6 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_SUPP_BANK_MERGE_PUB';
7
8 PROCEDURE MERGE_BANKS (
9 from_instr_pmt_use_list IN Id_Tab_Type,
10 from_ext_payee_id_list IN Id_Tab_Type,
11 from_bank_account_id_list IN Id_Tab_Type,
12 to_instr_pmt_use_list IN Id_Tab_Type,
13 to_ext_payee_id_list IN Id_Tab_Type,
14 to_bank_account_id_list IN Id_Tab_Type,
15 p_to_ext_payee_id IN NUMBER,
16 p_from_party_id IN NUMBER,
17 P_to_party_id IN NUMBER,
18 p_level IN VARCHAR2,
19 p_last_site_flag IN VARCHAR2,
20 X_return_status IN OUT NOCOPY VARCHAR2,
21 X_msg_count IN OUT NOCOPY NUMBER,
22 X_msg_data IN OUT NOCOPY VARCHAR2
23 ) IS
24
25 CURSOR get_from_instr_use_dtls (cp_instrument_payment_use_id IN NUMBER) IS
26 SELECT *
27 FROM iby_pmt_instr_uses_all
28 WHERE instrument_payment_use_id = cp_instrument_payment_use_id;
29
30 CURSOR max_order_of_pref (cp_ext_payee_id IN NUMBER) IS
31 SELECT MAX(order_of_preference)
32 FROM iby_pmt_instr_uses_all
33 WHERE ext_pmt_party_id = cp_ext_payee_id;
34
35 CURSOR cur_is_ownership_exists(cp_to_party_id IN NUMBER, cp_bank_act_id IN NUMBER) IS
36 SELECT 'Y'
37 FROM iby_account_owners
38 WHERE account_owner_party_id = cp_to_party_id
39 AND ext_bank_account_id = cp_bank_act_id
40 AND NVL(end_date,SYSDATE) > = SYSDATE;
41
42 CURSOR cur_get_primary_ownership (cp_from_party_id IN NUMBER, cp_bank_act_id IN NUMBER) IS
43 SELECT Primary_flag
44 FROM iby_account_owners
45 WHERE ext_bank_account_id = cp_bank_act_id
46 AND account_owner_party_id = cp_from_party_id
47 AND NVL (end_date, Sysdate) >= Sysdate;
48
49 rec_get_from_instr_use_dtls iby_pmt_instr_uses_all%ROWTYPE;
50
51 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.BANK_ACCOUNTS_MERGE(MERGE_BANKS)';
52 g_mesg VARCHAR2(1000) := '';
53 L_BANK_ACCOUNT_EXITS BOOLEAN;
54 l_max_order_of_pref NUMBER;
55 l_is_ownership_exist VARCHAR2(1);
56 l_is_primary VARCHAR2(1);
57
58 BEGIN
59 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
60 g_mesg := ' Start MERGE_BANKS ' ;
61 fnd_file.put_line(fnd_file.log, g_mesg);
62 END IF;
63 -- For all Bank Accounts for From supplier
64 IF (from_bank_account_id_list.COUNT > 0) THEN
65 FOR I IN from_bank_account_id_list.FIRST..from_bank_account_id_list.LAST
66 LOOP
67 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
68 g_mesg := ' Start Processing Bank Account Id => '|| from_bank_account_id_list(I) ;
69 fnd_file.put_line(fnd_file.log, g_mesg);
70 END IF;
71 l_bank_account_exits := FALSE;
72 l_max_order_of_pref := 0;
73 l_is_primary := 'N';
74 l_is_ownership_exist := NULL;
75
76 -- Check whether bank account is already associated to to-Supplier
77 IF (to_bank_account_id_list.COUNT > 0) THEN
78 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
79 g_mesg := ' Check whether the bank account is already existing for to-supplier ' ;
80 fnd_file.put_line(fnd_file.log, g_mesg);
81 END IF;
82
83 FOR J IN to_bank_account_id_list.FIRST..to_bank_account_id_list.LAST
84 LOOP
85 IF (from_bank_account_id_list(I) = to_bank_account_id_list(J)) THEN
86 l_bank_account_exits := TRUE;
87 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
88 g_mesg := ' Bank Account exist for Supplier2' ;
89 fnd_file.put_line(fnd_file.log, g_mesg);
90 END IF;
91 END IF;
92 END LOOP;
93 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
94 g_mesg := ' End of To Bank account id list Loop' ;
95 fnd_file.put_line(fnd_file.log, g_mesg);
96 END IF;
97 OPEN max_order_of_pref(p_to_ext_payee_id);
98 FETCH max_order_of_pref INTO l_max_order_of_pref;
99 CLOSE max_order_of_pref;
100 END IF;
101
102 -- If Bank account is not associated to To-Supplier
103 IF NOT l_bank_account_exits THEN
104 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
105 g_mesg := ' Bank Account Does not exist for Supplier2 ' ;
106 fnd_file.put_line(fnd_file.log, g_mesg);
107 END IF;
108
109 OPEN get_from_instr_use_dtls(from_instr_pmt_use_list(i));
110 FETCH get_from_instr_use_dtls INTO rec_get_from_instr_use_dtls;
111 CLOSE get_from_instr_use_dtls;
112 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
113 g_mesg := ' Inserting record into Instruments l_max_order_of_pref => '|| l_max_order_of_pref ;
114 fnd_file.put_line(fnd_file.log, g_mesg);
115 END IF;
116 INSERT INTO iby_pmt_instr_uses_all(
117 INSTRUMENT_PAYMENT_USE_ID,
118 PAYMENT_FLOW ,
119 EXT_PMT_PARTY_ID ,
120 INSTRUMENT_TYPE ,
121 INSTRUMENT_ID ,
122 PAYMENT_FUNCTION ,
123 ORDER_OF_PREFERENCE ,
124 CREATED_BY ,
125 CREATION_DATE ,
126 LAST_UPDATED_BY ,
127 LAST_UPDATE_DATE ,
128 LAST_UPDATE_LOGIN ,
129 OBJECT_VERSION_NUMBER ,
130 START_DATE ,
131 END_DATE ,
132 DEBIT_AUTH_FLAG ,
133 DEBIT_AUTH_METHOD ,
134 DEBIT_AUTH_REFERENCE ,
135 DEBIT_AUTH_BEGIN ,
136 DEBIT_AUTH_END ,
137 ATTRIBUTE_CATEGORY ,
138 ATTRIBUTE1 ,
139 ATTRIBUTE2 ,
140 ATTRIBUTE3 ,
141 ATTRIBUTE4 ,
142 ATTRIBUTE5 ,
143 ATTRIBUTE6 ,
144 ATTRIBUTE7 ,
145 ATTRIBUTE8 ,
146 ATTRIBUTE9 ,
147 ATTRIBUTE10 ,
148 ATTRIBUTE11 ,
149 ATTRIBUTE12 ,
150 ATTRIBUTE13 ,
151 ATTRIBUTE14 ,
152 ATTRIBUTE15
153 )
154 VALUES(
155 IBY_PMT_INSTR_USES_ALL_S.NEXTVAL ,
156 rec_get_from_instr_use_dtls.PAYMENT_FLOW ,
157 p_to_ext_payee_id ,
158 rec_get_from_instr_use_dtls.INSTRUMENT_TYPE ,
159 rec_get_from_instr_use_dtls.INSTRUMENT_ID ,
160 rec_get_from_instr_use_dtls.PAYMENT_FUNCTION ,
161 DECODE(l_max_order_of_pref, 0, rec_get_from_instr_use_dtls.order_of_preference,l_max_order_of_pref+1),
162 hz_utility_pub.created_by ,
163 hz_utility_pub.CREATION_DATE ,
164 hz_utility_pub.LAST_UPDATED_BY ,
165 hz_utility_pub.LAST_UPDATE_DATE ,
166 hz_utility_pub.LAST_UPDATE_LOGIN ,
167 1 ,
168 rec_get_from_instr_use_dtls.START_DATE ,
169 rec_get_from_instr_use_dtls.END_DATE ,
170 rec_get_from_instr_use_dtls.DEBIT_AUTH_FLAG ,
171 rec_get_from_instr_use_dtls.DEBIT_AUTH_METHOD ,
172 rec_get_from_instr_use_dtls.DEBIT_AUTH_REFERENCE ,
173 rec_get_from_instr_use_dtls.DEBIT_AUTH_BEGIN ,
174 rec_get_from_instr_use_dtls.DEBIT_AUTH_END ,
175 rec_get_from_instr_use_dtls.ATTRIBUTE_CATEGORY ,
176 rec_get_from_instr_use_dtls.ATTRIBUTE1 ,
177 rec_get_from_instr_use_dtls.ATTRIBUTE2 ,
178 rec_get_from_instr_use_dtls.ATTRIBUTE3 ,
179 rec_get_from_instr_use_dtls.ATTRIBUTE4 ,
180 rec_get_from_instr_use_dtls.ATTRIBUTE5 ,
181 rec_get_from_instr_use_dtls.ATTRIBUTE6 ,
182 rec_get_from_instr_use_dtls.ATTRIBUTE7 ,
183 rec_get_from_instr_use_dtls.ATTRIBUTE8 ,
184 rec_get_from_instr_use_dtls.ATTRIBUTE9 ,
185 rec_get_from_instr_use_dtls.ATTRIBUTE10 ,
186 rec_get_from_instr_use_dtls.ATTRIBUTE11 ,
187 rec_get_from_instr_use_dtls.ATTRIBUTE12 ,
188 rec_get_from_instr_use_dtls.ATTRIBUTE13 ,
189 rec_get_from_instr_use_dtls.ATTRIBUTE14 ,
190 rec_get_from_instr_use_dtls.ATTRIBUTE15
191 );
192
193 -- Check whether bank account , To-Supplier ownership exists.
194 OPEN cur_is_ownership_exists(P_to_party_id, from_bank_account_id_list(I));
195 FETCH cur_is_ownership_exists INTO l_is_ownership_exist;
196 CLOSE cur_is_ownership_exists;
197
198 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
199 g_mesg := ' Processing Bank Account Ownership';
200 fnd_file.put_line(fnd_file.log, g_mesg);
201 END IF;
202
203 IF p_last_site_flag = 'Y' THEN
204 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
205 g_mesg := ' p_last_site_flag := Y ' ;
206 fnd_file.put_line(fnd_file.log, g_mesg);
207 END IF;
208
209 OPEN cur_get_primary_ownership(p_from_party_id, from_bank_account_id_list(I));
210 FETCH cur_get_primary_ownership INTO l_is_primary;
211 CLOSE cur_get_primary_ownership;
212
213 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
214 g_mesg := ' Is From Supplier Primary? '|| l_is_primary;
215 fnd_file.put_line(fnd_file.log, g_mesg);
216 g_mesg := ' Is To Supplier Ownership Exists? '|| l_is_ownership_exist;
217 fnd_file.put_line(fnd_file.log, g_mesg);
218 END IF;
219
220
221 IF l_is_primary = 'Y' AND l_is_ownership_exist IS NOT NULL THEN
222 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
223 g_mesg := ' From Supplier is Primary and To Supplier Ownership Exists ' ;
224 fnd_file.put_line(fnd_file.log, g_mesg);
225 END IF;
226
227 UPDATE iby_account_owners
228 SET primary_flag = 'N'
229 WHERE ext_bank_account_id = from_bank_account_id_list(I)
230 AND account_owner_party_id = p_from_party_id;
231
232 UPDATE iby_account_owners
233 SET primary_flag = 'Y'
234 WHERE ext_bank_account_id = from_bank_account_id_list(I)
235 AND account_owner_party_id = P_to_party_id;
236
237 ELSIF l_is_primary = 'Y' AND l_is_ownership_exist IS NULL THEN
238 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
239 g_mesg := ' From Supplier is Primary and To Supplier Ownership Does not Exist ' ;
240 fnd_file.put_line(fnd_file.log, g_mesg);
241 END IF;
242
243 UPDATE iby_account_owners
244 SET primary_flag = 'N'
245 WHERE ext_bank_account_id = from_bank_account_id_list(I)
246 AND account_owner_party_id = p_from_party_id;
247
248 INSERT INTO iby_account_owners(
249 ACCOUNT_OWNER_ID ,
250 EXT_BANK_ACCOUNT_ID ,
251 ACCOUNT_OWNER_PARTY_ID ,
252 END_DATE ,
253 PRIMARY_FLAG ,
254 CREATED_BY ,
255 CREATION_DATE ,
256 LAST_UPDATED_BY ,
257 LAST_UPDATE_DATE ,
258 LAST_UPDATE_LOGIN ,
259 OBJECT_VERSION_NUMBER
260 )VALUES(
261 IBY_ACCOUNT_OWNERS_S.NEXTVAL ,
262 from_bank_account_id_list(I) ,
263 P_to_party_id ,
264 NULL ,
265 'Y' ,
266 hz_utility_pub.CREATED_BY ,
267 hz_utility_pub.CREATION_DATE ,
268 hz_utility_pub.LAST_UPDATED_BY ,
269 hz_utility_pub.LAST_UPDATE_DATE ,
270 hz_utility_pub.LAST_UPDATE_LOGIN ,
271 1
272 );
273 ELSIF l_is_primary = 'N' AND l_is_ownership_exist IS NULL THEN
274 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
275 g_mesg := ' From Supplier is non Primary and To Supplier Ownership Does not Exist ' ;
276 fnd_file.put_line(fnd_file.log, g_mesg);
277 END IF;
278
279 INSERT INTO iby_account_owners(
280 ACCOUNT_OWNER_ID ,
281 EXT_BANK_ACCOUNT_ID ,
282 ACCOUNT_OWNER_PARTY_ID ,
283 END_DATE ,
284 PRIMARY_FLAG ,
285 CREATED_BY ,
286 CREATION_DATE ,
287 LAST_UPDATED_BY ,
288 LAST_UPDATE_DATE ,
289 LAST_UPDATE_LOGIN ,
290 OBJECT_VERSION_NUMBER
291 )VALUES(
292 IBY_ACCOUNT_OWNERS_S.NEXTVAL ,
293 from_bank_account_id_list(I) ,
294 P_to_party_id ,
295 NULL ,
296 'N' ,
297 hz_utility_pub.CREATED_BY ,
298 hz_utility_pub.CREATION_DATE ,
299 hz_utility_pub.LAST_UPDATED_BY ,
300 hz_utility_pub.LAST_UPDATE_DATE ,
301 hz_utility_pub.LAST_UPDATE_LOGIN ,
302 1
303 );
304 END IF;
305 ELSIF l_is_ownership_exist IS NULL THEN
306 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
307 g_mesg := ' To Supplier Ownership Does not Exist ' ;
308 fnd_file.put_line(fnd_file.log, g_mesg);
309 END IF;
310
311 INSERT INTO iby_account_owners(
312 ACCOUNT_OWNER_ID ,
313 EXT_BANK_ACCOUNT_ID ,
314 ACCOUNT_OWNER_PARTY_ID ,
315 END_DATE ,
316 PRIMARY_FLAG ,
317 CREATED_BY ,
318 CREATION_DATE ,
319 LAST_UPDATED_BY ,
320 LAST_UPDATE_DATE ,
321 LAST_UPDATE_LOGIN ,
322 OBJECT_VERSION_NUMBER
323 )VALUES(
324 IBY_ACCOUNT_OWNERS_S.NEXTVAL ,
325 from_bank_account_id_list(I) ,
326 P_to_party_id ,
327 NULL ,
328 'N' ,
329 hz_utility_pub.CREATED_BY ,
330 hz_utility_pub.CREATION_DATE ,
331 hz_utility_pub.LAST_UPDATED_BY ,
332 hz_utility_pub.LAST_UPDATE_DATE ,
333 hz_utility_pub.LAST_UPDATE_LOGIN ,
334 1
335 );
336 END IF;
337 END IF;
338 IF p_level = 'SITE' THEN
339 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
340 g_mesg := ' Inactivating instruments at site level';
341 fnd_file.put_line(fnd_file.log, g_mesg);
342 END IF;
343
344 UPDATE iby_pmt_instr_uses_all
345 SET end_date = SYSDATE
346 WHERE instrument_payment_use_id = from_instr_pmt_use_list(I)
347 AND NVL(end_date,SYSDATE) >= SYSDATE/*Bug 14587066 */;
348
349 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
350 g_mesg := ' End Processing for Bank Account Id => '||from_bank_account_id_list(I);
351 fnd_file.put_line(fnd_file.log, g_mesg);
352 END IF;
353 END IF;
354 END LOOP;
355 END IF;
356 EXCEPTION
357 WHEN OTHERS THEN
358
359 g_mesg :='Unexpected error: ' || SQLERRM;
360 fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
361
362 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
363 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
364 FND_MSG_PUB.ADD;
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366 END MERGE_BANKS;
367
368 PROCEDURE INSERT_PAYEE_ROW(ext_payee_rec IN IBY_EXTERNAL_PAYEES_ALL%ROWTYPE,
369 P_EXT_PAYEE_ID IN OUT NOCOPY NUMBER,
370 x_return_status OUT NOCOPY VARCHAR2 ,
371 X_MSG_COUNT IN OUT NOCOPY NUMBER,
372 X_MSG_DATA OUT NOCOPY VARCHAR2)
373 IS
374
375 CURSOR get_next_ext_payee_id IS
376 SELECT IBY_EXTERNAL_PAYEES_ALL_S.NEXTVAL
377 FROM DUAL;
378
379 BEGIN
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 OPEN get_next_ext_payee_id;
383 FETCH get_next_ext_payee_id INTO P_EXT_PAYEE_ID;
384 CLOSE get_next_ext_payee_id;
385
386 INSERT INTO IBY_EXTERNAL_PAYEES_ALL(
387 EXT_PAYEE_ID,
388 PAYEE_PARTY_ID,
389 PAYMENT_FUNCTION,
390 EXCLUSIVE_PAYMENT_FLAG,
391 CREATED_BY,
392 CREATION_DATE,
393 LAST_UPDATED_BY,
394 LAST_UPDATE_DATE,
395 LAST_UPDATE_LOGIN,
396 OBJECT_VERSION_NUMBER,
397 PARTY_SITE_ID,
398 SUPPLIER_SITE_ID,
399 ORG_ID,
400 ORG_TYPE,
401 DEFAULT_PAYMENT_METHOD_CODE,
402 ECE_TP_LOCATION_CODE,
403 BANK_CHARGE_BEARER,
404 BANK_INSTRUCTION1_CODE,
405 BANK_INSTRUCTION2_CODE,
406 BANK_INSTRUCTION_DETAILS,
407 PAYMENT_REASON_CODE,
408 PAYMENT_REASON_COMMENTS,
409 INACTIVE_DATE,
410 PAYMENT_TEXT_MESSAGE1,
411 PAYMENT_TEXT_MESSAGE2,
412 PAYMENT_TEXT_MESSAGE3,
413 DELIVERY_CHANNEL_CODE,
414 PAYMENT_FORMAT_CODE,
415 SETTLEMENT_PRIORITY,
416 REMIT_ADVICE_DELIVERY_METHOD,
417 REMIT_ADVICE_EMAIL,
418 REMIT_ADVICE_FAX)
419 VALUES (
420 p_ext_payee_id,
421 ext_payee_rec.Payee_Party_Id,
422 ext_payee_rec.Payment_Function,
423 ext_payee_rec.EXCLUSIVE_PAYMENT_FLAG,
424 hz_utility_pub.CREATED_BY,
425 hz_utility_pub.CREATION_DATE,
426 hz_utility_pub.LAST_UPDATED_BY,
427 hz_utility_pub.LAST_UPDATE_DATE,
428 hz_utility_pub.LAST_UPDATE_LOGIN,
429 1.0,
430 ext_payee_rec.PARTY_SITE_ID,
431 ext_payee_rec.Supplier_Site_Id,
432 ext_payee_rec.Org_Id,
433 ext_payee_rec.ORG_TYPE,
434 ext_payee_rec.DEFAULT_PAYMENT_METHOD_CODE,
435 ext_payee_rec.ECE_TP_LOCATION_CODE,
436 ext_payee_rec.Bank_Charge_Bearer,
437 ext_payee_rec.BANK_INSTRUCTION1_CODE,
438 ext_payee_rec.BANK_INSTRUCTION2_CODE,
439 ext_payee_rec.BANK_INSTRUCTION_DETAILS,
440 ext_payee_rec.PAYMENT_REASON_CODE,
441 ext_payee_rec.PAYMENT_REASON_COMMENTS,
442 ext_payee_rec.Inactive_Date,
443 ext_payee_rec.PAYMENT_TEXT_MESSAGE1,
444 ext_payee_rec.PAYMENT_TEXT_MESSAGE2,
445 ext_payee_rec.PAYMENT_TEXT_MESSAGE3,
446 ext_payee_rec.DELIVERY_CHANNEL_CODE,
447 ext_payee_rec.PAYMENT_FORMAT_CODE,
448 ext_payee_rec.Settlement_Priority,
449 ext_payee_rec.REMIT_ADVICE_DELIVERY_METHOD,
450 ext_payee_rec.REMIT_ADVICE_EMAIL,
451 ext_payee_rec.remit_advice_fax);
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 fnd_file.put_line(fnd_file.log, 'Exception: insert_payee_row: '||SQLERRM);
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457 X_MSG_DATA := 'Exception: insert_payee_row: '||SQLERRM;
458
459 END INSERT_PAYEE_ROW;
460
461 PROCEDURE CREATE_EXTERNAL_PAYEE(
462 P_SITE_EXT_PAYEE_ID IN NUMBER,
463 P_LEVEL IN VARCHAR2,
464 P_EXT_PAYEE_ID IN OUT NOCOPY NUMBER,
465 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2,
466 X_MSG_COUNT IN OUT NOCOPY NUMBER,
467 X_MSG_DATA IN OUT NOCOPY VARCHAR2
468 )IS
469
470 CURSOR cur_get_payee_dtls (cp_ext_payee_id IN NUMBER) IS
471 SELECT *
472 FROM IBY_EXTERNAL_PAYEES_ALL
473 WHERE ext_payee_id = cp_ext_payee_id;
474
475 ext_payee_tab IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Tab_Type;
476 ext_payee_id_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Id_Tab_Type;
477 ext_payee_create_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Tab_Type;
478
479 l_Disb_payee_rec IBY_DISBURSEMENT_SETUP_PUB.EXTERNAL_PAYEE_REC_TYPE;
480 l_Ext_payee_rec IBY_EXTERNAL_PAYEES_ALL%ROWTYPE;
481
482 l_pay_return_status VARCHAR2(50);
483 l_pay_msg_count NUMBER;
484 l_pay_msg_data VARCHAR2(1000);
485 g_mesg VARCHAR2(1000) := '';
486
487 BEGIN
488 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
489
490 OPEN cur_get_payee_dtls(P_SITE_EXT_PAYEE_ID);
491 FETCH cur_get_payee_dtls INTO l_Ext_payee_rec;
492 CLOSE cur_get_payee_dtls;
493
494 IF P_LEVEL = 'ADDRESS_OU' THEN
495 -- Address_OU Record
496 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
497 g_mesg := 'Creating External Payee Record at Address-OU level';
498 fnd_file.put_line(fnd_file.log, g_mesg);
499 END IF;
500 l_Ext_payee_rec.supplier_site_id := NULL;
501 insert_payee_row(l_Ext_payee_rec, P_EXT_PAYEE_ID, x_return_status, X_MSG_COUNT, X_MSG_DATA);
502 ELSIF P_LEVEL = 'ADDRESS' THEN
503 -- Address Record
504 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
505 g_mesg := 'Creating External Payee Record at Address level';
506 fnd_file.put_line(fnd_file.log, g_mesg);
507 END IF;
508 l_Ext_payee_rec.supplier_site_id := NULL;
509 l_Ext_payee_rec.Org_Id := NULL;
510 l_Ext_payee_rec.Org_Type := NULL;
511 insert_payee_row(l_Ext_payee_rec, P_EXT_PAYEE_ID, x_return_status, X_MSG_COUNT, X_MSG_DATA);
512 ELSIF P_LEVEL = 'SUPPLIER' THEN
513 -- Supplier Record
514 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
515 g_mesg := 'Creating External Payee Record at Supplier level';
516 fnd_file.put_line(fnd_file.log, g_mesg);
517 END IF;
518 l_Ext_payee_rec.supplier_site_id := NULL;
519 l_Ext_payee_rec.Org_Id := NULL;
520 l_Ext_payee_rec.Org_Type := NULL;
521 l_Ext_payee_rec.PARTY_SITE_ID := NULL;
522 insert_payee_row(l_Ext_payee_rec, P_EXT_PAYEE_ID, x_return_status, X_MSG_COUNT, X_MSG_DATA);
523 END IF;
524 END CREATE_EXTERNAL_PAYEE;
525
526 PROCEDURE BANK_ACCOUNTS_MERGE (
527 P_from_vendor_id IN NUMBER,
528 P_to_vendor_id IN NUMBER,
529 P_from_party_id IN NUMBER,
530 P_to_party_id IN NUMBER,
531 P_from_vendor_site_id IN NUMBER,
532 P_to_vendor_site_id IN NUMBER,
533 P_from_party_site_id IN NUMBER,
534 P_to_partysite_id IN NUMBER,
535 P_from_org_id IN NUMBER,
536 P_to_org_id IN NUMBER,
537 P_from_org_type IN VARCHAR2,
538 P_to_org_type IN VARCHAR2,
539 p_keep_site_flag IN VARCHAR2,
540 p_last_site_flag IN VARCHAR2,
541 X_return_status IN OUT NOCOPY VARCHAR2,
542 X_msg_count IN OUT NOCOPY NUMBER,
543 X_msg_data IN OUT NOCOPY VARCHAR2
544 ) IS
545
546 --Get Active Bank Accounts assigned to Supplier 1
547 CURSOR get_supplier_site_bank_dtls(cp_party_id IN NUMBER, cp_vendor_site_id IN NUMBER, cp_party_site_id IN NUMBER, cp_org_id IN NUMBER, cp_org_type IN VARCHAR2) IS
548 SELECT
549 Uses.instrument_payment_use_id ,
550 Payee.ext_payee_id,
551 Uses.instrument_id bank_account_id
552 FROM
553 Iby_pmt_instr_uses_all uses,
554 Iby_external_payees_all payee
555 WHERE
556 Payee.ext_payee_id = uses.ext_pmt_party_id
557 AND uses.payment_function = 'PAYABLES_DISB'
558 AND uses.payment_flow = 'DISBURSEMENTS'
559 AND uses.instrument_type = 'BANKACCOUNT'
560 AND payee.payee_party_id = cp_party_id
561 AND (((payee.supplier_site_id IS NULL) AND (cp_vendor_site_id IS NULL)) OR (payee.supplier_site_id = cp_vendor_site_id))
562 AND (((payee.party_site_id IS NULL) AND (cp_party_site_id IS NULL)) OR (payee.party_site_id = cp_party_site_id))
563 AND (((payee.org_id IS NULL) AND (cp_org_id IS NULL)) OR (payee.org_id = cp_org_id))
564 AND (((payee.org_type IS NULL) AND (cp_org_type IS NULL)) OR (payee.org_type = cp_org_type));
565
566 CURSOR cur_get_to_party_site_id(cp_vendor_site_id IN NUMBER) IS
567 SELECT party_site_id
568 FROM ap_supplier_sites_all
569 WHERE vendor_site_id = cp_vendor_site_id;
570
571 CURSOR cur_get_ext_payee_id (cp_party_id IN NUMBER, cp_vendor_site_id IN NUMBER, cp_party_site_id IN NUMBER, cp_org_id IN NUMBER, cp_org_type IN VARCHAR2) IS
572 SELECT ext_payee_id
573 FROM Iby_external_payees_all payee
574 WHERE payee.payee_party_id = cp_party_id
575 AND (((payee.supplier_site_id IS NULL) AND (cp_vendor_site_id IS NULL)) OR (payee.supplier_site_id = cp_vendor_site_id))
576 AND (((payee.party_site_id IS NULL) AND (cp_party_site_id IS NULL)) OR (payee.party_site_id = cp_party_site_id))
577 AND (((payee.org_id IS NULL) AND (cp_org_id IS NULL)) OR (payee.org_id = cp_org_id))
578 AND (((payee.org_type IS NULL) AND (cp_org_type IS NULL)) OR (payee.org_type = cp_org_type));
579
580 from_instr_pmt_use_list Id_Tab_Type;
581 from_bank_account_id_list Id_Tab_Type;
582 from_ext_payee_id_list Id_Tab_Type;
583
584 to_instr_pmt_use_list Id_Tab_Type;
585 to_bank_account_id_list Id_Tab_Type;
586 to_ext_payee_id_list Id_Tab_Type;
587
588 rec_get_from_instr_use_dtls iby_pmt_instr_uses_all%ROWTYPE;
589
590 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.BANK_ACCOUNTS_MERGE';
591 g_mesg VARCHAR2(1000) := '';
592 L_BANK_ACCOUNT_EXITS BOOLEAN;
593 l_max_order_of_pref NUMBER;
594 l_is_ownership_exist VARCHAR2(1);
595 l_to_ext_payee_id NUMBER;
596 L_TO_PARTY_SITE_ID NUMBER;
597 l_site_payee_id NUMBER;
598
599 BEGIN
600 X_return_status := FND_API.G_RET_STS_SUCCESS;
601
602 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
603 -- Log All Parameters
604 g_mesg := 'Entering IBY_SUPP_BANK_MERGE.BANK_ACCOUNTS_MERGE ';
605 fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
606
607 g_mesg := 'P_FROM_VENDOR_ID : '|| p_from_vendor_id;
608 fnd_file.put_line(fnd_file.log, g_mesg );
609
610 g_mesg := 'P_FROM_VENDOR_ID => '||P_from_vendor_id;
611 fnd_file.put_line(fnd_file.log, g_mesg);
612
613 g_mesg := 'P_TO_VENDOR_ID => '||P_to_vendor_id ;
614 fnd_file.put_line(fnd_file.log, g_mesg);
615
616 g_mesg := 'P_FROM_PARTY_ID => '|| P_from_party_id;
617 fnd_file.put_line(fnd_file.log, g_mesg);
618
619 g_mesg := 'P_TO_PARTY_ID => '|| P_to_party_id ;
620 fnd_file.put_line(fnd_file.log, g_mesg);
621
622 g_mesg := 'P_FROM_VENDOR_SITE_ID => '|| P_from_vendor_site_id ;
623 fnd_file.put_line(fnd_file.log, g_mesg);
624
625 g_mesg := 'P_TO_VENDOR_SITE_ID => '|| P_to_vendor_site_id ;
626 fnd_file.put_line(fnd_file.log, g_mesg);
627
628 g_mesg := 'P_FROM_PARTY_SITE_ID => '|| P_from_party_site_id ;
629 fnd_file.put_line(fnd_file.log, g_mesg);
630
631 g_mesg := 'P_TO_PARTYSITE_ID => '|| P_to_partysite_id ;
632 fnd_file.put_line(fnd_file.log, g_mesg);
633
634 g_mesg := 'P_FROM_ORG_ID => '|| P_from_org_id ;
635 fnd_file.put_line(fnd_file.log, g_mesg);
636
637 g_mesg := 'P_TO_ORG_ID => '|| P_to_org_id ;
638 fnd_file.put_line(fnd_file.log, g_mesg);
639
640 g_mesg := 'P_FROM_ORG_TYPE => '||P_from_org_type ;
641 fnd_file.put_line(fnd_file.log, g_mesg);
642
643 g_mesg := 'P_TO_ORG_TYPE => '||P_to_org_type ;
644 fnd_file.put_line(fnd_file.log, g_mesg);
645
646 g_mesg := 'P_KEEP_SITE_FLAG => '||p_keep_site_flag ;
647 fnd_file.put_line(fnd_file.log, g_mesg);
648
649 g_mesg := 'P_LAST_SITE_FLAG => '||p_keep_site_flag ;
650 fnd_file.put_line(fnd_file.log, g_mesg);
651
652 --Merge Bank Accounts at Supplier Site level
653 g_mesg := 'Start Merging Bank Accounts at Suppier Site Level';
654 fnd_file.put_line(fnd_file.log, g_mesg);
655 END IF;
656 -- Incase of COPY, new party site is created. Get the new party site id associated to the To Supplier Site.
657 IF p_keep_site_flag = 'Y' THEN
658 OPEN cur_get_to_party_site_id(P_to_vendor_site_id);
659 FETCH cur_get_to_party_site_id INTO l_to_party_site_id;
660 CLOSE cur_get_to_party_site_id;
661 ELSE
662 l_to_party_site_id := p_to_partysite_id;
663 END IF;
664
665 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
666 g_mesg := 'l_to_party_site_id: '||l_to_party_site_id;
667 fnd_file.put_line(fnd_file.log, g_mesg);
668 END IF;
669
670 -- Get the From Supplier Bank Accounts at Supplier Site Level
671 OPEN get_supplier_site_bank_dtls(P_from_party_id, P_from_vendor_site_id, P_from_party_site_id, P_from_org_id, P_from_org_type);
672 FETCH get_supplier_site_bank_dtls BULK COLLECT INTO from_instr_pmt_use_list, from_ext_payee_id_list, from_bank_account_id_list;
673 CLOSE get_supplier_site_bank_dtls;
674
675 -- Get the To Supplier Bank Accounts at Supplier Site Level
676 OPEN get_supplier_site_bank_dtls(P_to_party_id, P_to_vendor_site_id, l_to_party_site_id, P_to_org_id, P_to_org_type);
677 FETCH get_supplier_site_bank_dtls BULK COLLECT INTO to_instr_pmt_use_list, to_ext_payee_id_list, to_bank_account_id_list;
678 CLOSE get_supplier_site_bank_dtls;
679
680 -- Get the To External Payee ID at Supplier Site Level. Incase of Copy this record is created by AP.
681 OPEN cur_get_ext_payee_id(P_to_party_id, P_to_vendor_site_id, l_to_party_site_id, P_to_org_id, P_to_org_type);
682 FETCH cur_get_ext_payee_id INTO l_to_ext_payee_id;
683 CLOSE cur_get_ext_payee_id;
684
685 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
686 g_mesg := 'Payee ID => '||l_to_ext_payee_id ;
687 fnd_file.put_line(fnd_file.log, g_mesg);
688 END IF;
689
690 l_site_payee_id := l_to_ext_payee_id;
691
692 -- Invoke Merge Banks
693 MERGE_BANKS(from_instr_pmt_use_list, from_ext_payee_id_list, from_bank_account_id_list,to_instr_pmt_use_list, to_ext_payee_id_list, to_bank_account_id_list,l_to_ext_payee_id,
694 P_from_party_id, P_to_party_id,'SITE',p_last_site_flag, X_return_status, X_msg_count, X_msg_data);
695
696 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
697 g_mesg := 'End Merging Bank Accounts at Suppier Site Level';
698 fnd_file.put_line(fnd_file.log, g_mesg);
699 END IF;
700
701 --Merge Bank Accounts at Address OU level
702 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
703 g_mesg := 'Start Merging Bank Accounts at Suppier Address - OU Level';
704 fnd_file.put_line(fnd_file.log, g_mesg);
705 END IF;
706 -- Get the From Supplier Bank Accounts at Address-OU Level
707 OPEN get_supplier_site_bank_dtls(P_from_party_id, null, P_from_party_site_id, P_from_org_id, P_from_org_type);
708 FETCH get_supplier_site_bank_dtls BULK COLLECT INTO from_instr_pmt_use_list, from_ext_payee_id_list, from_bank_account_id_list;
709 CLOSE get_supplier_site_bank_dtls;
710
711 -- Get the To Supplier Bank Accounts at Address-OU Level
712 OPEN get_supplier_site_bank_dtls(P_to_party_id, null, l_to_party_site_id, P_to_org_id, P_to_org_type);
713 FETCH get_supplier_site_bank_dtls BULK COLLECT INTO to_instr_pmt_use_list, to_ext_payee_id_list, to_bank_account_id_list;
714 CLOSE get_supplier_site_bank_dtls;
715
716 l_to_ext_payee_id := NULL;
717 -- Check whether the external Payee is created
718 OPEN cur_get_ext_payee_id(P_to_party_id, null, l_to_party_site_id, P_to_org_id, P_to_org_type);
719 FETCH cur_get_ext_payee_id INTO l_to_ext_payee_id;
720 CLOSE cur_get_ext_payee_id;
721
722 -- If external Payee record is not available and there are bank accounts associated to From Supplier Address-OU Then create External payee at address-OU level.
723 IF l_to_ext_payee_id IS NULL AND from_instr_pmt_use_list.Count > 0 THEN
724 Create_external_payee(l_site_payee_id, 'ADDRESS_OU', l_to_ext_payee_id, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
725 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
726 fnd_file.put_line(fnd_file.log, 'Exception: '||X_MSG_DATA);
727 END IF;
728 END IF;
729
730 -- Invoke Merge Banks
731 MERGE_BANKS(from_instr_pmt_use_list, from_ext_payee_id_list, from_bank_account_id_list,to_instr_pmt_use_list, to_ext_payee_id_list, to_bank_account_id_list,l_to_ext_payee_id,
732 P_from_party_id, P_to_party_id,'ADDRESS_OU',p_last_site_flag, X_return_status, X_msg_count, X_msg_data);
733
734 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
735 g_mesg := 'End Merging Bank Accounts at Suppier Address - OU Level';
736 fnd_file.put_line(fnd_file.log, g_mesg);
737 END IF;
738 --Merge Bank Accounts at Address level
739 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
740 g_mesg := 'Start Merging Bank Accounts at Suppier Address Level';
741 fnd_file.put_line(fnd_file.log, g_mesg);
742 END IF;
743 -- Get the From Supplier Bank Accounts at Address Level
744 OPEN get_supplier_site_bank_dtls(P_from_party_id, null, P_from_party_site_id, null, null);
745 FETCH get_supplier_site_bank_dtls BULK COLLECT INTO from_instr_pmt_use_list, from_ext_payee_id_list, from_bank_account_id_list;
746 CLOSE get_supplier_site_bank_dtls;
747
748 -- Get the To Supplier Bank Accounts at Address Level
749 OPEN get_supplier_site_bank_dtls(P_to_party_id, null, l_to_party_site_id, null, null);
750 FETCH get_supplier_site_bank_dtls BULK COLLECT INTO to_instr_pmt_use_list, to_ext_payee_id_list, to_bank_account_id_list;
751 CLOSE get_supplier_site_bank_dtls;
752
753 l_to_ext_payee_id := NULL;
754 -- Check whether the external Payee is created
755 OPEN cur_get_ext_payee_id(P_to_party_id, null, l_to_party_site_id, null, null);
756 FETCH cur_get_ext_payee_id INTO l_to_ext_payee_id;
757 CLOSE cur_get_ext_payee_id;
758
759 -- If external Payee record is not available and there are bank accounts associated to 'From Supplier' Address Then create External payee at address level.
760 IF l_to_ext_payee_id IS NULL AND from_instr_pmt_use_list.Count > 0 THEN
761 Create_external_payee(l_site_payee_id, 'ADDRESS', l_to_ext_payee_id, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
762 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
763 fnd_file.put_line(fnd_file.log, 'Exception: '||X_MSG_DATA);
764 END IF;
765 END IF;
766
767 -- Invoke Merge Banks
768 MERGE_BANKS(from_instr_pmt_use_list, from_ext_payee_id_list, from_bank_account_id_list,to_instr_pmt_use_list, to_ext_payee_id_list, to_bank_account_id_list,l_to_ext_payee_id,
769 P_from_party_id, P_to_party_id, 'ADDRESS', p_last_site_flag, X_return_status, X_msg_count, X_msg_data);
770
771 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
772 g_mesg := 'End Merging Bank Accounts at Suppier Address Level';
773 fnd_file.put_line(fnd_file.log, g_mesg);
774 END IF;
775 --Merge Bank Accounts at Address level
776 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
777 g_mesg := 'Start Merging Bank Accounts at Suppier Level';
778 fnd_file.put_line(fnd_file.log, g_mesg);
779 END IF;
780
781 -- Get the From Supplier Bank Accounts at Supplier Level
782 OPEN get_supplier_site_bank_dtls(P_from_party_id, null, null, null, null);
783 FETCH get_supplier_site_bank_dtls BULK COLLECT INTO from_instr_pmt_use_list, from_ext_payee_id_list, from_bank_account_id_list;
784 CLOSE get_supplier_site_bank_dtls;
785
786 -- Get the To Supplier Bank Accounts at Supplier Level
787 OPEN get_supplier_site_bank_dtls(P_to_party_id, null, null, null, null);
788 FETCH get_supplier_site_bank_dtls BULK COLLECT INTO to_instr_pmt_use_list, to_ext_payee_id_list, to_bank_account_id_list;
789 CLOSE get_supplier_site_bank_dtls;
790
791 l_to_ext_payee_id := NULL;
792 -- Check whether the external Payee is created
793 OPEN cur_get_ext_payee_id(P_to_party_id, null, null, null, null);
794 FETCH cur_get_ext_payee_id INTO l_to_ext_payee_id;
795 CLOSE cur_get_ext_payee_id;
796
797 -- If external Payee record is not available and there are bank accounts associated to 'From Supplier' Then create External payee at Supplier level.
798 IF l_to_ext_payee_id IS NULL AND from_instr_pmt_use_list.Count > 0 THEN
799 Create_external_payee(l_site_payee_id, 'SUPPLIER', l_to_ext_payee_id, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
800 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
801 fnd_file.put_line(fnd_file.log, 'Exception: '||X_MSG_DATA);
802 END IF;
803 END IF;
804
805 -- Invoke Merge Banks
806 MERGE_BANKS(from_instr_pmt_use_list, from_ext_payee_id_list, from_bank_account_id_list,to_instr_pmt_use_list, to_ext_payee_id_list, to_bank_account_id_list,
807 l_to_ext_payee_id, P_from_party_id, P_to_party_id, 'SUPPLIER', p_last_site_flag, X_return_status, X_msg_count, X_msg_data);
808
809 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
810 g_mesg := 'End Merging Bank Accounts at Suppier Level';
811 fnd_file.put_line(fnd_file.log, g_mesg);
812 END IF;
813 -- If Last Site for the supplier then inactivate bank account associations for from supplier at all levels.
814 IF p_last_site_flag = 'Y' THEN
815 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
816 g_mesg := 'Inactivating all Bank account associations of From Supplier';
817 fnd_file.put_line(fnd_file.log, g_mesg);
818 END IF;
819
820 -- Added the filter condition in inner query for Bug 12910775, do not inactivate the sites of the same supplier
821
822
823 UPDATE iby_pmt_instr_uses_all
824 SET end_date = SYSDATE
825 WHERE ext_pmt_party_id IN
826 (SELECT ext_payee_id
827 FROM Iby_external_payees_all
828 WHERE payee_party_id = P_from_party_id
829 AND supplier_site_id = P_from_vendor_site_id )
830 AND payment_function = 'PAYABLES_DISB'
831 AND payment_flow = 'DISBURSEMENTS'
832 AND instrument_type = 'BANKACCOUNT'
833 AND NVL(end_date,SYSDATE) >= SYSDATE;
834
835
836 END IF;
837
838 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
839 g_mesg :='Exit';
840 fnd_file.put_line(fnd_file.log, g_mesg);
841 END IF;
842 EXCEPTION
843 WHEN OTHERS THEN
844
845 g_mesg :='Unexpected error:=' || SQLERRM;
846 fnd_file.put_line(fnd_file.log, g_mesg || l_dbg_mod );
847
848 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
849 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT' ,SQLERRM);
850 FND_MSG_PUB.ADD;
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852
853 END BANK_ACCOUNTS_MERGE;
854
855 END IBY_SUPP_BANK_MERGE_PUB;
856