轉自：https://blog.csdn.net/yx183/article/details/106569229
-- WMS 庫存餘量查詢
SELECT vm.WarehouseID,
kc.CustomerID,
kc.SKU,
kc.LotNum,
kc.LocationID,
kc.TraceID,
kc.LPN,
Cast(kc.Qty / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS Qty,
Cast(kc.QtyAllocated / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS QtyAllocated,
Cast(kc.QtyMVIN / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS QtyMVIN,
Cast(kc.QtyMVOut / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS QtyMVOut,
Cast(Nvl((SELECT Cast(Sum(Nvl(DOC_ADJ_Details.ToQty, 0) - Nvl(DOC_ADJ_Details.Qty, 0)) AS NUMERIC(18, 0))
FROM DOC_ADJ_Details DOC_ADJ_Details
WHERE DOC_ADJ_Details.LineStatus < '10'
AND DOC_ADJ_Details.locationid = kc.locationid
AND DOC_ADJ_Details.LOTNUM = kc.LOTNUM
AND DOC_ADJ_Details.TRACEID = kc.TRACEID), 0) AS NUMERIC(18, 0)) AS QtyAdj,
Cast(kc.Qty / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) - Cast(kc.QtyAllocated / Nvl(vu.Qty, 1) AS NUMERIC(18, 0))
- Cast(kc.QtyOnHold / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) - Cast(kc.QTYRPOUT / Nvl(vu.Qty, 1) AS NUMERIC(18, 0))
- Cast(kc.QtyMVOut / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS QtyAvailed,
Cast(kc.QtyOnHold / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS QtyOnHold,
Cast(kc.QtyRPIn / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS QtyRPIn,
Cast(kc.QtyPA / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS QtyPA,
Cast(kc.QtyRPOut / Nvl(vu.Qty, 1) AS NUMERIC(18, 0)) AS QtyRPOut,
bas_sku.Descr_C AS SkuDescrc,
bas_sku.Descr_E AS SkuDescre,
bas_sku.ImageAddress,
kc.Cubic AS TOTalcubic,
kc.GrossWeight AS TOTalGrossWeight,
kc.NetWeight,
kc.Price,
bas_sku.SoftAllocationRule,
bas_sku.AllocationRule,
bas_sku.Rotationid,
iva.LotAtt01,
iva.LotAtt02,
iva.LotAtt03,
iva.LotAtt04,
iva.LotAtt05,
iva.LotAtt06,
iva.LotAtt07,
iva.LotAtt08,
iva.LotAtt09,
iva.LotAtt10,
iva.LotAtt11,
iva.LotAtt12
FROM INV_LOT_LOC_ID kc
LEFT OUTER JOIN BAS_Location bl ON bl.LocationID = kc.LocationID
LEFT OUTER JOIN BAS_SKU bas_sku ON bas_sku.CustomerID = kc.CustomerID AND bas_sku.SKU = kc.SKU
LEFT OUTER JOIN BAS_Customer bc ON bc.CustomerID = kc.CustomerID AND bc.Customer_Type = 'OW'
LEFT JOIN view_uom vu ON vu.UOM = bas_sku.ReportUOM AND vu.PACKID = bas_sku.PackID
LEFT OUTER JOIN INV_LOT_ATT iva ON iva.LotNum = kc.LotNum
LEFT OUTER JOIN INV_LOT_ATT_Extend ivae ON iva.LotAtt11 = ivae.ExLotNum
LEFT JOIN VIEW_MultiWarehouse vm ON kc.locationid = vm.locationid
LEFT JOIN bas_zone bz ON bl.pickZone = bz.zone
WHERE ( kc.Qty > 0 OR kc.QtyRPIN > 0 OR kc.QtyMVIN > 0 OR kc.QtyPa > 0 )
AND vm.WarehouseID = 'WH01'
AND kc.CustomerID = 'MEXICAN_M'
AND kc.SKU = 'MMJB01181997GY01A'
-- WMS 庫齡報表
select c.warehouseid 倉庫代碼,bc.descr_c 倉庫名稱,a.customerid 客戶,
a.sku 産品條碼,bs.descr_c 産品描述,bs.descr_e 名稱,bs.reservedfield10 尺寸顔色,bs.reservedfield03 品類,
replace(replace(replace(bs.freightclass,'ZC','正常商品'),'FL','輔料'),'ZP','贈品') as 貨類,
sum(case when ROUND(TO_NUMBER(sysdate - to_date(b.lotatt03,'YYYY-MM-DD')))<30 then a.qty end) "在庫<30天",
sum(case when ROUND(TO_NUMBER(sysdate - to_date(b.lotatt03,'YYYY-MM-DD')))>=30
and ROUND(TO_NUMBER(sysdate - to_date(b.lotatt03,'YYYY-MM-DD')))<60 then a.qty end) "30=<在庫天<60天",
sum(case when ROUND(TO_NUMBER(sysdate - to_date(b.lotatt03,'YYYY-MM-DD')))>=60
and ROUND(TO_NUMBER(sysdate - to_date(b.lotatt03,'YYYY-MM-DD')))<90 then a.qty end) "60=<在庫天<90天",
sum(case when ROUND(TO_NUMBER(sysdate - to_date(b.lotatt03,'YYYY-MM-DD')))>=90
and ROUND(TO_NUMBER(sysdate - to_date(b.lotatt03,'YYYY-MM-DD')))<180 then a.qty end) "90=<在庫天<180天",
sum(case when ROUND(TO_NUMBER(sysdate - to_date(b.lotatt03,'YYYY-MM-DD')))>=180 then a.qty end) "在庫>=180天",
sum(a.qty) 合計
from inv_lot_loc_id a left join inv_lot_att b on a.sku=b.sku and a.lotnum=b.lotnum
left join view_multiwarehouse c on a.locationid=c.locationid
left join bas_sku bs on a.sku = bs.sku
left join bas_customer bc on bc.customerid = c.warehouseid and bc.customer_type='WH'
where 1=1 and c.warehouseid='WH01'
group by c.warehouseid,bc.descr_c,a.customerid,
a.sku,bs.descr_c,bs.descr_e,bs.reservedfield10,bs.reservedfield03,
replace(replace(replace(bs.freightclass,'ZC','正常商品'),'FL','輔料'),'ZP','贈品')
--B2B訂單複核時效
select bc.descr_c 倉庫,to_char(d.checktime,'YYYY-MM-DD') 日期, 'B2B訂單複核' 業務單元,
b.orderno 訂單号,b.soreference1 通知單号,
case when substr(b.soreference1,1,3)='BAO' then '采銷B2B' when substr(b.soreference1,1,2)='VA' then '唯品JIT' end 訂單類型,
d.checkwho 複核工号,ssu.user_name 複核人,sum(d.qtypicked_each) "總數量(不含贈品)",count(distinct d.picktotraceid) 箱數,
MIN(to_char(d.checktime,'YYYY-MM-DD HH24:MI:SS')) 複核開始時間,
MAX(to_char(d.checktime,'YYYY-MM-DD HH24:MI:SS')) 複核結束時間,
ceil((MAX(d.checktime)-MIN(d.checktime))*24*60) "耗時/分"
from doc_order_header b
left join doc_order_details c on b.orderno=c.orderno
left join act_allocation_details d on b.orderno=d.orderno and c.sku=d.sku and d.orderlineno=c.orderlineno
left join bas_sku bs on bs.sku=c.sku
left join bas_customer bc on bc.customerid=b.warehouseid and bc.customer_type='WH'
left join sec_sys_user ssu on ssu.user_id=d.checkwho
where 1=1
and b.sostatus>='63' and b.sostatus<>'90'
and bs.freightclass='ZC'
and b.warehouseid in ('WHCX','WPJIT')
and b.ordertype in('DBCK','B2BCK')
and (substr(b.soreference1,1,3)='BAO' or substr(b.soreference1,1,2)='VA')
and d.checktime>trunc(sysdate -5)
group by bc.descr_c,to_char(d.checktime,'YYYY-MM-DD'), 'B2B訂單複核',
b.orderno,b.soreference1,
case when substr(b.soreference1,1,3)='BAO' then '采銷B2B' when substr(b.soreference1,1,2)='VA' then '唯品JIT' end,
d.checkwho,ssu.user_name
order by 倉庫,日期
--B2B訂單揀貨時效
select bc.descr_c 倉庫,to_char(d.pickedtime,'YYYY-MM-DD') 日期, 'B2B訂單揀貨' 業務單元,
b.orderno 訂單号,b.soreference1 通知單号,
case when substr(b.soreference1,1,3)='BAO' then '采銷B2B' when substr(b.soreference1,1,2)='VA' then '唯品JIT' end 訂單類型,
d.pickedwho 揀貨工号,ssu.user_name 揀貨人,sum(d.qtypicked_each) "總數量(不含贈品)",
count(distinct d.sku) 商品數,count(distinct d.location) 庫位數,
MIN(to_char(d.pickedtime,'YYYY-MM-DD HH24:MI:SS')) 揀貨開始時間,
MAX(to_char(d.pickedtime,'YYYY-MM-DD HH24:MI:SS')) 揀貨結束時間,
ceil((MAX(d.pickedtime)-MIN(d.pickedtime))*24*60) "耗時/分"
from doc_order_header b
left join doc_order_details c on b.orderno=c.orderno
left join act_allocation_details d on b.orderno=d.orderno and c.sku=d.sku and d.orderlineno=c.orderlineno
left join bas_sku bs on bs.sku=c.sku
left join bas_customer bc on bc.customerid=b.warehouseid and bc.customer_type='WH'
left join sec_sys_user ssu on ssu.user_id=d.pickedwho
where 1=1
and b.sostatus>='60' and b.sostatus<>'90'
and bs.freightclass='ZC'
and b.warehouseid in ('WHCX','WPJIT')
and b.ordertype in('DBCK','B2BCK')
and (substr(b.soreference1,1,3)='BAO' or substr(b.soreference1,1,2)='VA')
and d.pickedtime>trunc(sysdate -5)
group by bc.descr_c,to_char(d.pickedtime,'YYYY-MM-DD'), 'B2B訂單揀貨',
b.orderno,b.soreference1,
case when substr(b.soreference1,1,3)='BAO' then '采銷B2B' when substr(b.soreference1,1,2)='VA' then '唯品JIT' end,
d.pickedwho,ssu.user_name
order by 倉庫,日期
--B2C波次揀貨時效.
select bc.descr_c 倉庫,to_char(a.addtime,'YYYY-MM-DD') 日期, 'B2C波次揀貨' 業務單元,a.waveno 波次号,
case when a.descr like'%單品%' then '一單一品' when a.descr like'%多品%' then '一單多品' else a.descr end 波次類型,
d.pickedwho 揀貨工号,ssu.user_name 揀貨人,sum(d.qtypicked_each) "總數量(不含贈品)",
count(distinct d.orderno) 訂單數,count(distinct d.sku) 商品數,count(distinct d.location) 庫位數,
MIN(to_char(d.pickedtime,'YYYY-MM-DD HH24:MI:SS')) 揀貨開始時間,
MAX(to_char(d.pickedtime,'YYYY-MM-DD HH24:MI:SS')) 揀貨結束時間,
ceil((MAX(d.pickedtime)-MIN(d.pickedtime))*24*60) "耗時/分"
from doc_wave_header a left join doc_order_header b on a.waveno=b.waveno
left join doc_order_details c on b.orderno=c.orderno
left join act_allocation_details d on b.orderno=d.orderno and c.sku=d.sku and d.orderlineno=c.orderlineno
left join bas_sku bs on bs.sku=c.sku
left join bas_customer bc on bc.customerid=a.warehouseid and bc.customer_type='WH'
left join sec_sys_user ssu on ssu.user_id=d.pickedwho
where 1=1
and a.wavestatus>='60'
and bs.freightclass='ZC'
and a.warehouseid in ('WH01','WPJIT')
and b.ordertype='JYCK'
and a.addtime>trunc(sysdate)
group by bc.descr_c,to_char(a.addtime,'YYYY-MM-DD'), 'B2C波次揀貨',
a.waveno ,case when a.descr like'%單品%' then '一單一品' when a.descr like'%多品%' then '一單多品' else a.descr end,
d.pickedwho,ssu.user_name
order by 倉庫,日期
--B2C複核時效
select bc.descr_c 倉庫,to_char(a.addtime,'YYYY-MM-DD') 日期, 'B2C複核' 業務單元,a.waveno 波次号,
case when a.descr like'%單品%' then '一單一品' when a.descr like'%多品%' then '一單多品' else a.descr end 波次類型,
d.checkwho 複核工号,ssu.user_name 複核人,sum(d.qtypicked_each) "總數量(不含贈品)",count(distinct d.orderno) 訂單數,
MIN(to_char(d.checktime,'YYYY-MM-DD HH24:MI:SS')) 複核開始時間,
MAX(to_char(d.d.checktime,'YYYY-MM-DD HH24:MI:SS')) 複核結束時間,
ceil((MAX(d.d.checktime)-MIN(d.d.checktime))*24*60) "耗時/分"
from doc_wave_header a left join doc_order_header b on a.waveno=b.waveno
left join doc_order_details c on b.orderno=c.orderno
left join act_allocation_details d on b.orderno=d.orderno and c.sku=d.sku and d.orderlineno=c.orderlineno
left join bas_sku bs on bs.sku=c.sku
left join bas_customer bc on bc.customerid=a.warehouseid and bc.customer_type='WH'
left join sec_sys_user ssu on ssu.user_id=d.checkwho
where 1=1
and a.wavestatus='99'
and bs.freightclass='ZC'
and a.warehouseid in ('WH01','WPJIT')
and b.ordertype='JYCK'
and a.addtime>trunc(sysdate)
group by bc.descr_c,to_char(a.addtime,'YYYY-MM-DD'), 'B2C波次揀貨',
a.waveno ,case when a.descr like'%單品%' then '一單一品' when a.descr like'%多品%' then '一單多品' else a.descr end,
d.checkwho,ssu.user_name
order by 倉庫,日期
--采購通知單上架時效
select bc1.descr_c 倉庫,to_char(c.edittime,'YYYY-MM-DD') 日期,'采購通知單上架' 業務單元,
c.editwho 操作工号,ssu.user_name 操作人,count(c.docno) 箱數,sum(c.toqty_each) 數量,
NVL(ceil(((MAX(case when to_char(c.edittime,'HH24')<12 then c.edittime end ))-
(MIN(case when to_char(c.edittime,'HH24')<12 then c.edittime end )))* 24 * 60),0) +
NVL(ceil(((MAX(case when to_char(c.edittime,'HH24')>12 then c.edittime end ))-
(MIN(case when to_char(c.edittime,'HH24')>12 then c.edittime end )))* 24 * 60),0) "總耗時/分",
MIN(case when to_char(c.edittime,'HH24')<12 then to_char(c.edittime,'YYYY-MM-DD HH24:MI:SS') end ) 上午上架開始時間,
MAX(case when to_char(c.edittime,'HH24')<12 then to_char(c.edittime,'YYYY-MM-DD HH24:MI:SS') end ) 上午上架結束時間，
NVL(ceil(((MAX(case when to_char(c.edittime,'HH24')<12 then c.edittime end ))-
(MIN(case when to_char(c.edittime,'HH24')<12 then c.edittime end )))* 24 * 60),0) "上午耗時/分",
MIN(case when to_char(c.edittime,'HH24')>12 then to_char(c.edittime,'YYYY-MM-DD HH24:MI:SS') end ) 下午上架開始時間,
MAX(case when to_char(c.edittime,'HH24')>12 then to_char(c.edittime,'YYYY-MM-DD HH24:MI:SS') end ) 下午上架結束時間,
NVL(ceil(((MAX(case when to_char(c.edittime,'HH24')>12 then c.edittime end ))-
(MIN(case when to_char(c.edittime,'HH24')>12 then c.edittime end )))* 24 * 60),0) "下午耗時/分"
from doc_asn_header a left join doc_asn_details b on a.asnno=b.asnno
left join act_transaction_log c on a.asnno=c.docno and b.asnlineno=c.doclineno and b.sku=c.fmsku
left join bas_customer bc1 on bc1.customerid=a.warehouseid and bc1.customer_type='WH'
left join bas_codes bc2 on bc2.code=a.asntype and bc2.codeid='ASN_TYP'
left join SEC_SYS_USER ssu on ssu.user_id=c.editwho
left join bas_sku bs on b.sku=bs.sku and b.customerid=bs.customerid
where 1=1
and a.warehouseid in('WH01','WPJIT','WHCX','WHJX')
and a.asntype='CGRK' and bs.freightclass='ZC'
and a.asnstatus>'00' and a.asnstatus<>'90'
and c.transactiontype='PA' and c.doctype='ASN'
and to_char(c.edittime,'YYYY-MM-DD')>='2020-05-01'
group by bc1.descr_c,to_char(c.edittime,'YYYY-MM-DD'),'上架',
c.editwho,ssu.user_name
order by 倉庫,日期
--退貨單移庫時效
select bc.descr_c 倉庫,to_char(a.edittime,'YYYY-MM-DD') 日期,'退貨單移庫' 業務單元,
a.editwho 操作工号,ssu.user_name 操作人,sum(a.toqty_each) 移庫數量,
NVL(ceil(((MAX(case when to_char(a.edittime,'HH24')<12 then a.edittime end ))-
(MIN(case when to_char(a.edittime,'HH24')<12 then a.edittime end )))* 24 * 60),0) +
NVL(ceil(((MAX(case when to_char(a.edittime,'HH24')>12 then a.edittime end ))-
(MIN(case when to_char(a.edittime,'HH24')>12 then a.edittime end )))* 24 * 60),0) "總耗時/分",
MIN(case when to_char(a.edittime,'HH24')<12 then to_char(a.edittime,'YYYY-MM-DD HH24:MI:SS') end ) 上午移庫開始時間,
MAX(case when to_char(a.edittime,'HH24')<12 then to_char(a.edittime,'YYYY-MM-DD HH24:MI:SS') end ) 上午移庫結束時間，
NVL(ceil(((MAX(case when to_char(a.edittime,'HH24')<12 then a.edittime end ))-
(MIN(case when to_char(a.edittime,'HH24')<12 then a.edittime end )))* 24 * 60),0) "上午耗時/分",
MIN(case when to_char(a.edittime,'HH24')>12 then to_char(a.edittime,'YYYY-MM-DD HH24:MI:SS') end ) 下午移庫開始時間,
MAX(case when to_char(a.edittime,'HH24')>12 then to_char(a.edittime,'YYYY-MM-DD HH24:MI:SS') end ) 下午移庫結束時間,
NVL(ceil(((MAX(case when to_char(a.edittime,'HH24')>12 then a.edittime end ))-
(MIN(case when to_char(a.edittime,'HH24')>12 then a.edittime end )))* 24 * 60),0) "下午耗時/分"
from act_transaction_log a left join bas_customer bc on bc.customerid=a.warehouseid and bc.customer_type='WH'
left join SEC_SYS_USER ssu on ssu.user_id=a.editwho
left join bas_sku bs on a.fmsku=bs.sku
where 1=1
and a.transactiontype='MV' and a.doctype='MV'
and a.fmlocation in('LSZP01','WPZP01','CXZP01','JXZP01')
and a.tolocation not in('LSZP01','WPZP01','CXZP01','JXZP01')
and a.warehouseid in('WH01','WHJX','WHCX','WPJIT')
and bs.freightclass='ZC'
and to_char(a.edittime,'YYYY-MM-DD')>='2020-05-01'
group by bc.descr_c,to_char(a.edittime,'YYYY-MM-DD'),'退貨單移庫',
a.editwho,ssu.user_name
order by 倉庫,日期
create or replace procedure SP_REPORTUDF_B2CSHIP
( IN_Warehouse in varchar2,
IN_Date in varchar2,
IN_Language in varchar2,
IN_UserID in varchar2,
IN_IP IN VARCHAR2,
OUT_Return_Code out varchar2)
IS
/*
*****************************************************************
作者： yangxun
日期： 2019/08/25
功能描述： 根據日期按每小時統計B2C接單量和完成單量
******************************************************************
注意 IN_IP 參數是必須的
******************************************************************
*/
BEGIN
delete from TMP_SP_REPORTUDF_B2CSHIP where TMPID = IN_UserID ;
insert into TMP_SP_REPORTUDF_B2CSHIP(TMPID,倉庫名稱,時間,接單數,完成單品訂單，完成多品訂單，合計完成單數，總接單數，總完成單數,IP)
select IN_UserID ,'零售正品倉'倉庫名稱 ,A1.A 時間,A1.B 接單數,A21.D1 完成單品訂單,A22.D2 完成多品訂單,
A2.D 合計完成單數,A3.E 總接單數,A4.F 總完成單數,IN_IP
from
(select a.warehouseid wh1,to_char(a.addtime,'YYYY-MM-DD HH24') A,count(1) B from doc_order_header a
where a.warehouseid='WH01' and a.ordertype='JYCK' and to_char(a.addtime,'YYYY-MM-DD')=IN_Date and a.sostatus<>'90'
group by a.warehouseid,to_char(a.addtime,'YYYY-MM-DD HH24')
order by to_char(a.addtime,'YYYY-MM-DD HH24'))A1
FULL OUTER JOIN
(select a.warehouseid wh2,to_char(a.edittime,'YYYY-MM-DD HH24') C,count(1) D from doc_order_header a
where a.warehouseid='WH01' and a.ordertype='JYCK' and to_char(a.edittime,'YYYY-MM-DD')=IN_Date and a.sostatus='99'
group by a.warehouseid,to_char(a.edittime,'YYYY-MM-DD HH24')
order by to_char(a.edittime,'YYYY-MM-DD HH24'))A2 on A1.wh1=A2.wh2 and A1.A=A2.C
FULL OUTER JOIN
(select a.warehouseid wh21,to_char(a.edittime,'YYYY-MM-DD HH24') C1,count(1) D1 from doc_order_header a
where a.warehouseid='WH01' and a.ordertype='JYCK' and to_char(a.edittime,'YYYY-MM-DD')=IN_Date
and a.sostatus='99' and a.singlematch='Y'
group by a.warehouseid,to_char(a.edittime,'YYYY-MM-DD HH24')
order by to_char(a.edittime,'YYYY-MM-DD HH24'))A21 on A2.wh2=A21.wh21 and A2.C=A21.C1
FULL OUTER JOIN
(select a.warehouseid wh22,to_char(a.edittime,'YYYY-MM-DD HH24') C2,count(1) D2 from doc_order_header a
where a.warehouseid='WH01' and a.ordertype='JYCK' and to_char(a.edittime,'YYYY-MM-DD')=IN_Date
and a.sostatus='99' and a.singlematch='N'
group by a.warehouseid,to_char(a.edittime,'YYYY-MM-DD HH24')
order by to_char(a.edittime,'YYYY-MM-DD HH24'))A22 on A2.wh2=A22.wh22 and A2.C=A22.C2
FULL OUTER JOIN
(select a.warehouseid wh3,count(1) E from doc_order_header a
where a.warehouseid='WH01' and a.ordertype='JYCK' and to_char(a.addtime,'YYYY-MM-DD')=IN_Date and a.sostatus<>'90'
group by a.warehouseid)A3 on A1.wh1=A3.wh3
FULL OUTER JOIN
(select a.warehouseid wh4,count(1) F from doc_order_header a
where a.warehouseid='WH01' and a.ordertype='JYCK' and to_char(a.edittime,'YYYY-MM-DD')=IN_Date and a.sostatus='99'
group by a.warehouseid )A4 on A2.wh2=A4.wh4
order by A1.A;
commit;
OUT_Return_Code := '000#報表資料導出成功.';
return;
END;
/*
DROP TABLE TMP_SP_REPORTUDF_B2CSHIP
create table TMP_SP_REPORTUDF_B2CSHIP
(
TMPID varchar2(30),
倉庫名稱 varchar2(30),
時間 varchar2(30),
接單數 varchar2(30),
完成單品訂單 varchar2(30),
完成多品訂單 varchar2(30),
合計完成單數 varchar2(30),
總接單數 varchar2(30),
總完成單數 varchar2(30),
IP varchar2(30)
)
*/
create or replace procedure SP_REPORTUDF_B2CDP
( IN_Warehouse in varchar2,
IN_DateF in varchar2,
IN_DateT in varchar2,
IN_Language in varchar2,
IN_UserID in varchar2,
IN_IP IN VARCHAR2,
OUT_Return_Code out varchar2)
IS
/*
*****************************************************************
作者： yangxun
日期： 2020/03/20
功能描述： 根據時間段統計店鋪
******************************************************************
注意 IN_IP 參數是必須的
******************************************************************
*/
BEGIN
delete from TMP_SP_REPORTUDF_B2CDP where TMPID = IN_UserID ;
insert into TMP_SP_REPORTUDF_B2CDP(TMPID,SEQ,倉庫,店鋪,發貨量,IP)
select IN_UserID,ROW_NUMBER() OVER(order by t1.倉庫 ,t1.發貨量 desc),
t1.倉庫,t1.店鋪,t1.發貨量,IN_IP
from
(select bc.descr_c 倉庫,t.h_edi_16 店鋪,count(1) 發貨量
from doc_order_header t
left join bas_customer bc on t.warehouseid=bc.customerid and bc.customer_type='WH'
where t.warehouseid in ('WH01','WPJIT') and t.ordertype='JYCK' and t.sostatus='99'
and to_char(t.lastshipmenttime,'YYYY-MM-DD')>=IN_DateF
and to_char(t.lastshipmenttime,'YYYY-MM-DD')<=IN_DateT
group by bc.descr_c,t.h_edi_16)t1;
commit;
OUT_Return_Code := '000#報表資料導出成功！';
return;
END;
/*
select * from TMP_SP_REPORTUDF_B2CDP
DROP TABLE TMP_SP_REPORTUDF_B2CDP
create table TMP_SP_REPORTUDF_B2CDP
(
TMPID varchar2(30),
SEQ int,
倉庫 varchar2(30),
店鋪 varchar2(30),
發貨量 varchar2(30),
IP varchar2(30)
)
*/