[Home] [Help]
399: ,ppt.end_date
400: ,ppt.index_term_indicator
401: ,ppt.status
402: ,DECODE (ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE"
403: FROM pn_payment_terms_all ppt
404: WHERE ppt.index_period_id = ip_index_period_id
405: ORDER BY ppt.start_date;
406:
407: CURSOR index_periods_payments (
414: ,ppt.end_date
415: ,ppt.index_term_indicator
416: ,ppt.status
417: ,DECODE (ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE"
418: FROM pn_payment_terms_all ppt
419: WHERE ppt.index_period_id = ip_index_period_id
420: AND ( ppt.payment_term_id = ip_payment_term_id
421: OR ip_payment_term_id IS NULL
422: )
827: ,c_spread_frequency_one_time, 0
828: )
829: )
830: INTO p_prev_index_amount
831: FROM pn_payment_terms_all ppt, pn_index_lease_periods_all ppi
832: WHERE ppt.index_period_id = ppi.index_period_id
833: AND ppi.index_lease_id = p_index_lease_id
834: AND ppi.assessment_date < (SELECT assessment_date
835: FROM pn_index_lease_periods_all
894: -- get_profile_value
895: -- 14-AUG-06 pikhar o Added check to find out if contributing terms have
896: -- include_in_var_rent = 'INCLUDE_RI'
897: -- 23-SEP-06 prabhakar o Modified the curosrs to fetch terms from pn_index_exclude_term_all
898: -- with flag 'I' or from pn_payment_terms_all
899: -- 27-NOV-07 acprakas o Bug#6457105. Modified to consider new values for system option incl_terms_by_default_flag.
900: --------------------------------------------------------------------------------------------
901:
902: PROCEDURE sum_payment_items (
909: ) IS
910:
911: l_count NUMBER := 0;
912: l_total_sum NUMBER := 0;
913: l_amount PN_PAYMENT_TERMS.ACTUAL_AMOUNT%TYPE;
914: l_frequency PN_PAYMENT_TERMS.FREQUENCY_CODE%TYPE;
915: l_payment_term_id PN_PAYMENT_TERMS.PAYMENT_TERM_ID%TYPE;
916: l_payments NUMBER := 0;
917: /* profile to determine how to calculate annualized basis */
910:
911: l_count NUMBER := 0;
912: l_total_sum NUMBER := 0;
913: l_amount PN_PAYMENT_TERMS.ACTUAL_AMOUNT%TYPE;
914: l_frequency PN_PAYMENT_TERMS.FREQUENCY_CODE%TYPE;
915: l_payment_term_id PN_PAYMENT_TERMS.PAYMENT_TERM_ID%TYPE;
916: l_payments NUMBER := 0;
917: /* profile to determine how to calculate annualized basis */
918: l_calc_annualized_basis VARCHAR2(30);
911: l_count NUMBER := 0;
912: l_total_sum NUMBER := 0;
913: l_amount PN_PAYMENT_TERMS.ACTUAL_AMOUNT%TYPE;
914: l_frequency PN_PAYMENT_TERMS.FREQUENCY_CODE%TYPE;
915: l_payment_term_id PN_PAYMENT_TERMS.PAYMENT_TERM_ID%TYPE;
916: l_payments NUMBER := 0;
917: /* profile to determine how to calculate annualized basis */
918: l_calc_annualized_basis VARCHAR2(30);
919: l_org_id NUMBER;
928: CURSOR csr_exc_get_item_period (p_payment_type VARCHAR2,p_org_id NUMBER) IS
929: SELECT ppt.payment_term_id
930: ,NVL(ppt.actual_amount, ppt.estimated_amount)
931: ,ppt.frequency_code
932: FROM pn_payment_terms_all ppt
933: ,pn_index_leases_all pil
934: WHERE pil.index_lease_id = p_index_lease_id
935: AND ppt.lease_id = pil.lease_id
936: AND ppt.payment_term_type_code
968: CURSOR csr_exc_get_item_enddate (p_payment_type VARCHAR2, p_org_id NUMBER) IS
969: SELECT ppt.payment_term_id
970: ,NVL(ppt.actual_amount, ppt.estimated_amount)
971: ,ppt.frequency_code
972: FROM pn_payment_terms_all ppt
973: ,pn_index_leases_all pil
974: WHERE pil.index_lease_id = p_index_lease_id
975: AND ppt.lease_id = pil.lease_id
976: AND ppt.payment_term_type_code
1010: CURSOR csr_inc_get_item_period (p_payment_type VARCHAR2,p_org_id NUMBER) IS
1011: SELECT ppt.payment_term_id
1012: ,NVL(ppt.actual_amount,ppt.estimated_amount)
1013: ,ppt.frequency_code
1014: FROM pn_payment_terms_all ppt
1015: ,pn_index_leases_all pil
1016: WHERE pil.index_lease_id = p_index_lease_id
1017: AND ppt.lease_id = pil.lease_id
1018: AND ppt.payment_term_type_code
1046: CURSOR csr_inc_get_item_enddate (p_payment_type VARCHAR2,p_org_id NUMBER) IS
1047: SELECT ppt.payment_term_id
1048: ,NVL(ppt.actual_amount,ppt.estimated_amount)
1049: ,ppt.frequency_code
1050: FROM pn_payment_terms_all ppt
1051: ,pn_index_leases_all pil
1052: WHERE pil.index_lease_id = p_index_lease_id
1053: AND ppt.lease_id = pil.lease_id
1054: AND ppt.payment_term_type_code
1240:
1241: IF l_increase_on is NOT NULL THEN
1242: SELECT include_in_var_rent
1243: INTO l_include_in_var_rent
1244: FROM pn_payment_terms_all ppt
1245: WHERE ppt.payment_term_id = l_payment_term_id
1246: AND ppt.payment_term_type_code = nvl(p_type_code,ppt.payment_term_type_code);
1247: ELSE
1248: SELECT include_in_var_rent
1246: AND ppt.payment_term_type_code = nvl(p_type_code,ppt.payment_term_type_code);
1247: ELSE
1248: SELECT include_in_var_rent
1249: INTO l_include_in_var_rent
1250: FROM pn_payment_terms_all ppt
1251: WHERE ppt.payment_term_id = l_payment_term_id;
1252: END IF;
1253:
1254: IF l_count = 1 THEN
2406: ,op_normalize_amount_annual OUT NOCOPY NUMBER
2407: ,op_msg OUT NOCOPY VARCHAR2
2408: ) IS
2409: v_found_normalize NUMBER (1);
2410: v_normalized_amount pn_payment_terms.actual_amount%TYPE;
2411: v_normalized_frequency pn_payment_terms.frequency_code%TYPE;
2412: v_multiplier NUMBER (3);
2413:
2414:
2407: ,op_msg OUT NOCOPY VARCHAR2
2408: ) IS
2409: v_found_normalize NUMBER (1);
2410: v_normalized_amount pn_payment_terms.actual_amount%TYPE;
2411: v_normalized_frequency pn_payment_terms.frequency_code%TYPE;
2412: v_multiplier NUMBER (3);
2413:
2414:
2415: /* cursor to select all normalized payment terms recurring payment that are APPROVED and not normalized.*/
2416:
2417: CURSOR c1 IS
2418: SELECT ppt.actual_amount
2419: ,ppt.frequency_code
2420: FROM pn_payment_terms_all ppt
2421: WHERE ppt.index_period_id = p_index_period_id
2422: AND ppt.index_term_indicator = c_index_pay_term_type_atlst;
2423:
2424: BEGIN
2469: -- cursor to select all recurring payment that are APPROVED.
2470: --
2471: CURSOR c1 IS
2472: SELECT ppt.payment_term_id, ppt.actual_amount, ppt.frequency_code
2473: FROM pn_payment_terms_all ppt
2474: WHERE ppt.index_period_id = p_index_period_id
2475: AND ppt.status =c_payment_term_status_approved
2476: AND ppt.index_term_indicator = p_index_term_indicator;
2477: BEGIN
2520: ,op_approved_amount_annual OUT NOCOPY NUMBER
2521: ,op_msg OUT NOCOPY VARCHAR2
2522: ) IS
2523: v_found_approved NUMBER (1);
2524: v_approved_amount pn_payment_terms.actual_amount%TYPE;
2525: v_approved_frequency pn_payment_terms.frequency_code%TYPE;
2526: v_all_approved_amounts NUMBER := 0;
2527: v_multiplier NUMBER (3);
2528: v_exists NUMBER := 0;
2521: ,op_msg OUT NOCOPY VARCHAR2
2522: ) IS
2523: v_found_approved NUMBER (1);
2524: v_approved_amount pn_payment_terms.actual_amount%TYPE;
2525: v_approved_frequency pn_payment_terms.frequency_code%TYPE;
2526: v_all_approved_amounts NUMBER := 0;
2527: v_multiplier NUMBER (3);
2528: v_exists NUMBER := 0;
2529:
2541: ,ip_index_term_indicator IN VARCHAR2
2542: ,ip_normalize IN VARCHAR2
2543: ,ip_index_period_id IN NUMBER)IS
2544: SELECT ppt.payment_term_id, ppt.actual_amount,ppt.frequency_code
2545: FROM pn_payment_terms_all ppt,
2546: pn_payment_items_all ppi
2547: WHERE ppt.index_period_id = ip_index_period_id
2548: AND ppt.status =c_payment_term_status_approved
2549: AND ppt.index_term_indicator = ip_index_term_indicator
2577: BEGIN
2578:
2579: SELECT '1'
2580: INTO v_exists
2581: FROM pn_payment_terms_all
2582: WHERE index_period_id = p_index_period_id
2583: AND status = c_payment_term_status_approved
2584: AND index_term_indicator = p_index_term_indicator;
2585:
3009: ,ppt.frequency_code
3010: ,ppt.include_in_var_rent
3011: ,build_distributions_string (ppt.payment_term_id) "DISTRIBUTION_STRING"
3012: ,NVL (ppt.normalize, 'N') "NORMALIZE"
3013: FROM pn_payment_terms_all ppt,
3014: pn_index_leases_all pil
3015: WHERE pil.index_lease_id = ip_index_lease_id
3016: AND ppt.lease_id = pil.lease_id
3017: AND nvl(ppt.status,'-1') = decode(ppt.index_period_id,null,nvl(ppt.status,'-1'),'APPROVED')
3069: ) IS
3070: SELECT ppt.payment_term_id,nvl(ppt.actual_amount,ppt.estimated_amount)
3071: ,ppt.frequency_code
3072: ,ppt.norm_start_date
3073: FROM pn_payment_terms_all ppt
3074: WHERE ppt.payment_purpose_code = ip_payment_purpose_code
3075: AND ppt.payment_term_type_code = ip_payment_term_type_code
3076: AND nvl(ppt.include_in_var_rent,'N') = nvl(ip_include_in_var_rent,'N')
3077: AND nvl(ppt.status,'-1') = decode(ppt.index_period_id,null,nvl(ppt.status,'-1'),'APPROVED')
3129: normalize,
3130: schedule_day,
3131: end_date,
3132: include_in_var_rent
3133: FROM pn_payment_terms_all
3134: WHERE lease_id = p_lease_id
3135: AND index_period_id = p_index_period_id
3136: AND status = c_payment_term_status_approved;
3137:
3151: ,ip_index_period_id IN NUMBER
3152: ,ip_include_in_var_rent IN VARCHAR2
3153: ) IS
3154: SELECT nvl(ppt.actual_amount,0) actual_amount, ppt.index_term_indicator, ppt.payment_term_id
3155: FROM pn_payment_terms_all ppt
3156: WHERE ppt.payment_purpose_code = ip_payment_purpose_code
3157: AND ppt.payment_term_type_code = ip_payment_term_type_code
3158: AND ppt.index_period_id = ip_index_period_id
3159: AND nvl(ppt.status,'-1') = 'APPROVED'
3190: -- Therefore, the start date should be assessment date; the end date should be
3191: -- => last_day_of(month_of(recurring term start date) - 1).
3192:
3193: CURSOR cur_sum_backbill_overlap_items (
3194: p_end_date pn_payment_terms.end_date%TYPE
3195: ,p_location_id pn_payment_terms.location_id%TYPE
3196: ,p_payment_purpose_code pn_payment_terms.payment_purpose_code%TYPE
3197: ,p_payment_term_type_code pn_payment_terms.payment_term_type_code%TYPE
3198: ,p_vendor_id pn_payment_terms.vendor_id%TYPE
3191: -- => last_day_of(month_of(recurring term start date) - 1).
3192:
3193: CURSOR cur_sum_backbill_overlap_items (
3194: p_end_date pn_payment_terms.end_date%TYPE
3195: ,p_location_id pn_payment_terms.location_id%TYPE
3196: ,p_payment_purpose_code pn_payment_terms.payment_purpose_code%TYPE
3197: ,p_payment_term_type_code pn_payment_terms.payment_term_type_code%TYPE
3198: ,p_vendor_id pn_payment_terms.vendor_id%TYPE
3199: ,p_vendor_site_id pn_payment_terms.vendor_site_id%TYPE
3192:
3193: CURSOR cur_sum_backbill_overlap_items (
3194: p_end_date pn_payment_terms.end_date%TYPE
3195: ,p_location_id pn_payment_terms.location_id%TYPE
3196: ,p_payment_purpose_code pn_payment_terms.payment_purpose_code%TYPE
3197: ,p_payment_term_type_code pn_payment_terms.payment_term_type_code%TYPE
3198: ,p_vendor_id pn_payment_terms.vendor_id%TYPE
3199: ,p_vendor_site_id pn_payment_terms.vendor_site_id%TYPE
3200: ,p_customer_id pn_payment_terms.customer_id%TYPE
3193: CURSOR cur_sum_backbill_overlap_items (
3194: p_end_date pn_payment_terms.end_date%TYPE
3195: ,p_location_id pn_payment_terms.location_id%TYPE
3196: ,p_payment_purpose_code pn_payment_terms.payment_purpose_code%TYPE
3197: ,p_payment_term_type_code pn_payment_terms.payment_term_type_code%TYPE
3198: ,p_vendor_id pn_payment_terms.vendor_id%TYPE
3199: ,p_vendor_site_id pn_payment_terms.vendor_site_id%TYPE
3200: ,p_customer_id pn_payment_terms.customer_id%TYPE
3201: ,p_customer_site_use_id pn_payment_terms.customer_site_use_id%TYPE
3194: p_end_date pn_payment_terms.end_date%TYPE
3195: ,p_location_id pn_payment_terms.location_id%TYPE
3196: ,p_payment_purpose_code pn_payment_terms.payment_purpose_code%TYPE
3197: ,p_payment_term_type_code pn_payment_terms.payment_term_type_code%TYPE
3198: ,p_vendor_id pn_payment_terms.vendor_id%TYPE
3199: ,p_vendor_site_id pn_payment_terms.vendor_site_id%TYPE
3200: ,p_customer_id pn_payment_terms.customer_id%TYPE
3201: ,p_customer_site_use_id pn_payment_terms.customer_site_use_id%TYPE
3202: ,p_distribution_string VARCHAR2
3195: ,p_location_id pn_payment_terms.location_id%TYPE
3196: ,p_payment_purpose_code pn_payment_terms.payment_purpose_code%TYPE
3197: ,p_payment_term_type_code pn_payment_terms.payment_term_type_code%TYPE
3198: ,p_vendor_id pn_payment_terms.vendor_id%TYPE
3199: ,p_vendor_site_id pn_payment_terms.vendor_site_id%TYPE
3200: ,p_customer_id pn_payment_terms.customer_id%TYPE
3201: ,p_customer_site_use_id pn_payment_terms.customer_site_use_id%TYPE
3202: ,p_distribution_string VARCHAR2
3203: ,p_normalize pn_payment_terms.normalize%TYPE
3196: ,p_payment_purpose_code pn_payment_terms.payment_purpose_code%TYPE
3197: ,p_payment_term_type_code pn_payment_terms.payment_term_type_code%TYPE
3198: ,p_vendor_id pn_payment_terms.vendor_id%TYPE
3199: ,p_vendor_site_id pn_payment_terms.vendor_site_id%TYPE
3200: ,p_customer_id pn_payment_terms.customer_id%TYPE
3201: ,p_customer_site_use_id pn_payment_terms.customer_site_use_id%TYPE
3202: ,p_distribution_string VARCHAR2
3203: ,p_normalize pn_payment_terms.normalize%TYPE
3204: ) IS
3197: ,p_payment_term_type_code pn_payment_terms.payment_term_type_code%TYPE
3198: ,p_vendor_id pn_payment_terms.vendor_id%TYPE
3199: ,p_vendor_site_id pn_payment_terms.vendor_site_id%TYPE
3200: ,p_customer_id pn_payment_terms.customer_id%TYPE
3201: ,p_customer_site_use_id pn_payment_terms.customer_site_use_id%TYPE
3202: ,p_distribution_string VARCHAR2
3203: ,p_normalize pn_payment_terms.normalize%TYPE
3204: ) IS
3205: SELECT sum(item.actual_amount) sum_overlap_amt
3199: ,p_vendor_site_id pn_payment_terms.vendor_site_id%TYPE
3200: ,p_customer_id pn_payment_terms.customer_id%TYPE
3201: ,p_customer_site_use_id pn_payment_terms.customer_site_use_id%TYPE
3202: ,p_distribution_string VARCHAR2
3203: ,p_normalize pn_payment_terms.normalize%TYPE
3204: ) IS
3205: SELECT sum(item.actual_amount) sum_overlap_amt
3206: FROM pn_payment_items_all item,
3207: pn_payment_terms_all term
3203: ,p_normalize pn_payment_terms.normalize%TYPE
3204: ) IS
3205: SELECT sum(item.actual_amount) sum_overlap_amt
3206: FROM pn_payment_items_all item,
3207: pn_payment_terms_all term
3208: WHERE item.payment_term_id = term.payment_term_id
3209: AND item.due_date BETWEEN p_assessment_date AND p_end_date
3210: AND term.index_period_id = p_index_period_id
3211: AND term.index_term_indicator = p_index_term_indicator
3242: v_previous_backbill_amt NUMBER := 0;
3243: v_basis_start_date pn_index_lease_periods.basis_start_date%type;
3244: v_basis_end_date pn_index_lease_periods.basis_end_date%type;
3245: v_msg VARCHAR2(100);
3246: prev_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3247: prev_payment_term_type_code pn_payment_terms.payment_term_type_code%type := null;
3248: prev_location_id pn_payment_terms.location_id%type := null;
3249: prev_vendor_id pn_payment_terms.vendor_id%type := null;
3250: prev_vendor_site_id pn_payment_terms.vendor_site_id%type := null;
3243: v_basis_start_date pn_index_lease_periods.basis_start_date%type;
3244: v_basis_end_date pn_index_lease_periods.basis_end_date%type;
3245: v_msg VARCHAR2(100);
3246: prev_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3247: prev_payment_term_type_code pn_payment_terms.payment_term_type_code%type := null;
3248: prev_location_id pn_payment_terms.location_id%type := null;
3249: prev_vendor_id pn_payment_terms.vendor_id%type := null;
3250: prev_vendor_site_id pn_payment_terms.vendor_site_id%type := null;
3251: prev_customer_id pn_payment_terms.customer_id%type := null;
3244: v_basis_end_date pn_index_lease_periods.basis_end_date%type;
3245: v_msg VARCHAR2(100);
3246: prev_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3247: prev_payment_term_type_code pn_payment_terms.payment_term_type_code%type := null;
3248: prev_location_id pn_payment_terms.location_id%type := null;
3249: prev_vendor_id pn_payment_terms.vendor_id%type := null;
3250: prev_vendor_site_id pn_payment_terms.vendor_site_id%type := null;
3251: prev_customer_id pn_payment_terms.customer_id%type := null;
3252: prev_customer_site_use_id pn_payment_terms.customer_site_use_id%type := null;
3245: v_msg VARCHAR2(100);
3246: prev_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3247: prev_payment_term_type_code pn_payment_terms.payment_term_type_code%type := null;
3248: prev_location_id pn_payment_terms.location_id%type := null;
3249: prev_vendor_id pn_payment_terms.vendor_id%type := null;
3250: prev_vendor_site_id pn_payment_terms.vendor_site_id%type := null;
3251: prev_customer_id pn_payment_terms.customer_id%type := null;
3252: prev_customer_site_use_id pn_payment_terms.customer_site_use_id%type := null;
3253: prev_distribution_string VARCHAR2(2000) := null;
3246: prev_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3247: prev_payment_term_type_code pn_payment_terms.payment_term_type_code%type := null;
3248: prev_location_id pn_payment_terms.location_id%type := null;
3249: prev_vendor_id pn_payment_terms.vendor_id%type := null;
3250: prev_vendor_site_id pn_payment_terms.vendor_site_id%type := null;
3251: prev_customer_id pn_payment_terms.customer_id%type := null;
3252: prev_customer_site_use_id pn_payment_terms.customer_site_use_id%type := null;
3253: prev_distribution_string VARCHAR2(2000) := null;
3254: prev_normalize VARCHAR2(10) := null;
3247: prev_payment_term_type_code pn_payment_terms.payment_term_type_code%type := null;
3248: prev_location_id pn_payment_terms.location_id%type := null;
3249: prev_vendor_id pn_payment_terms.vendor_id%type := null;
3250: prev_vendor_site_id pn_payment_terms.vendor_site_id%type := null;
3251: prev_customer_id pn_payment_terms.customer_id%type := null;
3252: prev_customer_site_use_id pn_payment_terms.customer_site_use_id%type := null;
3253: prev_distribution_string VARCHAR2(2000) := null;
3254: prev_normalize VARCHAR2(10) := null;
3255: prev_frequency_code pn_payment_terms.frequency_code%TYPE;
3248: prev_location_id pn_payment_terms.location_id%type := null;
3249: prev_vendor_id pn_payment_terms.vendor_id%type := null;
3250: prev_vendor_site_id pn_payment_terms.vendor_site_id%type := null;
3251: prev_customer_id pn_payment_terms.customer_id%type := null;
3252: prev_customer_site_use_id pn_payment_terms.customer_site_use_id%type := null;
3253: prev_distribution_string VARCHAR2(2000) := null;
3254: prev_normalize VARCHAR2(10) := null;
3255: prev_frequency_code pn_payment_terms.frequency_code%TYPE;
3256: prev_end_date pn_payment_terms.end_date%TYPE;
3251: prev_customer_id pn_payment_terms.customer_id%type := null;
3252: prev_customer_site_use_id pn_payment_terms.customer_site_use_id%type := null;
3253: prev_distribution_string VARCHAR2(2000) := null;
3254: prev_normalize VARCHAR2(10) := null;
3255: prev_frequency_code pn_payment_terms.frequency_code%TYPE;
3256: prev_end_date pn_payment_terms.end_date%TYPE;
3257: v_overlap_backbill_amt NUMBER := 0;
3258: prev_include_in_var_rent VARCHAR2(30) := null;
3259: v_count NUMBER := 0;
3252: prev_customer_site_use_id pn_payment_terms.customer_site_use_id%type := null;
3253: prev_distribution_string VARCHAR2(2000) := null;
3254: prev_normalize VARCHAR2(10) := null;
3255: prev_frequency_code pn_payment_terms.frequency_code%TYPE;
3256: prev_end_date pn_payment_terms.end_date%TYPE;
3257: v_overlap_backbill_amt NUMBER := 0;
3258: prev_include_in_var_rent VARCHAR2(30) := null;
3259: v_count NUMBER := 0;
3260: v_source_payment_term_id NUMBER := 0;
3258: prev_include_in_var_rent VARCHAR2(30) := null;
3259: v_count NUMBER := 0;
3260: v_source_payment_term_id NUMBER := 0;
3261: v_prev_payment_term_id NUMBER := 0;
3262: curr_location_id pn_payment_terms.location_id%type := null;
3263: curr_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3264: curr_frequency_code pn_payment_terms.frequency_code%type := null;
3265: --curr_include_in_var_rent pn_payment_terms.include_in_var_rent%type := null;
3266: curr_include_in_var_rent VARCHAR2(30);
3259: v_count NUMBER := 0;
3260: v_source_payment_term_id NUMBER := 0;
3261: v_prev_payment_term_id NUMBER := 0;
3262: curr_location_id pn_payment_terms.location_id%type := null;
3263: curr_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3264: curr_frequency_code pn_payment_terms.frequency_code%type := null;
3265: --curr_include_in_var_rent pn_payment_terms.include_in_var_rent%type := null;
3266: curr_include_in_var_rent VARCHAR2(30);
3267: p_normalized VARCHAR2(1):='N';
3260: v_source_payment_term_id NUMBER := 0;
3261: v_prev_payment_term_id NUMBER := 0;
3262: curr_location_id pn_payment_terms.location_id%type := null;
3263: curr_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3264: curr_frequency_code pn_payment_terms.frequency_code%type := null;
3265: --curr_include_in_var_rent pn_payment_terms.include_in_var_rent%type := null;
3266: curr_include_in_var_rent VARCHAR2(30);
3267: p_normalized VARCHAR2(1):='N';
3268: p_backbill_term_indicator VARCHAR2(100) := null;
3261: v_prev_payment_term_id NUMBER := 0;
3262: curr_location_id pn_payment_terms.location_id%type := null;
3263: curr_payment_purpose_code pn_payment_terms.payment_purpose_code%type := null;
3264: curr_frequency_code pn_payment_terms.frequency_code%type := null;
3265: --curr_include_in_var_rent pn_payment_terms.include_in_var_rent%type := null;
3266: curr_include_in_var_rent VARCHAR2(30);
3267: p_normalized VARCHAR2(1):='N';
3268: p_backbill_term_indicator VARCHAR2(100) := null;
3269: v_date VARCHAR2(100);
3272: v_existing_payment_amount NUMBER := 0;
3273: v_previous_exist_backbill NUMBER := 0;
3274: v_total_exist_backbill NUMBER := 0;
3275: v_existing_backbill NUMBER := 0;
3276: v_created_payment_term_id PN_PAYMENT_TERMS.PAYMENT_TERM_ID%TYPE;
3277: l_frequency PN_PAYMENT_TERMS.FREQUENCY_CODE%TYPE;
3278: l_count NUMBER := 0;
3279: i NUMBER := 0;
3280: l_amount NUMBER := 0;
3273: v_previous_exist_backbill NUMBER := 0;
3274: v_total_exist_backbill NUMBER := 0;
3275: v_existing_backbill NUMBER := 0;
3276: v_created_payment_term_id PN_PAYMENT_TERMS.PAYMENT_TERM_ID%TYPE;
3277: l_frequency PN_PAYMENT_TERMS.FREQUENCY_CODE%TYPE;
3278: l_count NUMBER := 0;
3279: i NUMBER := 0;
3280: l_amount NUMBER := 0;
3281:
3283:
3284: exist_term_rec term_rec;
3285:
3286: TYPE exist_payment IS TABLE OF exist_term_rec%TYPE INDEX BY BINARY_INTEGER;
3287: TYPE payment_term_tbl IS TABLE OF pn_payment_terms_all%ROWTYPE INDEX BY BINARY_INTEGER;
3288: TYPE id_tbl IS TABLE OF pn_payment_terms.payment_term_id%TYPE INDEX BY BINARY_INTEGER;
3289:
3290: exist_term_tab exist_payment;
3291: appr_ind_lease_tbl exist_payment;
3284: exist_term_rec term_rec;
3285:
3286: TYPE exist_payment IS TABLE OF exist_term_rec%TYPE INDEX BY BINARY_INTEGER;
3287: TYPE payment_term_tbl IS TABLE OF pn_payment_terms_all%ROWTYPE INDEX BY BINARY_INTEGER;
3288: TYPE id_tbl IS TABLE OF pn_payment_terms.payment_term_id%TYPE INDEX BY BINARY_INTEGER;
3289:
3290: exist_term_tab exist_payment;
3291: appr_ind_lease_tbl exist_payment;
3292:
3294: l_impacted_term_tbl payment_term_tbl;
3295: l_chklist_tbl id_tbl;
3296: l_used BOOLEAN;
3297: l_nxt_schdate DATE;
3298: l_day pn_payment_terms.schedule_day%TYPE;
3299: l_org_id NUMBER;
3300:
3301: l_lst_norm_st_date DATE;
3302: l_this_norm_st_date DATE;
4187: -- 12-JUL-2005 Mrinal Misra o Added NOCOPY with OUT parameter.
4188: ------------------------------------------------------------------------
4189:
4190: PROCEDURE get_backbill_overlap_amt(
4191: p_term_template_id pn_payment_terms.term_template_id%TYPE,
4192: p_index_period_id pn_payment_terms.index_period_id%TYPE,
4193: p_index_term_indicator pn_payment_terms.index_term_indicator%TYPE,
4194: p_start_date pn_payment_terms.start_date%TYPE,
4195: p_end_date pn_payment_terms.end_date%TYPE,
4188: ------------------------------------------------------------------------
4189:
4190: PROCEDURE get_backbill_overlap_amt(
4191: p_term_template_id pn_payment_terms.term_template_id%TYPE,
4192: p_index_period_id pn_payment_terms.index_period_id%TYPE,
4193: p_index_term_indicator pn_payment_terms.index_term_indicator%TYPE,
4194: p_start_date pn_payment_terms.start_date%TYPE,
4195: p_end_date pn_payment_terms.end_date%TYPE,
4196: p_overlap_amt OUT NOCOPY NUMBER)
4189:
4190: PROCEDURE get_backbill_overlap_amt(
4191: p_term_template_id pn_payment_terms.term_template_id%TYPE,
4192: p_index_period_id pn_payment_terms.index_period_id%TYPE,
4193: p_index_term_indicator pn_payment_terms.index_term_indicator%TYPE,
4194: p_start_date pn_payment_terms.start_date%TYPE,
4195: p_end_date pn_payment_terms.end_date%TYPE,
4196: p_overlap_amt OUT NOCOPY NUMBER)
4197: IS
4190: PROCEDURE get_backbill_overlap_amt(
4191: p_term_template_id pn_payment_terms.term_template_id%TYPE,
4192: p_index_period_id pn_payment_terms.index_period_id%TYPE,
4193: p_index_term_indicator pn_payment_terms.index_term_indicator%TYPE,
4194: p_start_date pn_payment_terms.start_date%TYPE,
4195: p_end_date pn_payment_terms.end_date%TYPE,
4196: p_overlap_amt OUT NOCOPY NUMBER)
4197: IS
4198: CURSOR get_overlap_sum IS
4191: p_term_template_id pn_payment_terms.term_template_id%TYPE,
4192: p_index_period_id pn_payment_terms.index_period_id%TYPE,
4193: p_index_term_indicator pn_payment_terms.index_term_indicator%TYPE,
4194: p_start_date pn_payment_terms.start_date%TYPE,
4195: p_end_date pn_payment_terms.end_date%TYPE,
4196: p_overlap_amt OUT NOCOPY NUMBER)
4197: IS
4198: CURSOR get_overlap_sum IS
4199: SELECT sum(item.actual_amount) sum_overlap_amt
4197: IS
4198: CURSOR get_overlap_sum IS
4199: SELECT sum(item.actual_amount) sum_overlap_amt
4200: FROM pn_payment_items_all item,
4201: pn_payment_terms_all term
4202: WHERE item.payment_term_id = term.payment_term_id
4203: AND term.term_template_id = p_term_template_id
4204: AND term.index_period_id = p_index_period_id
4205: AND term.index_term_indicator = p_index_term_indicator
4292: v_recurring_payment_start_date DATE;
4293: v_normalize_basis_amount NUMBER;
4294: v_basis_amount NUMBER;
4295: v_annual_basis_amount NUMBER;
4296: v_existing_amounts pn_payment_terms.actual_amount%TYPE;
4297: v_prev_index_amt NUMBER;
4298: v_adjusted_amount_aggr NUMBER;
4299: v_atleast_indicator VARCHAR2(1):= 'N';
4300: v_prv_normalized_amount NUMBER := 0;
4514:
4515: END IF;
4516:
4517:
4518: /* create a record on pn_payment_terms table if main lease termination
4519: date > sysdate.
4520: Fix for bug# 2007492 */
4521:
4522: IF TRUNC(v_main_lease_termination_date) > p_calculate_date AND
4703:
4704: IF v_normalized_amount_annual = 0 THEN
4705: SELECT count(*)
4706: INTO l_found_atlst_bb
4707: FROM pn_payment_terms_all
4708: WHERE index_period_id = p_index_period_id
4709: AND index_term_indicator = c_index_pay_term_type_atlst_bb;
4710: END IF;
4711:
4799: IF v_adjusted_amount IS NOT NULL THEN
4800:
4801: /* creating the payment term record
4802: added on 22-AUG-01
4803: create a record on pn_payment_terms table if main lease termination
4804: date > sysdate.
4805: Fix for bug# 2007492 */
4806:
4807: IF TRUNC(v_main_lease_termination_date) > p_calculate_date AND
5099: v_inv_rule_id pn_term_templates.inv_rule_id%TYPE;
5100: v_account_rule_id pn_term_templates.account_rule_id%TYPE;
5101: v_salesrep_id pn_term_templates.salesrep_id%TYPE;
5102: v_set_of_books_id pn_term_templates.set_of_books_id%TYPE;
5103: v_currency_code pn_payment_terms.currency_code%TYPE;
5104: v_po_header_id pn_term_templates.po_header_id%TYPE;
5105: v_cust_po_number pn_term_templates.cust_po_number%TYPE;
5106: v_receipt_method_id pn_term_templates.receipt_method_id%TYPE;
5107: v_attribute_category pn_term_templates.attribute_category%TYPE;
5124: v_lease_change_id pn_lease_details.lease_change_id%TYPE;
5125: v_lease_class_code pn_leases.lease_class_code%TYPE;
5126: v_distribution_id pn_distributions.distribution_id%TYPE;
5127: v_rowid VARCHAR2 (100);
5128: v_payment_term_id pn_payment_terms.payment_term_id%TYPE;
5129: v_freq_divisor NUMBER;
5130: v_converted_amount NUMBER;
5131: v_actual_amount pn_payment_terms.actual_amount%TYPE;
5132: v_gl_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
5127: v_rowid VARCHAR2 (100);
5128: v_payment_term_id pn_payment_terms.payment_term_id%TYPE;
5129: v_freq_divisor NUMBER;
5130: v_converted_amount NUMBER;
5131: v_actual_amount pn_payment_terms.actual_amount%TYPE;
5132: v_gl_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
5133: v_expense_account_id pn_lease_details.expense_account_id%TYPE;
5134: v_receivable_account_id pn_lease_details.receivable_account_id%TYPE;
5135: v_account_class pn_distributions.account_class%TYPE;
5134: v_receivable_account_id pn_lease_details.receivable_account_id%TYPE;
5135: v_account_class pn_distributions.account_class%TYPE;
5136: v_payment_end_date DATE;
5137: v_num_years NUMBER;
5138: rec_payment_details pn_payment_terms%ROWTYPE;
5139: c_rec pn_distributions%ROWTYPE;
5140: v_frequency_code pn_payment_terms.frequency_code%type;
5141: v_location_id pn_payment_terms.location_id%type;
5142: v_payment_start_date DATE := null;
5136: v_payment_end_date DATE;
5137: v_num_years NUMBER;
5138: rec_payment_details pn_payment_terms%ROWTYPE;
5139: c_rec pn_distributions%ROWTYPE;
5140: v_frequency_code pn_payment_terms.frequency_code%type;
5141: v_location_id pn_payment_terms.location_id%type;
5142: v_payment_start_date DATE := null;
5143: v_term_end_date DATE := null;
5144: v_assessment_date DATE := null;
5137: v_num_years NUMBER;
5138: rec_payment_details pn_payment_terms%ROWTYPE;
5139: c_rec pn_distributions%ROWTYPE;
5140: v_frequency_code pn_payment_terms.frequency_code%type;
5141: v_location_id pn_payment_terms.location_id%type;
5142: v_payment_start_date DATE := null;
5143: v_term_end_date DATE := null;
5144: v_assessment_date DATE := null;
5145: v_org_id pn_payment_terms_all.org_id%type;
5141: v_location_id pn_payment_terms.location_id%type;
5142: v_payment_start_date DATE := null;
5143: v_term_end_date DATE := null;
5144: v_assessment_date DATE := null;
5145: v_org_id pn_payment_terms_all.org_id%type;
5146: v_area pn_payment_terms.area%TYPE;
5147: v_area_type_code pn_payment_terms.area_type_code%TYPE;
5148: l_org_id NUMBER;
5149: l_norm_st_date DATE := NULL;
5142: v_payment_start_date DATE := null;
5143: v_term_end_date DATE := null;
5144: v_assessment_date DATE := null;
5145: v_org_id pn_payment_terms_all.org_id%type;
5146: v_area pn_payment_terms.area%TYPE;
5147: v_area_type_code pn_payment_terms.area_type_code%TYPE;
5148: l_org_id NUMBER;
5149: l_norm_st_date DATE := NULL;
5150: v_include_in_var_rent pn_payment_terms_all.include_in_var_rent%TYPE ;
5143: v_term_end_date DATE := null;
5144: v_assessment_date DATE := null;
5145: v_org_id pn_payment_terms_all.org_id%type;
5146: v_area pn_payment_terms.area%TYPE;
5147: v_area_type_code pn_payment_terms.area_type_code%TYPE;
5148: l_org_id NUMBER;
5149: l_norm_st_date DATE := NULL;
5150: v_include_in_var_rent pn_payment_terms_all.include_in_var_rent%TYPE ;
5151:
5146: v_area pn_payment_terms.area%TYPE;
5147: v_area_type_code pn_payment_terms.area_type_code%TYPE;
5148: l_org_id NUMBER;
5149: l_norm_st_date DATE := NULL;
5150: v_include_in_var_rent pn_payment_terms_all.include_in_var_rent%TYPE ;
5151:
5152: CURSOR distributions (ip_term_template_id IN NUMBER)
5153: IS
5154: SELECT *
5294: ,v_attribute15
5295: ,v_frequency_code
5296: ,v_location_id
5297: ,v_org_id
5298: FROM pn_payment_terms_all
5299: WHERE payment_term_id = p_payment_term_id;
5300: ELSE
5301: SELECT name
5302: ,normalize
5618: p_area_type_code => v_area_type_code);
5619:
5620: END IF;
5621:
5622: /* Create a record in PN_PAYMENT_TERMS table */
5623:
5624: IF ( v_actual_amount <> 0
5625: AND abs(v_actual_amount) > nvl(pn_mo_cache_utils.get_profile_value('SMALLEST_TERM_AMOUNT',v_org_id),0)
5626: ) THEN --#@#Bug4291907
6025: AND lease.lease_id = p_lease_id;
6026:
6027: CURSOR get_extendable_terms(p_index_period_id NUMBER,l_term_date DATE) IS
6028: SELECT *
6029: FROM pn_payment_terms_all
6030: WHERE index_period_id = p_index_period_id
6031: AND end_date = l_term_date;
6032:
6033: l_comm_date DATE;
6032:
6033: l_comm_date DATE;
6034: l_term_date DATE;
6035: l_ext_end_dt DATE;
6036: l_term_rec pn_payment_terms_all%ROWTYPE;
6037: l_return_status VARCHAR2(100);
6038: l_schd_date DATE := NULL;
6039: l_schd_day NUMBER := NULL;
6040: l_terms_exist BOOLEAN := FALSE;
6237: AND ilt.approved_flag <> c_payment_term_status_approved ;
6238:
6239: DELETE FROM pn_distributions_all
6240: WHERE payment_term_id IN (SELECT payment_term_id
6241: FROM pn_payment_terms_all ppt
6242: WHERE ppt.index_period_id = c_rec.index_period_id
6243: AND ppt.status <> c_payment_term_status_approved);
6244:
6245: DELETE FROM pn_payment_terms_all ppt
6241: FROM pn_payment_terms_all ppt
6242: WHERE ppt.index_period_id = c_rec.index_period_id
6243: AND ppt.status <> c_payment_term_status_approved);
6244:
6245: DELETE FROM pn_payment_terms_all ppt
6246: WHERE ppt.index_period_id = c_rec.index_period_id
6247: AND ppt.status <> c_payment_term_status_approved;
6248:
6249: --
6475: x_return_status => l_return_status,
6476: x_return_message => op_msg);
6477:
6478: ELSE
6479: UPDATE pn_payment_terms_all
6480: SET end_date = l_ext_end_dt,
6481: last_update_date = SYSDATE,
6482: last_updated_by = fnd_global.user_id,
6483: last_update_login = fnd_global.login_id
7114: --
7115:
7116: select include_in_var_rent
7117: into l_include_in_var_rent
7118: from pn_payment_terms_all
7119: where payment_term_id = ip_index_pay_term_id;
7120:
7121: IF l_include_in_var_rent = 'INCLUDE_RI' THEN
7122: /* NBPs need to be recalculated */
7119: where payment_term_id = ip_index_pay_term_id;
7120:
7121: IF l_include_in_var_rent = 'INCLUDE_RI' THEN
7122: /* NBPs need to be recalculated */
7123: update pn_payment_terms_all
7124: set update_nbp_flag = 'Y'
7125: where payment_term_id = ip_index_pay_term_id;
7126: ELSE
7127: l_include_in_var_rent := NULL;
7127: l_include_in_var_rent := NULL;
7128: END IF;
7129:
7130:
7131: UPDATE pn_payment_terms_all
7132: SET status = c_payment_term_status_approved
7133: ,include_in_var_rent = l_include_in_var_rent
7134: ,last_update_date = SYSDATE
7135: ,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
7211: l_errmsg VARCHAR2(2000);
7212: l_errmsg1 VARCHAR2(2000);
7213: l_return_status VARCHAR2 (2) := NULL;
7214: l_nxt_schdate DATE;
7215: l_day pn_payment_terms.schedule_day%TYPE;
7216: l_info VARCHAR2(1000);
7217: l_message VARCHAR2(2000) := NULL;
7218: l_appr_count NUMBER := 0;
7219: l_batch_size NUMBER := 1000;
7261: ,ppt.schedule_day
7262: FROM pn_leases_all pl
7263: ,pn_index_leases pil
7264: ,pn_index_lease_periods_all pilp
7265: ,pn_payment_terms_all ppt
7266: WHERE pl.lease_id = pil.lease_id
7267: AND pil.index_lease_id = pilp.index_lease_id
7268: AND pilp.index_period_id = ppt.index_period_id
7269: AND (pilp.index_period_id = p_index_period_id
7300:
7301: CURSOR terms_cur (p1_lease_id IN NUMBER)
7302: IS
7303: SELECT UPDATE_NBP_FLAG
7304: FROM PN_PAYMENT_TERMS_ALL
7305: WHERE lease_id = p1_lease_id
7306: FOR UPDATE NOWAIT;
7307:
7308: CURSOR bkhd_exists_cur
7422:
7423:
7424: END LOOP;
7425:
7426: UPDATE pn_payment_terms_all
7427: SET UPDATE_NBP_FLAG = NULL
7428: WHERE lease_id = il_rec.lease_id;
7429:
7430: