Query to get the 11i Bank Accounts Setup Data

SELECT ABAA.SET_OF_BOOKS_ID,ABAA.ORG_ID,ABAA.CURRENCY_CODE,ABAA.MULTI_CURRENCY_FLAG,abaa.bank_account_id,abAa.bank_accounT_NAME,ABAA.BANK_ACCOUNT_NUM,
ABAA.CONTACT_FIRST_NAME AS BANKACC_CNCT_FIRST,
ABAA.CONTACT_MIDDLE_NAME  AS BANKACCT_CNCT_MID,ABAA.CONTACT_LAST_NAME AS BANKACC_CNCT_LAST,
ABAA.CONTACT_PREFIX,ABAA.CONTACT_TITLE,ABAA.INACTIVE_DATE,
ABAA.DESCRIPTION AS BANKACC_DESCRIPTION,ABAA.BANK_BRANCH_ID,ABAA.BANK_ACCOUNT_TYPE,ABAA.VALIDATION_NUMBER,ABAA.ACCOUNT_TYPE,ABAA.POOLED_FLAG,ABAA.ZERO_AMOUNTS_ALLOWED,
ABAA.RECEIPT_MULTI_CURRENCY_FLAG,ABAA.ALLOW_MULTI_ASSIGNMENTS_FLAG,ABAA.IBAN_NUMBER,
ABAA.ASSET_CODE_COMBINATION_ID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.ASSET_CODE_COMBINATION_ID) as ASSET_CODE_COMBINATION,
ABAA.GAIN_CODE_COMBINATION_ID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.GAIN_CODE_COMBINATION_ID) as GAIN_CODE_COMBINATION,
ABAA.LOSS_CODE_COMBINATION_ID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.LOSS_CODE_COMBINATION_ID) as LOSS_CODE_COMBINATION,
ABAA.CASH_CLEARING_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.CASH_CLEARING_CCID) as CASH_CLEARING,
ABAA.BANK_CHARGES_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.BANK_CHARGES_CCID) as BANK_CHARGES,
ABAA.BANK_ERRORS_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.BANK_ERRORS_CCID) as BANK_ERRORS,
ABAA.EARNED_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.EARNED_CCID) as EARNED,
ABAA.UNEARNED_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.UNEARNED_CCID) as UNEARNED,
ABAA.ON_ACCOUNT_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.ON_ACCOUNT_CCID) as ON_ACCOUNT,
ABAA.UNAPPLIED_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.UNAPPLIED_CCID) as UNAPPLIED,
ABAA.UNIDENTIFIED_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.UNIDENTIFIED_CCID) as UNIDENTIFIED,
ABAA.FACTOR_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.FACTOR_CCID) as FACTOR,
ABAA.RECEIPT_CLEARING_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.RECEIPT_CLEARING_CCID) as RECEIPT_CLEARING,
ABAA.REMITTANCE_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.REMITTANCE_CCID) as REMITTANCE,
ABAA.EDISC_RECEIVABLES_TRX_ID,
( select name from AR_RECEIVABLES_TRX_ALL where receivables_trx_id =  ABAA.EDISC_RECEIVABLES_TRX_ID) as EDISC_RECEIVABLES_TRX,
ABAA.UNEDISC_RECEIVABLES_TRX_ID,
( select name from AR_RECEIVABLES_TRX_ALL where receivables_trx_id =  ABAA.UNEDISC_RECEIVABLES_TRX_ID) as UNEDISC_RECEIVABLES_TRX,
ABAA.FUTURE_DATED_PAYMENT_CCID,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ABAA.FUTURE_DATED_PAYMENT_CCID) as FUTURE_DATED_PAYMENT,
abb.bank_name,abb.bank_name_alt,abb.bank_branch_name,abb.bank_branch_name_alt,abb.description as bank_description,
abb.address_line1,abb.address_line2,abb.city,abb.state,abb.zip,abb.country,abb.institution_type
--acsa.name as check_name,acsa.banK_account_id,acsa.disbursement_type_lookup_code,acsa.check_format_id,acsa.last_document_num,acsa.last_available_document_num,acsa.num_setup_checks,
--acsa.inactive_date,--acsa,org_id,
--acsa.bank_charges_ccid,acsa.bank_errors_ccid,acsa.clearing_days,acsa.future_dated_payments_flag,acsa.cash_clearing_ccid--,acsa,transmission_code
FROM ap_bank_accounts_all abaa, ap_bank_branches abb
--,ap_check_stocks_all acsa,
  --ap_check_formats acf
 WHERE abaa.bank_branch_id = abb.bank_branch_id
-- AND abaa.BANK_ACCOUNT_ID=acsa.BANK_ACCOUNT_ID
 --AND acsa.CHECK_FORMAT_ID=acf.CHECK_FORMAT_ID
 --AND acsa.INACTIVE_DATE is null
   AND abb.end_date IS NULL
   AND abaa.account_type = 'INTERNAL'

Query to get the Transaction type Setup information from 11i system with GL code combinations

select a.set_of_books_id,org_id,
a.cust_trx_type_id,a.name, description,type,
a.credit_memo_type_id ,
a.post_to_gl,a.accounting_affect_flag,
a.credit_memo_type_id as Open_receivables,
status,default_printing_option,default_status,
(select distinct name from ra_cust_trx_types_all where cust_trx_type_id = a.credit_memo_type_id) as credit_memo_type,
( select name from ra_terms where term_id = a.default_term),
 a.gl_id_rev,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  a.gl_id_rev) as revenue,
gl_id_freight,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  a.gl_id_freight) as freight,
gl_id_rec,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  a.gl_id_rec) as receivables,
gl_id_clearing,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  a.gl_id_clearing) as clearing,
gl_id_unbilled,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  a.gl_id_unbilled) as Unbilled,
gl_id_tax,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  a.gl_id_tax) as tax,
gl_id_unearned,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  a.gl_id_unearned) as unearned,
allow_freight_flag,allow_overapplication_flag,creation_sign,tax_calculation_flag,natural_application_only_flag
---,exclude_from_late_charges,
---adj_post_to_gl
from ra_cust_trx_types_all a;

Query to get the Receipt Methods in 11i with Bank account details including GL Code Combinations

SELECT arma.org_id,ARC.NAME RECEIPT_CLASS,ARC.receipt_class_ID,ARC.CREATION_METHOD_CODE,
ARC.REMIT_FLAG,ARC.CREATION_STATUS,
ARM.NAME RECEIPT_M,
ARM.END_DATE as receipt_method_end_date,
ARM.RECEIPT_METHOD_ID,
ARMA.ORG_ID,
arma.earned_ccid,
arma.end_date as recipt_bankacc_end_date,
arma.primary_flag,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.earned_ccid) as earned_disc,
arma.edisc_receivables_trx_id,
(SELECT NAME FROM AR_RECEIVABLES_TRX_ALL WHERE receivables_trx_id=ARMA.edisc_receivables_trx_id) as edisc_receivables_activ,
arma.unedisc_receivables_trx_id,
(SELECT NAME FROM AR_RECEIVABLES_TRX_ALL WHERE receivables_trx_id=ARMA.unedisc_receivables_trx_id) as unedisc_receivables_activ,
arma.unearned_ccid,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.unearned_ccid) as unearned_disc,
arma.on_account_ccid,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.on_account_ccid) as on_account,
arma.unapplied_ccid,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.unapplied_ccid) as unapplied,
arma.unidentified_ccid,
( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.unidentified_ccid) as Unidentified,
 arma.bank_charges_ccid,
 ( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.bank_charges_ccid) as bank_charges,
 arma.receipt_clearing_ccid,
 ( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.receipt_clearing_ccid) as receipt_clearing,
 arma.remittance_ccid,
  ( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.remittance_ccid) as remittance,
 ARMA.CASH_CCID,
 ( select concatenated_segments from gl_code_combinations_kfv where code_combination_id =  ARMA.CASH_CCID) as cash,
 br.BANK_NAME,
br.BANK_BRANCH_NAME,APB.BANK_ACCOUNT_NAME,apb.BANK_ACCOUNT_NUM,
apb.CURRENCY_CODE
FROM AR_RECEIPT_METHODS ARM,AR_RECEIPT_CLASSES ARC,AR_RECEIPT_METHOD_ACCOUNTS_ALL ARMA,
AP_BANK_ACCOUNTS_ALL APB,ap_bank_branches br
WHERE  ARM.RECEIPT_METHOD_ID=ARMA.RECEIPT_METHOD_ID
AND ARC.receipt_class_ID=ARM.receipt_class_ID
AND APB.BANK_ACCOUNT_ID=ARMA.BANK_ACCOUNT_ID
and br.bank_branch_id=apb.bank_branch_id

Query to get the 11i Asset categories setup information with GL Code Combinations,Salvage value and depreciation rate

SELECT fcb.book_type_code, fc.segment1, fc.segment2, FC.DESCRIPTION,FC.ENABLED_FLAG,fc.category_type,
       fc.owned_leased,
       (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.ASSET_CLEARING_ACCOUNT_CCID) asset_clearing_cost,
        (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.ASSET_COST_ACCOUNT_CCID ) asset_cost,
         DEPRN_RESERVE_ACCT,
          fcb.DEPRN_EXPENSE_ACCT,
          (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.RESERVE_ACCOUNT_CCID) ACCUM_DEPRN,
  fcb.BONUS_DEPRN_EXPENSE_ACCT,
          BONUS_DEPRN_RESERVE_ACCT ,
          (select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.BONUS_RESERVE_ACCT_CCID) BONUS_RESERVE_ACCT,
          fcb.CIP_COST_ACCT  ,
(select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.WIP_COST_ACCOUNT_CCID) CIP_cost,
          fcb.CIP_CLEARING_ACCT ,
(select CONCATENATED_SEGMENTS from gl_code_combinations_kfv where code_combination_id=fcb.WIP_CLEARING_ACCOUNT_CCID) CIP_CLEARING,
  fcbd.DEPRN_METHOD,
          fcbd.BASIC_RATE ,
          fcbd.ADJUSTED_RATE ,
 FCBD.LIFE_IN_MONTHS ,
 FCBD.PRORATE_CONVENTION_CODE,
 FCBD.RETIREMENT_PRORATE_CONVENTION,
 FCBD.PERCENT_SALVAGE_VALUE
  FROM fa_category_books fcb, fa_categories fc,fa_category_book_defaults fcbd
 WHERE  fcb.category_id = fc.category_id
 AND fcb.CATEGORY_ID=fcbd.CATEGORY_ID
 AND fcb.BOOK_TYPE_CODE=fcbd.BOOK_TYPE_CODE
--- AND Fcb.book_type_code =
  --AND fc.CATEGORY_ID=1