Thursday, 20 September 2018

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     

1 comment:

  1. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad
    Oracle Fusion HCM Training In Hyderabad

    ReplyDelete