eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.ARP_PROCESS_LOCKBOX dependencies on AR_PMTS_INTERFACE_LINE_DETAILS

Line 2118: FROM ar_pmts_interface_line_details

2114:
2115: CURSOR apply_to_rec(p_trans_request_id IN NUMBER,
2116: p_customer_trx_id IN NUMBER) IS
2117: SELECT distinct apply_to
2118: FROM ar_pmts_interface_line_details
2119: WHERE transmission_request_id = p_trans_request_id
2120: AND customer_trx_id = p_customer_trx_id;
2121:
2122: CURSOR trans_rec_cur(p_trans_request_id IN NUMBER) IS

Line 2124: FROM ar_pmts_interface_line_details

2120: AND customer_trx_id = p_customer_trx_id;
2121:
2122: CURSOR trans_rec_cur(p_trans_request_id IN NUMBER) IS
2123: SELECT distinct transmission_record_id
2124: FROM ar_pmts_interface_line_details
2125: WHERE transmission_request_id = p_trans_request_id
2126: AND status = 'AR_PLB_NEW_RECORD';
2127:
2128: CURSOR invoice_rec_cur(p_trans_request_id IN NUMBER,

Line 2131: FROM ar_pmts_interface_line_details

2127:
2128: CURSOR invoice_rec_cur(p_trans_request_id IN NUMBER,
2129: p_transmission_record_id IN NUMBER) IS
2130: SELECT distinct customer_trx_id
2131: FROM ar_pmts_interface_line_details
2132: WHERE transmission_request_id = p_trans_request_id
2133: AND transmission_record_id = p_transmission_record_id;
2134:
2135: CURSOR app_to_cur(p_trans_req_id IN NUMBER,

Line 2142: FROM ar_pmts_interface_line_details

2138: SELECT apply_to,
2139: line_amount,
2140: tax,
2141: amount_applied
2142: FROM ar_pmts_interface_line_details
2143: WHERE transmission_request_id = p_trans_req_id
2144: AND transmission_record_id = p_transmission_record_id
2145: AND customer_trx_id = p_customer_trx_id
2146: AND apply_to NOT IN ('FREIGHT','CHARGES')

Line 2199: UPDATE ar_pmts_interface_line_details

2195: debug1('arp_process_lockbox.validate_llca_interface_data()+');
2196: fnd_file.put_line(fnd_file.log, 'arp_process_lockbox.validate_llca_interface_data()+');
2197: p_trans_req_id := to_number(p_trans_request_id);
2198:
2199: UPDATE ar_pmts_interface_line_details
2200: SET status = 'AR_PLB_NEW_RECORD'
2201: WHERE transmission_request_id = p_trans_req_id;
2202:
2203: UPDATE ar_pmts_interface_line_details

Line 2203: UPDATE ar_pmts_interface_line_details

2199: UPDATE ar_pmts_interface_line_details
2200: SET status = 'AR_PLB_NEW_RECORD'
2201: WHERE transmission_request_id = p_trans_req_id;
2202:
2203: UPDATE ar_pmts_interface_line_details
2204: SET amount_applied = decode(apply_to,'FREIGHT',freight,charges)
2205: WHERE transmission_request_id = p_trans_req_id
2206: AND apply_to IN ('FREIGHT', 'CHARGES')
2207: AND amount_applied IS NULL

Line 2210: UPDATE ar_pmts_interface_line_details line_details

2206: AND apply_to IN ('FREIGHT', 'CHARGES')
2207: AND amount_applied IS NULL
2208: AND allocated_receipt_amount IS NULL;
2209:
2210: UPDATE ar_pmts_interface_line_details line_details
2211: SET status = 'AR_PLB_INVALID_REC_ID'
2212: WHERE transmission_record_id in ( SELECT transmission_record_id
2213: FROM ar_payments_interface interface
2214: WHERE interface.transmission_request_id = p_trans_req_id

Line 2222: UPDATE ar_pmts_interface_line_details line_details

2218: )
2219: AND transmission_request_id = p_trans_req_id
2220: AND status = 'AR_PLB_NEW_RECORD';
2221:
2222: UPDATE ar_pmts_interface_line_details line_details
2223: SET status = 'AR_PLB_INVALID_RECORD'
2224: WHERE transmission_record_id in ( SELECT transmission_record_id
2225: FROM ar_payments_interface interface
2226: WHERE interface.transmission_request_id = p_trans_req_id

Line 2304: FROM ar_pmts_interface_line_details line_details

2300: AND fc.currency_code = interface.currency_code
2301: AND fc1.currency_code = nvl(interface.invoice_currency_code1,
2302: interface.currency_code)
2303: AND EXISTS ( SELECT 'X'
2304: FROM ar_pmts_interface_line_details line_details
2305: WHERE line_details.transmission_record_id = interface.transmission_record_id
2306: AND line_details.invoice_number = interface.invoice1);
2307:
2308: INSERT INTO ar_pmts_interface_header_gt

Line 2341: FROM ar_pmts_interface_line_details line_details

2337: AND fc.currency_code = interface.currency_code
2338: AND fc1.currency_code = nvl(interface.invoice_currency_code2,
2339: interface.currency_code)
2340: AND EXISTS ( SELECT 'X'
2341: FROM ar_pmts_interface_line_details line_details
2342: WHERE line_details.transmission_record_id = interface.transmission_record_id
2343: AND line_details.invoice_number = interface.invoice2);
2344:
2345: INSERT INTO ar_pmts_interface_header_gt

Line 2378: FROM ar_pmts_interface_line_details line_details

2374: AND fc.currency_code = interface.currency_code
2375: AND fc1.currency_code = nvl(interface.invoice_currency_code3,
2376: interface.currency_code)
2377: AND EXISTS ( SELECT 'X'
2378: FROM ar_pmts_interface_line_details line_details
2379: WHERE line_details.transmission_record_id = interface.transmission_record_id
2380: AND line_details.invoice_number = interface.invoice3);
2381:
2382: INSERT INTO ar_pmts_interface_header_gt

Line 2415: FROM ar_pmts_interface_line_details line_details

2411: AND fc.currency_code = interface.currency_code
2412: AND fc1.currency_code = nvl(interface.invoice_currency_code4,
2413: interface.currency_code)
2414: AND EXISTS ( SELECT 'X'
2415: FROM ar_pmts_interface_line_details line_details
2416: WHERE line_details.transmission_record_id = interface.transmission_record_id
2417: AND line_details.invoice_number = interface.invoice4);
2418:
2419: INSERT INTO ar_pmts_interface_header_gt

Line 2452: FROM ar_pmts_interface_line_details line_details

2448: AND fc.currency_code = interface.currency_code
2449: AND fc1.currency_code = nvl(interface.invoice_currency_code5,
2450: interface.currency_code)
2451: AND EXISTS ( SELECT 'X'
2452: FROM ar_pmts_interface_line_details line_details
2453: WHERE line_details.transmission_record_id = interface.transmission_record_id
2454: AND line_details.invoice_number = interface.invoice5);
2455:
2456: INSERT INTO ar_pmts_interface_header_gt

Line 2489: FROM ar_pmts_interface_line_details line_details

2485: AND fc.currency_code = interface.currency_code
2486: AND fc1.currency_code = nvl(interface.invoice_currency_code6,
2487: interface.currency_code)
2488: AND EXISTS ( SELECT 'X'
2489: FROM ar_pmts_interface_line_details line_details
2490: WHERE line_details.transmission_record_id = interface.transmission_record_id
2491: AND line_details.invoice_number = interface.invoice6);
2492:
2493: INSERT INTO ar_pmts_interface_header_gt

Line 2526: FROM ar_pmts_interface_line_details line_details

2522: AND fc.currency_code = interface.currency_code
2523: AND fc1.currency_code = nvl(interface.invoice_currency_code7,
2524: interface.currency_code)
2525: AND EXISTS ( SELECT 'X'
2526: FROM ar_pmts_interface_line_details line_details
2527: WHERE line_details.transmission_record_id = interface.transmission_record_id
2528: AND line_details.invoice_number = interface.invoice7);
2529:
2530: INSERT INTO ar_pmts_interface_header_gt

Line 2563: FROM ar_pmts_interface_line_details line_details

2559: AND fc.currency_code = interface.currency_code
2560: AND fc1.currency_code = nvl(interface.invoice_currency_code8,
2561: interface.currency_code)
2562: AND EXISTS ( SELECT 'X'
2563: FROM ar_pmts_interface_line_details line_details
2564: WHERE line_details.transmission_record_id = interface.transmission_record_id
2565: AND line_details.invoice_number = interface.invoice8);
2566:
2567: UPDATE ar_pmts_interface_line_details line_details

Line 2567: UPDATE ar_pmts_interface_line_details line_details

2563: FROM ar_pmts_interface_line_details line_details
2564: WHERE line_details.transmission_record_id = interface.transmission_record_id
2565: AND line_details.invoice_number = interface.invoice8);
2566:
2567: UPDATE ar_pmts_interface_line_details line_details
2568: SET status = 'AR_PLB_DUP_INVOICE'
2569: WHERE invoice_number IN ( SELECT invoice_number
2570: FROM ar_pmts_interface_header_gt
2571: WHERE transmission_record_id = line_details.transmission_record_id

Line 2577: UPDATE ar_pmts_interface_line_details line_details

2573: HAVING count(invoice_number) > 1 )
2574: AND transmission_request_id = p_trans_req_id
2575: AND status = 'AR_PLB_NEW_RECORD';
2576:
2577: UPDATE ar_pmts_interface_line_details line_details
2578: SET status = 'AR_PLB_DUP_FRGT_CHRG'
2579: WHERE (transmission_record_id, invoice_number, apply_to)
2580: IN (SELECT transmission_record_id, invoice_number, apply_to
2581: FROM ar_pmts_interface_line_details ld

Line 2581: FROM ar_pmts_interface_line_details ld

2577: UPDATE ar_pmts_interface_line_details line_details
2578: SET status = 'AR_PLB_DUP_FRGT_CHRG'
2579: WHERE (transmission_record_id, invoice_number, apply_to)
2580: IN (SELECT transmission_record_id, invoice_number, apply_to
2581: FROM ar_pmts_interface_line_details ld
2582: WHERE ld.transmission_record_id = line_details.transmission_record_id
2583: AND ld.invoice_number = line_details.invoice_number
2584: AND ld.apply_to = line_details.apply_to
2585: AND ld.transmission_request_id = p_trans_req_id

Line 2592: UPDATE ar_pmts_interface_line_details

2588: AND transmission_request_id = p_trans_req_id
2589: AND apply_to IN ('FREIGHT', 'CHARGES')
2590: AND status = 'AR_PLB_NEW_RECORD';
2591:
2592: UPDATE ar_pmts_interface_line_details
2593: SET status = 'AR_PLB_NO_APP_INFO'
2594: WHERE transmission_request_id = p_trans_req_id
2595: AND amount_applied IS NULL
2596: AND line_amount IS NULL

Line 2601: /*UPDATE ar_pmts_interface_line_details

2597: AND allocated_receipt_amount IS NULL
2598: AND status = 'AR_PLB_NEW_RECORD';
2599:
2600: /* Does we need this since we are defaulting values? */
2601: /*UPDATE ar_pmts_interface_line_details
2602: SET status = 'AR_PLB_LINE_TAX_TOT_MISMATCH'
2603: WHERE transmission_request_id = p_trans_req_id
2604: AND amount_applied IS NOT NULL
2605: AND line_amount IS NOT NULL

Line 2616: UPDATE ar_pmts_interface_line_details ld

2612: SET (customer_trx_id) = ( SELECT customer_trx_id
2613: FROM ra_customer_trx
2614: WHERE trx_number = gt.invoice_number ) ;
2615:
2616: UPDATE ar_pmts_interface_line_details ld
2617: SET customer_trx_id = (SELECT customer_trx_id
2618: FROM ar_pmts_interface_header_gt
2619: WHERE invoice_number = ld.invoice_number
2620: AND transmission_record_id = ld.transmission_record_id

Line 2628: UPDATE ar_pmts_interface_line_details line_details

2624: /*UPDATE ar_pmts_interface_header_gt gt
2625: SET precision = get_currency_precision(gt.currency_code),
2626: inv_precision = get_currency_precision(NVL(gt.inv_currency_code, gt.currency_code));*/
2627:
2628: UPDATE ar_pmts_interface_line_details line_details
2629: SET amount_applied = allocated_receipt_amount
2630: WHERE amount_applied IS NULL
2631: AND allocated_receipt_amount IS NOT NULL
2632: AND invoice_number IN ( SELECT invoice_number

Line 2661: UPDATE ar_pmts_interface_line_details

2657: x_11i_app => ll_leg_app,
2658: x_mfar_app => ll_mfar_app );
2659:
2660: IF (ll_leg_adj = 'Y') OR (ll_leg_app = 'Y') then
2661: UPDATE ar_pmts_interface_line_details
2662: SET status = 'AR_PLB_BAL_STAMP_FAILED'
2663: WHERE transmission_request_id = p_trans_req_id
2664: AND invoice_number = ( SELECT invoice_number
2665: FROM ar_pmts_interface_header_gt

Line 2683: UPDATE ar_pmts_interface_line_details line_details

2679:
2680: end if;
2681: END LOOP;
2682:
2683: UPDATE ar_pmts_interface_line_details line_details
2684: SET status = 'AR_PLB_INVALID_LINE_NUM'
2685: WHERE transmission_request_id = p_trans_req_id
2686: AND apply_to NOT in ('FREIGHT','CHARGES')
2687: AND NOT EXISTS ( select 'x'

Line 2698: UPDATE ar_pmts_interface_line_details line_details

2694: AND trx.customer_trx_id = header.customer_trx_id
2695: AND lines.line_type = 'LINE'
2696: AND lines.line_number = line_details.apply_to );
2697:
2698: UPDATE ar_pmts_interface_line_details line_details
2699: SET status = 'AR_PLB_INVALID_LINE_NUM'
2700: WHERE transmission_request_id = p_trans_req_id
2701: AND apply_to IN ('FREIGHT', 'CHARGES')
2702: AND NOT EXISTS ( select 'x'

Line 2788: UPDATE ar_pmts_interface_line_details

2784: WHERE TL.customer_trx_id = lines.customer_trx_id
2785: AND TL.line_type = lines.apply_to)
2786: WHERE lines.apply_to = 'CHARGES';
2787:
2788: UPDATE ar_pmts_interface_line_details
2789: SET status = 'AR_PLB_INVALID_APP'
2790: WHERE transmission_request_id = p_trans_req_id
2791: AND (customer_trx_id, apply_to) IN
2792: (SELECT customer_trx_id, apply_to

Line 2798: UPDATE ar_pmts_interface_line_details

2794: WHERE line_amt_remaining = 0
2795: AND tax_remaining = 0
2796: AND apply_to NOT IN ('FREIGHT', 'CHARGES'));
2797:
2798: UPDATE ar_pmts_interface_line_details
2799: SET status = 'AR_PLB_INVALID_APP'
2800: WHERE transmission_request_id = p_trans_req_id
2801: AND (customer_trx_id, apply_to) IN
2802: (SELECT customer_trx_id, apply_to

Line 2807: UPDATE ar_pmts_interface_line_details

2803: FROM ar_pmts_interface_lines_gt
2804: WHERE freight_remaining = 0
2805: AND apply_to = 'FREIGHT');
2806:
2807: UPDATE ar_pmts_interface_line_details
2808: SET status = 'AR_PLB_INVALID_APP'
2809: WHERE transmission_request_id = p_trans_req_id
2810: AND (customer_trx_id, apply_to) IN
2811: (SELECT customer_trx_id, apply_to

Line 2820: FROM ar_pmts_interface_line_details

2816: UPDATE ar_pmts_interface_header_gt header
2817: SET default_by = 'LINE_AMT'
2818: WHERE transmission_request_id = p_trans_req_id
2819: AND invoice_number IN (SELECT invoice_number
2820: FROM ar_pmts_interface_line_details
2821: WHERE transmission_request_id = p_trans_req_id
2822: AND transmission_record_id = header.transmission_record_id
2823: AND allocated_receipt_amount IS NULL
2824: AND line_amount IS NOT NULL);

Line 2853: FROM ar_pmts_interface_line_details

2849:
2850: SELECT SUM(DECODE(apply_to,'FREIGHT',0, 'CHARGES',0,1)) line_app_count,
2851: count(*) tot_app_count
2852: INTO line_app_count, tot_app_count
2853: FROM ar_pmts_interface_line_details
2854: WHERE transmission_record_id = cur_var.transmission_record_id
2855: AND invoice_number = l_invoice_number
2856: AND transmission_request_id = p_trans_req_id ;
2857:

Line 2858: UPDATE ar_pmts_interface_line_details

2854: WHERE transmission_record_id = cur_var.transmission_record_id
2855: AND invoice_number = l_invoice_number
2856: AND transmission_request_id = p_trans_req_id ;
2857:
2858: UPDATE ar_pmts_interface_line_details
2859: SET amount_applied = ARPCURR.CurrRound( allocated_receipt_amount/l_trans_to_receipt_rate,
2860: l_currency_code),
2861: line_amount = NULL,
2862: tax = NULL

Line 2954: UPDATE ar_pmts_interface_line_details

2950: - Nvl(l_calc_line_amount,0))
2951: ,l_inv_currency_code);
2952: End If;
2953:
2954: UPDATE ar_pmts_interface_line_details
2955: SET amount_applied = l_calc_tot_amount_app,
2956: line_amount = l_calc_line_amount,
2957: tax = l_calc_tax_amount
2958: WHERE transmission_record_id = cur_var.transmission_record_id

Line 2966: UPDATE ar_pmts_interface_line_details line_details

2962: END LOOP;
2963: END IF;
2964:
2965: IF l_inv_currency_code <> l_currency_code THEN
2966: UPDATE ar_pmts_interface_line_details line_details
2967: SET allocated_receipt_amount
2968: = ARPCURR.CurrRound( amount_applied * l_trans_to_receipt_rate,
2969: l_inv_currency_code)
2970: WHERE amount_applied IS NOT NULL

Line 2990: FROM ar_pmts_interface_line_details

2986: END IF;
2987:
2988: SELECT SUM(amount_applied), SUM(allocated_receipt_amount)
2989: INTO l_tot_amt_app, l_tot_amt_app_from
2990: FROM ar_pmts_interface_line_details
2991: WHERE transmission_request_id = p_trans_req_id
2992: AND transmission_record_id = cur_var.transmission_record_id
2993: AND invoice_number = l_invoice_number;
2994:

Line 2997: UPDATE ar_pmts_interface_line_details

2993: AND invoice_number = l_invoice_number;
2994:
2995: IF l_default_by = 'AMT_APP_FRM' THEN
2996: arp_util.debug(' Deafult By '||l_default_by);
2997: UPDATE ar_pmts_interface_line_details
2998: SET amount_applied = amount_applied + (hdr_amt_app - l_tot_amt_app )
2999: WHERE transmission_request_id = p_trans_req_id
3000: AND transmission_record_id = cur_var.transmission_record_id
3001: AND invoice_number = l_invoice_number

Line 3007: UPDATE ar_pmts_interface_line_details

3003: END IF;
3004:
3005: IF l_default_by = 'AMT_APP' THEN
3006: arp_util.debug(' Deafult By '||l_default_by);
3007: UPDATE ar_pmts_interface_line_details
3008: SET allocated_receipt_amount = allocated_receipt_amount + (hdr_amt_app_frm - l_tot_amt_app_from )
3009: WHERE transmission_request_id = p_trans_req_id
3010: AND transmission_record_id = cur_var.transmission_record_id
3011: AND invoice_number = l_invoice_number

Line 3018: UPDATE ar_pmts_interface_line_details ld

3014:
3015: END LOOP;
3016: END LOOP;
3017: /* At this stage we will have all values calculated with us. So we can go for validation */
3018: UPDATE ar_pmts_interface_line_details ld
3019: SET status = 'AR_PLB_EXCEED_LINE_AMT'
3020: WHERE transmission_request_id = p_trans_req_id
3021: AND (customer_trx_id, apply_to) IN
3022: ( select lines_gt.customer_trx_id, lines_gt.apply_to

Line 3027: from ar_pmts_interface_line_details lines

3023: from ar_pmts_interface_lines_gt ld1,
3024: ( select customer_trx_id,
3025: apply_to,
3026: sum(lines.line_amount) tot_amt
3027: from ar_pmts_interface_line_details lines
3028: where lines.transmission_request_id = p_trans_req_id
3029: group by customer_trx_id, apply_to ) lines_gt
3030: where lines_gt.customer_trx_id = ld1.customer_trx_id
3031: and lines_gt.apply_to = ld1.apply_to

Line 3036: UPDATE ar_pmts_interface_line_details ld

3032: and ld1.apply_to NOT IN ('FREIGHT', 'CHARGES')
3033: and ld1.line_amt_remaining < lines_gt.tot_amt)
3034: AND status = 'AR_PLB_NEW_RECORD';
3035:
3036: UPDATE ar_pmts_interface_line_details ld
3037: SET status = 'AR_PLB_EXCEED_TAX_AMT'
3038: WHERE transmission_request_id = p_trans_req_id
3039: AND (customer_trx_id, apply_to) IN
3040: ( select lines_gt.customer_trx_id, lines_gt.apply_to

Line 3045: from ar_pmts_interface_line_details lines

3041: from ar_pmts_interface_lines_gt ld1,
3042: ( select customer_trx_id,
3043: apply_to,
3044: sum(lines.tax) tot_amt
3045: from ar_pmts_interface_line_details lines
3046: where lines.transmission_request_id = p_trans_req_id
3047: group by customer_trx_id, apply_to ) lines_gt
3048: where lines_gt.customer_trx_id = ld1.customer_trx_id
3049: and lines_gt.apply_to = ld1.apply_to

Line 3054: UPDATE ar_pmts_interface_line_details ld

3050: and ld1.apply_to NOT IN ('FREIGHT', 'CHARGES')
3051: and ld1.tax_remaining < lines_gt.tot_amt)
3052: AND status = 'AR_PLB_NEW_RECORD';
3053:
3054: UPDATE ar_pmts_interface_line_details ld
3055: SET status = 'AR_PLB_EXCEED_FRGT_AMT'
3056: WHERE transmission_request_id = p_trans_req_id
3057: AND (customer_trx_id, apply_to) IN
3058: ( select ld1.customer_trx_id, ld1.apply_to

Line 3060: ar_pmts_interface_line_details ld2

3056: WHERE transmission_request_id = p_trans_req_id
3057: AND (customer_trx_id, apply_to) IN
3058: ( select ld1.customer_trx_id, ld1.apply_to
3059: from ar_pmts_interface_lines_gt ld1,
3060: ar_pmts_interface_line_details ld2
3061: where ld1.customer_trx_id = ld2.customer_trx_id
3062: and ld1.apply_to = ld2.apply_to
3063: and ld2.apply_to = 'FREIGHT'
3064: and ld2.transmission_request_id = p_trans_req_id

Line 3068: UPDATE ar_pmts_interface_line_details ld

3064: and ld2.transmission_request_id = p_trans_req_id
3065: and ld1.freight_remaining < ld2.amount_applied)
3066: AND status = 'AR_PLB_NEW_RECORD';
3067:
3068: UPDATE ar_pmts_interface_line_details ld
3069: SET status = 'AR_PLB_EXCEED_CHRG_AMT'
3070: WHERE transmission_request_id = p_trans_req_id
3071: AND (customer_trx_id, apply_to) IN
3072: ( select ld1.customer_trx_id, ld1.apply_to

Line 3074: ar_pmts_interface_line_details ld2

3070: WHERE transmission_request_id = p_trans_req_id
3071: AND (customer_trx_id, apply_to) IN
3072: ( select ld1.customer_trx_id, ld1.apply_to
3073: from ar_pmts_interface_lines_gt ld1,
3074: ar_pmts_interface_line_details ld2
3075: where ld1.customer_trx_id = ld2.customer_trx_id
3076: and ld1.apply_to = ld2.apply_to
3077: and ld2.transmission_request_id = p_trans_req_id
3078: and ld2.apply_to = 'CHARGES'

Line 3082: UPDATE ar_pmts_interface_line_details line_details

3078: and ld2.apply_to = 'CHARGES'
3079: and ld1.charges_remaining < ld2.amount_applied)
3080: AND status = 'AR_PLB_NEW_RECORD';
3081:
3082: UPDATE ar_pmts_interface_line_details line_details
3083: SET status = 'AR_PLB_AMT_APP_INVALID'
3084: WHERE transmission_request_id = p_trans_req_id
3085: AND (transmission_record_id, customer_trx_id) IN
3086: (select header.transmission_record_id, header.customer_trx_id

Line 3091: from ar_pmts_interface_line_details ld

3087: from ar_pmts_interface_header_gt header,
3088: (select transmission_record_id,
3089: customer_trx_id,
3090: sum(amount_applied) aa
3091: from ar_pmts_interface_line_details ld
3092: where ld.transmission_request_id = p_trans_req_id
3093: group by transmission_record_id, customer_trx_id) line
3094: where header.transmission_record_id = line.transmission_record_id
3095: and header.customer_trx_id = line.customer_trx_id

Line 3100: UPDATE ar_pmts_interface_line_details line_details

3096: and header.transmission_request_id = p_trans_req_id
3097: and header.amount_applied <> line.aa)
3098: AND status = 'AR_PLB_NEW_RECORD';
3099:
3100: UPDATE ar_pmts_interface_line_details line_details
3101: SET status = 'AR_PLB_AMT_APP_FRM_INVALID'
3102: WHERE transmission_request_id = p_trans_req_id
3103: AND (transmission_record_id, customer_trx_id) IN
3104: (select header.transmission_record_id, header.customer_trx_id

Line 3109: from ar_pmts_interface_line_details ld

3105: from ar_pmts_interface_header_gt header,
3106: (select transmission_record_id,
3107: customer_trx_id,
3108: sum(allocated_receipt_amount) aa
3109: from ar_pmts_interface_line_details ld
3110: where ld.transmission_request_id = p_trans_req_id
3111: group by transmission_record_id, customer_trx_id) line
3112: where header.transmission_record_id = line.transmission_record_id
3113: and header.customer_trx_id = line.customer_trx_id

Line 3118: UPDATE ar_pmts_interface_line_details

3114: and header.transmission_request_id = p_trans_req_id
3115: and header.amount_applied_from <> line.aa)
3116: AND status = 'AR_PLB_NEW_RECORD';
3117:
3118: UPDATE ar_pmts_interface_line_details
3119: SET status = 'AR_PLB_LINE_OK'
3120: WHERE status = 'AR_PLB_NEW_RECORD'
3121: AND transmission_request_id = p_trans_req_id;
3122:

Line 3128: FROM ar_pmts_interface_line_details line_details

3124: UPDATE ar_payments_interface interface
3125: SET resolved_matching_number1 = NULL
3126: WHERE (transmission_record_id, resolved_matching_number1)
3127: IN (SELECT transmission_record_id, invoice_number
3128: FROM ar_pmts_interface_line_details line_details
3129: WHERE transmission_request_id = p_trans_req_id
3130: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3131: AND transmission_request_id = p_trans_req_id;
3132:

Line 3137: FROM ar_pmts_interface_line_details line_details

3133: UPDATE ar_payments_interface interface
3134: SET resolved_matching_number2 = NULL
3135: WHERE (transmission_record_id, resolved_matching_number2)
3136: IN (SELECT transmission_record_id, invoice_number
3137: FROM ar_pmts_interface_line_details line_details
3138: WHERE transmission_request_id = p_trans_req_id
3139: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3140: AND transmission_request_id = p_trans_req_id;
3141:

Line 3146: FROM ar_pmts_interface_line_details line_details

3142: UPDATE ar_payments_interface interface
3143: SET resolved_matching_number3 = NULL
3144: WHERE (transmission_record_id, resolved_matching_number3)
3145: IN (SELECT transmission_record_id, invoice_number
3146: FROM ar_pmts_interface_line_details line_details
3147: WHERE transmission_request_id = p_trans_req_id
3148: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3149: AND transmission_request_id = p_trans_req_id;
3150:

Line 3155: FROM ar_pmts_interface_line_details line_details

3151: UPDATE ar_payments_interface interface
3152: SET resolved_matching_number4 = NULL
3153: WHERE (transmission_record_id, resolved_matching_number4)
3154: IN (SELECT transmission_record_id, invoice_number
3155: FROM ar_pmts_interface_line_details line_details
3156: WHERE transmission_request_id = p_trans_req_id
3157: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3158: AND transmission_request_id = p_trans_req_id;
3159:

Line 3164: FROM ar_pmts_interface_line_details line_details

3160: UPDATE ar_payments_interface interface
3161: SET resolved_matching_number5 = NULL
3162: WHERE (transmission_record_id, resolved_matching_number5)
3163: IN (SELECT transmission_record_id, invoice_number
3164: FROM ar_pmts_interface_line_details line_details
3165: WHERE transmission_request_id = p_trans_req_id
3166: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3167: AND transmission_request_id = p_trans_req_id;
3168:

Line 3173: FROM ar_pmts_interface_line_details line_details

3169: UPDATE ar_payments_interface interface
3170: SET resolved_matching_number6 = NULL
3171: WHERE (transmission_record_id, resolved_matching_number6)
3172: IN (SELECT transmission_record_id, invoice_number
3173: FROM ar_pmts_interface_line_details line_details
3174: WHERE transmission_request_id = p_trans_req_id
3175: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3176: AND transmission_request_id = p_trans_req_id;
3177:

Line 3182: FROM ar_pmts_interface_line_details line_details

3178: UPDATE ar_payments_interface interface
3179: SET resolved_matching_number7 = NULL
3180: WHERE (transmission_record_id, resolved_matching_number7)
3181: IN (SELECT transmission_record_id, invoice_number
3182: FROM ar_pmts_interface_line_details line_details
3183: WHERE transmission_request_id = p_trans_req_id
3184: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3185: AND transmission_request_id = p_trans_req_id;
3186:

Line 3191: FROM ar_pmts_interface_line_details line_details

3187: UPDATE ar_payments_interface interface
3188: SET resolved_matching_number8 = NULL
3189: WHERE (transmission_record_id, resolved_matching_number8)
3190: IN (SELECT transmission_record_id, invoice_number
3191: FROM ar_pmts_interface_line_details line_details
3192: WHERE transmission_request_id = p_trans_req_id
3193: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3194: AND transmission_request_id = p_trans_req_id;
3195: ELSE

Line 3200: FROM ar_pmts_interface_line_details line_details

3196: UPDATE ar_payments_interface interface
3197: SET invoice1_status = 'AR_PLB_INVALID_LINE_DET'
3198: WHERE (transmission_record_id, resolved_matching_number1)
3199: IN (SELECT transmission_record_id, invoice_number
3200: FROM ar_pmts_interface_line_details line_details
3201: WHERE transmission_request_id = p_trans_req_id
3202: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3203: AND transmission_request_id = p_trans_req_id;
3204:

Line 3209: FROM ar_pmts_interface_line_details line_details

3205: UPDATE ar_payments_interface interface
3206: SET invoice2_status = 'AR_PLB_INVALID_LINE_DET'
3207: WHERE (transmission_record_id, resolved_matching_number2)
3208: IN (SELECT transmission_record_id, invoice_number
3209: FROM ar_pmts_interface_line_details line_details
3210: WHERE transmission_request_id = p_trans_req_id
3211: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3212: AND transmission_request_id = p_trans_req_id;
3213:

Line 3218: FROM ar_pmts_interface_line_details line_details

3214: UPDATE ar_payments_interface interface
3215: SET invoice3_status = 'AR_PLB_INVALID_LINE_DET'
3216: WHERE (transmission_record_id, resolved_matching_number3)
3217: IN (SELECT transmission_record_id, invoice_number
3218: FROM ar_pmts_interface_line_details line_details
3219: WHERE transmission_request_id = p_trans_req_id
3220: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3221: AND transmission_request_id = p_trans_req_id;
3222:

Line 3227: FROM ar_pmts_interface_line_details line_details

3223: UPDATE ar_payments_interface interface
3224: SET invoice4_status = 'AR_PLB_INVALID_LINE_DET'
3225: WHERE (transmission_record_id, resolved_matching_number4)
3226: IN (SELECT transmission_record_id, invoice_number
3227: FROM ar_pmts_interface_line_details line_details
3228: WHERE transmission_request_id = p_trans_req_id
3229: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3230: AND transmission_request_id = p_trans_req_id;
3231:

Line 3236: FROM ar_pmts_interface_line_details line_details

3232: UPDATE ar_payments_interface interface
3233: SET invoice5_status = 'AR_PLB_INVALID_LINE_DET'
3234: WHERE (transmission_record_id, resolved_matching_number5)
3235: IN (SELECT transmission_record_id, invoice_number
3236: FROM ar_pmts_interface_line_details line_details
3237: WHERE transmission_request_id = p_trans_req_id
3238: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3239: AND transmission_request_id = p_trans_req_id;
3240:

Line 3245: FROM ar_pmts_interface_line_details line_details

3241: UPDATE ar_payments_interface interface
3242: SET invoice6_status = 'AR_PLB_INVALID_LINE_DET'
3243: WHERE (transmission_record_id, resolved_matching_number6)
3244: IN (SELECT transmission_record_id, invoice_number
3245: FROM ar_pmts_interface_line_details line_details
3246: WHERE transmission_request_id = p_trans_req_id
3247: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3248: AND transmission_request_id = p_trans_req_id;
3249:

Line 3254: FROM ar_pmts_interface_line_details line_details

3250: UPDATE ar_payments_interface interface
3251: SET invoice7_status = 'AR_PLB_INVALID_LINE_DET'
3252: WHERE (transmission_record_id, resolved_matching_number7)
3253: IN (SELECT transmission_record_id, invoice_number
3254: FROM ar_pmts_interface_line_details line_details
3255: WHERE transmission_request_id = p_trans_req_id
3256: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3257: AND transmission_request_id = p_trans_req_id;
3258:

Line 3263: FROM ar_pmts_interface_line_details line_details

3259: UPDATE ar_payments_interface interface
3260: SET invoice8_status = 'AR_PLB_INVALID_LINE_DET'
3261: WHERE (transmission_record_id, resolved_matching_number8)
3262: IN (SELECT transmission_record_id, invoice_number
3263: FROM ar_pmts_interface_line_details line_details
3264: WHERE transmission_request_id = p_trans_req_id
3265: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3266: AND transmission_request_id = p_trans_req_id;
3267:

Line 3397: from ar_pmts_interface_line_details

3393: tax_discount,
3394: freight_discount,
3395: comments,
3396: transmission_record_id
3397: from ar_pmts_interface_line_details
3398: where transmission_request_id = req_id
3399: and invoice_number = inv_num
3400: and transmission_record_id in (
3401: select overflow.transmission_record_id

Line 3453: from ar_pmts_interface_line_details

3449: where cash_receipt_id = p_cash_receipt_id;
3450:
3451: select count(distinct status)
3452: into status_count
3453: from ar_pmts_interface_line_details
3454: where transmission_request_id = p_trans_req_id
3455: and invoice_number = inv_number;
3456:
3457: select count(distinct status)

Line 3459: from ar_pmts_interface_line_details

3455: and invoice_number = inv_number;
3456:
3457: select count(distinct status)
3458: into status_count
3459: from ar_pmts_interface_line_details
3460: where transmission_request_id = p_trans_req_id
3461: and invoice_number = inv_number;
3462:
3463: if status_count = 1 then

Line 3711: delete from ar_pmts_interface_line_details

3707:
3708: END LOOP;
3709: end if;
3710: end if;
3711: delete from ar_pmts_interface_line_details
3712: where transmission_request_id = p_trans_req_id
3713: and invoice_number = inv_number
3714: and transmission_record_id = det_line.transmission_record_id
3715: and status = 'AR_PLB_LINE_OK';