[Home] [Help]
PACKAGE BODY: APPS.GL_CI_DATA_TRANSFER_PKG
Source
1 PACKAGE BODY GL_CI_DATA_TRANSFER_PKG as
2 /* $Header: glucitrb.pls 120.11 2005/12/08 10:32:18 mikeward noship $ */
3 own CONSTANT varchar2(30) := 'GL';
4 appSchema CONSTANT varchar2(30) := 'APPS';
5 EMAIL_CONTACT_NOT_SET number := -5;
6 CONTACT_INFO_NOT_FOUND Number := -4;
7 nl CONSTANT varchar2(1) := fnd_global.local_chr(10);
8 j_import_menu_name varchar2(30) := 'GL_SU_J_IMPORT';
9 j_post_menu_name varchar2(30) := 'GL_SU_JOURNAL';
10 i_parallel_name varchar2(30) := 'GL_CONS_INTERFACE_';
11 TYPE t_RefCur IS REF CURSOR;
12 GLOBAL_ERROR exception; --+something is wrong - general handler
13 --+ Tell me what's wrong
14 global_retcode number;
15 global_errbuf varchar2(2000);
16 section_number number(5); --+where did I go wrong?
17 applSysSchema varchar2(30); --+bug#2630145, get applsys schema name
18 --+don't hardcoded the apps schema name
19 domainName varchar2(150); --+ bug fix for bug#2712006
20 --+ a place to keep COA attributes
21 chart gl_ci_remote_invoke_pkg.coa_table; --+ holds coa attributes
22 remote_chart gl_ci_remote_invoke_pkg.coa_table; --+ holds coa attributes
23 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
24 --+ Debug/Diagnostic routine
25 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
26 procedure debug_message (
27 message_string in varchar2 ,
28 verbose_mode in boolean default FALSE,
29 line_size in number default 255 ,
30 which_file in varchar2 default FND_FILE.log
31 ) is
32 msg_text varchar2(10000);
33 new_line number;
34 begin
35 msg_text := substr(message_string, 1, 32000);
36 new_line := 0;
37 if verbose_mode then
38 if message_string = fnd_global.local_chr(10) then
39 FND_FILE.put(which_file, message_string);
40 else
41 while msg_text is not null loop
42 new_line := instr(msg_text, fnd_global.local_chr(10));
43 if new_line = 0 then new_line := line_size;
44 elsif new_line > line_size then new_line := line_size;
45 end if;
46 --DBMS_OUTPUT.put_line( substr(msg_text, 1, new_line-1) );
47 FND_FILE.put_line(which_file, substr(msg_text, 1, new_line-1));
48 msg_text := substr(msg_text, new_line+1);
49 end loop;
50 end if;
51 end if;
52 exception
53 when OTHERS then
54 if SQLCODE = 1 then
55 --+ Raised if you call FND_FILE from a SQL*Plus session
56 --+ without initializing user_id, resp_id, login_resp_id.
57 --+ dbms_output.enable(1000000);
58 while msg_text is not null loop
59 --+can not use chr(10) any more, can not use dbms_output any more
60 new_line := instr(msg_text, fnd_global.local_chr(10));
61 if new_line = 0 then new_line := line_size;
62 elsif new_line > line_size then new_line := line_size;
63 end if;
64 raise_application_error(-20000, substr(msg_text, 1, new_line-1));
65 --+DBMS_OUTPUT.put_line( substr(msg_text, 1, new_line-1) );
66 msg_text := substr(msg_text, new_line+1);
67 end loop;
68 else
69 RAISE;
70 end if;
71 end debug_message;
72 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
73 --+ send log messages to log file routine
74 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
75 procedure log_message (
76 message_string in varchar2 ,
77 verbose_mode in boolean default FALSE,
78 line_size in number default 255 ,
79 which_file in varchar2 default FND_FILE.log
80 ) is
81 msg_text varchar2(10000);
82 new_line number;
83 begin
84 msg_text := substr(message_string, 1, 32000);
85 new_line := 0;
86 if verbose_mode then
87 if message_string = fnd_global.local_chr(10) then
88 FND_FILE.put(which_file, message_string);
89 else
90 while msg_text is not null loop
91 new_line := instr(msg_text, fnd_global.local_chr(10));
92 if new_line = 0 then new_line := line_size;
93 elsif new_line > line_size then new_line := line_size;
94 end if;
95 --DBMS_OUTPUT.put_line( substr(msg_text, 1, new_line-1) );
96 FND_FILE.put_line(which_file, substr(msg_text, 1, new_line-1));
97 msg_text := substr(msg_text, new_line+1);
98 end loop;
99 end if;
100 end if;
101 exception
102 when OTHERS then
103 if SQLCODE = 1 then
104 --+ Raised if you call FND_FILE from a SQL*Plus session
105 --+ without initializing user_id, resp_id, login_resp_id.
106 --+ dbms_output.enable(1000000);
107 while msg_text is not null loop
108 --+can not use chr(10) any more, can not use dbms_output any more
109 new_line := instr(msg_text, fnd_global.local_chr(10));
110 if new_line = 0 then new_line := line_size;
111 elsif new_line > line_size then new_line := line_size;
112 end if;
113 raise_application_error(-20000, substr(msg_text, 1, new_line-1));
114 --+DBMS_OUTPUT.put_line( substr(msg_text, 1, new_line-1) );
115 msg_text := substr(msg_text, new_line+1);
116 end loop;
117 else
118 RAISE;
119 end if;
120 end log_message;
121 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
122 --+ Debug/Diagnostic routine
123 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
124 procedure DD (
125 message_string in varchar2,
126 mode_override in boolean default TRUE,
127 line_size in number default 255,
128 which_file in varchar2 default FND_FILE.log
129 ) is
130 begin
131 if mode_override then
132 debug_message(message_string, TRUE, line_size, which_file);
133 end if;
134 end DD;
135 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
136 --+ to fix bug#2630145, do not hardcoded the apps schema name
137 --+ it could be anything at customer's site
138 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
139 PROCEDURE Get_Schema_Name (l_dblink IN varchar2)
140 is
141 dummy1 varchar2(30);
142 dummy2 varchar2(30);
143 result varchar2(150);
144 v_SQL varchar2(500);
145 l_flag varchar2(1);
146 l_oracle_id number;
147 l_return BOOLEAN; -- for some reason, this can't be boolean when
148 -- it is a returned value from native dynamic SQL
149 l_apps_short_name varchar2(30);
150 --FND_INSTALLATION.GET_APP_INFO returns a boolean, which is not a SQL type.
151 --I can't compile the code if l-return is declared as a boolean
152 --I can't execute the code if i omit the returned value.
153 --I can't execute the code if I declare the l_return as varchar2(n) either.
154 --I have to go back to use my original method of getting the schema name.
155 BEGIN
156 -- l_apps_short_name := 'FND';
157 -- v_SQL := 'BEGIN ' || ':rcode := fnd_installation.get_app_info@' || l_dblink ||
158 -- '(:1, :2, :3, :4);' || ' END;';
159 -- EXECUTE IMMEDIATE v_SQL USING OUT l_return,IN l_apps_short_name, OUT dummy1, OUT dummy2, OUT applSysSchema;
160 l_oracle_id := 900;
161 l_flag := 'U';
162 v_SQL := 'select oracle_username from fnd_oracle_userid@' || l_dblink ||
163 ' where read_only_flag = :flag' ||
164 ' and oracle_id = :or_id';
165 EXECUTE IMMEDIATE v_SQL INTO applSysSchema USING l_flag, l_oracle_id;
166 IF (applSysSchema IS NULL) THEN
167 RAISE GLOBAL_ERROR;
168 END IF;
169 exception
170 when GLOBAL_ERROR then
171 rollback;
172 result := SUBSTR(SQLERRM, 1, 200);
173 FND_MESSAGE.set_name('SQLGL', 'gl_us_ci_others_fail');
174 FND_MESSAGE.set_token('RESULT', result);
175 app_exception.raise_exception;
176 END Get_Schema_Name;
177 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
178 --+bug#2712006, cannot use hardcoded domain name, get doamin name here.
179 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
180 PROCEDURE Get_Domain_Name (l_dblink IN varchar2)
181 is
182 result varchar2(500);
183 v_SQL varchar2(500);
184 BEGIN
185 v_SQL := 'select domain_name ' ||
186 'from rg_database_links ' ||
187 'where name = :pd';
188 EXECUTE IMMEDIATE v_SQL INTO domainName USING l_dblink;
189 IF (domainName IS NULL) THEN
190 RAISE GLOBAL_ERROR;
191 END IF;
192 exception
193 when GLOBAL_ERROR then
194 rollback;
195 result := SUBSTR(SQLERRM, 1, 200);
196 FND_MESSAGE.set_name('SQLGL', 'gl_us_ci_others_fail');
197 FND_MESSAGE.set_token('RESULT', result);
198 app_exception.raise_exception;
199 END Get_Domain_Name;
200 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
201 --+ this function is called in the GLXCORUN.fmb to get the remote instance
202 --+ data for a specific mapping rule from gl_consolidation_history table
203 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
204 procedure Remote_Data_Map(
205 p_name IN varchar2,
206 p_resp_name IN OUT NOCOPY varchar2,
207 p_user_name IN OUT NOCOPY varchar2,
208 p_db_name IN OUT NOCOPY varchar2
209 ) IS
210 -- We get the information for the last consolidation run only
211 CURSOR C IS
212 SELECT h.*
213 FROM gl_consolidation_history h,
214 gl_consolidation c
215 WHERE h.consolidation_id = c.consolidation_id
216 AND c.name = p_name
217 ORDER BY h.last_update_date DESC;
218 v_Cons gl_consolidation_history%ROWTYPE;
219 begin
220 OPEN C;
221 FETCH C INTO v_Cons;
222 if (C%FOUND) then
223 p_resp_name := v_Cons.Target_resp_name;
224 p_user_name := v_Cons.Target_user_name;
225 p_db_name := v_Cons.Target_database_name;
226 else
227 p_resp_name := null;
228 p_user_name := null;
229 p_db_name := null;
230 end if;
231 CLOSE C;
232 end Remote_Data_Map;
233 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
234 --+ this function is called in GLXCORST.fmb to get the remote instance data
235 --+ for a specific mapping set from gl_consolidation_history table
236 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
237 procedure Remote_Data_Map_Set(
238 p_name IN varchar2,
239 p_resp_name IN OUT NOCOPY varchar2,
240 p_user_name IN OUT NOCOPY varchar2,
241 p_db_name IN OUT NOCOPY varchar2
242 ) IS
243 -- We get the information for the last consolidation set run only
244 CURSOR C IS
245 SELECT h.*
246 FROM gl_consolidation_history h,
247 gl_consolidation_sets c
248 WHERE h.consolidation_set_id = c.consolidation_set_id
249 AND c.name = p_name
250 ORDER BY h.last_update_date DESC;
251 v_Cons gl_consolidation_history%ROWTYPE;
252 begin
253 OPEN C;
254 FETCH C INTO v_Cons;
255 if (C%FOUND) then
256 p_resp_name := v_Cons.Target_resp_name;
257 p_user_name := v_Cons.Target_user_name;
258 p_db_name := v_Cons.Target_database_name;
259 else
260 p_resp_name := null;
261 p_user_name := null;
262 p_db_name := null;
263 end if;
264 CLOSE C;
265 end Remote_Data_Map_Set;
266 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
267 --+ this function is called to get the ledger id from the target db
268 --+ access rights are also checked at this point.
269 --+ If the ledger is not granted read/write access right, then the returned
270 --+ ledger id is -1. An error message will be written to the log file.
271 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
272 function Get_Ledger_ID(
273 user_id IN number,
274 resp_id IN number,
275 app_id IN number,
276 dblink IN varchar2,
277 access_set_id OUT NOCOPY number,
278 access_set OUT NOCOPY varchar2,
279 access_code OUT NOCOPY varchar2
280 ) return number
281 IS
282 ledger_id number;
283 test VARCHAR2(1);
284 v_SQL varchar2(500);
285 l_temp_db varchar2(30);
286 begin
287 --still needs to get the apps schema name here, because this function
288 --can be called from many places.
289 l_temp_db := dblink || '.' || domainName;
290 Get_Schema_name(L_TEMP_DB); --+bug#2630145
291 --Use Native Dynamic SQL
292 v_SQL := 'BEGIN '||' :a := ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Get_Ledger_ID@' || l_temp_db
293 || '(:user_id, :resp_id, :app_id, :id, :access_set, :code)' ||';'||' END;';
294 EXECUTE IMMEDIATE v_SQL USING OUT ledger_id, IN user_id,
295 IN resp_id, IN app_id, OUT access_set_id, OUT access_set, OUT access_code;
296 return ledger_id;
297 end Get_Ledger_ID;
298 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
299 --+ this function is called to get the Budget Version id from the target db
300 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
301 function Get_Budget_Version_ID(
302 user_id IN number,
303 resp_id IN number,
304 app_id IN number,
305 dblink IN varchar2,
306 budget_name IN varchar2
307 ) return number
308 IS
309 budget_version_id number;
310 test VARCHAR2(1);
311 v_SQL varchar2(500);
312 begin
313 --Get_Schema_name(dblink); --+bug#2630145
314 --Use Native Dynamic SQL
315 v_SQL := 'BEGIN '||' :a := ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Get_Budget_Version_ID@' || dblink
316 || '(:user_id, :resp_id, :app_id, :budget_name)' ||';'||' END;';
317 EXECUTE IMMEDIATE v_SQL USING OUT budget_version_id, IN user_id, IN resp_id, IN app_id, IN budget_name;
318 return budget_version_id;
319 end Get_Budget_Version_ID;
320 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
321 --+ this function is called to move consolidation data from source to target db
322 --+ ORA-06512: at "GL_FND_REQUEST_PKG", line 205
323 --+ need to commit from the source not at target database
324 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
325 FUNCTION Remote_Data_transfer(
326 actual_flag IN varchar2,
327 user_id IN number,
328 resp_id IN number,
329 app_id IN number,
330 dblink IN varchar2,
331 source_ledger_id IN number,
332 pd_name IN varchar2,
333 budget_name IN varchar2,
334 group_id IN number,
335 request_id IN number,
336 p_dblink IN varchar2,
337 p_target_ledger_id IN number,
338 avg_flag IN OUT NOCOPY varchar2,
339 balanced_flag IN OUT NOCOPY varchar2,
340 errbuf IN OUT NOCOPY varchar2
341 ) RETURN NUMBER
342 IS
343 l_source_ledger_id number;
344 l_group_id number;
345 v_InsertSQL VARCHAR2(10000);
346 l_out_table_name varchar2(30);
347 l_in_table_name varchar2(30);
348 l_pd_name gl_interface.period_name%TYPE;
349 v_BlockSQL varchar2(300);
350 v_SQL varchar2(1000);
351 l_target_ledger_id number;
352 l_request_id number;
353 n number;
354 l_count number;
355 l_cons_status varchar2(50);
356 l_entered_dr number;
357 l_entered_cr number;
358 l_db_username varchar2(30);
359 l_target_budget_version_id number;
360 l_budget_count number;
361 l_user_je_source_name varchar2(25);
362 l_adb_je_source varchar2(25);
363 l_je_source varchar2(25);
364 l_access_set_name varchar2(30);
365 l_r_adb_je_source varchar2(25);
366 l_r_je_source varchar2(25);
367 l_target_je_source varchar2(25);
368 begin
369 l_adb_je_source := 'Average Consolidation';
370 l_je_source := 'Consolidation';
371 l_pd_name := pd_name;
372 l_source_ledger_id := source_ledger_id;
373 l_target_ledger_id := p_target_ledger_id;
374 --+for debug from SQL Navigator, run consolidation from the Oracle Applications without
375 --+Journal Import, use its group ID and consolidation concurrent request id to run this
376 --+procedure, make sure you get source data from gl_interface table
377 --+l_in_table_name := 'GL_INTERFACE';
378 l_request_id := request_id;
379 l_in_table_name := i_parallel_name || group_id;
380 v_SQL := 'BEGIN '||' :l_group_id := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_Group_ID@' || dblink||';'||' END;';
381 EXECUTE IMMEDIATE v_SQL USING OUT l_group_id;
382 COMMIT;
383 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_GROUP_ID');
384 FND_MESSAGE.set_token('GROUP', l_group_id);
385 log_message('==>' || FND_MESSAGE.get, TRUE);
386 v_SQL := 'select apps_username ' ||
387 'from rg_database_links ' ||
388 'where name = :pd';
389 EXECUTE IMMEDIATE v_SQL INTO l_db_username USING p_dblink;
390 v_BlockSQL := 'BEGIN '|| applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Create_Interface_Table@' || dblink||'(:l_group_id, :l_db_username);'||' END;';
391 EXECUTE IMMEDIATE v_BlockSQL USING IN l_group_id,l_db_username;
392 commit;
393 --+dbms_output.put_line('after table is created in the target ');
394 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_INTERFACE');
395 FND_MESSAGE.set_token('GROUP', l_group_id);
396 log_message('==>' || FND_MESSAGE.get, TRUE);
397 l_out_table_name := i_parallel_name || l_group_id;
398 if actual_flag = 'B' then
399 l_target_budget_version_id := Get_Budget_Version_id(user_id, resp_id, app_id, dblink,budget_name);
400 --bug fix #3095741, returns a meaningful error message when no matching budget is found in target
401 if l_target_budget_version_id = -100 then
402 errbuf := 'FAILED';
403 return -1;
404 end if;
405 end if;
406 --+ debug_message('Get target ledger id again' || l_target_ledger_id,TRUE);
407 --+check if the journal entry is balanced
408 v_SQL := 'select sum(round(entered_dr, 2)), sum(round(entered_cr, 2)) ' ||
409 'from ' || l_in_table_name;
410 EXECUTE IMMEDIATE v_SQL INTO l_entered_dr, l_entered_cr;
411 --bug fix#3095489, return balanced_flag is Y when there is no data found
412 --in the interface table. Need to take care of no data found properly.
413 v_SQL := 'select count(*) ' ||
414 'from ' || l_in_table_name;
415 EXECUTE IMMEDIATE v_SQL INTO l_budget_count;
416 if l_budget_count > 0 then
417 if (l_entered_dr = l_entered_cr) then
418 balanced_flag := 'Y';
419 else
420 balanced_flag := 'N';
421 end if;
422 else
423 errbuf := 'NO_DATA_FOUND';
424 return -1;
425 end if;
426 --+add this piece of code to decide if this is a ADB transfer or Balance transfer
427 v_SQL := 'select user_je_source_name from gl_je_sources ' ||
428 'WHERE je_source_name = :s_name';
429 EXECUTE IMMEDIATE v_SQL INTO l_user_je_source_name USING l_je_source;
430 v_SQL := 'select count(*) from '|| l_in_table_name ||
431 ' where period_name = :pd_name' ||
432 ' and request_id = :rq_id' ||
433 ' and actual_flag = :flag' ||
434 ' and user_je_source_name = :s_name';
435 EXECUTE IMMEDIATE v_SQL INTO l_count USING l_pd_name, l_request_id,
436 actual_flag, l_user_je_source_name;
437 --+dbms_output.put_line('select count form gl_cons_interface table ');
438 --+Bug#3433592, unable to get data when trying to do Journal Import in
439 --+the target db, because the user_je_source_name is different from the
440 --+one in the source db.
441 --+get the je source name from the target db. Insert this name
442 --+into the gl_cons_interface table for the target.
443 v_BlockSQL := 'BEGIN '|| applSysSchema ||
444 '.GL_CI_REMOTE_INVOKE_PKG.Get_Target_Je_source_Name@' || dblink||
445 '(:l_adb, :l_source);'||' END;';
446 EXECUTE IMMEDIATE v_BlockSQL USING OUT l_r_adb_je_source, OUT l_r_je_source;
447 if l_count > 0 then
448 avg_flag := 'N';
449 l_target_je_source := l_r_je_source;
450 else
451 v_SQL := 'select user_je_source_name from gl_je_sources ' ||
452 'WHERE je_source_name = :s_name';
453 EXECUTE IMMEDIATE v_SQL INTO l_user_je_source_name USING l_adb_je_source;
454 avg_flag := 'Y';
455 l_target_je_source := l_r_adb_je_source;
456 end if;
457 --+dbms_output.put_line('what is this avg flag set? ' || avg_flag);
458 v_InsertSQL := 'INSERT INTO ' || applSysSchema || '.' ||l_out_table_name ||'@' || dblink ||
459 ' (status, ledger_id,accounting_date,' ||
460 ' currency_code, date_created, created_by,' ||
461 ' actual_flag, user_je_category_name, user_je_source_name,' ||
462 ' currency_conversion_date, encumbrance_type_id,' ||
463 ' budget_version_id, user_currency_conversion_type,' ||
464 ' currency_conversion_rate, segment1, segment2,' ||
465 ' segment3, segment4, segment5, segment6,' ||
466 ' segment7, segment8, segment9, segment10,' ||
467 ' segment11, segment12, segment13,' ||
468 ' segment14, segment15, segment16,' ||
469 ' segment17, segment18, segment19,' ||
470 ' segment20, segment21, segment22,' ||
471 ' segment23, segment24, segment25,' ||
472 ' segment26, segment27, segment28,' ||
473 ' segment29, segment30, entered_dr,' ||
474 ' entered_cr, accounted_dr, accounted_cr,' ||
475 ' transaction_date, reference1, reference2,' ||
476 ' reference3, reference4, reference5,' ||
477 ' reference6, reference7, reference8,' ||
478 ' reference9, reference10, reference11,' ||
479 ' reference12, reference13, reference14,' ||
480 ' reference15, reference16, reference17,' ||
481 ' reference18, reference19, reference20,' ||
482 ' reference21, reference22, reference23,' ||
483 ' reference24, reference25, reference26,' ||
484 ' reference27, reference28, reference29,' ||
485 ' reference30, je_batch_id, period_name,' ||
486 ' je_header_id, je_line_num, chart_of_accounts_id,' ||
487 ' functional_currency_code,' ||
488 ' code_combination_id, date_created_in_gl,' ||
489 ' warning_code, status_description,' ||
490 ' stat_amount, group_id, request_id,' ||
491 ' subledger_doc_sequence_id, subledger_doc_sequence_value,' ||
492 ' attribute1, attribute2, attribute3,' ||
493 ' attribute4, attribute5, attribute6,' ||
494 ' attribute7, attribute8, attribute9,' ||
495 ' attribute10, attribute11, attribute12,' ||
496 ' attribute13, attribute14, attribute15,' ||
497 ' attribute16, attribute17, attribute18,' ||
498 ' attribute19, attribute20, context,' ||
499 ' context2, invoice_date, tax_code,' ||
500 ' invoice_identifier, invoice_amount,' ||
501 ' context3, ussgl_transaction_code,' ||
502 ' descr_flex_error_message, jgzz_recon_ref,' ||
503 ' average_journal_flag, originating_bal_seg_value,' ||
504 ' gl_sl_link_id, gl_sl_link_table,' ||
505 ' reference_date, balancing_segment_value, management_segment_value)' ||
506 ' SELECT' ||
507 ' status, :l_target_ledger_id, accounting_date,' ||
508 ' currency_code, date_created, created_by,' ||
509 ' actual_flag, user_je_category_name, :l_user_je_source_name,' ||
510 ' currency_conversion_date, encumbrance_type_id,' ||
511 ' :l_target_budget_version_id, user_currency_conversion_type,' ||
512 ' currency_conversion_rate, segment1, segment2,' ||
513 ' segment3, segment4, segment5, segment6,' ||
514 ' segment7, segment8, segment9, segment10,' ||
515 ' segment11, segment12, segment13,' ||
516 ' segment14, segment15, segment16,' ||
517 ' segment17, segment18, segment19,' ||
518 ' segment20, segment21, segment22,' ||
519 ' segment23, segment24, segment25,' ||
520 ' segment26, segment27, segment28,' ||
521 ' segment29, segment30, entered_dr,' ||
522 ' entered_cr, accounted_dr, accounted_cr,' ||
523 ' transaction_date, reference1, reference2,' ||
524 ' reference3, reference4, reference5,' ||
525 ' reference6, reference7, reference8,' ||
526 ' reference9, reference10, reference11,' ||
527 ' reference12, reference13, reference14,' ||
528 ' reference15, reference16, reference17,' ||
529 ' reference18, reference19, reference20,' ||
530 ' reference21, reference22, reference23,' ||
531 ' reference24, reference25, reference26,' ||
532 ' reference27, reference28, reference29,' ||
533 ' reference30, je_batch_id, period_name,' ||
534 ' je_header_id, je_line_num, chart_of_accounts_id,' ||
535 ' functional_currency_code,' ||
536 ' code_combination_id, date_created_in_gl,' ||
537 ' warning_code, status_description,' ||
538 ' stat_amount, group_id, request_id,' ||
539 ' subledger_doc_sequence_id, subledger_doc_sequence_value,' ||
540 ' attribute1, attribute2, attribute3,' ||
541 ' attribute4, attribute5, attribute6,' ||
542 ' attribute7, attribute8, attribute9,' ||
543 ' attribute10, attribute11, attribute12,' ||
544 ' attribute13, attribute14, attribute15,' ||
545 ' attribute16, attribute17, attribute18,' ||
546 ' attribute19, attribute20, context,' ||
547 ' context2, invoice_date, tax_code,' ||
548 ' invoice_identifier, invoice_amount,' ||
549 ' context3, ussgl_transaction_code,' ||
550 ' descr_flex_error_message, jgzz_recon_ref,' ||
551 ' average_journal_flag, originating_bal_seg_value,' ||
552 ' gl_sl_link_id, gl_sl_link_table,' ||
553 ' reference_date, balancing_segment_value, management_segment_value' ||
554 ' FROM ' ||l_in_table_name ||
555 ' where period_name = :pd_name' ||
556 ' and request_id = :rq_id' ||
557 ' and actual_flag = :flag' ||
558 ' and user_je_source_name = :je_source';
559 EXECUTE IMMEDIATE v_InsertSQL USING l_target_ledger_id, l_target_je_source,
560 l_target_budget_version_id,l_pd_name, l_request_id,
561 actual_flag, l_user_je_source_name;
562 COMMIT;
563 RETURN l_group_id;
564 --+Don't forget to drop the gl_cons_interface_n table from the source db
565 exception
566 when OTHERS then
567 errbuf := SUBSTR(SQLCODE || ' ; ' || SQLERRM, 1, 150);
568 RETURN -1;
569 END Remote_Data_transfer;
570 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
571 --+check if the responsibility has access right to Journal Import and
572 --+Journal Post menu on the target database.
573 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
574 FUNCTION Verify_Menu_Access(
575 user_id IN number,
576 resp_id IN number,
577 app_id IN number,
578 p_dblink IN varchar2,
579 p_j_import IN varchar2,
580 p_j_post IN varchar2
581 ) RETURN VARCHAR2
582 IS
583 v_SQL varchar2(1000);
584 dblink varchar2(100);
585 l_count number;
586 l_one number;
587 l_two number;
588 l_three number;
589 l_four number;
590 l_test VARCHAR2(30);
591 func_name varchar2(30);
592 BEGIN
593 l_count := 0;
594 dblink := p_dblink ||'.' || domainName;
595 v_SQL := 'BEGIN ' || ':test := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Menu_Validation@' ||dblink ||
596 '(:user_id, :resp_id, :app_id, :JI, :JP); ' || 'END;';
597 EXECUTE IMMEDIATE v_SQL USING OUT l_test, IN user_id, IN resp_id, IN app_id, IN p_j_import, IN p_j_post;
598 return l_test;
599 END Verify_Menu_Access;
600 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
601 --+ Verify_Period
602 --+ verify if the source and the target has the same start_date, end_date,
603 --+ quarter_date and year_date for a specific ledger and period name.
604 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
605 FUNCTION Verify_Period(
606 p_pd_name IN varchar2,
607 user_id IN number,
608 resp_id IN number,
609 app_id IN number,
610 p_dblink IN varchar2,
611 p_ledger IN number,
612 p_r_ledger_id IN number
613 ) RETURN VARCHAR2
614 IS
615 v_PDSQL varchar2(300);
616 dblink varchar2(100);
617 l_count number;
618 l_start_date DATE;
619 l_end_date DATE;
620 l_quarter_date DATE;
621 l_year_date DATE;
622 l_r_start_date DATE;
623 l_r_end_date DATE;
624 l_r_quarter_date DATE;
625 l_r_year_date DATE;
626 l_access_set_name varchar2(30);
627 BEGIN
628 dblink := p_dblink ||'.' || domainName;
629 --input: target ledger id, period name
630 v_PDSQL := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Period_Exists@' || dblink
631 || '(:ledger_id, :period_name)' ||';'||' END;';
632 EXECUTE IMMEDIATE v_PDSQL USING OUT l_count, IN p_r_ledger_id, p_pd_name;
633 if l_count = 1 then
634 v_PDSQL := 'BEGIN ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Get_Period_Info@' || dblink ||
635 '(:1, :2, :3, :4, :5, :6);' || ' END;';
636 EXECUTE IMMEDIATE v_PDSQL USING IN p_r_ledger_id, IN p_pd_name,
637 OUT l_r_start_date, OUT l_r_end_date, OUT l_r_quarter_date, OUT l_r_year_date;
638 v_PDSQL := 'select p.start_date, p.end_date, p.quarter_start_date, ' ||
639 'p.year_start_date from gl_periods' ||
640 ' p, gl_ledgers b ' ||
641 'where p.period_set_name = b.period_set_name ' ||
642 'and p.period_type = b.accounted_period_type ' ||
643 'and b.ledger_id = :s ' ||
644 'and p.period_name = :pd';
645 EXECUTE IMMEDIATE v_PDSQL INTO l_start_date, l_end_date,
646 l_quarter_date, l_year_date USING p_ledger, p_pd_name;
647 if (l_r_start_date = l_start_date) AND
648 (l_r_end_date = l_end_date) AND
649 (l_r_quarter_date = l_quarter_date) AND
650 (l_r_year_date = l_year_date) THEN
651 return 'SUCCESS';
652 ELSE
653 return 'FAILURE';
654 END IF;
655 end if;
656 return 'FAILURE';
657 END Verify_Period;
658 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
659 --+ Verify_Ledger
660 --+verify the average daily balances flag is the same in both ledgers
661 --+verify the consolidation ledger flag is the same in both ledgers
662 --+validate primary currency code in the source ledger vs. the target
663 --+ledger
664 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
665 FUNCTION Verify_Ledger(
666 user_id IN number,
667 resp_id IN number,
668 app_id IN number,
669 p_dblink IN varchar2,
670 p_ledger IN number,
671 p_r_ledger_id IN number) RETURN VARCHAR2
672 IS
673 l_ledger_id number;
674 v_ledgerSQL varchar2(300);
675 v_R_ledgerSQL varchar2(300);
676 dblink varchar2(100);
677 l_daily_bal varchar2(1);
678 l_r_daily_bal varchar2(1);
679 l_cons_ledger varchar2(1);
680 l_r_cons_ledger varchar2(1);
681 l_cur_code varchar2(15);
682 l_r_cur_code varchar2(15);
683 l_access_set_name varchar2(30);
684 BEGIN
685 dblink := p_dblink ||'.' || domainName;
686 l_ledger_id := p_ledger;
687 v_ledgerSQL := 'select enable_average_balances_flag from gl_ledgers ' ||
688 'where ledger_id = :ledger_id';
689 EXECUTE IMMEDIATE v_ledgerSQL INTO l_daily_bal USING l_ledger_id;
690 --+input: target ledger id
691 v_R_ledgerSQL := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_Daily_Balance_Flag@' || dblink
692 || '(:ledger_id)' ||';'||' END;';
693 EXECUTE IMMEDIATE v_R_ledgerSQL USING OUT l_r_daily_bal, IN p_r_ledger_id;
694 --+dbms_output.put_line('what is the ledger id? ' || p_r_ledger_id);
695 if l_daily_bal <> l_r_daily_bal then
696 return 'FAILURE1';
697 end if;
698 v_ledgerSQL := 'select consolidation_ledger_flag from gl_ledgers ' ||
699 'where ledger_id = :ledger_id';
700 EXECUTE IMMEDIATE v_ledgerSQL INTO l_cons_ledger USING l_ledger_id;
701 --+dbms_output.put_line('what is the source cons flag? ' || l_cons_ledger);
702 v_R_ledgerSQL := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_Cons_ledger_Flag@' || dblink
703 || '(:ledger_id)' ||';'||' END;';
704 EXECUTE IMMEDIATE v_R_ledgerSQL USING OUT l_r_cons_ledger, IN p_r_ledger_id;
705 --+dbms_output.put_line('what is the target cons flag? ' || l_r_cons_ledger);
706 if l_cons_ledger <> l_r_cons_ledger then
707 return 'FAILURE1';
708 end if;
709 v_ledgerSQL := 'SELECT CURRENCY_CODE FROM GL_ledgers ' ||
710 'WHERE ledger_id = :ledger_id';
711 EXECUTE IMMEDIATE v_ledgerSQL INTO l_cur_code USING l_ledger_id;
712 --+dbms_output.put_line('what is source currency ' || l_cur_code);
713 v_R_ledgerSQL := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_Currency_Code@' || dblink
714 || '(:ledger_id)' ||';'||' END;';
715 EXECUTE IMMEDIATE v_R_ledgerSQL USING OUT l_r_cur_code, IN p_r_ledger_id;
716 --+dbms_output.put_line('what is target currency ' || l_r_cur_code);
717 if l_cur_code <> l_r_cur_code then
718 return 'FAILURE2';
719 end if;
720 return 'SUCCESS';
721 END Verify_Ledger;
722 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
723 --+check if the chart of accounts structure in the source and target
724 --+database are the same
725 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
726 FUNCTION Verify_COA(user_id IN number, resp_id IN number, app_id IN number,
727 p_dblink IN varchar2, p_ledger IN number,
728 p_r_ledger_id IN number) RETURN VARCHAR2
729 IS
730 v_COACursor t_RefCur;
731 v_R_COACursor t_RefCur;
732 v_SQL varchar2(2000);
733 v_R_SQL varchar2(3000);
734 l_ledger_id number;
735 l_coa_id number;
736 v_ledgerSQL varchar2(300);
737 l_r_ledger_id number;
738 l_r_coa_id number;
739 v_R_ledgerSQL varchar2(300);
740 l_index number;
741 l_r_index number;
742 p_count number;
743 l_segment_num FND_ID_FLEX_SEGMENTS.SEGMENT_NUM%TYPE;
744 l_column_name FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME%TYPE;
745 l_display_size FND_ID_FLEX_SEGMENTS.DISPLAY_SIZE%TYPE;
746 l_r_segment_num FND_ID_FLEX_SEGMENTS.SEGMENT_NUM%TYPE;
747 l_r_column_name FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME%TYPE;
748 l_r_display_size FND_ID_FLEX_SEGMENTS.DISPLAY_SIZE%TYPE;
749 l_app_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE;
750 l_gl_short_name FND_ID_FLEX_SEGMENTS.ID_FLEX_CODE%TYPE;
751 result varchar2(100);
752 dblink varchar2(100);
753 l_access_set_name varchar2(30);
754 BEGIN
755 l_index := 1;
756 l_r_index := 1;
757 p_count := 1;
758 dblink := p_dblink ||'.' || domainName;
759 l_app_id := 101;
760 l_gl_short_name := 'GL#';
761 l_r_ledger_id := p_r_ledger_id;
762 l_ledger_id := p_ledger;
763 v_ledgerSQL := 'select chart_of_accounts_id from gl_ledgers ' ||
764 'where ledger_id = :ledger_id';
765 EXECUTE IMMEDIATE v_ledgerSQL INTO l_coa_id USING l_ledger_id;
766 chart.DELETE;
767 --+get coa information from the fnd table in the source database
768 v_SQL := 'SELECT s.SEGMENT_NUM, ' ||
769 's.APPLICATION_COLUMN_NAME, ' ||
770 's.DISPLAY_SIZE ' ||
771 'FROM FND_FLEX_VALUE_SETS vs, ' ||
772 'FND_ID_FLEX_SEGMENTS s ' ||
773 'WHERE vs.flex_value_set_id = s.flex_value_set_id ' ||
774 'AND s.ID_FLEX_NUM = :coa_id ' ||
775 'AND s.application_id = :app_id ' ||
776 'AND s.id_flex_code = :gl' ||
777 ' order by segment_num';
778 OPEN v_COACursor FOR v_SQL USING l_coa_id, l_app_id, l_gl_short_name;
779 LOOP
780 FETCH v_COACursor INTO l_segment_num, l_column_name, l_display_size;
781 EXIT WHEN v_COACursor%NOTFOUND;
782 chart(l_index).segment_num := l_segment_num;
783 chart(l_index).application_column_name := l_column_name;
784 chart(l_index).display_size := l_display_size;
785 --+dbms_output.put_line('Seg '||chart(l_index).segment_num ||
786 --+ ' is '||chart(l_index).application_column_name||
787 --+ ' size='||chart(l_index).display_size);
788 l_index := l_index + 1;
789 END LOOP;
790 CLOSE v_COACursor;
791 remote_chart.DELETE;
792 v_R_ledgerSQL := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_COA_Id@' || dblink
793 || '(:ledger_id)' ||';'||' END;';
794 EXECUTE IMMEDIATE v_R_ledgerSQL USING OUT l_r_coa_id, IN l_r_ledger_id;
795 --+get coa information from the fnd table in the target database
796 v_R_SQL := 'BEGIN '||applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.coa_info@' || dblink
797 || '(:coa_id, :cnt)' ||';'||' END;';
798 EXECUTE IMMEDIATE v_R_SQL USING IN l_r_coa_id, IN OUT l_r_index;
799 if l_index <> l_r_index then
800 result := 'COA Not matched';
801 return result;
802 end if;
803 while p_count < l_index loop
804 v_R_SQL := 'BEGIN '||applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_Detail_coa_info@' || dblink
805 || '(:coa_id, :cnt, :1, :2)' ||';'||' END;';
806 EXECUTE IMMEDIATE v_R_SQL USING IN l_r_coa_id, IN p_count, IN OUT l_r_column_name,
807 IN OUT l_r_display_size;
808 if chart(p_count).display_size <> l_r_display_size then
809 result := 'COA Not matched';
810 return result;
811 end if;
812 if chart(p_count).application_column_name <> l_r_column_name then
813 result := 'COA Not matched';
814 return result;
815 end if;
816 p_count := p_count +1;
817 end loop;
818 return 'SUCCESS';
819 END Verify_COA;
820 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
821 --+verify the remote login information, chart of accounts structure and
822 --+ledger characteristics, period specifics in the calendar
823 --+and Journal Import and Post menu access right
824 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
825 FUNCTION Remote_Data_Validation(
826 dblink IN varchar2,
827 p_resp_name IN varchar2,
828 p_pd_name IN varchar2,
829 p_ledger IN number,
830 p_j_import IN varchar2,
831 p_j_post IN varchar2
832 ) RETURN varchar2
833 IS
834 test varchar2(100);
835 v_SQL1 varchar2(1000);
836 v_SQL2 varchar2(1000);
837 v_SelectSQL varchar2(300);
838 v_SelectSQL2 varchar2(300);
839 v_SelectSQL3 varchar2(300);
840 v_BlockSQL varchar2(500);
841 l_count number;
842 result varchar2(200);
843 return_result varchar2(200);
844 result_ledger varchar2(200);
845 result_period varchar2(200);
846 result_menu varchar2(100);
847 l_user_id number;
848 l_resp_id number;
849 l_app_id number;
850 l_temp_db varchar2(30);
851 l_error_code number;
852 l_user_name varchar2(150);
853 dummy1 varchar2(30);
854 dummy2 varchar2(30);
855 l_r_ledger_id number; -- target ledger_id
856 l_access_set varchar2(30);
857 l_access_code varchar2(1);
858 l_access_set_id number;
859 BEGIN
860 l_count := 0;
861 --make sure db link exists
862 v_SelectSQL := 'select count(*) from rg_database_links ' ||
863 'where name = :dblink';
864 EXECUTE IMMEDIATE v_SelectSQL INTO l_count USING dblink;
865 if l_count = 0 then
866 result := 'GL_US_CI_VALID_FAIL_DB';
867 return result;
868 end if;
869 Get_Domain_Name(dblink);
870 l_temp_db := dblink || '.' || domainName;
871 Get_Schema_Name(l_temp_db); --bug#2630145
872 --make sure this responsibility exists in the target db
873 v_SQL1 := 'BEGIN ' || ':num := ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Validate_Resp@' ||l_temp_db ||
874 '(:resp_name); ' || 'END;';
875 EXECUTE IMMEDIATE v_SQL1 USING OUT l_error_code, IN p_resp_name;
876 if l_error_code = 2 then
877 result := 'GL_US_CI_VALID_FAIL_RESP';
878 return result;
879 elsif l_error_code = 0 then
880 result := 'SUCCESS';
881 end if;
882 --make sure the same user exists in the target db
883 l_app_id := 101;
884 l_user_name := 'LEDGER';
885 -- l_user_name := fnd_global.USER_NAME; --bug#2543150, remove username from input
886 v_SQL1 := 'BEGIN ' || ':num := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_Login_Ids@' || l_temp_db ||
887 '(:1, :2, :3, :4);' || ' END;';
888 EXECUTE IMMEDIATE v_SQL1 USING OUT l_error_code, IN l_user_name, IN p_resp_name, OUT l_user_id, OUT l_resp_id;
889 if l_error_code = 1 then
890 result := 'GL_US_CI_VALID_FAIL_USER';
891 return result;
892 elsif l_error_code = 0 then
893 result := 'SUCCESS';
894 end if;
895 --get the target ledger id
896 l_r_ledger_id := Get_Ledger_id(l_user_id, l_resp_id, l_app_id,dblink, l_access_set_id, l_access_set, l_access_code);
897 if l_r_ledger_id < 0 then
898 result := 'GL_US_CI_NO_DEFAULT_LEDGER';
899 return result;
900 end if;
901 if l_r_ledger_id >= 0 then
902 if l_access_code <> 'B' then
903 result := 'GL_US_CI_NO_ACCESS_RIGHTS';
904 return result;
905 end if;
906 end if;
907 return_result := Verify_COA(l_user_id, l_resp_id, l_app_id,
908 dblink, p_ledger, l_r_ledger_id);
909 if return_result = 'SUCCESS' then
910 result_ledger := Verify_Ledger(l_user_id, l_resp_id, l_app_id,
911 dblink, p_ledger, l_r_ledger_id);
912 if result_ledger = 'SUCCESS' then
913 result_period := Verify_Period(p_pd_name,l_user_id, l_resp_id,
914 l_app_id, dblink, p_ledger, l_r_ledger_id);
915 if result_period = 'SUCCESS' then
916 result:= 'SUCCESS';
917 else
918 result := 'GL_US_CI_VALIDATION_PERIOD';
919 return result;
920 end if;
921 else
922 if result_ledger = 'FAILURE1' then
923 result := 'GL_US_CI_VALIDATION_LEDGER';
924 elsif result_ledger = 'FAILURE2' then
925 result := 'GL_US_CI_CURRENCY_CHECK';
926 end if;
927 return result;
928 end if;
929 else
930 result := 'GL_US_CI_VALIDATION_COA';
931 return result;
932 end if;
933 result_menu := Verify_Menu_Access(l_user_id, l_resp_id,l_app_id,dblink,p_j_import,p_j_post);
934 if result_menu = 'IMPORT FAIL' then
935 result := 'GL_US_CI_NO_IMPORT';
936 return result;
937 elsif result_menu = 'POST FAIL' then
938 result := 'GL_US_CI_NO_POSTING';
939 return result;
940 else
941 result := 'SUCCESS';
942 end if;
943 if result = 'SUCCESS' then
944 result := 'GL_US_CI_VALIDATION_OK';
945 end if;
946 RETURN result;
947 exception
948 WHEN OTHERS THEN
949 rollback;
950 result := SUBSTR(SQLERRM, 1, 200);
951 FND_MESSAGE.set_name('SQLGL', 'gl_us_ci_others_fail');
952 FND_MESSAGE.set_token('RESULT', result);
953 app_exception.raise_exception;
954 return result;
955 END Remote_Data_Validation;
956 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
957 --+get the group ID from the source dtaabase after the consolidation data
958 --+has been transfered into gl_interface table
959 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
960 function Get_Source_Group_ID(
961 cons_id IN number,
962 cons_run_id IN number
963 ) return number
964 IS
965 v_SQL varchar2(500);
966 l_group_id number;
967 BEGIN
968 v_SQL := 'select group_id from gl_consolidation_history ' ||
969 'where consolidation_id = :cons_id ' ||
970 'and consolidation_run_id = :cons_run_id';
971 EXECUTE IMMEDIATE v_SQL INTO l_group_id USING cons_id, cons_run_id;
972 l_group_id := l_group_id;
973 return l_group_id;
974 END Get_Source_Group_id;
975 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
976 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
977 procedure Compose_Import_message(
978 l_reqJI_id IN number,
979 dblink IN varchar2,
980 l_import_message_body OUT NOCOPY varchar2
981 ) IS
982 v_SelectSQL varchar2(500);
983 v_ReturnCursor t_RefCur;
984 v_Batches gl_je_batches%ROWTYPE;
985 l_batch_name varchar2(100);
986 l_batch_id number;
987 l_first_one boolean;
988 BEGIN
989 l_first_one := TRUE;
990 -- Get_Schema_Name(dblink); --=bug#2630145
991 v_SelectSQL := 'select * from ' || applSysSchema ||'.gl_je_batches@' || dblink ||
992 ' where name like ''%' || TO_CHAR(l_reqJI_id) || '%''';
993 OPEN v_ReturnCursor FOR v_SelectSQL;
994 LOOP --+for every batch in this transfer
995 FETCH v_ReturnCursor INTO v_Batches;
996 EXIT WHEN v_ReturnCursor%NOTFOUND;
997 l_batch_id := v_Batches.je_batch_id;
998 v_SelectSQL := 'select name from ' || applSysSchema ||'.gl_je_batches@' || dblink ||
999 ' WHERE je_batch_id = :b_id';
1000 EXECUTE IMMEDIATE v_SelectSQL INTO l_batch_name USING l_batch_id;
1001 COMMIT;
1002 if l_first_one then
1003 l_import_message_body := l_import_message_body || l_batch_name;
1004 l_first_one := FALSE;
1005 else
1006 l_import_message_body := l_import_message_body ||', ' || l_batch_name;
1007 end if;
1008 END LOOP;
1009 CLOSE v_ReturnCursor;
1010 END Compose_Import_Message;
1011 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1012 --+ Main Entry procedure for Cross Instance Consolidation Data Transfer
1013 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1014 procedure run_CI_transfer(
1015 errbuf in out NOCOPY varchar2,
1016 retcode in out NOCOPY varchar2,
1017 p_resp_name IN varchar2,
1018 p_user_name IN varchar2,
1019 p_dblink IN varchar2,
1020 from_group_id IN number,
1021 from_ledger_id IN number,
1022 p_pd_name IN varchar2,
1023 p_budget_name IN varchar2,
1024 p_j_import IN VARCHAR2,
1025 p_j_post IN varchar2,
1026 p_actual_flag IN varchar2,
1027 p_request_id IN number,
1028 p_csj_flag IN VARCHAR2,
1029 p_debug IN varchar2
1030 )
1031 IS
1032 dblink varchar2(30);
1033 l_inter_run_id number;
1034 l_reqJI_id number;
1035 l_reqPost_id number;
1036 l_pd_name gl_interface.period_name%TYPE;
1037 l_to_ledger_id number;
1038 l_user_id number;
1039 l_resp_id number;
1040 l_app_id number;
1041 l_to_group_id number;
1042 v_SQL varchar2(500);
1043 v_WaitSQL varchar2(500);
1044 v_TSQL varchar2(1000);
1045 v_SelectSQL varchar2(500);
1046 l_result varchar2(100);
1047 l_verify varchar2(100);
1048 l_postable_rows number;
1049 l_batch_name varchar2(100);
1050 l_batch_id number;
1051 v_SQL1 varchar2(1000);
1052 v_SQL2 varchar2(2000);
1053 v_ReturnCursor t_RefCur;
1054 v_Batches gl_je_batches%ROWTYPE;
1055 l_posted_rows number;
1056 l_status varchar2(1);
1057 l_target_ledger_name varchar2(30);
1058 l_post_run_id number;
1059 l_return_code number;
1060 l_import_message_body varchar2(4000);
1061 l_first_one boolean;
1062 l_post_request_id varchar2(3000);
1063 l_src_table_name varchar2(30);
1064 l_avg_flag varchar2(1);
1065 l_balanced_flag varchar2(1);
1066 l_suspense_flag varchar2(1);
1067 l_user_name varchar2(150);
1068 --get debug mode from GL: Debug Mode profile option
1069 l_dmode_profile fnd_profile_option_values.profile_option_value%TYPE;
1070 l_debug varchar2(1);
1071 --+the data access set name, it is used in a error messsage.
1072 l_access_set_name varchar2(30);
1073 l_access_code varchar2(1);
1074 l_access_set_id number;
1075 begin
1076 section_number := 0;
1077 l_inter_run_id := 0;
1078 l_first_one := TRUE;
1079 l_import_message_body := NULL;
1080 l_post_request_id := NULL;
1081 l_pd_name := p_pd_name;
1082 --+bug#2712006, cannot use hardcoded domain name
1083 Get_Domain_Name(p_dblink);
1084 dblink := p_dblink || '.' || domainName;
1085 Get_Schema_Name(dblink); --+bug#2630145, don't hardcoded apps schema name
1086 --added for 11ix. use debug profile option to determine the debug setting
1087 FND_PROFILE.GET('GL_DEBUG_MODE', l_dmode_profile);
1088 -- Determine if process will be run in debug mode
1089 IF (NVL(p_Debug, 'N') <> 'N') OR (l_dmode_profile = 'Y') THEN
1090 l_Debug := 'Y';
1091 ELSE
1092 l_Debug := 'N';
1093 END IF;
1094 if l_debug = 'Y' then
1095 debug_message('Running Cross Instance Data Transfer in debug mode',TRUE);
1096 debug_message('Section number is ' || section_number,TRUE);
1097 -- Turn trace on if process is run in debug mode
1098 EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
1099 end if;
1100 l_user_name := fnd_global.USER_NAME; --bug#2543150, remove username
1101 --from Remote Option form
1102 v_SQL1 := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_User_ID@' || dblink
1103 || '(:user_name)' ||';'||' END;';
1104 EXECUTE IMMEDIATE v_SQL1 USING OUT l_user_id, IN l_user_name;
1105 v_SQL1 := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_Resp_ID@' || dblink
1106 || '(:resp_name)' ||';'||' END;';
1107 EXECUTE IMMEDIATE v_SQL1 USING OUT l_resp_id, IN p_resp_name;
1108 if l_debug = 'Y' then
1109 debug_message('User ID is ' || l_user_id || ' and Responsibility ID is ' || l_resp_id,TRUE);
1110 end if;
1111 section_number := 1;
1112 l_app_id := 101;
1113 --+get the target ledger id based on data access set in the target db
1114 --+ledger_id = -1, when no default ledger can be found for this data access set
1115 l_to_ledger_id := Get_Ledger_id(l_user_id, l_resp_id,
1116 l_app_id, p_dblink, l_access_set_id, l_access_set_name, l_access_code);
1117 if l_to_ledger_id = -1 then
1118 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_NO_DEFAULT_LEDGER');
1119 FND_MESSAGE.set_token('ACCESS_SET', l_access_set_name);
1120 log_message('==>' || FND_MESSAGE.get, TRUE);
1121 global_retcode := 1;
1122 raise GLOBAL_ERROR;
1123 end if;
1124 if l_to_ledger_id >= 0 then
1125 v_SQL1 := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_Ledger_Name@' || dblink
1126 || '(:ledger_id)' ||';'||' END;';
1127 EXECUTE IMMEDIATE v_SQL1 USING OUT l_target_ledger_name, IN l_to_ledger_id;
1128 if l_debug = 'Y' then
1129 debug_message('Target ledger id is ' || l_to_ledger_id,TRUE);
1130 debug_message('Target ledger name is ' || l_target_ledger_name,TRUE);
1131 end if;
1132 if l_access_code <> 'B' then
1133 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_NO_ACCESS_RIGHTS');
1134 FND_MESSAGE.set_token('LEDGER_NAME', l_target_ledger_name);
1135 log_message('==>' || FND_MESSAGE.get, TRUE);
1136 global_retcode := 1;
1137 raise GLOBAL_ERROR;
1138 end if;
1139 end if;
1140 --+check if the suspense posting is allowed for this ledger on the target instance
1141 v_SQL1 := 'BEGIN '||' :a := ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Get_Suspense_Flag@' || dblink
1142 || '(:ledger_id)' ||';'||' END;';
1143 EXECUTE IMMEDIATE v_SQL1 USING OUT l_suspense_flag, IN l_to_ledger_id;
1144 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_ENTER_CI');
1145 log_message('==>' || FND_MESSAGE.get, TRUE);
1146 errbuf := FND_MESSAGE.get;
1147 global_errbuf := FND_MESSAGE.get;
1148 --+Add l_balanced_flag to check if total debits equal to total credits. if not, output warning message
1149 --+to log file.
1150 l_to_group_id := Remote_Data_transfer(p_actual_flag,l_user_id, l_resp_id, l_app_id,
1151 dblink, from_ledger_id,l_pd_name, p_budget_name, from_group_id,
1152 p_request_id, p_dblink, l_to_ledger_id,
1153 l_avg_flag, l_balanced_flag, global_errbuf);
1154 --bug fix#3095741, return a meaningful error message when budget name is no found in the target db
1155 if (l_to_group_id = -1) and (global_errbuf = 'FAILED') then
1156 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_NO_BUDGET');
1157 log_message('==>' || FND_MESSAGE.get, TRUE);
1158 global_errbuf := FND_MESSAGE.get;
1159 global_retcode := 2;
1160 raise GLOBAL_ERROR;
1161 end if;
1162 if (l_to_group_id = -1) and (global_errbuf = 'NO_DATA_FOUND') then
1163 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_NO_DATA_FOUND');
1164 log_message('==>' || FND_MESSAGE.get, TRUE);
1165 global_errbuf := FND_MESSAGE.get;
1166 global_retcode := 1;
1167 raise GLOBAL_ERROR;
1168 end if;
1169 if l_to_group_id < 0 then
1170 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_TRANSFER_FAIL');
1171 log_message('==>' || FND_MESSAGE.get, TRUE);
1172 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1173 if l_debug = 'Y' then
1174 debug_message('Invalid Target group ID in section number ' || section_number,TRUE);
1175 end if;
1176 global_retcode := 1;
1177 raise GLOBAL_ERROR;
1178 else
1179 if (l_balanced_flag = 'Y') then
1180 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_TRANSFER_OK');
1181 FND_MESSAGE.set_token('GROUP_ID', l_to_group_id);
1182 log_message('==>' || FND_MESSAGE.get, TRUE);
1183 errbuf := errbuf || ' ' || FND_MESSAGE.get;
1184 else
1185 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_TRANSFER_OK');
1186 FND_MESSAGE.set_token('GROUP_ID', l_to_group_id);
1187 log_message('==>' || FND_MESSAGE.get, TRUE);
1188 errbuf := errbuf || ' ' || FND_MESSAGE.get;
1189 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_IMBALANCED_OTHER');
1190 log_message('==>' || FND_MESSAGE.get, TRUE);
1191 errbuf := errbuf || ' ' || FND_MESSAGE.get;
1192 end if;
1193 IF (p_j_import = 'N') THEN
1194 --+populates the gl_interface_control table and gl_cons_interface_to_group_id table
1195 --+add an average flag to indicate whether this is a adb balances transfer or not
1196 v_SQL := 'BEGIN '||' :l_id := ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Apps_Initialize@' || dblink ||
1197 '(:usr_id, :rsp_id, :ap_id, :sb_id, :gp_id, :pd_name, :flag, :avg);'||' END;';
1198 EXECUTE IMMEDIATE v_SQL USING OUT l_inter_run_id, IN l_user_id, IN l_resp_id, IN l_app_id, IN l_to_ledger_id,
1199 IN l_to_group_id, IN l_pd_name, IN p_actual_flag, IN l_avg_flag;
1200 commit;
1201 end if;
1202 end if; --+if l_to_group_id < 0 then
1203 --+drop the gl_cons_interface_groupid table in the source db no matter what
1204 l_src_table_name := i_parallel_name || from_group_id;
1205 gl_journal_import_pkg.drop_table(l_src_table_name);
1206 if l_debug = 'Y' then
1207 debug_message('Data has been moved to Target database, target group ID is ' || l_to_group_id,TRUE);
1208 end if;
1209 IF (p_j_import = 'Y') THEN
1210 --+get the target ledger id
1211 section_number := 2;
1212 if l_debug = 'Y' then
1213 debug_message('Populates the gl_interface_control table on Target database',TRUE);
1214 debug_message('Updates the gl_cons_interface_' || l_to_group_id || ' on target database',TRUE);
1215 end if;
1216 --+populates the gl_interface_control table and gl_cons_interface_to_group_id table
1217 v_SQL := 'BEGIN '||' :l_id := ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Apps_Initialize@' || dblink ||
1218 '(:usr_id, :rsp_id, :ap_id, :sb_id, :gp_id, :pd_name, :flag, :avg);'||' END;';
1219 EXECUTE IMMEDIATE v_SQL USING OUT l_inter_run_id, IN l_user_id, IN l_resp_id, IN l_app_id, IN l_to_ledger_id,
1220 IN l_to_group_id, IN l_pd_name, IN p_actual_flag, IN l_avg_flag;
1221 commit;
1222 --+dbms_output.put_line('interface run id is ' || l_inter_run_id);
1223 if l_debug = 'Y' then
1224 debug_message('Start Journal Import on Target database',TRUE);
1225 end if;
1226 --+Journal Import
1227 v_SQL := 'BEGIN' || ' :r_id := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Run_Journal_Import@' || dblink ||
1228 '(:usr_id, :rsp_id, :ap_id, :run_id, :sb_id, :csj_flag);' || ' END;';
1229 EXECUTE IMMEDIATE v_SQL USING OUT l_reqJI_id, IN l_user_id, IN l_resp_id, IN l_app_id,
1230 IN l_inter_run_id, IN l_to_ledger_id, IN p_csj_flag;
1231 commit;
1232 if l_debug = 'Y' then
1233 debug_message('Journal Import has been submitted on Target database, the request ID is ' || l_reqJI_id,TRUE);
1234 end if;
1235 --+dbms_output.put_line('import sql is ' || v_SQL);
1236 if (l_reqJI_id <> 0) then
1237 --+Wait for Journal Import completes
1238 v_WaitSQL := 'BEGIN ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.wait_for_request@' || dblink ||
1239 '(:r_id, :result);' || ' END;';
1240 EXECUTE IMMEDIATE v_WaitSQL USING IN l_reqJI_id, OUT l_result;
1241 --COMMIT;
1242 if l_debug = 'Y' then
1243 debug_message('Section number is ' || section_number,TRUE);
1244 debug_message('Journal Import has completed, status is ' || l_result,TRUE);
1245 end if;
1246 if l_result = 'COMPLETE:PASS' then
1247 v_SQL2 := 'BEGIN ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Verify_Journal_Import@' || dblink ||
1248 '(:gp_id, :result);' || ' END;';
1249 EXECUTE IMMEDIATE v_SQL2 USING IN l_to_group_id, OUT l_verify;
1250 --commit;
1251 if l_debug = 'Y' then
1252 debug_message('The status after verifying journal import is ' || l_verify,TRUE);
1253 end if;
1254 if l_verify = 'SUCCESS' then
1255 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_IMPORT_OK');
1256 FND_MESSAGE.set_token('RID', l_reqJI_id);
1257 log_message('==>' || FND_MESSAGE.get, TRUE);
1258 global_errbuf := FND_MESSAGE.get;
1259 errbuf := errbuf || ' ' || FND_MESSAGE.get;
1260 --+dbms_output.put_line('Journal Import is successful ' || l_reqJI_id || ' ' || l_to_group_id);
1261 if (p_j_post = 'Y') then
1262 --+Journal Post
1263 section_number := 3;
1264 --+dbms_output.put_line('j post flag is ' || p_j_post);
1265 v_SelectSQL := 'select * from ' || applSysSchema || '.gl_je_batches@' || dblink ||
1266 ' where name like ''%' || TO_CHAR(l_reqJI_id) || '%''';
1267 OPEN v_ReturnCursor FOR v_SelectSQL;
1268 LOOP --+for every batch in this transfer
1269 FETCH v_ReturnCursor INTO v_Batches;
1270 EXIT WHEN v_ReturnCursor%NOTFOUND;
1271 l_batch_id := v_Batches.je_batch_id;
1272 l_status := 'U';
1273 v_SQL2 := 'BEGIN ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Get_Postable_Rows@' || dblink ||
1274 '(:1, :2, :3, :4, :5, :6, :7);' || ' END;';
1275 --+dbms_output.put_line('Get postable rows sql is ' || v_SQL2);
1276 EXECUTE IMMEDIATE v_SQL2 USING IN l_to_ledger_id, IN l_pd_name,
1277 IN l_batch_id, IN l_status, IN p_actual_flag, IN l_avg_flag, OUT l_postable_rows;
1278 --commit;
1279 if l_debug = 'Y' then
1280 debug_message('After Journal Import, the number of postable rows is ' || l_postable_rows,TRUE);
1281 end if;
1282 --+dbms_output.put_line('Get postable rows is ' || l_postable_rows);
1283 v_SQL2 := 'BEGIN ' || applSysSchema || '.GL_CI_REMOTE_INVOKE_PKG.Run_Journal_Post@' || dblink ||
1284 '(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12);' || ' END;';
1285 --+dbms_output.put_line('sql is ' || v_SQL2);
1286 EXECUTE IMMEDIATE v_SQL2 USING IN l_user_id, IN l_resp_id, IN l_app_id,
1287 IN l_to_ledger_id, IN l_pd_name, IN l_to_group_id, IN l_reqJI_id,
1288 IN l_batch_id, IN p_actual_flag, IN l_access_set_id, OUT l_post_run_id, OUT l_reqPost_id;
1289 commit;
1290 if l_debug = 'Y' then
1291 debug_message('Section number is ' || section_number,TRUE);
1292 debug_message('Journal Post has been submitted, the request ID is ' || l_reqPost_id,TRUE);
1293 end if;
1294 if (l_reqPost_id <> 0) then
1295 --+Wait for Journal Posting completes
1296 v_WaitSQL := 'BEGIN ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.wait_for_request@' || dblink ||
1297 ' (:r_id, :result);' || ' END;';
1298 EXECUTE IMMEDIATE v_WaitSQL USING IN l_reqPost_id, OUT l_result;
1299 --COMMIT;
1300 if l_debug = 'Y' then
1301 debug_message('Journal Post is complete. The status is ' || l_result,TRUE);
1302 end if;
1303 if l_result = 'COMPLETE:PASS' then
1304 --+dbms_output.put_line('Journal post complete');
1305 v_SQL2 := 'BEGIN ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Verify_Journal_Post@' || dblink ||
1306 '(:pd_name, :r, :ledger_id, :bid, :flag, :avg, :result);' || ' END;';
1307 EXECUTE IMMEDIATE v_SQL2 USING IN l_pd_name, IN l_postable_rows,
1308 IN l_to_ledger_id, IN l_batch_id, IN p_actual_flag, IN l_avg_flag, OUT l_verify;
1309 --COMMIT;
1310 --+dbms_output.put_line('After verify Journal post');
1311 v_SQL2 := 'select name from ' || applSysSchema ||'.gl_je_batches@' || dblink ||
1312 ' WHERE je_batch_id = :b_id';
1313 EXECUTE IMMEDIATE v_SQL2 INTO l_batch_name USING l_batch_id;
1314 --COMMIT;
1315 --+dbms_output.put_line('The name of the batch is' || l_batch_name);
1316 --+dbms_output.put_line('The status of Verify Journal Post is ' || l_verify);
1317 if l_debug = 'Y' then
1318 debug_message('The status of Verify Journal Post is ' || l_verify,TRUE);
1319 end if;
1320 if l_verify = 'SUCCESS' then
1321 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_POSTING_OK');
1322 FND_MESSAGE.set_token('RID', l_reqPost_id);
1323 log_message('==>' || FND_MESSAGE.get, TRUE);
1324 global_errbuf := FND_MESSAGE.get;
1325 errbuf := errbuf || ' ' || FND_MESSAGE.get;
1326 --+ dbms_output.put_line('Journal Post is successful ' ||l_reqPost_id);
1327 if l_first_one then
1328 l_import_message_body := l_import_message_body || l_batch_name;
1329 l_post_request_id := l_post_request_id || l_reqPost_id;
1330 l_first_one := FALSE;
1331 else
1332 l_import_message_body := l_import_message_body ||', ' || l_batch_name;
1333 l_post_request_id := l_post_request_id || ', ' || l_reqPost_id;
1334 end if;
1335 else --+ posting verify failed
1336 if (l_balanced_flag = 'Y') then
1337 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_POSTING_FAIL');
1338 FND_MESSAGE.set_token('RID', l_reqPost_id);
1339 log_message('==>' || FND_MESSAGE.get, TRUE);
1340 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1341 global_retcode := 2;
1342 raise GLOBAL_ERROR;
1343 else
1344 if (l_suspense_flag = 'N') then
1345 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_IMBALANCED_OTHER');
1346 log_message('==>' || FND_MESSAGE.get, TRUE);
1347 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1348 else
1349 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_IMBALANCED_SUS');
1350 FND_MESSAGE.set_token('RID', l_reqPost_id);
1351 log_message('==>' || FND_MESSAGE.get, TRUE);
1352 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1353 errbuf := errbuf || ' ' || FND_MESSAGE.get;
1354 end if;
1355 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_POSTING_FAIL');
1356 FND_MESSAGE.set_token('RID', l_reqPost_id);
1357 log_message('==>' || FND_MESSAGE.get, TRUE);
1358 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1359 global_retcode := 2;
1360 raise GLOBAL_ERROR;
1361 end if;
1362 end if;
1363 else --+ posting status is COMPLETE:FAIL
1364 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_POSTING_FAIL');
1365 FND_MESSAGE.set_token('RID', l_reqPost_id);
1366 log_message('==>' || FND_MESSAGE.get, TRUE);
1367 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1368 global_retcode := 1;
1369 raise GLOBAL_ERROR;
1370 end if; --+end of if wait for journal post is successful
1371 else --+For some reason, Journal Posting request id is zero
1372 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_POSTING_FAIL');
1373 FND_MESSAGE.set_token('RID', l_reqPost_id);
1374 log_message('==>' || FND_MESSAGE.get, TRUE);
1375 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1376 global_retcode := 1;
1377 raise GLOBAL_ERROR;
1378 end if; --+ end of if Journal is Posted
1379 END LOOP;
1380 CLOSE v_ReturnCursor;
1381 --+send one email for all posting
1382 gl_ci_workflow_pkg.send_cit_wf_ntf(
1383 p_request_id, 'JOURNAL_POSTED', p_dblink, l_batch_name, --100 CHARS
1384 ' ', l_target_ledger_name, ' ', l_inter_run_id, l_post_run_id, l_reqPost_id,
1385 0, ' ', ' ', 'JOURNAL_POSTED', ' ', from_ledger_id, l_import_message_body,l_post_request_id, l_return_code);
1386 --+bug#2750898, add more user friendly error message when
1387 --+user email address is not set
1388 if (l_return_code = EMAIL_CONTACT_NOT_SET) OR
1389 (l_return_code = CONTACT_INFO_NOT_FOUND) then
1390 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_EMAIL_USER');
1391 log_message('==>' || FND_MESSAGE.get, TRUE);
1392 end if;
1393 ELSE --+do not post, so send email at this point
1394 Compose_Import_message(l_reqJI_id, dblink,l_import_message_body);
1395 gl_ci_workflow_pkg.send_cit_wf_ntf(
1396 p_cons_request_id => p_request_id,
1397 p_Action => 'JOURNAL_IMPORTED',
1398 p_dblink => p_dblink,
1399 p_batch_name => ' ', --100 CHARS
1400 p_source_database_name => ' ',
1401 p_target_ledger_name => l_target_ledger_name,
1402 p_interface_table_name => ' ',
1403 p_interface_run_id => l_inter_run_id,
1404 p_posting_run_id => 0,
1405 p_request_id => l_reqJI_id,
1406 p_group_id => 0,
1407 p_send_to => ' ',
1408 p_sender_name => ' ',
1409 p_message_name =>'JOURNAL_IMPORTED',
1410 p_send_from => ' ',
1411 p_source_ledger_id => from_ledger_id,
1412 p_import_message_body => l_import_message_body,
1413 p_post_request_id => l_post_request_id,
1414 p_Return_Code => l_return_code);
1415 --+bug#2750898, add more user friendly error message when
1416 --+user email address is not set
1417 if (l_return_code = EMAIL_CONTACT_NOT_SET) OR
1418 (l_return_code = CONTACT_INFO_NOT_FOUND) then
1419 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_EMAIL_USER');
1420 log_message('==>' || FND_MESSAGE.get, TRUE);
1421 end if;
1422 end if; --+end of if Journal Post flag is on
1423 else --+JI not verified
1424 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_IMPORT_FAIL');
1425 FND_MESSAGE.set_token('RID', l_reqJI_id);
1426 log_message('==>' || FND_MESSAGE.get, TRUE);
1427 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1428 global_retcode := 2;
1429 raise GLOBAL_ERROR;
1430 end if; --+end of if Journal Import is verified
1431 else --+ JI status is COMPLETE:FAIL
1432 if (l_balanced_flag = 'Y') then
1433 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_IMPORT_FAIL');
1434 FND_MESSAGE.set_token('RID', l_reqJI_id);
1435 log_message('==>' || FND_MESSAGE.get, TRUE);
1436 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1437 global_retcode := 1;
1438 raise GLOBAL_ERROR;
1439 else
1440 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_IMPORT_FAIL');
1441 FND_MESSAGE.set_token('RID', l_reqJI_id);
1442 log_message('==>' || FND_MESSAGE.get, TRUE);
1443 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1444 if (l_suspense_flag = 'N') then
1445 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_IMBALANCED_NOSUS');
1446 FND_MESSAGE.set_token('RID', l_reqji_id);
1447 log_message('==>' || FND_MESSAGE.get, TRUE);
1448 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1449 global_retcode := 1;
1450 raise GLOBAL_ERROR;
1451 else
1452 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_IMBALANCED_OTHER');
1453 log_message('==>' || FND_MESSAGE.get, TRUE);
1454 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1455 global_retcode := 1;
1456 raise GLOBAL_ERROR;
1457 end if;
1458 end if;
1459 end if; --+end of if wait for journal import is successful
1460 else --+JI request id is zero, for some reason, the concurrent request failed
1461 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_JOURNAL_IMPORT_FAIL');
1462 FND_MESSAGE.set_token('RID', l_reqJI_id);
1463 log_message('==>' || FND_MESSAGE.get, TRUE);
1464 global_errbuf := global_errbuf || ' ' || FND_MESSAGE.get;
1465 global_retcode := 1;
1466 raise GLOBAL_ERROR;
1467 end if; --+end of if journal is imported
1468 ELSE --+data transfer only
1469 gl_ci_workflow_pkg.send_cit_wf_ntf(
1470 p_cons_request_id => p_request_id,
1471 p_Action => 'DATA_TRANSFER_DONE',
1472 p_dblink => p_dblink,
1473 p_batch_name => ' ', --100 CHARS
1474 p_source_database_name => ' ',
1475 p_target_ledger_name => l_target_ledger_name,
1476 p_interface_table_name => 'gl_cons_interface_' || l_to_group_id,
1477 p_interface_run_id => l_inter_run_id,
1478 p_posting_run_id => 0,
1479 p_request_id => 0,
1480 p_group_id => l_to_group_id,
1481 p_send_to => ' ',
1482 p_sender_name => ' ',
1483 p_message_name =>'DATA_TRANSFER_DONE',
1484 p_send_from => ' ',
1485 p_source_ledger_id => from_ledger_id,
1486 p_import_message_body => l_import_message_body,
1487 p_post_request_id => l_post_request_id,
1488 p_Return_Code => l_return_code);
1489 --+dbms_output.put_line('The end ');
1490 --+bug#2750898, add more user friendly error message when
1491 --+user email address is not set
1492 if (l_return_code = EMAIL_CONTACT_NOT_SET) OR
1493 (l_return_code = CONTACT_INFO_NOT_FOUND) then
1494 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_EMAIL_USER');
1495 log_message('==>' || FND_MESSAGE.get, TRUE);
1496 end if;
1497 end if; --+end of if journal import flag is on
1498 exception
1499 when GLOBAL_ERROR then
1500 rollback;
1501 retcode := global_retcode;
1502 errbuf := global_errbuf || SUBSTR(SQLERRM,1,200);
1503 when OTHERS then
1504 rollback;
1505 retcode := global_retcode;
1506 errbuf := SUBSTR(SQLERRM,1,255);
1507 FND_MESSAGE.set_name('SQLGL', 'gl_us_ci_others_fail');
1508 FND_MESSAGE.set_token('RESULT', errbuf);
1509 log_message('==>' || FND_MESSAGE.get, TRUE);
1510 END Run_CI_transfer;
1511 end gl_ci_data_transfer_pkg;
1512 --+ End of DDL script for GL_TEST_FND_REQUEST_PKG
1513 --+ End of DDL script for GL_TEST_FND_REQUEST_PKG