ERP表关联及说明--库存

库存

/*-------盘点卡， 查询库存现有量，明细至品牌、货号、子库存、现有量、单位-----*/

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24899662/viewspace-700310/