[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT line.rowid,
line.batch_id,
line.var_rent_id,
line.line_item_id,
line.rep_str_DATE,
line.rep_end_DATE,
line.amount,
line.status,
line.deduction_type_code,
batch.Volume_type,
line.report_type_code,
line.vol_hist_status_code,
line.reporting_date,
line.certified_by,
line.vol_deduct_id
FROM pn_vol_hist_batch_itf batch,
pn_vol_hist_lines_itf line
WHERE batch.batch_id = line.batch_id
AND batch.batch_id = p_batch_id
AND batch.status <>'I'
AND line.status <> 'I'
AND line.amount is not null;
SELECT rents.var_rent_id,
rents.rent_num,
lease.name,
rents.org_id
FROM pn_var_rents_all rents,
pn_leases_all lease
WHERE rents.var_rent_id = p_rent_id
AND rents.lease_id = lease.lease_id;
SELECT lines.line_item_id ,
lines.period_id ,
l_channel.meaning ,
l_category.meaning
FROM pn_var_lines_all lines,
fnd_lookups l_channel,
fnd_lookups l_category
WHERE lines.line_item_id = p_line_item_id
AND l_channel.lookup_code(+) = lines.SALES_TYPE_CODE
AND l_channel.lookup_type (+) ='PN_SALES_CHANNEL'
AND l_category.lookup_code(+) = lines.ITEM_CATEGORY_CODE
AND l_category.lookup_type(+) ='PN_ITEM_CATEGORY';
SELECT GRP_DATE_ID,
GROUP_DATE,
REPTG_DUE_DATE,
INVOICE_DATE
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_end_DATE
AND grp_end_DATE >= p_start_DATE;
SELECT 'Y'
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM pn_vol_hist_lines_itf
WHERE status in ('E','P')
AND batch_id = p_batch_id)
AND EXISTS (SELECT NULL
FROM pn_vol_hist_batch_itf
WHERE status = 'E'
AND batch_id = p_batch_id);
SELECT actual_amount,
forecasted_amount ,
actual_exp_code,
forecasted_exp_code
FROM
pn_var_vol_hist_all
WHERE
LINE_ITEM_ID = p_line_item_id
AND PERIOD_ID = p_period_id
AND GRP_DATE_ID = p_group_DATE_id
AND START_DATE = p_start_DATE
AND END_DATE =p_end_DATE;
SELECT LINE_ITEM_ID,
START_DATE,
END_DATE,
ACTUAL_AMOUNT,
VOL_HIST_STATUS_CODE,
FORECASTED_AMOUNT,
REPORT_TYPE_CODE,
REPORTING_DATE
FROM pn_var_vol_hist_all
WHERE vol_hist_id = p_vol_hist_id;
SELECT LINE_ITEM_ID,
START_DATE,
END_DATE,
DEDUCTION_TYPE_CODE,
DEDUCTION_AMOUNT
FROM PN_VAR_DEDUCTIONS_ALL
WHERE deduction_id = p_deduction_id;
SELECT distinct(line.line_item_id)
FROM pn_vol_hist_batch_itf batch,
pn_vol_hist_lines_itf line
WHERE batch.batch_id = line.batch_id
AND batch.batch_id = ip_batch_id;
UPDATE pn_vol_hist_batch_itf
SET status = 'I'
WHERE batch_id = p_batch_id;
DELETE FROM pn_vol_hist_lines_itf line
WHERE line.batch_id = p_batch_id
AND line.status <>'I'
AND line.amount is null;
pnp_debug_pkg.log('before insert in the table pn_var_vol_hist');
SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
FROM pn_var_vol_hist_all
WHERE line_item_id = v_lines.line_item_id;
INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
,VOL_HIST_NUM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,DUE_DATE
,INVOICING_DATE
,ACTUAL_AMOUNT
,VOL_HIST_STATUS_CODE
,CERTIFIED_BY
,ACTUAL_EXP_CODE
,FORECASTED_AMOUNT
,FORECASTED_EXP_CODE
,VARIANCE_EXP_CODE
,ORG_ID
,REPORT_TYPE_CODE
,REPORTING_DATE)
VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
,v_vol_hist_num
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,NVL(fnd_profile.value('USER_ID'), 0)
,v_lines.line_item_id
,v_period_id
,v_lines.rep_str_DATE
,v_lines.rep_end_DATE
,v_group_DATE_id
,v_group_DATE
,v_reptg_due_DATE
,v_invoice_DATE
,v_act_amount
,v_lines.vol_hist_status_code
,v_lines.certified_by
,'N'
,v_frc_amount
,'N'
,'N'
,l_org_id
,v_lines.report_type_code
,v_lines.reporting_date);
SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
FROM pn_var_vol_hist_all
WHERE line_item_id = v_lines.line_item_id;
INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
,VOL_HIST_NUM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,DUE_DATE
,INVOICING_DATE
,ACTUAL_AMOUNT
,VOL_HIST_STATUS_CODE
,CERTIFIED_BY
,ACTUAL_EXP_CODE
,FORECASTED_AMOUNT
,FORECASTED_EXP_CODE
,VARIANCE_EXP_CODE
,org_id
,REPORT_TYPE_CODE
,REPORTING_DATE)
VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
,v_vol_hist_num
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,NVL(fnd_profile.value('USER_ID'), 0)
,v_lines.line_item_id
,v_period_id
,v_lines.rep_str_DATE
,v_lines.rep_end_DATE
,v_group_DATE_id
,v_group_DATE
,v_reptg_due_DATE
,v_invoice_DATE
,NVL(v_act_amount,0)
,v_lines.vol_hist_status_code
,v_lines.certified_by
,'N'
,v_frc_amount
,'N'
,'N'
,l_org_id
,v_lines.report_type_code
,v_lines.reporting_date);
UPDATE PN_VAR_VOL_HIST_ALL
SET ACTUAL_AMOUNT = v_act_amount
,LAST_UPDATE_DATE =sysDATE
,LAST_UPDATED_BY = NVL(fnd_profile.value('USER_ID'), 0)
,LAST_UPDATE_LOGIN =NVL(fnd_profile.value('USER_ID'), 0)
WHERE LINE_ITEM_ID = v_lines.line_item_id
AND PERIOD_ID = v_period_id
AND GRP_DATE_ID = v_group_DATE_id
AND START_DATE = v_lines.rep_str_DATE
AND END_DATE =v_lines.rep_end_DATE
AND actual_exp_code ='N';
SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
FROM pn_var_vol_hist_all
WHERE line_item_id = v_lines.line_item_id;
INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
,VOL_HIST_NUM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,DUE_DATE
,INVOICING_DATE
,ACTUAL_AMOUNT
,VOL_HIST_STATUS_CODE
,CERTIFIED_BY
,ACTUAL_EXP_CODE
,FORECASTED_AMOUNT
,FORECASTED_EXP_CODE
,VARIANCE_EXP_CODE
,org_id
,REPORT_TYPE_CODE
,REPORTING_DATE)
VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
,v_vol_hist_num
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,NVL(fnd_profile.value('USER_ID'), 0)
,v_lines.line_item_id
,v_period_id
,v_lines.rep_str_DATE
,v_lines.rep_end_DATE
,v_group_DATE_id
,v_group_DATE
,v_reptg_due_DATE
,v_invoice_DATE
,NVL(v_act_amount,0)
,v_lines.vol_hist_status_code
,v_lines.certified_by
,'N'
,v_frc_amount
,'N'
,'N'
,l_org_id
,v_lines.report_type_code
,v_lines.reporting_date);
SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
FROM pn_var_vol_hist_all
WHERE line_item_id = v_lines.line_item_id;
INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
,VOL_HIST_NUM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,DUE_DATE
,INVOICING_DATE
,ACTUAL_AMOUNT
,VOL_HIST_STATUS_CODE
,CERTIFIED_BY
,ACTUAL_EXP_CODE
,FORECASTED_AMOUNT
,FORECASTED_EXP_CODE
,VARIANCE_EXP_CODE
,org_id
,REPORT_TYPE_CODE
,REPORTING_DATE)
VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
,v_vol_hist_num
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,NVL(fnd_profile.value('USER_ID'), 0)
,v_lines.line_item_id
,v_period_id
,v_lines.rep_str_DATE
,v_lines.rep_end_DATE
,v_group_DATE_id
,v_group_DATE
,v_reptg_due_DATE
,v_invoice_DATE
,v_act_amount
,v_lines.vol_hist_status_code
,v_lines.certified_by
,'N'
,NVL(v_frc_amount,0)
,'N'
,'N'
,l_org_id
,v_lines.report_type_code
,v_lines.reporting_date);
UPDATE PN_VAR_VOL_HIST_ALl
SET FORECASTED_AMOUNT = v_frc_amount
,LAST_UPDATE_DATE =sysDATE
,LAST_UPDATED_BY = NVL(fnd_profile.value('USER_ID'), 0)
,LAST_UPDATE_LOGIN =NVL(fnd_profile.value('USER_ID'), 0)
WHERE LINE_ITEM_ID = v_lines.line_item_id
AND PERIOD_ID = v_period_id
AND GRP_DATE_ID = v_group_DATE_id
AND START_DATE = v_lines.rep_str_DATE
AND END_DATE =v_lines.rep_end_DATE
AND forecasted_exp_code = 'N';
SELECT NVL(MAX(vol_hist_num), 0)+1 INTO v_vol_hist_num
FROM pn_var_vol_hist_all
WHERE line_item_id = v_lines.line_item_id;
INSERT INTO pn_var_vol_hist_all (VOL_HIST_ID
,VOL_HIST_NUM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,DUE_DATE
,INVOICING_DATE
,ACTUAL_AMOUNT
,VOL_HIST_STATUS_CODE
,CERTIFIED_BY
,ACTUAL_EXP_CODE
,FORECASTED_AMOUNT
,FORECASTED_EXP_CODE
,VARIANCE_EXP_CODE
,org_id
,REPORT_TYPE_CODE
,REPORTING_DATE)
VALUES (PN_VAR_VOL_HIST_S.NEXTVAL
,v_vol_hist_num
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,NVL(fnd_profile.value('USER_ID'), 0)
,v_lines.line_item_id
,v_period_id
,v_lines.rep_str_DATE
,v_lines.rep_end_DATE
,v_group_DATE_id
,v_group_DATE
,v_reptg_due_DATE
,v_invoice_DATE
,v_act_amount
,v_lines.vol_hist_status_code
,v_lines.certified_by
,'N'
,NVL(v_frc_amount,0)
,'N'
,'N'
,l_org_id
,v_lines.report_type_code
,v_lines.reporting_date);
INSERT INTO PN_VAR_VOL_ARCH_ALL(
VOL_ARCH_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,VOL_HIST_ID
,VOL_HIST_NUM
,HIST_LAST_UPDATE_DATE
,HIST_LAST_UPDATED_BY
,HIST_CREATION_DATE
,HIST_CREATED_BY
,HIST_LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,REPORTING_DATE
,DUE_DATE
,INVOICING_DATE
,ACTUAL_GL_ACCOUNT_ID
,ACTUAL_AMOUNT
,VOL_HIST_STATUS_CODE
,REPORT_TYPE_CODE
,CERTIFIED_BY
,ACTUAL_EXP_CODE
,FOR_GL_ACCOUNT_ID
,FORECASTED_AMOUNT
,FORECASTED_EXP_CODE
,VARIANCE_EXP_CODE
,COMMENTS
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,HIST_ORG_ID
,DAILY_ACTUAL_AMOUNT
)
SELECT
PN_VAR_VOL_ARCH_S.nextval
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,NVL(fnd_profile.value('USER_ID'), 0)
,PN_SOI_VOL_IMPORT.g_org_id
,VOL_HIST_ID
,VOL_HIST_NUM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,REPORTING_DATE
,DUE_DATE
,INVOICING_DATE
,ACTUAL_GL_ACCOUNT_ID
,ACTUAL_AMOUNT
,VOL_HIST_STATUS_CODE
,REPORT_TYPE_CODE
,CERTIFIED_BY
,ACTUAL_EXP_CODE
,FOR_GL_ACCOUNT_ID
,FORECASTED_AMOUNT
,FORECASTED_EXP_CODE
,VARIANCE_EXP_CODE
,COMMENTS
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORG_ID
,DAILY_ACTUAL_AMOUNT
FROM PN_VAR_VOL_HIST_ALL
WHERE VOL_HIST_ID = v_lines.vol_deduct_id;
UPDATE PN_VAR_VOL_HIST_ALL
SET 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_ITEM_ID = v_lines.line_item_id
,START_DATE = v_lines.rep_str_DATE
,END_DATE = v_lines.rep_end_DATE
,VOL_HIST_STATUS_CODE = v_lines.vol_hist_status_code
,ORG_ID = l_org_id
,REPORT_TYPE_CODE = v_lines.report_type_code
,REPORTING_DATE = v_lines.reporting_date
WHERE vol_hist_id = v_lines.vol_deduct_id;
UPDATE PN_VAR_VOL_HIST_ALL
SET ACTUAL_AMOUNT = NVL(v_act_amount,0)
WHERE vol_hist_id = v_lines.vol_deduct_id;
UPDATE PN_VAR_VOL_HIST_ALL
SET FORECASTED_AMOUNT = NVL(v_frc_amount,0)
WHERE vol_hist_id = v_lines.vol_deduct_id;
SELECT NVL(MAX(deduction_num), 0)+1 INTO v_ded_num
FROM pn_var_deductions_all
WHERE line_item_id = v_lines.line_item_id;
INSERT INTO pn_var_deductions_all (DEDUCTION_ID
,DEDUCTION_NUM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,INVOICING_DATE
,DEDUCTION_AMOUNT
,EXPORTED_CODE
,DEDUCTION_TYPE_CODE
,org_id)
VALUES (PN_VAR_DEDUCTIONS_S.NEXTVAL
,v_ded_num
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,NVL(fnd_profile.value('USER_ID'), 0)
,v_lines.line_item_id
,v_period_id
,v_lines.rep_str_DATE
,v_lines.rep_end_DATE
,v_group_DATE_id
,v_group_DATE
,v_invoice_DATE
,NVL(v_ded_amount,0)
,'N'
,v_lines.deduction_type_code
,l_org_id);
INSERT INTO PN_VAR_DEDUCT_ARCH_ALL(
DEDUCT_ARCH_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,DEDUCTION_ID
,DEDUCTION_NUM
,HIST_LAST_UPDATE_DATE
,HIST_LAST_UPDATED_BY
,HIST_CREATION_DATE
,HIST_CREATED_BY
,HIST_LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,INVOICING_DATE
,GL_ACCOUNT_ID
,DEDUCTION_TYPE_CODE
,DEDUCTION_AMOUNT
,EXPORTED_CODE
,COMMENTS
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,HIST_ORG_ID
)
SELECT
PN_VAR_DEDUCT_ARCH_S.nextval
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,sysDATE
,NVL(fnd_profile.value('USER_ID'), 0)
,NVL(fnd_profile.value('USER_ID'), 0)
,PN_SOI_VOL_IMPORT.g_org_id
,DEDUCTION_ID
,DEDUCTION_NUM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,LINE_ITEM_ID
,PERIOD_ID
,START_DATE
,END_DATE
,GRP_DATE_ID
,GROUP_DATE
,INVOICING_DATE
,GL_ACCOUNT_ID
,DEDUCTION_TYPE_CODE
,DEDUCTION_AMOUNT
,EXPORTED_CODE
,COMMENTS
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORG_ID
FROM PN_VAR_DEDUCTIONS_ALL
WHERE DEDUCTION_ID = v_lines.vol_deduct_id;
UPDATE PN_VAR_DEDUCTIONS_ALL
SET 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_ITEM_ID = v_lines.line_item_id
,START_DATE = v_lines.rep_str_DATE
,END_DATE = v_lines.rep_end_DATE
,DEDUCTION_TYPE_CODE = v_lines.deduction_type_code
,DEDUCTION_AMOUNT = NVL(v_ded_amount,0)
WHERE DEDUCTION_ID = v_lines.vol_deduct_id;
pnp_debug_pkg.log('before upDATE of table pn_vol_hist_lines_itf');
UPDATE pn_vol_hist_lines_itf
SET status = 'I',
error_log = NULL,
group_DATE = v_group_DATE
WHERE rowid = v_lines.rowid;
UPDATE pn_vol_hist_lines_itf
SET error_log = SUBSTR(l_error_message, 1, 240),
status ='E'
WHERE rowid = v_lines.rowid;
UPDATE pn_vol_hist_batch_itf
SET status = v_status_code
WHERE batch_id = p_batch_id;
UPDATE
pn_var_lines_all
SET
sales_vol_update_flag = 'Y'
WHERE
line_item_id = lines_t(line_id);
PROCEDURE delete_vol_hist(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_batch_id IN NUMBER,
p_start_DATE IN VARCHAR2,
p_end_DATE IN VARCHAR2) IS
CURSOR c_batch IS
SELECT batch_id,
batch_name,
status
FROM pn_vol_hist_batch_itf
WHERE ((p_batch_id IS NOT NULL AND batch_id = p_batch_id) OR (p_batch_id IS NULL))
AND ((fnd_DATE.canonical_to_DATE(p_start_DATE) IS NOT NULL
AND min_rep_DATE >= fnd_DATE.canonical_to_DATE(p_start_DATE))
OR (p_start_DATE IS NULL))
AND ((fnd_DATE.canonical_to_DATE(p_end_DATE) IS NOT NULL
AND max_rep_DATE <= fnd_DATE.canonical_to_DATE(p_end_DATE))
OR (p_end_DATE IS NULL))
AND status IN ('I','E');
l_total_batch_deleted NUMBER := 0 ;
l_total_lines_deleted NUMBER := 0 ;
PNP_DEBUG_PKG.debug ('PN_SOI_VOL_IMPORT .DELETE_VOL_HIST(+)');
DELETE FROM pn_vol_hist_lines_itf
WHERE batch_id = v_batch.batch_id;
l_total_lines_deleted := l_total_lines_deleted + SQL%ROWCOUNT ;
DELETE FROM pn_vol_hist_batch_itf
WHERE batch_id = v_batch.batch_id;
l_total_batch_deleted := l_total_batch_deleted + 1;
DELETE FROM pn_vol_hist_lines_itf
WHERE batch_id = v_batch.batch_id
AND status = 'I';
l_total_lines_deleted := l_total_lines_deleted + SQL%ROWCOUNT ;
fnd_message.set_name('PN', 'PN_SOI_BATCH_TOTAL_DELETED');
fnd_message.set_token('BATCH_TOTAL', l_total_batch_deleted);
fnd_message.set_name('PN', 'PN_SOI_LINES_TOTAL_DELETED');
fnd_message.set_token('LINES_TOTAL', l_total_lines_deleted);
pnp_debug_pkg.debug ('PN_VAR_RENTS_PKG.DELETE_VOL_HIST (-)');
END delete_vol_hist;