The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR c IS SELECT ROWID
FROM JTF_BRM_RULES_B
WHERE RULE_ID = l_rule_id;
SELECT JTF_BRM_RULES_S.NEXTVAL INTO l_rule_id
FROM DUAL;
INSERT INTO JTF_BRM_RULES_B
( RULE_ID
, BRM_OBJECT_TYPE
, BRM_OBJECT_CODE
, SEEDED_FLAG
, VIEW_DEFINITION
, VIEW_NAME
, RULE_OWNER
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE_CATEGORY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, OBJECT_VERSION_NUMBER
, APPLICATION_ID
) VALUES
( l_rule_id
, p_br_rec.BRM_OBJECT_TYPE
, p_br_rec.BRM_OBJECT_CODE
, p_br_rec.SEEDED_FLAG
, p_br_rec.VIEW_DEFINITION
, p_br_rec.VIEW_NAME
, p_br_rec.RULE_OWNER
, p_br_rec.START_DATE_ACTIVE
, p_br_rec.END_DATE_ACTIVE
, p_br_rec.ATTRIBUTE1
, p_br_rec.ATTRIBUTE2
, p_br_rec.ATTRIBUTE3
, p_br_rec.ATTRIBUTE4
, p_br_rec.ATTRIBUTE5
, p_br_rec.ATTRIBUTE6
, p_br_rec.ATTRIBUTE7
, p_br_rec.ATTRIBUTE8
, p_br_rec.ATTRIBUTE9
, p_br_rec.ATTRIBUTE10
, p_br_rec.ATTRIBUTE11
, p_br_rec.ATTRIBUTE12
, p_br_rec.ATTRIBUTE13
, p_br_rec.ATTRIBUTE14
, p_br_rec.ATTRIBUTE15
, p_br_rec.ATTRIBUTE_CATEGORY
, p_br_rec.CREATION_DATE
, p_br_rec.CREATED_BY
, p_br_rec.LAST_UPDATE_DATE
, p_br_rec.LAST_UPDATED_BY
, p_br_rec.LAST_UPDATE_LOGIN
, l_object_version_number
, p_br_rec.APPLICATION_ID
);
INSERT INTO JTF_BRM_RULES_TL
( RULE_ID
, RULE_NAME
, RULE_DESCRIPTION
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, LANGUAGE
, SOURCE_LANG
, APPLICATION_ID
) SELECT l_rule_id
, p_br_rec.RULE_NAME
, p_br_rec.RULE_DESCRIPTION
, p_br_rec.CREATED_BY
, p_br_rec.CREATION_DATE
, p_br_rec.LAST_UPDATED_BY
, p_br_rec.LAST_UPDATE_DATE
, p_br_rec.LAST_UPDATE_LOGIN
, l.LANGUAGE_CODE
, userenv('LANG')
, p_br_rec.APPLICATION_ID
FROM FND_LANGUAGES l
WHERE l.INSTALLED_FLAG IN ('I','B')
AND NOT EXISTS ( SELECT NULL
FROM JTF_BRM_RULES_TL t
WHERE t.RULE_ID = l_rule_id
AND t.LANGUAGE = l.LANGUAGE_CODE);
PROCEDURE Update_BRMRule
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
, p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
, p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_br_rec IN BRM_Rule_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_BRMRule';
SAVEPOINT Update_BRMRule_PVT;
SELECT JTF_BRM_OBJECT_VERSION_S.NEXTVAL
INTO l_object_version_number
FROM DUAL;
UPDATE JTF_BRM_RULES_B
SET BRM_OBJECT_TYPE = p_br_rec.BRM_OBJECT_TYPE
, BRM_OBJECT_CODE = p_br_rec.BRM_OBJECT_CODE
, SEEDED_FLAG = p_br_rec.SEEDED_FLAG
, VIEW_DEFINITION = p_br_rec.VIEW_DEFINITION
, VIEW_NAME = p_br_rec.VIEW_NAME
, RULE_OWNER = p_br_rec.RULE_OWNER
, START_DATE_ACTIVE = p_br_rec.START_DATE_ACTIVE
, END_DATE_ACTIVE = p_br_rec.END_DATE_ACTIVE
, ATTRIBUTE1 = p_br_rec.ATTRIBUTE1
, ATTRIBUTE2 = p_br_rec.ATTRIBUTE2
, ATTRIBUTE3 = p_br_rec.ATTRIBUTE3
, ATTRIBUTE4 = p_br_rec.ATTRIBUTE4
, ATTRIBUTE5 = p_br_rec.ATTRIBUTE5
, ATTRIBUTE6 = p_br_rec.ATTRIBUTE6
, ATTRIBUTE7 = p_br_rec.ATTRIBUTE7
, ATTRIBUTE8 = p_br_rec.ATTRIBUTE8
, ATTRIBUTE9 = p_br_rec.ATTRIBUTE9
, ATTRIBUTE10 = p_br_rec.ATTRIBUTE10
, ATTRIBUTE11 = p_br_rec.ATTRIBUTE11
, ATTRIBUTE12 = p_br_rec.ATTRIBUTE12
, ATTRIBUTE13 = p_br_rec.ATTRIBUTE13
, ATTRIBUTE14 = p_br_rec.ATTRIBUTE14
, ATTRIBUTE15 = p_br_rec.ATTRIBUTE15
, ATTRIBUTE_CATEGORY = p_br_rec.ATTRIBUTE_CATEGORY
, LAST_UPDATE_DATE = p_br_rec.LAST_UPDATE_DATE
, LAST_UPDATED_BY = p_br_rec.LAST_UPDATED_BY
, LAST_UPDATE_LOGIN = p_br_rec.LAST_UPDATE_LOGIN
, OBJECT_VERSION_NUMBER = l_object_version_number
, APPLICATION_ID = p_br_rec.APPLICATION_ID
WHERE RULE_ID = p_br_rec.RULE_ID;
UPDATE JTF_BRM_RULES_TL
SET RULE_NAME = p_br_rec.RULE_NAME
, RULE_DESCRIPTION = p_br_rec.RULE_DESCRIPTION
, LAST_UPDATE_DATE = p_br_rec.LAST_UPDATE_DATE
, LAST_UPDATED_BY = p_br_rec.LAST_UPDATED_BY
, LAST_UPDATE_LOGIN = p_br_rec.LAST_UPDATE_LOGIN
, SOURCE_LANG = userenv('LANG')
, APPLICATION_ID = p_br_rec.APPLICATION_ID
WHERE RULE_ID = p_br_rec.RULE_ID
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
ROLLBACK TO Update_BRMRule_PVT;
ROLLBACK TO Update_BRMRule_PVT;
ROLLBACK TO Update_BRMRule_PVT;
END Update_BRMRule;
PROCEDURE Delete_BRMRule
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
, p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
, p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_rule_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_BRMRule';
SAVEPOINT Delete_BRMRule_PVT;
DELETE FROM JTF_BRM_RULES_TL
WHERE RULE_ID = p_rule_id;
DELETE FROM JTF_BRM_RULES_B
WHERE RULE_ID = p_rule_id;
ROLLBACK TO Delete_BRMRule_PVT;
ROLLBACK TO Delete_BRMRule_PVT;
ROLLBACK TO Delete_BRMRule_PVT;
END Delete_BRMRule;
DELETE FROM JTF_BRM_RULES_TL t
WHERE NOT EXISTS (SELECT NULL
FROM JTF_BRM_RULES_B b
WHERE b.RULE_ID = t.RULE_ID
);
UPDATE JTF_BRM_RULES_TL T
SET ( RULE_NAME
, RULE_DESCRIPTION
) = ( SELECT B.RULE_NAME
, B.RULE_DESCRIPTION
FROM JTF_BRM_RULES_TL B
WHERE B.RULE_ID = T.RULE_ID
AND B.LANGUAGE = T.SOURCE_LANG
)
WHERE ( T.RULE_ID
, T.LANGUAGE
) IN ( SELECT SUBT.RULE_ID
, SUBT.LANGUAGE
FROM JTF_BRM_RULES_TL SUBB
, JTF_BRM_RULES_TL SUBT
WHERE SUBB.RULE_ID = SUBT.RULE_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND ( SUBB.RULE_NAME <> SUBT.RULE_NAME
OR SUBB.RULE_DESCRIPTION <> SUBT.RULE_DESCRIPTION
OR ( SUBB.RULE_DESCRIPTION IS NULL
AND SUBT.RULE_DESCRIPTION IS NOT NULL
)
OR ( SUBB.RULE_DESCRIPTION IS NOT NULL
AND SUBT.RULE_DESCRIPTION IS NULL
)
)
);
INSERT INTO JTF_BRM_RULES_TL
( RULE_ID
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, RULE_NAME
, RULE_DESCRIPTION
, LANGUAGE
, SOURCE_LANG
, APPLICATION_ID
) SELECT B.RULE_ID
, B.CREATED_BY
, B.CREATION_DATE
, B.LAST_UPDATED_BY
, B.LAST_UPDATE_DATE
, B.LAST_UPDATE_LOGIN
, B.RULE_NAME
, B.RULE_DESCRIPTION
, L.LANGUAGE_CODE
, B.SOURCE_LANG
, B.APPLICATION_ID
FROM JTF_BRM_RULES_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (SELECT NULL
FROM JTF_BRM_RULES_TL T
WHERE T.RULE_ID = B.RULE_ID
AND T.LANGUAGE = L.LANGUAGE_CODE
);
UPDATE JTF_BRM_RULES_TL
SET RULE_NAME = p_rule_name
, RULE_DESCRIPTION = p_rule_description
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = DECODE(p_owner, 'SEED',1,0)
, LAST_UPDATE_LOGIN = 0
, SOURCE_LANG = userenv('LANG')
WHERE userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
AND RULE_ID = p_rule_id;
UPDATE JTF_BRM_RULES_B
SET BRM_OBJECT_TYPE = p_br_rec.brm_object_type
, BRM_OBJECT_CODE = p_br_rec.brm_object_code
, SEEDED_FLAG = p_br_rec.seeded_flag
, VIEW_DEFINITION = P_br_rec.view_definition
, VIEW_NAME = p_br_rec.view_name
, RULE_OWNER = p_br_rec.rule_owner
, START_DATE_ACTIVE = p_br_rec.start_date_active
, END_DATE_ACTIVE = p_br_rec.end_date_active
, ATTRIBUTE1 = p_br_rec.attribute1
, ATTRIBUTE2 = p_br_rec.attribute2
, ATTRIBUTE3 = p_br_rec.attribute3
, ATTRIBUTE4 = p_br_rec.attribute4
, ATTRIBUTE5 = p_br_rec.attribute5
, ATTRIBUTE6 = p_br_rec.attribute6
, ATTRIBUTE7 = p_br_rec.attribute7
, ATTRIBUTE8 = p_br_rec.attribute8
, ATTRIBUTE9 = p_br_rec.attribute9
, ATTRIBUTE10 = p_br_rec.attribute10
, ATTRIBUTE11 = p_br_rec.attribute11
, ATTRIBUTE12 = p_br_rec.attribute12
, ATTRIBUTE13 = p_br_rec.attribute13
, ATTRIBUTE14 = p_br_rec.attribute14
, ATTRIBUTE15 = p_br_rec.attribute15
, ATTRIBUTE_CATEGORY = p_br_rec.attribute_category
, OBJECT_VERSION_NUMBER = p_br_rec.object_version_number
, APPLICATION_ID = p_br_rec.application_id
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_LOGIN = 0
WHERE RULE_ID = p_rule_id;
INSERT INTO JTF_BRM_RULES_B
( RULE_ID
, BRM_OBJECT_TYPE
, BRM_OBJECT_CODE
, SEEDED_FLAG
, VIEW_DEFINITION
, VIEW_NAME
, RULE_OWNER
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE_CATEGORY
, OBJECT_VERSION_NUMBER
, APPLICATION_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
) VALUES
( p_rule_id
, p_br_rec.BRM_OBJECT_TYPE
, p_br_rec.BRM_OBJECT_CODE
, p_br_rec.SEEDED_FLAG
, p_br_rec.VIEW_DEFINITION
, p_br_rec.VIEW_NAME
, p_br_rec.RULE_OWNER
, p_br_rec.START_DATE_ACTIVE
, p_br_rec.END_DATE_ACTIVE
, p_br_rec.ATTRIBUTE1
, p_br_rec.ATTRIBUTE2
, p_br_rec.ATTRIBUTE3
, p_br_rec.ATTRIBUTE4
, p_br_rec.ATTRIBUTE5
, p_br_rec.ATTRIBUTE6
, p_br_rec.ATTRIBUTE7
, p_br_rec.ATTRIBUTE8
, p_br_rec.ATTRIBUTE9
, p_br_rec.ATTRIBUTE10
, p_br_rec.ATTRIBUTE11
, p_br_rec.ATTRIBUTE12
, p_br_rec.ATTRIBUTE13
, p_br_rec.ATTRIBUTE14
, p_br_rec.ATTRIBUTE15
, p_br_rec.ATTRIBUTE_CATEGORY
, p_br_rec.object_version_number
, p_br_rec.application_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, 0
);
UPDATE JTF_BRM_RULES_TL
SET RULE_NAME = p_br_rec.rule_name
, RULE_DESCRIPTION = p_br_rec.rule_description
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_LOGIN = 0
, SOURCE_LANG = userenv('LANG')
WHERE RULE_ID = p_rule_id
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
INSERT INTO JTF_BRM_RULES_TL
( RULE_ID
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, RULE_NAME
, RULE_DESCRIPTION
, LANGUAGE
, SOURCE_LANG
, APPLICATION_ID
) SELECT p_rule_id
, l_user_id
, SYSDATE
, l_user_id
, SYSDATE
, 0
, p_br_rec.rule_name
, p_br_rec.rule_description
, l.LANGUAGE_CODE
, userenv('LANG')
, p_br_rec.application_id
FROM FND_LANGUAGES l
WHERE l.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS( SELECT NULL
FROM JTF_BRM_RULES_TL t
WHERE t.RULE_ID = p_rule_id
AND t.LANGUAGE = l.LANGUAGE_CODE
);