台账及库存管理制

第一章 总

第1条 为规范司的存货购销存业务，防范因存货管理不规范给公司带来营风险及经济损失，确保公司资产的安全有效，特定

第2条 本制适用于涉及公司存货采购、存货验收、存货确认、存保管、货发出、存货账目、存货盘等业务相关

第3条 本制度的存货是指公司为满足日常经营活动需要，外购持以备出的商品以及提供劳务过程中需用的材、物

第二章 存货的

第4条 相关部门根部门工作的需求向行政人事部提出存货采购计划。 1. 相关门对于预内存货采购项目，应当严格按照预执行度理请

2. 相关部对于超预算和预算外存货采购项目，应由具相应批权限的人员审批后，再行办理购

1

1)采购金额大于【】元，由_____审

2)采购金额在【】元(含),【】元(含)，由______批。 3)采购金额小于【】元，由______

第5条 政部根据存货采购计划，归类汇总衡现库存存货后填写“存货请

第6条 采购专员根据“存货请购单”所列示的内容，编制一式多联、预先编号“存货购单”提交采购经理审批，采购经理批上总经

第7条 采购员根据审批后的“存货订购单”向供应商询价、比，选供应商，提交行政人事部审核后再由总理

第8条 行政人事部经在总经理授权下，与供应商签订采购合同。 第9条 采购专员根据已订的采购同补充完善订单中的相关内容。其正联留存，副联交司财

第三章 存货的验收入库及

第10条 采购专员负责货物跟催，接货后，按照采购订单上

一进行核对，单据与货物是否一致，交货期与订交期是否一致，货物数量是

第11条 政人事部根据合同的约定以及货物的实际特，进行量检验，行政人事部出具“质量验

2

第12条 采购专员在清核对货物时出现问题以及货物存在质量题的，采购专员应根据合同定及货物实际情况提出具体的解决方案，提交行政事经理总经

第13条 采购专与供应商就具体问题协商后，进行退换货处理。 第14条 收合格的物，由行政人事部编制一式多联的“入库”理入

第15条 行政人部根据相关采购合同、“存货订购单”核对“入库单”、供商发票单证的正确性，确定无误后编制“款单”交

第16条 财部审核“付款凭单”、“入库单”、供应商发票等证无后，编制记账凭证，确认记录存货及相负

第四章 存货的

第17条 行政人事部对存货进行验收入库，根存货属性、尺寸等的不同安排存

第18条 行政人事部对入库的存建立存货明细台账，详细登记存货类、编号、名称、规格号、计量单位、数量等内容，并定期与财部就货品种、量、金额等(转载于:www.XltkWJ.Com 小 文档 :库

第19条 行政人事部要做好库存货的保管工

3

巡查和定期抽检在库存货的状

第20条 对代管、暂存的存货，应单独放记录，避免与本公司存

第21条 对于进入仓库的人员应办理进出记续，未经授权人员不得接

第五章 存货的发

第22条 相关部门根据已经审批签订的合同中相关款，编制发货通知单送交行政

第23条 行政人事部实相关部门部签发的发货通知单，根据规定品种、数量、时间等要求备货，并据此编制式多联的“出库单”，一联行政人事部留，一送交务部

第24条 财务部提交的各项单据进行审核，审核无误后允许发货。 第25条 行政人事调整账卡，核销存货，并进行复核，复核误，进

第六章 存货的账目

第29条 存货账目的范围包括库、出库单、存货台

第30条 所有出入库资，均需按品种登记入库单和出库单，并收、发数量登记后结出余额，时与实存量进行核对，并做到账实相符。如有不符，查明因，及时

4

第31条 行政人事部对库存货建立存货台帐，详细登记存货类别、编号、名称、规格型号、数量、计量位等内容，并定期与财务部就存货品种、数、金等进

第32条 存货台账记录应有合法依据，凭证须完无缺，记账时应遵守以下记

1(入库单记“收”，出库单记“发出”，相关资料作为其附件，分顺序编，作为记账索引号，按月装订册，以日后

2(账页不准毁，遇有改错，可以划红线加盖私章订正。账面录，严禁挖、补、刮、擦和使涂

3(账面记录采用永续盘存，每次发生增减变动，及时算结存余额。 4(启用账簿，应在账簿封面载明业名称、年，在启用页内载明账簿名称、启用日期，由记账员签或盖，并

5(调换记账人员时，应注明交接日期，并由移交人、交接人签名或盖章。 第33条 存台账是记录实物的收入、发出、结情的重账

按品种、规格登记，妥善保管，年装成册，至少保存

第34条 存货台账不得随意修改，如确需修存货账，应当经有效授权批准方

第35条 行政人事人员应每及时登记台账，

5

期同财务部会计核对出、入库单和细账、台账，

相符、账账相符。

附:

存货请购单

篇二:物资库存盘点

物资库存盘点台

本表编号:

篇三:办公用品库存

办公用品库存台

6

库存商品台账

1kg2kg3kg4kg5kg6kg7kg8kg9kg10kg合计0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 20105毛绒布错误:502错误:502错误:502错:502

误:502错误:502低弹平布错误:502错误:502错:502错误:502错误:502错

误:502错误:502摇粒绒错误:502错误:502错:502错误:502错误:502错

误:502错误:502天鹅绒错误:502错误:502错:502错误:502错误:502错

误:502错误:502TC氨纶汗布错误:502错误:502误:502错误:502错误:502错

误:502错误:502人棉氨纶汗布错误:502错误:502错误:502错误:502错误:502

错误:502错误:502氨纶罗纹错误:502错误:502误:502错误:502错误:502错

误:502错误:502全棉汗布错误:502错误:502错:502错误:502错误:502错

误:502错误:502全棉毛圈布错误:502错误:502误:502错误:502错误:502错

误:502错误:502珊瑚绒错误:502错误:502错:502错误:502错误:502错

误:502错误:502VALUE凭 证摘要收 入 (借)发 出 (贷方)结 存

数数量单价金额数量单价金额数量单价金0

12600274670.00 0115销售60480.00 0.00214190.00 0126销售60660.00 0.00153530.00 0215

126000.00 0.00215420.00 041入库126000.00 0.00341420.00 041

0.00320420.00 045销售23670.00 0.00296750.00 0410销售140350.00 0.00156400.00 0415销售3469.150.00 0.0012170.850.00 0.00 420售36250.00 0.008545.850.00 0430销售59500.00 0.002595.850.00 00.00 0.002595.850.00 01243

规格:计量单位: KG2010年 返回VALUE 证

结 存月日种类数数量单价金额数量单价金额数量价金

0.0013516.80.00 0315销售9595.80.00 0.0039210.00 0322销售39210.00 0.0000.00 00.00 0.0000.00 00.00 0.0000.00 00.00 0.0000.00 00.00 0.0000.00 00.00 0.0000.00 00.00

0.0000.00 00.00 0.0000.00 00.00 0.0000.00 00.00 0.0000.00 00.00 0.0000.00 00.00

0.0000.00 00.00 0.0000.00 00.00 0.0000.00 00.00 0.0000.00 01243存品类

商品规格:计量

方)结 存月日类号数数量单价金额数量单价金额量单

216800.00 00.00 00.00 00.00 00.00 00.00 00.00 000.00 000.00 000.00 000.00 000.00

000.00 000.00 000.00 000.00 000.00 000.00 000.00 000.00 01243存商类

商品规格:计量

方)结 存月种类号数数量单价金额数量单价金数

3595.20.00 0.003595.20.00 0213销售3595.20.00 0.0000.00 000.00 00.00 00.00 000.00 000.00 000.00 000.00 000.00 000.00 000.00 000.00 000.00 000.00 000.00 000.00 000.00

01243库存品类别: 库存商品规格:计量单位: KG2010年 返回VALUE凭

入 (借方)发 出 (贷方)结 存月日种号数量单价金额数量单价

价金额0453612入库25207056120销售28144242126销售424200.00 0313入库

613061300.00 0315销售237337570.00 0328

0410入库12960212550.00 0410销售42950.00 0.00169600.00 0415

0.00105070.00 0424销售0.00 64530.00 0.0040540.00 0427销售0.00 18900.00

0.0021640.00 00.00 0.00 0.0021640.00 00.00 0.00 0.0021640.00 00.00 0.00

0.0021640.00 00.00 0.00 0.0021640.00 00.00 0.00 0.0021640.00 01243存商

库存商品规格:量单位: KG2010年 返回VALUE凭 证摘要收 入 (借

(贷方)结 存日种类号数数量单价金额数量单金额

库60000.00 0.0060000.00 0110销售20130.00 0.0039870.00 0126

0.0027060.00 021入库6693.440.00 09399.440.00 0213销售21360.00 0.007263.440.00

031入库3366.40.00 0.0010629.840.00 035销售3366.40.00 0.007263.440.00 0314销售

0.00 2564.440.00 0.0046990.00 0328销售0.00 20720.00 0.0026270.00 0330销售0.00

26270.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 01243库存商品类别: 存商品规格:量单位: KG2010年 返

摘要收 入 (借方)发 出 (贷方)结 存日类号数数量单价金额数

数量单价金额000.00 021入库259250.00 0.00259250.00 021销售40130.00

0.00219120.00 025销售87500.00 0.00131620.00 0210销售65780.00 0.0065840.00 0215

销售28710.00 0.0037130.00 0225销售37130.00 0.0000.00 01243库存商类

商品规格:计量

方)结 存月种类号数数量单价金额数量单价金数

549054900.00 0110销售23040.00 0.0031860.00 0126销售31860.00 0.0000.00 01243库

存商品类别:

方)发 出 (贷方)结 存月日种类号数数单金额数量单价金额数

000.00 012入9000.00 0.009000.00 0126销售9000.00 0.0000.00 021入

0.0076860.00 022销售2156.20.00 0.005529.80.00 025销售0.00 5529.80.00 0.0000.00

00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 00.00 0.00 0.0000.00 01243库存商品类别: 库存商品规格:计位: KG2010

入 (借方)发 出 (贷方)结 存月日种号数量单价金额数量单价

价金额0上年结转454212入库25650.00 0.0071070.00 0110销售4400.00 0.0066670.00

0126销售66670.00 0.0000.00 01243存商品

年 返回

台账及库存管理制度

台账及库存

第一章 总则

第1条 为规范公司的存货购销存业务，防范因存货理不规范给公司带来经营险及其经济损失，确保资产的安全与有，特

第2条 本制度适用于涉及公司存货采购、存验收、存货确认、存货管、存货发出、存货目、存货盘点等业务的

第3条 本制度中的存货是指公司为满足日常营活动需要，外购持有以出售的商品以及提供务过程中需耗用

第二章

第4条 相关部门根据部门作的需求向政人事部提出存货采购

1. 相关部门对于预算内存货购项目，应当格按照预算执行进度办理请

2. 相关部门对于超预算和预算外存货购项目，应由具备应审批权限的人批后，再行办理请

1)采购金额大于【】元，_____审

2)采购金额在【】元(),【】元(含)，由______

3)采购金额小于【】元，______审

第5条 行政部根据存货采购计，归类汇总平现有库存存货填写“存货请

第6条 采购专员应根据“存货请购单”所列示的内容，编制一式多联、预先编号的“存货订购单”提交采购批，采购经理审后上报

第7条 采购专员根据审批后的“存货订购”向供应商询价、比价，选择供应商，提交人事部审核后再由总

第8条 行政人事部经在总经理授下，与供应商签订采购

第9条 采购专员根据已签订的采购合补充完善订单中的相内容。其正联留，副联送交公司财务

第三章 存货的

第10条 采购专员负责物跟催，接货物后，按照采购订单上

1

一进行核对，单据与货物是否一，交货期与订单货期是否一，货物数量是

第11条 行政人事部根据合同的约定及货物的实际特点，行质量检验，行事部出具“质量检

第12条 采购专员在清点核对货物时出现问题以及货物存在问题的，采购专员应根据合同规及货物的实际情况提出具决方案，提交行政人部经理

第13条 采购专员与应商就具体题协商后，进行退换货

第14条 验收合格的货物，行政人事部编制式多联的“库单”办理入

第15条 行政人事部根据相关采购合同、“存货订单”核对“入库单”、供应发票等单证的正确性，无误后编制“付款单”

第16条 财务部审核“付款凭单”、“入单”、供应商发票等单无误后，编制记账，确认记录存货及相

第四章

第17条 行政人事部对存货进验收入库，根据货的属性、尺寸的不同安排存

第18条 行政人事部对入库的存货建立存货明细台账，详细登存货类别、编号、名称、规格型号、计量单位、数量等内容，定与财务部就存货品种、量、金

第19条 行政人事部要做好在存货的保管工作，每日巡查和定抽检在库存货

第20条 对代管、暂存的货，应单独存和记录，避免与本公司存货

第21条 对于进入仓库的人应办理进出登手续，未经授权人员不得接

2

第五章

第22条 相关部门根据已经审批订的合同中的相条款，编制发通知单送交行政

第23条 行政人事部核实相关部门部签发的发货通知单，根据规的品种、数量、时间等要求备货，并此编制一式多联的“出库单”，联行政人事部留存，联送交财

第24条 财务部对提交各项单据进审核，审核无误后允许

第25条 行政人事部调整账，核销存货，进行复核，复核无误后，进

第六章 存货

第29条 存货账目的围包括入单、出库单、存货台

第30条 所有出入库物资，均需按品种登记入库单和出库单，收、发数量登记后结出余额，随与实存数量进行核对，并做相符。如有不符，应查原因，

第31条 行政人事部对入库存货建立存货台帐，详细登记存类别、编号、名称、规格型号、量、计量单位等内容，并财务部就存货品种、量、金

第32条 存货台账记录应有合依据，凭证必须整无缺，记账应遵守以下记

1(入库单记“收入”，出库单记“发出”，相资料作为其附件，分别顺编号，作为记账索引，按月装订成册，以便

2(账页不准撕毁，遇有改错，可以划线加盖私章订正。面记录，严禁挖、补、刮、擦和使用

3(账面记录采用永续盘存，每次发生减变动，及时计算结存

4(启用账簿，应在账簿封面载明企业名称、年，在启用页内载明账簿称、启用日期，由人员签名或盖章，并

5(调换记账人员时，应注交接日期，由移交人、交接人签名或

第33条 存货台账是记实物的收入、发出、结存情况的重要账

3

按品种、规格登记，妥善保管，年装订成册，至少保存

第34条 存货台账不得随意修，如确需修改存台账，应当经效授权批准方

第35条 行政人事人员应每日及时登记台账，每月定期同财务部会计对出、入库单和明细、台账，确保账实符、

4

附:

存货请购单

时间 物品名称 数量 领部门 领用人

5

质量检测单

序号 品名 规格型号 单位 量 检测意见 请

6

入库单

申购部门名称 申请时 物品名称 型号 数量 价格 购

入库物品

财政部审核单

行政部审核

领导审批

7

出库单

申请部门名称 申请时 物品名称 型号 数量 价格 物

申请物品

财务部审核

行政部审核

领导审批

8

库存台账按物料操作说明

1.1报表查看

1.2报表

点修改

修改报表标题

其中:

Fnumber:物料代码

Fname:物料名称

O1:1月

C1:1月

Block1:截止1

以上类推

相关的SQL:

---需求描述:

----按年份统计每月的物类型的订单，出库数，及每月的累计未

----上述单据

select s.fname, sum(R.orderqty_1) o1,sum(R.outqty_1)

c1,sum(R.orderqty_1)-sum(R.outqty_1)as block1

, sum(R.orderqty_2) o2,sum(R.outqty_2) c2,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)as block2

, sum(R.orderqty_3) o3,sum(R.outqty_3) c3,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)

-sum(R.outqty_3)as block3

, sum(R.orderqty_4) o4,sum(R.outqty_4) c4,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)

-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)as block4

, sum(R.orderqty_5) o5,sum(R.outqty_5) c5,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)

-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)+sum(R.orderqty_5)-sum(R.outqty_5)as block5

, sum(R.orderqty_6) o6,sum(R.outqty_6)

c6,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)+sum(R.orderqty_5)-sum(R.outqty_5)+sum(R.orderqty_6)-sum(R.outqty_6)as block6

, sum(R.orderqty_7) o7,sum(R.outqty_7)

c7,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)+sum(R.orderqty_5)-sum(R.outqty_5)+sum(R.orderqty_6)-sum(R.outqty_6)+sum(R.orderqty_7)-sum(R.outqty_7)as block7

, sum(R.orderqty_8) o8,sum(R.outqty_8)

c8,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)+sum(R.orderqty_5)-sum(R.outqty_5)+sum(R.orderqty_6)-sum(R.outqty_6)+sum(R.orderqty_7)-sum(R.outqty_7)+sum(R.orderqty_8)-sum(R.outqty_8)as block8

, sum(R.orderqty_9) o9,sum(R.outqty_9)

c9,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)+sum(R.orderqty_5)-sum(R.outqty_5)+sum(R.orderqty_6)-sum(R.outqty_6)+sum(R.orderqty_7)-sum(R.outqty_7)+sum(R.orderqty_8)-sum(R.outqty_8)+sum(R.orderqty_9)-sum(R.outqty_9)as block9

, sum(R.orderqty_10) o10,sum(R.outqty_10)

c10,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)+sum(R.orderqty_5)-sum(R.outqty_5)+sum(R.orderqty_6)-sum(R.outqty_6)+sum(R.orderqty_7)-sum(R.outqty_7)+sum(R.orderqty_8)-sum(R.outqty_8)+sum(R.orderqty_9)-sum(R.outqty_9)+sum(R.orderqty_10)-sum(R.outqty_10)as block10

, sum(R.orderqty_11) o11,sum(R.outqty_11)

c11,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)+sum(R.orderqty_5)-sum(R.outqty_5)+sum(R.orderqty_6)-sum(R.outqty_6)+sum(R.orderqty_7)-sum(R.outqty_7)+sum(R.orderqty_8)-sum(R.outqty_8)+sum(R.orderqty_9)-sum(R.outqty_9)+sum(R.orderqty_10)-sum(R.outqty_10)+sum(R.orderqty_11)-sum(R.outqty_11)as block11

, sum(R.orderqty_12) o12,sum(R.outqty_12)

c12,sum(R.orderqty_1)-sum(R.outqty_1)+sum(R.orderqty_2)-sum(R.outqty_2)+sum(R.orderqty_3)-sum(R.outqty_3)+sum(R.orderqty_4)-sum(R.outqty_4)+sum(R.orderqty_5)-sum(R.outqty_5)+sum(R.orderqty_6)-sum(R.outqty_6)+sum(R.orderqty_7)-sum(R.outqty_7)+sum(R.orderqty_8)-sum(R.outqty_8)+sum(R.orderqty_9)-sum(R.outqty_9)+sum(R.orderqty_10)-sum(R.outqty_10)+sum(R.orderqty_11)-sum(R.outqty_11)+sum(R.orderqty_12)-sum(R.outqty_12)as block12

from

(

-------1月

select fitemid,orderqty as orderqty_1,outqty as outqty_1,0 orderqty_2 ,0 outqty_2,0

orderqty_3,0 outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6,

0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8,0 orderqty_9,0 outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12

from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=1 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=1 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Jan

union

-------2月

select fitemid,0 orderqty_1,0 outqty_1,orderqty as orderqty_2 ,outqty as outqty_2,0 orderqty_3,0 outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6,

0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8,0 orderqty_9,0 outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12

from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=2 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=2 and ftrantype=21

and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Feb

union

-------3月

select fitemid,0 orderqty_1,0 outqty_1,0 orderqty_2 ,0 outqty_2,orderqty as orderqty_3,outqty as outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0

outqty_6,

0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8,0 orderqty_9,0 outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12 from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=3 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=3 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Mar

union

-------4月

select fitemid,0 orderqty_1,0 outqty_1,0 orderqty_2 ,0 outqty_2,0 orderqty_3,0 outqty_3,orderqty as orderqty_4,outqty as outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6,

0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8,0 orderqty_9,0 outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12

from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=4 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=4 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Apr

union

-------5月

select fitemid,0 orderqty_1,0 outqty_1,0 as orderqty_2 ,0 as outqty_2,0 orderqty_3,0 as outqty_3,0 orderqty_4,0 outqty_4,orderqty as orderqty_5,outqty as outqty_5,0 orderqty_6,0 outqty_6,

0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8,0 orderqty_9,0 outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12

from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=5 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=5 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) May

union

-------6月

select fitemid,0 orderqty_1,0 outqty_1,0 as orderqty_2 ,0 as outqty_2,0 orderqty_3,0 as outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,orderqty as orderqty_6,outqty as outqty_6,

0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8,0 orderqty_9,0 outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12

from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=6 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=6 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Jun

union

-------7月

select fitemid,0 orderqty_1,0 outqty_1,0 as orderqty_2 ,0 as outqty_2,0 orderqty_3,0 as outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6, orderqty as orderqty_7,outqty as outqty_7,0 orderqty_8,0 outqty_8,0 orderqty_9,0 outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12 from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=7 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=7 and ftrantype=21

and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) jul

union

-------8月

select fitemid,0 orderqty_1,0 outqty_1,0 as orderqty_2 ,0 as outqty_2,0 orderqty_3,0 as

outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6, 0 orderqty_7,0 outqty_7,orderqty orderqty_8,outqty outqty_8,0 orderqty_9,0 outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12 from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=8 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=8 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Aug

union

-------9月

select fitemid,0 orderqty_1,0 outqty_1,0 as orderqty_2 ,0 as outqty_2,0 orderqty_3,0 as outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6, 0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8,orderqty orderqty_9,outqty outqty_9,0 orderqty_10,0 outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12 from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=9 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=9 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Aug

union

-------10月

select fitemid,0 orderqty_1,0 outqty_1,0 as orderqty_2 ,0 as outqty_2,0 orderqty_3,0 as outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6, 0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8, 0 orderqty_9, 0 outqty_9,orderqty orderqty_10,outqty outqty_10,0 orderqty_11,0 outqty_11,0 orderqty_12,0 outqty_12 from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=10 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=10 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Oct

union

-------11月

select fitemid,0 orderqty_1,0 outqty_1,0 as orderqty_2 ,0 as outqty_2,0 orderqty_3,0 as outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6, 0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8, 0 orderqty_9, 0 outqty_9,0 orderqty_10,0 outqty_10,orderqty orderqty_11,orderqty outqty_11,0 orderqty_12,0 outqty_12 from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=11 and t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=11 and ftrantype=21 and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) Nov

union

-------12月

select fitemid,0 orderqty_1,0 outqty_1,0 as orderqty_2 ,0 as outqty_2,0 orderqty_3,0 as outqty_3,0 orderqty_4,0 outqty_4,0 orderqty_5,0 outqty_5,0 orderqty_6,0 outqty_6, 0 orderqty_7,0 outqty_7,0 orderqty_8,0 outqty_8, 0 orderqty_9, 0 outqty_9,0 orderqty_10,0

outqty_10,0 orderqty_11,0 outqty_11,orderqty as orderqty_12,outqty as outqty_12

from

(

select A.fitemid,isnull(B.fqty,0) as orderqty,isnull(C.fqty,0) as outqty

from

(select fitemid

from t_icitem

group by fitemid) A left join

(select r.fitemid,sum(t.fqty) as fqty

from seorder s,seorderentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@' and month(s.fdate)=12 and

t.fitemid=r.fitemid

group by r.fitemid)B on A.fitemid=B.fitemid

left join

(select r.fitemid,sum(t.fqty) as fqty

from icstockbill s,icstockbillentry t,t_icitem r

where s.finterid=t.finterid and year(s.fdate)='@year@'and month(s.fdate)=12 and ftrantype=21

and t.fitemid=r.fitemid

group by r.fitemid) C on A.fitemid=C.fitemid

) De

) R

LEFT JOIN

t_icitem s

on R.fitemid=s.fitemid

group by S.FNAME

设备台账模板示例

目 录

#2锅炉 A 中速煤机设备台 . .......................................................................................... 1 #2 B 速磨煤机备台帐 ......................................................................................... 15 #2锅炉 C 速磨煤机设备台帐 ......................................................................................... 29 #2锅炉 D 中磨煤机备台帐 . ........................................................................................ 43 #2锅炉 E 中速磨煤机备台帐 ......................................................................................... 57 #2炉 F 中速磨煤机设备台帐 .......................................................................................... 71 #2锅炉 A 称重式给煤机设备台帐 . ...................................................................................... 84 #2锅 B 称重式给煤机设备台 ....................................................................................... 92 #2锅炉 C 称重式给煤机设备台帐 ..................................................................................... 100 #2锅炉 D 称重式给煤机设备台帐 . .................................................................................... 108 #2锅炉 E 称重式给煤机设备台帐 ..................................................................................... 117 #2锅炉 F 称式给设备台帐 . ..................................................................................... 126 #2锅炉 A 空气预热器备台帐 . ...................................................................................... 135 #2锅炉 B 空气预热器设备台帐 ....................................................................................... 143 #2锅炉 A 密封风机设备台帐 . .......................................................................................... 151 #2炉 B 密封风机设备帐 ........................................................................................... 161 #2锅炉 A 机设备帐 . .............................................................................................. 170 #2锅 B 送风机设备台帐 ............................................................................................... 178 #2锅 A 一次风机设备帐 . .......................................................................................... 186 #2锅炉 B 一次风机备帐 ........................................................................................... 194 #2锅炉 A 引风机设备台 . ............................................................................................... 202 #2锅炉 B 引风机设

#2锅炉 A 中

1

2

3

4

5

6

7

8

9

10

11

12

13

14

#2锅炉 B 中

15

16

17

18

19

20

21

22

23

24

25

26

27

28

#2锅炉 C 中

29

30

31

32

33

34

35

36

37

38

39

40

41

42

#2锅炉 D 中

43

44

45

46

47

48

49

转载请注明出处范文大全网 » 库存台账表格