VIEW: APPS.JL_BR_AR_RET_INTERFACE_EXT_V

Source

View Text - Preformatted

SELECT A.ROWID ROW_ID, A.FILE_CONTROL, A.ENTRY_SEQUENTIAL_NUMBER, A.COMPANY_CODE, A.COMPANY_NAME, A.GENERATION_DATE, A.REMITTANCE_CODE, A.BANK_PARTY_ID, org.BANK_OR_BRANCH_NUMBER BANK_NUMBER, A.BANK_OCCURRENCE_CODE, C.DESCRIPTION, A.OCCURRENCE_DATE, A.PROCESSING_DATE, A.LAST_VALIDATION_DATE, A.INSCRIPTION_NUMBER, A.COMPANY_USE, A.OUR_NUMBER, A.PORTFOLIO_CODE, A.YOUR_NUMBER, A.DUE_DATE, A.TRADE_NOTE_AMOUNT, A.COLLECTOR_BANK_PARTY_ID, A.COLLECTOR_BRANCH_PARTY_ID, A.TRADE_NOTE_TYPE, A.BANK_CHARGE_AMOUNT, A.ABATEMENT_AMOUNT, A.DISCOUNT_AMOUNT, A.CREDIT_AMOUNT, A.INTEREST_AMOUNT_RECEIVED, A.CUSTOMER_NAME, A.RETURN_INFO, A.BANK_USE, A.ERROR_CODE , A.CANCELLATION_CODE, A.ORG_ID, A.LAST_UPDATE_DATE, A.LAST_UPDATED_BY, A.CREATION_DATE, A.CREATED_BY, A.LAST_UPDATE_LOGIN, B.MEANING ERROR_DESC FROM JL_BR_AR_RET_INTERFACE_EXT A, FND_LOOKUPS B, JL_BR_AR_BANK_OCCURRENCES C, HZ_ORGANIZATION_PROFILES org, HZ_PARTIES bank WHERE B.LOOKUP_TYPE = 'JLBR_AR_BANK_RETURN_ERRORS' AND B.LOOKUP_CODE = A.ERROR_CODE AND NVL(B.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(B.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND B.ENABLED_FLAG = 'Y' AND C.BANK_OCCURRENCE_CODE = A.BANK_OCCURRENCE_CODE AND C.BANK_PARTY_ID = A.BANK_PARTY_ID AND C.BANK_OCCURRENCE_TYPE = 'RETURN_OCCURRENCE' AND A.BANK_PARTY_ID = BANK.PARTY_ID AND BANK.PARTY_TYPE = 'ORGANIZATION' and BANK.status = 'A' and ORG.PARTY_ID = BANK.PARTY_ID and SYSDATE between TRUNC(ORG.effective_start_date) and NVL(TRUNC(ORG.effective_end_date), SYSDATE+1)

View Text - HTML Formatted

SELECT A.ROWID ROW_ID
, A.FILE_CONTROL
, A.ENTRY_SEQUENTIAL_NUMBER
, A.COMPANY_CODE
, A.COMPANY_NAME
, A.GENERATION_DATE
, A.REMITTANCE_CODE
, A.BANK_PARTY_ID
, ORG.BANK_OR_BRANCH_NUMBER BANK_NUMBER
, A.BANK_OCCURRENCE_CODE
, C.DESCRIPTION
, A.OCCURRENCE_DATE
, A.PROCESSING_DATE
, A.LAST_VALIDATION_DATE
, A.INSCRIPTION_NUMBER
, A.COMPANY_USE
, A.OUR_NUMBER
, A.PORTFOLIO_CODE
, A.YOUR_NUMBER
, A.DUE_DATE
, A.TRADE_NOTE_AMOUNT
, A.COLLECTOR_BANK_PARTY_ID
, A.COLLECTOR_BRANCH_PARTY_ID
, A.TRADE_NOTE_TYPE
, A.BANK_CHARGE_AMOUNT
, A.ABATEMENT_AMOUNT
, A.DISCOUNT_AMOUNT
, A.CREDIT_AMOUNT
, A.INTEREST_AMOUNT_RECEIVED
, A.CUSTOMER_NAME
, A.RETURN_INFO
, A.BANK_USE
, A.ERROR_CODE
, A.CANCELLATION_CODE
, A.ORG_ID
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.CREATION_DATE
, A.CREATED_BY
, A.LAST_UPDATE_LOGIN
, B.MEANING ERROR_DESC
FROM JL_BR_AR_RET_INTERFACE_EXT A
, FND_LOOKUPS B
, JL_BR_AR_BANK_OCCURRENCES C
, HZ_ORGANIZATION_PROFILES ORG
, HZ_PARTIES BANK
WHERE B.LOOKUP_TYPE = 'JLBR_AR_BANK_RETURN_ERRORS'
AND B.LOOKUP_CODE = A.ERROR_CODE
AND NVL(B.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(B.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND B.ENABLED_FLAG = 'Y'
AND C.BANK_OCCURRENCE_CODE = A.BANK_OCCURRENCE_CODE
AND C.BANK_PARTY_ID = A.BANK_PARTY_ID
AND C.BANK_OCCURRENCE_TYPE = 'RETURN_OCCURRENCE'
AND A.BANK_PARTY_ID = BANK.PARTY_ID
AND BANK.PARTY_TYPE = 'ORGANIZATION'
AND BANK.STATUS = 'A'
AND ORG.PARTY_ID = BANK.PARTY_ID
AND SYSDATE BETWEEN TRUNC(ORG.EFFECTIVE_START_DATE)
AND NVL(TRUNC(ORG.EFFECTIVE_END_DATE)
, SYSDATE+1)