View: IGI_EXP_AVAILABLE_TRX_V

Product:	IGI - Public Sector Financials International
Description:	Returns details of all transactions in Payables and Receivables that are available for inclusion into a Dialog Unit.
Implementation/DBA Data:	APPS.IGI_EXP_AVAILABLE_TRX_V

View Text

SELECT FAL.APPLICATION_NAME APPLICATION
, TRX.CTT_TYPE_NAME TRANSACTION_TYPE_DESC
, TRX.TRX_NUMBER TRANSACTION_NUMBER
, TRX.COMMENTS DESCRIPTION
, TRX.GD_GL_DATE GL_DATE
, TRX.RAC_BILL_TO_CUSTOMER_NAME THIRD_PARTY
, TRX.SU_BILL_TO_LOCATION THIRD_PARTY_SITE
, TRX_AMT.TRX_AMOUNT AMOUNT
, 222 APPLICATION_ID
, TRX.CTT_CLASS TRANSACTION_TYPE
, TRX.BILL_TO_CUSTOMER_ID THIRD_PARTY_ID
, TRX.BILL_TO_SITE_USE_ID THIRD_PARTY_SITE_ID
, TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, TRX_AMT.ORG_ID ORG_ID
, TRX.CREATION_DATE CREATION_DATE
, TRX.CREATED_BY CREATED_BY
, TRX.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, TRX.LAST_UPDATED_BY LAST_UPDATED_BY
, TRX.LAST_UPDATE_DATE LAST_UPDATE_DATE
, -999 INVOICE_ID
, TRX.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_V TRX
, FND_APPLICATION_VL FAL
, IGI_EXP_AR_TRX_AMOUNTS_V TRX_AMT
WHERE FAL.APPLICATION_ID = 222
AND TRX.CUSTOMER_TRX_ID = TRX_AMT.CUSTOMER_TRX_ID
AND TRX.COMPLETE_FLAG = 'N'
AND NOT EXISTS (SELECT AR_TRANS.CUSTOMER_TRX_ID
FROM IGI_EXP_AR_TRANS AR_TRANS
WHERE TRX.CUSTOMER_TRX_ID = AR_TRANS.CUSTOMER_TRX_ID) UNION SELECT FAL.APPLICATION_NAME APPLICATION
, INV.INVOICE_TYPE TRANSACTION_TYPE_DESC
, INV.INVOICE_NUM TRANSACTION_NUMBER
, INV.DESCRIPTION DESCRIPTION
, INV.GL_DATE GL_DATE
, INV.VENDOR_NAME THIRD_PARTY
, INV.VENDOR_SITE_CODE THIRD_PARTY_SITE
, INV.INVOICE_AMOUNT AMOUNT
, 200 APPLICATION_ID
, INV.INVOICE_TYPE_LOOKUP_CODE TRANSACTION_TYPE
, INV.VENDOR_ID THIRD_PARTY_ID
, INV.VENDOR_SITE_ID THIRD_PARTY_SITE_ID
, INV.INVOICE_CURRENCY_CODE CURRENCY_CODE
, INV.ORG_ID ORG_ID
, INV.CREATION_DATE CREATION_DATE
, INV.CREATED_BY CREATED_BY
, INV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, INV.LAST_UPDATED_BY LAST_UPDATED_BY
, INV.LAST_UPDATE_DATE LAST_UPDATE_DATE
, INV.INVOICE_ID INVOICE_ID
, -999 CUSTOMER_TRX_ID
FROM AP_INVOICES_V INV
, FND_APPLICATION_VL FAL
, AP_HOLDS HOLD
WHERE FAL.APPLICATION_ID = 200
AND HOLD.INVOICE_ID = INV.INVOICE_ID
AND HOLD.HOLD_LOOKUP_CODE = 'AWAIT EXP APP'
AND 1 = (SELECT COUNT(*)
FROM AP_HOLDS HOLD2
WHERE HOLD2.INVOICE_ID = INV.INVOICE_ID
AND HOLD2.RELEASE_LOOKUP_CODE IS NULL)
AND HOLD.RELEASE_LOOKUP_CODE IS NULL
AND INV.CANCELLED_DATE IS NULL
AND NOT EXISTS (SELECT AP_TRANS.INVOICE_ID
FROM IGI_EXP_AP_TRANS AP_TRANS
WHERE INV.INVOICE_ID = AP_TRANS.INVOICE_ID)

Columns