business view on same tables

CHo

VIP Member
Hi all.

I need to write a price audit report. The BA says the business view need to be a left outer join on F42199 and F42199 (ledger). (not a typo)

Has anyone done a business view with the same tables?

BSVW designs let me do it.

I'm wondering if there is anything I need to watch out for and if it is possible.

Thanks.
 

Larry_Jones

Legendary Poster
Perhaps there was a typo?

If not - what fields are the two tables supposed to be joined by? The obvious ones of DOCO and LNID make no sense.
 

CHo

VIP Member
The new UBE is price audit report over F42199. There is over 1 million in F42199 even with archiving. The reasoning is data selection over F42199 (1) would shrink number of records where override = 1 and status = 620, but UBE still needs to print info where override = 0 (F42199 (2)) (original price).

The join would be on KCOO, DOCO, DCTO, LNID. Price changes for each item must be captured thus LNID.
 

CHo

VIP Member
To improve performance. BA saying that F4211-> F42199 might miss some records (forgot explanation).

We are abandoning this self join business view idea and going back to the drawing board.
 

craig_welton

Legendary Poster
Just to add to this ... While a self-join in SQL is perfectly acceptable and useful (think of F0101 to F0101 by the related AB #s), the web client does not support it (last time I tried anyway). You can create the BSVW fine, but if it's used in a report where data selection can be applied (via the web), it throws an error. The old SQL view solution can be applied. I will try this is 9.2 when I get a minute to see if it's fixed, but I doubt it.

Craig
 

CHo

VIP Member
Hi.

Thanks for all the responses.

The BA has gone back to the drawing board. I've been told that they do not want to do a database view over virtual JDE table.
 

Kim Schmidt

Active Member
In situations like this, you might be able to use a subsection join in your UBE. Use the parent section to filter out the item(s) of interest, and the child to extract additional details. An example would be if you need to get the chart of accounts from F0901 for business units that have a certain object account. The parent section would have a set selection filtering the desired object account, and the subsection would be joined on parent.GMMCU = child.GMMCU. Your requirement might be met if you make your join on a subset of the primary key (SLKCOO, SLDOCO, SLDCTO, and SLLNID).
 
Top