[Home] [Help]
PACKAGE BODY: APPS.GCS_WF_NTF_PKG
Source
1 PACKAGE BODY GCS_WF_NTF_PKG AS
2 /* $Header: gcswfntfb.pls 120.8 2006/09/12 05:19:04 hakumar noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7
8 -- The API name
9 g_pkg_name CONSTANT VARCHAR2 (30) := 'gcs.plsql.GCS_WF_NTF_PKG';
10 -- A newline character. Included for convenience when writing long strings.
11 g_nl VARCHAR2 (1) := '
12 ';
13
14 --
15 -- PUBLIC PROCEDURES
16 --
17
18 PROCEDURE raise_status_notification ( p_cons_detail_id IN NUMBER)
19 IS
20 l_api_name VARCHAR2(80) := 'raise_status_notification';
21 l_event_key VARCHAR2(200);
22 l_entity_name VARCHAR2(200);
23 l_counter_entity_name VARCHAR2(200);
24 l_counter_entity_id NUMBER;
25 l_cons_entity_name VARCHAR2(200);
26 l_cons_entity_id NUMBER;
27 l_hierarchy_name VARCHAR2(200);
28 l_run_name VARCHAR2(200);
29 l_entry_id NUMBER(15);
30 l_balance_type VARCHAR2(200);
31 l_date VARCHAR2(200);
32 l_wf_region VARCHAR2(200);
33 l_attachment VARCHAR2(200);
34 l_subject VARCHAR2(200);
35 l_impacted_flag VARCHAR2(1);
36 l_category_code VARCHAR2(30);
37 l_status VARCHAR2(200);
38 l_recipient VARCHAR2(1000);
39 l_counter_recipient VARCHAR2(1000);
40 l_adhoc_role VARCHAR2(40);
41 l_adhoc_role_disp VARCHAR2(40);
42 l_adhoc_users VARCHAR2(2001);
43 l_entity_contact_attr NUMBER(15);
44 l_entity_contact_ver NUMBER;
45 l_oper_entity_attr NUMBER(15);
46 l_oper_entity_ver NUMBER;
47 -- Bug fix 5245250
48 l_seq NUMBER;
49 BEGIN
50
51 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' ENTER :'
52 || ' p_cons_detail_id = ' || p_cons_detail_id);
53 FND_FILE.NEW_LINE(FND_FILE.LOG);
54
55 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
56 fnd_log.STRING (fnd_log.level_procedure,
57 g_pkg_name || '.' || l_api_name,
58 gcs_utility_pkg.g_module_enter
59 || ' p_cons_detail_id = '
60 || p_cons_detail_id
61 || ' '
62 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
63 );
64 END IF;
65
66 l_entity_contact_attr := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_CONTACT').attribute_id;
67 l_entity_contact_ver := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_CONTACT').version_id;
68
69 -- bug fix 5109610: change joins of fnd_lookup_values to gcs_data_types_b/tl for balance_type_code
70 SELECT fet_cons.entity_name,
71 fet_cons.entity_id,
72 fet_child.entity_name,
73 fet_counter.entity_name,
74 fet_counter.entity_id,
75 gcerd.run_name,
76 gcerd.entry_id,
77 gdtct.data_type_name,
78 fcpt.cal_period_name,
79 ght.hierarchy_name,
80 gcerd.request_error_code,
81 gcerd.category_code,
82 --nvl(fea_cons.varchar_assign_value, fea_child.varchar_assign_value) contact
83 fea_child.varchar_assign_value,
84 -- Bug fix 5245250
85 fnd_flex_values_s.NEXTVAL
86 INTO l_cons_entity_name,
87 l_cons_entity_id,
88 l_entity_name,
89 l_counter_entity_name,
90 l_counter_entity_id,
91 l_run_name,
92 l_entry_id,
93 l_balance_type,
94 l_date,
95 l_hierarchy_name,
96 l_status,
97 l_category_code,
98 l_recipient,
99 -- Bug fix 5245250
100 l_seq
101 FROM gcs_cons_eng_run_dtls gcerd,
102 fem_entities_tl fet_cons,
103 fem_entities_tl fet_child,
104 fem_entities_tl fet_counter,
105 gcs_hierarchies_tl ght,
106 gcs_cons_eng_runs gcer,
107 gcs_data_type_codes_b gdtcb,
108 gcs_data_type_codes_tl gdtct,
109 fem_cal_periods_tl fcpt,
110 --fem_entities_attr fea_cons,
111 fem_entities_attr fea_child
112 WHERE gcerd.run_detail_id = p_cons_detail_id
113 AND gcerd.consolidation_entity_id = fet_cons.entity_id
114 AND gcerd.child_entity_id = fet_child.entity_id (+)
115 AND gcerd.contra_child_entity_id = fet_counter.entity_id (+)
116 AND gcerd.run_name = gcer.run_name
117 AND gcerd.consolidation_entity_id = gcer.run_entity_id
118 AND gcer.hierarchy_id = ght.hierarchy_id
119 AND ght.language = userenv('LANG')
120 AND fet_cons.language = userenv('LANG')
121 AND fet_child.language (+)= userenv('LANG')
122 AND fet_counter.language (+) = userenv('LANG')
123 AND gcer.balance_type_code = gdtcb.data_type_code
124 AND gdtcb.data_type_id = gdtct.data_type_id
125 AND gdtct.language = userenv('LANG')
126 AND gcer.cal_period_id = fcpt.cal_period_id
127 AND fcpt.language = userenv('LANG')
128 --AND fea_cons.entity_id = fet_cons.entity_id
129 AND fea_child.entity_id (+)= fet_child.entity_id
130 --AND fea_cons.attribute_id = l_entity_contact_attr
131 --AND fea_cons.version_id = l_entity_contact_ver
132 AND fea_child.attribute_id (+)= l_entity_contact_attr
133 AND fea_child.version_id (+)= l_entity_contact_ver;
134 -- end of bug fix 5109610
135
136 -- Bug fix 5245250
137 --l_event_key := substr(nvl(l_entity_name, l_cons_entity_name), 1, 150) || ' ' || p_cons_detail_id;
138 l_event_key := 'Consolidation Status Notification: ' || l_seq;
139
140 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' status is ' || l_status
141 || ' category is ' || l_category_code);
142 FND_FILE.NEW_LINE(FND_FILE.LOG);
143
144 -- consolidation successfully completed
145 IF ((l_status = 'COMPLETED' OR l_status = 'WARNING') AND l_category_code = 'AGGREGATION') THEN
146 FND_MESSAGE.SET_NAME ('GCS', 'GCS_WF_COMPLETE_TITLE');
147 FND_MESSAGE.set_token('ENTITY_NAME', l_cons_entity_name);
148 FND_MESSAGE.set_token('DATE_TOKEN', l_date);
149 l_subject := FND_MESSAGE.GET;
150 -- suspense violation
151 ELSIF (l_status = 'WARNING') THEN
152 IF (l_category_code = 'DATAPREPARATION') THEN
153 FND_MESSAGE.SET_NAME ('GCS', 'GCS_WF_DP_TITLE');
154 FND_MESSAGE.set_token('ENTITY_1', l_entity_name);
155 ELSIF (l_category_code = 'INTERCOMPANY' or l_category_code = 'INTRACOMPANY') THEN
156 FND_MESSAGE.SET_NAME ('GCS', 'GCS_WF_INTER_TITLE');
157 FND_MESSAGE.set_token('ENTITY_1', l_entity_name);
158 FND_MESSAGE.set_token('ENTITY_2', l_counter_entity_name);
159 IF ((l_category_code = 'INTERCOMPANY' OR l_category_code = 'INTRACOMPANY') AND l_counter_entity_id IS NOT NULL) THEN
160 BEGIN
161 -- Get contact for counter entity
162 SELECT fea_counter.varchar_assign_value
163 INTO l_counter_recipient
164 FROM fem_entities_attr fea_counter
165 WHERE fea_counter.entity_id = l_counter_entity_id
166 AND fea_counter.attribute_id = l_entity_contact_attr
167 AND fea_counter.version_id = l_entity_contact_ver;
168 -- Create ad-hoc role
169 IF (l_counter_recipient IS NOT NULL and l_recipient <> l_counter_recipient) THEN
170 l_adhoc_role := l_category_code||'_SUSPENSE_'||p_cons_detail_id;
171 l_adhoc_role_disp := l_category_code||'_SUSPENSE_'||p_cons_detail_id;
172 l_adhoc_users := l_recipient||','||l_counter_recipient;
173 WF_DIRECTORY.CreateAdHocRole( role_name => l_adhoc_role,
174 role_display_name => l_adhoc_role_disp,
175 role_users => l_adhoc_users);
176 -- Make this ad-hoc role to be recipient
177 l_recipient := l_adhoc_role;
178 END IF;
179 EXCEPTION WHEN OTHERS THEN NULL;
180 END;
181 END IF;
182 ELSE
183 FND_MESSAGE.SET_NAME ('GCS', 'GCS_WF_ELIM_TITLE');
184 FND_MESSAGE.set_token('ENTITY_1', l_entity_name);
185 END IF;
186 FND_MESSAGE.set_token('DATE_TOKEN', l_date);
187 l_subject := FND_MESSAGE.GET;
188
189 l_wf_region := 'JSP:/OA_HTML/OA.jsp?OAFunc=FCH_WF_EMBEDDED_RG'||'&'||'RUNNAME='||l_run_name||'&'||'CONS_ENTITY_ID='||l_cons_entity_id||'&'||'CATEGORY='||l_category_code||'&'||'RUN_DETAIL_ID='||p_cons_detail_id;
190 l_attachment := 'FND:entity=GCS_ENTRY_HEADERS'||'&'||'pk1name=ENTRY_ID'||'&'||'pk1value='||l_entry_id;
191 -- error
192 ELSE
193 FND_MESSAGE.SET_NAME ('GCS', 'GCS_WF_ERROR_TITLE');
194 FND_MESSAGE.set_token('CATEGORY', l_category_code);
195 FND_MESSAGE.set_token('ENTITY_NAME', l_cons_entity_name);
196 FND_MESSAGE.set_token('DATE_TOKEN', l_date);
197 l_subject := FND_MESSAGE.GET;
198
199 END IF;
200
201 WF_ENGINE.CreateProcess('FCHNTFWF', l_event_key, 'GCS NOTIFICATION PROCESS', l_event_key, null);
202 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'HIERARCHY', l_hierarchy_name);
203 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTITY_1', l_entity_name);
204 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTITY_2', l_counter_entity_name);
205 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'SENDER', fnd_global.user_name);
206 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'RECIPIENT', l_recipient);
207 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTITY', l_cons_entity_name);
208 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'RUNNAME', l_run_name);
209 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'WFREGION', l_wf_region);
210 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'BALTYPE', l_balance_type);
211 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'DATE', l_date);
212 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTRYID', l_entry_id);
213 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, '#ATTACHMENTS', l_attachment);
214 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'IMPACTED FLAG', 'N');
215 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'SUBJECT', l_subject);
216 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'MESSAGE CONTENT', l_subject);
217 WF_ENGINE.StartProcess('FCHNTFWF', l_event_key);
218
219 COMMIT;
220
221 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' EXIT ');
222 FND_FILE.NEW_LINE(FND_FILE.LOG);
223
224 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
225 fnd_log.STRING (fnd_log.level_procedure,
226 g_pkg_name || '.' || l_api_name,
227 gcs_utility_pkg.g_module_success
228 || ' '
229 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
230 );
231 END IF;
232
233 END raise_status_notification;
234
235 PROCEDURE raise_impact_notification ( p_run_name IN VARCHAR2,
236 p_cons_entity_id IN NUMBER,
237 p_entry_id IN NUMBER DEFAULT 0,
238 p_load_id IN NUMBER DEFAULT 0 )
239 IS
240 l_api_name VARCHAR2(80) := 'raise_impact_notification';
241 l_event_key VARCHAR2(200);
242 l_cons_entity_name VARCHAR2(200);
243 l_hierarchy_name VARCHAR2(200);
244 l_balance_type VARCHAR2(200);
245 l_date VARCHAR2(200);
246 l_wf_region VARCHAR2(200);
247 l_attachment VARCHAR2(200);
248 l_subject VARCHAR2(200);
249 l_recipient VARCHAR2(100);
250 l_entity_contact_attr NUMBER(15);
251 l_entity_contact_ver NUMBER;
252 -- Bug fix 5245250
253 l_seq NUMBER;
254 BEGIN
255
256 fnd_file.put_line(fnd_file.log, 'Within raise impact notification');
257
258 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
259 fnd_log.STRING (fnd_log.level_procedure,
260 g_pkg_name || '.' || l_api_name,
261 gcs_utility_pkg.g_module_enter
262 || ' p_run_name = '
263 || p_run_name
264 || ', p_cons_entity_id = '
265 || p_cons_entity_id
266 || ', p_entry_id = '
267 || p_entry_id
268 || ', p_load_id = '
269 || p_load_id
270 || ' '
271 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
272 );
273 END IF;
274
275 l_entity_contact_attr := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_CONTACT').attribute_id;
276 l_entity_contact_ver := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_CONTACT').version_id;
277 -- bug fix 5109610: change joins of fnd_lookup_values to gcs_data_types_b/tl for balance_type_code
278 SELECT fet_cons.entity_name,
279 gdtct.data_type_name,
280 fcpt.cal_period_name,
281 ght.hierarchy_name,
282 fea_cons.varchar_assign_value,
283 -- Bug fix 5245250
284 fnd_flex_values_s.NEXTVAL
285 INTO l_cons_entity_name,
286 l_balance_type,
287 l_date,
288 l_hierarchy_name,
289 l_recipient,
290 -- Bug fix 5245250
291 l_seq
292 FROM fem_entities_tl fet_cons,
293 gcs_hierarchies_tl ght,
294 gcs_cons_eng_runs gcer,
295 gcs_data_type_codes_b gdtcb,
296 gcs_data_type_codes_tl gdtct,
297 fem_entities_attr fea_cons,
298 fem_cal_periods_tl fcpt
299 WHERE p_run_name = gcer.run_name
300 AND gcer.run_entity_id = p_cons_entity_id
301 AND gcer.run_entity_id = fet_cons.entity_id
302 AND gcer.hierarchy_id = ght.hierarchy_id
303 AND ght.language = userenv('LANG')
304 AND fet_cons.language = userenv('LANG')
305 AND gcer.balance_type_code = gdtcb.data_type_code
306 AND gdtcb.data_type_id = gdtct.data_type_id
307 AND gdtct.language = userenv('LANG')
308 AND fea_cons.entity_id = gcer.run_entity_id
309 AND fea_cons.attribute_id = l_entity_contact_attr
310 AND fea_cons.version_id = l_entity_contact_ver
311 AND gcer.cal_period_id = fcpt.cal_period_id
312 AND fcpt.language = userenv('LANG');
313 -- end of bug fix 5109610
314
315 -- Bug fix 5245250
316 --l_event_key := substr(l_cons_entity_name, 1, 150) || ' impacted on ' || to_char(sysdate, 'DD-MON-RR HH24:MI:SS');
317 l_event_key := 'Consolidation Impact Notification: ' || l_seq;
318
319 FND_MESSAGE.SET_NAME ('GCS', 'GCS_WF_IMPACTED_TITLE');
320 FND_MESSAGE.set_token('ENTITY_NAME', l_cons_entity_name);
321 FND_MESSAGE.set_token('DATE_TOKEN', l_date);
322 l_subject := FND_MESSAGE.GET;
323
324 l_wf_region := 'JSP:/OA_HTML/OA.jsp?OAFunc=FCH_WF_EMBEDDED_RG'||'&'||'RUNNAME='||
325 p_run_name||'&'||'CONS_ENTITY_ID='||p_cons_entity_id||'&'||'CATEGORY=IMPACTED';
326
327 IF (p_entry_id <> 0) THEN
328 l_attachment := 'FND:
329 entity=GCS_ENTRY_HEADERS'||'&'||'pk1name=ENTRY_ID'||'&'||'pk1value='||p_entry_id;
330 --Commented for bug fix5521345
331 /* ELSIF (p_load_id <> 0) THEN
332 l_attachment := 'FND:entity=GCS_DATA_SUB_DTLS'||'&'||'pk1name=LOAD_ID'||'&'||'pk1value='||p_load_id;
333 */
334 END IF;
335
336 WF_ENGINE.CreateProcess('FCHNTFWF', l_event_key, 'GCS NOTIFICATION PROCESS', l_event_key, null);
337 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'HIERARCHY', l_hierarchy_name);
338 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'SENDER', fnd_global.user_name);
339 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'RECIPIENT', l_recipient);
340 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTITY', l_cons_entity_name);
341 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'RUNNAME', p_run_name);
342 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'WFREGION', l_wf_region);
343 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'BALTYPE', l_balance_type);
344 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'DATE', l_date);
345 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTRYID', p_entry_id);
346 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, '#ATTACHMENTS', l_attachment);
347 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'IMPACTED FLAG', 'Y');
348 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'SUBJECT', l_subject);
349 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'MESSAGE CONTENT', l_subject);
350 WF_ENGINE.StartProcess('FCHNTFWF', l_event_key);
351
352 COMMIT;
353
354 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
355 fnd_log.STRING (fnd_log.level_procedure,
356 g_pkg_name || '.' || l_api_name,
357 gcs_utility_pkg.g_module_success
358 || ' '
359 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
360 );
361 END IF;
362
363 END raise_impact_notification;
364
365 PROCEDURE raise_lock_notification ( p_run_name IN VARCHAR2,
366 p_cons_entity_id IN NUMBER)
367 IS
368 l_api_name VARCHAR2(80) := 'raise_lock_notification';
369 l_event_key VARCHAR2(200);
370 l_cons_entity_name VARCHAR2(200);
371 l_hierarchy_name VARCHAR2(200);
372 l_balance_type VARCHAR2(200);
373 l_date VARCHAR2(200);
374 l_subject VARCHAR2(200);
375 l_recipient VARCHAR2(100);
376 l_entity_contact_attr NUMBER(15);
377 l_entity_contact_ver NUMBER;
378 -- Bug fix 5245250
379 l_seq NUMBER;
380 BEGIN
381
382 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
383 fnd_log.STRING (fnd_log.level_procedure,
384 g_pkg_name || '.' || l_api_name,
385 gcs_utility_pkg.g_module_enter
386 || ' p_run_name = '
387 || p_run_name
388 || ', p_cons_entity_id = '
389 || p_cons_entity_id
390 || ' '
391 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
392 );
393 END IF;
394
395 l_entity_contact_attr := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_CONTACT').attribute_id;
396 l_entity_contact_ver := gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ENTITY_CONTACT').version_id;
397 -- bug fix 5109610: change joins of fnd_lookup_values to gcs_data_types_b/tl for balance_type_code
398 SELECT fet_cons.entity_name,
399 gdtct.data_type_name,
400 fcpt.cal_period_name,
401 ght.hierarchy_name,
402 fea_cons.varchar_assign_value,
403 -- Bug fix 5245250
404 fnd_flex_values_s.NEXTVAL
405 INTO l_cons_entity_name,
406 l_balance_type,
407 l_date,
408 l_hierarchy_name,
409 l_recipient,
410 -- Bug fix 5245250
411 l_seq
412 FROM fem_entities_tl fet_cons,
413 gcs_hierarchies_tl ght,
414 gcs_cons_eng_runs gcer,
415 gcs_data_type_codes_b gdtcb,
416 gcs_data_type_codes_tl gdtct,
417 fem_cal_periods_tl fcpt,
418 fem_entities_attr fea_cons
419 WHERE p_run_name = gcer.run_name
420 AND gcer.run_entity_id = p_cons_entity_id
421 AND gcer.run_entity_id = fet_cons.entity_id
422 AND gcer.hierarchy_id = ght.hierarchy_id
423 AND ght.language = userenv('LANG')
424 AND fet_cons.language = userenv('LANG')
425 AND gcer.balance_type_code = gdtcb.data_type_code
426 AND gdtcb.data_type_id = gdtct.data_type_id
427 AND gdtct.language = userenv('LANG')
428 AND fea_cons.entity_id = gcer.run_entity_id
429 AND fea_cons.attribute_id = l_entity_contact_attr
430 AND fea_cons.version_id = l_entity_contact_ver
431 AND gcer.cal_period_id = fcpt.cal_period_id
432 AND fcpt.language = userenv('LANG');
433 -- end of bug fix 5109610
434
435 -- Bug fix 5245250
436 --l_event_key := substr(l_cons_entity_name, 1, 150) || ' locked on ' || to_char(sysdate, 'DD-MON-RR HH24:MI:SS');
437 l_event_key := 'Consolidation Lock Notification: ' || l_seq;
438
439 FND_MESSAGE.SET_NAME ('GCS', 'GCS_WF_LOCKED_TITLE');
440 FND_MESSAGE.set_token('ENTITY_NAME', l_cons_entity_name);
441 FND_MESSAGE.set_token('DATE_TOKEN', l_date);
442 l_subject := FND_MESSAGE.GET;
443
444 WF_ENGINE.CreateProcess('FCHNTFWF', l_event_key, 'GCS NOTIFICATION PROCESS', l_event_key, null);
445 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTITY_1', null);
446 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTITY_2', null);
447 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'SENDER', fnd_global.user_name);
448 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'RECIPIENT', l_recipient);
449 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'HIERARCHY', l_hierarchy_name);
450 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTITY', l_cons_entity_name);
451 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'RUNNAME', p_run_name);
452 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'WFREGION', NULL);
453 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'BALTYPE', l_balance_type);
454 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'DATE', l_date);
455 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'ENTRYID', NULL);
456 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, '#ATTACHMENTS', NULL);
457 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'SUBJECT', l_subject);
458 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'MESSAGE CONTENT', l_subject);
459 WF_ENGINE.SetItemAttrText('FCHNTFWF', l_event_key, 'IMPACTED FLAG', 'N');
460 WF_ENGINE.StartProcess('FCHNTFWF', l_event_key);
461
462 COMMIT;
463
464 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
465 fnd_log.STRING (fnd_log.level_procedure,
466 g_pkg_name || '.' || l_api_name,
467 gcs_utility_pkg.g_module_success
468 || ' '
469 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
470 );
471 END IF;
472
473 END raise_lock_notification;
474
475 PROCEDURE check_attachment_required( p_itemtype IN VARCHAR2,
476 p_itemkey IN VARCHAR2,
477 p_actid IN NUMBER,
478 p_funcmode IN VARCHAR2,
479 p_result IN OUT NOCOPY VARCHAR2)
480 IS
481
482 l_attachment VARCHAR2(200);
483 l_api_name VARCHAR2(80) := 'check_attachment_required';
484
485 BEGIN
486
487 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
488 fnd_log.STRING (fnd_log.level_procedure,
489 g_pkg_name || '.' || l_api_name,
490 gcs_utility_pkg.g_module_enter
491 || ' '
492 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
493 );
494 END IF;
495
496 l_attachment := WF_ENGINE.GetItemAttrText(p_itemtype, p_itemkey, '#ATTACHMENTS', FALSE);
497
498 IF (l_attachment IS NULL) THEN
499 p_result := 'COMPLETE:F';
500 ELSE
501 p_result := 'COMPLETE:T';
502 END IF;
503
504 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
505 fnd_log.STRING (fnd_log.level_procedure,
506 g_pkg_name || '.' || l_api_name,
507 gcs_utility_pkg.g_module_success
508 || ' '
509 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
510 );
511 END IF;
512
513 END check_attachment_required;
514
515 PROCEDURE check_impacted( p_itemtype IN VARCHAR2,
516 p_itemkey IN VARCHAR2,
517 p_actid IN NUMBER,
518 p_funcmode IN VARCHAR2,
519 p_result IN OUT NOCOPY VARCHAR2)
520 IS
521
522 l_impacted_flag VARCHAR2(200);
523 l_api_name VARCHAR2(80) := 'check_impacted';
524
525 BEGIN
526
527 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
528 fnd_log.STRING (fnd_log.level_procedure,
529 g_pkg_name || '.' || l_api_name,
530 gcs_utility_pkg.g_module_enter
531 || ' '
532 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
533 );
534 END IF;
535
536 l_impacted_flag := WF_ENGINE.GetItemAttrText(p_itemtype, p_itemkey, 'IMPACTED FLAG', FALSE);
537
538 IF (l_impacted_flag = 'Y') THEN
539 p_result := 'COMPLETE:T';
540 ELSE
541 p_result := 'COMPLETE:F';
542 END IF;
543
544 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
545 fnd_log.STRING (fnd_log.level_procedure,
546 g_pkg_name || '.' || l_api_name,
547 gcs_utility_pkg.g_module_success
548 || ' '
549 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
550 );
551 END IF;
552
553 END check_impacted;
554
555 PROCEDURE update_consolidation( p_itemtype IN VARCHAR2,
556 p_itemkey IN VARCHAR2,
557 p_actid IN NUMBER,
558 p_funcmode IN VARCHAR2,
559 p_result IN OUT NOCOPY VARCHAR2)
560 IS
561 l_run_dtl_id NUMBER(15);
562 l_api_name VARCHAR2(80) := 'update_consolidation';
563 l_ntf_id NUMBER;
564 /*
565 CURSOR c_ntf_id ( p_item_type IN VARCHAR2,
566 p_item_key IN VARCHAR2 ) IS
567 SELECT wn.notification_id nid
568 FROM wf_notifications wn,
569 wf_item_activity_statuses wias
570 WHERE wn.group_id = wias.notification_id
571 AND wias.item_type = p_item_type
572 AND wias.item_key = p_item_key;
573 */
574 BEGIN
575
576 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
577 fnd_log.STRING (fnd_log.level_procedure,
578 g_pkg_name || '.' || l_api_name,
579 gcs_utility_pkg.g_module_enter
580 || ' '
581 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
582 );
583 END IF;
584
585 l_run_dtl_id := WF_ENGINE.GetItemAttrText(p_itemtype, p_itemkey, 'RUN DETAIL ID', FALSE);
586 /*
587 IF (l_run_dtl_id IS NOT NULL) THEN
588 OPEN c_ntf_id(p_itemtype, p_itemkey);
589 FETCH c_ntf_id INTO l_ntf_id;
590 CLOSE c_ntf_id;
591
592 UPDATE gcs_cons_eng_run_dtls
593 SET notification_id = l_ntf_id
594 WHERE run_detail_id = l_run_dtl_id;
595
596 COMMIT;
597
598 END IF;
599 */
600 p_result := 'COMPLETE:T';
601
602 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
603 fnd_log.STRING (fnd_log.level_procedure,
604 g_pkg_name || '.' || l_api_name,
605 gcs_utility_pkg.g_module_success
606 || ' '
607 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
608 );
609 END IF;
610
611 END update_consolidation;
612
613 END GCS_WF_NTF_PKG;