VIEW: APPS.PN_TERM_TEMPLATES_V

Source

View Text - Preformatted

SELECT DISTINCT term.ROWID ROW_ID, term.TERM_TEMPLATE_ID TERM_TEMPLATE_ID, term.NAME NAME, term.TERM_TEMPLATE_TYPE TERM_TEMPLATE_TYPE, term.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, term.CURRENCY_CODE CURRENCY_CODE, term.NORMALIZE NORMALIZE, term.SCHEDULE_DAY SCHEDULE_DAY, term.PAYMENT_PURPOSE_CODE PAYMENT_PURPOSE_CODE, term.PAYMENT_TERM_TYPE_CODE PAYMENT_TERM_TYPE_CODE, term.ACCRUAL_ACCOUNT_ID ACCRUAL_ACCOUNT_ID, term.PROJECT_ID PROJECT_ID, term.TASK_ID TASK_ID, term.ORGANIZATION_ID ORGANIZATION_ID, term.EXPENDITURE_TYPE EXPENDITURE_TYPE, term.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE, term.VENDOR_ID VENDOR_ID, term.VENDOR_SITE_ID VENDOR_SITE_ID, term.CUSTOMER_ID CUSTOMER_ID, term.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, term.CUST_SHIP_SITE_ID CUST_SHIP_SITE_ID, term.AP_AR_TERM_ID AP_AR_TERM_ID, term.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID, term.TAX_GROUP_ID TAX_GROUP_ID, term.TAX_CODE_ID TAX_CODE_ID, term.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID, term.INV_RULE_ID INV_RULE_ID, term.ACCOUNT_RULE_ID ACCOUNT_RULE_ID, term.SALESREP_ID SALESREP_ID, DECODE(term.TAX_INCLUDED,'Y','A' ,term.TAX_INCLUDED), term.PO_HEADER_ID PO_HEADER_ID, term.CUST_PO_NUMBER CUST_PO_NUMBER, term.RECEIPT_METHOD_ID RECEIPT_METHOD_ID, term.LOCATION_ID LOCATION_ID, term.ACTIVE ACTIVE, term.DESCRIPTION DESCRIPTION, term.LAST_UPDATE_DATE LAST_UPDATE_DATE, term.LAST_UPDATED_BY LAST_UPDATED_BY, term.CREATION_DATE CREATION_DATE, term.CREATED_BY CREATED_BY, term.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, term.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, term.ATTRIBUTE1 ATTRIBUTE1, term.ATTRIBUTE2 ATTRIBUTE2, term.ATTRIBUTE3 ATTRIBUTE3, term.ATTRIBUTE4 ATTRIBUTE4, term.ATTRIBUTE5 ATTRIBUTE5, term.ATTRIBUTE6 ATTRIBUTE6, term.ATTRIBUTE7 ATTRIBUTE7, term.ATTRIBUTE8 ATTRIBUTE8, term.ATTRIBUTE9 ATTRIBUTE9, term.ATTRIBUTE10 ATTRIBUTE10, term.ATTRIBUTE11 ATTRIBUTE11, term.ATTRIBUTE12 ATTRIBUTE12, term.ATTRIBUTE13 ATTRIBUTE13, term.ATTRIBUTE14 ATTRIBUTE14, term.ATTRIBUTE15 ATTRIBUTE15, pplook.meaning payment_purpose, ptlook.meaning payment_term_type, pov.vendor_name vendor_name, pov.segment1 vendor_number, povs.vendor_site_code vendor_site, hzp.party_name customer_name, hca.account_number customer_number, hcsu_bill.location customer_site_use, hcsu_ship.location cust_ship_site_use, NULL AS tax_flag, plv.property_name property_name, plv.location_code location_code, term.org_id org_id, term.tax_classification_code tax_classification_code, pclook.meaning tcc_payment, pclook.meaning tcc_billing FROM hz_cust_site_uses_all hcsu_bill, hz_cust_site_uses_all hcsu_ship, hz_parties hzp, hz_cust_accounts hca, po_vendors pov, po_vendor_sites_all povs, fnd_lookups ptlook, fnd_lookups pplook, fnd_lookups pclook, pn_locations_v plv, pn_term_templates term WHERE term.term_template_type = 'PAYMENT' AND pclook.lookup_type(+) = 'ZX_INPUT_CLASSIFICATIONS' AND pclook.lookup_code(+) = term.tax_classification_code AND pplook.lookup_type (+) = 'PN_PAYMENT_PURPOSE_TYPE' AND pplook.lookup_code (+) = term.payment_purpose_code AND ptlook.lookup_type (+) = 'PN_PAYMENT_TERM_TYPE' AND ptlook.lookup_code (+) = term.payment_term_type_code AND pov.vendor_id (+) = term.vendor_id AND povs.vendor_site_id(+) = term.vendor_site_id AND hca.cust_account_id(+) =term.customer_id AND hzp.party_id (+) = hca.party_id AND hcsu_bill.site_use_id (+) = term.customer_site_use_id AND hcsu_ship.site_use_id (+) = term.cust_ship_site_id AND plv.location_id (+) = term.location_id UNION ALL SELECT DISTINCT term.ROWID ROW_ID, term.TERM_TEMPLATE_ID TERM_TEMPLATE_ID, term.NAME NAME, term.TERM_TEMPLATE_TYPE TERM_TEMPLATE_TYPE, term.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, term.CURRENCY_CODE CURRENCY_CODE, term.NORMALIZE NORMALIZE, term.SCHEDULE_DAY SCHEDULE_DAY, term.PAYMENT_PURPOSE_CODE PAYMENT_PURPOSE_CODE, term.PAYMENT_TERM_TYPE_CODE PAYMENT_TERM_TYPE_CODE, term.ACCRUAL_ACCOUNT_ID ACCRUAL_ACCOUNT_ID, term.PROJECT_ID PROJECT_ID, term.TASK_ID TASK_ID, term.ORGANIZATION_ID ORGANIZATION_ID, term.EXPENDITURE_TYPE EXPENDITURE_TYPE, term.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE, term.VENDOR_ID VENDOR_ID, term.VENDOR_SITE_ID VENDOR_SITE_ID, term.CUSTOMER_ID CUSTOMER_ID, term.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, term.CUST_SHIP_SITE_ID CUST_SHIP_SITE_ID, term.AP_AR_TERM_ID AP_AR_TERM_ID, term.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID, term.TAX_GROUP_ID TAX_GROUP_ID, term.TAX_CODE_ID TAX_CODE_ID, term.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID, term.INV_RULE_ID INV_RULE_ID, term.ACCOUNT_RULE_ID ACCOUNT_RULE_ID, term.SALESREP_ID SALESREP_ID, term.TAX_INCLUDED TAX_INCLUDED, term.PO_HEADER_ID PO_HEADER_ID, term.CUST_PO_NUMBER CUST_PO_NUMBER, term.RECEIPT_METHOD_ID RECEIPT_METHOD_ID, term.LOCATION_ID LOCATION_ID, term.ACTIVE ACTIVE, term.DESCRIPTION DESCRIPTION, term.LAST_UPDATE_DATE LAST_UPDATE_DATE, term.LAST_UPDATED_BY LAST_UPDATED_BY, term.CREATION_DATE CREATION_DATE, term.CREATED_BY CREATED_BY, term.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, term.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, term.ATTRIBUTE1 ATTRIBUTE1, term.ATTRIBUTE2 ATTRIBUTE2, term.ATTRIBUTE3 ATTRIBUTE3, term.ATTRIBUTE4 ATTRIBUTE4, term.ATTRIBUTE5 ATTRIBUTE5, term.ATTRIBUTE6 ATTRIBUTE6, term.ATTRIBUTE7 ATTRIBUTE7, term.ATTRIBUTE8 ATTRIBUTE8, term.ATTRIBUTE9 ATTRIBUTE9, term.ATTRIBUTE10 ATTRIBUTE10, term.ATTRIBUTE11 ATTRIBUTE11, term.ATTRIBUTE12 ATTRIBUTE12, term.ATTRIBUTE13 ATTRIBUTE13, term.ATTRIBUTE14 ATTRIBUTE14, term.ATTRIBUTE15 ATTRIBUTE15, pplook.meaning payment_purpose, ptlook.meaning payment_term_type, pov.vendor_name vendor_name, pov.segment1 vendor_number, povs.vendor_site_code vendor_site, hzp.party_name customer_name, hca.account_number customer_number, hcsu_bill.location customer_site_use, hcsu_ship.location cust_ship_site_use, NULL AS tax_flag, plv.property_name property_name, plv.location_code location_code, term.org_id org_id, term.tax_classification_code tax_classification_code, pclook.meaning tcc_payment, pclook.meaning tcc_billing FROM hz_cust_site_uses_all hcsu_bill, hz_cust_site_uses_all hcsu_ship, hz_parties hzp, hz_cust_accounts hca, po_vendors pov, po_vendor_sites_all povs, fnd_lookups ptlook, fnd_lookups pplook, fnd_lookups pclook, pn_locations_v plv, pn_term_templates term WHERE term.term_template_type = 'BILLING' AND pclook.lookup_type(+) = 'ZX_OUTPUT_CLASSIFICATIONS' AND pclook.lookup_code(+) = term.tax_classification_code AND pplook.lookup_type (+) = 'PN_PAYMENT_PURPOSE_TYPE' AND pplook.lookup_code (+) = term.payment_purpose_code AND ptlook.lookup_type (+) = 'PN_PAYMENT_TERM_TYPE' AND ptlook.lookup_code (+) = term.payment_term_type_code AND pov.vendor_id (+) = term.vendor_id AND povs.vendor_site_id(+) = term.vendor_site_id AND hca.cust_account_id(+) =term.customer_id AND hzp.party_id (+) = hca.party_id AND hcsu_bill.site_use_id (+) = term.customer_site_use_id AND hcsu_ship.site_use_id (+) = term.cust_ship_site_id AND plv.location_id (+) = term.location_id

View Text - HTML Formatted

SELECT DISTINCT TERM.ROWID ROW_ID
, TERM.TERM_TEMPLATE_ID TERM_TEMPLATE_ID
, TERM.NAME NAME
, TERM.TERM_TEMPLATE_TYPE TERM_TEMPLATE_TYPE
, TERM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, TERM.CURRENCY_CODE CURRENCY_CODE
, TERM.NORMALIZE NORMALIZE
, TERM.SCHEDULE_DAY SCHEDULE_DAY
, TERM.PAYMENT_PURPOSE_CODE PAYMENT_PURPOSE_CODE
, TERM.PAYMENT_TERM_TYPE_CODE PAYMENT_TERM_TYPE_CODE
, TERM.ACCRUAL_ACCOUNT_ID ACCRUAL_ACCOUNT_ID
, TERM.PROJECT_ID PROJECT_ID
, TERM.TASK_ID TASK_ID
, TERM.ORGANIZATION_ID ORGANIZATION_ID
, TERM.EXPENDITURE_TYPE EXPENDITURE_TYPE
, TERM.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, TERM.VENDOR_ID VENDOR_ID
, TERM.VENDOR_SITE_ID VENDOR_SITE_ID
, TERM.CUSTOMER_ID CUSTOMER_ID
, TERM.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, TERM.CUST_SHIP_SITE_ID CUST_SHIP_SITE_ID
, TERM.AP_AR_TERM_ID AP_AR_TERM_ID
, TERM.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, TERM.TAX_GROUP_ID TAX_GROUP_ID
, TERM.TAX_CODE_ID TAX_CODE_ID
, TERM.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, TERM.INV_RULE_ID INV_RULE_ID
, TERM.ACCOUNT_RULE_ID ACCOUNT_RULE_ID
, TERM.SALESREP_ID SALESREP_ID
, DECODE(TERM.TAX_INCLUDED
, 'Y'
, 'A'
, TERM.TAX_INCLUDED)
, TERM.PO_HEADER_ID PO_HEADER_ID
, TERM.CUST_PO_NUMBER CUST_PO_NUMBER
, TERM.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, TERM.LOCATION_ID LOCATION_ID
, TERM.ACTIVE ACTIVE
, TERM.DESCRIPTION DESCRIPTION
, TERM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, TERM.LAST_UPDATED_BY LAST_UPDATED_BY
, TERM.CREATION_DATE CREATION_DATE
, TERM.CREATED_BY CREATED_BY
, TERM.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, TERM.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, TERM.ATTRIBUTE1 ATTRIBUTE1
, TERM.ATTRIBUTE2 ATTRIBUTE2
, TERM.ATTRIBUTE3 ATTRIBUTE3
, TERM.ATTRIBUTE4 ATTRIBUTE4
, TERM.ATTRIBUTE5 ATTRIBUTE5
, TERM.ATTRIBUTE6 ATTRIBUTE6
, TERM.ATTRIBUTE7 ATTRIBUTE7
, TERM.ATTRIBUTE8 ATTRIBUTE8
, TERM.ATTRIBUTE9 ATTRIBUTE9
, TERM.ATTRIBUTE10 ATTRIBUTE10
, TERM.ATTRIBUTE11 ATTRIBUTE11
, TERM.ATTRIBUTE12 ATTRIBUTE12
, TERM.ATTRIBUTE13 ATTRIBUTE13
, TERM.ATTRIBUTE14 ATTRIBUTE14
, TERM.ATTRIBUTE15 ATTRIBUTE15
, PPLOOK.MEANING PAYMENT_PURPOSE
, PTLOOK.MEANING PAYMENT_TERM_TYPE
, POV.VENDOR_NAME VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, HZP.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HCSU_BILL.LOCATION CUSTOMER_SITE_USE
, HCSU_SHIP.LOCATION CUST_SHIP_SITE_USE
, NULL AS TAX_FLAG
, PLV.PROPERTY_NAME PROPERTY_NAME
, PLV.LOCATION_CODE LOCATION_CODE
, TERM.ORG_ID ORG_ID
, TERM.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
, PCLOOK.MEANING TCC_PAYMENT
, PCLOOK.MEANING TCC_BILLING
FROM HZ_CUST_SITE_USES_ALL HCSU_BILL
, HZ_CUST_SITE_USES_ALL HCSU_SHIP
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HCA
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, FND_LOOKUPS PTLOOK
, FND_LOOKUPS PPLOOK
, FND_LOOKUPS PCLOOK
, PN_LOCATIONS_V PLV
, PN_TERM_TEMPLATES TERM
WHERE TERM.TERM_TEMPLATE_TYPE = 'PAYMENT'
AND PCLOOK.LOOKUP_TYPE(+) = 'ZX_INPUT_CLASSIFICATIONS'
AND PCLOOK.LOOKUP_CODE(+) = TERM.TAX_CLASSIFICATION_CODE
AND PPLOOK.LOOKUP_TYPE (+) = 'PN_PAYMENT_PURPOSE_TYPE'
AND PPLOOK.LOOKUP_CODE (+) = TERM.PAYMENT_PURPOSE_CODE
AND PTLOOK.LOOKUP_TYPE (+) = 'PN_PAYMENT_TERM_TYPE'
AND PTLOOK.LOOKUP_CODE (+) = TERM.PAYMENT_TERM_TYPE_CODE
AND POV.VENDOR_ID (+) = TERM.VENDOR_ID
AND POVS.VENDOR_SITE_ID(+) = TERM.VENDOR_SITE_ID
AND HCA.CUST_ACCOUNT_ID(+) =TERM.CUSTOMER_ID
AND HZP.PARTY_ID (+) = HCA.PARTY_ID
AND HCSU_BILL.SITE_USE_ID (+) = TERM.CUSTOMER_SITE_USE_ID
AND HCSU_SHIP.SITE_USE_ID (+) = TERM.CUST_SHIP_SITE_ID
AND PLV.LOCATION_ID (+) = TERM.LOCATION_ID UNION ALL SELECT DISTINCT TERM.ROWID ROW_ID
, TERM.TERM_TEMPLATE_ID TERM_TEMPLATE_ID
, TERM.NAME NAME
, TERM.TERM_TEMPLATE_TYPE TERM_TEMPLATE_TYPE
, TERM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, TERM.CURRENCY_CODE CURRENCY_CODE
, TERM.NORMALIZE NORMALIZE
, TERM.SCHEDULE_DAY SCHEDULE_DAY
, TERM.PAYMENT_PURPOSE_CODE PAYMENT_PURPOSE_CODE
, TERM.PAYMENT_TERM_TYPE_CODE PAYMENT_TERM_TYPE_CODE
, TERM.ACCRUAL_ACCOUNT_ID ACCRUAL_ACCOUNT_ID
, TERM.PROJECT_ID PROJECT_ID
, TERM.TASK_ID TASK_ID
, TERM.ORGANIZATION_ID ORGANIZATION_ID
, TERM.EXPENDITURE_TYPE EXPENDITURE_TYPE
, TERM.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, TERM.VENDOR_ID VENDOR_ID
, TERM.VENDOR_SITE_ID VENDOR_SITE_ID
, TERM.CUSTOMER_ID CUSTOMER_ID
, TERM.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, TERM.CUST_SHIP_SITE_ID CUST_SHIP_SITE_ID
, TERM.AP_AR_TERM_ID AP_AR_TERM_ID
, TERM.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, TERM.TAX_GROUP_ID TAX_GROUP_ID
, TERM.TAX_CODE_ID TAX_CODE_ID
, TERM.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, TERM.INV_RULE_ID INV_RULE_ID
, TERM.ACCOUNT_RULE_ID ACCOUNT_RULE_ID
, TERM.SALESREP_ID SALESREP_ID
, TERM.TAX_INCLUDED TAX_INCLUDED
, TERM.PO_HEADER_ID PO_HEADER_ID
, TERM.CUST_PO_NUMBER CUST_PO_NUMBER
, TERM.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, TERM.LOCATION_ID LOCATION_ID
, TERM.ACTIVE ACTIVE
, TERM.DESCRIPTION DESCRIPTION
, TERM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, TERM.LAST_UPDATED_BY LAST_UPDATED_BY
, TERM.CREATION_DATE CREATION_DATE
, TERM.CREATED_BY CREATED_BY
, TERM.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, TERM.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, TERM.ATTRIBUTE1 ATTRIBUTE1
, TERM.ATTRIBUTE2 ATTRIBUTE2
, TERM.ATTRIBUTE3 ATTRIBUTE3
, TERM.ATTRIBUTE4 ATTRIBUTE4
, TERM.ATTRIBUTE5 ATTRIBUTE5
, TERM.ATTRIBUTE6 ATTRIBUTE6
, TERM.ATTRIBUTE7 ATTRIBUTE7
, TERM.ATTRIBUTE8 ATTRIBUTE8
, TERM.ATTRIBUTE9 ATTRIBUTE9
, TERM.ATTRIBUTE10 ATTRIBUTE10
, TERM.ATTRIBUTE11 ATTRIBUTE11
, TERM.ATTRIBUTE12 ATTRIBUTE12
, TERM.ATTRIBUTE13 ATTRIBUTE13
, TERM.ATTRIBUTE14 ATTRIBUTE14
, TERM.ATTRIBUTE15 ATTRIBUTE15
, PPLOOK.MEANING PAYMENT_PURPOSE
, PTLOOK.MEANING PAYMENT_TERM_TYPE
, POV.VENDOR_NAME VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, HZP.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HCSU_BILL.LOCATION CUSTOMER_SITE_USE
, HCSU_SHIP.LOCATION CUST_SHIP_SITE_USE
, NULL AS TAX_FLAG
, PLV.PROPERTY_NAME PROPERTY_NAME
, PLV.LOCATION_CODE LOCATION_CODE
, TERM.ORG_ID ORG_ID
, TERM.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
, PCLOOK.MEANING TCC_PAYMENT
, PCLOOK.MEANING TCC_BILLING
FROM HZ_CUST_SITE_USES_ALL HCSU_BILL
, HZ_CUST_SITE_USES_ALL HCSU_SHIP
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HCA
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, FND_LOOKUPS PTLOOK
, FND_LOOKUPS PPLOOK
, FND_LOOKUPS PCLOOK
, PN_LOCATIONS_V PLV
, PN_TERM_TEMPLATES TERM
WHERE TERM.TERM_TEMPLATE_TYPE = 'BILLING'
AND PCLOOK.LOOKUP_TYPE(+) = 'ZX_OUTPUT_CLASSIFICATIONS'
AND PCLOOK.LOOKUP_CODE(+) = TERM.TAX_CLASSIFICATION_CODE
AND PPLOOK.LOOKUP_TYPE (+) = 'PN_PAYMENT_PURPOSE_TYPE'
AND PPLOOK.LOOKUP_CODE (+) = TERM.PAYMENT_PURPOSE_CODE
AND PTLOOK.LOOKUP_TYPE (+) = 'PN_PAYMENT_TERM_TYPE'
AND PTLOOK.LOOKUP_CODE (+) = TERM.PAYMENT_TERM_TYPE_CODE
AND POV.VENDOR_ID (+) = TERM.VENDOR_ID
AND POVS.VENDOR_SITE_ID(+) = TERM.VENDOR_SITE_ID
AND HCA.CUST_ACCOUNT_ID(+) =TERM.CUSTOMER_ID
AND HZP.PARTY_ID (+) = HCA.PARTY_ID
AND HCSU_BILL.SITE_USE_ID (+) = TERM.CUSTOMER_SITE_USE_ID
AND HCSU_SHIP.SITE_USE_ID (+) = TERM.CUST_SHIP_SITE_ID
AND PLV.LOCATION_ID (+) = TERM.LOCATION_ID