set echo ON;
spool /datos/p06b/log/salida_20170707.log;

UPDATE P06B24T00_ORGANOSDIREXP C
SET C.P06B24_TIPO = C.P06B24_TIPO + 10
WHERE (C.P06B24_NUMEXP, C.P06B24_IDENTIFICADOR, C.P06B24_IDCARGO, C.P06B24_TIPO) IN 
(
SELECT P06B24_NUMEXP, P06B24_IDENTIFICADOR, P06B24_IDCARGO, P06B24_TIPO FROM P06B24T00_ORGANOSDIREXP B,
(SELECT P06B24_NUMEXP AS NUMEXP, P06B24_IDENTIFICADOR AS IDEN, P06B24_IDCARGO AS CARGO, MIN(A.P06B24_TIPO) AS MINIMO
FROM P06B24T00_ORGANOSDIREXP A
WHERE A.P06B24_TIPO IN (1,2,3)
GROUP BY A.P06B24_NUMEXP, A.P06B24_IDENTIFICADOR, A.P06B24_IDCARGO
HAVING COUNT(*) > 1
ORDER BY 1)
WHERE B.P06B24_NUMEXP = NUMEXP AND
      B.P06B24_IDENTIFICADOR = IDEN AND
      B.P06B24_IDCARGO = CARGO AND
      B.P06B24_TIPO > MINIMO AND
      B.P06B24_TIPO IN (1,2,3)
);

UPDATE P06B24T00_ORGANOSDIREXP
SET P06B24_TIPO = 1
WHERE P06B24_TIPO IN (2,3);

ALTER TABLE P06B24T00_ORGANOSDIREXP DROP (
P06B24_DENCARGO,
P06B24_IDREP,
P06B24_NOMREP,
P06B24_TIPO1,
P06B24_RAZSOC);

ALTER TABLE P06B74T00_ORGANOSDIREXP DROP (
	P06B74_OD_TIPO1,
	P06B74_OD_IDREP,
	P06B74_OD_NOMREP
);

COMMIT;

exit;
