Alias | Descrição | Tamanho | Tipo | Formato | Obrigatório |
---|---|---|---|---|---|
TabelaProcedimento | Código da tabela TISS do procedimento | 2 | Caractere | x | |
Procedimento | Código do procedimento | 15 | Caractere | x | |
DenteRegiao | Código dente região | 4 | Caractere | ||
Face | Face do dente | 5 | Caractere |
View para clientes com o sistema de gestão ERP Protheus - Módulo Planos de Saúde
Bloco de código | ||
---|---|---|
| ||
SELECT
NVL(BTU.BTU_CODTAB, BTQ.BTQ_CODTAB) AS TabelaProcedimento,
NVL(BTU.BTU_CDTERM, BTQ.BTQ_CDTERM) AS Procedimento,
DenteRegiao,
Face
FROM (
SELECT
BYL_CODPAD TabelaProcedimento,
BYL_CODPSA Procedimento,
BYL_CODIGO DenteRegiao,
SUBSTR(BYL_FACE, numbers.n, 1) Face
FROM
(SELECT 1 n FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 4 FROM DUAL UNION ALL SELECT 5 FROM DUAL) numbers
INNER JOIN BYL010 BYL ON LENGTH(BYL.BYL_FACE)>numbers.n-1
WHERE
BYL_FACE <> ' '
AND SUBSTR(BYL_FACE, numbers.n, 1) <> ' '
AND D_E_L_E_T_ = ' '
AND D_E_L_E_T_ = ' '
UNION ALL
SELECT
B05_CODPAD TabelaProcedimento,
B05_CODPSA Procedimento,
B05_CODIGO DenteRegiao,
' ' Face
FROM B05010
WHERE
B05_CODPSA NOT IN (SELECT BYL_CODPSA FROM BYL010)
AND D_E_L_E_T_ = ' '
AND D_E_L_E_T_ = ' '
) temp
LEFT JOIN BTU010 BTU ON (BTU.BTU_VLRBUS = Procedimento AND BTU.D_E_L_E_T_ = ' ')
LEFT JOIN BTQ010 BTQ ON (BTQ.BTQ_CDTERM = Procedimento AND BTQ.D_E_L_E_T_ = ' ')
WHERE
(BTQ_CODTAB IS NULL OR BTQ_CODTAB IN ('18', '19', '20', '22', '90', '98'))
AND (BTU_CODTAB IS NULL OR BTU_CODTAB IN ('18', '19', '20', '22', '90', '98'))
AND ((BTU_CODTAB IS NOT NULL AND BTU_CDTERM IS NOT NULL) OR (BTQ_CODTAB IS NOT NULL AND BTQ_CDTERM IS NOT NULL))
GROUP BY NVL(BTU.BTU_CODTAB, BTQ.BTQ_CODTAB),NVL(BTU.BTU_CDTERM, BTQ.BTQ_CDTERM),DenteRegiao,Face
|
Bloco de código | ||||
---|---|---|---|---|
| ||||
SELECT ISNULL(BTU.BTU_CODTAB, BTQ.BTQ_CODTAB) AS TabelaProcedimento, ISNULL(BTU.BTU_CDTERM, BTQ.BTQ_CDTERM) AS Procedimento, DenteRegiao, Face FROM ( SELECT BYL_CODPAD TabelaProcedimento, BYL_CODPSA Procedimento, BYL_CODIGO DenteRegiao, SUBSTRING(BYL_FACE, numbers.n, 1) Face FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers INNER JOIN BYL010 BYL ON LEN(BYL.BYL_FACE)>numbers.n-1 WHERE S_T_A_M_P_ > (DATEADD(HOUR, -1, GETUTCDATE())) AND D_E_L_E_T_ = ' ' AND BYL_FACE <> ' ' AND SUBSTRING(BYL_FACE, numbers.n, 1) <> ' ' UNION ALL SELECT B05_CODPAD TabelaProcedimento, B05_CODPSA Procedimento, B05_CODIGO DenteRegiao, ' ' Face |
Observação: Para a view de carga inicial, basta remover o filtro de STAMP.
...