物流表结构

–20、产品单据1
CREATE TABLE MRTCPDJ0
(
DJBM00 NUMBER(10)
CONSTRAINT PK_MRTCPDJ0_DJBM00 PRIMARY KEY USING INDEX TABLESPACE INDX,
DJDH00 VARCHAR2(30) , –单号
DJSJ00 DATE , –制单时间
DJDJBM VARCHAR2(30) , –制单人编码
DJDJMC VARCHAR2(30) , –制单人名称
DJSSMD NUMBER(10) , –所属门店
DJLX00 CHAR(1) , –单据类型 2-请配 3-领用 4-入库 5-销售 6-移库 7-损溢 8-盘点 9-组拆
DJYWLX CHAR(1) , –业务类型

DJDWBM NUMBER(10) , –客户编码
DJDWMC VARCHAR2(100) , –客户名称
DJCKBM NUMBER(10) , –仓库编码
DJCKMC VARCHAR2(30) , –仓库名称
DJMBBM NUMBER(10) , –目标仓库编码
DJMBMC VARCHAR2(30) , –目标仓库名称
DJRYBM VARCHAR2(30) , –相关人编码
DJRYMC VARCHAR2(30) –人员名称
DJBZ00 VARCHAR2(100), –备注

DJSHBM VARCHAR2(30) , –审核人编码
DJSHMC VARCHAR2(30) , –审核人名称
DJSHBJ CHAR(1) DEFAULT ‘0’, –审核标记
DJSHSJ DATE , –审核时间

DJWCBJ CHAR(1) DEFAULT ‘0’, –完成标记
DJWCSJ DATE , –完成时间
DJPDBM NUMBER(10) , –盘点单编码
DJPDDH VARCHAR2(30) –盘点单号
)
TABLESPACE JC_DATA;

CREATE SEQUENCE SQ_MRTCPDJ0 INCREMENT BY 1 START WITH 1000;
SELECT SQ_MRTCPDJ0.NEXTVAL FROM DUAL;

–21、产品单据2
CREATE TABLE MRTCPDJ1
(
DJBM00 NUMBER(10)
CONSTRAINT PK_MRTCPDJ1_DJBM00 PRIMARY KEY USING INDEX TABLESPACE INDX,
DJSSBM NUMBER(10) , –所属ID
DJLX00 CHAR(1) , –单据类型 2-请配 3-领用 4-入库 5-销售 6-移库 7-损溢 8-盘点 9-组拆
DJYWLX CHAR(1) , –单据业务类型
DJCPBM NUMBER(10) , –产品编码
DJSL00 NUMBER(10,4) , –数量
DJJG00 NUMBER(10,4) , –价格
DJWCSL NUMBER(10,4) DEFAULT 0, –完成数量
DJSJ00 DATE, –时间
DJBZ00 VARCHAR2(30) –备注
)
TABLESPACE JC_DATA;

CREATE SEQUENCE SQ_MRTCPDJ1 INCREMENT BY 1 START WITH 1000;
SELECT SQ_MRTCPDJ1.NEXTVAL FROM DUAL;

–22、产品库存
CREATE TABLE MRTCPKC0
(
KCBM00 NUMBER(10)
CONSTRAINT PK_MRTCPKC0_KCBM00 PRIMARY KEY USING INDEX TABLESPACE INDX,
KCCKBM NUMBER(10), –仓库编码
KCCPBM NUMBER(10), –产品编码
KCSL00 NUMBER(10,4) –数量
)
TABLESPACE JC_DATA;

CREATE SEQUENCE SQ_MRTCPKC0 INCREMENT BY 1 START WITH 1000;
SELECT SQ_MRTCPKC0.NEXTVAL FROM DUAL;

–23、收付管理
CREATE TABLE MRTSFGL0
(
SFBM00 NUMBER(10)
CONSTRAINT PK_MRTSFGL0_SFBM00 PRIMARY KEY USING INDEX TABLESPACE INDX,
SFSJ00 DATE, –登记时间
SFDJBM NUMBER(10), –登记人编码
SFDJMC VARCHAR2(30), –登记人名称
SFDH00 VARCHAR2(30), –单号
SFLX00 CHAR(1), –类型 1-收款 2-付款
SFYWLX CHAR(1), –业务类型 1-期初建账 2-正常收款 3-正常调整 4-单据特调
SFSSDJ NUMBER(10), –所属单据
SFDJDH VARCHAR2(30), –单据单号
SFDWBM NUMBER(10), –单位编码
SFDWMC VARCHAR2(100), –单位名称
SFJE00 NUMBER(10,2), –金额
SFBZ00 VARCHAR2(100), –备注

SFSHBJ CHAR(1) DEFAULT ‘0’, –审核标记 0-未审核 1-审核
SFSHBM NUMBER(10), –审核人编码
SFSHMC VARCHAR2(30), –审核人名称
SFSHSJ DATE –审核时间
)
TABLESPACE JC_DATA;

CREATE SEQUENCE SQ_MRTSFGL0 INCREMENT BY 1 START WITH 1000;
SELECT SQ_MRTSFGL0.NEXTVAL FROM DUAL;

——————————-

进销存查询

SELECT DJCPBM,SUM(RKSL) RKSL,SUM(XSSL) XSSL,
SUM(KCSL) KCSL
FROM
(
SELECT DJCPBM,SUM(DECODE(DJLX00,’4’,DJSL00)) RKSL,
SUM(DECODE(DJLX00,’5’,DJSL00)) XSSL,0 KCSL
FROM MRTCPDJ0
(SELECT DJBM00,DJLX00 FROM
WHERE DJLX00 IN (‘4’,’5’) AND DJSHBJ=’1’) A,
(SELECT DJSSBM,DJCPBM,DJSL00
FROM MRTCPDJ1) B
WHERE A.DJBM00=B.DJSSBM
GROUP BY DJCPBM

UNION ALL

SELECT KCCPBM,0 RKSL,0 XSSL,SUM(KCSL00) KCSL
FROM MRTCPKC0
GROUP BY KCCPBM
)
GROUP BY DJCPBM