[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_PERIODS_ROW
(X_ROWID IN OUT NOCOPY VARCHAR2,
X_PERIOD_ID IN OUT NOCOPY NUMBER,
X_PERIOD_NUM IN OUT NOCOPY NUMBER,
X_VAR_RENT_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_PRORATION_FACTOR IN NUMBER,
X_PARTIAL_PERIOD IN VARCHAR2,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_ORG_ID NUMBER
) IS
CURSOR C IS
SELECT ROWID
FROM PN_VAR_PERIODS_ALL
WHERE PERIOD_ID = X_PERIOD_ID;
SELECT org_id
FROM PN_VAR_RENTS_ALL
WHERE VAR_RENT_ID = X_VAR_RENT_ID;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_PERIODS_ROW (+)');
SELECT NVL(MAX(pnp.PERIOD_NUM),0)
INTO X_PERIOD_NUM
FROM PN_VAR_PERIODS_ALL pnp
WHERE pnp.VAR_RENT_ID = X_VAR_RENT_ID;
SELECT pn_var_periods_s.nextval
INTO X_PERIOD_ID
FROM dual;
INSERT INTO PN_VAR_PERIODS_ALL
(
PERIOD_ID,
PERIOD_NUM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
VAR_RENT_ID,
START_DATE,
END_DATE,
PRORATION_FACTOR,
PARTIAL_PERIOD,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID
)
VALUES
(
X_PERIOD_ID,
X_PERIOD_NUM,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_VAR_RENT_ID,
X_START_DATE,
X_END_DATE,
X_PRORATION_FACTOR,
X_PARTIAL_PERIOD,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
l_org_id
);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_PERIODS_ROW (-)');
END INSERT_PERIODS_ROW;
| DELETE_PERIODS_ROW
|
| DESCRIPTION
| DELETE recORds FROM the PN_VAR_PERIODS
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| X_VAR_RENT_ID
|
| OUT:
|
| RETURNS : None
|
| NOTES : DELETE recORds FROM the PN_VAR_PERIODS table
|
| MODIFICATION HISTORY
|
| 03-SEP-2001 Daniel Thota o Created
| 27-DEC-2001 Daniel Thota o INcluded parameter x_term_date
| 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_PERIODS with _ALL table.
+===========================================================================*/
PROCEDURE DELETE_PERIODS_ROW (
X_VAR_RENT_ID IN NUMBER,
X_TERM_DATE IN DATE
) IS
l_date DATE;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_PERIODS_ROW (+)');
DELETE FROM PN_VAR_PERIODS_ALL
WHERE VAR_RENT_ID = X_VAR_RENT_ID
AND START_DATE > l_date
AND END_DATE > l_date;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_PERIODS_ROW (-)');
END DELETE_PERIODS_ROW;
| Inserts the records into PN_VAR_REPORT_DATES_ALL
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| X_VAR_RENT_ID
|
| OUT:
|
| RETURNS : None
|
| NOTES : Inserts the records into PN_VAR_REPORT_DATES_ALL
|
| MODIFICATION HISTORY
|
| 10-JAN-2006 Prabhakar o Created.
+===========================================================================*/
PROCEDURE CREATE_REPORT_DATES (p_var_rent_id IN NUMBER) IS
CURSOR get_group_details IS
SELECT GRP_DATE_ID,
GRP_START_DATE,
GRP_END_DATE,
ORG_ID
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id;
SELECT decode(vrg_reptg_freq_code, 'MON', 1,
'QTR', 3,
'SA', 6,
'YR', 12,
null) report_frequency,
decode(reptg_freq_code, 'MON', 1,
'QTR', 3,
'SA', 6,
'YR', 12,
null) group_frequency
FROM pn_var_rent_dates_all
WHERE var_rent_id = p_var_rent_id;
PN_VAR_RENT_PKG.INSERT_REPORT_DATE_ROW
(
X_ROWID => l_row_id
,X_REPORT_DATE_ID => l_report_date_id
,X_GRP_DATE_ID => l_group_date_id
,X_VAR_RENT_ID => p_var_rent_id
,X_REPORT_START_DATE => l_report_start_date
,X_REPORT_END_DATE => l_report_end_date
,X_CREATION_DATE => p_creation_date
,X_CREATED_BY => p_created_by
,X_LAST_UPDATE_DATE => p_creation_date
,X_LAST_UPDATED_BY => p_created_by
,X_LAST_UPDATE_LOGIN => p_created_by
,X_ATTRIBUTE_CATEGORY => NULL
,X_ATTRIBUTE1 => NULL
,X_ATTRIBUTE2 => NULL
,X_ATTRIBUTE3 => NULL
,X_ATTRIBUTE4 => NULL
,X_ATTRIBUTE5 => NULL
,X_ATTRIBUTE6 => NULL
,X_ATTRIBUTE7 => NULL
,X_ATTRIBUTE8 => NULL
,X_ATTRIBUTE9 => NULL
,X_ATTRIBUTE10 => NULL
,X_ATTRIBUTE11 => NULL
,X_ATTRIBUTE12 => NULL
,X_ATTRIBUTE13 => NULL
,X_ATTRIBUTE14 => NULL
,X_ATTRIBUTE15 => NULL
,X_ORG_ID => l_org_id
);
PROCEDURE INSERT_REPORT_DATE_ROW
(
X_ROWID IN OUT NOCOPY VARCHAR2,
X_REPORT_DATE_ID IN OUT NOCOPY NUMBER,
X_GRP_DATE_ID IN NUMBER,
X_VAR_RENT_ID IN NUMBER,
X_REPORT_START_DATE IN DATE,
X_REPORT_END_DATE IN DATE,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_ORG_ID IN NUMBER
) IS
CURSOR C IS
SELECT ROWID
FROM PN_VAR_REPORT_DATES_ALL
WHERE REPORT_DATE_ID = X_REPORT_DATE_ID;
SELECT org_id
FROM PN_VAR_RENTS_ALL
WHERE VAR_RENT_ID = X_VAR_RENT_ID;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_REPORT_DATE_ROW (+)');
SELECT pn_var_report_dates_s.nextval
INTO X_REPORT_DATE_ID
FROM dual;
INSERT INTO PN_VAR_REPORT_DATES_ALL
(
REPORT_DATE_ID,
GRP_DATE_ID,
VAR_RENT_ID,
REPORT_START_DATE,
REPORT_END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID
)
VALUES
(
X_REPORT_DATE_ID,
X_GRP_DATE_ID,
X_VAR_RENT_ID,
X_REPORT_START_DATE,
X_REPORT_END_DATE,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ORG_ID
);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_REPORT_DATE_ROW (-)');
END INSERT_REPORT_DATE_ROW;
| DELETE_REPORT_DATE_ROW
|
| DESCRIPTION
| DELETE records FROM the PN_VAR_REPORT_DATES
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| X_GRP_DATE_ID
|
| OUT:
|
| RETURNS : None
|
| NOTES : DELETE records FROM the PN_VAR_REPORT_DATES table
|
| MODIFICATION HISTORY
|
| 10-JAN_2006 Prabhakar o Created.
+===========================================================================*/
PROCEDURE DELETE_REPORT_DATE_ROW (
X_VAR_RENT_ID IN NUMBER,
X_END_DATE IN DATE
) IS
l_date DATE;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_REPORT_DATE_ROW (+)');
DELETE FROM PN_VAR_REPORT_DATES_ALL
WHERE VAR_RENT_ID = X_VAR_RENT_ID
AND REPORT_START_DATE > l_date
AND REPORT_END_DATE > l_date;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_REPORT_DATE_ROW (-)');
END DELETE_REPORT_DATE_ROW;
PROCEDURE INSERT_GRP_DATE_ROW
(
X_ROWID IN OUT NOCOPY VARCHAR2,
X_GRP_DATE_ID IN OUT NOCOPY NUMBER,
X_VAR_RENT_ID IN NUMBER,
X_PERIOD_ID IN NUMBER,
X_GRP_START_DATE IN DATE,
X_GRP_END_DATE IN DATE,
X_GROUP_DATE IN DATE,
X_REPTG_DUE_DATE IN DATE,
X_INV_START_DATE IN DATE,
X_INV_END_DATE IN DATE,
X_INVOICE_DATE IN DATE,
X_INV_SCHEDULE_DATE IN DATE,
X_PRORATION_FACTOR IN NUMBER,
X_ACTUAL_EXP_CODE IN VARCHAR2,
X_FORECASTED_EXP_CODE IN VARCHAR2,
X_VARIANCE_EXP_CODE IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_ORG_ID NUMBER
) IS
CURSOR C IS
SELECT ROWID
FROM PN_VAR_GRP_DATES_ALL
WHERE GRP_DATE_ID = X_GRP_DATE_ID;
SELECT org_id
FROM PN_VAR_RENTS_ALL
WHERE VAR_RENT_ID = X_VAR_RENT_ID;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW (+)');
SELECT pn_var_grp_dates_s.nextval
INTO X_GRP_DATE_ID
FROM dual;
INSERT INTO PN_VAR_GRP_DATES_ALL
(
GRP_DATE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
VAR_RENT_ID,
PERIOD_ID,
GRP_START_DATE,
GRP_END_DATE,
GROUP_DATE,
REPTG_DUE_DATE,
INV_START_DATE,
INV_END_DATE,
INVOICE_DATE,
INV_SCHEDULE_DATE,
PRORATION_FACTOR,
ACTUAL_EXP_CODE,
FORECASTED_EXP_CODE,
VARIANCE_EXP_CODE,
ORG_ID
)
VALUES
(
X_GRP_DATE_ID,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_VAR_RENT_ID,
X_PERIOD_ID,
X_GRP_START_DATE,
X_GRP_END_DATE,
X_GROUP_DATE,
X_REPTG_DUE_DATE,
X_INV_START_DATE,
X_INV_END_DATE,
X_INVOICE_DATE,
X_INV_SCHEDULE_DATE,
round(X_PRORATION_FACTOR,10),
X_ACTUAL_EXP_CODE,
X_FORECASTED_EXP_CODE,
X_VARIANCE_EXP_CODE,
l_org_id
);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW (-)');
END INSERT_GRP_DATE_ROW;
| DELETE_GRP_DATE_ROW
|
| DESCRIPTION
| DELETE recORds FROM the PN_VAR_GRP_DATES
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| X_VAR_RENT_ID
|
| OUT:
|
| RETURNS : None
|
| NOTES : DELETE recORds FROM the PN_VAR_GRP_DATES table
|
| MODIFICATION HISTORY
|
| 03-SEP-2001 Daniel Thota o Created
| 27-DEC-2001 Daniel Thota o INcluded parameter x_term_date
| 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_GRP_DATES with _ALL table.
+===========================================================================*/
PROCEDURE DELETE_GRP_DATE_ROW (
X_VAR_RENT_ID IN NUMBER,
X_TERM_DATE IN DATE
) IS
l_date DATE;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_GRP_DATE_ROW (+)');
DELETE FROM PN_VAR_GRP_DATES_ALL
WHERE VAR_RENT_ID = X_VAR_RENT_ID
AND GRP_START_DATE > l_date
AND GRP_END_DATE > l_date;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_GRP_DATE_ROW (-)');
END DELETE_GRP_DATE_ROW;
SELECT MIN(start_date) start_date, MAX(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND start_date <= p_vr_term_date
AND period_type = p_period_type
AND period_year >= TO_NUMBER(TO_CHAR(TO_DATE(p_vr_comm_date1,'DD/MM/RRRR'),'RRRR'))
GROUP BY period_year
ORDER BY start_date;
SELECT MIN(start_date) start_date, MAX(END_date) END_date
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND period_type = p_period_type
AND adjustment_period_flag = 'N'
GROUP BY period_year, quarter_num, period_num
ORDER BY start_date,end_date;
SELECT MIN(start_date) start_date, MAX(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND quarter_num IN(1,2,3,4)
AND period_type = p_period_type
GROUP BY period_year, quarter_num
ORDER BY start_date;
SELECT MIN(g1.start_date) start_date
,MAX(g2.end_date) end_date
FROM gl_periods g1, gl_periods g2
WHERE g1.period_set_name(+) = p_period_set_name
AND g2.period_set_name = p_period_set_name
AND g1.start_date(+) <= p_vr_term_date
AND g2.end_date >= p_vr_comm_date
AND g1.quarter_num(+) = 1
AND g2.quarter_num = 2
AND g1.period_year(+) = g2.period_year
AND g1.start_date IS NOT NULL
AND g2.end_date IS NOT NULL
AND g1.period_type = p_period_type
AND g2.period_type = p_period_type
GROUP BY g2.period_year
UNION
SELECT MIN(g1.start_date) start_date
,MAX(g2.end_date) end_date
FROM gl_periods g1, gl_periods g2
WHERE g1.period_set_name(+) = p_period_set_name
AND g2.period_set_name = p_period_set_name
AND g1.start_date(+) <= p_vr_term_date
AND g2.end_date >= p_vr_comm_date
AND g1.quarter_num(+) = 3
AND g2.quarter_num = 4
AND g1.period_year (+)= g2.period_year
AND g1.start_date IS NOT NULL
AND g2.end_date IS NOT NULL
AND g1.period_type = p_period_type
AND g2.period_type = p_period_type
GROUP BY g2.period_year
ORDER BY 1;
SELECT MIN(start_date) start_date,MAX(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND period_type = p_period_type
GROUP BY period_year
ORDER BY start_date;
SELECT period_id,start_date, end_date, proration_factor
FROM pn_var_periods
WHERE var_rent_id = p_var_rent_id
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
ORDER BY start_date;
SELECT grp_end_date, grp_date_id
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND grp_start_date = p_start_date;
SELECT period_id, end_date, status
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND start_date = p_start_date;
SELECT inv_start_date, inv_end_date
FROM pn_var_grp_dates_all
WHERE inv_start_date = p_inv_start_date
AND inv_end_date = p_inv_end_date
AND period_id = p_period_id;
SELECT vr.commencement_date,
vr.termination_date,
decode(vr.proration_days,'999',365,
vr.proration_days),
decode(cal.period_freq_code,'MON', 1,
'QTR', 3,
'SA', 6,
'YR', 12,
null),
decode(cal.reptg_freq_code, 'MON', 1,
'QTR', 3,
'SA', 6,
'YR', 12,
null),
cal.reptg_day_of_month,
cal.reptg_days_after,
decode(cal.invg_freq_code, 'MON', 1,
'QTR', 3,
'SA', 6,
'YR', 12,
null),
cal.invg_day_of_month,
cal.invg_days_after,
cal.gl_period_set_name,
cal.period_type,
cal.use_gl_calendar,
cal.year_start_date,
vr.org_id
INTO p_vr_comm_date,
p_vr_term_date,
tot_per_proration_days,
p_per_freq_code,
p_reptg_freq_code,
p_reptg_day_of_month,
p_reptg_days_after,
p_invg_freq_code,
p_invg_day_of_month,
p_invg_days_after,
p_period_set_name,
p_period_type,
l_use_gl_calendar,
l_year_start_date,
l_org_id
FROM PN_VAR_RENTS_ALL vr, PN_VAR_RENT_DATES_ALL cal
WHERE vr.var_rent_id = p_var_rent_id
AND cal.var_rent_id = vr.var_rent_id;
SELECT MIN(start_date), MAX(end_date)
INTO p_cal_start_date, p_cal_end_date
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_type = p_period_type;
SELECT MIN(start_date), MAX(end_date)
INTO p_cal_start_date, p_cal_end_date
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_type = p_period_type
AND start_date >= p_comm_date
AND end_date >= p_comm_date
AND start_date <= p_term_date;
IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
--call to insert into PN_VAR_PERIODS;
PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
(X_ROWID => l_rowId,
X_PERIOD_ID => l_periodId,
X_PERIOD_NUM => l_periodNum,
X_VAR_RENT_ID => p_var_rent_id,
X_START_DATE => p_per_start_date,
X_END_DATE => p_per_end_date,
X_PRORATION_FACTOR => p_proration_factor,
X_PARTIAL_PERIOD => l_partial_period,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_CREATION_DATE => p_creation_date,
X_CREATED_BY => p_created_by,
X_LAST_UPDATE_DATE => p_creation_date,
X_LAST_UPDATED_BY => p_created_by,
X_LAST_UPDATE_LOGIN => p_created_by
);
pnp_debug_pkg.debug('period inserted is ...'||l_periodId);
UPDATE pn_var_periods_all
SET status = NULL
WHERE period_id = l_period_id;
UPDATE pn_var_periods_all
SET end_date = p_per_end_date,
partial_period = l_partial_period,
status = NULL
WHERE period_id = l_period_id;
PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
(X_ROWID => l_rowId,
X_GRP_DATE_ID => l_grpDateId,
X_VAR_RENT_ID => p_var_rent_id,
X_PERIOD_ID => p_period_id,
X_GRP_START_DATE => p_grp_start_date,
X_GRP_END_DATE => p_grp_end_date,
X_GROUP_DATE => p_group_date,
X_REPTG_DUE_DATE => p_due_date,
X_INV_START_DATE => p_inv_start_date,
X_INV_END_DATE => p_inv_end_date,
X_INVOICE_DATE => p_invoice_date,
X_INV_SCHEDULE_DATE => p_schedule_date,
X_PRORATION_FACTOR => p_proration_factor,
X_ACTUAL_EXP_CODE => 'N',
X_FORECASTED_EXP_CODE => 'N',
X_VARIANCE_EXP_CODE => 'N',
X_CREATION_DATE => p_creation_date,
X_CREATED_BY => p_created_by,
X_LAST_UPDATE_DATE => p_creation_date,
X_LAST_UPDATED_BY => p_created_by,
X_LAST_UPDATE_LOGIN => p_created_by
);
UPDATE pn_var_grp_dates_all
SET grp_end_date = p_grp_end_date,
proration_Factor = round(p_proration_factor,10),
reptg_due_date = p_due_date --Bug # 5937888
WHERE grp_date_id = l_grp_date_id;
PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
(X_ROWID => l_rowId,
X_GRP_DATE_ID => l_grpDateId,
X_VAR_RENT_ID => p_var_rent_id,
X_PERIOD_ID => p_period_id,
X_GRP_START_DATE => p_grp_start_date,
X_GRP_END_DATE => p_grp_end_date,
X_GROUP_DATE => p_group_date,
X_REPTG_DUE_DATE => p_due_date,
X_INV_START_DATE => p_inv_start_date,
X_INV_END_DATE => p_inv_end_date,
X_INVOICE_DATE => p_invoice_date,
X_INV_SCHEDULE_DATE => p_schedule_date,
X_PRORATION_FACTOR => p_proration_factor,
X_ACTUAL_EXP_CODE => 'N',
X_FORECASTED_EXP_CODE => 'N',
X_VARIANCE_EXP_CODE => 'N',
X_CREATION_DATE => p_creation_date,
X_CREATED_BY => p_created_by,
X_LAST_UPDATE_DATE => p_creation_date,
X_LAST_UPDATED_BY => p_created_by,
X_LAST_UPDATE_LOGIN => p_created_by
);
UPDATE pn_var_grp_dates_all
SET grp_end_date = p_grp_end_date,
proration_Factor = round(p_proration_factor,10),
reptg_due_date = p_due_date --Bug # 5937888
WHERE grp_date_id = l_grp_date_id;
PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
(X_ROWID => l_rowId,
X_GRP_DATE_ID => l_grpDateId,
X_VAR_RENT_ID => p_var_rent_id,
X_PERIOD_ID => p_period_id,
X_GRP_START_DATE => p_grp_start_date,
X_GRP_END_DATE => p_grp_end_date,
X_GROUP_DATE => p_group_date,
X_REPTG_DUE_DATE => p_due_date,
X_INV_START_DATE => p_inv_start_date,
X_INV_END_DATE => p_inv_end_date,
X_INVOICE_DATE => p_invoice_date,
X_INV_SCHEDULE_DATE => p_schedule_date,
X_PRORATION_FACTOR => p_proration_factor,
X_ACTUAL_EXP_CODE => 'N',
X_FORECASTED_EXP_CODE => 'N',
X_VARIANCE_EXP_CODE => 'N',
X_CREATION_DATE => p_creation_date,
X_CREATED_BY => p_created_by,
X_LAST_UPDATE_DATE => p_creation_date,
X_LAST_UPDATED_BY => p_created_by,
X_LAST_UPDATE_LOGIN => p_created_by
);
UPDATE pn_var_grp_dates_all
SET grp_end_date = p_grp_end_date,
proration_Factor = round(p_proration_factor,10),
reptg_due_date = p_due_date --Bug # 5937888
WHERE grp_date_id = l_grp_date_id;
PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
(X_ROWID => l_rowId,
X_GRP_DATE_ID => l_grpDateId,
X_VAR_RENT_ID => p_var_rent_id,
X_PERIOD_ID => p_period_id,
X_GRP_START_DATE => p_grp_start_date,
X_GRP_END_DATE => p_grp_end_date,
X_GROUP_DATE => p_group_date,
X_REPTG_DUE_DATE => p_due_date,
X_INV_START_DATE => p_inv_start_date,
X_INV_END_DATE => p_inv_end_date,
X_INVOICE_DATE => p_invoice_date,
X_INV_SCHEDULE_DATE => p_schedule_date,
X_PRORATION_FACTOR => p_proration_factor,
X_ACTUAL_EXP_CODE => 'N',
X_FORECASTED_EXP_CODE => 'N',
X_VARIANCE_EXP_CODE => 'N',
X_CREATION_DATE => p_creation_date,
X_CREATED_BY => p_created_by,
X_LAST_UPDATE_DATE => p_creation_date,
X_LAST_UPDATED_BY => p_created_by,
X_LAST_UPDATE_LOGIN => p_created_by
);
UPDATE pn_var_grp_dates_all
SET grp_end_date = p_grp_end_date,
proration_Factor = round(p_proration_factor,10),
reptg_due_date = p_due_date --Bug # 5937888
WHERE grp_date_id = l_grp_date_id;
PN_VAR_RENT_PKG.delete_report_date_row(p_var_rent_id, NULL);
UPDATE PN_VAR_GRP_DATES
SET inv_start_date = p_inv_start_date,
inv_end_date = p_inv_end_date,
invoice_date = p_invoice_date,
inv_schedule_date = p_schedule_date
WHERE grp_date_id in (SELECT grp_date_id
FROM pn_var_grp_dates
WHERE grp_start_date <= p_inv_end_date
AND grp_end_date >= p_inv_start_date
AND period_id = p_period_id
AND var_rent_id = p_var_rent_id)
AND period_id = p_period_id
AND var_rent_id = p_var_rent_id;
UPDATE PN_VAR_GRP_DATES
SET inv_start_date = p_inv_start_date,
inv_end_date = p_inv_end_date,
invoice_date = p_invoice_date,
inv_schedule_date = p_schedule_date
WHERE grp_date_id in (SELECT grp_date_id
FROM pn_var_grp_dates
WHERE grp_start_date <= p_inv_end_date
AND grp_end_date >= p_inv_start_date
AND period_id = p_period_id
AND var_rent_id = p_var_rent_id)
AND period_id = p_period_id
AND var_rent_id = p_var_rent_id;
UPDATE PN_VAR_GRP_DATES
SET inv_start_date = p_inv_start_date,
inv_end_date = p_inv_end_date,
invoice_date = p_invoice_date,
inv_schedule_date = p_schedule_date
WHERE grp_date_id in (SELECT grp_date_id
FROM pn_var_grp_dates
WHERE grp_start_date <= p_inv_end_date
AND grp_end_date >= p_inv_start_date
AND period_id = p_period_id
AND var_rent_id = p_var_rent_id)
AND period_id = p_period_id
AND var_rent_id = p_var_rent_id;
UPDATE PN_VAR_GRP_DATES
SET inv_start_date = p_inv_start_date,
inv_end_date = p_inv_end_date,
invoice_date = p_invoice_date,
inv_schedule_date = p_schedule_date
WHERE grp_date_id in (SELECT grp_date_id
FROM pn_var_grp_dates
WHERE grp_start_date <= p_inv_end_date
AND grp_end_date >= p_inv_start_date
AND period_id = p_period_id
AND var_rent_id = p_var_rent_id)
AND period_id = p_period_id
AND var_rent_id = p_var_rent_id;
SELECT grp_end_date, grp_date_id
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND grp_start_date = p_start_date;
SELECT period_id, end_date, status
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND start_date = p_start_date;
SELECT VR.commencement_date,
VR.termination_date,
DECODE(VR.proration_days,'999',365,VR.proration_days),
DECODE(CAL.period_freq_code,'MON',1
,'QTR',3
,'SA' ,6
,'YR' ,12
,NULL),
DECODE(CAL.reptg_freq_code,'MON',1
,'QTR',3
,'SA' ,6
,'YR' ,12
,NULL),
CAL.reptg_day_of_month,
CAL.reptg_days_after,
DECODE(CAL.invg_freq_code,'MON',1
,'QTR',3
,'SA' ,6
,'YR' ,12
,NULL),
CAL.invg_day_of_month,
CAL.invg_days_after,
VR.org_id
INTO l_vr_comm_date,
l_vr_term_date,
l_total_per_proration_days,
l_period_freq_code,
l_reptg_freq_code,
l_reptg_day_of_month,
l_reptg_days_after,
l_invg_freq_code,
l_invg_day_of_month,
l_invg_days_after,
l_org_id
FROM PN_VAR_RENTS_ALL VR, PN_VAR_RENT_DATES_ALL CAL
WHERE VR.var_rent_id = p_var_rent_id
AND CAL.var_rent_id = VR.var_rent_id;
IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
IF (vr_periods(i).l_period_end_date - vr_periods(i).l_period_start_date+1) >=365 THEN
l_partial_period := 'N';
PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
( X_ROWID => l_rowId,
X_PERIOD_ID => VR_periods(i).l_period_id,
X_PERIOD_NUM => l_periodNum,
X_VAR_RENT_ID => p_var_rent_id,
X_START_DATE => VR_periods(i).l_period_start_date,
X_END_DATE => VR_periods(i).l_period_end_date,
X_PRORATION_FACTOR => VR_periods(i).l_proration_factor,
X_PARTIAL_PERIOD => l_partial_period,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_CREATION_DATE => l_creation_date,
X_CREATED_BY => l_created_by,
X_LAST_UPDATE_DATE => l_creation_date,
X_LAST_UPDATED_BY => l_created_by,
X_LAST_UPDATE_LOGIN => l_created_by
);
pnp_debug_pkg.debug('period inserted is ...'||VR_periods(i).l_period_id);
UPDATE pn_var_periods_all
SET status = NULL
WHERE period_id = l_periodId;
pnp_debug_pkg.debug('Need to update the period end date '||l_periodId);
UPDATE pn_var_periods_all
SET
end_date = vr_periods(i).l_period_end_date,
partial_period = l_partial_period,
status = NULL
WHERE period_id = l_periodId;
pnp_debug_pkg.debug (' Group does not exist - to insert group ');
PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
( X_ROWID => l_rowId,
X_GRP_DATE_ID => l_grpDateId,
X_VAR_RENT_ID => p_var_rent_id,
X_PERIOD_ID => VR_periods(i).l_period_id,
X_GRP_START_DATE => VR_grp_dates(j).l_grp_start_date,
X_GRP_END_DATE => VR_grp_dates(j).l_grp_end_date,
X_GROUP_DATE => VR_grp_dates(j).l_group_date,
X_REPTG_DUE_DATE => VR_grp_dates(j).l_reptg_due_date,
X_INV_START_DATE => VR_grp_dates(j).l_inv_start_date,
X_INV_END_DATE => VR_grp_dates(j).l_inv_end_date,
X_INVOICE_DATE => VR_grp_dates(j).l_invoice_date,
X_INV_SCHEDULE_DATE => VR_grp_dates(j).l_inv_schedule_date,
X_PRORATION_FACTOR => l_proration_factor,
X_ACTUAL_EXP_CODE => 'N',
X_FORECASTED_EXP_CODE => 'N',
X_VARIANCE_EXP_CODE => 'N',
X_CREATION_DATE => l_creation_date,
X_CREATED_BY => l_created_by,
X_LAST_UPDATE_DATE => l_creation_date,
X_LAST_UPDATED_BY => l_created_by,
X_LAST_UPDATE_LOGIN => l_created_by
);
pnp_debug_pkg.debug (' group exists - update it ');
UPDATE pn_var_grp_dates_all
SET grp_end_date = VR_grp_dates(j).l_grp_end_date,
proration_Factor = round(l_proration_Factor,10),
reptg_due_date = VR_grp_dates(j).l_reptg_due_date --Bug # 5937888
WHERE grp_date_id = l_grp_date_id;
PN_VAR_RENT_PKG.delete_report_date_row(p_var_rent_id, NULL);
| DELETE_VAR_RENT_PERIODS
|
| DESCRIPTION
| Delete variable rent periods record in PN_VAR_PERIODS, corresponding
| group date/invoice date records in the PN_VAR_GRP_DATES table,
| corresponding line items in the PN_VAR_LINES table
| for a variable rent record
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_var_rent_id
| p_term_date
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete variable rent periods record in PN_VAR_PERIODS,
| corresponding group date/invoice date records in the
| PN_VAR_GRP_DATES table, corresponding line items in the
| PN_VAR_LINES table for a variable rent record
| Calls DELETE_PERIODS_ROW, DELETE_GRP_DATE_ROW,
| DELETE_VAR_RENT_LINES procedures
|
| MODIFICATION HISTORY
|
| 03-SEP-01 Daniel o Created
| 27-DEC-01 Daniel o included parameter p_term_date for contraction.
| 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_RENT_SUMM with _ALL tbl.
| 11-JAN-07 Pseeram o Added the call delete_report_date_row to delete
| report dates records after undo periods.
| 09-FEB-07 Lokesh o Bug # 5874461, Added NVL for export_to_ar_flag
+=============================================================================*/
PROCEDURE DELETE_VAR_RENT_PERIODS(p_var_rent_id IN NUMBER,
p_term_date IN DATE ) IS
l_lines_exist NUMBER := NULL;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_PERIODS (+)');
DELETE FROM pn_payment_schedules_all
WHERE payment_schedule_id IN
(SELECT payment_schedule_id
FROM pn_payment_items_all
WHERE NVL(export_to_ar_flag,'N') <>'Y'
AND payment_term_id IN
(SELECT payment_term_id
FROM pn_payment_terms
WHERE VAR_RENT_INV_ID IN
(SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id
)
)
)
AND payment_status_lookup_code <>'APPROVED';
DELETE FROM pn_payment_items_all
WHERE payment_term_id IN
(SELECT payment_term_id
FROM pn_payment_terms_all
WHERE var_rent_inv_id IN
(SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id
)
)
AND NVL(export_to_ar_flag,'N') <>'Y' ;
DELETE FROM pn_var_rent_summ
WHERE var_rent_id = p_var_rent_id;
DELETE FROM pn_payment_terms
WHERE var_rent_inv_id IN
(SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id)
AND NOT EXISTS
(SELECT 1
FROM pn_payment_items
WHERE export_to_ar_flag ='Y'
AND payment_term_id IN
(SELECT payment_term_id
FROM pn_payment_terms
WHERE VAR_RENT_INV_ID IN
(SELECT VAR_RENT_INV_ID
FROM PN_VAR_RENT_INV_ALL
WHERE VAR_RENT_ID = p_var_rent_id
)
)
);
DELETE FROM pn_var_rent_inv
WHERE var_rent_id = p_var_rent_id;
pn_var_rent_pkg.delete_report_date_row(p_var_rent_id,l_date);
pn_var_rent_pkg.delete_grp_date_row(p_var_rent_id,l_date);
pn_var_rent_pkg.delete_var_rent_lines(p_var_rent_id,l_date);
pn_var_rent_pkg.delete_var_rent_constr(p_var_rent_id,l_date);
pn_var_rent_pkg.delete_periods_row(p_var_rent_id,l_date);
/*DELETE FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id;*/
DELETE FROM pn_var_abat_defaults_all
where var_rent_id = p_var_rent_id;
DELETE FROM pn_var_abatements_all abat
where abat.var_rent_id=p_var_rent_id;
DELETE FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_line_defaults_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_constr_defaults_all
WHERE var_rent_id = p_var_rent_id;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_PERIODS (-)');
END DELETE_VAR_RENT_PERIODS;
| UPDATE_VAR_RENT_PERIODS
|
| DESCRIPTION
| Update variable rent periods record in PN_VAR_PERIODS, corresponding
| group date/invoice date records in the PN_VAR_GRP_DATES table
| for a variable rent record contraction
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_var_rent_id
| p_term_date
|
| OUT:
|
| RETURNS : None
|
| NOTES : Update variable rent periods record in PN_VAR_PERIODS, corresponding
| group date/invoice date records in the PN_VAR_GRP_DATES table
| for a variable rent record contraction
|
| MODIFICATION HISTORY
|
| 29-DEC-01 Daniel o Created
| 04-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_PERIODS with _ALL table
+=============================================================================*/
PROCEDURE UPDATE_VAR_RENT_PERIODS(p_var_rent_id IN NUMBER,
p_term_date IN DATE ) IS
p_last_update_date DATE := SYSDATE;
p_last_updated_by NUMBER := NVL (fnd_profile.VALUE ('USER_ID'), 0);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_VAR_RENT_PERIODS (+)');
SELECT DECODE(cal.reptg_freq_code,'MON', 1,
'QTR', 3,
'SA', 6,
'YR', 12,
null)
INTO p_reptg_freq_code
FROM PN_VAR_RENT_DATES_ALL cal
WHERE cal.var_rent_id = p_var_rent_id;
SELECT per.start_date
INTO p_per_start_date
FROM PN_VAR_PERIODS_ALL per
WHERE per.var_rent_id = p_var_rent_id
AND per.start_date <= p_term_date
AND per.end_date >= p_term_date
AND rownum < 2;
SELECT decode(proration_days,999,365,proration_days)
INTO tot_per_proration_days
FROM pn_var_rents_ALL
WHERE var_rent_id = p_var_rent_id;
SELECT grp.grp_start_date
INTO p_grp_start_date
FROM pn_var_grp_dates_all grp
WHERE grp.var_rent_id = p_var_rent_id
AND grp.grp_start_date <= p_term_date
AND grp.grp_end_date >= p_term_date
AND rownum < 2;
UPDATE pn_var_periods_all
SET end_date = p_term_date,
proration_factor = p_proration_factor,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_updated_by
WHERE var_rent_id = p_var_rent_id
AND start_date <= p_term_date
AND end_date >= p_term_date
AND rownum < 2;
UPDATE pn_var_grp_dates_all
SET grp_end_date = p_term_date,
proration_factor = round(p_proration_factor,10),
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_updated_by
WHERE var_rent_id = p_var_rent_id
AND grp_start_date <= p_term_date
AND grp_end_date >= p_term_date
AND rownum < 2;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_VAR_RENT_PERIODS (-)');
END UPDATE_VAR_RENT_PERIODS;
| DELETE_VAR_RENT_CONSTR
|
| DESCRIPTION
| Delete variable rent constraints from the PN_VAR_CONSTRAINTS table for each period
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
| PN_VAR_CONSTRAINTS_PKG.DELETE_ROW
|
| ARGUMENTS : IN:
| p_var_rent_id
| p_term_date
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete variable rent constraints from the PN_VAR_CONSTRAINTS table for each period
|
| MODIFICATION HISTORY
|
| 03-DEC-2001 Daniel Thota o Created
| 27-DEC-2001 Daniel Thota o Included parameter p_term_date for contraction.
+===========================================================================*/
PROCEDURE DELETE_VAR_RENT_CONSTR(p_var_rent_id IN NUMBER,
p_term_date IN DATE ) IS
l_counter NUMBER := 0;
SELECT constraint_id
FROM PN_VAR_CONSTRAINTS_ALL
WHERE period_id IN (SELECT period_id
FROM PN_VAR_PERIODS_ALL
WHERE var_rent_id = p_var_rent_id
AND start_date > l_date
AND end_date > l_date);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_CONSTR (+)');
PN_VAR_CONSTRAINTS_PKG.DELETE_ROW(constr_rec.constraint_id);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_CONSTR (-)');
END DELETE_VAR_RENT_CONSTR;
| DELETE_VAR_RENT_LINES
|
| DESCRIPTION
| Delete variable rent lines record in PN_VAR_LINES and associated
| volume history records in the PN_VAR_VOL_HIST table for a
| variable rent record
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
| PN_VAR_LINES_PKG.DELETE_ROW
|
| ARGUMENTS : IN:
| p_var_rent_id
| p_term_date
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete variable rent lines record in PN_VAR_LINES and associated
| volume history records in the PN_VAR_VOL_HIST table for a
| variable rent record
|
| MODIFICATION HISTORY
|
| 15-OCT-2001 Daniel Thota o Created
| 27-DEC-2001 Daniel Thota o Included parameter p_term_date for contraction.
| 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_lines with _ALL table.
+===========================================================================*/
PROCEDURE DELETE_VAR_RENT_LINES(p_var_rent_id IN NUMBER,
p_term_date IN DATE ) IS
l_counter NUMBER := 0;
SELECT line_item_id
FROM pn_var_lines
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_ALL
WHERE var_rent_id = p_var_rent_id
AND start_date > l_date
AND end_date > l_date);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_LINES (+)');
PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_HEAD(lines_rec.line_item_id);
PN_VAR_RENT_PKG.DELETE_VAR_VOL_HIST(lines_rec.line_item_id);
PN_VAR_RENT_PKG.DELETE_VAR_RENT_DEDUCT(lines_rec.line_item_id);
PN_VAR_LINES_PKG.DELETE_ROW(lines_rec.line_item_id);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_LINES (-)');
END DELETE_VAR_RENT_LINES;
| DELETE_VAR_RENT_DEDUCT
|
| DESCRIPTION
| Delete variable rent deductions from the PN_VAR_DEDUCTIONS table for each line
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
| PN_VAR_DEDUCTIONS_PKG.DELETE_ROW
|
| ARGUMENTS : IN:
| p_line_item_id
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete variable rent deductions from the PN_VAR_DEDUCTIONS table for each line
|
| MODIFICATION HISTORY
|
| 03-DEC-2001 Daniel Thota o Created
+===========================================================================*/
PROCEDURE DELETE_VAR_RENT_DEDUCT(p_line_item_id IN NUMBER) IS
l_counter NUMBER := 0;
SELECT deduction_id
FROM PN_VAR_DEDUCTIONS_ALL
WHERE line_item_id = p_line_item_id;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_DEDUCT (+)');
PN_VAR_DEDUCTIONS_PKG.DELETE_ROW(deduct_rec.deduction_id);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_DEDUCT (-)');
END DELETE_VAR_RENT_DEDUCT;
| DELETE_VAR_VOL_HIST
|
| DESCRIPTION
| Delete variable rent lines
| volume history records in the PN_VAR_VOL_HIST table
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
| PN_VAR_LINES_PKG.DELETE_ROW
|
| ARGUMENTS : IN:
| p_line_item_id
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete variable rent lines
| volume history records in the PN_VAR_VOL_HIST table
|
| MODIFICATION HISTORY
|
| 30-NOV-2001 Daniel Thota o Created
+===========================================================================*/
PROCEDURE DELETE_VAR_VOL_HIST(p_line_item_id IN NUMBER) IS
l_counter NUMBER := 0;
SELECT vol_hist_id
FROM PN_VAR_VOL_HIST_ALL
WHERE line_item_id = p_line_item_id;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_VOL_HIST (+)');
PN_VAR_VOL_HIST_PKG.DELETE_ROW(vol_hist_rec.vol_hist_id);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_VOL_HIST (-)');
END DELETE_VAR_VOL_HIST;
| DELETE_VAR_BKPTS_HEAD
|
| DESCRIPTION
| Delete breakpoint details records in PN_VAR_BKPTS_DET for a
| line item record.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
| PN_VAR_LINES_PKG.DELETE_ROW
|
| ARGUMENTS : IN:
| p_line_item_id
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete breakpoint details records in PN_VAR_BKPTS_DET for a
| line item record.
|
| MODIFICATION HISTORY
|
| 17-NOV-2001 Daniel Thota o Created
| 20-JAN-06 Pikhar o Used cursor to delete Breakpoint Headers
+===========================================================================*/
PROCEDURE DELETE_VAR_BKPTS_HEAD(p_line_item_id IN NUMBER) IS
l_bkpt_header_id NUMBER := 0;
SELECT bkpt_header_id
FROM pn_var_bkpts_head_all
WHERE line_item_id = p_line_item_id;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_HEAD (+)');
PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_DET(head_rec.bkpt_header_id);
PN_VAR_BKPTS_HEAD_PKG.DELETE_ROW(head_rec.bkpt_header_id);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_HEAD (-)');
END DELETE_VAR_BKPTS_HEAD;
| DELETE_VAR_BKPTS_DET
|
| DESCRIPTION
| Delete breakpoint details records in PN_VAR_BKPTS_DET for a
| breakpoint header record.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
| PN_VAR_LINES_PKG.DELETE_ROW
|
| ARGUMENTS : IN:
| p_bkpt_header_id
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete breakpoint details records in PN_VAR_BKPTS_DET for a
| breakpoint header record.
|
| MODIFICATION HISTORY
|
| 17-NOV-2001 Daniel Thota o Created
+===========================================================================*/
PROCEDURE DELETE_VAR_BKPTS_DET(p_bkpt_header_id IN NUMBER) IS
l_counter NUMBER := 0;
SELECT bkpt_detail_id
FROM pn_var_bkpts_det_ALL
WHERE bkpt_header_id = p_bkpt_header_id;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_DET (+)');
PN_VAR_BKPTS_DET_PKG.DELETE_ROW(det_rec.bkpt_detail_id);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_BKPTS_DET (-)');
END DELETE_VAR_BKPTS_DET;
SELECT 1
INTO l_period_exists
FROM dual
WHERE EXISTS ( SELECT periods.period_id
FROM pn_var_periods_ALL periods
WHERE periods.var_rent_id = p_var_rent_id);
SELECT 1
INTO l_calculation_exists
FROM dual
WHERE EXISTS ( SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv
WHERE inv.var_rent_id = p_var_rent_id);
SELECT 1
INTO l_invoice_exists
FROM dual
WHERE EXISTS ( SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv
WHERE inv.var_rent_id = p_var_rent_id);
| DELETE_VAR_RENT_INVOICES
|
| DESCRIPTION
| Deletes variable rent Volumes, Deductions, Invoices and Terms created
| for a variable rent record
|
| SCOPE - PUBLIC
|
| ARGUMENTS : IN:
| p_var_rent_id
| p_term_date
|
| OUT:
|
| RETURNS : None
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 12-Jan-2007 Ram kumar o Created
+===========================================================================*/
PROCEDURE DELETE_VAR_RENT_INVOICES(p_var_rent_id IN NUMBER,
p_term_date IN DATE ) IS
l_lines_exist NUMBER := NULL;
SELECT line_item_id
FROM pn_var_lines
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_ALL
WHERE var_rent_id = p_var_rent_id
AND start_date > l_date
AND end_date > l_date);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_INVOICES (+)');
DELETE FROM pn_payment_items_all
WHERE payment_term_id IN
(SELECT payment_term_id
FROM pn_payment_terms_all
WHERE var_rent_inv_id IN
(SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id
)
)
AND export_to_ar_flag <>'Y' ;
DELETE FROM pn_payment_schedules_all
WHERE payment_schedule_id IN
(SELECT payment_schedule_id
FROM pn_payment_items_all
WHERE export_to_ar_flag <>'Y'
AND payment_term_id IN
(SELECT payment_term_id
FROM pn_payment_terms
WHERE VAR_RENT_INV_ID IN
(SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id
)
)
)
AND payment_status_lookup_code <>'APPROVED';
DELETE FROM pn_var_rent_summ
WHERE var_rent_id = p_var_rent_id;
DELETE FROM pn_payment_terms
WHERE var_rent_inv_id IN
(SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id)
AND NOT EXISTS
(SELECT 1
FROM pn_payment_items
WHERE export_to_ar_flag ='Y'
AND payment_term_id IN
(SELECT payment_term_id
FROM pn_payment_terms
WHERE VAR_RENT_INV_ID IN
(SELECT VAR_RENT_INV_ID
FROM PN_VAR_RENT_INV_ALL
WHERE VAR_RENT_ID = p_var_rent_id
)
)
);
DELETE FROM pn_var_rent_inv
WHERE var_rent_id = p_var_rent_id;
PN_VAR_RENT_PKG.DELETE_VAR_VOL_HIST(lines_rec.line_item_id);
PN_VAR_RENT_PKG.DELETE_VAR_RENT_DEDUCT(lines_rec.line_item_id);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_VAR_RENT_INVOICES (-)');
END DELETE_VAR_RENT_INVOICES;
SELECT 1
INTO l_vrdates_exists
FROM dual
WHERE EXISTS ( SELECT dates.var_rent_date_id
FROM pn_var_rent_dates_ALL dates
WHERE dates.var_rent_id = p_var_rent_id);
SELECT 1
INTO l_lines_exist
FROM dual
WHERE EXISTS ( SELECT line_item_id
FROM pn_var_lines_ALL
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_ALL
WHERE var_rent_id = p_var_rent_id
AND period_id = NVL(p_period_id,period_id)
AND start_date > l_date
AND end_date > l_date)
);
SELECT 1
INTO l_volhist_exist
FROM dual
WHERE EXISTS ( SELECT vol_hist_id
FROM pn_var_vol_hist_ALL
WHERE line_item_id = p_line_item_id
);
SELECT 'Y'
INTO l_volhist_approved_exist
FROM dual
WHERE EXISTS ( SELECT vol_hist_id
FROM pn_var_vol_hist_all
WHERE line_item_id = p_line_item_id
AND grp_date_id = p_grp_date_id
AND vol_hist_status_code = 'APPROVED'
);
SELECT bkpt_header_id, line_item_id
FROM pn_var_bkpts_head_ALL
WHERE line_item_id = p_id;
SELECT bkpt_header_id, line_item_id
FROM pn_var_bkpts_head_ALL
WHERE period_id = p_id;
SELECT bkpt_header_id, line_item_id
FROM pn_var_bkpts_head_ALL
WHERE period_id IN ( SELECT period_id
FROM pn_var_periods_ALL
WHERE var_rent_id = p_id );
SELECT 1
INTO l_constr_exist
FROM dual
WHERE EXISTS ( SELECT constraint_id
FROM pn_var_constraints_ALL
WHERE period_id IN (SELECT period_id
FROM PN_VAR_PERIODS_ALL
WHERE var_rent_id = p_var_rent_id
AND start_date > l_date
AND end_date > l_date)
);
SELECT 1
INTO l_abat_exist
FROM dual
WHERE EXISTS ( SELECT NULL
FROM pn_var_abat_defaults_all
WHERE var_rent_id = p_var_rent_id
);
SELECT 1
INTO l_constr_exist
FROM dual
WHERE EXISTS ( SELECT NULL
FROM pn_var_constr_defaults_all
WHERE var_rent_id = p_var_rent_id
);
SELECT 1
INTO l_deduct_exist
FROM dual
WHERE EXISTS ( SELECT deduction_id
FROM pn_var_deductions_ALL
WHERE line_item_id = p_line_item_id
);
SELECT count(GRP_START_DATE)
INTO l_reporting_periods
FROM pn_var_grp_dates_all
WHERE period_id = p_period_id;
SELECT SUM(item.ACTUAL_AMOUNT)
INTO l_base_rent
FROM pn_payment_items_ALL item,
pn_payment_terms_ALL term,
pn_var_periods_ALL per,
pn_var_rents_ALL var,
pn_payment_schedules_all sched
WHERE item.PAYMENT_TERM_ID = term.PAYMENT_TERM_ID
AND sched.PAYMENT_SCHEDULE_ID = item.PAYMENT_SCHEDULE_ID
AND term.lease_id = var.lease_id
AND var.var_rent_id = p_var_rent_id
AND per.period_id = p_period_id
AND per.var_rent_id = p_var_rent_id
AND sched.SCHEDULE_DATE >= FIRST_DAY(per.start_date)
AND sched.SCHEDULE_DATE <= per.end_date
AND term.PAYMENT_PURPOSE_CODE = 'RENT'
AND term.PAYMENT_TERM_TYPE_CODE = 'BASER'
AND term.start_date <= per.end_date
AND term.end_date >= FIRST_DAY(per.start_date)
AND item.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
AND term.currency_code = var.currency_code;
SELECT SUM(item.ACTUAL_AMOUNT)
INTO l_base_rent
FROM pn_payment_items_ALL item,
pn_payment_terms_ALL term,
pn_var_periods_ALL per,
pn_var_rents_ALL var,
pn_payment_schedules_all sched
WHERE item.PAYMENT_TERM_ID = term.PAYMENT_TERM_ID
AND sched.PAYMENT_SCHEDULE_ID = item.PAYMENT_SCHEDULE_ID
AND term.lease_id = var.lease_id
AND var.var_rent_id = p_var_rent_id
AND per.period_num = 1
AND per.var_rent_id = p_var_rent_id
AND sched.SCHEDULE_DATE >= FIRST_DAY(per.start_date)
AND sched.SCHEDULE_DATE <= per.end_date
AND term.PAYMENT_PURPOSE_CODE = 'RENT'
AND term.PAYMENT_TERM_TYPE_CODE = 'BASER'
AND term.start_date <= per.end_date
AND term.end_date >= FIRST_DAY(per.start_date)
AND item.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
AND term.currency_code = var.currency_code;
| 21-NOV-03 Daniel Thota o Added forecasted_exp_code to SELECT -- Fix for bug # 2435455
| 14-JUN-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
+===========================================================================*/
FUNCTION get_grp_date_info (p_var_rent_id IN NUMBER,
p_period_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE) RETURN GRP_DATE_INFO_REC
IS
l_grp_date_info GRP_DATE_INFO_REC ;
SELECT grp_date_id,
grp_start_date,
grp_end_date,
group_date,
reptg_due_date,
inv_start_date,
inv_end_date,
invoice_date,
inv_schedule_date,
forecasted_exp_code
INTO l_grp_date_info
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND period_id = p_period_id
AND grp_start_date <= p_start_date
AND grp_start_date <= p_end_date
AND grp_end_date >= p_start_date
AND grp_end_date >= p_end_date
AND rownum = 1;
SELECT
(p1.end_date-p1.start_date)+1 first_period_pro_days,
p1.proration_factor first_period_gl_days,
(p2.end_date-p2.start_date)+1 last_period_pro_days,
p2.proration_factor last_period_gl_days,
(g1.grp_end_date-g1.grp_start_date)+1 first_group_pro_days,
g1.proration_factor first_group_gl_days,
(g2.grp_end_date-g2.grp_start_date)+1 last_group_pro_days,
g2.proration_factor last_group_gl_days
INTO l_proration_factor
FROM pn_var_periods_ALL p1, pn_var_periods_ALL p2,
pn_var_grp_dates_ALL g1, pn_var_grp_dates_ALL g2
WHERE p1.var_rent_id = p2.var_rent_id
AND p1.var_rent_id = p_var_rent_id
AND g1.var_rent_id = g2.var_rent_id
AND g1.var_rent_id = p_var_rent_id
AND p1.period_id = (SELECT min(period_id)
FROM pn_var_periods_ALL
WHERE var_rent_id = p_var_rent_id)
AND p2.period_id = (SELECT max(period_id)
FROM pn_var_periods_ALL
WHERE var_rent_id = p_var_rent_id)
AND g1.grp_date_id = (SELECT min(grp_date_id)
FROM pn_var_grp_dates
WHERE var_rent_id = p_var_rent_id)
AND g2.grp_date_id = (SELECT max(grp_date_id)
FROM pn_var_grp_dates
WHERE var_rent_id = p_var_rent_id);
SELECT 1
INTO l_bkptshd_exists
FROM dual
WHERE EXISTS ( SELECT head.bkpt_header_id
FROM pn_var_bkpts_head_ALL head
WHERE head.line_item_id = p_line_item_id);
SELECT 1
INTO l_bkptsdet_exist
FROM dual
WHERE EXISTS ( SELECT det.bkpt_detail_id
FROM pn_var_bkpts_det_ALL det
WHERE det.bkpt_header_id = p_bkpt_header_id);
| 30-NOV-04 abanerje o corrected the SELECT statement when
| called FROM with p_block AS PERIODS_INV_BLK
| Added brackets so that the join condition
| IS evaluated correctly.
| Bug 4026980
| 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
| 12-Mar-07 Shabda o Bug 5911819 - Volume records are considered exported
| when actual or variance exp_code = y (Not forecasted exp_code)
+===========================================================================*/
FUNCTION find_if_exported (p_id IN NUMBER,
p_block IN VARCHAR2,
p_start_dt IN DATE DEFAULT NULL,
p_end_dt IN DATE DEFAULT NULL) RETURN NUMBER IS
l_exported NUMBER;
SELECT 1
INTO l_exported
FROM dual
WHERE EXISTS ( SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_id
AND (actual_exp_code = 'Y' OR
forecasted_exp_code = 'Y')
);
SELECT 1
INTO l_exported
FROM dual
WHERE EXISTS ( SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE period_id = p_id
AND (actual_exp_code = 'Y' OR
forecasted_exp_code = 'Y')
);
SELECT 1
INTO l_exported
FROM dual
WHERE EXISTS ( SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE period_id IN (SELECT period_id
FROM pn_var_lines_ALL
WHERE line_item_id = p_id)
AND (actual_exp_code = 'Y' OR
forecasted_exp_code = 'Y')
);
SELECT 1
INTO l_exported
FROM dual
WHERE EXISTS ( SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE period_id IN (SELECT period_id
FROM pn_var_lines_ALL
WHERE line_default_id = p_id)
AND (actual_exp_code = 'Y' OR
forecasted_exp_code = 'Y')
);
SELECT 1
INTO l_exported
FROM dual
WHERE EXISTS ( SELECT grp_date_id
FROM pn_var_grp_dates_all a,
pn_var_periods_all b,
pn_var_lines_all c
WHERE a.period_id = b.period_id
AND b.period_id = c.period_id
AND c.line_default_id = p_id
AND (a.actual_exp_code = 'Y' OR
a.forecasted_exp_code = 'Y')
AND ((b.start_date BETWEEN p_start_dt AND p_end_dt)
OR (b.end_date BETWEEN p_start_dt AND p_end_dt)
OR (p_start_dt BETWEEN b.start_date AND b.end_date)
OR (p_end_dt BETWEEN b.start_date AND b.end_date))
);
SELECT 1
INTO l_exported
FROM dual
WHERE EXISTS ( SELECT vol_hist_id
FROM pn_var_vol_hist_ALL
WHERE vol_hist_id = p_id
AND (actual_exp_code = 'Y' OR
variance_exp_code = 'Y')
);
SELECT 1
INTO l_exported
FROM dual
WHERE EXISTS ( SELECT VAR_RENT_INV_ID
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id = p_id
AND (actual_exp_code = 'Y' OR
variance_exp_code = 'Y')
);
SELECT 1
INTO l_exported
FROM dual
WHERE EXISTS ( SELECT vol_hist_id
FROM pn_var_vol_hist_ALL
WHERE vol_hist_id = p_id
AND forecasted_exp_code = 'Y'
);
| Completed and Reconciled Select stmts
| 11-Jul-02 Ashish Kumar Fix for BUG#2452276 In the Reconcile query change the
| Forcasted_exp_code to Variance_exp_code
| 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL table.
+===========================================================================*/
FUNCTION find_status (p_period_id NUMBER) RETURN VARCHAR2 IS
l_status VARCHAR2(30);
SELECT 'COMPLETE'
INTO l_status
FROM dual
WHERE NOT EXISTS
(SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE period_id = p_period_id
AND ((actual_exp_code = 'N' AND FORECASTED_exp_code ='N' AND variance_exp_code = 'N') OR
(actual_exp_code = 'N' AND FORECASTED_exp_code ='Y' AND variance_exp_code = 'Y')
)
)
AND NOT EXISTS
(SELECT vol_hist_id
FROM pn_var_vol_hist_ALL
WHERE period_id = p_period_id
AND ((actual_exp_code = 'N' AND FORECASTED_exp_code ='N' AND variance_exp_code = 'N') OR
(actual_exp_code = 'N' AND FORECASTED_exp_code ='Y' AND variance_exp_code = 'Y')
)
);
SELECT 'RECONCILED'
INTO l_status
FROM dual
WHERE NOT EXISTS(SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE period_id = p_period_id
AND variance_exp_code = 'N')
AND NOT EXISTS(SELECT vol_hist_id
FROM pn_var_vol_hist_all
WHERE period_id = p_period_id
AND VARIANCE_EXP_CODE ='N' );
SELECT 1
INTO l_adjust_hist_exists
FROM dual
WHERE EXISTS(SELECT 1
FROM pn_var_rent_inv_ALL inv1
WHERE inv1.period_id = p_period_id
AND (NVL(inv1.adjust_num,0) <> 0 OR
NVL(inv1.true_up_amt,0) <> 0 ));
SELECT 'Y' term_exists
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_var_rent_inv_all INv,
pn_payment_terms_all pt
WHERE inv.var_rent_id = p_var_rent_id
AND inv.period_id = NVL(p_period_id,inv.period_id)
AND inv.var_rent_inv_id = pt.var_rent_inv_id
AND pt.status ='APPROVED'
AND pt.var_rent_type IN ('ACTUAL','VARIANCE'))
OR EXISTS (SELECT NULL
FROM pn_var_rent_inv_all pvri
WHERE pvri.period_id = NVL(p_period_id,pvri.period_id)
AND pvri.actual_invoiced_amount = 0
AND actual_exp_code = 'Y'
);
PROCEDURE delete_inv_summ (p_var_rent_id IN NUMBER) IS
CURSOR C_EXIST IS
SELECT bp.bkpt_header_id
FROM pn_var_periods_all pd, pn_var_lines_all ln, pn_var_bkpts_head_all bp
WHERE pd.var_rent_id = p_var_rent_id
AND pd.period_id = ln.period_id
AND ln.lINe_item_id = bp.lINe_item_id
AND bp.break_type = 'NATURAL';
pn_var_rent_pkg.delete_var_bkpts_det(l_bkpt_header_id);
pn_var_bkpts_head_pkg.delete_row(l_bkpt_header_id);
DELETE FROM pn_var_rent_summ_all
WHERE var_rent_id = p_var_rent_id;
DELETE FROM pn_payment_terms_all
WHERE var_rent_inv_id IN
(SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id);
DELETE FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id;
DELETE FROM pn_var_abatements_all
WHERE var_rent_inv_id IN
(SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id);
END delete_inv_summ;
SELECT 1
INTO l_vol_ready_for_adjust
FROM dual
WHERE EXISTS(SELECT vh.grp_date_id
FROM pn_var_vol_hist_all vh
WHERE actual_exp_code = 'N'
AND period_id = p_period_id
AND EXISTS (SELECT grp.grp_date_id
FROM pn_var_grp_dates_all grp
WHERE actual_exp_code = 'Y'
AND grp.grp_date_id = vh.grp_date_id));
SELECT 1
INTO l_vol_ready_for_adjust
FROM dual
WHERE EXISTS(SELECT vh.grp_date_id
FROM pn_var_vol_hist_all vh
WHERE forecasted_exp_code = 'N'
AND period_id = p_period_id
AND EXISTS (SELECT grp.grp_date_id
FROM pn_var_grp_dates_all grp
WHERE forecasted_exp_code = 'Y'
AND grp.grp_date_id = vh.grp_date_id));
PROCEDURE UPDATE_LOCATION_FOR_VR_TERMS(
p_var_rent_id IN NUMBER,
p_location_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR C_UPD_TERMS IS
SELECT ppi.payment_term_id
FROM PN_PAYMENT_ITEMS_ALL ppi,
PN_PAYMENT_TERMS_ALL ppt,
PN_VAR_RENT_INV_ALL pvri,
PN_LEASES_ALL pl
WHERE DECODE(pl.lease_class_code, 'DIRECT', NVL(ppi.transferred_to_ap_flag,'N'),
'THIRD_PARTY', NVL(ppi.transferred_to_ar_flag,'N'),
'SUB_LEASE', NVL(ppi.transferred_to_ar_flag,'N')) = 'N'
AND ppi.payment_term_id = ppt.payment_term_id
AND ppt.STATUS = 'APPROVED'
AND ppt.lease_id = pl.lease_id
AND ppt.var_rent_inv_id = pvri.var_rent_inv_id
AND pvri.var_rent_id = p_var_rent_id
UNION ALL
SELECT ppt.payment_term_id
FROM PN_PAYMENT_TERMS_ALL ppt,
PN_VAR_RENT_INV_ALL pvri
WHERE ppt.STATUS = 'DRAFT'
AND ppt.var_rent_inv_id = pvri.var_rent_inv_id
AND pvri.var_rent_id = p_var_rent_id;
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_LOCATION_FOR_VR_TERMS (+)');
UPDATE PN_PAYMENT_TERMS_ALL
SET location_id = p_location_id
WHERE payment_term_id = l_payment_term_id(i);
pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.UPDATE_LOCATION_FOR_VR_TERMS (-)');
END UPDATE_LOCATION_FOR_VR_TERMS;
SELECT period_id
,start_date
,end_date
FROM pn_var_periods_all
WHERE var_rent_id = p_vr_id
ORDER BY start_date;
SELECT period_id
,start_date
,end_date
FROM pn_var_periods_all
WHERE period_id = p_prd_id;
SELECT line_item_id
FROM pn_var_lines_all
WHERE period_id = p_prd_id
ORDER BY line_item_id;
SELECT bkhd_start_date
,bkhd_end_date
,bkpt_header_id
,breakpoint_type
FROM pn_var_bkpts_head_all
WHERE line_item_id = p_line_id
ORDER BY bkhd_start_date;
SELECT bkpt_start_date
,bkpt_end_date
,COUNT(bkpt_detail_id) AS bkpt_count
FROM pn_var_bkpts_det_all
WHERE bkpt_header_id = p_bkhd_id
GROUP BY bkpt_start_date, bkpt_end_date
ORDER BY bkpt_start_date;
SELECT period_bkpt_vol_start
,period_bkpt_vol_end
FROM pn_var_bkpts_det_all
WHERE bkpt_header_id = p_bkhd_id
AND bkpt_start_date = p_st_dt
AND bkpt_end_date = p_end_dt
ORDER BY period_bkpt_vol_start;
SELECT count(*)
FROM pn_var_bkpts_det_all
WHERE bkpt_header_id = p_bkhd_id
AND bkpt_start_date = p_start_date
AND period_bkpt_vol_end IS NULL;
SELECT var_rent_id
,commencement_date
,termination_date
FROM pn_var_rents_all
WHERE var_rent_id = p_vr_id;
SELECT line_default_id
FROM pn_var_line_defaults_all
WHERE var_rent_id = p_vr_id
ORDER BY line_default_id;
SELECT bkhd_start_date
,bkhd_end_date
,bkhd_default_id
FROM pn_var_bkhd_defaults_all
WHERE line_default_id = p_line_def_id
ORDER BY bkhd_start_date;
SELECT bkdt_start_date
,bkdt_end_date
,COUNT(bkdt_default_id) AS bkpt_count
FROM pn_var_bkdt_defaults_all
WHERE bkhd_default_id = p_bkhd_def_id
GROUP BY bkdt_start_date, bkdt_end_date
ORDER BY bkdt_start_date;
SELECT period_bkpt_vol_start
,period_bkpt_vol_end
FROM pn_var_bkdt_defaults_all
WHERE bkhd_default_id = p_bkhd_def_id
AND bkdt_start_date = p_st_dt
AND bkdt_end_date = p_end_dt
ORDER BY period_bkpt_vol_start;
SELECT count(*)
FROM pn_var_bkdt_defaults_all
WHERE bkhd_default_id = p_bkhd_def_id
AND bkdt_start_date = p_start_date
AND period_bkpt_vol_end IS NULL;
l_line_t.DELETE;
SELECT count(*)
INTO l_bktd_num
FROM pn_var_bkdt_defaults_all
WHERE bkhd_default_id = bkhd_rec.bkhd_default_id;
SELECT min(bkdt_start_date)
INTO l_dt_min_st_dt
FROM pn_var_bkdt_defaults_all
WHERE bkhd_default_id = bkhd_rec.bkhd_default_id;
SELECT min(bkhd_start_date)
INTO l_hd_min_st_dt
FROM pn_var_bkhd_defaults_all
WHERE line_default_id = l_line_t(line_rec);
l_period_t.DELETE;
l_period_st_dt_t.DELETE;
l_period_end_dt_t.DELETE;
l_line_t.DELETE;
SELECT start_date , end_date
INTO l_period_st_dt, l_period_end_dt
FROM pn_var_periods_all
WHERE period_id =(SELECT period_id
FROM pn_var_lines_all
WHERE line_item_id = p_line_item_id);
SELECT var_rent_id
,commencement_date
,termination_date
FROM pn_var_rents_all
WHERE var_rent_id = p_vr_id;
SELECT constr_default_id
,constr_start_date
,constr_end_date
,type_code
,amount
FROM pn_var_constr_defaults_all
WHERE var_rent_id = p_var_rent_id
ORDER BY constr_start_date;
SELECT period_id
,start_date
,end_date
FROM pn_var_periods_all
WHERE var_rent_id = p_vr_id
ORDER BY start_date;
SELECT period_id
,start_date
,end_date
FROM pn_var_periods_all
WHERE period_id = p_prd_id;
SELECT constraint_id
,constr_start_date
,constr_end_date
,type_code
FROM pn_var_constraints_all
WHERE period_id = p_period_id
ORDER BY constr_start_date;
l_period_t.DELETE;
l_period_st_dt_t.DELETE;
l_period_end_dt_t.DELETE;
SELECT *
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
-- AND (start_date = NVL(p_start_date,start_date) OR
-- end_date = NVL(p_end_date ,end_date) )
Order by start_date ;
SELECT *
FROM pn_var_rent_dates_all
WHERE var_rent_id = p_var_rent_id ;
/*DELETE pn_var_transactions_all
WHERE period_id = v_new_periods_tbl(i).period_id ;*/
UPDATE pn_var_periods_all
SET end_date = v_new_periods_tbl(i).end_date,
partial_period = l_partial_period, --Srini
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE period_id = v_new_periods_tbl(i).period_id;
put_log('updated period id '|| v_new_periods_tbl(i).period_id || ' with date = ' || v_new_periods_tbl(i).end_date);
SELECT min(start_date) start_date, max(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND period_type = p_pn_var_rent_dates_rec.period_type
AND adjustment_period_flag = 'N'
GROUP BY period_year, quarter_num, period_num
ORDER BY start_date,end_date;
SELECT min(start_date) start_date, max(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND quarter_num IN(1,2,3,4)
AND period_type = p_pn_var_rent_dates_rec.period_type
GROUP BY period_year, quarter_num
ORDER BY start_date;
SELECT min(g1.start_date) start_date
,max(g2.end_date) end_date
FROM gl_periods g1, gl_periods g2
WHERE g1.period_set_name(+) = p_pn_var_rent_dates_rec.gl_period_set_name
AND g2.period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND g1.start_date(+) <= p_vr_term_date
AND g2.end_date >= p_vr_comm_date
AND g1.quarter_num(+) = 1
AND g2.quarter_num = 2
AND g1.period_year(+) = g2.period_year
AND g1.start_date IS NOT NULL
AND g2.end_date IS NOT NULL
AND g1.period_type = p_pn_var_rent_dates_rec.period_type
AND g2.period_type = p_pn_var_rent_dates_rec.period_type
GROUP BY g2.period_year
UNION
SELECT min(g1.start_date) start_date
,max(g2.end_date) end_date
FROM gl_periods g1, gl_periods g2
WHERE g1.period_set_name(+) = p_pn_var_rent_dates_rec.gl_period_set_name
AND g2.period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND g1.start_date(+) <= p_vr_term_date
AND g2.end_date >= p_vr_comm_date
AND g1.quarter_num(+) = 3
AND g2.quarter_num = 4
AND g1.period_year (+)= g2.period_year
AND g1.start_date IS NOT NULL
AND g2.end_date IS NOT NULL
AND g1.period_type = p_pn_var_rent_dates_rec.period_type
AND g2.period_type = p_pn_var_rent_dates_rec.period_type
GROUP BY g2.period_year
order by 1;
SELECT min(start_date) start_date,max(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND period_type = p_pn_var_rent_dates_rec.period_type
GROUP BY period_year
ORDER BY start_date;
SELECT start_date, end_date ,proration_factor
FROM pn_var_periods_all
WHERE period_id = p_period_id;
SELECT *
FROM pn_var_grp_dates_all
WHERE period_id = p_period_id
ORDER BY grp_start_date;
SELECT commencement_date, termination_date
INTO l_vr_comm_dt, l_vr_term_dt
FROM pn_var_rents_all
WHERE var_rent_id = p_pn_var_rent_dates_rec.var_rent_id;
SELECT year_start_date
INTO l_year_st_date
FROM pn_var_rent_dates_all
WHERE var_rent_id = p_pn_var_rent_dates_rec.var_rent_id;
UPDATE pn_var_grp_dates_all
SET grp_end_date = vr_grp_dates(i).l_grp_end_date ,
proration_factor = round(vr_grp_dates(i).l_proration_factor,10), --Chris.T. 20MAY2004
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE grp_date_id = pn_var_grp_dates_rec.grp_date_id;
/*UPDATE pn_var_transactions_all
SET bkpt_end_date = vr_grp_dates(i).l_grp_end_date,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE grp_date_id = pn_var_grp_dates_rec.grp_date_id;*/
PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW(
x_rowid => l_rowId,
x_grp_date_id => l_grpDateId,
x_var_rent_id => p_pn_var_rent_dates_rec.var_rent_id,
x_period_id => p_period_id,
x_grp_start_date => vr_grp_dates(i).l_grp_start_date,
x_grp_end_date => vr_grp_dates(i).l_grp_end_date,
x_group_date => vr_grp_dates(i).l_group_date,
x_reptg_due_date => l_rptg_date,
x_inv_start_date => l_inv_st_date,
x_inv_end_date => l_inv_end_date,
x_invoice_date => l_invoice_due_date,
x_inv_schedule_date => l_inv_schedule_date,
x_proration_factor => vr_grp_dates(i).l_proration_factor, --20MAY2004
x_actual_exp_code => 'N',
x_forecasted_exp_code => 'N',
x_variance_exp_code => 'N',
x_creation_date => SYSDATE,
x_created_by => NVL(FND_PROFILE.VALUE('USER_ID'),1),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(FND_PROFILE.VALUE('USER_ID'),1),
x_last_update_login => NVL(FND_PROFILE.VALUE('USER_ID'),1));
| agreement is extended and user selects to create new breakpoints rather than extending
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
| CALLS : None
|
| ARGUMENTS : IN:
|
| p_var_rent_id - var_rent_id of the VR which got extended.( PK),
| p_extension_end_date - new term date , NULL if no change
| p_old_end_date - old end date, NULL if no change
| ARGUMENTS : OUT:
| x_return_status
| x_return_message
|
| MODIFICATION HISTORY
|
| 29-MAR-2004 Srini Vijayareddy o Created
+===========================================================================*/
PROCEDURE create_new_bkpts(p_var_rent_id IN NUMBER,
p_extension_end_date IN DATE,
p_old_end_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_return_message OUT NOCOPY VARCHAR2)
IS
CURSOR main_vr_cur
IS
SELECT var_rent_id,
commencement_date start_date,
termination_date end_date,
cumulative_vol
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
UPDATE pn_var_rents_all
SET termination_date = p_extension_end_date
WHERE var_rent_id = main_vr_rec.var_rent_id;
SELECT 'Y'
INTO l_default
FROM DUAL
WHERE EXISTS (SELECT var_rent_id
FROM pn_var_bkhd_defaults_all
WHERE var_rent_id = main_vr_rec.var_rent_id);
UPDATE pn_var_line_defaults_all
SET line_end_date = p_extension_end_date,
processed_flag = 0
WHERE var_rent_id = main_vr_rec.var_rent_id
AND line_end_date = p_old_end_date;
SELECT a.proration_rule
FROM pn_var_rents_all a,
pn_var_periods_all b
WHERE a.var_rent_id = NVL(p_var_rent_id,a.var_rent_id)
AND a.var_rent_id = b.var_rent_id
AND b.period_id = NVL(p_period_id,b.period_id);
SELECT proration_rule
INTO l_proration_rule
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
SELECT proration_rule
INTO l_proration_rule
FROM pn_var_rents_all a,
pn_var_periods_all b
WHERE a.var_rent_id = b.var_rent_id
AND b.period_id = NVL(p_period_id,b.period_id);
SELECT MIN(start_date) start_date,
MAX(end_date) end_date ,
period_year
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_type = p_period_type
AND period_year >= TO_NUMBER(TO_CHAR(TO_DATE(p_end_date,'DD/MM/RRRR'),'RRRR'))
AND period_year <= TO_NUMBER(TO_CHAR(TO_DATE(p_extension_end_date,'DD/MM/RRRR'),'RRRR'))
GROUP BY period_year;
SELECT min(start_date) start_date, max(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND period_type = p_pn_var_rent_dates_rec.period_type
AND adjustment_period_flag = 'N'
GROUP BY period_year, quarter_num, period_num
ORDER BY start_date,end_date;
SELECT min(start_date) start_date, max(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND quarter_num IN(1,2,3,4)
AND period_type = p_pn_var_rent_dates_rec.period_type
GROUP BY period_year, quarter_num
ORDER BY start_date;
SELECT min(g1.start_date) start_date
,max(g2.end_date) end_date
FROM gl_periods g1, gl_periods g2
WHERE g1.period_set_name(+) = p_pn_var_rent_dates_rec.gl_period_set_name
AND g2.period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND g1.start_date(+) <= p_vr_term_date
AND g2.end_date >= p_vr_comm_date
AND g1.quarter_num(+) = 1
AND g2.quarter_num = 2
AND g1.period_year(+) = g2.period_year
AND g1.start_date IS NOT NULL
AND g2.end_date IS NOT NULL
AND g1.period_type = p_pn_var_rent_dates_rec.period_type
AND g2.period_type = p_pn_var_rent_dates_rec.period_type
GROUP BY g2.period_year
UNION
SELECT min(g1.start_date) start_date
,max(g2.end_date) end_date
FROM gl_periods g1, gl_periods g2
WHERE g1.period_set_name(+) = p_pn_var_rent_dates_rec.gl_period_set_name
AND g2.period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND g1.start_date(+) <= p_vr_term_date
AND g2.end_date >= p_vr_comm_date
AND g1.quarter_num(+) = 3
AND g2.quarter_num = 4
AND g1.period_year (+)= g2.period_year
AND g1.start_date IS NOT NULL
AND g2.end_date IS NOT NULL
AND g1.period_type = p_pn_var_rent_dates_rec.period_type
AND g2.period_type = p_pn_var_rent_dates_rec.period_type
GROUP BY g2.period_year
order by 1;
SELECT min(start_date) start_date,max(end_date) end_date
FROM gl_periods
WHERE period_set_name = p_pn_var_rent_dates_rec.gl_period_set_name
AND start_date <= p_vr_term_date
AND end_date >= p_vr_comm_date
AND period_type = p_pn_var_rent_dates_rec.period_type
GROUP BY period_year
ORDER BY start_date;
SELECT 'z'
FROM DUAL
WHERE EXISTS ( SELECT a.var_rent_id
FROM pn_var_bkdt_defaults_all a,
pn_var_bkhd_defaults_all b
WHERE a.var_rent_id = p_var_rent_id
AND a.bkhd_default_id = b.bkhd_default_id
AND b.line_default_id = p_line_default_id
AND ( (bkdt_start_date < p_start_date AND
bkdt_end_date > p_end_date) OR
(bkdt_start_date < p_end_date AND
bkdt_end_date > p_end_date))) ;
SELECT bkhd_default_id,
bkhd_start_date,
bkhd_end_date
FROM pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id
AND line_default_id = p_line_default_id
ORDER BY bkhd_start_date;
SELECT 'x'
FROM dual
WHERE EXISTS ( SELECT var_Rent_id
FROM pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id
AND line_default_id = p_line_default_id
AND bkhd_start_date = p_end_date + 1);
SELECT
bkdt_start_date,
bkdt_end_date
FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_var_rent_id
AND bkhd_default_id = p_header_id
ORDER BY bkdt_start_date;
SELECT 'x'
FROM dual
WHERE EXISTS ( SELECT var_Rent_id
FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_var_rent_id
AND bkhd_default_id = p_header_id
AND bkdt_start_date = p_end_date + 1);
SELECT bkpt_header_id,
bkhd_start_date,
bkhd_end_date
FROM pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id
AND line_item_id = p_line_item_id
ORDER BY bkhd_start_date;
SELECT 'x'
FROM dual
WHERE EXISTS ( SELECT var_Rent_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id
AND line_item_id = p_line_item_id
AND bkhd_start_date = p_end_date + 1);
SELECT
bkpt_start_date,
bkpt_end_date
FROM pn_var_bkpts_det_all
WHERE var_rent_id = p_var_rent_id
AND bkpt_header_id = p_header_id
ORDER BY bkpt_start_date;
SELECT 'x'
FROM dual
WHERE EXISTS ( SELECT var_Rent_id
FROM pn_var_bkpts_det_all
WHERE var_rent_id = p_var_rent_id
AND bkpt_header_id = p_header_id
AND bkpt_start_date = p_end_date + 1);
SELECT 'x'
FROM dual
WHERE EXISTS ( SELECT var_rent_id
FROM pn_var_rents_all
WHERE agreement_template_id = p_template_id);
/*SELECT 1
INTO l_bkpts_exists
FROM dual
WHERE EXISTS ( SELECT 1
FROM pn_var_transactions_all
WHERE var_rent_id = p_var_rent_id);
SELECT partial_period
FROM pn_var_periods_all
WHERE period_id = p_period_id;
SELECT X_VAR_RENT_END_DATE - X_VAR_RENT_START_DATE + 1
INTO l_days
FROM dual;
PROCEDURE update_bkpt_details(p_var_rent_id IN NUMBER,
p_bkdt_dflt_id IN NUMBER,
p_bkpt_rate IN NUMBER)
IS
BEGIN
UPDATE pn_var_bkpts_det_all
SET bkpt_rate = p_bkpt_rate
WHERE var_rent_id = p_var_rent_id
AND bkdt_default_id = p_bkdt_dflt_id;
END update_bkpt_details;
Update pn_var_bkdt_defaults_all
SET bkdt_start_date = p_bkdt_st_date,
bkdt_end_date = p_bkdt_end_date
where bkhd_default_id = p_bkhd_default_id
and bkdt_start_date = p_bkdt_st_date_old
and bkdt_end_date = p_bkdt_end_date_old
and bkdt_default_id <> p_bkdt_default_id;
PROCEDURE delete_vr_setup ( p_var_rent_id IN NUMBER
,p_new_termn_date IN DATE)
IS
BEGIN
pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_vr_setup (+)');
/* Delete the data from main tables */
DELETE pn_var_vol_hist_all
WHERE start_date > p_new_termn_date
AND period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
DELETE pn_var_bkpts_det_all
WHERE bkpt_start_date > p_new_termn_date
AND var_rent_id = p_var_rent_id;
DELETE pn_var_bkpts_head_all
WHERE bkhd_start_date > p_new_termn_date
AND var_rent_id = p_var_rent_id;
DELETE pn_var_deductions_all
WHERE start_date > p_new_termn_date
AND period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
DELETE pn_var_rent_summ_all
WHERE grp_date_id IN (SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND grp_start_date > p_new_termn_date);
DELETE pn_var_constraints_all
WHERE constr_start_date > p_new_termn_date
AND period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
DELETE pn_var_lines_all
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND start_date > p_new_termn_date);
/* Delete data from defaults table */
DELETE pn_var_bkdt_defaults_all
WHERE var_rent_id = p_var_rent_id
AND bkdt_start_date > p_new_termn_date;
DELETE pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id
AND bkhd_start_date > p_new_termn_date;
DELETE pn_var_line_defaults_all
WHERE line_start_date >= p_new_termn_date
AND var_rent_id = p_var_rent_id;
DELETE pn_var_constr_defaults_all
WHERE constr_start_date > p_new_termn_date
AND var_rent_id = p_var_rent_id;
DELETE pn_var_abat_defaults_all
WHERE start_date > p_new_termn_date
AND var_rent_id = p_var_rent_id;
pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_vr_setup (-)');
END delete_vr_setup;
SELECT DISTINCT pvi.invoice_date, pvi.period_id
FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp
WHERE pvp.period_id = pvi.period_id
AND pvp.start_date > p_new_termn_date
AND pvp.var_rent_id = p_var_rent_id;
/* This cursor fetches the invoices, which are not deleted, for the
periods starting after new termination date */
CURSOR rent_inv_cur ( p_period_id NUMBER
,p_invoice_date DATE
,p_adjust_num NUMBER) IS
SELECT *
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND (NVL(actual_exp_code, 'N') = 'Y' OR
NVL(variance_exp_code, 'N') = 'Y' OR
NVL(forecasted_exp_code, 'N') = 'Y' OR
NVL(true_up_exp_code,'N') = 'Y')
/*AND NVL(actual_invoiced_amount, 0) <> 0*/
AND invoice_date = p_invoice_date
AND adjust_num = p_adjust_num;
/* This cursor fetches the invoices, which are not deleted, for the
periods starting after new termination date */
CURSOR term_status_cur ( p_period_id NUMBER
,p_invoice_date DATE
,p_adjust_num NUMBER) IS
SELECT variance_exp_code, forecasted_exp_code
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND (NVL(actual_exp_code, 'N') = 'Y' OR
NVL(variance_exp_code, 'N') = 'Y' OR
NVL(forecasted_exp_code, 'N') = 'Y' OR
NVL(true_up_exp_code,'N') = 'Y')
/*AND NVL(actual_invoiced_amount, 0) <> 0*/
AND invoice_date = p_invoice_date
AND adjust_num = p_adjust_num;
SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND start_date > p_new_termn_date
ORDER BY start_date DESC;
SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id;
SELECT period_id
FROM pn_var_periods_all per
WHERE per.var_rent_id = p_var_rent_id
AND per.start_date > p_new_termn_date
AND EXISTS (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE period_id = per.period_id);
SELECT payment_term_id
FROM pn_payment_terms_all
WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE invoice_date = p_invoice_date
AND var_rent_id = p_var_rent_id);
SELECT max(adjust_num) max_adjust_num
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND invoice_date = p_invoice_date;
SELECT SUM(actual_invoiced_amount) actual_amount
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id IN ( SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND (NVL(actual_exp_code, 'N') = 'Y' OR
NVL(variance_exp_code, 'N') = 'Y' OR
NVL(forecasted_exp_code, 'N') = 'Y' OR
NVL(true_up_exp_code, 'N') = 'Y')
/*AND NVL(actual_invoiced_amount, 0) <> 0*/
AND invoice_date = p_invoice_date);
/* Delete draft terms for invoices for all periods which lie after the new termination date */
DELETE pn_payment_terms_all
WHERE status = 'DRAFT'
AND var_rent_inv_id IN (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp
WHERE pvi.period_id = pvp.period_id
AND pvp.var_rent_id = p_var_rent_id
AND pvp.start_date > p_new_termn_date);
/* Delete invoices for which there are no terms */
DELETE pn_var_rent_inv_all
WHERE var_rent_inv_id NOT IN (SELECT ppt.var_rent_inv_id
FROM pn_payment_terms_all ppt, pn_var_rent_inv_all pvi, pn_var_periods_all pvp
WHERE ppt.var_rent_inv_id = pvi.var_rent_inv_id
AND pvi.period_id = pvp.period_id
AND pvp.var_rent_id = p_var_rent_id
AND pvp.start_date > p_new_termn_date)
AND var_rent_id = p_var_rent_id
AND period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND start_date > p_new_termn_date);
/* If invoice exists in this period then exit from the loop as you need not delete any more periods */
pnp_debug_pkg.debug ('exiting ...');
/* Delete group dates and periods for which there exists no invoice */
DELETE pn_var_grp_dates_all
WHERE period_id = period_rec.period_id;
DELETE pn_var_periods_all
WHERE period_id = period_rec.period_id;
pnp_debug_pkg.debug ('Loop through the invoices which are not deleted to create negative terms for each of the approved term ...');
/* Loop through the invoices which are not deleted to create negative terms for each of the approved term */
FOR invoice_date_rec IN invoice_date_cur LOOP
FOR max_adjust_num_rec IN max_adjust_num_cur(invoice_date_rec.period_id, invoice_date_rec.invoice_date)
LOOP
l_max_adjust_num := max_adjust_num_rec.max_adjust_num;
/* If the amount for reversal term is 0 , do not insert an invoice */
IF l_actual_invoiced_amount <> 0 OR (l_variance_exp_code = 'N' AND l_forecasted_exp_code = 'Y' )THEN
/* Loop for the invoice with amx adjust num for a aprticular invoice date */
FOR rent_inv_rec IN rent_inv_cur(invoice_date_rec.period_id, invoice_date_rec.invoice_date, l_max_adjust_num )
LOOP
IF l_variance_exp_code = 'N' AND l_forecasted_exp_code = 'Y'
THEN
l_var_term_status := 'N';
/* Set var rent inv id to null before inserting a row */
l_var_rent_inv_id := NULL;
pn_var_rent_inv_pkg.insert_row ( x_rowid => l_rowid,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => l_max_adjust_num + 1,
x_invoice_date => rent_inv_rec.invoice_date,
x_for_per_rent => rent_inv_rec.for_per_rent ,
x_tot_act_vol => 0,
x_act_per_rent => 0,
x_constr_actual_rent => 0,
x_abatement_appl => rent_inv_rec.abatement_appl,
x_rec_abatement => rent_inv_rec.rec_abatement,
x_rec_abatement_override => rent_inv_rec.rec_abatement_override,
x_negative_rent => rent_inv_rec.negative_rent ,
x_actual_invoiced_amount => -l_actual_invoiced_amount,
x_period_id => rent_inv_rec.period_id,
x_var_rent_id => p_var_rent_id,
x_forecasted_term_status => rent_inv_rec.forecasted_term_status,
x_variance_term_status => l_var_term_status,
x_actual_term_status => 'N', --rent_inv_rec.actual_term_status, --Bug#6490896
x_forecasted_exp_code => 'N',
x_variance_exp_code => 'N',
x_actual_exp_code => 'N',
x_credit_flag => 'Y',
x_comments => 'negative invoices',
x_attribute_category => rent_inv_rec.attribute_category,
x_attribute1 => rent_inv_rec.attribute1,
x_attribute2 => rent_inv_rec.attribute2,
x_attribute3 => rent_inv_rec.attribute3,
x_attribute4 => rent_inv_rec.attribute4,
x_attribute5 => rent_inv_rec.attribute5,
x_attribute6 => rent_inv_rec.attribute6,
x_attribute7 => rent_inv_rec.attribute7,
x_attribute8 => rent_inv_rec.attribute8,
x_attribute9 => rent_inv_rec.attribute9,
x_attribute10 => rent_inv_rec.attribute10,
x_attribute11 => rent_inv_rec.attribute11,
x_attribute12 => rent_inv_rec.attribute12,
x_attribute13 => rent_inv_rec.attribute13,
x_attribute14 => rent_inv_rec.attribute14,
x_attribute15 => rent_inv_rec.attribute15,
x_creation_date => l_date,
x_created_by => nvl(fnd_profile.value('user_id'),1),
x_last_update_date => l_date,
x_last_updated_by => nvl(fnd_profile.value('user_id'),1),
x_last_update_login => nvl(fnd_profile.value('user_id'),1),
x_true_up_amount => rent_inv_rec.true_up_amt,
x_true_up_status => rent_inv_rec.true_up_status,
x_true_up_exp_code => rent_inv_rec.true_up_exp_code,
x_org_id => rent_inv_rec.org_id);
/* Update the periods after new termination date - set status = 'Reversed' */
UPDATE pn_var_periods_all
SET status = pn_var_rent_pkg.status
WHERE var_rent_id = p_var_rent_id
AND start_date > p_new_termn_date;
SELECT cal.reptg_day_of_month,
cal.invg_day_of_month,
cal.reptg_days_after,
cal.invg_days_after,
vr.cumulative_vol,
DECODE(cal.reptg_freq_code, 'MON', 1,
'QTR', 3,
'SA', 6,
'YR', 12,
NULL) reptg_freq_code
FROM pn_var_rents_all vr, pn_var_rent_dates_all cal
WHERE vr.var_rent_id = p_var_rent_id
AND cal.var_rent_id = vr.var_rent_id;
SELECT grp_start_date,
grp_date_id,
grp_end_date,
inv_start_date,
inv_end_date,
group_date,
invoice_date,
period_id
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN grp_start_date AND grp_end_date;
SELECT bkhd_default_id
FROM pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN bkhd_start_date AND bkhd_end_date;
SELECT bkdt_default_id, bkhd_default_id
FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN bkdt_start_date AND bkdt_end_date;
SELECT vol_hist_id, line_item_id
FROM pn_var_vol_hist_all
WHERE period_id = p_period_id
AND p_new_termn_date BETWEEN start_date AND end_date;
SELECT 'x' bkpt_exists
FROM DUAL
WHERE EXISTS (SELECT bkhd_default_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id
AND bkhd_default_id IS NOT NULL);
SELECT max(inv_end_date) inv_end_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND inv_end_date <= p_new_termn_date;
UPDATE pn_var_deductions_all
SET end_date = p_new_termn_date
WHERE period_id = p_period_id
AND p_new_termn_date BETWEEN start_date AND end_date;
UPDATE pn_var_grp_dates_all
SET grp_end_date = p_new_termn_date,
proration_factor = round(l_proration_factor,10),
reptg_due_date = l_due_date
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN grp_start_date AND grp_end_date;
PN_VAR_RENT_PKG.DELETE_REPORT_DATE_ROW(p_var_rent_id, p_new_termn_date);
UPDATE pn_var_report_dates_all
SET report_end_date = p_new_termn_date
WHERE grp_date_id = l_group_date_id
AND p_new_termn_date BETWEEN report_start_date AND report_end_date;
UPDATE pn_var_grp_dates_all
SET inv_end_date = p_new_termn_date,
inv_schedule_date = l_inv_sch_date
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN inv_start_date AND inv_end_date;
UPDATE pn_var_constraints_all
SET constr_end_date = p_new_termn_date
WHERE period_id = p_period_id
AND p_new_termn_date BETWEEN constr_start_date AND constr_end_date;
/* Update the Breakpoint detail records in defaults */
FOR bkdt_defaults_rec IN bkdt_defaults_cur LOOP
pn_var_bkdt_defaults_pkg.modify_row (x_bkdt_default_id => bkdt_defaults_rec.bkdt_default_id,
x_bkhd_default_id => bkdt_defaults_rec.bkhd_default_id,
x_bkdt_end_date => p_new_termn_date);
/* Update the Breakpoint header records in defaults */
FOR rec IN bkhd_default_cur LOOP
pn_var_bkhd_defaults_pkg.modify_row (x_bkhd_default_id => rec.bkhd_default_id,
x_bkhd_end_date => p_new_termn_date);
DELETE FROM pn_var_bkpts_det_all
WHERE var_rent_id = p_var_rent_id;
DELETE FROM pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id;
UPDATE pn_var_constr_defaults_all
SET constr_end_date = p_new_termn_date
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN constr_start_date AND constr_end_date;
UPDATE pn_var_line_defaults_all
SET line_end_date = p_new_termn_date
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN line_start_date AND line_end_date;
/* Delete all abatements whose start date is after the new max inv end date
since start and end dates of abatements must correspond to that of
invoice periods */
DELETE FROM pn_var_abat_defaults_all
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN start_date AND end_date
AND start_date > l_inv_end_dt;
/*Update all abatements so that their end date is the new max inv end date*/
UPDATE pn_var_abat_defaults_all
SET end_date = l_inv_end_dt
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN start_date AND end_date;
UPDATE pn_var_periods_all
SET end_date = p_new_termn_date,
Partial_period = 'Y'
WHERE var_rent_id = p_var_rent_id
AND period_id = p_period_id;
SELECT DISTINCT invoice_date
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND invoice_date > p_new_termn_date;
SELECT *
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND (NVL(actual_exp_code, 'N') = 'Y' OR
NVL(variance_exp_code, 'N') = 'Y' OR
NVL(forecasted_exp_code, 'N') = 'Y')
/*AND NVL(actual_invoiced_amount, 0) <> 0*/
AND invoice_date = p_invoice_date
AND adjust_num = p_adjust_num
AND true_up_amt IS NULL
AND true_up_status IS NULL
AND true_up_exp_code IS NULL;
/* This cursor fetches the invoices, which are not deleted, for the
periods starting after new termination date */
CURSOR term_status_cur ( p_period_id NUMBER
,p_invoice_date DATE
,p_adjust_num NUMBER) IS
SELECT variance_exp_code, forecasted_exp_code
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND (NVL(actual_exp_code, 'N') = 'Y' OR
NVL(variance_exp_code, 'N') = 'Y' OR
NVL(forecasted_exp_code, 'N') = 'Y')
/*AND NVL(actual_invoiced_amount, 0) <> 0*/
AND invoice_date = p_invoice_date
AND adjust_num = p_adjust_num
AND true_up_amt IS NULL
AND true_up_status IS NULL
AND true_up_exp_code IS NULL;
SELECT payment_term_id
FROM pn_payment_terms_all
WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE invoice_date = p_invoice_date
AND var_rent_id = p_var_rent_id);
SELECT max(adjust_num) max_adjust_num
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND invoice_date = p_invoice_date
AND true_up_amt IS NULL
AND true_up_status IS NULL
AND true_up_exp_code IS NULL;
SELECT SUM(actual_invoiced_amount) actual_amount
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id IN ( SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND (NVL(actual_exp_code, 'N') = 'Y' OR
NVL(variance_exp_code, 'N') = 'Y' OR
NVL(forecasted_exp_code, 'N') = 'Y')
/*AND NVL(actual_invoiced_amount, 0) <> 0*/
AND invoice_date = p_invoice_date);
/* Delete draft terms for invoices for this period */
DELETE pn_payment_terms_all
WHERE status = 'DRAFT'
AND var_rent_inv_id IN (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp
WHERE pvi.period_id = p_period_id );
/* Delete invoices for which there are no terms */
DELETE pn_var_rent_inv_all
WHERE period_id = p_period_id
AND actual_exp_code = 'N'
AND forecasted_exp_code = 'N'
AND variance_exp_code = 'N';
/* If the amount for reversal term is 0 , do not insert an invoice */
IF l_actual_invoiced_amount <> 0 OR (l_variance_exp_code = 'N' AND l_forecasted_exp_code = 'Y' ) THEN
/* Loop for the invoice with amx adjust num for a aprticular invoice date */
FOR invoice_rec IN invoice_cur(p_period_id, invoice_date_rec.invoice_date, l_max_adjust_num )
LOOP
IF l_variance_exp_code = 'N' AND l_forecasted_exp_code = 'Y'
THEN
l_var_term_status := 'N';
/* Set var rent inv id to null before inserting a row */
l_var_rent_inv_id := NULL;
pn_var_rent_inv_pkg.insert_row ( x_rowid => l_rowid,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => l_max_adjust_num + 1,
x_invoice_date => invoice_rec.invoice_date,
x_for_per_rent => invoice_rec.for_per_rent,
x_tot_act_vol => 0,
x_act_per_rent => 0,
x_constr_actual_rent => 0,
x_abatement_appl => invoice_rec.abatement_appl,
x_rec_abatement => invoice_rec.rec_abatement,
x_rec_abatement_override => invoice_rec.rec_abatement_override,
x_negative_rent => invoice_rec.negative_rent ,
x_actual_invoiced_amount => - l_actual_invoiced_amount,
x_period_id => invoice_rec.period_id,
x_var_rent_id => p_var_rent_id,
x_forecasted_term_status => invoice_rec.forecasted_term_status,
x_variance_term_status => l_var_term_status,
x_actual_term_status => 'N', --invoice_rec.actual_term_status, Bug#6490896
x_forecasted_exp_code => 'N',
x_variance_exp_code => 'N',
x_actual_exp_code => 'N',
x_credit_flag => 'Y',
x_comments => 'negative invoices',
x_attribute_category => invoice_rec.attribute_category,
x_attribute1 => invoice_rec.attribute1,
x_attribute2 => invoice_rec.attribute2,
x_attribute3 => invoice_rec.attribute3,
x_attribute4 => invoice_rec.attribute4,
x_attribute5 => invoice_rec.attribute5,
x_attribute6 => invoice_rec.attribute6,
x_attribute7 => invoice_rec.attribute7,
x_attribute8 => invoice_rec.attribute8,
x_attribute9 => invoice_rec.attribute9,
x_attribute10 => invoice_rec.attribute10,
x_attribute11 => invoice_rec.attribute11,
x_attribute12 => invoice_rec.attribute12,
x_attribute13 => invoice_rec.attribute13,
x_attribute14 => invoice_rec.attribute14,
x_attribute15 => invoice_rec.attribute15,
x_creation_date => l_date,
x_created_by => nvl(fnd_profile.value('user_id'),1),
x_last_update_date => l_date,
x_last_updated_by => nvl(fnd_profile.value('user_id'),1),
x_last_update_login => nvl(fnd_profile.value('user_id'),1),
x_true_up_amount => invoice_rec.true_up_amt,
x_true_up_status => invoice_rec.true_up_status,
x_true_up_exp_code => invoice_rec.true_up_exp_code,
x_org_id => invoice_rec.org_id);
PROCEDURE delete_var_agreement ( p_var_rent_id IN NUMBER)
IS
BEGIN
pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_var_agreement (+)');
/* Delete the data from main tables */
DELETE pn_var_vol_hist_all
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
DELETE pn_var_bkpts_det_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_deductions_all
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
DELETE pn_var_rent_summ_all
WHERE grp_date_id IN (SELECT grp_date_id
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id);
DELETE pn_var_constraints_all
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
DELETE pn_var_lines_all
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
/* Delete data from defaults table */
DELETE pn_var_bkdt_defaults_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_line_defaults_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_constr_defaults_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_payment_terms_all
WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id);
DELETE pn_var_rent_inv_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_periods_all
WHERE var_rent_id = p_var_rent_id;
DELETE pn_var_rents_all
WHERE var_rent_id = p_var_rent_id;
pnp_debug_pkg.debug ('pn_var_rent_pkg.delete_var_agreement (-)');
END delete_var_agreement;
SELECT var_rent_id, commencement_date
FROM pn_var_rents_all
WHERE lease_id = NVL(p_lease_id, lease_id)
AND var_rent_id = NVL (p_var_rent_id, var_rent_id)
AND (( termination_date = p_old_termn_date) OR
(termination_date < p_old_termn_date AND termination_date > p_new_termn_date))
AND commencement_date <= p_new_termn_date ;
SELECT period_id, end_date
FROM pn_var_periods_all
WHERE var_rent_id = l_var_rent_id
AND p_new_termn_date BETWEEN start_date AND end_date;
SELECT var_rent_id
FROM pn_var_rents_all
WHERE lease_id = p_lease_id
AND commencement_date > p_new_termn_date
AND commencement_date < p_old_termn_date ;
SELECT 'Y' approve_term_exits
FROM pn_payment_terms_all
WHERE status = 'APPROVED'
AND var_rent_inv_id IN (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp,
pn_var_rents_all pvr
WHERE pvi.period_id = pvp.period_id
AND pvr.var_rent_id = pvp.var_rent_id
AND pvr.var_rent_id = p_var_rent_id);
SELECT bkhd_default_id
FROM pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN bkhd_start_date AND bkhd_end_date;
SELECT bkdt_default_id, bkhd_default_id
FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_var_rent_id
AND p_new_termn_date BETWEEN bkdt_start_date AND bkdt_end_date;
SELECT max(inv_end_date) inv_end_date
FROM pn_var_grp_dates_all
WHERE var_rent_id = p_var_rent_id
AND inv_end_date <= p_new_termn_date;
/* Delete Vol hist, Bkpt Details, bkpt headers, lines, deductions, vr summary rows,
group dates, constraints for agreement */
Delete_vr_setup ( p_var_rent_id => var_rent_rec.var_rent_id
,p_new_termn_date => p_new_termn_date);
/* Update the Breakpoint header records in defaults */
FOR rec IN bkhd_default_cur(var_rent_rec.var_rent_id) LOOP
pn_var_bkhd_defaults_pkg.modify_row (x_bkhd_default_id => rec.bkhd_default_id,
x_bkhd_end_date => p_new_termn_date);
UPDATE pn_var_constr_defaults_all
SET constr_end_date = p_new_termn_date
WHERE var_rent_id = var_rent_rec.var_rent_id
AND p_new_termn_date BETWEEN constr_start_date AND constr_end_date;
UPDATE pn_var_line_defaults_all
SET line_end_date = p_new_termn_date
WHERE var_rent_id = var_rent_rec.var_rent_id
AND p_new_termn_date BETWEEN line_start_date AND line_end_date;
/* Delete all abatements whose start date is after the new max inv end date
since start and end dates of abatements must correspond to that of
invoice periods */
DELETE FROM pn_var_abat_defaults_all
WHERE var_rent_id = var_rent_rec.var_rent_id
AND p_new_termn_date BETWEEN start_date AND end_date
AND start_date > l_inv_end_dt;
/*Update all abatements so that their end date is the new max inv end date*/
UPDATE pn_var_abat_defaults_all
SET end_date = l_inv_end_dt
WHERE var_rent_id = var_rent_rec.var_rent_id
AND p_new_termn_date BETWEEN start_date AND end_date;
/* update the end date of agreement to new termination date */
UPDATE pn_var_rents_all
SET termination_date = p_new_termn_date
WHERE var_rent_id = var_rent_rec.var_rent_id;
/* update the bkpt_update_flag to 'Y for VR agreement */
UPDATE pn_var_lines_all
SET bkpt_update_flag = 'Y',
sales_vol_update_flag = 'Y'
WHERE var_rent_id = var_rent_rec.var_rent_id;
delete_var_agreement(p_var_rent_id => variable_rent_rec.var_rent_id);
SELECT line_default_id
FROM pn_var_line_defaults_all
WHERE var_rent_id = p_var_rent_id;
SELECT bkhd_default_id
FROM pn_var_bkhd_defaults_all
WHERE line_default_id = p_linedefid;
SELECT 'x' bkpt_exists
FROM DUAL
WHERE EXISTS (SELECT bkhd_default_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id
AND bkhd_default_id IS NOT NULL);
SELECT 'x'
FROM DUAL
WHERE EXISTS (SELECT constraint_id
FROM pn_var_constraints_all
WHERE period_id IN (SELECT PERIOD_ID
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id)
AND constr_default_id IS NOT NULL);
SELECT bkhd_default_id
FROM pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id
AND bkhd_end_date = p_old_termn_date;
SELECT bkdt_default_id, bkhd_default_id
FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_var_rent_id
AND bkdt_end_date = p_old_termn_date
AND bkhd_default_id IN ( SELECT bkhd_default_id
FROM pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id
AND bkhd_end_date = p_new_termn_date
AND break_type = 'ARTIFICIAL');
UPDATE pn_var_line_defaults_all
SET line_end_date = p_new_termn_date
WHERE var_rent_id = p_var_rent_id
AND line_end_date = p_old_termn_date;
/* Update the Breakpoint header records in defaults */
FOR rec IN bkhd_default_cur LOOP
pn_var_bkhd_defaults_pkg.modify_row (x_bkhd_default_id => rec.bkhd_default_id,
x_bkhd_end_date => p_new_termn_date);
/* Update the Breakpoint detail records in defaults */
FOR bkdt_defaults_rec IN bkdt_defaults_cur LOOP
pn_var_bkdt_defaults_pkg.modify_row (x_bkdt_default_id => bkdt_defaults_rec.bkdt_default_id,
x_bkhd_default_id => bkdt_defaults_rec.bkhd_default_id,
x_bkdt_end_date => p_new_termn_date);
UPDATE pn_var_constr_defaults_all
SET constr_end_date = p_new_termn_date
WHERE var_rent_id = p_var_rent_id
AND constr_end_date = p_old_termn_date;
DELETE FROM pn_var_bkpts_det_all
WHERE var_rent_id = p_var_rent_id;
DELETE FROM pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id;
DELETE FROM pn_var_constraints_all
WHERE period_id IN (SELECT period_id
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id);
SELECT period_id, start_date, end_date
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND start_date > ( SELECT end_date
FROM pn_var_periods_all
WHERE period_id = p_period_id);
SELECT end_date
FROM pn_var_periods_all
WHERE period_id = p_period_id;
SELECT sales_type_code
,item_category_code
,comments
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id
,line_template_id
,agreement_template_id
,line_default_id
,var_rent_id
,line_item_id
FROM pn_var_lines_all
WHERE period_id = p_period_id;
SELECT bkpt_header_id,
line_item_id,
break_type,
base_rent_type,
natural_break_rate,
base_rent,
breakpoint_type,
bkhd_default_id,
var_rent_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id
FROM pn_var_bkpts_head_all
WHERE line_item_id = p_line_item_id
AND bkhd_end_date = p_end_date;
SELECT period_bkpt_vol_start,
period_bkpt_vol_end,
group_bkpt_vol_start,
group_bkpt_vol_end,
bkpt_rate,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
annual_basis_amount,
bkdt_default_id
FROM pn_var_bkpts_det_all
WHERE bkpt_header_id = p_bkpt_header_id
AND bkpt_end_date = p_end_date;
SELECT constr_cat_code
,type_code
,amount
,comments
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id
,agreement_template_id
,constr_template_id
,constr_default_id
FROM pn_var_constraints_all
WHERE period_id = p_period_id
AND constr_end_date = p_end_date;
pnp_debug_pkg.debug ('period for which data is being inserted is ..'||period_rec.period_id);
pn_var_lines_pkg.insert_row(x_rowid => l_rowid,
x_line_item_id => l_line_item_id,
x_line_item_num => l_line_item_num,
x_period_id => period_rec.period_id,
x_sales_type_code => line_rec.sales_type_code,
x_item_category_code => line_rec.item_category_code,
x_comments => line_rec.comments,
x_attribute_category => line_rec.attribute_category ,
x_attribute1 => line_rec.attribute1,
x_attribute2 => line_rec.attribute2,
x_attribute3 => line_rec.attribute3,
x_attribute4 => line_rec.attribute4,
x_attribute5 => line_rec.attribute5,
x_attribute6 => line_rec.attribute6,
x_attribute7 => line_rec.attribute7,
x_attribute8 => line_rec.attribute8,
x_attribute9 => line_rec.attribute9,
x_attribute10 => line_rec.attribute10,
x_attribute11 => line_rec.attribute11,
x_attribute12 => line_rec.attribute12,
x_attribute13 => line_rec.attribute13,
x_attribute14 => line_rec.attribute14,
x_attribute15 => line_rec.attribute15,
x_org_id => line_rec.org_id,
x_creation_date => sysdate,
x_created_by => NVL(fnd_profile.value('USER_ID'),0),
x_last_update_date => sysdate,
x_last_updated_by => NVL(fnd_profile.value('USER_ID'),0),
x_last_update_login => NVL(fnd_profile.value('USER_ID'),0),
x_line_template_id => line_rec.line_template_id,
x_agreement_template_id => line_rec.agreement_template_id,
x_line_default_id => line_rec.line_default_id,
x_var_rent_id => p_var_rent_id);
pnp_debug_pkg.debug ('line item inserted is l_line_item_id ..'||l_line_item_id);
pn_var_bkpts_head_pkg.insert_row(x_rowid => l_rowid,
x_bkpt_header_id => l_bkpt_header_id,
x_line_item_id => l_line_item_id,
x_period_id => period_rec.period_id,
x_break_type => bkpt_head_rec.break_type,
x_base_rent_type => bkpt_head_rec.base_rent_type,
x_natural_break_rate => bkpt_head_rec.natural_break_rate,
x_base_rent => bkpt_head_rec.base_rent,
x_breakpoint_type => bkpt_head_rec.breakpoint_type,
x_bkhd_default_id => bkpt_head_rec.bkhd_default_id,
x_bkhd_start_date => period_rec.start_date,
x_bkhd_end_date => period_rec.end_date,
x_var_rent_id => p_var_rent_id,
x_attribute_category => bkpt_head_rec.attribute_category,
x_attribute1 => bkpt_head_rec.attribute1,
x_attribute2 => bkpt_head_rec.attribute2,
x_attribute3 => bkpt_head_rec.attribute3,
x_attribute4 => bkpt_head_rec.attribute4,
x_attribute5 => bkpt_head_rec.attribute5,
x_attribute6 => bkpt_head_rec.attribute6,
x_attribute7 => bkpt_head_rec.attribute7,
x_attribute8 => bkpt_head_rec.attribute8,
x_attribute9 => bkpt_head_rec.attribute9,
x_attribute10 => bkpt_head_rec.attribute10,
x_attribute11 => bkpt_head_rec.attribute11,
x_attribute12 => bkpt_head_rec.attribute12,
x_attribute13 => bkpt_head_rec.attribute13,
x_attribute14 => bkpt_head_rec.attribute14,
x_attribute15 => bkpt_head_rec.attribute15,
x_org_id => bkpt_head_rec.org_id,
x_creation_date => sysdate,
x_created_by => NVL(fnd_profile.value('USER_ID'),0),
x_last_update_date => sysdate,
x_last_updated_by => NVL(fnd_profile.value('USER_ID'),0),
x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0));
pnp_debug_pkg.debug ('breakpoint header inserted is l_bkpt_header_id ..'||l_bkpt_header_id);
pn_var_bkpts_det_pkg.insert_row(x_rowid => l_rowid,
x_bkpt_detail_id => l_bkpt_detail_id,
x_bkpt_detail_num => l_bkpt_detail_num,
x_bkpt_header_id => l_bkpt_header_id,
x_bkpt_start_date => period_rec.start_date,
x_bkpt_end_date => period_rec.end_date,
x_period_bkpt_vol_start => bkpt_detail_rec.period_bkpt_vol_start,
x_period_bkpt_vol_end => bkpt_detail_rec.period_bkpt_vol_end,
x_group_bkpt_vol_start => bkpt_detail_rec.group_bkpt_vol_start,
x_group_bkpt_vol_end => bkpt_detail_rec.group_bkpt_vol_end,
x_bkpt_rate => bkpt_detail_rec.bkpt_rate,
x_bkdt_default_id => bkpt_detail_rec.bkdt_default_id,
x_var_rent_id => p_var_rent_id,
x_comments => bkpt_detail_rec.comments,
x_attribute_category => bkpt_detail_rec.attribute_category,
x_attribute1 => bkpt_detail_rec.attribute1,
x_attribute2 => bkpt_detail_rec.attribute2,
x_attribute3 => bkpt_detail_rec.attribute3,
x_attribute4 => bkpt_detail_rec.attribute4,
x_attribute5 => bkpt_detail_rec.attribute5,
x_attribute6 => bkpt_detail_rec.attribute6,
x_attribute7 => bkpt_detail_rec.attribute7,
x_attribute8 => bkpt_detail_rec.attribute8,
x_attribute9 => bkpt_detail_rec.attribute9,
x_attribute10 => bkpt_detail_rec.attribute10,
x_attribute11 => bkpt_detail_rec.attribute11,
x_attribute12 => bkpt_detail_rec.attribute12,
x_attribute13 => bkpt_detail_rec.attribute13,
x_attribute14 => bkpt_detail_rec.attribute14,
x_attribute15 => bkpt_detail_rec.attribute15,
x_org_id => bkpt_detail_rec.org_id,
x_creation_date => sysdate,
x_created_by => nvl(fnd_profile.value('user_id'),0),
x_last_update_date => sysdate,
x_last_updated_by => nvl(fnd_profile.value('user_id'),0),
x_last_update_login => nvl(fnd_profile.value('user_id'),0),
x_annual_basis_amount => bkpt_detail_rec.annual_basis_amount
);
pnp_debug_pkg.debug ('breakpoint detail inserted is l_bkpt_detail_id ..'||l_bkpt_detail_id);
pn_var_constraints_pkg.insert_row( x_rowid => l_rowid,
x_constraint_id => l_constrid,
x_constraint_num => l_constrnum,
x_period_id => period_rec.period_id,
x_constr_cat_code => constraint_rec.constr_cat_code,
x_type_code => constraint_rec.type_code,
x_amount => constraint_rec.amount,
x_agreement_template_id => constraint_rec.agreement_template_id,
x_constr_template_id => constraint_rec.constr_template_id,
x_constr_default_id => constraint_rec.constr_default_id,
x_comments => constraint_rec.comments,
x_attribute_category => constraint_rec.attribute_category,
x_attribute1 => constraint_rec.attribute1,
x_attribute2 => constraint_rec.attribute2,
x_attribute3 => constraint_rec.attribute3,
x_attribute4 => constraint_rec.attribute4,
x_attribute5 => constraint_rec.attribute5,
x_attribute6 => constraint_rec.attribute6,
x_attribute7 => constraint_rec.attribute7,
x_attribute8 => constraint_rec.attribute8,
x_attribute9 => constraint_rec.attribute9,
x_attribute10 => constraint_rec.attribute10,
x_attribute11 => constraint_rec.attribute11,
x_attribute12 => constraint_rec.attribute12,
x_attribute13 => constraint_rec.attribute13,
x_attribute14 => constraint_rec.attribute14,
x_attribute15 => constraint_rec.attribute15,
x_org_id => constraint_rec.org_id,
x_creation_date => sysdate,
x_created_by => nvl(fnd_profile.value('user_id'),0),
x_last_update_date => sysdate,
x_last_updated_by => nvl(fnd_profile.value('user_id'),0),
x_last_update_login => nvl(fnd_profile.value('user_id'),0),
x_constr_start_date => period_rec.start_date,
x_constr_end_date => period_rec.end_date);
PROCEDURE update_setup_exp ( p_var_rent_id IN NUMBER
,p_old_termn_date IN DATE
,p_period_id IN NUMBER)
IS
CURSOR period_cur IS
SELECT end_date
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND period_id = p_period_id;
pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (+)');
UPDATE pn_var_bkpts_head_all
SET bkhd_end_date = period_rec.end_date
WHERE var_rent_id = p_var_rent_id
AND bkhd_end_date = p_old_termn_date;
UPDATE pn_var_bkpts_det_all
SET bkpt_end_date = period_rec.end_date
WHERE var_rent_id = p_var_rent_id
AND bkpt_end_date = p_old_termn_date;
UPDATE pn_var_constraints_all
SET constr_end_date = period_rec.end_date
WHERE period_id = p_period_id
AND constr_end_date = p_old_termn_date;
pnp_debug_pkg.debug ('pn_var_rent_pkg.update_setup_exp (-)');
SELECT DISTINCT invoice_date
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND NVL(actual_exp_code, 'N') = 'Y'
AND NVL(actual_invoiced_amount, 0) <> 0;
SELECT *
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND (NVL(actual_exp_code, 'N') = 'Y' OR
NVL(variance_exp_code, 'N') = 'Y')
AND NVL(actual_invoiced_amount, 0) <> 0
AND invoice_date = p_invoice_date
AND adjust_num = p_adjust_num;
SELECT SUM(actual_invoiced_amount) actual_amount
FROM pn_var_rent_inv_all
WHERE var_rent_inv_id IN ( SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND (NVL(actual_exp_code, 'N') = 'Y' OR
NVL(variance_exp_code, 'N') = 'Y')
AND NVL(actual_invoiced_amount, 0) <> 0
AND invoice_date = p_invoice_date);
SELECT payment_term_id
FROM pn_payment_terms_all
WHERE var_rent_inv_id IN ( SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND NVL(actual_exp_code, 'N') = 'Y'
AND NVL(actual_invoiced_amount, 0) <> 0);
SELECT max(adjust_num) max_adjust_num
FROM pn_var_rent_inv_all
WHERE period_id = p_period_id
AND invoice_date = p_invoice_date;
l_invoice_inserted BOOLEAN := FALSE;
DELETE pn_payment_terms_all
WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
FROM pn_var_rent_inv_all
WHERE period_id = p_last_period_id
AND NVL(actual_exp_code, 'N') <> 'Y')
AND status <> 'APPROVED';
DELETE pn_var_rent_inv_all
WHERE period_id = p_last_period_id
AND NVL(actual_exp_code, 'N') <> 'Y';
/* If the amount for reversal term is 0 , do not insert an invoice */
IF l_actual_invoiced_amount <> 0 THEN
/* Loop for the invoice with amx adjust num for a aprticular invoice date */
FOR invoice_rec IN invoice_cur(p_last_period_id, invoice_date_rec.invoice_date, l_max_adjust_num ) LOOP
/* Set var rent inv id to null before inserting a row */
l_var_rent_inv_id := NULL;
pn_var_rent_inv_pkg.insert_row ( x_rowid => l_rowid,
x_var_rent_inv_id => l_var_rent_inv_id,
x_adjust_num => l_max_adjust_num + 1,
x_invoice_date => invoice_rec.invoice_date,
x_for_per_rent => invoice_rec.for_per_rent,
x_tot_act_vol => 0,
x_act_per_rent => 0,
x_constr_actual_rent => 0,
x_abatement_appl => invoice_rec.abatement_appl,
x_rec_abatement => invoice_rec.rec_abatement,
x_rec_abatement_override => invoice_rec.rec_abatement_override,
x_negative_rent => invoice_rec.negative_rent ,
x_actual_invoiced_amount => -l_actual_invoiced_amount,
x_period_id => invoice_rec.period_id,
x_var_rent_id => p_var_rent_id,
x_forecasted_term_status => invoice_rec.forecasted_term_status,
x_variance_term_status => invoice_rec.variance_term_status,
x_actual_term_status => 'N', --invoice_rec.actual_term_status, Bug#6490896
x_forecasted_exp_code => 'N',
x_variance_exp_code => 'N',
x_actual_exp_code => 'N',
x_credit_flag => 'Y',
x_comments => 'negative invoices',
x_attribute_category => invoice_rec.attribute_category,
x_attribute1 => invoice_rec.attribute1,
x_attribute2 => invoice_rec.attribute2,
x_attribute3 => invoice_rec.attribute3,
x_attribute4 => invoice_rec.attribute4,
x_attribute5 => invoice_rec.attribute5,
x_attribute6 => invoice_rec.attribute6,
x_attribute7 => invoice_rec.attribute7,
x_attribute8 => invoice_rec.attribute8,
x_attribute9 => invoice_rec.attribute9,
x_attribute10 => invoice_rec.attribute10,
x_attribute11 => invoice_rec.attribute11,
x_attribute12 => invoice_rec.attribute12,
x_attribute13 => invoice_rec.attribute13,
x_attribute14 => invoice_rec.attribute14,
x_attribute15 => invoice_rec.attribute15,
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('user_id'),1),
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('user_id'),1),
x_last_update_login => nvl(fnd_profile.value('user_id'),1),
x_true_up_amount => invoice_rec.true_up_amt,
x_true_up_status => invoice_rec.true_up_status,
x_true_up_exp_code => invoice_rec.true_up_exp_code,
x_org_id => invoice_rec.org_id);
SELECT period_id, partial_period
FROM pn_var_periods_all
WHERE var_rent_id = p_var_rent_id
AND p_old_termn_date BETWEEN start_date AND end_date;
SELECT pvr.var_rent_id
, pvr.cumulative_vol
, pvr.proration_rule
, pvd.use_gl_calendar
, pvd.year_start_date
, pvr.commencement_date
FROM pn_var_rents_all pvr, pn_var_rent_dates_all pvd
WHERE pvr.lease_id = NVL (p_lease_id, pvr.lease_id)
AND pvr.var_rent_id = NVL (p_var_rent_id, pvr.var_rent_id)
AND pvr.termination_date = p_old_termn_date
AND pvd.var_rent_id = pvr.var_rent_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT var_rent_id
FROM pn_var_line_defaults_all
WHERE var_rent_id = p_var_rent_id)
OR EXISTS (SELECT var_rent_id
FROM pn_var_constr_defaults_all
WHERE var_rent_id = p_var_rent_id);
/* Update the vr agreement with the new termination date */
pnp_debug_pkg.debug ('Update the vr agreement with the new termination date ...');
UPDATE pn_var_rents_all
SET termination_date = p_new_termn_date
WHERE var_rent_id = main_vr_rec.var_rent_id;
pnp_debug_pkg.debug ('calling proc update_setup_exp .. if defaults do not exist ...');
update_setup_exp ( p_var_rent_id => main_vr_rec.var_rent_id
, p_old_termn_date => p_old_termn_date
, p_period_id => l_last_period_id);
pn_var_defaults_pkg.delete_default_lines (main_vr_rec.var_rent_id);
/* update the bkpt_update_flag to 'Y for VR agreement */
UPDATE pn_var_lines_all
SET bkpt_update_flag = 'Y',
sales_vol_update_flag = 'Y'
WHERE var_rent_id = main_vr_rec.var_rent_id;
SELECT max(lease_change_id) max_lease_change_id
FROM pn_lease_details_history
WHERE lease_id = p_lease_id;
SELECT lease_termination_date
FROM pn_lease_details_history
WHERE lease_id = p_lease_id
AND new_lease_change_id = p_lease_change_id;
SELECT lease_termination_date
FROM pn_lease_details_history
WHERE lease_id = p_lease_id
AND lease_change_id = p_lease_change_id;
SELECT lease_termination_date
FROM pn_lease_details_all
WHERE lease_change_id = p_lease_change_id;
PN_VAR_RENTS_PKG.delete_var_rent_agreement(p_lease_id => p_lease_id ,
p_termination_dt => l_new_term_date);
SELECT DISTINCT var.var_rent_id
from pn_var_rents_all var
where var.lease_id IN( SELECT distinct lease_id
FROM ( SELECT lease_id
FROM pn_tenancies_all
WHERE location_id in (SELECT location_id
FROM pn_locations_all
WHERE property_id = p_prop_id)
UNION
SELECT lease_id
FROM pn_leases_all
WHERE location_id in (SELECT location_id
FROM pn_locations_all
WHERE property_id = p_prop_id)));
SELECT DISTINCT var.var_rent_id
FROM pn_var_rents_all var
WHERE var.lease_id IN (SELECT lease_id
FROM (SELECT lease_id
FROM pn_tenancies_all
WHERE location_id = p_loc_id
UNION
SELECT lease_id
FROM pn_leases_all
WHERE location_id = p_loc_id));
SELECT DISTINCT var.var_rent_id
FROM pn_var_rents_all var
WHERE var.lease_id = p_lease_id;
SELECT DISTINCT per.period_id period_id,
var.rent_num rent_num
FROM pn_var_periods_all per,
pn_var_rents_all var
WHERE per.var_rent_id = p_var_rent_id
AND var.var_rent_id = p_var_rent_id;
SELECT DISTINCT
sales_type_code,
item_category_code,
org_id
FROM pn_var_lines_all
WHERE var_rent_id = p_var_rent_id;
SELECT count(*) lines_count
FROM pn_var_lines_all
WHERE sales_type_code = p_sales_type_code
AND item_category_code = p_item_category_code
AND period_id = p_per_id;
SELECT per.period_id, per.org_id
FROM pn_var_periods_all per
WHERE per.period_id not in (
SELECT lines.period_id
FROM pn_var_lines_all lines
WHERE lines.var_rent_id = p_var_rent_id
AND nvl(lines.sales_type_code,'-1') = nvl(p_sales_type_code,'-1')
AND nvl(lines.item_category_code,'-1') = nvl(p_item_category_code,'-1'))
AND var_rent_id = p_var_rent_id ;
SELECT bkpt.bkpt_header_id bkpt_header_id,
bkpt.bkhd_start_date start_date,
bkpt.bkhd_end_date end_date,
bkpt.break_type break_type,
bkpt.base_rent_type base_rent_type,
bkpt.natural_break_rate natural_break_rate,
bkpt.base_rent base_rent,
bkpt.breakpoint_type breakpoint_type,
bkpt.org_id org_id,
bkpt.attribute_category attribute_category,
bkpt.attribute1 attribute1,
bkpt.attribute2 attribute2,
bkpt.attribute3 attribute3,
bkpt.attribute4 attribute4,
bkpt.attribute5 attribute5,
bkpt.attribute6 attribute6,
bkpt.attribute7 attribute7,
bkpt.attribute8 attribute8,
bkpt.attribute9 attribute9,
bkpt.attribute10 attribute10,
bkpt.attribute11 attribute11,
bkpt.attribute12 attribute12,
bkpt.attribute13 attribute13,
bkpt.attribute14 attribute14,
bkpt.attribute15 attribute15,
lines.line_default_id line_default_id
FROM pn_var_bkpts_head_all bkpt,
pn_var_lines_all lines
WHERE bkpt.var_rent_id = p_var_rent_id
AND bkpt.line_item_id = lines.line_item_id;
SELECT bkdt.bkpt_detail_id bkpt_detail_id,
bkdt.bkpt_start_date bkpt_start_date,
bkdt.bkpt_end_date bkpt_end_date,
bkdt.period_bkpt_vol_start period_bkpt_vol_start,
bkdt.period_bkpt_vol_end period_bkpt_vol_end,
bkdt.group_bkpt_vol_start group_bkpt_vol_start,
bkdt.group_bkpt_vol_end group_bkpt_vol_end,
bkdt.bkpt_rate bkpt_rate,
bkdt.comments comments ,
bkdt.attribute_category attribute_category,
bkdt.attribute1 attribute1,
bkdt.attribute2 attribute2,
bkdt.attribute3 attribute3,
bkdt.attribute4 attribute4,
bkdt.attribute5 attribute5,
bkdt.attribute6 attribute6,
bkdt.attribute7 attribute7,
bkdt.attribute8 attribute8,
bkdt.attribute9 attribute9,
bkdt.attribute10 attribute10,
bkdt.attribute11 attribute11,
bkdt.attribute12 attribute12,
bkdt.attribute13 attribute13,
bkdt.attribute14 attribute14,
bkdt.attribute15 attribute15,
bkdt.org_id org_id,
bkdt.annual_basis_amount annual_basis_amount,
bkhd.bkhd_default_id bkhd_default_id
FROM pn_var_bkpts_det_all bkdt,
pn_var_bkpts_head_all bkhd
WHERE bkdt.var_rent_id = p_var_rent_id
AND bkdt.bkpt_header_id = bkhd.bkpt_header_id;
SELECT cons.constraint_id
,cons.constraint_num
,cons.last_update_date
,cons.last_updated_by
,cons.creation_date
,cons.created_by
,cons.last_update_login
,cons.period_id
,cons.constr_cat_code
,cons.type_code
,cons.amount
,cons.comments
,cons.attribute_category
,cons.attribute1
,cons.attribute2
,cons.attribute3
,cons.attribute4
,cons.attribute5
,cons.attribute6
,cons.attribute7
,cons.attribute8
,cons.attribute9
,cons.attribute10
,cons.attribute11
,cons.attribute12
,cons.attribute13
,cons.attribute14
,cons.attribute15
,cons.org_id
,cons.agreement_template_id
,cons.constr_template_id
,cons.constr_default_id
,cons.constr_start_date
,cons.constr_end_date
FROM pn_var_constraints_all cons,
pn_var_periods_all per
WHERE per.var_rent_id = p_var_rent_id
AND cons.period_id = per.period_id;
l_varent_tab.DELETE;
SELECT rent_num
INTO l_rent_num
FROM pn_var_rents_all
WHERE var_rent_id = l_var_rent_id;
SELECT sysdate
INTO l_date
FROM dual;
DELETE FROM PN_VAR_LINE_DEFAULTS_ALL
WHERE var_rent_id = l_var_rent_id;
DELETE FROM PN_VAR_BKHD_DEFAULTS_ALL
WHERE var_rent_id = l_var_rent_id;
DELETE FROM PN_VAR_BKDT_DEFAULTS_ALL
WHERE var_rent_id = l_var_rent_id;
DELETE FROM PN_VAR_CONSTR_DEFAULTS_ALL
WHERE var_rent_id = l_var_rent_id;
/* Inserting line into lines defaults table and populating lines with the line default id */
/* Fetch distinct line items from the VR and then populate them into defaults table*/
FOR lines_rec IN lines_cur(l_var_rent_id) LOOP
l_sales_type_code := lines_rec.sales_type_code;
PN_VAR_LINE_DEFAULTS_PKG.INSERT_ROW(
X_ROWID => l_rowid_line_defaults,
X_LINE_DEFAULT_ID => l_linedefaultid,
X_LINE_NUM => l_linedefNum,
X_VAR_RENT_ID => l_var_rent_id,
X_SALES_TYPE_CODE => l_sales_type_code,
X_ITEM_CATEGORY_CODE => l_item_category_code,
X_LINE_TEMPLATE_ID => NULL,
X_AGREEMENT_TEMPLATE_ID => NULL,
X_LINE_START_DATE => NULL,
X_LINE_END_DATE => NULL,
X_PROCESSED_FLAG => NULL,
X_CREATION_DATE => l_date,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => -1,
X_ORG_ID => lines_rec.org_id,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL
);
/* Insert the line item into pn_var_lines_all for thoses
periods where this line does not exists*/
FOR per_rec IN no_per_lines_exists(l_var_rent_id,
l_sales_type_code,
l_item_category_code) LOOP
l_rowId_lines := NULL;
PN_VAR_LINES_PKG.INSERT_ROW (
X_ROWID => l_rowId_lines,
X_LINE_ITEM_ID => l_lineitemId,
X_LINE_ITEM_NUM => l_lineitemNum,
X_PERIOD_ID => per_rec.period_id,
X_SALES_TYPE_CODE => l_sales_type_code,
X_ITEM_CATEGORY_CODE => l_item_category_code,
X_COMMENTS => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_CREATION_DATE => l_date,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => -1,
X_ORG_ID => per_rec.org_id,
X_VAR_RENT_ID => l_var_rent_id,
X_LINE_TEMPLATE_ID => NULL,
X_AGREEMENT_TEMPLATE_ID => NULL,
X_LINE_DEFAULT_ID => l_linedefaultid
);
UPDATE pn_var_lines_all
SET line_default_id = l_linedefaultid
WHERE var_rent_id = l_var_rent_id
AND nvl(sales_type_code,'-1') = nvl(l_sales_type_code,'-1')
AND nvl(item_category_code,'-1') = nvl(l_item_category_code,'-1');
/* Completed Inserting line into lines defaults table and
populating lines with the line default id */
/* Inserting into breakpoint Header defaults and populating
breakpoints with the breakpoint default id */
FOR bkpt_rec IN bkhd_cur(l_var_rent_id) LOOP
l_bkhd_rowid := NULL;
PN_VAR_BKHD_DEFAULTS_PKG.INSERT_ROW(
X_ROWID => l_bkhd_rowid,
X_BKHD_DEFAULT_ID => l_bkhddefault_id,
X_BKHD_DETAIL_NUM => l_linenum,
X_LINE_DEFAULT_ID => bkpt_rec.line_default_id,
X_BKPT_HEAD_TEMPLATE_ID => NULL,
X_AGREEMENT_TEMPLATE_ID => NULL,
X_BKHD_START_DATE => bkpt_rec.start_date,
X_BKHD_END_DATE => bkpt_rec.end_date,
X_BREAK_TYPE => bkpt_rec.break_type,
X_BASE_RENT_TYPE => bkpt_rec.base_rent_type,
X_NATURAL_BREAK_RATE => bkpt_rec.natural_break_rate,
X_BASE_RENT => bkpt_rec.base_rent,
X_BREAKPOINT_TYPE => bkpt_rec.breakpoint_type,
X_BREAKPOINT_LEVEL => NULL,
X_PROCESSED_FLAG => NULL,
X_VAR_RENT_ID => l_var_rent_id,
X_CREATION_DATE => l_date,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => -1,
X_ORG_ID => bkpt_rec.org_id,
X_ATTRIBUTE_CATEGORY => bkpt_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => bkpt_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => bkpt_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => bkpt_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => bkpt_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => bkpt_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => bkpt_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => bkpt_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => bkpt_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => bkpt_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => bkpt_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => bkpt_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => bkpt_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => bkpt_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => bkpt_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => bkpt_rec.ATTRIBUTE15
);
UPDATE pn_var_bkpts_head_all
SET bkhd_default_id = l_bkhddefault_id
WHERE bkpt_header_id = bkpt_rec.bkpt_header_id;
UPDATE pn_var_line_defaults_all
SET line_start_date = (select min(bkhd_start_date)
from pn_var_bkhd_defaults_all
where line_default_id = bkpt_rec.line_default_id)
WHERE line_default_id = bkpt_rec.line_default_id;
UPDATE pn_var_line_defaults_all
SET line_end_date = (select max(bkhd_end_date)
from pn_var_bkhd_defaults_all
where line_default_id = bkpt_rec.line_default_id)
WHERE line_default_id = bkpt_rec.line_default_id;
/* Completed Inserting breakpoints into breakpoint defaults table and
populating breakpoints with the breakpoint default id */
/* Inserting into breakpoint detail defaults and populating breakpoints
details with the breakpoint detail default id */
FOR bkdt_rec IN bkdt_cur(l_var_rent_id) LOOP
l_bkdt_rowId := NULL;
PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW(
X_ROWID => l_bkdt_rowId,
X_BKDT_DEFAULT_ID => l_bkdtdefaultId,
X_BKDT_DETAIL_NUM => l_bkdtdefaultNum,
X_BKHD_DEFAULT_ID => bkdt_rec.BKHD_DEFAULT_ID,
X_BKDT_START_DATE => bkdt_rec.BKPT_START_DATE,
X_BKDT_END_DATE => bkdt_rec.BKPT_END_DATE,
X_PERIOD_BKPT_VOL_START => bkdt_rec.PERIOD_BKPT_VOL_START,
X_PERIOD_BKPT_VOL_END => bkdt_rec.PERIOD_BKPT_VOL_END,
X_GROUP_BKPT_VOL_START => bkdt_rec.GROUP_BKPT_VOL_START,
X_GROUP_BKPT_VOL_END => bkdt_rec.GROUP_BKPT_VOL_END,
X_BKPT_RATE => bkdt_rec.BKPT_RATE,
X_PROCESSED_FLAG => NULL,
X_VAR_RENT_ID => l_var_rent_id,
X_CREATION_DATE => l_date,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => -1,
X_ORG_ID => bkdt_rec.org_id,
X_ANNUAL_BASIS_AMOUNT => bkdt_rec.ANNUAL_BASIS_AMOUNT,
X_ATTRIBUTE_CATEGORY => bkdt_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => bkdt_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => bkdt_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => bkdt_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => bkdt_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => bkdt_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => bkdt_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => bkdt_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => bkdt_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => bkdt_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => bkdt_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => bkdt_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => bkdt_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => bkdt_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => bkdt_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => bkdt_rec.ATTRIBUTE15
);
UPDATE pn_var_bkpts_det_all
SET BKDT_DEFAULT_ID = l_bkdtdefaultId
WHERE BKPT_DETAIL_ID = bkdt_rec.bkpt_detail_id;
/* Completed Inserting breakpoint detail into breakpoint defaults table
and populating breakpoints with the breakpoint default id */
/* Inserting into constraints defaults and populating constraints
details with the constraint detail default id */
FOR constr_rec IN constr_cur(l_var_rent_id) LOOP
l_rowid_constr_defaults := NULL;
PN_VAR_CONSTR_DEFAULTS_PKG.INSERT_ROW
(
X_ROWID => l_rowid_constr_defaults,
X_CONSTR_DEFAULT_ID => l_constrdefaultid,
X_CONSTR_DEFAULT_NUM => l_constrdefNum,
X_VAR_RENT_ID => l_var_rent_id,
X_AGREEMENT_TEMPLATE_ID => NULL,
X_CONSTR_TEMPLATE_ID => NULL,
X_CONSTR_START_DATE => constr_rec.constr_start_date,
X_CONSTR_END_DATE => constr_rec.constr_end_date,
X_CONSTR_CAT_CODE => constr_rec.constr_cat_code,
X_TYPE_CODE => constr_rec.type_code,
X_AMOUNT => constr_rec.amount,
X_CREATION_DATE => l_date,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => -1,
X_ORG_ID => constr_rec.org_id,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL
);
UPDATE pn_var_constraints_all
SET constr_default_id = l_constrdefaultid
WHERE constraint_id = constr_rec.constraint_id;
/* Completed Inserting constraints into constraint defaults table and
populating constraints with the constraints default id */
pnp_debug_pkg.log (' ---------------------------------------------------------------------------');
pnp_debug_pkg.log (' Agreement ' || l_rent_num || ' has been Updated');
SELECT 1
INTO l_inv_exp
FROM dual
WHERE EXISTS ( SELECT inv.var_rent_inv_id
FROM pn_var_rent_inv_all inv
WHERE inv.var_rent_id = p_var_rent_id
AND inv.invoice_date = p_invoice_date
AND (forecasted_exp_code = 'Y'));
SELECT commencement_date
,termination_date
,proration_rule
FROM pn_var_rents_all
WHERE var_rent_id = p_vr_id;
SELECT start_date
,end_date
FROM pn_var_periods_all
WHERE var_rent_id = p_vr_id
ORDER BY start_date;
/* Cursor to select Breakpoints whose start date
is in between given start dates */
CURSOR bkdt_st_dt_cur (p_vr_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE) IS
SELECT bkdt_default_id,
bkdt_start_date,
bkdt_end_date,
period_bkpt_vol_start,
period_bkpt_vol_end,
bkpt_rate
FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_vr_id
AND bkdt_start_date BETWEEN p_start_date AND p_end_date;
/* Cursor to select Breakpoints whose end date
is in between given start dates */
CURSOR bkdt_end_dt_cur (p_vr_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE) IS
SELECT bkdt_default_id,
bkdt_start_date,
bkdt_end_date,
period_bkpt_vol_start,
period_bkpt_vol_end,
bkpt_rate
FROM pn_var_bkdt_defaults_all
WHERE var_rent_id = p_vr_id
AND bkdt_end_date BETWEEN p_start_date AND p_end_date;
l_bkpt_st_tab.DELETE;
l_bkpt_end_tab.DELETE;
l_bkpt_st_tab.DELETE;
l_bkpt_end_tab.DELETE;
l_bkpt_st_tab.DELETE;