/*Executar o comando abaixo antes de criar a procedure

create view prazomedio as
select n.numord, sum(datediff(day,d.emissao,d.vencimento))/count(d.oid) AS PRAZO
from BDTESTE.DBO.contaapagar_r d, BDTESTE.DBO.nfentracad n 
where d.rdocdeorigem = n.oiddocdeorigem and 
      n.tpo like '1%' and n.atualiz = 1 and n.est in ( '2','3')
group by n.numord */


create procedure cargabi_compras

as

BEGIN

begin transaction
TRUNCATE TABLE NIVEL7_AC_CP
TRUNCATE TABLE NIVEL6_AC_CP
TRUNCATE TABLE NIVEL5_AC_CP
TRUNCATE TABLE NIVEL4_AC_CP
TRUNCATE TABLE NIVEL3_AC_CP
TRUNCATE TABLE NIVEL2_AC_CP
TRUNCATE TABLE NIVEL1_AC_CP
commit 

begin transaction

TRUNCATE TABLE TAB_NIVEL7_AC_CP
TRUNCATE TABLE TAB_NIVEL6_AC_CP
TRUNCATE TABLE TAB_NIVEL5_AC_CP
TRUNCATE TABLE TAB_NIVEL4_AC_CP
TRUNCATE TABLE TAB_NIVEL3_AC_CP
TRUNCATE TABLE TAB_NIVEL2_AC_CP
TRUNCATE TABLE TAB_NIVEL1_AC_CP
commit 


begin transaction


/* --- COMPRAS */
INSERT INTO NIVEL7_AC_CP
SELECT   RIGHT('00000' + LTRIM(RTRIM( ITNE.CODPRO )),5),
         SUBSTRING(CONVERT(VARCHAR,NE.DTCHEG,103) ,7,4) + SUBSTRING(CONVERT(VARCHAR,NE.DTCHEG,103),4,2) + SUBSTRING(CONVERT(VARCHAR,NE.DTCHEG,103) ,1,2),
         NE.FILIAL,
         RIGHT('0000000000' + LTRIM(RTRIM( NE.CODFOR )),10), 
         RIGHT('0000000000' + LTRIM(RTRIM( NE.TPO )),10),
         PROD.COMPRADOR,
         SUBSTRING(PROD.CLASPROD ,1,3 ),
         CONVERT(DATETIME,convert(char,NE.DTCHEG,103),103) ,
         '1',
         SUM( ITNE.QUANT),
         SUM((ITNE.PRECOTOTAL+ITNE.VALORIPI)* (1 + (NE.DESPINCL+NE.VALFRETE)/(NE.VALCONTAB - (NE.DESPINCL+NE.VALFRETE + NE.VALSUBSTRI)))),
         SUM(PM.PRAZO),
         COUNT(ITNE.CODPRO),0,0
FROM   BDTESTE.DBO.NFENTRACAD NE,
       BDTESTE.DBO.ITNFENTCAD ITNE,  PRAZOMEDIO PM, 
       BDTESTE.DBO.PRODUTOCAD PROD
WHERE  NE.NUMORD = ITNE.NUMORD AND NE.ATUALIZ = 1 AND NE.TPO LIKE '1%' AND NE.EST IN ('2','3') AND
       ITNE.CODPRO = PROD.CODPRO AND NE.NUMORD = PM.NUMORD AND
       (NE.VALCONTAB - (NE.DESPINCL+NE.VALFRETE + NE.VALSUBSTRI)) > 0
GROUP BY RIGHT('00000' + LTRIM(RTRIM( ITNE.CODPRO )),5),
         SUBSTRING(CONVERT(VARCHAR,NE.DTCHEG,103) ,7,4) + SUBSTRING(CONVERT(VARCHAR,NE.DTCHEG,103),4,2) + SUBSTRING(CONVERT(VARCHAR,NE.DTCHEG,103) ,1,2),
         NE.FILIAL,
         RIGHT('0000000000' + LTRIM(RTRIM( NE.CODFOR )),10), 
         RIGHT('0000000000' + LTRIM(RTRIM( NE.TPO )),10),
         PROD.COMPRADOR,
         SUBSTRING(PROD.CLASPROD ,1,3 ) ,
         CONVERT(DATETIME,convert(char,NE.DTCHEG,103),103)
commit 

/*begin transaction


DROP VIEW PRAZOMEDIO
commit */


begin transaction

INSERT INTO nivel6_AC_CP SELECT NIVEL6, NIVEL1, NIVEL2, NIVEL3,NIVEL4,NIVEL5,DATA , SUM( ATR1 ),
                          SUM( ATR2 ), round(SUM( ATR3 )/count(*),0), SUM( ATR4 ), SUM( ATR5 ), SUM( ATR6 )
                      FROM nivel7_AC_CP
                      GROUP BY NIVEL6, NIVEL1, NIVEL2, NIVEL3, NIVEL4, NIVEL5,DATA ;
commit 

begin transaction

INSERT INTO nivel5_AC_CP SELECT NIVEL5, NIVEL1, NIVEL2, NIVEL3,NIVEL4,DATA , SUM( ATR1 ),
                          SUM( ATR2 ), round(SUM( ATR3 )/count(*),0), SUM( ATR4 ), SUM( ATR5 ), SUM( ATR6 )
                      FROM nivel6_AC_CP
                      GROUP BY NIVEL5, NIVEL1, NIVEL2, NIVEL3, NIVEL4, DATA ;
commit 

begin transaction


INSERT INTO nivel4_AC_CP SELECT NIVEL4, NIVEL1, NIVEL2, NIVEL3,DATA , SUM( ATR1 ),
                          SUM( ATR2 ), round(SUM( ATR3 )/count(*),0), SUM( ATR4 ), SUM( ATR5 ), SUM( ATR6 )
                      FROM nivel5_AC_CP
                      GROUP BY NIVEL4, NIVEL1, NIVEL2, NIVEL3, DATA ;
commit 

begin transaction


INSERT INTO nivel3_AC_CP SELECT NIVEL3, NIVEL1, NIVEL2, DATA , SUM( ATR1 ),
                          SUM( ATR2 ), round(SUM( ATR3 )/count(*),0), SUM( ATR4 ), SUM( ATR5 ), SUM( ATR6 )
                      FROM nivel4_AC_CP
                      GROUP BY NIVEL3, NIVEL1, NIVEL2, DATA ;
commit 

begin transaction


INSERT INTO nivel2_AC_CP SELECT NIVEL2, NIVEL1, DATA , SUM( ATR1 ),
                          SUM( ATR2 ), round(SUM( ATR3 )/count(*),0), SUM( ATR4 ), SUM( ATR5 ), SUM( ATR6 )
                      FROM nivel3_AC_CP
                      GROUP BY NIVEL2, NIVEL1, DATA ;
commit 

begin transaction


INSERT INTO nivel1_AC_CP SELECT NIVEL1, DATA , SUM( ATR1 ),
                          SUM( ATR2 ), round(SUM( ATR3 )/count(*),0), SUM( ATR4 ), SUM( ATR5 ), SUM( ATR6 )
                      FROM nivel2_AC_CP
                      GROUP BY NIVEL1, DATA ;
commit 

begin transaction

INSERT INTO tab_nivel1_AC_CP (NIVEL1, DESCRICAO) SELECT DISTINCT NIVEL1, 
                          SUBSTRING(NIVEL1,7,2)+'/'+SUBSTRING(NIVEL1,5,2)+'/'+
                          SUBSTRING(NIVEL1,1,4)
                          FROM nivel1_AC_CP
                          WHERE NOT EXISTS (SELECT NIVEL1 FROM TAB_NIVEL1_AC_CP WHERE
                          TAB_NIVEL1_AC_CP.NIVEL1=NIVEL1_AC_CP.NIVEL1)
commit 

begin transaction


INSERT INTO tab_nivel2_AC_CP (NIVEL2, DESCRICAO) SELECT DISTINCT NIVEL2, 'FILIAL ' + NIVEL2
                          FROM nivel2_AC_CP
                          WHERE NOT EXISTS (SELECT NIVEL2 FROM TAB_NIVEL2_AC_CP WHERE
                          TAB_NIVEL2_AC_CP.NIVEL2=NIVEL2_AC_CP.NIVEL2)
commit 

begin transaction


INSERT INTO tab_nivel3_AC_CP (NIVEL3, DESCRICAO) SELECT DISTINCT NIVEL3, 'FORNECEDOR' + NIVEL3
                          FROM nivel3_AC_CP
                          WHERE NOT EXISTS (SELECT NIVEL3 FROM TAB_NIVEL3_AC_CP WHERE
                          TAB_NIVEL3_AC_CP.NIVEL3=NIVEL3_AC_CP.NIVEL3)
commit 

begin transaction


INSERT INTO tab_nivel4_AC_CP (NIVEL4, DESCRICAO) SELECT DISTINCT NIVEL4, 'TPO ' + NIVEL4
                          FROM nivel4_AC_CP
                          WHERE NOT EXISTS (SELECT NIVEL4 FROM TAB_NIVEL4_AC_CP WHERE
                          TAB_NIVEL4_AC_CP.NIVEL4=NIVEL4_AC_CP.NIVEL4)
commit 

begin transaction


INSERT INTO tab_nivel5_AC_CP (NIVEL5, DESCRICAO) SELECT DISTINCT NIVEL5, 'COMPRADOR ' + NIVEL5
                          FROM nivel5_AC_CP
                          WHERE NOT EXISTS (SELECT NIVEL5 FROM TAB_NIVEL5_AC_CP WHERE
                          TAB_NIVEL5_AC_CP.NIVEL5 = NIVEL5_AC_CP.NIVEL5)
commit 

begin transaction


INSERT INTO tab_nivel6_AC_CP (NIVEL6, DESCRICAO) SELECT DISTINCT NIVEL6, 'GRUPO ' + NIVEL6
                          FROM nivel6_AC_CP
                          WHERE NOT EXISTS (SELECT NIVEL6 FROM TAB_NIVEL6_AC_CP WHERE
                          TAB_NIVEL6_AC_CP.NIVEL6=NIVEL6_AC_CP.NIVEL6)
commit 

begin transaction


INSERT INTO tab_nivel7_AC_CP (NIVEL7, DESCRICAO) SELECT DISTINCT NIVEL7, 'PRODUTO ' + NIVEL7
                          FROM nivel7_AC_CP
                          WHERE NOT EXISTS (SELECT NIVEL7 FROM TAB_NIVEL7_AC_CP WHERE
                          TAB_NIVEL7_AC_CP.NIVEL7=NIVEL7_AC_CP.NIVEL7)
commit 


begin transaction


UPDATE TAB_NIVEL1_AC_CP SET MES      = SUBSTRING ( NIVEL1,5,2) + '/' + LEFT(NIVEL1,4),
		      BIMESTRE = CASE SUBSTRING (NIVEL1, 5,2) 
                      WHEN '01' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '02' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '03' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '04' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '05' THEN '03' +  '/' + LEFT(NIVEL1,4)
                      WHEN '06' THEN '03' +  '/' + LEFT(NIVEL1,4)
                      WHEN '07' THEN '04' +  '/' + LEFT(NIVEL1,4)
                      WHEN '08' THEN '04' +  '/' + LEFT(NIVEL1,4)
                      WHEN '09' THEN '05' +  '/' + LEFT(NIVEL1,4)
                      WHEN '10' THEN '05' +  '/' + LEFT(NIVEL1,4)
                      WHEN '11' THEN '06' +  '/' + LEFT(NIVEL1,4)
                      WHEN '12' THEN '06' +  '/' + LEFT(NIVEL1,4)
			END,
		      TRIMESTRE = CASE SUBSTRING (NIVEL1, 5,2) 
                      WHEN '01' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '02' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '03' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '04' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '05' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '06' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '07' THEN '03' +  '/' + LEFT(NIVEL1,4)
                      WHEN '08' THEN '03' +  '/' + LEFT(NIVEL1,4)
                      WHEN '09' THEN '03' +  '/' + LEFT(NIVEL1,4)
                      WHEN '10' THEN '04' +  '/' + LEFT(NIVEL1,4)
                      WHEN '11' THEN '04' +  '/' + LEFT(NIVEL1,4)
                      WHEN '12' THEN '04' +  '/' + LEFT(NIVEL1,4)
			END,
		      QUADRIMESTRE  = CASE SUBSTRING (NIVEL1, 5,2) 
                      WHEN '01' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '02' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '03' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '04' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '05' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '06' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '07' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '08' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '09' THEN '03' +  '/' + LEFT(NIVEL1,4)
                      WHEN '10' THEN '03' +  '/' + LEFT(NIVEL1,4)
                      WHEN '11' THEN '03' +  '/' + LEFT(NIVEL1,4)
                      WHEN '12' THEN '03' +  '/' + LEFT(NIVEL1,4)
			END,
		      SEMESTRE  = CASE SUBSTRING (NIVEL1, 5,2) 
                      WHEN '01' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '02' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '03' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '04' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '05' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '06' THEN '01' +  '/' + LEFT(NIVEL1,4)
                      WHEN '07' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '08' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '09' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '10' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '11' THEN '02' +  '/' + LEFT(NIVEL1,4)
                      WHEN '12' THEN '02' +  '/' + LEFT(NIVEL1,4)
			END,
			ANO = LEFT(NIVEL1,4),
		      DIAUTIL  = CASE DATEPART(DW,CONVERT(DATETIME,NIVEL1)) 
                      WHEN '1' THEN 'N'
                      WHEN '2' THEN 'S' 
                      WHEN '3' THEN 'S' 
                      WHEN '4' THEN 'S' 
                      WHEN '5' THEN 'S' 
                      WHEN '6' THEN 'S' 
                      WHEN '7' THEN 'N' 
			END
commit 

begin transaction


UPDATE TAB_NIVEL2_AC_CP SET DESCRICAO =  LEFT(upper(F.NOME),100)
           FROM TAB_NIVEL2_AC_CP, BDTESTE.DBO.FILIALCAD F
           WHERE CAST(NIVEL2 AS INT) = CAST(F.FILIAL AS INT)
commit 

begin transaction


UPDATE TAB_NIVEL3_AC_CP SET DESCRICAO =  LEFT(upper(F.NOME),100)
           FROM TAB_NIVEL3_AC_CP, BDTESTE.DBO.ITEM F
           WHERE CAST(NIVEL3 AS INT) = F.OID
commit 

begin transaction

UPDATE TAB_NIVEL4_AC_CP SET DESCRICAO = LEFT(upper(T.NOME),100)
           FROM TAB_NIVEL4_AC_CP, BDTESTE.DBO.TPO_R T
           WHERE NIVEL4  = RIGHT('0000000000' + LTRIM(RTRIM(T.HIERARQUIANUMERO )),10)

commit 

begin transaction

UPDATE TAB_NIVEL5_AC_CP SET DESCRICAO = LEFT(upper(NOME),100)
           FROM TAB_NIVEL5_AC_CP, BDTESTE.DBO.COMPRADCAD
           WHERE CAST(NIVEL5 AS INT) = CAST(CODCOMP AS INT)
commit 

begin transaction

UPDATE TAB_NIVEL6_AC_CP SET DESCRICAO = LEFT(upper(DESCR),100)
           FROM TAB_NIVEL6_AC_CP, BDTESTE.DBO.CLASSIFCAD
           WHERE CAST(NIVEL6 AS INT) = CAST(LEFT ( CLASPROD ,3) AS INT)
	     AND LEN(CLASPROD)= 3
commit 


begin transaction

UPDATE TAB_NIVEL7_AC_CP SET DESCRICAO = upper(DESCR),
                          FORN_PROD = RIGHT('0000000000' + LTRIM(RTRIM( P.CODFOR )),10),
                          MODELO = substring(P.MODELO,1,50)
           FROM TAB_NIVEL7_AC_CP, BDTESTE.DBO.PRODUTOCAD P
           WHERE CAST(NIVEL7 AS INT) = CAST(P.CODPRO AS INT)
commit 

begin transaction

UPDATE TAB_NIVEL7_AC_CP SET FORN_PROD = substring(upper(NOME),1,50)
           FROM TAB_NIVEL7_AC_CP, BDTESTE.DBO.ITEM
           WHERE FORN_PROD = RIGHT('0000000000' + LTRIM(RTRIM( OID )),10)
commit 

begin transaction



UPDATE TAB_PARAMETROS_AC_CP SET ATUALIZADOEM = GETDATE()
commit 

End

