- SAP Community
- Products and Technology
- Enterprise Resource Planning
- ERP Q&A
- Views Para Consultas de Documentos/Itens/Impostos
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Views Para Consultas de Documentos/Itens/Impostos
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 10-08-2008 7:24 PM
Pessoal,
Estou trabalhando em algumas views para realizar as conferencias de impostos "por fora" de add-ons. A quem interessar e quiser colaborar logo abaixo estão os códigos;
Todas as views devem ser instaladas, pois estão correlacionadas.
Para as chaves utilizei métodos de Data Warehouse, pois pretendo criar cubos logo após de prontas.
Segue Querys:
select * from VBR_PARCEIROS : Parceiros de Negócios por Endereços
select * from VBR_EMPRESA: Dados da Empresa
select * from VBR_DOCUMENTOS_ITENS: Itens de todos os documentos comtemplados pelo business one
select * from VBR_DOCUMENTOS_IMPOSTOS_ITENS: Impostos cobrados a nivel dos itens dos documentos
select * from VBR_DOCUMENTOS_IMPOSTOS: Totalizadores de todos os impostos por documentos
Código para criação das views:
(Próximos Posts)
Um abraço,
Marcus Alexandre Silva
Especialista C#/ SAP Busines One
- SAP Managed Tags:
- SAP Business One
Accepted Solutions (0)
Answers (5)
Answers (5)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
CREATE VIEW VBR_DOCUMENTOS_IMPOSTOS AS
SELECT
T0.CHAVE_DOCUMENTO + '-' + T0.IMPOSTO CHAVE
,T0.CHAVE_DOCUMENTO
,T0.IMPOSTO
,AVG(CAST(T0.PERCENTUAL AS MONEY)) PERCENTUAL
,SUM(T0.VALOR_DOCUMENTO) VALOR_DOCUMENTO
,SUM(T0.BASE_CALCULO) BASE_CALCULO
,SUM(T0.VALOR_IMPOSTO) VALOR_IMPOSTO
FROM
VBR_DOCUMENTOS_IMPOSTOS_ITENS T0
GROUP BY
T0.IMPOSTO
,T0.CHAVE_DOCUMENTO
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
CREATE VIEW VBR_DOCUMENTOS_IMPOSTOS_ITENS AS
SELECT
'INV-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) + '-' + T01.NAME CHAVE
,'INV-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) CHAVE_LINHA_DOCUMENTO
,'INV-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + T04.CODE CHAVE_IMPOSTO
,'INV-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,T04.CODE IMPOSTO
,T05.STCCODE IMPOSTO_CODIGO
,T01.NAME IMPOSTO_TIPO
,T00.CODE IMPOSTO_ATRIBUTO
,T08.CODE IMPOSTO_FORMULA
,CASE WHEN (T00.TAXINPRICE = 'Y') THEN 'Sim' ELSE 'Não' END INCLUSO_PRECO
,CAST(T05.TAXRATE AS MONEY) PERCENTUAL
,CAST(T07.LINETOTAL AS MONEY) VALOR_DOCUMENTO
,CAST(T05.BASESUM AS MONEY) BASE_CALCULO
,CAST(T05.TAXSUM AS MONEY) VALOR_IMPOSTO
FROM
OSTA T00 INNER JOIN
OSTT T01 ON T00.TYPE = T01.ABSID
INNER JOIN ONFT T04 ON T04.ABSID = T01.NFTAXID
INNER JOIN INV4 T05 ON T05.STACODE = T00.CODE AND T05.STATYPE = T01.ABSID
INNER JOIN STC1 T06 ON T06.STACODE = T00.CODE AND T05.STCCODE = T06.STCCODE
INNER JOIN INV1 T07 ON T05.DOCENTRY = T07.DOCENTRY AND T05.LINENUM = T07.LINENUM
INNER JOIN OFML T08 ON T06.FmlId = T08.AbsId
UNION ALL
SELECT
'DLN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) + '-' + T01.NAME CHAVE
,'DLN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) CHAVE_LINHA_DOCUMENTO
,'DLN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + T04.CODE CHAVE_IMPOSTO
,'DLN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) CHAVE_DOCUMENTO
,T04.CODE IMPOSTO
,T05.STCCODE IMPOSTO_CODIGO
,T01.NAME IMPOSTO_TIPO
,T00.CODE IMPOSTO_ATRIBUTO
,T08.CODE IMPOSTO_FORMULA
,CASE WHEN (T00.TAXINPRICE = 'Y') THEN 'Sim' ELSE 'Não' END INCLUSO_PRECO
,T05.TAXRATE PERCENTUAL
,T07.LINETOTAL VALOR_DOCUMENTO
,T05.BASESUM BASE_CALCULO
,T05.TAXSUM VALOR_IMPOSTO
FROM
OSTA T00 INNER JOIN
OSTT T01 ON T00.TYPE = T01.ABSID
INNER JOIN ONFT T04 ON T04.ABSID = T01.NFTAXID
INNER JOIN DLN4 T05 ON T05.STACODE = T00.CODE AND T05.STATYPE = T01.ABSID
INNER JOIN STC1 T06 ON T06.STACODE = T00.CODE AND T05.STCCODE = T06.STCCODE
INNER JOIN DLN1 T07 ON T05.DOCENTRY = T07.DOCENTRY AND T05.LINENUM = T07.LINENUM
INNER JOIN OFML T08 ON T06.FmlId = T08.AbsId
UNION ALL
SELECT
'RPC-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) + '-' + T01.NAME CHAVE
,'RPC-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) CHAVE_LINHA_DOCUMENTO
,'RPC-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + T04.CODE CHAVE_IMPOSTO
,'RPC-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) CHAVE_DOCUMENTO
,T04.CODE IMPOSTO
,T05.STCCODE IMPOSTO_CODIGO
,T01.NAME IMPOSTO_TIPO
,T00.CODE IMPOSTO_ATRIBUTO
,T08.CODE IMPOSTO_FORMULA
,CASE WHEN (T00.TAXINPRICE = 'Y') THEN 'Sim' ELSE 'Não' END INCLUSO_PRECO
,T05.TAXRATE PERCENTUAL
,T07.LINETOTAL VALOR_DOCUMENTO
,T05.BASESUM BASE_CALCULO
,T05.TAXSUM VALOR_IMPOSTO
FROM
OSTA T00 INNER JOIN
OSTT T01 ON T00.TYPE = T01.ABSID
INNER JOIN ONFT T04 ON T04.ABSID = T01.NFTAXID
INNER JOIN RPC4 T05 ON T05.STACODE = T00.CODE AND T05.STATYPE = T01.ABSID
INNER JOIN STC1 T06 ON T06.STACODE = T00.CODE AND T05.STCCODE = T06.STCCODE
INNER JOIN RPC1 T07 ON T05.DOCENTRY = T07.DOCENTRY AND T05.LINENUM = T07.LINENUM
INNER JOIN OFML T08 ON T06.FmlId = T08.AbsId
UNION ALL
SELECT
'RPD-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) + '-' + T01.NAME CHAVE
,'RPD-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) CHAVE_LINHA_DOCUMENTO
,'RPD-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + T04.CODE CHAVE_IMPOSTO
,'RPD-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) CHAVE_DOCUMENTO
,T04.CODE IMPOSTO
,T05.STCCODE IMPOSTO_CODIGO
,T01.NAME IMPOSTO_TIPO
,T00.CODE IMPOSTO_ATRIBUTO
,T08.CODE IMPOSTO_FORMULA
,CASE WHEN (T00.TAXINPRICE = 'Y') THEN 'Sim' ELSE 'Não' END INCLUSO_PRECO
,T05.TAXRATE PERCENTUAL
,T07.LINETOTAL VALOR_DOCUMENTO
,T05.BASESUM BASE_CALCULO
,T05.TAXSUM VALOR_IMPOSTO
FROM
OSTA T00 INNER JOIN
OSTT T01 ON T00.TYPE = T01.ABSID
INNER JOIN ONFT T04 ON T04.ABSID = T01.NFTAXID
INNER JOIN RPD4 T05 ON T05.STACODE = T00.CODE AND T05.STATYPE = T01.ABSID
INNER JOIN STC1 T06 ON T06.STACODE = T00.CODE AND T05.STCCODE = T06.STCCODE
INNER JOIN RPD1 T07 ON T05.DOCENTRY = T07.DOCENTRY AND T05.LINENUM = T07.LINENUM
INNER JOIN OFML T08 ON T06.FmlId = T08.AbsId
UNION ALL
SELECT
'PCH-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) + '-' + T01.NAME CHAVE
,'PCH-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) CHAVE_LINHA_DOCUMENTO
,'PCH-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + T04.CODE CHAVE_IMPOSTO
,'PCH-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) CHAVE_DOCUMENTO
,T04.CODE IMPOSTO
,T05.STCCODE IMPOSTO_CODIGO
,T01.NAME IMPOSTO_TIPO
,T00.CODE IMPOSTO_ATRIBUTO
,T08.CODE IMPOSTO_FORMULA
,CASE WHEN (T00.TAXINPRICE = 'Y') THEN 'Sim' ELSE 'Não' END INCLUSO_PRECO
,T05.TAXRATE PERCENTUAL
,T07.LINETOTAL VALOR_DOCUMENTO
,T05.BASESUM BASE_CALCULO
,T05.TAXSUM VALOR_IMPOSTO
FROM
OSTA T00 INNER JOIN
OSTT T01 ON T00.TYPE = T01.ABSID
INNER JOIN ONFT T04 ON T04.ABSID = T01.NFTAXID
INNER JOIN PCH4 T05 ON T05.STACODE = T00.CODE AND T05.STATYPE = T01.ABSID
INNER JOIN STC1 T06 ON T06.STACODE = T00.CODE AND T05.STCCODE = T06.STCCODE
INNER JOIN PCH1 T07 ON T05.DOCENTRY = T07.DOCENTRY AND T05.LINENUM = T07.LINENUM
INNER JOIN OFML T08 ON T06.FmlId = T08.AbsId
UNION ALL
SELECT
'RIN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) + '-' + T01.NAME CHAVE
,'RIN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) CHAVE_LINHA_DOCUMENTO
,'RIN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + T04.CODE CHAVE_IMPOSTO
,'RIN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) CHAVE_DOCUMENTO
,T04.CODE IMPOSTO
,T05.STCCODE IMPOSTO_CODIGO
,T01.NAME IMPOSTO_TIPO
,T00.CODE IMPOSTO_ATRIBUTO
,T08.CODE IMPOSTO_FORMULA
,CASE WHEN (T00.TAXINPRICE = 'Y') THEN 'Sim' ELSE 'Não' END INCLUSO_PRECO
,T05.TAXRATE PERCENTUAL
,T07.LINETOTAL VALOR_DOCUMENTO
,T05.BASESUM BASE_CALCULO
,T05.TAXSUM VALOR_IMPOSTO
FROM
OSTA T00 INNER JOIN
OSTT T01 ON T00.TYPE = T01.ABSID
INNER JOIN ONFT T04 ON T04.ABSID = T01.NFTAXID
INNER JOIN RIN4 T05 ON T05.STACODE = T00.CODE AND T05.STATYPE = T01.ABSID
INNER JOIN STC1 T06 ON T06.STACODE = T00.CODE AND T05.STCCODE = T06.STCCODE
INNER JOIN RIN1 T07 ON T05.DOCENTRY = T07.DOCENTRY AND T05.LINENUM = T07.LINENUM
INNER JOIN OFML T08 ON T06.FmlId = T08.AbsId
UNION ALL
SELECT
'RDN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) + '-' + T01.NAME CHAVE
,'RDN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) CHAVE_LINHA_DOCUMENTO
,'RDN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + T04.CODE CHAVE_IMPOSTO
,'RDN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) CHAVE_DOCUMENTO
,T04.CODE IMPOSTO
,T05.STCCODE IMPOSTO_CODIGO
,T01.NAME IMPOSTO_TIPO
,T00.CODE IMPOSTO_ATRIBUTO
,T08.CODE IMPOSTO_FORMULA
,CASE WHEN (T00.TAXINPRICE = 'Y') THEN 'Sim' ELSE 'Não' END INCLUSO_PRECO
,T05.TAXRATE PERCENTUAL
,T07.LINETOTAL VALOR_DOCUMENTO
,T05.BASESUM BASE_CALCULO
,T05.TAXSUM VALOR_IMPOSTO
FROM
OSTA T00 INNER JOIN
OSTT T01 ON T00.TYPE = T01.ABSID
INNER JOIN ONFT T04 ON T04.ABSID = T01.NFTAXID
INNER JOIN RDN4 T05 ON T05.STACODE = T00.CODE AND T05.STATYPE = T01.ABSID
INNER JOIN STC1 T06 ON T06.STACODE = T00.CODE AND T05.STCCODE = T06.STCCODE
INNER JOIN RDN1 T07 ON T05.DOCENTRY = T07.DOCENTRY AND T05.LINENUM = T07.LINENUM
INNER JOIN OFML T08 ON T06.FmlId = T08.AbsId
UNION ALL
SELECT
'PDN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) + '-' + T01.NAME CHAVE
,'PDN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + '-' + CAST(T05.LINENUM + 1 AS VARCHAR) CHAVE_LINHA_DOCUMENTO
,'PDN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) + CAST(T05.DOCENTRY AS VARCHAR) + '-' + T04.CODE CHAVE_IMPOSTO
,'PDN-' + REPLICATE('0', 8 - Len(CAST(T05.DOCENTRY AS VARCHAR))) CHAVE_DOCUMENTO
,T04.CODE IMPOSTO
,T05.STCCODE IMPOSTO_CODIGO
,T01.NAME IMPOSTO_TIPO
,T00.CODE IMPOSTO_ATRIBUTO
,T08.CODE IMPOSTO_FORMULA
,CASE WHEN (T00.TAXINPRICE = 'Y') THEN 'Sim' ELSE 'Não' END INCLUSO_PRECO
,T05.TAXRATE PERCENTUAL
,T07.LINETOTAL VALOR_DOCUMENTO
,T05.BASESUM BASE_CALCULO
,T05.TAXSUM VALOR_IMPOSTO
FROM
OSTA T00 INNER JOIN
OSTT T01 ON T00.TYPE = T01.ABSID
INNER JOIN ONFT T04 ON T04.ABSID = T01.NFTAXID
INNER JOIN PDN4 T05 ON T05.STACODE = T00.CODE AND T05.STATYPE = T01.ABSID
INNER JOIN STC1 T06 ON T06.STACODE = T00.CODE AND T05.STCCODE = T06.STCCODE
INNER JOIN PDN1 T07 ON T05.DOCENTRY = T07.DOCENTRY AND T05.LINENUM = T07.LINENUM
INNER JOIN OFML T08 ON T06.FmlId = T08.AbsId
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
CREATE VIEW VBR_DOCUMENTOS_ITENS AS
SELECT
'INV-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) + '-' + CAST(T00.LINENUM + 1 AS VARCHAR) CHAVE
,'INV-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,CAST(T00.LINENUM + 1 AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM INV1 WHERE INV1.DOCENTRY = T00.DOCENTRY) AS VARCHAR) LINHA
,T00.ITEMCODE ITEM
,T00.CODEBARS CODIGO_BARRAS
,T00.DSCRIPTION DESCRICAO
,T00.QUANTITY QUANTIDADE
,T00.CURRENCY MOEDA
,T00.PRICE PRECO
,T00.DISCPRCNT DESCONTO
,T00.LINETOTAL TOTAL
,T00.CFOPCODE CODIGO_CFOP
,CONVERT(NVARCHAR(300),T01.DESCRIP) CFOP
,T00.CSTCODE CODIGO_CST
,T00.USAGE CODIGO_UTILIZACAO
,T02.USAGE UTILIZACAO
FROM
INV1 T00
INNER JOIN OCFP T01 ON T01.CODE = T00.CFOPCODE
INNER JOIN OUSG T02 ON T00.USAGE = T02.ID
UNION ALL
SELECT
'DLN-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) + '-' + CAST(T00.LINENUM + 1 AS VARCHAR) CHAVE
,'DLN-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,CAST(T00.LINENUM + 1 AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM DLN1 WHERE DLN1.DOCENTRY = T00.DOCENTRY) AS VARCHAR) LINHA
,T00.ITEMCODE ITEM
,T00.CODEBARS CODIGO_BARRAS
,T00.DSCRIPTION DESCRICAO
,T00.QUANTITY QUANTIDADE
,T00.CURRENCY MOEDA
,T00.PRICE PRECO
,T00.DISCPRCNT DESCONTO
,T00.LINETOTAL TOTAL
,T00.CFOPCODE CODIGO_CFOP
,CONVERT(NVARCHAR(300),T01.DESCRIP) CFOP
,T00.CSTCODE CODIGO_CST
,T00.USAGE CODIGO_UTILIZACAO
,T02.USAGE UTILIZACAO
FROM
DLN1 T00
INNER JOIN OCFP T01 ON T01.CODE = T00.CFOPCODE
INNER JOIN OUSG T02 ON T00.USAGE = T02.ID
UNION ALL
SELECT
'RPC-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) + '-' + CAST(T00.LINENUM + 1 AS VARCHAR) CHAVE
,'RPC-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,CAST(T00.LINENUM + 1 AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM RPC1 WHERE RPC1.DOCENTRY = T00.DOCENTRY) AS VARCHAR) LINHA
,T00.ITEMCODE ITEM
,T00.CODEBARS CODIGO_BARRAS
,T00.DSCRIPTION DESCRICAO
,T00.QUANTITY QUANTIDADE
,T00.CURRENCY MOEDA
,T00.PRICE PRECO
,T00.DISCPRCNT DESCONTO
,T00.LINETOTAL TOTAL
,T00.CFOPCODE CODIGO_CFOP
,CONVERT(NVARCHAR(300),T01.DESCRIP) CFOP
,T00.CSTCODE CODIGO_CST
,T00.USAGE CODIGO_UTILIZACAO
,T02.USAGE UTILIZACAO
FROM
RPC1 T00
INNER JOIN OCFP T01 ON T01.CODE = T00.CFOPCODE
INNER JOIN OUSG T02 ON T00.USAGE = T02.ID
UNION ALL
SELECT
'RPD-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) + '-' + CAST(T00.LINENUM + 1 AS VARCHAR) CHAVE
,'RPD-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,CAST(T00.LINENUM + 1 AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM RPD1 WHERE RPD1.DOCENTRY = T00.DOCENTRY) AS VARCHAR) LINHA
,T00.ITEMCODE ITEM
,T00.CODEBARS CODIGO_BARRAS
,T00.DSCRIPTION DESCRICAO
,T00.QUANTITY QUANTIDADE
,T00.CURRENCY MOEDA
,T00.PRICE PRECO
,T00.DISCPRCNT DESCONTO
,T00.LINETOTAL TOTAL
,T00.CFOPCODE CODIGO_CFOP
,CONVERT(NVARCHAR(300),T01.DESCRIP) CFOP
,T00.CSTCODE CODIGO_CST
,T00.USAGE CODIGO_UTILIZACAO
,T02.USAGE UTILIZACAO
FROM
RPD1 T00
INNER JOIN OCFP T01 ON T01.CODE = T00.CFOPCODE
INNER JOIN OUSG T02 ON T00.USAGE = T02.ID
UNION ALL
SELECT
'PCH-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) + '-' + CAST(T00.LINENUM + 1 AS VARCHAR) CHAVE
,'PCH-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,CAST(T00.LINENUM + 1 AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM PCH1 WHERE PCH1.DOCENTRY = T00.DOCENTRY) AS VARCHAR) LINHA
,T00.ITEMCODE ITEM
,T00.CODEBARS CODIGO_BARRAS
,T00.DSCRIPTION DESCRICAO
,T00.QUANTITY QUANTIDADE
,T00.CURRENCY MOEDA
,T00.PRICE PRECO
,T00.DISCPRCNT DESCONTO
,T00.LINETOTAL TOTAL
,T00.CFOPCODE CODIGO_CFOP
,CONVERT(NVARCHAR(300),T01.DESCRIP) CFOP
,T00.CSTCODE CODIGO_CST
,T00.USAGE CODIGO_UTILIZACAO
,T02.USAGE UTILIZACAO
FROM
PCH1 T00
INNER JOIN OCFP T01 ON T01.CODE = T00.CFOPCODE
INNER JOIN OUSG T02 ON T00.USAGE = T02.ID
UNION ALL
SELECT
'RIN-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) + '-' + CAST(T00.LINENUM + 1 AS VARCHAR) CHAVE
,'RIN-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,CAST(T00.LINENUM + 1 AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM RIN1 WHERE RIN1.DOCENTRY = T00.DOCENTRY) AS VARCHAR) LINHA
,T00.ITEMCODE ITEM
,T00.CODEBARS CODIGO_BARRAS
,T00.DSCRIPTION DESCRICAO
,T00.QUANTITY QUANTIDADE
,T00.CURRENCY MOEDA
,T00.PRICE PRECO
,T00.DISCPRCNT DESCONTO
,T00.LINETOTAL TOTAL
,T00.CFOPCODE CODIGO_CFOP
,CONVERT(NVARCHAR(300),T01.DESCRIP) CFOP
,T00.CSTCODE CODIGO_CST
,T00.USAGE CODIGO_UTILIZACAO
,T02.USAGE UTILIZACAO
FROM
RIN1 T00
INNER JOIN OCFP T01 ON T01.CODE = T00.CFOPCODE
INNER JOIN OUSG T02 ON T00.USAGE = T02.ID
UNION ALL
SELECT
'RDN-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) + '-' + CAST(T00.LINENUM + 1 AS VARCHAR) CHAVE
,'RDN-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,CAST(T00.LINENUM + 1 AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM RDN1 WHERE RDN1.DOCENTRY = T00.DOCENTRY) AS VARCHAR) LINHA
,T00.ITEMCODE ITEM
,T00.CODEBARS CODIGO_BARRAS
,T00.DSCRIPTION DESCRICAO
,T00.QUANTITY QUANTIDADE
,T00.CURRENCY MOEDA
,T00.PRICE PRECO
,T00.DISCPRCNT DESCONTO
,T00.LINETOTAL TOTAL
,T00.CFOPCODE CODIGO_CFOP
,CONVERT(NVARCHAR(300),T01.DESCRIP) CFOP
,T00.CSTCODE CODIGO_CST
,T00.USAGE CODIGO_UTILIZACAO
,T02.USAGE UTILIZACAO
FROM
RDN1 T00
INNER JOIN OCFP T01 ON T01.CODE = T00.CFOPCODE
INNER JOIN OUSG T02 ON T00.USAGE = T02.ID
UNION ALL
SELECT
'PDN-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) + '-' + CAST(T00.LINENUM + 1 AS VARCHAR) CHAVE
,'PDN-' + REPLICATE('0', 8 - Len(CAST(T00.DOCENTRY AS VARCHAR))) + CAST(T00.DOCENTRY AS VARCHAR) CHAVE_DOCUMENTO
,CAST(T00.LINENUM + 1 AS VARCHAR) + '/' + CAST((SELECT COUNT(*) FROM PDN1 WHERE PDN1.DOCENTRY = T00.DOCENTRY) AS VARCHAR) LINHA
,T00.ITEMCODE ITEM
,T00.CODEBARS CODIGO_BARRAS
,T00.DSCRIPTION DESCRICAO
,T00.QUANTITY QUANTIDADE
,T00.CURRENCY MOEDA
,T00.PRICE PRECO
,T00.DISCPRCNT DESCONTO
,T00.LINETOTAL TOTAL
,T00.CFOPCODE CODIGO_CFOP
,CONVERT(NVARCHAR(300),T01.DESCRIP) CFOP
,T00.CSTCODE CODIGO_CST
,T00.USAGE CODIGO_UTILIZACAO
,T02.USAGE UTILIZACAO
FROM
PDN1 T00
INNER JOIN OCFP T01 ON T01.CODE = T00.CFOPCODE
INNER JOIN OUSG T02 ON T00.USAGE = T02.ID
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
CREATE VIEW VBR_EMPRESA AS
SELECT
T0.COMPNYNAME EMPRESA
,ISNULL(T0.TAXIDNUM,'') CNPJ
,ISNULL(T0.REVOFFICE,'') CPF
,CASE WHEN (T0.TAXIDNUM IS NULL OR T0.TAXIDNUM = '') THEN 'F' ELSE 'J' END TIPO_PESSOA
,ISNULL(T0.TAXIDNUM2,'') INSCRICAO_ESTADUAL
,CASE WHEN (T0.TAXIDNUM2 IS NULL OR T0.TAXIDNUM2 = 'Isento') THEN 'S' ELSE 'N' END ISENTO_IE
,ISNULL(T0.TAXIDNUM3,'') INSCRICAO_ESTADUAL_ST
,CASE WHEN (T0.TAXIDNUM3 IS NULL OR T0.TAXIDNUM3 = '') THEN 'N' ELSE 'S' END SUBSTITUICAO_TRIBUTARIA
,T0.FREEZONENO INSCRICAO_MUNICIPAL
,T1.CNAECODE CNAE
,T1.DESCRIP CNAE_DESCRICAO
,T2.COUNTY CODIGO_MUNICIPIO
,T3.NAME MUNICIPIO
,T3.STATE ESTADO
,T3.COUNTRY PAIS
,T2.STREET RUA
,T2.BLOCK BAIRRO
,T2.ZIPCODE CEP
FROM
OADM T0
LEFT JOIN OCNA T1 ON T0.DFLTAXCODE = T1.ABSID
LEFT JOIN ADM1 T2 ON T0.CODE = T2.CODE
LEFT JOIN OCNT T3 ON T2.COUNTY = T3.ABSID
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
CREATE VIEW VBR_PARCEIROS AS
SELECT
T0.CARDCODE + '-' + ISNULL(T1.ADDRESS, 'S/E')
CHAVE
,T0.CARDCODE CODIGO
,ISNULL(T1.ADDRESS, 'S/E') SUBCODIGO
,T0.CARDNAME EMPRESA
,CASE WHEN (T0.CARDTYPE = 'C') THEN 'CLIENTE' ELSE
CASE WHEN (T0.CARDTYPE = 'S') THEN 'FORNECEDOR' ELSE 'LEAD'
END END TIPO
,ISNULL(T2.TAXID0,'') CNPJ
,ISNULL(T2.TAXID4,'') CPF
,CASE WHEN ((T2.TAXID0 IS NULL OR T2.TAXID0 = '') AND (T2.TAXID4 IS NULL OR T2.TAXID4 = '')) THEN '' ELSE
CASE WHEN (T2.TAXID0 IS NULL OR T2.TAXID0 = '') THEN 'F' ELSE 'J' END END
TIPO_PESSOA
,ISNULL(T2.TAXID1,'') INSCRICAO_ESTADUAL
,CASE WHEN (T2.TAXID1 IS NULL OR T2.TAXID1 = 'Isento') THEN 'S' ELSE 'N' END
ISENTO
,ISNULL(T2.TAXID2,'') INSCRICAO_ESTADUAL_ST
,CASE WHEN (T2.TAXID2 IS NULL OR T2.TAXID2 = '') THEN 'N' ELSE 'S' END
SUBSTITUICAO_TRIBUTARIA
,ISNULL(T2.TAXID3,'') INSCRICAO_MUNICIPAL
,ISNULL(T2.TAXID8,'') SUFRAMA
,T3.CNAECODE CNAE
,T3.DESCRIP CNAE_DESCRICAO
,T1.COUNTY CODIGO_MUNICIPIO
,T4.NAME MUNICIPIO
,T4.STATE ESTADO
,T4.COUNTRY PAIS
,T1.STREET RUA
,T1.BLOCK BAIRRO
,T1.ZIPCODE CEP
--Colocar Abaixo os Testes de Inconsistencias para Obrigações FIscais
,CASE WHEN (T4.NAME IS NULL) Then 'Municipio Inválido' Else
CASE WHEN (T4.STATE IS NULL) Then 'Estado Inválido' Else
CASE WHEN (T4.COUNTRY IS NULL) Then 'Paiz Inválido' Else
CASE WHEN ((T2.TAXID0 IS NULL OR T2.TAXID0 = '') AND (T2.TAXID4 IS NULL OR T2.TAXID4 = '') AND T1.ADRESTYPE = 'S') Then 'CNPF/CPF Inválido' Else
CASE WHEN ((T2.TAXID0 IS NOT NULL AND T2.TAXID0 <> '') AND (T2.TAXID1 IS NULL OR T2.TAXID1 = '')) Then 'Iscrição Estadual Inválida' Else
NULL End End End End End TESTE_INCONSISTENCIAS
FROM
OCRD T0
LEFT JOIN CRD1 T1 ON T0.CARDCODE = T1.CARDCODE
LEFT JOIN CRD7 T2 ON T2.CARDCODE = T1.CARDCODE AND T2.ADDRESS = T1.ADDRESS
LEFT JOIN OCNA T3 ON T3.ABSID = T2.CNAEID
LEFT JOIN OCNT T4 ON T1.COUNTY = T4.ABSID
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Marcus, muito legal sua iniciativa, você está de parabéns!!
Só localizei um erro na primeira view, que está faltando um sinal de = na linha 38. Segue o código ajustado:
CREATE VIEW VBR_PARCEIROS AS
SELECT
T0.CARDCODE + '-' + ISNULL(T1.ADDRESS, 'S/E')
CHAVE
,T0.CARDCODE CODIGO
,ISNULL(T1.ADDRESS, 'S/E') SUBCODIGO
,T0.CARDNAME EMPRESA
,CASE WHEN (T0.CARDTYPE = 'C') THEN 'CLIENTE' ELSE
CASE WHEN (T0.CARDTYPE = 'S') THEN 'FORNECEDOR' ELSE 'LEAD'
END END TIPO
,ISNULL(T2.TAXID0,'') CNPJ
,ISNULL(T2.TAXID4,'') CPF
,CASE WHEN ((T2.TAXID0 IS NULL OR T2.TAXID0 = '') AND (T2.TAXID4 IS NULL OR T2.TAXID4 = '')) THEN '' ELSE
CASE WHEN (T2.TAXID0 IS NULL OR T2.TAXID0 = '') THEN 'F' ELSE 'J' END END
TIPO_PESSOA
,ISNULL(T2.TAXID1,'') INSCRICAO_ESTADUAL
,CASE WHEN (T2.TAXID1 IS NULL OR T2.TAXID1 = 'Isento') THEN 'S' ELSE 'N' END
ISENTO
,ISNULL(T2.TAXID2,'') INSCRICAO_ESTADUAL_ST
,CASE WHEN (T2.TAXID2 IS NULL OR T2.TAXID2 = '') THEN 'N' ELSE 'S' END
SUBSTITUICAO_TRIBUTARIA
,ISNULL(T2.TAXID3,'') INSCRICAO_MUNICIPAL
,ISNULL(T2.TAXID8,'') SUFRAMA
,T3.CNAECODE CNAE
,T3.DESCRIP CNAE_DESCRICAO
,T1.COUNTY CODIGO_MUNICIPIO
,T4.NAME MUNICIPIO
,T4.STATE ESTADO
,T4.COUNTRY PAIS
,T1.STREET RUA
,T1.BLOCK BAIRRO
,T1.ZIPCODE CEP
--Colocar Abaixo os Testes de Inconsistencias para Obrigações FIscais
,CASE WHEN (T4.NAME IS NULL) Then 'Municipio Inválido' Else
CASE WHEN (T4.STATE IS NULL) Then 'Estado Inválido' Else
CASE WHEN (T4.COUNTRY IS NULL) Then 'Paiz Inválido' Else
CASE WHEN ((T2.TAXID0 IS NULL OR T2.TAXID0 = '') AND (T2.TAXID4 IS NULL OR T2.TAXID4 = '') AND T1.ADRESTYPE = 'S') Then 'CNPF/CPF Inválido' Else
CASE WHEN ((T2.TAXID0 IS NOT NULL AND T2.TAXID0 = '') AND (T2.TAXID1 IS NULL OR T2.TAXID1 = '')) Then 'Iscrição Estadual Inválida' Else
NULL End End End End End TESTE_INCONSISTENCIAS
FROM
OCRD T0
LEFT JOIN CRD1 T1 ON T0.CARDCODE = T1.CARDCODE
LEFT JOIN CRD7 T2 ON T2.CARDCODE = T1.CARDCODE AND T2.ADDRESS = T1.ADDRESS
LEFT JOIN OCNA T3 ON T3.ABSID = T2.CNAEID
LEFT JOIN OCNT T4 ON T1.COUNTY = T4.ABSID
Um abraço,
Danilo Souza - EasyOne Consultoria
- CDS view to fetch: plant address and company code address in Enterprise Resource Planning Q&A
- Is there a CDS view that contains Label Type (MARA-ETIAR) and Label From (MARA-ETIFO) ? in Enterprise Resource Planning Q&A
- What is the CDS view to display plant address in the billing document printout in Enterprise Resource Planning Q&A
- Building Low Code Extensions with Key User Extensibility in SAP S/4HANA and SAP Build in Enterprise Resource Planning Blogs by SAP
- CDS View in ABAP Development Environment Eclipse is showing authorizationerror in test system Public in Enterprise Resource Planning Q&A
| User | Count |
|---|---|
| 104 | |
| 12 | |
| 11 | |
| 6 | |
| 6 | |
| 4 | |
| 3 | |
| 3 | |
| 3 | |
| 3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.