Buenas tardes,
tengo esta consulta en un procedure para las facturas de ventas:
select OADM.CompnyName, OADM.SumDec, OADM.QtyDec, OADM.PriceDec, OADM.RateDec,
OADM.PercentDec, OADM.MeasureDec, OADM.DecSep, OADM.ThousSep, OADM.DateFormat,
OADM.DateSep, OADM.MainCurncy, T40.Street, T40.ZIPCode, T40.City,
T40.Block as Phone1, T40.Building as Fax, OADM.E_mail, ‘’ as ‘Logo’,
T0.DocEntry, T0.DocNum, T6.SeriesName, T6.SeriesName + ’ / ’ + cast(T0.DocNum as varchar(15)) as ‘SerieYNº’,
T0.DocDate, T0.DocDueDate, T0.TaxDate, T0.CancelDate,
T0.CardCode, case
when isnull(t17.QryGroup11, ‘N’) = ‘Y’ then df.Address
else T0.CardName
end as CardName,
T0.Address as ‘Direc.Razon’,
case
when isnull(t17.qrygroup11, ‘N’) = ‘Y’ then T28.GlbLocNumB
else T0.LicTradNum
end as ‘CIF’,
isnull(T5.Name,‘’) as ‘Nom.Contacto’, isnull(T5.Tel1,‘’) as ‘Telf1’, isnull(T5.Fax,‘’) as ‘Fax’,
T0.SlpCode as ‘Cod.Vendedor’,
–CASE T0.SlpCode WHEN ‘-1’ THEN ‘’ ELSE T3.SlpName END as ‘Nom.Vendedor’,
CASE T0.SlpCode WHEN ‘-1’ THEN ‘’ ELSE T3.firstName END as ‘Nom.Vendedor’,
T0.NumAtCard as ‘SuReferencia’, T0.NumAtCard as ‘SuPedido’, T0.Comments, T0.Header, T0.Footer,
T0.ShipToCode as ‘Envio’, T0.Address2 as ‘Direc.Envio’, T4.TrnspName as ‘Clas.Exped.’,
T4.WebSite as ‘Clas.Exped.2’, T0.AgentCode, T16.AgentName, T16.Memo as ‘AgentDesc’,
T14.PymntGroup as ‘Cond.Pagos’, T0.PeyMethod as ‘ViaPago’, isnull(T15.Descript,‘’) as ‘Nom.ViaPago’,
T0.DocCur, T0.DocRate, T0.DocTotal as ‘Total.ML’, T0.DocTotalFC as ‘Total.ME’,
(T0.VatSum-T0.EquVatSum) as ‘IVA.ML’, (T0.VatSumFC-T0.EquVatSumF) as ‘IVA.ME’,
T0.EquVatSum as ‘REquiv.ML’, T0.EquVatSumF as ‘REquiv.ME’, T0.VatSum as ‘Imp.ML’, T0.VatSumFC as ‘Imp.ME’,
isnull(T17.HouseBank,'') as 'Entidad.IC.- Banco propio', isnull(T17.HousBnkBrn,'') as 'Agencia.IC-Banco propio',
isnull(T17.HousCtlKey,'') as 'DCtrl.IC-Banco propio', isnull(T17.HousBnkact,'') as 'NCta.IC-Banco propio',
isnull(T17.BankCode,'') as 'Entidad.IC', isnull(T17.DflBranch,'') as 'Agencia.IC',
isnull(T17.BankCtlKey,'') as 'DCtrl.IC', isnull(T17.DflAccount,'') as 'NCta.IC',
isnull(T18.SwiftNum,'') as 'SWIFT.IC', isnull(T18.BankName,'') as 'NomBco.IC',
isnull(T19.IBAN,'') as 'IBAN.IC', isnull(T19.Street,'') as 'CalleBco.IC', isnull(T19.Block,'') as 'BloqueBco.IC',
isnull(T19.Zipcode,'') as 'CPBco.IC', isnull(T19.City,'') as 'CiudadBco.IC',
isnull(T20.Name, '') as 'PaisBco.IC', isnull(T21.Name,'') as 'ProvinBco.IC',
isnull(T15.BnkDflt,'') as 'Entidad.Via', isnull(T15.Branch,'') as 'Agencia.Via',
isnull(T15.BankCtlKey,'') as 'DCtrl.Via', isnull(T15.DflAccount,'') as 'NCta.Via',
isnull(T25.SwiftNum,'') as 'SWIFT.Via', isnull(T25.BankName,'') as 'NomBco.Via',
isnull(T22.IBAN,'') as 'IBAN.Via',
isnull(T22.Street,'') as 'CalleBco.Via', isnull(T22.Block,'') as 'BloqueBco.Via',
isnull(T22.Zipcode,'') as 'CPBco.Via', isnull(T22.City,'') as 'CiudadBco.Via',
isnull(T23.Name, '') as 'PaisBco.Via', isnull(T24.Name,'') as 'ProvinBco.Via',
T1.LineNum, T1.WhsCode, T1.ItemCode, T1.Dscription, cast(T2.UserText as nvarchar(max)) as 'Comentario.Art',
T1.Quantity, T1.OpenQty,
T1.PriceBefDi, T1.DiscPrcnt, T1.Price, T1.LineTotal as 'TotLin.ML', T1.TotalFrgn as 'TotLin.ME',
CASE isnull(T1.Currency,'') WHEN '' THEN OADM.MainCurncy ELSE T1.Currency END as 'Curr.Precio',
isnull(T1.Rate,1) as 'Factor.Curr.Precio', T1.VatPrcnt, T1.VatGroup,
T17.Addid as 'Cod. Terceros', T1.ShipDate as 'Fecha Entrega Línea', T1.UomCode as 'Unidad de Ventas',
CASE WHEN T1.UomCode = 'MANUAL' THEN 'UD' ELSE T1.UomCode END as 'Unidad de Ventas 2',
t1.visorder as 'Orden Visual', T31.NumAtCard as 'NumAtCard Albaran', T31.DocDueDate as 'Fecha Albaran', T2.SuppCatNum as refproveedor,
T31.docnum as 'Numero Albaran',
(select T30.Duedate from INV6 T30 where T30.InstlmntID = 1 and T30.DocEntry = T0.DocEntry) as 'Fecha Vencimiento 1',
(select T30.Duedate from INV6 T30 where T30.InstlmntID = 2 and T30.DocEntry = T0.DocEntry) as 'Fecha Vencimiento 2',
(select T30.Duedate from INV6 T30 where T30.InstlmntID = 3 and T30.DocEntry = T0.DocEntry) as 'Fecha Vencimiento 3',
(select T30.Duedate from INV6 T30 where T30.InstlmntID = 4 and T30.DocEntry = T0.DocEntry) as 'Fecha Vencimiento 4',
(select T30.Instotal from INV6 T30 where T30.InstlmntID = 1 and T30.DocEntry = T0.DocEntry) as 'Importe Vencimiento 1',
(select T30.Instotal from INV6 T30 where T30.InstlmntID = 2 and T30.DocEntry = T0.DocEntry) as 'Importe Vencimiento 2',
(select T30.Instotal from INV6 T30 where T30.InstlmntID = 3 and T30.DocEntry = T0.DocEntry) as 'Importe Vencimiento 3',
(select T30.Instotal from INV6 T30 where T30.InstlmntID = 4 and T30.DocEntry = T0.DocEntry) as 'Importe Vencimiento 4',
T17.VatStatus as 'Status Impuesto IC', t29.ShortName as 'Idioma Documento', t30.DocNum 'Num.Pedido',
t30.DocDate 'FechaPedido', t40.Building as FaxCliente, t40.Block as TlfCliente, t1.u_seidto1, t1.u_seidto2,
T0.isIns 'Es Fact. Reserva', T0.DocType as 'Tipo Factura',T0.U_SII_TIPOFAC,T30.numAtCard as 'Referencia Pedido',
T2.U_SEIOld, ADM1.Street as StreetEmp, ADM1.ZipCode as ZipCodeEmp, ADM1.City as CityEmp, OADM.Phone1 as Phone1Emp, OADM.Fax as FaxEmp, OADM.E_Mail as E_MailEmp,
T1.Project, T0.U_SEI_Obra as Obra, T1.SubCatNum, T1.U_SEISuppCatNum, T31.U_SEI_Obra as 'Obra Albarán', T30.U_SEI_Obra as 'Obra Pedido',
case when t33.CashSum>0 and t33.CashAcct like '570%' then 'Efectivo'
when t33.CheckSum>0 then 'Cheque'
when t33.TrsfrSum>0 then 'Transferencia'
when t33.CreditSum>0 then 'Tarjeta de Crédito' end as 'Forma Pago Factura'
from OADM, ADM1, OADP, OINV T0
left join INV1 T1 on T0.DocEntry = T1.DocEntry
left join INV12 T28 on T0.DocEntry = T28.DocEntry
left join OWHS T40 on T1.WhsCode = T40.WhsCode
left join OITM T2 on T1.ItemCode = T2.ItemCode
join OCTG T14 on T0.GroupNum = T14.GroupNum
–join OSLP T3 on T0.SlpCode = T3.SlpCode
left join OHEM T3 on T0.OwnerCode = T3.empID
left join OCRD T17 on T0.CardCode = T17.CardCode
left join CRD1 de on de.CardCode = t17.CardCode and de.Address = t0.ShipToCode and de.AdresType = ‘S’
left join CRD1 df on df.CardCode = t17.CardCode and df.Address = t0.PayToCode and df.AdresType = ‘B’
left join OSHP T4 on T0.TrnspCode = T4.TrnspCode
left join OCPR T5 on T0.CardCode = T5.CardCode and T0.CntctCode = T5.CntctCode
left join NNM1 T6 on T0.Series = T6.Series and T0.ObjType = T6.ObjectCode
left join OPYM T15 on T0.PeyMethod = T15.PayMethCod
left join OAGP T16 on T0.AgentCode = T16.AgentCode
left join ODSC T18 on T17.DflBankKey = T18.DfltActKey
left join OCRB T19 on T17.BankCountr = T19.Country and T17.BankCode = T19.BankCode
and T17.DflBranch = T19.Branch and T17.BankCtlKey = T19.ControlKey
and T17.DflAccount = T19.Account and t17.CardCode = t19.CardCode
left join OCRY T20 on T19.Country = T20.Code
left join OCST T21 on T19.Country = T21.Country and T19.State = T21.Code
left join DSC1 T22 on T15.BnkActKey = T22.AbsEntry
left join OCRY T23 on T22.Country = T23.Code
left join OCST T24 on T22.Country = T24.Country and T22.State = T24.Code
left join ODSC T25 on T22.AbsEntry = T25.DfltActKey
left join DLN1 T27 on t1.BaseType = t27.ObjType and T1.BaseEntry = T27.DocEntry and T1.BaseLine = T27.LineNum
LEFT JOIN ODLN T31 on t27.DocEntry = T31.DocEntry
left join ORDR t30 on t27.BaseType = t30.ObjType and t27.BaseEntry = t30.DocEntry
left join OLNG t29 on t0.LangCode = t29.Code
left join RCT2 T32 on t32.docentry=t0.DocEntry
left join ORCT t33 on t32.docnum=t33.DocEntry
where t0.Docentry = @Dockey
Por lo que veo, las facturas de reserva utiliza la misma tabla que facturas normales, el caso es que en esa consulta quiero que el campo “OBRA PEDIDO” arrastre el campo obra del pedido a la factura de reserva pero así no me lo devuelve, cuando ese campo desde la tabla de pedido tiene información.
Que puede fallarme en esta consulta?teniendo en cuenta también que esta misma consulta como se utiliza para las facturas normales, tengo el campo “OBRA ALBARÁN” el cual debe de arrastrar la obra del albarán.