· 7 years ago · Oct 16, 2018, 03:39 PM
1/*
2Deployment script for BankPortalDataBase
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16:setvar DatabaseName "BankPortalDataBase"
17:setvar DefaultFilePrefix "BankPortalDataBase"
18:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL13.NAVDEMO\MSSQL\DATA\"
19:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL13.NAVDEMO\MSSQL\DATA\"
20
21GO
22:on error exit
23GO
24/*
25Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
26To re-enable the script after enabling SQLCMD mode, execute the following:
27SET NOEXEC OFF;
28*/
29:setvar __IsSqlCmdEnabled "True"
30GO
31IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
32 BEGIN
33 PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
34 SET NOEXEC ON;
35 END
36
37
38GO
39USE [master];
40
41
42GO
43
44IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
45BEGIN
46 ALTER DATABASE [$(DatabaseName)]
47 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
48 DROP DATABASE [$(DatabaseName)];
49END
50
51GO
52PRINT N'Creating $(DatabaseName)...'
53GO
54CREATE DATABASE [$(DatabaseName)]
55 ON
56 PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
57 LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
58GO
59USE [$(DatabaseName)];
60
61
62GO
63IF EXISTS (SELECT 1
64 FROM [master].[dbo].[sysdatabases]
65 WHERE [name] = N'$(DatabaseName)')
66 BEGIN
67 ALTER DATABASE [$(DatabaseName)]
68 SET ANSI_NULLS ON,
69 ANSI_PADDING ON,
70 ANSI_WARNINGS ON,
71 ARITHABORT ON,
72 CONCAT_NULL_YIELDS_NULL ON,
73 NUMERIC_ROUNDABORT OFF,
74 QUOTED_IDENTIFIER ON,
75 ANSI_NULL_DEFAULT ON,
76 CURSOR_DEFAULT LOCAL,
77 RECOVERY FULL,
78 CURSOR_CLOSE_ON_COMMIT OFF,
79 AUTO_CREATE_STATISTICS ON,
80 AUTO_SHRINK OFF,
81 AUTO_UPDATE_STATISTICS ON,
82 RECURSIVE_TRIGGERS OFF
83 WITH ROLLBACK IMMEDIATE;
84 ALTER DATABASE [$(DatabaseName)]
85 SET AUTO_CLOSE OFF
86 WITH ROLLBACK IMMEDIATE;
87 END
88
89
90GO
91IF EXISTS (SELECT 1
92 FROM [master].[dbo].[sysdatabases]
93 WHERE [name] = N'$(DatabaseName)')
94 BEGIN
95 ALTER DATABASE [$(DatabaseName)]
96 SET ALLOW_SNAPSHOT_ISOLATION OFF;
97 END
98
99
100GO
101IF EXISTS (SELECT 1
102 FROM [master].[dbo].[sysdatabases]
103 WHERE [name] = N'$(DatabaseName)')
104 BEGIN
105 ALTER DATABASE [$(DatabaseName)]
106 SET READ_COMMITTED_SNAPSHOT OFF
107 WITH ROLLBACK IMMEDIATE;
108 END
109
110
111GO
112IF EXISTS (SELECT 1
113 FROM [master].[dbo].[sysdatabases]
114 WHERE [name] = N'$(DatabaseName)')
115 BEGIN
116 ALTER DATABASE [$(DatabaseName)]
117 SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
118 PAGE_VERIFY NONE,
119 DATE_CORRELATION_OPTIMIZATION OFF,
120 DISABLE_BROKER,
121 PARAMETERIZATION SIMPLE,
122 SUPPLEMENTAL_LOGGING OFF
123 WITH ROLLBACK IMMEDIATE;
124 END
125
126
127GO
128IF IS_SRVROLEMEMBER(N'sysadmin') = 1
129 BEGIN
130 IF EXISTS (SELECT 1
131 FROM [master].[dbo].[sysdatabases]
132 WHERE [name] = N'$(DatabaseName)')
133 BEGIN
134 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
135 SET TRUSTWORTHY OFF,
136 DB_CHAINING OFF
137 WITH ROLLBACK IMMEDIATE';
138 END
139 END
140ELSE
141 BEGIN
142 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
143 END
144
145
146GO
147IF IS_SRVROLEMEMBER(N'sysadmin') = 1
148 BEGIN
149 IF EXISTS (SELECT 1
150 FROM [master].[dbo].[sysdatabases]
151 WHERE [name] = N'$(DatabaseName)')
152 BEGIN
153 EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
154 SET HONOR_BROKER_PRIORITY OFF
155 WITH ROLLBACK IMMEDIATE';
156 END
157 END
158ELSE
159 BEGIN
160 PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
161 END
162
163
164GO
165ALTER DATABASE [$(DatabaseName)]
166 SET TARGET_RECOVERY_TIME = 0 SECONDS
167 WITH ROLLBACK IMMEDIATE;
168
169
170GO
171IF EXISTS (SELECT 1
172 FROM [master].[dbo].[sysdatabases]
173 WHERE [name] = N'$(DatabaseName)')
174 BEGIN
175 ALTER DATABASE [$(DatabaseName)]
176 SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
177 CONTAINMENT = NONE
178 WITH ROLLBACK IMMEDIATE;
179 END
180
181
182GO
183IF EXISTS (SELECT 1
184 FROM [master].[dbo].[sysdatabases]
185 WHERE [name] = N'$(DatabaseName)')
186 BEGIN
187 ALTER DATABASE [$(DatabaseName)]
188 SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
189 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
190 DELAYED_DURABILITY = DISABLED
191 WITH ROLLBACK IMMEDIATE;
192 END
193
194
195GO
196IF EXISTS (SELECT 1
197 FROM [master].[dbo].[sysdatabases]
198 WHERE [name] = N'$(DatabaseName)')
199 BEGIN
200 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
201 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
202 ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
203 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
204 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
205 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
206 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
207 ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
208 END
209
210
211GO
212IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
213 EXECUTE sp_fulltext_database 'enable';
214
215
216GO
217PRINT N'Creating [dbo].[BankAccount]...';
218
219
220GO
221CREATE TABLE [dbo].[BankAccount] (
222 [Id] INT IDENTITY (1, 1) NOT NULL,
223 [OwnerEnterprise] INT NOT NULL,
224 [RegisterNumber] VARCHAR (100) NOT NULL,
225 [AccountName] VARCHAR (100) NOT NULL,
226 [AccountNumber] VARCHAR (100) NOT NULL,
227 [BankName] VARCHAR (100) NOT NULL,
228 [Currency] INT NULL,
229 [BranchOffice] VARCHAR (100) NULL,
230 [CountableAccount] VARCHAR (100) NULL,
231 [CuttingDay] INT NULL,
232 [NextCheck] INT NULL,
233 [IsActive] INT NOT NULL,
234 [OpeningDate] DATETIME NOT NULL,
235 [BeginningBalance] NUMERIC (18, 6) NULL,
236 [IsForeign] INT NOT NULL,
237 [Telephone] VARCHAR (50) NULL,
238 [Telephone2] VARCHAR (50) NULL,
239 [CreatedDate] DATETIME NULL,
240 [LastUpdate] DATETIME NOT NULL,
241 [LastUpdateBy] VARCHAR (100) NULL,
242 CONSTRAINT [PK_REGISTER_NUMBER_OWNER_ENTERPRISE] PRIMARY KEY CLUSTERED ([RegisterNumber] ASC, [OwnerEnterprise] ASC)
243);
244
245
246GO
247PRINT N'Creating [dbo].[BankMovementDetail]...';
248
249
250GO
251CREATE TABLE [dbo].[BankMovementDetail] (
252 [NUM_REGP] INT NOT NULL,
253 [NUM_MOV] INT NOT NULL,
254 [CVE_CONCEP] VARCHAR (6) NULL,
255 [REFERENCIA] VARCHAR (20) NULL,
256 [MONTO_IVA] FLOAT (53) NULL,
257 [FACT] VARCHAR (20) NULL,
258 [MONTO_DOC] FLOAT (53) NOT NULL,
259 [MONTO_EXT] FLOAT (53) NULL,
260 [ORDEN] INT NOT NULL,
261 [X_OBSER] VARCHAR (255) NULL,
262 [NUMCARGO] INT NULL,
263 [NUMCPTOPADRE] INT NULL,
264 [TIPOCAMBIOSAE] FLOAT (53) NULL,
265 [TIPOCAMBIOBANCO] FLOAT (53) NULL,
266 [DOCTO] VARCHAR (20) NULL,
267 [NO_PARTIDASAE] INT NULL,
268 [MONEDADOC] INT NULL,
269 [PART_CLPV] VARCHAR (10) NULL,
270 [PART_CLPV_RFC] VARCHAR (13) NULL,
271 [PART_ANOMBREDE] VARCHAR (120) NULL,
272 [IVA_MC] FLOAT (53) NULL,
273 [PART_CTA_CONTAB_ASOC] VARCHAR (40) NULL,
274 [PART_CONCEPSAE] INT NULL,
275 [PART_ANTICIPO] INT NULL,
276 [PART_CTA_BANCO_ASOC] VARCHAR (50) NULL,
277 [PART_CVE_BANCO_ASOC] VARCHAR (6) NULL,
278 [EnterpriseOwnerId] INT NOT NULL,
279 [AccountNumber] VARCHAR (100) NOT NULL,
280 [CreatedDate] DATETIME NULL,
281 [LastUpdate] DATETIME NULL,
282 [LastUpdateBy] VARCHAR (100) NULL,
283 PRIMARY KEY CLUSTERED ([NUM_REGP] ASC, [NUM_MOV] ASC, [EnterpriseOwnerId] ASC, [AccountNumber] ASC)
284);
285
286
287GO
288PRINT N'Creating [dbo].[BankMovementHeader]...';
289
290
291GO
292CREATE TABLE [dbo].[BankMovementHeader] (
293 [NUM_REG] INT NOT NULL,
294 [CVE_CONCEP] VARCHAR (6) NOT NULL,
295 [CON_PART] INT NULL,
296 [NUM_CHEQUE] INT NULL,
297 [REF1] VARCHAR (20) NULL,
298 [REF2] VARCHAR (20) NULL,
299 [STATUS] VARCHAR (1) NOT NULL,
300 [FECHA] DATETIME NOT NULL,
301 [F_COBRO] DATETIME NOT NULL,
302 [BAND_PRN] VARCHAR (1) NULL,
303 [BAND_CONT] VARCHAR (1) NULL,
304 [ACT_SAE] VARCHAR (1) NULL,
305 [NUM_POL] VARCHAR (5) NULL,
306 [TIP_POL] VARCHAR (2) NULL,
307 [SAE_COI] INT NULL,
308 [MONTO_TOT] FLOAT (53) NOT NULL,
309 [MONTO_IVA_TOT] FLOAT (53) NULL,
310 [MONTO_EXT] FLOAT (53) NULL,
311 [MONEDA] INT NULL,
312 [T_CAMBIO] FLOAT (53) NULL,
313 [HORA] INT NULL,
314 [CLPV] VARCHAR (10) NULL,
315 [CTA_TRANSF] INT NULL,
316 [FECHA_LIQ] DATETIME NULL,
317 [FECHA_POL] DATETIME NULL,
318 [CVE_INST] INT NULL,
319 [MONDIFSAE] VARCHAR (1) NULL,
320 [TCAMBIOSAE] FLOAT (53) NULL,
321 [RFC] VARCHAR (20) NULL,
322 [CONC_SAE] INT NULL,
323 [SOLICIT] VARCHAR (50) NULL,
324 [TRANS_COI] INT NULL,
325 [INTSAENOI] INT NULL,
326 [X_OBSER] VARCHAR (255) NULL,
327 [FACTOR] INT NOT NULL,
328 [FORMAPAGO] INT NOT NULL,
329 [ANOMBREDE] VARCHAR (120) NULL,
330 [ASOCIADO] VARCHAR (1) NOT NULL,
331 [CTA_CONTAB_ASOC] VARCHAR (40) NULL,
332 [REVISADO] INT NULL,
333 [PRIORIDAD] VARCHAR (6) NULL,
334 [DOC_ASOC] INT NULL,
335 [RESALTAR] INT NULL,
336 [ANTICIPO] INT NULL,
337 [IDTRANSF] INT NULL,
338 [SUCURSAL] VARCHAR (30) NULL,
339 [CUENTA] VARCHAR (30) NULL,
340 [CLABE] VARCHAR (30) NULL,
341 [MULTI_CLPV] INT NULL,
342 [CVE_BANCO_ASOC] VARCHAR (6) NULL,
343 [NUM_CONC_AUTO] INT NULL,
344 [COI_DEPTO] INT NULL,
345 [COI_CCOSTOS] INT NULL,
346 [COI_PROYECTO] INT NULL,
347 [EnterpriseOwnerId] INT NOT NULL,
348 [AccountNumber] VARCHAR (100) NOT NULL,
349 [IsPostedOnSae] INT NULL,
350 [DateTimeOfPostingOnSae] DATETIME NULL,
351 [CreatedDate] DATETIME NULL,
352 [LastUpdate] DATETIME NULL,
353 [LastUpdateBy] VARCHAR (100) NULL,
354 PRIMARY KEY CLUSTERED ([NUM_REG] ASC, [EnterpriseOwnerId] ASC, [AccountNumber] ASC)
355);
356
357
358GO
359PRINT N'Creating [dbo].[Customer]...';
360
361
362GO
363CREATE TABLE [dbo].[Customer] (
364 [Id] INT IDENTITY (1, 1) NOT NULL,
365 [CLAVE] VARCHAR (10) NOT NULL,
366 [STATUS] VARCHAR (1) NOT NULL,
367 [NOMBRE] VARCHAR (120) NULL,
368 [RFC] VARCHAR (15) NULL,
369 [CALLE] VARCHAR (80) NULL,
370 [NUMINT] VARCHAR (15) NULL,
371 [NUMEXT] VARCHAR (15) NULL,
372 [CRUZAMIENTOS] VARCHAR (40) NULL,
373 [CRUZAMIENTOS2] VARCHAR (40) NULL,
374 [COLONIA] VARCHAR (50) NULL,
375 [CODIGO] VARCHAR (5) NULL,
376 [LOCALIDAD] VARCHAR (50) NULL,
377 [MUNICIPIO] VARCHAR (50) NULL,
378 [ESTADO] VARCHAR (50) NULL,
379 [PAIS] VARCHAR (50) NULL,
380 [NACIONALIDAD] VARCHAR (40) NULL,
381 [REFERDIR] VARCHAR (255) NULL,
382 [TELEFONO] VARCHAR (25) NULL,
383 [CLASIFIC] VARCHAR (5) NULL,
384 [FAX] VARCHAR (25) NULL,
385 [PAG_WEB] VARCHAR (60) NULL,
386 [CURP] VARCHAR (18) NULL,
387 [CVE_ZONA] VARCHAR (6) NULL,
388 [IMPRIR] VARCHAR (1) NULL,
389 [MAIL] VARCHAR (1) NULL,
390 [NIVELSEC] INT NULL,
391 [ENVIOSILEN] VARCHAR (1) NULL,
392 [EMAILPRED] VARCHAR (60) NULL,
393 [DIAREV] VARCHAR (2) NULL,
394 [DIAPAGO] VARCHAR (2) NULL,
395 [CON_CREDITO] VARCHAR (1) NULL,
396 [DIASCRED] INT NULL,
397 [LIMCRED] FLOAT (53) NULL,
398 [SALDO] FLOAT (53) NULL,
399 [LISTA_PREC] INT NULL,
400 [CVE_BITA] INT NULL,
401 [ULT_PAGOD] VARCHAR (20) NULL,
402 [ULT_PAGOM] FLOAT (53) NULL,
403 [ULT_PAGOF] DATETIME NULL,
404 [DESCUENTO] FLOAT (53) NULL,
405 [ULT_VENTAD] VARCHAR (20) NULL,
406 [ULT_COMPM] FLOAT (53) NULL,
407 [FCH_ULTCOM] DATETIME NULL,
408 [VENTAS] FLOAT (53) NULL,
409 [CVE_VEND] VARCHAR (5) NULL,
410 [CVE_OBS] INT NULL,
411 [TIPO_EMPRESA] VARCHAR (1) NULL,
412 [MATRIZ] VARCHAR (10) NULL,
413 [PROSPECTO] VARCHAR (1) NULL,
414 [CALLE_ENVIO] VARCHAR (80) NULL,
415 [NUMINT_ENVIO] VARCHAR (15) NULL,
416 [NUMEXT_ENVIO] VARCHAR (15) NULL,
417 [CRUZAMIENTOS_ENVIO] VARCHAR (40) NULL,
418 [CRUZAMIENTOS_ENVIO2] VARCHAR (40) NULL,
419 [COLONIA_ENVIO] VARCHAR (50) NULL,
420 [LOCALIDAD_ENVIO] VARCHAR (50) NULL,
421 [MUNICIPIO_ENVIO] VARCHAR (50) NULL,
422 [ESTADO_ENVIO] VARCHAR (50) NULL,
423 [PAIS_ENVIO] VARCHAR (50) NULL,
424 [CODIGO_ENVIO] VARCHAR (5) NULL,
425 [CVE_ZONA_ENVIO] VARCHAR (6) NULL,
426 [REFERENCIA_ENVIO] VARCHAR (255) NULL,
427 [CUENTA_CONTABLE] VARCHAR (28) NULL,
428 [ADDENDAF] VARCHAR (255) NULL,
429 [ADDENDAD] VARCHAR (255) NULL,
430 [NAMESPACE] VARCHAR (255) NULL,
431 [METODODEPAGO] VARCHAR (255) NULL,
432 [NUMCTAPAGO] VARCHAR (255) NULL,
433 [MODELO] VARCHAR (255) NULL,
434 [DES_IMPU1] VARCHAR (1) NULL,
435 [DES_IMPU2] VARCHAR (1) NULL,
436 [DES_IMPU3] VARCHAR (1) NULL,
437 [DES_IMPU4] VARCHAR (1) NULL,
438 [DES_PER] VARCHAR (1) NULL,
439 [LAT_GENERAL] FLOAT (53) NULL,
440 [LON_GENERAL] FLOAT (53) NULL,
441 [LAT_ENVIO] FLOAT (53) NULL,
442 [LON_ENVIO] FLOAT (53) NULL,
443 [UUID] VARCHAR (50) NULL,
444 [VERSION_SINC] DATETIME NULL,
445 [USO_CFDI] VARCHAR (5) NULL,
446 [CVE_PAIS_SAT] VARCHAR (5) NULL,
447 [NUMIDREGFISCAL] VARCHAR (128) NULL,
448 [FORMADEPAGOSAT] VARCHAR (5) NULL,
449 [EnterpriseOwnerId] INT NOT NULL,
450 [CreatedDate] DATETIME NULL,
451 [LastUpdate] DATETIME NULL,
452 [LastUpdateBy] VARCHAR (100) NULL,
453 CONSTRAINT [PK_CUSTOMER_BANK_PORTAL] PRIMARY KEY CLUSTERED ([CLAVE] ASC, [EnterpriseOwnerId] ASC)
454);
455
456
457GO
458PRINT N'Creating [dbo].[Enterprise]...';
459
460
461GO
462CREATE TABLE [dbo].[Enterprise] (
463 [Id] INT IDENTITY (1, 1) NOT NULL,
464 [Name] VARCHAR (100) NOT NULL,
465 [Name2] VARCHAR (100) NULL,
466 [SaeEnterpriseName] VARCHAR (100) NOT NULL,
467 [SaeIdentifierNumber] VARCHAR (50) NOT NULL,
468 [SaeDataBaseName] VARCHAR (100) NOT NULL,
469 [SaeDataBaseSchema] VARCHAR (100) NOT NULL,
470 [BankEnterpriseName] VARCHAR (100) NOT NULL,
471 [BankIdentifierNumber] VARCHAR (50) NOT NULL,
472 [BankDataBaseName] VARCHAR (100) NOT NULL,
473 [BankDataBaseSchema] VARCHAR (100) NOT NULL,
474 [CreatedDate] DATETIME NOT NULL,
475 [LastUpdate] DATETIME NOT NULL,
476 [LastUpdateBy] VARCHAR (100) NULL,
477 [IsActive] INT NOT NULL,
478 PRIMARY KEY CLUSTERED ([Id] ASC)
479);
480
481
482GO
483PRINT N'Creating [dbo].[ErrorLog]...';
484
485
486GO
487CREATE TABLE [dbo].[ErrorLog] (
488 [Id] INT IDENTITY (1, 1) NOT NULL,
489 [SourceError] VARCHAR (MAX) NOT NULL,
490 [ErrorMessage] VARCHAR (MAX) NOT NULL,
491 [DateTimeLogged] DATETIME NULL,
492 PRIMARY KEY CLUSTERED ([Id] ASC)
493);
494
495
496GO
497PRINT N'Creating [dbo].[Parameter]...';
498
499
500GO
501CREATE TABLE [dbo].[Parameter] (
502 [Id] INT IDENTITY (1, 1) NOT NULL,
503 [Group] VARCHAR (100) NULL,
504 [Name] VARCHAR (100) NULL,
505 [Value] VARCHAR (100) NULL,
506 PRIMARY KEY CLUSTERED ([Id] ASC)
507);
508
509
510GO
511PRINT N'Creating [dbo].[PermisionByRole]...';
512
513
514GO
515CREATE TABLE [dbo].[PermisionByRole] (
516 [Id] INT IDENTITY (1, 1) NOT NULL,
517 [RoleId] INT NOT NULL,
518 [PermissionId] INT NOT NULL,
519 [IsActive] INT NOT NULL,
520 [CreatedDate] DATETIME NULL,
521 [LastUpdate] DATETIME NOT NULL,
522 [LastUpdateBy] VARCHAR (100) NULL,
523 PRIMARY KEY CLUSTERED ([Id] ASC)
524);
525
526
527GO
528PRINT N'Creating [dbo].[Permission]...';
529
530
531GO
532CREATE TABLE [dbo].[Permission] (
533 [Id] INT IDENTITY (1, 1) NOT NULL,
534 [ObjectId] VARCHAR (100) NOT NULL,
535 [ParentId] VARCHAR (100) NOT NULL,
536 [TextToShow] VARCHAR (100) NOT NULL,
537 [TypeId] INT NOT NULL,
538 [CreatedDate] DATETIME NULL,
539 [LastUpdate] DATETIME NOT NULL,
540 [LastUpdateBy] VARCHAR (100) NULL,
541 PRIMARY KEY CLUSTERED ([Id] ASC)
542);
543
544
545GO
546PRINT N'Creating [dbo].[PermissionType]...';
547
548
549GO
550CREATE TABLE [dbo].[PermissionType] (
551 [Id] INT IDENTITY (1, 1) NOT NULL,
552 [Type] VARCHAR (50) NOT NULL,
553 [Description] VARCHAR (100) NULL,
554 [CreatedDate] DATETIME NULL,
555 [LastUpdate] DATETIME NOT NULL,
556 [LastUpdateBy] VARCHAR (100) NULL,
557 PRIMARY KEY CLUSTERED ([Id] ASC)
558);
559
560
561GO
562PRINT N'Creating [dbo].[Person]...';
563
564
565GO
566CREATE TABLE [dbo].[Person] (
567 [Id] INT IDENTITY (1, 1) NOT NULL,
568 [FirstName] VARCHAR (100) NOT NULL,
569 [SecondName] VARCHAR (100) NULL,
570 [ThirdName] VARCHAR (100) NULL,
571 [FirstLastName] VARCHAR (100) NOT NULL,
572 [SecondLastName] VARCHAR (100) NULL,
573 [ThirdLastName] VARCHAR (100) NULL,
574 [Picture] VARCHAR (MAX) NULL,
575 [CreatedDate] DATETIME NULL,
576 [LastUpdate] DATETIME NOT NULL,
577 [LastUpdateBy] VARCHAR (100) NULL,
578 PRIMARY KEY CLUSTERED ([Id] ASC)
579);
580
581
582GO
583PRINT N'Creating [dbo].[Role]...';
584
585
586GO
587CREATE TABLE [dbo].[Role] (
588 [Id] INT IDENTITY (1, 1) NOT NULL,
589 [Name] VARCHAR (50) NOT NULL,
590 [Description] VARCHAR (100) NULL,
591 [CreatedDate] DATETIME NULL,
592 [LastUpdate] DATETIME NOT NULL,
593 [LastUpdateBy] VARCHAR (100) NULL,
594 PRIMARY KEY CLUSTERED ([Id] ASC)
595);
596
597
598GO
599PRINT N'Creating [dbo].[User]...';
600
601
602GO
603CREATE TABLE [dbo].[User] (
604 [Id] INT IDENTITY (1, 1) NOT NULL,
605 [UserName] VARCHAR (100) NOT NULL,
606 [UserPassword] VARCHAR (50) NOT NULL,
607 [IsActive] INT NOT NULL,
608 [RoleId] INT NULL,
609 [PersonId] INT NULL,
610 [Avatar] VARCHAR (MAX) NULL,
611 [LastLoginDate] DATETIME NULL,
612 [CreatedDate] DATETIME NULL,
613 [LastUpdate] DATETIME NOT NULL,
614 [LastUpdateBy] VARCHAR (100) NULL,
615 PRIMARY KEY CLUSTERED ([Id] ASC)
616);
617
618
619GO
620PRINT N'Creating unnamed constraint on [dbo].[BankAccount]...';
621
622
623GO
624ALTER TABLE [dbo].[BankAccount]
625 ADD DEFAULT (1) FOR [Currency];
626
627
628GO
629PRINT N'Creating unnamed constraint on [dbo].[BankAccount]...';
630
631
632GO
633ALTER TABLE [dbo].[BankAccount]
634 ADD DEFAULT (30) FOR [CuttingDay];
635
636
637GO
638PRINT N'Creating unnamed constraint on [dbo].[BankAccount]...';
639
640
641GO
642ALTER TABLE [dbo].[BankAccount]
643 ADD DEFAULT (1) FOR [NextCheck];
644
645
646GO
647PRINT N'Creating unnamed constraint on [dbo].[BankAccount]...';
648
649
650GO
651ALTER TABLE [dbo].[BankAccount]
652 ADD DEFAULT (1) FOR [IsActive];
653
654
655GO
656PRINT N'Creating unnamed constraint on [dbo].[BankAccount]...';
657
658
659GO
660ALTER TABLE [dbo].[BankAccount]
661 ADD DEFAULT (0) FOR [IsForeign];
662
663
664GO
665PRINT N'Creating unnamed constraint on [dbo].[BankAccount]...';
666
667
668GO
669ALTER TABLE [dbo].[BankAccount]
670 ADD DEFAULT (GETDATE()) FOR [LastUpdate];
671
672
673GO
674PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
675
676
677GO
678ALTER TABLE [dbo].[BankMovementDetail]
679 ADD DEFAULT ((0)) FOR [MONTO_IVA];
680
681
682GO
683PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
684
685
686GO
687ALTER TABLE [dbo].[BankMovementDetail]
688 ADD DEFAULT ((0)) FOR [MONTO_EXT];
689
690
691GO
692PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
693
694
695GO
696ALTER TABLE [dbo].[BankMovementDetail]
697 ADD DEFAULT ((0)) FOR [NUMCARGO];
698
699
700GO
701PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
702
703
704GO
705ALTER TABLE [dbo].[BankMovementDetail]
706 ADD DEFAULT ((0)) FOR [NUMCPTOPADRE];
707
708
709GO
710PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
711
712
713GO
714ALTER TABLE [dbo].[BankMovementDetail]
715 ADD DEFAULT ((0)) FOR [TIPOCAMBIOSAE];
716
717
718GO
719PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
720
721
722GO
723ALTER TABLE [dbo].[BankMovementDetail]
724 ADD DEFAULT ((0)) FOR [TIPOCAMBIOBANCO];
725
726
727GO
728PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
729
730
731GO
732ALTER TABLE [dbo].[BankMovementDetail]
733 ADD DEFAULT ((0)) FOR [NO_PARTIDASAE];
734
735
736GO
737PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
738
739
740GO
741ALTER TABLE [dbo].[BankMovementDetail]
742 ADD DEFAULT ((1)) FOR [MONEDADOC];
743
744
745GO
746PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
747
748
749GO
750ALTER TABLE [dbo].[BankMovementDetail]
751 ADD DEFAULT ((0)) FOR [IVA_MC];
752
753
754GO
755PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
756
757
758GO
759ALTER TABLE [dbo].[BankMovementDetail]
760 ADD DEFAULT ((0)) FOR [PART_CONCEPSAE];
761
762
763GO
764PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
765
766
767GO
768ALTER TABLE [dbo].[BankMovementDetail]
769 ADD DEFAULT ((0)) FOR [PART_ANTICIPO];
770
771
772GO
773PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
774
775
776GO
777ALTER TABLE [dbo].[BankMovementDetail]
778 ADD DEFAULT (getdate()) FOR [CreatedDate];
779
780
781GO
782PRINT N'Creating unnamed constraint on [dbo].[BankMovementDetail]...';
783
784
785GO
786ALTER TABLE [dbo].[BankMovementDetail]
787 ADD DEFAULT (getdate()) FOR [LastUpdate];
788
789
790GO
791PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
792
793
794GO
795ALTER TABLE [dbo].[BankMovementHeader]
796 ADD DEFAULT ((0)) FOR [CON_PART];
797
798
799GO
800PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
801
802
803GO
804ALTER TABLE [dbo].[BankMovementHeader]
805 ADD DEFAULT ((0)) FOR [NUM_CHEQUE];
806
807
808GO
809PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
810
811
812GO
813ALTER TABLE [dbo].[BankMovementHeader]
814 ADD DEFAULT ('N') FOR [BAND_PRN];
815
816
817GO
818PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
819
820
821GO
822ALTER TABLE [dbo].[BankMovementHeader]
823 ADD DEFAULT ('N') FOR [BAND_CONT];
824
825
826GO
827PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
828
829
830GO
831ALTER TABLE [dbo].[BankMovementHeader]
832 ADD DEFAULT ('N') FOR [ACT_SAE];
833
834
835GO
836PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
837
838
839GO
840ALTER TABLE [dbo].[BankMovementHeader]
841 ADD DEFAULT ((0)) FOR [SAE_COI];
842
843
844GO
845PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
846
847
848GO
849ALTER TABLE [dbo].[BankMovementHeader]
850 ADD DEFAULT ((0)) FOR [MONTO_IVA_TOT];
851
852
853GO
854PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
855
856
857GO
858ALTER TABLE [dbo].[BankMovementHeader]
859 ADD DEFAULT ((0)) FOR [MONTO_EXT];
860
861
862GO
863PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
864
865
866GO
867ALTER TABLE [dbo].[BankMovementHeader]
868 ADD DEFAULT ((1)) FOR [MONEDA];
869
870
871GO
872PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
873
874
875GO
876ALTER TABLE [dbo].[BankMovementHeader]
877 ADD DEFAULT ((1)) FOR [T_CAMBIO];
878
879
880GO
881PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
882
883
884GO
885ALTER TABLE [dbo].[BankMovementHeader]
886 ADD DEFAULT ((0)) FOR [HORA];
887
888
889GO
890PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
891
892
893GO
894ALTER TABLE [dbo].[BankMovementHeader]
895 ADD DEFAULT ((0)) FOR [CTA_TRANSF];
896
897
898GO
899PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
900
901
902GO
903ALTER TABLE [dbo].[BankMovementHeader]
904 ADD DEFAULT ((0)) FOR [CVE_INST];
905
906
907GO
908PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
909
910
911GO
912ALTER TABLE [dbo].[BankMovementHeader]
913 ADD DEFAULT ((1)) FOR [TCAMBIOSAE];
914
915
916GO
917PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
918
919
920GO
921ALTER TABLE [dbo].[BankMovementHeader]
922 ADD DEFAULT ((0)) FOR [CONC_SAE];
923
924
925GO
926PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
927
928
929GO
930ALTER TABLE [dbo].[BankMovementHeader]
931 ADD DEFAULT ((0)) FOR [TRANS_COI];
932
933
934GO
935PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
936
937
938GO
939ALTER TABLE [dbo].[BankMovementHeader]
940 ADD DEFAULT ((0)) FOR [INTSAENOI];
941
942
943GO
944PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
945
946
947GO
948ALTER TABLE [dbo].[BankMovementHeader]
949 ADD DEFAULT ((0)) FOR [REVISADO];
950
951
952GO
953PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
954
955
956GO
957ALTER TABLE [dbo].[BankMovementHeader]
958 ADD DEFAULT ('NORMAL') FOR [PRIORIDAD];
959
960
961GO
962PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
963
964
965GO
966ALTER TABLE [dbo].[BankMovementHeader]
967 ADD DEFAULT ((0)) FOR [DOC_ASOC];
968
969
970GO
971PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
972
973
974GO
975ALTER TABLE [dbo].[BankMovementHeader]
976 ADD DEFAULT ((0)) FOR [RESALTAR];
977
978
979GO
980PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
981
982
983GO
984ALTER TABLE [dbo].[BankMovementHeader]
985 ADD DEFAULT ((0)) FOR [ANTICIPO];
986
987
988GO
989PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
990
991
992GO
993ALTER TABLE [dbo].[BankMovementHeader]
994 ADD DEFAULT ((0)) FOR [IDTRANSF];
995
996
997GO
998PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
999
1000
1001GO
1002ALTER TABLE [dbo].[BankMovementHeader]
1003 ADD DEFAULT ((0)) FOR [MULTI_CLPV];
1004
1005
1006GO
1007PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
1008
1009
1010GO
1011ALTER TABLE [dbo].[BankMovementHeader]
1012 ADD DEFAULT ((0)) FOR [NUM_CONC_AUTO];
1013
1014
1015GO
1016PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
1017
1018
1019GO
1020ALTER TABLE [dbo].[BankMovementHeader]
1021 ADD DEFAULT ((0)) FOR [COI_DEPTO];
1022
1023
1024GO
1025PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
1026
1027
1028GO
1029ALTER TABLE [dbo].[BankMovementHeader]
1030 ADD DEFAULT ((0)) FOR [COI_CCOSTOS];
1031
1032
1033GO
1034PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
1035
1036
1037GO
1038ALTER TABLE [dbo].[BankMovementHeader]
1039 ADD DEFAULT ((0)) FOR [COI_PROYECTO];
1040
1041
1042GO
1043PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
1044
1045
1046GO
1047ALTER TABLE [dbo].[BankMovementHeader]
1048 ADD DEFAULT (0) FOR [IsPostedOnSae];
1049
1050
1051GO
1052PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
1053
1054
1055GO
1056ALTER TABLE [dbo].[BankMovementHeader]
1057 ADD DEFAULT (getdate()) FOR [CreatedDate];
1058
1059
1060GO
1061PRINT N'Creating unnamed constraint on [dbo].[BankMovementHeader]...';
1062
1063
1064GO
1065ALTER TABLE [dbo].[BankMovementHeader]
1066 ADD DEFAULT (getdate()) FOR [LastUpdate];
1067
1068
1069GO
1070PRINT N'Creating unnamed constraint on [dbo].[Customer]...';
1071
1072
1073GO
1074ALTER TABLE [dbo].[Customer]
1075 ADD DEFAULT ('N') FOR [DES_IMPU1];
1076
1077
1078GO
1079PRINT N'Creating unnamed constraint on [dbo].[Customer]...';
1080
1081
1082GO
1083ALTER TABLE [dbo].[Customer]
1084 ADD DEFAULT ('N') FOR [DES_IMPU2];
1085
1086
1087GO
1088PRINT N'Creating unnamed constraint on [dbo].[Customer]...';
1089
1090
1091GO
1092ALTER TABLE [dbo].[Customer]
1093 ADD DEFAULT ('N') FOR [DES_IMPU3];
1094
1095
1096GO
1097PRINT N'Creating unnamed constraint on [dbo].[Customer]...';
1098
1099
1100GO
1101ALTER TABLE [dbo].[Customer]
1102 ADD DEFAULT ('N') FOR [DES_IMPU4];
1103
1104
1105GO
1106PRINT N'Creating unnamed constraint on [dbo].[Customer]...';
1107
1108
1109GO
1110ALTER TABLE [dbo].[Customer]
1111 ADD DEFAULT ('N') FOR [DES_PER];
1112
1113
1114GO
1115PRINT N'Creating unnamed constraint on [dbo].[Customer]...';
1116
1117
1118GO
1119ALTER TABLE [dbo].[Customer]
1120 ADD DEFAULT (getdate()) FOR [CreatedDate];
1121
1122
1123GO
1124PRINT N'Creating unnamed constraint on [dbo].[Customer]...';
1125
1126
1127GO
1128ALTER TABLE [dbo].[Customer]
1129 ADD DEFAULT (getdate()) FOR [LastUpdate];
1130
1131
1132GO
1133PRINT N'Creating unnamed constraint on [dbo].[Enterprise]...';
1134
1135
1136GO
1137ALTER TABLE [dbo].[Enterprise]
1138 ADD DEFAULT (GETDATE()) FOR [LastUpdate];
1139
1140
1141GO
1142PRINT N'Creating unnamed constraint on [dbo].[Enterprise]...';
1143
1144
1145GO
1146ALTER TABLE [dbo].[Enterprise]
1147 ADD DEFAULT 0 FOR [IsActive];
1148
1149
1150GO
1151PRINT N'Creating unnamed constraint on [dbo].[ErrorLog]...';
1152
1153
1154GO
1155ALTER TABLE [dbo].[ErrorLog]
1156 ADD DEFAULT (getdate()) FOR [DateTimeLogged];
1157
1158
1159GO
1160PRINT N'Creating unnamed constraint on [dbo].[PermisionByRole]...';
1161
1162
1163GO
1164ALTER TABLE [dbo].[PermisionByRole]
1165 ADD DEFAULT (GETDATE()) FOR [LastUpdate];
1166
1167
1168GO
1169PRINT N'Creating unnamed constraint on [dbo].[Permission]...';
1170
1171
1172GO
1173ALTER TABLE [dbo].[Permission]
1174 ADD DEFAULT (GETDATE()) FOR [LastUpdate];
1175
1176
1177GO
1178PRINT N'Creating unnamed constraint on [dbo].[PermissionType]...';
1179
1180
1181GO
1182ALTER TABLE [dbo].[PermissionType]
1183 ADD DEFAULT (GETDATE()) FOR [LastUpdate];
1184
1185
1186GO
1187PRINT N'Creating unnamed constraint on [dbo].[Person]...';
1188
1189
1190GO
1191ALTER TABLE [dbo].[Person]
1192 ADD DEFAULT (GETDATE()) FOR [LastUpdate];
1193
1194
1195GO
1196PRINT N'Creating unnamed constraint on [dbo].[Role]...';
1197
1198
1199GO
1200ALTER TABLE [dbo].[Role]
1201 ADD DEFAULT (GETDATE()) FOR [LastUpdate];
1202
1203
1204GO
1205PRINT N'Creating unnamed constraint on [dbo].[User]...';
1206
1207
1208GO
1209ALTER TABLE [dbo].[User]
1210 ADD DEFAULT (0) FOR [IsActive];
1211
1212
1213GO
1214PRINT N'Creating unnamed constraint on [dbo].[User]...';
1215
1216
1217GO
1218ALTER TABLE [dbo].[User]
1219 ADD DEFAULT (GETDATE()) FOR [LastUpdate];
1220
1221
1222GO
1223PRINT N'Creating [dbo].[FK_OWNER_ENTERPRISE]...';
1224
1225
1226GO
1227ALTER TABLE [dbo].[BankAccount]
1228 ADD CONSTRAINT [FK_OWNER_ENTERPRISE] FOREIGN KEY ([OwnerEnterprise]) REFERENCES [dbo].[Enterprise] ([Id]);
1229
1230
1231GO
1232PRINT N'Creating [dbo].[FK_OWNER_MOVEMENT_DETAIL_ENTERPRISE]...';
1233
1234
1235GO
1236ALTER TABLE [dbo].[BankMovementDetail]
1237 ADD CONSTRAINT [FK_OWNER_MOVEMENT_DETAIL_ENTERPRISE] FOREIGN KEY ([NUM_MOV], [EnterpriseOwnerId], [AccountNumber]) REFERENCES [dbo].[BankMovementHeader] ([NUM_REG], [EnterpriseOwnerId], [AccountNumber]);
1238
1239
1240GO
1241PRINT N'Creating [dbo].[FK_ROLE_PERMISSION]...';
1242
1243
1244GO
1245ALTER TABLE [dbo].[PermisionByRole]
1246 ADD CONSTRAINT [FK_ROLE_PERMISSION] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Role] ([Id]);
1247
1248
1249GO
1250PRINT N'Creating [dbo].[FK_PERMISSION]...';
1251
1252
1253GO
1254ALTER TABLE [dbo].[PermisionByRole]
1255 ADD CONSTRAINT [FK_PERMISSION] FOREIGN KEY ([PermissionId]) REFERENCES [dbo].[Permission] ([Id]);
1256
1257
1258GO
1259PRINT N'Creating [dbo].[FK_PERMISSION_TYPE]...';
1260
1261
1262GO
1263ALTER TABLE [dbo].[Permission]
1264 ADD CONSTRAINT [FK_PERMISSION_TYPE] FOREIGN KEY ([TypeId]) REFERENCES [dbo].[PermissionType] ([Id]);
1265
1266
1267GO
1268PRINT N'Creating [dbo].[FK_USER_ROLE]...';
1269
1270
1271GO
1272ALTER TABLE [dbo].[User]
1273 ADD CONSTRAINT [FK_USER_ROLE] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Role] ([Id]);
1274
1275
1276GO
1277PRINT N'Creating [dbo].[FK_USER_PERSON]...';
1278
1279
1280GO
1281ALTER TABLE [dbo].[User]
1282 ADD CONSTRAINT [FK_USER_PERSON] FOREIGN KEY ([PersonId]) REFERENCES [dbo].[Person] ([Id]);
1283
1284
1285GO
1286PRINT N'Creating [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG]...';
1287
1288
1289GO
1290-- =============================================
1291-- Autor: diego.as
1292-- Fecha de Creacion: 9/30/2018
1293-- Description: Inserta log de error
1294
1295/*
1296-- Ejemplo de Ejecucion:
1297 EXEC [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG]
1298 @ERROR_MESSAGE = 'PRUEBA'
1299 ,@SOURCE_ERROR = 'PRUEBA'
1300*/
1301-- =============================================
1302CREATE PROCEDURE [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG]
1303 (
1304 @ERROR_MESSAGE VARCHAR(MAX)
1305 ,@SOURCE_ERROR VARCHAR(MAX)
1306 )
1307AS
1308 BEGIN
1309 SET NOCOUNT ON;
1310
1311 INSERT INTO [dbo].[ErrorLog]
1312 (
1313 [SourceError]
1314 ,[ErrorMessage]
1315 ,[DateTimeLogged]
1316 )
1317 VALUES
1318 (
1319 @SOURCE_ERROR -- SourceError - varchar(max)
1320 ,@ERROR_MESSAGE -- ErrorMessage - varchar(max)
1321 ,GETDATE() -- DateTimeLogged - datetime
1322 );
1323 END;
1324GO
1325PRINT N'Creating [dbo].[BANK_PORTAL_SP_GET_INVOICE_BY_CUSTOMER_AND_ENTERPRISE]...';
1326
1327
1328GO
1329-- =============================================
1330-- Autor: diego.as
1331-- Fecha de Creacion: 10/14/2018 @ A-TEAM Sprint
1332-- Description: SP que obtiene los registros de
1333
1334/*
1335-- Ejemplo de Ejecucion:
1336 EXEC [dbo].[BANK_PORTAL_SP_GET_INVOICE_BY_CUSTOMER_AND_ENTERPRISE]
1337 @XML_DOCUMENT = '
1338 <BankMovement>
1339 <BANK/>
1340 <BankAccountNumber/>
1341 <CustomerId>49</CustomerId>
1342 <DATE/>
1343 <ENTERPRISE/>
1344 <EnterpriseOwnerId>2</EnterpriseOwnerId>
1345 <IS_POSTED_ON_SAE/>
1346 <NUM_REG>0</NUM_REG>
1347 <Obervations/>
1348 <REF1/>
1349 <RegisterDate>0001-01-01T00:00:00</RegisterDate>
1350 <TOTAL_AMOUNT>0</TOTAL_AMOUNT>
1351 </BankMovement>
1352 '
1353*/
1354-- =============================================
1355CREATE PROCEDURE [dbo].[BANK_PORTAL_SP_GET_INVOICE_BY_CUSTOMER_AND_ENTERPRISE] (@XML_DOCUMENT XML)
1356AS
1357 BEGIN
1358
1359 DECLARE
1360 @QUERY VARCHAR(MAX)
1361 ,@ENTERPRISE_OWNER_ID INT
1362 ,@CUSTOMER_ID INT
1363 ,@CODE_CUSTOMER VARCHAR(MAX)
1364 ,@SAE_DATABASE_NAME VARCHAR(MAX)
1365 ,@SAE_IDENTIFIER_NUMBER VARCHAR(MAX)
1366 ,@SAE_DATABASE_SCHEMA VARCHAR(MAX);
1367
1368 BEGIN TRY
1369
1370 -- ------------------------------------------------------------------------------------
1371 -- Get Basic Info
1372 -- ------------------------------------------------------------------------------------
1373 SELECT
1374 @ENTERPRISE_OWNER_ID = [x].[Rec].[query]('./EnterpriseOwnerId').[value]('.' ,
1375 'int')
1376 ,@CUSTOMER_ID = [x].[Rec].[query]('./CustomerId').[value]('.' ,
1377 'int')
1378 FROM
1379 @XML_DOCUMENT.[nodes]('/BankMovement') AS [x] ([Rec]);
1380
1381 -- ------------------------------------------------------------------------------------
1382 -- Get Enterprise Info
1383 -- ------------------------------------------------------------------------------------
1384 SELECT TOP 1
1385 @SAE_DATABASE_NAME = [SaeDataBaseName]
1386 ,@SAE_DATABASE_SCHEMA = [SaeDataBaseSchema]
1387 ,@SAE_IDENTIFIER_NUMBER = [SaeIdentifierNumber]
1388 FROM
1389 [dbo].[Enterprise]
1390 WHERE
1391 [Id] = @ENTERPRISE_OWNER_ID;
1392
1393 -- ------------------------------------------------------------------------------------
1394 -- Get Customer Info
1395 -- ------------------------------------------------------------------------------------
1396 SELECT TOP 1
1397 @CODE_CUSTOMER = [CLAVE]
1398 FROM
1399 [dbo].[Customer]
1400 WHERE
1401 [Id] = @CUSTOMER_ID
1402 AND [EnterpriseOwnerId] = @ENTERPRISE_OWNER_ID;
1403
1404 -- ------------------------------------------------------------------------------------
1405 -- PREPARE QUERY
1406 -- ------------------------------------------------------------------------------------
1407 SET @QUERY = '
1408 SELECT
1409 [H].[CVE_CLIE] COLLATE DATABASE_DEFAULT AS [CVE_CLIE]
1410 ,[H].[NO_FACTURA] COLLATE DATABASE_DEFAULT AS [NO_FACTURA]
1411 ,[H].[REFER] COLLATE DATABASE_DEFAULT AS [REFER]
1412 ,[H].[IMPORTE]
1413 ,[H].[NUM_MONED]
1414 ,SUM([D].[IMPORTE]) AS SUMA_DETALLE
1415 FROM [' + @SAE_DATABASE_NAME + '].[dbo].[CUEN_M'
1416 + @SAE_IDENTIFIER_NUMBER + '] AS H
1417 LEFT JOIN [' + @SAE_DATABASE_NAME + '].[dbo].[CUEN_DET' + @SAE_IDENTIFIER_NUMBER
1418 + '] AS D
1419 ON(D.[CVE_CLIE] = H.[CVE_CLIE] AND D.[NO_FACTURA] = H.[NO_FACTURA])
1420 WHERE H.[CVE_CLIE] = ''' + @CODE_CUSTOMER + '''
1421 GROUP BY
1422 H.[CVE_CLIE]
1423 ,H.[NO_FACTURA]
1424 ,H.[REFER]
1425 ,H.[IMPORTE]
1426 ,H.[NUM_MONED]
1427 ,D.[NO_FACTURA]
1428 HAVING H.[IMPORTE] - SUM(D.[IMPORTE]) > 0
1429 ';
1430
1431 -- ------------------------------------------------------------------------------------
1432 -- Uncoment this line only in DEBUG
1433 -- ------------------------------------------------------------------------------------
1434 --PRINT (@QUERY);
1435
1436 -- ------------------------------------------------------------------------------------
1437 -- Execute Query
1438 -- ------------------------------------------------------------------------------------
1439 EXEC(@QUERY);
1440 END TRY
1441 BEGIN CATCH
1442 DECLARE @ERROR VARCHAR(MAX) = ERROR_MESSAGE();
1443
1444 EXEC [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG] @ERROR_MESSAGE = @ERROR , -- varchar(max)
1445 @SOURCE_ERROR = 'BANK_PORTAL_SP_GET_INVOICE_BY_CUSTOMER_AND_ENTERPRISE'; -- varchar(max)
1446
1447 END CATCH;
1448 END;
1449GO
1450PRINT N'Creating [dbo].[BANK_PORTAL_SP_IMPORT_BANK_ACCOUNTS]...';
1451
1452
1453GO
1454-- =============================================
1455-- Autor: diego.as
1456-- Fecha de Creacion: 9/30/2018
1457-- Description: SP que obtiene los registros de CUENTAS BANCARIAS DE CADA UNA DE LAS EMPRESAS CONFIGURADAS
1458
1459/*
1460-- Ejemplo de Ejecucion:
1461 EXEC [dbo].[BANK_PORTAL_SP_IMPORT_BANK_ACCOUNTS]
1462*/
1463-- =============================================
1464CREATE PROCEDURE [dbo].[BANK_PORTAL_SP_IMPORT_BANK_ACCOUNTS]
1465AS
1466 BEGIN
1467 SET NOCOUNT ON;
1468 --
1469 DECLARE @ENTERPRICES TABLE
1470 (
1471 [ID] INT IDENTITY(1 ,1)
1472 PRIMARY KEY
1473 ,[ENTERPRISE_ID] INT
1474 ,[ENTERPRISE_BANK_DATABASE_NAME] VARCHAR(100)
1475 ,[ENTERPRISE_BANK_DATABASE_SCHEMA] VARCHAR(100)
1476 );
1477
1478 DECLARE @BANK_ACCOUNTS TABLE
1479 (
1480 [Id] INT IDENTITY(1 ,1)
1481 PRIMARY KEY
1482 ,[RegisterNumber] VARCHAR(100)
1483 ,[AccountName] VARCHAR(100)
1484 ,[AccountNumber] VARCHAR(100)
1485 ,[BankName] VARCHAR(100)
1486 ,[Currency] INT
1487 ,[BranchOffice] VARCHAR(100)
1488 ,[CountableAccount] VARCHAR(100)
1489 ,[CuttingDay] INT
1490 ,[NextCheck] INT
1491 ,[IsActive] INT
1492 ,[OpeningDate] DATETIME
1493 ,[BeginningBalance] FLOAT
1494 ,[IsForeign] INT
1495 ,[OwnerEnterprise] INT
1496 );
1497
1498 DECLARE
1499 @QUERY_TO_EXECUTE VARCHAR(MAX) = N''
1500 ,@CURRENT_PROCESS_NUMBER INT = 0
1501 ,@ENTERPRICES_TO_PROCESS INT = 0
1502 ,@ENTERPRISE_ID_IN_PROCESS INT
1503 ,@ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS VARCHAR(100)
1504 ,@ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS VARCHAR(100);
1505
1506 -- -------------------------------------------------------------
1507 -- Enterprices to process
1508 -- -------------------------------------------------------------
1509 INSERT INTO @ENTERPRICES
1510 (
1511 [ENTERPRISE_ID]
1512 ,[ENTERPRISE_BANK_DATABASE_NAME]
1513 ,[ENTERPRISE_BANK_DATABASE_SCHEMA]
1514 )
1515 SELECT
1516 [Id]
1517 ,[BankDataBaseName]
1518 ,[BankDataBaseSchema]
1519 FROM
1520 [dbo].[Enterprise]
1521 WHERE
1522 [Id] > 0
1523 AND [IsActive] = 1;
1524
1525 -- -------------------------------------------------------------
1526 -- Make the Query
1527 -- -------------------------------------------------------------
1528 SELECT
1529 @ENTERPRICES_TO_PROCESS = COUNT(*)
1530 FROM
1531 @ENTERPRICES;
1532
1533 IF (
1534 @ENTERPRICES_TO_PROCESS IS NOT NULL
1535 AND @ENTERPRICES_TO_PROCESS > 0
1536 )
1537 BEGIN
1538
1539 -- -----------------------------------------------------------------------------
1540 -- Start error handling
1541 -- -----------------------------------------------------------------------------
1542 BEGIN TRY
1543 SET @CURRENT_PROCESS_NUMBER = 0;
1544 WHILE EXISTS ( SELECT TOP 1
1545 1
1546 FROM
1547 @ENTERPRICES )
1548 BEGIN
1549 -- -----------------------------------------------------------------------------
1550 -- Get ENTERPRISE info
1551 -- -----------------------------------------------------------------------------
1552 SELECT TOP 1
1553 @ENTERPRISE_ID_IN_PROCESS = [ENTERPRISE_ID]
1554 ,@ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS = [ENTERPRISE_BANK_DATABASE_NAME]
1555 ,@ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS = [ENTERPRISE_BANK_DATABASE_SCHEMA]
1556 FROM
1557 @ENTERPRICES;
1558
1559 SET @QUERY_TO_EXECUTE = @QUERY_TO_EXECUTE
1560 + '
1561 SELECT
1562 [NUM_REG]
1563 ,[NOMBRE_CTA] COLLATE DATABASE_DEFAULT AS AccountName
1564 ,[NUM_CTA] COLLATE DATABASE_DEFAULT AS AccountNumber
1565 ,[BANCO]COLLATE DATABASE_DEFAULT AS BankName
1566 ,[MONEDA] AS Currency
1567 ,[SUCURSAL] COLLATE DATABASE_DEFAULT AS BranchOffice
1568 ,[CTA_CONTAB] COLLATE DATABASE_DEFAULT AS CountableAccount
1569 ,[DIA_CORTE] AS CuttingDay
1570 ,[SIG_CHEQUE] AS NextCheck
1571 ,[STATUS] AS IsActive
1572 ,[FECH_APER] AS OpeningDate
1573 ,[SALDO_INI] AS BeginningBalance
1574 ,[ESBANCOEXT] AS IsForeign
1575 ,' + (CONVERT(VARCHAR ,@ENTERPRISE_ID_IN_PROCESS))
1576 + ' AS OwnerEnterprise
1577 FROM [' + @ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS + '].['
1578 + @ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS
1579 + '].[CTAS]
1580 ';
1581
1582 SET @CURRENT_PROCESS_NUMBER = @CURRENT_PROCESS_NUMBER + 1;
1583
1584
1585 IF (@CURRENT_PROCESS_NUMBER < @ENTERPRICES_TO_PROCESS)
1586 BEGIN
1587 SET @QUERY_TO_EXECUTE = @QUERY_TO_EXECUTE + '
1588 UNION ALL
1589 ';
1590 END;
1591
1592 -- ---------------------------------------------------------------
1593 -- Delete enterprise processed
1594 -- ---------------------------------------------------------------
1595 DELETE FROM
1596 @ENTERPRICES
1597 WHERE
1598 [ENTERPRISE_ID] = @ENTERPRISE_ID_IN_PROCESS;
1599 END;
1600
1601 -- ---------------------------------------------------------------------------
1602 -- Show built query (UNCOMMENT ONLY IN DEBUG)
1603 -- ---------------------------------------------------------------------------
1604 --PRINT (@QUERY_TO_EXECUTE);
1605
1606 -- ---------------------------------------------------------------------------
1607 -- Execute the query
1608 -- ---------------------------------------------------------------------------
1609 INSERT @BANK_ACCOUNTS
1610 EXEC (
1611 @QUERY_TO_EXECUTE
1612 );
1613
1614 -- ---------------------------------------------------------------------------
1615 -- Apply results
1616 -- ---------------------------------------------------------------------------
1617 TRUNCATE TABLE [dbo].[BankAccount];
1618
1619 INSERT INTO [dbo].[BankAccount]
1620 (
1621 [RegisterNumber]
1622 ,[AccountName]
1623 ,[AccountNumber]
1624 ,[BankName]
1625 ,[Currency]
1626 ,[BranchOffice]
1627 ,[CountableAccount]
1628 ,[CuttingDay]
1629 ,[NextCheck]
1630 ,[IsActive]
1631 ,[OpeningDate]
1632 ,[BeginningBalance]
1633 ,[IsForeign]
1634 ,[OwnerEnterprise]
1635 ,[CreatedDate]
1636 ,[LastUpdate]
1637 ,[LastUpdateBy]
1638 )
1639 SELECT
1640 CASE WHEN [BC].[RegisterNumber] < 10
1641 THEN ('0' + CAST([BC].[RegisterNumber] AS VARCHAR))
1642 ELSE CAST([BC].[RegisterNumber] AS VARCHAR)
1643 END
1644 ,[BC].[AccountName]
1645 ,[BC].[AccountNumber]
1646 ,[BC].[BankName]
1647 ,[BC].[Currency]
1648 ,[BC].[BranchOffice]
1649 ,[BC].[CountableAccount]
1650 ,[BC].[CuttingDay]
1651 ,[BC].[NextCheck]
1652 ,[BC].[IsActive]
1653 ,[BC].[OpeningDate]
1654 ,CAST([BC].[BeginningBalance] AS NUMERIC(18 ,6))
1655 ,[BC].[IsForeign]
1656 ,[BC].[OwnerEnterprise]
1657 ,GETDATE()
1658 ,GETDATE()
1659 ,'AUTOMATIC_PROCESS'
1660 FROM
1661 @BANK_ACCOUNTS AS [BC]
1662 WHERE
1663 [BC].[Id] > 0;
1664 END TRY
1665 BEGIN CATCH
1666 DECLARE @ERROR_MESSAGE VARCHAR(MAX) = ERROR_MESSAGE();
1667
1668 EXEC [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG] @ERROR_MESSAGE = @ERROR_MESSAGE , -- varchar(max)
1669 @SOURCE_ERROR = 'BANK_PORTAL_SP_IMPORT_BANK_ACCOUNTS'; -- varchar(max)
1670
1671 END CATCH;
1672
1673
1674
1675
1676 END;
1677
1678 END;
1679GO
1680PRINT N'Creating [dbo].[BANK_PORTAL_SP_IMPORT_BANK_MOVEMENT_DETAIL]...';
1681
1682
1683GO
1684-- =============================================
1685-- Autor: diego.as
1686-- Fecha de Creacion: 10/02/2018
1687-- Description: SP que obtiene los registros de DETALLE DE MOVIMIENTOS BANCARIOS DE CADA UNA DE LAS EMPRESAS CONFIGURADAS
1688
1689/*
1690-- Ejemplo de Ejecucion:
1691 EXEC [dbo].[BANK_PORTAL_SP_IMPORT_BANK_MOVEMENT_DETAIL]
1692*/
1693-- =============================================
1694CREATE PROCEDURE [dbo].[BANK_PORTAL_SP_IMPORT_BANK_MOVEMENT_DETAIL]
1695AS
1696 BEGIN
1697 SET NOCOUNT ON;
1698 --
1699 DECLARE @ENTERPRICES TABLE
1700 (
1701 [ID] INT IDENTITY(1 ,1)
1702 PRIMARY KEY
1703 ,[ENTERPRISE_ID] INT
1704 ,[ENTERPRISE_BANK_DATABASE_NAME] VARCHAR(100)
1705 ,[ENTERPRISE_BANK_DATABASE_SCHEMA] VARCHAR(100)
1706 );
1707
1708 DECLARE @BANK_ACCOUNTS TABLE
1709 (
1710 [ID] INT IDENTITY(1 ,1)
1711 PRIMARY KEY
1712 ,[REGISTER_NUMBER] VARCHAR(100)
1713 ,[ACCOUNT_NUMBER] VARCHAR(100)
1714 ,[OWNER_ENTERPRISE] INT
1715 ,[ENTERPRISE_BANK_DATABASE_NAME] VARCHAR(100)
1716 ,[ENTERPRISE_BANK_DATABASE_SCHEMA] VARCHAR(100)
1717 );
1718
1719 DECLARE @BANK_MOVEMENT_DETAIL TABLE
1720 (
1721 [Id] INT IDENTITY(1 ,1)
1722 PRIMARY KEY
1723 ,[NUM_REGP] [INT] NOT NULL
1724 ,[NUM_MOV] [INT] NOT NULL
1725 ,[CVE_CONCEP] [VARCHAR](6) NULL
1726 ,[REFERENCIA] [VARCHAR](20) NULL
1727 ,[MONTO_IVA] [FLOAT] NULL
1728 DEFAULT ((0))
1729 ,[FACT] [VARCHAR](20) NULL
1730 ,[MONTO_DOC] [FLOAT] NOT NULL
1731 ,[MONTO_EXT] [FLOAT] NULL
1732 DEFAULT ((0))
1733 ,[ORDEN] [INT] NOT NULL
1734 ,[X_OBSER] [VARCHAR](255) NULL
1735 ,[NUMCARGO] [INT] NULL
1736 DEFAULT ((0))
1737 ,[NUMCPTOPADRE] [INT] NULL
1738 DEFAULT ((0))
1739 ,[TIPOCAMBIOSAE] [FLOAT] NULL
1740 DEFAULT ((0))
1741 ,[TIPOCAMBIOBANCO] [FLOAT] NULL
1742 DEFAULT ((0))
1743 ,[DOCTO] [VARCHAR](20) NULL
1744 ,[NO_PARTIDASAE] [INT] NULL
1745 DEFAULT ((0))
1746 ,[MONEDADOC] [INT] NULL
1747 DEFAULT ((1))
1748 ,[PART_CLPV] [VARCHAR](10) NULL
1749 ,[PART_CLPV_RFC] [VARCHAR](13) NULL
1750 ,[PART_ANOMBREDE] [VARCHAR](120) NULL
1751 ,[IVA_MC] [FLOAT] NULL
1752 DEFAULT ((0))
1753 ,[PART_CTA_CONTAB_ASOC] [VARCHAR](40) NULL
1754 ,[PART_CONCEPSAE] [INT] NULL
1755 DEFAULT ((0))
1756 ,[PART_ANTICIPO] [INT] NULL
1757 DEFAULT ((0))
1758 ,[PART_CTA_BANCO_ASOC] [VARCHAR](50) NULL
1759 ,[PART_CVE_BANCO_ASOC] [VARCHAR](6) NULL
1760 ,[EnterpriseOwnerId] [INT] NOT NULL
1761 ,[AccountNumber] [VARCHAR](100) NOT NULL
1762 );
1763
1764 DECLARE
1765 @QUERY_TO_EXECUTE VARCHAR(MAX) = N''
1766 ,@CURRENT_PROCESS_NUMBER INT = 0
1767 ,@BANK_ACCOUNTS_TO_PROCESS INT = 0
1768 ,@ENTERPRISE_ID_IN_PROCESS INT
1769 ,@ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS VARCHAR(100)
1770 ,@ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS VARCHAR(100)
1771 ,@CURRENT_BANK_ACCOUNT_ID_IN_PROCESS INT = 0
1772 ,@CURRENT_BANK_ACCOUNT_REGISTER_NUMBER_IN_PROCESS VARCHAR(100)
1773 ,@CURRENT_BANK_ACCOUNT_PROCESS_NUMBER INT = 0
1774 ,@CURRENT_BANK_ACCOUNT_NUMBER_IN_PROCESS VARCHAR(100);
1775
1776 -- -------------------------------------------------------------
1777 -- Enterprices to process
1778 -- -------------------------------------------------------------
1779 INSERT INTO @ENTERPRICES
1780 (
1781 [ENTERPRISE_ID]
1782 ,[ENTERPRISE_BANK_DATABASE_NAME]
1783 ,[ENTERPRISE_BANK_DATABASE_SCHEMA]
1784 )
1785 SELECT
1786 [Id]
1787 ,[BankDataBaseName]
1788 ,[BankDataBaseSchema]
1789 FROM
1790 [dbo].[Enterprise]
1791 WHERE
1792 [Id] > 0
1793 AND [IsActive] = 1;
1794
1795 -- -------------------------------------------------------------------
1796 -- Bank accounts to process based on the companies previusly selected
1797 -- -------------------------------------------------------------------
1798 INSERT INTO @BANK_ACCOUNTS
1799 (
1800 [REGISTER_NUMBER]
1801 ,[OWNER_ENTERPRISE]
1802 ,[ACCOUNT_NUMBER]
1803 ,[ENTERPRISE_BANK_DATABASE_NAME]
1804 ,[ENTERPRISE_BANK_DATABASE_SCHEMA]
1805 )
1806 SELECT
1807 [BC].[RegisterNumber]
1808 ,[BC].[OwnerEnterprise]
1809 ,[BC].[AccountNumber]
1810 ,[E].[ENTERPRISE_BANK_DATABASE_NAME]
1811 ,[E].[ENTERPRISE_BANK_DATABASE_SCHEMA]
1812 FROM
1813 [dbo].[BankAccount] AS [BC]
1814 INNER JOIN @ENTERPRICES AS [E]
1815 ON ([E].[ENTERPRISE_ID] = [BC].[OwnerEnterprise])
1816 WHERE
1817 [BC].[Id] > 0
1818 ORDER BY
1819 [BC].[OwnerEnterprise] ASC
1820 ,[BC].[RegisterNumber] ASC;
1821
1822 -- -------------------------------------------------------------
1823 -- Make the Query
1824 -- -------------------------------------------------------------
1825 SELECT
1826 @BANK_ACCOUNTS_TO_PROCESS = COUNT(*)
1827 FROM
1828 @BANK_ACCOUNTS;
1829
1830 IF (
1831 @BANK_ACCOUNTS_TO_PROCESS IS NOT NULL
1832 AND @BANK_ACCOUNTS_TO_PROCESS > 0
1833 )
1834 BEGIN
1835 -- -----------------------------------------------------------------------------
1836 -- Start error handling
1837 -- -----------------------------------------------------------------------------
1838 BEGIN TRY
1839
1840 SET @CURRENT_PROCESS_NUMBER = 0;
1841 SET @CURRENT_BANK_ACCOUNT_ID_IN_PROCESS = 0;
1842 WHILE EXISTS ( SELECT TOP 1
1843 1
1844 FROM
1845 @BANK_ACCOUNTS )
1846 BEGIN
1847 -- -----------------------------------------------------------------------------
1848 -- Get BANK ACCOUNT info
1849 -- -----------------------------------------------------------------------------
1850 SELECT TOP 1
1851 @CURRENT_BANK_ACCOUNT_ID_IN_PROCESS = [ID]
1852 ,@ENTERPRISE_ID_IN_PROCESS = [OWNER_ENTERPRISE]
1853 ,@CURRENT_BANK_ACCOUNT_REGISTER_NUMBER_IN_PROCESS = [REGISTER_NUMBER]
1854 ,@ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS = [ENTERPRISE_BANK_DATABASE_NAME]
1855 ,@ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS = [ENTERPRISE_BANK_DATABASE_SCHEMA]
1856 ,@CURRENT_BANK_ACCOUNT_NUMBER_IN_PROCESS = [ACCOUNT_NUMBER]
1857 FROM
1858 @BANK_ACCOUNTS
1859 WHERE
1860 [ID] > 0;
1861
1862 -- -----------------------------------------------------------------------------
1863 -- Set Query info
1864 -- -----------------------------------------------------------------------------
1865 SET @QUERY_TO_EXECUTE = @QUERY_TO_EXECUTE
1866 + '
1867 SELECT
1868 [NUM_REGP],
1869 [NUM_MOV],
1870 [CVE_CONCEP] COLLATE DATABASE_DEFAULT [CVE_CONCEP],
1871 [REFERENCIA] COLLATE DATABASE_DEFAULT [REFERENCIA],
1872 [MONTO_IVA],
1873 [FACT] COLLATE DATABASE_DEFAULT [FACT],
1874 [MONTO_DOC],
1875 [MONTO_EXT],
1876 [ORDEN],
1877 [X_OBSER] COLLATE DATABASE_DEFAULT [X_OBSER],
1878 [NUMCARGO],
1879 [NUMCPTOPADRE],
1880 [TIPOCAMBIOSAE],
1881 [TIPOCAMBIOBANCO],
1882 [DOCTO] COLLATE DATABASE_DEFAULT [DOCTO],
1883 [NO_PARTIDASAE],
1884 [MONEDADOC],
1885 [PART_CLPV] COLLATE DATABASE_DEFAULT [PART_CLPV],
1886 [PART_CLPV_RFC] COLLATE DATABASE_DEFAULT [PART_CLPV_RFC],
1887 [PART_ANOMBREDE] COLLATE DATABASE_DEFAULT [PART_ANOMBREDE],
1888 [IVA_MC],
1889 [PART_CTA_CONTAB_ASOC] COLLATE DATABASE_DEFAULT [PART_CTA_CONTAB_ASOC],
1890 [PART_CONCEPSAE],
1891 [PART_ANTICIPO],
1892 [PART_CTA_BANCO_ASOC] COLLATE DATABASE_DEFAULT [PART_CTA_BANCO_ASOC],
1893 [PART_CVE_BANCO_ASOC] COLLATE DATABASE_DEFAULT [PART_CVE_BANCO_ASOC],
1894 ' + (CONVERT(VARCHAR ,@ENTERPRISE_ID_IN_PROCESS))
1895 + ' AS EnterpriseOwnerId,
1896 '''
1897 + CAST(@CURRENT_BANK_ACCOUNT_NUMBER_IN_PROCESS AS VARCHAR(100))
1898 + ''' AS [AccountNumber]
1899 FROM ['
1900 + @ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS + '].['
1901 + @ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS
1902 + '].[PARMOVS'
1903 + @CURRENT_BANK_ACCOUNT_REGISTER_NUMBER_IN_PROCESS + ']
1904 ';
1905
1906
1907 SET @CURRENT_PROCESS_NUMBER = @CURRENT_PROCESS_NUMBER + 1;
1908
1909
1910 IF (@CURRENT_PROCESS_NUMBER < @BANK_ACCOUNTS_TO_PROCESS)
1911 BEGIN
1912 SET @QUERY_TO_EXECUTE = @QUERY_TO_EXECUTE + '
1913 UNION ALL
1914 ';
1915 END;
1916
1917 -- ---------------------------------------------------------------
1918 -- Delete enterprise processed
1919 -- ---------------------------------------------------------------
1920 DELETE FROM
1921 @BANK_ACCOUNTS
1922 WHERE
1923 [ID] = @CURRENT_BANK_ACCOUNT_ID_IN_PROCESS;
1924 END;
1925
1926 -- ---------------------------------------------------------------------------
1927 -- Show built query (UNCOMMENT ONLY IN DEBUG)
1928 -- ---------------------------------------------------------------------------
1929 --PRINT (@QUERY_TO_EXECUTE);
1930
1931 -- ---------------------------------------------------------------------------
1932 -- Execute the query
1933 -- ---------------------------------------------------------------------------
1934 INSERT @BANK_MOVEMENT_DETAIL
1935 EXEC (
1936 @QUERY_TO_EXECUTE
1937 );
1938
1939 -- ---------------------------------------------------------------------------
1940 -- Apply results
1941 -- ---------------------------------------------------------------------------
1942 MERGE [dbo].[BankMovementDetail] AS [BMHT]
1943 USING
1944 (
1945 SELECT
1946 *
1947 FROM
1948 @BANK_MOVEMENT_DETAIL
1949 ) AS [BMHS]
1950 ON (
1951 [BMHS].[NUM_REGP] = [BMHT].[NUM_REGP]
1952 AND [BMHS].[EnterpriseOwnerId] = [BMHT].[EnterpriseOwnerId]
1953 AND [BMHS].[AccountNumber] = [BMHT].[AccountNumber]
1954 )
1955 WHEN MATCHED THEN
1956 UPDATE SET
1957 [BMHT].[LastUpdate] = GETDATE()
1958 ,[BMHT].[LastUpdateBy] = 'IMPORT_PROCESS'
1959 WHEN NOT MATCHED THEN
1960 INSERT
1961 (
1962 [NUM_REGP]
1963 ,[NUM_MOV]
1964 ,[CVE_CONCEP]
1965 ,[REFERENCIA]
1966 ,[MONTO_IVA]
1967 ,[FACT]
1968 ,[MONTO_DOC]
1969 ,[MONTO_EXT]
1970 ,[ORDEN]
1971 ,[X_OBSER]
1972 ,[NUMCARGO]
1973 ,[NUMCPTOPADRE]
1974 ,[TIPOCAMBIOSAE]
1975 ,[TIPOCAMBIOBANCO]
1976 ,[DOCTO]
1977 ,[NO_PARTIDASAE]
1978 ,[MONEDADOC]
1979 ,[PART_CLPV]
1980 ,[PART_CLPV_RFC]
1981 ,[PART_ANOMBREDE]
1982 ,[IVA_MC]
1983 ,[PART_CTA_CONTAB_ASOC]
1984 ,[PART_CONCEPSAE]
1985 ,[PART_ANTICIPO]
1986 ,[PART_CTA_BANCO_ASOC]
1987 ,[PART_CVE_BANCO_ASOC]
1988 ,[EnterpriseOwnerId]
1989 ,[AccountNumber]
1990 ,[CreatedDate]
1991 ,[LastUpdate]
1992 ,[LastUpdateBy]
1993 )
1994 VALUES (
1995 [NUM_REGP]
1996 ,[NUM_MOV]
1997 ,[CVE_CONCEP]
1998 ,[REFERENCIA]
1999 ,[MONTO_IVA]
2000 ,[FACT]
2001 ,[MONTO_DOC]
2002 ,[MONTO_EXT]
2003 ,[ORDEN]
2004 ,[X_OBSER]
2005 ,[NUMCARGO]
2006 ,[NUMCPTOPADRE]
2007 ,[TIPOCAMBIOSAE]
2008 ,[TIPOCAMBIOBANCO]
2009 ,[DOCTO]
2010 ,[NO_PARTIDASAE]
2011 ,[MONEDADOC]
2012 ,[PART_CLPV]
2013 ,[PART_CLPV_RFC]
2014 ,[PART_ANOMBREDE]
2015 ,[IVA_MC]
2016 ,[PART_CTA_CONTAB_ASOC]
2017 ,[PART_CONCEPSAE]
2018 ,[PART_ANTICIPO]
2019 ,[PART_CTA_BANCO_ASOC]
2020 ,[PART_CVE_BANCO_ASOC]
2021 ,[EnterpriseOwnerId]
2022 ,[AccountNumber]
2023 ,GETDATE()
2024 ,GETDATE()
2025 ,'IMPORT_PROCESS'
2026 );
2027
2028 END TRY
2029 BEGIN CATCH
2030 DECLARE @ERROR_MESSAGE VARCHAR(MAX) = ERROR_MESSAGE();
2031
2032 EXEC [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG] @ERROR_MESSAGE = @ERROR_MESSAGE , -- varchar(max)
2033 @SOURCE_ERROR = 'BANK_PORTAL_SP_IMPORT_BANK_MOVEMENT_DETAIL'; -- varchar(max)
2034 END CATCH;
2035 END;
2036 END;
2037GO
2038PRINT N'Creating [dbo].[BANK_PORTAL_SP_IMPORT_BANK_MOVEMENT_HEADER]...';
2039
2040
2041GO
2042-- =============================================
2043-- Autor: diego.as
2044-- Fecha de Creacion: 10/02/2018
2045-- Description: SP que obtiene los registros de ENCABEZADOS DE MOVIMIENTOS BANCARIOS DE CADA UNA DE LAS EMPRESAS CONFIGURADAS
2046
2047/*
2048-- Ejemplo de Ejecucion:
2049 EXEC [dbo].[BANK_PORTAL_SP_IMPORT_BANK_MOVEMENT_HEADER]
2050*/
2051-- =============================================
2052CREATE PROCEDURE [dbo].[BANK_PORTAL_SP_IMPORT_BANK_MOVEMENT_HEADER]
2053AS
2054 BEGIN
2055 SET NOCOUNT ON;
2056 --
2057 DECLARE @ENTERPRICES TABLE
2058 (
2059 [ID] INT IDENTITY(1 ,1)
2060 PRIMARY KEY
2061 ,[ENTERPRISE_ID] INT
2062 ,[ENTERPRISE_BANK_DATABASE_NAME] VARCHAR(100)
2063 ,[ENTERPRISE_BANK_DATABASE_SCHEMA] VARCHAR(100)
2064 );
2065
2066 DECLARE @BANK_ACCOUNTS TABLE
2067 (
2068 [ID] INT IDENTITY(1 ,1)
2069 PRIMARY KEY
2070 ,[REGISTER_NUMBER] VARCHAR(100)
2071 ,[ACCOUNT_NUMBER] VARCHAR(100)
2072 ,[OWNER_ENTERPRISE] INT
2073 ,[ENTERPRISE_BANK_DATABASE_NAME] VARCHAR(100)
2074 ,[ENTERPRISE_BANK_DATABASE_SCHEMA] VARCHAR(100)
2075 );
2076
2077 DECLARE @BANK_MOVEMENT_HEADER TABLE
2078 (
2079 [Id] INT IDENTITY(1 ,1)
2080 PRIMARY KEY
2081 ,[NUM_REG] [INT] NOT NULL
2082 ,[CVE_CONCEP] [VARCHAR](6) NOT NULL
2083 ,[CON_PART] [INT] NULL
2084 DEFAULT ((0))
2085 ,[NUM_CHEQUE] [INT] NULL
2086 DEFAULT ((0))
2087 ,[REF1] [VARCHAR](20) NULL
2088 ,[REF2] [VARCHAR](20) NULL
2089 ,[STATUS] [VARCHAR](1) NOT NULL
2090 ,[FECHA] [DATETIME] NOT NULL
2091 ,[F_COBRO] [DATETIME] NOT NULL
2092 ,[BAND_PRN] [VARCHAR](1) NULL
2093 DEFAULT ('N')
2094 ,[BAND_CONT] [VARCHAR](1) NULL
2095 DEFAULT ('N')
2096 ,[ACT_SAE] [VARCHAR](1) NULL
2097 DEFAULT ('N')
2098 ,[NUM_POL] [VARCHAR](5) NULL
2099 ,[TIP_POL] [VARCHAR](2) NULL
2100 ,[SAE_COI] [INT] NULL
2101 DEFAULT ((0))
2102 ,[MONTO_TOT] [FLOAT] NOT NULL
2103 ,[MONTO_IVA_TOT] [FLOAT] NULL
2104 DEFAULT ((0))
2105 ,[MONTO_EXT] [FLOAT] NULL
2106 DEFAULT ((0))
2107 ,[MONEDA] [INT] NULL
2108 DEFAULT ((1))
2109 ,[T_CAMBIO] [FLOAT] NULL
2110 DEFAULT ((1))
2111 ,[HORA] [INT] NULL
2112 DEFAULT ((0))
2113 ,[CLPV] [VARCHAR](10) NULL
2114 ,[CTA_TRANSF] [INT] NULL
2115 DEFAULT ((0))
2116 ,[FECHA_LIQ] [DATETIME] NULL
2117 ,[FECHA_POL] [DATETIME] NULL
2118 ,[CVE_INST] [INT] NULL
2119 DEFAULT ((0))
2120 ,[MONDIFSAE] [VARCHAR](1) NULL
2121 ,[TCAMBIOSAE] [FLOAT] NULL
2122 DEFAULT ((1))
2123 ,[RFC] [VARCHAR](20) NULL
2124 ,[CONC_SAE] [INT] NULL
2125 DEFAULT ((0))
2126 ,[SOLICIT] [VARCHAR](50) NULL
2127 ,[TRANS_COI] [INT] NULL
2128 DEFAULT ((0))
2129 ,[INTSAENOI] [INT] NULL
2130 DEFAULT ((0))
2131 ,[X_OBSER] [VARCHAR](255) NULL
2132 ,[FACTOR] [INT] NOT NULL
2133 ,[FORMAPAGO] [INT] NOT NULL
2134 ,[ANOMBREDE] [VARCHAR](120) NULL
2135 ,[ASOCIADO] [VARCHAR](1) NOT NULL
2136 ,[CTA_CONTAB_ASOC] [VARCHAR](40) NULL
2137 ,[REVISADO] [INT] NULL
2138 DEFAULT ((0))
2139 ,[PRIORIDAD] [VARCHAR](6) NULL
2140 DEFAULT ('NORMAL')
2141 ,[DOC_ASOC] [INT] NULL
2142 DEFAULT ((0))
2143 ,[RESALTAR] [INT] NULL
2144 DEFAULT ((0))
2145 ,[ANTICIPO] [INT] NULL
2146 DEFAULT ((0))
2147 ,[IDTRANSF] [INT] NULL
2148 DEFAULT ((0))
2149 ,[SUCURSAL] [VARCHAR](30) NULL
2150 ,[CUENTA] [VARCHAR](30) NULL
2151 ,[CLABE] [VARCHAR](30) NULL
2152 ,[MULTI_CLPV] [INT] NULL
2153 DEFAULT ((0))
2154 ,[CVE_BANCO_ASOC] [VARCHAR](6) NULL
2155 ,[NUM_CONC_AUTO] [INT] NULL
2156 DEFAULT ((0))
2157 ,[COI_DEPTO] [INT] NULL
2158 DEFAULT ((0))
2159 ,[COI_CCOSTOS] [INT] NULL
2160 DEFAULT ((0))
2161 ,[COI_PROYECTO] [INT] NULL
2162 DEFAULT ((0))
2163 ,[EnterpriseOwnerId] [INT] NOT NULL
2164 ,[AccountNumber] [VARCHAR](100) NOT NULL
2165 );
2166
2167 DECLARE
2168 @QUERY_TO_EXECUTE VARCHAR(MAX) = N''
2169 ,@CURRENT_PROCESS_NUMBER INT = 0
2170 ,@BANK_ACCOUNTS_TO_PROCESS INT = 0
2171 ,@ENTERPRISE_ID_IN_PROCESS INT
2172 ,@ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS VARCHAR(100)
2173 ,@ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS VARCHAR(100)
2174 ,@CURRENT_BANK_ACCOUNT_ID_IN_PROCESS INT = 0
2175 ,@CURRENT_BANK_ACCOUNT_REGISTER_NUMBER_IN_PROCESS VARCHAR(100)
2176 ,@CURRENT_BANK_ACCOUNT_PROCESS_NUMBER INT = 0
2177 ,@CURRENT_BANK_ACCOUNT_NUMBER_IN_PROCESS VARCHAR(100);
2178
2179 -- -------------------------------------------------------------
2180 -- Enterprices to process
2181 -- -------------------------------------------------------------
2182 INSERT INTO @ENTERPRICES
2183 (
2184 [ENTERPRISE_ID]
2185 ,[ENTERPRISE_BANK_DATABASE_NAME]
2186 ,[ENTERPRISE_BANK_DATABASE_SCHEMA]
2187 )
2188 SELECT
2189 [Id]
2190 ,[BankDataBaseName]
2191 ,[BankDataBaseSchema]
2192 FROM
2193 [dbo].[Enterprise]
2194 WHERE
2195 [Id] > 0
2196 AND [IsActive] = 1;
2197
2198 -- -------------------------------------------------------------------
2199 -- Bank accounts to process based on the companies previusly selected
2200 -- -------------------------------------------------------------------
2201 INSERT INTO @BANK_ACCOUNTS
2202 (
2203 [REGISTER_NUMBER]
2204 ,[OWNER_ENTERPRISE]
2205 ,[ACCOUNT_NUMBER]
2206 ,[ENTERPRISE_BANK_DATABASE_NAME]
2207 ,[ENTERPRISE_BANK_DATABASE_SCHEMA]
2208 )
2209 SELECT
2210 [BC].[RegisterNumber]
2211 ,[BC].[OwnerEnterprise]
2212 ,[BC].[AccountNumber]
2213 ,[E].[ENTERPRISE_BANK_DATABASE_NAME]
2214 ,[E].[ENTERPRISE_BANK_DATABASE_SCHEMA]
2215 FROM
2216 [dbo].[BankAccount] AS [BC]
2217 INNER JOIN @ENTERPRICES AS [E]
2218 ON ([E].[ENTERPRISE_ID] = [BC].[OwnerEnterprise])
2219 WHERE
2220 [BC].[Id] > 0
2221 ORDER BY
2222 [BC].[OwnerEnterprise] ASC
2223 ,[BC].[RegisterNumber] ASC;
2224
2225 -- -------------------------------------------------------------
2226 -- Make the Query
2227 -- -------------------------------------------------------------
2228 SELECT
2229 @BANK_ACCOUNTS_TO_PROCESS = COUNT(*)
2230 FROM
2231 @BANK_ACCOUNTS;
2232
2233 IF (
2234 @BANK_ACCOUNTS_TO_PROCESS IS NOT NULL
2235 AND @BANK_ACCOUNTS_TO_PROCESS > 0
2236 )
2237 BEGIN
2238 -- -----------------------------------------------------------------------------
2239 -- Start error handling
2240 -- -----------------------------------------------------------------------------
2241 BEGIN TRY
2242
2243 SET @CURRENT_PROCESS_NUMBER = 0;
2244 SET @CURRENT_BANK_ACCOUNT_ID_IN_PROCESS = 0;
2245 WHILE EXISTS ( SELECT TOP 1
2246 1
2247 FROM
2248 @BANK_ACCOUNTS )
2249 BEGIN
2250 -- -----------------------------------------------------------------------------
2251 -- Get BANK ACCOUNT info
2252 -- -----------------------------------------------------------------------------
2253 SELECT TOP 1
2254 @CURRENT_BANK_ACCOUNT_ID_IN_PROCESS = [ID]
2255 ,@ENTERPRISE_ID_IN_PROCESS = [OWNER_ENTERPRISE]
2256 ,@CURRENT_BANK_ACCOUNT_REGISTER_NUMBER_IN_PROCESS = [REGISTER_NUMBER]
2257 ,@ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS = [ENTERPRISE_BANK_DATABASE_NAME]
2258 ,@ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS = [ENTERPRISE_BANK_DATABASE_SCHEMA]
2259 ,@CURRENT_BANK_ACCOUNT_NUMBER_IN_PROCESS = [ACCOUNT_NUMBER]
2260 FROM
2261 @BANK_ACCOUNTS
2262 WHERE
2263 [ID] > 0;
2264
2265 -- -----------------------------------------------------------------------------
2266 -- Set Query info
2267 -- -----------------------------------------------------------------------------
2268 SET @QUERY_TO_EXECUTE = @QUERY_TO_EXECUTE
2269 + '
2270 SELECT
2271 [NUM_REG],
2272 [CVE_CONCEP] COLLATE DATABASE_DEFAULT AS CVE_CONCEP,
2273 [CON_PART],
2274 [NUM_CHEQUE],
2275 [REF1] COLLATE DATABASE_DEFAULT AS REF1,
2276 [REF2] COLLATE DATABASE_DEFAULT AS REF2,
2277 [STATUS] COLLATE DATABASE_DEFAULT AS STATUS,
2278 [FECHA],
2279 [F_COBRO],
2280 [BAND_PRN] COLLATE DATABASE_DEFAULT AS BAND_PRN,
2281 [BAND_CONT] COLLATE DATABASE_DEFAULT AS BAND_CONT,
2282 [ACT_SAE] COLLATE DATABASE_DEFAULT AS ACT_SAE,
2283 [NUM_POL] COLLATE DATABASE_DEFAULT AS NUM_POL,
2284 [TIP_POL] COLLATE DATABASE_DEFAULT AS TIP_POL,
2285 [SAE_COI],
2286 [MONTO_TOT],
2287 [MONTO_IVA_TOT],
2288 [MONTO_EXT],
2289 [MONEDA],
2290 [T_CAMBIO],
2291 [HORA],
2292 [CLPV] COLLATE DATABASE_DEFAULT AS CLPV,
2293 [CTA_TRANSF],
2294 [FECHA_LIQ],
2295 [FECHA_POL],
2296 [CVE_INST],
2297 [MONDIFSAE] COLLATE DATABASE_DEFAULT AS MONDIFSAE,
2298 [TCAMBIOSAE],
2299 [RFC] COLLATE DATABASE_DEFAULT AS RFC,
2300 [CONC_SAE],
2301 [SOLICIT] COLLATE DATABASE_DEFAULT AS SOLICIT,
2302 [TRANS_COI],
2303 [INTSAENOI],
2304 [X_OBSER] COLLATE DATABASE_DEFAULT AS X_OBSER,
2305 [FACTOR],
2306 [FORMAPAGO],
2307 [ANOMBREDE] COLLATE DATABASE_DEFAULT AS ANOMBREDE,
2308 [ASOCIADO] COLLATE DATABASE_DEFAULT AS ASOCIADO,
2309 [CTA_CONTAB_ASOC] COLLATE DATABASE_DEFAULT AS CTA_CONTAB_ASOC,
2310 [REVISADO],
2311 [PRIORIDAD] COLLATE DATABASE_DEFAULT AS PRIORIDAD,
2312 [DOC_ASOC],
2313 [RESALTAR],
2314 [ANTICIPO],
2315 [IDTRANSF],
2316 [SUCURSAL] COLLATE DATABASE_DEFAULT AS SUCURSAL,
2317 [CUENTA] COLLATE DATABASE_DEFAULT AS CUENTA,
2318 [CLABE] COLLATE DATABASE_DEFAULT AS CLABE,
2319 [MULTI_CLPV],
2320 [CVE_BANCO_ASOC] COLLATE DATABASE_DEFAULT AS CVE_BANCO_ASOC,
2321 [NUM_CONC_AUTO],
2322 [COI_DEPTO],
2323 [COI_CCOSTOS],
2324 [COI_PROYECTO],
2325 ' + (CONVERT(VARCHAR ,@ENTERPRISE_ID_IN_PROCESS))
2326 + ' AS EnterpriseOwnerId,
2327 '''
2328 + CAST(@CURRENT_BANK_ACCOUNT_NUMBER_IN_PROCESS AS VARCHAR(100))
2329 + ''' AS [AccountNumber]
2330 FROM ['
2331 + @ENTERPRISE_BANK_DATABASE_NAME_IN_PROCESS + '].['
2332 + @ENTERPRISE_BANK_DATABASE_SCHEMA_IN_PROCESS
2333 + '].[MOVS'
2334 + @CURRENT_BANK_ACCOUNT_REGISTER_NUMBER_IN_PROCESS + ']
2335 ';
2336
2337
2338 SET @CURRENT_PROCESS_NUMBER = @CURRENT_PROCESS_NUMBER + 1;
2339
2340
2341 IF (@CURRENT_PROCESS_NUMBER < @BANK_ACCOUNTS_TO_PROCESS)
2342 BEGIN
2343 SET @QUERY_TO_EXECUTE = @QUERY_TO_EXECUTE + '
2344 UNION ALL
2345 ';
2346 END;
2347
2348 -- ---------------------------------------------------------------
2349 -- Delete enterprise processed
2350 -- ---------------------------------------------------------------
2351 DELETE FROM
2352 @BANK_ACCOUNTS
2353 WHERE
2354 [ID] = @CURRENT_BANK_ACCOUNT_ID_IN_PROCESS;
2355 END;
2356
2357 -- ---------------------------------------------------------------------------
2358 -- Show built query (UNCOMMENT ONLY IN DEBUG)
2359 -- ---------------------------------------------------------------------------
2360 --PRINT (@QUERY_TO_EXECUTE);
2361
2362 -- ---------------------------------------------------------------------------
2363 -- Execute the query
2364 -- ---------------------------------------------------------------------------
2365 INSERT @BANK_MOVEMENT_HEADER
2366 EXEC (
2367 @QUERY_TO_EXECUTE
2368 );
2369
2370 -- ---------------------------------------------------------------------------
2371 -- Apply results
2372 -- ---------------------------------------------------------------------------
2373 MERGE [dbo].[BankMovementHeader] AS [BMHT]
2374 USING
2375 (
2376 SELECT
2377 *
2378 FROM
2379 @BANK_MOVEMENT_HEADER
2380 ) AS [BMHS]
2381 ON (
2382 [BMHS].[NUM_REG] = [BMHT].[NUM_REG]
2383 AND [BMHS].[EnterpriseOwnerId] = [BMHT].[EnterpriseOwnerId]
2384 AND [BMHS].[AccountNumber] = [BMHT].[AccountNumber]
2385 )
2386 WHEN MATCHED THEN
2387 UPDATE SET
2388 [BMHT].[LastUpdate] = GETDATE()
2389 ,[BMHT].[LastUpdateBy] = 'IMPORT_PROCESS'
2390 WHEN NOT MATCHED THEN
2391 INSERT
2392 (
2393 [NUM_REG]
2394 ,[CVE_CONCEP]
2395 ,[CON_PART]
2396 ,[NUM_CHEQUE]
2397 ,[REF1]
2398 ,[REF2]
2399 ,[STATUS]
2400 ,[FECHA]
2401 ,[F_COBRO]
2402 ,[BAND_PRN]
2403 ,[BAND_CONT]
2404 ,[ACT_SAE]
2405 ,[NUM_POL]
2406 ,[TIP_POL]
2407 ,[SAE_COI]
2408 ,[MONTO_TOT]
2409 ,[MONTO_IVA_TOT]
2410 ,[MONTO_EXT]
2411 ,[MONEDA]
2412 ,[T_CAMBIO]
2413 ,[HORA]
2414 ,[CLPV]
2415 ,[CTA_TRANSF]
2416 ,[FECHA_LIQ]
2417 ,[FECHA_POL]
2418 ,[CVE_INST]
2419 ,[MONDIFSAE]
2420 ,[TCAMBIOSAE]
2421 ,[RFC]
2422 ,[CONC_SAE]
2423 ,[SOLICIT]
2424 ,[TRANS_COI]
2425 ,[INTSAENOI]
2426 ,[X_OBSER]
2427 ,[FACTOR]
2428 ,[FORMAPAGO]
2429 ,[ANOMBREDE]
2430 ,[ASOCIADO]
2431 ,[CTA_CONTAB_ASOC]
2432 ,[REVISADO]
2433 ,[PRIORIDAD]
2434 ,[DOC_ASOC]
2435 ,[RESALTAR]
2436 ,[ANTICIPO]
2437 ,[IDTRANSF]
2438 ,[SUCURSAL]
2439 ,[CUENTA]
2440 ,[CLABE]
2441 ,[MULTI_CLPV]
2442 ,[CVE_BANCO_ASOC]
2443 ,[NUM_CONC_AUTO]
2444 ,[COI_DEPTO]
2445 ,[COI_CCOSTOS]
2446 ,[COI_PROYECTO]
2447 ,[EnterpriseOwnerId]
2448 ,[AccountNumber]
2449 ,[IsPostedOnSae]
2450 ,[DateTimeOfPostingOnSae]
2451 ,[CreatedDate]
2452 ,[LastUpdate]
2453 ,[LastUpdateBy]
2454 )
2455 VALUES (
2456 [BMHS].[NUM_REG]
2457 ,[BMHS].[CVE_CONCEP]
2458 ,[BMHS].[CON_PART]
2459 ,[BMHS].[NUM_CHEQUE]
2460 ,[BMHS].[REF1]
2461 ,[BMHS].[REF2]
2462 ,[BMHS].[STATUS]
2463 ,[BMHS].[FECHA]
2464 ,[BMHS].[F_COBRO]
2465 ,[BMHS].[BAND_PRN]
2466 ,[BMHS].[BAND_CONT]
2467 ,[BMHS].[ACT_SAE]
2468 ,[BMHS].[NUM_POL]
2469 ,[BMHS].[TIP_POL]
2470 ,[BMHS].[SAE_COI]
2471 ,[BMHS].[MONTO_TOT]
2472 ,[BMHS].[MONTO_IVA_TOT]
2473 ,[BMHS].[MONTO_EXT]
2474 ,[BMHS].[MONEDA]
2475 ,[BMHS].[T_CAMBIO]
2476 ,[BMHS].[HORA]
2477 ,[BMHS].[CLPV]
2478 ,[BMHS].[CTA_TRANSF]
2479 ,[BMHS].[FECHA_LIQ]
2480 ,[BMHS].[FECHA_POL]
2481 ,[BMHS].[CVE_INST]
2482 ,[BMHS].[MONDIFSAE]
2483 ,[BMHS].[TCAMBIOSAE]
2484 ,[BMHS].[RFC]
2485 ,[BMHS].[CONC_SAE]
2486 ,[BMHS].[SOLICIT]
2487 ,[BMHS].[TRANS_COI]
2488 ,[BMHS].[INTSAENOI]
2489 ,[BMHS].[X_OBSER]
2490 ,[BMHS].[FACTOR]
2491 ,[BMHS].[FORMAPAGO]
2492 ,[BMHS].[ANOMBREDE]
2493 ,[BMHS].[ASOCIADO]
2494 ,[BMHS].[CTA_CONTAB_ASOC]
2495 ,[BMHS].[REVISADO]
2496 ,[BMHS].[PRIORIDAD]
2497 ,[BMHS].[DOC_ASOC]
2498 ,[BMHS].[RESALTAR]
2499 ,[BMHS].[ANTICIPO]
2500 ,[BMHS].[IDTRANSF]
2501 ,[BMHS].[SUCURSAL]
2502 ,[BMHS].[CUENTA]
2503 ,[BMHS].[CLABE]
2504 ,[BMHS].[MULTI_CLPV]
2505 ,[BMHS].[CVE_BANCO_ASOC]
2506 ,[BMHS].[NUM_CONC_AUTO]
2507 ,[BMHS].[COI_DEPTO]
2508 ,[BMHS].[COI_CCOSTOS]
2509 ,[BMHS].[COI_PROYECTO]
2510 ,[BMHS].[EnterpriseOwnerId]
2511 ,[BMHS].[AccountNumber]
2512 ,0
2513 ,NULL
2514 ,GETDATE()
2515 ,GETDATE()
2516 ,'IMPORT_PROCESS'
2517 );
2518
2519 END TRY
2520 BEGIN CATCH
2521 DECLARE @ERROR_MESSAGE VARCHAR(MAX) = ERROR_MESSAGE();
2522
2523 EXEC [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG] @ERROR_MESSAGE = @ERROR_MESSAGE , -- varchar(max)
2524 @SOURCE_ERROR = 'BANK_PORTAL_SP_IMPORT_BANK_MOVEMENT_HEADER'; -- varchar(max)
2525
2526 END CATCH;
2527
2528 END;
2529
2530 END;
2531GO
2532PRINT N'Creating [dbo].[BANK_PORTAL_SP_IMPORT_CUSTOMER]...';
2533
2534
2535GO
2536-- =============================================
2537-- Autor: diego.as
2538-- Fecha de Creacion: 10/02/2018
2539-- Description: SP que obtiene los registros de ENCABEZADOS DE MOVIMIENTOS BANCARIOS DE CADA UNA DE LAS EMPRESAS CONFIGURADAS
2540
2541/*
2542-- Ejemplo de Ejecucion:
2543 EXEC [dbo].[BANK_PORTAL_SP_IMPORT_CUSTOMER]
2544*/
2545-- =============================================
2546CREATE PROCEDURE [dbo].[BANK_PORTAL_SP_IMPORT_CUSTOMER]
2547AS
2548 BEGIN
2549 SET NOCOUNT ON;
2550 --
2551 DECLARE @ENTERPRICES TABLE
2552 (
2553 [ID] INT IDENTITY(1 ,1)
2554 PRIMARY KEY
2555 ,[ENTERPRISE_ID] INT
2556 ,[SAE_DATABASE_NAME] VARCHAR(100)
2557 ,[SAE_DATABASE_SCHEMA] VARCHAR(100)
2558 ,[SAE_IDENTIFIER_NUMBER] VARCHAR(50)
2559 );
2560 DECLARE @CUSTOMER TABLE
2561 (
2562 [ID] INT IDENTITY(1 ,1)
2563 PRIMARY KEY
2564 ,[CLAVE] [VARCHAR](10) NOT NULL
2565 ,[STATUS] [VARCHAR](1) NOT NULL
2566 ,[NOMBRE] [VARCHAR](120) NULL
2567 ,[RFC] [VARCHAR](15) NULL
2568 ,[CALLE] [VARCHAR](80) NULL
2569 ,[NUMINT] [VARCHAR](15) NULL
2570 ,[NUMEXT] [VARCHAR](15) NULL
2571 ,[CRUZAMIENTOS] [VARCHAR](40) NULL
2572 ,[CRUZAMIENTOS2] [VARCHAR](40) NULL
2573 ,[COLONIA] [VARCHAR](50) NULL
2574 ,[CODIGO] [VARCHAR](5) NULL
2575 ,[LOCALIDAD] [VARCHAR](50) NULL
2576 ,[MUNICIPIO] [VARCHAR](50) NULL
2577 ,[ESTADO] [VARCHAR](50) NULL
2578 ,[PAIS] [VARCHAR](50) NULL
2579 ,[NACIONALIDAD] [VARCHAR](40) NULL
2580 ,[REFERDIR] [VARCHAR](255) NULL
2581 ,[TELEFONO] [VARCHAR](25) NULL
2582 ,[CLASIFIC] [VARCHAR](5) NULL
2583 ,[FAX] [VARCHAR](25) NULL
2584 ,[PAG_WEB] [VARCHAR](60) NULL
2585 ,[CURP] [VARCHAR](18) NULL
2586 ,[CVE_ZONA] [VARCHAR](6) NULL
2587 ,[IMPRIR] [VARCHAR](1) NULL
2588 ,[MAIL] [VARCHAR](1) NULL
2589 ,[NIVELSEC] [INT] NULL
2590 ,[ENVIOSILEN] [VARCHAR](1) NULL
2591 ,[EMAILPRED] [VARCHAR](60) NULL
2592 ,[DIAREV] [VARCHAR](2) NULL
2593 ,[DIAPAGO] [VARCHAR](2) NULL
2594 ,[CON_CREDITO] [VARCHAR](1) NULL
2595 ,[DIASCRED] [INT] NULL
2596 ,[LIMCRED] [FLOAT] NULL
2597 ,[SALDO] [FLOAT] NULL
2598 ,[LISTA_PREC] [INT] NULL
2599 ,[CVE_BITA] [INT] NULL
2600 ,[ULT_PAGOD] [VARCHAR](20) NULL
2601 ,[ULT_PAGOM] [FLOAT] NULL
2602 ,[ULT_PAGOF] [DATETIME] NULL
2603 ,[DESCUENTO] [FLOAT] NULL
2604 ,[ULT_VENTAD] [VARCHAR](20) NULL
2605 ,[ULT_COMPM] [FLOAT] NULL
2606 ,[FCH_ULTCOM] [DATETIME] NULL
2607 ,[VENTAS] [FLOAT] NULL
2608 ,[CVE_VEND] [VARCHAR](5) NULL
2609 ,[CVE_OBS] [INT] NULL
2610 ,[TIPO_EMPRESA] [VARCHAR](1) NULL
2611 ,[MATRIZ] [VARCHAR](10) NULL
2612 ,[PROSPECTO] [VARCHAR](1) NULL
2613 ,[CALLE_ENVIO] [VARCHAR](80) NULL
2614 ,[NUMINT_ENVIO] [VARCHAR](15) NULL
2615 ,[NUMEXT_ENVIO] [VARCHAR](15) NULL
2616 ,[CRUZAMIENTOS_ENVIO] [VARCHAR](40) NULL
2617 ,[CRUZAMIENTOS_ENVIO2] [VARCHAR](40) NULL
2618 ,[COLONIA_ENVIO] [VARCHAR](50) NULL
2619 ,[LOCALIDAD_ENVIO] [VARCHAR](50) NULL
2620 ,[MUNICIPIO_ENVIO] [VARCHAR](50) NULL
2621 ,[ESTADO_ENVIO] [VARCHAR](50) NULL
2622 ,[PAIS_ENVIO] [VARCHAR](50) NULL
2623 ,[CODIGO_ENVIO] [VARCHAR](5) NULL
2624 ,[CVE_ZONA_ENVIO] [VARCHAR](6) NULL
2625 ,[REFERENCIA_ENVIO] [VARCHAR](255) NULL
2626 ,[CUENTA_CONTABLE] [VARCHAR](28) NULL
2627 ,[ADDENDAF] [VARCHAR](255) NULL
2628 ,[ADDENDAD] [VARCHAR](255) NULL
2629 ,[NAMESPACE] [VARCHAR](255) NULL
2630 ,[METODODEPAGO] [VARCHAR](255) NULL
2631 ,[NUMCTAPAGO] [VARCHAR](255) NULL
2632 ,[MODELO] [VARCHAR](255) NULL
2633 ,[DES_IMPU1] [VARCHAR](1) NULL
2634 DEFAULT ('N')
2635 ,[DES_IMPU2] [VARCHAR](1) NULL
2636 DEFAULT ('N')
2637 ,[DES_IMPU3] [VARCHAR](1) NULL
2638 DEFAULT ('N')
2639 ,[DES_IMPU4] [VARCHAR](1) NULL
2640 DEFAULT ('N')
2641 ,[DES_PER] [VARCHAR](1) NULL
2642 DEFAULT ('N')
2643 ,[LAT_GENERAL] [FLOAT] NULL
2644 ,[LON_GENERAL] [FLOAT] NULL
2645 ,[LAT_ENVIO] [FLOAT] NULL
2646 ,[LON_ENVIO] [FLOAT] NULL
2647 ,[UUID] [VARCHAR](50) NULL
2648 ,[VERSION_SINC] [DATETIME] NULL
2649 ,[USO_CFDI] [VARCHAR](5) NULL
2650 ,[CVE_PAIS_SAT] [VARCHAR](5) NULL
2651 ,[NUMIDREGFISCAL] [VARCHAR](128) NULL
2652 ,[FORMADEPAGOSAT] [VARCHAR](5) NULL
2653 ,[EnterpriseOwnerId] [INT]
2654 );
2655
2656 DECLARE
2657 @QUERY_TO_EXECUTE VARCHAR(MAX) = N''
2658 ,@CURRENT_PROCESS_NUMBER INT = 0
2659 ,@ENTERPRISES_TO_PROCESS INT = 0
2660 ,@OWNER_ENTERPRISE_IN_PROCESS INT = 0
2661 ,@ENTERPRISE_ID_IN_PROCESS INT
2662 ,@SAE_DATABASE_NAME_IN_PROCESS VARCHAR(100)
2663 ,@SAE_DATABASE_SCHEMA_IN_PROCESS VARCHAR(100)
2664 ,@SAE_IDENTIFIER_NUMBER_IN_PROCESS VARCHAR(50);
2665
2666 -- -------------------------------------------------------------
2667 -- Enterprices to process
2668 -- -------------------------------------------------------------
2669 INSERT INTO @ENTERPRICES
2670 (
2671 [ENTERPRISE_ID]
2672 ,[SAE_DATABASE_NAME]
2673 ,[SAE_DATABASE_SCHEMA]
2674 ,[SAE_IDENTIFIER_NUMBER]
2675 )
2676 SELECT
2677 [Id]
2678 ,[SaeDataBaseName]
2679 ,[SaeDataBaseSchema]
2680 ,[SaeIdentifierNumber]
2681 FROM
2682 [dbo].[Enterprise]
2683 WHERE
2684 [Id] > 0
2685 AND [IsActive] = 1;
2686
2687 -- -------------------------------------------------------------
2688 -- Make the Query
2689 -- -------------------------------------------------------------
2690 SELECT
2691 @ENTERPRISES_TO_PROCESS = COUNT(*)
2692 FROM
2693 @ENTERPRICES;
2694
2695 IF (
2696 @ENTERPRISES_TO_PROCESS IS NOT NULL
2697 AND @ENTERPRISES_TO_PROCESS > 0
2698 )
2699 BEGIN
2700 -- -----------------------------------------------------------------------------
2701 -- Start error handling
2702 -- -----------------------------------------------------------------------------
2703 BEGIN TRY
2704
2705 SET @CURRENT_PROCESS_NUMBER = 0;
2706 SET @ENTERPRISE_ID_IN_PROCESS = 0;
2707 WHILE EXISTS ( SELECT TOP 1
2708 1
2709 FROM
2710 @ENTERPRICES )
2711 BEGIN
2712 -- -----------------------------------------------------------------------------
2713 -- Get ENTERPRISE info
2714 -- -----------------------------------------------------------------------------
2715 SELECT TOP 1
2716 @ENTERPRISE_ID_IN_PROCESS = [ID]
2717 ,@OWNER_ENTERPRISE_IN_PROCESS = [ENTERPRISE_ID]
2718 ,@SAE_DATABASE_NAME_IN_PROCESS = [SAE_DATABASE_NAME]
2719 ,@SAE_DATABASE_SCHEMA_IN_PROCESS = [SAE_DATABASE_SCHEMA]
2720 ,@SAE_IDENTIFIER_NUMBER_IN_PROCESS = [SAE_IDENTIFIER_NUMBER]
2721 FROM
2722 @ENTERPRICES
2723 WHERE
2724 [ID] > 0;
2725
2726 -- -----------------------------------------------------------------------------
2727 -- Set Query info
2728 -- -----------------------------------------------------------------------------
2729 SET @QUERY_TO_EXECUTE = @QUERY_TO_EXECUTE
2730 + '
2731 SELECT
2732 [CLAVE] COLLATE DATABASE_DEFAULT AS [CLAVE],
2733 [STATUS] COLLATE DATABASE_DEFAULT AS [STATUS],
2734 [NOMBRE] COLLATE DATABASE_DEFAULT AS [NOMBRE],
2735 [RFC] COLLATE DATABASE_DEFAULT AS [RFC],
2736 [CALLE] COLLATE DATABASE_DEFAULT AS [CALLE],
2737 [NUMINT] COLLATE DATABASE_DEFAULT AS [NUMINT],
2738 [NUMEXT] COLLATE DATABASE_DEFAULT AS [NUMEXT],
2739 [CRUZAMIENTOS] COLLATE DATABASE_DEFAULT AS [CRUZAMIENTOS],
2740 [CRUZAMIENTOS2] COLLATE DATABASE_DEFAULT AS [CRUZAMIENTOS2],
2741 [COLONIA] COLLATE DATABASE_DEFAULT AS [COLONIA],
2742 [CODIGO] COLLATE DATABASE_DEFAULT AS [CODIGO],
2743 [LOCALIDAD] COLLATE DATABASE_DEFAULT AS [LOCALIDAD],
2744 [MUNICIPIO] COLLATE DATABASE_DEFAULT AS [MUNICIPIO],
2745 [ESTADO] COLLATE DATABASE_DEFAULT AS [ESTADO],
2746 [PAIS] COLLATE DATABASE_DEFAULT AS [PAIS],
2747 [NACIONALIDAD] COLLATE DATABASE_DEFAULT AS [NACIONALIDAD],
2748 [REFERDIR] COLLATE DATABASE_DEFAULT AS [REFERDIR],
2749 [TELEFONO] COLLATE DATABASE_DEFAULT AS [TELEFONO],
2750 [CLASIFIC] COLLATE DATABASE_DEFAULT AS [CLASIFIC],
2751 [FAX] COLLATE DATABASE_DEFAULT AS [FAX],
2752 [PAG_WEB] COLLATE DATABASE_DEFAULT AS [PAG_WEB],
2753 [CURP] COLLATE DATABASE_DEFAULT AS [CURP],
2754 [CVE_ZONA] COLLATE DATABASE_DEFAULT AS [CVE_ZONA],
2755 [IMPRIR] COLLATE DATABASE_DEFAULT AS [IMPRIR],
2756 [MAIL] COLLATE DATABASE_DEFAULT AS [MAIL],
2757 [NIVELSEC],
2758 [ENVIOSILEN] COLLATE DATABASE_DEFAULT AS [ENVIOSILEN],
2759 [EMAILPRED] COLLATE DATABASE_DEFAULT AS [EMAILPRED],
2760 [DIAREV] COLLATE DATABASE_DEFAULT AS [DIAREV],
2761 [DIAPAGO] COLLATE DATABASE_DEFAULT AS [DIAPAGO],
2762 [CON_CREDITO] COLLATE DATABASE_DEFAULT AS [CON_CREDITO],
2763 [DIASCRED],
2764 [LIMCRED],
2765 [SALDO],
2766 [LISTA_PREC],
2767 [CVE_BITA],
2768 [ULT_PAGOD] COLLATE DATABASE_DEFAULT AS [ULT_PAGOD],
2769 [ULT_PAGOM],
2770 [ULT_PAGOF],
2771 [DESCUENTO],
2772 [ULT_VENTAD] COLLATE DATABASE_DEFAULT AS [ULT_VENTAD],
2773 [ULT_COMPM],
2774 [FCH_ULTCOM],
2775 [VENTAS],
2776 [CVE_VEND] COLLATE DATABASE_DEFAULT AS [CVE_VEND],
2777 [CVE_OBS],
2778 [TIPO_EMPRESA] COLLATE DATABASE_DEFAULT [TIPO_EMPRESA],
2779 [MATRIZ] COLLATE DATABASE_DEFAULT AS [MATRIZ],
2780 [PROSPECTO] COLLATE DATABASE_DEFAULT AS [PROSPECTO],
2781 [CALLE_ENVIO] COLLATE DATABASE_DEFAULT AS [CALLE_ENVIO],
2782 [NUMINT_ENVIO] COLLATE DATABASE_DEFAULT AS [NUMINT_ENVIO],
2783 [NUMEXT_ENVIO] COLLATE DATABASE_DEFAULT AS [NUMEXT_ENVIO],
2784 [CRUZAMIENTOS_ENVIO] COLLATE DATABASE_DEFAULT AS [CRUZAMIENTOS_ENVIO],
2785 [CRUZAMIENTOS_ENVIO2] COLLATE DATABASE_DEFAULT AS [CRUZAMIENTOS_ENVIO2],
2786 [COLONIA_ENVIO] COLLATE DATABASE_DEFAULT AS [COLONIA_ENVIO],
2787 [LOCALIDAD_ENVIO] COLLATE DATABASE_DEFAULT AS [LOCALIDAD_ENVIO],
2788 [MUNICIPIO_ENVIO] COLLATE DATABASE_DEFAULT AS [MUNICIPIO_ENVIO],
2789 [ESTADO_ENVIO] COLLATE DATABASE_DEFAULT AS [ESTADO_ENVIO],
2790 [PAIS_ENVIO] COLLATE DATABASE_DEFAULT AS [PAIS_ENVIO],
2791 [CODIGO_ENVIO] COLLATE DATABASE_DEFAULT AS [CODIGO_ENVIO],
2792 [CVE_ZONA_ENVIO] COLLATE DATABASE_DEFAULT AS [CVE_ZONA_ENVIO],
2793 [REFERENCIA_ENVIO] COLLATE DATABASE_DEFAULT AS [REFERENCIA_ENVIO],
2794 [CUENTA_CONTABLE] COLLATE DATABASE_DEFAULT AS [CUENTA_CONTABLE],
2795 [ADDENDAF] COLLATE DATABASE_DEFAULT AS [ADDENDAF],
2796 [ADDENDAD] COLLATE DATABASE_DEFAULT AS [ADDENDAD],
2797 [NAMESPACE] COLLATE DATABASE_DEFAULT AS [NAMESPACE],
2798 [METODODEPAGO] COLLATE DATABASE_DEFAULT AS [METODODEPAGO],
2799 [NUMCTAPAGO] COLLATE DATABASE_DEFAULT AS [NUMCTAPAGO],
2800 [MODELO] COLLATE DATABASE_DEFAULT AS [MODELO],
2801 [DES_IMPU1] COLLATE DATABASE_DEFAULT AS [DES_IMPU1],
2802 [DES_IMPU2] COLLATE DATABASE_DEFAULT AS [DES_IMPU2],
2803 [DES_IMPU3] COLLATE DATABASE_DEFAULT AS [DES_IMPU3],
2804 [DES_IMPU4] COLLATE DATABASE_DEFAULT AS [DES_IMPU4],
2805 [DES_PER] COLLATE DATABASE_DEFAULT AS [DES_PER],
2806 [LAT_GENERAL],
2807 [LON_GENERAL],
2808 [LAT_ENVIO],
2809 [LON_ENVIO],
2810 [UUID] COLLATE DATABASE_DEFAULT AS [UUID],
2811 [VERSION_SINC],
2812 [USO_CFDI] COLLATE DATABASE_DEFAULT AS [USO_CFDI],
2813 [CVE_PAIS_SAT] COLLATE DATABASE_DEFAULT AS [CVE_PAIS_SAT],
2814 [NUMIDREGFISCAL] COLLATE DATABASE_DEFAULT AS [NUMIDREGFISCAL],
2815 [FORMADEPAGOSAT] COLLATE DATABASE_DEFAULT AS [FORMADEPAGOSAT],
2816 '
2817 + (CONVERT(VARCHAR ,@OWNER_ENTERPRISE_IN_PROCESS))
2818 + ' AS EnterpriseOwnerId
2819 FROM [' + @SAE_DATABASE_NAME_IN_PROCESS + '].['
2820 + @SAE_DATABASE_SCHEMA_IN_PROCESS + '].[CLIE'
2821 + @SAE_IDENTIFIER_NUMBER_IN_PROCESS + ']
2822 ';
2823
2824 SET @CURRENT_PROCESS_NUMBER = @CURRENT_PROCESS_NUMBER + 1;
2825
2826
2827 IF (@CURRENT_PROCESS_NUMBER < @ENTERPRISES_TO_PROCESS)
2828 BEGIN
2829 SET @QUERY_TO_EXECUTE = @QUERY_TO_EXECUTE + '
2830 UNION ALL
2831 ';
2832 END;
2833
2834 -- ---------------------------------------------------------------
2835 -- Delete enterprise processed
2836 -- ---------------------------------------------------------------
2837 DELETE FROM
2838 @ENTERPRICES
2839 WHERE
2840 [ID] = @ENTERPRISE_ID_IN_PROCESS;
2841 END;
2842
2843 -- ---------------------------------------------------------------------------
2844 -- Show built query (UNCOMMENT ONLY IN DEBUG)
2845 -- ---------------------------------------------------------------------------
2846 --PRINT (@QUERY_TO_EXECUTE);
2847
2848 -- ---------------------------------------------------------------------------
2849 -- Execute the query
2850 -- ---------------------------------------------------------------------------
2851 INSERT @CUSTOMER
2852 EXEC (
2853 @QUERY_TO_EXECUTE
2854 );
2855
2856 -- ---------------------------------------------------------------------------
2857 -- Apply results
2858 -- ---------------------------------------------------------------------------
2859 MERGE [dbo].[Customer] AS [CUSTOMER_TARGET]
2860 USING
2861 (
2862 SELECT * FROM @CUSTOMER
2863 ) AS [CUSTOMER_SOURCE]
2864 ON (
2865 [CUSTOMER_TARGET].[CLAVE] = [CUSTOMER_SOURCE].[CLAVE]
2866 AND [CUSTOMER_TARGET].[EnterpriseOwnerId] = [CUSTOMER_SOURCE].[EnterpriseOwnerId]
2867 )
2868 WHEN MATCHED THEN
2869 UPDATE SET
2870 [CUSTOMER_TARGET].[LastUpdate] = GETDATE()
2871 ,[CUSTOMER_TARGET].[LastUpdateBy] = 'IMPORT_PROCESS'
2872 WHEN NOT MATCHED THEN
2873 INSERT
2874 (
2875 [CLAVE]
2876 ,[STATUS]
2877 ,[NOMBRE]
2878 ,[RFC]
2879 ,[CALLE]
2880 ,[NUMINT]
2881 ,[NUMEXT]
2882 ,[CRUZAMIENTOS]
2883 ,[CRUZAMIENTOS2]
2884 ,[COLONIA]
2885 ,[CODIGO]
2886 ,[LOCALIDAD]
2887 ,[MUNICIPIO]
2888 ,[ESTADO]
2889 ,[PAIS]
2890 ,[NACIONALIDAD]
2891 ,[REFERDIR]
2892 ,[TELEFONO]
2893 ,[CLASIFIC]
2894 ,[FAX]
2895 ,[PAG_WEB]
2896 ,[CURP]
2897 ,[CVE_ZONA]
2898 ,[IMPRIR]
2899 ,[MAIL]
2900 ,[NIVELSEC]
2901 ,[ENVIOSILEN]
2902 ,[EMAILPRED]
2903 ,[DIAREV]
2904 ,[DIAPAGO]
2905 ,[CON_CREDITO]
2906 ,[DIASCRED]
2907 ,[LIMCRED]
2908 ,[SALDO]
2909 ,[LISTA_PREC]
2910 ,[CVE_BITA]
2911 ,[ULT_PAGOD]
2912 ,[ULT_PAGOM]
2913 ,[ULT_PAGOF]
2914 ,[DESCUENTO]
2915 ,[ULT_VENTAD]
2916 ,[ULT_COMPM]
2917 ,[FCH_ULTCOM]
2918 ,[VENTAS]
2919 ,[CVE_VEND]
2920 ,[CVE_OBS]
2921 ,[TIPO_EMPRESA]
2922 ,[MATRIZ]
2923 ,[PROSPECTO]
2924 ,[CALLE_ENVIO]
2925 ,[NUMINT_ENVIO]
2926 ,[NUMEXT_ENVIO]
2927 ,[CRUZAMIENTOS_ENVIO]
2928 ,[CRUZAMIENTOS_ENVIO2]
2929 ,[COLONIA_ENVIO]
2930 ,[LOCALIDAD_ENVIO]
2931 ,[MUNICIPIO_ENVIO]
2932 ,[ESTADO_ENVIO]
2933 ,[PAIS_ENVIO]
2934 ,[CODIGO_ENVIO]
2935 ,[CVE_ZONA_ENVIO]
2936 ,[REFERENCIA_ENVIO]
2937 ,[CUENTA_CONTABLE]
2938 ,[ADDENDAF]
2939 ,[ADDENDAD]
2940 ,[NAMESPACE]
2941 ,[METODODEPAGO]
2942 ,[NUMCTAPAGO]
2943 ,[MODELO]
2944 ,[DES_IMPU1]
2945 ,[DES_IMPU2]
2946 ,[DES_IMPU3]
2947 ,[DES_IMPU4]
2948 ,[DES_PER]
2949 ,[LAT_GENERAL]
2950 ,[LON_GENERAL]
2951 ,[LAT_ENVIO]
2952 ,[LON_ENVIO]
2953 ,[UUID]
2954 ,[VERSION_SINC]
2955 ,[USO_CFDI]
2956 ,[CVE_PAIS_SAT]
2957 ,[NUMIDREGFISCAL]
2958 ,[FORMADEPAGOSAT]
2959 ,[EnterpriseOwnerId]
2960 ,[CreatedDate]
2961 ,[LastUpdate]
2962 ,[LastUpdateBy]
2963 )
2964 VALUES (
2965 [CLAVE]
2966 ,[STATUS]
2967 ,[NOMBRE]
2968 ,[RFC]
2969 ,[CALLE]
2970 ,[NUMINT]
2971 ,[NUMEXT]
2972 ,[CRUZAMIENTOS]
2973 ,[CRUZAMIENTOS2]
2974 ,[COLONIA]
2975 ,[CODIGO]
2976 ,[LOCALIDAD]
2977 ,[MUNICIPIO]
2978 ,[ESTADO]
2979 ,[PAIS]
2980 ,[NACIONALIDAD]
2981 ,[REFERDIR]
2982 ,[TELEFONO]
2983 ,[CLASIFIC]
2984 ,[FAX]
2985 ,[PAG_WEB]
2986 ,[CURP]
2987 ,[CVE_ZONA]
2988 ,[IMPRIR]
2989 ,[MAIL]
2990 ,[NIVELSEC]
2991 ,[ENVIOSILEN]
2992 ,[EMAILPRED]
2993 ,[DIAREV]
2994 ,[DIAPAGO]
2995 ,[CON_CREDITO]
2996 ,[DIASCRED]
2997 ,[LIMCRED]
2998 ,[SALDO]
2999 ,[LISTA_PREC]
3000 ,[CVE_BITA]
3001 ,[ULT_PAGOD]
3002 ,[ULT_PAGOM]
3003 ,[ULT_PAGOF]
3004 ,[DESCUENTO]
3005 ,[ULT_VENTAD]
3006 ,[ULT_COMPM]
3007 ,[FCH_ULTCOM]
3008 ,[VENTAS]
3009 ,[CVE_VEND]
3010 ,[CVE_OBS]
3011 ,[TIPO_EMPRESA]
3012 ,[MATRIZ]
3013 ,[PROSPECTO]
3014 ,[CALLE_ENVIO]
3015 ,[NUMINT_ENVIO]
3016 ,[NUMEXT_ENVIO]
3017 ,[CRUZAMIENTOS_ENVIO]
3018 ,[CRUZAMIENTOS_ENVIO2]
3019 ,[COLONIA_ENVIO]
3020 ,[LOCALIDAD_ENVIO]
3021 ,[MUNICIPIO_ENVIO]
3022 ,[ESTADO_ENVIO]
3023 ,[PAIS_ENVIO]
3024 ,[CODIGO_ENVIO]
3025 ,[CVE_ZONA_ENVIO]
3026 ,[REFERENCIA_ENVIO]
3027 ,[CUENTA_CONTABLE]
3028 ,[ADDENDAF]
3029 ,[ADDENDAD]
3030 ,[NAMESPACE]
3031 ,[METODODEPAGO]
3032 ,[NUMCTAPAGO]
3033 ,[MODELO]
3034 ,[DES_IMPU1]
3035 ,[DES_IMPU2]
3036 ,[DES_IMPU3]
3037 ,[DES_IMPU4]
3038 ,[DES_PER]
3039 ,[LAT_GENERAL]
3040 ,[LON_GENERAL]
3041 ,[LAT_ENVIO]
3042 ,[LON_ENVIO]
3043 ,[UUID]
3044 ,[VERSION_SINC]
3045 ,[USO_CFDI]
3046 ,[CVE_PAIS_SAT]
3047 ,[NUMIDREGFISCAL]
3048 ,[FORMADEPAGOSAT]
3049 ,[EnterpriseOwnerId]
3050 ,GETDATE()
3051 ,GETDATE()
3052 ,'IMPORT_PROCESS'
3053 );
3054 END TRY
3055 BEGIN CATCH
3056 DECLARE @ERROR_MESSAGE VARCHAR(MAX) = ERROR_MESSAGE();
3057
3058 EXEC [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG] @ERROR_MESSAGE = @ERROR_MESSAGE , -- varchar(max)
3059 @SOURCE_ERROR = 'BANK_PORTAL_SP_IMPORT_CUSTOMER'; -- varchar(max)
3060
3061 END CATCH;
3062
3063 END;
3064
3065 END;
3066GO
3067PRINT N'Creating [dbo].[BANK_PORTAL_SP_POST_BANK_MOVEMENT_ON_SAE]...';
3068
3069
3070GO
3071-- =============================================
3072-- Autor: diego.as
3073-- Fecha de Creacion: 10/12/2018
3074-- Description: SP que
3075
3076/*
3077-- Ejemplo de Ejecucion:
3078 EXEC [dbo].[BANK_PORTAL_SP_POST_BANK_MOVEMENT_ON_SAE]
3079 @XML_DOCUMENT = '
3080 <BankMovementHeaderDto>
3081 <NUM_REG>7</NUM_REG>
3082 <CVE_CONCEP>COBROD</CVE_CONCEP>
3083 <CON_PART>1</CON_PART>
3084 <NUM_CHEQUE>0</NUM_CHEQUE>
3085 <REF1>RC0623</REF1>
3086 <REF2>82162514</REF2>
3087 <STATUS>R</STATUS>
3088 <FECHA>2018-05-03T00:00:00</FECHA>
3089 <F_COBRO>2018-05-07T00:00:00</F_COBRO>
3090 <BAND_PRN>N</BAND_PRN>
3091 <BAND_CONT>N</BAND_CONT>
3092 <ACT_SAE>S</ACT_SAE>
3093 <NUM_POL> 2</NUM_POL>
3094 <TIP_POL>Dr</TIP_POL>
3095 <SAE_COI>0</SAE_COI>
3096 <MONTO_TOT>395</MONTO_TOT>
3097 <MONTO_IVA_TOT>0</MONTO_IVA_TOT>
3098 <MONTO_EXT>395</MONTO_EXT>
3099 <MONEDA>1</MONEDA>
3100 <T_CAMBIO>1</T_CAMBIO>
3101 <HORA>0</HORA>
3102 <CLPV>40.033.01</CLPV>
3103 <CTA_TRANSF>0</CTA_TRANSF>
3104 <FECHA_LIQ/>
3105 <FECHA_POL>2018-05-16T00:00:00</FECHA_POL>
3106 <CVE_INST>0</CVE_INST>
3107 <TCAMBIOSAE>0</TCAMBIOSAE>
3108 <RFC>682188-K</RFC>
3109 <CONC_SAE>11</CONC_SAE>
3110 <SOLICIT />
3111 <TRANS_COI>31</TRANS_COI>
3112 <INTSAENOI>0</INTSAENOI>
3113 <X_OBSER />
3114 <FACTOR>1</FACTOR>
3115 <FORMAPAGO>4</FORMAPAGO>
3116 <ANOMBREDE>JUAN ELADIO GOMEZ</ANOMBREDE>
3117 <ASOCIADO>C</ASOCIADO>
3118 <CTA_CONTAB_ASOC>1.01.02.01.001</CTA_CONTAB_ASOC>
3119 <REVISADO>0</REVISADO>
3120 <PRIORIDAD />
3121 <DOC_ASOC>0</DOC_ASOC>
3122 <RESALTAR>0</RESALTAR>
3123 <ANTICIPO>0</ANTICIPO>
3124 <IDTRANSF>0</IDTRANSF>
3125 <SUCURSAL />
3126 <CUENTA />
3127 <CLABE />
3128 <MULTI_CLPV>0</MULTI_CLPV>
3129 <CVE_BANCO_ASOC />
3130 <NUM_CONC_AUTO>0</NUM_CONC_AUTO>
3131 <COI_DEPTO>0</COI_DEPTO>
3132 <COI_CCOSTOS>0</COI_CCOSTOS>
3133 <COI_PROYECTO>0</COI_PROYECTO>
3134 <EnterpriseOwnerId>2</EnterpriseOwnerId>
3135 <AccountNumber>371-401124-2</AccountNumber>
3136 <IsPostedOnSae>0</IsPostedOnSae>
3137 <DateTimeOfPostingOnSae/>
3138 <CreatedDate>2018-10-03T23:18:37.05</CreatedDate>
3139 <LastUpdate>2018-10-04T01:26:35.367</LastUpdate>
3140 <LastUpdateBy>IMPORT_PROCESS</LastUpdateBy>
3141 <BankMovementDetails>
3142 <BankMovementDetailDto>
3143 <NUM_REGP>7</NUM_REGP>
3144 <NUM_MOV>7</NUM_MOV>
3145 <CVE_CONCEP>COBROE</CVE_CONCEP>
3146 <REFERENCIA>A00546</REFERENCIA>
3147 <MONTO_IVA>0</MONTO_IVA>
3148 <FACT>A00546</FACT>
3149 <MONTO_DOC>395</MONTO_DOC>
3150 <MONTO_EXT>395</MONTO_EXT>
3151 <ORDEN>1</ORDEN>
3152 <X_OBSER />
3153 <NUMCARGO>1</NUMCARGO>
3154 <NUMCPTOPADRE>1</NUMCPTOPADRE>
3155 <TIPOCAMBIOSAE>1</TIPOCAMBIOSAE>
3156 <TIPOCAMBIOBANCO>1</TIPOCAMBIOBANCO>
3157 <DOCTO>RC0623</DOCTO>
3158 <NO_PARTIDASAE>1</NO_PARTIDASAE>
3159 <MONEDADOC>1</MONEDADOC>
3160 <PART_CLPV>40.033.01</PART_CLPV>
3161 <PART_CLPV_RFC />
3162 <PART_ANOMBREDE>JUAN ELADIO GOMEZ</PART_ANOMBREDE>
3163 <IVA_MC>0</IVA_MC>
3164 <PART_CTA_CONTAB_ASOC>1.01.02.01.001</PART_CTA_CONTAB_ASOC>
3165 <PART_CONCEPSAE>10</PART_CONCEPSAE>
3166 <PART_ANTICIPO>0</PART_ANTICIPO>
3167 <PART_CTA_BANCO_ASOC />
3168 <PART_CVE_BANCO_ASOC />
3169 <EnterpriseOwnerId>2</EnterpriseOwnerId>
3170 <AccountNumber>371-401124-2</AccountNumber>
3171 <CreatedDate>2018-10-06T12:40:39.833</CreatedDate>
3172 <LastUpdate>2018-10-06T12:40:39.833</LastUpdate>
3173 <LastUpdateBy>IMPORT_PROCESS</LastUpdateBy>
3174 </BankMovementDetailDto>
3175 </BankMovementDetails>
3176 </BankMovementHeaderDto>
3177 '
3178*/
3179-- =============================================
3180CREATE PROCEDURE [dbo].[BANK_PORTAL_SP_POST_BANK_MOVEMENT_ON_SAE] (@XML_DOCUMENT XML)
3181AS
3182 BEGIN
3183 BEGIN TRY
3184 SET NOCOUNT ON;
3185
3186 DECLARE
3187 @BANK_ACCOUNT_NUMBER VARCHAR(100)
3188 ,@OWNER_ENTERPRISE_ID INT
3189 ,@DOC_REFERENCE VARCHAR(20)
3190 ,@DOC_NUM_REG INT
3191 ,@DOC_DATE DATE
3192 ,@PAY_DOC_DATE DATE
3193 ,@EXCHANGE_RATE INT;
3194
3195 DECLARE
3196 @SAE_DATABASE_NAME VARCHAR(100)
3197 ,@SAE_DATABASE_SCHEMA VARCHAR(100)
3198 ,@SAE_IDENTIFIER_NUMBER VARCHAR(50)
3199 ,@QUERY VARCHAR(MAX);
3200
3201 CREATE TABLE [#BANK_MOVEMENT_DETAIL]
3202 (
3203 [Id] INT IDENTITY(1 ,1)
3204 PRIMARY KEY
3205 ,[NUM_REGP] [INT] NOT NULL
3206 ,[NUM_MOV] [INT] NOT NULL
3207 ,[CVE_CONCEP] [VARCHAR](6) NULL
3208 ,[REFERENCIA] [VARCHAR](20) NULL
3209 ,[MONTO_IVA] [FLOAT] NULL
3210 DEFAULT ((0))
3211 ,[FACT] [VARCHAR](20) NULL
3212 ,[MONTO_DOC] [FLOAT] NOT NULL
3213 ,[MONTO_EXT] [FLOAT] NULL
3214 DEFAULT ((0))
3215 ,[ORDEN] [INT] NOT NULL
3216 ,[X_OBSER] [VARCHAR](255) NULL
3217 ,[NUMCARGO] [INT] NULL
3218 DEFAULT ((0))
3219 ,[NUMCPTOPADRE] [INT] NULL
3220 DEFAULT ((0))
3221 ,[TIPOCAMBIOSAE] [FLOAT] NULL
3222 DEFAULT ((0))
3223 ,[TIPOCAMBIOBANCO] [FLOAT] NULL
3224 DEFAULT ((0))
3225 ,[DOCTO] [VARCHAR](20) NULL
3226 ,[NO_PARTIDASAE] [INT] NULL
3227 DEFAULT ((0))
3228 ,[MONEDADOC] [INT] NULL
3229 DEFAULT ((1))
3230 ,[PART_CLPV] [VARCHAR](10) NULL
3231 ,[PART_CLPV_RFC] [VARCHAR](13) NULL
3232 ,[PART_ANOMBREDE] [VARCHAR](120) NULL
3233 ,[IVA_MC] [FLOAT] NULL
3234 DEFAULT ((0))
3235 ,[PART_CTA_CONTAB_ASOC] [VARCHAR](40) NULL
3236 ,[PART_CONCEPSAE] [INT] NULL
3237 DEFAULT ((0))
3238 ,[PART_ANTICIPO] [INT] NULL
3239 DEFAULT ((0))
3240 ,[PART_CTA_BANCO_ASOC] [VARCHAR](50) NULL
3241 ,[PART_CVE_BANCO_ASOC] [VARCHAR](6) NULL
3242 ,[EnterpriseOwnerId] [INT] NOT NULL
3243 ,[AccountNumber] [VARCHAR](100) NOT NULL
3244 ,[CreatedDate] [DATETIME] NULL
3245 DEFAULT (GETDATE())
3246 ,[LastUpdate] [DATETIME] NULL
3247 DEFAULT (GETDATE())
3248 ,[LastUpdateBy] [VARCHAR](100) NULL
3249 );
3250
3251 -- ------------------------------------------------------------------------------------
3252 -- GET BASIC INFO FROM XML
3253 -- ------------------------------------------------------------------------------------
3254 SELECT
3255 @BANK_ACCOUNT_NUMBER = [x].[Rec].[query]('./AccountNumber').[value]('.' ,
3256 'varchar(100)')
3257 ,@OWNER_ENTERPRISE_ID = [x].[Rec].[query]('./EnterpriseOwnerId').[value]('.' ,
3258 'int')
3259 ,@DOC_REFERENCE = [x].[Rec].[query]('./REF1').[value]('.' ,
3260 'varchar(20)')
3261 ,@DOC_NUM_REG = [x].[Rec].[query]('./NUM_REG').[value]('.' ,
3262 'int')
3263 FROM
3264 @XML_DOCUMENT.[nodes]('/BankMovementHeaderDto') AS [x] ([Rec]);
3265
3266
3267 -- ------------------------------------------------------------------------------------
3268 -- Get Movement Details
3269 -- ------------------------------------------------------------------------------------
3270 INSERT INTO [#BANK_MOVEMENT_DETAIL]
3271 SELECT
3272 [x].[Rec].[query]('./NUM_REGP').[value]('.' ,'INT')
3273 ,[x].[Rec].[query]('./NUM_MOV').[value]('.' ,'int')
3274 ,[x].[Rec].[query]('./CVE_CONCEP').[value]('.' ,'varchar(6)')
3275 ,[x].[Rec].[query]('./REFERENCIA').[value]('.' ,'VARCHAR(20)')
3276 ,[x].[Rec].[query]('./MONTO_IVA').[value]('.' ,'FLOAT')
3277 ,[x].[Rec].[query]('./FACT').[value]('.' ,'VARCHAR(20)')
3278 ,[x].[Rec].[query]('./MONTO_DOC').[value]('.' ,'FLOAT')
3279 ,[x].[Rec].[query]('./MONTO_EXT').[value]('.' ,'FLOAT')
3280 ,[x].[Rec].[query]('./ORDEN').[value]('.' ,'int')
3281 ,[x].[Rec].[query]('./X_OBSER').[value]('.' ,'VARCHAR(255)')
3282 ,[x].[Rec].[query]('./NUMCARGO').[value]('.' ,'int')
3283 ,[x].[Rec].[query]('./NUMCPTOPADRE').[value]('.' ,'int')
3284 ,[x].[Rec].[query]('./TIPOCAMBIOSAE').[value]('.' ,'FLOAT')
3285 ,[x].[Rec].[query]('./TIPOCAMBIOBANCO').[value]('.' ,'FLOAT')
3286 ,[x].[Rec].[query]('./DOCTO').[value]('.' ,'VARCHAR(20)')
3287 ,[x].[Rec].[query]('./NO_PARTIDASAE').[value]('.' ,'int')
3288 ,[x].[Rec].[query]('./MONEDADOC').[value]('.' ,'int')
3289 ,[x].[Rec].[query]('./PART_CLPV').[value]('.' ,'VARCHAR(10)')
3290 ,[x].[Rec].[query]('./PART_CLPV_RFC').[value]('.' ,'VARCHAR(13)')
3291 ,[x].[Rec].[query]('./PART_ANOMBREDE').[value]('.' ,
3292 'VARCHAR(120)')
3293 ,[x].[Rec].[query]('./IVA_MC').[value]('.' ,'FLOAT')
3294 ,[x].[Rec].[query]('./PART_CTA_CONTAB_ASOC').[value]('.' ,
3295 'VARCHAR(40)')
3296 ,[x].[Rec].[query]('./PART_CONCEPSAE').[value]('.' ,'int')
3297 ,[x].[Rec].[query]('./PART_ANTICIPO').[value]('.' ,'int')
3298 ,[x].[Rec].[query]('./PART_CTA_BANCO_ASOC').[value]('.' ,
3299 'VARCHAR(50)')
3300 ,[x].[Rec].[query]('./PART_CVE_BANCO_ASOC').[value]('.' ,
3301 'VARCHAR(6)')
3302 ,[x].[Rec].[query]('./EnterpriseOwnerId').[value]('.' ,'int')
3303 ,[x].[Rec].[query]('./AccountNumber').[value]('.' ,
3304 'VARCHAR(100)')
3305 ,[x].[Rec].[query]('./CreatedDate').[value]('.' ,'datetime')
3306 ,[x].[Rec].[query]('./LastUpdate').[value]('.' ,'datetime')
3307 ,[x].[Rec].[query]('./LastUpdateBy').[value]('.' ,'VARCHAR(100)')
3308 FROM
3309 @XML_DOCUMENT.[nodes]('/BankMovementHeaderDto/BankMovementDetails/BankMovementDetailDto')
3310 AS [x] ([Rec]);
3311
3312 -- ------------------------------------------------------------------------------------
3313 -- GETS ENTERPRISE INFO
3314 -- ------------------------------------------------------------------------------------
3315 SELECT
3316 @SAE_DATABASE_NAME = [SaeDataBaseName]
3317 ,@SAE_DATABASE_SCHEMA = [SaeDataBaseSchema]
3318 ,@SAE_IDENTIFIER_NUMBER = [SaeIdentifierNumber]
3319 FROM
3320 [dbo].[Enterprise]
3321 WHERE
3322 [Id] = @OWNER_ENTERPRISE_ID;
3323
3324 -- ------------------------------------------------------------------------------------
3325 -- Make query
3326 -- ------------------------------------------------------------------------------------
3327 DECLARE
3328 @PARAM_DEFINITION NVARCHAR(MAX)
3329 ,@DYNAMIC_QUERY NVARCHAR(MAX);
3330
3331 SET @DYNAMIC_QUERY = N'
3332
3333 INSERT INTO [' + @SAE_DATABASE_NAME + '].['
3334 + @SAE_DATABASE_SCHEMA + '].[CUEN_DET' + @SAE_IDENTIFIER_NUMBER
3335 + ']
3336 SELECT
3337 [BMD].[PART_CLPV] -- CVE_CLIE - varchar(10) ------------ codigo del cliente
3338 ,[BMD].[FACT] -- REFER - varchar(20) ------------ numero de factura
3339 ,CAST(1 AS VARCHAR) -- ID_MOV - int ------------ siempre 1
3340 ,CAST(10 AS VARCHAR) -- NUM_CPTO - int ------------ codigo concepto
3341 ,CAST(1 AS VARCHAR) -- NUM_CARGO - int ------------ siempre 1
3342 ,CAST(0 AS VARCHAR) -- CVE_OBS - int ------------ siempre 0
3343 ,[BMD].[FACT] -- NO_FACTURA - varchar(20) ------------ numero de factura
3344 ,[BMD].[REFERENCIA] -- DOCTO - varchar(20) ------------ REFERENCIA
3345 ,CAST([BMD].[MONTO_DOC] AS VARCHAR) -- IMPORTE - float ------------ valor del documento
3346 ,CAST(GETDATE() AS VARCHAR) -- FECHA_APLI - datetime ------------ fecha de cuando registramos el movimiento
3347 ,CAST(GETDATE() AS VARCHAR) -- FECHA_VENC - datetime ------------ fecha de cuando registramos el movimiento
3348 ,CAST(NULL AS VARCHAR(1)) -- AFEC_COI - varchar(1) ------------ siempre null
3349 ,'''' -- STRCVEVEND - varchar(5) ------------ siempre en blanco
3350 ,CAST([BMD].[MONEDADOC] AS VARCHAR) -- NUM_MONED - int ------------ 1 quetzales | 2 dolares ------ moneda del documento bancario
3351 ,CAST(0 AS VARCHAR) -- TCAMBIO - float ------------ si es dolares colocar el tipo de cambio
3352 ,CAST([BMD].[MONTO_EXT] AS VARCHAR) -- IMPMON_EXT - float ------------ valor en dolares ----- (posiblemente monto del documento)
3353 ,CAST(GETDATE() AS VARCHAR) -- FECHAELAB - datetime ------------ fecha de elaboracion del registro
3354 ,CAST(0 AS VARCHAR) -- CTLPOL - int ------------ siempre 0
3355 ,'''' -- CVE_FOLIO - varchar(9) ------------ en blanco
3356 ,''A'' -- TIPO_MOV - varchar(1) ------------ siempre A
3357 ,CAST(NULL AS VARCHAR) -- CVE_BITA - int ------------ siempre null
3358 ,CAST(-1 AS VARCHAR) -- SIGNO - int ------------ SIEMPRE -1 PORQUE ESTA RESTANDO
3359 ,CAST(0 AS VARCHAR) -- CVE_AUT - int ------------ SIEMPRE 0
3360 ,CAST(NULL AS VARCHAR) -- USUARIO - smallint ------------ SIEMPRE NULL
3361 ,CAST(NULL AS VARCHAR(13)) -- OPERACIONPL - varchar(13) ----------- SIEMPRE NULL
3362 ,CAST(NULL AS VARCHAR(1)) -- REF_SIST - varchar(1) ------------ SIEMPRE NULL
3363 ,CAST(1 AS VARCHAR) -- NO_PARTIDA - int ------------ SIEMPRE 1
3364 ,CAST(NULL AS VARCHAR(3)) -- REFBANCO_ORIGEN - varchar(3) -------- SIEMPRE NULL
3365 ,CAST(NULL AS VARCHAR(3)) -- REFBANCO_DEST - varchar(3) ---------- SIEMPRE NULL
3366 ,CAST(NULL AS VARCHAR(16)) -- NUMCTAPAGO_ORIGEN - varchar(16) ----- SIEMPRE NULL
3367 ,CAST(NULL AS VARCHAR(16)) -- NUMCTAPAGO_DESTINO - varchar(16) ---- SIEMPRE NULL
3368 ,CAST(NULL AS VARCHAR(20)) -- NUMCHEQUE - varchar(20) ------------ SIEMPRE NULL
3369 ,CAST(NULL AS VARCHAR(60)) -- BENEFICIARIO - varchar(60) ---------- SIEMPRE NULL
3370 ,'''' -- UUID - varchar(50) ------------ SIEMPRE VACIO
3371 ,CAST(GETDATE() AS VARCHAR) -- VERSION_SINC - datetime ------------ FECHA Y HORA DEL REGISTRO
3372 ,CAST(NULL AS VARCHAR(30)) -- ID_OPERACION - varchar(30) ---------- SIEMPRE NULL
3373 ,CAST(NULL AS VARCHAR(20)) -- CVE_DOC_COMPPAGO - varchar(20) ------ SIEMPRE NULL
3374 FROM
3375 [#BANK_MOVEMENT_DETAIL] AS [BMD];
3376
3377 ';
3378
3379 -- ------------------------------------------------------------------------------------
3380 -- Uncomment this line only on DEBUG
3381 -- ------------------------------------------------------------------------------------
3382 --PRINT (@DYNAMIC_QUERY);
3383
3384 -- ------------------------------------------------------------------------------------
3385 -- Execute query
3386 -- ------------------------------------------------------------------------------------
3387 EXECUTE [sp_executesql] @DYNAMIC_QUERY;
3388
3389 --
3390 SELECT
3391 1 AS [Resultado]
3392 ,'Proceso Exitoso' [Mensaje]
3393 ,0 [Codigo]
3394 ,CAST(0 AS VARCHAR) [DbData];
3395 END TRY
3396 BEGIN CATCH
3397 DECLARE @ERROR VARCHAR(MAX) = ERROR_MESSAGE();
3398
3399 EXEC [dbo].[BANK_PORTAL_SP_ADD_ERROR_LOG] @ERROR_MESSAGE = @ERROR , -- varchar(max)
3400 @SOURCE_ERROR = 'BANK_PORTAL_SP_POST_BANK_MOVEMENT_ON_SAE'; -- varchar(max)
3401
3402 SELECT
3403 -1 AS [Resultado]
3404 ,ERROR_MESSAGE() [Mensaje]
3405 ,@@ERROR [Codigo];
3406 END CATCH;
3407 END;
3408GO
3409PRINT N'Creating [dbo].[BANK_PORTAL_UPDATE_LAST_LOGIN_OF_USER]...';
3410
3411
3412GO
3413-- =============================================
3414-- Autor: diego.as
3415-- Fecha de Creacion: 10/7/2018
3416-- Description: SP que actualiza la fecha y hora del ultimo inicio de sesion de un usuario
3417
3418/*
3419-- Ejemplo de Ejecucion:
3420 EXEC [dbo].[BANK_PORTAL_UPDATE_LAST_LOGIN_OF_USER]
3421 @USER_ID = 10
3422 --
3423 SELECT * FROM [dbo].[User]
3424
3425*/
3426-- =============================================
3427CREATE PROCEDURE [dbo].[BANK_PORTAL_UPDATE_LAST_LOGIN_OF_USER] (@USER_ID INT)
3428AS
3429 BEGIN
3430 BEGIN TRY
3431 UPDATE
3432 [dbo].[User]
3433 SET
3434 [LastLoginDate] = GETDATE()
3435 WHERE
3436 [Id] = @USER_ID;
3437 --
3438 END TRY
3439 BEGIN CATCH
3440 DECLARE @ERROR VARCHAR(MAX) = ERROR_MESSAGE();
3441 RAISERROR(@ERROR,16,1);
3442 END CATCH;
3443 END;
3444GO
3445DECLARE @VarDecimalSupported AS BIT;
3446
3447SELECT @VarDecimalSupported = 0;
3448
3449IF ((ServerProperty(N'EngineEdition') = 3)
3450 AND (((@@microsoftversion / power(2, 24) = 9)
3451 AND (@@microsoftversion & 0xffff >= 3024))
3452 OR ((@@microsoftversion / power(2, 24) = 10)
3453 AND (@@microsoftversion & 0xffff >= 1600))))
3454 SELECT @VarDecimalSupported = 1;
3455
3456IF (@VarDecimalSupported > 0)
3457 BEGIN
3458 EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
3459 END
3460
3461
3462GO
3463PRINT N'Update complete.';
3464
3465
3466GO