Using Ledger Reporting View Access

To get data from a ledger, PS/nVision uses a Select statement from the Reporting view, not the actual Ledger table, using the user’s ID or primary permission list as part of the Where clause. PS/nVision checks the record definition of the Reporting view and determines whether it includes OPRID, OPRCLASS, or ROWSECCLASS in its keys, and generates the appropriate phrase in its Where clause. Individual users are not given direct database access, for example, using a GRANT to either the Ledger table or the Reporting view. (Using GRANT is specific to Oracle.)

This example shows Select of ledger data as used by PS/nVision, in which both OPRID and OPRCLASS are specified in the Authorization table:

SELECT ACCOUNT, SUM(POSTED_TOTAL_AMT)
 FROM PS_LED_RPTG_VW
WHERE BUSINESS_UNIT = 'NEWGN'
   AND LEDGER = 'ACTUALS'
   AND FISCAL_YEAR = 1993
   AND ACCOUNTING_PERIOD BETWEEN 9 AND 12
   AND ACCOUNT IN ('800100','800200','800300')
   AND DEPTID = '0300'
   AND OPRID = 'GLUSER' and OPRCLASS = ‘MANAGER’ 
GROUP BY ACCOUNT