[Home] [Help]
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
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;
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
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;
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
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
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
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
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
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;
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;
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;
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
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
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......');
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
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');
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;
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);
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 :=
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,
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 */
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 ';
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 ';
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' ;
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);
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);
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 ;
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');
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
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
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,
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);
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;
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));
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));
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');
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 ;
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
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);
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;
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');
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;
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);
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 :=
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,
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 */
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,
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 ||
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' ;
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 */
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);
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 :=
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 ;
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 */
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');
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);
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 ;
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));
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));
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');
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 ;
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 ';
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);
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 ;
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');
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;
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(
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
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;
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;
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');
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
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
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;
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);
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);
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 */
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
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;
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;
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;
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');
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
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;
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;
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;
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
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
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,
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,
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
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);
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);
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,
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,
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,
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
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,
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,
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
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,
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
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;
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,
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,
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,
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,
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,
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,
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,
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
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,
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,
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,
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
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,
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
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,
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
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
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
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
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);
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
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));
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;
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
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);
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);
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);
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));
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));
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
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
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
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
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
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);
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
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;
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;
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;
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,
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,
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
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 */
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');
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;
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
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;
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,
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,
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,
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 */
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
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,
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'
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
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
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;
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);
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;
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
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);
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;
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
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
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: '
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);
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);
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');
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);
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);
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,
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
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
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
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
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;
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;
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;
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;
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;
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
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
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;
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;
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
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');
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:
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;
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
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;
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' */
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,
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;
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
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
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
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
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;
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));
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 */
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,
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;
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*/
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';
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';
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 */
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
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'
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;
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
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;
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;
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;
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
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);
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;
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
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 */
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,
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);
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));
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));
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);
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;
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) );
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
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
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,
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(
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,
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,
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
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;
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;
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,
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
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,
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,
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,
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,
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,
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,
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
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
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);
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,
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,
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,
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,
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
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 ;
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);
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;
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;
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);
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.
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,
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,
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 );
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;
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
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
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
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;
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);
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);
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
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
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;
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;
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
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
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
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);
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
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
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');
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,
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,
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(
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,
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,
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,
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 );
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);
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;
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;
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 --
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;
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;
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
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,
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 --
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;
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;
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;
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;
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 );
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',
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 );
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
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 */
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 );
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);
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);
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);
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 );
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,
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');
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;
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,
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,
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;
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 );
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