[Home] [Help]
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
351: FROM ap_payment_schedules_ALL apps,
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
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
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:
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 */
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;
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;
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;
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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';
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 */