[Home] [Help]
39: TYPE txnRecType IS RECORD
40: (
41: customer_trx_id ra_customer_trx.customer_trx_id%TYPE,
42: exchange_rate ra_customer_trx.exchange_rate%TYPE,
43: amt_remaining ar_payment_schedules.amount_due_remaining%TYPE,
44: txn_amt fun_net_ar_txns.transaction_amt%TYPE,
45: open_amt fun_net_ar_txns.open_amt%TYPE,
46: txn_curr_open_amt fun_net_ar_txns.txn_curr_open_amt%TYPE,
47: txn_curr_amt ra_customer_trx_lines.extended_amount%TYPE,
319: fun_net_util.Log_String(g_state_level,l_path,'SELECT : '||l_trx_select_clause);
320:
321: l_trx_from_clause :=
322: ' FROM ra_customer_trx_all rct,
323: ar_payment_schedules_all arps,
324: ra_cust_trx_types_all rctt,
325: fun_net_customers_all fnc ';
326: fun_net_util.Log_String(g_state_level,l_path,'FROM : '||l_trx_from_clause);
327:
2893: p_inv_currency_code IN ra_customer_trx.invoice_currency_code%TYPE,
2894: p_exchange_rate IN ra_customer_trx.exchange_rate%TYPE)
2895: RETURN NUMBER
2896: IS
2897: l_amount ar_payment_schedules.amount_due_remaining%TYPE;
2898: BEGIN
2899:
2900: SELECT SUM(decode(p_inv_currency_code,
2901: g_batch_details.batch_currency,
2901: g_batch_details.batch_currency,
2902: amount_due_remaining,
2903: p_exchange_rate * amount_due_remaining))
2904: INTO l_amount
2905: FROM AR_PAYMENT_SCHEDULES
2906: WHERE due_date <= g_batch_details.transaction_due_date
2907: AND status = 'OP'
2908: AND customer_trx_id = p_customer_trx_id;
2909:
2942: arm.payment_type_code AS payment_type_code
2943: FROM FUN_NET_AR_TXNS ftxn,
2944: RA_CUSTOMER_TRX trx,
2945: RA_CUSTOMER_TRX_LINES trx_line,
2946: AR_PAYMENT_SCHEDULES ARPS,
2947: AR_RECEIPT_METHODS arm
2948: WHERE ftxn.customer_trx_id = trx.customer_trx_id
2949: AND trx.customer_trx_id = trx_line.customer_trx_id
2950: AND ARPS.customer_Trx_id = trx.customer_Trx_id
2997: arm.payment_type_code AS payment_type_code
2998: BULK COLLECT INTO txnTable
2999: FROM FUN_NET_AR_TXNS ftxn,
3000: RA_CUSTOMER_TRX trx,
3001: AR_PAYMENT_SCHEDULES ARPS,
3002: AR_RECEIPT_METHODS arm,
3003: RA_CUST_TRX_TYPES ctype
3004: WHERE ftxn.customer_trx_id = trx.customer_trx_id
3005: AND ARPS.customer_Trx_id = trx.customer_Trx_id
3215: IS
3216: l_applied_amt ra_customer_trx_lines.extended_amount%TYPE;
3217: l_open_amt ra_customer_trx_lines.extended_amount%TYPE;
3218: l_line_amt ra_customer_trx_lines.extended_amount%TYPE;
3219: current_amt_due AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
3220: l_applied_from ra_customer_trx_lines.extended_amount%TYPE;
3221: l_exchange_rate ra_customer_trx_lines.extended_amount%TYPE;
3222: l_return_status VARCHAR2(1);
3223:
3232: trx_line.extended_amount AS extended_amount,
3233: ps.payment_schedule_id AS pymt_schedule_id
3234: FROM fun_net_ar_txns ftxn,
3235: ra_customer_trx_lines trx_line,
3236: ar_payment_schedules ps
3237: WHERE ftxn.batch_id = g_batch_details.batch_id
3238: AND ftxn.customer_trx_id = p_cust_trx_id
3239: AND trx_line.customer_trx_id = ftxn.customer_trx_id
3240: AND trx_line.line_type = 'LINE'
3253: --are not locked. If so raise an error
3254:
3255: SELECT SUM(amount_due_remaining)
3256: INTO current_amt_due
3257: FROM ar_payment_schedules ps
3258: WHERE customer_trx_id = txnTable(i).customer_Trx_id
3259: AND due_date <= g_batch_details.TRANSACTION_DUE_DATE
3260: AND due_date between g_agr_start_date and g_agr_end_date
3261: AND ps.status = 'OP';
3346: l_applied_line_amt ra_customer_trx_lines.extended_amount%TYPE;
3347: l_tax_amount ra_customer_trx_lines.extended_amount%TYPE;
3348: l_open_amt ra_customer_trx_lines.extended_amount%TYPE;
3349: l_running_amt ra_customer_trx_lines.extended_amount%TYPE;
3350: current_amt_due AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
3351: l_applied_from ra_customer_trx_lines.extended_amount%TYPE;
3352: l_exchange_rate fun_net_batches_all.exchange_rate%TYPE;
3353: l_original_amt ra_customer_trx_lines.extended_amount%TYPE;
3354: l_trans_to_func_rate fun_net_batches_all.exchange_rate%TYPE;
3371: CURSOR apply_trx_cur(p_cust_trx_id IN NUMBER)
3372: IS
3373: SELECT ps.payment_schedule_id AS pymt_schedule_id,
3374: ps.amount_due_remaining AS amt_due_remain
3375: FROM ar_payment_schedules ps
3376: WHERE ps.customer_trx_id = p_cust_trx_id
3377: AND trunc(ps.due_date) <= trunc(g_batch_details.transaction_due_date)
3378: AND trunc(ps.due_date) between trunc(g_agr_start_date) and trunc(g_agr_end_date)
3379: AND ps.status = 'OP';
3407: --Check if Amount due remaining has changed since it was validated ,
3408: -- as txns are not locked. If so raise an error
3409: SELECT SUM(amount_due_remaining)
3410: INTO current_amt_due
3411: FROM ar_payment_schedules ps
3412: WHERE customer_trx_id = txnTable(i).customer_Trx_id
3413: AND due_date <= g_batch_details.TRANSACTION_DUE_DATE
3414: AND due_date between g_agr_start_date and g_agr_end_date
3415: AND ps.status = 'OP';
3415: AND ps.status = 'OP';
3416:
3417: SELECT sum(APS.amount_due_original)
3418: INTO l_original_amt
3419: FROM AR_PAYMENT_SCHEDULES APS
3420: WHERE APS.customer_Trx_id = txnTable(i).customer_Trx_id;
3421:
3422:
3423: l_open_amt := txnTable(i).txn_curr_open_amt;
3594:
3595: IF l_line_or_header >= 1 THEN
3596: --6179308. Call Apply_In_Detail for invoices and Debitmemo, otherwise call Apply API.
3597:
3598: Select Class into l_class from ar_payment_schedules_all
3599: where customer_trx_id = txnTable(i).customer_trx_id;
3600:
3601: IF(l_class = 'INV' OR l_class = 'DM') THEN
3602: AR_RECEIPT_API_PUB.Apply_In_Detail(