[Home] [Help]
PACKAGE BODY: APPS.GL_CI_REMOTE_INVOKE_PKG
Source
1 PACKAGE BODY gl_ci_remote_invoke_pkg as
2 /* $Header: glucirmb.pls 120.10 2006/05/25 16:26:09 abhjoshi noship $ */
3
4
5
6 TYPE t_RefCur IS REF CURSOR;
7 batch gl_ci_remote_invoke_PKG.batch_table; --+ holds batch names
8
9 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
10 --+drop any table
11 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
12 procedure drop_table (
13 p_table_name IN varchar2)
14 IS
15 BEGIN
16 gl_journal_import_pkg.drop_table(p_table_name);
17 END drop_table;
18 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
19 --+Get the chart of account information
20 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
21 PROCEDURE Get_Target_Je_source_Name(
22 p_adb_name OUT NOCOPY varchar2,
23 p_name OUT NOCOPY varchar2)
24 IS
25 BEGIN
26 SELECT USER_JE_SOURCE_NAME
27 INTO p_adb_name
28 FROM GL_JE_SOURCES
29 WHERE JE_SOURCE_NAME = 'Average Consolidation';
30
31 SELECT USER_JE_SOURCE_NAME
32 INTO p_name
33 FROM GL_JE_SOURCES
34 WHERE JE_SOURCE_NAME = 'Consolidation';
35
36 END Get_Target_Je_source_Name;
37 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
38 --+Get the chart of account information
39 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
40 procedure coa_info (
41 p_coa_id IN NUMBER,
42 p_count IN OUT NOCOPY Number)
43 IS
44 v_R_COACursor t_RefCur;
45 v_R_SQL varchar2(3000);
46 l_r_segment_num FND_ID_FLEX_SEGMENTS.SEGMENT_NUM%TYPE;
47 l_r_column_name FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME%TYPE;
48 l_r_display_size FND_ID_FLEX_SEGMENTS.DISPLAY_SIZE%TYPE;
49 l_app_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE;
50 l_gl_short_name FND_ID_FLEX_SEGMENTS.ID_FLEX_CODE%TYPE;
51 l_r_index number;
52 l_chart coa_table;
53 BEGIN
54 l_r_index := 1;
55 l_app_id := 101;
56 l_gl_short_name := 'GL#';
57
58 v_R_SQL := 'SELECT s.SEGMENT_NUM, ' ||
59 's.APPLICATION_COLUMN_NAME, ' ||
60 's.DISPLAY_SIZE ' ||
61 'FROM FND_FLEX_VALUE_SETS vs, ' ||
62 'FND_ID_FLEX_SEGMENTS s ' ||
63 'WHERE vs.flex_value_set_id = s.flex_value_set_id ' ||
64 'AND s.ID_FLEX_NUM = :coa_id ' ||
65 'AND s.application_id = :app_id ' ||
66 'AND s.id_flex_code = :gl' ||
67 ' order by segment_num';
68 OPEN v_R_COACursor FOR v_R_SQL USING p_coa_id, l_app_id, l_gl_short_name;
69
70 LOOP
71 FETCH v_R_COACursor INTO l_r_segment_num, l_r_column_name, l_r_display_size;
72 EXIT WHEN v_R_COACursor%NOTFOUND;
73 l_chart(l_r_index).segment_num := l_r_segment_num;
74 l_chart(l_r_index).application_column_name := l_r_column_name;
75 l_chart(l_r_index).display_size := l_r_display_size;
76 l_r_index := l_r_index + 1;
77 END LOOP;
78 CLOSE v_R_COACursor;
79 p_count := l_r_index;
80
81 END coa_info;
82
83 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
84 --+Get the chart of account information for each segment in the chart of accounts
85 --+ p_count is the index to the coa_table.
86 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
87 procedure Get_Detail_coa_info (
88 p_coa_id IN NUMBER,
89 p_count IN Number,
90 p_column_name IN OUT NOCOPY varchar2,
91 p_display_size IN OUT NOCOPY number)
92 IS
93 v_R_COACursor t_RefCur;
94 v_R_SQL varchar2(3000);
95 l_r_segment_num FND_ID_FLEX_SEGMENTS.SEGMENT_NUM%TYPE;
96 l_r_column_name FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME%TYPE;
97 l_r_display_size FND_ID_FLEX_SEGMENTS.DISPLAY_SIZE%TYPE;
98 l_app_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE;
99 l_gl_short_name FND_ID_FLEX_SEGMENTS.ID_FLEX_CODE%TYPE;
100 l_r_index number;
101 l_chart coa_table;
102 BEGIN
103 l_r_index := 1;
104 l_app_id := 101;
105 l_gl_short_name := 'GL#';
106 v_R_SQL := 'SELECT s.SEGMENT_NUM, ' ||
107 's.APPLICATION_COLUMN_NAME, ' ||
108 's.DISPLAY_SIZE ' ||
109 'FROM FND_FLEX_VALUE_SETS vs, ' ||
110 'FND_ID_FLEX_SEGMENTS s ' ||
111 'WHERE vs.flex_value_set_id = s.flex_value_set_id ' ||
112 'AND s.ID_FLEX_NUM = :coa_id ' ||
113 'AND s.application_id = :app_id ' ||
114 'AND s.id_flex_code = :gl_name' ||
115 ' order by segment_num';
116 OPEN v_R_COACursor FOR v_R_SQL USING p_coa_id, l_app_id, l_gl_short_name;
117 LOOP
118 FETCH v_R_COACursor INTO l_r_segment_num, l_r_column_name, l_r_display_size;
119 EXIT WHEN v_R_COACursor%NOTFOUND;
120 l_chart(l_r_index).segment_num := l_r_segment_num;
121 l_chart(l_r_index).application_column_name := l_r_column_name;
122 l_chart(l_r_index).display_size := l_r_display_size;
123 l_r_index := l_r_index + 1;
124 END LOOP;
125 CLOSE v_R_COACursor;
126 p_column_name := l_chart(p_count).application_column_name;
127 p_display_size := l_chart(p_count).display_size;
128
129 END Get_Detail_coa_info;
130 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
131 --+Get the email address of a specific user
132 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
133 function Get_eMAIL_Address(
134 p_user_name IN varchar2
135 ) return varchar2
136 IS
137 l_email_address FND_USER.EMAIL_ADDRESS%TYPE;
138 v_SQL varchar2(500);
139 CURSOR C IS
140 SELECT email_address
141 FROM fnd_user
142 WHERE user_name = p_user_name;
143
144 v_Users C%ROWTYPE;
145 begin
146 OPEN C;
147 FETCH C INTO v_Users;
148 if (C%FOUND) then
149 l_email_address := v_Users.email_address;
150 else
151 l_email_address := 'GETFAILURE';
152 end if;
153 CLOSE C;
154 return l_email_address;
155 end Get_eMAIL_Address;
156
157 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
158 --+Get the user id
159 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
160 function Get_User_ID(
161 user_name IN varchar2
162 ) return number
163 IS
164 l_user_id FND_USER.USER_ID%TYPE;
165 v_SQL1 varchar2(500);
166 begin
167 v_SQL1 := 'select user_id from fnd_user' ||
168 ' where user_name = :name';
169 EXECUTE IMMEDIATE v_SQL1 INTO l_user_id USING user_name;
170 return l_user_id;
171 end Get_User_ID;
172
173 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
174 --+Get the responsibility id
175 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
176 function Get_Resp_ID(
177 resp_name IN varchar2
178 ) return number
179 IS
180 l_resp_id number;
181 v_SQL1 varchar2(500);
182 l_app_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE;
183 begin
184 l_app_id := 101;
185 v_SQL1 := 'select responsibility_id from fnd_responsibility_tl' ||
186 ' where responsibility_name = :name' ||
187 ' and application_id = :app_id' ||
188 ' and language = :l';
189 EXECUTE IMMEDIATE v_SQL1 INTO l_resp_id USING resp_name, l_app_id, userenv('LANG');
190 return l_resp_id;
191 end Get_Resp_ID;
192 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
193 --+Get the name of the ledger
194 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
195 function Get_Ledger_Name(
196 ledger_id IN number
197 ) return varchar2
198 IS
199 l_ledger_name GL_LEDGERS.NAME%TYPE;
200 v_SQL1 varchar2(500);
201 begin
202 v_SQL1 := 'select name from gl_ledgers' ||
203 ' WHERE ledger_id = :l_id';
204 EXECUTE IMMEDIATE v_SQL1 INTO l_ledger_name USING ledger_id;
205 return l_ledger_name;
206 end Get_Ledger_Name;
207
208 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
209 --+Get the suspense flag for a specific ledger
210 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
211 function Get_Suspense_Flag(
212 ledger_id IN number
213 ) return varchar2
214 IS
215 l_suspense_flag GL_LEDGERS.SUSPENSE_ALLOWED_FLAG%TYPE;
216 v_SQL1 varchar2(500);
217 begin
218 v_SQL1 := 'select suspense_allowed_flag from gl_ledgers' ||
219 ' WHERE ledger_id = :l_id';
220 EXECUTE IMMEDIATE v_SQL1 INTO l_suspense_flag USING ledger_id;
221 return l_suspense_flag;
222 end Get_Suspense_Flag;
223 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
224 --+Get the average balance flag for a specific ledger
225 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
226 function Get_Daily_Balance_Flag(
227 ledger_id IN number
228 ) return varchar2
229 IS
230 l_balance_flag GL_LEDGERS.ENABLE_AVERAGE_BALANCES_FLAG%TYPE;
231 v_SQL1 varchar2(500);
232 begin
233 v_SQL1 := 'select enable_average_balances_flag from gl_ledgers' ||
234 ' where ledger_id = :ledger_id';
235 EXECUTE IMMEDIATE v_SQL1 INTO l_balance_flag USING ledger_id;
236 return l_balance_flag;
237 end Get_Daily_Balance_Flag;
238
239 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
240 --+Get the consolidation ledger flag for a specific ledger
241 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
242 function Get_Cons_ledger_Flag(
243 ledger_id IN number
244 ) return varchar2
245 IS
246 l_cons_ledger_flag GL_LEDGERS.CONSOLIDATION_LEDGER_FLAG%TYPE;
247 v_SQL1 varchar2(500);
248 begin
249 v_SQL1 := 'select consolidation_ledger_flag from gl_ledgers' ||
250 ' where ledger_id = :ledger_id';
251 EXECUTE IMMEDIATE v_SQL1 INTO l_cons_ledger_flag USING ledger_id;
252 return l_cons_ledger_flag;
253 end Get_Cons_ledger_Flag;
254 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
255 --+Get the currency code for a specific ledger
256 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
257 function Get_Currency_Code(
258 ledger_id IN number
259 ) return varchar2
260 IS
261 l_currency_code GL_LEDGERS.CURRENCY_CODE%TYPE;
262 v_SQL1 varchar2(500);
263 begin
264 v_SQL1 := 'SELECT CURRENCY_CODE FROM gl_ledgers' ||
265 ' WHERE ledger_id = :ledger_id';
266 EXECUTE IMMEDIATE v_SQL1 INTO l_currency_code USING ledger_id;
267 return l_currency_code;
268 end Get_Currency_Code;
269 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
270 --+Get the chart of accounts id for a specific ledger
271 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
272 function Get_COA_Id(
273 ledger_id IN number
274 ) return number
275 IS
276 l_coa_id GL_LEDGERS.CHART_OF_ACCOUNTS_ID%TYPE;
277 v_SQL1 varchar2(500);
278 begin
279 v_SQL1 := 'select chart_of_accounts_id from gl_ledgers' ||
280 ' where ledger_id = :ledger_id';
281 EXECUTE IMMEDIATE v_SQL1 INTO l_coa_id USING ledger_id;
282 return l_coa_id;
283 end Get_COA_Id;
284
285 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
286 --+Does the input period name exists in this ledger?
287 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
288 function Period_Exists(
289 ledger_id IN number,
290 period_name IN varchar2
291 ) return number
292 IS
293 l_count number;
294 v_SQL1 varchar2(1000);
295 begin
296 v_SQL1 := 'select count(*) from gl_periods p, gl_ledgers l ' ||
297 'where p.period_set_name = l.period_set_name ' ||
298 'and p.period_type = l.accounted_period_type ' ||
299 'and l.ledger_id = :s ' ||
300 'and p.period_name = :pd';
301 EXECUTE IMMEDIATE v_SQL1 INTO l_count USING ledger_id, period_name;
302 return l_count;
303 end Period_Exists;
304
305 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
306 --+Get information on a specific period for a specific ledger
307 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
308 procedure Get_Period_Info(
309 ledger_id IN number,
310 period_name IN varchar2,
311 start_date OUT NOCOPY varchar2,
312 end_date OUT NOCOPY varchar2,
313 quarter_date OUT NOCOPY varchar2,
314 year_date OUT NOCOPY varchar2
315 ) IS
316 v_PDSQL varchar2(1000);
317
318 BEGIN
319 v_PDSQL := 'select p.start_date, p.end_date, p.quarter_start_date, ' ||
320 'p.year_start_date from ' ||
321 'gl_periods p, gl_ledgers l ' ||
322 'where p.period_set_name = l.period_set_name ' ||
323 'and p.period_type = l.accounted_period_type ' ||
324 'and l.ledger_id = :s ' ||
325 'and p.period_name = :pd';
326 EXECUTE IMMEDIATE v_PDSQL INTO start_date, end_date, quarter_date, year_date USING ledger_id, period_name;
327
328 END Get_Period_Info;
329
330 procedure GLOBAL_INITIALIZE(
331 user_id in number,
332 resp_id in number,
333 resp_appl_id in number,
334 security_group_id in number default 0)
335 IS
336 BEGIN
337 fnd_global.Apps_Initialize(user_id, resp_id, resp_appl_id);
338 END GLOBAL_INITIALIZE;
339 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
340 --+Get user id, responsibility id
341 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
342 function Get_Login_Ids(
343 p_user_name IN varchar2,
344 p_resp_name IN varchar2,
345 user_id OUT NOCOPY number,
346 resp_id OUT NOCOPY number
347 )return number
348 IS
349 v_SQL1 varchar2(500);
350 cursor user_id_cursor is
351 select user_id
352 from fnd_user
353 where user_name = p_user_name;
354 return_value boolean;
355 l_app_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE;
356 BEGIN
357 return_value := FALSE;
358 open user_id_cursor;
359 fetch user_id_cursor into user_id;
360 return_value := user_id_cursor%FOUND;
361 close user_id_cursor;
362 if NOT return_value then
363 return 1;
364 end if;
365 l_app_id := 101;
366 v_SQL1 := 'select responsibility_id from fnd_responsibility_tl ' ||
367 'where responsibility_name = :name ' ||
368 'and application_id = :app_id ' ||
369 'and language = :l';
370 EXECUTE IMMEDIATE v_SQL1 INTO resp_id USING p_resp_name, l_app_id, userenv('LANG');
371 return 0;
372
373 END Get_Login_Ids;
374
375 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
376 --+Validate responsibility name
377 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
378 function Validate_Resp(
379 resp_name IN varchar2
380 ) return number
381 IS
382 l_count number;
383 v_SelectSQL2 varchar2(1000);
384 l_error_code number;
385 l_app_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE;
386 begin
387 l_error_code := 0;
388 l_app_id := 101;
389 l_count := 0;
390 v_SelectSQL2 := 'select count(*) from fnd_responsibility_tl ' ||
391 'where responsibility_name = :resp_name ' ||
392 'and application_id = :app_id ' ||
393 'and language = :l';
394 EXECUTE IMMEDIATE v_SelectSQL2 INTO l_count USING resp_name, l_app_id,userenv('LANG');
395 if l_count = 0 then
396 l_error_code := 2;
397 return l_error_code;
398 end if;
399 return l_error_code;
400
401 end Validate_Resp;
402 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
403 --+Check for Oracle Application Menu exclusion for Journal Import and Post
404 --+Can not use fnd_function.test to test the accessiblility of GL_SU_J_IMPORT
405 --+because it is a menu not a function. Therefore, is forced to access the
406 --+fnd_resp_functions table direcly to get the exclusion info for this menu
407 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
408 function Menu_Validation(
409 user_id IN number,
410 resp_id IN number,
411 app_id IN number,
412 import_flag IN varchar2,
413 post_flag IN varchar2
414 ) return varchar2
415 IS
416 l_menu number;
417 v_SQL varchar2(500);
418 l_count number;
419 l_rule_type varchar2(1);
420 begin
421 fnd_global.Apps_Initialize(user_id, resp_id, app_id);
422 l_rule_type := 'M';
423 IF (import_flag = 'Y' ) THEN
424 l_menu := 67905;
425 v_SQL := 'select count(*) from fnd_resp_functions' ||
426 ' where application_id = :app_id' ||
427 ' and responsibility_id = :r_id' ||
428 ' and action_id = :menu_id' ||
429 ' and rule_type = :r';
430 EXECUTE IMMEDIATE v_SQL INTO l_count USING app_id, resp_id, l_menu, l_rule_type;
431
432 --+ fnd_resp_functions contains all functions that are excluded from this responsibility
433 if l_count > 0 then
434 return 'IMPORT FAIL';
435 end if;
436 IF (FND_FUNCTION.TEST('GLXJIRUN') = FALSE) THEN
437 return 'IMPORT FAIL';
438 END IF;
439
440 END IF;
441 IF (post_flag = 'Y' ) THEN
442 IF (FND_FUNCTION.TEST('GLXSTAPO') = FALSE) or (FND_FUNCTION.TEST('GLXJEPST') = FALSE) or
443 (FND_FUNCTION.TEST('GLXCOWRK_P') = FALSE) or (FND_FUNCTION.TEST('GLPAUTOP_A') = FALSE)THEN
444 return 'POST FAIL';
445 END IF;
446 END IF;
447
448 return 'SUCCESS';
449 end Menu_Validation;
450
451 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
452 --+Get the budget version id
453 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
454 function Get_Budget_Version_ID(
455 p_user_id IN number,
456 p_resp_id IN number,
457 p_app_id IN number,
458 p_budget_name IN varchar2
459 ) return number
460 IS
461 budget_version_id number;
462 temp_n number;
463 cursor budget_cursor is
464 select budget_version_id
465 from gl_budget_versions
466 where budget_name = p_budget_name;
467 return_value boolean;
468 begin
469 return_value := TRUE;
470 fnd_global.Apps_Initialize(p_user_id, p_resp_id, p_app_id);
471 open budget_cursor;
472 fetch budget_cursor into temp_n;
473 return_value := budget_cursor%FOUND;
474 close budget_cursor;
475 if return_value then
476 budget_version_id := temp_n;
477 else
478 budget_version_id := -100;
479 end if;
480 return budget_version_id;
481
482 end Get_Budget_Version_ID;
483 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
484 --+Get the ledger id
485 --+also check if this ledger is granted read/write access right.
486 --+returns the ledger id only if the ledger has both read/write access right.
487 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
488 function Get_Ledger_ID(
489 p_user_id IN number,
490 p_resp_id IN number,
491 p_app_id IN number,
492 p_access_set_id OUT NOCOPY number,
493 p_access_set OUT NOCOPY varchar2,
494 p_access_code OUT NOCOPY varchar2
495 ) return number
496 IS
497 profile_val VARCHAR2(100);
498 defined_flag BOOLEAN;
499 l_ledger_id NUMBER;
500 l_access_set_id number;
501 l_access_code varchar2(1);
502 v_ReturnCursor t_RefCur;
503 v_SQL varchar2(500);
504 begin
505 fnd_global.Apps_Initialize(p_user_id, p_resp_id, p_app_id);
506 fnd_profile.get_specific(name_z => 'GL_ACCESS_SET_ID',
507 val_z => profile_val,
508 defined_z=> defined_flag);
509 if(profile_val IS NULL OR defined_flag = FALSE) then
510 app_exception.raise_exception;
511 end if;
512 l_access_set_id := to_number(profile_val);
513 SELECT NAME
514 INTO p_access_set
515 FROM GL_ACCESS_SETS
516 WHERE ACCESS_SET_ID = l_access_set_id;
517
518 v_SQL := 'select default_ledger_id from gl_access_sets' ||
519 ' where access_set_id = :s';
520 OPEN v_ReturnCursor FOR v_SQL USING l_access_set_id;
521 FETCH v_ReturnCursor INTO l_ledger_id;
522 IF v_ReturnCursor%NOTFOUND THEN
523 l_ledger_id := -1; --no default ledger is found
524 END IF;
525 CLOSE v_ReturnCursor;
526
527 IF l_ledger_id >= 0 THEN
528 v_SQL := 'select access_privilege_code from gl_access_set_assignments' ||
529 ' where access_set_id = :s and ledger_id = :l';
530 OPEN v_ReturnCursor FOR v_SQL USING l_access_set_id, l_ledger_id;
531 FETCH v_ReturnCursor INTO p_access_code;
532 CLOSE v_ReturnCursor;
533 END IF;
534 p_access_set_id := l_access_set_id;
535 return l_ledger_id;
536 exception
537 when NO_DATA_FOUND then
538 return -1;
539 end Get_Ledger_ID;
540
541 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
542 --+get the group id for the target db
543 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
544 FUNCTION Get_Group_ID RETURN number
545 IS
546 CURSOR gp_id IS
547 SELECT GL_INTERFACE_CONTROL_S.NEXTVAL
548 FROM sys.DUAL;
549 l_group_id number;
550 BEGIN
551 OPEN gp_id;
552 FETCH gp_id INTO l_group_id;
553 CLOSE gp_id;
554 RETURN l_group_id;
555 END Get_Group_ID;
556 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
557 --+ grant delete, update, insert, select rights on
558 --+ gl_cons_interface_groupid table to a specific user
559 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
560 PROCEDURE Grant_Rights(
561 group_id IN number,
562 db_username IN varchar2
563 )
564 IS
565 l_table_name varchar2(30);
566 v_SQL varchar2(500);
567 errbuf varchar2(500);
568 BEGIN
569 l_table_name := 'GL_CONS_INTERFACE_' || group_id;
570 v_SQL := 'grant select, update, insert, delete on ' || l_table_name
571 || ' to ' || db_username;
572 EXECUTE IMMEDIATE v_SQL;
573 exception
574 when OTHERS then
575 errbuf := SUBSTR(SQLCODE || ' ; ' || SQLERRM, 1, 255);
576 errbuf := errbuf;
577 END Grant_Rights;
578
579 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
580 --+ the group_ID will be returned
581 --+ the table with the given name and its index will be created
582 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
583 PROCEDURE Create_Interface_Table(
584 group_id IN number,
585 db_username IN varchar2
586 )
587 IS
588 l_table_name varchar2(30);
589 BEGIN
590 l_table_name := 'GL_CONS_INTERFACE_' || group_id;
591 gl_journal_import_pkg.create_table(l_table_name);
592 Grant_Rights(group_id, db_username);
593 END Create_Interface_Table;
594
595 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
596 --+this procedure can be called from a remote database. It will initialize the user id
597 --+responsibility id and application id.
598 --+load the input group id to the parallel gl_interface table and
599 --+update the gl_interface_control table with the necessary info for Journal Import.
600 --+ call this procedure to populate the gl_interface_control table
601 --+ PROCEDURE populate_interface_control(
602 --+ user_je_source_name VARCHAR2,
603 --+ group_id IN OUT NUMBER,
604 --+ ledger_id NUMBER,
605 --+ interface_run_id IN OUT NUMBER,
606 --+ table_name VARCHAR2 DEFAULT NULL,
607 --+ processed_data_action VARCHAR2 DEFAULT NULL);
608 --+ interface_run_id will be returned from this procedure
609 --+ when gl_interface_control table is populated here, the new gl_cons_interface_n
610 --+ table name is also saved in the table for Journal Import to use.
611 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
612 function Apps_Initialize (
613 user_id IN number,
614 resp_id IN number,
615 app_id IN number,
616 ledger_id IN number,
617 group_id IN number,
618 pd_name IN varchar2,
619 actual_flag IN varchar2,
620 avg_flag IN varchar2
621 ) return number
622 IS
623 l_group_id number;
624 inter_run_id number;
625 l_table_name varchar2(30);
626 v_UpdateSQL varchar2(500);
627 v_SelectSQL varchar2(1000);
628 l_reference1 varchar2(15);
629 l_user_je_source_name varchar2(25);
630 l_adb_je_source varchar2(25);
631 l_je_source varchar2(25);
632 begin
633 l_group_id := group_id;
634 fnd_global.Apps_Initialize(user_id, resp_id, app_id);
635 l_adb_je_source := 'Average Consolidation';
636 l_je_source := 'Consolidation';
637
638 l_table_name := 'GL_CONS_INTERFACE_' || group_id;
639 IF avg_flag = 'Y' THEN
640 v_SelectSQL := 'select user_je_source_name from gl_je_sources ' ||
641 'WHERE je_source_name = :s_name';
642 EXECUTE IMMEDIATE v_SelectSQL INTO l_user_je_source_name USING l_adb_je_source;
643 gl_journal_import_pkg.populate_interface_control(
644 --+ 'Average Consolidation',
645 l_user_je_source_name,
646 l_group_id,
647 ledger_id,
648 inter_run_id,
649 l_table_name,
650 'R'); --+drop interface table
651
652 v_UpdateSQL := 'UPDATE ' || l_table_name ||
653 ' SET group_id = :group_id' ||
654 ' WHERE ledger_id = :ledger_id' ||
655 ' AND period_name = :period_name' ||
656 ' AND actual_flag = :flag' ||
657 ' AND user_je_source_name = :s_name';
658 EXECUTE IMMEDIATE v_UpdateSQL USING group_id, ledger_id, pd_name,
659 actual_flag, l_user_je_source_name;
660 ELSE
661 v_SelectSQL := 'select user_je_source_name from gl_je_sources ' ||
662 'WHERE je_source_name = :s_name';
663 EXECUTE IMMEDIATE v_SelectSQL INTO l_user_je_source_name USING l_je_source;
664 gl_journal_import_pkg.populate_interface_control(
665 --+ 'Consolidation',
666 l_user_je_source_name,
667 l_group_id,
668 ledger_id,
669 inter_run_id,
670 l_table_name,
671 'R'); --+drop interface table
672
673 v_UpdateSQL := 'UPDATE ' || l_table_name ||
674 ' SET group_id = :group_id' ||
675 ' WHERE ledger_id = :ledger_id' ||
676 ' AND period_name = :period_name' ||
677 ' AND actual_flag = :flag' ||
678 ' AND user_je_source_name = :s_name';
679 EXECUTE IMMEDIATE v_UpdateSQL USING group_id, ledger_id, pd_name,
680 actual_flag, l_user_je_source_name;
681 END IF;
682
683 return inter_run_id;
684 end Apps_Initialize;
685
686 --+set_mode() is necessary to get rid of the ORA= 2074
687 --+Error -2074: ORA-02074: cannot SET SAVEPOINT in a distributed transaction
688 --+ORA-06512: at "APPS.FND_REQUEST", line 2434
689 --+ORA-06512: at "APPS.GL_FND_REQUEST_PKG", line 54
690 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
691 --+Submit a concurrent request to do Journal Import
692 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
693 function Run_Journal_Import(
694 user_id IN number,
695 resp_id IN number,
696 app_id IN number,
697 inter_run_id IN number,
698 ledger_id IN number,
699 csj_flag IN VARCHAR2
700 ) return number
701 IS
702 reqid number;
703 value_return boolean;
704 begin
705 fnd_global.Apps_Initialize(user_id, resp_id, app_id);
706 value_return := fnd_request.set_mode(TRUE);
707 reqid := fnd_request.submit_request(
708 'SQLGL',
709 'GLLEZL',
710 '',
711 '',
712 FALSE,
713 to_char(inter_run_id),
714 to_char(ledger_id),
715 'N', '', '',
716 csj_flag,
717 'N',
718 --+ 'NODEL',
719 --+ '',
720 chr(0),
721 '', '', '', '', '', '', '', '', '', '',
722 '', '', '', '', '', '', '', '', '', '',
723 '', '', '', '', '', '', '', '', '', '',
724 '', '', '', '', '', '', '', '', '', '',
725 '', '', '', '', '', '', '', '', '', '',
726 '', '', '', '', '', '', '', '', '', '',
727 '', '', '', '', '', '', '', '', '', '',
728 '', '', '', '', '', '', '', '', '', '',
729 '', '', '', '', '', '', '', '', '', '',
730 '');
731
732 return reqid;
733 END Run_Journal_Import;
734
735 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
736 --+Get either postable rows or posted rows, decided by the status ('U' or 'P')
737 --+each batch may have multiple headers
738 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
739 procedure Get_Postable_Rows(
740 ledger_id IN number,
741 pd_name IN varchar2,
742 batch_id IN number,
743 status IN varchar2,
744 actual_flag IN varchar2,
745 avg_flag IN varchar2,
746 postable_rows OUT NOCOPY number
747 ) IS
748 v_SelectSQL varchar2(500);
749 v_SelectSQL2 varchar2(500);
750 v_ReturnCursor t_RefCur;
751 v_Headers gl_je_headers%ROWTYPE;
752 v_header_id number;
753 v_count number;
754 v_temp number;
755 l_adb_je_source GL_JE_HEADERS.JE_SOURCE%type;
756 l_je_source GL_JE_HEADERS.JE_SOURCE%type;
757
758 BEGIN
759 v_temp := 0;
760 l_adb_je_source := 'Average Consolidation';
761 l_je_source := 'Consolidation';
762 IF avg_flag = 'Y' THEN
763 v_SelectSQL := 'select * from gl_je_headers' ||
764 ' where status = :s and je_batch_id = :b_id' ||
765 ' and ledger_id = :sid' ||
766 ' and je_source = :je' ||
767 ' and period_name = :name' ||
768 ' and actual_flag = :flag';
769 OPEN v_ReturnCursor FOR v_SelectSQL USING status, batch_id, ledger_id,
770 l_adb_je_source, pd_name, actual_flag;
771 ELSE
772 v_SelectSQL := 'select * from gl_je_headers' ||
773 ' where status = :s and je_batch_id = :b_id' ||
774 ' and ledger_id = :sid' ||
775 ' and je_source = :je' ||
776 ' and period_name = :name' ||
777 ' and actual_flag = :flag';
778 OPEN v_ReturnCursor FOR v_SelectSQL USING status, batch_id, ledger_id,
779 l_je_source, pd_name, actual_flag;
780 END IF;
781 LOOP --+for every batch in this transfer
782 FETCH v_ReturnCursor INTO v_Headers;
783 EXIT WHEN v_ReturnCursor%NOTFOUND;
784 v_header_id := v_Headers.je_header_id;
785 v_SelectSQL2 := 'select count(*) from gl_je_lines' ||
786 ' where je_header_id = :id';
787 EXECUTE IMMEDIATE v_SelectSQL2 INTO v_count USING v_header_id;
788 v_temp := v_temp + v_count;
789 END LOOP;
790 CLOSE v_ReturnCursor;
791 postable_rows := v_temp;
792 --+dbms_output.put_line('postatble rows are' || postable_rows || ' rows');
793
794 END Get_Postable_Rows;
795
796 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
797 --+Do Journal Post
798 --+need to set the status to 'S' in gl_je_batches, means selected to be posted
799 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
800 PROCEDURE Run_Journal_Post(
801 user_id IN number,
802 resp_id IN number,
803 app_id IN number,
804 ledger_id IN number,
805 pd_name IN varchar2,
806 group_id IN number,
807 import_request_id IN number,
808 batch_id IN number,
809 actual_flag IN varchar2,
810 access_set_id IN number,
811 post_run_id OUT NOCOPY number,
812 reqid OUT NOCOPY number
813
814 )
815 IS
816 CURSOR get_new_id IS
817 SELECT gl_je_posting_s.NEXTVAL
818 FROM sys.dual;
819
820 cursor ledger is
821 select chart_of_accounts_id
822 from gl_ledgers
823 where ledger_id = ledger_id;
824
825 value_return boolean;
826 l_coa_id number;
827 l_ledger_id NUMBER(15);
828 l_count number;
829 v_UpdateSQL varchar2(1000);
830 v_SelectSQL varchar2(1000);
831 v_Ledger_SQL varchar2(1000);
832 v_ALC_SQL varchar2(1000);
833 l_batch_name varchar2(100);
834 v_ReturnCursor t_RefCur;
835 v_Ledger_Cursor t_RefCur;
836 v_ALC_Cursor t_RefCur;
837 v_Batches gl_je_batches%ROWTYPE;
838
839 l_status varchar2(1);
840 l_request_id number(15);
841 dummy NUMBER(1);
842 l_ok_to_post boolean;
843 call_status BOOLEAN;
844 rphase VARCHAR2(80);
845 rstatus VARCHAR2(80);
846 dphase VARCHAR2(30);
847 dstatus VARCHAR2(30);
848 message VARCHAR2(240);
849 l_batch_status GL_JE_BATCHES.STATUS%TYPE;
850 l_budgetary_status GL_JE_BATCHES.BUDGETARY_CONTROL_STATUS%TYPE;
851
852 BEGIN
853 l_count := 1;
854 l_ok_to_post := TRUE;
855 --+dbms_output.put_line('start journal post');
856 fnd_global.Apps_Initialize(user_id, resp_id, app_id);
857 open ledger;
858 fetch ledger into l_coa_id;
859 close ledger;
860 l_coa_id := get_coa_id(ledger_id);
861
862 value_return := fnd_request.set_mode(TRUE);
863 --+bug fix for bug#3278513, check the status of this batch
864 --+if it is 'S'= SELECTED and concurrent request is found and not COMPLETED
865 --+or it is 'I' = UNDERWAY and concurrent request is RUNNING then don't post
866 --+if the status is 'P', don't post again, just exit
867 --+ lock this row too!
868 v_SelectSQL := 'select status, request_id, budgetary_control_status ' ||
869 'from gl_je_batches ' ||
870 'WHERE je_batch_id = :b_id ' ||
871 'and default_period_name = :pd ' ||
872 -- 'and ledger_id = :ledger ' ||
873 'and actual_flag = :flag ' ||
874 'FOR UPDATE OF status, posting_run_id';
875 EXECUTE IMMEDIATE v_SelectSQL INTO l_status, l_request_id, l_budgetary_status USING batch_id, pd_name, actual_flag;
876
877 --+ if status is 'P', then can't post the batch again. get out of here.
878 --+ if the status is neither 'P' nor 'I' nor 'S', then it is ok to post
879 IF (l_status = 'P') THEN
880 l_ok_to_post := FALSE;
881 ELSIF (l_status IN ('S', 'I')) THEN
882 IF (l_budgetary_status = 'I') THEN
883 --+ added budgetary_control_status check for the bug 5003755.
884 l_ok_to_post := FALSE;
885 ELSIF (l_request_id IS NULL) THEN
886 --+ This should not happen but just in case
887 l_ok_to_post := FALSE;
888 ELSE
889 call_status :=
890 FND_CONCURRENT.GET_REQUEST_STATUS(l_request_id,
891 null,
892 null,
893 rphase,
894 rstatus,
895 dphase,
896 dstatus,
897 message);
898
899 IF (NOT call_status) THEN
900 l_ok_to_post := FALSE;
901
902 ELSIF (l_status = 'S' AND ( dphase = 'COMPLETE'
903 AND (dstatus = 'CANCELLED' OR dstatus = 'TERMINATED'))) THEN
904 l_ok_to_post := TRUE;
905
906 ELSIF (l_status = 'I' AND
907 dphase <> 'RUNNING') THEN
908 l_ok_to_post := TRUE;
909
910 ELSE
911 l_ok_to_post := FALSE;
912
913 END IF;
914 END IF; --+IF (l_request_id IS NULL) THEN
915 END IF; --+ IF (l_status = 'P') THEN
916 IF (l_ok_to_post) THEN
917 l_batch_status := 'S';
918 open get_new_id;
919 fetch get_new_id into post_run_id;
920 close get_new_id;
921 v_UpdateSQL := 'UPDATE gl_je_batches ' ||
922 'SET posting_run_id = :post_run_id, ' ||
923 'status = :bs ' ||
924 'WHERE je_batch_id = :b_id ' ||
925 'and default_period_name = :pd ' ||
926 -- 'and ledger_id = :ledger ' ||
927 'and actual_flag = :flag';
928 EXECUTE IMMEDIATE v_UpdateSQL USING post_run_id,l_batch_status,
929 batch_id, pd_name, actual_flag;
930 v_SelectSQL := 'select name from gl_je_batches ' ||
931 'WHERE je_batch_id = :b_id';
932 EXECUTE IMMEDIATE v_SelectSQL INTO l_batch_name USING batch_id;
933 --+dbms_output.put_line('get batch name');
934
935 v_Ledger_SQL := 'SELECT max(JEH.ledger_id) ' ||
936 'FROM GL_JE_HEADERS JEH ' ||
937 'WHERE JEH.je_batch_id = :je_batch_id ' ||
938 'GROUP BY JEH.je_batch_id ' ||
939 'HAVING count(distinct JEH.ledger_id) = 1';
940 OPEN v_Ledger_Cursor FOR v_Ledger_SQL USING batch_id;
941 FETCH v_Ledger_Cursor INTO l_ledger_id;
942 IF v_Ledger_Cursor%NOTFOUND THEN
943 l_ledger_id := -99;
944 ELSE
945 v_ALC_SQL := 'SELECT 1 ' ||
946 'FROM GL_JE_HEADERS JEH ' ||
947 'WHERE JEH.je_batch_id = :je_batch_id ' ||
948 'AND JEH.actual_flag != ' || '''' || 'B' || '''' ||
949 ' AND JEH.reversed_je_header_id IS NULL ' ||
950 'AND EXISTS ' ||
951 '(SELECT 1 ' ||
952 'FROM GL_LEDGER_RELATIONSHIPS LRL ' ||
953 'WHERE LRL.source_ledger_id = JEH.ledger_id ' ||
954 'AND LRL.target_ledger_category_code = ' ||
955 '''' || 'ALC' || '''' ||
956 ' AND LRL.relationship_type_code IN ( ' ||
957 '''' || 'JOURNAL' || '''' || ', ' ||
958 '''' || 'SUBLEDGER' || '''' || ') ' ||
959 'AND LRL.application_id = 101 ' ||
960 'AND LRL.relationship_enabled_flag = ' ||
961 '''' || 'Y' || '''' ||
962 ' AND JEH.je_source NOT IN ' ||
963 '(SELECT INC.je_source_name ' ||
964 'FROM GL_JE_INCLUSION_RULES INC ' ||
965 'WHERE INC.je_rule_set_id = ' ||
966 'LRL.gl_je_conversion_set_id ' ||
967 'AND INC.je_source_name = JEH.je_source ' ||
968 'AND INC.je_category_name = ' ||
969 '''' || 'Other' || '''' ||
970 ' AND INC.include_flag = ' ||
971 '''' || 'N' || '''' ||
972 ' AND INC.user_updatable_flag = ' ||
973 '''' || 'N' || '''' || '))';
974 OPEN v_ALC_Cursor FOR v_ALC_SQL USING batch_id;
975 FETCH v_ALC_Cursor INTO dummy;
976 IF v_Ledger_Cursor%FOUND THEN
977 l_ledger_id := -99;
978 END IF;
979 CLOSE v_ALC_Cursor;
980 END IF;
981 CLOSE v_Ledger_Cursor;
982
983 IF (l_ledger_id = -99) THEN
984 reqid := fnd_request.submit_request(
985 'SQLGL',
986 'GLPPOS',
987 '',
988 '',
989 FALSE,
990 to_char(l_ledger_id),
991 to_char(access_set_id),
992 to_char(l_coa_id),
993 to_char(post_run_id),
994 chr(0),'', '', '', '',
995 '', '', '', '', '', '', '', '', '', '',
996 '', '', '', '', '', '', '', '', '', '',
997 '', '', '', '', '', '', '', '', '', '',
998 '', '', '', '', '', '', '', '', '', '',
999 '', '', '', '', '', '', '', '', '', '',
1000 '', '', '', '', '', '', '', '', '', '',
1001 '', '', '', '', '', '', '', '', '', '',
1002 '', '', '', '', '', '', '', '', '', '',
1003 '', '', '', '', '', '', '', '', '', '',
1004 '');
1005 ELSE
1006 reqid := fnd_request.submit_request(
1007 'SQLGL',
1008 'GLPPOSS',
1009 '',
1010 '',
1011 FALSE,
1012 to_char(l_ledger_id),
1013 to_char(access_set_id),
1014 to_char(l_coa_id),
1015 to_char(post_run_id),
1016 chr(0),'', '', '', '',
1017 '', '', '', '', '', '', '', '', '', '',
1018 '', '', '', '', '', '', '', '', '', '',
1019 '', '', '', '', '', '', '', '', '', '',
1020 '', '', '', '', '', '', '', '', '', '',
1021 '', '', '', '', '', '', '', '', '', '',
1022 '', '', '', '', '', '', '', '', '', '',
1023 '', '', '', '', '', '', '', '', '', '',
1024 '', '', '', '', '', '', '', '', '', '',
1025 '', '', '', '', '', '', '', '', '', '',
1026 '');
1027 END IF;
1028 --+dbms_output.put_line('after post submitted' || reqid);
1029
1030 IF (reqid <> 0) THEN
1031 v_UpdateSQL := 'UPDATE gl_je_batches ' ||
1032 'SET request_id = :request_id ' ||
1033 'WHERE je_batch_id = :b_id ' ||
1034 'and default_period_name = :pd ' ||
1035 -- 'and ledger_id = :ledger ' ||
1036 'and posting_run_id = :post_run_id ' ||
1037 'and actual_flag = :flag';
1038 EXECUTE IMMEDIATE v_UpdateSQL USING reqid, batch_id, pd_name, post_run_id, actual_flag;
1039 END IF;
1040 ELSE --+don't post, return
1041 post_run_id := 0;
1042 reqid := 0;
1043 END IF; --+IF (l_ok_to_post) THEN
1044 --+ commit; do the commit in the calling routine
1045 END Run_Journal_Post;
1046
1047 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1048 --+Verify Journal Import by the number of rows left in the
1049 --+gl_interface_control table
1050 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1051 PROCEDURE Verify_Journal_Import(
1052 p_group_id IN number,
1053 result OUT NOCOPY varchar2
1054 )
1055 IS
1056 l_count number;
1057 v_SelectSQL varchar2(300);
1058 BEGIN
1059 v_SelectSQL := 'select count(*) from gl_interface_control '||
1060 'where group_id = :group_id';
1061 EXECUTE IMMEDIATE v_SelectSQL INTO l_count USING p_group_id;
1062 if l_count = 0 then
1063 result := 'SUCCESS';
1064 else
1065 result := 'FAILURE';
1066 end if;
1067 END Verify_Journal_Import;
1068
1069 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1070 --+Verify Journal Post by comparing th epostable rows before Journal Post and
1071 --+ the posted rows after Journal Post.
1072 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1073 PROCEDURE Verify_Journal_Post(
1074 l_pd_name IN varchar2,
1075 postable_rows IN number,
1076 l_ledger_id IN number,
1077 l_batch_id IN number,
1078 actual_flag IN varchar2,
1079 avg_flag IN varchar2,
1080 result OUT NOCOPY varchar2
1081 )
1082 IS
1083 l_count number;
1084 v_SelectSQL varchar2(500);
1085 BEGIN
1086 Get_Postable_Rows(l_ledger_id, l_pd_name, l_batch_id, 'P', actual_flag, avg_flag, l_count);
1087 if l_count = postable_rows then
1088 result := 'SUCCESS';
1089 else
1090 result := 'FAILURE';
1091 end if;
1092
1093 END Verify_Journal_Post;
1094
1095 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1096 --+Wait for concurrent request to complete
1097 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1098 procedure wait_for_request(
1099 request_id IN number,
1100 result OUT NOCOPY varchar2
1101 )
1102 IS
1103 phase varchar2(80);
1104 status varchar2(80);
1105 dev_phase varchar2(30);
1106 dev_status varchar2(30);
1107 message varchar2(240);
1108 success boolean;
1109 begin
1110 if request_id <> 0 then
1111 success := fnd_concurrent.wait_for_request(request_id,
1112 30, 360000, phase, status, dev_phase, dev_status,
1113 message);
1114 end if;
1115 If dev_phase = 'COMPLETE' AND
1116 dev_status In ('NORMAL','WARNING' ) Then
1117 result := 'COMPLETE:PASS';
1118 Else
1119 result := 'COMPLETE:FAIL';
1120 End If;
1121 end wait_for_request;
1122
1123 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1124 --+Get the status of the concurrent request
1125 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1126 function get_request_status(
1127 request_id IN number,
1128 result OUT NOCOPY varchar2
1129 ) return boolean
1130 IS
1131 phase varchar2(80);
1132 status varchar2(80);
1133 dev_phase varchar2(30);
1134 dev_status varchar2(30);
1135 message varchar2(240);
1136 success boolean;
1137 reqid number;
1138 begin
1139 if request_id <> 0 then
1140 reqid := request_id;
1141 success := fnd_concurrent.get_request_status(reqid,
1142 '', '', phase, status, dev_phase, dev_status,
1143 message);
1144 end if;
1145 If dev_phase = 'COMPLETE' AND
1146 dev_status In ('NORMAL','WARNING' ) Then
1147 result := 'COMPLETE:PASS';
1148 Else
1149 result := 'COMPLETE:FAIL';
1150 End If;
1151 return success;
1152 end get_request_status;
1153
1154 --+the code below works
1155 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1156 --+To test the procedure from SQL Navigator
1157 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1158 procedure Test_run
1159 is
1160 l_inter_run_id number;
1161 l_request_id number;
1162 l_group_id number;
1163
1164 BEGIN
1165 --+ l_group_id := Create_Interface_Table;
1166 --+transfer data from gl_interface to gl_cons_interface_groupid table
1167 --+populate the gl_interface_control table to prepare for Journal Import
1168 --+ l_inter_run_id := Apps_Initialize(1238,50023,101,42, l_group_id, 'Apr-01');
1169 l_inter_run_id := Apps_Initialize(1238,50023,101,42, 3873, 'Apr-01','A','Y');
1170 --+ l_request_id := Run_Journal_Import(1238, 50023, 101, l_inter_run_id,42);
1171
1172 END Test_Run;
1173
1174 END gl_ci_remote_invoke_pkg;