[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT OKC_ARTICLES_ALL_S1.NEXTVAL FROM DUAL;
SELECT OKC_ARTICLE_VERSIONS_S1.NEXTVAL FROM DUAL;
SELECT INF.ORGANIZATION_ID, UNIT.NAME
FROM HR_ORGANIZATION_INFORMATION INF, HR_ALL_ORGANIZATION_UNITS UNIT
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND INF.ORGANIZATION_ID = UNIT.ORGANIZATION_ID
AND INF.ORGANIZATION_ID = G_CURRENT_ORG_ID;
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
VER.ARTICLE_VERSION_ID,
'ADOPTED',
G_CURRENT_ORG_ID,
'APPROVED',
NULL,
1.0,
G_User_Id,
sysdate,
G_User_Id,
G_Login_Id,
sysdate
FROM OKC_ARTICLE_VERSIONS VER, OKC_ARTICLES_ALL ART
WHERE ART.ORIG_SYSTEM_REFERENCE_CODE = 'OKCMIGORIG'
AND VER.ORIG_SYSTEM_REFERENCE_CODE = ART.ORIG_SYSTEM_REFERENCE_CODE
AND ART.ARTICLE_ID = VER.ARTICLE_ID
AND ART.ORG_ID = G_GLOBAL_ORG_ID
AND NOT EXISTS
(SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
AND ADP.LOCAL_ORG_ID = G_CURRENT_ORG_ID);
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
REL.SOURCE_ARTICLE_ID,
REL.TARGET_ARTICLE_ID,
G_CURRENT_ORG_ID,
'INCOMPATIBLE',
1.0,
G_User_Id,
sysdate,
G_User_Id,
G_Login_Id,
sysdate
FROM OKC_ARTICLE_RELATNS_ALL REL
WHERE ORG_ID = G_GLOBAL_ORG_ID
AND EXISTS
(SELECT 1 FROM OKC_ARTICLES_ALL SRC, OKC_ARTICLES_ALL TAR
WHERE SRC.orig_system_reference_code like 'OKCMIG%'
AND SRC.article_id = REL.source_article_id
AND SRC.org_id = G_GLOBAL_ORG_ID
AND TAR.org_id = G_GLOBAL_ORG_ID
AND TAR.article_id = REL.target_article_id
AND TAR.orig_system_reference_code = SRC.orig_system_reference_code)
AND NOT EXISTS
(SELECT 1 FROM OKC_ARTICLE_RELATNS_ALL REL1
WHERE REL1.source_article_id = REL.source_article_id AND
REL1.target_article_id = REL.target_article_id AND
REL1.org_id = G_CURRENT_ORG_ID);
SELECT language_code
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG = 'B';
SELECT -- translated both article and versions
decode(ARTL.LANGUAGE,cp_base_language,ARTL.NAME,
ARTL.NAME||'('||artl.language||')') ART_ARTICLE_TITLE ,
decode(ARTL.LANGUAGE,cp_base_language,ARTL.ID,
-99) ART_ARTICLE_ID,
ARTL.ID ART_SYSTEM_REFERENCE_ID1,
ARTL.LANGUAGE ART_ARTICLE_LANGUAGE,
ARTL.LANGUAGE ARTV_ARTICLE_LANGUAGE
FROM OKC_STD_ARTICLES_TL ARTL
WHERE
ARTL.LANGUAGE = ARTL.SOURCE_LANG
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_ARTICLES_ALL
WHERE ORIG_SYSTEM_REFERENCE_ID1 = TO_CHAR(ARTL.ID)
AND ORIG_SYSTEM_REFERENCE_CODE in ('OKCMIGORIG', 'OKCMIGNEW')
AND ARTICLE_LANGUAGE = ARTL.LANGUAGE)
UNION ALL -- translated versions only but not articles - results in new article -- distinct is needed as there may be multiple translated versions.
SELECT DISTINCT
ARTL.NAME||'('||artvl.language||')' ART_ARTICLE_TITLE ,
-99 ART_ARTICLE_ID,
ARTL.ID ART_SYSTEM_REFERENCE_ID1,
ARTVL.LANGUAGE ART_ARTICLE_LANGUAGE,
ARTVL.LANGUAGE ARTV_ARTICLE_LANGUAGE
FROM OKC_STD_ART_VERSIONS_TL ARTVL,
OKC_STD_ARTICLES_TL ARTL
WHERE ARTL.LANGUAGE = ARTL.SOURCE_LANG
AND ARTL.LANGUAGE = cp_base_language
AND ARTVL.LANGUAGE = ARTVL.SOURCE_LANG
AND ARTVL.LANGUAGE <> cp_base_language
AND ARTVL.SAE_ID = ARTL.ID
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_ARTICLES_ALL
WHERE ORIG_SYSTEM_REFERENCE_ID1 = TO_CHAR(ARTL.ID)
AND ORIG_SYSTEM_REFERENCE_CODE in ('OKCMIGORIG', 'OKCMIGNEW')
AND ARTICLE_LANGUAGE = ARTVL.LANGUAGE)
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_STD_ARTICLES_TL ARTL1
WHERE ARTL1.LANGUAGE = ARTVL.LANGUAGE
AND ARTL1.SOURCE_LANG = ARTVL.SOURCE_LANG
AND ARTVL.SAE_ID = ARTL1.ID);
SELECT
ARTV.SAV_RELEASE,
ARTV.DATE_ACTIVE,
ARTVL.TEXT
FROM OKC_STD_ART_VERSIONS_B ARTV,
OKC_STD_ART_VERSIONS_TL ARTVL
WHERE ARTV.SAE_ID = cp_article_id
AND ARTVL.LANGUAGE = ARTVL.SOURCE_LANG
AND ARTVL.LANGUAGE = cp_language
AND ARTVL.SAE_ID = ARTV.SAE_ID
AND ARTVL.SAV_RELEASE = ARTV.SAV_RELEASE
ORDER BY DATE_ACTIVE;
SELECT INF.ORGANIZATION_ID, UNIT.NAME
FROM HR_ORGANIZATION_INFORMATION INF, HR_ALL_ORGANIZATION_UNITS UNIT
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND INF.ORGANIZATION_ID = UNIT.ORGANIZATION_ID;
SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS ORG
WHERE EXISTS
(
SELECT 1 FROM OKC_K_HEADERS_B K
WHERE NOT EXISTS
(
SELECT
'1'
FROM HR_ORGANIZATION_INFORMATION ORGINF
WHERE ORGINF.ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND ORGINF.ORGANIZATION_ID = K.AUTHORING_ORG_ID
)
AND K.AUTHORING_ORG_ID = ORG.ORGANIZATION_ID);
SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS ORG
WHERE NOT EXISTS
(
SELECT /*+ NO_UNNEST */
'1'
FROM HR_ORGANIZATION_INFORMATION ORGINF
WHERE ORGINF.ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND ORGINF.ORGANIZATION_ID = ORG.ORGANIZATION_ID
)
AND ORG.ORGANIZATION_ID IN
(SELECT /*+ PARALLEL(K) */
AUTHORING_ORG_ID
FROM OKC_K_HEADERS_B K);
SELECT DISTINCT AUTHORING_ORG_ID
FROM OKC_K_HEADERS_B;
SELECT '1' FROM OKC_ARTICLES_ALL
WHERE article_title = p_article_title
AND org_id = G_GLOBAL_ORG_ID
AND standard_yn = 'Y'
AND rownum < 2;
l_org_name_tbl.DELETE;
l_org_name_tbl.DELETE;
UPDATE OKC_STD_ART_VERSIONS_TL
SET TEXT = ' '
WHERE TEXT IS NULL;
INSERT INTO OKC_FOLDERS_ALL_B (
FOLDER_ID,
OBJECT_VERSION_NUMBER,
ORG_ID,
SAT_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT
OKC_FOLDERS_ALL_B_S1.NEXTVAL,
1,
l_org_id_tbl(i),
LOOKUP_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
FROM FND_LOOKUP_VALUES LKU
WHERE LOOKUP_TYPE = 'OKC_ARTICLE_SET'
AND LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_FOLDERS_ALL_B FLD
WHERE FLD.SAT_CODE = LKU.LOOKUP_CODE
AND ORG_ID = l_org_id_tbl(i));
INSERT INTO OKC_FOLDERS_ALL_TL (
FOLDER_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
FOLDER_ID,
LANGUAGE,
SOURCE_LANG
) SELECT
LKU.MEANING,
LKU.DESCRIPTION,
LKU.CREATED_BY,
LKU.CREATION_DATE,
LKU.LAST_UPDATE_DATE,
LKU.LAST_UPDATED_BY,
LKU.LAST_UPDATE_LOGIN,
FLD.FOLDER_ID,
LKU.LANGUAGE,
LKU.LANGUAGE
FROM FND_LOOKUP_VALUES LKU , OKC_FOLDERS_ALL_B FLD
WHERE LOOKUP_CODE = SAT_CODE
AND LOOKUP_TYPE = 'OKC_ARTICLE_SET'
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_FOLDERS_ALL_TL FLDT
WHERE FLDT.FOLDER_ID = FLD.FOLDER_ID
AND FLDT.LANGUAGE = LKU.LANGUAGE) ;
article_version_id_tbl.DELETE;
article_text_tbl.DELETE;
start_date_tbl.DELETE;
artv_sav_release_tbl.DELETE;
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,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT
article_id_tbl(i),
article_title_tbl(i),
G_GLOBAL_ORG_ID,
article_number_tbl(i),
'Y', -- Standard YN
'S', -- Article Intent
article_language_tbl(i),
sbt_code,
system_reference_code_tbl(i), -- Orig System Reference Code
id, -- Orig System Reference ID1
NULL, -- Orig System Reference ID2
'N', -- CZ Transfer Status Flag
attribute_category,
substrb(attribute1,1,150),
substrb(attribute2,1,150),
substrb(attribute3,1,150),
substrb(attribute4,1,150),
substrb(attribute5,1,150),
substrb(attribute6,1,150),
substrb(attribute7,1,150),
substrb(attribute8,1,150),
substrb(attribute9,1,150),
substrb(attribute10,1,150),
substrb(attribute11,1,150),
substrb(attribute12,1,150),
substrb(attribute13,1,150),
substrb(attribute14,1,150),
substrb(attribute15,1,150),
G_PROGRAM_ID,
G_PROGRAM_LOGIN_ID,
G_PROGRAM_APPL_ID,
G_REQUEST_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY, -- Created By
CREATION_DATE, -- Creation Date
LAST_UPDATED_BY, -- Last Updated By
LAST_UPDATE_LOGIN, -- Last Update Login
sysdate -- Last Update Date
FROM OKC_STD_ARTICLES_B
WHERE art_process_status_tbl(i) = 'S'
AND id = art_system_reference_id1_tbl(i);
INSERT INTO OKC_ARTICLE_VERSIONS(
ARTICLE_VERSION_ID,
ARTICLE_ID,
ARTICLE_VERSION_NUMBER,
ARTICLE_TEXT,
PROVISION_YN,
INSERT_BY_REFERENCE,
LOCK_TEXT,
GLOBAL_YN,
ARTICLE_LANGUAGE,
ARTICLE_STATUS,
SAV_RELEASE,
START_DATE,
END_DATE,
STD_ARTICLE_VERSION_ID,
DISPLAY_NAME,
TRANSLATED_YN,
ARTICLE_DESCRIPTION,
DATE_APPROVED,
DEFAULT_SECTION,
REFERENCE_SOURCE,
REFERENCE_TEXT,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
ADDITIONAL_INSTRUCTIONS,
VARIATION_DESCRIPTION,
ADOPTION_TYPE,
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)
SELECT
t_article_version_id_tbl(i),
t_article_id_tbl(i),
t_art_version_number_tbl(i), -- Article Version Number
text, -- Article Text
'N', -- Provision Yn
'N', -- Insert by Reference
'N', -- Lock Text
'Y', -- Global Yn
t_art_language_tbl(i),
'APPROVED', -- Article Status
b.sav_release, -- Sav Release
t_start_date_tbl(i), -- Start Date
t_end_date_tbl(i), -- End Date
NULL, -- Std Article Version Id
t_display_name_tbl(i), -- Display Name
NULL, -- Translated Yn
t.short_description,
sysdate, -- Date Approved
NULL, -- Default Section
'OKCMIGRATE', -- Reference Source
NULL, -- Reference Text
t_system_reference_code_tbl(i), -- Orig System Reference Code
t_ver_system_reference_id1_tbl(i), -- System Reference ID1
NULL, -- Orig System Reference Id2
NULL, -- Additional Instructions
NULL, -- Variation Description
NULL, -- Adoption Type
G_PROGRAM_ID,
G_PROGRAM_LOGIN_ID,
G_PROGRAM_APPL_ID,
G_REQUEST_ID,
b.attribute_category,
substrb(b.attribute1,1,150),
substrb(b.attribute2,1,150),
substrb(b.attribute3,1,150),
substrb(b.attribute4,1,150),
substrb(b.attribute5,1,150),
substrb(b.attribute6,1,150),
substrb(b.attribute7,1,150),
substrb(b.attribute8,1,150),
substrb(b.attribute9,1,150),
substrb(b.attribute10,1,150),
substrb(b.attribute11,1,150),
substrb(b.attribute12,1,150),
substrb(b.attribute13,1,150),
substrb(b.attribute14,1,150),
substrb(b.attribute15,1,150), b.object_version_number, -- Object Version Number
b.created_by, -- Created By
b.creation_date, -- Creation Date
b.last_updated_by, -- Last Updated By
b.last_update_login, -- Last Update Login
SYSDATE -- Last Update Date
FROM OKC_STD_ART_VERSIONS_B B,
OKC_STD_ART_VERSIONS_TL T
WHERE
B.SAE_ID = t_ver_system_reference_id1_tbl(i)
AND B.SAV_RELEASE = t_artv_sav_release_tbl(i)
AND T.LANGUAGE = t_ver_language_tbl(i)
AND T.SAE_ID = B.SAE_ID
AND T.SAV_RELEASE = B.SAV_RELEASE
AND EXISTS
(SELECT 1 FROM OKC_ARTICLES_ALL ART WHERE
ART.ARTICLE_ID = t_article_id_tbl(i) AND
ART.ORIG_SYSTEM_REFERENCE_ID1 = TO_CHAR(B.SAE_ID) AND
ART.ORIG_SYSTEM_REFERENCE_ID1 = t_ver_system_reference_id1_tbl(i));
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 /*+ ORDERED USE_NL(INC,TAR) */
SRC.ARTICLE_ID source_article_id,
TAR.ARTICLE_ID target_article_id,
ORG.ORGANIZATION_ID,
'INCOMPATIBLE',
INC.object_version_number,
INC.created_by,
INC.creation_date,
INC.last_updated_by,
INC.last_update_login,
INC.last_update_date
FROM OKC_ARTICLES_ALL SRC, OKC_STD_ART_INCMPTS INC, OKC_ARTICLES_ALL TAR,
HR_ORGANIZATION_INFORMATION ORG
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND SRC.orig_system_reference_id1 = INC.SAE_ID
AND SRC.orig_system_reference_code in ('OKCMIGNEW' , 'OKCMIGORIG')
AND TAR.orig_system_reference_id1 = TO_CHAR(INC.SAE_ID_FOR)
AND TAR.orig_system_reference_code = SRC.orig_system_reference_code
AND SRC.article_id = article_id_tbl(i)
AND art_process_status_tbl(i) = 'S'
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_ARTICLE_RELATNS_ALL REL1
WHERE rel1.source_article_id = src.article_id and
rel1.target_article_id = tar.article_id and
rel1.org_id = org.organization_id);
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 /*+ ORDERED USE_NL(INC,SRC) */
SRC.ARTICLE_ID source_article_id,
TAR.ARTICLE_ID target_article_id,
ORG.ORGANIZATION_ID,
'INCOMPATIBLE',
INC.object_version_number,
INC.created_by,
INC.creation_date,
INC.last_updated_by,
INC.last_update_login,
INC.last_update_date
FROM OKC_ARTICLES_ALL TAR, OKC_STD_ART_INCMPTS INC, OKC_ARTICLES_ALL SRC,
HR_ORGANIZATION_INFORMATION ORG
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND SRC.orig_system_reference_id1 = TO_CHAR(INC.SAE_ID)
AND SRC.orig_system_reference_code in ('OKCMIGNEW' , 'OKCMIGORIG')
AND TAR.orig_system_reference_id1 = INC.SAE_ID_FOR
AND TAR.orig_system_reference_code = SRC.orig_system_reference_code
AND TAR.article_id = article_id_tbl(i)
AND art_process_status_tbl(i) = 'S'
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_ARTICLE_RELATNS_ALL REL1
WHERE rel1.source_article_id = src.article_id and
rel1.target_article_id = tar.article_id and
rel1.org_id = org.organization_id);
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 /*+ ORDERED USE_NL(INC,TAR) */
TAR.ARTICLE_ID target_article_id,
SRC.ARTICLE_ID source_article_id,
ORG.ORGANIZATION_ID,
'INCOMPATIBLE',
INC.object_version_number,
INC.created_by,
INC.creation_date,
INC.last_updated_by,
INC.last_update_login,
INC.last_update_date
FROM OKC_ARTICLES_ALL SRC, OKC_STD_ART_INCMPTS INC, OKC_ARTICLES_ALL TAR,
HR_ORGANIZATION_INFORMATION ORG
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND SRC.orig_system_reference_id1 = INC.SAE_ID
AND SRC.orig_system_reference_code in ('OKCMIGNEW' , 'OKCMIGORIG')
AND TAR.orig_system_reference_id1 = TO_CHAR(INC.SAE_ID_FOR)
AND TAR.orig_system_reference_code = SRC.orig_system_reference_code
AND SRC.article_id = article_id_tbl(i)
AND art_process_status_tbl(i) = 'S'
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_ARTICLE_RELATNS_ALL REL1
WHERE rel1.source_article_id = src.article_id and
rel1.target_article_id = tar.article_id and
rel1.org_id = org.organization_id);
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 /*+ ORDERED USE_NL(INC,SRC) */
TAR.ARTICLE_ID target_article_id,
SRC.ARTICLE_ID source_article_id,
ORG.ORGANIZATION_ID,
'INCOMPATIBLE',
INC.object_version_number,
INC.created_by,
INC.creation_date,
INC.last_updated_by,
INC.last_update_login,
INC.last_update_date
FROM OKC_ARTICLES_ALL TAR, OKC_STD_ART_INCMPTS INC, OKC_ARTICLES_ALL SRC,
HR_ORGANIZATION_INFORMATION ORG
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND SRC.orig_system_reference_id1 = TO_CHAR(INC.SAE_ID)
AND SRC.orig_system_reference_code in ('OKCMIGNEW' , 'OKCMIGORIG')
AND TAR.orig_system_reference_id1 = INC.SAE_ID_FOR
AND TAR.orig_system_reference_code = SRC.orig_system_reference_code
AND TAR.article_id = article_id_tbl(i)
AND art_process_status_tbl(i) = 'S'
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_ARTICLE_RELATNS_ALL REL1
WHERE rel1.source_article_id = src.article_id and
rel1.target_article_id = tar.article_id and
rel1.org_id = org.organization_id);
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
t_article_version_id_tbl(i),
'ADOPTED',
organization_id,
'APPROVED',
NULL,
1.0,
G_User_Id,
sysdate,
G_User_Id,
G_Login_Id,
sysdate
FROM HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND ORGANIZATION_ID <> G_GLOBAL_ORG_ID
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_ARTICLE_ADOPTIONS
WHERE GLOBAL_ARTICLE_VERSION_ID = t_article_version_id_tbl(i)
AND LOCAL_ORG_ID = ORGANIZATION_ID);
UPDATE OKC_K_ARTICLES_B B
SET ARTICLE_VERSION_ID = t_article_version_id_tbl(i),
SAV_SAE_ID = t_article_id_tbl(i),
ORIG_ARTICLE_ID = t_article_id_tbl(i)
WHERE OLD_ID IN
(SELECT TL.ID FROM OKC_K_ARTICLES_TL TL
WHERE sav_sav_release=t_artv_sav_release_tbl(i)
AND language=l_language
AND text is NULL )
AND sav_sae_id = t_ver_system_reference_id1_tbl(i)
AND l_language = t_art_language_tbl(i)
AND ARTICLE_VERSION_ID IS NULL;
UPDATE OKC_K_ARTICLES_BH B
SET ARTICLE_VERSION_ID = t_article_version_id_tbl(i),
SAV_SAE_ID = t_article_id_tbl(i),
ORIG_ARTICLE_ID = t_article_id_tbl(i)
WHERE (OLD_ID, MAJOR_VERSION) IN
(SELECT TL.ID, TL.MAJOR_VERSION FROM OKC_K_ARTICLES_TLH TL
WHERE sav_sav_release=t_artv_sav_release_tbl(i)
AND TL.ID = B.OLD_ID
AND TL.MAJOR_VERSION = B.MAJOR_VERSION
AND language=l_language
AND text is NULL )
AND sav_sae_id = t_ver_system_reference_id1_tbl(i)
AND l_language = t_art_language_tbl(i)
AND ARTICLE_VERSION_ID IS NULL;
INSERT INTO OKC_FOLDER_CONTENTS
(
FOLDER_ID,
MEMBER_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
SELECT
FOLDER_ID,
article_id_tbl(i),
1.0,
SM.CREATED_BY,
SM.CREATION_DATE,
SM.LAST_UPDATED_BY,
SM.LAST_UPDATE_LOGIN,
SM.LAST_UPDATE_DATE
FROM OKC_STD_ART_SET_MEMS SM, OKC_FOLDERS_ALL_B FLD
WHERE FLD.SAT_CODE = SM.SAT_CODE
AND SAE_ID = art_system_reference_id1_tbl(i)
AND system_reference_code_tbl(i) = 'OKCMIGORIG'
AND art_process_status_tbl(i) = 'S'
AND NOT EXISTS
(SELECT /*+ NO_UNNEST */
1 FROM OKC_FOLDER_CONTENTS
WHERE FOLDER_ID = FLD.FOLDER_ID
AND MEMBER_ID = article_id_tbl(i));
update records in FND_ATTACHED_DOCUMENTS table
ENTITY_NAME[varchar2(40)] : old(STD_ARTICLE_VERSIONS_B), new (?)
PK1_VALUE[varchar2(100)] : old (sae_id:number), new (article_version_id:number)
PK2_VALUE[varchar2(100)] : old (sav_release:varchar), new ( NULL)
LAST_UPDATE_DATE : sysdate
LAST_UPDATE_LOGIN:
LAST_UPDATED_BY:
*/
---------------------------------------------------------------------
BEGIN
G_context := 'ATT';
UPDATE FND_ATTACHED_DOCUMENTS
SET ENTITY_NAME = 'OKC_ARTICLE_VERSIONS',
PK1_VALUE = to_char(t_article_version_id_tbl(i)),
PK2_VALUE = NULL,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = G_Login_Id,
LAST_UPDATED_BY = G_User_Id
WHERE ENTITY_NAME = 'OKC_STD_ARTICLES_B'
AND PK1_VALUE = t_ver_system_reference_id1_tbl(i)
AND PK2_VALUE = t_artv_sav_release_tbl(i)
AND EXISTS
(SELECT 1 FROM OKC_ARTICLES_ALL ART WHERE
ART.ARTICLE_ID = t_article_id_tbl(i) AND
ART.ORIG_SYSTEM_REFERENCE_ID1 = t_ver_system_reference_id1_tbl(i));
UPDATE OKC_STD_ART_SET_MEMS MEM
SET SAE_ID = article_id_tbl(i)
WHERE SAE_ID = art_system_reference_id1_tbl(i)
AND system_reference_code_tbl(i) = 'OKCMIGORIG'
AND art_process_status_tbl(i) = 'S';
ver_language_tbl.DELETE;
article_title_tbl.DELETE;
article_number_tbl.DELETE;
article_id_tbl.DELETE;
article_language_tbl.DELETE;
system_reference_code_tbl.DELETE;
art_system_reference_id1_tbl.DELETE;
article_version_id_tbl.DELETE;
start_date_tbl.DELETE;
article_text_tbl.DELETE;
artv_sav_release_tbl.DELETE;
t_ver_language_tbl.DELETE;
t_art_language_tbl.DELETE;
t_article_id_tbl.DELETE;
t_article_version_id_tbl.DELETE;
t_art_version_number_tbl.DELETE;
t_start_date_tbl.DELETE;
t_end_date_tbl.DELETE;
t_article_text_tbl.DELETE;
t_artv_sav_release_tbl.DELETE;
art_process_status_tbl.DELETE;
ver_process_status_tbl.DELETE;
t_ver_system_reference_id1_tbl.DELETE;
t_system_reference_code_tbl.DELETE;
t_display_name_tbl.DELETE;
display_name_tbl.DELETE;
ver_language_tbl.DELETE;
article_title_tbl.DELETE;
article_number_tbl.DELETE;
article_id_tbl.DELETE;
article_language_tbl.DELETE;
system_reference_code_tbl.DELETE;
art_system_reference_id1_tbl.DELETE;
article_version_id_tbl.DELETE;
start_date_tbl.DELETE;
article_text_tbl.DELETE;
artv_sav_release_tbl.DELETE;
t_ver_language_tbl.DELETE;
t_art_language_tbl.DELETE;
t_article_id_tbl.DELETE;
t_article_version_id_tbl.DELETE;
t_art_version_number_tbl.DELETE;
t_start_date_tbl.DELETE;
t_end_date_tbl.DELETE;
t_article_text_tbl.DELETE;
t_artv_sav_release_tbl.DELETE;
art_process_status_tbl.DELETE;
ver_process_status_tbl.DELETE;
t_ver_system_reference_id1_tbl.DELETE;
t_system_reference_code_tbl.DELETE;
t_display_name_tbl.DELETE;
display_name_tbl.DELETE;
l_org_id_tbl.DELETE;
l_org_name_tbl.DELETE;