[Home] [Help]
398: ,ppt.end_date
399: ,ppt.index_term_indicator
400: ,ppt.status
401: ,DECODE (ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE"
402: FROM pn_payment_terms_all ppt
403: WHERE ppt.index_period_id = ip_index_period_id
404: ORDER BY ppt.start_date;
405:
406: CURSOR index_periods_payments (
413: ,ppt.end_date
414: ,ppt.index_term_indicator
415: ,ppt.status
416: ,DECODE (ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE"
417: FROM pn_payment_terms_all ppt
418: WHERE ppt.index_period_id = ip_index_period_id
419: AND ( ppt.payment_term_id = ip_payment_term_id
420: OR ip_payment_term_id IS NULL
421: )
826: ,c_spread_frequency_one_time, 0
827: )
828: )
829: INTO p_prev_index_amount
830: FROM pn_payment_terms_all ppt, pn_index_lease_periods_all ppi
831: WHERE ppt.index_period_id = ppi.index_period_id
832: AND ppi.index_lease_id = p_index_lease_id
833: AND ppi.assessment_date < (SELECT assessment_date
834: FROM pn_index_lease_periods_all
893: -- get_profile_value
894: -- 14-AUG-06 pikhar o Added check to find out if contributing terms have
895: -- include_in_var_rent = 'INCLUDE_RI'
896: -- 23-SEP-06 prabhakar o Modified the curosrs to fetch terms from pn_index_exclude_term_all
897: -- with flag 'I' or from pn_payment_terms_all
898: -- 27-NOV-07 acprakas o Bug#6457105. Modified to consider new values for system option incl_terms_by_default_flag.
899: --------------------------------------------------------------------------------------------
900:
901: PROCEDURE sum_payment_items (
927: CURSOR csr_exc_get_item_period (p_payment_type VARCHAR2,p_org_id NUMBER) IS
928: SELECT ppt.payment_term_id
929: ,NVL(ppt.actual_amount, ppt.estimated_amount)
930: ,ppt.frequency_code
931: FROM pn_payment_terms_all ppt
932: ,pn_index_leases_all pil
933: WHERE pil.index_lease_id = p_index_lease_id
934: AND ppt.lease_id = pil.lease_id
935: AND ppt.payment_term_type_code
967: CURSOR csr_exc_get_item_enddate (p_payment_type VARCHAR2, p_org_id NUMBER) IS
968: SELECT ppt.payment_term_id
969: ,NVL(ppt.actual_amount, ppt.estimated_amount)
970: ,ppt.frequency_code
971: FROM pn_payment_terms_all ppt
972: ,pn_index_leases_all pil
973: WHERE pil.index_lease_id = p_index_lease_id
974: AND ppt.lease_id = pil.lease_id
975: AND ppt.payment_term_type_code
1009: CURSOR csr_inc_get_item_period (p_payment_type VARCHAR2,p_org_id NUMBER) IS
1010: SELECT ppt.payment_term_id
1011: ,NVL(ppt.actual_amount,ppt.estimated_amount)
1012: ,ppt.frequency_code
1013: FROM pn_payment_terms_all ppt
1014: ,pn_index_leases_all pil
1015: WHERE pil.index_lease_id = p_index_lease_id
1016: AND ppt.lease_id = pil.lease_id
1017: AND ppt.payment_term_type_code
1045: CURSOR csr_inc_get_item_enddate (p_payment_type VARCHAR2,p_org_id NUMBER) IS
1046: SELECT ppt.payment_term_id
1047: ,NVL(ppt.actual_amount,ppt.estimated_amount)
1048: ,ppt.frequency_code
1049: FROM pn_payment_terms_all ppt
1050: ,pn_index_leases_all pil
1051: WHERE pil.index_lease_id = p_index_lease_id
1052: AND ppt.lease_id = pil.lease_id
1053: AND ppt.payment_term_type_code
1239:
1240: IF l_increase_on is NOT NULL THEN
1241: SELECT include_in_var_rent
1242: INTO l_include_in_var_rent
1243: FROM pn_payment_terms_all ppt
1244: WHERE ppt.payment_term_id = l_payment_term_id
1245: AND ppt.payment_term_type_code = nvl(p_type_code,ppt.payment_term_type_code);
1246: ELSE
1247: SELECT include_in_var_rent
1245: AND ppt.payment_term_type_code = nvl(p_type_code,ppt.payment_term_type_code);
1246: ELSE
1247: SELECT include_in_var_rent
1248: INTO l_include_in_var_rent
1249: FROM pn_payment_terms_all ppt
1250: WHERE ppt.payment_term_id = l_payment_term_id;
1251: END IF;
1252:
1253: IF l_count = 1 THEN
2415:
2416: CURSOR c1 IS
2417: SELECT ppt.actual_amount
2418: ,ppt.frequency_code
2419: FROM pn_payment_terms_all ppt
2420: WHERE ppt.index_period_id = p_index_period_id
2421: AND ppt.index_term_indicator = c_index_pay_term_type_atlst;
2422:
2423: BEGIN
2468: -- cursor to select all recurring payment that are APPROVED.
2469: --
2470: CURSOR c1 IS
2471: SELECT ppt.payment_term_id, ppt.actual_amount, ppt.frequency_code
2472: FROM pn_payment_terms_all ppt
2473: WHERE ppt.index_period_id = p_index_period_id
2474: AND ppt.status =c_payment_term_status_approved
2475: AND ppt.index_term_indicator = p_index_term_indicator;
2476: BEGIN
2540: ,ip_index_term_indicator IN VARCHAR2
2541: ,ip_normalize IN VARCHAR2
2542: ,ip_index_period_id IN NUMBER)IS
2543: SELECT ppt.payment_term_id, ppt.actual_amount,ppt.frequency_code
2544: FROM pn_payment_terms_all ppt,
2545: pn_payment_items_all ppi
2546: WHERE ppt.index_period_id = ip_index_period_id
2547: AND ppt.status =c_payment_term_status_approved
2548: AND ppt.index_term_indicator = ip_index_term_indicator
2576: BEGIN
2577:
2578: SELECT '1'
2579: INTO v_exists
2580: FROM pn_payment_terms_all
2581: WHERE index_period_id = p_index_period_id
2582: AND status = c_payment_term_status_approved
2583: AND index_term_indicator = p_index_term_indicator;
2584:
3008: ,ppt.frequency_code
3009: ,ppt.include_in_var_rent
3010: ,build_distributions_string (ppt.payment_term_id) "DISTRIBUTION_STRING"
3011: ,NVL (ppt.normalize, 'N') "NORMALIZE"
3012: FROM pn_payment_terms_all ppt,
3013: pn_index_leases_all pil
3014: WHERE pil.index_lease_id = ip_index_lease_id
3015: AND ppt.lease_id = pil.lease_id
3016: AND nvl(ppt.status,'-1') = decode(ppt.index_period_id,null,nvl(ppt.status,'-1'),'APPROVED')
3068: ) IS
3069: SELECT ppt.payment_term_id,nvl(ppt.actual_amount,ppt.estimated_amount)
3070: ,ppt.frequency_code
3071: ,ppt.norm_start_date
3072: FROM pn_payment_terms_all ppt
3073: WHERE ppt.payment_purpose_code = ip_payment_purpose_code
3074: AND ppt.payment_term_type_code = ip_payment_term_type_code
3075: AND nvl(ppt.include_in_var_rent,'N') = nvl(ip_include_in_var_rent,'N')
3076: AND nvl(ppt.status,'-1') = decode(ppt.index_period_id,null,nvl(ppt.status,'-1'),'APPROVED')
3128: normalize,
3129: schedule_day,
3130: end_date,
3131: include_in_var_rent
3132: FROM pn_payment_terms_all
3133: WHERE lease_id = p_lease_id
3134: AND index_period_id = p_index_period_id
3135: AND status = c_payment_term_status_approved;
3136:
3150: ,ip_index_period_id IN NUMBER
3151: ,ip_include_in_var_rent IN VARCHAR2
3152: ) IS
3153: SELECT nvl(ppt.actual_amount,0) actual_amount, ppt.index_term_indicator, ppt.payment_term_id
3154: FROM pn_payment_terms_all ppt
3155: WHERE ppt.payment_purpose_code = ip_payment_purpose_code
3156: AND ppt.payment_term_type_code = ip_payment_term_type_code
3157: AND ppt.index_period_id = ip_index_period_id
3158: AND nvl(ppt.status,'-1') = 'APPROVED'
3202: ,p_normalize pn_payment_terms.normalize%TYPE
3203: ) IS
3204: SELECT sum(item.actual_amount) sum_overlap_amt
3205: FROM pn_payment_items_all item,
3206: pn_payment_terms_all term
3207: WHERE item.payment_term_id = term.payment_term_id
3208: AND item.due_date BETWEEN p_assessment_date AND p_end_date
3209: AND term.index_period_id = p_index_period_id
3210: AND term.index_term_indicator = p_index_term_indicator
3282:
3283: exist_term_rec term_rec;
3284:
3285: TYPE exist_payment IS TABLE OF exist_term_rec%TYPE INDEX BY BINARY_INTEGER;
3286: TYPE payment_term_tbl IS TABLE OF pn_payment_terms_all%ROWTYPE INDEX BY BINARY_INTEGER;
3287: TYPE id_tbl IS TABLE OF pn_payment_terms.payment_term_id%TYPE INDEX BY BINARY_INTEGER;
3288:
3289: exist_term_tab exist_payment;
3290: appr_ind_lease_tbl exist_payment;
4195: IS
4196: CURSOR get_overlap_sum IS
4197: SELECT sum(item.actual_amount) sum_overlap_amt
4198: FROM pn_payment_items_all item,
4199: pn_payment_terms_all term
4200: WHERE item.payment_term_id = term.payment_term_id
4201: AND term.term_template_id = p_term_template_id
4202: AND term.index_period_id = p_index_period_id
4203: AND term.index_term_indicator = p_index_term_indicator
4701:
4702: IF v_normalized_amount_annual = 0 THEN
4703: SELECT count(*)
4704: INTO l_found_atlst_bb
4705: FROM pn_payment_terms_all
4706: WHERE index_period_id = p_index_period_id
4707: AND index_term_indicator = c_index_pay_term_type_atlst_bb;
4708: END IF;
4709:
5139: v_location_id pn_payment_terms.location_id%type;
5140: v_payment_start_date DATE := null;
5141: v_term_end_date DATE := null;
5142: v_assessment_date DATE := null;
5143: v_org_id pn_payment_terms_all.org_id%type;
5144: v_area pn_payment_terms.area%TYPE;
5145: v_area_type_code pn_payment_terms.area_type_code%TYPE;
5146: l_org_id NUMBER;
5147: l_norm_st_date DATE := NULL;
5144: v_area pn_payment_terms.area%TYPE;
5145: v_area_type_code pn_payment_terms.area_type_code%TYPE;
5146: l_org_id NUMBER;
5147: l_norm_st_date DATE := NULL;
5148: v_include_in_var_rent pn_payment_terms_all.include_in_var_rent%TYPE ;
5149:
5150: CURSOR distributions (ip_term_template_id IN NUMBER)
5151: IS
5152: SELECT *
5292: ,v_attribute15
5293: ,v_frequency_code
5294: ,v_location_id
5295: ,v_org_id
5296: FROM pn_payment_terms_all
5297: WHERE payment_term_id = p_payment_term_id;
5298: ELSE
5299: SELECT name
5300: ,normalize
6023: AND lease.lease_id = p_lease_id;
6024:
6025: CURSOR get_extendable_terms(p_index_period_id NUMBER,l_term_date DATE) IS
6026: SELECT *
6027: FROM pn_payment_terms_all
6028: WHERE index_period_id = p_index_period_id
6029: AND end_date = l_term_date;
6030:
6031: l_comm_date DATE;
6030:
6031: l_comm_date DATE;
6032: l_term_date DATE;
6033: l_ext_end_dt DATE;
6034: l_term_rec pn_payment_terms_all%ROWTYPE;
6035: l_return_status VARCHAR2(100);
6036: l_schd_date DATE := NULL;
6037: l_schd_day NUMBER := NULL;
6038: l_terms_exist BOOLEAN := FALSE;
6235: AND ilt.approved_flag <> c_payment_term_status_approved ;
6236:
6237: DELETE FROM pn_distributions_all
6238: WHERE payment_term_id IN (SELECT payment_term_id
6239: FROM pn_payment_terms_all ppt
6240: WHERE ppt.index_period_id = c_rec.index_period_id
6241: AND ppt.status <> c_payment_term_status_approved);
6242:
6243: DELETE FROM pn_payment_terms_all ppt
6239: FROM pn_payment_terms_all ppt
6240: WHERE ppt.index_period_id = c_rec.index_period_id
6241: AND ppt.status <> c_payment_term_status_approved);
6242:
6243: DELETE FROM pn_payment_terms_all ppt
6244: WHERE ppt.index_period_id = c_rec.index_period_id
6245: AND ppt.status <> c_payment_term_status_approved;
6246:
6247: --
6473: x_return_status => l_return_status,
6474: x_return_message => op_msg);
6475:
6476: ELSE
6477: UPDATE pn_payment_terms_all
6478: SET end_date = l_ext_end_dt,
6479: last_update_date = SYSDATE,
6480: last_updated_by = fnd_global.user_id,
6481: last_update_login = fnd_global.login_id
7112: --
7113:
7114: select include_in_var_rent
7115: into l_include_in_var_rent
7116: from pn_payment_terms_all
7117: where payment_term_id = ip_index_pay_term_id;
7118:
7119: IF l_include_in_var_rent = 'INCLUDE_RI' THEN
7120: /* NBPs need to be recalculated */
7117: where payment_term_id = ip_index_pay_term_id;
7118:
7119: IF l_include_in_var_rent = 'INCLUDE_RI' THEN
7120: /* NBPs need to be recalculated */
7121: update pn_payment_terms_all
7122: set update_nbp_flag = 'Y'
7123: where payment_term_id = ip_index_pay_term_id;
7124: ELSE
7125: l_include_in_var_rent := NULL;
7125: l_include_in_var_rent := NULL;
7126: END IF;
7127:
7128:
7129: UPDATE pn_payment_terms_all
7130: SET status = c_payment_term_status_approved
7131: ,include_in_var_rent = l_include_in_var_rent
7132: ,last_update_date = SYSDATE
7133: ,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
7259: ,ppt.schedule_day
7260: FROM pn_leases_all pl
7261: ,pn_index_leases pil
7262: ,pn_index_lease_periods_all pilp
7263: ,pn_payment_terms_all ppt
7264: WHERE pl.lease_id = pil.lease_id
7265: AND pil.index_lease_id = pilp.index_lease_id
7266: AND pilp.index_period_id = ppt.index_period_id
7267: AND (pilp.index_period_id = p_index_period_id
7298:
7299: CURSOR terms_cur (p1_lease_id IN NUMBER)
7300: IS
7301: SELECT UPDATE_NBP_FLAG
7302: FROM PN_PAYMENT_TERMS_ALL
7303: WHERE lease_id = p1_lease_id
7304: FOR UPDATE NOWAIT;
7305:
7306: CURSOR bkhd_exists_cur
7420:
7421:
7422: END LOOP;
7423:
7424: UPDATE pn_payment_terms_all
7425: SET UPDATE_NBP_FLAG = NULL
7426: WHERE lease_id = il_rec.lease_id;
7427:
7428: