[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;
SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
FROM pn_payment_items_all ppi, pn_payment_terms_all ppt, pn_payment_schedules_all pps
WHERE ppi.payment_term_id = ppt.payment_term_id
AND ppi.payment_schedule_id = pps.payment_schedule_id
AND ppt.lease_id = V_LEASE_ID
AND pps.lease_id = V_LEASE_ID
AND ppi.payment_item_type_lookup_code = 'CASH'
AND ppt.payment_term_type_code = 'BASER';
SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
FROM pn_payment_items_all ppi, pn_payment_terms_all ppt, pn_payment_schedules_all pps
WHERE ppi.payment_term_id = ppt.payment_term_id
AND ppi.payment_schedule_id = pps.payment_schedule_id
AND ppt.lease_id = V_LEASE_ID
AND pps.lease_id = V_LEASE_ID
AND ppi.payment_item_type_lookup_code = 'CASH'
AND ppt.payment_term_type_code = 'DEP';
SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
FROM pn_payment_items_all ppi, pn_payment_terms_all ppt, pn_payment_schedules_all pps
WHERE ppi.payment_term_id = ppt.payment_term_id
AND ppi.payment_schedule_id = pps.payment_schedule_id
AND ppt.lease_id = V_LEASE_ID
AND pps.lease_id = V_LEASE_ID
AND ppi.payment_item_type_lookup_code = 'CASH'
AND ppt.payment_term_type_code = 'OEXP';
SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
FROM pn_payment_items_all ppi, pn_payment_terms_all ppt
WHERE ppi.payment_term_id = ppt.payment_term_id
AND ppt.lease_id = V_LEASE_ID
AND ppi.payment_item_type_lookup_code = 'CASH';
SELECT SUM(NVL(ppi.estimated_amount,ppi.actual_amount)) amount
FROM pn_payment_items_all ppi, pn_payment_terms_all ppt
WHERE ppi.payment_term_id = ppt.payment_term_id
AND ppt.lease_id = V_LEASE_ID
AND ppi.payment_item_type_lookup_code = 'CASH'
AND (ppi.transferred_to_ap_flag = 'Y' OR
ppi.transferred_to_ar_flag = 'Y') ; -- Bug # 1671866
'SELECT
distinct
ten.location_id LOCATION_ID,
fnd2.meaning USAGE_TYPE,
les.lease_id LEASE_ID,
les.name LEASE_NAME,
les.lease_num LEASE_NUMBER,
ldet.lease_commencement_date LEASE_COMMENCEMENT_DATE,
ldet.lease_termination_date LEASE_TERMINATION_DATE,
ldet.lease_execution_date LEASE_EXECUTION_DATE,
(TRUNC(ldet.lease_termination_date)- TRUNC(ldet.lease_commencement_date)+1) LEASE_TERM,
fnd1.meaning LEASE_CLASS,
les.lease_class_code LEASE_CLASS_1, --bug#2099864
fnd4.user_name LEASE_RESPONSIBLE_USER,
fnd.meaning LEASE_STATUS,
fnd3.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,
ldet.last_update_date LAST_UPDATE_DATE,
ldet.last_updated_by LAST_UPDATED_BY,
ldet.last_update_login LAST_UPDATE_LOGIN,
ldet.creation_date CREATION_DATE,
ldet.created_by CREATED_BY,
les.payment_term_proration_rule PRORATION_RULE -- added for bug#2102098
FROM pn_lease_details ldet,
pn_tenancies_all ten,
fnd_lookups fnd,
fnd_lookups fnd1,
fnd_lookups fnd2,
fnd_lookups fnd3,
fnd_user fnd4,
pn_leases_all les
WHERE ten.lease_id = les.lease_id
AND les.lease_id = ldet.lease_id
AND ten.primary_flag = '||'''' || 'Y'||''''||'
AND les.status = fnd.lookup_code
AND fnd.lookup_type = '||''''||'PN_LEASE_STATUS_TYPE'||''''||'
AND fnd1.lookup_code = les.lease_class_code
AND fnd1.lookup_type = '||''''||'PN_LEASE_CLASS'||''''||'
AND fnd2.lookup_code = ten.tenancy_usage_lookup_code
AND fnd2.lookup_type = '||''''||'PN_TENANCY_USAGE_TYPE'||''''||'
AND fnd3.lookup_code = les.lease_type_code
AND fnd3.lookup_type = '||''''||'PN_LEASE_TYPE'||''''||'
AND fnd4.user_id = ldet.responsible_user
AND ten.location_id IN(SELECT loc.location_id from pn_locations loc'||location_code_where_clause||' ' ||
l_location_date_clause ||')'
||les_resp_user_where_clause
||lease_type_where_clause
||lease_number_where_clause
||lease_date_where_clause
||lease_status_where_clause
||lease_class_where_clause
||'ORDER BY les.lease_class_code';
dbms_sql.define_column (l_cursor,33,V_LAST_UPDATE_DATE);
dbms_sql.define_column (l_cursor,34,V_LAST_UPDATED_BY);
dbms_sql.define_column (l_cursor,35,V_LAST_UPDATE_LOGIN);
dbms_sql.column_value (l_cursor,33,V_LAST_UPDATE_DATE);
dbms_sql.column_value (l_cursor,34,V_LAST_UPDATED_BY);
dbms_sql.column_value (l_cursor,35,V_LAST_UPDATE_LOGIN);
SELECT
distinct
loc.location_id LOCATION_ID,
loc.active_start_date ACTIVE_START_DATE,
fnd1.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 fnd1
WHERE pa.address_id(+) = loc.address_id
AND loc.lease_or_owned = fnd.lookup_code(+)
AND fnd.lookup_type(+) = 'PN_LEASED_OR_OWNED'
AND fnd1.lookup_code(+) = loc.location_type_lookup_code
AND fnd1.lookup_type(+) = 'PN_LOCATION_TYPE'
AND loc.location_id = V_LOCATION_ID_1;
SELECT
pv.vENDor_id VENDOR_ID,
pvs.vENDor_site_code VENDOR_SITE,
pv.vENDor_name VENDOR_NAME,
pvs.address_line1||pvs.address_line2||pvs.address_line3||pvs.address_line4||pvs.county||pvs.city||pvs.state||pvs.province||pvs.zip||pvs.country INVOICING_ADDRESS
INTO V_VENDOR_ID ,
V_VENDOR_SITE ,
V_VENDOR_NAME ,
V_INVOICING_ADDRESS
FROM po_vENDors pv,
po_vENDor_sites pvs
WHERE pv.vENDor_id = pvs.vENDor_id
AND pvs.vENDor_id = c.vENDor_id
AND pvs.vENDor_site_id = c.vENDor_site_id;
SELECT
hca.cust_account_id CUSTOMER_ID,
hcs.location CUSTOMER_SITE_ID,
hp.party_name CUSTOMER_NAME,
hp.address1||hp.address2||hp.address3||hp.address4||hp.county||hp.city||hp.state||hp.province||hp.postal_code||hp.country INVOICING_ADDRESS
INTO V_CUSTOMER_ID,
V_CUSTOMER_SITE,
V_CUSTOMER_NAME,
V_INVOICING_ADDRESS
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_site_uses hcs
WHERE hca.cust_account_id = t.customer_id
AND hcs.site_use_id = t.customer_site_use_id
AND hca.party_id = hp.party_id;
select MONTHS_BETWEEN(LAST_DAY(ADD_MONTHS(V_LEASE_TERM_DATE,-1)),LAST_DAY(V_LEASE_COM_DATE)) +
ROUND(TO_CHAR(TO_DATE(V_LEASE_TERM_DATE,'DD/MM/YY'),'DD')/TO_CHAR(TO_DATE(LAST_DAY(V_LEASE_TERM_DATE),'DD/MM/YY'),'DD'),3)
+ ROUND((TO_CHAR(TO_DATE(LAST_DAY(V_LEASE_COM_DATE),'DD/MM/YY'),'DD')-TO_CHAR(TO_DATE(V_LEASE_COM_DATE,'DD/MM/YY'),'DD')+1)/
TO_CHAR(TO_DATE(LAST_DAY(V_LEASE_COM_DATE),'DD/MM/YY'),'DD'),3)
into v_no_of_months from dual;
SELECT ROUND(1/(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
SELECT ROUND(MONTHS_BETWEEN(v_lease_term_date,v_lease_com_date),0) INTO v_no_of_months FROM dual;
SELECT ROUND(MONTHS_BETWEEN(LAST_DAY(ADD_MONTHS(v_lease_term_date,-1)),v_lease_com_date),0)
+ROUND((TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'dd'))/(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
SELECT MONTHS_BETWEEN(v_lease_term_date,LAST_DAY(v_lease_com_date))+
ROUND((TO_CHAR(TO_DATE(LAST_DAY(v_lease_com_date),'dd/mm/yy'),'dd')
-TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'dd')+1)/(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
SELECT ROUND((TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'dd') - TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'dd') +1)/
(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
SELECT ROUND(MONTHS_BETWEEN(LAST_DAY(ADD_MONTHS(v_lease_term_date,-1)),LAST_DAY(v_lease_com_date)),0)
+ROUND((TO_CHAR(TO_DATE(v_lease_term_date,'dd/mm/yy'),'dd'))/(V_PRORATION_RULE/12),3)
+ ROUND((TO_CHAR(TO_DATE(LAST_DAY(v_lease_com_date),'dd/mm/yy'),'dd')-TO_CHAR(TO_DATE(v_lease_com_date,'dd/mm/yy'),'dd')+1)
/(V_PRORATION_RULE/12),3) INTO v_no_of_months FROM dual;
PNP_DEBUG_PKG.put_log_msg('pn_rentroll_insert1(+)');
INSERT INTO pn_rent_roll_lease_exp_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 ,
TENANT_NAME ,
TENANT_SITE ,
LANDLORD_NAME ,
LANDLORD_SITE ,
ANNUAL_BASE_RENT ,
MONTHLY_BASE_RENT ,
DEPOSIT ,
MONTHLY_OPERATING_EXPENSE ,
TOTAL_LEASE_LIABILITY ,
REMAINING_LEASE_LIABILITY ,
ANNUAL_RENT_PER_RENTABLE_AREA ,
INVOICING_ADDRESS ,
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_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_CUSTOMER_NAME , --bug#2099864
V_CUSTOMER_SITE , --bug#2099864
V_VENDOR_NAME ,
V_VENDOR_SITE ,
V_AVG_ANNUAL_BASE_RENT , ---BUG#2102098 CHANGED FROM ANNUAL TO AVG
V_AVG_MONTHLY_BASE_RENT , --- BUG #2102098 CHANGED FROM ANNUAL TO AVG
V_DEPOSIT ,
V_AVG_MONTHLY_OPERATING_EXP , ---BUG #2102098 CHANGED FROM ANNUAL TO AVG
V_TOTAL_LEASE_LIABILITY ,
(V_TOTAL_LEASE_LIABILITY)-(V_AMOUNT_EXPORTED) ,
V_ANNUAL_RENT_PER_RENT_AREA ,
V_INVOICING_ADDRESS ,
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_rentroll_insert1(-)');