API to find out Code Combination ID for given Segments

Here is a usage of standard API “fnd_flex_ext.get_combination_id” to find code_combination_id for given segment values. Returns TRUE if combination is valid, or FALSE and sets error message on server using FND_MESSAGE if invalid.

Note: This API return Code Combination ID but it will NOT create a Code Combination ID if it is not existing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
/*********************************************************
*PURPOSE: Sample API To Find Code Combination ID         *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
--
DECLARE
---------------------------
--Declaring Local Variables
---------------------------
   l_1_company                gl_code_combinations.segment1%TYPE;
   l_2_account                gl_code_combinations.segment2%TYPE;
   l_3_cost_center            gl_code_combinations.segment3%TYPE;
   l_4_site                   gl_code_combinations.segment4%TYPE;
   l_5_program                gl_code_combinations.segment5%TYPE;
   l_6_activity               gl_code_combinations.segment6%TYPE;
   l_7_intercompany           gl_code_combinations.segment7%TYPE;
   l_8_future                 gl_code_combinations.segment8%TYPE;
   --
   l_segments                 apps.fnd_flex_ext.segmentarray;
   l_structure_num            fnd_id_flex_structures.id_flex_num%TYPE;
   l_ccid                     gl_code_combinations.code_combination_id%TYPE;
   l_chart_of_accounts_id     gl_code_combinations.chart_of_accounts_id%TYPE;
   l_application_short_name   fnd_application.application_short_name%TYPE;
   l_key_flex_code            fnd_id_flex_structures.id_flex_code%TYPE;
   l_delimiter                fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
   l_return                   BOOLEAN;
   l_err_message              VARCHAR2 (2000);
BEGIN
-------------------------------------------------------------
--Setting Values for Application Short Name and Key Flex Code
-------------------------------------------------------------
   l_application_short_name := 'SQLGL';
   l_key_flex_code := 'GL#';
 
-----------------------------
--Finding Char of Accounts ID
-----------------------------
   SELECT chart_of_accounts_id
     INTO l_chart_of_accounts_id
     FROM gl_sets_of_books
    WHERE 1 = 1 AND NAME = 'XX Cust Company Name';
 
--------------------------------
--Finding Accounting Flex Number
--------------------------------
   SELECT id_flex_num
     INTO l_structure_num
     FROM apps.fnd_id_flex_structures
    WHERE id_flex_code = l_key_flex_code
      AND id_flex_structure_code = 'XXCUST_ACCOUNTING_FLEXFIELD';
 
-------------------
--Finding Delimiter
-------------------
   l_delimiter :=
      fnd_flex_ext.get_delimiter (l_application_short_name,
                                  l_key_flex_code,
                                  l_chart_of_accounts_id
                                 );
-------------------------------------------------------------------------
--Setting Segment Values (Here in this context there are only 8 segments)
-------------------------------------------------------------------------
   l_1_company      := '9000';
   l_2_account      := '700701';
   l_3_cost_center  := '0534';
   l_4_site         := '0000';
   l_5_program      := '65301';
   l_6_activity     := '000000';
   l_7_intercompany := '0000';
   l_8_future       := '0000';
-----------------------------------------------
--Assigning segments to a segmentarray variable
-----------------------------------------------
   l_segments (1) := l_1_company;
   l_segments (2) := l_2_account;
   l_segments (3) := l_3_cost_center;
   l_segments (4) := l_4_site;
   l_segments (5) := l_5_program;
   l_segments (6) := l_6_activity;
   l_segments (7) := l_7_intercompany;
   l_segments (8) := l_8_future;
 
---------------
--Fetching CCID
---------------
   BEGIN
      l_return :=
         fnd_flex_ext.get_combination_id
                         (application_short_name      => l_application_short_name,
                          key_flex_code               => l_key_flex_code,
                          structure_number            => l_structure_num,
                          validation_date             => SYSDATE,
                          n_segments                  => 8,
                          segments                    => l_segments,
                          combination_id              => l_ccid
                         );
--
      l_err_message := fnd_flex_ext.GET_MESSAGE;
 
--
      IF l_return
      THEN
         DBMS_OUTPUT.put_line ('Generated CCID: ' || l_ccid);
      ELSE
         DBMS_OUTPUT.put_line ('Failed to Fetch CCID: ' || l_err_message);
      END IF;
--
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Exception while fetching CCID: ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception When Others: ' || SQLERRM);
--
--
END;
/