FLUX WMS 自定义盘点数据采集与核对

才华有限青年_

已于 2024-04-26 16:47:10 修改

阅读量554

点赞数

文章标签：安全

于 2020-06-07 21:49:36 首次发布

本文链接：https://blog.csdn.net/yx183/article/details/106607833

版权

--建立 存储区采集数据表 TMP_STORE_INVQTY
CREATE TABLE "DBWMS"."TMP_STORE_INVQTY" 
   (	"SEQ" VARCHAR2(50), 
	"LOCATIONID" VARCHAR2(50), 
	"SKU" VARCHAR2(50), 
	"QTY" NUMBER, 
	"USERID" VARCHAR2(50), 
	"ADDTIME" DATE, 
	"CUSTOMERID" VARCHAR2(50), 
	"TRACEID" VARCHAR2(50), 
	"SKU_1" VARCHAR2(50)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBWMS"  ENABLE ROW MOVEMENT ;

--建立 拣选区采集数据表 TMP_CUS_INVCHECK_CSQTY
CREATE TABLE "DBWMS"."TMP_CUS_INVCHECK_CSQTY" 
   (	"SEQ" VARCHAR2(50), 
	"LOCATIONID" VARCHAR2(50), 
	"SKU" VARCHAR2(100), 
	"QTY" NUMBER, 
	"USERID" VARCHAR2(50), 
	"ADDTIME" DATE, 
	"CUSTOMERID" VARCHAR2(50), 
	"TRACEID" VARCHAR2(50), 
	"SKU_1" VARCHAR2(100)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBWMS"  ENABLE ROW MOVEMENT ;

--自定义盘点拣选区
if IN_UDFOperation ='INV_CHECKCSQTY' then 
--检查产品
if nvl(IN_Parameter2,'N') = 'N' then
   OUT_Return_Code := '999#产品不能为空,请检查.';
   return;
end if;

begin
  select sku,alternate_sku1,alternate_sku3 into r_sku ,r_alternate_sku1,r_alternate_sku3
  from bas_sku where (alternate_sku1=IN_Parameter2 or sku=IN_Parameter2 or alternate_sku3=IN_Parameter2)   and customerid ='MEXICAN_M';
   exception
   when no_data_found then
  OUT_Return_Code:='999%产品不存在' ;
  /*when too_many_rows then
  OUT_Return_Code:='999条码资料重复' ;*/
  return;
end ;

--检查输入的数量
if nvl(IN_Parameter3,'N') = 'N' then
   OUT_Return_Code := '999#数量不能为空,请检查.';
   return;
end if;

if IN_Parameter3 > 1000000 then
  OUT_Return_Code:='999数量异常请确认！';
  return;
end if;

--检查库位
if nvl(IN_Parameter1,'N') = 'N' then
   OUT_Return_Code := '999#库位不能为空,请检查.';
   return;
end if;

select  count(*) into is_loc from bas_location where locationid =IN_Parameter1;

     if is_loc=0 then
          OUT_Return_Code:='999库位不正确';
           return;
     end if;
     
--插入数据
insert into tmp_cus_invcheck_csqty (Seq,LocationID, SKU,sku_1, QTY,UserID,Addtime,CUSTOMERID,TRACEID)
  values
  (0,IN_Parameter1,r_sku,r_alternate_sku1,IN_Parameter3,IN_UserID,sysdate,'MEXICAN_M','*');


if is_locqty is null then

  select sum(qty),locationid into is_locqty,is_locon
  from tmp_cus_invcheck_csqty where locationid=IN_Parameter1  group by locationid;

  End If;
   commit;

OUT_Return_Code:='000库位'||IN_Parameter1 || ';数量:'|| is_locqty||':款号:'||r_alternate_sku1||'';
return;
end if ;
----------------------------------------------------------------------------------------

--自定义盘点存储区
if IN_UDFOperation ='INV_CHECKQTY' then 

--检查跟踪号
if nvl(IN_Parameter1,'N') = 'N' then
   OUT_Return_Code := '999#箱码不能为空,请检查.';
   return;
end if;

if IN_Parameter1 not like 'LP%' and IN_Parameter1 not like 'DBLZ%' 
  and IN_Parameter1 not like 'JXTC%' and IN_Parameter1 not like 'DBWP%'
  and IN_Parameter1 not like 'CXTC%'  then
   OUT_Return_Code := '999#箱码不正确，请检查.';
   return;
end if;

select count(1) into r_nrow from tmp_store_invqty t where t.traceid = IN_Parameter1;
if r_nrow > 0 then
   OUT_Return_Code := '999#箱码重复，请检查.';
   return;
end if;

--检查产品
if nvl(IN_Parameter2,'N') = 'N' then
   OUT_Return_Code := '999#产品不能为空,请检查.';
   return;
end if;

begin
    select sku,alternate_sku1,alternate_sku3 into r_sku ,r_alternate_sku1,r_alternate_sku3
    from bas_sku where (alternate_sku1=IN_Parameter2 or sku=IN_Parameter2 or alternate_sku3=IN_Parameter2)  and customerid ='MEXICAN_M';
     exception
     when no_data_found then
    OUT_Return_Code:='999%产品不存在' ;
    /*when too_many_rows then
    OUT_Return_Code:='999条码资料重复' ;*/
    return;
end ;

--检查库位
if nvl(IN_Parameter3,'N') = 'N' then
   OUT_Return_Code := '999#库位不能为空,请检查.';
   return;
end if;

select  count(*) into is_loc from bas_location where locationid =IN_Parameter3;

     if is_loc=0 then
          OUT_Return_Code:='999库位不正确';
           return;
     end if;
     
--检查输入的数量
if nvl(IN_Parameter4,'N') = 'N' then
   OUT_Return_Code := '999#数量不能为空,请检查.';
   return;
end if;

if IN_Parameter4 > 10000 or IN_Parameter4 < 0 then
    OUT_Return_Code:='999数量异常请确认！';
    return;
end if;

--插入数据
insert into tmp_store_invqty (Seq,LocationID, SKU,sku_1, QTY,UserID,Addtime,CUSTOMERID,TRACEID)
  values
  (0,IN_Parameter3,r_sku,r_alternate_sku1,IN_Parameter4,IN_UserID,sysdate,'MEXICAN_M',IN_Parameter1);

   commit;

OUT_Return_Code:='000箱码:'||IN_Parameter1||';产品:'||r_alternate_sku1 || ';库位:'|| IN_Parameter3||';数量:'||IN_Parameter4||'';
return;
end if ;

--通过视图核对差异数据（汇总差异数据）
CREATE OR REPLACE VIEW V_盘点差异_WH01 AS
SELECT NVL(A.SKU,B.SKU) SKU,A.N1 账面数,B.N2 盘点数,(NVL(B.N2,'0')-NVL(A.N1,'0')) 差异,CASE C.FREIGHTCLASS WHEN 'FL' THEN '辅料' WHEN 'ZP' THEN '赠品' ELSE '正常商品' END 分类,C.DESCR_C 中文说明  FROM
(SELECT SKU,SUM(QTY) N1 FROM INV_LOT_LOC_ID
WHERE LOCATIONID IN
(SELECT LOCATIONID FROM VIEW_MULTIWAREHOUSE WHERE  WAREHOUSEID='WH01' And LOCATIONID not in('LSZP01','LSCC01'))
GROUP BY SKU) A
FULL OUTER JOIN
(SELECT SKU,SUM(N) N2 FROM
(SELECT SKU,SUM(QTY) N,LOCATIONID FROM
(SELECT * FROM TMP_CUS_INVCHECK_CSQTY WHERE LOCATIONID IN
(SELECT LOCATIONID FROM VIEW_MULTIWAREHOUSE WHERE WAREHOUSEID='WH01' And LOCATIONID not in('LSZP01','LSCC01'))
UNION ALL
(SELECT * FROM TMP_STORE_INVQTY WHERE LOCATIONID IN
(SELECT LOCATIONID FROM VIEW_MULTIWAREHOUSE WHERE WAREHOUSEID='WH01'And LOCATIONID not in('LSZP01','LSCC01') )))  GROUP BY SKU,LOCATIONID) GROUP BY SKU) B ON A.SKU=B.SKU
LEFT JOIN BAS_SKU C ON (C.SKU=A.SKU OR C.SKU=B.SKU)
ORDER BY (NVL(B.N2,'0')-NVL(A.N1,'0'));

--筛选重复数据
CREATE OR REPLACE VIEW V_盘点数据重复项 AS
SELECT "SEQ" SEQ,"LOCATIONID" 库位,"SKU" SKU,"QTY" 盘点数量,"USERID" 员工工号,"ADDTIME" 新增时间,"CUSTOMERID" 货主,"TRACEID" 跟踪号,"SKU_1" 旧条码 FROM
(SELECT * FROM TMP_CUS_INVCHECK_CSQTY
UNION ALL
SELECT * FROM TMP_STORE_INVQTY)
WHERE LOCATIONID||SKU||QTY||TRACEID IN
(SELECT LOCATIONID||SKU||QTY||TRACEID  FROM
(SELECT COUNT(*) N,LOCATIONID,SKU,QTY,TRACEID FROM
(SELECT * FROM TMP_CUS_INVCHECK_CSQTY
UNION ALL
SELECT * FROM TMP_STORE_INVQTY)
GROUP BY LOCATIONID,SKU,QTY,TRACEID)
WHERE N>'1');

--通过视图核对差异数据（库位差异数据）
CREATE OR REPLACE VIEW V_盘点库位差异 AS
SELECT DD.WAREHOUSEID 仓库编号, NVL(AA.SKU,BB.SKU) SKU,NVL(AA.LOCATIONID,BB.LOCATIONID) 库位,NVL(AA.N,'0') 账面数量,NVL(BB.N,'0') 盘点数量,(NVL(BB.N,'0')-NVL(AA.N,'0')) 库位差异,CC.DESCR_C 中文说明 FROM
(SELECT SKU,LOCATIONID,SUM(QTY) N FROM INV_LOT_LOC_ID
WHERE LOCATIONID IN (SELECT LOCATIONID FROM VIEW_MULTIWAREHOUSE WHERE WAREHOUSEID='WH01' And LOCATIONID not in('LSZP01','LSCC01'))
GROUP BY SKU,LOCATIONID) AA
FULL OUTER JOIN
(SELECT SKU,LOCATIONID,SUM(QTY) N FROM
(SELECT * FROM TMP_CUS_INVCHECK_CSQTY
UNION ALL
SELECT * FROM TMP_STORE_INVQTY )
GROUP BY SKU,LOCATIONID) BB ON AA.SKU=BB.SKU AND AA.LOCATIONID=BB.LOCATIONID
LEFT JOIN
BAS_SKU CC ON AA.SKU=CC.SKU OR BB.SKU=CC.SKU
LEFT JOIN
VIEW_MULTIWAREHOUSE DD ON AA.LOCATIONID=DD.LOCATIONID OR BB.LOCATIONID=DD.LOCATIONID;