DaveWagoner
VIP Member
WOW that is good stuff you came up with. I'm going to have to come back to it when I have a few mins and some brain cells remaining
CREATE OR REPLACE VARIABLE <ACTUALLIB>.V_LANGCODE VARCHAR(1) DEFAULT '';
SELECT DATE(CHAR(F.ZZUPMJ+1900000)) AS PROCESS_DATE,
SUBSTR(LPAD(F.ZZUPMT,6,'0'),1,2) ||':'||SUBSTR(LPAD(F.ZZUPMT,6,'0'),3,2) ||':'||RIGHT(F.ZZUPMT,2) AS PROCESS_TIME,
SUBSTR(F.ZZTSV,1,INSTR(F.ZZTSV,'|')-1) AS JOB_NAME,
SUBSTR(F.ZZTSV,INSTR(F.ZZTSV,'|')+1,INSTR(F.ZZTSV,'|',INSTR(F.ZZTSV,'|')+1)-INSTR(F.ZZTSV,'|')-1) AS JOB_VERSION,
S.FRDSCA AS SUBJECT,
A.ABALPH AS USER,
D.GDTXFT AS ERROR_DETAILS
FROM F01131M AS M
LEFT JOIN F01131 AS F ON M.ZMPPSRK = F.ZZSERK
LEFT JOIN <YOURLIB>.F00165 AS D ON D.GDOBNM='GT92002' AND D.GDTXKY= TRIM(M.ZMTMPI)||'|'||<ACTUALLIB>.V_LANGCODE||'||'
LEFT JOIN <YOURLIB>.F9203 AS S ON S.FRDTAI = TRIM(M.ZMTMPI) AND S.FRLNGP = <ACTUALLIB>.V_LANGCODE
LEFT JOIN <SYSLIB>.F0092 AS U ON U.ULUSER = F.ZZUSER
LEFT JOIN F0101 AS A ON A.ABAN8 = U.ULAN8
WHERE M.ZMPPSRK IN (SELECT MAX(ZMPSRK) FROM F01131M WHERE ZMTSV='<YOUR_EDOC_GOES_HERE>');
DROP VARIABLE <ACTUALLIB>.V_LANGCODE;
PROCESS_DATE | PROCESS_TIME | JOB_NAME | JOB_VERSION | SUBJECT | USER | ERROR_DETAILS |
---|---|---|---|---|---|---|
2023-01-03 | 09:57:37 | R47011 | ABC47011 | Order &1 Contains Errors | John Doe | Cause . . . . . Order &1 contains errors. Resolution. . Review the detail errors for Order &1 . |
2023-01-03 | 09:57:37 | R47011 | ABC47011 | ERROR - Customer Inhibited from Batch | John Doe | Cause . . . . . The customer is inhibited from batch processing. Resolution. . . Go to Billing Instructions and change the Batch Processing Mode for this customer, or use a different customer. |
It is fine when you know the job number. I had to search by the EDOC number.Hi I usually find intel with this SQL on oracle :
select B.JCUSER as "Utilisateur",A.JCPID as "Programme",A.JCVERS as "Version",A.JCSTDTIM as "Heure_Debut",A.JCETDTIM as "Heure_Fin",B.JCJOBQUE as "File",B.JCJOBSTS as "Statut",B.JCJOBNBR as "Numero_Job",C.ZZSERK as "Index_Maitre",C.ZZMSGP as "Message_Maitre",D.ZMPSRK as "Message_Pere",D.ZMSERK as "Message_Fils",D.ZMLV as "Niveau",D.ZMTSV as "Champ",D.ZMTMPI as "DD_Erreur",D.ZMMSGP as "Texte_message_1",UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(GDTXFT)) as "Texte_message_2",D.ZMEAP as "Ecran_Erreur",D.ZMFMNM as "SS_ECRAN_ERREUR",D.ZMFCTNM as "BSFN_Erreur",D.ZMLNBR as "Ligne_BSFN"
from svm920.f986110 B,svm920.f986114 A,proddta.f01131 C,proddta.f01131m D,dd920.F00165
where B.JCJOBNBR=3031114
and A.JCJOBNBR=B.JCJOBNBR
and concat(trim(A.jcpid),concat('|',concat(trim(A.jcvers),'|0|')))= C.ZZTSV and C.ZZUSER=B.JCUSER AND C.ZZUPMJ=B.JCSBMDATE and C.ZZUPMT>=B.JCSBMTIME and C.ZZUPMT<=B.JCACTTIME
and D.ZMPPSRK=C.ZZSERK
and gdobnm='GT92002' and GDTXKY =trim(D.zmtmpi)||'|F||' --change your desired language for translations
order by C.ZZSERK,D.ZMSERK,D.ZMLV;
WITH MSG(ZMPSRK,ZMSERK) AS (SELECT ZMPSRK,ZMSERK FROM F01131M WHERE F01131M.ZMPPSRK=(SELECT MAX(ZMPSRK) FROM F01131M WHERE ZMTSV='<YOUR_EDOC>') AND F01131M.ZMTSV='<YOUR_EDOC>'
UNION ALL
SELECT M.ZMPSRK,M.ZMSERK FROM MSG,F01131M AS M WHERE M.ZMPSRK = MSG.ZMSERK)
SELECT ZMLV AS INDENT,
DATE(CHAR(F.ZZUPMJ+1900000)) AS PROCESS_DATE,
SUBSTR(LPAD(F.ZZUPMT,6,'0'),1,2) ||':'||SUBSTR(LPAD(F.ZZUPMT,6,'0'),3,2) ||':'||RIGHT(F.ZZUPMT,2) AS PROCESS_TIME,
SUBSTR(F.ZZTSV,1,INSTR(F.ZZTSV,'|')-1) AS JOB_NAME,
SUBSTR(F.ZZTSV,INSTR(F.ZZTSV,'|')+1,INSTR(F.ZZTSV,'|',INSTR(F.ZZTSV,'|')+1)-INSTR(F.ZZTSV,'|')-1) AS JOB_VERSION,
S.FRDSCA AS SUBJECT,
A.ABALPH AS USERNAME,
D.GDTXFT AS ERROR_DETAILS,
M.ZMTSV AS VARIABLES
FROM F01131M AS M
LEFT JOIN F01131 AS F ON M.ZMPPSRK = F.ZZSERK
LEFT JOIN <YOURLIB>.F00165 AS D ON D.GDOBNM='GT92002' AND D.GDTXKY= TRIM(M.ZMTMPI)||'|||'
LEFT JOIN <YOURLIB>.F9203 AS S ON S.FRDTAI = TRIM(M.ZMTMPI) AND S.FRLNGP = ''
LEFT JOIN <SYSLIB>.F0092 AS U ON U.ULUSER = F.ZZUSER
LEFT JOIN F0101 AS A ON A.ABAN8 = U.ULAN8
WHERE M.ZMSERK IN (SELECT ZMSERK FROM MSG)
ORDER BY ZMSERK
Thanks so much! I formatted it using poor man's t-sql for my own sakeHi I usually find intel with this SQL on oracle :
select B.JCUSER as "Utilisateur",A.JCPID as "Programme",A.JCVERS as "Version",A.JCSTDTIM as "Heure_Debut",A.JCETDTIM as "Heure_Fin",B.JCJOBQUE as "File",B.JCJOBSTS as "Statut",B.JCJOBNBR as "Numero_Job",C.ZZSERK as "Index_Maitre",C.ZZMSGP as "Message_Maitre",D.ZMPSRK as "Message_Pere",D.ZMSERK as "Message_Fils",D.ZMLV as "Niveau",D.ZMTSV as "Champ",D.ZMTMPI as "DD_Erreur",D.ZMMSGP as "Texte_message_1",UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(GDTXFT)) as "Texte_message_2",D.ZMEAP as "Ecran_Erreur",D.ZMFMNM as "SS_ECRAN_ERREUR",D.ZMFCTNM as "BSFN_Erreur",D.ZMLNBR as "Ligne_BSFN"
from svm920.f986110 B,svm920.f986114 A,proddta.f01131 C,proddta.f01131m D,dd920.F00165
where B.JCJOBNBR=3031114
and A.JCJOBNBR=B.JCJOBNBR
and concat(trim(A.jcpid),concat('|',concat(trim(A.jcvers),'|0|')))= C.ZZTSV and C.ZZUSER=B.JCUSER AND C.ZZUPMJ=B.JCSBMDATE and C.ZZUPMT>=B.JCSBMTIME and C.ZZUPMT<=B.JCACTTIME
and D.ZMPPSRK=C.ZZSERK
and gdobnm='GT92002' and GDTXKY =trim(D.zmtmpi)||'|F||' --change your desired language for translations
order by C.ZZSERK,D.ZMSERK,D.ZMLV;
SELECT B.JCUSER AS "Utilisateur"
,A.JCPID AS "Programme"
,A.JCVERS AS "Version"
,A.JCSTDTIM AS "Heure_Debut"
,A.JCETDTIM AS "Heure_Fin"
,B.JCJOBQUE AS "File"
,B.JCJOBSTS AS "Statut"
,B.JCJOBNBR AS "Numero_Job"
,C.ZZSERK AS "Index_Maitre"
,C.ZZMSGP AS "Message_Maitre"
,D.ZMPSRK AS "Message_Pere"
,D.ZMSERK AS "Message_Fils"
,D.ZMLV AS "Niveau"
,D.ZMTSV AS "Champ"
,D.ZMTMPI AS "DD_Erreur"
,D.ZMMSGP AS "Texte_message_1"
,UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(GDTXFT)) AS "Texte_message_2"
,D.ZMEAP AS "Ecran_Erreur"
,D.ZMFMNM AS "SS_ECRAN_ERREUR"
,D.ZMFCTNM AS "BSFN_Erreur"
,D.ZMLNBR AS "Ligne_BSFN"
FROM svm920.f986110 B
,svm920.f986114 A
,proddta.f01131 C
,proddta.f01131m D
,dd920.F00165
WHERE B.JCJOBNBR = 3031114
AND A.JCJOBNBR = B.JCJOBNBR
AND CONCAT (
trim(A.jcpid)
,CONCAT (
'|'
,CONCAT (
trim(A.jcvers)
,'|0|'
)
)
) = C.ZZTSV
AND C.ZZUSER = B.JCUSER
AND C.ZZUPMJ = B.JCSBMDATE
AND C.ZZUPMT >= B.JCSBMTIME
AND C.ZZUPMT <= B.JCACTTIME
AND D.ZMPPSRK = C.ZZSERK
AND gdobnm = 'GT92002'
AND GDTXKY = trim(D.zmtmpi) || '|F||' --change your desired language for translations
ORDER BY C.ZZSERK
,D.ZMSERK
,D.ZMLV
We use essential cookies to make this site work, and optional cookies to enhance your experience.