[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.12010000.2 2010/03/12 09:42:32 sommukhe ship $ */
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 NULL;
339 END GLOBAL_INITIALIZE;
340 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
341 --+Get user id, responsibility id
342 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
343 function Get_Login_Ids(
344 p_user_name IN varchar2,
345 p_resp_name IN varchar2,
346 user_id OUT NOCOPY number,
347 resp_id OUT NOCOPY number
348 )return number
349 IS
350 v_SQL1 varchar2(500);
351 cursor user_id_cursor is
352 select user_id
353 from fnd_user
354 where user_name = p_user_name;
355 return_value boolean;
356 l_app_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE;
357 BEGIN
358 return_value := FALSE;
359 open user_id_cursor;
360 fetch user_id_cursor into user_id;
361 return_value := user_id_cursor%FOUND;
362 close user_id_cursor;
363 if NOT return_value then
364 return 1;
365 end if;
366 l_app_id := 101;
367 v_SQL1 := 'select responsibility_id from fnd_responsibility_tl ' ||
368 'where responsibility_name = :name ' ||
369 'and application_id = :app_id ' ||
370 'and language = :l';
371 EXECUTE IMMEDIATE v_SQL1 INTO resp_id USING p_resp_name, l_app_id, userenv('LANG');
372 return 0;
373
374 END Get_Login_Ids;
375
376 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
377 --+Validate responsibility name
378 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
379 function Validate_Resp(
380 resp_name IN varchar2
381 ) return number
382 IS
383 l_count number;
384 v_SelectSQL2 varchar2(1000);
385 l_error_code number;
386 l_app_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE;
387 begin
388 l_error_code := 0;
389 l_app_id := 101;
390 l_count := 0;
391 v_SelectSQL2 := 'select count(*) from fnd_responsibility_tl ' ||
392 'where responsibility_name = :resp_name ' ||
393 'and application_id = :app_id ' ||
394 'and language = :l';
395 EXECUTE IMMEDIATE v_SelectSQL2 INTO l_count USING resp_name, l_app_id,userenv('LANG');
396 if l_count = 0 then
397 l_error_code := 2;
398 return l_error_code;
399 end if;
400 return l_error_code;
401
402 end Validate_Resp;
403 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
404 --+Check for Oracle Application Menu exclusion for Journal Import and Post
405 --+Can not use fnd_function.test to test the accessiblility of GL_SU_J_IMPORT
406 --+because it is a menu not a function. Therefore, is forced to access the
407 --+fnd_resp_functions table direcly to get the exclusion info for this menu
408 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
409 function Menu_Validation(
410 user_id IN number,
411 resp_id IN number,
412 app_id IN number,
413 import_flag IN varchar2,
414 post_flag IN varchar2
415 ) return varchar2
416 IS
417 l_menu number;
418 v_SQL varchar2(500);
419 l_count number;
420 l_rule_type varchar2(1);
421 begin
422 -- fnd_global.Apps_Initialize(user_id, resp_id, app_id);
423 l_rule_type := 'M';
424 IF (import_flag = 'Y' ) THEN
425 --l_menu := 67905;
426 SELECT menu_id INTO l_menu from fnd_menus where menu_name = 'GL_SU_J_IMPORT';
427 v_SQL := 'select count(*) from fnd_resp_functions' ||
428 ' where application_id = :app_id' ||
429 ' and responsibility_id = :r_id' ||
430 ' and action_id = :menu_id' ||
431 ' and rule_type = :r';
432 EXECUTE IMMEDIATE v_SQL INTO l_count USING app_id, resp_id, l_menu, l_rule_type;
433
434 --+ fnd_resp_functions contains all functions that are excluded from this responsibility
435 if l_count > 0 then
436 return 'IMPORT FAIL';
437 end if;
438 IF (FND_FUNCTION.TEST('GLXJIRUN') = FALSE) THEN
439 return 'IMPORT FAIL';
440 END IF;
441
442 END IF;
443 IF (post_flag = 'Y' ) THEN
444 IF (FND_FUNCTION.TEST('GLXSTAPO') = FALSE) or (FND_FUNCTION.TEST('GLXJEPST') = FALSE) or
445 (FND_FUNCTION.TEST('GLXCOWRK_P') = FALSE) or (FND_FUNCTION.TEST('GLPAUTOP_A') = FALSE)THEN
446 return 'POST FAIL';
447 END IF;
448 END IF;
449
450 return 'SUCCESS';
451 end Menu_Validation;
452
453 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
454 --+Get the budget version id
455 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
456 function Get_Budget_Version_ID(
457 p_user_id IN number,
458 p_resp_id IN number,
459 p_app_id IN number,
460 p_budget_name IN varchar2
461 ) return number
462 IS
463 budget_version_id number;
464 temp_n number;
465 cursor budget_cursor is
466 select budget_version_id
467 from gl_budget_versions
468 where budget_name = p_budget_name;
469 return_value boolean;
470 begin
471 return_value := TRUE;
472 --fnd_global.Apps_Initialize(p_user_id, p_resp_id, p_app_id);
473 open budget_cursor;
474 fetch budget_cursor into temp_n;
475 return_value := budget_cursor%FOUND;
476 close budget_cursor;
477 if return_value then
478 budget_version_id := temp_n;
479 else
480 budget_version_id := -100;
481 end if;
482 return budget_version_id;
483
484 end Get_Budget_Version_ID;
485 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
486 --+Get the ledger id
487 --+also check if this ledger is granted read/write access right.
488 --+returns the ledger id only if the ledger has both read/write access right.
489 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
490 function Get_Ledger_ID(
491 p_user_id IN number,
492 p_resp_id IN number,
493 p_app_id IN number,
494 p_access_set_id OUT NOCOPY number,
495 p_access_set OUT NOCOPY varchar2,
496 p_access_code OUT NOCOPY varchar2,
497 p_to_ledger_name IN VARCHAR2
498 ) return number
499 IS
500 profile_val VARCHAR2(100);
501 defined_flag BOOLEAN;
502 l_ledger_id NUMBER;
503 l_access_set_id number;
504 l_access_code varchar2(1);
505 v_ReturnCursor t_RefCur;
506 v_SQL varchar2(500);
507 begin
508 -- fnd_global.Apps_Initialize(p_user_id, p_resp_id, p_app_id);
509 fnd_profile.get_specific(name_z => 'GL_ACCESS_SET_ID',
510 val_z => profile_val,
511 defined_z=> defined_flag);
512 if(profile_val IS NULL OR defined_flag = FALSE) then
513 app_exception.raise_exception;
514 end if;
515 l_access_set_id := to_number(profile_val);
516 SELECT NAME
517 INTO p_access_set
518 FROM GL_ACCESS_SETS
519 WHERE ACCESS_SET_ID = l_access_set_id;
520
521 /*v_SQL := 'select default_ledger_id from gl_access_sets' ||
522 ' where access_set_id = :s';*/
523 v_SQL := 'select ledger_id from gl_ledgers' ||
524 ' where name = :s';
525 OPEN v_ReturnCursor FOR v_SQL USING p_to_ledger_name;
526 FETCH v_ReturnCursor INTO l_ledger_id;
527 IF v_ReturnCursor%NOTFOUND THEN
528 l_ledger_id := -1; --no default ledger is found
529 END IF;
530 CLOSE v_ReturnCursor;
531
532 IF l_ledger_id >= 0 THEN
533 v_SQL := 'select access_privilege_code from gl_access_set_assignments' ||
534 ' where access_set_id = :s and ledger_id = :l';
535 OPEN v_ReturnCursor FOR v_SQL USING l_access_set_id, l_ledger_id;
536 FETCH v_ReturnCursor INTO p_access_code;
537 CLOSE v_ReturnCursor;
538 END IF;
539 p_access_set_id := l_access_set_id;
540 return l_ledger_id;
541 exception
542 when NO_DATA_FOUND then
543 return -1;
544 end Get_Ledger_ID;
545
546 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
547 --+get the group id for the target db
548 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
549 FUNCTION Get_Group_ID RETURN number
550 IS
551 CURSOR gp_id IS
552 SELECT GL_INTERFACE_CONTROL_S.NEXTVAL
553 FROM sys.DUAL;
554 l_group_id number;
555 BEGIN
556 OPEN gp_id;
557 FETCH gp_id INTO l_group_id;
558 CLOSE gp_id;
559 RETURN l_group_id;
560 END Get_Group_ID;
561 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
562 --+ grant delete, update, insert, select rights on
563 --+ gl_cons_interface_groupid table to a specific user
564 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
565 PROCEDURE Grant_Rights(
566 group_id IN number,
567 db_username IN varchar2
568 )
569 IS
570 l_table_name varchar2(30);
571 v_SQL varchar2(500);
572 errbuf varchar2(500);
573 BEGIN
574 l_table_name := 'GL_CONS_INTERFACE_' || group_id;
575 v_SQL := 'grant select, update, insert, delete on ' || l_table_name
576 || ' to ' || db_username;
577 EXECUTE IMMEDIATE v_SQL;
578 exception
579 when OTHERS then
580 errbuf := SUBSTR(SQLCODE || ' ; ' || SQLERRM, 1, 255);
581 errbuf := errbuf;
582 END Grant_Rights;
583
584 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
585 --+ the group_ID will be returned
586 --+ the table with the given name and its index will be created
587 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
588 PROCEDURE Create_Interface_Table(
589 group_id IN number,
590 db_username IN varchar2
591 )
592 IS
593 l_table_name varchar2(30);
594 BEGIN
595 l_table_name := 'GL_CONS_INTERFACE_' || group_id;
596 gl_journal_import_pkg.create_table(l_table_name);
597 Grant_Rights(group_id, db_username);
598 END Create_Interface_Table;
599
600 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
601 --+this procedure can be called from a remote database. It will initialize the user id
602 --+responsibility id and application id.
603 --+load the input group id to the parallel gl_interface table and
604 --+update the gl_interface_control table with the necessary info for Journal Import.
605 --+ call this procedure to populate the gl_interface_control table
606 --+ PROCEDURE populate_interface_control(
607 --+ user_je_source_name VARCHAR2,
608 --+ group_id IN OUT NUMBER,
609 --+ ledger_id NUMBER,
610 --+ interface_run_id IN OUT NUMBER,
611 --+ table_name VARCHAR2 DEFAULT NULL,
612 --+ processed_data_action VARCHAR2 DEFAULT NULL);
613 --+ interface_run_id will be returned from this procedure
614 --+ when gl_interface_control table is populated here, the new gl_cons_interface_n
615 --+ table name is also saved in the table for Journal Import to use.
616 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
617 function Apps_Initialize (
618 user_id IN number,
619 resp_id IN number,
620 app_id IN number,
621 ledger_id IN number,
622 group_id IN number,
623 pd_name IN varchar2,
624 actual_flag IN varchar2,
625 avg_flag IN varchar2
626 ) return number
627 IS
628 l_group_id number;
629 inter_run_id number;
630 l_table_name varchar2(30);
631 v_UpdateSQL varchar2(500);
632 v_SelectSQL varchar2(1000);
633 l_reference1 varchar2(15);
634 l_user_je_source_name varchar2(25);
635 l_adb_je_source varchar2(25);
636 l_je_source varchar2(25);
637 begin
638 l_group_id := group_id;
639 --fnd_global.Apps_Initialize(user_id, resp_id, app_id);
640 l_adb_je_source := 'Average Consolidation';
641 l_je_source := 'Consolidation';
642
643 l_table_name := 'GL_CONS_INTERFACE_' || group_id;
644 IF avg_flag = 'Y' THEN
645 v_SelectSQL := 'select user_je_source_name from gl_je_sources ' ||
646 'WHERE je_source_name = :s_name';
647 EXECUTE IMMEDIATE v_SelectSQL INTO l_user_je_source_name USING l_adb_je_source;
648 gl_journal_import_pkg.populate_interface_control(
649 --+ 'Average Consolidation',
650 l_user_je_source_name,
651 l_group_id,
652 ledger_id,
653 inter_run_id,
654 l_table_name,
655 'R'); --+drop interface table
656
657 v_UpdateSQL := 'UPDATE ' || l_table_name ||
658 ' SET group_id = :group_id' ||
659 ' WHERE ledger_id = :ledger_id' ||
660 ' AND period_name = :period_name' ||
661 ' AND actual_flag = :flag' ||
662 ' AND user_je_source_name = :s_name';
663 EXECUTE IMMEDIATE v_UpdateSQL USING group_id, ledger_id, pd_name,
664 actual_flag, l_user_je_source_name;
665 ELSE
666 v_SelectSQL := 'select user_je_source_name from gl_je_sources ' ||
667 'WHERE je_source_name = :s_name';
668 EXECUTE IMMEDIATE v_SelectSQL INTO l_user_je_source_name USING l_je_source;
669 gl_journal_import_pkg.populate_interface_control(
670 --+ 'Consolidation',
671 l_user_je_source_name,
672 l_group_id,
673 ledger_id,
674 inter_run_id,
675 l_table_name,
676 'R'); --+drop interface table
677
678 v_UpdateSQL := 'UPDATE ' || l_table_name ||
679 ' SET group_id = :group_id' ||
680 ' WHERE ledger_id = :ledger_id' ||
681 ' AND period_name = :period_name' ||
682 ' AND actual_flag = :flag' ||
683 ' AND user_je_source_name = :s_name';
684 EXECUTE IMMEDIATE v_UpdateSQL USING group_id, ledger_id, pd_name,
685 actual_flag, l_user_je_source_name;
686 END IF;
687
688 return inter_run_id;
689 end Apps_Initialize;
690
691 --+set_mode() is necessary to get rid of the ORA= 2074
692 --+Error -2074: ORA-02074: cannot SET SAVEPOINT in a distributed transaction
693 --+ORA-06512: at "APPS.FND_REQUEST", line 2434
694 --+ORA-06512: at "APPS.GL_FND_REQUEST_PKG", line 54
695 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
696 --+Submit a concurrent request to do Journal Import
697 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
698 function Run_Journal_Import(
699 user_id IN number,
700 resp_id IN number,
701 app_id IN number,
702 inter_run_id IN number,
703 ledger_id IN number,
704 csj_flag IN VARCHAR2
705 ) return number
706 IS
707 reqid number;
708 value_return boolean;
709 begin
710 --fnd_global.Apps_Initialize(user_id, resp_id, app_id);
711 value_return := fnd_request.set_mode(TRUE);
712 reqid := fnd_request.submit_request(
713 'SQLGL',
714 'GLLEZL',
715 '',
716 '',
717 FALSE,
718 to_char(inter_run_id),
719 to_char(ledger_id),
720 'N', '', '',
721 csj_flag,
722 'N',
723 --+ 'NODEL',
724 --+ '',
725 chr(0),
726 '', '', '', '', '', '', '', '', '', '',
727 '', '', '', '', '', '', '', '', '', '',
728 '', '', '', '', '', '', '', '', '', '',
729 '', '', '', '', '', '', '', '', '', '',
730 '', '', '', '', '', '', '', '', '', '',
731 '', '', '', '', '', '', '', '', '', '',
732 '', '', '', '', '', '', '', '', '', '',
733 '', '', '', '', '', '', '', '', '', '',
734 '', '', '', '', '', '', '', '', '', '',
735 '');
736
737 return reqid;
738 END Run_Journal_Import;
739
740 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
741 --+Get either postable rows or posted rows, decided by the status ('U' or 'P')
742 --+each batch may have multiple headers
743 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
744 procedure Get_Postable_Rows(
745 ledger_id IN number,
746 pd_name IN varchar2,
747 batch_id IN number,
748 status IN varchar2,
749 actual_flag IN varchar2,
750 avg_flag IN varchar2,
751 postable_rows OUT NOCOPY number
752 ) IS
753 v_SelectSQL varchar2(500);
754 v_SelectSQL2 varchar2(500);
755 v_ReturnCursor t_RefCur;
756 v_Headers gl_je_headers%ROWTYPE;
757 v_header_id number;
758 v_count number;
759 v_temp number;
760 l_adb_je_source GL_JE_HEADERS.JE_SOURCE%type;
761 l_je_source GL_JE_HEADERS.JE_SOURCE%type;
762
763 BEGIN
764 v_temp := 0;
765 l_adb_je_source := 'Average Consolidation';
766 l_je_source := 'Consolidation';
767 IF avg_flag = 'Y' THEN
768 v_SelectSQL := 'select * from gl_je_headers' ||
769 ' where status = :s and je_batch_id = :b_id' ||
770 ' and ledger_id = :sid' ||
771 ' and je_source = :je' ||
772 ' and period_name = :name' ||
773 ' and actual_flag = :flag';
774 OPEN v_ReturnCursor FOR v_SelectSQL USING status, batch_id, ledger_id,
775 l_adb_je_source, pd_name, actual_flag;
776 ELSE
777 v_SelectSQL := 'select * from gl_je_headers' ||
778 ' where status = :s and je_batch_id = :b_id' ||
779 ' and ledger_id = :sid' ||
780 ' and je_source = :je' ||
781 ' and period_name = :name' ||
782 ' and actual_flag = :flag';
783 OPEN v_ReturnCursor FOR v_SelectSQL USING status, batch_id, ledger_id,
784 l_je_source, pd_name, actual_flag;
785 END IF;
786 LOOP --+for every batch in this transfer
787 FETCH v_ReturnCursor INTO v_Headers;
788 EXIT WHEN v_ReturnCursor%NOTFOUND;
789 v_header_id := v_Headers.je_header_id;
790 v_SelectSQL2 := 'select count(*) from gl_je_lines' ||
791 ' where je_header_id = :id';
792 EXECUTE IMMEDIATE v_SelectSQL2 INTO v_count USING v_header_id;
793 v_temp := v_temp + v_count;
794 END LOOP;
795 CLOSE v_ReturnCursor;
796 postable_rows := v_temp;
797 --+dbms_output.put_line('postatble rows are' || postable_rows || ' rows');
798
799 END Get_Postable_Rows;
800
801 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
802 --+Do Journal Post
803 --+need to set the status to 'S' in gl_je_batches, means selected to be posted
804 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
805 PROCEDURE Run_Journal_Post(
806 user_id IN number,
807 resp_id IN number,
808 app_id IN number,
809 ledger_id IN number,
810 pd_name IN varchar2,
811 group_id IN number,
812 import_request_id IN number,
813 batch_id IN number,
814 actual_flag IN varchar2,
815 access_set_id IN number,
816 post_run_id OUT NOCOPY number,
817 reqid OUT NOCOPY number
818
819 )
820 IS
821 CURSOR get_new_id IS
822 SELECT gl_je_posting_s.NEXTVAL
823 FROM sys.dual;
824
825 cursor ledger is
826 select chart_of_accounts_id
827 from gl_ledgers
828 where ledger_id = ledger_id;
829
830 value_return boolean;
831 l_coa_id number;
832 l_ledger_id NUMBER(15);
833 l_count number;
834 v_UpdateSQL varchar2(1000);
835 v_SelectSQL varchar2(1000);
836 v_Ledger_SQL varchar2(1000);
837 v_ALC_SQL varchar2(1000);
838 l_batch_name varchar2(100);
839 v_ReturnCursor t_RefCur;
840 v_Ledger_Cursor t_RefCur;
841 v_ALC_Cursor t_RefCur;
842 v_Batches gl_je_batches%ROWTYPE;
843
844 l_status varchar2(1);
845 l_request_id number(15);
846 dummy NUMBER(1);
847 l_ok_to_post boolean;
848 call_status BOOLEAN;
849 rphase VARCHAR2(80);
850 rstatus VARCHAR2(80);
851 dphase VARCHAR2(30);
852 dstatus VARCHAR2(30);
853 message VARCHAR2(240);
854 l_batch_status GL_JE_BATCHES.STATUS%TYPE;
855 l_budgetary_status GL_JE_BATCHES.BUDGETARY_CONTROL_STATUS%TYPE;
856
857 BEGIN
858 l_count := 1;
859 l_ok_to_post := TRUE;
860 --+dbms_output.put_line('start journal post');
861 --fnd_global.Apps_Initialize(user_id, resp_id, app_id);
862 open ledger;
863 fetch ledger into l_coa_id;
864 close ledger;
865 l_coa_id := get_coa_id(ledger_id);
866
867 value_return := fnd_request.set_mode(TRUE);
868 --+bug fix for bug#3278513, check the status of this batch
869 --+if it is 'S'= SELECTED and concurrent request is found and not COMPLETED
870 --+or it is 'I' = UNDERWAY and concurrent request is RUNNING then don't post
871 --+if the status is 'P', don't post again, just exit
872 --+ lock this row too!
873 v_SelectSQL := 'select status, request_id, budgetary_control_status ' ||
874 'from gl_je_batches ' ||
875 'WHERE je_batch_id = :b_id ' ||
876 'and default_period_name = :pd ' ||
877 -- 'and ledger_id = :ledger ' ||
878 'and actual_flag = :flag ' ||
879 'FOR UPDATE OF status, posting_run_id';
880 EXECUTE IMMEDIATE v_SelectSQL INTO l_status, l_request_id, l_budgetary_status USING batch_id, pd_name, actual_flag;
881
882 --+ if status is 'P', then can't post the batch again. get out of here.
883 --+ if the status is neither 'P' nor 'I' nor 'S', then it is ok to post
884 IF (l_status = 'P') THEN
885 l_ok_to_post := FALSE;
886 ELSIF (l_status IN ('S', 'I')) THEN
887 IF (l_budgetary_status = 'I') THEN
888 --+ added budgetary_control_status check for the bug 5003755.
889 l_ok_to_post := FALSE;
890 ELSIF (l_request_id IS NULL) THEN
891 --+ This should not happen but just in case
892 l_ok_to_post := FALSE;
893 ELSE
894 call_status :=
895 FND_CONCURRENT.GET_REQUEST_STATUS(l_request_id,
896 null,
897 null,
898 rphase,
899 rstatus,
900 dphase,
901 dstatus,
902 message);
903
904 IF (NOT call_status) THEN
905 l_ok_to_post := FALSE;
906
907 ELSIF (l_status = 'S' AND ( dphase = 'COMPLETE'
908 AND (dstatus = 'CANCELLED' OR dstatus = 'TERMINATED'))) THEN
909 l_ok_to_post := TRUE;
910
911 ELSIF (l_status = 'I' AND
912 dphase <> 'RUNNING') THEN
913 l_ok_to_post := TRUE;
914
915 ELSE
916 l_ok_to_post := FALSE;
917
918 END IF;
919 END IF; --+IF (l_request_id IS NULL) THEN
920 END IF; --+ IF (l_status = 'P') THEN
921 IF (l_ok_to_post) THEN
922 l_batch_status := 'S';
923 open get_new_id;
924 fetch get_new_id into post_run_id;
925 close get_new_id;
926 v_UpdateSQL := 'UPDATE gl_je_batches ' ||
927 'SET posting_run_id = :post_run_id, ' ||
928 'status = :bs ' ||
929 'WHERE je_batch_id = :b_id ' ||
930 'and default_period_name = :pd ' ||
931 -- 'and ledger_id = :ledger ' ||
932 'and actual_flag = :flag';
933 EXECUTE IMMEDIATE v_UpdateSQL USING post_run_id,l_batch_status,
934 batch_id, pd_name, actual_flag;
935 v_SelectSQL := 'select name from gl_je_batches ' ||
936 'WHERE je_batch_id = :b_id';
937 EXECUTE IMMEDIATE v_SelectSQL INTO l_batch_name USING batch_id;
938 --+dbms_output.put_line('get batch name');
939
940 v_Ledger_SQL := 'SELECT max(JEH.ledger_id) ' ||
941 'FROM GL_JE_HEADERS JEH ' ||
942 'WHERE JEH.je_batch_id = :je_batch_id ' ||
943 'GROUP BY JEH.je_batch_id ' ||
944 'HAVING count(distinct JEH.ledger_id) = 1';
945 OPEN v_Ledger_Cursor FOR v_Ledger_SQL USING batch_id;
946 FETCH v_Ledger_Cursor INTO l_ledger_id;
947 IF v_Ledger_Cursor%NOTFOUND THEN
948 l_ledger_id := -99;
949 ELSE
950 v_ALC_SQL := 'SELECT 1 ' ||
951 'FROM GL_JE_HEADERS JEH ' ||
952 'WHERE JEH.je_batch_id = :je_batch_id ' ||
953 'AND JEH.actual_flag != ' || '''' || 'B' || '''' ||
954 ' AND JEH.reversed_je_header_id IS NULL ' ||
955 'AND EXISTS ' ||
956 '(SELECT 1 ' ||
957 'FROM GL_LEDGER_RELATIONSHIPS LRL ' ||
958 'WHERE LRL.source_ledger_id = JEH.ledger_id ' ||
959 'AND LRL.target_ledger_category_code = ' ||
960 '''' || 'ALC' || '''' ||
961 ' AND LRL.relationship_type_code IN ( ' ||
962 '''' || 'JOURNAL' || '''' || ', ' ||
963 '''' || 'SUBLEDGER' || '''' || ') ' ||
964 'AND LRL.application_id = 101 ' ||
965 'AND LRL.relationship_enabled_flag = ' ||
966 '''' || 'Y' || '''' ||
967 ' AND JEH.je_source NOT IN ' ||
968 '(SELECT INC.je_source_name ' ||
969 'FROM GL_JE_INCLUSION_RULES INC ' ||
970 'WHERE INC.je_rule_set_id = ' ||
971 'LRL.gl_je_conversion_set_id ' ||
972 'AND INC.je_source_name = JEH.je_source ' ||
973 'AND INC.je_category_name = ' ||
974 '''' || 'Other' || '''' ||
975 ' AND INC.include_flag = ' ||
976 '''' || 'N' || '''' ||
977 ' AND INC.user_updatable_flag = ' ||
978 '''' || 'N' || '''' || '))';
979 OPEN v_ALC_Cursor FOR v_ALC_SQL USING batch_id;
980 FETCH v_ALC_Cursor INTO dummy;
981 IF v_Ledger_Cursor%FOUND THEN
982 l_ledger_id := -99;
983 END IF;
984 CLOSE v_ALC_Cursor;
985 END IF;
986 CLOSE v_Ledger_Cursor;
987
988 IF (l_ledger_id = -99) THEN
989 reqid := fnd_request.submit_request(
990 'SQLGL',
991 'GLPPOS',
992 '',
993 '',
994 FALSE,
995 to_char(l_ledger_id),
996 to_char(access_set_id),
997 to_char(l_coa_id),
998 to_char(post_run_id),
999 chr(0),'', '', '', '',
1000 '', '', '', '', '', '', '', '', '', '',
1001 '', '', '', '', '', '', '', '', '', '',
1002 '', '', '', '', '', '', '', '', '', '',
1003 '', '', '', '', '', '', '', '', '', '',
1004 '', '', '', '', '', '', '', '', '', '',
1005 '', '', '', '', '', '', '', '', '', '',
1006 '', '', '', '', '', '', '', '', '', '',
1007 '', '', '', '', '', '', '', '', '', '',
1008 '', '', '', '', '', '', '', '', '', '',
1009 '');
1010 ELSE
1011 reqid := fnd_request.submit_request(
1012 'SQLGL',
1013 'GLPPOSS',
1014 '',
1015 '',
1016 FALSE,
1017 to_char(l_ledger_id),
1018 to_char(access_set_id),
1019 to_char(l_coa_id),
1020 to_char(post_run_id),
1021 chr(0),'', '', '', '',
1022 '', '', '', '', '', '', '', '', '', '',
1023 '', '', '', '', '', '', '', '', '', '',
1024 '', '', '', '', '', '', '', '', '', '',
1025 '', '', '', '', '', '', '', '', '', '',
1026 '', '', '', '', '', '', '', '', '', '',
1027 '', '', '', '', '', '', '', '', '', '',
1028 '', '', '', '', '', '', '', '', '', '',
1029 '', '', '', '', '', '', '', '', '', '',
1030 '', '', '', '', '', '', '', '', '', '',
1031 '');
1032 END IF;
1033 --+dbms_output.put_line('after post submitted' || reqid);
1034
1035 IF (reqid <> 0) THEN
1036 v_UpdateSQL := 'UPDATE gl_je_batches ' ||
1037 'SET request_id = :request_id ' ||
1038 'WHERE je_batch_id = :b_id ' ||
1039 'and default_period_name = :pd ' ||
1040 -- 'and ledger_id = :ledger ' ||
1041 'and posting_run_id = :post_run_id ' ||
1042 'and actual_flag = :flag';
1043 EXECUTE IMMEDIATE v_UpdateSQL USING reqid, batch_id, pd_name, post_run_id, actual_flag;
1044 END IF;
1045 ELSE --+don't post, return
1046 post_run_id := 0;
1047 reqid := 0;
1048 END IF; --+IF (l_ok_to_post) THEN
1049 --+ commit; do the commit in the calling routine
1050 END Run_Journal_Post;
1051
1052 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1053 --+Verify Journal Import by the number of rows left in the
1054 --+gl_interface_control table
1055 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1056 PROCEDURE Verify_Journal_Import(
1057 p_group_id IN number,
1058 result OUT NOCOPY varchar2
1059 )
1060 IS
1061 l_count number;
1062 v_SelectSQL varchar2(300);
1063 BEGIN
1064 v_SelectSQL := 'select count(*) from gl_interface_control '||
1065 'where group_id = :group_id';
1066 EXECUTE IMMEDIATE v_SelectSQL INTO l_count USING p_group_id;
1067 if l_count = 0 then
1068 result := 'SUCCESS';
1069 else
1070 result := 'FAILURE';
1071 end if;
1072 END Verify_Journal_Import;
1073
1074 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1075 --+Verify Journal Post by comparing th epostable rows before Journal Post and
1076 --+ the posted rows after Journal Post.
1077 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1078 PROCEDURE Verify_Journal_Post(
1079 l_pd_name IN varchar2,
1080 postable_rows IN number,
1081 l_ledger_id IN number,
1082 l_batch_id IN number,
1083 actual_flag IN varchar2,
1084 avg_flag IN varchar2,
1085 result OUT NOCOPY varchar2
1086 )
1087 IS
1088 l_count number;
1089 v_SelectSQL varchar2(500);
1090 BEGIN
1091 Get_Postable_Rows(l_ledger_id, l_pd_name, l_batch_id, 'P', actual_flag, avg_flag, l_count);
1092 if l_count = postable_rows then
1093 result := 'SUCCESS';
1094 else
1095 result := 'FAILURE';
1096 end if;
1097
1098 END Verify_Journal_Post;
1099
1100 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1101 --+Wait for concurrent request to complete
1102 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1103 procedure wait_for_request(
1104 request_id IN number,
1105 result OUT NOCOPY varchar2
1106 )
1107 IS
1108 phase varchar2(80);
1109 status varchar2(80);
1110 dev_phase varchar2(30);
1111 dev_status varchar2(30);
1112 message varchar2(240);
1113 success boolean;
1114 begin
1115 if request_id <> 0 then
1116 success := fnd_concurrent.wait_for_request(request_id,
1117 30, 360000, phase, status, dev_phase, dev_status,
1118 message);
1119 end if;
1120 If dev_phase = 'COMPLETE' AND
1121 dev_status In ('NORMAL','WARNING' ) Then
1122 result := 'COMPLETE:PASS';
1123 Else
1124 result := 'COMPLETE:FAIL';
1125 End If;
1126 end wait_for_request;
1127
1128 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1129 --+Get the status of the concurrent request
1130 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1131 function get_request_status(
1132 request_id IN number,
1133 result OUT NOCOPY varchar2
1134 ) return boolean
1135 IS
1136 phase varchar2(80);
1137 status varchar2(80);
1138 dev_phase varchar2(30);
1139 dev_status varchar2(30);
1140 message varchar2(240);
1141 success boolean;
1142 reqid number;
1143 begin
1144 if request_id <> 0 then
1145 reqid := request_id;
1146 success := fnd_concurrent.get_request_status(reqid,
1147 '', '', phase, status, dev_phase, dev_status,
1148 message);
1149 end if;
1150 If dev_phase = 'COMPLETE' AND
1151 dev_status In ('NORMAL','WARNING' ) Then
1152 result := 'COMPLETE:PASS';
1153 Else
1154 result := 'COMPLETE:FAIL';
1155 End If;
1156 return success;
1157 end get_request_status;
1158
1159 --+the code below works
1160 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1161 --+To test the procedure from SQL Navigator
1162 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1163 procedure Test_run
1164 is
1165 l_inter_run_id number;
1166 l_request_id number;
1167 l_group_id number;
1168
1169 BEGIN
1170 --+ l_group_id := Create_Interface_Table;
1171 --+transfer data from gl_interface to gl_cons_interface_groupid table
1172 --+populate the gl_interface_control table to prepare for Journal Import
1173 --+ l_inter_run_id := Apps_Initialize(1238,50023,101,42, l_group_id, 'Apr-01');
1174 l_inter_run_id := Apps_Initialize(1238,50023,101,42, 3873, 'Apr-01','A','Y');
1175 --+ l_request_id := Run_Journal_Import(1238, 50023, 101, l_inter_run_id,42);
1176
1177 END Test_Run;
1178
1179 END gl_ci_remote_invoke_pkg;