Target Table System View Creator for Cross-DB Views

When building reports in ADMM, it is very common to have reports that go across databases. For example, consider the following setup:

• Material Master - WRKPTP database
• Customer Master - WRKMDM database
• CMIR - WRKOTC database

The CMIR object will have reports checking if a customer is extended, if a material has a specific unit of measure, etc. These views need to go across databases, and the recommended approach for this is to create a pass-through view. As an example, we would create a view named MMMVKET on the WRKOTC database, and the view definition would be 'SELECT * FROM WRKPTP.dbo.MMMVKET'.

This all works just fine during the build phase. The issue comes when we promote the objects and code to a load environment for projects where we either operate in a validated environment or have parallel load execution. In these scenarios, we now have new load databases below:

WRKPTPL

WRKMDML

WRKOTCL

If we use the Working DB Object Promotion functionality, we would create a view on the WRKOTCL database that pulls material data from the WRKPTP database. This is wrong, and the view needs to pull from WRKPTPL database instead. If we forget to update this manually, the consequences are quite awful as we'd be looking to the wrong environment. Imagine if we used WRKPTP to get material numbers in the CMIR object instead of WRKPTPL, we will have basically corrupted the entire object in the load client.

There are a few options I can think of to handle this better:

1. Expand the System View concept to Target Tables. This would let you select the target table and source database (i.e. WRKPTPL.dbo.MMMVKET), then define the target databases (WRKOTCL) and the tool would create the pass through views for you.
2. In Working DB Object Promotion, add in the capability to find/replace DB names in views with another DB name. We would also have to account for this when doing the code compare - right now the tool will flag these cross-database views as having a warning for the code being different, even though the different code is correct.
3. Do not promote cross-database views at all using Working DB Object Promotion and provide a modal telling the consultant to do it manually. This is at least better because it forcibly gives a decision point to the consultant to do something. Right now, the consultant just has to "remember" to do this, and if they don't the outcome can be dire.