[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AP_ASSOCIATE_COLLECTION
Source
1 package body JL_BR_AP_ASSOCIATE_COLLECTION as
2 /* $Header: jlbrpacb.pls 120.4.12020000.3 2012/11/19 17:51:05 abuissa ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(50) :=
13 'JL.PLSQL.JL_BR_AP_ASSOCIATE_COLLECTION.';
14
15
16
17
18 /*-----------------------------------------------------------*/
19 /*<<<<< JL_BR_AP_ASSOCIATE_COLL_DOC >>>>>*/
20 /*-----------------------------------------------------------*/
21 PROCEDURE jl_br_ap_associate_coll_doc (
22 bank_collection_id_e IN NUMBER,
23 association_method_e IN VARCHAR2,
24 invoice_id_s IN OUT NOCOPY NUMBER,
25 payment_num_s IN OUT NOCOPY NUMBER,
26 associate_flag_s IN OUT NOCOPY VARCHAR2 )
27 IS
28 x_enable_bank_coll VARCHAR2(1);
29 x_enable_association VARCHAR2(1);
30 -- Logging Infra:
31 l_procedure_name CONSTANT VARCHAR2(30) := 'jl_br_ap_associate_coll_doc';
32 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
33 -- Logging Infra:
34
35 /*-----------------------------------------------------------*/
36 /* ASSOCIATE_COLL_DOC */
37 /*-----------------------------------------------------------*/
38 PROCEDURE associate_coll_doc (
39 bank_collection_id_e IN NUMBER,
40 association_method_e IN VARCHAR2,
41 invoice_id_s IN OUT NOCOPY NUMBER,
42 payment_num_s IN OUT NOCOPY NUMBER,
43 associate_flag_s OUT NOCOPY VARCHAR2 )
44 IS
45 x_selected VARCHAR2(1);
46 -- Logging
47 l_procedure_name CONSTANT VARCHAR2(30) := 'associate_coll_doc';
48 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
49 -- Logging
50
51 BEGIN
52 x_selected := 'Y';
53 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
54 l_log_msg := l_procedure_name||'(+)';
55 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
56 l_log_msg := 'Parameters : bank_collection_id_e ' || bank_collection_id_e ||
57 ' Association_method ' || association_method_e;
58 END IF;
59 BEGIN
60 IF (association_method_e = 'METHOD1') THEN
61 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
62 l_log_msg := 'METHOD1';
63 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
64 END IF;
65 /* Invoice number AND Due date */
66 SELECT apps.invoice_id, apps.payment_num
67 INTO invoice_id_s, payment_num_s
68 FROM ap_payment_schedules_ALL apps,
69 jl_br_ap_collection_docs jlbl,
70 ap_invoices_ALL apinv
71 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
72 apps.org_id = jlbl.org_id AND --bug 14615620
73 apps.org_id = apinv.org_id AND --bug 14615620
74 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
75 apps.invoice_id = apinv.invoice_id ) AND
76 ( apps.due_date = jlbl.due_date )) AND
77 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
78 ( apps.global_attribute11 IS NULL ) AND
79 ( apinv.cancelled_date IS NULL ) AND
80 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
81 AND apinv.invoice_currency_code = jlbl.currency_code;
82
83 ELSIF (association_method_e = 'METHOD2') THEN
84 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
85 l_log_msg := 'Method2 ';
86 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
87 END IF;
88 /* Supplier Site AND Supplier Name */
89 SELECT apps.invoice_id, apps.payment_num
90 INTO invoice_id_s, payment_num_s
91 FROM ap_payment_schedules_ALL apps,
92 jl_br_ap_collection_docs jlbl,
93 ap_invoices_ALL apinv
94 /* Bug # 635847 / 659227
95 ,
96 po_vendor_sites povs
97 */
98 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
99 apps.org_id = jlbl.org_id AND --bug 14615620
100 apps.org_id = apinv.org_id AND --bug 14615620
101 (( apinv.vendor_site_id = jlbl.vendor_site_id AND
102 apps.invoice_id = apinv.invoice_id ) AND
103 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
104 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
105 apps.invoice_id = apinv.invoice_id ) ) AND
106 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
107 ( apps.global_attribute11 IS NULL ) AND
108 ( apinv.cancelled_date IS NULL ) AND
109 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
110 AND apinv.invoice_currency_code = jlbl.currency_code;
111
112 ELSIF (association_method_e = 'METHOD3') THEN
113 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
114 l_log_msg := 'METHOD3';
115 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
116 END IF;
117 /* Supplier Site OR Supplier Name */
118 BEGIN
119 SELECT apps.invoice_id, apps.payment_num
120 INTO invoice_id_s, payment_num_s
121 FROM ap_payment_schedules_ALL apps,
122 jl_br_ap_collection_docs jlbl,
123 ap_invoices_ALL apinv
124 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
125 apps.org_id = jlbl.org_id AND --bug 14615620
126 apps.org_id = apinv.org_id AND --bug 14615620
127 (( apinv.vendor_site_id = jlbl.vendor_site_id AND
128 apps.invoice_id = apinv.invoice_id ) ) AND
129 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
130 ( apps.global_attribute11 IS NULL ) AND
131 ( apinv.cancelled_date IS NULL ) AND
132 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
133 AND apinv.invoice_currency_code = jlbl.currency_code;
134 EXCEPTION
135 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
136 x_selected := 'N';
137 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
138 l_log_msg := 'Exception, x_selected set to N';
139 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
140 END IF;
141
142 END;
143 IF x_selected = 'N' THEN
144 SELECT apps.invoice_id, apps.payment_num
145 INTO invoice_id_s, payment_num_s
146 FROM ap_payment_schedules_ALL apps,
147 jl_br_ap_collection_docs jlbl,
148 ap_invoices_ALL apinv
149 /* Bug # 635847 / 659227
150 ,
151 po_vendor_sites povs
152 */
153 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
154 apps.org_id = jlbl.org_id AND --bug 14615620
155 apps.org_id = apinv.org_id AND --bug 14615620
156 /* (( povs.vendor_site_id = jlbl.vendor_site_id AND */
157 ((apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
158 apps.invoice_id = apinv.invoice_id ) ) AND
159 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
160 ( apps.global_attribute11 IS NULL ) AND
161 ( apinv.cancelled_date IS NULL ) AND
162 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
163 AND apinv.invoice_currency_code = jlbl.currency_code;
164 x_selected:='Y';
165 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
166 l_log_msg := 'Next select executed and x_selected is set to Y';
167 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
168 END IF;
169 END IF;
170 ELSIF (association_method_e = 'METHOD4') THEN
171 /* Invoice Number AND Due date AND Supplier Site */
172 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
173 l_log_msg := 'METHOD4';
174 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
175 END IF;
176 SELECT apps.invoice_id, apps.payment_num
177 INTO invoice_id_s, payment_num_s
178 FROM ap_payment_schedules_ALL apps,
179 jl_br_ap_collection_docs jlbl,
180 ap_invoices_ALL apinv
181 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
182 apps.org_id = jlbl.org_id AND --bug 14615620
183 apps.org_id = apinv.org_id AND --bug 14615620
184 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
185 apps.invoice_id = apinv.invoice_id ) AND
186 ( apps.due_date = jlbl.due_date ) AND
187 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
188 apps.invoice_id = apinv.invoice_id ) ) AND
189 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
190 ( apps.global_attribute11 IS NULL ) AND
191 ( apinv.cancelled_date IS NULL ) AND
192 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
193 AND apinv.invoice_currency_code = jlbl.currency_code;
194
195 ELSIF (association_method_e = 'METHOD5') THEN
196 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
197 l_log_msg := 'METHOD5 ';
198 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
199 END IF;
200 /* Invoice Number AND Due date AND Supplier Name */
201 SELECT apps.invoice_id, apps.payment_num
202 INTO invoice_id_s, payment_num_s
203 FROM ap_payment_schedules_ALL apps,
204 jl_br_ap_collection_docs jlbl,
205 ap_invoices_ALL apinv
206 /* Bug # 635847 / 659227
207 ,
208 po_vendor_sites povs
209 */
210 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
211 apps.org_id = jlbl.org_id AND --bug 14615620
212 apps.org_id = apinv.org_id AND --bug 14615620
213 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
214 apps.invoice_id = apinv.invoice_id ) AND
215 ( apps.due_date = jlbl.due_date ) AND
216 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
217 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
218 apps.invoice_id = apinv.invoice_id ) ) AND
219 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
220 ( apps.global_attribute11 IS NULL ) AND
221 ( apinv.cancelled_date IS NULL ) AND
222 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
223 AND apinv.invoice_currency_code = jlbl.currency_code;
224
225 ELSIF (association_method_e = 'METHOD6') THEN
226 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
227 l_log_msg := 'METHOD6';
228 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
229 END IF;
230 /* Invoice number AND Gross Amount AND Due Date */
231 SELECT apps.invoice_id, apps.payment_num
232 INTO invoice_id_s, payment_num_s
233 FROM ap_payment_schedules_ALL apps,
234 jl_br_ap_collection_docs jlbl,
235 ap_invoices_ALL apinv
236 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
237 apps.org_id = jlbl.org_id AND --bug 14615620
238 apps.org_id = apinv.org_id AND --bug 14615620
239 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
240 apps.invoice_id = apinv.invoice_id ) AND
241 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
242 apinv.invoice_id = apps.invoice_id AND
243 apinv.payment_currency_code = jlbl.currency_code ) AND
244 ( apps.due_date = jlbl.due_date ) ) AND
245 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
246 ( apps.global_attribute11 IS NULL ) AND
247 ( apinv.cancelled_date IS NULL ) AND
248 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
249 AND apinv.invoice_currency_code = jlbl.currency_code;
250
251 ELSIF (association_method_e = 'METHOD7') THEN
252 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
253 l_log_msg := 'METHOD7';
254 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
255 END IF;
256 /* Invoice number AND Gross Amount AND Due Date AND Supplier Site */
257 SELECT apps.invoice_id, apps.payment_num
258 INTO invoice_id_s, payment_num_s
259 FROM ap_payment_schedules_ALL apps,
260 jl_br_ap_collection_docs jlbl,
261 ap_invoices_ALL apinv
262 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
263 apps.org_id = jlbl.org_id AND --bug 14615620
264 apps.org_id = apinv.org_id AND --bug 14615620
265 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
266 apps.invoice_id = apinv.invoice_id ) AND
267 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
268 apinv.invoice_id = apps.invoice_id AND
269 apinv.payment_currency_code = jlbl.currency_code ) AND
270 ( apps.due_date = jlbl.due_date ) AND
271 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
272 apps.invoice_id = apinv.invoice_id ) ) AND
273 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
274 ( apps.global_attribute11 IS NULL ) AND
275 ( apinv.cancelled_date IS NULL ) AND
276 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
277 AND apinv.invoice_currency_code = jlbl.currency_code;
278
279 ELSIF (association_method_e = 'METHOD8') THEN
280 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
281 l_log_msg := 'METHOD8';
282 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
283 END IF;
284 /* Invoice number AND Gross Amount AND Due Date AND Supplier Name */
285 SELECT apps.invoice_id, apps.payment_num
286 INTO invoice_id_s, payment_num_s
287 FROM ap_payment_schedules_ALL apps,
288 jl_br_ap_collection_docs jlbl,
289 ap_invoices_ALL apinv
290 /* Bug # 635847 / 659227
291 ,
292 po_vendor_sites povs
293 */
294 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
295 apps.org_id = jlbl.org_id AND --bug 14615620
296 apps.org_id = apinv.org_id AND --bug 14615620
297 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
298 apps.invoice_id = apinv.invoice_id ) AND
299 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
300 apinv.invoice_id = apps.invoice_id AND
301 apinv.payment_currency_code = jlbl.currency_code ) AND
302 ( apps.due_date = jlbl.due_date ) AND
303 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND*/
304 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
305 apps.invoice_id = apinv.invoice_id ) ) AND
306 ( apinv.invoice_type_lookup_code ='STANDARD') AND
307 ( apps.global_attribute11 IS NULL ) AND
308 ( apinv.cancelled_date IS NULL ) AND
309 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
310 AND apinv.invoice_currency_code = jlbl.currency_code;
311
312 ELSIF (association_method_e = 'METHOD9') THEN
313 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
314 l_log_msg := 'METHOD9';
315 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
316 END IF;
317 /* Invoice number AND Gross Amount AND Due Date AND Supplier Site */
318 /* AND Supplier Name */
319 SELECT apps.invoice_id, apps.payment_num
320 INTO invoice_id_s, payment_num_s
321 FROM ap_payment_schedules_ALL apps,
322 jl_br_ap_collection_docs jlbl,
323 ap_invoices_ALL apinv
324 /* Bug # 635847 / 659227
325 ,
326 po_vendor_sites povs
327 */
328 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
329 apps.org_id = jlbl.org_id AND --bug 14615620
330 apps.org_id = apinv.org_id AND --bug 14615620
331 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
332 apps.invoice_id = apinv.invoice_id ) AND
333 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
334 apinv.invoice_id = apps.invoice_id AND
335 apinv.payment_currency_code = jlbl.currency_code ) AND
336 ( apps.due_date = jlbl.due_date ) AND
337 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
338 apps.invoice_id = apinv.invoice_id ) AND
339 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
340 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND */
341 apps.invoice_id = apinv.invoice_id ) ) AND
342 ( apinv.invoice_type_lookup_code ='STANDARD') AND
343 ( apps.global_attribute11 IS NULL ) AND
344 ( apinv.cancelled_date IS NULL ) AND
345 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
346 AND apinv.invoice_currency_code = jlbl.currency_code;
347
348 ELSIF (association_method_e = 'METHOD10') THEN
349 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
350 l_log_msg := 'METHOD10';
351 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
352 END IF;
353 /* Invoice number AND Gross Amount AND Due Date OR Supplier Site */
354 /* AND Supplier Name */
355 BEGIN
356 SELECT apps.invoice_id, apps.payment_num
357 INTO invoice_id_s, payment_num_s
358 FROM ap_payment_schedules_ALL apps,
359 jl_br_ap_collection_docs jlbl,
360 ap_invoices_ALL apinv
361 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
362 apps.org_id = jlbl.org_id AND --bug 14615620
363 apps.org_id = apinv.org_id AND --bug 14615620
364 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
365 apps.invoice_id = apinv.invoice_id ) AND
366 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
367 apinv.invoice_id = apps.invoice_id AND
368 apinv.payment_currency_code = jlbl.currency_code ) AND
369 ( apps.due_date = jlbl.due_date ) ) AND
370 ( apinv.invoice_type_lookup_code ='STANDARD') AND
371 ( apps.global_attribute11 IS NULL ) AND
372 ( apinv.cancelled_date IS NULL ) AND
373 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
374 AND apinv.invoice_currency_code = jlbl.currency_code;
375 EXCEPTION
376 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
377 x_selected := 'N';
378 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
379 l_log_msg := 'Exception, x_selected set to N';
380 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
381 END IF;
382
383 END;
384 IF x_selected = 'N' THEN
385 SELECT apps.invoice_id, apps.payment_num
386 INTO invoice_id_s, payment_num_s
387 FROM ap_payment_schedules_ALL apps,
388 jl_br_ap_collection_docs jlbl,
389 ap_invoices_ALL apinv
390 /* Bug # 635847 / 659227
391 ,
392 po_vendor_sites povs
393 */
394 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
395 apps.org_id = jlbl.org_id AND --bug 14615620
396 apps.org_id = apinv.org_id AND --bug 14615620
397 (( apinv.vendor_site_id = jlbl.vendor_site_id AND
398 apps.invoice_id = apinv.invoice_id ) AND
399 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
400 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND */
401 apps.invoice_id = apinv.invoice_id ) ) AND
402 ( apinv.invoice_type_lookup_code ='STANDARD') AND
403 ( apps.global_attribute11 IS NULL ) AND
404 ( apinv.cancelled_date IS NULL ) AND
405 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
406 AND apinv.invoice_currency_code = jlbl.currency_code;
407 x_selected:= 'Y';
408 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
409 l_log_msg := 'Next select executed and x_selected is set to Y';
410 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
411 END IF;
412 END IF;
413
414 ELSIF (association_method_e = 'METHOD11') THEN
415 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
416 l_log_msg := 'METHOD11';
417 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
418 END IF;
419 /* Invoice number OR Gross Amount AND Due Date AND Supplier Site */
420 BEGIN
421 SELECT apps.invoice_id, apps.payment_num
422 INTO invoice_id_s, payment_num_s
423 FROM ap_payment_schedules_ALL apps,
424 jl_br_ap_collection_docs jlbl,
425 ap_invoices_ALL apinv
426 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
427 apps.org_id = jlbl.org_id AND --bug 14615620
428 apps.org_id = apinv.org_id AND --bug 14615620
429 (( substr( apinv.invoice_num,1,15 ) = jlbl.document_number AND
430 apps.invoice_id = apinv.invoice_id ) ) AND
431 ( apinv.invoice_type_lookup_code ='STANDARD') AND
432 ( apps.global_attribute11 IS NULL ) AND
433 ( apinv.cancelled_date IS NULL ) AND
434 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
435 AND apinv.invoice_currency_code = jlbl.currency_code;
436 EXCEPTION
437 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
438 x_selected := 'N';
439 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
440 l_log_msg := 'Exception, x_selected set to N';
441 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
442 END IF;
443
444 END;
445 IF x_selected = 'N' THEN
446 SELECT apps.invoice_id, apps.payment_num
447 INTO invoice_id_s, payment_num_s
448 FROM ap_payment_schedules_ALL apps,
449 jl_br_ap_collection_docs jlbl,
450 ap_invoices_ALL apinv
451 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
452 apps.org_id = jlbl.org_id AND --bug 14615620
453 apps.org_id = apinv.org_id AND --bug 14615620
454 (( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
455 apinv.invoice_id = apps.invoice_id AND
456 apinv.payment_currency_code = jlbl.currency_code ) AND
457 ( apps.due_date = jlbl.due_date ) AND
458 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
459 apps.invoice_id = apinv.invoice_id ) ) AND
460 ( apinv.invoice_type_lookup_code ='STANDARD') AND
461 ( apps.global_attribute11 IS NULL ) AND
462 ( apinv.cancelled_date IS NULL ) AND
463 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
464 AND apinv.invoice_currency_code = jlbl.currency_code;
465 x_selected:= 'Y';
466 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
467 l_log_msg := 'Next select executed and x_selected is set to Y';
468 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
469 END IF;
470 END IF;
471 ELSIF (association_method_e = 'METHOD12') THEN
472 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
473 l_log_msg := 'METHOD12';
474 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
475 END IF;
476 /* Gross Amount AND Due Date AND Supplier Site */
477 SELECT apps.invoice_id, apps.payment_num
478 INTO invoice_id_s, payment_num_s
479 FROM ap_payment_schedules_ALL apps,
480 jl_br_ap_collection_docs jlbl,
481 ap_invoices_ALL apinv
482 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
483 apps.org_id = jlbl.org_id AND --bug 14615620
484 apps.org_id = apinv.org_id AND --bug 14615620
485 (( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
486 apinv.invoice_id = apps.invoice_id AND
487 apinv.payment_currency_code = jlbl.currency_code ) AND
488 ( apps.due_date = jlbl.due_date ) AND
489 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
490 apps.invoice_id = apinv.invoice_id ) ) AND
491 ( apinv.invoice_type_lookup_code ='STANDARD') AND
492 ( apps.global_attribute11 IS NULL ) AND
493 ( apinv.cancelled_date IS NULL ) AND
494 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
495 AND apinv.invoice_currency_code = jlbl.currency_code;
496
497 ELSIF (association_method_e = 'METHOD13') THEN
498 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
499 l_log_msg := 'METHOD13';
500 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
501 END IF;
502 /* Gross Amount AND Due Date AND Supplier Name */
503 SELECT apps.invoice_id, apps.payment_num
504 INTO invoice_id_s, payment_num_s
505 FROM ap_payment_schedules_ALL apps,
506 jl_br_ap_collection_docs jlbl,
507 ap_invoices_ALL apinv
508 /* Bug # 635847 / 659227
509 ,
510 po_vendor_sites povs
511 */
512 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
513 apps.org_id = jlbl.org_id AND --bug 14615620
514 apps.org_id = apinv.org_id AND --bug 14615620
515 (( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
516 apinv.invoice_id = apps.invoice_id AND
517 apinv.payment_currency_code = jlbl.currency_code ) AND
518 ( apps.due_date = jlbl.due_date ) AND
519 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND*/
520 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
521 apps.invoice_id = apinv.invoice_id ) ) AND
522 ( apinv.invoice_type_lookup_code ='STANDARD') AND
523 ( apps.global_attribute11 IS NULL ) AND
524 ( apinv.cancelled_date IS NULL ) AND
525 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
526 AND apinv.invoice_currency_code = jlbl.currency_code;
527
528 END IF;
529 EXCEPTION
530 -- Original condition Commented WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
531 WHEN NO_DATA_FOUND THEN
532 x_selected := 'N';
533 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
534 l_log_msg := 'Exception NO_DATA_FOUND, x_selected set to N';
535 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
536 END IF;
537 -- Added for debugging
538 WHEN TOO_MANY_ROWS THEN
539 x_selected := 'N';
540 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
541 l_log_msg := 'Exception TOO_MANY_ROWS, x_selected set to N';
542 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
543 END IF;
544 END;
545 IF x_selected = 'Y' THEN /* Create the link with two tables */
546 UPDATE jl_br_ap_collection_docs
547 SET invoice_id = invoice_id_s,
548 payment_num = payment_num_s
549 WHERE bank_collection_id = bank_collection_id_e;
550 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
551 l_log_msg := 'Updated jl_br_ap_collection_docs invoice_id ' ||
552 invoice_id_s || ' payment_num' || payment_num_s;
553 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
554 END IF;
555
556 -- Bug Number 659227 / R11 Patch / May 98 (Copying Y to GA8)
557 UPDATE ap_payment_schedules
558 SET global_attribute11 = bank_collection_id_e,
559 global_attribute8 = 'Y'
560 WHERE invoice_id = invoice_id_s
561 AND payment_num = payment_num_s;
562 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
563 l_log_msg := 'Updated ap_payment_schedules global_attribute11 ' || bank_collection_id_e;
564 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
565 END IF;
566 associate_flag_s := 'Y';
567 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
568 l_log_msg := 'associate_flag_s is set to Y';
569 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
570 END IF;
571 ELSE
572 associate_flag_s := 'N';
573 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
574 l_log_msg := 'associate_flag_s is set to N';
575 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
576 END IF;
577 END IF;
578 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
579 l_log_msg := l_procedure_name||'(-)';
580 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
581 END IF;
582 END associate_coll_doc;
583
584 BEGIN
585 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
586 l_log_msg := l_procedure_name||'(+)';
587 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
588 END IF;
589 SELECT global_attribute1,
590 global_attribute2
591 INTO x_enable_bank_coll,
592 x_enable_association
593 FROM ap_system_parameters;
594 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
595 l_log_msg := 'Value of x_enable_bank_coll ' || x_enable_bank_coll
596 || ' x_enable_association ' || x_enable_association;
597 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
598 END IF;
599
600 IF x_enable_bank_coll = 'Y' THEN
601 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
602 l_log_msg := 'x_enable_bank_coll is Y';
603 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
604 END IF;
605 IF x_enable_association = 'Y' THEN
606 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
607 l_log_msg := 'x_enable_association is Y callined associate_coll_doc';
608 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
609 END IF;
610 associate_coll_doc( bank_collection_id_e, association_method_e,
611 invoice_id_s, payment_num_s, associate_flag_s );
612 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
613 l_log_msg := 'After calling associate_coll_doc returned values ';
614 l_log_msg :=l_log_msg || 'invoice_id_s ' || invoice_id_s;
615 l_log_msg :=l_log_msg || 'payment_num_s ' || payment_num_s;
616 l_log_msg :=l_log_msg || 'associate_flag_s ' || associate_flag_s;
617 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
618 END IF;
619 IF associate_flag_s = 'Y' THEN /* If associated, release both*/
620 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
621 l_log_msg := 'associate_flag_s is Y';
622 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
623 END IF;
624 UPDATE ap_payment_schedules
625 SET hold_flag = 'N'
626 WHERE invoice_id = invoice_id_s
627 AND payment_num = payment_num_s;
628 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
629 l_log_msg := 'updated hold flag to N in ap_payment_schedules';
630 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
631 END IF;
632
633 UPDATE jl_br_ap_collection_docs
634 SET hold_flag = 'N'
635 WHERE bank_collection_id = bank_collection_id_e;
636 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
637 l_log_msg := 'updated hold flag to N in jl_br_ap_collection_docs';
638 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
639 END IF;
640
641 ELSE /* If not associated, hold the collection document */
642 UPDATE jl_br_ap_collection_docs
643 SET hold_flag = 'Y'
644 WHERE bank_collection_id = bank_collection_id_e;
645 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
646 l_log_msg := 'associate_flag_s is N updated hold_flag to Y in jl_br_ap_collection_docs';
647 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
648 END IF;
649
650 END IF;
651 ELSE /* If association is not enabled, hold the collection document */
652 UPDATE jl_br_ap_collection_docs
653 SET hold_flag = 'Y'
654 WHERE bank_collection_id = bank_collection_id_e;
655 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
656 l_log_msg := 'x_enable_association is N update hold flag to Y in jl_br_ap_collection_docs';
657 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
658 END IF;
659
660 END IF;
661 ELSE /* If bank collection is not enabled, release the collection document*/
662 UPDATE jl_br_ap_collection_docs
663 SET hold_flag = 'N'
664 WHERE bank_collection_id = bank_collection_id_e;
665
666 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
667 l_log_msg := 'x_enable_bank_coll is N update hold flag to N in jl_br_ap_collection_docs';
668 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
669 END IF;
670
671 END IF;
672 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
673 l_log_msg := l_procedure_name||'(-)';
674 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
675 END IF;
676 END jl_br_ap_associate_coll_doc;
677 /*-----------------------------------------------------------*/
678 /*<<<<< JL_BR_AP_ASSOCIATE_TRADE_NOTE >>>>>*/
679 /*-----------------------------------------------------------*/
680 PROCEDURE jl_br_ap_associate_trade_note (
681 invoice_id_e IN NUMBER,
682 payment_num_e IN NUMBER,
683 association_method_e IN VARCHAR2,
684 bank_collection_id_s IN OUT NOCOPY NUMBER,
685 associate_flag_s IN OUT NOCOPY VARCHAR2 )
686 IS
687 x_enable_bank_coll ap_system_parameters.global_attribute1%TYPE;
688 x_enable_association ap_system_parameters.global_attribute2%TYPE;
689 x_payment_status_flag ap_payment_schedules.payment_status_flag%TYPE;
690 -- Logging Infra:
691 l_procedure_name CONSTANT VARCHAR2(30) := 'jl_br_ap_associate_trade_note';
692 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
693 -- Logging Infra:
694 /*-----------------------------------------------------------*/
695 /* ASSOCIATE_TRADE_NOTE */
696 /*-----------------------------------------------------------*/
697 PROCEDURE associate_trade_note (
698 invoice_id_e IN NUMBER,
699 payment_num_e IN NUMBER,
700 association_method_e IN VARCHAR2,
701 bank_collection_id_s IN OUT NOCOPY NUMBER,
702 associate_flag_s OUT NOCOPY VARCHAR2 )
703 IS
704 x_selected VARCHAR2(1);
705 -- Logging Infra:
706 l_procedure_name CONSTANT VARCHAR2(30) := 'associate_trade_note';
707 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
708 -- Logging Infra:
709
710 BEGIN
711 x_selected := 'Y';
712 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
713 l_log_msg := l_procedure_name||'(+)';
714 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
715 END IF;
716 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
717 l_log_msg := 'Parameters : invoice_id_e ' || invoice_id_e || ' payment_num_e ' || payment_num_e ||
718 ' Association_method_e ' || association_method_e;
719 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
720 END IF;
721 BEGIN
722 IF (association_method_e = 'METHOD1') THEN
723 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
724 l_log_msg := 'METHOD1 ';
725 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
726 END IF;
727 /* Invoice number AND Due date */
728 SELECT bank_collection_id
729 INTO bank_collection_id_s
730 FROM ap_payment_schedules apps,
731 jl_br_ap_collection_docs_ALL jlbl,
732 ap_invoices_ALL apinv
733 WHERE apps.invoice_id = invoice_id_e AND
734 apps.payment_num = payment_num_e AND
735 apps.org_id = jlbl.org_id AND --bug 14615620
736 apps.org_id = apinv.org_id AND --bug 14615620
737 (( apinv.invoice_id = apps.invoice_id AND
738 jlbl.document_number = substr( apinv.invoice_num,1,15 )) AND
739 ( jlbl.due_date = apps.due_date ) ) AND
740 ( jlbl.invoice_id IS NULL AND
741 jlbl.payment_num IS NULL ) AND
742 ( jlbl.status_lookup_code = 'ACTIVE')
743 AND apinv.invoice_currency_code = jlbl.currency_code;
744
745 ELSIF (association_method_e = 'METHOD2') THEN
746 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
747 l_log_msg := 'METHOD2 ';
748 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
749 END IF;
750 /* Supplier Site and Supplier Name */
751 SELECT bank_collection_id
752 INTO bank_collection_id_s
753 FROM ap_payment_schedules apps,
754 jl_br_ap_collection_docs_ALL jlbl,
755 ap_invoices_ALL apinv
756 /* Bug # 635847 / 659227
757 ,
758 po_vendor_sites povs
759 */
760 WHERE apps.invoice_id = invoice_id_e AND
761 apps.payment_num = payment_num_e AND
762 apps.org_id = jlbl.org_id AND --bug 14615620
763 apps.org_id = apinv.org_id AND --bug 14615620
764 (( apinv.invoice_id = apps.invoice_id AND
765 jlbl.vendor_site_id = apinv.vendor_site_id ) AND
766 ( apinv.invoice_id = apps.invoice_id AND
767 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
768 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
769 ( jlbl.invoice_id IS NULL AND
770 jlbl.payment_num IS NULL ) AND
771 ( jlbl.status_lookup_code = 'ACTIVE')
772 AND apinv.invoice_currency_code = jlbl.currency_code;
773
774 ELSIF (association_method_e = 'METHOD3') THEN
775 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
776 l_log_msg := 'METHOD3 ';
777 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
778 END IF;
779 /* Supplier Site OR Supplier Name */
780 BEGIN
781 SELECT bank_collection_id
782 INTO bank_collection_id_s
783 FROM ap_payment_schedules apps,
784 jl_br_ap_collection_docs_ALL jlbl,
785 ap_invoices_ALL apinv
786 WHERE apps.invoice_id = invoice_id_e AND
787 apps.payment_num = payment_num_e AND
788 apps.org_id = jlbl.org_id AND --bug 14615620
789 apps.org_id = apinv.org_id AND --bug 14615620
790 (( apinv.invoice_id = apps.invoice_id AND
791 jlbl.vendor_site_id = apinv.vendor_site_id )) AND
792 ( jlbl.invoice_id IS NULL AND
793 jlbl.payment_num IS NULL ) AND
794 ( jlbl.status_lookup_code = 'ACTIVE')
795 AND apinv.invoice_currency_code = jlbl.currency_code;
796 EXCEPTION
797 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
798 x_selected := 'N';
799 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
800 l_log_msg := 'Exception, x_selected set to N';
801 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
802 END IF;
803
804 END;
805 IF x_selected = 'N' THEN
806 SELECT bank_collection_id
807 INTO bank_collection_id_s
808 FROM ap_payment_schedules apps,
809 jl_br_ap_collection_docs_ALL jlbl,
810 ap_invoices_ALL apinv
811 /* Bug # 635847 / 659227
812 ,
813 po_vendor_sites povs
814 */
815 WHERE apps.invoice_id = invoice_id_e AND
816 apps.payment_num = payment_num_e AND
817 apps.org_id = jlbl.org_id AND --bug 14615620
818 apps.org_id = apinv.org_id AND --bug 14615620
819 (( apinv.invoice_id = apps.invoice_id AND
820 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
821 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
822 ( jlbl.invoice_id IS NULL AND
823 jlbl.payment_num IS NULL ) AND
824 ( jlbl.status_lookup_code = 'ACTIVE')
825 AND apinv.invoice_currency_code = jlbl.currency_code;
826 x_selected:= 'Y';
827 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
828 l_log_msg := 'Next select executed and x_selected is set to Y';
829 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
830 END IF;
831 END IF;
832 ELSIF (association_method_e = 'METHOD4') THEN
833 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
834 l_log_msg := 'METHOD4 ';
835 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
836 END IF;
837 /* Invoice Number AND Due date AND Supplier Site */
838 SELECT bank_collection_id
839 INTO bank_collection_id_s
840 FROM ap_payment_schedules apps,
841 jl_br_ap_collection_docs_ALL jlbl,
842 ap_invoices_ALL apinv
843 WHERE apps.invoice_id = invoice_id_e AND
844 apps.payment_num = payment_num_e AND
845 apps.org_id = jlbl.org_id AND --bug 14615620
846 apps.org_id = apinv.org_id AND --bug 14615620
847 (( apinv.invoice_id = apps.invoice_id AND
848 jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
849 ( jlbl.due_date = apps.due_date ) AND
850 ( apinv.invoice_id = apps.invoice_id AND
851 jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
852 ( jlbl.invoice_id IS NULL AND
853 jlbl.payment_num IS NULL ) AND
854 ( jlbl.status_lookup_code = 'ACTIVE')
855 AND apinv.invoice_currency_code = jlbl.currency_code;
856
857 ELSIF (association_method_e = 'METHOD5') THEN
858 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
859 l_log_msg := 'METHOD5 ';
860 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
861 END IF;
862 /* Invoice Number AND Due date AND Supplier Name */
863 SELECT bank_collection_id
864 INTO bank_collection_id_s
865 FROM ap_payment_schedules apps,
866 jl_br_ap_collection_docs_ALL jlbl,
867 ap_invoices_ALL apinv
868 /* Bug # 635847 / 659227
869 ,
870 po_vendor_sites povs
871 */
872 WHERE apps.invoice_id = invoice_id_e AND
873 apps.payment_num = payment_num_e AND
874 apps.org_id = jlbl.org_id AND --bug 14615620
875 apps.org_id = apinv.org_id AND --bug 14615620
876 (( apinv.invoice_id = apps.invoice_id AND
877 jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
878 ( jlbl.due_date = apps.due_date ) AND
879 ( apinv.invoice_id = apps.invoice_id AND
880 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
881 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
882 ( jlbl.invoice_id IS NULL AND
883 jlbl.payment_num IS NULL ) AND
884 ( jlbl.status_lookup_code = 'ACTIVE')
885 AND apinv.invoice_currency_code = jlbl.currency_code;
886
887 ELSIF (association_method_e = 'METHOD6') THEN
888 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
889 l_log_msg := 'METHOD6 ';
890 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
891 END IF;
892 /* Invoice number AND Gross Amount AND Due Date */
893 SELECT bank_collection_id
894 INTO bank_collection_id_s
895 FROM ap_payment_schedules apps,
896 jl_br_ap_collection_docs_ALL jlbl,
897 ap_invoices_ALL apinv
898 WHERE apps.invoice_id = invoice_id_e AND
899 apps.payment_num = payment_num_e AND
900 apps.org_id = jlbl.org_id AND --bug 14615620
901 apps.org_id = apinv.org_id AND --bug 14615620
902 (( apinv.invoice_id = apps.invoice_id AND
903 jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
904 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
905 apinv.invoice_id = apps.invoice_id AND
906 jlbl.currency_code = apinv.payment_currency_code ) AND
907 ( jlbl.due_date = apps.due_date ) ) AND
908 ( jlbl.invoice_id IS NULL AND
909 jlbl.payment_num IS NULL ) AND
910 ( jlbl.status_lookup_code = 'ACTIVE')
911 AND apinv.invoice_currency_code = jlbl.currency_code;
912
913 ELSIF (association_method_e = 'METHOD7') THEN
914 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
915 l_log_msg := 'METHOD7 ';
916 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
917 END IF;
918 /* Invoice number AND Gross Amount AND Due Date AND Supplier Site */
919 SELECT bank_collection_id
920 INTO bank_collection_id_s
921 FROM ap_payment_schedules apps,
922 jl_br_ap_collection_docs_ALL jlbl,
923 ap_invoices_ALL apinv
924 WHERE apps.invoice_id = invoice_id_e AND
925 apps.payment_num = payment_num_e AND
926 apps.org_id = jlbl.org_id AND --bug 14615620
927 apps.org_id = apinv.org_id AND --bug 14615620
928 (( apinv.invoice_id = apps.invoice_id AND
929 jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
930 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
931 apinv.invoice_id = apps.invoice_id AND
932 jlbl.currency_code = apinv.payment_currency_code ) AND
933 ( jlbl.due_date = apps.due_date ) AND
934 ( apinv.invoice_id = apps.invoice_id AND
935 jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
936 ( jlbl.invoice_id IS NULL AND
937 jlbl.payment_num IS NULL ) AND
938 (jlbl.status_lookup_code = 'ACTIVE')
939 AND apinv.invoice_currency_code = jlbl.currency_code;
940
941 ELSIF (association_method_e = 'METHOD8') THEN
942 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
943 l_log_msg := 'METHOD8 ';
944 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
945 END IF;
946 /* Invoice number AND Gross Amount AND Due Date AND Supplier Name */
947 SELECT bank_collection_id
948 INTO bank_collection_id_s
949 FROM ap_payment_schedules apps,
950 jl_br_ap_collection_docs_ALL jlbl,
951 ap_invoices_ALL apinv
952 /* Bug # 635847 / 659227
953 ,
954 po_vendor_sites povs
955 */
956 WHERE apps.invoice_id = invoice_id_e AND
957 apps.payment_num = payment_num_e AND
958 apps.org_id = jlbl.org_id AND --bug 14615620
959 apps.org_id = apinv.org_id AND --bug 14615620
960 (( apinv.invoice_id = apps.invoice_id AND
961 jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
962 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
963 apinv.invoice_id = apps.invoice_id AND
964 jlbl.currency_code = apinv.payment_currency_code ) AND
965 ( jlbl.due_date = apps.due_date ) AND
966 ( apinv.invoice_id = apps.invoice_id AND
967 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
968 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
969 ( jlbl.invoice_id IS NULL AND
970 jlbl.payment_num IS NULL ) AND
971 ( jlbl.status_lookup_code = 'ACTIVE')
972 AND apinv.invoice_currency_code = jlbl.currency_code;
973
974 ELSIF (association_method_e = 'METHOD9') THEN
975 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
976 l_log_msg := 'METHOD9 ';
977 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
978 END IF;
979 /* Invoice number AND Gross Amount AND Due Date AND Supplier Site */
980 /* AND Supplier Name */
981 SELECT bank_collection_id
982 INTO bank_collection_id_s
983 FROM ap_payment_schedules apps,
984 jl_br_ap_collection_docs_ALL jlbl,
985 ap_invoices_ALL apinv
986 /* Bug # 635847 / 659227
987 ,
988 po_vendor_sites povs
989 */
990 WHERE apps.invoice_id = invoice_id_e AND
991 apps.payment_num = payment_num_e AND
992 apps.org_id = jlbl.org_id AND --bug 14615620
993 apps.org_id = apinv.org_id AND --bug 14615620
994 (( apinv.invoice_id = apps.invoice_id AND
995 jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
996 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
997 apinv.invoice_id = apps.invoice_id AND
998 jlbl.currency_code = apinv.payment_currency_code ) AND
999 ( jlbl.due_date = apps.due_date ) AND
1000 ( apinv.invoice_id = apps.invoice_id AND
1001 jlbl.vendor_site_id = apinv.vendor_site_id ) AND
1002 ( apinv.invoice_id = apps.invoice_id AND
1003 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
1004 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
1005 ( jlbl.invoice_id IS NULL AND
1006 jlbl.payment_num IS NULL ) AND
1007 ( jlbl.status_lookup_code = 'ACTIVE')
1008 AND apinv.invoice_currency_code = jlbl.currency_code;
1009
1010 ELSIF (association_method_e = 'METHOD10') THEN
1011 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1012 l_log_msg := 'METHOD10';
1013 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1014 END IF;
1015 /* Invoice number AND Gross Amount AND Due Date OR Supplier Site */
1016 /* AND Supplier Name */
1017 BEGIN
1018 SELECT bank_collection_id
1019 INTO bank_collection_id_s
1020 FROM ap_payment_schedules apps,
1021 jl_br_ap_collection_docs_ALL jlbl,
1022 ap_invoices_ALL apinv
1023 WHERE apps.invoice_id = invoice_id_e AND
1024 apps.payment_num = payment_num_e AND
1025 apps.org_id = jlbl.org_id AND --bug 14615620
1026 apps.org_id = apinv.org_id AND --bug 14615620
1027 (( apinv.invoice_id = apps.invoice_id AND
1028 jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) AND
1029 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
1030 apinv.invoice_id = apps.invoice_id AND
1031 jlbl.currency_code = apinv.payment_currency_code ) AND
1032 ( jlbl.due_date = apps.due_date ) ) AND
1033 ( jlbl.invoice_id IS NULL AND
1034 jlbl.payment_num IS NULL ) AND
1035 ( jlbl.status_lookup_code = 'ACTIVE')
1036 AND apinv.invoice_currency_code = jlbl.currency_code;
1037 EXCEPTION
1038 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1039 x_selected := 'N';
1040 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1041 l_log_msg := 'Exception, x_selected set to N';
1042 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1043 END IF;
1044
1045 END;
1046 IF x_selected = 'N' THEN
1047 SELECT bank_collection_id
1048 INTO bank_collection_id_s
1049 FROM ap_payment_schedules apps,
1050 jl_br_ap_collection_docs_ALL jlbl,
1051 ap_invoices_ALL apinv
1052 /* Bug # 635847 / 659227
1053 ,
1054 po_vendor_sites povs
1055 */
1056 WHERE apps.invoice_id = invoice_id_e AND
1057 apps.payment_num = payment_num_e AND
1058 apps.org_id = jlbl.org_id AND --bug 14615620
1059 apps.org_id = apinv.org_id AND --bug 14615620
1060 (( apinv.invoice_id = apps.invoice_id AND
1061 jlbl.vendor_site_id = apinv.vendor_site_id ) AND
1062 ( apinv.invoice_id = apps.invoice_id AND
1063 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
1064 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
1065 ( jlbl.invoice_id IS NULL AND
1066 jlbl.payment_num IS NULL ) AND
1067 ( jlbl.status_lookup_code = 'ACTIVE')
1068 AND apinv.invoice_currency_code = jlbl.currency_code;
1069 x_selected:= 'Y';
1070 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1071 l_log_msg := 'Next select executed and x_selected is set to Y';
1072 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1073 END IF;
1074 END IF;
1075
1076 ELSIF (association_method_e = 'METHOD11') THEN
1077 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1078 l_log_msg := 'METHOD11';
1079 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1080 END IF;
1081 /* Invoice number OR Gross Amount AND Due Date AND Supplier Site */
1082 BEGIN
1083 SELECT bank_collection_id
1084 INTO bank_collection_id_s
1085 FROM ap_payment_schedules apps,
1086 jl_br_ap_collection_docs_ALL jlbl,
1087 ap_invoices_ALL apinv
1088 WHERE apps.invoice_id = invoice_id_e AND
1089 apps.payment_num = payment_num_e AND
1090 apps.org_id = jlbl.org_id AND --bug 14615620
1091 apps.org_id = apinv.org_id AND --bug 14615620
1092 (( apinv.invoice_id = apps.invoice_id AND
1093 jlbl.document_number = substr( apinv.invoice_num,1,15 ) ) ) AND
1094 ( jlbl.invoice_id IS NULL AND
1095 jlbl.payment_num IS NULL ) AND
1096 ( jlbl.status_lookup_code = 'ACTIVE')
1097 AND apinv.invoice_currency_code = jlbl.currency_code;
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1100 x_selected := 'N';
1101 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1102 l_log_msg := 'Exception, x_selected set to N';
1103 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1104 END IF;
1105
1106 END;
1107 IF x_selected = 'N' THEN
1108 SELECT bank_collection_id
1109 INTO bank_collection_id_s
1110 FROM ap_payment_schedules apps,
1111 jl_br_ap_collection_docs_ALL jlbl,
1112 ap_invoices_ALL apinv
1113 WHERE apps.invoice_id = invoice_id_e AND
1114 apps.payment_num = payment_num_e AND
1115 apps.org_id = jlbl.org_id AND --bug 14615620
1116 apps.org_id = apinv.org_id AND --bug 14615620
1117 (( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
1118 apinv.invoice_id = apps.invoice_id AND
1119 jlbl.currency_code = apinv.payment_currency_code ) AND
1120 ( jlbl.due_date = apps.due_date ) AND
1121 ( apinv.invoice_id = apps.invoice_id AND
1122 jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
1123 ( jlbl.invoice_id IS NULL AND
1124 jlbl.payment_num IS NULL ) AND
1125 ( jlbl.status_lookup_code = 'ACTIVE')
1126 AND apinv.invoice_currency_code = jlbl.currency_code;
1127 x_selected:= 'Y';
1128 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1129 l_log_msg := 'Next select executed and x_selected is set to Y';
1130 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1131 END IF;
1132 END IF;
1133 ELSIF (association_method_e = 'METHOD12') THEN
1134 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1135 l_log_msg := 'METHOD12';
1136 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1137 END IF;
1138 /* Gross Amount AND Due Date AND Supplier Site */
1139 SELECT bank_collection_id
1140 INTO bank_collection_id_s
1141 FROM ap_payment_schedules apps,
1142 jl_br_ap_collection_docs_ALL jlbl,
1143 ap_invoices_ALL apinv
1144 WHERE apps.invoice_id = invoice_id_e AND
1145 apps.payment_num = payment_num_e AND
1146 apps.org_id = jlbl.org_id AND --bug 14615620
1147 apps.org_id = apinv.org_id AND --bug 14615620
1148 (( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
1149 apinv.invoice_id = apps.invoice_id AND
1150 jlbl.currency_code = apinv.payment_currency_code ) AND
1151 ( jlbl.due_date = apps.due_date ) AND
1152 ( apinv.invoice_id = apps.invoice_id AND
1153 jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
1154 ( jlbl.invoice_id IS NULL AND
1155 jlbl.payment_num IS NULL ) AND
1156 ( jlbl.status_lookup_code = 'ACTIVE')
1157 AND apinv.invoice_currency_code = jlbl.currency_code;
1158
1159 ELSIF (association_method_e = 'METHOD13') THEN
1160 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1161 l_log_msg := 'METHOD13';
1162 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1163 END IF;
1164 /* Gross Amount AND Due Date AND Supplier Name */
1165 SELECT bank_collection_id
1166 INTO bank_collection_id_s
1167 FROM ap_payment_schedules apps,
1168 jl_br_ap_collection_docs_ALL jlbl,
1169 ap_invoices_ALL apinv
1170 /* Bug # 635847 / 659227
1171 ,
1172 po_vendor_sites povs
1173 */
1174 WHERE apps.invoice_id = invoice_id_e AND
1175 apps.payment_num = payment_num_e AND
1176 apps.org_id = jlbl.org_id AND --bug 14615620
1177 apps.org_id = apinv.org_id AND --bug 14615620
1178 (( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
1179 apinv.invoice_id = apps.invoice_id AND
1180 jlbl.currency_code = apinv.payment_currency_code ) AND
1181 ( jlbl.due_date = apps.due_date ) AND
1182 ( apinv.invoice_id = apps.invoice_id AND
1183 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
1184 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
1185 ( jlbl.invoice_id IS NULL AND
1186 jlbl.payment_num IS NULL ) AND
1187 ( jlbl.status_lookup_code = 'ACTIVE')
1188 AND apinv.invoice_currency_code = jlbl.currency_code;
1189
1190 END IF;
1191 EXCEPTION
1192 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1193 x_selected := 'N';
1194 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1195 l_log_msg := 'Exception, x_selected set to N';
1196 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1197 END IF;
1198 END;
1199 IF x_selected = 'Y' THEN /* Create the link with two tables */
1200 -- Bug Number 659227 / R11 Patch / May 98 (Copying Y to GA8)
1201 UPDATE ap_payment_schedules
1202 SET global_attribute11 = bank_collection_id_s,
1203 global_attribute8 = 'Y'
1204 WHERE invoice_id = invoice_id_e AND
1205 payment_num = payment_num_e;
1206 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1207 l_log_msg := 'Updated ap_payment_schedules global_attribute11 ' || bank_collection_id_s;
1208 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1209 END IF;
1210 UPDATE jl_br_ap_collection_docs
1211 SET invoice_id = invoice_id_e,
1212 payment_num = payment_num_e
1213 WHERE bank_collection_id = bank_collection_id_s;
1214 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1215 l_log_msg := 'Updated jl_br_ap~_collection_docs invoice_id ' || invoice_id_e;
1216 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1217 END IF;
1218 associate_flag_s := 'Y';
1219 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1220 l_log_msg := 'associate_flag_s set to Y updated values';
1221 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1222 END IF;
1223
1224 ELSE
1225 associate_flag_s := 'N';
1226 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1227 l_log_msg := 'associate_flag_s set to N';
1228 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1229 END IF;
1230 END IF;
1231 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1232 l_log_msg := l_procedure_name||'(-)';
1233 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1234 END IF;
1235 END associate_trade_note;
1236
1237 BEGIN
1238 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1239 l_log_msg := l_procedure_name||'(+)';
1240 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1241 END IF;
1242 SELECT global_attribute1
1243 INTO x_enable_bank_coll
1244 FROM ap_system_parameters;
1245 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1246 l_log_msg := 'Value of x_enable_bank_coll ' || x_enable_bank_coll;
1247 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1248 END IF;
1249
1250 SELECT global_attribute1
1251 INTO x_enable_association
1252 FROM ap_invoices
1253 WHERE invoice_id = invoice_id_e;
1254 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1255 l_log_msg := 'Value of x_enable_association ' || x_enable_association;
1256 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1257 END IF;
1258
1259 IF x_enable_bank_coll = 'Y' THEN
1260 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1261 l_log_msg := 'x_enable_bank_coll is Y';
1262 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1263 END IF;
1264 IF x_enable_association = 'Y' THEN
1265 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1266 l_log_msg := 'x_enable_association is Y calling associate_trade_note';
1267 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1268 END IF;
1269 associate_trade_note( invoice_id_e, payment_num_e,
1270 association_method_e, bank_collection_id_s,
1271 associate_flag_s );
1272 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1273 l_log_msg := 'After calling associate_trade_note returned values ';
1274 l_log_msg :=l_log_msg || 'bank_collection_id_s ' || bank_collection_id_s;
1275 l_log_msg :=l_log_msg || 'associate_flag_s ' || associate_flag_s;
1276 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1277 END IF;
1278
1279 IF associate_flag_s = 'Y' THEN /* Se associou, */
1280 /* libera os dois */
1281 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1282 l_log_msg := 'associate_flag_s is Y';
1283 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1284 END IF;
1285
1286 UPDATE ap_payment_schedules
1287 SET hold_flag = 'N'
1288 WHERE invoice_id = invoice_id_e
1289 AND payment_num = payment_num_e;
1290
1291 UPDATE jl_br_ap_collection_docs
1292 SET hold_flag = 'N'
1293 WHERE bank_collection_id = bank_collection_id_s;
1294 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1295 l_log_msg := 'Updated hold to N in ar_payment_schedules, jl_br_ap_collection_docs';
1296 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1297 END IF;
1298
1299 ELSE /* If not associated and payment schedule not paid */
1300 /* then hold the payment schedule */
1301 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1302 l_log_msg := 'associate_flag_s is N';
1303 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1304 END IF;
1305 SELECT payment_status_flag
1306 INTO x_payment_status_flag
1307 FROM ap_payment_schedules
1308 WHERE invoice_id = invoice_id_e
1309 AND payment_num = payment_num_e;
1310
1311 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1312 l_log_msg := 'x_payment_status_flag is || x_payment_status_flag';
1313 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1314 END IF;
1315
1316 IF (x_payment_status_flag = 'N') THEN
1317 UPDATE ap_payment_schedules
1318 SET hold_flag = 'Y'
1319 WHERE invoice_id = invoice_id_e
1320 AND payment_num = payment_num_e;
1321 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1322 l_log_msg := 'x_payment_status_flag N, Updated hold_flag_s Y in ap_payment_schedules';
1323 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1324 END IF;
1325 END IF;
1326 END IF;
1327 ELSE /* If association not enabled, hold the payment schedule */
1328 UPDATE ap_payment_schedules
1329 SET hold_flag = 'Y'
1330 WHERE invoice_id = invoice_id_e
1331 AND payment_num = payment_num_e;
1332 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1333 l_log_msg := 'x_enable_association is N updated ap_payment_schedules hold flag to Y';
1334 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1335 END IF;
1336
1337 END IF;
1338 ELSE /* If bank collection is not enabled, release the payment schedule */
1339 UPDATE ap_payment_schedules
1340 SET hold_flag = 'N'
1341 WHERE invoice_id = invoice_id_e
1342 AND payment_num = payment_num_e;
1343 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1344 l_log_msg := 'x_enable_bank_coll is N, updated ap_payment_schedules to N';
1345 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1346 END IF;
1347
1348 END IF;
1349 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1350 l_log_msg := l_procedure_name||'(-)';
1351 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1352 END IF;
1353 END jl_br_ap_associate_trade_note;
1354
1355 END JL_BR_AP_ASSOCIATE_COLLECTION;