[Home] [Help]
PACKAGE BODY: APPS.GL_COA_SEG_VAL_IMP_PUB
Source
1 PACKAGE BODY gl_coa_seg_val_imp_pub AS
2 /* $Header: GLSVIPBB.pls 120.3.12010000.1 2009/12/16 11:52:27 sommukhe noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'gl_coa_seg_val_imp_pub';
5
6 PROCEDURE coa_segment_val_imp (
7 p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
9 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
10 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 p_gl_flex_values_obj_tbl IN OUT NOCOPY GL_FLEX_VALUES_OBJ_TBL,
15 p_gl_flex_values_nh_obj_tbl IN OUT NOCOPY GL_FLEX_VALUES_NH_OBJ_TBL,
16 p_gl_flex_values_status OUT NOCOPY VARCHAR2,
17 p_gl_flex_values_nh_status OUT NOCOPY VARCHAR2
18
19 ) AS
20 /***********************************************************************************************
21 Created By: Somnath Mukherjee
22 Date Created By: 01-AUG-2008
23 Purpose: This is a public API to import data from external system to GL.
24 Known limitations,enhancements,remarks:
25
26 Change History
27
28 Who When What
29 VGATTU 28-NOV-08 Submitting the CP "Program - Inherit Segment Value Attributes"
30 ***********************************************************************************************/
31 l_gl_flex_values_tbl gl_coa_seg_val_imp_pub.gl_flex_values_tbl_type;
32 l_gl_flex_values_nh_tbl gl_coa_seg_val_imp_pub.gl_flex_values_nh_tbl_type;
33
34 l_api_name CONSTANT VARCHAR2(30) := 'coa_segment_val_imp';
35 l_api_version CONSTANT NUMBER := 1.0;
36 l_err_msg VARCHAR2(2000);
37 l_appl_name VARCHAR2(30);
38 l_c_msg_name fnd_new_messages.message_name%TYPE;
39 l_n_msg_num fnd_new_messages.message_number%TYPE;
40 l_c_msg_txt fnd_new_messages.message_text%TYPE;
41 l_user_id NUMBER;
42 l_resp_id NUMBER;
43 l_apps_id NUMBER;
44 l_acc_set_id NUMBER;
45 l_req_id NUMBER;
46
47 PROCEDURE get_message(p_c_msg_name VARCHAR2,p_n_msg_num OUT NOCOPY NUMBER,p_c_msg_txt OUT NOCOPY VARCHAR2) AS
48 CURSOR c_msg(cp_c_msg_name fnd_new_messages.message_name%TYPE ) IS
49 SELECT
50 message_number,
51 message_text
52 FROM fnd_new_messages
53 WHERE application_id=101
54 AND language_code = USERENV('LANG')
55 AND message_name=cp_c_msg_name;
56
57 rec_c_msg c_msg%ROWTYPE;
58 BEGIN
59 OPEN c_msg(p_c_msg_name);
60 FETCH c_msg INTO rec_c_msg;
61 IF c_msg%FOUND THEN
62 p_n_msg_num := rec_c_msg.message_number;
63 p_c_msg_txt := rec_c_msg.message_text;
64 ELSE
65 p_c_msg_txt := p_c_msg_name;
66 END IF;
67 CLOSE c_msg;
68 END get_message;
69
70 /* Get message from Message Stack */
71
72 FUNCTION get_msg_from_stack(l_n_msg_count NUMBER) RETURN VARCHAR2 AS
73 l_c_msg VARCHAR2(3000);
74 l_c_msg_name fnd_new_messages.message_name%TYPE;
75 BEGIN
76 l_c_msg := FND_MSG_PUB.GET(p_msg_index => l_n_msg_count, p_encoded => 'T');
77 FND_MESSAGE.SET_ENCODED (l_c_msg);
78 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_appl_name, l_c_msg_name);
79 RETURN l_c_msg_name;
80 END get_msg_from_stack;
81
82 BEGIN
83
84
85 --Standard start of API savepoint
86 SAVEPOINT coa_segment_val_imp_pub;
87
88 --Standard call to check for call compatibility
89 IF NOT FND_API.Compatible_API_Call(l_api_version ,
90 p_api_version ,
91 l_api_name ,
92 G_PKG_NAME) THEN
93 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
94 END IF;
95
96 --Initialize message list if p_init_msg_list is set to TRUE
97 IF FND_API.to_Boolean(p_init_msg_list) THEN
98 FND_MSG_PUB.initialize;
99 END IF;
100
101 IF p_gl_flex_values_obj_tbl IS NOT NULL AND p_gl_flex_values_obj_tbl.COUNT > 0 THEN
102 FOR I in 1..p_gl_flex_values_obj_tbl.LAST LOOP
103 IF p_gl_flex_values_obj_tbl.EXISTS(I) THEN
104 l_gl_flex_values_tbl(I).value_set_name:= p_gl_flex_values_obj_tbl(I).value_set_name;
105 l_gl_flex_values_tbl(I).flex_value:= p_gl_flex_values_obj_tbl(I).flex_value;
106 l_gl_flex_values_tbl(I).flex_desc:= p_gl_flex_values_obj_tbl(I).flex_desc;
107 l_gl_flex_values_tbl(I).parent_flex_value:= p_gl_flex_values_obj_tbl(I).parent_flex_value;
108 l_gl_flex_values_tbl(I).summary_flag:= p_gl_flex_values_obj_tbl(I).summary_flag;
109 l_gl_flex_values_tbl(I).roll_up_group:= p_gl_flex_values_obj_tbl(I).roll_up_group;
110 l_gl_flex_values_tbl(I).hierarchy_level:= p_gl_flex_values_obj_tbl(I).hierarchy_level;
111 l_gl_flex_values_tbl(I).allow_budgeting:= p_gl_flex_values_obj_tbl(I).allow_budgeting;
112 l_gl_flex_values_tbl(I).allow_posting:= p_gl_flex_values_obj_tbl(I).allow_posting;
113 l_gl_flex_values_tbl(I).account_type:= p_gl_flex_values_obj_tbl(I).account_type;
114 l_gl_flex_values_tbl(I).reconcile:= p_gl_flex_values_obj_tbl(I).reconcile;
115 l_gl_flex_values_tbl(I).third_party_control_account:= p_gl_flex_values_obj_tbl(I).third_party_control_account;
116 l_gl_flex_values_tbl(I).enabled_flag:= p_gl_flex_values_obj_tbl(I).enabled_flag;
117 l_gl_flex_values_tbl(I).effective_from:= p_gl_flex_values_obj_tbl(I).effective_from;
118 l_gl_flex_values_tbl(I).effective_to:= p_gl_flex_values_obj_tbl(I).effective_to;
119 END IF;
120 END LOOP;
121 END IF;
122
123
124 IF p_gl_flex_values_nh_obj_tbl IS NOT NULL AND p_gl_flex_values_nh_obj_tbl.COUNT > 0 THEN
125 FOR I in 1..p_gl_flex_values_nh_obj_tbl.LAST LOOP
126 IF p_gl_flex_values_nh_obj_tbl.EXISTS(I) THEN
127 l_gl_flex_values_nh_tbl(I).value_set_name := p_gl_flex_values_nh_obj_tbl(I).value_set_name;
128 l_gl_flex_values_nh_tbl(I).parent_flex_value := p_gl_flex_values_nh_obj_tbl(I).parent_flex_value;
129 l_gl_flex_values_nh_tbl(I).range_attribute := p_gl_flex_values_nh_obj_tbl(I).range_attribute;
130 l_gl_flex_values_nh_tbl(I).child_flex_value_low := p_gl_flex_values_nh_obj_tbl(I).child_flex_value_low;
131 l_gl_flex_values_nh_tbl(I).child_flex_value_high := p_gl_flex_values_nh_obj_tbl(I).child_flex_value_high;
132 END IF;
133 END LOOP;
134 END IF;
135
136 --API body
137 --Call the COA Segment Values import Private API
138 gl_coa_segment_val_pvt.coa_segment_val_imp
139 (
140 p_api_version => p_api_version,
141 p_init_msg_list => p_init_msg_list,
142 p_commit => p_commit,
143 p_validation_level => p_validation_level,
144 x_return_status => x_return_status,
145 x_msg_count => x_msg_count,
146 x_msg_data => x_msg_data,
147 p_gl_flex_values_tbl => l_gl_flex_values_tbl,
148 p_gl_flex_values_nh_tbl => l_gl_flex_values_nh_tbl,
149 p_gl_flex_values_status => p_gl_flex_values_status,
150 p_gl_flex_values_nh_status => p_gl_flex_values_nh_status
151 );
152
153 IF l_gl_flex_values_tbl.COUNT > 0 THEN
154 FOR I in 1..l_gl_flex_values_tbl.LAST LOOP
155 IF l_gl_flex_values_tbl.EXISTS(I) THEN
156 p_gl_flex_values_obj_tbl(I).status := l_gl_flex_values_tbl(I).status;
157 END IF;
158 END LOOP;
159 END IF;
160
161 IF l_gl_flex_values_nh_tbl.COUNT > 0 THEN
162 FOR I in 1..l_gl_flex_values_nh_tbl.LAST LOOP
163 IF l_gl_flex_values_nh_tbl.EXISTS(I) THEN
164 p_gl_flex_values_nh_obj_tbl(I).status := l_gl_flex_values_nh_tbl(I).status;
165 END IF;
166 END LOOP;
167 END IF;
168
169 --Populate the x_msg_data
170 --get the error messages from stack for l_gl_flex_values_tbl
171 IF x_return_status = 'E' THEN
172 l_err_msg:= NULL;
173 x_msg_data := x_msg_data || 'Start of error messages for Import of Segment values'||FND_GLOBAL.newline;
174 IF l_gl_flex_values_tbl.COUNT > 0 THEN
175 FOR i IN 1..l_gl_flex_values_tbl.LAST
176 LOOP
177 IF l_gl_flex_values_tbl.EXISTS(I) THEN
178 IF l_gl_flex_values_tbl(i).status = 'E' THEN
179 l_err_msg := l_err_msg || 'Error for '|| l_gl_flex_values_tbl(I).value_set_name ||
180 'AND' || l_gl_flex_values_tbl(I).flex_value || FND_GLOBAL.newline;
181 FOR l_curr_num IN l_gl_flex_values_tbl(i).msg_from..l_gl_flex_values_tbl(i).msg_to
182 LOOP
183 l_c_msg_name := get_msg_from_stack(l_curr_num);
184 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
185 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
186 l_err_msg := l_err_msg || 'Message No. '|| I ||' ' || l_c_msg_txt;
187 IF length(l_err_msg) + length(x_msg_data) < 32000 THEN
188 x_msg_data := x_msg_data ||l_err_msg||FND_GLOBAL.newline;
189 END IF;
190 END LOOP;
191 l_err_msg:= NULL;
192 END IF;
193 END IF;
194 END LOOP;
195 END IF;
196 END IF;
197
198 --get the error messages from stack for l_gl_flex_values_nh_tbl
199 IF x_return_status = 'E' THEN
200 l_err_msg:= NULL;
201 x_msg_data := x_msg_data || 'Start of error messages for Import of child ranges for parent values'||FND_GLOBAL.newline;
202 IF l_gl_flex_values_nh_tbl.COUNT > 0 THEN
203 FOR i IN 1..l_gl_flex_values_nh_tbl.LAST
204 LOOP
205 IF l_gl_flex_values_tbl.EXISTS(I) THEN
206 IF l_gl_flex_values_nh_tbl(i).status = 'E' THEN
207 l_err_msg := l_err_msg || 'Error for '|| l_gl_flex_values_nh_tbl(I).value_set_name ||
208 'AND '|| l_gl_flex_values_nh_tbl(I).parent_flex_value ||
209 'AND '|| l_gl_flex_values_nh_tbl(I).range_attribute ||
210 'AND '|| l_gl_flex_values_nh_tbl(I).child_flex_value_low ||
211 'AND '|| l_gl_flex_values_nh_tbl(I).child_flex_value_high || FND_GLOBAL.newline;
212 FOR l_curr_num IN l_gl_flex_values_nh_tbl(i).msg_from..l_gl_flex_values_nh_tbl(i).msg_to
213 LOOP
214 l_c_msg_name := get_msg_from_stack(l_curr_num);
215 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
216 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
217 l_err_msg := l_err_msg || 'Message No. '|| I ||' ' || l_c_msg_txt;
218 IF length(l_err_msg) + length(x_msg_data) < 32000 THEN
219 x_msg_data := x_msg_data ||l_err_msg||FND_GLOBAL.newline;
220 END IF;
221 END LOOP;
222 l_err_msg:= NULL;
223 END IF;
224 END IF;
225 END LOOP;
226 END IF;
227 END IF;
228
229 l_user_id := fnd_global.user_id;
230 l_resp_id := fnd_global.resp_id;
231 l_apps_id := fnd_global.resp_appl_id;
232 fnd_global.apps_initialize (l_user_id, l_resp_id, l_apps_id);
233 fnd_profile.get('GL_ACCESS_SET_ID', l_acc_set_id);
234 --Submit the Request for "Program - Inherit Segment Value Attributes"
235 l_req_id :=fnd_request.submit_request('SQLGL', 'GLNSVI', '', '', FALSE,l_acc_set_id,'Y',
236 CHR(0), '', '', '', '', '', '',
237 '', '', '', '', '', '', '', '', '', '',
238 '', '', '', '', '', '', '', '', '', '',
239 '', '', '', '', '', '', '', '', '', '',
240 '', '', '', '', '', '', '', '', '', '',
241 '', '', '', '', '', '', '', '', '', '',
242 '', '', '', '', '', '', '', '', '', '',
243 '', '', '', '', '', '', '', '', '', '',
244 '', '', '', '', '', '', '', '', '', '',
245 '', '', '', '', '', '', '', '', '', '',
246 '');
247 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
248 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pub.coa_segment_val_imp.Triggered_GLNSVI',
249 'l_acc_set_id:'||TO_CHAR(l_acc_set_id)||' '||'Request id:'||l_req_id);
250 END IF;
251
252 IF (l_req_id = 0) THEN
253 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
254 fnd_log.string( fnd_log.level_statement, 'gl.plsql.gl_coa_seg_val_imp_pub.coa_segment_val_imp.Error_in_GLNSVI',
255 'l_acc_set_id:'||TO_CHAR(l_acc_set_id)||' '||'Error_Message:'||FND_MESSAGE.GET);
256 END IF;
257 END IF;
258
259 EXCEPTION
260 WHEN FND_API.G_EXC_ERROR THEN
261 ROLLBACK TO coa_segment_val_imp_pub;
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
264 p_data => x_msg_data );
265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
266 ROLLBACK TO coa_segment_val_imp_pub;
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
269 p_data => x_msg_data );
270 WHEN OTHERS THEN
271 ROLLBACK TO coa_segment_val_imp_pub;
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
274 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
275 l_api_name);
276 END IF;
277 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,
278 p_data => x_msg_data );
279
280 END coa_segment_val_imp;
281
282 END gl_coa_seg_val_imp_pub;