[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_date_table.delete;
p_number_table.delete;
PROCEDURE insert_vacancy_data(
p_location_id pn_locations.location_id%TYPE,
p_property_id pn_locations.property_id%TYPE,
p_date_table date_table_type,
p_num_table number_table_type,
p_ref_date_table date_table_type,
p_ref_num_table number_table_type,
p_from_date DATE,
p_to_date DATE,
p_as_of_date DATE,
p_assignable_area NUMBER,
p_curnt_ovr area_cls_line_dtl_tbl,
p_prior_ovr area_cls_line_dtl_tbl,
p_data_tbl IN OUT NOCOPY area_cls_line_dtl_tbl,
p_total_tbl IN OUT NOCOPY area_cls_line_hdr_tbl,
p_keep_override VARCHAR2,
p_regenerate VARCHAR2
)
IS
l_counter NUMBER;
l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.insert_vacancy_data' ;
delete_table number_table_type;
l_info := ' inserting row into area cls dtl line table ';
pn_rec_arcl_dtlln_pkg.insert_row(
x_org_id => pn_mo_cache_utils.get_current_org_id,
x_area_class_dtl_id => p_hdr_id,
x_area_class_dtl_line_id => l_area_cls_dtl_line_id,
x_from_date => p_new_data(i).from_date,
x_to_date => p_new_data(i).to_date,
x_location_id => p_new_data(i).location_id,
x_property_id => p_new_data(i).property_id,
x_cust_space_assign_id => p_new_data(i).cust_space_assign_id,
x_cust_account_id => p_new_data(i).cust_account_id,
x_lease_id => p_new_data(i).lease_id,
x_assignable_area => p_new_data(i).assignable_area,
x_assigned_area => p_new_data(i).assigned_area,
x_assigned_area_ovr => p_new_data(i).assigned_area_ovr,
x_occupancy_pct => p_new_data(i).occupancy_pct,
x_occupied_area => p_new_data(i).occupied_area,
x_occupied_area_ovr => p_new_data(i).occupied_area_ovr,
x_vacant_area => p_new_data(i).vacant_area,
x_vacant_area_ovr => p_new_data(i).vacant_area_ovr,
x_weighted_avg => p_new_data(i).weighted_avg,
x_weighted_avg_ovr => p_new_data(i).weighted_avg_ovr,
x_exclude_area_flag => p_new_data(i).exclude_area_flag,
x_exclude_area_ovr_flag => p_new_data(i).exclude_area_ovr_flag,
x_exclude_prorata_flag => p_new_data(i).exclude_prorata_flag,
x_exclude_prorata_ovr_flag => p_new_data(i).exclude_prorata_ovr_flag,
x_include_flag => p_new_data(i).include_flag,
x_recovery_space_std_code => p_new_data(i).recovery_space_std_code,
x_recovery_type_code => p_new_data(i).recovery_type_code,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
);
pn_rec_arcl_dtlln_pkg.update_row(
x_area_class_dtl_line_id => l_area_cls_dtl_line_id,
x_from_date => p_new_data(i).from_date,
x_to_date => p_new_data(i).to_date,
x_location_id => p_new_data(i).location_id,
x_property_id => p_new_data(i).property_id,
x_cust_space_assign_id => p_new_data(i).cust_space_assign_id,
x_cust_account_id => p_new_data(i).cust_account_id,
x_lease_id => p_new_data(i).lease_id,
x_assignable_area => p_new_data(i).assignable_area,
x_assigned_area => p_new_data(i).assigned_area,
x_assigned_area_ovr => p_new_data(i).assigned_area_ovr,
x_occupancy_pct => p_new_data(i).occupancy_pct,
x_occupied_area => p_new_data(i).occupied_area,
x_occupied_area_ovr => p_new_data(i).occupied_area_ovr,
x_vacant_area => p_new_data(i).vacant_area,
x_vacant_area_ovr => p_new_data(i).vacant_area_ovr,
x_weighted_avg => p_new_data(i).weighted_avg,
x_weighted_avg_ovr => p_new_data(i).weighted_avg_ovr,
x_exclude_area_flag => p_new_data(i).exclude_area_flag,
x_exclude_area_ovr_flag => p_new_data(i).exclude_area_ovr_flag,
x_exclude_prorata_flag => p_new_data(i).exclude_prorata_flag,
x_exclude_prorata_ovr_flag => p_new_data(i).exclude_prorata_ovr_flag,
x_include_flag => p_new_data(i).include_flag,
x_recovery_space_std_code => p_new_data(i).recovery_space_std_code,
x_recovery_type_code => p_new_data(i).recovery_type_code,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
);
IF NOT l_is_in THEN delete_table(delete_table.COUNT) := p_old_data(i).area_class_dtl_line_id; END IF;
FORALL i IN 0 .. delete_table.COUNT - 1
DELETE FROM pn_rec_arcl_dtlln_all
WHERE area_class_dtl_line_id = delete_table(i);
SELECT class.area_class_id,
class.property_id,
class.location_id,
excl_dtl.exclusion_type_code,
excl_dtl.relational_code,
excl_dtl.area,
excl_dtl.area_class_exclusion_id,
excl_dtl.recovery_space_std_code,
excl_dtl.recovery_type_code
FROM pn_rec_arcl_all class,
pn_rec_arcl_exc_all excl_dtl
WHERE class.area_class_id = excl_dtl.area_class_id (+)
AND class.area_class_id = p_area_class_id;
SELECT location_id,
property_id,
active_start_date,
active_end_date,
assignable_area
FROM pn_locations_all
WHERE location_type_lookup_code IN ('SECTION','OFFICE')
AND active_start_date < fnd_date.canonical_to_date(p_to_date)
AND active_end_date > fnd_date.canonical_to_date(p_from_date)
AND location_id IN
(SELECT location_id FROM pn_locations_all
START WITH (location_id = p_location_id OR
(property_id = p_property_id AND p_location_id IS NULL))
CONNECT BY PRIOR location_id = parent_location_id)
ORDER BY location_id;
SELECT cust_space_assign_id,
cust_account_id,
allocated_area,
cust_assign_start_date,
fin_oblig_end_date,
lease_id,
recovery_type_code,
recovery_space_std_code
FROM pn_space_assign_cust_all cust
WHERE cust.location_id = p_location_id
AND cust.cust_assign_start_date < fnd_date.canonical_to_date(p_to_date)
AND cust.fin_oblig_end_date > fnd_date.canonical_to_date(p_from_date);
SELECT uom_code
FROM pn_locations_all
WHERE property_id = p_property_id
AND active_start_date < fnd_date.canonical_to_date(p_to_date)
AND active_end_date > fnd_date.canonical_to_date(p_from_date);
SELECT uom_code
FROM pn_locations_all
WHERE location_id IN
(SELECT location_id FROM pn_locations_all
START WITH location_id = p_location_id
CONNECT BY location_id = PRIOR parent_location_id)
AND parent_location_id IS NULL
AND active_start_date < fnd_date.canonical_to_date(p_to_date)
AND active_end_date > fnd_date.canonical_to_date(p_from_date);
SELECT dtl.location_id,
dtl.from_date,
dtl.to_date,
dtl.cust_account_id,
dtl.weighted_avg_ovr,
dtl.occupied_area_ovr,
dtl.assigned_area_ovr,
dtl.exclude_prorata_flag,
dtl.exclude_prorata_ovr_flag,
dtl.exclude_area_flag,
dtl.exclude_area_ovr_flag,
dtl.recovery_space_std_code,
dtl.recovery_type_code,
dtl.area_class_dtl_line_id,
hdr.area_class_dtl_id,
hdr.status,
setup.area_class_name
FROM pn_rec_arcl_dtlln_all dtl,
pn_rec_arcl_dtl_all hdr,
pn_rec_arcl_all setup
WHERE dtl.area_class_dtl_id (+) = hdr.area_class_dtl_id
AND hdr.area_class_id = p_area_class_id
AND TRUNC(hdr.as_of_date) = TRUNC(fnd_date.canonical_to_date(p_as_of_date))
AND TRUNC(hdr.from_date) = TRUNC(fnd_date.canonical_to_date(p_from_date))
AND TRUNC(hdr.to_date) = TRUNC(fnd_date.canonical_to_date(p_to_date))
AND setup.area_class_id = hdr.area_class_id;
SELECT area_class_dtl_id
FROM pn_rec_arcl_dtl_all
WHERE from_date < fnd_date.canonical_to_date(p_to_date)
AND to_date <= fnd_date.canonical_to_date(p_to_date)
AND as_of_date < fnd_date.canonical_to_date(p_as_of_date)
AND area_class_id = p_area_class_id
ORDER BY as_of_date DESC, to_date DESC , from_date DESC;
SELECT location_id,
from_date,
to_date,
cust_account_id,
weighted_avg_ovr,
occupied_area_ovr,
assigned_area_ovr,
exclude_prorata_flag,
exclude_prorata_ovr_flag,
exclude_area_flag,
exclude_area_ovr_flag,
recovery_space_std_code,
recovery_type_code
FROM pn_rec_arcl_dtlln_all
WHERE area_class_dtl_id = p_prior_cls_dtl_id;
l_vacancy_date_table.delete;
l_vacancy_num_table.delete;
l_ref_vacancy_date_table.delete;
l_ref_vacancy_num_table.delete;
l_area_cls_ln_curnt_ovr.delete;
l_area_cls_ln_prior_ovr.delete;
l_area_cls_ln_data_tbl.delete;
l_arcl_exc_table.delete;
l_info := ' inserting vacancy data into details table for location: '||l_temp_loc_id||' ';
insert_vacancy_data(p_location_id => l_temp_loc_id,
p_property_id => l_temp_prop_id,
p_date_table => l_vacancy_date_table,
p_num_table => l_vacancy_num_table,
p_ref_date_table => l_ref_vacancy_date_table,
p_ref_num_table => l_ref_vacancy_num_table,
p_from_date => fnd_date.canonical_to_date(p_from_date),
p_to_date => fnd_date.canonical_to_date(p_to_date),
p_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
p_assignable_area => l_temp_assignable_area,
p_curnt_ovr => l_area_cls_ln_curnt_ovr,
p_prior_ovr => l_area_cls_ln_prior_ovr,
p_data_tbl => l_area_cls_ln_data_tbl,
p_total_tbl => l_area_total_tbl,
p_keep_override => p_keep_override,
p_regenerate => l_regenerate
);
l_vacancy_num_table.delete;
l_vacancy_date_table.delete;
l_ref_vacancy_num_table.delete;
l_ref_vacancy_date_table.delete;
l_info := ' inserting vacancy data for last location id';
insert_vacancy_data(
p_location_id => l_temp_loc_id,
p_property_id => l_temp_prop_id,
p_date_table => l_vacancy_date_table,
p_num_table => l_vacancy_num_table,
p_ref_date_table => l_ref_vacancy_date_table,
p_ref_num_table => l_ref_vacancy_num_table,
p_from_date => fnd_date.canonical_to_date(p_from_date),
p_to_date => fnd_date.canonical_to_date(p_to_date),
p_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
p_assignable_area => l_temp_assignable_area,
p_curnt_ovr => l_area_cls_ln_curnt_ovr,
p_prior_ovr => l_area_cls_ln_prior_ovr,
p_data_tbl => l_area_cls_ln_data_tbl,
p_total_tbl => l_area_total_tbl,
p_keep_override => p_keep_override,
p_regenerate => l_regenerate
);
l_vacancy_date_table.delete;
l_vacancy_num_table.delete;
l_ref_vacancy_date_table.delete;
l_ref_vacancy_num_table.delete;
pn_rec_arcl_dtl_pkg.update_row(
x_area_class_id => p_area_class_id,
x_area_class_dtl_id => l_area_class_dtl_id,
x_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
x_from_date => fnd_date.canonical_to_date(p_from_date),
x_to_date => fnd_date.canonical_to_date(p_to_date),
x_status => 'OPEN',
x_ttl_assignable_area => l_area_total_tbl(0).total_assignable_area,
x_ttl_occupied_area => l_area_total_tbl(0).total_occupied_area,
x_ttl_occupied_area_ovr => l_area_total_tbl(0).total_occupied_area_ovr,
x_ttl_occupied_area_exc => l_area_total_tbl(0).total_occupied_area_exc,
x_ttl_vacant_area => l_area_total_tbl(0).total_vacant_area,
x_ttl_vacant_area_ovr => l_area_total_tbl(0).total_vacant_area_ovr,
x_ttl_vacant_area_exc => l_area_total_tbl(0).total_vacant_area_exc,
x_ttl_weighted_avg => l_area_total_tbl(0).total_weighted_avg,
x_ttl_weighted_avg_ovr => l_area_total_tbl(0).total_weighted_avg_ovr,
x_ttl_weighted_avg_exc => l_area_total_tbl(0).total_weighted_avg_exc,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
);
pn_rec_arcl_dtl_pkg.insert_row(
x_org_id => pn_mo_cache_utils.get_current_org_id,
x_area_class_id => p_area_class_id,
x_area_class_dtl_id => l_area_class_dtl_id,
x_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
x_from_date => fnd_date.canonical_to_date(p_from_date),
x_to_date => fnd_date.canonical_to_date(p_to_date),
x_status => 'OPEN',
x_ttl_assignable_area => l_area_total_tbl(0).total_assignable_area,
x_ttl_occupied_area => l_area_total_tbl(0).total_occupied_area,
x_ttl_occupied_area_ovr => l_area_total_tbl(0).total_occupied_area_ovr,
x_ttl_occupied_area_exc => l_area_total_tbl(0).total_occupied_area_exc,
x_ttl_vacant_area => l_area_total_tbl(0).total_vacant_area,
x_ttl_vacant_area_ovr => l_area_total_tbl(0).total_vacant_area_ovr,
x_ttl_vacant_area_exc => l_area_total_tbl(0).total_vacant_area_exc,
x_ttl_weighted_avg => l_area_total_tbl(0).total_weighted_avg,
x_ttl_weighted_avg_ovr => l_area_total_tbl(0).total_weighted_avg_ovr,
x_ttl_weighted_avg_exc => l_area_total_tbl(0).total_weighted_avg_exc,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
);
SELECT 'TRUE' FROM pn_rec_exp_line
WHERE ((p_from_date BETWEEN from_date AND to_date) OR
(p_to_date BETWEEN from_date AND to_date))
AND (((location_id = p_location_id) OR (location_id IS NULL AND p_location_id IS NULL)) OR
(((property_id = p_property_id) OR (property_id IS NULL AND p_property_id IS NULL))
AND location_id IS NULL))
AND p_extract_code IS NOT NULL
AND expense_extract_code <> p_extract_code
AND rownum < 2;
SELECT 'TRUE' FROM pn_locations_all WHERE location_id = p_location_id;
SELECT 'TRUE' FROM pn_properties_all WHERE property_id = p_property_id;
SELECT 'TRUE' FROM pn_locations_all WHERE location_id = p_location_id AND property_id = p_property_id; */
SELECT 'TRUE' FROM pn_locations_all
WHERE property_id = p_property_id
START WITH location_id = p_location_id
CONNECT BY PRIOR parent_location_id = location_id;
SELECT 'TRUE' FROM fnd_lookups
WHERE lookup_type = 'PN_PAYMENT_PURPOSE_TYPE' and lookup_code = p_exp_type_code;
SELECT location_id,
property_id,
from_date,
to_date,
as_of_date,
currency_code,
org_id
FROM pn_rec_exp_line_all
WHERE expense_extract_code = p_extract_code;
SELECT 'TRUE' FROM gl_code_combinations where code_combination_id = p_cc_id;
SELECT 'EXISTS'
FROM pn_rec_exp_line_dtl dtl,
pn_rec_exp_line hdr
WHERE dtl.expense_line_id = hdr.expense_line_id
AND hdr.from_date = p_from_date
AND hdr.to_date = p_to_date
AND dtl.expense_type_code = p_exp_type_code
AND dtl.expense_account_id = p_cc_id;
l_info:= ' inserting expense line header';
pn_rec_exp_line_pkg.insert_row(
x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
x_expense_line_id => p_expense_line_id,
x_expense_extract_code => p_extract_code,
x_currency_code => p_currency_code,
x_as_of_date => p_as_of_date,
x_from_date => p_from_date,
x_to_date => p_to_date,
x_location_id => p_location_id,
x_property_id => p_property_id,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
);
l_info:= ' inserting expense line detail for header id: '||p_expense_line_id;
pn_rec_exp_line_dtl_pkg.insert_row(
x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
x_expense_line_id => p_expense_line_id,
x_expense_line_dtl_id => p_expense_line_dtl_id,
x_parent_expense_line_id => null,
x_property_id => p_property_id,
x_location_id => p_location_id,
x_expense_type_code => p_expense_type_code,
x_expense_account_id => p_expense_account_id,
x_account_description => p_account_description,
x_actual_amount => p_actual_amount,
x_actual_amount_ovr => null,
x_budgeted_amount => p_budgeted_amount,
x_budgeted_amount_ovr => null,
x_budgeted_pct => null,
x_actual_pct => null,
x_currency_code => p_currency_code,
x_recoverable_flag => 'Y',
x_expense_line_indicator => 'NEUTRAL',
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1),
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15
);
SELECT expense_type_code,
expense_account_id,
account_description,
actual_amount,
budgeted_amount,
currency_code,
location_id,
property_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM pn_rec_exp_itf
WHERE transfer_flag = 'N'
AND from_date = p_from_date
AND to_date = p_to_date
AND currency_code = p_currency_code
AND (location_id IN
(SELECT location_id FROM pn_locations_all
WHERE active_start_date < p_to_date
AND active_end_date > p_from_date
START WITH (location_id = p_location_id OR
(property_id = p_property_id AND p_location_id IS NULL))
CONNECT BY PRIOR location_id = parent_location_id)
OR
(property_id = p_property_id AND p_location_id IS NULL))
AND org_id = pn_mo_cache_utils.get_current_org_id
FOR UPDATE OF transfer_flag, expense_line_dtl_id NOWAIT;
SELECT expense_line_id
FROM pn_rec_exp_line_all hdr
WHERE hdr.expense_extract_code = p_extract_code
AND p_extract_code IS NOT NULL
AND rownum < 2;
UPDATE pn_rec_exp_itf
SET transfer_flag = l_transfer_flag,
expense_line_dtl_id = l_expense_line_dtl_id
WHERE CURRENT OF get_itf_lines_info;
l_info:= ' inserting into details pl/sql table ';
l_master_delete_table number_table_type;
l_detail_delete_table number_table_type;
l_info:= ' inserting data into class lines master table';
pn_rec_expcl_dtlln_pkg.insert_row(
x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
x_expense_class_dtl_id => p_master_data(i).expense_class_dtl_id,
x_expense_class_line_id => l_expense_class_line_id,
x_location_id => p_master_data(i).location_id,
x_cust_space_assign_id => p_master_data(i).cust_space_assign_id,
x_cust_account_id => p_master_data(i).cust_account_id,
x_lease_id => p_master_data(i).lease_id,
x_recovery_space_std_code => p_master_data(i).recovery_space_std_code,
x_recovery_type_code => p_master_data(i).recovery_type_code,
x_budgeted_amt => p_master_data(i).budgeted_amt,
x_expense_amt => p_master_data(i).expense_amt,
x_recoverable_amt => p_master_data(i).recoverable_amt,
x_computed_recoverable_amt => p_master_data(i).computed_recoverable_amt,
x_cls_line_share_pct => p_master_data(i).cls_line_share_pct,
x_cls_line_fee_bf_ct_ovr => p_master_data(i).cls_line_fee_before_contr_ovr,
x_cls_line_fee_af_ct_ovr => p_master_data(i).cls_line_fee_after_contr_ovr,
x_use_share_pct_flag => l_use_share_pct_flag,
x_use_fee_before_contr_flag => l_use_fee_pct_flag,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
);
pn_rec_expcl_dtlln_pkg.update_row(
x_expense_class_line_id => l_expense_class_line_id,
x_location_id => p_master_data(i).location_id,
x_cust_space_assign_id => p_master_data(i).cust_space_assign_id,
x_cust_account_id => p_master_data(i).cust_account_id,
x_lease_id => p_master_data(i).lease_id,
x_recovery_space_std_code => p_master_data(i).recovery_space_std_code,
x_recovery_type_code => p_master_data(i).recovery_type_code,
x_budgeted_amt => p_master_data(i).budgeted_amt,
x_expense_amt => p_master_data(i).expense_amt,
x_recoverable_amt => p_master_data(i).recoverable_amt,
x_computed_recoverable_amt => p_master_data(i).computed_recoverable_amt,
x_cls_line_share_pct => p_master_data(i).cls_line_share_pct,
x_cls_line_fee_bf_ct_ovr => p_master_data(i).cls_line_fee_before_contr_ovr,
x_cls_line_fee_af_ct_ovr => p_master_data(i).cls_line_fee_after_contr_ovr,
x_use_share_pct_flag => l_use_share_pct_flag,
x_use_fee_before_contr_flag => l_use_fee_pct_flag,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
);
l_info := ' inserting detail data for class line header: '||
p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id;
pn_rec_expcl_dtlacc_pkg.insert_row(
x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
x_expense_class_line_id => p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id,
x_expense_class_line_dtl_id => l_expense_class_line_dtl_id,
x_expense_line_dtl_id => p_detail_data(i).expense_line_dtl_id,
x_expense_account_id => p_detail_data(i).expense_account_id,
x_expense_type_code => p_detail_data(i).expense_type_code,
x_cls_line_dtl_share_pct => p_detail_data(i).cls_line_dtl_share_pct,
x_cls_line_dtl_share_pct_ovr => p_detail_data(i).cls_line_dtl_share_pct_ovr,
x_cls_line_dtl_fee_bf_ct => p_detail_data(i).cls_line_dtl_fee_bf_contr,
x_cls_line_dtl_fee_bf_ct_ovr => p_detail_data(i).cls_line_dtl_fee_bf_contr_ovr,
x_expense_amt => p_detail_data(i).expense_amt,
x_budgeted_amt => p_detail_data(i).budgeted_amt,
x_recoverable_amt => p_detail_data(i).recoverable_amt,
x_computed_recoverable_amt => p_detail_data(i).computed_recoverable_amt,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
);
pn_rec_expcl_dtlacc_pkg.update_row(
x_expense_class_line_dtl_id => l_expense_class_line_dtl_id,
x_expense_line_dtl_id => p_detail_data(i).expense_line_dtl_id,
x_expense_account_id => p_detail_data(i).expense_account_id,
x_expense_type_code => p_detail_data(i).expense_type_code,
x_cls_line_dtl_share_pct => p_detail_data(i).cls_line_dtl_share_pct,
x_cls_line_dtl_share_pct_ovr => p_detail_data(i).cls_line_dtl_share_pct_ovr,
x_cls_line_dtl_fee_bf_ct => p_detail_data(i).cls_line_dtl_fee_bf_contr,
x_cls_line_dtl_fee_bf_ct_ovr => p_detail_data(i).cls_line_dtl_fee_bf_contr_ovr,
x_expense_amt => p_detail_data(i).expense_amt,
x_budgeted_amt => p_detail_data(i).budgeted_amt,
x_recoverable_amt => p_detail_data(i).recoverable_amt,
x_computed_recoverable_amt => p_detail_data(i).computed_recoverable_amt,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
);
l_detail_delete_table(l_detail_delete_table.COUNT) := p_old_detail_data(i).expense_class_line_dtl_id;
l_master_delete_table(l_master_delete_table.COUNT) := p_old_master_data(i).expense_class_line_id;
FORALL i IN 0 .. l_detail_delete_table.COUNT - 1
DELETE FROM pn_rec_expcl_dtlacc_all
WHERE expense_class_line_dtl_id = l_detail_delete_table(i);
FORALL i IN 0 .. l_master_delete_table.COUNT - 1
DELETE FROM pn_rec_expcl_dtlln_all
WHERE expense_class_line_id = l_master_delete_table(i);
SELECT expense_class_line_dtl_id,
expense_class_line_id
FROM pn_rec_expcl_dtlacc_all
WHERE expense_line_dtl_id = p_expense_line_dtl_id;
SELECT sum(nvl(computed_recoverable_amt, 0)) computed_recoverable_amount,
sum(nvl(recoverable_amt, 0)) recoverable_amount,
sum(nvl(expense_amt, 0)) expense_amount,
sum(nvl(budgeted_amt, 0)) budgeted_amount,
min(decode(nvl(cls_line_dtl_share_pct_ovr, cls_line_dtl_share_pct), NULL, NULL, 100)) use_cls_line_share,
min(decode(nvl(cls_line_dtl_fee_bf_contr_ovr, cls_line_dtl_fee_bf_contr), NULL, NULL, 0)) use_cls_line_fee
FROM pn_rec_expcl_dtlacc_all
WHERE expense_class_line_id = p_exp_cls_line_id;
update_list num_tbl;
delete_list num_tbl;
l_update BOOLEAN;
DELETE pn_rec_expcl_dtlacc_all
WHERE expense_class_line_dtl_id = class_line_dtl_rec.expense_class_line_dtl_id;
l_update := FALSE;
l_info := ' getting update information for expclln id: '||hdr_id_list(i);
l_update := TRUE;
l_count := update_list.COUNT;
update_list(l_count) := hdr_id_list(i);
IF NOT l_update THEN delete_list(delete_list.COUNT) := hdr_id_list(i); END IF;
FORALL i IN 0 .. update_list.COUNT - 1
UPDATE pn_rec_expcl_dtlln_all
SET budgeted_amt = budgeted_list(i),
expense_amt = expense_list(i),
recoverable_amt = recoverable_list(i),
computed_recoverable_amt = computed_recoverable_list(i) *
nvl(share_use_tbl(i), cls_line_share_pct) / 100 *
(1 + nvl(fee_use_tbl(i), cls_line_fee_before_contr_ovr) / 100),
last_update_date = SYSDATE,
last_updated_by = nvl(fnd_profile.value('USER_ID'), -1),
last_update_login = nvl(fnd_profile.value('USER_ID'), -1)
WHERE expense_class_line_id = update_list(i);
FORALL i IN 0 .. delete_list.COUNT - 1
DELETE pn_rec_expcl_dtlln_all
WHERE expense_class_line_id = delete_list(i);
SELECT expense_line_id,
currency_code
FROM pn_rec_exp_line_all hdr
WHERE hdr.expense_extract_code = p_extract_code
AND rownum < 2;
SELECT expense_class_id
FROM pn_rec_expcl_all
WHERE property_id = p_propid
AND location_id IS NULL
AND currency_code = p_currency_code;
SELECT expense_class_id
FROM pn_rec_expcl_all
WHERE location_id = p_locnid
AND currency_code = p_currency_code;
SELECT property_id,
location_id
FROM pn_locations_all
WHERE active_start_date < fnd_date.canonical_to_date(p_to_date)
AND active_end_date > fnd_date.canonical_to_date(p_from_date)
START WITH location_id = p_location_id
CONNECT BY location_id = PRIOR parent_location_id;
SELECT area_class_id
FROM pn_rec_arcl_all
WHERE property_id = l_propid
AND location_id IS NULL;
SELECT area_class_id
FROM pn_rec_arcl_all
WHERE location_id = l_locnid;
SELECT property_id,
location_id
FROM pn_locations_all
WHERE active_start_date < fnd_date.canonical_to_date(p_to_date)
AND active_end_date > fnd_date.canonical_to_date(p_from_date)
START WITH location_id = p_location_id
CONNECT BY location_id = PRIOR parent_location_id;
SELECT 1
FROM pn_rec_exp_line_all
WHERE expense_line_id = p_expense_line_id
AND currency_code NOT IN
(SELECT currency_code FROM pn_rec_expcl_all
WHERE expense_class_id = p_expense_class_id);
SELECT class.expense_class_id,
class_type.expense_class_type_id,
class.area_class_id,
class.location_id,
class.property_id,
class.portion_pct,
class_type.expense_type_code,
class_inclusion.cls_incl_share_pct,
class.class_fee_before_contr,
class.class_fee_after_contr,
class_inclusion.cls_incl_fee_before_contr,
class_inclusion.recovery_type_code,
class_inclusion.recovery_space_std_code
FROM pn_rec_expcl_all class,
pn_rec_expcl_type_all class_type,
pn_rec_expcl_inc_all class_inclusion
WHERE class.expense_class_id = class_type.expense_class_id
AND class_type.expense_class_type_id = class_inclusion.expense_class_type_id
AND class.expense_class_id = p_expense_class_id;
SELECT nvl(lines_dtl.actual_amount_ovr, lines_dtl.actual_amount) actual_amount,
nvl(lines_dtl.budgeted_amount_ovr, lines_dtl.budgeted_amount) budgeted_amount,
lines_dtl.expense_type_code,
lines_dtl.expense_account_id,
lines_dtl.location_id,
lines_dtl.property_id,
lines_dtl.expense_line_dtl_id,
lines_dtl.expense_line_id,
lines_dtl.expense_line_indicator
FROM pn_rec_exp_line_dtl_all lines_dtl
WHERE (lines_dtl.expense_line_id = p_expense_line_id OR
lines_dtl.parent_expense_line_id IN
(SELECT expense_line_dtl_id
FROM pn_rec_exp_line_dtl_all
WHERE expense_line_id = p_expense_line_id))
AND lines_dtl.expense_type_code = p_expense_type_code
AND lines_dtl.recoverable_flag = 'Y';
SELECT cust.cust_space_assign_id,
cust.cust_account_id,
cust.lease_id,
cust.location_id,
cust.recovery_space_std_code,
cust.recovery_type_code
FROM pn_space_assign_cust_all cust
WHERE cust.location_id IN
(SELECT location_id FROM pn_locations_all locn
WHERE locn.active_start_date < fnd_date.canonical_to_date(p_to_date)
AND locn.active_end_date > fnd_date.canonical_to_date(p_from_date))
AND cust.cust_assign_start_date < fnd_date.canonical_to_date(p_to_date)
AND cust.fin_oblig_end_date > fnd_date.canonical_to_date(p_from_date)
AND cust.recovery_space_std_code = p_rec_spc_std_code
AND cust.recovery_type_code = p_rec_type_code
AND cust.location_id IN
(SELECT location_id FROM pn_locations_all
START WITH (location_id = p_location_id OR
(property_id = p_property_id AND p_location_id IS NULL))
CONNECT BY PRIOR location_id = parent_location_id);
SELECT class_line.cls_line_share_pct mst_shr_pc,
class_line.cls_line_fee_after_contr_ovr mst_fee_af,
class_line.cls_line_fee_before_contr_ovr mst_fee_bf,
class_line.location_id location_id,
class_line_dtl.cls_line_dtl_fee_bf_contr_ovr dtl_fee_bf,
class_line_dtl.cls_line_dtl_share_pct_ovr dtl_shr_pc,
class_line.cust_account_id cust_account_id,
class_line.recovery_space_std_code rec_space_std,
class_line.recovery_type_code rec_type_code,
class_line_dtl.expense_type_code exp_type,
class_line_dtl.expense_account_id exp_acct,
class_line_dtl.expense_class_line_dtl_id dtl_id,
class_line_dtl.expense_class_line_id mst_id
FROM pn_rec_expcl_dtl_all summary,
pn_rec_expcl_dtlln_all class_line,
pn_rec_expcl_dtlacc_all class_line_dtl,
pn_rec_expcl_all class,
pn_rec_exp_line_all lines
WHERE class_line.expense_class_line_id = class_line_dtl.expense_class_line_id
AND class_line.expense_class_dtl_id = summary.expense_class_dtl_id
AND summary.expense_class_id = class.expense_class_id
AND class.expense_class_id = p_expense_class_id
AND summary.expense_line_id = lines.expense_line_id
AND lines.expense_line_id = p_expense_line_id;
SELECT summary.expense_class_dtl_id
FROM pn_rec_expcl_dtl_all summary,
pn_rec_expcl_all class,
pn_rec_exp_line_all line_hdr,
(SELECT to_date, as_of_date FROM pn_rec_exp_line_all
WHERE expense_line_id = p_expense_line_id) ref_line_hdr
WHERE summary.expense_class_id = class.expense_class_id
AND summary.expense_line_id = line_hdr.expense_line_id
AND class.expense_class_id = p_expense_class_id
AND line_hdr.from_date < ref_line_hdr.to_date
AND line_hdr.to_date <= ref_line_hdr.to_date
AND line_hdr.as_of_date < ref_line_hdr.as_of_date
ORDER BY line_hdr.as_of_date DESC, line_hdr.to_date DESC, line_hdr.from_date DESC;
SELECT class_line.cls_line_share_pct mst_shr_pc,
class_line.cls_line_fee_after_contr_ovr mst_fee_af,
class_line.cls_line_fee_before_contr_ovr mst_fee_bf,
class_line.location_id location_id,
class_line_dtl.cls_line_dtl_fee_bf_contr_ovr dtl_fee_bf,
class_line_dtl.cls_line_dtl_share_pct_ovr dtl_shr_pc,
class_line.cust_account_id cust_account_id,
class_line.recovery_space_std_code rec_space_std,
class_line.recovery_type_code rec_type_code,
class_line_dtl.expense_type_code exp_type,
class_line_dtl.expense_account_id exp_acct
FROM pn_rec_expcl_dtlln_all class_line,
pn_rec_expcl_dtlacc_all class_line_dtl
WHERE class_line.expense_class_line_id = class_line_dtl.expense_class_line_id
AND class_line.expense_class_dtl_id = p_prior_cls_dtl_id;
SELECT dtl.expense_class_dtl_id,
setup.expense_class_name,
dtl.status,
dtl.default_area_class_id,
dtl.cls_line_portion_pct,
dtl.cls_line_fee_before_contr,
dtl.cls_line_fee_after_contr
FROM pn_rec_expcl_dtl_all dtl,
pn_rec_expcl_all setup
WHERE dtl.expense_line_id = p_expense_line_id
AND setup.expense_class_id = p_expense_class_id
AND setup.expense_class_id = dtl.expense_class_id;
l_fee_use_table.delete;
l_share_use_table.delete;
l_ovr_use_table.delete;
exp_cls_line_master_data.delete;
exp_cls_line_detail_data.delete;
exp_cls_curnt_master_ovr.delete;
exp_cls_curnt_detail_ovr.delete;
exp_cls_prior_master_ovr.delete;
exp_cls_prior_detail_ovr.delete;
pn_rec_expcl_dtl_pkg.update_row(
x_expense_class_id => p_expense_class_id,
x_expense_line_id => p_expense_line_id,
x_expense_class_dtl_id => l_expense_class_dtl_id,
x_status => 'OPEN',
x_def_area_cls_id => l_area_class_id,
x_cls_line_fee_bf_ct => l_mst_fee_bf_contr,
x_cls_line_fee_af_ct => l_fee_af_contr,
x_cls_line_portion_pct => l_portion_pct,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
);
pn_rec_expcl_dtl_pkg.insert_row(
x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
x_expense_class_id => p_expense_class_id,
x_expense_line_id => p_expense_line_id,
x_expense_class_dtl_id => l_expense_class_dtl_id,
x_status => 'OPEN',
x_def_area_cls_id => l_area_class_id,
x_cls_line_fee_bf_ct => l_mst_fee_bf_contr,
x_cls_line_fee_af_ct => l_fee_af_contr,
x_cls_line_portion_pct => l_portion_pct,
x_last_update_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
);
SELECT location_id, property_id
FROM pn_locations
WHERE location_code = p_location_code
AND rownum = 1;
SELECT property_id
FROM pn_properties
WHERE property_code = p_property_code;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
pn_mo_cache_utils.get_current_org_id));
p_delete_all_flag IN VARCHAR2)
IS
CURSOR derive_loc_id_from_loc_code IS
SELECT location_id
FROM pn_locations
WHERE location_code = p_location_code;
SELECT property_id
FROM pn_properties
WHERE property_code = p_property_code;
IF p_delete_all_flag = 'Y' THEN
l_info := ' purging everything ';
DELETE pn_rec_exp_itf;
l_sqlhead := 'DELETE pn_rec_exp_itf WHERE ';
'(SELECT dtl.expense_line_dtl_id ' ||
' FROM pn_rec_exp_line_all hdr, pn_rec_exp_line_dtl_all dtl ' ||
' WHERE hdr.expense_line_id = dtl.expense_line_id ' ||
' AND hdr.expense_extract_code = :l_extract_code)';