[Home] [Help]
PACKAGE BODY: APPS.M4U_EGOEVNT_HANDLER
Source
1 PACKAGE BODY m4u_egoevnt_handler AS
2 /* $Header: M4UEGOHB.pls 120.0 2005/05/24 16:19:24 appldev noship $ */
3 l_debug_level NUMBER ;
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'm4u_egoevnt_handler';
5
6
7 -- Name
8 -- EGO_EVENT_SUB
9 -- Purpose
13 -- default parameters.
10 -- This function is used to get the parameters from the EGO event
11 -- 'oracle.apps.ego.gtin.uccnetEvent'.This procedure in turn raises
12 -- event for triggering the generic M4U workflow after setting the
14 -- Arguments
15 --
16 -- Notes
17 -- No specific notes.
18
19 FUNCTION EGO_EVENT_SUB(
20 p_subscription_guid IN RAW,
21 p_event IN OUT NOCOPY WF_EVENT_T
22 ) RETURN VARCHAR2
23
24 IS
25 l_m4u_parameters wf_parameter_list_t;
26
27 l_batch_mode VARCHAR2(2);
28
29 l_target_market VARCHAR2(50);
30 l_tp_gln VARCHAR2(50);
31
32 l_xmlg_transaction_type VARCHAR2(100);
33 l_xmlg_transaction_subtype VARCHAR2(100);
34 l_coll_type VARCHAR2(100);
35 l_xml_event_key VARCHAR2(240);
36 l_xmlg_document_id VARCHAR2(256);
37 l_error_msg VARCHAR2(255);
38 l_debug_mode VARCHAR2(255);
39
40 l_error_code NUMBER;
41 l_gtin_count NUMBER;
42 l_ego_batch_id NUMBER;
43 l_ego_subbatch_id NUMBER;
44
45 l_doc_type VARCHAR2(100);
46 l_owner_role VARCHAR2(100);
47
48 l_doc_no VARCHAR2(255);
49 l_partner_doc_no VARCHAR2(255);
50
51 l_msg_data VARCHAR2(2000);
52 l_create_msg_text VARCHAR2(2000);
53
54
55 l_event_key VARCHAR2(50);
56
57 l_ecx_parameter1 VARCHAR2(150);
58 l_ecx_parameter2 VARCHAR2(150);
59 l_ecx_parameter3 VARCHAR2(150);
60 l_ecx_parameter4 VARCHAR2(150);
61 l_ecx_parameter5 VARCHAR2(150);
62
63 l_attribute1 VARCHAR2(150);
64 l_attribute2 VARCHAR2(150);
65 l_attribute3 VARCHAR2(150);
66 l_attribute4 VARCHAR2(150);
67 l_attribute5 VARCHAR2(150);
68 l_attribute6 VARCHAR2(150);
69 l_attribute7 VARCHAR2(150);
70 l_attribute8 VARCHAR2(150);
71 l_attribute9 VARCHAR2(150);
72 l_attribute10 VARCHAR2(150);
73 l_attribute11 VARCHAR2(150);
74 l_attribute12 VARCHAR2(150);
75 l_attribute13 VARCHAR2(150);
76 l_attribute14 VARCHAR2(150);
77 l_attribute15 VARCHAR2(150);
78
79 l_org_id NUMBER;
80
81 l_dattribute1 DATE;
82 l_dattribute2 DATE;
83 l_dattribute3 DATE;
84 l_dattribute4 DATE;
85 l_dattribute5 DATE;
86
87 l_event_type ego_uccnet_events.event_type%TYPE;
88 l_event_action ego_uccnet_events.event_action%TYPE;
89 l_gtin ego_uccnet_events.gtin%TYPE;
90 l_top_gtin ego_uccnet_events.top_gtin%TYPE;
91 l_doc_owner ego_uccnet_events.last_updated_by%TYPE;
92 l_item_number mtl_system_items_kfv.concatenated_segments%TYPE;
93
94 BEGIN
95
96 -- Sets the debug mode to be FILE
97 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
98
99 IF (l_Debug_Level <= 2) THEN
100 cln_debug_pub.Add('==========ENTERING EGO_EVENT_SUB ===========', 2);
101 END IF;
102
103 l_m4u_parameters := p_event.getParameterList();
104
105 -- parameters obtained with the ego event.
106 IF (l_Debug_Level <= 1) THEN
107 cln_debug_pub.Add('----------- PARAMETERS OBTAINED ----------', 1);
108 END IF;
109
110 l_ego_batch_id := WF_EVENT.getValueForParameter('ECX_DOCUMENT_ID',l_m4u_parameters);
111 IF (l_Debug_Level <= 1) THEN
112 cln_debug_pub.Add('ECX DOCUMENT ID -EGO BATCH ID ----'||l_ego_batch_id, 1);
113 END IF;
114
115 l_ego_subbatch_id := WF_EVENT.getValueForParameter('ECX_PARAMETER1',l_m4u_parameters);
116 IF (l_Debug_Level <= 1) THEN
117 cln_debug_pub.Add('ECX Param1 -EGO SUB BATCH ID ----'||l_ego_subbatch_id, 1);
118 END IF;
119
120 IF (l_Debug_Level <= 1) THEN
121 cln_debug_pub.Add('------------------------------------------', 1);
122 END IF;
123
124 -- default values
125 l_xmlg_transaction_type := 'M4U';
126 l_xmlg_transaction_subtype := 'UNKNOWN';
127 l_coll_type := 'UNKNOWN';
128 l_doc_type := 'UNKNOWN';
129
130 l_create_msg_text := 'M4U_REGISTRATION_INITIATED';
131 l_batch_mode := 'N';
132 l_xmlg_document_id := l_ego_batch_id || ':' || l_ego_subbatch_id;
133 IF (l_Debug_Level <= 1) THEN
134 cln_debug_pub.Add('ECX document id ----'|| l_xmlg_document_id, 1);
135 END IF;
136
140 FROM dual;
137 -- obtain a unique event-id
138 SELECT m4u_wlqid_s.NEXTVAL
139 INTO l_xml_event_key
141
142 l_xml_event_key := 'M4U_EGOEVT_'|| l_xml_event_key;
143 IF (l_Debug_Level <= 1) THEN
144 cln_debug_pub.Add('XML EVENT KEY ----'||l_xml_event_key,1);
145 END IF;
146
147 /* get count of events, in case of REGISTRATION */
148 /* if count > 1, we use RCIR in Batch mode for Registering the items */
149 /* else we go for simple RCIR message */
150 SELECT count(*), event_type
151 INTO l_gtin_count, l_event_type
152 FROM ego_uccnet_events
153 WHERE batch_id = l_ego_batch_id
154 AND subbatch_id = l_ego_subbatch_id
155 AND gtin = top_gtin
156 group by event_type;
157
158 IF (l_Debug_Level <= 1) THEN
159 cln_debug_pub.Add('Returned from SELECT query ..>>> ', 1);
160 cln_debug_pub.Add('Row Count -- '||l_gtin_count, 1);
161 cln_debug_pub.Add('Event Type -- '||l_event_type, 1);
162 END IF;
163
164 IF (l_event_type = 'REGISTRATION') THEN
165 l_xmlg_transaction_type := 'M4U';
166 l_xmlg_transaction_subtype := 'RCIR';
167 l_doc_type := 'M4U_RCIR';
168
169 IF (l_gtin_count > 1) THEN
170 l_doc_type := 'M4U_RCIR_BATCH';
171 l_ecx_parameter5 := 'RCIR_BATCH';
172 l_batch_mode := 'Y';
173
174 wf_event.AddParameterToList(
175 p_name => 'ECX_PARAMETER5',
176 p_value => l_ecx_parameter5,
177 p_parameterlist => l_m4u_parameters );
178 IF (l_Debug_Level <= 1) THEN
179 cln_debug_pub.Add('ECX_PARAMETER5 - '||l_ecx_parameter5,1);
180 END IF;
181 END IF;
182 ELSIF (l_event_type = 'PUBLICATION') THEN
183 l_xmlg_transaction_type := 'M4U';
184 l_xmlg_transaction_subtype := 'CIN';
185 l_doc_type := 'M4U_CIN';
186 END IF;
187
188
189 IF (l_gtin_count = 1) THEN
190 IF (l_Debug_Level <= 1) THEN
191 cln_debug_pub.Add('Gtin count = 1, Querying the table for more attributes...>> ', 1);
192 END IF;
193
194 SELECT e.event_action, e.gtin, e.top_gtin,
195 e.last_updated_by,e.target_market, e.tp_gln,
196 e.organization_id, f.user_name, mtlkfv.concatenated_segments
197 INTO l_event_action, l_gtin, l_top_gtin,
198 l_doc_owner, l_target_market, l_tp_gln,
199 l_org_id, l_owner_role, l_item_number
200 FROM ego_uccnet_events e,
201 fnd_user f,
202 mtl_system_items_kfv mtlkfv
203 WHERE e.batch_id = l_ego_batch_id
204 AND e.subbatch_id = l_ego_subbatch_id
205 AND e.gtin = e.top_gtin
206 AND e.event_type = l_event_type
207 AND e.INVENTORY_ITEM_ID = mtlkfv.INVENTORY_ITEM_ID
208 AND e.ORGANIZATION_ID = mtlkfv.ORGANIZATION_ID
209 AND e.last_updated_by = f.user_id(+);
210
211 IF (l_Debug_Level <= 1) THEN
212 cln_debug_pub.Add('Returned from SELECT query ..>>> ', 1);
213 cln_debug_pub.Add('Event Action - '||l_event_action, 1);
214 cln_debug_pub.Add('Gtin - '||l_gtin, 1);
215 cln_debug_pub.Add('Top GTIN - '||l_top_gtin, 1);
216 cln_debug_pub.Add('Doc Owner - '||l_doc_owner, 1);
217 cln_debug_pub.Add('Target Market - '||l_target_market, 1);
218 cln_debug_pub.Add('TP GLN - '||l_tp_gln, 1);
219 cln_debug_pub.Add('ORG ID - '||l_org_id, 1);
220 cln_debug_pub.Add('Owner Role - '||l_owner_role, 1);
221 cln_debug_pub.Add('Item Number - '||l_item_number, 1);
222 END IF;
223
224 -- setting the values for the attributes of the generic workflow
225 l_coll_type := l_doc_type||'_'||l_event_action;
226
227 wf_event.AddParameterToList(
228 p_name => 'M4U_DOC_NO',
229 p_value => l_gtin,
230 p_parameterlist => l_m4u_parameters );
231
232 IF (l_Debug_Level <= 1) THEN
233 cln_debug_pub.Add('M4U_DOC_NO - '||l_gtin,1);
234 END IF;
235
236 wf_event.AddParameterToList(
237 p_name => 'M4U_PARTNER_DOC_NO',
238 p_value => l_item_number,
239 p_parameterlist => l_m4u_parameters );
240
241 IF (l_Debug_Level <= 1) THEN
242 cln_debug_pub.Add('M4U_PARTNER_DOC_NO - '||l_item_number,1);
243 END IF;
244
245 wf_event.AddParameterToList(
246 p_name => 'M4U_DOC_OWNER',
247 p_value => l_doc_owner,
248 p_parameterlist => l_m4u_parameters );
249
250 IF (l_Debug_Level <= 1) THEN
254 wf_event.AddParameterToList(
251 cln_debug_pub.Add('M4U_DOC_OWNER - '||l_doc_owner,1);
252 END IF;
253
255 p_name => 'M4U_OWNER_ROLE',
256 p_value => l_owner_role,
257 p_parameterlist => l_m4u_parameters );
258
259 IF (l_Debug_Level <= 1) THEN
260 cln_debug_pub.Add('M4U_OWNER_ROLE - '||l_owner_role,1);
261 END IF;
262 END IF;
263
264
265
266 -- pass parameters as event parameters,
267 -- to be used in outboud xml generation map.
268
269 wf_event.AddParameterToList(
270 p_name => 'ECX_EVENT_KEY',
271 p_value => l_xml_event_key,
272 p_parameterlist => l_m4u_parameters );
273
274 IF (l_Debug_Level <= 1) THEN
275 cln_debug_pub.Add('ECX_EVENT_KEY - '||l_xml_event_key,1);
276 END IF;
277
278 wf_event.AddParameterToList(
279 p_name => 'ECX_TRANSACTION_TYPE',
280 p_value => l_xmlg_transaction_type,
281 p_parameterlist => l_m4u_parameters );
282 IF (l_Debug_Level <= 1) THEN
283 cln_debug_pub.Add('ECX_TRANSACTION_TYPE - '||l_xmlg_transaction_type,1);
284 END IF;
285
286 wf_event.AddParameterToList(
287 p_name => 'ECX_TRANSACTION_SUBTYPE',
288 p_value => l_xmlg_transaction_subtype,
289 p_parameterlist => l_m4u_parameters );
290 IF (l_Debug_Level <= 1) THEN
291 cln_debug_pub.Add('ECX_TRANSACTION_SUBTYPE - '||l_xmlg_transaction_subtype,1);
292 END IF;
293
294 wf_event.AddParameterToList(
295 p_name => 'M4U_CLN_COLL_TYPE',
296 p_value => l_coll_type,
297 p_parameterlist => l_m4u_parameters );
298 IF (l_Debug_Level <= 1) THEN
299 cln_debug_pub.Add('M4U_CLN_COLL_TYPE - '||l_coll_type,1);
300 END IF;
301
302 wf_event.AddParameterToList(
303 p_name => 'M4U_CLN_DOC_TYPE',
304 p_value => l_doc_type,
305 p_parameterlist => l_m4u_parameters );
306 IF (l_Debug_Level <= 1) THEN
307 cln_debug_pub.Add('M4U_CLN_DOC_TYPE - '||l_doc_type,1);
308 END IF;
309
310
311 wf_event.AddParameterToList(
312 p_name => 'ECX_PARAMETER4',
313 p_value => l_ego_subbatch_id,
314 p_parameterlist => l_m4u_parameters );
315 IF (l_Debug_Level <= 1) THEN
316 cln_debug_pub.Add('ECX_PARAMETER4 - '||l_ego_subbatch_id,1);
317 END IF;
318
319 wf_event.AddParameterToList(
320 p_name => 'ECX_PARAMETER2',
321 p_value => l_ego_batch_id,
322 p_parameterlist => l_m4u_parameters );
323 IF (l_Debug_Level <= 1) THEN
324 cln_debug_pub.Add('ECX_PARAMETER2 - '||l_ego_batch_id,1);
325 END IF;
326
327 wf_event.AddParameterToList(
328 p_name => 'ECX_PARAMETER3',
329 p_value => l_event_type,
330 p_parameterlist => l_m4u_parameters );
331 IF (l_Debug_Level <= 1) THEN
332 cln_debug_pub.Add('ECX_PARAMETER3 - '||l_event_type,1);
333 END IF;
334
335
336 wf_event.AddParameterToList(
337 p_name => 'ECX_DOCUMENT_ID',
338 p_value => l_xmlg_document_id,
339 p_parameterlist => l_m4u_parameters );
340 IF (l_Debug_Level <= 1) THEN
341 cln_debug_pub.Add('ECX_DOCUMENT_ID - '||l_xmlg_document_id,1);
342 END IF;
343
344 wf_event.AddParameterToList(
345 p_name => 'MESSAGE_TEXT',
346 p_value => l_create_msg_text,
347 p_parameterlist => l_m4u_parameters );
348 IF (l_Debug_Level <= 1) THEN
349 cln_debug_pub.Add('MESSAGE_TEXT - '||l_create_msg_text,1);
350 END IF;
351
352 wf_event.AddParameterToList(
353 p_name => 'ATTRIBUTE1',
354 p_value => m4u_ucc_utils.g_host_gln,
355 p_parameterlist => l_m4u_parameters );
356 IF (l_Debug_Level <= 1) THEN
357 cln_debug_pub.Add('ATTRIBUTE1 - '||m4u_ucc_utils.g_host_gln,1);
358 END IF;
359
360 wf_event.AddParameterToList(
361 p_name => 'ATTRIBUTE2',
362 p_value => l_target_market,
363 p_parameterlist => l_m4u_parameters );
364 IF (l_Debug_Level <= 1) THEN
365 cln_debug_pub.Add('ATTRIBUTE2 - '||l_target_market,1);
366 END IF;
367
368 wf_event.AddParameterToList(
369 p_name => 'ATTRIBUTE3',
370 p_value => l_ego_batch_id,
371 p_parameterlist => l_m4u_parameters );
372 IF (l_Debug_Level <= 1) THEN
373 cln_debug_pub.Add('ATTRIBUTE3 - '||l_ego_batch_id,1);
374 END IF;
375
376 wf_event.AddParameterToList(
377 p_name => 'ATTRIBUTE4',
378 p_value => l_ego_subbatch_id,
379 p_parameterlist => l_m4u_parameters );
380 IF (l_Debug_Level <= 1) THEN
381 cln_debug_pub.Add('ATTRIBUTE4 - '||l_ego_subbatch_id,1);
382 END IF;
383
384 wf_event.AddParameterToList(
385 p_name => 'ATTRIBUTE5',
386 p_value => m4u_ucc_utils.g_supp_gln,
387 p_parameterlist => l_m4u_parameters );
388 IF (l_Debug_Level <= 1) THEN
389 cln_debug_pub.Add('ATTRIBUTE5 - '||m4u_ucc_utils.g_supp_gln,1);
390 END IF;
391
392 wf_event.AddParameterToList(
393 p_name => 'ATTRIBUTE6',
394 p_value => l_tp_gln,
395 p_parameterlist => l_m4u_parameters );
396 IF (l_Debug_Level <= 1) THEN
397 cln_debug_pub.Add('ATTRIBUTE6 - '||l_tp_gln,1);
398 END IF;
399
400 wf_event.AddParameterToList(
401 p_name => 'ATTRIBUTE12',
402 p_value => l_xmlg_document_id,
403 p_parameterlist => l_m4u_parameters );
404 IF (l_Debug_Level <= 1) THEN
405 cln_debug_pub.Add('ATTRIBUTE12 - '||l_xmlg_document_id,1);
406 END IF;
407
408 wf_event.AddParameterToList(
409 p_name => 'M4U_BATCH_MODE',
410 p_value => l_batch_mode,
411 p_parameterlist => l_m4u_parameters );
412 IF (l_Debug_Level <= 1) THEN
413 cln_debug_pub.Add('M4U_BATCH_MODE - '||l_batch_mode,1);
414 END IF;
415
416 -- set event parameters, end
417 IF (l_Debug_Level <= 1) THEN
418 cln_debug_pub.Add('-- Raising Event oracle.apps.m4u.outboundwf.generate -- ',1);
419 cln_debug_pub.Add('p_event_key - '||l_xml_event_key ,1);
420 END IF;
421
422 -- raise event to trigger outbound wlq generation WF
423 wf_event.raise(
424 p_event_name => 'oracle.apps.m4u.outboundwf.generate',
425 p_event_key => l_xml_event_key,
426 p_parameters => l_m4u_parameters
427 );
428
429 -- check the message
430 IF (l_Debug_Level <= 2) THEN
431 cln_debug_pub.Add('============EXITING EGO_EVENT_SUB============', 2);
432 END IF;
433
434 RETURN 'SUCCESS' ;
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 l_error_code := SQLCODE;
439 l_error_msg := SQLERRM;
440 IF (l_Debug_Level <= 5) THEN
441 cln_debug_pub.Add('Error:' || l_error_code || ':' || l_error_msg, 3);
442 cln_debug_pub.Add('============EXITING EGO_EVENT_SUB============', 2);
443 END IF;
444
445 RETURN 'SUCCESS';
446
447 END EGO_EVENT_SUB;
448
449 BEGIN
450 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
451
452 END m4u_egoevnt_handler;
453