[Home] [Help]
828: l_tot_prop_area pn_rec_arcl_dtl_all.TOTAL_assignable_area%TYPE;
829: l_ten_recoverable_area_rec ten_recoverable_area_rec;
830: l_ten_recoverable_area pn_rec_arcl_dtlln_all.occupied_area%TYPE:=0;
831: l_ten_occupancy_pct pn_rec_arcl_dtlln_all.occupancy_pct%TYPE:=0;
832: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE:=0;
833: l_line_constraints pn_rec_agr_linconst_all.value%TYPE:=0;
834: l_line_abatements pn_rec_agr_linabat_all.amount%TYPE:=0;
835: l_ten_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
836: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
831: l_ten_occupancy_pct pn_rec_arcl_dtlln_all.occupancy_pct%TYPE:=0;
832: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE:=0;
833: l_line_constraints pn_rec_agr_linconst_all.value%TYPE:=0;
834: l_line_abatements pn_rec_agr_linabat_all.amount%TYPE:=0;
835: l_ten_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
836: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
837: l_amount_per_sft NUMBER:=0;
838: l_budget_amount_per_sft NUMBER:=0;
839: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
832: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE:=0;
833: l_line_constraints pn_rec_agr_linconst_all.value%TYPE:=0;
834: l_line_abatements pn_rec_agr_linabat_all.amount%TYPE:=0;
835: l_ten_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
836: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
837: l_amount_per_sft NUMBER:=0;
838: l_budget_amount_per_sft NUMBER:=0;
839: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
840: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
836: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
837: l_amount_per_sft NUMBER:=0;
838: l_budget_amount_per_sft NUMBER:=0;
839: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
840: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
841: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
842: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
843: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
844: l_rowId ROWID:= NULL;
837: l_amount_per_sft NUMBER:=0;
838: l_budget_amount_per_sft NUMBER:=0;
839: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
840: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
841: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
842: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
843: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
844: l_rowId ROWID:= NULL;
845: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
838: l_budget_amount_per_sft NUMBER:=0;
839: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
840: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
841: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
842: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
843: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
844: l_rowId ROWID:= NULL;
845: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
846: l_creation_date DATE := SYSDATE;
839: l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
840: l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
841: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
842: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
843: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
844: l_rowId ROWID:= NULL;
845: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
846: l_creation_date DATE := SYSDATE;
847: l_created_by NUMBER := NVL(fnd_profile.value('USER_ID'), 0);
841: l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
842: l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
843: l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
844: l_rowId ROWID:= NULL;
845: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
846: l_creation_date DATE := SYSDATE;
847: l_created_by NUMBER := NVL(fnd_profile.value('USER_ID'), 0);
848: l_BUDGET_PCT NUMBER:=0;
849: l_TENANCY_START_DATE DATE;
854: l_BUDGET_RECOVERY NUMBER:=0;
855: l_BUDGET_EXPENSE PN_REC_EXPCL_DTLLN_ALL.budgeted_amt%TYPE;
856: l_count NUMBER := 0;
857: i NUMBER := 0;
858: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
859: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
860: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
861: l_end_date pn_rec_period_lines_all.end_date%TYPE;
862: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
855: l_BUDGET_EXPENSE PN_REC_EXPCL_DTLLN_ALL.budgeted_amt%TYPE;
856: l_count NUMBER := 0;
857: i NUMBER := 0;
858: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
859: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
860: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
861: l_end_date pn_rec_period_lines_all.end_date%TYPE;
862: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
863: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
856: l_count NUMBER := 0;
857: i NUMBER := 0;
858: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
859: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
860: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
861: l_end_date pn_rec_period_lines_all.end_date%TYPE;
862: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
863: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
864: l_error_code NUMBER := 0;
857: i NUMBER := 0;
858: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
859: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
860: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
861: l_end_date pn_rec_period_lines_all.end_date%TYPE;
862: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
863: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
864: l_error_code NUMBER := 0;
865: l_rec_agr_name pn_rec_agreements_all.REC_AGREEMENT_NAME%TYPE;
858: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
859: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
860: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
861: l_end_date pn_rec_period_lines_all.end_date%TYPE;
862: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
863: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
864: l_error_code NUMBER := 0;
865: l_rec_agr_name pn_rec_agreements_all.REC_AGREEMENT_NAME%TYPE;
866: l_rec_agr_num pn_rec_agreements_all.REC_AGREEMENT_NUM%TYPE;
859: l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
860: l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
861: l_end_date pn_rec_period_lines_all.end_date%TYPE;
862: l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
863: l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
864: l_error_code NUMBER := 0;
865: l_rec_agr_name pn_rec_agreements_all.REC_AGREEMENT_NAME%TYPE;
866: l_rec_agr_num pn_rec_agreements_all.REC_AGREEMENT_NUM%TYPE;
867: --------------------------------------------------------------------------
917: CURSOR csr_check_line_status IS
918: SELECT 'Y'
919: FROM DUAL
920: WHERE exists (SELECT NULL
921: FROM pn_rec_period_lines_all plines
922: ,pn_rec_agr_lines_all lines
923: ,pn_rec_calc_periods_all calc_periods
924: WHERE lines.rec_agreement_id = p_rec_agreement_id
925: AND p_as_of_date between lines.start_date and lines.end_date
936: CURSOR csr_check_period_line IS
937: SELECT 'Y'
938: FROM DUAL
939: WHERE not exists (SELECT NULL
940: FROM pn_rec_period_lines_all plines
941: ,pn_rec_agr_lines_all lines
942: ,pn_rec_calc_periods_all calc_periods
943: WHERE lines.rec_agreement_id = p_rec_agreement_id
944: AND p_as_of_date between lines.start_date and lines.end_date
956: --------------------------------------------------------------------------------------------------
957:
958: CURSOR calc_all_cons IS
959: SELECT NVL(SUM(NVL(plines.reconciled_amount,0)),0) RECONCILED_AMOUNT
960: FROM pn_rec_period_lines_all plines
961: ,pn_rec_calc_periods_all calc_periods
962: ,pn_rec_agr_lines_all lines
963: WHERE lines.rec_agreement_id = p_rec_agreement_id
964: AND plines.rec_agr_line_id = lines.rec_agr_line_id
976: ,plines.end_date
977: ,NVL(plines.reconciled_amount,0) RECONCILED_AMOUNT
978: ,plines.billing_type
979: ,plines.billing_purpose
980: FROM pn_rec_period_lines_all plines
981: ,pn_rec_calc_periods_all calc_periods
982: ,pn_rec_agr_lines_all lines
983: WHERE lines.rec_agreement_id = p_rec_agreement_id
984: AND plines.rec_agr_line_id = lines.rec_agr_line_id
999: ,plines.end_date
1000: ,nvl(plines.reconciled_amount,0) RECONCILED_AMOUNT
1001: ,plines.billing_type
1002: ,plines.billing_purpose
1003: FROM pn_rec_period_lines_all plines
1004: ,pn_rec_calc_periods_all calc_periods
1005: WHERE plines.rec_agr_line_id = p_rec_agr_line_id
1006: AND plines.start_date = p_calc_period_start_date
1007: AND plines.end_date = p_calc_period_end_date
1942: IF p_error_code <> -99 THEN
1943:
1944: /* Check if recovery amount has already been calculated for the period start and
1945: end dates and the as of date.If it has not, then insert a new record into the
1946: PN_REC_PERIOD_LINES_ALL table with the calculated values or else update the
1947: existing record with the values as a result of the re-calculation. */
1948:
1949: l_rec_period_lines_id := PN_REC_CALC_PKG.find_if_period_line_exists(
1950: p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1968:
1969: pnp_debug_pkg.log('Error Code 29 : '||p_error_code);
1970: IF p_error_code <> -99 and l_rec_period_lines_id IS NULL THEN
1971:
1972: pnp_debug_pkg.log('Inserting into PN_REC_PERIOD_LINES_ALL ');
1973:
1974: IF p_error_code = -99 THEN
1975: l_status := 'Error';
1976: ELSE
2029:
2030: ELSIF p_error_code <> -99 AND l_rec_period_lines_id is not null THEN
2031: pnp_debug_pkg.log('Error Code 30 : '||p_error_code);
2032:
2033: pnp_debug_pkg.log('Updating PN_REC_PERIOD_LINES_ALL ');
2034:
2035: IF p_error_code = -99 THEN
2036: l_status := 'Error';
2037: ELSE
2293: IF p_error_code = -99 THEN
2294: IF l_consolidate = 'Y' AND l_calculate_all THEN
2295:
2296: ROLLBACK;
2297: UPDATE pn_rec_period_lines_all
2298: SET STATUS = 'Error'
2299: WHERE rec_agr_line_id in (SELECT rec_agr_line_id
2300: FROM PN_REC_AGR_LINES_ALL
2301: WHERE rec_agreement_id = p_rec_agreement_id)
2306:
2307: ELSE
2308:
2309: ROLLBACK;
2310: UPDATE pn_rec_period_lines_all
2311: SET STATUS = 'Error'
2312: WHERE rec_agr_line_id = l_rec_agr_line_id
2313: AND start_date = p_calc_period_start_date
2314: AND end_date = p_calc_period_end_date
2340: | FUNCTION
2341: | GET_RECOVERABLE_AREA
2342: |
2343: | DESCRIPTION
2344: | Gets recoverable area from pn_rec_period_lines_all table
2345: | for a line if the calc method is 'Fixed rate'
2346: |
2347: | SCOPE - PUBLIC
2348: |
2358: | OUT:
2359: |
2360: | RETURNS : None
2361: |
2362: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2363: | for a line if the calc method is 'Fixed rate'
2364: |
2365: | MODIFICATION HISTORY
2366: |
2366: |
2367: | 19-MAY-2003 Daniel Thota o Created
2368: +===========================================================================*/
2369: FUNCTION get_recoverable_area (
2370: p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
2371: ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2372: )
2373: RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2374:
2367: | 19-MAY-2003 Daniel Thota o Created
2368: +===========================================================================*/
2369: FUNCTION get_recoverable_area (
2370: p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
2371: ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2372: )
2373: RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2374:
2375: l_recoverable_area pn_rec_period_lines_all.recoverable_area%TYPE;
2369: FUNCTION get_recoverable_area (
2370: p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
2371: ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2372: )
2373: RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2374:
2375: l_recoverable_area pn_rec_period_lines_all.recoverable_area%TYPE;
2376:
2377: BEGIN
2371: ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2372: )
2373: RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2374:
2375: l_recoverable_area pn_rec_period_lines_all.recoverable_area%TYPE;
2376:
2377: BEGIN
2378:
2379: pnp_debug_pkg.log('PN_REC_CALC_PKG.get_recoverable_area (+) ');
2379: pnp_debug_pkg.log('PN_REC_CALC_PKG.get_recoverable_area (+) ');
2380:
2381: SELECT NVL(plines.recoverable_area,0)
2382: INTO l_recoverable_area
2383: FROM pn_rec_period_lines_all plines
2384: WHERE plines.rec_agr_line_id = p_rec_agr_line_id
2385: AND plines.rec_calc_period_id = p_rec_calc_period_id
2386: ;
2387:
2405: | FUNCTION
2406: | GET_TOT_PROP_AREA
2407: |
2408: | DESCRIPTION
2409: | Gets recoverable area from pn_rec_period_lines_all table
2410: | for a line if the calc method is 'Fixed rate'
2411: |
2412: | SCOPE - PUBLIC
2413: |
2423: | OUT:
2424: |
2425: | RETURNS : None
2426: |
2427: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2428: | for a line if the calc method is 'Fixed rate'
2429: |
2430: | MODIFICATION HISTORY
2431: |
2565: | FUNCTION
2566: | TEN_RECOVERABLE_AREA
2567: |
2568: | DESCRIPTION
2569: | Gets recoverable area from pn_rec_period_lines_all table
2570: | for a line if the calc method is 'Fixed rate'
2571: |
2572: | SCOPE - PUBLIC
2573: |
2583: | OUT:
2584: |
2585: | RETURNS : None
2586: |
2587: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2588: | for a line if the calc method is 'Fixed rate'
2589: |
2590: | MODIFICATION HISTORY
2591: |
2667: | OUT:
2668: |
2669: | RETURNS : None
2670: |
2671: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2672: | for a line if the calc method is 'Fixed rate'
2673: |
2674: | MODIFICATION HISTORY
2675: |
2689: ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
2690: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
2691: ,p_called_from VARCHAR2
2692: )
2693: RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
2694:
2695: -- Fix for bug # 3123283
2696: CURSOR csr_get_line IS
2697: SELECT purpose, type
2703: SELECT 'Y'
2704: FROM dual
2705: WHERE exists(
2706: SELECT 'Y'
2707: FROM pn_rec_period_lines_all period_lines
2708: ,pn_rec_agreements_all recagr
2709: ,pn_rec_agr_lines_all lines
2710: WHERE nvl(period_lines.actual_prorata_share,0) = 0
2711: AND period_lines.start_date = p_calc_period_start_date
2742: AND area_class_dtl_lines.include_flag = 'Y'
2743: AND area_class_dtl_lines.exclude_prorata_ovr_flag = 'Y'
2744: ));
2745:
2746: l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE := 0;
2747: l_exists VARCHAR2(1) := 'N';
2748:
2749: -- Fix for bug # 3123283
2750: l_line_purpose pn_rec_agr_lines_all.PURPOSE%TYPE;
2787: END IF;
2788:
2789: SELECT NVL(SUM(NVL(period_lines.actual_prorata_share,0)),0)
2790: INTO l_contr_actual_recovery
2791: FROM pn_rec_period_lines_all period_lines
2792: ,pn_rec_agreements_all recagr
2793: ,pn_rec_agr_lines_all lines
2794: WHERE period_lines.start_date = p_calc_period_start_date
2795: AND period_lines.end_date = p_calc_period_end_date
2857: | FUNCTION
2858: | GET_LINE_EXPENSES
2859: |
2860: | DESCRIPTION
2861: | Gets recoverable area from pn_rec_period_lines_all table
2862: | for a line if the calc method is 'Fixed rate'
2863: |
2864: | SCOPE - PUBLIC
2865: |
2875: | OUT:
2876: |
2877: | RETURNS : None
2878: |
2879: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2880: | for a line if the calc method is 'Fixed rate'
2881: |
2882: | MODIFICATION HISTORY
2883: |
2956: | FUNCTION
2957: | GET_BUDGET_EXPENSES
2958: |
2959: | DESCRIPTION
2960: | Gets recoverable area from pn_rec_period_lines_all table
2961: | for a line if the calc method is 'Fixed rate'
2962: |
2963: | SCOPE - PUBLIC
2964: |
2974: | OUT:
2975: |
2976: | RETURNS : None
2977: |
2978: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2979: | for a line if the calc method is 'Fixed rate'
2980: |
2981: | MODIFICATION HISTORY
2982: |
3062: | FUNCTION
3063: | GET_BILLED_RECOVERY
3064: |
3065: | DESCRIPTION
3066: | Gets recoverable area from pn_rec_period_lines_all table
3067: | for a line if the calc method is 'Fixed rate'
3068: |
3069: | SCOPE - PUBLIC
3070: |
3080: | OUT:
3081: |
3082: | RETURNS : None
3083: |
3084: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3085: | for a line if the calc method is 'Fixed rate'
3086: |
3087: | MODIFICATION HISTORY
3088: |
3104: ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
3105: ,p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
3106: ,p_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE
3107: )
3108: RETURN pn_rec_period_lines_all.billed_recovery%TYPE IS
3109:
3110: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE;
3111: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE :=
3112: PN_REC_CALC_PKG.find_if_period_line_exists(
3106: ,p_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE
3107: )
3108: RETURN pn_rec_period_lines_all.billed_recovery%TYPE IS
3109:
3110: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE;
3111: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE :=
3112: PN_REC_CALC_PKG.find_if_period_line_exists(
3113: p_rec_agr_line_id
3114: ,p_rec_calc_period_id
3107: )
3108: RETURN pn_rec_period_lines_all.billed_recovery%TYPE IS
3109:
3110: l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE;
3111: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE :=
3112: PN_REC_CALC_PKG.find_if_period_line_exists(
3113: p_rec_agr_line_id
3114: ,p_rec_calc_period_id
3115: );
3161: | FUNCTION
3162: | GET_LINE_CONSTRAINTS
3163: |
3164: | DESCRIPTION
3165: | Gets recoverable area from pn_rec_period_lines_all table
3166: | for a line if the calc method is 'Fixed rate'
3167: |
3168: | SCOPE - PUBLIC
3169: |
3179: | OUT:
3180: |
3181: | RETURNS : None
3182: |
3183: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3184: | for a line if the calc method is 'Fixed rate'
3185: |
3186: | MODIFICATION HISTORY
3187: |
3257: | FUNCTION
3258: | GET_LINE_ABATEMENTS
3259: |
3260: | DESCRIPTION
3261: | Gets recoverable area from pn_rec_period_lines_all table
3262: | for a line if the calc method is 'Fixed rate'
3263: |
3264: | SCOPE - PUBLIC
3265: |
3275: | OUT:
3276: |
3277: | RETURNS : None
3278: |
3279: | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3280: | for a line if the calc method is 'Fixed rate'
3281: |
3282: | MODIFICATION HISTORY
3283: |
3345: |
3346: | 22-MAY-2003 Daniel Thota o Created
3347: +===========================================================================*/
3348: FUNCTION find_if_period_line_exists (
3349: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
3350: ,p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
3351: )
3352: RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3353:
3346: | 22-MAY-2003 Daniel Thota o Created
3347: +===========================================================================*/
3348: FUNCTION find_if_period_line_exists (
3349: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
3350: ,p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
3351: )
3352: RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3353:
3354: CURSOR csr_chck_exist IS
3348: FUNCTION find_if_period_line_exists (
3349: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
3350: ,p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
3351: )
3352: RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3353:
3354: CURSOR csr_chck_exist IS
3355: SELECT periods.rec_period_lines_id
3356: FROM pn_rec_period_lines_all periods
3352: RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3353:
3354: CURSOR csr_chck_exist IS
3355: SELECT periods.rec_period_lines_id
3356: FROM pn_rec_period_lines_all periods
3357: WHERE periods.rec_agr_line_id = p_rec_agr_line_id
3358: AND periods.rec_calc_period_id = p_rec_calc_period_id;
3359:
3360: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE;
3356: FROM pn_rec_period_lines_all periods
3357: WHERE periods.rec_agr_line_id = p_rec_agr_line_id
3358: AND periods.rec_calc_period_id = p_rec_calc_period_id;
3359:
3360: l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE;
3361:
3362: BEGIN
3363:
3364: PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.find_if_period_line_exists (+)');
3396: | PROCEDURE
3397: | INSERT_PERIOD_LINES_ROW
3398: |
3399: | DESCRIPTION
3400: | Create records in the PN_REC_PERIOD_LINES_ALL table
3401: |
3402: | SCOPE - PUBLIC
3403: |
3404: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3408: | OUT:
3409: |
3410: | RETURNS : None
3411: |
3412: | NOTES : Create records in the PN_REC_PERIOD_LINES_ALL
3413: |
3414: | MODIFICATION HISTORY
3415: |
3416: | 21-MAY-2003 Daniel Thota o Created
3489: l_org_ID := pn_mo_cache_utils.get_current_org_id;
3490: END IF;
3491:
3492:
3493: INSERT INTO PN_REC_PERIOD_LINES_ALL (
3494: BUDGET_PCT
3495: ,OCCUPANCY_PCT
3496: ,MULTIPLE_PCT
3497: ,TENANCY_START_DATE
3589: | PROCEDURE
3590: | UPDATE_PERIOD_LINES_ROW
3591: |
3592: | DESCRIPTION
3593: | Update records in the PN_REC_PERIOD_LINES_ALL table
3594: |
3595: | SCOPE - PUBLIC
3596: |
3597: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3601: | OUT:
3602: |
3603: | RETURNS : None
3604: |
3605: | NOTES : Update records in the PN_REC_PERIOD_LINES_ALL
3606: |
3607: | MODIFICATION HISTORY
3608: |
3609: | 21-MAY-2003 Daniel Thota o Created
3649: BEGIN
3650:
3651: PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_LINES_ROW (+)');
3652:
3653: update PN_REC_PERIOD_LINES_ALL set
3654: BUDGET_PCT = X_BUDGET_PCT
3655: ,OCCUPANCY_PCT = X_OCCUPANCY_PCT
3656: ,MULTIPLE_PCT = X_MULTIPLE_PCT
3657: ,TENANCY_START_DATE = X_TENANCY_START_DATE
3704: | PROCEDURE
3705: | DELETE_PERIOD_LINES_ROW
3706: |
3707: | DESCRIPTION
3708: | Delete records in the PN_REC_PERIOD_LINES_ALL table
3709: |
3710: | SCOPE - PUBLIC
3711: |
3712: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3716: | OUT:
3717: |
3718: | RETURNS : None
3719: |
3720: | NOTES : Delete records in the PN_REC_PERIOD_LINES_ALL
3721: |
3722: | MODIFICATION HISTORY
3723: |
3724: | 21-MAY-2003 Daniel Thota o Created
3731: BEGIN
3732:
3733: PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_LINES_ROW (+)');
3734:
3735: delete from PN_REC_PERIOD_LINES_ALL
3736: where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID;
3737:
3738: if (sql%notfound) then
3739: raise no_data_found;
4768: |
4769: | 19-MAY-2003 Daniel Thota o Created
4770: +===========================================================================*/
4771: FUNCTION get_prior_period_actual_amount(
4772: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
4773: ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
4774: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4775: ,p_called_from VARCHAR2
4776: )
4773: ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
4774: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4775: ,p_called_from VARCHAR2
4776: )
4777: RETURN pn_rec_period_lines_all.constrained_actual%TYPE IS
4778:
4779: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4780: l_percent pn_rec_agr_linconst_all.value%TYPE;
4781:
4775: ,p_called_from VARCHAR2
4776: )
4777: RETURN pn_rec_period_lines_all.constrained_actual%TYPE IS
4778:
4779: l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4780: l_percent pn_rec_agr_linconst_all.value%TYPE;
4781:
4782: CURSOR csr_get_curr_percent (p_as_of_date date) is
4783: SELECT lineconst.VALUE
4792: pnp_debug_pkg.log('PN_REC_CALC_PKG.get_prior_period_actual_amount (+) ');
4793:
4794: SELECT NVL(period_lines.constrained_actual,0)
4795: INTO l_prior_period_amount
4796: FROM pn_rec_period_lines_all period_lines
4797: WHERE rec_agr_line_id = p_rec_agr_line_id
4798: AND end_date = (SELECT max(end_date)
4799: FROM pn_rec_period_lines_all
4800: WHERE start_date < p_start_date
4795: INTO l_prior_period_amount
4796: FROM pn_rec_period_lines_all period_lines
4797: WHERE rec_agr_line_id = p_rec_agr_line_id
4798: AND end_date = (SELECT max(end_date)
4799: FROM pn_rec_period_lines_all
4800: WHERE start_date < p_start_date
4801: AND end_date < p_start_date
4802: AND rec_agr_line_id = p_rec_agr_line_id) ;
4803:
4879: |
4880: | 23-aug-2003 achauhan o Created
4881: +===========================================================================*/
4882: FUNCTION get_prior_period_cap(
4883: p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
4884: ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
4885: ,p_end_date pn_rec_calc_periods_all.end_date%TYPE
4886: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4887: ,p_called_from VARCHAR2
4885: ,p_end_date pn_rec_calc_periods_all.end_date%TYPE
4886: ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4887: ,p_called_from VARCHAR2
4888: )
4889: RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
4890:
4891: l_percent pn_rec_agr_linconst_all.value%TYPE;
4892: l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4893: l_start_date pn_rec_period_lines_all.start_date%TYPE;
4888: )
4889: RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
4890:
4891: l_percent pn_rec_agr_linconst_all.value%TYPE;
4892: l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4893: l_start_date pn_rec_period_lines_all.start_date%TYPE;
4894: l_end_date pn_rec_period_lines_all.end_date%TYPE;
4895:
4896: CURSOR csr_get_base_cap is
4889: RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
4890:
4891: l_percent pn_rec_agr_linconst_all.value%TYPE;
4892: l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4893: l_start_date pn_rec_period_lines_all.start_date%TYPE;
4894: l_end_date pn_rec_period_lines_all.end_date%TYPE;
4895:
4896: CURSOR csr_get_base_cap is
4897: SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4890:
4891: l_percent pn_rec_agr_linconst_all.value%TYPE;
4892: l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4893: l_start_date pn_rec_period_lines_all.start_date%TYPE;
4894: l_end_date pn_rec_period_lines_all.end_date%TYPE;
4895:
4896: CURSOR csr_get_base_cap is
4897: SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4898: FROM pn_rec_period_lines_all period_lines
4894: l_end_date pn_rec_period_lines_all.end_date%TYPE;
4895:
4896: CURSOR csr_get_base_cap is
4897: SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4898: FROM pn_rec_period_lines_all period_lines
4899: WHERE rec_agr_line_id = p_rec_agr_line_id
4900: AND start_date = (select min(start_date)
4901: from pn_rec_period_lines_all
4902: WHERE rec_agr_line_id = p_rec_agr_line_id) ;
4897: SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4898: FROM pn_rec_period_lines_all period_lines
4899: WHERE rec_agr_line_id = p_rec_agr_line_id
4900: AND start_date = (select min(start_date)
4901: from pn_rec_period_lines_all
4902: WHERE rec_agr_line_id = p_rec_agr_line_id) ;
4903:
4904: CURSOR csr_get_prior_periods (p_start_date date, p_fst_end_date date) is
4905: SELECT lineconst.VALUE
4902: WHERE rec_agr_line_id = p_rec_agr_line_id) ;
4903:
4904: CURSOR csr_get_prior_periods (p_start_date date, p_fst_end_date date) is
4905: SELECT lineconst.VALUE
4906: FROM pn_rec_period_lines_all period_lines,
4907: pn_rec_agr_linconst_all lineconst,
4908: pn_rec_calc_periods_all recperiod
4909: WHERE period_lines.rec_agr_line_id = p_rec_agr_line_id
4910: AND recperiod.rec_calc_period_id = period_lines.rec_calc_period_id