OBIA 7.9.6.3 PeopleSoft - Unable To View The Dashboard Report Results (Doc ID 1470272.1)

Last updated on FEBRUARY 10, 2020

Applies to:

Goal

We ran the ETL successfully and we do see data in the fact tables for GL
OBIA 7.9.6.3 PeopleSoft
 

Go to Financials -->  Balance Sheet and any other reports having Current Fiscal Quarter and they get no Results , if we remove this filter we can see results.

Changed the Current Fiscal Quarter Variable to 2011 Q1 which is the default value and now they can see the results since they do have Data.

Bascially the Current Fiscal Variables Init Block is failing because Mcal Calendar Wid Identification is failing .

 User trying to login to OBIEE using PSFT application role using who belongs to the Legder Similar to EBS

The Ledger Inti Block has below query in Security guide

SELECT DISTINCT'LEDGER', LG.SETID || SO.LEDGER

FROM PS_SEC_LEDGER_OPR SO, PS_LED_DEFN_TBL LG, PS_INSTALLATION_FS IFS

WHERE SO.LEDGER = LG.LEDGER AND IFS.SECURITY_TYPE = 'O'

AND IFS.LEDGER_SECURITY = 'Y' AND SO.OPRID = 'VALUEOF(NQ_SESSION.USER)'

UNION

SELECT distinct 'LEDGER', LG.SETID || SC.LEDGER

FROM PS_SEC_LEDGER_CLS SC, PS_LED_GRP_TBL LG, PSOPRDEFN OP,PSROLEUSER ORL, PSROLECLASS RCL, PS_INSTALLATION_FS IFS

WHERE SC.LEDGER_GROUP = LG.LEDGER_GROUP AND SC.OPRCLASS = RCL.CLASSID AND OP.OPRID = ORL.ROLEUSER

AND ORL.ROLENAME = RCL.ROLENAME and IFS.SECURITY_TYPE = 'C' AND IFS.LEDGER_SECURITY = 'Y' AND OP.OPRID = 'VALUEOF(NQ_

SESSION.USER)'

NQ_SESSION.USER ='xyz1234'

Here is the output values of the Ledger SQL:

+++++++++++++++
LEDGER MIDIVACTUALS
+++++++++++++++

This Ledger Session variable  is used by Mcal Calendar Wid Identification Init Block in below query

select max(MCAL_CAL_WID) from VALUEOF(OLAPTBO).W_MCAL_CONTEXT_G where ((ledger_id=valueof(0,NQ_SESSION.LEDGER)) or

(org_id=valueof(NQ_SESSION.OU_ORG)')) AND CLASS='GL'

When we check the data in W_MCAL_CONTEXT_G  ledger_id is based on 3 column and values similar to “C0010~MIDIV~ACTUALS” which is BU~SETID~LEDGER

Please find attached data.

Based on this data this condition  ledger_id=valueof(0,NQ_SESSION.LEDGER  will never satisfy

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.