1 PACKAGE BODY oe_notify_user_wf as
2 /* $Header: OEXWNTFB.pls 120.2.12020000.2 2012/10/05 10:23:31 vmachett ship $ */
3
4 FUNCTION SetUserPreference(
5 p_user_id NUMBER,
6 p_value VARCHAR2
7 )
8 RETURN VARCHAR2
9 IS
10 l_result Boolean;
11 l_return_result VARCHAR2(1);
12 BEGIN
13 l_result := FND_PROFILE.save(
14 X_NAME => 'ONT_SEND_STATUS_UPDATE_NOTIFICATION' ,
15 X_VALUE => p_value ,
16 X_LEVEL_NAME => 'USER' ,
17 X_LEVEL_VALUE => To_Char(p_user_id),
18 X_LEVEL_VALUE_APP_ID => NULL,
19 X_LEVEL_VALUE2 => NULL );
20 IF l_result THEN
21 l_return_result := 'S';
22 ELSE
23 l_return_result := 'E';
24 END IF;
25
26 RETURN l_return_result;
27
28 END;
29
30
31 PROCEDURE Build_Message_Attributes(p_item_key VARCHAR2,
32 x_header_text OUT NOCOPY VARCHAR2,
33 x_line_text OUT NOCOPY VARCHAR2,
34 x_order_number OUT NOCOPY VARCHAR2)
35 IS
36
37 l_header_text VARCHAR2(2000);
38 l_line_text VARCHAR2(2000);
39 l_header_rec Oe_Order_Pub.Header_Rec_Type;
40 l_line_rec Oe_Order_Pub.Line_Rec_Type;
41 l_header_id NUMBER;
42 l_line_id NUMBER;
43 l_item_name VARCHAR2(2000);
44 l_event_type VARCHAR2(200);
45 l_line_num VARCHAR2(1000);
46 newline CONSTANT VARCHAR2(2) := fnd_global.local_chr(10);
47
48 --
49 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
50 --
51
52 BEGIN
53
54 -- Fetch the item attributes
55 l_header_id := wf_engine.getItemAttrNumber('OENF', p_item_key, 'HEADER_ID');
56 l_line_id := wf_engine.getItemAttrNumber('OENF', p_item_key, 'LINE_ID');
57 l_event_type := wf_engine.getItemAttrText('OENF', p_item_key, 'STATUS_CODE');
58
59 -- Query the header information and build the header section
60 Oe_header_util.Query_Row(p_header_id => l_header_id,
61 x_header_rec =>l_header_rec);
62
63 l_header_text := 'Order Number# ' || l_header_rec.order_number || ' has an update of type '
64 ||l_event_type ||newline
65 ||newline
66 ||'Header Details' || newline
67 ||'---------------------' || newline
68 ||'Order Number : ' || l_header_rec.order_number || newline
69 ||'Order Status : ' || l_header_rec.flow_status_code || newline
70 ||'Ordered Date : ' || l_header_rec.ordered_date || newline
71 ||'Request Date : ' || l_header_rec.request_date ;
72
73 -- Query the line information and build the line section
74
75 IF l_line_id IS NOT NULL THEN
76
77 Oe_line_util.Query_Row(p_line_id => l_line_id,
78 x_line_rec => l_line_rec);
79
80 -- Set the org context
81
82 mo_global.set_policy_context('S', l_line_rec.org_id);
83
84 BEGIN
85 SELECT item
86 INTO l_item_name
87 FROM oe_items_v
88 WHERE item_id = l_line_rec.ordered_item_id
89 AND inventory_item_id = l_line_rec.inventory_item_id
90 AND item_identifier_type = l_line_rec.item_identifier_type;
91 EXCEPTION
92 WHEN OTHERS THEN
93 l_item_name := '';
94 END;
95
96 SELECT to_char(l_line_rec.line_number) ||
97 decode(l_line_rec.shipment_number, null, null, '.'
98 || to_char(l_line_rec.shipment_number))||
99 decode(l_line_rec.option_number, null, null, '.'
100 || to_char(l_line_rec.option_number)) ||
101 decode(l_line_rec.component_number, null, null,
102 decode(l_line_rec.option_number, null, '.',null)||
103 '.'||to_char(l_line_rec.component_number))||
104 decode(l_line_rec.service_number,null,null,
105 decode(l_line_rec.component_number, null, '.' , null) ||
106 decode(l_line_rec.option_number, null, '.', null ) ||
107 '.'|| to_char(l_line_rec.service_number)) LINE_NUM
108 INTO l_line_num
109 FROM dual;
110
111 l_line_text := 'Line Details' || newline
112 ||'-----------------' || newline
113 ||'Line Number : ' || l_line_num || newline
114 ||'Line Status : ' || l_line_rec.flow_status_code || newline
115 ||'Ordered Item : ' || l_item_name || newline
116 ||'Ordered Quantity : ' || l_line_rec.ordered_quantity || newline
117 ||'Ordered UOM : ' || l_line_rec.order_quantity_uom || newline
118 ||'Schedule ship Date : ' || l_line_rec.schedule_ship_date || newline
119 ||'Shipped Quantity : ' || Nvl(l_line_rec.shipped_quantity,0) || newline
120 ||'Actual Shipment Date : ' || l_line_rec.actual_shipment_date;
121
122 -- If the line is a part of the model, show the top model info as well
123 IF l_line_rec.top_model_line_id IS NOT NULL THEN
124
125 Oe_line_util.Query_Row(p_line_id => l_line_rec.top_model_line_id,
126 x_line_rec => l_line_rec);
127
128 BEGIN
129 SELECT item
130 INTO l_item_name
131 FROM oe_items_v
132 WHERE item_id = l_line_rec.ordered_item_id
133 AND inventory_item_id = l_line_rec.inventory_item_id
134 AND item_identifier_type = l_line_rec.item_identifier_type;
135 EXCEPTION
136 WHEN OTHERS THEN
137 l_item_name := '';
138 END;
139
140 SELECT to_char(l_line_rec.line_number) ||
141 decode(l_line_rec.shipment_number, null, null, '.'
142 || to_char(l_line_rec.shipment_number))||
143 decode(l_line_rec.option_number, null, null, '.'
144 || to_char(l_line_rec.option_number)) ||
145 decode(l_line_rec.component_number, null, null,
146 decode(l_line_rec.option_number, null, '.',null)||
147 '.'||to_char(l_line_rec.component_number))||
148 decode(l_line_rec.service_number,null,null,
149 decode(l_line_rec.component_number, null, '.' , null) ||
150 decode(l_line_rec.option_number, null, '.', null ) ||
151 '.'|| to_char(l_line_rec.service_number)) LINE_NUM
152 INTO l_line_num
153 FROM dual;
154
155 l_line_text := l_line_text || newline
156 ||'Model Line Details' || newline
157 ||'--------------------------' || newline
158 ||'Line Number : ' || l_line_num || newline
159 ||'Line Status : ' || l_line_rec.flow_status_code || newline
160 ||'Ordered Item : ' || l_item_name || newline
161 ||'Ordered Quantity : ' || l_line_rec.ordered_quantity || newline
162 ||'Ordered UOM : ' || l_line_rec.order_quantity_uom || newline
163 ||'Schedule ship Date : ' || l_line_rec.schedule_ship_date || newline
164 ||'Shipped Quantity : ' || Nvl(l_line_rec.shipped_quantity,0) || newline
165 ||'Actual Shipment Date : ' || l_line_rec.actual_shipment_date;
166
167 END IF;
168 END IF;
169
170 x_header_text := l_header_text;
171 x_line_text := l_line_text;
172 x_order_number := l_header_rec.order_number;
173 END;
174
175 PROCEDURE sendNotification (
176 itemtype IN VARCHAR2,
177 itemkey IN VARCHAR2,
178 actid IN NUMBER,
179 funcmode IN VARCHAR2,
180 resultout IN OUT NOCOPY VARCHAR2 /* file.sql.39 change */
181 )
182 IS
183
184 L_API_NAME CONSTANT VARCHAR2(30) := 'send_notification';
185 l_user_name VARCHAR2(200);
186 l_role_name VARCHAR2(320);
187 l_header_id NUMBER;
188 l_order_number NUMBER;
189 l_line_id NUMBER;
190 l_header_text VARCHAR2(2000);
191 l_line_text VARCHAR2(2000);
192 l_event_type VARCHAR2(50);
193 l_eid NUMBER;
194 l_nid NUMBER;
195
196 --
197 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
198 --
199
200 BEGIN
201 --
202 -- RUN mode - normal process execution
203 --
204 IF (funcmode = 'RUN') THEN
205
206 resultout := 'COMPLETE';
207
208 -- Build attribute values to generate message text
209 Build_Message_Attributes( p_item_key => itemkey,
210 x_header_text => l_header_text,
211 x_line_text => l_line_text,
212 x_order_number => l_order_number);
213
214 -- send notification for users in global user table
215
216 FOR idx IN 1..G_USER_TAB.count
217 LOOP
218 SAVEPOINT PRE_NOTIFICATION;
219 BEGIN
220
221 SELECT employee_id
222 INTO l_eid
223 FROM fnd_user
224 WHERE user_id = G_USER_TAB(idx).user_id;
225
226 IF l_eid IS NULL THEN
227 SELECT name
228 INTO l_role_name
229 FROM wf_roles
230 WHERE orig_system = 'FND_USR'
231 AND orig_system_id = G_USER_TAB(idx).user_id;
232 ELSE
233 SELECT name
234 INTO l_role_name
235 FROM wf_roles
236 WHERE orig_system = 'PER'
237 AND parent_orig_system = 'HZ_PARTY'
238 AND orig_system_id = l_eid;
239 END IF;
240
241 l_nid := WF_NOTIFICATION.Send(l_role_name, 'OENF', 'ORDER_STATUS_UPDATED');
242
243 WF_NOTIFICATION.SetAttrText(l_nid, 'HEADER_TEXT', l_header_text);
244 WF_NOTIFICATION.SetAttrText(l_nid, 'LINE_TEXT', l_line_text);
245 WF_NOTIFICATION.SetAttrText(l_nid, 'ORDER_NUMBER', l_order_number);
246
247 Wf_Notification.Denormalize_Notification(nid => l_nid);
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 IF l_debug_level > 0 THEN
252 Oe_debug_pub.ADD('Exception in sending notification for user : ' ||
253 G_USER_TAB(idx).user_id);
254 Oe_debug_pub.ADD('Error => ' || SQLERRM);
255 END IF;
256 -- Rollback for this user and continue sending for other users
257 ROLLBACK TO PRE_NOTIFICATION;
258 CONTINUE;
259 END;
260
261 END LOOP;
262
263 END IF; -- End for 'RUN' mode
264
265 --
266 -- CANCEL mode - activity 'compensation'
267 --
268 -- This is an event point is called with the effect of the activity must
269 -- be undone, for example when a process is reset to an earlier point
270 -- due to a loop back.
271 --
272 IF (funcmode = 'CANCEL') THEN
273
274 -- your cancel code goes here
275 NULL;
276
277 -- no result needed
278 resultout := 'COMPLETE';
279 RETURN;
280 END IF;
281
282 --
283 -- Other execution modes may be created in the future. Your
284 -- activity will indicate that it does not implement a mode
285 -- by returning null
286 --
287 -- resultout := '';
288 -- return;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 -- The line below records this function call in the error system
293 -- in the case of an exception.
294 wf_core.context('oe_notify_user_wf', 'Send_notification',
295 itemtype, itemkey, to_char(actid), funcmode);
296 -- start data fix project
297 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
298 p_itemtype => itemtype,
299 p_itemkey => itemkey);
300 OE_STANDARD_WF.Save_Messages;
301 OE_STANDARD_WF.Clear_Msg_Context;
302 -- end data fix project
303 RAISE;
304 END sendNotification;
305
306 PROCEDURE validateContingency (
307 itemtype IN VARCHAR2,
308 itemkey IN VARCHAR2,
309 actid IN NUMBER,
310 funcmode IN VARCHAR2,
311 resultout IN OUT NOCOPY VARCHAR2
312 )
313 IS
314
315 l_line_id NUMBER;
316 l_event_type VARCHAR2(200);
317 l_result VARCHAR2(10);
318 l_item_type VARCHAR2(100);
319
320 --
321 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
322 --
323
324 BEGIN
325
326 IF (funcmode = 'RUN') THEN
327
328 -- Fetch the item attributes
329 l_line_id := wf_engine.getItemAttrNumber('OENF', itemkey, 'LINE_ID');
330 l_event_type := wf_engine.getItemAttrText('OENF', itemkey, 'STATUS_CODE'); --10277829
331
332 -- No notifications for header events
333
334 IF l_line_id IS NOT NULL THEN
335
336 SELECT item_type_code
337 INTO l_item_type
338 FROM oe_order_lines_all
339 WHERE line_id = l_line_id;
340
341 -- Check for valid events which trigger notification
342 -- and do not send notification for class lines.
343
344 IF l_event_type NOT IN ('SCHEDULED','SHIPPED','SSD Change') OR
345 l_item_type IN ('CLASS') THEN
346 l_result := 'FAIL';
347 ELSE
348 l_result := 'PASS';
349 END IF;
350
351 ELSE
352 l_result := 'FAIL';
353 END IF;
354
355 resultout := l_result;
356
357 END IF; -- End for 'RUN' mode
358
359 --
360 -- CANCEL mode - activity 'compensation'
361 --
362 -- This is an event point is called with the effect of the activity must
363 -- be undone, for example when a process is reset to an earlier point
364 -- due to a loop back.
365 --
366 IF (funcmode = 'CANCEL') THEN
367
368 -- your cancel code goes here
369 NULL;
370
371 -- no result needed
372 resultout := 'COMPLETE';
373 RETURN;
374 END IF;
375
376 --
377 -- Other execution modes may be created in the future. Your
378 -- activity will indicate that it does not implement a mode
379 -- by returning null
380 --
381 -- resultout := '';
382 -- return;
383
384 EXCEPTION
385 -- Start of bug# 14657521
386 WHEN NO_DATA_FOUND then
387 resultout := 'FAIL'; -- End of bug# 14657521
388 WHEN OTHERS THEN
389 -- The line below records this function call in the error system
390 -- in the case of an exception.
391 wf_core.context('oe_notify_user_wf', 'validateContingency',
392 itemtype, itemkey, to_char(actid), funcmode);
393 -- start data fix project
394 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
395 p_itemtype => itemtype,
396 p_itemkey => itemkey);
397 OE_STANDARD_WF.Save_Messages;
398 OE_STANDARD_WF.Clear_Msg_Context;
399 -- end data fix project
400 RAISE;
401
402 END validateContingency;
403
404
405 PROCEDURE getUserList (
406 itemtype IN VARCHAR2,
407 itemkey IN VARCHAR2,
408 actid IN NUMBER,
409 funcmode IN VARCHAR2,
410 resultout IN OUT NOCOPY VARCHAR2 /* file.sql.39 change */
411 )
412 IS
413
414 l_header_id NUMBER;
415 l_cust_type VARCHAR2(100);
416 l_customer_id NUMBER := -1;
417 l_invoice_to_org_id NUMBER;
418 l_sold_to_org_id NUMBER;
419
420 --
421 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
422 --
423
424 BEGIN
425
426 IF (funcmode = 'RUN') THEN
427
428 resultout := 'COMPLETE';
429
430 -- Fetch the item attributes
431 l_header_id := wf_engine.getItemAttrNumber('OENF', itemkey, 'HEADER_ID');
432
433 -- Fetch the profile setup information
434 l_cust_type := Nvl(fnd_profile.value('ONT_EXTERNAL_CUST_USAGE_TYPE'), 'CUSTOMER_ID');
435
436 -- Get the customer_account_id
437 SELECT invoice_to_org_id, sold_to_org_id
438 INTO l_invoice_to_org_id, l_sold_to_org_id
439 FROM oe_order_headers_all
440 WHERE header_id = l_header_id;
441
442 IF l_cust_type = 'CUSTOMER_ID' THEN
443 l_customer_id := l_sold_to_org_id;
444 ELSIF l_cust_type = 'BILL_TO_ACCT_ID' THEN
445 SELECT cust.cust_account_id
446 INTO l_customer_id
447 FROM hz_cust_site_uses_all site,
448 hz_cust_acct_sites_all cas,
449 hz_cust_accounts cust,
450 hz_parties party
451 WHERE site.site_use_code = 'BILL_TO'
452 AND site.site_use_id = l_invoice_to_org_id
453 AND site.cust_acct_site_id = cas.cust_acct_site_id
454 AND cas.cust_account_id = cust.cust_account_id
455 AND cust.party_id=party.party_id;
456 END IF;
457
458 -- Populate the global table with notification recipients
459 IF l_customer_id IS NOT NULL THEN
460 BEGIN
461
462 SELECT DISTINCT user_id, user_name -- 12616799
463 BULK COLLECT INTO G_USER_TAB
464 FROM (
465 SELECT fu.user_id, fu.user_name
466 FROM hz_cust_account_roles hcar,
467 fnd_user fu
468 WHERE hcar.cust_account_id = l_customer_id
469 AND hcar.party_id = fu.customer_id
470 AND hcar.role_type = 'CONTACT'
471 AND hcar.current_role_state = 'A'
472 -- AND hcar.cust_acct_site_id IS NULL -- 12616799
473 AND (hcar.end_date IS NULL OR hcar.end_date > SYSDATE)
474 AND (fu.end_date IS NULL OR fu.end_date > SYSDATE)
475 AND fnd_profile.value_specific('ONT_SEND_STATUS_UPDATE_NOTIFICATION',fu.user_id) = 'Y'
476 UNION
477 SELECT fu.user_id, fu.user_name
478 FROM hz_cust_account_roles hcar,
479 fnd_user fu,
480 hz_relationships hr
481 WHERE hcar.cust_account_id = l_customer_id
482 AND hcar.party_id = hr.party_id
483 AND hcar.role_type = 'CONTACT'
484 AND hcar.current_role_state = 'A'
485 -- AND hcar.cust_acct_site_id IS NULL -- 12616799
486 AND (hcar.end_date IS NULL OR hcar.end_date > SYSDATE)
487 AND hr.subject_id = fu.customer_id
488 AND hr.party_id = hcar.party_id
489 AND hr.relationship_code = 'CONTACT_OF'
490 AND hr.status = 'A'
491 AND hr.subject_table_name = 'HZ_PARTIES'
492 AND (hr.end_date IS NULL OR hr.end_date > SYSDATE)
493 AND (fu.end_date IS NULL OR fu.end_date > SYSDATE)
494 AND fnd_profile.value_specific('ONT_SEND_STATUS_UPDATE_NOTIFICATION',fu.user_id) = 'Y'
495 );
496
497 EXCEPTION
498 WHEN NO_DATA_FOUND THEN
499 IF l_debug_level>0 THEN
500 Oe_debug_pub.ADD('No contacts found to send notification.');
501 END IF;
502 END;
503 END IF;
504
505 END IF; -- End for 'RUN' mode
506
507 --
508 -- CANCEL mode - activity 'compensation'
509 --
510 -- This is an event point is called with the effect of the activity must
511 -- be undone, for example when a process is reset to an earlier point
512 -- due to a loop back.
513 --
514 IF (funcmode = 'CANCEL') THEN
515
516 -- your cancel code goes here
517 NULL;
518
519 -- no result needed
520 resultout := 'COMPLETE';
521 RETURN;
522 END IF;
523
524 --
525 -- Other execution modes may be created in the future. Your
526 -- activity will indicate that it does not implement a mode
527 -- by returning null
528 --
529 -- resultout := '';
530 -- return;
531
532 EXCEPTION
533 WHEN OTHERS THEN
534 -- The line below records this function call in the error system
535 -- in the case of an exception.
536 wf_core.context('oe_notify_user_wf', 'getUserList',
537 itemtype, itemkey, to_char(actid), funcmode);
538 -- start data fix project
539 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
540 p_itemtype => itemtype,
541 p_itemkey => itemkey);
542 OE_STANDARD_WF.Save_Messages;
543 OE_STANDARD_WF.Clear_Msg_Context;
544 -- end data fix project
545 RAISE;
546
547 END getUserList;
548
549
550 END oe_notify_user_wf;
[Home] [Help]