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
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
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.
ReplyDeleteOracle 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