[Home] [Help]
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,
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;
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,
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,
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);
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:
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:
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
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,
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,
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,
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,
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,
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,
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,
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,
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
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,
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
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:
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
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
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);
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
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,
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,
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,
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
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;
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
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,
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,
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;
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:
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,
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,
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,
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,
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:
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,
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,
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:
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,
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,
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,
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,
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,
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,
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:
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,
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,
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:
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,
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;
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:
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:
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;
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,
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,
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,
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,
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,
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
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,
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;
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,
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,
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,
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,
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
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,
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;
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,
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;
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,
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,
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,
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,
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,
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:
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,
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,
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,
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,
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,
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,
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:
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;
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;
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,
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,
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,
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,
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,
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,
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:
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,
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),
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,
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,
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,
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:
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
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
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:
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;
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
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
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;
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;
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
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
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
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
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:
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:
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
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
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
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;
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:
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:
3737: WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3738:
3739: IF (P_ENTITY_KEY IS NOT NULL) THEN
3740:
3741: IF(L_ENTITY_KEY LIKE (GMO_CONSTANTS_GRP.G_INSTR_PREFIX || '%' )) THEN
3742: --Bug 5224619: start
3743: UPDATE GMO_INSTR_SET_INSTANCE_B
3744: SET ENTITY_KEY = P_ENTITY_KEY
3745: WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3756: END IF;
3757:
3758: L_INSTANCE_STATUS := GET_PROCESS_VARIABLE
3759: ( P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
3760: P_ATTRIBUTE_NAME => GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
3761: P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
3762: );
3763:
3764: IF(L_INSTANCE_STATUS IS NOT NULL
3757:
3758: L_INSTANCE_STATUS := GET_PROCESS_VARIABLE
3759: ( P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
3760: P_ATTRIBUTE_NAME => GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
3761: P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
3762: );
3763:
3764: IF(L_INSTANCE_STATUS IS NOT NULL
3765: AND L_INSTANCE_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_COMPLETE ) THEN
3761: P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
3762: );
3763:
3764: IF(L_INSTANCE_STATUS IS NOT NULL
3765: AND L_INSTANCE_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_COMPLETE ) THEN
3766:
3767: OPEN L_TEMP_INSTR_SET_CSR;
3768: LOOP
3769: FETCH L_TEMP_INSTR_SET_CSR INTO L_INSTR_SET_REC;
3772: --Bug 4730261:start
3773: --update the instructions only when the set is active
3774: select count(*) into l_set_active from gmo_instr_set_instance_vl
3775: where instruction_set_id = L_INSTR_SET_REC.INSTRUCTION_SET_ID
3776: and instr_set_status = GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE;
3777:
3778: if (l_set_active > 0) then
3779: --Bug 4730261: end
3780: --Bug 5224619: start
3779: --Bug 4730261: end
3780: --Bug 5224619: start
3781: UPDATE GMO_INSTR_SET_INSTANCE_B
3782: SET
3783: ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
3784: LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
3785: LAST_UPDATED_BY = L_LAST_UPDATED_BY,
3786: LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
3787: WHERE
3796: EXIT WHEN L_TEMP_INSTR_CSR%NOTFOUND;
3797:
3798: L_INSTR_STATUS := L_INSTR_REC.INSTR_STATUS;
3799:
3800: IF(L_INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE ) THEN
3801: L_INSTR_STATUS := GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE;
3802:
3803: UPDATE GMO_INSTR_INSTANCE_T
3804: SET INSTR_STATUS = L_INSTR_STATUS
3797:
3798: L_INSTR_STATUS := L_INSTR_REC.INSTR_STATUS;
3799:
3800: IF(L_INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE ) THEN
3801: L_INSTR_STATUS := GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE;
3802:
3803: UPDATE GMO_INSTR_INSTANCE_T
3804: SET INSTR_STATUS = L_INSTR_STATUS
3805: WHERE INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE
3801: L_INSTR_STATUS := GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE;
3802:
3803: UPDATE GMO_INSTR_INSTANCE_T
3804: SET INSTR_STATUS = L_INSTR_STATUS
3805: WHERE INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE
3806: AND INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
3807:
3808: --Bug 5224619: start
3809: UPDATE GMO_INSTR_INSTANCE_TL
3917: --Set the instruction set status to 'complete' if all the
3918: --instructions are complete in a given instruction set
3919: SELECT COUNT(*) INTO L_INSTRUCTION_COUNT
3920: FROM GMO_INSTR_INSTANCE_B
3921: WHERE INSTR_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE
3922: AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3923:
3924: -- Set the Instruction set status to complete
3925: IF(L_INSTRUCTION_COUNT = 0) THEN
3924: -- Set the Instruction set status to complete
3925: IF(L_INSTRUCTION_COUNT = 0) THEN
3926: --Bug 5224619: start
3927: UPDATE GMO_INSTR_SET_INSTANCE_B
3928: SET INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_COMPLETE
3929: WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
3930: --Bug 5224619: end
3931: END IF;
3932:
3980: P_ENTITY_KEY IN VARCHAR2 DEFAULT NULL,
3981: P_TASK_ERECORD_ID IN FND_TABLE_OF_VARCHAR2_255,
3982: P_TASK_IDENTIFIER IN FND_TABLE_OF_VARCHAR2_255,
3983: P_TASK_VALUE IN FND_TABLE_OF_VARCHAR2_255,
3984: P_DISABLE_TASK IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO,
3985: P_MANUAL_ENTRY IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO,
3986: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3987: X_MSG_COUNT OUT NOCOPY NUMBER,
3988: X_MSG_DATA OUT NOCOPY VARCHAR2
3981: P_TASK_ERECORD_ID IN FND_TABLE_OF_VARCHAR2_255,
3982: P_TASK_IDENTIFIER IN FND_TABLE_OF_VARCHAR2_255,
3983: P_TASK_VALUE IN FND_TABLE_OF_VARCHAR2_255,
3984: P_DISABLE_TASK IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO,
3985: P_MANUAL_ENTRY IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.NO,
3986: X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3987: X_MSG_COUNT OUT NOCOPY NUMBER,
3988: X_MSG_DATA OUT NOCOPY VARCHAR2
3989: )
4027:
4028: SELECT COUNT(*) INTO L_PROCESS_COUNT
4029: FROM GMO_INSTR_ATTRIBUTES_T
4030: WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4031: AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_INSTANCE_STATUS
4032: AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
4033: AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_ERROR;
4034:
4035: IF L_PROCESS_COUNT = 0 THEN
4028: SELECT COUNT(*) INTO L_PROCESS_COUNT
4029: FROM GMO_INSTR_ATTRIBUTES_T
4030: WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4031: AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_INSTANCE_STATUS
4032: AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
4033: AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_ERROR;
4034:
4035: IF L_PROCESS_COUNT = 0 THEN
4036: RAISE L_INVALID_PARAM_ERR;
4029: FROM GMO_INSTR_ATTRIBUTES_T
4030: WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4031: AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_INSTANCE_STATUS
4032: AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
4033: AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_ERROR;
4034:
4035: IF L_PROCESS_COUNT = 0 THEN
4036: RAISE L_INVALID_PARAM_ERR;
4037: END IF;
4044: INSTR_TEMP.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4045: AND INSTR_TEMP.INSTRUCTION_ID = P_INSTRUCTION_ID
4046: AND INSTR_TEMP.INSTRUCTION_ID = INSTRN.INSTRUCTION_ID;
4047:
4048: IF ( P_MANUAL_ENTRY = GMO_CONSTANTS_GRP.YES ) THEN
4049: -- Remove all previously entered manual data, and enter the new
4050: -- rows passed in current API call, this will take care of
4051: -- delete task row in case of manual entry
4052:
4052:
4053: DELETE FROM GMO_INSTR_TASK_INSTANCE_T
4054: WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4055: AND INSTRUCTION_ID = P_INSTRUCTION_ID
4056: AND MANUAL_ENTRY = GMO_CONSTANTS_GRP.YES;
4057:
4058: END IF;
4059:
4060: FOR I IN 1..P_TASK_ERECORD_ID.COUNT LOOP
4116: INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4117:
4118: -- IF THE ENTITY KEY IS internally set by PI while creation,
4119: -- UPDATE IT
4120: IF (L_ENTITY_KEY LIKE ( GMO_CONSTANTS_GRP.G_INSTR_PREFIX || '%')
4121: AND P_ENTITY_KEY IS NOT NULL) THEN
4122: --Bug 5224619: start
4123: UPDATE GMO_INSTR_SET_INSTANCE_B
4124: SET ENTITY_KEY = P_ENTITY_KEY
4133: END IF;
4134:
4135: -- Acknowledge the task by setting the task ackn status
4136: -- only if API is called by task application
4137: IF(P_MANUAL_ENTRY <> GMO_CONSTANTS_GRP.YES ) THEN
4138: UPDATE GMO_INSTR_INSTANCE_T
4139: SET TASK_ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_TASK_ACKN_STATUS,
4140: TASK_ACKN_DATE = L_CREATION_DATE
4141: WHERE INSTRUCTION_ID = P_INSTRUCTION_ID
4135: -- Acknowledge the task by setting the task ackn status
4136: -- only if API is called by task application
4137: IF(P_MANUAL_ENTRY <> GMO_CONSTANTS_GRP.YES ) THEN
4138: UPDATE GMO_INSTR_INSTANCE_T
4139: SET TASK_ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_TASK_ACKN_STATUS,
4140: TASK_ACKN_DATE = L_CREATION_DATE
4141: WHERE INSTRUCTION_ID = P_INSTRUCTION_ID
4142: AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4143: END IF;
4253: FROM GMO_INSTR_SET_INSTANCE_VL
4254: WHERE ENTITY_NAME = P_ENTITY_NAME
4255: AND ENTITY_KEY = P_ENTITY_KEY
4256: AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
4257: AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
4258:
4259: --Set the instruction set status to CANCEL
4260: --Bug 5224619: start
4261: UPDATE GMO_INSTR_SET_INSTANCE_B
4259: --Set the instruction set status to CANCEL
4260: --Bug 5224619: start
4261: UPDATE GMO_INSTR_SET_INSTANCE_B
4262: SET
4263: INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL
4264: WHERE
4265: INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
4266: --Bug 5224619: end
4267: --Bug 4730261: start
4334: -- This must complete optional instructions in temp table for
4335: -- particular session
4336: UPDATE GMO_INSTR_INSTANCE_T
4337: SET
4338: INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE,
4339: LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
4340: LAST_UPDATED_BY = L_LAST_UPDATED_BY,
4341: LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
4342: WHERE
4339: LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
4340: LAST_UPDATED_BY = L_LAST_UPDATED_BY,
4341: LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
4342: WHERE
4343: INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
4344: AND
4345: INSTRUCTION_ID IN (
4346: SELECT INSTRUCTION_ID FROM GMO_INSTR_INSTANCE_B
4347: WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
4344: AND
4345: INSTRUCTION_ID IN (
4346: SELECT INSTRUCTION_ID FROM GMO_INSTR_INSTANCE_B
4347: WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
4348: AND INSTR_ACKN_TYPE = GMO_CONSTANTS_GRP.G_INSTR_OPTIONAL
4349: )
4350: AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
4351:
4352: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4397: L_API_NAME := 'GET_DEFN_STATUS';
4398:
4399: SELECT ATTRIBUTE_VALUE INTO X_DEFINITION_STATUS
4400: FROM GMO_INSTR_ATTRIBUTES_T
4401: WHERE ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS
4402: AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4403: AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
4404:
4405: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4399: SELECT ATTRIBUTE_VALUE INTO X_DEFINITION_STATUS
4400: FROM GMO_INSTR_ATTRIBUTES_T
4401: WHERE ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS
4402: AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
4403: AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
4404:
4405: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4406:
4407: EXCEPTION
4735: FROM GMO_INSTR_SET_INSTANCE_B
4736: WHERE ENTITY_NAME = P_ENTITY_NAME
4737: AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
4738: AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
4739: AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
4740:
4741: CURSOR L_INSTR_CSR IS
4742: SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID,
4743: COMMENTS, OPERATOR_ACKN, INSTR_STATUS,
4839: L_INSTR_REC.OPERATOR_ACKN,
4840: L_INSTR_REC.INSTR_STATUS,
4841: DECODE(L_INSTR_REC.TASK_ID,
4842: NULL,NULL,
4843: GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS),
4844: L_INSTR_REC.TASK_ID,
4845: DECODE(L_INSTR_REC.TASK_ID,NULL,NULL,GMO_CONSTANTS_GRP.NO),
4846: L_CREATION_DATE, -- FIGURE THIS OUT
4847: L_CREATED_BY,
4841: DECODE(L_INSTR_REC.TASK_ID,
4842: NULL,NULL,
4843: GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS),
4844: L_INSTR_REC.TASK_ID,
4845: DECODE(L_INSTR_REC.TASK_ID,NULL,NULL,GMO_CONSTANTS_GRP.NO),
4846: L_CREATION_DATE, -- FIGURE THIS OUT
4847: L_CREATED_BY,
4848: L_LAST_UPDATE_DATE,
4849: L_LAST_UPDATED_BY,
5012: WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
5013:
5014: -- CHECK THE INSTRUCTION SET STATUS, IT MUST BE ACTIVE
5015: -- FOR THE PROCESS TO PROCEED FURTHER
5016: IF( RTRIM(L_INSTR_SET_STATUS) <> GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE ) THEN
5017:
5018: ROLLBACK;
5019: X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5020:
5097: VALUES
5098: (
5099: L_INSTRUCTION_PROCESS_ID,
5100: GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5101: GMO_CONSTANTS_GRP.G_PARAM_ENTITY,
5102: P_ENTITY_NAME,
5103: P_ENTITY_KEY,
5104: P_INSTRUCTION_TYPE,
5105: GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5101: GMO_CONSTANTS_GRP.G_PARAM_ENTITY,
5102: P_ENTITY_NAME,
5103: P_ENTITY_KEY,
5104: P_INSTRUCTION_TYPE,
5105: GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5106: L_CREATION_DATE,
5107: L_CREATED_BY,
5108: L_LAST_UPDATE_DATE,
5109: L_LAST_UPDATED_BY,
5126: VALUES
5127: (
5128: L_INSTRUCTION_PROCESS_ID,
5129: GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5130: GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
5131: GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
5132: GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5133: L_CREATION_DATE,
5134: L_CREATED_BY,
5127: (
5128: L_INSTRUCTION_PROCESS_ID,
5129: GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5130: GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
5131: GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
5132: GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5133: L_CREATION_DATE,
5134: L_CREATED_BY,
5135: L_LAST_UPDATE_DATE,
5128: L_INSTRUCTION_PROCESS_ID,
5129: GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5130: GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
5131: GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
5132: GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5133: L_CREATION_DATE,
5134: L_CREATED_BY,
5135: L_LAST_UPDATE_DATE,
5136: L_LAST_UPDATED_BY,
5161: L_INSTRUCTION_PROCESS_ID,
5162: GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
5163: P_CONTEXT_PARAM_NAME(i),
5164: P_CONTEXT_PARAM_VALUE(i),
5165: GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
5166: L_CREATION_DATE,
5167: L_CREATED_BY,
5168: L_LAST_UPDATE_DATE,
5169: L_LAST_UPDATED_BY,
5361: IF(P_UPDATE_DEFN_STATUS = FND_API.G_TRUE) THEN
5362: UPDATE
5363: GMO_INSTR_ATTRIBUTES_T
5364: SET
5365: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
5366: WHERE
5367: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5368: AND
5369: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
5365: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
5366: WHERE
5367: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5368: AND
5369: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
5370: END IF;
5371:
5372: --Update the PROCESS_STATUS to success.
5373: UPDATE
5372: --Update the PROCESS_STATUS to success.
5373: UPDATE
5374: GMO_INSTR_ATTRIBUTES_T
5375: SET
5376: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
5377: WHERE
5378: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5379: AND
5380: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5376: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
5377: WHERE
5378: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5379: AND
5380: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5381:
5382: COMMIT;
5383:
5384: EXCEPTION
5424: IF(P_UPDATE_DEFN_STATUS = FND_API.G_TRUE) THEN
5425: UPDATE
5426: GMO_INSTR_ATTRIBUTES_T
5427: SET
5428: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
5429: WHERE
5430: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5431: AND
5432: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
5428: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
5429: WHERE
5430: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5431: AND
5432: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
5433: END IF;
5434:
5435: --Update the PROCESS_STATUS to success.
5436: UPDATE
5435: --Update the PROCESS_STATUS to success.
5436: UPDATE
5437: GMO_INSTR_ATTRIBUTES_T
5438: SET
5439: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
5440: WHERE
5441: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5442: AND
5443: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5439: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
5440: WHERE
5441: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5442: AND
5443: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5444:
5445: COMMIT;
5446:
5447: EXCEPTION
5488: --Update the PROCESS_STATUS to cancel.
5489: UPDATE
5490: GMO_INSTR_ATTRIBUTES_T
5491: SET
5492: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_CANCEL
5493: WHERE
5494: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5495: AND
5496: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5492: ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_CANCEL
5493: WHERE
5494: INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
5495: AND
5496: ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
5497:
5498: COMMIT;
5499:
5500: EXCEPTION
5522: FUNCTION GET_PROCESS_VARIABLE
5523: (
5524: P_INSTRUCTION_PROCESS_ID IN NUMBER,
5525: P_ATTRIBUTE_NAME IN VARCHAR2 ,
5526: P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5527: )
5528: RETURN VARCHAR2
5529: IS
5530: L_ATTRIBUTE_VALUE VARCHAR2(1000);
5552: (
5553: P_INSTRUCTION_PROCESS_ID IN NUMBER ,
5554: P_ATTRIBUTE_NAME IN VARCHAR2 ,
5555: P_ATTRIBUTE_VALUE IN VARCHAR2,
5556: P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5557: )
5558: RETURN VARCHAR2
5559: IS
5560: PRAGMA AUTONOMOUS_TRANSACTION;
5625: L_LAST_UPDATE_LOGIN
5626: );
5627: END IF;
5628: ELSE
5629: RETURN GMO_CONSTANTS_GRP.NO;
5630: END IF;
5631:
5632: COMMIT;
5633:
5630: END IF;
5631:
5632: COMMIT;
5633:
5634: RETURN GMO_CONSTANTS_GRP.YES;
5635:
5636: EXCEPTION
5637: WHEN OTHERS THEN
5638: ROLLBACK;
5635:
5636: EXCEPTION
5637: WHEN OTHERS THEN
5638: ROLLBACK;
5639: RETURN GMO_CONSTANTS_GRP.NO;
5640:
5641: END INSERT_PROCESS_VARIABLE;
5642:
5643:
5647: FUNCTION SET_PROCESS_VARIABLE
5648: ( P_INSTRUCTION_PROCESS_ID IN NUMBER ,
5649: P_ATTRIBUTE_NAME IN VARCHAR2 ,
5650: P_ATTRIBUTE_VALUE IN VARCHAR2,
5651: P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5652: )
5653: RETURN VARCHAR2
5654: IS
5655: L_CREATION_DATE DATE;
5709: END IF;
5710:
5711: COMMIT;
5712:
5713: RETURN GMO_CONSTANTS_GRP.YES;
5714: EXCEPTION
5715: WHEN OTHERS THEN
5716: ROLLBACK;
5717: RETURN GMO_CONSTANTS_GRP.NO;
5713: RETURN GMO_CONSTANTS_GRP.YES;
5714: EXCEPTION
5715: WHEN OTHERS THEN
5716: ROLLBACK;
5717: RETURN GMO_CONSTANTS_GRP.NO;
5718:
5719: END SET_PROCESS_VARIABLE;
5720:
5721: -- This API is wrapper over ERES E-record Validation API.
5767:
5768: IF ( LENGTH(L_INVALID_ERECORD_LIST) > 0) THEN
5769: X_ERECORD_LIST_STR := SUBSTR(L_INVALID_ERECORD_LIST,2,LENGTH(L_INVALID_ERECORD_LIST));
5770: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5771: X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.YES;
5772: ELSE
5773: X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.NO;
5774: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5775: END IF;
5769: X_ERECORD_LIST_STR := SUBSTR(L_INVALID_ERECORD_LIST,2,LENGTH(L_INVALID_ERECORD_LIST));
5770: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5771: X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.YES;
5772: ELSE
5773: X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.NO;
5774: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
5775: END IF;
5776:
5777: EXCEPTION
5776:
5777: EXCEPTION
5778: WHEN OTHERS THEN
5779: X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
5780: X_ERECORD_ID_INVALID := GMO_CONSTANTS_GRP.YES;
5781:
5782: --Diagnostics Start
5783: FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
5784: FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
5881: (
5882: P_INSTRUCTION_PROCESS_ID IN NUMBER,
5883: P_ATTRIBUTE_NAME IN FND_TABLE_OF_VARCHAR2_255,
5884: P_ATTRIBUTE_VALUE IN FND_TABLE_OF_VARCHAR2_255,
5885: P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
5886: )
5887: RETURN VARCHAR2
5888: IS
5889: PRAGMA AUTONOMOUS_TRANSACTION;
5907: P_ATTRIBUTE_TYPE => P_ATTRIBUTE_TYPE);
5908: END IF;
5909:
5910: --Check the return status for each process attribute
5911: IF (L_RETURN_STATUS = GMO_CONSTANTS_GRP.NO) THEN
5912: ROLLBACK;
5913: APP_EXCEPTION.RAISE_EXCEPTION;
5914: END IF;
5915:
5917:
5918: -- SAVE CHANGES
5919: COMMIT;
5920:
5921: RETURN GMO_CONSTANTS_GRP.YES;
5922:
5923: EXCEPTION
5924: WHEN OTHERS THEN
5925: ROLLBACK;
5939: BEGIN
5940:
5941: L_PARAM_VALUE := GET_PROCESS_VARIABLE(P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
5942: P_ATTRIBUTE_NAME => P_ATTRIBUTE_NAME,
5943: P_ATTRIBUTE_TYPE => GMO_CONSTANTS_GRP.G_PARAM_TASK );
5944: RETURN L_PARAM_VALUE;
5945:
5946: EXCEPTION
5947: WHEN OTHERS THEN
6270: WHERE
6271: LK2.LOOKUP_TYPE = 'GMO_INSTR_ACKN_TYPES'
6272: AND LK2.LOOKUP_CODE = INSTRVL.INSTR_ACKN_TYPE)
6273: AS INSTRUCTION_ACKN_TYPE,
6274: decode(INSTR.INSTR_STATUS,GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING,
6275: NULL,
6276: GMO_UTILITIES.GET_USER_DISPLAY_NAME(INSTR.LAST_UPDATED_BY))
6277: AS PERFORMED_BY,
6278: INSTR.LAST_UPDATE_DATE LAST_UPDATE_DATE,
6338: BEGIN
6339:
6340: --Update the process status to complete for the instruction definition identified by the specified process ID.
6341: UPDATE GMO_INSTR_ATTRIBUTES_T
6342: SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_TERMINATE
6343: WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
6344: AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
6345: AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
6346:
6340: --Update the process status to complete for the instruction definition identified by the specified process ID.
6341: UPDATE GMO_INSTR_ATTRIBUTES_T
6342: SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_TERMINATE
6343: WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
6344: AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
6345: AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
6346:
6347: --Commit the transaction.
6348: COMMIT;
6341: UPDATE GMO_INSTR_ATTRIBUTES_T
6342: SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_TERMINATE
6343: WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
6344: AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
6345: AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
6346:
6347: --Commit the transaction.
6348: COMMIT;
6349:
6394:
6395: --check valid process
6396: SELECT COUNT(*) into l_valid_process FROM GMO_INSTR_ATTRIBUTES_T
6397: WHERE INSTRUCTION_PROCESS_ID = p_instruction_process_id
6398: AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
6399: AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS;
6400:
6401: if (l_valid_process = 0) then
6402: l_instruction_process_id := -1;
6395: --check valid process
6396: SELECT COUNT(*) into l_valid_process FROM GMO_INSTR_ATTRIBUTES_T
6397: WHERE INSTRUCTION_PROCESS_ID = p_instruction_process_id
6398: AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
6399: AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS;
6400:
6401: if (l_valid_process = 0) then
6402: l_instruction_process_id := -1;
6403: end if;
6402: l_instruction_process_id := -1;
6403: end if;
6404:
6405:
6406: if (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) then
6407: --attribute key is oprnLindId
6408: l_check_attribute := p_attribute_key || '$' || '%';
6409: elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) then
6410: --attribute key is oprnLineId$Resources
6405:
6406: if (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) then
6407: --attribute key is oprnLindId
6408: l_check_attribute := p_attribute_key || '$' || '%';
6409: elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) then
6410: --attribute key is oprnLineId$Resources
6411: l_check_attribute := p_attribute_key;
6412: elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_MATERIAL) then
6413: --attribute key is formulaLineId
6408: l_check_attribute := p_attribute_key || '$' || '%';
6409: elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) then
6410: --attribute key is oprnLineId$Resources
6411: l_check_attribute := p_attribute_key;
6412: elsif (p_attribute_name = GMO_CONSTANTS_GRP.ENTITY_MATERIAL) then
6413: --attribute key is formulaLineId
6414: l_check_attribute := p_attribute_key;
6415: end if;
6416:
6452:
6453: end if;
6454:
6455: if (l_total_count > 0) then
6456: x_used_flag := GMO_CONSTANTS_GRP.YES;
6457: else
6458: x_used_flag := GMO_CONSTANTS_GRP.NO;
6459: end if;
6460:
6454:
6455: if (l_total_count > 0) then
6456: x_used_flag := GMO_CONSTANTS_GRP.YES;
6457: else
6458: x_used_flag := GMO_CONSTANTS_GRP.NO;
6459: end if;
6460:
6461: x_return_status := GMO_CONSTANTS_GRP.RETURN_STATUS_SUCCESS;
6462:
6457: else
6458: x_used_flag := GMO_CONSTANTS_GRP.NO;
6459: end if;
6460:
6461: x_return_status := GMO_CONSTANTS_GRP.RETURN_STATUS_SUCCESS;
6462:
6463: EXCEPTION
6464: WHEN OTHERS THEN
6465: X_RETURN_STATUS := GMO_CONSTANTS_GRP.RETURN_STATUS_UNEXP_ERROR;
6461: x_return_status := GMO_CONSTANTS_GRP.RETURN_STATUS_SUCCESS;
6462:
6463: EXCEPTION
6464: WHEN OTHERS THEN
6465: X_RETURN_STATUS := GMO_CONSTANTS_GRP.RETURN_STATUS_UNEXP_ERROR;
6466: FND_MESSAGE.SET_NAME('GMO','GMO_UNEXPECTED_DB_ERR');
6467: FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
6468: FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
6469: X_MSG_DATA := fnd_message.get;