[Home] [Help]
PACKAGE BODY: APPS.GL_COA_SVIM_CONC_PKG
Source
1 PACKAGE BODY gl_coa_svim_conc_pkg AS
2 /* $Header: GLSVICOB.pls 120.1.12010000.1 2009/12/16 11:50:41 sommukhe noship $ */
3 /******************************************************************
4 Created By :Somnath Mukherjee
5 Date Created By : 01-AUG-2008
6 Purpose :
7 Known limitations,
8 enhancements,
9 remarks :
10 Change History
11 Who When What
12 ******************************************************************/
13
14 PROCEDURE gl_coa_svim_process(
15 errbuf OUT NOCOPY VARCHAR2,
16 retcode OUT NOCOPY NUMBER,
17 p_batch_number IN VARCHAR2
18 )
19 AS
20 /**********************************************************
21 Created By : sommukhe
22 Date Created By : 01-AUG-2008
23 Purpose : For COA Segment values import
24 Know limitations, enhancements or remarks
25
26 Change History
27 Who When What
28 (reverse chronological order - newest change first)
29 ***************************************************************/
30
31 -- Fnd Flex val Interface Table cursor
32
33 CURSOR c_gl_flex_values IS
34 SELECT *
35 FROM GL_IMP_COA_SEG_VAL_INTERFACE
36 WHERE BATCH_NUMBER = p_batch_number
37 AND STATUS = 'N'
38 ORDER BY SEG_VAL_INT_ID;
39
40 -- Fnd Flex Val Interface Table cursor
41
42 CURSOR c_gl_flex_values_nh IS
43 SELECT *
44 FROM GL_IMP_COA_NORM_HIER_INTERFACE
45 WHERE BATCH_NUMBER = p_batch_number
46 AND STATUS = 'N'
47 ORDER BY NORM_HIER_INT_ID;
48
49
50
51 -- To collect the statistics of the interface tables
52 TYPE tabnames IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
53 tablenames_tbl tabnames;
54
55
56 l_n_chld_cntr NUMBER(7);
57 l_c_return_status VARCHAR2(1);
58 l_n_msg_count NUMBER(10);
59 l_c_msg_data VARCHAR2(2000);
60 l_n_msg_num fnd_new_messages.message_number%TYPE;
61 l_c_msg_txt fnd_new_messages.message_text%TYPE;
62 l_c_msg_name fnd_new_messages.message_name%TYPE;
63 l_appl_name VARCHAR2(30);
64
65
66 l_n_request_id NUMBER(15,0);
67 l_n_prog_appl_id NUMBER(15,0);
68 l_n_prog_id NUMBER(15,0);
69 l_d_prog_upd_dt DATE;
70 p_head BOOLEAN ;
71 l_ret_status BOOLEAN ; -- Holds return status, TRUE if all the attempted records to import result in Error.
72
73 l_b_print_row_heading BOOLEAN ; -- Use for logging the row_head
74
75 v_gl_flex_values_tbl gl_coa_seg_val_imp_pub.gl_flex_values_tbl_type;
76 v_gl_flex_values_nh_tbl gl_coa_seg_val_imp_pub.gl_flex_values_nh_tbl_type;
77
78 l_gl_flex_values_status VARCHAR2(1);
79 l_gl_flex_values_nh_status VARCHAR2(1);
80
81
82 /* Procedure to get messages */
83 PROCEDURE get_message(p_c_msg_name VARCHAR2,p_n_msg_num OUT NOCOPY NUMBER,p_c_msg_txt OUT NOCOPY VARCHAR2) AS
84
85 CURSOR c_msg(cp_c_msg_name fnd_new_messages.message_name%TYPE ) IS
86 SELECT
87 message_number,
88 message_text
89 FROM fnd_new_messages
90 WHERE application_id=101
91 AND language_code = USERENV('LANG')
92 AND message_name=cp_c_msg_name;
93
94 rec_c_msg c_msg%ROWTYPE;
95 BEGIN
96 OPEN c_msg(p_c_msg_name);
97 FETCH c_msg INTO rec_c_msg;
98 IF c_msg%FOUND THEN
99 p_n_msg_num := rec_c_msg.message_number;
100 p_c_msg_txt := rec_c_msg.message_text;
101 ELSE
102 p_c_msg_txt := p_c_msg_name;
103 END IF;
104 CLOSE c_msg;
105 END get_message;
106
107 /* Procedure to write log file */
108 PROCEDURE log_file(p_c_text VARCHAR2,p_c_type VARCHAR2) AS
109 /* different types are P -> fnd_file.put, L-> fnd_file.put_line,N -> fnd_file.new_line */
110 BEGIN
111
112 IF p_c_type = 'P' THEN
113 fnd_file.put(fnd_file.log,p_c_text);
114 ELSIF p_c_type = 'L' THEN
115 fnd_file.put_line(fnd_file.log,p_c_text);
116 ELSIF p_c_type = 'N' THEN
117 fnd_file.new_line(fnd_file.log);
118 END IF;
119 END log_file;
120
121 /* Procedure to char - n times */
122 PROCEDURE print_char(p_n_count NUMBER,p_c_char VARCHAR2) AS
123 BEGIN
124 FOR I IN 1..p_n_count
125 LOOP
126 log_file(p_c_char,'P');
127 END LOOP;
128 END print_char;
129
130 /* Get message from Message Stack */
131
132 FUNCTION get_msg_from_stack(l_n_msg_count NUMBER) RETURN VARCHAR2 AS
133 l_c_msg VARCHAR2(3000);
134 l_c_msg_name fnd_new_messages.message_name%TYPE;
135 BEGIN
136 l_c_msg := FND_MSG_PUB.GET(p_msg_index => l_n_msg_count, p_encoded => 'T');
137 FND_MESSAGE.SET_ENCODED (l_c_msg);
138 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_appl_name, l_c_msg_name);
139 RETURN l_c_msg_name;
140 END get_msg_from_stack;
141
142
143
144
145 BEGIN /* Main Begin */
146 -- Initializing the values
147 p_head := TRUE;
148 l_ret_status := TRUE; -- Holds return status, TRUE if the attempted records to import result in Error.
149 l_b_print_row_heading := TRUE; -- Use for logging the row_head
150
151 -- Set the default status as success
152 retcode := 0;
153
154 /* Setting concurrent program values */
155
156 l_n_request_id := fnd_global.conc_request_id;
157 l_n_prog_appl_id := fnd_global.prog_appl_id;
158 l_n_prog_id := fnd_global.conc_program_id;
159 l_d_prog_upd_dt := SYSDATE;
160 IF l_n_request_id = -1 THEN
161 l_n_request_id := NULL;
162 l_n_prog_appl_id := NULL;
163 l_n_prog_id := NULL;
164 l_d_prog_upd_dt := NULL;
165 END IF;
166
167
168 /******************Begin Fnd Flex Val **********************/
169
170 l_n_chld_cntr :=1;
171
172 FOR rec_c_gl_flex_values IN c_gl_flex_values
173 LOOP
174
175 --print_heading;
176
177 v_gl_flex_values_tbl(l_n_chld_cntr).value_set_name := rec_c_gl_flex_values.value_set_name;
178 v_gl_flex_values_tbl(l_n_chld_cntr).flex_value := rec_c_gl_flex_values.flex_value;
179 v_gl_flex_values_tbl(l_n_chld_cntr).flex_desc := rec_c_gl_flex_values.flex_desc;
180 v_gl_flex_values_tbl(l_n_chld_cntr).parent_flex_value := rec_c_gl_flex_values.parent_flex_value;
181 v_gl_flex_values_tbl(l_n_chld_cntr).summary_flag := rec_c_gl_flex_values.summary_flag;
182 v_gl_flex_values_tbl(l_n_chld_cntr).roll_up_group := rec_c_gl_flex_values.roll_up_group;
183 v_gl_flex_values_tbl(l_n_chld_cntr).hierarchy_level := rec_c_gl_flex_values.hierarchy_level;
184 v_gl_flex_values_tbl(l_n_chld_cntr).allow_budgeting := rec_c_gl_flex_values.allow_budgeting;
185 v_gl_flex_values_tbl(l_n_chld_cntr).allow_posting := rec_c_gl_flex_values.allow_posting;
186 v_gl_flex_values_tbl(l_n_chld_cntr).account_type := rec_c_gl_flex_values.account_type;
187 v_gl_flex_values_tbl(l_n_chld_cntr).reconcile := rec_c_gl_flex_values.reconcile;
188 v_gl_flex_values_tbl(l_n_chld_cntr).third_party_control_account := rec_c_gl_flex_values.third_party_control_account;
189 v_gl_flex_values_tbl(l_n_chld_cntr).enabled_flag := rec_c_gl_flex_values.enabled_flag;
190 v_gl_flex_values_tbl(l_n_chld_cntr).effective_from := rec_c_gl_flex_values.effective_from;
191 v_gl_flex_values_tbl(l_n_chld_cntr).effective_to := rec_c_gl_flex_values.effective_to;
192 v_gl_flex_values_tbl(l_n_chld_cntr).interface_id := rec_c_gl_flex_values.seg_val_int_id;
193
194
195 l_n_chld_cntr := l_n_chld_cntr+1;
196
197 END LOOP;
198
199 /******************End Fnd Flex Val **********************/
200
201
202 /******************Begin Fnd Flex Val norm Hierarchy******/
203
204 l_n_chld_cntr :=1;
205
206 FOR rec_c_gl_flex_values_nh IN c_gl_flex_values_nh
207 LOOP
208
209 --print_heading;
210
211 v_gl_flex_values_nh_tbl(l_n_chld_cntr).value_set_name := rec_c_gl_flex_values_nh.value_set_name;
212 v_gl_flex_values_nh_tbl(l_n_chld_cntr).parent_flex_value := rec_c_gl_flex_values_nh.parent_flex_value;
213 v_gl_flex_values_nh_tbl(l_n_chld_cntr).range_attribute := rec_c_gl_flex_values_nh.range_attribute;
214 v_gl_flex_values_nh_tbl(l_n_chld_cntr).child_flex_value_low := rec_c_gl_flex_values_nh.child_flex_value_low;
215 v_gl_flex_values_nh_tbl(l_n_chld_cntr).child_flex_value_high := rec_c_gl_flex_values_nh.child_flex_value_high;
216 v_gl_flex_values_nh_tbl(l_n_chld_cntr).interface_id := rec_c_gl_flex_values_nh.norm_hier_int_id;
217
218 l_n_chld_cntr := l_n_chld_cntr+1;
219
220 END LOOP;
221
222 /******************End Fnd Flex Val norm Hierarchy**********************/
223
224
225
226 /* Call to Private API */
227
228 gl_coa_segment_val_pvt.coa_segment_val_imp
229 (
230 p_api_version => 1.0,
231 p_init_msg_list => FND_API.G_TRUE,
232 p_commit => FND_API.G_TRUE,
233 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
234 x_return_status => l_c_return_status,
235 x_msg_count => l_n_msg_count,
236 x_msg_data => l_c_msg_data,
237 p_gl_flex_values_tbl => v_gl_flex_values_tbl,
238 p_gl_flex_values_nh_tbl => v_gl_flex_values_nh_tbl,
239 p_gl_flex_values_status => l_gl_flex_values_status,
240 p_gl_flex_values_nh_status => l_gl_flex_values_nh_status
241 );
242 /* -----------------------------*/
243
244 /* Error out if none of the tables have data */
245
246 IF l_n_msg_count = 1 THEN
247 IF get_msg_from_stack(l_n_msg_count) = 'GL_COA_DATA_NOT_PASSED' THEN
248 get_message('GL_COA_SVI_DATA_NOT_PASSED',l_n_msg_num,l_c_msg_txt);
249 log_file(l_c_msg_txt,'L');
250 retcode := 2;
251 RETURN;
252 END IF;
253 END IF;
254
255 IF l_ret_status AND l_c_return_status = 'S' THEN
256 l_ret_status := FALSE;
257 END IF;
258
259 /* -----------------------------------------------*/
260
261
262 /******************Begin Fnd Flex Val Log and Error**********************/
263 l_b_print_row_heading := TRUE;
264 IF v_gl_flex_values_tbl.EXISTS(1) THEN
265
266
267 FOR i IN 1..v_gl_flex_values_tbl.LAST
268 LOOP
269 /* Update the interface table with the status*/
270
271 UPDATE GL_IMP_COA_SEG_VAL_INTERFACE
272 SET status = v_gl_flex_values_tbl(i).status
273 WHERE batch_number=p_batch_number
274 AND seg_val_int_id = v_gl_flex_values_tbl(i).interface_id;
275
276 IF v_gl_flex_values_tbl(i).status = 'S' THEN
277 /* Write into log file */
278 NULL;
279 ELSIF v_gl_flex_values_tbl(i).status = 'E' THEN
280 NULL;
281 /* Write into log file */
282 FOR l_curr_num IN v_gl_flex_values_tbl(i).msg_from..v_gl_flex_values_tbl(i).msg_to
283 LOOP
284 l_c_msg_name := get_msg_from_stack(l_curr_num);
285 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
286 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
287
288 INSERT INTO GL_IMP_COA_ERR_INTERFACE
289 (
290 err_message_id,
291 int_table_name,
292 interface_id,
293 message_num,
294 message_text,
295 created_by,
296 creation_date,
297 last_updated_by,
298 last_update_date,
299 last_update_login,
300 request_id,
301 program_application_id,
302 program_id,
303 program_update_date)
304 VALUES
305 (
306 gl_imp_coa_err_interface_s.nextval,
307 'GL_IMP_COA_SEG_VAL_INTERFACE',
308 v_gl_flex_values_tbl(i).interface_id,
309 l_n_msg_num,
310 l_c_msg_txt,
311 NVL(fnd_global.user_id,-1),
312 SYSDATE,
313 NVL(fnd_global.user_id,-1),
314 SYSDATE,
315 NVL(fnd_global.login_id,-1),
316 l_n_request_id,
317 l_n_prog_appl_id,
318 l_n_prog_id,
319 l_d_prog_upd_dt
320 );
321 /* Write into log file */
322 log_file(RPAD(l_n_msg_num,15,' '),'P');
323 IF l_n_msg_num IS NULL THEN
324 print_char(15,' ');
325 END IF;
326 print_char(1,' ');
327 log_file(l_c_msg_txt,'L');
328
329 END LOOP; /* Messages loop */
330 END IF;
331 END LOOP; /* */
332 v_gl_flex_values_tbl.DELETE;
333 END IF;
334
335 /******************End Fnd Flex Val Log and Error***********************/
336
337 /******************Begin Fnd Flex Val Norm Hier Log and Error***********/
338 l_b_print_row_heading := TRUE;
339 IF v_gl_flex_values_nh_tbl.EXISTS(1) THEN
340
341
342 FOR i IN 1..v_gl_flex_values_nh_tbl.LAST
343 LOOP
344 /* Update the interface table with the status*/
345
346 UPDATE GL_IMP_COA_NORM_HIER_INTERFACE
347 SET status = v_gl_flex_values_nh_tbl(i).status
348 WHERE batch_number=p_batch_number
349 AND norm_hier_int_id = v_gl_flex_values_nh_tbl(i).interface_id;
350
351
352 IF v_gl_flex_values_nh_tbl(i).status = 'S' THEN
353 /* Write into log file */
354 NULL;
355
356 ELSIF v_gl_flex_values_nh_tbl(i).status = 'E' THEN
357 NULL;
358
359 /* Write into log file */
360 FOR l_curr_num IN v_gl_flex_values_nh_tbl(i).msg_from..v_gl_flex_values_nh_tbl(i).msg_to
361 LOOP
362 l_c_msg_name := get_msg_from_stack(l_curr_num);
363 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
364 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
365
366
367 INSERT INTO GL_IMP_COA_ERR_INTERFACE
368 (
369 err_message_id,
370 int_table_name,
371 interface_id,
372 message_num,
373 message_text,
374 created_by,
375 creation_date,
376 last_updated_by,
377 last_update_date,
378 last_update_login,
379 request_id,
380 program_application_id,
381 program_id,
382 program_update_date)
383 VALUES
384 (
385 gl_imp_coa_err_interface_s.nextval,
386 'GL_IMP_COA_NORM_HIER_INTERFACE',
387 v_gl_flex_values_nh_tbl(i).interface_id,
388 l_n_msg_num,
389 l_c_msg_txt,
390 NVL(fnd_global.user_id,-1),
391 SYSDATE,
392 NVL(fnd_global.user_id,-1),
393 SYSDATE,
394 NVL(fnd_global.login_id,-1),
395 l_n_request_id,
396 l_n_prog_appl_id,
397 l_n_prog_id,
398 l_d_prog_upd_dt
399 );
400
401 /* Write into log file */
402 log_file(RPAD(l_n_msg_num,15,' '),'P');
403 IF l_n_msg_num IS NULL THEN
404 print_char(15,' ');
405 END IF;
406 print_char(1,' ');
407 log_file(l_c_msg_txt,'L');
408
409 END LOOP; /* Messages loop */
410 END IF;
411 END LOOP; /* */
412 v_gl_flex_values_nh_tbl.DELETE;
413 END IF;
414
415 /******************End Fnd Flex Val Norm Hier Log and Error***********************/
416
417 /* Delete successfully imported records */
418 /* Delete from gl_imp_coa_seg_val_interface Interface Table */
419 DELETE FROM gl_imp_coa_seg_val_interface
420 WHERE status = 'P';
421
422 /* Delete from gl_imp_coa_norm_hier_interface Interface Table */
423 DELETE FROM gl_imp_coa_norm_hier_interface
424 WHERE status = 'P';
425
426
427 /* If none of the interface tables has not appropriate data that is to be processed, then set the message and error out */
428 IF l_c_return_status IS NULL THEN
429 get_message('GL_COA_SVI_DATA_NOT_PASSED',l_n_msg_num,l_c_msg_txt);
430 log_file(l_c_msg_txt,'L');
431 retcode := 2;
432 RETURN;
433 ELSE
434 print_char(80,'=');
435 END IF;
436
437 -- Set the concurrent program status to Error if the API return status is Error for all the attempted records
438 COMMIT WORK;
439 IF l_ret_status THEN
440 retcode:=2;
441 END IF;
442
443 /*Raise the Import process Completion Business event*/
444 gl_business_events.raise(
445 p_event_name =>'oracle.apps.gl.ChartOfAccounts.SegmentValues.completeImport',
446 p_event_key => 'The Chart of Accounts Segment Values Import Program is completed',
447 p_parameter_name1 => 'BATCH_NUMBER',
448 p_parameter_value1 => p_batch_number,
449 p_parameter_name2 => 'COMPLETION_STATUS',
450 p_parameter_value2 => l_c_return_status);
451
452
453 -- End of Procedure
454 EXCEPTION
455 WHEN OTHERS THEN
456 ROLLBACK;
457 retcode:=2;
458 fnd_file.put_line(fnd_file.log,sqlerrm);
459 errbuf := fnd_message.get_string('SQLGL','GL_COA_SVI_UNH_EXCEPTION') ;
460 gl_business_events.raise(
461 p_event_name =>'oracle.apps.gl.ChartOfAccounts.SegmentValues.completeImport',
462 p_event_key => 'The Chart of Accounts Segment Values Import Program is completed',
463 p_parameter_name1 => 'BATCH_NUMBER',
464 p_parameter_value1 => p_batch_number,
465 p_parameter_name2 => 'EXCEPTION',
466 p_parameter_value2 => sqlerrm);
467
468 END gl_coa_svim_process;
469
470 END gl_coa_svim_conc_pkg;