VIEW: APPS.IGI_EXP_AVAILABLE_TRX_V

Source

View Text - Preformatted

SELECT FAL.APPLICATION_NAME APPLICATION , TRX.CTT_TYPE_NAME TRANSACTION_TYPE_DESC , TRX.TRX_NUMBER TRANSACTION_NUMBER , ' ' 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 , -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.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)

View Text - HTML Formatted

SELECT FAL.APPLICATION_NAME APPLICATION
, TRX.CTT_TYPE_NAME TRANSACTION_TYPE_DESC
, TRX.TRX_NUMBER TRANSACTION_NUMBER
, ' ' 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
, -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.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)