Skip to content
logo
platformaerp.com

Helper scripts

Ważne skrypty

  • [document].[documentOUT_conditionalyDelete] - Usuwanie zatwierdzonego dokumentu rozchodowego - jest zabezpieczony na to, żeby nie usunąć korygowaneg

Pomocne skrypty SQL

Podstawowe złaczenie - dokumenty magazynowe

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
    SELECT
        top 100
        w.locationCode warehouseCode,
        l.locationCode,
        l.locationName,
        d.documentDate,
        i.itemCode itemCode,
        i.itemName itemName,
        dl.itemQuantity *dl.sign  quantity,
        d.documentFullNR
    FROM
        document.document d
        INNER JOIN document.documentLine dl ON dl.documentID = d.documentID
        inner join document.documenttype dt on dt.documentType = d.documentType
        INNER JOIN wms.item i ON i.itemID = dl.itemID
        inner join wms.location w on w.locationID  = d.warehouseID
        left join wms.location l on l.locationID  = dl.locationid
    WHERE
        d.status=1 and dl.status=1
        --and w.locationCode  ='1300' --magazyn
        and dt.baseDocumentType = 'wmm'     --pz,pzi
        --and cast(d.documentDate as date) >= '2023-11-01'
        --and cast(d.documentDate as date) <= '2023-11-30'
    ORDER BY documentDate ,   dl.signvalue * -1  ,d.documentNR
  • Sortowanie

Opcja nr 1: ORDER BY documentDate , dl.signvalue * -1 ,d.documentNR Wady - nie posortuje dobrze dokumentow WM- i WM+ w tym samym dniu

Opcja nr 2: order by d.documentDate ,d.fulltextid (chyba najlepsza opcja na razie)

Ostatnia data i numer dokumentu wydania

  • update: 20240605
SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
select * from (
    select
        row_number() OVER(PARTITION BY i.itemid ORDER BY d.documentdate DESC) row,
        dl.itemid,
      d.documentdate,
        d.documentFullNR
    from
        document.document d
        inner join document.documenttype dt on dt.documentType = d.documentType
      inner join document.documentline dl on d.documentid=dl.documentid
        inner join wms.item i on dl.itemid = i.itemid
    where
        dl.status >=0
      and d.status>=0
        and dt.basedocumenttype in ('rw','wz','RWCO','rwp') --wydania bez korekt,wm,mm itp
      --and d.warehouseid =@warehouseID
) x where row=1

Pierwsza cena

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
--pierwsza cena
select itemid,sysItemPrice from (
select
    row_number() OVER(PARTITION BY i.itemid ORDER BY d.documentdate DESC) row,
            i.itemID,
            dl.sysItemPrice
        from document.document d
            inner join document.documenttype dt on dt.documentType = d.documentType
            inner join document.documentline dl on d.documentid=dl.documentid
            inner join wms.item i on dl.itemid = i.itemid
        where
            dl.status >=0
            and d.status>=0
            and dt.baseDocumentType = 'pz' --ex. tylko pz, pzi
            and d.documentDate >=@date
) x where row=1) pierwszacena  on datasummary.itemID = pierwszacena.itemid