失败的成品成本统计报表

SELECT u.FBillNo '出库单号',
    u.FDate '日期',
    u.FSupplyID '客户代码',
    v.FItemID '产品ID',
    v.FBatchNo '批号',
    v.FUnitID '单位',
    v.FQtyMust '应发数量',
    v.FQty '实发数量',
    v.FConsignAmount '出库金额'--,v.*
FROM ICStockBill u
    LEFT JOIN dbo.ICStockBillEntry v
        ON u.FInterID = v.FInterID
WHERE FTranType = 21
      AND u.FDate>'2017-11-01' AND u.FStatus=1 ORDER BY v.FBatchNo DESC


--select TO_CHAR(to_date('2013-01-01', 'yyyy-MM-dd'), 'yyyyMM') dateStr from dual

SELECT FHeadSelfY0222 '批号',PPBOMEntry.FItemID,PPBOM.FAuxQty '生产数量',FBatchNo '小板批号',FQtyMust '应发数量', datepart(mm,FDate) '月',datepart(yy,FDate) '年' INTO #pihao1
FROM PPBOM
    LEFT JOIN dbo.PPBOMEntry
        ON PPBOMEntry.FInterID = PPBOM.FInterID
WHERE FHeadSelfY0222 = 'HXCBUZG1710000400';
--DROP TABLE #pihao1
SELECT * FROM ICBal

SELECT FItemID,
    FYear,
    FPeriod,
    FBegQty '期初数量',
    FBegBal '期初余额',
    CASE FBegQty WHEN 0 THEN 0
    ELSE FBegBal / FBegQty END '均价'  INTO #junjia1
FROM dbo.ICBal
--WHERE FItemID =131908
ORDER BY 均价 DESC, FItemID ASC;

SELECT * FROM #junjia1
SELECT * FROM #pihao1 
SELECT * FROM #pihao1 , #junjia1 where FYear=年 AND FPeriod=月 AND #junjia1.FItemID = #pihao1.FItemID 



SELECT u.*
FROM t_TableDescription v
    LEFT JOIN t_FieldDescription u
        ON v.FTableID = u.FTableID
WHERE FTableName = 'PPBOM';
SELECT ICStockBillEntry.*
FROM ICStockBill
    LEFT JOIN dbo.ICStockBillEntry
        ON ICStockBillEntry.FInterID = ICStockBill.FInterID
WHERE FTranType = 21
      AND
      (
          FBillNo = 'HXXOUT010647'
          OR FBillNo = 'HXXOUT010649'
          OR FBillNo = 'HXXOUT010651'
      );
SELECT ICStockBillEntry.*
FROM ICStockBill
    LEFT JOIN dbo.ICStockBillEntry
        ON ICStockBillEntry.FInterID = ICStockBill.FInterID
WHERE FTranType = 21
      AND FBatchNo = 'HXCBUJL1703016922';
--FBillNo,FDate,FSupplyID,



SELECT FHeadSelfY0222 '批号',
    u.FICMOInterID '工单',
    v.FItemID,
    *
FROM dbo.PPBOM u
    LEFT JOIN dbo.PPBOMEntry v
        ON u.FInterID = v.FInterID
WHERE u.FICMOInterID = 63341; --Entry



SELECT *
FROM ICStockBill u
    LEFT JOIN dbo.ICStockBillEntry v
        ON u.FInterID = v.FInterID
WHERE FTranType = 21
      AND
      (
          FBillNo = 'HXXOUT010649'
          OR FBillNo = 'HXXOUT010651'
      );

SELECT u.FBillNo '出库单号',
    u.FDate '日期',
    u.FSupplyID '客户代码',
    v.FItemID '产品ID',
    v.FBatchNo '批号',
    v.FUnitID '单位',
    v.FQtyMust '应发数量',
    v.FQty '实发数量',
    v.FConsignAmount '出库金额',*
FROM ICStockBill u
    LEFT JOIN dbo.ICStockBillEntry v
        ON u.FInterID = v.FInterID
WHERE FTranType = 21
      AND u.FDate>'2017-11-01' AND u.FStatus=0

SELECT u.*
FROM CVT_T_MODIFY_BOARD u
    LEFT JOIN dbo.CVT_T_MODIFY_BOARDEntry v
        ON v.FID = u.FID
WHERE u.FBillNo = 'CQ-1801172681'
      OR u.FBillNo = 'ZG-1710172513';
SELECT *
FROM dbo.CVT_T_MODIFY_BOARD
ORDER BY FDate DESC;
SELECT *
FROM t_Item
WHERE FItemID = 121281;
SELECT *
FROM dbo.CVT_T_MODIFY_BOARD
WHERE IsOnLine = '在线'
ORDER BY FDate DESC;

SELECT u.FBillNo '出库单号',
    u.FDate '日期',
    u.FSupplyID '客户代码',
    v.FItemID '产品ID',
    v.FBatchNo '批号',
    v.FUnitID '单位',
    v.FQtyMust '应发数量',
    v.FQty '实发数量',
    v.FConsignAmount '出库金额'--,v.*
FROM ICStockBill u
    LEFT JOIN dbo.ICStockBillEntry v
        ON u.FInterID = v.FInterID
WHERE FTranType = 21
      AND u.FDate>'2017-11-01' AND u.FStatus=1 ORDER BY v.FBatchNo DESC


--select TO_CHAR(to_date('2013-01-01', 'yyyy-MM-dd'), 'yyyyMM') dateStr from dual

SELECT FHeadSelfY0222 '批号',PPBOMEntry.FItemID,PPBOM.FAuxQty '生产数量',FBatchNo '小板批号',FQtyMust '应发数量', datepart(mm,FDate) '月',datepart(yy,FDate) '年' INTO #pihao1
FROM PPBOM
    LEFT JOIN dbo.PPBOMEntry
        ON PPBOMEntry.FInterID = PPBOM.FInterID
WHERE FHeadSelfY0222 = 'HXCBUZG1710000400';
--DROP TABLE #pihao1
SELECT * FROM ICBal

SELECT FItemID,
    FYear,
    FPeriod,
    FBegQty '期初数量',
    FBegBal '期初余额',
    CASE FBegQty WHEN 0 THEN 0
    ELSE FBegBal / FBegQty END '均价'  INTO #junjia1
FROM dbo.ICBal
--WHERE FItemID =131908
ORDER BY 均价 DESC, FItemID ASC;

SELECT * FROM #junjia1
SELECT * FROM #pihao1 
SELECT * FROM #pihao1 , #junjia1 where FYear=年 AND FPeriod=月 AND #junjia1.FItemID = #pihao1.FItemID 



SELECT u.*
FROM t_TableDescription v
    LEFT JOIN t_FieldDescription u
        ON v.FTableID = u.FTableID
WHERE FTableName = 'PPBOM';
SELECT ICStockBillEntry.*
FROM ICStockBill
    LEFT JOIN dbo.ICStockBillEntry
        ON ICStockBillEntry.FInterID = ICStockBill.FInterID
WHERE FTranType = 21
      AND
      (
          FBillNo = 'HXXOUT010647'
          OR FBillNo = 'HXXOUT010649'
          OR FBillNo = 'HXXOUT010651'
      );
SELECT ICStockBillEntry.*
FROM ICStockBill
    LEFT JOIN dbo.ICStockBillEntry
        ON ICStockBillEntry.FInterID = ICStockBill.FInterID
WHERE FTranType = 21
      AND FBatchNo = 'HXCBUJL1703016922';
--FBillNo,FDate,FSupplyID,

SELECT *
FROM t_Item
WHERE FItemClassID = 3
      AND FName = ''; -- FItemClassID=3 AND FName=''
SELECT *
FROM t_Emp
WHERE FName = '';
SELECT *
FROM t_ICItem
WHERE FItemID = 16919;

SELECT FHeadSelfY0222 '批号',
    u.FICMOInterID '工单',
    v.FItemID,
    *
FROM dbo.PPBOM u
    LEFT JOIN dbo.PPBOMEntry v
        ON u.FInterID = v.FInterID
WHERE u.FICMOInterID = 63341; --Entry



SELECT *
FROM ICStockBill u
    LEFT JOIN dbo.ICStockBillEntry v
        ON u.FInterID = v.FInterID
WHERE FTranType = 21
      AND
      (
          FBillNo = 'HXXOUT010649'
          OR FBillNo = 'HXXOUT010651'
      );

SELECT u.FBillNo '出库单号',
    u.FDate '日期',
    u.FSupplyID '客户代码',
    v.FItemID '产品ID',
    v.FBatchNo '批号',
    v.FUnitID '单位',
    v.FQtyMust '应发数量',
    v.FQty '实发数量',
    v.FConsignAmount '出库金额',*
FROM ICStockBill u
    LEFT JOIN dbo.ICStockBillEntry v
        ON u.FInterID = v.FInterID
WHERE FTranType = 21
      AND u.FDate>'2017-11-01' AND u.FStatus=0

SELECT u.*
FROM CVT_T_MODIFY_BOARD u
    LEFT JOIN dbo.CVT_T_MODIFY_BOARDEntry v
        ON v.FID = u.FID
WHERE u.FBillNo = 'CQ-1801172681'
      OR u.FBillNo = 'ZG-1710172513';
SELECT *
FROM dbo.CVT_T_MODIFY_BOARD
ORDER BY FDate DESC;
SELECT *
FROM t_Item
WHERE FItemID = 121281;
SELECT *
FROM dbo.CVT_T_MODIFY_BOARD
WHERE IsOnLine = '在线'
ORDER BY FDate DESC;