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

huajhua · 发表于 2006-11-17 11:15

4.11. 其他重要问题
Purge Cost Information
Purge Cost Information请求用来清理无用的成本数据，参数是成本类型、清除内容。
View Standard Cost History
每次执行标准成本更新，系统都会保留一份物品的历史成本（不包含子要素明细），以供日后查看；当然了，用户也可以手工把现行成本考虑到某个成本类型，同样达到备份的作用，而且有子要素明细记录。
Purge Standard Cost Update History
Purge Standard Cost Update History请求用来清理成本更新自动保存的历史数据，需要运行成本更新时所在的库存期间关闭后方可执行。
Cost Mass Edits
成本数据成批编辑：从其它成本类型拷贝、按百分比增加成本、修改成本相关账户、修改成本控制参数（基于卷积、包含在卷集中、批量）。

huajhua · 发表于 2006-11-17 11:15

如何写Item成本SQL
物品成本主要涉及两个表：头表保存汇总过的5大要素成本和总成本，行表保存各子要素的明细成本。
物品成本是分组织屏蔽的，同时也要注意按照成本类型区分。物品成本SQL如下：
SELECT mst.organization_id,
   mst.segment1,
   cct.cost_type,
   cic.item_cost,
   cic.material_cost,
   cic.material_overhead_cost,
   cic.resource_cost,
   cic.outside_processing_cost,
   cic.overhead_cost,
   cic.lot_size,
   cic.based_on_rollup_flag,
   cic.defaulted_flag
  FROM inv.mtl_system_items_b mst,
   bom.cst_item_costs    cic,
   bom.cst_cost_types    cct
WHERE mst.organization_id = cic.organization_id
AND mst.inventory_item_id = cic.inventory_item_id
AND cic.cost_type_id = cct.cost_type_id

   --Filter
AND mst.organization_id = 104
AND mst.segment1 LIKE '9%'
AND cct.cost_type = 'Frozen';
测试数据的Frozen成本如下：
物品编码物品成本原料成本原料间接资源成本外协费用制造费用批量

910001 12.5375 11.25 1.2875 8
910002 1.25 1.25 8
910003 10 10 8
910004 5 5 8
960001 55.6535 36.25 1.2875 9.72 8.396 5
960002 18.8 12.5 3 3.3 5
960003 0 0 0 0 0 0 5
960004 0 0 0 0 0 0 5

huajhua · 发表于 2006-11-17 11:15

物品明细成本SQL如下：
SELECT mst.organization_id,
   mst.segment1,
   cct.cost_type,
   cce.cost_element,
   res1.resource_code sub_element,
   res2.resource_code base_resource,
   lu1.meaning basis,
   lu2.meaning rollup_source_type,
   lu3.meaning level_type,
   cicd.usage_rate_or_amount,
   cicd.basis_factor,
   cicd.net_yield_or_shrinkage_factor,
   cicd.item_cost
  FROM inv.mtl_system_items_b mst,
   bom.cst_item_cost_details cicd,
   bom.cst_cost_types       cct,
   bom.cst_cost_elements    cce,
   mfg_lookups             lu1,
   mfg_lookups             lu2,
   mfg_lookups             lu3,
   bom.bom_resources       res1,
   bom.bom_resources       res2
WHERE mst.organization_id = cicd.organization_id
AND mst.inventory_item_id = cicd.inventory_item_id
AND cicd.cost_type_id = cct.cost_type_id
AND cicd.cost_element_id = cce.cost_element_id
AND lu1.lookup_type = 'CST_BASIS'
AND lu1.lookup_code = cicd.basis_type
AND lu2.lookup_type = 'CST_SOURCE_TYPE'
AND lu2.lookup_code = cicd.rollup_source_type
AND lu3.lookup_type = 'CST_LEVEL'
AND lu3.lookup_code = cicd.level_type
AND cicd.resource_id = res1.resource_id(+)
AND cicd.basis_resource_id = res2.resource_id(+)

   --Filter
AND mst.organization_id = 104
AND mst.segment1 LIKE '9%'
AND cct.cost_type = 'Frozen';
测试数据的Frozen成本如下：
物品成本要素子要素基于资源基准类型卷积来源层次费率基准成本

910001 Material RUBBER Lot User def This 10 0.125 1.25
910001 Material STEEL Item User def This 10 1 10
910001 Material Owh Duty Lot User def This 0.1 0.125 0.0125
910001 Material Owh Purchasing Item User def This 0.1 1 0.1
910001 Material Owh Import1 Res Units User def This 0.1 0 0
910001 Material Owh Import2 Res Value User def This 0.1 0 0
910001 Material Owh Import3 Ttl Value User def This 0.1 11.25 1.125
910001 Material Owh Import4 Activity User def This 0.1 0.5 0.05
910002 Material RUBBER Lot User def This 10 0.125 1.25
910003 Material STEEL Item User def This 10 1 10
910004 Material STEEL Item User def This 5 1 5
960001 Material Item Rolled up Previous 36.25 1 36.25
960001 Material Owh Item Rolled up Previous 1.2875 1 1.2875
960001 Resource HR01 Item Rolled up This 0.2 1 2
960001 Resource MS01 Item Rolled up This 0.05 1 0.5
960001 Resource HR01 Item Rolled up This 0.1 1 1
960001 Resource HR02 Lot Rolled up This 0.1 0.2 0.22
960001 Resource Item Rolled up Previous 6 1 6
960001 Overhead Water HR01 Res Units Rolled up This 1 0.2 0.2
960001 Overhead Water MS01 Res Units Rolled up This 1 0.05 0.05
960001 Overhead Water HR01 Res Units Rolled up This 1 0.1 0.1
960001 Overhead Coal HR02 Res Units Rolled up This 1.3 0.02 0.026
960001 Overhead Coal Item Rolled up This 1.2 1 1.2
960001 Overhead Coal Lot Rolled up This 1.1 0.2 0.22
960001 Overhead Item Rolled up Previous 6.6 1 6.6
960002 Material Item Rolled up Previous 12.5 1 12.5
960002 Resource HR01 Item Rolled up This 0.3 1 3
960002 Overhead Water HR01 Res Units Rolled up This 1 0.3 0.3
960002 Overhead Coal HR01 Res Value Rolled up This 1 3 3