[Home] [Help]
Skip to content
PACKAGE BODY: APPS.FLM_EKB_BUSINESS_EVENT_PKG
Source
1 package body FLM_EKB_BUSINESS_EVENT_PKG as
2 /* $Header: flmekbeb.pls 120.2.12020000.2 2012/08/09 18:51:10 pding ship $ */
3 TXN_TYPE_CREATION NUMBER := 1;
4 TXN_TYPE_UPDATE NUMBER := 2;
5
6 /*Internal Procedure to log debug message*/
7 /* Internal Procedure to log debug messages */
8 PROCEDURE log(p_proc IN VARCHAR2,
9 p_msg IN VARCHAR2) IS
10 BEGIN
11 inv_log_util.trace(p_msg, p_proc, 9);
12 END log;
13 /*Below procedure raise business event for pull sequence creation and updation
14 p_txn_type: 1 Creation
15 2 Updation
16 */
17 PROCEDURE Raise_Pull_Sequence_Event(p_pull_Sequence_id IN NUMBER,
18 p_from_planning in VARCHAR2 DEFAULT 'N',
19 p_txn_type in NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 x_return_status OUT NOCOPY VARCHAR2)
22 IS
23 CURSOR c_pullSequence
24 IS
25 SELECT organization_id,
26 inventory_item_id,
27 kanban_plan_id,
28 subinventory_name,
29 locator_id,
30 source_type,
31 kanban_size,
32 number_of_cards
33 from mtl_kanban_pull_sequences
34 where pull_sequence_id = p_pull_Sequence_Id;
35
36 l_pull_sequence c_pullSequence%ROWTYPE;
37 too_many_match Exception;
38 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
39 l_event_name VARCHAR2(240);
40 l_event_key VARCHAR2(240);
41 l_event_num NUMBER;
42 l_event_data clob;
43 l_send_date date;
44 BEGIN
45 l_send_date := sysdate;
46
47 if p_txn_type = TXN_TYPE_CREATION then
48 l_event_name := 'oracle.apps.flm.ekanban.pullSeqCreation';
49 elsif p_txn_type = TXN_TYPE_UPDATE then
50 l_event_name := 'oracle.apps.flm.ekanban.pullSeqUpdate';
51 end if;
52
53 SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual;
54 l_event_key := SUBSTRB(l_event_name, 1, 255) || '-' || l_event_num;
55
56 OPEN c_pullSequence;
57 FETCH c_pullSequence INTO l_pull_sequence;
58 if c_pullSequence%notfound then
59 raise no_data_found;
60 elsif c_pullSequence%ROWCOUNT <> 1 then
61 raise too_many_match;
62 end if;
63 CLOSE c_pullSequence;
64
65 wf_event.AddParameterToList(p_name=>'organization_id',
66 p_value=>l_pull_sequence.organization_id,
67 p_parameterlist=>l_parameter_list);
68 wf_event.AddParameterToList(p_name=>'pull_sequence_id',
69 p_value=>p_pull_Sequence_id,
70 p_parameterlist=>l_parameter_list);
71 wf_event.AddParameterToList(p_name=>'inventory_item_id',
72 p_value=>l_pull_sequence.inventory_item_id,
73 p_parameterlist=>l_parameter_list);
74 wf_event.AddParameterToList(p_name=>'subinventory_name',
75 p_value=>l_pull_sequence.subinventory_name,
76 p_parameterlist=>l_parameter_list);
77 wf_event.AddParameterToList(p_name=>'locator_id',
78 p_value=>l_pull_sequence.locator_id,
79 p_parameterlist=>l_parameter_list);
80 wf_event.AddParameterToList(p_name=>'source_type',
81 p_value=>l_pull_sequence.source_type,
82 p_parameterlist=>l_parameter_list);
83 wf_event.AddParameterToList(p_name=>'kanban_size',
84 p_value=>l_pull_sequence.kanban_size,
85 p_parameterlist=>l_parameter_list);
86 wf_event.AddParameterToList(p_name=>'number_of_cards',
87 p_value=>l_pull_sequence.number_of_cards,
88 p_parameterlist=>l_parameter_list);
89
90 if( l_event_name = 'oracle.apps.flm.ekanban.pullSeqUpdate') then
91 wf_event.AddParameterToList(p_name=>'update_from_planning',
92 p_value=>p_from_planning,
93 p_parameterlist=>l_parameter_list);
94 end if;
95
96 WF_EVENT.Raise( p_event_name => l_event_name
97 ,p_event_key => l_event_key
98 ,p_event_data => l_event_data
99 ,p_parameters => l_parameter_list
100 ,p_send_date => l_send_date);
101
102 l_parameter_list.DELETE;
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104
105 EXCEPTION
106 WHEN no_data_found THEN
107
108 log('Raise_Pull_Sequence_Event', 'no_data_found exception, Invalid pull sequence id: '||p_pull_Sequence_id);
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110 x_msg_data := 'Invalid Pull Sequence';
111 if(c_pullSequence%ISOPEN) then
112 close c_pullSequence;
113 end if;
114
115 WHEN too_many_match THEN
116
117 log('Raise_Pull_Sequence_Event', 'More than one row found for pull sequence id: '||p_pull_Sequence_id);
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 x_msg_data := 'Invalid Pull Sequence';
120 if(c_pullSequence%ISOPEN) then
121 close c_pullSequence;
122 end if;
123
124 when others THEN
125
126 log('Raise_Pull_Sequence_Event', 'pull sequence id: '||p_pull_Sequence_id||'-'||SQLERRM);
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128 x_msg_data := SQLERRM;
129
130 if(c_pullSequence%ISOPEN) then
131 close c_pullSequence;
132 end if;
133
134 END Raise_Pull_Sequence_Event;
135
136 /*Below procedure raise business event for kanban card creation and update
137 p_txn_type: 1 Creation
138 2 Update
139 */
140 PROCEDURE Raise_Kanban_Card_Event(p_kanban_card_id IN NUMBER,
141 p_txn_type in NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2,
143 x_return_status OUT NOCOPY VARCHAR2)
144 IS
145 CURSOR c_kanbanCard
146 IS
147 SELECT kanban_card_id,
148 organization_id,
149 kanban_card_number,
150 pull_sequence_id
151 from mtl_kanban_cards
152 where kanban_card_id = p_kanban_card_id;
153
154 l_kanban_card c_kanbanCard%ROWTYPE;
155 too_many_match Exception;
156 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
157 l_event_name VARCHAR2(240);
158 l_event_key VARCHAR2(240);
159 l_event_num NUMBER;
160 l_event_data clob;
161 l_send_date date;
162 BEGIN
163 l_send_date := sysdate;
164
165 if p_txn_type = TXN_TYPE_CREATION then
166 l_event_name := 'oracle.apps.flm.ekanban.kanbanCardCreation';
167 elsif p_txn_type = TXN_TYPE_UPDATE then
168 l_event_name := 'oracle.apps.flm.ekanban.kanbanCardUpdate';
169 end if;
170
171 OPEN c_kanbanCard;
172 FETCH c_kanbanCard INTO l_kanban_card;
173
174 if c_kanbanCard%notfound then
175 raise no_data_found;
176 elsif c_kanbanCard%ROWCOUNT <> 1 then
177 raise too_many_match;
178 end if;
179 CLOSE c_kanbanCard;
180
181
182 SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual;
183 l_event_key := SUBSTRB(l_event_name, 1, 255) || '-' || l_event_num;
184
185 wf_event.AddParameterToList(p_name=>'kanban_card_id',
186 p_value=>p_kanban_card_id,
187 p_parameterlist=>l_parameter_list);
188 wf_event.AddParameterToList(p_name=>'organization_id',
189 p_value=>l_kanban_card.organization_id,
190 p_parameterlist=>l_parameter_list);
191 wf_event.AddParameterToList(p_name=>'kanban_card_number',
192 p_value=>l_kanban_card.kanban_card_number,
193 p_parameterlist=>l_parameter_list);
194 wf_event.AddParameterToList(p_name=>'pull_sequence_id',
195 p_value=>l_kanban_card.pull_sequence_id,
196 p_parameterlist=>l_parameter_list);
197
198
199 WF_EVENT.Raise( p_event_name => l_event_name
200 ,p_event_key => l_event_key
201 ,p_event_data => l_event_data
202 ,p_parameters => l_parameter_list
203 ,p_send_date => l_send_date);
204
205 l_parameter_list.DELETE;
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207
208 EXCEPTION
209 WHEN no_data_found THEN
210
211 log('Raise_Kanban_Card_Event', 'no_data_found exception, Invalid kanban card id: '||p_kanban_card_id);
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 x_msg_data := 'Invalid kanban card';
214 if(c_kanbanCard%ISOPEN) then
215 close c_kanbanCard;
216 end if;
217
218 WHEN too_many_match THEN
219
220 log('Raise_Kanban_Card_Event', 'More than one row found for kanban card id: '||p_kanban_card_id);
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 x_msg_data := 'Invalid Pull Sequence';
223 if(c_kanbanCard%ISOPEN) then
224 close c_kanbanCard;
225 end if;
226
227 when others THEN
228
229 log('Raise_Kanban_Card_Event', 'kanban card id: '||p_kanban_card_id||'-'||SQLERRM);
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231 x_msg_data := SQLERRM;
232
233 if(c_kanbanCard%ISOPEN) then
234 close c_kanbanCard;
235 end if;
236
237 END Raise_Kanban_Card_Event;
238
239
240 /*Below procedure raise business event for card status change
241
242 PROCEDURE RaiseCardStatucChangeEvent(p_kanban_card_id IN NUMBER,
243 p_from_status IN NUMBER,
244 p_to_status IN NUMBER,
245 x_msg_data OUT NOCOPY VARCHAR2,
246 x_return_status OUT NOCOPY VARCHAR2)
247 IS
248 CURSOR c_kanbanCard
249 IS
250 SELECT kanban_card_id,
251 kanban_card_number,
252 pull_sequence_id,
253 card_status,
254 supply_status
255 from mtl_kanban_cards
256 where kanban_card_id = p_kanban_card_id;
257
258 l_kanban_card c_kanbanCard%ROWTYPE;
259 too_many_match Exception;
260 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
261 l_event_name VARCHAR2(240);
262 l_event_key VARCHAR2(240);
263 l_event_num NUMBER;
264 l_event_data clob;
265 l_send_date date;
266 BEGIN
267 l_send_date := sysdate;
268 l_event_name := 'oracle.apps.flm.ekanban.cardStatusChange';
269
270 SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual;
271 l_event_key := SUBSTRB(l_event_name, 1, 255) || '-' || l_event_num;
272
273 OPEN c_kanbanCard;
274 FETCH c_kanbanCard INTO l_kanban_card;
275
276 if c_kanbanCard%notfound then
277 raise no_data_found;
278 elsif c_kanbanCard%ROWCOUNT <> 1 then
279 raise too_many_match;
280 end if;
281 CLOSE c_kanbanCard;
282
283 wf_event.AddParameterToList(p_name=>'kanban_card_id',
284 p_value=>p_kanban_card_id,
285 p_parameterlist=>l_parameter_list);
286 wf_event.AddParameterToList(p_name=>'kanban_card_number',
287 p_value=>l_kanban_card.kanban_card_number,
288 p_parameterlist=>l_parameter_list);
289 wf_event.AddParameterToList(p_name=>'pull_sequence_id',
290 p_value=>l_kanban_card.pull_sequence_id,
291 p_parameterlist=>l_parameter_list);
292 wf_event.AddParameterToList(p_name=>'old_card_status',
293 p_value=>p_from_status,
294 p_parameterlist=>l_parameter_list);
295 wf_event.AddParameterToList(p_name=>'new_card_status',
296 p_value=>p_to_status,
297 p_parameterlist=>l_parameter_list);
298
299
300 WF_EVENT.Raise( p_event_name => l_event_name
301 ,p_event_key => l_event_key
302 ,p_event_data => l_event_data
303 ,p_parameters => l_parameter_list
304 ,p_send_date => l_send_date);
305
306 l_parameter_list.DELETE;
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308
309 EXCEPTION
310 WHEN no_data_found THEN
311
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 x_msg_data := 'Invalid kanban card';
314 if(c_kanbanCard%ISOPEN) then
315 close c_kanbanCard;
316 end if;
317
318 WHEN too_many_match THEN
319
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
321 x_msg_data := 'Invalid Pull Sequence';
322 if(c_kanbanCard%ISOPEN) then
323 close c_kanbanCard;
324 end if;
325
326 when others THEN
327
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
329 x_msg_data := SQLERRM;
330
331 if(c_kanbanCard%ISOPEN) then
332 close c_kanbanCard;
333 end if;
334
335 END RaiseCardStatucChangeEvent;
336 */
337
338 /*For testing supscription and sending notificaiton internally. Need to remove it later*/
339
340 function test_business_event (p_subscription_guid in raw,
341 p_event in out NOCOPY WF_EVENT_T) return varchar2 is
342 PRAGMA AUTONOMOUS_TRANSACTION;
343 l_return_status VARCHAR2(10);
344 l_msg_count NUMBER;
345 l_msg_data VARCHAR2(1000);
346 l_exception_id Number;
347 begin
348
349 begin
350 select exception_id
351 into l_exception_id
352 from wip_exceptions where rownum <2;
353 EXCEPTION
354 WHEN NO_DATA_FOUND
355 THEN
356 log('test_business_event', 'No Exception exsist for test subscription');
357 END;
358
359 INVOKE_NOTIFICATION(p_exception_id => l_exception_id,
360 p_init_msg_list => null,
361 x_return_status =>l_return_status,
362 x_msg_count =>l_msg_count,
363 x_msg_data =>l_msg_data);
364
365 commit;
366 return 'SUCCESS';
367 exception
368 when others then
369 WF_CORE.CONTEXT('FLM_EKB_BUSINESS_EVENT_PKG', 'test_business_event',
370 p_event.getEventName( ), p_subscription_guid);
371 WF_EVENT.setErrorInfo(p_event, 'ERROR');
372 return 'ERROR';
373 end;
374
375 /*For testing supscription and sending notificaiton internally. Need to remove it later*/
376
377 PROCEDURE INVOKE_NOTIFICATION(p_exception_id IN NUMBER,
378 p_init_msg_list IN VARCHAR2,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2)
382
383 IS
384
385 l_seq varchar2(10);
386 l_ItemType VARCHAR2(8);
387 l_ItemKey VARCHAR2(240) ;
388
389 l_job_name VARCHAR2(240);
390 l_op_seq_num NUMBER;
391 l_res_name VARCHAR2(10);
392 l_comp_name VARCHAR2(40);
393
394 x_progress varchar2(4) := '000';
395
396 begin
397
398 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
399 THEN
400 FND_MSG_PUB.initialize;
401 END IF;
402 x_return_status := FND_API.G_RET_STS_SUCCESS;
403
404 l_itemtype:='WIPEXPWK';
405
406 select
407 wen.wip_entity_name, we.operation_seq_num, br.resource_code, msi.concatenated_segments
408 into
409 l_job_name, l_op_seq_num, l_res_name, l_comp_name
410 from
411 wip_exceptions we, wip_entities wen, bom_resources br, mtl_system_items_vl msi
412 where
413 we.organization_id = wen.organization_id and
414 we.wip_entity_id = wen.wip_entity_id and
415 we.organization_id = br.organization_id(+) and
416 we.resource_id = br.resource_id (+) and
417 we.organization_id = msi.organization_id(+) and
418 we.component_item_id = msi.inventory_item_id (+) and
419 we.exception_id = p_exception_id;
420
421 select to_char(WIP_EXP_NOTIF_WF_ITEMKEY_S.NEXTVAL)
422 into l_seq from sys.dual;
423
424 l_itemkey := to_char (p_exception_id)|| '-' || l_seq;
425
426
427 wf_engine.createProcess ( ItemType => l_ItemType,
428 ItemKey => l_ItemKey,
429 Process => 'WIP_EXCEPTION_REPORT');
430
431 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
432 itemkey => l_itemkey,
433 aname => 'EXCEPTION_ID',
434 avalue => p_exception_id);
435
436 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
437 itemkey => l_itemkey,
438 aname => 'TO_RESPONSIBILITY',
439 avalue => 'FND_RESP|WIP|WIP_WS_SUPERVISOR|STANDARD');
440
441 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
442 itemkey => l_itemkey,
443 aname => 'FROM_RESPONSIBILITY',
444 avalue => 'FND_RESP|WIP|WIP_WS_OPERATOR|STANDARD');
445
446 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
447 itemkey => l_itemkey,
448 aname => 'JOB_NAME',
449 avalue => l_job_name);
450
451 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
452 itemkey => l_itemkey,
453 aname => 'OP_SEQ_NUM',
454 avalue => l_op_seq_num);
455
456 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
457 itemkey => l_itemkey,
458 aname => 'RESOURCE_NAME',
459 avalue => l_res_name);
460
461 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
462 itemkey => l_itemkey,
463 aname => 'COMPONENT_NAME',
464 avalue => l_comp_name);
465
466 wf_engine.StartProcess ( ItemType => l_ItemType,
467 ItemKey => l_ItemKey );
468
469
470 EXCEPTION
471
472 WHEN OTHERS THEN
473
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
476 FND_MSG_PUB.Add_Exc_Msg ('WIP_EXP_NOTIF_WF_PKG' ,'Invoke_Notification');
477 END IF;
478 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
479
480 WF_CORE.context('WIP_EXP_NOTIF_WF_PKG' , 'InvokeNotification',
481 x_progress);
482 RAISE;
483
484 end INVOKE_NOTIFICATION;
485
486
487 end FLM_EKB_BUSINESS_EVENT_PKG;