Need Help on F4211 & F4311 table joins

Hello,

Iam a new to JDE and need help on an issue.

Any help is greatly appreciated.

I am developing a report on F4311 (PO Detail file)
and F4211 (SO Detail file). For one sales Order in 4211 there will be 1 or more Purchase orders in 4311. What I need to do is, I need to display all the Sales Orders - only one detail line with total order value (sum of aexp column), SO number, Date req etc and the condition is that the related Purchase orders in 4311 for this sales orders should meet the condition that purchase orders should have a line type as D,status last = 347 and status next =348.

Key fileds to identify the related PO for a SO is RORN (Related PO SO number). RORN of 4211 equals PO in 4311 and RORN of 4311 equals SO in 4211.

I thought of doing a simple join between 4211 & 4311, but the problem is that eventhough SO in 4211 equals RORN in 4311 and vice versa, both the columns are of different data types. PO & SO numbers are MathNumeric datatype and RORN is string. So I cant join them.

If I take a businessview only on 4311 and thought of doing a fetch next on 4211, still I cannot pass the values as the data types are of different type.

Is there anyother way to get this.

Can you please let me know. I hope Iam not confusing you. Here is an example.


F4211 Table

SDDOCO SDDCTO SDRORN

310998 SO 01085074
310998 SO 01085068
310998 SO 01085053


F4311 Table

PDDOCO PDDCTO PDRORN PDLNTY PDLTTR PDNXTR
1085053 OP 00310998 D 347 348
1085053 OP 00310998 D 347 348
1085074 OP 00310998 D 347 348


In the above example, the 2 SO's with the RORN's 01085074 and 01085053 will satisfy my condition as they have related PO's in 4311 with the condition matching LNTY = D
and LTTR=347 and NXTR = 348.

Thanks in advance.

Oh how we've all had fun with this one!

You'll need to convert the numeric DOCO field from the F4311 into a string variable (use the Business Function B8000094 to do this).

Once you have your string variable you can use this to match against the RORN when you fetch from the F4211. You will however have to left pad the variable with zero's before can get a match! Use the LPAD facility or CONCATENATE to do this.

Good luck.