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!

Standard Query To Get Account Description

Discussion in 'Oracle Apps Technical' started by mshali, Nov 19, 2012.

  1. mshali

    mshali Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Riyadh
    Code (SQL):
    -- Function and procedure implementations
      FUNCTION To_Get_Account_Desc  (I_LEDGER_ID IN NUMBER,
                                     I_SEGMENT3  IN VARCHAR2,
                                     I_SEGMENT4  IN VARCHAR2,
                                     I_SEGMENT5  IN VARCHAR2) RETURN VARCHAR2 IS
       
      v_description1     Fnd_Flex_Values_Vl.description%TYPE;
      v_description2     Fnd_Flex_Values_Vl.description%TYPE;
      v_description3     Fnd_Flex_Values_Vl.description%TYPE;
         
      BEGIN
     
     SELECT v.description
     INTO  v_description1
    FROM  Fnd_Flex_Values_Vl v
       , fnd_flex_value_sets S
       , FND_ID_FLEX_SEGMENTS FIFS
       , GL_LEDGERS  L
    WHERE v.FLEX_VALUE               = I_SEGMENT3
    AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
    AND FIFS.APPLICATION_ID          = 101
    AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
    AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT3'
    AND FIFS.ID_FLEX_CODE            = 'GL#'
    AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
    AND L.LEDGER_ID                  = I_LEDGER_ID;
         
       
    SELECT   v.description
    INTO     v_description2
    FROM  Fnd_Flex_Values_Vl v
       , fnd_flex_value_sets S
       , FND_ID_FLEX_SEGMENTS FIFS
       , GL_LEDGERS  L
    WHERE v.FLEX_VALUE               = I_SEGMENT4
    AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
    AND FIFS.APPLICATION_ID          = 101
    AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
    AND v.parent_flex_value_low      = I_SEGMENT3
    AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT4'
    AND  FIFS.ID_FLEX_CODE           = 'GL#'
    AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
    AND L.LEDGER_ID                  = I_LEDGER_ID;

     
     SELECT v.description
     INTO  v_description3
    FROM  Fnd_Flex_Values_Vl v
       , fnd_flex_value_sets S
       , FND_ID_FLEX_SEGMENTS FIFS
       , GL_LEDGERS  L
    WHERE v.FLEX_VALUE               = I_SEGMENT5
    AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
    AND FIFS.APPLICATION_ID          = 101
    AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
    AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT5'
    AND  FIFS.ID_FLEX_CODE           = 'GL#'
    AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
    AND L.LEDGER_ID                  = I_LEDGER_ID;
                                 
                                   
      RETURN(v_description1||'-'||v_description2||'-'||v_description3);
      EXCEPTION
     WHEN others THEN
      RETURN(NULL);                            
      END  To_Get_Account_Desc;