[Home] [Help]
PACKAGE BODY: APPS.GMO_CBR_GRP
Source
1 PACKAGE BODY GMO_CBR_GRP AS
2 /* $Header: GMOGCBRB.pls 120.21 2006/09/22 12:29:56 rvsingh noship $ */
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(80) := NULL;
634 l_event_key varchar2(80) := NULL;
635 l_user_key_label varchar2(80) := NULL;
636 l_user_key_value varchar2(80) := 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(80) := NULL;
659 l_parent_key_node VARCHAR2(80) := NULL;
660 l_parent_key_val VARCHAR2(80) := NULL;
661 l_curr_node_key VARCHAR2(80) := NULL;
662 l_curr_key_val VARCHAR2(80) := NULL;
663 l_xml_erec CLOB := NULL;
664 l_entity_name VARCHAR2(40) := 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 cursor cur_qa_results IS
671 SELECT PROCESS_BATCH_ID
672 ,PROCESS_BATCHSTEP_ID
673 ,PROCESS_OPERATION_ID
674 ,PROCESS_ACTIVITY_ID
675 ,PROCESS_RESOURCE_ID
676 ,PROCESS_PARAMETER_ID
677 FROM QA_RESULTS
678 WHERE
679 plan_id = l_plan_id
680 AND collection_id = l_collection_id
681 AND occurrence = l_occurrence ;
682 CURSOR get_collection_id IS
683 SELECT substrb(l_event_key,instrb(l_event_key,'-',1,1)+1,
684 decode(instrb(l_event_key,'-',1,2),0,
685 length(substrb(l_event_key,instrb(l_event_key,'-',1,1)+1)),
686 instrb(l_event_key,'-',1,2)-(instrb(l_event_key,'-',1,1)+1)))
687 FROM DUAL;
688 CURSOR GET_SAMPLE_DETAILS IS
689 SELECT SMPL.BATCH_ID,SMPL.STEP_ID,SMPL.FORMULALINE_ID
690 ,SMPL.MATERIAL_DETAIL_ID
691 FROM GMD_SAMPLES SMPL
692 WHERE SMPL.SAMPLE_ID = l_sample_id;
693
694 BEGIN
695 l_return_status:=wf_rule.setParametersIntoParameterList(p_subscription_guid,p_event);
696 --
697 -- Get parameters from ef_event structure for processing
698 --
699 l_event_name := p_event.getEventName();
700 l_event_key := p_event.getEventKey();
701 l_user_key_label := wf_event.getValueForParameter('PSIG_USER_KEY_LABEL',p_event.Parameter_List);
702 l_user_key_value := wf_event.getValueForParameter('PSIG_USER_KEY_VALUE',p_event.Parameter_List);
703 l_erecord_id := wf_event.getValueForParameter('#ERECORD_ID',p_event.Parameter_List);
704 l_map_code := wf_event.getValueForParameter('CBR_XML_MAP_CODE',p_event.Parameter_List);
705 l_compare_XML := nvl(wf_event.getValueForParameter('COMPARE_XML',p_event.Parameter_List),'Y');
706
707 --
708 -- process Oracle Quality Events
709 --
710 IF l_event_name in ('oracle.apps.qa.disp.create'
711 ,'oracle.apps.qa.disp.detail.approve'
712 ,'oracle.apps.qa.disp.header.approve'
713 ,'oracle.apps.qa.disp.update'
714 ,'oracle.apps.qa.ncm.create'
715 ,'oracle.apps.qa.ncm.detail.approve'
716 ,'oracle.apps.qa.ncm.master.approve'
717 ,'oracle.apps.qa.ncm.update')
718 THEN
719 l_plan_id := substrb(l_event_key,1,instrb(l_event_key,'-',1,1)-1);
720 --
721 -- get collection id from event key. We need to use SQL as decode is not allowed
722 -- other than SQL
723 --
724 OPEN get_collection_id;
725 FETCH get_collection_id INTO l_collection_id;
726 CLOSE get_collection_id;
727 l_occurrence := substrb(l_event_key,instrb(l_event_key,'-',1,2)+1);
728 --
729 -- check current NCM is related to production batch
730 -- if current is not belogns to production batch then
731 -- we do not require to process
732 --
733 OPEN cur_qa_results;
734 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;
735 CLOSE cur_qa_results;
736 IF l_batch_id is null THEN
737 RETURN 'SUCCESS';
738 END IF;
739 l_message_prod := 'GMO';
740 l_message_token:= 'GMO_QA_ERES_KEY_LABEL';
741 l_include_in_CBR := 'XTEXT';
742
743 ELSIf l_event_name in ('oracle.apps.gme.batch.erecords'
744 ,'oracle.apps.gme.batch.complete'
745 ,'oracle.apps.gme.batch.uncertify'
746 ,'oracle.apps.gme.batch.unrelease'
747 ,'oracle.apps.gme.batch.close'
748 ,'oracle.apps.gme.batch.reopen'
749 ,'oracle.apps.gme.batch.release'
750 ,'oracle.apps.gme.batch.terminate'
751 ,'oracle.apps.gme.batch.transact'
752 ,'oracle.apps.gme.batch.scale'
753 ,'oracle.apps.gme.batch.rsrc.trx'
754 ,'oracle.apps.gme.batch.cancel'
755 ,'oracle.apps.gme.batch.reroute'
756 ,'oracle.apps.gme.batch.rescheduled'
757 ,'oracle.apps.gme.batch.update')
758 THEN
759 --
760 -- process batch level Events
761 --
762 l_batch_id := l_event_key;
763 l_message_prod := 'GME';
764 l_message_token:= 'GME_PSIG_BATCH_LABEL';
765 SELECT PLAN_START_DATE into l_plan_start_date
766 FROM GME_BATCH_HEADER
767 WHERE batch_id = l_batch_id;
768 --
769 -- If event is batch creation event then store
770 -- e-record XML into CBR XML
771 --
772 IF l_event_name = 'oracle.apps.gme.batch.erecords' THEN
773 insert into GMO_CBR_XML (batch_id, CBR_XML)
774 VALUES (l_batch_id,p_event.geteventdata());
775 INSERT_BATH_EVENTS(l_batch_id);
776 END IF;
777 ELSIF l_event_name in ('oracle.apps.gme.batchstep.complete'
778 ,'oracle.apps.gme.batchstep.release'
779 ,'oracle.apps.gme.batchstep.uncertify'
780 ,'oracle.apps.gme.batchstep.unrelease'
781 ,'oracle.apps.gme.batchstep.close'
782 ,'oracle.apps.gme.batchstep.reopen') THEN
783 --
784 -- process batch step events
785 --
786 l_batchstep_id := l_event_key;
787 l_message_prod := 'GME';
788 l_message_token:= 'GME_PSIG_BATCH_STEP_LABEL';
789 SELECT batch_id,PLAN_START_DATE into l_batch_id,l_plan_start_date
790 FROM GME_BATCH_STEPS
791 WHERE batchstep_id = l_batchstep_id;
792 ELSIF l_event_name in ('oracle.apps.gme.batchstep.added'
793 ,'oracle.apps.gme.batchstep.removed'
794 ,'oracle.apps.gme.batchstep.update') THEN
795 --
796 -- process batch step events
797 --
798 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
799 l_batchstep_id := substrb(l_event_key,instrb(l_event_key,'-',1)+1);
800 l_message_prod := 'GME';
801 l_message_token:= 'GME_PSIG_BATCH_STEP_LABEL';
802 IF l_event_name in ('oracle.apps.gme.batchstep.added') THEN
803 SELECT PLAN_START_DATE into l_plan_start_date
804 FROM GME_BATCH_STEPS
805 WHERE batch_id = l_batch_id
806 AND batchstep_id = l_batchstep_id;
807 END IF;
808 ELSIF l_event_name in ('oracle.apps.gme.batchmtl.added'
809 ,'oracle.apps.gme.batchmtl.removed'
810 ,'oracle.apps.gme.batchmtl.updated') THEN
811 --
812 -- process batch material events
813 --
814 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
815 l_material_detail_id := substrb(l_event_key,instrb(l_event_key,'-',1)+1);
816 l_message_prod := 'GME';
817 l_message_token:= 'GME_PSIG_BATCH_MATL_LABEL';
818
819 ELSIF l_event_name in ('oracle.apps.gme.resource.added'
820 ,'oracle.apps.gme.resource.removed'
821 ,'oracle.apps.gme.resource.update') THEN
822 --
823 -- process batch step resource events
824 --
825 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
826 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));
827 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));
828 l_batch_rsrc_id := substrb(l_event_key,instrb(l_event_key,'-',1,3)+1);
829 l_message_prod := 'GME';
830 l_message_token:= 'GME_PSIG_BATCH_STEP_RSRC_LABEL';
831 IF l_event_name = 'oracle.apps.gme.resource.added'
832 THEN
833 SELECT PLAN_START_DATE into l_plan_start_date
834 FROM GME_BATCH_STEP_RESOURCES
835 WHERE batch_id = l_batch_id
836 AND BATCHSTEP_RESOURCE_ID = l_batch_rsrc_id;
837 END IF;
838 ELSIF l_event_name in ('oracle.apps.gme.activity.added'
839 ,'oracle.apps.gme.activity.removed'
840 ,'oracle.apps.gme.activity.update') THEN
841 --
842 -- process batch step activity events
843 --
844 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
845 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));
846 l_batch_activity_id := substrb(l_event_key,instrb(l_event_key,'-',1,2)+1);
847 l_message_prod := 'GME';
848 l_message_token:= 'GME_PSIG_BATCH_STEP_ACT_LABEL';
849 IF l_event_name = 'oracle.apps.gme.activity.added' THEN
850 SELECT PLAN_START_DATE into l_plan_start_date
851 FROM GME_BATCH_STEP_ACTIVITIES
852 WHERE batch_id = l_batch_id
853 AND BATCHSTEP_ACTIVITY_ID = l_batch_activity_id;
854 END IF;
855 ELSIF l_event_name in ('oracle.apps.gme.batch.pparam') THEN
856 l_batch_id := substrb(l_event_key,1,instrb(l_event_key,'-',1)-1);
857 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));
858 l_message_prod := 'GME';
859 l_message_token:= 'GME_PSIG_BATCH_RSRC_PARM_LABEL';
860 ELSIF l_event_name in ('oracle.apps.gmo.labelprint') THEN
861 SELECT entity_name, entity_key INTO l_entity_name,l_dispense_id
862 from gmo_label_history
863 where label_id = l_event_key;
864 IF l_entity_name = 'GMO_DISPENSING'
865 THEN
866 l_message_prod := 'GMO';
867 l_message_token := 'GMO_PRNTLBL_KEY_LABEL';
868 l_parent_node := 'MATERIAL_DISPENSE';
869 l_parent_key_node := 'DISPENSE_ID';
870 l_parent_key_val := l_dispense_id;
871 l_curr_node_key := 'LABEL_ID';
872 l_curr_key_val := l_event_key;
873 ELSE
874 return 'SUCCESS';
875 END IF;
876 ELSIF l_event_name in ('oracle.apps.gmo.mtl.dispense') THEN
877 l_dispense_id := l_event_key;
878 l_message_prod := 'GMO';
879 l_message_token := 'GMO_DISP_MTL_DISP_KEY_LABEL';
880 ELSIF l_event_name in ('oracle.apps.gmo.mtl.revdisp') THEN
881 l_transaction_id := l_event_key;
882 l_message_prod := 'GMO';
883 l_message_token := 'GMO_DISP_RVDISP_KEY_LABEL';
884 ELSIF l_event_name in ('oracle.apps.gmd.qm.smpl.crea',
885 'oracle.apps.gmd.qm.rslt.entry' ) THEN
886 if l_event_name = 'oracle.apps.gmd.qm.smpl.crea'
887 then
888 l_sample_id := l_event_key;
889 else
890 l_sample_id := substrb(l_event_key,1,instrb(l_event_key,'-',1,1) -1);
891 end if;
892 OPEN GET_SAMPLE_DETAILS;
893 FETCH GET_SAMPLE_DETAILS INTO l_batch_id, l_batchstep_id ,l_line_id,l_material_detail_id;
894 CLOSE GET_SAMPLE_DETAILS;
895 if l_batch_id is null
896 then
897 return 'SUCCESS';
898 ELSE
899 if l_event_name = 'oracle.apps.gmd.qm.smpl.crea'
900 then
901 l_message_prod := 'GMD';
902 l_message_token := 'GMD_ERES_SAMPLE_LBL';
903 if l_material_detail_id is not null
904 then
905 l_parent_node := 'MATERIAL_REQUIREMENTS';
906 l_parent_key_node := 'MATERIAL_DETAIL_ID';
907 l_parent_key_val := l_material_detail_id;
908 l_curr_node_key := 'SAMPLE_ID';
909 l_curr_key_val := l_sample_id;
910 elsif l_batchstep_id is not null
911 then
912 l_parent_node := 'ROUTING_STEPS';
913 l_parent_key_node := 'STEP_ID';
914 l_parent_key_val := l_batchstep_id;
915 l_curr_node_key := 'SAMPLE_ID';
916 l_curr_key_val := l_sample_id;
917 elsif l_batch_id is not null
918 then
919 l_parent_node := 'HEADER_INFORMATION';
920 l_parent_key_node := 'BATCH_ID';
921 l_parent_key_val := l_batch_id;
922 l_curr_node_key := 'SAMPLE_ID';
923 l_curr_key_val := l_sample_id;
924 end if;
925 else
926 l_message_prod := 'GMD';
927 l_message_token := 'GMD_ERES_RESULT_LBL';
928 end if;
929 END IF;
930 END IF;
931 --
932 -- Manage events created in batch progression at the time of batch creation
933 --
934 If l_event_name in ('oracle.apps.gme.batch.complete'
935 ,'oracle.apps.gme.batch.close'
936 ,'oracle.apps.gme.batch.release'
937 ,'oracle.apps.gme.batchstep.complete'
938 ,'oracle.apps.gme.batchstep.release'
939 ,'oracle.apps.gme.batchstep.close')
940 THEN
941 /* delete progression row created when batch is created */
942 delete GMO_BATCH_PROGRESSION
943 where event = l_event_name
944 AND event_key = l_event_key
945 AND STATUS = 'PENDING';
946 END IF;
947
948 /* update the progression status for other event */
949 UPDATE_PROGRESSION_STATUS(l_batch_id,l_batchstep_id,l_event_name);
950
951 /* populate Record object with the details */
952
953 l_batch_prog_rec.PROGRESSION_ID := NULL;
954 l_batch_prog_rec.BATCH_ID := l_batch_id;
955 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id ;
956 l_batch_prog_rec.MATERIAL_LINE_ID := l_material_detail_id;
957 l_batch_prog_rec.BATCHACTIVITY_ID := l_batch_activity_id;
958 l_batch_prog_rec.BATCHRSRC_ID :=l_batch_rsrc_id ;
959 l_batch_prog_rec.DISPENSE_ID := l_dispense_id;
960 l_batch_prog_rec.SAMPLE_ID := l_sample_id;
961 l_batch_prog_rec.DEVIATION_ID := l_deviation_id;
962 l_batch_prog_rec.TRANSACTION_ID := l_transaction_id;
963 l_batch_prog_rec.PARENT_NODE := l_parent_node;
964 l_batch_prog_rec.PARENT_KEY_NODE := l_parent_key_node;
965 l_batch_prog_rec.PARENT_KEY_VAL := l_parent_key_val;
966 l_batch_prog_rec.CURR_NODE_KEY := l_curr_node_key;
967 l_batch_prog_rec.CURR_KEY_VAL := l_curr_key_val;
968 l_batch_prog_rec.USER_KEY_LABEL_PROD := l_message_prod;
969 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := l_message_token;
970 l_batch_prog_rec.USER_KEY_LABEL := l_user_key_label;
971 l_batch_prog_rec.USER_KEY_VALUE := l_user_key_value;
972 l_batch_prog_rec.EXTERNAL_EVENT := l_external_event;
973 l_batch_prog_rec.INCLUDE_IN_CBR := l_include_in_CBR;
974 l_batch_prog_rec.EVENT := l_event_name;
975 l_batch_prog_rec.EVENT_KEY := l_event_key;
976 l_batch_prog_rec.EVENT_DATE := SYSDATE;
977 l_batch_prog_rec.PLANED_START_DATE := l_plan_start_date;
978 l_batch_prog_rec.ERECORD_ID := l_erecord_id;
979 l_batch_prog_rec.XML_EREC := l_xml_erec;
980 IF l_event_name = 'oracle.apps.gme.batch.erecords' THEN
981 l_batch_prog_rec.STATUS := 'COMPLETE';
982 l_batch_prog_rec.INCLUDED_IN_CBR := 'Y';
983 ELSE
984 l_batch_prog_rec.STATUS := 'INPROGRESS';
985 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
986 END IF;
987 l_batch_prog_rec.XML_MAP_CODE := l_map_code;
988 l_batch_prog_rec.compare_xml:=l_compare_XML;
989 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
990 wf_event.AddParameterToList('BATCH_PROGRESSION_ID', l_batch_progression_id,p_event.Parameter_List);
991 return 'SUCCESS';
992 END;
993
994
995 PROCEDURE INSERT_BATH_EVENTS(P_BATCH_ID IN NUMBER) IS
996 l_batch_prog_rec GMO_BATCH_PROGRESSION%ROWTYPE;
997 l_batch_progression_id NUMBER;
998 l_step_label VARCHAR2(400);
999 l_step_user_val VARCHAR2(400);
1000 CURSOR Get_BATCH_DETAILS IS
1001 SELECT BATCH_ID,BATCH_NO,PLANT_CODE,PLAN_START_DATE,PLAN_CMPLT_DATE
1002 FROM GME_BATCH_HEADER_VW
1003 WHERE batch_id = P_BATCH_ID;
1004 CURSOR GET_STEP_DETAILS IS
1005 SELECT BATCH_ID,BATCHSTEP_ID,BATCHSTEP_NO,OPERATION_NO,PLAN_START_DATE,PLAN_CMPLT_DATE
1006 FROM gme_batch_steps_v
1007 WHERE BATCH_ID = P_BATCH_ID;
1008 BATCH_DETAILS_REC Get_BATCH_DETAILS%ROWTYPE;
1009 STEP_DETAILS_REC GET_STEP_DETAILS%ROWTYPE;
1010 BEGIN
1011 --
1012 -- Create Batch progression rows when batch is created.
1013 -- oracle.apps.gme.batch.complete
1014 -- oracle.apps.gme.batch.close
1015 -- oracle.apps.gme.batch.release
1016 -- create step level rows for each step.
1017 -- oracle.apps.gme.batchstep.complete
1018 -- oracle.apps.gme.batchstep.release
1019 -- oracle.apps.gme.batchstep.close
1020 --
1021 OPEN Get_BATCH_DETAILS;
1022 FETCH Get_BATCH_DETAILS INTO BATCH_DETAILS_REC;
1023 CLOSE Get_BATCH_DETAILS;
1024 l_batch_prog_rec.PROGRESSION_ID := NULL;
1025 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1026 l_batch_prog_rec.BATCHSTEP_ID := NULL;
1027 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1028 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1029 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1030 l_batch_prog_rec.DISPENSE_ID := NULL;
1031 l_batch_prog_rec.SAMPLE_ID := NULL;
1032 l_batch_prog_rec.DEVIATION_ID := NULL;
1033 l_batch_prog_rec.TRANSACTION_ID := NULL;
1034 l_batch_prog_rec.PARENT_NODE := NULL;
1035 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1036 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1037 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1038 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1039 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1040 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_LABEL';
1041 l_batch_prog_rec.USER_KEY_LABEL := FND_MESSAGE.GET_STRING('GME','GME_PSIG_BATCH_LABEL');
1042 l_batch_prog_rec.USER_KEY_VALUE := BATCH_DETAILS_REC.PLANT_CODE || '-'||BATCH_DETAILS_REC.BATCH_NO;
1043 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1044 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1045 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batch.release';
1046 l_batch_prog_rec.EVENT_KEY := P_BATCH_ID;
1047 l_batch_prog_rec.EVENT_DATE := NULL;
1048 l_batch_prog_rec.PLANED_START_DATE := BATCH_DETAILS_REC.PLAN_START_DATE;
1049 l_batch_prog_rec.ERECORD_ID := NULL;
1050 l_batch_prog_rec.XML_EREC := NULL;
1051 l_batch_prog_rec.STATUS := 'PENDING';
1052 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1053 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1054 OPEN GET_STEP_DETAILS ;
1055 LOOP
1056 FETCH GET_STEP_DETAILS INTO STEP_DETAILS_REC;
1057 EXIT WHEN GET_STEP_DETAILS%NOTFOUND;
1058 l_step_label := FND_MESSAGE.GET_STRING('GME','GME_PSIG_BATCH_STEP_LABEL');
1059 l_step_user_val := BATCH_DETAILS_REC.PLANT_CODE || '-'||BATCH_DETAILS_REC.BATCH_NO||
1060 '-' ||STEP_DETAILS_REC.BATCHSTEP_NO||'-'||STEP_DETAILS_REC.OPERATION_NO;
1061 l_batch_prog_rec.PROGRESSION_ID := NULL;
1062 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1063 l_batch_prog_rec.BATCHSTEP_ID := STEP_DETAILS_REC.BATCHSTEP_ID;
1064 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1065 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1066 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1067 l_batch_prog_rec.DISPENSE_ID := NULL;
1068 l_batch_prog_rec.SAMPLE_ID := NULL;
1069 l_batch_prog_rec.DEVIATION_ID := NULL;
1070 l_batch_prog_rec.TRANSACTION_ID := NULL;
1071 l_batch_prog_rec.PARENT_NODE := NULL;
1072 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1073 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1074 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1075 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1076 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1077 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_STEP_LABEL';
1078 l_batch_prog_rec.USER_KEY_LABEL := l_step_label;
1079 l_batch_prog_rec.USER_KEY_VALUE := l_step_user_val;
1080 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1081 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1082 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batchstep.release';
1083 l_batch_prog_rec.EVENT_DATE := NULL;
1084 l_batch_prog_rec.EVENT_KEY := STEP_DETAILS_REC.BATCHSTEP_ID ;
1085 l_batch_prog_rec.PLANED_START_DATE := STEP_DETAILS_REC.PLAN_START_DATE;
1086 l_batch_prog_rec.ERECORD_ID := NULL;
1087 l_batch_prog_rec.XML_EREC := NULL;
1088 l_batch_prog_rec.STATUS := 'PENDING';
1089 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1090 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1091 l_batch_prog_rec.PROGRESSION_ID := NULL;
1092 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1093 l_batch_prog_rec.BATCHSTEP_ID := STEP_DETAILS_REC.BATCHSTEP_ID;
1094 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1095 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1096 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1097 l_batch_prog_rec.DISPENSE_ID := NULL;
1098 l_batch_prog_rec.SAMPLE_ID := NULL;
1099 l_batch_prog_rec.DEVIATION_ID := NULL;
1100 l_batch_prog_rec.TRANSACTION_ID := NULL;
1101 l_batch_prog_rec.PARENT_NODE := NULL;
1102 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1103 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1104 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1105 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1106 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1107 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_STEP_LABEL';
1108 l_batch_prog_rec.USER_KEY_LABEL := l_step_label;
1109 l_batch_prog_rec.USER_KEY_VALUE :=l_step_user_val;
1110 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1111 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1112 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batchstep.complete';
1113 l_batch_prog_rec.EVENT_KEY := STEP_DETAILS_REC.BATCHSTEP_ID ;
1114 l_batch_prog_rec.EVENT_DATE := NULL;
1115 l_batch_prog_rec.PLANED_START_DATE := STEP_DETAILS_REC.PLAN_CMPLT_DATE;
1116 l_batch_prog_rec.ERECORD_ID := NULL;
1117 l_batch_prog_rec.XML_EREC := NULL;
1118 l_batch_prog_rec.STATUS := 'PENDING';
1119 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1120 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1121 l_batch_prog_rec.PROGRESSION_ID := NULL;
1122 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1123 l_batch_prog_rec.BATCHSTEP_ID := STEP_DETAILS_REC.BATCHSTEP_ID;
1124 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1125 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1126 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1127 l_batch_prog_rec.DISPENSE_ID := NULL;
1128 l_batch_prog_rec.SAMPLE_ID := NULL;
1129 l_batch_prog_rec.DEVIATION_ID := NULL;
1130 l_batch_prog_rec.TRANSACTION_ID := NULL;
1131 l_batch_prog_rec.PARENT_NODE := NULL;
1132 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1133 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1134 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1135 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1136 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1137 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_STEP_LABEL';
1138 l_batch_prog_rec.USER_KEY_LABEL := l_step_label;
1139 l_batch_prog_rec.USER_KEY_VALUE :=l_step_user_val;
1140 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1141 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1142 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batchstep.close';
1143 l_batch_prog_rec.EVENT_KEY := STEP_DETAILS_REC.BATCHSTEP_ID ;
1144 l_batch_prog_rec.EVENT_DATE := NULL;
1145 l_batch_prog_rec.PLANED_START_DATE := null;
1146 l_batch_prog_rec.ERECORD_ID := NULL;
1147 l_batch_prog_rec.XML_EREC := NULL;
1148 l_batch_prog_rec.STATUS := 'PENDING';
1149 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1150 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1151 END LOOP;
1152 CLOSE GET_STEP_DETAILS;
1153 l_batch_prog_rec.PROGRESSION_ID := NULL;
1154 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1155 l_batch_prog_rec.BATCHSTEP_ID := NULL;
1156 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1157 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1158 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1159 l_batch_prog_rec.DISPENSE_ID := NULL;
1160 l_batch_prog_rec.SAMPLE_ID := NULL;
1161 l_batch_prog_rec.DEVIATION_ID := NULL;
1162 l_batch_prog_rec.TRANSACTION_ID := NULL;
1163 l_batch_prog_rec.PARENT_NODE := NULL;
1164 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1165 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1166 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1167 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1168 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1169 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_LABEL';
1170 l_batch_prog_rec.USER_KEY_LABEL := FND_MESSAGE.GET_STRING('GME','GME_PSIG_BATCH_LABEL');
1171 l_batch_prog_rec.USER_KEY_VALUE := BATCH_DETAILS_REC.PLANT_CODE || '-'||BATCH_DETAILS_REC.BATCH_NO;
1172 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1173 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1174 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batch.complete';
1175 l_batch_prog_rec.EVENT_KEY := P_BATCH_ID;
1176 l_batch_prog_rec.EVENT_DATE := NULL;
1177 l_batch_prog_rec.PLANED_START_DATE := BATCH_DETAILS_REC.PLAN_CMPLT_DATE;
1178 l_batch_prog_rec.ERECORD_ID := NULL;
1179 l_batch_prog_rec.XML_EREC := NULL;
1180 l_batch_prog_rec.STATUS := 'PENDING';
1181 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1182 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1183 l_batch_prog_rec.PROGRESSION_ID := NULL;
1184 l_batch_prog_rec.BATCH_ID := P_BATCH_ID;
1185 l_batch_prog_rec.BATCHSTEP_ID := NULL;
1186 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1187 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1188 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1189 l_batch_prog_rec.DISPENSE_ID := NULL;
1190 l_batch_prog_rec.SAMPLE_ID := NULL;
1191 l_batch_prog_rec.DEVIATION_ID := NULL;
1192 l_batch_prog_rec.TRANSACTION_ID := NULL;
1193 l_batch_prog_rec.PARENT_NODE := NULL;
1194 l_batch_prog_rec.PARENT_KEY_NODE := NULL;
1195 l_batch_prog_rec.PARENT_KEY_VAL := NULL;
1196 l_batch_prog_rec.CURR_NODE_KEY := NULL;
1197 l_batch_prog_rec.CURR_KEY_VAL := NULL;
1198 l_batch_prog_rec.USER_KEY_LABEL_PROD := 'GME';
1199 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := 'GME_PSIG_BATCH_LABEL';
1200 l_batch_prog_rec.USER_KEY_LABEL := FND_MESSAGE.GET_STRING('GME','GME_PSIG_BATCH_LABEL');
1201 l_batch_prog_rec.USER_KEY_VALUE := BATCH_DETAILS_REC.PLANT_CODE || '-'||BATCH_DETAILS_REC.BATCH_NO;
1202 l_batch_prog_rec.EXTERNAL_EVENT := NULL;
1203 l_batch_prog_rec.INCLUDE_IN_CBR := NULL;
1204 l_batch_prog_rec.EVENT := 'oracle.apps.gme.batch.close';
1205 l_batch_prog_rec.EVENT_KEY := P_BATCH_ID;
1206 l_batch_prog_rec.EVENT_DATE := NULL;
1207 l_batch_prog_rec.PLANED_START_DATE := null;
1208 l_batch_prog_rec.ERECORD_ID := NULL;
1209 l_batch_prog_rec.XML_EREC := NULL;
1210 l_batch_prog_rec.STATUS := 'PENDING';
1211 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1212 l_batch_prog_rec.compare_xml:='N';
1213 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 null;
1217 END;
1218
1219 function getValue(P_XML XMLType,P_VARIABLE VARCHAR2) return VARCHAR2
1220 IS
1221 tmp varchar2(400);
1222 BEGIN
1223 select extractValue(P_XML,P_VARIABLE) into tmp from dual;
1224 return tmp;
1225 END getValue;
1226
1227
1228 PROCEDURE GET_INSTR_XML (P_EVENT_KEY IN VARCHAR2,P_MAP_CODE IN VARCHAR2,X_FINAL_XML OUT NOCOPY XMLType) IS
1229
1230 l_xml CLOB;
1231 l_final_xml CLOB;
1232 l_result XMLType;
1233 l_erecID NUMBER(22);
1234 --This holds the query context.
1235 qryCtx DBMS_XMLGEN.ctxHandle;
1236 l_srcDoc dbms_xmldom.DOMDocument;
1237 l_srcDocEle dbms_xmldom.DOMELEMENT;
1238 l_parentRootNode DBMS_XMLDOM.DOMNODE;
1239 l_sigHDRDoc DBMS_XMLDOM.DOMDOCUMENT;
1240 l_childRootNode DBMS_XMLDOM.DOMNODE;
1241 l_sigHDRDocEle dbms_xmldom.DOMELEMENT;
1242 l_childNodeList dbms_xmldom.DOMNodeList;
1243 l_childNode dbms_xmldom.DOMNode;
1244 l_nodeListDel dbms_xmldom.DOMNodeList;
1245 l_tmpnode dbms_xmldom.DOMNode;
1246 l_esigNode dbms_xmldom.DOMNode;
1247 l_ehdrNode dbms_xmldom.DOMNode;
1248 l_eleNodeList dbms_xmldom.DOMNodeList;
1249 l_eleNode dbms_xmldom.DOMNode;
1250 esigHdrFinalXML XMLType;
1251 l_debug_level number(2):=6;
1252 l_error_code pls_integer;
1253 l_log_file varchar2(2000);
1254 l_error_msg varchar2(2000);
1255 DB_TO_XML_ERROR EXCEPTION;
1256 BEGIN
1257
1258 ecx_outbound.getXML(i_map_code => P_MAP_CODE,
1259 i_document_id => P_EVENT_KEY,
1260 i_debug_level => l_debug_level,
1261 i_xmldoc => l_xml,
1262 i_ret_code => l_error_code,
1263 i_errbuf => l_error_msg,
1264 i_log_file => l_log_file);
1265
1266 --If the return code from ECX is a value other than 0 then
1267 --an error has occurred
1268 if(l_error_code <> 0) then
1269 raise DB_TO_XML_ERROR;
1270 end if;
1271
1272
1273
1274
1275 -- construct the XML Type object
1276 l_result := xmltype(l_xml);
1277
1278 l_srcDoc := dbms_xmldom.newDOMDocument(l_result);
1279 l_srcDocEle := dbms_xmldom.getDocumentElement(l_srcDoc);
1280 l_parentRootNode := DBMS_XMLDOM.makeNode(l_srcDocEle);
1281 l_eleNodeList := dbms_xmldom.getElementsByTagName(l_srcDocEle,'InstrErecID');
1282 For i in 0..dbms_xmldom.getLength(l_eleNodeList)-1 LOOP
1283 l_eleNode := dbms_xmldom.item(l_eleNodeList, i);
1284 -- import Erec Header node and signature node
1285 l_erecID := TO_NUMBER(DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(l_eleNode)));
1286 EDR_PSIG.GET_EVENT_XML(P_EVENT_NAME =>null,
1287 P_EVENT_KEY => null,
1288 P_ERECORD_ID => l_erecID,
1289 P_GET_ERECORD_XML =>'F',
1290 P_GET_PSIG_DETAILS =>'T',
1291 P_GET_ACKN_DETAILS =>'F',
1292 P_GET_PRINT_DETAILS =>'F',
1293 P_GET_RELATED_EREC_DETAILS =>'F',
1294 X_FINAL_XML => l_final_xml);
1295 esigHdrFinalXML := xmltype(l_final_xml);
1296
1297
1298 -- construct ESIG HDR Node
1299 l_sigHDRDoc := DBMS_XMLDOM.newDOMDocument(esigHdrFinalXML);
1300 -- make the Header Noe
1301 l_sigHDRDocEle := DBMS_XMLDOM.getDocumentElement(l_sigHDRDoc);
1302 l_childRootNode := DBMS_XMLDOM.makeNode(l_sigHDRDocEle);
1303
1304 -- get the Heder Details node
1305 l_childNodeList := dbms_xmldom.getElementsByTagName(l_sigHDRDocEle,'ERECORD_HEADER_DETAILS');
1306 l_ehdrNode := dbms_xmldom.item(l_childNodeList, 0);
1307 -- get the Node to be deleted
1308 l_nodeListDel := dbms_xmldom.getElementsByTagName(l_sigHDRDocEle,'DOC_PARAM_DETAILS');
1309 For i in 0..dbms_xmldom.getLength(l_nodeListDel)-1 LOOP
1310 l_tmpnode := dbms_xmldom.item(l_nodeListDel, i);
1311 l_childNode := dbms_xmldom.removeChild(l_ehdrNode,l_tmpnode);
1312 END LOOP;
1313
1314 -- get the Signature Details node
1315 l_childNodeList := dbms_xmldom.getElementsByTagName(l_sigHDRDocEle,'ERECORD_SIGNATURE_DETAILS');
1316 l_esigNode := dbms_xmldom.item(l_childNodeList, 0);
1317 -- get the Node to be deleted
1318 l_nodeListDel := dbms_xmldom.getElementsByTagName(l_sigHDRDocEle,'SIGNATURE_PARAMS');
1319 For i in 0..dbms_xmldom.getLength(l_nodeListDel)-1 LOOP
1320 l_tmpnode := dbms_xmldom.item(l_nodeListDel, i);
1321 l_childNode := dbms_xmldom.removeChild(l_esigNode,l_tmpnode);
1322 END LOOP;
1323 -- append Signature node into Header node
1324 if (dbms_xmldom.isNull(l_esigNode) = false) THEN
1325 l_tmpnode := dbms_xmldom.appendChild(l_ehdrNode,l_esigNode);
1326 END IF;
1327
1328 -- import the Hdr node in Source Document
1329 l_childRootNode := dbms_xmldom.importNode(l_srcDoc,l_childRootNode,TRUE);
1330 -- append the HDR node in Source XML
1331 l_parentRootNode := dbms_xmldom.appendChild(dbms_xmldom.getParentNode(l_eleNode),l_childRootNode);
1332 if (dbms_xmldom.isNull(l_sigHDRDoc) = false) THEN
1333 dbms_xmldom.freeDocument(l_sigHDRDoc);
1334 End IF;
1335 END LOOP;
1336
1337 X_FINAL_XML := l_result.extract('//INSTRUCTION_SET');
1338 if (dbms_xmldom.isNull(l_srcDoc) = false) THEN
1339 dbms_xmldom.freeDocument(l_srcDoc);
1340 End IF;
1341 EXCEPTION
1342 when DB_TO_XML_ERROR then
1343 FND_MESSAGE.SET_NAME('GMO','GMO_VALIDATE_XML_GEN_ERR');
1344 FND_MESSAGE.SET_TOKEN('OPERATION','DB to XML');
1345 FND_MESSAGE.SET_TOKEN('ERROR_DETAILS',l_error_msg);
1346 FND_MESSAGE.SET_TOKEN('LOG_DETAILS',l_log_file);
1347 APP_EXCEPTION.RAISE_EXCEPTION;
1348 END GET_INSTR_XML;
1349
1350 PROCEDURE PROCESS_INSTR_XML (p_entity_name VARCHAR2,p_entity_key VARCHAR2,p_event_key varchar2,p_instr_type VARCHAR2,
1351 p_instr_status VARCHAR2,P_BATCHPROGRESSION_STATUS VARCHAR2,P_EVENT_NAME VARCHAR2,P_FINAL_XML XMLType) IS
1352 l_batch_id number := null;
1353 l_batchstep_id number := null;
1354 l_material_detail_id number := null;
1355 l_batchstep_activity_id number := null;
1356 l_dispense_id NUMBER := NULL;
1357 l_parent_node VARCHAR2(80) := NULL;
1358 l_parent_key_node VARCHAR2(80) := NULL;
1359 l_parent_key_val VARCHAR2(80) := NULL;
1360 l_curr_node_key VARCHAR2(80) := NULL;
1361 l_curr_key_val VARCHAR2(80) := NULL;
1362 l_batch_prog_rec GMO_BATCH_PROGRESSION%ROWTYPE;
1363 l_batch_progression_id NUMBER := NULL;
1364 l_message_prod VARCHAR2(10) := 'GMO';
1365 l_message_token VARCHAR2(50) := NULL;
1366 l_user_key_label VARCHAR2(80) := NULL;
1367 l_user_key_value VARCHAR2(80) := p_entity_key;
1368
1369 l_event_data CLOB;
1370 l_MSG_COUNT NUMBER(22);
1371 l_MSG_DATA VARCHAR2(2000);
1372 CURSOR get_dispense_Details IS
1373 SELECT batch_id, batch_step_id ,material_detail_id
1374 FROM gmo_material_dispenses
1375 WHERE dispense_id = p_entity_key;
1376 CURSOR get_rev_dispense_Details IS
1377 SELECT batch_id, batch_step_id ,material_detail_id,dispense_id
1378 FROM gmo_material_undispenses
1379 WHERE undispense_id = p_entity_key;
1380 CURSOR get_resource_details IS
1381 SELECT BATCH_ID,BATCHSTEP_ID,BATCHSTEP_ACTIVITY_ID FROM GME_BATCH_STEP_RESOURCES
1382 where BATCHSTEP_RESOURCE_ID = p_entity_key;
1383 BEGIN
1384 delete GMO_BATCH_PROGRESSION
1385 where event = P_EVENT_NAME
1386 AND event_key = p_event_key
1387 AND STATUS = 'INPROGRESS';
1388
1389 select P_FINAL_XML.getCLobVal() into l_event_data from dual;
1390
1391 IF(p_instr_type = 'DISPENSE' and p_entity_name ='DISPENSE_ITEM') THEN
1392 OPEN get_dispense_Details;
1393 FETCH get_dispense_Details into l_batch_id, l_batchstep_id ,l_material_detail_id;
1394 CLOSE get_dispense_Details;
1395 l_message_token := 'GMO_ENTITY_DISPENSE_ITEM';
1396 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1397 l_batch_prog_rec.PROGRESSION_ID := NULL;
1398 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1399 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id;
1400 l_batch_prog_rec.MATERIAL_LINE_ID := l_material_detail_id;
1401 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1402 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1403 l_batch_prog_rec.DISPENSE_ID := p_entity_key;
1404 l_batch_prog_rec.SAMPLE_ID := NULL;
1405 l_batch_prog_rec.DEVIATION_ID := NULL;
1406 l_batch_prog_rec.TRANSACTION_ID := NULL;
1407 l_batch_prog_rec.PARENT_NODE := 'MATERIAL_DISPENSE';
1408 l_batch_prog_rec.PARENT_KEY_NODE := 'DISPENSE_ID';
1409 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1410 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1411 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1412 -- Bug 5231778 : change the Instr Type for Reverse Dispense.
1413 ELSIF(p_instr_type = 'REVERSE_DISPENSE' and p_entity_name ='DISPENSE_ITEM') THEN
1414 OPEN get_rev_dispense_Details;
1415 FETCH get_rev_dispense_Details into l_batch_id, l_batchstep_id ,l_material_detail_id,l_dispense_id;
1416 CLOSE get_rev_dispense_Details;
1417 l_message_token := 'GMO_ENTITY_REV_DISP_ITEM';
1418 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1419 l_batch_prog_rec.PROGRESSION_ID := NULL;
1420 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1421 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id;
1422 l_batch_prog_rec.MATERIAL_LINE_ID := l_material_detail_id;
1423 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1424 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1425 l_batch_prog_rec.DISPENSE_ID := p_entity_key;
1426 l_batch_prog_rec.SAMPLE_ID := NULL;
1427 l_batch_prog_rec.DEVIATION_ID := NULL;
1428 l_batch_prog_rec.TRANSACTION_ID := NULL;
1429 l_batch_prog_rec.PARENT_NODE := 'MATERIAL_REVERSE_DISPENSE';
1430 l_batch_prog_rec.PARENT_KEY_NODE := 'UNDISPENSE_ID';
1431 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1432 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1433 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1434 ELSIF(p_instr_type = 'PROCESS' and p_entity_name ='ACTIVITY') THEN
1435 SELECT BATCH_ID,BATCHSTEP_ID into l_batch_id,l_batchstep_id FROM GME_BATCH_STEP_ACTIVITIES
1436 WHERE batchstep_activity_id = p_entity_key;
1437 l_message_token := 'GMO_ENTITY_ACTIVITY';
1438 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1439 l_batch_prog_rec.PROGRESSION_ID := NULL;
1440 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1441 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id;
1442 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1443 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1444 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1445 l_batch_prog_rec.DISPENSE_ID := NULL;
1446 l_batch_prog_rec.SAMPLE_ID := NULL;
1447 l_batch_prog_rec.DEVIATION_ID := NULL;
1448 l_batch_prog_rec.TRANSACTION_ID := NULL;
1449 l_batch_prog_rec.PARENT_NODE := 'ACTIVITIES';
1450 l_batch_prog_rec.PARENT_KEY_NODE := 'STEP_ACTIVITY_ID';
1451 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1452 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1453 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1454 ELSIF(p_instr_type = 'PROCESS' and p_entity_name ='RESOURCE') THEN
1455 open get_resource_details;
1456 FETCH get_resource_details into l_batch_id,l_batchstep_id,l_batchstep_activity_id;
1457 CLOSE get_resource_details;
1458 l_message_token := 'GMO_ENTITY_RESOURCE';
1459 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1460 l_batch_prog_rec.PROGRESSION_ID := NULL;
1461 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1462 l_batch_prog_rec.BATCHSTEP_ID := l_batchstep_id;
1463 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1464 l_batch_prog_rec.BATCHACTIVITY_ID := l_batchstep_activity_id;
1465 l_batch_prog_rec.BATCHRSRC_ID := p_entity_key;
1466 l_batch_prog_rec.DISPENSE_ID := NULL;
1467 l_batch_prog_rec.SAMPLE_ID := NULL;
1468 l_batch_prog_rec.DEVIATION_ID := NULL;
1469 l_batch_prog_rec.TRANSACTION_ID := NULL;
1470 l_batch_prog_rec.PARENT_NODE := 'RESOURCE_REQUIREMENTS';
1471 l_batch_prog_rec.PARENT_KEY_NODE := 'STEP_RESOURCE_ID';
1472 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1473 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1474 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1475 ELSIF(p_instr_type = 'PROCESS' and p_entity_name ='MATERIAL') THEN
1476 select batch_id into l_batch_id from gme_material_details
1477 where material_detail_id=p_entity_key;
1478 l_message_token := 'GMO_ENTITY_MATERIAL';
1479 l_user_key_label := FND_MESSAGE.GET_STRING('GMO', l_message_token);
1480 l_batch_prog_rec.PROGRESSION_ID := NULL;
1481 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1482 l_batch_prog_rec.BATCHSTEP_ID := NULL;
1483 l_batch_prog_rec.MATERIAL_LINE_ID := p_entity_key;
1484 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1485 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1486 l_batch_prog_rec.DISPENSE_ID := NULL;
1487 l_batch_prog_rec.SAMPLE_ID := NULL;
1488 l_batch_prog_rec.DEVIATION_ID := NULL;
1489 l_batch_prog_rec.TRANSACTION_ID := NULL;
1490 l_batch_prog_rec.PARENT_NODE := 'MATERIAL_REQUIREMENTS';
1491 l_batch_prog_rec.PARENT_KEY_NODE := 'MATERIAL_DETAIL_ID';
1492 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1493 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1494 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1495 ELSIF(p_instr_type = 'PROCESS' and p_entity_name ='OPERATION') THEN
1496 SELECT batch_id into l_batch_id
1497 FROM GME_BATCH_STEPS
1498 WHERE batchstep_id = p_entity_key;
1499 l_message_token := 'GMO_ENTITY_OPERATION';
1500 l_user_key_label := FND_MESSAGE.GET_STRING('GMO',l_message_token);
1501 l_batch_prog_rec.PROGRESSION_ID := NULL;
1502 l_batch_prog_rec.BATCH_ID := l_batch_id ;
1503 l_batch_prog_rec.BATCHSTEP_ID := p_entity_key;
1504 l_batch_prog_rec.MATERIAL_LINE_ID := NULL;
1505 l_batch_prog_rec.BATCHACTIVITY_ID := NULL;
1506 l_batch_prog_rec.BATCHRSRC_ID := NULL;
1507 l_batch_prog_rec.DISPENSE_ID := NULL;
1508 l_batch_prog_rec.SAMPLE_ID := NULL;
1509 l_batch_prog_rec.DEVIATION_ID := NULL;
1510 l_batch_prog_rec.TRANSACTION_ID := NULL;
1511 l_batch_prog_rec.PARENT_NODE := 'ROUTING_STEPS';
1512 l_batch_prog_rec.PARENT_KEY_NODE := 'STEP_ID';
1513 l_batch_prog_rec.PARENT_KEY_VAL := p_entity_key;
1514 l_batch_prog_rec.CURR_NODE_KEY := 'INSTRUCTION_SET_ID';
1515 l_batch_prog_rec.CURR_KEY_VAL := p_event_key;
1516 END IF;
1517 l_batch_prog_rec.USER_KEY_LABEL_PROD := l_message_prod;
1518 l_batch_prog_rec.USER_KEY_LABEL_TOKEN := l_message_token;
1519 l_batch_prog_rec.USER_KEY_LABEL := l_user_key_label;
1520 l_batch_prog_rec.USER_KEY_VALUE := l_user_key_value;
1521 l_batch_prog_rec.EXTERNAL_EVENT := 'N';
1522 l_batch_prog_rec.INCLUDE_IN_CBR := 'XTEXT';
1523 l_batch_prog_rec.EVENT := P_EVENT_NAME;
1524 l_batch_prog_rec.EVENT_KEY := p_event_key;
1525 l_batch_prog_rec.EVENT_DATE := SYSDATE;
1526 l_batch_prog_rec.PLANED_START_DATE := null;
1527 l_batch_prog_rec.ERECORD_ID := NULL;
1528 l_batch_prog_rec.XML_EREC := l_event_data;
1529 l_batch_prog_rec.STATUS := P_BATCHPROGRESSION_STATUS;
1530 l_batch_prog_rec.INCLUDED_IN_CBR := 'N';
1531 l_batch_prog_rec.COMPARE_XML := 'N';
1532 gmo_cbr_grp.insert_event(l_batch_prog_rec,l_batch_progression_id);
1533
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 FND_MESSAGE.SET_NAME('GMO','FND_AS_UNEXPECTED_ERROR');
1537 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1538 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
1539 FND_MSG_PUB.ADD;
1540 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count, p_data => l_msg_data);
1541 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1542 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.GMO_CBR_GRP.PROCESS_INSTR_XML', FALSE);
1543 end if;
1544 END PROCESS_INSTR_XML;
1545
1546 FUNCTION PROCESS_INSTANCE_INSTR_SET(P_SUBSCRIPTION_GUID IN RAW, P_EVENT IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2 IS
1547 l_xml_map_code varchar2(240);
1548 l_result XMLType;
1549 l_temp XMLType;
1550 l_entity_name VARCHAR2(200);
1551 l_entity_key VARCHAR2(500);
1552 l_instr_type VARCHAR2(40);
1553 l_instr_status VARCHAR2(40);
1554 l_instr_set_id NUMBER(22):=2641;
1555 l_erecID NUMBER(22);
1556 l_orig_source varchar2(40);
1557 l_orig_sourceID number(22);
1558 l_batchprogression_status VARCHAR2(200) := NULL;
1559 l_batchprogression_curr_status VARCHAR2(200) := NULL;
1560 l_event_key varchar2(240);
1561 l_event_name VARCHAR2(240);
1562 l_return_status varchar2(10);
1563 CURSOR get_pending_instr_curr (p_event_name VARCHAR2,p_event_key VARCHAR2) is
1564 select STATUS
1565 from gmo_batch_progression
1566 where event =p_event_name
1567 and EVENT_KEY=p_event_key
1568 and STATUS = 'INPROGRESS' ;
1569 BEGIN
1570
1571 l_return_status:=wf_rule.setParametersIntoParameterList(p_subscription_guid,p_event);
1572 l_event_name:=p_event.getEventName();
1573 l_event_key:=p_event.getEventKey();
1574 l_xml_map_code := NVL(wf_event.getValueForParameter('GMO_XML_MAP_CODE',p_event.Parameter_List),
1575 P_EVENT.getEventName( ));
1576 -- Get the Instruction Set XML
1577 GET_INSTR_XML(l_event_key,l_xml_map_code,l_result);
1578 l_entity_name := getValue(l_result,'//ENTITY_NAME');
1579 l_entity_key := getValue(l_result,'//ENTITY_KEY');
1580 l_instr_type := getValue(l_result,'//INSTRUCTION_TYPE');
1581 l_orig_source := getValue(l_result,'//ORIG_SOURCE');
1582 l_orig_sourceID := getValue(l_result,'//ORIG_SOURCE_ID');
1583 l_instr_status := getValue(l_result,'//INSTR_SET_STATUS');
1584
1585 IF(l_instr_status = GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE OR l_instr_status = GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL) THEN
1586 l_batchprogression_status := 'COMPLETE';
1587 ELSE
1588 l_batchprogression_status := 'INPROGRESS';
1589 END IF;
1590
1591
1592 IF(l_orig_source = GMO_CONSTANTS_GRP.G_ORIG_SOURCE_DEFN) THEN
1593 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);
1594 ELSIF(l_orig_source = GMO_CONSTANTS_GRP.G_ORIG_SOURCE_INSTANCE) THEN
1595 -- process Instruction with new INSTR Set ID
1596 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);
1597 -- process Instruction with old INSTR Set ID
1598 l_event_key := l_orig_sourceID;
1599 open get_pending_instr_curr(l_event_name,l_event_key);
1600 loop
1601 fetch get_pending_instr_curr into l_batchprogression_curr_status;
1602 exit when get_pending_instr_curr%NOTFOUND;
1603 -- Get the Instruction Set XML for Old Set ID
1604 GET_INSTR_XML(l_event_key,l_xml_map_code,l_result);
1605 l_batchprogression_status := 'COMPLETE';
1606 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);
1607 END LOOP;
1608 END IF;
1609 return 'SUCCESS';
1610 END PROCESS_INSTANCE_INSTR_SET;
1611
1612 PROCEDURE DELETE_PROGRESSION_ROW (P_BATCH_PROGRESSION_ID NUMBER DEFAULT Null,
1613 P_ERECORD_ID NUMBER DEFAULT Null,
1614 P_EVENT VARCHAR2 DEFAULT Null,
1615 P_EVENT_KEY VARCHAR2 DEFAULT Null,
1616 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1617 X_MSG_COUNT OUT NOCOPY NUMBER,
1618 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1619 BEGIN
1620
1621 IF P_BATCH_PROGRESSION_ID IS NOT NULL
1622 THEN
1623 DELETE GMO_BATCH_PROGRESSION
1624 WHERE PROGRESSION_ID = P_BATCH_PROGRESSION_ID;
1625 X_RETURN_STATUS := 'S';
1626 ELSIF P_ERECORD_ID IS NOT NULL
1627 THEN
1628 DELETE GMO_BATCH_PROGRESSION
1629 WHERE ERECORD_ID = P_ERECORD_ID;
1630 X_RETURN_STATUS := 'S';
1631 ELSIF ((P_EVENT IS NOT NULL) and (P_EVENT_KEY IS NOT NULL))
1632 THEN
1633 DELETE GMO_BATCH_PROGRESSION
1634 WHERE EVENT = P_EVENT
1635 AND EVENT_KEY = P_EVENT_KEY;
1636 X_RETURN_STATUS := 'S';
1637 ELSE
1638 FND_MESSAGE.SET_NAME('GMO','GMO_DEL_PROG_ROW_PARAMETER_ERR');
1639 FND_MSG_PUB.ADD;
1640 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1641 X_RETURN_STATUS := 'E';
1642 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1643 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1644 'gmo.plsql.GMO_CBR_GRP.DELETE_PROGRESSION_ROW',
1645 FALSE
1646 );
1647 end if;
1648 END IF;
1649
1650 END DELETE_PROGRESSION_ROW;
1651
1652
1653
1654 END GMO_CBR_GRP;