JL_BR_AR_COLL_BATCHES

Details

Schema: FUSION
Object owner: JL
Object type: VIEW

Columns

Name
COLL_BATCH_ID SELECTION_CONTROL_ID BANK_ACCT_USE_ID COLL_BATCH_STATUS SEQUENTIAL_NUMBER_GENERATION TOTAL_COUNT TOTAL_AMOUNT SELECTION_DATE REMITTANCE_DATE REFUSED_DATE CANCELLATION_DATE COLLECTION_DATE WRITE_OFF_DATE DATE_IN_RECEIPT RECEIVED_DATE OUTPUT_PROGRAM_ID SELECT_ACCOUNT_ID OUTPUT_FORMAT OUTPUT_FORMAT_DESC FORMAT_DATE ORG_ID LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN BANK_NAME BANK_BRANCH_NAME BRANCH_NUMBER BANK_NUMBER BANK_PARTY_ID COLL_BATCH_STATUS_DESC AR_SYS_PARAM_GLOBAL_ATTRIBUTE1 AP_BNK_ACCTS_GLOBAL_ATTRIBUTE7 BANK_ACCOUNT_NUM OBJECT_VERSION_NUMBER

Name

COLL_BATCH_ID

SELECTION_CONTROL_ID

BANK_ACCT_USE_ID

COLL_BATCH_STATUS

SEQUENTIAL_NUMBER_GENERATION

TOTAL_COUNT

TOTAL_AMOUNT

SELECTION_DATE

REMITTANCE_DATE

REFUSED_DATE

CANCELLATION_DATE

COLLECTION_DATE

WRITE_OFF_DATE

DATE_IN_RECEIPT

RECEIVED_DATE

OUTPUT_PROGRAM_ID

SELECT_ACCOUNT_ID

OUTPUT_FORMAT

OUTPUT_FORMAT_DESC

FORMAT_DATE

ORG_ID

LAST_UPDATE_DATE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

LAST_UPDATE_LOGIN

BANK_NAME

BANK_BRANCH_NAME

BRANCH_NUMBER

BANK_NUMBER

BANK_PARTY_ID

COLL_BATCH_STATUS_DESC

AR_SYS_PARAM_GLOBAL_ATTRIBUTE1

AP_BNK_ACCTS_GLOBAL_ATTRIBUTE7

BANK_ACCOUNT_NUM

OBJECT_VERSION_NUMBER

Query

SQL_Statement
SELECT A.COLL_BATCH_ID , A.SELECTION_CONTROL_ID , A.BANK_ACCT_USE_ID , A.COLL_BATCH_STATUS , A.SEQUENTIAL_NUMBER_GENERATION , A.TOTAL_COUNT, A.TOTAL_AMOUNT , A.SELECTION_DATE , A.REMITTANCE_DATE , A.REFUSED_DATE , A.CANCELLATION_DATE , A.COLLECTION_DATE , A.WRITE_OFF_DATE , A.DATE_IN_RECEIPT , A.RECEIVED_DATE , A.OUTPUT_PROGRAM_ID , A.SELECT_ACCOUNT_ID , A.OUTPUT_FORMAT , J.MEANING OUTPUT_FORMAT_DESC , A.FORMAT_DATE, A.ORG_ID , A.LAST_UPDATE_DATE , A.LAST_UPDATED_BY , A.CREATION_DATE , A.CREATED_BY , A.LAST_UPDATE_LOGIN , HzPartyBank.PARTY_NAME BANK_NAME , HzPartyBranch.PARTY_NAME BANK_BRANCH_NAME , HzOrgProfileBranch.BANK_OR_BRANCH_NUMBER BRANCH_NUMBER , HzOrgProfileBank.BANK_OR_BRANCH_NUMBER BANK_NUMBER, HzPartyBank.party_id BANK_PARTY_ID, E.MEANING COLL_BATCH_STATUS_DESC , F.GLOBAL_ATTRIBUTE1 AR_SYS_PARAM_GLOBAL_ATTRIBUTE1 , CeBankAccount.SECONDARY_ACCOUNT_REFERENCE AP_BNK_ACCTS_GLOBAL_ATTRIBUTE7 , CeBankAccount.BANK_ACCOUNT_NUM ,A.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER FROM JL_BR_AR_COLL_BATCHES A, FND_LOOKUPS E, AR_SYSTEM_PARAMETERS F , FND_LOOKUPS J , CE_BANK_ACCOUNTS CeBankAccount , CE_BANK_ACCT_USES_ALL AcctUse, HZ_PARTIES HzPartyBank, HZ_ORGANIZATION_PROFILES HzOrgProfileBank, HZ_ORGANIZATION_PROFILES HzOrgProfileBranch , HZ_CODE_ASSIGNMENTS HzCodeAssignmentBank , HZ_PARTIES HzPartyBranch, HZ_CODE_ASSIGNMENTS HzCodeAssignmentBranch, HZ_RELATIONSHIPS HzRelationshipBR WHERE CeBankAccount.BANK_BRANCH_ID = HzPartyBranch.PARTY_ID AND AcctUse.BANK_ACCT_USE_ID = A.BANK_ACCT_USE_ID AND AcctUse.BANK_ACCOUNT_ID = CeBankAccount.BANK_ACCOUNT_ID AND HzPartyBranch.PARTY_TYPE = 'ORGANIZATION' and HzOrgProfileBranch.PARTY_ID = HzPartyBranch.PARTY_ID and trunc(SYSDATE) between TRUNC(HzOrgProfileBranch. effective_start_date) and NVL(TRUNC(HzOrgProfileBranch.effective_end_date), trunc(SYSDATE)+1) and HzCodeAssignmentBranch.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' and HzCodeAssignmentBranch.CLASS_CODE = 'BANK_BRANCH' and HzCodeAssignmentBranch.OWNER_TABLE_NAME = 'HZ_PARTIES' and HzCodeAssignmentBranch.OWNER_TABLE_ID = HzPartyBranch.PARTY_ID and trunc(SYSDATE) between TRUNC(HzCodeAssignmentBranch. start_date_active) and NVL(TRUNC(HzCodeAssignmentBranch.end_date_active), trunc(SYSDATE)+1) and HzPartyBank.PARTY_TYPE = 'ORGANIZATION' and HzPartyBank.status = 'A' and HzOrgProfileBank.PARTY_ID = HzPartyBank.PARTY_ID and trunc(SYSDATE) between TRUNC(HzOrgProfileBank.effective_start_date) and NVL(TRUNC(HzOrgProfileBank.effective_end_date), trunc(SYSDATE)+1) and trunc(SYSDATE) between TRUNC(HzCodeAssignmentBank. start_date_active) and NVL(TRUNC(HzCodeAssignmentBank.end_date_active), trunc(SYSDATE)+1) and HzCodeAssignmentBank.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' and HzCodeAssignmentBank.CLASS_CODE = 'BANK' and HzCodeAssignmentBank.OWNER_TABLE_NAME = 'HZ_PARTIES' and HzCodeAssignmentBank.OWNER_TABLE_ID = HzPartyBank.PARTY_ID and HzRelationshipBR.SUBJECT_ID = HzPartyBranch.PARTY_ID and HzRelationshipBR.OBJECT_ID = HzPartyBank.PARTY_ID and HzRelationshipBR.RELATIONSHIP_CODE = 'BRANCH_OF' and HzRelationshipBR.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH' and HzRelationshipBR.SUBJECT_TABLE_NAME = 'HZ_PARTIES' and HzRelationshipBR.SUBJECT_TYPE = 'ORGANIZATION' and HzRelationshipBR.OBJECT_TABLE_NAME = 'HZ_PARTIES' and HzRelationshipBR.OBJECT_TYPE = 'ORGANIZATION' AND E.LOOKUP_TYPE = 'JLBR_AR_BORDERO_STATUS' AND E.LOOKUP_CODE = A.COLL_BATCH_STATUS AND NVL(E.START_DATE_ACTIVE,trunc(SYSDATE)) <= trunc(SYSDATE) AND NVL(E.END_DATE_ACTIVE,trunc(SYSDATE)) >= trunc(SYSDATE) AND E.ENABLED_FLAG = 'Y' AND J.LOOKUP_TYPE = 'JLBR_AR_REMITTANCE_MEDIA' AND J.LOOKUP_CODE = A.OUTPUT_FORMAT AND NVL(J.START_DATE_ACTIVE,trunc(SYSDATE)) <= trunc(SYSDATE) AND NVL(J.END_DATE_ACTIVE,trunc(SYSDATE))>= trunc(SYSDATE) AND J.ENABLED_FLAG = 'Y'

SQL_Statement

SELECT A.COLL_BATCH_ID , A.SELECTION_CONTROL_ID , A.BANK_ACCT_USE_ID , A.COLL_BATCH_STATUS , A.SEQUENTIAL_NUMBER_GENERATION , A.TOTAL_COUNT, A.TOTAL_AMOUNT , A.SELECTION_DATE , A.REMITTANCE_DATE , A.REFUSED_DATE , A.CANCELLATION_DATE , A.COLLECTION_DATE , A.WRITE_OFF_DATE , A.DATE_IN_RECEIPT , A.RECEIVED_DATE , A.OUTPUT_PROGRAM_ID , A.SELECT_ACCOUNT_ID , A.OUTPUT_FORMAT , J.MEANING OUTPUT_FORMAT_DESC , A.FORMAT_DATE, A.ORG_ID , A.LAST_UPDATE_DATE , A.LAST_UPDATED_BY , A.CREATION_DATE , A.CREATED_BY , A.LAST_UPDATE_LOGIN , HzPartyBank.PARTY_NAME BANK_NAME , HzPartyBranch.PARTY_NAME BANK_BRANCH_NAME , HzOrgProfileBranch.BANK_OR_BRANCH_NUMBER BRANCH_NUMBER , HzOrgProfileBank.BANK_OR_BRANCH_NUMBER BANK_NUMBER, HzPartyBank.party_id BANK_PARTY_ID,

E.MEANING COLL_BATCH_STATUS_DESC ,

F.GLOBAL_ATTRIBUTE1 AR_SYS_PARAM_GLOBAL_ATTRIBUTE1 , CeBankAccount.SECONDARY_ACCOUNT_REFERENCE AP_BNK_ACCTS_GLOBAL_ATTRIBUTE7

, CeBankAccount.BANK_ACCOUNT_NUM

,A.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER

FROM JL_BR_AR_COLL_BATCHES A, FND_LOOKUPS E, AR_SYSTEM_PARAMETERS F

, FND_LOOKUPS J , CE_BANK_ACCOUNTS CeBankAccount

, CE_BANK_ACCT_USES_ALL AcctUse, HZ_PARTIES HzPartyBank, HZ_ORGANIZATION_PROFILES HzOrgProfileBank, HZ_ORGANIZATION_PROFILES HzOrgProfileBranch

, HZ_CODE_ASSIGNMENTS HzCodeAssignmentBank , HZ_PARTIES HzPartyBranch, HZ_CODE_ASSIGNMENTS HzCodeAssignmentBranch, HZ_RELATIONSHIPS HzRelationshipBR

WHERE CeBankAccount.BANK_BRANCH_ID = HzPartyBranch.PARTY_ID AND AcctUse.BANK_ACCT_USE_ID = A.BANK_ACCT_USE_ID AND AcctUse.BANK_ACCOUNT_ID = CeBankAccount.BANK_ACCOUNT_ID AND HzPartyBranch.PARTY_TYPE = 'ORGANIZATION' and HzOrgProfileBranch.PARTY_ID = HzPartyBranch.PARTY_ID and trunc(SYSDATE) between TRUNC(HzOrgProfileBranch. effective_start_date) and NVL(TRUNC(HzOrgProfileBranch.effective_end_date), trunc(SYSDATE)+1) and HzCodeAssignmentBranch.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' and HzCodeAssignmentBranch.CLASS_CODE = 'BANK_BRANCH' and HzCodeAssignmentBranch.OWNER_TABLE_NAME = 'HZ_PARTIES' and HzCodeAssignmentBranch.OWNER_TABLE_ID = HzPartyBranch.PARTY_ID and trunc(SYSDATE) between TRUNC(HzCodeAssignmentBranch. start_date_active) and NVL(TRUNC(HzCodeAssignmentBranch.end_date_active), trunc(SYSDATE)+1) and HzPartyBank.PARTY_TYPE = 'ORGANIZATION' and HzPartyBank.status = 'A' and HzOrgProfileBank.PARTY_ID = HzPartyBank.PARTY_ID and trunc(SYSDATE) between TRUNC(HzOrgProfileBank.effective_start_date) and NVL(TRUNC(HzOrgProfileBank.effective_end_date), trunc(SYSDATE)+1) and trunc(SYSDATE) between TRUNC(HzCodeAssignmentBank. start_date_active) and NVL(TRUNC(HzCodeAssignmentBank.end_date_active), trunc(SYSDATE)+1) and HzCodeAssignmentBank.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE' and HzCodeAssignmentBank.CLASS_CODE = 'BANK' and HzCodeAssignmentBank.OWNER_TABLE_NAME = 'HZ_PARTIES' and HzCodeAssignmentBank.OWNER_TABLE_ID = HzPartyBank.PARTY_ID and HzRelationshipBR.SUBJECT_ID = HzPartyBranch.PARTY_ID and HzRelationshipBR.OBJECT_ID = HzPartyBank.PARTY_ID and HzRelationshipBR.RELATIONSHIP_CODE = 'BRANCH_OF' and HzRelationshipBR.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH' and HzRelationshipBR.SUBJECT_TABLE_NAME = 'HZ_PARTIES' and HzRelationshipBR.SUBJECT_TYPE = 'ORGANIZATION' and HzRelationshipBR.OBJECT_TABLE_NAME = 'HZ_PARTIES' and HzRelationshipBR.OBJECT_TYPE = 'ORGANIZATION'

AND E.LOOKUP_TYPE = 'JLBR_AR_BORDERO_STATUS'

AND E.LOOKUP_CODE = A.COLL_BATCH_STATUS AND NVL(E.START_DATE_ACTIVE,trunc(SYSDATE)) <= trunc(SYSDATE) AND NVL(E.END_DATE_ACTIVE,trunc(SYSDATE)) >= trunc(SYSDATE) AND E.ENABLED_FLAG = 'Y'

AND J.LOOKUP_TYPE = 'JLBR_AR_REMITTANCE_MEDIA' AND J.LOOKUP_CODE = A.OUTPUT_FORMAT AND NVL(J.START_DATE_ACTIVE,trunc(SYSDATE)) <= trunc(SYSDATE) AND NVL(J.END_DATE_ACTIVE,trunc(SYSDATE))>= trunc(SYSDATE) AND J.ENABLED_FLAG = 'Y'