oracle erp供应链常用信息查询.docxVIP

Cajan.Z PAGE \* MERGEFORMAT PAGE \* MERGEFORMAT 38 ORACLE EBS常用表查询语句 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 用户、责任及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 姓