View: PN_PAYMENT_ITEMS_V

Product:	PN - Property Manager
Description:
Implementation/DBA Data:	APPS.PN_PAYMENT_ITEMS_V

View Text

SELECT PITEM.ROWID ROW_ID
, PITEM.PAYMENT_ITEM_ID PAYMENT_ITEM_ID
, PITEM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PITEM.LAST_UPDATED_BY LAST_UPDATED_BY
, PITEM.CREATION_DATE CREATION_DATE
, PITEM.CREATED_BY CREATED_BY
, PITEM.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PITEM.DUE_DATE DUE_DATE
, PITEM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PITEM.CURRENCY_CODE CURRENCY_CODE
, PITEM.RATE RATE
, PITEM.PAYMENT_ITEM_TYPE_LOOKUP_CODE PAYMENT_ITEM_TYPE_LOOKUP_CODE
, PITEM.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PITEM.PAYMENT_TERM_ID PAYMENT_TERM_ID
, PITEM.VENDOR_ID VENDOR_ID
, PITEM.VENDOR_SITE_ID VENDOR_SITE_ID
, PITEM.CUSTOMER_ID CUSTOMER_ID
, PITEM.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, PITEM.ACTUAL_AMOUNT ACTUAL_AMOUNT
, PITEM.ESTIMATED_AMOUNT ESTIMATED_AMOUNT
, PITEM.ACCOUNTED_AMOUNT ACCOUNTED_AMOUNT
, PITEM.ACCOUNTED_DATE ACCOUNTED_DATE
, PITEM.PERIOD_FRACTION PERIOD_FRACTION
, PITEM.DESCRIPTION DESCRIPTION
, PITEM.COMMENTS COMMENTS
, PITEM.TRANSFERRED_TO_AP_FLAG TRANSFERRED_TO_AP_FLAG
, PITEM.AP_INVOICE_NUM AP_INVOICE_NUM
, PITEM.ENTERED_BY_LEASE ENTERED_BY_LEASE
, PITEM.CUST_SHIP_SITE_ID CUST_SHIP_SITE_ID
, HZCSS.LOCATION CUST_SHIP_SITE_USE
, FLV.MEANING PAYMENT_PURPOSE
, FLV1.MEANING PAYMENT_TERM_TYPE
, DECODE(PTERM.FREQUENCY_CODE
, 'OT'
, 'Y'
, 'N') ONE_TIME
, PTERM.CODE_COMBINATION_ID CODE_COMBINATION_ID
, PTERM.NORMALIZE TERM_NORMALIZE_FLAG
, PTERM.ACTUAL_AMOUNT TERM_ACTUAL_AMOUNT
, PTERM.ESTIMATED_AMOUNT TERM_ESTIMATED_AMOUNT
, POV.VENDOR_NAME VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, HZC.ACCOUNT_NUMBER CUSTOMER_NUMBER
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, NULL AS TAX_FLAG
, HZP.PARTY_NAME CUSTOMER_NAME
, HZCSU.LOCATION CUSTOMER_SITE_USE
, PITEM.GROUPING_RULE_ID GROUPING_RULE_ID
, PPGR.NAME INV_GROUP_NAME
, PITEM.ADJ_START_DATE ADJ_START_DATE
, PITEM.ADJ_END_DATE ADJ_END_DATE
, PITEM.LAST_ADJUSTMENT_TYPE_CODE LAST_ADJUSTMENT_TYPE_CODE
, PITEM.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PITEM.ATTRIBUTE1 ATTRIBUTE1
, PITEM.ATTRIBUTE2 ATTRIBUTE2
, PITEM.ATTRIBUTE3 ATTRIBUTE3
, PITEM.ATTRIBUTE4 ATTRIBUTE4
, PITEM.ATTRIBUTE5 ATTRIBUTE5
, PITEM.ATTRIBUTE6 ATTRIBUTE6
, PITEM.ATTRIBUTE7 ATTRIBUTE7
, PITEM.ATTRIBUTE8 ATTRIBUTE8
, PITEM.ATTRIBUTE9 ATTRIBUTE9
, PITEM.ATTRIBUTE10 ATTRIBUTE10
, PITEM.ATTRIBUTE11 ATTRIBUTE11
, PITEM.ATTRIBUTE12 ATTRIBUTE12
, PITEM.ATTRIBUTE13 ATTRIBUTE13
, PITEM.ATTRIBUTE14 ATTRIBUTE14
, PITEM.ATTRIBUTE15 ATTRIBUTE15
, PTERM.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PTERM.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, PTERM.AP_AR_TERM_ID AP_AR_TERM_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.INV_RULE_ID INV_RULE_ID
, PTERM.ACCOUNT_RULE_ID ACCOUNT_RULE_ID
, PTERM.SALESREP_ID SALESREP_ID
, PTERM.CUST_PO_NUMBER CUST_PO_NUMBER
, PTERM.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, PTERM.TERM_TEMPLATE_ID TERM_TEMPLATE_ID
, PITEM.ORG_ID ORG_ID
, PTERM.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
, PCLOOK.MEANING TCC_PAYMENT
, PCLOOK.MEANING TCC_BILLING
FROM HZ_CUST_SITE_USES_ALL HZCSU
, HZ_CUST_SITE_USES_ALL HZCSS
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS_ALL HZC
, FND_LOOKUP_VALUES FLV
, FND_LOOKUP_VALUES FLV1
, PN_PAYMENT_TERMS_ALL PTERM
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PN_PAY_GROUP_RULES PPGR
, PN_PAYMENT_ITEMS PITEM
, PN_LEASES_ALL PLEASES
, FND_LOOKUPS PCLOOK
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 PITEM.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
AND NVL(PITEM.ORG_ID
, -99) = NVL(PTERM.ORG_ID
, -99)
AND PTERM.PAYMENT_TERM_ID = PITEM.PAYMENT_TERM_ID
AND FLV.LOOKUP_CODE = PTERM.PAYMENT_PURPOSE_CODE
AND FLV.LOOKUP_TYPE = 'PN_PAYMENT_PURPOSE_TYPE'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.VIEW_APPLICATION_ID = 0
AND FLV.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP('PN_PAYMENT_PURPOSE_TYPE'
, 0)
AND FLV1.LOOKUP_CODE = PTERM.PAYMENT_TERM_TYPE_CODE
AND FLV1.LOOKUP_TYPE ='PN_PAYMENT_TERM_TYPE'
AND FLV1.LANGUAGE = USERENV('LANG')
AND FLV1.VIEW_APPLICATION_ID = 0
AND FLV1.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP('PN_PAYMENT_PURPOSE_TYPE'
, 0)
AND POV.VENDOR_ID(+) = PITEM.VENDOR_ID
AND POVS.VENDOR_SITE_ID(+) = PITEM.VENDOR_SITE_ID
AND HZC.CUST_ACCOUNT_ID(+) = PITEM.CUSTOMER_ID
AND HZP.PARTY_ID(+) = HZC.PARTY_ID
AND HZCSU.SITE_USE_ID(+) = PITEM.CUSTOMER_SITE_USE_ID
AND PPGR.GROUPING_RULE_ID (+) = PITEM.GROUPING_RULE_ID
AND HZCSS.SITE_USE_ID(+) = PITEM.CUST_SHIP_SITE_ID UNION ALL SELECT PITEM.ROWID ROW_ID
, PITEM.PAYMENT_ITEM_ID PAYMENT_ITEM_ID
, PITEM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PITEM.LAST_UPDATED_BY LAST_UPDATED_BY
, PITEM.CREATION_DATE CREATION_DATE
, PITEM.CREATED_BY CREATED_BY
, PITEM.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PITEM.DUE_DATE DUE_DATE
, PITEM.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PITEM.CURRENCY_CODE CURRENCY_CODE
, PITEM.RATE RATE
, PITEM.PAYMENT_ITEM_TYPE_LOOKUP_CODE PAYMENT_ITEM_TYPE_LOOKUP_CODE
, PITEM.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PITEM.PAYMENT_TERM_ID PAYMENT_TERM_ID
, PITEM.VENDOR_ID VENDOR_ID
, PITEM.VENDOR_SITE_ID VENDOR_SITE_ID
, PITEM.CUSTOMER_ID CUSTOMER_ID
, PITEM.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, PITEM.ACTUAL_AMOUNT ACTUAL_AMOUNT
, PITEM.ESTIMATED_AMOUNT ESTIMATED_AMOUNT
, PITEM.ACCOUNTED_AMOUNT ACCOUNTED_AMOUNT
, PITEM.ACCOUNTED_DATE ACCOUNTED_DATE
, PITEM.PERIOD_FRACTION PERIOD_FRACTION
, PITEM.DESCRIPTION DESCRIPTION
, PITEM.COMMENTS COMMENTS
, PITEM.TRANSFERRED_TO_AP_FLAG TRANSFERRED_TO_AP_FLAG
, PITEM.AP_INVOICE_NUM AP_INVOICE_NUM
, PITEM.ENTERED_BY_LEASE ENTERED_BY_LEASE
, PITEM.CUST_SHIP_SITE_ID CUST_SHIP_SITE_ID
, HZCSS.LOCATION CUST_SHIP_SITE_USE
, FLV.MEANING PAYMENT_PURPOSE
, FLV1.MEANING PAYMENT_TERM_TYPE
, DECODE(PTERM.FREQUENCY_CODE
, 'OT'
, 'Y'
, 'N') ONE_TIME
, PTERM.CODE_COMBINATION_ID CODE_COMBINATION_ID
, PTERM.NORMALIZE TERM_NORMALIZE_FLAG
, PTERM.ACTUAL_AMOUNT TERM_ACTUAL_AMOUNT
, PTERM.ESTIMATED_AMOUNT TERM_ESTIMATED_AMOUNT
, POV.VENDOR_NAME VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, HZC.ACCOUNT_NUMBER CUSTOMER_NUMBER
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, NULL AS TAX_FLAG
, HZP.PARTY_NAME CUSTOMER_NAME
, HZCSU.LOCATION CUSTOMER_SITE_USE
, PITEM.GROUPING_RULE_ID GROUPING_RULE_ID
, PPGR.NAME INV_GROUP_NAME
, PITEM.ADJ_START_DATE ADJ_START_DATE
, PITEM.ADJ_END_DATE ADJ_END_DATE
, PITEM.LAST_ADJUSTMENT_TYPE_CODE LAST_ADJUSTMENT_TYPE_CODE
, PITEM.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PITEM.ATTRIBUTE1 ATTRIBUTE1
, PITEM.ATTRIBUTE2 ATTRIBUTE2
, PITEM.ATTRIBUTE3 ATTRIBUTE3
, PITEM.ATTRIBUTE4 ATTRIBUTE4
, PITEM.ATTRIBUTE5 ATTRIBUTE5
, PITEM.ATTRIBUTE6 ATTRIBUTE6
, PITEM.ATTRIBUTE7 ATTRIBUTE7
, PITEM.ATTRIBUTE8 ATTRIBUTE8
, PITEM.ATTRIBUTE9 ATTRIBUTE9
, PITEM.ATTRIBUTE10 ATTRIBUTE10
, PITEM.ATTRIBUTE11 ATTRIBUTE11
, PITEM.ATTRIBUTE12 ATTRIBUTE12
, PITEM.ATTRIBUTE13 ATTRIBUTE13
, PITEM.ATTRIBUTE14 ATTRIBUTE14
, PITEM.ATTRIBUTE15 ATTRIBUTE15
, PTERM.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PTERM.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, PTERM.AP_AR_TERM_ID AP_AR_TERM_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.INV_RULE_ID INV_RULE_ID
, PTERM.ACCOUNT_RULE_ID ACCOUNT_RULE_ID
, PTERM.SALESREP_ID SALESREP_ID
, PTERM.CUST_PO_NUMBER CUST_PO_NUMBER
, PTERM.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, PTERM.TERM_TEMPLATE_ID TERM_TEMPLATE_ID
, PITEM.ORG_ID ORG_ID
, PTERM.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
, PCLOOK.MEANING TCC_PAYMENT
, PCLOOK.MEANING TCC_BILLING
FROM HZ_CUST_SITE_USES_ALL HZCSU
, HZ_CUST_SITE_USES_ALL HZCSS
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS_ALL HZC
, FND_LOOKUP_VALUES FLV
, FND_LOOKUP_VALUES FLV1
, PN_PAYMENT_TERMS_ALL PTERM
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PN_PAY_GROUP_RULES PPGR
, PN_PAYMENT_ITEMS PITEM
, PN_LEASES_ALL PLEASES
, FND_LOOKUPS PCLOOK
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 PITEM.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
AND NVL(PITEM.ORG_ID
, -99) = NVL(PTERM.ORG_ID
, -99)
AND PTERM.PAYMENT_TERM_ID = PITEM.PAYMENT_TERM_ID
AND FLV.LOOKUP_CODE = PTERM.PAYMENT_PURPOSE_CODE
AND FLV.LOOKUP_TYPE = 'PN_PAYMENT_PURPOSE_TYPE'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.VIEW_APPLICATION_ID = 0
AND FLV.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP('PN_PAYMENT_PURPOSE_TYPE'
, 0)
AND FLV1.LOOKUP_CODE = PTERM.PAYMENT_TERM_TYPE_CODE
AND FLV1.LOOKUP_TYPE ='PN_PAYMENT_TERM_TYPE'
AND FLV1.LANGUAGE = USERENV('LANG')
AND FLV1.VIEW_APPLICATION_ID = 0
AND FLV1.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP('PN_PAYMENT_PURPOSE_TYPE'
, 0)
AND POV.VENDOR_ID(+) = PITEM.VENDOR_ID
AND POVS.VENDOR_SITE_ID(+) = PITEM.VENDOR_SITE_ID
AND HZC.CUST_ACCOUNT_ID(+) = PITEM.CUSTOMER_ID
AND HZP.PARTY_ID(+) = HZC.PARTY_ID
AND HZCSU.SITE_USE_ID(+) = PITEM.CUSTOMER_SITE_USE_ID
AND PPGR.GROUPING_RULE_ID (+) = PITEM.GROUPING_RULE_ID
AND HZCSS.SITE_USE_ID(+) = PITEM.CUST_SHIP_SITE_ID

Columns