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