0ei90GWf

1-- пока только за 2018 год
2DECLARE @FiscalYear INT = 2018;
3DECLARE @glaccountFrom INT = 8751; -- 7101010100
4DECLARE @glaccountTo_1 INT = 8754; -- 7101020100
5DECLARE @glaccountTo_2 INT = 7944; -- 5001000000
6DROP TABLE IF EXISTS #doc_link; -- для каждой исходной строки документа
7DROP TABLE IF EXISTS #main;
8-- выбираем номер документа связанной заявки 
9SELECT dr.CodeNum intRefDocCode
10    , dc.DocumentNumberInt intSrcDocCode
11    , AccountingDocumentSegmentID
12    , dc.CompanyID
13    , dc.FiscalYear
14INTO #doc_link
15FROM sap.AccountingDocument dc
16    INNER JOIN sap.AccountingDocumentSegment seg
17        ON dc.DocumentNumberInt = seg.DocumentNumberInt
18            AND dc.FiscalYear = seg.FiscalYear
19            AND dc.CompanyID = seg.CompanyID
20    INNER JOIN sap.DocumentReference dr
21        ON dr.DocumentReferenceID = dc.DocumentReferenceID
22WHERE dc.FiscalYear = @FiscalYear
23    AND GLAccountID IN ( @glaccountTo_1, @glaccountTo_2 );
24DELETE FROM #doc_link
25WHERE ISNULL(intRefDocCode, 0) = 0;
26
27-- оставляем только заявки с номером на '85*'
28DELETE FROM #doc_link
29WHERE NOT LEFT(CAST(intRefDocCode AS NVARCHAR(50)), 2) = N'85';
30CREATE CLUSTERED INDEX idxdx1
31    ON #doc_link (AccountingDocumentSegmentID);
32CREATE INDEX idxix2
33    ON #doc_link (intRefDocCode, CompanyID);
34DROP TABLE IF EXISTS #doc_src;
35
36-- вытаскиваем требуемые данные из документа - заявки
37-- где указан держатель внебюджета
38SELECT dl.intRefDocCode
39    , dl.AccountingDocumentSegmentID toADSegID
40    , dl.CompanyID
41    , seg.ExtraBudgetaryHolderId
42    , seg.GLAccountID
43    , seg.MainGLAccountID
44INTO #doc_src
45FROM sap.AccountingDocument dc
46    INNER JOIN sap.AccountingDocumentSegment seg
47        ON dc.DocumentNumberInt = seg.DocumentNumberInt
48            AND dc.FiscalYear = seg.FiscalYear
49            AND dc.CompanyID = seg.CompanyID
50    INNER JOIN #doc_link dl
51        ON dl.CompanyID = dc.CompanyID
52            AND dl.intRefDocCode = dc.DocumentNumberInt
53WHERE ExtraBudgetaryHolderId != 0;
54
55
56DROP TABLE IF EXISTS #tbl_res;
57SELECT seg.*, ds.ExtraBudgetaryHolderId ebhid, seg.AccountingDocumentSegmentID accDoccSegId
58INTO #tbl_res
59FROM sap.AccountingDocumentSegment seg
60    INNER JOIN #doc_src ds
61        ON seg.AccountingDocumentSegmentID = ds.toADSegID
62WHERE ds.GLAccountID = @glaccountFrom;
63
64-- выборка для обогащения
65SELECT t.ebhid AS ExtraBudgetaryHolderId
66	 , t.accDoccSegId AS AccountingDocumentSegmentID
67INTO #main
68FROM #tbl_res t
69    INNER JOIN sap.GLAccount g
70        ON t.GLAccountID = g.GLAccountID
71    INNER JOIN sap.Company c
72        ON c.CompanyID = t.CompanyID
73
74------------------
75
76
77DROP TABLE IF EXISTS #__gl5x;
78SELECT GLAccountID
79INTO #__gl5x
80FROM sap.GLAccount
81WHERE Code LIKE '51%'
82    OR Code LIKE '52%'
83    OR Code LIKE '57%';
84DROP TABLE IF EXISTS #__srcSeg;
85DROP TABLE IF EXISTS #__doc_link;
86
87-- выбираем документы и строки с движениями по заданным счетам
88SELECT dc.DocumentNumberInt intSrcDocCode
89    , AccountingDocumentSegmentID
90    , dc.CompanyID
91    , dc.FiscalYear
92INTO #__doc_link
93FROM sap.AccountingDocument dc
94    INNER JOIN sap.AccountingDocumentSegment seg
95        ON dc.DocumentNumberInt = seg.DocumentNumberInt
96            AND dc.FiscalYear = seg.FiscalYear
97            AND dc.CompanyID = seg.CompanyID
98    INNER JOIN #__gl5x g5
99        ON g5.GLAccountID = seg.GLAccountID
100WHERE dc.FiscalYear = @fiscalYear;
101DROP TABLE IF EXISTS #__srcBdjOpPos1;
102
103-- выбираем держателей внебюджета из полученных документов
104SELECT seg.AccountingDocumentSegmentID
105    , seg.AccDocPostingDate
106    , d.intSrcDocCode
107    , seg.GLAccountID
108    , seg.ExtraBudgetaryHolderId
109    , d.CompanyID
110    , seg.SummaDocumentCurrency --,seg.OperPosition 
111INTO #__srcBdjOpPos1
112FROM #__doc_link d
113    INNER JOIN sap.AccountingDocumentSegment seg
114        ON seg.DocumentNumberInt = d.intSrcDocCode
115WHERE ExtraBudgetaryHolderId != 0
116    AND OperPosition = 1
117    AND seg.FiscalYear = 2018
118
119DROP TABLE IF EXISTS #__destUpd;
120
121-- фильтруем строки исходных документов по заданным счетам
122SELECT seg.FiscalYear
123    , seg.DocumentNumberInt
124    , seg.CompanyID
125    , seg.GLAccountID
126    , seg.SelfBalanceGLAccountID
127    , p.GLAccountID pgl
128    , seg.SummaDocumentCurrency
129    , p.SummaDocumentCurrency psm
130    , seg.TransactionText
131    , seg.OperPosition
132    , p.ExtraBudgetaryHolderId
133    , p.AccountingDocumentSegmentID psid
134    , seg.AccountingDocumentSegmentID ssid
135INTO #__destUpd
136FROM sap.AccountingDocumentSegment seg
137    INNER JOIN #__srcBdjOpPos1 p
138        ON p.intSrcDocCode = seg.DocumentNumberInt
139            AND FiscalYear = 2018
140            AND seg.CompanyID = p.CompanyID
141            AND seg.SelfBalanceGLAccountID = p.GLAccountID
142            AND p.AccDocPostingDate = seg.AccDocPostingDate
143    INNER JOIN #__gl5x g
144        ON g.GLAccountID = seg.GLAccountID
145            AND seg.ExtraBudgetaryHolderId = 0
146            AND OperPosition > 1;
147
148-- выборка для обновления
149INSERT INTO #main (ExtraBudgetaryHolderId, AccountingDocumentSegmentID)
150	SELECT d.ExtraBudgetaryHolderId, d.psid
151	FROM #__destUpd d
152		INNER JOIN sap.Company c
153			ON c.CompanyID = d.CompanyID
154		INNER JOIN sap.GLAccount g
155			ON g.GLAccountID = d.GLAccountID
156	WHERE NOT EXISTS (
157		SELECT m.AccountingDocumentSegmentID 
158		FROM #main m
159		WHERE d.psid = m.AccountingDocumentSegmentID
160		)
161;
162
163
164-- обновляем AccountingDocumentSegment чанками по 10к строк дабы не убить лог
165WHILE 1 = 1
166BEGIN
167    UPDATE TOP (10000) adc
168    SET ExtraBudgetaryHolderId = m.ExtraBudgetaryHolderId
169    FROM sap.AccountingDocumentSegment adc
170        JOIN #main AS m ON adc.AccountingDocumentSegmentID = m.AccountingDocumentSegmentID
171    WHERE adc.ExtraBudgetaryHolderId = 0
172    ;
173   
174    IF @@ROWCOUNT = 0
175        BREAK;
176END
177;