SELECT
hou.name operating_unit,
xep.name legal_entity,
ala.lockbox_number “NUMBER”,
absa.name batch_source,
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.
–ceba.bank_account_name,
ceba.bank_account_num bank_account,
–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,
ala.batch_size,
decode(ala.gl_date_source,’DEP’,’Deposit Date’,’CON’,’Constant Date’,’IMP’,’Import Date’) gl_date_source,
ala.exchange_rate_type,
ARM.NAME RECEIPT_METHOD,
ala.require_billing_location_flag,
(select meaning from apps.ar_lookups where lookup_type = ‘ARLPLB_MATCHING_OPTION’ and lookup_code = ala.lockbox_matching_option) match_receipts_by,
ala.use_matching_date,
ala.auto_associate,
decode(ala.allow_invalid_txn_num, ‘Y’,’Post Partial Amount As Unapplied’,’Reject Entire Receipt’) invalid_txn_num_handling,
decode(ala.line_level_cash_app_rule, ‘N’,’None’,’C’,’Custom’) line_level_cash_app
FROM
apps.cefv_bank_branches cebb,
apps.ce_bank_accounts ceba,
apps.ce_bank_acct_uses_all cebau,
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.AR_RECEIPT_METHODS ARM,
apps.ar_batch_sources_all absa,
apps.ar_lockboxes_all ala
WHERE
1 = 1
AND cebb.bank_branch_id = ceba.bank_branch_id
AND ceba.bank_account_id = cebau.bank_account_id
AND ala.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 absa.REMIT_BANK_ACCT_USE_ID = cebau.BANK_ACCT_USE_ID
and absa.batch_source_id = ala.batch_source_id
AND ala.receipt_method_id = ARM.RECEIPT_METHOD_ID
and ala.lockbox_number in (
‘0003527’,
‘0021089’,
‘0027128’,
‘0601002’,
‘0601018’,
‘0915004’
);
Recommended Posts
