select '订单采购' as optype,
b.ID,--id
b.AD_CLIENT_ID,--公司
b.AD_ORG_ID,--组织
a.billdate,--单据日期
a.DOCNO,--单据编号
a.DOCTYPE,--单据类型
a.C_STORE_ID,--采购店仓
a.C_SUPPLIER_ID,--供应商
a.DESCRIPTION,--备注
b.M_PRODUCT_ID,--商品
b.M_ATTRIBUTESETINSTANCE_ID,--色尺寸
t.id as M_SIZES_ID,/*add by wdx 2012-06-06 用于前台获取尺寸中的矩阵序列*/
b.QTYIN as QTYPUR,--采购入库数量
0 as QTYRETPUR,--采购退货数量
b.PRICELIST,--标准价
b.TOT_AMTIN_LIST as AMTLISTPUR,--标准入库金额
0.0000 as AMTLISTRETPUR,--标准退货金额
b.PRICEACTUAL,--采购价
b.TOT_AMTIN_ACTUAL as AMTPUR,--采购入库金额
b.PRICECHECK,--审核价
b.TOT_AMTIN_PCHECK,--审核金额
0.0000 as AMTRETPUR,--采购退货金额
b.DISCOUNT as DISCOUNTPUR,--采购折扣
0.0000 as DISCOUNTRETPUR,--退货折扣(%)
s.id as M_PRODUCT_ALIAS_id,--颜色尺寸
a.datein,--出入库日期
b.qty,--单据数量
b.qtydiff,--入库数量差异
e.QTY as TOT_QTY,--ysq计划单量
(r.PRICELIST*r.DISCOUNT*r.qty) as TOT_AMT_LIST,--ysq计划采购标准金额
(b.PRICELIST*b.DISCOUNT*b.qty) as TOT_AMT_ACTUAL--ysq计划采购金额
from M_PURCHASE a,
M_PURCHASEITEM b,
M_PRODUCT_ALIAS s,
M_ATTRIBUTEVALUE t,
B_PLANITEM e,
B_PLAN q,
b_poitem r,
b_po d
where a.ID = b.M_PURCHASE_ID
and b.m_product_id = s.m_product_id
and b.m_attributesetinstance_id = s.m_attributesetinstance_id
and a.DOCTYPE = 'POO'--ysq
and e.b_plan_id = q.id
and q.id in (SELECT q.id FROM B_PO,B_PLAN WHERE
DBMS_LOB.INSTR(B_PO.B_PLAN_filter,'B_PLAN.docon',1,1)>0;) = q.id
and r.b_po_id = d.id
and a.b_po_id = d.id
/*add by wdx 2012-06-06 用于前台获取尺寸中的矩阵序列*/
and exists (select 1
from m_attributesetinstance m
where t.id = m.value2_id
and m.id = b.m_attributesetinstance_id)
and a.IN_STATUS = 2;
b.ID,--id
b.AD_CLIENT_ID,--公司
b.AD_ORG_ID,--组织
a.billdate,--单据日期
a.DOCNO,--单据编号
a.DOCTYPE,--单据类型
a.C_STORE_ID,--采购店仓
a.C_SUPPLIER_ID,--供应商
a.DESCRIPTION,--备注
b.M_PRODUCT_ID,--商品
b.M_ATTRIBUTESETINSTANCE_ID,--色尺寸
t.id as M_SIZES_ID,/*add by wdx 2012-06-06 用于前台获取尺寸中的矩阵序列*/
b.QTYIN as QTYPUR,--采购入库数量
0 as QTYRETPUR,--采购退货数量
b.PRICELIST,--标准价
b.TOT_AMTIN_LIST as AMTLISTPUR,--标准入库金额
0.0000 as AMTLISTRETPUR,--标准退货金额
b.PRICEACTUAL,--采购价
b.TOT_AMTIN_ACTUAL as AMTPUR,--采购入库金额
b.PRICECHECK,--审核价
b.TOT_AMTIN_PCHECK,--审核金额
0.0000 as AMTRETPUR,--采购退货金额
b.DISCOUNT as DISCOUNTPUR,--采购折扣
0.0000 as DISCOUNTRETPUR,--退货折扣(%)
s.id as M_PRODUCT_ALIAS_id,--颜色尺寸
a.datein,--出入库日期
b.qty,--单据数量
b.qtydiff,--入库数量差异
e.QTY as TOT_QTY,--ysq计划单量
(r.PRICELIST*r.DISCOUNT*r.qty) as TOT_AMT_LIST,--ysq计划采购标准金额
(b.PRICELIST*b.DISCOUNT*b.qty) as TOT_AMT_ACTUAL--ysq计划采购金额
from M_PURCHASE a,
M_PURCHASEITEM b,
M_PRODUCT_ALIAS s,
M_ATTRIBUTEVALUE t,
B_PLANITEM e,
B_PLAN q,
b_poitem r,
b_po d
where a.ID = b.M_PURCHASE_ID
and b.m_product_id = s.m_product_id
and b.m_attributesetinstance_id = s.m_attributesetinstance_id
and a.DOCTYPE = 'POO'--ysq
and e.b_plan_id = q.id
and q.id in (SELECT q.id FROM B_PO,B_PLAN WHERE
DBMS_LOB.INSTR(B_PO.B_PLAN_filter,'B_PLAN.docon',1,1)>0;) = q.id
and r.b_po_id = d.id
and a.b_po_id = d.id
/*add by wdx 2012-06-06 用于前台获取尺寸中的矩阵序列*/
and exists (select 1
from m_attributesetinstance m
where t.id = m.value2_id
and m.id = b.m_attributesetinstance_id)
and a.IN_STATUS = 2;
解决方案 »
- 怎样把Oracle 的DMP文件导入SQL Server 数据库?
- exists 之后追加怎么写???
- 请高手帮忙看看这个sql语句,多谢。
- 中文分词服务器(C语言开发+词库+源代码),最大特色可以让javascript来调用!当然也可以让其他任何语言来调用
- ORACLE 日期参数格式怎么计算啊?各位高手,散分了
- update一个日期类型的出错
- 如何利用存储过程动态创建表?
- Oracle 支持的SQL中 用什么函数据把字符串转换成整型???
- oracle8i的OPS和9i RAC 有什么区别
- 在建立OBJECT时会出现pls-00103错误!在线等待!
- 关于update一条数据库中不存在的数据
- Oracle PROCEDURE 求大神解决! 菜鸟初学。
分号放在括号里的?
还有SQL里可以直接用DBMS_LOB包么?不用PL/SQL?
DBMS_LOB.INSTR(B_PO.B_PLAN_filter,'B_PLAN.docon',1,1)>0;) = q.id有问题吧,怎么又是in,后边怎么还有"=" ?
DBMS_LOB.INSTR(B_PO.B_PLAN_filter,'B_PLAN.docon',1,1)>0;) = q.id
把后面的=q.id去掉
DBMS_LOB.INSTR(B_PO.B_PLAN_filter,'B_PLAN.docon',1,1)>0;) = q.id
这里改成
and q.id in (SELECT q.id FROM B_PO,B_PLAN WHERE
DBMS_LOB.INSTR(B_PO.B_PLAN_filter,'B_PLAN.docon',1,1)>0)
→
INSTR(B_PO.B_PLAN_filter,'B_PLAN.docon',1,1)>0)貌似这样吧。