View: IGI_STP_PACKAGES_V

Product:	IGI - Public Sector Financials International
Description:	View which displays information on Single Third Party netting transactions batch.
Implementation/DBA Data:	APPS.IGI_STP_PACKAGES_V

View Text

SELECT DISTINCT PCK.ROWID "ROW_ID"
, PCK.BATCH_ID
, PCK.PACKAGE_ID
, PCK.PACKAGE_NUM
, RAC.CUSTOMER_NAME
, PCK.STP_ID STP_ID
, RSU.LOCATION SITE_CODE
, PCK.APPLICATION
, APP.APPLICATION_NAME
, PCK.ACCOUNTING_DATE
, PCK.TRX_NUMBER
, PCK.RELATED_TRX_NUMBER
, PCK.REFERENCE
, NTYPE.MEANING NETTING_TRX_TYPE
, ' ' DOCUMENT_TRX_TYPE
, PCK.AMOUNT
, PCK.CURRENCY_CODE
, PCK.EXCHANGE_RATE_TYPE
, PCK.EXCHANGE_RATE
, PCK.EXCHANGE_DATE
, GL.USER_CONVERSION_TYPE USER_RATE_TYPE
, PCK.CREATED_BY
, PCK.CREATION_DATE
, PCK.LAST_UPDATED_BY
, PCK.LAST_UPDATE_DATE
, PCK.LAST_UPDATE_LOGIN
FROM RA_CUSTOMERS RAC
, IGI_STP_PACKAGES PCK
, IGI_STP_NET_TYPE_ALLOC NET
, IGI_LOOKUPS NTYPE
, RA_CUST_TRX_TYPES DTYPE
, RA_SITE_USES RSU
, GL_DAILY_CONVERSION_TYPES GL
, FND_APPLICATION_VL APP
WHERE RAC.CUSTOMER_ID = PCK.STP_ID
AND PCK.NETTING_TRX_TYPE_ID = NTYPE.LOOKUP_CODE
AND NTYPE.LOOKUP_TYPE = 'STP_NETTING_TYPE'
AND NET.CUST_TRX_TYPE_ID = TO_CHAR(DTYPE.CUST_TRX_TYPE_ID)
AND NET.APPLICATION = PCK.APPLICATION
AND PCK.NETTING_TRX_TYPE_ID = NET.NETTING_TRX_TYPE_ID
AND RSU.SITE_USE_ID = PCK.SITE_ID
AND PCK.APPLICATION ='AR'
AND APP.APPLICATION_SHORT_NAME = PCK.APPLICATION
AND NVL(PCK.EXCHANGE_RATE_TYPE
, 'X') = GL.CONVERSION_TYPE(+) UNION SELECT DISTINCT PCK.ROWID ROW_ID
, PCK.BATCH_ID
, PCK.PACKAGE_ID
, PCK.PACKAGE_NUM
, RAC.CUSTOMER_NAME
, PCK.STP_ID
, POV.VENDOR_SITE_CODE SITE_CODE
, PCK.APPLICATION
, APP.APPLICATION_NAME
, PCK.ACCOUNTING_DATE
, PCK.TRX_NUMBER
, PCK.RELATED_TRX_NUMBER
, PCK.REFERENCE
, NTYPE.MEANING NETTING_TRX_TYPE
, ' ' DOCUMENT_TRX_TYPE
, PCK.AMOUNT
, PCK.CURRENCY_CODE
, PCK.EXCHANGE_RATE_TYPE
, PCK.EXCHANGE_RATE
, PCK.EXCHANGE_DATE
, GL.USER_CONVERSION_TYPE USER_RATE_TYPE
, PCK.CREATED_BY
, PCK.CREATION_DATE
, PCK.LAST_UPDATED_BY
, PCK.LAST_UPDATE_DATE
, PCK.LAST_UPDATE_LOGIN
FROM RA_CUSTOMERS RAC
, IGI_STP_PACKAGES PCK
, IGI_STP_NET_TYPE_ALLOC NET
, PO_VENDOR_SITES POV
, IGI_LOOKUPS NTYPE
, AP_LOOKUP_CODES DTYPE
, GL_DAILY_CONVERSION_TYPES GL
, FND_APPLICATION_VL APP
, IGI_PO_VENDORS IGI
WHERE RAC.CUSTOMER_ID = IGI.CUSTOMER_ID
AND IGI.VENDOR_ID = PCK.STP_ID
AND PCK.NETTING_TRX_TYPE_ID = NTYPE.LOOKUP_CODE
AND NTYPE.LOOKUP_TYPE = 'STP_NETTING_TYPE'
AND DTYPE.LOOKUP_TYPE = 'INVOICE TYPE'
AND DTYPE.LOOKUP_CODE = NET.CUST_TRX_TYPE_ID
AND NET.APPLICATION = DECODE(PCK.APPLICATION
, 'AP'
, 'SQLAP'
, PCK.APPLICATION)
AND PCK.NETTING_TRX_TYPE_ID = NET.NETTING_TRX_TYPE_ID
AND POV.VENDOR_SITE_ID = PCK.SITE_ID
AND PCK.APPLICATION ='AP'
AND APP.APPLICATION_SHORT_NAME = DECODE(PCK.APPLICATION
, 'AP'
, 'SQLAP'
, PCK.APPLICATION)
AND NVL(PCK.EXCHANGE_RATE_TYPE
, 'X') = GL.CONVERSION_TYPE(+)

Columns