ECC Property Manager, Lease Pay Terms Data Set

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: pn-payterm
Query Procedure: pn_ecc_load_pkg.GET_ECC_DATA_LOAD_INFO
Security Procedure: pn_ecc_load_pkg.GetFilterAttributeValues
Run ECC Property Manager, Lease Pay Terms Data Set and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM
(
SELECT dfv.*,lcdfv.*,
ACCOUNTING_METHOD,
ACTUAL_AMOUNT,
AMOUNT_AREA,
AP_INVOICE_NUM,
AREA,
trunc(AREA_UTILIZED) AREA_UTILIZED,
ASSET_NUMBER,
ASSIGNABLE_AREA,
CALENDAR_MONTH,
CONV_MOUNT,
CONV_RATE,
COUNTRY,
CURRENCY_CODE,
pn_ecc_util_pvt.get_lookup_values('PN_PRORATION_RULE',dfvalias.day_convention,dfvalias.language) DAY_CONVENTION,
DISCOUNT_RATE,
ECC_SPEC_ID,
EXEMPT_YN,
EXPENSE_ACCRUED,
EXPENSE_UNACCRUED,
FUNC_CURRENCY,
GL_END_DATE,
GL_PERIOD_NAME,
GL_START_DATE,
GROSS_AREA,
INTEREST_ACCRUED,
ITEM,
ITEM_CATEGORY,
pn_ecc_util_pvt.get_lookup_values('PN_ECC_LEASE_CATEGORY',dfvalias.item_class,dfvalias.language) ITEM_CLASS,
LEASE_APPROVAL_STATUS,
LEASE_CLASS,
LEASE_DAYS,
LEASE_END_DATE,
LEASE_ID,
LEASE_MONTHS,
LEASE_NUM,
LEASE_START_DATE,
LEASE_STATUS,
LEASE_TYPE,
LEASE_YEARS,
LEDGER_CURRENCY,
LEDGER_NAME,
LIABILITY_FLAG,
LIABILITY_REMAINING,
LOCATION_NAME,
OCCUPANCY_MAX,
OCCUPANCY_OPTIMUN,
OCCUPANCY_PERCENT,
OCCUPANCY_STATUS,
OCCUPANCY_UTILIZED,
ORG_ID,
fnd_access_control_util.get_org_name(ORG_ID) ORG_NAME,
PAYMENT_CURRENCY,
PAYMENT_DUE_DATE,
PAYMENT_ITEM,
PAYMENT_ITEM_ID,
PAYMENT_PURPOSE,
PAYMENT_TERM_AMOUNT,
PAYMENT_TERM_FLAG,
PAYMENT_TERM_ID,
PAYMENT_TYPE,
PAY_FREQUENCY,
PRIMARY_REGIME,
PROPERTY_CODE,
PROPERTY_NAME,
REGION_NAME,
REMAINING_PERIODS,
RENTABLE_AREA,
ROU_FLAG,
ROU_REMAINING,
SCHEDULE_DAY,
SUPPLIER,
LEASE_SUPPLIER,
TERM_END_DATE,
TERM_START_DATE,
TERRITORY,
UOM,
USABLE_AREA,
VACANT_AREA,
to_char(payment_due_date,'YYYY-MM') rep_period,
to_date('01-'||to_char(payment_due_date,'MON-YYYY')) trans_date_range,
to_char(lease_end_date,'YYYY-MM') TERM_PERIOD,
decode(to_char(LEASE_START_DATE,'YYYY-MM'),to_char(sysdate,'YYYY-MM'),1,0) PERIOD_CREATED,
decode(sign(trunc(last_day(LEASE_END_DATE)) - trunc(last_day(sysdate))),-1,1,0) PERIOD_TERMEXP,
ASSET_ROU_REMAINING,
ASSET_MODEL,
UNITS,
UNIT_COST,
TOTAL_COST,
INSTALL_AT,
ADDRESS_LINE1,
LANGUAGE,
ZONE,
LOCATION_TYPE,
TENURE,
CLASS,
ASSET_DESCRIPTION,
PORTFOLIO,
CONDITION,
PROCESSING_STATUS_FLAG,
REGIME_CODE,
TERM_ROU_FLAG,
TERM_LIABILITY_FLAG,
REPFROMINC_FLAG,
NORMALIZE_FLAG,
INTERCOMPANY_FLAG,
LEASE_INTEREST_ACCRUED
FROM
    pn_ecc_pay_term_v dfvalias
 , (select ROW_ID "'HD_ROW_ID'",CONTEXT_VALUE "'HD_CONTEXT_VALUE'",to_char(DATE_ENTERED, 'yyyy-mm-dd')  "'HD_DATE_ENTERED'",ENTERED_BY "'HD_ENTERED_BY'",INVOICE_ "'HD_INVOICE_'",to_char(LANDLORD_CONTRIBUTION) "'HD_LANDLORD_CONTRIBUTION'",to_char(DUE_DATE_OF_INVOICE, 'yyyy-mm-dd')  "'HD_DUE_DATE_OF_INVOICE'",CONCATENATED_SEGMENTS "'HD_CONCATENATED_SEGMENTS'" from PN_LEASE_DETAILS_ALL_DFV) dfv , (select ROW_ID "'LC_ROW_ID'",CONTEXT_VALUE "'LC_CONTEXT_VALUE'",MORTGAGE_BALANCE "'LC_MORTGAGE_BALANCE'",MORTGAGE_DUE_DATE "'LC_MORTGAGE_DUE_DATE'",CONCATENATED_SEGMENTS "'LC_CONCATENATED_SEGMENTS'" from PN_LOCATIONS_ALL_DFV) lcdfv  WHERE dfvalias.HD_ROWID = dfv."'HD_ROW_ID'"(+) AND dfvalias.LC_ROWID = lcdfv."'LC_ROW_ID'"(+) AND  1=1 and language in ('US') ) PIVOT (MAX(ACCOUNTING_METHOD) AS ACCOUNTING_METHOD,
         MAX(DAY_CONVENTION) AS DAY_CONVENTION,
		 MAX(ITEM_CLASS) AS ITEM_CLASS,
         MAX(LEASE_APPROVAL_STATUS) AS LEASE_APPROVAL_STATUS,
         MAX(LEASE_CLASS) AS LEASE_CLASS,
         MAX(LEASE_STATUS) AS LEASE_STATUS,
         MAX(LEASE_TYPE) AS LEASE_TYPE,
         MAX(PAYMENT_PURPOSE) AS PAYMENT_PURPOSE,
         MAX(PAYMENT_TYPE) AS PAYMENT_TYPE,
         MAX(PAY_FREQUENCY) AS PAY_FREQUENCY,
         MAX(ZONE) AS ZONE,
         MAX(LOCATION_TYPE) AS LOCATION_TYPE,
         MAX(TENURE) AS TENURE,
         MAX(CLASS) AS CLASS,
         MAX(ASSET_DESCRIPTION) AS ASSET_DESCRIPTION,
         MAX(PORTFOLIO) AS PORTFOLIO,
         MAX(CONDITION) AS CONDITION,
         MAX(REGIME_CODE) AS REGIME_CODE,
         MAX(TERM_ROU_FLAG) AS TERM_ROU_FLAG,
         MAX(TERM_LIABILITY_FLAG) AS TERM_LIABILITY_FLAG,
         MAX(REPFROMINC_FLAG) AS REPFROMINC_FLAG,
         MAX(NORMALIZE_FLAG) AS NORMALIZE_FLAG,
         MAX(INTERCOMPANY_FLAG) AS INTERCOMPANY_FLAG,
         MAX(OCCUPANCY_STATUS) AS OCCUPANCY_STATUS
FOR LANGUAGE in  ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV
Blitz Report™