ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 品牌数量统计

品牌数量统计

原创 Linux操作系统 作者:KawaiPinky 时间:2011-03-15 15:30:07 0 删除 编辑
销售模组发运后销货统计:
SELECT /* SUM(OOLA.ORDERED_QUANTITY)
 MC.segment3,*/
SUM( NVL(OOLA.INVOICED_QUANTITY,OOLA.ORDERED_QUANTITY))
 /*OOHA.ORDER_NUMBER,
 MSIB.INVENTORY_ITEM_ID,
 OOLA.Actual_Shipment_Date*/
  FROM OE_ORDER_HEADERS_ALL OOHA,
       OE_ORDER_LINES_ALL   OOLA,
       MTL_ITEM_CATEGORIES  MIC,
       MTL_CATEGORY_SETS_TL MCST,
       MTL_CATEGORY_SETS_B  MCS,
       MTL_CATEGORIES_B_KFV MC,
       MTL_SYSTEM_ITEMS_B   MSIB
 WHERE OOHA.HEADER_ID = OOLA.HEADER_ID
   AND OOLA.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MSIB.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = MIC.ORGANIZATION_ID
   AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
   AND MCS.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
   AND MCST.LANGUAGE = USERENV('LANG')
   AND MIC.CATEGORY_ID = MC.CATEGORY_ID
   AND MCST.CATEGORY_SET_NAME = 'INV_Category'
   AND MIC.ORGANIZATION_ID = :organization
   AND MC.SEGMENT3 = :segment
   AND OOLA.flow_status_code = 'CLOSED'
   AND TO_CHAR(OOLA.Actual_Shipment_Date, 'YYYY-MM-DD') BETWEEN
       '2010-12-01' AND '2010-12-31'
 
AR模组品牌销货统计:
SELECT /*MC.SEGMENT3,*/
       SUM(NVL(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED)) AS QUNTITY
      /* NVL(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED),
       CTL.SALES_ORDER,
       MSIB.INVENTORY_ITEM_ID,
       ct.trx_number*/
  FROM RA_CUST_TRX_LINE_GL_DIST_ALL GD,
       RA_CUSTOMER_TRX_ALL          CT,
       RA_CUSTOMER_TRX_LINES_ALL    CTL,
       MTL_ITEM_CATEGORIES          MIC,
       MTL_CATEGORY_SETS_TL         MCST,
       MTL_CATEGORY_SETS_B          MCS,
       MTL_CATEGORIES_B_KFV         MC,
       MTL_SYSTEM_ITEMS_B           MSIB
 WHERE CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
   AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
   AND 'REC' = GD.ACCOUNT_CLASS
   AND 'Y' = GD.LATEST_REC_FLAG
   AND CTL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MSIB.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = MIC.ORGANIZATION_ID
   AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
   AND MCS.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
   AND MCST.LANGUAGE = USERENV('LANG')
   AND MIC.CATEGORY_ID = MC.CATEGORY_ID
   AND MCST.CATEGORY_SET_NAME = 'INV_Category'
   AND MIC.ORGANIZATION_ID =:organization
   AND TO_CHAR(GD.GL_DATE, 'YYYY-MM-DD') BETWEEN '2010-12-01' AND
       '2010-12-31'
   AND MC.SEGMENT3 =:segment
 GROUP BY MC.segment3

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

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

注册时间:2010-11-10

  • 博文量
    32
  • 访问量
    45849