Useful Apps Queries

Useful Queries on  Oracle Apps

Contents

1) Query to find Oracle Apps Release
2) Query to find concurrent execution file name
3) Query to get concurrent request details from request id

4) Query to get Customer number from Order number 
5) Query to get Supervisors of an active Employees
6) Query to get Responsibility of a concurrent program
7) Query to get responsibility attached to an user
8) Query to get Request Group associate with Responsibility Name
9) Query to get Menu name for an responsibility
10) Query to find customer Address

Previous                                                                                                    Next

1) Query to find Oracle Apps Release

SELECT release_name FROM fnd_product_groups;
Back to Contents

2)Query to find concurrent execution file name
SELECT fe.executable_name ,fe.execution_file_name ,flu.meaning execution_method ,fcp.concurrent_program_name ,fcpt.user_concurrent_program_name FROM fnd_executables fe ,fnd_concurrent_programs fcp ,fnd_concurrent_programs_tl fcpt ,apps.fnd_lookup_values flu WHERE fe.executable_id = fcp.executable_id AND fcp.concurrent_program_id = fcpt.concurrent_program_id AND flu.lookup_type = 'CP_EXECUTION_METHOD_CODE' AND fe.execution_method_code = flu.lookup_code AND fcpt.LANGUAGE = 'US' AND (fcp.concurrent_program_id =:concurrent_program_id OR fcp.concurrent_program_name =:concurent_program_name OR UPPER (fcpt.user_concurrent_program_name) =UPPER(:user_concurrent_program_name));

Back to Contents

3) Query to get concurrent request details from request id
SELECT DISTINCT fcp.user_concurrent_program_name "User Concurrent Program” ,fcp.concurrent_program_name "Concurrent Program Name" ,fcr.request_id "Request ID" ,fcr.request_date "Request Date" ,flv.meaning status ,fcr.status_code ,fcr.completion_text ,fcr.logfile_name ,fcr.outfile_name ,fcr.argument_text FROM apps.fnd_concurrent_programs_vl fcp ,apps.fnd_concurrent_requests fcr ,apps.fnd_lookup_values flv WHERE fcr.concurrent_program_id = fcp.concurrent_program_id AND TRUNC (fcr.last_update_date) = TRUNC (SYSDATE) AND flv.lookup_code = fcr.status_code AND flv.lookup_type = 'CP_STATUS_CODE' AND flv.LANGUAGE = 'US' AND fcr.request_id = :request_id -- AND fcr.concurrent_program_id=:Concurrent_program_id ORDER BY fcr.request_date ,fcr.request_id DESC;
Back to Contents

4)Query to get Customer number from Order number
  SELECT ooh.order_number "Order Number" ,TO_CHAR (ooh.ordered_date, 'DD-MON-RRRR') "Ordered Date" ,hca.account_number "Customer Number" ,hp.party_name "Customer Name" FROM oe_order_headers_all ooh ,hz_cust_accounts_all hca ,hz_parties hp WHERE ooh.sold_to_org_id = hca.cust_account_id AND hca.party_id = hp.party_id AND ooh.order_number = :order_num; Back to Contents

5) Query To get Supervisors of an active Employees
SELECT ppf.full_name emp_name ,ppf1.full_name supervisor_name FROM per_all_people_f ppf ,per_all_assignments_f paaf ,per_all_people_f ppf1 WHERE 1=1 AND ppf.person_id=paaf.person_id AND ppf.employee_number IS NOT NULL AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.supervisor_id=ppf1.person_id AND UPPER(ppf.FULL_NAME) LIKE UPPER(:emp_name) ORDER BY 1;

Back to Contents

6) Query to get Responsibility of a concurrent program
SELECT fcpt.user_concurrent_program_name "Program Name", frt.responsibility_name "Responsibility Name", frg.request_group_name "Request Group Name", frg.description Description FROM fnd_request_groups frg ,fnd_request_group_units frgu ,fnd_concurrent_programs fcp ,fnd_concurrent_programs_tl fcpt ,fnd_responsibility_tl frt ,fnd_responsibility frs WHERE frgu.unit_application_id = fcp.application_id AND frgu.request_unit_id = fcp.concurrent_program_id AND frg.request_group_id = frgu.request_group_id AND frg.application_id = frgu.application_id AND fcpt.source_lang = USERENV('LANG') AND fcp.application_id = fcpt.application_id AND fcp.concurrent_program_id = fcpt.concurrent_program_id AND frs.application_id = frt.application_id AND frs.responsibility_id = frt.responsibility_id AND frt.source_lang = USERENV('LANG') AND frs.request_group_id = frg.request_group_id AND frs.application_id = frg.application_id AND (fcp.concurrent_program_name =:Program_short_name OR UPPER(fcpt.user_concurrent_program_name) LIKE UPPER(:user_concurrent_program_name));

Back to Contents

7) Query to get responsibility attached to an user
SELECT distinct fu.user_name user_name, frt.responsibility_name responsiblity, fa.application_name application FROM fnd_user fu, fnd_user_resp_groups furg, fnd_application_tl fa, fnd_responsibility_tl frt WHERE furg.user_id(+) = fu.user_id AND furg.responsibility_application_id = fa.application_id AND fa.application_id = frt.application_id AND furg.responsibility_id = frt.responsibility_id AND fu.user_name=upper(:user_name) order by 2;

Back to Contents

8) Query to get Request Group associate with Responsibility Name
SELECT responsibility_name responsibility ,request_group_name ,frg.description FROM fnd_request_groups frg ,fnd_responsibility_vl frv WHERE frv.request_group_id = frg.request_group_id AND UPPER (frv.responsibility_name) LIKE UPPER (:responibility);

Back to Contents

9) Query to get Menu name for an responsibility
SELECT DISTINCT frt.responsibility_name "Responsibility Name", fmt.user_menu_name "User Menu Name" FROM apps.fnd_responsibility_tl frt, apps.fnd_responsibility fr, apps.fnd_menus_tl fmt, apps.fnd_menus fm, apps.fnd_application_tl fat, apps.fnd_application fa WHERE frt.responsibility_id= fr.responsibility_id AND UPPER(frt.responsibility_name) LIKE UPPER(:Responsibility) AND fr.menu_id = fmt.menu_id AND fr.menu_id = fm.menu_id AND fat.application_id = fa.application_id AND fa.application_id = fr.application_id;

Back to Contents

10) Query to find customer Address
SELECT hca.account_number customer_number, hp.party_name customer_name, hps.party_site_number site_number, hl.address1 address1, hl.address2 address2, hl.address3 address3, hl.address4 address4, hl.city city, hl.postal_code postal_code, hl.state state, ftt.territory_short_name country, hcsua1.LOCATION bill_to_location, hcsua2.LOCATION ship_to_location FROM hz_parties hp, hz_party_sites hps, hz_cust_accounts hca, hz_cust_acct_sites_all hcasa1, hz_cust_site_uses_all hcsua1, hz_locations hl, fnd_territories_tl ftt, hz_cust_acct_sites_all hcasa2, hz_cust_site_uses_all hcsua2 WHERE hp.party_id = hps.party_id(+) AND hp.party_id = hca.party_id(+) AND hcasa1.party_site_id(+) = hps.party_site_id AND hcasa2.party_site_id(+) = hps.party_site_id AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id AND hcsua1.site_use_code(+) = 'bill_to' AND hcsua2.site_use_code(+) = 'ship_to' AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id') AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id') AND hps.location_id = hl.location_id AND hl.country = ftt.territory_code AND ftt.LANGUAGE = USERENV ('lang') AND hca.account_number=:customer_number ORDER BY hps.party_site_number;

Back to Contents