1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Keyflexfield as a report parameter.

Discussion in 'Oracle Forms and Reports' started by durgareddygade, May 28, 2013.

  1. durgareddygade

    durgareddygade Active Member

    Messages:
    38
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Guntur,Andhra Pradesh
    Dear friends,

    In my report I have location keyflexfield as a parameter which contains seven segments.Generally In keyflexfield if we enter one segment we need to enter remaining segments also.
    But my client wants some of segments not mandatory.Please guide me to solve this.

    thanks
    Durga
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Durga,

    You can try like the following query.

    Code (SQL):
    SELECT *
    FROM   gl_code_combinations gcc
    WHERE  gcc.segment1 = NVL(:P_1,gcc.segment1)
    AND    gcc.segment2 = NVL(:P_2,gcc.segment2)
    AND    gcc.segment3 = NVL(:P_3,gcc.segment3)
    AND    gcc.segment4 = NVL(:P_4,gcc.segment4)


    Regards
    Sambasiva Reddy.K
     
    durgareddygade likes this.
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Durga,

    Please try the query as following. If you pass single flex value then it will take remaining as empty as of below query.

    Code (Text):
    SELECT *
    FROM   gl_code_combinations gcc
    WHERE  nvl2(:P_1,gcc.segment1,1) = nvl(:P_1,1)
    AND    nvl2(:P_2,gcc.segment2,1) = nvl(:P_2,1)
    AND    nvl2(:P_3,gcc.segment3,1) = nvl(:P_3,1)
    AND    nvl2(:P_4,gcc.segment4,1) = nvl(:P_4,1)
    AND    nvl2(:P_5,gcc.segment5,1) = nvl(:P_5,1)
    AND    nvl2(:P_6,gcc.segment6,1) = nvl(:P_6,1);
     
     
    durgareddygade likes this.
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Create a VIEW for the Flexfield structure...then you don't have to worry about all of that.
     
  5. durgareddygade

    durgareddygade Active Member

    Messages:
    38
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Guntur,Andhra Pradesh
    Thanks for all your prompt response..
    Actually here I am taking location_id in fa_locations as a parameter.
    I created special valueset for location_id parameter.
    Please suggest, can we get this by changing special valueset with out using gl_code_combinations table.

    Below is the code i used in special valueset
    FND POPID APPL_SHORT_NAME="OFA"
    CODE="LOC#"
    NUM= 101
    REQUIRED="N"
    DISPLAY="ALL"
    VALIDATE="FULL"
    ID=":!ID"
    SEG=":!VALUE"
    DESC=":!MEANING"
    NAVIGATE="!DIR"
    DINSERT="N"

    Thank u
    durga
     
  6. durgareddygade

    durgareddygade Active Member

    Messages:
    38
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Guntur,Andhra Pradesh
    Can you shed any light on this problem?

    Thanks
    Durga
     
  7. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
  8. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Durga,

    This totally depends on the Validation context passed into the Validate event. Here if u wanna make them not-required then u gotta sacrifice with the validation of the key flexfield (as key flexfield is a combination of values which forms a key). So, if u're validating then it'll actually search for the value in the flexfield and as it won't find any it'll reject the input value.

    I hope its clear.
    So, either u sacrifice your validate event with validation context as "None"
    Or
    Else change the flexfield and use a DFF inplace of KFF.
     
  9. durgareddygade

    durgareddygade Active Member

    Messages:
    38
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Guntur,Andhra Pradesh
    Dear Ari,
    Thanks for your reply.Here as you mentioned I changed validation to None.But still the problem exists.
    Please find attached file.In this location parameter contains special valueset.
    So,In this we need to enter either all values or no values.But I need to enter one of these values are optional.

    Thanks
    Durga
     

    Attached Files:

    • kff.PNG
      kff.PNG
      File size:
      18.4 KB
      Views:
      12
  10. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Durga,

    This is kinda strangely weird yeah !! .. Coz passing the Required="N" and Validate="NONE" does behave the way we desire it to be. I've tested this in my instance and its working like a gem. Plz check if the KFF was marked as required at the master-definition level. If it's, then remove the required flag from there and handle the required property at run-time through the parameters.
    This is what i did and its working all fine.

    If still the problem remains then, post the screenshots of the valueset and also the KFF master definition.
     
  11. durgareddygade

    durgareddygade Active Member

    Messages:
    38
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Guntur,Andhra Pradesh
    Dear Ari ,
    Thanks for your prompt response.
    Plz find the attached files of my special valueset and report parameter screen.
    In this If I enter only country it is showing mentioned error.
    Without entering any vales it is not showing error.If we enter all values also it is not showing any error.
    Plz guide me to move forward...

    Thanks
    Durga
     

    Attached Files:

  12. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Dude !!

    Why r u having a load and validate event? .. Remember u affirmed that u don't wanna validate the parameter input. Just remove the load and validate events. Work with only edit event n see, how it works out. Also, try removing the required_flag from the KFF segments.