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