-- 菜鸟云打印获取快递单号
CREATE OR REPLACE VIEW VIEW_TBYZCLOUD_GET01 AS
SELECT
'true' AS needEncrypt,
--B.DISTRICT as SENDER_ADDRESS_DISTRICT,
(select codename_c from bas_codes where codeid = 'CUS_TBCN' AND code = (CASE WHEN a.carrierid = 'EMS' then '02' else '01' end)) as SENDER_ADDRESS_DISTRICT,
(select codename_c from bas_codes where codeid = 'CUS_TBCN' AND CODE = '03') as SENDER_ADDRESS_PROVINCE,
--B.CITY AS SENDER_ADDRESS_CITY,
(select codename_c from bas_codes where codeid = 'CUS_TBCN' AND CODE = '04') as SENDER_ADDRESS_CITY,
'' as SENDER_ADDRESS_TOWN,
--b.ADDRESS1 as SENDER_ADDRESS_DETAIL,
(select codename_c from bas_codes where codeid = 'CUS_TBCN' AND code = (CASE WHEN a.carrierid = 'EMS' then '06' else '05' end)) as SENDER_ADDRESS_DETAIL,
case when A.CARRIERID='YTO-MP' then 'YTO'else A.CARRIERID end as cp_code,
'0' as PACKAGE_INFO_VOLUME,
(case when a.h_edi_16='稻草人爱库存旗舰店' then '18616839461'
when a.h_edi_16='爱库存经销代发店' then '18616839461' --经销代发店增加发货号码 20200106 yangxun
when a.h_edi_16='稻草人微折购旗舰店' then '18952266959' --微折购发店增加发货号码 20200826 yangxun
else '020-66358800' end) as SENDER_MOBILE, --爱库存增加发货号码 20190709 yangxun
(case when a.carrierid = 'ZTO' then '' else '' end) as SENDER_PHONE,
--'1' as TRADE_ORDER_LIST,
a.OrderNo as TRADE_ORDER_LIST,
a.OrderType as trade_order_product_type,
(CASE WHEN a.C_Tel1 IS NOT NULL THEN a.C_Tel1
WHEN a.C_Tel2 IS NOT NULL THEN a.C_Tel2 ELSE '0' END) as RECIPIENT_MOBILE,
(CASE WHEN a.C_Tel1 IS NOT NULL THEN a.C_Tel1
WHEN a.C_Tel2 IS NOT NULL THEN a.C_Tel2 ELSE '0' END) as RECIPIENT_PHONE,--20200529 修改电话优先取C_Tel1
(select codename_c from bas_codes where codeid = 'CUS_TBCN' AND code =
(CASE WHEN a.h_edi_16='稻草人爱库存旗舰店' then '09'
WHEN a.carrierid='EMS' then '12'
WHEN a.h_edi_16='稻草人金糠专卖店' then '10'
WHEN a.h_edi_16='稻草人好几百专卖店' then '10'
WHEN a.h_edi_16='稻草人微折购旗舰店' then '11'else '07' end)) as SENDER_NAME,
a.c_contact as RECIPIENT_NAME,
'1' as trade_order_package_count,
(select codename_c from bas_codes where codeid = 'CUS_TBCN' AND CODE = '09') as trade_order_package_item_name,
'{ "TIMED-DELIVERY":{ "value": "SEVERAL-DAYS" }}' as LOGISTICS_SERVICES,
'1' as PACKAGE_INFO_WEIGHT,
'819984768' as STORE_CODE,
a.OrderNo as PACKAGE_INFO_ID,
a.OrderNO as OBJECT_ID,
A.C_ADDRESS3 AS RECIPIENT_ADDRESS_DISTRICT,
A.c_province AS RECIPIENT_ADDRESS_PROVINCE,
'' as RECIPIENT_ADDRESS_TOWN,
a.c_city AS RECIPIENT_ADDRESS_CITY,
a.C_Address1 as RECIPIENT_ADDRESS_DETAIL,
'TB' as ORDER_CHANNELS_TYPE,
--case when a.CarrierID = 'HTKY_RM' then '3361760472'
--else '62478430' end as USER_ID,
'363555053' as USER_ID,
a.orderno as orderno,
a.addtime, --,'YTO-DS'
case --when a.CarrierID IN ('YTO') then 'http://cloudprint.cainiao.com/template/standard/101/584' --2联单
when a.CarrierID IN ('YTO') then 'http://cloudprint.cainiao.com/template/standard/290659/26' --1联单
when a.CarrierID IN ('YTO-MP') then 'http://cloudprint.cainiao.com/template/standard/290659/26' --1联单
--when a.CarrierID IN ('ZTO') then 'http://cloudprint.cainiao.com/template/standard/301/189'--2联单
when a.CarrierID IN ('ZTO') then 'http://cloudprint.cainiao.com/template/standard/300336/9'--1联单
--when a.carrierid = 'POSTB' then 'http://cloudprint.cainiao.com/template/standard/801/131' --2联单
when a.carrierid = 'POSTB' then 'http://cloudprint.cainiao.com/template/standard/304351/3' --1联单
when a.carrierid = 'EMS' then 'http://cloudprint.cainiao.com/template/standard/345208/1' --1联单
end as TEMPLATE_URL,
--a.consigneeaddressid as oaid,
case when a.consigneeaddressid='null' then '' else a.consigneeaddressid end as oaid, --20210723
(select d_edi_01 from doc_order_details where orderno = a.orderno and rownum =1) as tid
FROM DOC_Order_Header a
left join BAS_Customer b on b.CustomerID = a.CarrierID and b.Customer_Type = 'OW'
WHERE
1 = 1
--and a.orderno in ('ZPCK21083101738','ZPCK21083101712','ZPCK21083101634','ZPCK21083101625','ZPCK21083101598')
--and a.CarrierID IN('STO','ZJS','YTO','ZTO','HTKY','YUNDA','POSTB')
and a.CarrierID IN('YTO','ZTO','POSTB','EMS','YTO-MP')
And nvl(a.warehouseid,'*') <>'*'
AND a.SOStatus <= '60'
AND nvl(a.soreference5,'*') ='*'
AND nvl(a.udfprintflag1,'N')='N'
--and nvl(a.soreference2,'*') not in ('京东')
--and nvl(a.b_zip,'*')<>'C183'
and a.h_edi_16 not in (select code from bas_codes where codeid in('JDDP','PDDDP','DYDP'))
AND ROWNUM <=500
--and 2 < 1
order by (case when a.waveno <> '*' then 1 else 2 end),a.carrierid
;
-- 拼多多云打印获取快递单号（加密的）
CREATE OR REPLACE VIEW VIEW_PDD_WAYBILL_GET AS
SELECT DISTINCT
CASE WHEN a.carrierid='POSTB' THEN 'YZXB' else a.carrierid end as WP_CODE, --快递公司代码
'广东省' as SENDER_ADDRESS_PROVINCE, --发货人省
'广州市' as SENDER_ADDRESS_CITY, --发货人市
'花都区' as SENDER_ADDRESS_DISTRICT, --发货人区
'花山镇' as SENDER_ADDRESS_TOWN, --发货人镇
'花山镇华侨工业区龙腾路8号' as SENDER_ADDRESS_DETAIL, --发货人详细地址,
'赵明泽' as SENDER_NAME, --发货人名称
'020-66358800' as SENDER_PHONE, --发货方电话号码
'020-66358800' as SENDER_MOBILE, --发货方手机号码
a.ORDERNO as OBJECT_ID, -- 请求ID 固定WMS单号
'PDD' as order_channels_type, --订单渠道平台编码
a.orderno as TRADE_ORDER_LIST, --订单号
a.orderno as PACKAGE_INFO_ID, --包裹id，
/*{
"logistics_services":{
"TIMED-DELIVERY": {
"value": "2"},//时效产品
”INSURE": {
"value": "510.1"}//保价
}
}*/
case when a.carrierid='SF' then '{
"TIMED-DELIVERY": {
"value": "2"},
”INSURE": {
"value": "0"}
}' else '' end as LOGISTICS_SERVICES, --物流服务值 false 如不需要特殊服务，该值为空
'1' as TRADE_ORDER_PACKAGE_COUNT, --数量
'稻草人皮具' as TRADE_ORDER_PACKAGE_ITEM_NAME, --名称 true
'1' as PACKAGE_INFO_VOLUME, --体积 false
'1' as PACKAGE_INFO_WEIGHT, --重量 false
a.c_province AS RECIPIENT_ADDRESS_PROVINCE, --省，收货人地址
a.c_city AS RECIPIENT_ADDRESS_CITY, --市，收货人地址
a.C_Address3 AS RECIPIENT_ADDRESS_DISTRICT, --区，收货人地址
'' AS RECIPIENT_ADDRESS_TOWN, --镇，收货人地址
a.C_Address1 AS RECIPIENT_ADDRESS_DETAIL, --详细地址，收货人地址
a.c_contact AS RECIPIENT_NAME, --收货人姓名 c_contact consigneename
case when NVL(A.C_Tel1, '*') = '*' then a.C_Tel2
else a.C_Tel1
end AS RECIPIENT_MOBILE, --收货人电话
case when NVL(A.C_Tel1, '*') = '*' then a.C_Tel2
else a.C_Tel1
end AS RECIPIENT_PHONE, --固定电话
--a.CarrierAddress1 as TEMPLATE_URL, --云打印标准模板URL
--case when a.carrierid = 'POSTB' then 'http://pinduoduoimg.yangkeduo.com/print_template/2019-04-10/421e3c8163ed3e819ae297428720e9a8.xml' --2联单
case when a.carrierid = 'POSTB' then 'https://file-link.pinduoduo.com/yzxb_one' --1联单
when a.carrierid = 'YTO' then 'https://t16img.yangkeduo.com/mms_static/d70f64c6c96de5330f82597045044884.xml'
when a.carrierid='SF' then 'http://pinduoduoimg.yangkeduo.com/print_template/2019-05-06/e4f71182a33b8aecd48a389607b84ced.xml'
--when a.carrierid='SF' then 'https://file-link.pinduoduo.com/sf_std'
end as TEMPLATE_URL, --云打印标准模板URL
'449819196' as USER_ID,
--'1257646' as USER_ID, --使用者ID（使用电子面单账号的实际商家ID，如存在一个电子面单账号多个店铺使用时，请传入店铺的商家ID） true
'true' as NEED_ENCRYPT,--是否加密true加密；false明文(20201124中午发布启用)
a.orderno as orderno,
a.addtime,
a.SOStatus,
a.udfprintflag1
FROM DOC_Order_Header a
left join BAS_CUSTOMER b ON b.CUSTOMERID=a.WAREHOUSEID AND b.CUSTOMER_TYPE='WH'
WHERE 1 = 1
AND a.SOStatus <='60'
AND a.soreference5 IS NULL
AND nvl(a.udfprintflag1,'N')='N'
and a.h_edi_16 in (select code from bas_codes where codeid = 'PDDDP')
and a.carrierid in ('POSTB','YTO','SF')
--and a.carrierid in ('POSTB','YTO')
AND ROWNUM <= 500
--and a.orderno in ('ZPCK20111303829','ZPCK20111303827')
;
-- 京东无界云打印获取快递单号
CREATE OR REPLACE VIEW IDX_JDNONEXPRESS_WAYBILL AS
SELECT
'1' AS waybillType --运单类型：1普通运单
,'1' AS waybillCount --所需运单的数量，顺丰只能传1，非顺丰快递公司最多只能传99
,'' AS providerId --承运商id (providerId与providerCode两者必填一个)
---,a.carrierid AS providerCode --承运商编码(providerID与providerCode两者必填一个) ---- edit by fmq 180730
,case when a.carrierid= 'POSTB' then 'ZGYZZHDD' else a.carrierid end AS providerCode --承运商编码(providerID与providerCode两者必填一个)
,case when a.carrierid='YTO' then '200133' when a.carrierid='ZTO' then '02071' else '' end AS branchCode --承运商发货网点编码,加盟型快递公司必传
----,'' AS settlementCode --财务结算编码，直营型快递公司必传
,case when a.carrierid= 'POSTB' then '90000011204110' else '' end AS settlementCode --财务结算编码，直营型快递公司必传
,'0010001' AS salePlatform --销售平台 0010001代表京东平台下的订单 0010002天猫、淘宝订单 0030001 其他平台订单
--,a.soreference1 AS platformOrderNo --平台订单号，即pop订单号，如果多订单合并发货，每个订单号之间用“,”逗号分隔，每个订单号最多32位
,(select d_edi_01 from doc_order_details where orderno = a.orderno and rownum =1 ) as platformOrderNo --平台订单号，即pop订单号
,'61258' AS vendorCode --商家编码，是用POP商家ID
,b.udf1 AS customercode -- 门店ID，不同的客户门店ID不同
,a.h_edi_16 AS vendorName --商家名称
,a.soreference1 AS vendorOrderCode --商家自有订单号
--,'' AS fromAddress --京标发货四级地址
,'' as from_provinceId --省/直辖市id
,'广东省' as from_provinceName --省/直辖市名称
,'' as from_cityId --市ID
,'广州市' as from_cityName --市名称
,'' as from_countryId --区/县ID
,'花都区' as from_countryName --区/县名称n
,'' as from_countrysideId --乡镇/街道id N
,'' as from_countrysideName --县镇/街道名称 N
,'花山镇华侨工业区龙腾路8号' as from_address --发货详细地址
,'赵明泽' as from_contact --发货联系人
,'020-66358800' as from_phone --发货人电话
,'020-66358800' as from_mobile --发货人手机
--,'' AS toAddress--京标收货四级地址
,'' as to_provinceId --省/直辖市id
,a.c_province as to_provinceName --省/直辖市名称
,'' as to_cityId --市ID
,a.c_city as to_cityName --市名称
,'' as to_countryId --区/县ID
,a.c_address2 as to_countryName --区/县名称
,'' as to_countrysideId --乡镇/街道id N
,'' as to_countrysideName --县镇/街道名称 N
,a.c_address1 as to_address --收货详细地址
,a.c_Contact as to_contact --收货联系人
,a.c_tel1 as to_phone --收货人电话
,a.c_tel1 as to_mobile --收货人手机
,'0.0' AS weight --重量，单位为千克 两位小数
,'0.0' AS volume --体积，单位为统一为立方厘米 两位小数
--,'服装' AS goodsName --商品名称
--,'0' AS promiseTimeType --承诺时效类型 无时效默认传0
--,'' AS promiseCompleteTime --承诺完成时间，若未承诺时效，则不考虑此字段 2016-08-01 12:00:00
--,'' AS promiseOutStockTime --计划出库时间
,'0' AS payType --付款方式0-在线支付 目前暂时不支持货到付款业务
--,'0' AS goodsMoney --商品金额 两位小数
,'0' AS shouldPayMoney --代收金额 两位小数
,'false' AS needGuarantee --是否要保价（系统暂不开放报价业务）
,'0.0' AS guaranteeMoney --保价金额 两位小数 非保价默认传0.0
,a.orderno
--,'0' AS receiveTimeType --收货时间类型，0任何时间，1工作日2节假日
--,'' AS warehouseCode --发货仓编码
--,'' AS secondSectionCode --二段码
--,'' AS thirdSectionCode --三段码
--,A.NOTES AS remark --备注
--,'' AS expressPayMethod --快递费付款方式(顺丰必填)
---,'' AS expressType --快件产品类别(顺丰必填)
---,'' AS extendField1 --扩展字段
---,'' AS extendField2
---,'' AS extendField3
---,'' AS extendField4
---,'' AS extendField5
FROM DOC_ORDER_HEADER A
left join bas_codes b on a.h_edi_16 = b.code and b.codeid = 'JDDP'
WHERE 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
and a.sostatus<='60'
and a.soreference5 is null
and nvl(a.carrierzip,'N')='N'
and b.udf1 is not null
and a.carrierid in('YTO','ZTO','POSTB')
AND ROWNUM <= 500
--and a.orderno = 'ZPCK21091402120'
;
CREATE OR REPLACE VIEW IDX_JD_GETORDER AS
SELECT case when a.h_edi_16 = '京东稻草人男包旗舰店' then '020K3028'
when a.h_edi_16 = '京东稻草人配饰旗舰店' then '020K12699'
when a.h_edi_16 = '京东稻草人女包旗舰店' then '020K2565'
else '' end as expressSettlementId,
a.warehouseId,
a.orderno,
'0010001' AS salePlat,
-- '021K923782' AS customerCode,
case when a.h_edi_16 = '京东稻草人男包旗舰店' then '020K3028'
when a.h_edi_16 = '京东稻草人配饰旗舰店' then '020K12699'
when a.h_edi_16 = '京东稻草人女包旗舰店' then '020K2565'
else '' end as customerCode,
a.orderno orderId,
--a.soreference5 AS thrOrderId,
(select D_EDI_01 from doc_order_details where orderno = a.orderno and rownum = 1) as thrOrderId, ---京东平台订单号
nvl(a.h_edi_16, '稻草人') AS senderName,
'广东省广州市花都区花山镇华侨工业区龙腾路8号' AS senderAddress,
'020-66358800' AS senderTel,
'020-66358800' AS senderMobile,
'' AS senderPostcode,
a.c_Contact AS receiveName,
a.c_address1 AS receiveAddress,
a.c_province AS province,
a.c_city AS city,
a.c_country county,
a.c_address3 AS town,
'' AS provinceId,
'' AS cityId,
'' AS countyId,
'' AS townId,
'' AS siteType,
'' AS siteId,
'' AS siteName,
nvl(a.c_tel1, a.c_tel2) AS receiveTel,
nvl(a.c_tel2, a.c_tel1) AS receiveMobile,
a.c_zip AS postcode,
1 as packageCount,
'0' AS weight,
'1' AS vloumLong,
'1' AS vloumWidth,
'1' AS vloumHeight,
'1' AS vloumn,
'箱包' AS description,
CASE WHEN a.SOReference3='COD' THEN 1 ELSE 0 END as collectionValue, --
a.H_EDI_08 as collectionMoney ,--待付金额
'0' AS guaranteeValue,
'' AS guaranteeValueAmount,
'' AS signReturn,
'1' AS aging,
'1' AS transType,
'' AS remark,
'1' AS goodsType,
'0' AS orderType,
'' AS shopCode,
sysdate + 3 AS orderSendTime,
a.warehouseid AS warehouseCode,
'' AS areaProvId,
'' AS areaCityId,
'' AS shipmentStartTime,
'' AS shipmentEndTime,
'' AS idNumber,
'' AS addedService,
'' AS extendField1,
'' AS extendField2,
'' AS extendField3,
'' AS extendField4,
'' AS extendField5,
'' AS senderCompany,
'' AS receiveCompany,
'箱包' AS goods,
'' AS goodsCount,
'' AS promiseTimeType,
'' AS freight
FROM DOC_ORDER_HEADER a
left join bas_customer b on b.customerid = a.warehouseid and b.customer_type = 'WH'
--left join bas_codes c on c.codename_c = a.soreference3 and c.codeid = 'CA_INFO'
where 1=1
and a.sostatus <> '90'
and a.carrierid='JDKD'
and nvl(a.soreference5,'*')= '*'
and nvl(a.carrierzip,'N')='N'
--and a.orderno = 'ZPCK21101308867'
;
-- 京东无界云打印获取打印信息
CREATE OR REPLACE VIEW IDX_JDCLOUD_VIEW AS
SELECT
'778166' as expressSettlementId
-- ,'STANDARD_TEMPLATE' AS expressSettlementId
,a.orderno as orderno
,'1' AS popFlag -- 是否pop订单，1：pop订单；不填或者0：非pop订单
-- (select d_edi_01 from doc_order_details where orderno = a.orderno and rownum = 1) AS orderNo, -- 商城订单号
,'778166' AS ewCustomerCode -- 客户编码
--,'JD' AS cpCode -- 物流公司编码。京东快递：JD
,case when a.carrierid= 'POSTB' then 'ZGYZZHDD' ELSE a.carrierid END AS cpCode -- 物流公司编码。京东快递：JD
,a.orderno||to_char(sysdate,'hh24miss') AS objectId-- 每次调用需要传不同的唯一值
--,'YT3165207452519' as jdWayBillCode
,a.soreference5 AS jdWayBillCode -- 京东运单号
-- ,a.soreference5 as wayBillCode -- 运单号
FROM DOC_ORDER_HEADER A
INNER JOIN BAS_CODES B ON B.CODE = A.H_EDI_16 AND B.CODEID = 'JDDP'
where 1=1
--AND a.SOSTATUS>='63'
AND a.SOStatus<'90'
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
AND nvl(a.edisendflag5,'N')='N'
and nvl(a.soreference5,'*') <>'*'
and a.addtime >trunc(sysdate)-3
AND A.CARRIERID <> 'JDKD'
--and a.orderno = 'ZPCK21091402120'
union all
SELECT
--'778166' as expressSettlementId
case when a.h_edi_16 = '京东稻草人男包旗舰店' then '020K3028'
when a.h_edi_16 = '京东稻草人配饰旗舰店' then '020K12699'
when a.h_edi_16 = '京东稻草人女包旗舰店' then '020K2565'
else '' end as expressSettlementId
-- ,'STANDARD_TEMPLATE' AS expressSettlementId
,a.orderno as orderno
--,(select d_edi_01 from doc_order_details where orderno = a.orderno and rownum = 1) AS orderNo
--,a.soreference1 as orderno
,'1' AS popFlag -- 是否pop订单，1：pop订单；不填或者0：非pop订单
--, (select d_edi_01 from doc_order_details where orderno = a.orderno and rownum = 1) AS orderNo -- 商城订单号
--,'778166' AS ewCustomerCode -- 客户编码
,case when a.h_edi_16 = '京东稻草人男包旗舰店' then '020K3028'
when a.h_edi_16 = '京东稻草人配饰旗舰店' then '020K12699'
when a.h_edi_16 = '京东稻草人女包旗舰店' then '020K2565'
else '' end as ewCustomerCode
--,'JD' AS cpCode -- 物流公司编码。京东快递：JD
,case when a.carrierid= 'JDKD' then 'JD' ELSE a.carrierid END AS cpCode -- 物流公司编码。京东快递：JD
,a.orderno||to_char(sysdate,'hh24miss') AS objectId-- 每次调用需要传不同的唯一值
--,'YT3165207452519' as jdWayBillCode
,a.soreference5 AS jdWayBillCode -- 京东运单号
-- ,a.soreference5 as wayBillCode -- 运单号
FROM DOC_ORDER_HEADER A
INNER JOIN BAS_CODES B ON B.CODE = A.H_EDI_16 AND B.CODEID = 'JDDP'
where 1=1
--AND a.SOSTATUS>='63'
AND a.SOStatus<>'90'
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
AND nvl(a.edisendflag5,'N')='N'
and nvl(a.soreference5,'*') <>'*'
and a.edittime >trunc(sysdate)-3
and a.carrierid = 'JDKD'
--and a.orderno = 'ZPCK21100713181'
;
-- 抖音云打印获取快递单号
CREATE OR REPLACE VIEW VIEW_DYXD_WAYBILL_GET AS
SELECT
'DD_*_*_12027475_*' AS expressSettlementId,
A.warehouseid,
A.orderNo,
'CHN' AS senderCountry,
--赵明泽 020-66358800 广东省广州市花都区花山镇华侨工业区龙腾路8号
'广东省' AS senderProvince,
'广州市' AS senderCity,
'花都区' AS senderDistrict,
'' AS senderStreet,
'花山镇华侨工业区龙腾路8号' AS senderDetailAddress,
'赵明泽' AS senderName,
'020-66358800' AS senderPhone,
'020-66358800' AS senderMobile,
case when a.CarrierID = 'POSTB' then 'youzhengguonei'
when a.CarrierID = 'SF' then 'shunfeng'
when a.CarrierID = 'STO' then 'shentong'
when a.CarrierID = 'ZTO' then 'zhongtong'
when a.CarrierID = 'YTO' then 'yuantong'
when a.CarrierID = 'DBKD' then 'debangwuliu'
when a.CarrierID = 'YUNDA' then 'yunda'
end AS logistics_code,
A.orderno AS order_id,
a.orderno AS pack_id,
'1' AS product_type,
'' AS pay_method,
'' AS pay_amount,
'' AS country_code,
'' AS province_name,
'' AS city_name,
'' AS district_name,
'' AS street_name,
'' AS detail_address,
'CHN' AS receiverCountry,
A.C_Province AS receiverProvince,
A.C_city AS receiverCity,
A.c_Address3 AS receiverDistrict,
A.C_Street AS receiverStreet,
A.C_Address1 AS receiverDetailAddress,
A.C_CONTACT AS receiverName,
NVL (a.C_Tel1,a.C_Tel2) AS receiverPhone,
NVL (a.C_Tel2,a.C_Tel1) AS receiverMobile,
'' AS sender_fetch_time,
'0' AS is_sign_back,
'' AS remark,
'' AS extra,
'' AS total_pack_count,
'稻草人箱包' AS SKU ,
'稻草人箱包' AS item_name,
'' AS currency,
1 AS item_count,
'' AS unit,
'' AS amount,
'' AS weight,
'' AS source_area,
'12027475' AS user_id
FROM DOC_ORDER_HEADER A
left join BAS_CUSTOMER b ON b.CUSTOMERID=a.WAREHOUSEID AND b.CUSTOMER_TYPE='WH'
WHERE 1 = 1
AND a.SOStatus <='60'
AND NVL(a.soreference5,'*') = '*'
AND nvl(a.udfprintflag1,'N')='N'
and a.h_edi_16 in (select code from bas_codes where codeid = 'DYDP')
and a.carrierid in ('POSTB','YTO')
--AND A.OrderNo = 'ZPCK21083102677'
AND ROWNUM <= 500
;
-- 抖音云打印获取打印信息
CREATE OR REPLACE VIEW VIEW_DYXD_GETPRT AS
SELECT
'DD_*_*_12027475_*' AS expressSettlementId,
case when a.CarrierID = 'POSTB' then 'youzhengguonei'
when a.CarrierID = 'SF' then 'shunfeng'
when a.CarrierID = 'STO' then 'shentong'
when a.CarrierID = 'ZTO' then 'zhongtong'
when a.CarrierID = 'YTO' then 'yuantong'
when a.CarrierID = 'DBKD' then 'debangwuliu'
when a.CarrierID = 'YUNDA' then 'yunda'
end AS logistics_code,
SOReference5 AS track_no,
orderno
FROM DOC_ORDER_HEADER A
inner JOIN BAS_CODES B ON B.CODE = A.H_EDI_16 AND B.CODEID = 'DYDP'
WHERE 1=1
AND SOStatus <'90'
AND A.WEIGHTINGFLAG = 'N'
AND UDFPrintFlag1= 'Y'
AND NVL(soreference5,'*') <> '*'
--AND A.ORDERNO = 'ZPCK21083102677'
--AND A.H_EDI_01= 'DYXD'
;
-- 唯品会获取快递单号和大头笔
create or replace view idx_vip_getorder as
sELECT --'1558' as vendor_id,
'24252' as vendor_id,
'972bcfcf' as appkey,
'355DE8A01718CA41CE8870175DF4F2BA' as appsecret,
'F9152B354191EB1B97864CBE7D1D667C5DA0CC18' as accessToken, --20220210
--D3CB57E588E117E4457F53D366E9EE46C41A2F72
a.orderno as OrderNo, --wms订单号
B.D_EDI_01 as order_sn,--VIP订单号
case when a.carrierid='SHUNFENG' THEN 'shunfeng' else a.carrierid end as carrier_code,--承运商ID
'1' as Deliveryno_limit
,a.edisendflag5
FROM DOC_ORDER_HEADER a
LEFT JOIN DOC_ORDER_DETAILS B ON A.ORDERNO = B.ORDERNO
where 1=1
and nvl(a.edisendflag5,'N')='N'
and a.h_edi_16 in ('稻草人唯品会全国仓','稻草人唯品会精品仓')
and a.carrierid in ('YUNDA','SHUNFENG')
and nvl(a.soreference5,'*') = '*'
and a.sostatus<'80'
--and a.orderno='WPCK21020800421'
AND ROWNUM <= 1000
;
create or replace view idx_vip_gpt_orderno_d as
select
t.orderno,
t.SOReference5,
--t.SOReference3
a.d_edi_01 as order_sn,--唯品会订单号
t.SOReference5 as transport_no,--唯品会运单号
1 as box_no ,--订单当前包裹序号
a.sku||'*'||to_char(a.qtyordered_each) as goods_info --商品明细
from DOC_Order_Header t
left join DOC_Order_Details a on a.OrderNo=t.OrderNo
where 1=1
and t.CarrierID in ('YUANTONG','PJBEST','YUNDA','SHUNFENG')
and nvl(t.edisendflag4,'N') = 'N'
--and nvl(t.H_EDI_04,'N')='N'
AND nvl(T.SOReference5,'N')<>'N'
and nvl(t.h_edi_16,'*') = '稻草人唯品会全国仓'
and t.sostatus < '80'
--and t.orderno='WPCK19102800293'
;
create or replace view idx_vip_gpt_orderno_h as
select distinct
t.orderno,
t.SOReference5,
--t.SOReference3
a.d_edi_01 as order_sn,--唯品会订单号
t.SOReference5 as transport_no,--运单号
1 as box_no ,--订单当前包裹序号
case when t.carrierid='SHUNFENG' THEN 'shunfeng' else t.carrierid end as carrier_code,--承运商编码
1 as total_package,--订单包裹总数
'972bcfcf' as appkey,
'355DE8A01718CA41CE8870175DF4F2BA' as appsecret,
'24252' as vendor_id,
--'CFD3AB6B49BE7FF3DEE48088EA0EE80F20B66EFE' as accessToken
--'2B482BCF437D95775D3BBA7A9B75CF350E6492A9' as accessToken
--'6BF791FA6A92D5968EC4D555EDCC7EE4299DE1D6' as accessToken
--'89824CF100BD12347F4726351169727DA1F6A950' as accessToken --20191028
--'1022F9AC3A02C2085FD2D3E0F1C6ED5DBF7A2B68' as accessToken --20200203
--'496D31E7A7AD27B3EBA1004038E6ECC1066EC443' as accessToken --20200220
'948504AB6D4597A54E96FF133000F7F8B90B5E1C' as accessToken --2020518
/*'b843cc06' as appkey,
'9F2C9F78A40B0E9855B0F1669E129B34' as appsecret,
'24252' as vendorid,
'' as accessToken*/
,t.edisendflag4,t.H_EDI_04
from DOC_Order_Header t
left join DOC_Order_Details a on a.OrderNo=t.OrderNo
where 1=1
and t.CarrierID in ('YUANTONG','PJBEST','YUNDA','SHUNFENG')
AND nvl(t.edisendflag4,'N') = 'N'
--and nvl(t.H_EDI_04,'N')='N'
AND nvl(T.SOReference5,'N')<>'N'
and nvl(t.h_edi_16,'*') = '稻草人唯品会全国仓'
and t.sostatus < '80'
--and t.orderno in ('WPCK20031100354')
;
-- 快手获取快递单号和大头笔
CREATE OR REPLACE VIEW IDX_KUAISHOU_EBILLGET AS
SELECT
'2144137783' AS expressSettlementId,
'FLUX' AS organizationid ,
a.warehouseid,
a.orderNo,
'2144137783' AS merchantCode, --必填 商家编码（快手订单取订单信息中的sellerOpenId；线下订单为当前取号快手小店的商家ID）
'1' AS totalPackageQuantity, --必填 包裹总数量（包括母单和子单数），
--加盟型物流公司只能传1；
--直营型物流公司中，邮政和京东建议传1~10，顺丰字母单场景建议传1，余下子单通过补子单接口来获取；
a.orderno AS packageCode, --必填 包裹号，用来实现拆包功能：
--1.同一个交易订单下传递相同的包裹号会返回相同的运单号。
--2.如果需要同一个交易订单号下取不同的运单号需要传递不同的包裹号
'' AS expressProductCode, --物流产品类型"byte_pt" ---'2'
case when a.carrierid = 'YTO' then ''
when a.carrierid = 'POSTB' then '{"oneBillFeeType":"1"}'
when a.carrierid = 'EMS' then '{"oneBillFeeType":"1"}' end AS extData ,--面单扩展信息 （json格式）直营物流公司必填 顺丰： '{"isvClientCode":"0200021039"}' 邮政： '{"oneBillFeeType":"1"}'
'false' AS hasFreightInsurance, --是否有运费险 默认false无
case when a.carrierid = 'YTO' then '200133'
when a.carrierid = 'POSTB' then ''
when a.carrierid = 'EMS' then '' end AS netSiteCode, --网点编码（加盟物流公司必填，直营物流公司非必填）
case when a.carrierid = 'YTO' then '广东省广州市花都区白云机场'
when a.carrierid = 'POSTB' then ''
when a.carrierid = 'EMS' then '' end AS netSiteName, --网网点名称（仅加盟物流公司必填）
a.carrierId AS expressCompanyCode,--必填 快递公司编码
'KUAI_SHOU' AS orderChannel,--必填固定 订单渠道(快手电子面单-订单渠道列表)
'0.0' AS totalPackageLength, --包裹总长度（单位 cm）
'0' AS totalPackageWidth, --包裹总宽度（单位 cm）
'0' AS totalPackageHeight, --包裹总高度（单位 cm）
'0' AS totalPackageWeight, --包裹总重量（单位 cm）
'0' AS totalPackageVolume, --包裹总体积（单位 cm）
'' AS goodsDescription, --大件快递货品描述
'' AS packagingDescription, --大件快递的包装信息
a.notes AS tradeOrderRemark, --订单备注信息(最多500个字符)
'false' AS isSignBack, --默认false无 是否有签回单（默认 false，暂不支持签回单）
'稻草人' AS merchantName,--必填 商家名称（快手订单取订单信息中的sellerNick；线下订单为当前取号快手小店的商家名称）
case when a.carrierid = 'YTO' then ''
when a.carrierid = 'POSTB' then '1100073779049'
when a.carrierid = 'EMS' then '1100088618957' end as settleAccount, -- SF快递必填 客户编码(直营物流公司必传) ---0200021039
case when a.carrierid = 'YTO' then ''
when a.carrierid = 'POSTB' then '1'
when a.carrierid = 'EMS' then '1' end AS payMethod, --支付方式（直营物流公司必填） ---'1'
'' AS payAmount, --到付运费金额（单位 分）
(select d_edi_01 from doc_order_details where orderno = a.orderno and rownum =1) AS tradeOrderCode,--必填 订单编号（包括快手及非快手订单编号，最大支持32个字符长度）
'服装' as itemtitle, --必填 商品名称
1 as itemquantity, --必填 商品数量
----sender
'李正' as sender_name, --必填 发件人姓名
'' as sender_post_code, --非必填
'020-66358800' as sender_telephone, --非必填 发件人电话号码
'020-66358800' as sender_mobile, --必填 发件人手机号码
'' AS sender_countryName, --发件人国家名称
'广东省' as sender_provinceName, --必填 发件人省名称
'广州市' as sender_cityName, --必填 发件人 城市名称
'花都区' AS sender_districtName, --必填 发件人区名称
'' AS sender_streetName, --发件人街道名字
'' as sender_county,
'广东省广州市花都区花山镇龙腾路8号' as sender_detailAddress, --必填 发件人详细地址
'' AS sender_countryCode, --发件人国家编码
'' AS sender_provinceCode, --发件人省编码
'' AS sender_cityCode, --发件人城市编码
'' AS sender_districtCode, --发件人区编码
'' AS sender_streetCode, --发件人街道编码
----receiver
A.c_contact AS receiver_name, --必填 收件人姓名
(CASE WHEN a.C_Tel1 IS NOT NULL THEN a.C_Tel1
WHEN a.C_Tel2 IS NOT NULL THEN a.C_Tel2 ELSE '0' END) AS receiver_mobile, --必填 收件人手机号码
'020-66358800' AS receiver_telephone, --非必填 收件人电话号码
'' AS countryName, --国家名称
a.c_province AS provinceName, --必填 收件人省名称
a.c_city AS cityName, --必填 收件人城市名称
a.c_address3 AS districtName, --必填 收件人区名称
a.c_address2 AS streetName , --收件人街道名字
a.c_address1 AS detailAddress , --必填 收件人详细地址
'' AS countryCode, --收件人国家编码
'' AS provinceCode, --收件人省编码
'' AS cityCode, --收件人城市编码
'' AS districtCode, --收件人区编码
'' AS streetCode, --收件人街道编码
case when a.carrierid = 'YTO' then 'https://s1-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO12.xml' --1联单
when a.carrierid = 'POSTB' then 'https://s2-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO29.xml' --1联单
when a.carrierid = 'EMS' then 'https://s2-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO30.xml' --1联单
end AS templateUrl , --标准模板模板URL
'' AS reserveTime, --要求上门取件时间 （大于当前时间） 格式：1631006864262
a.orderNo AS requestId, --必填 请求唯一ID（ 批量取号时候，列表里面的子请求requestID要保证不重复）
'' as code, --增值服务编码
'' as value --增值服务对应的value
FROM DOC_ORDER_HEADER A
WHERE 1=1
AND a.h_edi_16 in (select code from bas_codes where codeid = 'KSDP')
AND a.SOStatus <= '60'
AND nvl(a.soreference5,'*') ='*'
AND nvl(a.udfprintflag1,'N')='N'
--AND a.orderno IN('ZPCK22052304469')
;
--唯品会MP获取快递单号视图
CREATE OR REPLACE VIEW IDX_VIP_TRANSPORTNOFORVOP AS
SELECT
--IDX_VIP_createTransportNoForVop 唯品会脱敏，获取面单接口
--IDX_VIP_getPrint 唯品会脱敏，获取面单数据接口
--IDX_VIP_getPrint_2 唯品会脱敏，获取面单数据接口,删除面单信息表重置获取定时器
--param CreateTransportNoVopParam 是 参数
'ST36007862' AS expressSettlementId,
A.warehouseid,
A.orderNo,
'ST36007862' as store_id,-- String 是 店铺ID
(select d_edi_01 from doc_order_details where orderno = a.orderno and rownum =1) as order_sn,--String 是 订单号
'1' as package_type,-- Integer 是 包裹类型 1：单包裹 2：多包裹
--packages List<TransportNoVopItem> 是 包裹信息,单包裹可不填写，多包裹需要填写
--goods List<PackageGoodsVop> 否 包裹明细
--d.SKU as barcode, --String 否 barcode
--'' as barcode, --String 否 barcode
-- Convert(decimal(18,0),d.QtyOrdered_Each ) as amount,--Integer 否 amount
--'0' as amount,--Integer 否 amount 临时方案启用
--'' as amount,--Integer 否
case when a.CarrierID ='YTO' then 'yuantong'
when a.CarrierID ='POSTB' then 'youzhengguonei'
when a.CarrierID ='EMS' then 'ems'
end as carriers_code,--承运商
'李正' as name, --发件人姓名
'020-66358800' as tel, --发件人电话
'广东省' as province_name,--发件人省（名称）
'广州市' as city_name, ---市（名称）
'花都区' as region_name,--发件人区（名称）
'花山镇' as town_name,--发件人镇（名称）
'华侨工业区龙腾路8号' as address,--发件人地址
--------------------------------------
'1' as count,--获取运单号数量，默认或不填为1，如果是单件多包裹，可填写>1的值
'1' as id,--第几条数据，从1开始
''as hebao_order_sn_list-- List<String>否 选择的合包订单SN
,'payType' as service_code_01
,'3' as value_01
,'pickupType' as service_code_02
,'1' as value_02
,'master' as service_code_03
,'ed-m-0001' as value_03
FROM DOC_ORDER_HEADER A
WHERE 1=1
AND a.h_edi_16 in (select code from bas_codes where codeid = 'VIPMPDP')
AND a.SOStatus <= '60'
AND nvl(a.soreference5,'*') ='*'
AND nvl(a.udfprintflag1,'N')='N'
AND a.carrierid in ('POSTB','YTO','EMS')
AND a.orderno IN('ZPCK22061404844')
;
--唯品会MP获取打印数据视图
CREATE OR REPLACE VIEW IDX_VIP_GETPRINT_2 AS
SELECT
--header --datahub配置 --datahub配置 请求方系统域名 calledDomain String 否
--header getdate（） getdate（） 请求方请求时间 requestTime Long 是
-- --datahub配置 --datahub配置 使用者ID，MP业务为店铺ID ownerId String 是
-- orderNo orderNo 面单请求ID，每次请求唯一，长度不超过20位 traceId String 是 出库复核环节调用传递为箱号
-- carrierId carrierId 承运商编码 carrierCode String 是
-- 来源表：DOC_ORDER_DELIVERYINFO 运单列表 transportNos List<TransportNo> 是
--transportNos trackingNo trackingNo 运单号 transportNo String 是
--transportNos orderNo orderNo 平台订单号 orderSn String 否
-- "calledDomain": "los-admin.vip.vip.com",
-- "requestTime": 154685412554
'los-admin.vip.vip.com' as calledDomain,
--TO_DATE(SYSDATE, 'YYYY-MM-DD HH:MI:SS') as requestTime,
(sysdate-to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss'))* 24*60*60*1000 AS requestTime,
--按单
'ST36007862' AS expressSettlementId,
a.orderno,
'ST36007862' AS ownerId,
RTRIM(a.orderno) as traceId,
case when a.CarrierID ='YTO' then 'yuantong'
when a.CarrierID ='POSTB' then 'youzhengguonei'
when a.CarrierID ='EMS' then 'ems'
end AS carrierCode, -- 承运商编码
NVL(d.TrackingNo,a.SOReference5) as transportNo, -- 运单号
(select d_edi_01 from doc_order_details where orderno = a.orderno and rownum =1) AS orderSn ,-- 平台订单号
NVL(d.TrackingNo,a.SOReference5) as WAYBILLCODE
FROM DOC_ORDER_HEADER A
left join Doc_Order_Deliveryinfo d on d.orderno=a.orderno
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid ='VIPMPDP')
and a.sostatus <>'90'
and NVL(a.udfprintflag1,'N') = 'Y'
and NVL(a.soreference5,'N') <>'N'
and A.WEIGHTINGFLAG = 'N'
and a.expectedshipmenttime1 > SYSDATE-7
and a.orderno='ZPCK22061404844'
;
-- 菜鸟云打印3个视图 发运订单补打、波次打印、单品复核打印
CREATE OR REPLACE VIEW SO_EXPRESSB AS
Select
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
--JoinudfSKU ('??????',b.OrderNo) as SKU, --4
--(select f.sku||' '||g.sku_group2||' '||g.sku_group3 from doc_order_details f
--left join bas_sku g on f.customerid = g.customerid and g.sku = f.sku
--where f.orderno = b.orderno) as SKU,
--dbms_lob.substr(replace(wm_concat(f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.location),',',';')) as sku,
--cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location as sku,
--dbms_lob.substr(replace(wm_concat(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location),',',';'))as sku,
listagg(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location,';') within GROUP (order by cc.sku) as sku,
--'' as sku,
'' as location, --5
'' as qty, --6
b.userdefine3, --7
a.waveno||'-'||e.seqno||'/'||I.B as wavenoprint, --8
sum(cc.qty_each) as ordqty,
case when c.printdata is not null then
'{"requestID":"001'||b.orderno||'","verson":"1.0","cmd":"print",
"task":{"taskID":"001'||b.orderno||to_char(sysdate,'hh24miss')||'","preview":true,"printer":"",
"previewType":"image","firstDocumentNumber":1,"totalDocumentCount":1,"documents":
[{"documentID":"1111111111","contents":['||c.printdata||']}]}}'
else '' end as CNPRINTPROTOCOL,
--d.puttolocation,
'' as puttolocation ,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
''as DF17,----b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
'http://10.180.184.184:8081/JPG/JDlogo.jpg' as columnanme21,
b.consigneeid,
nvl(a.waveno,'0') as wavno,
'' as 平台订单号
--nvl(SUBSTR(WM_CONCAT(F.D_EDI_01||'#'),1,(INSTR(WM_CONCAT(F.D_EDI_01||'#'),'#')-1)),'0') as 平台订单号 --24
,case when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人女包海鲸店' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人女包海鲸店' then ''
when b.h_edi_16='稻草人男包1688旗舰店' then ''
else B.H_EDI_16 end AS 店铺名称 --25
,case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end AS 快递名称 --26
--,cc.orderlineno
,b.orderno as DOCNO
from doc_order_header b
inner join idx_cn_cloudprint c on b.soreference5=c.deliveryno
left join doc_wave_header a on a.waveno=b.waveno
left join Doc_Wave_Details e on b.waveno=e.waveno and b.orderno=e.orderno
left join act_allocation_details cc on cc.orderno=b.orderno
LEFT JOIN DOC_ORDER_DETAILS F ON cc.ORDERNO=F.ORDERNO and cc.orderlineno=f.orderlineno
---left join idx_puttolight d on d.waveno = b.waveno and d.docno = b.orderno
left join bas_sku g on cc.customerid = g.customerid and cc.sku = g.sku
/*left join (SELECT orderno,orderlineno,sku,location,sum(qty) qty FROM act_allocation_details --WHERE ORDERNO='ZPCK19042901194'
group by orderno,orderlineno,sku,location) h on f.orderno = h.orderno and f.orderlineno = h.orderlineno and f.sku = g.sku*/
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1=1
and nvl(b.h_edi_03,'N') = 'TB'
and b.h_edi_16 not in (select code from bas_codes where codeid in('PDDDP','JDDP','DYDP'))
--and to_char(a.addtime,'YYYY-MM-DD')='2020-03-03'
--and b.ORDERNO = 'ZPCK20030201200'
--and b.sostatus='99'
group by
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
b.userdefine3, --7
a.waveno , --8
--d.puttolocation,
c.printdata,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
---b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
b.consigneeid,
e.seqno,
--cc.sku,
--g.sku_group2,
--g.sku_group3,
--cc.location,
--cc.qty_each,
B.H_EDI_16,
case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end,
I.B
;
CREATE OR REPLACE VIEW WAVE_TBYZ AS
Select
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
--dbms_lob.substr(replace(wm_concat(f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.location),',',';')) as SKU, --4
--f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.qty||' '||h.location as sku,
--dbms_lob.substr(replace(wm_concat(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location),',',';'))as sku,
listagg(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location,';') within GROUP (order by cc.sku) as sku,
'' as location, --5
'' as qty, --6
b.userdefine3, --7
a.waveno||'-'||e.seqno||'/'||I.B as printwaveno, --8
--(select sum(t.qtyallocated_each) from doc_order_details t where t.orderno=b.orderno) as ordqty,--9
sum(cc.qty_each) as ordqty,--9
case when c.printdata is not null then
'{"requestID":"001'||b.orderno||'","verson":"1.0","cmd":"print",
"task":{"taskID":"001'||b.orderno||to_char(sysdate,'hh24miss')||'","preview":true,"printer":"",
"previewType":"image","firstDocumentNumber":1,"totalDocumentCount":1,"documents":
[{"documentID":"1111111111","contents":['||c.printdata||']}]}}'
else '' end as CNPRINTPROTOCOL,--10
--d.picktolocation, --11
'' as picktolocation, --11
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
'http://10.180.184.184:8081/JPG/JDlogo.jpg' as columnanme21,
b.consigneeid,
a.waveno,
e.seqno
,SUBSTR(WM_CONCAT(F.D_EDI_01||'#'),1,(INSTR(WM_CONCAT(F.D_EDI_01||'#'),'#')-1)) as 平台订单号 --25
,case when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人女包海鲸店' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人女包海鲸店' then ''
when b.h_edi_16='稻草人男包1688旗舰店' then ''
else B.H_EDI_16 end AS 店铺名称 --26
,case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end AS 快递名称 --27
--,h.orderlineno
,b.orderno as docno
from doc_wave_header a
left join doc_order_header b on a.waveno=b.waveno
left join idx_cn_cloudprint c on b.soreference5=c.deliveryno
--left join idx_picktolight d on d.waveno = b.waveno and d.docno = b.orderno
left join doc_wave_details e on a.waveno=e.waveno and b.orderno=e.orderno
left join act_allocation_details cc on cc.orderno=b.orderno
left join doc_order_details f on b.orderno = f.orderno and cc.orderlineno=f.orderlineno
left join bas_sku g on f.customerid = g.customerid and f.sku = g.sku
/*left join (SELECT orderno,orderlineno,sku,location,sum(qty) qty FROM act_allocation_details --WHERE ORDERNO='ZPCK19042901194'
group by orderno,orderlineno,sku,location) h on f.orderno = h.orderno and f.orderlineno = h.orderlineno and f.sku = g.sku*/
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1 =1
and nvl(b.h_edi_03,'ZT') = 'TB'
and b.h_edi_16 not in (select code from bas_codes where codeid IN('PDDDP','JDDP','DYDP'))
--AND A.WAVENO='W19040100171'
group by
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
b.userdefine3, --7
a.waveno , --8
--d.picktolocation,
c.printdata,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
b.consigneeid
,e.seqno
,a.ordercount
--,f.sku
--,g.sku_group2
--,g.sku_group3
--,h.location
--,h.qty
,B.H_EDI_16
,case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end
,I.B
--,h.orderlineno
ORDER BY e.seqno
;
CREATE OR REPLACE VIEW SSC_SINGLE AS
Select
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
--dbms_lob.substr(replace(wm_concat(f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.location),',',';')) as SKU, --4
--f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.qty||' '||h.location as sku,
--dbms_lob.substr(replace(wm_concat(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location),',',';')) as SKU,
listagg(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location,';') within GROUP (order by cc.sku) as sku,
'' as location, --5
'' as qty, --6
b.userdefine3, --7
a.waveno||'-'||e.seqno||'/'||(select count(m.orderno) from doc_wave_details m where a.waveno = m.waveno) as wavenoprint , --8
(select sum(t.qtyallocated_each) from doc_order_details t where t.orderno=b.orderno) as ordqty,
case when c.printdata is not null then
'{"requestID":"001'||b.orderno||'","verson":"1.0","cmd":"print",
"task":{"taskID":"001'||b.orderno||to_char(sysdate,'hh24miss')||'","preview":true,"printer":"",
"previewType":"image","firstDocumentNumber":1,"totalDocumentCount":1,"documents":
[{"documentID":"1111111111","contents":['||c.printdata||']}]}}'
else '' end as CNPRINTPROTOCOL,
'' as columnname11,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
case when b.consigneeid = 'JD' THEN 'http://10.180.184.184:8081/JPG/JDlogo.jpg'
WHEN b.consigneeid = 'DB' THEN 'http://10.180.184.184:8081/JPG/DBlogo.jpg'
else '' end as columnanme21,
b.soreference3 as columnanme2,
b.consigneeid,
a.waveno
,case when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人女包海鲸店' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人女包海鲸店' then ''
when b.h_edi_16='稻草人男包1688旗舰店' then ''
else b.h_edi_16 end AS 店铺名称 --25
,case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end AS 快递名称 --26
,b.orderno as docno
,a.carrierid
from doc_wave_header a
left join doc_order_header b on a.waveno=b.waveno
left join idx_cn_cloudprint c on b.soreference5=c.deliveryno
left join Doc_Wave_Details e on b.waveno=e.waveno and b.orderno=e.orderno
left join act_allocation_details cc on cc.orderno=b.orderno
LEFT JOIN DOC_ORDER_DETAILS F ON cc.ORDERNO=F.ORDERNO and cc.orderlineno=f.orderlineno
left join bas_sku g on f.sku = g.sku and f.customerid = g.customerid
--left join (SELECT orderno,orderlineno,sku,location,sum(qty) qty FROM act_allocation_details --WHERE ORDERNO='ZPCK19042901194'
--group by orderno,orderlineno,sku,location) h on f.orderno = h.orderno and f.orderlineno = h.orderlineno and f.sku = h.sku
--LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS m where m.linestatus < '62' GROUP BY WAVENO) I
--ON A.WAVENO=I.A
where 1=1
and nvl(b.h_edi_03,'ZT') = 'TB'
and b.h_edi_16 not in (select code from bas_codes where codeid in('PDDDP','JDDP','DYDP'))
AND B.ORDERNO <>'ZPCK21080511045'
--and b.orderno='ZPCK20030201200'
group by
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
b.soreference3,
b.userdefine3, --7
a.waveno , --8
--d.puttolocation,
c.printdata,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
b.consigneeid,
e.seqno,a.carrierid,
--cc.sku,
--g.sku_group2,
--g.sku_group3,
--cc.location,
--cc.qty_each,
B.H_EDI_16,
case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end
;
--改版后的非预览打印模式
CREATE OR REPLACE VIEW SO_EXPRESSB AS
Select
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
--JoinudfSKU ('??????',b.OrderNo) as SKU, --4
--(select f.sku||' '||g.sku_group2||' '||g.sku_group3 from doc_order_details f
--left join bas_sku g on f.customerid = g.customerid and g.sku = f.sku
--where f.orderno = b.orderno) as SKU,
--dbms_lob.substr(replace(wm_concat(f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.location),',',';')) as sku,
--cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location as sku,
--dbms_lob.substr(replace(wm_concat(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location),',',';'))as sku,
--listagg(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location,';') within GROUP (order by cc.sku) as sku,
--'' as sku,
Join_PK_ByOrder(B.orderno) as SKU,
'' as location, --5
'' as qty, --6
b.userdefine3, --7
a.waveno||'-'||e.seqno||'/'||I.B as wavenoprint, --8
--sum(cc.qty_each) as ordqty,
(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = B.ORDERNO) as ordqty,
case when c.printdata is not null then
'{"requestID":"001'||b.orderno||'","verson":"1.0","cmd":"print",
"task":{"taskID":"001'||b.orderno||to_char(sysdate,'hh24miss')||'","preview":false,"printer":"",
"previewType":"image","firstDocumentNumber":1,"totalDocumentCount":1,"documents":
[{"documentID":"1111111111","contents":['||c.printdata||',
{
"data":{
"PRODUCTCOUNT": "'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = B.ORDERNO)||'",
"WAVEDESCR": "'||a.waveno||'-'||e.seqno||'/'||I.B||'",
"SHOPNAME": "'||case when b.h_edi_16='稻草人男包1688旗舰店' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人云货优选男包店' then ''
else b.h_edi_16 end||'",
"PRODUCTINFO": "'||joinsku_byorder(b.orderno)||'",
"REMARK": "'||case when b.carrierid='POSTB'then '越秀运营 黄嘉鑫 邮政'when b.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'"
},
"templateURL":"http://cloudprint.cainiao.com/template/customArea/15089386/3"
}
]}]}}'
--else '' end as CNPRINTPROTOCOL, --http://cloudprint.cainiao.com/template/customArea/15082441/15
END AS CNPRINTPROTOCOL,
--d.puttolocation,
'' as puttolocation ,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
''as DF17,----b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
'http://10.180.184.184:8081/JPG/JDlogo.jpg' as columnanme21,
b.consigneeid,
nvl(a.waveno,'0') as wavno,
'' as 平台订单号
--nvl(SUBSTR(WM_CONCAT(F.D_EDI_01||'#'),1,(INSTR(WM_CONCAT(F.D_EDI_01||'#'),'#')-1)),'0') as 平台订单号 --24
,case when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人男包1688旗舰店' then ''
else B.H_EDI_16 end AS 店铺名称 --25
,case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end AS 快递名称 --26
--,cc.orderlineno
,b.orderno as DOCNO
from doc_order_header b
inner join idx_cn_cloudprint c on b.soreference5=c.deliveryno
left join doc_wave_header a on a.waveno=b.waveno
left join Doc_Wave_Details e on b.waveno=e.waveno and b.orderno=e.orderno
--left join act_allocation_details cc on cc.orderno=b.orderno
--LEFT JOIN DOC_ORDER_DETAILS F ON cc.ORDERNO=F.ORDERNO and cc.orderlineno=f.orderlineno
---left join idx_puttolight d on d.waveno = b.waveno and d.docno = b.orderno
--left join bas_sku g on cc.customerid = g.customerid and cc.sku = g.sku
/*left join (SELECT orderno,orderlineno,sku,location,sum(qty) qty FROM act_allocation_details --WHERE ORDERNO='ZPCK19042901194'
group by orderno,orderlineno,sku,location) h on f.orderno = h.orderno and f.orderlineno = h.orderlineno and f.sku = g.sku*/
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1=1
and nvl(b.h_edi_03,'N') = 'TB'
and b.h_edi_16 not in (select code from bas_codes where codeid in('PDDDP','JDDP','DYDP'))
--and to_char(a.addtime,'YYYY-MM-DD')='2020-03-03'
--and b.ORDERNO = 'ZPCK21112403347'
--and b.sostatus='99'
/*group by
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
b.userdefine3, --7
a.waveno , --8
--d.puttolocation,
c.printdata,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
---b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
b.consigneeid,
e.seqno,
--cc.sku,
--g.sku_group2,
--g.sku_group3,
--cc.location,
--cc.qty_each,
B.H_EDI_16,
case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end,
I.B,
b.carrierid*/
;
CREATE OR REPLACE VIEW WAVE_TBYZ AS
Select
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
--dbms_lob.substr(replace(wm_concat(f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.location),',',';')) as SKU, --4
--f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.qty||' '||h.location as sku,
--dbms_lob.substr(replace(wm_concat(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location),',',';'))as sku,
--listagg(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location,';') within GROUP (order by cc.sku) as sku,
Join_PK_ByOrder(B.orderno) as SKU,
'' as location, --5
'' as qty, --6
b.userdefine3, --7
a.waveno||'-'||e.seqno||'/'||I.B as printwaveno, --8
--(select sum(t.qtyallocated_each) from doc_order_details t where t.orderno=b.orderno) as ordqty,--9
--sum(cc.qty_each) as ordqty,--9
(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = B.ORDERNO) as ordqty,--9
case when c.printdata is not null then
'{"requestID":"001'||b.orderno||'","verson":"1.0","cmd":"print",
"task":{"taskID":"001'||b.orderno||to_char(sysdate,'hh24miss')||'","preview":false,"printer":"",
"previewType":"image","firstDocumentNumber":1,"totalDocumentCount":1,"documents":
[{"documentID":"1111111111","contents":['||c.printdata||',
{
"data":{
"PRODUCTCOUNT": "'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = B.ORDERNO)||'",
"WAVEDESCR": "'||a.waveno||'-'||e.seqno||'/'||I.B||'",
"SHOPNAME": "'||case when b.h_edi_16='稻草人男包1688旗舰店' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人云货优选男包店' then ''
else b.h_edi_16 end||'",
"PRODUCTINFO": "'||joinsku_byorder(b.orderno)||'",
"REMARK": "'||case when b.carrierid='POSTB'then '越秀运营 黄嘉鑫 邮政' when b.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'",
"CODE":"'||nvl(a.waveno,'0')||'"
},
"templateURL":"http://cloudprint.cainiao.com/template/customArea/15092001/2"
}
]}]}}'
--else '' end as CNPRINTPROTOCOL, --http://cloudprint.cainiao.com/template/customArea/15082441/15 --10
end as CNPRINTPROTOCOL,
--d.picktolocation, --11
'' as picktolocation, --11
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
'http://10.180.184.184:8081/JPG/JDlogo.jpg' as columnanme21,
b.consigneeid,
a.waveno,
e.seqno
--,SUBSTR(WM_CONCAT(F.D_EDI_01||'#'),1,(INSTR(WM_CONCAT(F.D_EDI_01||'#'),'#')-1)) as 平台订单号 --25
,(SELECT D_EDI_01 FROM DOC_ORDER_DETAILS WHERE ORDERNO = B.ORDERNO AND ROWNUM =1 ) as 平台订单号 --25
,case when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人男包1688旗舰店' then ''
else B.H_EDI_16 end AS 店铺名称 --26
,case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end AS 快递名称 --27
--,h.orderlineno
,b.orderno as docno
from doc_wave_header a
left join doc_order_header b on a.waveno=b.waveno
left join idx_cn_cloudprint c on b.soreference5=c.deliveryno
--left join idx_picktolight d on d.waveno = b.waveno and d.docno = b.orderno
left join doc_wave_details e on a.waveno=e.waveno and b.orderno=e.orderno
--left join act_allocation_details cc on cc.orderno=b.orderno
--left join doc_order_details f on b.orderno = f.orderno and cc.orderlineno=f.orderlineno
--left join bas_sku g on f.customerid = g.customerid and f.sku = g.sku
/*left join (SELECT orderno,orderlineno,sku,location,sum(qty) qty FROM act_allocation_details --WHERE ORDERNO='ZPCK19042901194'
group by orderno,orderlineno,sku,location) h on f.orderno = h.orderno and f.orderlineno = h.orderlineno and f.sku = g.sku*/
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1 =1
and nvl(b.h_edi_03,'ZT') = 'TB'
and b.h_edi_16 not in (select code from bas_codes where codeid IN('PDDDP','JDDP','DYDP'))
--AND A.WAVENO='W21112400242'
/*group by
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
b.userdefine3, --7
a.waveno , --8
--d.picktolocation,
c.printdata,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
b.consigneeid
,e.seqno
,a.ordercount
--,f.sku
--,g.sku_group2
--,g.sku_group3
--,h.location
--,h.qty
,B.H_EDI_16
,case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end
,I.B,b.carrierid*/
--,h.orderlineno
ORDER BY e.seqno
;
CREATE OR REPLACE VIEW SSC_SINGLE AS
Select
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
--dbms_lob.substr(replace(wm_concat(f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.location),',',';')) as SKU, --4
--f.sku||' '||g.sku_group2||' '||g.sku_group3||' '||h.qty||' '||h.location as sku,
--dbms_lob.substr(replace(wm_concat(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location),',',';')) as SKU,
--listagg(cc.sku||' '||g.sku_group2||' '||g.sku_group3||' '||cc.qty_each||' '||cc.location,';') within GROUP (order by cc.sku) as sku,
Join_PK_ByOrder(b.orderno) as SKU,
'' as location, --5
'' as qty, --6
b.userdefine3, --7
a.waveno||'-'||e.seqno||'/'||I.B as wavenoprint , --8
(select sum(t.qtyallocated_each) from doc_order_details t where t.orderno=b.orderno) as ordqty,
case when c.printdata is not null then
'{"requestID":"001'||b.orderno||'","verson":"1.0","cmd":"print",
"task":{"taskID":"001'||b.orderno||to_char(sysdate,'hh24miss')||'","preview":false,"printer":"",
"previewType":"image","firstDocumentNumber":1,"totalDocumentCount":1,"documents":
[{"documentID":"1111111111","contents":['||c.printdata||',
{
"data":{
"PRODUCTCOUNT": "'||'商品总数：'||(SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = b.ORDERNO )||'",
"WAVEDESCR": "'||a.waveno||'-'||e.seqno||'/'||I.B||'",
"SHOPNAME": "'||case when b.h_edi_16='稻草人男包1688旗舰店' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人云货优选男包店' then ''
else b.h_edi_16 end||'",
"PRODUCTINFO": "'||joinsku_byorder(b.orderno)||'",
"REMARK": "'||case when b.carrierid='POSTB'then '越秀运营 黄嘉鑫 邮政' when b.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'"
},
"templateURL":"http://cloudprint.cainiao.com/template/customArea/15089386/3"
}
]}]}}'
else '' end as CNPRINTPROTOCOL,
'' as columnname11,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
case when b.consigneeid = 'JD' THEN 'http://10.180.184.184:8081/JPG/JDlogo.jpg'
WHEN b.consigneeid = 'DB' THEN 'http://10.180.184.184:8081/JPG/DBlogo.jpg'
else '' end as columnanme21,
b.soreference3 as columnanme2,
b.consigneeid,
a.waveno
,case when b.h_edi_16='稻草人爱库存旗舰店' then ''
when b.h_edi_16='稻草人云货优选店铺' then ''
when b.h_edi_16='稻草人好衣库旗舰店' then ''
when b.h_edi_16='稻草人聚爱优选旗舰店' then ''
when b.h_edi_16='稻草人微折购旗舰店' then ''
when b.h_edi_16='稻草人男包1688旗舰店' then ''
else b.h_edi_16 end AS 店铺名称 --25
,case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end AS 快递名称 --26
,b.orderno as docno
,a.carrierid
from doc_wave_header a
left join doc_order_header b on a.waveno=b.waveno
left join idx_cn_cloudprint c on b.soreference5=c.deliveryno
left join Doc_Wave_Details e on b.waveno=e.waveno and b.orderno=e.orderno
--left join act_allocation_details cc on cc.orderno=b.orderno
--LEFT JOIN DOC_ORDER_DETAILS F ON cc.ORDERNO=F.ORDERNO and cc.orderlineno=f.orderlineno
--left join bas_sku g on f.sku = g.sku and f.customerid = g.customerid
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
--left join (SELECT orderno,orderlineno,sku,location,sum(qty) qty FROM act_allocation_details --WHERE ORDERNO='ZPCK19042901194'
--group by orderno,orderlineno,sku,location) h on f.orderno = h.orderno and f.orderlineno = h.orderlineno and f.sku = h.sku
--LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS m where m.linestatus < '62' GROUP BY WAVENO) I
--ON A.WAVENO=I.A
where 1=1
and nvl(b.h_edi_03,'ZT') = 'TB'
and b.h_edi_16 not in (select code from bas_codes where codeid in('PDDDP','JDDP','DYDP'))
--AND B.ORDERNO <>'ZPCK21080511045'
--and b.orderno='ZPCK21110507989'
/*group by
b.orderno, ----1
b.soreference1, -----2
b.deliveryno, -----3
b.soreference3,
b.userdefine3, --7
a.waveno , --8
--d.puttolocation,
c.printdata,
b.c_contact,--12
b.c_tel1,
b.c_address1,
b.c_province,--15
b.c_city,
b.c_district,
b.notes,
b.h_edi_08,
b.i_address1,--20
b.consigneeid,
e.seqno,a.carrierid,
--cc.sku,
--g.sku_group2,
--g.sku_group3,
--cc.location,
--cc.qty_each,
B.H_EDI_16,
case when b.carrierid='EMS' then SUBSTR(B.CARRIERNAME,1,3) else SUBSTR(B.CARRIERNAME,1,2) end,
I.B,
b.carrierid*/
;
-- 拼多多打印3个视图 发运订单补打、波次打印、单品复核打印
--拼多多加密后代码
--升级非预览云打印后代码
CREATE OR REPLACE VIEW SO_PDDCLOUDNEW AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
case when a.CARRIERNAME='EMS' then 'EMS'else SUBSTR(a.CARRIERNAME,1,2) end as COLUMNNAME4,
bs.udf2 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
'商品明细：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
joinsku_byorder(a.orderno) as sku,
'{
"cmd": "print",
"requestID": "'||to_char(sysdate,'yyyymmddHH24miss')||a.OrderNo||'",
"version": "1.0",
"task": {
"taskID": "'||to_char(sysdate,'yyyymmddHH24miss')||a.OrderNo||'",
"firstDocumentNumber" : 20,
"totalDocumentCount" : 100,
"preview" : false,
"printer": "",
"previewType":"image",
"documents": [{
"documentID": "965021273",
"contents": ['||b.qrcode||'
,{
"templateURL": "https://pos-file.pinduoduo.com/express-common-no-cache/common/xmltemplate/printtemplate_404d835e69244cb28ee4356de18ac238.xml",
"data": {
"PRODUCTCOUNT":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"WAVEDESCR":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"SHOPNAME":"'||bs.udf2||'",
"PRODUCTINFO":"'||joinsku_byorder(a.orderno)||'",
"REMARK":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'",
"CODE":"'||nvl(a.waveno,'0')||'"
}
}
]
}]
}
}' as CNPRINTPROTOCOL--'||to_char(b.encrypteddata)||'
,a.carrierid
--,c.picktotraceid
,A.ORDERNO AS DOCNO
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and a.soreference5=b.deliveryno
--left join act_allocation_details c on c.orderno=a.orderno
--left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join bas_codes bs on a.h_edi_16=bs.code and bs.codeid='PDDDP'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'PDDDP')
and a.carrierid in ('POSTB','YTO','EMS')
--and a.orderno='ZPCK22033102701'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,bs.udf2,a.waveno,
f.seqno,I.B,a.carrierid,c.picktotraceid,b.qrcode*/
ORDER BY A.WAVENO,F.SEQNO
;
CREATE OR REPLACE VIEW WAVE_PDDCLOUDNEW_YTO AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
case when a.CARRIERNAME='EMS' then 'EMS'else SUBSTR(a.CARRIERNAME,1,2) end as COLUMNNAME4,
bs.udf2 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
'商品明细：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
joinsku_byorder(a.orderno) as sku,
'{
"cmd": "print",
"requestID": "'||to_char(sysdate,'yyyymmddHH24miss')||a.OrderNo||'",
"version": "1.0",
"task": {
"taskID": "'||to_char(sysdate,'yyyymmddHH24miss')||a.OrderNo||'",
"firstDocumentNumber" : 20,
"totalDocumentCount" : 100,
"preview" : false,
"printer": "",
"previewType":"image",
"documents": [{
"documentID": "965021273",
"contents": ['||b.qrcode||'
,{
"templateURL": "https://pos-file.pinduoduo.com/express-common-no-cache/common/xmltemplate/printtemplate_a4707189d3f340c78307ff36559d5f3a.xml",
"data": {
"PRODUCTCOUNT":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"WAVEDESCR":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"SHOPNAME":"'||bs.udf2||'",
"PRODUCTINFO":"'||joinsku_byorder(a.orderno)||'",
"REMARK":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'",
"CODE":"'||nvl(a.waveno,'0')||'"
}
}
]
}]
}
}' as CNPRINTPROTOCOL--'||to_char(b.encrypteddata)||'
,a.carrierid
--,c.picktotraceid
,A.ORDERNO AS DOCNO
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and a.soreference5=b.deliveryno
--left join act_allocation_details c on c.orderno=a.orderno
--left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join bas_codes bs on a.h_edi_16=bs.code and bs.codeid='PDDDP'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'PDDDP')
and a.carrierid in ('YTO')
--and a.orderno='ZPCK22033102701'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,bs.udf2,a.waveno,
f.seqno,I.B,a.carrierid,c.picktotraceid,b.qrcode*/
ORDER BY A.WAVENO,F.SEQNO
;
CREATE OR REPLACE VIEW SSC_PDDNEW AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
case when a.CARRIERNAME='EMS' then 'EMS'else SUBSTR(a.CARRIERNAME,1,2) end as COLUMNNAME4,
bs.udf2 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
'商品明细：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
joinsku_byorder(a.orderno) as sku,
'{
"cmd": "print",
"requestID": "'||to_char(sysdate,'yyyymmddHH24miss')||a.OrderNo||'",
"version": "1.0",
"task": {
"taskID": "'||to_char(sysdate,'yyyymmddHH24miss')||a.OrderNo||'",
"firstDocumentNumber" : 20,
"totalDocumentCount" : 100,
"preview" : false,
"printer": "",
"previewType":"image",
"documents": [{
"documentID": "965021273",
"contents": ['||b.qrcode||'
,{
"templateURL": "https://pos-file.pinduoduo.com/express-common-no-cache/common/xmltemplate/printtemplate_404d835e69244cb28ee4356de18ac238.xml",
"data": {
"PRODUCTCOUNT":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"WAVEDESCR":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"SHOPNAME":"'||bs.udf2||'",
"PRODUCTINFO":"'||joinsku_byorder(a.orderno)||'",
"REMARK":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'",
"CODE":"'||nvl(a.waveno,'0')||'"
}
}
]
}]
}
}' as CNPRINTPROTOCOL--'||to_char(b.encrypteddata)||'
,a.carrierid
--,c.picktotraceid
,A.ORDERNO AS DOCNO
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and a.soreference5=b.deliveryno
--left join act_allocation_details c on c.orderno=a.orderno
--left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join bas_codes bs on a.h_edi_16=bs.code and bs.codeid='PDDDP'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'PDDDP')
and a.carrierid in ('POSTB','YTO','EMS')
--and a.orderno='ZPCK22033102701'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,bs.udf2,a.waveno,
f.seqno,I.B,a.carrierid,c.picktotraceid,b.qrcode*/
ORDER BY A.WAVENO,F.SEQNO
;
--京东打印3个视图 发运订单补打、波次打印、单品复核打印
CREATE OR REPLACE VIEW SO_JDCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
Join_PK_ByOrder(a.orderno) as SKU,
--PRE_View 预览 print 打印
'
{
"orderType":"PRE_View",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ]
}
}
' as JDPRINTPROTOCOL
,a.carrierid
--,c.picktotraceid
,'' as aa
,A.ORDERNO AS DOCNO
from doc_order_header a
inner join doc_order_deliveryINFO b on b.orderno = a.orderno AND (B.DELIVERYNO = A.SOREFERENCE5 OR B.TRACKINGNO = A.SOREFERENCE5)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'JD'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
and a.carrierid in ('POSTB','YTO','JDKD')
--and a.orderno='ZPCK20100204368'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,
f.seqno,I.B,a.carrierid,c.picktotraceid,b.qrcode,CC.UDF1,B.CLOUDPRINTDATA
ORDER BY A.WAVENO,F.SEQNO*/
;
CREATE OR REPLACE VIEW WAVE_JDCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
Join_PK_ByOrder(a.orderno) as SKU,
--PRE_View 预览 print 打印
'
{
"orderType":"PRE_View",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ]
}
}
' as JDPRINTPROTOCOL
,a.carrierid
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'JD'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
and a.carrierid in('YTO','POSTB','JDKD')
--and a.orderno='ZPCK20111207803'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,f.seqno,I.B,b.qrcode,CC.UDF1,B.CLOUDPRINTDATA,
b.deliveryno,a.c_city,a.C_Address1,a.C_Address3,a.c_province,a.C_Tel1,a.C_Tel2,b.bigShotCode,b.bigShotName,
b.endBranchCode,b.endBranchName,b.threeSegmentCode,a.singlematch,a.c_contact,a.carrierid
order by f.seqno*/
;
CREATE OR REPLACE VIEW SSC_JDCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
Join_PK_ByOrder(a.orderno) as SKU,
--PRE_View 预览 print 打印
'
{
"orderType":"PRE_View",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ]
}
}
' as JDPRINTPROTOCOL
,a.carrierid
--,c.picktotraceid
,c.traceid
, a.orderno as docno
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_order_packing_summary c on c.orderno = a.orderno
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'JD'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
and a.carrierid in ('POSTB','YTO','JDKD')
--and a.orderno='WPCK20022600001'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,f.seqno,I.B,b.qrcode,
b.deliveryno,a.c_city,a.C_Address1,a.C_Address3,a.c_province,a.C_Tel1,a.C_Tel2,b.bigShotCode,b.bigShotName,CC.UDF1,B.CLOUDPRINTDATA,
b.endBranchCode,b.endBranchName,b.threeSegmentCode,a.singlematch,a.c_contact,a.carrierid,c.picktotraceid*/
;
--改版后直连非预览打印模式
CREATE OR REPLACE VIEW SO_JDCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
Join_PK_ByOrder(a.orderno) as SKU,
--PRE_View 预览 print 打印
case when a.carrierid='JDKD'
THEN
'
{
"orderType":"PRE_View",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ]
}
}
'
else
'
{
"orderType":"print",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ],
"customTempUrl": "https://storage.jd.com/jdl-template/customs-3d521a50-bf14-43f5-a417-f346d57adee4.1634782790817.txt",
"customData": [{
"PRODUCTCOUNT": "'||'商品总数：'||(SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO )||'",
"WAVEDESCR": "'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"SHOPNAME": "'||a.h_edi_16||'",
"PRODUCTINFO": "'||Join_PK_ByOrder(a.orderno)||'",
"REMARK": "'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫' else ''end ||'"
}]
}
}
'end as JDPRINTPROTOCOL
,a.carrierid
--,c.picktotraceid
,'' as aa
,A.ORDERNO AS DOCNO
from doc_order_header a
inner join doc_order_deliveryINFO b on b.orderno = a.orderno AND (B.DELIVERYNO = A.SOREFERENCE5 OR B.TRACKINGNO = A.SOREFERENCE5)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'JD'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
and a.carrierid in ('POSTB','YTO','JDKD')
--and a.orderno='ZPCK20100204368'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,
f.seqno,I.B,a.carrierid,c.picktotraceid,b.qrcode,CC.UDF1,B.CLOUDPRINTDATA
ORDER BY A.WAVENO,F.SEQNO*/
;
CREATE OR REPLACE VIEW WAVE_JDCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
Join_PK_ByOrder(a.orderno) as SKU,
--PRE_View 预览 print 打印
case when a.carrierid='JDKD'
THEN
'
{
"orderType":"PRE_View",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ]
}
}
'
else
'
{
"orderType":"print",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ],
"customTempUrl": "https://storage.jd.com/jdl-template/customs-04563022-a01c-4915-b6c3-3b1762ee5a8e.1634782949399.txt",
"customData": [{
"PRODUCTCOUNT": "'||'商品总数：'||(SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO )||'",
"WAVEDESCR": "'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"SHOPNAME": "'||a.h_edi_16||'",
"PRODUCTINFO": "'||Join_PK_ByOrder(a.orderno)||'",
"REMARK": "'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫' else ''end ||'",
"CODE":"'||nvl(a.waveno,'0')||'"
}]
}
}
'end as JDPRINTPROTOCOL
,a.carrierid
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'JD'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
and a.carrierid in('YTO','POSTB','JDKD')
--and a.orderno='ZPCK20111207803'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,f.seqno,I.B,b.qrcode,CC.UDF1,B.CLOUDPRINTDATA,
b.deliveryno,a.c_city,a.C_Address1,a.C_Address3,a.c_province,a.C_Tel1,a.C_Tel2,b.bigShotCode,b.bigShotName,
b.endBranchCode,b.endBranchName,b.threeSegmentCode,a.singlematch,a.c_contact,a.carrierid*/
order by f.seqno
;
CREATE OR REPLACE VIEW SSC_JDCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
Join_PK_ByOrder(a.orderno) as SKU,
--PRE_View 预览 print 打印
case when a.carrierid='JDKD'
THEN
'
{
"orderType":"PRE_View",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ]
}
}
'
else
'
{
"orderType":"print",
"key": "001'||A.orderno||to_char(sysdate,'hh24miss')||'",
"parameters":{
"printName": "Microsoft XPS Document Writer",
"tempUrl" : "'||CC.UDF1||'",
"customTempUrl" : "",
"customData" : [],
"printData" : [ "'||b.cloudprintdata||'" ],
"customTempUrl": "https://storage.jd.com/jdl-template/customs-3d521a50-bf14-43f5-a417-f346d57adee4.1634782790817.txt",
"customData": [{
"PRODUCTCOUNT": "'||'商品总数：'||(SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO )||'",
"WAVEDESCR": "'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"SHOPNAME": "'||a.h_edi_16||'",
"PRODUCTINFO": "'||Join_PK_ByOrder(a.orderno)||'",
"REMARK": "'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫' else ''end ||'"
}]
}
}
'end as JDPRINTPROTOCOL
,a.carrierid
--,c.picktotraceid
,c.traceid
, a.orderno as docno
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_order_packing_summary c on c.orderno = a.orderno
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'JD'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'JDDP')
and a.carrierid in ('POSTB','YTO','JDKD')
--and a.orderno='WPCK20022600001'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,f.seqno,I.B,b.qrcode,
b.deliveryno,a.c_city,a.C_Address1,a.C_Address3,a.c_province,a.C_Tel1,a.C_Tel2,b.bigShotCode,b.bigShotName,CC.UDF1,B.CLOUDPRINTDATA,
b.endBranchCode,b.endBranchName,b.threeSegmentCode,a.singlematch,a.c_contact,a.carrierid,c.picktotraceid*/
;
-- 抖音打印3个视图 发运订单补打、波次打印、单品复核打印
CREATE OR REPLACE VIEW SO_DYCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
Join_PK_ByOrder(a.orderno) as SKU,
case when a.carrierid = 'POSTB' THEN
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"preview": false,
"printer": "Microsoft XPS Document Writer",
"documents": [
{
"documentID": "'||a.orderno||'",
"contents": [
{
"templateURL": "'||CC.UDF1||'",
"params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1",
"signature": "'||b.signature||'",
"encryptedData": "'||b.cloudprintdata||'",
"config": {
"printlogo": false
}
},
{
"data": {
"productCount": "",
"productInfo": "'||joinsku_byorder(a.orderno)||'",
"shopName": "'||a.h_edi_16||'",
"orderId": "'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"remark": "越秀运营 黄嘉鑫"
},
"templateURL": "https://sf6-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h82ub99li7tr_1631168952.xml"
}
]
}
]
}
}
'
else
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"preview": false,
"printer": "Microsoft XPS Document Writer",
"documents": [
{
"documentID": "'||a.orderno||'",
"contents": [
{
"templateURL": "'||CC.UDF1||'",
"params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1",
"signature": "'||b.signature||'",
"encryptedData": "'||b.cloudprintdata||'",
"config": {
"printlogo": false
}
},
{
"data": {
"productCount": "",
"productInfo": "'||joinsku_byorder(a.orderno)||'",
"shopName": "'||a.h_edi_16||'",
"orderId": "'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"remark": ""
},
"templateURL": "https://sf6-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h82ub99li7tr_1631168952.xml"
}
]
}
]
}
}
'
end as TIKTOKPRINTPROTOCOL,
'12027475'as TIKTOKSHOPID,
'http://192.168.8.6:19192/datahub/EXPRESS_WMSV4/?messageid=CHK_TIKTOK_ACCESSTOKEN&warehouseid=FLUXWMSJSONDB&customerid=EXPRESS' as TIKTOKPRINTURL,
'*' as TIKTOKWAREHOUSEID,
'*' AS TIKTOKCUSTOMERID,
A.ORDERNO AS TIKTOKORDERNO
,a.carrierid
,'' as aa--c.picktotraceid
,A.ORDERNO AS DOCNO
--https://sf1-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h3ph6z7y8cv4_1627445338.xml 旧的模板xml
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'DY'
--INNER join bas_codes DD on DD.code = a.h_edi_16 and DD.codeid = 'DYDP'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'DYDP')
and a.carrierid in ('POSTB','YTO')
--and a.orderno = 'ZPCK21101304321'
--and a.orderno in('ZPCK21072301339','ZPCK21080511045','ZPCK21082702981')
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,CC.UDF1,b.signature,b.cloudprintdata,a.soreference1,
f.seqno,I.B,a.carrierid,c.picktotraceid,b.qrcode*/
--ORDER BY A.WAVENO,F.SEQNO
;
CREATE OR REPLACE VIEW WAVE_DYCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
--listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
--to_clob('{"cmd": "print","requestID": "123458976","version": "1.0","task": {"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'","preview": true,"printer": "Microsoft Print to PDF","documents": [{"documentID": "'||A.orderno||'","contents": [{"templateURL": "'||CC.UDF1||'","params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1","signature": "'||b.signature||'","encryptedData": "'||b.cloudprintdata||'"}]}]}}') as TIKTOKPRINTPROTOCOL
Join_PK_ByOrder(a.orderno) as SKU,
case when a.carrierid = 'POSTB' THEN
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"preview": true,
"printer": "Microsoft XPS Document Writer",
"documents": [
{
"documentID": "'||a.orderno||'",
"contents": [
{
"templateURL": "'||CC.UDF1||'",
"params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1",
"signature": "'||b.signature||'",
"encryptedData": "'||b.cloudprintdata||'",
"config": {
"printlogo": false
}
},
{
"data": {
"productCount": "",
"productInfo": "",
"shopName": "",
"orderId": "",
"remark": ""
},
"templateURL": "https://sf6-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h82ub99li7tr_1631168952.xml"
}
]
}
]
}
}
'
else
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"preview": true,
"printer": "Microsoft XPS Document Writer",
"documents": [
{
"documentID": "'||a.orderno||'",
"contents": [
{
"templateURL": "'||CC.UDF1||'",
"params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1",
"signature": "'||b.signature||'",
"encryptedData": "'||b.cloudprintdata||'",
"config": {
"printlogo": false
}
},
{
"data": {
"productCount": "",
"productInfo": "",
"shopName": "",
"orderId": "",
"remark": ""
},
"templateURL": "https://sf6-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h82ub99li7tr_1631168952.xml"
}
]
}
]
}
}
'
end as TIKTOKPRINTPROTOCOL,
a.carrierid
--,c.picktotraceid
,c.traceid
,A.ORDERNO AS DOCNO
,'12027475'as TIKTOKSHOPID,
'http://192.168.8.6:19192/datahub/EXPRESS_WMSV4/?messageid=CHK_TIKTOK_ACCESSTOKEN&warehouseid=FLUXWMSJSONDB&customerid=EXPRESS' as TIKTOKPRINTURL,
'*' as TIKTOKWAREHOUSEID,
'*' AS TIKTOKCUSTOMERID,
A.ORDERNO AS TIKTOKORDERNO
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or a.soreference5 = b.trackingno)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_order_packing_summary c on c.orderno = a.orderno
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'DY'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'DYDP')
and a.carrierid in ('YTO','POSTB')
--and a.orderno='WPCK20022600001'
--and a.waveno = 'W21101300278'
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,
f.seqno,I.B,a.carrierid,c.picktotraceid,b.qrcode,CC.UDF1,b.signature,b.cloudprintdata*/
--ORDER BY A.WAVENO,F.SEQNO
;
CREATE OR REPLACE VIEW SSC_DYCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
SUBSTR(a.CARRIERNAME,1,2) as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
a.waveno||'-'||f.seqno||'/'||I.B as COLUMNNAME6,
--'商品明细：'|| sum(c.qty_each) as COLUMNNAME7,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
--dbms_lob.substr(replace(wm_concat(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty||' '||c.location),',',';')) as COLUMNNAME8,
/*listagg(c.sku||' '||d.sku_group2||' '||d.sku_group3||' '||c.qty_each||' '||c.location,';') within GROUP (order by c.sku) as sku,
case when a.carrierid = 'POSTB' THEN
to_clob('{"cmd": "print", "requestID": "78", "version": "1.0", "task": { "taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'","preview": false, "printer": "Mis","documents": [ {"documentID": "'||a.orderno||'", "contents": [{"templateURL": "'||CC.UDF1||'", "params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1",
"signature": "'||b.signature||'","encryptedData": "'||b.cloudprintdata||'","config":{"printlogo":false}} ,{ "data": {"productCount": "", "productInfo": "","shopName": "","orderId": "'||a.waveno||'-'||f.seqno||'/'||I.B||'","remark": "越秀运营 黄嘉鑫"}, "templateURL": "https://sf6-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h82ub99li7tr_1631168952.xml"
}]}]}}')
else
to_clob('{ "cmd": "print","requestID":"77","version": "1.0","task": { "taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'","preview": false,"printer": "Mis","documents": [{ "documentID": "'||a.orderno||'","contents": [{ "templateURL": "'||CC.UDF1||'","params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1",
"signature": "'||b.signature||'","encryptedData": "'||b.cloudprintdata||'","config":{"printlogo":false}},{"data": {"productCount": "", "productInfo": "","shopName": "", "orderId": "'||a.waveno||'-'||f.seqno||'/'||I.B||'","remark": "" }, "templateURL": "https://sf6-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h82ub99li7tr_1631168952.xml"
}]}]}}')
end as TIKTOKPRINTPROTOCOL,*/
Join_PK_ByOrder(a.orderno) as SKU,
case when a.carrierid = 'POSTB' THEN
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"preview": false,
"printer": "Microsoft XPS Document Writer",
"documents": [
{
"documentID": "'||a.orderno||'",
"contents": [
{
"templateURL": "'||CC.UDF1||'",
"params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1",
"signature": "'||b.signature||'",
"encryptedData": "'||b.cloudprintdata||'",
"config": {
"printlogo": false
}
},
{
"data": {
"productCount": "",
"productInfo": "'||joinsku_byorder(a.orderno)||'",
"shopName": "'||a.h_edi_16||'",
"orderId": "'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"remark": "越秀运营 黄嘉鑫"
},
"templateURL": "https://sf6-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h82ub99li7tr_1631168952.xml"
}
]
}
]
}
}
'
else
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"preview": false,
"printer": "Microsoft XPS Document Writer",
"documents": [
{
"documentID": "'||a.orderno||'",
"contents": [
{
"templateURL": "'||CC.UDF1||'",
"params": "access_token=41728015-6001-4564-8974-ef17d16f7470'||'&'||'app_key=6932043716463019531'||'&'||'method=logistics.getShopKey'||'&'||'param_json={}'||'&'||'timestamp=2021-07-19 14:10:58'||'&'||'v=2'||'&'||'sign=4ca4f81c-dd41-4616-93e1-81738fc38cc1",
"signature": "'||b.signature||'",
"encryptedData": "'||b.cloudprintdata||'",
"config": {
"printlogo": false
}
},
{
"data": {
"productCount": "",
"productInfo": "'||joinsku_byorder(a.orderno)||'",
"shopName": "'||a.h_edi_16||'",
"orderId": "'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"remark": ""
},
"templateURL": "https://sf6-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h82ub99li7tr_1631168952.xml"
}
]
}
]
}
}
'
end as TIKTOKPRINTPROTOCOL,
'12027475'as TIKTOKSHOPID,
'http://192.168.8.6:19192/datahub/EXPRESS_WMSV4/?messageid=CHK_TIKTOK_ACCESSTOKEN&warehouseid=FLUXWMSJSONDB&customerid=EXPRESS' as TIKTOKPRINTURL,
'*' as TIKTOKWAREHOUSEID,
'*' AS TIKTOKCUSTOMERID,
A.ORDERNO AS TIKTOKORDERNO
,a.carrierid
--,c.picktotraceid
,c.traceid
,A.ORDERNO AS DOCNO
--https://sf1-ttcdn-tos.pstatp.com/obj/logistics-davinci/customerElements/C1h3ph6z7y8cv4_1627445338.xml 旧的模板xml
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
/*left join act_allocation_details c on c.orderno=a.orderno
left join bas_sku d on d.sku=c.sku and d.customerid=a.customerid*/
left join doc_order_packing_summary c on c.orderno = a.orderno
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
left join BAS_Codes CC on get_udfa_parameter(CC.CODE,'_',2) = a.CarrierID and CC.CodeID = 'TM_URL' AND get_udfa_parameter(CC.CODE,'_',1) = 'DY'
--INNER join bas_codes DD on DD.code = a.h_edi_16 and DD.codeid = 'DYDP'
where 1=1
and a.h_edi_16 in (select code from bas_codes where codeid = 'DYDP')
and a.carrierid in ('POSTB','YTO')
--and a.orderno = 'ZPCK21090911762'
--and a.orderno in('ZPCK21090911762','ZPCK21080511045','ZPCK21082702981')
/*group by
a.orderno,a.soreference5,a.waveno,a.CARRIERNAME,a.h_edi_16,a.waveno,CC.UDF1,b.signature,b.cloudprintdata,a.soreference1,
f.seqno,I.B,a.carrierid,c.picktotraceid,b.qrcode
*/ORDER BY A.WAVENO,F.SEQNO
;
-- 快手打印3个视图 发运订单补打、波次打印、单品复核打印
CREATE OR REPLACE VIEW SO_KSCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
case when a.CARRIERNAME='EMS' then 'EMS'else SUBSTR(a.CARRIERNAME,1,2) end as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
Join_PK_ByOrder(a.orderno) as SKU,
case when (a.carrierid = 'POSTB'OR a.carrierid = 'EMS' OR a.carrierid= 'YTO' ) THEN
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"printer": "Microsoft Print to PDF",
"firstDocumentNumber": 10,
"totalDocumentCount": 100,
"documents": [{
"documentID": "'||a.orderno||'",
"waybillCode": "'||a.soreference5||'",
"ksOrderFlag":true,
"contents": [{
"addData": {
"senderInfo": {
"address": {
"cityName": "广州市",
"countryCode": "CHN",
"detailAddress": "花山镇龙腾路8号",
"districtName": "花都区",
"provinceName": "广东省"
},
"contact": {
"mobile": "020-66358800",
"name": "李正"
}
}
},
"encryptedData": "'||b.cloudprintdata||'",
"signature": "'||b.signature||'",
"key": "'||b.QRCODE||'",
"templateURL": "'||case when a.carrierid = 'YTO' then 'https://s1-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO12.xml'
when a.carrierid = 'POSTB' then 'https://s2-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO29.xml'
when a.carrierid = 'EMS' then 'https://s2-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO30.xml' end ||'",
"ver": "waybill_print_secret_version_1"
},
{"customData":{
"key1":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"key2":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"key3":"'||a.h_edi_16||'",
"key4":"'||joinsku_byorder(a.orderno)||'",
"key5":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'",
"waybillCode":"'||nvl(a.waveno,'0')||'"
},
"templateURL": "https://s1-11586.kwimgs.com/kos/nlav11586/template/custom/EBCT-EBCTO7413.xml"
}
]
}]
}
}
'
end as KWAIPRINTPROTOCOL,
A.ORDERNO AS DOCNO
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1=1
--and a.h_edi_16 in (select code from bas_codes where codeid = 'KSDP')
and a.carrierid in ('POSTB','YTO','EMS')
and a.orderno IN('ZPCK22050506820')
;
CREATE OR REPLACE VIEW WAVE_KSCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
case when a.CARRIERNAME='EMS' then 'EMS'else SUBSTR(a.CARRIERNAME,1,2) end as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
Join_PK_ByOrder(a.orderno) as SKU,
case when (a.carrierid = 'POSTB'OR a.carrierid = 'EMS' OR a.carrierid= 'YTO' ) THEN
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"printer": "Microsoft Print to PDF",
"firstDocumentNumber": 10,
"totalDocumentCount": 100,
"documents": [{
"documentID": "'||a.orderno||'",
"waybillCode": "'||a.soreference5||'",
"ksOrderFlag":true,
"contents": [{
"addData": {
"senderInfo": {
"address": {
"cityName": "广州市",
"countryCode": "CHN",
"detailAddress": "花山镇龙腾路8号",
"districtName": "花都区",
"provinceName": "广东省"
},
"contact": {
"mobile": "020-66358800",
"name": "李正"
}
}
},
"encryptedData": "'||b.cloudprintdata||'",
"signature": "'||b.signature||'",
"key": "'||b.QRCODE||'",
"templateURL": "'||case when a.carrierid = 'YTO' then 'https://s1-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO12.xml'
when a.carrierid = 'POSTB' then 'https://s2-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO29.xml'
when a.carrierid = 'EMS' then 'https://s2-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO30.xml' end ||'",
"ver": "waybill_print_secret_version_1"
},
{"customData":{
"key1":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"key2":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"key3":"'||a.h_edi_16||'",
"key4":"'||joinsku_byorder(a.orderno)||'",
"key5":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'",
"waybillCode":"'||nvl(a.waveno,'0')||'"
},
"templateURL": "https://s1-11586.kwimgs.com/kos/nlav11586/template/custom/EBCT-EBCTO7560.xml"
}
]
}]
}
}
'
end as KWAIPRINTPROTOCOL,
A.ORDERNO AS DOCNO
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1=1
--and a.h_edi_16 in (select code from bas_codes where codeid = 'KSDP')
and a.carrierid in ('POSTB','YTO','EMS')
and a.waveno='W22050500221'
ORDER BY A.WAVENO,F.SEQNO
;
CREATE OR REPLACE VIEW SSC_KSCLOUD AS
select
a.orderno,
a.soreference5 as COLUMNNAME2,
a.waveno,
case when a.CARRIERNAME='EMS' then 'EMS'else SUBSTR(a.CARRIERNAME,1,2) end as COLUMNNAME4,
a.h_edi_16 as COLUMNNAME5,
'商品明细：'|| (SELECT SUM(QTYORDERED_EACH) FROM DOC_ORDER_DETAILS WHERE ORDERNO = A.ORDERNO ) as COLUMNNAME7,
Join_PK_ByOrder(a.orderno) as SKU,
case when (a.carrierid = 'POSTB'OR a.carrierid = 'EMS' OR a.carrierid= 'YTO' ) THEN
'
{
"cmd": "print",
"requestID": "123458976",
"version": "1.0",
"task": {
"taskID": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"printer": "Microsoft Print to PDF",
"firstDocumentNumber": 10,
"totalDocumentCount": 100,
"documents": [{
"documentID": "'||a.orderno||'",
"waybillCode": "'||a.soreference5||'",
"ksOrderFlag":true,
"contents": [{
"addData": {
"senderInfo": {
"address": {
"cityName": "广州市",
"countryCode": "CHN",
"detailAddress": "花山镇龙腾路8号",
"districtName": "花都区",
"provinceName": "广东省"
},
"contact": {
"mobile": "020-66358800",
"name": "李正"
}
}
},
"encryptedData": "'||b.cloudprintdata||'",
"signature": "'||b.signature||'",
"key": "'||b.QRCODE||'",
"templateURL": "'||case when a.carrierid = 'YTO' then 'https://s1-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO12.xml'
when a.carrierid = 'POSTB' then 'https://s2-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO29.xml'
when a.carrierid = 'EMS' then 'https://s2-11586.kwimgs.com/kos/nlav11586/EBST-EBSTO30.xml' end ||'",
"ver": "waybill_print_secret_version_1"
},
{"customData":{
"key1":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"key2":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"key3":"'||a.h_edi_16||'",
"key4":"'||joinsku_byorder(a.orderno)||'",
"key5":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end ||'",
"waybillCode":"'||nvl(a.waveno,'0')||'"
},
"templateURL": "https://s1-11586.kwimgs.com/kos/nlav11586/template/custom/EBCT-EBCTO7413.xml"
}
]
}]
}
}
'
end as KWAIPRINTPROTOCOL,
A.ORDERNO AS DOCNO
from doc_order_header a
inner join doc_order_deliveryinfo b on b.orderno=a.orderno and (a.soreference5=b.deliveryno or b.trackingno = a.soreference5)
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1=1
--and a.h_edi_16 in (select code from bas_codes where codeid = 'KSDP')
and a.carrierid in ('POSTB','YTO','EMS')
and a.orderno IN('ZPCK22050506820')
;
-- 唯品顺丰打印3个视图 发运订单补打、波次打印、单品复核打印
CREATE OR REPLACE VIEW SO_VIPSF AS
select
r.trackingno as 唯品订单号1, --25
r.deliveryday as SF配送时间要求2, --67
a.SOReference5 as 快递号3, --14
a.ConsigneeName as 收货人4, --5
a.C_Tel1 as 电话5, --9
r.consigneemobile as SF电话6, --68
a.C_Address1 as 地址7, --6
r.pickcode as SF三段码8, --69
r.codemapping as SF入港映射码9,-- 70
r.prepackagelabel as SF预10, --71
r.changelabel as SF换11, --72
'D:\WMS_VIPSF\plugins\com.flux.wms.print_3.0.0\ReportModelCustom\flux\'||r.userdefine4||'.png' as WPH顺丰时效12, --65
r.qrcode as SF二维码13, --66
r.goodsinfo as SF商品信息14, --73
r.deliverytype as SF运输工具15, --74
dbms_lob.substr(replace(wm_concat(b.sku||' '||c.qty||' '||c.location),',',';')) as sku16,
a.orderno ,
m.waveno||'-'||n.seqno||'/'||I.B as printwaveno18,
n.seqno as wavenoseqno19,
r.placetogather as YUNDA集包地20 ---75
from DOC_Order_Header a
left join ACT_Allocation_Details c on c.OrderNo=a.OrderNo
left join DOC_Order_Details b on b.OrderNo=a.OrderNo and c.OrderLineNO=b.OrderLineNo
left join DOC_ORDER_DELIVERYINFO r on r.orderno=a.orderno
left join DOC_WAVE_HEADER m on a.waveno = m.waveno
left join DOC_WAVE_Details n on m.waveno=n.waveno and a.orderno=n.orderno
left join (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON m.waveno=I.A
where 1 = 1
and a.sostatus <> '90' and nvl(a.h_edi_16,'*') = '稻草人唯品会全国仓'
and a.carrierid in('SHUNFENG','YUNDA')
group by
r.trackingno,
r.deliveryday,
a.SOReference5,
a.ConsigneeName,
a.C_Tel1,
r.consigneemobile,
a.C_Address1,
r.pickcode,
r.codemapping,
r.prepackagelabel,
r.changelabel,
r.userdefine4,
r.qrcode,
r.goodsinfo,
r.deliverytype,
a.orderno,
m.waveno||'-'||n.seqno||'/'||I.B,
n.seqno,
r.placetogather
;
CREATE OR REPLACE VIEW WAVE_VIPSF AS
select
r.trackingno as 唯品订单号1, --25
r.deliveryday as SF配送时间要求2, --67
a.SOReference5 as 快递号3, --14
a.ConsigneeName as 收货人4, --5
a.C_Tel1 as 电话5, --9
r.consigneemobile as SF电话6, --68
a.C_Address1 as 地址7, --6
r.pickcode as SF三段码8, --69
r.codemapping as SF入港映射码9,-- 70
r.prepackagelabel as SF预10, --71
r.changelabel as SF换11, --72
'D:\WMS_VIPSF\plugins\com.flux.wms.print_3.0.0\ReportModelCustom\flux\'||r.userdefine4||'.png' as WPH顺丰时效12, --65
r.qrcode as SF二维码13, --66
r.goodsinfo as SF商品信息14, --73
r.deliverytype as SF运输工具15, --74
dbms_lob.substr(replace(wm_concat(b.sku||' '||c.qty||' '||c.location),',',';')) as sku16,
a.orderno,
m.waveno||'-'||n.seqno||'/'||I.B as printwaveno18,
n.seqno as wavenoseqno19,
m.waveno,
r.placetogather as YUNDA集包地21 ---75
from DOC_Order_Header a
left join ACT_Allocation_Details c on c.OrderNo=a.OrderNo
left join DOC_Order_Details b on b.OrderNo=a.OrderNo and c.OrderLineNO=b.OrderLineNo
left join DOC_ORDER_DELIVERYINFO r on r.orderno=a.orderno
left join DOC_WAVE_HEADER m on a.waveno = m.waveno
left join DOC_WAVE_Details n on m.waveno=n.waveno and a.orderno=n.orderno
left join (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON m.waveno=I.A
where 1 = 1
and a.sostatus <> '90' and nvl(a.h_edi_16,'*') = '稻草人唯品会全国仓'
and a.carrierid in('SHUNFENG','YUNDA')
group by
r.trackingno,
r.deliveryday,
a.SOReference5,
a.ConsigneeName,
a.C_Tel1,
r.consigneemobile,
a.C_Address1,
r.pickcode,
r.codemapping,
r.prepackagelabel,
r.changelabel,
r.userdefine4,
r.qrcode,
r.goodsinfo,
r.deliverytype,
a.orderno,
m.waveno||'-'||n.seqno||'/'||I.B,
n.seqno,
m.waveno,
r.placetogather
order by n.seqno
;
-- 业务规则 -- 报表数据源自定义 -- 出库复核装箱清单（根据客户端自定义）
SELECT a.OrderNo, a.PickToTraceID as TRACEID, a.SKU, sum(a.Qty_Each) as QTY,
sum(g.GrossWeight) as ORDERPACKINGWEIGHT,sum(g.Cubic) as CUBIC,d.descr as UOM,
b.CONSIGNEEID,f.Descr_C as CONSIGNEENAME,c.Descr_C as SKUDESCRC,c.Descr_E as SKUDESCRE,
c.GrossWeight as SKUGROSSWEIGHT ,r.trackingno as DF01, r.deliveryday as DF02,
b.SOReference5 as DF03, b.ConsigneeName as DF04, b.C_Tel1 as DF05,
r.consigneemobile as DF06, b.C_Address1 as DF07, r.pickcode as DF08,
r.codemapping as DF09,r.prepackagelabel as DF10, r.changelabel as DF11,
'D:\WMS_VIPSF\plugins\com.flux.wms.print_3.0.0\ReportModelCustom\flux\'||r.userdefine4||'.png' as DF12,
r.qrcode as DF13, r.goodsinfo as DF14, r.deliverytype as DF15,
dbms_lob.substr(replace(wm_concat(bb.sku||' '||a.qty||' '||a.location),',',';')) as DF16,b.waveno as DF17,
r.placetogather as DF18
from ACT_Allocation_Details a
LEFT OUTER JOIN doc_order_header b ON a.OrderNo = b.OrderNo
LEFT OUTER JOIN BAS_SKU c ON a.SKU = c.SKU and a.CUSTOMERID = c.CUSTOMERID
LEFT OUTER JOIN DOC_Wave_Details w ON w.OrderNo = a.OrderNo
LEFT OUTER JOIN DOC_Wave_Header wh ON wh.WaveNo = w.WaveNo
LEFT OUTER JOIN view_uom d ON d.uom = 'EA' and c.packid = d.packid
LEFT OUTER JOIN BAS_Customer f ON f.CUSTOMERID = b.CONSIGNEEID and f.Customer_Type='CO'
LEFT OUTER JOIN DOC_Order_Packing_Summary g ON a.OrderNo=g.OrderNo and a.PickToTraceID=g.TraceID
LEFT OUTER JOIN DOC_Order_Details bb on b.OrderNo=bb.OrderNo and a.OrderLineNO=bb.OrderLineNo
LEFT OUTER JOIN DOC_ORDER_DELIVERYINFO r on r.orderno=b.orderno
where a.packflag='Y' and a.PickToTraceID = '999999999999'
group by a.OrderNo,a.PickToTraceID,a.SKU,d.descr,b.CONSIGNEEID,f.Descr_C,c.Descr_C,c.Descr_E,
c.GrossWeight,r.trackingno, r.deliveryday, b.SOReference5, b.ConsigneeName,
b.C_Tel1, r.consigneemobile, b.C_Address1, r.pickcode, r.codemapping,r.prepackagelabel,
r.changelabel, r.userdefine4, r.qrcode, r.goodsinfo, r.deliverytype,b.waveno,r.placetogather
-- 唯品会MP打印3个视图 发运订单补打、波次打印、单品复核打印
CREATE OR REPLACE VIEW SO_VIPCLOUND AS
SELECT
a.waveno, --03
a.orderno,--08
'{
"actionType": "PRINT",
"printerName": "",
"traceId": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"storeId": "'||'ST36007862'||'",
"printParamDTO": {
"templateUrl": "'||b.cloudPrintSTDUrl||'",
"printDatas": [{
"printData": '||b.cloudPrintData||',
"customData": {
"ext1":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"ext2":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"ext3":"'||a.h_edi_16||'",
"ext4":"'||joinsku_byorder(a.orderno)||'",
"ext5":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end||'",
"ext6":"'||'已验视'||'"
}
}]
},
"senderData": {
"name": "tester"
}
}'
as VIPSPRINTPROTOCOL
From doc_order_header a
inner JOIN Doc_Order_Deliveryinfo b ON b.TrackingNo = a.SOReference5
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1=1
AND a.h_edi_16 in (select code from bas_codes where codeid = 'VIPMPDP')
AND a.carrierid in ('POSTB','YTO','EMS')
AND a.SOStatus <>'90'
--AND a.orderno='ZPCK22061003336'
;
CREATE OR REPLACE VIEW WAVE_VIPCLOUND AS
SELECT
a.waveno, --03
a.orderno,--08
'{
"actionType": "PRINT",
"printerName": "",
"traceId": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"storeId": "'||'ST36007862'||'",
"printParamDTO": {
"templateUrl": "'||b.cloudPrintSTDUrl||'",
"printDatas": [{
"printData": '||b.cloudPrintData||',
"customData": {
"ext1":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"ext2":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"ext3":"'||a.h_edi_16||'",
"ext4":"'||joinsku_byorder(a.orderno)||'",
"ext5":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end||'",
"ext6":"'||'已验视'||'",
"ext7":"'||NVL(a.waveno,'0')||'"
}
}]
},
"senderData": {
"name": "tester"
}
}'
as VIPSPRINTPROTOCOL
From doc_order_header a
inner JOIN Doc_Order_Deliveryinfo b ON b.TrackingNo = a.SOReference5
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1=1
AND a.h_edi_16 in (select code from bas_codes where codeid = 'VIPMPDP')
AND a.carrierid in ('POSTB','YTO','EMS')
AND a.SOStatus <>'90'
--AND a.waveno='W22061300189'
ORDER BY A.WAVENO,F.SEQNO
;
CREATE OR REPLACE VIEW SSC_VIPCLOUND AS
SELECT
a.waveno, --03
a.orderno,--08
'{
"actionType": "PRINT",
"printerName": "",
"traceId": "'||a.orderno||to_char(sysdate,'hh24miss')||'",
"storeId": "'||'ST36007862'||'",
"printParamDTO": {
"templateUrl": "'||b.cloudPrintSTDUrl||'",
"printDatas": [{
"printData": '||b.cloudPrintData||',
"customData": {
"ext1":"'||'商品总数：'||(SELECT SUM(QTY_EACH) FROM ACT_ALLOCATION_DETAILS WHERE ORDERNO = A.ORDERNO)||'",
"ext2":"'||a.waveno||'-'||f.seqno||'/'||I.B||'",
"ext3":"'||a.h_edi_16||'",
"ext4":"'||joinsku_byorder(a.orderno)||'",
"ext5":"'||case when a.carrierid='POSTB'then '越秀运营 黄嘉鑫邮政' when a.carrierid='EMS'then '越秀运营 黄嘉鑫 EMS' else ''end||'",
"ext6":"'||'已验视'||'"
}
}]
},
"senderData": {
"name": "tester"
}
}'
as VIPSPRINTPROTOCOL
,A.ORDERNO AS DOCNO
From doc_order_header a
inner JOIN Doc_Order_Deliveryinfo b ON b.TrackingNo = a.SOReference5
left join doc_wave_header e on e.waveno=a.waveno
left join Doc_Wave_Details f on f.waveno=a.waveno and f.orderno=a.orderno
LEFT JOIN (SELECT WAVENO A,COUNT(DISTINCT(ORDERNO)) B FROM DOC_WAVE_DETAILS GROUP BY WAVENO) I ON A.WAVENO=I.A
where 1=1
--AND a.h_edi_16 in (select code from bas_codes where codeid = 'VIPMPDP')
AND a.carrierid in ('POSTB','YTO','EMS')
AND a.SOStatus <>'90'
AND a.orderno='ZPCK22061404844'
;
---------------------单据打印标记
if IN_Parameter1 = 'CHECK_PRINT' THEN
/* IF IN_Parameter4 like 'Label_Carton%' Then --- 单品复核装箱标签打印
update DOC_Order_Header s
set
s.expressprintflag = 'Y',
s.CarrierAddress3=to_char(sysdate,'YYYY-MM-DD hh24:mi:ss'),s.CarrierAddress4=IN_UserID
WHERE s.OrderNo = IN_Parameter2 ;*/
--commit;
--end if;
IF IN_Parameter4 like 'Document_Packing_List%' Then --- 单品复核装箱清单打印
update DOC_Order_Header s
set
s.expressprintflag = 'Y',
s.CarrierAddress3=to_char(sysdate,'YYYY-MM-DD hh24:mi:ss'),s.CarrierAddress4=IN_UserID
WHERE s.OrderNo = IN_Parameter2 ;
end if;
commit;
end if ;
----波次面单--波次
-------------------------单据打印前校验，打印后标记--HMY20180129
--20200303 升级拼多多 菜鸟升级打印参数改变 为CN_PRINT 、PDD_PRINT
--EXEC SPUDF_ProcessA 'WPJIT','CN_PRINT','Document_WAVE_TBYZ.jasper','WP20030400020','','cn','M00507',''
If IN_Parameter1 in( 'PRINT','CN_PRINT','PDD_PRINT') Then
if IN_Parameter2 like 'Document_WAVE_EXPRESS%' Then
spsys_getlistbystring(IN_Parameter3,',',IN_UserID);
update doc_order_header s set
s.expressprintflag = 'Y',
s.CarrierAddress3=to_char(sysdate,'YYYY-MM-DD hh24:mi:ss'),
s.CarrierAddress4=IN_UserID
where s.sostatus<>'90'
and s.orderno in (select orderno from doc_wave_details w
where linestatus < '63'
and waveno in (select code from tmp_code a where a.codeid=IN_UserID));
update doc_wave_header s
set s.userdefine1 = CASE
WHEN NVL(s.userdefine1,'N')='N' THEN
to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss') || '/' || IN_UserID || '/' || '1'
ELSE
to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss') || '/' || IN_UserID || '/' ||
(to_number(nvl(GET_UDFA_PARAMETER(s.userdefine1, '/', 3), 0)) + 1)
END
where s.waveno in (select code
from tmp_code t
where t.codeid = IN_UserID);
--菜鸟波次打印调用两次模板，所以需特殊设置
elsif IN_Parameter2 like 'Document_WAVE_TBYZ%' Then
spsys_getlistbystring(IN_Parameter3,',',IN_UserID);
update doc_order_header s set
s.expressprintflag = 'Y',
s.CarrierAddress3=to_char(sysdate,'YYYY-MM-DD hh24:mi:ss'),
s.CarrierAddress4=IN_UserID
where s.sostatus<>'90'
and s.orderno in (select orderno from doc_wave_details w
where linestatus < '63'
and waveno in (select code from tmp_code a where a.codeid=IN_UserID));
update doc_wave_header s
set s.userdefine1 = CASE
WHEN NVL(s.userdefine1,'N')='N' THEN
to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss') || '/' || IN_UserID || '/' || '1'
ELSE
to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss') || '/' || IN_UserID || '/' ||
(to_number(nvl(GET_UDFA_PARAMETER(s.userdefine1, '/', 3), 0)) + 1)
END
where s.waveno in (select code
from tmp_code t
where t.codeid = IN_UserID);
--唯品韵达波次打印添加打印标识 2019-12-20 YANGXUN
elsif IN_Parameter2 like 'Document_WAVE_VIP%' OR IN_Parameter2 like 'Document_WAVE_PDD%' Then
spsys_getlistbystring(IN_Parameter3,',',IN_UserID);
update doc_order_header s set
s.expressprintflag = 'Y',
s.CarrierAddress3=to_char(sysdate,'YYYY-MM-DD hh24:mi:ss'),
s.CarrierAddress4=IN_UserID
where s.sostatus<>'90'
and s.orderno in (select orderno from doc_wave_details w
where linestatus < '63'
and waveno in (select code from tmp_code a where a.codeid=IN_UserID));
update doc_wave_header s
set s.userdefine1 = CASE
WHEN NVL(s.userdefine1,'N')='N' THEN
to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss') || '/' || IN_UserID || '/' || '1'
ELSE
to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss') || '/' || IN_UserID || '/' ||
(to_number(nvl(GET_UDFA_PARAMETER(s.userdefine1, '/', 3), 0)) + 1)
END
where s.waveno in (select code
from tmp_code t
where t.codeid = IN_UserID);
commit;
end if;
----订单面单
if IN_Parameter2 like 'Document_SO_EXPRESS%' OR IN_Parameter2 like 'Document_SSC%' OR IN_Parameter2 like 'Document_SO_PDD%'
OR IN_Parameter2 like 'Document_SO_VIP%'OR IN_Parameter2 like 'Document_SO_B2B出库单_双联单%' Then
spsys_getlistbystring_lob(IN_Parameter3,',',IN_UserID);
update doc_order_header s set
s.expressprintflag = 'Y' ,
s.CarrierAddress3=to_char(sysdate,'YYYY-MM-DD hh24:mi:ss'),s.CarrierAddress4=IN_UserID
where s.orderno in (select code from tmp_code a where a.codeid=IN_UserID);
commit;
end IF;
end if;
--视图里面调用是函数
CREATE OR REPLACE FUNCTION Join_PK_ByOrder
(
IN_OrderNo varchar2
)
return varchar2 is Result varchar2(2000 char);
BEGIN
select
listagg(a.sku||' '||c.sku_group2||' '||c.sku_group3||' '||b.qty_each||' '||b.location,';') within GROUP (order by c.sku)
into Result
from Doc_Order_Details A
LEFT JOIN ACT_ALLOCATION_DETAILS B ON B.ORDERNO = A.ORDERNO AND B.ORDERLINENO = A.ORDERLINENO
LEFT JOIN BAS_SKU C ON C.SKU = A.SKU AND C.CUSTOMERID = A.CUSTOMERID
where A.OrderNo= IN_OrderNo ;
return(Result);
end;
CREATE OR REPLACE FUNCTION joinsku_byorder
(
IN_OrderNo varchar2
)
return varchar2 is Result varchar2(1000 char);
BEGIN
select to_char(wm_concat(a.sku||' '||a.sku_group2||' '||a.sku_group3||' '||to_char(a.qty_each)||' '||a.location ||' ')) into result
from
(Select a1.customerid, a1.sku, c1.qty_each,b1.descr_c,c1.location,b1.sku_group2,b1.sku_group3
from Doc_Order_Details a1
left join bas_sku b1 on b1.sku = a1.sku and b1.customerid = a1.customerid
left join act_allocation_details c1 on c1.orderno = a1.orderno and c1.orderlineno = a1.orderlineno
where a1.OrderNo= IN_OrderNo
Order By a1.CustomerID,a1.SKU
) A;
return(Result);
end;
自定义模板具体分类，以圆通为例：
京东无界单品复核：Document_SSC_JDCLOUD_%_%_YTO_%.jasper
京东无界发运订单：Document_SO_JDCLOUD_%_%_YTO_%.jasper
京东无界波次打印：Document_WAVE_JDCLOUD_%_%_YTO_%.jasper
菜鸟云打印单品复核：Document_SSC_SINGLE_%_%_YTO_%.jasper
菜鸟云打印发运订单：Document_SO_EXPRESSB.jasper
菜鸟云打印波次打印：Document_WAVE_TBYZ.jasper
拼多多云打印单品复核：Document_SSC_PDDNEW_%_%_YTO_%.jasper
拼多多云打印发运订单：Document_SO_PDDCLOUDNEW_%_%_YTO_%.jasper
拼多多云打印波次打印：Document_WAVE_PDDCLOUDNEW_YTO.jasper
抖音云打印单品复核：Document_SSC_DYCLOUD_%_%_YTO_%.jasper
抖音云打印发运订单：Document_SO_DYCLOUD_%_%_YTO_%.jasper
抖音云打印波次打印：Document_WAVE_DYCLOUD_%_%_YTO_%.jasper
唯品顺丰面单
单品复核：Document_Packing_List_%_%_SHUNFENG_%.jasper
发运订单：Document_SO_VIPSF_%_%_SHUNFENG_%.jasper
波次计划：Document_WAVE_VIPSF_%_%_SHUNFENG_%.jasper
模板下载链接如下：模板
自定义区域字段大小写要与SQL中的保持一致
菜鸟自定义区域字段格式 <%=_data.LOCATIONID%>
拼多多自定义区域字段格式 <%=data.LOCATIONID%>
京东自定义区域字段格式 @{LOCATIONID}
抖店自定义区域字段格式 <%=_data.LOCATIONID%>
各家平台自定义模板编辑发布的URL :
菜鸟
https://cloudprint.cainiao.com/cloudprint
拼多多
https://mms.pinduoduo.com/waybill
京东
https://template-design.jd.com
抖店
https://op.jinritemai.com/login
https://fxg.jinritemai.com/ffa/grs/deposit
快手
https://cloudprint.kwaixiaodian.com/
5713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
