VIEW: APPS.PN_VAR_SUMM_DET_V

Source

View Text - Preformatted

SELECT i.var_rent_inv_id reference_num, b.rent_num, b.var_rent_id, (SELECT company_name FROM pn_lease_contact_assign_v WHERE lease_id = lease.lease_id AND lease_role_type = 'P' AND status = 'A' and rownum=1) parent, lease.lease_status, proration_rule, lease.lease_type_code lease_type, lease.lease_class_code lease_class, i.invoice_date act_inv_date, pn_var_rent_calc_pkg.inv_sch_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_date, pn_var_rent_calc_pkg.inv_start_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_start_date, pn_var_rent_calc_pkg.inv_end_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_end_date, i.ACTUAL_INVOICED_AMOUNT act_amount, Decode(b.cumulative_vol,'C','Cumulative','N','Non-Cumulative','T','True up','Y','Year to Date','None') method, decode(c.invg_freq_code,'MON','Monthly','QTR','Quarterly','SA','Semi Annually','YR','Yearly','OT','One Time',c.invg_freq_code) Billing_Freq, decode(c.VRG_REPTG_FREQ_CODE,'MON','Monthly','QTR','Quarterly','SA','Semi Annually','YR','Yearly','OT','One Time',c.reptg_freq_code) Reporting_Frequency, lease.name lease_name, lease.lease_num lease_number, lease.lease_id, 'TERM NOT CREATED' BILL_STATUS, period.start_date, period.end_date, period.period_id, b.abstracted_by_user, pn_var_rent_calc_pkg.include_prd_no_term(period.period_id) AS incl_prd, (SELECT LOCATION_CODE FROM PN_LOCATIONS_ALL WHERE LOCATION_ID = b.location_id and rownum =1) AS LOCATION_CODE, b.location_id, (SELECT prop.property_code FROM pn_properties_all prop WHERE prop.property_id IN ( SELECT property_id FROM pn_locations_all WHERE parent_location_id IS NULL START WITH location_id=b.location_id CONNECT BY PRIOR parent_location_id=location_id) ) as property_code, (SELECT prop.property_name FROM pn_properties_all prop WHERE prop.property_id IN ( SELECT property_id FROM pn_locations_all WHERE parent_location_id IS NULL START WITH location_id=b.location_id CONNECT BY PRIOR parent_location_id=location_id) ) as property_name from pn_var_rent_inv_all i, pn_var_rents_all b, pn_var_rent_dates_all c, pn_leases_all lease, pn_var_periods_all period where i.var_rent_id = b.var_rent_id and b.var_rent_id = c.var_rent_id and i.actual_term_status = 'N' and i.actual_exp_code = 'N' and lease.lease_id =b.lease_id and period.period_id = i.period_id UNION ALL select i.var_rent_inv_id reference_num, b.rent_num, b.var_rent_id, (SELECT company_name FROM pn_lease_contact_assign_v WHERE lease_id = lease.lease_id AND lease_role_type = 'P' AND status = 'A' and rownum=1) parent, lease.lease_status, proration_rule, lease.lease_type_code lease_type, lease.lease_class_code lease_class, i.invoice_date act_inv_date, pn_var_rent_calc_pkg.inv_sch_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_date, pn_var_rent_calc_pkg.inv_start_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_start_date, pn_var_rent_calc_pkg.inv_end_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_end_date, i.ACTUAL_INVOICED_AMOUNT act_amount, Decode(b.cumulative_vol,'C','Cumulative','M','Manual','N','Non-Cumulative','T','True up','Y','Year to Date','None') method, decode(c.invg_freq_code,'MON','Monthly','QTR','Quarterly','SA','Semi Annually','YR','Yearly','OT','One Time',c.invg_freq_code) Billing_Freq, decode(c.VRG_REPTG_FREQ_CODE,'MON','Monthly','QTR','Quarterly','SA','Semi Annually','YR','Yearly','OT','One Time',c.reptg_freq_code) Reporting_Frequency, lease.name lease_name, lease.lease_num lease_number, lease.lease_id, 'TERM CREATED, NOT APPROVED' BILL_STATUS, period.start_date, period.end_date, period.period_id, b.abstracted_by_user, pn_var_rent_calc_pkg.include_prd_no_term(period.period_id) AS incl_prd, (SELECT LOCATION_CODE FROM PN_LOCATIONS_ALL WHERE LOCATION_ID = b.location_id and rownum =1) AS LOCATION_CODE, b.location_id, (SELECT prop.property_code FROM pn_properties_all prop WHERE prop.property_id IN ( SELECT property_id FROM pn_locations_all WHERE parent_location_id IS NULL START WITH location_id=b.location_id CONNECT BY PRIOR parent_location_id=location_id) ) as property_code, (SELECT prop.property_name FROM pn_properties_all prop WHERE prop.property_id IN ( SELECT property_id FROM pn_locations_all WHERE parent_location_id IS NULL START WITH location_id=b.location_id CONNECT BY PRIOR parent_location_id=location_id) ) as property_name from pn_var_rent_inv_all i, pn_var_rents_all b, pn_var_rent_dates_all c, pn_leases_all lease, pn_var_periods_all period where i.var_rent_id = b.var_rent_id and b.var_rent_id = c.var_rent_id and lease.lease_id =b.lease_id AND period.period_id = i.period_id and i.actual_term_status = 'Y' and i.actual_exp_code = 'N' UNION ALL select i.var_rent_inv_id reference_num, b.rent_num, b.var_rent_id, (SELECT company_name FROM pn_lease_contact_assign_v WHERE lease_id = lease.lease_id AND lease_role_type = 'P' AND status = 'A' and rownum=1) parent, lease.lease_status, proration_rule, lease.lease_type_code lease_type, lease.lease_class_code lease_class, i.invoice_date act_inv_date, pn_var_rent_calc_pkg.inv_sch_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_date, pn_var_rent_calc_pkg.inv_start_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_start_date, pn_var_rent_calc_pkg.inv_end_date(i.invoice_date,i.var_rent_id, i.period_id) as inv_end_date, i.ACTUAL_INVOICED_AMOUNT act_amount, Decode(b.cumulative_vol,'C','Cumulative','M','Manual','N','Non-Cumulative','T','True up','Y','Year to Date','None') method, decode(c.invg_freq_code,'MON','Monthly','QTR','Quarterly','SA','Semi Annually','YR','Yearly','OT','One Time',c.invg_freq_code) Billing_Freq, decode(c.VRG_REPTG_FREQ_CODE,'MON','Monthly','QTR','Quarterly','SA','Semi Annually','YR','Yearly','OT','One Time',c.reptg_freq_code) Reporting_Frequency, lease.name lease_name, lease.lease_num lease_number, lease.lease_id, decode(nvl(itm.transferred_to_ar_flag,'N'),'N', (decode(nvl(itm.transferred_to_ap_flag,'N'),'N','TERM APPROVED','TERM EXPORTED TO AP')), 'Y', 'TERM EXPORTED TO AR') BILL_STATUS, period.start_date, period.end_date, period.period_id, b.abstracted_by_user, pn_var_rent_calc_pkg.include_prd_no_term(period.period_id) AS incl_prd, (SELECT LOCATION_CODE FROM PN_LOCATIONS_ALL WHERE LOCATION_ID = b.location_id and rownum =1 ) AS LOCATION_CODE, b.location_id, (SELECT prop.property_code FROM pn_properties_all prop WHERE prop.property_id IN ( SELECT property_id FROM pn_locations_all WHERE parent_location_id IS NULL START WITH location_id=b.location_id CONNECT BY PRIOR parent_location_id=location_id )) as property_code, (SELECT prop.property_name FROM pn_properties_all prop WHERE prop.property_id IN ( SELECT property_id FROM pn_locations_all WHERE parent_location_id IS NULL START WITH location_id=b.location_id CONNECT BY PRIOR parent_location_id=location_id )) as property_name from pn_var_rent_inv_all i, pn_var_rents_all b, pn_var_rent_dates_all c, pn_leases_all lease, pn_var_periods_all period, pn_payment_terms_all term, pn_payment_items_all itm where i.var_rent_id = b.var_rent_id and b.var_rent_id = c.var_rent_id and lease.lease_id =b.lease_id AND period.period_id = i.period_id and i.var_rent_inv_id = term.var_rent_inv_id and term.status = 'APPROVED' and term.payment_term_id = itm.payment_term_id

View Text - HTML Formatted

SELECT I.VAR_RENT_INV_ID REFERENCE_NUM
, B.RENT_NUM
, B.VAR_RENT_ID
, (SELECT COMPANY_NAME
FROM PN_LEASE_CONTACT_ASSIGN_V
WHERE LEASE_ID = LEASE.LEASE_ID
AND LEASE_ROLE_TYPE = 'P'
AND STATUS = 'A'
AND ROWNUM=1) PARENT
, LEASE.LEASE_STATUS
, PRORATION_RULE
, LEASE.LEASE_TYPE_CODE LEASE_TYPE
, LEASE.LEASE_CLASS_CODE LEASE_CLASS
, I.INVOICE_DATE ACT_INV_DATE
, PN_VAR_RENT_CALC_PKG.INV_SCH_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_DATE
, PN_VAR_RENT_CALC_PKG.INV_START_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_START_DATE
, PN_VAR_RENT_CALC_PKG.INV_END_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_END_DATE
, I.ACTUAL_INVOICED_AMOUNT ACT_AMOUNT
, DECODE(B.CUMULATIVE_VOL
, 'C'
, 'CUMULATIVE'
, 'N'
, 'NON-CUMULATIVE'
, 'T'
, 'TRUE UP'
, 'Y'
, 'YEAR TO DATE'
, 'NONE') METHOD
, DECODE(C.INVG_FREQ_CODE
, 'MON'
, 'MONTHLY'
, 'QTR'
, 'QUARTERLY'
, 'SA'
, 'SEMI ANNUALLY'
, 'YR'
, 'YEARLY'
, 'OT'
, 'ONE TIME'
, C.INVG_FREQ_CODE) BILLING_FREQ
, DECODE(C.VRG_REPTG_FREQ_CODE
, 'MON'
, 'MONTHLY'
, 'QTR'
, 'QUARTERLY'
, 'SA'
, 'SEMI ANNUALLY'
, 'YR'
, 'YEARLY'
, 'OT'
, 'ONE TIME'
, C.REPTG_FREQ_CODE) REPORTING_FREQUENCY
, LEASE.NAME LEASE_NAME
, LEASE.LEASE_NUM LEASE_NUMBER
, LEASE.LEASE_ID
, 'TERM NOT CREATED' BILL_STATUS
, PERIOD.START_DATE
, PERIOD.END_DATE
, PERIOD.PERIOD_ID
, B.ABSTRACTED_BY_USER
, PN_VAR_RENT_CALC_PKG.INCLUDE_PRD_NO_TERM(PERIOD.PERIOD_ID) AS INCL_PRD
, (SELECT LOCATION_CODE
FROM PN_LOCATIONS_ALL
WHERE LOCATION_ID = B.LOCATION_ID
AND ROWNUM =1) AS LOCATION_CODE
, B.LOCATION_ID
, (SELECT PROP.PROPERTY_CODE
FROM PN_PROPERTIES_ALL PROP
WHERE PROP.PROPERTY_ID IN ( SELECT PROPERTY_ID
FROM PN_LOCATIONS_ALL
WHERE PARENT_LOCATION_ID IS NULL START WITH LOCATION_ID=B.LOCATION_ID CONNECT BY PRIOR PARENT_LOCATION_ID=LOCATION_ID) ) AS PROPERTY_CODE
, (SELECT PROP.PROPERTY_NAME
FROM PN_PROPERTIES_ALL PROP
WHERE PROP.PROPERTY_ID IN ( SELECT PROPERTY_ID
FROM PN_LOCATIONS_ALL
WHERE PARENT_LOCATION_ID IS NULL START WITH LOCATION_ID=B.LOCATION_ID CONNECT BY PRIOR PARENT_LOCATION_ID=LOCATION_ID) ) AS PROPERTY_NAME
FROM PN_VAR_RENT_INV_ALL I
, PN_VAR_RENTS_ALL B
, PN_VAR_RENT_DATES_ALL C
, PN_LEASES_ALL LEASE
, PN_VAR_PERIODS_ALL PERIOD
WHERE I.VAR_RENT_ID = B.VAR_RENT_ID
AND B.VAR_RENT_ID = C.VAR_RENT_ID
AND I.ACTUAL_TERM_STATUS = 'N'
AND I.ACTUAL_EXP_CODE = 'N'
AND LEASE.LEASE_ID =B.LEASE_ID
AND PERIOD.PERIOD_ID = I.PERIOD_ID UNION ALL SELECT I.VAR_RENT_INV_ID REFERENCE_NUM
, B.RENT_NUM
, B.VAR_RENT_ID
, (SELECT COMPANY_NAME
FROM PN_LEASE_CONTACT_ASSIGN_V
WHERE LEASE_ID = LEASE.LEASE_ID
AND LEASE_ROLE_TYPE = 'P'
AND STATUS = 'A'
AND ROWNUM=1) PARENT
, LEASE.LEASE_STATUS
, PRORATION_RULE
, LEASE.LEASE_TYPE_CODE LEASE_TYPE
, LEASE.LEASE_CLASS_CODE LEASE_CLASS
, I.INVOICE_DATE ACT_INV_DATE
, PN_VAR_RENT_CALC_PKG.INV_SCH_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_DATE
, PN_VAR_RENT_CALC_PKG.INV_START_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_START_DATE
, PN_VAR_RENT_CALC_PKG.INV_END_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_END_DATE
, I.ACTUAL_INVOICED_AMOUNT ACT_AMOUNT
, DECODE(B.CUMULATIVE_VOL
, 'C'
, 'CUMULATIVE'
, 'M'
, 'MANUAL'
, 'N'
, 'NON-CUMULATIVE'
, 'T'
, 'TRUE UP'
, 'Y'
, 'YEAR TO DATE'
, 'NONE') METHOD
, DECODE(C.INVG_FREQ_CODE
, 'MON'
, 'MONTHLY'
, 'QTR'
, 'QUARTERLY'
, 'SA'
, 'SEMI ANNUALLY'
, 'YR'
, 'YEARLY'
, 'OT'
, 'ONE TIME'
, C.INVG_FREQ_CODE) BILLING_FREQ
, DECODE(C.VRG_REPTG_FREQ_CODE
, 'MON'
, 'MONTHLY'
, 'QTR'
, 'QUARTERLY'
, 'SA'
, 'SEMI ANNUALLY'
, 'YR'
, 'YEARLY'
, 'OT'
, 'ONE TIME'
, C.REPTG_FREQ_CODE) REPORTING_FREQUENCY
, LEASE.NAME LEASE_NAME
, LEASE.LEASE_NUM LEASE_NUMBER
, LEASE.LEASE_ID
, 'TERM CREATED
, NOT APPROVED' BILL_STATUS
, PERIOD.START_DATE
, PERIOD.END_DATE
, PERIOD.PERIOD_ID
, B.ABSTRACTED_BY_USER
, PN_VAR_RENT_CALC_PKG.INCLUDE_PRD_NO_TERM(PERIOD.PERIOD_ID) AS INCL_PRD
, (SELECT LOCATION_CODE
FROM PN_LOCATIONS_ALL
WHERE LOCATION_ID = B.LOCATION_ID
AND ROWNUM =1) AS LOCATION_CODE
, B.LOCATION_ID
, (SELECT PROP.PROPERTY_CODE
FROM PN_PROPERTIES_ALL PROP
WHERE PROP.PROPERTY_ID IN ( SELECT PROPERTY_ID
FROM PN_LOCATIONS_ALL
WHERE PARENT_LOCATION_ID IS NULL START WITH LOCATION_ID=B.LOCATION_ID CONNECT BY PRIOR PARENT_LOCATION_ID=LOCATION_ID) ) AS PROPERTY_CODE
, (SELECT PROP.PROPERTY_NAME
FROM PN_PROPERTIES_ALL PROP
WHERE PROP.PROPERTY_ID IN ( SELECT PROPERTY_ID
FROM PN_LOCATIONS_ALL
WHERE PARENT_LOCATION_ID IS NULL START WITH LOCATION_ID=B.LOCATION_ID CONNECT BY PRIOR PARENT_LOCATION_ID=LOCATION_ID) ) AS PROPERTY_NAME
FROM PN_VAR_RENT_INV_ALL I
, PN_VAR_RENTS_ALL B
, PN_VAR_RENT_DATES_ALL C
, PN_LEASES_ALL LEASE
, PN_VAR_PERIODS_ALL PERIOD
WHERE I.VAR_RENT_ID = B.VAR_RENT_ID
AND B.VAR_RENT_ID = C.VAR_RENT_ID
AND LEASE.LEASE_ID =B.LEASE_ID
AND PERIOD.PERIOD_ID = I.PERIOD_ID
AND I.ACTUAL_TERM_STATUS = 'Y'
AND I.ACTUAL_EXP_CODE = 'N' UNION ALL SELECT I.VAR_RENT_INV_ID REFERENCE_NUM
, B.RENT_NUM
, B.VAR_RENT_ID
, (SELECT COMPANY_NAME
FROM PN_LEASE_CONTACT_ASSIGN_V
WHERE LEASE_ID = LEASE.LEASE_ID
AND LEASE_ROLE_TYPE = 'P'
AND STATUS = 'A'
AND ROWNUM=1) PARENT
, LEASE.LEASE_STATUS
, PRORATION_RULE
, LEASE.LEASE_TYPE_CODE LEASE_TYPE
, LEASE.LEASE_CLASS_CODE LEASE_CLASS
, I.INVOICE_DATE ACT_INV_DATE
, PN_VAR_RENT_CALC_PKG.INV_SCH_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_DATE
, PN_VAR_RENT_CALC_PKG.INV_START_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_START_DATE
, PN_VAR_RENT_CALC_PKG.INV_END_DATE(I.INVOICE_DATE
, I.VAR_RENT_ID
, I.PERIOD_ID) AS INV_END_DATE
, I.ACTUAL_INVOICED_AMOUNT ACT_AMOUNT
, DECODE(B.CUMULATIVE_VOL
, 'C'
, 'CUMULATIVE'
, 'M'
, 'MANUAL'
, 'N'
, 'NON-CUMULATIVE'
, 'T'
, 'TRUE UP'
, 'Y'
, 'YEAR TO DATE'
, 'NONE') METHOD
, DECODE(C.INVG_FREQ_CODE
, 'MON'
, 'MONTHLY'
, 'QTR'
, 'QUARTERLY'
, 'SA'
, 'SEMI ANNUALLY'
, 'YR'
, 'YEARLY'
, 'OT'
, 'ONE TIME'
, C.INVG_FREQ_CODE) BILLING_FREQ
, DECODE(C.VRG_REPTG_FREQ_CODE
, 'MON'
, 'MONTHLY'
, 'QTR'
, 'QUARTERLY'
, 'SA'
, 'SEMI ANNUALLY'
, 'YR'
, 'YEARLY'
, 'OT'
, 'ONE TIME'
, C.REPTG_FREQ_CODE) REPORTING_FREQUENCY
, LEASE.NAME LEASE_NAME
, LEASE.LEASE_NUM LEASE_NUMBER
, LEASE.LEASE_ID
, DECODE(NVL(ITM.TRANSFERRED_TO_AR_FLAG
, 'N')
, 'N'
, (DECODE(NVL(ITM.TRANSFERRED_TO_AP_FLAG
, 'N')
, 'N'
, 'TERM APPROVED'
, 'TERM EXPORTED TO AP'))
, 'Y'
, 'TERM EXPORTED TO AR') BILL_STATUS
, PERIOD.START_DATE
, PERIOD.END_DATE
, PERIOD.PERIOD_ID
, B.ABSTRACTED_BY_USER
, PN_VAR_RENT_CALC_PKG.INCLUDE_PRD_NO_TERM(PERIOD.PERIOD_ID) AS INCL_PRD
, (SELECT LOCATION_CODE
FROM PN_LOCATIONS_ALL
WHERE LOCATION_ID = B.LOCATION_ID
AND ROWNUM =1 ) AS LOCATION_CODE
, B.LOCATION_ID
, (SELECT PROP.PROPERTY_CODE
FROM PN_PROPERTIES_ALL PROP
WHERE PROP.PROPERTY_ID IN ( SELECT PROPERTY_ID
FROM PN_LOCATIONS_ALL
WHERE PARENT_LOCATION_ID IS NULL START WITH LOCATION_ID=B.LOCATION_ID CONNECT BY PRIOR PARENT_LOCATION_ID=LOCATION_ID )) AS PROPERTY_CODE
, (SELECT PROP.PROPERTY_NAME
FROM PN_PROPERTIES_ALL PROP
WHERE PROP.PROPERTY_ID IN ( SELECT PROPERTY_ID
FROM PN_LOCATIONS_ALL
WHERE PARENT_LOCATION_ID IS NULL START WITH LOCATION_ID=B.LOCATION_ID CONNECT BY PRIOR PARENT_LOCATION_ID=LOCATION_ID )) AS PROPERTY_NAME
FROM PN_VAR_RENT_INV_ALL I
, PN_VAR_RENTS_ALL B
, PN_VAR_RENT_DATES_ALL C
, PN_LEASES_ALL LEASE
, PN_VAR_PERIODS_ALL PERIOD
, PN_PAYMENT_TERMS_ALL TERM
, PN_PAYMENT_ITEMS_ALL ITM
WHERE I.VAR_RENT_ID = B.VAR_RENT_ID
AND B.VAR_RENT_ID = C.VAR_RENT_ID
AND LEASE.LEASE_ID =B.LEASE_ID
AND PERIOD.PERIOD_ID = I.PERIOD_ID
AND I.VAR_RENT_INV_ID = TERM.VAR_RENT_INV_ID
AND TERM.STATUS = 'APPROVED'
AND TERM.PAYMENT_TERM_ID = ITM.PAYMENT_TERM_ID