Definir Numero nStatusProcesso; Definir alfa aMensagemProcesso; Definir Alfa aResponse_JsonOutput; Definir Alfa aSql; Definir Alfa aAspaDupla; Definir Numero nParam_CodEmp; Definir Numero nParam_CodFil; Definir Numero nParam_NumAne; Definir Numero nParam_NumPfa; Definir Alfa aParam_CodEmp; Definir Alfa aParam_CodFil; Definir Alfa aParam_NumAne; Definir Alfa aParam_NumPfa; Definir Interno.com.br.Soelx.Util.genericConsult WsgenericConsult; Definir Funcao IniciaProcessamento(); Definir Funcao MontaResposta(); Definir Funcao BuscaDadosPrefatura(); aAspaDupla = "\""; IniciaProcessamento(); @------------------------------------------------------------------------------@ @----Função para iniciar a distribuição do processamento das informações-------@ @------------------------------------------------------------------------------@ Funcao IniciaProcessamento();{ aResponse_JsonOutput = ""; nStatusProcesso = 1; aMensagemProcesso ="Processamento realizado com sucesso."; nParam_CodEmp=Listar.CodEmp; nParam_CodFil=Listar.CodFil; nParam_NumAne=Listar.NumAne; nParam_NumPfa=Listar.NumPfa; nParam_OffSet=Listar.OffSet; nParam_Limit =Listar.Limit; Se(nParam_Limit=0){ nParam_Limit = 99999999; } Se(nStatusProcesso=1){ BuscaDadosPrefatura(); } MontaResposta(); } @--Função que devolve o resultado do processamento dos dados de entrada--------@ Funcao MontaResposta();{ Se(nStatusProcesso=1){ Listar.JsonOutput = aResponse_JsonOutput; } Listar.StatusProcesso = nStatusProcesso; Listar.MensagemProcesso = aMensagemProcesso; } Funcao BuscaDadosPrefatura();{ IntParaAlfa(nParam_CodEmp,aParam_CodEmp); IntParaAlfa(nParam_CodFil,aParam_CodFil); IntParaAlfa(nParam_NumAne,aParam_NumAne); IntParaAlfa(nParam_NumPfa,aParam_NumPfa); aSql = "select \ e135ane.codemp as e135ane_codemp,\ e135ane.codfil as e135ane_codfil,\ e135ane.numane as e135ane_numane,\ e135ane.sitreg as e135ane_sitreg,\ e135ane.sitcar as e135ane_sitcar,\ e135pfa.numpfa as e135pfa_numpfa,\ e135pfa.codcli as e135pfa_codcli,\ e085cli.nomcli as e085cli_nomcli,\ e085cli.cgccpf as e085cli_cgccpf,\ e085cli.cidcli as e085cli_cidcli,\ e085cli.baicli as e085cli_baicli,\ e085cli.sigufs as e085cli_sigufs,\ e085cli.cepcli as e085cli_cepcli,\ e135pfa.codrep as e135pfa_codrep,\ e090rep.nomrep as e090rep_nomrep,\ e135pfa.codcpg as e135pfa_codcpg,\ e135pfa.codfpg as e135pfa_codfpg,\ e135pfa.codtra as e135pfa_codtra,\ e073tra.nomtra as e073tra_nomtra,\ e135pfa.pesbru as e135pfa_pesbru,\ e135pfa.pesliq as e135pfa_pesliq,\ e135pfa.volpfa as e135pfa_volpfa,\ e135pfa.ciffob as e135pfa_ciffob,\ e135pfa.vlrpfa as e135pfa_vlrpfa,\ case when e073tra.codtra is not null then cast(e073tra.codtra as varchar(10)) || '-' || e073tra.nomtra else '' end as transportadora,\ case when redespacho.codtra is not null then cast(redespacho.codtra as varchar(10)) || '-' || redespacho.nomtra else ' ' end as redespacho,\ e135pfa.obspfa as observacoes,\ substr((\ select 'Pedido:' || substr(\ rtrim(xmlagg(\ xmlelement(e, linha.valor || ', ').extract('//text()') order by linha.valor\ ).GetClobVal(), ',')\ , 1, 3600)\ from (\ select distinct\ cast(e120ped.numped as varchar(10)) || ' - ' ||\ trim(replace(coalesce(e120ped.usu_obsped, ''), chr(13), '')) as valor\ from e135pes\ inner join e120ped\ on e120ped.codemp = e135pes.codemp\ and e120ped.codfil = e135pes.codfil\ and e120ped.numped = e135pes.numped\ where e135pes.codemp = e135pfa.codemp\ and e135pes.codfil = e135pfa.codfil\ and e135pes.numane = e135pfa.numane\ and e135pes.numpfa = e135pfa.numpfa\ ) linha), 1, 4000) as obs_interna,\ case when trim(e085cli.usu_obspfa) <> ' ' then e085cli.usu_obspfa else ' ' end as obs_cliente,\ ' ' as obs_nota,\ e085cli.endcli ||\ case when trim(e085cli.nencli) <> ' ' then ', ' || e085cli.nencli else ' ' end || ' - ' ||\ e085cli.baicli || ', ' || e085cli.cidcli || ' - ' || e085cli.sigufs || ' Cep:' ||\ substr(lpad(e085cli.cepcli, 8, '0'), 1, 2) || '.' ||\ substr(lpad(e085cli.cepcli, 8, '0'), 3, 3) || '-' ||\ substr(lpad(e085cli.cepcli, 8, '0'), 6, 3) as endereco_cliente,\ case when e135pfa.seqent = 0 then ' ' else\ e085ent.endent ||\ case when trim(e085ent.nenent) <> ' ' then ', ' || e085ent.nenent else ' ' end || ' - ' ||\ e085ent.baient || ', ' || e085ent.cident || ' - ' || e085ent.estent || ' Cep:' ||\ substr(lpad(e085ent.cepent, 8, '0'), 1, 2) || '.' ||\ substr(lpad(e085ent.cepent, 8, '0'), 3, 3) || '-' ||\ substr(lpad(e085ent.cepent, 8, '0'), 6, 3)\ end as endereco_entrega,\ r910usu.nomcom as usuario_gerador,\ e135pfa.usu_seplib as e135pfa_seplib,\ e135pfa.usu_data1 as e135pfa_prvfat,\ case when e135pfa.sitpfa = 9 and exists (select 1 from e135obs \ where e135obs.codemp = e135pfa.codemp \ and e135obs.codfil = e135pfa.codfil \ and e135obs.numane = e135pfa.numane \ and e135obs.numpfa = e135pfa.numpfa \ and e135obs.obspfa like '%Pré-Fatura Cancelada pelo processo de agrupamento de pré-faturas%' \ ) then 4 \ else e135pfa.sitpfa \ end as e135pfa_sitpfa, \ e028cpg.codcpg as e028cpg_codcpg,\ e028cpg.descpg as e028cpg_descpg,\ e066fpg.codfpg as e066fpg_codfpg,\ e066fpg.desfpg as e066fpg_desfpg \ from e135ane \ inner join e135pfa \ on e135pfa.codemp = e135ane.codemp\ and e135pfa.codfil = e135ane.codfil\ and e135pfa.numane = e135ane.numane\ left join e028cpg \ on e028cpg.codcpg = e135pfa.codcpg \ and e028cpg.codemp = e135pfa.codemp \ left join e066fpg \ on e066fpg.codfpg = e135pfa.codfpg \ and e066fpg.codemp = e135pfa.codemp \ inner join e085cli \ on e085cli.codcli = e135pfa.codcli\ left join e090rep \ on e090rep.codemp = e135pfa.codemp\ and e090rep.codrep = e135pfa.codrep\ left join e073tra \ on e073tra.codtra = e135pfa.codtra\ left join e073tra redespacho \ on redespacho.codtra = e135pfa.codred\ left join e085ent \ on e085ent.codcli = e135pfa.codcli\ and e085ent.seqent = e135pfa.seqent\ left join r910usu \ on r910usu.codent = e135pfa.usuger\ where ((0 = " + aParam_CodEmp + " and e135ane.codemp = 1) or e135ane.codemp = " + aParam_CodEmp + ")\ and ((0 = " + aParam_CodFil + " and e135ane.codfil = 1) or e135ane.codfil = " + aParam_CodFil + ")\ and coalesce(e135pfa.usu_seplib,'N') = 'S' \ and (e135pfa.USU_DATA1 >= trunc(current_date) and e135pfa.USU_DATA1 <= (trunc(current_date) + 120) or " + aParam_NumAne + " <> 0 )" + " and (e135pfa.numane = "+ aParam_NumAne +" or 0=" + aParam_NumAne + ")" + " and (e135pfa.numpfa = "+ aParam_NumPfa +" or 0=" + aParam_NumPfa + ")" ; /* and e135pfa.USU_DATA1 >= to_date(current_date,'DD/MM/YYYY')" + and e135pfa.numnfv = 0 \ and e135pfa.sitpfa not in (4,9) \ and (e135pfa.sitpfa not in (4,9) or " + aParam_NumAne + " <> 0 )" + */ WsgenericConsult.queryCompleted = aSql; WsgenericConsult.Limit = nParam_Limit; WsgenericConsult.OffSet = nParam_OffSet; WsgenericConsult.Executar(); Se(WsgenericConsult.ErroExecucao <> ""){ nStatusProcesso =0; aMensagemProcesso =WsgenericConsult.ErroExecucao; }senao { aResponse_JsonOutput = WsgenericConsult.pmJsonResponse; } }