ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ERP表关联及说明--库存

ERP表关联及说明--库存

原创 Linux操作系统 作者:KawaiPinky 时间:2011-06-20 16:50:14 0 删除 编辑
库存
/*-------盘点卡, 查询库存现有量,明细至品牌、货号、子库存、现有量、单位-----*/
SELECT mcb.segment3 AS BRAND,
       msib.segment1 AS ARTNO,
       moq.SUBINVENTORY_CODE AS STOCK,
       sum(moq.TRANSACTION_QUANTITY) AS QTY,
       msib.primary_unit_of_measure AS UOM
  FROM MTL_SYSTEM_ITEMS_b    msib,
       MTL_ONHAND_QUANTITIES moq,
       MTL_ITEM_CATEGORIES   MIC,
       MTL_CATEGORIES_B      MCB
 where msib.inventory_item_id = moq.INVENTORY_ITEM_ID
   and msib.organization_id = moq.ORGANIZATION_ID
   and mic.inventory_item_id = msib.inventory_item_id
   and mic.category_set_id = '1100000041'
   AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
   AND MCB.SEGMENT1 = '成品'
   and mic.organization_id = msib.organization_id
   and moq.SUBINVENTORY_CODE in (:stock)
   and msib.organization_id = 83
 group by msib.segment1, mcb.segment3, moq.SUBINVENTORY_CODE,msib.primary_unit_of_measure
 
/*---------- 盘点卡,产生序列-------------*/
1:创建占位符CP_1;
2:在组内创建公式列:
function CF_1Formula return VARCHAR2 is
 STRALL VARCHAR2(1000);
BEGIN
  IF:CP_1 IS NULL THEN
       :CP_1 :=1;
        STRALL :=LPAD(1, 4, '0');
  ELSIF :CP_1 IS NOT NULL THEN
      :CP_1 := :CP_1 + 1;
      STRALL :=  LPAD(:CP_1, 4, '0');
 END IF;
  RETURN STRALL; 
end;
 
 
 
 
 
-------------相关表---------------
MTL_SYSTEM_ITEMS_b---物料表
MTL_ONHAND_QUANTITIES ---现有量表
mtl_secondary_inventories --子库存表
Mtl_Item_Locations    ---货位表
org_organization_definitions  ---组织表
 
 
 
 
-----------接收入库-------------原物料    
      select
             rsl.item_description,
             rt.po_unit_price,
             rt.quantity,
             msib.segment1,
             sum(rt.quantity * rt.po_unit_price)
        from RCV_TRANSACTIONS   rt,
             RCV_SHIPMENT_LINES rsl,
             mtl_system_items_b msib
       where rt.shipment_line_id = rsl.shipment_line_id
         and msib.inventory_item_id = rsl.item_id
         and msib.organization_id = 83
         and RT.TRANSACTION_TYPE = 'DELIVER'
         and rt.source_document_code = 'PO'
         and rt.routing_header_id=2
            --   and rt.transaction_id = 64316
         /*and to_char(rt.transaction_date, 'yyyy-mm-dd') between
             '2011-03-01' and '2011-03-30'*/
       group by rsl.item_description,
                rt.po_unit_price,
                msib.segment1,
                rt.quantity
 
 
 
----------接收入库相关表--------
RCV_TRANSACTIONS ---接收事物处理汇总表
RCV_SHIPMENT_LINES---行
RCV_ROUTING_HEADERS---运输路线表

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24899662/viewspace-700310/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 接收入库表关联
请登录后发表评论 登录
全部评论

注册时间:2010-11-10

  • 博文量
    32
  • 访问量
    46003