[Home] [Help]
70: WHERE PERIOD_ID = X_PERIOD_ID;
71:
72: CURSOR org_id_cur IS
73: SELECT org_id
74: FROM PN_VAR_RENTS_ALL
75: WHERE VAR_RENT_ID = X_VAR_RENT_ID;
76:
77: l_org_id NUMBER;
78:
447: WHERE REPORT_DATE_ID = X_REPORT_DATE_ID;
448:
449: CURSOR org_id_cur IS
450: SELECT org_id
451: FROM PN_VAR_RENTS_ALL
452: WHERE VAR_RENT_ID = X_VAR_RENT_ID;
453:
454: l_org_id NUMBER ;
455:
667: WHERE GRP_DATE_ID = X_GRP_DATE_ID;
668:
669: CURSOR org_id_cur IS
670: SELECT org_id
671: FROM PN_VAR_RENTS_ALL
672: WHERE VAR_RENT_ID = X_VAR_RENT_ID;
673:
674: l_org_id NUMBER ;
675:
1109: p_period_type,
1110: l_use_gl_calendar,
1111: l_year_start_date,
1112: l_org_id
1113: FROM PN_VAR_RENTS_ALL vr, PN_VAR_RENT_DATES_ALL cal
1114: WHERE vr.var_rent_id = p_var_rent_id
1115: AND cal.var_rent_id = vr.var_rent_id;
1116:
1117: ----------------------------------
2252: l_invg_freq_code,
2253: l_invg_day_of_month,
2254: l_invg_days_after,
2255: l_org_id
2256: FROM PN_VAR_RENTS_ALL VR, PN_VAR_RENT_DATES_ALL CAL
2257: WHERE VR.var_rent_id = p_var_rent_id
2258: AND CAL.var_rent_id = VR.var_rent_id;
2259:
2260: -- generate periods
2880: -- Get proration days info from the main VR record
2881:
2882: SELECT decode(proration_days,999,365,proration_days)
2883: INTO tot_per_proration_days
2884: FROM pn_var_rents_ALL
2885: WHERE var_rent_id = p_var_rent_id;
2886:
2887: -- Get group start date for the last group which has to be
2888: -- updated with the new proration factor after lease contraction
4428: INTO l_base_rent
4429: FROM pn_payment_items_ALL item,
4430: pn_payment_terms_ALL term,
4431: pn_var_periods_ALL per,
4432: pn_var_rents_ALL var,
4433: pn_payment_schedules_all sched
4434: WHERE item.PAYMENT_TERM_ID = term.PAYMENT_TERM_ID
4435: AND sched.PAYMENT_SCHEDULE_ID = item.PAYMENT_SCHEDULE_ID
4436: AND term.lease_id = var.lease_id
4452: INTO l_base_rent
4453: FROM pn_payment_items_ALL item,
4454: pn_payment_terms_ALL term,
4455: pn_var_periods_ALL per,
4456: pn_var_rents_ALL var,
4457: pn_payment_schedules_all sched
4458: WHERE item.PAYMENT_TERM_ID = term.PAYMENT_TERM_ID
4459: AND sched.PAYMENT_SCHEDULE_ID = item.PAYMENT_SCHEDULE_ID
4460: AND term.lease_id = var.lease_id
5488: CURSOR var_rent_c(p_vr_id IN NUMBER) IS
5489: SELECT var_rent_id
5490: ,commencement_date
5491: ,termination_date
5492: FROM pn_var_rents_all
5493: WHERE var_rent_id = p_vr_id;
5494:
5495: CURSOR line_defs_c(p_vr_id IN NUMBER) IS
5496: SELECT line_default_id
6083: CURSOR var_rent_c(p_vr_id IN NUMBER) IS
6084: SELECT var_rent_id
6085: ,commencement_date
6086: ,termination_date
6087: FROM pn_var_rents_all
6088: WHERE var_rent_id = p_vr_id;
6089:
6090: /* cursor for defaults - SETUP */
6091: CURSOR constr_def_c IS
6610:
6611: BEGIN
6612: SELECT commencement_date, termination_date
6613: INTO l_vr_comm_dt, l_vr_term_dt
6614: FROM pn_var_rents_all
6615: WHERE var_rent_id = p_pn_var_rent_dates_rec.var_rent_id;
6616:
6617: SELECT year_start_date
6618: INTO l_year_st_date
7002: SELECT var_rent_id,
7003: commencement_date start_date,
7004: termination_date end_date,
7005: cumulative_vol
7006: FROM pn_var_rents_all
7007: WHERE var_rent_id = p_var_rent_id;
7008:
7009: errbuf VARCHAR2(5000);
7010: retcode VARCHAR2(5000);
7013: BEGIN
7014: put_log ('PN_VAR_RENT_PKG.create_new_bkpts (+)');
7015: FOR main_vr_rec in main_vr_cur
7016: LOOP
7017: UPDATE pn_var_rents_all
7018: SET termination_date = p_extension_end_date
7019: WHERE var_rent_id = main_vr_rec.var_rent_id;
7020:
7021: put_log ('Starting Extension of Periods and group Dates');
7067: RETURN VARCHAR2
7068: IS
7069: CURSOR var_rent_cur IS
7070: SELECT a.proration_rule
7071: FROM pn_var_rents_all a,
7072: pn_var_periods_all b
7073: WHERE a.var_rent_id = NVL(p_var_rent_id,a.var_rent_id)
7074: AND a.var_rent_id = b.var_rent_id
7075: AND b.period_id = NVL(p_period_id,b.period_id);
7080: IF p_var_rent_id IS NOT NULL THEN
7081: BEGIN
7082: SELECT proration_rule
7083: INTO l_proration_rule
7084: FROM pn_var_rents_all
7085: WHERE var_rent_id = p_var_rent_id;
7086: EXCEPTION
7087: WHEN OTHERS THEN
7088: l_proration_rule := NULL;
7091: ELSE
7092: BEGIN
7093: SELECT proration_rule
7094: INTO l_proration_rule
7095: FROM pn_var_rents_all a,
7096: pn_var_periods_all b
7097: WHERE a.var_rent_id = b.var_rent_id
7098: AND b.period_id = NVL(p_period_id,b.period_id);
7099: EXCEPTION
7653: CURSOR var_rent_cur IS
7654: SELECT 'x'
7655: FROM dual
7656: WHERE EXISTS ( SELECT var_rent_id
7657: FROM pn_var_rents_all
7658: WHERE agreement_template_id = p_template_id);
7659:
7660: l_return BOOLEAN := FALSE;
7661: BEGIN
7766: |
7767: | 30-JAN-2004 Daniel Thota o Created
7768: +===========================================================================*/
7769: FUNCTION DETERMINE_FREQUENCY (
7770: X_VAR_RENT_START_DATE IN PN_VAR_RENTS_ALL.COMMENCEMENT_DATE%TYPE
7771: ,X_VAR_RENT_END_DATE IN PN_VAR_RENTS_ALL.TERMINATION_DATE%TYPE
7772: ) RETURN PN_VAR_RENT_DATES_ALL.REPTG_FREQ_CODE%TYPE
7773: is
7774:
7767: | 30-JAN-2004 Daniel Thota o Created
7768: +===========================================================================*/
7769: FUNCTION DETERMINE_FREQUENCY (
7770: X_VAR_RENT_START_DATE IN PN_VAR_RENTS_ALL.COMMENCEMENT_DATE%TYPE
7771: ,X_VAR_RENT_END_DATE IN PN_VAR_RENTS_ALL.TERMINATION_DATE%TYPE
7772: ) RETURN PN_VAR_RENT_DATES_ALL.REPTG_FREQ_CODE%TYPE
7773: is
7774:
7775: l_days NUMBER;
8296: 'QTR', 3,
8297: 'SA', 6,
8298: 'YR', 12,
8299: NULL) reptg_freq_code
8300: FROM pn_var_rents_all vr, pn_var_rent_dates_all cal
8301: WHERE vr.var_rent_id = p_var_rent_id
8302: AND cal.var_rent_id = vr.var_rent_id;
8303:
8304: /* Get the details of grp dates for group in which the new termination date falls */
8829:
8830: DELETE pn_var_periods_all
8831: WHERE var_rent_id = p_var_rent_id;
8832:
8833: DELETE pn_var_rents_all
8834: WHERE var_rent_id = p_var_rent_id;
8835:
8836: pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_var_agreement (-)');
8837:
8870: termination date for a given lease or fetches information for a given agreement */
8871:
8872: CURSOR var_rent_cur IS
8873: SELECT var_rent_id, commencement_date
8874: FROM pn_var_rents_all
8875: WHERE lease_id = NVL(p_lease_id, lease_id)
8876: AND var_rent_id = NVL (p_var_rent_id, var_rent_id)
8877: AND (( termination_date = p_old_termn_date) OR
8878: (termination_date < p_old_termn_date AND termination_date > p_new_termn_date))
8887:
8888: /* Fetch the agreements which start after the new termination date */
8889: CURSOR variable_rent_cur IS
8890: SELECT var_rent_id
8891: FROM pn_var_rents_all
8892: WHERE lease_id = p_lease_id
8893: AND commencement_date > p_new_termn_date
8894: AND commencement_date < p_old_termn_date ;
8895:
8899: FROM pn_payment_terms_all
8900: WHERE status = 'APPROVED'
8901: AND var_rent_inv_id IN (SELECT var_rent_inv_id
8902: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp,
8903: pn_var_rents_all pvr
8904: WHERE pvi.period_id = pvp.period_id
8905: AND pvr.var_rent_id = pvp.var_rent_id
8906: AND pvr.var_rent_id = p_var_rent_id);
8907:
9034:
9035: pnp_debug_pkg.debug ('after for loop');
9036:
9037: /* update the end date of agreement to new termination date */
9038: UPDATE pn_var_rents_all
9039: SET termination_date = p_new_termn_date
9040: WHERE var_rent_id = var_rent_rec.var_rent_id;
9041:
9042: /* update the bkpt_update_flag to 'Y for VR agreement */
9875: , pvr.proration_rule
9876: , pvd.use_gl_calendar
9877: , pvd.year_start_date
9878: , pvr.commencement_date
9879: FROM pn_var_rents_all pvr, pn_var_rent_dates_all pvd
9880: WHERE pvr.lease_id = NVL (p_lease_id, pvr.lease_id)
9881: AND pvr.var_rent_id = NVL (p_var_rent_id, pvr.var_rent_id)
9882: AND pvr.termination_date = p_old_termn_date
9883: AND pvd.var_rent_id = pvr.var_rent_id;
9925:
9926: /* Update the vr agreement with the new termination date */
9927: pnp_debug_pkg.debug ('Update the vr agreement with the new termination date ...');
9928:
9929: UPDATE pn_var_rents_all
9930: SET termination_date = p_new_termn_date
9931: WHERE var_rent_id = main_vr_rec.var_rent_id;
9932:
9933: /* call appropriate procedures to create new periods or activate the inactive one
10311: /* Cursor to get all var_rents for the given property */
10312: CURSOR varent_prop_cur (p_prop_id IN NUMBER)
10313: IS
10314: SELECT DISTINCT var.var_rent_id
10315: from pn_var_rents_all var
10316: where var.lease_id IN( SELECT distinct lease_id
10317: FROM ( SELECT lease_id
10318: FROM pn_tenancies_all
10319: WHERE location_id in (SELECT location_id
10330: /* Cursor to get all var_rents for the given location */
10331: CURSOR varent_loc_cur(p_loc_id IN NUMBER)
10332: IS
10333: SELECT DISTINCT var.var_rent_id
10334: FROM pn_var_rents_all var
10335: WHERE var.lease_id IN (SELECT lease_id
10336: FROM (SELECT lease_id
10337: FROM pn_tenancies_all
10338: WHERE location_id = p_loc_id
10345: /* Cursor to get all var_rents for the given lease */
10346: CURSOR varent_lease_cur(p_lease_id IN NUMBER)
10347: IS
10348: SELECT DISTINCT var.var_rent_id
10349: FROM pn_var_rents_all var
10350: WHERE var.lease_id = p_lease_id;
10351:
10352:
10353: /* Cursor used to fetch distinct periods */
10354: CURSOR periods_cur(p_var_rent_id NUMBER) IS
10355: SELECT DISTINCT per.period_id period_id,
10356: var.rent_num rent_num
10357: FROM pn_var_periods_all per,
10358: pn_var_rents_all var
10359: WHERE per.var_rent_id = p_var_rent_id
10360: AND var.var_rent_id = p_var_rent_id;
10361:
10362:
10559: IF l_var_rent_id IS NOT NULL THEN
10560:
10561: SELECT rent_num
10562: INTO l_rent_num
10563: FROM pn_var_rents_all
10564: WHERE var_rent_id = l_var_rent_id;
10565:
10566: /* Checking if the var rent agreement has a period with two or more similar lines */
10567:
11067: CURSOR var_cur(p_vr_id IN NUMBER) IS
11068: SELECT commencement_date
11069: ,termination_date
11070: ,proration_rule
11071: FROM pn_var_rents_all
11072: WHERE var_rent_id = p_vr_id;
11073:
11074: /* Cursor to get var periods */
11075: CURSOR periods_vr_c(p_vr_id IN NUMBER) IS