Description:
This query fetches those leases which are going to expire within 15,30,60,90,180 days, respectively, based on the parameter.
SELECT GCC.segment4 SOL_ID
,gl_flexfields_pkg.get_description_SQL(GCC.chart_of_accounts_id ,4,GCC.segment4) BRANCH_NAME
,PLA.lease_id Lease_number
,PLDA.attribute7 Agreement_no
,PAA.address_line1||’,’||PAA.address_line2||’,’||PAA.address_line3||’,’||PAA.address_line4||’,’||PAA.city||’,’||PAA.state
||’,’||PAA.zip_code Address
,APS.vendor_name Landlord_name
,ASSA.vendor_site_code Landlord_Site
,PLDA.attribute4 Emp_No
,TO_CHAR(PPTA.end_date, ‘DD-MON-YYYY’) Expiry_date
,PPTA.frequency_code Payment_Freq
,PLA.lease_type_code Category_of_premises
,PPTA.attribute9 Station_id
,PPTA.attribute8 SITE_ID
from pn_leases_all PLA
,pn_lease_details_all PLDA
,pn_tenancies_all PTA
,pn_locations_all PNLA
,pn_addresses_all PAA
,fnd_territories_tl FTT
,pn_payment_terms_all PPTA
,pn_payment_items_all PPIA
,pn_payment_schedules_all PPSA
,pn_distributions_all PDA
,gl_code_combinations GCC
,ap_suppliers APS
,ap_supplier_sites_all ASSA
WHERE 1=1
AND PLA.lease_id = PLDA.lease_id
AND PLA.lease_id = PPTA.lease_id
AND PLA.lease_id = PTA.lease_id
AND PTA.location_id = PNLA.location_id
AND PNLA.address_id = PAA.address_id
AND FTT.territory_code = PAA.country
AND PPTA.payment_term_id = PPIA.payment_term_id
AND PPIA.payment_schedule_id = PPSA.payment_schedule_id
AND PPSA.lease_id = PLA.lease_id
AND PPTA.payment_term_id = PDA.payment_term_id
AND GCC.code_combination_id = PDA.account_id
AND PPIA.vendor_id = APS.vendor_id
AND PPIA.vendor_site_id = ASSA.vendor_site_id
AND PPTA.payment_purpose_code = ‘RENT’
AND PDA.account_class = ‘EXP’
— AND PLA.lease_status = ‘ACT’
— AND PLA.lease_id = 11000
and payment_status_lookup_code = ‘APPROVED’
HAVING TRUNC(Max(PPTA.end_date)) = TRUNC(sysdate+:P_NO_DAYS)
GROUP BY GCC.segment4
,GCC.chart_of_accounts_id
,GCC.segment4
,PLA.lease_id
,PLDA.attribute7
,PAA.address_line1
,PAA.address_line2
,PAA.address_line3
,PAA.address_line4
,PAA.city
,PAA.state
,PAA.zip_code
,APS.vendor_name
,ASSA.vendor_site_code
,PLDA.attribute4
,PPTA.end_date
,PPTA.frequency_code
,PLA.lease_type_code
,PPTA.attribute9
,PPTA.attribute8
Summary
This Post described the script for Leases, which are expiring 15, 30, 60, 180 days before respectively in Oracle EBS.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
