[Home] [Help]
PACKAGE BODY: APPS.GL_CI_WORKFLOW_PKG
Source
1 PACKAGE BODY GL_CI_WORKFLOW_PKG AS
2 /* $Header: gluciwfb.pls 120.5 2005/05/05 01:37:19 kvora noship $ */
3 NOTIFICATION_PROCESS_STARTED Number := 0;
4 NOTFICATION_NOT_REQUIRED Number := 1;
5 FATAL_EXCEPTION Number := -1;
6 TRANSACTION_NOT_EXIST Number := -2;
7 INVALID_ACTION Number := -3;
8 CONTACT_INFO_NOT_FOUND Number := -4;
9 EMAIL_CONTACT_NOT_SET Number := -5;
10 GLOBAL_ERROR exception;
11 applSysSchema varchar2(30);
12 --+ ****************************************************************************
13 --+ Private procedure: Display diagnostic message
14 --+ ****************************************************************************
15 PROCEDURE diagn_msg( message_string IN VARCHAR2) IS
16 BEGIN
17 IF diagn_msg_flag THEN
18 null;
19 --+ dbms_output.put_line (message_string);
20 ELSE
21 null;
22 END IF;
23 EXCEPTION
24 WHEN OTHERS THEN
25 NULL;
26 END diagn_msg;
27 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
28 --+ to fix bug#2630145, do not hardcoded the apps schema name
29 --+ it could be anything at customer's site
30 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
31 PROCEDURE Get_Schema_Name (l_dblink IN varchar2)
32 is
33 dummy1 varchar2(30);
34 dummy2 varchar2(30);
35 result varchar2(150);
36 v_SQL varchar2(1000);
37 l_flag varchar2(1);
38 l_oracle_id number;
39 BEGIN
40 l_oracle_id := 900;
41 l_flag := 'U';
42 /*** select oracle_username
43 into applSysSchema
44 from fnd_oracle_userid
45 where read_only_flag = 'U'
46 and oracle_id = 900;
47 ****/
48 v_SQL := 'select oracle_username from fnd_oracle_userid@' || l_dblink ||
49 ' where read_only_flag = :flag' ||
50 ' and oracle_id = :or_id';
51 EXECUTE IMMEDIATE v_SQL INTO applSysSchema USING l_flag, l_oracle_id;
52 /**
53 IF (NOT fnd_installation.get_app_info('SQLGL',
54 dummy1,dummy2,applSysSchema)) THEN
55 RAISE GLOBAL_ERROR;
56 END IF;
57 ***/
58 IF (applSysSchema IS NULL) THEN
59 RAISE GLOBAL_ERROR;
60 END IF;
61 exception
62 when GLOBAL_ERROR then
63 rollback;
64 result := SUBSTR(SQLERRM, 1, 200);
65 FND_MESSAGE.set_name('SQLGL', 'gl_us_ci_others_fail');
66 FND_MESSAGE.set_token('RESULT', result);
67 app_exception.raise_exception;
68 -- return result;
69 END Get_Schema_Name;
70 PROCEDURE SEND_CIT_WF_NTF (
71 p_cons_request_id IN number, --+consolidation request id
72 p_Action IN VARCHAR2,
73 p_dblink IN varchar2,
74 p_batch_name IN varchar2, --+100 CHARS
75 p_source_database_name IN varchar2,
76 p_target_ledger_name IN varchar2,
77 p_interface_table_name IN varchar2,
78 p_interface_run_id IN number,
79 p_posting_run_id IN number,
80 p_request_id IN number,
81 p_group_id IN number,
82 p_send_to IN varchar2,
83 p_sender_name IN varchar2,
84 p_message_name IN varchar2,
85 p_send_from IN varchar2,
86 p_source_ledger_id IN number,
87 p_import_message_body IN varchar2,
88 p_post_request_id IN varchar2,
89 p_Return_Code OUT NOCOPY NUMBER
90 ) IS
91 l_item_key Number;
92 l_item_type VARCHAR2(10) := 'GLCITNTF';
93 l_source_database_name VARCHAR2(30);
94 l_period_name VARCHAR2(15); --++15 CHARS
95 l_interface_table_name VARCHAR2(30);
96 l_interface_run_id number;
97 l_posting_run_id number;
98 l_group_id number;
99 l_send_to VARCHAR2(30);
100 l_sender_name VARCHAR2(30);
101 l_message_name varchar2(30);
102 l_send_from VARCHAR2(30);
103 v_SQL varchar2(500);
104 v_user_name fnd_user.user_name%type;
105 l_consolidation_id number;
106 l_consolidation_set_id number;
107 l_from_period_name varchar2(15);
108 l_to_period_name varchar2(15);
109 l_application_name VARCHAR2(30);
110 l_responsibility_name VARCHAR2(100);
111 l_user_name VARCHAR2(30);
112 l_target_database_name VARCHAR2(30);
113 Cursor cons_data IS
114 Select
115 consolidation_id,
116 consolidation_set_id,
117 from_period_name,
118 to_period_name,
119 target_resp_name,
120 target_user_name,
121 target_database_name
122 From GL_CONSOLIDATION_HISTORY
123 Where REQUEST_ID = p_cons_request_Id;
124 l_source_ledger_id number;
125 l_source_ledger_name VARCHAR2(30);
126 l_mapping_rule_name VARCHAR2(33);
127 l_journal_source_name VARCHAR2(25); --+25 chars
128 user_name varchar2(100):=null;
129 user_display_name varchar2(100):=null;
130 language varchar2(100):=userenv('LANG');
131 territory varchar2(100):='America';
132 description varchar2(100):=NULL;
133 notification_preference varchar2(100):='MAILTEXT';
134 email_address varchar2(100):=NULL;
135 fax varchar2(100):=NULL;
136 status varchar2(100):='ACTIVE';
137 expiration_date varchar2(100):=NULL;
138 role_name varchar2(100):=NULL;
139 role_display_name varchar2(100):=NULL;
140 role_description varchar2(100):=NULL;
141 wf_id Number;
142 due_date date:=NULL;
143 callback varchar2(100):=NULL;
144 context varchar2(100):=NULL;
145 send_comment varchar2(100):=NULL;
146 priority number:=NULL;
147 duplicate_user_or_role exception;
148 PRAGMA EXCEPTION_INIT (duplicate_user_or_role, -20002);
149 l_domainName VARCHAR2(150);
150 dblink VARCHAR2(30);
151 l_user_je_source_name varchar2(25);
152 l_adb_je_source varchar2(25):= 'Average Consolidation';
153 l_je_source varchar2(25):= 'Consolidation';
154 Begin
155 diagn_msg('Starting SEND_CIT_WF_NTF');
156 p_return_code := NOTIFICATION_PROCESS_STARTED;
157 /*GET ALL INPUT INFORMATION FROM SOME TABLES*/
158 l_application_name := 'Oracle General Ledger';
159 Open cons_data;
160 Fetch cons_data into
161 l_consolidation_id,
162 l_consolidation_set_id,
163 l_from_period_name,
164 l_to_period_name,
165 l_responsibility_name,
166 l_user_name,
167 l_target_database_name;
168 Close cons_data;
169 l_user_name := fnd_global.USER_NAME; --bug#2543150, remove username from login
170 IF (l_consolidation_id IS NULL) AND (l_consolidation_set_id is NULL) THEN
171 p_return_code := TRANSACTION_NOT_EXIST;
172 return;
173 END IF;
174 IF l_consolidation_id IS NOT NULL THEN
175 v_SQL := 'select name from gl_consolidation' ||
176 ' where consolidation_id = :id';
177 EXECUTE IMMEDIATE v_SQL INTO l_mapping_rule_name USING l_consolidation_id;
178 END IF;
179 IF l_consolidation_set_id IS NOT NULL THEN
180 v_SQL := 'select name from gl_consolidation_sets' ||
181 ' where consolidation_set_id = :id';
182 EXECUTE IMMEDIATE v_SQL INTO l_mapping_rule_name USING l_consolidation_set_id;
183 END IF;
184 l_period_name := l_to_period_name;
185 v_SQL := 'select name from gl_ledgers' ||
186 ' where ledger_id = :id';
187 EXECUTE IMMEDIATE v_SQL INTO l_source_ledger_name USING p_source_ledger_id;
188 --+bug#2712006, cannot use hardcoded domain name
189 v_SQL := 'select domain_name ' ||
190 'from rg_database_links ' ||
191 'where name = :pd';
192 EXECUTE IMMEDIATE v_SQL INTO l_domainName USING p_dblink;
193 dblink := p_dblink || '.' || l_domainName;
194 Get_Schema_Name(dblink); --+bug#2630145, no hardcoded apps schema name
195 v_SQL := 'select user_je_source_name from gl_je_sources ' ||
196 'WHERE je_source_name = :s_name';
197 EXECUTE IMMEDIATE v_SQL INTO l_user_je_source_name USING l_je_source;
198 --+ l_journal_source_name := 'Consolidation';
199 FND_PROFILE.GET('GL_GLCCIT_EMAIL_CONTACT', v_user_name);
200 IF v_user_name IS NOT NULL THEN
201 v_SQL := 'BEGIN '||' :a := ' || applSysSchema ||'.GL_CI_REMOTE_INVOKE_PKG.Get_email_address@' || dblink ||
202 '(:user_name)' ||';'||' END;';
203 EXECUTE IMMEDIATE v_SQL USING OUT l_send_to, IN v_user_name;
204 --+bug2750898, add more user friendly error message
205 IF (l_send_to = 'GETFAILURE') THEN
206 p_return_code := EMAIL_CONTACT_NOT_SET;
207 v_user_name := NULL;
208 l_send_to := NULL;
209 -- return;
210 END IF;
211 /***
212 v_SQL := 'select email_address from fnd_user@' || p_dblink || '.world' ||
213 ' where user_name = :name';
214 EXECUTE IMMEDIATE v_SQL INTO l_send_to USING v_user_name;
215 COMMIT;
216 ***/
217 ELSE
218 --+consolidation email contact is not set at all
219 p_return_code := CONTACT_INFO_NOT_FOUND;
220 l_send_to := NULL;
221 -- return;
222 END IF;
223 --++get source database name
224 v_SQL := 'select name from v$database';
225 EXECUTE IMMEDIATE v_SQL INTO l_source_database_name;
226 --commit;
227 /*Create a role for ad hoc user if none exist*/
228 -- v_user_name := v_user_name || 'CONS';
229 role_name:= v_user_name;
230 role_display_name:=role_name || 'Dis';
231 email_address:=l_send_to;
232 begin
233 WF_Directory.CreateAdHocRole (role_name, role_display_name,
234 language, territory, role_description, notification_preference,
235 user_name, email_address, fax, status, expiration_date);
236 exception
237 when duplicate_user_or_role then
238 WF_Directory.SetAdHocRoleAttr (role_name, role_display_name,
239 notification_preference, language, territory, email_address, fax);
240 /*dbms_output.put_line ('Messagge' || role_name || ' is already in */
241 /*the database Change Attributes'); */
242 end;
243 commit;
244 /*Create a role for ad hoc user if none exist*/
245 /*bug 2543150, so that the From: field can be populated with the
246 source database name*/
247 -- v_user_name := l_source_database_name;
248 role_name:= l_source_database_name;
249 role_display_name:=role_name;
250 email_address:=l_send_to;
251 begin
252 WF_Directory.CreateAdHocRole (role_name, role_display_name,
253 language, territory, role_description, notification_preference,
254 user_name, email_address, fax, status, expiration_date);
255 exception
256 when duplicate_user_or_role then
257 WF_Directory.SetAdHocRoleAttr (role_name, role_display_name,
258 notification_preference, language, territory, email_address, fax);
259 /*dbms_output.put_line ('Messagge' || role_name || ' is already in */
260 /*the database Change Attributes'); */
261 end;
262 commit;
263 If p_action In ( 'DATA_TRANSFER_DONE') THEN
264 l_message_name := 'DATA_TRANSFER_DONE';
265 diagn_msg('Workflow notification for data transfer complete');
266 ElsIf p_action In ( 'JOURNAL_IMPORTED') THEN
267 l_message_name := 'JOURNAL_IMPORTED';
268 diagn_msg('Workflow notification for journal imported');
269 ELSIf p_action In ( 'JOURNAL_POSTED') THEN
270 l_message_name := 'JOURNAL_POSTED';
271 diagn_msg('Workflow notification for journal posted');
272 Else
273 diagn_msg('Invalid Action '||p_action);
274 p_return_code := INVALID_ACTION;
275 return;
276 End If;
277 l_item_key := get_unique_id;
278 wf_engine.CreateProcess( itemtype => l_item_type
279 ,itemkey => l_item_key
280 ,process => 'GL_CIT_PROCESS' );
281 commit;
282 diagn_msg('Created workflow item key = '||to_char(l_item_key));
283 set_wf_variables (
284 l_item_type,
285 l_item_key,
286 l_application_name,
287 l_responsibility_name,
288 l_user_name,
289 l_mapping_rule_name,
290 p_batch_name, --+100 CHARS
291 l_source_database_name,
292 l_target_database_name,
293 l_source_ledger_name,
294 p_target_ledger_name,
295 l_period_name, --+15 CHARS
296 l_user_je_source_name, --+25 chars
297 p_interface_table_name,
298 p_interface_run_id,
299 p_posting_run_id,
300 p_request_id,
301 p_group_id,
302 v_user_name,
303 --++ email_address,
304 p_sender_name,
305 l_message_name,
306 p_send_from,
307 p_import_message_body,
308 p_post_request_id);
309 commit;
310 -- p_return_code := NOTIFICATION_PROCESS_STARTED ;
311 diagn_msg('Starting CIT Workflow Process');
312 wf_engine.StartProcess( itemtype => l_item_type,
313 itemkey => l_item_key );
314 commit;
315 diagn_msg('After Starting CIT Workflow Process');
316 Return;
317 EXCEPTION
318 WHEN OTHERS THEN
319 Wf_Core.Context('GL_CI_WORKFLOW_PKG',
320 'SEND_CIT_WF_NTF', l_item_type, l_item_key);
321 p_return_code := FATAL_EXCEPTION;
322 diagn_msg('Exception in CIT workflow process');
323 Raise;
324 End SEND_CIT_WF_NTF;
325 Procedure set_wf_variables (
326 l_item_type IN VARCHAR2,
327 l_item_key IN NUMBER,
328 l_application_name IN VARCHAR2,
329 l_responsibility_name IN VARCHAR2,
330 l_user_name IN VARCHAR2,
331 l_mapping_rule_name IN VARCHAR2,
332 l_batch_name IN VARChar2, --+100 CHARS
333 l_source_database_name IN VARCHAR2,
334 l_target_database_name IN VARCHAR2,
335 l_source_ledger_name IN VARCHAR2,
336 l_target_ledger_name IN VARCHAR2,
337 l_period_name IN VARCHAR2, --+15 CHARS
338 l_journal_source_name IN VARCHAR2, --+25 chars
339 l_interface_table_name IN VARCHAR2,
340 l_interface_run_id IN number,
341 l_posting_run_id IN number,
342 l_request_id IN number,
343 l_group_id IN number,
344 l_send_to IN VARCHAR2,
345 l_sender_name IN VARCHAR2,
346 l_message_name IN varchar2,
347 l_send_from IN VARCHAR2,
348 l_import_message_body IN VARCHAR2,
349 l_post_request_id IN varchar2
350 ) IS
351 l_user_id number;
352 fnd_user_name fnd_user.user_name%type;
353 Cursor f_user_name IS
354 SELECT user_name
355 FROM fnd_user
356 WHERE user_id = l_user_id;
357 l_monitor_url VARCHAR2(500);
358 Begin
359 --+ Set the process owner
360 wf_engine.SetItemOwner( itemtype => l_item_type,
361 itemkey => l_item_key,
362 owner => l_user_name );
363 wf_engine.SetItemUserKey( itemtype => l_item_type,
364 itemkey => l_item_key,
365 userkey => to_char(l_request_id)||'-'||to_char(l_item_key) );
366 diagn_msg('Request ID = '||l_request_id);
367 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
368 itemkey => l_item_key,
369 aname => 'ITEM_KEY',
370 avalue => l_item_key );
371 wf_engine.SetItemAttrText(itemtype => l_item_type,
372 itemkey => l_item_key,
373 aname => 'APPLICATION_NAME',
374 avalue => l_application_name );
375 wf_engine.SetItemAttrText(itemtype => l_item_type,
376 itemkey => l_item_key,
377 aname => 'RESPONSIBILITY_NAME',
378 avalue => l_responsibility_name );
379 wf_engine.SetItemAttrText(itemtype => l_item_type,
380 itemkey => l_item_key,
381 aname => 'USER_NAME',
382 avalue => l_user_name );
383 wf_engine.SetItemAttrText(itemtype => l_item_type,
384 itemkey => l_item_key,
385 aname => 'SOURCE_DATABASE_NAME',
386 avalue => l_source_database_name );
387 wf_engine.SetItemAttrText(itemtype => l_item_type,
388 itemkey => l_item_key,
389 aname => 'TARGET_DATABASE_NAME',
390 avalue => l_target_database_name );
391 wf_engine.SetItemAttrText(itemtype => l_item_type,
392 itemkey => l_item_key,
393 aname => 'SOURCE_LEDGER_NAME',
394 avalue => l_source_ledger_name );
395 diagn_msg('Source ledger Name = '||l_source_ledger_name);
396 wf_engine.SetItemAttrText(itemtype => l_item_type,
397 itemkey => l_item_key,
398 aname => 'TARGET_LEDGER_NAME',
399 avalue => l_target_ledger_name );
400 diagn_msg('Target ledger Name = '||l_target_ledger_name);
401 wf_engine.SetItemAttrText(itemtype => l_item_type,
402 itemkey => l_item_key,
403 aname => 'JOURNAL_SOURCE_NAME',
404 avalue => l_journal_source_name );
405 wf_engine.SetItemAttrText(itemtype => l_item_type,
406 itemkey => l_item_key,
407 aname => 'PERIOD_NAME',
408 avalue => l_period_name );
409 wf_engine.SetItemAttrText(itemtype => l_item_type,
410 itemkey => l_item_key,
411 aname => 'MAPPING_RULE_NAME',
412 avalue => l_mapping_rule_name );
413 wf_engine.SetItemAttrText(itemtype => l_item_type,
414 itemkey => l_item_key,
415 aname => 'BATCH_NAME',
416 avalue => l_batch_name );
417 wf_engine.SetItemAttrText(itemtype => l_item_type,
418 itemkey => l_item_key,
419 aname => 'INTERFACE_TABLE_NAME',
420 avalue => l_interface_table_name );
421 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
422 itemkey => l_item_key,
423 aname => 'REQUEST_ID',
424 avalue => l_request_id );
425 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
426 itemkey => l_item_key,
427 aname => 'INTERFACE_RUN_ID',
428 avalue => l_interface_run_id );
429 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
430 itemkey => l_item_key,
431 aname => 'POSTING_RUN_ID',
432 avalue => l_posting_run_id );
433 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
434 itemkey => l_item_key,
435 aname => 'GROUP_ID',
436 avalue => l_group_id );
437 wf_engine.SetItemAttrText(itemtype => l_item_type,
438 itemkey => l_item_key,
439 aname => 'SEND_TO',
440 avalue => l_send_to );
441 diagn_msg('Send To = '||l_send_to);
442 wf_engine.SetItemAttrText(itemtype => l_item_type,
443 itemkey => l_item_key,
444 aname => 'SENDER_NAME',
445 avalue => l_sender_name );
446 diagn_msg('Sender Name = '||l_sender_name);
447 wf_engine.SetItemAttrText(itemtype => l_item_type,
448 itemkey => l_item_key,
449 aname => 'MESSAGE_NAME',
450 avalue => l_message_name );
451 diagn_msg('Message Name = '||l_message_name);
452 wf_engine.SetItemAttrText(itemtype => l_item_type,
453 itemkey => l_item_key,
454 aname => 'SEND_FROM',
455 avalue => l_send_From );
456 diagn_msg('Send From = '||l_send_From);
457 wf_engine.SetItemAttrText(itemtype => l_item_type,
458 itemkey => l_item_key,
459 aname => 'IMPORT_MESSAGE_BODY',
460 avalue => l_import_message_body );
461 wf_engine.SetItemAttrText(itemtype => l_item_type,
462 itemkey => l_item_key,
463 aname => 'POST_REQUEST_ID',
464 avalue => l_post_request_id );
465 --+ Get the monitor URL
466 begin
467 l_monitor_url :=
468 wf_monitor.GetUrl(wf_core.translate('WF_WEB_AGENT'),
469 l_item_type, l_item_key,'YES');
470 Exception
471 When others then
472 l_monitor_url := 'Invalid URL';
473 end;
474 wf_engine.SetItemAttrText( itemtype => l_item_type,
475 itemkey => l_item_key,
476 aname => 'MONITOR_URL',
477 avalue => l_monitor_url);
478 End set_wf_variables;
479 FUNCTION get_unique_id RETURN NUMBER IS
480 CURSOR get_new_id IS
481 SELECT gl_cit_notify_s.NEXTVAL
482 FROM dual;
483 new_id number;
484 BEGIN
485 OPEN get_new_id;
486 FETCH get_new_id INTO new_id;
487 IF get_new_id%FOUND THEN
488 CLOSE get_new_id;
489 return(new_id);
490 ELSE
491 CLOSE get_new_id;
492 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
493 fnd_message.set_token('SEQUENCE', 'GL_CIT_NOTIFY_S');
494 app_exception.raise_exception;
495 END IF;
496 EXCEPTION
497 WHEN app_exceptions.application_exception THEN
498 RAISE;
499 WHEN OTHERS THEN
500 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
501 fnd_message.set_token('PROCEDURE',
502 'GL_CI_WORKFLOW_PKG.GET_UNIQUE_ID');
503 RAISE;
504 END get_unique_id;
505 Procedure Get_Action_Type (
506 p_item_type IN VARCHAR2,
507 p_item_key IN VARCHAR2,
508 p_actid IN NUMBER,
509 p_funcmode IN VARCHAR2,
510 p_result OUT NOCOPY VARCHAR2
511 ) IS
512 l_message_name VARCHAR2(100);
513 Begin
514 If p_funcmode = 'RUN' THEN
515 diagn_msg('Starting Get_Action_Type');
516 l_message_name := WF_ENGINE.GetItemAttrText
517 (p_item_type,
518 p_item_key,
519 'MESSAGE_NAME');
520 diagn_msg('Message_Name = '||l_message_name);
521 If l_message_name = 'DATA_TRANSFER_DONE' Then
522 p_result := 'COMPLETE:DATA_TRANSFER_DONE';
523 ElsIf l_message_name = 'JOURNAL_IMPORTED' Then
524 p_result := 'COMPLETE:JOURNAL_IMPORTED';
525 ElsIf l_message_name = 'JOURNAL_POSTED' Then
526 p_result := 'COMPLETE:JOURNAL_POSTED';
527 End If;
528 ElsIf ( p_funcmode = 'CANCEL' ) THEN
529 NULL;
530 END IF;
531 EXCEPTION
532 WHEN OTHERS THEN
533 Wf_Core.Context('GL_CI_WORKFLOW_PKG', 'Get_Action_Type', p_item_type, p_item_key);
534 Raise;
535 End Get_Action_Type;
536 End gl_ci_workflow_pkg;