Extraction of PO values

Ravi_Shankar41

Member
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C06F32.482BC590
Content-Type: text/plain;
charset="iso-8859-1"


Hi JDEList Users,

Can anyone help me to extract the data from the processing option (PO
values). My requirement is to figure out the tables that are involved in
storing the PO values. My research says that, the PO values are stored in
BLOB field of F983051,and PO text are in F98306.When we tried to blow this
field we have noticed that the field contains ID number. The data is bar
separated according to the ID.We are unable to figure out where the ID is
stored in the data base so as to correlate its PO value in the F983051.

Also, when we print the PO form in the design format, we found this ID
getting printed in the last column of PO template.(This output will show the
relationship with the ID and PO values).

We require to solve this ASAP.

Any input will be greatly appreciated.

Thanks and Regards,

Ravi Shankar R
Application Specialist
AristaSoft International PVT. LTD.
URL http://www.aristasoft.com
[email protected]




------_=_NextPart_001_01C06F32.482BC590
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2653.12">
<TITLE>Extraction of PO values</TITLE>
</HEAD>
<BODY>





<FONT SIZE=3D2 FACE=3D"Arial">Hi JDEList Users,</FONT>
</P>



<FONT SIZE=3D2 FACE=3D"Arial">Can anyone help me to extract the data =
from the processing option (PO values). My requirement is to figure out =
the tables that are involved in storing the PO values. My research says =
that, the PO values are stored in BLOB field of F983051,and PO text are =
in F98306.When we tried to blow this field we have noticed that the =
field contains ID number. The data is bar separated according to the =
ID.We are unable to figure out where the ID is stored in the data base =
so as to correlate its PO value in the F983051.</FONT></P>



<FONT SIZE=3D2 FACE=3D"Arial">Also, when we print the PO form in the =
design format, we found this ID getting printed in the last column of =
PO template.(This output will show the relationship with the ID =
and PO values).</FONT></P>



<FONT SIZE=3D2 FACE=3D"Arial">We require to solve this ASAP.</FONT>
</P>



<FONT SIZE=3D2 FACE=3D"Arial">Any input will be greatly =
appreciated.</FONT>
</P>



<FONT SIZE=3D2 FACE=3D"Arial">Thanks and Regards,</FONT>
</P>



<FONT COLOR=3D"#003366" SIZE=3D2 FACE=3D"Times New Roman">Ravi =
Shankar R</FONT>


<FONT COLOR=3D"#000080" SIZE=3D2 FACE=3D"Times New =
Roman">Application Specialist </FONT>


<FONT COLOR=3D"#000080" SIZE=3D2 FACE=3D"Book Antiqua">AristaSoft =
International PVT. LTD. </FONT>

<FONT COLOR=3D"#000080" SIZE=3D2 FACE=3D"Book Antiqua">URL <A =
HREF=3D"http://www.aristasoft.com" =
TARGET=3D"_blank">http://www.aristasoft.com</A></FONT>

<FONT COLOR=3D"#000080" SIZE=3D2 FACE=3D"Book =
Antiqua">[email protected]</FONT>
</P>





</BODY>
</HTML>
------_=_NextPart_001_01C06F32.482BC590--
 
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C06F52.7CC02A06
Content-Type: text/plain;
charset="iso-8859-1"

Ravi:

I thought I had seen data modeling schematics off of menu GH90735. (I don't
have any version information there currently.) You also might want to
review the documentation. For example:

You can run the following purge programs in Procurement:

PO Detail (F4311)
PO Receiver Detail (F43121)
PO Ledger (F43199)
PO Headers (F4301)
PO Text Lines (F4311)

There is also a section at the beginning of each application guide that
outlines all of the files used. This section usually includes a flow chart.

Joe Mulrooney
 
This emai should be read subject to disclaimer below:

As Zoltan commonly precedes: put your system config in the signature. Also,
I'm not sure that my answer actually answers your question - English in not
my native tongue. But I'll write my experiences just the same - maybe
someone will find them useful.

I did some experimenting with PO values in B7332 a while ago.
In this version (presumably, in all from B733 to XE), there's a field in
F985031, VRFRMTSTR, which describes, what fields are meaningful in BLOB
data. It has <c> for 1 char, <cNUMBER> for string NUMBER-1 [JDEdwards uses
zero-terminated strings], for example, <c9> for String 9-1=8; d for Date, m
for Math, there may be other values, never listed them all.
Lets take P0004A ZJDE0002 for example - it'll have value c5c3 in this field,
which means it's two strings, 4 and 2 chars long, which makes sense, 'cause
system code is 4 char long (don't flame me, I know that you only 3 are
really used now) and User Defined Code is 2 chars long. If you decode BLOB,
you'll get the following data:
" 00|LT.1|T0004A|2|3.."
(zero chars are replaced with dots to improve legibility, double quotes
enclose actual value). The same in Hex:
20 30 30 7C 4C 54 00 31 7C 54 30 30 30 34 41 7C 32 7C 33 00 00

Basically, to get PO values, you need to decode first part of the BLOB (the
one before the zero char): First PO parameter is "00", and the second
parameter is "LT". You need to drop everything, which is after zero char, or
after you decode all fields, which are described in VRFRMTSTR. By the way,
the last bit is important - I found more then several occasions, where you
have more fields in the first part of BLOB (before zero char), then are
described in VRFRMTSTR. At first I wondered, then just tried changing them
in interactive version. What a surprise: they were gone. I guess it was some
corruption during some stage at a time (and this time can be very long ago,
see P0004A ZJDE0133 for a bit of fun - this was there in B7331, and in B7332
(and in XE Standalone, as I just figured out)).
The part after zero char is the reference to PO data structure (I never used
this part, so I may be wrong, but points appear valid). 1 seems always to be
there, then there is T0004A, which is the PO data structure name, and then
the numbers. It appears, that if you take only fields from F98306 (PTPOTP =
1), and sort them first by PTITNUM and then by PTSQNUM, and assign numbers
from 1 upwards, you get the sequence numbers, which are used. But this logic
is immediately violated by the program we took as an example - here
numbering starts from 2. Still, it seems to me, that as long as you order
decoded values in the same sequence as fields in F98306, you should be on
the safe side.

Hope it helps,
Vladimir Ponomarev
Config: Well, as noted in the body - this particular part was on B7332,
Wintel, SQL

PS: if you just need PO values for all programs - why don't you output
R98306 to CSV file :).

Personal Disclaimer: All of the above is result of investigation out of
curiosity, and should be taken as such. I can't promise that it'll work for
you. My Employer does not approve these recommendations. I do not recommend
that you work with database directly instead of provided means in JDEdwards.
And so on and so forth.
Also:
E-mail disclaimer:
The information in the e-mail is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this e-mail by anyone
else is unauthorised. If you are not the intended recipient, any
disclosure,copying, distribution or any action taken or omitted to be taken
in reliance on it, is prohibited and may be unlawful.
 
Re: RE: Extraction of PO values

Hi vap,

Your technique - while interesting, seems like the hard way to me. I recommend using the API:

lpdsPrOptT59008 = (LPDST59008) AllocatePOVersionData (hUser,
szUBE, szVersion, sizeof(DST59008));

It returns a pointer to a Processing Option structure that can be simply interogated.

There are numerous examples of a call to this in OneWorld business fuinctions and it is portable over all versions, supported by JDE, and pretty easy to use.


Regards

Mike Maguire
[email protected]
 
RE: RE: Extraction of PO values

Well, Mike,

Your technique, while far better then mine in terms of reliability and
accuracy, is still a harder way for me: I'm no programmer, I know a good
deal about SQL and a little about VBA, but calling jde API (and writing a C
program or BSFN) is way beyond my abilities. Also, it beats the purpose as I
see it: retrieve PO data without using OneWorld, because if you can use
Print PO UBE to CSV. I may be wrong, I just don't know - maybe you can call
jde API just by declaring a function in JDEdwards dlls. Still thank you for
the info - it makes a lot more sense to our programmers then my approach.

Regards,
Vladimir Ponomarev
 
Ravi,
Why don't you trie to investigate R98306 PO Values and Text UBE to figure out how does it retrieve the values using the B92000030 Get PO Values and text BSFN calls.
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Back
Top