[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.13.12000000.2 2007/02/02 19:43:08 appradha noship $*/
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 select occurrence_id into l_occ from jl_br_ar_occurrence_docs_all
309 where document_id = cursor_ps.document_id;
310
311 -- SLA Uptake - Bug#4301543
312 JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists (
313 p_event_type_code => 'REMIT_COLL_DOC' ,
314 p_event_date => SYSDATE ,
315 p_document_id => CURSOR_PS.DOCUMENT_ID ,
316 p_gl_date => CURSOR_PS.GL_DATE ,
317 p_occurrence_id => l_occurrence_id ,
318 p_bank_occurrence_type => CURSOR_PS.BANK_OCCURRENCE_TYPE ,
319 p_bank_occurrence_code => CURSOR_PS.BANK_OCCURRENCE_CODE ,
320 p_std_occurrence_code => 'REMITTANCE' ,
321 p_bordero_type => var_bordero_type ,
322 p_endorsement_amt => CURSOR_PS.AMOUNT_DUE_REMAINING ,
323 p_bank_charges_amt => CURSOR_PS.BANK_CHARGE_AMOUNT ,
324 p_factoring_charges_amt => NULL ,
325 p_event_id => l_event_id
326 );
327
328 UPDATE JL_BR_AR_OCCURRENCE_DOCS_ALL
329 SET event_id =l_event_id
330 WHERE occurrence_id = l_occurrence_id;
331 -- End SLA Uptake - Bug#4301543
332
333 /* Update collection flag to WRITE_OFF */
334 /* update AR_PAYMENT_SCHEDULES
335 set GLOBAL_ATTRIBUTE11 = 'Y',
336 GLOBAL_ATTRIBUTE9 = 'BANK'
337 where PAYMENT_SCHEDULE_ID = CURSOR_PS.PAYMENT_SCHEDULE_ID;
338 */
339 mo_global.set_policy_context('S',cursor_ps.org_id);
340 begin
341 arp_ps_pkg.fetch_p(CURSOR_PS.PAYMENT_SCHEDULE_ID, l_ps_rec);
342 arp_ps_pkg.lock_p(CURSOR_PS.PAYMENT_SCHEDULE_ID);
343 l_ps_rec.GLOBAL_ATTRIBUTE11 := 'Y';
344 l_ps_rec.GLOBAL_ATTRIBUTE9 := 'BANK';
345 arp_ps_pkg.update_p(l_ps_rec, CURSOR_PS.PAYMENT_SCHEDULE_ID);
346 exception
347 when others then
348 null;
349 end;
350
351 /* Update status to FORMATTED and update bank accounts */
352 update JL_BR_AR_COLLECTION_DOCS_ALL
353 set DOCUMENT_STATUS = 'FORMATTED',
354 CASH_CCID = cursor_ps.CASH_CCID,
355 BANK_CHARGES_CCID = cursor_ps.BANK_CHARGES_CCID,
356 COLL_ENDORSEMENTS_CCID = cursor_ps.COLL_ENDORSEMENT_CCID,
357 BILLS_COLLECTION_CCID = cursor_ps.BILLS_COLLECTION_CCID,
358 CALCULATED_INTEREST_CCID = cursor_ps.CALCULATED_INTEREST_CCID,
359 INTEREST_WRITEOFF_CCID = cursor_ps.INTEREST_WRITEOFF_CCID,
360 ABATEMENT_WRITEOFF_CCID = cursor_ps.ABATEMENT_WRITEOFF_CCID,
361 ABATEMENT_REVENUE_CCID = cursor_ps.ABATEMENT_REVENUE_CCID,
362 INTEREST_REVENUE_CCID = cursor_ps.INTEREST_REVENUE_CCID,
363 CALCULATED_INTEREST_RECTRX_ID = cursor_ps.CALCULATED_INTEREST_RECTRX_ID,
364 INTEREST_WRITEOFF_RECTRX_ID = cursor_ps.INTEREST_WRITEOFF_RECTRX_ID,
365 INTEREST_REVENUE_RECTRX_ID = cursor_ps.INTEREST_REVENUE_RECTRX_ID,
366 ABATEMENT_WRITEOFF_RECTRX_ID = cursor_ps.ABATEMENT_WRITEOFF_RECTRX_ID,
367 ABATE_REVENUE_RECTRX_ID = cursor_ps.ABATEMENT_REVENUE_RECTRX_ID
368 where DOCUMENT_ID = CURSOR_PS.DOCUMENT_ID;
369
370 end loop;
371 close PS;
372 ELSIF var_bordero_type = 'FACTORING' THEN -- Change made for factoring remittance batch
373 open PS;
374 LOOP
375 FETCH PS into CURSOR_PS;
376 exit when PS%notfound;
377
378 Ar_receipt_api_pub.create_cash
379 ( p_api_version => 1.0,
380 p_init_msg_list => FND_API.G_FALSE,
381 p_commit => FND_API.G_FALSE,
382 x_return_status => l_return_status,
383 x_msg_count => l_msg_count,
384 x_msg_data => l_msg_data,
385 p_currency_code => l_currency_code,
386 p_amount => cursor_ps.amount_due_remaining,
387 p_receipt_number => cursor_ps.document_id,
388 p_receipt_date => sysdate,
389 p_gl_date => sysdate,
390 p_customer_id => cursor_ps.customer_id,
391 p_remittance_bank_account_id => cursor_ps.bank_account_id,
392 p_receipt_method_id => cursor_ps.receipt_method_id,
393 p_called_from => 'JLBRRATB',
394 p_cr_id => x_cash_receipt_id);
395
396 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));
397 /* Generate remittance occurrence to the document */
398 LOOP
399
400 l_mesg := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
401 IF l_mesg IS NULL THEN
402 EXIT;
403 ELSE
404 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);
405 END IF;
406
407 END LOOP;
408
409 -- SLA Uptake - Bug#4301543
410 select JL_BR_AR_OCCURRENCE_DOCS_S.NEXTVAL
411 into l_occurrence_id
412 from dual;
413
414
415 INSERT INTO JL_BR_AR_OCCURRENCE_DOCS_ALL
416 (OCCURRENCE_ID,
417 DOCUMENT_ID,
418 BANK_OCCURRENCE_CODE,
419 --BANK_NUMBER,
420 BANK_PARTY_ID,
421 BANK_OCCURRENCE_TYPE,
422 OCCURRENCE_DATE,
423 OCCURRENCE_STATUS,
424 ORIGINAL_REMITTANCE_MEDIA,
425 REMITTANCE_MEDIA,
426 SELECTION_DATE,
427 BORDERO_ID,
428 LAST_UPDATE_DATE,
429 LAST_UPDATED_BY,
430 LAST_UPDATE_LOGIN,
431 CREATION_DATE,
432 CREATED_BY,
433 PORTFOLIO_CODE,
434 TRADE_NOTE_NUMBER,
435 DUE_DATE,
436 DOCUMENT_AMOUNT,
437 BANK_INSTRUCTION_CODE1,
438 BANK_INSTRUCTION_CODE2,
439 NUM_DAYS_INSTRUCTION,
440 INTEREST_PERCENT,
441 INTEREST_PERIOD,
442 INTEREST_AMOUNT,
443 GRACE_DAYS,
444 DISCOUNT_LIMIT_DATE,
445 DISCOUNT_AMOUNT,
446 CUSTOMER_ID,
447 SITE_USE_ID,
448 ABATEMENT_AMOUNT,
449 FLAG_POST_GL,
450 GL_DATE,
451 ENDORSEMENT_CREDIT_CCID,
452 ENDORSEMENT_DEBIT_CCID,
453 ENDORSEMENT_DEBIT_AMOUNT,
454 ENDORSEMENT_CREDIT_AMOUNT,
455 BANK_CHARGES_CREDIT_CCID,
456 BANK_CHARGES_DEBIT_CCID,
457 BANK_CHARGES_DEBIT_AMOUNT,
458 BANK_CHARGES_CREDIT_AMOUNT,
459 FACTOR_INTEREST_CREDIT_CCID,
460 FACTOR_INTEREST_DEBIT_CCID,
461 FACTOR_INTEREST_DEBIT_AMOUNT,
462 FACTOR_INTEREST_CREDIT_AMOUNT,
463 ORG_ID)
464 select
465 l_occurrence_id, -- SLA Uptake - Bug#4301543
466 CURSOR_PS.DOCUMENT_ID,
467 CURSOR_PS.BANK_OCCURRENCE_CODE,
468 --CURSOR_PS.BANK_NUMBER,
469 CURSOR_PS.BANK_PARTY_ID,
470 CURSOR_PS.BANK_OCCURRENCE_TYPE,
471 SYSDATE,
472 'CONFIRMED',
473 CURSOR_PS.OUTPUT_FORMAT,
474 CURSOR_PS.OUTPUT_FORMAT,
475 null,
476 P_BORDERO_ID,
477 SYSDATE,
478 P_USER_ID,
479 P_USER_ID,
480 SYSDATE,
481 P_USER_ID,
482 CURSOR_PS.PORTFOLIO_CODE,
483 CURSOR_PS.GLOBAL_ATTRIBUTE10,
484 CURSOR_PS.DUE_DATE,
485 CURSOR_PS.AMOUNT_DUE_REMAINING,
486 CURSOR_PS.BANK_INSTRUCTION_CODE1,
487 CURSOR_PS.BANK_INSTRUCTION_CODE2,
488 CURSOR_PS.NUM_DAYS_INSTRUCTION,
489 fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), -- Bug 3107496
490 nvl(CT.GLOBAL_ATTRIBUTE3,''),
491 fnd_number.canonical_to_number(nvl(CT.GLOBAL_ATTRIBUTE2,'')), -- Bug 3107496
492 nvl(CT.GLOBAL_ATTRIBUTE5,''),
493 nvl(T.DISCOUNT_DAYS,0) + CURSOR_PS.DUE_DATE,
494 nvl(CURSOR_PS.FACTORING_AMOUNT,0),
495 CT.BILL_TO_CUSTOMER_ID,
496 CT.BILL_TO_SITE_USE_ID,
497 0,
498 'N',
499 CURSOR_PS.GL_DATE,
500 CURSOR_PS.BILLS_DISCOUNT_CCID,
501 CURSOR_PS.DISC_ENDORSEMENT_CCID,
502 CURSOR_PS.AMOUNT_DUE_REMAINING,
503 CURSOR_PS.AMOUNT_DUE_REMAINING,
504 decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'', CURSOR_PS.CASH_CCID),
505 decode(CURSOR_PS.BANK_CHARGE_AMOUNT,'','',0,'',CURSOR_PS.BANK_CHARGES_CCID),
506 CURSOR_PS.BANK_CHARGE_AMOUNT,
507 CURSOR_PS.BANK_CHARGE_AMOUNT,
508 decode(CURSOR_PS.FACTORING_AMOUNT,'','',0,'', CURSOR_PS.CASH_CCID),
509 decode(CURSOR_PS.FACTORING_AMOUNT,'','',0,'',CURSOR_PS.FACTORING_INTEREST_CCID),
510 CURSOR_PS.FACTORING_AMOUNT,
511 CURSOR_PS.FACTORING_AMOUNT,
512 CURSOR_PS.ORG_ID
513 from RA_CUSTOMER_TRX CT, RA_TERMS_LINES_DISCOUNTS T,
514 AR_PAYMENT_SCHEDULES PS
515 where CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
516 and T.TERM_ID(+) = PS.TERM_ID
517 and T.SEQUENCE_NUM(+) = PS.TERMS_SEQUENCE_NUMBER
518 and PS.PAYMENT_SCHEDULE_ID = CURSOR_PS.PAYMENT_SCHEDULE_ID;
519
520 -- SLA Uptake - Bug#4301543
521 JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists (
522 p_event_type_code => 'REMIT_FACT_DOC' ,
523 p_event_date => SYSDATE ,
524 p_document_id => CURSOR_PS.DOCUMENT_ID ,
525 p_gl_date => CURSOR_PS.GL_DATE ,
526 p_occurrence_id => l_occurrence_id ,
527 p_bank_occurrence_type => CURSOR_PS.BANK_OCCURRENCE_TYPE ,
528 p_bank_occurrence_code => CURSOR_PS.BANK_OCCURRENCE_CODE ,
529 p_std_occurrence_code => 'REMITTANCE' ,
530 p_bordero_type => var_bordero_type ,
531 p_endorsement_amt => CURSOR_PS.AMOUNT_DUE_REMAINING ,
532 p_bank_charges_amt => CURSOR_PS.BANK_CHARGE_AMOUNT ,
533 p_factoring_charges_amt => CURSOR_PS.FACTORING_AMOUNT ,
534 p_event_id => l_event_id
535 );
536
537 UPDATE JL_BR_AR_OCCURRENCE_DOCS_ALL
538 SET event_id =l_event_id
539 WHERE occurrence_id = l_occurrence_id;
540 -- End SLA Uptake - Bug#4301543
541
542 arp_ps_pkg.fetch_p(CURSOR_PS.PAYMENT_SCHEDULE_ID, l_ps_rec);
543 arp_ps_pkg.lock_p(CURSOR_PS.PAYMENT_SCHEDULE_ID);
544 l_ps_rec.GLOBAL_ATTRIBUTE11 := 'Y';
545 l_ps_rec.GLOBAL_ATTRIBUTE9 := 'BANK';
546 arp_ps_pkg.update_p(l_ps_rec, CURSOR_PS.PAYMENT_SCHEDULE_ID);
547
548 /* Update status to FORMATTED and update bank accounts */
549 update JL_BR_AR_COLLECTION_DOCS_ALL
550 set DOCUMENT_STATUS = 'FORMATTED',
551 CASH_CCID = cursor_ps.CASH_CCID,
552 BANK_CHARGES_CCID = cursor_ps.BANK_CHARGES_CCID,
553 COLL_ENDORSEMENTS_CCID = cursor_ps.DISC_ENDORSEMENT_CCID,
554 BILLS_COLLECTION_CCID = cursor_ps.BILLS_DISCOUNT_CCID,
555 CALCULATED_INTEREST_CCID = cursor_ps.CALCULATED_INTEREST_CCID,
556 INTEREST_WRITEOFF_CCID = cursor_ps.INTEREST_WRITEOFF_CCID,
557 ABATEMENT_WRITEOFF_CCID = cursor_ps.ABATEMENT_WRITEOFF_CCID,
558 ABATEMENT_REVENUE_CCID = cursor_ps.ABATEMENT_REVENUE_CCID,
559 INTEREST_REVENUE_CCID = cursor_ps.INTEREST_REVENUE_CCID,
560 CALCULATED_INTEREST_RECTRX_ID = cursor_ps.CALCULATED_INTEREST_RECTRX_ID,
561 INTEREST_WRITEOFF_RECTRX_ID = cursor_ps.INTEREST_WRITEOFF_RECTRX_ID, INTEREST_REVENUE_RECTRX_ID = cursor_ps.INTEREST_REVENUE_RECTRX_ID,
562 ABATEMENT_WRITEOFF_RECTRX_ID = cursor_ps.ABATEMENT_WRITEOFF_RECTRX_ID,
563 ABATE_REVENUE_RECTRX_ID = cursor_ps.ABATEMENT_REVENUE_RECTRX_ID,
564 CASH_RECEIPT_ID = x_cash_receipt_id
565 where DOCUMENT_ID = CURSOR_PS.DOCUMENT_ID;
566
567 END LOOP;
568 Close PS;
569 END IF;
570
571 select CS.SELECTION_STATUS,
572 B.SELECTION_CONTROL_ID,
573 B.SELECT_ACCOUNT_ID
574 into PL_CS_SELECTION_STATUS,
575 PL_SELECTION_CONTROL_ID,
576 PL_SELECT_ACCOUNT_ID
577 from JL_BR_AR_SELECT_CONTROLS_ALL CS,
578 JL_BR_AR_BORDEROS B
579 where CS.SELECTION_CONTROL_ID = B.SELECTION_CONTROL_ID
580 and B.BORDERO_ID = P_BORDERO_ID;
581
582 if PL_CS_SELECTION_STATUS = 'SELECTED'
583 then
584 /* Update status to FORMATTED */
585 update JL_BR_AR_SELECT_CONTROLS
586 set SELECTION_STATUS = 'FORMATTED',
587 REMITTANCE_DATE = SYSDATE
588 where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
589 update JL_BR_AR_SELECT_ACCOUNTS
590 set FORMAT_DATE= SYSDATE,
591 REMITTANCE_DATE = SYSDATE
592 where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
593 else
594 /* Update remittance date */
595 update JL_BR_AR_SELECT_CONTROLS
596 set REMITTANCE_DATE = SYSDATE
597 where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
598 update JL_BR_AR_SELECT_ACCOUNTS
599 set REMITTANCE_DATE = SYSDATE
600 where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
601 end if;
602
603 /* Update status to FORMATTED */
604 update JL_BR_AR_BORDEROS
605 set BORDERO_STATUS = 'FORMATTED',
606 REMITTANCE_DATE = SYSDATE
607 where BORDERO_ID = P_BORDERO_ID;
608
609 BEGIN
610 insert into JL_BR_AR_REMIT_BORDEROS_ALL
611 (FORMAT_REQUEST_ID,
612 LAST_UPDATE_DATE,
613 LAST_UPDATED_BY,
614 LAST_UPDATE_LOGIN,
615 CREATION_DATE,
616 CREATED_BY,
617 BORDERO_ID,
618 OUTPUT_PROGRAM_ID,
619 ORG_ID)
620 select
621 fnd_global.conc_request_id,
622 sysdate,
623 fnd_global.user_id,
624 fnd_global.login_id,
625 sysdate,
626 fnd_global.user_id,
627 bordero_id,
628 output_program_id,
629 org_id
630 from jl_br_ar_borderos_all
631 where bordero_id = P_BORDERO_ID;
632 EXCEPTION
633 WHEN OTHERS THEN
634 null;
635 end;
636
637
638 P_PROC_STATUS := 1;
639 END LOOP;
640 CLOSE check2;
641
642 END LOOP;
643 CLOSE check1;
644 COMMIT;
645
646 END remit_collection;
647
648
649 PROCEDURE remit_occurrence (P_BORDERO_ID IN NUMBER,
650 P_PROC_STATUS IN OUT NOCOPY NUMBER) is
651 PL_SELECTION_CONTROL_ID number;
652 PL_SELECT_ACCOUNT_ID number;
653 PL_CS_SELECTION_STATUS varchar2(30);
654 var_bordero_status_chk VARCHAR2(30);
655 var_selection_control NUMBER;
656 var_selection_control_chk NUMBER;
657
658 CURSOR check1 IS
659 SELECT bordero_status, selection_control_id
660 FROM jl_br_ar_borderos_all
661 WHERE bordero_id = P_BORDERO_ID
662 AND bordero_status in ('SELECTED', 'FORMATTED')
663 FOR UPDATE NOWAIT;
664
665 CURSOR check2 IS
666 SELECT selection_control_id
667 FROM jl_br_ar_select_controls_all
668 WHERE selection_control_id = var_selection_control
669 FOR UPDATE;
670
671 /* This cursor is used to read documents from Borderos that
672 was not formated */
673 cursor OC is
674 select OD.OCCURRENCE_ID, BO.STD_OCCURRENCE_CODE, OD.DOCUMENT_ID
675 from JL_BR_AR_OCCURRENCE_DOCS_ALL OD,
676 JL_BR_AR_BORDEROS B,
677 JL_BR_AR_BANK_OCCURRENCES BO
678 where OD.OCCURRENCE_STATUS = 'SELECTED'
679 and B.BORDERO_STATUS = 'SELECTED'
680 and B.BORDERO_ID = OD.BORDERO_ID
681 and OD.BORDERO_ID = P_BORDERO_ID
682 and BO.BANK_OCCURRENCE_CODE = OD.BANK_OCCURRENCE_CODE
683 --and BO.BANK_NUMBER = OD.BANK_NUMBER;
684 and BO.BANK_PARTY_ID = OD.BANK_PARTY_ID;
685
686 l_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
687 l_ps_rec ar_payment_schedules%ROWTYPE;
688 begin
689 P_PROC_STATUS := 0;
690 OPEN check1;
691 LOOP
692 FETCH check1 INTO var_bordero_status_chk, var_selection_control;
693 EXIT WHEN check1%NOTFOUND;
694
695 OPEN check2;
696 LOOP
697 FETCH check2 INTO var_selection_control_chk;
698 EXIT WHEN check2%NOTFOUND;
699
700 for TMP in OC loop
701 /* Update occurrence status to CONFIRMED */
702 update JL_BR_AR_OCCURRENCE_DOCS_ALL
703 set OCCURRENCE_STATUS = 'CONFIRMED'
704 where OCCURRENCE_ID = TMP.OCCURRENCE_ID;
705 if TMP.STD_OCCURRENCE_CODE = 'PROTEST' then
706 /* update AR_PAYMENT_SCHEDULES
707 set GLOBAL_ATTRIBUTE9 = 'REGISTRY'
708 where PAYMENT_SCHEDULE_ID = (select PAYMENT_SCHEDULE_ID
709 from JL_BR_AR_COLLECTION_DOCS
710 where DOCUMENT_ID = TMP.DOCUMENT_ID);
711 */
712
713 /* Replace Update by AR's Table Handlers. Bug # 2249731 */
714 SELECT payment_schedule_id
715 INTO l_ps_id
716 FROM jl_br_ar_collection_docs
717 WHERE document_id = TMP.DOCUMENT_ID;
718
719 arp_ps_pkg.fetch_p(l_ps_id, l_ps_rec);
720 arp_ps_pkg.lock_p(l_ps_id);
721 l_ps_rec.GLOBAL_ATTRIBUTE9 := 'REGISTRY';
722 arp_ps_pkg.update_p(l_ps_rec, l_ps_id);
723
724 /* Following elsif added for Bug 865082 */
725 elsif TMP.STD_OCCURRENCE_CODE = 'WRITE_OFF_REQUISITION' then
726 /* UPDATE ar_payment_schedules
727 SET selected_for_receipt_batch_id = NULL,
728 global_attribute9 = 'MANUAL_RECEIPT',
729 global_attribute11 = 'N'
730 where PAYMENT_SCHEDULE_ID = (select PAYMENT_SCHEDULE_ID
731 from JL_BR_AR_COLLECTION_DOCS
732 where DOCUMENT_ID = TMP.DOCUMENT_ID);
733 */
734
735 /* Replace Update by AR's Table Handlers. Bug # 2249731 */
736
737 SELECT payment_schedule_id
738 INTO l_ps_id
739 FROM jl_br_ar_collection_docs
740 WHERE document_id = TMP.DOCUMENT_ID;
741
742 arp_ps_pkg.fetch_p(l_ps_id, l_ps_rec);
743 arp_ps_pkg.lock_p(l_ps_id);
744 l_ps_rec.selected_for_receipt_batch_id := NULL;
745 l_ps_rec.GLOBAL_ATTRIBUTE9 := 'MANUAL_RECEIPT';
746 l_ps_rec.GLOBAL_ATTRIBUTE11 := 'N';
747 arp_ps_pkg.update_p(l_ps_rec, l_ps_id);
748
749 end if;
750 end loop;
751 select B.SELECTION_CONTROL_ID,
752 B.SELECT_ACCOUNT_ID,
753 CS.SELECTION_STATUS
754 into PL_SELECTION_CONTROL_ID,
755 PL_SELECT_ACCOUNT_ID,
756 PL_CS_SELECTION_STATUS
757 from JL_BR_AR_BORDEROS_ALL B, JL_BR_AR_SELECT_CONTROLS_ALL CS
758 where B.BORDERO_ID = P_BORDERO_ID
759 and CS.SELECTION_CONTROL_ID = B.SELECTION_CONTROL_ID;
760 /* Update bordero status to FORMATTED */
761 update JL_BR_AR_BORDEROS_ALL
762 set BORDERO_STATUS = 'FORMATTED',
763 REMITTANCE_DATE = SYSDATE
764 where BORDERO_ID = P_BORDERO_ID;
765
766 if PL_CS_SELECTION_STATUS = 'SELECTED'
767 then
768 /* Update selection status to FORMATTED */
769 update JL_BR_AR_SELECT_CONTROLS_ALL
770 set SELECTION_STATUS = 'FORMATTED',
771 GENERATION_DATE = SYSDATE,
772 REMITTANCE_DATE = SYSDATE
773 where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
774 update JL_BR_AR_SELECT_ACCOUNTS
775 set FORMAT_DATE = SYSDATE,
776 REMITTANCE_DATE = SYSDATE
777 where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
778 else
779 /* Update remittance date */
780 update JL_BR_AR_SELECT_CONTROLS_ALL
781 set REMITTANCE_DATE = SYSDATE
782 where SELECTION_CONTROL_ID = PL_SELECTION_CONTROL_ID;
783 update JL_BR_AR_SELECT_ACCOUNTS
784 set REMITTANCE_DATE = SYSDATE
785 where SELECT_ACCOUNT_ID = PL_SELECT_ACCOUNT_ID;
786 end if;
787
788 insert into JL_BR_AR_REMIT_BORDEROS_ALL
789 (FORMAT_REQUEST_ID,
790 LAST_UPDATE_DATE,
791 LAST_UPDATED_BY,
792 LAST_UPDATE_LOGIN,
793 CREATION_DATE,
794 CREATED_BY,
795 BORDERO_ID,
796 OUTPUT_PROGRAM_ID,
797 ORG_ID)
798 select
799 fnd_global.conc_request_id,
800 sysdate,
801 fnd_global.user_id,
802 fnd_global.login_id,
803 sysdate,
804 fnd_global.user_id,
805 bordero_id,
806 output_program_id,
807 mo_global.get_current_org_id
808 from jl_br_ar_borderos
809 where bordero_id = P_BORDERO_ID;
810
811
812 P_PROC_STATUS := 1;
813 END LOOP;
814 CLOSE check2;
815
816 END LOOP;
817 CLOSE check1;
818 COMMIT;
819
820 END remit_occurrence;
821
822 /*===========================================================================+
823 | FUNCTION |
824 | get_acct_line_type_name |
825 | |
826 | DESCRIPTION |
827 | This function is required to be called in occurrence view, where it |
828 | passes the meaning of the lookup code which is passed as the parameter, |
829 | to the view column ACCT_LINE_TYPE_NAME which is required to be shown |
830 | in SLA forms to name the account line. |
831 | |
832 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
833 | none |
834 | |
835 | ARGUMENTS |
836 | none |
837 | |
838 | USAGE NOTES: |
839 | Begin |
840 | x := JL_BR_AR_REMIT_COLL_OCCUR.get_acct_line_type_name; |
841 | End; |
842 | |
843 | MODIFICATION HISTORY |
844 | 19-Apr-00 Santosh Vaze Created |
845 | |
846 +===========================================================================*/
847
848 FUNCTION get_acct_line_type_name(code VARCHAR2) RETURN VARCHAR2 IS
849 name VARCHAR2(100);
850 BEGIN
851
852 BEGIN
853 SELECT meaning
854 INTO name
855 FROM fnd_lookups
856 WHERE lookup_code = code
857 AND lookup_type = 'JLBR_AR_SLA_ACCT_LINE_TYPE';
858 EXCEPTION
859 WHEN OTHERS THEN
860 name := NULL;
861 END;
862 RETURN ( name );
863 END get_acct_line_type_name;
864
865 /*===========================================================================+
866 | FUNCTION |
867 | get_trx_class_name |
868 | |
869 | DESCRIPTION |
870 | This function is required to be called in bank transfer view, where it |
871 | passes the meaning of the lookup code to the view column TRX_CLASS_NAME |
872 | which is required to be shown in SLA forms to name the transaction class|
873 | |
874 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
875 | none |
876 | |
877 | ARGUMENTS |
878 | none |
879 | |
880 | USAGE NOTES: |
881 | Begin |
882 | x := JL_BR_AR_REMIT_COLL_OCCUR.get_trx_class_name; |
883 | End; |
884 | |
885 | MODIFICATION HISTORY |
886 | 19-Apr-00 Santosh Vaze Created |
887 | |
888 +===========================================================================*/
889
890 FUNCTION get_trx_class_name(trx_class VARCHAR2) RETURN VARCHAR2 IS
891 name VARCHAR2(100);
892 BEGIN
893
894 BEGIN
895 SELECT meaning
896 INTO name
897 FROM fnd_lookups
898 WHERE lookup_code = trx_class
899 AND lookup_type = 'JLBR_AR_SLA_TRX_CLASS';
900 EXCEPTION
901 WHEN OTHERS THEN
902 name := NULL;
903 END;
904 RETURN ( name );
905 END get_trx_class_name;
906
907 END JL_BR_AR_REMIT_COLL_OCCUR;