Friday, 21 September 2018

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

No comments:

Post a Comment