How to eliminate duplicates from PLSQL table?

In Oracle 10g it made easy to filter out duplicate data from pl/sql table or nested table. This is possible in 10g by using"MULTISET" key word.

Following is the example which eliminates duplicate values from the PLSQL table...

DECLARE
TYPE nested_typ IS TABLE OF VARCHAR2(200);
v_test_type    nested_typ := nested_typ();
v_temp         nested_typ ;
BEGIN
v_test_type.extend(9);
-- read values into array.
v_test_type(1) := 'NEW YORK';
v_test_type(2) := 'SYDNEY';
v_test_type(3) := 'SINGAPORE';
v_test_type(4) := 'PERTH';
v_test_type(5) := 'NEW YORK';
v_test_type(6) := 'NEW YORK';
v_test_type(7) := 'DELHI';
v_test_type(8) := 'PERTH';
v_test_type(9) := 'MADURAI';

dbms_output.put_line('Cities before distinct..');
-- display values before distinct..
FOR i IN v_test_type.first..v_test_type.last
LOOP
   dbms_output.put_line(v_test_type(i));
END LOOP ;

-- assign all values to v_temp, which are in v_test_type
v_temp := v_test_type ;

-- take ditinct values out of v_temp, v_test_type into v_test_type
v_test_type := v_test_type MULTISET UNION DISTINCT v_temp ;
dbms_output.put_line('Cities after distinct..');

-- display values after distinct..
FOR i IN v_test_type.first..v_test_type.last
LOOP
   dbms_output.put_line(v_test_type(i));
END LOOP ;
END ;
/

OUTPUT
========

Cities BEFORE DISTINCT..
------------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW YORK
NEW YORK
DELHI
PERTH
MADURAI
.
Cities AFTER DISTINCT..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
DELHI
MADURAI

PL/SQL PROCEDURE successfully completed.

Account Combination Creation API

Account Combination Creation API:

There are 2 flexfield APIs which you can use to either validation the existing given code_combination_id (ccid) or to validate and create a new code combination (Account).
1. fnd_flex_keyval.VALIDATE_SEGS() - To validate and if required can create Account code combination.
2. fnd_flex_keyval.validate_ccid() - The API used to validate the code combination id value. If the code combination valid then this API will returns true else false.

1. fnd_flex_keyval.VALIDATE_SEGS():

------------------------------------------------------------------------------------------------------------
create or replace function create_ccid
( p_concat_segs in varchar2
) return varchar2
is
  -- pragma autonomous_transaction; -- if you need autonomy!
  l_keyval_status     BOOLEAN;
  l_coa_id            NUMBER;
begin
  begin
    select chart_of_accounts_id
    into   l_coa_id
    from   gl_sets_of_books
    where  set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID');
  exception
    when no_data_found then
      dbms_output.put_line('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID');
      dbms_output.put_line('Try setting up your environment with fnd_global.apps_initialize');
      raise;
  end;
  -- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION
  -- create will only work if dynamic inserts on and cross validation rules not broken
  l_keyval_status := fnd_flex_keyval.validate_segs(
                                           'CREATE_COMBINATION',
                                           'SQLGL',
                                           'GL#',
                                           l_coa_id,
                                           p_concat_segs,
                                           'V',
                                           sysdate,
                                           'ALL', NULL, NULL, NULL, NULL,
                                           FALSE,FALSE, NULL, NULL, NULL);
  if l_keyval_status then
    return 'S';
  else
    return 'F';
  end if;
end create_ccid;
------------------------------------------------------------------------------------------------------------
declare
  l_status varchar2(1);
begin
  l_status := create_ccid('1-9999-9999-9999-9999');
  dbms_output.put_line('Returned: ' || l_status);
  commit;
end;
------------------------------------------------------------------------------------------------------------
select gcc.code_combination_id
,      gcc.concatenated_segments
,      gcc.enabled_flag
,      gcc.start_date_active
,      gcc.end_date_active
,      gcc.detail_posting_allowed
,      gcc.detail_budgeting_allowed
from   gl_code_combinations_kfv gcc
where  concatenated_segments = '1-9999-9999-9999-9999';
------------------------------------------------------------------------------------------------------------
Here Operation can be :

'FIND_COMBINATION' - Combination must already exist.
'CREATE_COMBINATION' - Combination is created if doesn't exist.
'CREATE_COMB_NO_AT' - same as create_combination but does not use an autonomous transaction.
'CHECK_COMBINATION' - Checks if combination valid, doesn't create.
'DEFAULT_COMBINATION' - Returns minimal default combination.
'CHECK_SEGMENTS' - Validates segments individually.

If the above function returns FALSE then use fnd_flex_keyval.error_message() to get the exact FND FLEXFIELD error message to work on.
Another very important function - fnd_flex_keyval.combination_id() returns the ccid of the newly created code combination by the above API.

2. fnd_flex_keyval.validate_ccid():
------------------------------------------------------------------------------------------------------------
Begin
name VARCHAR(2000) := NULL;
result BOOLEAN := TRUE;
begin
IF :from_locator_id IS NULL THEN
RETURN '';
END IF;
result := FND_FLEX_KEYVAL.validate_ccid(appl_short_name=>'INV',
                                                                        key_flex_code=>:P_LOCATOR_FLEX_CODE,
                                                                        structure_number=>:LP_STRUCTURE_NUM,
                                                                        combination_id=>:from_locator_id,
                                                                        data_set=>:ORGANIZATION_ID);

IF result THEN
name := FND_FLEX_KEYVAL.concatenated_values;
END IF;
dbms_output.put_line(name);
end;

------------------------------------------------------------------------------------------------------------
FND_FLEX_KEYVAL.concatenated_values is the API used to concatenate segments of code combination id which was passed in the validate_ccid API.