Skip to content
logo
platformaerp.com

skrypty

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
/*Oznaczenie  status FK dlaw szystkei faktur ktore maja fk w tecie*/

update document.invoice set status =2
where invoiceid in(
SELECT invoiceid
  FROM [CRM_METALBUD_2016].[document].invoice  i
   inner join document.documentType typ on typ.documentType = i.invoiceCategory
inner join  TETA_CONS_PROD..TETA_ADMIN.PA_WRD_DOKUMENTY  teta
on i.invoicefullnr2 =teta.symbol collate SQL_Latin1_General_CP1_CI_AS and i.invoiceissuedate = teta.DATA_DOKUMENTU
where  invoiceissuedate >getdate() -360
   and  typ.componentName = 'orderInvoice' --zakup
   and i.status!=2
    and i.status!=0
   and teta.status_dokumentu ='T' --na to wychodzi ze to jest oznaczenie ksiag
   )
SQL
1
2
3
4
5
6
/*Pobranie z tety info o fakturze po symbolu*/

select* from
 TETA_CONS_PROD..TETA_ADMIN.PA_WRD_DOKUMENTY  teta
 where
 symbol ='SU003/10/2022'
SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
/*sprawdzenie roznic w datach miedzy teta platforma*/

update [document].invoice set invoiceissuedate = teta.DATA_DOKUMENTU--,teta.status_dokumentu
  FROM [CRM_METALBUD_2016].[document].invoice  i
    inner join document.documentType typ on typ.documentType = i.invoiceCategory
    inner join firm.firm f on f.firmid = i.issuerid
inner join  TETA_CONS_PROD..TETA_ADMIN.PA_WRD_DOKUMENTY  teta
on i.invoicefullnr2 =teta.symbol collate SQL_Latin1_General_CP1_CI_AS
and f.taxcode = teta.konr_nip collate SQL_Latin1_General_CP1_CI_AS
where  invoiceissuedate >getdate() -360
   and  typ.componentName = 'orderInvoice' --zakup
   and i.status!=2
   and i.status>-1
   and invoiceissuedate <>teta.DATA_DOKUMENTU
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
/*Eksport teta*/

declare @id uniqueidentifier;
DECLARE x_cursor CURSOR
    FOR
        select  i.invoiceid  from document.invoice i
        inner join document.documentType dt on dt.documentType = i.invoiceCategory
        and dt.masterDocumentCategory = 'commercial' and dt.componentName = 'orderInvoice'
        where
        i.status>-1 and  i.status<2 and i.invoiceissuedate >= '2022-10-01'
        --(i.financialsystemexport is null or i.moddate > i.financialsystemexport)

OPEN x_cursor
FETCH NEXT FROM x_cursor  into @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    print @id
    exec  [custom].[teta_zakup_wdr] @invoiceid =@id,@force=1
FETCH NEXT FROM x_cursor  into @id;
END

CLOSE x_cursor;
DEALLOCATE x_cursor;

update document.invoice set status=0
where invoiceid in
(
select invoiceid from document.invoice i
    inner join document.documentType typ on typ.documentType = i.invoiceCategory
where
invoiceissuedate >getdate() -30
 and  typ.componentName = 'orderInvoice' --zakup
and i.invoiceid not in  (select invoiceid from document.invoiceline
)
and i.status>-1
)