Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- https://pastebin.com/pSKvJd8p
- --Internal Bank Account Query
- SELECT
- hou.name operating_unit
- , xep.name legal_entity
- , cebb.bank_name
- , cebb.bank_number
- , cebb.bank_branch_name
- , cebb.bank_branch_number
- , cebb.bank_branch_type
- , — Branch TYPE indicates which list the bank routing NUMBER IS ON.Valid types are ABA
- , CHIPS
- , SWIFT
- , OTHER. cbv.address_line1
- , — Branch details – Branch Address details below LENGTH(cbv.address_line1)address_line1_length
- , cbv.address_line2
- , cbv.address_line3
- , cbv.address_line4
- , cbv.city
- , — Branch City cbv.state
- , — Branch State cbv.zip
- , cbv.country
- , ceba.bank_account_name
- , ceba.bank_account_num
- , cebb.eft_swift_code
- , ceba.iban_number
- , ceba.currency_code
- , ceba.check_digits
- , glcc1.concatenated_segments asset_code_combination
- , glcc2.concatenated_segments cash_clearing_code_combination
- , glcc3.concatenated_segments bank_charges_code_combination
- , cebau.ap_use_enable_flag ap_use_flag
- , cebau.ar_use_enable_flag ar_use_flag
- , cebau.xtr_use_enable_flag treasury_use_flag
- , cebau.pay_use_enable_flag payroll_use_flag
- , ceba.zero_amount_allowed
- , ceba.multi_currency_allowed_flag
- , ac.doc_category_code
- FROM
- apps.cefv_bank_branches cebb
- , apps.ce_bank_accounts ceba
- , apps.ce_bank_acct_uses_all cebau
- , apps.ce_banks_v cbv
- , apps.hr_operating_units hou
- , apps.xle_entity_profiles xep
- , apps.gl_code_combinations_kfv glcc1
- , apps.gl_code_combinations_kfv glcc2
- , apps.gl_code_combinations_kfv glcc3
- , apps.ap_checks_all ac
- WHERE
- 1 = 1
- AND cebb.bank_branch_id = ceba.bank_branch_id
- AND ceba.bank_account_id = cebau.bank_account_id
- AND ceba.bank_id = cbv.bank_party_id
- AND cebau.org_id = hou.organization_id
- AND ceba.account_owner_org_id = xep.legal_entity_id
- AND ceba.asset_code_combination_id = glcc1.code_combination_id( + )
- AND ceba.cash_clearing_ccid = glcc2.code_combination_id( + )
- AND ceba.bank_charges_ccid = glcc3.code_combination_id( + )
- AND ac.ce_bank_acct_use_id(+) = cebau.bank_acct_use_id
- ORDER BY
- 1
- ;
- -- Query to find Bank, Bank Account, and Bank Branches information
- SELECT
- cba.bank_account_name "Bank Account Name"
- , cba.bank_account_num "Bank Account Number"
- , cba.multi_currency_allowed_flag "Multi Currency Flag"
- , cba.zero_amount_allowed "Zero Amount Flag"
- , cba.account_classification "Account Classification"
- , bb.bank_name "Bank Name"
- , bb.bank_branch_type "Bank Branch Type"
- , bb.bank_branch_name "Bank Branch Name"
- , bb.bank_branch_number "Bank Branch Number"
- , bb.eft_swift_code "Swift Code"
- -- ,bb.description "Description"
- , ou.name "Operating Unit"
- , gcf.concatenated_segments "GL Code Combination"
- FROM
- ce_bank_accounts cba
- , ce_bank_acct_uses_all bau
- , cefv_bank_branches bb
- , hr_operating_units ou
- , gl_code_combinations_kfv gcf
- WHERE
- cba.bank_account_id = bau.bank_account_id
- AND cba.bank_branch_id = bb.bank_branch_id
- AND ou.organization_id = bau.org_id
- AND cba.asset_code_combination_id = gcf.code_combination_id
- AND
- (
- cba.end_date IS NULL
- OR cba.end_date > TRUNC(SYSDATE)
- )
- ORDER BY
- TO_NUMBER(cba.bank_account_num)
- ;
- -- Query to list Bank, Bank Branch, Bank Account information Operating Unit wise
- SELECT
- cba.bank_account_name
- , cba.bank_account_id
- , cba.bank_account_name_alt
- , cba.bank_account_num
- , cba.multi_currency_allowed_flag
- , cba.zero_amount_allowed
- , cba.account_classification
- , bb.bank_name
- , cba.bank_id
- , bb.bank_number
- , bb.bank_branch_type
- , bb.bank_branch_name
- , cba.bank_branch_id
- , bb.bank_branch_number
- , bb.eft_swift_code
- , bb.description BANK_DESCRIPTION
- , cba.currency_code
- , bb.address_line1
- , bb.city
- , bb.county
- , bb.state
- , bb.zip_code
- , bb.country
- , ou.name
- , gcf.concatenated_segments
- , cba.ap_use_allowed_flag
- , cba.ar_use_allowed_flag
- , cba.xtr_use_allowed_flag
- , cba.pay_use_allowed_flag
- FROM
- apps.ce_bank_accounts cba
- , apps.ce_bank_acct_uses_all bau
- , apps.cefv_bank_branches bb
- , apps.hr_operating_units ou
- , apps.gl_code_combinations_kfv gcf
- WHERE
- cba.bank_account_id = bau.bank_account_id
- AND cba.bank_branch_id = bb.bank_branch_id
- AND ou.organization_id = bau.org_id
- AND cba.asset_code_combination_id = gcf.code_combination_id
- AND
- (
- cba.end_date IS NULL
- OR cba.end_date > TRUNC(SYSDATE)
- )
- ORDER BY
- TO_NUMBER(cba.bank_account_num)
- ;
- SELECT (SELECT gl_sets_of_books.NAME
- FROM gl_sets_of_books
- WHERE set_of_books_id =
- fnd_doc_sequence_assignments.set_of_books_id)
- gl_sets_of_books,
- (SELECT application_name
- FROM fnd_application_tl fat
- WHERE fat.application_id =
- fnd_doc_sequence_assignments.application_id)
- application,
- (SELECT NAME
- FROM fnd_document_sequences
- WHERE doc_sequence_id =
- fnd_doc_sequence_assignments.doc_sequence_id)
- doc_sequence_name,
- start_date, end_date,
- (SELECT fu.user_name
- FROM fnd_user fu
- WHERE fu.user_id =
- fnd_doc_sequence_assignments.last_updated_by)
- last_updated_by,
- last_update_date,
- (SELECT fu.user_name
- FROM fnd_user fu
- WHERE fu.user_id =
- fnd_doc_sequence_assignments.created_by)
- created_by,
- creation_date, last_update_login, doc_sequence_assignment_id
- FROM fnd_doc_sequence_assignments
- ORDER BY 1, 2, 3
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement