Fetch From A Variable Table (E810)

Hi guys,
I have a request from the user.
Been cracking my head but still could not come up with the solution.
Would really appreciate the help!

The user wants a report that shows where a specific Address Number has been used across JDE.

I have come up with the partial solution:

Find the Table using the Cross-Reference Facility based by the data item "AN8" and "SHAN".
Here, I would have the tables such as:
1. F0101
2. F4211
3. etc.

Then I'm stuck!

I want to check that these tables that I have obtained whether the "AN8" or "SHAN" has the Address Book value, exp: AN8 = "4343".

But I can't figure out how to use the fetch using a table name that is "non-static".

The only workaround for me is to pull out all these table, then retrieve it 1 at a time.
By the way, I still need to match the table if the record can be found in that specific table to obtain the Application Name using the Cross-Reference Facility.

Any ideas?

Regards,
Adrian.
Technical Consultant
World, OneWorld

Wow, what a request !! May I know the reason for this i.e what exactly is the user trying to achieve ?

Even if you were able to retrieve and pass the table name as a parameter to open it, you'd also need to pass the table prefix too. For e.g AN8 is ABAN8 field in the Address Book Master F0101 and ALAN8 in the Address by Date F0116.

Regards,

Shailesh

Hello,
One way might be to narrow it down by the Address Book search type . For example, if it is a vendor you are looking for , look only in the purchasing tables(F4301, F4311, etc) and the AP Tables . This will require some hard coding though ...

Looks to me like you need to use a different reporting technology.

As long as you had the XRef and a table with all the prefixes for each of the tables that AN8 & SHAN are used there are a number of report technologies to use.

Personally, because of my familiarity with it, I'd use VB.Net.

Good luck!

Hi guys,
Thanks for the reply.

The client actually wants to check where the Address Number is being used across the system.
Name change in their Address Book is common and they would like to know the impact of the Address Number change before actually changing it.

Any ideas? Would really appreciate it.

Regards,
Adrian.

Depending on your back end database...

You could write a SQL Stored Proc to go through the tables which have the field (using the cross ref table), append on the table prefix (look that up by table), and load a static temp custom table to then query on in JDE. That way, you call the stored proc, passing in your AN8 value, and query the custom table. Done.

At least, that is how I would approach it. You should also be able to do it all in a C BSFN if you know how.

Either way, tough request to fullfil in standard JDE.

Adrian,

This is a pretty tall request. Personally, I would go back to the customer for some clarification. It would be virtually impossible to find every single location an AB number is used (...possible, but it would take you a very long time at a cost probably not acceptable to the client...). Imagine that this number may be found in AN8 fields, but also in a multitude of other fields. I like the other suggestion of working based on AB type.

My guess is that they just want to identify any open/current transactions for the AB# and be able to address any issues with the change. If you go back and clarify (and educate) them, I suspect your job will be much easier.

good luck!