Cajan.Z ORACLE EBS常用表查询语句1. OU、库存组织SELECT hou.organization_id ou_org_id, --org_id hou.name ou_name, --ou名称 ood.organization_id org_org_id, --库存组织id ood.organization_code org_org_code, --库存组织代码 msi.secondary_inventory_name, --子库存名称 msi.description --子库存描述 FROM hr_organization_information hoi, --组织分类表 hr_operating_units hou, --ou视图 org_organization_definitions ood, --库存组织定义视图 mtl_secondary_inventories msi --子库存信息表 WHERE hoi.org_information1 = 'OPERATING_UNIT' AND hoi.organization_id = hou.organization_id AND ood.operating_unit = hoi.organization_id AND ood.organization_id = msi.organization_id--获取系统IDcall fnd_global.APPS_INITIALIZE(1318,50583,401)select fnd_profile.VALUE('ORG_ID') FROM DUALselect * from hr_operating_units hou where hou.organization_id=2042. 用户、责任及HR--系统责任定义VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY)SELECT APPLICATION_ID, RESPONSIBILITY_ID, RESPONSIBILITY_KEY, END_DATE, RESPONSIBILITY_NAME, DESCRIPTION FROM FND_RESPONSIBILITY_VL;--用户责任关系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, CC.DESCRIPTION FROM FND_USER AA, FND_USER_RESP_GROUPS BB, FND_RESPONSIBILITY_VL CC, per_people_f DD WHERE AA.USER_ID = BB.USER_ID AND BB.RESPONSIBILITY_ID = CC.RESPONSIBILITY_ID AND AA.EMPLOYEE_ID = DD.PERSON_ID AND RESPONSIBILITY_NAME like '%供应处%' ORDER BY USER_NAME;--综合查询--人员状况基本信息表SELECT PAF.PERSON_ID 系统ID, PAF.FULL_NAME 姓名, PAF.DATE_OF_BIRTH 出生日期, PAF.REGION_OF_BIRTH 出生地区, PAF.NATIONAL_IDENTIFIER 身份证号, PAF.ATTRIBUTE1 招工来源, PAF.ATTRIBUTE3 员工类型, PAF.ATTRIBUTE11 集团合同号, PAF.original_date_of_hire 参加工作日期, PAF.PER_INFORMATION17 省份, DECODE(PAF.SEX,'M','男','F','女','NULL') 性别, --decode 适合和同一值做比较有多种结果，不适合和多种值比较有多种结果 CASE PAF.SEX WHEN 'M' THEN '男' WHEN 'F' THEN '女' ELSE 'NULL' END 性别1, --case 用法一 CASE WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY') < '1960' THEN '50年代' WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY') < '1970' THEN '60年代' WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY') < '1980' THEN '70年代' WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY') < '1990' THEN '80年代' WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY') < '2000' THEN '90年代' ELSE '21世纪' --case 用法二 END 出生年代FROM PER_ALL_PEOPLE_F PAF3. 供应商VENDOR--供应商主表数据：SELECT ass.vendor_id vendor_id, ass.party_id party_id, ass.segment1 vendor_code, ass.vendor_name vendor_name, ass.vendor_name vendor_short_name, ass.vendor_type_lookup_code vendor_type, flv.meaning vendor_type_meaning, hp.tax_reference tax_registered_name, ass.payment_method_lookup_code payment_method, att.name term_name, att.enabled_flag enabled_flag, att.end_date_active end_date_active, ass.creation_date creation_date, ass.created_by created_by, ass.last_update_date last_update_date, ass.last_updated_by last_updated_by, ass.last_update_login last_update_login FROM ap_suppliers ass, fnd_lookup_values flv, hz_parties hp, ap_terms_tl att WHERE ass.vendor_type_lookup_code = flv.lookup_code(+) AND flv.lookup_type(+) = 'VENDOR TYPE' AND flv.language(+) = userenv('LANG') AND ass.party_id = hp.party_id AND att.language = userenv('LANG') AND ass.terms_id = att.term_id(+)--供应商银行信息SELECT ass.vendor_id vendor_id, ass.party_id party_id, bank.party_id bank_id, bank.party_name bank_name, branch.party_id branch_id, branch.party_name bank_branch_name, ieba.bank_account_num 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 ass.party_id = hp.party_id AND hp.party_id = iao.account_owner_party_id(+) AN。