[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:
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;
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: /*
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
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;
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
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
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
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
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
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
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:
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
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: )
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
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;
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;
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:
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
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
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
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;
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
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
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
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
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;
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;
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
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,
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,
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,
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:
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,
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,
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,
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,
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,
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,
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;
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
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;
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
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;
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
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;
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
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;
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
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);
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;
5647: L_LAST_UPDATE_LOGIN
5648: );
5649: END IF;
5650: ELSE
5651: RETURN GMO_CONSTANTS_GRP.NO;
5652: END IF;
5653:
5654: COMMIT;
5655:
5652: END IF;
5653:
5654: COMMIT;
5655:
5656: RETURN GMO_CONSTANTS_GRP.YES;
5657:
5658: EXCEPTION
5659: WHEN OTHERS THEN
5660: ROLLBACK;
5657:
5658: EXCEPTION
5659: WHEN OTHERS THEN
5660: ROLLBACK;
5661: RETURN GMO_CONSTANTS_GRP.NO;
5662:
5663: END INSERT_PROCESS_VARIABLE;
5664:
5665:
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;
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;
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.
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;
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
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);
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;
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:
5939:
5940: -- SAVE CHANGES
5941: COMMIT;
5942:
5943: RETURN GMO_CONSTANTS_GRP.YES;
5944:
5945: EXCEPTION
5946: WHEN OTHERS THEN
5947: ROLLBACK;
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
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,
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:
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;
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:
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;
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;
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
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
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:
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:
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:
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;
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;