Oracle ERP供应链常用信息查询.docVIP

ORACLE EBS常用表查询语句1.OU、库存组织 SELECT anization_id ou_org_id, --org_id ou_name, --ou名称 anization_id org_org_id, --库存组织id anization_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 _information1 = OPERATING_UNIT AND anization_id = anization_id AND ood.operating_unit = anization_id AND anization_id = anization_id --获取系统ID call fnd_global.APPS_INITIALIZE(1318,50583,401 select fnd_profile.VALUE(ORG_ID FROM DUAL select * from hr_operating_units hou where anization_id=204 2.用户、责任及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; --人员表VIEW SELECT 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 WHENMTHEN男 WHENFTHEN女 ELSENULL END性别1, --case 用法一 CASE WHEN TO_CHAR(PAF.DATE_OF_BIRTH, YYYY 1960THEN50年代 WHEN TO_CHAR(PAF.DATE_OF_BIRTH, YYYY 1970THEN60年代 WHE