應不包含已保留的部分,以下是本人建的function
create or replace function
gobo_get_available(in_item_id in number,in_from_org_id in number) return number as
--傳入item_id與org_id,返回可用庫存
avai_qty number;
begin
selectnvl(sum(nvl(moq.transaction_quantity,0)),0)
-nvl(sum(nvl(mr.reservation_quantity,0)),0) into avai_qty
frommtl_onhand_quantities moq,
mtl_reservations mr,
mtl_item_revisions
mir,
mtl_secondary_inventories ms
wheremoq.inventory_item_id=nvl(in_item_id,-1)
andmoq.organization_id=nvl(in_from_org_id,-1)
andmr.staged_flag is null
andmr.organization_id=moq.organization_id
andmr.inventory_item_id=moq.inventory_item_id
andms.organization_id(+)=moq.organization_id
andms.secondary_inventory_name(+)=moq.subinventory_code
andms.reservable_type(+) = 1
andnvl(mr.revision,'-1')=mir.revision(+)
andmir.inventory_item_id(+)=mr.inventory_item_id
andmir.organization_id(+)=mr.organization_id
andsysdate = nvl(mir.implementation_date(+),sysdate);
if avai_qty is null then
avai_qty:=0;
end if;
return avai_qty;
end;
/ |
