VIEW: APPS.JL_BR_AR_OCC_DOCS_MRC_V

Source

View Text - Preformatted

SELECT MC.OCCURRENCE_ID , OD.DOCUMENT_ID , OD.BANK_OCCURRENCE_CODE , OD.BANK_PARTY_ID , BANKPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER, OD.BANK_OCCURRENCE_TYPE , OD.OCCURRENCE_DATE , OD.OCCURRENCE_STATUS , OD.ORIGINAL_REMITTANCE_MEDIA , OD.REMITTANCE_MEDIA , OD.SELECTION_DATE , OD.BORDERO_ID , OD.PORTFOLIO_CODE , OD.TRADE_NOTE_NUMBER , OD.DUE_DATE, OD.DOCUMENT_AMOUNT , OD.BANK_INSTRUCTION_CODE1 , OD.BANK_INSTRUCTION_CODE2 , OD.NUM_DAYS_INSTRUCTION , OD.INTEREST_PERCENT , OD.INTEREST_PERIOD , OD.INTEREST_AMOUNT , OD.GRACE_DAYS , OD.DISCOUNT_LIMIT_DATE , OD.DISCOUNT_AMOUNT , OD.CUSTOMER_ID , OD.SITE_USE_ID , OD.ABATEMENT_AMOUNT , MC.FLAG_POST_GL , OD.GL_DATE , MC.GL_POSTED_DATE , OD.ENDORSEMENT_CREDIT_CCID , OD.ENDORSEMENT_DEBIT_CCID , OD.ENDORSEMENT_DEBIT_AMOUNT , OD.ENDORSEMENT_CREDIT_AMOUNT , OD.BANK_CHARGE_AMOUNT , OD.BANK_CHARGES_CREDIT_CCID , OD.BANK_CHARGES_DEBIT_CCID , OD.BANK_CHARGES_CREDIT_AMOUNT , OD.BANK_CHARGES_DEBIT_AMOUNT, OD.DISCOUNT_CRED_ACCT_CCID , OD.FACTORING_DEBIT_CCID , OD.FACTORING_CREDIT_CCID , OD.FACTORING_CREDIT_AMOUNT , OD.FACTORING_DEBIT_AMOUNT , OD.FACTOR_INTEREST_CREDIT_CCID , OD.FACTOR_INTEREST_DEBIT_CCID , OD.FACTOR_INTEREST_DEBIT_AMOUNT , OD.FACTOR_INTEREST_CREDIT_AMOUNT , OD.REVERSE_CHARGES_CRED_CCID , OD.REVERSE_CHARGES_DEB_CCID , OD.REVERSE_CHARGES_DEB_AMOUNT , OD.REVERSE_CHARGES_CRED_AMOUNT , OD.REQUEST_ID , OD.RETURN_INFO , OD.INTEREST_INDICATOR , OD.RETURN_REQUEST_ID , OD.GL_CANCEL_DATE , OD.ORG_ID , OD.ATTRIBUTE_CATEGORY , OD.ATTRIBUTE1 , OD.ATTRIBUTE2 , OD.ATTRIBUTE3 , OD.ATTRIBUTE4 , OD.ATTRIBUTE5 , OD.ATTRIBUTE6 , OD.ATTRIBUTE7 , OD.ATTRIBUTE8 , OD.ATTRIBUTE9 , OD.ATTRIBUTE10 , OD.ATTRIBUTE11 , OD.ATTRIBUTE12 , OD.ATTRIBUTE13 , OD.ATTRIBUTE14 , OD.ATTRIBUTE15, MC.LAST_UPDATE_DATE , MC.LAST_UPDATED_BY , MC.CREATION_DATE , MC.CREATED_BY , MC.LAST_UPDATE_LOGIN , OD.FACTORING_CHARGES_CREDIT_CCID, OD.FACTORING_CHARGES_DEBIT_CCID, OD.FACTORING_CHARGES_CRED_AMOUNT, OD.FACTORING_CHARGES_DEB_AMOUNT, OD.DISCOUNTED_BILLS_CREDIT_CCID, OD.DISCOUNTED_BILLS_DEBIT_CCID, OD.DISCOUNTED_BILLS_CRED_AMOUNT, OD.DISCOUNTED_BILLS_DEB_AMOUNT, OD.DISCOUNT_CREDIT_CCID, OD.DISCOUNT_DEBIT_CCID, OD.DISCOUNT_CRED_AMOUNT, OD.DISCOUNT_DEB_AMOUNT, OD.CASH_CREDIT_CCID, OD.CASH_DEBIT_CCID, OD.CASH_CRED_AMOUNT , OD.CASH_DEB_AMOUNT FROM JL_BR_AR_OCCURRENCE_DOCS_ALL OD , JL_BR_AR_MC_OCC_DOCS MC , HZ_PARTIES BANK, HZ_ORGANIZATION_PROFILES BANKPROFILE WHERE mc.occurrence_id = od.occurrence_id AND BANK.PARTY_ID = OD.BANK_PARTY_ID AND BANK.PARTY_ID = BANKPROFILE.PARTY_ID AND SYSDATE between TRUNC(BANKPROFILE. effective_start_date) and NVL(TRUNC(BANKPROFILE.effective_end_date), SYSDATE+1) AND mc.set_of_books_id = NVL(TO_NUMBER(SUBSTR(USERENV('CLIENT_INFO'), 45, 10)), -99) AND NVL(od.org_id, NVL(TO_NUMBER (DECODE(SUBSTR(USERENV('CLIENT_INFO'), 1, 1), ' ', NULL, SUBSTR(USERENV('CLIENT_INFO'), 1, 10))), -99)) = NVL(TO_NUMBER(DECODE(SUBSTR( USERENV('CLIENT_INFO'), 1, 1), ' ',NULL,substr(userenv('CLIENT_INFO'), 1, 10))), -99)

View Text - HTML Formatted

SELECT MC.OCCURRENCE_ID
, OD.DOCUMENT_ID
, OD.BANK_OCCURRENCE_CODE
, OD.BANK_PARTY_ID
, BANKPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER
, OD.BANK_OCCURRENCE_TYPE
, OD.OCCURRENCE_DATE
, OD.OCCURRENCE_STATUS
, OD.ORIGINAL_REMITTANCE_MEDIA
, OD.REMITTANCE_MEDIA
, OD.SELECTION_DATE
, OD.BORDERO_ID
, OD.PORTFOLIO_CODE
, OD.TRADE_NOTE_NUMBER
, OD.DUE_DATE
, OD.DOCUMENT_AMOUNT
, OD.BANK_INSTRUCTION_CODE1
, OD.BANK_INSTRUCTION_CODE2
, OD.NUM_DAYS_INSTRUCTION
, OD.INTEREST_PERCENT
, OD.INTEREST_PERIOD
, OD.INTEREST_AMOUNT
, OD.GRACE_DAYS
, OD.DISCOUNT_LIMIT_DATE
, OD.DISCOUNT_AMOUNT
, OD.CUSTOMER_ID
, OD.SITE_USE_ID
, OD.ABATEMENT_AMOUNT
, MC.FLAG_POST_GL
, OD.GL_DATE
, MC.GL_POSTED_DATE
, OD.ENDORSEMENT_CREDIT_CCID
, OD.ENDORSEMENT_DEBIT_CCID
, OD.ENDORSEMENT_DEBIT_AMOUNT
, OD.ENDORSEMENT_CREDIT_AMOUNT
, OD.BANK_CHARGE_AMOUNT
, OD.BANK_CHARGES_CREDIT_CCID
, OD.BANK_CHARGES_DEBIT_CCID
, OD.BANK_CHARGES_CREDIT_AMOUNT
, OD.BANK_CHARGES_DEBIT_AMOUNT
, OD.DISCOUNT_CRED_ACCT_CCID
, OD.FACTORING_DEBIT_CCID
, OD.FACTORING_CREDIT_CCID
, OD.FACTORING_CREDIT_AMOUNT
, OD.FACTORING_DEBIT_AMOUNT
, OD.FACTOR_INTEREST_CREDIT_CCID
, OD.FACTOR_INTEREST_DEBIT_CCID
, OD.FACTOR_INTEREST_DEBIT_AMOUNT
, OD.FACTOR_INTEREST_CREDIT_AMOUNT
, OD.REVERSE_CHARGES_CRED_CCID
, OD.REVERSE_CHARGES_DEB_CCID
, OD.REVERSE_CHARGES_DEB_AMOUNT
, OD.REVERSE_CHARGES_CRED_AMOUNT
, OD.REQUEST_ID
, OD.RETURN_INFO
, OD.INTEREST_INDICATOR
, OD.RETURN_REQUEST_ID
, OD.GL_CANCEL_DATE
, OD.ORG_ID
, OD.ATTRIBUTE_CATEGORY
, OD.ATTRIBUTE1
, OD.ATTRIBUTE2
, OD.ATTRIBUTE3
, OD.ATTRIBUTE4
, OD.ATTRIBUTE5
, OD.ATTRIBUTE6
, OD.ATTRIBUTE7
, OD.ATTRIBUTE8
, OD.ATTRIBUTE9
, OD.ATTRIBUTE10
, OD.ATTRIBUTE11
, OD.ATTRIBUTE12
, OD.ATTRIBUTE13
, OD.ATTRIBUTE14
, OD.ATTRIBUTE15
, MC.LAST_UPDATE_DATE
, MC.LAST_UPDATED_BY
, MC.CREATION_DATE
, MC.CREATED_BY
, MC.LAST_UPDATE_LOGIN
, OD.FACTORING_CHARGES_CREDIT_CCID
, OD.FACTORING_CHARGES_DEBIT_CCID
, OD.FACTORING_CHARGES_CRED_AMOUNT
, OD.FACTORING_CHARGES_DEB_AMOUNT
, OD.DISCOUNTED_BILLS_CREDIT_CCID
, OD.DISCOUNTED_BILLS_DEBIT_CCID
, OD.DISCOUNTED_BILLS_CRED_AMOUNT
, OD.DISCOUNTED_BILLS_DEB_AMOUNT
, OD.DISCOUNT_CREDIT_CCID
, OD.DISCOUNT_DEBIT_CCID
, OD.DISCOUNT_CRED_AMOUNT
, OD.DISCOUNT_DEB_AMOUNT
, OD.CASH_CREDIT_CCID
, OD.CASH_DEBIT_CCID
, OD.CASH_CRED_AMOUNT
, OD.CASH_DEB_AMOUNT
FROM JL_BR_AR_OCCURRENCE_DOCS_ALL OD
, JL_BR_AR_MC_OCC_DOCS MC
, HZ_PARTIES BANK
, HZ_ORGANIZATION_PROFILES BANKPROFILE
WHERE MC.OCCURRENCE_ID = OD.OCCURRENCE_ID
AND BANK.PARTY_ID = OD.BANK_PARTY_ID
AND BANK.PARTY_ID = BANKPROFILE.PARTY_ID
AND SYSDATE BETWEEN TRUNC(BANKPROFILE. EFFECTIVE_START_DATE)
AND NVL(TRUNC(BANKPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND MC.SET_OF_BOOKS_ID = NVL(TO_NUMBER(SUBSTR(USERENV('CLIENT_INFO')
, 45
, 10))
, -99)
AND NVL(OD.ORG_ID
, NVL(TO_NUMBER (DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTR( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)