6 Replies Latest reply on Oct 10, 2015 12:18 PM by 775371

    Restrict LOV in Batch Element

    775371

      I want to restrict LOV in batch element entry-> batch name -> element lines->Element

         We have more than 20 ELEMENT_NAME in LOV. I have tried using form personalization. But it is not working

       

      Trigger Event as “WHEN-NEW-FORM-INSTANCE” and save the changes

      On Actions tab, enter or select the following values

      • Sequence = 10

      • Type = Builtin

      • Description = Create New Record Group

      On the right side of Actions tab, enter or select the following values

      • Builtin Type = Create Record Group from Query

      • Arguments =     select ELEMENT_NAME from PAY_ELEMENT_TYPES_F where ELEMENT_NAME='Ticket'

      Group Name = XX_ELE

      Click on the “Validate” button, then click on “Apply Now” button

      On next Actions tab, enter or select the following values

      • Sequence = 20

      • Type = Property

      • Description = Assign New Record Group

      On the right side of Actions tab, enter or select the following values

      • Object Type = LOV

      • Target Object = ELEMENTS

      • Property Name = GROUP_NAME

      • Value = XX_ELE

      Validate All (Menu Tool > Validate All), Apply and Save the changes

       

       

      Please help me to fix the issue

        • 1. Re: Restrict LOV in Batch Element
          Jagadekara

          Hi,

           

          did you took same query from .fmb?

           

          Because, we must use same no of columns and same data type.

           

           

          Your query is:

           

          Arguments = select ELEMENT_NAME from PAY_ELEMENT_TYPES_F where ELEMENT_NAME='Ticket'

           

          But in .fmb there is big query. which has 3 columns. So you must have 3 columns and those match with data type of .fmb query columns.

           

          Then it will work.

           

          Hope this will help you...

          • 2. Re: Restrict LOV in Batch Element
            775371

            Thanks for the update. Can you help me with query. I am not familiar with DEV suite

            • 3. Re: Restrict LOV in Batch Element
              Jagadekara

              This is query from .fmb.

               

              select elt1tl.element_name, elt1.element_type_id, elt1.element_name element_name_b from pay_element_types_f elt1, pay_element_types_f_tl elt1tl where

              elt1tl.language = userenv('LANG') and

              elt1.element_type_id = elt1tl.element_type_id and :ctl_globals.session_date between elt1.effective_start_date and elt1.effective_end_date and elt1.indirect_only_flag = 'N' and (elt1.business_group_id + 0 = :ctl_globals.business_group_id or (elt1.business_group_id + 0 is null and elt1.legislation_code = :ctl_globals.legislation_code))

              and (exists

                       (select null

                        from pay_restriction_values psv

                        where psv.restriction_code = 'ELEMENT_TYPE'

                        and psv.customized_restriction_id = :CTL_GLOBALS.customized_restriction_id

                        and (psv.value = 'BOTH' or psv.value = elt1.processing_type))

                   or not exists        

                       (select null

                        from pay_restriction_values psv

                        where psv.restriction_code = 'ELEMENT_TYPE'

                        and psv.customized_restriction_id = :CTL_GLOBALS.customized_restriction_id)

                   )

              and (exists

              (

                      select null

                      from   pay_element_types                ELE_TYPE1,

                             pay_ele_classification_rules     CLASS_RULES,

                             pay_element_sets                 ELE_SET1

                      where   ele_type1.classification_id     = class_rules.classification_id

                      and     ele_set1.element_set_type       = 'C'

                      and    ele_type1.element_type_id = elt1.element_type_id

                      and     exists

                              (select null

                                 from pay_restriction_values psv

                                where psv.restriction_code = 'ELEMENT_SET'

                                  and psv.customized_restriction_id = :ctl_globals.customized_restriction_id

                                  and psv.value = ele_set1.element_set_id)

                      and     class_rules.element_set_id      = ele_set1.element_set_id

                      and    not exists

                             (select 1

                              from   pay_element_type_rules   type_rules1

                              where  type_rules1.element_set_id     = ele_set1.element_set_id

                              and    type_rules1.element_type_id    = ele_type1.element_type_id

                              and    type_rules1.include_or_exclude = 'E')

                      union

                      select null

                      from   pay_element_types           ELE_TYPE2,

                             pay_element_type_rules      TYPE_RULES

                      where  type_rules.include_or_exclude = 'I'      

                      and    ele_type2.element_type_id = elt1.element_type_id

                      and     exists

                              (select null

                                 from pay_restriction_values psv

                                where psv.restriction_code = 'ELEMENT_SET'

                                  and psv.customized_restriction_id = :ctl_globals.customized_restriction_id

                                  and psv.value = type_rules.element_set_id)

                      and    ele_type2.element_type_id        = type_rules.element_type_id

              )

                   or not exists        

                       (select null

                        from pay_restriction_values psv

                        where psv.restriction_code = 'ELEMENT_SET'

                        and psv.customized_restriction_id = :CTL_GLOBALS.customized_restriction_id)

                   )

              order by 1,2

              It may be different some times.

               

              You just download PAYWSQEE.fmb and go to that LOv and take the query and modify according to your requirement.

              • 4. Re: Restrict LOV in Batch Element
                775371

                Thanks a lot Jagadekara, I have updated your query and Issue resolved.

                 

                But one query, I have opened fmd in form builder and in LOV -> Elements, I  can't find plsql editor. How I can get query

                • 5. Re: Restrict LOV in Batch Element
                  Jagadekara

                  Right click on elements click lov wizard. Then click on Query tab. There you can see the actual query

                  • 6. Re: Restrict LOV in Batch Element
                    775371

                    Thanks a lot . I have got....