API to Get and Create GL Code Combination Id

1
2
3

fnd_global.apps_initialize(resp_appl_id => 101,
resp_id => xxxxx,
user_id => xxx);

1
2
3
4
5

--
l_account_array fnd_flex_ext.segmentarray;
c_module VARCHAR2 (100) := 'XXCB_AR_INVOICE_UPLOAD_API';
l_new_ccid NUMBER;
--

1

l_new_ccid := NULL;

  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

--get Receivable Account gl code combinations id
BEGIN
--
SELECT code_combination_id
INTO l_code_combination_id_rec
FROM gl_code_combinations_kfv
WHERE concatenated_segments = SUBSTR(trim(i.rec_gl_account),1,38);
--
EXCEPTION
WHEN OTHERS THEN
-- l_verify_flag := 'N';
l_account_array (1) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 1);
l_account_array (2) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 2);
l_account_array (3) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 3);
l_account_array (4) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 4);
l_account_array (5) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 5);
l_account_array (6) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 6);
l_account_array (7) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 7);
l_account_array (8) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 8);
l_account_array (9) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 9);
l_account_array (10) := getsegmentxfromstring (SUBSTR(trim(i.rec_gl_account),1,38), 10);
--
l_new_ccid := processglsegments (c_module, l_account_array);
--
IF l_new_ccid IS NULL THEN
--
l_error_message := l_error_message||'Invalid Receivables GL Account - '||i.rec_gl_account;
--
ELSE
--
l_code_combination_id_rec := l_new_ccid;
fnd_file.put_line(fnd_file.log,'l_new_ccid ID = ' || l_new_ccid);
--
END IF;
--
--
END;
5) Supporting Functions

---
--- Function/procedures
---
FUNCTION getsegmentxfromstring(
p_string VARCHAR2,
p_token NUMBER)
RETURN VARCHAR2
IS
v_pos1 NUMBER;
v_pos2 NUMBER;
v_string VARCHAR2 (100);
v_string2 VARCHAR2 (100);
BEGIN
v_string := p_string || '-';
--
IF p_token = 1 THEN
v_pos1 := 0;
ELSE
v_pos1 := instr (v_string, '-', 1, p_token - 1);
END IF;
v_pos2 := instr (v_string, '-', 1, p_token);
v_string2 := SUBSTR (v_string, v_pos1 + 1, v_pos2 - v_pos1 - 1);
RETURN v_string2;
--DBMS_OUTPUT.PUT_LINE ( v_token||'='||v_pos1||'='||v_pos2||'='||v_string2 );
END;
--
FUNCTION processglsegments(
l_module VARCHAR2,
l_account_array fnd_flex_ext.segmentarray)
RETURN gl_code_combinations.code_combination_id%type
IS
l_where_segments VARCHAR2 (1000);
l_ccid gl_code_combinations.code_combination_id%type;
l_exception EXCEPTION;
l_structure_number NUMBER;
BEGIN
l_where_segments := NULL;
-- Get CCID using the segments for return
BEGIN
--
BEGIN
SELECT chart_of_accounts_id
INTO l_structure_number
FROM gl_sets_of_books
WHERE set_of_books_id = 2021; --fnd_profile.VALUE('GL_SET_OF_BKS_ID');
--
EXCEPTION
WHEN no_data_found THEN
apps.fnd_log.string ( log_level => fnd_log.level_error, module => l_module, MESSAGE => 'Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID Value ' || fnd_profile.value ('GL_SET_OF_BKS_ID') || ' ' || SQLCODE || ' -ERROR- ' || sqlerrm);
-- RAISE;
END;
--
IF NOT fnd_flex_ext.get_combination_id ( application_short_name => 'SQLGL', key_flex_code => 'GL#', structure_number => l_structure_number, validation_date => sysdate, n_segments => l_account_array.count, segments => l_account_array, combination_id => l_ccid, data_set => NULL) THEN
--
fnd_file.put_line(fnd_file.log,' Error Creating Code Combination '||fnd_flex_ext.get_message);
--
apps.fnd_log.string ( log_level => fnd_log.level_error, module => l_module, MESSAGE => 'Unable to get CCID Error: ' || fnd_flex_ext.get_message || ' ' || SQLCODE || ' -ERROR- ' || sqlerrm);
-- RAISE l_exception;
END IF;
--
apps.fnd_log.string (log_level => fnd_log.level_statement, module => l_module, MESSAGE => 'Found CCID: ' || l_ccid);
--
EXCEPTION
WHEN OTHERS THEN
--
apps.fnd_log.string ( log_level => fnd_log.level_error, module => l_module, MESSAGE => 'Unable to get CCID for Segments passed:' || l_where_segments || SQLCODE || ' -ERROR- ' || sqlerrm);
--
END;
--
RETURN l_ccid;
--
END;
--