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.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
cnn.Execute " BEGIN TRAN HoldClaim"
cnn.Execute "IF NOT EXISTS (SELECT * FROM tblIzmaksa_State_Log WHERE dok_id = " & CStr(dok_id) & ")" & vbCrLf & _
" INSERT INTO tblIzmaksa_State_Log (Dok_ID, State_ID) " & vbCrLf & _
" SELECT Dok_ID, State_ID FROM tblDok_Izmaksas WHERE Dok_ID = " & CStr(dok_id)
cnn.Execute "UPDATE tblDok_Izmaksas SET State_ID = 10 WHERE Dok_ID = " & CStr(dok_id)
cnn.Execute " INSERT INTO tblDok_Pieteikumi " & vbCrLf & _
" (Dok_ID, Obj_ID, Risk_ID, Datums, Piet_Summa, PD_Summa, Valuta_ID, Kurss) " & vbCrLf & _
"SELECT Dok_ID, Obj_ID, Risk_ID, Datums = '" & Format(Datums, "mm-dd-yyyy") & "', Piet_Summa = 0, PD_Summa = 0, Valuta_ID, Kurss " & vbCrLf & _
" FROM fncrPieteikumi('" & Format(Datums, "mm-dd-yyyy") & " 23:59:00') T " & vbCrLf & _
" WHERE dok_id = " + CStr(dok_id) + " "
cnn.Execute "UPDATE tblDok_Izmaksas SET State_ID = 10 WHERE Dok_ID = " & CStr(dok_id)
cnn.Execute "INSERT INTO tblIzmaksa_State_Log (Dok_ID, User_Date, State_ID) " & vbCrLf & _
" VALUES (" & CStr(dok_id) & ", '" & Format(Datums, "mm-dd-yyyy") & "', 10)"
'(S) AX Export...
If Apdr_Veids_ID <> 0 Then
cnn.Execute "INSERT INTO tblExportQueue(Description,StateID) VALUES('Atlîdzîbu pieteikuma eksports " & Lietas_Nr & "', 1)"
rst.Open "SELECT [InsertedID] = @@IDENTITY", cnn, adOpenStatic, adLockReadOnly, adCmdText
intQueue = rst!InsertedID
rst.Close
Query = "SELECT tblDok_Izmaksas.Dok_ID," & vbCrLf & _
" tblDok_Izmaksas.Lietas_Nr, " & vbCrLf & _
" _tblPolisesA.PolicyNumberAX," & vbCrLf & _
" tblDoks.DocAXCode, " & vbCrLf & _
" z_tblUserBase.DefaultAgentID," & vbCrLf & _
" _tblPolisesA.Apdr_Nemejs_ID," & vbCrLf & _
" tblDokIzmSum.Izm_SumVeids_ID, " & vbCrLf & _
" tblDokIzmSum.Izm_Summa," & vbCrLf & _
" tblDok_Izmaksas.State_ID," & vbCrLf & _
" tblDok_Izmaksas.Iesn_Datums," & vbCrLf
Query = Query & " tblRegioni.StructAXCode," & vbCrLf & _
" tblApdr_Veids.ProductAXCode," & vbCrLf & _
" tblApdr_Veids.GroupAXCode, tblRegioni.Id as Filials_Id" & vbCrLf
Query = Query & "FROM tblDok_Izmaksas INNER JOIN" & vbCrLf & _
" tblDoks ON " & vbCrLf & _
" tblDok_Izmaksas.Dok_ID = tblDoks.Dok_ID INNER JOIN" & vbCrLf & _
" tblDok_Pieteikumi ON " & vbCrLf & _
" tblDok_Izmaksas.Dok_ID = tblDok_Pieteikumi.Dok_ID LEFT OUTER JOIN" & vbCrLf & _
" tblDokIzmSum ON" & vbCrLf & _
" tblDokIzmSum.Piet_ID = tblDok_Pieteikumi.ID INNER JOIN" & vbCrLf & _
" sysusers INNER JOIN" & vbCrLf & _
" z_tblUserBase ON " & vbCrLf & _
" sysusers.uid = z_tblUserBase.UID ON " & vbCrLf & _
" tblDok_Izmaksas.Owner_Name = sysusers.name INNER JOIN" & vbCrLf & _
" _tblPolisesA ON " & vbCrLf & _
" tblDok_Izmaksas.Pol_Fil_ID = _tblPolisesA.Filials_ID AND " & vbCrLf & _
" tblDok_Izmaksas.Apdr_Veids_ID = _tblPolisesA.Apdr_Veids_ID AND " & vbCrLf & _
" tblDok_Izmaksas.Pol_Numurs = _tblPolisesA.Numurs INNER JOIN" & vbCrLf & _
" tblRegioni ON" & vbCrLf & _
" _tblPolisesA.Filials_ID = tblRegioni.ID INNER JOIN" & vbCrLf & _
" tblApdr_Veids ON" & vbCrLf & _
" tblApdr_Veids.Apdr_Veids_Real_ID = _tblPolisesA.Apdr_Veids_ID AND " & vbCrLf & _
" tblApdr_Veids.Apdr_Veids_Serija = _tblPolisesA.Serija_N " & vbCrLf & _
"WHERE (tblDok_Izmaksas.Dok_ID = " & dok_id & ") AND (tblDokIzmSum.Izm_SumVeids_ID = 2 OR tblDokIzmSum.Izm_SumVeids_ID IS NULL)" & vbCrLf
Query = Query & "UNION " & vbCrLf & _
"SELECT tblDok_Izmaksas.Dok_ID," & vbCrLf & _
" tblDok_Izmaksas.Lietas_Nr, " & vbCrLf & _
" _tblPolises.PolicyNumberAX," & vbCrLf & _
" tblDoks.DocAXCode, " & vbCrLf & _
" z_tblUserBase.DefaultAgentID," & vbCrLf & _
" _tblPolises.Apdr_Nemejs_ID," & vbCrLf & _
" tblDokIzmSum.Izm_SumVeids_ID, " & vbCrLf & _
" tblDokIzmSum.Izm_Summa," & vbCrLf & _
" tblDok_Izmaksas.State_ID," & vbCrLf & _
" tblDok_Izmaksas.Iesn_Datums," & vbCrLf & _
" tblRegioni.StructAXCode," & vbCrLf & _
" tblApdr_Veids.ProductAXCode," & vbCrLf & _
" tblApdr_Veids.GroupAXCode, tblRegioni.Id as Filials_Id" & vbCrLf
Query = Query & "FROM tblDok_Izmaksas INNER JOIN" & vbCrLf & _
" tblDoks ON " & vbCrLf & _
" tblDok_Izmaksas.Dok_ID = tblDoks.Dok_ID INNER JOIN" & vbCrLf & _
" tblDok_Pieteikumi ON " & vbCrLf & _
" tblDok_Izmaksas.Dok_ID = tblDok_Pieteikumi.Dok_ID LEFT OUTER JOIN" & vbCrLf & _
" tblDokIzmSum ON" & vbCrLf & _
" tblDokIzmSum.Piet_ID = tblDok_Pieteikumi.ID INNER JOIN" & vbCrLf & _
" sysusers INNER JOIN" & vbCrLf & _
" z_tblUserBase ON " & vbCrLf & _
" sysusers.uid = z_tblUserBase.UID ON " & vbCrLf & _
" tblDok_Izmaksas.Owner_Name = sysusers.name INNER JOIN" & vbCrLf & _
" _tblPolises ON " & vbCrLf & _
" tblDok_Izmaksas.Pol_Fil_ID = _tblPolises.Filials_ID AND " & vbCrLf & _
" tblDok_Izmaksas.Apdr_Veids_ID = _tblPolises.Apdr_Veids_ID AND " & vbCrLf & _
" tblDok_Izmaksas.Pol_Numurs = _tblPolises.Numurs INNER JOIN" & vbCrLf & _
" tblRegioni ON" & vbCrLf & _
" _tblPolises.Filials_ID = tblRegioni.ID INNER JOIN" & vbCrLf & _
" tblApdr_Veids ON _tblPolises.Apdr_Veids_ID = tblApdr_Veids.Apdr_Veids_Real_ID INNER JOIN" & vbCrLf & _
" tblSerija ON _tblPolises.Serija_ID = tblSerija.Serija_ID AND tblApdr_Veids.Apdr_Veids_Serija = tblSerija.Serija_Nos" & vbCrLf & _
"WHERE (tblDok_Izmaksas.Dok_ID = " & dok_id & ") AND (tblDokIzmSum.Izm_SumVeids_ID = 2 OR tblDokIzmSum.Izm_SumVeids_ID IS NULL)"
rst.Open Query, cnn, adOpenStatic, adLockReadOnly, adCmdText
branch_Id = rst!Filials_ID
cnn.Execute "INSERT INTO tblExportElement_Claim ([QueueId], [SequenceId], [DokId], [ClaimNr], " & vbCrLf & _
" [ClaimAXNumber], [ReserveTypeAX], " & vbCrLf & _
" [StructureAXCode], [GroupAXCode], [ProductAXCode], " & vbCrLf & _
" [ClaimPaymentTypeId], [EmployeeId], [ClaimDate], " & vbCrLf & _
" [CreditorId], [DebitorId], [OwnRiskSum]," & vbCrLf & _
" [ClaimStateId], [StateId],[IS_ID], [BranchId])" & vbCrLf & _
"VALUES " & vbCrLf & _
"(" & CStr(intQueue) & ",1," & dok_id & ", '" & rst!Lietas_Nr & "', " & IIf(IsNull(rst!DocAXCode), "NULL", " '" & rst!DocAXCode & "'") & ", 'RBNS','" & _
rst!StructAXCode & "', '" & rst!GroupAXCode & "', '" & rst!ProductAXCode & "',3," & rst!DefaultAgentId & ", '" & Format(IIf(AXIntegrationDate > rst!Iesn_Datums, AXIntegrationDate, rst!Iesn_Datums), "mm-dd-yyyy") & "', '" & rst!Apdr_Nemejs_ID & "', '" & rst!Apdr_Nemejs_ID & "', " & vbCrLf & _
IIf(IsNull(rst!Izm_Summa), "NULL", rst!Izm_Summa) & ", " & rst!State_ID & ",1,3, " & CStr(branch_Id) & ")"
