VIEW: APPS.JL_BR_AR_BANK_RETURNS_V

Source

View Text - Preformatted

SELECT A.ROWID ROW_ID, A.RETURN_ID, A.BANK_OCCURRENCE_CODE, A.OCCURRENCE_DATE, A.FILE_CONTROL, A.ENTRY_SEQUENTIAL_NUMBER, A.GENERATION_DATE, A.PROCESSING_DATE, A.DOCUMENT_ID, A.BANK_PARTY_ID, bankprofile.BANK_OR_BRANCH_NUMBER BANK_NUMBER, A.BATCH_SOURCE_ID, A.OUR_NUMBER, A.TRADE_NOTE_NUMBER, A.DUE_DATE, A.TRADE_NOTE_AMOUNT, A.COLLECTOR_BANK_PARTY_ID, A.COLLECTOR_BRANCH_PARTY_ID, A.BANK_CHARGE_AMOUNT, A.ABATEMENT_AMOUNT, A.DISCOUNT_AMOUNT, A.CREDIT_AMOUNT, A.INTEREST_AMOUNT_RECEIVED, A.CUSTOMER_ID, C.PARTY_NAME CUSTOMER_NAME, A.RETURN_INFO, A.BANK_USE, A.COMPANY_USE, A.ORG_ID, B.STD_OCCURRENCE_CODE, A.LAST_UPDATE_DATE, A.LAST_UPDATED_BY, A.CREATION_DATE, A.CREATED_BY, A.LAST_UPDATE_LOGIN FROM JL_BR_AR_BANK_RETURNS A, JL_BR_AR_BANK_OCCURRENCES B, HZ_PARTIES C, HZ_CUST_ACCOUNTS D , HZ_PARTIES bank, HZ_ORGANIZATION_PROFILES bankprofile WHERE B.BANK_PARTY_ID = A.BANK_PARTY_ID AND B.BANK_OCCURRENCE_TYPE = 'RETURN_OCCURRENCE' AND A.BANK_OCCURRENCE_CODE = B.BANK_OCCURRENCE_CODE AND D.CUST_ACCOUNT_ID = A.CUSTOMER_ID AND D.PARTY_ID = C.PARTY_ID AND bank.PARTY_ID = bankprofile.PARTY_ID AND bank.PARTY_ID = A.BANK_PARTY_ID AND SYSDATE between TRUNC(bankprofile. effective_start_date) and NVL(TRUNC(bankprofile.effective_end_date), SYSDATE+1)

View Text - HTML Formatted

SELECT A.ROWID ROW_ID
, A.RETURN_ID
, A.BANK_OCCURRENCE_CODE
, A.OCCURRENCE_DATE
, A.FILE_CONTROL
, A.ENTRY_SEQUENTIAL_NUMBER
, A.GENERATION_DATE
, A.PROCESSING_DATE
, A.DOCUMENT_ID
, A.BANK_PARTY_ID
, BANKPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER
, A.BATCH_SOURCE_ID
, A.OUR_NUMBER
, A.TRADE_NOTE_NUMBER
, A.DUE_DATE
, A.TRADE_NOTE_AMOUNT
, A.COLLECTOR_BANK_PARTY_ID
, A.COLLECTOR_BRANCH_PARTY_ID
, A.BANK_CHARGE_AMOUNT
, A.ABATEMENT_AMOUNT
, A.DISCOUNT_AMOUNT
, A.CREDIT_AMOUNT
, A.INTEREST_AMOUNT_RECEIVED
, A.CUSTOMER_ID
, C.PARTY_NAME CUSTOMER_NAME
, A.RETURN_INFO
, A.BANK_USE
, A.COMPANY_USE
, A.ORG_ID
, B.STD_OCCURRENCE_CODE
, A.LAST_UPDATE_DATE
, A.LAST_UPDATED_BY
, A.CREATION_DATE
, A.CREATED_BY
, A.LAST_UPDATE_LOGIN
FROM JL_BR_AR_BANK_RETURNS A
, JL_BR_AR_BANK_OCCURRENCES B
, HZ_PARTIES C
, HZ_CUST_ACCOUNTS D
, HZ_PARTIES BANK
, HZ_ORGANIZATION_PROFILES BANKPROFILE
WHERE B.BANK_PARTY_ID = A.BANK_PARTY_ID
AND B.BANK_OCCURRENCE_TYPE = 'RETURN_OCCURRENCE'
AND A.BANK_OCCURRENCE_CODE = B.BANK_OCCURRENCE_CODE
AND D.CUST_ACCOUNT_ID = A.CUSTOMER_ID
AND D.PARTY_ID = C.PARTY_ID
AND BANK.PARTY_ID = BANKPROFILE.PARTY_ID
AND BANK.PARTY_ID = A.BANK_PARTY_ID
AND SYSDATE BETWEEN TRUNC(BANKPROFILE. EFFECTIVE_START_DATE)
AND NVL(TRUNC(BANKPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)