[Easy to fly] packaging material forcast around rolling demand

"Background" reorganization, warehouse the largest storage capacity is used in packaging materials, especially cartons, trays, foam sheets, packaging tubes, etc., the value is not high, but occupies a larger area.
"Solutions"
1. This kind of material, does not go LRP purchase is not the contract review the purchase order.
2. Replenishment will be replenishment at the inventory replenishment point, replenishment Point inventory, the initial will be set to 3 days average usage. Material control One, three, five regular run replenishment report.
3. The purchase is informed by the quantity of replenishment requisitions. That is, our purchase order is the supplier's purchase note.
Because we have more packaging materials, fluctuations in demand is also relatively large, in order to facilitate suppliers to prepare their raw materials, and suppliers to build a certain safety inventory, this side will and it small gong communication, specifically this kind of material development rolling 1 months of demand report to the procurement, from the procurement to supplier preparation and preparation of finished products.
To compress such material inventory, the key is that suppliers must have a certain stock inventory, our replenishment point is based on the average amount to calculate, do not exclude individual time period, the actual amount will exceed the average dosage more caused emergency feed, please purchase in advance and suppliers to discuss the feasibility of preparation.

--=============================================--Author: <david gong>--CreateDate: < -- A- A>--Modify by:--Description: <forcast forPurchase>--============================================= ALTER PROC [dbo]. [Up_forcastforpur] asDeclare@NEEDAFTER BIT--whether the requirement after the demand date is included in the calculation:1--yes;0--NoDeclare@now as Char(8), @wkdate1 as Char(8), @wkdate2 as Char(8), @wkdate3 as Char(8), @wkdate4 as Char(8)Set@NEEDAFTER =1Set@now =convert (Char(8), GETDATE (), the)Set@wkdate1 =convert (Char(8), DateAdd (Week,1, GETDATE ()), the)Set@wkdate2 =convert (Char(8), DateAdd (Week,2, GETDATE ()), the)Set@wkdate3 =convert (Char(8), DateAdd (Week,3, GETDATE ()), the)Set@wkdate4 =convert (Char(8), DateAdd (Week,4, GETDATE ()), the); withA-Calculate expected demand as(SelectTB003 Product number, ISNULL (SUM (tb004-tb005) *isnull (MD004,1)/isnull (MD003,1),0) asExpected to receive, TA009 asDate of pick-up fromMocta INNERJOINMoctb onta001=tb001 andTa002=tb002 LeftJOINInvmd onmd001=tb003 andmd002=tb007 andmd007=' Y 'WHEREUPPER (TA011) <>' y ' and tb018= ' y '       andLeft (TB003,3)=' 312 '       andTB015 >[email protected] andTB015 < @wkdate4--tb015<= ( Case@NEEDAFTER when 1  Then ' 99991231 ' ELSE @NEEDDATE END)-- andTA009 >[email protected] andTA009 < @wkdate4Group  bytb003,ta009,md004,md003), B--Calculates the safety stock and the on-hand inventory quantity as(SelectMC001, MC004 safety stock, MC007 existing inventory fromInvmcWHEREMc002=' mc001,3 ' and Left (= ' 312 ')), C-Calculation4Weekly estimated lead distribution as(SELECTA. Product number, expected to receive, Case  whenA. Date of use >[email protected] andA. Date < @wkdate1 Then ' first week '          whenA. Date of use >[email protected] andA. Date < @wkdate2 Then ' second week '          whenA. Date of use >[email protected] andA. Date < @wkdate3 Then ' third week '          whenA. Date of use >[email protected] andA. Date < @wkdate4 Then ' fourth week '    Else ' other ' END belongs to week fromA)--Calculation resultsSelectPvt. Product No., MB002 asName, MB003 asSpecifications, MA002 asMain supplier, ISNULL (Pvt. First week,0) asThe first week, ISNULL (Pvt. Second week,0) asThe second week, IsNull (Pvt. Third week,0) asThe third week, IsNull (Pvt. Fourth Week,0) asWeek four, ISNULL (Pvt. First week,0) +isnull (Pvt. Second week,0) +isnull (Pvt. Third week,0) +isnull (Pvt. Fourth Week,0) Total Demand, Case  when(Pvt. First week, IsNull0) +isnull (Pvt. Second week,0) +isnull (Pvt. Third week,0) +isnull (Pvt. Fourth Week,0)-on-hand stock) <=0   Then 0 Else(Pvt. First week, IsNull0) +isnull (Pvt. Second week,0) +isnull (Pvt. Third week,0) +isnull (Pvt. Fourth Week,0)-on-hand stock)END  asLack of material, existing inventory, Case  whenmb034=' R ' then ' press the replenishment point '          whenmb034=' M ' then ' by MRP demand '          whenmb034=' L ' then ' press LRP demand '          whenmb034=' N ' then ' don't need '    Else ' Sales by historical ' END as replenishment policy,@now +' ~ ' [email protected] as date from(SelectProduct number, expected to be used, the week fromC) P PIVOT (SUM (Expected) forOwned weekinch([first week],[second week],[third week],[Fourth week])) asPvt INNERJOINInvmb onPvt. Number =MB001 LeftJOINPurma onmb032=ma001 INNERJOINB onPvt. Product No. =b. Product No.Order  byPvt. Product No.

[Easy to fly] packaging material forcast around rolling demand