function CF_huayinFormula return Number is
huayin number;
dep number;
begin
select distinct nvl(wo.department_id,0)
intodep
fromwip_operations wo,mtl_system_items_b msi,wip_discrete_jobs wdj
where wo.organization_id=:Org_id
and wo.wip_entity_id=wdj.wip_entity_id
and wo.organization_id=msi.organization_id
and wdj.primary_item_id=msi.inventory_item_id
and msi.segment1=:Item
and wo.department_id in (5,6,45);
if dep=5 then
select nvl(A.T_quan,0)
intohuayin --please update right dept
from
(select msi.organization_id org,msi.segment1 Item,msi.primary_uom_code Uom,msi.unit_volume Uom_u,--wo.department_id dept,
sum(nvl(To_move.T_quan,0)+nvl(Back_move.T_quan,0))T_quan
from wip_operations wo,mtl_system_items_b msi,wip_discrete_jobs wdj,
(select
wmt.wip_entity_id ,wmt.to_department_id, -1*sum(nvl(wmt.transaction_quantity,0)) T_quan
from wip_move_transactions wmt
where wmt.fm_operation_seq_num>wmt.to_operation_seq_num
and(to_char(wmt.transaction_date,'yyyy/mm/dd') between :Begin_date and:End_date)
and wmt.to_department_id in (5,6,45)--department id
and wmt.organization_id=:Org_id
group by wmt.wip_entity_id,wmt.to_department_id)Back_move,
(select wmt.wip_entity_id ,wmt.fm_department_id ,sum(nvl(wmt.transaction_quantity,0))T_quan
from wip_move_transactions wmt
where wmt.fm_operation_seq_numerror.jpg (10.78 KB, 下载次数: 3)
下载附件
2009-5-18 16:36 上传
|
