【Oracle】NC备料计划报表

系列文章

C#底层库--SQLBuilder脚本构建类（select、insert、update、in、带条件的SQL自动生成）
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/129179216](https://blog.csdn.net/youcheng_ge/article/details/129179216)

C#底层库--MySQL数据库操作辅助类（推荐阅读）
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/126886379](https://blog.csdn.net/youcheng_ge/article/details/126886379)

C#底层库--SQLite的使用（小型、本地数据库）
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/123666958](https://blog.csdn.net/youcheng_ge/article/details/123666958)

【提高编程效率】Excel数据批量导入数据库
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/126427323](https://blog.csdn.net/youcheng_ge/article/details/126427323)

【Oracle】数据库服务器限制ip访问
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/122220930](https://blog.csdn.net/youcheng_ge/article/details/122220930)

【Oracle】Excel导入数据教程
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/118722756](https://blog.csdn.net/youcheng_ge/article/details/118722756)

【Oracle】数据库还原教程_数据泵
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/118054855](https://blog.csdn.net/youcheng_ge/article/details/118054855)

【SQL】如何查询表字段并识别主键
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/110820405](https://blog.csdn.net/youcheng_ge/article/details/110820405)

【SQL】outer apply的用法
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/79903489](https://blog.csdn.net/youcheng_ge/article/details/79903489)

【SQL】多表连接重复数据处理
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/79903619](https://blog.csdn.net/youcheng_ge/article/details/79903619)

MySQL安装教程（详细）
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/126037520](https://blog.csdn.net/youcheng_ge/article/details/126037520)

MySQL卸载教程（详细）
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/129279265](https://blog.csdn.net/youcheng_ge/article/details/129279265)

【MySQL】group by分类汇总，如何增加“总计”字段?
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/128217837](https://blog.csdn.net/youcheng_ge/article/details/128217837)

【MySQL】WITH CHECK OPTION的用法
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/128147196](https://blog.csdn.net/youcheng_ge/article/details/128147196)

【MySQL】使用存储过程插入千万级数据如何提升效率？
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/77728189](https://blog.csdn.net/youcheng_ge/article/details/77728189)

【MySQL】数据库表行列转置的实现
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/77625052](https://blog.csdn.net/youcheng_ge/article/details/77625052)

【MySQL】查询中，NULL值转换为空字符串
本文链接：[https://blog.csdn.net/youcheng_ge/article/details/130381878](https://blog.csdn.net/youcheng_ge/article/details/130381878)
 

---

前言

本专栏为【数据库】，主要介绍SQL的功能与特点、SQL数据定义语言（表、视图、索引、约束）、SQL数据操作语言（数据检索、数据插入、数据删除、数据更新）、创建与删除触发器、SQL数据控制语言（安全性和授权、事务处理）以及嵌入式SQL。
如果你对本专辑感兴趣，持续关注吧。大家有任何问题，也可以评论区反馈，私信我。

一、技术介绍

NC备料计划报表

二、解决方案

--当月备料计划领料
select a.vbatch as 批次号,c.invcode as 存货编码,c.invname as 存货名称,c.invspec as 规格,
sum(a.nnumber) as 入库数量,a.nprice as 单价,sum(a.nmoney) as 金额,f.vreceiptcode as 销售单号,g.invcode as 存货编码,h.vdef1 as 产品名称
from ia_bill_b a inner join ic_general_b b on a.csourcebillitemid=b.cgeneralbid
left join bd_invbasdoc c on a.cinvbasid=c.pk_invbasdoc
left join mm_mo d on b.cfirstbillhid=d.pk_moid
left join so_saleorder_b e on d.firstbillrowid=e.corder_bid
left join so_sale f on d.firstbillid=f.csaleid
left join bd_invbasdoc g on e.cinvbasdocid=g.pk_invbasdoc
left join so_saleexecute h on d.firstbillrowid=h.csale_bid 
where nvl(a.dr,0)=0 and nvl(b.dr,0)=0 and nvl(c.dr,0)=0 and nvl(d.dr,0)=0 and a.cbilltypecode='I6' and a.cicbilltype='4D' and a.dbizdate>='2020-06-01' and a.dbizdate<='2020-06-30' 
and (c.invcode like 'Y%' OR c.invcode like 'B%')
group by a.vbatch,
c.invcode,c.invname,c.invspec ,
a.nprice,f.vreceiptcode,g.invcode,h.vdef1


--当月产成品入库
select a.vbatch as 批次号,
--c.invcode as 存货编码,c.invname as 存货名称,c.invspec as 规格,
sum(a.nnumber) as 入库数量,a.nprice as 单价,sum(a.nmoney) as 金额,f.vreceiptcode as 销售单号,g.invcode as 存货编码,h.vdef1 as 产品名称
from ia_bill_b a inner join ic_general_b b on a.csourcebillitemid=b.cgeneralbid
--left join bd_invbasdoc c on a.cinvbasid=c.pk_invbasdoc
left join mm_mo d on b.cfirstbillhid=d.pk_moid
left join so_saleorder_b e on d.firstbillrowid=e.corder_bid
left join so_sale f on d.firstbillid=f.csaleid
left join bd_invbasdoc g on e.cinvbasdocid=g.pk_invbasdoc
left join so_saleexecute h on d.firstbillrowid=h.csale_bid 
where nvl(a.dr,0)=0 and a.cbilltypecode='I3' and a.cicbilltype='46' and a.dbizdate>='2020-06-01' and a.dbizdate<='2020-06-30' 
group by a.vbatch,
--c.invcode,c.invname,c.invspec ,
a.nprice,f.vreceiptcode,g.invcode,h.vdef1

--累计领料
select a.vbatch as 批次号,
c.invcode as 存货编码,c.invname as 存货名称,c.invspec as 规格,
sum(a.nnumber) as 入库数量,a.nprice as 单价,sum(a.nmoney) as 金额,f.vreceiptcode as 销售单号,g.invcode as 存货编码,h.vdef1 as 产品名称
from ia_bill_b a inner join ic_general_b b on a.csourcebillitemid=b.cgeneralbid
left join bd_invbasdoc c on a.cinvbasid=c.pk_invbasdoc
left join mm_mo d on b.cfirstbillhid=d.pk_moid
left join so_saleorder_b e on d.firstbillrowid=e.corder_bid
left join so_sale f on d.firstbillid=f.csaleid
left join bd_invbasdoc g on e.cinvbasdocid=g.pk_invbasdoc
left join so_saleexecute h on d.firstbillrowid=h.csale_bid 
--left join (select  f.vreceiptcode as 销售单号
--from ia_bill_b a inner join ic_general_b b on a.csourcebillitemid=b.cgeneralbid
--left join bd_invbasdoc c on a.cinvbasid=c.pk_invbasdoc
--left join mm_mo d on b.cfirstbillhid=d.pk_moid
--left join so_saleorder_b e on d.firstbillrowid=e.corder_bid
--left join so_sale f on d.firstbillid=f.csaleid
--left join so_saleexecute h on d.firstbillrowid=h.csale_bid 
--where nvl(a.dr,0)=0 and a.cbilltypecode='I3' and a.cicbilltype='46' and a.dbizdate>='2020-06-01' and a.dbizdate<='2020-06-30' 
--)i on 
where nvl(a.dr,0)=0 and nvl(b.dr,0)=0 and nvl(c.dr,0)=0 and nvl(d.dr,0)=0 and a.cbilltypecode='I6' and a.cicbilltype='4D'  
and (c.invcode like 'Y%' OR c.invcode like 'B%')
and f.vreceiptcode in (select  f.vreceiptcode as 销售单号
from ia_bill_b a inner join ic_general_b b on a.csourcebillitemid=b.cgeneralbid
--left join bd_invbasdoc c on a.cinvbasid=c.pk_invbasdoc
left join mm_mo d on b.cfirstbillhid=d.pk_moid
--left join so_saleorder_b e on d.firstbillrowid=e.corder_bid
left join so_sale f on d.firstbillid=f.csaleid
--left join so_saleexecute h on d.firstbillrowid=h.csale_bid 
where nvl(a.dr,0)=0 and a.cbilltypecode='I3' and a.cicbilltype='46' and a.dbizdate>='2020-06-01' and a.dbizdate<='2020-06-30' 
)
group by a.vbatch,
c.invcode,c.invname,c.invspec ,
a.nprice,f.vreceiptcode,g.invcode,h.vdef1

select * from ia_bill_b where vbillcode='I3200708000272'
select * from mm_mo where pk_moid ='1002A8100000002NA9O5'
select * from bf_ksgd_b4 where ts>'2020-09-22 00:00:00'
select * from bd_invbasdoc where pk_invbasdoc='0001U61000000001U3D5'   
select pk_invbasdoc from bd_produce where pk_produce='1002U61000000006F922'
select * from mm_mo