[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rec_agreement_num,
rec_agreement_name
FROM pn_rec_agreements_all
WHERE rec_agreement_id = p_rec_agreement_id;
SELECT name,
lease_num
FROM pn_leases_all
WHERE lease_id = p_lease_id;
SELECT location_code
FROM pn_locations_all
WHERE location_id = p_location_id
AND NVL(l_as_of_date,sysdate) between active_start_date and
active_end_date;
SELECT pra.rec_agreement_id
,pra.lease_id
,pra.location_id
,pra.customer_id
,pra.cust_site_id
,prc.start_date
,prc.end_date
,prc.as_of_date
,prc.rec_calc_period_id
FROM pn_leases pl
,pn_rec_agreements_all pra
,pn_rec_calc_periods_all prc
,pn_locations_all ploc
WHERE pl.lease_id = pra.lease_id
AND pra.customer_id = nvl(p_customer_id,pra.customer_id)
AND pra.cust_site_id = nvl(p_cust_site_id,pra.cust_site_id)
AND pra.rec_agreement_id = prc.rec_agreement_id
AND prc.end_date <= nvl(l_calc_period_ending,prc.end_date)
AND ploc.location_id = pra.location_id
AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
AND (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pra.rec_agreement_id
;
SELECT pra.rec_agreement_id
,pra.lease_id
,pra.location_id
,pra.customer_id
,pra.cust_site_id
,prc.start_date
,prc.end_date
,prc.as_of_date
,prc.rec_calc_period_id
FROM pn_leases pl
,pn_rec_agreements_all pra
,pn_rec_calc_periods_all prc
WHERE pl.lease_id = pra.lease_id
AND pra.customer_id = nvl(p_customer_id,pra.customer_id)
AND pra.cust_site_id = nvl(p_cust_site_id,pra.cust_site_id)
AND pra.rec_agreement_id = prc.rec_agreement_id
AND prc.end_date <= nvl(l_calc_period_ending,prc.end_date)
AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
AND (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pra.rec_agreement_id
;
SELECT pra.rec_agreement_id
,pra.lease_id
,pra.location_id
,pra.customer_id
,pra.cust_site_id
,prc.start_date
,prc.end_date
,prc.as_of_date
,prc.rec_calc_period_id
FROM pn_leases pl
,pn_rec_agreements_all pra
,pn_rec_calc_periods_all prc
,pn_locations_all ploc
,pn_properties_all prop
WHERE pl.lease_id = pra.lease_id
AND pra.customer_id = nvl(p_customer_id,pra.customer_id)
AND pra.cust_site_id = nvl(p_cust_site_id,pra.cust_site_id)
AND pra.rec_agreement_id = prc.rec_agreement_id
AND prc.end_date <= nvl(l_calc_period_ending,prc.end_date)
AND ploc.location_id = pra.location_id
AND ploc.property_id = prop.property_id
AND prop.property_code = p_property_name
AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
AND (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pra.rec_agreement_id
;
SELECT pra.rec_agreement_id
,pra.lease_id
,pra.location_id
,pra.customer_id
,pra.cust_site_id
,prc.start_date
,prc.end_date
,prc.as_of_date
,prc.rec_calc_period_id
FROM pn_leases pl
,pn_rec_agreements_all pra
,pn_rec_calc_periods_all prc
,pn_locations_all ploc
,pn_properties_all prop
WHERE pl.lease_id = pra.lease_id
AND pra.customer_id = nvl(p_customer_id,pra.customer_id)
AND pra.cust_site_id = nvl(p_cust_site_id,pra.cust_site_id)
AND pra.rec_agreement_id = prc.rec_agreement_id
AND prc.end_date <= nvl(l_calc_period_ending,prc.end_date)
AND ploc.location_id = pra.location_id
AND ploc.property_id = prop.property_id
AND prop.property_code = p_property_name
AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
AND (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pra.rec_agreement_id
;
template_name_tbl.DELETE;
template_id_tbl.DELETE;
SELECT pra.rec_agreement_id
,pra.lease_id
,pra.location_id
,pra.customer_id
,pra.cust_site_id
FROM pn_leases pl
,pn_rec_agreements_all pra
,pn_locations_all ploc
WHERE pl.lease_id = pra.lease_id
AND ploc.location_id = pra.location_id
AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
AND (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pra.rec_agreement_id
;
SELECT pra.rec_agreement_id
,pra.lease_id
,pra.location_id
,pra.customer_id
,pra.cust_site_id
FROM pn_leases pl
,pn_rec_agreements_all pra
WHERE pl.lease_id = pra.lease_id
AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
AND (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pra.rec_agreement_id
;
SELECT pra.rec_agreement_id
,pra.lease_id
,pra.location_id
,pra.customer_id
,pra.cust_site_id
FROM pn_leases pl
,pn_rec_agreements_all pra
,pn_locations_all ploc
,pn_properties_all prop
WHERE pl.lease_id = pra.lease_id
AND ploc.location_id = pra.location_id
AND ploc.property_id = prop.property_id
AND prop.property_code = p_property_name
AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
AND (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pra.rec_agreement_id
;
SELECT pra.rec_agreement_id
,pra.lease_id
,pra.location_id
,pra.customer_id
,pra.cust_site_id
FROM pn_leases pl
,pn_rec_agreements_all pra
,pn_locations_all ploc
,pn_properties_all prop
WHERE pl.lease_id = pra.lease_id
AND ploc.location_id = pra.location_id
AND ploc.property_id = prop.property_id
AND prop.property_code = p_property_name
AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
AND (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pra.rec_agreement_id
;
SELECT currency_code
,negative_recovery
,rec_agreement_name
,rec_agreement_num
,org_id
FROM pn_rec_agreements_all
WHERE rec_agreement_id = p_rec_agreement_id;
SELECT lines.rec_agr_line_id
,lines.type
,lines.purpose
,lines.start_date
,lines.end_date
,lines.calc_method
,lines.fixed_amount
,lines.fixed_rate
,lines.fixed_pct
,lines.multiple_pct
FROM pn_rec_agr_lines_all lines
WHERE lines.rec_agreement_id = p_rec_agreement_id
AND p_as_of_date between lines.start_date AND end_date ;
SELECT lines.rec_agr_line_id
,lines.type
,lines.purpose
,lines.start_date
,lines.end_date
,lines.calc_method
,lines.fixed_amount
,lines.fixed_rate
,lines.fixed_pct
,lines.multiple_pct
FROM pn_rec_agr_lines_all lines
WHERE lines.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between lines.start_date AND end_date ;
SELECT 'Y'
FROM DUAL
WHERE exists (SELECT NULL
FROM pn_rec_period_lines_all plines
,pn_rec_agr_lines_all lines
,pn_rec_calc_periods_all calc_periods
WHERE lines.rec_agreement_id = p_rec_agreement_id
AND p_as_of_date between lines.start_date and lines.end_date
AND plines.rec_agr_line_id = lines.rec_agr_line_id
AND plines.start_date = p_calc_period_start_date
AND plines.end_date = p_calc_period_end_date
AND UPPER(plines.status) <> 'COMPLETE'
AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
AND calc_periods.start_date = p_calc_period_start_date
AND calc_periods.end_date = p_calc_period_end_date
AND calc_periods.as_of_date = p_as_of_date
);
SELECT 'Y'
FROM DUAL
WHERE not exists (SELECT NULL
FROM pn_rec_period_lines_all plines
,pn_rec_agr_lines_all lines
,pn_rec_calc_periods_all calc_periods
WHERE lines.rec_agreement_id = p_rec_agreement_id
AND p_as_of_date between lines.start_date and lines.end_date
AND plines.rec_agr_line_id = lines.rec_agr_line_id
AND plines.start_date = p_calc_period_start_date
AND plines.end_date = p_calc_period_end_date
AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
AND calc_periods.start_date = p_calc_period_start_date
AND calc_periods.end_date = p_calc_period_end_date
AND calc_periods.as_of_date = p_as_of_date
);
SELECT NVL(SUM(NVL(plines.reconciled_amount,0)),0) RECONCILED_AMOUNT
FROM pn_rec_period_lines_all plines
,pn_rec_calc_periods_all calc_periods
,pn_rec_agr_lines_all lines
WHERE lines.rec_agreement_id = p_rec_agreement_id
AND plines.rec_agr_line_id = lines.rec_agr_line_id
AND plines.start_date = p_calc_period_start_date
AND plines.end_date = p_calc_period_end_date
AND UPPER(plines.status) = 'COMPLETE'
AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
AND calc_periods.start_date = p_calc_period_start_date
AND calc_periods.end_date = p_calc_period_end_date
AND calc_periods.as_of_date = p_as_of_date
;
SELECT plines.rec_agr_line_id
,plines.end_date
,NVL(plines.reconciled_amount,0) RECONCILED_AMOUNT
,plines.billing_type
,plines.billing_purpose
FROM pn_rec_period_lines_all plines
,pn_rec_calc_periods_all calc_periods
,pn_rec_agr_lines_all lines
WHERE lines.rec_agreement_id = p_rec_agreement_id
AND plines.rec_agr_line_id = lines.rec_agr_line_id
AND plines.start_date = p_calc_period_start_date
AND plines.end_date = p_calc_period_end_date
AND UPPER(plines.status) = 'COMPLETE'
AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
AND calc_periods.start_date = p_calc_period_start_date
AND calc_periods.end_date = p_calc_period_end_date
AND calc_periods.as_of_date = p_as_of_date
;
SELECT plines.rec_agr_line_id
,plines.end_date
,nvl(plines.reconciled_amount,0) RECONCILED_AMOUNT
,plines.billing_type
,plines.billing_purpose
FROM pn_rec_period_lines_all plines
,pn_rec_calc_periods_all calc_periods
WHERE plines.rec_agr_line_id = p_rec_agr_line_id
AND plines.start_date = p_calc_period_start_date
AND plines.end_date = p_calc_period_end_date
AND upper(plines.status) = 'COMPLETE'
AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
AND calc_periods.start_date = p_calc_period_start_date
AND calc_periods.end_date = p_calc_period_end_date
AND calc_periods.as_of_date = p_as_of_date
;
line_constr_tbl.delete;
line_constr_tbl.delete;
end dates and the as of date.If it has not, then insert a new record into the
PN_REC_PERIOD_LINES_ALL table with the calculated values or else update the
existing record with the values as a result of the re-calculation. */
l_rec_period_lines_id := PN_REC_CALC_PKG.find_if_period_line_exists(
p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
,p_rec_calc_period_id => p_rec_calc_period_id);
pnp_debug_pkg.log('Inserting into PN_REC_PERIOD_LINES_ALL ');
PN_REC_CALC_PKG.INSERT_PERIOD_LINES_ROW(
X_ROWID => l_rowId
,X_REC_PERIOD_LINES_ID => l_rec_period_lines_id
,X_BUDGET_PCT => l_BUDGET_PCT
,X_OCCUPANCY_PCT => l_ten_occupancy_pct
,X_MULTIPLE_PCT => agr_lines_record.MULTIPLE_PCT
,X_FIXED_PCT => agr_lines_record.fixed_pct
,X_TENANCY_START_DATE => l_tenancy_start_date
,X_TENANCY_END_DATE => l_tenancy_end_date
,X_STATUS => l_status
,X_BUDGET_PRORATA_SHARE => l_BUDGET_PRORATA_SHARE
,X_BUDGET_COST_PER_AREA => l_BUDGET_COST_PER_AREA
,X_TOTAL_AREA => l_tot_prop_area
,X_TOTAL_EXPENSE => l_line_expenses
,X_RECOVERABLE_AREA => l_ten_recoverable_area
,X_ACTUAL_RECOVERY => l_ten_actual_recovery
,X_CONSTRAINED_ACTUAL => l_constrained_actual
,X_ABATEMENTS => l_line_abatements
,X_ACTUAL_PRORATA_SHARE => l_actual_prorata_share
,X_BILLED_RECOVERY => l_billed_recovery
,X_RECONCILED_AMOUNT => l_reconciled_amount
,X_BUDGET_RECOVERY => l_BUDGET_RECOVERY
,X_BUDGET_EXPENSE => l_BUDGET_EXPENSE
,X_REC_CALC_PERIOD_ID => p_REC_CALC_PERIOD_ID
,X_REC_AGR_LINE_ID => agr_lines_record.REC_AGR_LINE_ID
,X_AS_OF_DATE => p_AS_OF_DATE
,X_START_DATE => p_calc_period_START_DATE
,X_END_DATE => p_calc_period_END_DATE
,X_BILLING_TYPE => agr_lines_record.type
,X_BILLING_PURPOSE => agr_lines_record.purpose
,X_CUST_ACCOUNT_ID => p_customer_id
,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
,X_ERROR_CODE => l_error_code);
p_error := 'Error inserting into period lines';
PN_REC_CALC_PKG.UPDATE_PERIOD_LINES_ROW(
X_REC_PERIOD_LINES_ID => l_rec_period_lines_id
,X_BUDGET_PCT => l_BUDGET_PCT
,X_OCCUPANCY_PCT => l_ten_occupancy_pct
,X_MULTIPLE_PCT => agr_lines_record.MULTIPLE_PCT
,X_FIXED_PCT => agr_lines_record.fixed_pct
,X_TENANCY_START_DATE => l_TENANCY_START_DATE
,X_TENANCY_END_DATE => l_TENANCY_END_DATE
,X_STATUS => l_status
,X_BUDGET_PRORATA_SHARE => l_BUDGET_PRORATA_SHARE
,X_BUDGET_COST_PER_AREA => l_BUDGET_COST_PER_AREA
,X_TOTAL_AREA => l_tot_prop_area
,X_TOTAL_EXPENSE => l_line_expenses
,X_RECOVERABLE_AREA => l_ten_recoverable_area
,X_ACTUAL_RECOVERY => l_ten_actual_recovery
,X_CONSTRAINED_ACTUAL => l_constrained_actual
,X_ABATEMENTS => l_line_abatements
,X_ACTUAL_PRORATA_SHARE => l_actual_prorata_share
,X_BILLED_RECOVERY => l_billed_recovery
,X_RECONCILED_AMOUNT => l_reconciled_amount
,X_BUDGET_RECOVERY => l_BUDGET_RECOVERY
,X_BUDGET_EXPENSE => l_BUDGET_EXPENSE
,X_REC_CALC_PERIOD_ID => p_REC_CALC_PERIOD_ID
,X_REC_AGR_LINE_ID => agr_lines_record.REC_AGR_LINE_ID
,X_AS_OF_DATE => p_AS_OF_DATE
,X_START_DATE => p_calc_period_START_DATE
,X_END_DATE => p_calc_period_END_DATE
,X_BILLING_TYPE => agr_lines_record.type
,X_BILLING_PURPOSE => agr_lines_record.purpose
,X_CUST_ACCOUNT_ID => p_customer_id
,X_LAST_UPDATE_DATE => l_creation_date
,X_LAST_UPDATED_BY => l_created_by
,X_LAST_UPDATE_LOGIN => l_created_by
,X_ERROR_CODE => l_error_code);
UPDATE pn_rec_period_lines_all
SET STATUS = 'Error'
WHERE rec_agr_line_id in (SELECT rec_agr_line_id
FROM PN_REC_AGR_LINES_ALL
WHERE rec_agreement_id = p_rec_agreement_id)
AND start_date = p_calc_period_start_date
AND end_date = p_calc_period_end_date
AND rec_calc_period_id = p_rec_calc_period_id;
UPDATE pn_rec_period_lines_all
SET STATUS = 'Error'
WHERE rec_agr_line_id = l_rec_agr_line_id
AND start_date = p_calc_period_start_date
AND end_date = p_calc_period_end_date
AND rec_calc_period_id = p_rec_calc_period_id;
SELECT NVL(plines.recoverable_area,0)
INTO l_recoverable_area
FROM pn_rec_period_lines_all plines
WHERE plines.rec_agr_line_id = p_rec_agr_line_id
AND plines.rec_calc_period_id = p_rec_calc_period_id
;
SELECT area_class_dtl_hdr.TOTAL_assignable_area
,nvl(area_class_dtl_hdr.TOTAL_OCCUPIED_AREA_ovr,area_class_dtl_hdr.TOTAL_OCCUPIED_AREA)
,nvl(area_class_dtl_hdr.TOTAL_WEIGHTED_AVG_ovr, area_class_dtl_hdr.TOTAL_WEIGHTED_AVG)
,linearea.FLOOR_PCT
,linearea.area_type
,area_class_dtl_hdr.area_class_dtl_id
FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
,pn_rec_arcl_dtl_all area_class_dtl_hdr
,pn_rec_agr_linarea_all linearea
,pn_rec_arcl_all aclass
WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between linearea.start_date and linearea.end_date
AND linearea.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.as_of_date = p_as_of_date
AND area_class_dtl_hdr.from_date = p_calc_period_start_date
AND area_class_dtl_hdr.to_date = p_calc_period_end_date
AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
AND area_class_dtl_lines.cust_account_id = p_customer_id
AND area_class_dtl_lines.lease_id = p_lease_id
AND area_class_dtl_lines.location_id = p_location_id
;
SELECT NVL(SUM(ASSIGNABLE_AREA),0),
NVL(SUM(NVL(occupied_area_ovr, occupied_area)),0),
NVL(SUM (NVL( WEIGHTED_AVG_OVR, WEIGHTED_AVG)),0)
INTO
l_asgn_area_contr,
l_occp_area_contr,
l_wgt_avg_area_contr
FROM pn_rec_arcl_dtlln_all area_class_dtl
WHERE area_class_dtl_line_id = l_area_class_dtl_id
AND exclude_area_ovr_flag = 'Y'
AND include_flag ='Y';
SELECT nvl(area_class_dtl_lines.occupied_area_ovr, area_class_dtl_lines.occupied_area)
,occupancy_pct
INTO l_ten_recoverable_area_rec
FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
,pn_rec_arcl_dtl_all area_class_dtl_hdr
,pn_rec_agr_linarea_all linearea
,pn_rec_arcl_all aclass
WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between linearea.start_date and linearea.end_date
AND linearea.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.as_of_date = p_as_of_date
AND area_class_dtl_hdr.from_date = p_calc_period_start_date
AND area_class_dtl_hdr.to_date = p_calc_period_end_date
AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
AND area_class_dtl_lines.cust_account_id = p_customer_id
AND area_class_dtl_lines.lease_id = p_lease_id
AND area_class_dtl_lines.location_id = p_location_id
AND area_class_dtl_lines.include_flag = 'Y'
;
SELECT purpose, type
FROM pn_rec_agr_lines_all
WHERE rec_agr_line_id = p_rec_agr_line_id;
SELECT 'Y'
FROM dual
WHERE exists(
SELECT 'Y'
FROM pn_rec_period_lines_all period_lines
,pn_rec_agreements_all recagr
,pn_rec_agr_lines_all lines
WHERE nvl(period_lines.actual_prorata_share,0) = 0
AND period_lines.start_date = p_calc_period_start_date
AND period_lines.end_date = p_calc_period_end_date
AND period_lines.as_of_date = p_as_of_date
and lines.purpose = p_line_purpose -- Fix for bug # 3123283
and lines.type = p_line_type -- Fix for bug # 3123283
and lines.rec_agr_line_id = period_lines.rec_agr_line_id
and lines.rec_agreement_id = recagr.rec_agreement_id
and (recagr.location_id, recagr.customer_id, recagr.lease_id) in
(
SELECT area_class_dtl_lines.location_id,
area_class_dtl_lines.cust_account_id,
area_class_dtl_lines.lease_id
FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
WHERE area_class_dtl_id =
(SELECT area_class_dtl_hdr.area_class_dtl_id
FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
,pn_rec_arcl_dtl_all area_class_dtl_hdr
,pn_rec_agr_linarea_all linearea
,pn_rec_arcl_all aclass
WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between linearea.start_date
and linearea.end_date
AND linearea.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.as_of_date = p_as_of_date
AND area_class_dtl_hdr.from_date = p_calc_period_start_date
AND area_class_dtl_hdr.to_date = p_calc_period_end_date
AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
AND area_class_dtl_lines.cust_account_id = p_customer_id
AND area_class_dtl_lines.lease_id = p_lease_id
AND area_class_dtl_lines.location_id = p_location_id)
AND area_class_dtl_lines.include_flag = 'Y'
AND area_class_dtl_lines.exclude_prorata_ovr_flag = 'Y'
));
SELECT NVL(SUM(NVL(period_lines.actual_prorata_share,0)),0)
INTO l_contr_actual_recovery
FROM pn_rec_period_lines_all period_lines
,pn_rec_agreements_all recagr
,pn_rec_agr_lines_all lines
WHERE period_lines.start_date = p_calc_period_start_date
AND period_lines.end_date = p_calc_period_end_date
AND period_lines.as_of_date = p_as_of_date
AND lines.rec_agr_line_id = period_lines.rec_agr_line_id
AND lines.rec_agreement_id = recagr.rec_agreement_id
AND lines.purpose = l_line_purpose
AND lines.type = l_line_type
AND (recagr.location_id, recagr.customer_id, recagr.lease_id) in
(
SELECT area_class_dtl_lines.location_id,
area_class_dtl_lines.cust_account_id,
area_class_dtl_lines.lease_id
FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
WHERE area_class_dtl_id =
(SELECT area_class_dtl_hdr.area_class_dtl_id
FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
,pn_rec_arcl_dtl_all area_class_dtl_hdr
,pn_rec_agr_linarea_all linearea
,pn_rec_arcl_all aclass
WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between linearea.start_date
and linearea.end_date
AND linearea.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.as_of_date = p_as_of_date
AND area_class_dtl_hdr.from_date = p_calc_period_start_date
AND area_class_dtl_hdr.to_date = p_calc_period_end_date
AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
AND area_class_dtl_lines.cust_account_id = p_customer_id
AND area_class_dtl_lines.lease_id = p_lease_id
AND area_class_dtl_lines.location_id = p_location_id)
AND area_class_dtl_lines.include_flag = 'Y'
AND area_class_dtl_lines.exclude_prorata_ovr_flag = 'Y'
);
SELECT nvl(exp_detail_line.computed_recoverable_amt,0),
nvl(exp_detail_line.cls_line_fee_before_contr_ovr,0),
nvl(exp_detail_hdr.cls_line_fee_after_contr,0)
INTO l_line_expenses, l_fee_before, l_fee_after
FROM pn_rec_expcl_all rec_exp_class
,pn_rec_agr_linexp_all lineexp
,pn_rec_expcl_dtl_all exp_detail_hdr
,pn_rec_exp_line_all exp_extract_hdr
,pn_rec_expcl_dtlln_all exp_detail_line
WHERE exp_detail_hdr.expense_class_dtl_id = exp_detail_line.expense_class_dtl_id
AND exp_detail_line.cust_account_id = p_customer_id
AND exp_detail_line.lease_id = p_lease_id
AND exp_detail_line.location_id = p_location_id
AND exp_extract_hdr.to_date = p_calc_period_end_date
AND exp_extract_hdr.from_date = p_calc_period_start_date
AND exp_extract_hdr.as_of_date = p_calc_period_as_of_date
AND exp_extract_hdr.expense_line_id = exp_detail_hdr.expense_line_id
AND exp_detail_hdr.expense_class_id = rec_exp_class.expense_class_id
AND rec_exp_class.expense_class_id = lineexp.expense_class_id
AND p_calc_period_as_of_date between lineexp.start_date and lineexp.end_date
AND lineexp.rec_agr_line_id = p_rec_agr_line_id
;
| 27-AUG-04 abanerje o Modified the select statement to apply the share%
| to the budgeted amount. Bug 3711709.
+===========================================================================*/
FUNCTION get_budget_expenses (
p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
,p_customer_id pn_rec_agreements_all.customer_id%TYPE
,p_lease_id pn_rec_agreements_all.lease_id%TYPE
,p_location_id pn_rec_agreements_all.location_id%TYPE
,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
,p_calc_period_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
)
RETURN pn_rec_expcl_dtlln_all.budgeted_amt%TYPE IS
l_budget_expenses pn_rec_expcl_dtlln_all.budgeted_amt%TYPE;
SELECT NVL(
SUM(
NVL(expcl_lndtl_alloc.BUDGETED_AMT* (
(NVL
(NVL
(expcl_lndtl_alloc.CLS_LINE_DTL_SHARE_PCT_OVR,
expcl_lndtl_alloc.CLS_LINE_DTL_SHARE_PCT)
,100)
)/100)
,0)
) ,0)
INTO l_budget_expenses
FROM pn_rec_expcl_all rec_exp_class
,pn_rec_agr_linexp_all lineexp
,pn_rec_expcl_dtl_all exp_detail_hdr
,pn_rec_exp_line_all exp_extract_hdr
,pn_rec_expcl_dtlln_all exp_detail_line
,pn_rec_expcl_dtlacc_all expcl_lndtl_alloc
WHERE exp_detail_hdr.expense_class_dtl_id = exp_detail_line.expense_class_dtl_id
AND exp_detail_line.cust_account_id = p_customer_id
AND exp_detail_line.lease_id = p_lease_id
AND exp_detail_line.location_id = p_location_id
AND exp_extract_hdr.to_date = p_calc_period_end_date
AND exp_extract_hdr.from_date = p_calc_period_start_date
AND exp_extract_hdr.as_of_date = p_calc_period_as_of_date
AND exp_extract_hdr.expense_line_id = exp_detail_hdr.expense_line_id
AND exp_detail_hdr.expense_class_id = rec_exp_class.expense_class_id
AND rec_exp_class.expense_class_id = lineexp.expense_class_id
AND p_calc_period_as_of_date between lineexp.start_date AND lineexp.end_date
AND lineexp.rec_agr_line_id = p_rec_agr_line_id
AND expcl_lndtl_alloc.expense_class_line_id = exp_detail_line.expense_class_line_id
;
SELECT nvl(SUM(pitem.actual_amount),0)
INTO l_billed_recovery
FROM pn_payment_items_all pitem
,pn_payment_schedules_all psched
,pn_payment_terms_all ppt
WHERE psched.payment_status_lookup_code = 'APPROVED'
AND to_date(to_char(psched.schedule_date,'mm/yyyy'),'mm/yyyy') between
to_date(to_char( p_calc_period_start_date,'mm/yyyy'),'mm/yyyy')
and to_date(to_char(p_calc_period_end_date,'mm/yyyy'),'mm/yyyy')
AND psched.lease_id = p_lease_id
AND psched.payment_schedule_id = pitem.payment_schedule_id
AND pitem.payment_item_type_lookup_code = 'CASH'
AND pitem.payment_term_id = ppt.payment_term_id
AND nvl(pitem.export_to_ar_flag,'N') = 'Y'
AND ppt.payment_purpose_code = p_payment_purpose
AND ppt.payment_term_type_code = p_payment_type
AND ppt.start_date <= p_calc_period_end_date
AND ppt.end_date >= p_calc_period_start_date
AND ppt.currency_code = g_currency_code
AND ppt.recoverable_flag = 'Y'
AND ppt.lease_id = p_lease_id
AND ppt.location_id = p_location_id
;
SELECT CONSTR_ORDER,
SCOPE,
RELATION,
VALUE,
CPI_INDEX,
BASE_YEAR
FROM PN_REC_AGR_LINCONST_ALL lineconst
WHERE lineconst.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between lineconst.start_date and lineconst.end_date
;
SELECT NVL(SUM(NVL(amount,0)),0)
FROM pn_rec_agr_linabat_all abate
WHERE abate.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between abate.start_date AND abate.end_date;
SELECT periods.rec_period_lines_id
FROM pn_rec_period_lines_all periods
WHERE periods.rec_agr_line_id = p_rec_agr_line_id
AND periods.rec_calc_period_id = p_rec_calc_period_id;
| INSERT_PERIOD_LINES_ROW
|
| DESCRIPTION
| Create records in the PN_REC_PERIOD_LINES_ALL table
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| OUT:
|
| RETURNS : None
|
| NOTES : Create records in the PN_REC_PERIOD_LINES_ALL
|
| MODIFICATION HISTORY
|
| 21-MAY-2003 Daniel Thota o Created
| 25-JUL-2003 Daniel Thota o Added X_FIXED_PCT to INSERT_PERIOD_LINES_ROW
| and UPDATE_PERIOD_LINES_ROW.Fix for bug# 3067662
+===========================================================================*/
procedure INSERT_PERIOD_LINES_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2
,X_REC_PERIOD_LINES_ID IN OUT NOCOPY NUMBER
,X_BUDGET_PCT IN NUMBER
,X_OCCUPANCY_PCT IN NUMBER
,X_MULTIPLE_PCT IN NUMBER
,X_TENANCY_START_DATE IN DATE
,X_TENANCY_END_DATE IN DATE
,X_STATUS IN VARCHAR2
,X_BUDGET_PRORATA_SHARE IN NUMBER
,X_BUDGET_COST_PER_AREA IN NUMBER
,X_TOTAL_AREA IN NUMBER
,X_TOTAL_EXPENSE IN NUMBER
,X_RECOVERABLE_AREA IN NUMBER
,X_ACTUAL_RECOVERY IN NUMBER
,X_CONSTRAINED_ACTUAL IN NUMBER
,X_ABATEMENTS IN NUMBER
,X_ACTUAL_PRORATA_SHARE IN NUMBER
,X_BILLED_RECOVERY IN NUMBER
,X_RECONCILED_AMOUNT IN NUMBER
,X_BUDGET_RECOVERY IN NUMBER
,X_BUDGET_EXPENSE IN NUMBER
,X_REC_CALC_PERIOD_ID IN NUMBER
,X_REC_AGR_LINE_ID IN NUMBER
,X_AS_OF_DATE IN DATE
,X_START_DATE IN DATE
,X_END_DATE IN DATE
,X_BILLING_TYPE IN VARCHAR2
,X_BILLING_PURPOSE IN VARCHAR2
,X_CUST_ACCOUNT_ID IN NUMBER
,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_FIXED_PCT IN NUMBER
,X_ERROR_CODE IN OUT NOCOPY NUMBER
) is
CURSOR C is
select ROWID
from PN_REC_PERIOD_LINES
where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID;
SELECT org_id
FROM pn_rec_calc_periods_all
WHERE rec_calc_period_id = X_REC_CALC_PERIOD_ID;
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.INSERT_PERIOD_LINES_ROW (+)');
select pn_rec_period_lines_s.nextval
into X_REC_PERIOD_LINES_ID
from dual;
INSERT INTO PN_REC_PERIOD_LINES_ALL (
BUDGET_PCT
,OCCUPANCY_PCT
,MULTIPLE_PCT
,TENANCY_START_DATE
,TENANCY_END_DATE
,STATUS
,BUDGET_PRORATA_SHARE
,BUDGET_COST_PER_AREA
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,TOTAL_AREA
,TOTAL_EXPENSE
,RECOVERABLE_AREA
,ACTUAL_RECOVERY
,CONSTRAINED_ACTUAL
,ABATEMENTS
,ACTUAL_PRORATA_SHARE
,BILLED_RECOVERY
,RECONCILED_AMOUNT
,BUDGET_RECOVERY
,BUDGET_EXPENSE
,REC_PERIOD_LINES_ID
,REC_CALC_PERIOD_ID
,REC_AGR_LINE_ID
,AS_OF_DATE
,START_DATE
,END_DATE
,BILLING_TYPE
,BILLING_PURPOSE
,CUST_ACCOUNT_ID
,FIXED_PCT
,ORG_ID
)
values(
X_BUDGET_PCT
,X_OCCUPANCY_PCT
,X_MULTIPLE_PCT
,X_TENANCY_START_DATE
,X_TENANCY_END_DATE
,X_STATUS
,X_BUDGET_PRORATA_SHARE
,X_BUDGET_COST_PER_AREA
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_CREATION_DATE
,X_CREATED_BY
,X_LAST_UPDATE_LOGIN
,X_TOTAL_AREA
,X_TOTAL_EXPENSE
,X_RECOVERABLE_AREA
,X_ACTUAL_RECOVERY
,X_CONSTRAINED_ACTUAL
,X_ABATEMENTS
,X_ACTUAL_PRORATA_SHARE
,X_BILLED_RECOVERY
,X_RECONCILED_AMOUNT
,X_BUDGET_RECOVERY
,X_BUDGET_EXPENSE
,X_REC_PERIOD_LINES_ID
,X_REC_CALC_PERIOD_ID
,X_REC_AGR_LINE_ID
,X_AS_OF_DATE
,X_START_DATE
,X_END_DATE
,X_BILLING_TYPE
,X_BILLING_PURPOSE
,X_CUST_ACCOUNT_ID
,X_FIXED_PCT
,l_org_ID
);
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.INSERT_PERIOD_LINES_ROW (-)');
PNP_DEBUG_PKG.log ('Error inserting into period lines'|| to_char(sqlcode));
end INSERT_PERIOD_LINES_ROW;
| UPDATE_PERIOD_LINES_ROW
|
| DESCRIPTION
| Update records in the PN_REC_PERIOD_LINES_ALL table
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| OUT:
|
| RETURNS : None
|
| NOTES : Update records in the PN_REC_PERIOD_LINES_ALL
|
| MODIFICATION HISTORY
|
| 21-MAY-2003 Daniel Thota o Created
| 25-JUL-2003 Daniel Thota o Added X_FIXED_PCT to INSERT_PERIOD_LINES_ROW
| and UPDATE_PERIOD_LINES_ROW.Fix for bug# 3067662
+===========================================================================*/
procedure UPDATE_PERIOD_LINES_ROW (
X_REC_PERIOD_LINES_ID in NUMBER
,X_BUDGET_PCT in NUMBER
,X_OCCUPANCY_PCT in NUMBER
,X_MULTIPLE_PCT in NUMBER
,X_TENANCY_START_DATE in DATE
,X_TENANCY_END_DATE in DATE
,X_STATUS in VARCHAR2
,X_BUDGET_PRORATA_SHARE in NUMBER
,X_BUDGET_COST_PER_AREA in NUMBER
,X_TOTAL_AREA in NUMBER
,X_TOTAL_EXPENSE in NUMBER
,X_RECOVERABLE_AREA in NUMBER
,X_ACTUAL_RECOVERY in NUMBER
,X_CONSTRAINED_ACTUAL in NUMBER
,X_ABATEMENTS in NUMBER
,X_ACTUAL_PRORATA_SHARE in NUMBER
,X_BILLED_RECOVERY in NUMBER
,X_RECONCILED_AMOUNT in NUMBER
,X_BUDGET_RECOVERY in NUMBER
,X_BUDGET_EXPENSE in NUMBER
,X_REC_CALC_PERIOD_ID in NUMBER
,X_REC_AGR_LINE_ID in NUMBER
,X_AS_OF_DATE in DATE
,X_START_DATE in DATE
,X_END_DATE in DATE
,X_BILLING_TYPE in VARCHAR2
,X_BILLING_PURPOSE in VARCHAR2
,X_CUST_ACCOUNT_ID in NUMBER
,X_LAST_UPDATE_DATE in DATE
,X_LAST_UPDATED_BY in NUMBER
,X_LAST_UPDATE_LOGIN in NUMBER
,X_FIXED_PCT in NUMBER
,X_ERROR_CODE in out NOCOPY NUMBER
) is
BEGIN
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_LINES_ROW (+)');
update PN_REC_PERIOD_LINES_ALL set
BUDGET_PCT = X_BUDGET_PCT
,OCCUPANCY_PCT = X_OCCUPANCY_PCT
,MULTIPLE_PCT = X_MULTIPLE_PCT
,TENANCY_START_DATE = X_TENANCY_START_DATE
,TENANCY_END_DATE = X_TENANCY_END_DATE
,STATUS = X_STATUS
,BUDGET_PRORATA_SHARE = X_BUDGET_PRORATA_SHARE
,BUDGET_COST_PER_AREA = X_BUDGET_COST_PER_AREA
,TOTAL_AREA = X_TOTAL_AREA
,TOTAL_EXPENSE = X_TOTAL_EXPENSE
,RECOVERABLE_AREA = X_RECOVERABLE_AREA
,ACTUAL_RECOVERY = X_ACTUAL_RECOVERY
,CONSTRAINED_ACTUAL = X_CONSTRAINED_ACTUAL
,ABATEMENTS = X_ABATEMENTS
,ACTUAL_PRORATA_SHARE = X_ACTUAL_PRORATA_SHARE
,BILLED_RECOVERY = X_BILLED_RECOVERY
,RECONCILED_AMOUNT = X_RECONCILED_AMOUNT
,BUDGET_RECOVERY = X_BUDGET_RECOVERY
,BUDGET_EXPENSE = X_BUDGET_EXPENSE
,REC_CALC_PERIOD_ID = X_REC_CALC_PERIOD_ID
,REC_AGR_LINE_ID = X_REC_AGR_LINE_ID
,AS_OF_DATE = X_AS_OF_DATE
,START_DATE = X_START_DATE
,END_DATE = X_END_DATE
,BILLING_TYPE = X_BILLING_TYPE
,BILLING_PURPOSE = X_BILLING_PURPOSE
,CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID
,REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID
,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
,LAST_UPDATED_BY = X_LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
,FIXED_PCT = X_FIXED_PCT
where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID
;
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_LINES_ROW (-)');
end UPDATE_PERIOD_LINES_ROW;
| DELETE_PERIOD_LINES_ROW
|
| DESCRIPTION
| Delete records in the PN_REC_PERIOD_LINES_ALL table
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete records in the PN_REC_PERIOD_LINES_ALL
|
| MODIFICATION HISTORY
|
| 21-MAY-2003 Daniel Thota o Created
+===========================================================================*/
procedure DELETE_PERIOD_LINES_ROW (
X_REC_PERIOD_LINES_ID in NUMBER
) is
BEGIN
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_LINES_ROW (+)');
delete from PN_REC_PERIOD_LINES_ALL
where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID;
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_LINES_ROW (-)');
end DELETE_PERIOD_LINES_ROW;
| INSERT_PERIOD_BILLREC_ROW
|
| DESCRIPTION
| Create records in the PN_REC_PERIOD_BILL_ALL table
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| OUT:
|
| RETURNS : None
|
| NOTES : Create records in the PN_REC_PERIOD_BILL_ALL
|
| MODIFICATION HISTORY
|
| 21-MAY-2003 Daniel Thota o Created
+===========================================================================*/
procedure INSERT_PERIOD_BILLREC_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2
,X_PERIOD_BILLREC_ID IN OUT NOCOPY NUMBER
,X_REC_AGREEMENT_ID IN NUMBER
,X_REC_AGR_LINE_ID IN NUMBER
,X_REC_CALC_PERIOD_ID IN NUMBER
,X_AMOUNT IN NUMBER
,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
) is
CURSOR C is
SELECT ROWID FROM PN_REC_PERIOD_BILL_ALL
WHERE PERIOD_BILLREC_ID = X_PERIOD_BILLREC_ID;
SELECT org_id
FROM pn_rec_agreements_all
WHERE rec_agreement_id = X_REC_AGREEMENT_ID;
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.INSERT_PERIOD_BILLREC_ROW (+)');
SELECT PN_REC_PERIOD_BILL_S.nextval
INTO X_PERIOD_BILLREC_ID
FROM dual;
INSERT INTO PN_REC_PERIOD_BILL_ALL (
PERIOD_BILLREC_ID
,REC_AGREEMENT_ID
,REC_AGR_LINE_ID
,REC_CALC_PERIOD_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,AMOUNT
,ORG_ID
)
VALUES(
X_PERIOD_BILLREC_ID
,X_REC_AGREEMENT_ID
,X_REC_AGR_LINE_ID
,X_REC_CALC_PERIOD_ID
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_CREATION_DATE
,X_CREATED_BY
,X_LAST_UPDATE_LOGIN
,X_AMOUNT
,l_org_ID
);
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.INSERT_PERIOD_BILLREC_ROW (-)');
END INSERT_PERIOD_BILLREC_ROW;
| UPDATE_PERIOD_BILLREC_ROW
|
| DESCRIPTION
| Update records in the PN_REC_PERIOD_BILL_ALL table
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| OUT:
|
| RETURNS : None
|
| NOTES : Update records in the PN_REC_PERIOD_BILL_ALL
|
| MODIFICATION HISTORY
|
| 21-MAY-2003 Daniel Thota o Created
+===========================================================================*/
procedure UPDATE_PERIOD_BILLREC_ROW (
X_PERIOD_BILLREC_ID in NUMBER
,X_REC_AGREEMENT_ID in NUMBER
,X_REC_AGR_LINE_ID in NUMBER
,X_REC_CALC_PERIOD_ID in NUMBER
,X_AMOUNT in NUMBER
,X_LAST_UPDATE_DATE in DATE
,X_LAST_UPDATED_BY in NUMBER
,X_LAST_UPDATE_LOGIN in NUMBER
) is
BEGIN
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_BILLREC_ROW (+)');
update PN_REC_PERIOD_BILL_ALL set
REC_AGR_LINE_ID = X_REC_AGR_LINE_ID
,REC_AGREEMENT_ID = X_REC_AGREEMENT_ID
,REC_CALC_PERIOD_ID = X_REC_CALC_PERIOD_ID
,AMOUNT = X_AMOUNT
,PERIOD_BILLREC_ID = X_PERIOD_BILLREC_ID
,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
,LAST_UPDATED_BY = X_LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where PERIOD_BILLREC_ID = X_PERIOD_BILLREC_ID
;
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_BILLREC_ROW (-)');
end UPDATE_PERIOD_BILLREC_ROW;
| DELETE_PERIOD_BILLREC_ROW
|
| DESCRIPTION
| Delete records in the PN_REC_PERIOD_BILL_ALL table
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| OUT:
|
| RETURNS : None
|
| NOTES : Delete records in the PN_REC_PERIOD_BILL_ALL
|
| MODIFICATION HISTORY
|
| 21-MAY-2003 Daniel Thota o Created
+===========================================================================*/
procedure DELETE_PERIOD_BILLREC_ROW (
X_PERIOD_BILLREC_ID in NUMBER
) is
BEGIN
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_BILLREC_ROW (+)');
delete from PN_REC_PERIOD_BILL_ALL
where PERIOD_BILLREC_ID = X_PERIOD_BILLREC_ID;
PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_BILLREC_ROW (-)');
end DELETE_PERIOD_BILLREC_ROW;
| 15-Aug-03 Ashish o Bug#3099398 add the alias to the select clause
| in the cursor
| csr_distributions,csr_template and csr_lease_term
| 04-Sep-03 Daniel o assigned l_rec_agr_line_id with p_rec_agr_line_id
| Fix for bug # 3123730,3122264
| 16-JUN-04 abanerje o Modified call to pnt_payment_terms_pkg.insert_row
| to pass term_template_id. Bug 3657130.
| 15-SEP-04 atuppad o In the call pnt_payment_terms_pkg.insert_row,
| corrected the code to copy the payment DFF into
| payment DFF of new IR term and not in AR Projects
| DFF. Bug # 3841542
| 21-APR-05 ftanudja o Added area_type_code, area defaulting. #4324777
| 15-JUL-05 ftanudja o R12 change: add logic for tax_clsfctn_cd. #4495054
| 28-NOV-05 pikhar o fetched org_id using cursor
| 18-JUL-06 sdmahesh o Bug 5332426 Added handling for lazy upgrade
| of Term Templates for E-Tax
| 24-SEP-06 acprakas o Bug#6370014. Modified procedure to set schedule day,
| term start and term end date to 28 if it is more than 28.
+===========================================================================*/
PROCEDURE create_payment_terms(
p_lease_id IN NUMBER
,p_payment_amount IN NUMBER
,p_calc_period_end_date IN DATE
,p_rec_agreement_id IN NUMBER
,p_rec_agr_line_id IN NUMBER
,p_rec_calc_period_id IN NUMBER
,p_location_id IN NUMBER
,p_amount_type IN VARCHAR2
,p_org_id IN NUMBER
,p_billing_type IN VARCHAR2
,p_billing_purpose IN VARCHAR2
,p_customer_id IN NUMBER
,p_cust_site_id IN NUMBER
,p_consolidate IN VARCHAR2
,p_error IN OUT NOCOPY VARCHAR2
,p_error_code IN OUT NOCOPY NUMBER
) IS
l_lease_class_code pn_leases.lease_class_code%TYPE;
SELECT pd.*
FROM pn_distributions_all pd
WHERE pd.term_template_id = p_term_template_id;
SELECT pd.*
FROM pn_distributions_all pd
WHERE pd.payment_term_id = p_term_id;
SELECT ptt.*
FROM pn_term_templates_all ptt,
pn_rec_agreements_all prec
WHERE ptt.term_template_id = prec.term_template_id
AND prec.rec_agreement_id = p_rec_agreement_id;
SELECT ptt.*
FROM pn_term_templates_all ptt,
pn_rec_agreements_all prec
WHERE ptt.term_template_id = prec.term_template_id
AND (ptt.tax_code_id IS NOT NULL OR ptt.tax_group_id IS NOT NULL)
AND ptt.tax_classification_code IS NULL
AND prec.rec_agreement_id = p_rec_agreement_id;
SELECT term.*
FROM pn_payment_terms_all term
WHERE term.lease_id = p_lease_id
AND term.PAYMENT_TERM_TYPE_CODE = p_billing_type
AND term.PAYMENT_PURPOSE_CODE = p_billing_purpose
AND term.RECOVERABLE_FLAG = 'Y'
AND rownum = 1;
SELECT org_id
FROM pn_leases_all
WHERE lease_id = p_lease_id;
SELECT pl.lease_class_code
,pld.lease_change_id
INTO l_lease_class_code
,l_lease_change_id
FROM pn_leases_all pl
,pn_lease_details_all pld
WHERE pl.lease_id = pld.lease_id
AND pld.lease_id = p_lease_id;
PN_REC_CALC_PKG.update_period_billrec_row (
X_PERIOD_BILLREC_ID => l_period_billrec_id
,X_REC_AGREEMENT_ID => p_rec_agreement_id
,X_REC_AGR_LINE_ID => p_rec_agr_line_id
,X_REC_CALC_PERIOD_ID => p_rec_calc_period_id
,X_AMOUNT => p_payment_amount
,X_LAST_UPDATE_DATE => l_creation_date
,X_LAST_UPDATED_BY => l_created_by
,X_LAST_UPDATE_LOGIN => l_created_by
);
l_context := 'Inserting period_billrec ';
pnp_debug_pkg.log ('insert_period_billrec_row - agr id :'||p_rec_agreement_id);
pnp_debug_pkg.log ('insert_period_billrec_row - p_rec_agr_line_id :'||p_rec_agr_line_id);
pnp_debug_pkg.log ('insert_period_billrec_row - p_rec_calc_period_id :'||p_rec_calc_period_id);
pnp_debug_pkg.log ('insert_period_billrec_row - amount :'||l_payment_amount);
PN_REC_CALC_PKG.insert_period_billrec_row (
X_ROWID => l_rowId
,X_PERIOD_BILLREC_ID => l_period_billrec_id
,X_REC_AGREEMENT_ID => p_rec_agreement_id
,X_REC_AGR_LINE_ID => p_rec_agr_line_id
,X_REC_CALC_PERIOD_ID => p_rec_calc_period_id
,X_AMOUNT => l_payment_amount
,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
);
pnt_payment_terms_pkg.insert_row (
x_rowid => l_rowid
,x_payment_term_id => l_payment_term_id
,x_index_period_id => null
,x_index_term_indicator => null
,x_var_rent_inv_id => null
,x_var_rent_type => null
,x_last_update_date => SYSDATE
,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
,x_creation_date => SYSDATE
,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
,x_payment_purpose_code => nvl(p_billing_purpose,rec_template.payment_purpose_code)
,x_payment_term_type_code => nvl(p_billing_type,rec_template.payment_term_type_code)
,x_frequency_code => l_frequency
,x_lease_id => p_lease_id
,x_lease_change_id => l_lease_change_id
,x_start_date => l_term_date --Bug#6370014
,x_end_date => l_term_date --Bug#6370014
,x_set_of_books_id => NVL(rec_template.set_of_books_id,l_set_of_books_id)
--,x_currency_code => NVL(rec_template.currency_code, l_currency_code)
,x_currency_code => g_currency_code
,x_rate => 1 -- not used in application
,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
,x_vendor_id => nvl(rec_template.vendor_id,rec_lease_term.vendor_id)
,x_vendor_site_id => nvl(rec_template.vendor_site_id,rec_lease_term.vendor_site_id)
,x_target_date => NULL
,x_actual_amount => l_payment_amount
,x_estimated_amount => NULL
,x_attribute_category => rec_template.attribute_category
,x_attribute1 => rec_template.attribute1
,x_attribute2 => rec_template.attribute2
,x_attribute3 => rec_template.attribute3
,x_attribute4 => rec_template.attribute4
,x_attribute5 => rec_template.attribute5
,x_attribute6 => rec_template.attribute6
,x_attribute7 => rec_template.attribute7
,x_attribute8 => rec_template.attribute8
,x_attribute9 => rec_template.attribute9
,x_attribute10 => rec_template.attribute10
,x_attribute11 => rec_template.attribute11
,x_attribute12 => rec_template.attribute12
,x_attribute13 => rec_template.attribute13
,x_attribute14 => rec_template.attribute14
,x_attribute15 => rec_template.attribute15
,x_project_attribute_category => rec_lease_term.project_attribute_category
,x_project_attribute1 => rec_lease_term.project_attribute1
,x_project_attribute2 => rec_lease_term.project_attribute2
,x_project_attribute3 => rec_lease_term.project_attribute3
,x_project_attribute4 => rec_lease_term.project_attribute4
,x_project_attribute5 => rec_lease_term.project_attribute5
,x_project_attribute6 => rec_lease_term.project_attribute6
,x_project_attribute7 => rec_lease_term.project_attribute7
,x_project_attribute8 => rec_lease_term.project_attribute8
,x_project_attribute9 => rec_lease_term.project_attribute9
,x_project_attribute10 => rec_lease_term.project_attribute10
,x_project_attribute11 => rec_lease_term.project_attribute11
,x_project_attribute12 => rec_lease_term.project_attribute12
,x_project_attribute13 => rec_lease_term.project_attribute13
,x_project_attribute14 => rec_lease_term.project_attribute14
,x_project_attribute15 => rec_lease_term.project_attribute15
,x_customer_id => p_customer_id
,x_customer_site_use_id => p_cust_site_id
,x_normalize => 'N'
,x_location_id => p_location_id
,x_schedule_day => l_schedule_day
,x_cust_ship_site_id => nvl(rec_template.cust_ship_site_id,rec_lease_term.cust_ship_site_id)
,x_ap_ar_term_id => nvl(rec_template.ap_ar_term_id,rec_lease_term.ap_ar_term_id)
,x_cust_trx_type_id => nvl(rec_template.cust_trx_type_id,rec_lease_term.cust_trx_type_id)
,x_project_id => nvl(rec_template.project_id,rec_lease_term.project_id)
,x_task_id => nvl(rec_template.task_id,rec_lease_term.task_id)
,x_organization_id => nvl(rec_template.organization_id,rec_lease_term.organization_id)
,x_expenditure_type => nvl(rec_template.expenditure_type,rec_lease_term.expenditure_type)
,x_expenditure_item_date => nvl(rec_template.expenditure_item_date,rec_lease_term.expenditure_item_date)
,x_tax_group_id => nvl(rec_template.tax_group_id,rec_lease_term.tax_group_id)
,x_tax_code_id => nvl(rec_template.tax_code_id,rec_lease_term.tax_code_id)
,x_tax_classification_code => nvl(rec_template.tax_classification_code,rec_lease_term.tax_classification_code)
,x_tax_included => nvl(rec_template.tax_included,rec_lease_term.tax_included)
,x_distribution_set_id => nvl(rec_template.distribution_set_id,rec_lease_term.distribution_set_id)
,x_inv_rule_id => nvl(rec_template.inv_rule_id,rec_lease_term.inv_rule_id)
,x_account_rule_id => nvl(rec_template.account_rule_id,rec_lease_term.account_rule_id)
,x_salesrep_id => nvl(rec_template.salesrep_id,rec_lease_term.salesrep_id)
,x_approved_by => NULL
,x_status => 'DRAFT'
,x_po_header_id => nvl(rec_template.po_header_id,rec_lease_term.po_header_id)
,x_cust_po_number => nvl(rec_template.cust_po_number,rec_lease_term.cust_po_number)
,x_receipt_method_id => nvl(rec_template.receipt_method_id,rec_lease_term.receipt_method_id)
,x_calling_form => 'PNRECALB'
,x_org_id => l_org_id
,x_period_billrec_id => l_period_billrec_id
,x_rec_agr_line_id => l_rec_agr_line_id
,x_amount_type => 'CAM'
,x_recoverable_flag => NULL
,x_term_template_id => rec_template.term_template_id
,x_area => l_area
,x_area_type_code => l_area_type_code
);
l_context := 'Inserting Account Distributions ';
pn_distributions_pkg.insert_row (
x_rowid => l_rowid
,x_distribution_id => l_distribution_id
,x_account_id => rec_distributions.account_id
,x_payment_term_id => l_payment_term_id
,x_term_template_id => NULL
,x_account_class => rec_distributions.account_class
,x_percentage => rec_distributions.percentage
,x_line_number => rec_distributions.line_number
,x_last_update_date => SYSDATE
,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
,x_creation_date => SYSDATE
,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
,x_attribute_category => rec_distributions.attribute_category
,x_attribute1 => rec_distributions.attribute1
,x_attribute2 => rec_distributions.attribute2
,x_attribute3 => rec_distributions.attribute3
,x_attribute4 => rec_distributions.attribute4
,x_attribute5 => rec_distributions.attribute5
,x_attribute6 => rec_distributions.attribute6
,x_attribute7 => rec_distributions.attribute7
,x_attribute8 => rec_distributions.attribute8
,x_attribute9 => rec_distributions.attribute9
,x_attribute10 => rec_distributions.attribute10
,x_attribute11 => rec_distributions.attribute11
,x_attribute12 => rec_distributions.attribute12
,x_attribute13 => rec_distributions.attribute13
,x_attribute14 => rec_distributions.attribute14
,x_attribute15 => rec_distributions.attribute15
,x_org_id => l_org_id
);
| 05-SEP-2003 Daniel Thota o Added code to delete from pn_distributions_all,
| before deleting from pn_payment_terms_all
| 17-SEP-2003 Daniel Thota o Put in cursors get_distributions_exist_nocons
| and cursors get_distributions_exist_cons
| Fix for bug # 3142328
+===========================================================================*/
FUNCTION find_if_rec_payterm_exists(
p_rec_agreement_id PN_REC_PERIOD_BILL_all.period_billrec_id%TYPE
,p_rec_agr_line_id PN_REC_PERIOD_BILL_all.rec_agr_line_id%TYPE
,p_rec_calc_period_id PN_REC_PERIOD_BILL_all.rec_calc_period_id%TYPE
,p_consolidate VARCHAR2
)
RETURN period_bill_record IS
l_period_bill_record period_bill_record;
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM pn_distributions_all dist
,pn_payment_terms_all term
WHERE term.payment_term_id = dist.payment_term_id
AND term.period_billrec_id = l_period_bill_record.period_billrec_id
AND term.rec_agr_line_id = p_rec_agr_line_id
AND term.status = 'DRAFT')
;
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 1
FROM pn_distributions_all dist
,pn_payment_terms_all term
WHERE term.payment_term_id = dist.payment_term_id
AND term.period_billrec_id = l_period_bill_record.period_billrec_id
AND term.status = 'DRAFT')
;
l_context := 'selecting billed amount record';
SELECT period_billrec_id
INTO l_period_bill_record.period_billrec_id
FROM PN_REC_PERIOD_BILL_all
WHERE rec_agreement_id = p_rec_agreement_id
AND rec_agr_line_id = p_rec_agr_line_id
AND rec_calc_period_id = p_rec_calc_period_id;
SELECT period_billrec_id
INTO l_period_bill_record.period_billrec_id
FROM PN_REC_PERIOD_BILL_all
WHERE rec_agreement_id = p_rec_agreement_id
AND rec_calc_period_id = p_rec_calc_period_id;
SELECT NVL(SUM(actual_amount),0)
INTO l_period_bill_record.amount
FROM pn_payment_terms_all
WHERE period_billrec_id = l_period_bill_record.period_billrec_id
AND rec_agr_line_id = p_rec_agr_line_id
AND status = 'APPROVED';
SELECT NVL(SUM(actual_amount),0)
INTO l_period_bill_record.amount
FROM pn_payment_terms_all
WHERE period_billrec_id = l_period_bill_record.period_billrec_id
AND status = 'APPROVED';
DELETE pn_distributions_all
WHERE payment_term_id in (SELECT payment_term_id
FROM pn_payment_terms_all
WHERE period_billrec_id = l_period_bill_record.period_billrec_id
AND rec_agr_line_id = p_rec_agr_line_id
AND status = 'DRAFT')
;
DELETE pn_payment_terms_all
WHERE period_billrec_id = l_period_bill_record.period_billrec_id
AND rec_agr_line_id = p_rec_agr_line_id
AND status = 'DRAFT';
DELETE pn_distributions_all
WHERE payment_term_id in (SELECT payment_term_id
FROM pn_payment_terms_all
WHERE period_billrec_id = l_period_bill_record.period_billrec_id
AND status = 'DRAFT')
;
DELETE pn_payment_terms_all
WHERE period_billrec_id = l_period_bill_record.period_billrec_id
AND status = 'DRAFT';
pnp_debug_pkg.log('find_if_rec_payterm_exists - terms deleted '|| to_char(SQL%ROWCOUNT));
SELECT lineconst.VALUE
FROM pn_rec_agr_linconst_all lineconst
WHERE lineconst.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between lineconst.start_date and lineconst.end_date
AND lineconst.RELATION = 'MAX'
AND lineconst.SCOPE = 'OPYA';
SELECT NVL(period_lines.constrained_actual,0)
INTO l_prior_period_amount
FROM pn_rec_period_lines_all period_lines
WHERE rec_agr_line_id = p_rec_agr_line_id
AND end_date = (SELECT max(end_date)
FROM pn_rec_period_lines_all
WHERE start_date < p_start_date
AND end_date < p_start_date
AND rec_agr_line_id = p_rec_agr_line_id) ;
SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
FROM pn_rec_period_lines_all period_lines
WHERE rec_agr_line_id = p_rec_agr_line_id
AND start_date = (select min(start_date)
from pn_rec_period_lines_all
WHERE rec_agr_line_id = p_rec_agr_line_id) ;
SELECT lineconst.VALUE
FROM pn_rec_period_lines_all period_lines,
pn_rec_agr_linconst_all lineconst,
pn_rec_calc_periods_all recperiod
WHERE period_lines.rec_agr_line_id = p_rec_agr_line_id
AND recperiod.rec_calc_period_id = period_lines.rec_calc_period_id
AND recperiod.start_date > p_fst_end_date
AND recperiod.end_date > p_fst_end_date
AND recperiod.start_date < p_start_date
AND recperiod.end_date < p_start_date
AND lineconst.rec_agr_line_id = period_lines.rec_agr_line_id
AND recperiod.as_of_date between lineconst.start_date and lineconst.end_date
AND lineconst.RELATION = 'MAX'
AND lineconst.SCOPE = 'OPYC';
SELECT lineconst.VALUE
FROM pn_rec_agr_linconst_all lineconst
WHERE lineconst.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between lineconst.start_date and lineconst.end_date
AND lineconst.RELATION = 'MAX'
AND lineconst.SCOPE = 'OPYC';
select term.period_billrec_id,
term.rec_agr_line_id
from pn_payment_terms_all term
where term.payment_term_id = p_payment_term_id;
select
agr.customer_id as customer_id,
agr.lease_id as lease_id,
agr.location_id as location_id,
period.start_date as start_date,
period.end_date as end_date ,
period.as_of_date as as_of_date ,
agrlines.rec_agr_line_id as rec_agr_line_id
from pn_payment_terms_all term
,pn_rec_period_bill_all bill
,pn_rec_calc_periods_all period
,pn_rec_agr_lines_all agrlines
,pn_rec_agreements_all agr
where term.payment_term_id = p_payment_term_id
and bill.period_billrec_id = term.period_billrec_id
and period.rec_calc_period_id = bill.rec_calc_period_id
and agrlines.rec_agreement_id = bill.rec_agreement_id
and period.as_of_date between agrlines.start_date and agrlines.end_date
and agr.rec_agreement_id = agrlines.rec_agreement_id
;
select
agr.customer_id as customer_id,
agr.lease_id as lease_id,
agr.location_id as location_id,
period.start_date as start_date,
period.end_date as end_date ,
period.as_of_date as as_of_date ,
agrlines.rec_agr_line_id as rec_agr_line_id
from pn_payment_terms_all term
,pn_rec_period_bill_all bill
,pn_rec_calc_periods_all period
,pn_rec_agr_lines_all agrlines
,pn_rec_agreements_all agr
where term.payment_term_id = p_payment_term_id
and bill.period_billrec_id = term.period_billrec_id
and period.rec_calc_period_id = bill.rec_calc_period_id
and agrlines.rec_agreement_id = bill.rec_agreement_id
and agrlines.rec_agr_line_id = term.rec_agr_line_id
and period.as_of_date between agrlines.start_date and agrlines.end_date
and agr.rec_agreement_id = agrlines.rec_agreement_id
;
SELECT
exp_detail_hdr.expense_class_dtl_id as expense_class_dtl_id
FROM pn_rec_expcl_all rec_exp_class
,pn_rec_agr_linexp_all lineexp
,pn_rec_expcl_dtl_all exp_detail_hdr
,pn_rec_exp_line_all exp_extract_hdr
,pn_rec_expcl_dtlln_all exp_detail_line
WHERE exp_detail_hdr.expense_class_dtl_id = exp_detail_line.expense_class_dtl_id
AND exp_detail_line.cust_account_id = p_customer_id
AND exp_detail_line.lease_id = p_lease_id
AND exp_detail_line.location_id = p_location_id
AND exp_extract_hdr.to_date = p_calc_period_end_date
AND exp_extract_hdr.from_date = p_calc_period_start_date
AND exp_extract_hdr.as_of_date = p_calc_period_as_of_date
AND exp_extract_hdr.expense_line_id = exp_detail_hdr.expense_line_id
AND exp_detail_hdr.expense_class_id = rec_exp_class.expense_class_id
AND rec_exp_class.expense_class_id = lineexp.expense_class_id
AND p_calc_period_as_of_date between lineexp.start_date and lineexp.end_date
AND lineexp.rec_agr_line_id = p_rec_agr_line_id
;
SELECT
area_class_dtl_hdr.area_class_dtl_id as area_class_dtl_id
FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
,pn_rec_arcl_dtl_all area_class_dtl_hdr
,pn_rec_agr_linarea_all linearea
,pn_rec_arcl_all aclass
WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
AND p_as_of_date between linearea.start_date and linearea.end_date
AND aclass.area_class_id = linearea.area_class_id
AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
AND area_class_dtl_hdr.as_of_date = p_as_of_date
AND area_class_dtl_hdr.from_date = p_calc_period_start_date
AND area_class_dtl_hdr.to_date = p_calc_period_end_date
AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
AND area_class_dtl_lines.cust_account_id = p_customer_id
AND area_class_dtl_lines.lease_id = p_lease_id
AND area_class_dtl_lines.location_id = p_location_id
;
update pn_rec_expcl_dtl_all
set status = 'LOCKED',
last_update_date = sysdate,
last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
where
expense_class_dtl_id = l_exp_class_detail_rec.expense_class_dtl_id;
update pn_rec_arcl_dtl_all
set status = 'LOCKED' ,
last_update_date = sysdate,
last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
where
area_class_dtl_id = l_area_class_detail_rec.area_class_dtl_id;
| 30-Dec-2008 acprakas o Bug#7645185. Modified the insert stmt to populate org_id also.
+===========================================================================*/
FUNCTION validate_create_calc_period(p_rec_agreement_id pn_rec_agreements_all.REC_AGREEMENT_ID%TYPE,
p_start_date pn_rec_calc_periods_all.start_date%TYPE,
p_end_date pn_rec_calc_periods_all.end_date%TYPE,
p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE)
RETURN NUMBER
IS
l_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE;
SELECT count(1)
INTO l_period_count
FROM pn_rec_calc_periods_all
WHERE rec_agreement_id = p_rec_agreement_id
AND start_date = p_start_date
AND end_date = p_end_date
AND as_of_date = p_as_of_date;
select pn_rec_calc_periods_s.nextval
into l_rec_calc_period_id
from dual;
insert into pn_rec_calc_periods_all
(rec_calc_period_id,
REC_AGREEMENT_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
start_date,
end_date,
as_of_date,
org_id
)
values
(l_rec_calc_period_id,
p_rec_agreement_id,
sysdate,
TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
sysdate,
TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
p_start_date,
p_end_date,
p_as_of_date,
l_org_id
);