Linha 14: |
Linha 14: |
| == Exemplo da SQL de Faturamento == | | == Exemplo da SQL de Faturamento == |
| | | |
− | * Teste
| + | <syntaxhighlight lang="sql"> |
| + | DECLARE @CODCOLIGADA INT = 7; |
| + | DECLARE @DATAINICIAL DATE = '2021-05-01'; |
| + | DECLARE @DATAFINAL DATE = '2021-05-31'; |
| + | DECLARE @CODCCUSTO VARCHAR(30) = '%'; |
| + | DECLARE @SERVICO VARCHAR(100) = CASE |
| + | WHEN @CODCOLIGADA = 2 THEN '2,1001,1003,1004,106,1015,1017,1030,2003,2008,2010,2012' |
| + | WHEN @CODCOLIGADA = 4 THEN '8,9,1009,1015,1022,1027,1032,1033,2006,2010,3002,3004' |
| + | WHEN @CODCOLIGADA = 5 THEN '2,1006,1008,1011,1042,2022,5001,5002,5003' |
| + | WHEN @CODCOLIGADA = 6 THEN '3,7,8,9,1001,1003,1004,1005,1012,1015,1025,1042,2010,6001,6002' |
| + | WHEN @CODCOLIGADA = 7 THEN '2,4,8' |
| + | WHEN @CODCOLIGADA = 8 THEN '2,8,21' |
| + | WHEN @CODCOLIGADA = 5 THEN '17,1001,1015,1016,1017,1018,1023,1025,1031,1040,1041,2023,2026,3002,11002' |
| + | WHEN @CODCOLIGADA = 14 THEN '7,11,15,2003,3002' |
| + | WHEN @CODCOLIGADA = 20 THEN '9,16,101,1004,1005,1025,2010' |
| + | WHEN @CODCOLIGADA = 22 THEN '12,20,22,30,31,1006,2003,2004,2005,2007,2008,2014,2022,3002,22001,22002,22003,22004,22008,22009,22012,22013,22015,22016,22017,22018,22019' |
| + | WHEN @CODCOLIGADA = 23 THEN '5,101,1025,2001,2002,2003,2010,2018,3004,3015,11006,23004,23008,23009,23010' |
| + | WHEN @CODCOLIGADA = 26 THEN '29,3011' |
| + | WHEN @CODCOLIGADA = 28 THEN '9,1015,1033,1034,1035,1036,1037,1039,1044,1045,2002,2003,2006,2010,2020,23007,28001,28002,28003' |
| + | WHEN @CODCOLIGADA = 31 THEN '3,7,13,19,1001,2003,2007,2008,2010,2013,2016,2026,2027,3002,3007,3008,3017,3018,11002' |
| + | WHEN @CODCOLIGADA = 32 THEN '7,1010,2003,2007,2010,2020,2027,2030,3002,3007,3008,3010,3017' |
| + | ELSE '0' |
| + | END; |
| | | |
| + | SELECT BC.CODCONTA, BC.CONTA + ' - ' + CAST(BC.REDUZIDO AS VARCHAR) CONTA, B.NOME BOLSA, L.IDLAN, |
| + | /* 10/06/2019 - ADICIONADO PARA TRATAR CASOS EM QUE A BOLSA CONTABILIZA EM MAIS DE UMA CONTA */ |
| + | CAST(CASE |
| + | WHEN L.CODCOLIGADA IN (7) AND B.FIES = 1 AND BA.TIPODESC = 'P' AND @DATAINICIAL <= '2016-06-30' THEN /*UTILIZADO SOMENTE PELA FAHOR*/ |
| + | CAST(BL.VALOR AS NUMERIC(15,2)) - (FLOOR((BL.VALOR * 0.05) * 100) / 100.0) |
| + | ELSE |
| + | ROUND(CAST(BL.VALOR * (BC.PERCENTUAL / 100.00) AS NUMERIC(15,2)), 2, 1) |
| + | END AS NUMERIC(15,2)) VALOR |
| + | FROM FLAN L (NOLOCK) |
| + | INNER JOIN SLAN S (NOLOCK) ON (L.CODCOLIGADA = S.CODCOLIGADA |
| + | AND L.IDLAN = S.IDLAN) |
| + | INNER JOIN SPARCELA P (NOLOCK) ON (S.CODCOLIGADA = P.CODCOLIGADA |
| + | AND S.IDPARCELA = P.IDPARCELA) |
| + | INNER JOIN SBOLSALAN BL ON (S.CODCOLIGADA = BL.CODCOLIGADA |
| + | AND S.IDPARCELA = BL.IDPARCELA |
| + | AND S.IDLAN = BL.IDLAN |
| + | AND P.IDPERLET = BL.IDPERLET) |
| + | INNER JOIN SBOLSA B (NOLOCK) ON (BL.CODCOLIGADA = B.CODCOLIGADA |
| + | AND BL.CODBOLSA = B.CODBOLSA |
| + | AND B.VALIDADELIMITADA = 1) |
| + | INNER JOIN SCONTRATO CO (NOLOCK) ON (P.CODCOLIGADA = CO.CODCOLIGADA |
| + | AND P.CODCONTRATO = CO.CODCONTRATO |
| + | AND P.RA = CO.RA) /*LINHA ADICIONADO DIA 02/09/2016 POR: KEVIN */ |
| + | INNER JOIN SBOLSAALUNO BA (NOLOCK) ON (CO.CODCOLIGADA = BA.CODCOLIGADA |
| + | AND CO.RA = BA.RA |
| + | AND CO.IDPERLET = BA.IDPERLET |
| + | AND CO.CODCONTRATO = BA.CODCONTRATO |
| + | AND B.CODBOLSA = BA.CODBOLSA |
| + | AND P.CODSERVICO = BA.CODSERVICO |
| + | AND (P.DTVENCIMENTO BETWEEN BA.DTINICIO AND BA.DTFIM |
| + | OR P.PARCELA BETWEEN BA.PARCELAINICIAL AND BA.PARCELAFINAL) |
| + | /* 02/10/2019 - ALTERAÇÃO PARA SE ADAPTAR A NOVA BOLSA RETROATIVA */ |
| + | AND (ISNULL(BA.BOLSARETROATIVA, 'N') = 'N' |
| + | OR (BA.BOLSARETROATIVA = 'S' |
| + | AND BA.DATACONCESSAO <= L.DATACRIACAO))) |
| + | /* 02/10/2019 - ALTERAÇÃO PARA SE ADAPTAR A NOVA BOLSA RETROATIVA */ |
| + | /* 10/06/2019 - ADICIONADO PARA TRATAR CASOS EM QUE A BOLSA CONTABILIZA EM MAIS DE UMA CONTA */ |
| + | INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY X.CODCOLIGADA, X.CODBOLSA ORDER BY X.CODCOLIGADA, X.CODBOLSA, X.CODCONTA) ID, |
| + | X.CODCOLIGADA, X.CODBOLSA, X.CODCONTA, X.CONTA, X.REDUZIDO, X.PERCENTUAL |
| + | FROM ( |
| + | SELECT DISTINCT BC.CODCOLIGADA, BC.CODBOLSA, BC.CODCONTA, C.DESCRICAO CONTA, C.REDUZIDO, BC.PERCENTUAL |
| + | FROM SBOLSACONT BC (NOLOCK) |
| + | INNER JOIN CCONTA C (NOLOCK) ON (BC.CODCOLCONTA = C.CODCOLIGADA |
| + | AND BC.CODCONTA = C.CODCONTA) |
| + | WHERE BC.CODCOLIGADA = @CODCOLIGADA |
| + | ) X) BC ON (B.CODCOLIGADA = BC.CODCOLIGADA |
| + | AND B.CODBOLSA = BC.CODBOLSA) |
| + | WHERE L.CODCOLIGADA = @CODCOLIGADA |
| + | AND L.TIPOCONTABILLAN = 1 |
| + | AND L.STATUSLAN <> 2 |
| + | AND BC.REDUZIDO = '2016' |
| + | AND EXISTS (SELECT DISTINCT L1.IDLAN |
| + | FROM FLAN L1 (NOLOCK) |
| + | INNER JOIN COPERACAO O1 (NOLOCK) ON (L1.CODCOLIGADA = O1.CODCOLIGADA |
| + | AND L1.IDOPERACAO = O1.IDOPERACAO) |
| + | INNER JOIN CCONT C1 (NOLOCK) ON (O1.CODCOLIGADA = C1.CODCOLIGADA |
| + | AND O1.IDOPERACAO = C1.IDOPERACAO) |
| + | INNER JOIN CPARTIDA P1 (NOLOCK) ON (C1.CODCOLIGADA = P1.CODCOLIGADA |
| + | AND C1.IDPARTIDA = P1.IDPARTIDA) |
| + | WHERE L1.CODCOLIGADA = @CODCOLIGADA |
| + | AND L1.MODELOCONTABILIZACAO <> 1 |
| + | AND O1.ORIGEM = 'FLAN' |
| + | AND C1.TIPO IN (1,3) |
| + | AND C1.STATUS = 'C' |
| + | AND P1.DATA BETWEEN @DATAINICIAL AND @DATAFINAL |
| + | AND L1.CODCCUSTO LIKE @CODCCUSTO |
| + | AND L1.IDLAN = L.IDLAN) |
| + | GROUP BY L.CODCOLIGADA, L.IDLAN, BL.VALOR, BC.ID, BC.CODCONTA, BC.CONTA, BC.REDUZIDO, |
| + | BC.CODBOLSA, BC.PERCENTUAL, B.NOME, B.FIES, BA.TIPODESC |
| + | ORDER BY VALOR, IDLAN |
| + | </syntaxhighlight> |
| | | |
| == Relatório do Razão == | | == Relatório do Razão == |
Linha 28: |
Linha 121: |
| == Exemplo da SQL da Contabilidade == | | == Exemplo da SQL da Contabilidade == |
| | | |
− | * Teste
| + | <syntaxhighlight lang="sql"> |
| + | SELECT F.IDLAN, C.VALOR, |
| + | P.LCTREF, P.DATA, P.VALOR, C.TIPO, C.STATUS, O.ORIGEM |
| + | FROM CPARTIDA P |
| + | INNER JOIN CCONT C ON (P.CODCOLIGADA = C.CODCOLIGADA |
| + | AND P.IDPARTIDA = C.IDPARTIDA) |
| + | INNER JOIN COPERACAO O ON (C.CODCOLIGADA = O.CODCOLIGADA |
| + | AND C.IDOPERACAO = O.IDOPERACAO) |
| + | INNER JOIN FLAN F ON (O.CODCOLIGADA = F.CODCOLIGADA |
| + | AND O.IDOPERACAO = F.IDOPERACAO) |
| + | WHERE P.CODCOLIGADA = 7 |
| + | AND P.LCTREF IN (244333,244331,244340,244368,244373,244374, |
| + | 244375,244386,244391,244414,244431,244440) |
| + | ORDER BY C.VALOR, F.IDLAN |
| + | </syntaxhighlight> |
| | | |
| == Localizar diferenças == | | == Localizar diferenças == |
| * Teste | | * Teste |