Some queries to help the DevOp

-- Commencer par récupérer le NUDOSS
select * from DEVHR.ZY0J where NUDOSS in (select NUDOSS from DEVHR.ZY00 where MATCLE = '&Matricule')

-- Mettre à jour
update DEVHR.ZY0J
set VALIDE = ' ' where NUDOSS = '&Nudoss'

SELECT a.gptrai, a.cdtrai, b.lilong, a.cdcotx, a.cdsftr,
regexp_substr(a.zooprd,'("|'')[0-9A-Z][0-9A-Z][0-9A-Z]("|'')') as "Rubriques déclenchées en paie"
FROM tr3p a, tr21 b, ap40 c
WHERE a.gptrai = b.gptrai
AND a.cdtrai = b.cdtrai
AND a.gptrai = c.gptrai
AND a.cdtrai = c.cdtrai
AND c.cdpros = 'JDCAL'
AND regexp_like(a.zooprd,'("|'')&CodeRubrique("|'') +RAPCODRUB(X)?','i')
order by a.gptrai, a.cdtrai, a.cdcotx, a.cdsftr ;

SELECT DISTINCT(a.gptrai||a.cdtrai), a.lilong
FROM tr21 a, tr3p b
WHERE
a.gptrai = b.gptrai
AND a.cdtrai = b.cdtrai
AND b.zooprd LIKE '%&p_motif%'
ORDER BY a.gptrai||a.cdtrai ;

update tp13 set teverr ='0' 
where IDPOPL in (select IDPOPL
from TP33 where nudoss = (select nudoss from ZY00 where matcle ='&Matricule') and populs like '%') and teverr='1' ;

select ZONXML from PRDHR.EN30
where TYENTI = 'QL' and CDENTI = 'XAD2C81' and CDROLE = 'LAYOUT'
order by NOLIGN

select a.cdstdo, a.cdinfo, b.lilong, a.tyinfo "Info Type", a.tehist "Info Historique"
from di40 a, di41 b
where a.cdstdo = b.cdstdo
and a.cdinfo = b.cdinfo
and b.cdlang = 'F'
order by a.cdstdo, a.cdinfo ;

select a.tyenti, a.cdenti, b.lilong,
case
    when a.cdutmo = 'Matricule_1' then 'Prenom_1'
    when a.cdutmo = 'Matricule_2' then 'Prenom_2'
    else a.cdutmo 
end "Utilisateur",
a.timodi
from en10 a, lb12 b
where a.tyenti = b.tyenti and a.cdenti = b.cdenti
and a.tyenti = 'QR' and a.cdutmo in ('Matricule_1', 'Matricule_2')
order by a.cdenti ;

--REQUETE 4 : PAGES - SOUS PAGES - INFORMATIONS - RUBRIQUE - CARTOUCHE
select * from (
(SELECT DISTINCT 
      --ARBRE
      A1.CDENTI ARBRE_FONCT,A4.LILONG "Libellé de l'arbre"
      --NOEUD
      ,A1.CDENRF NOEUD_NAVIG,A5.LILONG "Libellé du noeud"
      --PAGES WEB et SOUS-PAGES
      ,AA.CDENTI PAGE_WEB,F.LILONG LIBELLE_PAGE,AA.CDENRF SOUS_PAGES,G.LILONG LIBELLE_SOUSPAGE
      --INFORMATIONS
      , A.CDENRF INFORMATION, B.LILONG "Libéllé de l'information"
      --RUBRIQUES
      , A.CDCPNT RUBRIQUES_PAGE, C.LILONG "Libellé de la rubrique"
      --,W.
      ,W.LGRUBE "Longueur Rubrique", W.TYFORM "Type de la rubrique",W.NBDECI "Nombre décimale", E.TYPRES "TYPE DE PRESENCE"
      , E.VACONT "Valeur rubrique", E.CDSCCO "Controle réglementaire"
FROM 
      RC50 AA,RC56 A, DI41 B, DI61 C, DI80 E, WF21 F, RC50 A1, RC50 A2, RC50 A3 , WT11 A4 , WT21 A5, WF21 A6,WF21 G , DI60 W
WHERE 
      B.CDSTDO||B.CDINFO=A.CDENRF AND SUBSTR(A.CDCPNT,1,6)=C.CDRUBR AND C.CDSTDO||C.CDINFO=A.CDENRF
      AND C.CDSTDO=E.CDSTDO(+) AND C.CDINFO=E.CDINFO(+) AND C.CDRUBR=E.CDRUBR(+)
      AND AA.CDENRF=A.CDENTI
      AND AA.CDENTI=F.GPECRA||F.CDECRA
      AND A.CDENTI=G.GPECRA||G.CDECRA
      --dosiier individuel
      AND AA.CDENTI='JAW00703'
      --Affectation
      --AND AA.CDENTI='JAW00024'
      AND A.TYENRF='IN'AND A.CDENRF LIKE 'ZY%'
      AND B.CDLANG ='F' AND C.CDLANG='F' AND F.CDLANG='F'  AND G.CDLANG='F'
      AND A1.TYENRF = A2.TYENTI AND A2.TYENTI=A3.TYENRF
      AND A1.CDENRF = A2.CDENTI AND A2.CDENTI=A3.CDENRF
      AND A1.CDENTI = A4.CDTREE AND A4.CDLANG='F'
      AND A2.CDENTI = A5.CDTREE||A5.CDNODE AND A5.CDLANG='F'
      AND A2.CDENRF = A6.GPECRA||A6.CDECRA AND A6.CDLANG='F'
      AND A1.TYENTI='AN' AND A2.TYENTI='NN' AND A2.TYENRF='WF' 
      --AND A1.CDENTI='AU2'
      --AND SUBSTR(A1.CDENRF,4,5)='1100J'
      --AND SUBSTR(A1.CDENRF,4,5)='2400J'
      AND AA.CDENTI=A2.CDENRF
      AND C.CDSTDO=W.CDSTDO AND C.CDINFO=W.CDINFO AND C.CDRUBR=W.CDRUBR)

UNION
(
SELECT DISTINCT 
      A1.CDENTI ARBRE_FONCT, A4.LILONG, A1.CDENRF NOEUD_NAVIG,A5.LILONG
      ,A.CDENTI PAGE_WEB,F.LILONG LIBELLE_PAGE, ' ' aa,' ' bb ,A.CDENRF INFORMATION, B.LILONG LIBELLE_INFO, A.CDCPNT RUBRIQUES_PAGE, C.LILONG
      ,W.LGRUBE "Longueur Rubrique", W.TYFORM "Type de la rubrique",W.NBDECI "Nombre décimale", E.TYPRES "TYPE DE PRESENCE"
      , E.VACONT "Valeur rubrique", E.CDSCCO "Controle réglementaire"
      
FROM RC56 A, DI41 B, DI61 C, DI80 E, WF21 F, RC50 A1, RC50 A2, RC50 A3 , WT11 A4 , WT21 A5, WF21 A6 , DI60 W
WHERE 
      B.CDSTDO||B.CDINFO=A.CDENRF AND SUBSTR(A.CDCPNT,1,6)=C.CDRUBR AND C.CDSTDO||C.CDINFO=A.CDENRF
      AND C.CDSTDO=E.CDSTDO(+) AND C.CDINFO=E.CDINFO(+) AND C.CDRUBR=E.CDRUBR(+)
      AND A.CDENTI=F.GPECRA||F.CDECRA
      --dosiier individuel
      AND A.CDENTI='JAW00703' 
      --Affectation
      --AND A.CDENTI='JAW00024'
      AND A.TYENRF='IN'AND A.CDENRF LIKE 'ZY%'
      AND B.CDLANG ='F' AND C.CDLANG='F' AND F.CDLANG='F' 
      AND A1.TYENRF = A2.TYENTI AND A2.TYENTI=A3.TYENRF
      AND A1.CDENRF = A2.CDENTI AND A2.CDENTI=A3.CDENRF
      AND A1.CDENTI = A4.CDTREE AND A4.CDLANG='F'
      AND A2.CDENTI = A5.CDTREE||A5.CDNODE AND A5.CDLANG='F'
      AND A2.CDENRF = A6.GPECRA||A6.CDECRA AND A6.CDLANG='F'
      AND A1.TYENTI='AN' AND A2.TYENTI='NN' AND A2.TYENRF='WF' 
      --AND A1.CDENTI='AU2' 
      AND A.CDENTI=A2.CDENRF
      AND C.CDSTDO=W.CDSTDO AND C.CDINFO=W.CDINFO AND C.CDRUBR=W.CDRUBR)
      )
      order by ARBRE_FONCT,NOEUD_NAVIG,PAGE_WEB,SOUS_PAGES,INFORMATION
;

set head on
set tab off
set feed off
set line 600
set pagesize 25000
set colsep '|'
spool dico_extrait.txt

select a.cdstdo as "CDSTDO",
a.cdinfo as "CDINFO",
a.cdrubr as "CDRUBR",
b.lilong as "LILONG",
a.nuordr as "NUORDR",
a.tyform as "TYFORM",
a.cddevi as "CDDEVI",
a.lgrube as "LGRUBE",
a.nbdeci as "NBDECI",
a.nunivr as "NUNIVR",
a.terdef as "TERDEF",
a.tyrole as "TYROLE",
a.tyrubr as "TYRUBR"
from di60 a, di61 b
where a.cdstdo = b.cdstdo
and a.cdinfo = b.cdinfo
and a.cdrubr = b.cdrubr
and b.cdlang = 'F'
and a.cdstdo in ('ZC','ZD','ZE','ZY')
order by a.cdstdo, a.cdinfo, a.nuordr
/

select a.cdstdo, a.vacdst , b.lilong
from di30 a, di31 b
where a.cdstdo = b.cdstdo
and a.vacdst = b.vacdst
and b.cdlang = 'F' 
order by a.cdstdo, a.vacdst ;

select a.gptrai, a.cdtrai, b.lilong, a.cdcotx, a.cdsftr, a.zooprd, regexp_substr(a.zooprd,'("|'')[0-9A-Z][0-9A-Z][0-9A-Z]("|'')') as "EV générés"
from tr3p a, tr21 b, ap40 c,
(select za.gptrai, za.cdtrai from tr3p za
where za.cdoptr = 'AMJ' and za.zooprd like '%ZY90%'
group by za.gptrai, za.cdtrai) d
where a.gptrai = b.gptrai
and a.cdtrai = b.cdtrai 
and a.gptrai = c.gptrai
and a.cdtrai = c.cdtrai 
and a.gptrai = d.gptrai
and a.cdtrai = d.cdtrai 
and regexp_like(a.zooprd, '("|'')[0-9A-Z][0-9A-Z][0-9A-Z]("|'') +[0-9A-Z-]*RUBRIQ')
order by a.gptrai, a.cdtrai, a.cdcotx, a.cdsftr
;

select a.CDSTDO, a.CDINFO, a.CDRUBR, b.LILONG, a.NUORDR, a.TYFORM, a.CDDEVI, a.LGRUBE, a.NBDECI, a.NUNIVR, a.TERDEF, a.TYROLE, a.TYRUBR, ' '
from  HR.DI60 a
left outer join HR.DI61 b
on a.CDSTDO = b.CDSTDO and a.CDINFO = b.CDINFO and a.CDRUBR = b.CDRUBR
and b.CDLANG = 'F'
where a.CDSTDO in ('ZY','ZD', 'ZE', 'ZC')
order by a.CDSTDO, a.CDINFO, a.NUORDR

select   a.cdstdo, a.cdinfo, a.cdrubr, nvl(b.lilong,'** libellé français absent **'), a.nuordr, a.tyform, a.cddevi, a.lgrube, a.nbdeci, a.nunivr, a.terdef, a.tyrole, a.tyrubr, ' '
from di60 a, di61 b
where a.cdstdo = b.cdstdo (+) and a.cdinfo = b.cdinfo (+) and a.cdrubr = b.cdrubr (+)
and b.cdlang (+) = 'F' and a.cdstdo in ('ZY','ZD', 'ZE', 'ZC')
order by a.cdstdo, a.cdinfo, a.nuordr;

SET VERIFY OFF
ACCEPT p_motif PROMPT 'Entrez le motif pour la zone ZOCOND: ' 
ACCEPT p_processus PROMPT 'Entrez le code du processus auquel doivent appartenir les traitements: '

SELECT a.* 
FROM tr3p a, ap40 b
WHERE a.gptrai = b.gptrai
AND a.cdtrai = b.cdtrai
AND a.zocond LIKE '%&p_motif%'
AND b.cdpros = '&p_processus' ;

-- Liste des jobs lancés dans la dernière heure  (retranchement de 1/24 à sysdate)
select * from zo00 where tisoum >= (sysdate - 1/24) ;

-- Liste des jobs sans date de fin, sur les dernières 24h
select a.nudoss, a.cdphas, a.idrequ, a.tisoum, a.lisoum, a.userid,
to_char(b.timdeb, 'DD/MM/YYYY HH24:MI') as "Début",
to_char(b.timfin, 'DD/MM/YYYY HH24:MI') as "Fin",
b.cdret
from zo00 a, zo2c b
where a.nudoss = b.nudoss
and a.tisoum >= (sysdate - 1)
and b.timfin = '01/01/0001'
and userid <> ' ' ;

-- Tentative pour faire la jonction avec le nom et le prénom du dossier
select a.cdphas, a.idrequ, a.tisoum, a.lisoum, a.userid, trim(c.prenom), trim(c.nomuse) from zo00 a,
(select a.prenom, a.nomuse, 'S01' || substr(trim(b.userid),2) || '.' || b.socdos as userid from zy00 a, zyy2 b where a.nudoss = b.nudoss) c
where a.tisoum >= (sysdate - 1/24)
and trim(a.userid) = trim(c.userid) ;

select a.nudoss, a.cdphas, a.idrequ, a.tisoum, a.lisoum, a.userid,
to_char(b.timdeb, 'DD/MM/YYYY HH24:MI') as "Début",
to_char(b.timfin, 'DD/MM/YYYY HH24:MI') as "Fin",
b.cdret
from zo00 a, zo2c b
where a.nudoss = b.nudoss
and a.tisoum >= (sysdate - 2/24) ;

select CDSTDO, CDINFO, TYATCH, TEUTIA, TEUTIS, TYETAL, NBOCCR, TEUTIM, TEANNU
from PRDHR.AP30 where CDPROS = 'YIYZD'
order by CDSTDO, CDINFO, TYATCH

select a.gptrai, a.cdtrai, b.lilong, a.cdcotx, a.cdsftr, a.zooprd, regexp_substr(a.zooprd,'("|'')[0-9A-Z][0-9A-Z][0-9A-Z]("|'')') as "EV générés"
from tr3p a, tr21 b, ap40 c,
(select za.gptrai, za.cdtrai from tr3p za
where za.cdoptr = 'AMJ' and za.zooprd like '%ZY90%'
group by za.gptrai, za.cdtrai) d
where a.gptrai = b.gptrai
and a.cdtrai = b.cdtrai 
and a.gptrai = c.gptrai
and a.cdtrai = c.cdtrai
and a.gptrai = d.gptrai
and a.cdtrai = d.cdtrai
and regexp_like(a.zooprd, '("|'')&CodeRubrique("|'') +[0-9A-Z-]*RUBRIQ')
order by a.gptrai, a.cdtrai, a.cdcotx, a.cdsftr
;

select * from tp33 where teverr='1' order by idpopl;
select * from tp13 where teverr='1';
 
delete tp33;
delete pg20  where cdpros='FDXWB';
update tp13 set teverr='0' where teverr='1';

select TYENTI,
case TYENTI 
when 'AM' then 'Arbre de publication'
when 'AN' then 'Arbre fonctionnel'
when 'AP' then 'Groupe de processus'
when 'AR' then 'HRa Scope'
when 'AS' then 'Plan de site'
when 'AT' then 'Service'
when 'BP' then 'Processus Guidé'
when 'CA' then 'Code action contextuel'
when 'CC' then 'Condition cataloguée'
when 'CF' then 'Code action fonctionnelle'
when 'CX' then 'Action fonctionnelle'
when 'DN' then 'Fichier de données'
when 'DO' then 'Document édit.'
when 'ET' then 'Etat'
when 'EX' then 'Exploration'
when 'E1' then 'Elément graphique'
when 'E2' then 'Document de fusion'
when 'E3' then 'Ressource Web'
when 'FM' then 'Fichier micro'
when 'FO' then 'Formulaire'
when 'GB' then 'Groupe de Processus Guidés'
when 'GF' then 'Groupe de formulaires'
when 'GK' then 'Groupe de collections'
when 'GM' then 'Groupe de modélisation'
when 'GT' then 'Groupe de traitements'
when 'GX' then 'Groupe d explorations'
when 'HB' then 'Habilitation (activité)'
when 'IN' then 'Information'
when 'IW' then 'Information WareHouse'
when 'IO' then 'Table ODS'
when 'KL' then 'Collection d objets'
when 'LI' then 'Lien'
when 'MI' then 'Modélisation'
when 'NA' then 'Assistant de navigation'
when 'NN' then 'Noeud de navigation'
when 'NS' then 'Etape de navigation'
when 'PH' then 'Plate-forme physique'
when 'PM' then 'Paramètre'
when 'PJ' then 'Projet'
when 'PL' then 'Plate-forme logique'
when 'PS' then 'Processus'
when 'QD' then 'Data modèle'
when 'QG' then 'Groupe de requêtes'
when 'QL' then 'Mise en page spécialisée'
when 'QP' then 'Population'
when 'QR' then 'Rapport'
when 'RO' then 'Rôle'
when 'SD' then 'Structure de données'
when 'SG' then 'Script de plug-in'
when 'SI' then 'Topologie système'
when 'SO' then 'Groupe ODS'
when 'SP' then 'Sous-projet'
when 'ST' then 'Type de dossier'
when 'SW' then 'Structure de données HR WareHouse'
when 'SY' then 'Macro système'
when 'TC' then 'Textes communs'
when 'TR' then 'Traitement'
when 'UC' then 'Utialisateur/Concepteur'
when 'WF' then 'Page Web'
when 'WG' then 'Groupe de pages Web'
when 'WK' then 'Workflow'
when 'WS' then 'Charte graphique Web'
when 'ZC' then 'Template HTML'
else 'Type inconnu'
end,
CDENTI, TICATL from 
PRDHR.KL50 where GPCOLL = '&GroupeCollection' and CDCOLL = '&Collection'
and TYENTI <> 'KL'
order by NULINE

select 
CDPLPH, 
CDCPAY, VACPAY from QUAHR.PP50 ;