API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 12th, 2019
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.61 KB | None | 0 0
raw download clone embed print report
  1. USE [CNHREMAN_App]
  2. GO
  3. /****** Object: StoredProcedure [dbo].[CNH_MaterialsRequirementReport] Script Date: 7/11/2019 4:07:17 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9. ALTER PROCEDURE [dbo].[CNH_MaterialsRequirementReport]
  10.  
  11. as
  12.  
  13. Begin
  14.  
  15. If object_ID ('CNH_MaterialsRequirementReportTable') is not null
  16. BEGIN
  17. drop table CNH_MaterialsRequirementReportTable
  18. END
  19. Create Table CNH_MaterialsRequirementReportTable
  20. (
  21. [CNHR Part] varchar (30),
  22. [Vendor Part#] varchar (30),
  23. [Description] varchar (300),
  24. [PC] int,
  25. [PC Description] varchar (300),
  26. [FPT] varchar (10),
  27. [Top 80% $] varchar (10),
  28. [Top 100 $] varchar (10),
  29. [CNH LT] int,
  30. [On Hand] int,
  31. [Months OH] decimal,
  32. [3_Mo Use] int,
  33. [Avg_Exp_Qty] int,
  34. [Sfty Stck] decimal,
  35. [Months Sfty_Stck] decimal,
  36. [On Order] int,
  37. [PO Nxt Due] datetime,
  38. [R_Part_Equiv] varchar (20),
  39. [R OnHand] int,
  40. [AvgReqs] decimal,
  41. [Std] numeric,
  42. [2019 07] int,
  43. [2019 08] int,
  44. [2019 09] int,
  45. [2019 10] int,
  46. [rc.2019 07] int,
  47. [rc.2019 08] int,
  48. [rc.2019 09] int,
  49. [rc.2019 10] int,
  50. [rc.2019 11] int,
  51. [rc.2019 12] int,
  52. [rc.2020 01] int,
  53. [Tot_Reqs] int,
  54. [Ext Reqs] numeric,
  55. [% Ext $] varchar(20),
  56. [pc.2019 07] int,
  57. [pc.2019 08] int,
  58. [pc.2019 09] int,
  59. [pc.2019 10] int,
  60. [pc.2019 11] int,
  61. [pc.2019 12] int,
  62. [pc.2020 01] int,
  63. [Tot_Open] int,
  64. [Ext_Open] numeric,
  65. [PO_LT MIN] int,
  66. [PO_LT MAX] int,
  67. [#_PO 12_Mos] int,
  68. [LTL Cnt] int,
  69. [Dem_LT MIN] int,
  70. [Dem_LT MAX] int,
  71. [Dem_LT AVG] int,
  72. [3_Mo Job] int,
  73. [3_Mo Exp] int,
  74. [Tot_Exp_$$] numeric,
  75. [pu.Avg_Exp_Qty] numeric,
  76. [Avg_Exp_$$] numeric,
  77. )
  78.  
  79. Declare @EndDate date = DATEADD(DAY, -1, GETDATE())
  80. Declare @StartDate date = DATEADD(Month, -3, GETDATE()-1)
  81. DECLARE @CutOffDate as Date = cast(DATEADD(year, -1, GETDATE()) AS DATE);
  82.  
  83. With
  84. ReqRowsCTE
  85. AS (
  86. SELECT wu.[Component_Part]
  87. , iv.vend_item
  88. , iv.lead_time
  89. , iv.vend_num
  90. , CIM.matl_cost
  91. , concat(DATEPART(Year,MR.Due_Date), '-', REPLACE(STR(Datepart(month, MR.due_date),2), ' ', '0') ) as Period
  92.  
  93. ,Round(sum(wu.Net_qty * MR.rcpt_qty),0) as Qty_Req_int
  94.  
  95. FROM rcpts_mst as MR
  96. JOIN [CNHREMAN_App].[dbo].[CNH_WhereUsed] wu on MR.item = wu.[Finished_Good]
  97. JOIN [CNHREMAN_App].[dbo].[item_mst] cim on cim.item = wu.component_part
  98. JOIN [CNHREMAN_App].[dbo].[itemvend_mst] iv on iv.item = cim.item and iv.rank = 1
  99.  
  100. WHERE cim.p_m_t_code = 'P' and iv.vend_num in (' 100350',' 100351',' 100356',' 100357')
  101. GROUP BY Component_Part, iv.vend_item, CIM.matl_cost, cim.description, iv.lead_time, iv.vend_num, DATEPART(Year,MR.Due_Date), DATEPART(Month,MR.Due_Date)
  102.  
  103.  
  104. ),
  105. ReqColsCTE
  106. AS (
  107. Select * From ReqRowsCTE
  108. Pivot
  109. (
  110. Sum(Qty_Req_int)
  111. For Period in
  112. (
  113. [2019-07]
  114. ,[2019-08]
  115. ,[2019-09]
  116. ,[2019-10]
  117. ,[2019-11]
  118. ,[2019-12]
  119. ,[2020-01]
  120. )
  121. ) as PivotTable
  122. --Group by Component_part, Vend_item, lead_time, Vend_num
  123.  
  124. ),
  125. PORowsCTE
  126. AS (
  127. SELECT poi.Item
  128.  
  129. , Sum(poi.qty_ordered - poi.qty_received) as Open_Qty
  130.  
  131. , concat(DATEPART(Year,poi.Due_Date), '-', REPLACE(STR(Datepart(month, poi.due_date),2), ' ', '0') ) as Period_Due
  132.  
  133.  
  134. FROM poitem_mst as POI
  135. join po_mst as POH
  136. on poi.po_num = poh.po_num and poh.whse = 'Main'
  137. and poh.vend_num in (' 100350',' 100351',' 100356',' 100357')
  138. and poh.po_num not like 'RD%'
  139.  
  140. WHERE poi.item like 'N%'
  141. and poi.qty_ordered > poi.qty_received
  142.  
  143. GROUP BY poi.item, DATEPART(Year,poi.Due_Date), DATEPART(Month,poi.Due_Date)
  144.  
  145. ),
  146. POColsCTE
  147. AS (
  148. Select * From PORowsCTE
  149. Pivot
  150. (
  151. sum(Open_qty)
  152. For Period_Due in
  153. (
  154. --[2019-04]
  155. [2019-05]
  156. ,[2019-06]
  157. ,[2019-07]
  158. ,[2019-08]
  159. ,[2019-09]
  160. ,[2019-10]
  161. ,[2019-11]
  162. ,[2019-12]
  163. ,[2020-01]
  164. )
  165. ) as PivotTable
  166. --Group by Component_part, Vend_item, lead_time, Vend_num
  167.  
  168. ),
  169. ReceiptSummCTE
  170. as
  171. ( SELECT
  172. POD.po_num AS PO_Num
  173. , POD.po_line AS PO_Line
  174. , count(RC.po_num) as Receipts
  175. , min(RC.rcvd_date) AS [1stRcvdDate]
  176. , max(RC.rcvd_date) AS [LstRcvdDate]
  177. , Sum(RC.qty_received) AS [Rcvd.Qty]
  178.  
  179. FROM
  180. poitem_mst POD
  181. INNER JOIN
  182. po_mst POM ON POD.site_ref = POM.site_ref AND POD.po_num = POM.po_num and POM.whse = 'Main'
  183. Inner Join
  184. vendaddr_mst VN on POM.vend_num = VN.vend_num
  185. LEFT OUTER JOIN
  186. po_rcpt_mst RC ON POD.site_ref = RC.site_ref AND POD.po_num = RC.po_num AND POD.po_line = RC.po_line
  187.  
  188. WHERE
  189. POD.item LIKE 'N%' AND (POM.order_date > @CutOffDate) AND (DATEDIFF(day, POM.order_date, POD.due_date) >= 0)
  190. Group By
  191. POD.po_num, POD.po_line
  192. ),
  193.  
  194. PODemLTLCTE
  195. AS (
  196.  
  197. SELECT
  198. POD.item
  199. ,Count(POD.po_num) as LTLCnt
  200. ,cast(Sum(POD.qty_ordered) as Int) as LTLTotQty
  201. ,Cast(Sum(POD.qty_received) as Int) as LTLTotRcvd
  202. ,Avg(DATEDIFF(day, POM.order_date, RC.[1stRcvdDate])) as LTLDaysAvg
  203. ,min(DATEDIFF(day, POM.order_date, RC.[1stRcvdDate])) as LTLDaysMin
  204. ,max(DATEDIFF(day, POM.order_date, RC.[1stRcvdDate])) as LTLDaysMax
  205.  
  206. FROM
  207. poitem_mst as POD
  208. INNER JOIN
  209. po_mst POM ON POD.site_ref = POM.site_ref
  210. AND POD.po_num = POM.po_num
  211. and pom.vend_num In (' 100350',' 100351',' 100356',' 100357')
  212. and POM.whse = 'Main'
  213. LEFT OUTER JOIN
  214. ReceiptSummCTE RC ON POD.po_num = RC.po_num AND POD.po_line = RC.po_line
  215. INNER JOIN
  216. item_mst IM ON POD.site_ref = IM.site_ref AND POD.item = IM.item
  217. WHERE
  218. POD.item LIKE 'N%'
  219. AND POM.order_date > @CutOffDate
  220. AND DATEDIFF(day, POM.order_date, POD.due_date) >= 0
  221. and POD.qty_ordered > 0
  222. and POD.qty_received > 0
  223. --Check If NOT OT1 and Less than Lead Time
  224. AND ((DATEDIFF(day, POM.order_date, POD.due_date) > 2)
  225. and ((DATEDIFF(day, POM.order_date, POD.promise_date) > 2) or POD.promise_date is null)
  226. and (
  227. ((POD.Promise_Date IS NOT NULL) and (DATEDIFF(day, POM.order_date, POD.Promise_Date) < IM.lead_time))
  228. or ((POD.Promise_Date IS NULL) and (DATEDIFF(day, POM.order_date, POD.Due_Date) < IM.lead_time))
  229. ))
  230. Group by POD.item
  231.  
  232. ),
  233.  
  234. POSumCTE
  235. AS (
  236.  
  237. SELECT
  238. POD.item
  239. ,Count(POD.po_num) as TotCnt
  240. ,Cast(Sum(POD.qty_ordered)as Int) as TotQty
  241. ,Cast(Sum(POD.qty_received) as INT) as TotRcvd
  242. ,Sum(Cast((POD.qty_ordered - POD.qty_received) as Int)) as TotOnOrder
  243. ,Avg(DATEDIFF(day, POM.order_date, RC.[1stRcvdDate])) as TotDaysAvg
  244. ,min(DATEDIFF(day, POM.order_date, RC.[1stRcvdDate])) as TotDaysMin
  245. ,max(DATEDIFF(day, POM.order_date, RC.[1stRcvdDate])) as TotDaysMax
  246.  
  247.  
  248.  
  249. FROM
  250. poitem_mst POD
  251. INNER JOIN
  252. po_mst POM ON POD.site_ref = POM.site_ref
  253. AND POD.po_num = POM.po_num
  254. and pom.vend_num In (' 100350',' 100351',' 100356',' 100357')
  255. and POM.whse = 'Main'
  256. LEFT OUTER JOIN
  257. ReceiptSummCTE RC ON POD.po_num = RC.po_num AND POD.po_line = RC.po_line
  258. INNER JOIN
  259. item_mst IM ON POD.site_ref = IM.site_ref AND POD.item = IM.item
  260. WHERE
  261. POD.item LIKE 'N%'
  262. AND POM.order_date > @CutOffDate
  263. AND DATEDIFF(day, POM.order_date, POD.due_date) >= 0
  264. and POD.qty_ordered > 0
  265. Group by POD.item
  266.  
  267. ),
  268.  
  269. PONextCTE
  270. AS (
  271.  
  272. SELECT
  273. POD.item
  274. ,Count(POD.po_num) as TotCnt
  275. ,Cast(Min(POD.due_date) as Date) as Next_Due
  276. ,cast(Min(DATEDIFF(day, POM.order_date, POD.due_date)) as Int) as MIN_PO_LT
  277. ,cast(Max(DATEDIFF(day, POM.order_date, POD.due_date)) as Int) as MAX_PO_LT
  278.  
  279. FROM
  280. poitem_mst POD
  281. INNER JOIN
  282. po_mst POM ON POD.site_ref = POM.site_ref and POM.whse = 'Main'
  283. AND POD.po_num = POM.po_num
  284. and pom.vend_num In (' 100350',' 100351',' 100356',' 100357')
  285.  
  286. WHERE
  287. POD.item LIKE 'N%'
  288. AND POM.order_date > @CutOffDate
  289. AND DATEDIFF(day, POM.order_date, POD.due_date) >= 0
  290. and POD.qty_ordered > 0
  291. --and POD.qty_received = 0
  292. and POD.qty_received < POD.qty_ordered
  293.  
  294. Group by POD.item
  295.  
  296. ),
  297.  
  298. PartUseCTE
  299.  
  300. AS (
  301. Select
  302.  
  303. MT.item
  304. , Sum(MT.qty * -1) AS [3_Mo_Use]
  305. , Sum(Case
  306. When mt.trans_type = 'I'
  307. Then mt.qty * -1
  308. Else 0
  309. End) as [3_Mo_Iss]
  310. , Sum(Case
  311. When mt.trans_type = 'G'
  312. Then mt.qty * -1
  313. Else 0
  314. End) as [3_Mo_Exp]
  315.  
  316. , Cast(Avg
  317. (Case
  318. When mt.trans_type = 'G'
  319. Then mt.qty * -1
  320. Else 0
  321. End)
  322. as INT) as [Avg_Exp_Qty]
  323.  
  324.  
  325. From matltran_mst MT
  326.  
  327. Where
  328. MT.item like 'N%'
  329.  
  330. and mt.trans_date >= @StartDate
  331. and mt.trans_date <= @EndDate
  332. and ((mt.trans_type = 'G' and reason_code = 'LST')
  333. or mt.trans_type = 'I')
  334.  
  335. Group By MT.Item
  336. )
  337. Insert into CNH_MaterialsRequirementReportTable
  338. Select
  339. RC.Component_Part as [CNHR Part]
  340. ,RC.vend_item as [Vendor Part#]
  341. , IM.description as Description
  342. , IM.product_code as PC
  343. , pcm.description as [PC Description]
  344. , Coalesce(left(FPT.[Vendor Name], 3), '') as FPT
  345. , '' as [Top 80% $]
  346. , '' as [Top 100 $]
  347. , rc.lead_time as [CNH LT]
  348.  
  349. , Cast(IW.qty_on_hand as INT) as [On Hand]
  350. ,CASE
  351. When ((ISNULL(RC.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0) + ISNULL(rc.[2019-11],0) + ISNULL(rc.[2019-12],0) + ISNULL(rc.[2020-01],0)) / 7) > 0
  352. Then Round((IW.qty_on_hand /
  353. (ISNULL(RC.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0) + ISNULL(rc.[2019-11],0) + ISNULL(rc.[2019-12],0) + ISNULL(rc.[2020-01],0)) / 7), 1)
  354. Else 0
  355. End as [Months OH]
  356.  
  357. , Coalesce(Cast(pu.[3_Mo_Use] as INT), 0) as [3_Mo Use]
  358.  
  359. , Coalesce(Cast(pu.[Avg_Exp_Qty] as INT), 0) as [Avg_Exp_Qty]
  360.  
  361.  
  362. , Cast(IW.qty_reorder as INT) as [Sfty Stck]
  363. ,CASE
  364. When ((ISNULL(RC.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0) + ISNULL(rc.[2019-11],0) + ISNULL(rc.[2019-12],0) + ISNULL(rc.[2020-01],0)) / 7) > 0
  365. Then Round((IW.qty_reorder /
  366. ((ISNULL(RC.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0) + ISNULL(rc.[2019-11],0) + ISNULL(rc.[2019-12],0) + ISNULL(rc.[2020-01],0)) / 7)), 1)
  367. Else 0
  368. End as [Months Sfty_Stck]
  369. ,iw.qty_ordered as [On Order]
  370. , Coalesce(LEFT(CONVERT(VARCHAR, PN.Next_Due, 120), 10), '') as [PO Nxt Due]
  371.  
  372. , Coalesce(concat('R',right(im.item,len(im.item)-1)),'') as [R_Part_Equiv]
  373. , Coalesce(Cast(riw.qty_on_hand as INT), 0) as [R OnHand]
  374.  
  375. , Cast((( + ISNULL(RC.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0) + ISNULL(rc.[2019-11],0) + ISNULL(rc.[2019-12],0) + ISNULL(rc.[2020-01],0)) / 7) as decimal(9,2)) as AvgReqs
  376. --, iw.matl_cost as [Matl Cost]
  377. , iw.unit_cost as Std
  378.  
  379. ,(Cast(IW.qty_on_hand as INT)+ ISNULL(PC.[2019-05],0) + ISNULL(pc.[2019-06],0) + ISNULL(pc.[2019-07],0))
  380. - (ISNULL(rc.[2019-07],0))
  381. as [2019 07]
  382. ,(Cast(IW.qty_on_hand as INT)+ ISNULL(PC.[2019-05],0) + ISNULL(pc.[2019-06],0) + ISNULL(pc.[2019-07],0) + ISNULL(pc.[2019-08],0))
  383. - (ISNULL(rc.[2019-07],0) + ISNULL(rc.[2019-08],0))
  384. as [2019 08]
  385. ,(Cast(IW.qty_on_hand as INT)+ ISNULL(PC.[2019-05],0) + ISNULL(pc.[2019-06],0) + ISNULL(pc.[2019-07],0) + ISNULL(pc.[2019-08],0) + ISNULL(pc.[2019-09],0))
  386. - (ISNULL(rc.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0))
  387. as [2019 09]
  388. ,(Cast(IW.qty_on_hand as INT)+ ISNULL(PC.[2019-05],0) + ISNULL(pc.[2019-06],0) + ISNULL(pc.[2019-07],0) + ISNULL(pc.[2019-08],0) + ISNULL(pc.[2019-09],0) + ISNULL(pc.[2019-10],0))
  389. - (ISNULL(rc.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0))
  390. as [2019 10]
  391.  
  392.  
  393. ,Coalesce(RC.[2019-07], '0') as [2019 07]
  394. ,Coalesce(rc.[2019-08], '0') as [2019 08]
  395. ,Coalesce(rc.[2019-09], '0') as [2019 09]
  396. ,Coalesce(rc.[2019-10], '0') as [2019 10]
  397. ,Coalesce(rc.[2019-11], '0') as [2019 11]
  398. ,Coalesce(rc.[2019-12], '0') as [2019 12]
  399. ,Coalesce(RC.[2020-01], '0') as [2020 01]
  400. ,ISNULL(RC.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0) + ISNULL(rc.[2019-11],0) + ISNULL(rc.[2019-12],0) + ISNULL(rc.[2020-01],0) as Tot_Reqs
  401. ,(ISNULL(RC.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0) + ISNULL(rc.[2019-11],0) + ISNULL(rc.[2019-12],0) + ISNULL(rc.[2020-01],0)) * iw.matl_cost as [Ext Reqs]
  402. , '' as [% Ext $]
  403.  
  404. ,ISNULL(PC.[2019-07],0) as [2019 07]
  405. ,ISNULL(PC.[2019-08],0) as [2019 08]
  406. ,ISNULL(PC.[2019-09],0) as [2019 09]
  407. ,ISNULL(PC.[2019-10],0) as [2019 10]
  408. ,ISNULL(PC.[2019-11],0) as [2019 11]
  409. ,ISNULL(PC.[2019-12],0) as [2019 12]
  410. ,ISNULL(PC.[2020-01],0) as [2020 01]
  411.  
  412. ,ISNULL(PC.[2019-07],0) + ISNULL(PC.[2019-08],0) + ISNULL(PC.[2019-09],0) + ISNULL(PC.[2019-10],0) + ISNULL(PC.[2019-11],0) + ISNULL(PC.[2019-12],0) + ISNULL(PC.[2020-01],0) as Tot_Open
  413. ,(ISNULL(PC.[2019-07],0) + ISNULL(PC.[2019-08],0) + ISNULL(PC.[2019-09],0) + ISNULL(PC.[2019-10],0) + ISNULL(PC.[2019-11],0) + ISNULL(PC.[2019-12],0) + ISNULL(PC.[2020-01],0)) * IW.matl_cost as Ext_Open
  414.  
  415.  
  416. , Coalesce(PN.MIN_PO_LT,'') as [PO_LT MIN]
  417. , Coalesce(PN.MAX_PO_LT,'') as [PO_LT MAX]
  418.  
  419. --, Coalesce(Cast(POS.TotOnOrder as INT),'0') as OnOrderPO
  420. , Coalesce(Cast(POS.TotCnt as INT), '0') as [#_PO 12_Mos]
  421. , Coalesce(pol.LTLCnt, '') as [LTL Cnt]
  422. , Coalesce(pol.LTLDaysMin, '') as [Dem_LT MIN]
  423. , Coalesce(pol.LTLDaysMax, '') as [Dem_LT MAX]
  424. , Coalesce(pol.LTLDaysAvg, '') as [Dem_LT AVG]
  425.  
  426. , Coalesce(Cast(pu.[3_Mo_iss] as INT), 0) as [3_Mo Job]
  427. , Coalesce(Cast(pu.[3_Mo_exp] as INT), 0) as [3_Mo Exp]
  428. , Coalesce(pu.[3_Mo_exp] * iw.unit_cost,0) as Tot_Exp_$$
  429. , Coalesce(pu.Avg_Exp_Qty, 0) as [Avg_Exp_Qty]
  430. , Coalesce(pu.Avg_Exp_Qty * iw.unit_cost, 0) as Avg_Exp_$$
  431.  
  432.  
  433. from ReqColsCTE RC
  434. Left Join item_mst as IM on RC.Component_Part = IM.item
  435. Left Join itemwhse_mst as IW on IM.item = IW.item and IW.whse = 'Main'
  436. Left Join POColsCTE PC on IM.item = PC.item
  437. Left Join PONextCTE PN on IM.item = PN.item
  438. Left join PODemLTLCTE as POL on IM.item = POL.item
  439. Left Join POSumCTE as POS on IM.item = POS.item
  440. Left Join CNH_FPT_Parts as FPT on IM.Item = FPT.Item
  441. Left Join PartUseCTE as pu on IM.Item = pu.Item
  442. Left join item_mst rim on rim.item = concat('R',right(im.item,len(im.item)-1))
  443. Left join itemwhse_mst riw on riw.item = concat('R',right(im.item,len(im.item)-1)) and riw.whse = 'Main'
  444. Left join prodcode_mst pcm on im.product_code = pcm.product_code
  445. Where
  446.  
  447. (ISNULL(RC.[2019-07],0) + ISNULL(rc.[2019-08],0) + ISNULL(rc.[2019-09],0) + ISNULL(rc.[2019-10],0) + ISNULL(rc.[2019-11],0) + ISNULL(rc.[2019-12],0) + ISNULL(rc.[2020-01],0)) > 1
  448.  
  449. Order by RC.Component_Part
  450.  
  451. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!