---
---Pedidos no facturados que estan en LE
---
select * 
from tbl_pedcab a
left join tbl_faccab b on a.strCodTer = b.strCodTer and b.strCamGen = a.strCamGen
inner join [192.168.10.14].[MP_StockSupply].Picking.PedidosEncabezados c on c.campana = a.strCamGen and c.numeropedido = a.strNumDoc 
where 
	a.strTipDoc = 'pd'
	and a.strCamGen = '201909'
	and b.strnumdoc is null 

---
---Pedidos facturados LE
---
select a.strNumDoc as Pedido, b.strNumDoc as Factura, a.strCodTer as Cedula, c.Estado, c.FechaAsignacion
from tbl_pedcab a with (nolock) 
inner join tbldoc b with (nolock) on b.strCamGen = a.strCamGen and b.strCodCA = a.strCodTer and b.strTipDoc = 'fc'
inner join [192.168.10.14].[MP_StockSupply].Picking.PedidosEncabezados c on c.Campana = a.strCamGen and c.NumeroPedido = a.strNumDoc
where 
a.strCamGen = '201909' 
and b.strNumDoc is not null
and c.Estado <> 4
and c.FechaAsignacion >= '20190612 13:00'

---
---Pedidos facturados
---
select a.* 
from tbl_pedcab a with (nolock) 
inner join tbldoc b with (nolock) on b.strCamGen = a.strCamGen and b.strCodCA = a.strCodTer and b.strTipDoc = 'fc'
where 
a.strCamGen = '201909' 
and b.strNumDoc is not null

--======================================================================================================================================
--
---
---Validar Códigos de Barra repetidos
---
select distinct a.strCodigoBarra, b.strcodBarraProveedor, a.strCodLin as CodLinSku, b.strCodLin as CodLinPrecios
into #CodRepetidos
from tblSku a 
inner join tblsku b on a.strCodigoBarra = b.strcodBarraProveedor 
inner join tbllistasprecios c on c.strCodLin = a.strCodLin and c.strCampaña = '201909'
where 
	a.strCodLin <> b.strCodLin

Select * from #CodRepetidos

---
---Validar los pedidos afectados por el código de barra repetido
---
select a.strNumDoc from tbl_pedcab a
inner join tbl_peddet b on a.strTipDoc = b.strTipDoc and a.strNumDoc = b.strNumDoc and a.strTipDoc = 'PD'
left join TBL_FACCAB c on a.strCodTer = c.strCodTer and a.strCamGen = c.strCamGen and c.strTipDoc = 'FC'
where 
	c.strNumDoc is null 
	and b.strCodLin in (select CodLinSku as strCodLin From #CodRepetidos Union Select CodLinPrecios as strCodLin From #CodRepetidos)

Drop table #CodRepetidos
---
--
--======================================================================================================================================

---
---Marcar el pedido como asignado para poderlo facturar en LE
---
update tbl_pedcab set logimpreso = 1, loteimpresion = 1 , bitimpresion_priorizada = 1, intordenimpresion = 1
where 
  strtipdoc = 'pd' 
  and strcamgen = '201909' 
  --and strnumdoc in ('','','')
  --and strcodter in ('','','')

---
---Validar Códigos de Barra repetidos
---
select distinct a.strCodigoBarra, b.strcodBarraProveedor, a.strCodLin, b.strCodLin
from tblSku a 
inner join tblsku b on a.strCodigoBarra = b.strcodBarraProveedor 
inner join tbllistasprecios c on c.strCodLin = a.strCodLin and c.strCampaña = '201909'
where 
	a.strCodLin <> b.strCodLin

---
---Insertar en lista de precios
---
insert into tbllistasprecios (strCampaña, strCodPre, strCodLin, chrtipo, strubicacionp, curPrecio, StrCodiva, strUsuario)
select '201909','0002',strcodlin, 'GR', STRUBICACIONP, 0, '02','SISTEMA'  
from tblsku 
where 
	strcodlin in
	('520555',
	'503368',
	'551994',
	'551995',
	'551997',
	'551998',
	'552000',
	'552002',
	'552003')

---
---Skus que no estan en la lista de precios
---
select 
	a.strCamGen, a.strCodLin, a.strAtributo, a.NroPedidos, d.strDescripcion  
from 
	(
		select a.strCamGen, b.strCodLin, b.strAtributo, COUNT(*) as NroPedidos
		from tbl_pedcab a 
		inner join tbl_peddet b on a.strTipDoc = b.strTipDoc and a.strNumDoc = b.strNumDoc
		where 
			a.strCamGen = '201909'
		group by a.strCamGen, b.strCodLin, b.strAtributo
	) a 
	left join tbllistasprecios b on b.strCampaña = a.strCamGen and b.strCodLin = a.strCodLin and b.strCodPre = '0002'
	inner join tblSku c on c.strCodLin = a.strCodLin
	inner join tblproductos d on c.strReferencia = d.strReferencia
where 
	b.strCodLin is null
order by 
	strAtributo, strCodLin

---
---Validar figura padre e hijo en listas de precios
---
select	a.strcampana, a.skuHijo, a.skupadre, c.strCodigoBarra, d.strDescripcion
from tbl_inv_figuras a 
left join tbllistasprecios b on b.strCampaña = a.strcampana and b.strCodLin = a.skuHijo 
inner join tblSku c on a.skuHijo = c.strCodLin
inner join tblproductos d on c.strReferencia = d.strReferencia
where 
	a.strcampana = '201909'
	and b.strCodLin is null 
	--and a.skupadre = '100678'

---
---Obtener los sku de la campaña
---
select b.strCodLin, b.strAtributo
from tbl_pedcab a 
inner join tbl_peddet b on a.strTipDoc = b.strTipDoc and a.strNumDoc = b.strNumDoc
where 
	a.strCamGen = '201909'
group by b.strCodLin, b.strAtributo