Creating Virtual Ledgers

A virtual ledger is an alternate view of ledger data used only for PS/nVision reporting. For instance, you might want to join data from another table (such as a product category table) with ledger data at reporting time.

You can also use a virtual ledger to provide an alternate security view for different groups of users. For example, you might want to provide an alternate view of the LOCAL ledger for users who need data secured by PROJECT, while other ledger users have access secured by DEPTID. You can set up a virtual ledger called PROJLOCAL, which is a view of the LOCAL ledger secured by PROJECT.

To create a virtual ledger called PROJLOCAL:

1. Select Start > Programs > PeopleTools 8.x > Application Designer.

2. In Application Designer, create an authorization table (AUTH_PROJ_TBL) with OPRID and PROJECT columns.

3. Create a page to maintain the authorization table.

4. Open the LEDGER record and use Save As to create a new record.

5. Name this new record LED_PROJLOCAL_VW.

6. Delete any unnecessary columns and create any additional columns.

   In this example, we add the OPRID column because this view is used for security, and we delete PROCESS_INSTANCE and TIMESTAMPS columns because these columns are not used for reporting.

   The view text must contain the following features:

   • Since we are using this view for security, it must deliver the OPRID (or OPRCLASS or ROWSECCLASS) field.

     This is the first item in the SELECT list below.

   • To get security by Project, the view joins the project authorization table to the ledger, making only the rows with matching projects visible for each user.

   • The SELECT list must deliver the name of the virtual ledger to satisfy the PS/nVision "...LEDGER='PROJLOCAL'..." criteria, even though the rows retrieved are really ACTUALS.

     See the third item in the SELECT list of the sample view text that follows.

   • The Where clause must include "... AND LEDGER = 'LOCAL' ..." to select the desired rows from the real ledger .

     This is example of the view text:

     select b.oprid,
            a.business_unit,
            'PROJLOCAL',
            a.fiscal_year,
            a.accounting_period,
            a.account,
            a.deptid,
            a.product,
            a.project,
            a.affiliate,
            a.currency_cd,
            a.statistics_code,
            a.posted_total_amt
       from ps_ledger a,
            ps_auth_proj_tbl b
      where a.ledger='LOCAL'
        and a.project=b.project

7. Save the record definition and create the SQL view.

8. Create a ledger definition for PROJLOCAL, specifying LED_PROJLOCAL_VW as the reporting view record name.

   The other record and field names can be the same as for LOCAL.

9. Use the Ledgers for a Business Unit page to associate the new ledger with the business units that will be using it.

In PS/nVision layouts, use PROJLOCAL, rather than LOCAL, in the Ledger criteria for reports to be run by the project accounting users.