MySQL Forums
Forum List  »  Spanish

Re: evento con varias query
Posted by: Josue Puma Jimenez
Date: May 01, 2018 02:31PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
440
May 01, 2018 02:28PM
Re: evento con varias query
137
May 01, 2018 02:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.