[Home] [Help]
PACKAGE BODY: APPS.GMO_CBR_GRP
Source
1 PACKAGE BODY GMO_CBR_GRP AS
2 /* $Header: GMOGCBRB.pls 120.21.12020000.3 2013/02/12 06:34:56 rborpatl ship $ */
3
4 PROCEDURE ENABLE_CBR (ERRBUF OUT NOCOPY VARCHAR2,RETCODE OUT NOCOPY VARCHAR2) IS
5 -- Local Variables
6 l_event_name VARCHAR2(80);
7 l_err BOOLEAN;
8 datetime_format CONSTANT VARCHAR2(32) := 'DD-MM-YYYY HH24:MI:SS';
9 --
10 -- ERES events where GMO CBR subscription exists
11 --
12
13 CURSOR CBR_EVENTS IS
14 SELECT a.GUID
15 ,a.NAME
16 ,a.TYPE
17 ,a.STATUS
18 ,a.GENERATE_FUNCTION
19 ,a.JAVA_GENERATE_FUNC
20 ,a.OWNER_NAME
21 ,a.OWNER_TAG
22 ,a.CUSTOMIZATION_LEVEL
23 ,a.DISPLAY_NAME
24 ,a.DESCRIPTION
25 FROM
26 wf_events_vl a,
27 wf_event_subscriptions b
28 WHERE a.GUID = b.EVENT_FILTER_GUID
29 and b.RULE_FUNCTION in ('GMO_CBR_GRP.PROCESS_EVENT','GMO_CBR_GRP.PROCESS_INSTANCE_INSTR_SET');
30
31 --
32 -- ERES event subscriptions owned by product teams where GMO subscription exists
33 --
34
35 CURSOR CBR_EVENT_SUBCRIPTIONS IS
36 SELECT b.GUID
37 ,b.SYSTEM_GUID
38 ,b.SOURCE_TYPE
39 ,b.SOURCE_AGENT_GUID
40 ,b.EVENT_FILTER_GUID
41 ,b.PHASE
42 ,b.STATUS
43 ,b.RULE_DATA
44 ,b.OUT_AGENT_GUID
45 ,b.TO_AGENT_GUID
46 ,b.PRIORITY
47 ,b.RULE_FUNCTION
48 ,b.WF_PROCESS_TYPE
49 ,b.WF_PROCESS_NAME
50 ,b.PARAMETERS
51 ,b.OWNER_NAME
52 ,b.OWNER_TAG
53 ,b.DESCRIPTION
54 ,b.EXPRESSION
55 ,b.SECURITY_GROUP_ID
56 ,b.CUSTOMIZATION_LEVEL
57 ,b.LICENSED_FLAG
58 ,b.INVOCATION_ID
59 ,b.MAP_CODE
60 ,b.STANDARD_TYPE
61 ,b.STANDARD_CODE
62 ,b.JAVA_RULE_FUNC
63 ,b.ON_ERROR_CODE
64 ,b.ACTION_CODE
65 FROM
66 wf_events_vl a,
67 wf_event_subscriptions b
68 WHERE a.GUID = b.EVENT_FILTER_GUID
69 AND b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
70 AND a.name = l_event_name
71 and b.owner_tag <> 'GMO'
72 and b.status = 'ENABLED';
73
74 --
75 -- ERES event subscriptions owned by GMO and they are in disabled state
76 --
77
78 CURSOR CBR_GMO_SUBCRIPTIONS IS
79 SELECT b.GUID
80 ,b.SYSTEM_GUID
81 ,b.SOURCE_TYPE
82 ,b.SOURCE_AGENT_GUID
83 ,b.EVENT_FILTER_GUID
84 ,b.PHASE
85 ,b.STATUS
86 ,b.RULE_DATA
87 ,b.OUT_AGENT_GUID
88 ,b.TO_AGENT_GUID
89 ,b.PRIORITY
90 ,b.RULE_FUNCTION
91 ,b.WF_PROCESS_TYPE
92 ,b.WF_PROCESS_NAME
93 ,b.PARAMETERS
94 ,b.OWNER_NAME
95 ,b.OWNER_TAG
96 ,b.DESCRIPTION
97 ,b.EXPRESSION
98 ,b.SECURITY_GROUP_ID
99 ,b.CUSTOMIZATION_LEVEL
100 ,b.LICENSED_FLAG
101 ,b.INVOCATION_ID
102 ,b.MAP_CODE
103 ,b.STANDARD_TYPE
104 ,b.STANDARD_CODE
105 ,b.JAVA_RULE_FUNC
106 ,b.ON_ERROR_CODE
107 ,b.ACTION_CODE
108 FROM
109 wf_events_vl a,
110 wf_event_subscriptions b
111 WHERE a.GUID = b.EVENT_FILTER_GUID
112 AND a.name = l_event_name
113 AND b.owner_tag = 'GMO'
114 AND b.status = 'DISABLED' ;
115
116 -- Cursor ROW TYPE Varaibles
117 l_EVENT_REC CBR_EVENTS%ROWTYPE;
118 l_CBR_GMO_SUBCRIPTIONS_REC CBR_GMO_SUBCRIPTIONS%ROWTYPE;
119 l_CBR_EVENT_SUBCRIPTIONS_REC CBR_EVENT_SUBCRIPTIONS%ROWTYPE;
120 BEGIN
121
122 if (fnd_profile.defined('GMO_ENABLED_FLAG')) THEN
123 if GMO_SETUP_GRP.IS_GMO_ENABLED = GMO_CONSTANTS_GRP.YES THEN
124 fnd_file.put_line(fnd_file.output, fnd_message.get_string('GMO', 'GMO_IS_ENABLED_START_PROCESS') );
125 fnd_file.new_line(fnd_file.output, 1);
126
127 OPEN CBR_EVENTS;
128 LOOP
129 FETCH CBR_EVENTS INTO l_EVENT_REC;
130 EXIT WHEN CBR_EVENTS%NOTFOUND;
131 l_event_name := l_EVENT_REC.name;
132 IF l_EVENT_REC.STATUS = 'DISABLED' THEN
133 fnd_message.set_name('GMO', 'GMO_CBR_EVENT_PROCESSING');
134 fnd_message.set_token('EVENT', l_EVENT_REC.DISPLAY_NAME);
135 fnd_file.put_line(fnd_file.output, fnd_message.get);
136 fnd_file.new_line(fnd_file.output, 2);
137
138 --
139 -- Enable ERES Event for Control Batch Recording
140 --
141 WF_EVENTS_PKG.UPDATE_ROW(X_GUID => l_EVENT_REC.GUID
142 ,X_NAME => l_EVENT_REC.NAME
143 ,X_TYPE => l_EVENT_REC.TYPE
144 ,X_STATUS => 'ENABLED'
145 ,X_GENERATE_FUNCTION => l_EVENT_REC.GENERATE_FUNCTION
146 ,X_OWNER_NAME => l_EVENT_REC.OWNER_NAME
147 ,X_OWNER_TAG => l_EVENT_REC.OWNER_TAG
148 ,X_DISPLAY_NAME => l_EVENT_REC.DISPLAY_NAME
149 ,X_DESCRIPTION => l_EVENT_REC.DESCRIPTION
150 ,X_CUSTOMIZATION_LEVEL => l_EVENT_REC.CUSTOMIZATION_LEVEL
151 ,X_LICENSED_FLAG => l_EVENT_REC.CUSTOMIZATION_LEVEL
152 ,X_JAVA_GENERATE_FUNC => l_EVENT_REC.JAVA_GENERATE_FUNC);
153 END IF;
154 OPEN CBR_EVENT_SUBCRIPTIONS;
155 LOOP
156 FETCH CBR_EVENT_SUBCRIPTIONS INTO l_CBR_EVENT_SUBCRIPTIONS_REC;
157 EXIT WHEN CBR_EVENT_SUBCRIPTIONS%NOTFOUND;
158 --
159 -- disable ERES Event subscriptions owned by product teams
160 --
161 fnd_message.set_name('GMO', 'GMO_CBR_EVT_PROD_SUB_PROCESS');
162 fnd_message.set_token('EVENT', l_EVENT_REC.DISPLAY_NAME);
163 fnd_file.put_line(fnd_file.output, fnd_message.get);
164 fnd_file.new_line(fnd_file.output, 2);
165
166 WF_EVENT_SUBSCRIPTIONS_PKG.UPDATE_ROW(X_GUID => l_CBR_EVENT_SUBCRIPTIONS_REC.GUID
167 ,X_SYSTEM_GUID => l_CBR_EVENT_SUBCRIPTIONS_REC.SYSTEM_GUID
168 ,X_SOURCE_TYPE => l_CBR_EVENT_SUBCRIPTIONS_REC.SOURCE_TYPE
169 ,X_SOURCE_AGENT_GUID => l_CBR_EVENT_SUBCRIPTIONS_REC.SOURCE_AGENT_GUID
170 ,X_EVENT_FILTER_GUID => l_CBR_EVENT_SUBCRIPTIONS_REC.EVENT_FILTER_GUID
171 ,X_PHASE => l_CBR_EVENT_SUBCRIPTIONS_REC.PHASE
172 ,X_STATUS => 'DISABLED'
173 ,X_RULE_DATA => l_CBR_EVENT_SUBCRIPTIONS_REC.RULE_DATA
174 ,X_OUT_AGENT_GUID => l_CBR_EVENT_SUBCRIPTIONS_REC.OUT_AGENT_GUID
175 ,X_TO_AGENT_GUID => l_CBR_EVENT_SUBCRIPTIONS_REC.TO_AGENT_GUID
176 ,X_PRIORITY => l_CBR_EVENT_SUBCRIPTIONS_REC.PRIORITY
177 ,X_RULE_FUNCTION => l_CBR_EVENT_SUBCRIPTIONS_REC.RULE_FUNCTION
178 ,X_WF_PROCESS_TYPE => l_CBR_EVENT_SUBCRIPTIONS_REC.WF_PROCESS_TYPE
179 ,X_WF_PROCESS_NAME => l_CBR_EVENT_SUBCRIPTIONS_REC.WF_PROCESS_NAME
180 ,X_PARAMETERS => l_CBR_EVENT_SUBCRIPTIONS_REC.PARAMETERS
181 ,X_OWNER_NAME => l_CBR_EVENT_SUBCRIPTIONS_REC.OWNER_NAME
182 ,X_OWNER_TAG => l_CBR_EVENT_SUBCRIPTIONS_REC.OWNER_TAG
183 ,X_CUSTOMIZATION_LEVEL => l_CBR_EVENT_SUBCRIPTIONS_REC.CUSTOMIZATION_LEVEL
184 ,X_LICENSED_FLAG => l_CBR_EVENT_SUBCRIPTIONS_REC.LICENSED_FLAG
185 ,X_DESCRIPTION => l_CBR_EVENT_SUBCRIPTIONS_REC.DESCRIPTION
186 ,X_EXPRESSION => l_CBR_EVENT_SUBCRIPTIONS_REC.EXPRESSION
187 ,X_ACTION_CODE => l_CBR_EVENT_SUBCRIPTIONS_REC.ACTION_CODE
188 ,X_ON_ERROR_CODE => l_CBR_EVENT_SUBCRIPTIONS_REC.ON_ERROR_CODE
189 ,X_JAVA_RULE_FUNC => l_CBR_EVENT_SUBCRIPTIONS_REC.JAVA_RULE_FUNC
190 ,X_MAP_CODE => l_CBR_EVENT_SUBCRIPTIONS_REC.MAP_CODE
191 ,X_STANDARD_CODE => l_CBR_EVENT_SUBCRIPTIONS_REC.STANDARD_CODE
192 ,X_STANDARD_TYPE => l_CBR_EVENT_SUBCRIPTIONS_REC.STANDARD_TYPE);
193 END LOOP;
194 CLOSE CBR_EVENT_SUBCRIPTIONS;
195 OPEN CBR_GMO_SUBCRIPTIONS;
196 LOOP
197 FETCH CBR_GMO_SUBCRIPTIONS INTO l_CBR_GMO_SUBCRIPTIONS_REC;
198 EXIT WHEN CBR_GMO_SUBCRIPTIONS%NOTFOUND;
199 fnd_message.set_name('GMO', 'GMO_CBR_EVT_GMO_SUB_PROCESS');
200 fnd_message.set_token('EVENT', l_EVENT_REC.DISPLAY_NAME);
201 fnd_file.put_line(fnd_file.output, fnd_message.get);
202 fnd_file.new_line(fnd_file.output, 2);
203
204 --
205 -- enable ERES Event subscriptions owned by GMO
206 --
207 WF_EVENT_SUBSCRIPTIONS_PKG.UPDATE_ROW(X_GUID => l_CBR_GMO_SUBCRIPTIONS_REC.GUID
208 ,X_SYSTEM_GUID => l_CBR_GMO_SUBCRIPTIONS_REC.SYSTEM_GUID
209 ,X_SOURCE_TYPE => l_CBR_GMO_SUBCRIPTIONS_REC.SOURCE_TYPE
210 ,X_SOURCE_AGENT_GUID => l_CBR_GMO_SUBCRIPTIONS_REC.SOURCE_AGENT_GUID
211 ,X_EVENT_FILTER_GUID => l_CBR_GMO_SUBCRIPTIONS_REC.EVENT_FILTER_GUID
212 ,X_PHASE => l_CBR_GMO_SUBCRIPTIONS_REC.PHASE
213 ,X_STATUS => 'ENABLED'
214 ,X_RULE_DATA => l_CBR_GMO_SUBCRIPTIONS_REC.RULE_DATA
215 ,X_OUT_AGENT_GUID => l_CBR_GMO_SUBCRIPTIONS_REC.OUT_AGENT_GUID
216 ,X_TO_AGENT_GUID => l_CBR_GMO_SUBCRIPTIONS_REC.TO_AGENT_GUID
217 ,X_PRIORITY => l_CBR_GMO_SUBCRIPTIONS_REC.PRIORITY
218 ,X_RULE_FUNCTION => l_CBR_GMO_SUBCRIPTIONS_REC.RULE_FUNCTION
219 ,X_WF_PROCESS_TYPE => l_CBR_GMO_SUBCRIPTIONS_REC.WF_PROCESS_TYPE
220 ,X_WF_PROCESS_NAME => l_CBR_GMO_SUBCRIPTIONS_REC.WF_PROCESS_NAME
221 ,X_PARAMETERS => l_CBR_GMO_SUBCRIPTIONS_REC.PARAMETERS
222 ,X_OWNER_NAME => l_CBR_GMO_SUBCRIPTIONS_REC.OWNER_NAME
223 ,X_OWNER_TAG => l_CBR_GMO_SUBCRIPTIONS_REC.OWNER_TAG
224 ,X_CUSTOMIZATION_LEVEL => l_CBR_GMO_SUBCRIPTIONS_REC.CUSTOMIZATION_LEVEL
225 ,X_LICENSED_FLAG => l_CBR_GMO_SUBCRIPTIONS_REC.LICENSED_FLAG
226 ,X_DESCRIPTION => l_CBR_GMO_SUBCRIPTIONS_REC.DESCRIPTION
227 ,X_EXPRESSION => l_CBR_GMO_SUBCRIPTIONS_REC.EXPRESSION
228 ,X_ACTION_CODE => l_CBR_GMO_SUBCRIPTIONS_REC.ACTION_CODE
229 ,X_ON_ERROR_CODE => l_CBR_GMO_SUBCRIPTIONS_REC.ON_ERROR_CODE
230 ,X_JAVA_RULE_FUNC => l_CBR_GMO_SUBCRIPTIONS_REC.JAVA_RULE_FUNC
231 ,X_MAP_CODE => l_CBR_GMO_SUBCRIPTIONS_REC.MAP_CODE
232 ,X_STANDARD_CODE => l_CBR_GMO_SUBCRIPTIONS_REC.STANDARD_CODE
233 ,X_STANDARD_TYPE => l_CBR_GMO_SUBCRIPTIONS_REC.STANDARD_TYPE);
234 END LOOP;
235 CLOSE CBR_GMO_SUBCRIPTIONS;
236 END LOOP;
237 CLOSE CBR_EVENTS;
238 IF TO_DATE(fnd_profile.value('GMO_CBR_ENABLED_DATE'), datetime_format) is null
239 THEN
240 l_err := fnd_profile.save('GMO_CBR_ENABLED_DATE',TO_CHAR(sysdate, datetime_format), 'SITE');
241 fnd_message.set_name('GMO', 'GMO_EBR_ENABLED_DATE');
242 fnd_message.set_token('ENABLE_DATE',TO_DATE(fnd_profile.value('GMO_CBR_ENABLED_DATE'), datetime_format) );
243 fnd_file.put_line(fnd_file.output, fnd_message.get);
244 fnd_file.new_line(fnd_file.output, 2);
245 END IF;
246 COMMIT;
247 else
248 fnd_file.put_line(fnd_file.output, fnd_message.get_string('GMO', 'GMO_IS_NOT_ENABLED') );
249 fnd_file.new_line(fnd_file.output, 1);
250 end if;
251 else
252 fnd_file.put_line(fnd_file.output, fnd_message.get_string('GMO', 'GMO_PROFILE_NOTFOUND') );
253 fnd_file.new_line(fnd_file.output, 1);
254 APP_EXCEPTION.Raise_exception;
255 end if;
256 EXCEPTION WHEN OTHERS THEN
257 ERRBUF := SQLERRM;
258 RETCODE := 2;
259 END;
260
261
262 PROCEDURE UPDATE_EVENT (P_ERECORD_ID NUMBER, P_BATCH_PROGRESSION_ID NUMBER, P_EVENT_DATE DATE, P_STATUS VARCHAR2) IS
263 BEGIN
264 --
265 -- Update event details based on parameters
266 --
267 IF P_ERECORD_ID IS NOT NULL
268 THEN
269 UPDATE gmo_batch_progression
270 SET STATUS = P_STATUS,
271 EVENT_DATE = p_EVENT_DATE
272 WHERE ERECORD_ID = p_ERECORD_ID;
273 END IF;
274 IF P_BATCH_PROGRESSION_ID IS NOT NULL
275 THEN
276 UPDATE gmo_batch_progression
277 SET STATUS = P_STATUS,
278 EVENT_DATE = p_EVENT_DATE
279 WHERE PROGRESSION_ID = P_BATCH_PROGRESSION_ID;
280 END IF;
281 END UPDATE_EVENT ;
282
283 PROCEDURE INSERT_EVENT(P_BATCH_PROG_REC GMO_BATCH_PROGRESSION%ROWTYPE,P_BATCH_PROGRESSION_ID OUT NOCOPY NUMBER) IS
284 CURSOR get_progression_ID is
285 SELECT GMO_BATCH_PROGRESSION_S.nextval
286 FROM DUAL;
287 BEGIN
288 OPEN get_progression_ID;
289 FETCH get_progression_ID INTO P_BATCH_PROGRESSION_ID;
290 CLOSE get_progression_ID;
291
292 --
293 -- insert into Batch Progression Table
294 --
295 INSERT INTO GMO_BATCH_PROGRESSION (
296 PROGRESSION_ID
297 ,BATCH_ID
298 ,BATCHSTEP_ID
299 ,MATERIAL_LINE_ID
300 ,BATCHACTIVITY_ID
301 ,BATCHRSRC_ID
302 ,DISPENSE_ID
303 ,SAMPLE_ID
304 ,DEVIATION_ID
305 ,TRANSACTION_ID
306 ,PARENT_NODE
307 ,PARENT_KEY_NODE
308 ,PARENT_KEY_VAL
309 ,CURR_NODE_KEY
310 ,CURR_KEY_VAL
311 ,USER_KEY_LABEL_PROD
312 ,USER_KEY_LABEL_TOKEN
313 ,USER_KEY_LABEL
314 ,USER_KEY_VALUE
315 ,EXTERNAL_EVENT
316 ,INCLUDE_IN_CBR
317 ,EVENT
318 ,EVENT_KEY
319 ,EVENT_DATE
320 ,PLANED_START_DATE
321 ,ERECORD_ID
322 ,XML_EREC
323 ,STATUS
324 ,INCLUDED_IN_CBR
325 ,XML_MAP_CODE
326 ,compare_xml)
327 VALUES
328 (P_BATCH_PROGRESSION_ID
329 ,P_BATCH_PROG_REC.BATCH_ID
330 ,P_BATCH_PROG_REC.BATCHSTEP_ID
331 ,P_BATCH_PROG_REC.MATERIAL_LINE_ID
332 ,P_BATCH_PROG_REC.BATCHACTIVITY_ID
333 ,P_BATCH_PROG_REC.BATCHRSRC_ID
334 ,P_BATCH_PROG_REC.DISPENSE_ID
335 ,P_BATCH_PROG_REC.SAMPLE_ID
336 ,P_BATCH_PROG_REC.DEVIATION_ID
337 ,P_BATCH_PROG_REC.TRANSACTION_ID
338 ,P_BATCH_PROG_REC.PARENT_NODE
339 ,P_BATCH_PROG_REC.PARENT_KEY_NODE
340 ,P_BATCH_PROG_REC.PARENT_KEY_VAL
341 ,P_BATCH_PROG_REC.CURR_NODE_KEY
342 ,P_BATCH_PROG_REC.CURR_KEY_VAL
343 ,P_BATCH_PROG_REC.USER_KEY_LABEL_PROD
344 ,P_BATCH_PROG_REC.USER_KEY_LABEL_TOKEN
345 ,P_BATCH_PROG_REC.USER_KEY_LABEL
346 ,P_BATCH_PROG_REC.USER_KEY_VALUE
347 ,P_BATCH_PROG_REC.EXTERNAL_EVENT
348 ,P_BATCH_PROG_REC.INCLUDE_IN_CBR
349 ,P_BATCH_PROG_REC.EVENT
350 ,P_BATCH_PROG_REC.EVENT_KEY
351 ,P_BATCH_PROG_REC.EVENT_DATE
352 ,P_BATCH_PROG_REC.PLANED_START_DATE
353 ,P_BATCH_PROG_REC.ERECORD_ID
354 ,P_BATCH_PROG_REC.XML_EREC
355 ,P_BATCH_PROG_REC.STATUS
356 ,P_BATCH_PROG_REC.INCLUDED_IN_CBR
357 ,P_BATCH_PROG_REC.XML_MAP_CODE
358 ,P_BATCH_PROG_REC.compare_xml );
359
360
361 END;
362
363 PROCEDURE CBR_PREPROCESS (P_BATCH_ID IN NUMBER) IS
364 -- local variables
365 l_document EDR_PSIG.DOCUMENT;
366 l_docparams EDR_PSIG.PARAMS_TABLE;
367 l_signatures EDR_PSIG.SIGNATURETABLE;
368 l_error NUMBER;
369 l_error_msg VARCHAR2(4000);
370 l_event_name varchar2(80) := NULL;
371 l_event_key varchar2(80) := NULL;
372 l_batch_id number := null;
373 l_batchstep_id number := null;
374 l_material_detail_id number := null;
375 l_dispense_id NUMBER := NULL;
376 l_parent_node VARCHAR2(80) := NULL;
377 l_parent_key_node VARCHAR2(80) := NULL;
378 l_parent_key_val VARCHAR2(80) := NULL;
379 l_curr_node_key VARCHAR2(80) := NULL;
380 l_curr_key_val VARCHAR2(80) := NULL;
381 l_entity_name VARCHAR2(40) := NULL;
382 l_progression_id NUMBER;
383 l_PSIG_EREC CLOB;
384 l_PROG_EREC CLOB;
385 l_QA_XML VARCHAR2(4000);
386 -- cursor declarations
387 -- This cursor is used to modify Progression row status for a given batch
388 -- from inprogress to complete status based on e-record status
389
390 CURSOR GMO_BATCH_PROGRESSION_CUR IS
391 SELECT PROGRESSION_ID,ERECORD_ID,BATCH_ID ,BATCHSTEP_ID,
392 MATERIAL_LINE_ID,BATCHACTIVITY_ID,BATCHRSRC_ID
393 FROM GMO_BATCH_PROGRESSION
394 WHERE BATCH_ID = P_BATCH_ID
395 AND STATUS = 'INPROGRESS'
396 AND ERECORD_ID IS NOT NULL;
397
398 -- This cursor is used to populate missing information for batch progression table
399 -- this can occur for SSWA based events.
400
401 CURSOR GMO_BATCH_PROGRESSION_CUR2 IS
402 SELECT PROGRESSION_ID,EVENT,EVENT_KEY,dispense_id
403 FROM GMO_BATCH_PROGRESSION
404 WHERE BATCH_ID IS NULL
405 AND STATUS = 'INPROGRESS'
406 AND ERECORD_ID IS NOT NULL
407 AND event in ('oracle.apps.gmo.mtl.dispense',
408 'oracle.apps.gmo.mtl.revdisp',
409 'oracle.apps.gmo.labelprint');
410
411 CURSOR get_dispense_Details IS
412 SELECT batch_id, batch_step_id ,material_detail_id
413 FROM gmo_material_dispenses
414 WHERE dispense_id = l_dispense_id;
415 CURSOR get_rev_dispense_Details IS
416 SELECT batch_id, batch_step_id ,material_detail_id,dispense_id
417 FROM gmo_material_undispenses
418 WHERE undispense_id = l_event_key;
419 CURSOR GET_LABEL_DETAILS IS
420 SELECT entity_name, entity_key
421 from gmo_label_history
422 where label_id = l_event_key;
423 BEGIN
424 -- Following code is to populate missing data values in batch progression for SSWA events
425
426 OPEN GMO_BATCH_PROGRESSION_CUR2;
427 LOOP
428 FETCH GMO_BATCH_PROGRESSION_CUR2 INTO l_progression_id,l_event_name,l_event_key,l_dispense_id;
429 EXIT WHEN GMO_BATCH_PROGRESSION_CUR2%NOTFOUND;
430 IF l_event_name in ('oracle.apps.gmo.mtl.dispense','oracle.apps.gmo.labelprint') THEN
431 IF l_event_name in ('oracle.apps.gmo.mtl.dispense') THEN
432 l_dispense_id := l_event_key ;
433 END IF;
434 OPEN get_dispense_Details;
435 FETCH get_dispense_Details into l_batch_id, l_batchstep_id ,l_material_detail_id;
436 CLOSE get_dispense_Details;
437 UPDATE GMO_BATCH_PROGRESSION
438 SET batch_id = l_batch_id,
439 batchstep_id = l_batchstep_id,
440 material_line_id = l_material_detail_id
441 WHERE PROGRESSION_ID = l_progression_id;
442 ELSIF l_event_name in ('oracle.apps.gmo.mtl.revdisp') THEN
443 OPEN get_rev_dispense_Details;
444 FETCH get_rev_dispense_Details into l_batch_id, l_batchstep_id ,l_material_detail_id,l_dispense_id;
445 CLOSE get_rev_dispense_Details;
446 UPDATE GMO_BATCH_PROGRESSION
447 SET batch_id = l_batch_id,
448 batchstep_id = l_batchstep_id,
449 material_line_id = l_material_detail_id,
450 dispense_id = l_dispense_id
451 WHERE PROGRESSION_ID = l_progression_id;
452 END IF;
453 END LOOP;
454
455 --
456 -- Based on batch ID process inprogress batch progression table rows
457 -- to mark as completed. This is required as when event is raised
458 -- rows will be in 'INPROGRESS' state and we do not have any hook from where
459 -- we can mark row as completed once ERES is success.
460 --
461 FOR GMO_BATCH_PROGRESSION_REC IN GMO_BATCH_PROGRESSION_CUR
462 LOOP
463 edr_psig.getdocumentdetails(GMO_BATCH_PROGRESSION_REC.ERECORD_ID,l_document,l_docparams,l_signatures,l_error,l_error_msg);
464 IF l_document.PSIG_STATUS = 'ERROR' THEN
465 DELETE FROM GMO_BATCH_PROGRESSION WHERE PROGRESSION_ID = GMO_BATCH_PROGRESSION_REC.PROGRESSION_ID;
466 ELSIF l_document.PSIG_STATUS in ('COMPLETE','REJECTED','TIMEDOUT') then
467 UPDATE GMO_BATCH_PROGRESSION
468 SET STATUS = 'COMPLETE'
469 WHERE PROGRESSION_ID = GMO_BATCH_PROGRESSION_REC.PROGRESSION_ID;
470 IF l_document.EVENT_NAME in ('oracle.apps.qa.disp.create'
471 ,'oracle.apps.qa.disp.detail.approve'
472 ,'oracle.apps.qa.disp.header.approve'
473 ,'oracle.apps.qa.disp.update'
474 ,'oracle.apps.qa.ncm.create'
475 ,'oracle.apps.qa.ncm.detail.approve'
476 ,'oracle.apps.qa.ncm.master.approve'
477 ,'oracle.apps.qa.ncm.update')
478 THEN
479 DBMS_LOB.CREATETEMPORARY(l_PROG_EREC,TRUE,DBMS_LOB.SESSION);
480 l_QA_XML :='<QA_NCM_EREC>
481 <ERECORD_ID>'||GMO_BATCH_PROGRESSION_REC.ERECORD_ID ||'
482 </ERECORD_ID>
483 <ERECORD>';
484 DBMS_LOB.append(l_PROG_EREC,l_QA_XML);
485 DBMS_LOB.append(l_PROG_EREC,l_document.PSIG_DOCUMENT);
486 DBMS_LOB.append(l_PROG_EREC,'
487 </ERECORD>
488 </QA_NCM_EREC>');
489
490 -- add code for constructing XML for E-Record Object.
491 -- IF l_document.EVENT_NAME = 'oracle.apps.qa.ncm.create' THEN
492 IF GMO_BATCH_PROGRESSION_REC.BATCHRSRC_ID is not null
493 THEN
494 l_parent_node := 'RESOURCE_REQUIREMENTS';
495 l_parent_key_node := 'STEP_RESOURCE_ID';
496 l_parent_key_val := GMO_BATCH_PROGRESSION_REC.BATCHRSRC_ID;
497 l_curr_node_key := 'ERECORD_ID';
498 l_curr_key_val := GMO_BATCH_PROGRESSION_REC.ERECORD_ID;
499 ELSIF GMO_BATCH_PROGRESSION_REC.BATCHACTIVITY_ID is not null
500 THEN
501 l_parent_node := 'ACTIVITIES';
502 l_parent_key_node := 'STEP_ACTIVITY_ID';
503 l_parent_key_val := GMO_BATCH_PROGRESSION_REC.BATCHACTIVITY_ID;
504 l_curr_node_key := 'ERECORD_ID';
505 l_curr_key_val := GMO_BATCH_PROGRESSION_REC.ERECORD_ID;
506 ELSIF GMO_BATCH_PROGRESSION_REC.MATERIAL_LINE_ID is not null
507 THEN
508 l_parent_node := 'MATERIAL_REQUIREMENTS';
509 l_parent_key_node := 'MATERIAL_DETAIL_ID';
510 l_parent_key_val := GMO_BATCH_PROGRESSION_REC.MATERIAL_LINE_ID;
511 l_curr_node_key := 'ERECORD_ID';
512 l_curr_key_val := GMO_BATCH_PROGRESSION_REC.ERECORD_ID;
513
514 ELSIF GMO_BATCH_PROGRESSION_REC.BATCHSTEP_ID is not null
515 THEN
516 l_parent_node := 'ROUTING_STEPS';
517 l_parent_key_node := 'STEP_ID';
518 l_parent_key_val := GMO_BATCH_PROGRESSION_REC.BATCHSTEP_ID;
519 l_curr_node_key := 'ERECORD_ID';
520 l_curr_key_val := GMO_BATCH_PROGRESSION_REC.ERECORD_ID;
521
522 ELSIF GMO_BATCH_PROGRESSION_REC.BATCH_ID is not null
523 THEN
524 l_parent_node := 'HEADER_INFORMATION';
525 l_parent_key_node := 'BATCH_ID';
526 l_parent_key_val := GMO_BATCH_PROGRESSION_REC.BATCH_ID;
527 l_curr_node_key := 'ERECORD_ID';
528 l_curr_key_val := GMO_BATCH_PROGRESSION_REC.ERECORD_ID;
529 END IF;
530 -- END IF;
531 UPDATE GMO_BATCH_PROGRESSION
532 SET XML_EREC = l_PROG_EREC,
533 PARENT_NODE = l_parent_node,
534 PARENT_KEY_NODE = l_parent_key_node,
535 PARENT_KEY_VAL = l_parent_key_val,
536 CURR_NODE_KEY = l_curr_node_key,
537 CURR_KEY_VAL = l_curr_key_val
538 WHERE PROGRESSION_ID = GMO_BATCH_PROGRESSION_REC.PROGRESSION_ID;
539 l_parent_node := null;
540 l_parent_key_node := null;
541 l_parent_key_val := null;
542 l_curr_node_key := null;
543 l_curr_key_val := null;
544
545 END IF;
546
547 END IF;
548 END LOOP;
549 COMMIT;
550 END CBR_PREPROCESS;
551
552 /* Bug : 5040377
553 * update the progression status based on Batch status
554 */
555
556 PROCEDURE UPDATE_PROGRESSION_STATUS(P_BATCH_ID IN NUMBER ,P_BATCHSTEP_ID IN NUMBER,P_EVENT_NAME IN VARCHAR2) IS
557 BEGIN
558 IF P_EVENT_NAME='oracle.apps.gme.batch.complete' THEN
559 UPDATE GMO_BATCH_PROGRESSION
560 SET STATUS='BYPASSED'
561 WHERE BATCH_ID = P_BATCH_ID
562 AND STATUS='PENDING'
563 AND EVENT IN ('oracle.apps.gme.batch.release'
564 ,'oracle.apps.gme.batchstep.release'
565 ,'oracle.apps.gme.batchstep.complete');
566 ELSIF P_EVENT_NAME='oracle.apps.gme.batch.cancel' THEN
567 -- Bug 5499897 : rvsingh : start
568 UPDATE GMO_BATCH_PROGRESSION
569 SET STATUS='CANCEL'
570 WHERE BATCH_ID = P_BATCH_ID
571 AND STATUS IN ('PENDING','INPROGRESS');
572 -- Bug 5499897 : rvsingh : End
573 ELSIF P_EVENT_NAME='oracle.apps.gme.batch.reroute' THEN
574 UPDATE GMO_BATCH_PROGRESSION
575 SET STATUS='CANCEL'
576 WHERE BATCH_ID = P_BATCH_ID
577 AND STATUS='PENDING'
578 AND EVENT IN ('oracle.apps.gme.batchstep.complete'
579 ,'oracle.apps.gme.batchstep.release'
580 ,'oracle.apps.gme.batchstep.close');
581 -- Bug 5499897 : rvsingh : start
582 UPDATE GMO_BATCH_PROGRESSION
583 SET STATUS='CANCEL'
584 WHERE BATCH_ID = P_BATCH_ID
585 AND STATUS='INPROGRESS'
586 AND EVENT = 'oracle.apps.gmo.instrset.update';
587 -- Bug 5499897 : rvsingh : End
588 ELSIF P_EVENT_NAME='oracle.apps.gme.batch.terminate' THEN
589 -- Bug 5499897 : rvsingh : start
590 UPDATE GMO_BATCH_PROGRESSION
591 SET STATUS='TERMINATE'
592 WHERE BATCH_ID = P_BATCH_ID
593 AND STATUS IN ('PENDING','INPROGRESS');
594 -- Bug 5499897 : rvsingh : End
595 ELSIF P_EVENT_NAME='oracle.apps.gme.batch.close' THEN
596 UPDATE GMO_BATCH_PROGRESSION
597 SET STATUS='BYPASSED'
598 WHERE BATCH_ID = P_BATCH_ID
599 AND STATUS='PENDING'
600 AND EVENT ='oracle.apps.gme.batchstep.close';
601 -- Bug 5499897 : rvsingh : start
602 UPDATE GMO_BATCH_PROGRESSION
603 SET STATUS='OPTIONAL_ACK'
604 WHERE BATCH_ID = P_BATCH_ID
605 AND STATUS='INPROGRESS'
606 AND EVENT = 'oracle.apps.gmo.instrset.update';
607 -- Bug 5499897 : rvsingh : End
608 ELSIF P_EVENT_NAME='oracle.apps.gme.batchstep.complete' THEN
609 UPDATE GMO_BATCH_PROGRESSION
610 SET STATUS='BYPASSED'
611 WHERE BATCH_ID = P_BATCH_ID
612 AND BATCHSTEP_ID = P_BATCHSTEP_ID
613 AND STATUS='PENDING'
614 AND EVENT ='oracle.apps.gme.batchstep.release';
615 ELSIF P_EVENT_NAME='oracle.apps.gme.batchstep.close' THEN
616 -- Bug 5499897 : rvsingh : start
617 UPDATE GMO_BATCH_PROGRESSION
618 SET STATUS='OPTIONAL_ACK'
619 WHERE BATCH_ID = P_BATCH_ID
620 AND BATCHSTEP_ID = P_BATCHSTEP_ID
621 AND STATUS='INPROGRESS'
622 AND EVENT = 'oracle.apps.gmo.instrset.update';
623 -- Bug 5499897 : rvsingh : End
624 END IF;
625 END UPDATE_PROGRESSION_STATUS;
626
627
628
629
630 FUNCTION PROCESS_EVENT (P_SUBSCRIPTION_GUID IN RAW, P_EVENT IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2 IS
631 l_batch_prog_rec GMO_BATCH_PROGRESSION%ROWTYPE;
632 l_batch_progression_id NUMBER := NULL;
633 l_event_name varchar2(2000) := NULL;
634 l_event_key varchar2(2000) := NULL;
635 l_user_key_label varchar2(2000) := NULL;
636 l_user_key_value varchar2(2000) := NULL;
637 l_erecord_id number := NULL;
638 l_batch_id number := null;
639 l_operation_id number := null;
640 l_batchstep_id number := null;
641 l_material_detail_id number := null;
642 l_batch_activity_id number := null;
643 l_batch_rsrc_id number := null;
644 l_parameter_id number := NULL;
645 l_plan_start_date date := null;
646 l_return_status varchar2(80) := NULL;
647 l_map_code varchar2(80) := NULL;
648 l_event_cnt number := -1;
649 l_message_prod VARCHAR2(10) := NULL;
650 l_message_token VARCHAR2(50) := NULL;
651 l_collection_id number := NULL;
652 l_plan_id number := NULL;
653 l_occurrence number := NULL;
654 l_dispense_id NUMBER := NULL;
655 l_sample_id NUMBER := NULL;
656 l_deviation_id NUMBER := NULL;
657 l_transaction_id NUMBER := NULL;
658 l_parent_node VARCHAR2(200) := NULL;
659 l_parent_key_node VARCHAR2(200) := NULL;
660 l_parent_key_val VARCHAR2(200) := NULL;
661 l_curr_node_key VARCHAR2(200) := NULL;
662 l_curr_key_val VARCHAR2(200) := NULL;
663 l_xml_erec CLOB := NULL;
664 l_entity_name VARCHAR2(200) := NULL;
665 l_line_id NUMBER := NULL;
666 l_smpl_source VARCHAR2(1) :=NULL;
667 l_include_in_CBR VARCHAR2(10) := 'XML';
668 l_external_event VARCHAR2(1) :='N';
669 l_compare_XML VARCHAR2(1) :='Y';
670 l_error_text VARCHAR2(400);
671 cursor cur_qa_results IS
672 SELECT PROCESS_BATCH_ID
673 ,PROCESS_BATCHSTEP_ID
674 ,PROCESS_OPERATION_ID
675 ,PROCESS_ACTIVITY_ID
676 ,PROCESS_RESOURCE_ID
677 ,PROCESS_PARAMETER_ID
678 FROM QA_RESULTS
679 WHERE
680 plan_id = l_plan_id
681 AND collection_id = l_collection_id
682 AND occurrence = l_occurrence ;
683 CURSOR get_collection_id IS
684 SELECT substrb(l_event_key,instrb(l_event_key,'-',1,1)+1,
685 decode(instrb(l_event_key,'-',1,2),0,
686 length(substrb(l_event_key,instrb(l_event_key,'-',1,1)+1)),
687 instrb(l_event_key,'-',1,2)-(instrb(l_event_key,'-',1,1)+1)))
688 FROM DUAL;
689 CURSOR GET_SAMPLE_DETAILS IS
690 SELECT SMPL.BATCH_ID,SMPL.STEP_ID,SMPL.FORMULALINE_ID
691 ,SMPL.MATERIAL_DETAIL_ID
692 FROM GMD_SAMPLES SMPL
693 WHERE SMPL.SAMPLE_ID = l_sample_id;
694
695 BEGIN
696 l_return_status:=wf_rule.setParametersIntoParameterList(p_subscription_guid,p_event);
697 --
698 -- Get parameters from ef_event structure for processing
699 --
700 l_event_name := p_event.getEventName();
701 l_event_key := p_event.getEventKey();
702 l_user_key_label := wf_event.getValueForParameter('PSIG_USER_KEY_LABEL',p_event.Parameter_List);
703 l_user_key_value := wf_event.getValueForParameter('PSIG_USER_KEY_VALUE',p_event.Parameter_List);
704 l_erecord_id := wf_event.getValueForParameter('#ERECORD_ID',p_event.Parameter_List);
705 l_map_code := wf_event.getValueForParameter('CBR_XML_MAP_CODE',p_event.Parameter_List);
706 l_compare_XML := nvl(wf_event.getValueForParameter('COMPARE_XML',p_event.Parameter_List),'Y');
707
708 l_error_text := 'Event Name:'|| l_event_name || 'Event Key:'||l_event_key;
709 --Diagnostics Start
710 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
711 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
712 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_CBR_GRP');
713 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PROCESS_EVENT');
714 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
715 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
716 'gmo.plsql.GMO_CBR_GRP.PROCESS_EVENT',
717 FALSE
718 );
719 end if;
720
721 l_error_text := 'user Key:'|| l_user_key_label || 'User Key:'||l_user_key_value ||'eRecordId:'||l_erecord_id ;
722 --Diagnostics Start
723 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
724 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
725 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_CBR_GRP');
726 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PROCESS_EVENT');
727 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
728 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
729 'gmo.plsql.GMO_CBR_GRP.PROCESS_EVENT',
730 FALSE
731 );
732 end if;
733 --
734 -- process Oracle Quality Events
735 --
736 IF l_event_name in ('oracle.apps.qa.disp.create'
737 ,'oracle.apps.qa.disp.detail.approve'
738 ,'oracle.apps.qa.disp.header.approve'
739 ,'oracle.apps.qa.disp.update'
740 ,'oracle.apps.qa.ncm.create'
741 ,'oracle.apps.qa.ncm.detail.approve'
742 ,'oracle.apps.qa.ncm.master.approve'
743 ,'oracle.apps.qa.ncm.update')
744 THEN
745 l_plan_id := substrb(l_event_key,1,instrb(l_event_key,'-',1,1)-1);
746 --
747 -- get collection id from event key. We need to use SQL as decode is not allowed
748 -- other than SQL
749 --
750 OPEN get_collection_id;
751 FETCH get_collection_id INTO l_collection_id;
752 CLOSE get_collection_id;
753 l_occurrence := substrb(l_event_key,instrb(l_event_key,'-',1,2)+1);
754 --
755 -- check current NCM is related to production batch
756 -- if current is not belogns to production batch then
757 -- we do not require to process
758 --
759 OPEN cur_qa_results;
760 FETCH cur_qa_results INTO l_batch_id,l_batchstep_id,l_operation_id,l_batch_activity_id,l_batch_rsrc_id,l_parameter_id;
761 CLOSE cur_qa_results;
762 IF l_batch_id is null THEN
763 RETURN 'SUCCESS';
764 END IF;
765 l_message_prod := 'GMO';
766 l_message_token:= 'GMO_QA_ERES_KEY_LABEL';
767 l_include_in_CBR := 'XTEXT';
768
769 ELSIf l_event_name in ('oracle.apps.gme.batch.erecords'
770 ,'oracle.apps.gme.batch.complete'
771 ,'oracle.apps.gme.batch.uncertify'
772 ,'oracle.apps.gme.batch.unrelease'
773 ,'oracle.apps.gme.batch.close'
774 ,'oracle.apps.gme.batch.reopen'
775 ,'oracle.apps.gme.batch.release'
776 ,'oracle.apps.gme.batch.terminate'
777 ,'oracle.apps.gme.batch.transact'
778 ,'oracle.apps.gme.batch.scale'
779 ,'oracle.apps.gme.batch.rsrc.trx'
780 ,'oracle.apps.gme.batch.cancel'
781 ,'oracle.apps.gme.batch.reroute'
782 ,'oracle.apps.gme.batch.rescheduled'
783 ,'oracle.apps.gme.batch.update')
784 THEN
785 --
786 -- process batch level Events
787 --
788 l_batch_id := l_event_key;
789 l_message_prod := 'GME';
790 l_message_token:= 'GME_PSIG_BATCH_LABEL';
791 SELECT PLAN_START_DATE into l_plan_start_date
792 FROM GME_BATCH_HEADER
793 WHERE batch_id = l_batch_id;
794 --
795 -- If event is batch creation event then store
796 -- e-record XML into CBR XML
797 --
798 IF l_event_name = 'oracle.apps.gme.batch.erecords' THEN
799 insert into GMO_CBR_XML (batch_id, CBR_XML)
800 VALUES (l_batch_id,p_event.geteventdata());
801 INSERT_BATH_EVENTS(l_batch_id);
802 END IF;
803 ELSIF l_event_name in ('oracle.apps.gme.batchstep.complete'
804 ,'oracle.apps.gme.batchstep.release'
805 ,'oracle.apps.gme.batchstep.uncertify'
806 ,'oracle.apps.gme.batchstep.unrelease'
807 ,'oracle.apps.gme.batchstep.close'
808 ,'oracle.apps.gme.batchstep.reopen') THEN
809 --
810 -- process batch step events
811 --
812 l_batchstep_id := l_event_key;
813 l_message_prod := 'GME';
814 l_message_token:= 'GME_PSIG_BATCH_STEP_LABEL';
815 SELECT batch_id,PLAN_START_DATE into l_batch_id,l_plan_start_date
816 FROM GME_BATCH_STEPS
817 WHERE batchstep_id = l_batchstep_id;
818 ELSIF l_event_name in ('oracle.apps.gme.batchstep.added'
819 ,'oracle.apps.gme.batchstep.removed'
820 ,'oracle.apps.gme.batchstep.update') THEN
821 --
822 -- process batch step events
823 --
824 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
825 l_batchstep_id := substrb(l_event_key,instrb(l_event_key,'-',1)+1);
826 l_message_prod := 'GME';
827 l_message_token:= 'GME_PSIG_BATCH_STEP_LABEL';
828 IF l_event_name in ('oracle.apps.gme.batchstep.added') THEN
829 SELECT PLAN_START_DATE into l_plan_start_date
830 FROM GME_BATCH_STEPS
831 WHERE batch_id = l_batch_id
832 AND batchstep_id = l_batchstep_id;
833 END IF;
834 ELSIF l_event_name in ('oracle.apps.gme.batchmtl.added'
835 ,'oracle.apps.gme.batchmtl.removed'
836 ,'oracle.apps.gme.batchmtl.updated') THEN
837 --
838 -- process batch material events
839 --
840 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
841 l_material_detail_id := substrb(l_event_key,instrb(l_event_key,'-',1)+1);
842 l_message_prod := 'GME';
843 l_message_token:= 'GME_PSIG_BATCH_MATL_LABEL';
844
845 ELSIF l_event_name in ('oracle.apps.gme.resource.added'
846 ,'oracle.apps.gme.resource.removed'
847 ,'oracle.apps.gme.resource.update') THEN
848 --
849 -- process batch step resource events
850 --
851 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
852 l_batchstep_id := substrb(l_event_key,instrb(l_event_key,'-',1)+1,instrb(l_event_key,'-',1,2)-(instrb(l_event_key,'-',1,1)+1));
853 l_batch_activity_id := substrb(l_event_key,instrb(l_event_key,'-',1,2)+1,instrb(l_event_key,'-',1,3)-(instrb(l_event_key,'-',1,2)+1));
854 l_batch_rsrc_id := substrb(l_event_key,instrb(l_event_key,'-',1,3)+1);
855 l_message_prod := 'GME';
856 l_message_token:= 'GME_PSIG_BATCH_STEP_RSRC_LABEL';
857 IF l_event_name = 'oracle.apps.gme.resource.added'
858 THEN
859 SELECT PLAN_START_DATE into l_plan_start_date
860 FROM GME_BATCH_STEP_RESOURCES
861 WHERE batch_id = l_batch_id
862 AND BATCHSTEP_RESOURCE_ID = l_batch_rsrc_id;
863 END IF;
864 ELSIF l_event_name in ('oracle.apps.gme.activity.added'
865 ,'oracle.apps.gme.activity.removed'
866 ,'oracle.apps.gme.activity.update') THEN
867 --
868 -- process batch step activity events
869 --
870 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
871 l_batchstep_id := substrb(l_event_key,instrb(l_event_key,'-',1)+1,instrb(l_event_key,'-',1,2)-(instrb(l_event_key,'-',1,1)+1));
872 l_batch_activity_id := substrb(l_event_key,instrb(l_event_key,'-',1,2)+1);
873 l_message_prod := 'GME';
874 l_message_token:= 'GME_PSIG_BATCH_STEP_ACT_LABEL';
875 IF l_event_name = 'oracle.apps.gme.activity.added' THEN
876 SELECT PLAN_START_DATE into l_plan_start_date
877 FROM GME_BATCH_STEP_ACTIVITIES
878 WHERE batch_id = l_batch_id
879 AND BATCHSTEP_ACTIVITY_ID = l_batch_activity_id;
880 END IF;
881 ELSIF l_event_name in ('oracle.apps.gme.batch.pparam') THEN
882 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
883 l_batch_rsrc_id := substrb(l_event_key,instrb(l_event_key,'-',1)+1,instrb(l_event_key,'-',1,2)-(instrb(l_event_key,'-',1,1)+1));
884 l_message_prod := 'GME';
885 l_message_token:= 'GME_PSIG_BATCH_RSRC_PARM_LABEL';
886
887 l_error_text := 'batch Id:'|| l_batch_id || 'resource id:'||l_batch_rsrc_id ;
888 --Diagnostics Start
889 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
890 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
891 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_CBR_GRP');
892 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PROCESS_EVENT');
893 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
894 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
895 'gmo.plsql.GMO_CBR_GRP.PROCESS_EVENT',
896 FALSE
897 );
898 end if;
899
900 ELSIF l_event_name in ('oracle.apps.gmo.labelprint') THEN
901 SELECT entity_name, entity_key INTO l_entity_name,l_dispense_id
902 from gmo_label_history
903 where label_id = l_event_key;
904 IF l_entity_name = 'GMO_DISPENSING'
905 THEN
906 l_message_prod := 'GMO';
907 l_message_token := 'GMO_PRNTLBL_KEY_LABEL';
908 l_parent_node := 'MATERIAL_DISPENSE';
909 l_parent_key_node := 'DISPENSE_ID';
910 l_parent_key_val := l_dispense_id;
911 l_curr_node_key := 'LABEL_ID';
912 l_curr_key_val := l_event_key;
913 ELSE
914 return 'SUCCESS';
915 END IF;
916 ELSIF l_event_name in ('oracle.apps.gmo.mtl.dispense') THEN
917 l_dispense_id := l_event_key;
918 l_message_prod := 'GMO';
919 l_message_token := 'GMO_DISP_MTL_DISP_KEY_LABEL';
920 ELSIF l_event_name in ('oracle.apps.gmo.mtl.revdisp') THEN
921 l_transaction_id := l_event_key;
922 l_message_prod := 'GMO';
923 l_message_token := 'GMO_DISP_RVDISP_KEY_LABEL';
924 ELSIF l_event_name in ('oracle.apps.gmd.qm.smpl.crea',
925 'oracle.apps.gmd.qm.rslt.entry' ) THEN
926 if l_event_name = 'oracle.apps.gmd.qm.smpl.crea'
927 then
928 l_sample_id := l_event_key;
929 else
930 l_sample_id := substrb(l_event_key,1,instrb(l_event_key,'-',1,1) -1);
931 end if;
932 OPEN GET_SAMPLE_DETAILS;
933 FETCH GET_SAMPLE_DETAILS INTO l_batch_id, l_batchstep_id ,l_line_id,l_material_detail_id;
934 CLOSE GET_SAMPLE_DETAILS;
935 if l_batch_id is null
936 then
937 return 'SUCCESS';
938 ELSE
939 if l_event_name = 'oracle.apps.gmd.qm.smpl.crea'
940 then
941 l_message_prod := 'GMD';
942 l_message_token := 'GMD_ERES_SAMPLE_LBL';
943 if l_material_detail_id is not null
944 then
945 l_parent_node := 'MATERIAL_REQUIREMENTS';
946 l_parent_key_node := 'MATERIAL_DETAIL_ID';
947 l_parent_key_val := l_material_detail_id;
948 l_curr_node_key := 'SAMPLE_ID';
949 l_curr_key_val := l_sample_id;
950 elsif l_batchstep_id is not null
951 then
952 l_parent_node := 'ROUTING_STEPS';
953 l_parent_key_node := 'STEP_ID';
954 l_parent_key_val := l_batchstep_id;
955 l_curr_node_key := 'SAMPLE_ID';
956 l_curr_key_val := l_sample_id;
957 elsif l_batch_id is not null
958 then
959 l_parent_node := 'HEADER_INFORMATION';
960 l_parent_key_node := 'BATCH_ID';
961 l_parent_key_val := l_batch_id;
962 l_curr_node_key := 'SAMPLE_ID';
963 l_curr_key_val := l_sample_id;
964 end if;
965 else
966 l_message_prod := 'GMD';
967 l_message_token := 'GMD_ERES_RESULT_LBL';
968 end if;
969 END IF;
970 END IF;
971 --
972 -- Manage events created in batch progression at the time of batch creation
973 --
974 If l_event_name in ('oracle.apps.gme.batch.complete'
975 ,'oracle.apps.gme.batch.close'
976 ,'oracle.apps.gme.batch.release'
977 ,'oracle.apps.gme.batchstep.complete'
978 ,'oracle.apps.gme.batchstep.release'
979 ,'oracle.apps.gme.batchstep.close')
980 THEN
981 /* delete progression row created when batch is created */
982 delete GMO_BATCH_PROGRESSION
983 where event = l_event_name
984 AND event_key = l_event_key
985 AND STATUS = 'PENDING';
986 END IF;
987
988
989 /* update the progression status for other event */
990 UPDATE_PROGRESSION_STATUS(l_batch_id,l_batchstep_id,l_event_name);
991
992 /* populate Record object with the details */
993 l_error_text := 'Before populating Record Type' ;
994 --Diagnostics Start
995 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
996 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
997 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_CBR_GRP');
998 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PROCESS_EVENT');
999 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1000 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1001 'gmo.plsql.GMO_CBR_GRP.PROCESS_EVENT',
1002 FALSE
1003 );
1004 end if;
1005 l_batch_prog_rec.PROGRESSION_ID := NULL;
1006 l_batch_prog_rec.BATCH_ID := l_batch_id;
1007 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id ;
1008 l_batch_prog_rec.MATERIAL_LINE_ID := l_material_detail_id;
1009 l_batch_prog_rec.BATCHACTIVITY_ID := l_batch_activity_id;
1010 l_batch_prog_rec.BATCHRSRC_ID :=l_batch_rsrc_id ;
1011 l_batch_prog_rec.DISPENSE_ID := l_dispense_id;
1012 l_batch_prog_rec.SAMPLE_ID := l_sample_id;
1013 l_batch_prog_rec.DEVIATION_ID := l_deviation_id;
1014 l_batch_prog_rec.TRANSACTION_ID := l_transaction_id;
1015 l_batch_prog_rec.PARENT_NODE := l_parent_node;
1016 l_batch_prog_rec.PARENT_KEY_NODE := l_parent_key_node;
1017 l_batch_prog_rec.PARENT_KEY_VAL := l_parent_key_val;
1018 l_batch_prog_rec.CURR_NODE_KEY := l_curr_node_key;
1019 l_batch_prog_rec.CURR_KEY_VAL := l_curr_key_val;
1020 l_batch_prog_rec.USER_KEY_LABEL_PROD := l_message_prod;
1021 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := l_message_token;
1022 l_batch_prog_rec.USER_KEY_LABEL := l_user_key_label;
1023 l_batch_prog_rec.USER_KEY_VALUE := l_user_key_value;
1024 l_batch_prog_rec.EXTERNAL_EVENT := l_external_event;
1025 l_batch_prog_rec.INCLUDE_IN_CBR := l_include_in_CBR;
1026 l_batch_prog_rec.EVENT := l_event_name;
1027 l_batch_prog_rec.EVENT_KEY := l_event_key;
1028 l_batch_prog_rec.EVENT_DATE := SYSDATE;
1029 l_batch_prog_rec.PLANED_START_DATE := l_plan_start_date;
1030 l_batch_prog_rec.ERECORD_ID := l_erecord_id;
1031 l_batch_prog_rec.XML_EREC := l_xml_erec;
1032 IF l_event_name = 'oracle.apps.gme.batch.erecords' THEN
1033 l_batch_prog_rec.STATUS := 'COMPLETE';
1034 l_batch_prog_rec.INCLUDED_IN_CBR := 'Y';
1035 ELSE
1036 l_batch_prog_rec.STATUS := 'INPROGRESS';
1037 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1038 END IF;
1039 l_batch_prog_rec.XML_MAP_CODE := l_map_code;
1040 l_batch_prog_rec.compare_xml:=l_compare_XML;
1041 l_error_text := 'Before insert' ;
1042 --Diagnostics Start
1043 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1044 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
1045 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_CBR_GRP');
1046 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PROCESS_EVENT');
1047 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1048 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1049 'gmo.plsql.GMO_CBR_GRP.PROCESS_EVENT',
1050 FALSE
1051 );
1052 end if;
1053 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1054 wf_event.AddParameterToList('BATCH_PROGRESSION_ID', l_batch_progression_id,p_event.Parameter_List);
1055 return 'SUCCESS';
1056 END;
1057
1058
1059 PROCEDURE INSERT_BATH_EVENTS(P_BATCH_ID IN NUMBER) IS
1060 l_batch_prog_rec GMO_BATCH_PROGRESSION%ROWTYPE;
1061 l_batch_progression_id NUMBER;
1062 l_step_label VARCHAR2(400);
1063 l_step_user_val VARCHAR2(400);
1064 CURSOR Get_BATCH_DETAILS IS
1065 SELECT BATCH_ID,BATCH_NO,PLANT_CODE,PLAN_START_DATE,PLAN_CMPLT_DATE
1066 FROM GME_BATCH_HEADER_VW
1067 WHERE batch_id = P_BATCH_ID;
1068 CURSOR GET_STEP_DETAILS IS
1069 SELECT BATCH_ID,BATCHSTEP_ID,BATCHSTEP_NO,OPERATION_NO,PLAN_START_DATE,PLAN_CMPLT_DATE
1070 FROM gme_batch_steps_v
1071 WHERE BATCH_ID = P_BATCH_ID;
1072 BATCH_DETAILS_REC Get_BATCH_DETAILS%ROWTYPE;
1073 STEP_DETAILS_REC GET_STEP_DETAILS%ROWTYPE;
1074 BEGIN
1075 --
1076 -- Create Batch progression rows when batch is created.
1077 -- oracle.apps.gme.batch.complete
1078 -- oracle.apps.gme.batch.close
1079 -- oracle.apps.gme.batch.release
1080 -- create step level rows for each step.
1081 -- oracle.apps.gme.batchstep.complete
1082 -- oracle.apps.gme.batchstep.release
1083 -- oracle.apps.gme.batchstep.close
1084 --
1085 OPEN Get_BATCH_DETAILS;
1086 FETCH Get_BATCH_DETAILS INTO BATCH_DETAILS_REC;
1087 CLOSE Get_BATCH_DETAILS;
1088 l_batch_prog_rec.PROGRESSION_ID := NULL;
1089 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1090 l_batch_prog_rec.BATCHSTEP_ID := NULL;
1091 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1092 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1093 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1094 l_batch_prog_rec.DISPENSE_ID := NULL;
1095 l_batch_prog_rec.SAMPLE_ID := NULL;
1096 l_batch_prog_rec.DEVIATION_ID := NULL;
1097 l_batch_prog_rec.TRANSACTION_ID := NULL;
1098 l_batch_prog_rec.PARENT_NODE := NULL;
1099 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1100 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1101 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1102 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1103 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1104 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_LABEL';
1105 l_batch_prog_rec.USER_KEY_LABEL := FND_MESSAGE.GET_STRING('GME','GME_PSIG_BATCH_LABEL');
1106 l_batch_prog_rec.USER_KEY_VALUE := BATCH_DETAILS_REC.PLANT_CODE || '-'||BATCH_DETAILS_REC.BATCH_NO;
1107 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1108 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1109 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batch.release';
1110 l_batch_prog_rec.EVENT_KEY := P_BATCH_ID;
1111 l_batch_prog_rec.EVENT_DATE := NULL;
1112 l_batch_prog_rec.PLANED_START_DATE := BATCH_DETAILS_REC.PLAN_START_DATE;
1113 l_batch_prog_rec.ERECORD_ID := NULL;
1114 l_batch_prog_rec.XML_EREC := NULL;
1115 l_batch_prog_rec.STATUS := 'PENDING';
1116 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1117 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1118 OPEN GET_STEP_DETAILS ;
1119 LOOP
1120 FETCH GET_STEP_DETAILS INTO STEP_DETAILS_REC;
1121 EXIT WHEN GET_STEP_DETAILS%NOTFOUND;
1122 l_step_label := FND_MESSAGE.GET_STRING('GME','GME_PSIG_BATCH_STEP_LABEL');
1123 l_step_user_val := BATCH_DETAILS_REC.PLANT_CODE || '-'||BATCH_DETAILS_REC.BATCH_NO||
1124 '-' ||STEP_DETAILS_REC.BATCHSTEP_NO||'-'||STEP_DETAILS_REC.OPERATION_NO;
1125 l_batch_prog_rec.PROGRESSION_ID := NULL;
1126 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1127 l_batch_prog_rec.BATCHSTEP_ID := STEP_DETAILS_REC.BATCHSTEP_ID;
1128 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1129 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1130 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1131 l_batch_prog_rec.DISPENSE_ID := NULL;
1132 l_batch_prog_rec.SAMPLE_ID := NULL;
1133 l_batch_prog_rec.DEVIATION_ID := NULL;
1134 l_batch_prog_rec.TRANSACTION_ID := NULL;
1135 l_batch_prog_rec.PARENT_NODE := NULL;
1136 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1137 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1138 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1139 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1140 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1141 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_STEP_LABEL';
1142 l_batch_prog_rec.USER_KEY_LABEL := l_step_label;
1143 l_batch_prog_rec.USER_KEY_VALUE := l_step_user_val;
1144 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1145 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1146 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batchstep.release';
1147 l_batch_prog_rec.EVENT_DATE := NULL;
1148 l_batch_prog_rec.EVENT_KEY := STEP_DETAILS_REC.BATCHSTEP_ID ;
1149 l_batch_prog_rec.PLANED_START_DATE := STEP_DETAILS_REC.PLAN_START_DATE;
1150 l_batch_prog_rec.ERECORD_ID := NULL;
1151 l_batch_prog_rec.XML_EREC := NULL;
1152 l_batch_prog_rec.STATUS := 'PENDING';
1153 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1154 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1155 l_batch_prog_rec.PROGRESSION_ID := NULL;
1156 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1157 l_batch_prog_rec.BATCHSTEP_ID := STEP_DETAILS_REC.BATCHSTEP_ID;
1158 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1159 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1160 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1161 l_batch_prog_rec.DISPENSE_ID := NULL;
1162 l_batch_prog_rec.SAMPLE_ID := NULL;
1163 l_batch_prog_rec.DEVIATION_ID := NULL;
1164 l_batch_prog_rec.TRANSACTION_ID := NULL;
1165 l_batch_prog_rec.PARENT_NODE := NULL;
1166 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1167 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1168 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1169 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1170 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1171 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_STEP_LABEL';
1172 l_batch_prog_rec.USER_KEY_LABEL := l_step_label;
1173 l_batch_prog_rec.USER_KEY_VALUE :=l_step_user_val;
1174 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1175 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1176 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batchstep.complete';
1177 l_batch_prog_rec.EVENT_KEY := STEP_DETAILS_REC.BATCHSTEP_ID ;
1178 l_batch_prog_rec.EVENT_DATE := NULL;
1179 l_batch_prog_rec.PLANED_START_DATE := STEP_DETAILS_REC.PLAN_CMPLT_DATE;
1180 l_batch_prog_rec.ERECORD_ID := NULL;
1181 l_batch_prog_rec.XML_EREC := NULL;
1182 l_batch_prog_rec.STATUS := 'PENDING';
1183 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1184 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1185 l_batch_prog_rec.PROGRESSION_ID := NULL;
1186 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1187 l_batch_prog_rec.BATCHSTEP_ID := STEP_DETAILS_REC.BATCHSTEP_ID;
1188 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1189 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1190 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1191 l_batch_prog_rec.DISPENSE_ID := NULL;
1192 l_batch_prog_rec.SAMPLE_ID := NULL;
1193 l_batch_prog_rec.DEVIATION_ID := NULL;
1194 l_batch_prog_rec.TRANSACTION_ID := NULL;
1195 l_batch_prog_rec.PARENT_NODE := NULL;
1196 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1197 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1198 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1199 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1200 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1201 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_STEP_LABEL';
1202 l_batch_prog_rec.USER_KEY_LABEL := l_step_label;
1203 l_batch_prog_rec.USER_KEY_VALUE :=l_step_user_val;
1204 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1205 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1206 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batchstep.close';
1207 l_batch_prog_rec.EVENT_KEY := STEP_DETAILS_REC.BATCHSTEP_ID ;
1208 l_batch_prog_rec.EVENT_DATE := NULL;
1209 l_batch_prog_rec.PLANED_START_DATE := null;
1210 l_batch_prog_rec.ERECORD_ID := NULL;
1211 l_batch_prog_rec.XML_EREC := NULL;
1212 l_batch_prog_rec.STATUS := 'PENDING';
1213 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1214 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1215 END LOOP;
1216 CLOSE GET_STEP_DETAILS;
1217 l_batch_prog_rec.PROGRESSION_ID := NULL;
1218 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1219 l_batch_prog_rec.BATCHSTEP_ID := NULL;
1220 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1221 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1222 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1223 l_batch_prog_rec.DISPENSE_ID := NULL;
1224 l_batch_prog_rec.SAMPLE_ID := NULL;
1225 l_batch_prog_rec.DEVIATION_ID := NULL;
1226 l_batch_prog_rec.TRANSACTION_ID := NULL;
1227 l_batch_prog_rec.PARENT_NODE := NULL;
1228 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1229 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1230 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1231 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1232 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1233 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_LABEL';
1234 l_batch_prog_rec.USER_KEY_LABEL := FND_MESSAGE.GET_STRING('GME','GME_PSIG_BATCH_LABEL');
1235 l_batch_prog_rec.USER_KEY_VALUE := BATCH_DETAILS_REC.PLANT_CODE || '-'||BATCH_DETAILS_REC.BATCH_NO;
1236 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1237 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1238 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batch.complete';
1239 l_batch_prog_rec.EVENT_KEY := P_BATCH_ID;
1240 l_batch_prog_rec.EVENT_DATE := NULL;
1241 l_batch_prog_rec.PLANED_START_DATE := BATCH_DETAILS_REC.PLAN_CMPLT_DATE;
1242 l_batch_prog_rec.ERECORD_ID := NULL;
1243 l_batch_prog_rec.XML_EREC := NULL;
1244 l_batch_prog_rec.STATUS := 'PENDING';
1245 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1246 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1247 l_batch_prog_rec.PROGRESSION_ID := NULL;
1248 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1249 l_batch_prog_rec.BATCHSTEP_ID := NULL;
1250 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1251 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1252 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1253 l_batch_prog_rec.DISPENSE_ID := NULL;
1254 l_batch_prog_rec.SAMPLE_ID := NULL;
1255 l_batch_prog_rec.DEVIATION_ID := NULL;
1256 l_batch_prog_rec.TRANSACTION_ID := NULL;
1257 l_batch_prog_rec.PARENT_NODE := NULL;
1258 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1259 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1260 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1261 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1262 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1263 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_LABEL';
1264 l_batch_prog_rec.USER_KEY_LABEL := FND_MESSAGE.GET_STRING('GME','GME_PSIG_BATCH_LABEL');
1265 l_batch_prog_rec.USER_KEY_VALUE := BATCH_DETAILS_REC.PLANT_CODE || '-'||BATCH_DETAILS_REC.BATCH_NO;
1266 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1267 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1268 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batch.close';
1269 l_batch_prog_rec.EVENT_KEY := P_BATCH_ID;
1270 l_batch_prog_rec.EVENT_DATE := NULL;
1271 l_batch_prog_rec.PLANED_START_DATE := null;
1272 l_batch_prog_rec.ERECORD_ID := NULL;
1273 l_batch_prog_rec.XML_EREC := NULL;
1274 l_batch_prog_rec.STATUS := 'PENDING';
1275 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1276 l_batch_prog_rec.compare_xml:='N';
1277 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 null;
1281 END;
1282
1283 function getValue(P_XML XMLType,P_VARIABLE VARCHAR2) return VARCHAR2
1284 IS
1285 tmp varchar2(400);
1286 BEGIN
1287 select extractValue(P_XML,P_VARIABLE) into tmp from dual;
1288 return tmp;
1289 END getValue;
1290
1291
1292 PROCEDURE GET_INSTR_XML (P_EVENT_KEY IN VARCHAR2,P_MAP_CODE IN VARCHAR2,X_FINAL_XML OUT NOCOPY XMLType) IS
1293
1294 l_xml CLOB;
1295 l_final_xml CLOB;
1296 l_result XMLType;
1297 l_erecID NUMBER(22);
1298 --This holds the query context.
1299 qryCtx DBMS_XMLGEN.ctxHandle;
1300 l_srcDoc dbms_xmldom.DOMDocument;
1301 l_srcDocEle dbms_xmldom.DOMELEMENT;
1302 l_parentRootNode DBMS_XMLDOM.DOMNODE;
1303 l_sigHDRDoc DBMS_XMLDOM.DOMDOCUMENT;
1304 l_childRootNode DBMS_XMLDOM.DOMNODE;
1305 l_sigHDRDocEle dbms_xmldom.DOMELEMENT;
1306 l_childNodeList dbms_xmldom.DOMNodeList;
1307 l_childNode dbms_xmldom.DOMNode;
1308 l_nodeListDel dbms_xmldom.DOMNodeList;
1309 l_tmpnode dbms_xmldom.DOMNode;
1310 l_esigNode dbms_xmldom.DOMNode;
1311 l_ehdrNode dbms_xmldom.DOMNode;
1312 l_eleNodeList dbms_xmldom.DOMNodeList;
1313 l_eleNode dbms_xmldom.DOMNode;
1314 esigHdrFinalXML XMLType;
1315 l_debug_level number(2):=6;
1316 l_error_code pls_integer;
1317 l_log_file varchar2(2000);
1318 l_error_msg varchar2(2000);
1319 DB_TO_XML_ERROR EXCEPTION;
1320 BEGIN
1321
1322 ecx_outbound.getXML(i_map_code => P_MAP_CODE,
1323 i_document_id => P_EVENT_KEY,
1324 i_debug_level => l_debug_level,
1325 i_xmldoc => l_xml,
1326 i_ret_code => l_error_code,
1327 i_errbuf => l_error_msg,
1328 i_log_file => l_log_file);
1329
1330 --If the return code from ECX is a value other than 0 then
1331 --an error has occurred
1332 if(l_error_code <> 0) then
1333 raise DB_TO_XML_ERROR;
1334 end if;
1335
1336
1337
1338
1339 -- construct the XML Type object
1340 l_result := xmltype(l_xml);
1341
1342 l_srcDoc := dbms_xmldom.newDOMDocument(l_result);
1343 l_srcDocEle := dbms_xmldom.getDocumentElement(l_srcDoc);
1344 l_parentRootNode := DBMS_XMLDOM.makeNode(l_srcDocEle);
1345 l_eleNodeList := dbms_xmldom.getElementsByTagName(l_srcDocEle,'InstrErecID');
1346 For i in 0..dbms_xmldom.getLength(l_eleNodeList)-1 LOOP
1347 l_eleNode := dbms_xmldom.item(l_eleNodeList, i);
1348 -- import Erec Header node and signature node
1349 l_erecID := TO_NUMBER(DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(l_eleNode)));
1350 EDR_PSIG.GET_EVENT_XML(P_EVENT_NAME =>null,
1351 P_EVENT_KEY => null,
1352 P_ERECORD_ID => l_erecID,
1353 P_GET_ERECORD_XML =>'F',
1354 P_GET_PSIG_DETAILS =>'T',
1355 P_GET_ACKN_DETAILS =>'F',
1356 P_GET_PRINT_DETAILS =>'F',
1357 P_GET_RELATED_EREC_DETAILS =>'F',
1358 X_FINAL_XML => l_final_xml);
1359 esigHdrFinalXML := xmltype(l_final_xml);
1360
1361
1362 -- construct ESIG HDR Node
1363 l_sigHDRDoc := DBMS_XMLDOM.newDOMDocument(esigHdrFinalXML);
1364 -- make the Header Noe
1365 l_sigHDRDocEle := DBMS_XMLDOM.getDocumentElement(l_sigHDRDoc);
1366 l_childRootNode := DBMS_XMLDOM.makeNode(l_sigHDRDocEle);
1367
1368 -- get the Heder Details node
1369 l_childNodeList := dbms_xmldom.getElementsByTagName(l_sigHDRDocEle,'ERECORD_HEADER_DETAILS');
1370 l_ehdrNode := dbms_xmldom.item(l_childNodeList, 0);
1371 -- get the Node to be deleted
1372 l_nodeListDel := dbms_xmldom.getElementsByTagName(l_sigHDRDocEle,'DOC_PARAM_DETAILS');
1373 For i in 0..dbms_xmldom.getLength(l_nodeListDel)-1 LOOP
1374 l_tmpnode := dbms_xmldom.item(l_nodeListDel, i);
1375 l_childNode := dbms_xmldom.removeChild(l_ehdrNode,l_tmpnode);
1376 END LOOP;
1377
1378 -- get the Signature Details node
1379 l_childNodeList := dbms_xmldom.getElementsByTagName(l_sigHDRDocEle,'ERECORD_SIGNATURE_DETAILS');
1380 l_esigNode := dbms_xmldom.item(l_childNodeList, 0);
1381 -- get the Node to be deleted
1382 l_nodeListDel := dbms_xmldom.getElementsByTagName(l_sigHDRDocEle,'SIGNATURE_PARAMS');
1383 For i in 0..dbms_xmldom.getLength(l_nodeListDel)-1 LOOP
1384 l_tmpnode := dbms_xmldom.item(l_nodeListDel, i);
1385 l_childNode := dbms_xmldom.removeChild(l_esigNode,l_tmpnode);
1386 END LOOP;
1387 -- append Signature node into Header node
1388 if (dbms_xmldom.isNull(l_esigNode) = false) THEN
1389 l_tmpnode := dbms_xmldom.appendChild(l_ehdrNode,l_esigNode);
1390 END IF;
1391
1392 -- import the Hdr node in Source Document
1393 l_childRootNode := dbms_xmldom.importNode(l_srcDoc,l_childRootNode,TRUE);
1394 -- append the HDR node in Source XML
1395 l_parentRootNode := dbms_xmldom.appendChild(dbms_xmldom.getParentNode(l_eleNode),l_childRootNode);
1396 if (dbms_xmldom.isNull(l_sigHDRDoc) = false) THEN
1397 dbms_xmldom.freeDocument(l_sigHDRDoc);
1398 End IF;
1399 END LOOP;
1400
1401 X_FINAL_XML := l_result.extract('//INSTRUCTION_SET');
1402 if (dbms_xmldom.isNull(l_srcDoc) = false) THEN
1403 dbms_xmldom.freeDocument(l_srcDoc);
1404 End IF;
1405 EXCEPTION
1406 when DB_TO_XML_ERROR then
1407 FND_MESSAGE.SET_NAME('GMO','GMO_VALIDATE_XML_GEN_ERR');
1408 FND_MESSAGE.SET_TOKEN('OPERATION','DB to XML');
1409 FND_MESSAGE.SET_TOKEN('ERROR_DETAILS',l_error_msg);
1410 FND_MESSAGE.SET_TOKEN('LOG_DETAILS',l_log_file);
1411 APP_EXCEPTION.RAISE_EXCEPTION;
1412 END GET_INSTR_XML;
1413
1414 PROCEDURE PROCESS_INSTR_XML (p_entity_name VARCHAR2,p_entity_key VARCHAR2,p_event_key varchar2,p_instr_type VARCHAR2,
1415 p_instr_status VARCHAR2,P_BATCHPROGRESSION_STATUS VARCHAR2,P_EVENT_NAME VARCHAR2,P_FINAL_XML XMLType) IS
1416 l_batch_id number := null;
1417 l_batchstep_id number := null;
1418 l_material_detail_id number := null;
1419 l_batchstep_activity_id number := null;
1420 l_dispense_id NUMBER := NULL;
1421 l_parent_node VARCHAR2(80) := NULL;
1422 l_parent_key_node VARCHAR2(80) := NULL;
1423 l_parent_key_val VARCHAR2(80) := NULL;
1424 l_curr_node_key VARCHAR2(80) := NULL;
1425 l_curr_key_val VARCHAR2(80) := NULL;
1426 l_batch_prog_rec GMO_BATCH_PROGRESSION%ROWTYPE;
1427 l_batch_progression_id NUMBER := NULL;
1428 l_message_prod VARCHAR2(10) := 'GMO';
1429 l_message_token VARCHAR2(50) := NULL;
1430 l_user_key_label VARCHAR2(80) := NULL;
1431 l_user_key_value VARCHAR2(80) := p_entity_key;
1432
1433 l_event_data CLOB;
1434 l_MSG_COUNT NUMBER(22);
1435 l_MSG_DATA VARCHAR2(2000);
1436 CURSOR get_dispense_Details IS
1437 SELECT batch_id, batch_step_id ,material_detail_id
1438 FROM gmo_material_dispenses
1439 WHERE dispense_id = p_entity_key;
1440 CURSOR get_rev_dispense_Details IS
1441 SELECT batch_id, batch_step_id ,material_detail_id,dispense_id
1442 FROM gmo_material_undispenses
1443 WHERE undispense_id = p_entity_key;
1444 CURSOR get_resource_details IS
1445 SELECT BATCH_ID,BATCHSTEP_ID,BATCHSTEP_ACTIVITY_ID FROM GME_BATCH_STEP_RESOURCES
1446 where BATCHSTEP_RESOURCE_ID = p_entity_key;
1447 BEGIN
1448 delete GMO_BATCH_PROGRESSION
1449 where event = P_EVENT_NAME
1450 AND event_key = p_event_key
1451 AND STATUS = 'INPROGRESS';
1452
1453 select P_FINAL_XML.getCLobVal() into l_event_data from dual;
1454
1455 IF(p_instr_type = 'DISPENSE' and p_entity_name ='DISPENSE_ITEM') THEN
1456 OPEN get_dispense_Details;
1457 FETCH get_dispense_Details into l_batch_id, l_batchstep_id ,l_material_detail_id;
1458 CLOSE get_dispense_Details;
1459 l_message_token := 'GMO_ENTITY_DISPENSE_ITEM';
1460 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1461 l_batch_prog_rec.PROGRESSION_ID := NULL;
1462 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1463 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id;
1464 l_batch_prog_rec.MATERIAL_LINE_ID := l_material_detail_id;
1465 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1466 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1467 l_batch_prog_rec.DISPENSE_ID := p_entity_key;
1468 l_batch_prog_rec.SAMPLE_ID := NULL;
1469 l_batch_prog_rec.DEVIATION_ID := NULL;
1470 l_batch_prog_rec.TRANSACTION_ID := NULL;
1471 l_batch_prog_rec.PARENT_NODE := 'MATERIAL_DISPENSE';
1472 l_batch_prog_rec.PARENT_KEY_NODE := 'DISPENSE_ID';
1473 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1474 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1475 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1476 -- Bug 5231778 : change the Instr Type for Reverse Dispense.
1477 ELSIF(p_instr_type = 'REVERSE_DISPENSE' and p_entity_name ='DISPENSE_ITEM') THEN
1478 OPEN get_rev_dispense_Details;
1479 FETCH get_rev_dispense_Details into l_batch_id, l_batchstep_id ,l_material_detail_id,l_dispense_id;
1480 CLOSE get_rev_dispense_Details;
1481 l_message_token := 'GMO_ENTITY_REV_DISP_ITEM';
1482 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1483 l_batch_prog_rec.PROGRESSION_ID := NULL;
1484 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1485 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id;
1486 l_batch_prog_rec.MATERIAL_LINE_ID := l_material_detail_id;
1487 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1488 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1489 l_batch_prog_rec.DISPENSE_ID := p_entity_key;
1490 l_batch_prog_rec.SAMPLE_ID := NULL;
1491 l_batch_prog_rec.DEVIATION_ID := NULL;
1492 l_batch_prog_rec.TRANSACTION_ID := NULL;
1493 l_batch_prog_rec.PARENT_NODE := 'MATERIAL_REVERSE_DISPENSE';
1494 l_batch_prog_rec.PARENT_KEY_NODE := 'UNDISPENSE_ID';
1495 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1496 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1497 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1498 ELSIF(p_instr_type = 'PROCESS' and p_entity_name ='ACTIVITY') THEN
1499 SELECT BATCH_ID,BATCHSTEP_ID into l_batch_id,l_batchstep_id FROM GME_BATCH_STEP_ACTIVITIES
1500 WHERE batchstep_activity_id = p_entity_key;
1501 l_message_token := 'GMO_ENTITY_ACTIVITY';
1502 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1503 l_batch_prog_rec.PROGRESSION_ID := NULL;
1504 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1505 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id;
1506 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1507 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1508 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1509 l_batch_prog_rec.DISPENSE_ID := NULL;
1510 l_batch_prog_rec.SAMPLE_ID := NULL;
1511 l_batch_prog_rec.DEVIATION_ID := NULL;
1512 l_batch_prog_rec.TRANSACTION_ID := NULL;
1513 l_batch_prog_rec.PARENT_NODE := 'ACTIVITIES';
1514 l_batch_prog_rec.PARENT_KEY_NODE := 'STEP_ACTIVITY_ID';
1515 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1516 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1517 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1518 ELSIF(p_instr_type = 'PROCESS' and p_entity_name ='RESOURCE') THEN
1519 open get_resource_details;
1520 FETCH get_resource_details into l_batch_id,l_batchstep_id,l_batchstep_activity_id;
1521 CLOSE get_resource_details;
1522 l_message_token := 'GMO_ENTITY_RESOURCE';
1523 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1524 l_batch_prog_rec.PROGRESSION_ID := NULL;
1525 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1526 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id;
1527 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1528 l_batch_prog_rec.BATCHACTIVITY_ID := l_batchstep_activity_id;
1529 l_batch_prog_rec.BATCHRSRC_ID := p_entity_key;
1530 l_batch_prog_rec.DISPENSE_ID := NULL;
1531 l_batch_prog_rec.SAMPLE_ID := NULL;
1532 l_batch_prog_rec.DEVIATION_ID := NULL;
1533 l_batch_prog_rec.TRANSACTION_ID := NULL;
1534 l_batch_prog_rec.PARENT_NODE := 'RESOURCE_REQUIREMENTS';
1535 l_batch_prog_rec.PARENT_KEY_NODE := 'STEP_RESOURCE_ID';
1536 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1537 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1538 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1539 ELSIF(p_instr_type = 'PROCESS' and p_entity_name ='MATERIAL') THEN
1540 select batch_id into l_batch_id from gme_material_details
1541 where material_detail_id=p_entity_key;
1542 l_message_token := 'GMO_ENTITY_MATERIAL';
1543 l_user_key_label := FND_MESSAGE.GET_STRING('GMO', l_message_token);
1544 l_batch_prog_rec.PROGRESSION_ID := NULL;
1545 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1546 l_batch_prog_rec.BATCHSTEP_ID := NULL;
1547 l_batch_prog_rec.MATERIAL_LINE_ID := p_entity_key;
1548 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1549 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1550 l_batch_prog_rec.DISPENSE_ID := NULL;
1551 l_batch_prog_rec.SAMPLE_ID := NULL;
1552 l_batch_prog_rec.DEVIATION_ID := NULL;
1553 l_batch_prog_rec.TRANSACTION_ID := NULL;
1554 l_batch_prog_rec.PARENT_NODE := 'MATERIAL_REQUIREMENTS';
1555 l_batch_prog_rec.PARENT_KEY_NODE := 'MATERIAL_DETAIL_ID';
1556 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1557 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1558 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1559 ELSIF(p_instr_type = 'PROCESS' and p_entity_name ='OPERATION') THEN
1560 SELECT batch_id into l_batch_id
1561 FROM GME_BATCH_STEPS
1562 WHERE batchstep_id = p_entity_key;
1563 l_message_token := 'GMO_ENTITY_OPERATION';
1564 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1565 l_batch_prog_rec.PROGRESSION_ID := NULL;
1566 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1567 l_batch_prog_rec.BATCHSTEP_ID := p_entity_key;
1568 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1569 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1570 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1571 l_batch_prog_rec.DISPENSE_ID := NULL;
1572 l_batch_prog_rec.SAMPLE_ID := NULL;
1573 l_batch_prog_rec.DEVIATION_ID := NULL;
1574 l_batch_prog_rec.TRANSACTION_ID := NULL;
1575 l_batch_prog_rec.PARENT_NODE := 'ROUTING_STEPS';
1576 l_batch_prog_rec.PARENT_KEY_NODE := 'STEP_ID';
1577 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1578 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1579 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1580 END IF;
1581 l_batch_prog_rec.USER_KEY_LABEL_PROD := l_message_prod;
1582 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := l_message_token;
1583 l_batch_prog_rec.USER_KEY_LABEL := l_user_key_label;
1584 l_batch_prog_rec.USER_KEY_VALUE := l_user_key_value;
1585 l_batch_prog_rec.EXTERNAL_EVENT := 'N';
1586 l_batch_prog_rec.INCLUDE_IN_CBR := 'XTEXT';
1587 l_batch_prog_rec.EVENT := P_EVENT_NAME;
1588 l_batch_prog_rec.EVENT_KEY := p_event_key;
1589 l_batch_prog_rec.EVENT_DATE := SYSDATE;
1590 l_batch_prog_rec.PLANED_START_DATE := null;
1591 l_batch_prog_rec.ERECORD_ID := NULL;
1592 l_batch_prog_rec.XML_EREC := l_event_data;
1593 l_batch_prog_rec.STATUS := P_BATCHPROGRESSION_STATUS;
1594 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1595 l_batch_prog_rec.COMPARE_XML := 'N';
1596 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1597
1598 EXCEPTION
1599 WHEN OTHERS THEN
1600 FND_MESSAGE.SET_NAME('GMO','FND_AS_UNEXPECTED_ERROR');
1601 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1602 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
1603 FND_MSG_PUB.ADD;
1604 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count, p_data => l_msg_data);
1605 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1606 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.GMO_CBR_GRP.PROCESS_INSTR_XML', FALSE);
1607 end if;
1608 END PROCESS_INSTR_XML;
1609
1610 FUNCTION PROCESS_INSTANCE_INSTR_SET(P_SUBSCRIPTION_GUID IN RAW, P_EVENT IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2 IS
1611 l_xml_map_code varchar2(240);
1612 l_result XMLType;
1613 l_temp XMLType;
1614 l_entity_name VARCHAR2(200);
1615 l_entity_key VARCHAR2(500);
1616 l_instr_type VARCHAR2(40);
1617 l_instr_status VARCHAR2(40);
1618 l_instr_set_id NUMBER(22):=2641;
1619 l_erecID NUMBER(22);
1620 l_orig_source varchar2(40);
1621 l_orig_sourceID number(22);
1622 l_batchprogression_status VARCHAR2(200) := NULL;
1623 l_batchprogression_curr_status VARCHAR2(200) := NULL;
1624 l_event_key varchar2(240);
1625 l_event_name VARCHAR2(240);
1626 l_return_status varchar2(10);
1627 CURSOR get_pending_instr_curr (p_event_name VARCHAR2,p_event_key VARCHAR2) is
1628 select STATUS
1629 from gmo_batch_progression
1630 where event =p_event_name
1631 and EVENT_KEY=p_event_key
1632 and STATUS = 'INPROGRESS' ;
1633 BEGIN
1634
1635 l_return_status:=wf_rule.setParametersIntoParameterList(p_subscription_guid,p_event);
1636 l_event_name:=p_event.getEventName();
1637 l_event_key:=p_event.getEventKey();
1638 l_xml_map_code := NVL(wf_event.getValueForParameter('GMO_XML_MAP_CODE',p_event.Parameter_List),
1639 P_EVENT.getEventName( ));
1640 -- Get the Instruction Set XML
1641 GET_INSTR_XML(l_event_key,l_xml_map_code,l_result);
1642 l_entity_name := getValue(l_result,'//ENTITY_NAME');
1643 l_entity_key := getValue(l_result,'//ENTITY_KEY');
1644 l_instr_type := getValue(l_result,'//INSTRUCTION_TYPE');
1645 l_orig_source := getValue(l_result,'//ORIG_SOURCE');
1646 l_orig_sourceID := getValue(l_result,'//ORIG_SOURCE_ID');
1647 l_instr_status := getValue(l_result,'//INSTR_SET_STATUS');
1648
1649 IF(l_instr_status = GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE OR l_instr_status = GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL) THEN
1650 l_batchprogression_status := 'COMPLETE';
1651 ELSE
1652 l_batchprogression_status := 'INPROGRESS';
1653 END IF;
1654
1655
1656 IF(l_orig_source = GMO_CONSTANTS_GRP.G_ORIG_SOURCE_DEFN) THEN
1657 PROCESS_INSTR_XML(l_entity_name,l_entity_key,l_event_key,l_instr_type,l_instr_status,l_batchprogression_status,l_event_name,l_result);
1658 ELSIF(l_orig_source = GMO_CONSTANTS_GRP.G_ORIG_SOURCE_INSTANCE) THEN
1659 -- process Instruction with new INSTR Set ID
1660 PROCESS_INSTR_XML(l_entity_name,l_entity_key,l_event_key,l_instr_type,l_instr_status,l_batchprogression_status,l_event_name,l_result);
1661 -- process Instruction with old INSTR Set ID
1662 l_event_key := l_orig_sourceID;
1663 open get_pending_instr_curr(l_event_name,l_event_key);
1664 loop
1665 fetch get_pending_instr_curr into l_batchprogression_curr_status;
1666 exit when get_pending_instr_curr%NOTFOUND;
1667 -- Get the Instruction Set XML for Old Set ID
1668 GET_INSTR_XML(l_event_key,l_xml_map_code,l_result);
1669 l_batchprogression_status := 'COMPLETE';
1670 PROCESS_INSTR_XML(l_entity_name,l_entity_key,l_event_key,l_instr_type,l_instr_status,l_batchprogression_status,l_event_name,l_result);
1671 END LOOP;
1672 END IF;
1673 return 'SUCCESS';
1674 END PROCESS_INSTANCE_INSTR_SET;
1675
1676 PROCEDURE DELETE_PROGRESSION_ROW (P_BATCH_PROGRESSION_ID NUMBER DEFAULT Null,
1677 P_ERECORD_ID NUMBER DEFAULT Null,
1678 P_EVENT VARCHAR2 DEFAULT Null,
1679 P_EVENT_KEY VARCHAR2 DEFAULT Null,
1680 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1681 X_MSG_COUNT OUT NOCOPY NUMBER,
1682 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1683 BEGIN
1684
1685 IF P_BATCH_PROGRESSION_ID IS NOT NULL
1686 THEN
1687 DELETE GMO_BATCH_PROGRESSION
1688 WHERE PROGRESSION_ID = P_BATCH_PROGRESSION_ID;
1689 X_RETURN_STATUS := 'S';
1690 ELSIF P_ERECORD_ID IS NOT NULL
1691 THEN
1692 DELETE GMO_BATCH_PROGRESSION
1693 WHERE ERECORD_ID = P_ERECORD_ID;
1694 X_RETURN_STATUS := 'S';
1695 ELSIF ((P_EVENT IS NOT NULL) and (P_EVENT_KEY IS NOT NULL))
1696 THEN
1697 DELETE GMO_BATCH_PROGRESSION
1698 WHERE EVENT = P_EVENT
1699 AND EVENT_KEY = P_EVENT_KEY;
1700 X_RETURN_STATUS := 'S';
1701 ELSE
1702 FND_MESSAGE.SET_NAME('GMO','GMO_DEL_PROG_ROW_PARAMETER_ERR');
1703 FND_MSG_PUB.ADD;
1704 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1705 X_RETURN_STATUS := 'E';
1706 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1707 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1708 'gmo.plsql.GMO_CBR_GRP.DELETE_PROGRESSION_ROW',
1709 FALSE
1710 );
1711 end if;
1712 END IF;
1713
1714 END DELETE_PROGRESSION_ROW;
1715
1716
1717
1718 END GMO_CBR_GRP;