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.PN_VAR_RENT_CALC_PKG dependencies on PN_VAR_RENT_INV_ALL

Line 1790: pn_var_rent_inv_all

1786: ,actual_invoiced_amount
1787: ,constr_actual_rent
1788: ,rec_abatement_override
1789: FROM
1790: pn_var_rent_inv_all
1791: WHERE
1792: var_rent_id = p_vr_id AND
1793: period_id = p_prd_id AND
1794: invoice_date = p_inv_dt AND

Line 1807: pn_var_rent_inv_all

1803: ,p_inv_dt IN DATE) IS
1804: SELECT
1805: NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
1806: FROM
1807: pn_var_rent_inv_all
1808: WHERE
1809: var_rent_id = p_vr_id AND
1810: period_id = p_prd_id AND
1811: invoice_date = p_inv_dt AND

Line 2069: pn_var_rent_inv_all

2065: var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_ACTUAL;
2066:
2067: /* update the invoice */
2068: UPDATE
2069: pn_var_rent_inv_all
2070: SET
2071: act_per_rent = ROUND(inv_rec.total_actual_rent, g_precision)
2072: ,constr_actual_rent = ROUND(l_constr_rent, g_precision)
2073: ,actual_invoiced_amount = ROUND((l_constr_rent - l_prev_inv_exp_rent), g_precision)

Line 2149: pn_var_rent_inv_all

2145: ,actual_invoiced_amount
2146: ,constr_actual_rent
2147: ,rec_abatement_override
2148: FROM
2149: pn_var_rent_inv_all
2150: WHERE
2151: var_rent_id = p_vr_id AND
2152: period_id = p_prd_id AND
2153: invoice_date = p_inv_dt AND

Line 2166: pn_var_rent_inv_all

2162: ,p_inv_dt IN DATE) IS
2163: SELECT
2164: NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
2165: FROM
2166: pn_var_rent_inv_all
2167: WHERE
2168: var_rent_id = p_vr_id AND
2169: period_id = p_prd_id AND
2170: invoice_date = p_inv_dt AND

Line 2476: pn_var_rent_inv_all

2472: var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_ACTUAL;
2473:
2474: /* update the invoice */
2475: UPDATE
2476: pn_var_rent_inv_all
2477: SET
2478: act_per_rent = ROUND(inv_rec.total_actual_rent, g_precision)
2479: ,constr_actual_rent = ROUND(l_constr_rent, g_precision)
2480: ,actual_invoiced_amount = ROUND((l_constr_rent - l_prev_inv_exp_rent), g_precision)

Line 2671: FROM pn_var_rent_inv_all inv1

2667: p_new_termn_date DATE
2668: ) IS
2669: SELECT var_rent_inv_id, invoice_date, variance_exp_code,
2670: actual_exp_code, constr_actual_rent
2671: FROM pn_var_rent_inv_all inv1
2672: WHERE inv1.var_rent_id = ip_var_rent_id
2673: AND inv1.period_id = ip_period_id
2674: AND inv1.invoice_date <= p_new_termn_date
2675: AND inv1.adjust_num= (

Line 2676: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2

2672: WHERE inv1.var_rent_id = ip_var_rent_id
2673: AND inv1.period_id = ip_period_id
2674: AND inv1.invoice_date <= p_new_termn_date
2675: AND inv1.adjust_num= (
2676: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
2677: where inv1.var_rent_id = inv2.var_rent_id
2678: AND inv1.invoice_date = inv2.invoice_date)
2679: ORDER BY invoice_date;
2680:

Line 2685: FROM pn_var_rent_inv_all

2681: -- Get all the details of a specific invoice.
2682: CURSOR inv_all_c(ip_vr_inv_id NUMBER
2683: ) IS
2684: SELECT *
2685: FROM pn_var_rent_inv_all
2686: WHERE var_rent_inv_id = ip_vr_inv_id;
2687:
2688:
2689: /* get prev invoiced amount. */

Line 2696: pn_var_rent_inv_all

2692: ,p_inv_dt IN DATE) IS
2693: SELECT
2694: NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
2695: FROM
2696: pn_var_rent_inv_all
2697: WHERE
2698: var_rent_id = p_vr_id AND
2699: period_id = p_prd_id AND
2700: invoice_date = p_inv_dt AND

Line 2716: pn_var_rent_inv_all inv

2712: G_INV_ON_FORECASTED, variance_exp_code) AS
2713: exp_code,
2714: inv.actual_invoiced_amount
2715: FROM pn_var_rents_all vr,
2716: pn_var_rent_inv_all inv
2717: WHERE vr.var_rent_id = inv.var_rent_id
2718: AND inv.var_rent_inv_id = ip_var_rent_inv_id;
2719:
2720: l_is_inv_exp VARCHAR(30);

Line 2797: pn_var_rent_inv_all

2793:
2794: -- Update the current invoice
2795: pnp_debug_pkg.log('Abatements - updating');
2796: UPDATE
2797: pn_var_rent_inv_all
2798: SET
2799: actual_invoiced_amount = ROUND((l_abated_rent - l_prev_inv_exp), g_precision)
2800: ,actual_term_status = 'N'
2801: ,last_update_date = SYSDATE

Line 2899: FROM pn_var_rent_inv_all inv1

2895: ,actual_invoiced_amount
2896: ,negative_rent
2897: ,invoice_date
2898: ,var_rent_inv_id
2899: FROM pn_var_rent_inv_all inv1
2900: WHERE inv1.var_rent_id = ip_var_rent_id
2901: AND inv1.period_id = ip_period_id
2902: AND var_rent_inv_id = ip_inv_id
2903: AND inv1.adjust_num= (

Line 2904: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2

2900: WHERE inv1.var_rent_id = ip_var_rent_id
2901: AND inv1.period_id = ip_period_id
2902: AND var_rent_inv_id = ip_inv_id
2903: AND inv1.adjust_num= (
2904: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
2905: where inv1.var_rent_id = inv2.var_rent_id
2906: AND inv1.invoice_date = inv2.invoice_date);
2907:
2908: CURSOR csr_last_inv(p_var_rent_id NUMBER)

Line 2917: FROM pn_var_rent_inv_all inv

2913:
2914: CURSOR csr_neg_avail (ip_var_rent_id NUMBER,
2915: ip_invoice_date DATE) IS
2916: SELECT ABS(NVL(SUM(constr_actual_rent),0)) negative_available
2917: FROM pn_var_rent_inv_all inv
2918: WHERE inv.var_rent_id = ip_var_rent_id
2919: AND inv.invoice_date < ip_invoice_date
2920: AND inv.adjust_num = (select MAX(inv1.adjust_num)
2921: from pn_var_rent_inv_all inv1

Line 2921: from pn_var_rent_inv_all inv1

2917: FROM pn_var_rent_inv_all inv
2918: WHERE inv.var_rent_id = ip_var_rent_id
2919: AND inv.invoice_date < ip_invoice_date
2920: AND inv.adjust_num = (select MAX(inv1.adjust_num)
2921: from pn_var_rent_inv_all inv1
2922: where inv1.var_rent_id = inv.var_rent_id
2923: AND inv1.invoice_date = inv.invoice_date)
2924: AND inv.constr_actual_rent < 0;
2925:

Line 2929: FROM pn_var_rent_inv_all inv

2925:
2926: CURSOR csr_neg_appl (ip_var_rent_id NUMBER,
2927: ip_invoice_date DATE) IS
2928: SELECT NVL(SUM(negative_rent),0) negative_applied
2929: FROM pn_var_rent_inv_all inv
2930: WHERE inv.var_rent_id = ip_var_rent_id
2931: AND inv.invoice_date < ip_invoice_date
2932: AND inv.adjust_num = (select MAX(inv1.adjust_num)
2933: from pn_var_rent_inv_all inv1

Line 2933: from pn_var_rent_inv_all inv1

2929: FROM pn_var_rent_inv_all inv
2930: WHERE inv.var_rent_id = ip_var_rent_id
2931: AND inv.invoice_date < ip_invoice_date
2932: AND inv.adjust_num = (select MAX(inv1.adjust_num)
2933: from pn_var_rent_inv_all inv1
2934: where inv1.var_rent_id = inv.var_rent_id
2935: AND inv1.invoice_date = inv.invoice_date);
2936: CURSOR csr_get_abt(ip_var_rent_id NUMBER)
2937: IS

Line 2946: FROM pn_var_rent_inv_all

2942: CURSOR temp_c(ip_var_rent_inv_id NUMBER
2943: ) IS
2944: SELECT abated_rent,
2945: negative_rent
2946: FROM pn_var_rent_inv_all
2947: WHERE var_rent_inv_id = ip_var_rent_inv_id;
2948:
2949:
2950: l_negative_rent pn_var_rent_inv.negative_rent%TYPE := 0;

Line 3015: UPDATE pn_var_rent_inv_all

3011: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3012: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
3013: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3014:
3015: UPDATE pn_var_rent_inv_all
3016: SET abated_rent = l_abated_rent,
3017: negative_rent = L_negative_rent
3018: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3019: END LOOP;

Line 3057: FROM pn_var_rent_inv_all inv1

3053: ,actual_invoiced_amount
3054: ,negative_rent
3055: ,invoice_date
3056: ,var_rent_inv_id
3057: FROM pn_var_rent_inv_all inv1
3058: WHERE inv1.var_rent_id = ip_var_rent_id
3059: AND inv1.period_id = ip_period_id
3060: AND var_rent_inv_id = ip_inv_id
3061: AND inv1.adjust_num= (

Line 3062: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2

3058: WHERE inv1.var_rent_id = ip_var_rent_id
3059: AND inv1.period_id = ip_period_id
3060: AND var_rent_inv_id = ip_inv_id
3061: AND inv1.adjust_num= (
3062: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
3063: where inv1.var_rent_id = inv2.var_rent_id
3064: AND inv1.invoice_date = inv2.invoice_date);
3065:
3066: CURSOR csr_last_inv(p_var_rent_id NUMBER)

Line 3080: FROM pn_var_rent_inv_all inv

3076:
3077: CURSOR csr_neg_avail (ip_var_rent_id NUMBER,
3078: ip_invoice_date DATE) IS
3079: SELECT ABS(NVL(SUM(def_neg_rent),0)) negative_available
3080: FROM pn_var_rent_inv_all inv
3081: WHERE inv.var_rent_id = ip_var_rent_id
3082: AND inv.invoice_date < ip_invoice_date
3083: AND inv.adjust_num = (select MAX(inv1.adjust_num)
3084: from pn_var_rent_inv_all inv1

Line 3084: from pn_var_rent_inv_all inv1

3080: FROM pn_var_rent_inv_all inv
3081: WHERE inv.var_rent_id = ip_var_rent_id
3082: AND inv.invoice_date < ip_invoice_date
3083: AND inv.adjust_num = (select MAX(inv1.adjust_num)
3084: from pn_var_rent_inv_all inv1
3085: where inv1.var_rent_id = inv.var_rent_id
3086: AND inv1.invoice_date = inv.invoice_date);
3087:
3088: CURSOR csr_neg_appl (ip_var_rent_id NUMBER,

Line 3091: FROM pn_var_rent_inv_all inv

3087:
3088: CURSOR csr_neg_appl (ip_var_rent_id NUMBER,
3089: ip_invoice_date DATE) IS
3090: SELECT NVL(SUM(negative_rent),0) negative_applied
3091: FROM pn_var_rent_inv_all inv
3092: WHERE inv.var_rent_id = ip_var_rent_id
3093: AND inv.invoice_date < ip_invoice_date
3094: AND inv.adjust_num = (select MAX(inv1.adjust_num)
3095: from pn_var_rent_inv_all inv1

Line 3095: from pn_var_rent_inv_all inv1

3091: FROM pn_var_rent_inv_all inv
3092: WHERE inv.var_rent_id = ip_var_rent_id
3093: AND inv.invoice_date < ip_invoice_date
3094: AND inv.adjust_num = (select MAX(inv1.adjust_num)
3095: from pn_var_rent_inv_all inv1
3096: where inv1.var_rent_id = inv.var_rent_id
3097: AND inv1.invoice_date = inv.invoice_date);
3098: CURSOR csr_get_abt(ip_var_rent_id NUMBER)
3099: IS

Line 3167: UPDATE pn_var_rent_inv_all

3163: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3164: pnp_debug_pkg.log('l_negative_rent:'||l_negative_rent);
3165: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);
3166:
3167: UPDATE pn_var_rent_inv_all
3168: SET negative_rent = ROUND(l_negative_rent, g_precision)
3169: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3170: x_abated_rent := l_abated_rent;
3171: END LOOP;

Line 3204: FROM pn_var_rent_inv_all inv1

3200: CURSOR invoices_c(ip_var_rent_id NUMBER, ip_period_id NUMBER, ip_inv_id NUMBER
3201: ) IS
3202: SELECT invoice_date
3203: ,var_rent_inv_id
3204: FROM pn_var_rent_inv_all inv1
3205: WHERE inv1.var_rent_id = ip_var_rent_id
3206: AND inv1.period_id = ip_period_id
3207: AND var_rent_inv_id = ip_inv_id
3208: AND inv1.adjust_num= (

Line 3209: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2

3205: WHERE inv1.var_rent_id = ip_var_rent_id
3206: AND inv1.period_id = ip_period_id
3207: AND var_rent_inv_id = ip_inv_id
3208: AND inv1.adjust_num= (
3209: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
3210: where inv1.var_rent_id = inv2.var_rent_id
3211: AND inv1.invoice_date = inv2.invoice_date);
3212:
3213: CURSOR csr_last_inv(p_var_rent_id NUMBER)

Line 3268: UPDATE pn_var_rent_inv_all

3264: l_abated_rent := x_abated_rent;
3265: END IF;
3266: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3267: pnp_debug_pkg.log('l_neg_rent_def*:'||l_neg_rent_def);
3268: UPDATE pn_var_rent_inv_all
3269: SET def_neg_rent = ROUND(l_neg_rent_def, g_precision)
3270: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3271: x_abated_rent := l_abated_rent;
3272: END LOOP;

Line 3311: FROM pn_var_rent_inv_all inv1

3307: ,invoice_date
3308: ,NVL(rec_abatement,0) rec_abatement
3309: ,rec_abatement_override
3310: ,var_rent_inv_id
3311: FROM pn_var_rent_inv_all inv1
3312: WHERE inv1.var_rent_id = ip_var_rent_id
3313: AND inv1.period_id = ip_period_id
3314: AND inv1.var_rent_inv_id = ip_inv_id
3315: AND inv1.adjust_num= (

Line 3316: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2

3312: WHERE inv1.var_rent_id = ip_var_rent_id
3313: AND inv1.period_id = ip_period_id
3314: AND inv1.var_rent_inv_id = ip_inv_id
3315: AND inv1.adjust_num= (
3316: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
3317: where inv1.var_rent_id = inv2.var_rent_id
3318: AND inv1.invoice_date = inv2.invoice_date);
3319: -- Get the details of fixed abatements
3320: CURSOR fixed_abat_c(ip_var_rent_id NUMBER,

Line 3346: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3342: -- Get the details of actual start and end date for FY/LY/FLY
3343: CURSOR invoice_dates_fyly_c(ip_var_rent_inv_id NUMBER
3344: ) IS
3345: SELECT per.start_date, per.end_date
3346: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3347: WHERE per.period_id = inv.period_id
3348: AND inv.var_rent_inv_id = ip_var_rent_inv_id;
3349:
3350: -- Get the id of invoice created for first year.

Line 3355: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3351: -- This has meaning only if proration is FY/FLY
3352: CURSOR get_fy_inv_c(ip_var_rent_id NUMBER
3353: ) IS
3354: SELECT inv.var_rent_inv_id
3355: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3356: WHERE per.period_id = inv.period_id
3357: AND inv.var_rent_id = ip_var_rent_id
3358: AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
3359: WHERE var_rent_id = ip_var_rent_id);

Line 3365: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3361: -- This has meaning only if proration is LY/FLY
3362: CURSOR get_ly_inv_c(ip_var_rent_id NUMBER
3363: ) IS
3364: SELECT inv.var_rent_inv_id
3365: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3366: WHERE per.period_id = inv.period_id
3367: AND inv.var_rent_id = ip_var_rent_id
3368: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
3369: WHERE var_rent_id = ip_var_rent_id)

Line 3370: AND inv.invoice_date = (SELECT MAX(invoice_date) FROM pn_var_rent_inv_all

3366: WHERE per.period_id = inv.period_id
3367: AND inv.var_rent_id = ip_var_rent_id
3368: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
3369: WHERE var_rent_id = ip_var_rent_id)
3370: AND inv.invoice_date = (SELECT MAX(invoice_date) FROM pn_var_rent_inv_all
3371: WHERE var_rent_id = ip_var_rent_id);
3372:
3373: -- Get the details of negative_rent
3374: CURSOR neg_rent_c(ip_var_rent_id NUMBER

Line 3390: FROM pn_var_rent_inv_all inv, pn_var_periods_all per, pn_var_rents_all vr, pn_var_rent_dates_all vrd

3386: -- Get the number of inv which should exist for a period
3387: CURSOR num_inv_c(ip_var_rent_inv_id NUMBER
3388: ) IS
3389: SELECT ceil(months_between(per.end_date, per.start_date)/decode(vrd.invg_freq_code,'YR', 12, 'SA', 6, 'QTR', 3, 'MON', 1, 1)) num_inv
3390: FROM pn_var_rent_inv_all inv, pn_var_periods_all per, pn_var_rents_all vr, pn_var_rent_dates_all vrd
3391: WHERE per.period_id = inv.period_id
3392: AND inv.var_rent_inv_id = ip_var_rent_inv_id
3393: AND per.var_rent_id = vr.var_rent_id
3394: AND vrd.var_rent_id = vr.var_rent_id;

Line 3404: pn_var_rent_inv_all inv

3400: inv.var_rent_inv_id,
3401: inv.period_id
3402: FROM pn_var_rents_all vr,
3403: pn_var_periods_all per,
3404: pn_var_rent_inv_all inv
3405: WHERE per.var_rent_id = vr.var_rent_id
3406: AND inv.period_id = per.period_id
3407: AND per.start_date = (select max(start_date) from pn_var_periods_all per1
3408: where per1.var_rent_id = vr.var_rent_id)

Line 3409: AND inv.invoice_date = (select max(invoice_date) from pn_var_rent_inv_all inv1

3405: WHERE per.var_rent_id = vr.var_rent_id
3406: AND inv.period_id = per.period_id
3407: AND per.start_date = (select max(start_date) from pn_var_periods_all per1
3408: where per1.var_rent_id = vr.var_rent_id)
3409: AND inv.invoice_date = (select max(invoice_date) from pn_var_rent_inv_all inv1
3410: where inv1.period_id = per.period_id)
3411: AND vr.var_rent_id = ip_vr_id;
3412:
3413: l_fixed_abat NUMBER := 0;

Line 3534: UPDATE pn_var_rent_inv_all

3530: pnp_debug_pkg.log('l_abated_rent:'||l_abated_rent);
3531: x_abated_rent := l_abated_rent;
3532: l_total_abat := l_unabated_rent - l_abated_rent;
3533: pnp_debug_pkg.log('total_abat_applied:'||l_total_abat);
3534: UPDATE pn_var_rent_inv_all
3535: SET rec_abatement = ROUND(l_total_abat, g_precision)
3536: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3537:
3538: END LOOP;

Line 3575: FROM pn_var_rent_inv_all inv1

3571: ) IS
3572: SELECT inv1.abatement_appl
3573: ,inv1.invoice_date
3574: ,inv1.var_rent_inv_id
3575: FROM pn_var_rent_inv_all inv1
3576: WHERE var_rent_id = ip_var_rent_id
3577: AND period_id = ip_period_id
3578: AND var_rent_inv_id = ip_inv_id
3579: AND inv1.adjust_num= (

Line 3580: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2

3576: WHERE var_rent_id = ip_var_rent_id
3577: AND period_id = ip_period_id
3578: AND var_rent_inv_id = ip_inv_id
3579: AND inv1.adjust_num= (
3580: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
3581: where inv1.var_rent_id = inv2.var_rent_id
3582: AND inv1.invoice_date = inv2.invoice_date);
3583: -- Get the details of rolling allowance
3584: CURSOR rolling_allow_c(ip_var_rent_id NUMBER,

Line 3608: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3604: -- Get the details of actual start and end date for FY/LY/FLY
3605: CURSOR invoice_dates_fyly_c(ip_var_rent_inv_id NUMBER
3606: ) IS
3607: SELECT per.start_date, per.end_date
3608: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3609: WHERE per.period_id = inv.period_id
3610: AND inv.var_rent_inv_id = ip_var_rent_inv_id;
3611: -- Get the id of invoice created for first year.
3612: -- This has meaning only if proration is FY/FLY

Line 3616: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3612: -- This has meaning only if proration is FY/FLY
3613: CURSOR get_fy_inv_c(ip_var_rent_id NUMBER
3614: ) IS
3615: SELECT inv.var_rent_inv_id
3616: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3617: WHERE per.period_id = inv.period_id
3618: AND inv.var_rent_id = ip_var_rent_id
3619: AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
3620: WHERE var_rent_id = ip_var_rent_id);

Line 3626: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3622: -- This has meaning only if proration is LY/FLY
3623: CURSOR get_ly_inv_c(ip_var_rent_id NUMBER
3624: ) IS
3625: SELECT inv.var_rent_inv_id
3626: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3627: WHERE per.period_id = inv.period_id
3628: AND inv.var_rent_id = ip_var_rent_id
3629: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
3630: WHERE var_rent_id = ip_var_rent_id)

Line 3631: AND inv.invoice_date = (SELECT MAX(invoice_date) from pn_var_rent_inv_all

3627: WHERE per.period_id = inv.period_id
3628: AND inv.var_rent_id = ip_var_rent_id
3629: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
3630: WHERE var_rent_id = ip_var_rent_id)
3631: AND inv.invoice_date = (SELECT MAX(invoice_date) from pn_var_rent_inv_all
3632: WHERE var_rent_id = ip_var_rent_id);
3633:
3634: -- Get the proration type
3635: CURSOR proration_type_c(ip_var_rent_id NUMBER

Line 3717: UPDATE pn_var_rent_inv_all

3713: WHERE abatement_id = allow_rec.abatement_id;
3714: END IF;
3715: END LOOP;
3716: pnp_debug_pkg.log('l_cur_abt_rent:'||l_cur_abt_rent);
3717: UPDATE pn_var_rent_inv_all
3718: SET abatement_appl = ROUND(l_allow_applied_inv, g_precision)
3719: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3720: x_abated_rent := l_cur_abt_rent;
3721: pnp_debug_pkg.log('inv_rec.var_rent_inv_id:'||inv_rec.var_rent_inv_id);

Line 3733: -- pn_var_rent_inv_all.rec_abatement.

3729:
3730: --------------------------------------------------------------------------------
3731: -- NAME : populate_abat
3732: -- DESCRIPTION : Populates the recurring abatements to abte in
3733: -- pn_var_rent_inv_all.rec_abatement.
3734: -- PURPOSE :
3735: -- INVOKED FROM : apply_abatements()
3736: -- ARGUMENTS : p_var_rent_id: Vr to apply abatements for.
3737: -- p_period_id: Period to calculate for.

Line 3754: FROM pn_var_rent_inv_all inv1

3750: ip_inv_id NUMBER
3751: ) IS
3752: SELECT invoice_date
3753: ,var_rent_inv_id
3754: FROM pn_var_rent_inv_all inv1
3755: WHERE inv1.var_rent_id = ip_var_rent_id
3756: AND inv1.period_id = ip_period_id
3757: AND var_rent_inv_id = ip_inv_id
3758: AND inv1.adjust_num= (

Line 3759: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2

3755: WHERE inv1.var_rent_id = ip_var_rent_id
3756: AND inv1.period_id = ip_period_id
3757: AND var_rent_inv_id = ip_inv_id
3758: AND inv1.adjust_num= (
3759: SELECT MAX(adjust_num) from pn_var_rent_inv_all inv2
3760: where inv1.var_rent_id = inv2.var_rent_id
3761: AND inv1.invoice_date = inv2.invoice_date);
3762: -- Get the details of actual start and end date for FY/LY/FLY
3763: CURSOR invoice_dates_fyly_c(ip_var_rent_inv_id NUMBER

Line 3766: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3762: -- Get the details of actual start and end date for FY/LY/FLY
3763: CURSOR invoice_dates_fyly_c(ip_var_rent_inv_id NUMBER
3764: ) IS
3765: SELECT per.start_date, per.end_date
3766: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3767: WHERE per.period_id = inv.period_id
3768: AND inv.var_rent_inv_id = ip_var_rent_inv_id;
3769: -- Get the id of invoice created for first year.
3770: -- This has meaning only if proration is FY/FLY

Line 3774: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3770: -- This has meaning only if proration is FY/FLY
3771: CURSOR get_fy_inv_c(ip_var_rent_id NUMBER
3772: ) IS
3773: SELECT inv.var_rent_inv_id
3774: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3775: WHERE per.period_id = inv.period_id
3776: AND inv.var_rent_id = ip_var_rent_id
3777: AND per.start_date = (SELECT MIN(start_date) from pn_var_periods_all
3778: WHERE var_rent_id = ip_var_rent_id);

Line 3784: FROM pn_var_rent_inv_all inv, pn_var_periods_all per

3780: -- This has meaning only if proration is LY/FLY
3781: CURSOR get_ly_inv_c(ip_var_rent_id NUMBER
3782: ) IS
3783: SELECT inv.var_rent_inv_id
3784: FROM pn_var_rent_inv_all inv, pn_var_periods_all per
3785: WHERE per.period_id = inv.period_id
3786: AND inv.var_rent_id = ip_var_rent_id
3787: AND per.start_date = (SELECT MAX(start_date) from pn_var_periods_all
3788: WHERE var_rent_id = ip_var_rent_id);

Line 3849: UPDATE pn_var_rent_inv_all

3845: inv_rec.var_rent_inv_id
3846: ,l_min_grp_date
3847: ,l_max_grp_date);
3848: g_precision := nvl(pn_var_rent_calc_pkg.get_currency_precision(g_org_id),4);
3849: UPDATE pn_var_rent_inv_all
3850: SET rec_abatement = ROUND(l_rec_abatement, g_precision)
3851: WHERE var_rent_inv_id = inv_rec.var_rent_inv_id;
3852:
3853: END LOOP;

Line 3884: UPDATE pn_var_rent_inv_all

3880: pnp_debug_pkg.log('Reset_abatement start(+)');
3881: UPDATE pn_var_abat_defaults_all
3882: SET allowance_applied =0
3883: WHERE var_rent_id = p_var_rent_id;
3884: UPDATE pn_var_rent_inv_all
3885: SET abatement_appl =0
3886: WHERE var_rent_id = p_var_rent_id;
3887: pnp_debug_pkg.log('Reset_abatement end(-)');
3888:

Line 4593: from pn_var_rent_inv_all

4589: END IF;
4590:
4591: select nvl(sum(actual_invoiced_amount),0)
4592: into l_actual_amount
4593: from pn_var_rent_inv_all
4594: where period_id = l_periods_t(p).period_id
4595: and var_rent_id = p_var_rent_id;
4596:
4597: fnd_message.set_name ('PN','PN_VRAM_RENT');

Line 4693: pn_var_rent_inv_all

4689: ,actual_invoiced_amount
4690: ,constr_actual_rent
4691: ,rec_abatement_override
4692: FROM
4693: pn_var_rent_inv_all
4694: WHERE
4695: var_rent_id = p_vr_id AND
4696: period_id = p_prd_id AND
4697: invoice_date = p_inv_dt

Line 4711: pn_var_rent_inv_all

4707: ,p_inv_dt IN DATE) IS
4708: SELECT
4709: NVL(SUM(actual_invoiced_amount), 0) AS prev_invoiced_amt
4710: FROM
4711: pn_var_rent_inv_all
4712: WHERE
4713: var_rent_id = p_vr_id AND
4714: period_id = p_prd_id AND
4715: invoice_date = p_inv_dt AND

Line 4910: pn_var_rent_inv_all

4906:
4907: /* update the invoice */
4908: /*Since forcasted rents are not yet exported, we can update them.*/
4909: UPDATE
4910: pn_var_rent_inv_all
4911: SET
4912: for_per_rent = ROUND(inv_rec.total_forecasted_rent, g_precision)
4913: ,forecasted_term_status = 'N'
4914: ,credit_flag = 'N' -- bug # 5937807

Line 5062: pn_var_rent_inv_all

5058: --var_rent_type = pn_var_rent_calc_pkg.G_INV_ON_FORECASTED;
5059:
5060: /* update the invoice */
5061: UPDATE
5062: pn_var_rent_inv_all
5063: SET
5064: act_per_rent = ROUND(inv_rec.total_actual_rent, g_precision)
5065: ,constr_actual_rent = ROUND(l_constr_rent, g_precision)
5066: ,actual_invoiced_amount = ROUND((l_constr_rent - l_prev_inv_exp_rent), g_precision)

Line 6690: FROM pn_var_rent_inv_all

6686: RETURN NUMBER IS
6687:
6688: CURSOR inv_cur IS
6689: SELECT true_up_amt, actual_invoiced_amount, act_per_rent, for_per_rent, adjust_num
6690: FROM pn_var_rent_inv_all
6691: WHERE var_rent_inv_id = p_var_rent_inv_id;
6692:
6693: CURSOR inv_actual IS
6694: SELECT SUM(actual_invoiced_amount) actual

Line 6695: FROM pn_var_rent_inv_all

6691: WHERE var_rent_inv_id = p_var_rent_inv_id;
6692:
6693: CURSOR inv_actual IS
6694: SELECT SUM(actual_invoiced_amount) actual
6695: FROM pn_var_rent_inv_all
6696: WHERE invoice_date = p_invoice_date
6697: AND period_id = p_period_id
6698: AND var_rent_inv_id <= p_var_rent_inv_id;
6699:

Line 6702: FROM pn_var_rent_inv_all rent_inv

6698: AND var_rent_inv_id <= p_var_rent_inv_id;
6699:
6700: CURSOR prev_inv_amt_cur(p_adjust_num NUMBER) IS
6701: SELECT sum(actual_invoiced_amount) prev_amt
6702: FROM pn_var_rent_inv_all rent_inv
6703: WHERE var_rent_inv_id < p_var_rent_inv_id
6704: AND (true_up_amt IS NULL
6705: OR (true_up_amt IS NOT NULL))
6706: AND period_id = p_period_id;

Line 8851: NOT EXISTS (SELECT NULL from pn_var_rent_inv_all vinv

8847: AND vol.period_id = gd.period_id
8848: AND vol.invoicing_date= gd.invoice_date
8849: )
8850: AND
8851: NOT EXISTS (SELECT NULL from pn_var_rent_inv_all vinv
8852: WHERE vinv.invoice_date=gd.invoice_date
8853: AND vinv.period_id=gd.period_id
8854: )
8855: AND gd.var_rent_id=p_vrent_id

Line 8899: AND NOT EXISTS (select NULL from pn_var_rent_inv_all vinv

8895: where vol_hist_status_code = p_status
8896: and vol.period_id = vp.period_id
8897: and var_rent_id=p_vrent_id
8898: )
8899: AND NOT EXISTS (select NULL from pn_var_rent_inv_all vinv
8900: where vinv.var_rent_id = p_vrent_id
8901: and vinv.period_id = vp.period_id
8902: )
8903: );

Line 8918: AND NOT EXISTS (select NULL from pn_var_rent_inv_all vinv

8914: where vol_hist_status_code = p_status
8915: and vol.period_id = gd.period_id
8916: and vol.invoicing_date= gd.invoice_date
8917: )
8918: AND NOT EXISTS (select NULL from pn_var_rent_inv_all vinv
8919: where vinv.invoice_date=gd.invoice_date
8920: and vinv.period_id=gd.period_id
8921: )
8922: AND gd.var_rent_id=p_vrent_id

Line 8984: pn_var_rent_inv_all vinv

8980: CURSOR get_all_pmt_terms(p_inv_id IN NUMBER) IS
8981: SELECT distinct pterm.payment_term_id payment_term_id
8982: FROM pn_payment_terms_all pterm,
8983: pn_var_rents_all vrent,
8984: pn_var_rent_inv_all vinv
8985: WHERE vrent.lease_id = pterm.lease_id
8986: AND vrent.var_rent_id = vinv.var_rent_id
8987: AND pterm.start_date <=
8988: (SELECT MAX(gd.grp_end_date)

Line 9020: FROM pn_var_rent_inv_all

9016:
9017: -- Get the invoice id for a given period_id and invoice_date
9018: CURSOR check_inv_exists(p_var_rent_id IN NUMBER,p_inv_dt IN DATE,p_prd_id IN NUMBER) IS
9019: SELECT var_rent_inv_id inv_id
9020: FROM pn_var_rent_inv_all
9021: WHERE invoice_date= p_inv_dt
9022: AND var_rent_id = p_var_rent_id
9023: AND period_id = p_prd_id
9024: AND adjust_num = 0;

Line 9029: FROM pn_var_rent_inv_all

9025:
9026: -- Get the details of invoice for 1st partial period
9027: CURSOR get_fst_inv_id(p_var_rent_id IN NUMBER) IS
9028: SELECT var_rent_inv_id
9029: FROM pn_var_rent_inv_all
9030: WHERE var_rent_id=p_var_rent_id
9031: AND period_id IN (SELECT period_id
9032: FROM pn_var_periods_all
9033: WHERE period_num=1

Line 9169: FROM pn_var_rent_inv_all

9165: ,p_inv_dt IN DATE
9166: )
9167: IS
9168: SELECT var_rent_inv_id
9169: FROM pn_var_rent_inv_all
9170: WHERE var_rent_id=p_var_rent_id
9171: AND period_id=p_prd_id
9172: AND invoice_date=p_inv_dt
9173: AND adjust_num=0;

Line 9181: FROM pn_var_rent_inv_all

9177: ,p_prd_id IN NUMBER
9178: ,p_inv_id IN NUMBER)
9179: IS
9180: SELECT var_rent_inv_id
9181: FROM pn_var_rent_inv_all
9182: WHERE var_rent_id=p_var_rent_id
9183: AND period_id=p_prd_id
9184: AND var_rent_inv_id <> p_inv_id
9185: AND adjust_num=0;

Line 9348: pn_var_rent_inv_all vinv

9344: CURSOR get_all_pmt_terms(p_inv_id IN NUMBER) IS
9345: SELECT distinct pterm.payment_term_id payment_term_id
9346: FROM pn_payment_terms_all pterm,
9347: pn_var_rents_all vrent,
9348: pn_var_rent_inv_all vinv
9349: WHERE vrent.lease_id = pterm.lease_id
9350: AND vrent.var_rent_id = vinv.var_rent_id
9351: AND pterm.start_date <=
9352: (SELECT MAX(gd.grp_end_date)

Line 9383: FROM pn_var_rent_inv_all

9379:
9380: -- Get the invoice id for combination of a given invoice_date and period_id
9381: CURSOR check_inv_exists(p_var_rent_id IN NUMBER,p_inv_dt IN DATE,p_prd_id IN NUMBER) IS
9382: SELECT var_rent_inv_id inv_id
9383: FROM pn_var_rent_inv_all
9384: WHERE invoice_date=p_inv_dt
9385: AND var_rent_id =p_var_rent_id
9386: AND period_id =p_prd_id
9387: AND adjust_num=0;

Line 9518: pn_var_rent_inv_all vinv

9514: SELECT ppt.payment_term_id rent_incr_term_id
9515: FROM pn_index_lease_terms_all pilt,
9516: pn_payment_terms_all ppt ,
9517: pn_index_leases_all pil,
9518: pn_var_rent_inv_all vinv
9519: WHERE pilt.index_lease_id = pil.index_lease_id
9520: AND pil.lease_id = ppt.lease_id
9521: AND pilt.index_period_id = ppt.index_period_id
9522: AND pilt.rent_increase_term_id = ppt.payment_term_id

Line 9551: FROM pn_var_rent_inv_all

9547: WHERE var_rent_id =p_var_rent_id;
9548: --Get invoice id of the 1st partial period
9549: CURSOR get_fst_inv_id(p_var_rent_id IN NUMBER) IS
9550: SELECT var_rent_inv_id
9551: FROM pn_var_rent_inv_all
9552: WHERE var_rent_id=p_var_rent_id
9553: AND period_id IN (SELECT period_id
9554: FROM pn_var_periods_all
9555: WHERE period_num=1

Line 9645: pn_var_rent_inv_all vinv

9641: SELECT ppt.payment_term_id rent_incr_term_id
9642: FROM pn_index_lease_terms_all pilt,
9643: pn_payment_terms_all ppt ,
9644: pn_index_leases_all pil,
9645: pn_var_rent_inv_all vinv
9646: WHERE pilt.index_lease_id = pil.index_lease_id
9647: AND pil.lease_id = ppt.lease_id
9648: AND pilt.index_period_id = ppt.index_period_id
9649: AND pilt.rent_increase_term_id = ppt.payment_term_id

Line 9675: FROM pn_var_rent_inv_all

9671: AND var_rent_inv_id=invoice_id);
9672:
9673: CURSOR check_inv_exists(p_var_rent_id IN NUMBER,p_inv_dt IN DATE,p_prd_id IN NUMBER) IS
9674: SELECT var_rent_inv_id inv_id
9675: FROM pn_var_rent_inv_all
9676: WHERE invoice_date=p_inv_dt
9677: AND period_id= p_prd_id
9678: AND var_rent_id= p_var_rent_id
9679: AND adjust_num=0;

Line 10018: FROM pn_var_rent_inv_all

10014:
10015: CURSOR var_for_rent ( l_period_id IN NUMBER ) IS
10016: SELECT sum(decode(adjust_num,0,
10017: FOR_PER_RENT,0)) for_var_rent
10018: FROM pn_var_rent_inv_all
10019: WHERE period_id = l_period_id;
10020:
10021: l_for_rent NUMBER := NULL;
10022:

Line 10203: FROM pn_var_rent_inv_all rent_inv

10199: p_inv_dt IN DATE
10200: )
10201: IS
10202: SELECT SUM(rent_inv.ACTUAL_INVOICED_AMOUNT) act_inv_amt
10203: FROM pn_var_rent_inv_all rent_inv
10204: WHERE rent_inv.period_id = p_prd_id
10205: AND (rent_inv.invoice_date = p_inv_dt OR p_inv_dt IS NULL);
10206:
10207: l_vr_id NUMBER :=NULL;

Line 10294: FROM pn_var_rent_inv_all rent_inv

10290: p_inv_dt IN DATE
10291: )
10292: IS
10293: SELECT SUM(rent_inv.ACTUAL_INVOICED_AMOUNT) act_inv_amt
10294: FROM pn_var_rent_inv_all rent_inv
10295: WHERE rent_inv.period_id = p_prd_id
10296: AND (rent_inv.invoice_date <= p_inv_dt OR p_inv_dt IS NULL);
10297:
10298: l_vr_id NUMBER :=NULL;

Line 10359: FROM pn_var_rent_inv_all vinv

10355: IS
10356: SELECT 'y' AS include_flag
10357: FROM DUAL
10358: WHERE NOT EXISTS (SELECT NULL
10359: FROM pn_var_rent_inv_all vinv
10360: WHERE vinv.ACTUAL_INVOICED_AMOUNT <> 0
10361: AND vinv.period_id = p_prd_id
10362: );
10363: l_include VARCHAR2(1):= 'n';

Line 10415: pn_var_rent_inv_all inv

10411: ,p_inv_sch_date DATE
10412: ,p_term_template_id NUMBER) IS
10413: SELECT inv.var_rent_inv_id
10414: FROM
10415: pn_var_rent_inv_all inv
10416: WHERE
10417: var_rent_id = p_vr_id AND
10418: period_id = p_prd_id AND
10419: invoice_date = p_inv_dt AND

Line 10433: FROM pn_var_rent_inv_all

10429: p_term_template_id NUMBER) IS
10430: SELECT payment_term_id, var_rent_inv_id
10431: FROM pn_payment_terms_all
10432: WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
10433: FROM pn_var_rent_inv_all
10434: WHERE var_rent_id = p_var_rent_id)
10435: AND status = 'DRAFT'
10436: AND (start_date <> p_inv_sch_date OR
10437: term_template_id <> p_term_template_id);*/

Line 10479: pn_var_rent_inv_all

10475:
10476: /* update the invoice */
10477: /*Since forcasted rents are not yet exported, we can update them.*/
10478: UPDATE
10479: pn_var_rent_inv_all
10480: SET
10481: actual_term_status = 'N'
10482: ,last_update_date = SYSDATE
10483: ,last_updated_by = NVL(fnd_global.user_id,0)

Line 10493: pn_var_rent_inv_all

10489:
10490: /* update the invoice */
10491: /*Since forcasted rents are not yet exported, we can update them.*/
10492: UPDATE
10493: pn_var_rent_inv_all
10494: SET
10495: forecasted_term_status = 'N'
10496: ,last_update_date = SYSDATE
10497: ,last_updated_by = NVL(fnd_global.user_id,0)

Line 10507: pn_var_rent_inv_all

10503:
10504: /* update the invoice */
10505: /*Since forcasted rents are not yet exported, we can update them.*/
10506: UPDATE
10507: pn_var_rent_inv_all
10508: SET
10509: actual_term_status = 'N'
10510: ,last_update_date = SYSDATE
10511: ,last_updated_by = NVL(fnd_global.user_id,0)

Line 10539: FROM pn_var_rent_inv_all

10535: RETURN NUMBER IS
10536:
10537: CURSOR csr_get_inv IS
10538: SELECT sum(actual_invoiced_amount) AS actual_rent
10539: FROM pn_var_rent_inv_all
10540: WHERE period_id = p_period_id
10541: AND invoice_date = p_invoice_date
10542: AND var_rent_inv_id <= p_var_rent_inv_id
10543: AND true_up_amt IS NULL;

Line 10547: FROM pn_var_rent_inv_all

10543: AND true_up_amt IS NULL;
10544:
10545: CURSOR csr_get_true IS
10546: SELECT constr_actual_rent
10547: FROM pn_var_rent_inv_all
10548: WHERE invoice_date = p_invoice_date
10549: AND true_up_amt IS NOT NULL
10550: AND var_rent_inv_id = p_var_rent_inv_id;
10551:

Line 10839: FROM pn_var_rent_inv_all

10835: IS
10836:
10837: CURSOR inv_details_cur IS
10838: SELECT variance_exp_code, actual_exp_code, adjust_num, actual_invoiced_amount, for_per_rent
10839: FROM pn_var_rent_inv_all
10840: WHERE period_id = p_period_id
10841: AND var_rent_inv_id = p_var_rent_inv_id;
10842:
10843: BEGIN