test---092223-CSDN博客

本文链接：https://blog.csdn.net/sinat_32849897/article/details/50385586
<code class="hljs vbnet has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">-- =============================================   
-- Author: <David Gong>   
-- Create <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">date</span>: <<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2015</span>-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">12</span>-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">22</span>>  
-- Modify <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span>: 
-- Description: <forcast <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">for</span> purchase>   
-- ============================================= 

ALTER PROC [dbo].[UP_ForcastForPur]
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">declare</span> @NEEDAFTER BIT              --需求日期后的需求是否纳入计算:<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>->是;<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>->否
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">declare</span> @now <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>), @wkdate1 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>), @wkdate2 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>), @wkdate3 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>), @wkdate4 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>)

<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> @NEEDAFTER=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> @now=convert(<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>),getdate(),<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">112</span>)
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> @wkdate1=convert(<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>),dateadd(week,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>,getdate()),<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">112</span>)
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> @wkdate2=convert(<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>),dateadd(week,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>,getdate()),<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">112</span>)
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> @wkdate3=convert(<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>),dateadd(week,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>,getdate()),<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">112</span>) 
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> @wkdate4=convert(<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">8</span>),dateadd(week,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">4</span>,getdate()),<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">112</span>)
;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">with</span> A --计算预计领用需求
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span>(
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> TB003 品号,ISNULL(SUM(TB004-TB005)*ISNULL(MD004,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>)/ISNULL(MD003,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>),<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> 预计领用,TA009 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> 领用日期   
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> MOCTA INNER <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">JOIN</span> MOCTB <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> TA001=TB001 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> TA002=TB002 
     LEFT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">JOIN</span> INVMD <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> MD001=TB003 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> MD002=TB007 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> MD007=<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'Y'</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span>  UPPER(TA011)<><span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'Y'  AND TB018='Y'</span>
      <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> LEFT(TB003,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>)=<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'312' </span>
      <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> TB015 >=@now <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> TB015 <@wkdate4  --TB015<=(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CASE</span> @NEEDAFTER <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">THEN</span> <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'99991231' ELSE @NEEDDATE END)</span>
      --<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> TA009 >=@now <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> TA009 <@wkdate4   
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">group</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> TB003,TA009,MD004,MD003
),
B   --计算安全库存量与现有库存量
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span>(
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> MC001 品号,MC004 安全库存,MC007 现有库存 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> INVMC <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> MC002=<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'01' and  left(MC001,3)='312'</span>
)
, C --计算<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">4</span>周预计领用分布
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span>(
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> A.品号,预计领用,
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">case</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span> A.领用日期 >=@now <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> A.领用日期 <@wkdate1 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">then</span> <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'第一周'</span>
         <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span> A.领用日期 >=@wkdate1 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> A.领用日期 <@wkdate2 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">then</span> <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'第二周'</span>
         <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span> A.领用日期 >=@wkdate2 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> A.领用日期 <@wkdate3 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">then</span> <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'第三周'</span>
         <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span> A.领用日期 >=@wkdate3 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AND</span> A.领用日期 <@wkdate4 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">then</span> <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'第四周'</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">else</span> <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'其他' END 所属周</span>

<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> A 

)
--计算结果
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> pvt.品号,MB002 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AS</span> 品名,MB003 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AS</span> 规格,MA002 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">AS</span> 主供应商,
isnull(pvt.第一周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> 第一周,
isnull(pvt.第二周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> 第二周,
isnull(pvt.第三周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> 第三周,
isnull(pvt.第四周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> 第四周,
isnull(pvt.第一周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第二周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第三周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第四周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>) 总需求,
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">case</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">when</span> (isnull(pvt.第一周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第二周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第三周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第四周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)-现有库存)<=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">then</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">else</span> (isnull(pvt.第一周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第二周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第三周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)+isnull(pvt.第四周,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>)-现有库存) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> 缺料情况,现有库存,
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CASE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> MB034=<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'R' then '按补货点' </span>
         <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> MB034=<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'M' then '按MRP需求' </span>
         <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> MB034=<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'L' then '按LRP需求' </span>
         <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> MB034=<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'N' then '不需'</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">else</span> <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'按历史销售' END as 补货政策,</span>
    @now+<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">'~'+@wkdate4 as 日期</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> ( <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> 品号,预计领用,所属周 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> C) p PIVOT(SUM(预计领用)  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOR</span> 所属周 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">in</span>([第一周],[第二周],[第三周],[第四周])) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> pvt
      INNER <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">JOIN</span> INVMB <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> pvt.品号=MB001
      LEFT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">JOIN</span> PURMA <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> MB032=MA001
      INNER <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">JOIN</span> B <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> pvt.品号=B.品号

<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">order</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> pvt.品号</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"></ul>