Oracle EBS核心功能之Manufacturing流程（325楼有整理过的文档下载）

huajhua · 发表于 2006-11-8 14:21

2.11. 其他重要问题
Revision and Version
虽然没有标记版本号，前面的“Modify Operation”因为有有效时间范围进行区分，使Routing在不同时点具有不同的Version（版本）；另一方面，Routing是可以创建Revision（修订号）的，不同Revision的Routing，其版本当然也不同。
所以，总的来说，可以通过这两种方式维护不同的Routing版本。

huajhua · 发表于 2006-11-8 14:21

如何写Routing SQL
Routing的SQL写法相对简单，但也要注意有效日期、类型的限制。下面是取当前时点（Sysdate）Routing的例子，尚缺考虑Revision：
SELECT mst.segment1 assembly_item,
   bor.alternate_routing_designator alternate,
   bor.completion_subinventory,
   bos.operation_seq_num,
   dept.department_code,
   bos.effectivity_date,
   bos.disable_date,
   bres.resource_seq_num,
   res.resource_code,
   bres.basis_type,
   bres.usage_rate_or_amount
  FROM inv.mtl_system_items_b    mst,
   bom.bom_operational_routings bor,
   bom.bom_operation_sequences  bos,
   bom.bom_departments       dept,
   bom.bom_operation_resources  bres,
   bom.bom_resources          res
WHERE mst.organization_id = bor.organization_id
AND mst.inventory_item_id = bor.assembly_item_id

AND bor.routing_sequence_id = bos.routing_sequence_id

AND bos.department_id = dept.department_id

AND bos.operation_sequence_id = bres.operation_sequence_id
AND bres.resource_id = res.resource_id
   --Item
AND mst.bom_enabled_flag = 'Y'
AND mst.bom_item_type IN (1, 2, 3, 4) --Dependent
AND nvl(mst.eam_item_type, 0) = 0
   --Routing Header
AND bor.routing_type = 1 --1 Manufature, 2 ENG
AND nvl(bor.cfm_routing_flag, 2) = 2
   --Operations
AND nvl(bos.disable_date, SYSDATE) >= SYSDATE
AND bos.implementation_date IS NOT NULL
AND nvl(bos.eco_for_production, 2) = 2
   --Filters
AND mst.organization_id = 104
AND mst.segment1 LIKE '960%'
ORDER BY 1, 2 NULLS FIRST, 4, 8;

huajhua · 发表于 2006-11-8 14:22

我们本章练习定义的Routing数据如下：
装配件替代完工库序号部门资源号资源基准使用量

960001 FSG1 10 Assembling 10 HR01 1 0.2
960001 FSG1 10 Assembling 20 MS01 1 0.05
960001 FSG1 20 Packing 10 HR01 1 0.1
960001 FSG1 30 Tesing 10 HR02 1 0.1
960002 10 Wheeling 10 HR01 1 0.2
960002 AAA 10 Wheeling 10 HR03 1 0.1

huajhua · 发表于 2006-11-8 14:22

如何写Resource及其费率 SQL
比较简单，注意成本类型即可：
SELECT res.organization_id,
   res.resource_code,
   res.description,
   lov_cse.meaning cost_element,
   CASE
      WHEN res.cost_element_id IN (3, 4) THEN
      decode(res.functional_currency_flag,
               1,
               'Currency',
               lov_brt.meaning)
      ELSE
      lov_cse.meaning
   END resource_type,
   lov_dbs.meaning default_basis_type,
   crc.resource_rate
  FROM bom.bom_resources       res,
   apps.fnd_lookup_values_vl lov_cse,
   apps.fnd_lookup_values_vl lov_dbs,
   apps.fnd_lookup_values_vl lov_brt,
   bom.cst_resource_costs crc
WHERE res.cost_element_id = lov_cse.lookup_code
AND 'CST_COST_CODE_TYPE' = lov_cse.lookup_type
AND res.default_basis_type = lov_dbs.lookup_code
AND 'CST_BASIS' = lov_dbs.lookup_type
AND res.resource_type = lov_brt.lookup_code
AND 'BOM_RESOURCE_TYPE' = lov_brt.lookup_type
AND res.resource_id = crc.resource_id

AND res.organization_id = 104
AND res.cost_element_id = 3 -- Element
AND crc.cost_type_id = 1 --Cost Type

ORDER BY 1, 2, 3, 4;
我们本章练习定义的数据如下：
组织ID 资源代码描述成本要素资源类型计费基准费率

104 HR01 初级工 Resource Person Item 10
104 HR02 测试工 Resource Person Item 11
104 HR03 特级工 Resource Person Item 20
104 MS01 机器 Resource Machine Item 10

huajhua · 发表于 2006-11-8 14:22

如何写制造费用、关联的资源、费率SQL
实际上，Oralce把各成本子要素的定义都存储在bom_resources表中，所以写SQL的时候需要注意自连接的使用，不要混淆。
制造费用关联的资源：
SELECT owh.organization_id,
   owh.resource_code overhead_code,
   owh.description,
   lov_cse.meaning cost_element,
   lov_dbs.meaning default_basis_type,
   res.resource_code resource_code
  FROM bom.bom_resources       owh,
   apps.fnd_lookup_values_vl  lov_cse,
   apps.fnd_lookup_values_vl  lov_dbs,
   bom.cst_resource_overheads cro,
   bom.bom_resources       res
WHERE owh.cost_element_id = lov_cse.lookup_code
AND 'CST_COST_CODE_TYPE' = lov_cse.lookup_type
AND owh.default_basis_type = lov_dbs.lookup_code
AND 'CST_BASIS' = lov_dbs.lookup_type
AND owh.resource_id = cro.overhead_id
AND cro.resource_id = res.resource_id

AND owh.organization_id = 104
AND owh.cost_element_id = 5 -- Element
AND cro.cost_type_id = 1 --Cost Type
ORDER BY 1, 2;
我们本章练习定义的数据如下：
组织ID 制造费用描述成本要素计费基准资源

104 Coal Overhead Resource Units HR01
104 Coal Overhead Resource Units HR02
104 Coal Overhead Resource Units HR03
104 Water Overhead Resource Units HR01
104 Water Overhead Resource Units MS01
104 Coal Overhead Resource Units HR01
制造费用的部门费率：
SELECT owh.organization_id,
   owh.resource_code overhead_code,
   owh.description,
   lov_cse.meaning cost_element,
   lov_dbs.meaning default_basis_type,
   dept.department_code,
   cdo.rate_or_amount
  FROM bom.bom_resources          owh,
   apps.fnd_lookup_values_vl lov_cse,
   apps.fnd_lookup_values_vl lov_dbs,
   bom.cst_department_overheads cdo,
   bom.bom_departments       dept
WHERE owh.cost_element_id = lov_cse.lookup_code
AND 'CST_COST_CODE_TYPE' = lov_cse.lookup_type
AND owh.default_basis_type = lov_dbs.lookup_code
AND 'CST_BASIS' = lov_dbs.lookup_type
AND owh.resource_id = cdo.overhead_id
AND cdo.department_id = dept.department_id

AND owh.organization_id = 104
AND owh.cost_element_id = 5 -- Element
AND cdo.cost_type_id = 3 --Cost Type
ORDER BY 1, 2, 6;
我们本章练习定义的数据如下：
组织ID 制造费用描述成本要素计费基准部门费率

104 Coal Overhead Resource Units Assembling 1.2
104 Coal Overhead Resource Units Packing 1.1
104 Coal Overhead Resource Units Tesing 1.3
104 Coal Overhead Resource Units Wheeling 1
104 Water Overhead Resource Units Assembling 1
104 Water Overhead Resource Units Packing 1
104 Water Overhead Resource Units Tesing 1
104 Water Overhead Resource Units Wheeling 1