1 PACKAGE BODY ARP_LOCKBOX_PKG AS
2 /* $Header: ARLBPIMB.pls 115.8 2003/10/10 14:24:51 mraymond ship $ */
3 --
4 /* Bugfix 2284014 */
5 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7 PROCEDURE lock_trans_data(
8 p_row_id IN VARCHAR2,
9 p_transmission_record_id IN NUMBER,
10 p_lockbox_number IN VARCHAR2,
11 p_batch_name IN VARCHAR2,
12 p_item_number IN VARCHAR2,
13 p_check_number IN VARCHAR2,
14 p_overflow_sequence IN NUMBER,
15 p_record_type IN VARCHAR2,
16 p_comments IN VARCHAR2,
17 p_origination IN VARCHAR2,
18 p_destination_account IN VARCHAR2,
19 p_deposit_date IN DATE,
20 p_gl_date IN DATE,
21 p_deposit_time IN VARCHAR2,
22 p_currency_code IN VARCHAR2,
23 p_exchange_rate_type IN VARCHAR2,
24 p_exchange_rate IN NUMBER,
25 p_transmission_record_count IN NUMBER,
26 p_transmission_amount IN NUMBER,
27 p_lockbox_batch_count IN NUMBER,
28 p_lockbox_amount IN NUMBER,
29 p_lockbox_record_count IN NUMBER,
30 p_batch_record_count IN NUMBER,
31 p_batch_amount IN NUMBER,
32 p_transferred_receipt_count IN NUMBER,
33 p_transferred_receipt_amount IN NUMBER,
34 p_overflow_indicator IN VARCHAR2,
35 p_receipt_date IN DATE,
36 p_receipt_method IN VARCHAR2,
37 p_receipt_method_id IN NUMBER,
38 p_remittance_amount IN NUMBER,
39 p_customer_number IN VARCHAR2,
40 p_customer_id IN NUMBER,
41 p_bill_to_location IN VARCHAR2,
42 p_customer_site_use_id IN NUMBER,
43 p_transit_routing_number IN VARCHAR2,
44 p_account IN VARCHAR2,
45 p_customer_bank_account_id IN NUMBER,
46 p_amount_applied1 IN NUMBER,
47 p_invoice1 IN VARCHAR2,
48 p_invoice1_installment IN NUMBER,
49 p_invoice1_status IN VARCHAR2,
50 p_amount_applied2 IN NUMBER,
51 p_invoice2 IN VARCHAR2,
52 p_invoice2_installment IN NUMBER,
53 p_invoice2_status IN VARCHAR2,
54 p_amount_applied3 IN NUMBER,
55 p_invoice3 IN VARCHAR2,
56 p_invoice3_installment IN NUMBER,
57 p_invoice3_status IN VARCHAR2,
58 p_amount_applied4 IN NUMBER,
59 p_invoice4 IN VARCHAR2,
60 p_invoice4_installment IN NUMBER,
61 p_invoice4_status IN VARCHAR2,
62 p_amount_applied5 IN NUMBER,
63 p_invoice5 IN VARCHAR2,
64 p_invoice5_installment IN NUMBER,
65 p_invoice5_status IN VARCHAR2,
66 p_amount_applied6 IN NUMBER,
67 p_invoice6 IN VARCHAR2,
68 p_invoice6_installment IN NUMBER,
69 p_invoice6_status IN VARCHAR2,
70 p_amount_applied7 IN NUMBER,
71 p_invoice7 IN VARCHAR2,
72 p_invoice7_installment IN NUMBER,
73 p_invoice7_status IN VARCHAR2,
74 p_amount_applied8 IN NUMBER,
75 p_invoice8 IN VARCHAR2,
76 p_invoice8_installment IN NUMBER,
77 p_invoice8_status IN VARCHAR2,
78 p_attribute1 IN VARCHAR2,
79 p_attribute2 IN VARCHAR2,
80 p_attribute3 IN VARCHAR2,
81 p_attribute4 IN VARCHAR2,
82 p_attribute5 IN VARCHAR2,
83 p_attribute6 IN VARCHAR2,
84 p_attribute7 IN VARCHAR2,
85 p_attribute8 IN VARCHAR2,
86 p_attribute9 IN VARCHAR2,
87 p_attribute10 IN VARCHAR2,
88 p_attribute11 IN VARCHAR2,
89 p_attribute12 IN VARCHAR2,
90 p_attribute13 IN VARCHAR2,
91 p_attribute14 IN VARCHAR2,
92 p_attribute15 IN VARCHAR2,
93 p_status IN VARCHAR2,
94 p_matching1_date IN DATE,
95 p_matching2_date IN DATE,
96 p_matching3_date IN DATE,
97 p_matching4_date IN DATE,
98 p_matching5_date IN DATE,
99 p_matching6_date IN DATE,
100 p_matching7_date IN DATE,
101 p_matching8_date IN DATE,
102 p_amount_applied_from1 IN NUMBER,
103 p_trans_to_receipt_rate1 IN NUMBER,
104 p_invoice_currency_code1 IN VARCHAR2,
105 p_amount_applied_from2 IN NUMBER,
106 p_trans_to_receipt_rate2 IN NUMBER,
107 p_invoice_currency_code2 IN VARCHAR2,
108 p_amount_applied_from3 IN NUMBER,
109 p_trans_to_receipt_rate3 IN NUMBER,
110 p_invoice_currency_code3 IN VARCHAR2,
111 p_amount_applied_from4 IN NUMBER,
112 p_trans_to_receipt_rate4 IN NUMBER,
113 p_invoice_currency_code4 IN VARCHAR2,
114 p_amount_applied_from5 IN NUMBER,
115 p_trans_to_receipt_rate5 IN NUMBER,
116 p_invoice_currency_code5 IN VARCHAR2,
117 p_amount_applied_from6 IN NUMBER,
118 p_trans_to_receipt_rate6 IN NUMBER,
119 p_invoice_currency_code6 IN VARCHAR2,
120 p_amount_applied_from7 IN NUMBER,
121 p_trans_to_receipt_rate7 IN NUMBER,
122 p_invoice_currency_code7 IN VARCHAR2,
123 p_amount_applied_from8 IN NUMBER,
124 p_trans_to_receipt_rate8 IN NUMBER,
125 p_invoice_currency_code8 IN VARCHAR2,
126 p_ussgl_transaction_code IN VARCHAR2,
127 p_ussgl_transaction_code1 IN VARCHAR2,
128 p_ussgl_transaction_code2 IN VARCHAR2,
129 p_ussgl_transaction_code3 IN VARCHAR2,
130 p_ussgl_transaction_code4 IN VARCHAR2,
131 p_ussgl_transaction_code5 IN VARCHAR2,
132 p_ussgl_transaction_code6 IN VARCHAR2,
133 p_ussgl_transaction_code7 IN VARCHAR2,
134 p_ussgl_transaction_code8 IN VARCHAR2
135 ) IS
136 CURSOR C IS
137 SELECT *
138 FROM ar_payments_interface
139 WHERE rowid = p_row_id
140 FOR UPDATE of transmission_record_id NOWAIT;
141
142 Recinfo C%ROWTYPE;
143 BEGIN
144 IF PG_DEBUG in ('Y', 'C') THEN
145 arp_util.debug( 'arp_lockbox_pkg.lock_trans_data()+' );
146 END IF;
147 OPEN C;
148 FETCH C INTO Recinfo;
149 if (C%NOTFOUND) then
150 CLOSE C;
151 FND_MESSAGE.Set_Name( 'FND', 'FORM_RECORD_DELETED');
152 APP_EXCEPTION.Raise_Exception;
153 end if;
154 CLOSE C;
155 if(
156 ((Recinfo.transmission_record_id = p_transmission_record_id)
157 OR ( (Recinfo.transmission_record_id IS NULL)
158 AND (p_transmission_record_id IS NULL)))
159 AND ((Recinfo.lockbox_number = p_lockbox_number)
160 OR ( (Recinfo.lockbox_number IS NULL)
161 AND (p_lockbox_number IS NULL)))
162 AND ((Recinfo.batch_name = p_batch_name)
163 OR ( (Recinfo.batch_name IS NULL)
164 AND (p_batch_name IS NULL)))
165 AND ((Recinfo.item_number = p_item_number)
166 OR ( (Recinfo.item_number IS NULL)
167 AND (p_item_number IS NULL)))
168 AND ((Recinfo.check_number = p_check_number)
169 OR ( (Recinfo.check_number IS NULL)
170 AND (p_check_number IS NULL)))
171 AND ((Recinfo.overflow_sequence = p_overflow_sequence)
172 OR ( (Recinfo.overflow_sequence IS NULL)
173 AND (p_overflow_sequence IS NULL)))
174 AND (Recinfo.record_type = p_record_type)
175 AND ((Recinfo.comments = p_comments)
176 OR ( (Recinfo.comments IS NULL)
177 AND (p_comments IS NULL)))
178 AND ((Recinfo.origination = p_origination)
179 OR ( (Recinfo.origination IS NULL)
180 AND (p_origination IS NULL)))
181 AND ((Recinfo.destination_account = p_destination_account)
182 OR ( (Recinfo.destination_account IS NULL)
183 AND (p_destination_account IS NULL)))
184 AND ((Recinfo.deposit_date = p_deposit_date)
185 OR ( (Recinfo.deposit_date IS NULL)
186 AND (p_deposit_date IS NULL)))
187 AND ((Recinfo.gl_date = p_gl_date)
188 OR ( (Recinfo.gl_date IS NULL)
189 AND (p_gl_date IS NULL)))
190 AND ((Recinfo.deposit_time = p_deposit_time)
191 OR ( (Recinfo.deposit_time IS NULL)
192 AND (p_deposit_time IS NULL)))
193 AND ((Recinfo.currency_code = p_currency_code)
194 OR ( (Recinfo.currency_code IS NULL)
195 AND (p_currency_code IS NULL)))
196 AND ((Recinfo.exchange_rate_type = p_exchange_rate_type)
197 OR ( (Recinfo.exchange_rate_type IS NULL)
198 AND (p_exchange_rate_type IS NULL)))
199 AND ((Recinfo.exchange_rate = p_exchange_rate)
200 OR ( (Recinfo.exchange_rate IS NULL)
201 AND (p_exchange_rate IS NULL)))
202 AND ((Recinfo.transmission_record_count = p_transmission_record_count)
203 OR ( (Recinfo.transmission_record_count IS NULL)
204 AND (p_transmission_record_count IS NULL)))
205 AND ((Recinfo.transmission_amount = p_transmission_amount)
206 OR ( (Recinfo.transmission_amount IS NULL)
207 AND (p_transmission_amount IS NULL)))
208 AND ((Recinfo.lockbox_batch_count = p_lockbox_batch_count)
209 OR ( (Recinfo.lockbox_batch_count IS NULL)
210 AND (p_lockbox_batch_count IS NULL)))
211 AND ((Recinfo.lockbox_amount = p_lockbox_amount)
212 OR ( (Recinfo.lockbox_amount IS NULL)
213 AND (p_lockbox_amount IS NULL)))
214 AND ((Recinfo.lockbox_record_count = p_lockbox_record_count)
215 OR ( (Recinfo.lockbox_record_count IS NULL)
216 AND (p_lockbox_record_count IS NULL)))
217 AND ((Recinfo.batch_record_count = p_batch_record_count)
218 OR ( (Recinfo.batch_record_count IS NULL)
219 AND (p_batch_record_count IS NULL)))
220 AND ((Recinfo.batch_amount = p_batch_amount)
221 OR ( (Recinfo.batch_amount IS NULL)
222 AND (p_batch_amount IS NULL)))
223 AND ((Recinfo.transferred_receipt_count = p_transferred_receipt_count)
224 OR ( (Recinfo.transferred_receipt_count IS NULL)
225 AND (p_transferred_receipt_count IS NULL)))
226 AND ((Recinfo.transferred_receipt_amount = p_transferred_receipt_amount)
227 OR ( (Recinfo.transferred_receipt_amount IS NULL)
228 AND (p_transferred_receipt_amount IS NULL)))
229 AND ((Recinfo.overflow_indicator = p_overflow_indicator)
230 OR ( (Recinfo.overflow_indicator IS NULL)
231 AND (p_overflow_indicator IS NULL)))
232 AND ((Recinfo.receipt_date = p_receipt_date)
233 OR ( (Recinfo.receipt_date IS NULL)
234 AND (p_receipt_date IS NULL)))
235 AND ((Recinfo.receipt_method = p_receipt_method)
236 OR ( (Recinfo.receipt_method IS NULL)
237 AND (p_receipt_method IS NULL)))
238 AND ((Recinfo.receipt_method_id = p_receipt_method_id)
239 OR ( (Recinfo.receipt_method_id IS NULL)
240 AND (p_receipt_method_id IS NULL)))
241 AND ((Recinfo.remittance_amount = p_remittance_amount)
242 OR ( (Recinfo.remittance_amount IS NULL)
243 AND (p_remittance_amount IS NULL)))
244 AND ((Recinfo.customer_number = p_customer_number)
245 OR ( (Recinfo.customer_number IS NULL)
246 AND (p_customer_number IS NULL)))
247 AND ((Recinfo.customer_id = p_customer_id)
248 OR ( (Recinfo.customer_id IS NULL)
249 AND (p_customer_id IS NULL)))
250 AND ((Recinfo.bill_to_location = p_bill_to_location)
251 OR ( (Recinfo.bill_to_location IS NULL)
252 AND (p_bill_to_location IS NULL)))
253 AND ((Recinfo.customer_site_use_id = p_customer_site_use_id)
254 OR ( (Recinfo.customer_site_use_id IS NULL)
255 AND (p_customer_site_use_id IS NULL)))
256 AND ((Recinfo.transit_routing_number = p_transit_routing_number)
257 OR ( (Recinfo.transit_routing_number IS NULL)
258 AND (p_transit_routing_number IS NULL)))
259 AND ((Recinfo.account = p_account)
260 OR ( (Recinfo.account IS NULL)
261 AND (p_account IS NULL)))
262 /* Bugfix 2750412 */
263 AND ((Recinfo.customer_bank_account_id = p_customer_bank_account_id)
264 OR ( (Recinfo.customer_bank_account_id IS NULL)
265 AND (p_customer_bank_account_id IS NULL)))
266 AND ((Recinfo.amount_applied1 = p_amount_applied1)
267 OR ( (Recinfo.amount_applied1 IS NULL)
268 AND (p_amount_applied1 IS NULL)))
269 AND ((Recinfo.invoice1 = p_invoice1)
270 OR ( (Recinfo.invoice1 IS NULL)
271 AND (p_invoice1 IS NULL)))
272 AND ((Recinfo.invoice1_installment = p_invoice1_installment)
273 OR ( (Recinfo.invoice1_installment IS NULL)
274 AND (p_invoice1_installment IS NULL)))
275 AND ((Recinfo.invoice1_status = p_invoice1_status)
276 OR ( (Recinfo.invoice1_status IS NULL)
277 AND (p_invoice1_status IS NULL)))
278 AND ((Recinfo.amount_applied2 = p_amount_applied2)
279 OR ( (Recinfo.amount_applied2 IS NULL)
280 AND (p_amount_applied2 IS NULL)))
281 AND ((Recinfo.invoice2 = p_invoice2)
282 OR ( (Recinfo.invoice2 IS NULL)
283 AND (p_invoice2 IS NULL)))
284 AND ((Recinfo.invoice2_installment = p_invoice2_installment)
285 OR ( (Recinfo.invoice2_installment IS NULL)
286 AND (p_invoice2_installment IS NULL)))
287 AND ((Recinfo.invoice2_status = p_invoice2_status)
288 OR ( (Recinfo.invoice2_status IS NULL)
289 AND (p_invoice2_status IS NULL)))
290 AND ((Recinfo.amount_applied3 = p_amount_applied3)
291 OR ( (Recinfo.amount_applied3 IS NULL)
292 AND (p_amount_applied3 IS NULL)))
293 AND ((Recinfo.invoice3 = p_invoice3)
294 OR ( (Recinfo.invoice3 IS NULL)
295 AND (p_invoice3 IS NULL)))
296 AND ((Recinfo.invoice3_installment = p_invoice3_installment)
297 OR ( (Recinfo.invoice3_installment IS NULL)
298 AND (p_invoice3_installment IS NULL)))
299 AND ((Recinfo.invoice3_status = p_invoice3_status)
300 OR ( (Recinfo.invoice3_status IS NULL)
301 AND (p_invoice3_status IS NULL)))
302 AND ((Recinfo.amount_applied4 = p_amount_applied4)
303 OR ( (Recinfo.amount_applied4 IS NULL)
304 AND (p_amount_applied4 IS NULL)))
305 AND ((Recinfo.invoice4 = p_invoice4)
306 OR ( (Recinfo.invoice4 IS NULL)
307 AND (p_invoice4 IS NULL)))
308 AND ((Recinfo.invoice4_installment = p_invoice4_installment)
309 OR ( (Recinfo.invoice4_installment IS NULL)
310 AND (p_invoice4_installment IS NULL)))
311 AND ((Recinfo.invoice4_status = p_invoice4_status)
312 OR ( (Recinfo.invoice4_status IS NULL)
313 AND (p_invoice4_status IS NULL)))
314 AND ((Recinfo.amount_applied5 = p_amount_applied5)
315 OR ( (Recinfo.amount_applied5 IS NULL)
316 AND (p_amount_applied5 IS NULL)))
317 AND ((Recinfo.invoice5 = p_invoice5)
318 OR ( (Recinfo.invoice5 IS NULL)
319 AND (p_invoice5 IS NULL)))
320 AND ((Recinfo.invoice5_installment = p_invoice5_installment)
321 OR ( (Recinfo.invoice5_installment IS NULL)
322 AND (p_invoice5_installment IS NULL)))
323 AND ((Recinfo.invoice5_status = p_invoice5_status)
324 OR ( (Recinfo.invoice5_status IS NULL)
325 AND (p_invoice5_status IS NULL)))
326 AND ((Recinfo.amount_applied6 = p_amount_applied6)
327 OR ( (Recinfo.amount_applied6 IS NULL)
328 AND (p_amount_applied6 IS NULL)))
329 AND ((Recinfo.invoice6 = p_invoice6)
330 OR ( (Recinfo.invoice6 IS NULL)
331 AND (p_invoice6 IS NULL)))
332 AND ((Recinfo.invoice6_installment = p_invoice6_installment)
333 OR ( (Recinfo.invoice6_installment IS NULL)
334 AND (p_invoice6_installment IS NULL)))
335 AND ((Recinfo.invoice6_status = p_invoice6_status)
336 OR ( (Recinfo.invoice6_status IS NULL)
337 AND (p_invoice6_status IS NULL)))
338 AND ((Recinfo.amount_applied7 = p_amount_applied7)
339 OR ( (Recinfo.amount_applied7 IS NULL)
340 AND (p_amount_applied7 IS NULL)))
341 AND ((Recinfo.invoice7 = p_invoice7)
342 OR ( (Recinfo.invoice7 IS NULL)
343 AND (p_invoice7 IS NULL)))
344 AND ((Recinfo.invoice7_installment = p_invoice7_installment)
345 OR ( (Recinfo.invoice7_installment IS NULL)
346 AND (p_invoice7_installment IS NULL)))
347 AND ((Recinfo.invoice7_status = p_invoice7_status)
348 OR ( (Recinfo.invoice7_status IS NULL)
349 AND (p_invoice7_status IS NULL)))
350 AND ((Recinfo.amount_applied8 = p_amount_applied8)
351 OR ( (Recinfo.amount_applied8 IS NULL)
352 AND (p_amount_applied8 IS NULL)))
353 AND ((Recinfo.invoice8 = p_invoice8)
354 OR ( (Recinfo.invoice8 IS NULL)
355 AND (p_invoice8 IS NULL)))
356 AND ((Recinfo.invoice8_installment = p_invoice8_installment)
357 OR ( (Recinfo.invoice8_installment IS NULL)
358 AND (p_invoice8_installment IS NULL)))
359 AND ((Recinfo.invoice8_status = p_invoice8_status)
360 OR ( (Recinfo.invoice8_status IS NULL)
361 AND (p_invoice8_status IS NULL)))
362 AND ((Recinfo.attribute1 = p_attribute1)
363 OR ( (Recinfo.attribute1 IS NULL)
364 AND (p_attribute1 IS NULL)))
365 AND ((Recinfo.attribute2 = p_attribute2)
366 OR ( (Recinfo.attribute2 IS NULL)
367 AND (p_attribute2 IS NULL)))
368 AND ((Recinfo.attribute3 = p_attribute3)
369 OR ( (Recinfo.attribute3 IS NULL)
370 AND (p_attribute3 IS NULL)))
371 AND ((Recinfo.attribute4 = p_attribute4)
372 OR ( (Recinfo.attribute4 IS NULL)
373 AND (p_attribute4 IS NULL)))
374 AND ((Recinfo.attribute5 = p_attribute5)
375 OR ( (Recinfo.attribute5 IS NULL)
376 AND (p_attribute5 IS NULL)))
377 AND ((Recinfo.attribute6 = p_attribute6)
378 OR ( (Recinfo.attribute6 IS NULL)
379 AND (p_attribute6 IS NULL)))
380 AND ((Recinfo.attribute7 = p_attribute7)
381 OR ( (Recinfo.attribute7 IS NULL)
382 AND (p_attribute7 IS NULL)))
383 AND ((Recinfo.attribute8 = p_attribute8)
384 OR ( (Recinfo.attribute8 IS NULL)
385 AND (p_attribute8 IS NULL)))
386 AND ((Recinfo.attribute9 = p_attribute9)
387 OR ( (Recinfo.attribute9 IS NULL)
388 AND (p_attribute9 IS NULL)))
389 AND ((Recinfo.attribute10 = p_attribute10)
390 OR ( (Recinfo.attribute10 IS NULL)
391 AND (p_attribute10 IS NULL)))
392 AND ((Recinfo.attribute11 = p_attribute11)
393 OR ( (Recinfo.attribute11 IS NULL)
394 AND (p_attribute11 IS NULL)))
395 AND ((Recinfo.attribute12 = p_attribute12)
396 OR ( (Recinfo.attribute12 IS NULL)
397 AND (p_attribute12 IS NULL)))
398 AND ((Recinfo.attribute13 = p_attribute13)
399 OR ( (Recinfo.attribute13 IS NULL)
400 AND (p_attribute13 IS NULL)))
401 AND ((Recinfo.attribute14 = p_attribute14)
402 OR ( (Recinfo.attribute14 IS NULL)
403 AND (p_attribute14 IS NULL)))
404 AND ((Recinfo.attribute15 = p_attribute15)
405 OR ( (Recinfo.attribute15 IS NULL)
406 AND (p_attribute15 IS NULL)))
407 AND ((Recinfo.status = p_status)
408 OR ( (Recinfo.status IS NULL)
409 AND (p_status IS NULL)))
410 AND ((Recinfo.matching1_date = p_matching1_date)
411 OR ( (Recinfo.matching1_date IS NULL)
412 AND (p_matching1_date IS NULL)))
413 AND ((Recinfo.matching2_date = p_matching2_date)
414 OR ( (Recinfo.matching2_date IS NULL)
415 AND (p_matching2_date IS NULL)))
416 AND ((Recinfo.matching3_date = p_matching3_date)
417 OR ( (Recinfo.matching3_date IS NULL)
418 AND (p_matching3_date IS NULL)))
419 AND ((Recinfo.matching4_date = p_matching4_date)
420 OR ( (Recinfo.matching4_date IS NULL)
421 AND (p_matching4_date IS NULL)))
422 AND ((Recinfo.matching5_date = p_matching5_date)
423 OR ( (Recinfo.matching5_date IS NULL)
424 AND (p_matching5_date IS NULL)))
425 AND ((Recinfo.matching6_date = p_matching6_date)
426 OR ( (Recinfo.matching6_date IS NULL)
427 AND (p_matching6_date IS NULL)))
428 AND ((Recinfo.matching7_date = p_matching7_date)
429 OR ( (Recinfo.matching7_date IS NULL)
430 AND (p_matching7_date IS NULL)))
431 AND ((Recinfo.matching8_date = p_matching8_date)
432 OR ( (Recinfo.matching8_date IS NULL)
433 AND (p_matching8_date IS NULL)))
434 AND ((Recinfo.amount_applied_from1 = p_amount_applied_from1)
435 OR ( (Recinfo.amount_applied_from1 IS NULL)
436 AND (p_amount_applied_from1 IS NULL)))
437 AND ((Recinfo.trans_to_receipt_rate1 = p_trans_to_receipt_rate1)
438 OR ( (Recinfo.trans_to_receipt_rate1 IS NULL)
439 AND (p_trans_to_receipt_rate1 IS NULL)))
440 AND ((Recinfo.invoice_currency_code1 = p_invoice_currency_code1)
441 OR ( (Recinfo.invoice_currency_code1 IS NULL)
442 AND (p_invoice_currency_code1 IS NULL)))
443 AND ((Recinfo.amount_applied_from2 = p_amount_applied_from2)
444 OR ( (Recinfo.amount_applied_from2 IS NULL)
445 AND (p_amount_applied_from2 IS NULL)))
446 AND ((Recinfo.trans_to_receipt_rate2 = p_trans_to_receipt_rate2)
447 OR ( (Recinfo.trans_to_receipt_rate2 IS NULL)
448 AND (p_trans_to_receipt_rate2 IS NULL)))
449 AND ((Recinfo.invoice_currency_code2 = p_invoice_currency_code2)
450 OR ( (Recinfo.invoice_currency_code2 IS NULL)
451 AND (p_invoice_currency_code2 IS NULL)))
452 AND ((Recinfo.amount_applied_from3 = p_amount_applied_from3)
453 OR ( (Recinfo.amount_applied_from3 IS NULL)
454 AND (p_amount_applied_from3 IS NULL)))
455 AND ((Recinfo.trans_to_receipt_rate3 = p_trans_to_receipt_rate3)
456 OR ( (Recinfo.trans_to_receipt_rate3 IS NULL)
457 AND (p_trans_to_receipt_rate3 IS NULL)))
458 AND ((Recinfo.invoice_currency_code3 = p_invoice_currency_code3)
459 OR ( (Recinfo.invoice_currency_code3 IS NULL)
460 AND (p_invoice_currency_code3 IS NULL)))
461 AND ((Recinfo.amount_applied_from4 = p_amount_applied_from4)
462 OR ( (Recinfo.amount_applied_from4 IS NULL)
463 AND (p_amount_applied_from4 IS NULL)))
464 AND ((Recinfo.trans_to_receipt_rate4 = p_trans_to_receipt_rate4)
465 OR ( (Recinfo.trans_to_receipt_rate4 IS NULL)
466 AND (p_trans_to_receipt_rate4 IS NULL)))
467 AND ((Recinfo.invoice_currency_code4 = p_invoice_currency_code4)
468 OR ( (Recinfo.invoice_currency_code4 IS NULL)
469 AND (p_invoice_currency_code4 IS NULL)))
470 AND ((Recinfo.amount_applied_from5 = p_amount_applied_from5)
471 OR ( (Recinfo.amount_applied_from5 IS NULL)
472 AND (p_amount_applied_from5 IS NULL)))
473 AND ((Recinfo.trans_to_receipt_rate5 = p_trans_to_receipt_rate5)
474 OR ( (Recinfo.trans_to_receipt_rate5 IS NULL)
475 AND (p_trans_to_receipt_rate5 IS NULL)))
476 AND ((Recinfo.invoice_currency_code5 = p_invoice_currency_code5)
477 OR ( (Recinfo.invoice_currency_code5 IS NULL)
478 AND (p_invoice_currency_code5 IS NULL)))
479 AND ((Recinfo.amount_applied_from6 = p_amount_applied_from6)
480 OR ( (Recinfo.amount_applied_from6 IS NULL)
481 AND (p_amount_applied_from6 IS NULL)))
482 AND ((Recinfo.trans_to_receipt_rate6 = p_trans_to_receipt_rate6)
483 OR ( (Recinfo.trans_to_receipt_rate6 IS NULL)
484 AND (p_trans_to_receipt_rate6 IS NULL)))
485 AND ((Recinfo.invoice_currency_code6 = p_invoice_currency_code6)
486 OR ( (Recinfo.invoice_currency_code6 IS NULL)
487 AND (p_invoice_currency_code6 IS NULL)))
488 AND ((Recinfo.amount_applied_from7 = p_amount_applied_from7)
489 OR ( (Recinfo.amount_applied_from7 IS NULL)
490 AND (p_amount_applied_from7 IS NULL)))
491 AND ((Recinfo.trans_to_receipt_rate7 = p_trans_to_receipt_rate7)
492 OR ( (Recinfo.trans_to_receipt_rate7 IS NULL)
493 AND (p_trans_to_receipt_rate7 IS NULL)))
494 AND ((Recinfo.invoice_currency_code7 = p_invoice_currency_code7)
495 OR ( (Recinfo.invoice_currency_code7 IS NULL)
496 AND (p_invoice_currency_code7 IS NULL)))
497 AND ((Recinfo.amount_applied_from8 = p_amount_applied_from8)
498 OR ( (Recinfo.amount_applied_from8 IS NULL)
499 AND (p_amount_applied_from8 IS NULL)))
500 AND ((Recinfo.trans_to_receipt_rate8 = p_trans_to_receipt_rate8)
501 OR ( (Recinfo.trans_to_receipt_rate8 IS NULL)
502 AND (p_trans_to_receipt_rate8 IS NULL)))
503 AND ((Recinfo.invoice_currency_code8 = p_invoice_currency_code8)
504 OR ( (Recinfo.invoice_currency_code8 IS NULL)
505 AND (p_invoice_currency_code8 IS NULL)))
506 AND ((Recinfo.ussgl_transaction_code = p_ussgl_transaction_code)
507 OR ( (Recinfo.ussgl_transaction_code IS NULL)
508 AND (p_ussgl_transaction_code IS NULL)))
509 AND ((Recinfo.ussgl_transaction_code1 = p_ussgl_transaction_code1)
510 OR ( (Recinfo.ussgl_transaction_code1 IS NULL)
511 AND (p_ussgl_transaction_code1 IS NULL)))
512 AND ((Recinfo.ussgl_transaction_code2 = p_ussgl_transaction_code2)
513 OR ( (Recinfo.ussgl_transaction_code2 IS NULL)
514 AND (p_ussgl_transaction_code2 IS NULL)))
515 AND ((Recinfo.ussgl_transaction_code3 = p_ussgl_transaction_code3)
516 OR ( (Recinfo.ussgl_transaction_code3 IS NULL)
517 AND (p_ussgl_transaction_code3 IS NULL)))
518 AND ((Recinfo.ussgl_transaction_code4 = p_ussgl_transaction_code4)
519 OR ( (Recinfo.ussgl_transaction_code4 IS NULL)
520 AND (p_ussgl_transaction_code4 IS NULL)))
521 AND ((Recinfo.ussgl_transaction_code5 = p_ussgl_transaction_code5)
522 OR ( (Recinfo.ussgl_transaction_code5 IS NULL)
523 AND (p_ussgl_transaction_code5 IS NULL)))
524 AND ((Recinfo.ussgl_transaction_code6 = p_ussgl_transaction_code6)
525 OR ( (Recinfo.ussgl_transaction_code6 IS NULL)
526 AND (p_ussgl_transaction_code6 IS NULL)))
527 AND ((Recinfo.ussgl_transaction_code7 = p_ussgl_transaction_code7)
528 OR ( (Recinfo.ussgl_transaction_code7 IS NULL)
529 AND (p_ussgl_transaction_code7 IS NULL)))
530 AND ((Recinfo.ussgl_transaction_code8 = p_ussgl_transaction_code8)
531 OR ( (Recinfo.ussgl_transaction_code8 IS NULL)
532 AND (p_ussgl_transaction_code8 IS NULL)))
533 ) then
534 return;
535 else
536 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
537 APP_EXCEPTION.Raise_Exception;
538 end if;
539
540 IF PG_DEBUG in ('Y', 'C') THEN
541 arp_util.debug( 'arp_lockbox_pkg.lock_trans_data()-' );
542 END IF;
543 EXCEPTION
544 WHEN OTHERS THEN
545 IF PG_DEBUG in ('Y', 'C') THEN
546 arp_util.debug( 'EXCEPTION: arp_lockbox_pkg.lock_trans_data' );
547 END IF;
548 RAISE;
549 END lock_trans_data;
550
551 --
552 PROCEDURE update_trans_data(
553 p_transmission_record_id IN NUMBER,
554 p_lockbox_number IN VARCHAR2,
555 p_batch_name IN VARCHAR2,
556 p_item_number IN VARCHAR2,
557 p_check_number IN VARCHAR2,
558 p_overflow_sequence IN NUMBER,
559 p_record_type IN VARCHAR2,
560 p_comments IN VARCHAR2,
561 p_origination IN VARCHAR2,
562 p_destination_account IN VARCHAR2,
563 p_deposit_date IN DATE,
564 p_gl_date IN DATE,
565 p_deposit_time IN VARCHAR2,
566 p_currency_code IN VARCHAR2,
567 p_exchange_rate_type IN VARCHAR2,
568 p_exchange_rate IN NUMBER,
569 p_transmission_record_count IN NUMBER,
570 p_transmission_amount IN NUMBER,
571 p_lockbox_batch_count IN NUMBER,
572 p_lockbox_amount IN NUMBER,
573 p_lockbox_record_count IN NUMBER,
574 p_batch_record_count IN NUMBER,
575 p_batch_amount IN NUMBER,
576 p_transferred_receipt_count IN NUMBER,
577 p_transferred_receipt_amount IN NUMBER,
578 p_overflow_indicator IN VARCHAR2,
579 p_receipt_date IN DATE,
580 p_receipt_method IN VARCHAR2,
581 p_receipt_method_id IN NUMBER,
582 p_remittance_amount IN NUMBER,
583 p_customer_number IN VARCHAR2,
584 p_customer_id IN NUMBER,
585 p_bill_to_location IN VARCHAR2,
586 p_customer_site_use_id IN NUMBER,
587 p_transit_routing_number IN VARCHAR2,
588 p_account IN VARCHAR2,
589 p_customer_bank_account_id IN NUMBER,
590 p_amount_applied1 IN NUMBER,
591 p_invoice1 IN VARCHAR2,
592 p_invoice1_installment IN NUMBER,
593 p_invoice1_status IN VARCHAR2,
594 p_amount_applied2 IN NUMBER,
595 p_invoice2 IN VARCHAR2,
596 p_invoice2_installment IN NUMBER,
597 p_invoice2_status IN VARCHAR2,
598 p_amount_applied3 IN NUMBER,
599 p_invoice3 IN VARCHAR2,
600 p_invoice3_installment IN NUMBER,
601 p_invoice3_status IN VARCHAR2,
602 p_amount_applied4 IN NUMBER,
603 p_invoice4 IN VARCHAR2,
604 p_invoice4_installment IN NUMBER,
605 p_invoice4_status IN VARCHAR2,
606 p_amount_applied5 IN NUMBER,
607 p_invoice5 IN VARCHAR2,
608 p_invoice5_installment IN NUMBER,
609 p_invoice5_status IN VARCHAR2,
610 p_amount_applied6 IN NUMBER,
611 p_invoice6 IN VARCHAR2,
612 p_invoice6_installment IN NUMBER,
613 p_invoice6_status IN VARCHAR2,
614 p_amount_applied7 IN NUMBER,
615 p_invoice7 IN VARCHAR2,
616 p_invoice7_installment IN NUMBER,
617 p_invoice7_status IN VARCHAR2,
618 p_amount_applied8 IN NUMBER,
619 p_invoice8 IN VARCHAR2,
620 p_invoice8_installment IN NUMBER,
621 p_invoice8_status IN VARCHAR2,
622 p_attribute1 IN VARCHAR2,
623 p_attribute2 IN VARCHAR2,
624 p_attribute3 IN VARCHAR2,
625 p_attribute4 IN VARCHAR2,
626 p_attribute5 IN VARCHAR2,
627 p_attribute6 IN VARCHAR2,
628 p_attribute7 IN VARCHAR2,
629 p_attribute8 IN VARCHAR2,
630 p_attribute9 IN VARCHAR2,
631 p_attribute10 IN VARCHAR2,
632 p_attribute11 IN VARCHAR2,
633 p_attribute12 IN VARCHAR2,
634 p_attribute13 IN VARCHAR2,
635 p_attribute14 IN VARCHAR2,
636 p_attribute15 IN VARCHAR2,
637 p_status IN VARCHAR2,
638 p_matching1_date IN DATE,
639 p_matching2_date IN DATE,
640 p_matching3_date IN DATE,
641 p_matching4_date IN DATE,
642 p_matching5_date IN DATE,
643 p_matching6_date IN DATE,
644 p_matching7_date IN DATE,
645 p_matching8_date IN DATE,
646 p_amount_applied_from1 IN NUMBER,
647 p_trans_to_receipt_rate1 IN NUMBER,
648 p_invoice_currency_code1 IN VARCHAR2,
649 p_amount_applied_from2 IN NUMBER,
650 p_trans_to_receipt_rate2 IN NUMBER,
651 p_invoice_currency_code2 IN VARCHAR2,
652 p_amount_applied_from3 IN NUMBER,
653 p_trans_to_receipt_rate3 IN NUMBER,
654 p_invoice_currency_code3 IN VARCHAR2,
655 p_amount_applied_from4 IN NUMBER,
656 p_trans_to_receipt_rate4 IN NUMBER,
657 p_invoice_currency_code4 IN VARCHAR2,
658 p_amount_applied_from5 IN NUMBER,
659 p_trans_to_receipt_rate5 IN NUMBER,
660 p_invoice_currency_code5 IN VARCHAR2,
661 p_amount_applied_from6 IN NUMBER,
662 p_trans_to_receipt_rate6 IN NUMBER,
663 p_invoice_currency_code6 IN VARCHAR2,
664 p_amount_applied_from7 IN NUMBER,
665 p_trans_to_receipt_rate7 IN NUMBER,
666 p_invoice_currency_code7 IN VARCHAR2,
667 p_amount_applied_from8 IN NUMBER,
668 p_trans_to_receipt_rate8 IN NUMBER,
669 p_invoice_currency_code8 IN VARCHAR2,
670 p_ussgl_transaction_code IN VARCHAR2,
671 p_ussgl_transaction_code1 IN VARCHAR2,
672 p_ussgl_transaction_code2 IN VARCHAR2,
673 p_ussgl_transaction_code3 IN VARCHAR2,
674 p_ussgl_transaction_code4 IN VARCHAR2,
675 p_ussgl_transaction_code5 IN VARCHAR2,
676 p_ussgl_transaction_code6 IN VARCHAR2,
677 p_ussgl_transaction_code7 IN VARCHAR2,
678 p_ussgl_transaction_code8 IN VARCHAR2
679 ) IS
680
681 l_dummy NUMBER;
682 l_break_point VARCHAR2(30);
683 BEGIN
684 IF PG_DEBUG in ('Y', 'C') THEN
685 arp_util.debug('arp_lockbox_pkg.update_trans_data()+');
686 END IF;
687 --
688 /* Bugfix 2284014. Commented the foll. code that earlier
689 locked the record
690 l_break_point := 'lock record';
691 SELECT transmission_record_id
692 INTO l_dummy
693 FROM ar_payments_interface
694 WHERE transmission_record_id = p_transmission_record_id
695 FOR UPDATE NOWAIT; */
696 --
697 l_break_point := 'update record';
698 UPDATE ar_payments_interface
699 SET lockbox_number = p_lockbox_number,
700 batch_name = p_batch_name,
701 item_number = p_item_number,
702 check_number = p_check_number,
703 overflow_sequence = p_overflow_sequence,
704 record_type = p_record_type,
705 comments = p_comments,
706 origination = p_origination,
707 destination_account = p_destination_account,
708 deposit_date = p_deposit_date,
709 gl_date = p_gl_date,
710 deposit_time = p_deposit_time,
711 currency_code = p_currency_code,
712 exchange_rate_type = p_exchange_rate_type,
713 exchange_rate = p_exchange_rate,
714 transmission_record_count = p_transmission_record_count,
715 transmission_amount = p_transmission_amount,
716 lockbox_batch_count = p_lockbox_batch_count,
717 lockbox_amount = p_lockbox_amount,
718 lockbox_record_count = p_lockbox_record_count,
719 batch_record_count = p_batch_record_count,
720 batch_amount = p_batch_amount,
721 transferred_receipt_count = p_transferred_receipt_count,
722 transferred_receipt_amount = p_transferred_receipt_amount,
723 overflow_indicator = p_overflow_indicator,
724 receipt_date = p_receipt_date,
725 receipt_method = p_receipt_method,
726 receipt_method_id = p_receipt_method_id,
727 remittance_amount = p_remittance_amount,
728 customer_number = p_customer_number,
729 customer_id = p_customer_id,
730 bill_to_location = p_bill_to_location,
731 customer_site_use_id = p_customer_site_use_id,
732 transit_routing_number = p_transit_routing_number,
733 account = p_account,
734 customer_bank_account_id = p_customer_bank_account_id,
735 amount_applied1 = p_amount_applied1,
736 invoice1 = p_invoice1,
737 invoice1_installment = p_invoice1_installment,
738 invoice1_status = p_invoice1_status,
739 amount_applied2 = p_amount_applied2,
740 invoice2 = p_invoice2,
741 invoice2_installment = p_invoice2_installment,
742 invoice2_status = p_invoice2_status,
743 amount_applied3 = p_amount_applied3,
744 invoice3 = p_invoice3,
745 invoice3_installment = p_invoice3_installment,
746 invoice3_status = p_invoice3_status,
747 amount_applied4 = p_amount_applied4,
748 invoice4 = p_invoice4,
749 invoice4_installment = p_invoice4_installment,
750 invoice4_status = p_invoice4_status,
751 amount_applied5 = p_amount_applied5,
752 invoice5 = p_invoice5,
753 invoice5_installment = p_invoice5_installment,
754 invoice5_status = p_invoice5_status,
755 amount_applied6 = p_amount_applied6,
756 invoice6 = p_invoice6,
757 invoice6_installment = p_invoice6_installment,
758 invoice6_status = p_invoice6_status,
759 amount_applied7 = p_amount_applied7,
760 invoice7 = p_invoice7,
761 invoice7_installment = p_invoice7_installment,
762 invoice7_status = p_invoice7_status,
763 amount_applied8 = p_amount_applied8,
764 invoice8 = p_invoice8,
765 invoice8_installment = p_invoice8_installment,
766 invoice8_status = p_invoice8_status,
767 attribute1 = p_attribute1,
768 attribute2 = p_attribute2,
769 attribute3 = p_attribute3,
770 attribute4 = p_attribute4,
771 attribute5 = p_attribute5,
772 attribute6 = p_attribute6,
773 attribute7 = p_attribute7,
774 attribute8 = p_attribute8,
775 attribute9 = p_attribute9,
776 attribute10 = p_attribute10,
777 attribute11 = p_attribute11,
778 attribute12 = p_attribute12,
779 attribute13 = p_attribute13,
780 attribute14 = p_attribute14,
781 attribute15 = p_attribute15,
782 status = p_status,
783 matching1_date = p_matching1_date,
784 matching2_date = p_matching2_date,
785 matching3_date = p_matching3_date,
786 matching4_date = p_matching4_date,
787 matching5_date = p_matching5_date,
788 matching6_date = p_matching6_date,
789 matching7_date = p_matching7_date,
790 matching8_date = p_matching8_date,
791 amount_applied_from1 = p_amount_applied_from1,
792 trans_to_receipt_rate1 = p_trans_to_receipt_rate1,
793 invoice_currency_code1 = p_invoice_currency_code1,
794 amount_applied_from2 = p_amount_applied_from2,
795 trans_to_receipt_rate2 = p_trans_to_receipt_rate2,
796 invoice_currency_code2= p_invoice_currency_code2,
797 amount_applied_from3 = p_amount_applied_from3,
798 trans_to_receipt_rate3 = p_trans_to_receipt_rate3,
799 invoice_currency_code3= p_invoice_currency_code3,
800 amount_applied_from4 = p_amount_applied_from4,
801 trans_to_receipt_rate4 = p_trans_to_receipt_rate4,
802 invoice_currency_code4= p_invoice_currency_code4,
803 amount_applied_from5 = p_amount_applied_from5,
804 trans_to_receipt_rate5 = p_trans_to_receipt_rate5,
805 invoice_currency_code5= p_invoice_currency_code5,
806 amount_applied_from6 = p_amount_applied_from6,
807 trans_to_receipt_rate6 = p_trans_to_receipt_rate6,
808 invoice_currency_code6= p_invoice_currency_code6,
809 amount_applied_from7 = p_amount_applied_from7,
810 trans_to_receipt_rate7 = p_trans_to_receipt_rate7,
811 invoice_currency_code7= p_invoice_currency_code7,
812 amount_applied_from8 = p_amount_applied_from8,
813 trans_to_receipt_rate8 = p_trans_to_receipt_rate8,
814 invoice_currency_code8= p_invoice_currency_code8,
815 ussgl_transaction_code = p_ussgl_transaction_code,
816 ussgl_transaction_code1 = p_ussgl_transaction_code1,
817 ussgl_transaction_code2 = p_ussgl_transaction_code2,
818 ussgl_transaction_code3 = p_ussgl_transaction_code3,
819 ussgl_transaction_code4 = p_ussgl_transaction_code4,
820 ussgl_transaction_code5 = p_ussgl_transaction_code5,
821 ussgl_transaction_code6 = p_ussgl_transaction_code6,
822 ussgl_transaction_code7 = p_ussgl_transaction_code7,
823 ussgl_transaction_code8 = p_ussgl_transaction_code8
824 WHERE transmission_record_id = p_transmission_record_id;
825 --
826 IF PG_DEBUG in ('Y', 'C') THEN
827 arp_util.debug('arp_lockbox_pkg.update_trans_data()-');
828 END IF;
829 --
830 EXCEPTION
831 WHEN OTHERS THEN
832 IF PG_DEBUG in ('Y', 'C') THEN
833 arp_standard.debug('update_trans_data: ' ||
834 'EXCEPTION: arp_lockbox_pkg.update_trans_data when '||
835 l_break_point);
836 END IF;
837 END update_trans_data;
838 --
839 --
840 --
841 PROCEDURE delete_trans_data(
842 p_transmission_record_id IN NUMBER ) IS
843 l_dummy NUMBER;
844 l_break_point VARCHAR2(30);
845 BEGIN
846 IF PG_DEBUG in ('Y', 'C') THEN
847 arp_util.debug('arp_lockbox_pkg.delete_trans_data()+');
848 END IF;
849 --
850 /* Bugfix 2284014. Commented the foll. code that earlier
851 locked the record
852 l_break_point := 'lock record';
853 SELECT transmission_record_id
854 INTO l_dummy
855 FROM ar_payments_interface
856 WHERE transmission_record_id = p_transmission_record_id
857 FOR UPDATE NOWAIT; */
858 --
859 l_break_point := 'delete record';
860 DELETE ar_payments_interface
861 WHERE transmission_record_id = p_transmission_record_id;
862 --
863 IF PG_DEBUG in ('Y', 'C') THEN
864 arp_util.debug('arp_lockbox_pkg.delete_trans_data()-');
865 END IF;
866 --
867 EXCEPTION
868 WHEN OTHERS THEN
869 IF PG_DEBUG in ('Y', 'C') THEN
870 arp_standard.debug('delete_trans_data: ' ||
871 'EXCEPTION: arp_lockbox_pkg.delete_trans_data when '||
872 l_break_point);
873 END IF;
874 END delete_trans_data;
875 --
876 --
877 --
878 FUNCTION get_batch_name(p_transmission_id number,
879 p_lockbox_number varchar2,
880 p_item_num number)
881 RETURN varchar2 IS
882 l_batch_name ar_payments_interface.batch_name%type;
883 BEGIN
884 if (p_transmission_id is NULL) THEN
885 return NULL;
886 end if;
887 --
888 if (p_lockbox_number is NOT NULL) THEN
889 begin
890 SELECT distinct(batch_name)
891 INTO l_batch_name
892 FROM ar_payments_interface
893 WHERE lockbox_number = p_lockbox_number
894 AND transmission_id = p_transmission_id
895 AND batch_name is NOT NULL;
896 if (l_batch_name is NOT NULL) THEN
897 return l_batch_name;
898 end if;
899 exception
900 when no_data_found then NULL;
901 when too_many_rows then return NULL;
902 when others then raise;
903 end;
904 end if;
905 --
906 if (p_item_num is NOT NULL) THEN
907 begin
908 SELECT distinct(batch_name)
909 INTO l_batch_name
910 FROM ar_payments_interface
911 WHERE item_number = p_item_num
912 AND transmission_id = p_transmission_id
913 AND batch_name is NOT NULL;
914 if (l_batch_name is NOT NULL) THEN
915 return l_batch_name;
916 end if;
917 exception
918 when no_data_found then NULL;
919 when too_many_rows then return NULL;
920 when others then raise;
921 end;
922 end if;
923 --
924 -- if the execution has reached here, it means that it could not find the batch name.
925 -- retun null.
926 --
927 return NULL;
928 --
929 END get_batch_name;
930 --
931 --
932 FUNCTION get_lb_num(p_transmission_id number,
933 p_batch_name varchar2,
934 p_item_num number)
935 RETURN VARCHAR2 IS
936 l_lockbox_number ar_payments_interface.lockbox_number%type;
937 BEGIN
938 if (p_transmission_id is NULL) THEN
939 return NULL;
940 end if;
941 --
942 if (p_batch_name is NOT NULL) THEN
943 begin
944 SELECT distinct(lockbox_number)
945 INTO l_lockbox_number
946 FROM ar_payments_interface
947 WHERE batch_name = p_batch_name
948 AND transmission_id = p_transmission_id
949 AND lockbox_number is NOT NULL;
950 if (l_lockbox_number is NOT NULL) THEN
951 return l_lockbox_number;
952 end if;
953 exception
954 when no_data_found then NULL;
955 when too_many_rows then return NULL;
956 when others then raise;
957 end;
958 end if;
959 --
960 if (p_item_num is NOT NULL) THEN
961 begin
962 SELECT distinct(lockbox_number)
963 INTO l_lockbox_number
964 FROM ar_payments_interface
965 WHERE item_number = p_item_num
966 AND transmission_id = p_transmission_id
967 AND lockbox_number is NOT NULL;
968 if (l_lockbox_number is NOT NULL) THEN
969 return l_lockbox_number;
970 end if;
971 exception
972 when no_data_found then NULL;
973 when too_many_rows then return NULL;
974 when others then raise;
975 end;
976 end if;
977 --
978 -- if the execution has reached here, it means that it could not find the LB num
979 -- retun null.
980 --
981 return NULL;
982 --
983 END get_lb_num;
984 --
985 END ARP_LOCKBOX_PKG;
[Home] [Help]