VIEW: APPS.PN_EXCLUDE_TERMS_V

Source

View Text - Preformatted

SELECT pterm.rowid row_id, pterm.payment_term_id payment_term_id, 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, pterm.payment_purpose_code payment_purpose_code, pterm.payment_term_type_code payment_term_type_code, pterm.frequency_code frequency_code, pterm.normalize normalize, pterm.lease_id lease_id, pterm.lease_change_id lease_change_id, pterm.start_date start_date, pterm.end_date end_date, pterm.vendor_id vendor_id, pterm.vendor_site_id vendor_site_id, pterm.customer_id customer_id, pterm.customer_site_use_id customer_site_use_id, pterm.target_date target_date, pterm.actual_amount actual_amount, pterm.estimated_amount estimated_amount, pterm.set_of_books_id set_of_books_id, pterm.currency_code currency_code, pterm.rate rate, pterm.attribute_category attribute_category, pterm.attribute1 attribute1, pterm.attribute2 attribute2, pterm.attribute3 attribute3, pterm.attribute4 attribute4, pterm.attribute5 attribute5, pterm.attribute6 attribute6, pterm.attribute7 attribute7, pterm.attribute8 attribute8, pterm.attribute9 attribute9, pterm.attribute10 attribute10, pterm.attribute11 attribute11, pterm.attribute12 attribute12, pterm.attribute13 attribute13, pterm.attribute14 attribute14, pterm.attribute15 attribute15, pterm.location_id location_id, pterm.schedule_day schedule_day, pterm.cust_ship_site_id cust_ship_site_id, pterm.ap_ar_term_id ap_ar_term_id, pterm.cust_trx_type_id cust_trx_type_id, pterm.project_id project_id, pterm.task_id task_id, pterm.organization_id organization_id, pterm.expenditure_type expenditure_type, pterm.expenditure_item_date expenditure_item_date, pterm.tax_group_id tax_group_id, pterm.tax_code_id tax_code_id, pterm.tax_included tax_included, pterm.distribution_set_id distribution_set_id, pterm.inv_rule_id inv_rule_id, pterm.account_rule_id account_rule_id, pterm.salesrep_id salesrep_id, pterm.approved_by approved_by, pterm.status status, pterm.index_period_id index_period_id, pterm.index_term_indicator index_term_indicator, pterm.po_header_id po_header_id, pterm.cust_po_number cust_po_number, pterm.receipt_method_id receipt_method_id, pterm.var_rent_inv_id var_rent_inv_id, pterm.var_rent_type var_rent_type, pterm.changed_flag changed_flag, pterm.recoverable_flag recoverable_flag, pterm.period_billrec_id period_billrec_id, pterm.rec_agr_line_id rec_agr_line_id, pterm.amount_type amount_type, pterm.norm_start_date norm_start_date, pterm.project_attribute_category interface_line_context, pterm.project_attribute1 interface_line_attribute1, pterm.project_attribute2 interface_line_attribute2, pterm.project_attribute3 interface_line_attribute3, pterm.project_attribute4 interface_line_attribute4, pterm.project_attribute5 interface_line_attribute5, pterm.project_attribute6 interface_line_attribute6, pterm.project_attribute7 interface_line_attribute7, pterm.project_attribute8 interface_line_attribute8, pterm.project_attribute9 interface_line_attribute9, pterm.project_attribute10 interface_line_attribute10, pterm.project_attribute11 interface_line_attribute11, pterm.project_attribute12 interface_line_attribute12, pterm.project_attribute13 interface_line_attribute13, pterm.project_attribute14 interface_line_attribute14, pterm.project_attribute15 interface_line_attribute15, pterm.term_template_id term_template_id, pplook.meaning payment_purpose, ptlook.meaning payment_term_type, pov.vendor_name vendor_name, pov.segment1 vendor_number, povs.vendor_site_code vendor_site, povs.auto_tax_calc_flag tax_flag, arc.customer_name customer_name, arc.customer_number customer_number, hzcb.location customer_site_use, hzcs.location cust_ship_site_use, PNP_UTIL_FUNC.get_total_payment_term_amt(pterm.payment_term_id) total_payment, PNP_UTIL_FUNC.get_total_leased_area(pterm.lease_id, pterm.start_date) leased_area, pterm.area_type_code area_type_code, pterm.area area, pterm.grouping_rule_id grouping_rule_id, ppgr.name inv_group_name, pterm.org_id org_id, ptemp.name term_template, pterm.term_altered_flag term_altered_flag, pterm.source_code source_code, pslook.meaning source_desc, pterm.term_comments term_comments, pterm.tax_classification_code tax_classification_code, pclook.meaning tcc_payment, pclook.meaning tcc_billing, pterm.legal_entity_id legal_entity_id, pileases.index_lease_id index_lease_id, (select decode(include_exclude_flag,'I','Y','E','N') from pn_index_exclude_term_all eterm where eterm.payment_term_id = pterm.payment_term_id and eterm.index_lease_id = pileases.index_lease_id union all select case when incl_terms_by_default_flag = 'Y' OR (incl_terms_by_default_flag = 'G' and NVL(pileases.gross_flag,'N') = 'Y' ) OR (incl_terms_by_default_flag = 'U' and NVL(pileases.gross_flag,'N') = 'N' ) THEN 'Y' ELSE 'N' END from pn_system_setup_options where org_id = pterm.org_id and not exists(select null from pn_index_exclude_term_all eterm where eterm.payment_term_id = pterm.payment_term_id and eterm.index_lease_id = pileases.index_lease_id) ) exclude_flag, (select index_exclude_term_id FROM pn_index_exclude_term_all WHERE payment_term_id = pterm.payment_term_id AND index_lease_id = pileases.index_lease_id) index_exclude_term_id, pterm.include_in_var_rent include_in_var_rent FROM hz_cust_site_uses_all hzcb, hz_cust_site_uses_all hzcs, ar_customers arc, po_vendors pov, po_vendor_sites_all povs, fnd_lookups ptlook, fnd_lookups pplook, fnd_lookups pslook, fnd_lookups pclook, pn_pay_group_rules ppgr, pn_payment_terms pterm, pn_term_templates_all ptemp, pn_leases_all pleases, pn_index_leases_all pileases WHERE pterm.lease_id = pleases.lease_id AND pleases.lease_class_code IN ('DIRECT','PAY') AND pclook.lookup_type(+) = 'ZX_INPUT_CLASSIFICATIONS' AND pclook.lookup_code(+) = pterm.tax_classification_code AND 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 pslook.lookup_type (+) = 'PN_TERM_SOURCE_TYPE' AND pslook.lookup_code (+) = pterm.source_code AND pov.vendor_id (+) = pterm.vendor_id AND povs.vendor_site_id (+) = pterm.vendor_site_id AND arc.customer_id (+) = pterm.customer_id AND hzcb.site_use_id (+) = pterm.customer_site_use_id AND hzcs.site_use_id (+) = pterm.cust_ship_site_id AND ppgr.grouping_rule_id (+) = pterm.grouping_rule_id AND pterm.term_template_id = ptemp.term_template_id(+) AND pterm.lease_id = pileases.lease_id AND pterm.frequency_code <> 'OT' UNION ALL SELECT pterm.rowid row_id, pterm.payment_term_id payment_term_id, 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, pterm.payment_purpose_code payment_purpose_code, pterm.payment_term_type_code payment_term_type_code, pterm.frequency_code frequency_code, pterm.normalize normalize, pterm.lease_id lease_id, pterm.lease_change_id lease_change_id, pterm.start_date start_date, pterm.end_date end_date, pterm.vendor_id vendor_id, pterm.vendor_site_id vendor_site_id, pterm.customer_id customer_id, pterm.customer_site_use_id customer_site_use_id, pterm.target_date target_date, pterm.actual_amount actual_amount, pterm.estimated_amount estimated_amount, pterm.set_of_books_id set_of_books_id, pterm.currency_code currency_code, pterm.rate rate, pterm.attribute_category attribute_category, pterm.attribute1 attribute1, pterm.attribute2 attribute2, pterm.attribute3 attribute3, pterm.attribute4 attribute4, pterm.attribute5 attribute5, pterm.attribute6 attribute6, pterm.attribute7 attribute7, pterm.attribute8 attribute8, pterm.attribute9 attribute9, pterm.attribute10 attribute10, pterm.attribute11 attribute11, pterm.attribute12 attribute12, pterm.attribute13 attribute13, pterm.attribute14 attribute14, pterm.attribute15 attribute15, pterm.location_id location_id, pterm.schedule_day schedule_day, pterm.cust_ship_site_id cust_ship_site_id, pterm.ap_ar_term_id ap_ar_term_id, pterm.cust_trx_type_id cust_trx_type_id, pterm.project_id project_id, pterm.task_id task_id, pterm.organization_id organization_id, pterm.expenditure_type expenditure_type, pterm.expenditure_item_date expenditure_item_date, pterm.tax_group_id tax_group_id, pterm.tax_code_id tax_code_id, pterm.tax_included tax_included, pterm.distribution_set_id distribution_set_id, pterm.inv_rule_id inv_rule_id, pterm.account_rule_id account_rule_id, pterm.salesrep_id salesrep_id, pterm.approved_by approved_by, pterm.status status, pterm.index_period_id index_period_id, pterm.index_term_indicator index_term_indicator, pterm.po_header_id po_header_id, pterm.cust_po_number cust_po_number, pterm.receipt_method_id receipt_method_id, pterm.var_rent_inv_id var_rent_inv_id, pterm.var_rent_type var_rent_type, pterm.changed_flag changed_flag, pterm.recoverable_flag recoverable_flag, pterm.period_billrec_id period_billrec_id, pterm.rec_agr_line_id rec_agr_line_id, pterm.amount_type amount_type, pterm.norm_start_date norm_start_date, pterm.project_attribute_category interface_line_context, pterm.project_attribute1 interface_line_attribute1, pterm.project_attribute2 interface_line_attribute2, pterm.project_attribute3 interface_line_attribute3, pterm.project_attribute4 interface_line_attribute4, pterm.project_attribute5 interface_line_attribute5, pterm.project_attribute6 interface_line_attribute6, pterm.project_attribute7 interface_line_attribute7, pterm.project_attribute8 interface_line_attribute8, pterm.project_attribute9 interface_line_attribute9, pterm.project_attribute10 interface_line_attribute10, pterm.project_attribute11 interface_line_attribute11, pterm.project_attribute12 interface_line_attribute12, pterm.project_attribute13 interface_line_attribute13, pterm.project_attribute14 interface_line_attribute14, pterm.project_attribute15 interface_line_attribute15, pterm.term_template_id term_template_id, pplook.meaning payment_purpose, ptlook.meaning payment_term_type, pov.vendor_name vendor_name, pov.segment1 vendor_number, povs.vendor_site_code vendor_site, povs.auto_tax_calc_flag tax_flag, arc.customer_name customer_name, arc.customer_number customer_number, hzcb.location customer_site_use, hzcs.location cust_ship_site_use, PNP_UTIL_FUNC.get_total_payment_term_amt(pterm.payment_term_id) total_payment, PNP_UTIL_FUNC.get_total_leased_area(pterm.lease_id, pterm.start_date) leased_area, pterm.area_type_code area_type_code, pterm.area area, pterm.grouping_rule_id grouping_rule_id, ppgr.name inv_group_name, pterm.org_id org_id, ptemp.name term_template, pterm.term_altered_flag term_altered_flag, pterm.source_code source_code, pslook.meaning source_desc, pterm.term_comments term_comments, pterm.tax_classification_code tax_classification_code, pclook.meaning tcc_payment, pclook.meaning tcc_billing, pterm.legal_entity_id legal_entity_id, pileases.index_lease_id index_lease_id, (select decode(include_exclude_flag,'I','Y','E','N') from pn_index_exclude_term_all eterm where eterm.payment_term_id = pterm.payment_term_id and eterm.index_lease_id = pileases.index_lease_id union all select case when incl_terms_by_default_flag = 'Y' OR (incl_terms_by_default_flag = 'G' and NVL(pileases.gross_flag,'N') = 'Y') OR (incl_terms_by_default_flag = 'U' and NVL(pileases.gross_flag,'N') = 'N') THEN 'Y' ELSE 'N' END from pn_system_setup_options where org_id = pterm.org_id and not exists(select null from pn_index_exclude_term_all eterm where eterm.payment_term_id = pterm.payment_term_id and eterm.index_lease_id = pileases.index_lease_id) ) exclude_flag, (select index_exclude_term_id FROM pn_index_exclude_term_all WHERE payment_term_id = pterm.payment_term_id AND index_lease_id = pileases.index_lease_id) index_exclude_term_id, pterm.include_in_var_rent include_in_var_rent FROM hz_cust_site_uses_all hzcb, hz_cust_site_uses_all hzcs, ar_customers arc, po_vendors pov, po_vendor_sites_all povs, fnd_lookups ptlook, fnd_lookups pplook, fnd_lookups pslook, fnd_lookups pclook, pn_pay_group_rules ppgr, pn_payment_terms pterm, pn_term_templates_all ptemp, pn_leases_all pleases, pn_index_leases_all pileases WHERE pterm.lease_id = pleases.lease_id AND pleases.lease_class_code IN ('THIRD_PARTY','SUB_LEASE','REC') AND pclook.lookup_type(+) = 'ZX_OUTPUT_CLASSIFICATIONS' AND pclook.lookup_code(+) = pterm.tax_classification_code AND 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 pslook.lookup_type (+) = 'PN_TERM_SOURCE_TYPE' AND pslook.lookup_code (+) = pterm.source_code AND pov.vendor_id (+) = pterm.vendor_id AND povs.vendor_site_id (+) = pterm.vendor_site_id AND arc.customer_id (+) = pterm.customer_id AND hzcb.site_use_id (+) = pterm.customer_site_use_id AND hzcs.site_use_id (+) = pterm.cust_ship_site_id AND ppgr.grouping_rule_id (+) = pterm.grouping_rule_id AND pterm.term_template_id = ptemp.term_template_id(+) AND pterm.lease_id = pileases.lease_id AND pterm.frequency_code <> 'OT' ORDER BY payment_term_type, start_date, payment_purpose

View Text - HTML Formatted

SELECT PTERM.ROWID ROW_ID
, PTERM.PAYMENT_TERM_ID PAYMENT_TERM_ID
, 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
, PTERM.PAYMENT_PURPOSE_CODE PAYMENT_PURPOSE_CODE
, PTERM.PAYMENT_TERM_TYPE_CODE PAYMENT_TERM_TYPE_CODE
, PTERM.FREQUENCY_CODE FREQUENCY_CODE
, PTERM.NORMALIZE NORMALIZE
, PTERM.LEASE_ID LEASE_ID
, PTERM.LEASE_CHANGE_ID LEASE_CHANGE_ID
, PTERM.START_DATE START_DATE
, PTERM.END_DATE END_DATE
, PTERM.VENDOR_ID VENDOR_ID
, PTERM.VENDOR_SITE_ID VENDOR_SITE_ID
, PTERM.CUSTOMER_ID CUSTOMER_ID
, PTERM.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, PTERM.TARGET_DATE TARGET_DATE
, PTERM.ACTUAL_AMOUNT ACTUAL_AMOUNT
, PTERM.ESTIMATED_AMOUNT ESTIMATED_AMOUNT
, PTERM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PTERM.CURRENCY_CODE CURRENCY_CODE
, PTERM.RATE RATE
, PTERM.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PTERM.ATTRIBUTE1 ATTRIBUTE1
, PTERM.ATTRIBUTE2 ATTRIBUTE2
, PTERM.ATTRIBUTE3 ATTRIBUTE3
, PTERM.ATTRIBUTE4 ATTRIBUTE4
, PTERM.ATTRIBUTE5 ATTRIBUTE5
, PTERM.ATTRIBUTE6 ATTRIBUTE6
, PTERM.ATTRIBUTE7 ATTRIBUTE7
, PTERM.ATTRIBUTE8 ATTRIBUTE8
, PTERM.ATTRIBUTE9 ATTRIBUTE9
, PTERM.ATTRIBUTE10 ATTRIBUTE10
, PTERM.ATTRIBUTE11 ATTRIBUTE11
, PTERM.ATTRIBUTE12 ATTRIBUTE12
, PTERM.ATTRIBUTE13 ATTRIBUTE13
, PTERM.ATTRIBUTE14 ATTRIBUTE14
, PTERM.ATTRIBUTE15 ATTRIBUTE15
, PTERM.LOCATION_ID LOCATION_ID
, PTERM.SCHEDULE_DAY SCHEDULE_DAY
, PTERM.CUST_SHIP_SITE_ID CUST_SHIP_SITE_ID
, PTERM.AP_AR_TERM_ID AP_AR_TERM_ID
, PTERM.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PTERM.PROJECT_ID PROJECT_ID
, PTERM.TASK_ID TASK_ID
, PTERM.ORGANIZATION_ID ORGANIZATION_ID
, PTERM.EXPENDITURE_TYPE EXPENDITURE_TYPE
, PTERM.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, PTERM.TAX_GROUP_ID TAX_GROUP_ID
, PTERM.TAX_CODE_ID TAX_CODE_ID
, PTERM.TAX_INCLUDED TAX_INCLUDED
, PTERM.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, PTERM.INV_RULE_ID INV_RULE_ID
, PTERM.ACCOUNT_RULE_ID ACCOUNT_RULE_ID
, PTERM.SALESREP_ID SALESREP_ID
, PTERM.APPROVED_BY APPROVED_BY
, PTERM.STATUS STATUS
, PTERM.INDEX_PERIOD_ID INDEX_PERIOD_ID
, PTERM.INDEX_TERM_INDICATOR INDEX_TERM_INDICATOR
, PTERM.PO_HEADER_ID PO_HEADER_ID
, PTERM.CUST_PO_NUMBER CUST_PO_NUMBER
, PTERM.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, PTERM.VAR_RENT_INV_ID VAR_RENT_INV_ID
, PTERM.VAR_RENT_TYPE VAR_RENT_TYPE
, PTERM.CHANGED_FLAG CHANGED_FLAG
, PTERM.RECOVERABLE_FLAG RECOVERABLE_FLAG
, PTERM.PERIOD_BILLREC_ID PERIOD_BILLREC_ID
, PTERM.REC_AGR_LINE_ID REC_AGR_LINE_ID
, PTERM.AMOUNT_TYPE AMOUNT_TYPE
, PTERM.NORM_START_DATE NORM_START_DATE
, PTERM.PROJECT_ATTRIBUTE_CATEGORY INTERFACE_LINE_CONTEXT
, PTERM.PROJECT_ATTRIBUTE1 INTERFACE_LINE_ATTRIBUTE1
, PTERM.PROJECT_ATTRIBUTE2 INTERFACE_LINE_ATTRIBUTE2
, PTERM.PROJECT_ATTRIBUTE3 INTERFACE_LINE_ATTRIBUTE3
, PTERM.PROJECT_ATTRIBUTE4 INTERFACE_LINE_ATTRIBUTE4
, PTERM.PROJECT_ATTRIBUTE5 INTERFACE_LINE_ATTRIBUTE5
, PTERM.PROJECT_ATTRIBUTE6 INTERFACE_LINE_ATTRIBUTE6
, PTERM.PROJECT_ATTRIBUTE7 INTERFACE_LINE_ATTRIBUTE7
, PTERM.PROJECT_ATTRIBUTE8 INTERFACE_LINE_ATTRIBUTE8
, PTERM.PROJECT_ATTRIBUTE9 INTERFACE_LINE_ATTRIBUTE9
, PTERM.PROJECT_ATTRIBUTE10 INTERFACE_LINE_ATTRIBUTE10
, PTERM.PROJECT_ATTRIBUTE11 INTERFACE_LINE_ATTRIBUTE11
, PTERM.PROJECT_ATTRIBUTE12 INTERFACE_LINE_ATTRIBUTE12
, PTERM.PROJECT_ATTRIBUTE13 INTERFACE_LINE_ATTRIBUTE13
, PTERM.PROJECT_ATTRIBUTE14 INTERFACE_LINE_ATTRIBUTE14
, PTERM.PROJECT_ATTRIBUTE15 INTERFACE_LINE_ATTRIBUTE15
, PTERM.TERM_TEMPLATE_ID TERM_TEMPLATE_ID
, PPLOOK.MEANING PAYMENT_PURPOSE
, PTLOOK.MEANING PAYMENT_TERM_TYPE
, POV.VENDOR_NAME VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, POVS.AUTO_TAX_CALC_FLAG TAX_FLAG
, ARC.CUSTOMER_NAME CUSTOMER_NAME
, ARC.CUSTOMER_NUMBER CUSTOMER_NUMBER
, HZCB.LOCATION CUSTOMER_SITE_USE
, HZCS.LOCATION CUST_SHIP_SITE_USE
, PNP_UTIL_FUNC.GET_TOTAL_PAYMENT_TERM_AMT(PTERM.PAYMENT_TERM_ID) TOTAL_PAYMENT
, PNP_UTIL_FUNC.GET_TOTAL_LEASED_AREA(PTERM.LEASE_ID
, PTERM.START_DATE) LEASED_AREA
, PTERM.AREA_TYPE_CODE AREA_TYPE_CODE
, PTERM.AREA AREA
, PTERM.GROUPING_RULE_ID GROUPING_RULE_ID
, PPGR.NAME INV_GROUP_NAME
, PTERM.ORG_ID ORG_ID
, PTEMP.NAME TERM_TEMPLATE
, PTERM.TERM_ALTERED_FLAG TERM_ALTERED_FLAG
, PTERM.SOURCE_CODE SOURCE_CODE
, PSLOOK.MEANING SOURCE_DESC
, PTERM.TERM_COMMENTS TERM_COMMENTS
, PTERM.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
, PCLOOK.MEANING TCC_PAYMENT
, PCLOOK.MEANING TCC_BILLING
, PTERM.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, PILEASES.INDEX_LEASE_ID INDEX_LEASE_ID
, (SELECT DECODE(INCLUDE_EXCLUDE_FLAG
, 'I'
, 'Y'
, 'E'
, 'N')
FROM PN_INDEX_EXCLUDE_TERM_ALL ETERM
WHERE ETERM.PAYMENT_TERM_ID = PTERM.PAYMENT_TERM_ID
AND ETERM.INDEX_LEASE_ID = PILEASES.INDEX_LEASE_ID UNION ALL SELECT CASE WHEN INCL_TERMS_BY_DEFAULT_FLAG = 'Y' OR (INCL_TERMS_BY_DEFAULT_FLAG = 'G'
AND NVL(PILEASES.GROSS_FLAG
, 'N') = 'Y' ) OR (INCL_TERMS_BY_DEFAULT_FLAG = 'U'
AND NVL(PILEASES.GROSS_FLAG
, 'N') = 'N' ) THEN 'Y' ELSE 'N' END
FROM PN_SYSTEM_SETUP_OPTIONS
WHERE ORG_ID = PTERM.ORG_ID
AND NOT EXISTS(SELECT NULL
FROM PN_INDEX_EXCLUDE_TERM_ALL ETERM
WHERE ETERM.PAYMENT_TERM_ID = PTERM.PAYMENT_TERM_ID
AND ETERM.INDEX_LEASE_ID = PILEASES.INDEX_LEASE_ID) ) EXCLUDE_FLAG
, (SELECT INDEX_EXCLUDE_TERM_ID
FROM PN_INDEX_EXCLUDE_TERM_ALL
WHERE PAYMENT_TERM_ID = PTERM.PAYMENT_TERM_ID
AND INDEX_LEASE_ID = PILEASES.INDEX_LEASE_ID) INDEX_EXCLUDE_TERM_ID
, PTERM.INCLUDE_IN_VAR_RENT INCLUDE_IN_VAR_RENT
FROM HZ_CUST_SITE_USES_ALL HZCB
, HZ_CUST_SITE_USES_ALL HZCS
, AR_CUSTOMERS ARC
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, FND_LOOKUPS PTLOOK
, FND_LOOKUPS PPLOOK
, FND_LOOKUPS PSLOOK
, FND_LOOKUPS PCLOOK
, PN_PAY_GROUP_RULES PPGR
, PN_PAYMENT_TERMS PTERM
, PN_TERM_TEMPLATES_ALL PTEMP
, PN_LEASES_ALL PLEASES
, PN_INDEX_LEASES_ALL PILEASES
WHERE PTERM.LEASE_ID = PLEASES.LEASE_ID
AND PLEASES.LEASE_CLASS_CODE IN ('DIRECT'
, 'PAY')
AND PCLOOK.LOOKUP_TYPE(+) = 'ZX_INPUT_CLASSIFICATIONS'
AND PCLOOK.LOOKUP_CODE(+) = PTERM.TAX_CLASSIFICATION_CODE
AND 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 PSLOOK.LOOKUP_TYPE (+) = 'PN_TERM_SOURCE_TYPE'
AND PSLOOK.LOOKUP_CODE (+) = PTERM.SOURCE_CODE
AND POV.VENDOR_ID (+) = PTERM.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = PTERM.VENDOR_SITE_ID
AND ARC.CUSTOMER_ID (+) = PTERM.CUSTOMER_ID
AND HZCB.SITE_USE_ID (+) = PTERM.CUSTOMER_SITE_USE_ID
AND HZCS.SITE_USE_ID (+) = PTERM.CUST_SHIP_SITE_ID
AND PPGR.GROUPING_RULE_ID (+) = PTERM.GROUPING_RULE_ID
AND PTERM.TERM_TEMPLATE_ID = PTEMP.TERM_TEMPLATE_ID(+)
AND PTERM.LEASE_ID = PILEASES.LEASE_ID
AND PTERM.FREQUENCY_CODE <> 'OT' UNION ALL SELECT PTERM.ROWID ROW_ID
, PTERM.PAYMENT_TERM_ID PAYMENT_TERM_ID
, 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
, PTERM.PAYMENT_PURPOSE_CODE PAYMENT_PURPOSE_CODE
, PTERM.PAYMENT_TERM_TYPE_CODE PAYMENT_TERM_TYPE_CODE
, PTERM.FREQUENCY_CODE FREQUENCY_CODE
, PTERM.NORMALIZE NORMALIZE
, PTERM.LEASE_ID LEASE_ID
, PTERM.LEASE_CHANGE_ID LEASE_CHANGE_ID
, PTERM.START_DATE START_DATE
, PTERM.END_DATE END_DATE
, PTERM.VENDOR_ID VENDOR_ID
, PTERM.VENDOR_SITE_ID VENDOR_SITE_ID
, PTERM.CUSTOMER_ID CUSTOMER_ID
, PTERM.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, PTERM.TARGET_DATE TARGET_DATE
, PTERM.ACTUAL_AMOUNT ACTUAL_AMOUNT
, PTERM.ESTIMATED_AMOUNT ESTIMATED_AMOUNT
, PTERM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PTERM.CURRENCY_CODE CURRENCY_CODE
, PTERM.RATE RATE
, PTERM.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PTERM.ATTRIBUTE1 ATTRIBUTE1
, PTERM.ATTRIBUTE2 ATTRIBUTE2
, PTERM.ATTRIBUTE3 ATTRIBUTE3
, PTERM.ATTRIBUTE4 ATTRIBUTE4
, PTERM.ATTRIBUTE5 ATTRIBUTE5
, PTERM.ATTRIBUTE6 ATTRIBUTE6
, PTERM.ATTRIBUTE7 ATTRIBUTE7
, PTERM.ATTRIBUTE8 ATTRIBUTE8
, PTERM.ATTRIBUTE9 ATTRIBUTE9
, PTERM.ATTRIBUTE10 ATTRIBUTE10
, PTERM.ATTRIBUTE11 ATTRIBUTE11
, PTERM.ATTRIBUTE12 ATTRIBUTE12
, PTERM.ATTRIBUTE13 ATTRIBUTE13
, PTERM.ATTRIBUTE14 ATTRIBUTE14
, PTERM.ATTRIBUTE15 ATTRIBUTE15
, PTERM.LOCATION_ID LOCATION_ID
, PTERM.SCHEDULE_DAY SCHEDULE_DAY
, PTERM.CUST_SHIP_SITE_ID CUST_SHIP_SITE_ID
, PTERM.AP_AR_TERM_ID AP_AR_TERM_ID
, PTERM.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PTERM.PROJECT_ID PROJECT_ID
, PTERM.TASK_ID TASK_ID
, PTERM.ORGANIZATION_ID ORGANIZATION_ID
, PTERM.EXPENDITURE_TYPE EXPENDITURE_TYPE
, PTERM.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, PTERM.TAX_GROUP_ID TAX_GROUP_ID
, PTERM.TAX_CODE_ID TAX_CODE_ID
, PTERM.TAX_INCLUDED TAX_INCLUDED
, PTERM.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, PTERM.INV_RULE_ID INV_RULE_ID
, PTERM.ACCOUNT_RULE_ID ACCOUNT_RULE_ID
, PTERM.SALESREP_ID SALESREP_ID
, PTERM.APPROVED_BY APPROVED_BY
, PTERM.STATUS STATUS
, PTERM.INDEX_PERIOD_ID INDEX_PERIOD_ID
, PTERM.INDEX_TERM_INDICATOR INDEX_TERM_INDICATOR
, PTERM.PO_HEADER_ID PO_HEADER_ID
, PTERM.CUST_PO_NUMBER CUST_PO_NUMBER
, PTERM.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, PTERM.VAR_RENT_INV_ID VAR_RENT_INV_ID
, PTERM.VAR_RENT_TYPE VAR_RENT_TYPE
, PTERM.CHANGED_FLAG CHANGED_FLAG
, PTERM.RECOVERABLE_FLAG RECOVERABLE_FLAG
, PTERM.PERIOD_BILLREC_ID PERIOD_BILLREC_ID
, PTERM.REC_AGR_LINE_ID REC_AGR_LINE_ID
, PTERM.AMOUNT_TYPE AMOUNT_TYPE
, PTERM.NORM_START_DATE NORM_START_DATE
, PTERM.PROJECT_ATTRIBUTE_CATEGORY INTERFACE_LINE_CONTEXT
, PTERM.PROJECT_ATTRIBUTE1 INTERFACE_LINE_ATTRIBUTE1
, PTERM.PROJECT_ATTRIBUTE2 INTERFACE_LINE_ATTRIBUTE2
, PTERM.PROJECT_ATTRIBUTE3 INTERFACE_LINE_ATTRIBUTE3
, PTERM.PROJECT_ATTRIBUTE4 INTERFACE_LINE_ATTRIBUTE4
, PTERM.PROJECT_ATTRIBUTE5 INTERFACE_LINE_ATTRIBUTE5
, PTERM.PROJECT_ATTRIBUTE6 INTERFACE_LINE_ATTRIBUTE6
, PTERM.PROJECT_ATTRIBUTE7 INTERFACE_LINE_ATTRIBUTE7
, PTERM.PROJECT_ATTRIBUTE8 INTERFACE_LINE_ATTRIBUTE8
, PTERM.PROJECT_ATTRIBUTE9 INTERFACE_LINE_ATTRIBUTE9
, PTERM.PROJECT_ATTRIBUTE10 INTERFACE_LINE_ATTRIBUTE10
, PTERM.PROJECT_ATTRIBUTE11 INTERFACE_LINE_ATTRIBUTE11
, PTERM.PROJECT_ATTRIBUTE12 INTERFACE_LINE_ATTRIBUTE12
, PTERM.PROJECT_ATTRIBUTE13 INTERFACE_LINE_ATTRIBUTE13
, PTERM.PROJECT_ATTRIBUTE14 INTERFACE_LINE_ATTRIBUTE14
, PTERM.PROJECT_ATTRIBUTE15 INTERFACE_LINE_ATTRIBUTE15
, PTERM.TERM_TEMPLATE_ID TERM_TEMPLATE_ID
, PPLOOK.MEANING PAYMENT_PURPOSE
, PTLOOK.MEANING PAYMENT_TERM_TYPE
, POV.VENDOR_NAME VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, POVS.AUTO_TAX_CALC_FLAG TAX_FLAG
, ARC.CUSTOMER_NAME CUSTOMER_NAME
, ARC.CUSTOMER_NUMBER CUSTOMER_NUMBER
, HZCB.LOCATION CUSTOMER_SITE_USE
, HZCS.LOCATION CUST_SHIP_SITE_USE
, PNP_UTIL_FUNC.GET_TOTAL_PAYMENT_TERM_AMT(PTERM.PAYMENT_TERM_ID) TOTAL_PAYMENT
, PNP_UTIL_FUNC.GET_TOTAL_LEASED_AREA(PTERM.LEASE_ID
, PTERM.START_DATE) LEASED_AREA
, PTERM.AREA_TYPE_CODE AREA_TYPE_CODE
, PTERM.AREA AREA
, PTERM.GROUPING_RULE_ID GROUPING_RULE_ID
, PPGR.NAME INV_GROUP_NAME
, PTERM.ORG_ID ORG_ID
, PTEMP.NAME TERM_TEMPLATE
, PTERM.TERM_ALTERED_FLAG TERM_ALTERED_FLAG
, PTERM.SOURCE_CODE SOURCE_CODE
, PSLOOK.MEANING SOURCE_DESC
, PTERM.TERM_COMMENTS TERM_COMMENTS
, PTERM.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
, PCLOOK.MEANING TCC_PAYMENT
, PCLOOK.MEANING TCC_BILLING
, PTERM.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, PILEASES.INDEX_LEASE_ID INDEX_LEASE_ID
, (SELECT DECODE(INCLUDE_EXCLUDE_FLAG
, 'I'
, 'Y'
, 'E'
, 'N')
FROM PN_INDEX_EXCLUDE_TERM_ALL ETERM
WHERE ETERM.PAYMENT_TERM_ID = PTERM.PAYMENT_TERM_ID
AND ETERM.INDEX_LEASE_ID = PILEASES.INDEX_LEASE_ID UNION ALL SELECT CASE WHEN INCL_TERMS_BY_DEFAULT_FLAG = 'Y' OR (INCL_TERMS_BY_DEFAULT_FLAG = 'G'
AND NVL(PILEASES.GROSS_FLAG
, 'N') = 'Y') OR (INCL_TERMS_BY_DEFAULT_FLAG = 'U'
AND NVL(PILEASES.GROSS_FLAG
, 'N') = 'N') THEN 'Y' ELSE 'N' END
FROM PN_SYSTEM_SETUP_OPTIONS
WHERE ORG_ID = PTERM.ORG_ID
AND NOT EXISTS(SELECT NULL
FROM PN_INDEX_EXCLUDE_TERM_ALL ETERM
WHERE ETERM.PAYMENT_TERM_ID = PTERM.PAYMENT_TERM_ID
AND ETERM.INDEX_LEASE_ID = PILEASES.INDEX_LEASE_ID) ) EXCLUDE_FLAG
, (SELECT INDEX_EXCLUDE_TERM_ID
FROM PN_INDEX_EXCLUDE_TERM_ALL
WHERE PAYMENT_TERM_ID = PTERM.PAYMENT_TERM_ID
AND INDEX_LEASE_ID = PILEASES.INDEX_LEASE_ID) INDEX_EXCLUDE_TERM_ID
, PTERM.INCLUDE_IN_VAR_RENT INCLUDE_IN_VAR_RENT
FROM HZ_CUST_SITE_USES_ALL HZCB
, HZ_CUST_SITE_USES_ALL HZCS
, AR_CUSTOMERS ARC
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, FND_LOOKUPS PTLOOK
, FND_LOOKUPS PPLOOK
, FND_LOOKUPS PSLOOK
, FND_LOOKUPS PCLOOK
, PN_PAY_GROUP_RULES PPGR
, PN_PAYMENT_TERMS PTERM
, PN_TERM_TEMPLATES_ALL PTEMP
, PN_LEASES_ALL PLEASES
, PN_INDEX_LEASES_ALL PILEASES
WHERE PTERM.LEASE_ID = PLEASES.LEASE_ID
AND PLEASES.LEASE_CLASS_CODE IN ('THIRD_PARTY'
, 'SUB_LEASE'
, 'REC')
AND PCLOOK.LOOKUP_TYPE(+) = 'ZX_OUTPUT_CLASSIFICATIONS'
AND PCLOOK.LOOKUP_CODE(+) = PTERM.TAX_CLASSIFICATION_CODE
AND 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 PSLOOK.LOOKUP_TYPE (+) = 'PN_TERM_SOURCE_TYPE'
AND PSLOOK.LOOKUP_CODE (+) = PTERM.SOURCE_CODE
AND POV.VENDOR_ID (+) = PTERM.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = PTERM.VENDOR_SITE_ID
AND ARC.CUSTOMER_ID (+) = PTERM.CUSTOMER_ID
AND HZCB.SITE_USE_ID (+) = PTERM.CUSTOMER_SITE_USE_ID
AND HZCS.SITE_USE_ID (+) = PTERM.CUST_SHIP_SITE_ID
AND PPGR.GROUPING_RULE_ID (+) = PTERM.GROUPING_RULE_ID
AND PTERM.TERM_TEMPLATE_ID = PTEMP.TERM_TEMPLATE_ID(+)
AND PTERM.LEASE_ID = PILEASES.LEASE_ID
AND PTERM.FREQUENCY_CODE <> 'OT' ORDER BY PAYMENT_TERM_TYPE
, START_DATE
, PAYMENT_PURPOSE