View: XTR_AR_OPEN_TRX_V

Product:	XTR - Treasury
Description:	This view is used for Hedge Positions form.
Implementation/DBA Data:	APPS.XTR_AR_OPEN_TRX_V

View Text

SELECT CUSTOMER_TRX_ID
, PAYMENT_SCHEDULE_ID
, BATCH_SOURCE_ID
, SOURCE_NAME
, CUST_TRX_TYPE_ID
, TRX_TYPE_NAME
, ORG_ID
, ORG_NAME
, CUSTOMER_ID
, CUSTOMER_NAME
, COMPANY_CODE
, TRX_DATE
, TRX_NUMBER
, CURRENCY_CODE
, SOB_CURRENCY_CODE
, AMOUNT
, MIN_DUE_DATE
, APPLIED_TRX
FROM XTR_AR_OPEN_APLD_TRX_V UNION ALL SELECT ACR.CASH_RECEIPT_ID
, APS.PAYMENT_SCHEDULE_ID
, TO_NUMBER(NULL)
, NULL
, -99999
, FND_MESSAGE.GET_STRING('XTR'
, 'XTR_AR_TRX_TYPE_CASH')
, ACR.ORG_ID
, HOU.NAME
, APS.CUSTOMER_ID
, HP.PARTY_NAME
, XPV.PARTY_CODE
, ACR.RECEIPT_DATE
, ACR.RECEIPT_NUMBER
, ACR.CURRENCY_CODE
, XPV.SET_OF_BOOKS_CURRENCY
, SUM(NVL((-1)*ARA.AMOUNT_APPLIED
, 0))
, MIN(APS.DUE_DATE)
, 'N'
FROM AR_CASH_RECEIPTS_ALL ACR
, AR_PAYMENT_SCHEDULES_ALL APS
, AR_RECEIVABLE_APPLICATIONS_ALL ARA
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, HR_OPERATING_UNITS HOU
, XTR_PARTIES_V XPV
, HZ_CUST_ACCT_SITES_ALL HCS
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HL
, HZ_CUST_SITE_USES_ALL HSU
WHERE APS.CLASS='PMT'
AND APS.STATUS = 'OP'
AND ACR.TYPE = 'CASH'
AND APS.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND APS.ORG_ID = ACR.ORG_ID
AND ARA.STATUS NOT IN('APP'
, 'UNID')
AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ARA.ORG_ID = ACR.ORG_ID
AND ACR.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.SET_OF_BOOKS_ID = (SELECT GLLE.LEDGER_ID
FROM GL_LEDGER_LE_V GLLE
WHERE GLLE.LEGAL_ENTITY_ID = (XPV.LEGAL_ENTITY_ID)
AND GLLE.LEDGER_CATEGORY_CODE = 'PRIMARY')
AND CRH.ORG_ID = ACR.ORG_ID
AND CRH.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> 'REVERSED'
AND CRH.REVERSAL_GL_DATE IS NULL
AND APS.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID = HP.PARTY_ID(+)
AND HSU.SITE_USE_CODE = 'BILL_TO'
AND HCS.ORG_ID = APS.ORG_ID
AND HCS.CUST_ACCOUNT_ID = APS.CUSTOMER_ID
AND HCS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND NVL(HSU.STATUS
, 'A') = 'A'
AND HCA.CUST_ACCOUNT_ID = HCS.CUST_ACCOUNT_ID
AND HCS.ORG_ID = HSU.ORG_ID
AND HCS.CUST_ACCT_SITE_ID = HSU.CUST_ACCT_SITE_ID
AND APS.ORG_ID = HSU.ORG_ID
AND APS.CUSTOMER_SITE_USE_ID = HSU.SITE_USE_ID (+) GROUP BY ACR.CASH_RECEIPT_ID
, APS.PAYMENT_SCHEDULE_ID
, ACR.TYPE
, ACR.ORG_ID
, HOU.NAME
, APS.CUSTOMER_ID
, HP.PARTY_NAME
, XPV.PARTY_CODE
, ACR.RECEIPT_DATE
, ACR.RECEIPT_NUMBER
, ACR.CURRENCY_CODE
, XPV.SET_OF_BOOKS_CURRENCY HAVING SUM(NVL(ARA.AMOUNT_APPLIED
, 0))<>0 UNION ALL SELECT ACR.CASH_RECEIPT_ID
, ARA.PAYMENT_SCHEDULE_ID
, TO_NUMBER(NULL)
, NULL
, -99999
, FND_MESSAGE.GET_STRING('XTR'
, 'XTR_AR_TRX_TYPE_CASH')
, ACR.ORG_ID
, HOU.NAME
, TO_NUMBER(NULL)
, NULL
, XPV.PARTY_CODE
, ACR.RECEIPT_DATE
, ACR.RECEIPT_NUMBER
, ACR.CURRENCY_CODE
, XPV.SET_OF_BOOKS_CURRENCY
, SUM(NVL((-1)*ARA.AMOUNT_APPLIED
, 0))
, TO_DATE(NULL)
, 'N'
FROM AR_CASH_RECEIPTS_ALL ACR
, AR_RECEIVABLE_APPLICATIONS_ALL ARA
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, HR_OPERATING_UNITS HOU
, XTR_PARTIES_V XPV
WHERE ACR.TYPE = 'CASH'
AND ARA.STATUS ='UNID'
AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND ARA.ORG_ID = ACR.ORG_ID
AND ACR.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.SET_OF_BOOKS_ID = (SELECT GLLE.LEDGER_ID
FROM GL_LEDGER_LE_V GLLE
WHERE GLLE.LEGAL_ENTITY_ID = (XPV.LEGAL_ENTITY_ID)
AND GLLE.LEDGER_CATEGORY_CODE = 'PRIMARY')
AND CRH.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
AND CRH.ORG_ID = ACR.ORG_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS <> 'REVERSED'
AND CRH.REVERSAL_GL_DATE IS NULL GROUP BY ACR.CASH_RECEIPT_ID
, ARA.PAYMENT_SCHEDULE_ID
, ACR.TYPE
, ACR.ORG_ID
, HOU.NAME
, XPV.PARTY_CODE
, ACR.RECEIPT_DATE
, ACR.RECEIPT_NUMBER
, ACR.CURRENCY_CODE
, XPV.SET_OF_BOOKS_CURRENCY HAVING SUM(NVL(ARA.AMOUNT_APPLIED
, 0))<>0

Columns