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.

APPS.ARP_PROCESS_LOCKBOX dependencies on AR_PMTS_INTERFACE_LINE_DETAILS

Line 2121: FROM ar_pmts_interface_line_details

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

Line 2127: FROM ar_pmts_interface_line_details

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

Line 2134: FROM ar_pmts_interface_line_details

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

Line 2145: FROM ar_pmts_interface_line_details

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

Line 2201: UPDATE ar_pmts_interface_line_details

2197: BEGIN
2198: debug1('arp_process_lockbox.validate_llca_interface_data()+');
2199: p_trans_req_id := to_number(p_trans_request_id);
2200:
2201: UPDATE ar_pmts_interface_line_details
2202: SET status = 'AR_PLB_NEW_RECORD'
2203: WHERE transmission_request_id = p_trans_req_id;
2204:
2205: UPDATE ar_pmts_interface_line_details

Line 2205: UPDATE ar_pmts_interface_line_details

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

Line 2212: UPDATE ar_pmts_interface_line_details line_details

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

Line 2224: UPDATE ar_pmts_interface_line_details line_details

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

Line 2306: FROM ar_pmts_interface_line_details line_details

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

Line 2343: FROM ar_pmts_interface_line_details line_details

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

Line 2380: FROM ar_pmts_interface_line_details line_details

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

Line 2417: FROM ar_pmts_interface_line_details line_details

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

Line 2454: FROM ar_pmts_interface_line_details line_details

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

Line 2491: FROM ar_pmts_interface_line_details line_details

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

Line 2528: FROM ar_pmts_interface_line_details line_details

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

Line 2565: FROM ar_pmts_interface_line_details line_details

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

Line 2569: UPDATE ar_pmts_interface_line_details line_details

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

Line 2579: UPDATE ar_pmts_interface_line_details line_details

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

Line 2583: FROM ar_pmts_interface_line_details ld

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

Line 2594: UPDATE ar_pmts_interface_line_details

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

Line 2603: /*UPDATE ar_pmts_interface_line_details

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

Line 2618: UPDATE ar_pmts_interface_line_details ld

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

Line 2630: UPDATE ar_pmts_interface_line_details line_details

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

Line 2663: UPDATE ar_pmts_interface_line_details

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

Line 2685: UPDATE ar_pmts_interface_line_details line_details

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

Line 2700: UPDATE ar_pmts_interface_line_details line_details

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

Line 2790: UPDATE ar_pmts_interface_line_details

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

Line 2800: UPDATE ar_pmts_interface_line_details

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

Line 2809: UPDATE ar_pmts_interface_line_details

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

Line 2822: FROM ar_pmts_interface_line_details

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

Line 2855: FROM ar_pmts_interface_line_details

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

Line 2860: UPDATE ar_pmts_interface_line_details

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

Line 2964: UPDATE ar_pmts_interface_line_details

2960: - Nvl(l_calc_line_amount,0))
2961: ,l_inv_currency_code);
2962: End If;
2963:
2964: UPDATE ar_pmts_interface_line_details
2965: SET amount_applied = l_calc_tot_amount_app,
2966: line_amount = l_calc_line_amount,
2967: tax = l_calc_tax_amount
2968: WHERE transmission_record_id = cur_var.transmission_record_id

Line 2976: UPDATE ar_pmts_interface_line_details line_details

2972: END LOOP;
2973: END IF;
2974:
2975: IF l_inv_currency_code <> l_currency_code THEN
2976: UPDATE ar_pmts_interface_line_details line_details
2977: SET allocated_receipt_amount
2978: = ARPCURR.CurrRound( amount_applied * l_trans_to_receipt_rate,
2979: l_inv_currency_code)
2980: WHERE amount_applied IS NOT NULL

Line 3000: FROM ar_pmts_interface_line_details

2996: END IF;
2997:
2998: SELECT SUM(amount_applied), SUM(allocated_receipt_amount)
2999: INTO l_tot_amt_app, l_tot_amt_app_from
3000: FROM ar_pmts_interface_line_details
3001: WHERE transmission_request_id = p_trans_req_id
3002: AND transmission_record_id = cur_var.transmission_record_id
3003: AND invoice_number = l_invoice_number;
3004:

Line 3007: UPDATE ar_pmts_interface_line_details

3003: AND invoice_number = l_invoice_number;
3004:
3005: IF l_default_by = 'AMT_APP_FRM' THEN
3006: arp_util.debug(' Deafult By '||l_default_by);
3007: UPDATE ar_pmts_interface_line_details
3008: SET amount_applied = amount_applied + (hdr_amt_app - l_tot_amt_app )
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 3017: UPDATE ar_pmts_interface_line_details

3013: END IF;
3014:
3015: IF l_default_by = 'AMT_APP' THEN
3016: arp_util.debug(' Deafult By '||l_default_by);
3017: UPDATE ar_pmts_interface_line_details
3018: SET allocated_receipt_amount = allocated_receipt_amount + (hdr_amt_app_frm - l_tot_amt_app_from )
3019: WHERE transmission_request_id = p_trans_req_id
3020: AND transmission_record_id = cur_var.transmission_record_id
3021: AND invoice_number = l_invoice_number

Line 3028: UPDATE ar_pmts_interface_line_details ld

3024:
3025: END LOOP;
3026: END LOOP;
3027: /* At this stage we will have all values calculated with us. So we can go for validation */
3028: UPDATE ar_pmts_interface_line_details ld
3029: SET status = 'AR_PLB_EXCEED_LINE_AMT'
3030: WHERE transmission_request_id = p_trans_req_id
3031: AND (customer_trx_id, apply_to) IN
3032: ( select lines_gt.customer_trx_id, lines_gt.apply_to

Line 3037: from ar_pmts_interface_line_details lines

3033: from ar_pmts_interface_lines_gt ld1,
3034: ( select customer_trx_id,
3035: apply_to,
3036: sum(lines.line_amount) tot_amt
3037: from ar_pmts_interface_line_details lines
3038: where lines.transmission_request_id = p_trans_req_id
3039: group by customer_trx_id, apply_to ) lines_gt
3040: where lines_gt.customer_trx_id = ld1.customer_trx_id
3041: and lines_gt.apply_to = ld1.apply_to

Line 3046: UPDATE ar_pmts_interface_line_details ld

3042: and ld1.apply_to NOT IN ('FREIGHT', 'CHARGES')
3043: and ld1.line_amt_remaining < lines_gt.tot_amt)
3044: AND status = 'AR_PLB_NEW_RECORD';
3045:
3046: UPDATE ar_pmts_interface_line_details ld
3047: SET status = 'AR_PLB_EXCEED_TAX_AMT'
3048: WHERE transmission_request_id = p_trans_req_id
3049: AND (customer_trx_id, apply_to) IN
3050: ( select lines_gt.customer_trx_id, lines_gt.apply_to

Line 3055: from ar_pmts_interface_line_details lines

3051: from ar_pmts_interface_lines_gt ld1,
3052: ( select customer_trx_id,
3053: apply_to,
3054: sum(lines.tax) tot_amt
3055: from ar_pmts_interface_line_details lines
3056: where lines.transmission_request_id = p_trans_req_id
3057: group by customer_trx_id, apply_to ) lines_gt
3058: where lines_gt.customer_trx_id = ld1.customer_trx_id
3059: and lines_gt.apply_to = ld1.apply_to

Line 3064: UPDATE ar_pmts_interface_line_details ld

3060: and ld1.apply_to NOT IN ('FREIGHT', 'CHARGES')
3061: and ld1.tax_remaining < lines_gt.tot_amt)
3062: AND status = 'AR_PLB_NEW_RECORD';
3063:
3064: UPDATE ar_pmts_interface_line_details ld
3065: SET status = 'AR_PLB_EXCEED_FRGT_AMT'
3066: WHERE transmission_request_id = p_trans_req_id
3067: AND (customer_trx_id, apply_to) IN
3068: ( select ld1.customer_trx_id, ld1.apply_to

Line 3070: ar_pmts_interface_line_details ld2

3066: WHERE transmission_request_id = p_trans_req_id
3067: AND (customer_trx_id, apply_to) IN
3068: ( select ld1.customer_trx_id, ld1.apply_to
3069: from ar_pmts_interface_lines_gt ld1,
3070: ar_pmts_interface_line_details ld2
3071: where ld1.customer_trx_id = ld2.customer_trx_id
3072: and ld1.apply_to = ld2.apply_to
3073: and ld2.apply_to = 'FREIGHT'
3074: and ld2.transmission_request_id = p_trans_req_id

Line 3078: UPDATE ar_pmts_interface_line_details ld

3074: and ld2.transmission_request_id = p_trans_req_id
3075: and ld1.freight_remaining < ld2.amount_applied)
3076: AND status = 'AR_PLB_NEW_RECORD';
3077:
3078: UPDATE ar_pmts_interface_line_details ld
3079: SET status = 'AR_PLB_EXCEED_CHRG_AMT'
3080: WHERE transmission_request_id = p_trans_req_id
3081: AND (customer_trx_id, apply_to) IN
3082: ( select ld1.customer_trx_id, ld1.apply_to

Line 3084: ar_pmts_interface_line_details ld2

3080: WHERE transmission_request_id = p_trans_req_id
3081: AND (customer_trx_id, apply_to) IN
3082: ( select ld1.customer_trx_id, ld1.apply_to
3083: from ar_pmts_interface_lines_gt ld1,
3084: ar_pmts_interface_line_details ld2
3085: where ld1.customer_trx_id = ld2.customer_trx_id
3086: and ld1.apply_to = ld2.apply_to
3087: and ld2.transmission_request_id = p_trans_req_id
3088: and ld2.apply_to = 'CHARGES'

Line 3092: UPDATE ar_pmts_interface_line_details line_details

3088: and ld2.apply_to = 'CHARGES'
3089: and ld1.charges_remaining < ld2.amount_applied)
3090: AND status = 'AR_PLB_NEW_RECORD';
3091:
3092: UPDATE ar_pmts_interface_line_details line_details
3093: SET status = 'AR_PLB_AMT_APP_INVALID'
3094: WHERE transmission_request_id = p_trans_req_id
3095: AND (transmission_record_id, customer_trx_id) IN
3096: (select header.transmission_record_id, header.customer_trx_id

Line 3101: from ar_pmts_interface_line_details ld

3097: from ar_pmts_interface_header_gt header,
3098: (select transmission_record_id,
3099: customer_trx_id,
3100: sum(amount_applied) aa
3101: from ar_pmts_interface_line_details ld
3102: where ld.transmission_request_id = p_trans_req_id
3103: group by transmission_record_id, customer_trx_id) line
3104: where header.transmission_record_id = line.transmission_record_id
3105: and header.customer_trx_id = line.customer_trx_id

Line 3110: UPDATE ar_pmts_interface_line_details line_details

3106: and header.transmission_request_id = p_trans_req_id
3107: and header.amount_applied <> line.aa)
3108: AND status = 'AR_PLB_NEW_RECORD';
3109:
3110: UPDATE ar_pmts_interface_line_details line_details
3111: SET status = 'AR_PLB_AMT_APP_FRM_INVALID'
3112: WHERE transmission_request_id = p_trans_req_id
3113: AND (transmission_record_id, customer_trx_id) IN
3114: (select header.transmission_record_id, header.customer_trx_id

Line 3119: from ar_pmts_interface_line_details ld

3115: from ar_pmts_interface_header_gt header,
3116: (select transmission_record_id,
3117: customer_trx_id,
3118: sum(allocated_receipt_amount) aa
3119: from ar_pmts_interface_line_details ld
3120: where ld.transmission_request_id = p_trans_req_id
3121: group by transmission_record_id, customer_trx_id) line
3122: where header.transmission_record_id = line.transmission_record_id
3123: and header.customer_trx_id = line.customer_trx_id

Line 3128: UPDATE ar_pmts_interface_line_details

3124: and header.transmission_request_id = p_trans_req_id
3125: and header.amount_applied_from <> line.aa)
3126: AND status = 'AR_PLB_NEW_RECORD';
3127:
3128: UPDATE ar_pmts_interface_line_details
3129: SET status = 'AR_PLB_LINE_OK'
3130: WHERE status = 'AR_PLB_NEW_RECORD'
3131: AND transmission_request_id = p_trans_req_id;
3132:

Line 3138: FROM ar_pmts_interface_line_details line_details

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

Line 3147: FROM ar_pmts_interface_line_details line_details

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

Line 3156: FROM ar_pmts_interface_line_details line_details

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

Line 3165: FROM ar_pmts_interface_line_details line_details

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

Line 3174: FROM ar_pmts_interface_line_details line_details

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

Line 3183: FROM ar_pmts_interface_line_details line_details

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

Line 3192: FROM ar_pmts_interface_line_details line_details

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

Line 3201: FROM ar_pmts_interface_line_details line_details

3197: UPDATE ar_payments_interface interface
3198: SET resolved_matching_number8 = NULL
3199: WHERE (transmission_record_id, resolved_matching_number8)
3200: IN (SELECT transmission_record_id, invoice_number
3201: FROM ar_pmts_interface_line_details line_details
3202: WHERE transmission_request_id = p_trans_req_id
3203: AND status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3204: AND transmission_request_id = p_trans_req_id;
3205: ELSE

Line 3210: FROM ar_pmts_interface_line_details line_details

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

Line 3219: FROM ar_pmts_interface_line_details line_details

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

Line 3228: FROM ar_pmts_interface_line_details line_details

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

Line 3237: FROM ar_pmts_interface_line_details line_details

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

Line 3246: FROM ar_pmts_interface_line_details line_details

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

Line 3255: FROM ar_pmts_interface_line_details line_details

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

Line 3264: FROM ar_pmts_interface_line_details line_details

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

Line 3273: FROM ar_pmts_interface_line_details line_details

3269: UPDATE ar_payments_interface interface
3270: SET invoice8_status = 'AR_PLB_INVALID_LINE_DET'
3271: WHERE (transmission_record_id, resolved_matching_number8)
3272: IN (SELECT transmission_record_id, invoice_number
3273: FROM ar_pmts_interface_line_details line_details
3274: WHERE transmission_request_id = p_trans_req_id
3275: AND line_details.status NOT IN ('AR_PLB_INVALID_RECORD', 'AR_PLB_LINE_OK'))
3276: AND transmission_request_id = p_trans_req_id;
3277:

Line 3425: from ar_pmts_interface_line_details

3421: tax_discount,
3422: freight_discount,
3423: comments,
3424: transmission_record_id
3425: from ar_pmts_interface_line_details
3426: where transmission_request_id = req_id
3427: and invoice_number = inv_num
3428: and transmission_record_id in (
3429: select overflow.transmission_record_id

Line 3472: from ar_pmts_interface_line_details

3468: where cash_receipt_id = p_cash_receipt_id;
3469:
3470: select count(distinct status)
3471: into status_count
3472: from ar_pmts_interface_line_details
3473: where transmission_request_id = p_trans_req_id
3474: and invoice_number = inv_number;
3475:
3476: select count(distinct status)

Line 3478: from ar_pmts_interface_line_details

3474: and invoice_number = inv_number;
3475:
3476: select count(distinct status)
3477: into status_count
3478: from ar_pmts_interface_line_details
3479: where transmission_request_id = p_trans_req_id
3480: and invoice_number = inv_number;
3481:
3482: if status_count = 1 then

Line 3730: delete from ar_pmts_interface_line_details

3726:
3727: END LOOP;
3728: end if;
3729: end if;
3730: delete from ar_pmts_interface_line_details
3731: where transmission_request_id = p_trans_req_id
3732: and invoice_number = inv_number
3733: and transmission_record_id = det_line.transmission_record_id
3734: and status = 'AR_PLB_LINE_OK';