[Easy to fly] statistics on the dynamic tracking of inventory and sales volume for internal stock orders

A demand
Sales Total:
About the annual meeting mentioned, I hope that sales attention to stock order quantity dynamic maintenance problem, and it communication, we can provide the following data in OA, for your reference, to determine whether it is necessary to adjust the quantity of stock orders, but also please suggest that you want to reflect the information to facilitate you to determine whether to do the preparation adjustment.
Customer number of product name or specifications of the total number of materials in the last 2 months of sales of the latest production time company inventory VMI inventory in the system quantity
Hefei XXX 123 xzm-16187 10000 5000 2015.12.01 10000 10000 3000

Data content:
1. Customer, product number, products name, the total quantity of all material preparation orders, the last time to stock order.
2. The customer's shipment quantity in the last 2 months. (VMI customer by sales reconciliation quantity)-Customer sales quantity time period, 1 months, 2 months or 3 months? Please sell to confirm how long is more appropriate, more close to the customer's recent needs.
3. Stock order products in the company and VMI warehouse real-Time inventory quantity, in-transit production work order quantity.

The above information, hope that you assess the quantity of the material is reasonable to help. Please respond to these two days so that it can start programming.

Other:
Once you have confirmed that you want to reduce or cancel the quantity of the stock order, if these stock orders are our internal application for sale, the sales assistant will cancel or reduce the first time after giving notice. At the same time let the material control confirmation in transit related materials, production planning, inventory and so on, can adjust the timely adjustment, resulting in inventory of that also no way, do a stock report prepared, after autumn again calculate, this can timely all inventory, containing material inventory released, for other orders to use, reduce the risk of sluggish, but also cut production capacity squeeze.
If the customer under the stock order, any quantity changes, must strictly according to the company's order change process to go.

Available inventory functions:

-- =============================================      --Author: <david gong>--Create Date: <>--Description: < available stock >-- ============================================= ALTERfunction [dbo]. [Getcurrentinvqty] (@ITEM as VARCHAR( -)) returnsdecimal( the,2) as beginDeclare@qtydecimal( the,2),--Fixed warehouse existing inventory @qty1 as decimal( the,2),--non-audited sales document quantity @qty2 as decimal( the,2),--Non-approved picking list quantity @mb025 as Char(1)--Product number attributeSet@qty1 =0Set@qty2 =0Select@MB025 =mb025 fromInvmbWHERE[Email protected]Select@qty =sum(MC007) fromInvmcwhere[Email protected] andMC002inch(' a ',' a ',' the ',' ",' on ')Group  byMC001Select@qty1 =isnull (sum(TH008),0) fromCopth with(NOLOCK)INNER JOINCoptg onth001=tg001 andtg002=th002where[Email protected] andtg023=' N '--if(@MB025 =' P ')--The quantity of the non-approved picking list is included in the purchased item--BEGINSelect@qty2 =isnull (sum(TE005),0) fromMocte with(NOLOCK)Inner JoinMoctc onte001=tc001 andte002=tc002where[Email protected] andtc009=' N '--ENDreturn @[email protected] @qty2End

WIP Quantity

-- =============================================      --Author: <david gong>--Create Date: <2016-01-22>--Description: <wip qty>-- ============================================= ALTERfunction [dbo]. [Wipqty] (@Item as VARCHAR( -)) returnsdecimal( -,4) as beginDeclare@qty as decimal( -,4)SELECT@qty =SUM(ta015-ta017) fromMoctaWHEREta013=' Y '  andTA011inch(' 2 ',' 3 ') and[Email Protected]return @qtyEnd

Number of VMI warehouses

–=============================================
–author:
–create Date: <2016-01-22>
–description:
–=============================================
ALTER function [dbo]. [Vmiqty] (
@CustomerNo as nvarchar (20),
@Item as VARCHAR (20)
) returns decimal (18,4)
As
Begin

Declare @qty as decimal (18,4)

Select @qty =sum (invmc. MC007) from Invmc inner joins CMSMC on INVMC. MC002=CMSMC. MC001
where [email protected] and [email protected]
GROUP by INVMC. MC001

Return @qty

End
Recent Sales volume function:

-- =============================================      --Author: <david gong>--Create Date: <2016-01-22>--Description: < designated customer product number Recent sales >-- ============================================= ALTERfunction [dbo]. [Recentlydaysaleqty] (@CustomerNo asnvarcharTen), @Item as VARCHAR( -),@ Day  as int) returnsdecimal( -,4) as beginDeclare@qty as decimal( -,4)SELECT@qty =sum(TH008) fromCoptgINNER JOINCopth ontg001=th001 andtg002=th002WHEREtg023=' Y '  and[Email protected] and[Email protected] andDATEDIFF ( Day, Tg003,getdate ()) <[email protected] Day  andDATEDIFF ( Day, Tg003,getdate ()) >=0Return @qtyEnd

Date of recent material preparation

-- =============================================      --Author: <david gong>--Create Date: <2016-01-22>--Description: < Last preparation time >-- ============================================= ALTERfunction [dbo]. [Recentlybldate] (@CustomerNo asnvarchar -), @Item as VARCHAR( -)) returns nvarchar (Ten) as beginDeclare@TC003 asnvarcharTen)Select@TC003 =tc003 from(SELECTTOP1TC003 fromCoptcINNER JOINCoptd ontc001=td001 andtc002=td002WHEREtc027=' Y '  and[Email protected] and[Email protected]Order  byTC003DESC) asAreturn @TC003End

Sql:

With stock order as (SELECT TC004 customer code, MA002 customer abbreviation, TD004 product number, MB002 name, MB003 specification, SUM (td008-td009) stock quantity from COPTC with (NOLOCK) INNER JOIN coptd on tc001=td001 and tc002=td002Left join INVMB on td004=mb001Left join COPMA on tc004=ma001WHERE tc027= ' Y ' and tc001= ' 2207 ' and (td008-td009)<>0GROUP by tc004,td004,mb002,mb003,ma002) SELECT customer code, customer abbreviation, product number, name, specification, quantity of stock, dbo. [Recentlydaysaleqty](Customer code, product number,As the last February sales, [dbo]. [recentlybldate](Customer code, product numberAs the date of the most recent material preparation, dbo. Getcurrentinvqty (product number) as available stock, [dbo]. [Wipqty](Product No.As in the number of systems, [dbo]. [Vmiqty](Customer code, product numberAs VMI quantity from preparation order

9 Seconds of Operation Speed:

The available inventory overhead takes up 79%.
If the preparation is more, the running speed will be reduced quickly

The available inventory is improved into a view, with tables associated with tables, reducing the number of full scans available for inventory.

Create View[dbo]. [Availableqty] as withA as(SelectMC001 asItem,sum(MC007) Invqty fromInvmc with(NOLOCK)whereMC002inch(' a ',' a ',' the ',' ",' on ')Group  byMC001), B as(SelectTH004 asItem,isnull (sum(TH008),0) Shippedqty fromCopth with(NOLOCK)INNER JOINCoptg onth001=tg001 andtg002=th002wheretg023=' N 'GROUP  byTH004), C as(SelectTE004 asItem,sum(IsNull (TE005,0)) Requisitionqty fromMocte with(NOLOCK)Inner JoinMoctc onte001=tc001 andte002=tc002wheretc009=' N 'Group  byTE004)Select* from(SelectA.item,a.invqty-isnull (B.shippedqty,0)-isnull (C.requisitionqty,0) asAvailableqty fromA Left JOINB onA.item=b.item Left JOINC onA.item=c.item) asDWHERED.availableqty<>0GO

Improved post-sql:

With stock order as (SELECT TC004 customer code, MA002 customer abbreviation, TD004 product number, MB002 name, MB003 specification, SUM (td008-td009) stock quantity from COPTC with (NOLOCK) INNER J OIN coptd on tc001=td001 and tc002=td002Left join INVMB on td004=mb001Left join COPMA on tc004=ma001WHERE tc027= ' Y ' and tc001= ' 2207 ' and (td008-td009)<>0GROUP by tc004,td004,mb002,mb003,ma002) SELECT customer code, customer abbreviation, product number, name, specification, quantity of stock, dbo. [Recentlydaysaleqty](Customer code, product number,As the last February sales, [dbo]. [recentlybldate](Customer code, product numberAs the date of the most recent material preparation, Availableqty available stock, [dbo]. [Wipqty](Product No.As in the number of systems, [dbo]. [Vmiqty](Customer code, product number) as VMI quantity, B. Region from stock order left join Availableqty on product number =itemLeft join COPMA on customer code =ma001The left join (select Mr002,ltrim (MR003) as region from CMSMR where mr001= ' 2 ') as B is on COPMA. ma076=b.mr002

Speed down to 1 seconds to complete:

In the actual development process, if the function involves a multi-table full scan, the data volume is large, the resource overhead will occupy relatively large, if can be changed to get the result set at once, the last association will improve the efficiency of operation.

[Easy to fly] statistics on the dynamic tracking of inventory and sales volume for internal stock orders