[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
V_LAST_UPDATE_DATE DATE;
V_LAST_UPDATED_BY NUMBER;
V_LAST_UPDATE_LOGIN NUMBER;
SELECT payment_term_id,start_date,end_date,payment_term_type_code,vendor_id,
vendor_site_id, customer_id, customer_site_use_id
FROM pn_payment_terms_all
WHERE lease_id = V_LEASE_ID
AND payment_term_type_code in ('BASE','DEP','OEXP');
SELECT schedule_date, payment_schedule_id
FROM pn_payment_schedules_all
WHERE lease_id = V_LEASE_ID;
SELECT actual_amount
FROM pn_payment_items_all
WHERE payment_term_id = V_PAYMENT_TERM_ID
AND payment_schedule_id = V_PAYMENT_SCHEDULE_ID
AND payment_item_type_lookup_code = 'CASH';
'SELECT
distinct
ten.location_id LOCATION_ID,
ten.tenancy_usage_lookup_code USAGE_TYPE,
les.lease_id LEASE_ID,
les.lease_name LEASE_NAME,
les.lease_number LEASE_NUMBER,
les.lease_commencement_date LEASE_COMMENCEMENT_DATE,
les.lease_termination_date LEASE_TERMINATION_DATE,
les.lease_execution_date LEASE_EXECUTION_DATE,
les.lease_term LEASE_TERM,
fl1.meaning LEASE_CLASS,
les.user_responsible LEASE_RESPONSIBLE_USER,
fnd.meaning LEASE_STATUS,
fl2.meaning LEASE_TYPE,
ten.estimated_occupancy_date ESTIMATED_OCCUPANCY_DATE,
ten.occupancy_date OCCUPANCY_DATE,
ten.attribute_category ATTRIBUTE_CATEGORY,
ten.attribute1 ATTRIBUTE1,
ten.attribute2 ATTRIBUTE2,
ten.attribute3 ATTRIBUTE3,
ten.attribute4 ATTRIBUTE4,
ten.attribute5 ATTRIBUTE5,
ten.attribute6 ATTRIBUTE6,
ten.attribute7 ATTRIBUTE7,
ten.attribute8 ATTRIBUTE8,
ten.attribute9 ATTRIBUTE9,
ten.attribute10 ATTRIBUTE10,
ten.attribute11 ATTRIBUTE11,
ten.attribute12 ATTRIBUTE12,
ten.attribute13 ATTRIBUTE13,
ten.attribute14 ATTRIBUTE14,
ten.attribute15 ATTRIBUTE15,
les.last_update_date LAST_UPDATE_DATE,
les.last_updated_by LAST_UPDATED_BY,
les.last_update_login LAST_UPDATE_LOGIN,
les.creation_date CREATION_DATE,
les.created_by CREATED_BY
FROM pn_leases_v les,
pn_tenancies ten,
fnd_lookups fnd,
fnd_lookups fl1,
fnd_lookups fl2,
pn_options opt
WHERE ten.lease_id = les.lease_id
AND ten.primary_flag ='||''''||'Y'||''''||'
AND les.status = fnd.lookup_code
AND fnd.lookup_type = '||''''||'PN_LEASE_STATUS_TYPE'||''''||'
AND fl1.lookup_code = les.lease_class_code
AND fl1.lookup_type = '||''''||'PN_LEASE_CLASS'||''''||'
AND fl2.lookup_code = les.lease_type_code
AND fl2.lookup_type = '||''''||'PN_LEASE_TYPE'||''''||'
AND ten.location_id IN(SELECT loc.location_id FROM pn_locations loc'
||location_code_where_clause|| ' '|| l_location_date_clause|| ')'
||lease_number_where_clause||les_resp_user_where_clause||option_type_where_clause
||exer_date_where_clause||lease_date_where_clause;
dbms_sql.define_column (l_cursor,32,V_LAST_UPDATE_DATE);
dbms_sql.define_column (l_cursor,33,V_LAST_UPDATED_BY);
dbms_sql.define_column (l_cursor,34,V_LAST_UPDATE_LOGIN);
dbms_sql.column_value (l_cursor,32,V_LAST_UPDATE_DATE);
dbms_sql.column_value (l_cursor,33,V_LAST_UPDATED_BY);
dbms_sql.column_value (l_cursor,34,V_LAST_UPDATE_LOGIN);
SELECT
distinct
loc.location_id LOCATION_ID,
fl1.meaning LOCATION_TYPE,
NVL(NVL(loc.building, loc.FLOOR), loc.office) LOCATION_NAME,
loc.location_code LOCATION_CODE,
pa.address_line1||pa.address_line2||pa.address_line3||pa.address_line4
||pa.county||pa.city||pa.state||pa.province||pa.zip_code||pa.country ADDRESS,
pa.county COUNTY,
pa.city CITY,
pa.state STATE,
pa.province PROVINCE,
pa.zip_code ZIP_CODE,
pa.country COUNTRY,
DECODE (location_type_lookup_code,'OFFICE',loc.rentable_area,
'FLOOR',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id, loc.active_start_date),
'PARCEL',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id, loc.active_start_date),
'BUILDING',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id, loc.active_start_date),
'LAND',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id, loc.active_start_date),
rentable_area) RENTABLE_AREA,
DECODE (location_type_lookup_code,'OFFICE',loc.usable_area,
'FLOOR',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id, loc.active_start_date),
'PARCEL',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id, loc.active_start_date),
'BUILDING',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id, loc.active_start_date),
'LAND',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id, loc.active_start_date),
usable_area) USABLE_AREA,
loc.gross_area GROSS_AREA,
fnd.meaning TENURE
FROM pn_locations_all loc,
pn_addresses_all pa,
fnd_lookups fnd,
fnd_lookups fl1
WHERE pa.address_id(+) = loc.address_id
AND loc.lease_or_owned = fnd.lookup_code(+)
AND fnd.lookup_type(+) = 'PN_LEASED_OR_OWNED'
AND fl1.lookup_code(+) = loc.location_type_lookup_code
AND fl1.lookup_type(+) = 'PN_LOCATION_TYPE'
AND loc.location_id = V_LOCATION_ID_1;
SELECT
distinct
opt.option_id OPTION_ID,
fl.meaning OPTION_TYPE,
fl1.meaning OPTION_STATUS,
(trunc(opt.expiration_date) - trunc(opt.start_date)+1) OPTION_TERM,
opt.option_reference REFERENCE,
opt.option_notice_reqd OPTION_NOTICE_REQD,
opt.option_action_date OPTION_ACTION_DATE,
opt.option_size OPTION_SIZE,
opt.option_cost OPTION_COST,
opt.uom_code UOM_CODE,
opt.option_area_change OPTION_AREA_CHANGE,
opt.option_exer_start_date OPTION_EXER_START_DATE,
opt.option_exer_end_date OPTION_EXER_END_DATE,
opt.start_date OPTION_COMM_DATE,
opt.expiration_date OPTION_EXP_DATE,
opt.option_comments OPTION_COMMENTS
FROM pn_options_all opt,
fnd_lookups fl,
fnd_lookups fl1
WHERE opt.lease_id = V_LEASE_ID
AND fl.lookup_code(+)= opt.option_type_code
AND fl.lookup_type(+)= 'PN_LEASE_OPTION_TYPE'
AND fl1.lookup_code(+)= opt.option_status_lookup_code
AND fl1.lookup_type(+)= 'PN_OPTION_STATUS_TYPE';
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
pn_mo_cache_utils.get_current_org_id));
PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_inserting(+)');
INSERT INTO pn_lease_options_itf
(LEASE_ID ,
LEASE_NAME ,
LEASE_NUMBER ,
LEASE_COMMENCEMENT_DATE ,
LEASE_TERMINATION_DATE ,
LEASE_EXECUTION_DATE ,
LEASE_TERM ,
LEASE_CLASS ,
LEASE_RESPONSIBLE_USER ,
LEASE_STATUS ,
LEASE_TYPE ,
ESTIMATED_OCCUPANCY_DATE ,
ACTUAL_OCCUPANCY_DATE ,
LOCATION_ID ,
LOCATION_TYPE ,
LOCATION_NAME ,
LOCATION_CODE ,
SPACE_TYPE ,
REGION ,
PROPERTY_NAME ,
BUILDING_OR_LAND_NAME ,
FLOOR_OR_PARCEL_NAME ,
OFFICE_OR_SECTION_NAME ,
ADDRESS ,
COUNTY ,
CITY ,
STATE ,
PROVINCE ,
ZIP_CODE ,
COUNTRY ,
GROSS_AREA ,
RENTABLE_AREA ,
USABLE_AREA ,
TENURE ,
OPTION_ID ,
OPTION_TYPE ,
OPTION_STATUS ,
OPTION_TERM ,
REFERENCE ,
OPTION_NOTICE_REQD ,
OPTION_ACTION_DATE ,
OPTION_SIZE ,
OPTION_COST ,
UOM_CODE ,
OPTION_AREA_CHANGE ,
OPTION_CURRENCY ,
OPTION_EXER_START_DATE ,
OPTION_EXER_END_DATE ,
OPTION_COMM_DATE ,
OPTION_EXP_DATE ,
OPTION_COMMENTS ,
TEN_ATTRIBUTE_CATEGORY ,
TEN_ATTRIBUTE1 ,
TEN_ATTRIBUTE2 ,
TEN_ATTRIBUTE3 ,
TEN_ATTRIBUTE4 ,
TEN_ATTRIBUTE5 ,
TEN_ATTRIBUTE6 ,
TEN_ATTRIBUTE7 ,
TEN_ATTRIBUTE8 ,
TEN_ATTRIBUTE9 ,
TEN_ATTRIBUTE10 ,
TEN_ATTRIBUTE11 ,
TEN_ATTRIBUTE12 ,
TEN_ATTRIBUTE13 ,
TEN_ATTRIBUTE14 ,
TEN_ATTRIBUTE15 ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
REQUEST_ID )
VALUES
(V_LEASE_ID ,
V_LEASE_NAME ,
V_LEASE_NUMBER ,
V_LEASE_COM_DATE ,
V_LEASE_TERM_DATE ,
V_LEASE_EXE_DATE ,
V_LEASE_TERM ,
V_LEASE_CLASS ,
V_LEASE_RESP_USER ,
V_LEASE_STATUS ,
V_LEASE_TYPE ,
V_ESTIMATED_OCCUPANCY_DATE ,
V_ACTUAL_OCCUPANCY_DATE ,
V_LOCATION_ID ,
V_LOCATION_TYPE ,
V_LOCATION_NAME ,
V_LOCATION_CODE ,
V_SPACE_TYPE ,
v_code_data.REGION_NAME ,
v_code_data.PROPERTY_NAME ,
v_code_data.BUILDING ,
v_code_data.FLOOR ,
v_code_data.OFFICE ,
V_ADDRESS ,
V_COUNTY ,
V_CITY ,
V_STATE ,
V_PROVINCE ,
V_ZIP_CODE ,
V_COUNTRY ,
V_GROSS_AREA ,
V_RENTABLE_AREA ,
V_USABLE_AREA ,
V_TENURE ,
V_OPTION_ID ,
V_OPTION_TYPE ,
V_OPTION_STATUS ,
V_OPTION_TERM ,
V_REFERENCE ,
V_OPTION_NOTICE_REQD ,
V_OPTION_ACTION_DATE ,
V_OPTION_SIZE ,
V_OPTION_COST ,
V_UOM_CODE ,
V_OPTION_AREA_CHANGE ,
V_OPTION_CURRENCY ,
V_OPTION_EXER_START_DATE ,
V_OPTION_EXER_END_DATE ,
V_OPTION_COMM_DATE ,
V_OPTION_EXP_DATE ,
V_OPTION_COMMENTS ,
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_LAST_UPDATE_DATE ,
V_LAST_UPDATED_BY ,
V_LAST_UPDATE_LOGIN ,
V_CREATION_DATE ,
V_CREATED_BY ,
l_request_id );
PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_inserting(-)');