[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pd.payment_term_id
,pd.account_class
,pd.account_id
,pd.percentage
FROM pn_distributions_all pd
WHERE pd.payment_term_id = ip_payment_term_id
ORDER BY pd.account_class,
pd.account_id,
pd.percentage;
SELECT ppt.actual_amount
,ppt.frequency_code
,ppt.start_date
,ppt.end_date
,ppt.index_term_indicator
,ppt.status
,DECODE (ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE"
FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = ip_index_period_id
ORDER BY ppt.start_date;
SELECT ppt.actual_amount
,ppt.frequency_code
,ppt.start_date
,ppt.end_date
,ppt.index_term_indicator
,ppt.status
,DECODE (ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE"
FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = ip_index_period_id
AND ( ppt.payment_term_id = ip_payment_term_id
OR ip_payment_term_id IS NULL
)
ORDER BY ppt.start_date;
SELECT pilp.basis_percent_change
,pilp.current_basis
,pilp.index_percent_change
,pilp.index_finder_date
,pilp.index_period_id
,pilp.basis_start_date
,pilp.basis_end_date
,pilp.assessment_date
,pilp.line_number
,pilp.relationship
,pilp.constraint_rent_due
,pilp.current_index_line_id
,pilp.current_index_line_value
,pilp.previous_index_line_id
,pilp.previous_index_line_value
,pilp.unconstraint_rent_due
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = ip_index_lease_id
AND pilp.index_period_id = ip_index_period_id
ORDER BY pilp.line_number;
SELECT pilp.index_period_id
INTO op_prev_index_lease_id
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
AND pilp.assessment_date = (SELECT MAX (pilp.assessment_date)
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
/*
-- if p_assessment_date is null, this will return the
-- assessment date of the last index rent period.
*/
AND ( pilp.assessment_date < p_assessment_date
OR p_assessment_date IS NULL
));
SELECT DECODE (
op_type
,'UNCONSTRAINT', unconstraint_rent_due
,'CONSTRAINT', constraint_rent_due
)
INTO op_index_amount
FROM pn_index_lease_periods_all pilp
WHERE index_period_id = v_index_period_id;
SELECT pilp.index_period_id,pilp.basis_start_date,pilp.basis_end_date
INTO op_next_index_period_id,op_basis_start_date,op_basis_end_date
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
AND pilp.assessment_date = (SELECT MIN (pilp.assessment_date)
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
AND pilp.assessment_date > p_assessment_date
);
SELECT SUM (
ppt.actual_amount
* DECODE (
frequency_code
,c_spread_frequency_monthly, 12
,c_spread_frequency_quarterly, 4
,c_spread_frequency_semiannual, 2
,c_spread_frequency_annually, 1
,c_spread_frequency_one_time, 0
)
)
INTO p_prev_index_amount
FROM pn_payment_terms_all ppt, pn_index_lease_periods_all ppi
WHERE ppt.index_period_id = ppi.index_period_id
AND ppi.index_lease_id = p_index_lease_id
AND ppi.assessment_date < (SELECT assessment_date
FROM pn_index_lease_periods_all
WHERE index_period_id = p_index_period_id)
AND ppt.lease_id = p_lease_id;
SELECT SUM (
DECODE (
op_type
,'UNCONSTRAINT', unconstraint_rent_due
,'CONSTRAINT', constraint_rent_due
)
)
INTO p_sum_prev_index_amts
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
AND pilp.assessment_date < p_assessment_date;
then the select statement should ignore payment items
whose parent payment term is from an index increase. */
/* gets the data from all terms active in the basis period */
CURSOR csr_exc_get_item_period (p_payment_type VARCHAR2,p_org_id NUMBER) IS
SELECT ppt.payment_term_id
,NVL(ppt.actual_amount, ppt.estimated_amount)
,ppt.frequency_code
FROM pn_payment_terms_all ppt
,pn_index_leases_all pil
WHERE pil.index_lease_id = p_index_lease_id
AND ppt.lease_id = pil.lease_id
AND ppt.payment_term_type_code
= DECODE(p_payment_type, c_increase_on_gross,
ppt.payment_term_type_code, p_payment_type)
AND NVL(ppt.index_period_id, -1) NOT IN
(SELECT index_period_id
FROM pn_index_lease_periods_all ppilx
WHERE ppilx.index_lease_id = p_index_lease_id)
AND NVL(ppt.status,'-1')
= DECODE(ppt.index_period_id, NULL,
NVL(ppt.status,'-1'), 'APPROVED')
AND ppt.end_date >= p_basis_start_date
AND ppt.start_date <= p_basis_end_date
AND ppt.frequency_code <> c_spread_frequency_one_time
AND (
ppt.payment_term_id IN (SELECT piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id
AND piet.include_exclude_flag = 'I')
OR
(
ppt.payment_term_id NOT IN (select piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id)
AND ( pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'Y' OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'G' and NVL(pil.gross_flag,'N') = 'Y') OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'U' and NVL(pil.gross_flag,'N') = 'N')
)
)
)
AND ppt.currency_code = pil.currency_code;
SELECT ppt.payment_term_id
,NVL(ppt.actual_amount, ppt.estimated_amount)
,ppt.frequency_code
FROM pn_payment_terms_all ppt
,pn_index_leases_all pil
WHERE pil.index_lease_id = p_index_lease_id
AND ppt.lease_id = pil.lease_id
AND ppt.payment_term_type_code
= DECODE(p_payment_type, c_increase_on_gross,
ppt.payment_term_type_code, p_payment_type)
AND NVL(ppt.index_period_id, -1) NOT IN
(SELECT index_period_id
FROM pn_index_lease_periods_all ppilx
WHERE ppilx.index_lease_id = p_index_lease_id)
AND NVL(ppt.status,'-1')
= DECODE(ppt.index_period_id, NULL,
NVL(ppt.status,'-1'), 'APPROVED')
AND ppt.end_date >= p_basis_end_date
AND ppt.start_date <= p_basis_end_date
AND ppt.frequency_code <> c_spread_frequency_one_time
AND (
ppt.payment_term_id IN (SELECT piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id
AND piet.include_exclude_flag = 'I')
OR
(
ppt.payment_term_id NOT IN (select piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id)
AND ( pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'Y' OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'G' and NVL(pil.gross_flag,'N') = 'Y') OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'U' and NVL(pil.gross_flag,'N') = 'N')
)
)
)
AND ppt.currency_code = pil.currency_code;
SELECT ppt.payment_term_id
,NVL(ppt.actual_amount,ppt.estimated_amount)
,ppt.frequency_code
FROM pn_payment_terms_all ppt
,pn_index_leases_all pil
WHERE pil.index_lease_id = p_index_lease_id
AND ppt.lease_id = pil.lease_id
AND ppt.payment_term_type_code
= DECODE(p_payment_type,c_increase_on_gross,
ppt.payment_term_type_code, p_payment_type)
AND NVL(ppt.status,'-1')
= DECODE(ppt.index_period_id,NULL,
NVL(ppt.status,'-1'),'APPROVED')
AND ppt.end_date >= p_basis_start_date
AND ppt.start_date <= p_basis_end_date
AND ppt.frequency_code <> c_spread_frequency_one_time
AND (
ppt.payment_term_id IN (SELECT piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id
AND piet.include_exclude_flag = 'I')
OR
(
ppt.payment_term_id NOT IN (select piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id)
AND ( pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'Y' OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'G' and NVL(pil.gross_flag,'N') = 'Y') OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'U' and NVL(pil.gross_flag,'N') = 'N')
)
)
)
AND ppt.currency_code = pil.currency_code;
SELECT ppt.payment_term_id
,NVL(ppt.actual_amount,ppt.estimated_amount)
,ppt.frequency_code
FROM pn_payment_terms_all ppt
,pn_index_leases_all pil
WHERE pil.index_lease_id = p_index_lease_id
AND ppt.lease_id = pil.lease_id
AND ppt.payment_term_type_code
= DECODE(p_payment_type,c_increase_on_gross,
ppt.payment_term_type_code, p_payment_type)
AND NVL(ppt.status,'-1')
= DECODE(ppt.index_period_id,NULL,
NVL(ppt.status,'-1'),'APPROVED')
AND ppt.end_date >= p_basis_end_date
AND ppt.start_date <= p_basis_end_date
AND ppt.frequency_code <> c_spread_frequency_one_time
AND (
ppt.payment_term_id IN (SELECT piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id
AND piet.include_exclude_flag = 'I')
OR
(
ppt.payment_term_id NOT IN (select piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id)
AND ( pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'Y' OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'G' and NVL(pil.gross_flag,'N') = 'Y') OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',p_org_id) = 'U' and NVL(pil.gross_flag,'N') = 'N')
)
)
)
AND ppt.currency_code = pil.currency_code;
SELECT org_id, increase_on
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT include_in_var_rent
INTO l_include_in_var_rent
FROM pn_payment_terms_all ppt
WHERE ppt.payment_term_id = l_payment_term_id
AND ppt.payment_term_type_code = nvl(p_type_code,ppt.payment_term_type_code);
SELECT include_in_var_rent
INTO l_include_in_var_rent
FROM pn_payment_terms_all ppt
WHERE ppt.payment_term_id = l_payment_term_id;
SELECT pil.index_lease_id
,pil.initial_basis
,pil.basis_type
,nvl(pil.increase_on,c_increase_on_gross) "INCREASE_ON"
,pilp.index_period_id
,pilp.basis_start_date
,pilp.basis_end_date
FROM pn_index_leases_all pil, pn_index_lease_periods_all pilp
WHERE pil.index_lease_id = pilp.index_lease_id
AND pil.index_lease_id = p_index_lease_id
AND pilp.line_number = 1;
SELECT scope,
( maximum_amount * ip_prorate_factor ) maximum_amount,
( maximum_percent * ip_prorate_factor ) maximum_percent,
( minimum_amount * ip_prorate_factor ) minimum_amount,
( minimum_percent * ip_prorate_factor ) minimum_percent
FROM pn_index_lease_constraints_all
WHERE index_lease_id = ip_index_lease_id;
SELECT phl.index_line_id
,phl.index_figure
INTO v_index_line_id
,v_index_figure
FROM pn_index_history_lines phl
WHERE phl.index_id = p_index_history_id
AND phl.index_date = p_index_finder_date;
SELECT phl.index_line_id
,phl.index_figure
INTO v_index_line_id
,v_index_figure
FROM pn_index_history_lines phl
WHERE phl.index_id = p_index_history_id
AND TO_NUMBER (TO_CHAR (phl.index_date, 'MMYYYY')) =
TO_NUMBER (TO_CHAR (p_index_finder_date, 'MMYYYY'));
SELECT relationship
FROM pn_index_lease_periods_all
WHERE assessment_date = p_assessment_date
AND index_lease_id = p_index_lease_id;
SELECT TO_NUMBER (TO_CHAR (p_index_finder_date, 'YYYYMM'))
,TO_NUMBER (TO_CHAR (ADD_MONTHS (p_index_finder_date, -12), 'YYYYMM'))
INTO v_upper_index_date_code
,v_lower_index_date_code
FROM DUAL;
SELECT MAX (phl.index_date)
INTO v_current_finder_date
FROM pn_index_history_lines phl
WHERE phl.index_id = p_index_history_id
AND TO_NUMBER (TO_CHAR (phl.index_date, 'YYYYMM'))
BETWEEN v_lower_index_date_code
AND v_upper_index_date_code
AND index_figure IS NOT NULL;
SELECT MAX (pilp.assessment_date)
INTO v_prev_assessment_date
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
AND pilp.assessment_date < p_assessment_date;
SELECT current_index_line_id
,current_index_line_value
INTO v_previous_cpi_id
,v_previous_cpi_value
FROM pn_index_lease_periods_all
WHERE index_period_id = v_prev_period_id;
/* cursor to select all normalized payment terms recurring payment that are APPROVED and not normalized.*/
CURSOR c1 IS
SELECT ppt.actual_amount
,ppt.frequency_code
FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = p_index_period_id
AND ppt.index_term_indicator = c_index_pay_term_type_atlst;
SELECT DECODE (
v_normalized_frequency
,c_spread_frequency_monthly, 12
,c_spread_frequency_one_time, 1
,c_spread_frequency_quarterly, 4
,c_spread_frequency_semiannual, 2
,c_spread_frequency_annually, 1
,1
)
INTO v_multiplier
FROM DUAL;
SELECT ppt.payment_term_id, ppt.actual_amount, ppt.frequency_code
FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = p_index_period_id
AND ppt.status =c_payment_term_status_approved
AND ppt.index_term_indicator = p_index_term_indicator;
SELECT DECODE (
cursor_rec.frequency_code
,c_spread_frequency_monthly, 12
,c_spread_frequency_one_time, 1
,c_spread_frequency_quarterly, 4
,c_spread_frequency_semiannual, 2
,c_spread_frequency_annually, 1
,1
)
INTO v_multiplier
FROM DUAL;
SELECT ppt.payment_term_id, ppt.actual_amount,ppt.frequency_code
FROM pn_payment_terms_all ppt,
pn_payment_items_all ppi
WHERE ppt.index_period_id = ip_index_period_id
AND ppt.status =c_payment_term_status_approved
AND ppt.index_term_indicator = ip_index_term_indicator
AND ppt.payment_purpose_code = ip_payment_purpose_code
AND ppt.payment_term_type_code = ip_payment_term_type_code
AND ppt.payment_term_id = ppi.payment_term_id
AND ppi.payment_item_type_lookup_code = 'CASH'
AND DECODE (
ip_index_term_indicator
,c_index_pay_term_type_atlst, NVL (ppt.normalize, 'N')
,'IGNORE'
) = ip_normalize
AND ( ppt.location_id = ip_location_id
OR ip_location_id IS NULL
)
AND ( ppt.vendor_id = ip_vendor_id
OR ip_vendor_id IS NULL
)
AND ( ppt.vendor_site_id = ip_vendor_site_id
OR ip_vendor_site_id IS NULL
)
AND ( ppt.customer_id = ip_customer_id
OR ip_customer_id IS NULL
)
AND ( ppt.customer_site_use_id = ip_customer_site_use_id
OR ip_customer_site_use_id IS NULL
)
AND ( build_distributions_string (ppt.payment_term_id) = ip_distribution_string
OR ip_distribution_string IS NULL
);
SELECT '1'
INTO v_exists
FROM pn_payment_terms_all
WHERE index_period_id = p_index_period_id
AND status = c_payment_term_status_approved
AND index_term_indicator = p_index_term_indicator;
SELECT DECODE (
cursor_rec.frequency_code
,c_spread_frequency_monthly, 12
,c_spread_frequency_one_time, 1
,c_spread_frequency_quarterly, 4
,c_spread_frequency_semiannual, 2
,c_spread_frequency_annually, 1
,1
)
INTO v_multiplier
FROM DUAL;
SELECT index_finder_method
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT DECODE (
p_spread_frequency
,c_spread_frequency_monthly, 1
,c_spread_frequency_quarterly, 3
,c_spread_frequency_semiannual, 6
,c_spread_frequency_annually, 12
,1
)
INTO v_num_months_bet_payments
FROM DUAL;
SELECT basis_type,reference_period,assessment_interval,termination_date
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT assessment_date, index_period_id
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
ORDER BY assessment_date;
procedure insert_inter_term (ip_index_period_id NUMBER,
ip_index_term_indicator VARCHAR2,
ip_combination_amt NUMBER,
ip_total_terms_amt NUMBER,
ip_rent_increase_term_id NUMBER,
ip_index_lease_id NUMBER) IS
i NUMBER;
SELECT SUM(amount) total_inter_term
FROM pn_index_lease_terms_all
WHERE index_period_id = ip_index_period_id
AND lease_term_id = ip_payment_term_id
AND index_term_indicator = ip_index_term_indicator;
PN_INDEX_LEASE_TERMS_PKG.INSERT_ROW
(
X_INDEX_LEASE_TERM_ID => l_index_lease_term_id
,X_INDEX_LEASE_ID => ip_index_lease_id
,X_INDEX_PERIOD_ID => ip_index_period_id
,X_LEASE_TERM_ID => item_amt_tab(i).payment_term_id
,X_RENT_INCREASE_TERM_ID => ip_rent_increase_term_id
,X_AMOUNT => (NVL(l_term_contribution,0)- NVL(l_total_inter_term,0))
,X_APPROVED_FLAG => 'DRAFT'
,X_INDEX_TERM_INDICATOR => ip_index_term_indicator
,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 ('USER_ID'), 0)
);
end insert_inter_term;
SELECT DISTINCT ppt.location_id
,ppt.payment_purpose_code
,ppt.payment_term_type_code
,ppt.vendor_id
,ppt.vendor_site_id
,ppt.customer_id
,ppt.customer_site_use_id
,ppt.frequency_code
,ppt.include_in_var_rent
,build_distributions_string (ppt.payment_term_id) "DISTRIBUTION_STRING"
,NVL (ppt.normalize, 'N') "NORMALIZE"
FROM pn_payment_terms_all ppt,
pn_index_leases_all pil
WHERE pil.index_lease_id = ip_index_lease_id
AND ppt.lease_id = pil.lease_id
AND nvl(ppt.status,'-1') = decode(ppt.index_period_id,null,nvl(ppt.status,'-1'),'APPROVED')
AND ppt.end_date >= ip_basis_start_date AND ppt.start_date <= ip_basis_end_date
AND ppt.payment_term_type_code = decode(ip_type_code,c_increase_on_gross,
ppt.payment_term_type_code,ip_type_code)
AND (
ppt.payment_term_id IN (SELECT piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id
AND piet.include_exclude_flag = 'I')
OR
(
ppt.payment_term_id NOT IN (select piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id)
AND ( pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',ip_org_id) = 'Y' OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',ip_org_id) = 'G' and NVL(pil.gross_flag,'N') = 'Y') OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',ip_org_id) = 'U' and NVL(pil.gross_flag,'N') = 'N')
)
)
)
AND (( p_basis_type = c_basis_type_compound AND
not exists( SELECT null
FROM pn_index_lease_periods_all plpx
WHERE plpx.index_period_id = ppt.index_period_id
AND plpx.index_lease_id = p_index_lease_id))
OR p_basis_type <> c_basis_type_compound)
AND ppt.frequency_code <> c_spread_frequency_one_time
AND ppt.currency_code = pil.currency_code
order by 1,2,3,4,5,6,7,8,9,10,11;
SELECT ppt.payment_term_id,nvl(ppt.actual_amount,ppt.estimated_amount)
,ppt.frequency_code
,ppt.norm_start_date
FROM pn_payment_terms_all ppt
WHERE ppt.payment_purpose_code = ip_payment_purpose_code
AND ppt.payment_term_type_code = ip_payment_term_type_code
AND nvl(ppt.include_in_var_rent,'N') = nvl(ip_include_in_var_rent,'N')
AND nvl(ppt.status,'-1') = decode(ppt.index_period_id,null,nvl(ppt.status,'-1'),'APPROVED')
AND NVL (ppt.normalize, 'N')= ip_normalize
AND nvl(ppt.location_id,-1) = nvl(ip_location_id,nvl(ppt.location_id,-1))
AND nvl(ppt.vendor_id,-1) = nvl(ip_vendor_id,nvl(ppt.vendor_id,-1))
AND nvl(ppt.vendor_site_id,-1) = nvl(ip_vendor_site_id,nvl(ppt.vendor_site_id,-1))
AND nvl(ppt.customer_id ,-1)= nvl(ip_customer_id,nvl(ppt.customer_id,-1))
AND nvl(ppt.customer_site_use_id,-1) = nvl(ip_customer_site_use_id,
nvl(ppt.customer_site_use_id,-1))
AND ppt.frequency_code = ip_frequency_code
AND ( build_distributions_string (ppt.payment_term_id) = ip_distribution_string
OR ip_distribution_string IS NULL
)
AND (
ppt.payment_term_id IN (SELECT piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id
AND piet.include_exclude_flag = 'I')
OR
(
ppt.payment_term_id NOT IN (select piet.payment_term_id
FROM pn_index_exclude_term_all piet
WHERE piet.index_lease_id = p_index_lease_id)
AND ( pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',ip_org_id) = 'Y' OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',ip_org_id) = 'G' and l_gross_flag = 'Y' ) OR
(pn_mo_cache_utils.get_profile_value('incl_terms_by_default_flag',ip_org_id) = 'U' and l_gross_flag = 'N' )
)
)
)
AND ( ( p_basis_type = c_basis_type_compound AND
not exists( SELECT null
FROM pn_index_lease_periods_all plpx
WHERE plpx.index_period_id = ppt.index_period_id
AND plpx.index_lease_id = p_index_lease_id))
OR p_basis_type <> c_basis_type_compound)
AND ppt.end_date >= ip_basis_start_date AND ppt.start_date <= ip_basis_end_date
AND ppt.FREQUENCY_CODE <> c_spread_frequency_one_time
AND ppt.lease_id = p_lease_id
AND ppt.currency_code = g_currency_code;
SELECT payment_term_id,
actual_amount,
location_id,
payment_purpose_code,
payment_term_type_code,
vendor_id,
vendor_site_id,
customer_id,
customer_site_use_id,
frequency_code,
index_term_indicator,
start_date,
normalize,
schedule_day,
end_date,
include_in_var_rent
FROM pn_payment_terms_all
WHERE lease_id = p_lease_id
AND index_period_id = p_index_period_id
AND status = c_payment_term_status_approved;
SELECT nvl(ppt.actual_amount,0) actual_amount, ppt.index_term_indicator, ppt.payment_term_id
FROM pn_payment_terms_all ppt
WHERE ppt.payment_purpose_code = ip_payment_purpose_code
AND ppt.payment_term_type_code = ip_payment_term_type_code
AND ppt.index_period_id = ip_index_period_id
AND nvl(ppt.status,'-1') = 'APPROVED'
AND ((ip_index_term_indicator = c_index_pay_term_type_atlst
AND ppt.index_term_indicator in (c_index_pay_term_type_atlst,c_index_pay_term_type_atlst_bb)) OR
(ip_index_term_indicator = c_index_pay_term_type_recur
AND ppt.index_term_indicator in (c_index_pay_term_type_recur,c_index_pay_term_type_backbill))
)
AND ((ppt.index_term_indicator = 'ATLEAST'
AND nvl(ppt.normalize,'N') = ip_normalize) OR
(ppt.index_term_indicator <> 'ATLEAST'))
AND nvl(ppt.location_id,-1) = nvl(ip_location_id,nvl(ppt.location_id,-1))
AND nvl(ppt.vendor_id,-1) = nvl(ip_vendor_id,nvl(ppt.vendor_id,-1))
AND nvl(ppt.vendor_site_id,-1) = nvl(ip_vendor_site_id,nvl(ppt.vendor_site_id,-1))
AND nvl(ppt.customer_id ,-1)= nvl(ip_customer_id,nvl(ppt.customer_id,-1))
AND nvl(ppt.customer_site_use_id,-1) = nvl(ip_customer_site_use_id,
nvl(ppt.customer_site_use_id,-1))
AND ((ppt.index_term_indicator in (c_index_pay_term_type_atlst,
c_index_pay_term_type_recur)
AND ppt.frequency_code = ip_frequency_code) OR
(ppt.index_term_indicator in (c_index_pay_term_type_atlst_bb,
c_index_pay_term_type_backbill)
AND ppt.frequency_code = ppt.frequency_code)
)
AND (build_distributions_string (ppt.payment_term_id) = ip_distribution_string
OR ip_distribution_string IS NULL
)
AND ppt.currency_code = g_currency_code;
SELECT sum(item.actual_amount) sum_overlap_amt
FROM pn_payment_items_all item,
pn_payment_terms_all term
WHERE item.payment_term_id = term.payment_term_id
AND item.due_date BETWEEN p_assessment_date AND p_end_date
AND term.index_period_id = p_index_period_id
AND term.index_term_indicator = p_index_term_indicator
AND nvl(term.location_id, -1) = nvl(p_location_id, -1)
AND nvl(term.payment_purpose_code, 'N') = nvl(p_payment_purpose_code, 'N')
AND nvl(term.payment_term_type_code, 'N') = nvl(p_payment_term_type_code, 'N')
AND nvl(term.vendor_id, -1) = nvl(p_vendor_id, -1)
AND nvl(term.vendor_site_id, -1) = nvl(p_vendor_site_id, -1)
AND nvl(term.customer_id, -1) = nvl(p_customer_id, -1)
AND nvl(term.customer_site_use_id, -1) = nvl(p_customer_site_use_id, -1)
AND nvl(term.normalize, 'N') = p_normalize
AND build_distributions_string(term.payment_term_id) = p_distribution_string;
SELECT org_id
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT NVL(GROSS_FLAG,'N')
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT reference_period
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT NVL (p_increase_on, c_increase_on_gross)
INTO v_type_code
FROM DUAL;
l_check_term_tbl.delete;
l_chklist_tbl.delete;
SELECT DECODE (
rec_unq_comb.frequency_code
,c_spread_frequency_monthly, 12
,c_spread_frequency_quarterly, 4
,c_spread_frequency_semiannual, 2
,c_spread_frequency_annually, 1
,1 )
INTO v_payments_per_year
FROM DUAL;
SELECT decode(p_index_term_indicator,
c_index_pay_term_type_atlst,
c_index_pay_term_type_atlst_bb,
c_index_pay_term_type_recur,
c_index_pay_term_type_backbill)
INTO p_backbill_term_indicator
FROM DUAL;
item_amt_tab.delete;
put_log ('create_aggr_payment_terms - delete tab ' );
exist_term_tab.delete;
appr_ind_lease_tbl.delete;
/* insert records into intermediate table */
IF v_created_payment_term_id IS NOT NULL THEN --#@#Bug4291907
insert_inter_term (
ip_index_period_id => p_index_period_id,
ip_index_term_indicator => p_index_term_indicator,
ip_combination_amt => v_payment_amount,
ip_total_terms_amt => v_total_amt,
ip_rent_increase_term_id => v_created_payment_term_id,
ip_index_lease_id => p_index_lease_id);
/* Insert records into the intermediate table , for the backbill amounts */
IF v_created_payment_term_id IS NOT NULL THEN --#@#Bug4291907
insert_inter_term (
ip_index_period_id => p_index_period_id,
ip_index_term_indicator => p_backbill_term_indicator,
ip_combination_amt => v_payment_amount,
ip_total_terms_amt => v_total_amt,
ip_rent_increase_term_id => v_created_payment_term_id,
ip_index_lease_id => p_index_lease_id);
/* Insert record into the intermediate table */
IF v_created_payment_term_id IS NOT NULL THEN --#@#Bug4291907
insert_inter_term (
ip_index_period_id => p_index_period_id,
ip_index_term_indicator => p_backbill_term_indicator,
ip_combination_amt => v_payment_amount,
ip_total_terms_amt => v_total_amt,
ip_rent_increase_term_id => v_created_payment_term_id,
ip_index_lease_id => p_index_lease_id);
l_impacted_term_tbl.delete;
insert_inter_term (
ip_index_period_id => p_index_period_id,
ip_index_term_indicator => l_impacted_term_tbl(c1).index_term_indicator,
ip_combination_amt => l_impacted_term_tbl(c1).actual_amount,
ip_total_terms_amt => l_impacted_term_tbl(c1).actual_amount,
ip_rent_increase_term_id => v_created_payment_term_id,
ip_index_lease_id => p_index_lease_id);
SELECT sum(item.actual_amount) sum_overlap_amt
FROM pn_payment_items_all item,
pn_payment_terms_all term
WHERE item.payment_term_id = term.payment_term_id
AND term.term_template_id = p_term_template_id
AND term.index_period_id = p_index_period_id
AND term.index_term_indicator = p_index_term_indicator
AND item.due_date BETWEEN p_start_date AND p_end_date;
SELECT reference_period
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT pld.lease_termination_date
INTO v_main_lease_termination_date
FROM pn_lease_details_all pld
WHERE pld.lease_id = p_lease_id;
SELECT NVL(pld.lease_extension_end_date,pld.lease_termination_date)
INTO v_main_lease_termination_date
FROM pn_lease_details_all pld
WHERE pld.lease_id = p_lease_id;
SELECT normalize
INTO v_normalize
FROM pn_term_templates_all
WHERE term_template_id = p_term_template_id;
SELECT current_basis
INTO v_normalize_basis_amount
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
AND line_number = 1;
SELECT DECODE (
p_spread_frequency
,c_spread_frequency_monthly, 12
,c_spread_frequency_quarterly, 4
,c_spread_frequency_semiannual, 2
,c_spread_frequency_annually, 1
,1
)
INTO v_payments_per_year
FROM DUAL;
SELECT count(*)
INTO l_found_atlst_bb
FROM pn_payment_terms_all
WHERE index_period_id = p_index_period_id
AND index_term_indicator = c_index_pay_term_type_atlst_bb;
SELECT DECODE (
p_spread_frequency
,c_spread_frequency_monthly, 12
,c_spread_frequency_quarterly, 4
,c_spread_frequency_semiannual, 2
,c_spread_frequency_annually, 1
,1
)
INTO v_payments_per_year
FROM DUAL;
SELECT DECODE (
p_spread_frequency
,c_spread_frequency_monthly, 12
,c_spread_frequency_quarterly, 4
,c_spread_frequency_semiannual, 2
,c_spread_frequency_annually, 1
,1
)
INTO v_payments_per_year
FROM DUAL;
SELECT *
FROM pn_distributions_all
WHERE term_template_id = ip_term_template_id;
SELECT *
FROM pn_distributions_all
WHERE payment_term_id = ip_payment_term_id;
SELECT location_id
FROM pn_index_leases_all
WHERE index_lease_id =
(SELECT index_lease_id
FROM pn_index_lease_periods_all
WHERE index_period_id = p_index_period_id);
SELECT org_id
FROM pn_leases_all
WHERE lease_id = p_lease_id;
SELECT vr_nbp_flag
FROM pn_index_leases_all
WHERE index_lease_id =
(SELECT index_lease_id
FROM pn_index_lease_periods_all
WHERE index_period_id = p_index_period_id);
SELECT include_in_var_rent
,normalize
,schedule_day
,end_date
,payment_purpose_code
,payment_term_type_code
,project_id
,task_id
,organization_id
,expenditure_type
,expenditure_item_date
,vendor_id
,vendor_site_id
,customer_id
,customer_site_use_id
,cust_ship_site_id
,ap_ar_term_id
,cust_trx_type_id
,tax_group_id
,tax_code_id
,tax_classification_code
,tax_included
,distribution_set_id
,inv_rule_id
,account_rule_id
,salesrep_id
,set_of_books_id
,currency_code
,po_header_id
,cust_po_number
,receipt_method_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,frequency_code
,location_id
,org_id
INTO v_include_in_var_rent
,v_normalize
,v_schedule_day
,v_term_end_date
,v_payment_purpose_code
,v_payment_term_type_code
,v_project_id
,v_task_id
,v_organization_id
,v_expenditure_type
,v_expenditure_item_date
,v_vendor_id
,v_vendor_site_id
,v_customer_id
,v_customer_site_use_id
,v_cust_ship_site_id
,v_ap_ar_term_id
,v_cust_trx_type_id
,v_tax_group_id
,v_tax_code_id
,v_tax_classification_code
,v_tax_included
,v_distribution_set_id
,v_inv_rule_id
,v_account_rule_id
,v_salesrep_id
,v_set_of_books_id
,v_currency_code
,v_po_header_id
,v_cust_po_number
,v_receipt_method_id
,v_attribute_category
,v_attribute1
,v_attribute2
,v_attribute3
,v_attribute4
,v_attribute5
,v_attribute6
,v_attribute7
,v_attribute8
,v_attribute9
,v_attribute10
,v_attribute11
,v_attribute12
,v_attribute13
,v_attribute14
,v_attribute15
,v_frequency_code
,v_location_id
,v_org_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_payment_term_id;
SELECT name
,normalize
,schedule_day
,payment_purpose_code
,payment_term_type_code
,accrual_account_id
,project_id
,task_id
,organization_id
,expenditure_type
,expenditure_item_date
,vendor_id
,vendor_site_id
,customer_id
,customer_site_use_id
,cust_ship_site_id
,ap_ar_term_id
,cust_trx_type_id
,tax_group_id
,tax_code_id
,tax_classification_code
,tax_included
,distribution_set_id
,inv_rule_id
,account_rule_id
,salesrep_id
,set_of_books_id
,po_header_id
,cust_po_number
,receipt_method_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id
INTO v_name
,v_normalize
,v_schedule_day
,v_payment_purpose_code
,v_payment_term_type_code
,v_accrual_account_id
,v_project_id
,v_task_id
,v_organization_id
,v_expenditure_type
,v_expenditure_item_date
,v_vendor_id
,v_vendor_site_id
,v_customer_id
,v_customer_site_use_id
,v_cust_ship_site_id
,v_ap_ar_term_id
,v_cust_trx_type_id
,v_tax_group_id
,v_tax_code_id
,v_tax_classification_code
,v_tax_included
,v_distribution_set_id
,v_inv_rule_id
,v_account_rule_id
,v_salesrep_id
,v_set_of_books_id
,v_po_header_id
,v_cust_po_number
,v_receipt_method_id
,v_attribute_category
,v_attribute1
,v_attribute2
,v_attribute3
,v_attribute4
,v_attribute5
,v_attribute6
,v_attribute7
,v_attribute8
,v_attribute9
,v_attribute10
,v_attribute11
,v_attribute12
,v_attribute13
,v_attribute14
,v_attribute15
,v_org_id
FROM pn_term_templates_all
WHERE term_template_id = p_term_template_id;
SELECT trunc(pilp.assessment_date)
INTO v_assessment_date
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_period_id = p_index_period_id;
SELECT pl.lease_class_code
,pld.expense_account_id
,pld.lease_termination_date
,pld.lease_change_id
INTO v_lease_class_code
,v_expense_account_id
,v_lease_termination_date
,v_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;
pnt_payment_terms_pkg.insert_row (
x_rowid => v_rowid
,x_payment_term_id => v_payment_term_id
,x_index_period_id => p_index_period_id
,x_index_term_indicator => p_index_term_indicator
,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 (v_payment_purpose_code, p_purpose_code)
,x_payment_term_type_code => NVL (v_payment_term_type_code,c_payment_term_type_index)
,x_frequency_code => p_spread_frequency
,x_lease_id => p_lease_id
,x_lease_change_id => v_lease_change_id
,x_start_date => v_payment_start_date --p_start_date
,x_end_date => v_payment_end_date
,x_set_of_books_id => NVL(v_set_of_books_id,v_gl_set_of_books_id)
,x_currency_code => v_currency_code
,x_rate => 1 -- not used in application
,x_last_update_login => NVL (fnd_profile.VALUE ('USER_ID'), 0)
,x_vendor_id => v_vendor_id
,x_vendor_site_id => v_vendor_site_id
,x_target_date => NULL
,x_actual_amount => v_actual_amount
,x_estimated_amount => NULL
,x_attribute_category => v_attribute_category
,x_attribute1 => v_attribute1
,x_attribute2 => v_attribute2
,x_attribute3 => v_attribute3
,x_attribute4 => v_attribute4
,x_attribute5 => v_attribute5
,x_attribute6 => v_attribute6
,x_attribute7 => v_attribute7
,x_attribute8 => v_attribute8
,x_attribute9 => v_attribute9
,x_attribute10 => v_attribute10
,x_attribute11 => v_attribute11
,x_attribute12 => v_attribute12
,x_attribute13 => v_attribute13
,x_attribute14 => v_attribute14
,x_attribute15 => v_attribute15
,x_project_attribute_category => NULL
,x_project_attribute1 => NULL
,x_project_attribute2 => NULL
,x_project_attribute3 => NULL
,x_project_attribute4 => NULL
,x_project_attribute5 => NULL
,x_project_attribute6 => NULL
,x_project_attribute7 => NULL
,x_project_attribute8 => NULL
,x_project_attribute9 => NULL
,x_project_attribute10 => NULL
,x_project_attribute11 => NULL
,x_project_attribute12 => NULL
,x_project_attribute13 => NULL
,x_project_attribute14 => NULL
,x_project_attribute15 => NULL
,x_customer_id => v_customer_id
,x_customer_site_use_id => v_customer_site_use_id
,x_normalize => v_normalize --p_normalized
,x_location_id => v_location_id
,x_schedule_day => v_schedule_day
,x_cust_ship_site_id => v_cust_ship_site_id
,x_ap_ar_term_id => v_ap_ar_term_id
,x_cust_trx_type_id => v_cust_trx_type_id
,x_project_id => v_project_id
,x_task_id => v_task_id
,x_organization_id => v_organization_id
,x_expenditure_type => v_expenditure_type
,x_expenditure_item_date => v_expenditure_item_date
,x_tax_group_id => v_tax_group_id
,x_tax_code_id => v_tax_code_id
,x_tax_classification_code => v_tax_classification_code
,x_tax_included => v_tax_included
,x_distribution_set_id => v_distribution_set_id
,x_inv_rule_id => v_inv_rule_id
,x_account_rule_id => v_account_rule_id
,x_salesrep_id => v_salesrep_id
,x_approved_by => NULL
,x_status => c_payment_term_status_draft
,x_po_header_id => v_po_header_id
,x_cust_po_number => v_cust_po_number
,x_receipt_method_id => v_receipt_method_id
,x_calling_form => 'PNTRENTI'
,x_org_id => v_org_id
,x_term_template_id => p_term_template_id
,x_area => v_area
,x_area_type_code => v_area_type_code
,x_norm_start_date => p_norm_st_date
,x_include_in_var_rent => v_include_in_var_rent
,x_recur_bb_calc_date => p_recur_bb_calc_date
);
pn_distributions_pkg.insert_row (
x_rowid => v_rowid
,x_distribution_id => v_distribution_id
,x_account_id => c_rec.account_id
,x_payment_term_id => v_payment_term_id
,x_term_template_id => NULL
,x_account_class => c_rec.account_class
,x_percentage => c_rec.percentage
,x_line_number => c_rec.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 ('USER_ID'), 0)
,x_attribute_category => c_rec.attribute_category
,x_attribute1 => c_rec.attribute1
,x_attribute2 => c_rec.attribute2
,x_attribute3 => c_rec.attribute3
,x_attribute4 => c_rec.attribute4
,x_attribute5 => c_rec.attribute5
,x_attribute6 => c_rec.attribute6
,x_attribute7 => c_rec.attribute7
,x_attribute8 => c_rec.attribute8
,x_attribute9 => c_rec.attribute9
,x_attribute10 => c_rec.attribute10
,x_attribute11 => c_rec.attribute11
,x_attribute12 => c_rec.attribute12
,x_attribute13 => c_rec.attribute13
,x_attribute14 => c_rec.attribute14
,x_attribute15 => c_rec.attribute15
);
SELECT pil.index_lease_id
,pil.index_id
,pil.lease_id
,pil.commencement_date
,pil.currency_code
,nvl(pil.increase_on,c_increase_on_gross) "INCREASE_ON"
,pil.basis_type
,pil.initial_basis
,pil.index_finder_method
,pil.base_index
,pil.base_index_line_id
,NVL (pil.rounding_flag, 'N') "ROUNDING_FLAG"
,pil.reference_period
,pil.spread_frequency
,pil.term_template_id
,pil.negative_rent_type
,NULL as purpose
,pil.location_id
,pil.index_lease_number
,pil.carry_forward_flag
,pilp.basis_percent_change
,pilp.current_basis
,pilp.index_percent_change
,pilp.index_finder_date
,pilp.index_period_id
,pilp.basis_start_date
,pilp.basis_end_date
,pilp.assessment_date
,pilp.line_number
,pilp.relationship
,pilp.constraint_rent_due
,pilp.unconstraint_rent_due
,pilp.current_index_line_id
,pilp.current_index_line_value
,pilp.previous_index_line_id
,pilp.previous_index_line_value
,pilp.carry_forward_amount
,pilp.constraint_applied_amount
,pilp.carry_forward_percent
,pilp.constraint_applied_percent
,pl.lease_class_code
,pil.org_id
,nvl (pilp.index_multiplier, 1) "INDEX_MULTIPLIER"
,nvl (pil.proration_rule, 'NO_PRORATION') "PRORATION_RULE"
,pil.proration_period_start_date
,pil.assessment_interval
FROM pn_index_leases_all pil,
pn_index_lease_periods_all pilp,
pn_leases_all pl
WHERE pil.index_lease_id = pilp.index_lease_id
AND pil.lease_id = pl.lease_id
AND pilp.index_period_id = p_index_lease_period_id;
SELECT lease_commencement_date,
lease_termination_date,
lease_extension_end_date
FROM pn_leases_all lease, pn_lease_details_all ldet
WHERE lease.lease_id = ldet.lease_id
AND lease.lease_id = p_lease_id;
SELECT *
FROM pn_payment_terms_all
WHERE index_period_id = p_index_period_id
AND end_date = l_term_date;
/* Delete from the Intermediate table PN_INDEX_LEASE_TERMS_ALL */
DELETE FROM pn_index_lease_terms_all ilt
WHERE ilt.index_period_id = c_rec.index_period_id
AND ilt.approved_flag <> c_payment_term_status_approved ;
DELETE FROM pn_distributions_all
WHERE payment_term_id IN (SELECT payment_term_id
FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = c_rec.index_period_id
AND ppt.status <> c_payment_term_status_approved);
DELETE FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = c_rec.index_period_id
AND ppt.status <> c_payment_term_status_approved;
pn_schedules_items.Insert_Payment_Term(
p_payment_term_rec => l_term_rec,
x_return_status => l_return_status,
x_return_message => op_msg);
UPDATE pn_payment_terms_all
SET end_date = l_ext_end_dt,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE payment_term_id = terms_rec.payment_term_id;
pn_schedules_items.Insert_Payment_Term(
p_payment_term_rec => l_term_rec,
x_return_status => l_return_status,
x_return_message => op_msg);
SELECT pil.index_lease_id
,pilp.index_period_id
,pil.lease_id
,pil.retain_initial_basis_flag
,pil.initial_basis
FROM pn_index_leases_all pil, pn_index_lease_periods_all pilp
WHERE pil.index_lease_id = pilp.index_lease_id
AND pil.index_lease_id = p_index_lease_id
ORDER BY pilp.line_number;
SELECT pil.index_lease_id
,pilp.index_period_id
,pil.lease_id
,pil.retain_initial_basis_flag
,pil.initial_basis
FROM pn_index_leases_all pil, pn_index_lease_periods_all pilp
WHERE pil.index_lease_id = pilp.index_lease_id
AND pil.index_lease_id = p_index_lease_id
AND pilp.index_period_id = p_index_lease_period_id
ORDER BY pilp.line_number;
UPDATE pn_index_leases_all
SET initial_basis = v_initial_basis
,last_update_date = SYSDATE
,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
WHERE index_lease_id = v_index_lease_id;
UPDATE pn_index_lease_periods_all
SET current_basis = v_basis_amount
,unconstraint_rent_due = v_uncontrained_index_amount
,constraint_rent_due = v_p_constrained_rent_amount
,index_percent_change = v_index_percent_change
,current_index_line_id = v_current_cpi_id
,current_index_line_value = v_current_cpi_value
,previous_index_line_id = v_previous_cpi_id
,previous_index_line_value = v_previous_cpi_value
,constraint_applied_amount = v_constraint_applied_amount
,carry_forward_amount = v_carry_forward_amount
,constraint_applied_percent = v_constraint_applied_percent
,carry_forward_percent = v_carry_forward_percent
,last_update_date = SYSDATE
,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
WHERE index_period_id = v_index_period_id;
SELECT pil.index_lease_id
,pilp.index_period_id
,pl.lease_class_code
,pl.lease_num
,pil.abstracted_by
,pil.location_id
,pilp.assessment_date
,pil.index_lease_number
,pil.term_template_id
,pil.org_id
FROM pn_leases_all pl, pn_index_leases pil, pn_index_lease_periods_all pilp
WHERE pl.lease_id = pil.lease_id
AND pil.index_lease_id = pilp.index_lease_id
AND (pil.index_lease_number >= nvl(p_index_rent_number_lower,pil.index_lease_number))
AND (pil.index_lease_number <= nvl(p_index_rent_number_upper,pil.index_lease_number))
AND (pl.lease_num = nvl(p_main_lease_number,pl.lease_num))
AND (pilp.assessment_date >= nvl(p_assessment_date_lower,pilp.assessment_date))
AND ((nvl(pil.carry_forward_flag,'N') = 'N' and
pilp.assessment_date <= nvl(p_assessment_date_upper,pilp.assessment_date)) OR
(nvl(pil.carry_forward_flag,'N') in ('A','P') and
pilp.assessment_date <= nvl(get_max_assessment_dt(pil.index_lease_id,p_assessment_date_upper),
pilp.assessment_date))
)
AND (pl.lease_class_code = nvl(p_lease_class,pl.lease_class_code))
AND (p_location_code is null OR pil.location_id IN
(SELECT location_id FROM pn_locations
START WITH location_code = p_location_code
CONNECT BY PRIOR location_id = parent_location_id)
)
AND (pil.abstracted_by = p_user_responsible OR
p_user_responsible is null);
PROCEDURE update_index_hist_line (
ip_index_history_line_id IN NUMBER
,ip_recalculate IN VARCHAR2
,op_msg OUT NOCOPY VARCHAR2
) IS
CURSOR index_periods (
p_index_history_line_id IN NUMBER
) IS
SELECT pilp.index_lease_id
,pilp.index_period_id
FROM pn_index_lease_periods_all pilp
WHERE ( pilp.previous_index_line_id = p_index_history_line_id
OR pilp.current_index_line_id = p_index_history_line_id
);
END update_index_hist_line;
PROCEDURE update_index_hist_line_batch (
errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY VARCHAR2
,ip_index_history_id IN NUMBER
,ip_recalculate IN VARCHAR2
) IS
CURSOR index_hist_lines_modified (
p_index_history_id IN NUMBER
) IS
SELECT pihl.index_line_id
,pihl.index_figure
,pihl.index_date
FROM pn_index_history_lines pihl
WHERE pihl.updated_flag = 'Y'
AND pihl.index_id = p_index_history_id;
SELECT pilp.index_lease_id
,pilp.index_period_id
,pilp.previous_index_line_id
,pilp.current_index_line_id
,pilp.current_index_line_value
,pilp.previous_index_line_value
,pilp.constraint_rent_due
FROM pn_index_lease_periods_all pilp
WHERE ( pilp.previous_index_line_id = p_index_history_line_id
OR pilp.current_index_line_id = p_index_history_line_id
);
v_updated_index_date pn_index_history_lines.index_date%TYPE;
v_updated_index_date := ihl_rec.index_date;
UPDATE pn_index_lease_periods_all
SET index_percent_change = v_index_percent_change
,current_index_line_value = v_current_cpi_value
,previous_index_line_value = v_previous_cpi_value
WHERE index_period_id = ilp_rec.index_period_id;
UPDATE pn_index_leases_all
SET base_index = v_new_index_figure
WHERE index_lease_id = ilp_rec.index_lease_id
AND base_year = v_updated_index_date;
UPDATE pn_index_history_lines
SET updated_flag = NULL
WHERE index_line_id = ihl_rec.index_line_id;
END update_index_hist_line_batch;
select include_in_var_rent
into l_include_in_var_rent
from pn_payment_terms_all
where payment_term_id = ip_index_pay_term_id;
update pn_payment_terms_all
set update_nbp_flag = 'Y'
where payment_term_id = ip_index_pay_term_id;
UPDATE pn_payment_terms_all
SET status = c_payment_term_status_approved
,include_in_var_rent = l_include_in_var_rent
,last_update_date = SYSDATE
,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
,approved_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
WHERE payment_term_id = ip_index_pay_term_id;
UPDATE pn_index_lease_terms_all
SET APPROVED_FLAG = c_payment_term_status_approved
,last_update_date = SYSDATE
,last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
WHERE rent_increase_term_id = ip_index_pay_term_id;
l_update_nbp_flag VARCHAR2(1);
SELECT pil.lease_id
,pil.index_lease_id
,pilp.index_period_id
,ppt.payment_term_id
,pl.lease_class_code
,pl.lease_num
,pl.status lease_status
,pil.index_lease_number
,pil.abstracted_by
,pil.location_id
,pilp.assessment_date
,pilp.line_number
,ppt.start_date
,ppt.actual_amount
,ppt.frequency_code
,ppt.end_date
,ppt.index_term_indicator
,ppt.status
,DECODE (ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE"
,ppt.schedule_day
FROM pn_leases_all pl
,pn_index_leases pil
,pn_index_lease_periods_all pilp
,pn_payment_terms_all ppt
WHERE pl.lease_id = pil.lease_id
AND pil.index_lease_id = pilp.index_lease_id
AND pilp.index_period_id = ppt.index_period_id
AND (pilp.index_period_id = p_index_period_id
OR p_index_period_id IS NULL)
AND (pil.index_lease_number BETWEEN
nvl(p_index_rent_number_lower, pil.index_lease_number) AND
nvl(p_index_rent_number_upper, pil.index_lease_number))
AND (pl.lease_num BETWEEN
nvl(p_main_lease_number_lower, pl.lease_num) AND
nvl(p_main_lease_number_upper, pl.lease_num))
AND (pilp.assessment_date BETWEEN
nvl(fnd_date.canonical_to_date (p_assessment_date_lower), pilp.assessment_date) AND
nvl(fnd_date.canonical_to_date (p_assessment_date_upper), pilp.assessment_date))
AND (pl.lease_class_code = p_lease_class
OR p_lease_class IS NULL)
AND (pil.location_id = p_location_code
OR p_location_code IS NULL)
AND (pil.abstracted_by = p_user_responsible
OR p_user_responsible IS NULL)
AND (ppt.start_date BETWEEN
nvl(fnd_date.canonical_to_date (p_payment_start_date_lower), ppt.start_date) AND
nvl(fnd_date.canonical_to_date (p_payment_start_date_upper), ppt.start_date))
AND ((p_approve_normalize_only = 'Y'
AND NVL (ppt.normalize, 'N') = 'Y')
OR p_approve_normalize_only = 'N')
AND ppt.status = p_payment_status;
SELECT var_rent_id
FROM pn_var_rents_all
WHERE lease_id = p1_lease_id;
SELECT UPDATE_NBP_FLAG
FROM PN_PAYMENT_TERMS_ALL
WHERE lease_id = p1_lease_id
FOR UPDATE NOWAIT;
select 'x'
FROM DUAL
where exists (select BKHD_DEFAULT_ID
from pn_var_bkpts_head_all
where period_id IN (select PERIOD_ID
FROM pn_var_periods_all
where VAR_RENT_ID = l_var_rent_id)
AND BKHD_DEFAULT_ID IS NOT NULL);
l_update_nbp_flag := NULL;
IF terms_rec.UPDATE_NBP_FLAG = 'Y' THEN
l_update_nbp_flag := 'Y';
IF l_update_nbp_flag = 'Y' THEN
FOR var_rec in var_cur(p1_lease_id => il_rec.lease_id)
LOOP
l_var_rent_id := var_rec.var_rent_id;
pnp_debug_pkg.log('Updated Natural Breakpoints for VR - '||var_rec.var_rent_id);
UPDATE pn_payment_terms_all
SET UPDATE_NBP_FLAG = NULL
WHERE lease_id = il_rec.lease_id;
/* Delete from table pn_index_exclude_term */
Delete pn_index_exclude_term_all
where index_lease_id = p_index_lease_id;
SELECT pilp.carry_forward_amount,
pilp.carry_forward_percent
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
AND pilp.assessment_date = (SELECT MAX (pilp.assessment_date)
FROM pn_index_lease_periods_all pilp
WHERE pilp.index_lease_id = p_index_lease_id
AND pilp.assessment_date < p_assessment_date);
SELECT unconstraint_rent_due, constraint_rent_due
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
AND assessment_date < p_assessment_date
ORDER BY assessment_date desc;
SELECT max(assessment_date)
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
AND assessment_date > p_assessment_date
AND constraint_rent_due is not null;
SELECT index_lease_id,
index_period_id,
assessment_date
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
AND assessment_date > p_assessment_date
AND assessment_date <= get_max_assessment_dt(index_lease_id,p_assessment_date);