CREATE OR REPLACE PROCEDURE RP_TRANSFER001PROC_GENERATE(p_pi_id NUMBER) IS /* Alley init 20110315
功能同调拨汇总报表一样 */
v_sql1 VARCHAR2(4000);
v_sql2 VARCHAR2(4000);
v_sql3 VARCHAR2(4000);
v_sql4 VARCHAR2(4000);
v_sql5 VARCHAR2(4000);
v_date VARCHAR2(80);
v_datebegin VARCHAR2(8);
v_dateend VARCHAR2(8);BEGIN -- raise_application_error(-20201, p_pi_id);24338
DELETE FROM RP_TRANSFER001PROC a
WHERE a.ad_pi_id = p_pi_id;
COMMIT; --依次获取界面查询条件参数 SELECT t.info
INTO v_date
FROM ad_pinstance_para t
WHERE t.NAME = 'OUTINDATE' AND t.ad_pinstance_id = p_pi_id; IF instr(v_date, '<=') = 0 THEN
SELECT substr(v_date, 22, 8), substr(v_date, 35, 8)
INTO v_datebegin, v_dateend
FROM dual;
ELSE
v_datebegin := 0;
SELECT substr(TRIM(v_date), 13, 8)
INTO v_dateend
FROM dual;
END IF; SELECT t.info
INTO v_sql1
FROM ad_pinstance_para t
WHERE t.NAME = 'ORIGSTORE' AND t.ad_pinstance_id = p_pi_id; SELECT t.info
INTO v_sql2
FROM ad_pinstance_para t
WHERE t.NAME = 'DESTSTORE' AND t.ad_pinstance_id = p_pi_id; SELECT t.info
INTO v_sql3
FROM ad_pinstance_para t
WHERE t.NAME = 'PRODUCT' AND t.ad_pinstance_id = p_pi_id; SELECT t.info
INTO v_sql4
FROM ad_pinstance_para t
WHERE t.NAME = 'ASI' AND t.ad_pinstance_id = p_pi_id; SELECT t.info
INTO v_sql5
FROM ad_pinstance_para t
WHERE t.NAME = 'DOCNO' AND t.ad_pinstance_id = p_pi_id;
--插入数据
select to_char(OUTTIME,'yyyymmdd') into v_outtime from M_TRANSFER;
select to_char(INTIME,'yyyymmdd') into v_INTIME from M_TRANSFER;
IF v_sql5 IS NOT NULL THEN
EXECUTE IMMEDIATE ' INSERT INTO RP_TRANSFER001PROC
(ID,AD_CLIENT_ID,AD_ORG_ID,OWNERID,MODIFIERID,CREATIONDATE,MODIFIEDDATE,ISACTIVE,
changedate,DOCNO,c_Orig_Id,C_DEST_ID,DESCRIPTION,M_PRODUCT_ID,M_ATTRIBUTESETINSTANCE_ID,
qty,QTYOUT,QTYIN,preqtyin,qtydiff,PRICELIST,TOT_AMTQTY_LIST,TOT_AMTOUT_LIST,TOT_AMTIN_LIST,
TOT_AMTPREIN_LIST,TOT_AMTDIFF_LIST,m_product_alias_id,ACOST,percost,tot_amtqty_acost,
tot_amtqty_percost,AD_PI_ID )
select get_sequences(''RP_TRANSFER001PRO''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
to_char(a.OUTTIME,'yyyymmdd') as changedate, a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,b.M_ATTRIBUTESETINSTANCE_ID,
0 as qty, b.QTYOUT QTYOUT ,0 as QTYIN,
(case when a.in_status=1 then b.QTYOUT else 0 end) preqtyin,0 as qtydiff,
(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
0 TOT_AMTQTY_LIST ,b.qtyout*(select m.pricelist from m_product m where b.m_product_id=m.id) TOT_AMTOUT_LIST ,0 as TOT_AMTIN_LIST,
(case when a.in_status=1 then b.qtyout*(select m.pricelist from m_product m where b.m_product_id=m.id) else 0 end) as TOT_AMTPREIN_LIST,0 as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,0 as percost,
(select m.acost from m_product m where m.id=b.m_product_id)*b.QTYOUT as tot_amtqty_acost,
0 as tot_amtqty_percost,' || p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b ,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.OUT_STATUS=2
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.OUTTIME between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 || ')
and s.id in(' || v_sql4 || ')
and ' || v_sql5 || ' union all
select get_sequences(''RP_TRANSFER001PROC''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
to_char(a.INTIME,'yyyymmdd') as changedate,
a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,
b.M_ATTRIBUTESETINSTANCE_ID,0 AS QTY,0 as QTYOUT,b.QTYIN,0 as preqtyin,(b.QTYOUT-b.QTYIN) as qtydiff,(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
0 as TOT_AMTQTY_LIST, 0 as TOT_AMTOUT_LIST,b.qtyin*(select m.pricelist from m_product m where b.m_product_id=m.id) TOT_AMTIN_LIST,0 as TOT_AMTPREIN_LIST,
(b.qtyout-b.qtyin)*(select m.pricelist from m_product m where b.m_product_id=m.id(+)) as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,
0 as percost,
0 as tot_amtqty_acost,0 as tot_amtqty_percost,' ||
p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b ,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.IN_STATUS=2
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.INTIME between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 ||
')
and s.id in(' || v_sql4 || ')
and ' || v_sql5 || ' union all
select get_sequences(''RP_TRANSFER001PROC''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
a.billdate as changedate,
a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,
b.M_ATTRIBUTESETINSTANCE_ID,b.qty as QTY,0 QTYOUT,0 QTYIN,0 as preqtyin,0 as qtydiff,(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
b.qty*(select m.pricelist from m_product m where b.m_product_id=m.id) as TOT_AMTQTY_LIST, 0 as TOT_AMTOUT_LIST,0 TOT_AMTIN_LIST,0 as TOT_AMTPREIN_LIST,0 as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,0 as percost,
0 as tot_amtqty_acost,0 as tot_amtqty_percost,' || p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.STATUS=2
and a.out_STATUS=1
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.billdate between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 || ')
and s.id in(' || v_sql4 || ')
and ' || v_sql5 || ''; ELSE
EXECUTE IMMEDIATE ' INSERT INTO RP_TRANSFER001PROC
(ID,AD_CLIENT_ID,AD_ORG_ID,OWNERID,MODIFIERID,CREATIONDATE,MODIFIEDDATE,ISACTIVE,
changedate,DOCNO,c_Orig_Id,C_DEST_ID,DESCRIPTION,M_PRODUCT_ID,M_ATTRIBUTESETINSTANCE_ID,
qty,QTYOUT,QTYIN,preqtyin,qtydiff,PRICELIST,TOT_AMTQTY_LIST,TOT_AMTOUT_LIST,TOT_AMTIN_LIST,
TOT_AMTPREIN_LIST,TOT_AMTDIFF_LIST,m_product_alias_id,ACOST,percost,tot_amtqty_acost,
tot_amtqty_percost,AD_PI_ID )
select get_sequences(''RP_TRANSFER001PROC''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
to_char(a.OUTTIME,'yyyymmdd') as changedate, a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,
b.M_ATTRIBUTESETINSTANCE_ID, 0 as qty, b.QTYOUT QTYOUT ,0 as QTYIN,
(case when a.in_status=1 then b.QTYOUT else 0 end) preqtyin,0 as qtydiff,
(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
0 TOT_AMTQTY_LIST ,b.qtyout*(select m.pricelist from m_product m where b.m_product_id=m.id) TOT_AMTOUT_LIST ,0 as TOT_AMTIN_LIST,
(case when a.in_status=1 then b.qtyout*(select m.pricelist from m_product m where b.m_product_id=m.id) else 0 end) as TOT_AMTPREIN_LIST,0 as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,
0 as percost,
(select m.acost from m_product m where m.id=b.m_product_id)*b.QTYOUT as tot_amtqty_acost,
0 as tot_amtqty_percost,' || p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b ,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.OUT_STATUS=2
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.OUTTIME between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 ||
')
and s.id in(' || v_sql4 || ')
union all
、
功能同调拨汇总报表一样 */
v_sql1 VARCHAR2(4000);
v_sql2 VARCHAR2(4000);
v_sql3 VARCHAR2(4000);
v_sql4 VARCHAR2(4000);
v_sql5 VARCHAR2(4000);
v_date VARCHAR2(80);
v_datebegin VARCHAR2(8);
v_dateend VARCHAR2(8);BEGIN -- raise_application_error(-20201, p_pi_id);24338
DELETE FROM RP_TRANSFER001PROC a
WHERE a.ad_pi_id = p_pi_id;
COMMIT; --依次获取界面查询条件参数 SELECT t.info
INTO v_date
FROM ad_pinstance_para t
WHERE t.NAME = 'OUTINDATE' AND t.ad_pinstance_id = p_pi_id; IF instr(v_date, '<=') = 0 THEN
SELECT substr(v_date, 22, 8), substr(v_date, 35, 8)
INTO v_datebegin, v_dateend
FROM dual;
ELSE
v_datebegin := 0;
SELECT substr(TRIM(v_date), 13, 8)
INTO v_dateend
FROM dual;
END IF; SELECT t.info
INTO v_sql1
FROM ad_pinstance_para t
WHERE t.NAME = 'ORIGSTORE' AND t.ad_pinstance_id = p_pi_id; SELECT t.info
INTO v_sql2
FROM ad_pinstance_para t
WHERE t.NAME = 'DESTSTORE' AND t.ad_pinstance_id = p_pi_id; SELECT t.info
INTO v_sql3
FROM ad_pinstance_para t
WHERE t.NAME = 'PRODUCT' AND t.ad_pinstance_id = p_pi_id; SELECT t.info
INTO v_sql4
FROM ad_pinstance_para t
WHERE t.NAME = 'ASI' AND t.ad_pinstance_id = p_pi_id; SELECT t.info
INTO v_sql5
FROM ad_pinstance_para t
WHERE t.NAME = 'DOCNO' AND t.ad_pinstance_id = p_pi_id;
--插入数据
select to_char(OUTTIME,'yyyymmdd') into v_outtime from M_TRANSFER;
select to_char(INTIME,'yyyymmdd') into v_INTIME from M_TRANSFER;
IF v_sql5 IS NOT NULL THEN
EXECUTE IMMEDIATE ' INSERT INTO RP_TRANSFER001PROC
(ID,AD_CLIENT_ID,AD_ORG_ID,OWNERID,MODIFIERID,CREATIONDATE,MODIFIEDDATE,ISACTIVE,
changedate,DOCNO,c_Orig_Id,C_DEST_ID,DESCRIPTION,M_PRODUCT_ID,M_ATTRIBUTESETINSTANCE_ID,
qty,QTYOUT,QTYIN,preqtyin,qtydiff,PRICELIST,TOT_AMTQTY_LIST,TOT_AMTOUT_LIST,TOT_AMTIN_LIST,
TOT_AMTPREIN_LIST,TOT_AMTDIFF_LIST,m_product_alias_id,ACOST,percost,tot_amtqty_acost,
tot_amtqty_percost,AD_PI_ID )
select get_sequences(''RP_TRANSFER001PRO''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
to_char(a.OUTTIME,'yyyymmdd') as changedate, a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,b.M_ATTRIBUTESETINSTANCE_ID,
0 as qty, b.QTYOUT QTYOUT ,0 as QTYIN,
(case when a.in_status=1 then b.QTYOUT else 0 end) preqtyin,0 as qtydiff,
(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
0 TOT_AMTQTY_LIST ,b.qtyout*(select m.pricelist from m_product m where b.m_product_id=m.id) TOT_AMTOUT_LIST ,0 as TOT_AMTIN_LIST,
(case when a.in_status=1 then b.qtyout*(select m.pricelist from m_product m where b.m_product_id=m.id) else 0 end) as TOT_AMTPREIN_LIST,0 as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,0 as percost,
(select m.acost from m_product m where m.id=b.m_product_id)*b.QTYOUT as tot_amtqty_acost,
0 as tot_amtqty_percost,' || p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b ,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.OUT_STATUS=2
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.OUTTIME between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 || ')
and s.id in(' || v_sql4 || ')
and ' || v_sql5 || ' union all
select get_sequences(''RP_TRANSFER001PROC''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
to_char(a.INTIME,'yyyymmdd') as changedate,
a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,
b.M_ATTRIBUTESETINSTANCE_ID,0 AS QTY,0 as QTYOUT,b.QTYIN,0 as preqtyin,(b.QTYOUT-b.QTYIN) as qtydiff,(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
0 as TOT_AMTQTY_LIST, 0 as TOT_AMTOUT_LIST,b.qtyin*(select m.pricelist from m_product m where b.m_product_id=m.id) TOT_AMTIN_LIST,0 as TOT_AMTPREIN_LIST,
(b.qtyout-b.qtyin)*(select m.pricelist from m_product m where b.m_product_id=m.id(+)) as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,
0 as percost,
0 as tot_amtqty_acost,0 as tot_amtqty_percost,' ||
p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b ,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.IN_STATUS=2
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.INTIME between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 ||
')
and s.id in(' || v_sql4 || ')
and ' || v_sql5 || ' union all
select get_sequences(''RP_TRANSFER001PROC''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
a.billdate as changedate,
a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,
b.M_ATTRIBUTESETINSTANCE_ID,b.qty as QTY,0 QTYOUT,0 QTYIN,0 as preqtyin,0 as qtydiff,(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
b.qty*(select m.pricelist from m_product m where b.m_product_id=m.id) as TOT_AMTQTY_LIST, 0 as TOT_AMTOUT_LIST,0 TOT_AMTIN_LIST,0 as TOT_AMTPREIN_LIST,0 as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,0 as percost,
0 as tot_amtqty_acost,0 as tot_amtqty_percost,' || p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.STATUS=2
and a.out_STATUS=1
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.billdate between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 || ')
and s.id in(' || v_sql4 || ')
and ' || v_sql5 || ''; ELSE
EXECUTE IMMEDIATE ' INSERT INTO RP_TRANSFER001PROC
(ID,AD_CLIENT_ID,AD_ORG_ID,OWNERID,MODIFIERID,CREATIONDATE,MODIFIEDDATE,ISACTIVE,
changedate,DOCNO,c_Orig_Id,C_DEST_ID,DESCRIPTION,M_PRODUCT_ID,M_ATTRIBUTESETINSTANCE_ID,
qty,QTYOUT,QTYIN,preqtyin,qtydiff,PRICELIST,TOT_AMTQTY_LIST,TOT_AMTOUT_LIST,TOT_AMTIN_LIST,
TOT_AMTPREIN_LIST,TOT_AMTDIFF_LIST,m_product_alias_id,ACOST,percost,tot_amtqty_acost,
tot_amtqty_percost,AD_PI_ID )
select get_sequences(''RP_TRANSFER001PROC''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
to_char(a.OUTTIME,'yyyymmdd') as changedate, a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,
b.M_ATTRIBUTESETINSTANCE_ID, 0 as qty, b.QTYOUT QTYOUT ,0 as QTYIN,
(case when a.in_status=1 then b.QTYOUT else 0 end) preqtyin,0 as qtydiff,
(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
0 TOT_AMTQTY_LIST ,b.qtyout*(select m.pricelist from m_product m where b.m_product_id=m.id) TOT_AMTOUT_LIST ,0 as TOT_AMTIN_LIST,
(case when a.in_status=1 then b.qtyout*(select m.pricelist from m_product m where b.m_product_id=m.id) else 0 end) as TOT_AMTPREIN_LIST,0 as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,
0 as percost,
(select m.acost from m_product m where m.id=b.m_product_id)*b.QTYOUT as tot_amtqty_acost,
0 as tot_amtqty_percost,' || p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b ,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.OUT_STATUS=2
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.OUTTIME between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 ||
')
and s.id in(' || v_sql4 || ')
union all
、
解决方案 »
- 求助 oracle 10.2.0.5 升级 1086400.1 内容
- ORACLE导入导出问题...火急!!!
- 请问关于ORACLE中创建TYPE的用法,比如CREATE OR REPLACE TYPE type_str_split IS TABLE OF VARCHAR2 (4000)
- 请教创建数据库和备份恢复的,主要是涉及性能和易用性
- 关于死锁os杀不死的问题求解疑
- 远程导表的问题,急
- 大侠请进,导入数据库时出错。IMP-00058
- Oracle支持这个函数吗?substring
- 我是oracle初学者,请高手指点,如何快速入门?
- oracle插入date类型
- Oracle提示the account is locked
- oracle两张表批量更新,速度很慢?
to_char(a.INTIME,'yyyymmdd') as changedate,
a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,
b.M_ATTRIBUTESETINSTANCE_ID,0 AS QTY,0 as QTYOUT,b.QTYIN,0 as preqtyin,(b.QTYOUT-b.QTYIN) as qtydiff,(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
0 as TOT_AMTQTY_LIST, 0 as TOT_AMTOUT_LIST,b.qtyin*(select m.pricelist from m_product m where b.m_product_id=m.id) TOT_AMTIN_LIST,0 as TOT_AMTPREIN_LIST,
(b.qtyout-b.qtyin)*(select m.pricelist from m_product m where b.m_product_id=m.id(+)) as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,
0 as percost,
0 as tot_amtqty_acost,0 as tot_amtqty_percost,' ||
p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b ,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.IN_STATUS=2
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.INTIME between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 ||
')
and s.id in(' || v_sql4 || ')
union all
select get_sequences(''RP_TRANSFER001PROC''),a.AD_CLIENT_ID,a.AD_ORG_ID,a.OWNERID,a.MODIFIERID,a.CREATIONDATE,a.MODIFIEDDATE,a.ISACTIVE,
a.billdate as changedate,
a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,
b.M_ATTRIBUTESETINSTANCE_ID,b.qty as QTY,0 QTYOUT,0 QTYIN,0 as preqtyin,0 as qtydiff,(select m.pricelist from m_product m where b.m_product_id=m.id) as PRICELIST,
b.qty*(select m.pricelist from m_product m where b.m_product_id=m.id) as TOT_AMTQTY_LIST, 0 as TOT_AMTOUT_LIST,0 TOT_AMTIN_LIST,
0 as TOT_AMTPREIN_LIST,0 as TOT_AMTDIFF_LIST,
s.id as m_product_alias_id,
(select m.acost from m_product m where m.id=b.m_product_id) as ACOST,
0 as percost,
0 as tot_amtqty_acost,0 as tot_amtqty_percost,' ||
p_pi_id || '
from M_TRANSFER a,M_TRANSFERITEM b,m_product_alias s
where a.ID=b.M_TRANSFER_ID
and a.STATUS=2
and a.out_STATUS=1
and b.m_product_id=s.m_product_id
and b.m_attributesetinstance_id=s.m_attributesetinstance_id
and a.billdate between ' || v_datebegin || '
and ' || v_dateend || '
and a.c_orig_id in(' || v_sql1 || ')
and a.c_dest_id in(' || v_sql2 || ')
and b.m_product_id in(' || v_sql3 || ')
and s.id in(' || v_sql4 || ') ';
END IF;
--update cost
UPDATE RP_TRANSFER001PROC t
SET t.percost = percost_analyse(substr(t.changedate, 1, 6),
t.C_DEST_ID,
t.m_product_id)
WHERE t.ad_pi_id = p_pi_id; UPDATE RP_TRANSFER001PROC t
SET t.tot_amtqty_percost = t.percost * qtyout
WHERE t.ad_pi_id = p_pi_id; COMMIT;报下面错END RP_TRANSFER001PROC_GENERATE;Compilation errors for PROCEDURE NEANDS3.RP_TRANSFER001PROC_GENERATE76 Error: PLS-00103: 出现符号 "YYYYMMDD"在需要下列之一时:
* & = - + ; < / > at
in is mod remainder not rem return returning
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between into using || multiset bulk member
SUBMULTISET_
符号 "*在 "YYYYMMDD" 继续之前已插入。
Line: 76
Text: to_char(a.OUTTIME,'yyyymmdd') as changedate, a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,b.M_ATTRIBUTESETINSTANCE_ID,101 Error: PLS-00103: 出现符号 "YYYYMMDD"在需要下列之一时:
* & = - + ; < / > at
in is mod remainder not rem return returning
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between into using || bulk member SUBMULTISET_
符号 "*在 "YYYYMMDD" 继续之前已插入。
Line: 101
Text: to_char(a.INTIME,'yyyymmdd') as changedate,156 Error: PLS-00103: 出现符号 "YYYYMMDD"在需要下列之一时:
* & = - + ; < / > at
in is mod remainder not rem return returning
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between into using || multiset bulk member
SUBMULTISET_
符号 "*在 "YYYYMMDD" 继续之前已插入。
Line: 156
Text: to_char(a.OUTTIME,'yyyymmdd') as changedate, a.DOCNO,a.c_Orig_Id,a.C_DEST_ID,a.DESCRIPTION,b.M_PRODUCT_ID,181 Error: PLS-00103: 出现符号 "YYYYMMDD"在需要下列之一时:
* & = - + ; < / > at
in is mod remainder not rem return returning
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between into using || bulk member SUBMULTISET_
符号 "*在 "YYYYMMDD" 继续之前已插入。
Line: 181
Text: to_char(a.INTIME,'yyyymmdd') as changedate,
select to_char(OUTTIME,'yyyymmdd') into v_outtime from M_TRANSFER;
select to_char(INTIME,'yyyymmdd') into v_INTIME from M_TRANSFER;
好像没有看到v_outtime 和 v_INTIME 声明的地方啊