@Tools 銷貨單檢查 - 批號異常

銷貨單檢查 - 批號異常
檢查銷貨單上,銷貨回報批號日期
於該月份大於銷貨單銷貨日期的批號資料


==================================

'加入 生產日報表
去核對lotno, 料號
撈出實際日期

'讓使用者選取是否只看不同月份的異常資料(同月份不會影響帳務)


====================================
'結合生產日報表,查詢日期異常資料

select a.saledate as 銷貨日期, c.pdtansdate as 回報日期, c.saleno as 銷貨單號,
 c.orderno as 訂貨單號, c.partcode as 訂貨料號, c.batchno as 批號,
c.lotno, c.stockqty as 出貨良品數, c.impdate as 建檔時間
from tblsale a
join
(select  g.pdtansdate, b.saleno, b.orderno, b.partcode, b.batchno,
 b.lotno, b.stockqty, b.impdate
from tbllotexpsale b
join (select d.pdtansno, d.lotno, d.partcode, f.pdtansdate from tblLotImpPdtAns d
join (select e.pdtansno, e.pdtansdate from tblPdtAns e where e.status = 2 ) f
on d.pdtansno = f.pdtansno ) as g
on b.partcode = g.partcode and b.lotno = g.lotno
) as c
on c.saleno = a.saleno
where a.status = 2 and c.saleno = 'SA20080811004' and a.saledate < c.pdtansdate




'生產日報表,核對包裝回報單號,撈出正確回報日期
select d.pdtansno, d.lotno, d.partcode, f.pdtansdate from tblLotImpPdtAns d
join (select e.pdtansno, e.pdtansdate from tblPdtAns e where e.status = 2 ) f
on d.pdtansno = f.pdtansno
where d.lotno = 'PC200800000000002' and partcode = '000216BNOL0XX'
order by partcode




'銷貨日期 < 出貨回報日期 異常資料
'LotNo PD開頭, 長度13
select a.saledate as 銷貨日期, c.lotdate as 批號回報日期, c.saleno as 銷貨單號,
 c.orderno as 訂貨單號, c.partcode as 訂貨料號, c.batchno as 批號,
c.lotno, c.stockqty as 出貨良品數, c.impdate as 建檔時間
from tblsale a
join
(select substring(b.lotno,3,8) as lotdate, b.saleno, b.orderno, b.partcode, b.batchno,
 b.lotno, b.stockqty, b.impdate
from tbllotexpsale b where b.saleno = 'SA20111003001' and len(b.lotno) = 13
and b.lotno like 'PD%' ) as c
on c.saleno = a.saleno
where a.status = 2 and a.saledate < c.lotdate



'出庫明細, 找批號
select b.* from tbllotexpsale b where b.saleno = 'SA20111005001'

'銷貨單號
select * from tblsale where saledate between '20111001' and '20111030' and status = 2