VIEW: APPS.PN_VAR_TERMS_V

Source

View Text - Preformatted

SELECT vinv.invoice_date invoice_date, vinv.var_rent_inv_id var_rent_inv_id, vrent.var_rent_id var_rent_id, decode(vinv.period_id,NULL,(select period_id from pn_var_periods_all WHERE var_rent_id=vrent.var_rent_id and period_num=1),vinv.period_id)period_id, PN_VAR_ABATEMENTS_PKG.get_include_term(pterm.payment_term_id, nvl(vinv.var_rent_inv_id,-1), vrent.var_rent_id) include_term, PN_VAR_ABATEMENTS_PKG.get_include_increases(pterm.payment_term_id, nvl(vinv.var_rent_inv_id,-1), vrent.var_rent_id) include_increases, 'N' update_flag, pterm.rowid row_id, pterm.payment_term_id payment_term_id, pterm.lease_id lease_id, pterm.start_date start_date, pterm.end_date end_date, pterm.frequency_code frequency_code, pplook.meaning payment_purpose, ptlook.meaning payment_term_type, pflook.meaning frequency_type, pov.vendor_name vendor_name, pov.segment1 vendor_number, povs.vendor_site_code vendor_site, hz.party_name customer_name, hz.account_number customer_number, hcsu.location customer_site_use, pterm.normalize normalize, pterm.actual_amount actual_amount, pterm.estimated_amount estimated_amount, pterm.last_update_date last_update_date, pterm.last_updated_by last_updated_by, pterm.creation_date creation_date, pterm.created_by created_by, pterm.last_update_login last_update_login, vrent.org_id org_id, pterm.currency_code currency_code FROM (SELECT hp.party_name, hca.cust_account_id, hca.account_number FROM hz_cust_accounts_all hca, hz_parties hp where hp.party_id = hca.party_id) hz, hz_cust_site_uses_all hcsu, po_vendors pov, po_vendor_sites_all povs, fnd_lookups pflook, fnd_lookups ptlook, fnd_lookups pplook, pn_payment_terms_all pterm, pn_var_rents_all vrent, pn_var_rent_inv_all vinv WHERE pplook.lookup_type = 'PN_PAYMENT_PURPOSE_TYPE' AND pplook.lookup_code = pterm.payment_purpose_code AND ptlook.lookup_type = 'PN_PAYMENT_TERM_TYPE' AND ptlook.lookup_code = pterm.payment_term_type_code AND pflook.lookup_type = 'PN_PAYMENT_FREQUENCY_TYPE' AND pflook.lookup_code = pterm.frequency_code AND pov.vendor_id(+) = pterm.vendor_id AND povs.vendor_site_id(+) = pterm.vendor_site_id AND hz.cust_account_id(+) = pterm.customer_id AND hcsu.site_use_id(+) = pterm.customer_site_use_id AND vrent.lease_id = pterm.lease_id AND vrent.var_rent_id = vinv.var_rent_id(+) AND pterm.start_date <= (SELECT MAX(gd.grp_end_date) FROM pn_var_grp_dates_all gd WHERE gd.var_rent_id=vrent.var_rent_id) AND pterm.end_date >= (SELECT MIN(grp_start_date) FROM pn_var_grp_dates_all gd1 WHERE gd1.var_rent_id=vrent.var_rent_id) AND pterm.var_rent_inv_id IS NULL AND (pterm.index_period_id IS NULL OR pterm.status='APPROVED') AND vinv.adjust_num(+) = 0

View Text - HTML Formatted

SELECT VINV.INVOICE_DATE INVOICE_DATE
, VINV.VAR_RENT_INV_ID VAR_RENT_INV_ID
, VRENT.VAR_RENT_ID VAR_RENT_ID
, DECODE(VINV.PERIOD_ID
, NULL
, (SELECT PERIOD_ID
FROM PN_VAR_PERIODS_ALL
WHERE VAR_RENT_ID=VRENT.VAR_RENT_ID
AND PERIOD_NUM=1)
, VINV.PERIOD_ID)PERIOD_ID
, PN_VAR_ABATEMENTS_PKG.GET_INCLUDE_TERM(PTERM.PAYMENT_TERM_ID
, NVL(VINV.VAR_RENT_INV_ID
, -1)
, VRENT.VAR_RENT_ID) INCLUDE_TERM
, PN_VAR_ABATEMENTS_PKG.GET_INCLUDE_INCREASES(PTERM.PAYMENT_TERM_ID
, NVL(VINV.VAR_RENT_INV_ID
, -1)
, VRENT.VAR_RENT_ID) INCLUDE_INCREASES
, 'N' UPDATE_FLAG
, PTERM.ROWID ROW_ID
, PTERM.PAYMENT_TERM_ID PAYMENT_TERM_ID
, PTERM.LEASE_ID LEASE_ID
, PTERM.START_DATE START_DATE
, PTERM.END_DATE END_DATE
, PTERM.FREQUENCY_CODE FREQUENCY_CODE
, PPLOOK.MEANING PAYMENT_PURPOSE
, PTLOOK.MEANING PAYMENT_TERM_TYPE
, PFLOOK.MEANING FREQUENCY_TYPE
, POV.VENDOR_NAME VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, HZ.PARTY_NAME CUSTOMER_NAME
, HZ.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HCSU.LOCATION CUSTOMER_SITE_USE
, PTERM.NORMALIZE NORMALIZE
, PTERM.ACTUAL_AMOUNT ACTUAL_AMOUNT
, PTERM.ESTIMATED_AMOUNT ESTIMATED_AMOUNT
, PTERM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PTERM.LAST_UPDATED_BY LAST_UPDATED_BY
, PTERM.CREATION_DATE CREATION_DATE
, PTERM.CREATED_BY CREATED_BY
, PTERM.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, VRENT.ORG_ID ORG_ID
, PTERM.CURRENCY_CODE CURRENCY_CODE
FROM (SELECT HP.PARTY_NAME
, HCA.CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER
FROM HZ_CUST_ACCOUNTS_ALL HCA
, HZ_PARTIES HP
WHERE HP.PARTY_ID = HCA.PARTY_ID) HZ
, HZ_CUST_SITE_USES_ALL HCSU
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, FND_LOOKUPS PFLOOK
, FND_LOOKUPS PTLOOK
, FND_LOOKUPS PPLOOK
, PN_PAYMENT_TERMS_ALL PTERM
, PN_VAR_RENTS_ALL VRENT
, PN_VAR_RENT_INV_ALL VINV
WHERE PPLOOK.LOOKUP_TYPE = 'PN_PAYMENT_PURPOSE_TYPE'
AND PPLOOK.LOOKUP_CODE = PTERM.PAYMENT_PURPOSE_CODE
AND PTLOOK.LOOKUP_TYPE = 'PN_PAYMENT_TERM_TYPE'
AND PTLOOK.LOOKUP_CODE = PTERM.PAYMENT_TERM_TYPE_CODE
AND PFLOOK.LOOKUP_TYPE = 'PN_PAYMENT_FREQUENCY_TYPE'
AND PFLOOK.LOOKUP_CODE = PTERM.FREQUENCY_CODE
AND POV.VENDOR_ID(+) = PTERM.VENDOR_ID
AND POVS.VENDOR_SITE_ID(+) = PTERM.VENDOR_SITE_ID
AND HZ.CUST_ACCOUNT_ID(+) = PTERM.CUSTOMER_ID
AND HCSU.SITE_USE_ID(+) = PTERM.CUSTOMER_SITE_USE_ID
AND VRENT.LEASE_ID = PTERM.LEASE_ID
AND VRENT.VAR_RENT_ID = VINV.VAR_RENT_ID(+)
AND PTERM.START_DATE <= (SELECT MAX(GD.GRP_END_DATE)
FROM PN_VAR_GRP_DATES_ALL GD
WHERE GD.VAR_RENT_ID=VRENT.VAR_RENT_ID)
AND PTERM.END_DATE >= (SELECT MIN(GRP_START_DATE)
FROM PN_VAR_GRP_DATES_ALL GD1
WHERE GD1.VAR_RENT_ID=VRENT.VAR_RENT_ID)
AND PTERM.VAR_RENT_INV_ID IS NULL
AND (PTERM.INDEX_PERIOD_ID IS NULL OR PTERM.STATUS='APPROVED')
AND VINV.ADJUST_NUM(+) = 0