【正文】 ORACLE EBS常用表查詢語句1. OU、庫存組織SELECT ou_org_id, org_id ou_name, ou名稱 org_org_id, 庫存組織id org_org_code, 庫存組織代碼 , 子庫存名稱 子庫存描述 FROM hr_organization_information hoi, 組織分類表 hr_operating_units hou, ou視圖 org_organization_definitions ood, 庫存組織定義視圖 mtl_secondary_inventories msi 子庫存信息表 WHERE = 39。OPERATING_UNIT39。 AND = AND = AND = 獲取系統(tǒng)IDcall (1318,50583,401)select (39。ORG_ID39。) FROM DUALselect * from hr_operating_units hou where =2042. 用戶、責(zé)任及HR系統(tǒng)責(zé)任定義VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY)SELECT APPLICATION_ID, RESPONSIBILITY_ID, RESPONSIBILITY_KEY, END_DATE, RESPONSIBILITY_NAME, DESCRIPTION FROM FND_RESPONSIBILITY_VL。用戶責(zé)任關(guān)系SELECT USER_ID, RESPONSIBILITY_ID FROM FND_USER_RESP_GROUPS。用戶表SELECT USER_ID, USER_NAME, EMPLOYEE_ID, PERSON_PARTY_ID, END_DATE FROM FND_USER。人員表VIEWSELECT PERSON_ID, START_DATE, DATE_OF_BIRTH, EMPLOYEE_NUMBER, NATIONAL_IDENTIFIER, SEX, FULL_NAME FROM per_people_f。綜合查詢SELECT USER_NAME, FULL_NAME, RESPONSIBILITY_NAME, FROM FND_USER AA, FND_USER_RESP_GROUPS BB, FND_RESPONSIBILITY_VL CC, per_people_f DD WHERE = AND = AND = AND RESPONSIBILITY_NAME like 39。%供應(yīng)處%39。 ORDER BY USER_NAME。綜合查詢?nèi)藛T狀況基本信息表SELECT 系統(tǒng)ID, 姓名, 出生日期, 出生地區(qū), 身份證號, 招工來源, 員工類型, 集團合同號, 參加工作日期, 省份, DECODE(,39。M39。,39。男39。,39。F39。,39。女39。,39。NULL39。) 性別, decode 適合和同一值做比較有多種結(jié)果，不適合和多種值比較有多種結(jié)果 CASE WHEN 39。M39。 THEN 39。男39。 WHEN 39。F39。 THEN 39。女39。 ELSE 39。NULL39。 END 性別1, case 用法一 CASE WHEN TO_CHAR(, 39。YYYY39。) 39。196039。 THEN 39。50年代39。 WHEN TO_CHAR(, 39。YYYY39。) 39。197039。 THEN 39。60年代39。 WHEN TO_CHAR(, 39。YYYY39。) 39。198039。 THEN 39。70年代39。 WHEN TO_CHAR(, 39。YYYY39。) 39。199039。 THEN 39。80年代39。 WHEN TO_CHAR(, 39。YYYY39。) 39。200039。 THEN 39。90年代39。 ELSE 39。21世紀(jì)39。 case 用法二 END 出生年代FROM PER_ALL_PEOPLE_F PAF3. 供應(yīng)商VENDOR供應(yīng)商主表數(shù)據(jù)：SELECT vendor_id, party_id, vendor_code, vendor_name, vendor_short_name, vendor_type, vendor_type_meaning, tax_registered_name, payment_method, term_name, enabled_flag, end_date_active, creation_date, created_by, last_update_date, last_updated_by, last_update_login FROM ap_suppliers ass, fnd_lookup_values flv, hz_parties hp, ap_terms_tl att WHERE = (+) AND (+) = 39。VENDOR TYPE39。 AND (+) = userenv(39。LANG39。) AND = AND = userenv(39。LANG39。) AND = (+)供應(yīng)商銀行信息SELECT vendor_id, party_id, bank_id, bank_name, branch_id, bank_branch_name, bank_account_num FROM ap_suppliers ass, hz_parties hp, iby_account_owners iao, iby_ext_bank_accounts ieba, hz_parties bank, hz_parties branch WHERE = AND = (+) AND = (+) AND = (+) AND = (+) ORDER BY 。供應(yīng)商開戶行地址信息SELECT party_id, party_site_id, location_id, country, province, city, address1, address2, address3, address4 FROM hz_party_sites hps, hz_locations hl WHERE = ORDER BY 供應(yīng)商聯(lián)系人信息SELECT subject_id, object_id, party_id, || 39。 39。 || || 39。 39。 || contact_person, phone_area_code, phone_number, phone_extension, fax_phone_area_code, fax_phone_number, _address FROM hz_relationships hr, hz_contact_points hcpp, hz_contact_points hcpf, hz_contact_points hcpe, hz_parties hp WHERE = AND (+) = AND (+) = AND (+) = AND = 39。PERSON39。 AND (+) = 39。CONTACT39。 AND (+) = 39。HZ_PARTIES39。 AND (+) = 39。HZ_PARTIES39。 AND (+) = 39。HZ_PARTIES39。 AND (+) = 39。PHONE39。 AND (+) = 39。GEN39。 AND (+) = 39。PHONE39。 AND (+) = 39。FAX39。 AND (+) = 39。EMAIL39。 AND IS NULL ORDER BY 。供應(yīng)商地址主信息SELECT vendor_site_id, vendor_id, vendor_code, address_short_name, address_line1, address_line2, address_line3, address_line4, org_id, country, province, city, county, zip, pay_site_flag, purchasing_site_flag, inactive_date, creation_date, created_by, last_update_date, last_updated_by, last_update_login FROM ap_suppliers ass, ap_supplier_sites_all assa WHERE = 。供應(yīng)商地址聯(lián)系人信息：phone、fax和EmailSELECT phone_area_code, phone_number, phone_extension, fax_phone_area_code, fax_phone_number, _address FROM ap_supplier_sites_all assa, hz_contact_points hcpp, hz_contact_points h