SELECT
pla.lease_num,
pla.name lease_name,
plda.lease_commencement_date,
plda.lease_execution_date,
plda.lease_termination_date,
plda.attribute1,
plda.attribute2,
plda.attribute3,
plda.attribute4,
plda.attribute5,
plda.attribute6,
pta.tenancy_usage_lookup_code,
pta.estimated_occupancy_date,
pta.expiration_date,
(
SELECT
vendor_name
FROM
ap_suppliers
WHERE
vendor_id = ( ppta.vendor_id )
) supplier_name,
(
SELECT
vendor_site_code
FROM
ap_supplier_sites_all
WHERE
vendor_id = ( ppta.vendor_id )
AND vendor_site_id = ( ppta.vendor_site_id )
) supplier_site_name
FROM
pn_leases_all pla,
pn_lease_details_all plda,
pn_tenancies_all pta,
pn_payment_terms_all ppta
WHERE
1 = 1
AND plda.lease_id = pla.lease_id
AND pla.lease_id = pta.lease_id (+)
AND pla.lease_id = ppta.lease_id (+)
AND pla.STATUS ='F' ---Final
AND pla.creation_date BETWEEN sysdate - 2 AND sysdate
GROUP BY
pla.lease_num,
ppta.vendor_id,
ppta.vendor_site_id,
pla.name,
plda.lease_commencement_date,
plda.lease_execution_date,
plda.lease_termination_date,
plda.attribute1,
plda.attribute2,
plda.attribute3,
plda.attribute4,
plda.attribute5,
plda.attribute6,
pta.tenancy_usage_lookup_code,
pta.estimated_occupancy_date,
pta.expiration_date
ORDER BY
pla.lease_num
/