select seq_dct_data_batchid.nextval into nDataBatchID from dual;
insert into DCT_BMS_SALES_SCRIPT_DAY
(sid,
sourceid,
useno,
filedate,
salesdate,
customid,
customname,
goodsid,
goodsname,
goodsgenname,
goodstype,
factoryname,
goodsunit,
lotno,
proddate,
invaliddate,
goodsqty,
unitprice,
total_line,
billid,
databatchid,
datacount,
datastate)
select seq_dct_bms_sales_script_day.nextval,
nSourceID,
szUseNo,
dtNow,
BMS_ST_IO_DOC_V.KEEPDATE 销售日期, --TO_char(BMS_ST_IO_DOC_V.KEEPDATE,' YYYY - MM - DD HH24 :MI :SS') 销售日期,
to_char(BMS_SA_DOC.CUSTOMID) 客户ID,
BMS_SA_DOC.CUSTOMNAME 客户名称,
to_char(BMS_SA_DTL.GOODSID) 货品ID,
pub_goods.goodsname 货品名称,
pub_goods.goodsformalname 通用名,
pub_goods.goodstype 规格,
pub_goods.prodarea 生产厂家,
pub_goods.goodsunit 计量单位,
BMS_ST_IO_DTL_V.LOTNO 批号,
case
when BMS_ST_IO_DTL_V.prodDATE is null then
TO_date(' 3000 - 1 - 1 00 :00 :00',
' YYYY - MM - DD HH24 :MI :SS')
else
BMS_ST_IO_DTL_V.prodDATE --to_date(BMS_ST_IO_DTL_V.prodDATE, ' YYYY - MM - DD HH24 :MI :SS')
end 生产日期,
case
when BMS_ST_IO_DTL_V.INVALIDDATE is null then
TO_date(' 3000 - 1 - 1 00 :00 :00',
' YYYY - MM - DD HH24 :MI :SS')
else
BMS_ST_IO_DTL_V.INVALIDDATE --to_date(BMS_ST_IO_DTL_V.INVALIDDATE, ' YYYY - MM - DD HH24 :MI :SS')
end 有效期至,
decode(BMS_SA_DOC.SATYPEID,
2,
BMS_ST_IO_DTL_V.DTLGOODSQTY * -1,
BMS_ST_IO_DTL_V.DTLGOODSQTY) 数量,
BMS_SA_DTL.UNITPRICE 单价,
round(BMS_SA_DTL.UNITPRICE *
decode(BMS_SA_DOC.SATYPEID,
2,
BMS_ST_IO_DTL_V.DTLGOODSQTY * -1,
BMS_ST_IO_DTL_V.DTLGOODSQTY),
2) 金额,
to_char(BMS_ST_IO_DTL_V.IODTLID) 业务单据ID,
nDataBatchID,
0,
null
from BMS_SA_DOC@dbl_dct_off BMS_SA_DOC,
BMS_SA_DTL@dbl_dct_off BMS_SA_DTL,
BMS_ST_IO_DOC_V@dbl_dct_off BMS_ST_IO_DOC_V,
BMS_ST_IO_DTL_V@dbl_dct_off BMS_ST_IO_DTL_V,
pub_goods@dbl_dct_off pub_goods
where BMS_SA_DTL.GOODSID = PUB_GOODS.GOODSID
and BMS_SA_DOC.SALESID = BMS_SA_DTL.SALESID
and BMS_ST_IO_DOC_V.KEEPDATE >= dtStart
and BMS_ST_IO_DOC_V.KEEPDATE < dtEnd --to_char(BMS_ST_IO_DOC_V.KEEPDATE,' yyyymm') =to_char(sysdate,' yyyymm')
and BMS_SA_DTL.USESTATUS = 2
and BMS_ST_IO_DOC_V.INOUTID = BMS_ST_IO_DTL_V.INOUTID
and BMS_SA_DTL.STIOFLAG = 1
and BMS_SA_DTL.SALESDTLID = BMS_ST_IO_DOC_V.SOURCEID
and BMS_ST_IO_DOC_V.SOURCETABLE = 2;
insert into DCT_BMS_SALES_SCRIPT_DAY
(sid,
sourceid,
useno,
filedate,
salesdate,
customid,
customname,
goodsid,
goodsname,
goodsgenname,
goodstype,
factoryname,
goodsunit,
lotno,
proddate,
invaliddate,
goodsqty,
unitprice,
total_line,
billid,
databatchid,
datacount,
datastate)
select seq_dct_bms_sales_script_day.nextval,
nSourceID,
szUseNo,
dtNow,
BMS_ST_IO_DOC_V.KEEPDATE 销售日期, --TO_char(BMS_ST_IO_DOC_V.KEEPDATE,' YYYY - MM - DD HH24 :MI :SS') 销售日期,
to_char(BMS_SA_DOC.CUSTOMID) 客户ID,
BMS_SA_DOC.CUSTOMNAME 客户名称,
to_char(BMS_SA_DTL.GOODSID) 货品ID,
pub_goods.goodsname 货品名称,
pub_goods.goodsformalname 通用名,
pub_goods.goodstype 规格,
pub_goods.prodarea 生产厂家,
pub_goods.goodsunit 计量单位,
BMS_ST_IO_DTL_V.LOTNO 批号,
case
when BMS_ST_IO_DTL_V.prodDATE is null then
TO_date(' 3000 - 1 - 1 00 :00 :00',
' YYYY - MM - DD HH24 :MI :SS')
else
BMS_ST_IO_DTL_V.prodDATE --to_date(BMS_ST_IO_DTL_V.prodDATE, ' YYYY - MM - DD HH24 :MI :SS')
end 生产日期,
case
when BMS_ST_IO_DTL_V.INVALIDDATE is null then
TO_date(' 3000 - 1 - 1 00 :00 :00',
' YYYY - MM - DD HH24 :MI :SS')
else
BMS_ST_IO_DTL_V.INVALIDDATE --to_date(BMS_ST_IO_DTL_V.INVALIDDATE, ' YYYY - MM - DD HH24 :MI :SS')
end 有效期至,
decode(BMS_SA_DOC.SATYPEID,
2,
BMS_ST_IO_DTL_V.DTLGOODSQTY * -1,
BMS_ST_IO_DTL_V.DTLGOODSQTY) 数量,
BMS_SA_DTL.UNITPRICE 单价,
round(BMS_SA_DTL.UNITPRICE *
decode(BMS_SA_DOC.SATYPEID,
2,
BMS_ST_IO_DTL_V.DTLGOODSQTY * -1,
BMS_ST_IO_DTL_V.DTLGOODSQTY),
2) 金额,
to_char(BMS_ST_IO_DTL_V.IODTLID) 业务单据ID,
nDataBatchID,
0,
null
from BMS_SA_DOC@dbl_dct_off BMS_SA_DOC,
BMS_SA_DTL@dbl_dct_off BMS_SA_DTL,
BMS_ST_IO_DOC_V@dbl_dct_off BMS_ST_IO_DOC_V,
BMS_ST_IO_DTL_V@dbl_dct_off BMS_ST_IO_DTL_V,
pub_goods@dbl_dct_off pub_goods
where BMS_SA_DTL.GOODSID = PUB_GOODS.GOODSID
and BMS_SA_DOC.SALESID = BMS_SA_DTL.SALESID
and BMS_ST_IO_DOC_V.KEEPDATE >= dtStart
and BMS_ST_IO_DOC_V.KEEPDATE < dtEnd --to_char(BMS_ST_IO_DOC_V.KEEPDATE,' yyyymm') =to_char(sysdate,' yyyymm')
and BMS_SA_DTL.USESTATUS = 2
and BMS_ST_IO_DOC_V.INOUTID = BMS_ST_IO_DTL_V.INOUTID
and BMS_SA_DTL.STIOFLAG = 1
and BMS_SA_DTL.SALESDTLID = BMS_ST_IO_DOC_V.SOURCEID
and BMS_ST_IO_DOC_V.SOURCETABLE = 2;
SELECT SEQ_DCT_DATA_BATCHID.NEXTVAL INTO NDATABATCHID FROM DUAL;INSERT INTO DCT_BMS_SALES_SCRIPT_DAY
(SID,
SOURCEID,
USENO,
FILEDATE,
SALESDATE,
CUSTOMID,
CUSTOMNAME,
GOODSID,
GOODSNAME,
GOODSGENNAME,
GOODSTYPE,
FACTORYNAME,
GOODSUNIT,
LOTNO,
PRODDATE,
INVALIDDATE,
GOODSQTY,
UNITPRICE,
TOTAL_LINE,
BILLID,
DATABATCHID,
DATACOUNT,
DATASTATE)
SELECT SEQ_DCT_BMS_SALES_SCRIPT_DAY.NEXTVAL,
NSOURCEID,
SZUSENO,
DTNOW,
BMS_ST_IO_DOC_V.KEEPDATE 销售日期, --TO_CHAR(BMS_ST_IO_DOC_V.KEEPDATE,' YYYY - MM - DD HH24 :MI :SS') 销售日期,
TO_CHAR(BMS_SA_DOC.CUSTOMID) 客户ID,
BMS_SA_DOC.CUSTOMNAME 客户名称,
TO_CHAR(BMS_SA_DTL.GOODSID) 货品ID,
PUB_GOODS.GOODSNAME 货品名称,
PUB_GOODS.GOODSFORMALNAME 通用名,
PUB_GOODS.GOODSTYPE 规格,
PUB_GOODS.PRODAREA 生产厂家,
PUB_GOODS.GOODSUNIT 计量单位,
BMS_ST_IO_DTL_V.LOTNO 批号,
CASE
WHEN BMS_ST_IO_DTL_V.PRODDATE IS NULL THEN
TO_DATE('3000-1-1 00:00:00','YYYY-MM-DD HH24:MI:SS')
ELSE
BMS_ST_IO_DTL_V.PRODDATE --TO_DATE(BMS_ST_IO_DTL_V.PRODDATE, ' YYYY - MM - DD HH24 :MI :SS')
END 生产日期,
CASE
WHEN BMS_ST_IO_DTL_V.INVALIDDATE IS NULL THEN
TO_DATE('3000-1-1 00:00:00','YYYY-MM-DD HH24:MI:SS')
ELSE
BMS_ST_IO_DTL_V.INVALIDDATE --TO_DATE(BMS_ST_IO_DTL_V.INVALIDDATE, ' YYYY - MM - DD HH24 :MI :SS')
END 有效期至,
DECODE(BMS_SA_DOC.SATYPEID,
2,
BMS_ST_IO_DTL_V.DTLGOODSQTY * -1,
BMS_ST_IO_DTL_V.DTLGOODSQTY) 数量,
BMS_SA_DTL.UNITPRICE 单价,
ROUND(BMS_SA_DTL.UNITPRICE *
DECODE(BMS_SA_DOC.SATYPEID,
2,
BMS_ST_IO_DTL_V.DTLGOODSQTY * -1,
BMS_ST_IO_DTL_V.DTLGOODSQTY),
2) 金额,
TO_CHAR(BMS_ST_IO_DTL_V.IODTLID) 业务单据ID,
NDATABATCHID,
0,
NULL
FROM BMS_SA_DOC@DBL_DCT_OFF BMS_SA_DOC,
BMS_SA_DTL@DBL_DCT_OFF BMS_SA_DTL,
BMS_ST_IO_DOC_V@DBL_DCT_OFF BMS_ST_IO_DOC_V,
BMS_ST_IO_DTL_V@DBL_DCT_OFF BMS_ST_IO_DTL_V,
PUB_GOODS@DBL_DCT_OFF PUB_GOODS
WHERE BMS_SA_DTL.GOODSID = PUB_GOODS.GOODSID
AND BMS_SA_DOC.SALESID = BMS_SA_DTL.SALESID
AND BMS_ST_IO_DOC_V.KEEPDATE >= DTSTART
AND BMS_ST_IO_DOC_V.KEEPDATE < DTEND --TO_CHAR(BMS_ST_IO_DOC_V.KEEPDATE,' YYYYMM') =TO_CHAR(SYSDATE,' YYYYMM')
AND BMS_SA_DTL.USESTATUS = 2
AND BMS_ST_IO_DOC_V.INOUTID = BMS_ST_IO_DTL_V.INOUTID
AND BMS_SA_DTL.STIOFLAG = 1
AND BMS_SA_DTL.SALESDTLID = BMS_ST_IO_DOC_V.SOURCEID
AND BMS_ST_IO_DOC_V.SOURCETABLE = 2;
楼主代码简化下就是
INSERT INTO table SELECT * FROM table. 就是插入表。 插入的字段就不多说了。
case
when BMS_ST_IO_DTL_V.prodDATE is null then
TO_date(' 3000 - 1 - 1 00 :00 :00',
' YYYY - MM - DD HH24 :MI :SS')
else
BMS_ST_IO_DTL_V.prodDATE --to_date(BMS_ST_IO_DTL_V.prodDATE, ' YYYY - MM - DD HH24 :MI :SS')
end 生产日期,
是一组判断语句,对BMS_ST_IO_DTL_V.prodDATE 是否为空进行判断。
decode(BMS_SA_DOC.SATYPEID,
2,
BMS_ST_IO_DTL_V.DTLGOODSQTY * -1,
BMS_ST_IO_DTL_V.DTLGOODSQTY) 数量,
decode 函数:
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
decode(字段或字段的运算,值1,值2,值3)
SELECT DECODE (value, <if this value>, <return this value>) FROM table 这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3
当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多最后的where 都是一些条件了..
是说复制一个表的数据吗?还是?
BMS_SA_DOC@DBL_DCT_OFF BMS_SA_DOC,
BMS_SA_DTL@DBL_DCT_OFF BMS_SA_DTL,
BMS_ST_IO_DOC_V@DBL_DCT_OFF BMS_ST_IO_DOC_V,
BMS_ST_IO_DTL_V@DBL_DCT_OFF BMS_ST_IO_DTL_V,
PUB_GOODS@DBL_DCT_OFF PUB_GOODS
关联得到!INSERT INTO DCT_BMS_SALES_SCRIPT_DAY
(SID,
SOURCEID,
USENO,
FILEDATE,
SALESDATE,
CUSTOMID,
CUSTOMNAME,
GOODSID,
GOODSNAME,
GOODSGENNAME,
GOODSTYPE,
FACTORYNAME,
GOODSUNIT,
LOTNO,
PRODDATE,
INVALIDDATE,
GOODSQTY,
UNITPRICE,
TOTAL_LINE,
BILLID,
DATABATCHID,
DATACOUNT,
DATASTATE)
SELECT SEQ_DCT_BMS_SALES_SCRIPT_DAY.NEXTVAL,
NSOURCEID,
SZUSENO,
DTNOW,
BMS_ST_IO_DOC_V.KEEPDATE 销售日期, --TO_char(BMS_ST_IO_DOC_V.KEEPDATE,' YYYY - MM - DD HH24 :MI :SS') 销售日期,
TO_CHAR(BMS_SA_DOC.CUSTOMID) 客户ID,
BMS_SA_DOC.CUSTOMNAME 客户名称,
TO_CHAR(BMS_SA_DTL.GOODSID) 货品ID,
PUB_GOODS.GOODSNAME 货品名称,
PUB_GOODS.GOODSFORMALNAME 通用名,
PUB_GOODS.GOODSTYPE 规格,
PUB_GOODS.PRODAREA 生产厂家,
PUB_GOODS.GOODSUNIT 计量单位,
BMS_ST_IO_DTL_V.LOTNO 批号,
CASE
WHEN BMS_ST_IO_DTL_V.PRODDATE IS NULL THEN
TO_DATE(' 3000 - 1 - 1 00 :00 :00', ' YYYY - MM - DD HH24 :MI :SS')
ELSE
BMS_ST_IO_DTL_V.PRODDATE --to_date(BMS_ST_IO_DTL_V.prodDATE, ' YYYY - MM - DD HH24 :MI :SS')
END 生产日期,
CASE
WHEN BMS_ST_IO_DTL_V.INVALIDDATE IS NULL THEN
TO_DATE(' 3000 - 1 - 1 00 :00 :00', ' YYYY - MM - DD HH24 :MI :SS')
ELSE
BMS_ST_IO_DTL_V.INVALIDDATE --to_date(BMS_ST_IO_DTL_V.INVALIDDATE, ' YYYY - MM - DD HH24 :MI :SS')
END 有效期至,
DECODE(BMS_SA_DOC.SATYPEID, 2, BMS_ST_IO_DTL_V.DTLGOODSQTY * -1, BMS_ST_IO_DTL_V.DTLGOODSQTY) 数量,
BMS_SA_DTL.UNITPRICE 单价,
ROUND(BMS_SA_DTL.UNITPRICE *
DECODE(BMS_SA_DOC.SATYPEID, 2, BMS_ST_IO_DTL_V.DTLGOODSQTY * -1, BMS_ST_IO_DTL_V.DTLGOODSQTY), 2) 金额,
TO_CHAR(BMS_ST_IO_DTL_V.IODTLID) 业务单据ID,
NDATABATCHID,
0,
NULL
FROM BMS_SA_DOC@DBL_DCT_OFF BMS_SA_DOC,
BMS_SA_DTL@DBL_DCT_OFF BMS_SA_DTL,
BMS_ST_IO_DOC_V@DBL_DCT_OFF BMS_ST_IO_DOC_V,
BMS_ST_IO_DTL_V@DBL_DCT_OFF BMS_ST_IO_DTL_V,
PUB_GOODS@DBL_DCT_OFF PUB_GOODS
WHERE BMS_SA_DTL.GOODSID = PUB_GOODS.GOODSID
AND BMS_SA_DOC.SALESID = BMS_SA_DTL.SALESID
AND BMS_ST_IO_DOC_V.KEEPDATE >= DTSTART
AND BMS_ST_IO_DOC_V.KEEPDATE < DTEND --to_char(BMS_ST_IO_DOC_V.KEEPDATE,' yyyymm') =to_char(sysdate,' yyyymm')
AND BMS_SA_DTL.USESTATUS = 2
AND BMS_ST_IO_DOC_V.INOUTID = BMS_ST_IO_DTL_V.INOUTID
AND BMS_SA_DTL.STIOFLAG = 1
AND BMS_SA_DTL.SALESDTLID = BMS_ST_IO_DOC_V.SOURCEID
AND BMS_ST_IO_DOC_V.SOURCETABLE = 2;