[Home] [Help]
PACKAGE BODY: APPS.AR_LATE_CHARGE_PKG
Source
1 PACKAGE BODY ar_late_charge_pkg AS
2 /* $Header: ARLCDOCB.pls 120.25.12020000.3 2012/11/24 17:17:58 naneja ship $ */
3
4 g_bulk_fetch_rows NUMBER := 10000;
5 g_func_curr VARCHAR2(15);
6 g_interest_batch_id NUMBER;
7 g_object_version_number NUMBER;
8 g_org_id NUMBER;
9 g_BATCH_SOURCE_ID NUMBER;
10 g_int_cal_date DATE;
11
12 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
13
14 -----------
15 -- Invoice:
16 -----------
17 iv_INTEREST_HEADER_ID DBMS_SQL.NUMBER_TABLE;
18 iv_CURRENCY_CODE DBMS_SQL.VARCHAR2_TABLE;
19 iv_HEADER_TYPE DBMS_SQL.VARCHAR2_TABLE;
20 iv_cust_trx_type_id DBMS_SQL.NUMBER_TABLE;
21 iv_CUSTOMER_ID DBMS_SQL.NUMBER_TABLE;
22 iv_CUSTOMER_SITE_USE_ID DBMS_SQL.NUMBER_TABLE;
23 iv_LATE_CHARGE_TERM_ID DBMS_SQL.NUMBER_TABLE;
24 iv_EXCHANGE_RATE_TYPE DBMS_SQL.VARCHAR2_TABLE;
25 iv_EXCHANGE_RATE DBMS_SQL.NUMBER_TABLE;
26 iv_PAYMENT_SCHEDULE_ID DBMS_SQL.NUMBER_TABLE;
27 iv_org_id DBMS_SQL.NUMBER_TABLE;
28 iv_legal_entity_id DBMS_SQL.NUMBER_TABLE;
29 iv_interest_line_id DBMS_SQL.NUMBER_TABLE;
30 iv_LATE_CHARGE_CALCULATION_TRX DBMS_SQL.VARCHAR2_TABLE;
31 iv_DAYS_OF_INTEREST DBMS_SQL.NUMBER_TABLE;
32 iv_DAYS_OVERDUE_LATE DBMS_SQL.NUMBER_TABLE;
33 iv_DAILY_INTEREST_CHARGE DBMS_SQL.NUMBER_TABLE;
34 iv_INTEREST_CHARGED DBMS_SQL.NUMBER_TABLE;
35 iv_type DBMS_SQL.VARCHAR2_TABLE;
36 iv_salesrep_required_flag DBMS_SQL.VARCHAR2_TABLE;
37 iv_salesrep_id DBMS_SQL.NUMBER_TABLE;
38 iv_salesrep_number DBMS_SQL.VARCHAR2_TABLE;
39 iv_GL_ID_REC DBMS_SQL.NUMBER_TABLE;
40 iv_GL_ID_REV DBMS_SQL.NUMBER_TABLE;
41 iv_cpt NUMBER := 0;
42 iv_salesrep_set VARCHAR2(1) := 'N';
43 iv_sales_credit_name VARCHAR2(200);
44 iv_sales_credit_id NUMBER;
45 iv_original_trx_id DBMS_SQL.NUMBER_TABLE;
46 iv_original_trx_class DBMS_SQL.VARCHAR2_TABLE;
47 iv_DUE_DATE DBMS_SQL.DATE_TABLE;
48 iv_OUTSTANDING_AMOUNT DBMS_SQL.NUMBER_TABLE;
49 iv_PAYMENT_DATE DBMS_SQL.DATE_TABLE;
50 iv_LAST_CHARGE_DATE DBMS_SQL.DATE_TABLE;
51 iv_INTEREST_RATE DBMS_SQL.NUMBER_TABLE;
52
53 /*10297326 declare for contact*/
54 iv_CONTACT_ID DBMS_SQL.NUMBER_TABLE;
55 iv_CUST_ACCT_SITE_ID DBMS_SQL.NUMBER_TABLE;
56 --------
57 -- invoice api
58 --------
59 iv_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
60 iv_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
61 iv_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
62 iv_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
63 iv_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
64 iv_create_flag VARCHAR2(1) := 'N';
65 iv_header_cpt NUMBER := 0;
66 iv_line_cpt NUMBER := 0;
67 iv_dist_cpt NUMBER := 0;
68 iv_salescredits_cpt NUMBER := 0;
69 iv_curr_header_id NUMBER := 0;
70 iv_line_num NUMBER := 0;
71
72
73
74 -------------
75 -- Recycle
76 -------------
77 nl_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
78 nl_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
79 nl_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
80 nl_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
81 nl_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
82 nl_INTEREST_HEADER_ID DBMS_SQL.NUMBER_TABLE;
83 nl_CURRENCY_CODE DBMS_SQL.VARCHAR2_TABLE;
84 nl_HEADER_TYPE DBMS_SQL.VARCHAR2_TABLE;
85 nl_cust_trx_type_id DBMS_SQL.NUMBER_TABLE;
86 nl_CUSTOMER_ID DBMS_SQL.NUMBER_TABLE;
87 nl_CUSTOMER_SITE_USE_ID DBMS_SQL.NUMBER_TABLE;
88 nl_LATE_CHARGE_TERM_ID DBMS_SQL.NUMBER_TABLE;
89 nl_EXCHANGE_RATE_TYPE DBMS_SQL.VARCHAR2_TABLE;
90 nl_EXCHANGE_RATE DBMS_SQL.NUMBER_TABLE;
91 nl_PAYMENT_SCHEDULE_ID DBMS_SQL.NUMBER_TABLE;
92 nl_org_id DBMS_SQL.NUMBER_TABLE;
93 nl_legal_entity_id DBMS_SQL.NUMBER_TABLE;
94 nl_interest_line_id DBMS_SQL.NUMBER_TABLE;
95 nl_LATE_CHARGE_CALCULATION_TRX DBMS_SQL.VARCHAR2_TABLE;
96 nl_DAYS_OF_INTEREST DBMS_SQL.NUMBER_TABLE;
97 nl_DAYS_OVERDUE_LATE DBMS_SQL.NUMBER_TABLE;
98 nl_DAILY_INTEREST_CHARGE DBMS_SQL.NUMBER_TABLE;
99 nl_INTEREST_CHARGED DBMS_SQL.NUMBER_TABLE;
100 nl_type DBMS_SQL.VARCHAR2_TABLE;
101 nl_salesrep_required_flag DBMS_SQL.VARCHAR2_TABLE;
102 nl_salesrep_id DBMS_SQL.NUMBER_TABLE;
103 nl_salesrep_number DBMS_SQL.VARCHAR2_TABLE;
104 nl_GL_ID_REC DBMS_SQL.NUMBER_TABLE;
105 nl_GL_ID_REV DBMS_SQL.NUMBER_TABLE;
106 nl_original_trx_id DBMS_SQL.NUMBER_TABLE;
107 nl_original_trx_class DBMS_SQL.VARCHAR2_TABLE;
108 nl_DUE_DATE DBMS_SQL.DATE_TABLE;
109 nl_OUTSTANDING_AMOUNT DBMS_SQL.NUMBER_TABLE;
110 nl_PAYMENT_DATE DBMS_SQL.DATE_TABLE;
111 nl_LAST_CHARGE_DATE DBMS_SQL.DATE_TABLE;
112 nl_INTEREST_RATE DBMS_SQL.NUMBER_TABLE;
113
114
115
116 PROCEDURE log(
117 message IN VARCHAR2,
118 newline IN BOOLEAN DEFAULT TRUE) IS
119 BEGIN
120 IF message = 'NEWLINE' THEN
121 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
122 ELSIF (newline) THEN
123 FND_FILE.put_line(fnd_file.log,message);
124 ELSE
125 FND_FILE.put(fnd_file.log,message);
126 END IF;
127 IF PG_DEBUG = 'Y' THEN
128 ARP_STANDARD.DEBUG(message);
129 END IF;
130 END log;
131
132
133
134 PROCEDURE out(
135 message IN VARCHAR2,
136 newline IN BOOLEAN DEFAULT TRUE) IS
137 BEGIN
138 IF message = 'NEWLINE' THEN
139 FND_FILE.NEW_LINE(FND_FILE.output, 1);
140 ELSIF (newline) THEN
141 FND_FILE.put_line(fnd_file.output,message);
142 ELSE
143 FND_FILE.put(fnd_file.output,message);
144 END IF;
145 END out;
146
147
148
149 PROCEDURE outandlog(
150 message IN VARCHAR2,
151 newline IN BOOLEAN DEFAULT TRUE) IS
152 BEGIN
153 out(message, newline);
154 log(message, newline);
155 END outandlog;
156
157
158
159
160 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
161 RETURN VARCHAR2 IS
162 l_msg_data VARCHAR2(2000);
163 BEGIN
164 FND_MSG_PUB.Reset;
165
166 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
167 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
168 END LOOP;
169 IF (SQLERRM IS NOT NULL) THEN
170 l_msg_data := l_msg_data || SQLERRM;
171 END IF;
172 log(l_msg_data);
173 RETURN l_msg_data;
174 END logerror;
175
176
177
178
179
180
181 FUNCTION get_lookup_desc (p_lookup_type IN VARCHAR2,
182 p_lookup_code IN VARCHAR2)
183 RETURN VARCHAR2 IS
184 l_description VARCHAR2(240);
185 l_hash_value NUMBER;
186 BEGIN
187 IF p_lookup_code IS NOT NULL AND
188 p_lookup_type IS NOT NULL THEN
189
190 l_hash_value := DBMS_UTILITY.get_hash_value(
191 p_lookup_type||'@*?'||p_lookup_code,
192 1000,
193 25000);
194
195 IF pg_ar_lookups_desc_rec.EXISTS(l_hash_value) THEN
196 l_description := pg_ar_lookups_desc_rec(l_hash_value);
197 ELSE
198
199 SELECT description
200 INTO l_description
201 FROM ar_lookups
202 WHERE lookup_type = p_lookup_type
203 AND lookup_code = p_lookup_code ;
204
205 pg_ar_lookups_desc_rec(l_hash_value) := l_description;
206
207 END IF;
208
209 END IF;
210
211 return(l_description);
212
213 EXCEPTION
214 WHEN no_data_found THEN
215 return( p_lookup_code);
216 WHEN OTHERS THEN
217 raise;
218 END;
219
220
221
222
223 FUNCTION phrase
224 (p_type IN VARCHAR2,
225 p_class IN VARCHAR2,
226 p_trx_number IN VARCHAR2,
227 p_receipt_number IN VARCHAR2,
228 p_due_date IN DATE,
229 p_outstanding_amt IN NUMBER,
230 p_payment_date IN DATE,
231 p_days_overdue_late IN NUMBER,
232 p_last_charge_date IN DATE,
233 p_interest_rate IN NUMBER,
234 p_calculate_interest_to_date IN DATE)
235 RETURN VARCHAR2
236 IS
237 l_doc_num VARCHAR2(30);
238 l_text VARCHAR2(240);
239 BEGIN
240 --log( message => 'Phrase +');
241 IF p_class = 'PMT' THEN
242 l_doc_num := p_receipt_number;
243 ELSE
244 l_doc_num := p_trx_number;
245 END IF;
246 /*Bug 7441039 used correct lookup type for Late charge type and late charge line type
247 Wrong name were used earlier.*/
248 IF p_type = 'LATE' THEN
249 l_text := SUBSTRB(
250 get_lookup_desc('AR_LATE_CHARGE_TYPE_DESCR' ,'LATE') ||' '||l_doc_num ||';'||
251 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DUEDATE') ||' '||p_due_date ||';'||
252 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','LATEPAYAMT')||' '||p_outstanding_amt ||';'||
253 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','PAYDATE') ||' '||p_payment_date ||';'||
254 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DAYSLATE') ||' '||p_days_overdue_late||';'||
255 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','INTRATE') ||' '||p_interest_rate ||'%',1,240);
256 ELSIF p_type = 'OVERDUE' THEN
257 IF p_last_charge_date IS NULL THEN
258 l_text := SUBSTRB(
259 get_lookup_desc('AR_LATE_CHARGE_TYPE_DESCR' ,'OVERDUE') ||' '||l_doc_num ||';'||
260 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DUEDATE') ||' '||p_due_date ||';'||
261 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','OVERDUEAMT') ||' '||p_outstanding_amt ||';'||
262 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','CALCINTTODATE')||' '||p_calculate_interest_to_date||';'||
263 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DAYSOVERDUE') ||' '||p_days_overdue_late||';'||
264 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','INTRATE') ||' '||p_interest_rate ||'%',1,240);
265 ELSE
266 l_text := SUBSTRB(
267 get_lookup_desc('AR_LATE_CHARGE_TYPE_DESCR' ,'OVERDUE') ||' '||l_doc_num ||';'||
268 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DUEDATE') ||' '||p_due_date ||';'||
269 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','OVERDUEAMT') ||' '||p_outstanding_amt ||';'||
270 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','CALCINTTODATE') ||' '||p_calculate_interest_to_date||';'||
271 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','LASTCHARGEDATE')||' '||p_last_charge_date ||';'||
272 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DAYSOVERDUE') ||' '||p_days_overdue_late||';'||
273 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','INTRATE') ||' '||p_interest_rate ||'%',1,240);
274 END IF;
275 ELSIF p_type = 'CREDIT' THEN
276 IF p_last_charge_date IS NULL THEN
277 l_text := SUBSTRB(
278 get_lookup_desc('AR_LATE_CHARGE_TYPE_DESCR' ,'CREDIT') ||' '||l_doc_num ||';'||
279 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DUEDATE') ||' '||p_due_date ||';'||
280 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','OVERDUEAMT') ||' '||p_outstanding_amt ||';'||
281 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','CALCINTTODATE')||' '||p_calculate_interest_to_date ||';'||
282 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DAYSOVERDUE') ||' '||p_days_overdue_late ||';'||
283 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','INTRATE') ||' '||p_interest_rate ||'%' ,1,240);
284 ELSE
285 l_text := SUBSTRB(
286 get_lookup_desc('AR_LATE_CHARGE_TYPE_DESCR' ,'CREDIT') ||' '||l_doc_num ||';'||
287 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DUEDATE') ||' '||p_due_date ||';'||
288 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','OVERDUEAMT') ||' '||p_outstanding_amt ||';'||
289 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','CALCINTTODATE') ||' '||p_calculate_interest_to_date||';'||
290 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','LASTCHARGEDATE')||' '||p_last_charge_date ||';'||
291 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','DAYSOVERDUE') ||' '||p_days_overdue_late ||';'||
292 get_lookup_desc('AR_LATE_CHARGE_LINE_DESCR','INTRATE') ||' '||p_interest_rate ||'%',1,240);
293 END IF;
294 ELSIF p_type = 'AVERAGE_DAILY_BALANCE' THEN
295 l_text := get_lookup_desc('AR_LATE_CHARGE_TYPE_DESCR' ,'AVERAGE_DAILY_BALANCE');
296 ELSIF p_type = 'PENALTY' THEN
297 l_text := get_lookup_desc('AR_LATE_CHARGE_TYPE_DESCR' ,'PENALTY');
298 ELSE
299 l_text := get_lookup_desc('AR_LATE_CHARGE_TYPE_DESCR' ,'DESC_NOT_SET');
300 END IF;
301 --log( message => 'Phrase -');
302 RETURN (l_text);
303 END;
304
305
306
307
308
309
310
311 PROCEDURE INSERT_HDR IS
312 i NUMBER;
313 BEGIN
314 log( message => 'INSERT_HEADER +');
315 FOR i IN iv_trx_header_tbl.FIRST .. iv_trx_header_tbl.LAST LOOP
316 INSERT INTO ar_inv_api_headers_gt (
317 trx_header_id ,
318 trx_date ,
319 trx_currency ,
320 trx_class ,
321 cust_trx_type_id ,
322 gl_date ,
323 bill_to_customer_id ,
324 bill_to_site_use_id ,
325 term_id ,
326 exchange_rate_type ,
327 exchange_date ,
328 exchange_rate ,
329 comments )
330 VALUES
331 (iv_trx_header_tbl(i).trx_header_id,
332 iv_trx_header_tbl(i).trx_date ,
333 iv_trx_header_tbl(i).trx_currency ,
334 iv_trx_header_tbl(i).trx_class ,
335 iv_trx_header_tbl(i).cust_trx_type_id,
336 iv_trx_header_tbl(i).gl_date ,
337 iv_trx_header_tbl(i).bill_to_customer_id,
338 iv_trx_header_tbl(i).bill_to_site_use_id,
339 iv_trx_header_tbl(i).term_id ,
340 iv_trx_header_tbl(i).exchange_rate_type,
341 iv_trx_header_tbl(i).exchange_date,
342 iv_trx_header_tbl(i).exchange_rate,
343 iv_trx_header_tbl(i).comments);
344 END LOOP;
345 log( message => 'INSERT_HEADER -');
346 END;
347
348
349 PROCEDURE INSERT_LINE IS
350 i NUMBER;
351 BEGIN
352 log( message => 'INSERT_LINE +');
353 FOR i IN iv_trx_lines_tbl.FIRST..iv_trx_lines_tbl.LAST LOOP
354 INSERT INTO ar_inv_api_lines_gt(
355 trx_header_id ,
356 trx_line_id ,
357 LINE_NUMBER ,
358 DESCRIPTION ,
359 QUANTITY_ORDERED ,
360 QUANTITY_INVOICED ,
361 UNIT_STANDARD_PRICE ,
362 UNIT_SELLING_PRICE ,
363 LINE_TYPE ,
364 AMOUNT )
365 VALUES
366 (iv_trx_lines_tbl(i).trx_header_id,
367 iv_trx_lines_tbl(i).trx_line_id ,
368 iv_trx_lines_tbl(i).LINE_NUMBER ,
369 iv_trx_lines_tbl(i).DESCRIPTION ,
370 iv_trx_lines_tbl(i).QUANTITY_ORDERED,
371 iv_trx_lines_tbl(i).QUANTITY_INVOICED,
372 iv_trx_lines_tbl(i).UNIT_STANDARD_PRICE,
373 iv_trx_lines_tbl(i).UNIT_SELLING_PRICE,
374 iv_trx_lines_tbl(i).LINE_TYPE ,
375 iv_trx_lines_tbl(i).AMOUNT);
376 END LOOP;
377 log( message => 'INSERT_LINE -');
378 END;
379
380
381
382 PROCEDURE INSERT_DIST IS
383 I NUMBER;
384 BEGIN
385 log( message => 'INSERT_DIST +');
386 FOR i IN iv_trx_dist_tbl.FIRST..iv_trx_dist_tbl.LAST LOOP
387 INSERT INTO ar_inv_api_dist_gt(
388 trx_dist_id ,
389 trx_header_id ,
390 trx_LINE_ID ,
391 ACCOUNT_CLASS ,
392 PERCENT ,
393 CODE_COMBINATION_ID )
394 VALUES (
395 iv_trx_dist_tbl(i).trx_dist_id,
396 iv_trx_dist_tbl(i).trx_header_id,
397 iv_trx_dist_tbl(i).trx_LINE_ID ,
398 iv_trx_dist_tbl(i).ACCOUNT_CLASS,
399 iv_trx_dist_tbl(i).PERCENT ,
400 iv_trx_dist_tbl(i).CODE_COMBINATION_ID);
401 END LOOP;
402 log( message => 'INSERT_DIST -');
403 END;
404
405
406
407
408
409 PROCEDURE empty_var_iv IS
410 BEGIN
411 log( message => 'empty_var_iv +');
412 iv_INTEREST_HEADER_ID := nl_INTEREST_HEADER_ID;
413 iv_CURRENCY_CODE := nl_CURRENCY_CODE;
414 iv_HEADER_TYPE := nl_HEADER_TYPE;
415 iv_cust_trx_type_id := nl_cust_trx_type_id;
416 iv_CUSTOMER_ID := nl_CUSTOMER_ID;
417 iv_CUSTOMER_SITE_USE_ID := nl_CUSTOMER_SITE_USE_ID;
418 iv_LATE_CHARGE_TERM_ID := nl_LATE_CHARGE_TERM_ID;
419 iv_EXCHANGE_RATE_TYPE := nl_EXCHANGE_RATE_TYPE;
420 iv_EXCHANGE_RATE := nl_EXCHANGE_RATE;
421 iv_PAYMENT_SCHEDULE_ID := nl_PAYMENT_SCHEDULE_ID;
422 iv_org_id := nl_org_id;
423 iv_legal_entity_id := nl_legal_entity_id;
424 iv_interest_line_id := nl_interest_line_id;
425 iv_LATE_CHARGE_CALCULATION_TRX := nl_LATE_CHARGE_CALCULATION_TRX;
426 iv_DAYS_OF_INTEREST := nl_DAYS_OF_INTEREST;
427 iv_DAYS_OVERDUE_LATE := nl_DAYS_OVERDUE_LATE;
428 iv_DAILY_INTEREST_CHARGE := nl_DAILY_INTEREST_CHARGE;
429 iv_PAYMENT_SCHEDULE_ID := nl_PAYMENT_SCHEDULE_ID;
430 iv_INTEREST_CHARGED := nl_INTEREST_CHARGED;
431 iv_type := nl_type;
432 iv_salesrep_required_flag := nl_salesrep_required_flag;
433 iv_salesrep_id := nl_salesrep_id;
434 iv_salesrep_number := nl_salesrep_number;
435 iv_GL_ID_REC := nl_GL_ID_REC;
436 iv_GL_ID_REV := nl_gl_id_rev;
437 iv_cpt := 0;
438 iv_salesrep_set := 'N';
439 iv_sales_credit_name := NULL;
440 iv_sales_credit_id := NULL;
441 iv_original_trx_id := nl_original_trx_id;
442 iv_original_trx_class := nl_original_trx_class;
443 iv_DUE_DATE := nl_DUE_DATE;
444 iv_OUTSTANDING_AMOUNT := nl_OUTSTANDING_AMOUNT;
445 iv_PAYMENT_DATE := nl_PAYMENT_DATE;
446 iv_LAST_CHARGE_DATE := nl_LAST_CHARGE_DATE;
447 iv_INTEREST_RATE := nl_INTEREST_RATE;
448 log( message => 'empty_var_iv -');
449 END;
450
451
452
453
454
455 ----------------------
456 -- Feed inv to Inv api
457 ----------------------
458 PROCEDURE inv_to_inv_api_interface
459 (p_gl_date IN DATE,
460 p_cal_int_date IN DATE,
461 p_batch_id IN NUMBER)
462 IS
463 CURSOR c_trx(p_trx_id IN NUMBER) IS
464 SELECT trx_number
465 FROM ra_customer_trx
466 WHERE customer_trx_id = p_trx_id;
467
468 CURSOR c_recp(p_recp_id IN NUMBER) IS
469 SELECT receipt_number
470 FROM ar_cash_receipts
471 WHERE cash_receipt_id = p_recp_id;
472
473 l_curr_recp_id NUMBER := -9;
474 l_recp_num VARCHAR2(30);
475 l_curr_trx_id NUMBER := -9;
476 l_trx_num VARCHAR2(30);
477 l_comments VARCHAR2(2000); /*Bug 11737619*/
478
479 BEGIN
480 outandlog( message => 'inv_to_inv_api_interface +');
481 outandlog( message => ' p_gl_date :'||p_gl_date);
482 outandlog( message => ' p_cal_int_date :'||p_cal_int_date);
483 outandlog( message => ' p_batch_id :'||p_batch_id);
484
485 iv_header_cpt := iv_trx_header_tbl.COUNT;
486 iv_line_cpt := iv_trx_lines_tbl.COUNT;
487 iv_dist_cpt := iv_trx_dist_tbl.COUNT;
488 iv_salescredits_cpt := iv_trx_salescredits_tbl.COUNT;
489 iv_line_num := 0;
490
491 fnd_message.set_name('AR', 'AR_LATE_CHRGE_INTRST_INV_IMPRT'); /*Bug 11737619*/
492 l_comments := fnd_message.get; /*Bug 11737619*/
493
494 FOR i IN iv_interest_line_id.FIRST .. iv_interest_line_id.LAST LOOP
495
496
497 IF iv_curr_header_id <> iv_INTEREST_HEADER_ID(i) THEN
498 --
499 -- Invoice Header
500 --
501 iv_curr_header_id := iv_INTEREST_HEADER_ID(i);
502 iv_header_cpt := iv_header_cpt + 1;
503 iv_trx_header_tbl(iv_header_cpt).trx_header_id := iv_INTEREST_HEADER_ID(i);
504 iv_trx_header_tbl(iv_header_cpt).interest_header_id := iv_INTEREST_HEADER_ID(i);
505 iv_trx_header_tbl(iv_header_cpt).trx_date := p_cal_int_date;
506 iv_trx_header_tbl(iv_header_cpt).trx_currency := iv_CURRENCY_CODE(i);
507 iv_trx_header_tbl(iv_header_cpt).trx_class := iv_header_type(i);
508 iv_trx_header_tbl(iv_header_cpt).cust_trx_type_id := iv_cust_trx_type_id(i);
509 iv_trx_header_tbl(iv_header_cpt).gl_date := p_gl_date;
510 iv_trx_header_tbl(iv_header_cpt).bill_to_customer_id := iv_CUSTOMER_ID(i);
511 iv_trx_header_tbl(iv_header_cpt).bill_to_site_use_id := iv_CUSTOMER_SITE_USE_ID(i);
512 iv_trx_header_tbl(iv_header_cpt).term_id := iv_LATE_CHARGE_TERM_ID(i);
513 iv_trx_header_tbl(iv_header_cpt).org_id := iv_org_ID(i);
514 iv_trx_header_tbl(iv_header_cpt).legal_entity_id := iv_legal_entity_ID(i);
515 iv_trx_header_tbl(iv_header_cpt).late_charges_assessed := 'Y';
516 /*8266696*/
517 IF(iv_salesrep_required_flag(i) = 'Y') THEN
518 iv_trx_header_tbl(iv_header_cpt).primary_salesrep_id := iv_salesrep_id(i) ;
519 ELSE
520 iv_trx_header_tbl(iv_header_cpt).primary_salesrep_id := NULL;
521 END IF;
522
523 /*10297326 populate header table contact id with the table type variable*/
524
525 iv_trx_header_tbl(iv_header_cpt).bill_to_contact_id := iv_CONTACT_ID(i);
526 iv_trx_header_tbl(iv_header_cpt).bill_to_address_id := iv_CUST_ACCT_SITE_ID(i);
527
528 IF iv_CURRENCY_CODE(i) <> g_func_curr THEN
529 iv_trx_header_tbl(iv_header_cpt).exchange_rate_type := iv_EXCHANGE_RATE_TYPE(i);
530 iv_trx_header_tbl(iv_header_cpt).exchange_date := p_cal_int_date;
531 iv_trx_header_tbl(iv_header_cpt).exchange_rate := iv_EXCHANGE_RATE(i);
532 END IF;
533
534 iv_trx_header_tbl(iv_header_cpt).comments := l_comments; /*'Late Charge interest invoice import'; Commented hardcoded comment string. Bug 11737619*/
535 iv_trx_header_tbl(iv_header_cpt).internal_notes := NULL;
536 iv_trx_header_tbl(iv_header_cpt).finance_charges := NULL;
537
538
539 /*
540 IF iv_header_type(i) = 'INV' THEN
541 iv_trx_header_tbl(iv_header_cpt).interface_header_context := 'Interest Invoice';
542 ELSE
543 iv_trx_header_tbl(iv_header_cpt).interface_header_context := 'Debit memo Charge';
544 END IF;
545 iv_trx_header_tbl(iv_header_cpt).interface_header_attribute1:= p_batch_id;
546 -- interest batch id
547 iv_trx_header_tbl(iv_header_cpt).interface_header_attribute2:= iv_INTEREST_HEADER_ID(i);
548 -- interest header id
549 iv_trx_header_tbl(iv_header_cpt).interface_header_attribute3:= iv_PAYMENT_SCHEDULE_ID(i);
550 -- payment schedule id
551 iv_trx_header_tbl(iv_header_cpt).interface_header_attribute4:= 0;
552 -- line number 0 for header
553 iv_trx_header_tbl(iv_header_cpt).interface_header_attribute5:= NULL;
554 */
555
556
557 iv_trx_header_tbl(iv_header_cpt).org_id := iv_org_id(i);
558 iv_trx_header_tbl(iv_header_cpt).legal_entity_id := iv_legal_entity_id(i);
559 --
560 -- Receivables distribution
561 --
562 iv_dist_cpt := iv_dist_cpt + 1;
563 iv_trx_dist_tbl(iv_dist_cpt).trx_dist_id := iv_INTEREST_HEADER_ID(i);
564 iv_trx_dist_tbl(iv_dist_cpt).trx_header_id := iv_INTEREST_HEADER_ID(i);
565 iv_trx_dist_tbl(iv_dist_cpt).trx_LINE_ID := NULL;
566 iv_trx_dist_tbl(iv_dist_cpt).ACCOUNT_CLASS := 'REC';
567 iv_trx_dist_tbl(iv_dist_cpt).PERCENT := 100;
568 iv_trx_dist_tbl(iv_dist_cpt).CODE_COMBINATION_ID := iv_GL_ID_REC(i);
569 iv_trx_dist_tbl(iv_dist_cpt).COMMENTS := NULL;
570 END IF;
571
572
573 --
574 -- invoice line
575 --
576 iv_line_cpt := iv_line_cpt + 1;
577 iv_line_num := iv_line_num + 1;
578
579 iv_trx_lines_tbl(iv_line_cpt).trx_header_id := iv_INTEREST_HEADER_ID(i);
580 iv_trx_lines_tbl(iv_line_cpt).trx_line_id := iv_interest_line_id(i);
581 iv_trx_lines_tbl(iv_line_cpt).interest_line_id := iv_interest_line_id(i);
582 iv_trx_lines_tbl(iv_line_cpt).link_to_trx_line_id := NULL;
583 iv_trx_lines_tbl(iv_line_cpt).LINE_NUMBER := iv_line_num;
584 iv_trx_lines_tbl(iv_line_cpt).REASON_CODE := NULL;
585 iv_trx_lines_tbl(iv_line_cpt).INVENTORY_ITEM_ID := NULL;
586 iv_trx_lines_tbl(iv_line_cpt).QUANTITY_ORDERED := iv_DAYS_OF_INTEREST(i);
587 iv_trx_lines_tbl(iv_line_cpt).QUANTITY_INVOICED := iv_DAYS_OF_INTEREST(i);
588 iv_trx_lines_tbl(iv_line_cpt).UNIT_STANDARD_PRICE := iv_DAILY_INTEREST_CHARGE(i);
589 iv_trx_lines_tbl(iv_line_cpt).UNIT_SELLING_PRICE := iv_DAILY_INTEREST_CHARGE(i);
590 iv_trx_lines_tbl(iv_line_cpt).LINE_TYPE := 'LINE';
591
592 IF iv_GL_ID_REV(i) IS NOT NULL AND iv_GL_ID_REC(i) <> 0 THEN
593 --
594 -- Revenue distribution
595 --
596 iv_dist_cpt := iv_dist_cpt + 1;
597 iv_trx_dist_tbl(iv_dist_cpt).trx_dist_id := iv_INTEREST_LINE_ID(i);
598 iv_trx_dist_tbl(iv_dist_cpt).trx_header_id := iv_INTEREST_HEADER_ID(i);
599 iv_trx_dist_tbl(iv_dist_cpt).trx_LINE_ID := iv_INTEREST_LINE_ID(i);
600 iv_trx_dist_tbl(iv_dist_cpt).ACCOUNT_CLASS := 'REV';
601 iv_trx_dist_tbl(iv_dist_cpt).PERCENT := 100;
602 iv_trx_dist_tbl(iv_dist_cpt).CODE_COMBINATION_ID := iv_GL_ID_REV(i);
603 iv_trx_dist_tbl(iv_dist_cpt).COMMENTS := NULL;
604 END IF;
605
606 IF iv_original_trx_class(i) = 'PMT' THEN
607 IF l_curr_recp_id <> iv_original_trx_id(i) THEN
608 OPEN c_recp(p_recp_id => iv_original_trx_id(i));
609 FETCH c_recp INTO l_recp_num;
610 CLOSE c_recp;
611 l_curr_recp_id := iv_original_trx_id(i);
612 END IF;
613 ELSE
614 IF l_curr_trx_id <> iv_original_trx_id(i) THEN
615 OPEN c_trx(p_trx_id => iv_original_trx_id(i));
616 FETCH c_trx INTO l_trx_num;
617 CLOSE c_trx;
618 l_curr_trx_id := iv_original_trx_id(i);
619 END IF;
620 END IF;
621
622 iv_trx_lines_tbl(iv_line_cpt).DESCRIPTION :=
623 phrase
624 (p_type => iv_type(i),
625 p_class => iv_original_trx_class(i),
626 p_trx_number => l_trx_num,
627 p_receipt_number => l_recp_num,
628 p_due_date => iv_due_date(i),
629 p_outstanding_amt => iv_outstanding_amount(i),
630 p_payment_date => iv_payment_date(i),
631 p_days_overdue_late => iv_days_overdue_late(i),
632 p_last_charge_date => iv_last_charge_date(i),
633 p_interest_rate => iv_interest_rate(i),
634 p_calculate_interest_to_date => p_cal_int_date);
635
636 /* Can be usefull to match 11i autoinv
637 IF iv_header_type(i) = 'INV' THEN
638 iv_trx_lines_tbl(iv_line_cpt).INTERFACE_LINE_CONTEXT := 'Interest Invoice';
639 ELSE
640 iv_trx_lines_tbl(iv_line_cpt).INTERFACE_LINE_CONTEXT := 'Debit Memo Charge';
641 END IF;
642 iv_trx_lines_tbl(iv_line_cpt).interface_line_attribute1:= p_batch_id;
643 -- interest batch id
644 iv_trx_lines_tbl(iv_line_cpt).interface_line_attribute2:= iv_INTEREST_HEADER_ID(i);
645 -- interest header id
646 iv_trx_lines_tbl(iv_line_cpt).interface_line_attribute3:= iv_PAYMENT_SCHEDULE_ID(i);
647 -- payment schedule id
648 iv_trx_lines_tbl(iv_line_cpt).interface_line_attribute4:= iv_line_num;
649 -- line number 0 for header
650 iv_trx_lines_tbl(iv_line_cpt).interface_line_attribute5:= NULL;
651 */
652
653 /*To DO revert after testing done* BUG830281/
654
655 /* iv_trx_lines_tbl(iv_line_cpt).AMOUNT := iv_INTEREST_CHARGED(i);*/
656
657 /*Bug 8302813*/
658 iv_trx_lines_tbl(iv_line_cpt).AMOUNT := NULL;
659
660 /* iv_trx_lines_tbl(iv_line_cpt).AMOUNT := (iv_DAYS_OF_INTEREST(i)) * (iv_DAILY_INTEREST_CHARGE(i));*/
661
662 iv_trx_lines_tbl(iv_line_cpt).UOM_CODE := 'EA';
663
664
665 /*
666 -- Sales rep not required
667 --
668 -- Sales Credit
669 --
670 IF iv_salesrep_required_flag(i) = 'Y' OR iv_salesrep_id(i) IS NOT NULL THEN
671 iv_salesrep_set := 'Y';
672 ELSE
673 iv_salesrep_set := 'N';
674 END IF;
675
676 IF iv_salesrep_set = 'Y' AND iv_salesrep_id(i) IS NULL THEN
677 iv_salesrep_id(i) := -3;
678 iv_salesrep_number(i) := '-3';
679 END IF;
680 */
681
682
683 IF iv_salesrep_required_flag(i) = 'Y' THEN
684 iv_salesrep_id(i) := -3;
685 iv_salesrep_number(i) := '-3';
686 iv_salesrep_set := 'Y';
687 END IF;
688
689 IF iv_salesrep_set = 'Y' THEN
690 SELECT ssct.name,
691 ssct.sales_credit_type_id
692 INTO iv_sales_credit_name,
693 iv_sales_credit_id
694 FROM so_sales_credit_types ssct,
695 ra_salesreps ras
696 WHERE ras.salesrep_id = iv_salesrep_id(i)
697 AND ras.sales_credit_type_id = ssct.sales_credit_type_id;
698
699 iv_salescredits_cpt := iv_salescredits_cpt + 1;
700 iv_trx_salescredits_tbl(iv_salescredits_cpt).TRX_salescredit_ID := iv_interest_line_id(i);
701 iv_trx_salescredits_tbl(iv_salescredits_cpt).TRX_LINE_ID := iv_interest_line_id(i);
702 iv_trx_salescredits_tbl(iv_salescredits_cpt).SALESREP_ID := iv_salesrep_id(i);
703 iv_trx_salescredits_tbl(iv_salescredits_cpt).SALESREP_NUMBER := iv_salesrep_number(i);
704 iv_trx_salescredits_tbl(iv_salescredits_cpt).SALES_CREDIT_TYPE_NAME := iv_sales_credit_name;
705 iv_trx_salescredits_tbl(iv_salescredits_cpt).SALES_CREDIT_TYPE_ID := iv_sales_credit_id;
706 iv_trx_salescredits_tbl(iv_salescredits_cpt).SALESCREDIT_PERCENT_SPLIT := 100;
707 END IF;
708
709 END LOOP;
710
711 IF iv_create_flag = 'N' THEN
712 iv_create_flag := 'Y';
713 END IF;
714
715
716 UPDATE ar_late_charge_doc_gt
717 SET execution_status = 'R' --Ready
718 WHERE interest_header_id = iv_curr_header_id;
719
720 log( message => 'The interest header with the interest_header_id:'||iv_curr_header_id||' is ready for process');
721
722 empty_var_iv;
723
724 outandlog( message => ' iv_curr_header_id :'||iv_curr_header_id);
725 outandlog( message => 'inv_to_inv_api_interface -');
726 END;
727
728
729
730
731
732
733
734
735
736 ----------------------------
737 -- Execute Inv API
738 ----------------------------
739 PROCEDURE call_invoice_api
740 ( x_return_status OUT NOCOPY VARCHAR2,
741 x_msg_count OUT NOCOPY NUMBER,
742 x_msg_data OUT NOCOPY VARCHAR2)
743 IS
744 CURSOR c_line IS
745 SELECT TRX_LINE_ID ,
746 ERROR_MESSAGE||':'||INVALID_VALUE
747 FROM ar_trx_errors_gt
748 WHERE TRX_LINE_ID IS NOT NULL
749 ORDER BY TRX_HEADER_ID, TRX_LINE_ID;
750
751
752 CURSOR c_hdr IS
753 SELECT TRX_HEADER_ID ,
754 ERROR_MESSAGE||':'||INVALID_VALUE
755 FROM ar_trx_errors_gt
756 WHERE TRX_LINE_ID IS NULL
757 ORDER BY TRX_HEADER_ID;
758
759 CURSOR c_nb_inv_in_err IS
760 SELECT count(TRX_HEADER_ID)
761 FROM ar_trx_errors_gt
762 GROUP BY TRX_HEADER_ID;
763
764
765 CURSOR c_s IS
766 SELECT a.interest_line_id,
767 --{HYU update late_charge_Date
768 a.payment_schedule_id
769 --}
770 FROM ar_late_charge_doc_gt a
771 WHERE a.interest_batch_id = g_interest_batch_id
772 AND a.execution_status = 'R'
773 AND NOT EXISTS
774 (SELECT NULL
775 FROM ar_late_charge_doc_gt b
776 WHERE b.interest_header_id = a.interest_header_id
777 AND b.interest_batch_id = g_interest_batch_id
778 AND b.execution_status = 'E');
779
780 l_sucess_line_id DBMS_SQL.NUMBER_TABLE;
781 l_success_ps_id DBMS_SQL.NUMBER_TABLE;
782
783 l_trx_header_id DBMS_SQL.NUMBER_TABLE;
784 l_trx_line_id DBMS_SQL.NUMBER_TABLE;
785 l_err_text DBMS_SQL.VARCHAR2_TABLE;
786 l_err_line_text DBMS_SQL.VARCHAR2_TABLE;
787
788 l_curr_hdr_id NUMBER := 0;
789 l_curr_line_id NUMBER := 0;
790
791 l_header_upg DBMS_SQL.NUMBER_TABLE;
792 l_header_text DBMS_SQL.VARCHAR2_TABLE;
793 hcpt NUMBER := 0;
794
795 l_line_upg DBMS_SQL.NUMBER_TABLE;
796 l_line_text DBMS_SQL.VARCHAR2_TABLE;
797 lcpt NUMBER := 0;
798 l_text VARCHAR2(32000);
799 i NUMBER;
800
801
802 l_stop VARCHAR2(1) := 'N';
803 BEGIN
804 outandlog( message => 'call_invoice_api +');
805
806 x_return_status := FND_API.G_RET_STS_SUCCESS;
807 --
808 -- Execution of the invoice api
809 --
810 ar_late_charge_pkg.g_invoice_api_called_from_lc := 'Y';
811 AR_INVOICE_API_PUB.create_invoice(
812 p_api_version => 1.0,
813 x_return_status => x_return_status,
814 x_msg_count => x_msg_count,
815 x_msg_data => x_msg_data,
816 p_batch_source_rec => iv_batch_source_rec,
817 p_trx_header_tbl => iv_trx_header_tbl,
818 p_trx_lines_tbl => iv_trx_lines_tbl,
819 p_trx_dist_tbl => iv_trx_dist_tbl,
820 p_trx_salescredits_tbl => iv_trx_salescredits_tbl);
821 ar_late_charge_pkg.g_invoice_api_called_from_lc := 'N';
822
823 IF PG_DEBUG = 'Y' THEN
824 INSERT_HDR;
825
826 INSERT_LINE;
827
828 INSERT_DIST;
829 END IF;
830
831 --
832 -- Note Invoice API only return status <> FND_API.G_RET_STS_SUCCESS
833 -- if none standard errors are found
834 --
835 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
836 RAISE fnd_api.g_exc_error;
837 END IF;
838
839 --
840 -- Error at header
841 --
842 OPEN c_hdr;
843 FETCH c_hdr BULK COLLECT INTO
844 l_trx_header_id,
845 l_err_text ;
846 CLOSE c_hdr;
847
848 --
849 -- Invoice api caller will return error is functional setup incorrect
850 --
851 IF l_trx_header_id.COUNT > 0 THEN
852 x_return_status := fnd_api.g_ret_sts_error;
853 x_msg_count := 0;
854
855 FOR i IN 1..l_trx_header_id.COUNT LOOP
856
857 IF l_trx_header_id(i) <> l_curr_hdr_id THEN
858 IF l_curr_hdr_id <> 0 THEN
859 hcpt := hcpt + 1;
860 l_header_upg(hcpt) := l_curr_hdr_id;
861 l_header_text(hcpt):= SUBSTRB(l_text,1,2000);
862 x_msg_count := x_msg_count + 1;
863 END IF;
864
865 l_curr_hdr_id := l_trx_header_id(i);
866 l_text := NULL;
867 END IF;
868
869 l_text := SUBSTRB(l_text||l_err_text(i)||';',1,2000);
870 log('Header error interest_header_id:'||l_trx_header_id(i)||':'||l_text);
871
872 IF i = l_trx_header_id.LAST AND l_text IS NOT NULL THEN
873 hcpt := hcpt + 1;
874 l_header_upg(hcpt) := l_curr_hdr_id;
875 l_header_text(hcpt):= SUBSTRB(l_text,1,2000);
876 x_msg_count := x_msg_count + 1;
877 END IF;
878
879 END LOOP;
880
881 END IF;
882
883
884 IF l_header_upg.COUNT > 0 THEN
885
886 FORALL i IN l_header_upg.FIRST..l_header_upg.LAST
887 UPDATE ar_late_charge_doc_gt
888 SET execution_status = 'E',
889 hdr_err_msg = l_header_text(i)
890 WHERE interest_header_id = l_header_upg(i)
891 AND interest_batch_id = g_interest_batch_id;
892
893 FORALL i IN l_header_upg.FIRST..l_header_upg.LAST
894 UPDATE ar_interest_headers
895 SET process_status = 'E',
896 process_message = l_header_text(i)
897 WHERE interest_header_id = l_header_upg(i)
898 AND interest_batch_id = g_interest_batch_id;
899 END IF;
900
901 --
902 -- Error at line
903 --
904 OPEN c_line;
905 FETCH c_line BULK COLLECT INTO
906 l_trx_line_id ,
907 l_err_line_text;
908 CLOSE c_line;
909
910 --
911 -- Invoice api caller will return error is functional setup incorrect
912 --
913 IF l_trx_line_id.COUNT > 0 THEN
914 x_return_status := fnd_api.g_ret_sts_error;
915
916 FOR i IN 1..l_trx_line_id.COUNT LOOP
917
918 IF l_trx_line_id(i) <> l_curr_line_id THEN
919 IF l_curr_line_id <> 0 THEN
920 lcpt := lcpt + 1;
921 l_line_upg(lcpt) := l_curr_line_id;
922 l_line_text(lcpt):= SUBSTRB(l_text,1,2000);
923 END IF;
924
925 l_curr_line_id := l_trx_line_id(i);
926 l_text := NULL;
927 END IF;
928
929 l_text := SUBSTRB(l_text||l_err_line_text(i)||';',1,2000);
930 log('Line error interest_line_id:'||l_trx_line_id(i)||':'||l_text);
931
932 IF i = l_trx_line_id.LAST AND l_text IS NOT NULL THEN
933 lcpt := lcpt + 1;
934 l_line_upg(lcpt) := l_curr_line_id;
935 l_line_text(lcpt):= SUBSTRB(l_text,1,2000);
936 END IF;
937
938 END LOOP;
939 END IF;
940
941 IF l_line_upg.COUNT >0 THEN
942
943 FORALL i IN l_line_upg.FIRST..l_line_upg.LAST
944 UPDATE ar_late_charge_doc_gt
945 SET execution_status = 'E',
946 line_err_msg = l_line_text(i)
947 WHERE interest_line_id = l_line_upg(i);
948
949
950 FORALL i IN l_line_upg.FIRST..l_line_upg.LAST
951 UPDATE ar_interest_lines
952 SET process_status = 'E',
953 process_message= l_line_text(i)
954 WHERE interest_line_id = l_line_upg(i);
955 END IF;
956
957 OPEN c_s;
958 FETCH c_s BULK COLLECT INTO l_sucess_line_id,l_success_ps_id;
959 CLOSE c_s;
960
961 IF l_sucess_line_id.COUNT > 0 THEN
962 FORALL i IN l_sucess_line_id.FIRST..l_sucess_line_id.LAST
963 UPDATE ar_late_charge_doc_gt
964 SET execution_status = 'S'
965 WHERE execution_status = 'R'
966 AND interest_batch_id = g_interest_batch_id
967 AND interest_line_id = l_sucess_line_id(i);
968
969
970 log(message => 'Updating ar_payment_schedules late_charge_date for invoice and DM');
971
972 FORALL i IN l_success_ps_id.FIRST..l_success_ps_id.LAST
973 UPDATE ar_payment_schedules
974 SET last_charge_date = g_int_cal_date
975 WHERE payment_schedule_id = l_success_ps_id(i);
976
977 END IF;
978
979 --
980 -- empty_var_iv_api
981 --
982 iv_trx_header_tbl := nl_trx_header_tbl;
983 iv_trx_lines_tbl := nl_trx_lines_tbl;
984 iv_trx_dist_tbl := nl_trx_dist_tbl;
985 iv_trx_salescredits_tbl := nl_trx_salescredits_tbl;
986 iv_create_flag := 'N';
987 iv_header_cpt := 0;
988 iv_line_cpt := 0;
989 iv_dist_cpt := 0;
990 iv_salescredits_cpt := 0;
991 iv_curr_header_id := 0;
992 iv_line_num := 0;
993 iv_create_flag := 'N';
994
995
996 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
997 x_msg_data := 'Error are set back in the ar_interest_headers and lines table,
998 please retrieve from table with the interest_batch_id:'||g_interest_batch_id;
999 END IF;
1000
1001 --
1002 -- All errors at lines - return number of header in error
1003 --
1004 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1005 OPEN c_nb_inv_in_err;
1006 FETCH c_nb_inv_in_err INTO x_msg_count;
1007 CLOSE c_nb_inv_in_err;
1008 END IF;
1009
1010
1011 outandlog( message => 'call_invoice_api -');
1012
1013 EXCEPTION
1014 WHEN FND_API.G_EXC_ERROR THEN
1015 --
1016 -- Error from invoice api directly out
1017 --
1018 ar_late_charge_pkg.g_invoice_api_called_from_lc := 'N';
1019
1020 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1021 p_count => x_msg_count,
1022 p_data => x_msg_data );
1023
1024 outandlog( message => x_msg_data);
1025
1026 WHEN OTHERS THEN
1027
1028 ar_late_charge_pkg.g_invoice_api_called_from_lc := 'N';
1029
1030 outandlog( message => 'EXCEPTION OTHERS in call_invoice_api:'||SQLERRM);
1031 x_return_status := fnd_api.g_ret_sts_unexp_error;
1032 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1033 fnd_message.set_token('ERROR',SQLERRM);
1034 fnd_msg_pub.add;
1035
1036 fnd_msg_pub.count_and_get(
1037 p_encoded => fnd_api.g_false,
1038 p_count => x_msg_count,
1039 p_data => x_msg_data);
1040 outandlog( message => ' EXCEPTION OTHERS call_invoice_api :'||SQLERRM);
1041
1042 END;
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053 --------------------------
1054 -- Interest Invoice Cursor
1055 --------------------------
1056 PROCEDURE get_the_row_to_process
1057 (x_exec_status OUT NOCOPY VARCHAR2,
1058 p_worker_num IN NUMBER DEFAULT NULL)
1059 IS
1060 CURSOR csp IS
1061 SELECT LATE_CHARGE_DM_TYPE_ID,
1062 LATE_CHARGE_INV_TYPE_ID,
1063 ALLOW_LATE_CHARGES,
1064 PENALTY_REC_TRX_ID,
1065 FINCHRG_RECEIVABLES_TRX_ID
1066 FROM ar_system_parameters;
1067 l_sp csp%ROWTYPE;
1068
1069 CURSOR c IS
1070 SELECT NULL
1071 FROM ar_late_charge_doc_gt
1072 WHERE interest_batch_id = g_interest_batch_id
1073 AND header_type IN ('INV','DM')
1074 AND NVL(p_worker_num,-9) = NVL(worker_num, -9)
1075 AND execution_status = 'I';
1076 lf VARCHAR2(1);
1077 BEGIN
1078 log( message => 'get_the_row_to_process +');
1079 OPEN csp;
1080 FETCH csp INTO l_sp;
1081 CLOSE csp;
1082 INSERT INTO ar_late_charge_doc_gt
1083 (interest_header_id ,
1084 CURRENCY_CODE ,
1085 HEADER_TYPE ,
1086 cust_trx_type_id ,
1087 CUSTOMER_ID ,
1088 CUSTOMER_SITE_USE_ID ,
1089 LATE_CHARGE_TERM_ID ,
1090 EXCHANGE_RATE_TYPE ,
1091 EXCHANGE_RATE ,
1092 org_id ,
1093 legal_entity_id ,
1094 LATE_CHARGE_CALCULATION_TRX,
1095 interest_line_id ,
1096 DAYS_OF_INTEREST ,
1097 DAYS_OVERDUE_LATE ,
1098 DAILY_INTEREST_CHARGE ,
1099 PAYMENT_SCHEDULE_ID ,
1100 INTEREST_CHARGED ,
1101 type ,
1102 salesrep_required_flag ,
1103 salesrep_id ,
1104 salesrep_number ,
1105 GL_ID_REC ,
1106 execution_status ,
1107 interest_batch_id ,
1108 original_trx_id ,
1109 original_trx_class ,
1110 DUE_DATE ,
1111 OUTSTANDING_AMOUNT ,
1112 PAYMENT_DATE ,
1113 LAST_CHARGE_DATE ,
1114 INTEREST_RATE ,
1115 gl_id_rev ,
1116 worker_num )
1117 SELECT h.INTEREST_HEADER_ID,
1118 h.CURRENCY_CODE,
1119 h.HEADER_TYPE,
1120 --{
1121 -- h.cust_trx_type_id,
1122 DECODE(h.HEADER_TYPE,'INV',l_sp.LATE_CHARGE_INV_TYPE_ID,l_sp.LATE_CHARGE_DM_TYPE_ID),
1123 --}
1124 h.CUSTOMER_ID,
1125 h.CUSTOMER_SITE_USE_ID,
1126 h.LATE_CHARGE_TERM_ID,
1127 h.EXCHANGE_RATE_TYPE,
1128 h.EXCHANGE_RATE,
1129 h.org_id,
1130 h.legal_entity_id,
1131 h.LATE_CHARGE_CALCULATION_TRX,
1132 l.interest_line_id,
1133 l.DAYS_OF_INTEREST,
1134 l.DAYS_OVERDUE_LATE,
1135 l.DAILY_INTEREST_CHARGE,
1136 l.PAYMENT_SCHEDULE_ID,
1137 l.INTEREST_CHARGED,
1138 l.type,
1139 sp.salesrep_required_flag,
1140 -3,
1141 '-3',
1142 tty.GL_ID_REC,
1143 'I',
1144 g_interest_batch_id,
1145 l.original_trx_id,
1146 l.original_trx_class,
1147 l.DUE_DATE ,
1148 l.OUTSTANDING_AMOUNT,
1149 l.PAYMENT_DATE ,
1150 l.LAST_CHARGE_DATE ,
1151 l.INTEREST_RATE ,
1152 tty.GL_ID_REV,
1153 p_worker_num
1154 FROM ar_interest_headers h,
1155 ar_interest_lines l,
1156 ar_system_parameters sp,
1157 ra_cust_trx_types tty
1158 WHERE h.interest_batch_id = g_interest_batch_id
1159 AND h.INTEREST_HEADER_ID = l.INTEREST_HEADER_ID
1160 AND tty.cust_trx_type_id(+) = h.cust_trx_type_id
1161 AND h.HEADER_TYPE IN ('INV','DM')
1162 AND h.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
1163 AND NVL(l.interest_charged,0) <> 0
1164 AND DECODE(p_worker_num,NULL,NVL(h.worker_num,-9),p_worker_num)=NVL(h.worker_num,-9)
1165 AND h.PROCESS_STATUS = 'N'
1166 ORDER BY h.INTEREST_HEADER_ID,
1167 l.interest_line_id;
1168
1169
1170 --INSERT INTO hy_late_charge_doc_gt select * from ar_late_charge_doc_gt;
1171
1172 OPEN c;
1173 FETCH c INTO lf;
1174 IF c%FOUND THEN
1175 x_exec_status := 'Y';
1176 ELSE
1177 x_exec_status := 'N';
1178 END IF;
1179 CLOSE c;
1180 log( message => ' Find row to process :'||x_exec_status);
1181 log( message => 'get_the_row_to_process -');
1182 END;
1183
1184
1185 PROCEDURE get_nb_row_ready
1186 (x_nb_row_ready OUT NOCOPY NUMBER)
1187 IS
1188 CURSOR c IS
1189 SELECT COUNT(*)
1190 FROM ar_late_charge_doc_gt
1191 WHERE execution_status = 'R'
1192 AND interest_batch_id = g_interest_batch_id;
1193 BEGIN
1194 log( message => 'get_nb_row_ready +');
1195 OPEN c;
1196 FETCH c INTO x_nb_row_ready;
1197 IF c%NOTFOUND THEN
1198 x_nb_row_ready := 0;
1199 END IF;
1200 CLOSE c;
1201 log( message => ' x_nb_row_ready :'||x_nb_row_ready);
1202 log( message => 'get_nb_row_ready -');
1203 END;
1204
1205
1206 PROCEDURE get_list_headers
1207 (x_list OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
1208 x_nb_list OUT NOCOPY NUMBER,
1209 p_worker_num IN NUMBER DEFAULT NULL)
1210 IS
1211 CURSOR nb_headers IS
1212 SELECT DISTINCT interest_header_id
1213 FROM ar_late_charge_doc_gt
1214 WHERE execution_status = 'I'
1215 AND interest_batch_id = g_interest_batch_id
1216 AND NVL(p_worker_num,-9) = NVL(worker_num,-9)
1217 AND header_type IN ('INV','DM');
1218 BEGIN
1219 log( message => 'get_list_headers +');
1220 OPEN nb_headers;
1221 FETCH nb_headers BULK COLLECT INTO x_list;
1222 CLOSE nb_headers;
1223 x_nb_list := x_list.COUNT;
1224 log( message => ' x_nb_list :'||x_nb_list);
1225 log( message => 'get_list_headers -');
1226 END;
1227
1228
1229 PROCEDURE get_read_a_header
1230 (p_header_id IN NUMBER,
1231 p_exec_status IN VARCHAR2 DEFAULT 'I',
1232 p_clear_iv IN VARCHAR2 DEFAULT 'Y',
1233 x_nb_row OUT NOCOPY NUMBER)
1234 IS
1235 /*10297326 add join with hz_cust_site uses to get contact id*/
1236 CURSOR get_read_a_header IS
1237 SELECT
1238 lgt.interest_header_id ,
1239 lgt.CURRENCY_CODE ,
1240 lgt.HEADER_TYPE ,
1241 lgt.cust_trx_type_id ,
1242 lgt.CUSTOMER_ID ,
1243 lgt.CUSTOMER_SITE_USE_ID ,
1244 lgt.LATE_CHARGE_TERM_ID ,
1245 lgt.EXCHANGE_RATE_TYPE ,
1246 lgt.EXCHANGE_RATE ,
1247 lgt.org_id ,
1248 lgt.legal_entity_id ,
1249 lgt.LATE_CHARGE_CALCULATION_TRX,
1250 lgt.interest_line_id ,
1251 lgt.DAYS_OF_INTEREST ,
1252 lgt.DAYS_OVERDUE_LATE ,
1253 lgt.DAILY_INTEREST_CHARGE ,
1254 lgt.PAYMENT_SCHEDULE_ID ,
1255 lgt.INTEREST_CHARGED ,
1256 lgt.type ,
1257 lgt.salesrep_required_flag ,
1258 lgt.salesrep_id ,
1259 lgt.salesrep_number ,
1260 lgt.GL_ID_REC ,
1261 lgt.original_trx_id ,
1262 lgt.original_trx_class ,
1263 lgt.DUE_DATE ,
1264 lgt.OUTSTANDING_AMOUNT,
1265 lgt.PAYMENT_DATE ,
1266 lgt.LAST_CHARGE_DATE ,
1267 lgt.INTEREST_RATE ,
1268 lgt.gl_id_rev,
1269 su.contact_id,
1270 su.cust_acct_site_id
1271 FROM ar_late_charge_doc_gt lgt,
1272 hz_cust_site_uses_all su
1273 WHERE interest_header_id = p_header_id
1274 AND interest_batch_id = g_interest_batch_id
1275 AND execution_status = p_exec_status
1276 AND header_type IN ('INV','DM')
1277 AND su.site_use_id= CUSTOMER_SITE_USE_ID;
1278 BEGIN
1279 log( message => 'get_read_a_header +');
1280 log( message => ' p_header_id :'||p_header_id);
1281 IF p_clear_iv = 'Y' THEN
1282 empty_var_iv;
1283 END IF;
1284 /*10297326 Fetch value of contact id in newly declared table variable*/
1285 OPEN get_read_a_header;
1286 FETCH get_read_a_header BULK COLLECT INTO
1287 iv_INTEREST_HEADER_ID,
1288 iv_CURRENCY_CODE,
1289 iv_HEADER_TYPE,
1290 iv_cust_trx_type_id,
1291 iv_CUSTOMER_ID,
1292 iv_CUSTOMER_SITE_USE_ID,
1293 iv_LATE_CHARGE_TERM_ID,
1294 iv_EXCHANGE_RATE_TYPE,
1295 iv_EXCHANGE_RATE,
1296 iv_org_id,
1297 iv_legal_entity_id,
1298 iv_LATE_CHARGE_CALCULATION_TRX,
1299 iv_interest_line_id,
1300 iv_DAYS_OF_INTEREST,
1301 iv_DAYS_OVERDUE_LATE,
1302 iv_DAILY_INTEREST_CHARGE,
1303 iv_PAYMENT_SCHEDULE_ID,
1304 iv_INTEREST_CHARGED,
1305 iv_type,
1306 iv_salesrep_required_flag,
1307 iv_salesrep_id,
1308 iv_salesrep_number,
1309 iv_GL_ID_REC,
1310 iv_original_trx_id,
1311 iv_original_trx_class,
1312 iv_DUE_DATE ,
1313 iv_OUTSTANDING_AMOUNT,
1314 iv_PAYMENT_DATE ,
1315 iv_LAST_CHARGE_DATE ,
1316 iv_INTEREST_RATE ,
1317 iv_gl_id_rev,
1318 iv_CONTACT_ID,
1319 iv_CUST_ACCT_SITE_ID;
1320 CLOSE get_read_a_header;
1321 x_nb_row := iv_INTEREST_HEADER_ID.COUNT;
1322 log( message => ' x_nb_row :'||x_nb_row);
1323 log( message => 'get_read_a_header -');
1324 END;
1325
1326
1327
1328
1329
1330 PROCEDURE create_charge_inv_dm
1331 ( p_batch_source_id IN NUMBER,
1332 p_batch_id IN NUMBER,
1333 p_worker_num IN NUMBER DEFAULT NULL,
1334 p_gl_date IN DATE DEFAULT NULL,
1335 p_cal_int_date IN DATE DEFAULT NULL,
1336 p_api_bulk_size IN NUMBER DEFAULT NULL,
1337 x_return_status OUT NOCOPY VARCHAR2,
1338 x_msg_count OUT NOCOPY NUMBER,
1339 x_msg_data OUT NOCOPY VARCHAR2)
1340 IS
1341 CURSOR c_gl_date IS
1342 SELECT gl_date,
1343 calculate_interest_to_date
1344 FROM ar_interest_batches
1345 WHERE interest_batch_id = g_interest_batch_id;
1346
1347 l_current_trx_id NUMBER := -9;
1348 l_return_status VARCHAR2(10);
1349 l_msg_count NUMBER;
1350 l_msg_data VARCHAR2(2000);
1351 l_bulk_size NUMBER;
1352 s_gl_date date;
1353 s_cal_int_date date;
1354 l_inv_meaning VARCHAR2(80);
1355 l_dm_meaning VARCHAR2(80);
1356 l_last_fetch BOOLEAN := FALSE;
1357 j NUMBER := 0;
1358 l_list_header_id_tab DBMS_SQL.NUMBER_TABLE;
1359 l_nb_headers NUMBER;
1360 l_nb_row_ready NUMBER;
1361 l_exec_status VARCHAR2(1) := 'Y';
1362 L_NB_OF_DOC NUMBER;
1363 l_list_header_in_error DBMS_SQL.NUMBER_TABLE;
1364 BEGIN
1365 outandlog( message => 'create_charge_inv_dm +');
1366 outandlog( message => ' p_batch_source_id :'||p_batch_source_id);
1367 outandlog( message => ' p_batch_id :'||p_batch_id);
1368 outandlog( message => ' p_worker_num :'||p_worker_num);
1369 outandlog( message => ' p_gl_date :'||p_gl_date );
1370 outandlog( message => ' p_cal_int_date :'||p_cal_int_date );
1371 outandlog( message => ' p_api_bulk_size :'||p_api_bulk_size );
1372
1373 g_interest_batch_id := p_batch_id;
1374 arp_standard.debug('g_interest_batch_id:'||g_interest_batch_id);
1375
1376
1377 l_inv_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('AR_LATE_CHARGE_TYPE', 'INV');
1378 l_dm_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('AR_LATE_CHARGE_TYPE', 'DM');
1379
1380 x_return_status := FND_API.G_RET_STS_SUCCESS;
1381 x_msg_count := 0;
1382
1383 IF p_api_bulk_size IS NULL OR p_api_bulk_size = 0 THEN
1384 l_bulk_size := g_bulk_fetch_rows;
1385 ELSE
1386 l_bulk_size := p_api_bulk_size;
1387 END IF;
1388
1389
1390 IF p_gl_date IS NULL OR p_cal_int_date IS NULL THEN
1391 OPEN c_gl_date;
1392 FETCH c_gl_date INTO s_gl_date,
1393 s_cal_int_date;
1394 IF c_gl_date%NOTFOUND OR s_gl_date IS NULL THEN
1395 arp_standard.debug(' Late Charge Batch GL date and calculate interest date is required');
1396 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1397 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'GL_DATE' );
1398 FND_MSG_PUB.ADD;
1399 x_return_status := FND_API.G_RET_STS_ERROR;
1400 END IF;
1401 IF c_gl_date%NOTFOUND OR s_cal_int_date IS NULL THEN
1402 arp_standard.debug(' Late Charge Batch calculate interest date is required');
1403 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1404 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'calculate_interest_to_date' );
1405 FND_MSG_PUB.ADD;
1406 x_return_status := FND_API.G_RET_STS_ERROR;
1407 END IF;
1408 CLOSE c_gl_date;
1409 ELSE
1410 s_gl_date := p_gl_date;
1411 s_cal_int_date := p_cal_int_date;
1412 END IF;
1413
1414 g_int_cal_date := s_cal_int_date;
1415
1416 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1417 RAISE FND_API.G_EXC_ERROR;
1418 END IF;
1419
1420 iv_batch_source_rec.batch_source_id := p_batch_source_id;
1421 iv_batch_source_rec.default_date := s_gl_date;
1422
1423
1424 --Get all rows to process
1425 get_the_row_to_process(x_exec_status => l_exec_status,
1426 p_worker_num => p_worker_num);
1427 outandlog('l_exec_status:'||l_exec_status);
1428
1429
1430 IF l_exec_status = 'Y' THEN
1431
1432 -- Num documents
1433 get_list_headers(x_list => l_list_header_id_tab,
1434 x_nb_list => l_nb_headers,
1435 p_worker_num => p_worker_num);
1436
1437 outandlog('l_nb_headers:'||l_nb_headers);
1438
1439
1440 IF l_nb_headers > 0 THEN
1441
1442 FOR i IN l_list_header_id_tab.FIRST.. l_list_header_id_tab.LAST LOOP
1443
1444 -- call structure
1445 get_read_a_header(p_header_id => l_list_header_id_tab(i),
1446 p_exec_status => 'I',
1447 p_clear_iv => 'Y',
1448 x_nb_row => l_nb_of_doc);
1449
1450
1451 --Put in invoice api interface
1452 IF l_nb_of_doc > 0 THEN
1453 inv_to_inv_api_interface(p_gl_date => s_gl_date,
1454 p_cal_int_date => s_cal_int_date,
1455 p_batch_id => g_interest_batch_id);
1456 END IF;
1457
1458 --Execute API if required
1459 get_nb_row_ready(x_nb_row_ready => l_nb_row_ready);
1460
1461
1462 IF l_nb_row_ready >= l_bulk_size THEN
1463
1464 arp_standard.debug('l_nb_row_ready:'||l_nb_row_ready);
1465 call_invoice_api(x_return_status => l_return_status,
1466 x_msg_count => l_msg_count,
1467 x_msg_data => l_msg_data);
1468
1469 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1470 IF x_msg_count IS NULL THEN
1471 x_msg_count := l_msg_count;
1472 ELSE
1473 x_msg_count := x_msg_count + l_msg_count;
1474 END IF;
1475 x_msg_data := l_msg_data;
1476 END IF;
1477
1478 END IF;
1479
1480 END LOOP;
1481
1482 outandlog('iv_create_flag:'||iv_create_flag);
1483
1484
1485 IF iv_create_flag = 'Y' THEN
1486 call_invoice_api(x_return_status => l_return_status,
1487 x_msg_count => l_msg_count,
1488 x_msg_data => l_msg_data);
1489
1490 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1491
1492 IF x_msg_count IS NULL THEN
1493 x_msg_count := l_msg_count;
1494 ELSE
1495 x_msg_count := x_msg_count + l_msg_count;
1496 END IF;
1497
1498 x_msg_data := l_msg_data;
1499
1500 END IF;
1501
1502 END IF;
1503
1504 END IF;
1505
1506 log( message => ' update ar_interest_headers for successfull headers');
1507
1508 UPDATE ar_interest_headers SET
1509 process_status = 'S',
1510 process_message = NULL
1511 WHERE interest_batch_id = g_interest_batch_id
1512 AND process_status = 'N'
1513 AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
1514 AND DECODE(p_worker_num,NULL,NVL(worker_num,-9),p_worker_num)=NVL(worker_num,-9)
1515 AND interest_header_id IN
1516 (SELECT MAX(interest_header_id)
1517 FROM ar_late_charge_doc_gt
1518 WHERE interest_batch_id = g_interest_batch_id
1519 AND execution_status = 'S'
1520 AND NVL(p_worker_num,-9)= NVL(worker_num,-9)
1521 AND header_type IN ('INV','DM')
1522 GROUP BY interest_header_id);
1523
1524 log( message => ' update ar_interest_headers for error headers');
1525 UPDATE ar_interest_headers SET
1526 process_status = 'E'
1527 WHERE interest_batch_id = g_interest_batch_id
1528 AND process_status = 'N'
1529 AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
1530 AND DECODE(p_worker_num,NULL,NVL(worker_num,-9),p_worker_num) = NVL(worker_num,-9)
1531 AND header_type IN ('INV','DM')
1532 RETURN interest_batch_id BULK COLLECT INTO l_list_header_in_error;
1533
1534 log( message => ' update ar_payment_schedule for successfull headers');
1535 UPDATE ar_payment_schedules
1536 SET last_charge_date = g_int_cal_date
1537 WHERE payment_schedule_id IN
1538 (SELECT l.PAYMENT_SCHEDULE_ID
1539 FROM ar_interest_headers h,
1540 ar_interest_lines l
1541 WHERE h.interest_batch_id = g_interest_batch_id
1542 AND h.process_status = 'S'
1543 AND h.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
1544 AND DECODE(p_worker_num,NULL,NVL(h.worker_num,-9),p_worker_num)=NVL(h.worker_num,-9)
1545 AND h.interest_header_id = l.interest_header_id
1546 AND h.header_type IN ('INV','DM'));
1547
1548 END IF;
1549
1550 outandlog( message => 'create_charge_inv_dm -');
1551
1552 EXCEPTION
1553 WHEN FND_API.G_EXC_ERROR THEN
1554 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1555 p_count => x_msg_count,
1556 p_data => x_msg_data );
1557
1558 outandlog( message => x_msg_data);
1559
1560 WHEN OTHERS THEN
1561 FND_MESSAGE.Set_Name('AR','HZ_API_OTHERS_EXCEP');
1562 FND_MESSAGE.Set_Token('ERROR',SQLERRM);
1563 fnd_msg_pub.add;
1564 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1565 p_count => x_msg_count,
1566 p_data => x_msg_data );
1567 outandlog( message => x_msg_data);
1568 END;
1569
1570
1571
1572
1573 PROCEDURE insert_adj_process
1574 (x_nb_adj OUT NOCOPY NUMBER,
1575 p_worker_num IN NUMBER DEFAULT NULL)
1576 IS
1577 CURSOR csp IS
1578 SELECT LATE_CHARGE_DM_TYPE_ID,
1579 LATE_CHARGE_INV_TYPE_ID,
1580 ALLOW_LATE_CHARGES,
1581 PENALTY_REC_TRX_ID,
1582 FINCHRG_RECEIVABLES_TRX_ID
1583 FROM ar_system_parameters;
1584 l_sp csp%ROWTYPE;
1585
1586 CURSOR c IS
1587 SELECT COUNT(*) FROM ar_late_charge_doc_gt
1588 WHERE interest_batch_id = g_interest_batch_id
1589 AND NVL(p_worker_num,-9) = NVL(p_worker_num,worker_num)
1590 AND execution_status = 'I'
1591 AND header_type = 'ADJ';
1592 BEGIN
1593 log( message => 'insert_adj_process +');
1594 OPEN csp;
1595 FETCH csp INTO l_sp;
1596 CLOSE csp;
1597
1598 INSERT INTO ar_late_charge_doc_gt
1599 ( INTEREST_CHARGED,
1600 PAYMENT_SCHEDULE_ID,
1601 TYPE,
1602 ORIGINAL_TRX_ID,
1603 INTEREST_HEADER_ID,
1604 INTEREST_LINE_ID,
1605 receivables_trx_id,
1606 receivables_trx_name,
1607 interest_batch_id,
1608 execution_status,
1609 header_type,
1610 worker_num)
1611 SELECT l.INTEREST_CHARGED,
1612 l.PAYMENT_SCHEDULE_ID,
1613 l.TYPE,
1614 l.ORIGINAL_TRX_ID,
1615 l.INTEREST_HEADER_ID,
1616 l.INTEREST_LINE_ID,
1617 --{
1618 decode(l.receivables_TRX_ID,null,
1619 DECODE(l.type,'PENALTY',l_sp.PENALTY_REC_TRX_ID,
1620 l_sp.FINCHRG_RECEIVABLES_TRX_ID),
1621 -1,DECODE(l.type,'PENALTY',l_sp.PENALTY_REC_TRX_ID,
1622 l_sp.FINCHRG_RECEIVABLES_TRX_ID)
1623 , l.receivables_TRX_ID),
1624 -- rtrx.receivables_trx_id,
1625 --}
1626 rtrx.name,
1627 g_interest_batch_id,
1628 'I',
1629 header_type,
1630 p_worker_num
1631 FROM ar_interest_lines l,
1632 ar_interest_headers h,
1633 ar_interest_batches b,
1634 ar_receivables_trx rtrx,
1635 ar_payment_schedules psch
1636 WHERE b.interest_batch_id = g_interest_batch_id
1637 AND h.interest_batch_id = b.interest_batch_id
1638 AND l.INTEREST_HEADER_ID = h.INTEREST_HEADER_ID
1639 AND h.HEADER_TYPE = 'ADJ'
1640 AND h.display_flag = 'Y' --HYU CDI Only adjustment generatable documents need to be considered
1641 AND rtrx.receivables_trx_id(+) = l.receivables_trx_id
1642 AND psch.payment_schedule_id(+) = l.PAYMENT_SCHEDULE_ID
1643 AND psch.customer_trx_id(+) = l.ORIGINAL_TRX_ID
1644 AND NVL(l.INTEREST_CHARGED,0) <> 0
1645 AND l.PROCESS_STATUS = 'N'
1646 AND DECODE(p_worker_num,NULL,NVL(h.worker_num,-9),p_worker_num) =NVL(h.worker_num,-9);
1647 OPEN c;
1648 FETCH c INTO x_nb_adj;
1649 CLOSE c;
1650 log( message => ' x_nb_adj :'||x_nb_adj);
1651 log( message => 'insert_adj_process -');
1652 END;
1653
1654
1655 PROCEDURE create_charge_adj
1656 ( p_batch_id IN NUMBER,
1657 p_worker_num IN NUMBER DEFAULT NULL,
1658 p_gl_date IN DATE DEFAULT NULL,
1659 p_cal_int_date IN DATE DEFAULT NULL,
1660 p_api_bulk_size IN NUMBER DEFAULT NULL,
1661 x_num_adj_created OUT NOCOPY NUMBER,
1662 x_num_adj_error OUT NOCOPY NUMBER,
1663 x_return_status OUT NOCOPY VARCHAR2,
1664 x_msg_count OUT NOCOPY NUMBER,
1665 x_msg_data OUT NOCOPY VARCHAR2)
1666 IS
1667 CURSOR c_gl_date IS
1668 SELECT gl_date,
1669 calculate_interest_to_date
1670 FROM ar_interest_batches
1671 WHERE interest_batch_id = g_interest_batch_id;
1672
1673 CURSOR cadj IS
1674 SELECT INTEREST_CHARGED,
1675 PAYMENT_SCHEDULE_ID,
1676 TYPE,
1677 ORIGINAL_TRX_ID,
1678 INTEREST_HEADER_ID,
1679 INTEREST_LINE_ID,
1680 receivables_trx_id,
1681 receivables_trx_name,
1682 g_interest_batch_id,
1683 worker_num
1684 FROM ar_late_charge_doc_gt
1685 WHERE interest_batch_id = g_interest_batch_id
1686 AND header_type = 'ADJ'
1687 AND execution_status = 'I'
1688 AND NVL(p_worker_num,-9) = NVL(worker_num,-9);
1689
1690
1691 l_interest_charged DBMS_SQL.NUMBER_TABLE;
1692 l_payment_schedule_id DBMS_SQL.NUMBER_TABLE;
1693 l_type DBMS_SQL.VARCHAR2_TABLE;
1694 l_original_trx_id DBMS_SQL.NUMBER_TABLE;
1695 l_interest_header_id DBMS_SQL.NUMBER_TABLE;
1696 l_interest_line_id DBMS_SQL.NUMBER_TABLE;
1697 l_rec_trx_id DBMS_SQL.NUMBER_TABLE;
1698 l_rec_name DBMS_SQL.VARCHAR2_TABLE;
1699 l_interest_batch_id DBMS_SQL.NUMBER_TABLE;
1700 l_worker_num DBMS_SQL.NUMBER_TABLE;
1701 l_process_status DBMS_SQL.VARCHAR2_TABLE;
1702 l_process_msg DBMS_SQL.VARCHAR2_TABLE;
1703 l_error_line_id DBMS_SQL.NUMBER_TABLE;
1704
1705 --For late_charge_date on payment schedules
1706 l_adjusted_ps DBMS_SQL.NUMBER_TABLE;
1707 l_adjusted_ps_cnt NUMBER := 0;
1708
1709 l_last_fetch BOOLEAN := FALSE;
1710 l_adj_meaning VARCHAR2(80);
1711 s_gl_date DATE;
1712 s_cal_int_date DATE;
1713
1714 l_null_char DBMS_SQL.VARCHAR2_TABLE;
1715 l_null_num DBMS_SQL.NUMBER_TABLE;
1716 l_null_date DBMS_SQL.DATE_TABLE;
1717 l_msg_count NUMBER;
1718 l_msg_data VARCHAR2(2000);
1719 l_return_status VARCHAR2(10);
1720 l_adj_rec ar_adjustments%ROWTYPE;
1721 l_new_adjust_number VARCHAR2(20);
1722 l_new_adjust_id NUMBER;
1723 l_bulk_size NUMBER;
1724 i NUMBER;
1725 j NUMBER := 0;
1726 err_cpt NUMBER := 0;
1727 x_nb_adj NUMBER;
1728 ll_msg_data VARCHAR2(2000);
1729 cnt NUMBER;
1730 no_adj_to_process EXCEPTION;
1731 BEGIN
1732 outandlog( message => 'create_charge_adj +');
1733
1734 g_interest_batch_id := p_batch_id;
1735 arp_standard.debug('g_interest_batch_id:'||g_interest_batch_id);
1736
1737 l_adj_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning('AR_LATE_CHARGE_TYPE', 'ADJ');
1738
1739 x_return_status := FND_API.G_RET_STS_SUCCESS;
1740 x_msg_count := 0;
1741 x_num_adj_created := 0;
1742 x_num_adj_error := 0;
1743
1744
1745 insert_adj_process(x_nb_adj,p_worker_num);
1746
1747 IF x_nb_adj = 0 THEN
1748 RAISE no_adj_to_process;
1749 END IF;
1750
1751 IF p_api_bulk_size IS NULL OR p_api_bulk_size = 0 THEN
1752 l_bulk_size := g_bulk_fetch_rows;
1753 ELSE
1754 l_bulk_size := p_api_bulk_size;
1755 END IF;
1756
1757
1758 IF p_gl_date IS NULL OR p_cal_int_date IS NULL THEN
1759 OPEN c_gl_date;
1760 FETCH c_gl_date INTO s_gl_date,
1761 s_cal_int_date;
1762 IF c_gl_date%NOTFOUND OR s_gl_date IS NULL THEN
1763 arp_standard.debug(' Late Charge Batch GL date and calculate interest date is required');
1764 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1765 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'GL_DATE' );
1766 FND_MSG_PUB.ADD;
1767 x_return_status := FND_API.G_RET_STS_ERROR;
1768 END IF;
1769 IF c_gl_date%NOTFOUND OR s_cal_int_date IS NULL THEN
1770 arp_standard.debug(' Late Charge Batch calculate interest date is required');
1771 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1772 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'calculate_interest_to_date' );
1773 FND_MSG_PUB.ADD;
1774 x_return_status := FND_API.G_RET_STS_ERROR;
1775 END IF;
1776 CLOSE c_gl_date;
1777 ELSE
1778 s_gl_date := p_gl_date;
1779 s_cal_int_date := p_cal_int_date;
1780 END IF;
1781
1782 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1783 RAISE FND_API.G_EXC_ERROR;
1784 END IF;
1785
1786
1787 OPEN cadj;
1788 LOOP
1789 FETCH cadj BULK COLLECT INTO
1790 l_interest_charged ,
1791 l_payment_schedule_id ,
1792 l_type ,
1793 l_original_trx_id ,
1794 l_interest_header_id ,
1795 l_interest_line_id ,
1796 l_rec_trx_id ,
1797 l_rec_name ,
1798 l_interest_batch_id ,
1799 l_worker_num
1800 LIMIT l_bulk_size;
1801
1802 IF cadj%NOTFOUND THEN
1803 l_last_fetch := TRUE;
1804 END IF;
1805
1806 IF (l_original_trx_id.COUNT = 0) AND (l_last_fetch) THEN
1807 EXIT;
1808 END IF;
1809
1810 j := j + 1;
1811 log(' loop interation in create_adj num '||j);
1812
1813 FOR i IN l_interest_line_id.FIRST .. l_interest_line_id.LAST LOOP
1814
1815
1816 l_adj_rec.payment_schedule_id := l_payment_schedule_id(i);
1817 l_adj_rec.apply_date := s_cal_int_date;
1818 l_adj_rec.gl_date := s_gl_date;
1819 l_adj_rec.receivables_trx_id := l_rec_trx_id(i);
1820 l_adj_rec.created_from := 'LATE_CHARGE_BATCH';
1821 l_adj_rec.adjustment_type := 'A';
1822 l_adj_rec.type := 'CHARGES';
1823 l_adj_rec.interest_header_id := l_interest_header_id(i);
1824 l_adj_rec.interest_line_id := l_interest_line_id(i);
1825 l_adj_rec.amount := l_interest_charged(i);
1826
1827
1828 -- Call Adjustment api:
1829 log('Calling ar_adjust_pub.Create_Adjustment for interest_line_id : '||l_interest_line_id(i));
1830
1831
1832 ar_adjust_pub.Create_Adjustment (
1833 p_api_name => 'AR_ADJUST_PUB',
1834 p_api_version => 1.0,
1835 p_msg_count => l_msg_count,
1836 p_msg_data => l_msg_data,
1837 p_return_status => l_return_status,
1838 p_adj_rec => l_adj_rec,
1839 p_new_adjust_number => l_new_adjust_number,
1840 p_new_adjust_id => l_new_adjust_id);
1841
1842
1843 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1844
1845
1846 l_process_status(i) := 'E';
1847 x_num_adj_error := x_num_adj_error + 1;
1848
1849 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1850 x_return_status := l_return_status;
1851 x_msg_data := l_msg_data;
1852 x_msg_count := l_msg_count;
1853 END IF;
1854
1855 log('The adjustment creation fails customer_trx_id : '||l_rec_trx_id(i)||'
1856 payment_schedule_id : '||l_payment_schedule_id(i));
1857
1858 IF l_msg_count > 1 THEN
1859 ll_msg_data := NULL;
1860 FOR cnt IN 1..l_msg_count LOOP
1861 ll_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
1862 FND_API.G_FALSE);
1863 log(' error text : '|| ll_msg_data);
1864 l_msg_data := SUBSTRB(l_msg_data||ll_msg_data,1,2000);
1865 END LOOP;
1866 END IF;
1867
1868 l_process_msg(i) := SUBSTRB(l_msg_data,1,2000);
1869
1870 ELSE
1871
1872 l_process_status(i) := 'S';
1873 l_process_msg(i) := 'Adjustment_id:'||l_new_adjust_id;
1874 x_num_adj_created := x_num_adj_created + 1;
1875
1876 --Update Payment_Schedule Late_Charge_Date
1877 l_adjusted_ps_cnt := l_adjusted_ps_cnt + 1;
1878 l_adjusted_ps(l_adjusted_ps_cnt) := l_payment_schedule_id(i);
1879
1880 outandlog('The adjustment creation succes customer_trx_id : '||l_rec_trx_id(i)||'
1881 payment_schedule_id : '||l_payment_schedule_id(i)||'
1882 with the adjustment number : '|| l_new_adjust_number);
1883
1884 END IF;
1885
1886 END LOOP;
1887
1888 --Update Payment_Schedule Late_Charge_Date HYU
1889 IF l_adjusted_ps.COUNT > 0 THEN
1890 log( message => 'Updating ar_payment_schedules late_charge_date for adjustments');
1891
1892 FORALL i IN l_adjusted_ps.FIRST .. l_adjusted_ps.LAST
1893 UPDATE ar_payment_schedules
1894 SET last_charge_date = s_cal_int_date
1895 WHERE payment_schedule_id = l_adjusted_ps(i);
1896
1897 l_adjusted_ps_cnt := 0;
1898 l_adjusted_ps := l_null_num;
1899 END IF;
1900 --}
1901
1902 IF l_interest_line_id.COUNT > 0 THEN
1903 FORALL i IN l_interest_line_id.FIRST .. l_interest_line_id.LAST
1904 UPDATE ar_late_charge_doc_gt
1905 SET execution_status = l_process_status(i),
1906 LINE_ERR_MSG = l_process_msg(i)
1907 WHERE interest_line_id = l_interest_line_id(i)
1908 AND interest_batch_id = g_interest_batch_id;
1909
1910 log( message => 'Updating ar_interest_lines process_status for adjustment');
1911
1912 FORALL i IN l_interest_line_id.FIRST .. l_interest_line_id.LAST
1913 UPDATE ar_interest_lines
1914 SET PROCESS_STATUS = l_process_status(i),
1915 PROCESS_MESSAGE = l_process_msg(i)
1916 WHERE interest_line_id = l_interest_line_id(i);
1917 END IF;
1918
1919 l_interest_charged := l_null_num;
1920 l_payment_schedule_id := l_null_num;
1921 l_type := l_null_char;
1922 l_original_trx_id := l_null_num;
1923 l_interest_header_id := l_null_num;
1924 l_interest_line_id := l_null_num;
1925 l_rec_trx_id := l_null_num;
1926 l_rec_name := l_null_char;
1927 l_interest_batch_id := l_null_num;
1928 l_worker_num := l_null_num;
1929 l_process_status := l_null_char;
1930 l_process_msg := l_null_char;
1931
1932
1933 COMMIT;
1934
1935 END LOOP;
1936 CLOSE cadj;
1937
1938
1939 log( message => 'Updating ar_interest_headers for adjustment in Error');
1940
1941 UPDATE ar_interest_headers a
1942 SET a.process_status = 'E'
1943 WHERE a.interest_batch_id = g_interest_batch_id
1944 AND DECODE(p_worker_num,NULL,NVL(a.worker_num,-9),p_worker_num)=NVL(a.worker_num,-9)
1945 AND a.header_type = 'ADJ'
1946 AND a.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
1947 AND EXISTS
1948 (SELECT NULL
1949 FROM ar_interest_lines b
1950 WHERE b.interest_header_id = a.interest_header_id
1951 AND b.process_status = 'E');
1952
1953 log( message => 'Updating ar_interest_headers for adjustment in Success');
1954 UPDATE ar_interest_headers a
1955 SET a.process_status = 'S',
1956 a.process_message= NULL
1957 WHERE a.interest_batch_id = g_interest_batch_id
1958 AND a.header_type = 'ADJ'
1959 AND a.process_status = 'N'
1960 AND a.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
1961 AND DECODE(p_worker_num,NULL,NVL(a.worker_num,-9),p_worker_num)=NVL(a.worker_num,-9);
1962
1963
1964 --{Update the last_accrue_date for customer account site uses
1965 log( message => 'Updating hz_cust_site_uses for adjustment in Success');
1966 UPDATE hz_cust_site_uses
1967 SET LAST_ACCRUE_CHARGE_DATE = s_cal_int_date
1968 WHERE SITE_USE_ID IN
1969 (SELECT DISTINCT customer_site_use_id
1970 FROM ar_interest_headers h
1971 WHERE h.process_status = 'S'
1972 AND h.interest_batch_id = g_interest_batch_id
1973 AND h.header_type = 'ADJ'
1974 AND h.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
1975 AND DECODE(p_worker_num,NULL,NVL(h.worker_num,-9),p_worker_num)=NVL(h.worker_num,-9));
1976 --}
1977 outandlog( message => 'create_charge_adj -');
1978
1979 EXCEPTION
1980 WHEN no_adj_to_process THEN
1981 NULL;
1982 WHEN FND_API.G_EXC_ERROR THEN
1983 IF cadj%ISOPEN THEN CLOSE cadj; END IF;
1984 fnd_msg_pub.count_and_get(
1985 p_encoded => fnd_api.g_false,
1986 p_count => x_msg_count,
1987 p_data => x_msg_data);
1988 outandlog( message => ' EXCEPTION FND_API.G_EXC_ERROR create_charge_adj :'||x_msg_data);
1989
1990 WHEN OTHERS THEN
1991 IF cadj%ISOPEN THEN CLOSE cadj; END IF;
1992 x_return_status := fnd_api.g_ret_sts_unexp_error;
1993 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1994 fnd_message.set_token('ERROR',SQLERRM);
1995 fnd_msg_pub.add;
1996
1997 fnd_msg_pub.count_and_get(
1998 p_encoded => fnd_api.g_false,
1999 p_count => x_msg_count,
2000 p_data => x_msg_data);
2001 outandlog( message => ' EXCEPTION OTHERS create_charge_adj :'||SQLERRM);
2002 END;
2003
2004
2005 PROCEDURE write_exec_report
2006 IS
2007
2008 CURSOR nb_of_inv IS
2009 SELECT count(*) nb,
2010 execution_status status
2011 FROM (select interest_header_id interest_header_id,
2012 MIN(execution_status) execution_status
2013 FROM ar_late_charge_doc_gt
2014 WHERE interest_batch_id = g_interest_batch_id
2015 AND header_type = 'INV'
2016 AND execution_status IN ('E','S')
2017 GROUP BY interest_header_id) b
2018 GROUP BY execution_status;
2019
2020
2021 CURSOR nb_of_dm IS
2022 SELECT count(*) nb,
2023 execution_status status
2024 FROM (select interest_header_id interest_header_id,
2025 MIN(execution_status) execution_status
2026 FROM ar_late_charge_doc_gt
2027 WHERE interest_batch_id = g_interest_batch_id
2028 AND header_type = 'DM'
2029 AND execution_status IN ('E','S')
2030 GROUP BY interest_header_id) b
2031 GROUP BY execution_status;
2032
2033
2034 CURSOR nb_of_adjustment IS
2035 SELECT count(*) nb,
2036 execution_status status
2037 FROM ar_late_charge_doc_gt
2038 WHERE interest_batch_id = g_interest_batch_id
2039 AND header_type = 'ADJ'
2040 GROUP BY execution_status;
2041 l_count DBMS_SQL.NUMBER_TABLE;
2042 l_status DBMS_SQL.VARCHAR2_TABLE;
2043 l_clr_count DBMS_SQL.NUMBER_TABLE;
2044 l_clr_status DBMS_SQL.VARCHAR2_TABLE;
2045 l_batch_status VARCHAR2(1) := 'S';
2046
2047 PROCEDURE set_batch_status
2048 (p_status IN VARCHAR2,
2049 x_batch_status OUT NOCOPY VARCHAR2)
2050 IS
2051 BEGIN
2052 IF p_status = 'E' THEN
2053 l_batch_status := 'E';
2054 ELSIF p_status = 'I' THEN
2055 IF l_batch_status = 'S' THEN
2056 l_batch_status := NULL;
2057 END IF;
2058 END IF;
2059 END;
2060
2061 BEGIN
2062 log('write_exec_report +');
2063 outandlog('The submission for late charges creation:');
2064 outandlog(' interest_batch_id:'||g_interest_batch_id);
2065
2066 OPEN nb_of_adjustment;
2067 FETCH nb_of_adjustment BULK COLLECT INTO
2068 l_count ,
2069 l_status;
2070 CLOSE nb_of_adjustment;
2071
2072 IF l_status.COUNT > 0 THEN
2073 FOR i IN l_status.FIRST..l_status.LAST LOOP
2074 IF l_status(i) = 'S' THEN
2075 outandlog(' Number of adjustment successfully created:'|| l_count(i));
2076 ELSIF l_status(i) = 'E' THEN
2077 outandlog(' Number of adjustment in error:'|| l_count(i));
2078 ELSE
2079 outandlog(' Number of adjustment in status '|| l_status(i) ||':' || l_count(i));
2080 END IF;
2081 set_batch_status(l_status(i),l_batch_status);
2082 END LOOP;
2083 l_count := l_clr_count;
2084 l_status := l_clr_status;
2085 END IF;
2086
2087 OPEN nb_of_inv;
2088 FETCH nb_of_inv BULK COLLECT INTO
2089 l_count ,
2090 l_status;
2091 CLOSE nb_of_inv;
2092
2093 IF l_status.COUNT > 0 THEN
2094 FOR i IN l_status.FIRST..l_status.LAST LOOP
2095 IF l_status(i) = 'S' THEN
2096 outandlog(' Number of invoice successfully created:'|| l_count(i));
2097 ELSIF l_status(i) = 'E' THEN
2098 outandlog(' Number of invoice in error:'|| l_count(i));
2099 ELSE
2100 outandlog(' Number of invoice in status '|| l_status(i) ||':' || l_count(i));
2101 END IF;
2102 set_batch_status(l_status(i),l_batch_status);
2103 END LOOP;
2104 l_count := l_clr_count;
2105 l_status := l_clr_status;
2106 END IF;
2107
2108 OPEN nb_of_dm;
2109 FETCH nb_of_dm BULK COLLECT INTO
2110 l_count ,
2111 l_status;
2112 CLOSE nb_of_dm;
2113
2114 IF l_status.COUNT > 0 THEN
2115 FOR i IN l_status.FIRST..l_status.LAST LOOP
2116 IF l_status(i) = 'S' THEN
2117 outandlog(' Number of invoice successfully created:'|| l_count(i));
2118 ELSIF l_status(i) = 'E' THEN
2119 outandlog(' Number of invoice in error:'|| l_count(i));
2120 ELSIF l_status(i) = 'I' THEN
2121 outandlog(' Number of invoice not executed:'|| l_count(i));
2122 ELSE
2123 outandlog(' Number of invoice in status '|| l_status(i) ||':' || l_count(i));
2124 END IF;
2125 set_batch_status(l_status(i),l_batch_status);
2126 END LOOP;
2127 END IF;
2128 log('write_exec_report -');
2129 END;
2130
2131
2132 PROCEDURE create_late_charge_child
2133 (errbuf OUT NOCOPY VARCHAR2,
2134 retcode OUT NOCOPY VARCHAR2,
2135 p_batch_source_id IN NUMBER,
2136 p_batch_id IN NUMBER,
2137 p_gl_date IN DATE,
2138 p_cal_int_date IN DATE,
2139 p_api_bulk_size IN NUMBER)
2140 IS
2141 x_num_adj_created NUMBER;
2142 x_num_adj_error NUMBER;
2143 x_return_status VARCHAR2(10);
2144 x_msg_count NUMBER;
2145 x_msg_data VARCHAR2(2000);
2146
2147 CURSOR c_err IS
2148 SELECT NULL
2149 FROM ar_interest_headers
2150 WHERE interest_batch_id = g_interest_batch_id
2151 AND process_status = 'E'
2152 AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
2153 AND header_type IN ('INV','DM');
2154 l_err_found VARCHAR2(1);
2155
2156 CURSOR c_one_doc_success IS
2157 SELECT NULL
2158 FROM ar_interest_headers
2159 WHERE interest_batch_id = g_interest_batch_id
2160 AND process_status = 'S'
2161 AND display_flag = 'Y'; -- Document generating a Late Charges Document
2162
2163 l_success_found VARCHAR2(1);
2164
2165 BEGIN
2166 log('create_late_charge +');
2167
2168 DELETE FROM ar_late_charge_doc_gt;
2169
2170 UPDATE ar_interest_headers a
2171 SET a.process_message = ''
2172 WHERE a.interest_batch_id = p_batch_id
2173 AND a.display_flag = 'Y'; --HYU CDI only document generating the Late Charge s Doc
2174
2175 UPDATE ar_interest_lines a
2176 SET a.process_message = ''
2177 WHERE a.interest_header_id IN
2178 (SELECT interest_header_id
2179 FROM ar_interest_headers
2180 WHERE interest_batch_id = p_batch_id
2181 AND display_flag = 'Y'); --HYU CDI only document generating the Late Charge s Doc
2182
2183
2184
2185
2186 retcode := 0;
2187 outandlog( message =>'create_late_charge per site for the batch:'||p_batch_id );
2188 outandlog( message =>' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
2189 outandlog( message =>' p_cal_int_date :'||p_cal_int_date);
2190 outandlog( message =>' p_cal_int_date :'||p_cal_int_date);
2191 outandlog( message =>' p_api_bulk_size :'||p_api_bulk_size);
2192
2193 x_return_status := fnd_api.g_ret_sts_success;
2194 log('1 create_charge_adj...');
2195
2196 create_charge_adj
2197 (p_batch_id => p_batch_id,
2198 p_gl_date => p_gl_date,
2199 p_cal_int_date => p_cal_int_date,
2200 p_api_bulk_size => p_api_bulk_size,
2201 x_num_adj_created => x_num_adj_created,
2202 x_num_adj_error => x_num_adj_error,
2203 x_return_status => x_return_status,
2204 x_msg_count => x_msg_count,
2205 x_msg_data => x_msg_data);
2206
2207 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2208 retcode := 1;
2209 errbuf := 'Some Charge Adjustments have failed,
2210 please verify the data in ar_interest_headers and lines tables with the batch_id :'||g_interest_batch_id;
2211 log(errbuf);
2212 END IF;
2213
2214 x_return_status := fnd_api.g_ret_sts_success;
2215
2216 log('2 create_charge_inv_dm...');
2217 create_charge_inv_dm
2218 (p_batch_source_id => p_batch_source_id,
2219 p_batch_id => p_batch_id,
2220 p_gl_date => p_gl_date,
2221 p_cal_int_date => p_cal_int_date,
2222 p_api_bulk_size => p_api_bulk_size,
2223 x_return_status => x_return_status,
2224 x_msg_count => x_msg_count,
2225 x_msg_data => x_msg_data);
2226
2227
2228 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2229 retcode := 1;
2230 errbuf := 'Some Charge invoice, debit memo have failed
2231 please verify the data in ar_interest_headers and lines tables with the batch_id :'||g_interest_batch_id;
2232 log(errbuf);
2233 ELSE
2234 OPEN c_err;
2235 FETCH c_err INTO l_err_found;
2236 IF c_err%FOUND THEN
2237 retcode := 1;
2238 errbuf := 'Some Charge invoice, debit memo have failed
2239 please verify the data in ar_interest_headers and lines tables with the batch_id :'||g_interest_batch_id;
2240 log(errbuf);
2241 END IF;
2242 CLOSE c_err;
2243
2244 --{ HYU CDI included in the calculation
2245 OPEN c_one_doc_success;
2246 FETCH c_one_doc_success INTO l_success_found;
2247 IF c_one_doc_success%FOUND THEN
2248 --{ HYU CDI included in calculation without generating the late charges document
2249 UPDATE ar_payment_schedules
2250 SET last_charge_date = g_int_cal_date
2251 WHERE payment_schedule_id IN
2252 (SELECT l.PAYMENT_SCHEDULE_ID
2253 FROM ar_interest_headers h,
2254 ar_interest_lines l
2255 WHERE h.interest_batch_id = g_interest_batch_id
2256 AND h.display_flag = 'N' -- Document included in Late Charges Calculation
2257 AND h.process_status = 'N'
2258 AND h.interest_header_id = l.interest_header_id);
2259
2260 UPDATE ar_interest_headers
2261 SET process_status = 'S'
2262 WHERE interest_batch_id = g_interest_batch_id
2263 AND display_flag = 'N' -- Document included in Late Charges Calculation
2264 AND process_status = 'N';
2265 --}
2266 END IF;
2267 CLOSE c_one_doc_success;
2268
2269
2270 END IF;
2271
2272 IF retcode = 1 THEN
2273 errbuf := errbuf||'-Please check the log file for detail';
2274 END IF;
2275
2276 write_exec_report;
2277
2278 FND_FILE.close;
2279 EXCEPTION
2280 WHEN fnd_Api.g_exc_error THEN
2281 fnd_msg_pub.count_and_get(
2282 p_encoded => fnd_api.g_false,
2283 p_count => x_msg_count,
2284 p_data => x_msg_data);
2285 retcode := 1;
2286 outandlog('Error: ' || FND_MESSAGE.GET);
2287 log('Batch en error ' || x_msg_data);
2288 errbuf := errbuf || logerror || x_msg_data;
2289 outandlog('Aborting concurrent program execution');
2290 FND_FILE.close;
2291
2292 WHEN OTHERS THEN
2293 outandlog('Error: ' || FND_MESSAGE.GET);
2294 log('SQL Error ' || SQLERRM);
2295 retcode := 2;
2296 errbuf := errbuf || logerror || SQLERRM;
2297 outandlog('Aborting concurrent program execution');
2298 FND_FILE.close;
2299 END;
2300
2301
2302 PROCEDURE submit_late_charge_child
2303 (p_batch_id IN NUMBER,
2304 p_batch_source_id IN NUMBER,
2305 p_gl_date IN DATE,
2306 p_cal_int_date IN DATE,
2307 p_api_bulk_size IN NUMBER,
2308 x_out_request_id OUT NOCOPY NUMBER)
2309 IS
2310 lc_sub_pb EXCEPTION;
2311 BEGIN
2312 FND_REQUEST.SET_ORG_ID(g_org_id);
2313 x_out_request_id := FND_REQUEST.SUBMIT_REQUEST(
2314 application=>'AR',
2315 program =>'ARLCPS',
2316 sub_request=>FALSE,
2317 argument1 => p_batch_source_id,
2318 argument2 => p_batch_id,
2319 argument3 => p_gl_date,
2320 argument4 => p_cal_int_date,
2321 argument5 => p_api_bulk_size );
2322 IF x_out_request_id <> 0 THEN
2323 INSERT INTO ar_submission_ctrl_gt
2324 (worker_id , --p_batch_source_id
2325 batch_id , --
2326 script_name , --script_name
2327 status , --
2328 order_num , --order helper number
2329 request_id , --request_id
2330 table_name ) --table_name
2331 VALUES
2332 (p_batch_source_id,
2333 NULL,
2334 'ARLCPS',
2335 'SUBMITTED',
2336 1,
2337 x_out_request_id,
2338 'ARLC');
2339 COMMIT;
2340 ELSE
2341 RAISE lc_sub_pb;
2342 END IF;
2343 EXCEPTION
2344 WHEN lc_sub_pb THEN
2345 log(logerror(SQLERRM));
2346 WHEN OTHERS THEN
2347 log(logerror(SQLERRM));
2348 END;
2349
2350
2351 PROCEDURE wait_for_end_subreq(
2352 p_interval IN NUMBER DEFAULT 60
2353 ,p_max_wait IN NUMBER DEFAULT 180
2354 ,p_sub_name IN VARCHAR2)
2355 IS
2356 CURSOR reqs IS
2357 SELECT request_id
2358 FROM ar_submission_ctrl_gt
2359 WHERE status <> 'COMPLETE'
2360 AND script_name = p_sub_name;
2361 l_req_id NUMBER;
2362 l_phase VARCHAR2(50);
2363 l_status VARCHAR2(50);
2364 l_dev_phase VARCHAR2(50);
2365 l_dev_status VARCHAR2(50);
2366 l_message VARCHAR2(2000);
2367 l_complete BOOLEAN;
2368 done EXCEPTION;
2369 BEGIN
2370 log('wait_for_end_subreq :'|| p_sub_name ||' to finish');
2371 LOOP
2372 OPEN reqs;
2373 LOOP
2374 FETCH reqs INTO l_req_id;
2375 EXIT WHEN reqs%NOTFOUND;
2376
2377 FND_REQUEST.SET_ORG_ID(g_org_id);
2378 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
2379 request_id=>l_req_id,
2380 interval=>p_interval,
2381 max_wait=>p_max_wait,
2382 phase=>l_phase,
2383 status=>l_status,
2384 dev_phase=>l_dev_phase,
2385 dev_status=>l_dev_status,
2386 message=>l_message);
2387 IF l_dev_phase = 'COMPLETE' THEN
2388 UPDATE ar_submission_ctrl_gt
2389 SET status = 'COMPLETE'
2390 WHERE request_id = l_req_id;
2391 END IF;
2392 END LOOP;
2393 CLOSE reqs;
2394
2395 OPEN reqs;
2396 FETCH reqs INTO l_req_id;
2397 IF reqs%NOTFOUND THEN
2398 RAISE done;
2399 END IF;
2400 CLOSE reqs;
2401 END LOOP;
2402 EXCEPTION
2403 WHEN done THEN
2404 IF reqs%ISOPEN THEN
2405 CLOSE reqs;
2406 END IF;
2407 WHEN OTHERS THEN
2408 IF reqs%ISOPEN THEN
2409 CLOSE reqs;
2410 END IF;
2411 RAISE;
2412 END;
2413
2414 PROCEDURE get_status_for_sub_process
2415 (p_sub_name IN VARCHAR2,
2416 x_status OUT NOCOPY VARCHAR2)
2417 IS
2418 CURSOR reqs IS
2419 SELECT request_id
2420 FROM ar_submission_ctrl_gt
2421 WHERE status = 'COMPLETE'
2422 AND script_name = p_sub_name;
2423 l_req_id NUMBER;
2424 lbool BOOLEAN;
2425 lphase VARCHAR2(80);
2426 lstatus VARCHAR2(80);
2427 dphase VARCHAR2(30);
2428 dstatus VARCHAR2(30);
2429 lmessage VARCHAR2(240);
2430 PROCEDURE set_status
2431 (p_status IN VARCHAR2,
2432 x_status IN OUT NOCOPY VARCHAR2) IS
2433 BEGIN
2434 IF x_status = 'E' THEN
2435 RETURN;
2436 ELSE
2437 IF p_status <> 'NORMAL' THEN
2438 x_status := 'E';
2439 END IF;
2440 END IF;
2441 END;
2442 BEGIN
2443 x_status := 'S';
2444 OPEN reqs;
2445 LOOP
2446 FETCH reqs INTO l_req_id;
2447 EXIT WHEN reqs%NOTFOUND;
2448 lbool := FND_CONCURRENT.GET_REQUEST_STATUS
2449 (request_id => l_req_id,
2450 phase => lphase,
2451 status => lstatus,
2452 dev_phase => dphase,
2453 dev_status => dstatus,
2454 message => lmessage);
2455 IF lbool THEN
2456 set_status(dstatus,x_status);
2457 END IF;
2458 END LOOP;
2459 CLOSE reqs;
2460 END;
2461
2462
2463 PROCEDURE create_late_charge
2464 (errbuf OUT NOCOPY VARCHAR2,
2465 retcode OUT NOCOPY VARCHAR2,
2466 p_max_workers IN NUMBER DEFAULT 4,
2467 p_interval IN NUMBER DEFAULT 60,
2468 p_max_wait IN NUMBER DEFAULT 180,
2469 p_api_bulk_size IN NUMBER DEFAULT 1000,
2470 p_batch_source_id IN NUMBER,
2471 p_batch_id IN NUMBER )
2472 IS
2473 CURSOR c IS
2474 SELECT transferred_status,
2475 object_version_number,
2476 CALCULATE_INTEREST_TO_DATE,
2477 BATCH_STATUS,
2478 GL_DATE
2479 FROM ar_interest_batches
2480 WHERE interest_batch_id = g_interest_batch_id;
2481
2482 l_transferred_status VARCHAR2(1);
2483 x_object_version_number NUMBER;
2484 l_CALCULATE_INTEREST_TO_DATE DATE;
2485 l_BATCH_STATUS VARCHAR2(30);
2486 l_GL_DATE DATE;
2487
2488 CURSOR c_site IS
2489 SELECT DISTINCT customer_site_use_id
2490 FROM ar_interest_headers
2491 WHERE interest_batch_id = g_interest_batch_id
2492 AND process_status = 'N';
2493
2494 CURSOR c_batch_source IS
2495 SELECT NULL
2496 FROM ra_batch_sources
2497 WHERE BATCH_SOURCE_ID = p_batch_source_id;
2498
2499 CURSOR c_err IS
2500 SELECT NULL
2501 FROM ar_interest_headers
2502 WHERE interest_batch_id = g_interest_batch_id;
2503
2504 l_err VARCHAR2(1);
2505 l_test VARCHAR2(1);
2506 l_customer_site_use_id DBMS_SQL.NUMBER_TABLE;
2507 x_num_adj_created NUMBER;
2508 x_num_adj_error NUMBER;
2509 x_return_status VARCHAR2(10);
2510 x_msg_count NUMBER;
2511 x_msg_data VARCHAR2(2000);
2512 l_request_id NUMBER;
2513 l_exec_srs VARCHAR2(1) := 'N';
2514 BEGIN
2515 log('create_late_charge +');
2516
2517 retcode := 0;
2518 g_interest_batch_id := p_batch_id;
2519
2520 outandlog( message =>'create_late_charge for the batch:'||p_batch_id );
2521 outandlog( message =>' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
2522
2523 x_return_status := fnd_api.g_ret_sts_success;
2524
2525 IF p_batch_source_id IS NULL THEN
2526 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2527 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_source_id' );
2528 FND_MSG_PUB.ADD;
2529 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
2530 ELSE
2531 OPEN c_batch_source;
2532 FETCH c_batch_source INTO l_test;
2533 IF c_batch_source%NOTFOUND THEN
2534 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
2535 FND_MESSAGE.SET_TOKEN( 'FK', 'batch_source_id' );
2536 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_source_id' );
2537 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ra_batch_sources');
2538 FND_MSG_PUB.ADD;
2539 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
2540 END IF;
2541 CLOSE c_batch_source;
2542 END IF;
2543
2544 OPEN c;
2545 FETCH c INTO l_transferred_status,
2546 x_object_version_number,
2547 l_CALCULATE_INTEREST_TO_DATE,
2548 l_BATCH_STATUS,
2549 l_GL_DATE;
2550 IF c%NOTFOUND THEN
2551 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
2552 FND_MESSAGE.SET_TOKEN( 'FK', 'batch_id' );
2553 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_id' );
2554 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_interest_batches');
2555 FND_MSG_PUB.ADD;
2556 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
2557 outandlog('Interest Batch not found with the interest_batch_id :'||p_batch_id);
2558 ELSE
2559 IF l_BATCH_STATUS <> 'F' OR l_BATCH_STATUS IS NULL THEN
2560 fnd_message.set_name('AR', 'AR_ONLY_VALUE_ALLOWED');
2561 fnd_message.set_token('COLUMN', 'BATCH_STATUS');
2562 fnd_message.set_token('VALUES', 'F');
2563 fnd_msg_pub.add;
2564 retcode := 2;
2565 x_return_status := fnd_api.g_ret_sts_error;
2566 outandlog('Interest Batch batch_status should be Final to import interest_batch_id :'||p_batch_id);
2567 END IF;
2568 IF l_CALCULATE_INTEREST_TO_DATE IS NULL THEN
2569 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2570 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'CALCULATE_INTEREST_TO_DATE' );
2571 FND_MSG_PUB.ADD;
2572 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
2573 outandlog('Interest Batch calculation interest to date is mandatory interest_batch_id :'||p_batch_id);
2574 END IF;
2575 IF l_GL_DATE IS NULL THEN
2576 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2577 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'GL_DATE' );
2578 FND_MSG_PUB.ADD;
2579 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
2580 outandlog('Interest Batch GL date is mandatory interest_batch_id :'||p_batch_id);
2581 END IF;
2582 IF l_transferred_status = 'S' THEN
2583 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
2584 FND_MESSAGE.SET_TOKEN( 'STATUS', l_transferred_status );
2585 fnd_msg_pub.add;
2586 retcode := 1;
2587 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
2588 outandlog('Interest Batch already successfully transferred interest_batch_id :'||p_batch_id);
2589 ELSIF l_transferred_status = 'E' THEN
2590 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
2591 FND_MESSAGE.SET_TOKEN( 'STATUS', l_transferred_status );
2592 fnd_msg_pub.add;
2593 retcode := 1;
2594 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2595 outandlog('Interest Batch is in Error, please fix it before submitting interest_batch_id :'||p_batch_id);
2596 ELSIF l_transferred_status = 'P' THEN
2597 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
2598 FND_MESSAGE.SET_TOKEN( 'STATUS', l_transferred_status );
2599 fnd_msg_pub.add;
2600 retcode := 1;
2601 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2602 outandlog('Interest Batch is in process, can not resubmit interest_batch_id :'||p_batch_id);
2603 ELSIF l_transferred_status = 'N' THEN
2604 outandlog('Processing the batch interest_batch_id :'||p_batch_id);
2605 ELSIF l_transferred_status IS NOT NULL THEN
2606 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
2607 FND_MESSAGE.SET_TOKEN( 'STATUS', l_transferred_status);
2608 fnd_msg_pub.add;
2609 fnd_message.set_name('AR', 'AR_ONLY_VALUES_ALLOWED');
2610 fnd_message.set_token('COLUMN', 'TRANSFERRED_STATUS');
2611 fnd_message.set_token('VALUES', 'P,E,N,S');
2612 fnd_msg_pub.add;
2613 retcode := 2;
2614 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2615 outandlog('Interest Batch transferred flag should be in (NULL,S,E) no other value permitted interest_batch_id :'||p_batch_id);
2616 END IF;
2617 END IF;
2618 CLOSE c;
2619
2620 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2621 RAISE fnd_Api.g_exc_unexpected_error;
2622 END IF;
2623
2624 log('Updating the batch status to Pending to start the process...');
2625 AR_INTEREST_BATCHES_PKG.update_batch
2626 (p_init_msg_list => 'T',
2627 P_INTEREST_BATCH_ID => g_interest_batch_id,
2628 P_BATCH_STATUS => 'F',
2629 P_TRANSFERRED_status => 'P',
2630 p_updated_by_program => 'ARLCSM',
2631 x_OBJECT_VERSION_NUMBER => x_object_version_number,
2632 x_return_status => x_return_status,
2633 x_msg_count => x_msg_count,
2634 x_msg_data => x_msg_data);
2635
2636 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2637 RAISE fnd_Api.g_exc_error;
2638 END IF;
2639
2640 OPEN c_site;
2641 FETCH c_site BULK COLLECT INTO l_customer_site_use_id;
2642 CLOSE c_site;
2643
2644
2645 IF l_customer_site_use_id.COUNT <> 0 THEN
2646 FOR i IN l_customer_site_use_id.FIRST..l_customer_site_use_id.LAST LOOP
2647 --submission of late charge per site use
2648 submit_late_charge_child
2649 (p_batch_id => p_batch_id,
2650 p_batch_source_id => p_batch_source_id,
2651 p_gl_date => l_GL_DATE,
2652 p_cal_int_date => l_CALCULATE_INTEREST_TO_DATE,
2653 p_api_bulk_size => p_api_bulk_size,
2654 x_out_request_id => l_request_id);
2655 END LOOP;
2656
2657 wait_for_end_subreq(
2658 p_interval => p_interval
2659 ,p_max_wait => p_max_wait
2660 ,p_sub_name => 'ARLCPS' );
2661 END IF;
2662
2663 log( message => 'Updating interest batch status to :'||l_batch_status||' for batch_id :'||g_interest_batch_id);
2664
2665 get_status_for_sub_process
2666 (p_sub_name => 'ARLCPS',
2667 x_status => l_transferred_status);
2668
2669 -- UPDATE ar_interest_batches
2670 -- SET TRANSFERRED_STATUS = l_transferred_status,
2671 -- object_version_number = x_object_version_number + 1
2672 -- WHERE interest_batch_id = g_interest_batch_id;
2673
2674 OPEN c_err;
2675 FETCH c_err INTO l_err;
2676 IF c_err%NOTFOUND THEN
2677 AR_INTEREST_BATCHES_PKG.update_batch
2678 (p_init_msg_list => 'T',
2679 P_INTEREST_BATCH_ID => g_interest_batch_id,
2680 P_BATCH_STATUS => 'F',
2681 P_TRANSFERRED_STATUS => l_transferred_status,
2682 p_updated_by_program => 'ARLCSM',
2683 x_OBJECT_VERSION_NUMBER => x_object_version_number,
2684 x_return_status => x_return_status,
2685 x_msg_count => x_msg_count,
2686 x_msg_data => x_msg_data);
2687 ELSE
2688 AR_INTEREST_BATCHES_PKG.update_batch
2689 (p_init_msg_list => 'T',
2690 P_INTEREST_BATCH_ID => g_interest_batch_id,
2691 P_BATCH_STATUS => 'F',
2692 P_TRANSFERRED_STATUS => 'E',
2693 p_updated_by_program => 'ARLCSM',
2694 x_OBJECT_VERSION_NUMBER => x_object_version_number,
2695 x_return_status => x_return_status,
2696 x_msg_count => x_msg_count,
2697 x_msg_data => x_msg_data);
2698 log('Some documents are in error in in the batch :'||g_interest_batch_id);
2699 errbuf := 'Some documents are in error in in the batch :'||g_interest_batch_id;
2700 retcode := 1;
2701 END IF;
2702
2703 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2704 RAISE fnd_Api.g_exc_error;
2705 END IF;
2706
2707 outandlog( message =>' End at ' || to_char(SYSDATE, 'HH24:MI:SS') );
2708 outandlog( message =>'End create_late_charge for the batch:'||p_batch_id||' Please check log files' );
2709 EXCEPTION
2710 WHEN fnd_Api.g_exc_unexpected_error THEN
2711 fnd_msg_pub.count_and_get(
2712 p_encoded => fnd_api.g_false,
2713 p_count => x_msg_count,
2714 p_data => x_msg_data);
2715
2716 outandlog('Error: ' || FND_MESSAGE.GET);
2717 log('Batch en error ' || x_msg_data);
2718 errbuf := errbuf || logerror || x_msg_data;
2719 outandlog('Aborting concurrent program execution');
2720 FND_FILE.close;
2721 WHEN fnd_Api.g_exc_error THEN
2722 outandlog('Error: ' || FND_MESSAGE.GET);
2723 log('Batch en error ' || x_msg_data);
2724 errbuf := errbuf || logerror || x_msg_data;
2725 outandlog('Aborting concurrent program execution');
2726 FND_FILE.close;
2727
2728 WHEN OTHERS THEN
2729 outandlog('Error: ' || FND_MESSAGE.GET);
2730 log('SQL Error ' || SQLERRM);
2731 retcode := 2;
2732 errbuf := errbuf || logerror || SQLERRM;
2733 outandlog('Aborting concurrent program execution');
2734 FND_FILE.close;
2735 END;
2736
2737
2738 PROCEDURE create_late_charge_per_worker
2739 ( errbuf OUT NOCOPY VARCHAR2,
2740 retcode OUT NOCOPY VARCHAR2,
2741 p_batch_source_id IN NUMBER,
2742 p_batch_id IN NUMBER,
2743 p_worker_num IN NUMBER,
2744 p_gl_date IN DATE,
2745 p_cal_int_date IN DATE,
2746 p_api_bulk_size IN NUMBER)
2747 IS
2748 x_num_adj_created NUMBER;
2749 x_num_adj_error NUMBER;
2750 x_return_status VARCHAR2(10);
2751 x_msg_count NUMBER;
2752 x_msg_data VARCHAR2(2000);
2753 CURSOR c_err IS
2754 SELECT NULL
2755 FROM ar_interest_headers
2756 WHERE interest_batch_id = g_interest_batch_id
2757 AND process_status = 'E'
2758 AND display_flag = 'Y' -- Document generating a Late Charges Document
2759 AND p_worker_num = worker_num
2760 AND header_type IN ('INV','DM');
2761
2762 CURSOR c_one_doc_success IS
2763 SELECT 'Y'
2764 FROM ar_interest_headers
2765 WHERE interest_batch_id = g_interest_batch_id
2766 AND process_status = 'S'
2767 AND display_flag = 'Y' -- Document generating a Late Charges Document
2768 AND p_worker_num = worker_num;
2769
2770 CURSOR c_dm_exist IS
2771 SELECT 'Y'
2772 FROM ar_interest_headers
2773 WHERE interest_batch_id = g_interest_batch_id
2774 AND process_status = 'N'
2775 AND display_flag = 'N'; -- Document included in the calculation without generating late charges
2776
2777 l_dm_exist VARCHAR2(1);
2778 l_err_found VARCHAR2(1);
2779 l_success_found VARCHAR2(1);
2780 BEGIN
2781 log('create_late_charge_per_worker +');
2782 outandlog( message =>'create_late_charge per worker for the batch:'||p_batch_id );
2783 outandlog( message =>' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
2784 outandlog( message =>' p_worker_num :'||p_worker_num);
2785 outandlog( message =>' p_batch_source_id :'||p_batch_source_id);
2786 outandlog( message =>' p_cal_int_date :'||p_cal_int_date);
2787 outandlog( message =>' p_gl_date :'||p_gl_date);
2788 outandlog( message =>' p_api_bulk_size :'||p_api_bulk_size);
2789
2790
2791 DELETE FROM ar_late_charge_doc_gt;
2792
2793 UPDATE ar_interest_headers a
2794 SET a.process_message = ''
2795 WHERE a.interest_batch_id = p_batch_id
2796 AND a.worker_num = p_worker_num
2797 AND a.display_flag = 'Y'; -- Document generating a Late Charges Document
2798 -- no need to include Credit Debit items
2799 -- as no error messages will be tied to such an item
2800 UPDATE ar_interest_lines a
2801 SET a.process_message = ''
2802 WHERE a.interest_header_id IN
2803 (SELECT interest_header_id
2804 FROM ar_interest_headers
2805 WHERE interest_batch_id = p_batch_id
2806 AND display_flag = 'Y' -- Document generating a Late Charges Document
2807 AND worker_num = p_worker_num);
2808
2809 retcode := 0;
2810 x_return_status := fnd_api.g_ret_sts_success;
2811 log('1 create_charge_adj...');
2812
2813 create_charge_adj
2814 (p_batch_id => p_batch_id,
2815 p_worker_num => p_worker_num,
2816 p_gl_date => p_gl_date,
2817 p_cal_int_date => p_cal_int_date,
2818 p_api_bulk_size => p_api_bulk_size,
2819 x_num_adj_created => x_num_adj_created,
2820 x_num_adj_error => x_num_adj_error,
2821 x_return_status => x_return_status,
2822 x_msg_count => x_msg_count,
2823 x_msg_data => x_msg_data);
2824
2825 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2826 retcode := 1;
2827 errbuf := SUBSTRB('Some Charge Adjustments have failed,
2828 please verify the data in ar_interest_headers and lines tables with the batch_id :'||g_interest_batch_id,1,239);
2829 log(errbuf);
2830 END IF;
2831
2832 x_return_status := fnd_api.g_ret_sts_success;
2833
2834 log('2 create_charge_inv_dm...');
2835 create_charge_inv_dm
2836 (p_batch_source_id => p_batch_source_id,
2837 p_batch_id => p_batch_id,
2838 p_worker_num => p_worker_num,
2839 p_gl_date => p_gl_date,
2840 p_cal_int_date => p_cal_int_date,
2841 p_api_bulk_size => p_api_bulk_size,
2842 x_return_status => x_return_status,
2843 x_msg_count => x_msg_count,
2844 x_msg_data => x_msg_data);
2845
2846
2847 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2848 retcode := 1;
2849 errbuf := SUBSTRB('Some Charge invoice, debit memo have failed
2850 please verify the data in ar_interest_headers and lines tables with the batch_id :'||g_interest_batch_id,1,239);
2851 log(errbuf);
2852 ELSE
2853 OPEN c_err;
2854 FETCH c_err INTO l_err_found;
2855 IF c_err%FOUND THEN
2856 retcode := 1;
2857 errbuf := SUBSTRB('Some Charge invoice, debit memo have failed
2858 please verify the data in ar_interest_headers and lines tables with the batch_id :'||g_interest_batch_id,1,239);
2859 log(errbuf);
2860 END IF;
2861 CLOSE c_err;
2862
2863 --{ HYU CDI included in the calculation
2864 OPEN c_one_doc_success;
2865 FETCH c_one_doc_success INTO l_success_found;
2866 IF c_one_doc_success%NOTFOUND THEN
2867 l_success_found := 'N';
2868 l_dm_exist := 'N';
2869 END IF;
2870 CLOSE c_one_doc_success;
2871
2872 IF l_success_found = 'Y' THEN
2873 OPEN c_dm_exist;
2874 FETCH c_dm_exist INTO l_dm_exist;
2875 IF c_dm_exist%NOTFOUND THEN
2876 l_dm_exist := 'N';
2877 END IF;
2878 CLOSE c_dm_exist;
2879 END IF;
2880
2881 log('l_success_found: '||l_success_found);
2882 log('l_dm_exist : '||l_dm_exist);
2883
2884 IF l_dm_exist = 'Y' THEN
2885 --{ HYU CDI included in calculation without generating the late charges document
2886 UPDATE ar_payment_schedules
2887 SET last_charge_date = g_int_cal_date
2888 WHERE payment_schedule_id IN
2889 (SELECT l.PAYMENT_SCHEDULE_ID
2890 FROM ar_interest_headers h,
2891 ar_interest_lines l
2892 WHERE h.interest_batch_id = g_interest_batch_id
2893 AND h.display_flag = 'N' -- Document included in Late Charges Calculation
2894 AND h.process_status = 'N'
2895 AND h.interest_header_id = l.interest_header_id);
2896
2897 UPDATE ar_interest_headers
2898 SET process_status = 'S'
2899 WHERE interest_batch_id = g_interest_batch_id
2900 AND display_flag = 'N' -- Document included in Late Charges Calculation
2901 AND process_status = 'N';
2902 --}
2903 END IF;
2904
2905 END IF;
2906
2907 IF retcode = 0 THEN
2908 COMMIT;
2909 END IF;
2910
2911 write_exec_report;
2912
2913
2914 FND_FILE.close;
2915 EXCEPTION
2916 WHEN fnd_Api.g_exc_error THEN
2917 fnd_msg_pub.count_and_get(
2918 p_encoded => fnd_api.g_false,
2919 p_count => x_msg_count,
2920 p_data => x_msg_data);
2921 retcode := 1;
2922 outandlog('Error: ' || FND_MESSAGE.GET);
2923 log('Batch en error ' || x_msg_data);
2924 errbuf := errbuf || logerror || x_msg_data;
2925 outandlog('Aborting concurrent program execution');
2926 FND_FILE.close;
2927
2928 WHEN OTHERS THEN
2929 outandlog('Error: ' || FND_MESSAGE.GET);
2930 log('SQL Error ' || SQLERRM);
2931 retcode := 2;
2932 errbuf := errbuf || logerror || SQLERRM;
2933 outandlog('Aborting concurrent program execution');
2934 FND_FILE.close;
2935 END;
2936
2937
2938
2939
2940 PROCEDURE ordonancer_per_worker
2941 ( p_worker_num IN NUMBER,
2942 p_request_id IN NUMBER)
2943 IS
2944 CURSOR c IS
2945 SELECT MAX(b.request_id) request_id,
2946 b.interest_batch_id,
2947 b.org_id,
2948 b.gl_date,
2949 b.calculate_interest_to_date,
2950 s.late_charge_batch_source_id,
2951 lg.currency_code
2952 FROM ar_interest_batches_all b,
2953 ar_interest_headers_all h,
2954 ar_system_parameters_all s,
2955 gl_ledgers lg
2956 WHERE b.request_id = p_request_id
2957 AND b.org_id = s.org_id
2958 AND b.interest_batch_id = h.interest_batch_id
2959 AND h.worker_num = p_worker_num
2960 AND h.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
2961 AND lg.ledger_id = s.set_of_books_id
2962 GROUP BY
2963 b.interest_batch_id,
2964 b.org_id,
2965 b.gl_date,
2966 b.calculate_interest_to_date,
2967 s.late_charge_batch_source_id,
2968 lg.currency_code;
2969
2970 --{Check if the interest batch has some INV or DM before requiring the batch_source_id
2971 CURSOR ht(p_request_id IN NUMBER, p_worker_num IN NUMBER) IS
2972 SELECT NULL
2973 FROM ar_interest_headers_all
2974 WHERE request_id = p_request_id
2975 AND worker_num = p_worker_num
2976 AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
2977 AND header_type IN ('INV','DM');
2978 --}
2979 l_test VARCHAR2(1);
2980 l_request_id NUMBER;
2981 l_interest_batch_id NUMBER;
2982 l_org_id NUMBER;
2983 l_gl_date DATE;
2984 l_cal_int_date DATE;
2985 l_batch_source_id NUMBER;
2986 l_currency_code VARCHAR2(30);
2987 l_stop VARCHAR2(1) := 'N';
2988 x_num_adj_created NUMBER;
2989 x_num_adj_error NUMBER;
2990 x_return_status VARCHAR2(10);
2991 x_msg_count NUMBER;
2992 x_msg_data VARCHAR2(2000);
2993 i NUMBER := 0;
2994 errbuf VARCHAR2(240);
2995 retcode VARCHAR2(30);
2996
2997 BEGIN
2998 log('ordonancer_per_worker +');
2999 log(' ordonancer_per_worker executing for worker_num:'||p_worker_num);
3000 log(' ordonancer_per_worker executing for request_id:'||p_request_id);
3001
3002 IF p_worker_num IS NULL OR p_request_id IS NULL THEN
3003 log('The arguments p_worker_num and p_request_id are both required');
3004 ELSE
3005 OPEN c;
3006 LOOP
3007 FETCH c INTO
3008 l_request_id ,
3009 l_interest_batch_id ,
3010 l_org_id ,
3011 l_gl_date ,
3012 l_cal_int_date ,
3013 l_batch_source_id ,
3014 l_currency_code;
3015 EXIT WHEN c%NOTFOUND;
3016 l_stop := 'N';
3017 IF l_interest_batch_id IS NULL THEN
3018 log('no interest batch no found with the id:'||l_interest_batch_id);
3019 l_stop := 'Y';
3020 END IF;
3021 IF l_batch_source_id IS NULL THEN
3022 OPEN ht(p_worker_num, p_worker_num);
3023 IF ht%FOUND THEN
3024 log('no late batch charge batch source defined for the org_id:'||l_org_id);
3025 l_stop := 'Y';
3026 END IF;
3027 CLOSE ht;
3028 END IF;
3029 IF l_gl_date IS NULL OR l_cal_int_date IS NULL THEN
3030 log('no calculate interest to date or GL date for the interest batch id:'||l_interest_batch_id);
3031 l_stop := 'Y';
3032 END IF;
3033 IF l_currency_code IS NULL THEN
3034 log(' Issue with base currency for the org_id:'||l_org_id);
3035 l_stop := 'Y';
3036 END IF;
3037 IF l_stop = 'N' THEN
3038 log('ordonancer_per_worker executing the loop for :');
3039 log(' for org_id:'||l_org_id);
3040 log(' for currecny code :'||l_currency_code);
3041 log(' for interest_batch_id :'||l_interest_batch_id);
3042 log(' for batch_source_id :'||l_batch_source_id);
3043 log(' for responsibility :'||fnd_global.resp_id);
3044 log(' for user :'||fnd_global.user_id);
3045 log(' for application :'||fnd_global.resp_appl_id);
3046
3047 --set org context
3048 mo_global.init('AR');
3049 mo_global.set_policy_context('S',l_org_id);
3050 fnd_global.APPS_INITIALIZE(
3051 user_id => fnd_global.user_id,
3052 resp_id => fnd_global.resp_id,
3053 resp_appl_id => fnd_global.resp_appl_id);
3054
3055 g_func_curr := l_currency_code;
3056 g_interest_batch_id := l_interest_batch_id;
3057 g_org_id := l_org_id;
3058 g_BATCH_SOURCE_ID := l_batch_source_id;
3059
3060 fnd_msg_pub.initialize;
3061
3062 log('Calling create_late_charge_per_worker +');
3063
3064 create_late_charge_per_worker
3065 ( errbuf => errbuf,
3066 retcode => retcode,
3067 p_batch_source_id => l_batch_source_id,
3068 p_batch_id => l_interest_batch_id,
3069 p_worker_num => p_worker_num,
3070 p_gl_date => l_gl_date,
3071 p_cal_int_date => l_cal_int_date,
3072 p_api_bulk_size => 9000);
3073
3074 log('Calling create_late_charge_per_worker -');
3075 --{Message Stack
3076 IF retcode <> '0' THEN
3077 fnd_msg_pub.count_and_get(
3078 p_encoded => fnd_api.g_false,
3079 p_count => x_msg_count,
3080 p_data => x_msg_data);
3081 IF x_msg_count > 1 THEN
3082 i := 0;
3083 LOOP
3084 IF i < x_msg_count THEN
3085 i := i + 1 ;
3086 x_msg_data :=FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
3087 log(x_msg_data);
3088 ELSE
3089 EXIT;
3090 END IF;
3091 END LOOP;
3092 ELSIF x_msg_count = 1 THEN
3093 log(x_msg_data);
3094 END IF;
3095 END IF;
3096 --}
3097 COMMIT;
3098 log('End of ordonancer_per_worker executing the cuuernt loop.');
3099 END IF; -- End of l_stop = N
3100
3101 END LOOP;
3102 CLOSE c;
3103 END IF;
3104 log('ordonancer_per_worker -');
3105 END ordonancer_per_worker;
3106
3107 --{HYU Implemetation per worker
3108 PROCEDURE prepare_header_for_worker
3109 (p_interest_batch_id IN NUMBER,
3110 p_max_workers IN NUMBER,
3111 x_worker_list OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
3112 x_nb_doc_list OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
3113 x_return_status OUT NOCOPY VARCHAR2)
3114 IS
3115 CURSOR c IS
3116 SELECT worker_num,
3117 COUNT(interest_header_id)
3118 FROM ar_interest_headers
3119 WHERE interest_batch_id = p_interest_batch_id
3120 AND process_status = 'N'
3121 AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
3122 GROUP BY worker_num;
3123 i NUMBER;
3124 worker_cpt NUMBER;
3125 BEGIN
3126 log('prepare_header_for_worker +');
3127 log(' p_interest_batch_id '||p_interest_batch_id);
3128 log(' p_max_workers '||p_max_workers);
3129 x_return_status := fnd_api.g_ret_sts_success;
3130 IF p_interest_batch_id IS NULL THEN
3131 log('p_interest_batch_id required ');
3132 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3133 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'interest_batch_id' );
3134 FND_MSG_PUB.ADD;
3135 x_return_status := fnd_Api.G_RET_STS_ERROR;
3136 END IF;
3137
3138 IF NOT(p_max_workers > 0) THEN
3139 log('p_max_worker should be greater than 0');
3140 FND_MESSAGE.SET_NAME( 'AR', 'AR_NB_WORKER_GREATER_ZERO' );
3141 FND_MSG_PUB.ADD;
3142 x_return_status := fnd_Api.G_RET_STS_ERROR;
3143 END IF;
3144
3145 IF x_return_status = fnd_api.g_ret_sts_success THEN
3146
3147 log('updating worker_num');
3148 --{HYU CDI this update statement includes Credit Item
3149 UPDATE ar_interest_headers
3150 SET worker_num = mod(rownum, p_max_workers) + 1
3151 WHERE interest_batch_id = p_interest_batch_id
3152 AND process_status = 'N'
3153 AND display_flag = 'Y'; --HYU CDI only document generating the Late Charge s Doc
3154 --}
3155 log('open cursor c');
3156 OPEN c;
3157 FETCH c BULK COLLECT INTO
3158 x_worker_list,
3159 x_nb_doc_list;
3160 CLOSE c;
3161
3162 worker_cpt := x_worker_list.COUNT;
3163
3164 log('worker_cpt :'|| worker_cpt);
3165 IF NOT (worker_cpt > 0) THEN
3166 FND_MESSAGE.SET_NAME( 'AR', 'AR_NO_HEADER_TO_PROCESS' );
3167 FND_MSG_PUB.ADD;
3168 x_return_status := fnd_Api.G_RET_STS_ERROR;
3169 END IF;
3170 END IF;
3171 log('prepare_header_for_worker -');
3172 EXCEPTION
3173 WHEN OTHERS THEN
3174 outandlog('Error: ' || FND_MESSAGE.GET);
3175 log('SQL Error in prepare_header_for_worker' || SQLERRM);
3176 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
3177 END;
3178
3179
3180
3181 PROCEDURE create_late_charge_by_worker
3182 (errbuf OUT NOCOPY VARCHAR2,
3183 retcode OUT NOCOPY VARCHAR2,
3184 p_max_workers IN NUMBER DEFAULT 4,
3185 p_interval IN NUMBER DEFAULT 60,
3186 p_max_wait IN NUMBER DEFAULT 180,
3187 p_api_bulk_size IN NUMBER DEFAULT 9000,
3188 p_batch_source_id IN NUMBER,
3189 p_batch_id IN NUMBER )
3190 IS
3191 CURSOR c IS
3192 SELECT transferred_status,
3193 object_version_number,
3194 CALCULATE_INTEREST_TO_DATE,
3195 BATCH_STATUS,
3196 GL_DATE
3197 FROM ar_interest_batches
3198 WHERE interest_batch_id = g_interest_batch_id;
3199
3200 l_transferred_status VARCHAR2(1);
3201 x_object_version_number NUMBER;
3202 l_CALCULATE_INTEREST_TO_DATE DATE;
3203 l_BATCH_STATUS VARCHAR2(30);
3204 l_GL_DATE DATE;
3205
3206 CURSOR c_hdr IS
3207 SELECT interest_header_id
3208 FROM ar_interest_headers
3209 WHERE interest_batch_id = g_interest_batch_id
3210 AND process_status = 'N';
3211
3212 l_ihid NUMBER;
3213 l_empty_batch BOOLEAN := FALSE;
3214
3215 CURSOR c_batch_source IS
3216 SELECT NULL
3217 FROM ra_batch_sources
3218 WHERE BATCH_SOURCE_ID = p_batch_source_id;
3219
3220 CURSOR c_err IS
3221 SELECT NULL
3222 FROM ar_interest_headers
3223 WHERE interest_batch_id = g_interest_batch_id
3224 AND process_status <> 'S';
3225
3226 l_err VARCHAR2(1);
3227 l_test VARCHAR2(1);
3228 l_customer_site_use_id DBMS_SQL.NUMBER_TABLE;
3229 x_num_adj_created NUMBER;
3230 x_num_adj_error NUMBER;
3231 x_return_status VARCHAR2(10);
3232 x_msg_count NUMBER;
3233 x_msg_data VARCHAR2(2000);
3234 l_request_id NUMBER;
3235 l_exec_srs VARCHAR2(1) := 'N';
3236 l_need_wait VARCHAR2(1) := 'N';
3237 x_worker_list DBMS_SQL.NUMBER_TABLE;
3238 x_nb_doc_list DBMS_SQL.NUMBER_TABLE;
3239
3240 nothing_to_process EXCEPTION;
3241 BEGIN
3242 log('create_late_charge_by_worker +');
3243
3244 retcode := 0;
3245 g_interest_batch_id := p_batch_id;
3246
3247
3248 outandlog( message =>'create_late_charge for the batch:'||p_batch_id );
3249 outandlog( message =>' Starting at '|| to_char(SYSDATE, 'HH24:MI:SS') );
3250 outandlog( message =>' p_max_workers '|| p_max_workers);
3251 outandlog( message =>' p_interval '|| p_interval);
3252 outandlog( message =>' p_max_wait '|| p_max_wait);
3253 outandlog( message =>' p_api_bulk_size '|| p_api_bulk_size);
3254 outandlog( message =>' p_batch_source_id'|| p_batch_source_id);
3255 outandlog( message =>' p_batch_id '|| p_batch_id);
3256
3257
3258
3259 x_return_status := fnd_api.g_ret_sts_success;
3260
3261 IF p_batch_source_id IS NULL THEN
3262 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3263 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_source_id' );
3264 FND_MSG_PUB.ADD;
3265 retcode := 2;
3266 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
3267 ELSE
3268 OPEN c_batch_source;
3269 FETCH c_batch_source INTO l_test;
3270 IF c_batch_source%NOTFOUND THEN
3271 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
3272 FND_MESSAGE.SET_TOKEN( 'FK', 'batch_source_id' );
3273 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_source_id' );
3274 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ra_batch_sources');
3275 FND_MSG_PUB.ADD;
3276 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
3277 END IF;
3278 CLOSE c_batch_source;
3279 END IF;
3280
3281 OPEN c;
3282 FETCH c INTO l_transferred_status,
3283 x_object_version_number,
3284 l_CALCULATE_INTEREST_TO_DATE,
3285 l_BATCH_STATUS,
3286 l_GL_DATE;
3287 IF c%NOTFOUND THEN
3288 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
3289 FND_MESSAGE.SET_TOKEN( 'FK', 'batch_id' );
3290 FND_MESSAGE.SET_TOKEN( 'COLUMN',p_batch_id );
3291 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_interest_batches' );
3292 FND_MSG_PUB.ADD;
3293 x_return_status := FND_API.G_RET_STS_ERROR;
3294 retcode := 2;
3295 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
3296 outandlog('Interest Batch not found with the interest_batch_id :'||p_batch_id);
3297 ELSE
3298 IF l_BATCH_STATUS <> 'F' OR l_BATCH_STATUS IS NULL THEN
3299 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
3300 fnd_message.set_token('STATE', l_BATCH_STATUS);
3301 fnd_msg_pub.add;
3302 retcode := 2;
3303 x_return_status := fnd_api.g_ret_sts_error;
3304 outandlog('Interest Batch batch_status should be Final to import interest_batch_id :'||p_batch_id);
3305 END IF;
3306 IF l_CALCULATE_INTEREST_TO_DATE IS NULL THEN
3307 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3308 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'CALCULATE_INTEREST_TO_DATE' );
3309 FND_MSG_PUB.ADD;
3310 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
3311 outandlog('Interest Batch calculation interest to date is mandatory interest_batch_id :'||p_batch_id);
3312 END IF;
3313 IF l_GL_DATE IS NULL THEN
3314 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3315 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'GL_DATE' );
3316 FND_MSG_PUB.ADD;
3317 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
3318 outandlog('Interest Batch GL date is mandatory interest_batch_id :'||p_batch_id);
3319 END IF;
3320 IF l_transferred_status = 'S' THEN
3321 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
3322 fnd_message.set_token('STATE', l_BATCH_STATUS);
3323 fnd_msg_pub.add;
3324 retcode := 1;
3325 x_return_status := fnd_Api.G_RET_STS_UNEXP_ERROR;
3326 outandlog('Interest Batch already successfully transferred interest_batch_id :'||p_batch_id);
3327 ELSIF l_transferred_status = 'E' THEN
3328 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
3329 fnd_message.set_token('STATE', l_BATCH_STATUS);
3330 fnd_msg_pub.add;
3331 retcode := 1;
3332 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
3333 outandlog('Interest Batch is in Error, please fix it before submitting interest_batch_id :'||p_batch_id);
3334 ELSIF l_transferred_status = 'P' THEN
3335 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
3336 fnd_message.set_token('STATE', l_BATCH_STATUS);
3337 fnd_msg_pub.add;
3338 retcode := 1;
3339 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
3340 outandlog('Interest Batch is in process, can not resubmit interest_batch_id :'||p_batch_id);
3341 ELSIF l_transferred_status = 'N' THEN
3342 outandlog('Processing the batch interest_batch_id :'||p_batch_id);
3343 ELSIF l_transferred_status IS NOT NULL THEN
3344 fnd_message.set_name('AR', 'AR_INT_BATCH_STATUS');
3345 fnd_message.set_token('STATE', l_BATCH_STATUS);
3346 fnd_msg_pub.add;
3347 retcode := 2;
3348 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
3349 outandlog('Interest Batch transferred flag should be in (NULL,S,E) no other value permitted interest_batch_id :'||p_batch_id);
3350 END IF;
3351 END IF;
3352 CLOSE c;
3353
3354 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3355 RAISE fnd_Api.g_exc_unexpected_error;
3356 END IF;
3357
3358 OPEN c_hdr;
3359 FETCH c_hdr INTO l_ihid;
3360 IF c_hdr%NOTFOUND THEN
3361 l_empty_batch := TRUE;
3362 END IF;
3363 CLOSE c_hdr;
3364 IF l_empty_batch THEN
3365 RAISE nothing_to_process;
3366 END IF;
3367
3368
3369 -- Set the new worker_num on interest headers
3370 prepare_header_for_worker
3371 (p_interest_batch_id => g_interest_batch_id,
3372 p_max_workers => p_max_workers,
3373 x_worker_list => x_worker_list,
3374 x_nb_doc_list => x_nb_doc_list,
3375 x_return_status => x_return_status);
3376
3377
3378 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3379 retcode := 2;
3380 RAISE fnd_Api.g_exc_unexpected_error;
3381 END IF;
3382
3383 log('Updating the batch status to Pending to start the process...');
3384 AR_INTEREST_BATCHES_PKG.update_batch
3385 (p_init_msg_list => 'T',
3386 P_INTEREST_BATCH_ID => g_interest_batch_id,
3387 P_BATCH_STATUS => 'F',
3388 P_TRANSFERRED_STATUS => 'P',
3389 p_updated_by_program => 'ARLCSM',
3390 x_OBJECT_VERSION_NUMBER => x_object_version_number,
3391 x_return_status => x_return_status,
3392 x_msg_count => x_msg_count,
3393 x_msg_data => x_msg_data);
3394
3395 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3396 RAISE fnd_Api.g_exc_error;
3397 END IF;
3398
3399 FOR i IN x_worker_list.FIRST..x_worker_list.LAST LOOP
3400 submit_late_charge_worker
3401 (p_batch_id => p_batch_id,
3402 p_batch_source_id => p_batch_source_id,
3403 p_gl_date => l_GL_DATE,
3404 p_cal_int_date => l_CALCULATE_INTEREST_TO_DATE,
3405 p_api_bulk_size => p_api_bulk_size,
3406 p_worker_num => x_worker_list(i),
3407 x_out_request_id => l_request_id);
3408 IF l_need_wait = 'N' AND l_request_id > 0 THEN
3409 l_need_wait := 'Y';
3410 END IF;
3411 END LOOP;
3412
3413 IF l_need_wait = 'Y' THEN
3414 wait_for_end_subreq(
3415 p_interval => p_interval
3416 ,p_max_wait => p_max_wait
3417 ,p_sub_name => 'ARLCPW' );
3418 END IF;
3419
3420 log( message => 'Updating interest batch status to :'||l_batch_status||' for batch_id :'||g_interest_batch_id);
3421
3422 get_status_for_sub_process
3423 (p_sub_name => 'ARLCPW',
3424 x_status => l_transferred_status);
3425
3426 -- UPDATE ar_interest_batches
3427 -- SET TRANSFERRED_status = l_transferred_status,
3428 -- object_version_number = x_object_version_number + 1
3429 -- WHERE interest_batch_id = g_interest_batch_id;
3430
3431 OPEN c_err;
3432 FETCH c_err INTO l_err;
3433 IF c_err%NOTFOUND THEN
3434 AR_INTEREST_BATCHES_PKG.update_batch
3435 (p_init_msg_list => 'T',
3436 P_INTEREST_BATCH_ID => g_interest_batch_id,
3437 P_BATCH_STATUS => 'F',
3438 P_TRANSFERRED_status => l_transferred_status,
3439 p_updated_by_program => 'ARLCSM',
3440 x_OBJECT_VERSION_NUMBER => x_object_version_number,
3441 x_return_status => x_return_status,
3442 x_msg_count => x_msg_count,
3443 x_msg_data => x_msg_data);
3444 ELSE
3445 AR_INTEREST_BATCHES_PKG.update_batch
3446 (p_init_msg_list => 'T',
3447 P_INTEREST_BATCH_ID => g_interest_batch_id,
3448 P_BATCH_STATUS => 'F',
3449 P_TRANSFERRED_status => 'E',
3450 p_updated_by_program => 'ARLCSM',
3451 x_OBJECT_VERSION_NUMBER => x_object_version_number,
3452 x_return_status => x_return_status,
3453 x_msg_count => x_msg_count,
3454 x_msg_data => x_msg_data);
3455 log('Some documents are in error in in the batch :'||g_interest_batch_id);
3456 errbuf := 'Some documents are in error in in the batch :'||g_interest_batch_id;
3457 retcode := 1;
3458 END IF;
3459
3460 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3461 RAISE fnd_Api.g_exc_error;
3462 END IF;
3463
3464 outandlog( message =>' End at ' || to_char(SYSDATE, 'HH24:MI:SS') );
3465 outandlog( message =>'End create_late_charge_by_worker for the batch:'||p_batch_id||' Please check log files' );
3466 EXCEPTION
3467 WHEN nothing_to_process THEN
3468 outandlog( message =>' Empty batch' );
3469 AR_INTEREST_BATCHES_PKG.update_batch
3470 (p_init_msg_list => 'T',
3471 P_INTEREST_BATCH_ID => g_interest_batch_id,
3472 P_BATCH_STATUS => 'F',
3473 P_TRANSFERRED_status => 'S',
3474 p_updated_by_program => 'ARLCSM',
3475 x_OBJECT_VERSION_NUMBER => x_object_version_number,
3476 x_return_status => x_return_status,
3477 x_msg_count => x_msg_count,
3478 x_msg_data => x_msg_data);
3479
3480 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3481 retcode := 2;
3482 fnd_msg_pub.count_and_get(
3483 p_encoded => fnd_api.g_false,
3484 p_count => x_msg_count,
3485 p_data => x_msg_data);
3486
3487 outandlog('Error: ' || FND_MESSAGE.GET);
3488 log('Batch en error ' || x_msg_data);
3489 errbuf := errbuf || logerror || x_msg_data;
3490 outandlog('Aborting concurrent program execution');
3491 END IF;
3492
3493 FND_FILE.close;
3494
3495 WHEN fnd_Api.g_exc_unexpected_error THEN
3496 fnd_msg_pub.count_and_get(
3497 p_encoded => fnd_api.g_false,
3498 p_count => x_msg_count,
3499 p_data => x_msg_data);
3500
3501 outandlog('Error: ' || FND_MESSAGE.GET);
3502 log('Batch en error ' || x_msg_data);
3503 errbuf := errbuf || logerror || x_msg_data;
3504 outandlog('Aborting concurrent program execution');
3505 FND_FILE.close;
3506 WHEN fnd_Api.g_exc_error THEN
3507 outandlog('Error: ' || FND_MESSAGE.GET);
3508 log('Batch en error ' || x_msg_data);
3509 errbuf := errbuf || logerror || x_msg_data;
3510 outandlog('Aborting concurrent program execution');
3511 FND_FILE.close;
3512
3513 WHEN OTHERS THEN
3514 outandlog('Error: ' || FND_MESSAGE.GET);
3515 log('SQL Error ' || SQLERRM);
3516 retcode := 2;
3517 errbuf := errbuf || logerror || SQLERRM;
3518 outandlog('Aborting concurrent program execution');
3519 FND_FILE.close;
3520 END;
3521
3522 PROCEDURE submit_late_charge_worker
3523 (p_batch_id IN NUMBER,
3524 p_batch_source_id IN NUMBER,
3525 p_gl_date IN DATE,
3526 p_cal_int_date IN DATE,
3527 p_api_bulk_size IN NUMBER,
3528 p_worker_num IN NUMBER,
3529 x_out_request_id OUT NOCOPY NUMBER)
3530 IS
3531 lc_sub_pb EXCEPTION;
3532 BEGIN
3533
3534 FND_REQUEST.SET_ORG_ID(g_org_id);
3535 x_out_request_id := FND_REQUEST.SUBMIT_REQUEST(
3536 application=>'AR',
3537 program =>'ARLCPW',
3538 sub_request=>FALSE,
3539 argument1 => p_batch_source_id,
3540 argument2 => p_batch_id,
3541 argument3 => p_worker_num,
3542 argument4 => p_gl_date,
3543 argument5 => p_cal_int_date,
3544 argument6 => p_api_bulk_size );
3545 IF x_out_request_id <> 0 THEN
3546 INSERT INTO ar_submission_ctrl_gt
3547 (worker_id , --p_batch_source_id
3548 batch_id , --
3549 script_name , --script_name
3550 status , --
3551 order_num , --order helper number
3552 request_id , --request_id
3553 table_name ) --table_name
3554 VALUES
3555 (p_batch_source_id,
3556 NULL,
3557 'ARLCPW',
3558 'SUBMITTED',
3559 1,
3560 x_out_request_id,
3561 'ARLC');
3562 COMMIT;
3563 ELSE
3564 RAISE lc_sub_pb;
3565 END IF;
3566 EXCEPTION
3567 WHEN lc_sub_pb THEN
3568 log(logerror(SQLERRM));
3569 WHEN OTHERS THEN
3570 log(logerror(SQLERRM));
3571 END;
3572
3573 --}
3574
3575 PROCEDURE init IS
3576 CURSOR c_initial IS
3577 SELECT lg.currency_code,
3578 sysp.org_id,
3579 sysp.LATE_CHARGE_BATCH_SOURCE_ID
3580 FROM ar_system_parameters sysp,
3581 gl_ledgers lg
3582 WHERE lg.ledger_id = sysp.set_of_books_id;
3583 BEGIN
3584
3585 OPEN c_initial;
3586 FETCH c_initial INTO
3587 g_func_curr, g_org_id, g_BATCH_SOURCE_ID;
3588 IF c_initial%NOTFOUND THEN
3589 RAISE NO_DATA_FOUND;
3590 END IF;
3591 CLOSE c_initial;
3592 END;
3593
3594
3595 BEGIN
3596
3597 init;
3598
3599 EXCEPTION
3600 WHEN NO_DATA_FOUND THEN
3601 log('No system parameter!!!');
3602 RAISE;
3603
3604 END;