[Home] [Help]
PACKAGE BODY: APPS.FUN_NET_ARAP_PKG
Source
1 PACKAGE BODY FUN_NET_ARAP_PKG AS
2 /* $Header: funnttxb.pls 120.65.12010000.11 2009/02/10 12:58:53 srampure ship $ */
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER;
7 g_proc_level NUMBER;
8 g_event_level NUMBER;
9 g_excep_level NUMBER;
10 g_error_level NUMBER;
11 g_unexp_level NUMBER;
12 g_path VARCHAR2(100);
13
14 --===========================FND_LOG.END=======================================
15
16
17 --Declare all required global variables
18 TYPE batch_rec IS RECORD
19 (batch_id fun_net_batches_all.batch_id%TYPE,
20 agreement_id fun_net_agreements_all.agreement_id%TYPE,
21 currency gl_ledgers.currency_code%TYPE);
22
23 TYPE batch_table IS TABLE OF batch_rec INDEX BY BINARY_INTEGER;
24
25 SUBTYPE batch_details_rec IS fun_net_batches_all%ROWTYPE;
26
27 g_batch_details batch_details_rec;
28 g_batch_list batch_table;
29 g_idx BINARY_INTEGER;
30
31 -- TYPE txnCurType IS REF CURSOR;
32 TYPE pymt_sch_rec IS RECORD
33 (invoice_id ap_invoices_all.invoice_id%TYPE,
34 min_payment_num ap_payment_schedules_all.payment_num%TYPE,
35 max_payment_num ap_payment_schedules_all.payment_num%TYPE);
36
37 TYPE pymt_sch_tab IS TABLE OF pymt_sch_rec INDEX BY BINARY_INTEGER;
38
39 TYPE txnRecType IS RECORD
40 (
41 customer_trx_id ra_customer_trx.customer_trx_id%TYPE,
42 exchange_rate ra_customer_trx.exchange_rate%TYPE,
43 amt_remaining ar_payment_schedules.amount_due_remaining%TYPE,
44 txn_amt fun_net_ar_txns.transaction_amt%TYPE,
45 open_amt fun_net_ar_txns.open_amt%TYPE,
46 txn_curr_open_amt fun_net_ar_txns.txn_curr_open_amt%TYPE,
47 txn_curr_amt ra_customer_trx_lines.extended_amount%TYPE,
48 txn_curr_net_amt fun_net_ar_txns.txn_curr_net_amt%TYPE,
49 net_amt fun_net_ar_txns.netted_amt%TYPE,
50 invoice_currency_code ra_customer_trx.invoice_currency_code%TYPE,
51 payment_type_code ar_receipt_methods.payment_type_code%TYPE);
52
53 TYPE txnTblType IS TABLE OF txnRecType INDEX BY BINARY_INTEGER;
54 TYPE arReceiptRecType IS RECORD
55 (
56 currency_code fun_net_batches.batch_currency%TYPE,
57 exchange_rate_type fun_net_batches.exchange_rate_type%TYPE,
58 exchange_rate ra_customer_trx.exchange_rate%TYPE,
59 exchange_rate_date ra_customer_trx.exchange_date%TYPE,
60 amount fun_net_batches.total_netted_amt%TYPE,
61 factor_discount_amount fun_net_batches.total_netted_amt%TYPE,
62 receipt_number ar_cash_receipts.receipt_number%TYPE,
63 receipt_date ar_cash_receipts.receipt_date%TYPE,
64 gl_date ar_cash_receipts.receipt_date%TYPE,
65 customer_id hz_cust_accounts.cust_account_id%TYPE,
66 customer_site_use_id hz_cust_site_uses.site_use_id%TYPE,
67 remittance_bank_account_id fun_net_agreements.bank_account_id%TYPE,
68 remittance_bank_account_num ce_bank_accounts.bank_account_num%TYPE,
69 remittance_bank_account_name ce_bank_accounts.bank_account_name%TYPE ,
70 receipt_method_id ar_receipt_methods.receipt_method_id%TYPE,
71 org_id fun_net_batches.org_id%TYPE
72 );
73
74 g_batch_id fun_net_batches_all.batch_id%TYPE;
75 g_net_currency_rule fun_net_agreements_all.net_currency_rule_code%TYPE;
76 g_net_order_rule fun_net_agreements_all.net_order_rule_code%TYPE;
77 g_net_balance_rule fun_net_agreements_all.net_balance_rule_code%TYPE;
78 g_net_currency fun_net_agreements_all.net_currency_code%TYPE;
79 g_bank_account_id fun_net_agreements_all.bank_account_id%TYPE;
80 g_agreement_id fun_net_agreements_all.agreement_id%TYPE;
81 g_agr_start_date fun_net_agreements_all.agreement_start_date%TYPE;
82 g_agr_end_date fun_net_agreements_all.agreement_end_date%TYPE;
83 g_func_currency gl_ledgers.currency_code%TYPE;
84 g_shikyu_rule fun_net_agreements_all.shikyu_rule_code%TYPE;
85 g_days_past_due fun_net_agreements_all.days_past_due%TYPE;
86 g_sel_past_due_flag fun_net_agreements_all.sel_rec_past_due_txns_flag%TYPE;
87
88 g_agreement_count NUMBER;
89 g_currency_count NUMBER;
90
91 g_user_id NUMBER;
92 g_login_id NUMBER;
93 g_today DATE;
94
95
96 PROCEDURE get_functional_currency IS
97 l_ledger_id gl_ledgers.ledger_id%TYPE;
98 l_ledger_name gl_ledgers.name%TYPE;
99 BEGIN
100 MO_Utils.Get_Ledger_Info(
101 g_batch_details.org_id,
102 l_ledger_id,
103 l_ledger_name);
104
105 SELECT currency_code
106 INTO g_func_currency
107 FROM gl_ledgers
108 WHERE ledger_id = l_ledger_id;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 NULL;
113 END get_functional_currency;
114
115
116 FUNCTION get_batch_details RETURN BOOLEAN IS
117
118 CURSOR c_get_batch_details IS
119 SELECT batch_id,
120 object_version_number,
121 agreement_id,
122 batch_name,
123 batch_number,
124 review_netting_batch_flag,
125 batch_currency,
126 batch_status_code,
127 total_netted_amt,
128 transaction_due_date,
129 settlement_date,
130 response_date,
131 exchange_rate_type,
132 exchange_rate,
133 gl_date,
134 org_id,
135 attribute_category,
136 attribute1,
137 attribute2,
138 attribute3,
139 attribute4,
140 attribute5,
141 attribute6,
142 attribute7,
143 attribute8,
144 attribute9,
145 attribute10,
146 attribute11,
147 attribute12,
148 attribute13,
149 attribute14,
150 attribute15,
151 attribute16,
152 attribute17,
153 attribute18,
154 attribute19,
155 attribute20,
156 checkrun_id
157 FROM fun_net_batches_all
158 WHERE batch_id = g_batch_id;
159
160 BEGIN
161
162 --Get all the batch details into global variables from the table fun_net_batches_all
163 OPEN c_get_batch_details;
164 FETCH c_get_batch_details
165 INTO g_batch_details.batch_id,
166 g_batch_details.object_version_number,
167 g_batch_details.agreement_id,
168 g_batch_details.batch_name,
169 g_batch_details.batch_number,
170 g_batch_details.review_netting_batch_flag,
171 g_batch_details.batch_currency,
172 g_batch_details.batch_status_code,
173 g_batch_details.total_netted_amt,
174 g_batch_details.transaction_due_date,
175 g_batch_details.settlement_date,
176 g_batch_details.response_date,
177 g_batch_details.exchange_rate_type,
178 g_batch_details.exchange_rate,
179 g_batch_details.gl_date,
180 g_batch_details.org_id,
181 g_batch_details.attribute_category,
182 g_batch_details.attribute1,
183 g_batch_details.attribute2,
184 g_batch_details.attribute3,
185 g_batch_details.attribute4,
186 g_batch_details.attribute5,
187 g_batch_details.attribute6,
188 g_batch_details.attribute7,
189 g_batch_details.attribute8,
190 g_batch_details.attribute9,
191 g_batch_details.attribute10,
192 g_batch_details.attribute11,
193 g_batch_details.attribute12,
194 g_batch_details.attribute13,
195 g_batch_details.attribute14,
196 g_batch_details.attribute15,
197 g_batch_details.attribute16,
198 g_batch_details.attribute17,
199 g_batch_details.attribute18,
200 g_batch_details.attribute19,
201 g_batch_details.attribute20,
202 g_batch_details.checkrun_id;
203
204 IF c_get_batch_details%NOTFOUND THEN
205 CLOSE c_get_batch_details;
206 RETURN FALSE;
207 ELSE
208 CLOSE c_get_batch_details;
209 g_batch_details.transaction_due_date := TRUNC(g_batch_details.transaction_due_date);
210 RETURN TRUE;
211 END IF;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 IF c_get_batch_details%ISOPEN THEN
216 CLOSE c_get_batch_details;
217 END IF;
218 RETURN FALSE;
219 END get_batch_details;
220
221
222 FUNCTION get_agreement_details RETURN BOOLEAN IS
223
224 CURSOR c_get_agreement_details IS
225 SELECT net_currency_rule_code,
226 net_order_rule_code,
227 net_balance_rule_code,
228 bank_account_id,
229 net_currency_code,
230 agreement_start_date,
231 agreement_end_date,
232 nvl(days_past_due,0),
233 sel_rec_past_due_txns_flag
234 FROM fun_net_agreements
235 WHERE agreement_id = g_agreement_id;
236
237 BEGIN
238
239 -- Get all the agreement details like netting_currency_rule, netting_order_rule, --etc, into global variables from the table fun_net_agreements_all
240 OPEN c_get_agreement_details;
241 FETCH c_get_agreement_details
242 INTO g_net_currency_rule,
243 g_net_order_rule,
244 g_net_balance_rule,
245 g_bank_account_id,
246 g_net_currency,
247 g_agr_start_date,
248 g_agr_end_date,
249 g_days_past_due,
250 g_sel_past_due_flag;
251 IF c_get_agreement_details%NOTFOUND THEN
252 CLOSE c_get_agreement_details;
253 RETURN FALSE;
254 ELSE
255 CLOSE c_get_agreement_details;
256 IF g_agr_end_date IS NULL THEN
257 g_agr_end_date := to_date('31/12/9999','DD/MM/YYYY');
258 END IF;
259 g_agr_start_date := TRUNC(g_agr_start_date);
260 g_agr_end_date := TRUNC(g_agr_end_date);
261 END IF;
262 RETURN TRUE;
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 IF c_get_agreement_details%ISOPEN THEN
267 CLOSE c_get_agreement_details;
268 END IF;
269 RETURN FALSE;
270 END get_agreement_details;
271
272
273 FUNCTION update_batch_status(p_status VARCHAR2) RETURN BOOLEAN IS
274 l_path VARCHAR2(100);
275 BEGIN
276 l_path := g_path || 'Update_Batch_Status';
277 /* Check for mandatory parameters */
278 IF p_status IS NULL THEN
279 RETURN FALSE;
280 ELSE
281 FUN_NET_BATCHES_PKG.Update_Row
282 (x_batch_id => g_batch_id,
283 x_batch_status_code => p_status);
284 END IF;
285 fun_net_util.Log_String(g_state_level,l_path,'Successfully updated batch status');
286 RETURN TRUE;
287
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION: No data found');
291 RETURN FALSE;
292 WHEN OTHERS THEN
293 fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION: '||sqlerrm);
294 RETURN FALSE;
295 END update_batch_status;
296
297
298 FUNCTION prepare_ar_transactions RETURN BOOLEAN IS
299 l_trx_select_clause VARCHAR2(2000);
300 l_trx_from_clause VARCHAR2(2000);
301 l_trx_where_clause VARCHAR2(3000);
302 l_trx_group_by_clause VARCHAR2(2000);
303 l_trx_order_by_clause VARCHAR2(2000);
304 l_sql_stmt VARCHAR2(6000);
305
306 TYPE trxCurTyp IS REF CURSOR;
307 trx_cur trxCurTyp;
308 l_currency_code VARCHAR2(15);
309 l_path VARCHAR2(100);
310 BEGIN
311 l_path := g_path || 'Prepare_AR_Transactions';
312 fun_net_util.Log_String(g_proc_level,l_path,'Preparing AR Transactions');
313
314 -- Build Select Clause --
315 l_trx_select_clause :=
316 'SELECT rct.customer_trx_id,
317 fun_net_arap_pkg.Calculate_ar_trx_amt(rct.customer_trx_id) transaction_amount,
318 sum(arps.amount_due_remaining) AS open_amount ' ;
319 fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
320
321 l_trx_from_clause :=
322 ' FROM ra_customer_trx_all rct,
323 ar_payment_schedules_all arps,
324 ra_cust_trx_types_all rctt,
325 fun_net_customers_all fnc ';
326 fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
327
328 -- Build the WHERE clause --
329 /* Only select the transactions where the due date is on or before the Tnx due date in the batch */
330 /* Only select transactions where the tnx due date is between the start and end date of the agreement */
331 /* Select only completed AR Transactions */
332 /* Past Due Transactions */
333 /* Do not select transactions that have prepayments ie : Preparyment flag = 'Y' */
334 /* Disputed transactions should not be selected */
335 /* Only select the transactions whose invoice types have been defined in the agreement */
336 /* Do not select transactions where the payment type code = 'CREDIT CARD' */
337 /* Do not select transactions that are already selected in another batch that is not in Status Complete */
338 l_trx_where_clause :=
339 ' WHERE arps.customer_trx_id = rct.customer_trx_id
340 AND TRUNC(arps.due_date) <= trunc(:v_transaction_due_date)
341 AND arps.status = ''OP''
342 AND rct.complete_flag = ''Y''
343 AND TRUNC(arps.due_date) BETWEEN trunc(:v_agr_start_date)
344 AND trunc(:v_agr_end_date)
345 AND NVL(rct.prepayment_flag, ''N'') = ''N''
346 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
347 AND NVL(arpt_sql_func_util.get_dispute_amount
348 (rct.customer_trx_id,rctt.type,rctt.accounting_affect_flag),0) = 0
349 AND arpt_sql_func_util.get_dispute_date
350 (rct.customer_trx_id,rctt.type,rctt.accounting_affect_flag) IS NULL
351 AND rct.bill_to_customer_id = fnc.cust_account_id
352 AND rct.bill_to_site_use_id = nvl(fnc.cust_site_use_id, rct.bill_to_site_use_id)
353 AND fnc.agreement_id = :v_agreement_id1
354 AND EXISTS
355 (SELECT ''X''
356 FROM fun_net_ar_trx_types_all fnar
357 WHERE fnar.cust_trx_type_id = rct.cust_trx_type_id
358 AND fnar.agreement_id = :v_agreement_id2)
359 AND NOT EXISTS
360 (SELECT ''X''
361 FROM ar_receipt_methods arm
362 WHERE arm.receipt_method_id = rct.receipt_method_id
363 AND arm.payment_type_code = ''CREDIT_CARD'')
364 AND NOT EXISTS
365 (SELECT ''X''
366 FROM fun_net_ar_txns_all fnar,
367 fun_net_batches_all fnba
368 WHERE Fnar.customer_trx_id = rct.customer_trx_id
369 AND fnar.batch_id = fnba.batch_id
370 AND fnba.batch_status_code <> ''CANCELLED''
371 AND fnba.batch_status_code <> ''COMPLETE''
372 AND fnba.batch_status_code <> ''REVERSED'')
373 AND fnc.org_id = :v_org_id1
374 AND rct.org_id = fnc.org_id
375 AND rctt.org_id = fnc.org_id
376 AND ((:v_sel_past_due_date1=''N'') OR (:v_sel_past_due_date2=''Y'' AND
377 TRUNC(arps.due_date) + nvl(:v_days_past_due,0) < trunc(sysdate) ))
378 AND arps.org_id = fnc.org_id';
379 fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
380
381 /* Build the Group by clause */
382 l_trx_group_by_clause :=
383 ' GROUP BY rct.customer_trx_id,
384 fnc.cust_priority ';
385
386 IF g_net_order_rule = 'TRX_DATE' then
387 l_trx_group_by_clause := l_trx_group_by_clause ||
388 ',' ||
389 ' rct.trx_date ';
390 END IF;
391
392 /* l_trx_group_by_clause := l_trx_group_by_clause ||','||
393 CASE g_net_order_rule
394 WHEN 'DUEDATE_OLD' THEN 'arps.due_date'
395 WHEN 'DUEDATE_RECENT' THEN 'arps.due_date'
396 WHEN 'AMOUNT_ASCENDING' THEN 'open_amount'
397 WHEN 'AMOUNT_DESCENDING' THEN 'open_amount'
398 END; */
399
400 fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
401
402 /* Build the Order by clause */
403 --Order by the Customer Priority .If all the customers have the same priority =1,then use the netting order rule.
404 l_trx_order_by_clause :=
405 ' ORDER BY fnc.cust_priority ';
406
407 --Set the order by clause for netting order
408 l_trx_order_by_clause := l_trx_order_by_clause ||','||
409 CASE g_net_order_rule
410 WHEN 'DUEDATE_OLD' THEN 'min(arps.due_date) asc'
411 WHEN 'DUEDATE_RECENT' THEN 'min(arps.due_date) desc'
412 WHEN 'AMOUNT_ASCENDING' THEN 'sum(arps.amount_due_remaining) asc'
413 WHEN 'AMOUNT_DESCENDING' THEN 'sum(arps.amount_due_remaining) desc'
414 WHEN 'TRX_DATE' THEN 'rct.trx_date asc'
415 END;
416 fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
417
418 /* Select the transactions with currency specified in the agreement if the netting currency rule is Single currency */
419 fun_net_util.Log_String(g_state_level,l_path,'currency rule :'||g_net_currency_rule);
420
421 IF g_net_currency_rule = 'SINGLE_CURRENCY' THEN
422
423 l_trx_where_clause := l_trx_where_clause || ' AND RCT.INVOICE_CURRENCY_CODE = :v_currency' ;
424
425 fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
426 fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
427 fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
428 fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
429 fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
430
431 l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause ;
432 insert_transactions(l_sql_stmt , g_net_currency, 'AR');
433
434
435 ELSIF g_net_currency_rule = 'ACCOUNTING_CURRENCY' THEN
436
437 --Select all the invoices irrespective of the currency code and call GL API to convert the amount from the invoice currency to the accounting currency
438
439 fun_net_util.Log_String(g_state_level,l_path,'g_batch_details.exchange_rate_type :' ||g_batch_details.exchange_rate_type);
440
441 l_trx_select_clause :=
442 'SELECT rct.customer_trx_id,
443 fun_net_arap_pkg.Calculate_ar_trx_amt(rct.customer_trx_id) transaction_amount,
444 gl_currency_api.convert_amount_sql(rct.invoice_currency_code,
445 :batch_currency,
446 trunc(:SETTLEMENT_DATE),
447 :exchange_rate_type,
448 sum(arps.amount_due_remaining)) AS open_amount,
449 sum(arps.amount_due_remaining) AS txn_curr_open_amt ' ;
450
451 l_trx_group_by_clause := l_trx_group_by_clause ||
452 ',' ||
453 ' rct.invoice_currency_code ';
454
455 fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
456 fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
457 fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
458 fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
459 fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
460
461 l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
462 fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
463 fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,2001,2000));
464 insert_transactions(l_sql_stmt , g_func_currency, 'AR');
465
466 ELSIF g_net_currency_rule = 'WITHIN_CURRENCY' THEN
467
468 l_sql_stmt := 'SELECT DISTINCT RCT.INVOICE_CURRENCY_CODE CURRENCY_CODE ' || l_trx_from_clause || l_trx_where_clause ;
469 --Select the currencies in a cursor
470 l_trx_where_clause := l_trx_where_clause || ' AND RCT.INVOICE_CURRENCY_CODE = :v_currency ';
471 OPEN trx_cur FOR l_sql_stmt USING g_batch_details.transaction_due_date,
472 g_agr_start_date,
473 g_agr_end_date,
474 g_agreement_id,
475 g_agreement_id,
476 g_batch_details.org_id,
477 g_sel_past_due_flag,
478 g_sel_past_due_flag,
479 g_days_past_due;
480 LOOP
481 FETCH trx_cur INTO l_currency_code ;
482
483
484 EXIT WHEN trx_cur%NOTFOUND;
485 --Set the select where clause to select all transactions belonging to the cursor currency, as shown above in the first if condition.
486 g_currency_count := g_currency_count + 1;
487
488 fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
489 fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
490 fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
491 fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
492 fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
493
494 l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
495 insert_transactions (l_sql_stmt , l_currency_code, 'AR');
496
497
498 END LOOP;
499 CLOSE trx_cur;
500
501 END IF; -- Currency
502
503 RETURN TRUE;
504 EXCEPTION
505 WHEN OTHERS THEN
506 fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
507 RETURN FALSE;
508 END prepare_ar_transactions;
509
510
511
512 FUNCTION prepare_ap_transactions RETURN BOOLEAN IS
513
514 l_inv_select_clause VARCHAR2(2000);
515 l_inv_from_clause VARCHAR2(2000);
516 l_inv_where_clause VARCHAR2(2000);
517 l_inv_group_by_clause VARCHAR2(2000);
518 l_inv_order_by_clause VARCHAR2(2000);
519 l_sql_stmt VARCHAR2(4000);
520
521 TYPE InvCurTyp IS REF CURSOR;
522 inv_cur InvCurTyp;
523 l_currency_code VARCHAR2(15);
524 l_path VARCHAR2(100);
525 BEGIN
526 l_path := g_path || 'Prepare_AP_Transactions';
527 fun_net_util.Log_String(g_proc_level,l_path,'Preparing AP Transactions');
528
529 /* Build the select clause */
530 l_inv_select_clause :=
531 -- Select all ap_invoices based on the batch id
532 'SELECT
533 api.invoice_id,
534 api.invoice_amount AS transaction_amount,
535 SUM(aps.amount_remaining) AS open_amount,
536 min(aps.payment_num),
537 max(aps.payment_num) ';
538 fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
539
540 /* Build the from clause */
541 l_inv_from_clause :=
542 ' FROM ap_invoices_all api,
543 fun_net_suppliers_all fns,
544 ap_payment_schedules_all aps';
545 fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
546
547 /* Build where clause */
548 /* Do not select invoices that are on hold */
549 /* Select invoices that have been approved */
550 /* Select the invoices whose invoice types have been defined in the agreement */
551 /* Select only invoices where the transaction due date is on or before the Transaction Due date in the Batch */
552 /* Select transactions where the transactions due deate is between the start and end dates of the agreement */
553 /* Do not select invoices that are already selected in another batch where the batch status is not COMPLETE */
554
555 l_inv_where_clause :=
556 ' WHERE api.invoice_id = aps.invoice_id
557 AND aps.hold_flag = ''N''
558 AND aps.payment_status_flag <> ''Y''
559 AND ap_invoices_pkg.get_approval_status(api.invoice_id,
560 api.invoice_amount,
561 api.payment_status_flag,
562 api.invoice_type_lookup_code) = ''APPROVED''
563 AND fns.agreement_id = :v_agreement_id1
564 AND fns.supplier_id = api.vendor_id
565 AND NVL(fns.supplier_site_id, api.vendor_site_id)= api.vendor_site_id
566 AND EXISTS
567 (SELECT ''X''
568 FROM fun_net_ap_inv_types_all fnai
569 WHERE fnai.invoice_type = api.invoice_type_lookup_code
570 AND fnai.agreement_id = :v_agreement_id2)
571 AND TRUNC(aps.due_date) <= TRUNC(:v_transaction_due_date)
572 AND TRUNC(aps.due_date) BETWEEN TRUNC(:v_agr_start_date)
573 AND TRUNC(:v_agr_end_date)
574 AND NOT EXISTS
575 (SELECT ''X''
576 FROM fun_net_ap_invs_all fnap,
577 fun_net_batches_all fnba
578 WHERE fnap.invoice_id = api.invoice_id
579 AND fnap.batch_id = fnba.batch_id
580 AND fnba.batch_status_code <> ''CANCELLED''
581 AND fnba.batch_status_code <> ''COMPLETE''
582 AND fnba.batch_status_code <> ''REVERSED'')
583 AND fns.org_id = :v_org_id
584 AND api.org_id = fns.org_id
585 AND aps.org_id = fns.org_id';
586
587 /* Check Shikyu rule code on the Agreement
588 Y - "Yes" - Only invoices lines that were matched to PO lines with the OSA flag checked are selected for Netting
589 N - "No" -Only invoices lines that were matched to PO lines with the OSA flag not checked are selected for Netting
590 D - "Disregard"- All AP invoices selected for Netting; no filtering, therefore will select everything, as if the profile is "Off"
591 null value - if that scenario is met should be the same as disregard */
592
593 IF g_shikyu_rule = 'Y' THEN
594 l_inv_where_clause := l_inv_where_clause || ' AND JMF_SHIKYU_GRP.Is_AP_Inv_Shikyu_Nettable_func(api.invoice_id) = ''Y'' ';
595 ElSIF g_shikyu_rule = 'N' THEN
596 l_inv_where_clause := l_inv_where_clause || ' AND JMF_SHIKYU_GRP.Is_AP_Inv_Shikyu_Nettable_func(API.invoice_id) = ''N'' ';
597 l_inv_where_clause := l_inv_where_clause || ' AND EXISTS ' || ' (SELECT apd.distribution_line_number '|| ' FROM ap_invoice_distributions_all apd '|| 'WHERE apd.invoice_id = api.invoice_id '|| 'AND apd.po_distribution_id IS NOT NULL) ';
598 END IF;
599
600 fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
601
602 /* Build the Group by Clause */
603 l_inv_group_by_clause :=
604 ' GROUP BY api.invoice_id,
605 api.invoice_amount,
606 fns.supplier_priority ';
607
608 IF g_net_order_rule = 'TRX_DATE' then
609 l_inv_group_by_clause := l_inv_group_by_clause ||','||
610 'api.invoice_date ';
611 END IF;
612
613
614 /* l_inv_group_by_clause := l_inv_group_by_clause ||','||
615 CASE g_net_order_rule
616 WHEN 'DUEDATE_OLD' THEN 'aps.due_date'
617 WHEN 'DUEDATE_RECENT' THEN 'aps.due_date'
618 WHEN 'AMOUNT_ASCENDING' THEN 'open_amount'
619 WHEN 'AMOUNT_DESCENDING' THEN 'open_amount'
620 END; */
621
622 fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
623
624 /* Build the order by Clause . If the all the vendors priority =1 , then use the netting order by rule */
625 l_inv_order_by_clause :=
626 ' ORDER BY fns.supplier_priority,';
627 l_inv_order_by_clause := l_inv_order_by_clause ||
628 CASE g_net_order_rule
629 WHEN 'DUEDATE_OLD' THEN 'min(aps.due_date) asc'
630 WHEN 'DUEDATE_RECENT' THEN 'min(aps.due_date) desc'
631 WHEN 'AMOUNT_ASCENDING' THEN 'SUM(aps.amount_remaining) asc'
632 WHEN 'AMOUNT_DESCENDING' THEN 'SUM(aps.amount_remaining) desc'
633 WHEN 'TRX_DATE' THEN 'api.invoice_date asc'
634 END;
635 fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
636
637 fun_net_util.Log_String(g_state_level,l_path,'Net currency rule '||g_net_currency_rule);
638 --Select only the transactions which have the currency code of the netting currency rule if the Netting currency rule = 'SINGLE_CURRENCY'
639 IF g_net_currency_rule = 'SINGLE_CURRENCY' THEN
640
641 l_inv_where_clause := l_inv_where_clause || ' AND API.INVOICE_CURRENCY_CODE = :v_currency' ;
642 /* Build the entire select statement */
643 fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
644 fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
645 fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
646 fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
647 fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
648 l_sql_stmt :=
649 l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
650 /* Call the procedure to insert AP netting transactions */
651 insert_transactions(l_sql_stmt , g_net_currency, 'AP');
652
653 ELSIF g_net_currency_rule = 'ACCOUNTING_CURRENCY' THEN
654 --Select all the invoices irrespective of the currency code
655 l_inv_select_clause := 'SELECT
656 api.invoice_id,
657 api.invoice_amount as transaction_amount,
658 nvl(api.exchange_rate,1),
659 fc.precision,
660 SUM(aps.amount_remaining),
661 gl_currency_api.convert_amount_sql(api.invoice_currency_code,
662 :batch_currency,
663 trunc(:SETTLEMENT_DATE),
664 :exchange_rate_type,
665 SUM(aps.amount_remaining)) AS open_amount,
666 min(aps.payment_num),
667 max(aps.payment_num) ';
668 l_inv_from_clause := l_inv_from_clause || ', fnd_currencies fc ';
669 l_inv_where_clause := l_inv_where_clause || ' AND api.invoice_currency_code = fc.currency_code ';
670
671 l_inv_group_by_clause := ' GROUP BY api.invoice_id,
672 api.invoice_amount,
673 nvl(api.exchange_rate,1),
674 api.invoice_currency_code,
675 fc.precision,
676 fns.supplier_priority ';
677 /* Build the entire select statement */
678 fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
679 fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
680 fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
681 fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
682 fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
683
684 l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
685 fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
686 fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,2001,2000));
687
688 insert_transactions(l_sql_stmt, g_func_currency, 'AP');
689
690 ELSIF g_net_currency_rule = 'WITHIN_CURRENCY' THEN
691
692 l_sql_stmt := 'SELECT DISTINCT api.invoice_currency_code ' || l_inv_from_clause || l_inv_where_clause ;
693 l_inv_where_clause := l_inv_where_clause || ' AND api.invoice_currency_code = :v_currency ';
694 --Select the currencies in a cursor
695 OPEN inv_cur FOR l_sql_stmt USING g_agreement_id,
696 g_agreement_id,
697 g_batch_details.transaction_due_date,
698 g_agr_start_date,
699 g_agr_end_date,
700 g_batch_details.org_id;
701 LOOP
702 FETCH inv_cur INTO l_currency_code ;
703 EXIT WHEN inv_cur%NOTFOUND;
704
705 g_currency_count := g_currency_count + 1;
706 --Set the select where clause to select all transactions belonging to the cursor currency, as shown above in the first if condition.
707
708 fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
709 fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
710 fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
711 fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
712 fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
713
714 l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
715 insert_transactions(l_sql_stmt , l_currency_code, 'AP');
716
717
718 END LOOP;
719 CLOSE inv_cur;
720 END IF ; -- Currency
721 RETURN TRUE;
722
723 EXCEPTION
724 WHEN OTHERS THEN
725 fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
726 RETURN FALSE;
727 END prepare_ap_transactions;
728
729
730 /* Locks Payment Schedule lines */
731 PROCEDURE lock_ap_pymt_schedules(
732 p_batch_id IN fun_net_batches.batch_id%TYPE,
733 p_trx_due_date IN fun_net_batches.transaction_due_date%TYPE,
734 p_schd_tab IN pymt_sch_tab,
735 x_return_status OUT NOCOPY VARCHAR2)
736 IS
737 l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
738 l_path VARCHAR2(100);
739 BEGIN
740 l_path := g_path || 'lock_ap_pymt_schedules';
741 fun_net_util.Log_String(g_state_level,l_path,'Start of locking');
742 x_return_status := FND_API.G_TRUE;
743 fun_net_util.Log_String(g_state_level,l_path,'p_schd_tab.count :'||p_schd_tab.count);
744 fun_net_util.Log_String(g_state_level,l_path,'p_batch_id :'||p_batch_id);
745 -- Check for Mandatory Parameters
746 IF p_schd_tab.count = 0 OR p_batch_id IS NULL THEN
747 x_return_status := FND_API.G_FALSE;
748 RETURN;
749 END IF;
750
751 select checkrun_id
752 INTO l_checkrun_id
753 from FUN_NET_BATCHES_ALL
754 WHERE batch_id = p_batch_id;
755 -- Bug:8234111.
756 -- Update AP Payment Schedules with the Checkrun id
757 FOR i in 1..p_schd_tab.COUNT LOOP
758 UPDATE AP_PAYMENT_SCHEDULES aps
759 SET checkrun_id = l_checkrun_id
760 WHERE aps.invoice_id = p_schd_tab(i).invoice_id
761 AND amount_remaining <> 0
762 AND TRUNC(due_date) <= NVL(p_trx_due_date,TRUNC(due_date))
763 AND aps.payment_num BETWEEN p_schd_tab(i).min_payment_num
764 AND p_schd_tab(i).max_payment_num;
765 fun_net_util.Log_String(g_state_level,l_path,'invoice_id :'||p_schd_tab(i).invoice_id);
766 fun_net_util.Log_String(g_state_level,l_path,'Min_payment_number :'||p_schd_tab(i).min_payment_num);
767 fun_net_util.Log_String(g_state_level,l_path,'Max_payment_number :'||p_schd_tab(i).max_payment_num);
768 END LOOP;
769 fun_net_util.Log_String(g_state_level,l_path,'Payment schedules updated');
770 EXCEPTION
771
772 WHEN OTHERS THEN
773
774 fun_net_util.Log_String(g_state_level,l_path,'Failure in locking ap payment schedules');
775 x_return_status := FND_API.G_FALSE;
776 END lock_ap_pymt_schedules;
777
778 PROCEDURE insert_transactions(p_inv_cur VARCHAR2,p_currency_code VARCHAR2, p_appln VARCHAR2) IS
779 l_batch_exists VARCHAR2(1);
780 --l_inv_rank NUMBER;
781
782 TYPE InvCurTyp IS REF CURSOR;
783 inv_rec InvCurTyp;
784
785 TYPE amt_type IS TABLE OF fun_net_batches_all.total_netted_amt%TYPE INDEX BY BINARY_INTEGER;
786 TYPE trx_type IS TABLE OF fun_net_ar_txns_all.customer_trx_id%TYPE INDEX BY BINARY_INTEGER;
787 TYPE payment_num IS TABLE OF ap_payment_schedules_all.payment_num%TYPE INDEX BY BINARY_INTEGER;
788 TYPE inv_rank IS TABLE OF fun_net_ap_invs_all.ap_txn_rank%TYPE INDEX BY BINARY_INTEGER;
789 TYPE exchange_rate IS TABLE OF ap_invoices_all.exchange_rate%TYPE INDEX BY BINARY_INTEGER;
790 TYPE precision_tab IS TABLE OF fnd_currencies.precision%TYPE INDEX BY BINARY_INTEGER;
791 --l_invoice_id fun_net_ap_invs_all.invoice_id%TYPE;
792 --l_invoice_amt fun_net_ap_invs_all.invoice_amt%TYPE;
793 --l_open_amt fun_net_ap_invs_all.open_amt%TYPE;
794 --l_inv_curr_open_amt fun_net_ap_invs_all.inv_curr_open_amt%TYPE;
795 l_invoice_id trx_type;
796 l_invoice_amt amt_type;
797 l_open_amt amt_type;
798 l_inv_curr_open_amt amt_type;
799 l_min_payment_num payment_num;
800 l_max_payment_num payment_num;
801 l_invoice_rank inv_rank;
802 l_exchange_rate exchange_rate;
803 l_precision precision_tab;
804 l_rank NUMBER;
805 l_pymt_sch_table pymt_sch_tab;
806 l_return_status VARCHAR2(1);
807 --l_customer_trx_id fun_net_ar_txns_all.customer_trx_id%TYPE;
808 --l_transaction_amt fun_net_ar_txns_all.transaction_amt%TYPE;
809 l_path varchar2(100);
810 l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
811 BEGIN
812 l_path := g_path || 'Insert_Transactions';
813 fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,1,2000));
814 fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,2001,2000));
815 fun_net_util.Log_String(g_state_level,l_path,'Currency:'||p_currency_code);
816 fun_net_util.Log_String(g_state_level,l_path,'Application:'||p_appln);
817 fun_net_util.Log_String(g_state_level,l_path,'Currency rule: '||g_net_currency_rule);
818 /* Check for mandatory parameters */
819 IF p_inv_cur IS NULL OR p_currency_code IS NULL OR p_appln IS NULL THEN
820 RETURN;
821 END IF;
822
823 fun_net_util.Log_String(g_state_level,l_path,'Agreement count:'||g_agreement_count);
824 /* If this is the first agreement and the netting currency rule is as below then this will be the first group of tnxs to be parsed , need not create a batch as we can use the batch that already exists */
825 IF g_agreement_count = 1 AND g_net_currency_rule IN ('SINGLE_CURRENCY','ACCOUNTING_CURRENCY') THEN
826 fun_net_util.Log_String(g_state_level,l_path,'Setting currency code for first agreement');
827 g_batch_list(g_idx).currency := p_currency_code;
828 /* If the rule is 'NET WITHIN CURRENCY ' */
829 ELSIF g_agreement_count = 1 AND g_net_currency_rule = 'WITHIN_CURRENCY' AND g_currency_count = 1 THEN
830 fun_net_util.Log_String(g_state_level,l_path,'Setting currency code for first currency and first agreement');
831 g_batch_list(g_idx).currency := p_currency_code;
832 ELSE /* prow_count > 1 or if this is not the first agreement then */
833 fun_net_util.Log_String(g_state_level,l_path,'Checking if batch already exists');
834 IF NOT batch_exists(p_currency_code) THEN
835 fun_net_util.Log_String(g_state_level,l_path,'Batch does not exist. Creating new batch');
836 insert_batch_record(p_currency_code);
837 g_idx := g_idx + 1;
838 g_batch_list(g_idx).batch_id := g_batch_id;
839 g_batch_list(g_idx).agreement_id := g_agreement_id;
840 g_batch_list(g_idx).currency := p_currency_code;
841
842 END IF;
843
844 END IF ;
845
846 select checkrun_id
847 INTO l_checkrun_id
848 from FUN_NET_BATCHES_ALL
849 WHERE batch_id = g_batch_id;
850
851 IF l_checkrun_id is NULL THEN
852 SELECT ap_inv_selection_criteria_s.nextval
853 INTO l_checkrun_id
854 FROM dual;
855 fun_net_util.Log_String(g_state_level,l_path,'l_checkrun_id :'||l_checkrun_id);
856
857 -- Update Netting Batch with the Checkrun id
858
859 UPDATE FUN_NET_BATCHES_ALL
860 SET checkrun_id = l_checkrun_id
861 WHERE batch_id = g_batch_id;
862 fun_net_util.Log_String(g_state_level,l_path,'Batch updated');
863 END IF;
864
865 fun_net_util.Log_String(g_state_level,l_path,'Inserting invoices into Netting tables');
866 l_rank := 0;
867
868 IF p_appln = 'AP' AND g_net_currency_rule IN ('SINGLE_CURRENCY', 'WITHIN_CURRENCY') THEN
869 fun_net_util.Log_String(g_state_level,l_path,'Fetching the AP invoices');
870 OPEN inv_rec FOR p_inv_cur USING g_agreement_id,
871 g_agreement_id,
872 g_batch_details.transaction_due_date,
873 g_agr_start_date,
874 g_agr_end_date,
875 g_batch_details.org_id,
876 p_currency_code;
877 FETCH inv_rec BULK COLLECT INTO l_invoice_id, l_invoice_amt, l_open_amt, l_min_payment_num,l_max_payment_num;
878 FOR i IN 1..l_invoice_id.COUNT
879 LOOP
880 l_rank := l_rank + 1;
881 l_invoice_rank(i) := l_rank;
882 l_pymt_sch_table(i).invoice_id := l_invoice_id(i);
883 l_pymt_sch_table(i).min_payment_num := l_min_payment_num(i);
884 l_pymt_sch_table(i).max_payment_num := l_max_payment_num(i);
885 END LOOP;
886
887 fun_net_util.Log_String(g_state_level,l_path,'Calling lock_ap_pymt_schedules for batch id:'||g_batch_id);
888 lock_ap_pymt_schedules(
889 p_batch_id => g_batch_id,
890 p_trx_due_date => g_batch_details.transaction_due_date,
891 p_schd_tab => l_pymt_sch_table,
892 x_return_status => l_return_status);
893
894 FORALL i IN 1..l_invoice_id.COUNT
895 --LOOP
896 --FETCH inv_rec INTO l_invoice_id,
897 -- l_invoice_amt,
898 -- l_open_amt;
899 --EXIT WHEN inv_rec%NOTFOUND;
900 --l_inv_rank(i) := i;
901 --fun_net_util.Log_String(g_state_level,l_path,'Invoice ID:'||l_invoice_id);
902
903 INSERT INTO fun_net_ap_invs_all
904 (batch_id,
905 invoice_id,
906 object_version_number,
907 ap_txn_rank,
908 invoice_amt,
909 open_amt,
910 inv_curr_open_amt,
911 org_id,
912 creation_date,
913 created_by,
914 last_update_date,
915 last_updated_by,
916 last_update_login)
917 VALUES
918 (g_batch_id,
919 l_invoice_id(i),
920 1,
921 l_invoice_rank(i),
922 l_invoice_amt(i),
923 l_open_amt(i),
924 l_open_amt(i),
925 g_batch_details.org_id,
926 sysdate,
927 g_user_id,
928 sysdate,
929 g_user_id,
930 g_login_id);
931
932 --END LOOP;
933 CLOSE inv_rec;
934
935 ELSIF p_appln = 'AP' AND g_net_currency_rule IN ('ACCOUNTING_CURRENCY') THEN
936 fun_net_util.Log_String(g_state_level,l_path,'Fetching the AP Invoices');
937 OPEN inv_rec FOR p_inv_cur USING
938 p_currency_code,
939 g_batch_details.SETTLEMENT_DATE,
940 g_batch_details.exchange_rate_type,
941 g_agreement_id,
942 g_agreement_id,
943 g_batch_details.transaction_due_date,
944 g_agr_start_date,
945 g_agr_end_date,
946 g_batch_details.org_id;
947
948 FETCH inv_rec BULK COLLECT INTO l_invoice_id, l_invoice_amt, l_exchange_rate,l_precision,l_inv_curr_open_amt,l_open_amt, l_min_payment_num,l_max_payment_num;
949 FOR i IN 1..l_invoice_id.COUNT
950 LOOP
951 l_rank := l_rank + 1;
952 l_invoice_rank(i) := l_rank ;
953 l_pymt_sch_table(i).invoice_id := l_invoice_id(i);
954 l_pymt_sch_table(i).min_payment_num := l_min_payment_num(i);
955 l_pymt_sch_table(i).max_payment_num := l_max_payment_num(i);
956 END LOOP;
957
958 lock_ap_pymt_schedules(
959 p_batch_id => g_batch_id,
960 p_trx_due_date => g_batch_details.transaction_due_date,
961 p_schd_tab => l_pymt_sch_table,
962 x_return_status => l_return_status);
963
964 FORALL i IN 1..l_invoice_id.COUNT
965 --LOOP
966 --FETCH inv_rec INTO l_invoice_id,
967 -- l_invoice_amt,
968 -- l_open_amt,
969 -- l_inv_curr_open_amt;
970 --EXIT WHEN inv_rec%NOTFOUND;
971 --l_inv_rank := l_inv_rank + 1;
972 --fun_net_util.Log_String(g_state_level,l_path,'Invoice ID:'||l_invoice_id);
973
974 INSERT INTO fun_net_ap_invs_all
975 (batch_id,
976 invoice_id,
977 object_version_number,
978 ap_txn_rank,
979 invoice_amt,
980 open_amt,
981 inv_curr_open_amt,
982 org_id,
983 creation_date,
984 created_by,
985 last_update_date,
986 last_updated_by,
987 last_update_login)
988 VALUES
989 (g_batch_id,
990 l_invoice_id(i),
991 1,
992 l_invoice_rank(i),
993 l_invoice_amt(i),
994 l_open_amt(i),
995 l_inv_curr_open_amt(i),
996 g_batch_details.org_id,
997 sysdate,
998 g_user_id,
999 sysdate,
1000 g_user_id,
1001 g_login_id);
1002
1003 --END LOOP;
1004 CLOSE inv_rec;
1005
1006
1007 ELSIF p_appln = 'AR' AND g_net_currency_rule IN ('SINGLE_CURRENCY', 'WITHIN_CURRENCY') THEN
1008 fun_net_util.Log_String(g_state_level,l_path,'Fetching the AR Transactions');
1009 OPEN inv_rec FOR p_inv_cur USING g_batch_details.transaction_due_date,
1010 g_agr_start_date,
1011 g_agr_end_date,
1012 g_agreement_id,
1013 g_agreement_id,
1014 g_batch_details.org_id,
1015 g_sel_past_due_flag,
1016 g_sel_past_due_flag,
1017 g_days_past_due,
1018 p_currency_code;
1019 FETCH inv_rec BULK COLLECT INTO l_invoice_id, l_invoice_amt, l_open_amt;
1020 FOR i IN 1..l_invoice_id.COUNT
1021 LOOP
1022 l_rank := l_rank + 1;
1023 l_invoice_rank(i) := l_rank ;
1024 END LOOP;
1025
1026 FORALL i IN 1..l_invoice_id.COUNT
1027
1028 --LOOP
1029 --FETCH inv_rec INTO l_customer_trx_id,
1030 -- l_transaction_amt,
1031 -- l_open_amt;
1032 --EXIT WHEN inv_rec%NOTFOUND;
1033 --l_inv_rank := l_inv_rank + 1;
1034 --fun_net_util.Log_String(g_state_level,l_path,'Transaction ID:'||l_customer_trx_id);
1035
1036 INSERT INTO fun_net_ar_txns_all
1037 (batch_id,
1038 customer_trx_id,
1039 object_version_number,
1040 ar_txn_rank,
1041 transaction_amt,
1042 open_amt,
1043 txn_curr_open_amt,
1044 org_id,
1045 creation_date,
1046 created_by,
1047 last_update_date,
1048 last_updated_by,
1049 last_update_login)
1050 VALUES
1051 (g_batch_id,
1052 l_invoice_id(i),
1053 1,
1054 l_invoice_rank(i),
1055 l_invoice_amt(i),
1056 l_open_amt(i),
1057 l_open_amt(i),
1058 g_batch_details.org_id,
1059 sysdate,
1060 g_user_id,
1061 sysdate,
1062 g_user_id,
1063 g_login_id);
1064 --END LOOP;
1065 CLOSE inv_rec;
1066
1067 ELSIF p_appln = 'AR' AND g_net_currency_rule IN ('ACCOUNTING_CURRENCY') THEN
1068 fun_net_util.Log_String(g_state_level,l_path,'Fetching the AR Transactions');
1069 OPEN inv_rec FOR p_inv_cur USING
1070 p_currency_code,
1071 g_batch_details.SETTLEMENT_DATE,
1072 g_batch_details.exchange_rate_type,
1073 g_batch_details.transaction_due_date,
1074 g_agr_start_date,
1075 g_agr_end_date,
1076 g_agreement_id,
1077 g_agreement_id,
1078 g_batch_details.org_id,
1079 g_sel_past_due_flag,
1080 g_sel_past_due_flag,
1081 g_days_past_due;
1082 FETCH inv_rec BULK COLLECT INTO l_invoice_id, l_invoice_amt, l_open_amt,l_inv_curr_open_amt;
1083 FOR i IN 1..l_invoice_id.COUNT
1084 LOOP
1085 l_rank := l_rank + 1;
1086 l_invoice_rank(i) := l_rank;
1087 END LOOP;
1088
1089 FORALL i IN 1..l_invoice_id.COUNT
1090
1091 -- LOOP
1092 -- FETCH inv_rec INTO l_customer_trx_id,
1093 -- l_transaction_amt,
1094 -- l_open_amt,
1095 -- l_inv_curr_open_amt;
1096 -- EXIT WHEN inv_rec%NOTFOUND;
1097 -- l_inv_rank := l_inv_rank + 1;
1098 -- fun_net_util.Log_String(g_state_level,l_path,'Transaction ID:'||l_customer_trx_id);
1099
1100 INSERT INTO fun_net_ar_txns_all
1101 (batch_id,
1102 customer_trx_id,
1103 object_version_number,
1104 ar_txn_rank,
1105 transaction_amt,
1106 open_amt,
1107 txn_curr_open_amt,
1108 org_id,
1109 creation_date,
1110 created_by,
1111 last_update_date,
1112 last_updated_by,
1113 last_update_login)
1114 VALUES
1115 (g_batch_id,
1116 l_invoice_id(i),
1117 1,
1118 l_invoice_rank(i),
1119 l_invoice_amt(i),
1120 l_open_amt(i),
1121 l_inv_curr_open_amt(i),
1122 g_batch_details.org_id,
1123 sysdate,
1124 g_user_id,
1125 sysdate,
1126 g_user_id,
1127 g_login_id);
1128
1129 --END LOOP;
1130 CLOSE inv_rec;
1131 END IF;
1132
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1136 NULL;
1137
1138 END insert_transactions;
1139
1140 PROCEDURE calculate_AP_AR_balances(p_amt_to_net OUT NOCOPY NUMBER,
1141 p_status_flag OUT NOCOPY VARCHAR2) IS
1142 l_ap_bal fun_net_ap_invs_all.open_amt%TYPE;
1143 l_ar_bal fun_net_ar_txns_all.open_amt%TYPE;
1144 l_amt_to_net fun_net_batches_all.total_netted_amt%TYPE;
1145 l_status_flag VARCHAR2(1);
1146 l_sql_stmt VARCHAR2(2000);
1147 l_path varchar2(100);
1148 BEGIN
1149 l_path := g_path || 'Calculate_AP_AR_Balances';
1150
1151 validate_AP_AR_balances(l_ar_bal, l_ap_bal, l_status_flag);
1152 IF l_status_flag = FND_API.G_TRUE THEN
1153 IF l_ap_bal >= l_ar_bal THEN
1154 fun_net_util.Log_String(g_state_level,l_path,'AP Balance > AR Balance');
1155 l_amt_to_net := l_ar_bal;
1156 /* As the Ar Bal = Total Net amount , update the net amount for each AR tnx with the open balance of that tnx */
1157 UPDATE fun_net_ar_txns_all
1158 SET netted_amt = open_amt,
1159 txn_curr_net_amt = txn_curr_open_amt
1160 WHERE batch_id = g_batch_id;
1161
1162 /*Order the transactions by rank as the tnxs with a higher rank should be netted first */
1163 l_sql_stmt := 'SELECT open_amt,invoice_id,0,inv_curr_open_amt,0 FROM fun_net_ap_invs_all WHERE batch_id = :v_batch_id ORDER BY ap_txn_rank';
1164 update_net_balances(l_sql_stmt,l_amt_to_net,'AP');
1165 ELSIF l_ar_bal > l_ap_bal THEN
1166 fun_net_util.Log_String(g_state_level,l_path,'AR Balance > AP Balance');
1167 l_amt_to_net := l_ap_bal;
1168 /* As the AP Bal = Total Net amount , update the net amount for each AP tnx with the open balance of that tnx */
1169 UPDATE fun_net_ap_invs_all
1170 SET netted_amt = open_amt,
1171 inv_curr_net_amt = inv_curr_open_amt
1172 WHERE batch_id = g_batch_id;
1173
1174 l_sql_stmt := 'SELECT open_amt,customer_trx_id,0, txn_curr_open_amt,0 FROM fun_net_ar_txns_all WHERE batch_id = :v_batch_id ORDER BY ar_txn_rank';
1175 update_net_balances(l_sql_stmt,l_amt_to_net,'AR');
1176 END IF;
1177
1178 fun_net_util.Log_String(g_state_level,l_path,'Total Netted Amount :'||l_amt_to_net);
1179 /*UPDATE fun_net_batches_all
1180 SET total_netted_amt = l_amt_to_net
1181 WHERE batch_id = g_batch_id; */
1182 p_amt_to_net := l_amt_to_net;
1183 p_status_flag := FND_API.G_TRUE;
1184 ELSE
1185 /*Unlock AP and AR Transactions that have been locked */
1186
1187 fun_net_util.Log_String(g_state_level,l_path,'validation of ap and ar balances failed.Some transactions in AP and AR might have to be unlocked manually');
1188 p_status_flag := FND_API.G_FALSE;
1189 END IF;
1190
1191 EXCEPTION
1192 WHEN NO_DATA_FOUND then
1193 fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1194 RETURN;
1195 END calculate_AP_AR_balances;
1196
1197 PROCEDURE validate_AP_AR_balances(p_ar_bal OUT NOCOPY NUMBER,
1198 p_ap_bal OUT NOCOPY NUMBER,
1199 p_status_flag OUT NOCOPY VARCHAR2) IS
1200
1201 CURSOR c_get_ar_open_amount IS
1202 SELECT SUM (open_amt)
1203 FROM fun_net_ar_txns_all
1204 WHERE batch_id = g_batch_id;
1205
1206 CURSOR c_get_ap_open_amount IS
1207 SELECT SUM (open_amt)
1208 FROM fun_net_ap_invs_all
1209 WHERE batch_id = g_batch_id;
1210
1211 l_path varchar2(100);
1212 BEGIN
1213 l_path := g_path || 'Validate_AP_AR_Balances';
1214
1215 p_status_flag := FND_API.G_TRUE;
1216
1217 OPEN c_get_ar_open_amount;
1218 FETCH c_get_ar_open_amount INTO p_ar_bal;
1219 CLOSE c_get_ar_open_amount;
1220
1221 OPEN c_get_ap_open_amount;
1222 FETCH c_get_ap_open_amount INTO p_ap_bal;
1223 CLOSE c_get_ap_open_amount;
1224
1225 fun_net_util.Log_String(g_state_level,l_path,'AP open amount :'||p_ap_bal);
1226 fun_net_util.Log_String(g_state_level,l_path,'AR open amount :'||p_ar_bal);
1227 IF nvl(p_ap_bal,0) = 0 OR nvl(p_ar_bal,0) = 0 THEN
1228
1229 /* Error out the Batch to say netting cannot continue and set the Batch to Error and put message in the log*/
1230 --ERROR MESSAGE : 'Netting cannot be performed as the Outstanding balance is zero'
1231 /*UPDATE fun_net_batches_all
1232 SET batch_status_code = 'ERROR'
1233 WHERE batch_id = g_batch_id; */
1234
1235 p_status_flag := FND_API.G_FALSE;
1236 /*IF NOT update_Batch_Status('ERROR') THEN
1237 p_status_flag := FND_API.G_FALSE;
1238 END IF; */
1239
1240 FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BALANCES');
1241
1242 fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION : AP or AR balance is zero');
1243 RETURN;
1244
1245 END IF;
1246 fun_net_util.Log_String(g_state_level,l_path,'AP and AR balances validated');
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1250 END validate_AP_AR_balances;
1251
1252
1253 PROCEDURE insert_batch_record(p_currency_code VARCHAR2) IS
1254 l_batch_id fun_net_batches_all.batch_id%TYPE;
1255 l_path varchar2(200);
1256 BEGIN
1257
1258 l_path := g_path||'insert_batch_record';
1259 /* Check for mandatory parameters and all values that are going to be inserted */
1260 SELECT fun_net_batches_s.NEXTVAL
1261 INTO g_batch_id
1262 FROM DUAL;
1263
1264 INSERT INTO FUN_NET_BATCHES_ALL
1265 (batch_id,
1266 object_version_number,
1267 agreement_id,
1268 batch_name,
1269 batch_number,
1270 review_netting_batch_flag,
1271 batch_currency,
1272 batch_status_code,
1273 total_netted_amt,
1274 transaction_due_date,
1275 settlement_date,
1276 response_date,
1277 exchange_rate_type,
1278 exchange_rate,
1279 gl_date,
1280 org_id,
1281 creation_date,
1282 created_by,
1283 last_update_date,
1284 last_updated_by,
1285 last_update_login,
1286 attribute_category,
1287 attribute1,
1288 attribute2,
1289 attribute3,
1290 attribute4,
1291 attribute5,
1292 attribute6,
1293 attribute7,
1294 attribute8,
1295 attribute9,
1296 attribute10,
1297 attribute11,
1298 attribute12,
1299 attribute13,
1300 attribute14,
1301 attribute15,
1302 attribute16,
1303 attribute17,
1304 attribute18,
1305 attribute19,
1306 attribute20)
1307 VALUES
1308 (g_batch_id,
1309 1,
1310 g_agreement_id,
1311 g_batch_details.batch_name,
1312 g_batch_id,
1313 g_batch_details.review_netting_batch_flag,
1314 p_currency_code,
1315 g_batch_details.batch_status_code,
1316 g_batch_details.total_netted_amt,
1317 g_batch_details.transaction_due_date,
1318 g_batch_details.settlement_date,
1319 g_batch_details.response_date,
1320 g_batch_details.exchange_rate_type,
1321 g_batch_details.exchange_rate,
1322 g_batch_details.gl_date,
1323 g_batch_details.org_id,
1324 sysdate,
1325 g_user_id,
1326 sysdate,
1327 g_user_id,
1328 g_login_id,
1329 g_batch_details.attribute_category,
1330 g_batch_details.attribute1,
1331 g_batch_details.attribute2,
1332 g_batch_details.attribute3,
1333 g_batch_details.attribute4,
1334 g_batch_details.attribute5,
1335 g_batch_details.attribute6,
1336 g_batch_details.attribute7,
1337 g_batch_details.attribute8,
1338 g_batch_details.attribute9,
1339 g_batch_details.attribute10,
1340 g_batch_details.attribute11,
1341 g_batch_details.attribute12,
1342 g_batch_details.attribute13,
1343 g_batch_details.attribute14,
1344 g_batch_details.attribute15,
1345 g_batch_details.attribute16,
1346 g_batch_details.attribute17,
1347 g_batch_details.attribute18,
1348 g_batch_details.attribute19,
1349 g_batch_details.attribute20);
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352
1353 fun_net_util.Log_String(g_state_level,l_path,'Insertion of batch_record failed.');
1354 NULL;
1355 END insert_batch_record;
1356
1357 FUNCTION batch_exists(p_currency_code VARCHAR2) RETURN BOOLEAN IS
1358 l_path varchar2(200);
1359 BEGIN
1360 l_path := g_path||'batch_exists';
1361 /* Check for mandatory parameters */
1362 IF p_currency_code IS NULL THEN
1363 fun_net_util.Log_String(g_state_level,l_path,'Currency code is NULL');
1364 RETURN FALSE;
1365 END IF;
1366
1367 /* Check if the Batch already exists for the given currency and agreement.
1368 AP and AR Transactions that have the same currency code and agreement will belong to
1369 the same batch if the 'Netting within Currency' option is Selected. */
1370
1371 FOR i IN 1..g_idx LOOP
1372 IF g_batch_list(i).agreement_id = g_agreement_id AND
1373 g_batch_list(i).currency = p_currency_code THEN
1374 fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
1375 fun_net_util.Log_String(g_state_level,l_path,'Currency: '||g_batch_list(i).currency);
1376 g_batch_id := g_batch_list(i).batch_id;
1377 fun_net_util.Log_String(g_state_level,l_path,'Batch ID: '||g_batch_list(i).batch_id);
1378 RETURN TRUE;
1379 ELSE
1380 fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
1381 fun_net_util.Log_String(g_state_level,l_path,'Currency: '||g_batch_list(i).currency);
1382 END IF;
1383 END LOOP;
1384
1385 RETURN FALSE;
1386
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389 RETURN FALSE;
1390 END batch_exists;
1391
1392
1393 PROCEDURE validate_exch_rate (p_status_flag OUT NOCOPY VARCHAR2) IS
1394
1395 CURSOR trx_curr_cur IS
1396 SELECT INVOICE_CURRENCY_CODE
1397 FROM ap_invoices_all api, fun_net_ap_invs_all fnapi
1398 WHERE api.invoice_id = fnapi.invoice_id
1399 AND fnapi.batch_id = g_batch_id
1400 UNION
1401 SELECT INVOICE_CURRENCY_CODE
1402 FROM ra_customer_trx_all rct, fun_net_ar_txns_all fnart
1403 WHERE rct.customer_trx_id = fnart.customer_trx_id
1404 AND fnart.batch_id = g_batch_id;
1405
1406 l_count NUMBER;
1407 l_exc_rate NUMBER;
1408 l_conv_rate fun_net_batches_all.exchange_rate_type%TYPE;
1409 l_path VARCHAR2(100);
1410 l_msg_count NUMBER;
1411 l_msg_data VARCHAR2(2000);
1412
1413 BEGIN
1414
1415 l_path := g_path || 'validate_exch_rate';
1416 l_count := 0;
1417 p_status_flag := FND_API.G_TRUE;
1418
1419 SELECT USER_CONVERSION_TYPE
1420 INTO l_conv_rate
1421 FROM GL_DAILY_CONVERSION_TYPES
1422 WHERE CONVERSION_TYPE = g_batch_details.exchange_rate_type;
1423
1424 fun_net_util.Log_String(g_state_level,l_path,'l_conv_rate:'||l_conv_rate);
1425
1426 fun_net_util.Log_String(g_state_level,l_path,' p_status_flag :'
1427 || p_status_flag);
1428
1429 For trx_curr_rec in trx_curr_cur
1430 LOOP
1431
1432 fun_net_util.Log_String(g_state_level,l_path,'g_func_currency: '
1433 ||g_func_currency);
1434
1435 fun_net_util.Log_String(g_state_level,l_path,
1436 'trx_curr_REC.invoice_currency_code: '||trx_curr_REC.invoice_currency_code);
1437
1438 l_exc_rate := Derive_Net_Exchg_Rate(g_func_currency,trx_curr_REC.invoice_currency_code);
1439
1440 fun_net_util.Log_String(g_state_level,l_path,'l_exc_rate :'||l_exc_rate);
1441 fun_net_util.Log_String(g_state_level,l_path,'l_count :'||l_count);
1442
1443 IF l_exc_rate IS NULL and l_count = 0 THEN
1444
1445 fun_net_util.Log_String(g_state_level,l_path,'Inside if ');
1446
1447 FND_MESSAGE.SET_NAME('FUN','FUN_NET_EX_RATE_NOT_DEFINED');
1448 FND_MESSAGE.SET_TOKEN('RATE_TYPE', l_conv_rate);
1449 l_msg_data := FND_MESSAGE.get;
1450
1451 fnd_file.put_line(fnd_file.log,l_msg_data);
1452
1453 fnd_file.put_line(fnd_file.log, ' '||
1454 trx_curr_REC.invoice_currency_code || ' -> ' || g_func_currency );
1455 l_count := 2;
1456
1457 ELSIF l_exc_rate IS NULL AND l_count = 2 THEN
1458 fun_net_util.Log_String(g_state_level,l_path,'Inside esle if');
1459
1460
1461 fnd_file.put_line(fnd_file.log, ' '||
1462 trx_curr_REC.invoice_currency_code || ' -> ' || g_func_currency );
1463
1464
1465 END IF;
1466
1467 IF l_exc_rate is NULL THEN
1468 p_status_flag := FND_API.G_FALSE;
1469 END IF;
1470
1471 END LOOP;
1472 fun_net_util.Log_String(g_state_level,l_path,' p_status_flag :'
1473 || p_status_flag);
1474
1475 END validate_exch_rate;
1476
1477
1478
1479 PROCEDURE update_net_balances(p_sql_stmt VARCHAR2,
1480 p_amt_to_net NUMBER,
1481 p_appln VARCHAR2) IS
1482
1483 TYPE amt_type IS TABLE OF fun_net_batches_all.total_netted_amt%TYPE;
1484 TYPE trx_type IS TABLE OF fun_net_ar_txns_all.customer_trx_id%TYPE;
1485 l_open_amt amt_type;
1486 l_exc_rate NUMBER;
1487 l_net_amt amt_type;
1488 l_trx_id trx_type;
1489 l_inv_curr_open_amt amt_type;
1490 l_inv_curr_net_amt amt_type;
1491 l_amt_to_net fun_net_batches_all.total_netted_amt%TYPE;
1492 l_inv_currency fnd_currencies.currency_code%TYPE;
1493 l_precision fnd_currencies.precision%TYPE;
1494
1495 TYPE tnxCurTyp IS REF CURSOR;
1496 tnx_rec tnxCurTyp;
1497
1498 l_path varchar2(100);
1499 BEGIN
1500 l_path := g_path || 'Update_Net_Balances';
1501
1502 /* Check for mandatory parameters */
1503 l_amt_to_net := p_amt_to_net;
1504 fun_net_util.Log_String(g_state_level,l_path,'Fetching the transactions');
1505 OPEN tnx_rec FOR p_sql_stmt USING g_batch_id;
1506 FETCH tnx_rec BULK COLLECT INTO l_open_amt ,l_trx_id, l_net_amt, l_inv_curr_open_amt, l_inv_curr_net_amt;
1507
1508 /* Scan the table for every tnx selected and compute the net amount .
1509 Example :if the Total Net amount = 950
1510
1511 Tot_Net_Amount = 950
1512 Rank Tnx Open Amount Net Amount
1513 1 1001 400 400
1514 2 1002 500 500
1515 3 1003 600 50 */
1516
1517 FOR i IN 1..l_trx_id.COUNT
1518 LOOP
1519 IF l_open_amt(i) < l_amt_to_net THEN
1520 l_net_amt(i) := l_open_amt(i);
1521 l_inv_curr_net_amt(i) := l_inv_curr_open_amt(i);
1522 ELSE
1523 l_net_amt(i) := l_amt_to_net;
1524 IF g_net_currency_rule = 'ACCOUNTING_CURRENCY' THEN
1525 IF p_appln = 'AP' THEN
1526
1527
1528
1529 SELECT fc.currency_code,fc.precision
1530 INTO l_inv_currency,l_precision
1531 FROM ap_invoices_all api, fnd_currencies fc
1532 WHERE api.invoice_id = l_trx_id(i)
1533 AND api.invoice_currency_code = fc.currency_code;
1534
1535 l_exc_rate := Derive_Net_Exchg_Rate(g_func_currency,l_inv_currency);
1536
1537 fun_net_util.Log_String(g_state_level,l_path,'l_exc_rate:'||l_exc_rate);
1538
1539 fun_net_util.Log_String(g_state_level,l_path,'l_amt_to_net:'||l_amt_to_net);
1540
1541
1542 l_inv_curr_net_amt(i) := l_amt_to_net * Derive_Net_Exchg_Rate(g_func_currency,l_inv_currency);
1543 l_inv_curr_net_amt(i) := ROUND(l_inv_curr_net_amt(i),l_precision);
1544 ELSIF p_appln = 'AR' THEN
1545 SELECT fc.currency_code,fc.precision
1546 INTO l_inv_currency,l_precision
1547 FROM ra_customer_trx_all rct, fnd_currencies fc
1548 WHERE rct.customer_trx_id = l_trx_id(i)
1549 AND rct.invoice_currency_code = fc.currency_code;
1550
1551 l_inv_curr_net_amt(i) := l_amt_to_net * Derive_Net_Exchg_Rate(g_func_currency,l_inv_currency);
1552 l_inv_curr_net_amt(i) := ROUND(l_inv_curr_net_amt(i),l_precision);
1553 END IF;
1554 ELSE
1555 l_inv_curr_net_amt(i) := l_net_amt(i);
1556 END IF;
1557 END IF;
1558 fun_net_util.Log_String(g_state_level,l_path,'trx_id :'||l_trx_id(i));
1559 fun_net_util.Log_String(g_state_level,l_path,'Netted Amount :'||l_net_amt(i));
1560 fun_net_util.Log_String(g_state_level,l_path,'Invoice currency Netted Amount :'||l_inv_curr_net_amt(i));
1561 l_amt_to_net := l_amt_to_net - l_open_amt(i);
1562
1563 IF l_amt_to_net <= 0 THEN
1564 EXIT;
1565 END IF;
1566 END LOOP;
1567
1568 IF p_appln = 'AP' THEN
1569 FORALL i IN 1..l_trx_id.COUNT
1570 UPDATE fun_net_ap_invs_all
1571 SET netted_amt = l_net_amt(i),
1572 inv_curr_net_amt = l_inv_curr_net_amt(i)
1573 WHERE batch_id = g_batch_id
1574 AND invoice_id = l_trx_id(i);
1575
1576 ELSIF p_appln = 'AR' THEN
1577 FORALL i IN 1..l_trx_id.COUNT
1578 UPDATE fun_net_ar_txns_all
1579 SET netted_amt = l_net_amt(i),
1580 txn_curr_net_amt = l_inv_curr_net_amt(i)
1581 WHERE batch_id = g_batch_id
1582 AND customer_trx_id = l_trx_id(i);
1583 END IF;
1584 EXCEPTION
1585 WHEN OTHERS THEN
1586 fun_net_util.Log_String(g_state_level,l_path,sqlerrm);
1587 END update_net_balances;
1588
1589 PROCEDURE Update_Net_Amounts(p_batch_id NUMBER, p_amt_to_net NUMBER, p_appln VARCHAR2) IS
1590 BEGIN
1591 NULL;
1592 END Update_Net_Amounts;
1593
1594
1595 FUNCTION calculate_ar_trx_amt(
1596 p_customer_trx_id NUMBER)
1597 RETURN NUMBER
1598 IS
1599 l_total_amount ra_cust_trx_line_gl_dist.amount%TYPE;
1600 BEGIN
1601
1602 SELECT sum(amount)
1603 INTO l_total_amount
1604 from ra_cust_trx_line_gl_dist dist,
1605 ra_customer_trx_lines_all lines
1606 Where lines.customer_trx_id = p_customer_trx_id
1607 And lines.customer_trx_line_id = dist.customer_trx_line_id
1608 And dist.account_class <> 'REC';
1609 RETURN l_total_amount;
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 NULL;
1613 END calculate_ar_trx_amt;
1614
1615 PROCEDURE create_net_batch(
1616 -- ***** Standard API Parameters *****
1617 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1618 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1619 x_return_status OUT NOCOPY VARCHAR2,
1620 x_msg_count OUT NOCOPY NUMBER,
1621 x_msg_data OUT NOCOPY VARCHAR2,
1622 -- ***** Netting batch input parameters *****
1623 p_batch_id IN NUMBER) IS
1624
1625 -- ***** local variables *****
1626 l_return_status VARCHAR2(1);
1627 l_msg_count NUMBER;
1628 l_msg_data VARCHAR2(2000);
1629 l_path VARCHAR2(100);
1630 l_amt_to_net fun_net_batches_all.total_netted_amt%TYPE;
1631 l_status_flag VARCHAR2(1);
1632 batch_status_flag BOOLEAN;
1633
1634 CURSOR c_agreement_cur IS
1635 SELECT agreement_id,
1636 net_currency_rule_code,
1637 net_order_rule_code,
1638 net_balance_rule_code,
1639 bank_account_id,
1640 net_currency_code,
1641 agreement_start_date,
1642 agreement_end_date,
1643 shikyu_rule_code,
1644 days_past_due,
1645 sel_rec_past_due_txns_flag
1646 FROM fun_net_agreements
1647 WHERE org_id = g_batch_details.org_id
1648 AND TRUNC(agreement_start_date) <= TRUNC(g_batch_details.settlement_date)
1649 AND TRUNC(nvl(agreement_end_date,sysdate)) >= TRUNC(sysdate)
1650 AND agreement_id = nvl(g_agreement_id,agreement_id);
1651
1652 BEGIN
1653
1654 l_path := g_path||'Create_Net_Batch';
1655 fun_net_util.Log_String(g_event_level,l_path,'Creating Netting batches');
1656
1657 x_msg_count := NULL;
1658 x_msg_data := NULL;
1659 g_user_id := fnd_global.user_id;
1660 g_login_id := fnd_global.login_id;
1661
1662 -- **** Standard start of API savepoint ****
1663 SAVEPOINT create_net_batch_SP;
1664
1665 -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
1666 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1667 FND_MSG_PUB.initialize;
1668 END IF;
1669
1670 -- **** Initialize return status to SUCCESS *****
1671 x_return_status := FND_API.G_RET_STS_SUCCESS;
1672
1673 /*-----------------------------------------------+
1674 | ======== START OF API BODY ============ |
1675 +-----------------------------------------------*/
1676
1677 /* Check for mandatory parameters */
1678
1679 IF p_batch_id IS NULL THEN
1680 RAISE FND_API.G_EXC_ERROR;
1681 END IF;
1682
1683 g_batch_id := p_batch_id;
1684
1685 --Call the procedure to get the batch details
1686 fun_net_util.Log_String(g_state_level,l_path,'Fetching batch details');
1687 IF NOT get_batch_details THEN
1688 fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
1689 RAISE FND_API.G_EXC_ERROR;
1690 END IF;
1691
1692 --If the call is successful then call the procedure to update batch status
1693 IF NOT update_batch_status('RUNNING') THEN
1694 fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status');
1695 RAISE FND_API.G_EXC_ERROR;
1696 END IF; -- Return Code
1697
1698 --If the call is successful then call the procedure to get all agreement details. If agreement id is null , then loop through each agreement and select tnx for every agreement.
1699 -- If the call is successful then get the agreement details and select transactions for every agreement
1700 fun_net_util.Log_String(g_state_level,l_path,'Before processing the agreements');
1701 get_functional_currency;
1702 g_agreement_id := g_batch_details.agreement_id;
1703 g_agreement_count := 0;
1704 g_currency_count := 0;
1705
1706 FOR agr_rec IN c_agreement_cur
1707 LOOP
1708 /* Reset the global varaibles for every agreement */
1709 fun_net_util.Log_String(g_state_level,l_path,'Agreement_id:'||agr_rec.agreement_id);
1710 g_agreement_id := agr_rec.agreement_id;
1711 g_net_currency_rule := agr_rec.net_currency_rule_code;
1712 g_net_order_rule := agr_rec.net_order_rule_code;
1713 g_net_balance_rule := agr_rec.net_balance_rule_code;
1714 g_shikyu_rule := nvl(agr_rec.shikyu_rule_code,'D');
1715 g_bank_account_id := agr_rec.bank_account_id;
1716 g_net_currency := agr_rec.net_currency_code;
1717 g_agr_start_date := agr_rec.agreement_start_date;
1718 g_agr_end_date := agr_rec.agreement_end_date;
1719 g_days_past_due := nvl(agr_rec.days_past_due,0);
1720 g_sel_past_due_flag := agr_rec.sel_rec_past_due_txns_flag;
1721
1722
1723 IF g_agr_end_date IS NULL THEN
1724 g_agr_end_date := to_date('31-12-9999','DD-MM-YYYY');
1725 END IF;
1726
1727 /*If this is the first agreement then assign the current batch id to the Batch id Table */
1728 g_agreement_count := g_agreement_count + 1;
1729 IF g_agreement_count = 1 THEN
1730 g_idx := 1;
1731 g_batch_list(g_idx).batch_id := g_batch_id;
1732 g_batch_list(g_idx).agreement_id := g_agreement_id;
1733 END IF;
1734
1735
1736 /*IF NOT get_agreement_details THEN
1737 RAISE G_EXC_ERROR;
1738 END IF;*/
1739
1740 --g_currency_count := 0;
1741 --If the call is successful then call the procedure to select all customer transactions --and insert them into the customer transactions table
1742 IF NOT prepare_ar_transactions THEN
1743 fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AR Transactions');
1744 RAISE FND_API.G_EXC_ERROR;
1745 END IF;
1746
1747 --If the call is successful then call the procedure to select all supplier transactions and insert them into the supplier invoices table.
1748 IF NOT prepare_ap_transactions THEN
1749 fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AP transactions');
1750 RAISE FND_API.G_EXC_ERROR;
1751 END IF;
1752
1753 END LOOP;
1754
1755 fun_net_util.Log_String(g_state_level,l_path,'Processing the batches');
1756 FOR i IN 1..g_idx
1757 LOOP
1758 g_batch_id := g_batch_list(i).batch_id;
1759 IF NOT get_batch_details THEN
1760 fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
1761 RAISE FND_API.G_EXC_ERROR;
1762 END IF;
1763
1764 g_agreement_id := g_batch_list(i).agreement_id;
1765 IF NOT get_agreement_details THEN
1766 fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching agreement details');
1767 RAISE FND_API.G_EXC_ERROR;
1768 END IF;
1769
1770 fun_net_util.Log_String(g_state_level,l_path,'Batch ID:'||g_batch_id);
1771
1772 -- verify currency rates
1773
1774 validate_exch_rate(l_return_status);
1775
1776
1777 IF l_return_status = FND_API.G_FALSE THEN
1778
1779 fun_net_util.Log_String(g_event_level,l_path, 'validate_exch_rate returns false');
1780 RAISE FND_API.G_EXC_ERROR;
1781
1782 END IF;
1783
1784
1785 calculate_AP_AR_balances(l_amt_to_net,l_status_flag);
1786
1787 /* Set the status of the Batch to Selected */
1788 IF l_status_flag = FND_API.G_TRUE THEN
1789 UPDATE fun_net_batches_all
1790 SET batch_status_code = 'SELECTED',
1791 batch_currency = g_batch_list(i).currency,
1792 agreement_id = g_batch_list(i).agreement_id,
1793 total_netted_amt = l_amt_to_net
1794 WHERE batch_id = g_batch_id;
1795 g_batch_details.batch_status_code := 'SELECTED';
1796 ELSE
1797 UPDATE fun_net_batches_all
1798 SET batch_status_code = 'ERROR',
1799 batch_currency = g_batch_list(i).currency,
1800 agreement_id = g_batch_list(i).agreement_id,
1801 total_netted_amt = l_amt_to_net
1802 WHERE batch_id = g_batch_id;
1803 g_batch_details.batch_status_code := 'ERROR';
1804
1805 END IF;
1806 /* IF NOT update_batch_status('SELECTED') THEN
1807 fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status to SELECTED');
1808 NULL;
1809 END IF; */
1810
1811 /* Check if Auto submission is on If not call the Submit API process*/
1812 /* VDOBREV: Bug 5003118 IF g_batch_details.review_netting_batch_flag = 'N' */
1813 IF g_batch_details.review_netting_batch_flag = 'Y'
1814 AND g_batch_details.batch_status_code = 'SELECTED' THEN
1815 fun_net_util.Log_String(g_state_level,l_path,'Submitting Netting Batch');
1816 submit_net_batch (
1817 p_init_msg_list => FND_API.G_FALSE,
1818 p_commit => FND_API.G_FALSE,
1819 x_return_status => l_return_status,
1820 x_msg_count => l_msg_count,
1821 x_msg_data => l_msg_data,
1822 p_batch_id => g_batch_id);
1823 END IF;
1824 END LOOP;
1825
1826 -- Standard check of p_commit.
1827 fun_net_util.Log_String(g_state_level,l_path,'p_commit :'|| p_commit);
1828 IF FND_API.To_Boolean( p_commit ) THEN
1829 fun_net_util.Log_String(g_state_level,l_path,'Saving the batches');
1830 COMMIT WORK;
1831 END IF;
1832
1833 EXCEPTION
1834 WHEN FND_API.G_EXC_ERROR THEN
1835
1836 fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1837 ROLLBACK TO create_net_batch_SP;
1838 batch_status_flag := update_batch_status('ERROR');
1839 x_return_status := FND_API.G_RET_STS_ERROR;
1840 FND_MSG_PUB.Count_And_Get (
1841 p_count => x_msg_count,
1842 p_data => x_msg_data );
1843
1844 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1845
1846 fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1847 ROLLBACK TO create_net_batch_SP;
1848 batch_status_flag := update_batch_status('ERROR');
1849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1850 FND_MSG_PUB.Count_And_Get (
1851 p_count => x_msg_count,
1852 p_data => x_msg_data );
1853
1854 WHEN OTHERS THEN
1855
1856 fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1857 ROLLBACK TO Create_Net_Batch_SP;
1858 batch_status_flag := update_batch_status('ERROR');
1859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1860 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1861 --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
1862 FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'create_net_batch');
1863 END IF;
1864 FND_MSG_PUB.Count_And_Get (
1865 p_count => x_msg_count,
1866 p_data => x_msg_data );
1867
1868 END create_net_batch;
1869
1870
1871
1872 PROCEDURE Validate_multi_currency_flag IS
1873 l_count number(3);
1874 l_flag varchar2(3);
1875 l_batch_currency fun_net_batches_all.batch_currency%type;
1876
1877
1878 BEGIN
1879 l_count:=0;
1880 l_flag := 'N';
1881
1882 if(g_net_currency_rule<>'ACCOUNTING_CURRENCY') then
1883
1884 if(g_net_currency_rule='SINGLE_CURRENCY') then
1885 begin
1886 select batch_currency into l_batch_currency
1887 from fun_net_batches_all
1888 where batch_id = g_batch_id;
1889 exception
1890 when others then
1891 RAISE FND_API.G_EXC_ERROR;
1892 end;
1893
1894 if l_batch_currency<> g_func_currency then
1895 l_count:=1;
1896 end if;
1897
1898 elsif (g_net_currency_rule = 'WITHIN_CURRENCY') then
1899
1900 SELECT count (DISTINCT rac.invoice_currency_code)
1901 into l_count
1902 FROM ra_customer_trx_all rac,fun_net_ar_txns_all fnar
1903 WHERE rac.customer_trx_id = fnar.customer_trx_id
1904 AND fnar.batch_id = g_batch_id
1905 AND rac.invoice_currency_code <> g_func_currency;
1906
1907
1908 end if;
1909
1910 if( l_count > 0 ) then
1911 begin
1912 select cba.receipt_multi_currency_flag
1913 into l_flag
1914 from ce_bank_accounts cba, ce_bank_acct_uses_ALL ba,ar_receipt_classes rc,
1915 ar_receipt_methods rm,ar_receipt_method_accounts_ALL rma
1916 where rc.creation_method_code = 'NETTING'
1917 and rc.receipt_class_id = rm.receipt_class_id
1918 and ba.bank_account_id = cba.bank_account_id
1919 and rm.receipt_method_id = rma.receipt_method_id
1920 and rma.remit_bank_acct_use_id = ba.bank_acct_use_id;
1921 exception
1922 when others then
1923 RAISE FND_API.G_EXC_ERROR;
1924 end;
1925 if ( l_flag <>'Y' ) then
1926 FND_MESSAGE.SET_NAME('FUN','FUN_NET_MULTI_CUR_FLAG_ERR');
1927 RAISE FND_API.G_EXC_ERROR;
1928 end if;
1929
1930 end if;
1931
1932
1933 END IF;
1934
1935
1936 END Validate_multi_currency_flag;
1937
1938
1939 PROCEDURE submit_net_batch (
1940 -- ***** Standard API Parameters *****
1941 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1942 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1943 x_return_status OUT NOCOPY VARCHAR2,
1944 x_msg_count OUT NOCOPY NUMBER,
1945 x_msg_data OUT NOCOPY VARCHAR2,
1946 -- ***** Netting batch input parameters *****
1947 p_batch_id IN NUMBER) IS
1948
1949 l_ap_bal fun_net_ap_invs_all.open_amt%TYPE;
1950 l_ar_bal fun_net_ar_txns_all.open_amt%TYPE;
1951 l_status_flag VARCHAR2(1);
1952 l_TP_approver fun_net_agreements_all.approver_name%TYPE;
1953 l_return_status VARCHAR2(1);
1954 l_msg_count NUMBER;
1955 l_msg_data VARCHAR2(2000);
1956 l_batch_status fun_net_batches_all.settlement_date%TYPE;
1957 l_Request_id fnd_concurrent_requests.request_id%TYPE;
1958 l_batch_status_flag BOOLEAN;
1959
1960
1961 CURSOR c_TP_approver IS
1962 SELECT approver_name
1963 FROM FUN_NET_AGREEMENTS
1964 WHERE agreement_id = g_agreement_id;
1965 l_path VARCHAR2(100);
1966 BEGIN
1967 l_path := g_path||'submit_net_batch';
1968 fun_net_util.Log_String(g_state_level,l_path,'Start submit batch');
1969 /* Intialize standard API parameters */
1970 /* Intialize message list */
1971 x_msg_count := NULL;
1972 x_msg_data := NULL;
1973
1974 -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
1975 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1976 FND_MSG_PUB.initialize;
1977 END IF;
1978
1979 -- **** Initialize return status to SUCCESS *****
1980 x_return_status := FND_API.G_RET_STS_SUCCESS;
1981
1982 /* Create Save point */
1983 SAVEPOINT submit_net_batch_SP;
1984
1985 /* Validate Mandatory parameters */
1986 /* Get Batch Details for the given batch id*/
1987 IF p_batch_id IS NULL THEN
1988
1989 fun_net_util.Log_String(g_state_level,l_path,'Batch id passed to submit netting batch procedure is null');
1990 RAISE FND_API.G_EXC_ERROR;
1991 END IF;
1992
1993 g_batch_id := p_batch_id;
1994 IF NOT get_batch_details THEN
1995
1996 fun_net_util.Log_String(g_state_level,l_path,'Unable to get batch details for the netting batch with batch id :'||g_batch_id);
1997 RAISE FND_API.G_EXC_ERROR;
1998 END IF;
1999
2000 fun_net_util.Log_String(g_state_level,l_path,'Validating batch');
2001 /* Compares Final AP and AR Balances. Need to check the balance as the batch might have been modified by the user */
2002 g_agreement_id := g_batch_details.agreement_id;
2003
2004 IF NOT get_agreement_details THEN
2005
2006 fun_net_util.Log_String(g_event_level,l_path,
2007 'Error getting Agreement details');
2008 RAISE FND_API.G_EXC_ERROR;
2009 END IF;
2010
2011
2012 Validate_multi_currency_flag;
2013
2014 Validate_AP_AR_balances(l_ar_bal,l_ap_bal,l_status_flag);
2015
2016 -- Check for negative AP or AR balances
2017
2018 IF l_ar_bal < 0 or l_ap_bal < 0 THEN
2019 FND_MESSAGE.SET_NAME('FUN','FUN_NET_BATCH_NEG_AP_AR_BAL');
2020 RAISE FND_API.G_EXC_ERROR;
2021 END IF;
2022
2023 IF g_net_balance_rule='NET_PAYABLES' and l_ar_bal > l_ap_bal THEN
2024
2025 FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_BAL_LESS_THAN_AP');
2026 RAISE FND_API.G_EXC_ERROR;
2027
2028 END IF;
2029
2030
2031
2032 IF l_status_flag = FND_API.G_TRUE then
2033 /*Check for Trading Partner Approval and set status to Submitted if TP approval is required*/
2034 IF g_batch_details.settlement_date < TRUNC(SYSDATE) THEN
2035 fun_net_util.Log_String(g_state_level,l_path,'Setting status to SUSPENDED');
2036 UPDATE fun_net_batches
2037 SET batch_status_code = 'SUSPENDED'
2038 WHERE batch_id = g_batch_id;
2039 g_batch_details.batch_status_code := 'SUSPENDED';
2040 ELSE
2041 OPEN c_TP_approver;
2042 FETCH c_TP_approver INTO l_TP_approver;
2043 CLOSE c_TP_APPROVER;
2044
2045 IF l_TP_approver IS NOT NULL THEN
2046 IF g_batch_details.batch_status_code IN ( 'SELECTED','REJECTED','ERROR' ) THEN
2047 fun_net_util.Log_String(g_state_level,l_path,'Setting status to SUBMITTED');
2048 UPDATE fun_net_batches
2049 SET batch_status_code = 'SUBMITTED'
2050 WHERE batch_id = g_batch_id;
2051 g_batch_details.batch_status_code := 'SUBMITTED';
2052 fun_net_util.Log_String(g_state_level,l_path,'Raising WF business event');
2053 fun_net_approval_wf.raise_approval_event(g_batch_id);
2054 END IF;
2055 ELSIF g_batch_details.batch_status_code IN('SELECTED','ERROR') THEN /* TP Approval is not necessary */
2056 /* If TP approval is not necessary call Netting Settlement Date API */
2057 /*l_batch_status := validate_netting_dates(
2058 p_init_msg_list => FND_API.G_FALSE,
2059 p_commit => FND_API.G_FALSE,
2060 x_return_status => l_return_status,
2061 x_msg_count => l_msg_count,
2062 x_msg_data => l_msg_data,
2063 p_batch_id => g_batch_id,
2064 p_net_settle_date => g_batch_details.settlement_date,
2065 p_response_date => NULL);
2066 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2067
2068 g_batch_details.batch_status_code := l_batch_status;
2069 END IF; */
2070 UPDATE fun_net_batches
2071 SET batch_status_code = 'APPROVED'
2072 WHERE batch_id = g_batch_id;
2073 fun_net_util.Log_String(g_state_level,l_path,'Setting status to APPROVED');
2074 g_batch_details.batch_status_code := 'APPROVED';
2075
2076 END IF;
2077 END IF;
2078 ELSE
2079 RAISE FND_API.G_EXC_ERROR;
2080 END IF;
2081
2082 IF g_batch_details.batch_status_code = 'APPROVED' then
2083 UPDATE fun_net_batches
2084 SET batch_status_code = 'CLEARING'
2085 WHERE batch_id = g_batch_id;
2086 fun_net_util.Log_String(g_state_level,l_path,'Setting status to CLEARING');
2087 --settle_net_batch(g_batch_id);
2088 COMMIT;
2089 l_Request_id := FND_REQUEST.SUBMIT_REQUEST ( 'FUN'
2090 , 'FUNNTSTB'
2091 , null
2092 , null
2093 , FALSE -- Is a sub request
2094 , g_batch_id
2095 );
2096
2097 fun_net_util.Log_String(g_state_level,l_path,'Settle batch request:'||l_request_id);
2098 END IF;
2099
2100 -- Standard check of p_commit.
2101 IF FND_API.To_Boolean( p_commit ) THEN
2102 COMMIT WORK;
2103 END IF;
2104
2105 EXCEPTION
2106 WHEN FND_API.G_EXC_ERROR THEN
2107 ROLLBACK TO submit_net_batch_SP;
2108 l_batch_status_flag := update_batch_status('ERROR');
2109 x_return_status := FND_API.G_RET_STS_ERROR;
2110 FND_MSG_PUB.Count_And_Get (
2111 p_count => x_msg_count,
2112 p_data => x_msg_data );
2113
2114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2115 ROLLBACK TO submit_net_batch_SP;
2116 l_batch_status_flag := update_batch_status('ERROR');
2117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2118 FND_MSG_PUB.Count_And_Get (
2119 p_count => x_msg_count,
2120 p_data => x_msg_data );
2121
2122 WHEN OTHERS THEN
2123 ROLLBACK TO submit_Net_Batch_SP;
2124 l_batch_status_flag := update_batch_status('ERROR');
2125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2126 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2127 --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2128 FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'submit_net_batch');
2129 END IF;
2130 FND_MSG_PUB.Count_And_Get (
2131 p_count => x_msg_count,
2132 p_data => x_msg_data );
2133 END submit_net_batch;
2134
2135
2136 FUNCTION Validate_Netting_Dates(
2137 -- ***** Standard API Parameters *****
2138 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2139 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2140 x_return_status OUT NOCOPY VARCHAR2,
2141 x_msg_count OUT NOCOPY NUMBER,
2142 x_msg_data OUT NOCOPY VARCHAR2,
2143 -- ***** Netting batch input parameters *****
2144 p_batch_id IN NUMBER,
2145 p_net_settle_date IN DATE,
2146 p_response_date IN DATE) RETURN VARCHAR2 IS
2147
2148 l_non_response_action fun_net_agreements_all.non_response_action_code%TYPE;
2149 l_path varchar2(200);
2150 CURSOR c_non_response_action IS
2151 SELECT non_response_action_code
2152 FROM FUN_NET_AGREEMENTS
2153 WHERE agreement_id = g_agreement_id;
2154
2155 BEGIN
2156
2157
2158 l_path := g_path||'batch_exists';
2159 /* Initialize standard API parameters */
2160 x_msg_count := NULL;
2161 x_msg_data := NULL;
2162
2163 -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
2164 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2165 FND_MSG_PUB.initialize;
2166 END IF;
2167
2168 -- **** Initialize return status to SUCCESS *****
2169 x_return_status := FND_API.G_RET_STS_SUCCESS;
2170
2171 /* Create Save point */
2172 SAVEPOINT validate_netting_dates_SP;
2173
2174 /* Validate Mandatory parameters */
2175 /* Get Batch Details for the given batch id*/
2176 IF p_batch_id IS NULL OR (p_net_settle_date IS NULL AND p_response_date IS NULL) THEN
2177
2178 fun_net_util.Log_String(g_state_level,l_path,'One of the mandatory arguments passed to the validate_netting_dates procedure is null');
2179 RAISE FND_API.G_EXC_ERROR;
2180 END IF;
2181
2182 g_batch_id := p_batch_id;
2183 /* Validate NSD with the date of
2184 when the Netting Batch is changed from Submitted to Approved.
2185 This will be called by workflow when the TP approves a batch
2186 and also by the Submit Netting Batch API when TP approval is not required .*/
2187 IF p_net_settle_date IS NOT NULL THEN
2188 IF TRUNC(p_net_settle_date) < TRUNC(SYSDATE) THEN
2189
2190 fun_net_util.Log_String(g_state_level,l_path,'The netting batch with batch_id:'||g_batch_id||'has gone past the netting settlement date');
2191 --Error ' Batch has gone past the Netting Settlement Date';
2192 IF NOT Update_batch_status('SUSPENDED') THEN
2193 RAISE FND_API.G_EXC_ERROR;
2194 ELSE
2195 RETURN 'SUSPENDED';
2196 END IF;
2197 ELSE
2198 IF NOT update_batch_status ('APPROVED') THEN
2199 fun_net_util.Log_String(g_state_level,l_path,'Unable to update the batch status to approved');
2200 RAISE FND_API.G_EXC_ERROR;
2201 ELSE
2202 RETURN 'APPROVED';
2203 END IF;
2204 END IF;
2205 END IF;
2206
2207 /* Check for the ' No response' action when there is no response
2208 to the notification and set status accordingly.
2209 This will be called by workflow */
2210 IF p_response_date IS NOT NULL THEN
2211 OPEN c_non_response_action;
2212 FETCH c_non_response_action INTO l_non_response_action;
2213 CLOSE c_non_response_action;
2214
2215 IF l_non_response_action IS NULL THEN
2216 RAISE FND_API.G_EXC_ERROR;
2217 END IF;
2218
2219 IF TRUNC(p_response_date) < TRUNC(SYSDATE) THEN
2220 IF l_non_response_action = 'APPROVE' then
2221 IF NOT Update_batch_status('APPROVED') THEN
2222 RAISE FND_API.G_EXC_ERROR;
2223 END IF;
2224 RETURN 'APPROVED';
2225 ELSIF l_non_response_action = 'REJECT' THEN
2226 IF NOT Update_batch_status('REJECTED') THEN
2227 RAISE FND_API.G_EXC_ERROR;
2228 END IF;
2229 RETURN 'REJECTED';
2230 END IF;
2231 END IF;
2232 END IF;
2233
2234 -- Standard check of p_commit.
2235 IF FND_API.To_Boolean( p_commit ) THEN
2236 COMMIT WORK;
2237 END IF;
2238
2239 EXCEPTION
2240 /* Handle standard exceptions */
2241 WHEN FND_API.G_EXC_ERROR THEN
2242 ROLLBACK TO validate_netting_dates_SP;
2243 x_return_status := FND_API.G_RET_STS_ERROR;
2244 FND_MSG_PUB.Count_And_Get (
2245 p_count => x_msg_count,
2246 p_data => x_msg_data );
2247
2248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2249 ROLLBACK TO validate_netting_dates_SP;
2250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2251 FND_MSG_PUB.Count_And_Get (
2252 p_count => x_msg_count,
2253 p_data => x_msg_data );
2254
2255 WHEN OTHERS THEN
2256 ROLLBACK TO validate_netting_dates_SP;
2257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2258 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2259 --FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2260 FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'validate_netting_dates');
2261 END IF;
2262 FND_MSG_PUB.Count_And_Get (
2263 p_count => x_msg_count,
2264 p_data => x_msg_data );
2265
2266 END Validate_Netting_Dates;
2267
2268 PROCEDURE Set_Agreement_Status(
2269 x_batch_id IN fun_net_batches.batch_id%TYPE,
2270 x_agreement_id IN fun_net_agreements.agreement_id%TYPE,
2271 x_mode IN VARCHAR2,
2272 x_return_status OUT NOCOPY VARCHAR2)
2273 IS
2274 l_agreement_id fun_net_agreements.agreement_id%TYPE;
2275 BEGIN
2276 x_return_status := FND_API.G_TRUE;
2277 IF x_agreement_id IS NOT NULL THEN
2278
2279 SELECT agreement_id
2280 INTO l_agreement_id
2281 FROM fun_net_batches
2282 WHERE agreement_id = x_agreement_id
2283 AND batch_id <> x_batch_id
2284 AND batch_status_code IN ('RUNNING',
2285 'SELECTED',
2286 'SUBMITTED',
2287 'REVERSING',
2288 'CLEARING');
2289
2290 ELSE
2291 x_return_status := FND_API.G_FALSE;
2292 END IF;
2293
2294 EXCEPTION
2295 WHEN TOO_MANY_ROWS THEN
2296 null;
2297
2298 WHEN NO_DATA_FOUND THEN
2299 IF x_mode = 'SET' THEN
2300
2301 FUN_NET_AGREEMENTS_PKG.Update_Row(
2302 x_agreement_id => x_agreement_id,
2303 x_in_process_flag => 'Y');
2304 ELSIF x_mode = 'UNSET' THEN
2305 FUN_NET_AGREEMENTS_PKG.Update_Row(
2306 x_agreement_id => x_agreement_id,
2307 x_in_process_flag => 'N');
2308 END IF;
2309
2310 WHEN OTHERS THEN
2311 x_return_status := FND_API.G_FALSE;
2312 END Set_Agreement_Status;
2313
2314 /* Derive Bank Account Information. Find the Bank Account Use Id
2315 from the Bank Account Id. Netting Bank is stored in the agreement details */
2316
2317 PROCEDURE Get_Netting_Bank_Details(
2318 p_bank_acct_use_id OUT NOCOPY NUMBER,
2319 p_bank_acct_name OUT NOCOPY VARCHAR2,
2320 p_bank_acct_num OUT NOCOPY ce_bank_accounts.bank_account_num%TYPE,
2321 p_le_id OUT NOCOPY NUMBER,
2322 p_bank_num OUT NOCOPY VARCHAR2,
2323 p_return_status OUT NOCOPY VARCHAR2)
2324 IS
2325 BEGIN
2326 p_return_status := FND_API.G_TRUE;
2327 SELECT bank_acct_use_id ,
2328 ac.bank_account_name,
2329 ac.bank_account_num,
2330 ac.account_owner_org_id,
2331 ba.bank_number
2332 INTO p_bank_acct_use_id,
2333 p_bank_acct_name,
2334 p_bank_acct_num,
2335 p_le_id,
2336 p_bank_num
2337 FROM ce_bank_acct_uses ac_use,
2338 ce_bank_accounts ac,
2339 ce_banks_v ba
2340 WHERE ac.bank_account_id = g_bank_account_id
2341 AND ac.bank_account_id = ac_use.bank_account_id
2342 AND ac_use.org_id = g_batch_details.org_id
2343 AND ba.bank_party_id = ac.bank_id;
2344
2345 EXCEPTION
2346
2347 WHEN OTHERS THEN
2348 p_return_status := FND_API.G_FALSE;
2349
2350 END Get_Netting_Bank_Details;
2351
2352 /* Get the Customer Details to Create Receipt . If there is more than
2353 one customer with the same priority then select the customer with
2354 minimum customer id */
2355
2356 PROCEDURE Get_Customer_Details (p_cust_acct_id OUT NOCOPY NUMBER,
2357 p_cust_site_use_id OUT NOCOPY NUMBER,
2358 p_return_status OUT NOCOPY VARCHAR2)
2359 IS
2360 BEGIN
2361 p_return_status := FND_API.G_TRUE;
2362
2363 /* Select First Customer Account on Receipt */
2364
2365
2366 SELECT min(cust_account_id)
2367 INTO p_cust_acct_id
2368 FROM fun_net_customers ca
2369 WHERE ca.agreement_id = g_agreement_id
2370 AND cust_priority = 1;
2371
2372
2373 -- Bug 6982905. added AND u.org_id = fc.org_id to the query
2374 SELECT u.site_use_id
2375 INTO p_cust_site_use_id
2376 FROM
2377 fun_net_customers fc,
2378 hz_cust_acct_sites s,
2379 hz_cust_site_uses u
2380 WHERE
2381 fc.agreement_id = g_agreement_id
2382 AND
2383 fc.cust_account_id = p_cust_acct_id
2384 AND fc.cust_account_id = s.cust_account_id
2385 AND s.cust_acct_site_id = u.cust_acct_site_id
2386 AND ( (u.site_use_id = fc.cust_site_use_id AND
2387 u.primary_flag='Y'
2388 and u.site_use_code='BILL_TO')
2389 OR ( u.site_use_code = 'BILL_TO'
2390 AND u.primary_flag = 'Y'
2391 AND fc.cust_site_use_id IS NULL))
2392 AND u.org_id = fc.org_id ;
2393
2394
2395
2396 EXCEPTION
2397 WHEN OTHERS THEN
2398 p_return_status := FND_API.G_FALSE;
2399 END Get_Customer_Details;
2400
2401 /* Unlocks Payment Schedule lines */
2402
2403 PROCEDURE unlock_ap_pymt_schedules(
2404 p_batch_id IN fun_net_batches.batch_id%TYPE,
2405 x_return_status OUT NOCOPY VARCHAR2)
2406 IS
2407 PRAGMA AUTONOMOUS_TRANSACTION;
2408 l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
2409 l_path varchar2(200);
2410 l_org_id NUMBER(15);
2411 BEGIN
2412
2413 x_return_status := FND_API.G_TRUE;
2414 l_path := g_path||'unlock_payment_schedules';
2415 -- Check for Mandatory Parameters
2416 IF p_batch_id IS NULL THEN
2417 x_return_status := FND_API.G_FALSE;
2418 END IF;
2419
2420 -- Select the checkrun ID for the
2421 -- Netting Batch
2422
2423 SELECT checkrun_id , org_id
2424 INTO l_checkrun_id , l_org_id
2425 FROM fun_net_batches_all
2426 WHERE batch_id = p_batch_id ;
2427
2428
2429 UPDATE AP_PAYMENT_SCHEDULES_ALL
2430 SET checkrun_id = NULL
2431 WHERE checkrun_id =l_checkrun_id
2432 AND org_id = l_org_id;
2433 IF SQL%FOUND THEN
2434 COMMIT;
2435 ELSIF SQL%NOTFOUND THEN
2436
2437 ROLLBACK;
2438 RETURN;
2439 END IF;
2440
2441
2442 UPDATE FUN_NET_BATCHES_ALL
2443 SET checkrun_id = NULL
2444 WHERE batch_id = p_batch_id ;
2445 IF SQL%FOUND THEN
2446 COMMIT;
2447 ELSE
2448 ROLLBACK;
2449
2450 x_return_status := FND_API.G_FALSE;
2451 RETURN;
2452 END IF;
2453 EXCEPTION
2454 WHEN OTHERS THEN
2455 fun_net_util.Log_String(g_state_level,l_path,'error while unlocking payment schedules');
2456 x_return_status := FND_API.G_FALSE;
2457 END unlock_ap_pymt_schedules;
2458
2459 /* Derives Netting Exchange Rate . Returns 1 if the from and the to
2460 currency are the same . If the Rate type = 'User' then derives the
2461 rate from fun_net_batches table */
2462
2463 FUNCTION Derive_Net_Exchg_Rate(x_from_currency IN VARCHAR2,
2464 x_to_currency IN VARCHAR2)
2465 RETURN NUMBER
2466 IS
2467 x_exchange_rate fun_net_batches_all.exchange_rate%TYPE;
2468 l_exchange_rate_type fun_net_batches_all.exchange_rate_type%TYPE;
2469
2470 l_path varchar2(100);
2471 BEGIN
2472 l_path := g_path || 'Derive net exchange';
2473 IF x_from_currency = x_to_currency THEN
2474 x_exchange_rate := 1.0;
2475 RETURN x_exchange_rate;
2476 END IF;
2477
2478 fun_net_util.Log_String(g_state_level,l_path,'x_from_currency:'||x_from_currency);
2479
2480 fun_net_util.Log_String(g_state_level,l_path,'x_to_currency:'||x_to_currency);
2481
2482
2483 fun_net_util.Log_String(g_state_level,l_path,g_batch_details.exchange_rate_type);
2484
2485 IF g_batch_details.exchange_rate_type = 'User' then
2486 x_exchange_rate := g_batch_details.exchange_rate;
2487 ELSIF g_batch_details.exchange_rate_type IS NOT NULL THEN
2488 l_exchange_rate_type := g_batch_details.exchange_rate_type;
2489 ELSE
2490 SELECT default_exchange_rate_type
2491 INTO l_exchange_rate_type
2492 FROM ap_system_parameters_all
2493 WHERE org_id = g_batch_details.org_id;
2494
2495 END IF;
2496
2497 IF l_exchange_rate_type IS NOT NULL THEN
2498 IF gl_currency_api.rate_exists(
2499 x_from_currency,
2500 x_to_currency,
2501 g_batch_details.settlement_date,
2502 g_batch_details.exchange_rate_type) = 'Y' THEN
2503 x_exchange_rate := gl_currency_api.get_rate(
2504 x_from_currency,
2505 x_to_currency,
2506 g_batch_details.settlement_date,
2507 g_batch_details.exchange_rate_type);
2508 ELSE
2509 RETURN null;
2510 END IF;
2511
2512 END IF;
2513 RETURN x_exchange_rate;
2514 EXCEPTION
2515 WHEN OTHERS THEN
2516 RETURN NULL;
2517 END Derive_Net_Exchg_Rate;
2518
2519 PROCEDURE Validate_Settlement_Period(
2520 x_appln_id IN fnd_application.application_id%TYPE,
2521 x_period_name OUT NOCOPY VARCHAR2,
2522 x_return_status OUT NOCOPY VARCHAR2,
2523 x_return_msg OUT NOCOPY VARCHAR2)
2524
2525 IS
2526 l_ledger_id gl_ledgers.ledger_id%TYPE;
2527 x_closing_status gl_period_statuses.closing_status%TYPE;
2528 x_period_year gl_period_statuses.period_year%TYPE;
2529 x_period_num gl_period_statuses.period_num%TYPE;
2530 x_period_type gl_period_statuses.period_type%TYPE;
2531 l_path varchar2(200);
2532
2533 BEGIN
2534
2535
2536 l_path := g_path||'validate_settlement_period';
2537 /* Check if GL Period is open*/
2538 x_return_status := FND_API.G_TRUE;
2539
2540 SELECT set_of_books_id
2541 INTO l_ledger_id
2542 FROM hr_operating_units
2543 WHERE organization_id = g_batch_details.org_id;
2544
2545 GL_PERIOD_STATUSES_PKG.get_period_by_date(
2546 x_appln_id,
2547 l_ledger_id,
2548 nvl(g_batch_details.gl_date,g_batch_details.settlement_date),
2549 x_period_name,
2550 x_closing_status,
2551 x_period_year,
2552 x_period_num,
2553 x_period_type);
2554
2555 IF (x_period_name IS NULL and x_closing_status IS NULL) OR
2556 x_closing_status not in ('O','F') THEN
2557 x_return_status := FND_API.G_FALSE;
2558 END IF;
2559 EXCEPTION
2560 WHEN OTHERS THEN
2561
2562 fun_net_util.Log_String(g_state_level,l_path,'The GL period for the settlement of netting batch is closed or not yet opened ');
2563 x_return_status := FND_API.G_FALSE;
2564 END Validate_Settlement_Period;
2565 /* Creates AP Check per vendor / vendor site / currency . Also creates
2566 invoice payments for each AP Check */
2567
2568 PROCEDURE settle_ap_invs(
2569 p_bank_acct_use_id IN ce_bank_acct_uses_all.bank_acct_use_id%TYPE,
2570 p_bank_acct_name IN ce_bank_accounts.bank_account_name%TYPE,
2571 p_bank_acct_num IN ce_bank_accounts.bank_account_num%TYPE,
2572 p_le_id IN xle_entity_profiles.legal_entity_id%TYPE,
2573 p_bank_num IN ce_banks_v.bank_number%TYPE,
2574 x_return_status OUT NOCOPY VARCHAR2)
2575 IS
2576 l_vendor_name po_vendors.vendor_name%TYPE;
2577 l_vendor_site_code po_vendor_sites_all.vendor_site_code%TYPE;
2578 l_check_id ap_checks.check_id%TYPE;
2579 l_old_invoice ap_invoices.invoice_id%TYPE;
2580 amt_remaining ap_payment_schedules.amount_remaining%TYPE;
2581 l_bank_num ce_banks_v.bank_number%TYPE;
2582 l_return_status VARCHAR2(1);
2583 m integer;
2584 l_path VARCHAR2(100);
2585 l_gl_date date; -- Bug: 7639863
2586
2587 TYPE vendor_type IS TABLE OF po_vendors.vendor_id%TYPE INDEX BY BINARY_INTEGER;
2588 TYPE vendor_site_type IS TABLE OF po_vendor_sites_all.vendor_site_id%TYPE INDEX BY BINARY_INTEGER;
2589 TYPE currency_type IS TABLE OF fnd_currencies.currency_code%TYPE INDEX BY BINARY_INTEGER;
2590 TYPE ap_check_amt_type IS TABLE OF ap_checks.amount%TYPE INDEX BY BINARY_INTEGER;
2591 TYPE ap_invoice_type IS TABLE OF ap_invoices.invoice_id%TYPE INDEX BY BINARY_INTEGER;
2592 TYPE party_type IS TABLE OF ap_invoices_all.party_id%TYPE INDEX BY BINARY_INTEGER;
2593 TYPE party_site_type IS TABLE OF ap_invoices_all.party_site_id%TYPE INDEX BY BINARY_INTEGER;
2594
2595 ap_check_amt_list ap_check_amt_type;
2596 ap_check_base_amt_list ap_check_amt_type;
2597 vendor_list vendor_type;
2598 vendor_site_list vendor_site_type;
2599 currency_list currency_type;
2600 amtDueTab ap_check_amt_type;
2601 ap_invoice ap_invoice_type;
2602 party_list party_type;
2603 party_site_list party_site_type;
2604
2605 ap_payment_info_tab AP_PAYMENT_PUBLIC_PKG.Invoice_Payment_Info_Tab;
2606 ap_check_rec ap_checks_all%ROWTYPE;
2607 p_schd_tab pymt_sch_tab;
2608
2609 BEGIN
2610
2611 -- Group Invoices by Vendor , Vendor Site and Payment Currency .
2612 x_return_status := FND_API.G_TRUE;
2613
2614 BEGIN
2615 l_path := g_path || 'Settle AP Transactions';
2616 fun_net_util.Log_String(g_event_level,l_path,'Group AP Invoices');
2617 SELECT
2618 sum(finv.inv_curr_net_amt) AS pymt_amt,
2619 -- sum(finv.netted_amt) AS base_pymt_amt,
2620 inv.vendor_id,
2621 inv.vendor_site_id,
2622 inv.party_id,
2623 inv.party_site_id,
2624 inv.payment_currency_code
2625 BULK COLLECT INTO ap_check_amt_list,
2626 -- ap_check_base_amt_list,
2627 vendor_list,
2628 vendor_site_list,
2629 party_list,
2630 party_site_list,
2631 currency_list
2632 FROM fun_net_ap_invs finv,
2633 ap_invoices inv
2634 WHERE inv.invoice_id = finv.invoice_id
2635 AND finv.batch_id = g_batch_details.batch_id
2636 AND finv.inv_curr_net_amt <> 0
2637 GROUP BY vendor_id,
2638 vendor_site_id,
2639 party_id,
2640 party_site_id,
2641 inv.payment_currency_code;
2642
2643 EXCEPTION
2644 WHEN OTHERS THEN
2645
2646 fun_net_util.Log_String(g_event_level,l_path,sqlcode || sqlerrm);
2647 RAISE FND_API.G_EXC_ERROR;
2648 END;
2649
2650
2651
2652 fun_net_util.Log_String(g_event_level,l_path,'Processing Txns by Vendor'||
2653 vendor_list.count);
2654
2655 FOR i IN 1..vendor_list.count
2656 LOOP
2657
2658
2659 fun_net_util.Log_String(g_event_level,l_path,'vendor_id'|| vendor_list(i));
2660
2661 /* Get Vendor Name and Vendor Site Name */
2662
2663 ap_check_rec.VENDOR_NAME := PO_VENDORS_SV.get_vendor_name_func(
2664 vendor_list(i));
2665
2666 fun_net_util.Log_String(g_event_level,l_path,'vendor_site_id'||vendor_site_list(i));
2667
2668
2669
2670 PO_VENDOR_SITES_SV.get_vendor_site_name(
2671 vendor_site_list(i),
2672 ap_check_rec.vendor_site_code);
2673
2674 ap_check_rec.vendor_id := vendor_list(i);
2675 ap_check_rec.vendor_site_id := vendor_site_list(i);
2676
2677
2678 /* Get Check Number from Sequence */
2679
2680 SELECT fun.fun_net_ap_checks_s.nextval
2681 INTO ap_check_rec.check_number
2682 FROM DUAL;
2683
2684 ap_check_rec.CHECK_DATE := g_batch_details.settlement_date;
2685 l_gl_date := nvl(g_batch_details.gl_date, g_batch_details.settlement_date); /* 7639863 */
2686
2687 IF currency_list(i) = g_func_currency THEN
2688 ap_check_rec.exchange_rate := 1.0;
2689 ELSE
2690 -- ap_check_rec.BASE_AMOUNT := ap_check_base_amt_list(i);
2691 ap_check_rec.exchange_rate := Derive_Net_Exchg_rate(
2692 currency_list(i),
2693 g_func_currency);
2694
2695 ap_check_rec.BASE_AMOUNT := ap_check_amt_list(i)*ap_check_rec.exchange_rate;
2696 END IF;
2697
2698 ap_check_rec.exchange_rate_type := g_batch_details.exchange_rate_type;
2699 ap_check_rec.EXCHANGE_DATE := g_batch_details.settlement_date;
2700
2701 ap_check_rec.currency_code := currency_list(i);
2702 -- ap_check_rec.amount := (ap_check_amt_list(i)/ap_check_rec.exchange_rate);
2703
2704 fun_net_util.Log_String(g_event_level,l_path,'ap_check_amt_list(i) :'|| ap_check_amt_list(i));
2705
2706
2707 ap_check_rec.amount := ap_check_amt_list(i);
2708 -- ap_check_rec.BASE_AMOUNT := ap_check_amt_list(i) ;
2709 --ap_check_rec.cleared_amount := ap_check_rec.amount;
2710 --ap_check_rec.cleared_date := ap_check_rec.check_date;
2711 ap_check_rec.CHECKRUN_ID := g_batch_details.checkrun_id;
2712
2713 ap_check_rec.CE_BANK_ACCT_USE_ID:= p_bank_acct_use_id;
2714 ap_check_rec.BANK_ACCOUNT_ID := g_bank_account_id;
2715 ap_check_rec.BANK_ACCOUNT_NAME := p_bank_acct_name;
2716
2717 ap_check_rec.party_id := party_list(i);
2718 ap_check_rec.party_site_id := party_site_list(i);
2719
2720 ap_check_rec.LAST_UPDATED_BY := fnd_global.user_id;
2721 ap_check_rec.LAST_UPDATE_DATE := sysdate;
2722 ap_check_rec.CREATED_BY := fnd_global.user_id;
2723 ap_check_rec.CREATION_DATE := sysdate;
2724 ap_check_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
2725
2726 ap_check_rec.PAYMENT_TYPE_FLAG := 'N';
2727 ap_check_rec.PAYMENT_METHOD_LOOKUP_CODE := 'N';
2728
2729 ap_check_rec.ORG_ID := g_batch_details.org_id;
2730 ap_check_rec.LEGAL_ENTITY_ID := p_le_id;
2731 ap_check_rec.CHECKRUN_NAME := g_batch_details.batch_number;
2732
2733 /* Selects invoices per vendor. Amt to be paid reflects the total amount to to be paid per invoice. Calculate the amt to be paid per installment */
2734
2735
2736
2737 fun_net_util.Log_String(g_event_level,
2738 l_path,
2739 ' checkrun_id :'||g_batch_details.checkrun_id ||
2740 ' batch_id :'||g_batch_details.batch_id ||
2741 ' currency :' || currency_list(i) );
2742
2743
2744 BEGIN
2745
2746 SELECT
2747 inv.invoice_id AS invoice_id,
2748 apps.payment_num AS payment_num,
2749 finv.inv_curr_net_amt AS pymt_amt
2750 --Amt in Payment Currency = Invoice Currency
2751 BULK COLLECT INTO
2752 ap_payment_info_tab
2753 FROM
2754 ap_invoices inv,
2755 fun_net_ap_invs finv,
2756 ap_payment_schedules apps
2757 WHERE finv.invoice_id = inv.invoice_id
2758 AND apps.invoice_id = inv.invoice_id
2759 AND apps.checkrun_id = g_batch_details.checkrun_id
2760 AND finv.batch_id = g_batch_details.batch_id
2761 AND inv.vendor_id = vendor_list(i)
2762 AND inv.vendor_site_id = vendor_site_list(i)
2763 AND inv.invoice_currency_code = currency_list(i)
2764 AND finv.inv_curr_net_amt <> 0
2765 ORDER BY inv.invoice_id,apps.payment_num;
2766 EXCEPTION
2767 WHEN OTHERS THEN
2768 RAISE FND_API.G_EXC_ERROR;
2769 END;
2770
2771 m := 0;
2772
2773 IF (ap_invoice.EXISTS(1)) THEN
2774 ap_invoice.DELETE;
2775 END IF;
2776
2777 /* Calculate the Amount to be netted per Payment Schedule */
2778 SELECT amount_remaining
2779 BULK COLLECT INTO amtDueTab
2780 FROM ap_payment_schedules apps,
2781 ap_invoices inv
2782 WHERE apps.invoice_id = inv.invoice_id
2783 AND apps.checkrun_id = g_batch_details.checkrun_id
2784 AND inv.vendor_id = vendor_list(i)
2785 AND inv.vendor_site_id = vendor_site_list(i)
2786 AND inv.invoice_currency_code = currency_list(i)
2787 ORDER BY inv.invoice_id,apps.payment_num;
2788
2789 l_old_invoice := 0;
2790 fun_net_util.Log_String(g_event_level,
2791 l_path
2792 ,'Calculating Amt to be paid by Schedule');
2793
2794
2795
2796 fun_net_util.Log_String(g_event_level,
2797 l_path
2798 ,'ap_payment_info_tab.count:'||
2799 ap_payment_info_tab.count);
2800
2801 FOR j in 1..ap_payment_info_tab.count
2802 LOOP
2803 fun_net_util.Log_String(g_event_level,
2804 l_path,
2805 'j' || j);
2806 fun_net_util.Log_String(
2807 g_event_level,
2808 l_path,
2809 'invoice_id' || ap_payment_info_tab(j).invoice_id);
2810 fun_net_util.Log_String(
2811 g_event_level,
2812 l_path,
2813 'payment num' || ap_payment_info_tab(j).payment_schedule_num);
2814 fun_net_util.Log_String(g_event_level,
2815 l_path,
2816 'Amt to Pay' || ap_payment_info_tab(j).amount_to_pay);
2817 fun_net_util.Log_String(g_event_level,
2818 l_path,
2819 'Amt Remaining' ||amt_remaining);
2820
2821 IF ap_payment_info_tab(j).invoice_id <> l_old_invoice THEN
2822 amt_remaining := ap_payment_info_tab(j).amount_to_pay;
2823 m:= m + 1;
2824 ap_invoice(m) := ap_payment_info_tab(j).invoice_id;
2825 END IF;
2826 IF amt_remaining < 0 THEN
2827 IF amtDueTab(j) >= amt_remaining THEN
2828 ap_payment_info_tab(j).amount_to_pay := amtDueTab(j);
2829 amt_remaining := amt_remaining - amtDuetab(j);
2830 ELSE
2831 ap_payment_info_tab(j).amount_to_pay := amt_remaining;
2832 amt_remaining := 0;
2833 END IF;
2834 ELSE -- Amounts are positive
2835 IF amt_remaining >= amtDueTab(j) THEN
2836 ap_payment_info_tab(j).amount_to_pay := amtDueTab(j);
2837 amt_remaining := amt_remaining - amtDueTab(j);
2838 ELSE
2839 ap_payment_info_tab(j).amount_to_pay := amt_remaining;
2840 amt_remaining := 0 ;
2841 END IF;
2842 END IF; -- Negative Amounts
2843 l_old_invoice := ap_payment_info_tab(j).invoice_id;
2844 fun_net_util.Log_String(g_event_level,
2845 l_path,
2846 'Calc Amt to Pay' || ap_payment_info_tab(j).amount_to_pay);
2847 END LOOP;
2848
2849 /* Call Payment API to create Check and Invoice Payments */
2850
2851 AP_PAYMENT_PUBLIC_PKG.Create_Netting_Payment(
2852 P_Check_Rec => ap_check_rec,
2853 P_Invoice_Payment_Info_Tab => ap_payment_info_tab,
2854 P_Check_ID => l_check_id,
2855 P_Curr_Calling_Sequence => 'Netting Settlement Process',
2856 p_gl_date => l_gl_date) ; /* p_gl_date Added for bug#7639863 */
2857
2858 fun_net_util.Log_String(g_event_level,
2859 l_path,
2860 'Check ID :'||l_check_id );
2861
2862
2863 IF l_check_id is null THEN
2864
2865 x_return_status := FND_API.G_FALSE;
2866 RETURN;
2867 END IF;
2868
2869
2870 /* Update FUN_NET_AP_INVS all with the check Id */
2871
2872 BEGIN
2873 FORALL k IN ap_invoice.FIRST..ap_invoice.LAST
2874
2875 UPDATE FUN_NET_AP_INVS
2876 SET check_id = l_check_id
2877 WHERE batch_id = g_batch_id
2878 AND inv_curr_net_amt <> 0
2879 AND invoice_id = ap_invoice(k);
2880 END;
2881 END LOOP;
2882 EXCEPTION
2883 WHEN OTHERS THEN
2884 x_return_status := FND_API.G_FALSE;
2885 RETURN;
2886 END;
2887
2888 /* Calculates the Total Open Amount for an AR Transaction based on the
2889 Due date of the batch */
2890
2891 FUNCTION Calculate_AR_Txn_Open_Amt(
2892 p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
2893 p_inv_currency_code IN ra_customer_trx.invoice_currency_code%TYPE,
2894 p_exchange_rate IN ra_customer_trx.exchange_rate%TYPE)
2895 RETURN NUMBER
2896 IS
2897 l_amount ar_payment_schedules.amount_due_remaining%TYPE;
2898 BEGIN
2899
2900 SELECT SUM(decode(p_inv_currency_code,
2901 g_batch_details.batch_currency,
2902 amount_due_remaining,
2903 p_exchange_rate * amount_due_remaining))
2904 INTO l_amount
2905 FROM AR_PAYMENT_SCHEDULES
2906 WHERE due_date <= g_batch_details.transaction_due_date
2907 AND status = 'OP'
2908 AND customer_trx_id = p_customer_trx_id;
2909
2910 RETURN l_amount;
2911
2912 EXCEPTION
2913 WHEN OTHERS THEN
2914 RETURN null;
2915 END;
2916
2917 /* Selects all the AR Transactions that need to be locked */
2918
2919 /*PROCEDURE Lock_AR_Txn(
2920 txnCur IN OUT NOCOPY txnCurType,
2921 x_return_status OUT NOCOPY VARCHAR2)
2922 IS
2923 BEGIN
2924 x_return_status := FND_API.G_TRUE;
2925 OPEN txnCur FOR
2926 SELECT trx.invoice_currency_code AS invoice_currency_code,
2927 trx.customer_trx_id AS customer_trx_id,
2928 trx.trx_number AS trx_number,
2929 trx_line.customer_trx_line_id AS customer_trx_line_id,
2930 trx_line.line_number AS line_number,
2931 ftxn.transaction_amt AS txn_amt,
2932 trx_line.extended_amount AS line_amt,
2933 ARPS.payment_schedule_id AS pymt_schedule_id,
2934 ARPS.amount_due_remaining AS amt_remaining,
2935 ftxn.netted_amt AS net_amt,
2936 ftxn.open_amt AS open_amt,
2937 Derive_net_exchg_rate(trx.invoice_currency_code,
2938 g_func_currency) trans_receipt_rate,
2939 Derive_net_exchg_rate(g_func_currency,
2940 trx.invoice_currency_code) receipt_trans_rate,
2941 arm.name AS receipt_name,
2942 arm.payment_type_code AS payment_type_code
2943 FROM FUN_NET_AR_TXNS ftxn,
2944 RA_CUSTOMER_TRX trx,
2945 RA_CUSTOMER_TRX_LINES trx_line,
2946 AR_PAYMENT_SCHEDULES ARPS,
2947 AR_RECEIPT_METHODS arm
2948 WHERE ftxn.customer_trx_id = trx.customer_trx_id
2949 AND trx.customer_trx_id = trx_line.customer_trx_id
2950 AND ARPS.customer_Trx_id = trx.customer_Trx_id
2951 AND ARPS.DUE_DATE <= g_batch_details.TRANSACTION_DUE_DATE
2952 AND ARPS.status = 'OP'
2953 AND ftxn.batch_id = g_batch_details.batch_id
2954 AND arm.receipt_method_id = trx.receipt_method_id
2955 ORDER BY ftxn.customer_trx_id;
2956 -- FOR UPDATE of ftxn.batch_id, trx.customer_trx_id,trx_line.customer_trx_id;
2957 -- arps.payment_schedule_id;
2958
2959 EXCEPTION
2960 WHEN OTHERS THEN
2961 x_return_status := FND_API.G_FALSE;
2962 END;
2963 */
2964 /* Validates the following
2965 The current Invoice Amount and the Open Amount against the Amount
2966 stored in the netting tables.
2967 not be CREDIT CARD*/
2968
2969 PROCEDURE Validate_AR_Txns(
2970 txnTable OUT NOCOPY TxnTblType,
2971 x_return_status OUT NOCOPY VARCHAR2
2972 )
2973 IS
2974
2975 l_old_invoice ra_customer_trx.customer_trx_id%TYPE;
2976 l_inv_amt ra_customer_trx_lines.extended_amount%TYPE;
2977 l_path VARCHAR2(100);
2978 round_diff ra_customer_trx_lines.extended_amount%TYPE;
2979 l_count_test number;
2980
2981 BEGIN
2982 x_return_status := FND_API.G_TRUE;
2983 l_path := g_path || 'Validate AR Transactions';
2984 fun_net_util.Log_String(g_event_level,l_path,
2985 'Validating AR Transactions');
2986
2987 SELECT trx.customer_trx_id AS customer_trx_id,
2988 trx.exchange_rate AS inv_exchange_rate,
2989 sum(ARPS.amount_due_remaining) AS amt_remaining,
2990 ftxn.transaction_amt AS txn_amount,
2991 ftxn.open_amt AS open_amt,
2992 ftxn.txn_curr_open_amt AS txn_curr_open_amt,
2993 0 AS txn_curr_amt,
2994 ftxn.txn_curr_net_amt AS txn_curr_net_amt,
2995 ftxn.netted_amt AS net_amt,
2996 trx.invoice_currency_code AS invoice_currency_code,
2997 arm.payment_type_code AS payment_type_code
2998 BULK COLLECT INTO txnTable
2999 FROM FUN_NET_AR_TXNS ftxn,
3000 RA_CUSTOMER_TRX trx,
3001 AR_PAYMENT_SCHEDULES ARPS,
3002 AR_RECEIPT_METHODS arm,
3003 RA_CUST_TRX_TYPES ctype
3004 WHERE ftxn.customer_trx_id = trx.customer_trx_id
3005 AND ARPS.customer_Trx_id = trx.customer_Trx_id
3006 AND ARPS.DUE_DATE <= g_batch_details.TRANSACTION_DUE_DATE
3007 AND ARPS.DUE_DATE between g_agr_start_date and g_agr_end_date
3008 AND ARPS.status = 'OP'
3009 AND ftxn.batch_id = g_batch_details.batch_id
3010 AND arm.receipt_method_id (+) = trx.receipt_method_id
3011 AND ctype.cust_trx_type_id = trx.cust_trx_type_id
3012 and trx.org_id = ftxn.org_id
3013 and trx.org_id = arps.org_id
3014 and trx.org_id = ctype.org_id
3015 GROUP BY trx.customer_trx_id,
3016 trx.exchange_rate,
3017 ftxn.transaction_amt,
3018 ftxn.open_amt,
3019 ftxn.netted_amt,
3020 ftxn.txn_curr_open_amt,
3021 ftxn.txn_curr_net_amt,
3022 trx.invoice_currency_code,
3023 arm.payment_type_code,
3024 ctype.type
3025 ORDER BY ctype.type;
3026
3027
3028 IF txnTable.EXISTS(1) THEN
3029
3030 fun_net_util.Log_String(g_event_level,l_path,
3031 'record count '||txnTable.COUNT);
3032
3033
3034 FOR i in txnTable.FIRST..txnTable.LAST
3035 LOOP
3036
3037 fun_net_util.Log_String(g_event_level,l_path,
3038 'Txn CurrOpen Amt fail22 ' || txnTable(i).txn_curr_open_amt);
3039
3040 fun_net_util.Log_String(g_event_level,l_path,
3041 'Amt Remaining fail22 ' || txnTable(i).amt_remaining);
3042
3043
3044 fun_net_util.Log_String(g_event_level,l_path,
3045 'Txn Due Date' || g_batch_details.TRANSACTION_DUE_DATE);
3046 fun_net_util.Log_String(g_event_level,l_path,
3047 'Agreement Start Date' || g_agr_start_date);
3048 fun_net_util.Log_String(g_event_level,l_path,
3049 'Agreement End Date' || g_agr_end_date);
3050 fun_net_util.Log_String(g_event_level,l_path,
3051 'i' || i);
3052 fun_net_util.Log_String(g_event_level,l_path,
3053 'Customer Trx Id ' || txnTable(i).customer_trx_id);
3054
3055 -- Check if the Open Amount in the Netting tables are
3056 -- different to the Open Amounts in Payment Schedules
3057
3058 txnTable(i).txn_curr_amt := calculate_ar_trx_amt
3059 (txnTable(i).customer_trx_id);
3060 l_inv_amt := fun_net_util.round_currency(
3061 txnTable(i).txn_curr_amt * nvl(txnTable(i).exchange_rate,1),
3062 g_func_currency);
3063
3064 fun_net_util.Log_String(g_event_level,l_path,
3065 'Txn Curr Amt' || txnTable(i).txn_curr_amt);
3066 fun_net_util.Log_String(g_event_level,l_path,
3067 'Net Txn Amt ' || txnTable(i).txn_amt);
3068 fun_net_util.Log_String(g_event_level,l_path,
3069 'Txn base amt' || l_inv_amt);
3070 fun_net_util.Log_String(g_event_level,l_path,
3071 'Txn CurrOpen Amt' || txnTable(i).txn_curr_open_amt);
3072 fun_net_util.Log_String(g_event_level,l_path,
3073 'Amt Remaining' || txnTable(i).amt_remaining);
3074 fun_net_util.Log_String(g_event_level,l_path,
3075 'Payment Type' || txnTable(i).payment_type_code);
3076
3077 -- Check if the Invoice Amount in the Netting table is different
3078 -- to the Invoice amount in the transaction
3079
3080 round_diff := txnTable(i).txn_amt - l_inv_amt ;
3081
3082 -- compare the transaction amounts in transaction currecy between the fun tables and the transaction tables.
3083
3084 -- IF txnTable(i).txn_amt <> l_inv_amt THEN Commented for bug 5326485
3085 IF txnTable(i).txn_curr_amt <> txnTable(i).txn_amt THEN
3086
3087 IF abs(round_diff) > 0.01 THEN
3088 x_return_status := FND_API.G_FALSE;
3089 EXIT;
3090 END IF;
3091 END IF;
3092
3093 -- Compare the open amounts in transaction currency between the fun tables and the payment schedules tables.
3094
3095 IF (txnTable(i).txn_curr_open_amt
3096 <> txnTable(i).amt_remaining) THEN
3097 fun_net_util.Log_String(g_event_level,l_path,'fails in comparison 2');
3098
3099 fun_net_util.Log_String(g_event_level,l_path,
3100 'Txn CurrOpen Amt fail2 ' || txnTable(i).txn_curr_open_amt);
3101
3102 fun_net_util.Log_String(g_event_level,l_path,
3103 'Amt Remaining fail2 ' || txnTable(i).amt_remaining);
3104
3105 x_return_status := FND_API.G_FALSE;
3106 EXIT;
3107 END IF;
3108
3109 -- If the Payment Type = CREDIT CARD then raise error
3110 IF txnTable(i).payment_type_code = 'CREDIT_CARD' THEN
3111 x_return_status := FND_API.G_FALSE;
3112 EXIT;
3113 END IF;
3114
3115 END LOOP;
3116 ELSE
3117
3118 x_return_status := FND_API.G_FALSE;
3119 END IF;
3120
3121 EXCEPTION
3122 WHEN OTHERS THEN
3123 x_return_status := FND_API.G_FALSE;
3124 END Validate_AR_Txns;
3125
3126 PROCEDURE create_cash_receipt(
3127 pARReceiptRec IN arReceiptRecType,
3128 x_cash_receipt_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
3129 x_return_status OUT NOCOPY VARCHAR2,
3130 x_msg_data OUT NOCOPY VARCHAR2,
3131 x_msg_count OUT NOCOPY NUMBER)
3132 IS
3133 l_path VARCHAR2(100);
3134 BEGIN
3135 l_path := g_path || 'Creat Cash Receipt';
3136 fun_net_util.log_string(g_event_level,l_path
3137 ,'Create Cash Receipt');
3138
3139 -- Call AR API Create Cash Receipt per Batch --
3140
3141
3142 fun_net_util.log_string(g_event_level,l_path,'receipt currency:'||pARReceiptRec.currency_code);
3143
3144
3145
3146 fun_net_util.log_string(g_event_level,l_path,'exchange rate:'||pARReceiptRec.exchange_rate);
3147
3148
3149 fun_net_util.log_string(g_event_level,l_path,'amount:'||pARReceiptRec.amount);
3150
3151 AR_RECEIPT_API_PUB.Create_cash(
3152 -- Standard API parameters.
3153 p_api_version => 1.0,
3154 p_init_msg_list => FND_API.G_TRUE,
3155 p_commit => FND_API.G_FALSE,
3156 x_return_status => x_return_status,
3157 x_msg_count => x_msg_count,
3158 x_msg_data => x_msg_data,
3159
3160 -- Receipt info. Parameters
3161 p_currency_code => pARReceiptRec.currency_code,
3162 p_exchange_rate_type => pARReceiptRec.exchange_rate_type,
3163 p_exchange_rate => pARReceiptRec.exchange_rate,
3164 p_exchange_rate_date => pARReceiptRec.exchange_rate_date,
3165 p_amount => pARReceiptRec.amount,
3166 p_factor_discount_amount => pARReceiptRec.factor_discount_amount ,
3167 p_receipt_number => pARReceiptRec.receipt_number,
3168 p_receipt_date => pARReceiptRec.receipt_date,
3169 p_gl_date => pARReceiptRec.gl_date,
3170 p_customer_id => pARReceiptRec.customer_id,
3171 p_customer_site_use_id => pARReceiptRec.customer_site_use_id,
3172 p_remittance_bank_account_id => pARReceiptRec.remittance_bank_account_id,
3173 p_remittance_bank_account_num => pARReceiptRec.remittance_bank_account_num,
3174 p_remittance_bank_account_name => pARReceiptRec.remittance_bank_account_name,
3175 p_receipt_method_id => pARReceiptRec.receipt_method_id,
3176 p_org_id => pARReceiptRec.org_id,
3177
3178 p_customer_receipt_reference => g_batch_details.batch_number,
3179
3180 -- ** OUT NOCOPY variables
3181 p_cr_id => x_cash_receipt_id);
3182
3183 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3184 fun_net_util.log_string(g_event_level,l_path
3185 ,'Cash receipt creation failed either because AR_RECEIPT_API_PUB. Creat_cash failed or there is no receipt class associated ');
3186
3187 FND_MSG_PUB.Count_And_Get (
3188 p_count => x_msg_count,
3189 p_data => x_msg_data );
3190 IF x_msg_count > 1 THEN
3191
3192
3193 FOR x IN 1..x_msg_count LOOP
3194
3195 x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3196 fun_net_util.Log_String(g_unexp_level,l_path,'Error message:' ||x_msg_data);
3197
3198 END LOOP;
3199 END IF;
3200
3201
3202
3203 x_return_status := FND_API.G_FALSE;
3204 RETURN;
3205 END IF;
3206 EXCEPTION
3207 WHEN OTHERS THEN
3208 x_return_status := FND_API.G_FALSE;
3209 END create_cash_receipt;
3210
3211 /*PROCEDURE apply2_cash_receipt
3212 (txnTable IN txnTblType,
3213 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
3214 x_return_status OUT NOCOPY VARCHAR2)
3215 IS
3216 l_applied_amt ra_customer_trx_lines.extended_amount%TYPE;
3217 l_open_amt ra_customer_trx_lines.extended_amount%TYPE;
3218 l_line_amt ra_customer_trx_lines.extended_amount%TYPE;
3219 current_amt_due AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
3220 l_applied_from ra_customer_trx_lines.extended_amount%TYPE;
3221 l_exchange_rate ra_customer_trx_lines.extended_amount%TYPE;
3222 l_return_status VARCHAR2(1);
3223
3224 l_exchange_rate_type varchar2(20);
3225 l_exchange_rate_date fun_net_batches.settlement_date%TYPE;
3226 l_msg_count NUMBER;
3227 l_msg_data VARCHAR2(2000);
3228
3229 CURSOR apply_trx_cur(p_cust_trx_id IN NUMBER)
3230 IS
3231 SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
3232 trx_line.extended_amount AS extended_amount,
3233 ps.payment_schedule_id AS pymt_schedule_id
3234 FROM fun_net_ar_txns ftxn,
3235 ra_customer_trx_lines trx_line,
3236 ar_payment_schedules ps
3237 WHERE ftxn.batch_id = g_batch_details.batch_id
3238 AND ftxn.customer_trx_id = p_cust_trx_id
3239 AND trx_line.customer_trx_id = ftxn.customer_trx_id
3240 AND trx_line.line_type = 'LINE'
3241 AND ps.customer_trx_id = trx_line.customer_trx_id
3242 AND ps.due_date <= g_batch_details.transaction_due_date
3243 AND ps.due_date between g_agr_start_date and g_agr_end_date
3244 AND ps.status = 'OP';
3245
3246
3247
3248 BEGIN
3249 FOR i in txnTable.FIRST..txnTable.LAST
3250 LOOP
3251
3252 --Check if Amount due remaining has changed since it was validated ,as txns
3253 --are not locked. If so raise an error
3254
3255 SELECT SUM(amount_due_remaining)
3256 INTO current_amt_due
3257 FROM ar_payment_schedules ps
3258 WHERE customer_trx_id = txnTable(i).customer_Trx_id
3259 AND due_date <= g_batch_details.TRANSACTION_DUE_DATE
3260 AND due_date between g_agr_start_date and g_agr_end_date
3261 AND ps.status = 'OP';
3262
3263 l_open_amt := txnTable(i).txn_curr_open_amt;
3264
3265 IF l_open_amt <> current_amt_due THEN
3266 x_return_status := FND_API.G_FALSE;
3267 EXIT;
3268 END IF;
3269
3270 FOR apply_rec in apply_trx_cur(txnTable(i).customer_trx_id)
3271 LOOP
3272 AND tl.sequence_num(+) = ps.terms_sequence_number
3273 AND t.term_id(+) = tl.term_id;
3274
3275 /* Amount to be applied per line- Calculate percentage of contribution based
3276 on the Netting AMount and Line AMount */
3277
3278 /* l_applied_amt := (l_line_amt / txnTable(i).amt_remaining)
3279 * txnTable(i).txn_curr_net_amt; */
3280
3281 /* When the Invoice Currency = Receipt Currency do not provide values for
3282 exchange rate and the Applied From amounts */
3283
3284
3285 /* IF txnTable(i).invoice_currency_code = g_func_currency THEN
3286 l_exchange_rate := null;
3287 l_applied_from := null;
3288 ELSE
3289 l_exchange_rate := Derive_Net_Exchg_Rate(
3290 txnTable(i).invoice_currency_code,
3291 g_func_currency);
3292 l_applied_from := l_applied_amt * l_exchange_rate;
3293
3294 END IF;
3295
3296
3297 AR_RECEIPT_API_PUB.Apply(
3298 -- Standard API parameters.
3299 p_api_version => 1.0,
3300 p_init_msg_list => FND_API.G_FALSE,
3301 p_commit => FND_API.G_FALSE,
3302 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3303 x_return_status => l_return_status,
3304 x_msg_count => l_msg_count,
3305 x_msg_data => l_msg_data,
3306 -- Receipt application parameters.
3307 p_cash_receipt_id => p_cash_receipt_id,
3308 p_customer_trx_id => txnTable(i).customer_trx_id,
3309 p_applied_payment_schedule_id => apply_rec.pymt_schedule_id,
3310 -- this is the amount to be applied in the transaction currency
3311 p_amount_applied => l_applied_amt,
3312 -- this the amount to be applied in the receipt currency
3313 p_amount_applied_from => l_applied_from,
3314 p_trans_to_receipt_rate => l_exchange_rate,
3315 p_discount => 0,
3316 p_apply_date => g_batch_details.settlement_date,
3317 p_apply_gl_date => g_batch_details.gl_date,
3318 p_customer_trx_line_id => apply_rec.cust_trx_line_id,
3319 p_org_id => g_batch_details.org_id
3320 );
3321
3322 dbms_output.put_line('Apply' || l_return_status);
3323
3324 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3325 x_return_status := FND_API.G_FALSE;
3326 RETURN;
3327 END IF;
3328
3329 END LOOP;
3330 END LOOP;
3331
3332 EXCEPTION
3333 WHEN OTHERS THEN
3334 x_return_status := FND_API.G_FALSE;
3335 END apply2_cash_receipt; */
3336
3337
3338
3339
3340 PROCEDURE apply_cash_receipt
3341 (txnTable IN txnTblType,
3342 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
3343 x_return_status OUT NOCOPY VARCHAR2)
3344 IS
3345 l_applied_amt ra_customer_trx_lines.extended_amount%TYPE;
3346 l_applied_line_amt ra_customer_trx_lines.extended_amount%TYPE;
3347 l_tax_amount ra_customer_trx_lines.extended_amount%TYPE;
3348 l_open_amt ra_customer_trx_lines.extended_amount%TYPE;
3349 l_running_amt ra_customer_trx_lines.extended_amount%TYPE;
3350 current_amt_due AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
3351 l_applied_from ra_customer_trx_lines.extended_amount%TYPE;
3352 l_exchange_rate fun_net_batches_all.exchange_rate%TYPE;
3353 l_original_amt ra_customer_trx_lines.extended_amount%TYPE;
3354 l_trans_to_func_rate fun_net_batches_all.exchange_rate%TYPE;
3355 l_func_to_trans_rate fun_net_batches_all.exchange_rate%TYPE;
3356 l_return_status VARCHAR2(1);
3357
3358 l_exchange_rate_type varchar2(20);
3359 l_exchange_rate_date fun_net_batches.settlement_date%TYPE;
3360 l_msg_count NUMBER;
3361 l_msg_data VARCHAR2(2000);
3362 l_path VARCHAR2(100);
3363 l_receipt_currecycode VARCHAR2(10); -- bug 52380292
3364 llca_def_trx_lines_tbl AR_RECEIPT_API_PUB.llca_trx_lines_tbl_type ;
3365 l_precision NUMBER;
3366 l_running_amount1 NUMBER;
3367 l_freight_amount NUMBER;
3368 l_class VARCHAR2(5); --6179308
3369 l_line_or_header NUMBER; --7639693
3370
3371 CURSOR apply_trx_cur(p_cust_trx_id IN NUMBER)
3372 IS
3373 SELECT ps.payment_schedule_id AS pymt_schedule_id,
3374 ps.amount_due_remaining AS amt_due_remain
3375 FROM ar_payment_schedules ps
3376 WHERE ps.customer_trx_id = p_cust_trx_id
3377 AND trunc(ps.due_date) <= trunc(g_batch_details.transaction_due_date)
3378 AND trunc(ps.due_date) between trunc(g_agr_start_date) and trunc(g_agr_end_date)
3379 AND ps.status = 'OP';
3380
3381 CURSOR apply_trx_line_cur(p_cust_trx_id IN NUMBER)
3382 IS
3383 SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
3384 trx_line.extended_amount AS extended_amount,
3385 trx_line.line_type AS line_type,
3386 trx_line.amount_due_remaining AS line_am_due_remaining
3387 FROM ra_customer_trx_lines trx_line
3388 WHERE trx_line.customer_trx_id = p_cust_trx_id
3389 AND trx_line.line_type IN ('LINE');
3390
3391
3392
3393 BEGIN
3394 l_path := g_path || 'Apply Cash Receipt';
3395 fun_net_util.Log_String(g_event_level,
3396 l_path,
3397 'Applying Transactions');
3398
3399 -- Bug 52380292
3400 select currency_code into l_receipt_currecycode
3401 from ar_cash_receipts
3402 where cash_receipt_id = p_cash_receipt_id;
3403
3404
3405 FOR i in txnTable.FIRST..txnTable.LAST
3406 LOOP
3407 --Check if Amount due remaining has changed since it was validated ,
3408 -- as txns are not locked. If so raise an error
3409 SELECT SUM(amount_due_remaining)
3410 INTO current_amt_due
3411 FROM ar_payment_schedules ps
3412 WHERE customer_trx_id = txnTable(i).customer_Trx_id
3413 AND due_date <= g_batch_details.TRANSACTION_DUE_DATE
3414 AND due_date between g_agr_start_date and g_agr_end_date
3415 AND ps.status = 'OP';
3416
3417 SELECT sum(APS.amount_due_original)
3418 INTO l_original_amt
3419 FROM AR_PAYMENT_SCHEDULES APS
3420 WHERE APS.customer_Trx_id = txnTable(i).customer_Trx_id;
3421
3422
3423 l_open_amt := txnTable(i).txn_curr_open_amt;
3424
3425 IF l_open_amt <> current_amt_due THEN
3426 x_return_status := FND_API.G_FALSE;
3427 EXIT;
3428 END IF;
3429
3430 -- Exchange rate related logic for Bug 5463394
3431
3432 IF txnTable(i).invoice_currency_code <> g_func_currency THEN
3433 IF g_net_currency_rule='ACCOUNTING_CURRENCY' THEN
3434 l_func_to_trans_rate:=Derive_Net_Exchg_Rate(g_func_currency,txnTable(i).invoice_currency_code);
3435 l_trans_to_func_rate:=Derive_Net_Exchg_Rate(txnTable(i).invoice_currency_code,g_func_currency);
3436 ELSE l_trans_to_func_rate := 1;
3437 l_func_to_trans_rate := 1;
3438 END IF;
3439 ELSE l_trans_to_func_rate :=1;
3440 l_func_to_trans_rate :=1;
3441 END IF;
3442
3443
3444
3445 fun_net_util.Log_String(g_state_level,l_path,'l_trans_to_func_rate:'||l_trans_to_func_rate);
3446
3447 fun_net_util.Log_String(g_state_level,l_path,'l_func_to_trans_rate:'||l_func_to_trans_rate);
3448
3449
3450 -- get the prcesion of the trx currency
3451
3452
3453 SELECT fc.precision
3454 INTO l_precision
3455 FROM fnd_currencies fc
3456 WHERE fc.currency_code = txnTable(i).invoice_currency_code;
3457
3458 fun_net_util.Log_String(g_event_level,l_path
3459 ,'l_precision:' ||l_precision);
3460
3461
3462
3463
3464 -- End of Exchange related logic for bug 5463394
3465
3466 -- amount to be applied per installment
3467 l_running_amt := txnTable(i).txn_curr_net_amt;
3468 FOR apply_rec in apply_trx_cur(txnTable(i).customer_trx_id)
3469 LOOP
3470
3471 fun_net_util.Log_String(g_event_level,l_path,'Payment Schedule id' || apply_rec.pymt_schedule_id);
3472 fun_net_util.Log_String(g_event_level,l_path,'Running Amount' || l_running_amt);
3473 fun_net_util.Log_String(g_event_level,l_path,'Curr Net Amt' || txnTable(i).txn_curr_net_amt);
3474 fun_net_util.Log_String(g_event_level,l_path,'Customer Trx id' || txnTable(i).customer_trx_id);
3475
3476 fun_net_util.Log_String(g_event_level,l_path,'apply_rec.amt_due_remain:'|| apply_rec.amt_due_remain);
3477
3478
3479 fun_net_util.Log_String(g_event_level,l_path,'l_running_amt*l_func_to_trans_rate:'|| l_running_amt*l_func_to_trans_rate);
3480
3481
3482 -- Calculate Amount to be applied per Payment Schedule
3483 IF l_running_amt = 0 THEN
3484 EXIT;
3485 END IF;
3486
3487 IF l_running_amt < 0 AND apply_rec.amt_due_remain < 0 THEN
3488 IF (l_running_amt) <= apply_rec.amt_due_remain THEN
3489 l_applied_amt := apply_rec.amt_due_remain;
3490 l_running_amt := (l_running_amt) - (apply_rec.amt_due_remain);
3491 ELSE
3492 l_applied_amt := (l_running_amt) ;
3493 l_running_amt := 0;
3494 END IF;
3495 ELSIF l_running_amt > 0 AND apply_rec.amt_due_remain > 0 THEN
3496 IF (l_running_amt) >= (apply_rec.amt_due_remain) THEN
3497 l_applied_amt := (apply_rec.amt_due_remain);
3498 l_running_amt := l_running_amt - (apply_rec.amt_due_remain);
3499 ELSE
3500 l_applied_amt := l_running_amt;
3501 l_running_amt := 0;
3502 END IF;
3503 END IF;
3504 /* When the Invoice Currency = Receipt Currency do not provide values for
3505 exchange rate and the Applied From amounts */
3506 /* When the Invoice Currency = Receipt Currency do not provide values for
3507 exchange rate and the Applied From amounts */
3508 IF txnTable(i).invoice_currency_code = g_func_currency THEN
3509 l_exchange_rate := null;
3510 l_applied_from := null;
3511 ELSE
3512 IF txnTable(i).invoice_currency_code <> l_receipt_currecycode THEN -- Bug 52380292
3513 l_exchange_rate := Derive_Net_Exchg_Rate(txnTable(i).invoice_currency_code,g_func_currency);
3514 l_applied_from := round((l_applied_amt * l_exchange_rate),l_precision) ;
3515 ELSE
3516 l_exchange_rate := null;
3517 l_applied_from := null;
3518 END IF;
3519 END IF;
3520 fun_net_util.Log_String(g_event_level,l_path,'exchange rate:'||l_exchange_rate);
3521 fun_net_util.Log_String(g_event_level,l_path,'amount applied:'||l_applied_amt);
3522 fun_net_util.Log_String(g_event_level,l_path,'applied from'||l_applied_from);
3523
3524 BEGIN
3525 SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
3526 trx_line.line_number AS line_number,'','',
3527 round(((((nvl(trx_line.amount_due_remaining,trx_line.extended_amount)/current_amt_due)*l_applied_amt) +
3528 ( select nvl((sum( nvl(trx_line_tax.amount_due_remaining,trx_line_tax.extended_amount) )/current_amt_due)*l_applied_amt,0)
3529 FROM ra_customer_trx_lines trx_line_tax
3530 WHERE trx_line_tax.link_to_cust_trx_line_id = trx_line.customer_trx_line_id
3531 AND trx_line_tax.customer_trx_id = trx_line.customer_trx_id
3532 AND trx_line_tax.line_type IN ('TAX') ))),l_precision) AS amount_applied,'','','','','','','','',''
3533 ,'','','','','','','','','',''
3534 BULK COLLECT INTO llca_def_trx_lines_tbl
3535 FROM ra_customer_trx_lines trx_line
3536 WHERE trx_line.customer_trx_id = txnTable(i).customer_trx_id
3537 AND trx_line.line_type IN ('LINE')
3538 AND nvl(trx_line.amount_due_remaining,trx_line.extended_amount) <> 0 ;
3539 EXCEPTION
3540 WHEN OTHERS THEN
3541 RAISE FND_API.G_EXC_ERROR;
3542 END;
3543
3544
3545 -- get the freight amount
3546
3547
3548 SELECT round(SUM((nvl(trx_line.amount_due_remaining,trx_line.extended_amount)
3549 /current_amt_due)*l_applied_amt),l_precision)
3550 INTO l_freight_amount
3551 FROM ra_customer_trx_lines trx_line
3552 WHERE trx_line.customer_trx_id = txnTable(i).customer_trx_id
3553 AND trx_line.line_type IN ('FREIGHT')
3554 AND nvl(trx_line.amount_due_remaining,trx_line.extended_amount) <> 0 ;
3555
3556
3557 fun_net_util.Log_String(g_event_level,l_path
3558 ,' l_freight_amount : ' || l_freight_amount);
3559
3560
3561
3562 -- Prorate the amounts for each line
3563
3564 /*
3565 l_applied_amt := round(l_applied_amt,l_precision);
3566 l_running_amount1 := l_applied_amt ;
3567
3568 FOR i in llca_def_trx_lines_tbl.FIRST..llca_def_trx_lines_tbl.LAST LOOP
3569
3570 fun_net_util.Log_String(g_event_level,l_path
3571 ,'l_running_amount1:' ||l_running_amount1);
3572
3573 fun_net_util.Log_String(g_event_level,l_path
3574 ,'lllca_def_trx_lines_tbl(i).amount_applied:' ||llca_def_trx_lines_tbl(i).amount_applied);
3575
3576 IF l_running_amount1 < llca_def_trx_lines_tbl(i).amount_applied THEN
3577
3578 llca_def_trx_lines_tbl(i).amount_applied := l_running_amount1 ;
3579
3580 ELSE
3581
3582 l_running_amount1 := l_running_amount1 - llca_def_trx_lines_tbl(i).amount_applied;
3583 END IF;
3584
3585 END LOOP;
3586
3587 */ -- Commented this for issue no : 7368248
3588
3589 SELECT count(*) into l_line_or_header
3590 FROM ra_batch_sources bs, ra_customer_trx ct
3591 WHERE ct.customer_trx_id = txnTable(i).customer_trx_id AND
3592 ct.batch_Source_id = bs.batch_source_id AND
3593 NVL(gen_line_level_bal_flag,'Y') = 'Y';
3594
3595 IF l_line_or_header >= 1 THEN
3596 --6179308. Call Apply_In_Detail for invoices and Debitmemo, otherwise call Apply API.
3597
3598 Select Class into l_class from ar_payment_schedules_all
3599 where customer_trx_id = txnTable(i).customer_trx_id;
3600
3601 IF(l_class = 'INV' OR l_class = 'DM') THEN
3602 AR_RECEIPT_API_PUB.Apply_In_Detail(
3603 -- Standard API parameters.
3604 p_api_version => 1.0,
3605 p_init_msg_list => FND_API.G_FALSE,
3606 p_commit => FND_API.G_FALSE,
3607 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3608 x_return_status => l_return_status,
3609 x_msg_count => l_msg_count,
3610 x_msg_data => l_msg_data,
3611 -- Receipt application parameters.
3612 p_cash_receipt_id => p_cash_receipt_id,
3613 p_customer_trx_id => txnTable(i).customer_trx_id,
3614 p_applied_payment_schedule_id => apply_rec.pymt_schedule_id,
3615 p_llca_type => 'L',
3616 p_llca_trx_lines_tbl => llca_def_trx_lines_tbl,
3617 p_trans_to_receipt_rate => l_exchange_rate,
3618 p_freight_amount => l_freight_amount,
3619 p_discount => 0,
3620 p_apply_date => g_batch_details.settlement_date,
3621 p_apply_gl_date => g_batch_details.gl_date,
3622 p_org_id => g_batch_details.org_id
3623 );
3624
3625 ELSE
3626 Ar_receipt_api_pub.apply(
3627 p_api_version => 1.0,
3628 p_init_msg_list => FND_API.G_TRUE,
3629 p_cash_receipt_id => p_cash_receipt_id,
3630 p_customer_trx_id => txnTable(i).customer_trx_id,
3631 x_return_status => l_return_status,
3632 x_msg_count => l_msg_count,
3633 x_msg_data => l_msg_data
3634 );
3635 END IF;
3636 ELSE
3637 Ar_receipt_api_pub.apply(
3638 p_api_version => 1.0,
3639 p_init_msg_list => FND_API.G_TRUE,
3640 p_cash_receipt_id => p_cash_receipt_id,
3641 p_customer_trx_id => txnTable(i).customer_trx_id,
3642 x_return_status => l_return_status,
3643 x_msg_count => l_msg_count,
3644 x_msg_data => l_msg_data
3645 );
3646
3647 END IF;
3648
3649
3650 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3651
3652 FND_MSG_PUB.Count_And_Get (
3653 p_count => l_msg_count,
3654 p_data => l_msg_data );
3655
3656 fun_net_util.Log_String(g_event_level,l_path
3657 ,'apply cash receipt package after AR_RECEIPT_API_PUB.Apply:' ||l_msg_data);
3658
3659 IF l_msg_count > 1 THEN
3660
3661
3662
3663 FOR x IN 1..l_msg_count LOOP
3664
3665 l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3666 fun_net_util.Log_String(g_event_level,l_path
3667 ,'apply cash receipt package Error message AR_RECEIPT_API_PUB.Apply' ||l_msg_data||' '||' '||x);
3668
3669 END LOOP;
3670 END IF;
3671
3672 x_return_status := FND_API.G_FALSE;
3673 RETURN;
3674 END IF;
3675
3676 END LOOP;
3677 END LOOP;
3678
3679 EXCEPTION
3680 WHEN OTHERS THEN
3681 x_return_status := FND_API.G_FALSE;
3682 END apply_cash_receipt;
3683
3684
3685
3686 /******************************************************************************
3687 |PROCEDURE settle_ar_txns : Settles AR Transactions for the given batch |
3688 | |
3689 | Calls : Get_Customer_Details |
3690 | : create_cash_receipt |
3691 | : apply_cash_receipt |
3692 *******************************************************************************/
3693
3694 PROCEDURE settle_ar_txns(
3695 txnTable IN txnTblType,
3696 p_bank_acct_name IN ce_bank_accounts.bank_account_name%TYPE,
3697 p_bank_acct_num IN ce_bank_accounts.bank_account_num%TYPE,
3698 p_bank_acct_use_id IN ce_bank_acct_uses_all.bank_acct_use_id%TYPE,
3699 x_return_status OUT NOCOPY VARCHAR2,
3700 x_msg_data OUT NOCOPY VARCHAR2,
3701 x_msg_count OUT NOCOPY NUMBER)
3702 IS
3703
3704
3705 l_receipt_method_id ar_receipt_methods.receipt_method_id%TYPE;
3706 l_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
3707 l_cust_acct_id hz_cust_accounts.cust_account_id%TYPE;
3708 l_cust_site_use_id hz_cust_site_uses.site_use_id%TYPE;
3709 arReceiptRec arReceiptRecType;
3710
3711 l_bank_acct_used NUMBER;
3712 l_return_status VARCHAR2(1);
3713 l_msg_count NUMBER;
3714 l_msg_data VARCHAR2(2000);
3715 l_path VARCHAR2(100);
3716
3717 BEGIN
3718 -- Get Customer Details for the AR Receipt --
3719 l_path := g_path || 'Settle_AR_Transactions';
3720 Get_Customer_Details (p_cust_acct_id => l_cust_acct_id,
3721 p_cust_site_use_id => l_cust_site_use_id,
3722 p_return_status => l_return_status
3723 );
3724
3725
3726 IF l_return_status = FND_API.G_FALSE then
3727 fun_net_util.Log_String(g_event_level,
3728 l_path
3729 ,'No Customer Details');
3730 FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_CUST_DETAILS');
3731 x_return_status := FND_API.G_FALSE;
3732 RETURN;
3733 ELSE
3734 fun_net_util.Log_String(g_event_level,
3735 l_path
3736 ,'Customer Details Success');
3737 END IF;
3738
3739 -- Get the Seeded Receipt Method Id for Netting --
3740 -- Bug 5967665, Changed the where clause to WHERE receipt_method_id= -1
3741 BEGIN
3742 SELECT receipt_method_id
3743 INTO l_receipt_method_id
3744 FROM ar_receipt_methods
3745 WHERE receipt_method_id= -1;
3746
3747 EXCEPTION
3748 WHEN OTHERS THEN
3749
3750 fun_net_util.Log_String(g_event_level,
3751 l_path
3752 ,'Receipt Method not found');
3753 FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_RECEIPT_METHOD');
3754 x_return_status := FND_API.G_FALSE;
3755 RETURN;
3756 END;
3757
3758 BEGIN
3759 SELECT remit_bank_acct_use_id
3760 INTO l_bank_acct_used
3761 FROM ar_receipt_method_accounts_all
3762 WHERE receipt_method_id = l_receipt_method_id
3763 and org_id =g_batch_details.org_id
3764 and remit_bank_acct_use_id= p_bank_acct_use_id;
3765
3766 EXCEPTION
3767 WHEN OTHERS THEN
3768
3769 fun_net_util.Log_String(g_event_level,
3770 l_path
3771 ,'Agreement Bank Account not associated to Receipt Method AP/AR Netting ');
3772 FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_BANK_ACCT_MISSING');
3773 x_return_status := FND_API.G_FALSE;
3774 RETURN;
3775 END;
3776
3777 -- Set all inputs to create cash receipt --
3778
3779 arReceiptRec.currency_code := g_batch_details.batch_currency;
3780 arReceiptRec.amount := g_batch_details.total_netted_amt;
3781 arReceiptRec.factor_discount_amount := 0;
3782 arReceiptRec.receipt_number := g_batch_details.batch_number;
3783 arReceiptRec.receipt_date := g_batch_details.settlement_date;
3784 arReceiptRec.gl_date := g_batch_details.gl_date;
3785 arReceiptRec.customer_id := l_cust_acct_id;
3786 arReceiptRec.customer_site_use_id := l_cust_site_use_id;
3787 arReceiptRec.remittance_bank_account_id := p_bank_acct_use_id;
3788 arReceiptRec.remittance_bank_account_num := p_bank_acct_num;
3789 arReceiptRec.remittance_bank_account_name:= p_bank_acct_name;
3790 arReceiptRec.receipt_method_id := l_receipt_method_id;
3791 arReceiptRec.org_id := g_batch_details.org_id;
3792
3793 IF g_batch_details.batch_currency = g_func_currency THEN
3794
3795 arReceiptRec.exchange_rate := null;
3796 arReceiptRec.exchange_rate_type := null;
3797 arReceiptRec.exchange_rate_date := null;
3798 ELSE
3799 IF g_batch_details.exchange_rate_type = 'User' THEN -- Bug 52380292
3800 /*
3801 arReceiptRec.exchange_rate := Derive_Net_Exchg_Rate(
3802 g_batch_details.batch_currency,
3803 g_func_currency); */
3804 arReceiptRec.exchange_rate := g_batch_details.exchange_rate;
3805 END IF;
3806 arReceiptRec.exchange_rate_type := g_batch_details.exchange_rate_type;
3807 arReceiptRec.exchange_rate_date := g_batch_details.settlement_date;
3808
3809 END IF;
3810
3811 -- Call to create Cash Receipt --
3812
3813 create_cash_receipt( pARReceiptRec => arReceiptRec,
3814 x_cash_receipt_id => l_cash_receipt_id,
3815 x_return_status => x_return_status,
3816 x_msg_data => x_msg_data,
3817 x_msg_count => x_msg_count);
3818
3819 fun_net_util.Log_String(g_event_level,
3820 l_path
3821 ,'After Create Cash Receipt');
3822
3823 fun_net_util.Log_String(g_event_level,
3824 l_path
3825 ,x_msg_data|| ' with message ' || x_msg_count);
3826
3827 IF x_return_status = FND_API.G_FALSE THEN
3828
3829 fun_net_util.Log_String(g_event_level,
3830 l_path
3831 ,'Cash Receipt Creation Failed');
3832
3833 FND_MESSAGE.SET_NAME('FUN','FUN_NET_CASH_RECEIPT_FAIL');
3834 RETURN;
3835 ELSE
3836
3837 fun_net_util.Log_String(g_event_level,
3838 l_path
3839 ,'Cash Receipt Creation Success');
3840 END IF;
3841
3842 -- Apply Receipt to the AR Transactions in the Batch --
3843 apply_cash_receipt
3844 (txnTable => txnTable,
3845 p_cash_receipt_id => l_cash_receipt_id,
3846 x_return_status => x_return_status);
3847
3848 IF x_return_status = FND_API.G_FALSE THEN
3849 FND_MESSAGE.SET_NAME('FUN','FUN_NET_APPLY_RECEIPT_FAIL');
3850 RETURN;
3851 END IF;
3852
3853 -- Update AR Transactions in the Batch with the Cash Receipt Id --
3854
3855 IF l_cash_receipt_id IS NOT NULL THEN
3856 BEGIN
3857
3858 UPDATE fun_net_ar_txns
3859 SET cash_receipt_id = l_cash_receipt_id
3860 WHERE
3861 batch_id = g_batch_id;
3862
3863
3864 EXCEPTION
3865 WHEN OTHERS THEN
3866 fun_net_util.Log_String(g_event_level,
3867 l_path
3868 ,'Failed to associate AR transactions in the batch with cash receipt');
3869
3870 x_return_status := FND_API.G_FALSE;
3871 END;
3872 END IF;
3873
3874 EXCEPTION
3875 WHEN OTHERS THEN
3876 fun_net_util.Log_String(g_event_level,
3877 l_path
3878 ,'Unknown error in settle_ar_txns procedure');
3879
3880 x_return_status := FND_API.G_FALSE;
3881 END settle_ar_txns;
3882
3883
3884 /*************************************************************************
3885 | Procedure : Perform_common_tasks. |
3886 | Sets the Agreement Status to 'N' and also unlocks AP Payment Schdedules|
3887 | Called when the settlement process is committed or when an Exception is|
3888 | raised |
3889 **************************************************************************/
3890
3891 PROCEDURE perform_common_tasks
3892 IS
3893 l_return_status VARCHAR2(1);
3894 BEGIN
3895
3896 unlock_ap_pymt_schedules(
3897 p_batch_id => g_batch_id,
3898 x_return_status => l_return_status);
3899
3900
3901 -- Update Agreement Status
3902 Set_Agreement_Status(
3903 x_agreement_id => g_batch_details.agreement_id,
3904 x_batch_id => g_batch_id,
3905 x_mode => 'UNSET',
3906 x_return_status => l_return_status);
3907
3908
3909 -- Unlock AP and AR Transactions
3910 -- AR Transactions will be unlocked automatically when a COMMIT is
3911 -- issued.
3912 EXCEPTION
3913 WHEN OTHERS THEN
3914 null;
3915 END perform_common_tasks;
3916
3917
3918
3919 /***************************************************************************
3920 |PROCEDURE apply_cash_receipt : Applies a Receipt to AR transactions in the|
3921 |Batch . Applies at Line level based on perecent contribution per line |
3922 | |
3923 |Calls : AR_RECEIPT_API_PUB.Apply |
3924 ***************************************************************************/
3925
3926
3927 FUNCTION default_batch_details
3928 RETURN VARCHAR2
3929 IS
3930 BEGIN
3931 IF g_batch_details.gl_date IS NULL THEN
3932 g_batch_details.gl_date := g_batch_details.settlement_date;
3933 END IF;
3934 IF g_batch_details.exchange_rate_type IS NULL THEN
3935 g_batch_details.exchange_rate_type := 'Corporate';
3936 END IF;
3937 RETURN FND_API.G_TRUE;
3938 EXCEPTION
3939 WHEN OTHERS THEN
3940 RETURN FND_API.G_FALSE;
3941
3942 END default_batch_details;
3943
3944
3945 PROCEDURE settle_net_batch (
3946 -- ***** Standard API Parameters *****
3947 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3948 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3949 x_return_status OUT NOCOPY VARCHAR2,
3950 x_msg_count OUT NOCOPY NUMBER,
3951 x_msg_data OUT NOCOPY VARCHAR2,
3952 -- ***** Netting batch input parameters *****
3953 p_batch_id IN NUMBER) IS
3954
3955 l_schd_tab pymt_sch_tab;
3956 l_status_flag VARCHAR2(1);
3957 l_return_status VARCHAR2(1);
3958 l_batch_status fun_net_batches_all.settlement_date%TYPE;
3959 l_bank_acct_use_id ce_bank_acct_uses_all.bank_acct_use_id%TYPE;
3960 l_bank_acct_name ce_bank_accounts.bank_account_name%TYPE;
3961 l_bank_acct_num ce_bank_accounts.bank_account_num%TYPE;
3962 l_le_id xle_entity_profiles.legal_entity_id%TYPE;
3963 l_bank_num ce_banks_v.bank_number%TYPE;
3964 batch_status_flag BOOLEAN;
3965 l_msg_data VARCHAR2(1000);
3966 l_msg_count NUMBER;
3967 -- txnCur txnCurType;
3968 l_period_name gl_period_statuses.period_name%TYPE;
3969 txnTable txnTblType;
3970 l_path VARCHAR2(100);
3971 VALIDATE_AR_TXN_FAIL EXCEPTION;
3972 l_request_id NUMBER;
3973 l_amt_to_net NUMBER;
3974 BEGIN
3975
3976
3977 l_path := g_path||'Settle_Net_Batch';
3978 fun_net_util.Log_String(g_event_level,l_path,'Settling Netting batches');
3979 /* Intialize standard API parameters */
3980 /* Intialize message list */
3981 x_msg_count := NULL;
3982 x_msg_data := NULL;
3983
3984 -- **** Initialize message list if p_init_msg_list is set to TRUE. ****
3985 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3986 FND_MSG_PUB.initialize;
3987 END IF;
3988
3989 -- **** Initialize return status to SUCCESS *****
3990 x_return_status := FND_API.G_RET_STS_SUCCESS;
3991
3992 /* Create Save point */
3993 SAVEPOINT settle_net_batch_SP;
3994
3995 /* Get Batch Details for the given batch id*/
3996 IF p_batch_id IS NULL THEN
3997
3998 fun_net_util.Log_String(
3999 g_event_level
4000 ,l_path,'Batch Id is null');
4001 FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4002 RAISE FND_API.G_EXC_ERROR;
4003 END IF;
4004
4005 g_batch_id := p_batch_id;
4006
4007 IF NOT get_batch_details THEN
4008
4009 fun_net_util.Log_String(g_event_level,l_path,
4010 'Error getting Batch details');
4011 FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4012 RAISE FND_API.G_EXC_ERROR;
4013 END IF;
4014
4015 g_agreement_id := g_batch_details.agreement_id;
4016
4017 IF NOT get_agreement_details THEN
4018
4019 fun_net_util.Log_String(g_event_level,l_path,
4020 'Error getting Agreement details');
4021 RAISE FND_API.G_EXC_ERROR;
4022 END IF;
4023
4024 IF g_batch_details.batch_status_code NOT IN ( 'CLEARING',
4025 'SUBMITTED') THEN
4026
4027 fun_net_util.Log_String(g_event_level,l_path,
4028 'Batch Status is not CLEARING');
4029 FND_MESSAGE.SET_NAME('FUN','FUN_NET_BATCH_STATUS_ERROR');
4030 RAISE FND_API.G_EXC_ERROR;
4031 END IF;
4032
4033 /* Get Functional Currency */
4034 get_functional_currency;
4035
4036
4037 /* Default Batch Details */
4038
4039 IF default_batch_details = FND_API.G_FALSE THEN
4040
4041 fun_net_util.Log_String(g_event_level,l_path,
4042 'Error defaulting Batch Details');
4043 FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4044 RAISE FND_API.G_EXC_ERROR;
4045 END IF;
4046
4047 -- Calculate Netting balances
4048
4049 calculate_AP_AR_balances(l_amt_to_net,l_status_flag);
4050
4051 IF l_status_flag = FND_API.G_FALSE THEN
4052
4053 RAISE FND_API.G_EXC_ERROR;
4054
4055 END IF;
4056
4057
4058
4059
4060 /* Validate AR Transactions to check if they have changed */
4061
4062 Validate_AR_Txns(
4063 txnTable => txnTable,
4064 x_return_status => l_return_status
4065 );
4066
4067 IF l_return_status = FND_API.G_FALSE THEN
4068
4069 fun_net_util.Log_String(g_event_level,l_path,
4070 'Error Validating AR Transactions');
4071 FND_MESSAGE.SET_NAME('FUN','FUN_NET_VAL_AR_TXN_FAIL');
4072 RAISE VALIDATE_AR_TXN_FAIL;
4073
4074 END IF;
4075
4076
4077
4078 /* Validate GL Period - AP */
4079
4080 Validate_Settlement_Period(
4081 x_appln_id => 200,
4082 x_period_name => l_period_name,
4083 x_return_status => l_return_status ,
4084 x_return_msg => l_msg_data);
4085
4086 IF l_return_status = FND_API.G_FALSE THEN
4087
4088 fun_net_util.Log_String(g_event_level,l_path,
4089 'Error Validating AP Period');
4090 FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_PERIOD_FAIL');
4091 FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4092 g_batch_details.settlement_date,FALSE);
4093 RAISE FND_API.G_EXC_ERROR;
4094 END IF;
4095
4096 /* Validate GL Period - AR */
4097
4098 Validate_Settlement_Period
4099 (x_appln_id => 222,
4100 x_period_name => l_period_name,
4101 x_return_status => l_return_status ,
4102 x_return_msg => l_msg_data);
4103
4104 IF l_return_status = FND_API.G_FALSE THEN
4105
4106 fun_net_util.Log_String(g_event_level,l_path,
4107 'Error Validating AR Period');
4108 FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_PERIOD_FAIL');
4109 FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4110 g_batch_details.settlement_date,FALSE);
4111 RAISE FND_API.G_EXC_ERROR;
4112 END IF;
4113
4114 Validate_Settlement_Period
4115 (x_appln_id => 101,
4116 x_period_name => l_period_name,
4117 x_return_status => l_return_status ,
4118 x_return_msg => l_msg_data);
4119
4120 IF l_return_status = FND_API.G_FALSE THEN
4121
4122 fun_net_util.Log_String(g_event_level,l_path,
4123 'Error Validating GL Period');
4124 FND_MESSAGE.SET_NAME('FUN','FUN_NET_GL_PERIOD_FAIL');
4125 FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4126 g_batch_details.settlement_date,FALSE);
4127 RAISE FND_API.G_EXC_ERROR;
4128 END IF;
4129
4130
4131 /* Get Netting Bank Account Information */
4132 Get_Netting_Bank_Details(l_bank_acct_use_id,
4133 l_bank_acct_name,
4134 l_bank_acct_num,
4135 l_le_id,
4136 l_bank_num,
4137 l_return_status);
4138
4139 IF l_return_status = FND_API.G_FALSE THEN
4140
4141 fun_net_util.Log_String(g_event_level,l_path,
4142 'Error in Getting Netting Bank Details');
4143 FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BANK_DETAILS');
4144 RAISE FND_API.G_EXC_ERROR;
4145 END IF;
4146
4147 --------------------------------------------------------------------
4148 /******************** Settle AP Transactions ***********************/
4149 --------------------------------------------------------------------
4150
4151 settle_ap_invs(p_bank_acct_use_id => l_bank_acct_use_id,
4152 p_bank_acct_name => l_bank_acct_name,
4153 p_bank_acct_num => l_bank_acct_num,
4154 p_le_id => l_le_id,
4155 p_bank_num => l_bank_num,
4156 x_return_status => l_return_status);
4157
4158
4159 IF l_return_status = FND_API.G_FALSE THEN
4160
4161 fun_net_util.Log_String(g_event_level,l_path,
4162 'Error in Settling AP Invoices');
4163 -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_SETTLE_FAIL');
4164 RAISE FND_API.G_EXC_ERROR;
4165 END IF;
4166
4167
4168 /* -----------------------------------------------------------------*/
4169 /*********************** SETTLE AR TRANSACTIONS *******************/
4170 -------------------------------------------------------------------*/
4171
4172 settle_ar_txns( txnTable => txnTable,
4173 p_bank_acct_name => l_bank_acct_name,
4174 p_bank_acct_num => l_bank_acct_num,
4175 p_bank_acct_use_id => l_bank_acct_use_id,
4176 x_return_status => l_return_status,
4177 x_msg_data => l_msg_data,
4178 x_msg_count => l_msg_count);
4179
4180
4181 IF l_return_status = FND_API.G_FALSE THEN
4182 -- x_msg_count := l_msg_count;
4183 -- x_msg_data := l_msg_data;
4184
4185 fun_net_util.Log_String(g_event_level,l_path,
4186 'Error in Settling AR Transactions');
4187 -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_SETTLE_FAIL');
4188 RAISE FND_API.G_EXC_ERROR;
4189 END IF;
4190
4191
4192
4193 -- Update Batch Status to Complete
4194
4195 IF NOT update_batch_status('COMPLETE') THEN
4196
4197 fun_net_util.Log_String(g_event_level,l_path,
4198 'Error in Setting Status to Complete');
4199 FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4200 RAISE FND_API.G_EXC_ERROR;
4201
4202 ELSE
4203
4204 -- Submit the Data Extract report
4205
4206 l_Request_id := FND_REQUEST.SUBMIT_REQUEST ( 'FUN'
4207 , 'FUNNTDEX'
4208 , null
4209 , null
4210 , FALSE
4211 , g_batch_id
4212 );
4213
4214 fun_net_util.Log_String(g_state_level,l_path,
4215 'Data Extract request: '||l_request_id);
4216
4217
4218 END IF;
4219
4220
4221 -- Standard check of p_commit.
4222 IF FND_API.To_Boolean( p_commit ) THEN
4223 COMMIT WORK;
4224 END IF;
4225
4226 -- Call Procedure to perform common tasks after committing
4227 -- Example : Unlocking txns , setting Agreement Status
4228
4229 perform_common_tasks;
4230
4231 EXCEPTION
4232
4233
4234 WHEN FND_API.G_EXC_ERROR THEN
4235 ROLLBACK TO settle_net_batch_SP;
4236 batch_status_flag := update_batch_status('ERROR');
4237
4238 --perform_common_tasks;
4239 x_return_status := FND_API.G_RET_STS_ERROR;
4240 FND_MSG_PUB.Count_And_Get (
4241 p_count => x_msg_count,
4242 p_data => x_msg_data );
4243
4244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4245 ROLLBACK TO settle_net_batch_SP;
4246 batch_status_flag := update_batch_status('ERROR');
4247
4248 --perform_common_tasks;
4249
4250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4251
4252 FND_MSG_PUB.Count_And_Get (
4253 p_count => x_msg_count,
4254 p_data => x_msg_data );
4255
4256 WHEN VALIDATE_AR_TXN_FAIL THEN
4257 ROLLBACK TO settle_Net_Batch_SP;
4258 batch_status_flag := update_batch_status('CANCELLED');
4259 perform_common_tasks; -- Bug 5608043
4260
4261 x_return_status := FND_API.G_RET_STS_ERROR;
4262 FND_MSG_PUB.Count_And_Get (
4263 p_count => x_msg_count,
4264 p_data => x_msg_data );
4265
4266
4267 WHEN OTHERS THEN
4268
4269 ROLLBACK TO settle_Net_Batch_SP;
4270 batch_status_flag := update_batch_status('ERROR');
4271
4272 -- perform_common_tasks;
4273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4274
4275 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4276 FND_MSG_PUB.Add_Exc_Msg( 'FUN_ARAP_NET_PKG', 'settle_net_batch');
4277 END IF;
4278 FND_MSG_PUB.Count_And_Get (
4279 p_count => x_msg_count,
4280 p_data => x_msg_data );
4281
4282
4283 END settle_net_batch;
4284
4285
4286 BEGIN
4287 g_today := TRUNC(sysdate);
4288 --===========================FND_LOG.START=====================================
4289
4290 g_state_level := FND_LOG.LEVEL_STATEMENT;
4291 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
4292 g_event_level := FND_LOG.LEVEL_EVENT;
4293 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
4294 g_error_level := FND_LOG.LEVEL_ERROR;
4295 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
4296 g_path := 'FUN.PLSQL.funnttxb.FUN_NET_ARAP_PKG.';
4297
4298 --===========================FND_LOG.END=======================================
4299
4300 END FUN_NET_ARAP_PKG;