R12 Get Employee and Supervisor Information

SELECT DISTINCT level,e.* FROM ( SELECT DISTINCT papf.person_id, nvl(papf.employee_number, papf.attribute12) employee_number, papf.full_name "EMPLOYEE_FULL_NAME", papf.email_address, paaf.supervisor_id, papf1.employee_number "SUPERVISOR_EMP_NUMBER", papf1.full_name "SUPERVISOR_FULL_NAME" FROM apps.per_all_people_f papf, apps.per_all_assignments_f paaf, apps.per_all_people_f papf1, apps.per_person_types ppt WHERE papf.person_id = paaf.person_id AND papf1.person_id = paaf.supervisor_id AND papf.business_group_id = paaf.business_group_id AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND ppt.person_type_id = papf.person_type_id AND ppt.user_person_type <> 'Ex-employee' ) e CONNECT BY PRIOR person_id = supervisor_id START WITH employee_number = '1234567' ORDER BY employee_number select * from apps.per_all_people_f papf where papf.full_name ='Mr. James Bond'

R12 BOM Get Departments

SELECT mp.organization_code, bdv.department_code, description, disable_date, department_class_code, class_description, location_code, location_description, bdv.attribute1, bdv.attribute2 FROM bom_departments_v bdv, mtl_parameters mp WHERE mp.organization_id = bdv.organization_id AND mp.organization_code IN ( 'ABC','PQR' ) AND nvl(disable_date,(sysdate + 1)) > sysdate

Oracle Apps R12 TCA Customer Information Extract

SELECT hou.name source_ou, hp.party_type, hp.party_name customer_name, hp.known_as, --Account Information -- hca.account_number customer_number, hca.account_name, NULL orig_system_customer_ref, hca.customer_type, hcsu.location, hcsu.site_use_code, hcsub.location bill_to_loc_shipto, hcsu.primary_flag site_primary_flag, hcsu.status site_status, hps.party_site_name, hps.party_site_number, hcsu.primary_flag primary_site_use_flag, NULL orig_system_address_ref, --Site Address Information -- hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.state, hl.postal_code, --hl.country, ftv.territory_short_name, hcsu.gl_id_rec gl_id_rec_acnt_code, hcsu.gl_id_rev gl_id_rev_acnt_code, hcsu.tax_code, hcpc.name customer_profile_class_name, ac.name collector_name, rt.name payment_terms, hps.addressee, hcsu.ship_sets_include_lines_flag, hcsu.arrivalsets_include_lines_flag, hcsu.sched_date_push_flag, hcsu.freight_term, hcsu.fob_point, hcsu.ship_via, hcsu.ship_partial, hcsu.gsa_indicator, hcp.credit_checking, hcp.discount_terms, hcp.dunning_letters, hcp.interest_charges, hcp.send_statements, hcp.credit_balance_statements, hcp.credit_hold, hcp.standard_terms, hcp.interest_period_days, hcp.statement_cycle_id, hcp.tax_printing_option, hcp.auto_rec_incl_disputed_flag FROM hz_parties hp, hz_party_sites hps, hz_cust_accounts_all hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, hz_cust_site_uses_all hcsub, hz_customer_profiles hcp, hz_cust_profile_classes hcpc, hz_locations hl, fnd_territories_vl ftv, ra_terms rt, ar_collectors ac, hr_operating_units hou WHERE hp.party_id = hca.party_id AND hca.cust_account_id = hcas.cust_account_id AND hps.party_site_id = hcas.party_site_id AND hcp.site_use_id (+) = hcsu.site_use_id AND hps.location_id = hl.location_id AND hcsub.site_use_id (+) = hcsu.bill_to_site_use_id AND hp.status = 'A' AND hcas.status = 'A' AND hca.status = 'A' AND hcsu.status = 'A' AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id AND rt.term_id (+) = hcsu.payment_term_id AND hcp.profile_class_id = hcpc.profile_class_id (+) AND hcsu.org_id = hou.organization_id AND ac.collector_id (+) = hcp.collector_id AND ftv.territory_code (+) = hl.country AND hou.name = :P_OU_NAME