[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_REMIT_COLL_OCCUR
Source
1 PACKAGE BODY JL_BR_AR_REMIT_COLL_OCCUR AS
2 /* $Header: jlbrratb.pls 120.17 2011/06/30 17:56:16 abuissa ship $*/
3
4 PROCEDURE remit_collection (P_BORDERO_ID IN NUMBER,
5 P_USER_ID IN NUMBER,
6 P_PROC_STATUS IN OUT NOCOPY NUMBER) IS
7 PL_SELECTION_CONTROL_ID NUMBER(38);
8 PL_SELECT_ACCOUNT_ID NUMBER(38);
9 PL_CS_SELECTION_STATUS VARCHAR2(30);
10 var_selection_control_chk NUMBER;
11 var_selection_control NUMBER;
12 var_bordero_status_chk VARCHAR2(30);
13 var_bordero_type VARCHAR2(30);
14 l_set_of_books_id NUMBER;
15 l_currency_code VARCHAR2(30);
16 l_msg_count NUMBER;
17 l_msg_data VARCHAR2(80);
18 l_mesg VARCHAR2(1000);
19 l_return_status VARCHAR2(30);
20 x_cash_receipt_id NUMBER;
21 l_occurrence_id NUMBER; -- SLA Uptake - Bug#4301543
22 l_event_id NUMBER; -- SLA Uptake - Bug#4301543
23 l_occ NUMBER;
24 l_org_id NUMBER;
25
26 CURSOR check1 IS
27 SELECT bordero_status, selection_control_id,bordero_type
28 FROM jl_br_ar_borderos
29 WHERE bordero_id = P_BORDERO_ID
30 AND bordero_status in ('SELECTED', 'FORMATTED')
31 FOR UPDATE NOWAIT;
32
33 CURSOR check2 IS
34 SELECT selection_control_id
35 FROM jl_br_ar_select_controls
36 WHERE selection_control_id = var_selection_control
37 FOR UPDATE;
38
39 /* Cursor PS is used to read documents from bordero which were not remitted */
40
41 /* CE uptake - Bug#2932986
42 cursor PS is
43 select PS.PAYMENT_SCHEDULE_ID, DC.DOCUMENT_ID,
44 DC.PORTFOLIO_CODE, PS.GLOBAL_ATTRIBUTE10,
45 DC.FACTORING_AMOUNT, DC.BANK_INSTRUCTION_CODE1,
46 PS.AMOUNT_DUE_REMAINING, DC.BANK_INSTRUCTION_CODE2,
47 DC.NUM_DAYS_INSTRUCTION, PS.DUE_DATE,
48 DC.BANK_CHARGE_AMOUNT, DC.RECEIPT_METHOD_ID,
49 jlbo.BANK_NUMBER,
50 jlbo.BANK_OCCURRENCE_CODE,
51 jlbo.BANK_OCCURRENCE_TYPE,
52 b.OUTPUT_FORMAT,
53 JLBRRMA.BANK_CHARGES_CCID,
54 JLBRRMA.COLL_ENDORSEMENT_CCID,
55 JLBRRMA.BILLS_COLLECTION_CCID,
56 JLBRRMA.OTHER_CREDITS_CCID,
57 JLBRRMA.FACTORING_DOCS_CCID,
58 JLBRRMA.CALCULATED_INTEREST_CCID,
59 JLBRRMA.INTEREST_WRITEOFF_CCID,
60 RMA.CASH_CCID,
61 RMA.EARNED_CCID,
62 RMA.ON_ACCOUNT_CCID,
63 RMA.UNAPPLIED_CCID,
64 RMA.UNEARNED_CCID,
65 RMA.UNIDENTIFIED_CCID,
66 JLBRRMA.ABATEMENT_WRITEOFF_CCID,
67 JLBRRMA.ABATEMENT_REVENUE_CCID,
68 JLBRRMA.INTEREST_REVENUE_CCID,
69 JLBRRMA.CALCULATED_INTEREST_RECTRX_ID,
70 JLBRRMA.INTEREST_WRITEOFF_RECTRX_ID,
71 JLBRRMA.INTEREST_REVENUE_RECTRX_ID,
72 JLBRRMA.ABATEMENT_WRITEOFF_RECTRX_ID,
73 JLBRRMA.ABATEMENT_REVENUE_RECTRX_ID,
74 CSC.GL_DATE
75 from AR_PAYMENT_SCHEDULES_ALL PS, JL_BR_AR_COLLECTION_DOCS_ALL DC,
76 JL_BR_AR_BORDEROS B,
77 AR_RECEIPT_METHOD_ACCOUNTS_ALL RMA,
78 JL_BR_AR_SELECT_ACCOUNTS_ALL CSC,
79 JL_BR_AR_REC_MET_ACCTS_EXT_ALL JLBRRMA,
80 AP_BANK_ACCOUNTS_ALL apba,
81 AP_BANK_BRANCHES apbb,
82 JL_BR_AR_BANK_OCCURRENCES jlbo
83 where DC.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
84 and PS.STATUS = 'OP'
85 and B.BORDERO_STATUS = 'SELECTED'
86 and DC.DOCUMENT_STATUS = 'SELECTED'
87 and B.BORDERO_ID = DC.BORDERO_ID
88 and DC.BORDERO_ID = P_BORDERO_ID
89 and CSC.SELECT_ACCOUNT_ID = B.SELECT_ACCOUNT_ID
90 and JLBRRMA.RECEIPT_METHOD_ID = DC.RECEIPT_METHOD_ID
91 and JLBRRMA.BANK_ACCOUNT_ID = B.BANK_ACCOUNT_ID
92 and RMA.RECEIPT_METHOD_ID = DC.RECEIPT_METHOD_ID
93 and RMA.BANK_ACCOUNT_ID = B.BANK_ACCOUNT_ID
94 and apba.BANK_ACCOUNT_ID =b.bank_account_id
95 and apba.bank_branch_id = apbb.bank_branch_id
96 and jlbo.BANK_NUMBER = apbb.bank_number
97 and jlbo.STD_OCCURRENCE_CODE = 'REMITTANCE'
98 and jlbo.BANK_OCCURRENCE_TYPE = 'REMITTANCE_OCCURRENCE';
99 */
100
101
102 cursor PS is
103 select PS.PAYMENT_SCHEDULE_ID, DC.DOCUMENT_ID,
104 DC.PORTFOLIO_CODE, PS.GLOBAL_ATTRIBUTE10,
105 DC.FACTORING_AMOUNT, DC.BANK_INSTRUCTION_CODE1,
106 PS.AMOUNT_DUE_REMAINING, DC.BANK_INSTRUCTION_CODE2,
107 DC.NUM_DAYS_INSTRUCTION, PS.DUE_DATE,
108 DC.BANK_CHARGE_AMOUNT, DC.RECEIPT_METHOD_ID,
109 --jlbo.BANK_NUMBER,
110 jlbo.bank_party_id,
111 jlbo.BANK_OCCURRENCE_CODE,
112 jlbo.BANK_OCCURRENCE_TYPE,
113 b.OUTPUT_FORMAT,
114 JLBRRMA.BANK_CHARGES_CCID,
115 JLBRRMA.COLL_ENDORSEMENT_CCID,
116 JLBRRMA.BILLS_COLLECTION_CCID,
117 JLBRRMA.OTHER_CREDITS_CCID,
118 JLBRRMA.FACTORING_DOCS_CCID,
119 JLBRRMA.CALCULATED_INTEREST_CCID,
120 JLBRRMA.INTEREST_WRITEOFF_CCID,
121 JLBRRMA.BILLS_DISCOUNT_CCID,
122 JLBRRMA.DISC_ENDORSEMENT_CCID,
123 JLBRRMA.DISCOUNTED_BILLS_CCID,
124 JLBRRMA.FACTORING_INTEREST_CCID,
125 RMA.CASH_CCID,
126 RMA.EARNED_CCID,
127 RMA.ON_ACCOUNT_CCID,
128 RMA.UNAPPLIED_CCID,
129 RMA.UNEARNED_CCID,
130 RMA.UNIDENTIFIED_CCID,
131 JLBRRMA.ABATEMENT_WRITEOFF_CCID,
132 JLBRRMA.ABATEMENT_REVENUE_CCID,
133 JLBRRMA.INTEREST_REVENUE_CCID,
134 JLBRRMA.CALCULATED_INTEREST_RECTRX_ID,
135 JLBRRMA.INTEREST_WRITEOFF_RECTRX_ID,
136 JLBRRMA.INTEREST_REVENUE_RECTRX_ID,
137 JLBRRMA.ABATEMENT_WRITEOFF_RECTRX_ID,
138 JLBRRMA.ABATEMENT_REVENUE_RECTRX_ID,
139 CSC.GL_DATE,
140 CeBankAccount.bank_account_id,
141 DC.org_id,
142 PS.CUSTOMER_ID
143 from AR_PAYMENT_SCHEDULES_ALL PS, JL_BR_AR_COLLECTION_DOCS_ALL DC,
144 JL_BR_AR_BORDEROS B,
145 AR_RECEIPT_METHOD_ACCOUNTS_ALL RMA,
146 JL_BR_AR_SELECT_ACCOUNTS_ALL CSC,
147 JL_BR_AR_REC_MET_ACCTS_EXT_ALL JLBRRMA,
148 JL_BR_AR_BANK_OCCURRENCES jlbo,
149 CE_BANK_ACCOUNTS CeBankAccount,
150 CE_BANK_ACCT_USES_ALL CeBankAcctUse,
151 HZ_PARTIES HzPartyBank
152 Where b.bank_acct_use_id = CeBankAcctUse.bank_acct_use_id
153 And CeBankAccount.bank_account_id = CeBankAcctUse.bank_account_id
154 And CeBankAccount.BANK_ID = HzPartyBank.PARTY_ID
155 --And HzPartyBank.COUNTRY = 'BR'
156 And DC.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
157 And PS.STATUS = 'OP'
158 And B.BORDERO_STATUS = 'SELECTED'
159 And DC.DOCUMENT_STATUS = 'SELECTED'
160 And B.BORDERO_ID = DC.BORDERO_ID
161 And DC.BORDERO_ID = P_BORDERO_ID
162 And CSC.SELECT_ACCOUNT_ID = B.SELECT_ACCOUNT_ID
163 And JLBRRMA.RECEIPT_METHOD_ID = DC.RECEIPT_METHOD_ID
164 And JLBRRMA.BANK_ACCT_USE_ID = B.BANK_ACCT_USE_ID
165 And RMA.RECEIPT_METHOD_ID = DC.RECEIPT_METHOD_ID
166 And RMA.REMIT_BANK_ACCT_USE_ID = B.BANK_ACCT_USE_ID
167 And jlbo.BANK_PARTY_ID = HzPartyBank.party_id
168 And jlbo.STD_OCCURRENCE_CODE = 'REMITTANCE'
169 And jlbo.BANK_OCCURRENCE_TYPE = 'REMITTANCE_OCCURRENCE';
170
171
172 CURSOR_PS PS%rowtype;
173 l_ps_rec ar_payment_schedules%rowtype;
174
175 begin
176 --mo_global.set_policy_context('S',3812);
177 select org_id into l_org_id from jl_br_ar_borderos_all
178 where bordero_id = p_bordero_id;
179 mo_global.set_policy_context('S',l_org_id);
180 P_PROC_STATUS := 0;
181 SELECT g.set_of_books_id, g.currency_code
182 INTO l_set_of_books_id, l_currency_code
183 FROM gl_sets_of_books g, ar_system_parameters a
184 where g.set_of_books_id = a.set_of_books_id
185 and rownum=1;
186
187
188 OPEN check1;
189 LOOP
190 FETCH check1 INTO var_bordero_status_chk, var_selection_control,var_bordero_type;
191 EXIT WHEN check1%NOTFOUND;
192
193 OPEN check2;
194 LOOP
195 FETCH check2 INTO var_selection_control_chk;
196 EXIT WHEN check2%NOTFOUND;
197
198 IF var_bordero_type = 'COLLECTION' THEN
199 open PS;
200 loop
201 fetch PS into CURSOR_PS;
202 exit when PS%notfound;
203
204 /* Generate remittance occurrence to the document */
205
206 -- SLA Uptake - Bug#4301543
207 select JL_BR_AR_OCCURRENCE_DOCS_S.NEXTVAL
208 into l_occurrence_id
209 from dual;
210
211 -- CE uptake - Bug#2932986
212 insert into JL_BR_AR_OCCURRENCE_DOCS_ALL
213 (OCCURRENCE_ID,
214 DOCUMENT_ID,
215 BANK_OCCURRENCE_CODE,
216 --BANK_NUMBER,
217 BANK_PARTY_ID,
218 BANK_OCCURRENCE_TYPE,
219 OCCURRENCE_DATE,
220 OCCURRENCE_STATUS,
221 ORIGINAL_REMITTANCE_MEDIA,
222 REMITTANCE_MEDIA,
223 SELECTION_DATE,
224 BORDERO_ID,
225 LAST_UPDATE_DATE,
226 LAST_UPDATED_BY,
227 LAST_UPDATE_LOGIN,
228 CREATION_DATE,
229 CREATED_BY,
230 PORTFOLIO_CODE,
231 TRADE_NOTE_NUMBER,
232 DUE_DATE,
233 DOCUMENT_AMOUNT,
234 BANK_INSTRUCTION_CODE1,
235 BANK_INSTRUCTION_CODE2,
236 NUM_DAYS_INSTRUCTION,
237 INTEREST_PERCENT,
238 INTEREST_PERIOD,
239 INTEREST_AMOUNT,
240 GRACE_DAYS,
241 DISCOUNT_LIMIT_DATE,
242 DISCOUNT_AMOUNT,
243 CUSTOMER_ID,
244 SITE_USE_ID,
245 ABATEMENT_AMOUNT,
246 FLAG_POST_GL,
247 GL_DATE,
248 ENDORSEMENT_CREDIT_CCID,
249 ENDORSEMENT_DEBIT_CCID,
250 ENDORSEMENT_DEBIT_AMOUNT,
251 ENDORSEMENT_CREDIT_AMOUNT,
252 BANK_CHARGES_CREDIT_CCID,
253 BANK_CHARGES_DEBIT_CCID,
254 BANK_CHARGES_DEBIT_AMOUNT,
255 BANK_CHARGES_CREDIT_AMOUNT,
256 ORG_ID)
257 select
258 l_occurrence_id, -- SLA Uptake - Bug#4301543
259 CURSOR_PS.DOCUMENT_ID,
260 CURSOR_PS.BANK_OCCURRENCE_CODE,
261 --CURSOR_PS.BANK_NUMBER,
262 CURSOR_PS.BANK_PARTY_ID,
263 CURSOR_PS.BANK_OCCURRENCE_TYPE,
264 SYSDATE,
265 'CONFIRMED',
266 CURSOR_PS.OUTPUT_FORMAT,
267 CURSOR_PS.OUTPUT_FORMAT,
268 null,
269 P_BORDERO_ID,
270 SYSDATE,
271 P_USER_ID,
272 P_USER_ID,
273 SYSDATE,
274 P_USER_ID,
275 CURSOR_PS.PORTFOLIO_CODE,
276 CURSOR_PS.GLOBAL_ATTRIBUTE10,
277 CURSOR_PS.DUE_DATE,
278 CURSOR_PS.AMOUNT_DUE_REMAINING,
279 CURSOR_PS.BANK_INSTRUCTION_CODE1,
280 CURSOR_PS.BANK_INSTRUCTION_CODE2,
281 CURSOR_PS.NUM_DAYS_INSTRUCTION,
282 fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), /* Bug 2724399 */
283 nvl(CT.GLOBAL_ATTRIBUTE3,''),
284 fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), /* Bug 2724399 */
285 nvl(CT.GLOBAL_ATTRIBUTE5,''),
286 nvl(T.DISCOUNT_DAYS,0) + CURSOR_PS.DUE_DATE,
287 nvl(CURSOR_PS.FACTORING_AMOUNT,0),
288 CT.BILL_TO_CUSTOMER_ID,
289 CT.BILL_TO_SITE_USE_ID,
290 0,
291 'N',
292 CURSOR_PS.GL_DATE,
293 CURSOR_PS.COLL_ENDORSEMENT_CCID,
294 CURSOR_PS.BILLS_COLLECTION_CCID,
295 CURSOR_PS.AMOUNT_DUE_REMAINING,
296 CURSOR_PS.AMOUNT_DUE_REMAINING,
297 decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'', CURSOR_PS.CASH_CCID),
298 decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'',CURSOR_PS.BANK_CHARGES_CCID),
299 CURSOR_PS.BANK_CHARGE_AMOUNT,
300 CURSOR_PS.BANK_CHARGE_AMOUNT,
301 CURSOR_PS.org_id
302 from RA_CUSTOMER_TRX CT, RA_TERMS_LINES_DISCOUNTS T,
303 AR_PAYMENT_SCHEDULES_ALL PS
304 where CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
305 and T.TERM_ID(+) = PS.TERM_ID
306 and T.SEQUENCE_NUM(+) = PS.TERMS_SEQUENCE_NUMBER
307 and PS.PAYMENT_SCHEDULE_ID = CURSOR_PS.PAYMENT_SCHEDULE_ID;
308 --commented below code for bug 9468277.
309 /* select occurrence_id into l_occ from jl_br_ar_occurrence_docs_all
310 where document_id = cursor_ps.document_id; */
311
312 -- SLA Uptake - Bug#4301543
313 JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists (
314 p_event_type_code => 'REMIT_COLL_DOC' ,
315 p_event_date => SYSDATE ,
316 p_document_id => CURSOR_PS.DOCUMENT_ID ,
317 p_gl_date => CURSOR_PS.GL_DATE ,
318 p_occurrence_id => l_occurrence_id ,
319 p_bank_occurrence_type => CURSOR_PS.BANK_OCCURRENCE_TYPE ,
320 p_bank_occurrence_code => CURSOR_PS.BANK_OCCURRENCE_CODE ,
321 p_std_occurrence_code => 'REMITTANCE' ,
322 p_bordero_type => var_bordero_type ,
323 p_endorsement_amt => CURSOR_PS.AMOUNT_DUE_REMAINING ,
324 p_bank_charges_amt => CURSOR_PS.BANK_CHARGE_AMOUNT ,
325 p_factoring_charges_amt => NULL ,
326 p_event_id => l_event_id
327 );
328
329 UPDATE JL_BR_AR_OCCURRENCE_DOCS_ALL
330 SET event_id =l_event_id
331 WHERE occurrence_id = l_occurrence_id;
332 -- End SLA Uptake - Bug#4301543
333
334 /* Update collection flag to WRITE_OFF */
335 /* update AR_PAYMENT_SCHEDULES
336 set GLOBAL_ATTRIBUTE11 = 'Y',
337 GLOBAL_ATTRIBUTE9 = 'BANK'
338 where PAYMENT_SCHEDULE_ID = CURSOR_PS.PAYMENT_SCHEDULE_ID;
339 */
340 mo_global.set_policy_context('S',cursor_ps.org_id);
341 begin
342 arp_ps_pkg.fetch_p(CURSOR_PS.PAYMENT_SCHEDULE_ID, l_ps_rec);
343 arp_ps_pkg.lock_p(CURSOR_PS.PAYMENT_SCHEDULE_ID);
344 l_ps_rec.GLOBAL_ATTRIBUTE12 := 'FORMATTED'; --bug 12606008
345 l_ps_rec.GLOBAL_ATTRIBUTE11 := 'Y';
346 l_ps_rec.GLOBAL_ATTRIBUTE9 := 'BANK';
347 arp_ps_pkg.update_p(l_ps_rec, CURSOR_PS.PAYMENT_SCHEDULE_ID);
348 exception
349 when others then
350 null;
351 end;
352
353 /* Update status to FORMATTED and update bank accounts */
354 update JL_BR_AR_COLLECTION_DOCS_ALL
355 set DOCUMENT_STATUS = 'FORMATTED',
356 CASH_CCID = cursor_ps.CASH_CCID,
357 BANK_CHARGES_CCID = cursor_ps.BANK_CHARGES_CCID,
358 COLL_ENDORSEMENTS_CCID = cursor_ps.COLL_ENDORSEMENT_CCID,
359 BILLS_COLLECTION_CCID = cursor_ps.BILLS_COLLECTION_CCID,
360 CALCULATED_INTEREST_CCID = cursor_ps.CALCULATED_INTEREST_CCID,
361 INTEREST_WRITEOFF_CCID = cursor_ps.INTEREST_WRITEOFF_CCID,
362 ABATEMENT_WRITEOFF_CCID = cursor_ps.ABATEMENT_WRITEOFF_CCID,
363 ABATEMENT_REVENUE_CCID = cursor_ps.ABATEMENT_REVENUE_CCID,
364 INTEREST_REVENUE_CCID = cursor_ps.INTEREST_REVENUE_CCID,
365 CALCULATED_INTEREST_RECTRX_ID = cursor_ps.CALCULATED_INTEREST_RECTRX_ID,
366 INTEREST_WRITEOFF_RECTRX_ID = cursor_ps.INTEREST_WRITEOFF_RECTRX_ID,
367 INTEREST_REVENUE_RECTRX_ID = cursor_ps.INTEREST_REVENUE_RECTRX_ID,
368 ABATEMENT_WRITEOFF_RECTRX_ID = cursor_ps.ABATEMENT_WRITEOFF_RECTRX_ID,
369 ABATE_REVENUE_RECTRX_ID = cursor_ps.ABATEMENT_REVENUE_RECTRX_ID
370 where DOCUMENT_ID = CURSOR_PS.DOCUMENT_ID;
371
372 end loop;
373 close PS;
374 ELSIF var_bordero_type = 'FACTORING' THEN -- Change made for factoring remittance batch
375 open PS;
376 LOOP
377 FETCH PS into CURSOR_PS;
378 exit when PS%notfound;
379
380 Ar_receipt_api_pub.create_cash
381 ( p_api_version => 1.0,
382 p_init_msg_list => FND_API.G_FALSE,
383 p_commit => FND_API.G_FALSE,
384 x_return_status => l_return_status,
385 x_msg_count => l_msg_count,
386 x_msg_data => l_msg_data,
387 p_currency_code => l_currency_code,
388 p_amount => cursor_ps.amount_due_remaining,
389 p_receipt_number => cursor_ps.document_id,
390 p_receipt_date => sysdate,
391 p_gl_date => sysdate,
392 p_customer_id => cursor_ps.customer_id,
393 p_remittance_bank_account_id => cursor_ps.bank_account_id,
394 p_receipt_method_id => cursor_ps.receipt_method_id,
395 p_called_from => 'JLBRRATB',
396 p_cr_id => x_cash_receipt_id);
397
398 fnd_file.put_line(FND_FILE.lOG,'After creating the receipt'||to_char(x_cash_receipt_id)||'return status'||l_return_status||'message '||l_msg_data||'message count'||to_char(l_msg_count));
399 /* Generate remittance occurrence to the document */
400 LOOP
401
402 l_mesg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
403 IF l_mesg IS NULL THEN
404 EXIT;
405 ELSE
406 fnd_file.put_line(FND_FILE.lOG,'After creating the receipt'||to_char(x_cash_receipt_id)||'return status'||l_return_status||'message count'||to_char(l_msg_count)||'message '||l_mesg);
407 END IF;
408
409 END LOOP;
410
411 -- SLA Uptake - Bug#4301543
412 select JL_BR_AR_OCCURRENCE_DOCS_S.NEXTVAL
413 into l_occurrence_id
414 from dual;
415
416
417 INSERT INTO JL_BR_AR_OCCURRENCE_DOCS_ALL
418 (OCCURRENCE_ID,
419 DOCUMENT_ID,
420 BANK_OCCURRENCE_CODE,
421 --BANK_NUMBER,
422 BANK_PARTY_ID,
423 BANK_OCCURRENCE_TYPE,
424 OCCURRENCE_DATE,
425 OCCURRENCE_STATUS,
426 ORIGINAL_REMITTANCE_MEDIA,
427 REMITTANCE_MEDIA,
428 SELECTION_DATE,
429 BORDERO_ID,
430 LAST_UPDATE_DATE,
431 LAST_UPDATED_BY,
432 LAST_UPDATE_LOGIN,
433 CREATION_DATE,
434 CREATED_BY,
435 PORTFOLIO_CODE,
436 TRADE_NOTE_NUMBER,
437 DUE_DATE,
438 DOCUMENT_AMOUNT,
439 BANK_INSTRUCTION_CODE1,
440 BANK_INSTRUCTION_CODE2,
441 NUM_DAYS_INSTRUCTION,
442 INTEREST_PERCENT,
443 INTEREST_PERIOD,
444 INTEREST_AMOUNT,
445 GRACE_DAYS,
446 DISCOUNT_LIMIT_DATE,
447 DISCOUNT_AMOUNT,
448 CUSTOMER_ID,
449 SITE_USE_ID,
450 ABATEMENT_AMOUNT,
451 FLAG_POST_GL,
452 GL_DATE,
453 ENDORSEMENT_CREDIT_CCID,
454 ENDORSEMENT_DEBIT_CCID,
455 ENDORSEMENT_DEBIT_AMOUNT,
456 ENDORSEMENT_CREDIT_AMOUNT,
457 BANK_CHARGES_CREDIT_CCID,
458 BANK_CHARGES_DEBIT_CCID,
459 BANK_CHARGES_DEBIT_AMOUNT,
460 BANK_CHARGES_CREDIT_AMOUNT,
461 FACTOR_INTEREST_CREDIT_CCID,
462 FACTOR_INTEREST_DEBIT_CCID,
463 FACTOR_INTEREST_DEBIT_AMOUNT,
464 FACTOR_INTEREST_CREDIT_AMOUNT,
465 ORG_ID)
466 select
467 l_occurrence_id, -- SLA Uptake - Bug#4301543
468 CURSOR_PS.DOCUMENT_ID,
469 CURSOR_PS.BANK_OCCURRENCE_CODE,
470 --CURSOR_PS.BANK_NUMBER,
471 CURSOR_PS.BANK_PARTY_ID,
472 CURSOR_PS.BANK_OCCURRENCE_TYPE,
473 SYSDATE,
474 'CONFIRMED',
475 CURSOR_PS.OUTPUT_FORMAT,
476 CURSOR_PS.OUTPUT_FORMAT,
477 null,
478 P_BORDERO_ID,
479 SYSDATE,
480 P_USER_ID,
481 P_USER_ID,
482 SYSDATE,
483 P_USER_ID,
484 CURSOR_PS.PORTFOLIO_CODE,
485 CURSOR_PS.GLOBAL_ATTRIBUTE10,
486 CURSOR_PS.DUE_DATE,
487 CURSOR_PS.AMOUNT_DUE_REMAINING,
488 CURSOR_PS.BANK_INSTRUCTION_CODE1,
489 CURSOR_PS.BANK_INSTRUCTION_CODE2,
490 CURSOR_PS.NUM_DAYS_INSTRUCTION,
491 fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), -- Bug 3107496
492 nvl(CT.GLOBAL_ATTRIBUTE3,''),
493 fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), -- Bug 3107496
494 nvl(CT.GLOBAL_ATTRIBUTE5,''),
495 nvl(T.DISCOUNT_DAYS,0) + CURSOR_PS.DUE_DATE,
496 nvl(CURSOR_PS.FACTORING_AMOUNT,0),
497 CT.BILL_TO_CUSTOMER_ID,
498 CT.BILL_TO_SITE_USE_ID,
499 0,
500 'N',
501 CURSOR_PS.GL_DATE,
502 -- Bug#8302889 in factoring
503 -- CURSOR_PS.BILLS_DISCOUNT_CCID,
504 CURSOR_PS.DISC_ENDORSEMENT_CCID,
505 CURSOR_PS.BILLS_DISCOUNT_CCID,
506 CURSOR_PS.AMOUNT_DUE_REMAINING,
507 CURSOR_PS.AMOUNT_DUE_REMAINING,
508 decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'', CURSOR_PS.CASH_CCID),
509 decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'',CURSOR_PS.BANK_CHARGES_CCID),
510 CURSOR_PS.BANK_CHARGE_AMOUNT,
511 CURSOR_PS.BANK_CHARGE_AMOUNT,
512 decode(CURSOR_PS.FACTORING_AMOUNT,'','',0,'', CURSOR_PS.CASH_CCID),
513 decode(CURSOR_PS.FACTORING_AMOUNT,'','',0,'',CURSOR_PS.FACTORING_INTEREST_CCID),
514 CURSOR_PS.FACTORING_AMOUNT,
515 CURSOR_PS.FACTORING_AMOUNT,
516 CURSOR_PS.ORG_ID
517 from RA_CUSTOMER_TRX CT, RA_TERMS_LINES_DISCOUNTS T,
518 AR_PAYMENT_SCHEDULES PS
519 where CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
520 and T.TERM_ID(+) = PS.TERM_ID
521 and T.SEQUENCE_NUM(+) = PS.TERMS_SEQUENCE_NUMBER
522 and PS.PAYMENT_SCHEDULE_ID = CURSOR_PS.PAYMENT_SCHEDULE_ID;
523
524 -- SLA Uptake - Bug#4301543
525 JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists (
526 p_event_type_code => 'REMIT_FACT_DOC' ,
527 p_event_date => SYSDATE ,
528 p_document_id => CURSOR_PS.DOCUMENT_ID ,
529 p_gl_date => CURSOR_PS.GL_DATE ,
530 p_occurrence_id => l_occurrence_id ,
531 p_bank_occurrence_type => CURSOR_PS.BANK_OCCURRENCE_TYPE ,
532 p_bank_occurrence_code => CURSOR_PS.BANK_OCCURRENCE_CODE ,
533 p_std_occurrence_code => 'REMITTANCE' ,
534 p_bordero_type => var_bordero_type ,
535 p_endorsement_amt => CURSOR_PS.AMOUNT_DUE_REMAINING ,
536 p_bank_charges_amt => CURSOR_PS.BANK_CHARGE_AMOUNT ,
537 p_factoring_charges_amt => CURSOR_PS.FACTORING_AMOUNT ,
538 p_event_id => l_event_id
539 );
540
541 UPDATE JL_BR_AR_OCCURRENCE_DOCS_ALL
542 SET event_id =l_event_id
543 WHERE occurrence_id = l_occurrence_id;
544 -- End SLA Uptake - Bug#4301543
545
546 arp_ps_pkg.fetch_p(CURSOR_PS.PAYMENT_SCHEDULE_ID, l_ps_rec);
547 arp_ps_pkg.lock_p(CURSOR_PS.PAYMENT_SCHEDULE_ID);
548 l_ps_rec.GLOBAL_ATTRIBUTE12 := 'FORMATTED'; --bug 12606008
549 l_ps_rec.GLOBAL_ATTRIBUTE11 := 'Y';
550 l_ps_rec.GLOBAL_ATTRIBUTE9 := 'BANK';
551 arp_ps_pkg.update_p(l_ps_rec, CURSOR_PS.PAYMENT_SCHEDULE_ID);
552
553 /* Update status to FORMATTED and update bank accounts */
554 update JL_BR_AR_COLLECTION_DOCS_ALL
555 set DOCUMENT_STATUS = 'FORMATTED',
556 CASH_CCID = cursor_ps.CASH_CCID,
557 BANK_CHARGES_CCID = cursor_ps.BANK_CHARGES_CCID,
558 COLL_ENDORSEMENTS_CCID = cursor_ps.DISC_ENDORSEMENT_CCID,
559 BILLS_COLLECTION_CCID = cursor_ps.BILLS_DISCOUNT_CCID,
560 CALCULATED_INTEREST_CCID = cursor_ps.CALCULATED_INTEREST_CCID,
561 INTEREST_WRITEOFF_CCID = cursor_ps.INTEREST_WRITEOFF_CCID,
562 ABATEMENT_WRITEOFF_CCID = cursor_ps.ABATEMENT_WRITEOFF_CCID,
563 ABATEMENT_REVENUE_CCID = cursor_ps.ABATEMENT_REVENUE_CCID,
564 INTEREST_REVENUE_CCID = cursor_ps.INTEREST_REVENUE_CCID,
565 CALCULATED_INTEREST_RECTRX_ID = cursor_ps.CALCULATED_INTEREST_RECTRX_ID,
566 INTEREST_WRITEOFF_RECTRX_ID = cursor_ps.INTEREST_WRITEOFF_RECTRX_ID, INTEREST_REVENUE_RECTRX_ID = cursor_ps.INTEREST_REVENUE_RECTRX_ID,
567 ABATEMENT_WRITEOFF_RECTRX_ID = cursor_ps.ABATEMENT_WRITEOFF_RECTRX_ID,
568 ABATE_REVENUE_RECTRX_ID = cursor_ps.ABATEMENT_REVENUE_RECTRX_ID,
569 CASH_RECEIPT_ID = x_cash_receipt_id
570 where DOCUMENT_ID = CURSOR_PS.DOCUMENT_ID;
571
572 END LOOP;
573 Close PS;
574 END IF;
575
576 select CS.SELECTION_STATUS,
577 B.SELECTION_CONTROL_ID,
578 B.SELECT_ACCOUNT_ID
579 into PL_CS_SELECTION_STATUS,
580 PL_SELECTION_CONTROL_ID,
581 PL_SELECT_ACCOUNT_ID
582 from JL_BR_AR_SELECT_CONTROLS_ALL CS,
583 JL_BR_AR_BORDEROS B
584 where CS.SELECTION_CONTROL_ID = B.SELECTION_CONTROL_ID
585 and B.BORDERO_ID = P_BORDERO_ID;
586
587 if PL_CS_SELECTION_STATUS = 'SELECTED'
588 then
589 /* Update status to FORMATTED */
590 update JL_BR_AR_SELECT_CONTROLS
591 set SELECTION_STATUS = 'FORMATTED',
592 REMITTANCE_DATE = SYSDATE
593 where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
594 update JL_BR_AR_SELECT_ACCOUNTS
595 set FORMAT_DATE= SYSDATE,
596 REMITTANCE_DATE = SYSDATE
597 where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
598 else
599 /* Update remittance date */
600 update JL_BR_AR_SELECT_CONTROLS
601 set REMITTANCE_DATE = SYSDATE
602 where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
603 update JL_BR_AR_SELECT_ACCOUNTS
604 set REMITTANCE_DATE = SYSDATE
605 where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
606 end if;
607
608 /* Update status to FORMATTED */
609 update JL_BR_AR_BORDEROS
610 set BORDERO_STATUS = 'FORMATTED',
611 REMITTANCE_DATE = SYSDATE
612 where BORDERO_ID = P_BORDERO_ID;
613
614 BEGIN
615 insert into JL_BR_AR_REMIT_BORDEROS_ALL
616 (FORMAT_REQUEST_ID,
617 LAST_UPDATE_DATE,
618 LAST_UPDATED_BY,
619 LAST_UPDATE_LOGIN,
620 CREATION_DATE,
621 CREATED_BY,
622 BORDERO_ID,
623 OUTPUT_PROGRAM_ID,
624 ORG_ID)
625 select
626 fnd_global.conc_request_id,
627 sysdate,
628 fnd_global.user_id,
629 fnd_global.login_id,
630 sysdate,
631 fnd_global.user_id,
632 bordero_id,
633 output_program_id,
634 org_id
635 from jl_br_ar_borderos_all
636 where bordero_id = P_BORDERO_ID;
637 EXCEPTION
638 WHEN OTHERS THEN
639 null;
640 end;
641
642
643 P_PROC_STATUS := 1;
644 END LOOP;
645 CLOSE check2;
646
647 END LOOP;
648 CLOSE check1;
649 COMMIT;
650
651 END remit_collection;
652
653
654 PROCEDURE remit_occurrence (P_BORDERO_ID IN NUMBER,
655 P_PROC_STATUS IN OUT NOCOPY NUMBER) is
656 PL_SELECTION_CONTROL_ID number;
657 PL_SELECT_ACCOUNT_ID number;
658 PL_CS_SELECTION_STATUS varchar2(30);
659 var_bordero_status_chk VARCHAR2(30);
660 var_selection_control NUMBER;
661 var_selection_control_chk NUMBER;
662
663 CURSOR check1 IS
664 SELECT bordero_status, selection_control_id
665 FROM jl_br_ar_borderos_all
666 WHERE bordero_id = P_BORDERO_ID
667 AND bordero_status in ('SELECTED', 'FORMATTED')
668 FOR UPDATE NOWAIT;
669
670 CURSOR check2 IS
671 SELECT selection_control_id
672 FROM jl_br_ar_select_controls_all
673 WHERE selection_control_id = var_selection_control
674 FOR UPDATE;
675
676 /* This cursor is used to read documents from Borderos that
677 was not formated */
678 cursor OC is
679 select OD.OCCURRENCE_ID, BO.STD_OCCURRENCE_CODE, OD.DOCUMENT_ID
680 from JL_BR_AR_OCCURRENCE_DOCS_ALL OD,
681 JL_BR_AR_BORDEROS B,
682 JL_BR_AR_BANK_OCCURRENCES BO
683 where OD.OCCURRENCE_STATUS = 'SELECTED'
684 and B.BORDERO_STATUS = 'SELECTED'
685 and B.BORDERO_ID = OD.BORDERO_ID
686 and OD.BORDERO_ID = P_BORDERO_ID
687 and BO.BANK_OCCURRENCE_CODE = OD.BANK_OCCURRENCE_CODE
688 --and BO.BANK_NUMBER = OD.BANK_NUMBER;
689 and BO.BANK_PARTY_ID = OD.BANK_PARTY_ID;
690
691 l_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
692 l_ps_rec ar_payment_schedules%ROWTYPE;
693 begin
694 P_PROC_STATUS := 0;
695 OPEN check1;
696 LOOP
697 FETCH check1 INTO var_bordero_status_chk, var_selection_control;
698 EXIT WHEN check1%NOTFOUND;
699
700 OPEN check2;
701 LOOP
702 FETCH check2 INTO var_selection_control_chk;
703 EXIT WHEN check2%NOTFOUND;
704
705 for TMP in OC loop
706 /* Update occurrence status to CONFIRMED */
707 update JL_BR_AR_OCCURRENCE_DOCS_ALL
708 set OCCURRENCE_STATUS = 'CONFIRMED'
709 where OCCURRENCE_ID = TMP.OCCURRENCE_ID;
710 if TMP.STD_OCCURRENCE_CODE = 'PROTEST' then
711 /* update AR_PAYMENT_SCHEDULES
712 set GLOBAL_ATTRIBUTE9 = 'REGISTRY'
713 where PAYMENT_SCHEDULE_ID = (select PAYMENT_SCHEDULE_ID
714 from JL_BR_AR_COLLECTION_DOCS
715 where DOCUMENT_ID = TMP.DOCUMENT_ID);
716 */
717
718 /* Replace Update by AR's Table Handlers. Bug # 2249731 */
719 SELECT payment_schedule_id
720 INTO l_ps_id
721 FROM jl_br_ar_collection_docs
722 WHERE document_id = TMP.DOCUMENT_ID;
723
724 arp_ps_pkg.fetch_p(l_ps_id, l_ps_rec);
725 arp_ps_pkg.lock_p(l_ps_id);
726 l_ps_rec.GLOBAL_ATTRIBUTE9 := 'REGISTRY';
727 arp_ps_pkg.update_p(l_ps_rec, l_ps_id);
728
729 /* Following elsif added for Bug 865082 */
730 elsif TMP.STD_OCCURRENCE_CODE = 'WRITE_OFF_REQUISITION' then
731 /* UPDATE ar_payment_schedules
732 SET selected_for_receipt_batch_id = NULL,
733 global_attribute9 = 'MANUAL_RECEIPT',
734 global_attribute11 = 'N'
735 where PAYMENT_SCHEDULE_ID = (select PAYMENT_SCHEDULE_ID
736 from JL_BR_AR_COLLECTION_DOCS
737 where DOCUMENT_ID = TMP.DOCUMENT_ID);
738 */
739
740 /* Replace Update by AR's Table Handlers. Bug # 2249731 */
741
742 SELECT payment_schedule_id
743 INTO l_ps_id
744 FROM jl_br_ar_collection_docs
745 WHERE document_id = TMP.DOCUMENT_ID;
746
747 arp_ps_pkg.fetch_p(l_ps_id, l_ps_rec);
748 arp_ps_pkg.lock_p(l_ps_id);
749 l_ps_rec.GLOBAL_ATTRIBUTE12 := NULL; --bug 12606008
750 l_ps_rec.selected_for_receipt_batch_id := NULL;
751 l_ps_rec.GLOBAL_ATTRIBUTE9 := 'MANUAL_RECEIPT';
752 l_ps_rec.GLOBAL_ATTRIBUTE11 := 'N';
753 arp_ps_pkg.update_p(l_ps_rec, l_ps_id);
754
755 end if;
756 end loop;
757 select B.SELECTION_CONTROL_ID,
758 B.SELECT_ACCOUNT_ID,
759 CS.SELECTION_STATUS
760 into PL_SELECTION_CONTROL_ID,
761 PL_SELECT_ACCOUNT_ID,
762 PL_CS_SELECTION_STATUS
763 from JL_BR_AR_BORDEROS_ALL B, JL_BR_AR_SELECT_CONTROLS_ALL CS
764 where B.BORDERO_ID = P_BORDERO_ID
765 and CS.SELECTION_CONTROL_ID = B.SELECTION_CONTROL_ID;
766 /* Update bordero status to FORMATTED */
767 update JL_BR_AR_BORDEROS_ALL
768 set BORDERO_STATUS = 'FORMATTED',
769 REMITTANCE_DATE = SYSDATE
770 where BORDERO_ID = P_BORDERO_ID;
771
772 if PL_CS_SELECTION_STATUS = 'SELECTED'
773 then
774 /* Update selection status to FORMATTED */
775 update JL_BR_AR_SELECT_CONTROLS_ALL
776 set SELECTION_STATUS = 'FORMATTED',
777 GENERATION_DATE = SYSDATE,
778 REMITTANCE_DATE = SYSDATE
779 where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
780 update JL_BR_AR_SELECT_ACCOUNTS
781 set FORMAT_DATE = SYSDATE,
782 REMITTANCE_DATE = SYSDATE
783 where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
784 else
785 /* Update remittance date */
786 update JL_BR_AR_SELECT_CONTROLS_ALL
787 set REMITTANCE_DATE = SYSDATE
788 where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
789 update JL_BR_AR_SELECT_ACCOUNTS
790 set REMITTANCE_DATE = SYSDATE
791 where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
792 end if;
793
794 insert into JL_BR_AR_REMIT_BORDEROS_ALL
795 (FORMAT_REQUEST_ID,
796 LAST_UPDATE_DATE,
797 LAST_UPDATED_BY,
798 LAST_UPDATE_LOGIN,
799 CREATION_DATE,
800 CREATED_BY,
801 BORDERO_ID,
802 OUTPUT_PROGRAM_ID,
803 ORG_ID)
804 select
805 fnd_global.conc_request_id,
806 sysdate,
807 fnd_global.user_id,
808 fnd_global.login_id,
809 sysdate,
810 fnd_global.user_id,
811 bordero_id,
812 output_program_id,
813 mo_global.get_current_org_id
814 from jl_br_ar_borderos
815 where bordero_id = P_BORDERO_ID;
816
817
818 P_PROC_STATUS := 1;
819 END LOOP;
820 CLOSE check2;
821
822 END LOOP;
823 CLOSE check1;
824 COMMIT;
825
826 END remit_occurrence;
827
828 /*===========================================================================+
829 | FUNCTION |
830 | get_acct_line_type_name |
831 | |
832 | DESCRIPTION |
833 | This function is required to be called in occurrence view, where it |
834 | passes the meaning of the lookup code which is passed as the parameter, |
835 | to the view column ACCT_LINE_TYPE_NAME which is required to be shown |
836 | in SLA forms to name the account line. |
837 | |
838 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
839 | none |
840 | |
841 | ARGUMENTS |
842 | none |
843 | |
844 | USAGE NOTES: |
845 | Begin |
846 | x := JL_BR_AR_REMIT_COLL_OCCUR.get_acct_line_type_name; |
847 | End; |
848 | |
849 | MODIFICATION HISTORY |
850 | 19-Apr-00 Santosh Vaze Created |
851 | |
852 +===========================================================================*/
853
854 FUNCTION get_acct_line_type_name(code VARCHAR2) RETURN VARCHAR2 IS
855 name VARCHAR2(100);
856 BEGIN
857
858 BEGIN
859 SELECT meaning
860 INTO name
861 FROM fnd_lookups
862 WHERE lookup_code = code
863 AND lookup_type = 'JLBR_AR_SLA_ACCT_LINE_TYPE';
864 EXCEPTION
865 WHEN OTHERS THEN
866 name := NULL;
867 END;
868 RETURN ( name );
869 END get_acct_line_type_name;
870
871 /*===========================================================================+
872 | FUNCTION |
873 | get_trx_class_name |
874 | |
875 | DESCRIPTION |
876 | This function is required to be called in bank transfer view, where it |
877 | passes the meaning of the lookup code to the view column TRX_CLASS_NAME |
878 | which is required to be shown in SLA forms to name the transaction class|
879 | |
880 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
881 | none |
882 | |
883 | ARGUMENTS |
884 | none |
885 | |
886 | USAGE NOTES: |
887 | Begin |
888 | x := JL_BR_AR_REMIT_COLL_OCCUR.get_trx_class_name; |
889 | End; |
890 | |
891 | MODIFICATION HISTORY |
892 | 19-Apr-00 Santosh Vaze Created |
893 | |
894 +===========================================================================*/
895
896 FUNCTION get_trx_class_name(trx_class VARCHAR2) RETURN VARCHAR2 IS
897 name VARCHAR2(100);
898 BEGIN
899
900 BEGIN
901 SELECT meaning
902 INTO name
903 FROM fnd_lookups
904 WHERE lookup_code = trx_class
905 AND lookup_type = 'JLBR_AR_SLA_TRX_CLASS';
906 EXCEPTION
907 WHEN OTHERS THEN
908 name := NULL;
909 END;
910 RETURN ( name );
911 END get_trx_class_name;
912
913 END JL_BR_AR_REMIT_COLL_OCCUR;