Automation,
[ QUOTE ]
Someone is giving a presentation at this year's InSync11 conference in Sydney Australia about this very topic. How to modify the R47011 PDF output to include workcentre messages.
[/ QUOTE ]
That would be me.
The presentation is entitled:
Include Work Centre Error Messages in Logs When Importing Transactions Via ‘Z’ Tables
The abstract is:
<font color="green"> It can be frustrating when a transaction import fails and you need to go to the work centre and open all the nodes to get the base errors. The work centre error messages can be put into a log created during the importation processing. The log can then be emailed to the user or perhaps used to create a Help Desk Call. This is enabled by having a master UBE run the entire import process, including the loading of the "Z" tables then processing the data into the normal JDE tables. There are a couple of custom NER business functions that are used in the process that may be used for other purposes as well. </font>
However, the detail could probably be adapted to the R47011
If a Work Centre report is needed, there is a custom Work Centre UBE (in a Boomerang package thanks to Alex Pastuhov, Everest Software International) attached to a post in
Thread 142984 that may be of use, as may the information and other attachments in the thread.
[ QUOTE ]
I found it difficult to join on the GDTXKY = F01131.ZZSERK.
Instead I had to hard code the join GDTXKY = 38494455
[/ QUOTE ]
Have you tried converting SERK to text first (missed this first time) and using a subquery in the select clause (this will NOT work for the column GDTXFT as it is a BLOB):
Example:
select zzserk,
(select GD???? from F00165 where GDOBNM = 'GT01131' and GDTXKY = TO_CHAR(ZZSERK))
from F01131
where ....
Another option could be an adaption of an Oracle database function procedure and view that Larry Jones posted some years ago. I adapted it for the data dictionary, but it could be used for the Work Centre. I don't know about adapting it to the AS/400 and DB2. (sorry Larry, I can't remember where your original post is and I don't have time to find it at the moment as I'm preparing a presentation for a conference)
Here is the database function procedure:
<font color="blue">
CREATE OR REPLACE FUNCTION dd811."JDETEXTFIX" (p_rowid in
ROWID) RETURN VARCHAR2
IS
v_textout VARCHAR2(4000);
v_lob BLOB;
v_buffer RAW(8192);
v_offset INTEGER := 1;
v_textlen BINARY_INTEGER := 8192;
v_rawlen NUMBER;
v_from_set RAW(2);
v_to_set RAW(1);
v_output RAW(8192);
BEGIN
SELECT gdtxft INTO v_lob
FROM DD811.F00165 where ROWID = p_rowid;
DBMS_LOB.READ (v_lob, v_textlen, v_offset,v_buffer);
v_textout := ' ';
v_from_set := HEXTORAW('7D00');
v_to_set := HEXTORAW('7D');
v_output := UTL_RAW.TRANSLATE (v_buffer, v_from_set, v_to_set);
v_rawlen := UTL_RAW.LENGTH (v_output);
if (v_rawlen > 4000) then
v_rawlen := 4000;
end if;
if (v_rawlen > 0) then
v_textout := UTL_RAW.CAST_TO_VARCHAR2 (UTL_RAW.SUBSTR(v_output,1,v_rawlen));
end if;
RETURN (v_textout);
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN (v_textout);
END;
</font>
Here is the database view that uses the funtion:
<font color="blue">
CREATE OR REPLACE VIEW dd811.F00165_VIEW AS
SELECT
GDOBNM,
GDTXKY,
GDMOSEQN,
GDGTMOTYPE,
GDLNGP,
GDUSER,
(CASE WHEN GDUPMJ > 0 THEN TO_CHAR(TO_DATE(TO_CHAR(GDUPMJ+1900000),'YYYYDDD'),'DD/MM/YYYY') ELSE '0' END) GDUPMJ,
GDTDAY,
GDGTITNM,
GDQUNAM,
GDGTFILENM,
JDETextFix(ROWID) GDTXFT
FROM DD811.F00165
WHERE GDGTMOTYPE = 0 WITH READ ONLY;
</font>
