August 22, 2018 at 10:04 am
Hello community,
I built this stored procedure to create an XML file with a specific structure and using Row_Number () to distinguish each row by its number and order (LineNumber).
My problem is that on the rows node I have 2 sub-nodes, LineNumberTax and LineNumberDetails.
What happens is if LineNumber = 1 then the 2 sub-nodes, LineNumberTax and LineNumberDetails must also be equal to 1.
If it is on line 2, the LineNumber + LineNumberTax + LineNumberDetails must be equal to 2 ... etc
How can I do this in my stored procedure.
I leave here the current return and the correct return that I intend.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetXML_InputcomDetalhe]
(@Stamp VARCHAR(25)
)
as
DECLARE @XML XML = N'';
DECLARE @XMLSTR NVARCHAR(MAX) = N'';
SELECT @XML = (
SELECT(
SELECT
1 AS Versao,
220 AS coddoc,
5 AS tipodoc,
'EUR' as codmoeda,
c.obrano AS numdoc,
c.dataobra AS datadoc,
Rtrim(c.nome) AS localentrega,
Rtrim(c.morada) AS moradaentrega,
Rtrim(c.local) AS loclocalalentrega,
Rtrim(c.codpost) AS codpostentrega,
c.datafinal AS dataentrega,
c.datafinal AS datalimite,
5600000561737 AS glnfornecedor,
'My Company , Lda' AS nomefornecedor,
'Zona Ind. do Roligo' AS moradafornecedor,
501167323 as ncontfornecedor,
cl.glncl as glncliente,
cl.ncont AS ncontcliente,
Rtrim(c.nome) AS nomecliente,
Rtrim(c.morada) AS moradacliente,
Rtrim(c.codpost) AS codpostcliente,
Rtrim(c.local) AS localcliente,
'4524-909' AS codpostfornecedor,
(SELECT COUNT(*) FROM bi (nolock) WHERE bi.bostamp = @Stamp) AS numlinhas,
(select cl.eancl from cl (nolock) where cl.no = c.no and cl.estab = 0) AS eancliente,
5600000561737 AS eanfornecedor,
Rtrim(c.obs) AS observacoes,
cl.glncl as glnentrega,
--Lines Details
(
SELECT 1 AS Versao,
ROW_NUMBER() OVER (ORDER BY Linh.lordem asc) AS LineNumber,
Rtrim(Linh.codigo) AS codean,
Rtrim(Linh.litem) AS refcliente,
Rtrim(Linh.ref) AS referencia,
Rtrim(Linh.design) AS descricao,
Linh.unidad2 AS unidalternativa,
Linh.ettdeb AS precoliquido,
Linh.qtt AS quantidade,
Linh.uni2qtt AS quantalternativa ,
--TAX Percent and Value for each line
(
SELECT TOP 1
ROW_NUMBER() OVER (ORDER BY LinhDet.lordem asc) AS LineNumberTax,
LinhDet.tabiva AS [Tabiva], LinhDet.iva AS [IVA]
FROM bi LinhDet INNER JOIN bi Linh ON Linh.lordem = LinhDet.Lordem AND Linh.bistamp = LinhDet.bistamp
WHERE LinhDet.bostamp = @Stamp
FOR XML PATH('Taxes'),TYPE
),
--Original Source Document
(
SELECT TOP 1
ROW_NUMBER() OVER (ORDER BY LinhREF.lordem asc) AS LineNumberDetalhe,
convert(datetime, LinhREF.dataobra, 126)AS [refdata],
'Customer Order' AS [refnomedoc],
999999 AS [refnumdoc], 5 AS [reftipodoc]
FROM bi LinhREF INNER JOIN bi Linh ON Linh.lordem = LinhREF.Lordem AND Linh.bistamp = LinhREF.bistamp
WHERE LinhREF.bostamp = @Stamp
FOR XML PATH('SourceDocument'),TYPE
)
FROM BI Linh (NOLOCK)
WHERE Linh.bostamp = @Stamp
FOR XML PATH('Lines'),TYPE
)
FROM bo c (NOLOCK)
LEFT JOIN cl (nolock) ON c.no = cl.no AND cl.estab = 0
where c.bostamp = @Stamp
FOR XML PATH('Documento'),ROOT('VFPDATASET'),TYPE) );
select @XMLSTR = N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>' + CAST(@XML AS NVARCHAR(MAX))
SELECT convert(NVARCHAR(MAX) , @XMLSTR ,1) AS meuXml;
--EXEC uspGetXML_InputcomDetalhe 'SEG15022343671,411000002'
my atual XML File:
<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>
- <VFPDATASET>
- <Documento>
<Versao>1</Versao>
<coddoc>220</coddoc>
<tipodoc>5</tipodoc>
<codmoeda>EUR</codmoeda>
<numdoc>139425</numdoc>
<datadoc>2015-02-23T00:00:00</datadoc>
<localentrega>TOYS R US IBERIA, S.A.</localentrega>
<moradaentrega>POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800</moradaentrega>
<loclocalalentrega>ALCLÁ DE HENARES</loclocalalentrega>
<codpostentrega>28802 MADRID - ESPAÑA</codpostentrega>
<dataentrega>2015-02-23T00:00:00</dataentrega>
<datalimite>2015-02-23T00:00:00</datalimite>
<glnfornecedor>5600000561737</glnfornecedor>
<nomefornecedor>My Company , Lda</nomefornecedor>
<moradafornecedor>Zona Ind. do Roligo</moradafornecedor>
<ncontfornecedor>501167323</ncontfornecedor>
<glncliente>8421703000012</glncliente>
<ncontcliente>A79520656</ncontcliente>
<nomecliente>TOYS R US IBERIA, S.A.</nomecliente>
<moradacliente>POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800</moradacliente>
<codpostcliente>28802 MADRID - ESPAÑA</codpostcliente>
<localcliente>ALCLÁ DE HENARES</localcliente>
<codpostfornecedor>4524-909</codpostfornecedor>
<numlinhas>7</numlinhas>
<eancliente>8421703000012</eancliente>
<eanfornecedor>5600000561737</eanfornecedor>
<observacoes>.</observacoes>
<glnentrega>8421703000012</glnentrega>
- <Lines>
<Versao>1</Versao>
<LineNumber>1</LineNumber>
<codean>2000021311701</codean>
<refcliente>247790</refcliente>
<referencia>2131170</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>2</LineNumber>
<codean>2000021311718</codean>
<refcliente>247793</refcliente>
<referencia>2131171</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>3</LineNumber>
<codean>2000021339316</codean>
<refcliente>247801</refcliente>
<referencia>2133931</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>4</LineNumber>
<codean>2000021339309</codean>
<refcliente>247804</refcliente>
<referencia>2133930</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>5</LineNumber>
<codean>2000021410107</codean>
<refcliente>247807</refcliente>
<referencia>2141010</referencia>
<descricao>Sapato Lona INFANTIL 20/26 Cx14</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>878.430000</precoliquido>
<quantidade>210.0000</quantidade>
<quantalternativa>15.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>6</LineNumber>
<codean>2000021425019</codean>
<refcliente>247866</refcliente>
<referencia>2142500-TOYS</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>1095.450000</precoliquido>
<quantidade>156.0000</quantidade>
<quantalternativa>13.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>7</LineNumber>
<codean>2000021411821</codean>
<refcliente>247860</refcliente>
<referencia>2141182</referencia>
<descricao>Sapato Lona INFANTIL 25/30 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>599.150000</precoliquido>
<quantidade>120.0000</quantidade>
<quantalternativa>10.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
</Documento>
</VFPDATASET>
My expect XML result with the same LineNumber on Subnodes LineNumberTax and LineNumberDetalhe :
<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>
- <VFPDATASET>
- <Documento>
<Versao>1</Versao>
<coddoc>220</coddoc>
<tipodoc>5</tipodoc>
<codmoeda>EUR</codmoeda>
<numdoc>139425</numdoc>
<datadoc>2015-02-23T00:00:00</datadoc>
<localentrega>TOYS R US IBERIA, S.A.</localentrega>
<moradaentrega>POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800</moradaentrega>
<loclocalalentrega>ALCLÁ DE HENARES</loclocalalentrega>
<codpostentrega>28802 MADRID - ESPAÑA</codpostentrega>
<dataentrega>2015-02-23T00:00:00</dataentrega>
<datalimite>2015-02-23T00:00:00</datalimite>
<glnfornecedor>5600000561737</glnfornecedor>
<nomefornecedor>My Company , Lda</nomefornecedor>
<moradafornecedor>Zona Ind. do Roligo</moradafornecedor>
<ncontfornecedor>501167323</ncontfornecedor>
<glncliente>8421703000012</glncliente>
<ncontcliente>A79520656</ncontcliente>
<nomecliente>TOYS R US IBERIA, S.A.</nomecliente>
<moradacliente>POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800</moradacliente>
<codpostcliente>28802 MADRID - ESPAÑA</codpostcliente>
<localcliente>ALCLÁ DE HENARES</localcliente>
<codpostfornecedor>4524-909</codpostfornecedor>
<numlinhas>7</numlinhas>
<eancliente>8421703000012</eancliente>
<eanfornecedor>5600000561737</eanfornecedor>
<observacoes>.</observacoes>
<glnentrega>8421703000012</glnentrega>
- <Lines>
<Versao>1</Versao>
<LineNumber>1</LineNumber>
<codean>2000021311701</codean>
<refcliente>247790</refcliente>
<referencia>2131170</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>2</LineNumber>
<codean>2000021311718</codean>
<refcliente>247793</refcliente>
<referencia>2131171</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
- <Taxes>
<LineNumberTax>2</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>2</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>3</LineNumber>
<codean>2000021339316</codean>
<refcliente>247801</refcliente>
<referencia>2133931</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
- <Taxes>
<LineNumberTax>3</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>3</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>4</LineNumber>
<codean>2000021339309</codean>
<refcliente>247804</refcliente>
<referencia>2133930</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
- <Taxes>
<LineNumberTax>4</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>4</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>5</LineNumber>
<codean>2000021410107</codean>
<refcliente>247807</refcliente>
<referencia>2141010</referencia>
<descricao>Sapato Lona INFANTIL 20/26 Cx14</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>878.430000</precoliquido>
<quantidade>210.0000</quantidade>
<quantalternativa>15.0000</quantalternativa>
- <Taxes>
<LineNumberTax>5</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>5</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>6</LineNumber>
<codean>2000021425019</codean>
<refcliente>247866</refcliente>
<referencia>2142500-TOYS</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>1095.450000</precoliquido>
<quantidade>156.0000</quantidade>
<quantalternativa>13.0000</quantalternativa>
- <Taxes>
<LineNumberTax>6</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>6</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>7</LineNumber>
<codean>2000021411821</codean>
<refcliente>247860</refcliente>
<referencia>2141182</referencia>
<descricao>Sapato Lona INFANTIL 25/30 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>599.150000</precoliquido>
<quantidade>120.0000</quantidade>
<quantalternativa>10.0000</quantalternativa>
- <Taxes>
<LineNumberTax>7</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>7</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
</Documento>
</VFPDATASET>
Code for creating temporary table to test:
SELECT * INTO
#myOrderCab
FROM (
SELECT
1 AS Versao,
220 AS coddoc,
5 AS tipodoc,
'EUR' as codmoeda,
c.obrano AS numdoc,
c.dataobra AS datadoc,
Rtrim(c.nome) AS localentrega,
Rtrim(c.morada) AS moradaentrega,
Rtrim(c.local) AS loclocalalentrega,
Rtrim(c.codpost) AS codpostentrega,
c.datafinal AS dataentrega,
c.datafinal AS datalimite,
5600000561737 AS glnfornecedor,
'My Company , Lda' AS nomefornecedor,
'Zona Ind. do Roligo' AS moradafornecedor,
501167323 as ncontfornecedor,
cl.glncl as glncliente,
cl.ncont AS ncontcliente,
Rtrim(c.nome) AS nomecliente,
Rtrim(c.morada) AS moradacliente,
Rtrim(c.codpost) AS codpostcliente,
Rtrim(c.local) AS localcliente,
'4524-909' AS codpostfornecedor,
(SELECT COUNT(*) FROM bi (nolock) WHERE bi.bostamp = 'SEG15022343671,411000002') AS numlinhas,
(select cl.eancl from cl (nolock) where cl.no = c.no and cl.estab = 0) AS eancliente,
5600000561737 AS eanfornecedor,
Rtrim(c.obs) AS observacoes,
cl.glncl as glnentrega
FROM bo c (NOLOCK)
LEFT JOIN cl (nolock) ON c.no = cl.no AND cl.estab = 0
where c.bostamp = 'SEG15022343671,411000002'
)x
--Lines
SELECT * INTO
#myOrderLines
FROM (
SELECT 1 AS Versao,
ROW_NUMBER() OVER (ORDER BY Linh.lordem asc) AS LineNumber,
Rtrim(Linh.codigo) AS codean,
Rtrim(Linh.litem) AS refcliente,
Rtrim(Linh.ref) AS referencia,
Rtrim(Linh.design) AS descricao,
Linh.unidad2 AS unidalternativa,
Linh.ettdeb AS precoliquido,
Linh.qtt AS quantidade,
Linh.uni2qtt AS quantalternativa ,
Linh.tabiva AS Tabiva,
Linh.iva AS [IVA],
Linh.lordem,
Linh.bistamp,
Linh.bostamp,
convert(datetime, Linh.dataobra, 126)AS [refdata],
'Customer Order' AS [refnomedoc],
999999 AS [refnumdoc], 5 AS [reftipodoc]
FROM BI Linh (NOLOCK) INNER JOIN BO c
ON Linh.bostamp = c.bostamp
WHERE Linh.bostamp = 'SEG15022343671,411000002'
)z
Many thanks,
Best regards,
LS
August 22, 2018 at 12:16 pm
luissantos - Wednesday, August 22, 2018 10:04 AMHello community,I built this stored procedure to create an XML file with a specific structure and using Row_Number () to distinguish each row by its number and order (LineNumber).
My problem is that on the rows node I have 2 sub-nodes, LineNumberTax and LineNumberDetails.
What happens is if LineNumber = 1 then the 2 sub-nodes, LineNumberTax and LineNumberDetails must also be equal to 1.
If it is on line 2, the LineNumber + LineNumberTax + LineNumberDetails must be equal to 2 ... etcHow can I do this in my stored procedure.
I leave here the current return and the correct return that I intend.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[uspGetXML_InputcomDetalhe]
(@Stamp VARCHAR(25)
)
as
DECLARE @XML XML = N'';
DECLARE @XMLSTR NVARCHAR(MAX) = N'';
SELECT @XML = (
SELECT(
SELECT
1 AS Versao,
220 AS coddoc,
5 AS tipodoc,
'EUR' as codmoeda,
c.obrano AS numdoc,
c.dataobra AS datadoc,
Rtrim(c.nome) AS localentrega,
Rtrim(c.morada) AS moradaentrega,
Rtrim(c.local) AS loclocalalentrega,
Rtrim(c.codpost) AS codpostentrega,
c.datafinal AS dataentrega,
c.datafinal AS datalimite,
5600000561737 AS glnfornecedor,
'My Company , Lda' AS nomefornecedor,
'Zona Ind. do Roligo' AS moradafornecedor,
501167323 as ncontfornecedor,
cl.glncl as glncliente,
cl.ncont AS ncontcliente,
Rtrim(c.nome) AS nomecliente,
Rtrim(c.morada) AS moradacliente,
Rtrim(c.codpost) AS codpostcliente,
Rtrim(c.local) AS localcliente,
'4524-909' AS codpostfornecedor,
(SELECT COUNT(*) FROM bi (nolock) WHERE bi.bostamp = @Stamp) AS numlinhas,
(select cl.eancl from cl (nolock) where cl.no = c.no and cl.estab = 0) AS eancliente,
5600000561737 AS eanfornecedor,
Rtrim(c.obs) AS observacoes,
cl.glncl as glnentrega,--Lines Details
(
SELECT 1 AS Versao,
ROW_NUMBER() OVER (ORDER BY Linh.lordem asc) AS LineNumber,
Rtrim(Linh.codigo) AS codean,
Rtrim(Linh.litem) AS refcliente,
Rtrim(Linh.ref) AS referencia,
Rtrim(Linh.design) AS descricao,
Linh.unidad2 AS unidalternativa,
Linh.ettdeb AS precoliquido,
Linh.qtt AS quantidade,
Linh.uni2qtt AS quantalternativa ,
--TAX Percent and Value for each line
(
SELECT TOP 1
ROW_NUMBER() OVER (ORDER BY LinhDet.lordem asc) AS LineNumberTax,
LinhDet.tabiva AS [Tabiva], LinhDet.iva AS [IVA]
FROM bi LinhDet INNER JOIN bi Linh ON Linh.lordem = LinhDet.Lordem AND Linh.bistamp = LinhDet.bistamp
WHERE LinhDet.bostamp = @Stamp
FOR XML PATH('Taxes'),TYPE
),
--Original Source Document
(
SELECT TOP 1
ROW_NUMBER() OVER (ORDER BY LinhREF.lordem asc) AS LineNumberDetalhe,
convert(datetime, LinhREF.dataobra, 126)AS [refdata],
'Customer Order' AS [refnomedoc],
999999 AS [refnumdoc], 5 AS [reftipodoc]
FROM bi LinhREF INNER JOIN bi Linh ON Linh.lordem = LinhREF.Lordem AND Linh.bistamp = LinhREF.bistamp
WHERE LinhREF.bostamp = @Stamp
FOR XML PATH('SourceDocument'),TYPE
)FROM BI Linh (NOLOCK)
WHERE Linh.bostamp = @Stamp
FOR XML PATH('Lines'),TYPE
)
FROM bo c (NOLOCK)
LEFT JOIN cl (nolock) ON c.no = cl.no AND cl.estab = 0
where c.bostamp = @Stamp
FOR XML PATH('Documento'),ROOT('VFPDATASET'),TYPE) );
select @XMLSTR = N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>' + CAST(@XML AS NVARCHAR(MAX))SELECT convert(NVARCHAR(MAX) , @XMLSTR ,1) AS meuXml;
--EXEC uspGetXML_InputcomDetalhe 'SEG15022343671,411000002'
my atual XML File:
<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>
- <VFPDATASET>
- <Documento>
<Versao>1</Versao>
<coddoc>220</coddoc>
<tipodoc>5</tipodoc>
<codmoeda>EUR</codmoeda>
<numdoc>139425</numdoc>
<datadoc>2015-02-23T00:00:00</datadoc>
<localentrega>TOYS R US IBERIA, S.A.</localentrega>
<moradaentrega>POLIGONO IDUSTRIAL ALCALÃ OESTE CTRA, M-300, KM 29.800</moradaentrega>
<loclocalalentrega>ALCLÃ DE HENARES</loclocalalentrega>
<codpostentrega>28802 MADRID - ESPAÑA</codpostentrega>
<dataentrega>2015-02-23T00:00:00</dataentrega>
<datalimite>2015-02-23T00:00:00</datalimite>
<glnfornecedor>5600000561737</glnfornecedor>
<nomefornecedor>My Company , Lda</nomefornecedor>
<moradafornecedor>Zona Ind. do Roligo</moradafornecedor>
<ncontfornecedor>501167323</ncontfornecedor>
<glncliente>8421703000012</glncliente>
<ncontcliente>A79520656</ncontcliente>
<nomecliente>TOYS R US IBERIA, S.A.</nomecliente>
<moradacliente>POLIGONO IDUSTRIAL ALCALÃ OESTE CTRA, M-300, KM 29.800</moradacliente>
<codpostcliente>28802 MADRID - ESPAÑA</codpostcliente>
<localcliente>ALCLÃ DE HENARES</localcliente>
<codpostfornecedor>4524-909</codpostfornecedor>
<numlinhas>7</numlinhas>
<eancliente>8421703000012</eancliente>
<eanfornecedor>5600000561737</eanfornecedor>
<observacoes>.</observacoes>
<glnentrega>8421703000012</glnentrega>
- <Lines>
<Versao>1</Versao>
<LineNumber>1</LineNumber>
<codean>2000021311701</codean>
<refcliente>247790</refcliente>
<referencia>2131170</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>2</LineNumber>
<codean>2000021311718</codean>
<refcliente>247793</refcliente>
<referencia>2131171</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>3</LineNumber>
<codean>2000021339316</codean>
<refcliente>247801</refcliente>
<referencia>2133931</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>4</LineNumber>
<codean>2000021339309</codean>
<refcliente>247804</refcliente>
<referencia>2133930</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>5</LineNumber>
<codean>2000021410107</codean>
<refcliente>247807</refcliente>
<referencia>2141010</referencia>
<descricao>Sapato Lona INFANTIL 20/26 Cx14</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>878.430000</precoliquido>
<quantidade>210.0000</quantidade>
<quantalternativa>15.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>6</LineNumber>
<codean>2000021425019</codean>
<refcliente>247866</refcliente>
<referencia>2142500-TOYS</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>1095.450000</precoliquido>
<quantidade>156.0000</quantidade>
<quantalternativa>13.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>7</LineNumber>
<codean>2000021411821</codean>
<refcliente>247860</refcliente>
<referencia>2141182</referencia>
<descricao>Sapato Lona INFANTIL 25/30 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>599.150000</precoliquido>
<quantidade>120.0000</quantidade>
<quantalternativa>10.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
</Documento>
</VFPDATASET>My expect XML result with the same LineNumber on Subnodes LineNumberTax and LineNumberDetalhe :
<?xml version="1.0" encoding="Windows-1252" standalone="yes" ?>
- <VFPDATASET>
- <Documento>
<Versao>1</Versao>
<coddoc>220</coddoc>
<tipodoc>5</tipodoc>
<codmoeda>EUR</codmoeda>
<numdoc>139425</numdoc>
<datadoc>2015-02-23T00:00:00</datadoc>
<localentrega>TOYS R US IBERIA, S.A.</localentrega>
<moradaentrega>POLIGONO IDUSTRIAL ALCALÃ OESTE CTRA, M-300, KM 29.800</moradaentrega>
<loclocalalentrega>ALCLÃ DE HENARES</loclocalalentrega>
<codpostentrega>28802 MADRID - ESPAÑA</codpostentrega>
<dataentrega>2015-02-23T00:00:00</dataentrega>
<datalimite>2015-02-23T00:00:00</datalimite>
<glnfornecedor>5600000561737</glnfornecedor>
<nomefornecedor>My Company , Lda</nomefornecedor>
<moradafornecedor>Zona Ind. do Roligo</moradafornecedor>
<ncontfornecedor>501167323</ncontfornecedor>
<glncliente>8421703000012</glncliente>
<ncontcliente>A79520656</ncontcliente>
<nomecliente>TOYS R US IBERIA, S.A.</nomecliente>
<moradacliente>POLIGONO IDUSTRIAL ALCALÃ OESTE CTRA, M-300, KM 29.800</moradacliente>
<codpostcliente>28802 MADRID - ESPAÑA</codpostcliente>
<localcliente>ALCLÃ DE HENARES</localcliente>
<codpostfornecedor>4524-909</codpostfornecedor>
<numlinhas>7</numlinhas>
<eancliente>8421703000012</eancliente>
<eanfornecedor>5600000561737</eanfornecedor>
<observacoes>.</observacoes>
<glnentrega>8421703000012</glnentrega>
- <Lines>
<Versao>1</Versao>
<LineNumber>1</LineNumber>
<codean>2000021311701</codean>
<refcliente>247790</refcliente>
<referencia>2131170</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
- <Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>1</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>2</LineNumber>
<codean>2000021311718</codean>
<refcliente>247793</refcliente>
<referencia>2131171</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
- <Taxes>
<LineNumberTax>2</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>2</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>3</LineNumber>
<codean>2000021339316</codean>
<refcliente>247801</refcliente>
<referencia>2133931</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
- <Taxes>
<LineNumberTax>3</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>3</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>4</LineNumber>
<codean>2000021339309</codean>
<refcliente>247804</refcliente>
<referencia>2133930</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
- <Taxes>
<LineNumberTax>4</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>4</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>5</LineNumber>
<codean>2000021410107</codean>
<refcliente>247807</refcliente>
<referencia>2141010</referencia>
<descricao>Sapato Lona INFANTIL 20/26 Cx14</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>878.430000</precoliquido>
<quantidade>210.0000</quantidade>
<quantalternativa>15.0000</quantalternativa>
- <Taxes>
<LineNumberTax>5</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>5</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>6</LineNumber>
<codean>2000021425019</codean>
<refcliente>247866</refcliente>
<referencia>2142500-TOYS</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>1095.450000</precoliquido>
<quantidade>156.0000</quantidade>
<quantalternativa>13.0000</quantalternativa>
- <Taxes>
<LineNumberTax>6</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>6</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
- <Lines>
<Versao>1</Versao>
<LineNumber>7</LineNumber>
<codean>2000021411821</codean>
<refcliente>247860</refcliente>
<referencia>2141182</referencia>
<descricao>Sapato Lona INFANTIL 25/30 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>599.150000</precoliquido>
<quantidade>120.0000</quantidade>
<quantalternativa>10.0000</quantalternativa>
- <Taxes>
<LineNumberTax>7</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
- <SourceDocument>
<LineNumberDetalhe>7</LineNumberDetalhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
</Documento>
</VFPDATASET>Code for creating temporary table to test:
SELECT * INTO
#myOrderCab
FROM (
SELECT
1 AS Versao,
220 AS coddoc,
5 AS tipodoc,
'EUR' as codmoeda,
c.obrano AS numdoc,
c.dataobra AS datadoc,
Rtrim(c.nome) AS localentrega,
Rtrim(c.morada) AS moradaentrega,
Rtrim(c.local) AS loclocalalentrega,
Rtrim(c.codpost) AS codpostentrega,
c.datafinal AS dataentrega,
c.datafinal AS datalimite,
5600000561737 AS glnfornecedor,
'My Company , Lda' AS nomefornecedor,
'Zona Ind. do Roligo' AS moradafornecedor,
501167323 as ncontfornecedor,
cl.glncl as glncliente,
cl.ncont AS ncontcliente,
Rtrim(c.nome) AS nomecliente,
Rtrim(c.morada) AS moradacliente,
Rtrim(c.codpost) AS codpostcliente,
Rtrim(c.local) AS localcliente,
'4524-909' AS codpostfornecedor,
(SELECT COUNT(*) FROM bi (nolock) WHERE bi.bostamp = 'SEG15022343671,411000002') AS numlinhas,
(select cl.eancl from cl (nolock) where cl.no = c.no and cl.estab = 0) AS eancliente,
5600000561737 AS eanfornecedor,
Rtrim(c.obs) AS observacoes,
cl.glncl as glnentregaFROM bo c (NOLOCK)
LEFT JOIN cl (nolock) ON c.no = cl.no AND cl.estab = 0
where c.bostamp = 'SEG15022343671,411000002'
)x--Lines
SELECT * INTO
#myOrderLines
FROM (
SELECT 1 AS Versao,
ROW_NUMBER() OVER (ORDER BY Linh.lordem asc) AS LineNumber,
Rtrim(Linh.codigo) AS codean,
Rtrim(Linh.litem) AS refcliente,
Rtrim(Linh.ref) AS referencia,
Rtrim(Linh.design) AS descricao,
Linh.unidad2 AS unidalternativa,
Linh.ettdeb AS precoliquido,
Linh.qtt AS quantidade,
Linh.uni2qtt AS quantalternativa ,
Linh.tabiva AS Tabiva,
Linh.iva AS [IVA],
Linh.lordem,
Linh.bistamp,
Linh.bostamp,
convert(datetime, Linh.dataobra, 126)AS [refdata],
'Customer Order' AS [refnomedoc],
999999 AS [refnumdoc], 5 AS [reftipodoc]
FROM BI Linh (NOLOCK) INNER JOIN BO c
ON Linh.bostamp = c.bostamp
WHERE Linh.bostamp = 'SEG15022343671,411000002'
)zMany thanks,
Best regards,
LS
You've been around long enough to know that we can't run scripts that reference your tables. We can't create your sample data, because we don't have access to BO, CL, or BI.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2018 at 12:48 pm
Hello Drew,
my apologies, you are quite right, here are the insert to create the 3 tables with data:
Table BO :
INSERT INTO dbo.bo (bostamp, nmdos, obrano, dataobra, nome, totaldeb, tipo, datafinal, smoe4, smoe3, smoe2, smoe1, moetotal, sdeb2, sdeb1, sdeb4, sdeb3, sqtt14, sqtt13, sqtt12, sqtt11, sqtt24, sqtt23, sqtt22, sqtt21, vqtt24, vqtt23, vqtt22, vqtt21, vendedor, vendnm, stot1, stot2, stot3, stot4, no, obranome, boano, dataopen, datafecho, fechada, nopat, total, tecnico, tecnnm, nomquina, maquina, marca, serie, zona, obs, trab1, trab2, trab3, trab4, trab5, ndos, custo, moeda, estab, morada, local, codpost, ultfact, period, tabela1, ncont, logi1, logi2, logi3, logi4, logi5, logi6, logi7, logi8, segmento, impresso, fref, ccusto, cobranca, infref, lifref, esdeb1, esdeb2, esdeb3, esdeb4, evqtt21, evqtt22, evqtt23, evqtt24, estot1, estot2, estot3, estot4, etotal, etotaldeb, ecusto, bo_2tdesc1, bo_2tdesc2, ebo_2tdes1, ebo_2tdes2, bo_1tvall, bo_2tvall, ebo_1tvall, ebo_2tvall, bo11_bins, bo11_iva, ebo11_bins, ebo11_iva, bo21_bins, bo21_iva, ebo21_bins, ebo21_iva, bo31_bins, bo31_iva, ebo31_bins, ebo31_iva, bo41_bins, bo41_iva, ebo41_bins, ebo41_iva, bo51_bins, bo51_iva, ebo51_bins, ebo51_iva, bo61_bins, bo61_iva, ebo61_bins, ebo61_iva, bo12_bins, bo12_iva, ebo12_bins, ebo12_iva, bo22_bins, bo22_iva, ebo22_bins, ebo22_iva, bo32_bins, bo32_iva, ebo32_bins, ebo32_iva, bo42_bins, bo42_iva, ebo42_bins, ebo42_iva, bo52_bins, bo52_iva, ebo52_bins, ebo52_iva, bo62_bins, bo62_iva, ebo62_bins, ebo62_iva, bo_totp1, bo_totp2, ebo_totp1, ebo_totp2, edi, memissao, nome2, pastamp, snstamp, mastamp, origem, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, orinopat, iiva, iunit, itotais, iunitiva, itotaisiva, site, descc, edescc, pnome, pno, cxstamp, cxusername, ssstamp, ssusername, alldescli, alldesfor, series, ncusto, series2, quarto, ocupacao, tabela2, obstab2, iemail, inome, situacao, lang, ean, iecacodisen, boclose, dtclose, u_totdesc, u_totenc, userimpresso, tpstamp, tpdesc, statuspda, emconf, aprovado, u_glnfac, u_refapi, u_refzzz, u_numped)
VALUES ('SEG15022343671,411000002', 'Ordem de Separação', 139425, '2015-02-23', 'TOYS R US IBERIA, S.A.', 1050190.87506, '0%', '2015-02-23', 0, 0, 0, 0, 0, 0, 0, 1050190.87506, 0, 1374, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 'ADMINISTRAÇAO', 0, 0, 0, 0, 20640, '592641', 2015, '2015-03-30', '2015-03-26 10:19:53.49', 1, 0, 0, 3, '', 0, '', '', '', 'ESP- ADMINIST ', '.', '', '', '', '', '', 7, 0, 'PTE ou EURO', 0, 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', 'ALCLÁ DE HENARES', '28802 MADRID - ESPAÑA', '2015-03-26', 0, 'AZKAR', 'A79520656 ', 0, 0, 0, 0, 0, 0, 0, 0, 'ESPANHA', 1, ' ', ' ', ' ', 0, 0, 0, 0, 0, 5238.33, 0, 0, 0, 0, 0, 0, 0, 0, 0, 5238.33, 0, 129798.06126, 0, 647.43, 0, 0, 1050190.87506, 0, 5238.33, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1050190.87506, 0, 5238.33, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1179988.93632, 0, 5885.76, 0, 'EURO', ' ', '', '', '', 'BO', 'SEG', '2015-02-23', '12:08:27', 'SEG', '2015-03-10', '18:05:33', 0, 0, 0, 0, 0, 0, 0, '', 129798.06126, 647.43, '', 0, '', '', '', '', 0, 0, '', ' ', '', '', 3, '', '', ' ', 'Sergio Magalhães', 0, '', '8421703000012', '', 0, '1900-01-01', 0, 0, 'Sergio Magalhães ', 'JM07111339317,315812509', '90 DAYS ', '', 0, 0, '', '', '', 0)
GO
Table BI :
INSERT INTO dbo.bi (bistamp, obrano, ref, design, qtt, qtt2, iva, desconto, tabiva, armazem, pu, debito, prorc, stipo, no, pcusto, serie, nomquina, nopat, fno, fdata, nmdoc, ndoc, nmdos, ndos, forref, txiva, rdata, lobs, ldossier, obranome, fechada, datafinal, dataobra, dataopen, datafecho, tecnico, maquina, marca, zona, litem, vumoeda, resfor, rescli, resrec, iprint, lobs2, litem2, lobs3, estab, resusr, ar2mazem, composto, lrecno, lordem, fmarcada, producao, local, morada, codpost, nome, desc2, vendedor, vendnm, tabfor, tabela1, descli, reff, lote, bostamp, ivaincl, cor, tam, segmento, bofref, bifref, grau, partes, altura, largura, espessura, biserie, infref, lifref, uni2qtt, epu, edebito, eprorc, epcusto, ttdeb, ettdeb, ttmoeda, adoc, binum1, binum2, codigo, cpoc, stns, obistamp, usr1, usr2, usr3, usr4, usr5, usalote, texteis, unidade, unidad2, oftstamp, ofostamp, promo, familia, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, usr6, sattotal, noserie, ncmassa, ncunsup, ncvest, encvest, nccod, ncinteg, dedata, atedata, classif, posic, classifc, desc3, desc4, desc5, desc6, series, oobistamp, epromo, series2, ccusto, ncusto, num1, slvu, eslvu, sltt, esltt, slvumoeda, slttmoeda, fechabo, oobostamp, ltab1, ltab2, ltab3, ltab4, ltab5, fami, pctfami, adjudicada, tieca, etieca, mtieca, volume, iecasug, iecagrad, iecacodisen, peso, pbruto, codfiscal, boclose, dtclose, u_starma, u_stdisp, u_stdispcx, u_stenc, u_stres, dgeral, temoci, temomi, temsubemp, encargo, eencargo, custoind, ecustoind, tiposemp, pvok, quarto, partes2, efornecedor, efornec, efornestab, cativo, optstamp, oristamp, compostoori, emconf, temeco, ecoval, eecoval, tecoval, etecoval, ecoval2, eecoval2, tecoval2, etecoval2, econotcalc, mntencargos, debitoori, edebitoori, trocaequi, tpromo, valdesc, evaldesc, u_idcor, u_caixas, u_myvolume, u_pesoliq)
VALUES ('SEG15022343704,2230000-3', 139425, '2131170', 'Clogs BEBE 18/24 Cx12', 312, 312, 0, 11, 1, 1, 0, 641.5424, 0, 4, 20640, 0, ' ', 0, 0, 1709, '2015-03-26', 'Factura', 1, 'Ordem de Separação', 7, '247790', 0, '2015-02-23', ' ', 0, '592641', 1, '2015-02-23', '2015-02-23', '2015-02-23', '2015-03-26 10:19:53.5', 3, ' ', ' ', 'ESP- ADMINIST ', '247790 ', 0, 0, 1, 0, 0, ' ', ' ', ' ', 0, 0, 0, 0, ' ', 30000, 0, 0, 'ALCLÁ DE HENARES', 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', '28802 MADRID - ESPAÑA', 'TOYS R US IBERIA, S.A.', 0, 2, 'ADMINISTRAÇAO', 0, 'Transportadora', 0, ' ', ' ', 'SEG15022343671,411000002', 0, ' ', ' ', 'ESPANHA', ' ', ' ', 0, 0, 0, 0, 0, ' ', 0, 0, 26, 0, 3.2, 0, 0, 178143, 888.58, 0, '1709', 0, 0, '2000021311701 ', 0, 0, 'SEG14120351326,796000002', '12', ' ', 'Primavera/Verão 2014', '64029991', ' ', 0, 0, 'PAR', 'CT', 'jm15032637179,769119027', ' ', 0, '314', 'SEG', '2015-02-23', '12:08:23', 'SEG', '2015-02-23', '12:08:23', 0, ' ', 0, 0, 0, 0, 0, 0, ' ', ' ', '1900-01-01', '2015-02-23', 0, ' ', ' ', 0, 0, 0, 0, '', 'SEG14120351326,796000002', 0, '', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, ' ', 0, '1900-01-01', 704, 956, 79.67, 252, 348, '', 0, 0, 0, 0, 0, 0, 0, '', 0, ' ', 0, ' ', 0, 0, 0, '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 26, 0.65, 41.5)
GO
INSERT INTO dbo.bi (bistamp, obrano, ref, design, qtt, qtt2, iva, desconto, tabiva, armazem, pu, debito, prorc, stipo, no, pcusto, serie, nomquina, nopat, fno, fdata, nmdoc, ndoc, nmdos, ndos, forref, txiva, rdata, lobs, ldossier, obranome, fechada, datafinal, dataobra, dataopen, datafecho, tecnico, maquina, marca, zona, litem, vumoeda, resfor, rescli, resrec, iprint, lobs2, litem2, lobs3, estab, resusr, ar2mazem, composto, lrecno, lordem, fmarcada, producao, local, morada, codpost, nome, desc2, vendedor, vendnm, tabfor, tabela1, descli, reff, lote, bostamp, ivaincl, cor, tam, segmento, bofref, bifref, grau, partes, altura, largura, espessura, biserie, infref, lifref, uni2qtt, epu, edebito, eprorc, epcusto, ttdeb, ettdeb, ttmoeda, adoc, binum1, binum2, codigo, cpoc, stns, obistamp, usr1, usr2, usr3, usr4, usr5, usalote, texteis, unidade, unidad2, oftstamp, ofostamp, promo, familia, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, usr6, sattotal, noserie, ncmassa, ncunsup, ncvest, encvest, nccod, ncinteg, dedata, atedata, classif, posic, classifc, desc3, desc4, desc5, desc6, series, oobistamp, epromo, series2, ccusto, ncusto, num1, slvu, eslvu, sltt, esltt, slvumoeda, slttmoeda, fechabo, oobostamp, ltab1, ltab2, ltab3, ltab4, ltab5, fami, pctfami, adjudicada, tieca, etieca, mtieca, volume, iecasug, iecagrad, iecacodisen, peso, pbruto, codfiscal, boclose, dtclose, u_starma, u_stdisp, u_stdispcx, u_stenc, u_stres, dgeral, temoci, temomi, temsubemp, encargo, eencargo, custoind, ecustoind, tiposemp, pvok, quarto, partes2, efornecedor, efornec, efornestab, cativo, optstamp, oristamp, compostoori, emconf, temeco, ecoval, eecoval, tecoval, etecoval, ecoval2, eecoval2, tecoval2, etecoval2, econotcalc, mntencargos, debitoori, edebitoori, trocaequi, tpromo, valdesc, evaldesc, u_idcor, u_caixas, u_myvolume, u_pesoliq)
VALUES ('SEG15022343704,2700000-4', 139425, '2131171', 'Clogs BEBE 18/24 Cx12', 312, 312, 0, 11, 1, 1, 0, 641.5424, 0, 4, 20640, 0, ' ', 0, 0, 1709, '2015-03-26', 'Factura', 1, 'Ordem de Separação', 7, '247793', 0, '2015-02-23', ' ', 0, '592641', 1, '2015-02-23', '2015-02-23', '2015-02-23', '2015-03-26 10:19:53.523', 3, ' ', ' ', 'ESP- ADMINIST ', '247793 ', 0, 0, 1, 0, 0, ' ', ' ', ' ', 0, 0, 0, 0, ' ', 40000, 0, 0, 'ALCLÁ DE HENARES', 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', '28802 MADRID - ESPAÑA', 'TOYS R US IBERIA, S.A.', 0, 2, 'ADMINISTRAÇAO', 0, 'Transportadora', 0, ' ', ' ', 'SEG15022343671,411000002', 0, ' ', ' ', 'ESPANHA', ' ', ' ', 0, 0, 0, 0, 0, ' ', 0, 0, 26, 0, 3.2, 0, 0, 178143, 888.58, 0, '1709', 0, 0, '2000021311718 ', 0, 0, 'SEG14120351326,843000003', '12', ' ', 'Primavera/Verão 2014', '64029991', ' ', 0, 0, 'PAR', 'CT', 'jm15032637179,769119027', ' ', 0, '314', 'SEG', '2015-02-23', '12:08:23', 'SEG', '2015-02-23', '12:08:23', 0, ' ', 0, 0, 0, 0, 0, 0, ' ', ' ', '1900-01-01', '2015-02-23', 0, ' ', ' ', 0, 0, 0, 0, '', 'SEG14120351326,843000003', 0, '', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, ' ', 0, '1900-01-01', 703, 919, 76.58, 216, 360, '', 0, 0, 0, 0, 0, 0, 0, '', 0, ' ', 0, ' ', 0, 0, 0, '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 26, 0.65, 41.5)
GO
INSERT INTO dbo.bi (bistamp, obrano, ref, design, qtt, qtt2, iva, desconto, tabiva, armazem, pu, debito, prorc, stipo, no, pcusto, serie, nomquina, nopat, fno, fdata, nmdoc, ndoc, nmdos, ndos, forref, txiva, rdata, lobs, ldossier, obranome, fechada, datafinal, dataobra, dataopen, datafecho, tecnico, maquina, marca, zona, litem, vumoeda, resfor, rescli, resrec, iprint, lobs2, litem2, lobs3, estab, resusr, ar2mazem, composto, lrecno, lordem, fmarcada, producao, local, morada, codpost, nome, desc2, vendedor, vendnm, tabfor, tabela1, descli, reff, lote, bostamp, ivaincl, cor, tam, segmento, bofref, bifref, grau, partes, altura, largura, espessura, biserie, infref, lifref, uni2qtt, epu, edebito, eprorc, epcusto, ttdeb, ettdeb, ttmoeda, adoc, binum1, binum2, codigo, cpoc, stns, obistamp, usr1, usr2, usr3, usr4, usr5, usalote, texteis, unidade, unidad2, oftstamp, ofostamp, promo, familia, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, usr6, sattotal, noserie, ncmassa, ncunsup, ncvest, encvest, nccod, ncinteg, dedata, atedata, classif, posic, classifc, desc3, desc4, desc5, desc6, series, oobistamp, epromo, series2, ccusto, ncusto, num1, slvu, eslvu, sltt, esltt, slvumoeda, slttmoeda, fechabo, oobostamp, ltab1, ltab2, ltab3, ltab4, ltab5, fami, pctfami, adjudicada, tieca, etieca, mtieca, volume, iecasug, iecagrad, iecacodisen, peso, pbruto, codfiscal, boclose, dtclose, u_starma, u_stdisp, u_stdispcx, u_stenc, u_stres, dgeral, temoci, temomi, temsubemp, encargo, eencargo, custoind, ecustoind, tiposemp, pvok, quarto, partes2, efornecedor, efornec, efornestab, cativo, optstamp, oristamp, compostoori, emconf, temeco, ecoval, eecoval, tecoval, etecoval, ecoval2, eecoval2, tecoval2, etecoval2, econotcalc, mntencargos, debitoori, edebitoori, trocaequi, tpromo, valdesc, evaldesc, u_idcor, u_caixas, u_myvolume, u_pesoliq)
VALUES ('SEG15022343704,3170000-5', 139425, '2133931', 'Sapato Lona INFANTIL 22/28 Cx12', 132, 132, 0, 11, 1, 1, 0, 757.82196, 0, 4, 20640, 0, ' ', 0, 0, 1709, '2015-03-26', 'Factura', 1, 'Ordem de Separação', 7, '247801', 0, '2015-02-23', ' ', 0, '592641', 1, '2015-02-23', '2015-02-23', '2015-02-23', '2015-03-26 10:19:53.527', 3, ' ', ' ', 'ESP- ADMINIST ', '247801 ', 0, 0, 1, 0, 0, ' ', ' ', ' ', 0, 0, 0, 0, ' ', 50000, 0, 0, 'ALCLÁ DE HENARES', 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', '28802 MADRID - ESPAÑA', 'TOYS R US IBERIA, S.A.', 0, 2, 'ADMINISTRAÇAO', 0, 'Transportadora', 0, ' ', ' ', 'SEG15022343671,411000002', 0, ' ', ' ', 'ESPANHA', ' ', ' ', 0, 0, 0, 0, 0, ' ', 0, 0, 11, 0, 3.78, 0, 0, 89029, 444.07, 0, '1709', 0, 0, '2000021339316 ', 0, 0, 'SEG14120351326,905000004', '12', ' ', 'Primavera/Verão 2014', '64041990', ' ', 0, 0, 'PAR', 'CT', 'jm15032637179,769119027', ' ', 0, '337', 'SEG', '2015-02-23', '12:08:23', 'SEG', '2015-02-23', '12:08:23', 0, ' ', 0, 0, 0, 0, 0, 0, ' ', ' ', '1900-01-01', '2015-02-23', 0, ' ', ' ', 0, 0, 0, 0, '', 'SEG14120351326,905000004', 0, '', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, ' ', 0, '1900-01-01', 6, 6, 0.5, 0, 162, '', 0, 0, 0, 0, 0, 0, 0, '', 0, ' ', 0, ' ', 0, 0, 0, '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 11, 0.451, 50.16)
GO
INSERT INTO dbo.bi (bistamp, obrano, ref, design, qtt, qtt2, iva, desconto, tabiva, armazem, pu, debito, prorc, stipo, no, pcusto, serie, nomquina, nopat, fno, fdata, nmdoc, ndoc, nmdos, ndos, forref, txiva, rdata, lobs, ldossier, obranome, fechada, datafinal, dataobra, dataopen, datafecho, tecnico, maquina, marca, zona, litem, vumoeda, resfor, rescli, resrec, iprint, lobs2, litem2, lobs3, estab, resusr, ar2mazem, composto, lrecno, lordem, fmarcada, producao, local, morada, codpost, nome, desc2, vendedor, vendnm, tabfor, tabela1, descli, reff, lote, bostamp, ivaincl, cor, tam, segmento, bofref, bifref, grau, partes, altura, largura, espessura, biserie, infref, lifref, uni2qtt, epu, edebito, eprorc, epcusto, ttdeb, ettdeb, ttmoeda, adoc, binum1, binum2, codigo, cpoc, stns, obistamp, usr1, usr2, usr3, usr4, usr5, usalote, texteis, unidade, unidad2, oftstamp, ofostamp, promo, familia, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, usr6, sattotal, noserie, ncmassa, ncunsup, ncvest, encvest, nccod, ncinteg, dedata, atedata, classif, posic, classifc, desc3, desc4, desc5, desc6, series, oobistamp, epromo, series2, ccusto, ncusto, num1, slvu, eslvu, sltt, esltt, slvumoeda, slttmoeda, fechabo, oobostamp, ltab1, ltab2, ltab3, ltab4, ltab5, fami, pctfami, adjudicada, tieca, etieca, mtieca, volume, iecasug, iecagrad, iecacodisen, peso, pbruto, codfiscal, boclose, dtclose, u_starma, u_stdisp, u_stdispcx, u_stenc, u_stres, dgeral, temoci, temomi, temsubemp, encargo, eencargo, custoind, ecustoind, tiposemp, pvok, quarto, partes2, efornecedor, efornec, efornestab, cativo, optstamp, oristamp, compostoori, emconf, temeco, ecoval, eecoval, tecoval, etecoval, ecoval2, eecoval2, tecoval2, etecoval2, econotcalc, mntencargos, debitoori, edebitoori, trocaequi, tpromo, valdesc, evaldesc, u_idcor, u_caixas, u_myvolume, u_pesoliq)
VALUES ('SEG15022343704,3480000-6', 139425, '2133930', 'Sapato Lona INFANTIL 22/28 Cx12', 132, 132, 0, 11, 1, 1, 0, 757.82196, 0, 4, 20640, 0, ' ', 0, 0, 1709, '2015-03-26', 'Factura', 1, 'Ordem de Separação', 7, '247804', 0, '2015-02-23', ' ', 0, '592641', 1, '2015-02-23', '2015-02-23', '2015-02-23', '2015-03-26 10:19:53.53', 3, ' ', ' ', 'ESP- ADMINIST ', '247804 ', 0, 0, 1, 0, 0, ' ', ' ', ' ', 0, 0, 0, 0, ' ', 60000, 0, 0, 'ALCLÁ DE HENARES', 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', '28802 MADRID - ESPAÑA', 'TOYS R US IBERIA, S.A.', 0, 2, 'ADMINISTRAÇAO', 0, 'Transportadora', 0, ' ', ' ', 'SEG15022343671,411000002', 0, ' ', ' ', 'ESPANHA', ' ', ' ', 0, 0, 0, 0, 0, ' ', 0, 0, 11, 0, 3.78, 0, 0, 89029, 444.07, 0, '1709', 0, 0, '2000021339309 ', 0, 0, 'SEG14120351327,108000005', '12', ' ', 'Primavera/Verão 2014', '64041990', ' ', 0, 0, 'PAR', 'CT', 'jm15032637179,769119027', ' ', 0, '337', 'SEG', '2015-02-23', '12:08:23', 'SEG', '2015-02-23', '12:08:23', 0, ' ', 0, 0, 0, 0, 0, 0, ' ', ' ', '1900-01-01', '2015-02-23', 0, ' ', ' ', 0, 0, 0, 0, '', 'SEG14120351327,108000005', 0, '', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, ' ', 0, '1900-01-01', 76, 124, 10.33, 48, 206, '', 0, 0, 0, 0, 0, 0, 0, '', 0, ' ', 0, ' ', 0, 0, 0, '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 11, 0.451, 50.16)
GO
INSERT INTO dbo.bi (bistamp, obrano, ref, design, qtt, qtt2, iva, desconto, tabiva, armazem, pu, debito, prorc, stipo, no, pcusto, serie, nomquina, nopat, fno, fdata, nmdoc, ndoc, nmdos, ndos, forref, txiva, rdata, lobs, ldossier, obranome, fechada, datafinal, dataobra, dataopen, datafecho, tecnico, maquina, marca, zona, litem, vumoeda, resfor, rescli, resrec, iprint, lobs2, litem2, lobs3, estab, resusr, ar2mazem, composto, lrecno, lordem, fmarcada, producao, local, morada, codpost, nome, desc2, vendedor, vendnm, tabfor, tabela1, descli, reff, lote, bostamp, ivaincl, cor, tam, segmento, bofref, bifref, grau, partes, altura, largura, espessura, biserie, infref, lifref, uni2qtt, epu, edebito, eprorc, epcusto, ttdeb, ettdeb, ttmoeda, adoc, binum1, binum2, codigo, cpoc, stns, obistamp, usr1, usr2, usr3, usr4, usr5, usalote, texteis, unidade, unidad2, oftstamp, ofostamp, promo, familia, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, usr6, sattotal, noserie, ncmassa, ncunsup, ncvest, encvest, nccod, ncinteg, dedata, atedata, classif, posic, classifc, desc3, desc4, desc5, desc6, series, oobistamp, epromo, series2, ccusto, ncusto, num1, slvu, eslvu, sltt, esltt, slvumoeda, slttmoeda, fechabo, oobostamp, ltab1, ltab2, ltab3, ltab4, ltab5, fami, pctfami, adjudicada, tieca, etieca, mtieca, volume, iecasug, iecagrad, iecacodisen, peso, pbruto, codfiscal, boclose, dtclose, u_starma, u_stdisp, u_stdispcx, u_stenc, u_stres, dgeral, temoci, temomi, temsubemp, encargo, eencargo, custoind, ecustoind, tiposemp, pvok, quarto, partes2, efornecedor, efornec, efornestab, cativo, optstamp, oristamp, compostoori, emconf, temeco, ecoval, eecoval, tecoval, etecoval, ecoval2, eecoval2, tecoval2, etecoval2, econotcalc, mntencargos, debitoori, edebitoori, trocaequi, tpromo, valdesc, evaldesc, u_idcor, u_caixas, u_myvolume, u_pesoliq)
VALUES ('SEG15022343704,3950000-7', 139425, '2141010', 'Sapato Lona INFANTIL 20/26 Cx14', 210, 210, 0, 11, 1, 1, 0, 942.2654, 0, 4, 20640, 0, ' ', 0, 0, 1709, '2015-03-26', 'Factura', 1, 'Ordem de Separação', 7, '247807', 0, '2015-02-23', ' ', 0, '592641', 1, '2015-02-23', '2015-02-23', '2015-02-23', '2015-03-26 10:19:53.533', 3, ' ', ' ', 'ESP- ADMINIST ', '247807 ', 0, 0, 1, 0, 0, ' ', ' ', ' ', 0, 0, 0, 0, ' ', 70000, 0, 0, 'ALCLÁ DE HENARES', 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', '28802 MADRID - ESPAÑA', 'TOYS R US IBERIA, S.A.', 0, 2, 'ADMINISTRAÇAO', 0, 'Transportadora', 0, ' ', ' ', 'SEG15022343671,411000002', 0, ' ', ' ', 'ESPANHA', ' ', ' ', 0, 0, 0, 0, 0, ' ', 0, 0, 15, 0, 4.7, 0, 0, 176109, 878.43, 0, '1709', 0, 0, '2000021410107 ', 0, 0, 'SEG14120351327,171000006', '14', ' ', 'Primavera/Verão 2015', ' ', ' ', 0, 0, 'PAR', 'CT', 'jm15032637179,769119027', ' ', 0, '337', 'SEG', '2015-02-23', '12:08:23', 'SEG', '2015-02-23', '12:08:23', 0, ' ', 0, 0, 0, 0, 0, 0, ' ', ' ', '1900-01-01', '2015-02-23', 0, ' ', ' ', 0, 0, 0, 0, '', 'SEG14120351327,171000006', 0, '', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, ' ', 0, '1900-01-01', 1833, -1, -0.07, 266, 490, '', 0, 0, 0, 0, 0, 0, 0, '', 0, ' ', 0, ' ', 0, 0, 0, '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 15, 0.72, 77.7)
GO
INSERT INTO dbo.bi (bistamp, obrano, ref, design, qtt, qtt2, iva, desconto, tabiva, armazem, pu, debito, prorc, stipo, no, pcusto, serie, nomquina, nopat, fno, fdata, nmdoc, ndoc, nmdos, ndos, forref, txiva, rdata, lobs, ldossier, obranome, fechada, datafinal, dataobra, dataopen, datafecho, tecnico, maquina, marca, zona, litem, vumoeda, resfor, rescli, resrec, iprint, lobs2, litem2, lobs3, estab, resusr, ar2mazem, composto, lrecno, lordem, fmarcada, producao, local, morada, codpost, nome, desc2, vendedor, vendnm, tabfor, tabela1, descli, reff, lote, bostamp, ivaincl, cor, tam, segmento, bofref, bifref, grau, partes, altura, largura, espessura, biserie, infref, lifref, uni2qtt, epu, edebito, eprorc, epcusto, ttdeb, ettdeb, ttmoeda, adoc, binum1, binum2, codigo, cpoc, stns, obistamp, usr1, usr2, usr3, usr4, usr5, usalote, texteis, unidade, unidad2, oftstamp, ofostamp, promo, familia, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, usr6, sattotal, noserie, ncmassa, ncunsup, ncvest, encvest, nccod, ncinteg, dedata, atedata, classif, posic, classifc, desc3, desc4, desc5, desc6, series, oobistamp, epromo, series2, ccusto, ncusto, num1, slvu, eslvu, sltt, esltt, slvumoeda, slttmoeda, fechabo, oobostamp, ltab1, ltab2, ltab3, ltab4, ltab5, fami, pctfami, adjudicada, tieca, etieca, mtieca, volume, iecasug, iecagrad, iecacodisen, peso, pbruto, codfiscal, boclose, dtclose, u_starma, u_stdisp, u_stdispcx, u_stenc, u_stres, dgeral, temoci, temomi, temsubemp, encargo, eencargo, custoind, ecustoind, tiposemp, pvok, quarto, partes2, efornecedor, efornec, efornestab, cativo, optstamp, oristamp, compostoori, emconf, temeco, ecoval, eecoval, tecoval, etecoval, ecoval2, eecoval2, tecoval2, etecoval2, econotcalc, mntencargos, debitoori, edebitoori, trocaequi, tpromo, valdesc, evaldesc, u_idcor, u_caixas, u_myvolume, u_pesoliq)
VALUES ('SEG15022343704,4260000-8', 139425, '2142500-TOYS', 'Sapato Lona INFANTIL 22/28 Cx12', 156, 156, 0, 11, 1, 1, 0, 1581.80298, 0, 4, 20640, 0, ' ', 0, 0, 1709, '2015-03-26', 'Factura', 1, 'Ordem de Separação', 7, '247866', 0, '2015-02-23', ' ', 0, '592641', 1, '2015-02-23', '2015-02-23', '2015-02-23', '2015-03-26 10:19:53.54', 3, ' ', ' ', 'ESP- ADMINIST ', '247866 ', 0, 0, 1, 0, 0, ' ', ' ', ' ', 0, 0, 0, 0, ' ', 80000, 0, 0, 'ALCLÁ DE HENARES', 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', '28802 MADRID - ESPAÑA', 'TOYS R US IBERIA, S.A.', 0, 2, 'ADMINISTRAÇAO', 0, 'Transportadora', 0, ' ', ' ', 'SEG15022343671,411000002', 0, ' ', ' ', 'ESPANHA', ' ', ' ', 0, 0, 0, 0, 0, ' ', 0, 0, 13, 0, 7.89, 0, 0, 219618, 1095.45, 0, '1709', 0, 0, '2000021425019 ', 0, 0, 'SEG14120351327,436000009', '12', ' ', 'Primavera/Verão 2015', '64041990', ' ', 0, 0, 'PAR', 'CT', 'jm15032637179,769119027', ' ', 0, '337', 'SEG', '2015-02-23', '12:08:23', 'SEG', '2015-02-23', '12:08:23', 0, ' ', 0, 0, 0, 0, 0, 0, ' ', ' ', '1900-01-01', '2015-02-23', 0, ' ', ' ', 0, 0, 0, 0, '', 'SEG14120351327,436000009', 0, '', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, ' ', 0, '1900-01-01', 0, 0, 0, 0, 156, '', 0, 0, 0, 0, 0, 0, 0, '', 0, ' ', 0, ' ', 0, 0, 0, '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 13, 0.546, 71.76)
GO
INSERT INTO dbo.bi (bistamp, obrano, ref, design, qtt, qtt2, iva, desconto, tabiva, armazem, pu, debito, prorc, stipo, no, pcusto, serie, nomquina, nopat, fno, fdata, nmdoc, ndoc, nmdos, ndos, forref, txiva, rdata, lobs, ldossier, obranome, fechada, datafinal, dataobra, dataopen, datafecho, tecnico, maquina, marca, zona, litem, vumoeda, resfor, rescli, resrec, iprint, lobs2, litem2, lobs3, estab, resusr, ar2mazem, composto, lrecno, lordem, fmarcada, producao, local, morada, codpost, nome, desc2, vendedor, vendnm, tabfor, tabela1, descli, reff, lote, bostamp, ivaincl, cor, tam, segmento, bofref, bifref, grau, partes, altura, largura, espessura, biserie, infref, lifref, uni2qtt, epu, edebito, eprorc, epcusto, ttdeb, ettdeb, ttmoeda, adoc, binum1, binum2, codigo, cpoc, stns, obistamp, usr1, usr2, usr3, usr4, usr5, usalote, texteis, unidade, unidad2, oftstamp, ofostamp, promo, familia, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, usr6, sattotal, noserie, ncmassa, ncunsup, ncvest, encvest, nccod, ncinteg, dedata, atedata, classif, posic, classifc, desc3, desc4, desc5, desc6, series, oobistamp, epromo, series2, ccusto, ncusto, num1, slvu, eslvu, sltt, esltt, slvumoeda, slttmoeda, fechabo, oobostamp, ltab1, ltab2, ltab3, ltab4, ltab5, fami, pctfami, adjudicada, tieca, etieca, mtieca, volume, iecasug, iecagrad, iecacodisen, peso, pbruto, codfiscal, boclose, dtclose, u_starma, u_stdisp, u_stdispcx, u_stenc, u_stres, dgeral, temoci, temomi, temsubemp, encargo, eencargo, custoind, ecustoind, tiposemp, pvok, quarto, partes2, efornecedor, efornec, efornestab, cativo, optstamp, oristamp, compostoori, emconf, temeco, ecoval, eecoval, tecoval, etecoval, ecoval2, eecoval2, tecoval2, etecoval2, econotcalc, mntencargos, debitoori, edebitoori, trocaequi, tpromo, valdesc, evaldesc, u_idcor, u_caixas, u_myvolume, u_pesoliq)
VALUES ('SEG15022760778,6180000-3', 139425, '2141182', 'Sapato Lona INFANTIL 25/30 Cx12', 120, 120, 0, 11, 1, 1, 0, 1124.70402, 0, 4, 20640, 0, ' ', 0, 0, 1709, '2015-03-26', 'Factura', 1, 'Ordem de Separação', 7, '247860', 0, '2015-02-23', ' ', 0, '592641', 1, '2015-02-23', '2015-02-23', '2015-02-23', '2015-03-26 10:19:53.54', 3, ' ', ' ', 'ESP- ADMINIST ', '247860 ', 0, 0, 1, 0, 0, ' ', ' ', ' ', 0, 0, 0, 0, ' ', 110000, 0, 0, 'ALCLÁ DE HENARES', 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', '28802 MADRID - ESPAÑA', 'TOYS R US IBERIA, S.A.', 0, 2, 'ADMINISTRAÇAO', 0, 'Transportadora', 0, ' ', ' ', 'SEG15022343671,411000002', 0, ' ', ' ', 'ESPANHA', ' ', ' ', 0, 0, 0, 0, 0, ' ', 0, 0, 10, 0, 5.61, 0, 0, 120118, 599.15, 0, '1709', 0, 0, '2000021411821 ', 0, 0, 'SEG14120351327,264000008', '12', ' ', 'Primavera/Verão 2015', '64041990', ' ', 0, 0, 'PAR', 'CT', 'jm15032637179,769119027', ' ', 0, '337', 'SEG', '2015-02-27', '16:52:58', 'SEG', '2015-02-27', '16:52:58', 0, ' ', 0, 0, 0, 0, 0, 0, ' ', ' ', '1900-01-01', '2015-02-23', 0, ' ', ' ', 0, 0, 0, 0, '', 'SEG14120351327,264000008', 0, '', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, ' ', 0, '1900-01-01', 1044, 2196, 183, 1152, 804, '', 0, 0, 0, 0, 0, 0, 0, '', 0, ' ', 0, ' ', 0, 0, 0, '', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 10, 0.41, 50.4)
GO
Table CL :
INSERT INTO dbo.cl (clstamp, nome, no, estab, vendnm, ncont, nome2, saldo, esaldo, moeda, fax, telefone, contacto, acmfact, eacmfact, rentval, erentval, eem, emno, eag, agno, eid, idno, efl, flno, flestab, morada, local, codpost, zona, tipo, desconto, vendedor, vencimento, plafond, eplafond, obs, preco, pais, particular, bino, bidata, bilocal, naturalid, passaporte, conta, nascimento, pagamento, cobranca, nib, descpp, imagem, odatraso, tabiva, c1tele, c1fax, c1func, c2tele, c2fax, c2func, c2tacto, c3tele, c3fax, c3func, c3tacto, dqtt, clivd, descarga, nocredit, segmento, email, fref, ccusto, ncusto, naood, naomail, contalet, contaletdes, contaletsac, alimite, dqttval, tipodesc, tlmvl, cobrador, rota, contaainc, contaacer, eancl, ediexp, url, tpstamp, tpdesc, pncont, cobtele, cobfax, cobfunc, cobtacto, ollocal, contado, pscm, zncm, excm, ptcm, encm, ntcm, pscmdesc, znregiao, excmdesc, ptcmdesc, encmdesc, usaintra, cobnao, saldlet, esaldlet, site, bizzaddress, bizzproto, cass, classe, lang, iectisento, niec, gaenome, gaecstamp, clinica, ftndias, txftndias, ftdiasmr, txftdias, ftdatasmr, txftdata, ftnid, txftnid, ftidnome, txftidnome, ftidcontacto, txftidcontacto, ftidnac, txftidnac, ftidcont, txftidcont, ftidutente, txftidutente, ftidbi, txftidbi, ftidcob, txftidcob, ftmrtot, txftmrtot, ftumamr, paramr, filtrast, contatit, statuspda, inactivo, naoencomenda, clifactor, contafac, dfront, dsuporte, dformacao, dteam, recdocdig, glncl, codfornecedor, localentrega, obsdoc, ecoisento, tbprcod, area, exporpos, temcred, temftglob, ltyp, lmlt, rbal, addd, id, track, tracknr, pin, encrpin, blck, acc, repl, odo, did, carr, fuels, cw, shop, refcli, matric, desccmb, descloj, isperson, radicaltipoemp, autofact, u_plafseg, u_memo, u_crdcauci, u_etiqmor, u_semcarta, u_obs2, u_tipoemb, u_sinistro, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, marcada, u_obsinf, u_failscor, u_capfin, u_avrisco, cancpos, numcontrepres, codprovincia, autorizacaoactiva, numautorizacaosdd, numseqaut, mesesnaopag, diaspag, descregiva, c1email, c2email, c3email, cobemail, pcktsyncdate, pcktsynctime, motiseimp, codmotiseimp, distrito, ccadmin, geramb, contamovinc, contadivinc, bic, iban, datasdd, sepacode, consfinal, operext, u_nclsegur, u_tipocred, u_plafcos, saldopa, saldoini, taxairs, txirspersonalizada)
VALUES ('SEG07060466885,0770000-1', 'TOYS R US IBERIA, S.A.', 20640, 0, 'ADMINISTRAÇAO', 'A79520656', '', 0, 0, 'PTE ou EURO', '918878273 ', '918878218 ', ' ', 137090660.16906, 683805.33, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', 'ALCLÁ DE HENARES', '28802 MADRID - ESPAÑA', 'ESP- ADMINIST', '0%', 11, 2, 90, 0, 0, 'PAGAMENTO A 120 DIAS
Telf.(Pagamentos) - 0034 - 91 887 8200 ou 0034 - 91 887 8351
0034 - 91 887 8356', 2, 2, 0, '', '1900-01-01', '', 'MADRID', '', '211120020640', '1900-01-01', '', '', '', 0, '', 0, 1, '', '', '', '', '', '', '', '', '', '', '', 0, 0, 'Morada do Cliente', 1, 'ESPANHA', ' ', '', '', '', 0, 0, ' ', ' ', ' ', 22, 0, '', '', '', '', ' ', ' ', '8421703000012', 1, '', 'JM07111339317,315812509', '90 DAYS', 'ES', '', '', '', '', '', 0, '', 0, 0, '', '', 0, '', '', '', '', '', 0, 0, 0, 0, '', '', 1, '', 'HIPERMERC.', '', 0, '', '', '', 0, 0, '', 0, '', 0, '', 0, '', 0, '', 0, '', 0, '', 0, '', 0, '', 0, '', 0, '', 0, '', 0, 0, 0, '211170020640', '', 1, 1, 0, ' ', 0, 0, 0, 0, 1, '8421703000012', '561737', '', '', 0, '', 'MADRID', 0, 0, 0, 1, 0, 0, 0, '', '', 0, '', '', 0, 0, 0, 0, 0, 0, '', 0, '', '', '', 0, 0, 0, 1, 0, 0, 'Toys R Us Iberia, S.A.
Poligono Industrial Alcala Oeste
Carretera M-300, Km 29,800
28802 - Alcalá de Henares (Madrid) España
', '13', '28802 ALCLÁ DE HENARES', 0, '04/06/2013 - Acordado com o Sr. Nuno Maia: condições de venda a 120 dias. Na CESCE o cliente está enquadrado no Grupo 1 e será aplicada uma taxa de prémio de 0,32625% (invés de 0,261%). Carla
29-11-2010. Fim a Linha de apoio OCDE I e OCDE II. Jdeus.
A CESCE concedeu um plafond de 100.000€.
30/03/2010-Redução do plafond da OCDE II de 75.000,00 para 50.000,00 euros.
01/01/2010-Plafond atribuido pela OCDE II no valor de 75.000,00 euros. Vigência de 1 ano.
17/09/2009-Plafond atribuido pela OCDE II no valor de 75.000,00 euros. Até final do ano.
15/09/09 - Envio de mercadoria autorizado por Paulo Maia (Fact.139009 - 8.890,90 €)
01/09/09 - Envio de mercadoria autorizado por Paulo Maia (Fact.138306 - 26.284,66 €)
18/06/2009-Cesce não concedeu plafond (75.000,00 euros pedidos). Mot.25.
05/05/2009-Tirado relatório da InformaDb.
23/04/2009-Plafond da Cesce 0.
24/03/2009-Cesce reduziu risco comercial p/50% até 23/04/2009;
09/03/2009 - Cesce reduziu plafond de 100.000,00 p/50.000,00 eurs até 30/06/09 - Mot.25;
22/12/2008 - CESCE voltou a conceder 100.000€ (= ao solicitado)
02/08/2008-Plafond a 0;
07/07/2008-Cliente em 02/08/2008 vai ficar sem plafond - motivo 42;
27/07/2007 - CESCE concedeu 100.000€ (= ao solicitado)
', 'CIP', 0, 'SEG', '2007-06-04', '18:38:48', 'SEG', '2018-06-19', '17:24:50', 0, '12/08/2014 - O Crédito Máximo Recomendado para TOYS "R" US IBERIA SA foi REDUZIDO de € 4.300.000 para € 2.400.000
23/10/2013 - O Crédito Máximo Recomendado para TOYS "R" US IBERIA SA foi AUMENTADO de € 2.800.000 para € 4.300.000
Limite de credito da Informa DB é de 5.500.000,00€.', '85', '5A', '2', 0, '', '', 0, '', 0, '', '', 'PT', '', '', '', '', '2018-06-19', '17:24:51', 'Isento Artigo 14.º do RITI', 'M16', '', 0, 0, '', '', '', '', '1900-01-01', '', 0, 0, '11083514', 'C/PLAFOND', 100000, 0, 0, 0, 0)
GO
I hope now that it is easier to test.
Many thanks,
Best regards,
Luis
August 22, 2018 at 1:36 pm
That's better, but
I found the following issues with your query,
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2018 at 2:48 pm
I completely rewrote your query. You had way too many calls to each of the tables. I used a CTE to get the row numbers for the details section. Except for differences in number/date formats, because you didn't supply your data types, it matches your expected output.
WITH bi_lines AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY bi.bostamp ORDER BY bi.lordem asc) AS LineNumber
FROM bi
)
--SELECT(
SELECT
1 AS Versao,
220 AS coddoc,
5 AS tipodoc,
'EUR' as codmoeda,
bo.obrano AS numdoc,
bo.dataobra AS datadoc,
Rtrim(bo.nome) AS localentrega,
Rtrim(bo.morada) AS moradaentrega,
Rtrim(bo.local) AS loclocalalentrega,
Rtrim(bo.codpost) AS codpostentrega,
bo.datafinal AS dataentrega,
bo.datafinal AS datalimite,
5600000561737 AS glnfornecedor,
'My Company , Lda' AS nomefornecedor,
'Zona Ind. do Roligo' AS moradafornecedor,
501167323 as ncontfornecedor,
cl.glncl as glncliente,
cl.ncont AS ncontcliente,
Rtrim(bo.nome) AS nomecliente,
Rtrim(bo.morada) AS moradacliente,
Rtrim(bo.codpost) AS codpostcliente,
Rtrim(bo.local) AS localcliente,
'4524-909' AS codpostfornecedor,
(SELECT COUNT(*) FROM bi WHERE bi.bostamp = bo.bostamp) AS numbias, /* Changed to correlated subquery */
cl.eancl AS eancliente, /* Moved to LEFT OUTER JOIN */
5600000561737 AS eanfornecedor,
Rtrim(bo.obs) AS observacoes,
cl.glncl as glnentrega,
bi_xml.Lines
FROM bo
LEFT JOIN cl
ON bo.no = cl.no and cl.estab = 0
CROSS APPLY
(
SELECT
1 AS Versao,
bil.LineNumber,
Rtrim(bil.codigo) AS codean,
Rtrim(bil.litem) AS refcliente,
Rtrim(bil.ref) AS referencia,
Rtrim(bil.design) AS descricao,
bil.unidad2 AS unidalternativa,
bil.ettdeb AS precoliquido,
bil.qtt AS quantidade,
bil.uni2qtt AS quantalternativa ,
bil.LineNumber AS [Taxes/LineNumberTax],
bil.tabiva AS [Taxes/Tabiva],
bil.iva AS [Taxes/IVA],
bil.LineNumber AS [SourceDocument/LineNumberDetlhe],
CONVERT(DATETIME, bil.dataobra, 126) AS [SourceDocument/refdata],
'Customer Order' AS [SourceDocument/refnomedoc],
999999 AS [SourceDocument/refnumdoc],
5 AS [SourceDocument/reftipodoc]
FROM bi_lines AS bil
WHERE bil.bostamp = bo.bostamp
FOR XML PATH('Lines'), TYPE
) AS bi_xml(Lines)
FOR XML PATH('Documento'), TYPE, ROOT('VFPDATASET')
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2018 at 3:45 pm
Hello Drew,
Thanks for your advises, i send you the script for the 3 tables only with the columns that keep the data for my XML query.
Also, all of them are Temporary tables.
--Table CL
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #CL(
[no] numeric(10, 0) NOT NULL,
[estab] numeric(3, 0) NOT NULL,
[ncont] varchar(20) NOT NULL,
[eancl] varchar(35) not NULL,
[glncl] varchar(35) NOT NULL
)
INSERT INTO #CL (no, estab, ncont, eancl, glncl)
VALUES (20640, 0, 'A79520656', '8421703000012', '8421703000012')
GO
--Table BO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #BO(
[bostamp] char(25) NOT NULL,
[obrano] numeric(10, 0) NOT NULL,
[dataobra] datetime NOT NULL,
[nome] char(55) NOT NULL,
[morada] varchar(55) NOT NULL,
[local] varchar(43) NOT NULL,
[codpost] varchar(45) NOT NULL,
[obs] varchar(67) not null,
[eanfornecedor] varchar(20) not null,
[codpostfornecedor] varchar(8) not null,
[glnfornecedor] varchar(20) not null,
[nomefornecedor] varchar(60) not null,
[moradafornecedor] varchar(40) not null,
[ncontfornecedor] varchar(20) not null,
[Versao] numeric(1,0) not null,
[coddoc] numeric(3,0) not null,
[tipodoc] numeric(2,0) not null,
[codmoeda] varchar(10) not null
)
INSERT INTO #BO (bostamp, obrano, dataobra, nome, morada, local, codpost, obs, eanfornecedor, codpostfornecedor, glnfornecedor, nomefornecedor, ncontfornecedor, Versao, coddoc, tipodoc, codmoeda)
VALUES ('SEG15022343671,411000002', 139425, '2015-02-23', 'TOYS R US IBERIA, S.A.', 'POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800', 'ALCLÁ DE HENARES', '28802 MADRID - ESPAÑA', '.', '5600000561737', '4520-149', '5600000561737', 'Planitoi', '501167323', 1, 220, 5, 'EUR')
GO
--Table BI
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #BI (
[bistamp] char(25) NOT NULL,
[dataobra] Datetime not null,
[ref] char(18) NOT NULL,
[design] varchar(60) NOT NULL,
[qtt] numeric(14, 4) NOT NULL,
[litem] varchar(20) NOT NULL,
[iva] numeric(5, 2) NOT NULL,
[tabiva] numeric(1, 0) NOT NULL,
[lordem] numeric(10, 0) NOT NULL,
[bostamp] varchar(25) NOT NULL,
[uni2qtt] numeric(14, 4) NOT NULL,
[ettdeb] numeric(19, 6) NOT NULL,
[codigo] varchar(40) NOT NULL
)
INSERT INTO #BI (bistamp, dataobra, ref, design, qtt, litem, iva, tabiva, lordem, bostamp, uni2qtt, ettdeb, codigo)
VALUES ('SEG15022343704,2230000-3', '2015-02-23', '2131170', 'Clogs BEBE 18/24 Cx12', 312, '247790 ', 0, 1, 30000, 'SEG15022343671,411000002', 26, 888.58, '2000021311701 ')
GO
INSERT INTO #BI (bistamp, dataobra, ref, design, qtt, litem, iva, tabiva, lordem, bostamp, uni2qtt, ettdeb, codigo)
VALUES ('SEG15022343704,2700000-4', '2015-02-23', '2131171', 'Clogs BEBE 18/24 Cx12', 312, '247793 ', 0, 1, 40000, 'SEG15022343671,411000002', 26, 888.58, '2000021311718 ')
GO
INSERT INTO #BI (bistamp, dataobra, ref, design, qtt, litem, iva, tabiva, lordem, bostamp, uni2qtt, ettdeb, codigo)
VALUES ('SEG15022343704,3170000-5', '2015-02-23', '2133931', 'Sapato Lona INFANTIL 22/28 Cx12', 132, '247801 ', 0, 1, 50000, 'SEG15022343671,411000002', 11, 444.07, '2000021339316 ')
GO
INSERT INTO #BI (bistamp, dataobra, ref, design, qtt, litem, iva, tabiva, lordem, bostamp, uni2qtt, ettdeb, codigo)
VALUES ('SEG15022343704,3480000-6', '2015-02-23', '2133930', 'Sapato Lona INFANTIL 22/28 Cx12', 132, '247804 ', 0, 1, 60000, 'SEG15022343671,411000002', 11, 444.07, '2000021339309 ')
GO
INSERT INTO #BI (bistamp, dataobra, ref, design, qtt, litem, iva, tabiva, lordem, bostamp, uni2qtt, ettdeb, codigo)
VALUES ('SEG15022343704,3950000-7', '2015-02-23', '2141010', 'Sapato Lona INFANTIL 20/26 Cx14', 210, '247807 ', 0, 1, 70000, 'SEG15022343671,411000002', 15, 878.43, '2000021410107 ')
GO
INSERT INTO #BI (bistamp, dataobra, ref, design, qtt, litem, iva, tabiva, lordem, bostamp, uni2qtt, ettdeb, codigo)
VALUES ('SEG15022343704,4260000-8', '2015-02-23', '2142500-TOYS', 'Sapato Lona INFANTIL 22/28 Cx12', 156, '247866 ', 0, 1, 80000, 'SEG15022343671,411000002', 13, 1095.45, '2000021425019 ')
GO
INSERT INTO #BI (bistamp, dataobra, ref, design, qtt, litem, iva, tabiva, lordem, bostamp, uni2qtt, ettdeb, codigo)
VALUES ('SEG15022760778,6180000-3', '2015-02-23', '2141182', 'Sapato Lona INFANTIL 25/30 Cx12', 120, '247860 ', 0, 1, 110000, 'SEG15022343671,411000002', 10, 599.15, '2000021411821 ')
GO
I hope this time everything was ok.
Many thanks,
LS
August 22, 2018 at 4:52 pm
Hello Drew,
Thanks for your reply and your rewrite query , but they don´t return any information about lines just information of header.
Also, i need to pass @Stamp in the CTE, that´s ok, but also in the query because they show me all the documents and what i want is to create XML file only for the document that i want and not all.
But , more important is why they don´t show me details of lines with the 2 new nodes ?
Thanks for your help,
Best regards,
Ls
August 22, 2018 at 5:11 pm
Hello again Drew,
I solve the 2 problems with some modification, like this:
;
WITH bii
AS
(SELECT
*
,ROW_NUMBER() OVER (PARTITION BY bi.bostamp ORDER BY bi.lordem ASC) AS LineNumber
FROM bi
WHERE bostamp = 'SEG15022343671,411000002')
--SELECT(
SELECT
1 AS Versao
,220 AS coddoc
,5 AS tipodoc
,'EUR' AS codmoeda
,bo.obrano AS numdoc
,bo.dataobra AS datadoc
,RTRIM(bo.nome) AS localentrega
,RTRIM(bo.morada) AS moradaentrega
,RTRIM(bo.local) AS loclocalalentrega
,RTRIM(bo.codpost) AS codpostentrega
,bo.datafinal AS dataentrega
,bo.datafinal AS datalimite
,5600000561737 AS glnfornecedor
,'My Company , Lda' AS nomefornecedor
,'Zona Ind. do Roligo' AS moradafornecedor
,501167323 AS ncontfornecedor
,cl.glncl AS glncliente
,cl.ncont AS ncontcliente
,RTRIM(bo.nome) AS nomecliente
,RTRIM(bo.morada) AS moradacliente
,RTRIM(bo.codpost) AS codpostcliente
,RTRIM(bo.local) AS localcliente
,'4524-909' AS codpostfornecedor
,(SELECT
COUNT(*)
FROM bi
WHERE bi.bostamp = bo.bostamp)
AS numbias
, /* Changed to correlated subquery */
cl.eancl AS eancliente
, /* Moved to LEFT OUTER JOIN */
5600000561737 AS eanfornecedor
,RTRIM(bo.obs) AS observacoes
,cl.glncl AS glnentrega
,bi_xml.Lines
FROM bo
LEFT JOIN cl
ON bo.no = cl.no
AND cl.estab = 0
CROSS APPLY (SELECT
1 AS Versao
,bil.LineNumber
,RTRIM(bil.codigo) AS codean
,RTRIM(bil.litem) AS refcliente
,RTRIM(bil.ref) AS referencia
,RTRIM(bil.design) AS descricao
,bil.unidad2 AS unidalternativa
,bil.ettdeb AS precoliquido
,bil.qtt AS quantidade
,bil.uni2qtt AS quantalternativa
,bil.LineNumber AS [Taxes/LineNumberTax]
,bil.tabiva AS [Taxes/Tabiva]
,bil.iva AS [Taxes/IVA]
,bil.LineNumber AS [SourceDocument/LineNumberDetlhe]
,CONVERT(DATETIME, bil.dataobra, 126) AS [SourceDocument/refdata]
,'Customer Order' AS [SourceDocument/refnomedoc]
,999999 AS [SourceDocument/refnumdoc]
,5 AS [SourceDocument/reftipodoc]
FROM bii AS bil
WHERE bil.bistamp = bil.bistamp
AND bil.bostamp = 'SEG15022343671,411000002'
FOR XML PATH ('Lines'), TYPE) AS bi_xml (Lines)
WHERE bostamp = 'SEG15022343671,411000002'
FOR XML PATH ('Documento'), TYPE, ROOT ('VFPDATASET')
Many, Many thanks because i have learn 2 important things the use o cross join when i want create a XML query file and also how to create Children nodes on the same line.
Very great help.
Best regards,
Luis
August 23, 2018 at 7:40 am
luissantos - Wednesday, August 22, 2018 4:52 PMHello Drew,Thanks for your reply and your rewrite query , but they don´t return any information about lines just information of header.
Also, i need to pass @Stamp in the CTE, that´s ok, but also in the query because they show me all the documents and what i want is to create XML file only for the document that i want and not all.But , more important is why they don´t show me details of lines with the 2 new nodes ?
Thanks for your help,
Best regards,
Ls
The @Stamp parameter should only be used to filter the BO record. Everything else should be filtered by matching the BO.bostamp field. You were using the parameter in places where you should have been using BO.bostamp. I did forget to add in the single criteria where BO.bostamp = @Stamp.
I coded to match your expected output, which it did. My code can only be as good as the information you provide.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 23, 2018 at 7:55 am
luissantos - Wednesday, August 22, 2018 5:11 PMHello again Drew,I solve the 2 problems with some modification, like this:
;
WITH bii
AS
(SELECT
*
,ROW_NUMBER() OVER (PARTITION BY bi.bostamp ORDER BY bi.lordem ASC) AS LineNumber
FROM bi
WHERE bostamp = 'SEG15022343671,411000002')--SELECT(
SELECT
1 AS Versao
,220 AS coddoc
,5 AS tipodoc
,'EUR' AS codmoeda
,bo.obrano AS numdoc
,bo.dataobra AS datadoc
,RTRIM(bo.nome) AS localentrega
,RTRIM(bo.morada) AS moradaentrega
,RTRIM(bo.local) AS loclocalalentrega
,RTRIM(bo.codpost) AS codpostentrega
,bo.datafinal AS dataentrega
,bo.datafinal AS datalimite
,5600000561737 AS glnfornecedor
,'My Company , Lda' AS nomefornecedor
,'Zona Ind. do Roligo' AS moradafornecedor
,501167323 AS ncontfornecedor
,cl.glncl AS glncliente
,cl.ncont AS ncontcliente
,RTRIM(bo.nome) AS nomecliente
,RTRIM(bo.morada) AS moradacliente
,RTRIM(bo.codpost) AS codpostcliente
,RTRIM(bo.local) AS localcliente
,'4524-909' AS codpostfornecedor
,(SELECT
COUNT(*)
FROM bi
WHERE bi.bostamp = bo.bostamp)
AS numbias
, /* Changed to correlated subquery */
cl.eancl AS eancliente
, /* Moved to LEFT OUTER JOIN */
5600000561737 AS eanfornecedor
,RTRIM(bo.obs) AS observacoes
,cl.glncl AS glnentrega
,bi_xml.LinesFROM bo
LEFT JOIN cl
ON bo.no = cl.no
AND cl.estab = 0
CROSS APPLY (SELECT
1 AS Versao
,bil.LineNumber
,RTRIM(bil.codigo) AS codean
,RTRIM(bil.litem) AS refcliente
,RTRIM(bil.ref) AS referencia
,RTRIM(bil.design) AS descricao
,bil.unidad2 AS unidalternativa
,bil.ettdeb AS precoliquido
,bil.qtt AS quantidade
,bil.uni2qtt AS quantalternativa
,bil.LineNumber AS [Taxes/LineNumberTax]
,bil.tabiva AS [Taxes/Tabiva]
,bil.iva AS [Taxes/IVA]
,bil.LineNumber AS [SourceDocument/LineNumberDetlhe]
,CONVERT(DATETIME, bil.dataobra, 126) AS [SourceDocument/refdata]
,'Customer Order' AS [SourceDocument/refnomedoc]
,999999 AS [SourceDocument/refnumdoc]
,5 AS [SourceDocument/reftipodoc]
FROM bii AS bil
WHERE bil.bistamp = bil.bistamp
AND bil.bostamp = 'SEG15022343671,411000002'
FOR XML PATH ('Lines'), TYPE) AS bi_xml (Lines)
WHERE bostamp = 'SEG15022343671,411000002'
FOR XML PATH ('Documento'), TYPE, ROOT ('VFPDATASET')Many, Many thanks because i have learn 2 important things the use o cross join when i want create a XML query file and also how to create Children nodes on the same line.
Very great help.
Best regards,
Luis
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 23, 2018 at 5:07 pm
Hello Drew,
Thanks for your reply.
1) You don't want to filter your CTE. The filtering will take place later when you link to the BO table.
Answer: I am using WHERE clause in the CTE for field bi.bostamp otherwise the CTE Query runs through all the documents when I only want 1 document.
The field in table BO (Document Header) BO.BOSTAMP links to my BI table (Document rows) on BI.BOSTAMP .
2) You changed WHERE bil.bostamp = bo.bostamp to WHERE bil.bostamp = bil.bostamp. The whole purpose of that line was to link the bil table to the bo table. By changing that, you have not only broken that link, but you have introduced an (almost) tautology. bil.bostamp will ALWAYS answerequal itself unless it is NULL.
R: Yes, you are quite right this join does not make sense.
Then after your advises, i rewrite like this:
DECLARE @Stamp AS VARCHAR(25)
SET @Stamp = 'SEG15022343671,411000002'
;WITH bi_lines AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY bi.bostamp ORDER BY bi.lordem asc) AS LineNumber
FROM bi WHERE bostamp = @Stamp
)
SELECT
1 AS Versao,
220 AS coddoc,
5 AS tipodoc,
'EUR' as codmoeda,
bo.obrano AS numdoc,
bo.dataobra AS datadoc,
Rtrim(bo.nome) AS localentrega,
Rtrim(bo.morada) AS moradaentrega,
Rtrim(bo.local) AS loclocalalentrega,
Rtrim(bo.codpost) AS codpostentrega,
bo.datafinal AS dataentrega,
bo.datafinal AS datalimite,
5600000561737 AS glnfornecedor,
'My Company , Lda' AS nomefornecedor,
'Zona Ind. do Roligo' AS moradafornecedor,
501167323 as ncontfornecedor,
cl.glncl as glncliente,
cl.ncont AS ncontcliente,
Rtrim(bo.nome) AS nomecliente,
Rtrim(bo.morada) AS moradacliente,
Rtrim(bo.codpost) AS codpostcliente,
Rtrim(bo.local) AS localcliente,
'4524-909' AS codpostfornecedor,
(SELECT COUNT(*) FROM bi WHERE bi.bostamp = bo.bostamp) AS numbias, /* Changed to correlated subquery */
cl.eancl AS eancliente, /* Moved to LEFT OUTER JOIN */
5600000561737 AS eanfornecedor,
Rtrim(bo.obs) AS observacoes,
cl.glncl as glnentrega,
bi_xml.Lines
FROM bo
LEFT JOIN cl
ON bo.no = cl.no and cl.estab = 0
CROSS APPLY
(
SELECT
1 AS Versao,
bil.LineNumber,
Rtrim(bil.codigo) AS codean,
Rtrim(bil.litem) AS refcliente,
Rtrim(bil.ref) AS referencia,
Rtrim(bil.design) AS descricao,
bil.unidad2 AS unidalternativa,
bil.ettdeb AS precoliquido,
bil.qtt AS quantidade,
bil.uni2qtt AS quantalternativa ,
bil.LineNumber AS [Taxes/LineNumberTax],
bil.tabiva AS [Taxes/Tabiva],
bil.iva AS [Taxes/IVA],
bil.LineNumber AS [SourceDocument/LineNumberDetlhe],
CONVERT(DATETIME, bil.dataobra, 126) AS [SourceDocument/refdata],
'Customer Order' AS [SourceDocument/refnomedoc],
999999 AS [SourceDocument/refnumdoc],
5 AS [SourceDocument/reftipodoc]
FROM bi_lines AS bil
WHERE bil.bostamp = bo.bostamp
FOR XML PATH('Lines'), TYPE
) AS bi_xml(Lines)
WHERE bo.bostamp = @Stamp
FOR XML PATH('Documento'), TYPE, ROOT('VFPDATASET')
;
And the XML file result is:
<VFPDATASET>
<Documento>
<Versao>1</Versao>
<coddoc>220</coddoc>
<tipodoc>5</tipodoc>
<codmoeda>EUR</codmoeda>
<numdoc>139425</numdoc>
<datadoc>2015-02-23T00:00:00</datadoc>
<localentrega>TOYS R US IBERIA, S.A.</localentrega>
<moradaentrega>POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800</moradaentrega>
<loclocalalentrega>ALCLÁ DE HENARES</loclocalalentrega>
<codpostentrega>28802 MADRID - ESPAÑA</codpostentrega>
<dataentrega>2015-02-23T00:00:00</dataentrega>
<datalimite>2015-02-23T00:00:00</datalimite>
<glnfornecedor>5600000561737</glnfornecedor>
<nomefornecedor>My Company , Lda</nomefornecedor>
<moradafornecedor>Zona Ind. do Roligo</moradafornecedor>
<ncontfornecedor>501167323</ncontfornecedor>
<glncliente>8421703000012</glncliente>
<ncontcliente>A79520656</ncontcliente>
<nomecliente>TOYS R US IBERIA, S.A.</nomecliente>
<moradacliente>POLIGONO IDUSTRIAL ALCALÁ OESTE CTRA, M-300, KM 29.800</moradacliente>
<codpostcliente>28802 MADRID - ESPAÑA</codpostcliente>
<localcliente>ALCLÁ DE HENARES</localcliente>
<codpostfornecedor>4524-909</codpostfornecedor>
<numbias>7</numbias>
<eancliente>8421703000012</eancliente>
<eanfornecedor>5600000561737</eanfornecedor>
<observacoes>.</observacoes>
<glnentrega>8421703000012</glnentrega>
<Lines>
<Lines>
<Versao>1</Versao>
<LineNumber>1</LineNumber>
<codean>2000021311701</codean>
<refcliente>247790</refcliente>
<referencia>2131170</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
<Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
<SourceDocument>
<LineNumberDetlhe>1</LineNumberDetlhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
<Lines>
<Versao>1</Versao>
<LineNumber>2</LineNumber>
<codean>2000021311718</codean>
<refcliente>247793</refcliente>
<referencia>2131171</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
<Taxes>
<LineNumberTax>2</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
<SourceDocument>
<LineNumberDetlhe>2</LineNumberDetlhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
<Lines>
<Versao>1</Versao>
<LineNumber>3</LineNumber>
<codean>2000021339316</codean>
<refcliente>247801</refcliente>
<referencia>2133931</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
<Taxes>
<LineNumberTax>3</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
<SourceDocument>
<LineNumberDetlhe>3</LineNumberDetlhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
<Lines>
<Versao>1</Versao>
<LineNumber>4</LineNumber>
<codean>2000021339309</codean>
<refcliente>247804</refcliente>
<referencia>2133930</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>444.070000</precoliquido>
<quantidade>132.0000</quantidade>
<quantalternativa>11.0000</quantalternativa>
<Taxes>
<LineNumberTax>4</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
<SourceDocument>
<LineNumberDetlhe>4</LineNumberDetlhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
<Lines>
<Versao>1</Versao>
<LineNumber>5</LineNumber>
<codean>2000021410107</codean>
<refcliente>247807</refcliente>
<referencia>2141010</referencia>
<descricao>Sapato Lona INFANTIL 20/26 Cx14</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>878.430000</precoliquido>
<quantidade>210.0000</quantidade>
<quantalternativa>15.0000</quantalternativa>
<Taxes>
<LineNumberTax>5</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
<SourceDocument>
<LineNumberDetlhe>5</LineNumberDetlhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
<Lines>
<Versao>1</Versao>
<LineNumber>6</LineNumber>
<codean>2000021425019</codean>
<refcliente>247866</refcliente>
<referencia>2142500-TOYS</referencia>
<descricao>Sapato Lona INFANTIL 22/28 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>1095.450000</precoliquido>
<quantidade>156.0000</quantidade>
<quantalternativa>13.0000</quantalternativa>
<Taxes>
<LineNumberTax>6</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
<SourceDocument>
<LineNumberDetlhe>6</LineNumberDetlhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
<Lines>
<Versao>1</Versao>
<LineNumber>7</LineNumber>
<codean>2000021411821</codean>
<refcliente>247860</refcliente>
<referencia>2141182</referencia>
<descricao>Sapato Lona INFANTIL 25/30 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>599.150000</precoliquido>
<quantidade>120.0000</quantidade>
<quantalternativa>10.0000</quantalternativa>
<Taxes>
<LineNumberTax>7</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
<SourceDocument>
<LineNumberDetlhe>7</LineNumberDetlhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
</Lines>
</Documento>
</VFPDATASET>
The unique issue is that i have 2 time just in the beginning the same node name <Line>, the rest is 100% OK:
<Lines> ««««« this one is not necessary, and i don´t know how to remove it
<Lines>
<Versao>1</Versao>
<LineNumber>1</LineNumber>
<codean>2000021311701</codean>
<refcliente>247790</refcliente>
<referencia>2131170</referencia>
<descricao>Clogs BEBE 18/24 Cx12</descricao>
<unidalternativa>CT</unidalternativa>
<precoliquido>888.580000</precoliquido>
<quantidade>312.0000</quantidade>
<quantalternativa>26.0000</quantalternativa>
<Taxes>
<LineNumberTax>1</LineNumberTax>
<Tabiva>1</Tabiva>
<IVA>0.00</IVA>
</Taxes>
<SourceDocument>
<LineNumberDetlhe>1</LineNumberDetlhe>
<refdata>2015-02-23T00:00:00</refdata>
<refnomedoc>Customer Order</refnomedoc>
<refnumdoc>999999</refnumdoc>
<reftipodoc>5</reftipodoc>
</SourceDocument>
</Lines>
<lines>
......
.....
</Lines>
</Lines>
</Documento>
</VFPDATASET>
Many thanks again
Best regards,
Luis
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply