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;
SELECT OKC_ARTICLES_ALL_S1.NEXTVAL FROM DUAL;
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_ARTICLES_ALL_pk_csr (cp_article_id IN NUMBER) IS
SELECT
ARTICLE_TITLE,
ORG_ID,
ARTICLE_NUMBER,
STANDARD_YN,
ARTICLE_INTENT,
ARTICLE_LANGUAGE,
ARTICLE_TYPE,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
CZ_TRANSFER_STATUS_FLAG,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
FROM OKC_ARTICLES_ALL t
WHERE t.ARTICLE_ID = cp_article_id;
x_last_updated_by,
x_last_update_login,
x_last_update_date;
l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_ARTICLES_ALL.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 HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = p_org_id;
SELECT '!'
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = p_article_language
AND INSTALLED_FLAG in ('B','I');
SELECT '!'
FROM FND_LOOKUPS FNDLKUP
WHERE FNDLKUP.LOOKUP_TYPE = 'OKC_SUBJECT'
AND FNDLKUP.LOOKUP_CODE = p_article_type;
SELECT '1' FROM OKC_ARTICLES_ALL
WHERE article_title = p_article_title
AND org_id = p_org_id
AND standard_yn = 'Y'
AND article_id <> nvl(p_article_id,-99)
AND rownum < 2
UNION ALL
SELECT '1'
FROM okc_articles_all art, okc_article_adoptions adp, okc_article_versions artv
WHERE adp.local_org_id = p_org_id
AND art.article_title = p_article_title
AND adp.adoption_type = 'ADOPTED'
AND adp.global_article_version_id = artv.article_version_id
AND art.article_id = artv.article_id
AND art.article_id <> nvl(p_article_id,-99)
AND rownum < 2;
l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
FUNCTION Insert_Row(
p_article_id IN NUMBER,
p_article_title IN VARCHAR2,
p_org_id IN NUMBER,
p_article_number IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_article_intent IN VARCHAR2,
p_article_language IN VARCHAR2,
p_article_type IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2,
p_orig_system_reference_id1 IN VARCHAR2,
p_orig_system_reference_id2 IN VARCHAR2,
p_cz_transfer_status_flag IN VARCHAR2,
p_program_id IN NUMBER,
p_program_login_id IN NUMBER,
p_program_application_id IN NUMBER,
p_request_id IN NUMBER,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 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
l_program_id OKC_ARTICLES_ALL.PROGRAM_ID%TYPE;
Okc_Debug.Log('3600: Entered Insert_Row function', 2);
INSERT INTO OKC_ARTICLES_ALL(
ARTICLE_ID,
ARTICLE_TITLE,
ORG_ID,
ARTICLE_NUMBER,
STANDARD_YN,
ARTICLE_INTENT,
ARTICLE_LANGUAGE,
ARTICLE_TYPE,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
CZ_TRANSFER_STATUS_FLAG,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
VALUES (
p_article_id,
p_article_title,
p_org_id,
p_article_number,
p_standard_yn,
p_article_intent,
nvl(p_article_language,USERENV('LANG')),
p_article_type,
p_orig_system_reference_code,
p_orig_system_reference_id1,
p_orig_system_reference_id2,
'N', -- Default value for cz_transfer_status_flag
l_program_id,
l_program_login_id,
l_program_appl_id,
l_request_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_object_version_number,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_login,
p_last_update_date);
Okc_Debug.Log('3700: Leaving Insert_Row', 2);
Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
END Insert_Row;
PROCEDURE Insert_Row(
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
p_article_id IN NUMBER := NULL,
p_article_title IN VARCHAR2,
p_org_id IN NUMBER,
p_article_number IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_article_intent IN VARCHAR2,
p_article_language IN VARCHAR2,
p_article_type IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2,
p_orig_system_reference_id1 IN VARCHAR2,
p_orig_system_reference_id2 IN VARCHAR2,
p_cz_transfer_status_flag IN VARCHAR2,
p_program_id IN NUMBER := NULL,
p_program_login_id IN NUMBER := NULL,
p_program_application_id IN NUMBER := NULL,
p_request_id IN NUMBER := NULL,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL,
x_article_number OUT NOCOPY VARCHAR2,
x_article_id OUT NOCOPY NUMBER
) IS
l_object_version_number OKC_ARTICLES_ALL.OBJECT_VERSION_NUMBER%TYPE;
l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
Okc_Debug.Log('4200: Entered Insert_Row', 2);
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);
x_return_status := Insert_Row(
p_article_id => x_article_id,
p_article_title => p_article_title,
p_org_id => p_org_id,
p_article_number => p_article_number,
p_standard_yn => p_standard_yn,
p_article_intent => p_article_intent,
p_article_language => p_article_language,
p_article_type => p_article_type,
p_orig_system_reference_code => p_orig_system_reference_code,
p_orig_system_reference_id1 => p_orig_system_reference_id1,
p_orig_system_reference_id2 => p_orig_system_reference_id2,
p_cz_transfer_status_flag => p_cz_transfer_status_flag,
p_program_id => p_program_id,
p_program_login_id => p_program_login_id,
p_program_application_id => p_program_application_id,
p_request_id => p_request_id,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
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('4500: Leaving Insert_Row', 2);
Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
END Insert_Row;
SELECT object_version_number
FROM OKC_ARTICLES_ALL
WHERE ARTICLE_ID = cp_article_id
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_ARTICLES_ALL
WHERE ARTICLE_ID = cp_article_id;
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_article_id IN NUMBER,
p_article_title IN VARCHAR2,
p_org_id IN NUMBER,
p_article_number IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_article_intent IN VARCHAR2,
p_article_language IN VARCHAR2,
p_article_type IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2,
p_orig_system_reference_id1 IN VARCHAR2,
p_orig_system_reference_id2 IN VARCHAR2,
p_cz_transfer_status_flag IN VARCHAR2,
p_program_id IN NUMBER,
p_program_login_id IN NUMBER,
p_program_application_id IN NUMBER,
p_request_id IN NUMBER,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 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
l_program_id OKC_ARTICLES_ALL.PROGRAM_ID%TYPE;
Okc_Debug.Log('6400: Entered Update_Row', 2);
UPDATE OKC_ARTICLES_ALL
SET ARTICLE_TITLE = p_article_title,
-- ORG_ID = p_org_id,
ARTICLE_NUMBER = p_article_number,
STANDARD_YN = p_standard_yn,
ARTICLE_INTENT = p_article_intent,
-- ARTICLE_LANGUAGE = p_article_language,
ARTICLE_TYPE = p_article_type,
ORIG_SYSTEM_REFERENCE_CODE = p_orig_system_reference_code,
ORIG_SYSTEM_REFERENCE_ID1 = p_orig_system_reference_id1,
ORIG_SYSTEM_REFERENCE_ID2 = p_orig_system_reference_id2,
CZ_TRANSFER_STATUS_FLAG = p_cz_transfer_status_flag,
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
ATTRIBUTE_CATEGORY = p_attribute_category,
ATTRIBUTE1 = p_attribute1,
ATTRIBUTE2 = p_attribute2,
ATTRIBUTE3 = p_attribute3,
ATTRIBUTE4 = p_attribute4,
ATTRIBUTE5 = p_attribute5,
ATTRIBUTE6 = p_attribute6,
ATTRIBUTE7 = p_attribute7,
ATTRIBUTE8 = p_attribute8,
ATTRIBUTE9 = p_attribute9,
ATTRIBUTE10 = p_attribute10,
ATTRIBUTE11 = p_attribute11,
ATTRIBUTE12 = p_attribute12,
ATTRIBUTE13 = p_attribute13,
ATTRIBUTE14 = p_attribute14,
ATTRIBUTE15 = p_attribute15,
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 ARTICLE_ID = p_article_id;
Okc_Debug.Log('6500: Leaving Update_Row', 2);
update OKC_ARTICLE_VERSIONS
set article_text = article_text
where article_id = p_article_id;
Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
END Update_Row;
PROCEDURE Update_Row(
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_article_intent OUT NOCOPY VARCHAR2,
p_article_id IN NUMBER,
p_article_title IN VARCHAR2,
p_org_id IN NUMBER,
p_article_number IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_article_intent IN VARCHAR2,
p_article_language IN VARCHAR2,
p_article_type IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2,
p_orig_system_reference_id1 IN VARCHAR2,
p_orig_system_reference_id2 IN VARCHAR2,
p_cz_transfer_status_flag IN VARCHAR2,
p_program_id IN NUMBER := NULL,
p_program_login_id IN NUMBER := NULL,
p_program_application_id IN NUMBER := NULL,
p_request_id IN NUMBER := NULL,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER
) IS
l_article_title OKC_ARTICLES_ALL.ARTICLE_TITLE%TYPE;
l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
Okc_Debug.Log('7000: Entered Update_Row', 2);
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_article_id => p_article_id,
p_article_title => l_article_title,
p_org_id => l_org_id,
p_article_number => l_article_number,
p_standard_yn => l_standard_yn,
p_article_intent => l_article_intent,
p_article_language => l_article_language,
p_article_type => l_article_type,
p_orig_system_reference_code => l_orig_system_reference_code,
p_orig_system_reference_id1 => l_orig_system_reference_id1,
p_orig_system_reference_id2 => l_orig_system_reference_id2,
p_cz_transfer_status_flag => l_cz_transfer_status_flag,
p_program_id => l_program_id,
p_program_login_id => l_program_login_id,
p_program_application_id => l_program_application_id,
p_request_id => l_request_id,
p_attribute_category => l_attribute_category,
p_attribute1 => l_attribute1,
p_attribute2 => l_attribute2,
p_attribute3 => l_attribute3,
p_attribute4 => l_attribute4,
p_attribute5 => l_attribute5,
p_attribute6 => l_attribute6,
p_attribute7 => l_attribute7,
p_attribute8 => l_attribute8,
p_attribute9 => l_attribute9,
p_attribute10 => l_attribute10,
p_attribute11 => l_attribute11,
p_attribute12 => l_attribute12,
p_attribute13 => l_attribute13,
p_attribute14 => l_attribute14,
p_attribute15 => l_attribute15,
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);
Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
END Update_Row;
FUNCTION Delete_Row(
p_article_id IN NUMBER
) RETURN VARCHAR2 IS
BEGIN
IF (l_debug = 'Y') THEN
Okc_Debug.Log('8200: Entered Delete_Row', 2);
DELETE FROM OKC_ARTICLES_ALL WHERE ARTICLE_ID = p_ARTICLE_ID;
Okc_Debug.Log('8300: Leaving Delete_Row', 2);
Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
END Delete_Row;
PROCEDURE Delete_Row(
x_return_status OUT NOCOPY VARCHAR2,
p_article_id IN NUMBER,
p_object_version_number IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
Okc_Debug.Log('8800: Entered Delete_Row', 2);
x_return_status := Delete_Row( p_article_id => p_article_id );
Okc_Debug.Log('9300: Leaving Delete_Row', 2);
Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
END Delete_Row;