Leases which are expiring 15,30,60,180 days before respectively

This query fetches those leases which are going to expiry 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

  • September 21, 2018 | 19 views
  • Comments