lunes, 4 de agosto de 2014

Query de Recibos Aplicados con Transacciones

Este query nos devolverá el historial de Recibos Aplicados a las transacciones de los clientes indicando información del estado del recibo que puede ser Compensado (Cleared) Revertido(Reversed) 


SELECT AC.CUSTOMER_NAME               CLIENTE,
       RCTA.TRX_NUMBER                TRANSACCION,
       ACRA.RECEIPT_NUMBER            RECIBO,
       ACRA.TYPE                      TIPO,
       ACRHA.STATUS                   ESTADO,
       ACRHA.TRX_DATE                 FECHA,
       ARAA.AMOUNT_APPLIED            IMPORTE,
       ACRHA.EXCHANGE_RATE            TIPO_CAMBIO,
       ARAA.ACCTD_AMOUNT_APPLIED_FROM IMPORTE_FUNCIONAL

  FROM APPS.RA_CUSTOMER_TRX_ALL            RCTA,
       APPS.AR_CUSTOMERS                   AC,
       APPS.AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
       APPS.AR_CASH_RECEIPTS_ALL           ACRA,
       APPS.AR_CASH_RECEIPT_HISTORY_ALL    ACRHA
 WHERE AC.CUSTOMER_ID = ACRA.PAY_FROM_CUSTOMER
   AND ARAA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
   AND ARAA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
   AND ACRHA.CASH_RECEIPT_HISTORY_ID = ARAA.CASH_RECEIPT_HISTORY_ID
   AND (RCTA.TRX_NUMBER = '&TRX_NUMBER' OR '&TRX_NUMBER' IS NULL)
   AND (ACRA.RECEIPT_NUMBER = '&RECEIPT_NUMBER' OR '&RECEIPT_NUMBER' IS NULL)

No hay comentarios:

Publicar un comentario