主数据同步涉及到的表

以下是项目中将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'