VIEW: APPS.IGI_STP_PACKAGES_V

Source

View Text - Preformatted

SELECT distinct PCK.ORG_ID "ORG_ID" , PCK.ROWID "ROW_ID" , PCK.BATCH_ID , PCK.PACKAGE_ID , PCK.PACKAGE_NUM ,substrb(PS.party_name,1,50) "CUSTOMER_NAME" , PCK.STP_ID "STP_ID" , CSU.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 HZ_PARTIES PS, HZ_CUST_ACCOUNTS CS, IGI_STP_PACKAGES PCK, IGI_STP_NET_TYPE_ALLOC NET, IGI_LOOKUPS NTYPE, RA_CUST_TRX_TYPES DTYPE, HZ_CUST_SITE_USES CSU, GL_DAILY_CONVERSION_TYPES GL, FND_APPLICATION_VL APP WHERE PS.PARTY_ID = CS.PARTY_ID AND CS. cust_account_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 CSU.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.ORG_ID "ORG_ID" ,PCK.ROWID "ROW_ID" , PCK.BATCH_ID , PCK.PACKAGE_ID , PCK.PACKAGE_NUM , substrb(PS.party_name,1,50) "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 HZ_PARTIES PS, HZ_CUST_ACCOUNTS CS, IGI_STP_PACKAGES PCK, IGI_STP_NET_TYPE_ALLOC NET, AP_SUPPLIER_SITES_ALL POV, IGI_LOOKUPS NTYPE, AP_LOOKUP_CODES DTYPE, GL_DAILY_CONVERSION_TYPES GL, FND_APPLICATION_VL APP, IGI_PO_VENDORS IGI WHERE PS.PARTY_ID = CS.PARTY_ID AND CS. cust_account_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(+)

View Text - HTML Formatted

SELECT DISTINCT PCK.ORG_ID "ORG_ID"
, PCK.ROWID "ROW_ID"
, PCK.BATCH_ID
, PCK.PACKAGE_ID
, PCK.PACKAGE_NUM
, SUBSTRB(PS.PARTY_NAME
, 1
, 50) "CUSTOMER_NAME"
, PCK.STP_ID "STP_ID"
, CSU.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 HZ_PARTIES PS
, HZ_CUST_ACCOUNTS CS
, IGI_STP_PACKAGES PCK
, IGI_STP_NET_TYPE_ALLOC NET
, IGI_LOOKUPS NTYPE
, RA_CUST_TRX_TYPES DTYPE
, HZ_CUST_SITE_USES CSU
, GL_DAILY_CONVERSION_TYPES GL
, FND_APPLICATION_VL APP
WHERE PS.PARTY_ID = CS.PARTY_ID
AND CS. CUST_ACCOUNT_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 CSU.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.ORG_ID "ORG_ID"
, PCK.ROWID "ROW_ID"
, PCK.BATCH_ID
, PCK.PACKAGE_ID
, PCK.PACKAGE_NUM
, SUBSTRB(PS.PARTY_NAME
, 1
, 50) "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 HZ_PARTIES PS
, HZ_CUST_ACCOUNTS CS
, IGI_STP_PACKAGES PCK
, IGI_STP_NET_TYPE_ALLOC NET
, AP_SUPPLIER_SITES_ALL POV
, IGI_LOOKUPS NTYPE
, AP_LOOKUP_CODES DTYPE
, GL_DAILY_CONVERSION_TYPES GL
, FND_APPLICATION_VL APP
, IGI_PO_VENDORS IGI
WHERE PS.PARTY_ID = CS.PARTY_ID
AND CS. CUST_ACCOUNT_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(+)