修改表结构,增加字段

首营表:

ALTER TABLE T_CHK_VENDOR ADD ( XYYNO VARCHAR2(100), YSBNO VARCHAR2(100) );

批发申请单主表

ALTER TABLE T_BATSALEAPPLY_H ADD ( djbh VARCHAR2(100), billsource VARCHAR2(100));

批发申请单明细表

ALTER TABLE T_BATSALEAPPLY_H ADD DJ_SN NUMBER;

批发销售单主表

ALTER TABLE T_BATSALE_H ADD ( djbh VARCHAR2(100), billsource VARCHAR2(100), sale_idbs VARCHAR2(100), expresscompany VARCHAR2(100), express_code VARCHAR2(100), express_no VARCHAR2(100), expressno VARCHAR2(100) );

创建客户视图

CREATE OR REPLACE VIEW XYY_SELLER_CONSUMER_INFO AS
SELECT t_vencus.vencuscode as customer_id,
       t_vencus.vencuscode as customer_code,
       t_vencus.vencusname    as customer_name
       ,null as province_id
       ,null as province_name
       ,null as city_id
       ,null as city_name
       ,null as country_id
       ,null as country_name
 FROM t_vencus  t_vencus
 WHERE t_vencus.status = 1
 AND t_vencus.compid=1
 and t_vencus.vencusflag in (2,0)  --只取仅客户 与 供应商与客户
  ---暂未控制仅电商客户
order by t_vencus.vencuscode;

创建订单视图

CREATE OR REPLACE VIEW XYY_SELLER_ORDER_DELIVER_D AS
SELECT a.djbh AS order_no,
       to_number(d.back_var5) AS order_detail_id,
       w.warecode AS erp_code,
       w.warename AS product_name,
       d.makeno AS batch_code,
       d.wareqty AS batch_num,
       to_char(d.invalidate, 'yyyy-mm-dd') AS batch_valid_date,
       to_char(i.makedate, 'yyyy-mm-dd') AS production_date,
       d.whlprice AS batch_price
  FROM t_batsale_h a
  LEFT JOIN t_batsale_d d
    ON a.batsaleno = d.batsaleno
  LEFT JOIN t_ware w
    ON d.wareid = w.wareid
   AND w.compid = a.compid
  LEFT JOIN t_store_i i
    ON d.wareid = i.wareid
   AND d.batid = i.batid
 WHERE a.status = 1
   AND a.compid = 1
   AND a.billsource = '3';

创建商品视图

create or replace view xyy_seller_product_full_info as
select
t_ware.warecode as erp_code, --varchar（32） 是 erp 编码（该商品在ERP系统中的唯一编码，对应卖家中心商品的 “erp 编码”字段）
t_ware.warename as product_name, --varchar（128） 是 商品名称
t_ware.warename as common_name, -- varchar（128） 是 通用名称，若没有通用名称可以映射商品名称
t_ware.fileno as approval_number, -- varchar（128） 是 批准文号
case when  nvl(t_ware.barcode,'')='' or t_ware.barcode='无' then '0' else nvl(t_ware.barcode,0) end  as code, -- varchar（64） 否 商品条码
(select t_factory.factoryname  from t_factory where factoryid = t_ware.factoryid) as  manufacturer, -- varchar（200） 是 生产厂家
cast(t_ware.midqty as int) as  medium_package_num, -- Int 是 中包装
t_ware.wareunit  as product_unit, -- varchar（32） 是 包装单位
t_ware.warespec as spec, -- varchar（255） 是 规格
(select nvl(classname,'无') from t_class_base t_class_base where classgroupno='03' and classcode <>'03' and classcode =t_ware.bandcode) as dosage_form, -- varchar（255） 是 剂型
'36' as term, -- varchar（32） 是 有效期，单位“月”
(select t_ware_whlprice.setwhlprice1   from t_ware_whlprice t_ware_whlprice where whlgroupid =104
 and wareid =t_ware.wareid and compid =t_ware.compid) as  suggest_price, -- decimal（14,2） 否 建议零售价，单位“元”
(select  t_area.areaname from t_area t_area where t_area.areacode = t_ware.areacode) as production_area, -- varchar（255） 否 产地

(select sum(wareqty) - sum(awaitqty) from t_store_d t_store_d where wareid =t_ware.wareid and compid =t_ware.compid) as available_qyt, -- int 是 库存=该商品所有批号的库存累计之和-订单中间表未拉取订单商品库存。该库存为可售库存

(select t_ware_whlprice.setwhlprice1   from t_ware_whlprice t_ware_whlprice where whlgroupid =104
and wareid =t_ware.wareid and compid =t_ware.compid) as monomer_price, -- decimal（14,2） 是 单体售价，单位（元），保留两位小数
(select t_ware_whlprice.setwhlprice1   from t_ware_whlprice t_ware_whlprice where whlgroupid =104
and wareid =t_ware.wareid and compid =t_ware.compid) as chain_price, -- decimal（14,2） 是 连锁售价，单位（元），保留两位小数
(select to_char(min(makedate),'YYYY-MM-DD') from t_store_i t_store_i where wareid =t_ware.Wareid and compid =t_ware.compid) as oldest_pro_date, -- varchar（32） 否 最老生产日期（格式要求：yyyy-MM-dd），该商品所有可售库存中距离当前最远的批号生产日期
(select to_char(max(makedate),'YYYY-MM-DD') from t_store_i t_store_i where wareid =t_ware.Wareid and compid =t_ware.compid) as new_pro_date, -- varchar（32） 否 最新生产日期（格式要求：yyyy-MM-dd），该商品所有可售库存中距离当前最近的批号生产日期
(select to_char(max(invalidate),'YYYY-MM-DD') from t_store_i t_store_i where wareid =t_ware.Wareid and compid =t_ware.compid) as near_effect, -- varchar（32） 否 近效期至（格式要求：yyyy-MM-dd），该商品所有可售库存中有效期至距离当前最近的批号效期
(select to_char(min(invalidate),'YYYY-MM-DD') from t_store_i t_store_i where wareid =t_ware.Wareid and compid =t_ware.compid) as far_effect, -- varchar（32） 否 远效期至（格式要求：yyyy-MM-dd），该商品所有可售库存中有效期至距离当前最远的批号效期
t_ware.INITTIME as create_time ,
 '无' as drug_classification,
    '' as manufacturing_license_no,'' as marketAuthor_address,'' as manufacturer_address,'' as production_address,'' as base_price

---datetime 是 创建时间，具体到秒商品真实创建时间，工具会根据时间增量同步
from t_ware t_ware
where t_ware.compid =1 and status=1;

创建商品分类视图

CREATE OR REPLACE VIEW XYY_SELLER_PRODUCT_INFO AS
SELECT w.warecode AS erp_code,
       CAST(w.midqty AS INT) AS medium_package_num,
       w.wareunit AS product_unit,
       nvl(c.classname, '无') AS dosage_form,
       '36' AS term,
       CAST(round(h.setwhlprice1, 2) AS NUMBER(14, 2)) AS suggest_price,
       a.areaname AS production_area,
       w.listing_holder as market_author,
    w.INITTIME AS update_time,
   w.MAXQTY as  piece_loading
  FROM t_ware w
  LEFT JOIN t_ware_class_base b
    ON w.compid = b.compid
   AND w.wareid = b.wareid
   AND b.classgroupno = '03'
  LEFT JOIN t_class_base c
    ON b.classgroupno = c.classgroupno
    and b.classcode=c.classcode
  LEFT JOIN t_ware_whlprice h
    ON w.compid = h.compid
   AND h.whlgroupid = 12
   and w.wareid=h.wareid
  LEFT JOIN t_area a
    ON a.areacode = w.areacode
 WHERE w.status = 1
   AND w.compid = 1
   and w.warekind <> 3
   and w.salestatus = 1;

创建商品价格视图

CREATE OR REPLACE VIEW XYY_SELLER_PRODUCT_PRICE AS
SELECT w.warecode AS erp_code,
       CAST(round(p.setwhlprice1, 2) AS NUMBER(14, 2)) AS monomer_price,
       CAST(round(p.setwhlprice1, 2) AS NUMBER(14, 2)) AS chain_price,
       CAST(round(p.leastwhlprice, 2) AS NUMBER(14, 2)) AS base_price
  FROM t_ware_whlprice p
  LEFT JOIN t_ware w
    ON p.compid = w.compid
   AND p.wareid = w.wareid
 WHERE p.whlgroupid = 12
   AND p.setwhlprice1 > 0;

创建商品库存视图

CREATE OR REPLACE VIEW XYY_SELLER_PRODUCT_STOCK AS
SELECT w.warecode AS erp_code,
       to_char(MIN(i.makedate), 'yyyy-mm-dd') AS oldest_pro_date,
       to_char(MAX(i.makedate), 'yyyy-mm-dd') AS new_pro_date,
       to_char(MIN(i.invalidate), 'yyyy-mm-dd') AS near_effect,
       to_char(MAX(i.invalidate), 'yyyy-mm-dd') AS far_effect,
       SUM(d.wareqty - d.awaitqty - d.pendingqty) AS available_qyt
  FROM t_store_d d
  LEFT JOIN t_store_i i
    ON d.wareid = i.wareid
   AND d.batid = i.batid
  LEFT JOIN t_ware w
    ON d.compid = w.compid
   AND d.wareid = w.wareid
 WHERE w.status = 1
   AND d.wareqty - d.awaitqty - d.pendingqty > 0
   AND d.compid = 1
   and i.ownerid='01'
   AND EXISTS (SELECT 1
          FROM t_ware_whlprice p
         WHERE d.wareid = p.wareid
           AND i.compid = p.compid
           AND p.whlgroupid = 12
           AND p.setwhlprice1 > 0)
 GROUP BY w.warecode;

创建小药药账号

CREATE USER XYYUSER IDENTIFIED BY XYYPWD
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
-- 授予创建会话的权限
GRANT CREATE SESSION TO XYYUSER;
-- 授予无限表空间的权限
GRANT UNLIMITED TABLESPACE TO XYYUSER;
-- 授予系统权限
GRANT CREATE TABLE TO XYYUSER;
GRANT INSERT ANY TABLE TO XYYUSER;
GRANT UPDATE ANY TABLE TO XYYUSER;
GRANT DELETE ANY TABLE TO XYYUSER;
GRANT SELECT ANY TABLE TO XYYUSER;
GRANT ALTER ANY TABLE TO XYYUSER;
GRANT DROP ANY TABLE TO XYYUSER;
GRANT CREATE VIEW TO XYYUSER;
GRANT CREATE PROCEDURE TO XYYUSER;
GRANT CREATE SEQUENCE TO XYYUSER;
GRANT CREATE DATABASE LINK TO XYYUSER;
GRANT CREATE TRIGGER TO XYYUSER;

-- 使用 H2 用户或具有足够权限的用户登录
CONNECT H2/your_password;

-- 批量授予 H2 用户下所有表的权限
DECLARE
    v_table_name VARCHAR2(255);
    CURSOR table_cur IS SELECT table_name FROM user_tables;
BEGIN
    OPEN table_cur;
    LOOP
        FETCH table_cur INTO v_table_name;
        EXIT WHEN table_cur%NOTFOUND;

        -- 授予 SELECT 权限
        EXECUTE IMMEDIATE 'GRANT SELECT ON ' || v_table_name || ' TO XYYUSER';

        -- 授予 INSERT 权限
        EXECUTE IMMEDIATE 'GRANT INSERT ON ' || v_table_name || ' TO XYYUSER';

        -- 授予 UPDATE 权限
        EXECUTE IMMEDIATE 'GRANT UPDATE ON ' || v_table_name || ' TO XYYUSER';

        -- 授予 DELETE 权限
        EXECUTE IMMEDIATE 'GRANT DELETE ON ' || v_table_name || ' TO XYYUSER';
    END LOOP;
    CLOSE table_cur;
END;

--批量授予 H2 用户下所有函数的 EXECUTE 权限
DECLARE
    v_function_name VARCHAR2(255);
    CURSOR function_cur IS SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION';
BEGIN
    OPEN function_cur;
    LOOP
        FETCH function_cur INTO v_function_name;
        EXIT WHEN function_cur%NOTFOUND;

        -- 授予 EXECUTE 权限
        EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || v_function_name || ' TO XYYUSER';
    END LOOP;
    CLOSE function_cur;
END;
-- 批量授予 H2 用户下所有存储过程的 EXECUTE 权限
DECLARE
    v_procedure_name VARCHAR2(255);
    CURSOR procedure_cur IS SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE';
BEGIN
    OPEN procedure_cur;
    LOOP
        FETCH procedure_cur INTO v_procedure_name;
        EXIT WHEN procedure_cur%NOTFOUND;

        -- 授予 EXECUTE 权限
        EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || v_procedure_name || ' TO XYYUSER';
    END LOOP;
    CLOSE procedure_cur;
END;

创建存储过程参考 药师帮接口

B2B平台对接-药师帮 – XIANYU

小药药账号登录后自己创建表格,不需要海典操作

创建的表格如下

xyy_ddhz xyy_ddmx xyy_drugstoreinfo xyy_seller_change_info xyy_seller_logistics_info xyy_seller_order_detail xyy_seller_order_info xyy_seller_order_invoice xyy_seller_un_consumer_info