eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.XTR_AR_OPEN_TRX_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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