4 Replies Latest reply: Oct 16, 2018 7:31 AM by Juraj Misina RSS

    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.




        • Re: Section Access Issue
          Juraj Misina

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

          • Re: Section Access Issue
            Andrea Gigliotti

            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.

              • Re: Section Access Issue
                Elie Issa

                below my section access

                LOAD ACCESS,


                     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;


                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(


                                                                                          (SELECT haou2.type

                                                                                             FROM hr_all_organization_units haou2

                                                                                            WHERE haou2.organization_id = flv.attribute3),


                                                  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.