Query: RA Transaction Type Form Query

This query is for RA Transaction Type form. 
Navigation
Receivables-> Setup-> Transactions-> Transaction Types



 
SELECT haout.name operating_unit,
xep.name legal_entity,
rctt.name,
rctt.description,
tt_class.meaning class,
tt_sign.meaning creation_sign,
tt_its.meaning transaction_status,
tt_ipo.meaning printing_option,
inv_type.name invoice_type,
credit_type.name credit_memo_type,
aars.description application_rule_set,
rtv.name terms,
rctt.start_date,
rctt.end_date,
rctt.accounting_affect_flag open_receivable,
NVL (rctt.adj_post_to_gl, 'N') allow_adjustment_posting,
rctt.post_to_gl,
rctt.allow_freight_flag,
rctt.natural_application_only_flag natural_application_only,
rctt.tax_calculation_flag default_tax_classification,
rctt.exclude_from_late_charges exclude_from_late_charges,
rctt.allow_overapplication_flag,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_rec)
receivable_account,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_freight)
freight_account,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_rev)
revenue_account,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_clearing)
clearning_account,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_unbilled)
unbilled_account,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_unearned)
unearned_account,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_tax)
tax_account,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_unpaid_rec)
unpaid_bills_receivable,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_remittance)
remitted_bill_receivable,
(SELECT concatenated_segments
FROM apps.gl_code_combinations_kfv gcct
WHERE 1 = 1 AND gcct.code_combination_id = rctt.gl_id_factor)
factored_bills_receivable,
rctt.signed_flag,
rctt.drawee_issued_flag,
app.friendly_name format_program_name,
DECODE (rctt.allocate_tax_freight,
'Y', 'Lines, Tax and Freight',
'Lines Only')
allocation_basis
FROM apps.hr_all_organization_units haout,
fnd_lookup_values_vl tt_class,
fnd_lookup_values_vl tt_sign,
fnd_lookup_values_vl tt_its,
fnd_lookup_values_vl tt_ipo,
ra_cust_trx_types_all inv_type,
ra_cust_trx_types_all credit_type,
ra_terms_vl rtv,
xle_entity_profiles xep,
ar_app_rule_sets aars,
ap_payment_programs app,
ra_cust_trx_types_all rctt
WHERE     1 = 1
AND rctt.legal_entity_id = xep.legal_entity_id(+)
AND rctt.format_program_id = app.program_id(+)
AND tt_class.lookup_type IN ('INV/CM')
AND tt_class.lookup_code = rctt.TYPE
AND tt_sign.lookup_type IN ('SIGN')
AND tt_sign.lookup_code = rctt.creation_sign
AND tt_its.lookup_type IN ('INVOICE_TRX_STATUS')
AND tt_its.lookup_code = rctt.default_status
AND tt_ipo.lookup_type IN ('INVOICE_PRINT_OPTIONS')
AND tt_ipo.lookup_code = rctt.default_printing_option
AND rctt.subsequent_trx_type_id = inv_type.cust_trx_type_id(+)
AND rctt.org_id = inv_type.org_id(+)
AND rctt.credit_memo_type_id = credit_type.cust_trx_type_id(+)
AND rctt.org_id = credit_type.org_id(+)
AND rctt.default_term = rtv.term_id(+)
AND rctt.rule_set_id = aars.rule_set_id(+)
AND rctt.org_id = haout.organization_id
ORDER BY 1;