Skip to content
logo
platformaerp.com

Zadania do wykonania

SRS: srs/500

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
SELECT --cl.commisionLineID remoteID,
     cl.commisionLineID,
     cl.commisionID,
     c.commisionfullnr,
     tp.priority,
     tp.worknest_id,
     tp.workcell_id,
     i.itemcode + ' - ' + i.itemname itemcode,
     area.dictionaryvalue areaname,
     operationtype.dictionaryvalue operationname, --cast('commisionline' as nvarchar(50)) as remoteSource,
     cast(isnull(cl.commisionLineQuantity - tc.quantity, cl.commisionLineQuantityLeft) AS int) AS quantity,
     cast(isnull(cl.commisionLineQuantity - tc.quantity, cl.commisionLineQuantityLeft) * isnull(tl.estimatedTime, 0) AS decimal(18, 2)) AS [time], --cast(isnull(cl.commisionLineQuantity - tc.quantity, cl.commisionLineQuantityLeft) * isnull(tl.estimatedTime, 0) * @costPerHour * aread.decimalValue/ 60 as decimal(18,2)) as [value],
     tl.technologyLineID technologyLineID,
     tl.program,
     tl.description,
     tl.status,
     cast(NULL AS uniqueidentifier) AS technologyMadeID,
     cl.commisionLineQuantity AS remoteQuantity,
     cl.itemID AS itemID,
     cl.structureID,
     cl.commisionLineExpectedDate,
     tl.operationTypeID,
     tl.areaID areaID,
     tl.technologyOrder
FROM wms.commisionLine cl
INNER JOIN wms.commision c ON cl.commisionID = c.commisionID
LEFT JOIN wms.item i ON cl.itemID = i.itemID
INNER JOIN wms.technologyLine tl ON cl.structureID = tl.remoteID
AND tl.status > -1
LEFT JOIN common.dictionary area ON area.dictionaryName = 'area'
AND area.dictionaryKeyGuid = tl.areaID
LEFT JOIN common.dictionary operationtype ON operationtype.dictionaryName = 'operationtype'
AND operationtype.dictionaryKeyGuid = tl.operationTypeID
LEFT JOIN
  (SELECT remoteID,
          technologyLineID,
          SUM(isnull(quantity, 0)+ isnull(quantity_failed, 0)) AS quantity
   FROM wms.technologyMade
   GROUP BY remoteID,
            technologyLineID) tc ON cl.commisionLineID = tc.remoteID
AND tl.technologyLineID = tc.technologyLineID --plan na ktore gniazdo

LEFT JOIN wms.technologyplan tp ON cl.commisionLineID =tp.commisionLineID
AND tp.technologyLineID =tl.technologyLineID
WHERE isnull(cl.commisionLineQuantity - tc.quantity, cl.commisionLineQuantityLeft) >0 --and (@areaID is null or tl.areaID = @areaID)

  AND (tc.quantity IS NULL
       OR (tc.quantity)< cl.commisionLineQuantity)
  AND cl.[status] > -1
  and c.commisionfullnr ='d07842/2022'
  AND c.commisionStatusID = '953c9ffd-7dd7-4887-9ed0-443011c026b9' --tylko uruchomione