[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_event_name VARCHAR2(100) := 'oracle.apps.gmo.instrset.update';
cursor c_get_entity_key is select entity_key from gmo_instr_set_instance_b where instruction_set_id = P_INSTRUCTION_SET_ID;
PROCEDURE UPDATE_ENTITY_KEY
(
P_INSTRUCTION_PROCESS_ID IN NUMBER,
P_ENTITY_KEY IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE GMO_INSTR_ATTRIBUTES_T SET ENTITY_KEY = P_ENTITY_KEY
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY;
FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','UPDATE_ENTITY_KEY');
'edr.plsql.GMO_INSTRUCTION_PVT.UPDATE_ENTITY_KEY',
FALSE
);
END UPDATE_ENTITY_KEY;
P_MODE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE,
P_CONTEXT_PARAMETERS IN GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE,
X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS PRAGMA AUTONOMOUS_TRANSACTION;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT INSTRUCTION_SET_ID, INSTRUCTION_TYPE,
ENTITY_NAME, ENTITY_KEY, INSTR_SET_NAME,
INSTR_SET_DESC, ACKN_STATUS
FROM GMO_INSTR_SET_DEFN_VL
WHERE ENTITY_NAME = L_ENTITY_NAME
AND NVL(ENTITY_KEY,1) = NVL(L_ENTITY_KEY,1)
AND ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS;
SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID, INSTRUCTION_TEXT,
TASK_ID, TASK_ATTRIBUTE, TASK_ATTRIBUTE_ID, TASK_LABEL, INSTR_SEQ,
INSTR_ACKN_TYPE, INSTR_NUMBER
FROM
GMO_INSTR_DEFN_VL
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
SELECT INSTRUCTION_ID, APPROVER_SEQ,
ROLE_COUNT, ROLE_NAME
FROM
GMO_INSTR_APPR_DEFN
WHERE INSTRUCTION_ID = L_INSTRUCTION_ID;
SELECT COUNT(*) FROM GMO_INSTR_ATTRIBUTES_T
WHERE INSTRUCTION_PROCESS_ID = P_CURR_INSTR_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
AND ATTRIBUTE_VALUE <> GMO_CONSTANTS_GRP.G_PROCESS_TERMINATE;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
AND (P_MODE <> GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE)) THEN
RAISE L_MODE_PARAM_ERR;
SELECT GMO_INSTR_PROCESS_ID_S.NEXTVAL INTO L_INSTRUCTION_PROCESS_ID
FROM DUAL;
--Insert 'MODE' = P_MODE
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE,
P_MODE,
GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
GMO_CONSTANTS_GRP.G_DEFINITION_STATUS,
GMO_CONSTANTS_GRP.G_STATUS_NO_CHANGE,
GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
GMO_CONSTANTS_GRP.G_PROCESS_STATUS,
GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
-- update the mode
UPDATE GMO_INSTR_ATTRIBUTES_T
SET ATTRIBUTE_VALUE = P_MODE,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE
AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
-- update the status to ERROR
UPDATE GMO_INSTR_ATTRIBUTES_T
SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
UPDATE GMO_INSTR_ATTRIBUTES_T
SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_RENDER_FALSE,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY;
SELECT COUNT(*) INTO L_ENTITY_EXIST_COUNT FROM GMO_INSTR_ATTRIBUTES_T
WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
AND ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_ENTITY_KEY;
-- Validate all of these before inserting
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
ENTITY_NAME,
ENTITY_KEY,
ENTITY_DISPLAY_NAME,
INSTRUCTION_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
GMO_CONSTANTS_GRP.G_PARAM_ENTITY,
GMO_CONSTANTS_GRP.G_RENDER_TRUE,
GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
L_ENTITY_NAME,
L_ENTITY_KEY,
L_ENTITY_DISPLAYNAME,
L_INSTRUCTION_TYPE,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
UPDATE GMO_INSTR_ATTRIBUTES_T
SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_RENDER_TRUE,
ENTITY_DISPLAY_NAME = L_ENTITY_DISPLAYNAME
WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
AND ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_ENTITY_KEY;
DELETE FROM GMO_INSTR_ATTRIBUTES_T WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_CONTEXT;
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
P_CONTEXT_PARAMETERS(I).NAME,
P_CONTEXT_PARAMETERS(I).VALUE,
GMO_CONSTANTS_GRP.G_PARAM_CONTEXT,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
SELECT COUNT(*) INTO L_INSTR_SET_EXIST_COUNT
FROM GMO_INSTR_SET_DEFN_T
WHERE INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
INSERT INTO GMO_INSTR_SET_DEFN_T
(
INSTRUCTION_PROCESS_ID,
INSTRUCTION_SET_ID,
INSTRUCTION_TYPE,
ENTITY_NAME,
ENTITY_KEY,
INSTR_SET_NAME,
INSTR_SET_DESC,
ACKN_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
L_GMO_INSTR_SET_REC.INSTRUCTION_SET_ID,
L_GMO_INSTR_SET_REC.INSTRUCTION_TYPE,
L_GMO_INSTR_SET_REC.ENTITY_NAME,
L_GMO_INSTR_SET_REC.ENTITY_KEY,
L_GMO_INSTR_SET_REC.INSTR_SET_NAME,
L_GMO_INSTR_SET_REC.INSTR_SET_DESC,
GMO_CONSTANTS_GRP.G_INSTR_SET_UNACKN_STATUS,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL
);
INSERT INTO GMO_INSTR_DEFN_T
(
INSTRUCTION_PROCESS_ID,
INSTRUCTION_ID,
INSTRUCTION_SET_ID,
INSTRUCTION_TEXT,
TASK_ID,
TASK_ATTRIBUTE,
TASK_ATTRIBUTE_ID,
TASK_LABEL,
INSTR_SEQ,
INSTR_ACKN_TYPE,
INSTR_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
L_GMO_INSTR_REC.INSTRUCTION_ID,
L_GMO_INSTR_REC.INSTRUCTION_SET_ID,
L_GMO_INSTR_REC.INSTRUCTION_TEXT,
L_GMO_INSTR_REC.TASK_ID,
L_GMO_INSTR_REC.TASK_ATTRIBUTE,
L_GMO_INSTR_REC.TASK_ATTRIBUTE_ID,
L_GMO_INSTR_REC.TASK_LABEL,
L_GMO_INSTR_REC.INSTR_SEQ,
L_GMO_INSTR_REC.INSTR_ACKN_TYPE,
L_GMO_INSTR_REC.INSTR_NUMBER,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL
);
INSERT INTO GMO_INSTR_APPR_DEFN_T
(
INSTRUCTION_PROCESS_ID,
INSTRUCTION_ID,
APPROVER_SEQ,
ROLE_COUNT,
ROLE_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
L_GMO_INSTR_APPR_REC.INSTRUCTION_ID,
L_GMO_INSTR_APPR_REC.APPROVER_SEQ,
L_GMO_INSTR_APPR_REC.ROLE_COUNT,
L_GMO_INSTR_APPR_REC.ROLE_NAME,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
P_MODE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE,
P_CONTEXT_PARAMETERS IN GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE,
X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
P_MODE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE,
P_CONTEXT_PARAMETERS IN GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE,
X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
PROCEDURE DELETE_ENTITY_FOR_PROCESS
(
P_CURR_INSTR_PROCESS_ID IN NUMBER,
P_ENTITY_NAME IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
P_ENTITY_KEY IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)IS PRAGMA AUTONOMOUS_TRANSACTION;
SELECT LOOKUP_CODE FROM FND_LOOKUPS WHERE LOOKUP_TYPE = 'GMO_INSTR_' || L_LOOKUP_TYPE;
L_API_NAME := 'DELETE_ENTITY_FOR_PROCESS';
P_UPDATE_DEFN_STATUS => FND_API.G_TRUE);
DELETE FROM GMO_INSTR_APPR_DEFN_T WHERE INSTRUCTION_PROCESS_ID = X_INSTRUCTION_PROCESS_ID;
DELETE FROM GMO_INSTR_DEFN_T WHERE INSTRUCTION_PROCESS_ID = X_INSTRUCTION_PROCESS_ID;
DELETE FROM GMO_INSTR_SET_DEFN_T WHERE INSTRUCTION_PROCESS_ID = X_INSTRUCTION_PROCESS_ID;
'gmo.plsql.GMO_INSTRUCTION_PVT.DELETE_ENTITY_FOR_PROCESS',FALSE);
END DELETE_ENTITY_FOR_PROCESS;
SELECT INSTRUCTION_SET_ID, INSTRUCTION_TYPE,
ENTITY_NAME, ENTITY_KEY, INSTR_SET_NAME,
INSTR_SET_DESC, ACKN_STATUS
FROM GMO_INSTR_SET_DEFN_VL
WHERE
ENTITY_NAME = P_SOURCE_ENTITY_NAME
AND ENTITY_KEY = P_SOURCE_ENTITY_KEY
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID,
INSTR_SEQ, TASK_ID, TASK_ATTRIBUTE_ID,
TASK_ATTRIBUTE, INSTR_ACKN_TYPE, INSTR_NUMBER,
INSTRUCTION_TEXT, TASK_LABEL
FROM GMO_INSTR_DEFN_VL
WHERE
INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
SELECT INSTRUCTION_ID, APPROVER_SEQ,
ROLE_COUNT, ROLE_NAME
FROM GMO_INSTR_APPR_DEFN
WHERE
INSTRUCTION_ID = L_INSTRUCTION_ID;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
SELECT INSTRUCTION_SET_ID INTO L_TARGET_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_DEFN_VL
WHERE ENTITY_NAME = P_SOURCE_ENTITY_NAME
AND ENTITY_KEY = P_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
SELECT GMO_INSTR_SET_DEFN_S.NEXTVAL INTO L_NEW_INSTRUCTION_SET_ID FROM DUAL;
INSERT INTO GMO_INSTR_SET_DEFN_VL
(
INSTRUCTION_SET_ID,
INSTRUCTION_TYPE,
INSTR_SET_NAME,
INSTR_SET_DESC,
ENTITY_NAME,
ENTITY_KEY,
ACKN_STATUS,
ORIG_SOURCE,
ORIG_SOURCE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_SET_ID,
P_INSTRUCTION_TYPE,
L_INSTR_SET_DEFN_REC.INSTR_SET_NAME,
L_INSTR_SET_DEFN_REC.INSTR_SET_DESC,
P_TARGET_ENTITY_NAME,
P_TARGET_ENTITY_KEY,
GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
GMO_CONSTANTS_GRP.G_ORIG_SOURCE_DEFN,
L_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL
);
SELECT GMO_INSTR_DEFN_S.NEXTVAL INTO L_NEW_INSTRUCTION_ID FROM DUAL;
select task_id into l_entity_task_id from gmo_instr_task_defn_vl
where entity_name = P_TARGET_ENTITY_NAME
and GMO_INSTR_ENTITY_PVT.GET_ENTITYKEY_SEPARATOR_COUNT(entity_key_pattern) = GMO_INSTR_ENTITY_PVT.GET_ENTITYKEY_SEPARATOR_COUNT(P_TARGET_ENTITY_KEY)
and instruction_type = P_INSTRUCTION_TYPE
and task_name = (select task_name from gmo_instr_task_defn_b where task_id = L_INSTR_DEFN_REC.TASK_ID);
INSERT INTO GMO_INSTR_DEFN_VL
(
INSTRUCTION_ID,
INSTRUCTION_SET_ID,
INSTR_SEQ,
TASK_ID,
TASK_LABEL,
TASK_ATTRIBUTE_ID,
TASK_ATTRIBUTE,
INSTR_ACKN_TYPE,
INSTR_NUMBER,
INSTRUCTION_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_ID,
X_INSTRUCTION_SET_ID,
L_INSTR_DEFN_REC.INSTR_SEQ,
l_entity_task_id,
L_INSTR_DEFN_REC.TASK_LABEL,
l_new_task_attribute_id,
L_INSTR_DEFN_REC.TASK_ATTRIBUTE,
L_INSTR_DEFN_REC.INSTR_ACKN_TYPE,
L_INSTR_DEFN_REC.INSTR_NUMBER,
L_INSTR_DEFN_REC.INSTRUCTION_TEXT,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL
);
INSERT INTO GMO_INSTR_APPR_DEFN
(
INSTRUCTION_ID,
APPROVER_SEQ,
ROLE_COUNT,
ROLE_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_ID,
L_INSTR_APPR_DEFN_REC.APPROVER_SEQ,
L_INSTR_APPR_DEFN_REC.ROLE_COUNT,
L_INSTR_APPR_DEFN_REC.ROLE_NAME,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT INSTRUCTION_PROCESS_ID, INSTRUCTION_SET_ID,
INSTRUCTION_TYPE,
ENTITY_NAME, ENTITY_KEY, INSTR_SET_NAME,
INSTR_SET_DESC, CREATION_DATE,
LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
FROM GMO_INSTR_SET_DEFN_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
SELECT INSTRUCTION_PROCESS_ID, INSTRUCTION_ID, INSTRUCTION_SET_ID,
INSTRUCTION_TEXT, INSTR_SEQ, TASK_ID, TASK_ATTRIBUTE_ID,
TASK_ATTRIBUTE, INSTR_ACKN_TYPE, INSTR_NUMBER, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, TASK_LABEL
FROM GMO_INSTR_DEFN_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
ORDER BY INSTR_SEQ, INSTRUCTION_ID;
SELECT INSTRUCTION_PROCESS_ID, INSTRUCTION_ID, APPROVER_SEQ,
ROLE_COUNT, ROLE_NAME, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN
FROM GMO_INSTR_APPR_DEFN_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_ID = L_INSTRUCTION_ID;
SELECT INSTRUCTION_PROCESS_ID, ATTRIBUTE_SEQ,
ATTRIBUTE_NAME, ATTRIBUTE_VALUE,
ENTITY_NAME, ENTITY_KEY, ENTITY_DISPLAY_NAME, INSTRUCTION_TYPE,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
FROM GMO_INSTR_ATTRIBUTES_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
ORDER BY ATTRIBUTE_SEQ;
SELECT INSTRUCTION_PROCESS_ID, ATTRIBUTE_SEQ,
ATTRIBUTE_NAME, ATTRIBUTE_VALUE,
ENTITY_NAME, ENTITY_KEY, ENTITY_DISPLAY_NAME, INSTRUCTION_TYPE,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
FROM GMO_INSTR_ATTRIBUTES_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
AND ENTITY_NAME = L_IN_ENTITY_NAME
AND ENTITY_KEY = L_IN_ENTITY_KEY;
SELECT INSTRUCTION_ID, INSTR_SEQ INTO L_TEMP_INSTR_ID, L_TEMP_INSTR_SEQ FROM GMO_INSTR_DEFN_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_SET_ID = L_PERM_INSTRUCTION_SET_ID
ORDER BY INSTR_SEQ ;
SELECT ATTRIBUTE_VALUE INTO L_DEFINITION_STATUS FROM GMO_INSTR_ATTRIBUTES_T
WHERE ATTRIBUTE_NAME = 'DEFINITION_STATUS' AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID AND ATTRIBUTE_TYPE = 'INTERNAL';
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
-- update the temp table with the data,
-- so that the movement from temp to permanent is
-- to the correct set of rows
IF (L_SOURCE_ENTITY_KEY <> L_TARGET_ENTITY_KEY) THEN
UPDATE GMO_INSTR_SET_DEFN_T
SET ENTITY_KEY = L_TARGET_ENTITY_KEY
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_SOURCE_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
UPDATE GMO_INSTR_ATTRIBUTES_T
SET ENTITY_KEY = L_TARGET_ENTITY_KEY
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PARAM_ENTITY
AND ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_SOURCE_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
SELECT INSTRUCTION_SET_ID INTO L_PERM_INSTRUCTION_SET_ID FROM GMO_INSTR_SET_DEFN_B
WHERE ENTITY_NAME = L_ENTITY_NAME AND ENTITY_KEY = L_TARGET_ENTITY_KEY AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
SELECT INSTRUCTION_SET_ID INTO L_TEMP_INSTRUCTION_SET_ID FROM GMO_INSTR_SET_DEFN_T
WHERE ENTITY_NAME = L_ENTITY_NAME AND ENTITY_KEY = L_TARGET_ENTITY_KEY AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
UPDATE GMO_INSTR_SET_DEFN_T
SET INSTRUCTION_SET_ID = L_PERM_INSTRUCTION_SET_ID
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
UPDATE GMO_INSTR_DEFN_T
SET INSTRUCTION_SET_ID = L_PERM_INSTRUCTION_SET_ID
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_SET_ID = L_TEMP_INSTRUCTION_SET_ID;
--When the target entity is different the instruction id needs to be updated, after instruction
--set id update, to ensure the correct values get updated in the permanent for the new entity.
OPEN L_TEMP_INSTR_CHK_CSR;
SELECT INSTRUCTION_ID INTO L_PERM_INSTR_ID FROM GMO_INSTR_DEFN_B
WHERE INSTRUCTION_SET_ID = L_PERM_INSTRUCTION_SET_ID AND INSTR_SEQ = L_TEMP_INSTR_SEQ;
SELECT GMO_INSTR_DEFN_S.NEXTVAL INTO L_PERM_INSTR_ID FROM DUAL;
update gmo_instr_defn_t set instruction_id = L_PERM_INSTR_ID where instruction_id = l_temp_instr_id;
update gmo_instr_appr_defn_t set instruction_id = L_PERM_INSTR_ID where instruction_id = l_temp_instr_id;
--we may need to update the task attribute if entity key changes
--the below api takes care
GMO_INSTR_ENTITY_PVT.UPDATE_TASK_ATTRIBUTE
(
P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
P_INSTRUCTION_SET_ID => L_PERM_INSTRUCTION_SET_ID,
P_ENTITY_NAME => L_ENTITY_NAME,
P_SOURCE_ENTITY_KEY => L_SOURCE_ENTITY_KEY,
P_TARGET_ENTITY_KEY => L_TARGET_ENTITY_KEY
);
SELECT COUNT(*) INTO L_INSTR_SET_COUNT FROM GMO_INSTR_SET_DEFN_B
WHERE INSTRUCTION_SET_ID = L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
-- If Instruction Set is already present in permenant table then update
-- it with data from temporary table record
IF (L_INSTR_SET_COUNT > 0 ) THEN
--Bug 5224619: start
UPDATE GMO_INSTR_SET_DEFN_B SET
INSTR_SET_NAME = L_TEMP_INSTR_SET_DEFN_REC.INSTR_SET_NAME,
ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_SET_ID = L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
UPDATE GMO_INSTR_SET_DEFN_TL SET
INSTR_SET_DESC = L_TEMP_INSTR_SET_DEFN_REC.INSTR_SET_DESC,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_SET_ID = L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID;
INSERT INTO GMO_INSTR_SET_DEFN_VL
(
INSTRUCTION_SET_ID,
INSTRUCTION_TYPE,
INSTR_SET_NAME,
INSTR_SET_DESC,
ENTITY_NAME,
ENTITY_KEY,
ACKN_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_TYPE,
L_TEMP_INSTR_SET_DEFN_REC.INSTR_SET_NAME,
L_TEMP_INSTR_SET_DEFN_REC.INSTR_SET_DESC,
L_TEMP_INSTR_SET_DEFN_REC.ENTITY_NAME,
L_TARGET_ENTITY_KEY,
GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
-- Delete Attachments from permenant ENTITY, and copy them back from temporary ENTITY
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
X_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_B_ENTITY,
X_pk1_value => L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
X_pk2_value => NULL,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_delete_document_flag => GMO_CONSTANTS_GRP.NO,
X_automatically_added_flag => NULL);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL );
-- Delete Attachments from Temporary ENTITY now
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
X_entity_name => GMO_CONSTANTS_GRP.G_INSTR_SET_DEFN_T_ENTITY,
X_pk1_value => L_TEMP_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
X_pk2_value => P_INSTRUCTION_PROCESS_ID,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_delete_document_flag => GMO_CONSTANTS_GRP.NO,
X_automatically_added_flag => NULL);
-- if yes update the instruction
-- else insert it
SELECT COUNT(*) INTO L_INSTR_COUNT FROM GMO_INSTR_DEFN_B
WHERE INSTRUCTION_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID
AND INSTRUCTION_SET_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_SET_ID;
UPDATE GMO_INSTR_DEFN_B SET
INSTR_SEQ = L_INSTR_SEQ_COUNT,
TASK_ID = L_TEMP_INSTR_DEFN_REC.TASK_ID,
TASK_ATTRIBUTE_ID = L_TEMP_INSTR_DEFN_REC.TASK_ATTRIBUTE_ID,
TASK_ATTRIBUTE = L_TEMP_INSTR_DEFN_REC.TASK_ATTRIBUTE,
INSTR_ACKN_TYPE = L_TEMP_INSTR_DEFN_REC.INSTR_ACKN_TYPE,
INSTR_NUMBER = L_TEMP_INSTR_DEFN_REC.INSTR_NUMBER,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID
AND INSTRUCTION_SET_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_SET_ID;
UPDATE GMO_INSTR_DEFN_TL SET
INSTRUCTION_TEXT = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_TEXT,
TASK_LABEL = L_TEMP_INSTR_DEFN_REC.TASK_LABEL,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_ID = L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID
AND LANGUAGE = USERENV('LANG');
INSERT INTO GMO_INSTR_DEFN_VL
(
INSTRUCTION_ID,
INSTRUCTION_SET_ID,
INSTR_SEQ,
TASK_ID,
TASK_ATTRIBUTE_ID,
TASK_ATTRIBUTE,
INSTR_ACKN_TYPE,
INSTR_NUMBER,
INSTRUCTION_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TASK_LABEL
)
VALUES
(
L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID,
L_TEMP_INSTR_DEFN_REC.INSTRUCTION_SET_ID,
L_INSTR_SEQ_COUNT,
L_TEMP_INSTR_DEFN_REC.TASK_ID,
L_TEMP_INSTR_DEFN_REC.TASK_ATTRIBUTE_ID,
L_TEMP_INSTR_DEFN_REC.TASK_ATTRIBUTE,
L_TEMP_INSTR_DEFN_REC.INSTR_ACKN_TYPE,
L_TEMP_INSTR_DEFN_REC.INSTR_NUMBER,
L_TEMP_INSTR_DEFN_REC.INSTRUCTION_TEXT,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN,
L_TEMP_INSTR_DEFN_REC.TASK_LABEL
);
-- Delete Attachments from permenant ENTITY, and copy
-- them back from temporary
-- ENTITY
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments (
X_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_B_ENTITY,
X_pk1_value => L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID,
X_pk2_value => NULL,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_delete_document_flag => GMO_CONSTANTS_GRP.NO,
X_automatically_added_flag => NULL);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL);
-- Delete Attachments from Temporary ENTITY now
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
X_entity_name => GMO_CONSTANTS_GRP.G_INSTR_DEFN_T_ENTITY,
X_pk1_value => L_TEMP_INSTR_DEFN_REC.INSTRUCTION_ID,
X_pk2_value => P_INSTRUCTION_PROCESS_ID,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_delete_document_flag => GMO_CONSTANTS_GRP.NO,
X_automatically_added_flag => NULL);
SELECT COUNT(*) INTO L_INSTR_APPR_COUNT FROM GMO_INSTR_APPR_DEFN
WHERE INSTRUCTION_ID = L_INSTRUCTION_ID
AND APPROVER_SEQ = L_TEMP_INSTR_APPR_DEFN_REC.APPROVER_SEQ;
UPDATE GMO_INSTR_APPR_DEFN SET
ROLE_COUNT = L_TEMP_INSTR_APPR_DEFN_REC.ROLE_COUNT,
ROLE_NAME = L_TEMP_INSTR_APPR_DEFN_REC.ROLE_NAME,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_ID = L_INSTRUCTION_ID
AND APPROVER_SEQ = L_TEMP_INSTR_APPR_DEFN_REC.APPROVER_SEQ;
INSERT INTO GMO_INSTR_APPR_DEFN
(
INSTRUCTION_ID,
APPROVER_SEQ,
ROLE_COUNT,
ROLE_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_TEMP_INSTR_APPR_DEFN_REC.INSTRUCTION_ID,
L_TEMP_INSTR_APPR_DEFN_REC.APPROVER_SEQ,
L_TEMP_INSTR_APPR_DEFN_REC.ROLE_COUNT,
L_TEMP_INSTR_APPR_DEFN_REC.ROLE_NAME,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
DELETE FROM GMO_INSTR_APPR_DEFN
WHERE INSTRUCTION_ID = L_INSTRUCTION_ID
AND APPROVER_SEQ NOT IN
( SELECT APPROVER_SEQ FROM GMO_INSTR_APPR_DEFN_T WHERE INSTRUCTION_ID = L_INSTRUCTION_ID
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID);
--Cleanup deleted records from instruction table
--Bug 5224619: start
DELETE FROM GMO_INSTR_DEFN_TL
WHERE INSTRUCTION_ID NOT IN
( SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_T WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID)
AND INSTRUCTION_ID IN (SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_B WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID)
AND LANGUAGE=USERENV('LANG');
DELETE FROM GMO_INSTR_DEFN_B
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
AND INSTRUCTION_ID NOT IN
( SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_T WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID);
--Cleanup deleted records from instruction set table
-- Count = number of instruction sets in temporary table
SELECT COUNT(*) INTO L_ENTITY_COUNT_T FROM GMO_INSTR_SET_DEFN_T
WHERE ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
SELECT COUNT(*) INTO L_ENTITY_COUNT_P FROM GMO_INSTR_SET_DEFN_B
WHERE ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
-- If exist in permanent but not in temp, delete from permanent
IF (L_ENTITY_COUNT_T < L_ENTITY_COUNT_P ) THEN
SELECT count(*) INTO L_DEL_INSTR_SET_ID
FROM GMO_INSTR_SET_DEFN_B
WHERE ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
SELECT INSTRUCTION_SET_ID INTO L_DEL_INSTR_SET_ID
FROM GMO_INSTR_SET_DEFN_B
WHERE ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
-- First delete the approvers
DELETE FROM GMO_INSTR_APPR_DEFN
WHERE INSTRUCTION_ID IN
(SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_B
WHERE INSTRUCTION_SET_ID = L_DEL_INSTR_SET_ID);
DELETE FROM GMO_INSTR_DEFN_TL WHERE INSTRUCTION_ID IN
(SELECT INSTRUCTION_ID FROM GMO_INSTR_DEFN_B WHERE INSTRUCTION_SET_ID = L_DEL_INSTR_SET_ID) AND LANGUAGE=USERENV('LANG');
DELETE FROM GMO_INSTR_DEFN_B
WHERE INSTRUCTION_SET_ID = L_DEL_INSTR_SET_ID;
DELETE FROM GMO_INSTR_SET_DEFN_TL
WHERE INSTRUCTION_SET_ID IN (SELECT INSTRUCTION_SET_ID FROM GMO_INSTR_SET_DEFN_B WHERE ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_TARGET_ENTITY_KEY AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE)
AND LANGUAGE=USERENV('LANG');
DELETE FROM GMO_INSTR_SET_DEFN_B
WHERE ENTITY_NAME = L_ENTITY_NAME
AND ENTITY_KEY = L_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = L_INSTRUCTION_TYPE;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT INSTRUCTION_SET_ID, INSTRUCTION_TYPE,
ENTITY_NAME, ENTITY_KEY, INSTR_SET_NAME,
INSTR_SET_DESC, ACKN_STATUS
FROM GMO_INSTR_SET_DEFN_VL
WHERE
ENTITY_NAME = P_DEFINITION_ENTITY_NAME
AND ENTITY_KEY = P_DEFINITION_ENTITY_KEY
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
AND ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS;
SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID, INSTRUCTION_TEXT,
INSTR_SEQ, INSTR_NUMBER, INSTR_ACKN_TYPE,
TASK_LABEL, TASK_ATTRIBUTE, TASK_ATTRIBUTE_ID, TASK_ID
FROM GMO_INSTR_DEFN_VL
WHERE
INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
SELECT INSTRUCTION_ID, APPROVER_SEQ, ROLE_COUNT, ROLE_NAME
FROM GMO_INSTR_APPR_DEFN
WHERE
INSTRUCTION_ID = L_INSTRUCTION_ID
ORDER BY APPROVER_SEQ;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
SELECT GMO_INSTR_SET_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_SET_ID
FROM DUAL;
SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE
ENTITY_NAME = P_INSTANCE_ENTITY_NAME
AND nvl(ENTITY_KEY,1) = nvl(P_INSTANCE_ENTITY_KEY,1)
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
SELECT GMO_INSTR_SET_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_SET_ID
FROM DUAL;
INSERT INTO GMO_INSTR_SET_INSTANCE_VL
(
INSTRUCTION_SET_ID,
INSTRUCTION_TYPE,
ENTITY_NAME,
ENTITY_KEY,
INSTR_SET_NAME,
INSTR_SET_DESC,
ACKN_STATUS,
INSTR_SET_STATUS,
ORIG_SOURCE,
ORIG_SOURCE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_SET_ID,
L_INSTR_SET_DEFN_REC.INSTRUCTION_TYPE,
P_INSTANCE_ENTITY_NAME, -- It must insert instance entity name and key
L_INSTANCE_ENTITY_KEY,
L_INSTR_SET_DEFN_REC.INSTR_SET_NAME,
L_INSTR_SET_DEFN_REC.INSTR_SET_DESC,
GMO_CONSTANTS_GRP.G_INSTR_SET_UNACKN_STATUS,
GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE,
GMO_CONSTANTS_GRP.G_ORIG_SOURCE_DEFN,
L_INSTR_SET_DEFN_REC.INSTRUCTION_SET_ID,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL
);
SELECT GMO_INSTR_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_ID
FROM DUAL;
select display_name into l_task_label from gmo_instr_task_defn_vl where task_id = L_INSTR_DEFN_REC.TASK_ID;
-- INSERT INSTRNS FROM DEFN TABLE TO INSTANCE TABLE
INSERT INTO GMO_INSTR_INSTANCE_VL
(
INSTRUCTION_ID,
INSTRUCTION_SET_ID,
INSTRUCTION_TEXT,
INSTR_STATUS,
COMMENTS,
TASK_LABEL,
INSTR_NUMBER,
INSTR_SEQ,
OPERATOR_ACKN,
INSTR_ACKN_TYPE,
TASK_ID,
TASK_ACKN_DATE,
TASK_ACKN_STATUS,
TASK_ATTRIBUTE,
TASK_ATTRIBUTE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_ID,
L_NEW_INSTRUCTION_SET_ID,
L_INSTR_DEFN_REC.INSTRUCTION_TEXT,
GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING,
NULL,
l_task_label,
L_INSTR_DEFN_REC.INSTR_NUMBER,
L_INSTR_DEFN_REC.INSTR_SEQ,
GMO_CONSTANTS_GRP.G_INSTR_OPERATOR_ACKN_NO,
L_INSTR_DEFN_REC.INSTR_ACKN_TYPE,
L_INSTR_DEFN_REC.TASK_ID,
NULL,
DECODE( L_INSTR_DEFN_REC.TASK_ID,
NULL,NULL,
GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS ),
--L_INSTR_DEFN_REC.TASK_ATTRIBUTE,
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')),
L_INSTR_DEFN_REC.TASK_ATTRIBUTE_ID,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL
);
INSERT INTO GMO_INSTR_APPR_INSTANCE
(
INSTRUCTION_ID,
APPROVER_SEQ,
ROLE_COUNT,
ROLE_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_ID,
GMO_INSTR_APPR_INSTANCE_S.NEXTVAL,
L_INSTR_APPR_DEFN_REC.ROLE_COUNT,
L_INSTR_APPR_DEFN_REC.ROLE_NAME,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT INSTRUCTION_SET_ID, INSTRUCTION_TYPE, ENTITY_NAME, ENTITY_KEY,
INSTR_SET_NAME, INSTR_SET_DESC, ACKN_STATUS, ORIG_SOURCE, ORIG_SOURCE_ID
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE INSTRUCTION_SET_ID = (SELECT MAX(INSTRUCTION_SET_ID)
FROM GMO_INSTR_SET_INSTANCE_B
WHERE ENTITY_NAME = P_SOURCE_ENTITY_NAME AND ENTITY_KEY = P_SOURCE_ENTITY_KEY AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE);
SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID, INSTRUCTION_TEXT, INSTR_STATUS,
COMMENTS, INSTR_NUMBER, INSTR_SEQ, OPERATOR_ACKN, INSTR_ACKN_TYPE, TASK_ID,
TASK_ACKN_DATE, TASK_ACKN_STATUS, TASK_LABEL, TASK_ATTRIBUTE, TASK_ATTRIBUTE_ID
FROM GMO_INSTR_INSTANCE_VL
WHERE
INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
SELECT INSTRUCTION_ID, APPROVER_SEQ, ROLE_COUNT, ROLE_NAME
FROM GMO_INSTR_APPR_INSTANCE
WHERE
INSTRUCTION_ID = L_INSTRUCTION_ID;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
SELECT INSTRUCTION_SET_ID, INSTR_SET_STATUS
INTO L_INSTRUCTION_SET_ID, L_INSTR_SET_STATUS
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE INSTRUCTION_SET_ID = (SELECT MAX(INSTRUCTION_SET_ID)
FROM GMO_INSTR_SET_INSTANCE_B
WHERE ENTITY_NAME = P_SOURCE_ENTITY_NAME AND ENTITY_KEY = P_SOURCE_ENTITY_KEY AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE);
SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE
ENTITY_NAME = P_SOURCE_ENTITY_NAME
AND ENTITY_KEY = P_TARGET_ENTITY_KEY
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
SELECT GMO_INSTR_SET_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_SET_ID
FROM DUAL;
INSERT INTO GMO_INSTR_SET_INSTANCE_VL
(
INSTRUCTION_SET_ID,
INSTRUCTION_TYPE,
ENTITY_NAME,
ENTITY_KEY,
INSTR_SET_NAME,
INSTR_SET_DESC,
ACKN_STATUS,
INSTR_SET_STATUS,
ORIG_SOURCE,
ORIG_SOURCE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_SET_ID,
L_INSTR_SET_REC.INSTRUCTION_TYPE,
P_SOURCE_ENTITY_NAME,
NVL(P_TARGET_ENTITY_KEY, GMO_CONSTANTS_GRP.G_INSTR_PREFIX || L_NEW_INSTRUCTION_SET_ID),
L_INSTR_SET_REC.INSTR_SET_NAME,
L_INSTR_SET_REC.INSTR_SET_DESC,
GMO_CONSTANTS_GRP.G_INSTR_SET_UNACKN_STATUS,
GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE,
GMO_CONSTANTS_GRP.G_ORIG_SOURCE_INSTANCE,
L_INSTR_SET_REC.INSTRUCTION_SET_ID,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL
);
SELECT GMO_INSTR_INSTANCE_S.NEXTVAL INTO L_NEW_INSTRUCTION_ID
FROM DUAL;
INSERT INTO GMO_INSTR_INSTANCE_VL
(
INSTRUCTION_ID,
INSTRUCTION_SET_ID,
INSTRUCTION_TEXT,
INSTR_STATUS,
COMMENTS,
TASK_LABEL,
INSTR_NUMBER,
INSTR_SEQ,
OPERATOR_ACKN,
INSTR_ACKN_TYPE,
TASK_ID,
TASK_ACKN_DATE,
TASK_ACKN_STATUS,
TASK_ATTRIBUTE,
TASK_ATTRIBUTE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_ID,
L_NEW_INSTRUCTION_SET_ID,
L_INSTR_REC.INSTRUCTION_TEXT,
GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING,
NULL,
L_INSTR_REC.TASK_LABEL,
L_INSTR_REC.INSTR_NUMBER,
L_INSTR_REC.INSTR_SEQ,
GMO_CONSTANTS_GRP.G_INSTR_OPERATOR_ACKN_NO,
L_INSTR_REC.INSTR_ACKN_TYPE,
L_INSTR_REC.TASK_ID,
NULL,
DECODE(L_INSTR_REC.TASK_ID,NULL,NULL,GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS),
L_INSTR_REC.TASK_ATTRIBUTE,
L_INSTR_REC.TASK_ATTRIBUTE_ID,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => GMO_CONSTANTS_GRP.NO,
X_from_category_id => NULL,
X_to_category_id => NULL
);
INSERT INTO GMO_INSTR_APPR_INSTANCE
(
INSTRUCTION_ID,
APPROVER_SEQ,
ROLE_COUNT,
ROLE_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_NEW_INSTRUCTION_ID,
GMO_INSTR_APPR_INSTANCE_S.NEXTVAL,
L_INSTR_APPR_REC.ROLE_COUNT,
L_INSTR_APPR_REC.ROLE_NAME,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
SELECT COUNT(INSTRUCTION_SET_ID) INTO L_INSTR_SET_COUNT
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE ENTITY_NAME = P_ENTITY_NAME
AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
X_MODE := GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_MODE_INSERT;
SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE ENTITY_NAME = P_ENTITY_NAME
AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
SELECT COUNT(INSTRUCTION_ID) INTO L_INSTR_COUNT
FROM GMO_INSTR_INSTANCE_VL WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
SELECT COUNT(INSTRUCTION_ID) INTO L_INSTR_COUNT
FROM GMO_INSTR_INSTANCE_VL WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
AND ( INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
OR INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE );
X_MODE := GMO_CONSTANTS_GRP.G_INSTR_INSTANCE_MODE_UPDATE;
SELECT INSTR_DEFN.INSTRUCTION_ID, INSTR_DEFN.INSTRUCTION_SET_ID,
INSTR_DEFN.INSTRUCTION_TEXT,
INSTR_DEFN.TASK_ATTRIBUTE, TSK.TASK_NAME, TSK.DISPLAY_NAME
FROM GMO_INSTR_DEFN_VL INSTR_DEFN, GMO_INSTR_TASK_DEFN_VL TSK
WHERE INSTR_DEFN.INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
AND INSTR_DEFN.TASK_ID = TSK.TASK_ID;
SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_DEFN_VL
WHERE
ENTITY_NAME = P_ENTITY_NAME
AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE ENTITY_NAME = P_ENTITY_NAME
AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
SELECT COUNT(INSTRUCTION_ID) INTO L_TOTAL_INSTRUCTIONS
FROM GMO_INSTR_INSTANCE_VL
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
SELECT COUNT(INSTRUCTION_ID) INTO L_OPTIONAL_PENDING_INSTR
FROM GMO_INSTR_INSTANCE_VL
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
AND INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
AND ( INSTR_ACKN_TYPE = L_INSTR_OPTIONAL );
SELECT COUNT(INSTRUCTION_ID) INTO L_MANDATORY_PENDING_INSTR
FROM GMO_INSTR_INSTANCE_VL
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
AND INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
AND ( INSTR_ACKN_TYPE = L_INSTR_MANDATORY OR INSTR_ACKN_TYPE = L_INSTR_PROCEED_ALLOWED);
SELECT COUNT(INSTRUCTION_ID) INTO L_TOTAL_INSTRUCTIONS
FROM GMO_INSTR_INSTANCE_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
SELECT COUNT(TEMP.INSTRUCTION_ID) INTO L_OPTIONAL_PENDING_INSTR
FROM GMO_INSTR_INSTANCE_T TEMP, GMO_INSTR_INSTANCE_VL PERM
WHERE TEMP.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND TEMP.INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
AND TEMP.INSTRUCTION_ID = PERM.INSTRUCTION_ID
AND ( PERM.INSTR_ACKN_TYPE = L_INSTR_OPTIONAL);
SELECT COUNT(TEMP.INSTRUCTION_ID) INTO L_MANDATORY_PENDING_INSTR
FROM GMO_INSTR_INSTANCE_VL PERM, GMO_INSTR_INSTANCE_T TEMP
WHERE TEMP.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND TEMP.INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
AND TEMP.INSTRUCTION_ID = PERM.INSTRUCTION_ID
AND ( PERM.INSTR_ACKN_TYPE = L_INSTR_MANDATORY OR PERM.INSTR_ACKN_TYPE = L_INSTR_PROCEED_ALLOWED);
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT INSTRUCTION_SET_ID, INSTRUCTION_PROCESS_ID, ACKN_STATUS
FROM GMO_INSTR_SET_INSTANCE_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
SELECT TEMP.INSTRUCTION_PROCESS_ID, TEMP.INSTRUCTION_ID, PERM.INSTRUCTION_SET_ID,
TEMP.COMMENTS, TEMP.OPERATOR_ACKN, TEMP.INSTR_STATUS,
TEMP.TASK_ACKN_STATUS, TEMP.TASK_ACKN_DATE, TEMP.DISABLE_TASK
FROM GMO_INSTR_INSTANCE_T TEMP, GMO_INSTR_INSTANCE_VL PERM
WHERE PERM.INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND PERM.INSTRUCTION_ID = TEMP.INSTRUCTION_ID FOR UPDATE;
SELECT INSTRUCTION_PROCESS_ID, INSTR_EREC_SEQ, INSTRUCTION_ID,
TASK_EREC_ID, INSTR_EREC_ID
FROM GMO_INSTR_EREC_INSTANCE_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_ID = L_INSTRUCTION_ID
ORDER BY INSTR_EREC_SEQ;
SELECT INSTRUCTION_PROCESS_ID, INSTR_TASK_SEQ, TASK_EREC_ID,
TASK_IDENTIFIER, TASK_VALUE, MANUAL_ENTRY, INSTRUCTION_ID
FROM GMO_INSTR_TASK_INSTANCE_T
WHERE
INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_ID = L_INSTRUCTION_ID
ORDER BY INSTR_TASK_SEQ;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_INSTANCE_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
SELECT ENTITY_KEY INTO L_ENTITY_KEY
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
UPDATE GMO_INSTR_SET_INSTANCE_B
SET ENTITY_KEY = P_ENTITY_KEY
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
UPDATE_ENTITY_KEY
(
P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
P_ENTITY_KEY => P_ENTITY_KEY
);
--update the instructions only when the set is active
select count(*) into l_set_active from gmo_instr_set_instance_vl
where instruction_set_id = L_INSTR_SET_REC.INSTRUCTION_SET_ID
and instr_set_status = GMO_CONSTANTS_GRP.G_PROCESS_ACTIVE;
UPDATE GMO_INSTR_SET_INSTANCE_B
SET
ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_SET_ACKN_STATUS,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE
INSTRUCTION_SET_ID = L_INSTR_SET_REC.INSTRUCTION_SET_ID;
UPDATE GMO_INSTR_INSTANCE_T
SET INSTR_STATUS = L_INSTR_STATUS
WHERE INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE
AND INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
UPDATE GMO_INSTR_INSTANCE_TL
SET
COMMENTS = L_INSTR_REC.COMMENTS,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE
INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
UPDATE GMO_INSTR_INSTANCE_B
SET
OPERATOR_ACKN = L_INSTR_REC.OPERATOR_ACKN,
TASK_ACKN_STATUS = L_INSTR_REC.TASK_ACKN_STATUS,
TASK_ACKN_DATE = L_INSTR_REC.TASK_ACKN_DATE,
INSTR_STATUS = L_INSTR_STATUS,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE
INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
DELETE FROM GMO_INSTR_EREC_INSTANCE
WHERE INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
INSERT INTO GMO_INSTR_EREC_INSTANCE
(
INSTRUCTION_ID,
INSTR_EREC_SEQ,
TASK_EREC_ID,
INSTR_EREC_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTR_EREC_REC.INSTRUCTION_ID,
L_INSTR_EREC_REC.INSTR_EREC_SEQ,
L_INSTR_EREC_REC.TASK_EREC_ID,
L_INSTR_EREC_REC.INSTR_EREC_ID,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
DELETE FROM GMO_INSTR_TASK_INSTANCE
WHERE INSTRUCTION_ID = L_INSTR_REC.INSTRUCTION_ID;
INSERT INTO GMO_INSTR_TASK_INSTANCE
(
INSTRUCTION_ID,
INSTR_TASK_SEQ,
TASK_EREC_ID,
TASK_IDENTIFIER,
TASK_VALUE,
MANUAL_ENTRY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_TASK_EREC_REC.INSTRUCTION_ID,
L_TASK_EREC_REC.INSTR_TASK_SEQ,
L_TASK_EREC_REC.TASK_EREC_ID,
L_TASK_EREC_REC.TASK_IDENTIFIER,
L_TASK_EREC_REC.TASK_VALUE,
L_TASK_EREC_REC.MANUAL_ENTRY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
SELECT COUNT(*) INTO L_INSTRUCTION_COUNT
FROM GMO_INSTR_INSTANCE_B
WHERE INSTR_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_COMPLETE
AND INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
UPDATE GMO_INSTR_SET_INSTANCE_B
SET INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_PROCESS_COMPLETE
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
SELECT COUNT(*) INTO L_PROCESS_COUNT
FROM GMO_INSTR_ATTRIBUTES_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_INSTANCE_STATUS
AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_ERROR;
SELECT INSTRN.INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM
GMO_INSTR_INSTANCE_T INSTR_TEMP,
GMO_INSTR_INSTANCE_VL INSTRN
WHERE
INSTR_TEMP.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTR_TEMP.INSTRUCTION_ID = P_INSTRUCTION_ID
AND INSTR_TEMP.INSTRUCTION_ID = INSTRN.INSTRUCTION_ID;
DELETE FROM GMO_INSTR_TASK_INSTANCE_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTRUCTION_ID = P_INSTRUCTION_ID
AND MANUAL_ENTRY = GMO_CONSTANTS_GRP.YES;
INSERT INTO GMO_INSTR_TASK_INSTANCE_T
(
INSTRUCTION_PROCESS_ID,
INSTRUCTION_ID,
INSTR_TASK_SEQ,
TASK_EREC_ID,
TASK_IDENTIFIER,
TASK_VALUE,
MANUAL_ENTRY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
P_INSTRUCTION_PROCESS_ID,
P_INSTRUCTION_ID,
GMO_INSTR_TASK_INSTANCE_S.NEXTVAL,
TO_NUMBER(P_TASK_ERECORD_ID(I),999999999999.999999),
P_TASK_IDENTIFIER(I),
P_TASK_VALUE(I),
P_MANUAL_ENTRY,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
UPDATE GMO_INSTR_INSTANCE_T
SET
DISABLE_TASK = P_DISABLE_TASK
WHERE
INSTRUCTION_ID = P_INSTRUCTION_ID
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
SELECT ENTITY_NAME, ENTITY_KEY
INTO L_ENTITY_NAME, L_ENTITY_KEY
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE
INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
UPDATE GMO_INSTR_SET_INSTANCE_B
SET ENTITY_KEY = P_ENTITY_KEY
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
UPDATE_ENTITY_KEY
(
P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
P_ENTITY_KEY => P_ENTITY_KEY
);
UPDATE GMO_INSTR_INSTANCE_T
SET TASK_ACKN_STATUS = GMO_CONSTANTS_GRP.G_INSTR_TASK_ACKN_STATUS,
TASK_ACKN_DATE = L_CREATION_DATE
WHERE INSTRUCTION_ID = P_INSTRUCTION_ID
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE ENTITY_NAME = P_ENTITY_NAME
AND ENTITY_KEY = P_ENTITY_KEY
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
UPDATE GMO_INSTR_SET_INSTANCE_B
SET
INSTR_SET_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL
WHERE
INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
SELECT INSTRUCTION_SET_ID INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_INSTANCE_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
UPDATE GMO_INSTR_INSTANCE_T
SET
INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_DONE,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE
INSTR_STATUS = GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING
AND
INSTRUCTION_ID IN (
SELECT INSTRUCTION_ID FROM GMO_INSTR_INSTANCE_B
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID
AND INSTR_ACKN_TYPE = GMO_CONSTANTS_GRP.G_INSTR_OPTIONAL
)
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
SELECT ATTRIBUTE_VALUE INTO X_DEFINITION_STATUS
FROM GMO_INSTR_ATTRIBUTES_T
WHERE ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL;
SELECT ATTRIBUTE_VALUE INTO X_INSTANCE_STATUS
FROM GMO_INSTR_ATTRIBUTES_T
WHERE ATTRIBUTE_NAME = 'INSTANCE_STATUS'
AND ATTRIBUTE_TYPE = 'INTERNAL'
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
UPDATE GMO_INSTR_INSTANCE_T
SET
OPERATOR_ACKN = P_OPERATOR_ACKN,
COMMENTS = P_INSTR_COMMENTS,
INSTR_STATUS = P_INSTR_STATUS,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE
INSTRUCTION_ID = P_INSTRUCTION_ID
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
PROCEDURE INSERT_ERECORD_DETAILS
(
P_INSTRUCTION_ID IN NUMBER,
P_INSTRUCTION_PROCESS_ID IN NUMBER,
P_INSTRUCTION_ERECORD_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS PRAGMA AUTONOMOUS_TRANSACTION;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
L_API_NAME := 'INSERT_ERECORD_DETAILS';
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_EREC_INSTANCE_T
(
INSTRUCTION_ID,
INSTRUCTION_PROCESS_ID,
INSTR_EREC_SEQ,
INSTR_EREC_ID,
TASK_EREC_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
P_INSTRUCTION_ID,
P_INSTRUCTION_PROCESS_ID,
GMO_INSTR_EREC_INSTANCE_S.NEXTVAL,
P_INSTRUCTION_ERECORD_ID,
NULL,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
END INSERT_ERECORD_DETAILS;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT INSTRUCTION_SET_ID, ACKN_STATUS
FROM GMO_INSTR_SET_INSTANCE_B
WHERE ENTITY_NAME = P_ENTITY_NAME
AND nvl(ENTITY_KEY,1) = nvl(P_ENTITY_KEY,1)
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE
AND INSTR_SET_STATUS <> GMO_CONSTANTS_GRP.G_INSTR_STATUS_CANCEL;
SELECT INSTRUCTION_ID, INSTRUCTION_SET_ID,
COMMENTS, OPERATOR_ACKN, INSTR_STATUS,
TASK_ACKN_STATUS, TASK_ID
FROM GMO_INSTR_INSTANCE_VL
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
SELECT INSTRUCTION_ID, INSTR_TASK_SEQ, TASK_EREC_ID,
TASK_IDENTIFIER, TASK_VALUE, MANUAL_ENTRY
FROM GMO_INSTR_TASK_INSTANCE
WHERE INSTRUCTION_ID = L_INSTRUCTION_ID;
SELECT INSTRUCTION_ID, INSTR_EREC_SEQ, INSTR_EREC_ID,
TASK_EREC_ID
FROM GMO_INSTR_EREC_INSTANCE
WHERE INSTRUCTION_ID = L_INSTRUCTION_ID;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_SET_INSTANCE_T
(
INSTRUCTION_PROCESS_ID,
INSTRUCTION_SET_ID,
ACKN_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
P_INSTRUCTION_PROCESS_ID,
L_INSTR_SET_REC.INSTRUCTION_SET_ID,
L_INSTR_SET_REC.ACKN_STATUS,
L_CREATION_DATE, -- FIGURE THIS OUT
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_INSTANCE_T
(
INSTRUCTION_PROCESS_ID,
INSTRUCTION_ID,
COMMENTS,
OPERATOR_ACKN,
INSTR_STATUS,
TASK_ACKN_STATUS,
TASK_ID,
DISABLE_TASK,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
P_INSTRUCTION_PROCESS_ID,
L_INSTR_REC.INSTRUCTION_ID,
L_INSTR_REC.COMMENTS,
L_INSTR_REC.OPERATOR_ACKN,
L_INSTR_REC.INSTR_STATUS,
DECODE(L_INSTR_REC.TASK_ID,
NULL,NULL,
GMO_CONSTANTS_GRP.G_INSTR_TASK_UNACKN_STATUS),
L_INSTR_REC.TASK_ID,
DECODE(L_INSTR_REC.TASK_ID,NULL,NULL,GMO_CONSTANTS_GRP.NO),
L_CREATION_DATE, -- FIGURE THIS OUT
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_TASK_INSTANCE_T
(
INSTRUCTION_ID,
INSTRUCTION_PROCESS_ID,
INSTR_TASK_SEQ,
TASK_EREC_ID,
TASK_IDENTIFIER,
TASK_VALUE,
MANUAL_ENTRY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTR_TASK_REC.INSTRUCTION_ID,
P_INSTRUCTION_PROCESS_ID,
L_INSTR_TASK_REC.INSTR_TASK_SEQ,
L_INSTR_TASK_REC.TASK_EREC_ID,
L_INSTR_TASK_REC.TASK_IDENTIFIER,
L_INSTR_TASK_REC.TASK_VALUE,
L_INSTR_TASK_REC.MANUAL_ENTRY,
L_CREATION_DATE, -- FIGURE THIS OUT
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_EREC_INSTANCE_T
(
INSTRUCTION_ID,
INSTRUCTION_PROCESS_ID,
INSTR_EREC_SEQ,
INSTR_EREC_ID,
TASK_EREC_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_ID,
P_INSTRUCTION_PROCESS_ID,
L_INSTR_EREC_REC.INSTR_EREC_SEQ,
L_INSTR_EREC_REC.INSTR_EREC_ID,
L_INSTR_EREC_REC.TASK_EREC_ID,
L_CREATION_DATE, -- FIGURE THIS OUT
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT GMO_INSTR_PROCESS_ID_S.NEXTVAL INTO L_INSTRUCTION_PROCESS_ID
FROM DUAL;
SELECT MAX(INSTRUCTION_SET_ID) INTO L_INSTRUCTION_SET_ID
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE ENTITY_NAME = P_ENTITY_NAME
AND ENTITY_KEY = P_ENTITY_KEY
AND INSTRUCTION_TYPE = P_INSTRUCTION_TYPE;
SELECT INSTR_SET_STATUS INTO L_INSTR_SET_STATUS
FROM GMO_INSTR_SET_INSTANCE_VL
WHERE INSTRUCTION_SET_ID = L_INSTRUCTION_SET_ID;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ENTITY_NAME,
ENTITY_KEY,
INSTRUCTION_TYPE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
GMO_CONSTANTS_GRP.G_PARAM_ENTITY,
P_ENTITY_NAME,
P_ENTITY_KEY,
P_INSTRUCTION_TYPE,
GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
GMO_CONSTANTS_GRP.G_INSTANCE_STATUS,
GMO_CONSTANTS_GRP.G_PROCESS_ERROR,
GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
L_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
P_CONTEXT_PARAM_NAME(i),
P_CONTEXT_PARAM_VALUE(i),
GMO_CONSTANTS_GRP.G_PARAM_INTERNAL,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
SELECT INSTRUCTION_TYPE, ENTITY_NAME, ENTITY_KEY
INTO L_INSTRUCTION_TYPE, L_ENTITY_NAME, L_ENTITY_KEY
FROM GMO_INSTR_SET_INSTANCE_VL WHERE
INSTRUCTION_SET_ID = P_INSTRUCTION_SET_ID;
PROCEDURE UPDATE_INSTR_ATTRIBUTES(P_INSTRUCTION_PROCESS_ID IN VARCHAR2,
P_UPDATE_DEFN_STATUS IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
L_API_NAME := 'UPDATE_INSTR_ATTRIBUTES';
IF(P_UPDATE_DEFN_STATUS = FND_API.G_TRUE) THEN
UPDATE
GMO_INSTR_ATTRIBUTES_T
SET
ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
WHERE
INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND
ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
UPDATE
GMO_INSTR_ATTRIBUTES_T
SET
ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
WHERE
INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND
ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
'gmo.plsql.GMO_INSTRUCTION_PVT.UPDATE_INSTR_ATTRIBUTES',
FALSE
);
END UPDATE_INSTR_ATTRIBUTES;
P_UPDATE_DEFN_STATUS IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
IF(P_UPDATE_DEFN_STATUS = FND_API.G_TRUE) THEN
UPDATE
GMO_INSTR_ATTRIBUTES_T
SET
ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_STATUS_MODIFIED
WHERE
INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND
ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_DEFINITION_STATUS;
UPDATE
GMO_INSTR_ATTRIBUTES_T
SET
ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS
WHERE
INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND
ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
PROCEDURE DELETE_INSTR_SET_DETAILS
(
P_INSTRUCTION_PROCESS_ID IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE
GMO_INSTR_ATTRIBUTES_T
SET
ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_CANCEL
WHERE
INSTRUCTION_PROCESS_ID = L_INSTRUCTION_PROCESS_ID
AND
ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','DELETE_INSTR_SET_DETAILS');
'gmo.plsql.GMO_INSTRUCTION_PVT.DELETE_INSTR_SET_DETAILS',
FALSE
);
END DELETE_INSTR_SET_DETAILS;
SELECT ATTRIBUTE_VALUE INTO L_ATTRIBUTE_VALUE
FROM GMO_INSTR_ATTRIBUTES_T
WHERE ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
FUNCTION INSERT_PROCESS_VARIABLE
(
P_INSTRUCTION_PROCESS_ID IN NUMBER ,
P_ATTRIBUTE_NAME IN VARCHAR2 ,
P_ATTRIBUTE_VALUE IN VARCHAR2,
P_ATTRIBUTE_TYPE IN VARCHAR2 DEFAULT GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
SELECT COUNT(*) INTO L_COUNT FROM GMO_INSTR_ATTRIBUTES_T
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
UPDATE GMO_INSTR_ATTRIBUTES_T
SET ATTRIBUTE_VALUE = P_ATTRIBUTE_VALUE,
LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
LAST_UPDATED_BY = L_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE;
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
P_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
P_ATTRIBUTE_NAME,
P_ATTRIBUTE_VALUE,
P_ATTRIBUTE_TYPE,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
END INSERT_PROCESS_VARIABLE;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
UPDATE GMO_INSTR_ATTRIBUTES_T
SET ATTRIBUTE_VALUE = P_ATTRIBUTE_VALUE
WHERE ATTRIBUTE_NAME = P_ATTRIBUTE_NAME
AND ATTRIBUTE_TYPE = P_ATTRIBUTE_TYPE
AND INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
INSERT INTO GMO_INSTR_ATTRIBUTES_T
(
INSTRUCTION_PROCESS_ID,
ATTRIBUTE_SEQ,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE,
ATTRIBUTE_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
P_INSTRUCTION_PROCESS_ID,
GMO_INSTR_ATTRIBUTES_T_S.NEXTVAL,
P_ATTRIBUTE_NAME,
P_ATTRIBUTE_VALUE,
P_ATTRIBUTE_TYPE,
L_CREATION_DATE,
L_CREATED_BY,
L_LAST_UPDATE_DATE,
L_LAST_UPDATED_BY,
L_LAST_UPDATE_LOGIN
);
SELECT XMLELEMENT("INSTRUCTIONS",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
INSTR_SET.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
INSTR_SET.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
(SELECT
LK.MEANING
FROM
FND_LOOKUP_VALUES_VL LK
WHERE
LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SET.ENTITY_NAME
AND
LK.LOOKUP_CODE = INSTR_SET.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
(SELECT
XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",
XMLFOREST(INSTR.INSTR_NUMBER AS INSTRUCTION_NUMBER,
INSTR.INSTRUCTION_TEXT AS INSTRUCTION_TEXT,
(SELECT
LK1.MEANING
FROM
FND_LOOKUP_VALUES_VL LK1
WHERE
LK1.LOOKUP_TYPE = 'GMO_INSTR_ACKN_TYPES'
AND
LK1.LOOKUP_CODE = INSTR.INSTR_ACKN_TYPE) AS INSTRUCTION_ACKN_TYPE,
(SELECT
TK.DISPLAY_NAME
FROM
GMO_INSTR_TASK_DEFN_VL TK
WHERE
TK.TASK_ID = INSTR.TASK_ID) AS TASK_NAME,
INSTR.TASK_ATTRIBUTE AS TASK_ATTRIBUTE,
INSTR.TASK_LABEL AS TASK_LABEL,
(SELECT
DECODE((SELECT COUNT(*) FROM GMO_INSTR_APPR_DEFN_T APPR
WHERE
APPR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID
AND APPR.INSTRUCTION_ID = INSTR.INSTRUCTION_ID),0,
FND_MESSAGE.GET_STRING('GMO','GMO_INSTR_SIG_NOT_REQUIRED'),
FND_MESSAGE.GET_STRING('GMO','GMO_INSTR_SIG_REQUIRED'))
FROM DUAL)
AS SIGNATURE_REQUIRED
)
)
)
FROM GMO_INSTR_DEFN_T INSTR
WHERE INSTR.INSTRUCTION_SET_ID = INSTR_SET.INSTRUCTION_SET_ID
AND INSTR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID) AS "RELATED_INSTRUCTIONS")
)
)
)
INTO L_INSTR_XML
FROM GMO_INSTR_SET_DEFN_T INSTR_SET
WHERE INSTR_SET.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
select instruction_id, task_id, task_attribute_id,
task_attribute, instr_ackn_type,
instruction_text, task_label
from gmo_instr_defn_vl
where instruction_id = l_instruction_id;
select approver_seq, role_name, role_count
from gmo_instr_appr_defn
where instruction_id = l_instruction_id;
select count(*) from gmo_instr_set_defn_t
where instruction_set_id = P_INSTRUCTION_SET_ID
and instruction_process_id = P_INSTRUCTION_PROCESS_ID;
select count(*) from gmo_instr_defn_t where
instruction_process_id = p_instruction_process_id
AND instruction_set_id = p_instruction_set_id
AND task_id =l_task_id;
X_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => L_LAST_UPDATE_LOGIN
);
select instr_seq into l_instr_seq
from gmo_instr_defn_t
where instruction_set_id = P_INSTRUCTION_SET_ID
and instruction_process_id = P_INSTRUCTION_PROCESS_ID
and instruction_id = P_INSTRUCTION_ID;
update gmo_instr_defn_t
set instr_seq = instr_seq + l_count
where instruction_set_id = P_INSTRUCTION_SET_ID
and instruction_process_id = P_INSTRUCTION_PROCESS_ID
and instr_seq >= l_working_instr_seq;
select nvl(max(instr_seq), 0) into l_working_instr_seq
from gmo_instr_defn_t
where instruction_set_id = P_INSTRUCTION_SET_ID
and instruction_process_id = P_INSTRUCTION_PROCESS_ID;
select max_allowed_task into l_maximum_allowed_task from GMO_INSTR_TASK_DEFN_VL
where task_id = l_task_id;
select gmo_instr_defn_s.nextval into l_working_instruction_id from dual;
insert into gmo_instr_defn_t
(instruction_id,
instruction_process_id,
instruction_text,
instruction_set_id,
instr_seq,
task_id,
task_attribute_id,
task_attribute,
instr_ackn_type,
instr_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
task_label)
values
(
l_working_instruction_id,
p_instruction_process_id,
l_instr_text,
p_instruction_set_id,
l_working_instr_seq,
l_task_id,
l_task_attribute_id,
l_task_attribute,
l_instr_ackn_type,
P_INSTRUCTION_NOS(i),
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_task_label
);
X_last_update_login => L_LAST_UPDATE_LOGIN,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL,
X_automatically_added_flag => 'N',
X_from_category_id => NULL,
X_to_category_id => NULL
);
select gmo_instr_appr_defn_s.nextval
into l_approver_seq from dual;
insert into
gmo_instr_appr_defn_t
(instruction_id, instruction_process_id,
approver_seq, role_name, role_count, creation_date,
created_by, last_update_date, last_updated_by, last_update_login)
values (l_working_instruction_id, p_instruction_process_id,
l_approver_seq, l_role_name, l_appr_count, l_creation_date,
l_created_by, l_last_update_date, l_last_updated_by, l_last_update_login);
SELECT XMLELEMENT("INSTRUCTION_SET",XMLAGG(XMLELEMENT("INSTRUCTION_SET_DETAILS",
XMLFOREST(INSTR_SET.INSTRUCTION_PROCESS_ID AS INSTRUCTION_PROCESS_ID,
INSTR_SETVL.INSTR_SET_NAME AS INSTRUCTION_SET_NAME,
INSTR_SETVL.INSTR_SET_DESC AS INSTRUCTION_SET_DESC,
(SELECT
LK.MEANING
FROM
FND_LOOKUP_VALUES_VL LK
WHERE
LK.LOOKUP_TYPE = 'GMO_INSTR_'||INSTR_SETVL.ENTITY_NAME
AND
LK.LOOKUP_CODE = INSTR_SETVL.INSTRUCTION_TYPE) AS INSTRUCTION_TYPE,
(SELECT
XMLAGG(XMLELEMENT("INSTRUCTION_DETAILS",XMLFOREST(INSTRVL.INSTRUCTION_TEXT,
(SELECT
MEANING
FROM
FND_LOOKUP_VALUES_VL LK1
WHERE
LK1.LOOKUP_TYPE = 'GMO_INSTR_STATUS_TYPES'
AND
LK1.LOOKUP_CODE = INSTR.INSTR_STATUS) AS INSTRUCTION_STATUS,
(SELECT
MEANING
FROM
FND_LOOKUP_VALUES_VL LK2
WHERE
LK2.LOOKUP_TYPE = 'GMO_INSTR_ACKN_TYPES'
AND LK2.LOOKUP_CODE = INSTRVL.INSTR_ACKN_TYPE)
AS INSTRUCTION_ACKN_TYPE,
decode(INSTR.INSTR_STATUS,GMO_CONSTANTS_GRP.G_INSTR_STATUS_PENDING,
NULL,
GMO_UTILITIES.GET_USER_DISPLAY_NAME(INSTR.LAST_UPDATED_BY))
AS PERFORMED_BY,
INSTR.LAST_UPDATE_DATE LAST_UPDATE_DATE,
INSTR.COMMENTS COMMENTS,
(SELECT
MAX(EREC.INSTR_EREC_ID)
FROM
GMO_INSTR_EREC_INSTANCE EREC
WHERE
EREC.INSTRUCTION_ID = INSTR.INSTRUCTION_ID)
AS INSTRUCTION_ERECORD_ID,
(SELECT XMLAGG(XMLELEMENT("TASK_DETAILS",XMLFOREST(TK.TASK_EREC_ID AS TASK_ERECORD_ID,
TK.TASK_IDENTIFIER AS TASK_IDENTIFIER,
TK.TASK_VALUE AS TASK_VALUE)))
FROM
GMO_INSTR_TASK_INSTANCE TK
WHERE TK.INSTRUCTION_ID = INSTR.INSTRUCTION_ID) AS TASKS
)
)
)
FROM GMO_INSTR_INSTANCE_T INSTR,
GMO_INSTR_INSTANCE_VL INSTRVL
WHERE INSTR.INSTRUCTION_PROCESS_ID = INSTR_SET.INSTRUCTION_PROCESS_ID
AND INSTR.INSTRUCTION_ID = INSTRVL.INSTRUCTION_ID) AS "INSTRUCTIONS")
)
)
)
INTO L_INSTR_XML
FROM GMO_INSTR_SET_INSTANCE_T INSTR_SET,
GMO_INSTR_SET_INSTANCE_VL INSTR_SETVL
WHERE INSTR_SET.INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND INSTR_SETVL.INSTRUCTION_SET_ID = INSTR_SET.INSTRUCTION_SET_ID;
UPDATE GMO_INSTR_ATTRIBUTES_T
SET ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_TERMINATE
WHERE INSTRUCTION_PROCESS_ID = P_INSTRUCTION_PROCESS_ID
AND ATTRIBUTE_TYPE = GMO_CONSTANTS_GRP.G_PARAM_INTERNAL
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS;
SELECT COUNT(*) into l_valid_process FROM GMO_INSTR_ATTRIBUTES_T
WHERE INSTRUCTION_PROCESS_ID = p_instruction_process_id
AND ATTRIBUTE_NAME = GMO_CONSTANTS_GRP.G_PROCESS_STATUS
AND ATTRIBUTE_VALUE = GMO_CONSTANTS_GRP.G_PROCESS_SUCCESS;
select count(*) into l_count
from gmo_instr_defn_b
where task_attribute_id like l_check_attribute;
select count(*) into l_count
from gmo_instr_defn_t
where instruction_process_id = l_instruction_process_id
and task_attribute_id like l_check_attribute;
select count(*) into l_count
from gmo_instr_defn_b
where task_attribute_id like l_check_attribute
and instruction_set_id not in (select instruction_set_id from gmo_instr_set_defn_t where instruction_process_id = l_instruction_process_id);