[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.2 2003/04/26 01:27:12 thwon ship $ */
3
4 /*-----------------------------------------------------------*/
5 /*<<<<< JL_BR_AP_ASSOCIATE_COLL_DOC >>>>>*/
6 /*-----------------------------------------------------------*/
7 PROCEDURE jl_br_ap_associate_coll_doc (
8 bank_collection_id_e IN NUMBER,
9 association_method_e IN VARCHAR2,
10 invoice_id_s IN OUT NOCOPY NUMBER,
11 payment_num_s IN OUT NOCOPY NUMBER,
12 associate_flag_s IN OUT NOCOPY VARCHAR2 )
13 IS
14 x_enable_bank_coll VARCHAR2(1);
15 x_enable_association VARCHAR2(1);
16
17 /*-----------------------------------------------------------*/
18 /* ASSOCIATE_COLL_DOC */
19 /*-----------------------------------------------------------*/
20 PROCEDURE associate_coll_doc (
21 bank_collection_id_e IN NUMBER,
22 association_method_e IN VARCHAR2,
23 invoice_id_s IN OUT NOCOPY NUMBER,
24 payment_num_s IN OUT NOCOPY NUMBER,
25 associate_flag_s OUT NOCOPY VARCHAR2 )
26 IS
27 x_selected VARCHAR2(1);
28
29 BEGIN
30 x_selected := 'Y';
31 BEGIN
32 IF (association_method_e = 'METHOD1') THEN
33 /* Invoice number AND Due date */
34 SELECT apps.invoice_id, apps.payment_num
35 INTO invoice_id_s, payment_num_s
36 FROM ap_payment_schedules_ALL apps,
37 jl_br_ap_collection_docs jlbl,
38 ap_invoices_ALL apinv
39 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
40 (( substr( apinv.invoice_num,1,10 ) = jlbl.document_number AND
41 apps.invoice_id = apinv.invoice_id ) AND
42 ( apps.due_date = jlbl.due_date )) AND
43 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
44 ( apps.global_attribute11 IS NULL ) AND
45 ( apinv.cancelled_date IS NULL ) AND
46 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
47 AND apinv.invoice_currency_code = jlbl.currency_code;
48
49 ELSIF (association_method_e = 'METHOD2') THEN
50 /* Supplier Site AND Supplier Name */
51 SELECT apps.invoice_id, apps.payment_num
52 INTO invoice_id_s, payment_num_s
53 FROM ap_payment_schedules_ALL apps,
54 jl_br_ap_collection_docs jlbl,
55 ap_invoices_ALL apinv
56 /* Bug # 635847 / 659227
57 ,
58 po_vendor_sites povs
59 */
60 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
61 (( apinv.vendor_site_id = jlbl.vendor_site_id AND
62 apps.invoice_id = apinv.invoice_id ) AND
63 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
64 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
65 apps.invoice_id = apinv.invoice_id ) ) AND
66 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
67 ( apps.global_attribute11 IS NULL ) AND
68 ( apinv.cancelled_date IS NULL ) AND
69 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
70 AND apinv.invoice_currency_code = jlbl.currency_code;
71
72 ELSIF (association_method_e = 'METHOD3') THEN
73 /* Supplier Site OR Supplier Name */
74 BEGIN
75 SELECT apps.invoice_id, apps.payment_num
76 INTO invoice_id_s, payment_num_s
77 FROM ap_payment_schedules_ALL apps,
78 jl_br_ap_collection_docs jlbl,
79 ap_invoices_ALL apinv
80 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
81 (( apinv.vendor_site_id = jlbl.vendor_site_id AND
82 apps.invoice_id = apinv.invoice_id ) ) AND
83 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
84 ( apps.global_attribute11 IS NULL ) AND
85 ( apinv.cancelled_date IS NULL ) AND
86 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
87 AND apinv.invoice_currency_code = jlbl.currency_code;
88 EXCEPTION
89 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
90 x_selected := 'N';
91
92 END;
93 IF x_selected = 'N' THEN
94 SELECT apps.invoice_id, apps.payment_num
95 INTO invoice_id_s, payment_num_s
96 FROM ap_payment_schedules_ALL apps,
97 jl_br_ap_collection_docs jlbl,
98 ap_invoices_ALL apinv
99 /* Bug # 635847 / 659227
100 ,
101 po_vendor_sites povs
102 */
103 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
104 /* (( povs.vendor_site_id = jlbl.vendor_site_id AND */
105 ((apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
106 apps.invoice_id = apinv.invoice_id ) ) AND
107 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
108 ( apps.global_attribute11 IS NULL ) AND
109 ( apinv.cancelled_date IS NULL ) AND
110 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
111 AND apinv.invoice_currency_code = jlbl.currency_code;
112 x_selected:='Y';
113 END IF;
114 ELSIF (association_method_e = 'METHOD4') THEN
115 /* Invoice Number AND Due date AND Supplier Site */
116 SELECT apps.invoice_id, apps.payment_num
117 INTO invoice_id_s, payment_num_s
118 FROM ap_payment_schedules_ALL apps,
119 jl_br_ap_collection_docs jlbl,
120 ap_invoices_ALL apinv
121 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
122 (( substr( apinv.invoice_num,1,10 ) = jlbl.document_number AND
123 apps.invoice_id = apinv.invoice_id ) AND
124 ( apps.due_date = jlbl.due_date ) AND
125 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
126 apps.invoice_id = apinv.invoice_id ) ) AND
127 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
128 ( apps.global_attribute11 IS NULL ) AND
129 ( apinv.cancelled_date IS NULL ) AND
130 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
131 AND apinv.invoice_currency_code = jlbl.currency_code;
132
133 ELSIF (association_method_e = 'METHOD5') THEN
134 /* Invoice Number AND Due date AND Supplier Name */
135 SELECT apps.invoice_id, apps.payment_num
136 INTO invoice_id_s, payment_num_s
137 FROM ap_payment_schedules_ALL apps,
138 jl_br_ap_collection_docs jlbl,
139 ap_invoices_ALL apinv
140 /* Bug # 635847 / 659227
141 ,
142 po_vendor_sites povs
143 */
144 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
145 (( substr( apinv.invoice_num,1,10 ) = jlbl.document_number AND
146 apps.invoice_id = apinv.invoice_id ) AND
147 ( apps.due_date = jlbl.due_date ) AND
148 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
149 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
150 apps.invoice_id = apinv.invoice_id ) ) AND
151 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
152 ( apps.global_attribute11 IS NULL ) AND
153 ( apinv.cancelled_date IS NULL ) AND
154 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
155 AND apinv.invoice_currency_code = jlbl.currency_code;
156
157 ELSIF (association_method_e = 'METHOD6') THEN
158 /* Invoice number AND Gross Amount AND Due Date */
159 SELECT apps.invoice_id, apps.payment_num
160 INTO invoice_id_s, payment_num_s
161 FROM ap_payment_schedules_ALL apps,
162 jl_br_ap_collection_docs jlbl,
163 ap_invoices_ALL apinv
164 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
165 (( substr( apinv.invoice_num,1,10 ) = jlbl.document_number AND
166 apps.invoice_id = apinv.invoice_id ) AND
167 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
168 apinv.invoice_id = apps.invoice_id AND
169 apinv.payment_currency_code = jlbl.currency_code ) AND
170 ( apps.due_date = jlbl.due_date ) ) AND
171 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
172 ( apps.global_attribute11 IS NULL ) AND
173 ( apinv.cancelled_date IS NULL ) AND
174 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
175 AND apinv.invoice_currency_code = jlbl.currency_code;
176
177 ELSIF (association_method_e = 'METHOD7') THEN
178 /* Invoice number AND Gross Amount AND Due Date AND Supplier Site */
179 SELECT apps.invoice_id, apps.payment_num
180 INTO invoice_id_s, payment_num_s
181 FROM ap_payment_schedules_ALL apps,
182 jl_br_ap_collection_docs jlbl,
183 ap_invoices_ALL apinv
184 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
185 (( substr( apinv.invoice_num,1,10 ) = jlbl.document_number AND
186 apps.invoice_id = apinv.invoice_id ) AND
187 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
188 apinv.invoice_id = apps.invoice_id AND
189 apinv.payment_currency_code = jlbl.currency_code ) AND
190 ( apps.due_date = jlbl.due_date ) AND
191 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
192 apps.invoice_id = apinv.invoice_id ) ) AND
193 ( apinv.invoice_type_lookup_code = 'STANDARD') AND
194 ( apps.global_attribute11 IS NULL ) AND
195 ( apinv.cancelled_date IS NULL ) AND
196 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
197 AND apinv.invoice_currency_code = jlbl.currency_code;
198
199 ELSIF (association_method_e = 'METHOD8') THEN
200 /* Invoice number AND Gross Amount 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 (( substr( apinv.invoice_num,1,10 ) = jlbl.document_number AND
212 apps.invoice_id = apinv.invoice_id ) AND
213 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
214 apinv.invoice_id = apps.invoice_id AND
215 apinv.payment_currency_code = jlbl.currency_code ) AND
216 ( apps.due_date = jlbl.due_date ) AND
217 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND*/
218 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
219 apps.invoice_id = apinv.invoice_id ) ) AND
220 ( apinv.invoice_type_lookup_code ='STANDARD') AND
221 ( apps.global_attribute11 IS NULL ) AND
222 ( apinv.cancelled_date IS NULL ) AND
223 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
224 AND apinv.invoice_currency_code = jlbl.currency_code;
225
226 ELSIF (association_method_e = 'METHOD9') THEN
227 /* Invoice number AND Gross Amount AND Due Date AND Supplier Site */
228 /* AND Supplier Name */
229 SELECT apps.invoice_id, apps.payment_num
230 INTO invoice_id_s, payment_num_s
231 FROM ap_payment_schedules_ALL apps,
232 jl_br_ap_collection_docs jlbl,
233 ap_invoices_ALL apinv
234 /* Bug # 635847 / 659227
235 ,
236 po_vendor_sites povs
237 */
238 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
239 (( substr( apinv.invoice_num,1,10 ) = 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.vendor_site_id = jlbl.vendor_site_id AND
246 apps.invoice_id = apinv.invoice_id ) AND
247 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
248 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND */
249 apps.invoice_id = apinv.invoice_id ) ) AND
250 ( apinv.invoice_type_lookup_code ='STANDARD') AND
251 ( apps.global_attribute11 IS NULL ) AND
252 ( apinv.cancelled_date IS NULL ) AND
253 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
254 AND apinv.invoice_currency_code = jlbl.currency_code;
255
256 ELSIF (association_method_e = 'METHOD10') THEN
257 /* Invoice number AND Gross Amount AND Due Date OR Supplier Site */
258 /* AND Supplier Name */
259 BEGIN
260 SELECT apps.invoice_id, apps.payment_num
261 INTO invoice_id_s, payment_num_s
262 FROM ap_payment_schedules_ALL apps,
263 jl_br_ap_collection_docs jlbl,
264 ap_invoices_ALL apinv
265 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
266 (( substr( apinv.invoice_num,1,10 ) = jlbl.document_number AND
267 apps.invoice_id = apinv.invoice_id ) AND
268 ( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
269 apinv.invoice_id = apps.invoice_id AND
270 apinv.payment_currency_code = jlbl.currency_code ) AND
271 ( apps.due_date = jlbl.due_date ) ) AND
272 ( apinv.invoice_type_lookup_code ='STANDARD') AND
273 ( apps.global_attribute11 IS NULL ) AND
274 ( apinv.cancelled_date IS NULL ) AND
275 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
276 AND apinv.invoice_currency_code = jlbl.currency_code;
277 EXCEPTION
278 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
279 x_selected := 'N';
280
281 END;
282 IF x_selected = 'N' THEN
283 SELECT apps.invoice_id, apps.payment_num
284 INTO invoice_id_s, payment_num_s
285 FROM ap_payment_schedules_ALL apps,
286 jl_br_ap_collection_docs jlbl,
287 ap_invoices_ALL apinv
288 /* Bug # 635847 / 659227
289 ,
290 po_vendor_sites povs
291 */
292 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
293 (( apinv.vendor_site_id = jlbl.vendor_site_id AND
294 apps.invoice_id = apinv.invoice_id ) AND
295 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND */
296 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND */
297 apps.invoice_id = apinv.invoice_id ) ) AND
298 ( apinv.invoice_type_lookup_code ='STANDARD') AND
299 ( apps.global_attribute11 IS NULL ) AND
300 ( apinv.cancelled_date IS NULL ) AND
301 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
302 AND apinv.invoice_currency_code = jlbl.currency_code;
303 x_selected:= 'Y';
304 END IF;
305
306 ELSIF (association_method_e = 'METHOD11') THEN
307 /* Invoice number OR Gross Amount AND Due Date AND Supplier Site */
308 BEGIN
309 SELECT apps.invoice_id, apps.payment_num
310 INTO invoice_id_s, payment_num_s
311 FROM ap_payment_schedules_ALL apps,
312 jl_br_ap_collection_docs jlbl,
313 ap_invoices_ALL apinv
314 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
315 (( substr( apinv.invoice_num,1,10 ) = jlbl.document_number AND
316 apps.invoice_id = apinv.invoice_id ) ) AND
317 ( apinv.invoice_type_lookup_code ='STANDARD') AND
318 ( apps.global_attribute11 IS NULL ) AND
319 ( apinv.cancelled_date IS NULL ) AND
320 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
321 AND apinv.invoice_currency_code = jlbl.currency_code;
322 EXCEPTION
323 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
324 x_selected := 'N';
325
326 END;
327 IF x_selected = 'N' THEN
328 SELECT apps.invoice_id, apps.payment_num
329 INTO invoice_id_s, payment_num_s
330 FROM ap_payment_schedules_ALL apps,
331 jl_br_ap_collection_docs jlbl,
332 ap_invoices_ALL apinv
333 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
334 (( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
335 apinv.invoice_id = apps.invoice_id AND
336 apinv.payment_currency_code = jlbl.currency_code ) AND
337 ( apps.due_date = jlbl.due_date ) AND
338 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
339 apps.invoice_id = apinv.invoice_id ) ) AND
340 ( apinv.invoice_type_lookup_code ='STANDARD') AND
341 ( apps.global_attribute11 IS NULL ) AND
342 ( apinv.cancelled_date IS NULL ) AND
343 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
344 AND apinv.invoice_currency_code = jlbl.currency_code;
345 x_selected:= 'Y';
346 END IF;
347 ELSIF (association_method_e = 'METHOD12') THEN
351 FROM ap_payment_schedules_ALL apps,
348 /* Gross Amount AND Due Date AND Supplier Site */
349 SELECT apps.invoice_id, apps.payment_num
350 INTO invoice_id_s, payment_num_s
352 jl_br_ap_collection_docs jlbl,
353 ap_invoices_ALL apinv
354 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
355 (( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
356 apinv.invoice_id = apps.invoice_id AND
357 apinv.payment_currency_code = jlbl.currency_code ) AND
358 ( apps.due_date = jlbl.due_date ) AND
359 ( apinv.vendor_site_id = jlbl.vendor_site_id AND
360 apps.invoice_id = apinv.invoice_id ) ) AND
361 ( apinv.invoice_type_lookup_code ='STANDARD') AND
362 ( apps.global_attribute11 IS NULL ) AND
363 ( apinv.cancelled_date IS NULL ) AND
364 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
365 AND apinv.invoice_currency_code = jlbl.currency_code;
366
367 ELSIF (association_method_e = 'METHOD13') THEN
368 /* Gross Amount AND Due Date AND Supplier Name */
369 SELECT apps.invoice_id, apps.payment_num
370 INTO invoice_id_s, payment_num_s
371 FROM ap_payment_schedules_ALL apps,
372 jl_br_ap_collection_docs jlbl,
373 ap_invoices_ALL apinv
374 /* Bug # 635847 / 659227
375 ,
376 po_vendor_sites povs
377 */
378 WHERE jlbl.bank_collection_id = bank_collection_id_e AND
379 (( nvl(apps.gross_amount,0) = nvl(jlbl.amount,0) AND
380 apinv.invoice_id = apps.invoice_id AND
381 apinv.payment_currency_code = jlbl.currency_code ) AND
382 ( apps.due_date = jlbl.due_date ) AND
383 /* ( povs.vendor_site_id = jlbl.vendor_site_id AND*/
384 ( apinv.vendor_id = jlbl.vendor_id AND /*povs.vendor_id AND*/
385 apps.invoice_id = apinv.invoice_id ) ) AND
386 ( apinv.invoice_type_lookup_code ='STANDARD') AND
387 ( apps.global_attribute11 IS NULL ) AND
388 ( apinv.cancelled_date IS NULL ) AND
389 (nvl(substr(apinv.global_attribute1,1,1),'N') = 'Y')
390 AND apinv.invoice_currency_code = jlbl.currency_code;
391
392 END IF;
393 EXCEPTION
394 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
395 x_selected := 'N';
396 END;
397 IF x_selected = 'Y' THEN /* Create the link with two tables */
398 UPDATE jl_br_ap_collection_docs
399 SET invoice_id = invoice_id_s,
400 payment_num = payment_num_s
401 WHERE bank_collection_id = bank_collection_id_e;
402
403 -- Bug Number 659227 / R11 Patch / May 98 (Copying Y to GA8)
404 UPDATE ap_payment_schedules
405 SET global_attribute11 = bank_collection_id_e,
406 global_attribute8 = 'Y'
407 WHERE invoice_id = invoice_id_s
408 AND payment_num = payment_num_s;
409 associate_flag_s := 'Y';
410 ELSE
411 associate_flag_s := 'N';
412 END IF;
413 END associate_coll_doc;
414
415 BEGIN
416 SELECT global_attribute1,
417 global_attribute2
418 INTO x_enable_bank_coll,
419 x_enable_association
420 FROM ap_system_parameters;
421
422 IF x_enable_bank_coll = 'Y' THEN
423 IF x_enable_association = 'Y' THEN
424 associate_coll_doc( bank_collection_id_e, association_method_e,
425 invoice_id_s, payment_num_s, associate_flag_s );
426 IF associate_flag_s = 'Y' THEN /* If associated, release both*/
427 UPDATE ap_payment_schedules
428 SET hold_flag = 'N'
429 WHERE invoice_id = invoice_id_s
430 AND payment_num = payment_num_s;
431
432 UPDATE jl_br_ap_collection_docs
433 SET hold_flag = 'N'
434 WHERE bank_collection_id = bank_collection_id_e;
435
436 ELSE /* If not associated, hold the collection document */
437 UPDATE jl_br_ap_collection_docs
438 SET hold_flag = 'Y'
439 WHERE bank_collection_id = bank_collection_id_e;
440
441 END IF;
442 ELSE /* If association is not enabled, hold the collection document */
443 UPDATE jl_br_ap_collection_docs
444 SET hold_flag = 'Y'
445 WHERE bank_collection_id = bank_collection_id_e;
446
447 END IF;
448 ELSE /* If bank collection is not enabled, release the collection document*/
449 UPDATE jl_br_ap_collection_docs
450 SET hold_flag = 'N'
451 WHERE bank_collection_id = bank_collection_id_e;
452
453 END IF;
454 END jl_br_ap_associate_coll_doc;
455 /*-----------------------------------------------------------*/
456 /*<<<<< JL_BR_AP_ASSOCIATE_TRADE_NOTE >>>>>*/
457 /*-----------------------------------------------------------*/
458 PROCEDURE jl_br_ap_associate_trade_note (
459 invoice_id_e IN NUMBER,
460 payment_num_e IN NUMBER,
461 association_method_e IN VARCHAR2,
462 bank_collection_id_s IN OUT NOCOPY NUMBER,
463 associate_flag_s IN OUT NOCOPY VARCHAR2 )
464 IS
465 x_enable_bank_coll ap_system_parameters.global_attribute1%TYPE;
466 x_enable_association ap_system_parameters.global_attribute2%TYPE;
467 x_payment_status_flag ap_payment_schedules.payment_status_flag%TYPE;
468 /*-----------------------------------------------------------*/
469 /* ASSOCIATE_TRADE_NOTE */
470 /*-----------------------------------------------------------*/
471 PROCEDURE associate_trade_note (
472 invoice_id_e IN NUMBER,
473 payment_num_e IN NUMBER,
474 association_method_e IN VARCHAR2,
478 x_selected VARCHAR2(1);
475 bank_collection_id_s IN OUT NOCOPY NUMBER,
476 associate_flag_s OUT NOCOPY VARCHAR2 )
477 IS
479
480 BEGIN
481 x_selected := 'Y';
482 BEGIN
483 IF (association_method_e = 'METHOD1') THEN
484 /* Invoice number AND Due date */
485 SELECT bank_collection_id
486 INTO bank_collection_id_s
487 FROM ap_payment_schedules apps,
488 jl_br_ap_collection_docs_ALL jlbl,
489 ap_invoices_ALL apinv
490 WHERE apps.invoice_id = invoice_id_e AND
491 apps.payment_num = payment_num_e AND
492 (( apinv.invoice_id = apps.invoice_id AND
493 jlbl.document_number = substr( apinv.invoice_num,1,10 )) AND
494 ( jlbl.due_date = apps.due_date ) ) AND
495 ( jlbl.invoice_id IS NULL AND
496 jlbl.payment_num IS NULL ) AND
497 ( jlbl.status_lookup_code = 'ACTIVE')
498 AND apinv.invoice_currency_code = jlbl.currency_code;
499
500 ELSIF (association_method_e = 'METHOD2') THEN
501 /* Supplier Site and Supplier Name */
502 SELECT bank_collection_id
503 INTO bank_collection_id_s
504 FROM ap_payment_schedules apps,
505 jl_br_ap_collection_docs_ALL jlbl,
506 ap_invoices_ALL apinv
507 /* Bug # 635847 / 659227
508 ,
509 po_vendor_sites povs
510 */
511 WHERE apps.invoice_id = invoice_id_e AND
512 apps.payment_num = payment_num_e AND
513 (( apinv.invoice_id = apps.invoice_id AND
514 jlbl.vendor_site_id = apinv.vendor_site_id ) AND
515 ( apinv.invoice_id = apps.invoice_id AND
516 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
517 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
518 ( jlbl.invoice_id IS NULL AND
519 jlbl.payment_num IS NULL ) AND
520 ( jlbl.status_lookup_code = 'ACTIVE')
521 AND apinv.invoice_currency_code = jlbl.currency_code;
522
523 ELSIF (association_method_e = 'METHOD3') THEN
524 /* Supplier Site OR Supplier Name */
525 BEGIN
526 SELECT bank_collection_id
527 INTO bank_collection_id_s
528 FROM ap_payment_schedules apps,
529 jl_br_ap_collection_docs_ALL jlbl,
530 ap_invoices_ALL apinv
531 WHERE apps.invoice_id = invoice_id_e AND
532 apps.payment_num = payment_num_e AND
533 (( apinv.invoice_id = apps.invoice_id AND
534 jlbl.vendor_site_id = apinv.vendor_site_id )) AND
535 ( jlbl.invoice_id IS NULL AND
536 jlbl.payment_num IS NULL ) AND
537 ( jlbl.status_lookup_code = 'ACTIVE')
538 AND apinv.invoice_currency_code = jlbl.currency_code;
539 EXCEPTION
540 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
541 x_selected := 'N';
542
543 END;
544 IF x_selected = 'N' THEN
545 SELECT bank_collection_id
546 INTO bank_collection_id_s
547 FROM ap_payment_schedules apps,
548 jl_br_ap_collection_docs_ALL jlbl,
549 ap_invoices_ALL apinv
550 /* Bug # 635847 / 659227
551 ,
552 po_vendor_sites povs
553 */
554 WHERE apps.invoice_id = invoice_id_e AND
555 apps.payment_num = payment_num_e AND
556 (( apinv.invoice_id = apps.invoice_id AND
557 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
558 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
559 ( jlbl.invoice_id IS NULL AND
560 jlbl.payment_num IS NULL ) AND
561 ( jlbl.status_lookup_code = 'ACTIVE')
562 AND apinv.invoice_currency_code = jlbl.currency_code;
563 x_selected:= 'Y';
564 END IF;
565 ELSIF (association_method_e = 'METHOD4') THEN
566 /* Invoice Number AND Due date AND Supplier Site */
567 SELECT bank_collection_id
568 INTO bank_collection_id_s
569 FROM ap_payment_schedules apps,
570 jl_br_ap_collection_docs_ALL jlbl,
571 ap_invoices_ALL apinv
572 WHERE apps.invoice_id = invoice_id_e AND
573 apps.payment_num = payment_num_e AND
574 (( apinv.invoice_id = apps.invoice_id AND
575 jlbl.document_number = substr( apinv.invoice_num,1,10 ) ) AND
576 ( jlbl.due_date = apps.due_date ) AND
577 ( apinv.invoice_id = apps.invoice_id AND
578 jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
579 ( jlbl.invoice_id IS NULL AND
580 jlbl.payment_num IS NULL ) AND
581 ( jlbl.status_lookup_code = 'ACTIVE')
582 AND apinv.invoice_currency_code = jlbl.currency_code;
583
584 ELSIF (association_method_e = 'METHOD5') THEN
585 /* Invoice Number AND Due date AND Supplier Name */
586 SELECT bank_collection_id
587 INTO bank_collection_id_s
588 FROM ap_payment_schedules apps,
589 jl_br_ap_collection_docs_ALL jlbl,
590 ap_invoices_ALL apinv
591 /* Bug # 635847 / 659227
592 ,
593 po_vendor_sites povs
594 */
595 WHERE apps.invoice_id = invoice_id_e AND
596 apps.payment_num = payment_num_e AND
597 (( apinv.invoice_id = apps.invoice_id AND
598 jlbl.document_number = substr( apinv.invoice_num,1,10 ) ) AND
599 ( jlbl.due_date = apps.due_date ) AND
600 ( apinv.invoice_id = apps.invoice_id AND
601 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
602 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
603 ( jlbl.invoice_id IS NULL AND
607
604 jlbl.payment_num IS NULL ) AND
605 ( jlbl.status_lookup_code = 'ACTIVE')
606 AND apinv.invoice_currency_code = jlbl.currency_code;
608 ELSIF (association_method_e = 'METHOD6') THEN
609 /* Invoice number AND Gross Amount AND Due Date */
610 SELECT bank_collection_id
611 INTO bank_collection_id_s
612 FROM ap_payment_schedules apps,
613 jl_br_ap_collection_docs_ALL jlbl,
614 ap_invoices_ALL apinv
615 WHERE apps.invoice_id = invoice_id_e AND
616 apps.payment_num = payment_num_e AND
617 (( apinv.invoice_id = apps.invoice_id AND
618 jlbl.document_number = substr( apinv.invoice_num,1,10 ) ) AND
619 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
620 apinv.invoice_id = apps.invoice_id AND
621 jlbl.currency_code = apinv.payment_currency_code ) AND
622 ( jlbl.due_date = apps.due_date ) ) AND
623 ( jlbl.invoice_id IS NULL AND
624 jlbl.payment_num IS NULL ) AND
625 ( jlbl.status_lookup_code = 'ACTIVE')
626 AND apinv.invoice_currency_code = jlbl.currency_code;
627
628 ELSIF (association_method_e = 'METHOD7') THEN
629 /* Invoice number AND Gross Amount AND Due Date AND Supplier Site */
630 SELECT bank_collection_id
631 INTO bank_collection_id_s
632 FROM ap_payment_schedules apps,
633 jl_br_ap_collection_docs_ALL jlbl,
634 ap_invoices_ALL apinv
635 WHERE apps.invoice_id = invoice_id_e AND
636 apps.payment_num = payment_num_e AND
637 (( apinv.invoice_id = apps.invoice_id AND
638 jlbl.document_number = substr( apinv.invoice_num,1,10 ) ) AND
639 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
640 apinv.invoice_id = apps.invoice_id AND
641 jlbl.currency_code = apinv.payment_currency_code ) AND
642 ( jlbl.due_date = apps.due_date ) AND
643 ( apinv.invoice_id = apps.invoice_id AND
644 jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
645 ( jlbl.invoice_id IS NULL AND
646 jlbl.payment_num IS NULL ) AND
647 (jlbl.status_lookup_code = 'ACTIVE')
648 AND apinv.invoice_currency_code = jlbl.currency_code;
649
650 ELSIF (association_method_e = 'METHOD8') THEN
651 /* Invoice number AND Gross Amount AND Due Date AND Supplier Name */
652 SELECT bank_collection_id
653 INTO bank_collection_id_s
654 FROM ap_payment_schedules apps,
655 jl_br_ap_collection_docs_ALL jlbl,
656 ap_invoices_ALL apinv
657 /* Bug # 635847 / 659227
658 ,
659 po_vendor_sites povs
660 */
661 WHERE apps.invoice_id = invoice_id_e AND
662 apps.payment_num = payment_num_e AND
663 (( apinv.invoice_id = apps.invoice_id AND
664 jlbl.document_number = substr( apinv.invoice_num,1,10 ) ) AND
665 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
666 apinv.invoice_id = apps.invoice_id AND
667 jlbl.currency_code = apinv.payment_currency_code ) AND
668 ( jlbl.due_date = apps.due_date ) AND
669 ( apinv.invoice_id = apps.invoice_id AND
670 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
671 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
672 ( jlbl.invoice_id IS NULL AND
673 jlbl.payment_num IS NULL ) AND
674 ( jlbl.status_lookup_code = 'ACTIVE')
675 AND apinv.invoice_currency_code = jlbl.currency_code;
676
677 ELSIF (association_method_e = 'METHOD9') THEN
678 /* Invoice number AND Gross Amount AND Due Date AND Supplier Site */
679 /* AND Supplier Name */
680 SELECT bank_collection_id
681 INTO bank_collection_id_s
682 FROM ap_payment_schedules apps,
683 jl_br_ap_collection_docs_ALL jlbl,
684 ap_invoices_ALL apinv
685 /* Bug # 635847 / 659227
686 ,
687 po_vendor_sites povs
688 */
689 WHERE apps.invoice_id = invoice_id_e AND
690 apps.payment_num = payment_num_e AND
691 (( apinv.invoice_id = apps.invoice_id AND
692 jlbl.document_number = substr( apinv.invoice_num,1,10 ) ) AND
693 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
694 apinv.invoice_id = apps.invoice_id AND
695 jlbl.currency_code = apinv.payment_currency_code ) AND
696 ( jlbl.due_date = apps.due_date ) AND
697 ( apinv.invoice_id = apps.invoice_id AND
698 jlbl.vendor_site_id = apinv.vendor_site_id ) AND
699 ( apinv.invoice_id = apps.invoice_id AND
700 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
701 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
702 ( jlbl.invoice_id IS NULL AND
703 jlbl.payment_num IS NULL ) AND
704 ( jlbl.status_lookup_code = 'ACTIVE')
705 AND apinv.invoice_currency_code = jlbl.currency_code;
706
707 ELSIF (association_method_e = 'METHOD10') THEN
708 /* Invoice number AND Gross Amount AND Due Date OR Supplier Site */
709 /* AND Supplier Name */
710 BEGIN
711 SELECT bank_collection_id
712 INTO bank_collection_id_s
713 FROM ap_payment_schedules apps,
714 jl_br_ap_collection_docs_ALL jlbl,
715 ap_invoices_ALL apinv
716 WHERE apps.invoice_id = invoice_id_e AND
717 apps.payment_num = payment_num_e AND
718 (( apinv.invoice_id = apps.invoice_id AND
719 jlbl.document_number = substr( apinv.invoice_num,1,10 ) ) AND
720 ( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
721 apinv.invoice_id = apps.invoice_id AND
725 jlbl.payment_num IS NULL ) AND
722 jlbl.currency_code = apinv.payment_currency_code ) AND
723 ( jlbl.due_date = apps.due_date ) ) AND
724 ( jlbl.invoice_id IS NULL AND
726 ( jlbl.status_lookup_code = 'ACTIVE')
727 AND apinv.invoice_currency_code = jlbl.currency_code;
728 EXCEPTION
729 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
730 x_selected := 'N';
731
732 END;
733 IF x_selected = 'N' THEN
734 SELECT bank_collection_id
735 INTO bank_collection_id_s
736 FROM ap_payment_schedules apps,
737 jl_br_ap_collection_docs_ALL jlbl,
738 ap_invoices_ALL apinv
739 /* Bug # 635847 / 659227
740 ,
741 po_vendor_sites povs
742 */
743 WHERE apps.invoice_id = invoice_id_e AND
744 apps.payment_num = payment_num_e AND
745 (( apinv.invoice_id = apps.invoice_id AND
746 jlbl.vendor_site_id = apinv.vendor_site_id ) AND
747 ( apinv.invoice_id = apps.invoice_id AND
748 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
749 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
750 ( jlbl.invoice_id IS NULL AND
751 jlbl.payment_num IS NULL ) AND
752 ( jlbl.status_lookup_code = 'ACTIVE')
753 AND apinv.invoice_currency_code = jlbl.currency_code;
754 x_selected:= 'Y';
755 END IF;
756
757 ELSIF (association_method_e = 'METHOD11') THEN
758 /* Invoice number OR Gross Amount AND Due Date AND Supplier Site */
759 BEGIN
760 SELECT bank_collection_id
761 INTO bank_collection_id_s
762 FROM ap_payment_schedules apps,
763 jl_br_ap_collection_docs_ALL jlbl,
764 ap_invoices_ALL apinv
765 WHERE apps.invoice_id = invoice_id_e AND
766 apps.payment_num = payment_num_e AND
767 (( apinv.invoice_id = apps.invoice_id AND
768 jlbl.document_number = substr( apinv.invoice_num,1,10 ) ) ) 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 EXCEPTION
774 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
775 x_selected := 'N';
776
777 END;
778 IF x_selected = 'N' THEN
779 SELECT bank_collection_id
780 INTO bank_collection_id_s
781 FROM ap_payment_schedules apps,
782 jl_br_ap_collection_docs_ALL jlbl,
783 ap_invoices_ALL apinv
784 WHERE apps.invoice_id = invoice_id_e AND
785 apps.payment_num = payment_num_e AND
786 (( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
787 apinv.invoice_id = apps.invoice_id AND
788 jlbl.currency_code = apinv.payment_currency_code ) AND
789 ( jlbl.due_date = apps.due_date ) AND
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 x_selected:= 'Y';
797 END IF;
798 ELSIF (association_method_e = 'METHOD12') THEN
799 /* Gross Amount AND Due Date AND Supplier Site */
800 SELECT bank_collection_id
801 INTO bank_collection_id_s
802 FROM ap_payment_schedules apps,
803 jl_br_ap_collection_docs_ALL jlbl,
804 ap_invoices_ALL apinv
805 WHERE apps.invoice_id = invoice_id_e AND
806 apps.payment_num = payment_num_e AND
807 (( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
808 apinv.invoice_id = apps.invoice_id AND
809 jlbl.currency_code = apinv.payment_currency_code ) AND
810 ( jlbl.due_date = apps.due_date ) AND
811 ( apinv.invoice_id = apps.invoice_id AND
812 jlbl.vendor_site_id = apinv.vendor_site_id ) ) AND
813 ( jlbl.invoice_id IS NULL AND
814 jlbl.payment_num IS NULL ) AND
815 ( jlbl.status_lookup_code = 'ACTIVE')
816 AND apinv.invoice_currency_code = jlbl.currency_code;
817
818 ELSIF (association_method_e = 'METHOD13') THEN
819 /* Gross Amount AND Due Date AND Supplier Name */
820 SELECT bank_collection_id
821 INTO bank_collection_id_s
822 FROM ap_payment_schedules apps,
823 jl_br_ap_collection_docs_ALL jlbl,
824 ap_invoices_ALL apinv
825 /* Bug # 635847 / 659227
826 ,
827 po_vendor_sites povs
828 */
829 WHERE apps.invoice_id = invoice_id_e AND
830 apps.payment_num = payment_num_e AND
831 (( nvl(jlbl.amount, 0) = nvl(apps.gross_amount, 0) AND
832 apinv.invoice_id = apps.invoice_id AND
833 jlbl.currency_code = apinv.payment_currency_code ) AND
834 ( jlbl.due_date = apps.due_date ) AND
835 ( apinv.invoice_id = apps.invoice_id AND
836 /* povs.vendor_id*/ jlbl.vendor_id = apinv.vendor_id )) AND
837 /* jlbl.vendor_site_id = povs.vendor_site_id ) ) AND*/
838 ( jlbl.invoice_id IS NULL AND
839 jlbl.payment_num IS NULL ) AND
840 ( jlbl.status_lookup_code = 'ACTIVE')
841 AND apinv.invoice_currency_code = jlbl.currency_code;
842
843 END IF;
844 EXCEPTION
845 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
846 x_selected := 'N';
850 UPDATE ap_payment_schedules
847 END;
848 IF x_selected = 'Y' THEN /* Create the link with two tables */
849 -- Bug Number 659227 / R11 Patch / May 98 (Copying Y to GA8)
851 SET global_attribute11 = bank_collection_id_s,
852 global_attribute8 = 'Y'
853 WHERE invoice_id = invoice_id_e AND
854 payment_num = payment_num_e;
855 UPDATE jl_br_ap_collection_docs
856 SET invoice_id = invoice_id_e,
857 payment_num = payment_num_e
858 WHERE bank_collection_id = bank_collection_id_s;
859 associate_flag_s := 'Y';
860
861 ELSE
862 associate_flag_s := 'N';
863 END IF;
864 END associate_trade_note;
865
866 BEGIN
867 SELECT global_attribute1
868 INTO x_enable_bank_coll
869 FROM ap_system_parameters;
870
871 SELECT global_attribute1
872 INTO x_enable_association
873 FROM ap_invoices
874 WHERE invoice_id = invoice_id_e;
875
876 IF x_enable_bank_coll = 'Y' THEN
877 IF x_enable_association = 'Y' THEN
878 associate_trade_note( invoice_id_e, payment_num_e,
879 association_method_e, bank_collection_id_s,
880 associate_flag_s );
881
882 IF associate_flag_s = 'Y' THEN /* Se associou, */
883 /* libera os dois */
884 UPDATE ap_payment_schedules
885 SET hold_flag = 'N'
886 WHERE invoice_id = invoice_id_e
887 AND payment_num = payment_num_e;
888
889 UPDATE jl_br_ap_collection_docs
890 SET hold_flag = 'N'
891 WHERE bank_collection_id = bank_collection_id_s;
892
893 ELSE /* If not associated and payment schedule not paid */
894 /* then hold the payment schedule */
895 SELECT payment_status_flag
896 INTO x_payment_status_flag
897 FROM ap_payment_schedules
898 WHERE invoice_id = invoice_id_e
899 AND payment_num = payment_num_e;
900
901 IF (x_payment_status_flag = 'N') THEN
902 UPDATE ap_payment_schedules
903 SET hold_flag = 'Y'
904 WHERE invoice_id = invoice_id_e
905 AND payment_num = payment_num_e;
906 END IF;
907 END IF;
908 ELSE /* If association not enabled, hold the payment schedule */
909 UPDATE ap_payment_schedules
910 SET hold_flag = 'Y'
911 WHERE invoice_id = invoice_id_e
912 AND payment_num = payment_num_e;
913
914 END IF;
915 ELSE /* If bank collection is not enabled, release the payment schedule */
916 UPDATE ap_payment_schedules
917 SET hold_flag = 'N'
918 WHERE invoice_id = invoice_id_e
919 AND payment_num = payment_num_e;
920
921 END IF;
922 END jl_br_ap_associate_trade_note;
923
924 END JL_BR_AP_ASSOCIATE_COLLECTION;