Introduction:
This Post illustrates the steps required to Fetch Leases which are Expired but not terminated in Oracle EBS R12.
Script to Leases Expired but not terminated
SELECT
GCC.segment4 SOL_ID
,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
,to_char(MAX(PPTA.end_date),’DD-MON-YYYY’) Expiry_date
,DECODE(PPTA.frequency_code ,
‘OT’, ‘One Time’,
‘QTR’, ‘Quarterly’,
‘MON’, ‘Monthly’) payment_frequency
,PLA.lease_type_code Category_of_premises
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 <> ‘TER’
HAVING MAX(PPTA.end_date) BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE
— AND PLA.lease_id = 16004
–and payment_status_lookup_code = ‘APPROVED’
GROUP BY GCC.segment4
,PLA.lease_id ,PPTA.end_date
,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
,PPTA.end_date
,PPTA.frequency_code
,PLA.lease_type_code
UNION ALL
SELECT
GCC.segment4 SOL_ID
,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
,TO_CHAR(MAX(PPTA.end_date),’DD-MON-YYYY’) Expiry_date
,DECODE(PPTA.frequency_code ,
‘OT’, ‘One Time’,
‘QTR’, ‘Quarterly’,
‘MON’, ‘Monthly’) payment_frequency
,PLA.lease_type_code Category_of_premises
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 NOT IN (SELECT ppta.lease_id FROM pn_payment_terms_all ppta)
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 <> ‘TER’
HAVING MAX(PPTA.end_date) BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE
— AND PLA.lease_id = 16004
–and payment_status_lookup_code = ‘APPROVED’
GROUP BY GCC.segment4
,PLA.lease_id
,PPTA.end_date
,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
,PPTA.end_date
,PPTA.frequency_code
,PLA.lease_type_code
Queries
Do drop a note by writing us at Venkatesh.b@gmail.com or use the comment section below to ask your questions
