Lease Expired But Not Terminated

Query to Fetch Leases which are 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