VIEW: APPS.JL_BR_AR_REC_MET_ACCTS_EXT_V

Source

View Text - Preformatted

SELECT RMAE.ROWID ROW_ID , RMAE.BANK_ACCT_USE_ID BANK_ACCT_USE_ID, RMAE.RECEIPT_METHOD_ID RECEIPT_METHOD_ID , RMAE.BANK_CHARGES_CCID BANK_CHARGES_CCID, RMAE.COLL_ENDORSEMENT_CCID COLL_ENDORSEMENT_CCID , RMAE.BILLS_COLLECTION_CCID BILLS_COLLECTION_CCID , RMAE.OTHER_CREDITS_CCID OTHER_CREDITS_CCID , RMAE.FACTORING_DOCS_CCID FACTORING_DOCS_CCID , RMAE.PORTFOLIO_CODE PORTFOLIO_CODE , RMAE.MIN_DOCUMENT_AMOUNT MIN_DOCUMENT_AMOUNT , RMAE.MAX_DOCUMENT_AMOUNT MAX_DOCUMENT_AMOUNT , RMAE.MIN_REMITTANCE_AMOUNT MIN_REMITTANCE_AMOUNT , RMAE.MAX_REMITTANCE_AMOUNT MAX_REMITTANCE_AMOUNT , RMAE.BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE1 , RMAE.BANK_INSTRUCTION_CODE2 BANK_INSTRUCTION_CODE2 , RMAE.BANK_CHARGE_AMOUNT BANK_CHARGE_AMOUNT , RMAE.FACTORING_RATE FACTORING_RATE , RMAE.FACTORING_RATE_PERIOD FACTORING_RATE_PERIOD , RMAE.BATCH_SOURCE_ID BATCH_SOURCE_ID , RMAE.CALCULATED_INTEREST_CCID CALCULATED_INTEREST_CCID , RMAE.INTEREST_WRITEOFF_CCID INTEREST_WRITEOFF_CCID , RMAE.FACTORING_INTEREST_CCID FACTORING_INTEREST_CCID , RMAE.ABATEMENT_WRITEOFF_CCID ABATEMENT_WRITEOFF_CCID , RMAE.ABATEMENT_REVENUE_CCID ABATEMENT_REVENUE_CCID , RMAE.INTEREST_REVENUE_CCID INTEREST_REVENUE_CCID , RMAE.CALCULATED_INTEREST_RECTRX_ID CALCULATED_INTEREST_RECTRX_ID , RMAE.INTEREST_WRITEOFF_RECTRX_ID INTEREST_WRITEOFF_RECTRX_ID , RMAE.INTEREST_REVENUE_RECTRX_ID INTEREST_REVENUE_RECTRX_ID , RMAE.ABATEMENT_WRITEOFF_RECTRX_ID ABATEMENT_WRITEOFF_RECTRX_ID , RMAE.ABATEMENT_REVENUE_RECTRX_ID ABATEMENT_REVENUE_RECTRX_ID , RMAE.WRITEOFF_PERC_TOLERANCE WRITEOFF_PERC_TOLERANCE , RMAE.WRITEOFF_AMOUNT_TOLERANCE WRITEOFF_AMOUNT_TOLERANCE , RMAE.GL_DATE_BANK_RETURN GL_DATE_BANK_RETURN , RMAE.DISC_ENDORSEMENT_CCID, RMAE.DISCOUNTED_BILLS_CCID, RMAE.BILLS_DISCOUNT_CCID, RMAE.FACTORING_CHARGES_CCID, FLV2.MEANING GL_DATE_BANK_RETURN_DSP , RMAE.FLOATING FLOATING , RMAE.ORG_ID ORG_ID , RMAE.LAST_UPDATE_DATE LAST_UPDATE_DATE , RMAE.LAST_UPDATED_BY LAST_UPDATED_BY , RMAE.CREATION_DATE CREATION_DATE , RMAE.CREATED_BY CREATED_BY , RMAE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , ARM.NAME RECEIPT_METHOD_NAME_DSP , ARC.NAME RECEIPT_CLASS_NAME_DSP , ARC.GLOBAL_ATTRIBUTE1 COLLECTION_METHOD , FLV.MEANING COLLECTION_METHOD_DSP , HzPartyBank.PARTY_ID BANK_PARTY_ID, HzPartyBank.PARTY_NAME BANK_NAME_DSP , HzOrgProfileBank.BANK_OR_BRANCH_NUMBER BANK_NUMBER_DSP , HzPartyBranch.PARTY_NAME BANK_BRANCH_NAME_DSP , HzOrgProfileBranch.BANK_OR_BRANCH_NUMBER BANK_NUM_DSP , CeBankAccount.BANK_BRANCH_ID BANK_BRANCH_ID_DSP , CeBankAccount.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM_DSP , ART1.NAME CALC_INTEREST_RECTRX_ID_DSP , ART2.NAME INT_WRITEOFF_RECTRX_ID_DSP , ART3.NAME INT_REVENUE_RECTRX_ID_DSP , ART4.NAME ABATE_WRITEOFF_RECTRX_ID_DSP , ART5.NAME ABATE_REVENUE_RECTRX_ID_DSP , JABI.BANK_INSTRUCTION_TYPE BANK_INSTRUCTION_TYPE1 , JABI.DESCRIPTION BANK_INSTRUCTION_CODE1_DSP , JABI2.BANK_INSTRUCTION_TYPE BANK_INSTRUCTION_TYPE2 , JABI2.DESCRIPTION BANK_INSTRUCTION_CODE2_DSP , ABS.NAME BATCH_SOURCE_DSP , RMAE.max_collection_docs, RMAE.occ_file_format_pgm_id, RMAE.occ_rep_format_pgm_id, RMAE.factor_format_pgm_id, RMAE.factor_remittance_media, RMAE.coll_format_pgm_id, RMAE.coll_remittance_media, COLLPGM.program_name coll_format_program_dsp, FCTPGM.program_name factor_format_program_dsp, OCCREP.program_name occ_rep_format_program_dsp, OCCFILE.program_name occ_file_format_program_dsp FROM JL_BR_AR_REC_MET_ACCTS_EXT_all RMAE, AR_RECEIPT_METHODS ARM, AR_RECEIPT_CLASSES ARC, FND_LOOKUPS FLV, FND_LOOKUPS FLV2, AR_RECEIVABLES_TRX_ALL ART1, AR_RECEIVABLES_TRX_ALL ART2, AR_RECEIVABLES_TRX_ALL ART3, AR_RECEIVABLES_TRX_ALL ART4, AR_RECEIVABLES_TRX_ALL ART5, JL_BR_AR_BANK_INSTRUCTIONS JABI, JL_BR_AR_BANK_INSTRUCTIONS JABI2, AR_BATCH_SOURCES ABS, CE_BANK_ACCOUNTS CeBankAccount, CE_BANK_ACCT_USES_ALL AcctUse, HZ_PARTIES HzPartyBank, HZ_ORGANIZATION_PROFILES HzOrgProfileBank, HZ_ORGANIZATION_PROFILES HzOrgProfileBranch, HZ_PARTIES HzPartyBranch, AP_PAYMENT_PROGRAMS CollPgm, AP_PAYMENT_PROGRAMS FctPgm, AP_PAYMENT_PROGRAMS OccRep, AP_PAYMENT_PROGRAMS OccFile WHERE ARM.RECEIPT_METHOD_ID = RMAE.RECEIPT_METHOD_ID AND ARM.RECEIPT_CLASS_ID = ARC.RECEIPT_CLASS_ID AND FLV.LOOKUP_CODE (+)= ARC.GLOBAL_ATTRIBUTE1 AND FLV.LOOKUP_TYPE (+) = 'JLBR_AR_COLLECTION_METHOD' AND FLV.ENABLED_FLAG (+) = 'Y' AND NVL(FLV.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(FLV.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND FLV2.LOOKUP_CODE (+) = RMAE.GL_DATE_BANK_RETURN AND FLV2.LOOKUP_TYPE (+) = 'JLBR_AR_GL_DATE_BANK_RETURN' AND FLV2.ENABLED_FLAG (+) = 'Y' AND NVL(FLV2.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(FLV2.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND AcctUse.BANK_ACCT_USE_ID = RMAE.BANK_ACCT_USE_ID AND AcctUse.BANK_ACCOUNT_ID = CeBankAccount.BANK_ACCOUNT_ID AND JABI.BANK_INSTRUCTION_CODE (+) = RMAE.BANK_INSTRUCTION_CODE1 AND (JABI.BANK_PARTY_ID IS NULL OR (HzPartyBank.PARTY_ID = JABI.BANK_PARTY_ID))AND JABI2.BANK_INSTRUCTION_CODE (+) = RMAE.BANK_INSTRUCTION_CODE2 AND (JABI2.BANK_PARTY_ID IS NULL OR (HzPartyBank.PARTY_ID = JABI2.BANK_PARTY_ID)) AND ART1.RECEIVABLES_TRX_ID (+) = RMAE.CALCULATED_INTEREST_RECTRX_ID AND ART2.RECEIVABLES_TRX_ID (+) = RMAE.INTEREST_WRITEOFF_RECTRX_ID AND ART3.RECEIVABLES_TRX_ID (+) = RMAE.INTEREST_REVENUE_RECTRX_ID AND ART4.RECEIVABLES_TRX_ID (+) =RMAE.ABATEMENT_WRITEOFF_RECTRX_ID AND ART5.RECEIVABLES_TRX_ID (+) = RMAE.ABATEMENT_REVENUE_RECTRX_ID AND ABS.BATCH_SOURCE_ID (+) = RMAE.BATCH_SOURCE_ID and CeBankAccount.BANK_BRANCH_ID =HzPartyBranch.PARTY_ID AND HzPartyBranch.PARTY_TYPE = 'ORGANIZATION' and HzOrgProfileBranch.PARTY_ID = HzPartyBranch.PARTY_ID and SYSDATE between TRUNC(HzOrgProfileBranch. effective_start_date) and NVL(TRUNC(HzOrgProfileBranch.effective_end_date), SYSDATE+1) and CeBankAccount.BANK_ID =HzPartyBank.PARTY_ID AND HzPartyBank.PARTY_TYPE = 'ORGANIZATION' and HzPartyBank.status = 'A' and HzOrgProfileBank.PARTY_ID = HzPartyBank.PARTY_ID and SYSDATE between TRUNC(HzOrgProfileBank.effective_start_date) and NVL(TRUNC(HzOrgProfileBank.effective_end_date), SYSDATE+1) and CollPgm.program_id = RMAE.coll_format_pgm_id and FctPgm.program_id = RMAE.factor_format_pgm_id and OccFile.program_id = RMAE.occ_file_format_pgm_id and OccRep.program_id = RMAE.occ_rep_format_pgm_id

View Text - HTML Formatted

SELECT RMAE.ROWID ROW_ID
, RMAE.BANK_ACCT_USE_ID BANK_ACCT_USE_ID
, RMAE.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, RMAE.BANK_CHARGES_CCID BANK_CHARGES_CCID
, RMAE.COLL_ENDORSEMENT_CCID COLL_ENDORSEMENT_CCID
, RMAE.BILLS_COLLECTION_CCID BILLS_COLLECTION_CCID
, RMAE.OTHER_CREDITS_CCID OTHER_CREDITS_CCID
, RMAE.FACTORING_DOCS_CCID FACTORING_DOCS_CCID
, RMAE.PORTFOLIO_CODE PORTFOLIO_CODE
, RMAE.MIN_DOCUMENT_AMOUNT MIN_DOCUMENT_AMOUNT
, RMAE.MAX_DOCUMENT_AMOUNT MAX_DOCUMENT_AMOUNT
, RMAE.MIN_REMITTANCE_AMOUNT MIN_REMITTANCE_AMOUNT
, RMAE.MAX_REMITTANCE_AMOUNT MAX_REMITTANCE_AMOUNT
, RMAE.BANK_INSTRUCTION_CODE1 BANK_INSTRUCTION_CODE1
, RMAE.BANK_INSTRUCTION_CODE2 BANK_INSTRUCTION_CODE2
, RMAE.BANK_CHARGE_AMOUNT BANK_CHARGE_AMOUNT
, RMAE.FACTORING_RATE FACTORING_RATE
, RMAE.FACTORING_RATE_PERIOD FACTORING_RATE_PERIOD
, RMAE.BATCH_SOURCE_ID BATCH_SOURCE_ID
, RMAE.CALCULATED_INTEREST_CCID CALCULATED_INTEREST_CCID
, RMAE.INTEREST_WRITEOFF_CCID INTEREST_WRITEOFF_CCID
, RMAE.FACTORING_INTEREST_CCID FACTORING_INTEREST_CCID
, RMAE.ABATEMENT_WRITEOFF_CCID ABATEMENT_WRITEOFF_CCID
, RMAE.ABATEMENT_REVENUE_CCID ABATEMENT_REVENUE_CCID
, RMAE.INTEREST_REVENUE_CCID INTEREST_REVENUE_CCID
, RMAE.CALCULATED_INTEREST_RECTRX_ID CALCULATED_INTEREST_RECTRX_ID
, RMAE.INTEREST_WRITEOFF_RECTRX_ID INTEREST_WRITEOFF_RECTRX_ID
, RMAE.INTEREST_REVENUE_RECTRX_ID INTEREST_REVENUE_RECTRX_ID
, RMAE.ABATEMENT_WRITEOFF_RECTRX_ID ABATEMENT_WRITEOFF_RECTRX_ID
, RMAE.ABATEMENT_REVENUE_RECTRX_ID ABATEMENT_REVENUE_RECTRX_ID
, RMAE.WRITEOFF_PERC_TOLERANCE WRITEOFF_PERC_TOLERANCE
, RMAE.WRITEOFF_AMOUNT_TOLERANCE WRITEOFF_AMOUNT_TOLERANCE
, RMAE.GL_DATE_BANK_RETURN GL_DATE_BANK_RETURN
, RMAE.DISC_ENDORSEMENT_CCID
, RMAE.DISCOUNTED_BILLS_CCID
, RMAE.BILLS_DISCOUNT_CCID
, RMAE.FACTORING_CHARGES_CCID
, FLV2.MEANING GL_DATE_BANK_RETURN_DSP
, RMAE.FLOATING FLOATING
, RMAE.ORG_ID ORG_ID
, RMAE.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RMAE.LAST_UPDATED_BY LAST_UPDATED_BY
, RMAE.CREATION_DATE CREATION_DATE
, RMAE.CREATED_BY CREATED_BY
, RMAE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, ARM.NAME RECEIPT_METHOD_NAME_DSP
, ARC.NAME RECEIPT_CLASS_NAME_DSP
, ARC.GLOBAL_ATTRIBUTE1 COLLECTION_METHOD
, FLV.MEANING COLLECTION_METHOD_DSP
, HZPARTYBANK.PARTY_ID BANK_PARTY_ID
, HZPARTYBANK.PARTY_NAME BANK_NAME_DSP
, HZORGPROFILEBANK.BANK_OR_BRANCH_NUMBER BANK_NUMBER_DSP
, HZPARTYBRANCH.PARTY_NAME BANK_BRANCH_NAME_DSP
, HZORGPROFILEBRANCH.BANK_OR_BRANCH_NUMBER BANK_NUM_DSP
, CEBANKACCOUNT.BANK_BRANCH_ID BANK_BRANCH_ID_DSP
, CEBANKACCOUNT.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM_DSP
, ART1.NAME CALC_INTEREST_RECTRX_ID_DSP
, ART2.NAME INT_WRITEOFF_RECTRX_ID_DSP
, ART3.NAME INT_REVENUE_RECTRX_ID_DSP
, ART4.NAME ABATE_WRITEOFF_RECTRX_ID_DSP
, ART5.NAME ABATE_REVENUE_RECTRX_ID_DSP
, JABI.BANK_INSTRUCTION_TYPE BANK_INSTRUCTION_TYPE1
, JABI.DESCRIPTION BANK_INSTRUCTION_CODE1_DSP
, JABI2.BANK_INSTRUCTION_TYPE BANK_INSTRUCTION_TYPE2
, JABI2.DESCRIPTION BANK_INSTRUCTION_CODE2_DSP
, ABS.NAME BATCH_SOURCE_DSP
, RMAE.MAX_COLLECTION_DOCS
, RMAE.OCC_FILE_FORMAT_PGM_ID
, RMAE.OCC_REP_FORMAT_PGM_ID
, RMAE.FACTOR_FORMAT_PGM_ID
, RMAE.FACTOR_REMITTANCE_MEDIA
, RMAE.COLL_FORMAT_PGM_ID
, RMAE.COLL_REMITTANCE_MEDIA
, COLLPGM.PROGRAM_NAME COLL_FORMAT_PROGRAM_DSP
, FCTPGM.PROGRAM_NAME FACTOR_FORMAT_PROGRAM_DSP
, OCCREP.PROGRAM_NAME OCC_REP_FORMAT_PROGRAM_DSP
, OCCFILE.PROGRAM_NAME OCC_FILE_FORMAT_PROGRAM_DSP
FROM JL_BR_AR_REC_MET_ACCTS_EXT_ALL RMAE
, AR_RECEIPT_METHODS ARM
, AR_RECEIPT_CLASSES ARC
, FND_LOOKUPS FLV
, FND_LOOKUPS FLV2
, AR_RECEIVABLES_TRX_ALL ART1
, AR_RECEIVABLES_TRX_ALL ART2
, AR_RECEIVABLES_TRX_ALL ART3
, AR_RECEIVABLES_TRX_ALL ART4
, AR_RECEIVABLES_TRX_ALL ART5
, JL_BR_AR_BANK_INSTRUCTIONS JABI
, JL_BR_AR_BANK_INSTRUCTIONS JABI2
, AR_BATCH_SOURCES ABS
, CE_BANK_ACCOUNTS CEBANKACCOUNT
, CE_BANK_ACCT_USES_ALL ACCTUSE
, HZ_PARTIES HZPARTYBANK
, HZ_ORGANIZATION_PROFILES HZORGPROFILEBANK
, HZ_ORGANIZATION_PROFILES HZORGPROFILEBRANCH
, HZ_PARTIES HZPARTYBRANCH
, AP_PAYMENT_PROGRAMS COLLPGM
, AP_PAYMENT_PROGRAMS FCTPGM
, AP_PAYMENT_PROGRAMS OCCREP
, AP_PAYMENT_PROGRAMS OCCFILE
WHERE ARM.RECEIPT_METHOD_ID = RMAE.RECEIPT_METHOD_ID
AND ARM.RECEIPT_CLASS_ID = ARC.RECEIPT_CLASS_ID
AND FLV.LOOKUP_CODE (+)= ARC.GLOBAL_ATTRIBUTE1
AND FLV.LOOKUP_TYPE (+) = 'JLBR_AR_COLLECTION_METHOD'
AND FLV.ENABLED_FLAG (+) = 'Y'
AND NVL(FLV.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(FLV.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND FLV2.LOOKUP_CODE (+) = RMAE.GL_DATE_BANK_RETURN
AND FLV2.LOOKUP_TYPE (+) = 'JLBR_AR_GL_DATE_BANK_RETURN'
AND FLV2.ENABLED_FLAG (+) = 'Y'
AND NVL(FLV2.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(FLV2.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND ACCTUSE.BANK_ACCT_USE_ID = RMAE.BANK_ACCT_USE_ID
AND ACCTUSE.BANK_ACCOUNT_ID = CEBANKACCOUNT.BANK_ACCOUNT_ID
AND JABI.BANK_INSTRUCTION_CODE (+) = RMAE.BANK_INSTRUCTION_CODE1
AND (JABI.BANK_PARTY_ID IS NULL OR (HZPARTYBANK.PARTY_ID = JABI.BANK_PARTY_ID))AND JABI2.BANK_INSTRUCTION_CODE (+) = RMAE.BANK_INSTRUCTION_CODE2
AND (JABI2.BANK_PARTY_ID IS NULL OR (HZPARTYBANK.PARTY_ID = JABI2.BANK_PARTY_ID))
AND ART1.RECEIVABLES_TRX_ID (+) = RMAE.CALCULATED_INTEREST_RECTRX_ID
AND ART2.RECEIVABLES_TRX_ID (+) = RMAE.INTEREST_WRITEOFF_RECTRX_ID
AND ART3.RECEIVABLES_TRX_ID (+) = RMAE.INTEREST_REVENUE_RECTRX_ID
AND ART4.RECEIVABLES_TRX_ID (+) =RMAE.ABATEMENT_WRITEOFF_RECTRX_ID
AND ART5.RECEIVABLES_TRX_ID (+) = RMAE.ABATEMENT_REVENUE_RECTRX_ID
AND ABS.BATCH_SOURCE_ID (+) = RMAE.BATCH_SOURCE_ID
AND CEBANKACCOUNT.BANK_BRANCH_ID =HZPARTYBRANCH.PARTY_ID
AND HZPARTYBRANCH.PARTY_TYPE = 'ORGANIZATION'
AND HZORGPROFILEBRANCH.PARTY_ID = HZPARTYBRANCH.PARTY_ID
AND SYSDATE BETWEEN TRUNC(HZORGPROFILEBRANCH. EFFECTIVE_START_DATE)
AND NVL(TRUNC(HZORGPROFILEBRANCH.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND CEBANKACCOUNT.BANK_ID =HZPARTYBANK.PARTY_ID
AND HZPARTYBANK.PARTY_TYPE = 'ORGANIZATION'
AND HZPARTYBANK.STATUS = 'A'
AND HZORGPROFILEBANK.PARTY_ID = HZPARTYBANK.PARTY_ID
AND SYSDATE BETWEEN TRUNC(HZORGPROFILEBANK.EFFECTIVE_START_DATE)
AND NVL(TRUNC(HZORGPROFILEBANK.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND COLLPGM.PROGRAM_ID = RMAE.COLL_FORMAT_PGM_ID
AND FCTPGM.PROGRAM_ID = RMAE.FACTOR_FORMAT_PGM_ID
AND OCCFILE.PROGRAM_ID = RMAE.OCC_FILE_FORMAT_PGM_ID
AND OCCREP.PROGRAM_ID = RMAE.OCC_REP_FORMAT_PGM_ID