Active Party Relationships SQL query

-------------------------------------------------------------------------------------------
 -- SQL statement to derive Active Party relationships related to active account parties
--------------------------------------------------------------------------------------
SELECT hr.relationship_id, hr.subject_id,
       hp.party_number subject_party_number, hr.subject_type,
       subject_table_name, object_id,
       hp.party_name Subject_Party_Name,
       (SELECT party_number
          FROM hz_parties
         WHERE party_id = hr.object_id) object_party_number, object_type,
       object_table_name, hr.party_id rel_party_id,
       (SELECT party_number
          FROM hz_parties
         WHERE party_id = hr.party_id) rel_party_number, relationship_type,
       relationship_code, directional_flag, direction_code, start_date,
       end_date
  FROM hz_relationships hr, hz_parties hp, hz_cust_accounts hca
 WHERE hp.party_id = hca.party_id
   AND hp.party_id = hr.subject_id
   AND hr.object_id<>hr.subject_id
   AND hr.status = 'A'
   AND hp.status = 'A'
   AND hr.directional_flag = 'F'
   AND TRUNC (hr.end_date) >= TRUNC (SYSDATE)
   AND hca.status = 'A';

How to derive/create code_combination_id(CCID) programatically using fnd_flex_ext.get_ccid

/*----------------------------------------------------------------*/
/*  Function: xx_get_ccid_fnc                                     */
/*  Program desc: Program creates/derives ccid                    */
/*  Finds combination_id for given concatenated segment values.   */
/*  Combination is automatically created if it does not already   */ /*  exist.                                                        */
/*  Commit the transaction soon after calling this function since */
/*if a combination is created it will prevent other users creating*/
/* similar combinations on any flexfield until a commit is issued.*/
/*Returns positive combination_id or 0 and sets error if invalid. */
/*----------------------------------------------------------------*/

FUNCTION xx_get_ccid_fnc (p_segment1            IN OUT VARCHAR2,
                          p_segment2            IN OUT VARCHAR2,
                          p_segment3            IN OUT VARCHAR2,
                          p_segment4            IN OUT VARCHAR2,
                          p_segment5            IN OUT VARCHAR2,
                          p_segment6            IN OUT VARCHAR2,
                          p_start_date_active   IN OUT DATE)
   RETURN NUMBER
IS
   l_nsegs               NUMBER := 6;
   l_message             VARCHAR2 (1000) := NULL;
   l_coa_id              NUMBER;
   l_ledger_id           NUMBER := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
   acctarray             apps.fnd_flex_ext.segmentarray;
   ccid                  NUMBER := 0;
   l_segment1            VARCHAR2 (30) := p_segment1;
   l_segment2            VARCHAR2 (30) := p_segment2;
   l_segment3            VARCHAR2 (30) := p_segment3;
   l_segment4            VARCHAR2 (30) := p_segment4;
   l_segment5            VARCHAR2 (30) := p_segment5;
   l_segment6            VARCHAR2 (30) := p_segment6;
   l_conc_segs           VARCHAR2 (300);
   l_start_date_active   DATE := p_start_date_active;
   l_account             NUMBER := 0;
BEGIN
   -- Concatenate GL Accounting Segment values

   l_conc_segs :=
         l_segment1
      || '.'
      || l_segment2
      || '.'
      || l_segment3
      || '.'
      || l_segment4
      || '.'
      || l_segment5
      || '.'
      || l_segment6;


   --Creates New Accounting Combination if not exist
   SELECT chart_of_accounts_id
     INTO l_coa_id
     FROM gl_ledgers
    WHERE ledger_id = l_ledger_id;

   --Print Concatenated segments
   DBMS_OUTPUT.put_line ('Accounting-Segments Combination=>' || l_conc_segs);


   -- Create/Derive code_combination_id using API
   l_account :=
      fnd_flex_ext.get_ccid (
         application_short_name   => 'SQLGL',
         key_flex_code            => 'GL#',
         structure_number         => l_coa_id,
         validation_date          => TRUNC (NVL (SYSDATE)),
         concatenated_segments    => l_conc_segs);
   -- Get message
   l_message := apps.fnd_message.get;

   -- Print ccid/error details
   IF l_account = 0
   THEN
      DBMS_OUTPUT.put_line (
            'Cannot create CCID for Accounting-Segments=>'
         || l_conc_segs
         || ' , Error details=>'
         || l_message);
   ELSE
      DBMS_OUTPUT.put_line ('Following CCID is created=>' || l_account);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error in xx_get_ccid_fnc=>' || SQLERRM);
END xx_get_ccid_fnc;