This AR cross currency exchange gain loss query retrieves a detailed report of foreign currency receipts applied to customer invoices in Oracle Receivables, focusing on cross-currency transactions and exchange rate impacts.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves a detailed report of foreign currency receipts applied to customer invoices
HOU.NAME OPERATING_UNIT,
ACRA.RECEIPT_NUMBER,
ACRA.RECEIPT_DATE,
ACRA.CURRENCY_CODE RECEIPT_CURRENCY,
NVL(ACRA.AMOUNT, 0) RECEIPT_AMOUNT,
ACRA.EXCHANGE_RATE RECEIPT_EXCHANGE_RATE,
RCTA.TRX_NUMBER TRANSACTION_NUMBER,
RCTA.TRX_DATE TRANSACTION_DATE,
RCTA.INVOICE_CURRENCY_CODE,
NVL(ARAA.AMOUNT_APPLIED, 0) TRANSACTION_AMOUNT_APPLIED,
NVL(ARAA.ACCTD_AMOUNT_APPLIED_TO,0) TRANSACTION_ALLOCATED_AMOUNT,
NVL(ARAA.AMOUNT_APPLIED_FROM,0) TRANSACTION_AMOUNT_APPLIED_FROM,
NVL(ARAA.ACCTD_AMOUNT_APPLIED_FROM,0) ALLOCATED_AMOUNT_APPLIED_FROM,
ARAA.TRANS_TO_RECEIPT_RATE ACTUAL_CROSS_CURRENCY_RATE,
NVL(ARAA.ACCTD_AMOUNT_APPLIED_FROM,0) - NVL(ARAA.ACCTD_AMOUNT_APPLIED_TO,0) EXCHANGE_GAIN_LOSS,
FC.PRECISION RECEIPT_PRECISION,
ARAA.DISPLAY RECEIVABLES_FLAG,
ARAA.ACCTD_EARNED_DISCOUNT_TAKEN EARNED_DISCOUNT_TAKEN,
RCTA.DEFAULT_TAX_EXEMPT_FLAG,
FC.DERIVE_TYPE,
RCTA.FOB_POINT CUSTOMER_TRANSACTION_FOB_POINT,
RCTA.REASON_CODE ,
ACRA.REVERSAL_CATEGORY ,
ACRA.REVERSAL_REASON_CODE ,
ARAA.STATUS RECEIPT_STATUS,
RCTA.STATUS_TRX TRANSACTION_STATUS
FROM
RA_CUSTOMER_TRX_ALL RCTA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
AR_CASH_RECEIPTS_ALL ACRA,
FND_CURRENCIES FC,
HR_OPERATING_UNITS HOU
WHERE
1 = 1
AND RCTA.CUSTOMER_TRX_ID = ARAA.APPLIED_CUSTOMER_TRX_ID
AND ARAA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
AND ACRA.CURRENCY_CODE = FC.CURRENCY_CODE
AND ARAA.STATUS = 'APP'
AND ACRA.CURRENCY_CODE != RCTA.INVOICE_CURRENCY_CODE
AND HOU.ORGANIZATION_ID = RCTA.ORG_ID