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