Skip to content
logo
platformaerp.com

Znajduje się też w rpocedurze kontrolnej nr

[document].[test_balanceCheck] @code='064'

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
;with documentRealizedQuantities as
    (
        select sum((case when dt.componentName = 'documentOut' then -1 else 1 end) * sign * itemQuantity) sumIQ
        , orderLineID from document.documentLine dl
        inner join document.document d on d.documentID = dl.documentID
        inner join document.documenttype dt on dt.documentType = d.documentType and masterDocumentCategory = 'warehouse'
        and dt.realizeOrder = 1
        where dl.[status] = 1 --and dl.itemid ='3b58ebdb-669a-ea11-9546-d89d6719e0cb'
        group by orderLineID
    )
    select
      (ol.itemQuantity - drq.sumIQ ) total,
        ol.orderLineStatus,
        o.orderFullNR,
        O.ORDERID,
        ol.orderLineID,
        ol.orderOrdinalNumber,
        o.orderDate,
        drq.sumIQ DOKUMENTY_IL,
        ol.itemQuantity ZAMOWIENIE_IL,
        ol.itemQuantityLeft ZAMOWIENIE_il_P ,
        'update [document].[orderline] set itemQuantityLeft = ' + cast(  (ol.itemQuantity - drq.sumIQ ) as varchar(12)) +' where orderlineid='''+
        cast(ol.orderLineID as varchar(36)) + '''' SQL
    from documentRealizedQuantities drq
    left join document.orderLine ol on ol.orderLineID = drq.orderLineID
    left join document.[order] o on o.orderID = ol.orderID
    where
    drq.sumIQ >  (ol.itemQuantity - ol.itemQuantityLeft)
    and ol.orderLineStatus = 3 --realizacja automatyczna
    and o.orderdate > @date