HR: Query for HR Assignment Form

This query is for Person information regarding his assignment.  
Navigation
Human Resource-> HR-> People-> Enter and Maintain-> More-> Assignment



  SELECT ppf.full_name,
         pap.name position_name,
         pj.name job_name,
         psf.full_name supervisor_name,
         assignment_number,
         gcct.concatenated_segments,
         pbg.name business_group,
         gsb.name ledger_name
    FROM per_assignments_f paf,
         per_people_f ppf,
         per_jobs pj,
         per_all_positions pap,
         per_people_f psf,
         apps.gl_code_combinations_kfv gcct,
         per_business_groups pbg,
         gl_sets_of_books gsb
   WHERE     1 = 1
         AND ppf.person_id = paf.person_id
         AND pj.job_id = paf.job_id
         AND pap.position_id = paf.position_id
         AND psf.person_id = paf.supervisor_id
         AND paf.default_code_comb_id = gcct.code_combination_id
         AND pbg.business_group_id = paf.business_group_id
         AND paf.effective_end_date > SYSDATE
         AND ppf.effective_end_date > SYSDATE
         AND psf.effective_end_date > SYSDATE
         AND gsb.set_of_books_id = paf.set_of_books_id
ORDER BY 1;

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;