Thursday, May 24, 2012

To Get Account Description

FUNCTION XXAA_GL_ACCT_DESCRIPTION (
   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 XXAA_GL_ACCT_DESCRIPTION;

1 comment:

Larry Bostick said...

This is so perfect. This is my homework and I am so glad to find this piece of code in here. I better understand it first before submitting it to my professor.

Post a Comment

Best Blogger TipsGet Flower Effect