Procediment emmagatzemat per transferir factures desde APM a les taules intermitges que utilitza SAP:
USE [apmtosap]
GO
/****** Object: StoredProcedure [dbo].[apmInfoToSAP] Script Date: 14/03/2019 15:04:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[apmInfoToSAP]
AS
BEGIN
-- Traspàs de dades APM a SAP
--
-- Autor: Sergi Zaragoza
-- Modificacions: David Marti
--
-- Registre de canvis:
-- 25/01/2018 S'afegeix el pais a les adreces (SII) i es crea la taula INTRX_PaisosZones pel seu tractament
-- 25/01/2018 Treure els espais en blanc dels codis (articles, vies de pagament, condicions ...)
-- 25/01/2018 Arreglar el procés de les comptes bancaries de clients (esta mal particionat i no posa les dades correctes als camps)
-- 25/01/2018 S'Esborra la taula IVAAPMtoSAP, no fa res.
-- 25/01/2018 S'Esborra el procediment apmBusinessPartsToSAP
-- 25/01/2018 S'ha creat l'usuari SQLServer intarex per utilitzar al programa de traspàs de dades
-- 26/01/2018 Esborrar totes les dades que no hagin estat marcades com processades (BusinessPartners, Documents ...) així es poden actualitzar correctament.
-- 26/01/2018 Arreglar els conceptes de vies de pago i condicions de pago, estan al revés.
-- Canviar les dades dels camps PayTermsGrpCode <-> PeymentMethodCode
-- Ampliar el camp PeymentMethodCode a 20
-- 26/01/2018 Crear taula INTRX_FormaPagoSAP per solucionar el problema de les vies de pago (pagament/cobrament)
-- Aquesta taula la usa internament el programa de traspàs de dades i no hi ha referencia en aquest procedimient
-- 26/01/2018 Es suprimeixen de tots els NIF els espais en blanc i el guionet.
-- 27/01/2018 Els grups impositius EI (IVA comunitari) i EX (IVA exportació) estan al revés
-- 27/01/2018 Es renombra aquest procediment de apmInfoToBusinessPartners a apmInfoToSAP
-- 29/01/2018 Els venciments en divises tornen el valor en moneda base (EUR) i no en divisa
-- 29/01/2018 No se quin es el motiu, però hi ha venciments que venen duplicats o triplicats (factura de compra 01000001 per exemple, amb 3 venciments)
-- Al programa que ho importa ja ho tracto (select distinct)
-- 29/01/2018 L'indicador per que un IC utilitzi totes les monedes no es el ###, es el ##
-- 29/01/2018 Hi ha clients amb NIF malformat degut al doble codi de pais (C002230 PLPL7331003763)
-- 29/01/2018 Hi ha factures de vendes sense línies, doncs l'albarà es de 2017 i la factura del 2018
-- 30/01/2018 Canviar de nom els camps de la taula INTRX_FormaPagoSAP i utilitzar-la directament en aquest procediment
-- 30/01/2018 Les factures de compres de la CEE porten el total amb IVA quan no l'hauríen de portar
-- (el grup impositiu ja indica que s'ha de crear una autoliquidació)
-- 30/01/2018 Els venciments, en algunes factures, estan agafant dades d'altres anys i això fa que no
-- quadri els total de venciments amb el total de la factura i no es traspassi (Exemple factura compra 01000011)
-- Per arreglar-ho afegeixo la condició situacion='C' als venciments de les factures de compra.
-- 31/01/2018 S'havia assumit de forma errónea que totes les factures de compra teníen albarans associats,
-- Però no es així, hi ha factures de compra SENSE albarans i aquestes no sortíen a la apmtosap.
-- 31/01/2018 S'afegeix una taula de mapeig INTRX_MapeigPC per les comptes comptables
-- 31/01/2018 S'omplen correctament els camps DebitorAccount de la BusinessPartners i AccountCode de la Document_Lines
-- 01/02/2018 Els proveïdors només tenen IBAN (en la majoria dels casos) a APM, ho tractarem al procés d'importació cap a SAP
-- S'ignora el contingut de la taula BPBankAccounts a la importació de IC, doncs cada IC només te un banc i aquesta
-- informació ja ve a la BusinessPartner.
-- 01/02/2018 S'esborra la taula BPBankAccounts.
-- 01/02/2018 Tant per clients com per proveïdors es passa la compta sensera a DefaultAccount de BusinessPartners.
-- Als clients es una CCC i ha proveïdors un IBAN (normalment, també he vist alguna CCC)
-- 02/02/2018 S'havia assumit erroneament que les comptes comptables de vendes estaven a la familia d'articles.
-- No es així, agafarem la compta comptable de vendes a partir del tipus d'iva (Normal/Comunitari/Exent)
-- Tambe diferenciem els articles que porten cost de ports a la compta 705002
-- Com que son pocs (7) ZZxxxx els posem estàtics al codi.
-- 02/02/2018 Al camp DocType dels documents hi posem el tipus d'iva.
-- 02/02/2018 La diferencia entre factures i abonaments de venda no la fem per tipus de factura (6,7,8,9 = abonament)
-- La fem mirant el total_factura, doncs he vist una factura en negatiu i entenc que hauría de ser un abonament.
-- 05/02/2018 Arreglar els codis de pais que no estan normalitzats a APM
-- Inclou NIFS, Adreces.
-- 08/02/2018 Deshabilitem el mapeig de comptes de compres, doncs entenem que ja estan entrant les comptes correctes al APM
-- 20/02/2018 La taula d'intercanvi INTRX_PaisosZones només em servía per posar inicialment el pais de intrastat als clients/proveidors
-- A partir d'ara només la faig servir per trobar el codi ISO correcte, doncs hi han clients/proveidors que no es així.
-- 23/04/2018 A partir del 01/05/2018 tots els abonaments de factures de venda es passen a la compta 708000
-- 04/06/2018 Contactes
-- 05/06/2018 He afegit un parell de disparadors a la Vidalb clientes i proveedores per controlar les modificacions de dades desde APM
-- 14/01/2019 He canviat les taules del 2017 a 2018 i les del 2018 al 2019 per que es puguin importar les noves factures
-- 24/01/2019 Millores en la selecció de dades.
-- 24/01/2019 He canviat com es crea el numero de la factura de compra a traspassar a SAP.
-- Ara te en compte l'any de la data de registre:
-- RIGHT(YEAR(regiva.DATA_REGISTRE),2)+(regiva.SERIE)+(regiva.REGISTRE) -> 1902000012
-- 04/03/2019 Arreglada la posibilitat de que dues o mes factures de compres puguin tenir un mateix número
-- 04/03/2019 Afegir que el article Z0005 vagi a parar la compta 705001
-- 14/03/2019 Arreglat el error que duplicava les factures de compra, doncs no tenia en compta que el h_facturas_c20xx porta info dels albarans
DECLARE @inici as DateTime = GETDATE();
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Inici del traspàs de APM a SAP ...';
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + '[BP] Esborrar no traspassats (es tornaràn a traspassar) ...';
-- Només esborrem els clients/proveidors que no estiguin traspassats
DELETE [apmtosap].[dbo].[BusinessPartners] WHERE (Processed='N' or Processed is Null);
-- Clients
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + '[BP] Clients ...';
INSERT INTO [apmtosap].[dbo].[BusinessPartners]
([DateTime]
,[Processed]
,[CardCode]
,[CardType]
,[CardName]
,[CardForeignName]
,[SinglePayment]
,[Phone1]
,[Fax]
,[Cellular]
,[EmailAddress]
,[Website]
,[ContactPerson]
,[Notes]
,[BilltoDefault]
,[ShipToDefault]
,[SalesPersonCode]
,[PayTermsGrpCode]
,[PeymentMethodCode]
,[CreditLimit]
,[MaxCommitment]
,[PriceListNum]
,[Currency]
,[BankCountry]
,[DefaultBankCode]
,[DefaultBranch]
,[DefaultAccount]
,[HouseBankCountry]
,[HouseBank]
,[HouseBankBranch]
,[HouseBankAccount]
,[VatLiable]
,[Equalitzation]
,[FederalTaxID]
,[ResidentNumber]
,[DebitorAccount]
,[LanguageCode]
,[SubjectToWithholdingTax]
,[WTCode]
)
SELECT GETDATE()
, 'N' procesado
, 'C0' + cli.cliente CardCode
, 'C' CardType
, cli.n_fiscal CardName
, cli.nombre CardForeignName
, 'Y' SinglePayment
, cli.telefono Phone1
, cli.fax Fax
, mobil_c1 Cellular
, cli.email EmailAddress
, cli.web Website
, null ContactPerson
, SUBSTRING(cli.notas, 1,100) Notes
, cli.n_fiscal BilltoDefault
, cli.nombre ShipToDefault
, SUBSTRING(ISNULL(cli.representante + '-' + rep.nombre,''),1,30) SalesPersonCode
, fpag.PayTermsGrpCode
, sapfpag.PayMethodCodeCobro
, cli.riesgo_con CreditLimit
, cli.riesgo_con MaxCommitment
, 1 PriceListNum
, '##' Currency
, pz.paisISO BankCountry
, Null DefaultBank
, Null DefaultBrach
, case when LEN(cli.ccc_1) = 23 then replace(replace(ccc_1,'-',''),' ','')
when LEN(cli.ccc_2) = 23 then replace(replace(ccc_2,'-',''),' ','')
when LEN(cli.ccc_3) = 23 then replace(replace(ccc_3,'-',''),' ','')
else null end DefaultAccount
, Null HouseBankCountry
, Null HouseBank
, Null HouseBankBranch
, Null HouseBankAccount
, case when tfac.clase_iva IN ('N') then 'Y'
when tfac.clase_iva IN ('C') then 'E'
when tfac.clase_iva IN ('E','X') then 'N'
end
, 'N' Equalitzation
, REPLACE(REPLACE(case when ISNULL(cli.destino_intras, 'ES') = 'ES' then LTRIM(RTRIM(pz.paisISO COLLATE Modern_Spanish_CI_AS + cli.cif))
else RTRIM(LTRIM(cli.cif)) end,' ',''),'-','') FederalTaxID
, case when tfac.clase_iva IN ('N') then '1'
when tfac.clase_iva IN ('C') then '2'
when tfac.clase_iva IN ('E','X') then '6'
end ResidentNumber
, mpc.ComptaSAP DebitorAccount
, CASE WHEN cli.idioma = 'L' then '3'
WHEN cli.idioma = 'A' then '3'
WHEN cli.idioma = 'C' then '23'
WHEN cli.idioma = 'D' then '3'
WHEN cli.idioma = 'E' then '23'
WHEN cli.idioma = 'F' then '22'
WHEN cli.idioma = 'G' then '23'
WHEN cli.idioma = 'H' then '3'
WHEN cli.idioma = 'I' then '13'
WHEN cli.idioma = 'N' then '13'
WHEN cli.idioma = 'P' then '19'
WHEN cli.idioma = 'R' then '3'
WHEN cli.idioma = 'S' then '3'
WHEN cli.idioma = 'X' then '3' end LanguageCode
, 'N' SubjectToWithholdingTax
, '0' WTCode
FROM vidalb.dbo.clientes cli
INNER JOIN vidalb.dbo.facturas tfac ON tfac.factura = cli.t_factura
INNER JOIN FormaPagoAPMtoSAP fpag ON cli.forma = fpag.FormaPagoAPM COLLATE Modern_Spanish_CI_AS
INNER JOIN vidalb.dbo.representantes rep ON rep.representante = cli.representante
LEFT JOIN BusinessPartners sapcli ON sapcli.CardCode = 'C0' + cli.cliente COLLATE Modern_Spanish_CI_AS
LEFT JOIN INTRX_FormaPagoSAP sapfpag ON sapfpag.PeymentMethodCode=fpag.PeymentMethodCode
LEFT JOIN INTRX_MapeigPC mpc ON mpc.ComptaAPM=cli.pref_com COLLATE Modern_Spanish_CI_AS
LEFT JOIN INTRX_PaisosZones pz ON pz.pais=cli.destino_intras
WHERE sapcli.CardCode IS NULL
-- Proveidors
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + '[BP] Proveïdors ...';
INSERT INTO [apmtosap].[dbo].[BusinessPartners]
([DateTime]
,[Processed]
,[CardCode]
,[CardType]
,[CardName]
,[CardForeignName]
,[SinglePayment]
,[Phone1]
,[Fax]
,[Cellular]
,[EmailAddress]
,[Website]
,[ContactPerson]
,[Notes]
,[BilltoDefault]
,[ShipToDefault]
,[SalesPersonCode]
,[PayTermsGrpCode]
,[PeymentMethodCode]
,[CreditLimit]
,[MaxCommitment]
,[PriceListNum]
,[Currency]
,[BankCountry]
,[DefaultBankCode]
,[DefaultBranch]
,[DefaultAccount]
,[HouseBankCountry]
,[HouseBank]
,[HouseBankBranch]
,[HouseBankAccount]
,[VatLiable]
,[Equalitzation]
,[FederalTaxID]
,[ResidentNumber]
,[DebitorAccount]
,[LanguageCode]
,[SubjectToWithholdingTax]
,[WTCode]
)
SELECT GETDATE()
, 'N' procesado
, 'P' + prov.proveedor CardCode
, 'S' CardType
, prov.n_fiscal CardName
, prov.nombre CardForeignName
, 'Y' SinglePayment
, telefono Phone1
, prov.fax Fax
, mobil_c1 Cellular
, email EmailAddress
, www Website
, null ContactPerson
, SUBSTRING(notas, 1,100) Notes
, prov.n_fiscal BilltoDefault
, prov.nombre ShipToDefault
, null SalesPersonCode
, fpag.PayTermsGrpCode
, sapfpag.PayMethodCodePago
, null CreditLimit
, null MaxCommitment
, 1 PriceListNum
, '##' Currency
, pz.PaisISO BankCountry
, Null DefaultBankCode
, Null DefaultBranch
, REPLACE(REPLACE(ccc_1,'-',''),' ','') DefaultAccount
, Null HouseBankCountry
, Null HouseBank
, Null HouseBankBranch
, Null HouseBankAccount
, case when prov.rec_equi IN ('1') then 'Y'
when prov.rec_equi IN ('4') then 'E'
when prov.rec_equi IN ('6') then 'N'
else 'Y'
end VatLiable
, 'N' Equalitzation
, REPLACE(REPLACE(case when ISNULL(prov.pais_orig, 'ES') = 'ES'
then ISNULL(RTRIM(pz.PaisISO COLLATE Modern_Spanish_CI_AS), 'ES') + prov.cif_dni
else prov.cif_dni end,' ',''),'-','') FederalTaxID
, case when prov.rec_equi IN ('1') then '1'
when prov.rec_equi IN ('4') then '2'
when prov.rec_equi IN ('6') then '6'
else '1'
end ResidentNumber
, mpc.ComptaSAP DebitorAccount
, CASE WHEN prov.idioma = 'L' then '3'
WHEN prov.idioma = 'A' then '3'
WHEN prov.idioma = 'C' then '23'
WHEN prov.idioma = 'D' then '3'
WHEN prov.idioma = 'E' then '23'
WHEN prov.idioma = 'F' then '22'
WHEN prov.idioma = 'G' then '23'
WHEN prov.idioma = 'H' then '3'
WHEN prov.idioma = 'I' then '13'
WHEN prov.idioma = 'N' then '13'
WHEN prov.idioma = 'P' then '19'
WHEN prov.idioma = 'R' then '3'
WHEN prov.idioma = 'S' then '3'
WHEN prov.idioma = 'X' then '3' end LanguageCode
, 'N' SubjectToWithholdingTax
, '0' WTCode
FROM vidalb.dbo.proveedores prov
INNER JOIN dbo.FormaPagoAPMtoSAP fpag ON prov.forma_pago = fpag.FormaPagoAPM COLLATE Modern_Spanish_CI_AS
LEFT JOIN BusinessPartners sapprov ON sapprov.CardCode = 'P' + prov.proveedor COLLATE Modern_Spanish_CI_AS
LEFT JOIN INTRX_FormaPagoSAP sapfpag ON sapfpag.PeymentMethodCode=fpag.PeymentMethodCode
LEFT JOIN INTRX_MapeigPC mpc ON mpc.ComptaAPM=(LTRIM(RTRIM(pre_contable))+LTRIM(RTRIM(prov.proveedor))) COLLATE Modern_Spanish_CI_AS
LEFT JOIN INTRX_PaisosZones pz ON pz.pais=prov.pais_orig
WHERE sapprov.CardCode IS NULL
--Adreces
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + '[BP] Adreces ...';
DELETE FROM [apmtosap].[dbo].[BPAddresses]
INSERT INTO [apmtosap].[dbo].[BPAddresses]
([CardCode]
,[AddressName]
,[AddressType]
,[Street]
,[Block]
,[StreetNo]
,[ZipCode]
,[City]
,[Country]
,[FederalTaxID])
SELECT [CardCode]
,[AddressName]
,[AddressType]
,[Street]
,[Block]
,[StreetNo]
,[ZipCode]
,[City]
,[Country]
,[FederalTaxID]
FROM (SELECT 'C0' + cliente CardCode
,ISNULL(cli.n_fiscal, cli.nombre) AddressName
, 'B' AddressType
, cli.direccion Street
, cli.direccion_c Block
, '' StreetNo
, cli.poblacion ZipCode
, pob.poblacion City
, ISNULL(pz.paisISO COLLATE Modern_Spanish_CI_AS, cli.destino_intras) Country
, REPLACE(REPLACE(cli.cif,' ',''),'-','') FederalTaxID
FROM vidalb.dbo.clientes cli
INNER JOIN vidalb.dbo.poblaciones pob ON pob.c_postal = cli.poblacion
LEFT JOIN INTRX_PaisosZones pz ON pz.pais=cli.destino_intras
UNION ALL
SELECT 'C0' + cliente CardCode
,cli.nombre AddressName
, 'S' AddressType
, cli.direccion Street
, cli.direccion_c Block
, '' StreetNo
, cli.poblacion ZipCode
, pob.poblacion City
, ISNULL(pz.paisISO COLLATE Modern_Spanish_CI_AS, cli.destino_intras) Country
, cli.cif
FROM vidalb.dbo.clientes cli
INNER JOIN vidalb.dbo.poblaciones pob ON pob.c_postal = cli.poblacion
LEFT JOIN INTRX_PaisosZones pz ON pz.pais=cli.destino_intras
UNION ALL
SELECT 'C0' + adg.cliente CardCode
, adg.c_direccion + ' ' + ISNULL(adg.nombre,cli.nombre) AddressName
, 'S' AddressType
, adg.direccion Street
, adg.direccion_c Block
, '' StreetNo
, adg.poblacion ZipCode
, pob.poblacion City
, ISNULL(pz.paisISO COLLATE Modern_Spanish_CI_AS, cli.destino_intras) Country
, cli.cif
FROM vidalb.dbo.g_clientes adg
INNER JOIN vidalb.dbo.poblaciones pob ON pob.c_postal = adg.poblacion
INNER JOIN vidalb.dbo.clientes cli ON cli.cliente = adg.cliente
LEFT JOIN INTRX_PaisosZones pz ON pz.pais=cli.destino_intras
UNION ALL
SELECT 'P' + proveedor CardCode
,ISNULL(prov.n_fiscal, prov.nombre) AddressName
, 'B' AddressType
, prov.direccion Street
, prov.direccion_c Block
, '' StreetNo
, prov.c_poblacion ZipCode
, pob.poblacion City
, ISNULL(pz.paisISO COLLATE Modern_Spanish_CI_AS, prov.pais_orig) Country
, prov.cif_dni
FROM vidalb.dbo.proveedores prov
INNER JOIN vidalb.dbo.poblaciones pob ON pob.c_postal = prov.c_poblacion
LEFT JOIN INTRX_PaisosZones pz ON pz.pais=prov.pais_orig
UNION ALL
SELECT 'P' + proveedor CardCode
,prov.nombre AddressName
, 'S' AddressType
, prov.direccion Street
, prov.direccion_c Block
, '' StreetNo
, prov.c_poblacion ZipCode
, pob.poblacion City
, ISNULL(pz.paisISO COLLATE Modern_Spanish_CI_AS, prov.pais_orig) Country
, prov.cif_dni
FROM vidalb.dbo.proveedores prov
INNER JOIN vidalb.dbo.poblaciones pob ON pob.c_postal = prov.c_poblacion
LEFT JOIN INTRX_PaisosZones pz ON pz.pais=prov.pais_orig
) ad
GROUP BY [CardCode]
,[AddressName]
,[AddressType]
,[Street]
,[Block]
,[StreetNo]
,[ZipCode]
,[City]
,[Country]
,[FederalTaxID]
-- Contactes
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + '[BP] Contactes ...';
DELETE FROM [apmtosap].dbo.BPContactes
INSERT INTO [apmtosap].dbo.BPContactes
(
[CardCode],
[Contacte],
[EMail],
[Telefon],
[Mobil],
[Fax],
[Carrec])
SELECT
cardcode,
contacte,
email,
tel,
mobil,
fax,
cargo
FROM
(
-- Clients
select 'C0'+cliente as cardcode,contacto1 as contacte,email_c1 as email,mobil_c1 as mobil,tel_c1 as tel,particular1 as particular,cargo1 as cargo,fax1 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto2 as contacte,email_c2 as email,mobil_c2 as mobil,tel_c2 as tel,particular2 as particular,cargo2 as cargo,fax2 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto3 as contacte,email_c3 as email,mobil_c3 as mobil,tel_c3 as tel,particular3 as particular,cargo3 as cargo,fax3 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto4 as contacte,email_c4 as email,mobil_c4 as mobil,tel_c4 as tel,particular4 as particular,cargo4 as cargo,fax4 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto5 as contacte,email_c5 as email,mobil_c5 as mobil,tel_c5 as tel,particular5 as particular,cargo5 as cargo,fax5 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto6 as contacte,email_c6 as email,mobil_c6 as mobil,tel_c6 as tel,particular6 as particular,cargo6 as cargo,fax6 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto7 as contacte,email_c7 as email,mobil_c7 as mobil,tel_c7 as tel,particular7 as particular,cargo7 as cargo,fax7 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto8 as contacte,email_c8 as email,mobil_c8 as mobil,tel_c8 as tel,particular8 as particular,cargo8 as cargo,fax8 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto9 as contacte,email_c9 as email,mobil_c9 as mobil,tel_c9 as tel,particular9 as particular,cargo9 as cargo,fax9 as fax from Vidalb.dbo.clientes
union all
select 'C0'+cliente as cardcode,contacto10 as contacte,email_c10 as email,mobil_c10 as mobil,tel_c10 as tel,particular10 as particular,cargo10 as cargo,fax10 as fax from Vidalb.dbo.clientes
union all
-- Proveidors
select 'P0'+proveedor as cardcode,contacto1 as contacte,email1 as email,mobil_c1 as mobil,tel_c1 as tel,particular1 as particular,cargo1 as cargo,fax_c1 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto2 as contacte,email2 as email,mobil_c2 as mobil,tel_c2 as tel,particular2 as particular,cargo2 as cargo,fax_c2 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto3 as contacte,email3 as email,mobil_c3 as mobil,tel_c3 as tel,particular3 as particular,cargo3 as cargo,fax_c3 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto4 as contacte,email4 as email,mobil_c4 as mobil,tel_c4 as tel,particular4 as particular,cargo4 as cargo,fax_c4 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto5 as contacte,email5 as email,mobil_c5 as mobil,tel_c5 as tel,particular5 as particular,cargo5 as cargo,fax_c5 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto6 as contacte,email6 as email,mobil_c6 as mobil,tel_c6 as tel,particular6 as particular,cargo6 as cargo,fax_c6 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto7 as contacte,email7 as email,mobil_c7 as mobil,tel_c7 as tel,particular7 as particular,cargo7 as cargo,fax_c7 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto8 as contacte,email8 as email,mobil_c8 as mobil,tel_c8 as tel,particular8 as particular,cargo8 as cargo,fax_c8 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto9 as contacte,email9 as email,mobil_c9 as mobil,tel_c9 as tel,particular9 as particular,cargo9 as cargo,fax_c9 as fax from Vidalb.dbo.proveedores
union all
select 'P0'+proveedor as cardcode,contacto10 as contacte,email10 as email,mobil_c10 as mobil,tel_c10 as tel,particular10 as particular,cargo10 as cargo,fax_c10 as fax from Vidalb.dbo.proveedores
) C0 where not contacte is null or not email is null or not mobil is null or not cargo is null
-- Métodes de pagament (VIES)
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Vies de pagament ...';
DELETE FROM [apmtosap].[dbo].[BPPaymentMethods]
INSERT INTO [apmtosap].[dbo].[BPPaymentMethods]
([CardCode]
,[PaymentMethodCode])
SELECT prt.CardCode [CardCode]
, prt.PayTermsGrpCode [PaymentMethodCode]
FROM BusinessPartners prt
-- Dias de pagament (CONDICIONS)
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Condicions de pagament ...';
DELETE FROM [apmtosap].dbo.BPPaymentDates
INSERT INTO [apmtosap].[dbo].[BPPaymentDates]
([CardCode]
,[PmntDate])
SELECT cardcode, PmntDate
FROM (SELECT 'C0' + cliente cardcode
, case
when LEN(cli.dias_pag) IN ('6','4','2')
then substring(convert(varchar, cli.dias_pag),1,2)
when LEN(cli.dias_pag) IN ('5','3','1')
then substring(convert(varchar, cli.dias_pag),1,1)
end PmntDate
FROM Vidalb.dbo.clientes cli
WHERE LEN(cli.dias_pag) > 0
UNION ALL SELECT 'C0' + cliente
, case
when LEN(cli.dias_pag) IN ('6','4')
then substring(convert(varchar, cli.dias_pag),3,2)
when LEN(cli.dias_pag) IN ('5','3')
then substring(convert(varchar, cli.dias_pag),2,2)
end
FROM Vidalb.dbo.clientes cli
WHERE LEN(cli.dias_pag) > 2
UNION ALL SELECT 'C0' + cliente
, case
when LEN(cli.dias_pag) IN ('6')
then substring(convert(varchar, cli.dias_pag),5,2)
when LEN(cli.dias_pag) IN ('5')
then substring(convert(varchar, cli.dias_pag),4,2)
end
FROM Vidalb.dbo.clientes cli
WHERE LEN(cli.dias_pag) > 4
UNION ALL SELECT 'P' + proveedor cardcode
, case
when LEN(prov.dias_pago) IN ('6','4','2')
then substring(convert(varchar, prov.dias_pago),1,2)
when LEN(prov.dias_pago) IN ('5','3','1')
then substring(convert(varchar, prov.dias_pago),1,1)
end PmntDate
FROM Vidalb.dbo.proveedores prov
WHERE LEN(prov.dias_pago) > 0
UNION ALL SELECT 'P' + proveedor
, case
when LEN(prov.dias_pago) IN ('6','4')
then substring(convert(varchar, prov.dias_pago),3,2)
when LEN(prov.dias_pago) IN ('5','3')
then substring(convert(varchar, prov.dias_pago),2,2)
end
FROM Vidalb.dbo.proveedores prov
WHERE LEN(prov.dias_pago) > 2
UNION ALL SELECT 'P' + proveedor
, case
when LEN(prov.dias_pago) IN ('6')
then substring(convert(varchar, prov.dias_pago),5,2)
when LEN(prov.dias_pago) IN ('5')
then substring(convert(varchar, prov.dias_pago),4,2)
end
FROM Vidalb.dbo.proveedores prov
WHERE LEN(prov.dias_pago) > 4) diaspag
ORDER BY diaspag.cardcode, diaspag.PmntDate * 1
-- Articles
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Esborrant articles no traspassats (es tornaràn a traspassar) ...';
-- Només esborrem els articles que no han estat traspassats
DELETE FROM [apmtosap].[dbo].[Items] WHERE (Processed='N' or Processed is Null);
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Inserint articles ...';
INSERT INTO [apmtosap].[dbo].[Items]
([DateTime]
,[Processed]
,[ItemCode]
,[ItemName]
,[ItemsGroupCode]
,[InventoryItem]
,[SalesItem]
,[PurchaseItem]
,[AssetItem])
SELECT
GETDATE()
, 'N' as Processed
, LTRIM(RTRIM(articulo)) ItemCode
, LTRIM(RTRIM(descripcion)) ItemName
, LTRIM(RTRIM(familia)) ItemsGroupCode
, 'N' InventoryItem
, 'Y' SalesItem
, 'Y' PurchaseItem
, 'N' AssetItem
FROM vidalb.dbo.articulos art
LEFT JOIN [apmtosap].[dbo].[Items] sapart ON sapart.[ItemCode] = art.articulo COLLATE Modern_Spanish_CI_AS
WHERE sapart.[ItemCode] IS NULL
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Inserint formes de pagament no controlades ...';
-- Inserto les Formes de Pagament no Controlades
INSERT INTO dbo.FormaPagoAPMtoSAP
(FormaPagoAPM,DescripcionAPM)
SELECT pag.pago, pag.descripcion
FROM Vidalb.dbo.pagos pag
LEFT JOIN dbo.FormaPagoAPMtoSAP sappag ON sappag.FormaPagoAPM = pag.pago COLLATE Modern_Spanish_CI_AS
WHERE sappag.FormaPagoAPM IS NULL
-- FACTURES
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Esborrant factures no traspassades (es tornaràn a traspassar) ...';
-- Només esborrem les factures que no s'han traspassat
DELETE FROM [apmtosap].[dbo].[Documents] WHERE (Processed='N' or Processed is Null);
-- Factures de venda
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Inserint factures de venda ...';
INSERT INTO [apmtosap].[dbo].[Documents]
(
[DateTime]
,[id_document]
,[objectType]
,[DocType]
,[HandWritten]
,[Series]
,[DocDate]
,[TaxDate]
,[CardCode]
,[NumAtCard]
,[DocCurrency]
,[DocRate]
,[DocTotal]
,[DocTotalFc]
,[FederalTaxID]
,[SalesPersonCode]
,[PaymentGroupCode]
,[PaymentMethod]
,[PayToCode]
,[ShipToCode]
,[TrackingNumber]
,[Comments])
SELECT
GETDATE()
, (facv.serie+facv.registro) ID_DOCUMENT
, case when facv.total_factura<0 then 'oCreditNotes' else 'oInvoices' end objectType
, facv.clase_iva DocType
,'N' HandWritten
, facv.serie Series
, CONVERT(varchar, facv.fecha_registro, 112) DocDate
, CONVERT(varchar, facv.fecha_registro, 112) TaxDate
, 'C0' + cli.cliente CardCode
, facv.serie + facv.registro NumAtCard
, facv.divisa DocCurrency
, ROUND(facv.cotizacion,6) DocRate
, ROUND(case when facv.divisa = 'EUR' then facv.total_factura else null end,6) DocTotal
, ROUND(case when facv.divisa = 'EUR' then null else facv.importe_divisa end,6) DocTotalFc
, REPLACE(REPLACE(case when ISNULL(cli.destino_intras, 'ES') = 'ES' then LTRIM(RTRIM(cli.destino_intras + cli.cif))
else RTRIM(LTRIM(cli.cif)) end,' ',''),'-','') FederalTaxID
, SUBSTRING(ISNULL(cli.representante + '-' + rep.nombre,''),1,30) SalesPersonCode
, fpag.PayTermsGrpCode
, sapfpag.PayMethodCodeCobro
, LTRIM(RTRIM(cli.n_fiscal)) BilltoDefault
, LTRIM(RTRIM(cli.nombre)) ShipToDefault
, '' TrackingNumber
, '' Comments
FROM vidalb.dbo.ivaclie2019 facv
INNER JOIN
(
SELECT cab.n_factura, cab.cliente, MAX(cab.t_factura) t_factura, MAX(cab.f_pago) f_pago
FROM vidalb.dbo.h_alba_v2019 cab
GROUP BY cab.n_factura, cab.cliente
UNION ALL
SELECT cab.n_factura, cab.cliente, MAX(cab.t_factura) t_factura, MAX(cab.f_pago) f_pago
FROM vidalb.dbo.h_alba_v2018 cab
GROUP BY cab.n_factura, cab.cliente
) cab ON cab.n_factura = facv.serie + facv.registro
INNER JOIN vidalb.dbo.facturas tv ON tv.factura = cab.t_factura
INNER JOIN vidalb.dbo.clientes cli ON cli.cliente = cab.cliente
INNER JOIN vidalb.dbo.representantes rep ON rep.representante = cli.representante
INNER JOIN dbo.FormaPagoAPMtoSAP fpag ON cab.f_pago = fpag.FormaPagoAPM COLLATE Modern_Spanish_CI_AS
LEFT JOIN Documents sapcab ON sapcab.id_document = (facv.serie+facv.registro) COLLATE Modern_Spanish_CI_AS
LEFT JOIN INTRX_FormaPagoSAP sapfpag ON sapfpag.PeymentMethodCode=fpag.PeymentMethodCode
WHERE sapcab.id_document IS NULL
ORDER BY id_document
-- Factures de compra
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Inserint factures de compra ...';
INSERT INTO [apmtosap].[dbo].[Documents]
(
[DateTime]
,[id_document]
,[objectType]
,[DocType]
,[HandWritten]
,[Series]
,[DocDate]
,[TaxDate]
,[CardCode]
,[NumAtCard]
,[DocCurrency]
,[DocRate]
,[DocTotal]
,[DocTotalFc]
,[FederalTaxID]
,[SalesPersonCode]
,[PaymentGroupCode]
,[PaymentMethod]
,[PayToCode]
,[ShipToCode]
,[TrackingNumber]
,[Comments])
SELECT
GETDATE()
, RIGHT(YEAR(facc.fecha_registro),2)+(facc.serie+facc.registro) id_document
, case when total_factura >= 0 then 'oPurchaseInvoices' else 'oPurchaseCreditNotes' end objectType
, facc.clase_iva DocType
,'N' HandWritten
, facc.serie Series
, CONVERT(varchar, facc.fecha_registro, 112) TaxDate
, CONVERT(varchar, facc.fecha_registro, 112) DocDate
, 'P' + prov.proveedor CardCode
, facc.documento NumAtCard
, facc.divisa DocCurrency
, ROUND(facc.cotizacion,6) DocRate
, ROUND( case when facc.divisa = 'EUR' then
case when clase_iva='C' then facc.base_1 else facc.total_factura end
else null end,6) DocTotal
, ROUND( case when facc.divisa = 'EUR' then null
else facc.total_factura end,6) DocTotalFc
, REPLACE(REPLACE(case when ISNULL(prov.pais_orig, 'ES') = 'ES'
then ISNULL(RTRIM(prov.pais_orig), 'ES') + RTRIM(LTRIM(prov.cif_dni))
else prov.cif_dni end,' ',''),'-','') FederalTaxID
, '' SalesPersonCode
, fpag.PayTermsGrpCode
, sapfpag.PayMethodCodePago
, LTRIM(RTRIM(prov.n_fiscal)) BilltoDefault
, LTRIM(RTRIM(prov.nombre)) ShipToDefault
, '' TrackingNumber
, '' Comments
FROM vidalb.dbo.ivaprov2019 facc
LEFT JOIN Vidalb.dbo.proveedores prov ON prov.proveedor=(SELECT TOP 1 proveedor from Vidalb.dbo.h_facturas_c2019 where factura=(facc.serie+facc.registro))
LEFT JOIN FormaPagoAPMtoSAP fpag ON prov.forma_pago = fpag.FormaPagoAPM COLLATE Modern_Spanish_CI_AS
LEFT JOIN Documents sapcab ON sapcab.id_document = RIGHT(YEAR(facc.fecha_registro),2)+(facc.serie+facc.registro) COLLATE Modern_Spanish_CI_AS
LEFT JOIN INTRX_FormaPagoSAP sapfpag ON sapfpag.PeymentMethodCode=fpag.PeymentMethodCode
WHERE sapcab.id_document IS NULL
-- LINIES
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Esborrant Document_Lines ...';
-- Nomes esborrem les linies de les factures que no s'han traspassat
DELETE [apmtosap].[dbo].[Document_Lines]
-- Linies de factures de vendes
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Inserint línies de factures de clients ...';
INSERT INTO [apmtosap].[dbo].[Document_Lines]
([ID_DOCUMENT]
,[ItemDescription]
,[VatGroup]
,[AccountCode]
,[ItemCode]
,[ItemDetails]
,[Quantity]
,[MeasureUnit]
,[UnitPrice]
,[FreeText]
,[WTLiable]
,[Weight1]
,[Weight1Unit]
,[Weight2]
,[Weight2Unit]
,[Volume]
,[Factor4]
,[Num_Linea])
SELECT
(facv.serie+facv.registro) ID_DOCUMENT
, LTRIM(RTRIM(art.descripcion)) ItemDescription
, case when fam.tabla_iva = 1 AND tfac.clase_iva IN ('N') then 'R3'
when fam.tabla_iva = 4 AND tfac.clase_iva IN ('N') then 'R0'
when tfac.clase_iva IN('C') then 'EI'
when tfac.clase_iva IN ('E','X') then 'EX'
end VatGroup
, case
when (facv.total_factura<0) and (GETDATE()>='20180501') then '708000'
else
case
when lin.articulo in ('Z0001','Z0003','Z0010','Z0011','Z0012','Z0013','Z0014') then '705002'
when tfac.clase_iva='N' then '701001'
when tfac.clase_iva='C' then '701002'
when tfac.clase_iva='E' then '701003'
when tfac.clase_iva='X' then '701003'
end
end AccountCode
, LTRIM(RTRIM(lin.articulo)) ItemCode
, lin.cantidad_alter ItemDetails
, lin.cantidad Quantity
, art.un_medida MeasureUnit
, lin.precio_m_emp UnitPrice
, Null [FreeText]
, 'N' WTLiable
, Null Weight1
, Null Weight1Unit
, Null Weight2
, Null Weight2Unit
, Null Volume
, Null Factor4
, CONVERT(int,lin.n_orden) Num_Linia
FROM
vidalb.dbo.h_alba_l_v2019 lin
INNER JOIN vidalb.dbo.h_alba_v2019 cab ON cab.albaran = lin.albaran
INNER JOIN vidalb.dbo.ivaclie2019 facv ON facv.serie + facv.registro = cab.n_factura
INNER JOIN vidalb.dbo.articulos art ON art.articulo = lin.articulo
INNER JOIN vidalb.dbo.familias fam ON fam.familia = art.familia
INNER JOIN vidalb.dbo.facturas tfac ON tfac.factura = cab.t_factura
LEFT JOIN Document_Lines saplin ON saplin.ID_DOCUMENT = cab.albaran COLLATE Modern_Spanish_CI_AS AND saplin.Num_Linea = lin.n_orden COLLATE Modern_Spanish_CI_AS
LEFT JOIN INTRX_MapeigPC mpc ON mpc.ComptaAPM=fam.c_ventas COLLATE Modern_Spanish_CI_AS
WHERE lin.concepto = 'A'
UNION ALL
SELECT
(facv.serie+facv.registro) id_document
, LTRIM(RTRIM(art.descripcion)) ItemDescription
, case when fam.tabla_iva = 1 AND tfac.clase_iva IN ('N') then 'R3'
when fam.tabla_iva = 4 AND tfac.clase_iva IN ('N') then 'R0'
when tfac.clase_iva IN('C') then 'EI'
when tfac.clase_iva IN ('E','X') then 'EX'
end VatGroup
, case
when lin.articulo in ('Z0001','Z0003','Z0010','Z0011','Z0012','Z0013','Z0014') then '705002'
when tfac.clase_iva='N' then '701001'
when tfac.clase_iva='C' then '701002'
when tfac.clase_iva='E' then '701003'
when tfac.clase_iva='X' then '701003'
when lin.articulo in ('Z0005') then '705001'
end AccountCode
, LTRIM(RTRIM(lin.articulo)) ItemCode
, lin.cantidad_alter ItemDetails
, lin.cantidad Quantity
, art.un_medida MeasureUnit
, lin.precio_m_emp UnitPrice
, Null [FreeText]
, 'N' WTLiable
, Null Weight1
, Null Weight1Unit
, Null Weight2
, Null Weight2Unit
, Null Volume
, Null Factor4
, CONVERT(int,lin.n_orden) Num_Linia
FROM
vidalb.dbo.h_alba_l_v2018 lin
INNER JOIN vidalb.dbo.h_alba_v2018 cab ON cab.albaran = lin.albaran
INNER JOIN vidalb.dbo.ivaclie2019 facv ON facv.serie + facv.registro = cab.n_factura
INNER JOIN vidalb.dbo.articulos art ON art.articulo = lin.articulo
INNER JOIN vidalb.dbo.familias fam ON fam.familia = art.familia
INNER JOIN vidalb.dbo.facturas tfac ON tfac.factura = cab.t_factura
LEFT JOIN Document_Lines saplin ON saplin.ID_DOCUMENT = cab.albaran COLLATE Modern_Spanish_CI_AS AND saplin.Num_Linea = lin.n_orden COLLATE Modern_Spanish_CI_AS
LEFT JOIN INTRX_MapeigPC mpc ON mpc.ComptaAPM=fam.c_ventas COLLATE Modern_Spanish_CI_AS
WHERE lin.concepto = 'A'
-- Linies factures de compra
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Inserint línies de compra ...';
INSERT INTO [apmtosap].[dbo].[Document_Lines]
([ID_DOCUMENT]
,[ItemDescription]
,[VatGroup]
,[AccountCode]
,[ItemCode]
,[ItemDetails]
,[Quantity]
,[MeasureUnit]
,[UnitPrice]
,[FreeText]
,[WTLiable]
,[Weight1]
,[Weight1Unit]
,[Weight2]
,[Weight2Unit]
,[Volume]
,[Factor4]
,[Num_Linea])
SELECT
docSAP.id_document ID_DOCUMENT
, Null ItemDescription
, case ivap.clase_iva
when 'N' then -- IVA NACIONAL
case
when not iva_1 is null and iva_2 is null and iva_3 is null then
case
when iva_1=21 then 'S3'
when iva_1=10 then 'S2'
when iva_1=0 then 'S0'
end
else
case
when importe=base_1 then
case
when iva_1=21 then 'S3'
when iva_1=10 then 'S2'
when iva_1=0 then 'S0'
end
when importe=base_2 then
case
when iva_2=21 then 'S3'
when iva_2=10 then 'S2'
when iva_2=0 then 'S0'
end
when importe=base_3 then
case
when iva_3=21 then 'S3'
when iva_3=10 then 'S2'
when iva_3=0 then 'S0'
end
end
end
when 'C' then -- IVA COMUNITARI
'A3'
when 'E' then -- EXENT
case when bp.BankCountry='ES' then 'S0'
else 'I0'
end
else
''
end VatGroup
, RTRIM(c.cuenta) AccountCode
, Null ItemCode
, Null ItemDetails
, 1 Quantity
, Null MeasureUnit
, c.importe UnitPrice
, Null [FreeText]
, Null WTLiable
, Null Weight1
, Null Weight1Unit
, Null Weight2
, Null Weight2Unit
, Null Volume
, Null Factor4
, (ROW_NUMBER() OVER(PARTITION BY docSAP.id_document ORDER BY docSAP.id_document))*100 as Num_Linea
FROM Vidalb.dbo.contab_c c
JOIN Vidalb.dbo.ivaprov2019 ivap ON (ivap.serie+ivap.registro)=c.registro_iva COLLATE Modern_Spanish_CI_AS
JOIN Documents docSAP ON docSAP.id_document=RIGHT(YEAR(c.fecha_registro),2)+c.registro_iva COLLATE Modern_Spanish_CI_AS
LEFT JOIN BusinessPartners bp on bp.CardCode=docSAP.CardCode
WHERE (c.cuenta like '6%' or c.cuenta like '419%' or c.cuenta like '174%')
-- Venciments factures de venda
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Netejant venciments ...';
DELETE FROM [apmtosap].[dbo].[Document_Installments]
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Venciments factures de clients ...';
INSERT INTO [apmtosap].[dbo].[Document_Installments]
([ID_DOCUMENT]
,[DueDate]
,[Total]
,[TotalFc])
SELECT (ivac.serie+ivac.registro) ID_DOCUMENT
, CONVERT(varchar, vencimiento, 112) DueDate
, case when cob.divisa = 'EUR' then cob.importe else 0 end [Total]
, case when cob.divisa <> 'EUR' then ISNULL(cob.importe_divisa,cob.importe) else 0 end [TotalFc]
FROM vidalb.dbo.cobro cob
INNER JOIN Vidalb.dbo.ivaclie2019 ivac on (ivac.serie+ivac.registro)=cob.documento
INNER JOIN [apmtosap].[dbo].[Documents] sapdocs ON sapdocs.id_document COLLATE Modern_Spanish_CI_AS = (ivac.serie+ivac.registro)
WHERE sapdocs.[objectType] IN ('oCreditNotes', 'oInvoices')
-- Venciments factures de proveidors
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Venciments factures de proveïdors ...';
INSERT INTO [apmtosap].[dbo].[Document_Installments]
([ID_DOCUMENT]
,[DueDate]
,[Total]
,[TotalFc])
SELECT RIGHT(YEAR(ivap.fecha_registro),2)+ivap.serie + ivap.registro ID_DOCUMENT
, CONVERT(varchar, pag.vencimiento, 112) DueDate
, case when pag.divisa = 'EUR' then pag.importe else 0 end [Total]
, case when pag.divisa <> 'EUR' then pag.importe else 0 end [TotalFc]
FROM vidalb.dbo.pago pag
INNER JOIN vidalb.dbo.ivaprov2019 ivap ON ivap.documento = pag.documento AND ivap.cuenta = pag.creditor AND situacion='C'
INNER JOIN [apmtosap].[dbo].[Documents] sapdocs ON sapdocs.id_document COLLATE Modern_Spanish_CI_AS = RIGHT(Year(ivap.fecha_registro),2)+(ivap.serie+ivap.registro)
WHERE sapdocs.[objectType] IN ('oPurchaseInvoices', 'oPurchaseCreditNotes')
-- Representants
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'Representants ...';
DELETE FROM [apmtosap].[dbo].[SalesPersons]
INSERT INTO [apmtosap].[dbo].[SalesPersons]
(SalesEmployeeName)
SELECT DISTINCT SalesPersonCode
FROM [apmtosap].[dbo].[Documents]
WHERE ISNULL(SalesPersonCode,'') <> ''
PRINT CONVERT(varChar,DATEDIFF(millisecond,@inici,GETDATE())) + ' milisegons';
PRINT CONVERT(nVarChar, GETDATE(), 113) + ' ' + 'FI del traspàs de APM a SAP.';
END