ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 接收入库表关联

接收入库表关联

原创 Linux操作系统 作者:KawaiPinky 时间:2011-06-02 14:04:20 0 删除 编辑


-------------杂项接收-------------------    

select  /*PR.release_num,*/
                rsh.receipt_num,
               /* rsl.item_description,  */          
                pv.VENDOR_NAME,rt.quantity,rt.po_unit_price
               -- sum(rt.quantity*rt.po_unit_price)
  from RCV_SHIPMENT_HEADERS rsh,
       RCV_SHIPMENT_LINES   rsl,
       RCV_TRANSACTIONS     rt,
       PO_HEADERS_ALL       POH,
       PO_LINES_ALL         POL,
       PO_VENDORS           PV
 where rsh.shipment_header_id = rsl.shipment_header_id
   and rsh.shipment_header_id = rt.shipment_header_id
   and rsl.shipment_line_id = rt.shipment_line_id
   and poh.po_header_id = rt.po_header_id
   and pol.po_line_id = rt.po_line_id
   and poh.org_id = pol.org_id
   AND PV.VENDOR_ID = RT.VENDOR_ID
   AND RT.TRANSACTION_TYPE='DELIVER'
   and poh.org_id = 81/*
   AND PR.po_release_id=RT.PO_RELEASE_ID*/
 /*  and poh.segment1 = '采购单号'  */
and  rsh.receipt_num='接收编号'  
 AND TO_CHAR(RT.TRANSACTION_DATE, 'YYYY-MM-DD') BETWEEN '2011-04-01' AND
       '2011-05-31'
       group by rsh.receipt_num,
   pv.VENDOR_NAME/*,rsh.receipt_num*/
  
  -------------------------原物料接收---------------------------
  
   select/* PR.release_num, */pv.VENDOR_NAME,/*rt.quantity,rt.po_unit_price*/sum( rt.quantity*rt.po_unit_price)
     from PO_RELEASES_ALL  pr,
          RCV_TRANSACTIONS RT,
          PO_HEADERS_ALL   POH,
          PO_VENDORS       PV
    WHERE PR.po_release_id = RT.PO_RELEASE_ID
      and poh.po_header_id = rt.po_header_id
      AND PV.VENDOR_ID = RT.VENDOR_ID
      AND RT.TRANSACTION_TYPE = 'DELIVER'
  /*    AND TO_CHAR(RT.TRANSACTION_DATE, 'YYYY-MM-DD') BETWEEN '2011-04-01' AND
       '2011-05-31' */
      AND poh.segment1 =‘ 采购单号'  
    /*  and pr.release_num='发放版本号'*/
      group by /* PR.release_num, */pv.VENDOR_NAME

 
 
 
------------------------------------原材料接收入库----------------数量、单价
select SUM(mmt.primary_quantity) AS beginqty,
        SUM(mmt.primary_quantity *
            DECODE(MTA.PRIMARY_QUANTITY,
                   0,
                   NULL,
                   NULL,
                   NULL,
                   decode(MTA.ACCOUNTING_LINE_TYPE,
                          1,
                          MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                          14,
                          MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                          3,
                          MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY,
                          ABS(MTA.BASE_TRANSACTION_VALUE /
                              MTA.PRIMARY_QUANTITY)))) AS beginamount,                          
        to_char(MMT.transaction_date, 'YYYY') ||
        to_char(MMT.transaction_date, 'MM') AS Currentdate
   from MTL_MATERIAL_TRANSACTIONS mmt,
        mtl_system_items_b        msib,
        mtl_transaction_types     mtt,
        MTL_TRANSACTION_ACCOUNTS  MTA
  WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
    AND MSIB.ORGANIZATION_ID = 83
    and msib.segment1 = ARTNO
    and mmt.transaction_type_id = mtt.transaction_type_id
    AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
    AND MTA.ACCOUNTING_LINE_TYPE = 5
    and mtt.transaction_type_name in ('PO Receipt', 'Return to Vendor','期初开帐')
    and mmt.transaction_date between to_date('2010-07-01', 'yyyy-mm-dd') and
        enddate 
  group by to_char(mmt.transaction_date, 'YYYY') ||
           to_char(mmt.transaction_date, 'MM')
  ORDER BY to_char(mmt.transaction_date, 'YYYY') ||
           to_char(mmt.transaction_date, 'MM');

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

上一篇: 品牌数量统计
请登录后发表评论 登录
全部评论

注册时间:2010-11-10

  • 博文量
    32
  • 访问量
    44404