[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT article_id,
start_date,
article_status,
local_article_version_id,
okc_article_adoptions.adoption_type
FROM OKC_ARTICLE_ADOPTIONS, OKC_ARTICLE_VERSIONS
WHERE global_article_version_id = cp_global_article_version_id
AND article_version_id = global_article_version_id
AND global_yn = 'Y'
AND local_org_id = cp_local_org_id
AND article_status = 'APPROVED'
AND nvl(end_date, sysdate+1) >= trunc(sysdate)
AND okc_article_adoptions.adoption_type = 'AVAILABLE';
SELECT
av.start_date,
av.end_date,
ad.adoption_type,
ad.adoption_status,
av.article_version_number,
av.article_version_id
FROM
okc_article_versions av,
okc_article_adoptions ad
WHERE
av.article_id = cp_article_id
AND
ad.global_article_version_id = av.article_version_id
AND
ad.local_org_id = cp_local_org_id
AND
ad.adoption_type = 'ADOPTED'
AND
ad.global_article_version_id <> cp_article_version_id
AND
av.start_date = (SELECT
max(av1.start_date)
FROM
okc_article_versions av1,
okc_article_adoptions ad1
WHERE
av1.article_id = av.article_id
AND
ad1.global_article_version_id = av1.article_version_id
AND
ad1.local_org_id = ad.local_org_id
AND
ad1.adoption_type = 'ADOPTED'
AND
ad1.global_article_version_id <> cp_article_version_id
);
SELECT S.GLOBAL_YN,
S.ARTICLE_STATUS,
S.START_DATE,
S.END_DATE,
S.MAX_START_DATE,
S.ADOPTION_TYPE,
S.ADOPTION_STATUS,
S.LOCAL_ARTICLE_VERSION_ID,
S.ARTICLE_VERSION_NUMBER,
S.ARTICLE_VERSION_ID
FROM (
SELECT
A.GLOBAL_YN,
A.ARTICLE_STATUS,
A.START_DATE, A.END_DATE,
MAX(A.START_DATE) OVER (PARTITION BY A.ARTICLE_ID) AS MAX_START_DATE,
AD.ADOPTION_TYPE,
AD.ADOPTION_STATUS,
AD.LOCAL_ARTICLE_VERSION_ID,
A.ARTICLE_VERSION_NUMBER,
A.ARTICLE_VERSION_ID
FROM OKC_ARTICLE_VERSIONS A, OKC_ARTICLE_ADOPTIONS AD
WHERE A.ARTICLE_ID = cp_article_id
AND AD.GLOBAL_ARTICLE_VERSION_ID = A.ARTICLE_VERSION_ID
AND AD.LOCAL_ORG_ID = cp_local_org_id
AND AD.ADOPTION_TYPE <> 'AVAILABLE'
AND AD.GLOBAL_ARTICLE_VERSION_ID <> cp_article_version_id
) S
WHERE S.START_DATE = S.MAX_START_DATE;
SELECT distinct(ARVL.ARTICLE_ID)
FROM OKC_ARTICLE_ADOPTIONS ADP,
OKC_ARTICLE_VERSIONS ARVG,
OKC_ARTICLE_VERSIONS ARVL
WHERE ARVG.ARTICLE_ID = cp_global_article_id
AND ADP.GLOBAL_ARTICLE_VERSION_ID = ARVG.ARTICLE_VERSION_ID
AND ADP.LOCAL_ORG_ID = cp_local_org_id
AND ADP.ADOPTION_TYPE = 'LOCALIZED'
AND ADP.LOCAL_ARTICLE_VERSION_ID = ARVL.ARTICLE_VERSION_ID;
SELECT
AV.START_DATE,
AV.END_DATE,
AV.ADOPTION_TYPE,
AV.ARTICLE_STATUS ADOPTION_STATUS,
AV.ARTICLE_VERSION_NUMBER,
AV.ARTICLE_VERSION_ID
FROM OKC_ARTICLE_VERSIONS AV
WHERE AV.ARTICLE_ID = cp_lcz_article_id
AND AV.ADOPTION_TYPE = 'LOCALIZED'
AND AV.START_DATE = ( SELECT MAX(V.START_DATE)
FROM OKC_ARTICLE_VERSIONS V
WHERE V.ARTICLE_ID = cp_lcz_article_id
AND V.ADOPTION_TYPE = 'LOCALIZED');
SELECT 1
FROM OKC_ARTICLES_ALL ARTL
WHERE ARTL.ORG_ID = cp_local_org_id
AND ARTL.STANDARD_YN = 'Y'
AND ARTL.ARTICLE_TITLE = ( SELECT ARTG.ARTICLE_TITLE
FROM OKC_ARTICLES_ALL ARTG
WHERE ARTG.ARTICLE_ID = cp_global_article_id
AND ARTG.ORG_ID = cp_global_org_id
AND ARTG.STANDARD_YN= 'Y');
PROCEDURE delete_local_adoption_details(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_only_local_version IN VARCHAR2,
p_local_article_version_id IN NUMBER,
p_local_org_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_delete_adoption';
l_delete_adoption VARCHAR2(1) := 'T';
SELECT 'T' , global_article_version_id FROM OKC_ARTICLE_ADOPTIONS A
WHERE local_article_version_id = cp_article_version_id
AND local_org_id = cp_local_org_id
AND EXISTS
(SELECT '1' FROM OKC_ARTICLE_ADOPTIONS B
WHERE B.GLOBAL_ARTICLE_VERSION_ID = A.GLOBAL_ARTICLE_VERSION_ID
AND B.LOCAL_ARTICLE_VERSION_ID <> A.LOCAL_ARTICLE_VERSION_ID
AND B.LOCAL_ORG_ID = A.LOCAL_ORG_ID);
okc_debug.log('100: Entered delete_adoption', 2);
l_delete_adoption := 'T';
FETCH l_other_version_csr into l_delete_adoption, l_global_article_version_id;
l_delete_adoption := 'F';
l_delete_adoption := 'F';
IF l_delete_adoption = 'F' Then
OKC_ARTICLE_ADOPTIONS_PVT.update_row(
x_return_status => x_return_status,
p_global_article_version_id => l_global_article_version_id,
p_adoption_type => 'AVAILABLE',
p_local_org_id => p_local_org_id,
p_orig_local_version_id => p_local_article_version_id,
p_new_local_version_id => NULL,
p_adoption_status => OKC_API.G_MISS_CHAR,
p_object_version_number => NULL
);
ELSIF l_delete_adoption = 'T' Then
OKC_ARTICLE_ADOPTIONS_PVT.delete_row(
x_return_status => x_return_status,
p_global_article_version_id => l_global_article_version_id,
p_local_org_id => p_local_org_id,
p_local_article_version_id => p_local_article_version_id,
p_object_version_number => NULL
);
okc_debug.log('300: Leaving delete_Adoption: OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.log('500: Leaving delete_Adoption because of EXCEPTION: '||sqlerrm, 2);
END delete_local_adoption_details;
SELECT global_article_version_id, adoption_type FROM
OKC_ARTICLE_ADOPTIONS
WHERE local_article_version_id = cp_local_article_version_id
AND local_org_id = cp_local_org_id;
OKC_ARTICLE_ADOPTIONS_PVT.update_row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_global_article_version_id => p_global_article_version_id,
p_adoption_type => 'LOCALIZED',
p_local_org_id => p_local_org_id,
p_orig_local_version_id => NULL,
p_new_local_version_id => p_local_article_version_id,
p_adoption_status => nvl(p_article_status,'DRAFT'),
p_object_version_number => NULL
);
OKC_ARTICLE_ADOPTIONS_PVT.update_row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_global_article_version_id => l_global_article_version_id,
p_adoption_type => 'LOCALIZED',
p_local_org_id => p_local_org_id,
p_orig_local_version_id => NULL,
p_new_local_version_id => p_local_article_version_id,
p_adoption_status => nvl(p_article_status,'DRAFT'),
p_object_version_number => NULL
);
OKC_ARTICLE_ADOPTIONS_PVT.INSERT_ROW
(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_global_article_version_id=> l_global_article_version_id,
p_adoption_type => 'LOCALIZED',
p_local_org_id => p_local_org_id,
p_local_article_version_id => p_local_article_version_id,
p_adoption_status => nvl(p_article_status,'DRAFT'),
x_global_article_version_id => l_global_version_id_out,
x_local_org_id => l_local_org_id,
x_local_article_version_id => l_local_article_version_id
);
SELECT ORGANIZATION_ID,
decode(nvl(ORG_INFORMATION1,'N'),'N','AVAILABLE','Y','ADOPTED') ADOPTION_TYPE ,
ORG_INFORMATION2
FROM HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND ORGANIZATION_ID <> cp_global_org_id;
SELECT '1'
FROM OKC_ARTICLE_ADOPTIONS
WHERE GLOBAL_ARTICLE_VERSION_ID = cp_global_version_id
AND LOCAL_ORG_ID = cp_local_org_id
AND rownum < 2;
SELECT A.ARTICLE_NUMBER,
SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
RELATIONSHIP_TYPE
FROM OKC_ARTICLE_RELATNS_ALL R,
OKC_ARTICLES_ALL A
WHERE R.SOURCE_ARTICLE_ID = cp_global_article_id
AND R.TARGET_ARTICLE_ID = A.ARTICLE_ID
AND R.ORG_ID = cp_global_org_id
AND EXISTS
(SELECT 1 FROM OKC_ARTICLE_VERSIONS V
WHERE V.ARTICLE_ID = R.TARGET_ARTICLE_ID
AND V.GLOBAL_YN = 'Y'
AND V.ARTICLE_STATUS = 'APPROVED'
AND NVL(V.END_DATE,SYSDATE + 1) > SYSDATE
)
AND EXISTS
(SELECT 1 FROM OKC_ARTICLE_VERSIONS V1
WHERE V1.ARTICLE_ID = R.SOURCE_ARTICLE_ID
AND V1.GLOBAL_YN = 'Y'
AND V1.ARTICLE_STATUS = 'APPROVED'
AND NVL(V1.END_DATE,SYSDATE + 1) > SYSDATE
)
AND NOT EXISTS
(
SELECT '1'
FROM OKC_ARTICLE_RELATNS_ALL R1
WHERE R1.SOURCE_ARTICLE_ID = R.SOURCE_ARTICLE_ID AND
R1.TARGET_ARTICLE_ID = R.TARGET_ARTICLE_ID AND
R1.RELATIONSHIP_TYPE = R.RELATIONSHIP_TYPE AND
R1.ORG_ID = cp_local_org_id
);
SELECT 1
FROM OKC_ARTICLES_ALL ARTL
WHERE ARTL.ORG_ID = cp_local_org_id
AND ARTL.STANDARD_YN= 'Y'
AND ARTL.ARTICLE_TITLE = ( SELECT ARTG.ARTICLE_TITLE
FROM OKC_ARTICLES_ALL ARTG
WHERE ARTG.ARTICLE_ID = cp_global_article_id
AND ARTG.ORG_ID = cp_global_org_id
AND ARTG.STANDARD_YN='Y');
INSERT INTO OKC_ARTICLE_RELATNS_ALL
(
SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
ORG_ID,
RELATIONSHIP_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
VALUES
(
l_source_article_id_tbl(j),
l_target_article_id_tbl(j),
l_org_id_tbl(i),
l_relationship_type_tbl(j),
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_login_Id,
sysdate
);
INSERT INTO OKC_ARTICLE_RELATNS_ALL
(
SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
ORG_ID,
RELATIONSHIP_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
VALUES
(
l_target_article_id_tbl(j),
l_source_article_id_tbl(j),
l_org_id_tbl(i),
l_relationship_type_tbl(j),
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_Login_Id,
sysdate);
l_target_article_id_tbl.DELETE;
l_source_article_id_tbl.DELETE;
l_relationship_type_tbl.DELETE;
INSERT INTO OKC_ARTICLE_ADOPTIONS
(
GLOBAL_ARTICLE_VERSION_ID,
ADOPTION_TYPE,
LOCAL_ORG_ID,
ADOPTION_STATUS,
LOCAL_ARTICLE_VERSION_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
SELECT
p_global_article_version_id,
l_adoption_type_tbl(i),
l_org_id_tbl(i),
l_adoption_status_tbl(i),
NULL,
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_Login_Id,
sysdate
FROM DUAL
WHERE l_adp_record_status_tbl(i) = 'S';
l_org_id_tbl.DELETE;
l_adoption_type_tbl.DELETE;
l_notifier_tbl.DELETE;
l_adoption_status_tbl.DELETE;
l_adp_record_status_tbl.DELETE;
SELECT decode(nvl(ORG_INFORMATION1,'N'),'N','AVAILABLE','Y','ADOPTED') ADOPTION_TYPE, U.NAME
FROM HR_ORGANIZATION_INFORMATION I,
HR_ORGANIZATION_UNITS U
WHERE I.ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND I.ORGANIZATION_ID = cp_org_id
AND I.ORGANIZATION_ID = U.ORGANIZATION_ID
AND I.ORGANIZATION_ID <> cp_global_org_id;
SELECT article_version_id , article_title,
art.article_id, art.orig_system_reference_code
FROM OKC_ARTICLE_VERSIONS VER, OKC_ARTICLES_ALL ART
WHERE global_yn = 'Y'
AND org_id = cp_global_org_id
AND VER.article_id = ART.article_id
AND article_status in ('APPROVED', 'ON_HOLD')
AND nvl(end_date, sysdate) >= trunc(sysdate)
AND NOT EXISTS
(SELECT 1 FROM OKC_ARTICLE_ADOPTIONS
WHERE global_article_version_id = VER.article_version_id
AND local_org_id = cp_local_org_id)
UNION ALL
SELECT article_version_id , article_title, article_id, orig_system_reference_code
FROM
(
SELECT article_version_id , article_title , art.article_id ,
start_date , end_date,
global_yn , org_id , article_status,
art.orig_system_reference_code
FROM OKC_ARTICLE_VERSIONS VER, OKC_ARTICLES_ALL ART
WHERE VER.article_id = ART.article_id
AND global_yn = 'Y'
AND org_id = cp_global_org_id
AND start_date = ( SELECT max(start_date)
FROM OKC_ARTICLE_VERSIONS VER1,OKC_ARTICLES_ALL ART1
WHERE VER1.ARTICLE_ID = ART1.ARTICLE_ID
AND VER1.ARTICLE_ID = VER.ARTICLE_ID )
)
WHERE
article_status = 'APPROVED'
AND end_date < trunc(sysdate)
AND NOT EXISTS
(SELECT 1 FROM OKC_ARTICLE_ADOPTIONS
WHERE global_article_version_id = article_version_id
AND local_org_id = cp_local_org_id);
SELECT '1'
FROM OKC_ARTICLES_ALL
WHERE org_id = cp_local_org_id
AND article_title = cp_article_title
AND standard_yn = 'Y';
SELECT meaning
FROM FND_LOOKUPS
WHERE lookup_type = 'OKC_ARTICLE_ADOPTION_TYPE'
AND lookup_code = cp_adoption_type;
INSERT INTO OKC_ARTICLE_ADOPTIONS
(
GLOBAL_ARTICLE_VERSION_ID,
ADOPTION_TYPE,
LOCAL_ORG_ID,
ADOPTION_STATUS,
LOCAL_ARTICLE_VERSION_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
VALUES
(
l_article_version_id_tbl(i),
'AVAILABLE',
p_org_id,
NULL,
NULL,
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_Login_Id,
sysdate );
INSERT INTO OKC_ARTICLE_ADOPTIONS
(
GLOBAL_ARTICLE_VERSION_ID,
ADOPTION_TYPE,
LOCAL_ORG_ID,
ADOPTION_STATUS,
LOCAL_ARTICLE_VERSION_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
SELECT
l_article_version_id_tbl(i),
l_adoption_type_tbl(i),
p_org_id,
l_adoption_status_tbl(i),
NULL,
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_Login_Id,
sysdate
FROM DUAL
WHERE l_adp_record_status_tbl(i) = 'S';
INSERT INTO OKC_ARTICLE_RELATNS_ALL
(
SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
ORG_ID,
RELATIONSHIP_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
SELECT SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
p_org_id,
RELATIONSHIP_TYPE,
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_Login_Id,
sysdate
FROM OKC_ARTICLE_RELATNS_ALL R
WHERE R.ORG_ID = l_global_org_id
AND EXISTS
(SELECT 1 FROM OKC_ARTICLE_VERSIONS V1, OKC_ARTICLE_ADOPTIONS A1
WHERE V1.ARTICLE_ID = R.TARGET_ARTICLE_ID
AND A1.GLOBAL_ARTICLE_VERSION_ID = V1.ARTICLE_VERSION_ID
AND V1.GLOBAL_YN = 'Y'
AND A1.ADOPTION_STATUS = 'APPROVED'
AND A1.ADOPTION_TYPE = 'ADOPTED'
AND A1.LOCAL_ORG_ID = p_org_id
)
AND EXISTS
(SELECT 1 FROM OKC_ARTICLE_VERSIONS V2, OKC_ARTICLE_ADOPTIONS A2
WHERE V2.ARTICLE_ID = R.SOURCE_ARTICLE_ID
AND A2.GLOBAL_ARTICLE_VERSION_ID = V2.ARTICLE_VERSION_ID
AND V2.GLOBAL_YN = 'Y'
AND A2.ADOPTION_STATUS = 'APPROVED'
AND A2.ADOPTION_TYPE = 'ADOPTED'
AND A2.LOCAL_ORG_ID = p_org_id
)
AND NOT EXISTS
(
SELECT '1'
FROM OKC_ARTICLE_RELATNS_ALL R1
WHERE R1.SOURCE_ARTICLE_ID = R.SOURCE_ARTICLE_ID AND
R1.TARGET_ARTICLE_ID = R.TARGET_ARTICLE_ID AND
R1.RELATIONSHIP_TYPE = R.RELATIONSHIP_TYPE AND
R1.ORG_ID = p_org_id
);
l_adoption_type_tbl.DELETE;
l_adoption_status_tbl.DELETE;
l_adp_record_status_tbl.DELETE;
l_article_version_id_tbl.DELETE;