[Home] [Help]
281: FUN_NET_BATCHES_PKG.Update_Row
282: (x_batch_id => g_batch_id,
283: x_batch_status_code => p_status);
284: END IF;
285: fun_net_util.Log_String(g_state_level,l_path,'Successfully updated batch status');
286: RETURN TRUE;
287:
288: EXCEPTION
289: WHEN NO_DATA_FOUND THEN
286: RETURN TRUE;
287:
288: EXCEPTION
289: WHEN NO_DATA_FOUND THEN
290: fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION: No data found');
291: RETURN FALSE;
292: WHEN OTHERS THEN
293: fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION: '||sqlerrm);
294: RETURN FALSE;
289: WHEN NO_DATA_FOUND THEN
290: fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION: No data found');
291: RETURN FALSE;
292: WHEN OTHERS THEN
293: fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION: '||sqlerrm);
294: RETURN FALSE;
295: END update_batch_status;
296:
297:
308: l_currency_code VARCHAR2(15);
309: l_path VARCHAR2(100);
310: BEGIN
311: l_path := g_path || 'Prepare_AR_Transactions';
312: fun_net_util.Log_String(g_proc_level,l_path,'Preparing AR Transactions');
313:
314: -- Build Select Clause --
315: l_trx_select_clause :=
316: 'SELECT rct.customer_trx_id,
315: l_trx_select_clause :=
316: 'SELECT rct.customer_trx_id,
317: fun_net_arap_pkg.Calculate_ar_trx_amt(rct.customer_trx_id) transaction_amount,
318: sum(arps.amount_due_remaining) AS open_amount ' ;
319: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
320:
321: l_trx_from_clause :=
322: ' FROM ra_customer_trx_all rct,
323: ar_payment_schedules_all arps,
322: ' FROM ra_customer_trx_all rct,
323: ar_payment_schedules_all arps,
324: ra_cust_trx_types_all rctt,
325: fun_net_customers_all fnc ';
326: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
327:
328: -- Build the WHERE clause --
329: /* Only select the transactions where the due date is on or before the Tnx due date in the batch */
330: /* Only select transactions where the tnx due date is between the start and end date of the agreement */
375: AND rctt.org_id = fnc.org_id
376: AND ((:v_sel_past_due_date1=''N'') OR (:v_sel_past_due_date2=''Y'' AND
377: TRUNC(arps.due_date) + nvl(:v_days_past_due,0) < trunc(sysdate) ))
378: AND arps.org_id = fnc.org_id';
379: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
380:
381: /* Build the Group by clause */
382: l_trx_group_by_clause :=
383: ' GROUP BY rct.customer_trx_id,
396: WHEN 'AMOUNT_ASCENDING' THEN 'open_amount'
397: WHEN 'AMOUNT_DESCENDING' THEN 'open_amount'
398: END; */
399:
400: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
401:
402: /* Build the Order by clause */
403: --Order by the Customer Priority .If all the customers have the same priority =1,then use the netting order rule.
404: l_trx_order_by_clause :=
412: WHEN 'AMOUNT_ASCENDING' THEN 'sum(arps.amount_due_remaining) asc'
413: WHEN 'AMOUNT_DESCENDING' THEN 'sum(arps.amount_due_remaining) desc'
414: WHEN 'TRX_DATE' THEN 'rct.trx_date asc'
415: END;
416: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
417:
418: /* Select the transactions with currency specified in the agreement if the netting currency rule is Single currency */
419: fun_net_util.Log_String(g_state_level,l_path,'currency rule :'||g_net_currency_rule);
420:
415: END;
416: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
417:
418: /* Select the transactions with currency specified in the agreement if the netting currency rule is Single currency */
419: fun_net_util.Log_String(g_state_level,l_path,'currency rule :'||g_net_currency_rule);
420:
421: IF g_net_currency_rule = 'SINGLE_CURRENCY' THEN
422:
423: l_trx_where_clause := l_trx_where_clause || ' AND RCT.INVOICE_CURRENCY_CODE = :v_currency' ;
421: IF g_net_currency_rule = 'SINGLE_CURRENCY' THEN
422:
423: l_trx_where_clause := l_trx_where_clause || ' AND RCT.INVOICE_CURRENCY_CODE = :v_currency' ;
424:
425: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
426: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
427: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
428: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
429: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
422:
423: l_trx_where_clause := l_trx_where_clause || ' AND RCT.INVOICE_CURRENCY_CODE = :v_currency' ;
424:
425: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
426: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
427: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
428: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
429: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
430:
423: l_trx_where_clause := l_trx_where_clause || ' AND RCT.INVOICE_CURRENCY_CODE = :v_currency' ;
424:
425: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
426: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
427: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
428: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
429: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
430:
431: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause ;
424:
425: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
426: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
427: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
428: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
429: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
430:
431: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause ;
432: insert_transactions(l_sql_stmt , g_net_currency, 'AR');
425: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
426: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
427: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
428: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
429: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
430:
431: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause ;
432: insert_transactions(l_sql_stmt , g_net_currency, 'AR');
433:
435: ELSIF g_net_currency_rule = 'ACCOUNTING_CURRENCY' THEN
436:
437: --Select all the invoices irrespective of the currency code and call GL API to convert the amount from the invoice currency to the accounting currency
438:
439: fun_net_util.Log_String(g_state_level,l_path,'g_batch_details.exchange_rate_type :' ||g_batch_details.exchange_rate_type);
440:
441: l_trx_select_clause :=
442: 'SELECT rct.customer_trx_id,
443: fun_net_arap_pkg.Calculate_ar_trx_amt(rct.customer_trx_id) transaction_amount,
451: l_trx_group_by_clause := l_trx_group_by_clause ||
452: ',' ||
453: ' rct.invoice_currency_code ';
454:
455: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
456: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
457: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
458: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
459: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
452: ',' ||
453: ' rct.invoice_currency_code ';
454:
455: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
456: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
457: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
458: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
459: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
460:
453: ' rct.invoice_currency_code ';
454:
455: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
456: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
457: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
458: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
459: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
460:
461: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
454:
455: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
456: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
457: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
458: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
459: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
460:
461: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
462: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
455: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
456: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
457: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
458: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
459: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
460:
461: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
462: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
463: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,2001,2000));
458: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
459: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
460:
461: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
462: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
463: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,2001,2000));
464: insert_transactions(l_sql_stmt , g_func_currency, 'AR');
465:
466: ELSIF g_net_currency_rule = 'WITHIN_CURRENCY' THEN
459: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
460:
461: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
462: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
463: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,2001,2000));
464: insert_transactions(l_sql_stmt , g_func_currency, 'AR');
465:
466: ELSIF g_net_currency_rule = 'WITHIN_CURRENCY' THEN
467:
484: EXIT WHEN trx_cur%NOTFOUND;
485: --Set the select where clause to select all transactions belonging to the cursor currency, as shown above in the first if condition.
486: g_currency_count := g_currency_count + 1;
487:
488: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
489: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
490: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
491: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
492: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
485: --Set the select where clause to select all transactions belonging to the cursor currency, as shown above in the first if condition.
486: g_currency_count := g_currency_count + 1;
487:
488: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
489: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
490: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
491: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
492: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
493:
486: g_currency_count := g_currency_count + 1;
487:
488: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
489: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
490: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
491: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
492: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
493:
494: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
487:
488: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
489: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
490: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
491: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
492: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
493:
494: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
495: insert_transactions (l_sql_stmt , l_currency_code, 'AR');
488: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
489: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
490: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_trx_where_clause);
491: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_trx_group_by_clause);
492: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_trx_order_by_clause);
493:
494: l_sql_stmt := l_trx_select_clause || l_trx_from_clause || l_trx_where_clause || l_trx_group_by_clause || l_trx_order_by_clause;
495: insert_transactions (l_sql_stmt , l_currency_code, 'AR');
496:
502:
503: RETURN TRUE;
504: EXCEPTION
505: WHEN OTHERS THEN
506: fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
507: RETURN FALSE;
508: END prepare_ar_transactions;
509:
510:
523: l_currency_code VARCHAR2(15);
524: l_path VARCHAR2(100);
525: BEGIN
526: l_path := g_path || 'Prepare_AP_Transactions';
527: fun_net_util.Log_String(g_proc_level,l_path,'Preparing AP Transactions');
528:
529: /* Build the select clause */
530: l_inv_select_clause :=
531: -- Select all ap_invoices based on the batch id
534: api.invoice_amount AS transaction_amount,
535: SUM(aps.amount_remaining) AS open_amount,
536: min(aps.payment_num),
537: max(aps.payment_num) ';
538: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
539:
540: /* Build the from clause */
541: l_inv_from_clause :=
542: ' FROM ap_invoices_all api,
541: l_inv_from_clause :=
542: ' FROM ap_invoices_all api,
543: fun_net_suppliers_all fns,
544: ap_payment_schedules_all aps';
545: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
546:
547: /* Build where clause */
548: /* Do not select invoices that are on hold */
549: /* Select invoices that have been approved */
596: l_inv_where_clause := l_inv_where_clause || ' AND JMF_SHIKYU_GRP.Is_AP_Inv_Shikyu_Nettable_func(API.invoice_id) = ''N'' ';
597: l_inv_where_clause := l_inv_where_clause || ' AND EXISTS ' || ' (SELECT apd.distribution_line_number '|| ' FROM ap_invoice_distributions_all apd '|| 'WHERE apd.invoice_id = api.invoice_id '|| 'AND apd.po_distribution_id IS NOT NULL) ';
598: END IF;
599:
600: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
601:
602: /* Build the Group by Clause */
603: l_inv_group_by_clause :=
604: ' GROUP BY api.invoice_id,
618: WHEN 'AMOUNT_ASCENDING' THEN 'open_amount'
619: WHEN 'AMOUNT_DESCENDING' THEN 'open_amount'
620: END; */
621:
622: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
623:
624: /* Build the order by Clause . If the all the vendors priority =1 , then use the netting order by rule */
625: l_inv_order_by_clause :=
626: ' ORDER BY fns.supplier_priority,';
631: WHEN 'AMOUNT_ASCENDING' THEN 'SUM(aps.amount_remaining) asc'
632: WHEN 'AMOUNT_DESCENDING' THEN 'SUM(aps.amount_remaining) desc'
633: WHEN 'TRX_DATE' THEN 'api.invoice_date asc'
634: END;
635: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
636:
637: fun_net_util.Log_String(g_state_level,l_path,'Net currency rule '||g_net_currency_rule);
638: --Select only the transactions which have the currency code of the netting currency rule if the Netting currency rule = 'SINGLE_CURRENCY'
639: IF g_net_currency_rule = 'SINGLE_CURRENCY' THEN
633: WHEN 'TRX_DATE' THEN 'api.invoice_date asc'
634: END;
635: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
636:
637: fun_net_util.Log_String(g_state_level,l_path,'Net currency rule '||g_net_currency_rule);
638: --Select only the transactions which have the currency code of the netting currency rule if the Netting currency rule = 'SINGLE_CURRENCY'
639: IF g_net_currency_rule = 'SINGLE_CURRENCY' THEN
640:
641: l_inv_where_clause := l_inv_where_clause || ' AND API.INVOICE_CURRENCY_CODE = :v_currency' ;
639: IF g_net_currency_rule = 'SINGLE_CURRENCY' THEN
640:
641: l_inv_where_clause := l_inv_where_clause || ' AND API.INVOICE_CURRENCY_CODE = :v_currency' ;
642: /* Build the entire select statement */
643: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
644: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
645: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
646: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
647: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
640:
641: l_inv_where_clause := l_inv_where_clause || ' AND API.INVOICE_CURRENCY_CODE = :v_currency' ;
642: /* Build the entire select statement */
643: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
644: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
645: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
646: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
647: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
648: l_sql_stmt :=
641: l_inv_where_clause := l_inv_where_clause || ' AND API.INVOICE_CURRENCY_CODE = :v_currency' ;
642: /* Build the entire select statement */
643: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
644: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
645: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
646: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
647: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
648: l_sql_stmt :=
649: l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
642: /* Build the entire select statement */
643: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
644: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
645: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
646: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
647: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
648: l_sql_stmt :=
649: l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
650: /* Call the procedure to insert AP netting transactions */
643: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
644: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
645: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
646: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
647: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
648: l_sql_stmt :=
649: l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
650: /* Call the procedure to insert AP netting transactions */
651: insert_transactions(l_sql_stmt , g_net_currency, 'AP');
674: api.invoice_currency_code,
675: fc.precision,
676: fns.supplier_priority ';
677: /* Build the entire select statement */
678: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
679: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
680: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
681: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
682: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
675: fc.precision,
676: fns.supplier_priority ';
677: /* Build the entire select statement */
678: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
679: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
680: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
681: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
682: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
683:
676: fns.supplier_priority ';
677: /* Build the entire select statement */
678: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
679: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
680: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
681: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
682: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
683:
684: l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
677: /* Build the entire select statement */
678: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
679: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
680: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
681: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
682: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
683:
684: l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
685: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
678: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
679: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
680: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
681: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
682: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
683:
684: l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
685: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
686: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,2001,2000));
681: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
682: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
683:
684: l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
685: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
686: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,2001,2000));
687:
688: insert_transactions(l_sql_stmt, g_func_currency, 'AP');
689:
682: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
683:
684: l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
685: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,1,2000));
686: fun_net_util.Log_String(g_state_level,l_path,substr(l_sql_stmt,2001,2000));
687:
688: insert_transactions(l_sql_stmt, g_func_currency, 'AP');
689:
690: ELSIF g_net_currency_rule = 'WITHIN_CURRENCY' THEN
704:
705: g_currency_count := g_currency_count + 1;
706: --Set the select where clause to select all transactions belonging to the cursor currency, as shown above in the first if condition.
707:
708: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
709: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
710: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
711: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
712: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
705: g_currency_count := g_currency_count + 1;
706: --Set the select where clause to select all transactions belonging to the cursor currency, as shown above in the first if condition.
707:
708: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
709: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
710: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
711: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
712: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
713:
706: --Set the select where clause to select all transactions belonging to the cursor currency, as shown above in the first if condition.
707:
708: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
709: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
710: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
711: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
712: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
713:
714: l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
707:
708: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
709: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
710: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
711: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
712: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
713:
714: l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
715: insert_transactions(l_sql_stmt , l_currency_code, 'AP');
708: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_inv_select_clause);
709: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_inv_from_clause);
710: fun_net_util.Log_String(g_state_level,l_path,'WHERE : '||l_inv_where_clause);
711: fun_net_util.Log_String(g_state_level,l_path,'GROUP : '||l_inv_group_by_clause);
712: fun_net_util.Log_String(g_state_level,l_path,'ORDER : '||l_inv_order_by_clause);
713:
714: l_sql_stmt := l_inv_select_clause || l_inv_from_clause || l_inv_where_clause || l_inv_group_by_clause || l_inv_order_by_clause ;
715: insert_transactions(l_sql_stmt , l_currency_code, 'AP');
716:
721: RETURN TRUE;
722:
723: EXCEPTION
724: WHEN OTHERS THEN
725: fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
726: RETURN FALSE;
727: END prepare_ap_transactions;
728:
729:
737: l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
738: l_path VARCHAR2(100);
739: BEGIN
740: l_path := g_path || 'lock_ap_pymt_schedules';
741: fun_net_util.Log_String(g_state_level,l_path,'Start of locking');
742: x_return_status := FND_API.G_TRUE;
743: fun_net_util.Log_String(g_state_level,l_path,'p_schd_tab.count :'||p_schd_tab.count);
744: fun_net_util.Log_String(g_state_level,l_path,'p_batch_id :'||p_batch_id);
745: -- Check for Mandatory Parameters
739: BEGIN
740: l_path := g_path || 'lock_ap_pymt_schedules';
741: fun_net_util.Log_String(g_state_level,l_path,'Start of locking');
742: x_return_status := FND_API.G_TRUE;
743: fun_net_util.Log_String(g_state_level,l_path,'p_schd_tab.count :'||p_schd_tab.count);
744: fun_net_util.Log_String(g_state_level,l_path,'p_batch_id :'||p_batch_id);
745: -- Check for Mandatory Parameters
746: IF p_schd_tab.count = 0 OR p_batch_id IS NULL THEN
747: x_return_status := FND_API.G_FALSE;
740: l_path := g_path || 'lock_ap_pymt_schedules';
741: fun_net_util.Log_String(g_state_level,l_path,'Start of locking');
742: x_return_status := FND_API.G_TRUE;
743: fun_net_util.Log_String(g_state_level,l_path,'p_schd_tab.count :'||p_schd_tab.count);
744: fun_net_util.Log_String(g_state_level,l_path,'p_batch_id :'||p_batch_id);
745: -- Check for Mandatory Parameters
746: IF p_schd_tab.count = 0 OR p_batch_id IS NULL THEN
747: x_return_status := FND_API.G_FALSE;
748: RETURN;
761: AND amount_remaining <> 0
762: AND TRUNC(due_date) <= NVL(p_trx_due_date,TRUNC(due_date))
763: AND aps.payment_num BETWEEN p_schd_tab(i).min_payment_num
764: AND p_schd_tab(i).max_payment_num;
765: fun_net_util.Log_String(g_state_level,l_path,'invoice_id :'||p_schd_tab(i).invoice_id);
766: fun_net_util.Log_String(g_state_level,l_path,'Min_payment_number :'||p_schd_tab(i).min_payment_num);
767: fun_net_util.Log_String(g_state_level,l_path,'Max_payment_number :'||p_schd_tab(i).max_payment_num);
768: END LOOP;
769: fun_net_util.Log_String(g_state_level,l_path,'Payment schedules updated');
762: AND TRUNC(due_date) <= NVL(p_trx_due_date,TRUNC(due_date))
763: AND aps.payment_num BETWEEN p_schd_tab(i).min_payment_num
764: AND p_schd_tab(i).max_payment_num;
765: fun_net_util.Log_String(g_state_level,l_path,'invoice_id :'||p_schd_tab(i).invoice_id);
766: fun_net_util.Log_String(g_state_level,l_path,'Min_payment_number :'||p_schd_tab(i).min_payment_num);
767: fun_net_util.Log_String(g_state_level,l_path,'Max_payment_number :'||p_schd_tab(i).max_payment_num);
768: END LOOP;
769: fun_net_util.Log_String(g_state_level,l_path,'Payment schedules updated');
770: EXCEPTION
763: AND aps.payment_num BETWEEN p_schd_tab(i).min_payment_num
764: AND p_schd_tab(i).max_payment_num;
765: fun_net_util.Log_String(g_state_level,l_path,'invoice_id :'||p_schd_tab(i).invoice_id);
766: fun_net_util.Log_String(g_state_level,l_path,'Min_payment_number :'||p_schd_tab(i).min_payment_num);
767: fun_net_util.Log_String(g_state_level,l_path,'Max_payment_number :'||p_schd_tab(i).max_payment_num);
768: END LOOP;
769: fun_net_util.Log_String(g_state_level,l_path,'Payment schedules updated');
770: EXCEPTION
771:
765: fun_net_util.Log_String(g_state_level,l_path,'invoice_id :'||p_schd_tab(i).invoice_id);
766: fun_net_util.Log_String(g_state_level,l_path,'Min_payment_number :'||p_schd_tab(i).min_payment_num);
767: fun_net_util.Log_String(g_state_level,l_path,'Max_payment_number :'||p_schd_tab(i).max_payment_num);
768: END LOOP;
769: fun_net_util.Log_String(g_state_level,l_path,'Payment schedules updated');
770: EXCEPTION
771:
772: WHEN OTHERS THEN
773:
770: EXCEPTION
771:
772: WHEN OTHERS THEN
773:
774: fun_net_util.Log_String(g_state_level,l_path,'Failure in locking ap payment schedules');
775: x_return_status := FND_API.G_FALSE;
776: END lock_ap_pymt_schedules;
777:
778: PROCEDURE insert_transactions(p_inv_cur VARCHAR2,p_currency_code VARCHAR2, p_appln VARCHAR2) IS
809: l_path varchar2(100);
810: l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
811: BEGIN
812: l_path := g_path || 'Insert_Transactions';
813: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,1,2000));
814: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,2001,2000));
815: fun_net_util.Log_String(g_state_level,l_path,'Currency:'||p_currency_code);
816: fun_net_util.Log_String(g_state_level,l_path,'Application:'||p_appln);
817: fun_net_util.Log_String(g_state_level,l_path,'Currency rule: '||g_net_currency_rule);
810: l_checkrun_id ap_inv_selection_criteria_all.checkrun_id%TYPE;
811: BEGIN
812: l_path := g_path || 'Insert_Transactions';
813: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,1,2000));
814: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,2001,2000));
815: fun_net_util.Log_String(g_state_level,l_path,'Currency:'||p_currency_code);
816: fun_net_util.Log_String(g_state_level,l_path,'Application:'||p_appln);
817: fun_net_util.Log_String(g_state_level,l_path,'Currency rule: '||g_net_currency_rule);
818: /* Check for mandatory parameters */
811: BEGIN
812: l_path := g_path || 'Insert_Transactions';
813: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,1,2000));
814: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,2001,2000));
815: fun_net_util.Log_String(g_state_level,l_path,'Currency:'||p_currency_code);
816: fun_net_util.Log_String(g_state_level,l_path,'Application:'||p_appln);
817: fun_net_util.Log_String(g_state_level,l_path,'Currency rule: '||g_net_currency_rule);
818: /* Check for mandatory parameters */
819: IF p_inv_cur IS NULL OR p_currency_code IS NULL OR p_appln IS NULL THEN
812: l_path := g_path || 'Insert_Transactions';
813: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,1,2000));
814: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,2001,2000));
815: fun_net_util.Log_String(g_state_level,l_path,'Currency:'||p_currency_code);
816: fun_net_util.Log_String(g_state_level,l_path,'Application:'||p_appln);
817: fun_net_util.Log_String(g_state_level,l_path,'Currency rule: '||g_net_currency_rule);
818: /* Check for mandatory parameters */
819: IF p_inv_cur IS NULL OR p_currency_code IS NULL OR p_appln IS NULL THEN
820: RETURN;
813: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,1,2000));
814: fun_net_util.Log_String(g_state_level,l_path,substr(p_inv_cur,2001,2000));
815: fun_net_util.Log_String(g_state_level,l_path,'Currency:'||p_currency_code);
816: fun_net_util.Log_String(g_state_level,l_path,'Application:'||p_appln);
817: fun_net_util.Log_String(g_state_level,l_path,'Currency rule: '||g_net_currency_rule);
818: /* Check for mandatory parameters */
819: IF p_inv_cur IS NULL OR p_currency_code IS NULL OR p_appln IS NULL THEN
820: RETURN;
821: END IF;
819: IF p_inv_cur IS NULL OR p_currency_code IS NULL OR p_appln IS NULL THEN
820: RETURN;
821: END IF;
822:
823: fun_net_util.Log_String(g_state_level,l_path,'Agreement count:'||g_agreement_count);
824: /* If this is the first agreement and the netting currency rule is as below then this will be the first group of tnxs to be parsed , need not create a batch as we can use the batch that already exists */
825: IF g_agreement_count = 1 AND g_net_currency_rule IN ('SINGLE_CURRENCY','ACCOUNTING_CURRENCY') THEN
826: fun_net_util.Log_String(g_state_level,l_path,'Setting currency code for first agreement');
827: g_batch_list(g_idx).currency := p_currency_code;
822:
823: fun_net_util.Log_String(g_state_level,l_path,'Agreement count:'||g_agreement_count);
824: /* If this is the first agreement and the netting currency rule is as below then this will be the first group of tnxs to be parsed , need not create a batch as we can use the batch that already exists */
825: IF g_agreement_count = 1 AND g_net_currency_rule IN ('SINGLE_CURRENCY','ACCOUNTING_CURRENCY') THEN
826: fun_net_util.Log_String(g_state_level,l_path,'Setting currency code for first agreement');
827: g_batch_list(g_idx).currency := p_currency_code;
828: /* If the rule is 'NET WITHIN CURRENCY ' */
829: ELSIF g_agreement_count = 1 AND g_net_currency_rule = 'WITHIN_CURRENCY' AND g_currency_count = 1 THEN
830: fun_net_util.Log_String(g_state_level,l_path,'Setting currency code for first currency and first agreement');
826: fun_net_util.Log_String(g_state_level,l_path,'Setting currency code for first agreement');
827: g_batch_list(g_idx).currency := p_currency_code;
828: /* If the rule is 'NET WITHIN CURRENCY ' */
829: ELSIF g_agreement_count = 1 AND g_net_currency_rule = 'WITHIN_CURRENCY' AND g_currency_count = 1 THEN
830: fun_net_util.Log_String(g_state_level,l_path,'Setting currency code for first currency and first agreement');
831: g_batch_list(g_idx).currency := p_currency_code;
832: ELSE /* prow_count > 1 or if this is not the first agreement then */
833: fun_net_util.Log_String(g_state_level,l_path,'Checking if batch already exists');
834: IF NOT batch_exists(p_currency_code) THEN
829: ELSIF g_agreement_count = 1 AND g_net_currency_rule = 'WITHIN_CURRENCY' AND g_currency_count = 1 THEN
830: fun_net_util.Log_String(g_state_level,l_path,'Setting currency code for first currency and first agreement');
831: g_batch_list(g_idx).currency := p_currency_code;
832: ELSE /* prow_count > 1 or if this is not the first agreement then */
833: fun_net_util.Log_String(g_state_level,l_path,'Checking if batch already exists');
834: IF NOT batch_exists(p_currency_code) THEN
835: fun_net_util.Log_String(g_state_level,l_path,'Batch does not exist. Creating new batch');
836: insert_batch_record(p_currency_code);
837: g_idx := g_idx + 1;
831: g_batch_list(g_idx).currency := p_currency_code;
832: ELSE /* prow_count > 1 or if this is not the first agreement then */
833: fun_net_util.Log_String(g_state_level,l_path,'Checking if batch already exists');
834: IF NOT batch_exists(p_currency_code) THEN
835: fun_net_util.Log_String(g_state_level,l_path,'Batch does not exist. Creating new batch');
836: insert_batch_record(p_currency_code);
837: g_idx := g_idx + 1;
838: g_batch_list(g_idx).batch_id := g_batch_id;
839: g_batch_list(g_idx).agreement_id := g_agreement_id;
851: IF l_checkrun_id is NULL THEN
852: SELECT ap_inv_selection_criteria_s.nextval
853: INTO l_checkrun_id
854: FROM dual;
855: fun_net_util.Log_String(g_state_level,l_path,'l_checkrun_id :'||l_checkrun_id);
856:
857: -- Update Netting Batch with the Checkrun id
858:
859: UPDATE FUN_NET_BATCHES_ALL
858:
859: UPDATE FUN_NET_BATCHES_ALL
860: SET checkrun_id = l_checkrun_id
861: WHERE batch_id = g_batch_id;
862: fun_net_util.Log_String(g_state_level,l_path,'Batch updated');
863: END IF;
864:
865: fun_net_util.Log_String(g_state_level,l_path,'Inserting invoices into Netting tables');
866: l_rank := 0;
861: WHERE batch_id = g_batch_id;
862: fun_net_util.Log_String(g_state_level,l_path,'Batch updated');
863: END IF;
864:
865: fun_net_util.Log_String(g_state_level,l_path,'Inserting invoices into Netting tables');
866: l_rank := 0;
867:
868: IF p_appln = 'AP' AND g_net_currency_rule IN ('SINGLE_CURRENCY', 'WITHIN_CURRENCY') THEN
869: fun_net_util.Log_String(g_state_level,l_path,'Fetching the AP invoices');
865: fun_net_util.Log_String(g_state_level,l_path,'Inserting invoices into Netting tables');
866: l_rank := 0;
867:
868: IF p_appln = 'AP' AND g_net_currency_rule IN ('SINGLE_CURRENCY', 'WITHIN_CURRENCY') THEN
869: fun_net_util.Log_String(g_state_level,l_path,'Fetching the AP invoices');
870: OPEN inv_rec FOR p_inv_cur USING g_agreement_id,
871: g_agreement_id,
872: g_batch_details.transaction_due_date,
873: g_agr_start_date,
883: l_pymt_sch_table(i).min_payment_num := l_min_payment_num(i);
884: l_pymt_sch_table(i).max_payment_num := l_max_payment_num(i);
885: END LOOP;
886:
887: fun_net_util.Log_String(g_state_level,l_path,'Calling lock_ap_pymt_schedules for batch id:'||g_batch_id);
888: lock_ap_pymt_schedules(
889: p_batch_id => g_batch_id,
890: p_trx_due_date => g_batch_details.transaction_due_date,
891: p_schd_tab => l_pymt_sch_table,
897: -- l_invoice_amt,
898: -- l_open_amt;
899: --EXIT WHEN inv_rec%NOTFOUND;
900: --l_inv_rank(i) := i;
901: --fun_net_util.Log_String(g_state_level,l_path,'Invoice ID:'||l_invoice_id);
902:
903: INSERT INTO fun_net_ap_invs_all
904: (batch_id,
905: invoice_id,
932: --END LOOP;
933: CLOSE inv_rec;
934:
935: ELSIF p_appln = 'AP' AND g_net_currency_rule IN ('ACCOUNTING_CURRENCY') THEN
936: fun_net_util.Log_String(g_state_level,l_path,'Fetching the AP Invoices');
937: OPEN inv_rec FOR p_inv_cur USING
938: p_currency_code,
939: g_batch_details.SETTLEMENT_DATE,
940: g_batch_details.exchange_rate_type,
968: -- l_open_amt,
969: -- l_inv_curr_open_amt;
970: --EXIT WHEN inv_rec%NOTFOUND;
971: --l_inv_rank := l_inv_rank + 1;
972: --fun_net_util.Log_String(g_state_level,l_path,'Invoice ID:'||l_invoice_id);
973:
974: INSERT INTO fun_net_ap_invs_all
975: (batch_id,
976: invoice_id,
1004: CLOSE inv_rec;
1005:
1006:
1007: ELSIF p_appln = 'AR' AND g_net_currency_rule IN ('SINGLE_CURRENCY', 'WITHIN_CURRENCY') THEN
1008: fun_net_util.Log_String(g_state_level,l_path,'Fetching the AR Transactions');
1009: OPEN inv_rec FOR p_inv_cur USING g_batch_details.transaction_due_date,
1010: g_agr_start_date,
1011: g_agr_end_date,
1012: g_agreement_id,
1030: -- l_transaction_amt,
1031: -- l_open_amt;
1032: --EXIT WHEN inv_rec%NOTFOUND;
1033: --l_inv_rank := l_inv_rank + 1;
1034: --fun_net_util.Log_String(g_state_level,l_path,'Transaction ID:'||l_customer_trx_id);
1035:
1036: INSERT INTO fun_net_ar_txns_all
1037: (batch_id,
1038: customer_trx_id,
1064: --END LOOP;
1065: CLOSE inv_rec;
1066:
1067: ELSIF p_appln = 'AR' AND g_net_currency_rule IN ('ACCOUNTING_CURRENCY') THEN
1068: fun_net_util.Log_String(g_state_level,l_path,'Fetching the AR Transactions');
1069: OPEN inv_rec FOR p_inv_cur USING
1070: p_currency_code,
1071: g_batch_details.SETTLEMENT_DATE,
1072: g_batch_details.exchange_rate_type,
1094: -- l_open_amt,
1095: -- l_inv_curr_open_amt;
1096: -- EXIT WHEN inv_rec%NOTFOUND;
1097: -- l_inv_rank := l_inv_rank + 1;
1098: -- fun_net_util.Log_String(g_state_level,l_path,'Transaction ID:'||l_customer_trx_id);
1099:
1100: INSERT INTO fun_net_ar_txns_all
1101: (batch_id,
1102: customer_trx_id,
1131: END IF;
1132:
1133: EXCEPTION
1134: WHEN OTHERS THEN
1135: fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1136: NULL;
1137:
1138: END insert_transactions;
1139:
1150:
1151: validate_AP_AR_balances(l_ar_bal, l_ap_bal, l_status_flag);
1152: IF l_status_flag = FND_API.G_TRUE THEN
1153: IF l_ap_bal >= l_ar_bal THEN
1154: fun_net_util.Log_String(g_state_level,l_path,'AP Balance > AR Balance');
1155: l_amt_to_net := l_ar_bal;
1156: /* As the Ar Bal = Total Net amount , update the net amount for each AR tnx with the open balance of that tnx */
1157: UPDATE fun_net_ar_txns_all
1158: SET netted_amt = open_amt,
1162: /*Order the transactions by rank as the tnxs with a higher rank should be netted first */
1163: l_sql_stmt := 'SELECT open_amt,invoice_id,0,inv_curr_open_amt,0 FROM fun_net_ap_invs_all WHERE batch_id = :v_batch_id ORDER BY ap_txn_rank';
1164: update_net_balances(l_sql_stmt,l_amt_to_net,'AP');
1165: ELSIF l_ar_bal > l_ap_bal THEN
1166: fun_net_util.Log_String(g_state_level,l_path,'AR Balance > AP Balance');
1167: l_amt_to_net := l_ap_bal;
1168: /* As the AP Bal = Total Net amount , update the net amount for each AP tnx with the open balance of that tnx */
1169: UPDATE fun_net_ap_invs_all
1170: SET netted_amt = open_amt,
1174: l_sql_stmt := 'SELECT open_amt,customer_trx_id,0, txn_curr_open_amt,0 FROM fun_net_ar_txns_all WHERE batch_id = :v_batch_id ORDER BY ar_txn_rank';
1175: update_net_balances(l_sql_stmt,l_amt_to_net,'AR');
1176: END IF;
1177:
1178: fun_net_util.Log_String(g_state_level,l_path,'Total Netted Amount :'||l_amt_to_net);
1179: /*UPDATE fun_net_batches_all
1180: SET total_netted_amt = l_amt_to_net
1181: WHERE batch_id = g_batch_id; */
1182: p_amt_to_net := l_amt_to_net;
1183: p_status_flag := FND_API.G_TRUE;
1184: ELSE
1185: /*Unlock AP and AR Transactions that have been locked */
1186:
1187: fun_net_util.Log_String(g_state_level,l_path,'validation of ap and ar balances failed.Some transactions in AP and AR might have to be unlocked manually');
1188: p_status_flag := FND_API.G_FALSE;
1189: END IF;
1190:
1191: EXCEPTION
1189: END IF;
1190:
1191: EXCEPTION
1192: WHEN NO_DATA_FOUND then
1193: fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1194: RETURN;
1195: END calculate_AP_AR_balances;
1196:
1197: PROCEDURE validate_AP_AR_balances(p_ar_bal OUT NOCOPY NUMBER,
1221: OPEN c_get_ap_open_amount;
1222: FETCH c_get_ap_open_amount INTO p_ap_bal;
1223: CLOSE c_get_ap_open_amount;
1224:
1225: fun_net_util.Log_String(g_state_level,l_path,'AP open amount :'||p_ap_bal);
1226: fun_net_util.Log_String(g_state_level,l_path,'AR open amount :'||p_ar_bal);
1227: IF nvl(p_ap_bal,0) = 0 OR nvl(p_ar_bal,0) = 0 THEN
1228:
1229: /* Error out the Batch to say netting cannot continue and set the Batch to Error and put message in the log*/
1222: FETCH c_get_ap_open_amount INTO p_ap_bal;
1223: CLOSE c_get_ap_open_amount;
1224:
1225: fun_net_util.Log_String(g_state_level,l_path,'AP open amount :'||p_ap_bal);
1226: fun_net_util.Log_String(g_state_level,l_path,'AR open amount :'||p_ar_bal);
1227: IF nvl(p_ap_bal,0) = 0 OR nvl(p_ar_bal,0) = 0 THEN
1228:
1229: /* Error out the Batch to say netting cannot continue and set the Batch to Error and put message in the log*/
1230: --ERROR MESSAGE : 'Netting cannot be performed as the Outstanding balance is zero'
1238: END IF; */
1239:
1240: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BALANCES');
1241:
1242: fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION : AP or AR balance is zero');
1243: RETURN;
1244:
1245: END IF;
1246: fun_net_util.Log_String(g_state_level,l_path,'AP and AR balances validated');
1242: fun_net_util.Log_String(g_state_level,l_path,'EXCEPTION : AP or AR balance is zero');
1243: RETURN;
1244:
1245: END IF;
1246: fun_net_util.Log_String(g_state_level,l_path,'AP and AR balances validated');
1247: EXCEPTION
1248: WHEN OTHERS THEN
1249: fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1250: END validate_AP_AR_balances;
1245: END IF;
1246: fun_net_util.Log_String(g_state_level,l_path,'AP and AR balances validated');
1247: EXCEPTION
1248: WHEN OTHERS THEN
1249: fun_net_util.Log_String(g_proc_level,l_path,'EXCEPTION : '||sqlerrm);
1250: END validate_AP_AR_balances;
1251:
1252:
1253: PROCEDURE insert_batch_record(p_currency_code VARCHAR2) IS
1349: g_batch_details.attribute20);
1350: EXCEPTION
1351: WHEN OTHERS THEN
1352:
1353: fun_net_util.Log_String(g_state_level,l_path,'Insertion of batch_record failed.');
1354: NULL;
1355: END insert_batch_record;
1356:
1357: FUNCTION batch_exists(p_currency_code VARCHAR2) RETURN BOOLEAN IS
1359: BEGIN
1360: l_path := g_path||'batch_exists';
1361: /* Check for mandatory parameters */
1362: IF p_currency_code IS NULL THEN
1363: fun_net_util.Log_String(g_state_level,l_path,'Currency code is NULL');
1364: RETURN FALSE;
1365: END IF;
1366:
1367: /* Check if the Batch already exists for the given currency and agreement.
1370:
1371: FOR i IN 1..g_idx LOOP
1372: IF g_batch_list(i).agreement_id = g_agreement_id AND
1373: g_batch_list(i).currency = p_currency_code THEN
1374: fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
1375: fun_net_util.Log_String(g_state_level,l_path,'Currency: '||g_batch_list(i).currency);
1376: g_batch_id := g_batch_list(i).batch_id;
1377: fun_net_util.Log_String(g_state_level,l_path,'Batch ID: '||g_batch_list(i).batch_id);
1378: RETURN TRUE;
1371: FOR i IN 1..g_idx LOOP
1372: IF g_batch_list(i).agreement_id = g_agreement_id AND
1373: g_batch_list(i).currency = p_currency_code THEN
1374: fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
1375: fun_net_util.Log_String(g_state_level,l_path,'Currency: '||g_batch_list(i).currency);
1376: g_batch_id := g_batch_list(i).batch_id;
1377: fun_net_util.Log_String(g_state_level,l_path,'Batch ID: '||g_batch_list(i).batch_id);
1378: RETURN TRUE;
1379: ELSE
1373: g_batch_list(i).currency = p_currency_code THEN
1374: fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
1375: fun_net_util.Log_String(g_state_level,l_path,'Currency: '||g_batch_list(i).currency);
1376: g_batch_id := g_batch_list(i).batch_id;
1377: fun_net_util.Log_String(g_state_level,l_path,'Batch ID: '||g_batch_list(i).batch_id);
1378: RETURN TRUE;
1379: ELSE
1380: fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
1381: fun_net_util.Log_String(g_state_level,l_path,'Currency: '||g_batch_list(i).currency);
1376: g_batch_id := g_batch_list(i).batch_id;
1377: fun_net_util.Log_String(g_state_level,l_path,'Batch ID: '||g_batch_list(i).batch_id);
1378: RETURN TRUE;
1379: ELSE
1380: fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
1381: fun_net_util.Log_String(g_state_level,l_path,'Currency: '||g_batch_list(i).currency);
1382: END IF;
1383: END LOOP;
1384:
1377: fun_net_util.Log_String(g_state_level,l_path,'Batch ID: '||g_batch_list(i).batch_id);
1378: RETURN TRUE;
1379: ELSE
1380: fun_net_util.Log_String(g_state_level,l_path,'Agreement_id: '||g_batch_list(i).agreement_id);
1381: fun_net_util.Log_String(g_state_level,l_path,'Currency: '||g_batch_list(i).currency);
1382: END IF;
1383: END LOOP;
1384:
1385: RETURN FALSE;
1420: INTO l_conv_rate
1421: FROM GL_DAILY_CONVERSION_TYPES
1422: WHERE CONVERSION_TYPE = g_batch_details.exchange_rate_type;
1423:
1424: fun_net_util.Log_String(g_state_level,l_path,'l_conv_rate:'||l_conv_rate);
1425:
1426: fun_net_util.Log_String(g_state_level,l_path,' p_status_flag :'
1427: || p_status_flag);
1428:
1422: WHERE CONVERSION_TYPE = g_batch_details.exchange_rate_type;
1423:
1424: fun_net_util.Log_String(g_state_level,l_path,'l_conv_rate:'||l_conv_rate);
1425:
1426: fun_net_util.Log_String(g_state_level,l_path,' p_status_flag :'
1427: || p_status_flag);
1428:
1429: For trx_curr_rec in trx_curr_cur
1430: LOOP
1428:
1429: For trx_curr_rec in trx_curr_cur
1430: LOOP
1431:
1432: fun_net_util.Log_String(g_state_level,l_path,'g_func_currency: '
1433: ||g_func_currency);
1434:
1435: fun_net_util.Log_String(g_state_level,l_path,
1436: 'trx_curr_REC.invoice_currency_code: '||trx_curr_REC.invoice_currency_code);
1431:
1432: fun_net_util.Log_String(g_state_level,l_path,'g_func_currency: '
1433: ||g_func_currency);
1434:
1435: fun_net_util.Log_String(g_state_level,l_path,
1436: 'trx_curr_REC.invoice_currency_code: '||trx_curr_REC.invoice_currency_code);
1437:
1438: l_exc_rate := Derive_Net_Exchg_Rate(g_func_currency,trx_curr_REC.invoice_currency_code);
1439:
1436: 'trx_curr_REC.invoice_currency_code: '||trx_curr_REC.invoice_currency_code);
1437:
1438: l_exc_rate := Derive_Net_Exchg_Rate(g_func_currency,trx_curr_REC.invoice_currency_code);
1439:
1440: fun_net_util.Log_String(g_state_level,l_path,'l_exc_rate :'||l_exc_rate);
1441: fun_net_util.Log_String(g_state_level,l_path,'l_count :'||l_count);
1442:
1443: IF l_exc_rate IS NULL and l_count = 0 THEN
1444:
1437:
1438: l_exc_rate := Derive_Net_Exchg_Rate(g_func_currency,trx_curr_REC.invoice_currency_code);
1439:
1440: fun_net_util.Log_String(g_state_level,l_path,'l_exc_rate :'||l_exc_rate);
1441: fun_net_util.Log_String(g_state_level,l_path,'l_count :'||l_count);
1442:
1443: IF l_exc_rate IS NULL and l_count = 0 THEN
1444:
1445: fun_net_util.Log_String(g_state_level,l_path,'Inside if ');
1441: fun_net_util.Log_String(g_state_level,l_path,'l_count :'||l_count);
1442:
1443: IF l_exc_rate IS NULL and l_count = 0 THEN
1444:
1445: fun_net_util.Log_String(g_state_level,l_path,'Inside if ');
1446:
1447: FND_MESSAGE.SET_NAME('FUN','FUN_NET_EX_RATE_NOT_DEFINED');
1448: FND_MESSAGE.SET_TOKEN('RATE_TYPE', l_conv_rate);
1449: l_msg_data := FND_MESSAGE.get;
1454: trx_curr_REC.invoice_currency_code || ' -> ' || g_func_currency );
1455: l_count := 2;
1456:
1457: ELSIF l_exc_rate IS NULL AND l_count = 2 THEN
1458: fun_net_util.Log_String(g_state_level,l_path,'Inside esle if');
1459:
1460:
1461: fnd_file.put_line(fnd_file.log, ' '||
1462: trx_curr_REC.invoice_currency_code || ' -> ' || g_func_currency );
1468: p_status_flag := FND_API.G_FALSE;
1469: END IF;
1470:
1471: END LOOP;
1472: fun_net_util.Log_String(g_state_level,l_path,' p_status_flag :'
1473: || p_status_flag);
1474:
1475: END validate_exch_rate;
1476:
1500: l_path := g_path || 'Update_Net_Balances';
1501:
1502: /* Check for mandatory parameters */
1503: l_amt_to_net := p_amt_to_net;
1504: fun_net_util.Log_String(g_state_level,l_path,'Fetching the transactions');
1505: OPEN tnx_rec FOR p_sql_stmt USING g_batch_id;
1506: FETCH tnx_rec BULK COLLECT INTO l_open_amt ,l_trx_id, l_net_amt, l_inv_curr_open_amt, l_inv_curr_net_amt;
1507:
1508: /* Scan the table for every tnx selected and compute the net amount .
1533: AND api.invoice_currency_code = fc.currency_code;
1534:
1535: l_exc_rate := Derive_Net_Exchg_Rate(g_func_currency,l_inv_currency);
1536:
1537: fun_net_util.Log_String(g_state_level,l_path,'l_exc_rate:'||l_exc_rate);
1538:
1539: fun_net_util.Log_String(g_state_level,l_path,'l_amt_to_net:'||l_amt_to_net);
1540:
1541:
1535: l_exc_rate := Derive_Net_Exchg_Rate(g_func_currency,l_inv_currency);
1536:
1537: fun_net_util.Log_String(g_state_level,l_path,'l_exc_rate:'||l_exc_rate);
1538:
1539: fun_net_util.Log_String(g_state_level,l_path,'l_amt_to_net:'||l_amt_to_net);
1540:
1541:
1542: l_inv_curr_net_amt(i) := l_amt_to_net * Derive_Net_Exchg_Rate(g_func_currency,l_inv_currency);
1543: l_inv_curr_net_amt(i) := ROUND(l_inv_curr_net_amt(i),l_precision);
1554: ELSE
1555: l_inv_curr_net_amt(i) := l_net_amt(i);
1556: END IF;
1557: END IF;
1558: fun_net_util.Log_String(g_state_level,l_path,'trx_id :'||l_trx_id(i));
1559: fun_net_util.Log_String(g_state_level,l_path,'Netted Amount :'||l_net_amt(i));
1560: fun_net_util.Log_String(g_state_level,l_path,'Invoice currency Netted Amount :'||l_inv_curr_net_amt(i));
1561: l_amt_to_net := l_amt_to_net - l_open_amt(i);
1562:
1555: l_inv_curr_net_amt(i) := l_net_amt(i);
1556: END IF;
1557: END IF;
1558: fun_net_util.Log_String(g_state_level,l_path,'trx_id :'||l_trx_id(i));
1559: fun_net_util.Log_String(g_state_level,l_path,'Netted Amount :'||l_net_amt(i));
1560: fun_net_util.Log_String(g_state_level,l_path,'Invoice currency Netted Amount :'||l_inv_curr_net_amt(i));
1561: l_amt_to_net := l_amt_to_net - l_open_amt(i);
1562:
1563: IF l_amt_to_net <= 0 THEN
1556: END IF;
1557: END IF;
1558: fun_net_util.Log_String(g_state_level,l_path,'trx_id :'||l_trx_id(i));
1559: fun_net_util.Log_String(g_state_level,l_path,'Netted Amount :'||l_net_amt(i));
1560: fun_net_util.Log_String(g_state_level,l_path,'Invoice currency Netted Amount :'||l_inv_curr_net_amt(i));
1561: l_amt_to_net := l_amt_to_net - l_open_amt(i);
1562:
1563: IF l_amt_to_net <= 0 THEN
1564: EXIT;
1582: AND customer_trx_id = l_trx_id(i);
1583: END IF;
1584: EXCEPTION
1585: WHEN OTHERS THEN
1586: fun_net_util.Log_String(g_state_level,l_path,sqlerrm);
1587: END update_net_balances;
1588:
1589: PROCEDURE Update_Net_Amounts(p_batch_id NUMBER, p_amt_to_net NUMBER, p_appln VARCHAR2) IS
1590: BEGIN
1651:
1652: BEGIN
1653:
1654: l_path := g_path||'Create_Net_Batch';
1655: fun_net_util.Log_String(g_event_level,l_path,'Creating Netting batches');
1656:
1657: x_msg_count := NULL;
1658: x_msg_data := NULL;
1659: g_user_id := fnd_global.user_id;
1682:
1683: g_batch_id := p_batch_id;
1684:
1685: --Call the procedure to get the batch details
1686: fun_net_util.Log_String(g_state_level,l_path,'Fetching batch details');
1687: IF NOT get_batch_details THEN
1688: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
1689: RAISE FND_API.G_EXC_ERROR;
1690: END IF;
1684:
1685: --Call the procedure to get the batch details
1686: fun_net_util.Log_String(g_state_level,l_path,'Fetching batch details');
1687: IF NOT get_batch_details THEN
1688: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
1689: RAISE FND_API.G_EXC_ERROR;
1690: END IF;
1691:
1692: --If the call is successful then call the procedure to update batch status
1690: END IF;
1691:
1692: --If the call is successful then call the procedure to update batch status
1693: IF NOT update_batch_status('RUNNING') THEN
1694: fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status');
1695: RAISE FND_API.G_EXC_ERROR;
1696: END IF; -- Return Code
1697:
1698: --If the call is successful then call the procedure to get all agreement details. If agreement id is null , then loop through each agreement and select tnx for every agreement.
1696: END IF; -- Return Code
1697:
1698: --If the call is successful then call the procedure to get all agreement details. If agreement id is null , then loop through each agreement and select tnx for every agreement.
1699: -- If the call is successful then get the agreement details and select transactions for every agreement
1700: fun_net_util.Log_String(g_state_level,l_path,'Before processing the agreements');
1701: get_functional_currency;
1702: g_agreement_id := g_batch_details.agreement_id;
1703: g_agreement_count := 0;
1704: g_currency_count := 0;
1705:
1706: FOR agr_rec IN c_agreement_cur
1707: LOOP
1708: /* Reset the global varaibles for every agreement */
1709: fun_net_util.Log_String(g_state_level,l_path,'Agreement_id:'||agr_rec.agreement_id);
1710: g_agreement_id := agr_rec.agreement_id;
1711: g_net_currency_rule := agr_rec.net_currency_rule_code;
1712: g_net_order_rule := agr_rec.net_order_rule_code;
1713: g_net_balance_rule := agr_rec.net_balance_rule_code;
1739:
1740: --g_currency_count := 0;
1741: --If the call is successful then call the procedure to select all customer transactions --and insert them into the customer transactions table
1742: IF NOT prepare_ar_transactions THEN
1743: fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AR Transactions');
1744: RAISE FND_API.G_EXC_ERROR;
1745: END IF;
1746:
1747: --If the call is successful then call the procedure to select all supplier transactions and insert them into the supplier invoices table.
1745: END IF;
1746:
1747: --If the call is successful then call the procedure to select all supplier transactions and insert them into the supplier invoices table.
1748: IF NOT prepare_ap_transactions THEN
1749: fun_net_util.Log_String(g_state_level,l_path,'Error in prepare AP transactions');
1750: RAISE FND_API.G_EXC_ERROR;
1751: END IF;
1752:
1753: END LOOP;
1751: END IF;
1752:
1753: END LOOP;
1754:
1755: fun_net_util.Log_String(g_state_level,l_path,'Processing the batches');
1756: FOR i IN 1..g_idx
1757: LOOP
1758: g_batch_id := g_batch_list(i).batch_id;
1759: IF NOT get_batch_details THEN
1756: FOR i IN 1..g_idx
1757: LOOP
1758: g_batch_id := g_batch_list(i).batch_id;
1759: IF NOT get_batch_details THEN
1760: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching batch details');
1761: RAISE FND_API.G_EXC_ERROR;
1762: END IF;
1763:
1764: g_agreement_id := g_batch_list(i).agreement_id;
1762: END IF;
1763:
1764: g_agreement_id := g_batch_list(i).agreement_id;
1765: IF NOT get_agreement_details THEN
1766: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching agreement details');
1767: RAISE FND_API.G_EXC_ERROR;
1768: END IF;
1769:
1770: fun_net_util.Log_String(g_state_level,l_path,'Batch ID:'||g_batch_id);
1766: fun_net_util.Log_String(g_state_level,l_path,'Error in Fetching agreement details');
1767: RAISE FND_API.G_EXC_ERROR;
1768: END IF;
1769:
1770: fun_net_util.Log_String(g_state_level,l_path,'Batch ID:'||g_batch_id);
1771:
1772: -- verify currency rates
1773:
1774: validate_exch_rate(l_return_status);
1775:
1776:
1777: IF l_return_status = FND_API.G_FALSE THEN
1778:
1779: fun_net_util.Log_String(g_event_level,l_path, 'validate_exch_rate returns false');
1780: RAISE FND_API.G_EXC_ERROR;
1781:
1782: END IF;
1783:
1803: g_batch_details.batch_status_code := 'ERROR';
1804:
1805: END IF;
1806: /* IF NOT update_batch_status('SELECTED') THEN
1807: fun_net_util.Log_String(g_state_level,l_path,'Error in updating batch status to SELECTED');
1808: NULL;
1809: END IF; */
1810:
1811: /* Check if Auto submission is on If not call the Submit API process*/
1811: /* Check if Auto submission is on If not call the Submit API process*/
1812: /* VDOBREV: Bug 5003118 IF g_batch_details.review_netting_batch_flag = 'N' */
1813: IF g_batch_details.review_netting_batch_flag = 'Y'
1814: AND g_batch_details.batch_status_code = 'SELECTED' THEN
1815: fun_net_util.Log_String(g_state_level,l_path,'Submitting Netting Batch');
1816: submit_net_batch (
1817: p_init_msg_list => FND_API.G_FALSE,
1818: p_commit => FND_API.G_FALSE,
1819: x_return_status => l_return_status,
1823: END IF;
1824: END LOOP;
1825:
1826: -- Standard check of p_commit.
1827: fun_net_util.Log_String(g_state_level,l_path,'p_commit :'|| p_commit);
1828: IF FND_API.To_Boolean( p_commit ) THEN
1829: fun_net_util.Log_String(g_state_level,l_path,'Saving the batches');
1830: COMMIT WORK;
1831: END IF;
1825:
1826: -- Standard check of p_commit.
1827: fun_net_util.Log_String(g_state_level,l_path,'p_commit :'|| p_commit);
1828: IF FND_API.To_Boolean( p_commit ) THEN
1829: fun_net_util.Log_String(g_state_level,l_path,'Saving the batches');
1830: COMMIT WORK;
1831: END IF;
1832:
1833: EXCEPTION
1832:
1833: EXCEPTION
1834: WHEN FND_API.G_EXC_ERROR THEN
1835:
1836: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1837: ROLLBACK TO create_net_batch_SP;
1838: batch_status_flag := update_batch_status('ERROR');
1839: x_return_status := FND_API.G_RET_STS_ERROR;
1840: FND_MSG_PUB.Count_And_Get (
1842: p_data => x_msg_data );
1843:
1844: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1845:
1846: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1847: ROLLBACK TO create_net_batch_SP;
1848: batch_status_flag := update_batch_status('ERROR');
1849: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1850: FND_MSG_PUB.Count_And_Get (
1852: p_data => x_msg_data );
1853:
1854: WHEN OTHERS THEN
1855:
1856: fun_net_util.Log_String(g_state_level,l_path,'netting batch creation failed');
1857: ROLLBACK TO Create_Net_Batch_SP;
1858: batch_status_flag := update_batch_status('ERROR');
1859: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1860: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1964: WHERE agreement_id = g_agreement_id;
1965: l_path VARCHAR2(100);
1966: BEGIN
1967: l_path := g_path||'submit_net_batch';
1968: fun_net_util.Log_String(g_state_level,l_path,'Start submit batch');
1969: /* Intialize standard API parameters */
1970: /* Intialize message list */
1971: x_msg_count := NULL;
1972: x_msg_data := NULL;
1985: /* Validate Mandatory parameters */
1986: /* Get Batch Details for the given batch id*/
1987: IF p_batch_id IS NULL THEN
1988:
1989: fun_net_util.Log_String(g_state_level,l_path,'Batch id passed to submit netting batch procedure is null');
1990: RAISE FND_API.G_EXC_ERROR;
1991: END IF;
1992:
1993: g_batch_id := p_batch_id;
1992:
1993: g_batch_id := p_batch_id;
1994: IF NOT get_batch_details THEN
1995:
1996: fun_net_util.Log_String(g_state_level,l_path,'Unable to get batch details for the netting batch with batch id :'||g_batch_id);
1997: RAISE FND_API.G_EXC_ERROR;
1998: END IF;
1999:
2000: fun_net_util.Log_String(g_state_level,l_path,'Validating batch');
1996: fun_net_util.Log_String(g_state_level,l_path,'Unable to get batch details for the netting batch with batch id :'||g_batch_id);
1997: RAISE FND_API.G_EXC_ERROR;
1998: END IF;
1999:
2000: fun_net_util.Log_String(g_state_level,l_path,'Validating batch');
2001: /* Compares Final AP and AR Balances. Need to check the balance as the batch might have been modified by the user */
2002: g_agreement_id := g_batch_details.agreement_id;
2003:
2004: IF NOT get_agreement_details THEN
2002: g_agreement_id := g_batch_details.agreement_id;
2003:
2004: IF NOT get_agreement_details THEN
2005:
2006: fun_net_util.Log_String(g_event_level,l_path,
2007: 'Error getting Agreement details');
2008: RAISE FND_API.G_EXC_ERROR;
2009: END IF;
2010:
2031:
2032: IF l_status_flag = FND_API.G_TRUE then
2033: /*Check for Trading Partner Approval and set status to Submitted if TP approval is required*/
2034: IF g_batch_details.settlement_date < TRUNC(SYSDATE) THEN
2035: fun_net_util.Log_String(g_state_level,l_path,'Setting status to SUSPENDED');
2036: UPDATE fun_net_batches
2037: SET batch_status_code = 'SUSPENDED'
2038: WHERE batch_id = g_batch_id;
2039: g_batch_details.batch_status_code := 'SUSPENDED';
2043: CLOSE c_TP_APPROVER;
2044:
2045: IF l_TP_approver IS NOT NULL THEN
2046: IF g_batch_details.batch_status_code IN ( 'SELECTED','REJECTED','ERROR' ) THEN
2047: fun_net_util.Log_String(g_state_level,l_path,'Setting status to SUBMITTED');
2048: UPDATE fun_net_batches
2049: SET batch_status_code = 'SUBMITTED'
2050: WHERE batch_id = g_batch_id;
2051: g_batch_details.batch_status_code := 'SUBMITTED';
2048: UPDATE fun_net_batches
2049: SET batch_status_code = 'SUBMITTED'
2050: WHERE batch_id = g_batch_id;
2051: g_batch_details.batch_status_code := 'SUBMITTED';
2052: fun_net_util.Log_String(g_state_level,l_path,'Raising WF business event');
2053: fun_net_approval_wf.raise_approval_event(g_batch_id);
2054: END IF;
2055: ELSIF g_batch_details.batch_status_code IN('SELECTED','ERROR') THEN /* TP Approval is not necessary */
2056: /* If TP approval is not necessary call Netting Settlement Date API */
2069: END IF; */
2070: UPDATE fun_net_batches
2071: SET batch_status_code = 'APPROVED'
2072: WHERE batch_id = g_batch_id;
2073: fun_net_util.Log_String(g_state_level,l_path,'Setting status to APPROVED');
2074: g_batch_details.batch_status_code := 'APPROVED';
2075:
2076: END IF;
2077: END IF;
2082: IF g_batch_details.batch_status_code = 'APPROVED' then
2083: UPDATE fun_net_batches
2084: SET batch_status_code = 'CLEARING'
2085: WHERE batch_id = g_batch_id;
2086: fun_net_util.Log_String(g_state_level,l_path,'Setting status to CLEARING');
2087: --settle_net_batch(g_batch_id);
2088: COMMIT;
2089: l_Request_id := FND_REQUEST.SUBMIT_REQUEST ( 'FUN'
2090: , 'FUNNTSTB'
2093: , FALSE -- Is a sub request
2094: , g_batch_id
2095: );
2096:
2097: fun_net_util.Log_String(g_state_level,l_path,'Settle batch request:'||l_request_id);
2098: END IF;
2099:
2100: -- Standard check of p_commit.
2101: IF FND_API.To_Boolean( p_commit ) THEN
2174: /* Validate Mandatory parameters */
2175: /* Get Batch Details for the given batch id*/
2176: IF p_batch_id IS NULL OR (p_net_settle_date IS NULL AND p_response_date IS NULL) THEN
2177:
2178: fun_net_util.Log_String(g_state_level,l_path,'One of the mandatory arguments passed to the validate_netting_dates procedure is null');
2179: RAISE FND_API.G_EXC_ERROR;
2180: END IF;
2181:
2182: g_batch_id := p_batch_id;
2186: and also by the Submit Netting Batch API when TP approval is not required .*/
2187: IF p_net_settle_date IS NOT NULL THEN
2188: IF TRUNC(p_net_settle_date) < TRUNC(SYSDATE) THEN
2189:
2190: fun_net_util.Log_String(g_state_level,l_path,'The netting batch with batch_id:'||g_batch_id||'has gone past the netting settlement date');
2191: --Error ' Batch has gone past the Netting Settlement Date';
2192: IF NOT Update_batch_status('SUSPENDED') THEN
2193: RAISE FND_API.G_EXC_ERROR;
2194: ELSE
2195: RETURN 'SUSPENDED';
2196: END IF;
2197: ELSE
2198: IF NOT update_batch_status ('APPROVED') THEN
2199: fun_net_util.Log_String(g_state_level,l_path,'Unable to update the batch status to approved');
2200: RAISE FND_API.G_EXC_ERROR;
2201: ELSE
2202: RETURN 'APPROVED';
2203: END IF;
2451: RETURN;
2452: END IF;
2453: EXCEPTION
2454: WHEN OTHERS THEN
2455: fun_net_util.Log_String(g_state_level,l_path,'error while unlocking payment schedules');
2456: x_return_status := FND_API.G_FALSE;
2457: END unlock_ap_pymt_schedules;
2458:
2459: /* Derives Netting Exchange Rate . Returns 1 if the from and the to
2474: x_exchange_rate := 1.0;
2475: RETURN x_exchange_rate;
2476: END IF;
2477:
2478: fun_net_util.Log_String(g_state_level,l_path,'x_from_currency:'||x_from_currency);
2479:
2480: fun_net_util.Log_String(g_state_level,l_path,'x_to_currency:'||x_to_currency);
2481:
2482:
2476: END IF;
2477:
2478: fun_net_util.Log_String(g_state_level,l_path,'x_from_currency:'||x_from_currency);
2479:
2480: fun_net_util.Log_String(g_state_level,l_path,'x_to_currency:'||x_to_currency);
2481:
2482:
2483: fun_net_util.Log_String(g_state_level,l_path,g_batch_details.exchange_rate_type);
2484:
2479:
2480: fun_net_util.Log_String(g_state_level,l_path,'x_to_currency:'||x_to_currency);
2481:
2482:
2483: fun_net_util.Log_String(g_state_level,l_path,g_batch_details.exchange_rate_type);
2484:
2485: IF g_batch_details.exchange_rate_type = 'User' then
2486: x_exchange_rate := g_batch_details.exchange_rate;
2487: ELSIF g_batch_details.exchange_rate_type IS NOT NULL THEN
2558: END IF;
2559: EXCEPTION
2560: WHEN OTHERS THEN
2561:
2562: fun_net_util.Log_String(g_state_level,l_path,'The GL period for the settlement of netting batch is closed or not yet opened ');
2563: x_return_status := FND_API.G_FALSE;
2564: END Validate_Settlement_Period;
2565: /* Creates AP Check per vendor / vendor site / currency . Also creates
2566: invoice payments for each AP Check */
2612: x_return_status := FND_API.G_TRUE;
2613:
2614: BEGIN
2615: l_path := g_path || 'Settle AP Transactions';
2616: fun_net_util.Log_String(g_event_level,l_path,'Group AP Invoices');
2617: SELECT
2618: sum(finv.inv_curr_net_amt) AS pymt_amt,
2619: -- sum(finv.netted_amt) AS base_pymt_amt,
2620: inv.vendor_id,
2642:
2643: EXCEPTION
2644: WHEN OTHERS THEN
2645:
2646: fun_net_util.Log_String(g_event_level,l_path,sqlcode || sqlerrm);
2647: RAISE FND_API.G_EXC_ERROR;
2648: END;
2649:
2650:
2648: END;
2649:
2650:
2651:
2652: fun_net_util.Log_String(g_event_level,l_path,'Processing Txns by Vendor'||
2653: vendor_list.count);
2654:
2655: FOR i IN 1..vendor_list.count
2656: LOOP
2655: FOR i IN 1..vendor_list.count
2656: LOOP
2657:
2658:
2659: fun_net_util.Log_String(g_event_level,l_path,'vendor_id'|| vendor_list(i));
2660:
2661: /* Get Vendor Name and Vendor Site Name */
2662:
2663: ap_check_rec.VENDOR_NAME := PO_VENDORS_SV.get_vendor_name_func(
2662:
2663: ap_check_rec.VENDOR_NAME := PO_VENDORS_SV.get_vendor_name_func(
2664: vendor_list(i));
2665:
2666: fun_net_util.Log_String(g_event_level,l_path,'vendor_site_id'||vendor_site_list(i));
2667:
2668:
2669:
2670: PO_VENDOR_SITES_SV.get_vendor_site_name(
2700:
2701: ap_check_rec.currency_code := currency_list(i);
2702: -- ap_check_rec.amount := (ap_check_amt_list(i)/ap_check_rec.exchange_rate);
2703:
2704: fun_net_util.Log_String(g_event_level,l_path,'ap_check_amt_list(i) :'|| ap_check_amt_list(i));
2705:
2706:
2707: ap_check_rec.amount := ap_check_amt_list(i);
2708: -- ap_check_rec.BASE_AMOUNT := ap_check_amt_list(i) ;
2733: /* Selects invoices per vendor. Amt to be paid reflects the total amount to to be paid per invoice. Calculate the amt to be paid per installment */
2734:
2735:
2736:
2737: fun_net_util.Log_String(g_event_level,
2738: l_path,
2739: ' checkrun_id :'||g_batch_details.checkrun_id ||
2740: ' batch_id :'||g_batch_details.batch_id ||
2741: ' currency :' || currency_list(i) );
2786: AND inv.invoice_currency_code = currency_list(i)
2787: ORDER BY inv.invoice_id,apps.payment_num;
2788:
2789: l_old_invoice := 0;
2790: fun_net_util.Log_String(g_event_level,
2791: l_path
2792: ,'Calculating Amt to be paid by Schedule');
2793:
2794:
2792: ,'Calculating Amt to be paid by Schedule');
2793:
2794:
2795:
2796: fun_net_util.Log_String(g_event_level,
2797: l_path
2798: ,'ap_payment_info_tab.count:'||
2799: ap_payment_info_tab.count);
2800:
2799: ap_payment_info_tab.count);
2800:
2801: FOR j in 1..ap_payment_info_tab.count
2802: LOOP
2803: fun_net_util.Log_String(g_event_level,
2804: l_path,
2805: 'j' || j);
2806: fun_net_util.Log_String(
2807: g_event_level,
2802: LOOP
2803: fun_net_util.Log_String(g_event_level,
2804: l_path,
2805: 'j' || j);
2806: fun_net_util.Log_String(
2807: g_event_level,
2808: l_path,
2809: 'invoice_id' || ap_payment_info_tab(j).invoice_id);
2810: fun_net_util.Log_String(
2806: fun_net_util.Log_String(
2807: g_event_level,
2808: l_path,
2809: 'invoice_id' || ap_payment_info_tab(j).invoice_id);
2810: fun_net_util.Log_String(
2811: g_event_level,
2812: l_path,
2813: 'payment num' || ap_payment_info_tab(j).payment_schedule_num);
2814: fun_net_util.Log_String(g_event_level,
2810: fun_net_util.Log_String(
2811: g_event_level,
2812: l_path,
2813: 'payment num' || ap_payment_info_tab(j).payment_schedule_num);
2814: fun_net_util.Log_String(g_event_level,
2815: l_path,
2816: 'Amt to Pay' || ap_payment_info_tab(j).amount_to_pay);
2817: fun_net_util.Log_String(g_event_level,
2818: l_path,
2813: 'payment num' || ap_payment_info_tab(j).payment_schedule_num);
2814: fun_net_util.Log_String(g_event_level,
2815: l_path,
2816: 'Amt to Pay' || ap_payment_info_tab(j).amount_to_pay);
2817: fun_net_util.Log_String(g_event_level,
2818: l_path,
2819: 'Amt Remaining' ||amt_remaining);
2820:
2821: IF ap_payment_info_tab(j).invoice_id <> l_old_invoice THEN
2840: amt_remaining := 0 ;
2841: END IF;
2842: END IF; -- Negative Amounts
2843: l_old_invoice := ap_payment_info_tab(j).invoice_id;
2844: fun_net_util.Log_String(g_event_level,
2845: l_path,
2846: 'Calc Amt to Pay' || ap_payment_info_tab(j).amount_to_pay);
2847: END LOOP;
2848:
2854: P_Check_ID => l_check_id,
2855: P_Curr_Calling_Sequence => 'Netting Settlement Process',
2856: p_gl_date => l_gl_date) ; /* p_gl_date Added for bug#7639863 */
2857:
2858: fun_net_util.Log_String(g_event_level,
2859: l_path,
2860: 'Check ID :'||l_check_id );
2861:
2862:
2980:
2981: BEGIN
2982: x_return_status := FND_API.G_TRUE;
2983: l_path := g_path || 'Validate AR Transactions';
2984: fun_net_util.Log_String(g_event_level,l_path,
2985: 'Validating AR Transactions');
2986:
2987: SELECT trx.customer_trx_id AS customer_trx_id,
2988: trx.exchange_rate AS inv_exchange_rate,
3026:
3027:
3028: IF txnTable.EXISTS(1) THEN
3029:
3030: fun_net_util.Log_String(g_event_level,l_path,
3031: 'record count '||txnTable.COUNT);
3032:
3033:
3034: FOR i in txnTable.FIRST..txnTable.LAST
3033:
3034: FOR i in txnTable.FIRST..txnTable.LAST
3035: LOOP
3036:
3037: fun_net_util.Log_String(g_event_level,l_path,
3038: 'Txn CurrOpen Amt fail22 ' || txnTable(i).txn_curr_open_amt);
3039:
3040: fun_net_util.Log_String(g_event_level,l_path,
3041: 'Amt Remaining fail22 ' || txnTable(i).amt_remaining);
3036:
3037: fun_net_util.Log_String(g_event_level,l_path,
3038: 'Txn CurrOpen Amt fail22 ' || txnTable(i).txn_curr_open_amt);
3039:
3040: fun_net_util.Log_String(g_event_level,l_path,
3041: 'Amt Remaining fail22 ' || txnTable(i).amt_remaining);
3042:
3043:
3044: fun_net_util.Log_String(g_event_level,l_path,
3040: fun_net_util.Log_String(g_event_level,l_path,
3041: 'Amt Remaining fail22 ' || txnTable(i).amt_remaining);
3042:
3043:
3044: fun_net_util.Log_String(g_event_level,l_path,
3045: 'Txn Due Date' || g_batch_details.TRANSACTION_DUE_DATE);
3046: fun_net_util.Log_String(g_event_level,l_path,
3047: 'Agreement Start Date' || g_agr_start_date);
3048: fun_net_util.Log_String(g_event_level,l_path,
3042:
3043:
3044: fun_net_util.Log_String(g_event_level,l_path,
3045: 'Txn Due Date' || g_batch_details.TRANSACTION_DUE_DATE);
3046: fun_net_util.Log_String(g_event_level,l_path,
3047: 'Agreement Start Date' || g_agr_start_date);
3048: fun_net_util.Log_String(g_event_level,l_path,
3049: 'Agreement End Date' || g_agr_end_date);
3050: fun_net_util.Log_String(g_event_level,l_path,
3044: fun_net_util.Log_String(g_event_level,l_path,
3045: 'Txn Due Date' || g_batch_details.TRANSACTION_DUE_DATE);
3046: fun_net_util.Log_String(g_event_level,l_path,
3047: 'Agreement Start Date' || g_agr_start_date);
3048: fun_net_util.Log_String(g_event_level,l_path,
3049: 'Agreement End Date' || g_agr_end_date);
3050: fun_net_util.Log_String(g_event_level,l_path,
3051: 'i' || i);
3052: fun_net_util.Log_String(g_event_level,l_path,
3046: fun_net_util.Log_String(g_event_level,l_path,
3047: 'Agreement Start Date' || g_agr_start_date);
3048: fun_net_util.Log_String(g_event_level,l_path,
3049: 'Agreement End Date' || g_agr_end_date);
3050: fun_net_util.Log_String(g_event_level,l_path,
3051: 'i' || i);
3052: fun_net_util.Log_String(g_event_level,l_path,
3053: 'Customer Trx Id ' || txnTable(i).customer_trx_id);
3054:
3048: fun_net_util.Log_String(g_event_level,l_path,
3049: 'Agreement End Date' || g_agr_end_date);
3050: fun_net_util.Log_String(g_event_level,l_path,
3051: 'i' || i);
3052: fun_net_util.Log_String(g_event_level,l_path,
3053: 'Customer Trx Id ' || txnTable(i).customer_trx_id);
3054:
3055: -- Check if the Open Amount in the Netting tables are
3056: -- different to the Open Amounts in Payment Schedules
3056: -- different to the Open Amounts in Payment Schedules
3057:
3058: txnTable(i).txn_curr_amt := calculate_ar_trx_amt
3059: (txnTable(i).customer_trx_id);
3060: l_inv_amt := fun_net_util.round_currency(
3061: txnTable(i).txn_curr_amt * nvl(txnTable(i).exchange_rate,1),
3062: g_func_currency);
3063:
3064: fun_net_util.Log_String(g_event_level,l_path,
3060: l_inv_amt := fun_net_util.round_currency(
3061: txnTable(i).txn_curr_amt * nvl(txnTable(i).exchange_rate,1),
3062: g_func_currency);
3063:
3064: fun_net_util.Log_String(g_event_level,l_path,
3065: 'Txn Curr Amt' || txnTable(i).txn_curr_amt);
3066: fun_net_util.Log_String(g_event_level,l_path,
3067: 'Net Txn Amt ' || txnTable(i).txn_amt);
3068: fun_net_util.Log_String(g_event_level,l_path,
3062: g_func_currency);
3063:
3064: fun_net_util.Log_String(g_event_level,l_path,
3065: 'Txn Curr Amt' || txnTable(i).txn_curr_amt);
3066: fun_net_util.Log_String(g_event_level,l_path,
3067: 'Net Txn Amt ' || txnTable(i).txn_amt);
3068: fun_net_util.Log_String(g_event_level,l_path,
3069: 'Txn base amt' || l_inv_amt);
3070: fun_net_util.Log_String(g_event_level,l_path,
3064: fun_net_util.Log_String(g_event_level,l_path,
3065: 'Txn Curr Amt' || txnTable(i).txn_curr_amt);
3066: fun_net_util.Log_String(g_event_level,l_path,
3067: 'Net Txn Amt ' || txnTable(i).txn_amt);
3068: fun_net_util.Log_String(g_event_level,l_path,
3069: 'Txn base amt' || l_inv_amt);
3070: fun_net_util.Log_String(g_event_level,l_path,
3071: 'Txn CurrOpen Amt' || txnTable(i).txn_curr_open_amt);
3072: fun_net_util.Log_String(g_event_level,l_path,
3066: fun_net_util.Log_String(g_event_level,l_path,
3067: 'Net Txn Amt ' || txnTable(i).txn_amt);
3068: fun_net_util.Log_String(g_event_level,l_path,
3069: 'Txn base amt' || l_inv_amt);
3070: fun_net_util.Log_String(g_event_level,l_path,
3071: 'Txn CurrOpen Amt' || txnTable(i).txn_curr_open_amt);
3072: fun_net_util.Log_String(g_event_level,l_path,
3073: 'Amt Remaining' || txnTable(i).amt_remaining);
3074: fun_net_util.Log_String(g_event_level,l_path,
3068: fun_net_util.Log_String(g_event_level,l_path,
3069: 'Txn base amt' || l_inv_amt);
3070: fun_net_util.Log_String(g_event_level,l_path,
3071: 'Txn CurrOpen Amt' || txnTable(i).txn_curr_open_amt);
3072: fun_net_util.Log_String(g_event_level,l_path,
3073: 'Amt Remaining' || txnTable(i).amt_remaining);
3074: fun_net_util.Log_String(g_event_level,l_path,
3075: 'Payment Type' || txnTable(i).payment_type_code);
3076:
3070: fun_net_util.Log_String(g_event_level,l_path,
3071: 'Txn CurrOpen Amt' || txnTable(i).txn_curr_open_amt);
3072: fun_net_util.Log_String(g_event_level,l_path,
3073: 'Amt Remaining' || txnTable(i).amt_remaining);
3074: fun_net_util.Log_String(g_event_level,l_path,
3075: 'Payment Type' || txnTable(i).payment_type_code);
3076:
3077: -- Check if the Invoice Amount in the Netting table is different
3078: -- to the Invoice amount in the transaction
3093: -- Compare the open amounts in transaction currency between the fun tables and the payment schedules tables.
3094:
3095: IF (txnTable(i).txn_curr_open_amt
3096: <> txnTable(i).amt_remaining) THEN
3097: fun_net_util.Log_String(g_event_level,l_path,'fails in comparison 2');
3098:
3099: fun_net_util.Log_String(g_event_level,l_path,
3100: 'Txn CurrOpen Amt fail2 ' || txnTable(i).txn_curr_open_amt);
3101:
3095: IF (txnTable(i).txn_curr_open_amt
3096: <> txnTable(i).amt_remaining) THEN
3097: fun_net_util.Log_String(g_event_level,l_path,'fails in comparison 2');
3098:
3099: fun_net_util.Log_String(g_event_level,l_path,
3100: 'Txn CurrOpen Amt fail2 ' || txnTable(i).txn_curr_open_amt);
3101:
3102: fun_net_util.Log_String(g_event_level,l_path,
3103: 'Amt Remaining fail2 ' || txnTable(i).amt_remaining);
3098:
3099: fun_net_util.Log_String(g_event_level,l_path,
3100: 'Txn CurrOpen Amt fail2 ' || txnTable(i).txn_curr_open_amt);
3101:
3102: fun_net_util.Log_String(g_event_level,l_path,
3103: 'Amt Remaining fail2 ' || txnTable(i).amt_remaining);
3104:
3105: x_return_status := FND_API.G_FALSE;
3106: EXIT;
3132: IS
3133: l_path VARCHAR2(100);
3134: BEGIN
3135: l_path := g_path || 'Creat Cash Receipt';
3136: fun_net_util.log_string(g_event_level,l_path
3137: ,'Create Cash Receipt');
3138:
3139: -- Call AR API Create Cash Receipt per Batch --
3140:
3138:
3139: -- Call AR API Create Cash Receipt per Batch --
3140:
3141:
3142: fun_net_util.log_string(g_event_level,l_path,'receipt currency:'||pARReceiptRec.currency_code);
3143:
3144:
3145:
3146: fun_net_util.log_string(g_event_level,l_path,'exchange rate:'||pARReceiptRec.exchange_rate);
3142: fun_net_util.log_string(g_event_level,l_path,'receipt currency:'||pARReceiptRec.currency_code);
3143:
3144:
3145:
3146: fun_net_util.log_string(g_event_level,l_path,'exchange rate:'||pARReceiptRec.exchange_rate);
3147:
3148:
3149: fun_net_util.log_string(g_event_level,l_path,'amount:'||pARReceiptRec.amount);
3150:
3145:
3146: fun_net_util.log_string(g_event_level,l_path,'exchange rate:'||pARReceiptRec.exchange_rate);
3147:
3148:
3149: fun_net_util.log_string(g_event_level,l_path,'amount:'||pARReceiptRec.amount);
3150:
3151: AR_RECEIPT_API_PUB.Create_cash(
3152: -- Standard API parameters.
3153: p_api_version => 1.0,
3180: -- ** OUT NOCOPY variables
3181: p_cr_id => x_cash_receipt_id);
3182:
3183: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3184: fun_net_util.log_string(g_event_level,l_path
3185: ,'Cash receipt creation failed either because AR_RECEIPT_API_PUB. Creat_cash failed or there is no receipt class associated ');
3186:
3187: FND_MSG_PUB.Count_And_Get (
3188: p_count => x_msg_count,
3192:
3193: FOR x IN 1..x_msg_count LOOP
3194:
3195: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3196: fun_net_util.Log_String(g_unexp_level,l_path,'Error message:' ||x_msg_data);
3197:
3198: END LOOP;
3199: END IF;
3200:
3391:
3392:
3393: BEGIN
3394: l_path := g_path || 'Apply Cash Receipt';
3395: fun_net_util.Log_String(g_event_level,
3396: l_path,
3397: 'Applying Transactions');
3398:
3399: -- Bug 52380292
3441: END IF;
3442:
3443:
3444:
3445: fun_net_util.Log_String(g_state_level,l_path,'l_trans_to_func_rate:'||l_trans_to_func_rate);
3446:
3447: fun_net_util.Log_String(g_state_level,l_path,'l_func_to_trans_rate:'||l_func_to_trans_rate);
3448:
3449:
3443:
3444:
3445: fun_net_util.Log_String(g_state_level,l_path,'l_trans_to_func_rate:'||l_trans_to_func_rate);
3446:
3447: fun_net_util.Log_String(g_state_level,l_path,'l_func_to_trans_rate:'||l_func_to_trans_rate);
3448:
3449:
3450: -- get the prcesion of the trx currency
3451:
3454: INTO l_precision
3455: FROM fnd_currencies fc
3456: WHERE fc.currency_code = txnTable(i).invoice_currency_code;
3457:
3458: fun_net_util.Log_String(g_event_level,l_path
3459: ,'l_precision:' ||l_precision);
3460:
3461:
3462:
3467: l_running_amt := txnTable(i).txn_curr_net_amt;
3468: FOR apply_rec in apply_trx_cur(txnTable(i).customer_trx_id)
3469: LOOP
3470:
3471: fun_net_util.Log_String(g_event_level,l_path,'Payment Schedule id' || apply_rec.pymt_schedule_id);
3472: fun_net_util.Log_String(g_event_level,l_path,'Running Amount' || l_running_amt);
3473: fun_net_util.Log_String(g_event_level,l_path,'Curr Net Amt' || txnTable(i).txn_curr_net_amt);
3474: fun_net_util.Log_String(g_event_level,l_path,'Customer Trx id' || txnTable(i).customer_trx_id);
3475:
3468: FOR apply_rec in apply_trx_cur(txnTable(i).customer_trx_id)
3469: LOOP
3470:
3471: fun_net_util.Log_String(g_event_level,l_path,'Payment Schedule id' || apply_rec.pymt_schedule_id);
3472: fun_net_util.Log_String(g_event_level,l_path,'Running Amount' || l_running_amt);
3473: fun_net_util.Log_String(g_event_level,l_path,'Curr Net Amt' || txnTable(i).txn_curr_net_amt);
3474: fun_net_util.Log_String(g_event_level,l_path,'Customer Trx id' || txnTable(i).customer_trx_id);
3475:
3476: fun_net_util.Log_String(g_event_level,l_path,'apply_rec.amt_due_remain:'|| apply_rec.amt_due_remain);
3469: LOOP
3470:
3471: fun_net_util.Log_String(g_event_level,l_path,'Payment Schedule id' || apply_rec.pymt_schedule_id);
3472: fun_net_util.Log_String(g_event_level,l_path,'Running Amount' || l_running_amt);
3473: fun_net_util.Log_String(g_event_level,l_path,'Curr Net Amt' || txnTable(i).txn_curr_net_amt);
3474: fun_net_util.Log_String(g_event_level,l_path,'Customer Trx id' || txnTable(i).customer_trx_id);
3475:
3476: fun_net_util.Log_String(g_event_level,l_path,'apply_rec.amt_due_remain:'|| apply_rec.amt_due_remain);
3477:
3470:
3471: fun_net_util.Log_String(g_event_level,l_path,'Payment Schedule id' || apply_rec.pymt_schedule_id);
3472: fun_net_util.Log_String(g_event_level,l_path,'Running Amount' || l_running_amt);
3473: fun_net_util.Log_String(g_event_level,l_path,'Curr Net Amt' || txnTable(i).txn_curr_net_amt);
3474: fun_net_util.Log_String(g_event_level,l_path,'Customer Trx id' || txnTable(i).customer_trx_id);
3475:
3476: fun_net_util.Log_String(g_event_level,l_path,'apply_rec.amt_due_remain:'|| apply_rec.amt_due_remain);
3477:
3478:
3472: fun_net_util.Log_String(g_event_level,l_path,'Running Amount' || l_running_amt);
3473: fun_net_util.Log_String(g_event_level,l_path,'Curr Net Amt' || txnTable(i).txn_curr_net_amt);
3474: fun_net_util.Log_String(g_event_level,l_path,'Customer Trx id' || txnTable(i).customer_trx_id);
3475:
3476: fun_net_util.Log_String(g_event_level,l_path,'apply_rec.amt_due_remain:'|| apply_rec.amt_due_remain);
3477:
3478:
3479: fun_net_util.Log_String(g_event_level,l_path,'l_running_amt*l_func_to_trans_rate:'|| l_running_amt*l_func_to_trans_rate);
3480:
3475:
3476: fun_net_util.Log_String(g_event_level,l_path,'apply_rec.amt_due_remain:'|| apply_rec.amt_due_remain);
3477:
3478:
3479: fun_net_util.Log_String(g_event_level,l_path,'l_running_amt*l_func_to_trans_rate:'|| l_running_amt*l_func_to_trans_rate);
3480:
3481:
3482: -- Calculate Amount to be applied per Payment Schedule
3483: IF l_running_amt = 0 THEN
3516: l_exchange_rate := null;
3517: l_applied_from := null;
3518: END IF;
3519: END IF;
3520: fun_net_util.Log_String(g_event_level,l_path,'exchange rate:'||l_exchange_rate);
3521: fun_net_util.Log_String(g_event_level,l_path,'amount applied:'||l_applied_amt);
3522: fun_net_util.Log_String(g_event_level,l_path,'applied from'||l_applied_from);
3523:
3524: BEGIN
3517: l_applied_from := null;
3518: END IF;
3519: END IF;
3520: fun_net_util.Log_String(g_event_level,l_path,'exchange rate:'||l_exchange_rate);
3521: fun_net_util.Log_String(g_event_level,l_path,'amount applied:'||l_applied_amt);
3522: fun_net_util.Log_String(g_event_level,l_path,'applied from'||l_applied_from);
3523:
3524: BEGIN
3525: SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
3518: END IF;
3519: END IF;
3520: fun_net_util.Log_String(g_event_level,l_path,'exchange rate:'||l_exchange_rate);
3521: fun_net_util.Log_String(g_event_level,l_path,'amount applied:'||l_applied_amt);
3522: fun_net_util.Log_String(g_event_level,l_path,'applied from'||l_applied_from);
3523:
3524: BEGIN
3525: SELECT trx_line.customer_trx_line_id AS cust_trx_line_id,
3526: trx_line.line_number AS line_number,'','',
3553: AND trx_line.line_type IN ('FREIGHT')
3554: AND nvl(trx_line.amount_due_remaining,trx_line.extended_amount) <> 0 ;
3555:
3556:
3557: fun_net_util.Log_String(g_event_level,l_path
3558: ,' l_freight_amount : ' || l_freight_amount);
3559:
3560:
3561:
3566: l_running_amount1 := l_applied_amt ;
3567:
3568: FOR i in llca_def_trx_lines_tbl.FIRST..llca_def_trx_lines_tbl.LAST LOOP
3569:
3570: fun_net_util.Log_String(g_event_level,l_path
3571: ,'l_running_amount1:' ||l_running_amount1);
3572:
3573: fun_net_util.Log_String(g_event_level,l_path
3574: ,'lllca_def_trx_lines_tbl(i).amount_applied:' ||llca_def_trx_lines_tbl(i).amount_applied);
3569:
3570: fun_net_util.Log_String(g_event_level,l_path
3571: ,'l_running_amount1:' ||l_running_amount1);
3572:
3573: fun_net_util.Log_String(g_event_level,l_path
3574: ,'lllca_def_trx_lines_tbl(i).amount_applied:' ||llca_def_trx_lines_tbl(i).amount_applied);
3575:
3576: IF l_running_amount1 < llca_def_trx_lines_tbl(i).amount_applied THEN
3577:
3652: FND_MSG_PUB.Count_And_Get (
3653: p_count => l_msg_count,
3654: p_data => l_msg_data );
3655:
3656: fun_net_util.Log_String(g_event_level,l_path
3657: ,'apply cash receipt package after AR_RECEIPT_API_PUB.Apply:' ||l_msg_data);
3658:
3659: IF l_msg_count > 1 THEN
3660:
3662:
3663: FOR x IN 1..l_msg_count LOOP
3664:
3665: l_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3666: fun_net_util.Log_String(g_event_level,l_path
3667: ,'apply cash receipt package Error message AR_RECEIPT_API_PUB.Apply' ||l_msg_data||' '||' '||x);
3668:
3669: END LOOP;
3670: END IF;
3723: );
3724:
3725:
3726: IF l_return_status = FND_API.G_FALSE then
3727: fun_net_util.Log_String(g_event_level,
3728: l_path
3729: ,'No Customer Details');
3730: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_CUST_DETAILS');
3731: x_return_status := FND_API.G_FALSE;
3730: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_CUST_DETAILS');
3731: x_return_status := FND_API.G_FALSE;
3732: RETURN;
3733: ELSE
3734: fun_net_util.Log_String(g_event_level,
3735: l_path
3736: ,'Customer Details Success');
3737: END IF;
3738:
3746:
3747: EXCEPTION
3748: WHEN OTHERS THEN
3749:
3750: fun_net_util.Log_String(g_event_level,
3751: l_path
3752: ,'Receipt Method not found');
3753: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_RECEIPT_METHOD');
3754: x_return_status := FND_API.G_FALSE;
3765:
3766: EXCEPTION
3767: WHEN OTHERS THEN
3768:
3769: fun_net_util.Log_String(g_event_level,
3770: l_path
3771: ,'Agreement Bank Account not associated to Receipt Method AP/AR Netting ');
3772: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_BANK_ACCT_MISSING');
3773: x_return_status := FND_API.G_FALSE;
3815: x_return_status => x_return_status,
3816: x_msg_data => x_msg_data,
3817: x_msg_count => x_msg_count);
3818:
3819: fun_net_util.Log_String(g_event_level,
3820: l_path
3821: ,'After Create Cash Receipt');
3822:
3823: fun_net_util.Log_String(g_event_level,
3819: fun_net_util.Log_String(g_event_level,
3820: l_path
3821: ,'After Create Cash Receipt');
3822:
3823: fun_net_util.Log_String(g_event_level,
3824: l_path
3825: ,x_msg_data|| ' with message ' || x_msg_count);
3826:
3827: IF x_return_status = FND_API.G_FALSE THEN
3825: ,x_msg_data|| ' with message ' || x_msg_count);
3826:
3827: IF x_return_status = FND_API.G_FALSE THEN
3828:
3829: fun_net_util.Log_String(g_event_level,
3830: l_path
3831: ,'Cash Receipt Creation Failed');
3832:
3833: FND_MESSAGE.SET_NAME('FUN','FUN_NET_CASH_RECEIPT_FAIL');
3833: FND_MESSAGE.SET_NAME('FUN','FUN_NET_CASH_RECEIPT_FAIL');
3834: RETURN;
3835: ELSE
3836:
3837: fun_net_util.Log_String(g_event_level,
3838: l_path
3839: ,'Cash Receipt Creation Success');
3840: END IF;
3841:
3862:
3863:
3864: EXCEPTION
3865: WHEN OTHERS THEN
3866: fun_net_util.Log_String(g_event_level,
3867: l_path
3868: ,'Failed to associate AR transactions in the batch with cash receipt');
3869:
3870: x_return_status := FND_API.G_FALSE;
3872: END IF;
3873:
3874: EXCEPTION
3875: WHEN OTHERS THEN
3876: fun_net_util.Log_String(g_event_level,
3877: l_path
3878: ,'Unknown error in settle_ar_txns procedure');
3879:
3880: x_return_status := FND_API.G_FALSE;
3974: BEGIN
3975:
3976:
3977: l_path := g_path||'Settle_Net_Batch';
3978: fun_net_util.Log_String(g_event_level,l_path,'Settling Netting batches');
3979: /* Intialize standard API parameters */
3980: /* Intialize message list */
3981: x_msg_count := NULL;
3982: x_msg_data := NULL;
3994:
3995: /* Get Batch Details for the given batch id*/
3996: IF p_batch_id IS NULL THEN
3997:
3998: fun_net_util.Log_String(
3999: g_event_level
4000: ,l_path,'Batch Id is null');
4001: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4002: RAISE FND_API.G_EXC_ERROR;
4005: g_batch_id := p_batch_id;
4006:
4007: IF NOT get_batch_details THEN
4008:
4009: fun_net_util.Log_String(g_event_level,l_path,
4010: 'Error getting Batch details');
4011: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4012: RAISE FND_API.G_EXC_ERROR;
4013: END IF;
4015: g_agreement_id := g_batch_details.agreement_id;
4016:
4017: IF NOT get_agreement_details THEN
4018:
4019: fun_net_util.Log_String(g_event_level,l_path,
4020: 'Error getting Agreement details');
4021: RAISE FND_API.G_EXC_ERROR;
4022: END IF;
4023:
4023:
4024: IF g_batch_details.batch_status_code NOT IN ( 'CLEARING',
4025: 'SUBMITTED') THEN
4026:
4027: fun_net_util.Log_String(g_event_level,l_path,
4028: 'Batch Status is not CLEARING');
4029: FND_MESSAGE.SET_NAME('FUN','FUN_NET_BATCH_STATUS_ERROR');
4030: RAISE FND_API.G_EXC_ERROR;
4031: END IF;
4037: /* Default Batch Details */
4038:
4039: IF default_batch_details = FND_API.G_FALSE THEN
4040:
4041: fun_net_util.Log_String(g_event_level,l_path,
4042: 'Error defaulting Batch Details');
4043: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4044: RAISE FND_API.G_EXC_ERROR;
4045: END IF;
4065: );
4066:
4067: IF l_return_status = FND_API.G_FALSE THEN
4068:
4069: fun_net_util.Log_String(g_event_level,l_path,
4070: 'Error Validating AR Transactions');
4071: FND_MESSAGE.SET_NAME('FUN','FUN_NET_VAL_AR_TXN_FAIL');
4072: RAISE VALIDATE_AR_TXN_FAIL;
4073:
4084: x_return_msg => l_msg_data);
4085:
4086: IF l_return_status = FND_API.G_FALSE THEN
4087:
4088: fun_net_util.Log_String(g_event_level,l_path,
4089: 'Error Validating AP Period');
4090: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_PERIOD_FAIL');
4091: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4092: g_batch_details.settlement_date,FALSE);
4102: x_return_msg => l_msg_data);
4103:
4104: IF l_return_status = FND_API.G_FALSE THEN
4105:
4106: fun_net_util.Log_String(g_event_level,l_path,
4107: 'Error Validating AR Period');
4108: FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_PERIOD_FAIL');
4109: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4110: g_batch_details.settlement_date,FALSE);
4118: x_return_msg => l_msg_data);
4119:
4120: IF l_return_status = FND_API.G_FALSE THEN
4121:
4122: fun_net_util.Log_String(g_event_level,l_path,
4123: 'Error Validating GL Period');
4124: FND_MESSAGE.SET_NAME('FUN','FUN_NET_GL_PERIOD_FAIL');
4125: FND_MESSAGE.SET_TOKEN('SETTLE_DATE',
4126: g_batch_details.settlement_date,FALSE);
4137: l_return_status);
4138:
4139: IF l_return_status = FND_API.G_FALSE THEN
4140:
4141: fun_net_util.Log_String(g_event_level,l_path,
4142: 'Error in Getting Netting Bank Details');
4143: FND_MESSAGE.SET_NAME('FUN','FUN_NET_NO_BANK_DETAILS');
4144: RAISE FND_API.G_EXC_ERROR;
4145: END IF;
4157:
4158:
4159: IF l_return_status = FND_API.G_FALSE THEN
4160:
4161: fun_net_util.Log_String(g_event_level,l_path,
4162: 'Error in Settling AP Invoices');
4163: -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AP_SETTLE_FAIL');
4164: RAISE FND_API.G_EXC_ERROR;
4165: END IF;
4181: IF l_return_status = FND_API.G_FALSE THEN
4182: -- x_msg_count := l_msg_count;
4183: -- x_msg_data := l_msg_data;
4184:
4185: fun_net_util.Log_String(g_event_level,l_path,
4186: 'Error in Settling AR Transactions');
4187: -- FND_MESSAGE.SET_NAME('FUN','FUN_NET_AR_SETTLE_FAIL');
4188: RAISE FND_API.G_EXC_ERROR;
4189: END IF;
4193: -- Update Batch Status to Complete
4194:
4195: IF NOT update_batch_status('COMPLETE') THEN
4196:
4197: fun_net_util.Log_String(g_event_level,l_path,
4198: 'Error in Setting Status to Complete');
4199: FND_MESSAGE.SET_NAME('FUN','FUN_NET_UNEXPECTED_ERROR');
4200: RAISE FND_API.G_EXC_ERROR;
4201:
4210: , FALSE
4211: , g_batch_id
4212: );
4213:
4214: fun_net_util.Log_String(g_state_level,l_path,
4215: 'Data Extract request: '||l_request_id);
4216:
4217:
4218: END IF;