[Home] [Help]
PACKAGE BODY: APPS.GMO_INSTRUCTION_PVT
Source
1 PACKAGE BODY GMO_INSTRUCTION_PVT AS
2 /* $Header: GMOVINTB.pls 120.32.12020000.7 2013/03/04 13:44:05 rborpatl 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 CURSOR L_AUTO_COMMIT_ATTRIBUTE IS
3703 SELECT ATTRIBUTE_VALUE
3704 FROM GMO_INSTR_ATTRIBUTES_T
3705 WHERE INSTRUCTION_PROCESS_ID=P_INSTRUCTION_PROCESS_ID
3706 AND Upper(ATTRIBUTE_NAME)='AUTO_COMMIT'
3707 AND Upper(ATTRIBUTE_TYPE)='ACONTEXT';
3708 L_INSTR_SET_REC L_TEMP_INSTR_SET_CSR%ROWTYPE;
3709 L_INSTR_REC L_TEMP_INSTR_CSR%ROWTYPE;
3710 L_INSTR_EREC_REC L_TEMP_INSTR_EREC_CSR%ROWTYPE;
3711 L_TASK_EREC_REC L_TEMP_TASK_EREC_CSR%ROWTYPE;
3712
3713 L_INSTR_ACKN_TYPE VARCHAR2(40);
3714 L_OPERATOR_ACKN VARCHAR2(1);
3715 L_INSTRUCTION_COUNT NUMBER;
3716
3717 L_INSTANCE_STATUS VARCHAR2(40);
3718
3719 L_API_NAME VARCHAR2(40);
3720 L_MESG_TEXT VARCHAR2(1000);
3721 L_IS_AUTOCOMMIT VARCHAR2(3); --Added by rborpatl
3722 l_set_active NUMBER;
3723
3724 BEGIN
3725
3726 L_API_NAME := 'SEND_INSTANCE_ACKN';
3727
3728 GMO_UTILITIES.GET_WHO_COLUMNS
3729 (
3730 X_CREATION_DATE => L_CREATION_DATE,
3731 X_CREATED_BY => L_CREATED_BY,
3732 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
3733 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
3734 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
3735 );
3736
3737 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
3738 FROM GMO_INSTR_SET_INSTANCE_T
3739 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
3740
3741 SELECT ENTITY_KEY INTO L_ENTITY_KEY
3742 FROM GMO_INSTR_SET_INSTANCE_VL
3743 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3744
3745 IF (P_ENTITY_KEY IS NOT NULL) THEN
3746
3747 IF(L_ENTITY_KEY LIKE (GMO_CONSTANTS_GRP.G_INSTR_PREFIX || '%' )) THEN
3748 --Bug 5224619: start
3749 UPDATE GMO_INSTR_SET_INSTANCE_B
3750 SET ENTITY_KEY = P_ENTITY_KEY
3751 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3752 --Bug 5224619: end
3753
3754 UPDATE_ENTITY_KEY
3755 (
3756 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
3757 P_ENTITY_KEY => P_ENTITY_KEY
3758 );
3759
3760 END IF;
3761
3762 END IF;
3763
3764 L_INSTANCE_STATUS := GET_PROCESS_VARIABLE
3765 ( P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
3766 P_ATTRIBUTE_NAME => GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
3767 P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
3768 );
3769
3770 /*
3771 Code added by rborpatl : Bug
3772 Auto Commit
3773 */
3774 OPEN L_AUTO_COMMIT_ATTRIBUTE;
3775 -- LOOP
3776 FETCH L_AUTO_COMMIT_ATTRIBUTE INTO L_IS_AUTOCOMMIT;
3777 CLOSE L_AUTO_COMMIT_ATTRIBUTE; -- ADDED BY RBORPATL FOR BUG.
3778
3779 -- EXIT WHEN L_AUTO_COMMIT_ATTRIBUTE%NOTFOUND;
3780
3781 /**
3782 Changes made for AutoCommit by rborpatl - ends
3783 Bug:
3784 */
3785 IF(L_INSTANCE_STATUS IS NOT NULL
3786 AND (L_INSTANCE_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_COMPLETE OR L_IS_AUTOCOMMIT = 'YES')) THEN
3787
3788 OPEN L_TEMP_INSTR_SET_CSR;
3789 LOOP
3790 FETCH L_TEMP_INSTR_SET_CSR INTO L_INSTR_SET_REC;
3791 EXIT WHEN L_TEMP_INSTR_SET_CSR%NOTFOUND;
3792
3793 --Bug 4730261:start
3794 --update the instructions only when the set is active
3795 select count(*) into l_set_active from gmo_instr_set_instance_vl
3796 where instruction_set_id = L_INSTR_SET_REC.INSTRUCTION_SET_ID
3797 and instr_set_status = GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE;
3798
3799 if (l_set_active > 0) then
3800 --Bug 4730261: end
3801 --Bug 5224619: start
3802 UPDATE GMO_INSTR_SET_INSTANCE_B
3803 SET
3804 ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
3805 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
3806 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
3807 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
3808 WHERE
3809 INSTRUCTION_SET_ID = L_INSTR_SET_REC.INSTRUCTION_SET_ID;
3810 --Bug 5224619: end
3811
3812 L_INSTRUCTION_SET_ID := L_INSTR_SET_REC.INSTRUCTION_SET_ID;
3813
3814 OPEN L_TEMP_INSTR_CSR;
3815 LOOP
3816 FETCH L_TEMP_INSTR_CSR INTO L_INSTR_REC;
3817 EXIT WHEN L_TEMP_INSTR_CSR%NOTFOUND;
3818
3819 L_INSTR_STATUS := L_INSTR_REC.INSTR_STATUS;
3820
3821 IF(L_INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE ) THEN
3822 L_INSTR_STATUS := GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE;
3823
3824 UPDATE GMO_INSTR_INSTANCE_T
3825 SET INSTR_STATUS = L_INSTR_STATUS
3826 WHERE INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE
3827 AND INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3828
3829 --Bug 5224619: start
3830 UPDATE GMO_INSTR_INSTANCE_TL
3831 SET
3832 COMMENTS = L_INSTR_REC.COMMENTS,
3833 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
3834 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
3835 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
3836 WHERE
3837 INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3838
3839 UPDATE GMO_INSTR_INSTANCE_B
3840 SET
3841 OPERATOR_ACKN = L_INSTR_REC.OPERATOR_ACKN,
3842 TASK_ACKN_STATUS = L_INSTR_REC.TASK_ACKN_STATUS,
3843 TASK_ACKN_DATE = L_INSTR_REC.TASK_ACKN_DATE,
3844 INSTR_STATUS = L_INSTR_STATUS,
3845 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
3846 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
3847 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
3848 WHERE
3849 INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3850 --Bug 5224619: end
3851
3852 L_INSTRUCTION_ID := L_INSTR_REC.INSTRUCTION_ID;
3853
3854 --First delete data from instr_erec_instance table
3855 DELETE FROM GMO_INSTR_EREC_INSTANCE
3856 WHERE INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3857
3858 OPEN L_TEMP_INSTR_EREC_CSR;
3859 LOOP
3860 FETCH L_TEMP_INSTR_EREC_CSR INTO L_INSTR_EREC_REC;
3861 EXIT WHEN L_TEMP_INSTR_EREC_CSR%NOTFOUND;
3862
3863 INSERT INTO GMO_INSTR_EREC_INSTANCE
3864 (
3865 INSTRUCTION_ID,
3866 INSTR_EREC_SEQ,
3867 TASK_EREC_ID,
3868 INSTR_EREC_ID,
3869 CREATION_DATE,
3870 CREATED_BY,
3871 LAST_UPDATE_DATE,
3872 LAST_UPDATED_BY,
3873 LAST_UPDATE_LOGIN
3874 )
3875 VALUES
3876 (
3877 L_INSTR_EREC_REC.INSTRUCTION_ID,
3878 L_INSTR_EREC_REC.INSTR_EREC_SEQ,
3879 L_INSTR_EREC_REC.TASK_EREC_ID,
3880 L_INSTR_EREC_REC.INSTR_EREC_ID,
3881 L_CREATION_DATE,
3882 L_CREATED_BY,
3883 L_LAST_UPDATE_DATE,
3884 L_LAST_UPDATED_BY,
3885 L_LAST_UPDATE_LOGIN
3886 );
3887
3888 END LOOP;
3889 CLOSE L_TEMP_INSTR_EREC_CSR;
3890
3891 --First delete data from instr_task_instance table
3892 DELETE FROM GMO_INSTR_TASK_INSTANCE
3893 WHERE INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3894
3895 OPEN L_TEMP_TASK_EREC_CSR;
3896 LOOP
3897 FETCH L_TEMP_TASK_EREC_CSR INTO L_TASK_EREC_REC;
3898 EXIT WHEN L_TEMP_TASK_EREC_CSR%NOTFOUND;
3899
3900 INSERT INTO GMO_INSTR_TASK_INSTANCE
3901 (
3902 INSTRUCTION_ID,
3903 INSTR_TASK_SEQ,
3904 TASK_EREC_ID,
3905 TASK_IDENTIFIER,
3906 TASK_VALUE,
3907 MANUAL_ENTRY,
3908 CREATION_DATE,
3909 CREATED_BY,
3910 LAST_UPDATE_DATE,
3911 LAST_UPDATED_BY,
3912 LAST_UPDATE_LOGIN
3913 )
3914 VALUES
3915 (
3916 L_TASK_EREC_REC.INSTRUCTION_ID,
3917 L_TASK_EREC_REC.INSTR_TASK_SEQ,
3918 L_TASK_EREC_REC.TASK_EREC_ID,
3919 L_TASK_EREC_REC.TASK_IDENTIFIER,
3920 L_TASK_EREC_REC.TASK_VALUE,
3921 L_TASK_EREC_REC.MANUAL_ENTRY,
3922 L_CREATION_DATE,
3923 L_CREATED_BY,
3924 L_LAST_UPDATE_DATE,
3925 L_LAST_UPDATED_BY,
3926 L_LAST_UPDATE_LOGIN
3927 );
3928
3929 END LOOP;
3930 CLOSE L_TEMP_TASK_EREC_CSR;
3931
3932 END IF; -- INSTR_STATUS = DONE
3933
3934 END LOOP;
3935 CLOSE L_TEMP_INSTR_CSR;
3936
3937 -- Bug 5231778 : update the Instructin Set Status before raising Event
3938 --Set the instruction set status to 'complete' if all the
3939 --instructions are complete in a given instruction set
3940 SELECT COUNT(*) INTO L_INSTRUCTION_COUNT
3941 FROM GMO_INSTR_INSTANCE_B
3942 WHERE INSTR_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE
3943 AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3944
3945 -- Set the Instruction set status to complete
3946 IF(L_INSTRUCTION_COUNT = 0) THEN
3947 --Bug 5224619: start
3948 UPDATE GMO_INSTR_SET_INSTANCE_B
3949 SET INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_COMPLETE
3950 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3951 --Bug 5224619: end
3952 END IF;
3953
3954 --Bug 4730261: start
3955 RAISE_INSTR_SET_EVENT(P_INSTRUCTION_SET_ID => L_INSTRUCTION_SET_ID);
3956 end if; -- end if (l_set_active > 0)
3957 --Bug 4730261: end
3958
3959 END LOOP;
3960 CLOSE L_TEMP_INSTR_SET_CSR;
3961
3962 END IF; -- If Instance Status is COMPLETE
3963
3964 -- END LOOP; -- Auto commit cursor loop
3965 --COMMIT CHANGES
3966 COMMIT;
3967
3968 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3969
3970 EXCEPTION
3971 WHEN OTHERS THEN
3972 ROLLBACK;
3973
3974 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3975 FND_MSG_PUB.ADD_EXC_MSG
3976 (G_PKG_NAME,
3977 L_API_NAME);
3978 END IF;
3979
3980 FND_MSG_PUB.COUNT_AND_GET
3981 ( P_COUNT => X_MSG_COUNT,
3982 P_DATA => X_MSG_DATA);
3983
3984 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3985 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3986 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_CONTEXT',
3987 FALSE);
3988 END IF;
3989
3990 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3991
3992 END SEND_INSTANCE_ACKN;
3993
3994 -- This API is called to send the task acknowledgment
3995 -- It acknowledges the task identifier, value and e-record
3996 -- id into the GMO_INSTR_TASK_INSTANCE_T table
3997
3998 PROCEDURE SEND_TASK_ACKN
3999 (
4000 P_INSTRUCTION_ID IN NUMBER,
4001 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4002 P_ENTITY_KEY IN VARCHAR2 DEFAULT NULL,
4003 P_TASK_ERECORD_ID IN FND_TABLE_OF_VARCHAR2_255,
4004 P_TASK_IDENTIFIER IN FND_TABLE_OF_VARCHAR2_255,
4005 P_TASK_VALUE IN FND_TABLE_OF_VARCHAR2_255,
4006 P_DISABLE_TASK IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO,
4007 P_MANUAL_ENTRY IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO,
4008 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4009 X_MSG_COUNT OUT NOCOPY NUMBER,
4010 X_MSG_DATA OUT NOCOPY VARCHAR2
4011 )
4012 IS PRAGMA AUTONOMOUS_TRANSACTION;
4013
4014 L_CREATION_DATE DATE;
4015 L_CREATED_BY NUMBER;
4016 L_LAST_UPDATE_DATE DATE;
4017 L_LAST_UPDATED_BY NUMBER;
4018 L_LAST_UPDATE_LOGIN NUMBER;
4019
4020 L_INSTRUCTION_ID NUMBER;
4021 L_INSTRUCTION_SET_ID NUMBER;
4022
4023 L_ENTITY_NAME VARCHAR2(200);
4024 L_ENTITY_KEY VARCHAR2(500);
4025
4026 L_INVALID_PARAM_ERR EXCEPTION;
4027 L_ENTITY_KEY_ERR EXCEPTION;
4028
4029 L_API_NAME VARCHAR2(40);
4030 L_MESG_TEXT VARCHAR2(1000);
4031
4032 L_PROCESS_COUNT NUMBER;
4033
4034 BEGIN
4035
4036 L_API_NAME := 'SEND_TASK_ACKN';
4037
4038 GMO_UTILITIES.GET_WHO_COLUMNS
4039 (
4040 X_CREATION_DATE => L_CREATION_DATE,
4041 X_CREATED_BY => L_CREATED_BY,
4042 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4043 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4044 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4045 );
4046
4047 -- VALIDATE THE INSTRUCTION ID FIRST BY SEEING IF IT EXISTS IN
4048 -- THE TEMPORARY TABLE
4049
4050 SELECT COUNT(*) INTO L_PROCESS_COUNT
4051 FROM GMO_INSTR_ATTRIBUTES_T
4052 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4053 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_INSTANCE_STATUS
4054 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
4055 AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_ERROR;
4056
4057 IF L_PROCESS_COUNT = 0 THEN
4058 RAISE L_INVALID_PARAM_ERR;
4059 END IF;
4060
4061 SELECT INSTRN.INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
4062 FROM
4063 GMO_INSTR_INSTANCE_T INSTR_TEMP,
4064 GMO_INSTR_INSTANCE_VL INSTRN
4065 WHERE
4066 INSTR_TEMP.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4067 AND INSTR_TEMP.INSTRUCTION_ID = P_INSTRUCTION_ID
4068 AND INSTR_TEMP.INSTRUCTION_ID = INSTRN.INSTRUCTION_ID;
4069
4070 IF ( P_MANUAL_ENTRY = GMO_CONSTANTS_GRP.YES ) THEN
4071 -- Remove all previously entered manual data, and enter the new
4072 -- rows passed in current API call, this will take care of
4073 -- delete task row in case of manual entry
4074
4075 DELETE FROM GMO_INSTR_TASK_INSTANCE_T
4076 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4077 AND INSTRUCTION_ID = P_INSTRUCTION_ID
4078 AND MANUAL_ENTRY = GMO_CONSTANTS_GRP.YES;
4079
4080 END IF;
4081
4082 FOR I IN 1..P_TASK_ERECORD_ID.COUNT LOOP
4083 --the task erecord can be null for some cases
4084 --so we insert the record when either id or erecord is available
4085 IF ( (P_TASK_ERECORD_ID(I) IS NOT NULL)
4086 OR (P_TASK_IDENTIFIER(I) IS NOT NULL)) THEN
4087
4088 INSERT INTO GMO_INSTR_TASK_INSTANCE_T
4089 (
4090 INSTRUCTION_PROCESS_ID,
4091 INSTRUCTION_ID,
4092 INSTR_TASK_SEQ,
4093 TASK_EREC_ID,
4094 TASK_IDENTIFIER,
4095 TASK_VALUE,
4096 MANUAL_ENTRY,
4097 CREATION_DATE,
4098 CREATED_BY,
4099 LAST_UPDATE_DATE,
4100 LAST_UPDATED_BY,
4101 LAST_UPDATE_LOGIN
4102 )
4103 VALUES
4104 (
4105 P_INSTRUCTION_PROCESS_ID,
4106 P_INSTRUCTION_ID,
4107 GMO_INSTR_TASK_INSTANCE_S.NEXTVAL,
4108 TO_NUMBER(P_TASK_ERECORD_ID(I),999999999999.999999),
4109 P_TASK_IDENTIFIER(I),
4110 P_TASK_VALUE(I),
4111 P_MANUAL_ENTRY,
4112 L_CREATION_DATE,
4113 L_CREATED_BY,
4114 L_LAST_UPDATE_DATE,
4115 L_LAST_UPDATED_BY,
4116 L_LAST_UPDATE_LOGIN
4117 );
4118
4119 END IF;
4120
4121 END LOOP;
4122
4123 -- UPDATE THE PARAMETER IN GMO_INSTR_INSTANCE_T 'DISABLE_TASK' = 'Y' OR 'N'
4124 UPDATE GMO_INSTR_INSTANCE_T
4125 SET
4126 DISABLE_TASK = P_DISABLE_TASK
4127 WHERE
4128 INSTRUCTION_ID = P_INSTRUCTION_ID
4129 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4130
4131 -- UPDATE THE ENTITY KEY IN PERM TABLES FOR THAT INSTRUCTION_ID AND
4132 -- INSTRUCTION SET ID
4133
4134 SELECT ENTITY_NAME, ENTITY_KEY
4135 INTO L_ENTITY_NAME, L_ENTITY_KEY
4136 FROM GMO_INSTR_SET_INSTANCE_VL
4137 WHERE
4138 INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4139
4140 -- IF THE ENTITY KEY IS internally set by PI while creation,
4141 -- UPDATE IT
4142 IF (L_ENTITY_KEY LIKE ( GMO_CONSTANTS_GRP.G_INSTR_PREFIX || '%')
4143 AND P_ENTITY_KEY IS NOT NULL) THEN
4144 --Bug 5224619: start
4145 UPDATE GMO_INSTR_SET_INSTANCE_B
4146 SET ENTITY_KEY = P_ENTITY_KEY
4147 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4148 --Bug 5224619: end
4149
4150 UPDATE_ENTITY_KEY
4151 (
4152 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
4153 P_ENTITY_KEY => P_ENTITY_KEY
4154 );
4155 END IF;
4156
4157 -- Acknowledge the task by setting the task ackn status
4158 -- only if API is called by task application
4159 IF(P_MANUAL_ENTRY <> GMO_CONSTANTS_GRP.YES ) THEN
4160 UPDATE GMO_INSTR_INSTANCE_T
4161 SET TASK_ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_TASK_ACKN_STATUS,
4162 TASK_ACKN_DATE = L_CREATION_DATE
4163 WHERE INSTRUCTION_ID = P_INSTRUCTION_ID
4164 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4165 END IF;
4166
4167 -- COMMIT TASK DATA
4168 COMMIT;
4169
4170 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4171
4172 EXCEPTION
4173 WHEN NO_DATA_FOUND THEN
4174 ROLLBACK;
4175 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4176
4177 WHEN L_INVALID_PARAM_ERR THEN
4178 ROLLBACK;
4179 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4180
4181 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_API_PARAM_ERR');
4182 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4183 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4184
4185 L_MESG_TEXT := FND_MESSAGE.GET();
4186
4187 FND_MSG_PUB.ADD_EXC_MSG
4188 ( G_PKG_NAME,
4189 L_API_NAME,
4190 L_MESG_TEXT
4191 );
4192
4193 FND_MSG_PUB.COUNT_AND_GET
4194 (
4195 P_COUNT => X_MSG_COUNT,
4196 P_DATA => X_MSG_DATA
4197 );
4198
4199 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4200 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4201 'gmo.plsql.GMO_INSTRUCTIONS_PVT.SEND_TASK_ACKN',
4202 FALSE);
4203 END IF;
4204
4205 WHEN L_ENTITY_KEY_ERR THEN
4206 ROLLBACK;
4207 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4208
4209 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_API_PARAM_ERR');
4210 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4211 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4212
4213 L_MESG_TEXT := FND_MESSAGE.GET();
4214
4215 FND_MSG_PUB.ADD_EXC_MSG
4216 ( G_PKG_NAME,
4217 L_API_NAME,
4218 L_MESG_TEXT
4219 );
4220
4221 FND_MSG_PUB.COUNT_AND_GET
4222 (
4223 P_COUNT => X_MSG_COUNT,
4224 P_DATA => X_MSG_DATA
4225 );
4226
4227 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4228 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4229 'gmo.plsql.GMO_INSTRUCTIONS_PVT.SEND_TASK_ACKN',
4230 FALSE);
4231 END IF;
4232
4233 WHEN OTHERS THEN
4234 ROLLBACK;
4235 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4236
4237 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4238 FND_MSG_PUB.ADD_EXC_MSG
4239 (G_PKG_NAME,
4240 L_API_NAME);
4241 END IF;
4242
4243 FND_MSG_PUB.COUNT_AND_GET
4244 ( P_COUNT => X_MSG_COUNT,
4245 P_DATA => X_MSG_DATA);
4246
4247 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4248 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4249 'gmo.plsql.GMO_INSTRUCTION_PVT.SEND_TASK_ACKN',
4250 FALSE);
4251 END IF;
4252 END SEND_TASK_ACKN;
4253
4254 -- This API marks the instruction set as CANCELLED
4255 -- It is used to de-activate an instruction set/
4256 PROCEDURE NULLIFY_INSTR_FOR_ENTITY
4257 (
4258 P_ENTITY_NAME IN VARCHAR2,
4259 P_ENTITY_KEY IN VARCHAR2,
4260 P_INSTRUCTION_TYPE IN VARCHAR2,
4261 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4262 X_MSG_COUNT OUT NOCOPY NUMBER,
4263 X_MSG_DATA OUT NOCOPY VARCHAR2
4264 )
4265 IS
4266 L_INSTRUCTION_SET_ID NUMBER;
4267
4268 L_API_NAME VARCHAR2(40);
4269
4270 BEGIN
4271
4272 L_API_NAME := 'NULLIFY_INSTR_FOR_ENTITY';
4273
4274 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
4275 FROM GMO_INSTR_SET_INSTANCE_VL
4276 WHERE ENTITY_NAME = P_ENTITY_NAME
4277 AND ENTITY_KEY = P_ENTITY_KEY
4278 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
4279 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
4280
4281 --Set the instruction set status to CANCEL
4282 --Bug 5224619: start
4283 UPDATE GMO_INSTR_SET_INSTANCE_B
4284 SET
4285 INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL
4286 WHERE
4287 INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4288 --Bug 5224619: end
4289 --Bug 4730261: start
4290 RAISE_INSTR_SET_EVENT(P_INSTRUCTION_SET_ID => L_INSTRUCTION_SET_ID);
4291 --Bug 4730261: end
4292
4293 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4294
4295 EXCEPTION
4296 WHEN NO_DATA_FOUND THEN
4297 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4298
4299 WHEN OTHERS THEN
4300 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4301
4302 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4303 FND_MSG_PUB.ADD_EXC_MSG
4304 (G_PKG_NAME,
4305 L_API_NAME);
4306 END IF;
4307
4308 FND_MSG_PUB.COUNT_AND_GET
4309 ( P_COUNT => X_MSG_COUNT,
4310 P_DATA => X_MSG_DATA );
4311
4312 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4313 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4314 'gmo.plsql.GMO_INSTRUCTION_PVT.NULLIFY_INSTR_FOR_ENTITY',
4315 FALSE);
4316 END IF;
4317
4318 END NULLIFY_INSTR_FOR_ENTITY;
4319
4320 -- This API must complete optional instructions in temp table for
4321 -- particular session
4322 PROCEDURE COMPLETE_OPTIONAL_INSTR
4323 (
4324 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4325 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4326 X_MSG_COUNT OUT NOCOPY NUMBER,
4327 X_MSG_DATA OUT NOCOPY VARCHAR2
4328 )
4329 IS PRAGMA AUTONOMOUS_TRANSACTION;
4330 L_INSTRUCTION_SET_ID NUMBER;
4331
4332 L_API_NAME VARCHAR2(40);
4333 L_CREATION_DATE DATE;
4334 L_CREATED_BY NUMBER;
4335 L_LAST_UPDATE_DATE DATE;
4336 L_LAST_UPDATED_BY NUMBER;
4337 L_LAST_UPDATE_LOGIN NUMBER;
4338
4339
4340 BEGIN
4341
4342 L_API_NAME := 'COMPLETE_OPTIONAL_INSTR';
4343 GMO_UTILITIES.GET_WHO_COLUMNS
4344 (
4345 X_CREATION_DATE => L_CREATION_DATE,
4346 X_CREATED_BY => L_CREATED_BY,
4347 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4348 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4349 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4350 );
4351
4352 SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
4353 FROM GMO_INSTR_SET_INSTANCE_T
4354 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4355
4356 -- This must complete optional instructions in temp table for
4357 -- particular session
4358 UPDATE GMO_INSTR_INSTANCE_T
4359 SET
4360 INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE,
4361 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
4362 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
4363 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
4364 WHERE
4365 INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
4366 AND
4367 INSTRUCTION_ID IN (
4368 SELECT INSTRUCTION_ID FROM GMO_INSTR_INSTANCE_B
4369 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
4370 AND INSTR_ACKN_TYPE = GMO_CONSTANTS_GRP.G_INSTR_OPTIONAL
4371 )
4372 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4373
4374 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4375
4376 --Commit the changes
4377 COMMIT;
4378
4379 EXCEPTION
4380 WHEN OTHERS THEN
4381 ROLLBACK;
4382
4383 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4384
4385 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4386 FND_MSG_PUB.ADD_EXC_MSG
4387 (G_PKG_NAME,
4388 L_API_NAME);
4389 END IF;
4390
4391 FND_MSG_PUB.COUNT_AND_GET
4392 ( P_COUNT => X_MSG_COUNT,
4393 P_DATA => X_MSG_DATA);
4394
4395 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4396 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4397 'gmo.plsql.GMO_INSTRUCTION_PVT.COMPLETE_OPTIONAL_INSTR',
4398 FALSE);
4399 END IF;
4400
4401 END COMPLETE_OPTIONAL_INSTR;
4402
4403 -- This API returns the Definition process status. It can be
4404 -- MODIFIED or NO_CHANGE.
4405
4406 PROCEDURE GET_DEFN_STATUS
4407 (
4408 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4409 X_DEFINITION_STATUS OUT NOCOPY VARCHAR2,
4410 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4411 X_MSG_COUNT OUT NOCOPY NUMBER,
4412 X_MSG_DATA OUT NOCOPY VARCHAR2
4413 )
4414 IS
4415 L_API_NAME VARCHAR2(40);
4416 L_MESG_TEXT VARCHAR2(4000);
4417 BEGIN
4418
4419 L_API_NAME := 'GET_DEFN_STATUS';
4420
4421 SELECT ATTRIBUTE_VALUE INTO X_DEFINITION_STATUS
4422 FROM GMO_INSTR_ATTRIBUTES_T
4423 WHERE ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS
4424 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4425 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
4426
4427 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4428
4429 EXCEPTION
4430 WHEN NO_DATA_FOUND THEN
4431
4432 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_INV_PROCESSID_ERR');
4433 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4434 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4435 FND_MESSAGE.SET_TOKEN('PROCESS_ID', P_INSTRUCTION_PROCESS_ID );
4436
4437 L_MESG_TEXT := FND_MESSAGE.GET();
4438
4439 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
4440 L_API_NAME,
4441 L_MESG_TEXT);
4442
4443 FND_MSG_PUB.COUNT_AND_GET(P_COUNT => X_MSG_COUNT,
4444 P_DATA => X_MSG_DATA);
4445
4446 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4447 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4448 'gmo.plsql.GMO_INSTRUCTIONS_PVT.GET_DEFN_STATUS',
4449 FALSE);
4450 END IF;
4451
4452 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4453
4454 WHEN OTHERS THEN
4455 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4456
4457 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4458 FND_MSG_PUB.ADD_EXC_MSG
4459 (G_PKG_NAME,
4460 L_API_NAME);
4461 END IF;
4462
4463 FND_MSG_PUB.COUNT_AND_GET
4464 ( P_COUNT => X_MSG_COUNT,
4465 P_DATA => X_MSG_DATA);
4466
4467 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4468 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4469 'gmo.plsql.GMO_INSTRUCTION_PVT.GET_DEFN_STATUS',
4470 FALSE);
4471 END IF;
4472
4473 END GET_DEFN_STATUS;
4474
4475
4476 -- This API returns the INSTANCE_STATUS, It can be PENDING or COMPLETE
4477 -- or TERMINATE
4478
4479 PROCEDURE GET_INSTANCE_STATUS
4480 (
4481 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4482 X_INSTANCE_STATUS OUT NOCOPY VARCHAR2,
4483 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4484 X_MSG_COUNT OUT NOCOPY NUMBER,
4485 X_MSG_DATA OUT NOCOPY VARCHAR2
4486 )
4487 IS
4488 L_API_NAME VARCHAR2(40);
4489 L_MESG_TEXT VARCHAR2(4000);
4490 BEGIN
4491
4492 L_API_NAME := 'GET_INSTANCE_STATUS';
4493
4494 SELECT ATTRIBUTE_VALUE INTO X_INSTANCE_STATUS
4495 FROM GMO_INSTR_ATTRIBUTES_T
4496 WHERE ATTRIBUTE_NAME = 'INSTANCE_STATUS'
4497 AND ATTRIBUTE_TYPE = 'INTERNAL'
4498 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4499
4500 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4501
4502 EXCEPTION
4503 WHEN NO_DATA_FOUND THEN
4504
4505 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_INV_PROCESSID_ERR');
4506 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4507 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4508 FND_MESSAGE.SET_TOKEN('PROCESS_ID', P_INSTRUCTION_PROCESS_ID );
4509
4510 L_MESG_TEXT := FND_MESSAGE.GET();
4511
4512 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
4513 L_API_NAME,
4514 L_MESG_TEXT);
4515
4516 FND_MSG_PUB.COUNT_AND_GET(P_COUNT => X_MSG_COUNT,
4517 P_DATA => X_MSG_DATA);
4518
4519 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4520 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4521 'gmo.plsql.GMO_INSTRUCTIONS_PVT.GET_INSTANCE_STATUS',
4522 FALSE);
4523 END IF;
4524
4525 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4526
4527 WHEN OTHERS THEN
4528 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4529
4530 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4531 FND_MSG_PUB.ADD_EXC_MSG
4532 (G_PKG_NAME,
4533 L_API_NAME);
4534 END IF;
4535
4536 FND_MSG_PUB.COUNT_AND_GET
4537 ( P_COUNT => X_MSG_COUNT,
4538 P_DATA => X_MSG_DATA);
4539
4540 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4541 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4542 'gmo.plsql.GMO_INSTRUCTION_PVT.GET_INSTANCE_STATUS',
4543 FALSE);
4544 END IF;
4545
4546 END GET_INSTANCE_STATUS;
4547
4548 -- This Private API is used to capture the operator response
4549 -- and store it in Instance Temp Table
4550
4551 PROCEDURE CAPTURE_OPERATOR_RESPONSE
4552 (
4553 P_INSTRUCTION_ID IN NUMBER,
4554 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4555 P_OPERATOR_ACKN IN VARCHAR2,
4556 P_INSTR_COMMENTS IN VARCHAR2,
4557 P_INSTR_STATUS IN VARCHAR2,
4558 X_RETURN_STATUS OUT NOCOPY VARCHAR2
4559 )
4560 IS PRAGMA AUTONOMOUS_TRANSACTION;
4561 L_INVALID_PROCESS_ID EXCEPTION;
4562 L_CREATION_DATE DATE;
4563 L_CREATED_BY NUMBER;
4564 L_LAST_UPDATE_DATE DATE;
4565 L_LAST_UPDATED_BY NUMBER;
4566 L_LAST_UPDATE_LOGIN NUMBER;
4567
4568 L_API_NAME VARCHAR2(40);
4569 L_MESG_TEXT VARCHAR2(1000);
4570
4571 BEGIN
4572
4573 L_API_NAME := 'CAPTURE_OPERATOR_RESPONSE';
4574
4575 GMO_UTILITIES.GET_WHO_COLUMNS
4576 (
4577 X_CREATION_DATE => L_CREATION_DATE,
4578 X_CREATED_BY => L_CREATED_BY,
4579 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4580 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4581 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4582 );
4583
4584 UPDATE GMO_INSTR_INSTANCE_T
4585 SET
4586 OPERATOR_ACKN = P_OPERATOR_ACKN,
4587 COMMENTS = P_INSTR_COMMENTS,
4588 INSTR_STATUS = P_INSTR_STATUS,
4589 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
4590 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
4591 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
4592 WHERE
4593 INSTRUCTION_ID = P_INSTRUCTION_ID
4594 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4595
4596 IF(SQL%NOTFOUND ) THEN
4597 RAISE L_INVALID_PROCESS_ID;
4598 END IF;
4599
4600 -- SAVE CHANGES
4601 COMMIT;
4602
4603 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4604
4605 EXCEPTION
4606 WHEN L_INVALID_PROCESS_ID THEN
4607 ROLLBACK;
4608
4609 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_INV_PROCESSID_ERR');
4610 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
4611 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
4612 FND_MESSAGE.SET_TOKEN('PROCESS_ID', P_INSTRUCTION_PROCESS_ID );
4613
4614 L_MESG_TEXT := FND_MESSAGE.GET();
4615
4616 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4617 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
4618 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CAPTURE_OPERATOR_RESPONSE',
4619 FALSE);
4620 END IF;
4621
4622 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
4623
4624 WHEN OTHERS THEN
4625 ROLLBACK;
4626
4627 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4628 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4629 'gmo.plsql.GMO_INSTRUCTION_PVT.CAPTURE_OPERATOR_RESPONSE',
4630 FALSE);
4631 END IF;
4632
4633 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4634
4635 END CAPTURE_OPERATOR_RESPONSE;
4636
4637
4638 -- This API inserts instruction e-record details in e-records table
4639 PROCEDURE INSERT_ERECORD_DETAILS
4640 (
4641 P_INSTRUCTION_ID IN NUMBER,
4642 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4643 P_INSTRUCTION_ERECORD_ID IN NUMBER,
4644 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4645 X_MSG_COUNT OUT NOCOPY VARCHAR2,
4646 X_MSG_DATA OUT NOCOPY VARCHAR2
4647 )
4648 IS PRAGMA AUTONOMOUS_TRANSACTION;
4649
4650 L_CREATION_DATE DATE;
4651 L_CREATED_BY NUMBER;
4652 L_LAST_UPDATE_DATE DATE;
4653 L_LAST_UPDATED_BY NUMBER;
4654 L_LAST_UPDATE_LOGIN NUMBER;
4655
4656 L_PARAM_ERR EXCEPTION;
4657
4658 L_API_NAME VARCHAR2(40);
4659
4660 BEGIN
4661
4662 L_API_NAME := 'INSERT_ERECORD_DETAILS';
4663
4664 GMO_UTILITIES.GET_WHO_COLUMNS
4665 (
4666 X_CREATION_DATE => L_CREATION_DATE,
4667 X_CREATED_BY => L_CREATED_BY,
4668 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4669 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4670 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4671 );
4672
4673 IF (P_INSTRUCTION_ID IS NULL OR P_INSTRUCTION_PROCESS_ID IS NULL
4674 OR P_INSTRUCTION_ERECORD_ID IS NULL ) THEN
4675 RAISE L_PARAM_ERR;
4676 END IF;
4677
4678 INSERT INTO GMO_INSTR_EREC_INSTANCE_T
4679 (
4680 INSTRUCTION_ID,
4681 INSTRUCTION_PROCESS_ID,
4682 INSTR_EREC_SEQ,
4683 INSTR_EREC_ID,
4684 TASK_EREC_ID,
4685 CREATION_DATE,
4686 CREATED_BY,
4687 LAST_UPDATE_DATE,
4688 LAST_UPDATED_BY,
4689 LAST_UPDATE_LOGIN
4690 )
4691 VALUES
4692 (
4693 P_INSTRUCTION_ID,
4694 P_INSTRUCTION_PROCESS_ID,
4695 GMO_INSTR_EREC_INSTANCE_S.NEXTVAL,
4696 P_INSTRUCTION_ERECORD_ID,
4697 NULL,
4698 L_CREATION_DATE,
4699 L_CREATED_BY,
4700 L_LAST_UPDATE_DATE,
4701 L_LAST_UPDATED_BY,
4702 L_LAST_UPDATE_LOGIN
4703 );
4704
4705 -- SAVE CHANGES
4706 COMMIT;
4707
4708 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4709
4710 EXCEPTION
4711 WHEN OTHERS THEN
4712 ROLLBACK;
4713 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4714
4715
4716 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4717 FND_MSG_PUB.ADD_EXC_MSG
4718 (G_PKG_NAME,
4719 L_API_NAME);
4720 END IF;
4721
4722 FND_MSG_PUB.COUNT_AND_GET
4723 ( P_COUNT => X_MSG_COUNT,
4724 P_DATA => X_MSG_DATA);
4725
4726 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4727 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4728 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_CONTEXT',
4729 FALSE);
4730 END IF;
4731
4732 END INSERT_ERECORD_DETAILS;
4733
4734 -- This API creates temporary instance of instructions from
4735 -- permanent instance tables It is called from CREATE_INSTANCE_CONTEXT
4736 PROCEDURE CREATE_TEMPORARY_INSTANCES
4737 (
4738 P_INSTRUCTION_PROCESS_ID IN NUMBER,
4739 P_ENTITY_NAME IN VARCHAR2,
4740 P_ENTITY_KEY IN VARCHAR2,
4741 P_INSTRUCTION_TYPE IN VARCHAR2,
4742 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4743 X_MSG_COUNT OUT NOCOPY NUMBER,
4744 X_MSG_DATA OUT NOCOPY VARCHAR2
4745 ) IS
4746 L_CREATION_DATE DATE;
4747 L_CREATED_BY NUMBER;
4748 L_LAST_UPDATE_DATE DATE;
4749 L_LAST_UPDATED_BY NUMBER;
4750 L_LAST_UPDATE_LOGIN NUMBER;
4751
4752 L_INSTRUCTION_SET_ID NUMBER;
4753 L_INSTRUCTION_ID NUMBER;
4754
4755 CURSOR L_INSTR_SET_CSR IS
4756 SELECT INSTRUCTION_SET_ID, ACKN_STATUS
4757 FROM GMO_INSTR_SET_INSTANCE_B
4758 WHERE ENTITY_NAME = P_ENTITY_NAME
4759 AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
4760 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
4761 AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
4762
4763 CURSOR L_INSTR_CSR IS
4764 SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID,
4765 COMMENTS, OPERATOR_ACKN, INSTR_STATUS,
4766 TASK_ACKN_STATUS, TASK_ID
4767 FROM GMO_INSTR_INSTANCE_VL
4768 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4769
4770 CURSOR L_INSTR_TASK_CSR IS
4771 SELECT INSTRUCTION_ID, INSTR_TASK_SEQ, TASK_EREC_ID,
4772 TASK_IDENTIFIER, TASK_VALUE, MANUAL_ENTRY
4773 FROM GMO_INSTR_TASK_INSTANCE
4774 WHERE INSTRUCTION_ID = L_INSTRUCTION_ID;
4775
4776 CURSOR L_INSTR_EREC_CSR IS
4777 SELECT INSTRUCTION_ID, INSTR_EREC_SEQ, INSTR_EREC_ID,
4778 TASK_EREC_ID
4779 FROM GMO_INSTR_EREC_INSTANCE
4780 WHERE INSTRUCTION_ID = L_INSTRUCTION_ID;
4781
4782 L_INSTR_SET_REC L_INSTR_SET_CSR%ROWTYPE;
4783 L_INSTR_REC L_INSTR_CSR%ROWTYPE;
4784
4785 L_INSTR_TASK_REC L_INSTR_TASK_CSR%ROWTYPE;
4786 L_INSTR_EREC_REC L_INSTR_EREC_CSR%ROWTYPE;
4787
4788 L_API_NAME VARCHAR2(40);
4789
4790 BEGIN
4791
4792 L_API_NAME := 'CREATE_TEMPORARY_INSTANCES';
4793
4794 GMO_UTILITIES.GET_WHO_COLUMNS
4795 (
4796 X_CREATION_DATE => L_CREATION_DATE,
4797 X_CREATED_BY => L_CREATED_BY,
4798 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
4799 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
4800 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
4801 );
4802
4803 OPEN L_INSTR_SET_CSR;
4804 LOOP
4805 FETCH L_INSTR_SET_CSR INTO L_INSTR_SET_REC;
4806 EXIT WHEN L_INSTR_SET_CSR%NOTFOUND;
4807
4808 INSERT INTO GMO_INSTR_SET_INSTANCE_T
4809 (
4810 INSTRUCTION_PROCESS_ID,
4811 INSTRUCTION_SET_ID,
4812 ACKN_STATUS,
4813 CREATION_DATE,
4814 CREATED_BY,
4815 LAST_UPDATE_DATE,
4816 LAST_UPDATED_BY,
4817 LAST_UPDATE_LOGIN
4818 )
4819 VALUES
4820 (
4821 P_INSTRUCTION_PROCESS_ID,
4822 L_INSTR_SET_REC.INSTRUCTION_SET_ID,
4823 L_INSTR_SET_REC.ACKN_STATUS,
4824 L_CREATION_DATE, -- FIGURE THIS OUT
4825 L_CREATED_BY,
4826 L_LAST_UPDATE_DATE,
4827 L_LAST_UPDATED_BY,
4828 L_LAST_UPDATE_LOGIN
4829 );
4830
4831 L_INSTRUCTION_SET_ID := L_INSTR_SET_REC.INSTRUCTION_SET_ID;
4832
4833 OPEN L_INSTR_CSR;
4834 LOOP
4835 FETCH L_INSTR_CSR INTO L_INSTR_REC;
4836 EXIT WHEN L_INSTR_CSR%NOTFOUND;
4837
4838 L_INSTRUCTION_ID := L_INSTR_REC.INSTRUCTION_ID;
4839
4840 INSERT INTO GMO_INSTR_INSTANCE_T
4841 (
4842 INSTRUCTION_PROCESS_ID,
4843 INSTRUCTION_ID,
4844 COMMENTS,
4845 OPERATOR_ACKN,
4846 INSTR_STATUS,
4847 TASK_ACKN_STATUS,
4848 TASK_ID,
4849 DISABLE_TASK,
4850 CREATION_DATE,
4851 CREATED_BY,
4852 LAST_UPDATE_DATE,
4853 LAST_UPDATED_BY,
4854 LAST_UPDATE_LOGIN
4855 )
4856 VALUES
4857 (
4858 P_INSTRUCTION_PROCESS_ID,
4859 L_INSTR_REC.INSTRUCTION_ID,
4860 L_INSTR_REC.COMMENTS,
4861 L_INSTR_REC.OPERATOR_ACKN,
4862 L_INSTR_REC.INSTR_STATUS,
4863 DECODE(L_INSTR_REC.TASK_ID,
4864 NULL,NULL,
4865 GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS),
4866 L_INSTR_REC.TASK_ID,
4867 DECODE(L_INSTR_REC.TASK_ID,NULL,NULL,GMO_CONSTANTS_GRP.NO),
4868 L_CREATION_DATE, -- FIGURE THIS OUT
4869 L_CREATED_BY,
4870 L_LAST_UPDATE_DATE,
4871 L_LAST_UPDATED_BY,
4872 L_LAST_UPDATE_LOGIN
4873 );
4874
4875 OPEN L_INSTR_TASK_CSR;
4876 LOOP
4877 FETCH L_INSTR_TASK_CSR INTO L_INSTR_TASK_REC;
4878 EXIT WHEN L_INSTR_TASK_CSR%NOTFOUND;
4879
4880 INSERT INTO GMO_INSTR_TASK_INSTANCE_T
4881 (
4882 INSTRUCTION_ID,
4883 INSTRUCTION_PROCESS_ID,
4884 INSTR_TASK_SEQ,
4885 TASK_EREC_ID,
4886 TASK_IDENTIFIER,
4887 TASK_VALUE,
4888 MANUAL_ENTRY,
4889 CREATION_DATE,
4890 CREATED_BY,
4891 LAST_UPDATE_DATE,
4892 LAST_UPDATED_BY,
4893 LAST_UPDATE_LOGIN
4894 )
4895 VALUES
4896 (
4897 L_INSTR_TASK_REC.INSTRUCTION_ID,
4898 P_INSTRUCTION_PROCESS_ID,
4899 L_INSTR_TASK_REC.INSTR_TASK_SEQ,
4900 L_INSTR_TASK_REC.TASK_EREC_ID,
4901 L_INSTR_TASK_REC.TASK_IDENTIFIER,
4902 L_INSTR_TASK_REC.TASK_VALUE,
4903 L_INSTR_TASK_REC.MANUAL_ENTRY,
4904 L_CREATION_DATE, -- FIGURE THIS OUT
4905 L_CREATED_BY,
4906 L_LAST_UPDATE_DATE,
4907 L_LAST_UPDATED_BY,
4908 L_LAST_UPDATE_LOGIN
4909 );
4910
4911 END LOOP;
4912 CLOSE L_INSTR_TASK_CSR;
4913
4914 OPEN L_INSTR_EREC_CSR;
4915 LOOP
4916 FETCH L_INSTR_EREC_CSR INTO L_INSTR_EREC_REC;
4917 EXIT WHEN L_INSTR_EREC_CSR%NOTFOUND;
4918
4919 INSERT INTO GMO_INSTR_EREC_INSTANCE_T
4920 (
4921 INSTRUCTION_ID,
4922 INSTRUCTION_PROCESS_ID,
4923 INSTR_EREC_SEQ,
4924 INSTR_EREC_ID,
4925 TASK_EREC_ID,
4926 CREATION_DATE,
4927 CREATED_BY,
4928 LAST_UPDATE_DATE,
4929 LAST_UPDATED_BY,
4930 LAST_UPDATE_LOGIN
4931
4932 )
4933 VALUES
4934 (
4935 L_INSTRUCTION_ID,
4936 P_INSTRUCTION_PROCESS_ID,
4937 L_INSTR_EREC_REC.INSTR_EREC_SEQ,
4938 L_INSTR_EREC_REC.INSTR_EREC_ID,
4939 L_INSTR_EREC_REC.TASK_EREC_ID,
4940 L_CREATION_DATE, -- FIGURE THIS OUT
4941 L_CREATED_BY,
4942 L_LAST_UPDATE_DATE,
4943 L_LAST_UPDATED_BY,
4944 L_LAST_UPDATE_LOGIN
4945 );
4946
4947 END LOOP;
4948 CLOSE L_INSTR_EREC_CSR;
4949
4950 END LOOP;
4951 CLOSE L_INSTR_CSR;
4952
4953 END LOOP;
4954 CLOSE L_INSTR_SET_CSR;
4955
4956 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4957
4958 EXCEPTION
4959 WHEN OTHERS THEN
4960 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
4961
4962 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4963 FND_MSG_PUB.ADD_EXC_MSG
4964 (G_PKG_NAME,
4965 L_API_NAME);
4966 END IF;
4967
4968 FND_MSG_PUB.COUNT_AND_GET
4969 ( P_COUNT => X_MSG_COUNT,
4970 P_DATA => X_MSG_DATA);
4971
4972 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4973 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
4974 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_TEMPORARY_INSTANCES',
4975 FALSE);
4976 END IF;
4977
4978 -- propogate this exception
4979 RAISE;
4980
4981 END CREATE_TEMPORARY_INSTANCES;
4982
4983
4984 -- This API creates instance context when called by entity application
4985 -- and returns an Instruction Process Id
4986 PROCEDURE CREATE_INSTANCE_CONTEXT
4987 (
4988 P_ENTITY_NAME IN VARCHAR2,
4989 P_ENTITY_KEY IN VARCHAR2,
4990 P_INSTRUCTION_TYPE IN VARCHAR2,
4991 P_CONTEXT_PARAM_NAME IN FND_TABLE_OF_VARCHAR2_255,
4992 P_CONTEXT_PARAM_VALUE IN FND_TABLE_OF_VARCHAR2_255,
4993 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
4994 X_INSTRUCTION_SET_ID OUT NOCOPY NUMBER,
4995 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4996 X_MSG_COUNT OUT NOCOPY NUMBER,
4997 X_MSG_DATA OUT NOCOPY VARCHAR2
4998
4999 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
5000
5001 L_INSTRUCTION_PROCESS_ID NUMBER;
5002 L_CREATION_DATE DATE;
5003 L_CREATED_BY NUMBER;
5004 L_LAST_UPDATE_DATE DATE;
5005 L_LAST_UPDATED_BY NUMBER;
5006 L_LAST_UPDATE_LOGIN NUMBER;
5007
5008 L_INSTRUCTION_SET_ID NUMBER;
5009 L_INSTR_SET_STATUS VARCHAR2(40);
5010
5011 L_API_NAME VARCHAR2(40);
5012 L_MESG_TEXT VARCHAR2(1000);
5013
5014 BEGIN
5015
5016 L_API_NAME := 'CREATE_INSTANCE_CONTEXT';
5017
5018 SELECT GMO_INSTR_PROCESS_ID_S.NEXTVAL INTO L_INSTRUCTION_PROCESS_ID
5019 FROM DUAL;
5020
5021 -- Begin Validation Block
5022 BEGIN
5023
5024 -- GET THE INSTRUCTION SET ID FROM INSTANCE INSTRUCTION SET TABLE
5025 SELECT MAX(INSTRUCTION_SET_ID) INTO L_INSTRUCTION_SET_ID
5026 FROM GMO_INSTR_SET_INSTANCE_VL
5027 WHERE ENTITY_NAME = P_ENTITY_NAME
5028 AND ENTITY_KEY = P_ENTITY_KEY
5029 AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
5030
5031 -- CHECK IF THE INSTRUCTION SET IS NULLIFIED
5032 SELECT INSTR_SET_STATUS INTO L_INSTR_SET_STATUS
5033 FROM GMO_INSTR_SET_INSTANCE_VL
5034 WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
5035
5036 -- CHECK THE INSTRUCTION SET STATUS, IT MUST BE ACTIVE
5037 -- FOR THE PROCESS TO PROCEED FURTHER
5038 IF( RTRIM(L_INSTR_SET_STATUS) <> GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE ) THEN
5039
5040 ROLLBACK;
5041 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5042
5043 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SET_INACTIVE');
5044 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
5045 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
5046
5047 L_MESG_TEXT := FND_MESSAGE.GET();
5048
5049 FND_MSG_PUB.ADD_EXC_MSG
5050 ( G_PKG_NAME,
5051 L_API_NAME,
5052 L_MESG_TEXT
5053 );
5054
5055 FND_MSG_PUB.COUNT_AND_GET
5056 (
5057 P_COUNT => X_MSG_COUNT,
5058 P_DATA => X_MSG_DATA
5059 );
5060
5061 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5062 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
5063 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_INSTANCE_CONTEXT',
5064 FALSE);
5065 END IF;
5066
5067 RETURN;
5068
5069 END IF;
5070
5071 EXCEPTION
5072 WHEN NO_DATA_FOUND THEN
5073 ROLLBACK;
5074 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5075
5076 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5077 FND_MSG_PUB.ADD_EXC_MSG
5078 (G_PKG_NAME,
5079 L_API_NAME);
5080 END IF;
5081
5082 FND_MSG_PUB.COUNT_AND_GET
5083 ( P_COUNT => X_MSG_COUNT,
5084 P_DATA => X_MSG_DATA);
5085
5086 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5087 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5088 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_DEFN_CONTEXT',
5089 FALSE);
5090 END IF;
5091
5092 RETURN;
5093 END; -- Validation Block
5094
5095 GMO_UTILITIES.GET_WHO_COLUMNS
5096 (
5097 X_CREATION_DATE => L_CREATION_DATE,
5098 X_CREATED_BY => L_CREATED_BY,
5099 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
5100 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
5101 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
5102 );
5103
5104 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5105 (
5106 INSTRUCTION_PROCESS_ID,
5107 ATTRIBUTE_SEQ,
5108 ATTRIBUTE_NAME,
5109 ENTITY_NAME,
5110 ENTITY_KEY,
5111 INSTRUCTION_TYPE,
5112 ATTRIBUTE_TYPE,
5113 CREATION_DATE,
5114 CREATED_BY,
5115 LAST_UPDATE_DATE,
5116 LAST_UPDATED_BY,
5117 LAST_UPDATE_LOGIN
5118 )
5119 VALUES
5120 (
5121 L_INSTRUCTION_PROCESS_ID,
5122 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5123 GMO_CONSTANTS_GRP.G_PARAM_ENTITY,
5124 P_ENTITY_NAME,
5125 P_ENTITY_KEY,
5126 P_INSTRUCTION_TYPE,
5127 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5128 L_CREATION_DATE,
5129 L_CREATED_BY,
5130 L_LAST_UPDATE_DATE,
5131 L_LAST_UPDATED_BY,
5132 L_LAST_UPDATE_LOGIN
5133 );
5134
5135 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5136 (
5137 INSTRUCTION_PROCESS_ID,
5138 ATTRIBUTE_SEQ,
5139 ATTRIBUTE_NAME,
5140 ATTRIBUTE_VALUE,
5141 ATTRIBUTE_TYPE,
5142 CREATION_DATE,
5143 CREATED_BY,
5144 LAST_UPDATE_DATE,
5145 LAST_UPDATED_BY,
5146 LAST_UPDATE_LOGIN
5147 )
5148 VALUES
5149 (
5150 L_INSTRUCTION_PROCESS_ID,
5151 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5152 GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
5153 GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
5154 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5155 L_CREATION_DATE,
5156 L_CREATED_BY,
5157 L_LAST_UPDATE_DATE,
5158 L_LAST_UPDATED_BY,
5159 L_LAST_UPDATE_LOGIN
5160 );
5161
5162 IF ( (P_CONTEXT_PARAM_NAME IS NOT NULL)
5163 AND (P_CONTEXT_PARAM_VALUE IS NOT NULL )) THEN
5164
5165 FOR I IN 1..P_CONTEXT_PARAM_NAME.COUNT LOOP
5166 IF(P_CONTEXT_PARAM_NAME(I) IS NOT NULL) THEN
5167
5168 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5169 (
5170 INSTRUCTION_PROCESS_ID,
5171 ATTRIBUTE_SEQ,
5172 ATTRIBUTE_NAME,
5173 ATTRIBUTE_VALUE,
5174 ATTRIBUTE_TYPE,
5175 CREATION_DATE,
5176 CREATED_BY,
5177 LAST_UPDATE_DATE,
5178 LAST_UPDATED_BY,
5179 LAST_UPDATE_LOGIN
5180 )
5181 VALUES
5182 (
5183 L_INSTRUCTION_PROCESS_ID,
5184 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5185 P_CONTEXT_PARAM_NAME(i),
5186 P_CONTEXT_PARAM_VALUE(i),
5187 GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5188 L_CREATION_DATE,
5189 L_CREATED_BY,
5190 L_LAST_UPDATE_DATE,
5191 L_LAST_UPDATED_BY,
5192 L_LAST_UPDATE_LOGIN
5193 );
5194
5195 END IF;
5196
5197 END LOOP;
5198 END IF;
5199
5200 -- Create temporary table entries from perm, by calling this procedure
5201 CREATE_TEMPORARY_INSTANCES
5202 (
5203 P_INSTRUCTION_PROCESS_ID => L_INSTRUCTION_PROCESS_ID,
5204 P_ENTITY_NAME => P_ENTITY_NAME,
5205 P_ENTITY_KEY => P_ENTITY_KEY,
5206 P_INSTRUCTION_TYPE => P_INSTRUCTION_TYPE,
5207 X_RETURN_STATUS => X_RETURN_STATUS,
5208 X_MSG_COUNT => X_MSG_COUNT,
5209 X_MSG_DATA => X_MSG_DATA
5210 );
5211
5212 --Commit changes
5213 COMMIT;
5214
5215 X_INSTRUCTION_SET_ID := L_INSTRUCTION_SET_ID;
5216 X_INSTRUCTION_PROCESS_ID := L_INSTRUCTION_PROCESS_ID;
5217
5218 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5219 X_MSG_COUNT := 0;
5220 X_MSG_DATA := FND_API.G_RET_STS_SUCCESS;
5221
5222 EXCEPTION
5223 WHEN OTHERS THEN
5224 ROLLBACK;
5225 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
5226
5227 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5228 FND_MSG_PUB.ADD_EXC_MSG
5229 (G_PKG_NAME,
5230 L_API_NAME);
5231 END IF;
5232
5233 FND_MSG_PUB.COUNT_AND_GET
5234 ( P_COUNT => X_MSG_COUNT,
5235 P_DATA => X_MSG_DATA);
5236
5237 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5238 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5239 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_INSTANCE_CONTEXT',
5240 FALSE);
5241 END IF;
5242
5243 END CREATE_INSTANCE_CONTEXT;
5244
5245 -- This API creates instance context when called by entity application
5246 -- and returns an Instruction Process Id. This flavor is created for
5247 -- calling applications which only have Instruction Set Id, and not
5248 -- Entity Name, Entity Key and Instruction Type.
5249
5250 PROCEDURE CREATE_INSTANCE_CONTEXT
5251 (
5252 P_INSTRUCTION_SET_ID IN NUMBER,
5253 P_CONTEXT_PARAM_NAME IN FND_TABLE_OF_VARCHAR2_255,
5254 P_CONTEXT_PARAM_VALUE IN FND_TABLE_OF_VARCHAR2_255,
5255 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
5256 X_ENTITY_NAME OUT NOCOPY VARCHAR2,
5257 X_ENTITY_KEY OUT NOCOPY VARCHAR2,
5258 X_INSTRUCTION_TYPE OUT NOCOPY VARCHAR2,
5259 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5260 X_MSG_COUNT OUT NOCOPY NUMBER,
5261 X_MSG_DATA OUT NOCOPY VARCHAR2
5262 )
5263 IS PRAGMA AUTONOMOUS_TRANSACTION;
5264
5265 L_INSTRUCTION_SET_ID NUMBER;
5266 L_INSTRUCTION_TYPE VARCHAR2(40);
5267 L_ENTITY_NAME VARCHAR2(200);
5268 L_ENTITY_KEY VARCHAR2(500);
5269
5270 L_API_NAME VARCHAR2(40);
5271 L_MESG_TEXT VARCHAR2(1000);
5272
5273 BEGIN
5274
5275 L_API_NAME := 'CREATE_INSTANCE_CONTEXT';
5276
5277 BEGIN
5278 SELECT INSTRUCTION_TYPE, ENTITY_NAME, ENTITY_KEY
5279 INTO L_INSTRUCTION_TYPE, L_ENTITY_NAME, L_ENTITY_KEY
5280 FROM GMO_INSTR_SET_INSTANCE_VL WHERE
5281 INSTRUCTION_SET_ID = P_INSTRUCTION_SET_ID;
5282 EXCEPTION
5283 WHEN NO_DATA_FOUND THEN
5284 ROLLBACK;
5285 X_INSTRUCTION_PROCESS_ID := -1;
5286 X_ENTITY_NAME := NULL;
5287 X_ENTITY_KEY := NULL;
5288 X_INSTRUCTION_TYPE := NULL;
5289
5290 FND_MESSAGE.SET_NAME('GMO', 'GMO_INSTR_SET_INACTIVE');
5291 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME', G_PKG_NAME);
5292 FND_MESSAGE.SET_TOKEN('API_NAME', L_API_NAME );
5293
5294 L_MESG_TEXT := FND_MESSAGE.GET();
5295
5296 FND_MSG_PUB.ADD_EXC_MSG
5297 ( G_PKG_NAME,
5298 L_API_NAME,
5299 L_MESG_TEXT
5300 );
5301
5302 FND_MSG_PUB.COUNT_AND_GET
5303 (
5304 P_COUNT => X_MSG_COUNT,
5305 P_DATA => X_MSG_DATA
5306 );
5307
5308 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5309 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
5310 'gmo.plsql.GMO_INSTRUCTIONS_PVT.CREATE_INSTANCE_CONTEXT',
5311 FALSE);
5312 END IF;
5313
5314 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5315
5316 RETURN;
5317 END;
5318
5319 CREATE_INSTANCE_CONTEXT (
5320 P_ENTITY_NAME => L_ENTITY_NAME,
5321 P_ENTITY_KEY => L_ENTITY_KEY,
5322 P_INSTRUCTION_TYPE => L_INSTRUCTION_TYPE,
5323 P_CONTEXT_PARAM_NAME => P_CONTEXT_PARAM_NAME,
5324 P_CONTEXT_PARAM_VALUE => P_CONTEXT_PARAM_VALUE,
5325 X_INSTRUCTION_PROCESS_ID => X_INSTRUCTION_PROCESS_ID,
5326 X_INSTRUCTION_SET_ID => L_INSTRUCTION_SET_ID,
5327 X_RETURN_STATUS => X_RETURN_STATUS,
5328 X_MSG_COUNT=> X_MSG_COUNT,
5329 X_MSG_DATA => X_MSG_DATA
5330 );
5331
5332 COMMIT;
5333
5334 X_ENTITY_NAME := L_ENTITY_NAME;
5335 X_ENTITY_KEY := L_ENTITY_KEY;
5336 X_INSTRUCTION_TYPE := L_INSTRUCTION_TYPE;
5337
5338 EXCEPTION
5339 WHEN OTHERS THEN
5340 ROLLBACK;
5341
5342 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5343 FND_MSG_PUB.ADD_EXC_MSG
5344 (G_PKG_NAME,
5345 L_API_NAME);
5346 END IF;
5347
5348 FND_MSG_PUB.COUNT_AND_GET
5349 ( P_COUNT => X_MSG_COUNT,
5350 P_DATA => X_MSG_DATA);
5351
5352 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5353 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5354 'gmo.plsql.GMO_INSTRUCTION_PVT.CREATE_INSTANCE_CONTEXT',
5355 FALSE);
5356 END IF;
5357
5358 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
5359
5360 END CREATE_INSTANCE_CONTEXT;
5361
5362
5363 --This procedure updates the definition and
5364 --process status attribute values to "MODIFIED" and "SUCCESS"
5365 --respectively in GMO_INSTR_ATTRIBUTES_T for the specified
5366 --instruction process ID.
5367 PROCEDURE UPDATE_INSTR_ATTRIBUTES(P_INSTRUCTION_PROCESS_ID IN VARCHAR2,
5368 P_UPDATE_DEFN_STATUS IN VARCHAR2)
5369 IS
5370 PRAGMA AUTONOMOUS_TRANSACTION;
5371 L_INSTRUCTION_PROCESS_ID NUMBER;
5372
5373 L_API_NAME VARCHAR2(40);
5374
5375 BEGIN
5376
5377 L_API_NAME := 'UPDATE_INSTR_ATTRIBUTES';
5378
5379 --Convert the instruction process ID to number.
5380 L_INSTRUCTION_PROCESS_ID := TO_NUMBER(P_INSTRUCTION_PROCESS_ID,'999999999999.999999');
5381
5382 --Update the definition status if required based on the flag.
5383 IF(P_UPDATE_DEFN_STATUS = FND_API.G_TRUE) THEN
5384 UPDATE
5385 GMO_INSTR_ATTRIBUTES_T
5386 SET
5387 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
5388 WHERE
5389 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5390 AND
5391 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
5392 END IF;
5393
5394 --Update the PROCESS_STATUS to success.
5395 UPDATE
5396 GMO_INSTR_ATTRIBUTES_T
5397 SET
5398 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
5399 WHERE
5400 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5401 AND
5402 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5403
5404 COMMIT;
5405
5406 EXCEPTION
5407 WHEN OTHERS THEN
5408 ROLLBACK;
5409
5410 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5411 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5412 FND_MESSAGE.SET_TOKEN('PKG_NAME',G_PKG_NAME);
5413 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',L_API_NAME);
5414
5415 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5416 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5417 'gmo.plsql.GMO_INSTRUCTION_PVT.UPDATE_INSTR_ATTRIBUTES',
5418 FALSE
5419 );
5420 end if;
5421
5422 APP_EXCEPTION.RAISE_EXCEPTION;
5423
5424 END UPDATE_INSTR_ATTRIBUTES;
5425
5426 --This procedure sets the definition and
5427 --process status attribute values to "MODIFIED" and "SUCCESS"
5428 --respectively in GMO_INSTR_ATTRIBUTES_T for the specified
5429 --instruction process ID.
5430 PROCEDURE SET_INSTR_STATUS_ATTRIBUTES(P_INSTRUCTION_PROCESS_ID IN VARCHAR2,
5431 P_UPDATE_DEFN_STATUS IN VARCHAR2)
5432 IS
5433 PRAGMA AUTONOMOUS_TRANSACTION;
5434 L_INSTRUCTION_PROCESS_ID NUMBER;
5435
5436 L_API_NAME VARCHAR2(40);
5437
5438 BEGIN
5439
5440 L_API_NAME := 'SET_INSTR_STATUS_ATTRIBUTES';
5441
5442 --Convert the instruction process ID to number.
5443 L_INSTRUCTION_PROCESS_ID := TO_NUMBER(P_INSTRUCTION_PROCESS_ID,'999999999999.999999');
5444
5445 --Update the definition status if required based on the flag.
5446 IF(P_UPDATE_DEFN_STATUS = FND_API.G_TRUE) THEN
5447 UPDATE
5448 GMO_INSTR_ATTRIBUTES_T
5449 SET
5450 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
5451 WHERE
5452 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5453 AND
5454 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
5455 END IF;
5456
5457 --Update the PROCESS_STATUS to success.
5458 UPDATE
5459 GMO_INSTR_ATTRIBUTES_T
5460 SET
5461 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
5462 WHERE
5463 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5464 AND
5465 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5466
5467 COMMIT;
5468
5469 EXCEPTION
5470 WHEN OTHERS THEN
5471 ROLLBACK;
5472
5473 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5474 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5475 FND_MESSAGE.SET_TOKEN('PKG_NAME',G_PKG_NAME);
5476 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',L_API_NAME);
5477
5478 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5479 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5480 'gmo.plsql.GMO_INSTRUCTION_PVT.SET_INSTR_STATUS_ATTRIBUTES',
5481 FALSE
5482 );
5483 end if;
5484
5485 APP_EXCEPTION.RAISE_EXCEPTION;
5486
5487 END SET_INSTR_STATUS_ATTRIBUTES;
5488
5489
5490
5491 --This procedure deletes the instruction set details in
5492 -- GMO_INSTR_SET_DEFN_T for the specified
5493 --process ID. It also updates the process status in
5494 --GMO_INSTR_ATTRUBUTES_T to "CANCEL".
5495
5496 PROCEDURE DELETE_INSTR_SET_DETAILS
5497 (
5498 P_INSTRUCTION_PROCESS_ID IN VARCHAR2
5499 )
5500 IS
5501
5502 PRAGMA AUTONOMOUS_TRANSACTION;
5503
5504 L_INSTRUCTION_PROCESS_ID NUMBER;
5505
5506 BEGIN
5507
5508 L_INSTRUCTION_PROCESS_ID := TO_NUMBER(P_INSTRUCTION_PROCESS_ID,'999999999999.999999');
5509
5510 --Update the PROCESS_STATUS to cancel.
5511 UPDATE
5512 GMO_INSTR_ATTRIBUTES_T
5513 SET
5514 ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_CANCEL
5515 WHERE
5516 INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5517 AND
5518 ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5519
5520 COMMIT;
5521
5522 EXCEPTION
5523 WHEN OTHERS THEN
5524
5525 ROLLBACK;
5526 --Diagnostics Start
5527 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5528 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5529 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_INSTRUCTION_PVT');
5530 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','DELETE_INSTR_SET_DETAILS');
5531
5532 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5533 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5534 'gmo.plsql.GMO_INSTRUCTION_PVT.DELETE_INSTR_SET_DETAILS',
5535 FALSE
5536 );
5537 end if;
5538
5539 APP_EXCEPTION.RAISE_EXCEPTION;
5540
5541 END DELETE_INSTR_SET_DETAILS;
5542
5543 -- This function gets the value of specified process variable
5544 FUNCTION GET_PROCESS_VARIABLE
5545 (
5546 P_INSTRUCTION_PROCESS_ID IN NUMBER,
5547 P_ATTRIBUTE_NAME IN VARCHAR2 ,
5548 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5549 )
5550 RETURN VARCHAR2
5551 IS
5552 L_ATTRIBUTE_VALUE VARCHAR2(1000);
5553 BEGIN
5554 SELECT ATTRIBUTE_VALUE INTO L_ATTRIBUTE_VALUE
5555 FROM GMO_INSTR_ATTRIBUTES_T
5556 WHERE ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
5557 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
5558 AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
5559
5560 RETURN L_ATTRIBUTE_VALUE;
5561
5562 EXCEPTION
5563 WHEN NO_DATA_FOUND THEN
5564 L_ATTRIBUTE_VALUE := NULL;
5565 RETURN L_ATTRIBUTE_VALUE;
5566
5567 END GET_PROCESS_VARIABLE;
5568
5569
5570 -- This function inserts a process variable in temporary
5571 -- session table gmo_instr_attributes_t
5572
5573 FUNCTION INSERT_PROCESS_VARIABLE
5574 (
5575 P_INSTRUCTION_PROCESS_ID IN NUMBER ,
5576 P_ATTRIBUTE_NAME IN VARCHAR2 ,
5577 P_ATTRIBUTE_VALUE IN VARCHAR2,
5578 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5579 )
5580 RETURN VARCHAR2
5581 IS
5582 PRAGMA AUTONOMOUS_TRANSACTION;
5583
5584 L_CREATION_DATE DATE;
5585 L_CREATED_BY NUMBER;
5586 L_LAST_UPDATE_DATE DATE;
5587 L_LAST_UPDATED_BY NUMBER;
5588 L_LAST_UPDATE_LOGIN NUMBER;
5589
5590 L_COUNT NUMBER;
5591
5592 BEGIN
5593
5594 GMO_UTILITIES.GET_WHO_COLUMNS
5595 (
5596 X_CREATION_DATE => L_CREATION_DATE,
5597 X_CREATED_BY => L_CREATED_BY,
5598 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
5599 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
5600 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
5601 );
5602
5603 SELECT COUNT(*) INTO L_COUNT FROM GMO_INSTR_ATTRIBUTES_T
5604 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
5605 AND ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
5606 AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
5607
5608 IF (P_ATTRIBUTE_NAME IS NOT NULL AND P_INSTRUCTION_PROCESS_ID IS NOT NULL) THEN
5609 IF L_COUNT > 0 THEN
5610
5611 UPDATE GMO_INSTR_ATTRIBUTES_T
5612 SET ATTRIBUTE_VALUE = P_ATTRIBUTE_VALUE,
5613 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
5614 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
5615 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
5616 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
5617 AND ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
5618 AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
5619
5620
5621 ELSE
5622
5623 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5624 (
5625 INSTRUCTION_PROCESS_ID,
5626 ATTRIBUTE_SEQ,
5627 ATTRIBUTE_NAME,
5628 ATTRIBUTE_VALUE,
5629 ATTRIBUTE_TYPE,
5630 CREATION_DATE,
5631 CREATED_BY,
5632 LAST_UPDATE_DATE,
5633 LAST_UPDATED_BY,
5634 LAST_UPDATE_LOGIN
5635 )
5636 VALUES
5637 (
5638 P_INSTRUCTION_PROCESS_ID,
5639 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5640 P_ATTRIBUTE_NAME,
5641 P_ATTRIBUTE_VALUE,
5642 P_ATTRIBUTE_TYPE,
5643 L_CREATION_DATE,
5644 L_CREATED_BY,
5645 L_LAST_UPDATE_DATE,
5646 L_LAST_UPDATED_BY,
5647 L_LAST_UPDATE_LOGIN
5648 );
5649 END IF;
5650 ELSE
5651 RETURN GMO_CONSTANTS_GRP.NO;
5652 END IF;
5653
5654 COMMIT;
5655
5656 RETURN GMO_CONSTANTS_GRP.YES;
5657
5658 EXCEPTION
5659 WHEN OTHERS THEN
5660 ROLLBACK;
5661 RETURN GMO_CONSTANTS_GRP.NO;
5662
5663 END INSERT_PROCESS_VARIABLE;
5664
5665
5666 -- This function sets the process variable to the value
5667 -- passed in the input.
5668
5669 FUNCTION SET_PROCESS_VARIABLE
5670 ( P_INSTRUCTION_PROCESS_ID IN NUMBER ,
5671 P_ATTRIBUTE_NAME IN VARCHAR2 ,
5672 P_ATTRIBUTE_VALUE IN VARCHAR2,
5673 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5674 )
5675 RETURN VARCHAR2
5676 IS
5677 L_CREATION_DATE DATE;
5678 L_CREATED_BY NUMBER;
5679 L_LAST_UPDATE_DATE DATE;
5680 L_LAST_UPDATED_BY NUMBER;
5681 L_LAST_UPDATE_LOGIN NUMBER;
5682 L_ATTRIBUTE_VALUE VARCHAR2(1000);
5683
5684 PRAGMA AUTONOMOUS_TRANSACTION;
5685
5686 BEGIN
5687
5688 UPDATE GMO_INSTR_ATTRIBUTES_T
5689 SET ATTRIBUTE_VALUE = P_ATTRIBUTE_VALUE
5690 WHERE ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
5691 AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE
5692 AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
5693
5694 IF(SQL%ROWCOUNT = 0 ) THEN
5695
5696 GMO_UTILITIES.GET_WHO_COLUMNS
5697 (
5698 X_CREATION_DATE => L_CREATION_DATE,
5699 X_CREATED_BY => L_CREATED_BY,
5700 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
5701 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
5702 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
5703 );
5704
5705 INSERT INTO GMO_INSTR_ATTRIBUTES_T
5706 (
5707 INSTRUCTION_PROCESS_ID,
5708 ATTRIBUTE_SEQ,
5709 ATTRIBUTE_NAME,
5710 ATTRIBUTE_VALUE,
5711 ATTRIBUTE_TYPE,
5712 CREATION_DATE,
5713 CREATED_BY,
5714 LAST_UPDATE_DATE,
5715 LAST_UPDATED_BY,
5716 LAST_UPDATE_LOGIN
5717 )
5718 VALUES
5719 (
5720 P_INSTRUCTION_PROCESS_ID,
5721 GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5722 P_ATTRIBUTE_NAME,
5723 P_ATTRIBUTE_VALUE,
5724 P_ATTRIBUTE_TYPE,
5725 L_CREATION_DATE,
5726 L_CREATED_BY,
5727 L_LAST_UPDATE_DATE,
5728 L_LAST_UPDATED_BY,
5729 L_LAST_UPDATE_LOGIN
5730 );
5731 END IF;
5732
5733 COMMIT;
5734
5735 RETURN GMO_CONSTANTS_GRP.YES;
5736 EXCEPTION
5737 WHEN OTHERS THEN
5738 ROLLBACK;
5739 RETURN GMO_CONSTANTS_GRP.NO;
5740
5741 END SET_PROCESS_VARIABLE;
5742
5743 -- This API is wrapper over ERES E-record Validation API.
5744 -- Returns Y or N based on E-record Id validity
5745
5746 PROCEDURE VALIDATE_TASK_ERECORD_ID
5747 (
5748 P_TASK_ERECORD_ID IN FND_TABLE_OF_VARCHAR2_255,
5749 X_ERECORD_ID_INVALID OUT NOCOPY VARCHAR2,
5750 X_ERECORD_LIST_STR OUT NOCOPY VARCHAR2,
5751 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5752 X_MSG_COUNT OUT NOCOPY NUMBER,
5753 X_MSG_DATA OUT NOCOPY VARCHAR2
5754 )
5755 IS
5756 L_MSG_COUNT NUMBER;
5757 L_MSG_DATA VARCHAR2(4000);
5758 L_RETURN_STATUS VARCHAR2(1);
5759 L_ERECORD_ID VARCHAR2(100);
5760 L_INVALID_ERECORD_LIST VARCHAR2(4000);
5761
5762 L_MESG_TEXT VARCHAR2(1000);
5763 L_API_NAME VARCHAR2(40);
5764
5765 BEGIN
5766
5767 L_API_NAME := 'VALIDATE_TASK_ERECORD_ID';
5768
5769 L_INVALID_ERECORD_LIST := '';
5770
5771 FOR I IN 1..P_TASK_ERECORD_ID.COUNT LOOP
5772 L_ERECORD_ID := P_TASK_ERECORD_ID(I);
5773
5774 EDR_ERES_EVENT_PUB.VALIDATE_ERECORD
5775 (
5776 p_api_version => 1.0,
5777 P_init_msg_list => FND_API.G_FALSE ,
5778 x_return_status => L_RETURN_STATUS ,
5779 X_msg_count => L_MSG_COUNT ,
5780 x_msg_data => L_MSG_DATA,
5781 p_erecord_id => TO_NUMBER(L_ERECORD_ID,999999999999.999999)
5782 );
5783
5784 IF(L_RETURN_STATUS = FND_API.G_RET_STS_ERROR) THEN
5785 L_INVALID_ERECORD_LIST := L_INVALID_ERECORD_LIST || ',' || L_ERECORD_ID;
5786 END IF;
5787
5788 END LOOP;
5789
5790 IF ( LENGTH(L_INVALID_ERECORD_LIST) > 0) THEN
5791 X_ERECORD_LIST_STR := SUBSTR(L_INVALID_ERECORD_LIST,2,LENGTH(L_INVALID_ERECORD_LIST));
5792 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5793 X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.YES;
5794 ELSE
5795 X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.NO;
5796 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5797 END IF;
5798
5799 EXCEPTION
5800 WHEN OTHERS THEN
5801 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5802 X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.YES;
5803
5804 --Diagnostics Start
5805 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5806 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5807 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_INSTRUCTION_PVT');
5808 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',L_API_NAME);
5809 L_MESG_TEXT := FND_MESSAGE.GET;
5810
5811 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5812 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
5813 'gmo.plsql.GMO_INSTRUCTION_PVT.'|| L_API_NAME,
5814 FALSE
5815 );
5816 end if;
5817
5818 FND_MSG_PUB.ADD_EXC_MSG
5819 ( G_PKG_NAME,
5820 L_API_NAME,
5821 L_MESG_TEXT
5822 );
5823
5824 FND_MSG_PUB.COUNT_AND_GET
5825 (
5826 P_COUNT => X_MSG_COUNT,
5827 P_DATA => X_MSG_DATA
5828 );
5829
5830 END VALIDATE_TASK_ERECORD_ID;
5831
5832 -- This procedure is used to fetch the instruction set and related
5833 -- instruction details in XML format
5834
5835 PROCEDURE GET_INSTR_XML(P_INSTRUCTION_PROCESS_ID IN NUMBER,
5836 X_OUTPUT_XML OUT NOCOPY CLOB)
5837 IS
5838 --This variable would hold the XML details in XMLType format.
5839 L_INSTR_XML XMLTYPE;
5840
5841 BEGIN
5842 --This SQL Query would provide all the instruction details for the specified process ID in XMLType.
5843 SELECT XMLELEMENT("INSTRUCTIONS",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
5844 XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
5845 INSTR_SET.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
5846 INSTR_SET.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
5847 (SELECT
5848 LK.MEANING
5849 FROM
5850 FND_LOOKUP_VALUES_VL LK
5851 WHERE
5852 LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SET.ENTITY_NAME
5853 AND
5854 LK.LOOKUP_CODE = INSTR_SET.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
5855 (SELECT
5856 XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",
5857 XMLFOREST(INSTR.INSTR_NUMBER AS INSTRUCTION_NUMBER,
5858 INSTR.INSTRUCTION_TEXT AS INSTRUCTION_TEXT,
5859 (SELECT
5860 LK1.MEANING
5861 FROM
5862 FND_LOOKUP_VALUES_VL LK1
5863 WHERE
5864 LK1.LOOKUP_TYPE = 'GMO_INSTR_ACKN_TYPES'
5865 AND
5866 LK1.LOOKUP_CODE = INSTR.INSTR_ACKN_TYPE) AS INSTRUCTION_ACKN_TYPE,
5867 (SELECT
5868 TK.DISPLAY_NAME
5869 FROM
5870 GMO_INSTR_TASK_DEFN_VL TK
5871 WHERE
5872 TK.TASK_ID = INSTR.TASK_ID) AS TASK_NAME,
5873 INSTR.TASK_ATTRIBUTE AS TASK_ATTRIBUTE,
5874 INSTR.TASK_LABEL AS TASK_LABEL,
5875 (SELECT
5876 DECODE((SELECT COUNT(*) FROM GMO_INSTR_APPR_DEFN_T APPR
5877 WHERE
5878 APPR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID
5879 AND APPR.INSTRUCTION_ID = INSTR.INSTRUCTION_ID),0,
5880 FND_MESSAGE.GET_STRING('GMO','GMO_INSTR_SIG_NOT_REQUIRED'),
5881 FND_MESSAGE.GET_STRING('GMO','GMO_INSTR_SIG_REQUIRED'))
5882 FROM DUAL)
5883 AS SIGNATURE_REQUIRED
5884 )
5885 )
5886 )
5887 FROM GMO_INSTR_DEFN_T INSTR
5888 WHERE INSTR.INSTRUCTION_SET_ID = INSTR_SET.INSTRUCTION_SET_ID
5889 AND INSTR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID) AS "RELATED_INSTRUCTIONS")
5890 )
5891 )
5892 )
5893 INTO L_INSTR_XML
5894 FROM GMO_INSTR_SET_DEFN_T INSTR_SET
5895 WHERE INSTR_SET.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
5896
5897 --Convert XMLType to CLOB.
5898 X_OUTPUT_XML := L_INSTR_XML.GETCLOBVAL();
5899
5900 END GET_INSTR_XML;
5901
5902 FUNCTION SET_PROCESS_ATTRIBUTES
5903 (
5904 P_INSTRUCTION_PROCESS_ID IN NUMBER,
5905 P_ATTRIBUTE_NAME IN FND_TABLE_OF_VARCHAR2_255,
5906 P_ATTRIBUTE_VALUE IN FND_TABLE_OF_VARCHAR2_255,
5907 P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5908 )
5909 RETURN VARCHAR2
5910 IS
5911 PRAGMA AUTONOMOUS_TRANSACTION;
5912
5913 L_PARAM_NAME VARCHAR2(200);
5914 L_PARAM_VALUE VARCHAR2(500);
5915 L_RETURN_STATUS VARCHAR2(1);
5916
5917 BEGIN
5918
5919 FOR I IN 1..P_ATTRIBUTE_NAME.COUNT LOOP
5920 L_PARAM_NAME := P_ATTRIBUTE_NAME(I);
5921
5922 IF( L_PARAM_NAME IS NOT NULL) THEN
5923 -- set process variable will update the attribute if it exists,
5924 -- else, inserts it
5925 L_RETURN_STATUS := SET_PROCESS_VARIABLE(
5926 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
5927 P_ATTRIBUTE_NAME => L_PARAM_NAME,
5928 P_ATTRIBUTE_VALUE => P_ATTRIBUTE_VALUE(I),
5929 P_ATTRIBUTE_TYPE => P_ATTRIBUTE_TYPE);
5930 END IF;
5931
5932 --Check the return status for each process attribute
5933 IF (L_RETURN_STATUS = GMO_CONSTANTS_GRP.NO) THEN
5934 ROLLBACK;
5935 APP_EXCEPTION.RAISE_EXCEPTION;
5936 END IF;
5937
5938 END LOOP;
5939
5940 -- SAVE CHANGES
5941 COMMIT;
5942
5943 RETURN GMO_CONSTANTS_GRP.YES;
5944
5945 EXCEPTION
5946 WHEN OTHERS THEN
5947 ROLLBACK;
5948 APP_EXCEPTION.RAISE_EXCEPTION;
5949
5950 END SET_PROCESS_ATTRIBUTES;
5951
5952 -- This API returns the Task parameter from the GMO Attributes Temp table
5953 FUNCTION GET_TASK_PARAMETER
5954 (
5955 P_INSTRUCTION_PROCESS_ID IN NUMBER,
5956 P_ATTRIBUTE_NAME IN VARCHAR2
5957 )
5958 RETURN VARCHAR2
5959 IS PRAGMA AUTONOMOUS_TRANSACTION;
5960 L_PARAM_VALUE VARCHAR2(500);
5961 BEGIN
5962
5963 L_PARAM_VALUE := GET_PROCESS_VARIABLE(P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
5964 P_ATTRIBUTE_NAME => P_ATTRIBUTE_NAME,
5965 P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_TASK );
5966 RETURN L_PARAM_VALUE;
5967
5968 EXCEPTION
5969 WHEN OTHERS THEN
5970 APP_EXCEPTION.RAISE_EXCEPTION;
5971
5972 END GET_TASK_PARAMETER;
5973
5974 -- This API adds the selected instruction to the working instruction set during
5975 -- Instruction setup time.
5976
5977 PROCEDURE ADD_INSTRUCTIONS (
5978 P_INSTRUCTION_PROCESS_ID IN NUMBER,
5979 P_INSTRUCTION_SET_ID IN NUMBER,
5980 P_INSTRUCTION_ID IN NUMBER,
5981 P_ADD_MODE IN VARCHAR2,
5982 P_INSTRUCTIONS IN FND_TABLE_OF_VARCHAR2_255,
5983 P_INSTRUCTION_NOS IN FND_TABLE_OF_VARCHAR2_255,
5984 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5985 X_MSG_COUNT OUT NOCOPY NUMBER,
5986 X_MSG_DATA OUT NOCOPY VARCHAR2
5987 )
5988 IS PRAGMA AUTONOMOUS_TRANSACTION;
5989
5990 l_creation_date date;
5991 l_created_by number;
5992 l_last_update_date date;
5993 l_last_updated_by number;
5994 l_last_update_login number;
5995
5996 l_count number;
5997 l_working_instr_seq number;
5998
5999 l_working_instruction_id number;
6000 l_instruction_id number;
6001 l_instr_seq number;
6002 l_task_id number;
6003 l_task_attribute_id varchar2(4000);
6004 l_task_attribute varchar2(4000);
6005 l_instr_ackn_type varchar2(40);
6006 l_instr_text varchar2(4000);
6007 l_task_label varchar2(200);
6008 l_approver_seq number;
6009 l_role_name varchar2(300);
6010 l_appr_count number;
6011 l_valid_process number;
6012 -- Bug 5686314 : start
6013 l_maximum_allowed_task number;
6014 l_task_count_per_inst_set number;
6015 -- Bug 5686314 : End
6016
6017 cursor get_instr_detail is
6018 select instruction_id, task_id, task_attribute_id,
6019 task_attribute, instr_ackn_type,
6020 instruction_text, task_label
6021 from gmo_instr_defn_vl
6022 where instruction_id = l_instruction_id;
6023
6024 cursor get_instr_appr_detail is
6025 select approver_seq, role_name, role_count
6026 from gmo_instr_appr_defn
6027 where instruction_id = l_instruction_id;
6028
6029 cursor is_valid_process is
6030 select count(*) from gmo_instr_set_defn_t
6031 where instruction_set_id = P_INSTRUCTION_SET_ID
6032 and instruction_process_id = P_INSTRUCTION_PROCESS_ID;
6033 -- Bug 5686314 : rvsingh :start
6034 cursor task_count_per_inst_set is
6035 select count(*) from gmo_instr_defn_t where
6036 instruction_process_id = p_instruction_process_id
6037 AND instruction_set_id = p_instruction_set_id
6038 AND task_id =l_task_id;
6039
6040 INVALID_TASK_ERR exception;
6041 -- Bug 5686314 : rvsingh :End
6042
6043 INVALID_PROCESS_ERR exception;
6044 INVALID_PROCESS_INSTR_ERR exception;
6045
6046 BEGIN
6047 GMO_UTILITIES.GET_WHO_COLUMNS
6048 (
6049 X_CREATION_DATE => L_CREATION_DATE,
6050 X_CREATED_BY => L_CREATED_BY,
6051 X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
6052 X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
6053 X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
6054 );
6055
6056 open is_valid_process;
6057 fetch is_valid_process into l_valid_process;
6058 close is_valid_process;
6059
6060 if (l_valid_process = 0) then
6061 raise INVALID_PROCESS_ERR;
6062 end if;
6063
6064 l_count := p_instructions.count;
6065 l_working_instr_seq := 0;
6066 l_instr_seq := 0;
6067
6068 if (l_count > 0) then
6069
6070 if (p_instruction_id is not null) then
6071 select instr_seq into l_instr_seq
6072 from gmo_instr_defn_t
6073 where instruction_set_id = P_INSTRUCTION_SET_ID
6074 and instruction_process_id = P_INSTRUCTION_PROCESS_ID
6075 and instruction_id = P_INSTRUCTION_ID;
6076
6077 if (l_instr_seq <> 0) then
6078 if (P_ADD_MODE = 'AFTER') then
6079 l_working_instr_seq := l_instr_seq + 1;
6080 elsif (P_ADD_MODE = 'BEFORE') then
6081 l_working_instr_seq := l_instr_seq;
6082 end if;
6083
6084 update gmo_instr_defn_t
6085 set instr_seq = instr_seq + l_count
6086 where instruction_set_id = P_INSTRUCTION_SET_ID
6087 and instruction_process_id = P_INSTRUCTION_PROCESS_ID
6088 and instr_seq >= l_working_instr_seq;
6089
6090 else
6091 raise INVALID_PROCESS_INSTR_ERR;
6092 end if;
6093
6094 end if;
6095
6096 if (l_working_instr_seq = 0) then
6097 select nvl(max(instr_seq), 0) into l_working_instr_seq
6098 from gmo_instr_defn_t
6099 where instruction_set_id = P_INSTRUCTION_SET_ID
6100 and instruction_process_id = P_INSTRUCTION_PROCESS_ID;
6101
6102 l_working_instr_seq := l_working_instr_seq + 1;
6103 end if;
6104
6105 for i in 1 .. P_INSTRUCTIONS.count loop
6106 l_instruction_id := to_number (P_INSTRUCTIONS (i));
6107
6108 open get_instr_detail;
6109 fetch get_instr_detail
6110 into l_instruction_id,l_task_id,
6111 l_task_attribute_id,l_task_attribute,
6112 l_instr_ackn_type, l_instr_text, l_task_label;
6113
6114 close get_instr_detail;
6115
6116 if (l_instruction_id is not null) then
6117 -- task id is not null then check with maximum allowed task
6118 if(l_task_id IS NOT NULL) THEN
6119 select max_allowed_task into l_maximum_allowed_task from GMO_INSTR_TASK_DEFN_VL
6120 where task_id = l_task_id;
6121 open task_count_per_inst_set;
6122 fetch task_count_per_inst_set into l_task_count_per_inst_set;
6123 if(l_task_count_per_inst_set >= l_maximum_allowed_task) THEN
6124 RAISE INVALID_TASK_ERR;
6125 end if;
6126 CLOSE task_count_per_inst_set; --Code added by RBORPATL . Bug#14661007
6127 end if;
6128
6129 select gmo_instr_defn_s.nextval into l_working_instruction_id from dual;
6130
6131 insert into gmo_instr_defn_t
6132 (instruction_id,
6133 instruction_process_id,
6134 instruction_text,
6135 instruction_set_id,
6136 instr_seq,
6137 task_id,
6138 task_attribute_id,
6139 task_attribute,
6140 instr_ackn_type,
6141 instr_number,
6142 creation_date,
6143 created_by,
6144 last_update_date,
6145 last_updated_by,
6146 last_update_login,
6147 task_label)
6148 values
6149 (
6150 l_working_instruction_id,
6151 p_instruction_process_id,
6152 l_instr_text,
6153 p_instruction_set_id,
6154 l_working_instr_seq,
6155 l_task_id,
6156 l_task_attribute_id,
6157 l_task_attribute,
6158 l_instr_ackn_type,
6159 P_INSTRUCTION_NOS(i),
6160 l_creation_date,
6161 l_created_by,
6162 l_last_update_date,
6163 l_last_updated_by,
6164 l_last_update_login,
6165 l_task_label
6166 );
6167
6168 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments (
6169 X_from_entity_name => 'GMO_INSTR_DEFN_B',
6170 X_from_pk1_value => l_instruction_id,
6171 X_from_pk2_value => NULL,
6172 X_from_pk3_value => NULL,
6173 X_from_pk4_value => NULL,
6174 X_from_pk5_value => NULL,
6175 X_to_entity_name => 'GMO_INSTR_DEFN_T',
6176 X_to_pk1_value => l_working_instruction_id,
6177 X_to_pk2_value => P_INSTRUCTION_PROCESS_ID,
6178 X_to_pk3_value => NULL,
6179 X_to_pk4_value => NULL,
6180 X_to_pk5_value => NULL,
6181 X_created_by => L_CREATED_BY ,
6182 X_last_update_login => L_LAST_UPDATE_LOGIN,
6183 X_program_application_id => NULL,
6184 X_program_id => NULL,
6185 X_request_id => NULL,
6186 X_automatically_added_flag => 'N',
6187 X_from_category_id => NULL,
6188 X_to_category_id => NULL
6189 );
6190
6191 open get_instr_appr_detail;
6192 loop
6193 fetch get_instr_appr_detail into l_approver_seq,l_role_name, l_appr_count;
6194 exit when get_instr_appr_detail%notfound;
6195
6196 select gmo_instr_appr_defn_s.nextval
6197 into l_approver_seq from dual;
6198
6199 insert into
6200 gmo_instr_appr_defn_t
6201 (instruction_id, instruction_process_id,
6202 approver_seq, role_name, role_count, creation_date,
6203 created_by, last_update_date, last_updated_by, last_update_login)
6204 values (l_working_instruction_id, p_instruction_process_id,
6205 l_approver_seq, l_role_name, l_appr_count, l_creation_date,
6206 l_created_by, l_last_update_date, l_last_updated_by, l_last_update_login);
6207
6208 end loop;
6209 close get_instr_appr_detail;
6210 l_working_instr_seq := l_working_instr_seq + 1;
6211 end if;
6212
6213 end loop;
6214
6215 end if;
6216
6217 commit;
6218
6219 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
6220
6221 EXCEPTION
6222 WHEN INVALID_TASK_ERR THEN
6223 ROLLBACK;
6224
6225 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
6226 FND_MESSAGE.SET_NAME('GMO', 'GMO_EXCEEDED_TASK');
6227 FND_MSG_PUB.ADD;
6228 FND_MSG_PUB.COUNT_AND_GET
6229 (
6230 P_COUNT => X_MSG_COUNT,
6231 P_DATA => X_MSG_DATA
6232 );
6233 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6234 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
6235 'gmo.plsql.gmo_instructions_pvt.add_instructions',
6236 FALSE);
6237 END IF;
6238 WHEN OTHERS THEN
6239 rollback;
6240 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
6241 FND_MESSAGE.SET_NAME('GMO','GMO_INSTR_UNEXPECTED_DB_ERR');
6242 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6243 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
6244 FND_MSG_PUB.ADD;
6245 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
6246 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6247 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_instructions_pvt.add_instructions', FALSE);
6248 end if;
6249
6250 END ADD_INSTRUCTIONS;
6251
6252
6253 PROCEDURE GET_INSTR_INSTANCE_XML
6254 (
6255 P_INSTRUCTION_PROCESS_ID IN NUMBER,
6256 X_OUTPUT_XML OUT NOCOPY CLOB
6257 )
6258
6259 IS
6260
6261 --This variable would hold the required instruction details in XML format.
6262 L_INSTR_XML XMLTYPE;
6263
6264 BEGIN
6265
6266 --This SQLX query will fetch us the required XML.
6267 SELECT XMLELEMENT("INSTRUCTION_SET",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
6268 XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
6269 INSTR_SETVL.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
6270 INSTR_SETVL.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
6271 (SELECT
6272 LK.MEANING
6273 FROM
6274 FND_LOOKUP_VALUES_VL LK
6275 WHERE
6276 LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SETVL.ENTITY_NAME
6277 AND
6278 LK.LOOKUP_CODE = INSTR_SETVL.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
6279 (SELECT
6280 XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",XMLFOREST(INSTRVL.INSTRUCTION_TEXT,
6281 (SELECT
6282 MEANING
6283 FROM
6284 FND_LOOKUP_VALUES_VL LK1
6285 WHERE
6286 LK1.LOOKUP_TYPE = 'GMO_INSTR_STATUS_TYPES'
6287 AND
6288 LK1.LOOKUP_CODE = INSTR.INSTR_STATUS) AS INSTRUCTION_STATUS,
6289 (SELECT
6290 MEANING
6291 FROM
6292 FND_LOOKUP_VALUES_VL LK2
6293 WHERE
6294 LK2.LOOKUP_TYPE = 'GMO_INSTR_ACKN_TYPES'
6295 AND LK2.LOOKUP_CODE = INSTRVL.INSTR_ACKN_TYPE)
6296 AS INSTRUCTION_ACKN_TYPE,
6297 decode(INSTR.INSTR_STATUS,GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING,
6298 NULL,
6299 GMO_UTILITIES.GET_USER_DISPLAY_NAME(INSTR.LAST_UPDATED_BY))
6300 AS PERFORMED_BY,
6301 INSTR.LAST_UPDATE_DATE LAST_UPDATE_DATE,
6302 INSTR.COMMENTS COMMENTS,
6303 (SELECT
6304 MAX(EREC.INSTR_EREC_ID)
6305 FROM
6306 GMO_INSTR_EREC_INSTANCE EREC
6307 WHERE
6308 EREC.INSTRUCTION_ID = INSTR.INSTRUCTION_ID)
6309 AS INSTRUCTION_ERECORD_ID,
6310 (SELECT XMLAGG(XMLELEMENT("TASK_DETAILS",XMLFOREST(TK.TASK_EREC_ID AS TASK_ERECORD_ID,
6311 TK.TASK_IDENTIFIER AS TASK_IDENTIFIER,
6312 TK.TASK_VALUE AS TASK_VALUE)))
6313 FROM
6314 GMO_INSTR_TASK_INSTANCE TK
6315 WHERE TK.INSTRUCTION_ID = INSTR.INSTRUCTION_ID) AS TASKS
6316 )
6317 )
6318 )
6319 FROM GMO_INSTR_INSTANCE_T INSTR,
6320 GMO_INSTR_INSTANCE_VL INSTRVL
6321 WHERE INSTR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID
6322 AND INSTR.INSTRUCTION_ID = INSTRVL.INSTRUCTION_ID) AS "INSTRUCTIONS")
6323 )
6324 )
6325 )
6326 INTO L_INSTR_XML
6327 FROM GMO_INSTR_SET_INSTANCE_T INSTR_SET,
6328 GMO_INSTR_SET_INSTANCE_VL INSTR_SETVL
6329 WHERE INSTR_SET.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
6330 AND INSTR_SETVL.INSTRUCTION_SET_ID = INSTR_SET.INSTRUCTION_SET_ID;
6331
6332 --Return the CLOB value of the XML.
6333 X_OUTPUT_XML := L_INSTR_XML.GETCLOBVAL();
6334
6335 EXCEPTION
6336 WHEN OTHERS THEN
6337 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
6338 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6339 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_INSTRUCTION_PVT');
6340 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_INSTR_INSTANCE_XML');
6341 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6342 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
6343 'gmo.plsql.GMO_INSTRUCTION_PVT.GET_INSTR_INSTANCE_XML',
6344 FALSE
6345 );
6346 end if;
6347 APP_EXCEPTION.RAISE_EXCEPTION;
6348
6349 END GET_INSTR_INSTANCE_XML;
6350
6351
6352 --This function is used to terminate the instruction definition process identified by the
6353 --specified process ID.
6354 PROCEDURE TERMINATE_INSTR_DEFN_PROCESS
6355 (P_INSTRUCTION_PROCESS_ID IN NUMBER)
6356
6357 IS
6358
6359 PRAGMA AUTONOMOUS_TRANSACTION;
6360
6361 BEGIN
6362
6363 --Update the process status to complete for the instruction definition identified by the specified process ID.
6364 UPDATE GMO_INSTR_ATTRIBUTES_T
6365 SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_TERMINATE
6366 WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
6367 AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
6368 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
6369
6370 --Commit the transaction.
6371 COMMIT;
6372
6373 EXCEPTION WHEN OTHERS THEN
6374
6375 ROLLBACK;
6376
6377 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
6378 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6379 FND_MESSAGE.SET_TOKEN('PKG_NAME',G_PKG_NAME);
6380 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','TERMINATE_INSTR_DEFN_PROCESS');
6381
6382 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6383 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
6384 'gmo.plsql.GMO_INSTRUCTION_PVT.TERMINATE_INSTR_DEFN_PROCESS',
6385 FALSE
6386 );
6387 end if;
6388
6389 APP_EXCEPTION.RAISE_EXCEPTION;
6390
6391 END TERMINATE_INSTR_DEFN_PROCESS;
6392
6393 --Bug 5383022: start
6394 procedure is_task_attribute_used
6395 (
6396 p_instruction_process_id IN number,
6397 p_attribute_name IN varchar2,
6398 p_attribute_key IN varchar2,
6399 x_used_flag OUT NOCOPY varchar2,
6400 x_return_status OUT NOCOPY varchar2,
6401 x_msg_count OUT NOCOPY number,
6402 x_msg_data OUT NOCOPY varchar2
6403 )
6404 IS
6405 l_check_attribute varchar2(4000);
6406 l_count number;
6407 l_total_count number;
6408 l_user_response varchar2(1);
6409 l_instruction_set_id number;
6410 l_instruction_process_id number;
6411 l_valid_process number;
6412 BEGIN
6413
6414 l_check_attribute := null;
6415 l_count := 0;
6416 l_instruction_process_id := p_instruction_process_id;
6417
6418 --check valid process
6419 SELECT COUNT(*) into l_valid_process FROM GMO_INSTR_ATTRIBUTES_T
6420 WHERE INSTRUCTION_PROCESS_ID = p_instruction_process_id
6421 AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
6422 AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS;
6423
6424 if (l_valid_process = 0) then
6425 l_instruction_process_id := -1;
6426 end if;
6427
6428
6429 if (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) then
6430 --attribute key is oprnLindId
6431 l_check_attribute := p_attribute_key || '$' || '%';
6432 elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) then
6433 --attribute key is oprnLineId$Resources
6434 l_check_attribute := p_attribute_key;
6435 elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_MATERIAL) then
6436 --attribute key is formulaLineId
6437 l_check_attribute := p_attribute_key;
6438 end if;
6439
6440 if (l_check_attribute is not null) then
6441 BEGIN
6442 if (l_instruction_process_id is null or l_instruction_process_id = -1) then
6443 --if process is not valid check permanent tables
6444 --we are not using any specific entity, so it will take care of all levels
6445 select count(*) into l_count
6446 from gmo_instr_defn_b
6447 where task_attribute_id like l_check_attribute;
6448
6449 l_total_count := l_count;
6450
6451 else
6452 select count(*) into l_count
6453 from gmo_instr_defn_t
6454 where instruction_process_id = l_instruction_process_id
6455 and task_attribute_id like l_check_attribute;
6456
6457 l_total_count := l_count;
6458
6459 --now check permanent tables for the attribute usage
6460 --for all instructions not in current process
6461
6462 select count(*) into l_count
6463 from gmo_instr_defn_b
6464 where task_attribute_id like l_check_attribute
6465 and instruction_set_id not in (select instruction_set_id from gmo_instr_set_defn_t where instruction_process_id = l_instruction_process_id);
6466
6467 l_total_count := l_total_count + l_count;
6468
6469 end if;
6470
6471 EXCEPTION
6472 WHEN NO_DATA_FOUND THEN
6473 null;
6474 END;
6475
6476 end if;
6477
6478 if (l_total_count > 0) then
6479 x_used_flag := GMO_CONSTANTS_GRP.YES;
6480 else
6481 x_used_flag := GMO_CONSTANTS_GRP.NO;
6482 end if;
6483
6484 x_return_status := GMO_CONSTANTS_GRP.RETURN_STATUS_SUCCESS;
6485
6486 EXCEPTION
6487 WHEN OTHERS THEN
6488 X_RETURN_STATUS := GMO_CONSTANTS_GRP.RETURN_STATUS_UNEXP_ERROR;
6489 FND_MESSAGE.SET_NAME('GMO','GMO_UNEXPECTED_DB_ERR');
6490 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6491 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
6492 X_MSG_DATA := fnd_message.get;
6493 END is_task_attribute_used;
6494
6495 --Bug 5383022: end
6496
6497 END GMO_INSTRUCTION_PVT;