[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pl.lease_id
, pl.payment_term_proration_rule
, pt.location_id
, pld.lease_commencement_date
, pld.lease_termination_date
FROM pn_leases_all pl
, pn_tenancies_all pt
, pn_lease_details_all pld
, fnd_user fnr
, fnd_lookups lse_lkp
, fnd_lookups flg_lkp
, pn_locations_all ploc
, pn_properties_all prop
WHERE pt.lease_id(+) = pl.lease_id
AND ploc.location_id(+) = pt.location_id
AND prop.property_id(+) = ploc.property_id
AND NVL (pt.occupancy_date
, pt.estimated_occupancy_date
) BETWEEN ploc.active_start_date(+) AND ploc.active_end_date(+)
AND pl.lease_id = pld.lease_id
AND fnr.user_id = pld.responsible_user
AND lse_lkp.lookup_code = pl.status
AND lse_lkp.lookup_type = 'PN_LEASE_STATUS_TYPE'
AND flg_lkp.lookup_code = pt.primary_flag
AND flg_lkp.lookup_type = 'PN_YES_NO'
AND pl.org_id = p_org_id
AND pl.lease_id = x_lease_id;
SELECT pl.lease_id
, pl.payment_term_proration_rule
, pt.location_id
, pld.lease_commencement_date
, pld.lease_termination_date
FROM pn_leases_all pl
, pn_tenancies_all pt
, pn_lease_details_all pld
, fnd_user fnr
, fnd_lookups lse_lkp
, fnd_lookups flg_lkp
, pn_locations_all ploc
, pn_properties_all prop
WHERE pt.lease_id(+) = pl.lease_id
AND ploc.location_id(+) = pt.location_id
AND prop.property_id(+) = ploc.property_id
AND NVL (pt.occupancy_date
, pt.estimated_occupancy_date
) BETWEEN ploc.active_start_date(+) AND ploc.active_end_date(+)
AND pl.lease_id = pld.lease_id
AND fnr.user_id = pld.responsible_user
AND lse_lkp.lookup_code = pl.status
AND lse_lkp.lookup_type = 'PN_LEASE_STATUS_TYPE'
AND flg_lkp.lookup_code = pt.primary_flag
AND flg_lkp.lookup_type = 'PN_YES_NO'
AND pl.org_id = p_org_id
AND pl.NAME = p_lease_name;
SELECT pl.lease_id
, pl.payment_term_proration_rule
, pt.location_id
, pld.lease_commencement_date
, pld.lease_termination_date
FROM pn_leases_all pl
, pn_tenancies_all pt
, pn_lease_details_all pld
, fnd_user fnr
, fnd_lookups lse_lkp
, fnd_lookups flg_lkp
, pn_locations_all ploc
, pn_properties_all prop
WHERE pt.lease_id(+) = pl.lease_id
AND ploc.location_id(+) = pt.location_id
AND prop.property_id(+) = ploc.property_id
AND NVL (pt.occupancy_date
, pt.estimated_occupancy_date
) BETWEEN ploc.active_start_date(+) AND ploc.active_end_date(+)
AND pl.lease_id = pld.lease_id
AND fnr.user_id = pld.responsible_user
AND lse_lkp.lookup_code = pl.status
AND lse_lkp.lookup_type = 'PN_LEASE_STATUS_TYPE'
AND flg_lkp.lookup_code = pt.primary_flag
AND flg_lkp.lookup_type = 'PN_YES_NO'
AND pl.org_id = p_org_id
AND pl.lease_num = p_lease_num;
SELECT var_rent_id
FROM pn_var_rents_all
WHERE UPPER (rent_num) = UPPER (x_rent_num)
AND lease_id = p_lease_id
AND org_id = p_org_id;
SELECT auto_var_rent_num_gen
INTO l_auto_var_rent_num_gen
FROM pn_system_setup_options
WHERE org_id = p_org_id;
SELECT pn_var_rents_s.NEXTVAL
INTO x_var_rent_id
FROM DUAL;
SELECT pl.location_id
FROM pn_tenancies_all pt
, pn_locations_all pl
, pn_properties_all prop
, fnd_lookups loc_lkp
, fnd_lookups flg_lkp
WHERE pt.location_id = pl.location_id
AND prop.property_id(+) = pl.property_id
AND NVL (pt.occupancy_date
, pt.estimated_occupancy_date
) BETWEEN pl.active_start_date AND pl.active_end_date
AND loc_lkp.lookup_code = pl.location_type_lookup_code
AND loc_lkp.lookup_type = 'PN_LOCATION_TYPE'
AND flg_lkp.lookup_code = pt.primary_flag
AND flg_lkp.lookup_type = 'PN_YES_NO'
AND pt.lease_id = p_lease_id
AND pl.location_id = x_location_id;
SELECT pl.location_id
FROM pn_tenancies_all pt
, pn_locations_all pl
, pn_properties_all prop
, fnd_lookups loc_lkp
, fnd_lookups flg_lkp
WHERE pt.location_id = pl.location_id
AND prop.property_id(+) = pl.property_id
AND NVL (pt.occupancy_date
, pt.estimated_occupancy_date
) BETWEEN pl.active_start_date AND pl.active_end_date
AND loc_lkp.lookup_code = pl.location_type_lookup_code
AND loc_lkp.lookup_type = 'PN_LOCATION_TYPE'
AND flg_lkp.lookup_code = pt.primary_flag
AND flg_lkp.lookup_type = 'PN_YES_NO'
AND pt.lease_id = p_lease_id
AND pl.location_code = x_location_code;
SELECT meaning
, lookup_code
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND meaning = x_lookup_meaning
AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active
, SYSDATE
)) AND TRUNC (NVL (end_date_active
, SYSDATE
));
SELECT meaning
, lookup_code
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = x_lookup_code
AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active
, SYSDATE
)) AND TRUNC (NVL (end_date_active
, SYSDATE
));
SELECT term.term_template_id
, term.NAME
FROM pn_term_templates_all term
WHERE active = 'Y'
AND ( UPPER (NAME) = UPPER (x_term_template_name)
OR term_template_id = x_term_template_id)
AND term.term_template_type IN ('NEUTRAL', 'PAYMENT')
AND org_id = p_org_id;
SELECT term.term_template_id
, term.NAME
FROM pn_term_templates_all term
WHERE active = 'Y'
AND ( UPPER (NAME) = UPPER (NVL(x_term_template_name,NAME))
AND term_template_id = NVL(x_term_template_id,term_template_id))
AND term.term_template_type IN ('NEUTRAL', 'PAYMENT')
AND org_id = p_org_id;
SELECT user_id
, user_name
FROM fnd_user
WHERE ( UPPER (user_name) = UPPER (x_user_name)
OR user_id = x_user_id)
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date
, SYSDATE
)) AND TRUNC (NVL (end_date
, SYSDATE
));
SELECT user_id
, user_name
FROM fnd_user
WHERE ( UPPER (user_name) = UPPER (NVL(x_user_name,user_name))
OR user_id = NVL(x_user_id,user_id))
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date
, SYSDATE
)) AND TRUNC (NVL (end_date
, SYSDATE
));
SELECT COUNT (1)
INTO l_count
FROM pn_currencies pn
, gl_daily_conversion_types gl
WHERE pn.conversion_type = gl.conversion_type
AND currency_code = p_currency_code;
SELECT meaning
, lookup_code
FROM fnd_lookups
WHERE lookup_type = p_lookup_type --'PN_VAR_CALCULATION_METHOD'
AND ( UPPER (lookup_code) = UPPER (x_lookup_code)
OR UPPER (meaning) = UPPER (x_lookup_meaning))
AND enabled_flag = 'Y'
AND lookup_code <> 'OT'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active
, SYSDATE
)) AND TRUNC (NVL (end_date_active
, SYSDATE
));
SELECT meaning
, lookup_code
FROM fnd_lookups
WHERE lookup_type = p_lookup_type --'PN_VAR_CALCULATION_METHOD'
AND ( UPPER (lookup_code) = UPPER (NVL(x_lookup_code,lookup_code))
AND UPPER (meaning) = UPPER (NVL(x_lookup_meaning,meaning)))
AND enabled_flag = 'Y'
AND lookup_code <> 'OT'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active
, SYSDATE
)) AND TRUNC (NVL (end_date_active
, SYSDATE
));
SELECT period_type
INTO p_period_type
FROM gl_periods
WHERE UPPER (period_set_name) = UPPER (p_gl_period_set_name)
AND UPPER (period_type) = UPPER (p_period_type) -- new condition
GROUP BY period_type
, period_set_name;
SELECT agreement_template_id
, agreement_template
, purpose_code
, type_code
, cumulative_vol
, invoice_on
, negative_rent
, term_template_id
, abatement_amount
, proration_rule
, vrg_reptg_freq_code
, period_freq_code
, use_gl_calendar
, year_start_date
, gl_period_set_name
, period_type
, reptg_freq_code
, reptg_day_of_month
, reptg_days_after
, invg_freq_code
, invg_spread_code
, invg_day_of_month
, invg_days_after
, comments
FROM pn_var_templates_all
--WHERE agreement_template_id = x_agreement_template_id -- Commented by Vivek on 08-Dec-2010 as NULL value is not handled
WHERE agreement_template_id = NVL(x_agreement_template_id,agreement_template_id) -- Added by Vivek on 08-Dec-2010 to handle NULL value
--OR UPPER (agreement_template) = UPPER (x_agreement_template) -- Commented by Vivek on 08-Dec-2010 as NULL value and mutliple rows are not handled
AND UPPER (agreement_template) = UPPER (NVL(x_agreement_template,agreement_template)) -- Added by Vivek on 08-Dec-2010 to handle NULL value and mutliple row cases
AND org_id = p_org_id;
SELECT commencement_date
, termination_date
INTO x_commencement_date
, x_termination_date
FROM pn_var_rents_all
WHERE var_rent_id = p_var_rent_id
AND org_id = p_org_id;
SELECT var_rent_id
, lease_id
, location_id
FROM pn_var_rents_all
WHERE UPPER (rent_num) = UPPER (p_rent_num);
SELECT p_rent_num
, var_rent_id
-- , lease_id
-- , location_id
FROM pn_var_rents_all
WHERE UPPER (rent_num) = UPPER (NVL(p_rent_num,rent_num))
AND var_rent_id= NVL(x_var_rent_id,var_rent_id);
SELECT period_type
, period_set_name
INTO l_period_type
, l_gl_period_set_name
FROM gl_periods
WHERE UPPER (period_set_name) = UPPER (p_gl_period_set_name)
AND UPPER (period_type) = UPPER (p_period_type)
GROUP BY period_type
, period_set_name;
SELECT COUNT (1)
INTO x_bkpt_exist --l_bkpt_exist
FROM pn_var_bkpts_head_all
WHERE var_rent_id = p_var_rent_id;
SELECT COUNT (1)
INTO x_constr_count
FROM pn_var_constr_defaults_all
WHERE var_rent_id = p_var_rent_id;
SELECT PLA.lease_id lease_id
,PLD.lease_commencement_date lease_commencement_date
,PLD.lease_termination_date lease_termination_date
FROM pn_leases_all PLA
,pn_lease_details_all PLD
WHERE PLD.org_id=PLA.org_id
AND PLA.org_id=FND_PROFILE.VALUE('ORG_ID')
AND PLD.lease_id=PLA.lease_id
AND PLA.lease_id=p_lease_id;