Section Access Issue

[elie_issa]

Dear all,

I have a dashboard where we have two major tables :

Vacancies and Assignments

We are facing data issue when putting the security on the two tables because not all the vacancies have assignments so the security on the vacancy table is removing all the data not found in the assignment table (Vacancy ID is the key between the two tables)

If we remove the security on the vacancy we have the data.

we tried to make left join on the assignment table but still not working.

N.B : if we remove the section access the relation and the data is correct but when working with security there is data missing.

Regards.

[juraj_misina]

I think you need to create a compound key for your section access table. Have a look at Generic keys.

[agigliotti]

i think your problem is related to data reduction based on section access.

i suggest you to take a look at working with SA in Qliksense qmc

I hope it helps.

[elie_issa]

below my section access

LOAD ACCESS,

     USERID,

     upper(S_COUNTRY) as S_COUNTRY,

     upper(S_ORG) as S_ORG,

     upper(S_GRADE) as S_GRADE,

     upper(S_NATIONALITY) as S_NATIONALITY,

     upper(S_GRADE) as S_VAC_GRADE,

     SubField(OMIT,'|') AS OMIT;

[SecurityTable]:

SELECT flv.attribute8 "ACCESS",

       hl.description "USERID",

       upper(decode(flv.attribute6, '*', '*', pg.name)) S_GRADE,

       upper(nvl(hr_general.decode_territory(flv.attribute2), '*')) S_COUNTRY,

       upper(nvl(haou.name,'*')) S_ORG,

       upper(nvl(hr_general.decode_lookup('NATIONALITY', flv.attribute9), '*')) S_NATIONALITY,

       (select hl1.description from hr_lookups hl1 where hl1.lookup_type = 'XXIDC_BI_USER_SECURITY_OMIT' and hl1.lookup_code = flv.attribute7) OMIT

  FROM hr_lookups                hl,

       fnd_lookup_values         flv,

       hr_all_organization_units haou,

       per_grades                pg,

       fnd_user fu

WHERE hl.lookup_type = 'XXIDC_BI_USER_SECURITY'

   AND fu.user_id = flv.ATTRIBUTE1

   AND flv.language = 'US'

   and nvl(to_char(haou.organization_id), 'xx')  in  (select to_char(haou1.organization_id)

     from hr_all_organization_units haou1

                                  where flv.attribute3 = xxidc_pay_utils_pkg1.get_parentorg_bytyp(

haou1.organization_id,

                                                                          (SELECT haou2.type

                                                                             FROM hr_all_organization_units haou2

                                                                            WHERE haou2.organization_id = flv.attribute3),

                                                                          4062)

                                  and decode (flv.attribute2,'LB',81,'SA',1221,'EG',901) = haou1.business_group_id

                                    union all (select 'xx' from dual where flv.attribute3 is null))    

   AND flv.lookup_type = hl.lookup_type

   AND decode (flv.attribute2,'LB',81,'SA',1221,'EG',901) = haou.business_group_id(+)

   AND flv.lookup_code = hl.lookup_code

   AND to_number(pg.name) <= to_number(decode(flv.attribute6, '*', 6, flv.attribute6))

   AND pg.business_group_id = 81

   AND flv.enabled_flag = 'Y'

   AND flv.meaning LIKE 'HCM Dashboard-%';

the problem we are facing is when the assignment doesn't have a link in the vacancy table in this case the S_VAC_GRADE will not match and the record will be removed.

Any idea what is the best way to fix it.

Thanks.

[juraj_misina]

Can you make a screenshot of data model from data model viewer and post it here? That would give us a better picture.

Juraj