[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM pn_opex_est_payments_all
WHERE est_payment_id = est_pay_trm_id;
SELECT *
FROM pn_term_templates_all
WHERE term_template_id = term_temp_id;
SELECT agr.* , loc.location_id
FROM pn_opex_agreements_all agr,
pn_locations_all loc,
pn_tenancies_all ten
WHERE agreement_id = agr_id
AND agr.tenancy_id = ten.tenancy_id
AND ten.location_id = loc.location_id;
SELECT *
FROM pn_distributions_all
WHERE term_template_id = term_temp_id;
SELECT * FROM pn_payment_terms_all
WHERE payment_term_id =
(SELECT MAX(payment_term_id) FROM pn_payment_terms_all
WHERE opex_agr_id = arg_id
AND opex_type = 'ESTPMT');
SELECT pl.lease_class_code,
pld.lease_change_id,
pl.org_id
INTO l_lease_class_code,
l_lease_change_id,
l_org_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;
pnp_debug_pkg.put_log_msg('inserting row 1');
pnp_debug_pkg.put_log_msg('x_last_update_login' || NVL(fnd_profile.value('LOGIN_ID'),0));
pnp_debug_pkg.put_log_msg('x_last_updated_by '|| NVL (fnd_profile.VALUE ('USER_ID'), 0));
pnt_payment_terms_pkg.insert_row (
x_rowid => l_rowid
,x_payment_term_id => l_est_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(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code)
,x_payment_term_type_code => NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code)
,x_frequency_code => NVL(agreement_rec.est_pay_freq_code , l_frequency)
,x_lease_id => p_lease_id
,x_lease_change_id => l_lease_change_id
,x_start_date => l_payment_start_date
,x_end_date => l_payment_end_date
,x_set_of_books_id => NVL(template_rec.set_of_books_id,l_set_of_books_id)
,x_currency_code => l_currency_code
,x_rate => 1 -- not used in application
,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
,x_vendor_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_id ELSE pay_term_rec.vendor_id END
,x_vendor_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_site_id ELSE pay_term_rec.vendor_site_id END
,x_target_date => NULL
,x_actual_amount => opex_est_pay_rec.est_pmt_amount
,x_estimated_amount => NULL
,x_attribute_category => NVL(opex_est_pay_rec.attribute_category , template_rec.attribute_category)
,x_attribute1 => NVL(opex_est_pay_rec.attribute1 , template_rec.attribute1)
,x_attribute2 => NVL(opex_est_pay_rec.attribute2 , template_rec.attribute2)
,x_attribute3 => NVL(opex_est_pay_rec.attribute3 , template_rec.attribute3)
,x_attribute4 => NVL(opex_est_pay_rec.attribute4 , template_rec.attribute4)
,x_attribute5 => NVL(opex_est_pay_rec.attribute5 , template_rec.attribute5)
,x_attribute6 => NVL(opex_est_pay_rec.attribute6 , template_rec.attribute6)
,x_attribute7 => NVL(opex_est_pay_rec.attribute7 , template_rec.attribute7)
,x_attribute8 => NVL(opex_est_pay_rec.attribute8 , template_rec.attribute8)
,x_attribute9 => NVL(opex_est_pay_rec.attribute9 , template_rec.attribute9)
,x_attribute10 => NVL(opex_est_pay_rec.attribute10 , template_rec.attribute10)
,x_attribute11 => NVL(opex_est_pay_rec.attribute11 , template_rec.attribute11)
,x_attribute12 => NVL(opex_est_pay_rec.attribute12 , template_rec.attribute12)
,x_attribute13 => NVL(opex_est_pay_rec.attribute13 , template_rec.attribute13)
,x_attribute14 => NVL(opex_est_pay_rec.attribute14 , template_rec.attribute14)
,x_attribute15 => NVL(opex_est_pay_rec.attribute15 , template_rec.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 => template_rec.customer_id
,x_customer_site_use_id => template_rec.customer_site_use_id
,x_normalize => 'N'
,x_location_id => agreement_rec.location_id
,x_schedule_day => l_schedule_day
,x_cust_ship_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_ship_site_id ELSE pay_term_rec.cust_ship_site_id END
,x_ap_ar_term_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.ap_ar_term_id ELSE pay_term_rec.ap_ar_term_id END
,x_cust_trx_type_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_trx_type_id ELSE pay_term_rec.cust_trx_type_id END
,x_project_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.project_id ELSE pay_term_rec.project_id END
,x_task_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.task_id ELSE pay_term_rec.task_id END
,x_organization_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.organization_id ELSE pay_term_rec.organization_id END
,x_expenditure_type => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_type ELSE pay_term_rec.expenditure_type END
,x_expenditure_item_date => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_item_date ELSE pay_term_rec.expenditure_item_date END
,x_tax_group_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_group_id ELSE pay_term_rec.tax_group_id END
,x_tax_code_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_tax_classification_code => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_classification_code ELSE pay_term_rec.tax_classification_code END
,x_tax_included => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_included ELSE pay_term_rec.tax_included END
,x_distribution_set_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.distribution_set_id ELSE pay_term_rec.distribution_set_id END
,x_inv_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.inv_rule_id ELSE pay_term_rec.inv_rule_id END
,x_account_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.account_rule_id ELSE pay_term_rec.account_rule_id END
,x_salesrep_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.salesrep_id ELSE pay_term_rec.salesrep_id END
,x_approved_by => NULL
,x_status => 'DRAFT'
,x_po_header_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_cust_po_number => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_receipt_method_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
--C ,x_calling_form => NULL
,x_org_id => l_org_id
,x_term_template_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.term_template_id ELSE pay_term_rec.term_template_id END
,x_area => l_area
,x_area_type_code => l_area_type_code
);
UPDATE pn_payment_terms_all
SET opex_agr_id = agreement_rec.agreement_id,
opex_type = 'ESTPMT'
WHERE payment_term_id = l_est_payment_term_id;
pnt_payment_terms_pkg.insert_row (
x_rowid => l_rowid
,x_payment_term_id => l_catch_up_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(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code)
,x_payment_term_type_code => NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code)
,x_frequency_code => 'OT'
,x_lease_id => p_lease_id
,x_lease_change_id => l_lease_change_id
,x_start_date => SYSDATE -- Defaulted to sysdate for catchup terms
,x_end_date => SYSDATE -- Defaulted to sysdate for catchup terms
,x_set_of_books_id => NVL(template_rec.set_of_books_id,l_set_of_books_id)
,x_currency_code => l_currency_code
,x_rate => 1 -- not used in application
,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
,x_vendor_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_id ELSE pay_term_rec.vendor_id END
,x_vendor_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_site_id ELSE pay_term_rec.vendor_site_id END
,x_target_date => NULL
,x_actual_amount => opex_est_pay_rec.catch_up_amount
,x_estimated_amount => NULL
,x_attribute_category => NVL(opex_est_pay_rec.attribute_category , template_rec.attribute_category)
,x_attribute1 => NVL(opex_est_pay_rec.attribute1 , template_rec.attribute1)
,x_attribute2 => NVL(opex_est_pay_rec.attribute2 , template_rec.attribute2)
,x_attribute3 => NVL(opex_est_pay_rec.attribute3 , template_rec.attribute3)
,x_attribute4 => NVL(opex_est_pay_rec.attribute4 , template_rec.attribute4)
,x_attribute5 => NVL(opex_est_pay_rec.attribute5 , template_rec.attribute5)
,x_attribute6 => NVL(opex_est_pay_rec.attribute6 , template_rec.attribute6)
,x_attribute7 => NVL(opex_est_pay_rec.attribute7 , template_rec.attribute7)
,x_attribute8 => NVL(opex_est_pay_rec.attribute8 , template_rec.attribute8)
,x_attribute9 => NVL(opex_est_pay_rec.attribute9 , template_rec.attribute9)
,x_attribute10 => NVL(opex_est_pay_rec.attribute10 , template_rec.attribute10)
,x_attribute11 => NVL(opex_est_pay_rec.attribute11 , template_rec.attribute11)
,x_attribute12 => NVL(opex_est_pay_rec.attribute12 , template_rec.attribute12)
,x_attribute13 => NVL(opex_est_pay_rec.attribute13 , template_rec.attribute13)
,x_attribute14 => NVL(opex_est_pay_rec.attribute14 , template_rec.attribute14)
,x_attribute15 => NVL(opex_est_pay_rec.attribute15 , template_rec.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 => template_rec.customer_id
,x_customer_site_use_id => template_rec.customer_site_use_id
,x_normalize => 'N'
,x_location_id => agreement_rec.location_id
,x_schedule_day => l_schedule_day
,x_cust_ship_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_ship_site_id ELSE pay_term_rec.cust_ship_site_id END
,x_ap_ar_term_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.ap_ar_term_id ELSE pay_term_rec.ap_ar_term_id END
,x_cust_trx_type_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_trx_type_id ELSE pay_term_rec.cust_trx_type_id END
,x_project_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.project_id ELSE pay_term_rec.project_id END
,x_task_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.task_id ELSE pay_term_rec.task_id END
,x_organization_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.organization_id ELSE pay_term_rec.organization_id END
,x_expenditure_type => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_type ELSE pay_term_rec.expenditure_type END
,x_expenditure_item_date => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_item_date ELSE pay_term_rec.expenditure_item_date END
,x_tax_group_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_group_id ELSE pay_term_rec.tax_group_id END
,x_tax_code_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_tax_classification_code => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_classification_code ELSE pay_term_rec.tax_classification_code END
,x_tax_included => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_included ELSE pay_term_rec.tax_included END
,x_distribution_set_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.distribution_set_id ELSE pay_term_rec.distribution_set_id END
,x_inv_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.inv_rule_id ELSE pay_term_rec.inv_rule_id END
,x_account_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.account_rule_id ELSE pay_term_rec.account_rule_id END
,x_salesrep_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.salesrep_id ELSE pay_term_rec.salesrep_id END
,x_approved_by => NULL
,x_status => 'DRAFT'
,x_po_header_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_cust_po_number => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_receipt_method_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
--C ,x_calling_form => NULL
,x_org_id => l_org_id
,x_term_template_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.term_template_id ELSE pay_term_rec.term_template_id END
,x_area => l_area
,x_area_type_code => l_area_type_code
);
UPDATE pn_payment_terms_all
SET opex_agr_id = agreement_rec.agreement_id,
opex_type = 'CATCHUP'
WHERE payment_term_id = l_catch_up_payment_term_id;
l_context := 'Inserting into pn_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_est_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
);
l_context := 'Inserting into pn_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_catch_up_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
);
SELECT *
FROM pn_opex_est_payments_all
WHERE est_payment_id = est_pay_trm_id;
SELECT *
FROM pn_term_templates_all
WHERE term_template_id = term_temp_id;
SELECT agr.* , loc.location_id
FROM pn_opex_agreements_all agr,
pn_locations_all loc,
pn_tenancies_all ten
WHERE agreement_id = agr_id
AND agr.tenancy_id = ten.tenancy_id
AND ten.location_id = loc.location_id;
SELECT *
FROM pn_distributions_all
WHERE term_template_id = term_temp_id;
SELECT * FROM pn_payment_terms_all
WHERE payment_term_id =
(SELECT MAX(payment_term_id) FROM pn_payment_terms_all
WHERE opex_agr_id = arg_id
AND opex_type = 'ESTPMT');
SELECT pl.lease_class_code,
pld.lease_change_id,
pl.org_id
INTO l_lease_class_code,
l_lease_change_id,
l_org_id
FROM pn_leases_all pl,
pn_lease_details_all pld
WHERE pl.lease_id = pld.lease_id
AND pld.lease_id = agreement_rec.lease_id;
pnp_debug_pkg.put_log_msg('inserting row 1');
pnp_debug_pkg.put_log_msg('x_last_update_login' || NVL(fnd_profile.value('LOGIN_ID'),0));
pnp_debug_pkg.put_log_msg('x_last_updated_by '|| NVL (fnd_profile.VALUE ('USER_ID'), 0));
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(agreement_rec.payment_purpose_code ,template_rec.payment_purpose_code)
,x_payment_term_type_code => NVL(agreement_rec.payment_type_code , template_rec.payment_term_type_code)
,x_frequency_code => l_frequency
,x_lease_id => agreement_rec.lease_id
,x_lease_change_id => l_lease_change_id
,x_start_date => l_payment_start_date
,x_end_date => l_payment_end_date
,x_set_of_books_id => NVL(template_rec.set_of_books_id,l_set_of_books_id)
,x_currency_code => l_currency_code
,x_rate => 1 -- not used in application
,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
,x_vendor_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_id ELSE pay_term_rec.vendor_id END
,x_vendor_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.vendor_site_id ELSE pay_term_rec.vendor_site_id END
,x_target_date => NULL
,x_actual_amount => p_amount
,x_estimated_amount => NULL
,x_attribute_category => template_rec.attribute_category
,x_attribute1 => template_rec.attribute1
,x_attribute2 => template_rec.attribute2
,x_attribute3 => template_rec.attribute3
,x_attribute4 => template_rec.attribute4
,x_attribute5 => template_rec.attribute5
,x_attribute6 => template_rec.attribute6
,x_attribute7 => template_rec.attribute7
,x_attribute8 => template_rec.attribute8
,x_attribute9 => template_rec.attribute9
,x_attribute10 => template_rec.attribute10
,x_attribute11 => template_rec.attribute11
,x_attribute12 => template_rec.attribute12
,x_attribute13 => template_rec.attribute13
,x_attribute14 => template_rec.attribute14
,x_attribute15 => template_rec.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 => template_rec.customer_id
,x_customer_site_use_id => template_rec.customer_site_use_id
,x_normalize => 'N'
,x_location_id => agreement_rec.location_id
,x_schedule_day => l_schedule_day
,x_cust_ship_site_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_ship_site_id ELSE pay_term_rec.cust_ship_site_id END
,x_ap_ar_term_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.ap_ar_term_id ELSE pay_term_rec.ap_ar_term_id END
,x_cust_trx_type_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.cust_trx_type_id ELSE pay_term_rec.cust_trx_type_id END
,x_project_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.project_id ELSE pay_term_rec.project_id END
,x_task_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.task_id ELSE pay_term_rec.task_id END
,x_organization_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.organization_id ELSE pay_term_rec.organization_id END
,x_expenditure_type => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_type ELSE pay_term_rec.expenditure_type END
,x_expenditure_item_date => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.expenditure_item_date ELSE pay_term_rec.expenditure_item_date END
,x_tax_group_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_group_id ELSE pay_term_rec.tax_group_id END
,x_tax_code_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_tax_classification_code => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_classification_code ELSE pay_term_rec.tax_classification_code END
,x_tax_included => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_included ELSE pay_term_rec.tax_included END
,x_distribution_set_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.distribution_set_id ELSE pay_term_rec.distribution_set_id END
,x_inv_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.inv_rule_id ELSE pay_term_rec.inv_rule_id END
,x_account_rule_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.account_rule_id ELSE pay_term_rec.account_rule_id END
,x_salesrep_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.salesrep_id ELSE pay_term_rec.salesrep_id END
,x_approved_by => NULL
,x_status => 'DRAFT'
,x_po_header_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_cust_po_number => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
,x_receipt_method_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.tax_code_id ELSE pay_term_rec.tax_code_id END
--C ,x_calling_form => NULL
,x_org_id => l_org_id
,x_term_template_id => CASE WHEN pay_term_rec.payment_term_id IS NULL THEN template_rec.term_template_id ELSE pay_term_rec.term_template_id END
,x_area => l_area
,x_area_type_code => l_area_type_code
);
UPDATE pn_payment_terms_all
SET opex_recon_id = p_recon_id,
opex_agr_id = p_agreement_id,
opex_type = 'RECON'
WHERE payment_term_id = l_payment_term_id;
l_context := 'Inserting into pn_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
);
SELECT *
FROM pn_opex_est_payments_all
WHERE est_payment_id = est_pay_trm_id;
SELECT payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = p1_lease_id
and payment_status_lookup_code = 'DRAFT'
AND schedule_date >= pl_contract_sch_date;
SELECT
pmt.lease_id,
ADD_MONTHS(TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy'),
DECODE(max(pmt.frequency_code),'MON', 0, 'QTR', 2, 'SA', 5 , 'YR', 11)) as last_sch_date
FROM pn_payment_terms_all pmt,
pn_payment_schedules_all sch
WHERE sch.payment_status_lookup_code = 'APPROVED'
AND pmt.payment_term_id = c_pay_term_id
AND sch.lease_id = c_lease_id
AND pmt.lease_id = c_lease_id
GROUP BY pmt.lease_id;
SELECT
pmt.lease_id,
(TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy')) as last_sch_date
FROM pn_payment_terms_all pmt,
pn_payment_schedules_all sch
WHERE sch.payment_status_lookup_code = 'APPROVED'
AND pmt.payment_term_id = c_pay_term_id
AND sch.lease_id = c_lease_id
AND pmt.lease_id = c_lease_id
GROUP BY pmt.lease_id;*/
SELECT * FROM
pn_payment_terms_all
WHERE payment_term_id = pay_term_id;
SELECT payment_term_id INTO prev_pay_term_id
FROM pn_opex_est_payments_all
WHERE est_payment_id <> p_est_payment_id
AND AGREEMENT_ID = opex_est_pay_rec.agreement_id
AND END_DATE = (
SELECT MAX(END_DATE) FROM pn_opex_est_payments_all
WHERE AGREEMENT_ID = opex_est_pay_rec.agreement_id
AND est_payment_id <> p_est_payment_id)
AND ROWNUM = 1;
DELETE FROM pn_payment_items_all
WHERE payment_term_id = prev_pay_term_id
AND payment_schedule_id = l_sch_id
AND payment_item_type_lookup_code = 'CASH';
DELETE FROM pn_payment_schedules_all
WHERE NOT EXISTS (SELECT NULL
FROM pn_payment_items_all
WHERE payment_schedule_id = l_sch_id)
AND payment_schedule_id = l_sch_id;
UPDATE pn_opex_est_payments_all
SET END_DATE = prev_trm_end_date
Where payment_term_id = prev_pay_term_id;
UPDATE pn_payment_terms_all
SET END_DATE = prev_trm_end_date
Where payment_term_id = prev_pay_term_id;
SELECT (to_date((substr(to_char(max(pmt.start_date),'dd-mm-yyyy'),1,2) || substr(to_char(max(sch.schedule_date),'dd-mm-yyyy'),3)), 'dd-mm-yyyy')) schedule_date
FROM pn_payment_terms_all pmt,
pn_payment_schedules_all sch,
pn_opex_est_payments_all est
WHERE pmt.payment_term_id = est.payment_term_id
AND est.est_payment_id = p_payment_term_id
AND sch.payment_status_lookup_code = 'APPROVED'
AND sch.lease_id = p_lease_id;
SELECT DECODE(pmt.FREQUENCY_CODE, 'MON', 1, 'QTR', 3, 'SA', 6 , 'YR', 12) frequency
FROM pn_payment_terms_all pmt
WHERE pmt.payment_term_id = p_pay_trm_id;
SELECT payment_term_id
FROM pn_opex_est_payments_all
WHERE est_payment_id = est_pay_trm_id;
SELECT
pmt.lease_id,
ADD_MONTHS(TO_DATE((TO_CHAR(MAX(pmt.start_date),'dd') || '-' || to_char(max(sch.schedule_date) , 'mm-yyyy')),'dd-mm-yyyy'),
DECODE(max(pmt.frequency_code),'MON', 0, 'QTR', 2, 'SA', 5 , 'YR', 11)) as last_sch_date
FROM pn_payment_terms_all pmt,
pn_payment_schedules_all sch
WHERE sch.payment_status_lookup_code = 'APPROVED'
AND pmt.payment_term_id = c_pay_term_id
AND sch.lease_id = c_lease_id
AND pmt.lease_id = c_lease_id
GROUP BY pmt.lease_id;
SELECT (to_date((substr(to_char(max(pmt.start_date),'dd-mm-yyyy'),1,2) || substr(to_char(max(sch.schedule_date),'dd-mm-yyyy'),3)), 'dd-mm-yyyy')) schedule_date
FROM pn_payment_terms_all pmt,
pn_payment_schedules_all sch
WHERE pmt.payment_term_id = p_payment_term_id
AND sch.payment_status_lookup_code = 'APPROVED'
AND sch.lease_id = p_lease_id;
SELECT DECODE(pmt.FREQUENCY_CODE, 'MON', 1, 'QTR', 3, 'SA', 6 , 'YR', 12) frequency
FROM pn_payment_terms_all pmt
WHERE pmt.payment_term_id = p_payment_term_id;
SELECT EST_PMT_AMOUNT INTO amount
FROM pn_opex_est_payments_all
WHERE agreement_id = agr_id
AND END_DATE IN
(SELECT MAX(END_DATE) FROM
pn_opex_est_payments_all
WHERE agreement_id = agr_id)
AND ROWNUM = 1;
SELECT est_payment_id
FROM pn_opex_est_payments_all
WHERE agreement_id = p_agr_id
AND END_DATE IN
(SELECT MAX(END_DATE) FROM
pn_opex_est_payments_all
WHERE agreement_id = p_agr_id);
SELECT * INTO x_recon_id
FROM (SELECT recon_id FROM pn_opex_recon_all
WHERE agreement_id = agr_id
ORDER BY period_end_dt DESC , revision_number DESC)
WHERE ROWNUM = 1 ;
SELECT loc.property_id prop_id
FROM pn_locations_all loc
WHERE loc.parent_location_id IS NULL
START WITH loc.location_id = loc_id
CONNECT BY PRIOR loc.parent_location_id=loc.location_id;
SELECT * FROM
pn_opex_critical_dates_all
WHERE agreement_id = agr_id
AND critical_date_type_code = 'RSDFL';
SELECT * FROM
pn_opex_recon_all
WHERE recon_id = p_recon_id;
SELECT DECODE (st_due_rec.event_code ,
'RS' , recon_rec.period_start_dt ,
'RE' , recon_rec.period_end_dt ,
'CS' , TO_DATE('01-01-'||TO_CHAR(recon_rec.period_end_dt,'YYYY'),'DD-MM-YYYY'),
'ST' , recon_rec.st_recv_dt ,
null) INTO working_date FROM DUAL;
SELECT DECODE (st_due_rec.when_code , 'A',DECODE (st_due_rec.time_unit_code,
'M' , ADD_MONTHS(working_date , NVL(st_due_rec.time_unit,0)),
'D' , working_date + NVL(st_due_rec.time_unit,0),
'Y' , ADD_MONTHS(working_date , NVL(st_due_rec.time_unit,0) * 12),
'W' , working_date + NVL(st_due_rec.time_unit,0)*7 ,
null),--default
'B' ,DECODE (st_due_rec.time_unit_code,
'M' , ADD_MONTHS(working_date , -NVL(st_due_rec.time_unit,0)),
'D' , working_date + -NVL(st_due_rec.time_unit,0),
'Y' , ADD_MONTHS(working_date , -NVL(st_due_rec.time_unit,0) * 12),
'W' , working_date + -NVL(st_due_rec.time_unit,0)*7,
null) -- default
, null) INTO working_date from dual;
PROCEDURE delete_agreement (p_agreement_id IN NUMBER
,x_return_status IN OUT NOCOPY VARCHAR2)
IS
l_deletion_allowed VARCHAR2(1) := 'N';
SELECT 'N' INTO l_deletion_allowed
FROM DUAL WHERE EXISTS
(SELECT payment_term_id
FROM pn_payment_terms_all
WHERE opex_agr_id = p_agreement_id
AND status = 'APPROVED');
DELETE FROM PN_OPEX_NOTES_ALL
WHERE agreement_id = p_agreement_id;
DELETE FROM PN_OPEX_CRITICAL_DATES_ALL
WHERE agreement_id = p_agreement_id;
DELETE FROM PN_OPEX_EXP_GRPS_ALL
WHERE agreement_id = p_agreement_id;
DELETE FROM PN_OPEX_PRORAT_BASIS_DTLS_ALL
WHERE agreement_id = p_agreement_id;
DELETE FROM PN_OPEX_EST_PAYMENTS_ALL
WHERE agreement_id = p_agreement_id;
FOR i IN (SELECT recon_id FROM
pn_opex_recon_all WHERE agreement_id = p_agreement_id) LOOP
DELETE FROM PN_OPEX_RECON_CRDT_ALL
WHERE recon_id = i.recon_id;
DELETE FROM PN_OPEX_RECON_PRTBS_ALL
WHERE recon_id = i.recon_id;
DELETE FROM PN_OPEX_RECON_EXP_GRP_ALL
WHERE recon_id = i.recon_id;
DELETE FROM PN_OPEX_RECON_DETAILS_ALL
WHERE recon_id = i.recon_id;
DELETE FROM PN_OPEX_NOTES_ALL
WHERE recon_id = i.recon_id;
DELETE FROM PN_PAYMENT_TERMS_ALL
WHERE opex_agr_id = p_agreement_id;
DELETE FROM PN_OPEX_AGREEMENTS_ALL
WHERE agreement_id = p_agreement_id;
DELETE FROM PN_OPEX_RECON_ALL
WHERE agreement_id = p_agreement_id;
DELETE FROM PN_OPEX_EST_PAYMENTS_ALL
WHERE agreement_id = p_agreement_id;
pnp_debug_pkg.debug ('Agreemnt cannot be deleted');
END delete_agreement;
select recon_id , ten_tot_charge INTO rec_id,ten_tot_crg FROM pn_opex_recon_all WHERE
agreement_id = p_agr_id
AND period_end_dt + 1 = p_period_start_dt
AND current_flag = 'Y';
UPDATE pn_payment_terms_all
SET status = 'APPROVED'
,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_opex_pay_term_id;
SELECT popex.lease_id,
popex.agreement_id,
ppt.payment_term_id,
pl.lease_num,
popex.agr_num,
ppt.start_date,
ppt.actual_amount,
ppt.frequency_code,
ppt.end_date,
ppt.status,
ppt.schedule_day,
ppt.currency_code,
DECODE(ppt.normalize, 'Y', 'NORMALIZE') "NORMALIZE",
prop.property_id,
loc.location_code,
popex.created_by
FROM pn_leases_all pl,
pn_opex_agreements_all popex,
pn_payment_terms_all ppt,
pn_properties_all prop,
pn_locations_all loc,
pn_tenancies_all ten
WHERE pl.lease_id = popex.lease_id
AND popex.agreement_id = ppt.opex_agr_id
AND popex.tenancy_id = ten.tenancy_id
AND ten.location_id = loc.location_id
AND prop.property_id(+) = pn_opex_terms_pkg.get_prop_id(ppt.location_id)
AND(popex.agr_num BETWEEN nvl(p_agreement_number_lower, popex.agr_num) AND nvl(p_agreement_number_upper, popex.agr_num))
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(loc.location_code BETWEEN nvl(p_location_code_lower, loc.location_code)AND nvl(p_location_code_upper, loc.location_code))
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 popex.created_by = nvl(p_user_responsible, popex.created_by)
AND ppt.status = p_payment_status
AND(p_payment_function IS NULL OR
(p_payment_function = 'RECON' AND ppt.opex_type = 'RECON' AND ppt.opex_agr_id IS NOT NULL AND ppt.opex_recon_id IS NOT NULL) OR
(p_payment_function = 'CATCHUP' AND ppt.opex_type = 'CATCHUP' AND ppt.opex_agr_id IS NOT NULL AND ppt.opex_recon_id IS NULL) OR
(p_payment_function = 'ESTPMT' AND ppt.opex_type = 'ESTPMT' AND ppt.opex_agr_id IS NOT NULL AND ppt.opex_recon_id IS NULL) OR
(p_payment_function = 'ESTPMT_AND_CATCHUP' AND ppt.opex_type IN('ESTPMT', 'CATCHUP') AND ppt.opex_agr_id IS NOT NULL AND ppt.opex_recon_id IS NULL) OR
(p_payment_function = 'ALL' AND ppt.opex_type IN('ESTPMT', 'CATCHUP', 'RECON') AND ppt.opex_agr_id IS NOT NULL))
AND(p_property_code_ret_by_id IS NULL OR p_property_code_ret_by_id = prop.property_id)
AND nvl(pl.status, 'D') = 'F';
SELECT SUM(item.actual_amount) act_amt
FROM pn_payment_items_all item,
pn_payment_terms_all term,
pn_opex_recon_all recon
WHERE item.payment_item_type_lookup_code = 'CASH'
AND item.payment_term_id = term.payment_term_id
AND term.opex_recon_id = recon.recon_id
AND recon.recon_id = c_recon_id ;
SELECT * FROM
pn_opex_recon_all WHERE recon_id = c_recon_id;
SELECT NVL(expected_ovr , expected) AS amt FROM
pn_opex_recon_details_all
WHERE recon_id = c_recon_id
AND TYPE = '1PRP';
SELECT amount_st,
recoverable_st
FROM pn_opex_recon_exp_grp_all
WHERE recon_id = c_recon_id;