The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
PROCEDURE delete_mrv_uda_data(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2,p_major_version IN NUMBER);
x_last_updated_by OUT NOCOPY NUMBER,
x_last_update_login OUT NOCOPY NUMBER,
x_last_update_date OUT NOCOPY DATE
) RETURN VARCHAR2 IS
CURSOR OKC_K_ART_VARIABLES_pk_csr (cp_cat_id IN NUMBER,cp_variable_code IN VARCHAR2) IS
SELECT
VARIABLE_TYPE,
EXTERNAL_YN,
VARIABLE_VALUE_ID,
VARIABLE_VALUE,
ATTRIBUTE_VALUE_SET_ID,
OVERRiDE_GLOBAL_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
FROM OKC_K_ART_VARIABLES t
WHERE t.CAT_ID = cp_cat_id and
t.VARIABLE_CODE = cp_variable_code;
x_last_updated_by,
x_last_update_login,
x_last_update_date;
l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login,
x_last_update_date => l_last_update_date
);
SELECT '!'
FROM OKC_K_ARTICLES_B
WHERE ID = p_cat_id;
SELECT '!'
FROM fnd_flex_value_sets
WHERE FLEX_VALUE_SET_ID = p_attribute_value_set_id;
SELECT '!'
FROM OKC_BUS_VARIABLES_B
WHERE VARIABLE_CODE = p_VARIABLE_CODE;
l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
FUNCTION Insert_Row(
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2,
p_variable_type IN VARCHAR2,
p_external_yn IN VARCHAR2,
p_variable_value_id IN VARCHAR2,
p_variable_value IN VARCHAR2,
p_attribute_value_set_id IN NUMBER,
p_override_global_yn IN VARCHAR2,
p_object_version_number IN NUMBER,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_last_update_date IN DATE,
p_global_variable_value IN VARCHAR2 := NULL,
p_global_var_value_id IN NUMBER := NULL
) RETURN VARCHAR2 IS
BEGIN
/*IF (l_debug = 'Y') THEN
Okc_Debug.Log('3600: Entered Insert_Row function', 2);
G_PKG_NAME, '3600: Entered Insert_Row function' );
INSERT INTO OKC_K_ART_VARIABLES(
CAT_ID,
VARIABLE_CODE,
VARIABLE_TYPE,
EXTERNAL_YN,
VARIABLE_VALUE_ID,
VARIABLE_VALUE,
ATTRIBUTE_VALUE_SET_ID,
OVERRIDE_GLOBAL_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
GLOBAL_VARIABLE_VALUE,
GLOBAL_VARIABLE_VALUE_ID)
VALUES (
p_cat_id,
p_variable_code,
p_variable_type,
p_external_yn,
p_variable_value_id,
p_variable_value,
p_attribute_value_set_id,
p_override_global_yn,
p_object_version_number,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_login,
p_last_update_date,
p_global_variable_value,
p_global_var_value_id);
Okc_Debug.Log('3700: Leaving Insert_Row', 2);
G_PKG_NAME, '3700: Leaving Insert_Row' );
Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
G_PKG_NAME, '3800: Leaving Insert_Row:OTHERS Exception' );
END Insert_Row;
PROCEDURE Insert_Row(
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2,
p_variable_type IN VARCHAR2,
p_external_yn IN VARCHAR2,
p_variable_value_id IN VARCHAR2,
p_variable_value IN VARCHAR2,
p_attribute_value_set_id IN NUMBER,
p_override_global_yn IN VARCHAR2,
p_global_variable_value IN VARCHAR2 := NULL,
p_global_var_value_id IN NUMBER := NULL,
x_cat_id OUT NOCOPY NUMBER,
x_variable_code OUT NOCOPY VARCHAR2
) IS
l_object_version_number OKC_K_ART_VARIABLES.OBJECT_VERSION_NUMBER%TYPE;
l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
Okc_Debug.Log('4200: Entered Insert_Row', 2);
G_PKG_NAME, '4200: Entered Insert_Row' );
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
G_PKG_NAME, '4300: Call the internal Insert_Row for Base Table' );
x_return_status := Insert_Row(
p_cat_id => p_cat_id,
p_variable_code => p_variable_code,
p_variable_type => p_variable_type,
p_external_yn => p_external_yn,
p_variable_value_id => p_variable_value_id,
p_variable_value => p_variable_value,
p_attribute_value_set_id => p_attribute_value_set_id,
p_override_global_yn => p_override_global_yn,
p_object_version_number => l_object_version_number,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_last_update_date => l_last_update_date,
p_global_variable_value => p_global_variable_value,
p_global_var_value_id => p_global_var_value_id
);
Okc_Debug.Log('4500: Leaving Insert_Row', 2);
G_PKG_NAME, '4500: Leaving Insert_Row' );
Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
G_PKG_NAME, '4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception' );
Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm );
END Insert_Row;
SELECT object_version_number
FROM OKC_K_ART_VARIABLES
WHERE CAT_ID = cp_cat_id AND VARIABLE_CODE = cp_variable_code
AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
FOR UPDATE OF object_version_number NOWAIT;
SELECT object_version_number
FROM OKC_K_ART_VARIABLES
WHERE CAT_ID = cp_cat_id AND VARIABLE_CODE = cp_variable_code;
Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
FUNCTION Update_Row(
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2,
p_variable_type IN VARCHAR2,
p_external_yn IN VARCHAR2,
p_variable_value_id IN VARCHAR2,
p_variable_value IN VARCHAR2,
p_attribute_value_set_id IN NUMBER,
p_override_global_yn IN VARCHAR2,
p_object_version_number IN NUMBER,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_last_update_date IN DATE
) RETURN VARCHAR2 IS
BEGIN
/*IF (l_debug = 'Y') THEN
Okc_Debug.Log('6400: Entered Update_Row', 2);
G_PKG_NAME, '6400: Entered Update_Row' );
UPDATE OKC_K_ART_VARIABLES
SET VARIABLE_TYPE = p_variable_type,
EXTERNAL_YN = p_external_yn,
VARIABLE_VALUE_ID = p_variable_value_id,
VARIABLE_VALUE = p_variable_value,
ATTRIBUTE_VALUE_SET_ID = p_attribute_value_set_id,
OVERRIDE_GLOBAL_YN = p_override_global_yn,
OBJECT_VERSION_NUMBER = p_object_version_number,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_LOGIN = p_last_update_login,
LAST_UPDATE_DATE = p_last_update_date
WHERE CAT_ID = p_cat_id
AND VARIABLE_CODE = p_variable_code;
Okc_Debug.Log('6500: Leaving Update_Row', 2);
G_PKG_NAME, '6500: Leaving Update_Row' );
Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm );
END Update_Row;
PROCEDURE Update_Row(
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2,
p_variable_type IN VARCHAR2,
p_external_yn IN VARCHAR2,
p_variable_value_id IN VARCHAR2,
p_variable_value IN VARCHAR2,
p_attribute_value_set_id IN NUMBER := NULL,
p_override_global_yn IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER
) IS
l_variable_type OKC_K_ART_VARIABLES.VARIABLE_TYPE%TYPE;
l_last_updated_by OKC_K_ART_VARIABLES.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_K_ART_VARIABLES.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_K_ART_VARIABLES.LAST_UPDATE_DATE%TYPE;
Okc_Debug.Log('7000: Entered Update_Row', 2);
G_PKG_NAME, '7000: Entered Update_Row');
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
x_return_status := Update_Row(
p_cat_id => p_cat_id,
p_variable_code => p_variable_code,
p_variable_type => l_variable_type,
p_external_yn => l_external_yn,
p_variable_value_id => l_variable_value_id,
p_variable_value => l_variable_value,
p_attribute_value_set_id => l_attribute_value_set_id,
p_override_global_yn => l_override_global_yn,
p_object_version_number => l_object_version_number,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_last_update_date => l_last_update_date
);
Okc_Debug.Log('7800: Leaving Update_Row', 2);
G_PKG_NAME, '7800: Leaving Update_Row' );
Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
G_PKG_NAME, '7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception' );
Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm );
END Update_Row;
FUNCTION Delete_Row(
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
/*IF (l_debug = 'Y') THEN
Okc_Debug.Log('8200: Entered Delete_Row', 2);
G_PKG_NAME, '8200: Entered Delete_Row' );
delete_mrv_uda_data(p_cat_id =>p_CAT_ID, p_VARIABLE_CODE => p_VARIABLE_CODE, p_major_version => NULL);
DELETE FROM OKC_K_ART_VARIABLES WHERE CAT_ID = p_CAT_ID AND VARIABLE_CODE = p_VARIABLE_CODE;
Okc_Debug.Log('8300: Leaving Delete_Row', 2);
G_PKG_NAME, '8300: Leaving Delete_Row' );
Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
END Delete_Row;
PROCEDURE Delete_Row(
x_return_status OUT NOCOPY VARCHAR2,
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2,
p_object_version_number IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
Okc_Debug.Log('8800: Entered Delete_Row', 2);
G_PKG_NAME, '8800: Entered Delete_Row');
x_return_status := Delete_Row( p_cat_id => p_cat_id,p_variable_code => p_variable_code );
Okc_Debug.Log('9300: Leaving Delete_Row', 2);
G_PKG_NAME, '9300: Leaving Delete_Row' );
Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
G_PKG_NAME, '9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception' );
Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
END Delete_Row;
PROCEDURE delete_set(
x_return_status OUT NOCOPY VARCHAR2,
p_cat_id IN NUMBER
)
IS
CURSOR lock_csr IS
SELECT rowid
FROM OKC_K_ART_VARIABLES
WHERE cat_id = p_cat_id
FOR UPDATE NOWAIT;
Okc_Debug.Log('9700: Entered Delete_Set', 2);
G_PKG_NAME, '9700: Entered Delete_Set');
delete_mrv_uda_data(p_cat_id =>p_CAT_ID, p_VARIABLE_CODE => null, p_major_version => NULL);
DELETE FROM OKC_K_ART_VARIABLES
WHERE cat_id = p_cat_id;
Okc_Debug.Log('11000: Leaving Delete_set', 2);
G_PKG_NAME, '11000: Leaving Delete_set' );
Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
END Delete_Set;
PROCEDURE delete_set(
x_return_status OUT NOCOPY VARCHAR2,
p_scn_id IN NUMBER
)
IS
CURSOR lock_csr IS
SELECT rowid
FROM OKC_K_ART_VARIABLES
WHERE CAT_ID IN (SELECT ID FROM OKC_K_ARTICLES_B
WHERE SCN_ID=p_scn_id)
FOR UPDATE NOWAIT;
SELECT kart.ID
FROM OKC_K_ARTICLES_B KART
, OKC_BUS_VARIABLES_B BUS_VAR
, OKC_K_ART_VARIABLES KVAR
WHERE kart.SCN_ID=p_scn_id
AND KVAR.cat_id=kart.id
AND KVAR.variable_code=BUS_VAR.variable_code
AND BUS_VAR.MRV_FLAG='Y';
Okc_Debug.Log('9700: Entered Delete_Set', 2);
G_PKG_NAME, '9700: Entered Delete_Set');
delete_mrv_uda_data(p_cat_id => cat_rec.ID, p_VARIABLE_CODE => null, p_major_version => NULL);
DELETE FROM OKC_K_ART_VARIABLES
WHERE CAT_ID IN (SELECT ID FROM OKC_K_ARTICLES_B
WHERE SCN_ID=p_scn_id);
Okc_Debug.Log('11000: Leaving Delete_set', 2);
G_PKG_NAME, '11000: Leaving Delete_set' );
Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
END Delete_Set;
PROCEDURE delete_set(
x_return_status OUT NOCOPY VARCHAR2,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER
,p_retain_lock_terms_yn IN VARCHAR2 := 'N'
)
IS
CURSOR lock_csr IS
SELECT rowid
FROM OKC_K_ART_VARIABLES
WHERE cat_id IN (SELECT id FROM OKC_K_ARTICLES_B WHERE
document_type= p_doc_type AND
document_id = p_doc_id
AND
(( p_retain_lock_terms_yn = 'N')
OR
(p_retain_lock_terms_yn ='Y' AND amendment_operation_code IS NULL)
)
)
FOR UPDATE NOWAIT;
SELECT kart.ID
FROM OKC_K_ARTICLES_B KART
, OKC_BUS_VARIABLES_B BUS_VAR
, OKC_K_ART_VARIABLES KVAR
WHERE kart.document_type=p_doc_type
AND kart.document_id = p_doc_id
AND KVAR.cat_id=kart.id
AND KVAR.variable_code=BUS_VAR.variable_code
AND BUS_VAR.MRV_FLAG='Y'
AND
(( p_retain_lock_terms_yn = 'N')
OR
(p_retain_lock_terms_yn ='Y' AND KART.amendment_operation_code IS NULL)
);
Okc_Debug.Log('9700: Entered Delete_Set', 2);
G_PKG_NAME, '9700: Entered Delete_Set');
delete_mrv_uda_data(p_cat_id => cat_rec.ID, p_VARIABLE_CODE => null, p_major_version => NULL);
DELETE FROM OKC_K_ART_VARIABLES
WHERE cat_id IN (SELECT id FROM OKC_K_ARTICLES_B WHERE
document_type=p_doc_type AND
document_id = p_doc_id);
Okc_Debug.Log('11000: Leaving Delete_set', 2);
G_PKG_NAME, '11000: Leaving Delete_set' );
Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
END Delete_Set;
SELECT kart.ID
FROM OKC_K_ARTICLES_B KART
, OKC_BUS_VARIABLES_B BUS_VAR
, OKC_K_ART_VARIABLES KVAR
WHERE kart.document_type=p_doc_type
AND kart.document_id = p_doc_id
AND KVAR.cat_id=kart.id
AND KVAR.variable_code=BUS_VAR.variable_code
AND BUS_VAR.MRV_FLAG='Y';
INSERT INTO OKC_K_ART_VARIABLES_H (
major_version,
CAT_ID,
VARIABLE_CODE,
VARIABLE_TYPE,
EXTERNAL_YN,
VARIABLE_VALUE_ID,
VARIABLE_VALUE,
ATTRIBUTE_VALUE_SET_ID,
override_global_yn,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
mr_variable_html,
mr_variable_xml)
SELECT
p_major_version,
CAT_ID,
VARIABLE_CODE,
VARIABLE_TYPE,
EXTERNAL_YN,
VARIABLE_VALUE_ID,
VARIABLE_VALUE,
ATTRIBUTE_VALUE_SET_ID,
OVERRIDE_GLOBAL_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
mr_variable_html,
mr_variable_xml
FROM OKC_K_ART_VARIABLES
WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_B
WHERE DOCUMENT_TYPE = P_DOC_TYPE
AND DOCUMENT_ID = P_DOC_ID);
SELECT kart.ID
FROM OKC_K_ARTICLES_BH KART
, OKC_BUS_VARIABLES_B BUS_VAR
, OKC_K_ART_VARIABLES_H KVAR
WHERE kart.document_type=p_doc_type
AND kart.document_id = p_doc_id
AND KVAR.cat_id=kart.id
AND KVAR.variable_code=BUS_VAR.variable_code
AND BUS_VAR.MRV_FLAG='Y'
AND KART.major_version=p_major_version
AND KVAR.major_version=p_major_version;
INSERT INTO OKC_K_ART_VARIABLES (
CAT_ID,
VARIABLE_CODE,
VARIABLE_TYPE,
EXTERNAL_YN,
VARIABLE_VALUE_ID,
VARIABLE_VALUE,
ATTRIBUTE_VALUE_SET_ID,
OVERRIDE_GLOBAL_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
mr_variable_html,
mr_variable_xml)
SELECT
CAT_ID,
VARIABLE_CODE,
VARIABLE_TYPE,
EXTERNAL_YN,
VARIABLE_VALUE_ID,
VARIABLE_VALUE,
ATTRIBUTE_VALUE_SET_ID,
override_global_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
mr_variable_html,
mr_variable_xml
FROM OKC_K_ART_VARIABLES_H
WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_BH
WHERE DOCUMENT_TYPE = P_DOC_TYPE
AND DOCUMENT_ID = P_DOC_ID)
AND major_version = p_major_version;
FUNCTION Delete_Version(
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_major_version IN NUMBER
) RETURN VARCHAR2
IS
CURSOR doc_ver_mrv_csr
IS
SELECT kart.ID
FROM OKC_K_ARTICLES_BH KART
, OKC_BUS_VARIABLES_B BUS_VAR
, OKC_K_ART_VARIABLES_H KVAR
WHERE kart.document_type=p_doc_type
AND kart.document_id = p_doc_id
AND KVAR.cat_id=kart.id
AND KVAR.variable_code=BUS_VAR.variable_code
AND BUS_VAR.MRV_FLAG='Y'
AND KART.major_version=p_major_version
AND KVAR.major_version=p_major_version;
Okc_Debug.Log('7200: Entered Delete_Version', 2);
G_PKG_NAME, '7200: Entered Delete_Version' );
delete_mrv_uda_data(p_cat_id => cat_rec.id,p_variable_code => NULL, p_major_version =>p_major_version);
DELETE
FROM OKC_K_ART_VARIABLES_H
WHERE cat_id in (SELECT ID FROM OKC_K_ARTICLES_BH
WHERE document_type = p_doc_type and document_id = p_doc_id)
AND major_version = p_major_version;
Okc_Debug.Log('7300: Leaving Delete_Version', 2);
G_PKG_NAME, '7300: Leaving Delete_Version' );
Okc_Debug.Log('7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm );
END Delete_Version;
SELECT 'Y'
INTO l_mrv_flag
FROM okc_k_art_variables kav
WHERE cat_id = p_cat_id
AND EXISTS (SELECT 1
FROM okc_bus_variables_b var
WHERE kav.variable_code=var.variable_code
AND var.mrv_flag='Y');
SELECT Nvl(mrv_flag,'N')
INTO l_mrv_flag
FROM okc_bus_variables_b
WHERE variable_code=p_variable_code;
PROCEDURE delete_mrv_uda_data(p_cat_id IN NUMBER,p_variable_code IN VARCHAR2,p_major_version IN NUMBER)
IS
BEGIN
if p_major_version IS NULL THEN
IF p_cat_id IS NOT NULL THEN
IF p_variable_code IS NOT NULL
THEN
DELETE FROM OKC_K_ART_VAR_EXT_B
WHERE cat_id = p_cat_id
AND variable_code = p_variable_code;
DELETE FROM OKC_K_ART_VAR_EXT_TL
WHERE cat_id = p_cat_id
AND variable_code = p_variable_code;
DELETE FROM OKC_K_ART_VAR_EXT_B
WHERE cat_id = p_cat_id;
DELETE FROM OKC_K_ART_VAR_EXT_TL
WHERE cat_id = p_cat_id;
DELETE FROM OKC_K_ART_VAR_EXT_BH
WHERE cat_id = p_cat_id
AND variable_code = p_variable_code
AND major_version =p_major_version;
DELETE FROM OKC_K_ART_VAR_EXT_TLH
WHERE cat_id = p_cat_id
AND variable_code = p_variable_code
AND major_version =p_major_version;
DELETE FROM OKC_K_ART_VAR_EXT_BH
WHERE cat_id = p_cat_id
AND major_version =p_major_version;
DELETE FROM OKC_K_ART_VAR_EXT_TLH
WHERE cat_id = p_cat_id
AND major_version =p_major_version;
G_PKG_NAME, ' Leaving delete_mrv_uda_data because of EXCEPTION: '||sqlerrm );
END delete_mrv_uda_data;
INSERT INTO okc_k_art_var_ext_bh
(
EXTENSION_ID
,ATTR_GROUP_ID
,CAT_ID
,VARIABLE_CODE
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,C_EXT_ATTR1
,C_EXT_ATTR2
,C_EXT_ATTR3
,C_EXT_ATTR4
,C_EXT_ATTR5
,C_EXT_ATTR6
,C_EXT_ATTR7
,C_EXT_ATTR8
,C_EXT_ATTR9
,C_EXT_ATTR10
,C_EXT_ATTR11
,C_EXT_ATTR12
,C_EXT_ATTR13
,C_EXT_ATTR14
,C_EXT_ATTR15
,C_EXT_ATTR16
,C_EXT_ATTR17
,C_EXT_ATTR18
,C_EXT_ATTR19
,C_EXT_ATTR20
,C_EXT_ATTR21
,C_EXT_ATTR22
,C_EXT_ATTR23
,C_EXT_ATTR24
,C_EXT_ATTR25
,C_EXT_ATTR26
,C_EXT_ATTR27
,C_EXT_ATTR28
,C_EXT_ATTR29
,C_EXT_ATTR30
,C_EXT_ATTR31
,C_EXT_ATTR32
,C_EXT_ATTR33
,C_EXT_ATTR34
,C_EXT_ATTR35
,C_EXT_ATTR36
,C_EXT_ATTR37
,C_EXT_ATTR38
,C_EXT_ATTR39
,C_EXT_ATTR40
,N_EXT_ATTR1
,N_EXT_ATTR2
,N_EXT_ATTR3
,N_EXT_ATTR4
,N_EXT_ATTR5
,N_EXT_ATTR6
,N_EXT_ATTR7
,N_EXT_ATTR8
,N_EXT_ATTR9
,N_EXT_ATTR10
,N_EXT_ATTR11
,N_EXT_ATTR12
,N_EXT_ATTR13
,N_EXT_ATTR14
,N_EXT_ATTR15
,N_EXT_ATTR16
,N_EXT_ATTR17
,N_EXT_ATTR18
,N_EXT_ATTR19
,N_EXT_ATTR20
,UOM_EXT_ATTR1
,UOM_EXT_ATTR2
,UOM_EXT_ATTR3
,UOM_EXT_ATTR4
,UOM_EXT_ATTR5
,UOM_EXT_ATTR6
,UOM_EXT_ATTR7
,UOM_EXT_ATTR8
,UOM_EXT_ATTR9
,UOM_EXT_ATTR10
,UOM_EXT_ATTR11
,UOM_EXT_ATTR12
,UOM_EXT_ATTR13
,UOM_EXT_ATTR14
,UOM_EXT_ATTR15
,UOM_EXT_ATTR16
,UOM_EXT_ATTR17
,UOM_EXT_ATTR18
,UOM_EXT_ATTR19
,UOM_EXT_ATTR20
,D_EXT_ATTR1
,D_EXT_ATTR2
,D_EXT_ATTR3
,D_EXT_ATTR4
,D_EXT_ATTR5
,D_EXT_ATTR6
,D_EXT_ATTR7
,D_EXT_ATTR8
,D_EXT_ATTR9
,D_EXT_ATTR10
,MAJOR_VERSION
)
SELECT
extension_id
, ATTR_GROUP_ID
, CAT_ID
, VARIABLE_CODE
, DATA_LEVEL_ID
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, C_EXT_ATTR1
, C_EXT_ATTR2
, C_EXT_ATTR3
, C_EXT_ATTR4
, C_EXT_ATTR5
, C_EXT_ATTR6
, C_EXT_ATTR7
, C_EXT_ATTR8
, C_EXT_ATTR9
, C_EXT_ATTR10
, C_EXT_ATTR11
, C_EXT_ATTR12
, C_EXT_ATTR13
, C_EXT_ATTR14
, C_EXT_ATTR15
, C_EXT_ATTR16
, C_EXT_ATTR17
, C_EXT_ATTR18
, C_EXT_ATTR19
, C_EXT_ATTR20
, C_EXT_ATTR21
, C_EXT_ATTR22
, C_EXT_ATTR23
, C_EXT_ATTR24
, C_EXT_ATTR25
, C_EXT_ATTR26
, C_EXT_ATTR27
, C_EXT_ATTR28
, C_EXT_ATTR29
, C_EXT_ATTR30
, C_EXT_ATTR31
, C_EXT_ATTR32
, C_EXT_ATTR33
, C_EXT_ATTR34
, C_EXT_ATTR35
, C_EXT_ATTR36
, C_EXT_ATTR37
, C_EXT_ATTR38
, C_EXT_ATTR39
, C_EXT_ATTR40
, N_EXT_ATTR1
, N_EXT_ATTR2
, N_EXT_ATTR3
, N_EXT_ATTR4
, N_EXT_ATTR5
, N_EXT_ATTR6
, N_EXT_ATTR7
, N_EXT_ATTR8
, N_EXT_ATTR9
, N_EXT_ATTR10
, N_EXT_ATTR11
, N_EXT_ATTR12
, N_EXT_ATTR13
, N_EXT_ATTR14
, N_EXT_ATTR15
, N_EXT_ATTR16
, N_EXT_ATTR17
, N_EXT_ATTR18
, N_EXT_ATTR19
, N_EXT_ATTR20
, UOM_EXT_ATTR1
, UOM_EXT_ATTR2
, UOM_EXT_ATTR3
, UOM_EXT_ATTR4
, UOM_EXT_ATTR5
, UOM_EXT_ATTR6
, UOM_EXT_ATTR7
, UOM_EXT_ATTR8
, UOM_EXT_ATTR9
, UOM_EXT_ATTR10
, UOM_EXT_ATTR11
, UOM_EXT_ATTR12
, UOM_EXT_ATTR13
, UOM_EXT_ATTR14
, UOM_EXT_ATTR15
, UOM_EXT_ATTR16
, UOM_EXT_ATTR17
, UOM_EXT_ATTR18
, UOM_EXT_ATTR19
, UOM_EXT_ATTR20
, D_EXT_ATTR1
, D_EXT_ATTR2
, D_EXT_ATTR3
, D_EXT_ATTR4
, D_EXT_ATTR5
, D_EXT_ATTR6
, D_EXT_ATTR7
, D_EXT_ATTR8
, D_EXT_ATTR9
, D_EXT_ATTR10
,p_major_version
FROM OKC_K_ART_VAR_EXT_B
WHERE cat_id=p_cat_id;
INSERT INTO OKC_K_ART_VAR_EXT_TLH
(EXTENSION_ID
, ATTR_GROUP_ID
, CAT_ID
, VARIABLE_CODE
, DATA_LEVEL_ID
, SOURCE_LANG
, LANGUAGE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, TL_EXT_ATTR1
, TL_EXT_ATTR2
, TL_EXT_ATTR3
, TL_EXT_ATTR4
, TL_EXT_ATTR5
, TL_EXT_ATTR6
, TL_EXT_ATTR7
, TL_EXT_ATTR8
, TL_EXT_ATTR9
, TL_EXT_ATTR10
, TL_EXT_ATTR11
, TL_EXT_ATTR12
, TL_EXT_ATTR13
, TL_EXT_ATTR14
, TL_EXT_ATTR15
, TL_EXT_ATTR16
, TL_EXT_ATTR17
, TL_EXT_ATTR18
, TL_EXT_ATTR19
, TL_EXT_ATTR20
, TL_EXT_ATTR21
, TL_EXT_ATTR22
, TL_EXT_ATTR23
, TL_EXT_ATTR24
, TL_EXT_ATTR25
, TL_EXT_ATTR26
, TL_EXT_ATTR27
, TL_EXT_ATTR28
, TL_EXT_ATTR29
, TL_EXT_ATTR30
, TL_EXT_ATTR31
, TL_EXT_ATTR32
, TL_EXT_ATTR33
, TL_EXT_ATTR34
, TL_EXT_ATTR35
, TL_EXT_ATTR36
, TL_EXT_ATTR37
, TL_EXT_ATTR38
, TL_EXT_ATTR39
, TL_EXT_ATTR40
, MAJOR_VERSION
)
SELECT
EXTENSION_ID
, ATTR_GROUP_ID
, CAT_ID
, VARIABLE_CODE
, DATA_LEVEL_ID
, SOURCE_LANG
, LANGUAGE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, TL_EXT_ATTR1
, TL_EXT_ATTR2
, TL_EXT_ATTR3
, TL_EXT_ATTR4
, TL_EXT_ATTR5
, TL_EXT_ATTR6
, TL_EXT_ATTR7
, TL_EXT_ATTR8
, TL_EXT_ATTR9
, TL_EXT_ATTR10
, TL_EXT_ATTR11
, TL_EXT_ATTR12
, TL_EXT_ATTR13
, TL_EXT_ATTR14
, TL_EXT_ATTR15
, TL_EXT_ATTR16
, TL_EXT_ATTR17
, TL_EXT_ATTR18
, TL_EXT_ATTR19
, TL_EXT_ATTR20
, TL_EXT_ATTR21
, TL_EXT_ATTR22
, TL_EXT_ATTR23
, TL_EXT_ATTR24
, TL_EXT_ATTR25
, TL_EXT_ATTR26
, TL_EXT_ATTR27
, TL_EXT_ATTR28
, TL_EXT_ATTR29
, TL_EXT_ATTR30
, TL_EXT_ATTR31
, TL_EXT_ATTR32
, TL_EXT_ATTR33
, TL_EXT_ATTR34
, TL_EXT_ATTR35
, TL_EXT_ATTR36
, TL_EXT_ATTR37
, TL_EXT_ATTR38
, TL_EXT_ATTR39
, TL_EXT_ATTR40
, P_MAJOR_VERSION
FROM OKC_K_ART_VAR_EXT_TL
WHERE cat_id=p_cat_id;
INSERT INTO okc_k_art_var_ext_b
(
EXTENSION_ID
,ATTR_GROUP_ID
,CAT_ID
,VARIABLE_CODE
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,C_EXT_ATTR1
,C_EXT_ATTR2
,C_EXT_ATTR3
,C_EXT_ATTR4
,C_EXT_ATTR5
,C_EXT_ATTR6
,C_EXT_ATTR7
,C_EXT_ATTR8
,C_EXT_ATTR9
,C_EXT_ATTR10
,C_EXT_ATTR11
,C_EXT_ATTR12
,C_EXT_ATTR13
,C_EXT_ATTR14
,C_EXT_ATTR15
,C_EXT_ATTR16
,C_EXT_ATTR17
,C_EXT_ATTR18
,C_EXT_ATTR19
,C_EXT_ATTR20
,C_EXT_ATTR21
,C_EXT_ATTR22
,C_EXT_ATTR23
,C_EXT_ATTR24
,C_EXT_ATTR25
,C_EXT_ATTR26
,C_EXT_ATTR27
,C_EXT_ATTR28
,C_EXT_ATTR29
,C_EXT_ATTR30
,C_EXT_ATTR31
,C_EXT_ATTR32
,C_EXT_ATTR33
,C_EXT_ATTR34
,C_EXT_ATTR35
,C_EXT_ATTR36
,C_EXT_ATTR37
,C_EXT_ATTR38
,C_EXT_ATTR39
,C_EXT_ATTR40
,N_EXT_ATTR1
,N_EXT_ATTR2
,N_EXT_ATTR3
,N_EXT_ATTR4
,N_EXT_ATTR5
,N_EXT_ATTR6
,N_EXT_ATTR7
,N_EXT_ATTR8
,N_EXT_ATTR9
,N_EXT_ATTR10
,N_EXT_ATTR11
,N_EXT_ATTR12
,N_EXT_ATTR13
,N_EXT_ATTR14
,N_EXT_ATTR15
,N_EXT_ATTR16
,N_EXT_ATTR17
,N_EXT_ATTR18
,N_EXT_ATTR19
,N_EXT_ATTR20
,UOM_EXT_ATTR1
,UOM_EXT_ATTR2
,UOM_EXT_ATTR3
,UOM_EXT_ATTR4
,UOM_EXT_ATTR5
,UOM_EXT_ATTR6
,UOM_EXT_ATTR7
,UOM_EXT_ATTR8
,UOM_EXT_ATTR9
,UOM_EXT_ATTR10
,UOM_EXT_ATTR11
,UOM_EXT_ATTR12
,UOM_EXT_ATTR13
,UOM_EXT_ATTR14
,UOM_EXT_ATTR15
,UOM_EXT_ATTR16
,UOM_EXT_ATTR17
,UOM_EXT_ATTR18
,UOM_EXT_ATTR19
,UOM_EXT_ATTR20
,D_EXT_ATTR1
,D_EXT_ATTR2
,D_EXT_ATTR3
,D_EXT_ATTR4
,D_EXT_ATTR5
,D_EXT_ATTR6
,D_EXT_ATTR7
,D_EXT_ATTR8
,D_EXT_ATTR9
,D_EXT_ATTR10
)
SELECT
EXTENSION_ID -- EGO_EXTFWK_S.NEXTVAL
, ATTR_GROUP_ID
, CAT_ID
, VARIABLE_CODE
, DATA_LEVEL_ID
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, C_EXT_ATTR1
, C_EXT_ATTR2
, C_EXT_ATTR3
, C_EXT_ATTR4
, C_EXT_ATTR5
, C_EXT_ATTR6
, C_EXT_ATTR7
, C_EXT_ATTR8
, C_EXT_ATTR9
, C_EXT_ATTR10
, C_EXT_ATTR11
, C_EXT_ATTR12
, C_EXT_ATTR13
, C_EXT_ATTR14
, C_EXT_ATTR15
, C_EXT_ATTR16
, C_EXT_ATTR17
, C_EXT_ATTR18
, C_EXT_ATTR19
, C_EXT_ATTR20
, C_EXT_ATTR21
, C_EXT_ATTR22
, C_EXT_ATTR23
, C_EXT_ATTR24
, C_EXT_ATTR25
, C_EXT_ATTR26
, C_EXT_ATTR27
, C_EXT_ATTR28
, C_EXT_ATTR29
, C_EXT_ATTR30
, C_EXT_ATTR31
, C_EXT_ATTR32
, C_EXT_ATTR33
, C_EXT_ATTR34
, C_EXT_ATTR35
, C_EXT_ATTR36
, C_EXT_ATTR37
, C_EXT_ATTR38
, C_EXT_ATTR39
, C_EXT_ATTR40
, N_EXT_ATTR1
, N_EXT_ATTR2
, N_EXT_ATTR3
, N_EXT_ATTR4
, N_EXT_ATTR5
, N_EXT_ATTR6
, N_EXT_ATTR7
, N_EXT_ATTR8
, N_EXT_ATTR9
, N_EXT_ATTR10
, N_EXT_ATTR11
, N_EXT_ATTR12
, N_EXT_ATTR13
, N_EXT_ATTR14
, N_EXT_ATTR15
, N_EXT_ATTR16
, N_EXT_ATTR17
, N_EXT_ATTR18
, N_EXT_ATTR19
, N_EXT_ATTR20
, UOM_EXT_ATTR1
, UOM_EXT_ATTR2
, UOM_EXT_ATTR3
, UOM_EXT_ATTR4
, UOM_EXT_ATTR5
, UOM_EXT_ATTR6
, UOM_EXT_ATTR7
, UOM_EXT_ATTR8
, UOM_EXT_ATTR9
, UOM_EXT_ATTR10
, UOM_EXT_ATTR11
, UOM_EXT_ATTR12
, UOM_EXT_ATTR13
, UOM_EXT_ATTR14
, UOM_EXT_ATTR15
, UOM_EXT_ATTR16
, UOM_EXT_ATTR17
, UOM_EXT_ATTR18
, UOM_EXT_ATTR19
, UOM_EXT_ATTR20
, D_EXT_ATTR1
, D_EXT_ATTR2
, D_EXT_ATTR3
, D_EXT_ATTR4
, D_EXT_ATTR5
, D_EXT_ATTR6
, D_EXT_ATTR7
, D_EXT_ATTR8
, D_EXT_ATTR9
, D_EXT_ATTR10
FROM OKC_K_ART_VAR_EXT_BH
WHERE cat_id=p_cat_id
AND major_version =p_major_version;
INSERT INTO OKC_K_ART_VAR_EXT_TL
(EXTENSION_ID
, ATTR_GROUP_ID
, CAT_ID
, VARIABLE_CODE
, DATA_LEVEL_ID
, SOURCE_LANG
, LANGUAGE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CREATED_BY
, CREATION_DATE
, TL_EXT_ATTR1
, TL_EXT_ATTR2
, TL_EXT_ATTR3
, TL_EXT_ATTR4
, TL_EXT_ATTR5
, TL_EXT_ATTR6
, TL_EXT_ATTR7
, TL_EXT_ATTR8
, TL_EXT_ATTR9
, TL_EXT_ATTR10
, TL_EXT_ATTR11
, TL_EXT_ATTR12
, TL_EXT_ATTR13
, TL_EXT_ATTR14
, TL_EXT_ATTR15
, TL_EXT_ATTR16
, TL_EXT_ATTR17
, TL_EXT_ATTR18
, TL_EXT_ATTR19
, TL_EXT_ATTR20
, TL_EXT_ATTR21
, TL_EXT_ATTR22
, TL_EXT_ATTR23
, TL_EXT_ATTR24
, TL_EXT_ATTR25
, TL_EXT_ATTR26
, TL_EXT_ATTR27
, TL_EXT_ATTR28
, TL_EXT_ATTR29
, TL_EXT_ATTR30
, TL_EXT_ATTR31
, TL_EXT_ATTR32
, TL_EXT_ATTR33
, TL_EXT_ATTR34
, TL_EXT_ATTR35
, TL_EXT_ATTR36
, TL_EXT_ATTR37
, TL_EXT_ATTR38
, TL_EXT_ATTR39
, TL_EXT_ATTR40
)
SELECT
TLH.EXTENSION_ID
, TLH.ATTR_GROUP_ID
, TLH.CAT_ID
, TLH.VARIABLE_CODE
, TLH.DATA_LEVEL_ID
, TLH.SOURCE_LANG
, TLH.LANGUAGE
, TLH.LAST_UPDATE_DATE
, TLH.LAST_UPDATED_BY
, TLH.LAST_UPDATE_LOGIN
, TLH.CREATED_BY
, TLH.CREATION_DATE
, TLH.TL_EXT_ATTR1
, TLH.TL_EXT_ATTR2
, TLH.TL_EXT_ATTR3
, TLH.TL_EXT_ATTR4
, TLH.TL_EXT_ATTR5
, TLH.TL_EXT_ATTR6
, TLH.TL_EXT_ATTR7
, TLH.TL_EXT_ATTR8
, TLH.TL_EXT_ATTR9
, TLH.TL_EXT_ATTR10
, TLH.TL_EXT_ATTR11
, TLH.TL_EXT_ATTR12
, TLH.TL_EXT_ATTR13
, TLH.TL_EXT_ATTR14
, TLH.TL_EXT_ATTR15
, TLH.TL_EXT_ATTR16
, TLH.TL_EXT_ATTR17
, TLH.TL_EXT_ATTR18
, TLH.TL_EXT_ATTR19
, TLH.TL_EXT_ATTR20
, TLH.TL_EXT_ATTR21
, TLH.TL_EXT_ATTR22
, TLH.TL_EXT_ATTR23
, TLH.TL_EXT_ATTR24
, TLH.TL_EXT_ATTR25
, TLH.TL_EXT_ATTR26
, TLH.TL_EXT_ATTR27
, TLH.TL_EXT_ATTR28
, TLH.TL_EXT_ATTR29
, TLH.TL_EXT_ATTR30
, TLH.TL_EXT_ATTR31
, TLH.TL_EXT_ATTR32
, TLH.TL_EXT_ATTR33
, TLH.TL_EXT_ATTR34
, TLH.TL_EXT_ATTR35
, TLH.TL_EXT_ATTR36
, TLH.TL_EXT_ATTR37
, TLH.TL_EXT_ATTR38
, TLH.TL_EXT_ATTR39
, TLH.TL_EXT_ATTR40
FROM OKC_K_ART_VAR_EXT_TLH TLH
WHERE 1=1
AND TLH.cat_id=p_cat_id
AND TLH.MAJOR_VERSION=p_major_version;