[Home] [Help]
PACKAGE BODY: APPS.GMO_INSTRUCTION_PVT
Source
1 PACKAGE BODY GMO_INSTRUCTION_PVT AS
2 /* $Header: GMOVINTB.pls 120.27.12000000.3 2007/03/13 06:32:02 rvsingh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(40) := 'GMO_INSTRUCTION_PVT';
5
6 --Bug 4730261: start
7 --This is an internal procedure to raise instruction set event
8 --for CBR implementation.
9 PROCEDURE RAISE_INSTR_SET_EVENT
10 (
11 P_INSTRUCTION_SET_ID NUMBER
12 )
13 IS
14 l_event_name VARCHAR2(100) := 'oracle.apps.gmo.instrset.update';
15 l_event_key VARCHAR2(100);
16 l_event_data clob default NULL;
17 l_param_table FND_WF_EVENT.Param_Table;
18 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
19
20 l_entity_key varchar2(500);
21 cursor c_get_entity_key is select entity_key from gmo_instr_set_instance_b where instruction_set_id = P_INSTRUCTION_SET_ID;
22
23 BEGIN
24
25 if (P_INSTRUCTION_SET_ID is not null and P_INSTRUCTION_SET_ID <> -1 ) THEN
26 open c_get_entity_key;
27 fetch c_get_entity_key into l_entity_key;
28 close c_get_entity_key;
29
30 --Bug 5224619: start
31 --we should not raise the event when the event key is not set
32 --and pi internal value is being used as entity key.
33 if (instr (l_entity_key, GMO_CONSTANTS_GRP.G_INSTR_PREFIX) = 0 ) THEN
34 l_event_key := P_INSTRUCTION_SET_ID;
35 wf_event.raise3(
36 p_event_name =>l_event_name ,
37 p_event_key => l_event_key,
38 p_event_data => l_event_data,
39 p_parameter_list => l_parameter_list,
40 p_send_date => sysdate
41 );
42 end if;
43 --Bug 5224619: end
44 end if;
45 END RAISE_INSTR_SET_EVENT;
46 --Bug 4730261: end
47 -- This API is private, and used to update the entity key
48 -- stored across all temporary tables
49 PROCEDURE UPDATE_ENTITY_KEY
50 (
51 P_INSTRUCTION_PROCESS_ID IN NUMBER,
52 P_ENTITY_KEY IN VARCHAR2
53 )
54 IS
55 PRAGMA AUTONOMOUS_TRANSACTION;
56 BEGIN
57 IF(P_INSTRUCTION_PROCESS_ID IS NOT NULL AND P_ENTITY_KEY IS NOT NULL) THEN
58 UPDATE GMO_INSTR_ATTRIBUTES_T SET ENTITY_KEY = P_ENTITY_KEY
59 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
60 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY;
61 END IF;
62
63 -- Commit the changes
64 COMMIT;
65 EXCEPTION
66 WHEN OTHERS THEN
67 ROLLBACK;
68 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
69 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
70 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_INSTRUCTION_PVT');
71 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','UPDATE_ENTITY_KEY');
72 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
73 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
74 'edr.plsql.GMO_INSTRUCTION_PVT.UPDATE_ENTITY_KEY',
75 FALSE
76 );
77 end if;
78 --Diagnostics End
79
80 APP_EXCEPTION.RAISE_EXCEPTION;
81
82 END UPDATE_ENTITY_KEY;
83
84 -- This API is called to create a definition context with multiple
85 -- entity name, entity key, entity display names, and Instruction
86 -- types. It is called before definition UI is invoked to create
87 -- the necessary context for definition
88 PROCEDURE CREATE_DEFN_CONTEXT
89 (
90 P_CURR_INSTR_PROCESS_ID IN NUMBER DEFAULT NULL,
91 P_ENTITY_NAME IN FND_TABLE_OF_VARCHAR2_255,
92 P_ENTITY_KEY IN FND_TABLE_OF_VARCHAR2_255,
93 P_ENTITY_DISPLAYNAME IN FND_TABLE_OF_VARCHAR2_255,
94 P_INSTRUCTION_TYPE IN FND_TABLE_OF_VARCHAR2_255,
95 P_MODE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE,
96 P_CONTEXT_PARAMETERS IN GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE,
97 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
98 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
99 X_MSG_COUNT OUT NOCOPY NUMBER,
100 X_MSG_DATA OUT NOCOPY VARCHAR2
101 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
102
103 L_INSTRUCTION_PROCESS_ID NUMBER;
104
105 L_ENTITY_NAME VARCHAR2(100);
106 L_ENTITY_KEY VARCHAR2(1000);
107 L_ENTITY_DISPLAYNAME VARCHAR2(500);
108
109 L_INSTRUCTION_TYPE VARCHAR2(4000);
110
111 L_INSTRUCTION_SET_ID NUMBER;
112 L_INSTRUCTION_ID NUMBER;
113
114 L_CREATION_DATE DATE;
115 L_CREATED_BY NUMBER;
116 L_LAST_UPDATE_DATE DATE;
117 L_LAST_UPDATED_BY NUMBER;
118 L_LAST_UPDATE_LOGIN NUMBER;
119
120 L_MODE_PARAM_ERR EXCEPTION;
121 L_DUPLICATE_PARAM_ERR EXCEPTION;
122
123 L_PARAM_REC1 GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_REC_TYPE;
124 L_PARAM_REC2 GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_REC_TYPE;
125
126 L_PARAM_NAME1 VARCHAR2(100);
127 L_PARAM_NAME2 VARCHAR2(100);
128
129 L_COUNT NUMBER;
130
131 L_CONTEXT_TYPE VARCHAR2(400);
132
133 L_RETURN_STATUS VARCHAR2(10);
134
135 CURSOR L_GMO_INSTR_SET_CSR IS
136 SELECT INSTRUCTION_SET_ID, INSTRUCTION_TYPE,
137 ENTITY_NAME, ENTITY_KEY, INSTR_SET_NAME,
138 INSTR_SET_DESC, ACKN_STATUS
139 FROM GMO_INSTR_SET_DEFN_VL
140 WHERE ENTITY_NAME = L_ENTITY_NAME
141 AND NVL(ENTITY_KEY,1) = NVL(L_ENTITY_KEY,1)
142 AND ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS;
143
144 CURSOR L_GMO_INSTR_CSR IS
145 SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID, INSTRUCTION_TEXT,
146 TASK_ID, TASK_ATTRIBUTE, TASK_ATTRIBUTE_ID, TASK_LABEL, INSTR_SEQ,
147 INSTR_ACKN_TYPE, INSTR_NUMBER
148 FROM
149 GMO_INSTR_DEFN_VL
150 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
151
152 CURSOR L_GMO_INSTR_APPR_CSR IS
153 SELECT INSTRUCTION_ID, APPROVER_SEQ,
154 ROLE_COUNT, ROLE_NAME
155 FROM
156 GMO_INSTR_APPR_DEFN
157 WHERE INSTRUCTION_ID = L_INSTRUCTION_ID;
158
159 L_GMO_INSTR_SET_REC L_GMO_INSTR_SET_CSR%ROWTYPE;
160 L_GMO_INSTR_REC L_GMO_INSTR_CSR%ROWTYPE;
161 L_GMO_INSTR_APPR_REC L_GMO_INSTR_APPR_CSR%ROWTYPE;
162
163 L_VALID_PROCESS NUMBER;
164 L_ENTITY_EXIST_COUNT NUMBER;
165 L_INSTR_SET_EXIST_COUNT NUMBER;
166
167 CURSOR L_IS_VALID_PROCESS_CSR IS
168 SELECT COUNT(*) FROM GMO_INSTR_ATTRIBUTES_T
169 WHERE INSTRUCTION_PROCESS_ID = P_CURR_INSTR_PROCESS_ID
170 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
171 AND ATTRIBUTE_VALUE <> GMO_CONSTANTS_GRP.G_PROCESS_TERMINATE;
172
173 L_API_NAME VARCHAR2(40);
174 L_MESG_TEXT varchar2(1000);
175
176 BEGIN
177
178 L_API_NAME := 'CREATE_DEFN_CONTEXT';
179 L_VALID_PROCESS := 0;
180
181 IF (P_CURR_INSTR_PROCESS_ID is null) THEN
182 L_VALID_PROCESS := 0;
183 ELSE
184 open L_IS_VALID_PROCESS_CSR;
185 fetch L_IS_VALID_PROCESS_CSR into L_VALID_PROCESS;
186 close L_IS_VALID_PROCESS_CSR;
187 END IF;
188
189 GMO_UTILITIES.GET_WHO_COLUMNS
190 (
191 X_CREATION_DATE => L_CREATION_DATE,
192 X_CREATED_BY => L_CREATED_BY,
193 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
194 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
195 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
196 );
197
198 -- Check the MODE parameter, it must be either READ or UPDATE
199 IF ((P_MODE <> GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_READ)
200 AND (P_MODE <> GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE)) THEN
201 RAISE L_MODE_PARAM_ERR;
202 END IF;
203
204 -- If the process is valid, then proceed with creating the definition
205 -- context
206 IF (L_VALID_PROCESS = 0) THEN
207 -- This API will create rows in GMO_INSTR_ATTRIBUTES_T table with
208 -- name , value pairs for entity_name, entity_key and context parameters
209 -- to create a context for the definition of Process Instructions to start
210 SELECT GMO_INSTR_PROCESS_ID_S.NEXTVAL INTO L_INSTRUCTION_PROCESS_ID
211 FROM DUAL;
212
213 --Insert 'MODE' = P_MODE
214 INSERT INTO GMO_INSTR_ATTRIBUTES_T
215 (
216 INSTRUCTION_PROCESS_ID,
217 ATTRIBUTE_SEQ,
218 ATTRIBUTE_NAME,
219 ATTRIBUTE_VALUE,
220 ATTRIBUTE_TYPE,
221 CREATION_DATE,
222 CREATED_BY,
223 LAST_UPDATE_DATE,
224 LAST_UPDATED_BY,
225 LAST_UPDATE_LOGIN
226 )
227 VALUES
228 (
229 L_INSTRUCTION_PROCESS_ID,
230 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
231 GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE,
232 P_MODE,
233 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
234 L_CREATION_DATE,
235 L_CREATED_BY,
236 L_LAST_UPDATE_DATE,
237 L_LAST_UPDATED_BY,
238 L_LAST_UPDATE_LOGIN
239 );
240
241 -- Insert 'DEFINITION_STATUS' = 'NO_CHANGE'
242 -- If anything is modified in definition UI, this status
243 -- gets changed from G_STATUS_NO_CHANGE to G_STATUS_MODIFIED
244
245 INSERT INTO GMO_INSTR_ATTRIBUTES_T
246 (
247 INSTRUCTION_PROCESS_ID,
248 ATTRIBUTE_SEQ,
249 ATTRIBUTE_NAME,
250 ATTRIBUTE_VALUE,
251 ATTRIBUTE_TYPE,
252 CREATION_DATE,
253 CREATED_BY,
254 LAST_UPDATE_DATE,
255 LAST_UPDATED_BY,
256 LAST_UPDATE_LOGIN
257 )
258 VALUES
259 (
260 L_INSTRUCTION_PROCESS_ID,
261 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
262 GMO_CONSTANTS_GRP.G_DEFINITION_STATUS,
263 GMO_CONSTANTS_GRP.G_STATUS_NO_CHANGE,
264 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
265 L_CREATION_DATE,
266 L_CREATED_BY,
267 L_LAST_UPDATE_DATE,
268 L_LAST_UPDATED_BY,
269 L_LAST_UPDATE_LOGIN
270 );
271
272 -- Insert 'PROCESS_STATUS' = 'ERROR'
273 -- if the Process is successful, and Apply is clicked
274 -- PROCESS_STATUS = SUCCESS
275 -- if in the process, Cancel is clicked,
276 -- PROCESS_STATUS = CANCEL
277 -- If the process is error, i.e. browser close,
278 -- PROCESS_STATUS = ERROR , remains as is
279
280 INSERT INTO GMO_INSTR_ATTRIBUTES_T
281 (
282 INSTRUCTION_PROCESS_ID,
283 ATTRIBUTE_SEQ,
284 ATTRIBUTE_NAME,
285 ATTRIBUTE_VALUE,
286 ATTRIBUTE_TYPE,
287 CREATION_DATE,
288 CREATED_BY,
289 LAST_UPDATE_DATE,
290 LAST_UPDATED_BY,
291 LAST_UPDATE_LOGIN
292 )
293 VALUES
294 (
295 L_INSTRUCTION_PROCESS_ID,
296 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
297 GMO_CONSTANTS_GRP.G_PROCESS_STATUS,
298 GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
299 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
300 L_CREATION_DATE,
301 L_CREATED_BY,
302 L_LAST_UPDATE_DATE,
303 L_LAST_UPDATED_BY,
304 L_LAST_UPDATE_LOGIN
305 );
306 ELSE
307 L_INSTRUCTION_PROCESS_ID := P_CURR_INSTR_PROCESS_ID;
308
309 -- update the mode
310 UPDATE GMO_INSTR_ATTRIBUTES_T
311 SET ATTRIBUTE_VALUE = P_MODE,
312 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
313 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
314 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
315 WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
316 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE
317 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
318
319 -- update the status to ERROR
320 UPDATE GMO_INSTR_ATTRIBUTES_T
321 SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
322 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
323 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
324 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
325 WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
326 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
327 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
328
329 END IF; -- if L_VALID_PROCESS = 0;
330
331 --Insert Entity Name and Entity Key values in GMO_INSTR_ATTRIBUTES_T
332 L_INSTRUCTION_TYPE := P_INSTRUCTION_TYPE(1);
333
334 FOR J IN 2..P_INSTRUCTION_TYPE.count LOOP
335 L_INSTRUCTION_TYPE := L_INSTRUCTION_TYPE || ',' || P_INSTRUCTION_TYPE(J);
336 END LOOP;
337
338 IF (P_ENTITY_NAME.count > 0) THEN
339
340 -- Mark all entities as RENDER FALSE
341 UPDATE GMO_INSTR_ATTRIBUTES_T
342 SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_RENDER_FALSE,
343 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
344 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
346 WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
347 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY;
348
349 FOR I IN 1..P_ENTITY_NAME.COUNT LOOP
350 L_ENTITY_NAME := P_ENTITY_NAME(I);
351 L_ENTITY_KEY := P_ENTITY_KEY(I);
352 L_ENTITY_DISPLAYNAME := P_ENTITY_DISPLAYNAME(I);
353
354 L_ENTITY_EXIST_COUNT := 0;
355
356 SELECT COUNT(*) INTO L_ENTITY_EXIST_COUNT FROM GMO_INSTR_ATTRIBUTES_T
357 WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
358 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
359 AND ENTITY_NAME = L_ENTITY_NAME
360 AND ENTITY_KEY = L_ENTITY_KEY;
361
362 IF (l_entity_exist_count = 0) THEN
363 -- Validate all of these before inserting
364 INSERT INTO GMO_INSTR_ATTRIBUTES_T
365 (
366 INSTRUCTION_PROCESS_ID,
367 ATTRIBUTE_SEQ,
368 ATTRIBUTE_NAME,
369 ATTRIBUTE_VALUE,
370 ATTRIBUTE_TYPE,
371 ENTITY_NAME,
372 ENTITY_KEY,
373 ENTITY_DISPLAY_NAME,
374 INSTRUCTION_TYPE,
375 CREATION_DATE,
376 CREATED_BY,
377 LAST_UPDATE_DATE,
378 LAST_UPDATED_BY,
379 LAST_UPDATE_LOGIN
380 )
381 VALUES
382 (
383 L_INSTRUCTION_PROCESS_ID,
384 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
385 GMO_CONSTANTS_GRP.G_PARAM_ENTITY,
386 GMO_CONSTANTS_GRP.G_RENDER_TRUE,
387 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
388 L_ENTITY_NAME,
389 L_ENTITY_KEY,
390 L_ENTITY_DISPLAYNAME,
391 L_INSTRUCTION_TYPE,
392 L_CREATION_DATE,
393 L_CREATED_BY,
394 L_LAST_UPDATE_DATE,
395 L_LAST_UPDATED_BY,
396 L_LAST_UPDATE_LOGIN
397 );
398 ELSE
399 UPDATE GMO_INSTR_ATTRIBUTES_T
400 SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_RENDER_TRUE,
401 ENTITY_DISPLAY_NAME = L_ENTITY_DISPLAYNAME
402 WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
403 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
404 AND ENTITY_NAME = L_ENTITY_NAME
405 AND ENTITY_KEY = L_ENTITY_KEY;
406 END IF;
407 END LOOP;
408 END IF;
409
410 -- Delete the existing context parameters
411 IF (P_CONTEXT_PARAMETERS.COUNT > 0) THEN
412 DELETE FROM GMO_INSTR_ATTRIBUTES_T WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
413 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_CONTEXT;
414 END IF;
415
416 -- Insert P_CONTEXT_PARAMETERS in GMO_INSTR_ATTRIBUTES_T
417 FOR I IN 1..P_CONTEXT_PARAMETERS.COUNT LOOP
418 IF P_CONTEXT_PARAMETERS(I).NAME = GMO_CONSTANTS_GRP.G_INSTR_RETURN_URL OR
419 P_CONTEXT_PARAMETERS(I).NAME = GMO_CONSTANTS_GRP.G_INSTR_SOURCE_APPL_TYPE THEN
420
421 L_RETURN_STATUS := SET_PROCESS_VARIABLE(P_INSTRUCTION_PROCESS_ID => L_INSTRUCTION_PROCESS_ID,
422 P_ATTRIBUTE_NAME => P_CONTEXT_PARAMETERS(I).NAME,
423 P_ATTRIBUTE_VALUE => P_CONTEXT_PARAMETERS(I).VALUE,
424 P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_INTERNAL);
425
426 --Check the return status for each process attribute
427 IF (L_RETURN_STATUS = GMO_CONSTANTS_GRP.NO) THEN
428 ROLLBACK;
429 APP_EXCEPTION.RAISE_EXCEPTION;
430 END IF;
431
432 ELSE
433 INSERT INTO GMO_INSTR_ATTRIBUTES_T
434 (
435 INSTRUCTION_PROCESS_ID,
436 ATTRIBUTE_SEQ,
437 ATTRIBUTE_NAME,
438 ATTRIBUTE_VALUE,
439 ATTRIBUTE_TYPE,
440 CREATION_DATE,
441 CREATED_BY,
442 LAST_UPDATE_DATE,
443 LAST_UPDATED_BY,
444 LAST_UPDATE_LOGIN
445 )
446 VALUES
447 (
448 L_INSTRUCTION_PROCESS_ID,
449 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
450 P_CONTEXT_PARAMETERS(I).NAME,
451 P_CONTEXT_PARAMETERS(I).VALUE,
452 GMO_CONSTANTS_GRP.G_PARAM_CONTEXT,
453 L_CREATION_DATE,
454 L_CREATED_BY,
455 L_LAST_UPDATE_DATE,
456 L_LAST_UPDATED_BY,
457 L_LAST_UPDATE_LOGIN
458 );
459 END IF;
460
461 END LOOP;
462
463 -- Also, find if there are any entries already existing in permanent tables
464 -- if yes, copy them to temporary table with this new Instruction_Process_Id
465
466 FOR I IN 1..P_ENTITY_NAME.COUNT LOOP
467
468 L_ENTITY_NAME := P_ENTITY_NAME(I);
469 L_ENTITY_KEY := P_ENTITY_KEY(I);
470
471 OPEN L_GMO_INSTR_SET_CSR;
472 LOOP
473 FETCH L_GMO_INSTR_SET_CSR INTO L_GMO_INSTR_SET_REC;
474 EXIT WHEN L_GMO_INSTR_SET_CSR%NOTFOUND;
475 L_INSTRUCTION_SET_ID := L_GMO_INSTR_SET_REC.INSTRUCTION_SET_ID;
476 L_INSTR_SET_EXIST_COUNT := 0;
477
478 SELECT COUNT(*) INTO L_INSTR_SET_EXIST_COUNT
479 FROM GMO_INSTR_SET_DEFN_T
480 WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
481 AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
482
483 IF ( ( L_INSTRUCTION_TYPE IS NOT NULL AND ( LENGTH(L_INSTRUCTION_TYPE) = 0 OR
484 INSTR( l_instruction_type, L_GMO_INSTR_SET_REC.INSTRUCTION_TYPE) > 0 )) AND L_INSTR_SET_EXIST_COUNT = 0) THEN
485
486 -- Insert the Instruction Set Record from permenant to temporary
487 -- tables
488 INSERT INTO GMO_INSTR_SET_DEFN_T
489 (
490 INSTRUCTION_PROCESS_ID,
491 INSTRUCTION_SET_ID,
492 INSTRUCTION_TYPE,
493 ENTITY_NAME,
494 ENTITY_KEY,
495 INSTR_SET_NAME,
496 INSTR_SET_DESC,
497 ACKN_STATUS,
498 CREATION_DATE,
499 CREATED_BY,
500 LAST_UPDATE_DATE,
501 LAST_UPDATED_BY,
502 LAST_UPDATE_LOGIN
503 )
504 VALUES
505 (
506 L_INSTRUCTION_PROCESS_ID,
507 L_GMO_INSTR_SET_REC.INSTRUCTION_SET_ID,
508 L_GMO_INSTR_SET_REC.INSTRUCTION_TYPE,
509 L_GMO_INSTR_SET_REC.ENTITY_NAME,
510 L_GMO_INSTR_SET_REC.ENTITY_KEY,
511 L_GMO_INSTR_SET_REC.INSTR_SET_NAME,
512 L_GMO_INSTR_SET_REC.INSTR_SET_DESC,
513 GMO_CONSTANTS_GRP.G_INSTR_SET_UNACKN_STATUS,
514 L_CREATION_DATE,
515 L_CREATED_BY,
516 L_LAST_UPDATE_DATE,
517 L_LAST_UPDATED_BY,
518 L_LAST_UPDATE_LOGIN
519 );
520
521 -- Also copy attachments related to this Instruction Set Id
522 -- from permenant entity to temporary entity
523
524 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
525 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_B_ENTITY,
526 X_from_pk1_value => L_GMO_INSTR_SET_REC.INSTRUCTION_SET_ID,
527 X_from_pk2_value => NULL,
528 X_from_pk3_value => NULL,
529 X_from_pk4_value => NULL,
530 X_from_pk5_value => NULL,
531 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_T_ENTITY,
532 X_to_pk1_value => L_GMO_INSTR_SET_REC.INSTRUCTION_SET_ID,
533 X_to_pk2_value => L_INSTRUCTION_PROCESS_ID,
534 X_to_pk3_value => NULL,
535 X_to_pk4_value => NULL,
536 X_to_pk5_value => NULL,
537 X_created_by => L_CREATED_BY ,
538 X_last_update_login => L_LAST_UPDATE_LOGIN,
539 X_program_application_id => NULL,
540 X_program_id => NULL,
541 X_request_id => NULL,
542 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
543 X_from_category_id => NULL,
544 X_to_category_id => NULL
545 );
546
547 OPEN L_GMO_INSTR_CSR;
548 LOOP
549 FETCH L_GMO_INSTR_CSR INTO L_GMO_INSTR_REC;
550 EXIT WHEN L_GMO_INSTR_CSR%NOTFOUND;
551
552 INSERT INTO GMO_INSTR_DEFN_T
553 (
554 INSTRUCTION_PROCESS_ID,
555 INSTRUCTION_ID,
556 INSTRUCTION_SET_ID,
557 INSTRUCTION_TEXT,
558 TASK_ID,
559 TASK_ATTRIBUTE,
560 TASK_ATTRIBUTE_ID,
561 TASK_LABEL,
562 INSTR_SEQ,
563 INSTR_ACKN_TYPE,
564 INSTR_NUMBER,
565 CREATION_DATE,
566 CREATED_BY,
567 LAST_UPDATE_DATE,
568 LAST_UPDATED_BY,
569 LAST_UPDATE_LOGIN
570 )
571 VALUES
572 (
573 L_INSTRUCTION_PROCESS_ID,
574 L_GMO_INSTR_REC.INSTRUCTION_ID,
575 L_GMO_INSTR_REC.INSTRUCTION_SET_ID,
576 L_GMO_INSTR_REC.INSTRUCTION_TEXT,
577 L_GMO_INSTR_REC.TASK_ID,
578 L_GMO_INSTR_REC.TASK_ATTRIBUTE,
579 L_GMO_INSTR_REC.TASK_ATTRIBUTE_ID,
580 L_GMO_INSTR_REC.TASK_LABEL,
581 L_GMO_INSTR_REC.INSTR_SEQ,
582 L_GMO_INSTR_REC.INSTR_ACKN_TYPE,
583 L_GMO_INSTR_REC.INSTR_NUMBER,
584 L_CREATION_DATE,
585 L_CREATED_BY,
586 L_LAST_UPDATE_DATE,
587 L_LAST_UPDATED_BY,
588 L_LAST_UPDATE_LOGIN
589 );
590
591 -- Also copy attachments related to this Instruction Set Id
592 -- from permenant entity to temporary entity
593
594 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
595 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_B_ENTITY,
596 X_from_pk1_value => L_GMO_INSTR_REC.INSTRUCTION_ID,
597 X_from_pk2_value => NULL,
598 X_from_pk3_value => NULL,
599 X_from_pk4_value => NULL,
600 X_from_pk5_value => NULL,
601 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_T_ENTITY,
602 X_to_pk1_value => L_GMO_INSTR_REC.INSTRUCTION_ID,
603 X_to_pk2_value => L_INSTRUCTION_PROCESS_ID,
604 X_to_pk3_value => NULL,
605 X_to_pk4_value => NULL,
606 X_to_pk5_value => NULL,
607 X_created_by => L_CREATED_BY ,
608 X_last_update_login => L_LAST_UPDATE_LOGIN,
609 X_program_application_id => NULL,
610 X_program_id => NULL,
611 X_request_id => NULL,
612 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
613 X_from_category_id => NULL,
614 X_to_category_id => NULL
615 );
616
617 L_INSTRUCTION_ID := L_GMO_INSTR_REC.INSTRUCTION_ID;
618
619 OPEN L_GMO_INSTR_APPR_CSR;
620 LOOP
621 FETCH L_GMO_INSTR_APPR_CSR INTO L_GMO_INSTR_APPR_REC;
622 EXIT WHEN L_GMO_INSTR_APPR_CSR%NOTFOUND;
623
624 INSERT INTO GMO_INSTR_APPR_DEFN_T
625 (
626 INSTRUCTION_PROCESS_ID,
627 INSTRUCTION_ID,
628 APPROVER_SEQ,
629 ROLE_COUNT,
630 ROLE_NAME,
631 CREATION_DATE,
632 CREATED_BY,
633 LAST_UPDATE_DATE,
634 LAST_UPDATED_BY,
635 LAST_UPDATE_LOGIN
636 )
637 VALUES
638 (
639 L_INSTRUCTION_PROCESS_ID,
640 L_GMO_INSTR_APPR_REC.INSTRUCTION_ID,
641 L_GMO_INSTR_APPR_REC.APPROVER_SEQ,
642 L_GMO_INSTR_APPR_REC.ROLE_COUNT,
643 L_GMO_INSTR_APPR_REC.ROLE_NAME,
644 L_CREATION_DATE,
645 L_CREATED_BY,
646 L_LAST_UPDATE_DATE,
647 L_LAST_UPDATED_BY,
648 L_LAST_UPDATE_LOGIN
649 );
650
651 END LOOP;
652 CLOSE L_GMO_INSTR_APPR_CSR;
653
654 END LOOP;
655 CLOSE L_GMO_INSTR_CSR;
656 END IF;
657
658 END LOOP;
659 CLOSE L_GMO_INSTR_SET_CSR;
660
661 END LOOP;
662
663 -- COMMIT CHANGES
664 COMMIT;
665
666 X_INSTRUCTION_PROCESS_ID := L_INSTRUCTION_PROCESS_ID;
667 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
668
669 EXCEPTION
670 WHEN L_MODE_PARAM_ERR THEN
671
672 ROLLBACK;
673 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_MODE_PARAM_ERR');
674 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
675 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
676 FND_MESSAGE.SET_TOKEN('MODE', P_MODE );
677 L_MESG_TEXT := FND_MESSAGE.GET();
678
679 FND_MSG_PUB.ADD_EXC_MSG
680 ( G_PKG_NAME,
681 L_API_NAME,
682 L_MESG_TEXT
683 );
684
685 FND_MSG_PUB.COUNT_AND_GET
686 (
687 P_COUNT => X_MSG_COUNT,
688 P_DATA => X_MSG_DATA
689 );
690
691 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
692 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
693 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_DEFN_CONTEXT',
694 FALSE);
695 END IF;
696
697 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
698
699 WHEN OTHERS THEN
700
701 ROLLBACK;
702 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
703 FND_MSG_PUB.ADD_EXC_MSG
704 ( G_PKG_NAME,
705 L_API_NAME );
706 END IF;
707
708 FND_MSG_PUB.COUNT_AND_GET
709 ( P_COUNT => X_MSG_COUNT,
710 P_DATA => X_MSG_DATA
711 );
712
713 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
714 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
715 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_CONTEXT',
716 FALSE);
717 END IF;
718
719 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
720
721 END CREATE_DEFN_CONTEXT;
722
723 -- This API is called to create a definition context with multiple
724 -- entity name, entity key, entity display names, and Instruction
725 -- types. It is called before definition UI is invoked to create
726 -- the necessary context for definition
727
728 PROCEDURE CREATE_DEFN_CONTEXT
729 (
730 P_CURR_INSTR_PROCESS_ID IN NUMBER DEFAULT NULL,
731 P_ENTITY_NAME IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
732 P_ENTITY_KEY IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
733 P_ENTITY_DISPLAYNAME IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
734 P_INSTRUCTION_TYPE IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
735 P_MODE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE,
736 P_CONTEXT_PARAMETERS IN GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE,
737
738 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
739 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
740 X_MSG_COUNT OUT NOCOPY NUMBER,
741 X_MSG_DATA OUT NOCOPY VARCHAR2
742 )
743 IS
744
745 L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
746 L_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
747 L_ENTITY_DISPLAYNAME FND_TABLE_OF_VARCHAR2_255;
748 L_INSTRUCTION_TYPE FND_TABLE_OF_VARCHAR2_255;
749
750 BEGIN
751
752 L_ENTITY_NAME := FND_TABLE_OF_VARCHAR2_255();
753 L_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
754 L_ENTITY_DISPLAYNAME := FND_TABLE_OF_VARCHAR2_255();
755 L_INSTRUCTION_TYPE := FND_TABLE_OF_VARCHAR2_255();
756
757 FOR J IN 1..P_INSTRUCTION_TYPE.count LOOP
758 L_INSTRUCTION_TYPE.EXTEND;
759 L_INSTRUCTION_TYPE(J) := P_INSTRUCTION_TYPE(J);
760 END LOOP;
761
762 FOR I IN 1..P_ENTITY_NAME.COUNT LOOP
763 L_ENTITY_NAME.EXTEND;
764 L_ENTITY_KEY.EXTEND;
765 L_ENTITY_DISPLAYNAME.EXTEND;
766
767 L_ENTITY_NAME(I) := P_ENTITY_NAME(I);
768 L_ENTITY_KEY(I) := P_ENTITY_KEY(I);
769 L_ENTITY_DISPLAYNAME(I) := P_ENTITY_DISPLAYNAME(I);
770
771 END LOOP;
772
773 BEGIN
774
775 CREATE_DEFN_CONTEXT
776 (
777 P_CURR_INSTR_PROCESS_ID => P_CURR_INSTR_PROCESS_ID,
778 P_ENTITY_NAME => L_ENTITY_NAME,
779 P_ENTITY_KEY => L_ENTITY_KEY,
780 P_ENTITY_DISPLAYNAME => L_ENTITY_DISPLAYNAME,
781 P_INSTRUCTION_TYPE => L_INSTRUCTION_TYPE,
782 P_MODE => P_MODE,
783 P_CONTEXT_PARAMETERS => P_CONTEXT_PARAMETERS,
784 X_INSTRUCTION_PROCESS_ID => X_INSTRUCTION_PROCESS_ID,
785 X_RETURN_STATUS => X_RETURN_STATUS,
786 X_MSG_COUNT => X_MSG_COUNT,
787 X_MSG_DATA => X_MSG_DATA
788 );
789
790 END;
791
792 END;
793
794 -- This API is called to create a definition context with multiple
795 -- entity name, entity key, entity display names, and Instruction
796 -- types. It is called before definition UI is invoked to create
797 -- the necessary context for definition
798
799 PROCEDURE CREATE_DEFN_CONTEXT
800 (
801 P_CURR_INSTR_PROCESS_ID IN NUMBER DEFAULT NULL,
802 P_ENTITY_NAME IN VARCHAR2,
803 P_ENTITY_KEY IN VARCHAR2,
804 P_ENTITY_DISPLAYNAME IN VARCHAR2,
805 P_INSTRUCTION_TYPE IN VARCHAR2,
806 P_MODE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE,
807 P_CONTEXT_PARAMETERS IN GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE,
808 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
809 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
810 X_MSG_COUNT OUT NOCOPY NUMBER,
811 X_MSG_DATA OUT NOCOPY VARCHAR2
812 ) IS
813
814 L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
815 L_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
816 L_ENTITY_DISPLAYNAME FND_TABLE_OF_VARCHAR2_255;
817 L_INSTRUCTION_TYPE FND_TABLE_OF_VARCHAR2_255;
818
819 BEGIN
820
821 L_ENTITY_NAME := FND_TABLE_OF_VARCHAR2_255();
822 L_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
823 L_ENTITY_DISPLAYNAME := FND_TABLE_OF_VARCHAR2_255();
824 L_INSTRUCTION_TYPE := FND_TABLE_OF_VARCHAR2_255();
825
826 L_ENTITY_NAME.EXTEND;
827 L_ENTITY_KEY.EXTEND;
828 L_ENTITY_DISPLAYNAME.EXTEND;
829 L_INSTRUCTION_TYPE.EXTEND;
830
831 L_ENTITY_NAME(1) := P_ENTITY_NAME;
832 L_ENTITY_KEY(1) := P_ENTITY_KEY;
833 L_ENTITY_DISPLAYNAME(1) := P_ENTITY_DISPLAYNAME;
834 L_INSTRUCTION_TYPE(1) := P_INSTRUCTION_TYPE;
835
836 BEGIN
837 CREATE_DEFN_CONTEXT
838 (
839 P_CURR_INSTR_PROCESS_ID => P_CURR_INSTR_PROCESS_ID,
840 P_ENTITY_NAME => L_ENTITY_NAME,
841 P_ENTITY_KEY => L_ENTITY_KEY,
842 P_ENTITY_DISPLAYNAME => L_ENTITY_DISPLAYNAME,
843 P_INSTRUCTION_TYPE => L_INSTRUCTION_TYPE,
844 P_MODE => P_MODE,
845 P_CONTEXT_PARAMETERS => P_CONTEXT_PARAMETERS,
846 X_INSTRUCTION_PROCESS_ID => X_INSTRUCTION_PROCESS_ID,
847 X_RETURN_STATUS => X_RETURN_STATUS,
848 X_MSG_COUNT => X_MSG_COUNT,
849 X_MSG_DATA => X_MSG_DATA
850 );
851
852 END;
853
854 END CREATE_DEFN_CONTEXT;
855
856 -- This API is called to delete the instructions related to an entity
857 -- from the Process Instructions System.
858
859 PROCEDURE DELETE_ENTITY_FOR_PROCESS
860 (
861 P_CURR_INSTR_PROCESS_ID IN NUMBER,
862 P_ENTITY_NAME IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
863 P_ENTITY_KEY IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
864 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
865 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
866 X_MSG_COUNT OUT NOCOPY NUMBER,
867 X_MSG_DATA OUT NOCOPY VARCHAR2
868 )IS PRAGMA AUTONOMOUS_TRANSACTION;
869
870 L_COUNT NUMBER;
871
872 L_INSTRUCTION_TYPE FND_TABLE_OF_VARCHAR2_255;
873 L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
874 L_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
875 L_ENTITY_DISPLAYNAME FND_TABLE_OF_VARCHAR2_255;
876 L_CONTEXT_PARAMETERS GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE;
877
878 L_LOOKUP_TYPE VARCHAR2(300);
879 L_LOOKUP_CODE VARCHAR2(300);
880 L_EXIST_INSTR_TYPE BOOLEAN;
881
882 L_API_NAME VARCHAR2(40);
883
884 CURSOR L_CSR_GET_INSTR_TYPES IS
885 SELECT LOOKUP_CODE FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'GMO_INSTR_' || L_LOOKUP_TYPE;
886
887 BEGIN
888
889 L_API_NAME := 'DELETE_ENTITY_FOR_PROCESS';
890
891 L_ENTITY_NAME := FND_TABLE_OF_VARCHAR2_255();
892 L_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
893 L_ENTITY_DISPLAYNAME := FND_TABLE_OF_VARCHAR2_255();
894
895 FOR I IN 1..P_ENTITY_NAME.COUNT LOOP
896 L_ENTITY_NAME.EXTEND;
897 L_ENTITY_KEY.EXTEND;
898 L_ENTITY_DISPLAYNAME.EXTEND;
899
900 L_ENTITY_NAME(I) := P_ENTITY_NAME(I);
901 L_ENTITY_KEY(I) := P_ENTITY_KEY(I);
902 L_ENTITY_DISPLAYNAME(I) := '';
903 END LOOP;
904
905 L_COUNT := 0;
906 L_INSTRUCTION_TYPE := FND_TABLE_OF_VARCHAR2_255();
907
908 FOR I IN 1..P_ENTITY_NAME.COUNT LOOP
909
910 L_LOOKUP_TYPE := P_ENTITY_NAME(I);
911
912 OPEN L_CSR_GET_INSTR_TYPES;
913 LOOP
914 FETCH L_CSR_GET_INSTR_TYPES INTO L_LOOKUP_CODE;
915 EXIT WHEN L_CSR_GET_INSTR_TYPES%NOTFOUND;
916 IF (L_INSTRUCTION_TYPE IS NULL) THEN
917 L_COUNT := L_COUNT + 1;
918 L_INSTRUCTION_TYPE(L_COUNT) := l_lookup_code;
919 ELSE
920 L_EXIST_INSTR_TYPE := FALSE;
921
922 FOR J IN 1..L_INSTRUCTION_TYPE.COUNT LOOP
923 IF (L_INSTRUCTION_TYPE(J) = l_lookup_code) THEN
924 L_EXIST_INSTR_TYPE := TRUE;
925 END IF;
926 END LOOP;
927
928 IF (NOT L_EXIST_INSTR_TYPE) THEN
929 L_COUNT := L_COUNT + 1;
930 L_INSTRUCTION_TYPE.extend;
931 L_INSTRUCTION_TYPE(L_COUNT) := L_LOOKUP_CODE;
932 END IF;
933 END IF;
934
935 END LOOP;
936
937 CLOSE L_CSR_GET_INSTR_TYPES;
938
939 END LOOP;
940
941 CREATE_DEFN_CONTEXT
942 (
943 P_CURR_INSTR_PROCESS_ID => P_CURR_INSTR_PROCESS_ID,
944 P_ENTITY_NAME => L_ENTITY_NAME,
945 P_ENTITY_KEY => L_ENTITY_KEY,
946 P_ENTITY_DISPLAYNAME => L_ENTITY_DISPLAYNAME,
947 P_INSTRUCTION_TYPE => L_INSTRUCTION_TYPE,
948 P_CONTEXT_PARAMETERS => L_CONTEXT_PARAMETERS,
949 X_INSTRUCTION_PROCESS_ID => X_INSTRUCTION_PROCESS_ID,
950 X_RETURN_STATUS => X_RETURN_STATUS,
951 X_MSG_COUNT => X_MSG_COUNT,
952 X_MSG_DATA => X_MSG_DATA
953 );
954
955 -- update the definition status as modified and process status sucess
956 SET_INSTR_STATUS_ATTRIBUTES(P_INSTRUCTION_PROCESS_ID => X_INSTRUCTION_PROCESS_ID,
957 P_UPDATE_DEFN_STATUS => FND_API.G_TRUE);
958
959 --Delete the contents of Temp Table for the specified process ID.
960 DELETE FROM GMO_INSTR_APPR_DEFN_T WHERE INSTRUCTION_PROCESS_ID = X_INSTRUCTION_PROCESS_ID;
961 DELETE FROM GMO_INSTR_DEFN_T WHERE INSTRUCTION_PROCESS_ID = X_INSTRUCTION_PROCESS_ID;
962 DELETE FROM GMO_INSTR_SET_DEFN_T WHERE INSTRUCTION_PROCESS_ID = X_INSTRUCTION_PROCESS_ID;
963
964 -- COMMIT CHANGES
965 COMMIT;
966
967 EXCEPTION
968 WHEN OTHERS THEN
969 ROLLBACK;
970 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
971
972 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
973 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', SQLERRM);
974 FND_MESSAGE.SET_TOKEN('PKG_NAME',G_PKG_NAME);
975 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',L_API_NAME);
976 FND_MSG_PUB.ADD;
977
978 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
979
980 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
981
982 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
983 'gmo.plsql.GMO_INSTRUCTION_PVT.DELETE_ENTITY_FOR_PROCESS',FALSE);
984
985 END IF;
986
987 END DELETE_ENTITY_FOR_PROCESS;
988
989 -- This API is called to create a definition from existing
990 -- definition. It is called by the entity application to create
991 -- a new definition from existing ones
992
993 PROCEDURE CREATE_DEFN_FROM_DEFN
994 (
995 P_SOURCE_ENTITY_NAME IN VARCHAR2,
996 P_SOURCE_ENTITY_KEY IN VARCHAR2,
997 P_TARGET_ENTITY_NAME IN VARCHAR2,
998 P_TARGET_ENTITY_KEY IN VARCHAR2,
999 P_INSTRUCTION_TYPE IN VARCHAR2,
1000 X_INSTRUCTION_SET_ID OUT NOCOPY NUMBER,
1001 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1002 X_MSG_COUNT OUT NOCOPY NUMBER,
1003 X_MSG_DATA OUT NOCOPY VARCHAR2
1004 )
1005 IS
1006 L_INSTRUCTION_ID NUMBER;
1007 L_INSTRUCTION_SET_ID NUMBER;
1008
1009 L_NEW_INSTRUCTION_ID NUMBER;
1010 L_NEW_INSTRUCTION_SET_ID NUMBER;
1011
1012 CURSOR L_INSTR_SET_DEFN_CSR IS
1013 SELECT INSTRUCTION_SET_ID, INSTRUCTION_TYPE,
1014 ENTITY_NAME, ENTITY_KEY, INSTR_SET_NAME,
1015 INSTR_SET_DESC, ACKN_STATUS
1016 FROM GMO_INSTR_SET_DEFN_VL
1017 WHERE
1018 ENTITY_NAME = P_SOURCE_ENTITY_NAME
1019 AND ENTITY_KEY = P_SOURCE_ENTITY_KEY
1020 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
1021
1022 CURSOR L_INSTR_DEFN_CSR IS
1023 SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID,
1024 INSTR_SEQ, TASK_ID, TASK_ATTRIBUTE_ID,
1025 TASK_ATTRIBUTE, INSTR_ACKN_TYPE, INSTR_NUMBER,
1026 INSTRUCTION_TEXT, TASK_LABEL
1027 FROM GMO_INSTR_DEFN_VL
1028 WHERE
1029 INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
1030
1031 CURSOR L_INSTR_APPR_DEFN_CSR IS
1032 SELECT INSTRUCTION_ID, APPROVER_SEQ,
1033 ROLE_COUNT, ROLE_NAME
1034 FROM GMO_INSTR_APPR_DEFN
1035 WHERE
1036 INSTRUCTION_ID = L_INSTRUCTION_ID;
1037
1038 L_INSTR_SET_DEFN_REC L_INSTR_SET_DEFN_CSR%ROWTYPE;
1039 L_INSTR_DEFN_REC L_INSTR_DEFN_CSR%ROWTYPE;
1040 L_INSTR_APPR_DEFN_REC L_INSTR_APPR_DEFN_CSR%ROWTYPE;
1041
1042 L_SOURCE_ENTITY_ERR EXCEPTION;
1043 L_TARGET_ENTITY_ERR EXCEPTION;
1044
1045 L_SOURCE_ENTITY_NOT_FOUND_ERR EXCEPTION;
1046 L_SOURCE_COUNT NUMBER;
1047
1048 L_CREATION_DATE DATE;
1049 L_CREATED_BY NUMBER;
1050 L_LAST_UPDATE_DATE DATE;
1051 L_LAST_UPDATED_BY NUMBER;
1052 L_LAST_UPDATE_LOGIN NUMBER;
1053
1054 L_API_NAME VARCHAR2(40);
1055 L_MESG_TEXT VARCHAR2(1000);
1056
1057 L_TARGET_INSTRUCTION_SET_ID NUMBER;
1058
1059 l_entity_task_id number;
1060 l_new_task_attribute_id varchar2(4000);
1061
1062 BEGIN
1063
1064 L_API_NAME := 'CREATE_DEFN_FROM_DEFN';
1065
1066 GMO_UTILITIES.GET_WHO_COLUMNS
1067 (
1068 X_CREATION_DATE => L_CREATION_DATE,
1069 X_CREATED_BY => L_CREATED_BY,
1070 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
1071 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
1072 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
1073 );
1074
1075 IF ((P_SOURCE_ENTITY_NAME IS NULL) OR (P_SOURCE_ENTITY_KEY IS NULL)
1076 OR (P_INSTRUCTION_TYPE IS NULL )) THEN
1077 RAISE L_SOURCE_ENTITY_ERR;
1078 END IF;
1079
1080 IF ((P_TARGET_ENTITY_NAME IS NULL) OR (P_TARGET_ENTITY_KEY IS NULL)) THEN
1081 RAISE L_TARGET_ENTITY_ERR;
1082 END IF;
1083
1084 BEGIN
1085 SELECT INSTRUCTION_SET_ID INTO L_TARGET_INSTRUCTION_SET_ID
1086 FROM GMO_INSTR_SET_DEFN_VL
1087 WHERE ENTITY_NAME = P_SOURCE_ENTITY_NAME
1088 AND ENTITY_KEY = P_TARGET_ENTITY_KEY
1089 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
1090
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 L_TARGET_INSTRUCTION_SET_ID := 0;
1094 END;
1095
1096 IF (L_TARGET_INSTRUCTION_SET_ID > 0) THEN
1097 X_INSTRUCTION_SET_ID := L_TARGET_INSTRUCTION_SET_ID;
1098 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1099 RETURN;
1100 END IF;
1101
1102 OPEN L_INSTR_SET_DEFN_CSR;
1103 FETCH L_INSTR_SET_DEFN_CSR INTO L_INSTR_SET_DEFN_REC;
1104
1105 IF(L_INSTR_SET_DEFN_CSR%ROWCOUNT > 0) THEN
1106 L_SOURCE_COUNT := 1;
1107
1108 SELECT GMO_INSTR_SET_DEFN_S.NEXTVAL INTO L_NEW_INSTRUCTION_SET_ID FROM DUAL;
1109
1110 X_INSTRUCTION_SET_ID := L_NEW_INSTRUCTION_SET_ID;
1111
1112 INSERT INTO GMO_INSTR_SET_DEFN_VL
1113 (
1114 INSTRUCTION_SET_ID,
1115 INSTRUCTION_TYPE,
1116 INSTR_SET_NAME,
1117 INSTR_SET_DESC,
1118 ENTITY_NAME,
1119 ENTITY_KEY,
1120 ACKN_STATUS,
1121 ORIG_SOURCE,
1122 ORIG_SOURCE_ID,
1123 CREATION_DATE,
1124 CREATED_BY,
1125 LAST_UPDATE_DATE,
1126 LAST_UPDATED_BY,
1127 LAST_UPDATE_LOGIN
1128 )
1129 VALUES
1130 (
1131 L_NEW_INSTRUCTION_SET_ID,
1132 P_INSTRUCTION_TYPE,
1133 L_INSTR_SET_DEFN_REC.INSTR_SET_NAME,
1134 L_INSTR_SET_DEFN_REC.INSTR_SET_DESC,
1135 P_TARGET_ENTITY_NAME,
1136 P_TARGET_ENTITY_KEY,
1137 GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
1138 GMO_CONSTANTS_GRP.G_ORIG_SOURCE_DEFN,
1139 L_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
1140 L_CREATION_DATE,
1141 L_CREATED_BY,
1142 L_LAST_UPDATE_DATE,
1143 L_LAST_UPDATED_BY,
1144 L_LAST_UPDATE_LOGIN
1145 );
1146
1147 --Also copy attachments related to this Instruction set id
1148 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
1149 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_B_ENTITY,
1150 X_from_pk1_value => L_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
1151 X_from_pk2_value => NULL,
1152 X_from_pk3_value => NULL,
1153 X_from_pk4_value => NULL,
1154 X_from_pk5_value => NULL,
1155 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_B_ENTITY,
1156 X_to_pk1_value => L_NEW_INSTRUCTION_SET_ID,
1157 X_to_pk2_value => NULL,
1158 X_to_pk3_value => NULL,
1159 X_to_pk4_value => NULL,
1160 X_to_pk5_value => NULL,
1161 X_created_by => L_CREATED_BY,
1162 X_last_update_login => L_LAST_UPDATE_LOGIN,
1163 X_program_application_id => NULL,
1164 X_program_id => NULL,
1165 X_request_id => NULL,
1166 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
1167 X_from_category_id => NULL,
1168 X_to_category_id => NULL
1169 );
1170
1171 L_INSTRUCTION_SET_ID := L_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
1172
1173 OPEN L_INSTR_DEFN_CSR;
1174 LOOP
1175 FETCH L_INSTR_DEFN_CSR INTO L_INSTR_DEFN_REC;
1176 EXIT WHEN L_INSTR_DEFN_CSR%NOTFOUND;
1177
1178 SELECT GMO_INSTR_DEFN_S.NEXTVAL INTO L_NEW_INSTRUCTION_ID FROM DUAL;
1179
1180 -- get the correct task id for the new entity name and entity key
1181 l_entity_task_id := null;
1182 if (L_INSTR_DEFN_REC.TASK_ID is not null) then
1183
1184 select task_id into l_entity_task_id from gmo_instr_task_defn_vl
1185 where entity_name = P_TARGET_ENTITY_NAME
1186 and GMO_INSTR_ENTITY_PVT.GET_ENTITYKEY_SEPARATOR_COUNT(entity_key_pattern) = GMO_INSTR_ENTITY_PVT.GET_ENTITYKEY_SEPARATOR_COUNT(P_TARGET_ENTITY_KEY)
1187 and instruction_type = P_INSTRUCTION_TYPE
1188 and task_name = (select task_name from gmo_instr_task_defn_b where task_id = L_INSTR_DEFN_REC.TASK_ID);
1189
1190 end if;
1191 l_new_task_attribute_id := '';
1192 if (L_INSTR_DEFN_REC.TASK_ATTRIBUTE_ID is not null) then
1193 l_new_task_attribute_id := GMO_INSTR_ENTITY_PVT.GET_TARGET_TASK_ATTRIBUTE (
1194 P_ENTITY_NAME => P_SOURCE_ENTITY_NAME,
1195 P_SOURCE_ENTITY_KEY => P_SOURCE_ENTITY_KEY,
1196 P_TARGET_ENTITY_KEY => P_TARGET_ENTITY_KEY,
1197 P_TASK_ID => l_entity_task_id,
1198 P_TASK_ATTRIBUTE_ID => L_INSTR_DEFN_REC.TASK_ATTRIBUTE_ID
1199 );
1200 end if;
1201
1202 INSERT INTO GMO_INSTR_DEFN_VL
1203 (
1204 INSTRUCTION_ID,
1205 INSTRUCTION_SET_ID,
1206 INSTR_SEQ,
1207 TASK_ID,
1208 TASK_LABEL,
1209 TASK_ATTRIBUTE_ID,
1210 TASK_ATTRIBUTE,
1211 INSTR_ACKN_TYPE,
1212 INSTR_NUMBER,
1213 INSTRUCTION_TEXT,
1214 CREATION_DATE,
1215 CREATED_BY,
1216 LAST_UPDATE_DATE,
1217 LAST_UPDATED_BY,
1218 LAST_UPDATE_LOGIN
1219 )
1220 VALUES
1221 (
1222 L_NEW_INSTRUCTION_ID,
1223 X_INSTRUCTION_SET_ID,
1224 L_INSTR_DEFN_REC.INSTR_SEQ,
1225 l_entity_task_id,
1226 L_INSTR_DEFN_REC.TASK_LABEL,
1227 l_new_task_attribute_id,
1228 L_INSTR_DEFN_REC.TASK_ATTRIBUTE,
1229 L_INSTR_DEFN_REC.INSTR_ACKN_TYPE,
1230 L_INSTR_DEFN_REC.INSTR_NUMBER,
1231 L_INSTR_DEFN_REC.INSTRUCTION_TEXT,
1232 L_CREATION_DATE,
1233 L_CREATED_BY,
1234 L_LAST_UPDATE_DATE,
1235 L_LAST_UPDATED_BY,
1236 L_LAST_UPDATE_LOGIN
1237 );
1238
1239 --Also copy attachments related to this Instruction Id
1240 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
1241 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_B_ENTITY,
1242 X_from_pk1_value => L_INSTR_DEFN_REC.INSTRUCTION_ID,
1243 X_from_pk2_value => NULL,
1244 X_from_pk3_value => NULL,
1245 X_from_pk4_value => NULL,
1246 X_from_pk5_value => NULL,
1247 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_B_ENTITY,
1248 X_to_pk1_value => L_NEW_INSTRUCTION_ID,
1249 X_to_pk2_value => NULL,
1250 X_to_pk3_value => NULL,
1251 X_to_pk4_value => NULL,
1252 X_to_pk5_value => NULL,
1253 X_created_by => L_CREATED_BY,
1254 X_last_update_login => L_LAST_UPDATE_LOGIN,
1255 X_program_application_id => NULL,
1256 X_program_id => NULL,
1257 X_request_id => NULL,
1258 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
1259 X_from_category_id => NULL,
1260 X_to_category_id => NULL
1261 );
1262
1263 L_INSTRUCTION_ID := L_INSTR_DEFN_REC.INSTRUCTION_ID;
1264
1265 OPEN L_INSTR_APPR_DEFN_CSR;
1266 LOOP
1267 FETCH L_INSTR_APPR_DEFN_CSR INTO L_INSTR_APPR_DEFN_REC;
1268 EXIT WHEN L_INSTR_APPR_DEFN_CSR%NOTFOUND;
1269
1270 INSERT INTO GMO_INSTR_APPR_DEFN
1271 (
1272 INSTRUCTION_ID,
1273 APPROVER_SEQ,
1274 ROLE_COUNT,
1275 ROLE_NAME,
1276 CREATION_DATE,
1277 CREATED_BY,
1278 LAST_UPDATE_DATE,
1279 LAST_UPDATED_BY,
1280 LAST_UPDATE_LOGIN
1281
1282 )
1283 VALUES
1284 (
1285 L_NEW_INSTRUCTION_ID,
1286 L_INSTR_APPR_DEFN_REC.APPROVER_SEQ,
1287 L_INSTR_APPR_DEFN_REC.ROLE_COUNT,
1288 L_INSTR_APPR_DEFN_REC.ROLE_NAME,
1289 L_CREATION_DATE,
1290 L_CREATED_BY,
1291 L_LAST_UPDATE_DATE,
1292 L_LAST_UPDATED_BY,
1293 L_LAST_UPDATE_LOGIN
1294
1295 );
1296
1297 END LOOP;
1298 CLOSE L_INSTR_APPR_DEFN_CSR;
1299
1300 END LOOP;
1301 CLOSE L_INSTR_DEFN_CSR;
1302
1303 CLOSE L_INSTR_SET_DEFN_CSR;
1304
1305 END IF;
1306
1307
1308 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1309
1310 EXCEPTION
1311 WHEN L_SOURCE_ENTITY_ERR THEN
1312 X_INSTRUCTION_SET_ID := -1;
1313
1314 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SOURCE_ENTITY_ER');
1315 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
1316 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME);
1317 L_MESG_TEXT := FND_MESSAGE.GET();
1318
1319 FND_MSG_PUB.ADD_EXC_MSG
1320 ( G_PKG_NAME,
1321 L_API_NAME,
1322 L_MESG_TEXT
1323 );
1324
1325 FND_MSG_PUB.COUNT_AND_GET
1326 (
1327 P_COUNT => X_MSG_COUNT,
1328 P_DATA => X_MSG_DATA
1329 );
1330
1331 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1332 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1333 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_DEFN_FROM_DEFN',
1334 FALSE);
1335 END IF;
1336
1337 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1338
1339 WHEN L_TARGET_ENTITY_ERR THEN
1340 X_INSTRUCTION_SET_ID := -1;
1341
1342 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_TARGET_ENTITY_ER');
1343 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
1344 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME);
1345 L_MESG_TEXT := FND_MESSAGE.GET();
1346
1347 FND_MSG_PUB.ADD_EXC_MSG
1348 ( G_PKG_NAME,
1349 L_API_NAME,
1350 L_MESG_TEXT
1351 );
1352
1353 FND_MSG_PUB.COUNT_AND_GET
1354 (
1355 P_COUNT => X_MSG_COUNT,
1356 P_DATA => X_MSG_DATA
1357 );
1358
1359 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1360 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1361 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_DEFN_FROM_DEFN',
1362 FALSE);
1363 END IF;
1364
1365 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1366
1367 WHEN OTHERS THEN
1368 X_INSTRUCTION_SET_ID := -1;
1369
1370 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1371 FND_MSG_PUB.ADD_EXC_MSG
1372 (G_PKG_NAME,
1373 L_API_NAME);
1374 END IF;
1375
1376 FND_MSG_PUB.COUNT_AND_GET
1377 ( P_COUNT => X_MSG_COUNT,
1378 P_DATA => X_MSG_DATA);
1379
1380 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1381 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1382 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_FROM_DEFN',
1383 FALSE);
1384 END IF;
1385
1386 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1387
1388 END CREATE_DEFN_FROM_DEFN;
1389
1390 -- This API is called to send the acknowledegement of definition
1391 -- process, and copies all the data from temporary tables
1392 -- back to permenant tables
1393 PROCEDURE SEND_DEFN_ACKN
1394 (
1395 P_INSTRUCTION_PROCESS_ID IN NUMBER,
1396 P_ENTITY_NAME IN FND_TABLE_OF_VARCHAR2_255,
1397 P_SOURCE_ENTITY_KEY IN FND_TABLE_OF_VARCHAR2_255,
1398 P_TARGET_ENTITY_KEY IN FND_TABLE_OF_VARCHAR2_255,
1399 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1400 X_MSG_COUNT OUT NOCOPY NUMBER,
1401 X_MSG_DATA OUT NOCOPY VARCHAR2
1402 )
1403 IS
1404
1405 L_INSTRUCTION_SET_ID NUMBER;
1406 L_INSTRUCTION_ID NUMBER;
1407
1408 L_CREATION_DATE DATE;
1409 L_CREATED_BY NUMBER;
1410 L_LAST_UPDATE_DATE DATE;
1411 L_LAST_UPDATED_BY NUMBER;
1412 L_LAST_UPDATE_LOGIN NUMBER;
1413
1414 L_INSTRUCTION_TYPE_ARR FND_TABLE_OF_VARCHAR2_255;
1415 L_CNT NUMBER;
1416
1417 L_ENTITY_NAME VARCHAR2(200);
1418 L_SOURCE_ENTITY_KEY VARCHAR2(1000);
1419 L_TARGET_ENTITY_KEY VARCHAR2(1000);
1420 L_INSTRUCTION_TYPE VARCHAR2(40);
1421
1422 L_INSTR_SET_COUNT NUMBER;
1423 L_INSTR_COUNT NUMBER;
1424 L_INSTR_APPR_COUNT NUMBER;
1425 L_PNTR NUMBER;
1426 L_IN_ENTITY_NAME VARCHAR2(200);
1427 L_IN_ENTITY_KEY VARCHAR2(1000);
1428
1429 CURSOR L_TEMP_INSTR_SET_DEFN_CSR IS
1430 SELECT INSTRUCTION_PROCESS_ID, INSTRUCTION_SET_ID,
1431 INSTRUCTION_TYPE,
1432 ENTITY_NAME, ENTITY_KEY, INSTR_SET_NAME,
1433 INSTR_SET_DESC, CREATION_DATE,
1434 LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY,
1435 LAST_UPDATE_LOGIN
1436 FROM GMO_INSTR_SET_DEFN_T
1437 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1438 AND ENTITY_NAME = L_ENTITY_NAME
1439 AND ENTITY_KEY = L_TARGET_ENTITY_KEY
1440 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
1441
1442 CURSOR L_TEMP_INSTR_DEFN_CSR IS
1443 SELECT INSTRUCTION_PROCESS_ID, INSTRUCTION_ID, INSTRUCTION_SET_ID,
1444 INSTRUCTION_TEXT, INSTR_SEQ, TASK_ID, TASK_ATTRIBUTE_ID,
1445 TASK_ATTRIBUTE, INSTR_ACKN_TYPE, INSTR_NUMBER, CREATION_DATE,
1446 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, TASK_LABEL
1447 FROM GMO_INSTR_DEFN_T
1448 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1449 AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
1450 ORDER BY INSTR_SEQ, INSTRUCTION_ID;
1451
1452 CURSOR L_TEMP_INSTR_APPR_DEFN_CSR IS
1453 SELECT INSTRUCTION_PROCESS_ID, INSTRUCTION_ID, APPROVER_SEQ,
1454 ROLE_COUNT, ROLE_NAME, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
1455 LAST_UPDATED_BY, LAST_UPDATE_LOGIN
1456 FROM GMO_INSTR_APPR_DEFN_T
1457 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1458 AND INSTRUCTION_ID = L_INSTRUCTION_ID;
1459
1460 CURSOR L_TEMP_ENTITY_CSR IS
1461 SELECT INSTRUCTION_PROCESS_ID, ATTRIBUTE_SEQ,
1462 ATTRIBUTE_NAME, ATTRIBUTE_VALUE,
1463 ENTITY_NAME, ENTITY_KEY, ENTITY_DISPLAY_NAME, INSTRUCTION_TYPE,
1464 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1465 LAST_UPDATE_LOGIN
1466 FROM GMO_INSTR_ATTRIBUTES_T
1467 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1468 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
1469 ORDER BY ATTRIBUTE_SEQ;
1470
1471 CURSOR L_TEMP_IN_ENTITY_CSR IS
1472 SELECT INSTRUCTION_PROCESS_ID, ATTRIBUTE_SEQ,
1473 ATTRIBUTE_NAME, ATTRIBUTE_VALUE,
1474 ENTITY_NAME, ENTITY_KEY, ENTITY_DISPLAY_NAME, INSTRUCTION_TYPE,
1475 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1476 LAST_UPDATE_LOGIN
1477 FROM GMO_INSTR_ATTRIBUTES_T
1478 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1479 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
1480 AND ENTITY_NAME = L_IN_ENTITY_NAME
1481 AND ENTITY_KEY = L_IN_ENTITY_KEY;
1482
1483 L_TEMP_INSTR_SET_DEFN_REC L_TEMP_INSTR_SET_DEFN_CSR%ROWTYPE;
1484 L_TEMP_INSTR_DEFN_REC L_TEMP_INSTR_DEFN_CSR%ROWTYPE;
1485 L_TEMP_INSTR_APPR_DEFN_REC L_TEMP_INSTR_APPR_DEFN_CSR%ROWTYPE;
1486 L_TEMP_ENTITY_REC L_TEMP_ENTITY_CSR%ROWTYPE;
1487
1488 L_ENTITY_NAME_ARR FND_TABLE_OF_VARCHAR2_255;
1489 L_SOURCE_ENTITY_KEY_ARR FND_TABLE_OF_VARCHAR2_255;
1490 L_TARGET_ENTITY_KEY_ARR FND_TABLE_OF_VARCHAR2_255;
1491 L_INSTRUCTION_TYPES FND_TABLE_OF_VARCHAR2_255;
1492
1493 L_ENTITY_INFO_NOTFOUND_ERR EXCEPTION;
1494 L_INSTR_SEQ_COUNT NUMBER;
1495
1496 L_DEFINITION_STATUS VARCHAR2(100);
1497 L_DEL_INSTR_SET_ID NUMBER;
1498
1499 L_API_NAME VARCHAR2(40);
1500 L_MESG_TEXT VARCHAR2(1000);
1501
1502 L_ENTITY_COUNT_P NUMBER;
1503 L_ENTITY_COUNT_T NUMBER;
1504 L_PERM_INSTRUCTION_SET_ID NUMBER;
1505 L_TEMP_INSTRUCTION_SET_ID NUMBER;
1506
1507 L_TEMP_INSTR_ID NUMBER;
1508 L_PERM_INSTR_ID NUMBER;
1509 L_TEMP_INSTR_SEQ NUMBER;
1510
1511 CURSOR L_TEMP_INSTR_CHK_CSR IS
1512 SELECT INSTRUCTION_ID, INSTR_SEQ INTO L_TEMP_INSTR_ID, L_TEMP_INSTR_SEQ FROM GMO_INSTR_DEFN_T
1513 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1514 AND INSTRUCTION_SET_ID = L_PERM_INSTRUCTION_SET_ID
1515 ORDER BY INSTR_SEQ ;
1516
1517
1518 BEGIN
1519 L_API_NAME := 'SEND_DEFN_ACKN';
1520
1521 -- Validate definition
1522 SELECT ATTRIBUTE_VALUE INTO L_DEFINITION_STATUS FROM GMO_INSTR_ATTRIBUTES_T
1523 WHERE ATTRIBUTE_NAME = 'DEFINITION_STATUS' AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID AND ATTRIBUTE_TYPE = 'INTERNAL';
1524
1525
1526 -- Perform all the processing only if the definition status is
1527 -- modified.
1528 IF( L_DEFINITION_STATUS <> GMO_CONSTANTS_GRP.G_STATUS_MODIFIED) THEN
1529 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1530 RETURN;
1531 END IF;
1532
1533 -- If P_SOURCE_ENTITY_NAME and P_SOURCE_ENTITY_KEY is null,
1534 -- Then construct it from GMO_INSTR_ATTRIBUTES_T table by querying
1535 -- entity name, entity key and instruction type values
1536
1537 L_SOURCE_ENTITY_KEY_ARR := FND_TABLE_OF_VARCHAR2_255();
1538 L_INSTRUCTION_TYPE_ARR := FND_TABLE_OF_VARCHAR2_255();
1539 L_ENTITY_NAME_ARR := FND_TABLE_OF_VARCHAR2_255();
1540 L_TARGET_ENTITY_KEY_ARR := FND_TABLE_OF_VARCHAR2_255();
1541
1542 -- check if entity information is passed or not
1543 -- if passed used the entity key else use the information from the
1544 -- temp table.
1545 IF(P_ENTITY_NAME IS NULL OR (P_ENTITY_NAME IS NOT NULL AND P_ENTITY_NAME.COUNT <= 0)) THEN
1546 OPEN L_TEMP_ENTITY_CSR;
1547 L_CNT := 0;
1548 LOOP
1549 FETCH L_TEMP_ENTITY_CSR INTO L_TEMP_ENTITY_REC;
1550 EXIT WHEN L_TEMP_ENTITY_CSR%NOTFOUND;
1551 L_CNT := L_CNT + 1;
1552
1553 L_INSTRUCTION_TYPE_ARR.EXTEND;
1554 L_INSTRUCTION_TYPE_ARR(L_CNT) := L_TEMP_ENTITY_REC.INSTRUCTION_TYPE;
1555 L_ENTITY_NAME_ARR.EXTEND;
1556 L_ENTITY_NAME_ARR(L_CNT) := L_TEMP_ENTITY_REC.ENTITY_NAME;
1557 L_SOURCE_ENTITY_KEY_ARR.EXTEND;
1558 L_SOURCE_ENTITY_KEY_ARR(L_CNT) := L_TEMP_ENTITY_REC.ENTITY_KEY;
1559
1560 L_TARGET_ENTITY_KEY_ARR.EXTEND;
1561 L_TARGET_ENTITY_KEY_ARR(L_CNT) := L_TEMP_ENTITY_REC.ENTITY_KEY;
1562
1563 END LOOP;
1564
1565 CLOSE L_TEMP_ENTITY_CSR;
1566 ELSE
1567 L_CNT := 0;
1568 FOR I IN 1..P_ENTITY_NAME.COUNT LOOP
1569 L_IN_ENTITY_NAME := P_ENTITY_NAME(I);
1570 L_IN_ENTITY_KEY := P_SOURCE_ENTITY_KEY (I);
1571
1572 OPEN L_TEMP_IN_ENTITY_CSR;
1573 LOOP
1574 FETCH L_TEMP_IN_ENTITY_CSR INTO L_TEMP_ENTITY_REC;
1575 EXIT WHEN L_TEMP_IN_ENTITY_CSR%NOTFOUND;
1576
1577 L_CNT := L_CNT + 1;
1578
1579 L_INSTRUCTION_TYPE_ARR.EXTEND;
1580 L_INSTRUCTION_TYPE_ARR(L_CNT) := L_TEMP_ENTITY_REC.INSTRUCTION_TYPE;
1581
1582 L_ENTITY_NAME_ARR.EXTEND;
1583 L_ENTITY_NAME_ARR(L_CNT) := L_IN_ENTITY_NAME;
1584 L_SOURCE_ENTITY_KEY_ARR.EXTEND;
1585 L_SOURCE_ENTITY_KEY_ARR(L_CNT) := L_IN_ENTITY_KEY;
1586
1587 L_TARGET_ENTITY_KEY_ARR.EXTEND;
1588 L_TARGET_ENTITY_KEY_ARR(L_CNT) := L_IN_ENTITY_KEY;
1589 -- If target entity key is passed, set target entity key to parameter
1590 -- passed value, else target entity key is same as source entity key
1591 IF(P_TARGET_ENTITY_KEY IS NOT NULL AND P_TARGET_ENTITY_KEY.COUNT > 0 AND P_TARGET_ENTITY_KEY(I) IS NOT NULL ) THEN
1592 L_TARGET_ENTITY_KEY_ARR(L_CNT) := P_TARGET_ENTITY_KEY(I);
1593 END IF;
1594 END LOOP;
1595 CLOSE L_TEMP_IN_ENTITY_CSR;
1596 END LOOP;
1597 END IF;
1598
1599 -- If the count is zero, raise exception
1600 IF (L_CNT = 0) THEN
1601 RAISE L_ENTITY_INFO_NOTFOUND_ERR;
1602 END IF;
1603
1604 GMO_UTILITIES.GET_WHO_COLUMNS
1605 (
1606 X_CREATION_DATE => L_CREATION_DATE,
1607 X_CREATED_BY => L_CREATED_BY,
1608 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
1609 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
1610 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
1611 );
1612
1613
1614 -- For every entity do the acknowledgement ,
1615 -- by copying the details from temporary tables to permenant
1616
1617 FOR I IN 1..L_ENTITY_NAME_ARR.COUNT LOOP
1618
1619
1620 -- This is comma seperated set of instruction types
1621 L_INSTRUCTION_TYPE := L_INSTRUCTION_TYPE_ARR(I);
1622 L_INSTRUCTION_TYPES := FND_TABLE_OF_VARCHAR2_255();
1623 L_INSTRUCTION_TYPES.EXTEND;
1624
1625 --Instruction Types must be a comma seperated String value
1626 IF(INSTR(L_INSTRUCTION_TYPE,',') > 0) THEN
1627 L_PNTR := 1;
1628
1629 LOOP
1630 L_INSTRUCTION_TYPES(L_PNTR) := SUBSTR(l_instruction_type,1, instr(l_instruction_type,',') -1);
1631 L_INSTRUCTION_TYPES.EXTEND;
1632 L_INSTRUCTION_TYPE := SUBSTR(l_instruction_type, instr(l_instruction_type,',') +1);
1633 L_PNTR := L_PNTR + 1;
1634
1635 IF(INSTR(L_INSTRUCTION_TYPE,',') = 0 AND LENGTH(L_INSTRUCTION_TYPE) > 0) THEN
1636 L_INSTRUCTION_TYPES.EXTEND;
1637 L_INSTRUCTION_TYPES(L_PNTR) := L_INSTRUCTION_TYPE;
1638 END IF;
1639
1640 EXIT WHEN LENGTH(L_INSTRUCTION_TYPE) = 0 OR INSTR(L_INSTRUCTION_TYPE,',') = 0 ;
1641 END LOOP;
1642
1643 ELSE
1644 L_INSTRUCTION_TYPES(1) := l_INSTRUCTION_TYPE;
1645 END IF;
1646
1647 L_ENTITY_NAME := L_ENTITY_NAME_ARR(I);
1648 L_SOURCE_ENTITY_KEY := L_SOURCE_ENTITY_KEY_ARR(I);
1649 L_TARGET_ENTITY_KEY := L_TARGET_ENTITY_KEY_ARR(I);
1650
1651
1652 -- For each Instruction Type for the entity loop.
1653 FOR J IN 1..L_INSTRUCTION_TYPES.COUNT LOOP
1654
1655 L_INSTRUCTION_TYPE := L_INSTRUCTION_TYPES(J);
1656
1657
1658 -- if the target entity keys have changed
1659 -- update the temp table with the data,
1660 -- so that the movement from temp to permanent is
1661 -- to the correct set of rows
1662 IF (L_SOURCE_ENTITY_KEY <> L_TARGET_ENTITY_KEY) THEN
1663
1664 UPDATE GMO_INSTR_SET_DEFN_T
1665 SET ENTITY_KEY = L_TARGET_ENTITY_KEY
1666 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1667 AND ENTITY_NAME = L_ENTITY_NAME
1668 AND ENTITY_KEY = L_SOURCE_ENTITY_KEY
1669 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
1670
1671
1672 UPDATE GMO_INSTR_ATTRIBUTES_T
1673 SET ENTITY_KEY = L_TARGET_ENTITY_KEY
1674 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1675 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
1676 AND ENTITY_NAME = L_ENTITY_NAME
1677 AND ENTITY_KEY = L_SOURCE_ENTITY_KEY
1678 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
1679
1680 BEGIN
1681
1682 L_PERM_INSTRUCTION_SET_ID := -1;
1683 L_TEMP_INSTRUCTION_SET_ID := -1;
1684
1685 SELECT INSTRUCTION_SET_ID INTO L_PERM_INSTRUCTION_SET_ID FROM GMO_INSTR_SET_DEFN_B
1686 WHERE ENTITY_NAME = L_ENTITY_NAME AND ENTITY_KEY = L_TARGET_ENTITY_KEY AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
1687
1688 SELECT INSTRUCTION_SET_ID INTO L_TEMP_INSTRUCTION_SET_ID FROM GMO_INSTR_SET_DEFN_T
1689 WHERE ENTITY_NAME = L_ENTITY_NAME AND ENTITY_KEY = L_TARGET_ENTITY_KEY AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE
1690 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
1691
1692 UPDATE GMO_INSTR_SET_DEFN_T
1693 SET INSTRUCTION_SET_ID = L_PERM_INSTRUCTION_SET_ID
1694 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1695 AND ENTITY_NAME = L_ENTITY_NAME
1696 AND ENTITY_KEY = L_TARGET_ENTITY_KEY
1697 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
1698
1699 UPDATE GMO_INSTR_DEFN_T
1700 SET INSTRUCTION_SET_ID = L_PERM_INSTRUCTION_SET_ID
1701 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
1702 AND INSTRUCTION_SET_ID = L_TEMP_INSTRUCTION_SET_ID;
1703
1704 --Bug 5020834: start
1705 --When the target entity is different the instruction id needs to be updated, after instruction
1706 --set id update, to ensure the correct values get updated in the permanent for the new entity.
1707 OPEN L_TEMP_INSTR_CHK_CSR;
1708 LOOP
1709 FETCH L_TEMP_INSTR_CHK_CSR INTO L_TEMP_INSTR_ID,L_TEMP_INSTR_SEQ;
1710 EXIT WHEN L_TEMP_INSTR_CHK_CSR%NOTFOUND;
1711 begin
1712 SELECT INSTRUCTION_ID INTO L_PERM_INSTR_ID FROM GMO_INSTR_DEFN_B
1713 WHERE INSTRUCTION_SET_ID = L_PERM_INSTRUCTION_SET_ID AND INSTR_SEQ = L_TEMP_INSTR_SEQ;
1714 exception
1715 when no_data_found then
1716 SELECT GMO_INSTR_DEFN_S.NEXTVAL INTO L_PERM_INSTR_ID FROM DUAL;
1717 end;
1718 update gmo_instr_defn_t set instruction_id = L_PERM_INSTR_ID where instruction_id = l_temp_instr_id;
1719 update gmo_instr_appr_defn_t set instruction_id = L_PERM_INSTR_ID where instruction_id = l_temp_instr_id;
1720 END LOOP;
1721 CLOSE L_TEMP_INSTR_CHK_CSR;
1722 --Bug 5020834: end
1723 EXCEPTION
1724 WHEN NO_DATA_FOUND THEN
1725 NULL;
1726 END;
1727 --Bug 5203096: start
1728 --we may need to update the task attribute if entity key changes
1729 --the below api takes care
1730 GMO_INSTR_ENTITY_PVT.UPDATE_TASK_ATTRIBUTE
1731 (
1732 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
1733 P_INSTRUCTION_SET_ID => L_PERM_INSTRUCTION_SET_ID,
1734 P_ENTITY_NAME => L_ENTITY_NAME,
1735 P_SOURCE_ENTITY_KEY => L_SOURCE_ENTITY_KEY,
1736 P_TARGET_ENTITY_KEY => L_TARGET_ENTITY_KEY
1737 );
1738 --Bug 5203096: end
1739 END IF;
1740
1741 OPEN L_TEMP_INSTR_SET_DEFN_CSR;
1742
1743 LOOP
1744 FETCH L_TEMP_INSTR_SET_DEFN_CSR INTO L_TEMP_INSTR_SET_DEFN_REC;
1745 EXIT WHEN L_TEMP_INSTR_SET_DEFN_CSR%NOTFOUND;
1746
1747 L_INSTR_SET_COUNT := 0;
1748
1749 SELECT COUNT(*) INTO L_INSTR_SET_COUNT FROM GMO_INSTR_SET_DEFN_B
1750 WHERE INSTRUCTION_SET_ID = L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
1751
1752 -- If Instruction Set is already present in permenant table then update
1753 -- it with data from temporary table record
1754 IF (L_INSTR_SET_COUNT > 0 ) THEN
1755 --Bug 5224619: start
1756 UPDATE GMO_INSTR_SET_DEFN_B SET
1757 INSTR_SET_NAME = L_TEMP_INSTR_SET_DEFN_REC.INSTR_SET_NAME,
1758 ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
1759 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
1760 LAST_UPDATED_BY = L_LAST_UPDATED_BY ,
1761 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
1762 WHERE INSTRUCTION_SET_ID = L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
1763
1764 UPDATE GMO_INSTR_SET_DEFN_TL SET
1765 INSTR_SET_DESC = L_TEMP_INSTR_SET_DEFN_REC.INSTR_SET_DESC,
1766 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
1767 LAST_UPDATED_BY = L_LAST_UPDATED_BY ,
1768 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
1769 WHERE INSTRUCTION_SET_ID = L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
1770 --Bug 5224619: end
1771
1772 ELSE
1773 INSERT INTO GMO_INSTR_SET_DEFN_VL
1774 (
1775 INSTRUCTION_SET_ID,
1776 INSTRUCTION_TYPE,
1777 INSTR_SET_NAME,
1778 INSTR_SET_DESC,
1779 ENTITY_NAME,
1780 ENTITY_KEY,
1781 ACKN_STATUS,
1782 CREATION_DATE,
1783 CREATED_BY,
1784 LAST_UPDATE_DATE,
1785 LAST_UPDATED_BY,
1786 LAST_UPDATE_LOGIN
1787 )
1788 VALUES
1789 (
1790 L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
1791 L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_TYPE,
1792 L_TEMP_INSTR_SET_DEFN_REC.INSTR_SET_NAME,
1793 L_TEMP_INSTR_SET_DEFN_REC.INSTR_SET_DESC,
1794 L_TEMP_INSTR_SET_DEFN_REC.ENTITY_NAME,
1795 L_TARGET_ENTITY_KEY,
1796 GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
1797 L_CREATION_DATE,
1798 L_CREATED_BY,
1799 L_LAST_UPDATE_DATE,
1800 L_LAST_UPDATED_BY,
1801 L_LAST_UPDATE_LOGIN
1802 );
1803 END IF;
1804
1805
1806 -- Delete Attachments from permenant ENTITY, and copy them back from temporary ENTITY
1807 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
1808 X_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_B_ENTITY,
1809 X_pk1_value => L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
1810 X_pk2_value => NULL,
1811 X_pk3_value => NULL,
1812 X_pk4_value => NULL,
1813 X_pk5_value => NULL,
1814 X_delete_document_flag => GMO_CONSTANTS_GRP.NO,
1815 X_automatically_added_flag => NULL);
1816
1817 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
1818 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_T_ENTITY,
1819 X_from_pk1_value => L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
1820 X_from_pk2_value => P_INSTRUCTION_PROCESS_ID,
1821 X_from_pk3_value => NULL,
1822 X_from_pk4_value => NULL,
1823 X_from_pk5_value => NULL,
1824 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_B_ENTITY,
1825 X_to_pk1_value => L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
1826 X_to_pk2_value => NULL,
1827 X_to_pk3_value => NULL,
1828 X_to_pk4_value => NULL,
1829 X_to_pk5_value => NULL,
1830 X_created_by => L_CREATED_BY,
1831 X_last_update_login => L_LAST_UPDATE_LOGIN,
1832 X_program_application_id => NULL,
1833 X_program_id => NULL,
1834 X_request_id => NULL,
1835 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
1836 X_from_category_id => NULL,
1837 X_to_category_id => NULL );
1838
1839 -- Delete Attachments from Temporary ENTITY now
1840 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
1841 X_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_T_ENTITY,
1842 X_pk1_value => L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
1843 X_pk2_value => P_INSTRUCTION_PROCESS_ID,
1844 X_pk3_value => NULL,
1845 X_pk4_value => NULL,
1846 X_pk5_value => NULL,
1847 X_delete_document_flag => GMO_CONSTANTS_GRP.NO,
1848 X_automatically_added_flag => NULL);
1849
1850
1851 L_INSTRUCTION_SET_ID := L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
1852 L_INSTR_SEQ_COUNT := 0;
1853
1854 OPEN L_TEMP_INSTR_DEFN_CSR;
1855 LOOP
1856 FETCH L_TEMP_INSTR_DEFN_CSR INTO L_TEMP_INSTR_DEFN_REC;
1857 EXIT WHEN L_TEMP_INSTR_DEFN_CSR%NOTFOUND;
1858
1859 L_INSTR_COUNT := 0;
1860
1861 -- Increment the Instruction Sequence
1862 L_INSTR_SEQ_COUNT := L_INSTR_SEQ_COUNT + 1;
1863
1864
1865 -- check if we have the instruction
1866 -- for that set in the permanent table
1867 -- if yes update the instruction
1868 -- else insert it
1869
1870 SELECT COUNT(*) INTO L_INSTR_COUNT FROM GMO_INSTR_DEFN_B
1871 WHERE INSTRUCTION_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID
1872 AND INSTRUCTION_SET_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_SET_ID;
1873
1874 IF(L_INSTR_COUNT > 0) THEN
1875 --Bug 5224619: start
1876 UPDATE GMO_INSTR_DEFN_B SET
1877 INSTR_SEQ = L_INSTR_SEQ_COUNT,
1878 TASK_ID = L_TEMP_INSTR_DEFN_REC.TASK_ID,
1879 TASK_ATTRIBUTE_ID = L_TEMP_INSTR_DEFN_REC.TASK_ATTRIBUTE_ID,
1880 TASK_ATTRIBUTE = L_TEMP_INSTR_DEFN_REC.TASK_ATTRIBUTE,
1881 INSTR_ACKN_TYPE = L_TEMP_INSTR_DEFN_REC.INSTR_ACKN_TYPE,
1882 INSTR_NUMBER = L_TEMP_INSTR_DEFN_REC.INSTR_NUMBER,
1883 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
1884 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
1885 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
1886 WHERE INSTRUCTION_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID
1887 AND INSTRUCTION_SET_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_SET_ID;
1888
1889 UPDATE GMO_INSTR_DEFN_TL SET
1890 INSTRUCTION_TEXT = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_TEXT,
1891 TASK_LABEL = L_TEMP_INSTR_DEFN_REC.TASK_LABEL,
1892 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
1893 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
1894 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
1895 WHERE INSTRUCTION_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID
1896 AND LANGUAGE = USERENV('LANG');
1897 --Bug 5224619: end
1898
1899 ELSE
1900
1901 INSERT INTO GMO_INSTR_DEFN_VL
1902 (
1903 INSTRUCTION_ID,
1904 INSTRUCTION_SET_ID,
1905 INSTR_SEQ,
1906 TASK_ID,
1907 TASK_ATTRIBUTE_ID,
1908 TASK_ATTRIBUTE,
1909 INSTR_ACKN_TYPE,
1910 INSTR_NUMBER,
1911 INSTRUCTION_TEXT,
1912 CREATION_DATE,
1913 CREATED_BY,
1914 LAST_UPDATE_DATE,
1915 LAST_UPDATED_BY,
1916 LAST_UPDATE_LOGIN,
1917 TASK_LABEL
1918 )
1919 VALUES
1920 (
1921 L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID,
1922 L_TEMP_INSTR_DEFN_REC.INSTRUCTION_SET_ID,
1923 L_INSTR_SEQ_COUNT,
1924 L_TEMP_INSTR_DEFN_REC.TASK_ID,
1925 L_TEMP_INSTR_DEFN_REC.TASK_ATTRIBUTE_ID,
1926 L_TEMP_INSTR_DEFN_REC.TASK_ATTRIBUTE,
1927 L_TEMP_INSTR_DEFN_REC.INSTR_ACKN_TYPE,
1928 L_TEMP_INSTR_DEFN_REC.INSTR_NUMBER,
1929 L_TEMP_INSTR_DEFN_REC.INSTRUCTION_TEXT,
1930 L_CREATION_DATE,
1931 L_CREATED_BY,
1932 L_LAST_UPDATE_DATE,
1933 L_LAST_UPDATED_BY,
1934 L_LAST_UPDATE_LOGIN,
1935 L_TEMP_INSTR_DEFN_REC.TASK_LABEL
1936 );
1937
1938 END IF;
1939
1940 -- Delete Attachments from permenant ENTITY, and copy
1941 -- them back from temporary
1942 -- ENTITY
1943
1944 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
1945 X_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_B_ENTITY,
1946 X_pk1_value => L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID,
1947 X_pk2_value => NULL,
1948 X_pk3_value => NULL,
1949 X_pk4_value => NULL,
1950 X_pk5_value => NULL,
1951 X_delete_document_flag => GMO_CONSTANTS_GRP.NO,
1952 X_automatically_added_flag => NULL);
1953
1954 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
1955 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_T_ENTITY,
1956 X_from_pk1_value => L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID,
1957 X_from_pk2_value => P_INSTRUCTION_PROCESS_ID,
1958 X_from_pk3_value => NULL,
1959 X_from_pk4_value => NULL,
1960 X_from_pk5_value => NULL,
1961 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_B_ENTITY,
1962 X_to_pk1_value => L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID,
1963 X_to_pk2_value => NULL,
1964 X_to_pk3_value => NULL,
1965 X_to_pk4_value => NULL,
1966 X_to_pk5_value => NULL,
1967 X_created_by => L_CREATED_BY,
1968 X_last_update_login => L_LAST_UPDATE_LOGIN,
1969 X_program_application_id => NULL,
1970 X_program_id => NULL,
1971 X_request_id => NULL,
1972 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
1973 X_from_category_id => NULL,
1974 X_to_category_id => NULL);
1975
1976 -- Delete Attachments from Temporary ENTITY now
1977 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
1978 X_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_T_ENTITY,
1979 X_pk1_value => L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID,
1980 X_pk2_value => P_INSTRUCTION_PROCESS_ID,
1981 X_pk3_value => NULL,
1982 X_pk4_value => NULL,
1983 X_pk5_value => NULL,
1984 X_delete_document_flag => GMO_CONSTANTS_GRP.NO,
1985 X_automatically_added_flag => NULL);
1986
1987
1988 L_INSTRUCTION_ID := L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID;
1989
1990 OPEN L_TEMP_INSTR_APPR_DEFN_CSR;
1991 LOOP
1992 FETCH L_TEMP_INSTR_APPR_DEFN_CSR INTO L_TEMP_INSTR_APPR_DEFN_REC;
1993 EXIT WHEN L_TEMP_INSTR_APPR_DEFN_CSR%NOTFOUND;
1994
1995 L_INSTR_APPR_COUNT := 0;
1996
1997 SELECT COUNT(*) INTO L_INSTR_APPR_COUNT FROM GMO_INSTR_APPR_DEFN
1998 WHERE INSTRUCTION_ID = L_INSTRUCTION_ID
1999 AND APPROVER_SEQ = L_TEMP_INSTR_APPR_DEFN_REC.APPROVER_SEQ;
2000
2001 IF ( L_INSTR_APPR_COUNT > 0) THEN
2002 UPDATE GMO_INSTR_APPR_DEFN SET
2003 ROLE_COUNT = L_TEMP_INSTR_APPR_DEFN_REC.ROLE_COUNT,
2004 ROLE_NAME = L_TEMP_INSTR_APPR_DEFN_REC.ROLE_NAME,
2005 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
2006 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
2007 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
2008 WHERE INSTRUCTION_ID = L_INSTRUCTION_ID
2009 AND APPROVER_SEQ = L_TEMP_INSTR_APPR_DEFN_REC.APPROVER_SEQ;
2010 ELSE
2011 INSERT INTO GMO_INSTR_APPR_DEFN
2012 (
2013 INSTRUCTION_ID,
2014 APPROVER_SEQ,
2015 ROLE_COUNT,
2016 ROLE_NAME,
2017 CREATION_DATE,
2018 CREATED_BY,
2019 LAST_UPDATE_DATE,
2020 LAST_UPDATED_BY,
2021 LAST_UPDATE_LOGIN
2022 )
2023 VALUES
2024 (
2025 L_TEMP_INSTR_APPR_DEFN_REC.INSTRUCTION_ID,
2026 L_TEMP_INSTR_APPR_DEFN_REC.APPROVER_SEQ,
2027 L_TEMP_INSTR_APPR_DEFN_REC.ROLE_COUNT,
2028 L_TEMP_INSTR_APPR_DEFN_REC.ROLE_NAME,
2029 L_CREATION_DATE,
2030 L_CREATED_BY,
2031 L_LAST_UPDATE_DATE,
2032 L_LAST_UPDATED_BY,
2033 L_LAST_UPDATE_LOGIN
2034 );
2035 END IF;
2036
2037 END LOOP;
2038 CLOSE L_TEMP_INSTR_APPR_DEFN_CSR;
2039
2040 -- Cleanup deleted approvers from approvers table
2041 DELETE FROM GMO_INSTR_APPR_DEFN
2042 WHERE INSTRUCTION_ID = L_INSTRUCTION_ID
2043 AND APPROVER_SEQ NOT IN
2044 ( SELECT APPROVER_SEQ FROM GMO_INSTR_APPR_DEFN_T WHERE INSTRUCTION_ID = L_INSTRUCTION_ID
2045 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID);
2046
2047 END LOOP;
2048 CLOSE L_TEMP_INSTR_DEFN_CSR;
2049
2050 --Cleanup deleted records from instruction table
2051 --Bug 5224619: start
2052 DELETE FROM GMO_INSTR_DEFN_TL
2053 WHERE INSTRUCTION_ID NOT IN
2054 ( SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_T WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
2055 AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID)
2056 AND INSTRUCTION_ID IN (SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_B WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID)
2057 AND LANGUAGE=USERENV('LANG');
2058
2059 DELETE FROM GMO_INSTR_DEFN_B
2060 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
2061 AND INSTRUCTION_ID NOT IN
2062 ( SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_T WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
2063 AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID);
2064 --Bug 5224619: end
2065
2066 END LOOP;
2067 CLOSE L_TEMP_INSTR_SET_DEFN_CSR;
2068
2069
2070 --Cleanup deleted records from instruction set table
2071 -- Count = number of instruction sets in temporary table
2072
2073 SELECT COUNT(*) INTO L_ENTITY_COUNT_T FROM GMO_INSTR_SET_DEFN_T
2074 WHERE ENTITY_NAME = L_ENTITY_NAME
2075 AND ENTITY_KEY = L_TARGET_ENTITY_KEY
2076 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE
2077 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
2078
2079 -- Count = number of instruction sets in permanent table
2080 SELECT COUNT(*) INTO L_ENTITY_COUNT_P FROM GMO_INSTR_SET_DEFN_B
2081 WHERE ENTITY_NAME = L_ENTITY_NAME
2082 AND ENTITY_KEY = L_TARGET_ENTITY_KEY
2083 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
2084
2085 -- If exist in permanent but not in temp, delete from permanent
2086 IF (L_ENTITY_COUNT_T < L_ENTITY_COUNT_P ) THEN
2087
2088 SELECT count(*) INTO L_DEL_INSTR_SET_ID
2089 FROM GMO_INSTR_SET_DEFN_B
2090 WHERE ENTITY_NAME = L_ENTITY_NAME
2091 AND ENTITY_KEY = L_TARGET_ENTITY_KEY
2092 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
2093
2094
2095 SELECT INSTRUCTION_SET_ID INTO L_DEL_INSTR_SET_ID
2096 FROM GMO_INSTR_SET_DEFN_B
2097 WHERE ENTITY_NAME = L_ENTITY_NAME
2098 AND ENTITY_KEY = L_TARGET_ENTITY_KEY
2099 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
2100
2101 -- First delete the approvers
2102 DELETE FROM GMO_INSTR_APPR_DEFN
2103 WHERE INSTRUCTION_ID IN
2104 (SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_B
2105 WHERE INSTRUCTION_SET_ID = L_DEL_INSTR_SET_ID);
2106
2107 -- Second remove the instructions
2108 --Bug 5224619: start
2109 DELETE FROM GMO_INSTR_DEFN_TL WHERE INSTRUCTION_ID IN
2110 (SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_B WHERE INSTRUCTION_SET_ID = L_DEL_INSTR_SET_ID) AND LANGUAGE=USERENV('LANG');
2111
2112 DELETE FROM GMO_INSTR_DEFN_B
2113 WHERE INSTRUCTION_SET_ID = L_DEL_INSTR_SET_ID;
2114
2115 -- Finally remove the instruction set from table
2116 DELETE FROM GMO_INSTR_SET_DEFN_TL
2117 WHERE INSTRUCTION_SET_ID IN (SELECT INSTRUCTION_SET_ID FROM GMO_INSTR_SET_DEFN_B WHERE ENTITY_NAME = L_ENTITY_NAME
2118 AND ENTITY_KEY = L_TARGET_ENTITY_KEY AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE)
2119 AND LANGUAGE=USERENV('LANG');
2120
2121 DELETE FROM GMO_INSTR_SET_DEFN_B
2122 WHERE ENTITY_NAME = L_ENTITY_NAME
2123 AND ENTITY_KEY = L_TARGET_ENTITY_KEY
2124 AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
2125 --Bug 5224619: end
2126
2127 END IF;
2128
2129 END LOOP; --Instruction Types
2130 END LOOP; --Entity Name Loop
2131
2132 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2133
2134 EXCEPTION
2135 WHEN L_ENTITY_INFO_NOTFOUND_ERR THEN
2136
2137 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SOURCE_ENTITY_ER');
2138 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
2139 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
2140 L_MESG_TEXT := FND_MESSAGE.GET();
2141
2142 FND_MSG_PUB.ADD_EXC_MSG (G_PKG_NAME, L_API_NAME,L_MESG_TEXT );
2143 FND_MSG_PUB.COUNT_AND_GET (P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
2144 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2145 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.GMO_INSTRUCTIONS_PVT.SEND_DEFN_ACKN',FALSE);
2146 END IF;
2147
2148 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2149
2150 WHEN OTHERS THEN
2151 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2152 FND_MSG_PUB.ADD_EXC_MSG (G_PKG_NAME, L_API_NAME);
2153 END IF;
2154
2155 FND_MSG_PUB.COUNT_AND_GET (P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
2156 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2157 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED, 'gmo.plsql.GMO_INSTRUCTION_PVT.SEND_DEFN_ACKN',FALSE);
2158 END IF;
2159
2160 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2161 END SEND_DEFN_ACKN;
2162
2163 -- This API is called to send the acknowledegement of definition
2164 -- process, and copies all the data from temporary tables
2165 -- back to permenant tables
2166 PROCEDURE SEND_DEFN_ACKN
2167 (
2168 P_INSTRUCTION_PROCESS_ID IN NUMBER,
2169 P_ENTITY_NAME IN VARCHAR2,
2170 P_SOURCE_ENTITY_KEY IN VARCHAR2,
2171 P_TARGET_ENTITY_KEY IN VARCHAR2,
2172 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2173 X_MSG_COUNT OUT NOCOPY NUMBER,
2174 X_MSG_DATA OUT NOCOPY VARCHAR2
2175 )
2176 IS
2177 L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
2178 L_SOURCE_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
2179 L_TARGET_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
2180
2181 BEGIN
2182
2183 L_ENTITY_NAME := FND_TABLE_OF_VARCHAR2_255();
2184 L_SOURCE_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
2185 L_TARGET_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
2186
2187 L_ENTITY_NAME.EXTEND;
2188 L_SOURCE_ENTITY_KEY.EXTEND;
2189 L_TARGET_ENTITY_KEY.EXTEND;
2190
2191 L_ENTITY_NAME(1) := P_ENTITY_NAME;
2192 L_SOURCE_ENTITY_KEY(1) := P_SOURCE_ENTITY_KEY;
2193 L_TARGET_ENTITY_KEY(1) := P_TARGET_ENTITY_KEY;
2194
2195 SEND_DEFN_ACKN
2196 (
2197 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
2198 P_ENTITY_NAME => L_ENTITY_NAME,
2199 P_SOURCE_ENTITY_KEY => L_SOURCE_ENTITY_KEY,
2200 P_TARGET_ENTITY_KEY => L_TARGET_ENTITY_KEY,
2201 X_RETURN_STATUS => X_RETURN_STATUS,
2202 X_MSG_COUNT => X_MSG_COUNT,
2203 X_MSG_DATA => X_MSG_DATA
2204 );
2205
2206 END SEND_DEFN_ACKN;
2207
2208 -- This API is called to send the acknowledegement of definition
2209 -- process, and copies all the data from temporary tables
2210 -- back to permenant tables
2211 PROCEDURE SEND_DEFN_ACKN
2212 (
2213 P_INSTRUCTION_PROCESS_ID IN NUMBER,
2214 P_ENTITY_NAME IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
2215 P_SOURCE_ENTITY_KEY IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
2216 P_TARGET_ENTITY_KEY IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
2217 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2218 X_MSG_COUNT OUT NOCOPY NUMBER,
2219 X_MSG_DATA OUT NOCOPY VARCHAR2
2220 )
2221 IS
2222
2223 L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
2224 L_SOURCE_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
2225 L_TARGET_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
2226
2227 BEGIN
2228
2229 L_ENTITY_NAME := FND_TABLE_OF_VARCHAR2_255();
2230 L_SOURCE_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
2231 L_TARGET_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
2232
2233
2234 FOR I IN 1..P_ENTITY_NAME.COUNT LOOP
2235
2236 L_ENTITY_NAME.EXTEND;
2237 L_SOURCE_ENTITY_KEY.EXTEND;
2238 L_TARGET_ENTITY_KEY.EXTEND;
2239
2240 L_ENTITY_NAME(I) := P_ENTITY_NAME(I);
2241 L_SOURCE_ENTITY_KEY(I) := P_SOURCE_ENTITY_KEY(I);
2242 L_TARGET_ENTITY_KEY(I) := P_TARGET_ENTITY_KEY(I);
2243
2244 END LOOP;
2245
2246 BEGIN
2247
2248 SEND_DEFN_ACKN
2249 (
2250 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
2251 P_ENTITY_NAME => L_ENTITY_NAME,
2252 P_SOURCE_ENTITY_KEY => L_SOURCE_ENTITY_KEY,
2253 P_TARGET_ENTITY_KEY => L_TARGET_ENTITY_KEY,
2254 X_RETURN_STATUS => X_RETURN_STATUS,
2255 X_MSG_COUNT => X_MSG_COUNT,
2256 X_MSG_DATA => X_MSG_DATA
2257 );
2258
2259 END;
2260
2261 END SEND_DEFN_ACKN;
2262
2263 -- This API is called to create an instance from the definition
2264 -- It is called to instantiate the instructions from definition tables
2265 -- into runtime, and available in operator workbench
2266
2267 PROCEDURE CREATE_INSTANCE_FROM_DEFN
2268 (
2269 P_DEFINITION_ENTITY_NAME IN VARCHAR2,
2270 P_DEFINITION_ENTITY_KEY IN VARCHAR2,
2271 P_INSTANCE_ENTITY_NAME IN VARCHAR2,
2272 P_INSTANCE_ENTITY_KEY IN VARCHAR2,
2273 P_INSTRUCTION_TYPE IN VARCHAR2,
2274
2275 X_INSTRUCTION_SET_ID OUT NOCOPY NUMBER,
2276 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2277 X_MSG_COUNT OUT NOCOPY NUMBER,
2278 X_MSG_DATA OUT NOCOPY VARCHAR2
2279 )
2280 IS
2281
2282 L_INSTRUCTION_SET_ID NUMBER;
2283 L_INSTRUCTION_ID NUMBER;
2284 L_NEW_INSTRUCTION_SET_ID NUMBER;
2285 L_NEW_INSTRUCTION_ID NUMBER;
2286
2287 L_CREATION_DATE DATE;
2288 L_CREATED_BY NUMBER;
2289 L_LAST_UPDATE_DATE DATE;
2290 L_LAST_UPDATED_BY NUMBER;
2291 L_LAST_UPDATE_LOGIN NUMBER;
2292
2293 CURSOR L_INSTR_SET_DEFN_CSR IS
2294 SELECT INSTRUCTION_SET_ID, INSTRUCTION_TYPE,
2295 ENTITY_NAME, ENTITY_KEY, INSTR_SET_NAME,
2296 INSTR_SET_DESC, ACKN_STATUS
2297 FROM GMO_INSTR_SET_DEFN_VL
2298 WHERE
2299 ENTITY_NAME = P_DEFINITION_ENTITY_NAME
2300 AND ENTITY_KEY = P_DEFINITION_ENTITY_KEY
2301 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
2302 AND ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS;
2303
2304 CURSOR L_INSTR_DEFN_CSR IS
2305 SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID, INSTRUCTION_TEXT,
2306 INSTR_SEQ, INSTR_NUMBER, INSTR_ACKN_TYPE,
2307 TASK_LABEL, TASK_ATTRIBUTE, TASK_ATTRIBUTE_ID, TASK_ID
2308 FROM GMO_INSTR_DEFN_VL
2309 WHERE
2310 INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
2311
2312 CURSOR L_INSTR_APPR_DEFN_CSR IS
2313 SELECT INSTRUCTION_ID, APPROVER_SEQ, ROLE_COUNT, ROLE_NAME
2314 FROM GMO_INSTR_APPR_DEFN
2315 WHERE
2316 INSTRUCTION_ID = L_INSTRUCTION_ID
2317 ORDER BY APPROVER_SEQ;
2318
2319 L_INSTR_SET_DEFN_REC L_INSTR_SET_DEFN_CSR%ROWTYPE;
2320 L_INSTR_DEFN_REC L_INSTR_DEFN_CSR%ROWTYPE;
2321 L_INSTR_APPR_DEFN_REC L_INSTR_APPR_DEFN_CSR%ROWTYPE;
2322
2323 L_INSTANCE_ENTITY_KEY VARCHAR2(500);
2324 L_DEFN_NOTFOUND_ERR EXCEPTION;
2325
2326 L_API_NAME VARCHAR2(40);
2327 L_MESG_TEXT VARCHAR2(1000);
2328
2329 l_task_label varchar2(200);
2330
2331 BEGIN
2332
2333 L_API_NAME := 'CREATE_INSTANCE_FROM_DEFN';
2334
2335 GMO_UTILITIES.GET_WHO_COLUMNS
2336 (
2337 X_CREATION_DATE => L_CREATION_DATE,
2338 X_CREATED_BY => L_CREATED_BY,
2339 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
2340 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
2341 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
2342 );
2343
2344 -- If the input parameters are invalid, log a message
2345 -- and return error status.
2346 IF(P_DEFINITION_ENTITY_NAME IS NULL
2347 OR P_DEFINITION_ENTITY_KEY IS NULL
2348 OR P_INSTANCE_ENTITY_NAME IS NULL
2349 OR P_INSTRUCTION_TYPE IS NULL ) THEN
2350
2351 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_CIFD_PARAM_ERR');
2352 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
2353 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
2354 L_MESG_TEXT := FND_MESSAGE.GET();
2355
2356 FND_MSG_PUB.ADD_EXC_MSG
2357 ( G_PKG_NAME,
2358 L_API_NAME,
2359 L_MESG_TEXT
2360 );
2361
2362 FND_MSG_PUB.COUNT_AND_GET
2363 (
2364 P_COUNT => X_MSG_COUNT,
2365 P_DATA => X_MSG_DATA
2366 );
2367
2368 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2369 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2370 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_INSTANCE_FROM_DEFN',
2371 FALSE);
2372 END IF;
2373
2374 X_INSTRUCTION_SET_ID := -1;
2375 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2376
2377 RETURN;
2378
2379 END IF;
2380
2381 -- If Instance entity key is null, create a new instruction set id from sequence
2382 IF(P_INSTANCE_ENTITY_KEY IS NULL) THEN
2383 SELECT GMO_INSTR_SET_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_SET_ID
2384 FROM DUAL;
2385 L_INSTANCE_ENTITY_KEY := GMO_CONSTANTS_GRP.G_INSTR_PREFIX || L_NEW_INSTRUCTION_SET_ID;
2386 ELSE
2387 BEGIN
2388 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
2389 FROM GMO_INSTR_SET_INSTANCE_VL
2390 WHERE
2391 ENTITY_NAME = P_INSTANCE_ENTITY_NAME
2392 AND nvl(ENTITY_KEY,1) = nvl(P_INSTANCE_ENTITY_KEY,1)
2393 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
2394 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
2395
2396 EXCEPTION
2397 WHEN NO_DATA_FOUND THEN
2398 L_INSTRUCTION_SET_ID := 0;
2399 END;
2400
2401 -- If The Instruction Instance Already Exist For The Given
2402 -- Entity Name And Entity_key, Return The Set Id Of The Active
2403 -- Instance Instruction Set Found Above
2404 IF (L_INSTRUCTION_SET_ID > 0) THEN
2405
2406 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2407 X_INSTRUCTION_SET_ID := L_INSTRUCTION_SET_ID;
2408
2409 RETURN;
2410 END IF;
2411
2412 SELECT GMO_INSTR_SET_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_SET_ID
2413 FROM DUAL;
2414
2415 L_INSTANCE_ENTITY_KEY := P_INSTANCE_ENTITY_KEY;
2416
2417 END IF;
2418
2419 -- For instruction set, instruction , approvers copy data from definition
2420 -- tables to permenant tables
2421 OPEN L_INSTR_SET_DEFN_CSR;
2422 LOOP
2423 FETCH L_INSTR_SET_DEFN_CSR INTO L_INSTR_SET_DEFN_REC;
2424 EXIT WHEN L_INSTR_SET_DEFN_CSR%NOTFOUND;
2425
2426 --INSERT DATA FROM DEFN TABLE TO INSTANCE TABLE
2427 INSERT INTO GMO_INSTR_SET_INSTANCE_VL
2428 (
2429 INSTRUCTION_SET_ID,
2430 INSTRUCTION_TYPE,
2431 ENTITY_NAME,
2432 ENTITY_KEY,
2433 INSTR_SET_NAME,
2434 INSTR_SET_DESC,
2435 ACKN_STATUS,
2436 INSTR_SET_STATUS,
2437 ORIG_SOURCE,
2438 ORIG_SOURCE_ID,
2439 CREATION_DATE,
2440 CREATED_BY,
2441 LAST_UPDATE_DATE,
2442 LAST_UPDATED_BY,
2443 LAST_UPDATE_LOGIN
2444 )
2445 VALUES
2446 (
2447 L_NEW_INSTRUCTION_SET_ID,
2448 L_INSTR_SET_DEFN_REC.INSTRUCTION_TYPE,
2449 P_INSTANCE_ENTITY_NAME, -- It must insert instance entity name and key
2450 L_INSTANCE_ENTITY_KEY,
2451 L_INSTR_SET_DEFN_REC.INSTR_SET_NAME,
2452 L_INSTR_SET_DEFN_REC.INSTR_SET_DESC,
2453 GMO_CONSTANTS_GRP.G_INSTR_SET_UNACKN_STATUS,
2454 GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE,
2455 GMO_CONSTANTS_GRP.G_ORIG_SOURCE_DEFN,
2456 L_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
2457 L_CREATION_DATE,
2458 L_CREATED_BY,
2459 L_LAST_UPDATE_DATE,
2460 L_LAST_UPDATED_BY,
2461 L_LAST_UPDATE_LOGIN
2462 );
2463
2464 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
2465 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_B_ENTITY,
2466 X_from_pk1_value => L_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
2467 X_from_pk2_value => NULL,
2468 X_from_pk3_value => NULL,
2469 X_from_pk4_value => NULL,
2470 X_from_pk5_value => NULL,
2471 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_INSTANCE_B_ENTITY,
2472 X_to_pk1_value => L_NEW_INSTRUCTION_SET_ID,
2473 X_to_pk2_value => NULL,
2474 X_to_pk3_value => NULL,
2475 X_to_pk4_value => NULL,
2476 X_to_pk5_value => NULL,
2477 X_created_by => L_CREATED_BY,
2478 X_last_update_login => L_LAST_UPDATE_LOGIN,
2479 X_program_application_id => NULL,
2480 X_program_id => NULL,
2481 X_request_id => NULL,
2482 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
2483 X_from_category_id => NULL,
2484 X_to_category_id => NULL
2485 );
2486
2487 L_INSTRUCTION_SET_ID := L_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
2488
2489 OPEN L_INSTR_DEFN_CSR;
2490 LOOP
2491 FETCH L_INSTR_DEFN_CSR INTO L_INSTR_DEFN_REC;
2492 EXIT WHEN L_INSTR_DEFN_CSR%NOTFOUND;
2493
2494 SELECT GMO_INSTR_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_ID
2495 FROM DUAL;
2496
2497 --Bug 4730261: start
2498 --if task id is available, and task label is not available, we
2499 --take the task display name and assign to task label.
2500 if (L_INSTR_DEFN_REC.TASK_ID is not null and trim(L_INSTR_DEFN_REC.TASK_LABEL) is null ) then
2501 select display_name into l_task_label from gmo_instr_task_defn_vl where task_id = L_INSTR_DEFN_REC.TASK_ID;
2502 else
2503 l_task_label := L_INSTR_DEFN_REC.TASK_LABEL;
2504 end if;
2505 --Bug 4730261: end
2506 -- INSERT INSTRNS FROM DEFN TABLE TO INSTANCE TABLE
2507 INSERT INTO GMO_INSTR_INSTANCE_VL
2508 (
2509 INSTRUCTION_ID,
2510 INSTRUCTION_SET_ID,
2511 INSTRUCTION_TEXT,
2512 INSTR_STATUS,
2513 COMMENTS,
2514 TASK_LABEL,
2515 INSTR_NUMBER,
2516 INSTR_SEQ,
2517 OPERATOR_ACKN,
2518 INSTR_ACKN_TYPE,
2519 TASK_ID,
2520 TASK_ACKN_DATE,
2521 TASK_ACKN_STATUS,
2522 TASK_ATTRIBUTE,
2523 TASK_ATTRIBUTE_ID,
2524 CREATION_DATE,
2525 CREATED_BY,
2526 LAST_UPDATE_DATE,
2527 LAST_UPDATED_BY,
2528 LAST_UPDATE_LOGIN
2529 )
2530 VALUES
2531 (
2532 L_NEW_INSTRUCTION_ID,
2533 L_NEW_INSTRUCTION_SET_ID,
2534 L_INSTR_DEFN_REC.INSTRUCTION_TEXT,
2535 GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING,
2536 NULL,
2537 l_task_label,
2538 L_INSTR_DEFN_REC.INSTR_NUMBER,
2539 L_INSTR_DEFN_REC.INSTR_SEQ,
2540 GMO_CONSTANTS_GRP.G_INSTR_OPERATOR_ACKN_NO,
2541 L_INSTR_DEFN_REC.INSTR_ACKN_TYPE,
2542 L_INSTR_DEFN_REC.TASK_ID,
2543 NULL,
2544 DECODE( L_INSTR_DEFN_REC.TASK_ID,
2545 NULL,NULL,
2546 GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS ),
2547 --L_INSTR_DEFN_REC.TASK_ATTRIBUTE,
2548 decode ( L_INSTR_DEFN_REC.task_attribute_id, null, null, decode(0, instr( L_INSTR_DEFN_REC.task_attribute_id, gmo_constants_grp.all_attribute), L_INSTR_DEFN_REC.task_attribute, null, null, 'ALL')),
2549 L_INSTR_DEFN_REC.TASK_ATTRIBUTE_ID,
2550 L_CREATION_DATE,
2551 L_CREATED_BY,
2552 L_LAST_UPDATE_DATE,
2553 L_LAST_UPDATED_BY,
2554 L_LAST_UPDATE_LOGIN
2555 );
2556
2557 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
2558 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_B_ENTITY,
2559 X_from_pk1_value => L_INSTR_DEFN_REC.INSTRUCTION_ID,
2560 X_from_pk2_value => NULL,
2561 X_from_pk3_value => NULL,
2562 X_from_pk4_value => NULL,
2563 X_from_pk5_value => NULL,
2564 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_B_ENTITY,
2565 X_to_pk1_value => L_NEW_INSTRUCTION_ID,
2566 X_to_pk2_value => NULL,
2567 X_to_pk3_value => NULL,
2568 X_to_pk4_value => NULL,
2569 X_to_pk5_value => NULL,
2570 X_created_by => L_CREATED_BY,
2571 X_last_update_login => L_LAST_UPDATE_LOGIN,
2572 X_program_application_id => NULL,
2573 X_program_id => NULL,
2574 X_request_id => NULL,
2575 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
2576 X_from_category_id => NULL,
2577 X_to_category_id => NULL
2578 );
2579
2580 L_INSTRUCTION_ID := L_INSTR_DEFN_REC.INSTRUCTION_ID;
2581
2582 OPEN L_INSTR_APPR_DEFN_CSR;
2583
2584 LOOP
2585 FETCH L_INSTR_APPR_DEFN_CSR INTO L_INSTR_APPR_DEFN_REC;
2586 EXIT WHEN L_INSTR_APPR_DEFN_CSR%NOTFOUND;
2587
2588 INSERT INTO GMO_INSTR_APPR_INSTANCE
2589 (
2590 INSTRUCTION_ID,
2591 APPROVER_SEQ,
2592 ROLE_COUNT,
2593 ROLE_NAME,
2594 CREATION_DATE,
2595 CREATED_BY,
2596 LAST_UPDATE_DATE,
2597 LAST_UPDATED_BY,
2598 LAST_UPDATE_LOGIN
2599 )
2600 VALUES
2601 (
2602 L_NEW_INSTRUCTION_ID,
2603 GMO_INSTR_APPR_INSTANCE_S.NEXTVAL,
2604 L_INSTR_APPR_DEFN_REC.ROLE_COUNT,
2605 L_INSTR_APPR_DEFN_REC.ROLE_NAME,
2606 L_CREATION_DATE,
2607 L_CREATED_BY,
2608 L_LAST_UPDATE_DATE,
2609 L_LAST_UPDATED_BY,
2610 L_LAST_UPDATE_LOGIN
2611 );
2612
2613 END LOOP;
2614
2615 CLOSE L_INSTR_APPR_DEFN_CSR;
2616 END LOOP;
2617 CLOSE L_INSTR_DEFN_CSR;
2618 END LOOP;
2619
2620 IF(L_INSTR_SET_DEFN_CSR%ROWCOUNT > 0) THEN
2621 X_INSTRUCTION_SET_ID := L_NEW_INSTRUCTION_SET_ID;
2622 ELSE
2623 -- Definition Instructon Set was not found, hence could
2624 -- not create instance, log the error and return ERROR status
2625
2626 X_INSTRUCTION_SET_ID := -1;
2627
2628 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_CIFD_DIS_NOTFOUND');
2629 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
2630 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
2631
2632 L_MESG_TEXT := FND_MESSAGE.GET();
2633
2634 FND_MSG_PUB.ADD_EXC_MSG
2635 ( G_PKG_NAME,
2636 L_API_NAME,
2637 L_MESG_TEXT
2638 );
2639
2640 FND_MSG_PUB.COUNT_AND_GET
2641 (
2642 P_COUNT => X_MSG_COUNT,
2643 P_DATA => X_MSG_DATA
2644 );
2645
2646 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2647 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2648 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_INSTANCE_FROM_DEFIN',
2649 FALSE);
2650 END IF;
2651
2652 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2653 END IF;
2654
2655 CLOSE L_INSTR_SET_DEFN_CSR;
2656 --Bug 4730261: start
2657 RAISE_INSTR_SET_EVENT(P_INSTRUCTION_SET_ID => X_INSTRUCTION_SET_ID);
2658 --Bug 4730261: end
2659 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2660
2661 EXCEPTION
2662 WHEN OTHERS THEN
2663 X_INSTRUCTION_SET_ID := -1;
2664 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2665
2666 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2667 FND_MSG_PUB.ADD_EXC_MSG
2668 (G_PKG_NAME,
2669 L_API_NAME);
2670 END IF;
2671
2672 FND_MSG_PUB.COUNT_AND_GET
2673 ( P_COUNT => X_MSG_COUNT,
2674 P_DATA => X_MSG_DATA);
2675
2676 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2677 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
2678 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_INSTANCE_FROM_DEFN',
2679 FALSE);
2680 END IF;
2681
2682 END CREATE_INSTANCE_FROM_DEFN;
2683
2684 -- This API is called to create instance from instance
2685 -- It creates a copy of instance from existing instance
2686 PROCEDURE CREATE_INSTANCE_FROM_INSTANCE
2687 (
2688 P_SOURCE_ENTITY_NAME IN VARCHAR2,
2689 P_SOURCE_ENTITY_KEY IN VARCHAR2,
2690 P_TARGET_ENTITY_KEY IN VARCHAR2,
2691 P_INSTRUCTION_TYPE IN VARCHAR2,
2692 X_INSTRUCTION_SET_ID OUT NOCOPY NUMBER,
2693 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2694 X_MSG_COUNT OUT NOCOPY NUMBER,
2695 X_MSG_DATA OUT NOCOPY VARCHAR2
2696 )
2697 IS
2698 L_CREATION_DATE DATE;
2699 L_CREATED_BY NUMBER;
2700 L_LAST_UPDATE_DATE DATE;
2701 L_LAST_UPDATED_BY NUMBER;
2702 L_LAST_UPDATE_LOGIN NUMBER;
2703
2704 L_INSTRUCTION_SET_ID NUMBER;
2705 L_INSTRUCTION_ID NUMBER;
2706
2707 L_NEW_INSTRUCTION_SET_ID NUMBER;
2708 L_NEW_INSTRUCTION_ID NUMBER;
2709
2710 --Bug 5013199: start
2711 --The where clause had check for instruction set status, which should
2712 --not be there, we check in the procedure for cancel status
2713 --Removed the clause
2714 --Bug 5013199: end
2715 CURSOR L_INSTR_SET_CSR IS
2716 SELECT INSTRUCTION_SET_ID, INSTRUCTION_TYPE, ENTITY_NAME, ENTITY_KEY,
2717 INSTR_SET_NAME, INSTR_SET_DESC, ACKN_STATUS, ORIG_SOURCE, ORIG_SOURCE_ID
2718 FROM GMO_INSTR_SET_INSTANCE_VL
2719 WHERE INSTRUCTION_SET_ID = (SELECT MAX(INSTRUCTION_SET_ID)
2720 FROM GMO_INSTR_SET_INSTANCE_B
2721 WHERE ENTITY_NAME = P_SOURCE_ENTITY_NAME AND ENTITY_KEY = P_SOURCE_ENTITY_KEY AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE);
2722
2723 CURSOR L_INSTR_CSR IS
2724 SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID, INSTRUCTION_TEXT, INSTR_STATUS,
2725 COMMENTS, INSTR_NUMBER, INSTR_SEQ, OPERATOR_ACKN, INSTR_ACKN_TYPE, TASK_ID,
2726 TASK_ACKN_DATE, TASK_ACKN_STATUS, TASK_LABEL, TASK_ATTRIBUTE, TASK_ATTRIBUTE_ID
2727 FROM GMO_INSTR_INSTANCE_VL
2728 WHERE
2729 INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
2730
2731 CURSOR L_INSTR_APPR_CSR IS
2732 SELECT INSTRUCTION_ID, APPROVER_SEQ, ROLE_COUNT, ROLE_NAME
2733 FROM GMO_INSTR_APPR_INSTANCE
2734 WHERE
2735 INSTRUCTION_ID = L_INSTRUCTION_ID;
2736
2737 L_INSTR_SET_REC L_INSTR_SET_CSR%ROWTYPE;
2738 L_INSTR_REC L_INSTR_CSR%ROWTYPE;
2739 L_INSTR_APPR_REC L_INSTR_APPR_CSR%ROWTYPE;
2740
2741 L_CNT NUMBER;
2742
2743 L_SOURCE_ENTITY_ERR EXCEPTION;
2744 L_DUPLICATE_ENTITY_KEY_ERR EXCEPTION;
2745 L_ENTITY_NOTFOUND_ERR EXCEPTION;
2746
2747 L_INSTR_SET_STATUS VARCHAR2(40);
2748 L_API_NAME VARCHAR2(40);
2749 L_MESG_TEXT VARCHAR2(1000);
2750
2751 BEGIN
2752
2753 GMO_UTILITIES.GET_WHO_COLUMNS
2754 (
2755 X_CREATION_DATE => L_CREATION_DATE,
2756 X_CREATED_BY => L_CREATED_BY,
2757 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
2758 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
2759 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
2760 );
2761
2762 -- Do validation
2763 IF(P_SOURCE_ENTITY_NAME IS NULL) OR (P_SOURCE_ENTITY_KEY IS NULL)
2764 OR (P_INSTRUCTION_TYPE IS NULL) THEN
2765 RAISE L_SOURCE_ENTITY_ERR;
2766 END IF;
2767
2768 -- if the source and target entity key are same, "valid"
2769 -- this case is executed, when source instr set is
2770 -- nullified, and a new instruction set is to be
2771 -- created from source
2772 IF(P_SOURCE_ENTITY_KEY = P_TARGET_ENTITY_KEY) THEN
2773 BEGIN
2774
2775 -- first check if the current instruction set is
2776 -- nullified, if it is nullified, allow same entity
2777 -- key
2778 SELECT INSTRUCTION_SET_ID, INSTR_SET_STATUS
2779 INTO L_INSTRUCTION_SET_ID, L_INSTR_SET_STATUS
2780 FROM GMO_INSTR_SET_INSTANCE_VL
2781 WHERE INSTRUCTION_SET_ID = (SELECT MAX(INSTRUCTION_SET_ID)
2782 FROM GMO_INSTR_SET_INSTANCE_B
2783 WHERE ENTITY_NAME = P_SOURCE_ENTITY_NAME AND ENTITY_KEY = P_SOURCE_ENTITY_KEY AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE);
2784
2785 -- if the instruction instance already exist for the given
2786 -- target entity name and entity_key, it will exist
2787 -- as source and target entity_key are same,
2788 -- return error if the instr set is not nullified (already active),
2789 -- if it is nullified, a new instruction set is
2790 -- created, further in the api
2791 IF(L_INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE) THEN
2792 -- return an error conveying source instruction
2793 -- set already active
2794 X_INSTRUCTION_SET_ID := -1;
2795 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2796
2797 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SET_ALREADY_ACTIVE');
2798 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
2799 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME);
2800
2801 L_MESG_TEXT := FND_MESSAGE.GET();
2802
2803 FND_MSG_PUB.ADD_EXC_MSG
2804 ( G_PKG_NAME,
2805 L_API_NAME,
2806 L_MESG_TEXT
2807 );
2808
2809 FND_MSG_PUB.COUNT_AND_GET
2810 (
2811 P_COUNT => X_MSG_COUNT,
2812 P_DATA => X_MSG_DATA
2813 );
2814
2815 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2816 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2817 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_DEFN_CONTEXT',
2818 FALSE);
2819 END IF;
2820
2821 RETURN;
2822 END IF;
2823
2824 EXCEPTION
2825 WHEN NO_DATA_FOUND THEN
2826 L_INSTRUCTION_SET_ID := -1;
2827 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2828 RETURN;
2829 END;
2830 END IF;
2831
2832 -- if the source and target entity key are different
2833 IF(P_TARGET_ENTITY_KEY IS NOT NULL AND P_SOURCE_ENTITY_KEY <> P_TARGET_ENTITY_KEY) THEN
2834 BEGIN
2835 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
2836 FROM GMO_INSTR_SET_INSTANCE_VL
2837 WHERE
2838 ENTITY_NAME = P_SOURCE_ENTITY_NAME
2839 AND ENTITY_KEY = P_TARGET_ENTITY_KEY
2840 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
2841 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
2842 EXCEPTION
2843 WHEN NO_DATA_FOUND THEN
2844 L_INSTRUCTION_SET_ID := -1;
2845 END;
2846
2847 -- if the instruction instance already exist for the given
2848 -- target entity name and entity_key, return this instruction_set_id
2849 IF (L_INSTRUCTION_SET_ID > 0) THEN
2850 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2851 X_INSTRUCTION_SET_ID := L_INSTRUCTION_SET_ID;
2852 RETURN;
2853 END IF;
2854 END IF;
2855
2856 -- Only 1 instruction set can be there.
2857 OPEN L_INSTR_SET_CSR;
2858 FETCH L_INSTR_SET_CSR INTO L_INSTR_SET_REC;
2859
2860 IF (L_INSTR_SET_CSR%ROWCOUNT <= 0) THEN
2861 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2862 X_INSTRUCTION_SET_ID := -1;
2863 RETURN;
2864 END IF;
2865
2866 SELECT GMO_INSTR_SET_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_SET_ID
2867 FROM DUAL;
2868
2869 INSERT INTO GMO_INSTR_SET_INSTANCE_VL
2870 (
2871 INSTRUCTION_SET_ID,
2872 INSTRUCTION_TYPE,
2873 ENTITY_NAME,
2874 ENTITY_KEY,
2875 INSTR_SET_NAME,
2876 INSTR_SET_DESC,
2877 ACKN_STATUS,
2878 INSTR_SET_STATUS,
2879 ORIG_SOURCE,
2880 ORIG_SOURCE_ID,
2881 CREATION_DATE,
2882 CREATED_BY,
2883 LAST_UPDATE_DATE,
2884 LAST_UPDATED_BY,
2885 LAST_UPDATE_LOGIN
2886 )
2887 VALUES
2888 (
2889 L_NEW_INSTRUCTION_SET_ID,
2890 L_INSTR_SET_REC.INSTRUCTION_TYPE,
2891 P_SOURCE_ENTITY_NAME,
2892 NVL(P_TARGET_ENTITY_KEY, GMO_CONSTANTS_GRP.G_INSTR_PREFIX || L_NEW_INSTRUCTION_SET_ID),
2893 L_INSTR_SET_REC.INSTR_SET_NAME,
2894 L_INSTR_SET_REC.INSTR_SET_DESC,
2895 GMO_CONSTANTS_GRP.G_INSTR_SET_UNACKN_STATUS,
2896 GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE,
2897 GMO_CONSTANTS_GRP.G_ORIG_SOURCE_INSTANCE,
2898 L_INSTR_SET_REC.INSTRUCTION_SET_ID,
2899 L_CREATION_DATE,
2900 L_CREATED_BY,
2901 L_LAST_UPDATE_DATE,
2902 L_LAST_UPDATED_BY,
2903 L_LAST_UPDATE_LOGIN
2904 );
2905
2906 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
2907 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_INSTANCE_B_ENTITY,
2908 X_from_pk1_value => L_INSTR_SET_REC.INSTRUCTION_SET_ID,
2909 X_from_pk2_value => NULL,
2910 X_from_pk3_value => NULL,
2911 X_from_pk4_value => NULL,
2912 X_from_pk5_value => NULL,
2913 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_INSTANCE_B_ENTITY,
2914 X_to_pk1_value => L_NEW_INSTRUCTION_SET_ID,
2915 X_to_pk2_value => NULL,
2916 X_to_pk3_value => NULL,
2917 X_to_pk4_value => NULL,
2918 X_to_pk5_value => NULL,
2919 X_created_by => L_CREATED_BY,
2920 X_last_update_login => L_LAST_UPDATE_LOGIN,
2921 X_program_application_id => NULL,
2922 X_program_id => NULL,
2923 X_request_id => NULL,
2924 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
2925 X_from_category_id => NULL,
2926 X_to_category_id => NULL
2927 );
2928
2929 L_INSTRUCTION_SET_ID := L_INSTR_SET_REC.INSTRUCTION_SET_ID;
2930
2931 OPEN L_INSTR_CSR;
2932 LOOP
2933 FETCH L_INSTR_CSR INTO L_INSTR_REC;
2934 EXIT WHEN L_INSTR_CSR%NOTFOUND;
2935
2936 SELECT GMO_INSTR_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_ID
2937 FROM DUAL;
2938
2939 INSERT INTO GMO_INSTR_INSTANCE_VL
2940 (
2941 INSTRUCTION_ID,
2942 INSTRUCTION_SET_ID,
2943 INSTRUCTION_TEXT,
2944 INSTR_STATUS,
2945 COMMENTS,
2946 TASK_LABEL,
2947 INSTR_NUMBER,
2948 INSTR_SEQ,
2949 OPERATOR_ACKN,
2950 INSTR_ACKN_TYPE,
2951 TASK_ID,
2952 TASK_ACKN_DATE,
2953 TASK_ACKN_STATUS,
2954 TASK_ATTRIBUTE,
2955 TASK_ATTRIBUTE_ID,
2956 CREATION_DATE,
2957 CREATED_BY,
2958 LAST_UPDATE_DATE,
2959 LAST_UPDATED_BY,
2960 LAST_UPDATE_LOGIN
2961 )
2962 VALUES
2963 (
2964 L_NEW_INSTRUCTION_ID,
2965 L_NEW_INSTRUCTION_SET_ID,
2966 L_INSTR_REC.INSTRUCTION_TEXT,
2967 GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING,
2968 NULL,
2969 L_INSTR_REC.TASK_LABEL,
2970 L_INSTR_REC.INSTR_NUMBER,
2971 L_INSTR_REC.INSTR_SEQ,
2972 GMO_CONSTANTS_GRP.G_INSTR_OPERATOR_ACKN_NO,
2973 L_INSTR_REC.INSTR_ACKN_TYPE,
2974 L_INSTR_REC.TASK_ID,
2975 NULL,
2976 DECODE(L_INSTR_REC.TASK_ID,NULL,NULL,GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS),
2977 L_INSTR_REC.TASK_ATTRIBUTE,
2978 L_INSTR_REC.TASK_ATTRIBUTE_ID,
2979 L_CREATION_DATE,
2980 L_CREATED_BY,
2981 L_LAST_UPDATE_DATE,
2982 L_LAST_UPDATED_BY,
2983 L_LAST_UPDATE_LOGIN
2984 );
2985
2986 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
2987 X_from_entity_name => GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_B_ENTITY,
2988 X_from_pk1_value => L_INSTR_REC.INSTRUCTION_ID,
2989 X_from_pk2_value => NULL,
2990 X_from_pk3_value => NULL,
2991 X_from_pk4_value => NULL,
2992 X_from_pk5_value => NULL,
2993 X_to_entity_name => GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_B_ENTITY,
2994 X_to_pk1_value => L_NEW_INSTRUCTION_ID,
2995 X_to_pk2_value => NULL,
2996 X_to_pk3_value => NULL,
2997 X_to_pk4_value => NULL,
2998 X_to_pk5_value => NULL,
2999 X_created_by => L_CREATED_BY,
3000 X_last_update_login => L_LAST_UPDATE_LOGIN,
3001 X_program_application_id => NULL,
3002 X_program_id => NULL,
3003 X_request_id => NULL,
3004 X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
3005 X_from_category_id => NULL,
3006 X_to_category_id => NULL
3007 );
3008
3009 L_INSTRUCTION_ID := L_INSTR_REC.INSTRUCTION_ID;
3010
3011 OPEN L_INSTR_APPR_CSR;
3012 LOOP
3013 FETCH L_INSTR_APPR_CSR INTO L_INSTR_APPR_REC;
3014 EXIT WHEN L_INSTR_APPR_CSR%NOTFOUND;
3015
3016 INSERT INTO GMO_INSTR_APPR_INSTANCE
3017 (
3018 INSTRUCTION_ID,
3019 APPROVER_SEQ,
3020 ROLE_COUNT,
3021 ROLE_NAME,
3022 CREATION_DATE,
3023 CREATED_BY,
3024 LAST_UPDATE_DATE,
3025 LAST_UPDATED_BY,
3026 LAST_UPDATE_LOGIN
3027 )
3028 VALUES
3029 (
3030 L_NEW_INSTRUCTION_ID,
3031 GMO_INSTR_APPR_INSTANCE_S.NEXTVAL,
3032 L_INSTR_APPR_REC.ROLE_COUNT,
3033 L_INSTR_APPR_REC.ROLE_NAME,
3034 L_CREATION_DATE,
3035 L_CREATED_BY,
3036 L_LAST_UPDATE_DATE,
3037 L_LAST_UPDATED_BY,
3038 L_LAST_UPDATE_LOGIN
3039 );
3040 END LOOP;
3041 CLOSE L_INSTR_APPR_CSR;
3042
3043 END LOOP;
3044 CLOSE L_INSTR_CSR;
3045 CLOSE L_INSTR_SET_CSR;
3046
3047 -- If the L_NEW_INSTRUCTION_SET_ID is NULL, return -1
3048 X_INSTRUCTION_SET_ID := NVL(L_NEW_INSTRUCTION_SET_ID,-1);
3049 --Bug 4730261: start
3050 RAISE_INSTR_SET_EVENT(P_INSTRUCTION_SET_ID => X_INSTRUCTION_SET_ID);
3051 --Bug 4730261: end
3052 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3053
3054 EXCEPTION
3055 WHEN NO_DATA_FOUND THEN
3056 X_INSTRUCTION_SET_ID := -1;
3057 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3058
3059 WHEN OTHERS THEN
3060 X_INSTRUCTION_SET_ID := -1;
3061 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3062
3063 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3064 FND_MSG_PUB.ADD_EXC_MSG
3065 (G_PKG_NAME,
3066 L_API_NAME);
3067 END IF;
3068
3069 FND_MSG_PUB.COUNT_AND_GET
3070 ( P_COUNT => X_MSG_COUNT,
3071 P_DATA => X_MSG_DATA);
3072
3073 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3074 FND_LOG.MESSAGE ( FND_LOG.LEVEL_UNEXPECTED,
3075 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_INSTANCE_FROM_INSTANCE',
3076 FALSE );
3077 END IF;
3078
3079 END CREATE_INSTANCE_FROM_INSTANCE;
3080
3081 -- This API is called to get mode for entity. The mode for entity is returned READ if all
3082 -- instructions are compeleted in instruction set. It is UPDATE is some instructions are
3083 -- pending, and INSERT if there are no instruction defined
3084
3085 PROCEDURE GET_MODE_FOR_ENTITY
3086 (
3087 P_ENTITY_NAME IN VARCHAR2,
3088 P_ENTITY_KEY IN VARCHAR2,
3089 P_INSTRUCTION_TYPE IN VARCHAR2,
3090 X_MODE OUT NOCOPY VARCHAR2,
3091 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3092 X_MSG_COUNT OUT NOCOPY NUMBER,
3093 X_MSG_DATA OUT NOCOPY VARCHAR2
3094 )
3095 IS
3096 L_INSTRUCTION_SET_ID NUMBER;
3097 L_INSTR_COUNT NUMBER;
3098 L_INSTR_SET_COUNT NUMBER;
3099 L_ENTITY_ERR EXCEPTION;
3100
3101 L_API_NAME VARCHAR2(40);
3102 L_MESG_TEXT VARCHAR2(1000);
3103
3104 BEGIN
3105 L_API_NAME := 'GET_MODE_FOR_ENTITY';
3106
3107 IF(P_ENTITY_NAME IS NULL) OR (P_ENTITY_KEY IS NULL)
3108 OR (P_INSTRUCTION_TYPE IS NULL) THEN
3109 RAISE L_ENTITY_ERR;
3110 END IF;
3111
3112 SELECT COUNT(INSTRUCTION_SET_ID) INTO L_INSTR_SET_COUNT
3113 FROM GMO_INSTR_SET_INSTANCE_VL
3114 WHERE ENTITY_NAME = P_ENTITY_NAME
3115 AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
3116 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
3117 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
3118
3119 IF (L_INSTR_SET_COUNT = 0 ) THEN
3120 X_MODE := GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_MODE_INSERT;
3121 ELSE
3122 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
3123 FROM GMO_INSTR_SET_INSTANCE_VL
3124 WHERE ENTITY_NAME = P_ENTITY_NAME
3125 AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
3126 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
3127 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
3128
3129 SELECT COUNT(INSTRUCTION_ID) INTO L_INSTR_COUNT
3130 FROM GMO_INSTR_INSTANCE_VL WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3131
3132 IF (L_INSTR_COUNT > 0) THEN
3133 SELECT COUNT(INSTRUCTION_ID) INTO L_INSTR_COUNT
3134 FROM GMO_INSTR_INSTANCE_VL WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
3135 AND ( INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
3136 OR INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE );
3137
3138 IF (L_INSTR_COUNT > 0) THEN
3139 X_MODE := GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_MODE_UPDATE;
3140 ELSE
3141 X_MODE := GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_MODE_READ;
3142 END IF;
3143 ELSE
3144 X_MODE := GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_MODE_READ;
3145 END IF;
3146 END IF;
3147
3148 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3149
3150 EXCEPTION
3151 WHEN L_ENTITY_ERR THEN
3152 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3153
3154 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SOURCE_ENTITY_ER');
3155 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
3156 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
3157
3158 L_MESG_TEXT := FND_MESSAGE.GET();
3159
3160 FND_MSG_PUB.ADD_EXC_MSG
3161 ( G_PKG_NAME,
3162 L_API_NAME,
3163 L_MESG_TEXT
3164 );
3165
3166 FND_MSG_PUB.COUNT_AND_GET
3167 (
3168 P_COUNT => X_MSG_COUNT,
3169 P_DATA => X_MSG_DATA
3170 );
3171
3172 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3173 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3174 'gmo.plsql.GMO_INSTRUCTIONS_PVT.GET_MODE_FOR_ENTITY',
3175 FALSE);
3176 END IF;
3177
3178 WHEN OTHERS THEN
3179
3180 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3181
3182 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3183 FND_MSG_PUB.ADD_EXC_MSG
3184 (G_PKG_NAME,
3185 L_API_NAME);
3186 END IF;
3187
3188 FND_MSG_PUB.COUNT_AND_GET
3189 ( P_COUNT => X_MSG_COUNT,
3190 P_DATA => X_MSG_DATA);
3191
3192 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3193 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3194 'gmo.plsql.GMO_INSTRUCTION_PVT.GET_MODE_FOR_ENTITY',
3195 FALSE);
3196 END IF;
3197
3198 END GET_MODE_FOR_ENTITY;
3199
3200 -- This API is called to get the list of all instructions
3201 -- in a single table type for given entity name, entity key
3202 -- and instruction type. It returns Definition Time Instructions.
3203
3204 PROCEDURE GET_DEFN_INSTRUCTIONS
3205 (
3206 P_ENTITY_NAME IN VARCHAR2,
3207 P_ENTITY_KEY IN VARCHAR2,
3208 P_INSTRUCTION_TYPE VARCHAR2,
3209 X_INSTRUCTION_TABLE OUT NOCOPY GMO_DATATYPES_GRP.GMO_INSTRUCTION_TBL_TYPE,
3210 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3211 X_MSG_COUNT OUT NOCOPY NUMBER,
3212 X_MSG_DATA OUT NOCOPY VARCHAR2
3213 )
3214 IS
3215 L_INSTRUCTION_SET_ID NUMBER;
3216 L_INSTRUCTION_REC GMO_DATATYPES_GRP.GMO_INSTRUCTION_REC_TYPE;
3217 L_INSTRUCTION_TBL GMO_DATATYPES_GRP.GMO_INSTRUCTION_TBL_TYPE;
3218
3219 CURSOR L_INSTR_CSR IS
3220 SELECT INSTR_DEFN.INSTRUCTION_ID, INSTR_DEFN.INSTRUCTION_SET_ID,
3221 INSTR_DEFN.INSTRUCTION_TEXT,
3222 INSTR_DEFN.TASK_ATTRIBUTE, TSK.TASK_NAME, TSK.DISPLAY_NAME
3223 FROM GMO_INSTR_DEFN_VL INSTR_DEFN, GMO_INSTR_TASK_DEFN_VL TSK
3224 WHERE INSTR_DEFN.INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
3225 AND INSTR_DEFN.TASK_ID = TSK.TASK_ID;
3226
3227 L_INSTR_REC L_INSTR_CSR%ROWTYPE;
3228 L_INSTR_CNT NUMBER;
3229
3230 L_ENTITY_PARAM_ERR EXCEPTION;
3231 L_INVALID_ENTITY_ERR EXCEPTION;
3232
3233 L_API_NAME VARCHAR2(40);
3234 L_MESG_TEXT VARCHAR2(1000);
3235
3236 BEGIN
3237
3238 L_API_NAME := 'GET_DEFN_INSTRUCTIONS';
3239
3240 IF ((P_ENTITY_NAME IS NULL) OR (P_ENTITY_KEY IS NULL)
3241 OR (P_INSTRUCTION_TYPE IS NULL) ) THEN
3242 RAISE L_ENTITY_PARAM_ERR;
3243 END IF;
3244
3245 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
3246 FROM GMO_INSTR_SET_DEFN_VL
3247 WHERE
3248 ENTITY_NAME = P_ENTITY_NAME
3249 AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
3250 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
3251
3252 IF(SQL%NOTFOUND) THEN
3253 RAISE L_INVALID_ENTITY_ERR;
3254 END IF;
3255
3256 L_INSTR_CNT := 0;
3257
3258 OPEN L_INSTR_CSR;
3259 LOOP
3260 FETCH L_INSTR_CSR INTO L_INSTR_REC;
3261 EXIT WHEN L_INSTR_CSR%NOTFOUND;
3262
3263 L_INSTR_CNT := L_INSTR_CNT + 1;
3264
3265 L_INSTRUCTION_REC.INSTRUCTION_ID := L_INSTR_REC.INSTRUCTION_ID;
3266 L_INSTRUCTION_REC.INSTRUCTION_SET_ID := L_INSTR_REC.INSTRUCTION_SET_ID;
3267 L_INSTRUCTION_REC.INSTRUCTION_TEXT := L_INSTR_REC.INSTRUCTION_TEXT;
3268 L_INSTRUCTION_REC.TASK_ATTRIBUTE := L_INSTR_REC.TASK_ATTRIBUTE;
3269 L_INSTRUCTION_REC.TASK_NAME := L_INSTR_REC.TASK_NAME;
3270 L_INSTRUCTION_REC.TASK_DISPLAY_NAME := L_INSTR_REC.DISPLAY_NAME;
3271
3272 L_INSTRUCTION_TBL(L_INSTR_CNT) := L_INSTRUCTION_REC;
3273
3274 END LOOP;
3275 CLOSE L_INSTR_CSR;
3276
3277 X_INSTRUCTION_TABLE := L_INSTRUCTION_TBL;
3278 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3279
3280 EXCEPTION
3281 WHEN L_ENTITY_PARAM_ERR THEN
3282 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_API_PARAM_ERR');
3283 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
3284 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
3285
3286 L_MESG_TEXT := FND_MESSAGE.GET();
3287
3288 FND_MSG_PUB.ADD_EXC_MSG
3289 ( G_PKG_NAME,
3290 L_API_NAME,
3291 L_MESG_TEXT
3292 );
3293
3294 FND_MSG_PUB.COUNT_AND_GET
3295 (
3296 P_COUNT => X_MSG_COUNT,
3297 P_DATA => X_MSG_DATA
3298 );
3299
3300 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3301 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3302 'gmo.plsql.GMO_INSTRUCTIONS_PVT.GET_DEFN_INSTRUCTIONS',
3303 FALSE);
3304 END IF;
3305
3306 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3307
3308 WHEN L_INVALID_ENTITY_ERR THEN
3309 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SOURCE_ENTITY_ER');
3310 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
3311 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
3312
3313 L_MESG_TEXT := FND_MESSAGE.GET();
3314
3315 FND_MSG_PUB.ADD_EXC_MSG
3316 ( G_PKG_NAME,
3317 L_API_NAME,
3318 L_MESG_TEXT
3319 );
3320
3321 FND_MSG_PUB.COUNT_AND_GET
3322 (
3323 P_COUNT => X_MSG_COUNT,
3324 P_DATA => X_MSG_DATA
3325 );
3326
3327 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3328 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3329 'gmo.plsql.GMO_INSTRUCTIONS_PVT.GET_DEFN_INSTRUCTIONS',
3330 FALSE);
3331 END IF;
3332
3333 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3334
3335 WHEN OTHERS THEN
3336
3337 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3338 FND_MSG_PUB.ADD_EXC_MSG
3339 (G_PKG_NAME,
3340 L_API_NAME);
3341 END IF;
3342
3343 FND_MSG_PUB.COUNT_AND_GET
3344 ( P_COUNT => X_MSG_COUNT,
3345 P_DATA => X_MSG_DATA);
3346
3347 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3348 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3349 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_CONTEXT',
3350 FALSE);
3351 END IF;
3352
3353 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3354
3355 END GET_DEFN_INSTRUCTIONS;
3356
3357 -- This API is checks if there are any pending instructions for
3358 -- the given entity name, key and instruction type on instance
3359 -- permenant tables
3360
3361 PROCEDURE HAS_PENDING_INSTRUCTIONS
3362 (
3363 P_ENTITY_NAME IN VARCHAR2,
3364 P_ENTITY_KEY IN VARCHAR2,
3365 P_INSTRUCTION_TYPE IN VARCHAR2,
3366 X_INSTRUCTION_PENDING OUT NOCOPY VARCHAR2,
3367 X_TOTAL_INSTRUCTIONS OUT NOCOPY NUMBER,
3368 X_OPTIONAL_PENDING_INSTR OUT NOCOPY NUMBER,
3369 X_MANDATORY_PENDING_INSTR OUT NOCOPY NUMBER,
3370 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3371 X_MSG_COUNT OUT NOCOPY NUMBER,
3372 X_MSG_DATA OUT NOCOPY VARCHAR2
3373 )
3374 IS
3375 L_TOTAL_INSTRUCTIONS NUMBER;
3376 L_OPTIONAL_PENDING_INSTR NUMBER;
3377 L_MANDATORY_PENDING_INSTR NUMBER;
3378
3379 L_INSTRUCTION_SET_ID NUMBER;
3380 L_INSTR_OPTIONAL VARCHAR2(40);
3381 L_INSTR_PROCEED_ALLOWED VARCHAR2(40);
3382 L_INSTR_MANDATORY VARCHAR2(40);
3383
3384 L_ENTITY_ERR EXCEPTION;
3385
3386 L_API_NAME VARCHAR2(40);
3387 L_MESG_TEXT VARCHAR2(1000);
3388
3389 BEGIN
3390
3391 L_API_NAME := 'HAS_PENDING_INSTRUCTIONS';
3392
3393 -- Validation for Entity
3394 IF(P_ENTITY_NAME IS NULL) OR (P_ENTITY_KEY IS NULL)
3395 OR (P_INSTRUCTION_TYPE IS NULL) THEN
3396 RAISE L_ENTITY_ERR;
3397 END IF;
3398
3399 L_INSTR_MANDATORY := GMO_CONSTANTS_GRP.G_INSTR_MANDATORY;
3400 L_INSTR_OPTIONAL := GMO_CONSTANTS_GRP.G_INSTR_OPTIONAL;
3401 L_INSTR_PROCEED_ALLOWED := GMO_CONSTANTS_GRP.G_INSTR_PROCEED_ALLOWED;
3402
3403 -- Check if the instruction set exists, get instruction set id
3404 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
3405 FROM GMO_INSTR_SET_INSTANCE_VL
3406 WHERE ENTITY_NAME = P_ENTITY_NAME
3407 AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
3408 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
3409 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
3410
3411 SELECT COUNT(INSTRUCTION_ID) INTO L_TOTAL_INSTRUCTIONS
3412 FROM GMO_INSTR_INSTANCE_VL
3413 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3414
3415 SELECT COUNT(INSTRUCTION_ID) INTO L_OPTIONAL_PENDING_INSTR
3416 FROM GMO_INSTR_INSTANCE_VL
3417 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
3418 AND INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
3419 AND ( INSTR_ACKN_TYPE = L_INSTR_OPTIONAL );
3420
3421 SELECT COUNT(INSTRUCTION_ID) INTO L_MANDATORY_PENDING_INSTR
3422 FROM GMO_INSTR_INSTANCE_VL
3423 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
3424 AND INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
3425 AND ( INSTR_ACKN_TYPE = L_INSTR_MANDATORY OR INSTR_ACKN_TYPE = L_INSTR_PROCEED_ALLOWED);
3426
3427 X_TOTAL_INSTRUCTIONS := L_TOTAL_INSTRUCTIONS;
3428 X_OPTIONAL_PENDING_INSTR := L_OPTIONAL_PENDING_INSTR;
3429 X_MANDATORY_PENDING_INSTR := L_MANDATORY_PENDING_INSTR;
3430
3431 IF ((X_OPTIONAL_PENDING_INSTR + X_MANDATORY_PENDING_INSTR ) > 0)
3432 THEN
3433 X_INSTRUCTION_PENDING := GMO_CONSTANTS_GRP.YES;
3434 ELSE
3435 X_INSTRUCTION_PENDING := GMO_CONSTANTS_GRP.NO;
3436 END IF;
3437
3438 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3439
3440 EXCEPTION
3441 WHEN NO_DATA_FOUND THEN
3442 -- If Process Id is invalid, NO_DATA_FOUND
3443 -- Exception is thrown, in that case also,
3444 -- return all values as 0, and status = S
3445 X_TOTAL_INSTRUCTIONS := 0;
3446 X_OPTIONAL_PENDING_INSTR := 0;
3447 X_MANDATORY_PENDING_INSTR := 0;
3448 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3449
3450 WHEN L_ENTITY_ERR THEN
3451 X_TOTAL_INSTRUCTIONS := 0;
3452 X_OPTIONAL_PENDING_INSTR := 0;
3453 X_MANDATORY_PENDING_INSTR := 0;
3454
3455 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_API_PARAM_ERR');
3456 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
3457 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
3458
3459 L_MESG_TEXT := FND_MESSAGE.GET();
3460
3461 FND_MSG_PUB.ADD_EXC_MSG
3462 ( G_PKG_NAME,
3463 L_API_NAME,
3464 L_MESG_TEXT
3465 );
3466
3467 FND_MSG_PUB.COUNT_AND_GET
3468 (
3469 P_COUNT => X_MSG_COUNT,
3470 P_DATA => X_MSG_DATA
3471 );
3472
3473 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3474 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3475 'gmo.plsql.GMO_INSTRUCTIONS_PVT.HAS_PENDING_INSTRUCTIONS',
3476 FALSE);
3477 END IF;
3478
3479 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3480
3481 WHEN OTHERS THEN
3482 X_TOTAL_INSTRUCTIONS := 0;
3483 X_OPTIONAL_PENDING_INSTR := 0;
3484 X_MANDATORY_PENDING_INSTR := 0;
3485
3486 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3487 FND_MSG_PUB.ADD_EXC_MSG
3488 (G_PKG_NAME,
3489 L_API_NAME);
3490 END IF;
3491
3492 FND_MSG_PUB.COUNT_AND_GET
3493 ( P_COUNT => X_MSG_COUNT,
3494 P_DATA => X_MSG_DATA
3495 );
3496
3497 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3498 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3499 'gmo.plsql.GMO_INSTRUCTION_PVT.HAS_PENDING_INSTRUCTIONS',
3500 FALSE);
3501 END IF;
3502
3503 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3504
3505 END HAS_PENDING_INSTRUCTIONS;
3506
3507 -- This API is checks if there are any pending instructions for
3508 -- the given entity name, key and instruction type on instance
3509 -- process / temporary tables
3510 PROCEDURE HAS_PENDING_INSTR_FOR_PROCESS
3511 (
3512 P_INSTRUCTION_PROCESS_ID IN VARCHAR2,
3513 X_INSTRUCTION_PENDING OUT NOCOPY VARCHAR2,
3514 X_TOTAL_INSTRUCTIONS OUT NOCOPY NUMBER,
3515 X_OPTIONAL_PENDING_INSTR OUT NOCOPY NUMBER,
3516 X_MANDATORY_PENDING_INSTR OUT NOCOPY NUMBER,
3517 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3518 X_MSG_COUNT OUT NOCOPY NUMBER,
3519 X_MSG_DATA OUT NOCOPY VARCHAR2
3520 )
3521 IS
3522
3523 L_TOTAL_INSTRUCTIONS NUMBER;
3524 L_OPTIONAL_PENDING_INSTR NUMBER;
3525 L_MANDATORY_PENDING_INSTR NUMBER;
3526
3527 L_INSTRUCTION_SET_ID NUMBER;
3528 L_INSTR_OPTIONAL VARCHAR2(40);
3529 L_INSTR_PROCEED_ALLOWED VARCHAR2(40);
3530 L_INSTR_MANDATORY VARCHAR2(40);
3531
3532 L_ENTITY_ERR EXCEPTION;
3533
3534 L_API_NAME VARCHAR2(40);
3535 L_MESG_TEXT VARCHAR2(1000);
3536
3537 BEGIN
3538
3539 L_API_NAME := 'HAS_PENDING_INSTR_FOR_PROCESS';
3540
3541 L_INSTR_MANDATORY := GMO_CONSTANTS_GRP.G_INSTR_MANDATORY;
3542 L_INSTR_OPTIONAL := GMO_CONSTANTS_GRP.G_INSTR_OPTIONAL;
3543 L_INSTR_PROCEED_ALLOWED := GMO_CONSTANTS_GRP.G_INSTR_PROCEED_ALLOWED;
3544
3545 SELECT COUNT(INSTRUCTION_ID) INTO L_TOTAL_INSTRUCTIONS
3546 FROM GMO_INSTR_INSTANCE_T
3547 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
3548
3549 SELECT COUNT(TEMP.INSTRUCTION_ID) INTO L_OPTIONAL_PENDING_INSTR
3550 FROM GMO_INSTR_INSTANCE_T TEMP, GMO_INSTR_INSTANCE_VL PERM
3551 WHERE TEMP.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
3552 AND TEMP.INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
3553 AND TEMP.INSTRUCTION_ID = PERM.INSTRUCTION_ID
3554 AND ( PERM.INSTR_ACKN_TYPE = L_INSTR_OPTIONAL);
3555
3556 SELECT COUNT(TEMP.INSTRUCTION_ID) INTO L_MANDATORY_PENDING_INSTR
3557 FROM GMO_INSTR_INSTANCE_VL PERM, GMO_INSTR_INSTANCE_T TEMP
3558 WHERE TEMP.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
3559 AND TEMP.INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
3560 AND TEMP.INSTRUCTION_ID = PERM.INSTRUCTION_ID
3561 AND ( PERM.INSTR_ACKN_TYPE = L_INSTR_MANDATORY OR PERM.INSTR_ACKN_TYPE = L_INSTR_PROCEED_ALLOWED);
3562
3563 X_TOTAL_INSTRUCTIONS := L_TOTAL_INSTRUCTIONS;
3564 X_OPTIONAL_PENDING_INSTR := L_OPTIONAL_PENDING_INSTR;
3565 X_MANDATORY_PENDING_INSTR := L_MANDATORY_PENDING_INSTR;
3566
3567 IF ((X_OPTIONAL_PENDING_INSTR + X_MANDATORY_PENDING_INSTR ) > 0)
3568 THEN
3569 X_INSTRUCTION_PENDING := GMO_CONSTANTS_GRP.YES;
3570 ELSE
3571 X_INSTRUCTION_PENDING := GMO_CONSTANTS_GRP.NO;
3572 END IF;
3573
3574 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3575
3576 EXCEPTION
3577 WHEN NO_DATA_FOUND THEN
3578 -- If Process Id is invalid, NO_DATA_FOUND
3579 -- Exception is thrown, in that case also,
3580 -- return all values as 0, and status = S
3581 X_TOTAL_INSTRUCTIONS := 0;
3582 X_OPTIONAL_PENDING_INSTR := 0;
3583 X_MANDATORY_PENDING_INSTR := 0;
3584 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3585
3586 WHEN L_ENTITY_ERR THEN
3587 X_TOTAL_INSTRUCTIONS := 0;
3588 X_OPTIONAL_PENDING_INSTR := 0;
3589 X_MANDATORY_PENDING_INSTR := 0;
3590
3591 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_API_PARAM_ERR');
3592 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
3593 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
3594
3595 L_MESG_TEXT := FND_MESSAGE.GET();
3596
3597 FND_MSG_PUB.ADD_EXC_MSG
3598 ( G_PKG_NAME,
3599 L_API_NAME,
3600 L_MESG_TEXT
3601 );
3602
3603 FND_MSG_PUB.COUNT_AND_GET
3604 (
3605 P_COUNT => X_MSG_COUNT,
3606 P_DATA => X_MSG_DATA
3607 );
3608
3609 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3610 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3611 'gmo.plsql.GMO_INSTRUCTIONS_PVT.HAS_PENDING_INSTR_FOR_PROCESS',
3612 FALSE);
3613 END IF;
3614 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3615
3616 WHEN OTHERS THEN
3617 X_TOTAL_INSTRUCTIONS := 0;
3618 X_OPTIONAL_PENDING_INSTR := 0;
3619 X_MANDATORY_PENDING_INSTR := 0;
3620
3621 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3622 FND_MSG_PUB.ADD_EXC_MSG
3623 (G_PKG_NAME,
3624 L_API_NAME);
3625 END IF;
3626
3627 FND_MSG_PUB.COUNT_AND_GET
3628 ( P_COUNT => X_MSG_COUNT,
3629 P_DATA => X_MSG_DATA );
3630
3631 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3632 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3633 'gmo.plsql.GMO_INSTRUCTION_PVT.HAS_PENDING_INSTR_FOR_PROCESS',
3634 FALSE);
3635 END IF;
3636 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3637
3638 END HAS_PENDING_INSTR_FOR_PROCESS;
3639
3640 -- This API is called to send the instance acknowledgment
3641 -- It acknowledges the instruction set and copies the temporary
3642 -- data to permenant instance tables. It also marks all DONE instructions
3643 -- to COMPLETE
3644
3645 PROCEDURE SEND_INSTANCE_ACKN
3646 (
3647 P_INSTRUCTION_PROCESS_ID IN NUMBER,
3648 P_ENTITY_NAME IN VARCHAR2,
3649 P_ENTITY_KEY IN VARCHAR2,
3650 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3651 X_MSG_COUNT OUT NOCOPY NUMBER,
3652 X_MSG_DATA OUT NOCOPY VARCHAR2
3653 )
3654 IS PRAGMA AUTONOMOUS_TRANSACTION;
3655 L_CREATION_DATE DATE;
3656 L_CREATED_BY NUMBER;
3657 L_LAST_UPDATE_DATE DATE;
3658 L_LAST_UPDATED_BY NUMBER;
3659 L_LAST_UPDATE_LOGIN NUMBER;
3660
3661 L_INSTRUCTION_SET_ID NUMBER;
3662 L_INSTRUCTION_ID NUMBER;
3663
3664 L_INSTR_STATUS VARCHAR2(40);
3665
3666 L_INSTR_EREC_COUNT NUMBER;
3667 L_TASK_EREC_COUNT NUMBER;
3668
3669 L_ENTITY_KEY VARCHAR2(500);
3670
3671 CURSOR L_TEMP_INSTR_SET_CSR IS
3672 SELECT INSTRUCTION_SET_ID, INSTRUCTION_PROCESS_ID, ACKN_STATUS
3673 FROM GMO_INSTR_SET_INSTANCE_T
3674 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
3675
3676 CURSOR L_TEMP_INSTR_CSR IS
3677 SELECT TEMP.INSTRUCTION_PROCESS_ID, TEMP.INSTRUCTION_ID, PERM.INSTRUCTION_SET_ID,
3678 TEMP.COMMENTS, TEMP.OPERATOR_ACKN, TEMP.INSTR_STATUS,
3679 TEMP.TASK_ACKN_STATUS, TEMP.TASK_ACKN_DATE, TEMP.DISABLE_TASK
3680 FROM GMO_INSTR_INSTANCE_T TEMP, GMO_INSTR_INSTANCE_VL PERM
3681 WHERE PERM.INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
3682 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
3683 AND PERM.INSTRUCTION_ID = TEMP.INSTRUCTION_ID FOR UPDATE;
3684
3685 CURSOR L_TEMP_INSTR_EREC_CSR IS
3686 SELECT INSTRUCTION_PROCESS_ID, INSTR_EREC_SEQ, INSTRUCTION_ID,
3687 TASK_EREC_ID, INSTR_EREC_ID
3688 FROM GMO_INSTR_EREC_INSTANCE_T
3689 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
3690 AND INSTRUCTION_ID = L_INSTRUCTION_ID
3691 ORDER BY INSTR_EREC_SEQ;
3692
3693 CURSOR L_TEMP_TASK_EREC_CSR IS
3694 SELECT INSTRUCTION_PROCESS_ID, INSTR_TASK_SEQ, TASK_EREC_ID,
3695 TASK_IDENTIFIER, TASK_VALUE, MANUAL_ENTRY, INSTRUCTION_ID
3696 FROM GMO_INSTR_TASK_INSTANCE_T
3697 WHERE
3698 INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
3699 AND INSTRUCTION_ID = L_INSTRUCTION_ID
3700 ORDER BY INSTR_TASK_SEQ;
3701
3702 L_INSTR_SET_REC L_TEMP_INSTR_SET_CSR%ROWTYPE;
3703 L_INSTR_REC L_TEMP_INSTR_CSR%ROWTYPE;
3704 L_INSTR_EREC_REC L_TEMP_INSTR_EREC_CSR%ROWTYPE;
3705 L_TASK_EREC_REC L_TEMP_TASK_EREC_CSR%ROWTYPE;
3706
3707 L_INSTR_ACKN_TYPE VARCHAR2(40);
3708 L_OPERATOR_ACKN VARCHAR2(1);
3709 L_INSTRUCTION_COUNT NUMBER;
3710
3711 L_INSTANCE_STATUS VARCHAR2(40);
3712
3713 L_API_NAME VARCHAR2(40);
3714 L_MESG_TEXT VARCHAR2(1000);
3715
3716 l_set_active NUMBER;
3717
3718 BEGIN
3719
3720 L_API_NAME := 'SEND_INSTANCE_ACKN';
3721
3722 GMO_UTILITIES.GET_WHO_COLUMNS
3723 (
3724 X_CREATION_DATE => L_CREATION_DATE,
3725 X_CREATED_BY => L_CREATED_BY,
3726 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
3727 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
3728 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
3729 );
3730
3731 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
3732 FROM GMO_INSTR_SET_INSTANCE_T
3733 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
3734
3735 SELECT ENTITY_KEY INTO L_ENTITY_KEY
3736 FROM GMO_INSTR_SET_INSTANCE_VL
3737 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3738
3739 IF (P_ENTITY_KEY IS NOT NULL) THEN
3740
3741 IF(L_ENTITY_KEY LIKE (GMO_CONSTANTS_GRP.G_INSTR_PREFIX || '%' )) THEN
3742 --Bug 5224619: start
3743 UPDATE GMO_INSTR_SET_INSTANCE_B
3744 SET ENTITY_KEY = P_ENTITY_KEY
3745 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3746 --Bug 5224619: end
3747
3748 UPDATE_ENTITY_KEY
3749 (
3750 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
3751 P_ENTITY_KEY => P_ENTITY_KEY
3752 );
3753
3754 END IF;
3755
3756 END IF;
3757
3758 L_INSTANCE_STATUS := GET_PROCESS_VARIABLE
3759 ( P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
3760 P_ATTRIBUTE_NAME => GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
3761 P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
3762 );
3763
3764 IF(L_INSTANCE_STATUS IS NOT NULL
3765 AND L_INSTANCE_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_COMPLETE ) THEN
3766
3767 OPEN L_TEMP_INSTR_SET_CSR;
3768 LOOP
3769 FETCH L_TEMP_INSTR_SET_CSR INTO L_INSTR_SET_REC;
3770 EXIT WHEN L_TEMP_INSTR_SET_CSR%NOTFOUND;
3771
3772 --Bug 4730261:start
3773 --update the instructions only when the set is active
3774 select count(*) into l_set_active from gmo_instr_set_instance_vl
3775 where instruction_set_id = L_INSTR_SET_REC.INSTRUCTION_SET_ID
3776 and instr_set_status = GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE;
3777
3778 if (l_set_active > 0) then
3779 --Bug 4730261: end
3780 --Bug 5224619: start
3781 UPDATE GMO_INSTR_SET_INSTANCE_B
3782 SET
3783 ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
3784 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
3785 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
3786 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
3787 WHERE
3788 INSTRUCTION_SET_ID = L_INSTR_SET_REC.INSTRUCTION_SET_ID;
3789 --Bug 5224619: end
3790
3791 L_INSTRUCTION_SET_ID := L_INSTR_SET_REC.INSTRUCTION_SET_ID;
3792
3793 OPEN L_TEMP_INSTR_CSR;
3794 LOOP
3795 FETCH L_TEMP_INSTR_CSR INTO L_INSTR_REC;
3796 EXIT WHEN L_TEMP_INSTR_CSR%NOTFOUND;
3797
3798 L_INSTR_STATUS := L_INSTR_REC.INSTR_STATUS;
3799
3800 IF(L_INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE ) THEN
3801 L_INSTR_STATUS := GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE;
3802
3803 UPDATE GMO_INSTR_INSTANCE_T
3804 SET INSTR_STATUS = L_INSTR_STATUS
3805 WHERE INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE
3806 AND INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3807
3808 --Bug 5224619: start
3809 UPDATE GMO_INSTR_INSTANCE_TL
3810 SET
3811 COMMENTS = L_INSTR_REC.COMMENTS,
3812 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
3813 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
3814 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
3815 WHERE
3816 INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3817
3818 UPDATE GMO_INSTR_INSTANCE_B
3819 SET
3820 OPERATOR_ACKN = L_INSTR_REC.OPERATOR_ACKN,
3821 TASK_ACKN_STATUS = L_INSTR_REC.TASK_ACKN_STATUS,
3822 TASK_ACKN_DATE = L_INSTR_REC.TASK_ACKN_DATE,
3823 INSTR_STATUS = L_INSTR_STATUS,
3824 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
3825 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
3826 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
3827 WHERE
3828 INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3829 --Bug 5224619: end
3830
3831 L_INSTRUCTION_ID := L_INSTR_REC.INSTRUCTION_ID;
3832
3833 --First delete data from instr_erec_instance table
3834 DELETE FROM GMO_INSTR_EREC_INSTANCE
3835 WHERE INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3836
3837 OPEN L_TEMP_INSTR_EREC_CSR;
3838 LOOP
3839 FETCH L_TEMP_INSTR_EREC_CSR INTO L_INSTR_EREC_REC;
3840 EXIT WHEN L_TEMP_INSTR_EREC_CSR%NOTFOUND;
3841
3842 INSERT INTO GMO_INSTR_EREC_INSTANCE
3843 (
3844 INSTRUCTION_ID,
3845 INSTR_EREC_SEQ,
3846 TASK_EREC_ID,
3847 INSTR_EREC_ID,
3848 CREATION_DATE,
3849 CREATED_BY,
3850 LAST_UPDATE_DATE,
3851 LAST_UPDATED_BY,
3852 LAST_UPDATE_LOGIN
3853 )
3854 VALUES
3855 (
3856 L_INSTR_EREC_REC.INSTRUCTION_ID,
3857 L_INSTR_EREC_REC.INSTR_EREC_SEQ,
3858 L_INSTR_EREC_REC.TASK_EREC_ID,
3859 L_INSTR_EREC_REC.INSTR_EREC_ID,
3860 L_CREATION_DATE,
3861 L_CREATED_BY,
3862 L_LAST_UPDATE_DATE,
3863 L_LAST_UPDATED_BY,
3864 L_LAST_UPDATE_LOGIN
3865 );
3866
3867 END LOOP;
3868 CLOSE L_TEMP_INSTR_EREC_CSR;
3869
3870 --First delete data from instr_task_instance table
3871 DELETE FROM GMO_INSTR_TASK_INSTANCE
3872 WHERE INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3873
3874 OPEN L_TEMP_TASK_EREC_CSR;
3875 LOOP
3876 FETCH L_TEMP_TASK_EREC_CSR INTO L_TASK_EREC_REC;
3877 EXIT WHEN L_TEMP_TASK_EREC_CSR%NOTFOUND;
3878
3879 INSERT INTO GMO_INSTR_TASK_INSTANCE
3880 (
3881 INSTRUCTION_ID,
3882 INSTR_TASK_SEQ,
3883 TASK_EREC_ID,
3884 TASK_IDENTIFIER,
3885 TASK_VALUE,
3886 MANUAL_ENTRY,
3887 CREATION_DATE,
3888 CREATED_BY,
3889 LAST_UPDATE_DATE,
3890 LAST_UPDATED_BY,
3891 LAST_UPDATE_LOGIN
3892 )
3893 VALUES
3894 (
3895 L_TASK_EREC_REC.INSTRUCTION_ID,
3896 L_TASK_EREC_REC.INSTR_TASK_SEQ,
3897 L_TASK_EREC_REC.TASK_EREC_ID,
3898 L_TASK_EREC_REC.TASK_IDENTIFIER,
3899 L_TASK_EREC_REC.TASK_VALUE,
3900 L_TASK_EREC_REC.MANUAL_ENTRY,
3901 L_CREATION_DATE,
3902 L_CREATED_BY,
3903 L_LAST_UPDATE_DATE,
3904 L_LAST_UPDATED_BY,
3905 L_LAST_UPDATE_LOGIN
3906 );
3907
3908 END LOOP;
3909 CLOSE L_TEMP_TASK_EREC_CSR;
3910
3911 END IF; -- INSTR_STATUS = DONE
3912
3913 END LOOP;
3914 CLOSE L_TEMP_INSTR_CSR;
3915
3916 -- Bug 5231778 : update the Instructin Set Status before raising Event
3917 --Set the instruction set status to 'complete' if all the
3918 --instructions are complete in a given instruction set
3919 SELECT COUNT(*) INTO L_INSTRUCTION_COUNT
3920 FROM GMO_INSTR_INSTANCE_B
3921 WHERE INSTR_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE
3922 AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3923
3924 -- Set the Instruction set status to complete
3925 IF(L_INSTRUCTION_COUNT = 0) THEN
3926 --Bug 5224619: start
3927 UPDATE GMO_INSTR_SET_INSTANCE_B
3928 SET INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_COMPLETE
3929 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3930 --Bug 5224619: end
3931 END IF;
3932
3933 --Bug 4730261: start
3934 RAISE_INSTR_SET_EVENT(P_INSTRUCTION_SET_ID => L_INSTRUCTION_SET_ID);
3935 end if; -- end if (l_set_active > 0)
3936 --Bug 4730261: end
3937
3938 END LOOP;
3939 CLOSE L_TEMP_INSTR_SET_CSR;
3940
3941 END IF; -- If Instance Status is COMPLETE
3942
3943 --COMMIT CHANGES
3944 COMMIT;
3945
3946 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3947
3948 EXCEPTION
3949 WHEN OTHERS THEN
3950 ROLLBACK;
3951
3952 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3953 FND_MSG_PUB.ADD_EXC_MSG
3954 (G_PKG_NAME,
3955 L_API_NAME);
3956 END IF;
3957
3958 FND_MSG_PUB.COUNT_AND_GET
3959 ( P_COUNT => X_MSG_COUNT,
3960 P_DATA => X_MSG_DATA);
3961
3962 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3963 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3964 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_CONTEXT',
3965 FALSE);
3966 END IF;
3967
3968 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3969
3970 END SEND_INSTANCE_ACKN;
3971
3972 -- This API is called to send the task acknowledgment
3973 -- It acknowledges the task identifier, value and e-record
3974 -- id into the GMO_INSTR_TASK_INSTANCE_T table
3975
3976 PROCEDURE SEND_TASK_ACKN
3977 (
3978 P_INSTRUCTION_ID IN NUMBER,
3979 P_INSTRUCTION_PROCESS_ID IN NUMBER,
3980 P_ENTITY_KEY IN VARCHAR2 DEFAULT NULL,
3981 P_TASK_ERECORD_ID IN FND_TABLE_OF_VARCHAR2_255,
3982 P_TASK_IDENTIFIER IN FND_TABLE_OF_VARCHAR2_255,
3983 P_TASK_VALUE IN FND_TABLE_OF_VARCHAR2_255,
3984 P_DISABLE_TASK IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO,
3985 P_MANUAL_ENTRY IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO,
3986 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3987 X_MSG_COUNT OUT NOCOPY NUMBER,
3988 X_MSG_DATA OUT NOCOPY VARCHAR2
3989 )
3990 IS PRAGMA AUTONOMOUS_TRANSACTION;
3991
3992 L_CREATION_DATE DATE;
3993 L_CREATED_BY NUMBER;
3994 L_LAST_UPDATE_DATE DATE;
3995 L_LAST_UPDATED_BY NUMBER;
3996 L_LAST_UPDATE_LOGIN NUMBER;
3997
3998 L_INSTRUCTION_ID NUMBER;
3999 L_INSTRUCTION_SET_ID NUMBER;
4000
4001 L_ENTITY_NAME VARCHAR2(200);
4002 L_ENTITY_KEY VARCHAR2(500);
4003
4004 L_INVALID_PARAM_ERR EXCEPTION;
4005 L_ENTITY_KEY_ERR EXCEPTION;
4006
4007 L_API_NAME VARCHAR2(40);
4008 L_MESG_TEXT VARCHAR2(1000);
4009
4010 L_PROCESS_COUNT NUMBER;
4011
4012 BEGIN
4013
4014 L_API_NAME := 'SEND_TASK_ACKN';
4015
4016 GMO_UTILITIES.GET_WHO_COLUMNS
4017 (
4018 X_CREATION_DATE => L_CREATION_DATE,
4019 X_CREATED_BY => L_CREATED_BY,
4020 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4021 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4022 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4023 );
4024
4025 -- VALIDATE THE INSTRUCTION ID FIRST BY SEEING IF IT EXISTS IN
4026 -- THE TEMPORARY TABLE
4027
4028 SELECT COUNT(*) INTO L_PROCESS_COUNT
4029 FROM GMO_INSTR_ATTRIBUTES_T
4030 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4031 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_INSTANCE_STATUS
4032 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
4033 AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_ERROR;
4034
4035 IF L_PROCESS_COUNT = 0 THEN
4036 RAISE L_INVALID_PARAM_ERR;
4037 END IF;
4038
4039 SELECT INSTRN.INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
4040 FROM
4041 GMO_INSTR_INSTANCE_T INSTR_TEMP,
4042 GMO_INSTR_INSTANCE_VL INSTRN
4043 WHERE
4044 INSTR_TEMP.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4045 AND INSTR_TEMP.INSTRUCTION_ID = P_INSTRUCTION_ID
4046 AND INSTR_TEMP.INSTRUCTION_ID = INSTRN.INSTRUCTION_ID;
4047
4048 IF ( P_MANUAL_ENTRY = GMO_CONSTANTS_GRP.YES ) THEN
4049 -- Remove all previously entered manual data, and enter the new
4050 -- rows passed in current API call, this will take care of
4051 -- delete task row in case of manual entry
4052
4053 DELETE FROM GMO_INSTR_TASK_INSTANCE_T
4054 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4055 AND INSTRUCTION_ID = P_INSTRUCTION_ID
4056 AND MANUAL_ENTRY = GMO_CONSTANTS_GRP.YES;
4057
4058 END IF;
4059
4060 FOR I IN 1..P_TASK_ERECORD_ID.COUNT LOOP
4061 --the task erecord can be null for some cases
4062 --so we insert the record when either id or erecord is available
4063 IF ( (P_TASK_ERECORD_ID(I) IS NOT NULL)
4064 OR (P_TASK_IDENTIFIER(I) IS NOT NULL)) THEN
4065
4066 INSERT INTO GMO_INSTR_TASK_INSTANCE_T
4067 (
4068 INSTRUCTION_PROCESS_ID,
4069 INSTRUCTION_ID,
4070 INSTR_TASK_SEQ,
4071 TASK_EREC_ID,
4072 TASK_IDENTIFIER,
4073 TASK_VALUE,
4074 MANUAL_ENTRY,
4075 CREATION_DATE,
4076 CREATED_BY,
4077 LAST_UPDATE_DATE,
4078 LAST_UPDATED_BY,
4079 LAST_UPDATE_LOGIN
4080 )
4081 VALUES
4082 (
4083 P_INSTRUCTION_PROCESS_ID,
4084 P_INSTRUCTION_ID,
4085 GMO_INSTR_TASK_INSTANCE_S.NEXTVAL,
4086 TO_NUMBER(P_TASK_ERECORD_ID(I),999999999999.999999),
4087 P_TASK_IDENTIFIER(I),
4088 P_TASK_VALUE(I),
4089 P_MANUAL_ENTRY,
4090 L_CREATION_DATE,
4091 L_CREATED_BY,
4092 L_LAST_UPDATE_DATE,
4093 L_LAST_UPDATED_BY,
4094 L_LAST_UPDATE_LOGIN
4095 );
4096
4097 END IF;
4098
4099 END LOOP;
4100
4101 -- UPDATE THE PARAMETER IN GMO_INSTR_INSTANCE_T 'DISABLE_TASK' = 'Y' OR 'N'
4102 UPDATE GMO_INSTR_INSTANCE_T
4103 SET
4104 DISABLE_TASK = P_DISABLE_TASK
4105 WHERE
4106 INSTRUCTION_ID = P_INSTRUCTION_ID
4107 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4108
4109 -- UPDATE THE ENTITY KEY IN PERM TABLES FOR THAT INSTRUCTION_ID AND
4110 -- INSTRUCTION SET ID
4111
4112 SELECT ENTITY_NAME, ENTITY_KEY
4113 INTO L_ENTITY_NAME, L_ENTITY_KEY
4114 FROM GMO_INSTR_SET_INSTANCE_VL
4115 WHERE
4116 INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4117
4118 -- IF THE ENTITY KEY IS internally set by PI while creation,
4119 -- UPDATE IT
4120 IF (L_ENTITY_KEY LIKE ( GMO_CONSTANTS_GRP.G_INSTR_PREFIX || '%')
4121 AND P_ENTITY_KEY IS NOT NULL) THEN
4122 --Bug 5224619: start
4123 UPDATE GMO_INSTR_SET_INSTANCE_B
4124 SET ENTITY_KEY = P_ENTITY_KEY
4125 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4126 --Bug 5224619: end
4127
4128 UPDATE_ENTITY_KEY
4129 (
4130 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
4131 P_ENTITY_KEY => P_ENTITY_KEY
4132 );
4133 END IF;
4134
4135 -- Acknowledge the task by setting the task ackn status
4136 -- only if API is called by task application
4137 IF(P_MANUAL_ENTRY <> GMO_CONSTANTS_GRP.YES ) THEN
4138 UPDATE GMO_INSTR_INSTANCE_T
4139 SET TASK_ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_TASK_ACKN_STATUS,
4140 TASK_ACKN_DATE = L_CREATION_DATE
4141 WHERE INSTRUCTION_ID = P_INSTRUCTION_ID
4142 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4143 END IF;
4144
4145 -- COMMIT TASK DATA
4146 COMMIT;
4147
4148 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4149
4150 EXCEPTION
4151 WHEN NO_DATA_FOUND THEN
4152 ROLLBACK;
4153 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4154
4155 WHEN L_INVALID_PARAM_ERR THEN
4156 ROLLBACK;
4157 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4158
4159 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_API_PARAM_ERR');
4160 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4161 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4162
4163 L_MESG_TEXT := FND_MESSAGE.GET();
4164
4165 FND_MSG_PUB.ADD_EXC_MSG
4166 ( G_PKG_NAME,
4167 L_API_NAME,
4168 L_MESG_TEXT
4169 );
4170
4171 FND_MSG_PUB.COUNT_AND_GET
4172 (
4173 P_COUNT => X_MSG_COUNT,
4174 P_DATA => X_MSG_DATA
4175 );
4176
4177 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4178 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4179 'gmo.plsql.GMO_INSTRUCTIONS_PVT.SEND_TASK_ACKN',
4180 FALSE);
4181 END IF;
4182
4183 WHEN L_ENTITY_KEY_ERR THEN
4184 ROLLBACK;
4185 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4186
4187 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_API_PARAM_ERR');
4188 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4189 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4190
4191 L_MESG_TEXT := FND_MESSAGE.GET();
4192
4193 FND_MSG_PUB.ADD_EXC_MSG
4194 ( G_PKG_NAME,
4195 L_API_NAME,
4196 L_MESG_TEXT
4197 );
4198
4199 FND_MSG_PUB.COUNT_AND_GET
4200 (
4201 P_COUNT => X_MSG_COUNT,
4202 P_DATA => X_MSG_DATA
4203 );
4204
4205 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4206 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4207 'gmo.plsql.GMO_INSTRUCTIONS_PVT.SEND_TASK_ACKN',
4208 FALSE);
4209 END IF;
4210
4211 WHEN OTHERS THEN
4212 ROLLBACK;
4213 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4214
4215 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4216 FND_MSG_PUB.ADD_EXC_MSG
4217 (G_PKG_NAME,
4218 L_API_NAME);
4219 END IF;
4220
4221 FND_MSG_PUB.COUNT_AND_GET
4222 ( P_COUNT => X_MSG_COUNT,
4223 P_DATA => X_MSG_DATA);
4224
4225 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4226 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4227 'gmo.plsql.GMO_INSTRUCTION_PVT.SEND_TASK_ACKN',
4228 FALSE);
4229 END IF;
4230 END SEND_TASK_ACKN;
4231
4232 -- This API marks the instruction set as CANCELLED
4233 -- It is used to de-activate an instruction set/
4234 PROCEDURE NULLIFY_INSTR_FOR_ENTITY
4235 (
4236 P_ENTITY_NAME IN VARCHAR2,
4237 P_ENTITY_KEY IN VARCHAR2,
4238 P_INSTRUCTION_TYPE IN VARCHAR2,
4239 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4240 X_MSG_COUNT OUT NOCOPY NUMBER,
4241 X_MSG_DATA OUT NOCOPY VARCHAR2
4242 )
4243 IS
4244 L_INSTRUCTION_SET_ID NUMBER;
4245
4246 L_API_NAME VARCHAR2(40);
4247
4248 BEGIN
4249
4250 L_API_NAME := 'NULLIFY_INSTR_FOR_ENTITY';
4251
4252 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
4253 FROM GMO_INSTR_SET_INSTANCE_VL
4254 WHERE ENTITY_NAME = P_ENTITY_NAME
4255 AND ENTITY_KEY = P_ENTITY_KEY
4256 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
4257 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
4258
4259 --Set the instruction set status to CANCEL
4260 --Bug 5224619: start
4261 UPDATE GMO_INSTR_SET_INSTANCE_B
4262 SET
4263 INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL
4264 WHERE
4265 INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4266 --Bug 5224619: end
4267 --Bug 4730261: start
4268 RAISE_INSTR_SET_EVENT(P_INSTRUCTION_SET_ID => L_INSTRUCTION_SET_ID);
4269 --Bug 4730261: end
4270
4271 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4272
4273 EXCEPTION
4274 WHEN NO_DATA_FOUND THEN
4275 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4276
4277 WHEN OTHERS THEN
4278 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4279
4280 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4281 FND_MSG_PUB.ADD_EXC_MSG
4282 (G_PKG_NAME,
4283 L_API_NAME);
4284 END IF;
4285
4286 FND_MSG_PUB.COUNT_AND_GET
4287 ( P_COUNT => X_MSG_COUNT,
4288 P_DATA => X_MSG_DATA );
4289
4290 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4291 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4292 'gmo.plsql.GMO_INSTRUCTION_PVT.NULLIFY_INSTR_FOR_ENTITY',
4293 FALSE);
4294 END IF;
4295
4296 END NULLIFY_INSTR_FOR_ENTITY;
4297
4298 -- This API must complete optional instructions in temp table for
4299 -- particular session
4300 PROCEDURE COMPLETE_OPTIONAL_INSTR
4301 (
4302 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4303 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4304 X_MSG_COUNT OUT NOCOPY NUMBER,
4305 X_MSG_DATA OUT NOCOPY VARCHAR2
4306 )
4307 IS PRAGMA AUTONOMOUS_TRANSACTION;
4308 L_INSTRUCTION_SET_ID NUMBER;
4309
4310 L_API_NAME VARCHAR2(40);
4311 L_CREATION_DATE DATE;
4312 L_CREATED_BY NUMBER;
4313 L_LAST_UPDATE_DATE DATE;
4314 L_LAST_UPDATED_BY NUMBER;
4315 L_LAST_UPDATE_LOGIN NUMBER;
4316
4317
4318 BEGIN
4319
4320 L_API_NAME := 'COMPLETE_OPTIONAL_INSTR';
4321 GMO_UTILITIES.GET_WHO_COLUMNS
4322 (
4323 X_CREATION_DATE => L_CREATION_DATE,
4324 X_CREATED_BY => L_CREATED_BY,
4325 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4326 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4327 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4328 );
4329
4330 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
4331 FROM GMO_INSTR_SET_INSTANCE_T
4332 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4333
4334 -- This must complete optional instructions in temp table for
4335 -- particular session
4336 UPDATE GMO_INSTR_INSTANCE_T
4337 SET
4338 INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE,
4339 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
4340 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
4341 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
4342 WHERE
4343 INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
4344 AND
4345 INSTRUCTION_ID IN (
4346 SELECT INSTRUCTION_ID FROM GMO_INSTR_INSTANCE_B
4347 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
4348 AND INSTR_ACKN_TYPE = GMO_CONSTANTS_GRP.G_INSTR_OPTIONAL
4349 )
4350 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4351
4352 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4353
4354 --Commit the changes
4355 COMMIT;
4356
4357 EXCEPTION
4358 WHEN OTHERS THEN
4359 ROLLBACK;
4360
4361 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4362
4363 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4364 FND_MSG_PUB.ADD_EXC_MSG
4365 (G_PKG_NAME,
4366 L_API_NAME);
4367 END IF;
4368
4369 FND_MSG_PUB.COUNT_AND_GET
4370 ( P_COUNT => X_MSG_COUNT,
4371 P_DATA => X_MSG_DATA);
4372
4373 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4374 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4375 'gmo.plsql.GMO_INSTRUCTION_PVT.COMPLETE_OPTIONAL_INSTR',
4376 FALSE);
4377 END IF;
4378
4379 END COMPLETE_OPTIONAL_INSTR;
4380
4381 -- This API returns the Definition process status. It can be
4382 -- MODIFIED or NO_CHANGE.
4383
4384 PROCEDURE GET_DEFN_STATUS
4385 (
4386 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4387 X_DEFINITION_STATUS OUT NOCOPY VARCHAR2,
4388 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4389 X_MSG_COUNT OUT NOCOPY NUMBER,
4390 X_MSG_DATA OUT NOCOPY VARCHAR2
4391 )
4392 IS
4393 L_API_NAME VARCHAR2(40);
4394 L_MESG_TEXT VARCHAR2(4000);
4395 BEGIN
4396
4397 L_API_NAME := 'GET_DEFN_STATUS';
4398
4399 SELECT ATTRIBUTE_VALUE INTO X_DEFINITION_STATUS
4400 FROM GMO_INSTR_ATTRIBUTES_T
4401 WHERE ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS
4402 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4403 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
4404
4405 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4406
4407 EXCEPTION
4408 WHEN NO_DATA_FOUND THEN
4409
4410 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_INV_PROCESSID_ERR');
4411 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4412 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4413 FND_MESSAGE.SET_TOKEN('PROCESS_ID', P_INSTRUCTION_PROCESS_ID );
4414
4415 L_MESG_TEXT := FND_MESSAGE.GET();
4416
4417 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
4418 L_API_NAME,
4419 L_MESG_TEXT);
4420
4421 FND_MSG_PUB.COUNT_AND_GET(P_COUNT => X_MSG_COUNT,
4422 P_DATA => X_MSG_DATA);
4423
4424 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4425 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4426 'gmo.plsql.GMO_INSTRUCTIONS_PVT.GET_DEFN_STATUS',
4427 FALSE);
4428 END IF;
4429
4430 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4431
4432 WHEN OTHERS THEN
4433 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4434
4435 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4436 FND_MSG_PUB.ADD_EXC_MSG
4437 (G_PKG_NAME,
4438 L_API_NAME);
4439 END IF;
4440
4441 FND_MSG_PUB.COUNT_AND_GET
4442 ( P_COUNT => X_MSG_COUNT,
4443 P_DATA => X_MSG_DATA);
4444
4445 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4446 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4447 'gmo.plsql.GMO_INSTRUCTION_PVT.GET_DEFN_STATUS',
4448 FALSE);
4449 END IF;
4450
4451 END GET_DEFN_STATUS;
4452
4453
4454 -- This API returns the INSTANCE_STATUS, It can be PENDING or COMPLETE
4455 -- or TERMINATE
4456
4457 PROCEDURE GET_INSTANCE_STATUS
4458 (
4459 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4460 X_INSTANCE_STATUS OUT NOCOPY VARCHAR2,
4461 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4462 X_MSG_COUNT OUT NOCOPY NUMBER,
4463 X_MSG_DATA OUT NOCOPY VARCHAR2
4464 )
4465 IS
4466 L_API_NAME VARCHAR2(40);
4467 L_MESG_TEXT VARCHAR2(4000);
4468 BEGIN
4469
4470 L_API_NAME := 'GET_INSTANCE_STATUS';
4471
4472 SELECT ATTRIBUTE_VALUE INTO X_INSTANCE_STATUS
4473 FROM GMO_INSTR_ATTRIBUTES_T
4474 WHERE ATTRIBUTE_NAME = 'INSTANCE_STATUS'
4475 AND ATTRIBUTE_TYPE = 'INTERNAL'
4476 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4477
4478 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4479
4480 EXCEPTION
4481 WHEN NO_DATA_FOUND THEN
4482
4483 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_INV_PROCESSID_ERR');
4484 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4485 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4486 FND_MESSAGE.SET_TOKEN('PROCESS_ID', P_INSTRUCTION_PROCESS_ID );
4487
4488 L_MESG_TEXT := FND_MESSAGE.GET();
4489
4490 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
4491 L_API_NAME,
4492 L_MESG_TEXT);
4493
4494 FND_MSG_PUB.COUNT_AND_GET(P_COUNT => X_MSG_COUNT,
4495 P_DATA => X_MSG_DATA);
4496
4497 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4498 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4499 'gmo.plsql.GMO_INSTRUCTIONS_PVT.GET_INSTANCE_STATUS',
4500 FALSE);
4501 END IF;
4502
4503 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4504
4505 WHEN OTHERS THEN
4506 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4507
4508 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4509 FND_MSG_PUB.ADD_EXC_MSG
4510 (G_PKG_NAME,
4511 L_API_NAME);
4512 END IF;
4513
4514 FND_MSG_PUB.COUNT_AND_GET
4515 ( P_COUNT => X_MSG_COUNT,
4516 P_DATA => X_MSG_DATA);
4517
4518 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4519 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4520 'gmo.plsql.GMO_INSTRUCTION_PVT.GET_INSTANCE_STATUS',
4521 FALSE);
4522 END IF;
4523
4524 END GET_INSTANCE_STATUS;
4525
4526 -- This Private API is used to capture the operator response
4527 -- and store it in Instance Temp Table
4528
4529 PROCEDURE CAPTURE_OPERATOR_RESPONSE
4530 (
4531 P_INSTRUCTION_ID IN NUMBER,
4532 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4533 P_OPERATOR_ACKN IN VARCHAR2,
4534 P_INSTR_COMMENTS IN VARCHAR2,
4535 P_INSTR_STATUS IN VARCHAR2,
4536 X_RETURN_STATUS OUT NOCOPY VARCHAR2
4537 )
4538 IS PRAGMA AUTONOMOUS_TRANSACTION;
4539 L_INVALID_PROCESS_ID EXCEPTION;
4540 L_CREATION_DATE DATE;
4541 L_CREATED_BY NUMBER;
4542 L_LAST_UPDATE_DATE DATE;
4543 L_LAST_UPDATED_BY NUMBER;
4544 L_LAST_UPDATE_LOGIN NUMBER;
4545
4546 L_API_NAME VARCHAR2(40);
4547 L_MESG_TEXT VARCHAR2(1000);
4548
4549 BEGIN
4550
4551 L_API_NAME := 'CAPTURE_OPERATOR_RESPONSE';
4552
4553 GMO_UTILITIES.GET_WHO_COLUMNS
4554 (
4555 X_CREATION_DATE => L_CREATION_DATE,
4556 X_CREATED_BY => L_CREATED_BY,
4557 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4558 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4559 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4560 );
4561
4562 UPDATE GMO_INSTR_INSTANCE_T
4563 SET
4564 OPERATOR_ACKN = P_OPERATOR_ACKN,
4565 COMMENTS = P_INSTR_COMMENTS,
4566 INSTR_STATUS = P_INSTR_STATUS,
4567 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
4568 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
4569 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
4570 WHERE
4571 INSTRUCTION_ID = P_INSTRUCTION_ID
4572 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4573
4574 IF(SQL%NOTFOUND ) THEN
4575 RAISE L_INVALID_PROCESS_ID;
4576 END IF;
4577
4578 -- SAVE CHANGES
4579 COMMIT;
4580
4581 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4582
4583 EXCEPTION
4584 WHEN L_INVALID_PROCESS_ID THEN
4585 ROLLBACK;
4586
4587 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_INV_PROCESSID_ERR');
4588 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4589 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4590 FND_MESSAGE.SET_TOKEN('PROCESS_ID', P_INSTRUCTION_PROCESS_ID );
4591
4592 L_MESG_TEXT := FND_MESSAGE.GET();
4593
4594 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4595 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4596 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CAPTURE_OPERATOR_RESPONSE',
4597 FALSE);
4598 END IF;
4599
4600 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4601
4602 WHEN OTHERS THEN
4603 ROLLBACK;
4604
4605 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4606 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4607 'gmo.plsql.GMO_INSTRUCTION_PVT.CAPTURE_OPERATOR_RESPONSE',
4608 FALSE);
4609 END IF;
4610
4611 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4612
4613 END CAPTURE_OPERATOR_RESPONSE;
4614
4615
4616 -- This API inserts instruction e-record details in e-records table
4617 PROCEDURE INSERT_ERECORD_DETAILS
4618 (
4619 P_INSTRUCTION_ID IN NUMBER,
4620 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4621 P_INSTRUCTION_ERECORD_ID IN NUMBER,
4622 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4623 X_MSG_COUNT OUT NOCOPY VARCHAR2,
4624 X_MSG_DATA OUT NOCOPY VARCHAR2
4625 )
4626 IS PRAGMA AUTONOMOUS_TRANSACTION;
4627
4628 L_CREATION_DATE DATE;
4629 L_CREATED_BY NUMBER;
4630 L_LAST_UPDATE_DATE DATE;
4631 L_LAST_UPDATED_BY NUMBER;
4632 L_LAST_UPDATE_LOGIN NUMBER;
4633
4634 L_PARAM_ERR EXCEPTION;
4635
4636 L_API_NAME VARCHAR2(40);
4637
4638 BEGIN
4639
4640 L_API_NAME := 'INSERT_ERECORD_DETAILS';
4641
4642 GMO_UTILITIES.GET_WHO_COLUMNS
4643 (
4644 X_CREATION_DATE => L_CREATION_DATE,
4645 X_CREATED_BY => L_CREATED_BY,
4646 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4647 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4648 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4649 );
4650
4651 IF (P_INSTRUCTION_ID IS NULL OR P_INSTRUCTION_PROCESS_ID IS NULL
4652 OR P_INSTRUCTION_ERECORD_ID IS NULL ) THEN
4653 RAISE L_PARAM_ERR;
4654 END IF;
4655
4656 INSERT INTO GMO_INSTR_EREC_INSTANCE_T
4657 (
4658 INSTRUCTION_ID,
4659 INSTRUCTION_PROCESS_ID,
4660 INSTR_EREC_SEQ,
4661 INSTR_EREC_ID,
4662 TASK_EREC_ID,
4663 CREATION_DATE,
4664 CREATED_BY,
4665 LAST_UPDATE_DATE,
4666 LAST_UPDATED_BY,
4667 LAST_UPDATE_LOGIN
4668 )
4669 VALUES
4670 (
4671 P_INSTRUCTION_ID,
4672 P_INSTRUCTION_PROCESS_ID,
4673 GMO_INSTR_EREC_INSTANCE_S.NEXTVAL,
4674 P_INSTRUCTION_ERECORD_ID,
4675 NULL,
4676 L_CREATION_DATE,
4677 L_CREATED_BY,
4678 L_LAST_UPDATE_DATE,
4679 L_LAST_UPDATED_BY,
4680 L_LAST_UPDATE_LOGIN
4681 );
4682
4683 -- SAVE CHANGES
4684 COMMIT;
4685
4686 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4687
4688 EXCEPTION
4689 WHEN OTHERS THEN
4690 ROLLBACK;
4691 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4692
4693
4694 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4695 FND_MSG_PUB.ADD_EXC_MSG
4696 (G_PKG_NAME,
4697 L_API_NAME);
4698 END IF;
4699
4700 FND_MSG_PUB.COUNT_AND_GET
4701 ( P_COUNT => X_MSG_COUNT,
4702 P_DATA => X_MSG_DATA);
4703
4704 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4705 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4706 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_CONTEXT',
4707 FALSE);
4708 END IF;
4709
4710 END INSERT_ERECORD_DETAILS;
4711
4712 -- This API creates temporary instance of instructions from
4713 -- permanent instance tables It is called from CREATE_INSTANCE_CONTEXT
4714 PROCEDURE CREATE_TEMPORARY_INSTANCES
4715 (
4716 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4717 P_ENTITY_NAME IN VARCHAR2,
4718 P_ENTITY_KEY IN VARCHAR2,
4719 P_INSTRUCTION_TYPE IN VARCHAR2,
4720 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4721 X_MSG_COUNT OUT NOCOPY NUMBER,
4722 X_MSG_DATA OUT NOCOPY VARCHAR2
4723 ) IS
4724 L_CREATION_DATE DATE;
4725 L_CREATED_BY NUMBER;
4726 L_LAST_UPDATE_DATE DATE;
4727 L_LAST_UPDATED_BY NUMBER;
4728 L_LAST_UPDATE_LOGIN NUMBER;
4729
4730 L_INSTRUCTION_SET_ID NUMBER;
4731 L_INSTRUCTION_ID NUMBER;
4732
4733 CURSOR L_INSTR_SET_CSR IS
4734 SELECT INSTRUCTION_SET_ID, ACKN_STATUS
4735 FROM GMO_INSTR_SET_INSTANCE_B
4736 WHERE ENTITY_NAME = P_ENTITY_NAME
4737 AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
4738 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
4739 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
4740
4741 CURSOR L_INSTR_CSR IS
4742 SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID,
4743 COMMENTS, OPERATOR_ACKN, INSTR_STATUS,
4744 TASK_ACKN_STATUS, TASK_ID
4745 FROM GMO_INSTR_INSTANCE_VL
4746 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4747
4748 CURSOR L_INSTR_TASK_CSR IS
4749 SELECT INSTRUCTION_ID, INSTR_TASK_SEQ, TASK_EREC_ID,
4750 TASK_IDENTIFIER, TASK_VALUE, MANUAL_ENTRY
4751 FROM GMO_INSTR_TASK_INSTANCE
4752 WHERE INSTRUCTION_ID = L_INSTRUCTION_ID;
4753
4754 CURSOR L_INSTR_EREC_CSR IS
4755 SELECT INSTRUCTION_ID, INSTR_EREC_SEQ, INSTR_EREC_ID,
4756 TASK_EREC_ID
4757 FROM GMO_INSTR_EREC_INSTANCE
4758 WHERE INSTRUCTION_ID = L_INSTRUCTION_ID;
4759
4760 L_INSTR_SET_REC L_INSTR_SET_CSR%ROWTYPE;
4761 L_INSTR_REC L_INSTR_CSR%ROWTYPE;
4762
4763 L_INSTR_TASK_REC L_INSTR_TASK_CSR%ROWTYPE;
4764 L_INSTR_EREC_REC L_INSTR_EREC_CSR%ROWTYPE;
4765
4766 L_API_NAME VARCHAR2(40);
4767
4768 BEGIN
4769
4770 L_API_NAME := 'CREATE_TEMPORARY_INSTANCES';
4771
4772 GMO_UTILITIES.GET_WHO_COLUMNS
4773 (
4774 X_CREATION_DATE => L_CREATION_DATE,
4775 X_CREATED_BY => L_CREATED_BY,
4776 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4777 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4778 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4779 );
4780
4781 OPEN L_INSTR_SET_CSR;
4782 LOOP
4783 FETCH L_INSTR_SET_CSR INTO L_INSTR_SET_REC;
4784 EXIT WHEN L_INSTR_SET_CSR%NOTFOUND;
4785
4786 INSERT INTO GMO_INSTR_SET_INSTANCE_T
4787 (
4788 INSTRUCTION_PROCESS_ID,
4789 INSTRUCTION_SET_ID,
4790 ACKN_STATUS,
4791 CREATION_DATE,
4792 CREATED_BY,
4793 LAST_UPDATE_DATE,
4794 LAST_UPDATED_BY,
4795 LAST_UPDATE_LOGIN
4796 )
4797 VALUES
4798 (
4799 P_INSTRUCTION_PROCESS_ID,
4800 L_INSTR_SET_REC.INSTRUCTION_SET_ID,
4801 L_INSTR_SET_REC.ACKN_STATUS,
4802 L_CREATION_DATE, -- FIGURE THIS OUT
4803 L_CREATED_BY,
4804 L_LAST_UPDATE_DATE,
4805 L_LAST_UPDATED_BY,
4806 L_LAST_UPDATE_LOGIN
4807 );
4808
4809 L_INSTRUCTION_SET_ID := L_INSTR_SET_REC.INSTRUCTION_SET_ID;
4810
4811 OPEN L_INSTR_CSR;
4812 LOOP
4813 FETCH L_INSTR_CSR INTO L_INSTR_REC;
4814 EXIT WHEN L_INSTR_CSR%NOTFOUND;
4815
4816 L_INSTRUCTION_ID := L_INSTR_REC.INSTRUCTION_ID;
4817
4818 INSERT INTO GMO_INSTR_INSTANCE_T
4819 (
4820 INSTRUCTION_PROCESS_ID,
4821 INSTRUCTION_ID,
4822 COMMENTS,
4823 OPERATOR_ACKN,
4824 INSTR_STATUS,
4825 TASK_ACKN_STATUS,
4826 TASK_ID,
4827 DISABLE_TASK,
4828 CREATION_DATE,
4829 CREATED_BY,
4830 LAST_UPDATE_DATE,
4831 LAST_UPDATED_BY,
4832 LAST_UPDATE_LOGIN
4833 )
4834 VALUES
4835 (
4836 P_INSTRUCTION_PROCESS_ID,
4837 L_INSTR_REC.INSTRUCTION_ID,
4838 L_INSTR_REC.COMMENTS,
4839 L_INSTR_REC.OPERATOR_ACKN,
4840 L_INSTR_REC.INSTR_STATUS,
4841 DECODE(L_INSTR_REC.TASK_ID,
4842 NULL,NULL,
4843 GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS),
4844 L_INSTR_REC.TASK_ID,
4845 DECODE(L_INSTR_REC.TASK_ID,NULL,NULL,GMO_CONSTANTS_GRP.NO),
4846 L_CREATION_DATE, -- FIGURE THIS OUT
4847 L_CREATED_BY,
4848 L_LAST_UPDATE_DATE,
4849 L_LAST_UPDATED_BY,
4850 L_LAST_UPDATE_LOGIN
4851 );
4852
4853 OPEN L_INSTR_TASK_CSR;
4854 LOOP
4855 FETCH L_INSTR_TASK_CSR INTO L_INSTR_TASK_REC;
4856 EXIT WHEN L_INSTR_TASK_CSR%NOTFOUND;
4857
4858 INSERT INTO GMO_INSTR_TASK_INSTANCE_T
4859 (
4860 INSTRUCTION_ID,
4861 INSTRUCTION_PROCESS_ID,
4862 INSTR_TASK_SEQ,
4863 TASK_EREC_ID,
4864 TASK_IDENTIFIER,
4865 TASK_VALUE,
4866 MANUAL_ENTRY,
4867 CREATION_DATE,
4868 CREATED_BY,
4869 LAST_UPDATE_DATE,
4870 LAST_UPDATED_BY,
4871 LAST_UPDATE_LOGIN
4872 )
4873 VALUES
4874 (
4875 L_INSTR_TASK_REC.INSTRUCTION_ID,
4876 P_INSTRUCTION_PROCESS_ID,
4877 L_INSTR_TASK_REC.INSTR_TASK_SEQ,
4878 L_INSTR_TASK_REC.TASK_EREC_ID,
4879 L_INSTR_TASK_REC.TASK_IDENTIFIER,
4880 L_INSTR_TASK_REC.TASK_VALUE,
4881 L_INSTR_TASK_REC.MANUAL_ENTRY,
4882 L_CREATION_DATE, -- FIGURE THIS OUT
4883 L_CREATED_BY,
4884 L_LAST_UPDATE_DATE,
4885 L_LAST_UPDATED_BY,
4886 L_LAST_UPDATE_LOGIN
4887 );
4888
4889 END LOOP;
4890 CLOSE L_INSTR_TASK_CSR;
4891
4892 OPEN L_INSTR_EREC_CSR;
4893 LOOP
4894 FETCH L_INSTR_EREC_CSR INTO L_INSTR_EREC_REC;
4895 EXIT WHEN L_INSTR_EREC_CSR%NOTFOUND;
4896
4897 INSERT INTO GMO_INSTR_EREC_INSTANCE_T
4898 (
4899 INSTRUCTION_ID,
4900 INSTRUCTION_PROCESS_ID,
4901 INSTR_EREC_SEQ,
4902 INSTR_EREC_ID,
4903 TASK_EREC_ID,
4904 CREATION_DATE,
4905 CREATED_BY,
4906 LAST_UPDATE_DATE,
4907 LAST_UPDATED_BY,
4908 LAST_UPDATE_LOGIN
4909
4910 )
4911 VALUES
4912 (
4913 L_INSTRUCTION_ID,
4914 P_INSTRUCTION_PROCESS_ID,
4915 L_INSTR_EREC_REC.INSTR_EREC_SEQ,
4916 L_INSTR_EREC_REC.INSTR_EREC_ID,
4917 L_INSTR_EREC_REC.TASK_EREC_ID,
4918 L_CREATION_DATE, -- FIGURE THIS OUT
4919 L_CREATED_BY,
4920 L_LAST_UPDATE_DATE,
4921 L_LAST_UPDATED_BY,
4922 L_LAST_UPDATE_LOGIN
4923 );
4924
4925 END LOOP;
4926 CLOSE L_INSTR_EREC_CSR;
4927
4928 END LOOP;
4929 CLOSE L_INSTR_CSR;
4930
4931 END LOOP;
4932 CLOSE L_INSTR_SET_CSR;
4933
4934 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4935
4936 EXCEPTION
4937 WHEN OTHERS THEN
4938 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4939
4940 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4941 FND_MSG_PUB.ADD_EXC_MSG
4942 (G_PKG_NAME,
4943 L_API_NAME);
4944 END IF;
4945
4946 FND_MSG_PUB.COUNT_AND_GET
4947 ( P_COUNT => X_MSG_COUNT,
4948 P_DATA => X_MSG_DATA);
4949
4950 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4951 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4952 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_TEMPORARY_INSTANCES',
4953 FALSE);
4954 END IF;
4955
4956 -- propogate this exception
4957 RAISE;
4958
4959 END CREATE_TEMPORARY_INSTANCES;
4960
4961
4962 -- This API creates instance context when called by entity application
4963 -- and returns an Instruction Process Id
4964 PROCEDURE CREATE_INSTANCE_CONTEXT
4965 (
4966 P_ENTITY_NAME IN VARCHAR2,
4967 P_ENTITY_KEY IN VARCHAR2,
4968 P_INSTRUCTION_TYPE IN VARCHAR2,
4969 P_CONTEXT_PARAM_NAME IN FND_TABLE_OF_VARCHAR2_255,
4970 P_CONTEXT_PARAM_VALUE IN FND_TABLE_OF_VARCHAR2_255,
4971 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
4972 X_INSTRUCTION_SET_ID OUT NOCOPY NUMBER,
4973 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4974 X_MSG_COUNT OUT NOCOPY NUMBER,
4975 X_MSG_DATA OUT NOCOPY VARCHAR2
4976
4977 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
4978
4979 L_INSTRUCTION_PROCESS_ID NUMBER;
4980 L_CREATION_DATE DATE;
4981 L_CREATED_BY NUMBER;
4982 L_LAST_UPDATE_DATE DATE;
4983 L_LAST_UPDATED_BY NUMBER;
4984 L_LAST_UPDATE_LOGIN NUMBER;
4985
4986 L_INSTRUCTION_SET_ID NUMBER;
4987 L_INSTR_SET_STATUS VARCHAR2(40);
4988
4989 L_API_NAME VARCHAR2(40);
4990 L_MESG_TEXT VARCHAR2(1000);
4991
4992 BEGIN
4993
4994 L_API_NAME := 'CREATE_INSTANCE_CONTEXT';
4995
4996 SELECT GMO_INSTR_PROCESS_ID_S.NEXTVAL INTO L_INSTRUCTION_PROCESS_ID
4997 FROM DUAL;
4998
4999 -- Begin Validation Block
5000 BEGIN
5001
5002 -- GET THE INSTRUCTION SET ID FROM INSTANCE INSTRUCTION SET TABLE
5003 SELECT MAX(INSTRUCTION_SET_ID) INTO L_INSTRUCTION_SET_ID
5004 FROM GMO_INSTR_SET_INSTANCE_VL
5005 WHERE ENTITY_NAME = P_ENTITY_NAME
5006 AND ENTITY_KEY = P_ENTITY_KEY
5007 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
5008
5009 -- CHECK IF THE INSTRUCTION SET IS NULLIFIED
5010 SELECT INSTR_SET_STATUS INTO L_INSTR_SET_STATUS
5011 FROM GMO_INSTR_SET_INSTANCE_VL
5012 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
5013
5014 -- CHECK THE INSTRUCTION SET STATUS, IT MUST BE ACTIVE
5015 -- FOR THE PROCESS TO PROCEED FURTHER
5016 IF( RTRIM(L_INSTR_SET_STATUS) <> GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE ) THEN
5017
5018 ROLLBACK;
5019 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5020
5021 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SET_INACTIVE');
5022 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
5023 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
5024
5025 L_MESG_TEXT := FND_MESSAGE.GET();
5026
5027 FND_MSG_PUB.ADD_EXC_MSG
5028 ( G_PKG_NAME,
5029 L_API_NAME,
5030 L_MESG_TEXT
5031 );
5032
5033 FND_MSG_PUB.COUNT_AND_GET
5034 (
5035 P_COUNT => X_MSG_COUNT,
5036 P_DATA => X_MSG_DATA
5037 );
5038
5039 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5040 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
5041 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_INSTANCE_CONTEXT',
5042 FALSE);
5043 END IF;
5044
5045 RETURN;
5046
5047 END IF;
5048
5049 EXCEPTION
5050 WHEN NO_DATA_FOUND THEN
5051 ROLLBACK;
5052 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5053
5054 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5055 FND_MSG_PUB.ADD_EXC_MSG
5056 (G_PKG_NAME,
5057 L_API_NAME);
5058 END IF;
5059
5060 FND_MSG_PUB.COUNT_AND_GET
5061 ( P_COUNT => X_MSG_COUNT,
5062 P_DATA => X_MSG_DATA);
5063
5064 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5065 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5066 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_CONTEXT',
5067 FALSE);
5068 END IF;
5069
5070 RETURN;
5071 END; -- Validation Block
5072
5073 GMO_UTILITIES.GET_WHO_COLUMNS
5074 (
5075 X_CREATION_DATE => L_CREATION_DATE,
5076 X_CREATED_BY => L_CREATED_BY,
5077 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
5078 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
5079 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
5080 );
5081
5082 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5083 (
5084 INSTRUCTION_PROCESS_ID,
5085 ATTRIBUTE_SEQ,
5086 ATTRIBUTE_NAME,
5087 ENTITY_NAME,
5088 ENTITY_KEY,
5089 INSTRUCTION_TYPE,
5090 ATTRIBUTE_TYPE,
5091 CREATION_DATE,
5092 CREATED_BY,
5093 LAST_UPDATE_DATE,
5094 LAST_UPDATED_BY,
5095 LAST_UPDATE_LOGIN
5096 )
5097 VALUES
5098 (
5099 L_INSTRUCTION_PROCESS_ID,
5100 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5101 GMO_CONSTANTS_GRP.G_PARAM_ENTITY,
5102 P_ENTITY_NAME,
5103 P_ENTITY_KEY,
5104 P_INSTRUCTION_TYPE,
5105 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5106 L_CREATION_DATE,
5107 L_CREATED_BY,
5108 L_LAST_UPDATE_DATE,
5109 L_LAST_UPDATED_BY,
5110 L_LAST_UPDATE_LOGIN
5111 );
5112
5113 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5114 (
5115 INSTRUCTION_PROCESS_ID,
5116 ATTRIBUTE_SEQ,
5117 ATTRIBUTE_NAME,
5118 ATTRIBUTE_VALUE,
5119 ATTRIBUTE_TYPE,
5120 CREATION_DATE,
5121 CREATED_BY,
5122 LAST_UPDATE_DATE,
5123 LAST_UPDATED_BY,
5124 LAST_UPDATE_LOGIN
5125 )
5126 VALUES
5127 (
5128 L_INSTRUCTION_PROCESS_ID,
5129 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5130 GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
5131 GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
5132 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5133 L_CREATION_DATE,
5134 L_CREATED_BY,
5135 L_LAST_UPDATE_DATE,
5136 L_LAST_UPDATED_BY,
5137 L_LAST_UPDATE_LOGIN
5138 );
5139
5140 IF ( (P_CONTEXT_PARAM_NAME IS NOT NULL)
5141 AND (P_CONTEXT_PARAM_VALUE IS NOT NULL )) THEN
5142
5143 FOR I IN 1..P_CONTEXT_PARAM_NAME.COUNT LOOP
5144 IF(P_CONTEXT_PARAM_NAME(I) IS NOT NULL) THEN
5145
5146 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5147 (
5148 INSTRUCTION_PROCESS_ID,
5149 ATTRIBUTE_SEQ,
5150 ATTRIBUTE_NAME,
5151 ATTRIBUTE_VALUE,
5152 ATTRIBUTE_TYPE,
5153 CREATION_DATE,
5154 CREATED_BY,
5155 LAST_UPDATE_DATE,
5156 LAST_UPDATED_BY,
5157 LAST_UPDATE_LOGIN
5158 )
5159 VALUES
5160 (
5161 L_INSTRUCTION_PROCESS_ID,
5162 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5163 P_CONTEXT_PARAM_NAME(i),
5164 P_CONTEXT_PARAM_VALUE(i),
5165 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5166 L_CREATION_DATE,
5167 L_CREATED_BY,
5168 L_LAST_UPDATE_DATE,
5169 L_LAST_UPDATED_BY,
5170 L_LAST_UPDATE_LOGIN
5171 );
5172
5173 END IF;
5174
5175 END LOOP;
5176 END IF;
5177
5178 -- Create temporary table entries from perm, by calling this procedure
5179 CREATE_TEMPORARY_INSTANCES
5180 (
5181 P_INSTRUCTION_PROCESS_ID => L_INSTRUCTION_PROCESS_ID,
5182 P_ENTITY_NAME => P_ENTITY_NAME,
5183 P_ENTITY_KEY => P_ENTITY_KEY,
5184 P_INSTRUCTION_TYPE => P_INSTRUCTION_TYPE,
5185 X_RETURN_STATUS => X_RETURN_STATUS,
5186 X_MSG_COUNT => X_MSG_COUNT,
5187 X_MSG_DATA => X_MSG_DATA
5188 );
5189
5190 --Commit changes
5191 COMMIT;
5192
5193 X_INSTRUCTION_SET_ID := L_INSTRUCTION_SET_ID;
5194 X_INSTRUCTION_PROCESS_ID := L_INSTRUCTION_PROCESS_ID;
5195
5196 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5197 X_MSG_COUNT := 0;
5198 X_MSG_DATA := FND_API.G_RET_STS_SUCCESS;
5199
5200 EXCEPTION
5201 WHEN OTHERS THEN
5202 ROLLBACK;
5203 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
5204
5205 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5206 FND_MSG_PUB.ADD_EXC_MSG
5207 (G_PKG_NAME,
5208 L_API_NAME);
5209 END IF;
5210
5211 FND_MSG_PUB.COUNT_AND_GET
5212 ( P_COUNT => X_MSG_COUNT,
5213 P_DATA => X_MSG_DATA);
5214
5215 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5216 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5217 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_INSTANCE_CONTEXT',
5218 FALSE);
5219 END IF;
5220
5221 END CREATE_INSTANCE_CONTEXT;
5222
5223 -- This API creates instance context when called by entity application
5224 -- and returns an Instruction Process Id. This flavor is created for
5225 -- calling applications which only have Instruction Set Id, and not
5226 -- Entity Name, Entity Key and Instruction Type.
5227
5228 PROCEDURE CREATE_INSTANCE_CONTEXT
5229 (
5230 P_INSTRUCTION_SET_ID IN NUMBER,
5231 P_CONTEXT_PARAM_NAME IN FND_TABLE_OF_VARCHAR2_255,
5232 P_CONTEXT_PARAM_VALUE IN FND_TABLE_OF_VARCHAR2_255,
5233 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
5234 X_ENTITY_NAME OUT NOCOPY VARCHAR2,
5235 X_ENTITY_KEY OUT NOCOPY VARCHAR2,
5236 X_INSTRUCTION_TYPE OUT NOCOPY VARCHAR2,
5237 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5238 X_MSG_COUNT OUT NOCOPY NUMBER,
5239 X_MSG_DATA OUT NOCOPY VARCHAR2
5240 )
5241 IS PRAGMA AUTONOMOUS_TRANSACTION;
5242
5243 L_INSTRUCTION_SET_ID NUMBER;
5244 L_INSTRUCTION_TYPE VARCHAR2(40);
5245 L_ENTITY_NAME VARCHAR2(200);
5246 L_ENTITY_KEY VARCHAR2(500);
5247
5248 L_API_NAME VARCHAR2(40);
5249 L_MESG_TEXT VARCHAR2(1000);
5250
5251 BEGIN
5252
5253 L_API_NAME := 'CREATE_INSTANCE_CONTEXT';
5254
5255 BEGIN
5256 SELECT INSTRUCTION_TYPE, ENTITY_NAME, ENTITY_KEY
5257 INTO L_INSTRUCTION_TYPE, L_ENTITY_NAME, L_ENTITY_KEY
5258 FROM GMO_INSTR_SET_INSTANCE_VL WHERE
5259 INSTRUCTION_SET_ID = P_INSTRUCTION_SET_ID;
5260 EXCEPTION
5261 WHEN NO_DATA_FOUND THEN
5262 ROLLBACK;
5263 X_INSTRUCTION_PROCESS_ID := -1;
5264 X_ENTITY_NAME := NULL;
5265 X_ENTITY_KEY := NULL;
5266 X_INSTRUCTION_TYPE := NULL;
5267
5268 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SET_INACTIVE');
5269 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
5270 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
5271
5272 L_MESG_TEXT := FND_MESSAGE.GET();
5273
5274 FND_MSG_PUB.ADD_EXC_MSG
5275 ( G_PKG_NAME,
5276 L_API_NAME,
5277 L_MESG_TEXT
5278 );
5279
5280 FND_MSG_PUB.COUNT_AND_GET
5281 (
5282 P_COUNT => X_MSG_COUNT,
5283 P_DATA => X_MSG_DATA
5284 );
5285
5286 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5287 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
5288 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_INSTANCE_CONTEXT',
5289 FALSE);
5290 END IF;
5291
5292 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5293
5294 RETURN;
5295 END;
5296
5297 CREATE_INSTANCE_CONTEXT (
5298 P_ENTITY_NAME => L_ENTITY_NAME,
5299 P_ENTITY_KEY => L_ENTITY_KEY,
5300 P_INSTRUCTION_TYPE => L_INSTRUCTION_TYPE,
5301 P_CONTEXT_PARAM_NAME => P_CONTEXT_PARAM_NAME,
5302 P_CONTEXT_PARAM_VALUE => P_CONTEXT_PARAM_VALUE,
5303 X_INSTRUCTION_PROCESS_ID => X_INSTRUCTION_PROCESS_ID,
5304 X_INSTRUCTION_SET_ID => L_INSTRUCTION_SET_ID,
5305 X_RETURN_STATUS => X_RETURN_STATUS,
5306 X_MSG_COUNT=> X_MSG_COUNT,
5307 X_MSG_DATA => X_MSG_DATA
5308 );
5309
5310 COMMIT;
5311
5312 X_ENTITY_NAME := L_ENTITY_NAME;
5313 X_ENTITY_KEY := L_ENTITY_KEY;
5314 X_INSTRUCTION_TYPE := L_INSTRUCTION_TYPE;
5315
5316 EXCEPTION
5317 WHEN OTHERS THEN
5318 ROLLBACK;
5319
5320 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5321 FND_MSG_PUB.ADD_EXC_MSG
5322 (G_PKG_NAME,
5323 L_API_NAME);
5324 END IF;
5325
5326 FND_MSG_PUB.COUNT_AND_GET
5327 ( P_COUNT => X_MSG_COUNT,
5328 P_DATA => X_MSG_DATA);
5329
5330 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5331 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5332 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_INSTANCE_CONTEXT',
5333 FALSE);
5334 END IF;
5335
5336 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
5337
5338 END CREATE_INSTANCE_CONTEXT;
5339
5340
5341 --This procedure updates the definition and
5342 --process status attribute values to "MODIFIED" and "SUCCESS"
5343 --respectively in GMO_INSTR_ATTRIBUTES_T for the specified
5344 --instruction process ID.
5345 PROCEDURE UPDATE_INSTR_ATTRIBUTES(P_INSTRUCTION_PROCESS_ID IN VARCHAR2,
5346 P_UPDATE_DEFN_STATUS IN VARCHAR2)
5347 IS
5348 PRAGMA AUTONOMOUS_TRANSACTION;
5349 L_INSTRUCTION_PROCESS_ID NUMBER;
5350
5351 L_API_NAME VARCHAR2(40);
5352
5353 BEGIN
5354
5355 L_API_NAME := 'UPDATE_INSTR_ATTRIBUTES';
5356
5357 --Convert the instruction process ID to number.
5358 L_INSTRUCTION_PROCESS_ID := TO_NUMBER(P_INSTRUCTION_PROCESS_ID,'999999999999.999999');
5359
5360 --Update the definition status if required based on the flag.
5361 IF(P_UPDATE_DEFN_STATUS = FND_API.G_TRUE) THEN
5362 UPDATE
5363 GMO_INSTR_ATTRIBUTES_T
5364 SET
5365 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
5366 WHERE
5367 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5368 AND
5369 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
5370 END IF;
5371
5372 --Update the PROCESS_STATUS to success.
5373 UPDATE
5374 GMO_INSTR_ATTRIBUTES_T
5375 SET
5376 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
5377 WHERE
5378 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5379 AND
5380 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5381
5382 COMMIT;
5383
5384 EXCEPTION
5385 WHEN OTHERS THEN
5386 ROLLBACK;
5387
5388 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5389 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5390 FND_MESSAGE.SET_TOKEN('PKG_NAME',G_PKG_NAME);
5391 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',L_API_NAME);
5392
5393 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5394 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5395 'gmo.plsql.GMO_INSTRUCTION_PVT.UPDATE_INSTR_ATTRIBUTES',
5396 FALSE
5397 );
5398 end if;
5399
5400 APP_EXCEPTION.RAISE_EXCEPTION;
5401
5402 END UPDATE_INSTR_ATTRIBUTES;
5403
5404 --This procedure sets the definition and
5405 --process status attribute values to "MODIFIED" and "SUCCESS"
5406 --respectively in GMO_INSTR_ATTRIBUTES_T for the specified
5407 --instruction process ID.
5408 PROCEDURE SET_INSTR_STATUS_ATTRIBUTES(P_INSTRUCTION_PROCESS_ID IN VARCHAR2,
5409 P_UPDATE_DEFN_STATUS IN VARCHAR2)
5410 IS
5411 PRAGMA AUTONOMOUS_TRANSACTION;
5412 L_INSTRUCTION_PROCESS_ID NUMBER;
5413
5414 L_API_NAME VARCHAR2(40);
5415
5416 BEGIN
5417
5418 L_API_NAME := 'SET_INSTR_STATUS_ATTRIBUTES';
5419
5420 --Convert the instruction process ID to number.
5421 L_INSTRUCTION_PROCESS_ID := TO_NUMBER(P_INSTRUCTION_PROCESS_ID,'999999999999.999999');
5422
5423 --Update the definition status if required based on the flag.
5424 IF(P_UPDATE_DEFN_STATUS = FND_API.G_TRUE) THEN
5425 UPDATE
5426 GMO_INSTR_ATTRIBUTES_T
5427 SET
5428 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
5429 WHERE
5430 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5431 AND
5432 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
5433 END IF;
5434
5435 --Update the PROCESS_STATUS to success.
5436 UPDATE
5437 GMO_INSTR_ATTRIBUTES_T
5438 SET
5439 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
5440 WHERE
5441 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5442 AND
5443 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5444
5445 COMMIT;
5446
5447 EXCEPTION
5448 WHEN OTHERS THEN
5449 ROLLBACK;
5450
5451 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5452 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5453 FND_MESSAGE.SET_TOKEN('PKG_NAME',G_PKG_NAME);
5454 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',L_API_NAME);
5455
5456 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5457 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5458 'gmo.plsql.GMO_INSTRUCTION_PVT.SET_INSTR_STATUS_ATTRIBUTES',
5459 FALSE
5460 );
5461 end if;
5462
5463 APP_EXCEPTION.RAISE_EXCEPTION;
5464
5465 END SET_INSTR_STATUS_ATTRIBUTES;
5466
5467
5468
5469 --This procedure deletes the instruction set details in
5470 -- GMO_INSTR_SET_DEFN_T for the specified
5471 --process ID. It also updates the process status in
5472 --GMO_INSTR_ATTRUBUTES_T to "CANCEL".
5473
5474 PROCEDURE DELETE_INSTR_SET_DETAILS
5475 (
5476 P_INSTRUCTION_PROCESS_ID IN VARCHAR2
5477 )
5478 IS
5479
5480 PRAGMA AUTONOMOUS_TRANSACTION;
5481
5482 L_INSTRUCTION_PROCESS_ID NUMBER;
5483
5484 BEGIN
5485
5486 L_INSTRUCTION_PROCESS_ID := TO_NUMBER(P_INSTRUCTION_PROCESS_ID,'999999999999.999999');
5487
5488 --Update the PROCESS_STATUS to cancel.
5489 UPDATE
5490 GMO_INSTR_ATTRIBUTES_T
5491 SET
5492 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_CANCEL
5493 WHERE
5494 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5495 AND
5496 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5497
5498 COMMIT;
5499
5500 EXCEPTION
5501 WHEN OTHERS THEN
5502
5503 ROLLBACK;
5504 --Diagnostics Start
5505 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5506 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5507 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_INSTRUCTION_PVT');
5508 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','DELETE_INSTR_SET_DETAILS');
5509
5510 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5511 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5512 'gmo.plsql.GMO_INSTRUCTION_PVT.DELETE_INSTR_SET_DETAILS',
5513 FALSE
5514 );
5515 end if;
5516
5517 APP_EXCEPTION.RAISE_EXCEPTION;
5518
5519 END DELETE_INSTR_SET_DETAILS;
5520
5521 -- This function gets the value of specified process variable
5522 FUNCTION GET_PROCESS_VARIABLE
5523 (
5524 P_INSTRUCTION_PROCESS_ID IN NUMBER,
5525 P_ATTRIBUTE_NAME IN VARCHAR2 ,
5526 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5527 )
5528 RETURN VARCHAR2
5529 IS
5530 L_ATTRIBUTE_VALUE VARCHAR2(1000);
5531 BEGIN
5532 SELECT ATTRIBUTE_VALUE INTO L_ATTRIBUTE_VALUE
5533 FROM GMO_INSTR_ATTRIBUTES_T
5534 WHERE ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
5535 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
5536 AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
5537
5538 RETURN L_ATTRIBUTE_VALUE;
5539
5540 EXCEPTION
5541 WHEN NO_DATA_FOUND THEN
5542 L_ATTRIBUTE_VALUE := NULL;
5543 RETURN L_ATTRIBUTE_VALUE;
5544
5545 END GET_PROCESS_VARIABLE;
5546
5547
5548 -- This function inserts a process variable in temporary
5549 -- session table gmo_instr_attributes_t
5550
5551 FUNCTION INSERT_PROCESS_VARIABLE
5552 (
5553 P_INSTRUCTION_PROCESS_ID IN NUMBER ,
5554 P_ATTRIBUTE_NAME IN VARCHAR2 ,
5555 P_ATTRIBUTE_VALUE IN VARCHAR2,
5556 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5557 )
5558 RETURN VARCHAR2
5559 IS
5560 PRAGMA AUTONOMOUS_TRANSACTION;
5561
5562 L_CREATION_DATE DATE;
5563 L_CREATED_BY NUMBER;
5564 L_LAST_UPDATE_DATE DATE;
5565 L_LAST_UPDATED_BY NUMBER;
5566 L_LAST_UPDATE_LOGIN NUMBER;
5567
5568 L_COUNT NUMBER;
5569
5570 BEGIN
5571
5572 GMO_UTILITIES.GET_WHO_COLUMNS
5573 (
5574 X_CREATION_DATE => L_CREATION_DATE,
5575 X_CREATED_BY => L_CREATED_BY,
5576 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
5577 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
5578 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
5579 );
5580
5581 SELECT COUNT(*) INTO L_COUNT FROM GMO_INSTR_ATTRIBUTES_T
5582 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
5583 AND ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
5584 AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
5585
5586 IF (P_ATTRIBUTE_NAME IS NOT NULL AND P_INSTRUCTION_PROCESS_ID IS NOT NULL) THEN
5587 IF L_COUNT > 0 THEN
5588
5589 UPDATE GMO_INSTR_ATTRIBUTES_T
5590 SET ATTRIBUTE_VALUE = P_ATTRIBUTE_VALUE,
5591 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
5592 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
5593 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
5594 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
5595 AND ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
5596 AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
5597
5598
5599 ELSE
5600
5601 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5602 (
5603 INSTRUCTION_PROCESS_ID,
5604 ATTRIBUTE_SEQ,
5605 ATTRIBUTE_NAME,
5606 ATTRIBUTE_VALUE,
5607 ATTRIBUTE_TYPE,
5608 CREATION_DATE,
5609 CREATED_BY,
5610 LAST_UPDATE_DATE,
5611 LAST_UPDATED_BY,
5612 LAST_UPDATE_LOGIN
5613 )
5614 VALUES
5615 (
5616 P_INSTRUCTION_PROCESS_ID,
5617 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5618 P_ATTRIBUTE_NAME,
5619 P_ATTRIBUTE_VALUE,
5620 P_ATTRIBUTE_TYPE,
5621 L_CREATION_DATE,
5622 L_CREATED_BY,
5623 L_LAST_UPDATE_DATE,
5624 L_LAST_UPDATED_BY,
5625 L_LAST_UPDATE_LOGIN
5626 );
5627 END IF;
5628 ELSE
5629 RETURN GMO_CONSTANTS_GRP.NO;
5630 END IF;
5631
5632 COMMIT;
5633
5634 RETURN GMO_CONSTANTS_GRP.YES;
5635
5636 EXCEPTION
5637 WHEN OTHERS THEN
5638 ROLLBACK;
5639 RETURN GMO_CONSTANTS_GRP.NO;
5640
5641 END INSERT_PROCESS_VARIABLE;
5642
5643
5644 -- This function sets the process variable to the value
5645 -- passed in the input.
5646
5647 FUNCTION SET_PROCESS_VARIABLE
5648 ( P_INSTRUCTION_PROCESS_ID IN NUMBER ,
5649 P_ATTRIBUTE_NAME IN VARCHAR2 ,
5650 P_ATTRIBUTE_VALUE IN VARCHAR2,
5651 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5652 )
5653 RETURN VARCHAR2
5654 IS
5655 L_CREATION_DATE DATE;
5656 L_CREATED_BY NUMBER;
5657 L_LAST_UPDATE_DATE DATE;
5658 L_LAST_UPDATED_BY NUMBER;
5659 L_LAST_UPDATE_LOGIN NUMBER;
5660 L_ATTRIBUTE_VALUE VARCHAR2(1000);
5661
5662 PRAGMA AUTONOMOUS_TRANSACTION;
5663
5664 BEGIN
5665
5666 UPDATE GMO_INSTR_ATTRIBUTES_T
5667 SET ATTRIBUTE_VALUE = P_ATTRIBUTE_VALUE
5668 WHERE ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
5669 AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE
5670 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
5671
5672 IF(SQL%ROWCOUNT = 0 ) THEN
5673
5674 GMO_UTILITIES.GET_WHO_COLUMNS
5675 (
5676 X_CREATION_DATE => L_CREATION_DATE,
5677 X_CREATED_BY => L_CREATED_BY,
5678 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
5679 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
5680 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
5681 );
5682
5683 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5684 (
5685 INSTRUCTION_PROCESS_ID,
5686 ATTRIBUTE_SEQ,
5687 ATTRIBUTE_NAME,
5688 ATTRIBUTE_VALUE,
5689 ATTRIBUTE_TYPE,
5690 CREATION_DATE,
5691 CREATED_BY,
5692 LAST_UPDATE_DATE,
5693 LAST_UPDATED_BY,
5694 LAST_UPDATE_LOGIN
5695 )
5696 VALUES
5697 (
5698 P_INSTRUCTION_PROCESS_ID,
5699 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5700 P_ATTRIBUTE_NAME,
5701 P_ATTRIBUTE_VALUE,
5702 P_ATTRIBUTE_TYPE,
5703 L_CREATION_DATE,
5704 L_CREATED_BY,
5705 L_LAST_UPDATE_DATE,
5706 L_LAST_UPDATED_BY,
5707 L_LAST_UPDATE_LOGIN
5708 );
5709 END IF;
5710
5711 COMMIT;
5712
5713 RETURN GMO_CONSTANTS_GRP.YES;
5714 EXCEPTION
5715 WHEN OTHERS THEN
5716 ROLLBACK;
5717 RETURN GMO_CONSTANTS_GRP.NO;
5718
5719 END SET_PROCESS_VARIABLE;
5720
5721 -- This API is wrapper over ERES E-record Validation API.
5722 -- Returns Y or N based on E-record Id validity
5723
5724 PROCEDURE VALIDATE_TASK_ERECORD_ID
5725 (
5726 P_TASK_ERECORD_ID IN FND_TABLE_OF_VARCHAR2_255,
5727 X_ERECORD_ID_INVALID OUT NOCOPY VARCHAR2,
5728 X_ERECORD_LIST_STR OUT NOCOPY VARCHAR2,
5729 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5730 X_MSG_COUNT OUT NOCOPY NUMBER,
5731 X_MSG_DATA OUT NOCOPY VARCHAR2
5732 )
5733 IS
5734 L_MSG_COUNT NUMBER;
5735 L_MSG_DATA VARCHAR2(4000);
5736 L_RETURN_STATUS VARCHAR2(1);
5737 L_ERECORD_ID VARCHAR2(100);
5738 L_INVALID_ERECORD_LIST VARCHAR2(4000);
5739
5740 L_MESG_TEXT VARCHAR2(1000);
5741 L_API_NAME VARCHAR2(40);
5742
5743 BEGIN
5744
5745 L_API_NAME := 'VALIDATE_TASK_ERECORD_ID';
5746
5747 L_INVALID_ERECORD_LIST := '';
5748
5749 FOR I IN 1..P_TASK_ERECORD_ID.COUNT LOOP
5750 L_ERECORD_ID := P_TASK_ERECORD_ID(I);
5751
5752 EDR_ERES_EVENT_PUB.VALIDATE_ERECORD
5753 (
5754 p_api_version => '1.0',
5755 P_init_msg_list => FND_API.G_FALSE ,
5756 x_return_status => L_RETURN_STATUS ,
5757 X_msg_count => L_MSG_COUNT ,
5758 x_msg_data => L_MSG_DATA,
5759 p_erecord_id => TO_NUMBER(L_ERECORD_ID,999999999999.999999)
5760 );
5761
5762 IF(L_RETURN_STATUS = FND_API.G_RET_STS_ERROR) THEN
5763 L_INVALID_ERECORD_LIST := L_INVALID_ERECORD_LIST || ',' || L_ERECORD_ID;
5764 END IF;
5765
5766 END LOOP;
5767
5768 IF ( LENGTH(L_INVALID_ERECORD_LIST) > 0) THEN
5769 X_ERECORD_LIST_STR := SUBSTR(L_INVALID_ERECORD_LIST,2,LENGTH(L_INVALID_ERECORD_LIST));
5770 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5771 X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.YES;
5772 ELSE
5773 X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.NO;
5774 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5775 END IF;
5776
5777 EXCEPTION
5778 WHEN OTHERS THEN
5779 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5780 X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.YES;
5781
5782 --Diagnostics Start
5783 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5784 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5785 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_INSTRUCTION_PVT');
5786 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',L_API_NAME);
5787 L_MESG_TEXT := FND_MESSAGE.GET;
5788
5789 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5790 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5791 'gmo.plsql.GMO_INSTRUCTION_PVT.'|| L_API_NAME,
5792 FALSE
5793 );
5794 end if;
5795
5796 FND_MSG_PUB.ADD_EXC_MSG
5797 ( G_PKG_NAME,
5798 L_API_NAME,
5799 L_MESG_TEXT
5800 );
5801
5802 FND_MSG_PUB.COUNT_AND_GET
5803 (
5804 P_COUNT => X_MSG_COUNT,
5805 P_DATA => X_MSG_DATA
5806 );
5807
5808 END VALIDATE_TASK_ERECORD_ID;
5809
5810 -- This procedure is used to fetch the instruction set and related
5811 -- instruction details in XML format
5812
5813 PROCEDURE GET_INSTR_XML(P_INSTRUCTION_PROCESS_ID IN NUMBER,
5814 X_OUTPUT_XML OUT NOCOPY CLOB)
5815 IS
5816 --This variable would hold the XML details in XMLType format.
5817 L_INSTR_XML XMLTYPE;
5818
5819 BEGIN
5820 --This SQL Query would provide all the instruction details for the specified process ID in XMLType.
5821 SELECT XMLELEMENT("INSTRUCTIONS",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
5822 XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
5823 INSTR_SET.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
5824 INSTR_SET.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
5825 (SELECT
5826 LK.MEANING
5827 FROM
5828 FND_LOOKUP_VALUES_VL LK
5829 WHERE
5830 LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SET.ENTITY_NAME
5831 AND
5832 LK.LOOKUP_CODE = INSTR_SET.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
5833 (SELECT
5834 XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",
5835 XMLFOREST(INSTR.INSTR_NUMBER AS INSTRUCTION_NUMBER,
5836 INSTR.INSTRUCTION_TEXT AS INSTRUCTION_TEXT,
5837 (SELECT
5838 LK1.MEANING
5839 FROM
5840 FND_LOOKUP_VALUES_VL LK1
5841 WHERE
5842 LK1.LOOKUP_TYPE = 'GMO_INSTR_ACKN_TYPES'
5843 AND
5844 LK1.LOOKUP_CODE = INSTR.INSTR_ACKN_TYPE) AS INSTRUCTION_ACKN_TYPE,
5845 (SELECT
5846 TK.DISPLAY_NAME
5847 FROM
5848 GMO_INSTR_TASK_DEFN_VL TK
5849 WHERE
5850 TK.TASK_ID = INSTR.TASK_ID) AS TASK_NAME,
5851 INSTR.TASK_ATTRIBUTE AS TASK_ATTRIBUTE,
5852 INSTR.TASK_LABEL AS TASK_LABEL,
5853 (SELECT
5854 DECODE((SELECT COUNT(*) FROM GMO_INSTR_APPR_DEFN_T APPR
5855 WHERE
5856 APPR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID
5857 AND APPR.INSTRUCTION_ID = INSTR.INSTRUCTION_ID),0,
5858 FND_MESSAGE.GET_STRING('GMO','GMO_INSTR_SIG_NOT_REQUIRED'),
5859 FND_MESSAGE.GET_STRING('GMO','GMO_INSTR_SIG_REQUIRED'))
5860 FROM DUAL)
5861 AS SIGNATURE_REQUIRED
5862 )
5863 )
5864 )
5865 FROM GMO_INSTR_DEFN_T INSTR
5866 WHERE INSTR.INSTRUCTION_SET_ID = INSTR_SET.INSTRUCTION_SET_ID
5867 AND INSTR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID) AS "RELATED_INSTRUCTIONS")
5868 )
5869 )
5870 )
5871 INTO L_INSTR_XML
5872 FROM GMO_INSTR_SET_DEFN_T INSTR_SET
5873 WHERE INSTR_SET.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
5874
5875 --Convert XMLType to CLOB.
5876 X_OUTPUT_XML := L_INSTR_XML.GETCLOBVAL();
5877
5878 END GET_INSTR_XML;
5879
5880 FUNCTION SET_PROCESS_ATTRIBUTES
5881 (
5882 P_INSTRUCTION_PROCESS_ID IN NUMBER,
5883 P_ATTRIBUTE_NAME IN FND_TABLE_OF_VARCHAR2_255,
5884 P_ATTRIBUTE_VALUE IN FND_TABLE_OF_VARCHAR2_255,
5885 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5886 )
5887 RETURN VARCHAR2
5888 IS
5889 PRAGMA AUTONOMOUS_TRANSACTION;
5890
5891 L_PARAM_NAME VARCHAR2(200);
5892 L_PARAM_VALUE VARCHAR2(500);
5893 L_RETURN_STATUS VARCHAR2(1);
5894
5895 BEGIN
5896
5897 FOR I IN 1..P_ATTRIBUTE_NAME.COUNT LOOP
5898 L_PARAM_NAME := P_ATTRIBUTE_NAME(I);
5899
5900 IF( L_PARAM_NAME IS NOT NULL) THEN
5901 -- set process variable will update the attribute if it exists,
5902 -- else, inserts it
5903 L_RETURN_STATUS := SET_PROCESS_VARIABLE(
5904 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
5905 P_ATTRIBUTE_NAME => L_PARAM_NAME,
5906 P_ATTRIBUTE_VALUE => P_ATTRIBUTE_VALUE(I),
5907 P_ATTRIBUTE_TYPE => P_ATTRIBUTE_TYPE);
5908 END IF;
5909
5910 --Check the return status for each process attribute
5911 IF (L_RETURN_STATUS = GMO_CONSTANTS_GRP.NO) THEN
5912 ROLLBACK;
5913 APP_EXCEPTION.RAISE_EXCEPTION;
5914 END IF;
5915
5916 END LOOP;
5917
5918 -- SAVE CHANGES
5919 COMMIT;
5920
5921 RETURN GMO_CONSTANTS_GRP.YES;
5922
5923 EXCEPTION
5924 WHEN OTHERS THEN
5925 ROLLBACK;
5926 APP_EXCEPTION.RAISE_EXCEPTION;
5927
5928 END SET_PROCESS_ATTRIBUTES;
5929
5930 -- This API returns the Task parameter from the GMO Attributes Temp table
5931 FUNCTION GET_TASK_PARAMETER
5932 (
5933 P_INSTRUCTION_PROCESS_ID IN NUMBER,
5934 P_ATTRIBUTE_NAME IN VARCHAR2
5935 )
5936 RETURN VARCHAR2
5937 IS PRAGMA AUTONOMOUS_TRANSACTION;
5938 L_PARAM_VALUE VARCHAR2(500);
5939 BEGIN
5940
5941 L_PARAM_VALUE := GET_PROCESS_VARIABLE(P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
5942 P_ATTRIBUTE_NAME => P_ATTRIBUTE_NAME,
5943 P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_TASK );
5944 RETURN L_PARAM_VALUE;
5945
5946 EXCEPTION
5947 WHEN OTHERS THEN
5948 APP_EXCEPTION.RAISE_EXCEPTION;
5949
5950 END GET_TASK_PARAMETER;
5951
5952 -- This API adds the selected instruction to the working instruction set during
5953 -- Instruction setup time.
5954
5955 PROCEDURE ADD_INSTRUCTIONS (
5956 P_INSTRUCTION_PROCESS_ID IN NUMBER,
5957 P_INSTRUCTION_SET_ID IN NUMBER,
5958 P_INSTRUCTION_ID IN NUMBER,
5959 P_ADD_MODE IN VARCHAR2,
5960 P_INSTRUCTIONS IN FND_TABLE_OF_VARCHAR2_255,
5961 P_INSTRUCTION_NOS IN FND_TABLE_OF_VARCHAR2_255,
5962 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5963 X_MSG_COUNT OUT NOCOPY NUMBER,
5964 X_MSG_DATA OUT NOCOPY VARCHAR2
5965 )
5966 IS PRAGMA AUTONOMOUS_TRANSACTION;
5967
5968 l_creation_date date;
5969 l_created_by number;
5970 l_last_update_date date;
5971 l_last_updated_by number;
5972 l_last_update_login number;
5973
5974 l_count number;
5975 l_working_instr_seq number;
5976
5977 l_working_instruction_id number;
5978 l_instruction_id number;
5979 l_instr_seq number;
5980 l_task_id number;
5981 l_task_attribute_id varchar2(4000);
5982 l_task_attribute varchar2(4000);
5983 l_instr_ackn_type varchar2(40);
5984 l_instr_text varchar2(4000);
5985 l_task_label varchar2(200);
5986 l_approver_seq number;
5987 l_role_name varchar2(300);
5988 l_appr_count number;
5989 l_valid_process number;
5990 -- Bug 5686314 : start
5991 l_maximum_allowed_task number;
5992 l_task_count_per_inst_set number;
5993 -- Bug 5686314 : End
5994
5995 cursor get_instr_detail is
5996 select instruction_id, task_id, task_attribute_id,
5997 task_attribute, instr_ackn_type,
5998 instruction_text, task_label
5999 from gmo_instr_defn_vl
6000 where instruction_id = l_instruction_id;
6001
6002 cursor get_instr_appr_detail is
6003 select approver_seq, role_name, role_count
6004 from gmo_instr_appr_defn
6005 where instruction_id = l_instruction_id;
6006
6007 cursor is_valid_process is
6008 select count(*) from gmo_instr_set_defn_t
6009 where instruction_set_id = P_INSTRUCTION_SET_ID
6010 and instruction_process_id = P_INSTRUCTION_PROCESS_ID;
6011 -- Bug 5686314 : rvsingh :start
6012 cursor task_count_per_inst_set is
6013 select count(*) from gmo_instr_defn_t where
6014 instruction_process_id = p_instruction_process_id
6015 AND instruction_set_id = p_instruction_set_id
6016 AND task_id =l_task_id;
6017
6018 INVALID_TASK_ERR exception;
6019 -- Bug 5686314 : rvsingh :End
6020
6021 INVALID_PROCESS_ERR exception;
6022 INVALID_PROCESS_INSTR_ERR exception;
6023
6024 BEGIN
6025 GMO_UTILITIES.GET_WHO_COLUMNS
6026 (
6027 X_CREATION_DATE => L_CREATION_DATE,
6028 X_CREATED_BY => L_CREATED_BY,
6029 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
6030 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
6031 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
6032 );
6033
6034 open is_valid_process;
6035 fetch is_valid_process into l_valid_process;
6036 close is_valid_process;
6037
6038 if (l_valid_process = 0) then
6039 raise INVALID_PROCESS_ERR;
6040 end if;
6041
6042 l_count := p_instructions.count;
6043 l_working_instr_seq := 0;
6044 l_instr_seq := 0;
6045
6046 if (l_count > 0) then
6047
6048 if (p_instruction_id is not null) then
6049 select instr_seq into l_instr_seq
6050 from gmo_instr_defn_t
6051 where instruction_set_id = P_INSTRUCTION_SET_ID
6052 and instruction_process_id = P_INSTRUCTION_PROCESS_ID
6053 and instruction_id = P_INSTRUCTION_ID;
6054
6055 if (l_instr_seq <> 0) then
6056 if (P_ADD_MODE = 'AFTER') then
6057 l_working_instr_seq := l_instr_seq + 1;
6058 elsif (P_ADD_MODE = 'BEFORE') then
6059 l_working_instr_seq := l_instr_seq;
6060 end if;
6061
6062 update gmo_instr_defn_t
6063 set instr_seq = instr_seq + l_count
6064 where instruction_set_id = P_INSTRUCTION_SET_ID
6065 and instruction_process_id = P_INSTRUCTION_PROCESS_ID
6066 and instr_seq >= l_working_instr_seq;
6067
6068 else
6069 raise INVALID_PROCESS_INSTR_ERR;
6070 end if;
6071
6072 end if;
6073
6074 if (l_working_instr_seq = 0) then
6075 select nvl(max(instr_seq), 0) into l_working_instr_seq
6076 from gmo_instr_defn_t
6077 where instruction_set_id = P_INSTRUCTION_SET_ID
6078 and instruction_process_id = P_INSTRUCTION_PROCESS_ID;
6079
6080 l_working_instr_seq := l_working_instr_seq + 1;
6081 end if;
6082
6083 for i in 1 .. P_INSTRUCTIONS.count loop
6084 l_instruction_id := to_number (P_INSTRUCTIONS (i));
6085
6086 open get_instr_detail;
6087 fetch get_instr_detail
6088 into l_instruction_id,l_task_id,
6089 l_task_attribute_id,l_task_attribute,
6090 l_instr_ackn_type, l_instr_text, l_task_label;
6091
6092 close get_instr_detail;
6093
6094 if (l_instruction_id is not null) then
6095 -- task id is not null then check with maximum allowed task
6096 if(l_task_id IS NOT NULL) THEN
6097 select max_allowed_task into l_maximum_allowed_task from GMO_INSTR_TASK_DEFN_VL
6098 where task_id = l_task_id;
6099 open task_count_per_inst_set;
6100 fetch task_count_per_inst_set into l_task_count_per_inst_set;
6101 if(l_task_count_per_inst_set >= l_maximum_allowed_task) THEN
6102 RAISE INVALID_TASK_ERR;
6103 end if;
6104 end if;
6105
6106 select gmo_instr_defn_s.nextval into l_working_instruction_id from dual;
6107
6108 insert into gmo_instr_defn_t
6109 (instruction_id,
6110 instruction_process_id,
6111 instruction_text,
6112 instruction_set_id,
6113 instr_seq,
6114 task_id,
6115 task_attribute_id,
6116 task_attribute,
6117 instr_ackn_type,
6118 instr_number,
6119 creation_date,
6120 created_by,
6121 last_update_date,
6122 last_updated_by,
6123 last_update_login,
6124 task_label)
6125 values
6126 (
6127 l_working_instruction_id,
6128 p_instruction_process_id,
6129 l_instr_text,
6130 p_instruction_set_id,
6131 l_working_instr_seq,
6132 l_task_id,
6133 l_task_attribute_id,
6134 l_task_attribute,
6135 l_instr_ackn_type,
6136 P_INSTRUCTION_NOS(i),
6137 l_creation_date,
6138 l_created_by,
6139 l_last_update_date,
6140 l_last_updated_by,
6141 l_last_update_login,
6142 l_task_label
6143 );
6144
6145 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
6146 X_from_entity_name => 'GMO_INSTR_DEFN_B',
6147 X_from_pk1_value => l_instruction_id,
6148 X_from_pk2_value => NULL,
6149 X_from_pk3_value => NULL,
6150 X_from_pk4_value => NULL,
6151 X_from_pk5_value => NULL,
6152 X_to_entity_name => 'GMO_INSTR_DEFN_T',
6153 X_to_pk1_value => l_working_instruction_id,
6154 X_to_pk2_value => P_INSTRUCTION_PROCESS_ID,
6155 X_to_pk3_value => NULL,
6156 X_to_pk4_value => NULL,
6157 X_to_pk5_value => NULL,
6158 X_created_by => L_CREATED_BY ,
6159 X_last_update_login => L_LAST_UPDATE_LOGIN,
6160 X_program_application_id => NULL,
6161 X_program_id => NULL,
6162 X_request_id => NULL,
6163 X_automatically_added_flag => 'N',
6164 X_from_category_id => NULL,
6165 X_to_category_id => NULL
6166 );
6167
6168 open get_instr_appr_detail;
6169 loop
6170 fetch get_instr_appr_detail into l_approver_seq,l_role_name, l_appr_count;
6171 exit when get_instr_appr_detail%notfound;
6172
6173 select gmo_instr_appr_defn_s.nextval
6174 into l_approver_seq from dual;
6175
6176 insert into
6177 gmo_instr_appr_defn_t
6178 (instruction_id, instruction_process_id,
6179 approver_seq, role_name, role_count, creation_date,
6180 created_by, last_update_date, last_updated_by, last_update_login)
6181 values (l_working_instruction_id, p_instruction_process_id,
6182 l_approver_seq, l_role_name, l_appr_count, l_creation_date,
6183 l_created_by, l_last_update_date, l_last_updated_by, l_last_update_login);
6184
6185 end loop;
6186 close get_instr_appr_detail;
6187 l_working_instr_seq := l_working_instr_seq + 1;
6188 end if;
6189
6190 end loop;
6191
6192 end if;
6193
6194 commit;
6195
6196 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
6197
6198 EXCEPTION
6199 WHEN INVALID_TASK_ERR THEN
6200 ROLLBACK;
6201
6202 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
6203 FND_MESSAGE.SET_NAME('GMO', 'GMO_EXCEEDED_TASK');
6204 FND_MSG_PUB.ADD;
6205 FND_MSG_PUB.COUNT_AND_GET
6206 (
6207 P_COUNT => X_MSG_COUNT,
6208 P_DATA => X_MSG_DATA
6209 );
6210 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6211 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
6212 'gmo.plsql.gmo_instructions_pvt.add_instructions',
6213 FALSE);
6214 END IF;
6215 WHEN OTHERS THEN
6216 rollback;
6217 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
6218 FND_MESSAGE.SET_NAME('GMO','GMO_INSTR_UNEXPECTED_DB_ERR');
6219 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6220 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
6221 FND_MSG_PUB.ADD;
6222 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
6223 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6224 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_instructions_pvt.add_instructions', FALSE);
6225 end if;
6226
6227 END ADD_INSTRUCTIONS;
6228
6229
6230 PROCEDURE GET_INSTR_INSTANCE_XML
6231 (
6232 P_INSTRUCTION_PROCESS_ID IN NUMBER,
6233 X_OUTPUT_XML OUT NOCOPY CLOB
6234 )
6235
6236 IS
6237
6238 --This variable would hold the required instruction details in XML format.
6239 L_INSTR_XML XMLTYPE;
6240
6241 BEGIN
6242
6243 --This SQLX query will fetch us the required XML.
6244 SELECT XMLELEMENT("INSTRUCTION_SET",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
6245 XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
6246 INSTR_SETVL.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
6247 INSTR_SETVL.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
6248 (SELECT
6249 LK.MEANING
6250 FROM
6251 FND_LOOKUP_VALUES_VL LK
6252 WHERE
6253 LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SETVL.ENTITY_NAME
6254 AND
6255 LK.LOOKUP_CODE = INSTR_SETVL.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
6256 (SELECT
6257 XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",XMLFOREST(INSTRVL.INSTRUCTION_TEXT,
6258 (SELECT
6259 MEANING
6260 FROM
6261 FND_LOOKUP_VALUES_VL LK1
6262 WHERE
6263 LK1.LOOKUP_TYPE = 'GMO_INSTR_STATUS_TYPES'
6264 AND
6265 LK1.LOOKUP_CODE = INSTR.INSTR_STATUS) AS INSTRUCTION_STATUS,
6266 (SELECT
6267 MEANING
6268 FROM
6269 FND_LOOKUP_VALUES_VL LK2
6270 WHERE
6271 LK2.LOOKUP_TYPE = 'GMO_INSTR_ACKN_TYPES'
6272 AND LK2.LOOKUP_CODE = INSTRVL.INSTR_ACKN_TYPE)
6273 AS INSTRUCTION_ACKN_TYPE,
6274 decode(INSTR.INSTR_STATUS,GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING,
6275 NULL,
6276 GMO_UTILITIES.GET_USER_DISPLAY_NAME(INSTR.LAST_UPDATED_BY))
6277 AS PERFORMED_BY,
6278 INSTR.LAST_UPDATE_DATE LAST_UPDATE_DATE,
6279 INSTR.COMMENTS COMMENTS,
6280 (SELECT
6281 MAX(EREC.INSTR_EREC_ID)
6282 FROM
6283 GMO_INSTR_EREC_INSTANCE EREC
6284 WHERE
6285 EREC.INSTRUCTION_ID = INSTR.INSTRUCTION_ID)
6286 AS INSTRUCTION_ERECORD_ID,
6287 (SELECT XMLAGG(XMLELEMENT("TASK_DETAILS",XMLFOREST(TK.TASK_EREC_ID AS TASK_ERECORD_ID,
6288 TK.TASK_IDENTIFIER AS TASK_IDENTIFIER,
6289 TK.TASK_VALUE AS TASK_VALUE)))
6290 FROM
6291 GMO_INSTR_TASK_INSTANCE TK
6292 WHERE TK.INSTRUCTION_ID = INSTR.INSTRUCTION_ID) AS TASKS
6293 )
6294 )
6295 )
6296 FROM GMO_INSTR_INSTANCE_T INSTR,
6297 GMO_INSTR_INSTANCE_VL INSTRVL
6298 WHERE INSTR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID
6299 AND INSTR.INSTRUCTION_ID = INSTRVL.INSTRUCTION_ID) AS "INSTRUCTIONS")
6300 )
6301 )
6302 )
6303 INTO L_INSTR_XML
6304 FROM GMO_INSTR_SET_INSTANCE_T INSTR_SET,
6305 GMO_INSTR_SET_INSTANCE_VL INSTR_SETVL
6306 WHERE INSTR_SET.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
6307 AND INSTR_SETVL.INSTRUCTION_SET_ID = INSTR_SET.INSTRUCTION_SET_ID;
6308
6309 --Return the CLOB value of the XML.
6310 X_OUTPUT_XML := L_INSTR_XML.GETCLOBVAL();
6311
6312 EXCEPTION
6313 WHEN OTHERS THEN
6314 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
6315 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6316 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_INSTRUCTION_PVT');
6317 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_INSTR_INSTANCE_XML');
6318 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6319 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
6320 'gmo.plsql.GMO_INSTRUCTION_PVT.GET_INSTR_INSTANCE_XML',
6321 FALSE
6322 );
6323 end if;
6324 APP_EXCEPTION.RAISE_EXCEPTION;
6325
6326 END GET_INSTR_INSTANCE_XML;
6327
6328
6329 --This function is used to terminate the instruction definition process identified by the
6330 --specified process ID.
6331 PROCEDURE TERMINATE_INSTR_DEFN_PROCESS
6332 (P_INSTRUCTION_PROCESS_ID IN NUMBER)
6333
6334 IS
6335
6336 PRAGMA AUTONOMOUS_TRANSACTION;
6337
6338 BEGIN
6339
6340 --Update the process status to complete for the instruction definition identified by the specified process ID.
6341 UPDATE GMO_INSTR_ATTRIBUTES_T
6342 SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_TERMINATE
6343 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
6344 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
6345 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
6346
6347 --Commit the transaction.
6348 COMMIT;
6349
6350 EXCEPTION WHEN OTHERS THEN
6351
6352 ROLLBACK;
6353
6354 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
6355 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6356 FND_MESSAGE.SET_TOKEN('PKG_NAME',G_PKG_NAME);
6357 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','TERMINATE_INSTR_DEFN_PROCESS');
6358
6359 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6360 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
6361 'gmo.plsql.GMO_INSTRUCTION_PVT.TERMINATE_INSTR_DEFN_PROCESS',
6362 FALSE
6363 );
6364 end if;
6365
6366 APP_EXCEPTION.RAISE_EXCEPTION;
6367
6368 END TERMINATE_INSTR_DEFN_PROCESS;
6369
6370 --Bug 5383022: start
6371 procedure is_task_attribute_used
6372 (
6373 p_instruction_process_id IN number,
6374 p_attribute_name IN varchar2,
6375 p_attribute_key IN varchar2,
6376 x_used_flag OUT NOCOPY varchar2,
6377 x_return_status OUT NOCOPY varchar2,
6378 x_msg_count OUT NOCOPY number,
6379 x_msg_data OUT NOCOPY varchar2
6380 )
6381 IS
6382 l_check_attribute varchar2(4000);
6383 l_count number;
6384 l_total_count number;
6385 l_user_response varchar2(1);
6386 l_instruction_set_id number;
6387 l_instruction_process_id number;
6388 l_valid_process number;
6389 BEGIN
6390
6391 l_check_attribute := null;
6392 l_count := 0;
6393 l_instruction_process_id := p_instruction_process_id;
6394
6395 --check valid process
6396 SELECT COUNT(*) into l_valid_process FROM GMO_INSTR_ATTRIBUTES_T
6397 WHERE INSTRUCTION_PROCESS_ID = p_instruction_process_id
6398 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
6399 AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS;
6400
6401 if (l_valid_process = 0) then
6402 l_instruction_process_id := -1;
6403 end if;
6404
6405
6406 if (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) then
6407 --attribute key is oprnLindId
6408 l_check_attribute := p_attribute_key || '$' || '%';
6409 elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) then
6410 --attribute key is oprnLineId$Resources
6411 l_check_attribute := p_attribute_key;
6412 elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_MATERIAL) then
6413 --attribute key is formulaLineId
6414 l_check_attribute := p_attribute_key;
6415 end if;
6416
6417 if (l_check_attribute is not null) then
6418 BEGIN
6419 if (l_instruction_process_id is null or l_instruction_process_id = -1) then
6420 --if process is not valid check permanent tables
6421 --we are not using any specific entity, so it will take care of all levels
6422 select count(*) into l_count
6423 from gmo_instr_defn_b
6424 where task_attribute_id like l_check_attribute;
6425
6426 l_total_count := l_count;
6427
6428 else
6429 select count(*) into l_count
6430 from gmo_instr_defn_t
6431 where instruction_process_id = l_instruction_process_id
6432 and task_attribute_id like l_check_attribute;
6433
6434 l_total_count := l_count;
6435
6436 --now check permanent tables for the attribute usage
6437 --for all instructions not in current process
6438
6439 select count(*) into l_count
6440 from gmo_instr_defn_b
6441 where task_attribute_id like l_check_attribute
6442 and instruction_set_id not in (select instruction_set_id from gmo_instr_set_defn_t where instruction_process_id = l_instruction_process_id);
6443
6444 l_total_count := l_total_count + l_count;
6445
6446 end if;
6447
6448 EXCEPTION
6449 WHEN NO_DATA_FOUND THEN
6450 null;
6451 END;
6452
6453 end if;
6454
6455 if (l_total_count > 0) then
6456 x_used_flag := GMO_CONSTANTS_GRP.YES;
6457 else
6458 x_used_flag := GMO_CONSTANTS_GRP.NO;
6459 end if;
6460
6461 x_return_status := GMO_CONSTANTS_GRP.RETURN_STATUS_SUCCESS;
6462
6463 EXCEPTION
6464 WHEN OTHERS THEN
6465 X_RETURN_STATUS := GMO_CONSTANTS_GRP.RETURN_STATUS_UNEXP_ERROR;
6466 FND_MESSAGE.SET_NAME('GMO','GMO_UNEXPECTED_DB_ERR');
6467 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6468 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
6469 X_MSG_DATA := fnd_message.get;
6470 END is_task_attribute_used;
6471
6472 --Bug 5383022: end
6473
6474 END GMO_INSTRUCTION_PVT;