Re: evento con varias query
Al no poder editar el post, vuelvo a poner la consulta, ya que el anterior mensaje estaba mal...
delimiter |
CREATE event evento_lanzatest ON schedule every 5 minute do
BEGIN
INSERT INTO alioz_fact.ventas_cab (pruc,idcpe,proceso,grupo,tipo,tcom,ser,nro,fec,fecv,moneda,tafec,tdoc,ruc,rsoc,dir,orden,grem,subtot,gravada,exonera,inafecta,gratis,dscto,igv,isc,total,perc)
select
'20507462082' as 'RUC Emisor',
concat((case(est.cf_942) when 'Factura' then '01' when 'Boleta' then '03' Else '00' END),'F001',inv.invoice_no) as 'idcpe',
'P',
'0',
'01',
(case(est.cf_942)
when 'Factura' then '01'
when 'Boleta' then '03'
Else '00' END) as 'tcom',
'F001' as 'ser',
inv.invoice_no as 'nro',
(case (isnull(duedate)) when 1 then DATE_FORMAT(CURDATE()-1, "%Y-%m-%d") when 0 then duedate END) as 'fec',
(case (isnull(duedate)) when 1 then DATE_FORMAT(CURDATE(), "%Y-%m-%d") when 0 then duedate END) as 'fecv',
mon.currency_code,
'01',
'6',
ruc.cf_757 as 'ruc',
va.accountname as 'rsoc',
vads.bill_street as'dir',
inv.purchaseorder as 'orden',
est.cf_952 as 'grem',
CAST(inv.subtotal AS DECIMAL (20,2)) as 'subtot',
CAST(inv.pre_tax_total AS DECIMAL (20,2)) as 'gravada',
'0.00' as 'exonera',
'0.00' as 'inafecta',
'0.00' as 'Gratis',
(IF (inv.discount_amount <> '0.00000000', (CASE(ISNULL( inv.discount_amount )) WHEN 1 THEN 0 WHEN 0 THEN CAST(inv.discount_amount AS DECIMAL (20,2)) END) ,IF(inv.discount_percent <> '0.000',(CASE(ISNULL( ((inv.discount_percent*inv.subtotal)/100) )) WHEN 1 THEN 0 WHEN 0 THEN CAST(((inv.discount_percent*inv.subtotal)/100) AS DECIMAL (20,2)) END),'0.00'))) as'dscto',
CAST(inv.pre_tax_total*0.18 AS DECIMAL (20,2)) as 'IGV',
'0.00' as 'ISC',
CAST(inv.total AS DECIMAL (20,2)) as 'Total',
'0.00'
from vtiger_invoice inv
left join vtiger_account va ON va.accountid = inv.accountid
left join vtiger_accountscf ruc ON ruc.accountid = inv.accountid
left join vtiger_currency_info mon ON mon.id = inv.currency_id
left join vtiger_invoicecf est on est.invoiceid = inv.invoiceid
left join vtiger_accountbillads vads on vads.accountaddressid = va.accountid
where invoicestatus='Procesando a Sunat'
order by inv.invoiceid desc;
update alioz_vtig724.vtiger_invoice vti left join alioz_fact.ventas_cab vca ON vti.invoice_no = vca.nro set invoicestatus='Enviando detalle a Sunat'
where vti.invoicestatus = 'Procesando a Sunat';
INSERT INTO alioz_fact.ventas_det (identi, idcpe, it, id_cab, cod, det, umed, cant, tpre, pre, subt, dscto, igv, tot, timp, exo, ina, grav, grat, total, unimed)
select
concat('01','F001',vinv.invoice_no,'-',sequence_no) as 'identi',
concat('01','F001',vinv.invoice_no) as 'idcpe',
sequence_no as 'it',
'0' as 'id_cab',
(IF (vpro.product_no like 'PRO%', vpro.product_no ,IF(vserv.service_no like 'SER%',vserv.service_no,'nada'))) as 'cod',
(IF (vpro.product_no like 'PRO%', vpro.productname ,IF(vserv.service_no like 'SER%', vserv.servicename,'nada'))) as 'det',
(IF (vpro.product_no like 'PRO%', 'NIU' ,'ZZ')) as 'umed',
quantity as 'cant',
'01' as 'tpre',
(CASE(ISNULL( listprice )) WHEN 1 THEN 0 WHEN 0 THEN CAST(listprice AS DECIMAL (20,2)) END) as 'pre',
(CASE(ISNULL( listprice * quantity )) WHEN 1 THEN 0 WHEN 0 THEN CAST(listprice * quantity AS DECIMAL (20,2)) END) as 'subt',
(CASE(ISNULL( invp.discount_amount )) WHEN 1 THEN 0 WHEN 0 THEN CAST(invp.discount_amount AS DECIMAL (20,2)) END) as 'dscto',
(CASE(ISNULL( (listprice*quantity)*0.18 )) WHEN 1 THEN 0 WHEN 0 THEN CAST(((listprice*quantity)*0.18) AS DECIMAL (20,2)) END) as 'igv',
(CASE(ISNULL( (listprice*quantity)*1.18 )) WHEN 1 THEN 0 WHEN 0 THEN CAST(((listprice*quantity)*1.18) AS DECIMAL (20,2)) END) as 'tot',
'10' as 'timp',
'0.00' as 'exo',
'0.00' as 'ina',
(CASE(ISNULL( listprice*quantity )) WHEN 1 THEN 0 WHEN 0 THEN CAST(listprice*quantity AS DECIMAL (20,2)) END) as 'grav',
'0.00' as 'grat',
(CASE(ISNULL( (listprice*quantity)*1.18 )) WHEN 1 THEN 0 WHEN 0 THEN CAST(((listprice*quantity)*1.18) AS DECIMAL (20,2)) END) as 'total',
(IF (vpro.product_no like 'PRO%', vpro.usageunit,IF(vserv.service_no like 'SER%' ,vserv.service_usageunit,'nada'))) as 'unimed'
from alioz_vtig724.vtiger_inventoryproductrel invp
left join alioz_vtig724.vtiger_products vpro ON vpro.productid = invp.productid
left join alioz_vtig724.vtiger_invoice vinv ON vinv.invoiceid = invp.id
left join alioz_vtig724.vtiger_service vserv ON vserv.serviceid = invp.productid
where vinv.invoicestatus='Enviando detalle a Sunat'
order by vinv.invoiceid desc;
update alioz_vtig724.vtiger_invoice vti left join alioz_fact.ventas_cab vca ON vti.invoice_no = vca.nro set invoicestatus='Enviada a Sunat'
where vti.invoicestatus = 'Enviando detalle a Sunat'
END |
delimiter ;