Custom Table/View

[binky]

Is it possible to create a View in MSSQL and then have JDE recognize it as a table?

My thought was that the specifications wouldn't be created for JDE without creating the table through OMW. What if I created a tabel with the three fields I required then deleted the table in MSSQL and created the required View with a custom query and gave it the same name as the table?

Any thoughts or comments would be greatly appreciated!

 

[BBritain]

Yes Binky, it is possible. In fact, if you wish, look up 'virtual table', or 'database view', or 'archiving' to see some of the applications we have applied this to.

Ben

 

[binky]

Thanks for the responce. I did as you recommended and found a thread addressing my exact need.

I have done the following
(1) Design the table in JDE table designer. Did not generate the table or indexes. Save the table. (Does anything more need to be done like generate the header file?)
(2) Created the View on SQL Server. Assigned the view the same permissions as the base table(s). Made sure the columns are the same type and name as the columns I designed in JDE. Named the view the same as the table I designed in JDE (Fxxxx)
(3) Designed a business view in JDE to use with a UBE.

I am getting an error in the UBE indicating:
'Unable to locate table in specified datasource for section'

Any thoughts?

 

[BBritain]

Binky,

By the way, I didn't mean to be short worded - I had been called away in the middle of my response, but you got the main gist of it, which is good.

Now this is where I depart from many of the others who speak up about this subject; I worked my problems on this many versions back and I DID have to generate the table and then drop it. Somehow, OneWorld 'knew' that the table did not exist unless I generated it. This is directly contrary to about everyone else who say 'don't generate' the table.

Another thing to look at is when I (or my database administrator) created the view - they created it in the proper JDE (Peoplesoft) environment while logged in as the login used by the midlevel software.

Obviously, I'm am not fully versed on the technical jargon. I'm sure there are plenty about to chime in. Right? Scott?

Ben again

 

[DBohner-(db)]

Just to add to what Ben was saying...

JDE Tables/Views, when generated from JDE, are created with authorities necessary for JDE to see and use them. If tables/views are created outside of JDE - they may not have the necessary permissions.

You might want to review the permissions of the logical/view and validate that JDE can use it.

Daniel

 

[Adrian_Chimirel]

Did you check the TBLE & BSVW & UBE in?
Did you deploy them on the server through an update package?

 

[michelle_perrin]

After the creating the Table Definition in JDE click on the Generate Table button then click on CANCEL at the next form. This seems to create the table specs within JDE without actually creating the Table Definition on the Database.

 

[binky]

No, I haven't built a package yet. Is this absolutely necessary? I have the view on the server and the header file.

I am going to try that next anyways.

Thanks
Binky

 

[habarric]

I know you also have to grant rights to jde_role(in my environment)...
sign on as table owner and issue:
Grant all on <View/table name> to jde_role;

I'm sure MSSQL has a similar command...

Hall B.
OneWorld XE SP20_Q1
Oracle 8.1.7

 

[binky]

Thanks to all to helped with this! I greatly appreciate it. I realized after reading another thread that I had remembered to alias the fields the same as the Data Dictionary alias but forgot the table prefix. Once I fixed that it worked like a charm.

Thanks Again