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
distinct
ten.location_id LOCATION_ID,
NVL(ten.occupancy_date, ten.estimated_occupancy_date) OCCUPANCY_DATE,
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,
(TRUNC(les.lease_termination_date)- TRUNC(les.lease_commencement_date)+1) LEASE_TERM,
fnd4.meaning LEASE_CLASS,
les.user_responsible LEASE_RESPONSIBLE_USER,
fnd1.meaning LEASE_STATUS,
fnd6.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,
fnd5.meaning LOCATION_TYPE,
NVL(NVL(loc.building, loc.FLOOR), loc.office) LOCATION_NAME,
loc.location_code LOCATION_CODE,
fnd7.meaning USAGE_TYPE,
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),
'||''''||'PARCEL'||''''||',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id),
'||''''||'BUILDING'||''''||',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id),
'||''''||'LAND'||''''||',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id),
rentable_area) RENTABLE_AREA,
DECODE (location_type_lookup_code,'||''''||'OFFICE'||''''||',loc.usable_area,
'||''''||'FLOOR'||''''||',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id),
'||''''||'PARCEL'||''''||',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id),
'||''''||'BUILDING'||''''||',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id),
'||''''||'LAND'||''''||',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id),
usable_area) USABLE_AREA,
loc.gross_area GROSS_AREA,
fnd2.meaning TENURE,
mil.lease_milestone_id LEASE_MILESTONE_ID,
fnd3.meaning MILESTONE_TYPE,
fnd.user_name RESPONSIBLE_USER,
mil.milestone_date ACTION_DATE,
mil.milestone_date-mil.lead_days MILESTONE_DATE,
mil.action_taken ACTION_TAKEN
FROM pn_leases_v les,
pn_tenancies ten,
pn_locations loc,
pn_lease_milestones mil,
fnd_user fnd,
pn_addresses pa,
fnd_lookups fnd1,
fnd_lookups fnd2,
fnd_lookups fnd3,
fnd_lookups fnd4,
fnd_lookups fnd5,
fnd_lookups fnd6,
fnd_lookups fnd7
WHERE ten.lease_id = les.lease_id
AND ten.location_id = loc.location_id
AND ( (( loc.active_start_date BETWEEN les.lease_commencement_date AND les.lease_Termination_date) OR
(loc.active_end_date BETWEEN les.lease_commencement_date AND les.lease_Termination_date)) OR ( loc.active_start_date < les.lease_commencement_date AND loc.active_end_date
> les.lease_Termination_date))
AND mil.lease_id = les.lease_id
AND mil.user_id = fnd.user_id
AND ten.primary_flag ='||''''||'Y'||''''||'
AND les.status = fnd1.lookup_code (+)
AND loc.lease_or_owned = fnd2.lookup_code (+)
AND fnd3.lookup_code = mil.milestone_type_code
AND fnd4.lookup_code (+) = les.lease_class_code
AND fnd5.lookup_code = loc.location_type_lookup_code
AND fnd6.lookup_code = les.lease_type_code
AND fnd7.lookup_code = ten.tenancy_usage_lookup_code
AND fnd1.lookup_type (+) = '||''''||'PN_LEASE_STATUS_TYPE'||''''||'
AND fnd2.lookup_type (+) = '||''''||'PN_LEASED_OR_OWNED'||''''||'
AND fnd3.lookup_type = '||''''||'PN_MILESTONES_TYPE'||''''||'
AND fnd4.lookup_type (+) = '||''''||'PN_LEASE_CLASS'||''''||'
AND fnd5.lookup_type = '||''''||'PN_LOCATION_TYPE'||''''||'
AND fnd6.lookup_type = '||''''||'PN_LEASE_TYPE'||''''||'
AND fnd7.lookup_type = '||''''||'PN_TENANCY_USAGE_TYPE'||''''||'
AND pa.address_id(+) = loc.address_id';
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);
PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_les: insert(+)');
INSERT INTO pn_milestones_itf
(LEASE_MILESTONE_ID ,
MILESTONE_TYPE_CODE ,
RESPONSIBLE_USER ,
ACTION_DATE ,
NOTIFICATION_DATE ,
ACTION_TAKEN ,
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 ,
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_MILESTONE_ID ,
V_MILESTONE_TYPE ,
V_RESPONSIBLE_USER ,
V_ACTION_DATE ,
V_MILESTONE_DATE ,
V_ACTION_TAKEN ,
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_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_les: insert(-)');