[Home] [Help]
70: --| 120.14 made for bug#6691354 |
71: --| |
72: --| Bug 6784276 Changes performed by nprashar . The date columns |
73: --| actual_closed_date, gl_date_closed of table |
74: --| ar_payment_schedules_all, are set to a default |
75: --| value TO_DATE('31/12/4712','DD/MM/RRRR'). |
76: --| |
77: --| 19-Jan-2010 Bo Li modified for VAT/Excise Number shown |
78: --| in AR transaction workbench and Bug 9303168# can |
1001: END process_batch;
1002:
1003: ------------------------------------------------ ---------------------------------------
1004: --=========================================================================================--
1005: --This procedure updates the MRC data for ra_cust_trx_line_gl_dist_all, ar_payment_schedules_all,
1006: --ar_receivable_applications_all
1007: --=========================================================================================--
1008:
1009: PROCEDURE maintain_mrc( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1026:
1027: CURSOR cur_payment_schedule_mrc(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1028: IS
1029: SELECT payment_schedule_id
1030: FROM ar_payment_schedules_all
1031: WHERE customer_trx_id = cp_customer_trx_id;
1032:
1033: --get the cust_trx_line_gl_dist_id for the REC row from ra_cust_trx_line_gl_dist_all
1034: CURSOR cur_gl_dist(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1256: p_process_message := SUBSTR(SQLERRM,1,300);
1257: END maintain_applications;
1258:
1259: --=========================================================================================--
1260: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
1261: --=========================================================================================--
1262:
1263: PROCEDURE maintain_schedules( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1264: p_payment_schedule_id IN ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL,
1260: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
1261: --=========================================================================================--
1262:
1263: PROCEDURE maintain_schedules( p_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1264: p_payment_schedule_id IN ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL,
1265: p_cm_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE DEFAULT NULL,
1266: p_invoice_customer_trx_id IN ra_customer_trx_all.customer_trx_id%TYPE,
1267: p_concurrent_req_num IN NUMBER,
1268: p_request_id IN NUMBER,
1356: ld_creation_date,
1357: ln_last_updated_by,
1358: ld_last_update_date,
1359: ln_last_update_login
1360: FROM ar_payment_schedules_all
1361: WHERE customer_trx_id = p_customer_trx_id
1362: AND payment_schedule_id = NVL(p_payment_schedule_id, payment_schedule_id);
1363:
1364: ELSIF p_operation_type = 'UPDATE' THEN
1394: actual_date_closed,
1395: ln_last_updated_by,
1396: ld_last_update_date,
1397: ln_last_update_login
1398: FROM ar_payment_schedules_all b
1399: WHERE customer_trx_id = a.original_customer_trx_id
1400: AND payment_schedule_id = a.payment_schedule_id)
1401: WHERE original_customer_trx_id = p_customer_trx_id
1402: AND payment_schedule_id = NVL(p_payment_schedule_id, payment_schedule_id)
1790: SELECT allow_overapplication_flag
1791: FROM ra_cust_trx_types_all
1792: WHERE cust_trx_type_id = cp_cust_trx_type_id;
1793:
1794: --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
1795: CURSOR cur_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1796: cp_payment_Schedule_id ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL)
1797: IS
1798: SELECT payment_schedule_id,
1792: WHERE cust_trx_type_id = cp_cust_trx_type_id;
1793:
1794: --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
1795: CURSOR cur_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
1796: cp_payment_Schedule_id ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL)
1797: IS
1798: SELECT payment_schedule_id,
1799: term_id,
1800: terms_sequence_number,
1803: tax_original,
1804: tax_remaining,
1805: freight_original,
1806: amount_due_remaining
1807: FROM ar_payment_schedules_all
1808: WHERE customer_trx_id = cp_customer_trx_id
1809: AND payment_schedule_id = NVL(cp_payment_schedule_id, payment_schedule_id);
1810:
1811: --get the sum of extended_amount, taxable_amount from ra_customer_trx_lines_all for customer_trx_id, customer_trx_line_id and line_type
1923: AND customer_trx_line_id = cp_customer_trx_line_id
1924: AND line_type = lv_line_type_line; --'LINE'
1925:
1926:
1927: --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
1928: CURSOR cur_min_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1929: IS
1930: SELECT MIN(payment_schedule_id) payment_schedule_id,
1931: MIN(term_id) term_id
1928: CURSOR cur_min_payment_schedule(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
1929: IS
1930: SELECT MIN(payment_schedule_id) payment_schedule_id,
1931: MIN(term_id) term_id
1932: FROM ar_payment_schedules_all
1933: WHERE customer_trx_id = cp_customer_trx_id;
1934:
1935:
1936: --get the data from ra_customer_trx_all for a customer_trx_id
2031: AND display = 'Y'
2032: and status = 'APP'
2033: AND applied_payment_Schedule_id = cp_applied_payment_Schedule_id;
2034:
2035: --Get the data from ar_payment_schedules_all for customer_trx_id
2036: CURSOR cur_prev_payment_schedule( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
2037: cp_payment_Schedule_id ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL)
2038: IS
2039: SELECT amount_line_items_original,
2033: AND applied_payment_Schedule_id = cp_applied_payment_Schedule_id;
2034:
2035: --Get the data from ar_payment_schedules_all for customer_trx_id
2036: CURSOR cur_prev_payment_schedule( cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE,
2037: cp_payment_Schedule_id ar_payment_schedules_all.payment_schedule_id%TYPE DEFAULT NULL)
2038: IS
2039: SELECT amount_line_items_original,
2040: amount_line_items_remaining,
2041: tax_original,
2041: tax_original,
2042: tax_remaining,
2043: freight_original,
2044: amount_due_remaining
2045: FROM ar_payment_schedules_all
2046: WHERE customer_trx_id = cp_customer_trx_id
2047: AND payment_schedule_id = NVL(cp_payment_schedule_id, payment_schedule_id);
2048:
2049: CURSOR ORG_CUR IS
2238: jai_tax_processing_pkg.process_complete(p_customer_trx_id);
2239: ----------------------------------------------------------------------
2240: --Added by Zhiwei for Bug#14040855 end
2241:
2242: --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
2243: OPEN cur_min_payment_schedule(p_customer_trx_id);
2244: FETCH cur_min_payment_schedule INTO rec_min_payment_schedule;
2245: CLOSE cur_min_payment_schedule;
2246:
2652: END LOOP;
2653: --bug#9230409, end
2654:
2655:
2656: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2657: maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2658: p_payment_schedule_id => NULL,
2659: p_cm_customer_trx_id => p_customer_trx_id,
2660: p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2674: IF p_debug = 'Y' THEN
2675: fnd_file.put_line(FND_FILE.LOG, 'After INSERT call to maintain_schedules');
2676: END IF;
2677:
2678: UPDATE ar_payment_schedules_all
2679: SET amount_due_original = ROUND(NVL(amount_line_items_original,0) + ln_total_tax_amt_for_inv + ln_total_frt_amt_for_inv, ln_precision),
2680: tax_original = ROUND(ln_total_tax_amt_for_inv, ln_precision),
2681: freight_original = ROUND(ln_total_frt_amt_for_inv, ln_precision),
2682: amount_applied = ROUND(NVL(amount_line_items_original,0) + ln_total_tax_amt_for_inv + ln_total_frt_amt_for_inv, ln_precision),
2682: amount_applied = ROUND(NVL(amount_line_items_original,0) + ln_total_tax_amt_for_inv + ln_total_frt_amt_for_inv, ln_precision),
2683: last_update_date = SYSDATE
2684: WHERE customer_trx_id = p_customer_trx_id;
2685:
2686: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2687: maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2688: p_payment_schedule_id => NULL,
2689: p_cm_customer_trx_id => p_customer_trx_id,
2690: p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2705: fnd_file.put_line(FND_FILE.LOG, 'After UPDATE call to maintain_schedules');
2706: END IF;
2707:
2708:
2709: --get the min(payment_schedule_id) and term_id from ar_payment_schedules_all for customer_trx_id
2710: OPEN cur_min_payment_schedule(ln_previous_customer_trx_id);
2711: FETCH cur_min_payment_schedule INTO rec_inv_payment_schedule;
2712: CLOSE cur_min_payment_schedule;
2713:
2737: IF p_debug = 'Y' THEN
2738: fnd_file.put_line(FND_FILE.LOG, 'Before entering rec_payment LOOP');
2739: END IF;
2740:
2741: --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
2742: FOR rec_payment in cur_payment_schedule(ln_previous_customer_trx_id)
2743: LOOP
2744: IF p_debug = 'Y' THEN
2745: fnd_file.put_line(FND_FILE.LOG, 'In rec_payment LOOP');
2786: fnd_file.put_line(FND_FILE.LOG, 'after cursor cur_trx_types');
2787: END IF;
2788:
2789:
2790: --Get the data from ar_payment_schedules_all for customer_trx_id
2791: OPEN cur_prev_payment_schedule( ln_previous_customer_trx_id,
2792: rec_payment.payment_schedule_id);
2793: FETCH cur_prev_payment_schedule INTO rec_prev_payment_schedule;
2794: CLOSE cur_prev_payment_schedule;
2812: IF ( NVL(lv_allow_overappln_flag,'N') = 'Y' ) OR
2813: ( NVL(lv_allow_overappln_flag,'N') = 'N' AND ln_amt_due_rem >= 0 )
2814: THEN
2815:
2816: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2817: maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2818: p_payment_schedule_id => rec_payment.payment_schedule_id,
2819: p_cm_customer_trx_id => p_customer_trx_id,
2820: p_invoice_customer_trx_id => ln_previous_customer_trx_id,
2834: IF p_debug = 'Y' THEN
2835: fnd_file.put_line(FND_FILE.LOG, 'after Call to maintain_schedules');
2836: END IF;
2837:
2838: UPDATE ar_payment_schedules_all
2839: SET amount_due_remaining = ROUND (ln_amt_due_rem ,ln_precision) ,
2840: tax_remaining = ROUND(tax_original - ln_tax_amt_cashrcpt + NVL(ln_inst_tax_amt_for_inv,0),ln_precision) ,
2841: freight_remaining = ROUND(freight_original - ln_frt_amt_cashrcpt + NVL(ln_inst_frt_amt_for_inv,0),ln_precision) ,
2842: acctd_amount_due_remaining = ROUND(ln_amt_due_rem * ln_inv_curr_conv_rate, ln_precision) ,
2846: AND payment_schedule_id = rec_payment.payment_schedule_id;
2847:
2848:
2849: IF p_debug = 'Y' THEN
2850: fnd_file.put_line(FND_FILE.LOG, 'After first update fo ar_payment_schedules_all');
2851: END IF;
2852:
2853: UPDATE ar_payment_schedules_all
2854: SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
2849: IF p_debug = 'Y' THEN
2850: fnd_file.put_line(FND_FILE.LOG, 'After first update fo ar_payment_schedules_all');
2851: END IF;
2852:
2853: UPDATE ar_payment_schedules_all
2854: SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
2855: gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')) ,
2856: actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ),-- TO_DATE('31-DEC-4712','DD-MON-YYYY')),
2857: last_update_date = SYSDATE
2858: WHERE customer_trx_id = ln_previous_customer_trx_id
2859: AND payment_schedule_id = rec_payment.payment_schedule_id;
2860:
2861: IF p_debug = 'Y' THEN
2862: fnd_file.put_line(FND_FILE.LOG, 'After second update fo ar_payment_schedules_all');
2863: END IF;
2864:
2865: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2866: maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2861: IF p_debug = 'Y' THEN
2862: fnd_file.put_line(FND_FILE.LOG, 'After second update fo ar_payment_schedules_all');
2863: END IF;
2864:
2865: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
2866: maintain_schedules( p_customer_trx_id => p_customer_trx_id,
2867: p_payment_schedule_id => rec_payment.payment_schedule_id,
2868: p_cm_customer_trx_id => p_customer_trx_id,
2869: p_invoice_customer_trx_id => ln_previous_customer_trx_id,
3046: OPEN cur_term_details(rec_min_payment_schedule.term_id);
3047: FETCH cur_term_details INTO rec_term_details;
3048: CLOSE cur_term_details;
3049:
3050: --get the data from ar_payment_schedules_all for customer_trx_id and payment_schedule_id
3051: FOR rec_payment in cur_payment_schedule(p_customer_trx_id)
3052: LOOP
3053:
3054: IF p_debug = 'Y' THEN
3072: ln_inst_tax_amt_for_inv := ln_total_tax_amt_for_inv * ln_apportion_factor;
3073: ln_inst_frt_amt_for_inv := ln_total_frt_amt_for_inv * ln_apportion_factor;
3074:
3075:
3076: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
3077: maintain_schedules( p_customer_trx_id => p_customer_trx_id,
3078: p_payment_schedule_id => rec_payment.payment_schedule_id,
3079: p_cm_customer_trx_id => NULL,
3080: p_invoice_customer_trx_id => p_customer_trx_id,
3094: IF p_debug = 'Y' THEN
3095: fnd_file.put_line(FND_FILE.LOG, 'After call to maintain_schedules');
3096: END IF;
3097:
3098: UPDATE ar_payment_schedules_all
3099: SET amount_due_original = ROUND(NVL(amount_line_items_original,0) + NVL(ln_inst_tax_amt_for_inv,0) + NVL(ln_inst_frt_amt_for_inv,0) , ln_precision),
3100: amount_due_remaining = ROUND(NVL(amount_line_items_remaining,0) + NVL(ln_inst_tax_amt_for_inv,0) + NVL(ln_inst_frt_amt_for_inv,0), ln_precision),
3101: tax_original = ROUND(NVL(ln_inst_tax_amt_for_inv,0), ln_precision),
3102: tax_remaining = ROUND(NVL(ln_inst_tax_amt_for_inv,0), ln_precision),
3107: WHERE customer_trx_id = p_customer_trx_id
3108: AND payment_schedule_id = rec_payment.payment_schedule_id;
3109:
3110: IF p_debug = 'Y' THEN
3111: fnd_file.put_line(FND_FILE.LOG, 'After First update of ar_payment_schedules_all');
3112: END IF;
3113:
3114: UPDATE ar_payment_schedules_all
3115: SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
3110: IF p_debug = 'Y' THEN
3111: fnd_file.put_line(FND_FILE.LOG, 'After First update of ar_payment_schedules_all');
3112: END IF;
3113:
3114: UPDATE ar_payment_schedules_all
3115: SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
3116: gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')) ,
3117: actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/RRRR') /* Commented by Nprashar for Bug #6784276(SYSDATE -100000 )*/ ), --TO_DATE('31-DEC-4712','DD-MON-YYYY')),
3118: last_update_date = SYSDATE
3119: WHERE customer_trx_id = p_customer_trx_id
3120: AND payment_schedule_id = rec_payment.payment_schedule_id;
3121:
3122: IF p_debug = 'Y' THEN
3123: fnd_file.put_line(FND_FILE.LOG, 'After Second update of ar_payment_schedules_all '||SQL%ROWCOUNT);
3124: END IF;
3125:
3126:
3127: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
3123: fnd_file.put_line(FND_FILE.LOG, 'After Second update of ar_payment_schedules_all '||SQL%ROWCOUNT);
3124: END IF;
3125:
3126:
3127: --This procedure maintains the history of ar_payment_schedules_all in jai_ar_payment_audits
3128: maintain_schedules( p_customer_trx_id => p_customer_trx_id,
3129: p_payment_schedule_id => rec_payment.payment_schedule_id,
3130: p_cm_customer_trx_id => NULL,
3131: p_invoice_customer_trx_id => p_customer_trx_id,
3155: END LOOP; --End cursor rec_payment
3156: END IF;
3157:
3158:
3159: --This procedure updates the MRC data for ra_cust_trx_line_gl_dist_all, ar_payment_schedules_all, ar_receivable_applications_all
3160: maintain_mrc( p_customer_trx_id => p_customer_trx_id,
3161: p_previous_cust_trx_id => ln_previous_customer_trx_id,
3162: p_called_from => 7/13/2007,
3163: p_process_status => lv_process_status,
3337:
3338: --2001/06/26 Anuradha Parthasarathy
3339: Cursor payment_schedule_cur IS
3340: Select min(payment_schedule_id)
3341: From Ar_Payment_Schedules_All
3342: Where Customer_trx_ID = p_customer_trx_id;
3343:
3344: /* AR Transactions with Invoicing Acctg Rules not supported by Localization, Enhancement Done on 16th NOV */
3345: Cursor accounting_set_cur IS
3358: v_prev_customer_trx_id Number;
3359:
3360: Cursor Inv_payment_schedule_cur(p_prev_customer_trx_id IN Number) is
3361: Select payment_schedule_id
3362: from ar_payment_schedules_all
3363: where customer_trx_id = p_prev_customer_trx_id;
3364:
3365: --2001/07/04 Anuradha Parthasarathy
3366: v_interface_line_attribute6 Varchar2(30);
3874: DELETE RA_CUSTOMER_TRX_LINES_ALL
3875: WHERE LINK_TO_CUST_TRX_LINE_ID = LINK_REC.LINK_TO_CUST_TRX_LINE_ID;
3876: v_sql_num :=16;
3877:
3878: Update Ar_Payment_Schedules_All
3879: Set Tax_Original = 0,
3880: Tax_remaining = 0,
3881: Freight_Original = 0,
3882: Freight_remaining = 0,
4167: If v_prev_customer_trx_id is null then
4168:
4169: v_sql_num :=32;
4170:
4171: Update Ar_Payment_Schedules_All
4172: Set Tax_Original = NVL(Tax_Original,0) + NVL(v_tax_amount,0),
4173: Tax_remaining = NVL(Tax_remaining,0) + NVL(v_tax_amount,0),
4174: Freight_Original = NVL(Freight_Original,0) + NVL(v_freight_amount,0),
4175: Freight_remaining = NVL(Freight_remaining,0) + NVL(v_freight_amount,0),
4251:
4252: --2001/07/04 Anuradha Parthasarathy
4253:
4254: IF v_return_reference_type_code = 'Sales Order India' and v_credit_invoice_line_id IS NULL THEN
4255: Update Ar_Payment_Schedules_All
4256: Set Tax_Original = NVL(Tax_Original,0) + NVL(v_tax_amount,0),
4257: Freight_Original = NVL(Freight_Original,0) + NVL(v_freight_amount,0),
4258: Amount_Due_Original = NVL(Amount_Due_Original,0) + NVL(v_receivable_amount,0)
4259: Where Customer_Trx_ID = p_customer_trx_id
4277: WHERE customer_trx_id = p_customer_trx_id
4278: AND account_class = lv_acc_class_rec; --rchandan for bug#4428980
4279:
4280:
4281: Update Ar_Payment_Schedules_All
4282: Set Tax_Original = NVL(v_tax_amount1,0),
4283: Freight_Original = NVL(v_freight_amount1,0),
4284: Amount_Due_Original = NVL(Amount_line_items_Original,0) + NVL(v_tax_amount1,0) + NVL(v_freight_amount1,0),
4285: Amount_Applied = NVL(Amount_line_items_Original,0) + NVL(v_tax_amount1,0) + NVL(v_freight_amount1,0)
4304: OPEN Inv_payment_schedule_cur(v_prev_customer_trx_id);
4305: FETCH Inv_payment_schedule_cur into v_payment_schedule_id;
4306: CLOSE Inv_payment_schedule_cur;
4307:
4308: Update Ar_Payment_Schedules_All
4309: Set --Tax_remaining = NVL(Tax_remaining,0) - NVL(v_tax_amount1,0),
4310: Freight_remaining = NVL(Freight_remaining,0) + NVL(v_freight_amount1,0),
4311: Amount_Due_remaining = NVL(Amount_Due_remaining,0) + NVL(v_freight_amount1,0),
4312: Amount_Credited = NVL(Amount_Credited,0) + NVL(v_freight_amount1,0),