View: JL_BR_AR_REC_MET_ACCTS_EXT_V

Product:	JL - Latin America Localizations
Description:
Implementation/DBA Data:	APPS.JL_BR_AR_REC_MET_ACCTS_EXT_V

View Text

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

Columns