[Home] [Help]
PACKAGE BODY: APPS.INV_CONSIGN_NOTIF_UTL
Source
1 PACKAGE BODY INV_CONSIGN_NOTIF_UTL AS
2 -- $Header: INVCNTFB.pls 120.0 2005/05/25 05:05:56 appldev noship $ --
3 --+=======================================================================+
4 --| Copyright (c) 2002 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVCNTFB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Consigned Inventory Diagnostics Send Notification to Buyer API |
13 --| |
14 --| PROCEDURE LIST |
15 --| Send_Notification |
16 --| |
17 --| HISTORY |
18 --| 10/06/03 vma Created |
19 --| 01/26/04 vma Bug 3396257: Added code to handled the case |
20 --| where a buyer (agent) is not assigned to a |
21 --| user. |
22 --+========================================================================
23
24 --===================
25 -- CONSTANTS
26 --===================
27 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'inv.plsql.' || G_PKG_NAME || '.';
28 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
29 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
30 G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.login_id;
31
32 --=============================================
33 -- GLOBAL VARIABLES
34 --=============================================
35 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
36
37
38 --===================
39 -- PROCEDURES AND FUNCTIONS
40 --===================
41
42 --========================================================================
43 -- PROCEDURE : Call_Workflow_Notification PRIVATE
44 --
45 -- PARAMETERS :
46 -- p_item_type IN
47 -- p_item_key IN
48 -- p_activity_name IN
49 -- p_user_name IN
50 -- p_agent_id IN
51 -- p_language IN
52 -- x_notification_id OUT
53 -- x_notification_date OUT
54 -- x_return_status OUT
55 --
56 -- COMMENT : Call Workflow to send Consigned Diagnostics notification
57 -- to a user.
58 --
59 -- CHANGE HISTORY :
60 -- 10/06/03 vma Created.
61 --=========================================================================
62 PROCEDURE Call_Workflow_Notification(
63 p_user_name IN fnd_user.user_name%TYPE,
64 p_agent_id IN mtl_consigned_diag_errors.agent_id%TYPE,
65 p_lang_code IN fnd_languages.language_code%TYPE,
66 x_notification_id OUT NOCOPY mtl_consigned_diag_errors.notification_id%TYPE,
67 x_notification_date OUT NOCOPY mtl_consigned_diag_errors.last_notification_date%TYPE,
68 x_return_status OUT NOCOPY VARCHAR2
69 )
70 IS
71
72 l_api_name VARCHAR2(30) := 'Call_Workflow_Notification';
73 l_item_type VARCHAR2(8) := 'INVCDNTF';
74 l_process_name VARCHAR2(25) := 'CONSIGN_DIAG_NOTIF_PROC';
75 l_activity_name VARCHAR2(18) := 'CONSIGN_DIAG_NOTIF';
76 l_item_key_prefix VARCHAR2(17) := 'CONSIGN_DIAG_NTF_';
77 l_item_key_s NUMBER;
78 l_item_key VARCHAR2(240);
79 l_agent_name per_all_people_f.full_name%TYPE;
80
81 BEGIN
82
83 IF g_fnd_debug = 'Y' THEN
84 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
85 THEN
86 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
87 , G_MODULE_PREFIX || l_api_name || '.invoked'
88 , 'Entry');
89 END IF;
90 END IF;
91
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 -- send notification to the user
95 SELECT mtl_consigned_diag_notif_s.NEXTVAL INTO l_item_key_s FROM DUAL;
96 l_item_key := l_item_key_prefix || to_char(l_item_key_s);
97
98
99 SELECT full_name
100 INTO l_agent_name
101 FROM per_all_people_f
102 WHERE person_id = p_agent_id
103 AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
104
105 IF g_fnd_debug = 'Y' THEN
106 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
107 THEN
108 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
109 , G_MODULE_PREFIX || l_api_name || '.invoked'
110 , 'Create workflow process');
111 END IF;
112 END IF;
113
114 wf_engine.CreateProcess(itemtype => l_item_type,
115 itemkey => l_item_key,
116 process => l_process_name);
117
118 wf_engine.SetItemAttrText(itemtype => l_item_type,
119 itemkey => l_item_key,
120 aname => 'USER_NAME',
121 avalue => p_user_name);
122
123 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
124 itemkey => l_item_key,
125 aname => 'AGENT_ID',
126 avalue => p_agent_id);
127
128 wf_engine.SetItemAttrText(itemtype => l_item_type,
129 itemkey => l_item_key,
130 aname => 'LANG_CODE',
131 avalue => p_lang_code);
132
133 wf_engine.SetItemAttrText(itemtype => l_item_type,
134 itemkey => l_item_key,
135 aname => 'BUYER',
136 avalue => l_agent_name);
137
138 wf_engine.StartProcess(itemtype => l_item_type,
139 itemkey => l_item_key);
140
141 IF g_fnd_debug = 'Y' THEN
142 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
143 THEN
144 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
145 , G_MODULE_PREFIX || l_api_name || '.invoked'
146 , 'Retrieve Notification ID and Sent date');
147 END IF;
148 END IF;
149
150 -- get notification_id and last_notification_date
151 SELECT wfn.notification_id, wfn.begin_date
152 INTO x_notification_id, x_notification_date
153 FROM wf_item_activity_statuses_v was, wf_notifications wfn
154 WHERE was.item_type = l_item_type
155 AND was.activity_name = l_activity_name
156 AND was.item_key = l_item_key
157 AND wfn.notification_id = was.notification_id;
158
159 IF g_fnd_debug = 'Y' THEN
160 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
161 THEN
162 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
163 , G_MODULE_PREFIX || l_api_name || '.invoked'
164 , 'Exit');
165 END IF;
166 END IF;
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171
172 IF g_fnd_debug = 'Y' THEN
173 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
174 THEN
175 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
176 , G_MODULE_PREFIX || l_api_name || '.invoked'
177 , 'Unexpected exception for user name ' || p_user_name ||
178 ' agent_id ' || p_agent_id || ' language ' || p_lang_code);
179 END IF;
180 END IF;
181
182 END Call_Workflow_Notification;
183
184
185 --==========================================================================
186 -- PROCEDURE NAME: Send_Notification
187 --
188 -- DESCRIPTION: Send Workflow Notification to buyers that have Consigned
189 -- Inventory Diagnostics errors to resolve.
190 --
191 -- PARAMETERS:
192 -- p_api_version REQUIRED API version
193 -- p_init_msg_list REQUIRED FND_API.G_TRUE to reset the message list
194 -- FND_API.G_FALSE to not reset it.
195 -- If pass NULL, it means FND_API.G_FALSE.
196 -- p_commit REQUIRED FND_API.G_TRUE to have API commit the change
197 -- FND_API.G_FALSE to not commit the change.
198 -- If pass NULL, it means FND_API.G_FALSE.
199 -- x_return_status REQUIRED Value can be
200 -- FND_API.G_RET_STS_SUCCESS
201 -- FND_API.G_RET_STS_ERROR
202 -- FND_API.G_RET_STS_UNEXP_ERROR
203 -- x_msg_count REQUIRED Number of messages on the message list
204 -- x_msg_data REQUIRED Return message data if message count is 1
205 -- p_notification_resend_days
206 -- REQUIRED Number of days elapsed before resending
207 -- notification to a buyer
208 --
209 -- COMMENT : Call Workflow to send Consigned Diagnostics notification
210 -- to buyers.
211 --
212 -- CHANGE HISTORY :
213 -- 10/06/03 vma Created.
214 --=========================================================================
215 PROCEDURE Send_Notification
216 ( p_api_version IN NUMBER
217 , p_init_msg_list IN VARCHAR2
218 , p_commit IN VARCHAR2
219 , x_return_status OUT NOCOPY VARCHAR2
220 , x_msg_count OUT NOCOPY NUMBER
221 , x_msg_data OUT NOCOPY VARCHAR2
222 , p_notification_resend_days IN NUMBER
223 )
224 IS
225
226 l_api_name CONSTANT VARCHAR2(30) := 'Send_Notification';
227 l_api_version CONSTANT NUMBER := 1.0;
228
229 l_agent_id mtl_consigned_diag_errors.agent_id%TYPE;
230 l_user_name fnd_user.user_name%TYPE;
231 l_language fnd_languages.nls_language%TYPE;
232 l_lang_code fnd_languages.language_code%TYPE;
233
234 TYPE notif_id_tbl_type IS TABLE OF
235 mtl_consigned_diag_errors.notification_id%TYPE
236 INDEX BY BINARY_INTEGER;
237
238 TYPE notif_date_tbl_type IS TABLE OF
239 mtl_consigned_diag_errors.last_notification_date%TYPE
240 INDEX BY BINARY_INTEGER;
241
242 TYPE notif_status_tbl_type IS TABLE OF VARCHAR2(20)
243 INDEX BY BINARY_INTEGER;
244
245 TYPE agent_id_tbl_type IS TABLE OF
246 MTL_CONSIGNED_DIAG_ERRORS.agent_id%TYPE
247 INDEX BY BINARY_INTEGER;
248
249 agent_id_tbl agent_id_tbl_type;
250 notif_id_tbl notif_id_tbl_type;
251 notif_date_tbl notif_date_tbl_type;
252 notif_status_tbl notif_status_tbl_type;
253
254 BEGIN
255
256 SAVEPOINT Send_Notification_PUB;
257
258 IF g_fnd_debug = 'Y' THEN
259 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
260 THEN
261 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
262 , G_MODULE_PREFIX || l_api_name || '.invoked'
263 , 'Entry');
264 END IF;
265 END IF;
266
267 IF FND_API.To_Boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
268 FND_MSG_PUB.initialize;
269 END IF;
270
271 IF NOT FND_API.Compatible_API_Call( l_api_version
272 , p_api_version
273 , l_api_name
274 , G_PKG_NAME
275 )
276 THEN
277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
278 END IF;
279
280 x_return_status := FND_API.G_RET_STS_SUCCESS;
281
282 SELECT DISTINCT agent_id BULK COLLECT INTO agent_id_tbl
283 FROM mtl_consigned_diag_errors
284 WHERE agent_id IS NOT NULL
285 AND (notification_id IS NULL
286 OR last_notification_date + p_notification_resend_days <= SYSDATE);
287
288 -- send notification to the buyer
289 FOR i IN agent_id_tbl.FIRST..agent_id_tbl.LAST
290 LOOP
291 l_agent_id := agent_id_tbl(i);
292
293 -- Bug #3396257: Initialize tbl for when an agent is not
294 -- assigned to any user.
295 notif_status_tbl(i) := NULL;
296 notif_id_tbl(i) := NULL;
297 notif_date_tbl(i) := NULL;
298
299 FOR rec IN (SELECT user_name, user_id
300 FROM fnd_user
301 WHERE employee_id = l_agent_id)
302 LOOP
303 -- get user's preferred lanaguage
304 l_language := FND_PROFILE.Value_Specific('ICX_LANGUAGE', rec.user_id);
305
306 IF l_language IS NOT NULL THEN
307
308 SELECT language_code
309 INTO l_lang_code
310 FROM FND_LANGUAGES
311 WHERE nls_language = l_language;
312
313 Call_Workflow_Notification(
314 p_user_name => rec.user_name
315 , p_agent_id => l_agent_id
316 , p_lang_code => l_lang_code
317 , x_notification_id => notif_id_tbl(i)
318 , x_notification_date => notif_date_tbl(i)
319 , x_return_status => notif_status_tbl(i));
320
321 ELSE
322 IF g_fnd_debug = 'Y' THEN
323 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
324 THEN
325 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
326 , G_MODULE_PREFIX || l_api_name || '.invoked'
327 , 'Preferred language for user ' || rec.user_name ||
328 ', user_id ' || rec.user_id || ' not found.');
329 END IF;
330 END IF;
331 END IF;
332
333 END LOOP;
334
335 END LOOP;
336
337 -- update last_notification_date and notification_id
338 FORALL j IN agent_id_tbl.FIRST..agent_id_tbl.LAST
339 UPDATE mtl_consigned_diag_errors
340 SET notification_id = notif_id_tbl(j),
341 last_notification_date = notif_date_tbl(j),
342 last_update_date = SYSDATE,
343 last_updated_by = G_USER_ID,
344 last_update_login = G_LOGIN_ID
345 WHERE notif_status_tbl(j) = G_RET_STS_SUCCESS
346 AND agent_id = agent_id_tbl(j);
347
348 IF p_commit = FND_API.G_TRUE THEN
349 COMMIT;
350 END IF;
351
352 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
353 , p_data => x_msg_data);
354 IF g_fnd_debug = 'Y' THEN
355 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
356 THEN
357 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
358 , G_MODULE_PREFIX || l_api_name || '.invoked'
359 , 'Exit');
360 END IF;
361 END IF;
362
363 EXCEPTION
364
365 WHEN OTHERS THEN
366 ROLLBACK TO Send_Notification_PUB;
367 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
368 , p_data => x_msg_data);
369
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371
372 IF (g_fnd_debug = 'Y') THEN
373 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
374 THEN
375 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
376 , G_MODULE_PREFIX || l_api_name || '.others_exception'
377 , 'Exception');
378 END IF;
379 END IF;
380
381 END Send_Notification;
382
383 END INV_CONSIGN_NOTIF_UTL;