[Home] [Help]
853: l_tot_prop_area pn_rec_arcl_dtl_all.TOTAL_assignable_area%TYPE;
854: l_ten_recoverable_area_rec ten_recoverable_area_rec;
855: l_ten_recoverable_area pn_rec_arcl_dtlln_all.occupied_area%TYPE:=0;
856: l_ten_occupancy_pct pn_rec_arcl_dtlln_all.occupancy_pct%TYPE:=0;
857: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE:=0;
858: l_line_constraints pn_rec_agr_linconst_all.value%TYPE:=0;
859: l_line_abatements pn_rec_agr_linabat_all.amount%TYPE:=0;
860: l_ten_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
861: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
856: l_ten_occupancy_pct pn_rec_arcl_dtlln_all.occupancy_pct%TYPE:=0;
857: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE:=0;
858: l_line_constraints pn_rec_agr_linconst_all.value%TYPE:=0;
859: l_line_abatements pn_rec_agr_linabat_all.amount%TYPE:=0;
860: l_ten_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
861: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
862: l_amount_per_sft NUMBER:=0;
863: l_budget_amount_per_sft NUMBER:=0;
864: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
857: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE:=0;
858: l_line_constraints pn_rec_agr_linconst_all.value%TYPE:=0;
859: l_line_abatements pn_rec_agr_linabat_all.amount%TYPE:=0;
860: l_ten_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
861: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
862: l_amount_per_sft NUMBER:=0;
863: l_budget_amount_per_sft NUMBER:=0;
864: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
865: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
861: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
862: l_amount_per_sft NUMBER:=0;
863: l_budget_amount_per_sft NUMBER:=0;
864: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
865: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
866: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
867: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
868: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
869: l_rowId ROWID:= NULL;
862: l_amount_per_sft NUMBER:=0;
863: l_budget_amount_per_sft NUMBER:=0;
864: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
865: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
866: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
867: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
868: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
869: l_rowId ROWID:= NULL;
870: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
863: l_budget_amount_per_sft NUMBER:=0;
864: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
865: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
866: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
867: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
868: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
869: l_rowId ROWID:= NULL;
870: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
871: l_creation_date DATE := SYSDATE;
864: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
865: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
866: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
867: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
868: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
869: l_rowId ROWID:= NULL;
870: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
871: l_creation_date DATE := SYSDATE;
872: l_created_by NUMBER := NVL(fnd_profile.value('USER_ID'), 0);
866: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
867: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
868: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
869: l_rowId ROWID:= NULL;
870: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
871: l_creation_date DATE := SYSDATE;
872: l_created_by NUMBER := NVL(fnd_profile.value('USER_ID'), 0);
873: l_BUDGET_PCT NUMBER:=0;
874: l_TENANCY_START_DATE DATE;
879: l_BUDGET_RECOVERY NUMBER:=0;
880: l_BUDGET_EXPENSE PN_REC_EXPCL_DTLLN_ALL.budgeted_amt%TYPE;
881: l_count NUMBER := 0;
882: i NUMBER := 0;
883: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
884: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
885: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
886: l_end_date pn_rec_period_lines_all.end_date%TYPE;
887: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
880: l_BUDGET_EXPENSE PN_REC_EXPCL_DTLLN_ALL.budgeted_amt%TYPE;
881: l_count NUMBER := 0;
882: i NUMBER := 0;
883: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
884: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
885: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
886: l_end_date pn_rec_period_lines_all.end_date%TYPE;
887: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
888: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
881: l_count NUMBER := 0;
882: i NUMBER := 0;
883: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
884: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
885: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
886: l_end_date pn_rec_period_lines_all.end_date%TYPE;
887: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
888: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
889: l_error_code NUMBER := 0;
882: i NUMBER := 0;
883: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
884: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
885: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
886: l_end_date pn_rec_period_lines_all.end_date%TYPE;
887: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
888: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
889: l_error_code NUMBER := 0;
890: l_rec_agr_name pn_rec_agreements_all.REC_AGREEMENT_NAME%TYPE;
883: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
884: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
885: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
886: l_end_date pn_rec_period_lines_all.end_date%TYPE;
887: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
888: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
889: l_error_code NUMBER := 0;
890: l_rec_agr_name pn_rec_agreements_all.REC_AGREEMENT_NAME%TYPE;
891: l_rec_agr_num pn_rec_agreements_all.REC_AGREEMENT_NUM%TYPE;
884: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
885: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
886: l_end_date pn_rec_period_lines_all.end_date%TYPE;
887: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
888: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
889: l_error_code NUMBER := 0;
890: l_rec_agr_name pn_rec_agreements_all.REC_AGREEMENT_NAME%TYPE;
891: l_rec_agr_num pn_rec_agreements_all.REC_AGREEMENT_NUM%TYPE;
892: --------------------------------------------------------------------------
942: CURSOR csr_check_line_status IS
943: SELECT 'Y'
944: FROM DUAL
945: WHERE exists (SELECT NULL
946: FROM pn_rec_period_lines_all plines
947: ,pn_rec_agr_lines_all lines
948: ,pn_rec_calc_periods_all calc_periods
949: WHERE lines.rec_agreement_id = p_rec_agreement_id
950: AND p_as_of_date between lines.start_date and lines.end_date
961: CURSOR csr_check_period_line IS
962: SELECT 'Y'
963: FROM DUAL
964: WHERE not exists (SELECT NULL
965: FROM pn_rec_period_lines_all plines
966: ,pn_rec_agr_lines_all lines
967: ,pn_rec_calc_periods_all calc_periods
968: WHERE lines.rec_agreement_id = p_rec_agreement_id
969: AND p_as_of_date between lines.start_date and lines.end_date
981: --------------------------------------------------------------------------------------------------
982:
983: CURSOR calc_all_cons IS
984: SELECT NVL(SUM(NVL(plines.reconciled_amount,0)),0) RECONCILED_AMOUNT
985: FROM pn_rec_period_lines_all plines
986: ,pn_rec_calc_periods_all calc_periods
987: ,pn_rec_agr_lines_all lines
988: WHERE lines.rec_agreement_id = p_rec_agreement_id
989: AND plines.rec_agr_line_id = lines.rec_agr_line_id
1001: ,plines.end_date
1002: ,NVL(plines.reconciled_amount,0) RECONCILED_AMOUNT
1003: ,plines.billing_type
1004: ,plines.billing_purpose
1005: FROM pn_rec_period_lines_all plines
1006: ,pn_rec_calc_periods_all calc_periods
1007: ,pn_rec_agr_lines_all lines
1008: WHERE lines.rec_agreement_id = p_rec_agreement_id
1009: AND plines.rec_agr_line_id = lines.rec_agr_line_id
1024: ,plines.end_date
1025: ,nvl(plines.reconciled_amount,0) RECONCILED_AMOUNT
1026: ,plines.billing_type
1027: ,plines.billing_purpose
1028: FROM pn_rec_period_lines_all plines
1029: ,pn_rec_calc_periods_all calc_periods
1030: WHERE plines.rec_agr_line_id = p_rec_agr_line_id
1031: AND plines.start_date = p_calc_period_start_date
1032: AND plines.end_date = p_calc_period_end_date
1967: IF p_error_code <> -99 THEN
1968:
1969: /* Check if recovery amount has already been calculated for the period start and
1970: end dates and the as of date.If it has not, then insert a new record into the
1971: PN_REC_PERIOD_LINES_ALL table with the calculated values or else update the
1972: existing record with the values as a result of the re-calculation. */
1973:
1974: l_rec_period_lines_id := PN_REC_CALC_PKG.find_if_period_line_exists(
1975: p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1993:
1994: pnp_debug_pkg.log('Error Code 29 : '||p_error_code);
1995: IF p_error_code <> -99 and l_rec_period_lines_id IS NULL THEN
1996:
1997: pnp_debug_pkg.log('Inserting into PN_REC_PERIOD_LINES_ALL ');
1998:
1999: IF p_error_code = -99 THEN
2000: l_status := 'Error';
2001: ELSE
2054:
2055: ELSIF p_error_code <> -99 AND l_rec_period_lines_id is not null THEN
2056: pnp_debug_pkg.log('Error Code 30 : '||p_error_code);
2057:
2058: pnp_debug_pkg.log('Updating PN_REC_PERIOD_LINES_ALL ');
2059:
2060: IF p_error_code = -99 THEN
2061: l_status := 'Error';
2062: ELSE
2143: ELSIF agr_lines_one%ISOPEN THEN
2144: CLOSE agr_lines_one;
2145: END IF;
2146:
2147: /* Commit the record in pn_rec_period_lines */
2148:
2149: COMMIT;
2150:
2151: IF l_consolidate = 'Y' THEN
2323: IF l_consolidate = 'Y' AND l_calculate_all THEN
2324: pnp_debug_pkg.log('Rolling Back Lines for l_consolidate = Y and l_calculate_all');
2325:
2326: ROLLBACK;
2327: UPDATE pn_rec_period_lines_all
2328: SET STATUS = 'Error'
2329: WHERE rec_agr_line_id in (SELECT rec_agr_line_id
2330: FROM PN_REC_AGR_LINES_ALL
2331: WHERE rec_agreement_id = p_rec_agreement_id)
2336:
2337: ELSE
2338: pnp_debug_pkg.log('Rolling Back Lines for Else Part of l_consolidate = Y and l_calculate_all');
2339: ROLLBACK;
2340: UPDATE pn_rec_period_lines_all
2341: SET STATUS = 'Error'
2342: WHERE rec_agr_line_id = l_rec_agr_line_id
2343: AND start_date = p_calc_period_start_date
2344: AND end_date = p_calc_period_end_date
2370: | FUNCTION
2371: | GET_RECOVERABLE_AREA
2372: |
2373: | DESCRIPTION
2374: | Gets recoverable area from pn_rec_period_lines_all table
2375: | for a line if the calc method is 'Fixed rate'
2376: |
2377: | SCOPE - PUBLIC
2378: |
2388: | OUT:
2389: |
2390: | RETURNS : None
2391: |
2392: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2393: | for a line if the calc method is 'Fixed rate'
2394: |
2395: | MODIFICATION HISTORY
2396: |
2396: |
2397: | 19-MAY-2003 Daniel Thota o Created
2398: +===========================================================================*/
2399: FUNCTION get_recoverable_area (
2400: p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
2401: ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2402: )
2403: RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2404:
2397: | 19-MAY-2003 Daniel Thota o Created
2398: +===========================================================================*/
2399: FUNCTION get_recoverable_area (
2400: p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
2401: ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2402: )
2403: RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2404:
2405: l_recoverable_area pn_rec_period_lines_all.recoverable_area%TYPE;
2399: FUNCTION get_recoverable_area (
2400: p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
2401: ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2402: )
2403: RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2404:
2405: l_recoverable_area pn_rec_period_lines_all.recoverable_area%TYPE;
2406:
2407: BEGIN
2401: ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2402: )
2403: RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2404:
2405: l_recoverable_area pn_rec_period_lines_all.recoverable_area%TYPE;
2406:
2407: BEGIN
2408:
2409: pnp_debug_pkg.log('PN_REC_CALC_PKG.get_recoverable_area (+) ');
2409: pnp_debug_pkg.log('PN_REC_CALC_PKG.get_recoverable_area (+) ');
2410:
2411: SELECT NVL(plines.recoverable_area,0)
2412: INTO l_recoverable_area
2413: FROM pn_rec_period_lines_all plines
2414: WHERE plines.rec_agr_line_id = p_rec_agr_line_id
2415: AND plines.rec_calc_period_id = p_rec_calc_period_id
2416: ;
2417:
2435: | FUNCTION
2436: | GET_TOT_PROP_AREA
2437: |
2438: | DESCRIPTION
2439: | Gets recoverable area from pn_rec_period_lines_all table
2440: | for a line if the calc method is 'Fixed rate'
2441: |
2442: | SCOPE - PUBLIC
2443: |
2453: | OUT:
2454: |
2455: | RETURNS : None
2456: |
2457: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2458: | for a line if the calc method is 'Fixed rate'
2459: |
2460: | MODIFICATION HISTORY
2461: |
2599: | FUNCTION
2600: | TEN_RECOVERABLE_AREA
2601: |
2602: | DESCRIPTION
2603: | Gets recoverable area from pn_rec_period_lines_all table
2604: | for a line if the calc method is 'Fixed rate'
2605: |
2606: | SCOPE - PUBLIC
2607: |
2617: | OUT:
2618: |
2619: | RETURNS : None
2620: |
2621: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2622: | for a line if the calc method is 'Fixed rate'
2623: |
2624: | MODIFICATION HISTORY
2625: |
2701: | OUT:
2702: |
2703: | RETURNS : None
2704: |
2705: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2706: | for a line if the calc method is 'Fixed rate'
2707: |
2708: | MODIFICATION HISTORY
2709: |
2723: ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
2724: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
2725: ,p_called_from VARCHAR2
2726: )
2727: RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
2728:
2729: -- Fix for bug # 3123283
2730: CURSOR csr_get_line IS
2731: SELECT purpose, type
2737: SELECT 'Y'
2738: FROM dual
2739: WHERE exists(
2740: SELECT 'Y'
2741: FROM pn_rec_period_lines_all period_lines
2742: ,pn_rec_agreements_all recagr
2743: ,pn_rec_agr_lines_all lines
2744: WHERE nvl(period_lines.actual_prorata_share,0) = 0
2745: AND period_lines.start_date = p_calc_period_start_date
2776: AND area_class_dtl_lines.include_flag = 'Y'
2777: AND area_class_dtl_lines.exclude_prorata_ovr_flag = 'Y'
2778: ));
2779:
2780: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE := 0;
2781: l_exists VARCHAR2(1) := 'N';
2782:
2783: -- Fix for bug # 3123283
2784: l_line_purpose pn_rec_agr_lines_all.PURPOSE%TYPE;
2821: END IF;
2822:
2823: SELECT NVL(SUM(NVL(period_lines.actual_prorata_share,0)),0)
2824: INTO l_contr_actual_recovery
2825: FROM pn_rec_period_lines_all period_lines
2826: ,pn_rec_agreements_all recagr
2827: ,pn_rec_agr_lines_all lines
2828: WHERE period_lines.start_date = p_calc_period_start_date
2829: AND period_lines.end_date = p_calc_period_end_date
2891: | FUNCTION
2892: | GET_LINE_EXPENSES
2893: |
2894: | DESCRIPTION
2895: | Gets recoverable area from pn_rec_period_lines_all table
2896: | for a line if the calc method is 'Fixed rate'
2897: |
2898: | SCOPE - PUBLIC
2899: |
2909: | OUT:
2910: |
2911: | RETURNS : None
2912: |
2913: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2914: | for a line if the calc method is 'Fixed rate'
2915: |
2916: | MODIFICATION HISTORY
2917: |
3041: | FUNCTION
3042: | GET_BUDGET_EXPENSES
3043: |
3044: | DESCRIPTION
3045: | Gets recoverable area from pn_rec_period_lines_all table
3046: | for a line if the calc method is 'Fixed rate'
3047: |
3048: | SCOPE - PUBLIC
3049: |
3059: | OUT:
3060: |
3061: | RETURNS : None
3062: |
3063: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3064: | for a line if the calc method is 'Fixed rate'
3065: |
3066: | MODIFICATION HISTORY
3067: |
3147: | FUNCTION
3148: | GET_BILLED_RECOVERY
3149: |
3150: | DESCRIPTION
3151: | Gets recoverable area from pn_rec_period_lines_all table
3152: | for a line if the calc method is 'Fixed rate'
3153: |
3154: | SCOPE - PUBLIC
3155: |
3165: | OUT:
3166: |
3167: | RETURNS : None
3168: |
3169: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3170: | for a line if the calc method is 'Fixed rate'
3171: |
3172: | MODIFICATION HISTORY
3173: |
3189: ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
3190: ,p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
3191: ,p_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE
3192: )
3193: RETURN pn_rec_period_lines_all.billed_recovery%TYPE IS
3194:
3195: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE;
3196: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE :=
3197: PN_REC_CALC_PKG.find_if_period_line_exists(
3191: ,p_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE
3192: )
3193: RETURN pn_rec_period_lines_all.billed_recovery%TYPE IS
3194:
3195: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE;
3196: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE :=
3197: PN_REC_CALC_PKG.find_if_period_line_exists(
3198: p_rec_agr_line_id
3199: ,p_rec_calc_period_id
3192: )
3193: RETURN pn_rec_period_lines_all.billed_recovery%TYPE IS
3194:
3195: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE;
3196: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE :=
3197: PN_REC_CALC_PKG.find_if_period_line_exists(
3198: p_rec_agr_line_id
3199: ,p_rec_calc_period_id
3200: );
3246: | FUNCTION
3247: | GET_LINE_CONSTRAINTS
3248: |
3249: | DESCRIPTION
3250: | Gets recoverable area from pn_rec_period_lines_all table
3251: | for a line if the calc method is 'Fixed rate'
3252: |
3253: | SCOPE - PUBLIC
3254: |
3264: | OUT:
3265: |
3266: | RETURNS : None
3267: |
3268: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3269: | for a line if the calc method is 'Fixed rate'
3270: |
3271: | MODIFICATION HISTORY
3272: |
3342: | FUNCTION
3343: | GET_LINE_ABATEMENTS
3344: |
3345: | DESCRIPTION
3346: | Gets recoverable area from pn_rec_period_lines_all table
3347: | for a line if the calc method is 'Fixed rate'
3348: |
3349: | SCOPE - PUBLIC
3350: |
3360: | OUT:
3361: |
3362: | RETURNS : None
3363: |
3364: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3365: | for a line if the calc method is 'Fixed rate'
3366: |
3367: | MODIFICATION HISTORY
3368: |
3430: |
3431: | 22-MAY-2003 Daniel Thota o Created
3432: +===========================================================================*/
3433: FUNCTION find_if_period_line_exists (
3434: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
3435: ,p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
3436: )
3437: RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3438:
3431: | 22-MAY-2003 Daniel Thota o Created
3432: +===========================================================================*/
3433: FUNCTION find_if_period_line_exists (
3434: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
3435: ,p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
3436: )
3437: RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3438:
3439: CURSOR csr_chck_exist IS
3433: FUNCTION find_if_period_line_exists (
3434: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
3435: ,p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
3436: )
3437: RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3438:
3439: CURSOR csr_chck_exist IS
3440: SELECT periods.rec_period_lines_id
3441: FROM pn_rec_period_lines_all periods
3437: RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3438:
3439: CURSOR csr_chck_exist IS
3440: SELECT periods.rec_period_lines_id
3441: FROM pn_rec_period_lines_all periods
3442: WHERE periods.rec_agr_line_id = p_rec_agr_line_id
3443: AND periods.rec_calc_period_id = p_rec_calc_period_id;
3444:
3445: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE;
3441: FROM pn_rec_period_lines_all periods
3442: WHERE periods.rec_agr_line_id = p_rec_agr_line_id
3443: AND periods.rec_calc_period_id = p_rec_calc_period_id;
3444:
3445: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE;
3446:
3447: BEGIN
3448:
3449: PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.find_if_period_line_exists (+)');
3481: | PROCEDURE
3482: | INSERT_PERIOD_LINES_ROW
3483: |
3484: | DESCRIPTION
3485: | Create records in the PN_REC_PERIOD_LINES_ALL table
3486: |
3487: | SCOPE - PUBLIC
3488: |
3489: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3493: | OUT:
3494: |
3495: | RETURNS : None
3496: |
3497: | NOTES : Create records in the PN_REC_PERIOD_LINES_ALL
3498: |
3499: | MODIFICATION HISTORY
3500: |
3501: | 21-MAY-2003 Daniel Thota o Created
3542: ) is
3543:
3544: CURSOR C is
3545: select ROWID
3546: from PN_REC_PERIOD_LINES
3547: where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID;
3548:
3549: CURSOR org_cur IS
3550: SELECT org_id
3560: -------------------------------------------------------
3561: -- Select the nextval for group date id
3562: -------------------------------------------------------
3563: IF ( X_REC_PERIOD_LINES_ID IS NULL) THEN
3564: select pn_rec_period_lines_s.nextval
3565: into X_REC_PERIOD_LINES_ID
3566: from dual;
3567: END IF;
3568:
3574: l_org_ID := pn_mo_cache_utils.get_current_org_id;
3575: END IF;
3576:
3577:
3578: INSERT INTO PN_REC_PERIOD_LINES_ALL (
3579: BUDGET_PCT
3580: ,OCCUPANCY_PCT
3581: ,MULTIPLE_PCT
3582: ,TENANCY_START_DATE
3674: | PROCEDURE
3675: | UPDATE_PERIOD_LINES_ROW
3676: |
3677: | DESCRIPTION
3678: | Update records in the PN_REC_PERIOD_LINES_ALL table
3679: |
3680: | SCOPE - PUBLIC
3681: |
3682: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3686: | OUT:
3687: |
3688: | RETURNS : None
3689: |
3690: | NOTES : Update records in the PN_REC_PERIOD_LINES_ALL
3691: |
3692: | MODIFICATION HISTORY
3693: |
3694: | 21-MAY-2003 Daniel Thota o Created
3734: BEGIN
3735:
3736: PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_LINES_ROW (+)');
3737:
3738: update PN_REC_PERIOD_LINES_ALL set
3739: BUDGET_PCT = X_BUDGET_PCT
3740: ,OCCUPANCY_PCT = X_OCCUPANCY_PCT
3741: ,MULTIPLE_PCT = X_MULTIPLE_PCT
3742: ,TENANCY_START_DATE = X_TENANCY_START_DATE
3789: | PROCEDURE
3790: | DELETE_PERIOD_LINES_ROW
3791: |
3792: | DESCRIPTION
3793: | Delete records in the PN_REC_PERIOD_LINES_ALL table
3794: |
3795: | SCOPE - PUBLIC
3796: |
3797: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3801: | OUT:
3802: |
3803: | RETURNS : None
3804: |
3805: | NOTES : Delete records in the PN_REC_PERIOD_LINES_ALL
3806: |
3807: | MODIFICATION HISTORY
3808: |
3809: | 21-MAY-2003 Daniel Thota o Created
3816: BEGIN
3817:
3818: PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_LINES_ROW (+)');
3819:
3820: delete from PN_REC_PERIOD_LINES_ALL
3821: where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID;
3822:
3823: if (sql%notfound) then
3824: raise no_data_found;
4853: |
4854: | 19-MAY-2003 Daniel Thota o Created
4855: +===========================================================================*/
4856: FUNCTION get_prior_period_actual_amount(
4857: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
4858: ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
4859: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4860: ,p_called_from VARCHAR2
4861: )
4858: ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
4859: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4860: ,p_called_from VARCHAR2
4861: )
4862: RETURN pn_rec_period_lines_all.constrained_actual%TYPE IS
4863:
4864: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4865: l_percent pn_rec_agr_linconst_all.value%TYPE;
4866:
4860: ,p_called_from VARCHAR2
4861: )
4862: RETURN pn_rec_period_lines_all.constrained_actual%TYPE IS
4863:
4864: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4865: l_percent pn_rec_agr_linconst_all.value%TYPE;
4866:
4867: CURSOR csr_get_curr_percent (p_as_of_date date) is
4868: SELECT lineconst.VALUE
4877: pnp_debug_pkg.log('PN_REC_CALC_PKG.get_prior_period_actual_amount (+) ');
4878:
4879: SELECT NVL(period_lines.constrained_actual,0)
4880: INTO l_prior_period_amount
4881: FROM pn_rec_period_lines_all period_lines
4882: WHERE rec_agr_line_id = p_rec_agr_line_id
4883: AND end_date = (SELECT max(end_date)
4884: FROM pn_rec_period_lines_all
4885: WHERE start_date < p_start_date
4880: INTO l_prior_period_amount
4881: FROM pn_rec_period_lines_all period_lines
4882: WHERE rec_agr_line_id = p_rec_agr_line_id
4883: AND end_date = (SELECT max(end_date)
4884: FROM pn_rec_period_lines_all
4885: WHERE start_date < p_start_date
4886: AND end_date < p_start_date
4887: AND rec_agr_line_id = p_rec_agr_line_id) ;
4888:
4964: |
4965: | 23-aug-2003 achauhan o Created
4966: +===========================================================================*/
4967: FUNCTION get_prior_period_cap(
4968: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
4969: ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
4970: ,p_end_date pn_rec_calc_periods_all.end_date%TYPE
4971: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4972: ,p_called_from VARCHAR2
4970: ,p_end_date pn_rec_calc_periods_all.end_date%TYPE
4971: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4972: ,p_called_from VARCHAR2
4973: )
4974: RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
4975:
4976: l_percent pn_rec_agr_linconst_all.value%TYPE;
4977: l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4978: l_start_date pn_rec_period_lines_all.start_date%TYPE;
4973: )
4974: RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
4975:
4976: l_percent pn_rec_agr_linconst_all.value%TYPE;
4977: l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4978: l_start_date pn_rec_period_lines_all.start_date%TYPE;
4979: l_end_date pn_rec_period_lines_all.end_date%TYPE;
4980:
4981: CURSOR csr_get_base_cap is
4974: RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
4975:
4976: l_percent pn_rec_agr_linconst_all.value%TYPE;
4977: l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4978: l_start_date pn_rec_period_lines_all.start_date%TYPE;
4979: l_end_date pn_rec_period_lines_all.end_date%TYPE;
4980:
4981: CURSOR csr_get_base_cap is
4982: SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4975:
4976: l_percent pn_rec_agr_linconst_all.value%TYPE;
4977: l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4978: l_start_date pn_rec_period_lines_all.start_date%TYPE;
4979: l_end_date pn_rec_period_lines_all.end_date%TYPE;
4980:
4981: CURSOR csr_get_base_cap is
4982: SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4983: FROM pn_rec_period_lines_all period_lines
4979: l_end_date pn_rec_period_lines_all.end_date%TYPE;
4980:
4981: CURSOR csr_get_base_cap is
4982: SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4983: FROM pn_rec_period_lines_all period_lines
4984: WHERE rec_agr_line_id = p_rec_agr_line_id
4985: AND start_date = (select min(start_date)
4986: from pn_rec_period_lines_all
4987: WHERE rec_agr_line_id = p_rec_agr_line_id) ;
4982: SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4983: FROM pn_rec_period_lines_all period_lines
4984: WHERE rec_agr_line_id = p_rec_agr_line_id
4985: AND start_date = (select min(start_date)
4986: from pn_rec_period_lines_all
4987: WHERE rec_agr_line_id = p_rec_agr_line_id) ;
4988:
4989: CURSOR csr_get_prior_periods (p_start_date date, p_fst_end_date date) is
4990: SELECT lineconst.VALUE
4987: WHERE rec_agr_line_id = p_rec_agr_line_id) ;
4988:
4989: CURSOR csr_get_prior_periods (p_start_date date, p_fst_end_date date) is
4990: SELECT lineconst.VALUE
4991: FROM pn_rec_period_lines_all period_lines,
4992: pn_rec_agr_linconst_all lineconst,
4993: pn_rec_calc_periods_all recperiod
4994: WHERE period_lines.rec_agr_line_id = p_rec_agr_line_id
4995: AND recperiod.rec_calc_period_id = period_lines.rec_calc_period_id