The following lines contain the word 'select', 'insert', 'update' or 'delete':
select * from pn_var_line_defaults_all
where var_rent_id = l_old_rentid;
select * from pn_var_bkhd_defaults_all
where line_default_id = p_line_def_id;
select * from pn_var_bkdt_defaults_all
where bkhd_default_id = p_bkhd_def_id;
SELECT pn_var_line_defaults_s.nextval
INTO l_lineDefid
FROM DUAL;
insert into pn_var_line_defaults_all (
line_default_id,
line_num,
var_rent_id,
sales_type_code,
item_category_code,
line_template_id,
agreement_template_id,
line_start_date,
line_end_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
processed_flag
) values (
l_lineDefid,
l_lineNum,
l_new_rentid,
c_lrec.SALES_TYPE_CODE,
c_lrec.ITEM_CATEGORY_CODE,
c_lrec.line_template_id,
c_lrec.AGREEMENT_TEMPLATE_ID,
c_lrec.LINE_START_DATE,
c_lrec.LINE_END_DATE,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c_lrec.ORG_ID,
c_lrec.processed_flag
);
SELECT pn_var_bkhd_defaults_s.nextval
INTO l_bkhdDefId
FROM DUAL;
insert into pn_var_bkhd_defaults_all (
bkhd_default_id,
bkhd_detail_num,
line_default_id,
bkhd_start_date,
bkhd_end_date,
break_type,
base_rent_type,
natural_break_rate,
base_rent,
breakpoint_type,
breakpoint_level,
bkpt_head_template_id,
agreement_template_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
var_rent_id,
processed_flag
) values (
l_bkhdDefId,
l_bkhdNum,
l_lineDefId,
c_hdrec.bkhd_start_date,
c_hdrec.bkhd_end_date,
c_hdrec.break_type,
c_hdrec.base_rent_type,
c_hdrec.natural_break_rate,
c_hdrec.base_rent,
c_hdrec.breakpoint_type,
c_hdrec.breakpoint_level,
c_hdrec.bkpt_head_template_id,
c_hdrec.agreement_template_id,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c_hdrec.ORG_ID,
c_hdrec.var_rent_id,
c_hdrec.processed_flag
);
SELECT pn_var_bkdt_defaults_s.nextval
INTO l_bkdtDefId
FROM DUAL;
INSERT into pn_var_bkdt_defaults_all (
bkdt_default_id,
bkdt_detail_num,
bkhd_default_id,
bkdt_start_date,
bkdt_end_date,
period_bkpt_vol_start,
period_bkpt_vol_end,
group_bkpt_vol_start,
group_bkpt_vol_end,
bkpt_rate,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
org_id,
var_rent_id,
processed_flag
) values (
l_bkdtDefid,
l_bkdtNum,
l_bkhdDefid,
c_dtrec.BKDT_START_DATE,
c_dtrec.BKDT_END_DATE,
c_dtrec.PERIOD_BKPT_VOL_START,
c_dtrec.PERIOD_BKPT_VOL_END,
c_dtrec.GROUP_BKPT_VOL_START,
c_dtrec.GROUP_BKPT_VOL_END,
c_dtrec.BKPT_RATE,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
C_dtrec.ORG_ID,
C_dtrec.var_rent_id,
C_dtrec.processed_flag
);
select * from pn_var_constr_defaults_all
where var_rent_id = l_old_rentid;
SELECT pn_var_constr_defaults_s.nextval
INTO l_consDefId
FROM DUAL;
pn_var_constr_defaults_pkg.insert_row (
X_ROWID => l_rowid,
X_CONSTR_DEFAULT_ID => l_consDefid,
X_CONSTR_DEFAULT_NUM => l_consNum,
X_VAR_RENT_ID => l_new_rentid,
X_AGREEMENT_TEMPLATE_ID => c_crec.agreement_template_id,
X_CONSTR_TEMPLATE_ID => c_crec.constr_template_id ,
X_CONSTR_START_DATE => c_crec.constr_start_date,
X_CONSTR_END_DATE => c_crec.CONSTR_END_DATE,
X_CONSTR_CAT_CODE => c_crec.CONSTR_CAT_CODE,
X_TYPE_CODE => c_crec.type_code,
X_AMOUNT => c_crec.amount,
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),
X_ORG_ID => c_crec.ORG_ID
);
select distinct period_id, start_date, end_date
from pn_var_periods_all
where var_rent_id = l_var_rent_id
and start_date <= p_end
and end_date >= p_start
and period_id not in (select period_id
from pn_var_constraints_all
where constr_default_id = p_defid);
select *
from pn_var_constr_defaults_all
where var_rent_id = l_var_rent_id;
select NVL(max(CONSTRAINT_NUM),0)
from pn_var_constraints_all
where period_id = p_periodId;
PN_VAR_CONSTRAINTS_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_CONSTRAINT_ID => l_constrId,
x_CONSTRAINT_NUM => l_constrNum,
X_PERIOD_ID => c1_rec.period_id,
X_CONSTR_CAT_CODE => c2_rec.constr_cat_code,
X_TYPE_CODE => c2_rec.type_code,
X_AMOUNT => c2_rec.amount,
X_AGREEMENT_TEMPLATE_ID => c2_rec.agreement_template_id,
X_CONSTR_TEMPLATE_ID => c2_rec.constr_template_id,
X_CONSTR_DEFAULT_ID => c2_rec.constr_default_id,
X_COMMENTS => NULL,
X_ATTRIBUTE_CATEGORY => c2_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c2_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c2_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c2_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c2_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c2_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c2_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c2_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c2_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c2_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c2_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c2_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c2_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c2_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c2_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c2_rec.ATTRIBUTE15,
X_ORG_ID => c2_rec.org_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
X_CONSTR_START_DATE => l_start_date,
X_CONSTR_END_DATE => l_end_date);
select a.period_id,
a.start_date,
a.end_date,
b.reptg_freq_code
from pn_var_periods_all a,
pn_var_rent_dates_all b
where a.var_rent_id = l_var_rent_id
and a.var_rent_id = b.var_rent_id;
SELECT *
/*sales_type_code,
item_category_code,
line_template_id,
agreement_template_id,
line_start_date,
line_end_date,
line_default_id,
created_by,
org_id*/
FROM pn_var_line_defaults_all
WHERE var_rent_id = l_var_rent_id
--AND NVL(processed_flag,0) <> 1
AND (p_start_date BETWEEN line_start_date AND line_end_date
OR p_end_date BETWEEN line_start_date AND line_end_date
OR line_start_date BETWEEN p_start_date AND p_end_date
OR line_end_date BETWEEN p_start_date AND p_end_date);
SELECT *
/*bkhd_default_id,
line_default_id,
bkhd_start_date,
bkhd_end_date,
break_type,
base_rent_type,
natural_break_rate,
base_rent,
breakpoint_type,
breakpoint_level,
bkpt_head_template_id,
agreement_template_id,
last_update_login,
org_id*/
from pn_var_bkhd_defaults_all
where line_default_id = p_line_default_id
and NVL(processed_flag,0) <> 1;
SELECT *
/*bkdt_default_id,
bkdt_start_date,
bkdt_end_date,
period_bkpt_vol_start,
period_bkpt_vol_end,
group_bkpt_vol_start,
group_bkpt_vol_end,
bkpt_rate,
org_id,
annual_basis_amount --03-NOV-2003 */
from pn_var_bkdt_defaults_all
where bkhd_default_id = p_head_default_id
and NVL(processed_flag,0) <> 1;
select count(grp_start_date)
from pn_var_grp_dates_all
where period_id = p_period_id;
SELECT 'x' line_exists
FROM dual
WHERE exists ( select line_item_id
from pn_var_lines_all
where period_id = ip_period_id
and item_category_code = ip_item_category_code
and sales_type_code = ip_sales_type_code);
SELECT 'x' line_exists
FROM dual
WHERE exists ( select line_item_id
from pn_var_lines_all
where period_id = ip_period_id
and sales_type_code = ip_sales_type_code
and rownum <2);
SELECT 'x' line_exists
FROM dual
WHERE exists ( select line_item_id
from pn_var_lines_all
where period_id = ip_period_id
and item_category_code = ip_item_category_code
and rownum <2);
SELECT *
/*bkhd_default_id,
line_default_id,
bkhd_start_date,
bkhd_end_date,
break_type,
base_rent_type,
natural_break_rate,
base_rent,
breakpoint_type,
breakpoint_level,
bkpt_head_template_id,
agreement_template_id,
last_update_login,
org_id */
FROM pn_var_bkhd_defaults_all
WHERE line_default_id = p_line_default_id
AND break_type = 'NATURAL';
SELECT *
/*bkdt_default_id,
bkdt_start_date,
bkdt_end_date,
period_bkpt_vol_start,
period_bkpt_vol_end,
group_bkpt_vol_start,
group_bkpt_vol_end,
bkpt_rate,
org_id,
annual_basis_amount --03-NOV-2003 */
FROM pn_var_bkdt_defaults_all
WHERE bkhd_default_id = p_head_default_id;
SELECT bkhd_default_id
FROM pn_var_bkhd_defaults_all
WHERE var_rent_id = p_var_rent_id;
PN_VAR_LINES_PKG.INSERT_ROW(l_rowid,
l_line_item_id,
l_line_item_num,
c_period_rec.period_id,
c_line_rec.sales_type_code,
c_line_rec.item_category_code,
l_null,
c_line_rec.ATTRIBUTE_CATEGORY,
c_line_rec.ATTRIBUTE1,
c_line_rec.ATTRIBUTE2,
c_line_rec.ATTRIBUTE3,
c_line_rec.ATTRIBUTE4,
c_line_rec.ATTRIBUTE5,
c_line_rec.ATTRIBUTE6,
c_line_rec.ATTRIBUTE7,
c_line_rec.ATTRIBUTE8,
c_line_rec.ATTRIBUTE9,
c_line_rec.ATTRIBUTE10,
c_line_rec.ATTRIBUTE11,
c_line_rec.ATTRIBUTE12,
c_line_rec.ATTRIBUTE13,
c_line_rec.ATTRIBUTE14,
c_line_rec.ATTRIBUTE15,
c_line_rec.org_id,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c_line_rec.line_template_id,
c_line_rec.agreement_template_id,
c_line_rec.line_default_id,
l_var_rent_id);
UPDATE pn_var_line_defaults_all
SET processed_flag = 1
WHERE var_rent_id = l_var_rent_id
AND line_default_id = c_line_rec.line_default_id
AND line_start_date >= c_period_rec.start_date
AND line_end_date <= c_period_rec.end_date;
SELECT line_item_id
INTO l_line_item_id
FROM pn_var_lines_all
WHERE line_default_id = c_line_rec.line_default_id
AND period_id = c_period_rec.period_id
AND var_rent_id = l_var_rent_id
AND ROWNUM = 1;
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 => c_period_rec.period_id,
x_break_type => c_head_rec.break_type,
x_base_rent_type => c_head_rec.base_rent_type,
x_natural_break_rate => c_head_rec.natural_break_rate,
x_base_rent => c_head_rec.base_rent,
x_breakpoint_type => c_head_rec.breakpoint_type,
x_bkhd_default_id => c_head_rec.bkhd_default_id,
x_bkhd_start_date => null,
x_bkhd_end_date => null,
x_var_rent_id => l_var_rent_id,
x_attribute_category => c_head_rec.ATTRIBUTE_CATEGORY,
x_attribute1 => c_head_rec.ATTRIBUTE1,
x_attribute2 => c_head_rec.ATTRIBUTE2,
x_attribute3 => c_head_rec.ATTRIBUTE3,
x_attribute4 => c_head_rec.ATTRIBUTE4,
x_attribute5 => c_head_rec.ATTRIBUTE5,
x_attribute6 => c_head_rec.ATTRIBUTE6,
x_attribute7 => c_head_rec.ATTRIBUTE7,
x_attribute8 => c_head_rec.ATTRIBUTE8,
x_attribute9 => c_head_rec.ATTRIBUTE9,
x_attribute10 => c_head_rec.ATTRIBUTE10,
x_attribute11 => c_head_rec.ATTRIBUTE11,
x_attribute12 => c_head_rec.ATTRIBUTE12,
x_attribute13 => c_head_rec.ATTRIBUTE13,
x_attribute14 => c_head_rec.ATTRIBUTE14,
x_attribute15 => c_head_rec.ATTRIBUTE15,
x_org_id => c_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),
x_bkpt_update_flag => c_head_rec.bkpt_update_flag);
PN_VAR_BKPTS_DET_PKG.INSERT_ROW(
l_rowid,
l_bkpt_detail_id,
l_bkpt_detail_num,
l_bkpt_header_id,
l_period_start,
l_period_end,
l_period_bkpt_vol_start,
l_period_bkpt_vol_end,
l_group_bkpt_vol_start,
l_group_bkpt_vol_end,
c_det_rec.bkpt_rate,
c_det_rec.bkdt_default_id,
l_var_rent_id,
l_null,
c_det_rec.ATTRIBUTE_CATEGORY,
c_det_rec.ATTRIBUTE1,
c_det_rec.ATTRIBUTE2,
c_det_rec.ATTRIBUTE3,
c_det_rec.ATTRIBUTE4,
c_det_rec.ATTRIBUTE5,
c_det_rec.ATTRIBUTE6,
c_det_rec.ATTRIBUTE7,
c_det_rec.ATTRIBUTE8,
c_det_rec.ATTRIBUTE9,
c_det_rec.ATTRIBUTE10,
c_det_rec.ATTRIBUTE11,
c_det_rec.ATTRIBUTE12,
c_det_rec.ATTRIBUTE13,
c_det_rec.ATTRIBUTE14,
c_det_rec.ATTRIBUTE15,
c_det_rec.org_id,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c_det_rec.annual_basis_amount --03-NOV-2003
);
UPDATE pn_var_bkdt_defaults_all
SET processed_flag = 1
WHERE var_rent_id = l_var_rent_id
AND bkdt_default_id = c_det_rec.bkdt_default_id;
UPDATE pn_var_bkpts_head_all
SET bkhd_start_date = l_bkhd_start_date,
bkhd_end_date = l_bkhd_end_date
WHERE bkpt_header_id = l_bkpt_header_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 => c_period_rec.period_id,
x_break_type => c_head_rec.break_type,
x_base_rent_type => c_head_rec.base_rent_type,
x_natural_break_rate => c_head_rec.natural_break_rate,
x_base_rent => c_head_rec.base_rent,
x_breakpoint_type => c_head_rec.breakpoint_type,
x_bkhd_default_id => c_head_rec.bkhd_default_id,
x_bkhd_start_date => null,
x_bkhd_end_date => null,
x_var_rent_id => l_var_rent_id,
x_attribute_category => c_head_rec.ATTRIBUTE_CATEGORY,
x_attribute1 => c_head_rec.ATTRIBUTE1,
x_attribute2 => c_head_rec.ATTRIBUTE2,
x_attribute3 => c_head_rec.ATTRIBUTE3,
x_attribute4 => c_head_rec.ATTRIBUTE4,
x_attribute5 => c_head_rec.ATTRIBUTE5,
x_attribute6 => c_head_rec.ATTRIBUTE6,
x_attribute7 => c_head_rec.ATTRIBUTE7,
x_attribute8 => c_head_rec.ATTRIBUTE8,
x_attribute9 => c_head_rec.ATTRIBUTE9,
x_attribute10 => c_head_rec.ATTRIBUTE10,
x_attribute11 => c_head_rec.ATTRIBUTE11,
x_attribute12 => c_head_rec.ATTRIBUTE12,
x_attribute13 => c_head_rec.ATTRIBUTE13,
x_attribute14 => c_head_rec.ATTRIBUTE14,
x_attribute15 => c_head_rec.ATTRIBUTE15,
x_org_id => c_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),
x_bkpt_update_flag => c_head_rec.bkpt_update_flag);
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 => l_period_start,
X_BKPT_END_DATE => l_period_end,
X_PERIOD_BKPT_VOL_START => l_period_bkpt_vol_start,
X_PERIOD_BKPT_VOL_END => l_period_bkpt_vol_end,
X_GROUP_BKPT_VOL_START => l_group_bkpt_vol_start,
X_GROUP_BKPT_VOL_END => l_group_bkpt_vol_end,
X_BKPT_RATE => c_det_rec.bkpt_rate,
X_BKDT_DEFAULT_ID => c_det_rec.bkdt_default_id,
X_VAR_RENT_ID => l_var_rent_id,
X_COMMENTS => l_null,
X_ATTRIBUTE_CATEGORY => c_det_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_det_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_det_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_det_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_det_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_det_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_det_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_det_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_det_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_det_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_det_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_det_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_det_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_det_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_det_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_det_rec.ATTRIBUTE15,
X_ORG_ID => c_det_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 => c_det_rec.annual_basis_amount
);
UPDATE pn_var_bkdt_defaults_all
SET processed_flag = 1
WHERE var_rent_id = l_var_rent_id
AND bkdt_default_id = c_det_rec.bkdt_default_id;
UPDATE pn_var_bkpts_head_all
SET bkhd_start_date = l_bkhd_start_date,
bkhd_end_date = l_bkhd_end_date
WHERE bkpt_header_id = l_bkpt_header_id;
UPDATE pn_var_bkhd_defaults_all
SET bkpt_update_flag = 'N'
WHERE var_rent_id = x_var_rent_id;
UPDATE pn_var_bkdt_defaults_all
SET processed_flag = 1
WHERE var_rent_id = l_var_rent_id
AND bkhd_default_id IN (SELECT a.bkhd_default_id
FROM pn_var_bkhd_defaults_all a,
pn_var_bkpts_head_all b
WHERE a.var_rent_id = b.var_rent_id
AND a.var_rent_id = l_var_rent_id
AND a.bkhd_default_id = b.bkhd_default_id
AND a.break_type = b.break_type
AND a.break_type <> 'NATURAL');
SELECT COUNT(*)
INTO l_cnt
FROM pn_var_bkdt_defaults_all
WHERE bkhd_default_id = i.bkhd_default_id
AND NVL(processed_flag,0) <> 1;
UPDATE pn_var_bkhd_defaults_all
SET processed_flag = 1
WHERE var_rent_id = l_var_rent_id
AND bkhd_default_id = i.bkhd_default_id;
UPDATE pn_var_bkhd_defaults_all
SET processed_flag = 0
WHERE var_rent_id = l_var_rent_id
AND bkhd_default_id = i.bkhd_default_id;
DELETE FROM pn_var_bkpts_head_all
WHERE var_rent_id = l_var_rent_id
AND bkhd_start_date IS NULL
AND bkhd_end_date IS NULL;
| PROCEDURE DELETE_DEFAULT_LINES
|
|
| DESCRIPTION
| Delete records from the PN_VAR_LINE_DEFAULTS_ALL tables.
|
| SCOPE - PUBLIC
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| X_VAR_RENT_ID
|
| OUT:
|
| RETURNS : None
|
|
| MODIFICATION HISTORY
|
| 13-MAR-2003 Gary Olson o Created
| 16-FEB-2004 Srini Vijayareddy o Support for multiple default header
|
+===========================================================================*/
procedure delete_default_lines (X_VAR_RENT_ID IN NUMBER,
x_bkhd_default_id IN NUMBER DEFAULT NULL,
x_bkpt_header_id IN NUMBER DEFAULT NULL) IS
begin
IF x_bkhd_default_id IS NULL AND x_bkpt_header_id IS NULL THEN
DELETE FROM pn_var_bkpts_det_all
WHERE var_rent_id = X_VAR_RENT_ID;
DELETE FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID;
/*DELETE FROM pn_var_vol_hist_all
WHERE line_item_id IN (SELECT line_item_id
FROM pn_var_lines_all
WHERE var_rent_id = X_VAR_RENT_ID);
DELETE FROM pn_var_lines_all
WHERE var_rent_id = X_VAR_RENT_ID;
DELETE FROM pn_var_transactions_all
WHERE var_rent_id = X_VAR_RENT_ID;*/
UPDATE pn_var_line_defaults_all
SET processed_flag = 0
WHERE var_rent_id = X_VAR_RENT_ID;
UPDATE pn_var_bkhd_defaults_all
SET processed_flag = 0
WHERE var_rent_id = X_VAR_RENT_ID;
UPDATE pn_var_bkdt_defaults_all
SET processed_flag = 0
WHERE var_rent_id = X_VAR_RENT_ID;
/*DELETE FROM pn_var_transactions_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_detail_id IN (SELECT det.bkpt_detail_id
FROM pn_var_bkpts_det_all det,
pn_var_bkpts_head_all head
WHERE det.var_rent_id = X_VAR_RENT_ID
AND det.var_rent_id = head.var_rent_id
AND det.bkpt_header_id = head.bkpt_header_id
AND head.bkhd_default_id = x_bkhd_default_id);*/
DELETE FROM pn_var_vol_hist_all
--WHERE vol_hist_status_code <> 'APPROVED'
WHERE actual_exp_code = 'N'
AND line_item_id IN (SELECT line_item_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkhd_default_id = x_bkhd_default_id)
AND line_item_id NOT IN (SELECT line_item_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkhd_default_id <> x_bkhd_default_id); */
DELETE FROM pn_var_lines_all
WHERE var_rent_id = X_VAR_RENT_ID
AND line_item_id IN (SELECT line_item_id
FROM pn_var_bkpts_head_all
WHERE bkhd_default_id = x_bkhd_default_id)
AND line_item_id NOT IN (SELECT line_item_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkhd_default_id <> x_bkhd_default_id); */
DELETE FROM pn_var_bkpts_det_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_header_id IN (SELECT bkpt_header_id
FROM pn_var_bkpts_head_all
WHERE bkhd_default_id = x_bkhd_default_id);
DELETE FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkhd_default_id = x_bkhd_default_id;
UPDATE pn_var_line_defaults_all
SET processed_flag = 0
WHERE var_rent_id = X_VAR_RENT_ID;
UPDATE pn_var_bkhd_defaults_all
SET processed_flag = 0
WHERE var_rent_id = X_VAR_RENT_ID
AND bkhd_default_id = x_bkhd_default_id;
UPDATE pn_var_bkdt_defaults_all
SET processed_flag = 0
WHERE var_rent_id = X_VAR_RENT_ID
AND bkhd_default_id = x_bkhd_default_id;
/*DELETE FROM pn_var_transactions_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_detail_id IN (SELECT bkpt_detail_id
FROM pn_var_bkpts_det_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_header_id = x_bkpt_header_id);*/
/* DELETE FROM pn_var_vol_hist_all
--WHERE vol_hist_status_code <> 'APPROVED'
WHERE actual_exp_code = 'N'
AND line_item_id IN (SELECT line_item_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_header_id = x_bkpt_header_id)
AND line_item_id NOT IN (SELECT line_item_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_header_id <> x_bkpt_header_id);
DELETE FROM pn_var_lines_all
WHERE var_rent_id = X_VAR_RENT_ID
AND line_item_id IN (SELECT line_item_id
FROM pn_var_bkpts_head_all
WHERE bkpt_header_id = x_bkpt_header_id)
AND line_item_id NOT IN (SELECT line_item_id
FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_header_id <> x_bkpt_header_id);*/
DELETE FROM pn_var_bkpts_det_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_header_id = x_bkpt_header_id;
DELETE FROM pn_var_bkpts_head_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_header_id = x_bkpt_header_id;
end delete_default_lines;
| Delete records from the PN_VAR_LINE_DEFAULTS_ALL tables.
|
| SCOPE - PUBLIC
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| X_LINE_DEFAULT_ID
|
| OUT:
|
| RETURNS : None
|
|
| MODIFICATION HISTORY
|
| 13-MAR-2003 Gary Olson o Created
|
+===========================================================================*/
procedure reset_default_lines (
X_BKHD_DEFAULT_ID in NUMBER
) IS
begin
delete from pn_var_bkdt_defaults_all
where bkhd_default_id = X_BKHD_DEFAULT_ID;
| PROCEDURE DELETE_TRANSACTIONS
|
|
| DESCRIPTION
| Delete records from the PN_VAR_TRANSACTIONS_ALL tables.
|
| SCOPE - PUBLIC
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| X_VAR_RENT_ID
|
| OUT:
|
| RETURNS : None
|
|
| MODIFICATION HISTORY
|
| 13-MAR-2003 Gary Olson o Created
| 16-FEB-2004 Srini Vijayareddy o Support for multiple default header
|
+===========================================================================*/
procedure delete_transactions ( X_VAR_RENT_ID in NUMBER,
x_bkhd_default_id IN NUMBER DEFAULT NULL,
x_bkpt_header_id IN NUMBER DEFAULT NULL) IS
begin
/*IF x_bkhd_default_id IS NULL AND x_bkpt_header_id IS NULL THEN
delete from pn_var_transactions_all
where var_rent_id = X_VAR_RENT_ID;
DELETE FROM pn_var_transactions_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_detail_id IN (SELECT det.bkpt_detail_id
FROM pn_var_bkpts_det_all det,
pn_var_bkpts_head_all head
WHERE det.var_rent_id = X_VAR_RENT_ID
AND det.var_rent_id = head.var_rent_id
AND det.bkpt_header_id = head.bkpt_header_id
AND head.bkhd_default_id = x_bkhd_default_id);
DELETE FROM pn_var_transactions_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_detail_id IN (SELECT bkpt_detail_id
FROM pn_var_bkpts_det_all
WHERE var_rent_id = X_VAR_RENT_ID
AND bkpt_header_id = x_bkpt_header_id);
end delete_transactions;
select commencement_date,
TO_DATE('31-12-'||TO_CHAR(commencement_date,'YYYY'),'DD-MM-YYYY') year,
TO_DATE(TO_CHAR(commencement_date,'DD-MM-')||
TO_CHAR(TO_NUMBER(TO_CHAR(commencement_date,'YYYY'))+1), 'DD-MM-YYYY')-1 end_date
from pn_var_rents_all
where var_rent_id = p_var_rent_id;
select grp_date_id,bkpt_start_date, bkpt_end_date,
no_of_group_days, prorated_group_sales
from pn_var_transactions_all
where var_rent_id = p_var_rent_id
and prorated_group_sales is not null
and grp_date_id in (select grp_date_id
from pn_var_grp_dates_all
where grp_start_date <= p_date );*/
select grp_start_date, grp_end_date
from pn_var_grp_dates_all
where grp_date_id = p_grp_date_id;
SELECT sum(item.ACTUAL_AMOUNT)
INTO l_base_rent
FROM pn_payment_items_ALL item,
pn_payment_terms_ALL term,
pn_var_rents_ALL var,
pn_payment_schedules 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 sched.SCHEDULE_DATE between term.start_date and term.end_date
AND term.PAYMENT_PURPOSE_CODE = 'RENT'
AND term.PAYMENT_TERM_TYPE_CODE = 'BASER'
AND term.start_date >= var.commencement_date
AND term.end_date <= var.termination_date
AND item.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
AND term.currency_code = var.currency_code --BUG#2452909
;
SELECT sum(item.ACTUAL_AMOUNT)
INTO l_base_rent
FROM pn_payment_items_ALL item,
pn_payment_terms_ALL term,
pn_var_rents_ALL var,
pn_payment_schedules 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 sched.SCHEDULE_DATE between term.start_date and term.end_date
AND term.PAYMENT_PURPOSE_CODE = 'RENT'
AND term.PAYMENT_TERM_TYPE_CODE = 'BASER'
AND term.start_date >= var.commencement_date
AND term.end_date <= var.termination_date
AND item.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
AND term.currency_code = var.currency_code
;
select TO_DATE(TO_CHAR(termination_date,'DD-MM-')||
TO_CHAR(TO_NUMBER(TO_CHAR(termination_date,'YYYY'))-1), 'DD-MM-YYYY')+1 start_date,
TO_DATE('1-1-'||TO_CHAR(termination_date,'YYYY'),'DD-MM-YYYY') year,
termination_date
from pn_var_rents_all
where var_rent_id = p_var_rent_id;
select grp_date_id,bkpt_start_date, bkpt_end_date,
no_of_group_days, prorated_group_sales
from pn_var_transactions_all
where var_rent_id = p_var_rent_id
and prorated_group_sales is not null
and grp_date_id in (select grp_date_id
from pn_var_grp_dates_all
where grp_end_date >= p_date)
order by bkpt_start_date desc;*/
select grp_start_date, grp_end_date
from pn_var_grp_dates_all
where grp_date_id = p_grp_date_id;
SELECT 1
INTO l_line_found
FROM pn_var_line_defaults_all
WHERE var_rent_id = p_var_rent_id
AND rownum < 2;
SELECT 1
INTO l_constr_found
FROM pn_var_constr_defaults_all
WHERE var_rent_id = p_var_rent_id;
select *
from pn_var_bkpts_head_template_all
where agreement_template_id = X_AGREEMENT_TEMPLATE_ID
and line_template_id = X_LINE_TEMPLATE_ID;
select *
from pn_var_bkpts_det_template_all
where bkpt_head_template_id = p_bkpt_head_template_id;*/
PN_VAR_BKPTS_HEAD_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_BKPT_HEADER_ID => l_bkpt_header_id,
X_LINE_ITEM_ID => X_LINE_ID,
X_PERIOD_ID => X_PERIOD_ID,
X_BREAK_TYPE => c_hd.break_type,
X_BASE_RENT_TYPE => c_hd.base_rent_type,
X_NATURAL_BREAK_RATE => c_hd.natural_break_rate,
X_BASE_RENT => c_hd.base_rent,
X_BREAKPOINT_TYPE => c_hd.breakpoint_type,
X_BKHD_DEFAULT_ID => l_nullid,
X_BKHD_START_DATE => NULL,
X_BKHD_END_DATE => NULL,
X_VAR_RENT_ID => X_VAR_RENT_ID,
X_ATTRIBUTE_CATEGORY => l_null,
X_ATTRIBUTE1 => l_null,
X_ATTRIBUTE2 => l_null,
X_ATTRIBUTE3 => l_null,
X_ATTRIBUTE4 => l_null,
X_ATTRIBUTE5 => l_null,
X_ATTRIBUTE6 => l_null,
X_ATTRIBUTE7 => l_null,
X_ATTRIBUTE8 => l_null,
X_ATTRIBUTE9 => l_null,
X_ATTRIBUTE10 => l_null,
X_ATTRIBUTE11 => l_null,
X_ATTRIBUTE12 => l_null,
X_ATTRIBUTE13 => l_null,
X_ATTRIBUTE14 => l_null,
X_ATTRIBUTE15 => l_null,
X_ORG_ID => c_hd.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)
);
PN_VAR_BKPTS_DET_PKG.INSERT_ROW(
l_rowid,
l_nullid,
l_itemNum,
l_bkpt_header_id,
l_nulldate,
l_nulldate,
c_det.period_bkpt_vol_start,
c_det.period_bkpt_vol_end,
c_det.group_bkpt_vol_start,
c_det.group_bkpt_vol_end,
c_det.bkpt_rate,
l_nullid,
X_VAR_RENT_ID,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
c_hd.org_id,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
NULL --03-NOV-2003
);
PN_VAR_BKHD_DEFAULTS_PKG.INSERT_ROW (
l_rowid,
l_bkpt_header_id,
l_itemNum,
X_LINE_ID,
l_nullid,
X_AGREEMENT_TEMPLATE_ID,
l_nulldate,
l_nulldate,
c_hd.break_type,
c_hd.base_rent_type,
c_hd.natural_break_rate,
c_hd.base_rent,
c_hd.breakpoint_type,
l_null,
l_null,
X_VAR_RENT_ID,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c_hd.org_id,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null
);
PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW(
l_rowid,
l_itemId,
l_itemNum,
l_bkpt_header_id,
l_nulldate,
l_nulldate,
c_det.period_bkpt_vol_start,
c_det.period_bkpt_vol_end,
c_det.group_bkpt_vol_start,
c_det.group_bkpt_vol_end,
c_det.bkpt_rate,
l_null,
X_VAR_RENT_ID,
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
sysdate,
NVL(fnd_profile.value('USER_ID'),0),
NVL(fnd_profile.value('USER_ID'),0),
c_hd.org_id,
NULL, --03-NOV-2003
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null,
l_null);
UPDATE pn_var_bkhd_defaults_all
SET bkhd_start_date = (select min(bkdt_start_date)
from pn_var_bkdt_defaults_all
where var_rent_id = X_VAR_RENT_ID
and bkhd_default_id = X_BKHD_DEFAULT_ID),
bkhd_end_date = (select max(bkdt_end_date)
from pn_var_bkdt_defaults_all
where var_rent_id = X_VAR_RENT_ID
and bkhd_default_id = X_BKHD_DEFAULT_ID)
WHERE var_rent_id = X_VAR_RENT_ID
AND bkhd_default_id = X_BKHD_DEFAULT_ID;
UPDATE pn_var_line_defaults_all
SET line_start_date = (select min(bkhd_start_date)
from pn_var_bkhd_defaults_all
where var_rent_id = X_VAR_RENT_ID
and line_default_id = X_LINE_DEFAULT_ID),
line_end_date = (select max(bkhd_end_date)
from pn_var_bkhd_defaults_all
where var_rent_id = X_VAR_RENT_ID
and line_default_id = X_LINE_DEFAULT_ID)
WHERE var_rent_id = X_VAR_RENT_ID
AND line_default_id = X_LINE_DEFAULT_ID;
| pn_var_defaults_pkg.delete_default_lines
|
+=============================================================================*/
PROCEDURE CREATE_SETUP_DATA (X_VAR_RENT_ID IN NUMBER) IS
l_var_rent_id NUMBER := NULL;
SELECT a.period_id,
a.start_date,
a.end_date
FROM pn_var_periods_all a
WHERE a.var_rent_id = l_var_rent_id
AND a.status IS NULL;
SELECT *
FROM pn_var_line_defaults_all
WHERE var_rent_id = l_var_rent_id;
SELECT *
FROM pn_var_bkhd_defaults_all
WHERE line_default_id = p_line_default_id
AND bkhd_start_date <= p_end_date
AND bkhd_end_date >= p_start_date;
SELECT *
FROM pn_var_bkdt_defaults_all
WHERE bkhd_default_id = p_head_default_id
AND bkdt_start_date <= p_end_date
AND bkdt_end_date >= p_start_date;
SELECT line_item_id
FROM pn_var_lines_all
WHERE line_default_id = p_line_def_id
AND period_id = p_period_id;
SELECT bkhd_default_id
FROM pn_var_bkhd_defaults_all
WHERE line_default_id = p_line_def_id;
SELECT 'x' bkdt_exists
FROM DUAL
WHERE EXISTS (SELECT null
from pn_var_bkdt_defaults_all
where bkhd_default_id = p_bkhd_def_id);
pn_var_defaults_pkg.delete_default_lines (l_var_rent_id);
/* Inserting line defaults data into PN_VAR_LINES_ALL */
l_rowid := NULL;
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 => per_rec.period_id,
X_SALES_TYPE_CODE => line_def_rec.sales_type_code,
X_ITEM_CATEGORY_CODE => line_def_rec.item_category_code,
X_COMMENTS => null,
X_ATTRIBUTE_CATEGORY => line_def_rec.attribute_category,
X_ATTRIBUTE1 => line_def_rec.attribute1,
X_ATTRIBUTE2 => line_def_rec.attribute2,
X_ATTRIBUTE3 => line_def_rec.attribute3,
X_ATTRIBUTE4 => line_def_rec.attribute4,
X_ATTRIBUTE5 => line_def_rec.attribute5,
X_ATTRIBUTE6 => line_def_rec.attribute6,
X_ATTRIBUTE7 => line_def_rec.attribute7,
X_ATTRIBUTE8 => line_def_rec.attribute8,
X_ATTRIBUTE9 => line_def_rec.attribute9,
X_ATTRIBUTE10 => line_def_rec.attribute10,
X_ATTRIBUTE11 => line_def_rec.attribute11,
X_ATTRIBUTE12 => line_def_rec.attribute12,
X_ATTRIBUTE13 => line_def_rec.attribute13,
X_ATTRIBUTE14 => line_def_rec.attribute14,
X_ATTRIBUTE15 => line_def_rec.attribute15,
X_ORG_ID => line_def_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_def_rec.line_template_id,
X_AGREEMENT_TEMPLATE_ID => line_def_rec.agreement_template_id,
X_LINE_DEFAULT_ID => line_def_rec.line_default_id,
X_VAR_RENT_ID => l_var_rent_id);
/* UPDATE PN_VAR_LINES_ALL, using data from PN_VAR_LINE_DEFAULTS_ALL */
/*dbms_output.put_line('at update');
UPDATE PN_VAR_LINES_ALL SET
PERIOD_ID = per_rec.period_id,
SALES_TYPE_CODE = line_def_rec.sales_type_code,
ITEM_CATEGORY_CODE = line_def_rec.item_category_code,
ATTRIBUTE_CATEGORY = line_def_rec.attribute_category,
ATTRIBUTE1 = line_def_rec.attribute1,
ATTRIBUTE2 = line_def_rec.attribute2,
ATTRIBUTE3 = line_def_rec.attribute3,
ATTRIBUTE4 = line_def_rec.attribute4,
ATTRIBUTE5 = line_def_rec.attribute5,
ATTRIBUTE6 = line_def_rec.attribute6,
ATTRIBUTE7 = line_def_rec.attribute7,
ATTRIBUTE8 = line_def_rec.attribute8,
ATTRIBUTE9 = line_def_rec.attribute9,
ATTRIBUTE10 = line_def_rec.attribute10,
ATTRIBUTE11 = line_def_rec.attribute11,
ATTRIBUTE12 = line_def_rec.attribute12,
ATTRIBUTE13 = line_def_rec.attribute13,
ATTRIBUTE14 = line_def_rec.attribute14,
ATTRIBUTE15 = line_def_rec.attribute15,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = NVL(fnd_profile.value('USER_ID'),0),
LAST_UPDATE_LOGIN = NVL(fnd_profile.value('USER_ID'),0),
LINE_TEMPLATE_ID = line_def_rec.line_template_id ,
AGREEMENT_TEMPLATE_ID = line_def_rec.agreement_template_id,
LINE_DEFAULT_ID = line_def_rec.line_default_id,
VAR_RENT_ID = l_var_rent_id
WHERE 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 => per_rec.period_id,
X_BREAK_TYPE => bkhd_def_rec.break_type,
X_BASE_RENT_TYPE => bkhd_def_rec.base_rent_type,
X_NATURAL_BREAK_RATE => bkhd_def_rec.natural_break_rate,
X_BASE_RENT => bkhd_def_rec.base_rent,
X_BREAKPOINT_TYPE => bkhd_def_rec.breakpoint_type,
X_BKHD_DEFAULT_ID => bkhd_def_rec.bkhd_default_id,
X_BKHD_START_DATE => l_bkhd_start_date,
X_BKHD_END_DATE => l_bkhd_end_Date,
X_VAR_RENT_ID => l_var_rent_id,
X_ATTRIBUTE_CATEGORY => bkhd_def_rec.attribute_category,
X_ATTRIBUTE1 => bkhd_def_rec.attribute1,
X_ATTRIBUTE2 => bkhd_def_rec.attribute2,
X_ATTRIBUTE3 => bkhd_def_rec.attribute3,
X_ATTRIBUTE4 => bkhd_def_rec.attribute4,
X_ATTRIBUTE5 => bkhd_def_rec.attribute5,
X_ATTRIBUTE6 => bkhd_def_rec.attribute6,
X_ATTRIBUTE7 => bkhd_def_rec.attribute7,
X_ATTRIBUTE8 => bkhd_def_rec.attribute8,
X_ATTRIBUTE9 => bkhd_def_rec.attribute9,
X_ATTRIBUTE10 => bkhd_def_rec.attribute10,
X_ATTRIBUTE11 => bkhd_def_rec.attribute11,
X_ATTRIBUTE12 => bkhd_def_rec.attribute12,
X_ATTRIBUTE13 => bkhd_def_rec.attribute13,
X_ATTRIBUTE14 => bkhd_def_rec.attribute14,
X_ATTRIBUTE15 => bkhd_def_rec.attribute15,
X_ORG_ID => bkhd_def_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),
X_BKPT_UPDATE_FLAG => bkhd_def_rec.bkpt_update_flag);
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 => l_bkdt_start_date,
X_BKPT_END_DATE => l_bkdt_end_date,
X_PERIOD_BKPT_VOL_START => bkdt_def_rec.period_bkpt_vol_start,
X_PERIOD_BKPT_VOL_END => bkdt_def_rec.period_bkpt_vol_end,
X_GROUP_BKPT_VOL_START => bkdt_def_rec.group_bkpt_vol_start,
X_GROUP_BKPT_VOL_END => bkdt_def_rec.group_bkpt_vol_end,
X_BKPT_RATE => bkdt_def_rec.bkpt_rate,
X_BKDT_DEFAULT_ID => bkdt_def_rec.bkdt_default_id,
X_VAR_RENT_ID => l_var_rent_id,
X_COMMENTS => NULL,
X_ATTRIBUTE_CATEGORY => bkdt_def_rec.attribute_category,
X_ATTRIBUTE1 => bkdt_def_rec.attribute1,
X_ATTRIBUTE2 => bkdt_def_rec.attribute2,
X_ATTRIBUTE3 => bkdt_def_rec.attribute3,
X_ATTRIBUTE4 => bkdt_def_rec.attribute4,
X_ATTRIBUTE5 => bkdt_def_rec.attribute5,
X_ATTRIBUTE6 => bkdt_def_rec.attribute6,
X_ATTRIBUTE7 => bkdt_def_rec.attribute7,
X_ATTRIBUTE8 => bkdt_def_rec.attribute8,
X_ATTRIBUTE9 => bkdt_def_rec.attribute9,
X_ATTRIBUTE10 => bkdt_def_rec.attribute10,
X_ATTRIBUTE11 => bkdt_def_rec.attribute11,
X_ATTRIBUTE12 => bkdt_def_rec.attribute12,
X_ATTRIBUTE13 => bkdt_def_rec.attribute13,
X_ATTRIBUTE14 => bkdt_def_rec.attribute14,
X_ATTRIBUTE15 => bkdt_def_rec.attribute15,
X_ORG_ID => bkdt_def_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 => bkdt_def_rec.annual_basis_amount);
UPDATE pn_var_bkhd_defaults_all
SET bkpt_update_flag = 'N'
WHERE var_rent_id = l_var_rent_id;
UPDATE pn_var_lines_all
SET bkpt_update_flag = 'Y'
WHERE var_rent_id = l_var_rent_id;