VIEW: APPS.JL_BR_AR_BORDEROS_V

Source

View Text - Preformatted

SELECT A.BORDERO_ID , A.SELECTION_CONTROL_ID , A.BANK_ACCT_USE_ID , A.BORDERO_STATUS , A.SEQUENTIAL_NUMBER_GENERATION , A.BORDERO_TYPE , 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.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, D.MEANING BORDERO_TYPE_DESC , E.MEANING BORDERO_STATUS_DESC , F.GLOBAL_ATTRIBUTE1 AR_SYS_PARAM_GLOBAL_ATTRIBUTE1 , CeBankAccount.SECONDARY_ACCOUNT_REFERENCE AP_BNK_ACCTS_GLOBAL_ATTRIBUTE7 , I.PROGRAM_NAME , CeBankAccount.BANK_ACCOUNT_NUM FROM JL_BR_AR_BORDEROS A, FND_LOOKUPS D, FND_LOOKUPS E, AR_SYSTEM_PARAMETERS F, AP_PAYMENT_PROGRAMS I, 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 SYSDATE between TRUNC(HzOrgProfileBranch. effective_start_date) and NVL(TRUNC(HzOrgProfileBranch.effective_end_date), 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 SYSDATE between TRUNC(HzCodeAssignmentBranch. start_date_active) and NVL(TRUNC(HzCodeAssignmentBranch.end_date_active), SYSDATE+1) 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 SYSDATE between TRUNC(HzCodeAssignmentBank. start_date_active) and NVL(TRUNC(HzCodeAssignmentBank.end_date_active), 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 D.LOOKUP_TYPE = 'JLBR_AR_BORDERO_TYPE' AND D.LOOKUP_CODE = A.BORDERO_TYPE AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND D.ENABLED_FLAG = 'Y' AND E.LOOKUP_TYPE = 'JLBR_AR_BORDERO_STATUS' AND E.LOOKUP_CODE = A.BORDERO_STATUS AND NVL(E.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(E.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND E.ENABLED_FLAG = 'Y' AND I.PROGRAM_ID = A.OUTPUT_PROGRAM_ID AND J.LOOKUP_TYPE = 'JLBR_AR_REMITTANCE_MEDIA' AND J.LOOKUP_CODE = A.OUTPUT_FORMAT AND NVL(J.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(J.END_DATE_ACTIVE,SYSDATE)>= SYSDATE AND J.ENABLED_FLAG = 'Y'

View Text - HTML Formatted

SELECT A.BORDERO_ID
, A.SELECTION_CONTROL_ID
, A.BANK_ACCT_USE_ID
, A.BORDERO_STATUS
, A.SEQUENTIAL_NUMBER_GENERATION
, A.BORDERO_TYPE
, 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.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
, D.MEANING BORDERO_TYPE_DESC
, E.MEANING BORDERO_STATUS_DESC
, F.GLOBAL_ATTRIBUTE1 AR_SYS_PARAM_GLOBAL_ATTRIBUTE1
, CEBANKACCOUNT.SECONDARY_ACCOUNT_REFERENCE AP_BNK_ACCTS_GLOBAL_ATTRIBUTE7
, I.PROGRAM_NAME
, CEBANKACCOUNT.BANK_ACCOUNT_NUM
FROM JL_BR_AR_BORDEROS A
, FND_LOOKUPS D
, FND_LOOKUPS E
, AR_SYSTEM_PARAMETERS F
, AP_PAYMENT_PROGRAMS I
, 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 SYSDATE BETWEEN TRUNC(HZORGPROFILEBRANCH. EFFECTIVE_START_DATE)
AND NVL(TRUNC(HZORGPROFILEBRANCH.EFFECTIVE_END_DATE)
, 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 SYSDATE BETWEEN TRUNC(HZCODEASSIGNMENTBRANCH. START_DATE_ACTIVE)
AND NVL(TRUNC(HZCODEASSIGNMENTBRANCH.END_DATE_ACTIVE)
, SYSDATE+1)
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 SYSDATE BETWEEN TRUNC(HZCODEASSIGNMENTBANK. START_DATE_ACTIVE)
AND NVL(TRUNC(HZCODEASSIGNMENTBANK.END_DATE_ACTIVE)
, 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 D.LOOKUP_TYPE = 'JLBR_AR_BORDERO_TYPE'
AND D.LOOKUP_CODE = A.BORDERO_TYPE
AND NVL(D.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(D.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND D.ENABLED_FLAG = 'Y'
AND E.LOOKUP_TYPE = 'JLBR_AR_BORDERO_STATUS'
AND E.LOOKUP_CODE = A.BORDERO_STATUS
AND NVL(E.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(E.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND E.ENABLED_FLAG = 'Y'
AND I.PROGRAM_ID = A.OUTPUT_PROGRAM_ID
AND J.LOOKUP_TYPE = 'JLBR_AR_REMITTANCE_MEDIA'
AND J.LOOKUP_CODE = A.OUTPUT_FORMAT
AND NVL(J.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(J.END_DATE_ACTIVE
, SYSDATE)>= SYSDATE
AND J.ENABLED_FLAG = 'Y'