[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT '1'
INTO v
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM pn_index_lease_periods_all pilp
,pn_payment_terms_all ppt
WHERE pilp.index_period_id = ppt.index_period_id
AND ppt.status = 'APPROVED'
AND pilp.index_lease_id = p_index_lease_id
AND ppt.index_period_id > l_zero_period
AND ( p_chk_index_ind = 'Y'
AND ppt.index_term_indicator IN
(
pn_index_amount_pkg.c_index_pay_term_type_recur
,pn_index_amount_pkg.c_index_pay_term_type_backbill)
OR NVL (p_chk_index_ind, 'N') = 'N'));
SELECT '1'
INTO v
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = p_index_lease_period_id
AND ppt.status = 'APPROVED'
AND ( p_chk_index_ind = 'Y'
AND ppt.index_term_indicator IN
(
pn_index_amount_pkg.c_index_pay_term_type_recur
,pn_index_amount_pkg.c_index_pay_term_type_backbill)
OR NVL (p_chk_index_ind, 'N') = 'N'));
SELECT '1'
INTO v
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM pn_payment_items_all ppi, pn_payment_terms_all ppt
WHERE ppt.payment_term_id = ppi.payment_term_id
AND ppi.payment_item_type_lookup_code = 'CASH'
AND ( ppi.transferred_to_ap_flag = 'Y'
OR ppi.transferred_to_ar_flag = 'Y')
AND ppt.index_period_id = ip_index_period_id);
SELECT pl.lease_id
,pl.lease_num
,pl.lease_class_code
,ppt.payment_term_id
,ppt.payment_purpose_code
,ppt.payment_term_type_code
,ppt.frequency_code
,ppt.start_date
,ppt.end_date
,ppt.schedule_day
,ppt.vendor_id
,ppt.vendor_site_id
,ppt.customer_id
,ppt.customer_site_use_id
,ppt.code_combination_id
FROM pn_leases_all pl, pn_payment_terms_all ppt
WHERE pl.lease_id = ppt.lease_id
AND ppt.payment_term_id = ip_payment_term_id;
SELECT lines.index_figure
INTO v_base_index
FROM pn_index_history_lines lines, pn_index_history_headers headers
WHERE lines.index_id = headers.index_id
AND TO_CHAR (lines.index_date, 'MON-RRRR') = TO_CHAR (p_base_year, 'MON-RRRR')
AND lines.index_id = p_index_id;
SELECT *
FROM pn_index_leases_all
WHERE index_lease_id = p_index_lease_id;
SELECT *
FROM pn_index_lease_periods_all
WHERE index_period_id = p_index_period_id;
SELECT index_leases.commencement_date, lease_det.lease_termination_date
FROM pn_index_lease_periods_all periods, pn_index_leases_all index_leases,
pn_leases_all leases, pn_lease_details_all lease_det
WHERE periods.index_period_id = p_index_period_id
AND index_leases.index_lease_id = periods.index_lease_id
AND index_leases.lease_id = leases.lease_id
AND leases.lease_id = lease_det.lease_id
AND rownum = 1;
SELECT *
FROM pn_index_history_lines
WHERE index_line_id = p_index_line_id;
SELECT name
INTO l_name
FROM ra_terms
WHERE term_id = p_term_id;
SELECT name
INTO l_name
FROM ap_terms
WHERE term_id = p_term_id;
SELECT name
INTO l_name
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_cust_trx_type_id;
SELECT current_basis
,constraint_rent_due
,unconstraint_rent_due
,index_percent_change
,current_index_line_id
,current_index_line_value
,previous_index_line_id
,previous_index_line_value
INTO l_index_lease_periods_rec
FROM pn_index_lease_periods_all
WHERE index_period_id = p_index_period_id;
SELECT lease_change_id
INTO l_lease_change_id
FROM pn_lease_changes_all
WHERE lease_change_number IS NULL
AND lease_id = p_lease_id;
PROCEDURE delete_index_payment_term (
p_index_period_id IN NUMBER
,p_payment_term_id IN NUMBER
,p_msg OUT NOCOPY VARCHAR2) IS
l_tax_data_rec tax_data_rec;
DELETE FROM pn_payment_terms_all
WHERE ( payment_term_id = p_payment_term_id
OR p_payment_term_id IS NULL)
AND index_period_id = p_index_period_id;
END delete_index_payment_term;
SELECT 1
INTO l_period_exists
FROM DUAL
WHERE EXISTS ( SELECT periods.index_period_id
FROM pn_index_lease_periods_all periods
WHERE periods.index_lease_id = p_index_lease_id);
SELECT 1
INTO l_term_exists
FROM DUAL
WHERE EXISTS ( SELECT ppt.payment_term_id
FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = p_index_period_id);
SELECT 1
INTO l_term_exists
FROM DUAL
WHERE EXISTS ( SELECT ppt.payment_term_id
FROM pn_payment_terms_all ppt
WHERE ppt.index_period_id = p_index_period_id
AND ppt.status = 'APPROVED');
SELECT 1
INTO l_template_used
FROM DUAL
WHERE EXISTS ( SELECT pil.index_lease_id
FROM pn_index_leases_all pil, pn_term_templates_all ptt
WHERE ptt.term_template_id = pil.term_template_id
AND ptt.term_template_id = p_term_template_id);
SELECT status
INTO l_term_status
FROM pn_payment_terms_all
WHERE payment_term_id = p_payment_term_id;
SELECT segment1
INTO l_po_number
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT name
INTO l_receipt_method
FROM ar_receipt_methods
WHERE receipt_method_id = p_receipt_method_id;
SELECT location_code
INTO l_location_code
FROM pn_locations_all
WHERE location_id = p_location_id
AND ROWNUM < 2;
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_CHAR (phl.index_date, 'Mm-YYYY') =
TO_CHAR (p_index_finder_date, 'Mm-YYYY');
SELECT name
INTO l_term_template
FROM pn_term_templates_all
WHERE term_template_id = p_term_template_id;
SELECT user_name
INTO l_approver
FROM fnd_user
WHERE user_id = p_approved_by;
SELECT lease_class_code
INTO l_lease_class
FROM pn_leases_all
WHERE lease_id = p_lease_id;
SELECT name
FROM ra_salesreps
WHERE salesrep_id = p_salesrep_id
AND org_id = p_org_id;
SELECT name
INTO l_invoicing_rule
FROM ra_rules
WHERE rule_id = p_rule_id;
SELECT name
INTO l_accounting_rule
FROM ra_rules
WHERE rule_id = p_rule_id;
SELECT distribution_set_name
INTO l_distribution_set
FROM ap_distribution_sets_all
WHERE distribution_set_id = p_distribution_set_id;
SELECT projects.name project_name
,org.name organization
FROM pa_projects_all projects, hr_organization_units org
WHERE projects.project_id = p_project_id
AND projects.carrying_out_organization_id = org.organization_id;
SELECT 1
INTO l_hist_line_used
FROM DUAL
WHERE EXISTS ( SELECT periods.previous_index_line_id
FROM pn_index_lease_periods_all periods
,pn_index_history_lines lines
WHERE periods.previous_index_line_id = lines.index_line_id
AND lines.index_line_id = p_index_line_id);
SELECT 2
INTO l_hist_line_used
FROM DUAL
WHERE EXISTS ( SELECT periods.current_index_line_id
FROM pn_index_lease_periods_all periods
,pn_index_history_lines lines
WHERE periods.current_index_line_id = lines.index_line_id
AND lines.index_line_id = p_index_line_id);
SELECT 1
INTO l_calc_exists
FROM DUAL
WHERE EXISTS ( SELECT terms.payment_term_id
FROM pn_payment_terms_all terms
,pn_index_leases_all lease
,pn_index_lease_periods_all periods
WHERE terms.index_period_id = periods.index_period_id
AND periods.index_lease_id = lease.index_lease_id
AND lease.index_lease_id = p_index_lease_id);
SELECT 1
INTO l_basis_exists
FROM DUAL
WHERE EXISTS ( SELECT current_basis
FROM pn_index_lease_periods_all
WHERE index_lease_id = p_index_lease_id
AND current_basis is null);
SELECT max(SCHEDULE_DATE)
INTO l_max_sch_date
FROM pn_index_lease_periods_all pilp,
pn_payment_terms_all ppt,
pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE pilp.index_period_id=ppt.index_period_id
AND ppt.payment_term_id=ppi.payment_term_id
AND pps.payment_schedule_id=ppi.payment_schedule_id
AND (ppi.export_to_ar_flag='Y' OR
ppi.export_to_ap_flag='Y' )
AND pilp.index_lease_id=p_index_leaseId;
SELECT name
INTO l_project_name
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT 'N' ,index_exclude_term_id
INTO p_exclude_flag,p_index_exclude_term_id
FROM pn_index_exclude_term_all
WHERE index_lease_id=p_index_lease_id
AND payment_term_id=p_payment_term_id;
SELECT name
INTO l_organization_name
FROM pa_organizations_expend_v
WHERE organization_id = p_organization_id;
PROCEDURE UPDATE_LOCATION_FOR_IR_TERMS(
p_index_lease_id IN NUMBER,
p_location_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR C_UPD_TERMS IS
SELECT ppt.payment_term_id,
NVL(ppt.status, 'X')
FROM PN_PAYMENT_TERMS_ALL ppt,
PN_INDEX_LEASE_PERIODS_ALL pilp
WHERE ppt.status = 'DRAFT'
AND ppt.index_period_id = pilp.index_period_id
AND pilp.index_lease_id = p_index_lease_id
UNION ALL
SELECT ppt.payment_term_id,
NVL(ppt.status, 'X')
FROM PN_PAYMENT_TERMS_ALL ppt,
PN_INDEX_LEASE_PERIODS_ALL pilp,
PN_LEASES_ALL pl
WHERE ppt.status = 'APPROVED'
AND ppt.lease_id = pl.lease_id
AND ppt.index_period_id = pilp.index_period_id
AND pilp.index_lease_id = p_index_lease_id
AND EXISTS (SELECT NULL
FROM PN_PAYMENT_ITEMS_ALL ppi
WHERE DECODE(pl.lease_class_code,
'DIRECT', NVL(ppi.transferred_to_ap_flag,'N'),
'THIRD_PARTY', NVL(ppi.transferred_to_ar_flag,'N'),
'SUB_LEASE', NVL(ppi.transferred_to_ar_flag,'N')) = 'N'
AND ppi.payment_term_id = ppt.payment_term_id);
PNP_DEBUG_PKG.debug ('PN_INDEX_LEASE_COMMON_PKG.UPDATE_LOCATION_FOR_IR_TERMS (+)');
UPDATE PN_PAYMENT_TERMS_ALL
SET location_id = p_location_id
WHERE payment_term_id = l_payment_term_id(i);
PNP_DEBUG_PKG.debug ('PN_INDEX_LEASE_COMMON_PKG.UPDATE_LOCATION_FOR_IR_TERMS (-)');
END UPDATE_LOCATION_FOR_IR_TERMS;
SELECT name
INTO l_name
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = p_cust_trx_type_id
AND org_id = p_org_id;
SELECT segment1
INTO l_po_number
FROM po_headers_all
WHERE po_header_id = p_po_header_id
AND org_id = p_org_id;
SELECT distribution_set_name
INTO l_distribution_set
FROM ap_distribution_sets_all
WHERE distribution_set_id = p_distribution_set_id
AND org_id = p_org_id;
SELECT name
INTO l_project_name
FROM pa_projects_all
WHERE project_id = p_project_id
AND org_id = p_org_id;