AR Transaction types SQL Query 11i

--AR Transactions SQL Query
SELECT
  (SELECT NAME FROM apps.hr_operating_units WHERE organization_id=rct.org_id
  ) ou_name ,
  rct.NAME ,
  (SELECT meaning
  FROM ar_lookups
  WHERE lookup_type='INV/CM'
  AND lookup_code  =rct.TYPE
  ) CLASS ,
  rct.description ,
  rct.post_to_gl ,
  rct.default_printing_option ,
  (SELECT meaning
  FROM ar_lookups
  WHERE lookup_type='INVOICE_TRX_STATUS'
  AND lookup_code  =rct.default_status
  )transaction_status,
  rct.allow_freight_flag ,
  rct.accounting_affect_flag ,
  rct.status ,
  (SELECT NAME FROM ra_terms WHERE term_id=rct.default_term
  ) terms,
  (SELECT NAME
  FROM ra_cust_trx_types_all
  WHERE cust_trx_type_id=rct.subsequent_trx_type_id
  ) invoice_type ,
  -- RCT.SET_OF_BOOKS_ID ,
  rct.allow_overapplication_flag ,
  rct.tax_calculation_flag ,
  rct.creation_sign ,
  (SELECT rule_set_name
  FROM apps.ar_app_rule_sets
  WHERE rule_set_id=rct.rule_set_id
  )application_rule_set,
  (SELECT NAME
  FROM ra_cust_trx_types_all
  WHERE cust_trx_type_id=rct.credit_memo_type_id
  )credit_memo_type ,
  rct.start_date ,
  rct.end_date ,
  rct.allocate_tax_freight,
  rct.signed_flag ,
  rct.drawee_issued_flag ,
  rct.natural_application_only_flag ,
  --RCT.MAGNETIC_FORMAT_CODE ,
  (
  SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_rec
  )receivable_acc,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_rev
  )revenue_account,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_unpaid_rec
  )unapp_receivable_acc ,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_tax
  ) tax_account,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_freight
  )frieght_account,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_clearing
  )clearing_account ,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_unearned
  )unern_revenue_account,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_unbilled
  )unbilled_account ,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_remittance
  )remittance_account ,
  (SELECT gcc.segment1
    ||'-'
    || gcc.segment2
    ||'-'
    || gcc.segment3
    ||'-'
    || gcc.segment4
    ||'-'
    || gcc.segment5
    ||'-'
    || gcc.segment6
    ||'-'
    || gcc.segment7
  FROM gl_code_combinations gcc
  WHERE gcc.code_combination_id=rct.gl_id_factor
  )factor_account
FROM ra_cust_trx_types_all rct
WHERE org_id=:p_org_id
  --AND NAME='EIT Deposit'--'EIT Deposit';
  ;