以下是项目中将EBS中的数据导出，供其他系统使用。
注意：取出的数据都是 中文 环境下的数据。
1.组织主数据：
使用的表：hr_all_organization_units、hr_locations_all 和 hr_locations_all_tl
查询的sql：
- SELECT hou.organization_id,
- hou.name,
- la.location_code,
- la.country,
- la.address_line_1,
- la.address_line_2,
- hou.date_from,
- hou.date_to,
- hou.attribute1,
- hou.attribute2,
- hou.attribute3,
- hou.attribute4,
- hou.attribute5,
- hou.last_update_date,
- hou.last_updated_by,
- hou.last_update_login,
- hou.created_by,
- hou.creation_date
- FROM hr_locations_all la,
- hr_all_organization_units hou,
- hr_locations_all_tl lat
- WHERE hou.location_id = la.location_id(+)
- AND la.location_id = lat.location_id(+)
- AND lat.language(+) = 'ZHS';
2. 组织分类主数据：
使用到的表：hr_organization_information和fnd_lookup_values
查询的sql：
- SELECT info.org_information_id,
- info.organization_id,
- info.org_information1,
- flv.meaning org_information_meaning,
- info.org_information2,
- info.last_update_date,
- info.last_updated_by,
- info.last_update_login,
- info.created_by,
- info.creation_date
- FROM hr_organization_information info, fnd_lookup_values flv
- WHERE info.org_information1 = flv.lookup_code
- AND info.org_information_context = 'CLASS'
- AND flv.lookup_type = 'ORG_CLASS'
- AND flv.language = 'ZHS';
3. 单位主数据：
使用到的表：mtl_units_of_measure_tl
使用到的sql：
- SELECT uom.unit_of_measure ,
- uom.uom_code,
- uom.uom_class,
- uom.base_uom_flag,
- uom.unit_of_measure_tl,
- uom.disable_date,
- uom.description,
- uom.last_update_date,
- uom.last_updated_by,
- uom.created_by,
- uom.creation_date,
- uom.last_update_login
- FROM mtl_units_of_measure_tl uom
- WHERE uom.language = 'ZHS';
4. 单位换算关系：
使用到的表： mtl_uom_conversions
使用到的sql：
- SELECT cov.uom_code uom_code,
- cov.inventory_item_id inventory_item_id,
- cov.unit_of_measure unit_of_measure,
- cov.uom_class uom_class,
- base_uom.uom_code base_uom_code,
- base_uom.unit_of_measure base_unit_of_measure,
- cov.conversion_rate conversion_rate,
- cov.disable_date disable_date,
- cov.last_update_date last_update_date,
- cov.last_updated_by last_updated_by,
- cov.created_by created_by,
- cov.creation_date creation_date,
- cov.last_update_login last_update_login
- FROM mtl_uom_conversions cov,
- (SELECT uom.uom_class, uom.uom_code, uom.unit_of_measure
- FROM mtl_units_of_measure_tl uom
- WHERE uom.language = 'ZHS'
- AND uom.base_uom_flag = 'Y'
- GROUP BY uom.uom_class,
- uom.uom_code,
- uom.unit_of_measure,
- uom.base_uom_flag) base_uom
- WHERE cov.uom_class = base_uom.uom_class;
5. 销售员主数据：
使用到的表：jtf_rs_resource_extns、jtf_rs_resource_extns_tl、jtf_objects_b、jtf_objects_tl 和 fnd_application_vl
使用到的sql：
- SELECT rs.resource_id resource_id,
- rs.category category_code,
- obt.name category_meaning,
- rs.resource_number resource_number,
- rst.resource_name resource_name,
- rs.source_id source_id,
- rs.start_date_active start_date_active,
- rs.end_date_active end_date_active,
- rs.last_update_date last_update_date,
- rs.last_updated_by last_updated_by,
- rs.created_by created_by,
- rs.creation_date creation_date,
- rs.last_update_login last_update_login
- FROM jtf_rs_resource_extns rs,
- jtf_rs_resource_extns_tl rst,
- jtf_objects_b ob,
- jtf_objects_tl obt,
- fnd_application_vl fav
- WHERE rs.resource_id = rst.resource_id
- AND rst.language = 'ZHS'
- AND ob.object_code = obt.object_code
- AND rs.category = ob.object_code
- AND obt.language = 'ZHS'
- AND fav.APPLICATION_ID = ob.application_id(+)
- AND fav.APPLICATION_SHORT_NAME = 'JTF'
- AND rs.category = 'EMPLOYEE';
6. 采购员主数据：
使用到的表：po_agents、hr_employees、po_ship_to_loc_org_v 和 mtl_categories_kfv
使用到的sql：
- SELECT pa.agent_id buyer_id,
- pa.category_id category_id,
- pa.location_id location_id,
- he.full_name buyer_name,
- ca.concatenated_segments categoay_name,
- psv.LOCATION_CODE location_code,
- pa.start_date_active start_date_active,
- pa.end_date_active end_date_active,
- pa.last_update_date last_update_date,
- pa.last_updated_by last_updated_by,
- pa.created_by created_by,
- pa.creation_date creation_date,
- pa.last_update_login last_update_login
- FROM po_agents pa,
- hr_employees he,
- po_ship_to_loc_org_v psv,
- mtl_categories_kfv ca
- WHERE pa.agent_id = he.employee_id
- AND pa.location_id = psv.LOCATION_ID(+)
- AND PA.CATEGORY_ID = ca.category_id(+);
7. 国家(地区)主数据：
使用到的表：fnd_territories_tl 和 fnd_territories
使用到的sql：
- SELECT b.territory_code territory_code,
- t.territory_short_name territory_short_name,
- t.description description,
- b.alternate_territory_code alternate_territory_code,
- b.address_style address_style,
- b.last_update_date last_update_date,
- b.last_updated_by last_updated_by,
- b.creation_date creation_date,
- b.created_by created_by,
- b.last_update_login last_update_login
- FROM fnd_territories_tl t, fnd_territories b
- WHERE B.TERRITORY_CODE = T.TERRITORY_CODE
- AND t.language = 'ZHS';
8. 员工主数据：
使用到的表：per_all_people_f
使用到的sql：
- SELECT per.person_id person_id, -- 员工id
- per.effective_start_date effective_start_date, --任职日期自
- per.effective_end_date effective_end_date, --任职日期至
- per.last_name last_name, --员工姓名
- per.employee_number employee_number, --员工编号
- DECODE(per.sex, 'M', '男', 'F', '女', NULL) gender, --性别
- per.work_telephone work_telephone, --工作电话
- per.email_address email_address, --email地址
- per.last_update_date last_update_date,
- per.last_updated_by last_updated_by,
- per.created_by created_by,
- per.creation_date creation_date,
- per.last_update_login
- FROM per_all_people_f per
9. 员工分配主数据：
使用到的表：per_all_assignments_f、per_jobs、per_jobs_tl、per_all_positions 和 hr_all_positions_f_tl
使用到的sql：
- SELECT ass.assignment_id assignment_id, --员工分配id
- ass.effective_start_date effective_start_date, --任职日期自
- ass.effective_end_date effective_end_date, --任职日期至
- ass.person_id person_id, --员工id
- ass.assignment_number assignment_number, --员工分配编号
- ass.job_id job_id, --职务id
- t.name job_name, --职务名称
- ass.position_id position_id, --职位id
- ht.name position_name, --职位名称
- ass.supervisor_id supervisor_id, --上级主管员工id
- ass.organization_id organization_id, --组织id,
- ass.last_update_date last_update_date,
- ass.last_updated_by last_updated_by,
- ass.created_by created_by,
- ass.creation_date creation_date,
- ass.last_update_login last_update_login
- FROM per_all_assignments_f ass, --员工分配表
- per_jobs b, --员工职务
- per_jobs_tl t, --员工职务多语言表
- per_all_positions pb, --员工职位表
- hr_all_positions_f_tl ht --员工职位多语言表
- WHERE ass.job_id = b.job_id(+)
- AND b.job_id = t.job_id(+)
- AND t.language(+) = 'ZHS'
- AND ass.position_id = pb.position_id(+)
- AND pb.position_id = ht.position_id(+)
- AND ht.language(+) = 'ZHS'
