API tools faq
paste
Advertisement
hectorhds

Untitled

hectorhds
Aug 10th, 2022
3,763
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 8.14 KB | None | 0 0
raw download clone embed print report
  1. SET DATEFORMAT DMY
  2. DECLARE
  3. @fecha_inicial datetime
  4. ,@fecha_final datetime
  5. ,@almacen VARCHAR(8)
  6. ,@grupo INT
  7. ,@ok INT
  8.  
  9. /*
  10. Select @ok = count(1) from [dbo].[oinv] t99 where
  11. t99.[docdate] >= '[%0]'
  12. and t99.[docdate] <= '[%1]'
  13.  
  14. Select @almacen = t98.[whscode] from [dbo].[owhs] t98 where
  15.  t98.[whsname] =  '[%2]'
  16.  
  17. Select @grupo = t97.[itmsgrpcod] from [dbo].[oitb] t97 where
  18. t97.[itmsgrpnam] =  '[%3]'
  19.  
  20. Set @fecha_inicial = '[%0]'
  21. Set @fecha_final = '[%1]'
  22. */
  23.  
  24. SET @fecha_inicial = '01/01/2022'
  25. SET @fecha_final   = '31/01/2022'
  26. --set @almacen =  '01'
  27. --set @grupo = '101'
  28.  
  29. SET DATEFORMAT DMY
  30. SELECT [Codigo] AS 'Codigo'
  31. ,[Nombre Articulo] AS 'Nombre Articulo'
  32.         ,SUM([Stock]) AS 'Stock'
  33.         ,SUM([Comprometido]) AS 'Comprometido'
  34.         ,SUM([Saldo Inicial]) AS 'Saldo Inicial'
  35.         ,SUM([Facturas]) AS '(-)Facturas'
  36.         ,SUM([Entregas]) AS '(-)Entregas'
  37.         ,SUM([NC\Ventas]) AS '(+)Nota Credito'
  38.         ,SUM([COMPRAS]) AS '(+)Compras'
  39.         ,SUM([ENTRADAS INV]) AS '(+)Entradas INV'
  40.         ,SUM([SALIDAS INV]) AS '(-)Salidas INV'
  41.         ,SUM([Saldo Final]) AS 'Saldo Final'
  42.         ,SUM([Costo]) AS 'Costo'
  43.         ,[Almacen] AS 'Almacen'
  44.         ,[SUCURSAL] AS 'SUCURSAL'
  45. FROM (
  46. SELECT T0.[ItemCode] AS 'Codigo'
  47. ,T0.[ItemName] AS 'Nombre Articulo'
  48.         ,T1.onhand AS 'Stock'
  49.         ,T1.iscommited AS 'Comprometido'
  50.         ,0 AS 'Saldo Inicial'
  51.         ,0 AS 'Facturas'
  52.         ,0 AS 'Entregas'
  53.         ,0 AS 'NC\Ventas'
  54.         ,0 AS 'COMPRAS'
  55.         ,0 AS 'ENTRADAS INV'
  56.         ,0 AS 'SALIDAS INV'
  57.         ,0 AS 'Saldo Final'
  58.         ,T0.avgprice AS 'Costo'
  59. ,t1.WhsCode [Almacen]
  60. ,T2.WhsName [SUCURSAL]
  61. FROM [dbo].[OITM] T0, [dbo].[OITW] T1,[dbo].[OWHS] T2
  62. WHERE
  63. --T1.[whscode] = @almacen
  64.  T0.itemcode = t1.itemcode
  65.  AND t1.whscode=t2.whscode
  66. --and t0.[ItmsGrpCod] =  @grupo
  67. --Saldo Inicial
  68. UNION ALL
  69. SELECT T1.[ItemCode]
  70. ,T1.[ItemName]
  71.         ,0 AS 'Stock'
  72.         ,0 AS 'Comprometido'
  73. ,SUM((T0.[InQty]- T0.[OutQty])) 'Saldo_Inicial'
  74.         ,0 AS 'Facturas'
  75.         ,0 AS 'Entregas'
  76.         ,0 AS 'NC\Ventas'
  77.         ,0 AS 'COMPRAS'
  78.         ,0 AS 'ENTRADAS INV'
  79.         ,0 AS 'SALIDAS INV'
  80. ,0 AS 'Saldo_Final'
  81.         ,0 AS 'Costo'
  82. ,t0.[Warehouse] 'Almacen'
  83. ,T2.WhsName 'SUCURSAL'
  84. FROM [dbo].[OINM] T0  INNER JOIN [dbo].[OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]
  85. INNER JOIN OWHS T2 ON T0.Warehouse = T2.WhsCode
  86. WHERE T0.[DocDate] < @fecha_inicial
  87. --and T0.[Warehouse] = @almacen
  88. --and t1.[ItmsGrpCod] =  @grupo
  89.  
  90. GROUP BY T1.[ItemCode], T1.[ItemName], t0.[Warehouse],T2.[WhsName]
  91. --Saldo final
  92. UNION ALL
  93. SELECT T1.[ItemCode]
  94. ,T1.[ItemName]
  95.         ,0 AS 'Stock'
  96.         ,0 AS 'Comprometido'
  97. ,0 AS 'Saldo_Inicial'
  98.         ,0 AS 'Facturas'
  99.         ,0 AS 'Entregas'
  100.         ,0 AS 'NC\Ventas'
  101.         ,0 AS 'COMPRAS'
  102.         ,0 AS 'ENTRADAS INV'
  103.         ,0 AS 'SALIDAS INV'
  104. ,SUM((T0.[InQty]- T0.[OutQty]))  AS 'Saldo_Final'
  105.         ,0 AS 'Costo'
  106. ,t0.[Warehouse] 'Almacen'
  107. ,T2.WhsName 'SUCURSAL'
  108. FROM [dbo].[OINM] T0  INNER JOIN [dbo].[OITM] T1 ON T0.[ItemCode] = T1.[ItemCode]
  109. INNER JOIN OWHS T2 ON T0.Warehouse = T2.WhsCode
  110. WHERE T0.[DocDate] <= @fecha_final
  111. --and T0.[Warehouse] = @almacen
  112. --and t1.[ItmsGrpCod] =  @grupo
  113. GROUP BY T1.[ItemCode], T1.[ItemName],t0.[Warehouse],T2.[WhsName]
  114.  
  115. --Ventas facturas
  116. UNION ALL
  117. SELECT T1.[ItemCode]
  118. ,T1.[ItemName]
  119.         ,0 AS 'Stock'
  120.         ,0 AS 'Comprometido'
  121. ,0 'Saldo_Inicial'
  122.         ,SUM(T0.[outQty]) AS 'Facturas'
  123.         ,0 AS 'Entregas'
  124.         ,0 AS 'NC\Ventas'
  125.         ,0 AS 'COMPRAS'
  126.         ,0 AS 'ENTRADAS INV'
  127.         ,0 AS 'SALIDAS INV'
  128. ,0 AS 'Saldo_Final'
  129.         ,0 AS 'Costo'
  130. ,t0.[Warehouse] 'Almacen'
  131. ,T2.WhsName 'SUCURSAL'
  132. FROM [dbo].[OINM] T0  INNER JOIN [dbo].[OITM] T1 ON T0.ItemCode = T1.ItemCode
  133. INNER JOIN OWHS T2 ON T0.Warehouse = T2.WhsCode
  134. WHERE T0.[DocDate] >=  @fecha_inicial
  135. AND T0.[DocDate] <= @fecha_final
  136. --and T0.[Warehouse] = @almacen
  137. --and t1.[ItmsGrpCod] =  @grupo
  138. AND T0.transtype IN (13)
  139. GROUP BY T1.[ItemCode], T1.[ItemName],t0.[Warehouse],T2.[WhsName]
  140. -- entregas de mercadería
  141. UNION ALL
  142. SELECT T1.[ItemCode]
  143. ,T1.[ItemName]
  144.         ,0 AS 'Stock'
  145.         ,0 AS 'Comprometido'
  146. ,0 'Saldo_Inicial'
  147.         ,0 AS 'Facturas'
  148.         ,SUM(T0.[outQty]) AS 'Entregas'
  149.         ,0 AS 'NC\Ventas'
  150.         ,0 AS 'COMPRAS'
  151.         ,0 AS 'ENTRADAS INV'
  152.         ,0 AS 'SALIDAS INV'
  153. ,0 AS 'Saldo_Final'
  154.         ,0 AS 'Costo'
  155. ,t0.[Warehouse] 'Almacen'
  156. ,T2.WhsName 'SUCURSAL'
  157. FROM [dbo].[OINM] T0  INNER JOIN [dbo].[OITM] T1 ON T0.ItemCode = T1.ItemCode
  158. INNER JOIN OWHS T2 ON T0.Warehouse = T2.WhsCode
  159. WHERE T0.[DocDate] >=  @fecha_inicial
  160. AND T0.[DocDate] <= @fecha_final
  161. --and T0.[Warehouse] = @almacen
  162. --and t1.[ItmsGrpCod] =  @grupo
  163. AND T0.transtype IN (15)
  164. GROUP BY T1.[ItemCode], T1.[ItemName],t0.[Warehouse],T2.[WhsName]
  165. --Ventas notas de credito
  166. UNION ALL
  167. SELECT T1.[ItemCode]
  168. ,T1.[ItemName]
  169.         ,0 AS 'Stock'
  170.         ,0 AS 'Comprometido'
  171. ,0 'Saldo_Inicial'
  172.         ,0 AS 'Facturas'
  173.         ,0 AS 'Entregas'
  174.         ,SUM(T0.[inQty]) AS 'NC\Ventas'
  175.         ,0 AS 'COMPRAS'
  176.         ,0 AS 'ENTRADAS INV'
  177.         ,0 AS 'SALIDAS INV'
  178. ,0 AS 'Saldo_Final'
  179.         ,0 AS 'Costo'
  180. ,t0.[Warehouse] 'Almacen'
  181. ,T2.WhsName 'SUCURSAL'
  182. FROM [dbo].[OINM] T0  INNER JOIN [dbo].[OITM] T1 ON T0.ItemCode = T1.ItemCode
  183. INNER JOIN OWHS T2 ON T0.Warehouse = T2.WhsCode
  184. WHERE T0.[DocDate] >=  @fecha_inicial
  185. AND T0.[DocDate] <= @fecha_final
  186. --and T0.[Warehouse] = @almacen
  187. --and t1.[ItmsGrpCod] =  @grupo
  188. AND T0.transtype IN (14,16)
  189. GROUP BY T1.[ItemCode], T1.[ItemName],t0.[Warehouse],T2.[WhsName]
  190.  
  191. --compras neto
  192. UNION ALL
  193. SELECT T1.[ItemCode]
  194. ,T1.[ItemName]
  195.         ,0 AS 'Stock'
  196.         ,0 AS 'Comprometido'
  197. ,0 'Saldo_Inicial'
  198.         ,0 AS 'Facturas'
  199.         ,0 AS 'Entregas'
  200.         ,0 AS 'NC\Ventas'
  201.         ,SUM(T0.[inQty])- SUM(T0.[outQty])AS 'COMPRAS'
  202.         ,0 AS 'ENTRADAS INV'
  203.         ,0 AS 'SALIDAS INV'
  204. ,0 AS 'Saldo_Final'
  205.         ,0 AS 'Costo'
  206. ,t0.[Warehouse] 'Almacen'
  207. ,T2.WhsName 'SUCURSAL'
  208. FROM [dbo].[OINM] T0  INNER JOIN [dbo].[OITM] T1 ON T0.ItemCode = T1.ItemCode
  209. INNER JOIN OWHS T2 ON T0.Warehouse = T2.WhsCode
  210. WHERE T0.[DocDate] >=  @fecha_inicial
  211. AND T0.[DocDate] <= @fecha_final
  212. --and T0.[Warehouse] = @almacen
  213. --and t1.[ItmsGrpCod] =  @grupo
  214. AND T0.transtype IN (20,21,18,19)
  215. GROUP BY T1.[ItemCode], T1.[ItemName],t0.[Warehouse],T2.[WhsName]
  216.  
  217. --entradas de inventario
  218. UNION ALL
  219. SELECT T1.[ItemCode]
  220. ,T1.[ItemName]
  221.         ,0 AS 'Stock'
  222.         ,0 AS 'Comprometido'
  223. ,0 'Saldo_Inicial'
  224.         ,0 AS 'Facturas'
  225.         ,0 AS 'Entregas'
  226.         ,0 AS 'NC\Ventas'
  227.         ,0 AS 'COMPRAS'
  228.         ,SUM(T0.[inQty]) AS 'ENTRADAS INV'
  229.         ,0 AS 'SALIDAS INV'
  230. ,0 AS 'Saldo_Final'
  231.         ,0 AS 'Costo'
  232. ,t0.[Warehouse] 'Almacen'
  233. ,T2.WhsName 'SUCURSAL'
  234. FROM [dbo].[OINM] T0  INNER JOIN [dbo].[OITM] T1 ON T0.ItemCode = T1.ItemCode
  235. INNER JOIN OWHS T2 ON T0.Warehouse = T2.WhsCode
  236. WHERE T0.[DocDate] >=  @fecha_inicial
  237. AND T0.[DocDate] <= @fecha_final
  238. --and T0.[Warehouse] = @almacen
  239. --and t1.[ItmsGrpCod] =  @grupo
  240. AND T0.transtype IN (59,67)
  241. GROUP BY T1.[ItemCode], T1.[ItemName],t0.[Warehouse],T2.[WhsName]
  242.  
  243. --salidas de inventario
  244. UNION ALL
  245. SELECT T1.[ItemCode]
  246. ,T1.[ItemName]
  247.         ,0 AS 'Stock'
  248.         ,0 AS 'Comprometido'
  249. ,0 'Saldo_Inicial'
  250.         ,0 AS 'Facturas'
  251.         ,0 AS 'Entregas'
  252.         ,0 AS 'NC\Ventas'
  253.         ,0 AS 'COMPRAS'
  254.         ,0 AS 'ENTRADAS INV'
  255.         ,SUM(T0.[outQty]) AS 'SALIDAS INV'
  256. ,0 AS 'Saldo_Final'
  257.         ,0 AS 'Costo'
  258. ,t0.[Warehouse] 'Almacen'
  259. ,T2.WhsName 'SUCURSAL'
  260. FROM [dbo].[OINM] T0  INNER JOIN [dbo].[OITM] T1 ON T0.ItemCode = T1.ItemCode
  261. INNER JOIN OWHS T2 ON T0.Warehouse = T2.WhsCode
  262. WHERE T0.[DocDate] >=  @fecha_inicial
  263. AND T0.[DocDate] <= @fecha_final
  264. --and T0.[Warehouse] = @almacen
  265. --and t1.[ItmsGrpCod] =  @grupo
  266. AND T0.transtype IN (60,67)
  267. GROUP BY T1.[ItemCode], T1.[ItemName],t0.[Warehouse],T2.[WhsName]
  268. ) TR
  269. WHERE [Stock]     <>0
  270. OR [Comprometido] <> 0
  271. OR [Saldo Inicial] <> 0
  272. OR  [Facturas] <> 0
  273. OR [Entregas] <> 0
  274. OR [NC\Ventas] <> 0
  275. OR [COMPRAS]<> 0
  276. OR [ENTRADAS INV] <> 0
  277. OR [SALIDAS INV] <> 0
  278. OR [Saldo Final] <> 0
  279.  
  280. GROUP BY TR.[Codigo], TR.[Nombre Articulo],tr.[Almacen],TR.[SUCURSAL]
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!