[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
FROM DUAL;
SELECT
total_rows_processed,total_rows_failed,
f.meaning entity_meaning
FROM OKC_ART_INT_BATPROCS_ALL a,
fnd_lookups f
WHERE UPPER(a.entity) = f.lookup_code
and f.lookup_type = 'OKC_ARTICLE_IMPORT_ENTITY'
and a.batch_process_id = p_batch_procs_id
and a.batch_number = p_batch_number
and a.entity = p_entity
and rownum =1;
SELECT a.interface_id,a.entity,error_description,
b.article_title,b.article_number,
hr.name org_name
from okc_art_int_errors a,okc_art_interface_all b,
hr_organization_units hr
where a.interface_id = b.interface_id
and b.org_id = hr.organization_id
and b.batch_number = p_batch_number
and a.batch_process_id = p_batch_procs_id
and a.entity = p_entity
and a.entity = 'CLAUSE';
SELECT a.interface_id,a.entity,error_description,
b.variable_code,b.variable_name
from okc_art_int_errors a,okc_variables_interface b
where a.interface_id = b.interface_id
and b.batch_number = p_batch_number
and a.batch_process_id = p_batch_procs_id
and a.entity = p_entity
and a.entity = 'VARIABLE';
SELECT a.interface_id,a.entity,error_description,
b.flex_value,b.flex_value_set_name
from okc_art_int_errors a,okc_vs_values_interface b
where a.interface_id = b.interface_id
and b.batch_number = p_batch_number
and a.batch_process_id = p_batch_procs_id
and a.entity = p_entity
and a.entity = 'VALUE';
SELECT a.interface_id,a.entity,error_description,
b.flex_value_set_name
from okc_art_int_errors a,okc_valuesets_interface b
where a.interface_id = b.interface_id
and b.batch_number = p_batch_number
and a.batch_process_id = p_batch_procs_id
and a.entity = p_entity
and a.entity = 'VALUESET';
SELECT a.interface_id,a.entity,error_description,
b.source_article_title,b.target_article_title,relationship_type,
hr.name org_name
from okc_art_int_errors a,okc_art_rels_interface b,
hr_organization_units hr
where a.interface_id = b.interface_id
and b.batch_number = p_batch_number
and a.batch_process_id = p_batch_procs_id
and b.org_id = hr.organization_id
and a.entity = p_entity
and a.entity = 'RELATIONSHIP';
l_insert_errors_exception EXCEPTION;
l_insert_errors_error CONSTANT VARCHAR2(1) := 'X';
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'OKC_YN' and lookup_code = cp_code;
SELECT import_source
FROM OKC_ART_INT_BATPROCS_ALL
WHERE batch_process_id = p_batch_procs_id
and batch_number = p_batch_number
and rownum =1;
SELECT a.source_file_name,
nvl(hr.name,' ') org_name,
--to_char( a.start_date,'DD-MON-YYYY HH24:MI:SS') start_date,
fnd_date.date_to_displaydt( a.start_date) start_date,
f.meaning validate_meaning,
nvl(f1.meaning,' ') global_flag_meaning,
nvl(f2.meaning,' ') status_meaning
FROM OKC_ART_INT_BATPROCS_ALL a,
fnd_lookups f,fnd_lookups f1,fnd_lookups f2,hr_organization_units hr
WHERE a.validate_only_yn = f.lookup_code
and f.lookup_type = 'OKC_YN'
and a.global_flag = f1.lookup_code (+)
and f1.lookup_type (+)= 'OKC_YN'
and a.clause_Status = f2.lookup_code (+)
and f2.lookup_type (+) = 'OKC_ARTICLE_STATUS'
and a.org_id = hr.organization_id (+)
and a.batch_process_id = p_batch_procs_id
and batch_number = p_batch_number
and a.import_source IS NOT NULL
and rownum =1;
SELECT
fnd_date.date_to_displaydt( a.start_date) start_date,
f.meaning validate_meaning,a.fetch_size
FROM OKC_ART_INT_BATPROCS_ALL a,
fnd_lookups f
WHERE a.validate_only_yn = f.lookup_code
and f.lookup_type = 'OKC_YN'
and a.batch_process_id = p_batch_procs_id
and a.batch_number = p_batch_number
and a.import_source IS NULL
and rownum =1;
SELECT DISTINCT a.org_id org_id,nvl(hr.name,' ') org_name
FROM okc_art_interface_all a,hr_organization_units hr
WHERE a.org_id IS NOT NULL
AND batch_number = p_batch_number
AND a.org_id = hr.organization_id(+)
UNION ALL
SELECT DISTINCT a.org_id org_id,nvl(hr.name,' ') org_name
FROM okc_art_rels_interface a,hr_organization_units hr
WHERE a.org_id IS NOT NULL
AND batch_number = p_batch_number
AND a.org_id = hr.organization_id(+)
AND NOT EXISTS
(SELECT 1 FROM okc_art_interface_all b
WHERE a.org_id = b.org_id
AND b.batch_number = p_batch_number);
p_msg_name => 'OKC_ART_WARN_PUR_ALL_DELETED');
DELETE FROM OKC_ART_INT_ERRORS
WHERE batch_process_id in
( SELECT batch_process_id
FROM OKC_ART_INT_BATPROCS_ALL
WHERE org_id = G_CURRENT_ORG_ID
AND batch_number in
(SELECT batch_number
FROM OKC_ART_INTERFACE_ALL
WHERE org_id = G_CURRENT_ORG_ID));
DELETE FROM OKC_ART_INTERFACE_ALL
WHERE org_id = G_CURRENT_ORG_ID;
DELETE FROM OKC_VARIABLES_INTERFACE ;
DELETE FROM OKC_ART_RELS_INTERFACE
WHERE org_id = G_CURRENT_ORG_ID ;
DELETE FROM OKC_VALUESETS_INTERFACE ;
DELETE FROM OKC_VS_VALUES_INTERFACE ;
DELETE FROM OKC_ART_INT_ERRORS
WHERE interface_id in ( SELECT interface_id
FROM OKC_ART_INTERFACE_ALL
WHERE last_update_date >= trunc(l_start_date)
AND last_update_date <= l_end_date
AND (batch_number = p_batch_number OR p_batch_number IS NULL)
AND (process_status = p_process_status OR p_process_status IS NULL)
AND org_id = G_CURRENT_ORG_ID);
DELETE FROM OKC_ART_INTERFACE_ALL
WHERE last_update_date >= trunc(l_start_date)
AND last_update_date <= l_end_date
AND (batch_number = p_batch_number OR p_batch_number IS NULL)
AND (process_status = p_process_status OR p_process_status IS NULL)
AND org_id = G_CURRENT_ORG_ID;
DELETE FROM OKC_VARIABLES_INTERFACE
WHERE last_update_date >= trunc(l_start_date)
AND last_update_date <= l_end_date
AND (batch_number = p_batch_number OR p_batch_number IS NULL)
AND (process_status = p_process_status OR p_process_status IS NULL)
;
DELETE FROM OKC_ART_RELS_INTERFACE
WHERE last_update_date >= trunc(l_start_date)
AND last_update_date <= l_end_date
AND (batch_number = p_batch_number OR p_batch_number IS NULL)
AND (process_status = p_process_status OR p_process_status IS NULL)
AND org_id = G_CURRENT_ORG_ID;
DELETE FROM OKC_VALUESETS_INTERFACE
WHERE last_update_date >= trunc(l_start_date)
AND last_update_date <= l_end_date
AND (batch_number = p_batch_number OR p_batch_number IS NULL)
AND (process_status = p_process_status OR p_process_status IS NULL)
;
DELETE FROM OKC_VS_VALUES_INTERFACE
WHERE last_update_date >= trunc(l_start_date)
AND last_update_date <= l_end_date
AND (batch_number = p_batch_number OR p_batch_number IS NULL)
AND (process_status = p_process_status OR p_process_status IS NULL)
;
DELETE FROM OKC_ART_INT_ERRORS
WHERE batch_process_id in ( SELECT batch_process_id
FROM OKC_ART_INT_BATPROCS_ALL
WHERE org_id = G_CURRENT_ORG_ID
AND batch_number not in
(SELECT batch_number
FROM OKC_ART_INTERFACE_ALL
WHERE org_id = G_CURRENT_ORG_ID));
SELECT DISTINCT a.org_id org_id,nvl(hr.name,' ') org_name
FROM okc_art_interface_all a,hr_organization_units hr
WHERE a.org_id IS NOT NULL
AND batch_number = p_batch_number
AND a.org_id = hr.organization_id(+)
UNION ALL
SELECT DISTINCT a.org_id org_id,nvl(hr.name,' ') org_name
FROM okc_art_rels_interface a,hr_organization_units hr
WHERE a.org_id IS NOT NULL
AND batch_number = p_batch_number
AND a.org_id = hr.organization_id(+)
AND NOT EXISTS
(SELECT 1 FROM okc_art_interface_all b
WHERE a.org_id = b.org_id
AND b.batch_number = p_batch_number);
SELECT
OKC_ART_INT_BATPROCS_ALL_S1.nextval into lc_batch_process_id
FROM DUAL;
INSERT INTO OKC_ART_INT_BATPROCS_ALL
(
BATCH_PROCESS_ID,
OBJECT_VERSION_NUMBER,
BATCH_NUMBER,
ORG_ID,
VALIDATE_ONLY_YN,
FETCH_SIZE,
START_DATE,
END_DATE,
TOTAL_ROWS_PROCESSED,
TOTAL_ROWS_FAILED,
TOTAL_ROWS_WARNED,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ENTITY
)
VALUES
(
lc_batch_process_id,
1.0,
p_batch_number,
G_CURRENT_ORG_ID,
p_validate_only,
p_fetchsize,
sysdate,
NULL,
NULL,
NULL,
NULL,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_appl_id,
l_request_id ,
l_entity_tbl_type(i)
) returning BATCH_PROCESS_ID INTO l_batch_process_id;
l_entity_tbl_type.delete;
UPDATE OKC_ART_INT_BATPROCS_ALL
SET
TOTAL_ROWS_PROCESSED = l_tot_art_rows_processed,
TOTAL_ROWS_FAILED = l_tot_art_rows_failed,
TOTAL_ROWS_WARNED = l_tot_art_rows_warned,
END_DATE = SYSDATE,
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
BATCH_PROCESS_ID = l_batch_process_id
AND ENTITY = 'Clause';
UPDATE OKC_ART_INT_BATPROCS_ALL
SET
TOTAL_ROWS_PROCESSED = l_tot_rel_rows_processed,
TOTAL_ROWS_FAILED = l_tot_rel_rows_failed,
TOTAL_ROWS_WARNED = l_tot_rel_rows_warned,
END_DATE = SYSDATE,
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
BATCH_PROCESS_ID = l_batch_process_id
AND ENTITY = 'Relationship' ;
SELECT OKC_ARTICLES_ALL_S1.NEXTVAL FROM DUAL;
SELECT OKC_ARTICLE_VERSIONS_S1.NEXTVAL FROM DUAL;
| PROCEDURE insert_error_array
| This procedure will insert the error array built by build_error_array
+====================================================================*/
PROCEDURE insert_error_array(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
l_user_id NUMBER;
l_insert_errors_exception EXCEPTION;
l_insert_errors_error CONSTANT VARCHAR2(1) := 'X';
INSERT INTO OKC_ART_INT_ERRORS
(
BATCH_PROCESS_ID,
INTERFACE_ID,
ERROR_NUMBER,
OBJECT_VERSION_NUMBER,
ERROR_TYPE,
ERROR_DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ENTITY
)
VALUES
(
err_batch_process_id_tbl(i_err),
err_interface_id_tbl(i_err),
err_error_number_tbl(i_err),
err_object_version_number_tbl(i_err),
err_error_type_tbl(i_err),
err_error_description_tbl(i_err),
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
l_program_id,
l_program_login_id,
l_program_appl_id,
l_request_id,
err_entity_tbl(i_err)
);
err_batch_process_id_tbl.DELETE;
err_article_title_tbl.DELETE;
err_interface_id_tbl.DELETE;
err_error_number_tbl.DELETE;
err_object_version_number_tbl.DELETE;
err_error_type_tbl.DELETE;
err_entity_tbl.DELETE;
err_error_description_tbl.DELETE;
okc_debug.log('500: Leaving insert_error_array because of EXCEPTION: '||sqlerrm, 2);
x_return_status := l_insert_errors_error;
END insert_error_array;
insert_error_array(
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'OKC_YN' and lookup_code = cp_code;
SELECT NAME
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = cp_org_id;
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'OKC_ARTICLE_IMPORT_ENTITY' and lookup_code = cp_ent_code;
l_insert_errors_exception EXCEPTION;
l_insert_errors_error CONSTANT VARCHAR2(1) := 'X';
SELECT
INTRFC.INTERFACE_ID ,
INTRFC.BATCH_NUMBER ,
INTRFC.OBJECT_VERSION_NUMBER ,
INTRFC.ARTICLE_TITLE ,
INTRFC.ORG_ID ,
INTRFC.PROCESS_STATUS ,
INTRFC.ACTION ,
NVL(INTRFC.ARTICLE_NUMBER, ART.ARTICLE_NUMBER) ,
NVL(INTRFC.ARTICLE_INTENT, ART.ARTICLE_INTENT) ,
ART.ARTICLE_INTENT X_ARTICLE_INTENT ,
INTRFC.ARTICLE_LANGUAGE ,
ART.ARTICLE_LANGUAGE X_ARTICLE_LANGUAGE ,
NVL(INTRFC.ARTICLE_TYPE, ART.ARTICLE_TYPE) ,
ART.ARTICLE_ID ,
INTRFC.ART_SYSTEM_REFERENCE_CODE,
INTRFC.ART_SYSTEM_REFERENCE_ID1,
INTRFC.ART_SYSTEM_REFERENCE_ID2,
INTRFC.ARTICLE_VERSION_NUMBER ,
INTRFC.ARTICLE_TEXT ,
INTRFC.PROVISION_YN ,
INTRFC.INSERT_BY_REFERENCE ,
INTRFC.LOCK_TEXT ,
INTRFC.GLOBAL_YN ,
INTRFC.ARTICLE_STATUS ,
INTRFC.START_DATE ,
INTRFC.END_DATE ,
INTRFC.DISPLAY_NAME ,
INTRFC.ARTICLE_DESCRIPTION ,
INTRFC.DATE_APPROVED ,
INTRFC.DEFAULT_SECTION ,
INTRFC.REFERENCE_SOURCE ,
INTRFC.REFERENCE_TEXT ,
INTRFC.VER_SYSTEM_REFERENCE_CODE ,
INTRFC.VER_SYSTEM_REFERENCE_ID1 ,
INTRFC.VER_SYSTEM_REFERENCE_ID2 ,
INTRFC.ADDITIONAL_INSTRUCTIONS ,
INTRFC.DATE_PUBLISHED,
INTRFC.ATTRIBUTE_CATEGORY,
INTRFC.ATTRIBUTE1 ,
INTRFC.ATTRIBUTE2 ,
INTRFC.ATTRIBUTE3 ,
INTRFC.ATTRIBUTE4 ,
INTRFC.ATTRIBUTE5 ,
INTRFC.ATTRIBUTE6 ,
INTRFC.ATTRIBUTE7 ,
INTRFC.ATTRIBUTE8 ,
INTRFC.ATTRIBUTE9 ,
INTRFC.ATTRIBUTE10 ,
INTRFC.ATTRIBUTE11 ,
INTRFC.ATTRIBUTE12 ,
INTRFC.ATTRIBUTE13 ,
INTRFC.ATTRIBUTE14 ,
INTRFC.ATTRIBUTE15,
TO_NUMBER(NULL) ARTICLE_VERSION_ID
FROM OKC_ART_INTERFACE_ALL INTRFC, OKC_ARTICLES_ALL ART
WHERE nvl(PROCESS_STATUS,'*') NOT IN ('W', 'S')
AND INTRFC.ORG_ID = cp_local_org_id
AND BATCH_NUMBER = cp_batch_number
AND RTRIM(INTRFC.ARTICLE_TITLE) = ART.ARTICLE_TITLE(+)
AND INTRFC.ORG_ID = ART.ORG_ID(+)
AND ART.STANDARD_YN(+) = 'Y'
ORDER BY RTRIM(INTRFC.ARTICLE_TITLE) ASC;
SELECT
ARTICLE_VERSION_ID,
ARTICLE_VERSION_NUMBER,
ARTICLE_STATUS,
START_DATE
FROM OKC_ARTICLE_VERSIONS A
WHERE article_id = cp_article_id
and start_date = (SELECT max(start_date)
FROM okc_article_versions
WHERE article_id = a.article_id);
SELECT
ARTICLE_INTENT,
ARTICLE_LANGUAGE
FROM OKC_ARTICLES_ALL
WHERE ARTICLE_ID = cp_article_id;
SELECT lookup_code
FROM fnd_lookups
WHERE lookup_type = 'OKC_SUBJECT'
AND lookup_code = cp_article_type
AND start_date_active <= trunc(sysdate)
AND nvl(end_date_active,sysdate+1) >= trunc(sysdate);
SELECT FLX.FLEX_VALUE_SET_ID,
BVB.VARIABLE_TYPE
FROM
OKC_BUS_VARIABLES_B BVB,
FND_FLEX_VALUE_SETS FLX
WHERE BVB.VARIABLE_CODE = cp_variable_code
--AND BVB.VARIABLE_TYPE = 'U'
AND FLX.FLEX_VALUE_SET_ID(+) = BVB.VALUE_SET_ID;
SELECT BVT.VARIABLE_NAME
FROM
OKC_BUS_VARIABLES_TL BVT
WHERE BVT.VARIABLE_CODE = cp_variable_code
AND BVT.LANGUAGE = cp_lang;
TYPE l_inf_insert_by_reference IS TABLE OF OKC_ART_INTERFACE_ALL.INSERT_BY_REFERENCE%TYPE INDEX BY BINARY_INTEGER ;
inf_insert_by_reference_tbl l_inf_insert_by_reference ;
l_variables_to_insert_tbl OKC_ARTICLES_GRP.variable_code_tbl_type;
l_variables_to_delete_tbl OKC_ARTICLES_GRP.variable_code_tbl_type;
inf_insert_by_reference_tbl ,
inf_lock_text_tbl ,
inf_global_yn_tbl ,
inf_article_status_tbl ,
inf_start_date_tbl ,
inf_end_date_tbl ,
inf_display_name_tbl ,
inf_article_description_tbl ,
inf_date_approved_tbl ,
inf_default_section_tbl ,
inf_reference_source_tbl ,
inf_reference_text_tbl ,
inf_ver_reference_code_tbl ,
inf_ver_reference_id1_tbl ,
inf_ver_reference_id2_tbl ,
inf_instructions_tbl ,
inf_date_published_tbl ,
inf_attribute_category_tbl ,
inf_attribute1_tbl ,
inf_attribute2_tbl ,
inf_attribute3_tbl ,
inf_attribute4_tbl ,
inf_attribute5_tbl ,
inf_attribute6_tbl ,
inf_attribute7_tbl ,
inf_attribute8_tbl ,
inf_attribute9_tbl ,
inf_attribute10_tbl ,
inf_attribute11_tbl ,
inf_attribute12_tbl ,
inf_attribute13_tbl ,
inf_attribute14_tbl ,
inf_attribute15_tbl ,
inf_article_version_id_tbl LIMIT p_fetchsize;
p_insert_by_reference => inf_insert_by_reference_tbl(i),
p_lock_text => inf_lock_text_tbl(i),
p_global_yn => inf_global_yn_tbl(i),
p_article_language => inf_article_language_tbl(i),
p_article_status => l_tmp_article_status,
p_sav_release => NULL,
p_start_date => inf_start_date_tbl(i),
p_end_date => inf_end_date_tbl(i),
p_std_article_version_id => NULL,
p_display_name => inf_display_name_tbl(i),
p_translated_yn => NULL,
p_article_description => inf_article_description_tbl(i),
p_date_approved => inf_date_approved_tbl(i),
p_default_section => inf_default_section_tbl(i),
p_reference_source => inf_reference_source_tbl(i),
p_reference_text => inf_reference_text_tbl(i),
p_orig_system_reference_code => inf_art_reference_code_tbl(i),
p_orig_system_reference_id1 => inf_art_reference_id1_tbl(i),
p_orig_system_reference_id2 => inf_art_reference_id2_tbl(i),
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_current_org_id => G_CURRENT_ORG_ID,
p_additional_instructions => inf_instructions_tbl(i),
p_variation_description => NULL,
p_date_published => inf_date_published_tbl(i) ,
p_attribute_category => inf_attribute_category_tbl(i) ,
p_attribute1 => inf_attribute1_tbl(i) ,
p_attribute2 => inf_attribute2_tbl(i) ,
p_attribute3 => inf_attribute3_tbl(i) ,
p_attribute4 => inf_attribute4_tbl(i) ,
p_attribute5 => inf_attribute5_tbl(i) ,
p_attribute6 => inf_attribute6_tbl(i) ,
p_attribute7 => inf_attribute7_tbl(i) ,
p_attribute8 => inf_attribute8_tbl(i) ,
p_attribute9 => inf_attribute9_tbl(i) ,
p_attribute10 => inf_attribute10_tbl(i) ,
p_attribute11 => inf_attribute11_tbl(i) ,
p_attribute12 => inf_attribute12_tbl(i) ,
p_attribute13 => inf_attribute13_tbl(i) ,
p_attribute14 => inf_attribute14_tbl(i) ,
p_attribute15 => inf_attribute15_tbl(i)
);
p_insert_by_reference => inf_insert_by_reference_tbl(i),
p_lock_text => inf_lock_text_tbl(i),
p_global_yn => inf_global_yn_tbl(i),
p_article_language => inf_article_language_tbl(i),
p_article_status => l_tmp_article_status,
p_sav_release => NULL,
p_start_date => inf_start_date_tbl(i),
p_end_date => inf_end_date_tbl(i),
p_std_article_version_id => NULL,
p_display_name => inf_display_name_tbl(i),
p_translated_yn => NULL,
p_article_description => inf_article_description_tbl(i),
p_date_approved => inf_date_approved_tbl(i),
p_default_section => inf_default_section_tbl(i),
p_reference_source => inf_reference_source_tbl(i),
p_reference_text => inf_reference_text_tbl(i),
p_orig_system_reference_code => inf_art_reference_code_tbl(i),
p_orig_system_reference_id1 => inf_art_reference_id1_tbl(i),
p_orig_system_reference_id2 => inf_art_reference_id2_tbl(i),
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_current_org_id => G_CURRENT_ORG_ID,
p_additional_instructions => inf_instructions_tbl(i),
p_variation_description => NULL,
p_date_published => inf_date_published_tbl(i) ,
p_attribute_category => inf_attribute_category_tbl(i) ,
p_attribute1 => inf_attribute1_tbl(i) ,
p_attribute2 => inf_attribute2_tbl(i) ,
p_attribute3 => inf_attribute3_tbl(i) ,
p_attribute4 => inf_attribute4_tbl(i) ,
p_attribute5 => inf_attribute5_tbl(i) ,
p_attribute6 => inf_attribute6_tbl(i) ,
p_attribute7 => inf_attribute7_tbl(i) ,
p_attribute8 => inf_attribute8_tbl(i) ,
p_attribute9 => inf_attribute9_tbl(i) ,
p_attribute10 => inf_attribute10_tbl(i) ,
p_attribute11 => inf_attribute11_tbl(i) ,
p_attribute12 => inf_attribute12_tbl(i) ,
p_attribute13 => inf_attribute13_tbl(i) ,
p_attribute14 => inf_attribute14_tbl(i) ,
p_attribute15 => inf_attribute15_tbl(i)
);
OKC_ARTICLES_GRP.UPDATE_ARTICLE_VARIABLES (
p_article_version_id => inf_article_version_id_tbl(i),
p_variable_code_tbl => l_variable_code_tbl,
p_do_dml => 'N', -- indicates not to do dml in the API
x_variables_to_insert_tbl => l_variables_to_insert_tbl,
x_variables_to_delete_tbl => l_variables_to_delete_tbl,
x_return_status => api_return_status);
IF l_variables_to_insert_tbl.COUNT > 0 THEN
FOR k in l_variables_to_insert_tbl.FIRST ..l_variables_to_insert_tbl.LAST LOOP
artv_variable_code_tbl(j) := l_variables_to_insert_tbl(k);
IF l_variables_to_delete_tbl.COUNT > 0 THEN
FOR k in l_variables_to_delete_tbl.FIRST ..l_variables_to_delete_tbl.LAST LOOP
artv_variable_code_tbl(j) := l_variables_to_delete_tbl(k);
l_variables_to_insert_tbl.DELETE;
l_variables_to_delete_tbl.DELETE;
l_variable_code_tbl.DELETE;
g_temp_clob_tbl.DELETE;
g_temp_clob_tbl.DELETE;
g_temp_clob_tbl.DELETE;
g_temp_clob_tbl.DELETE;
l_context := 'INSERTING NEW CLAUSE';
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,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT
inf_article_id_tbl(i),
inf_article_title_tbl(i),
G_CURRENT_ORG_ID,
inf_article_number_tbl(i),
'Y',
inf_article_intent_tbl(i),
nvl(inf_article_language_tbl(i),l_userenv_lang),
inf_article_type_tbl(i),
inf_art_reference_code_tbl(i),
inf_art_reference_id1_tbl(i),
inf_art_reference_id2_tbl(i),
'N',
l_program_id,
l_program_login_id,
l_program_appl_id,
l_request_id,
1.0,
l_user_id,
sysdate,
l_user_id,
l_login_id,
sysdate
FROM DUAL
WHERE inf_action_tbl(i) = 'N' and
inf_process_status_tbl(i) in ('S', 'W') ;
l_context := 'INSERTING NEW VERSION OF CLAUSE';
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,
DATE_PUBLISHED,
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
inf_article_version_id_tbl(i),
inf_article_id_tbl(i),
inf_article_version_number_tbl(i),
g_temp_clob_tbl(i),
inf_provision_yn_tbl(i),
inf_insert_by_reference_tbl(i),
inf_lock_text_tbl(i),
inf_global_yn_tbl(i),
nvl(inf_article_language_tbl(i),l_userenv_lang),
inf_article_status_tbl(i),
NULL,
trunc(inf_start_date_tbl(i)),
trunc(inf_end_date_tbl(i)),
NULL,
inf_display_name_tbl(i),
NULL,
inf_article_description_tbl(i),
inf_date_approved_tbl(i),
inf_default_section_tbl(i),
inf_reference_source_tbl(i),
inf_reference_text_tbl(i),
inf_ver_reference_code_tbl(i),
inf_ver_reference_id1_tbl(i),
inf_ver_reference_id2_tbl(i),
inf_instructions_tbl(i),
NULL,
inf_date_published_tbl(i),
inf_adoption_type_tbl(i),
l_program_id,
l_program_login_id,
l_program_appl_id,
l_request_id,
inf_attribute_category_tbl(i),
inf_attribute1_tbl(i),
inf_attribute2_tbl(i),
inf_attribute3_tbl(i),
inf_attribute4_tbl(i),
inf_attribute5_tbl(i),
inf_attribute6_tbl(i),
inf_attribute7_tbl(i),
inf_attribute8_tbl(i),
inf_attribute9_tbl(i),
inf_attribute10_tbl(i),
inf_attribute11_tbl(i),
inf_attribute12_tbl(i),
inf_attribute13_tbl(i),
inf_attribute14_tbl(i),
inf_attribute15_tbl(i),
1.0,
l_user_id,
sysdate,
l_user_id,
l_login_id,
sysdate
FROM OKC_ARTICLES_ALL
WHERE inf_action_tbl(i) IN ( 'N', 'V')
AND inf_process_status_tbl(i) in ('S', 'W')
AND article_id = inf_article_id_tbl(i);
l_context := 'INSERT ASSOCIATION FOR VARIABLES';
INSERT INTO OKC_ARTICLE_VARIABLES
(
ARTICLE_VERSION_ID ,
VARIABLE_CODE ,
OBJECT_VERSION_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT
artv_article_version_id_tbl(i),
artv_variable_code_tbl(i),
1.0,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id
FROM OKC_ARTICLE_VERSIONS
WHERE artv_action_tbl(i) = 'N'
AND article_version_id = artv_article_version_id_tbl(i);
l_context := 'DELETE ASSOCIATION FOR VARIABLES';
DELETE FROM OKC_ARTICLE_VARIABLES
WHERE VARIABLE_CODE = artv_variable_code_tbl(i)
AND ARTICLE_VERSION_ID = artv_article_version_id_tbl(i)
AND artv_action_tbl(i) = 'D';
artv_article_version_id_tbl.DELETE;
artv_variable_code_tbl.DELETE;
artv_action_tbl.DELETE;
UPDATE OKC_ARTICLE_VERSIONS
SET END_DATE = trunc(inf_start_date_tbl(i)-1),
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = object_version_number + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE inf_action_tbl(i) = 'V'
AND inf_process_status_tbl(i) in ('S','W')
AND inf_earlier_version_id_tbl(i) <> -99
AND article_version_id = inf_earlier_version_id_tbl(i);
UPDATE OKC_ARTICLES_ALL
SET ARTICLE_TYPE = inf_article_type_tbl(i),
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = object_version_number + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE inf_action_tbl(i) in ('U', 'V')
AND inf_process_status_tbl(i) in ('S','W')
AND article_id = inf_article_id_tbl(i);
UPDATE OKC_ARTICLE_VERSIONS
SET END_DATE = trunc(inf_end_date_tbl(i)),
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = object_version_number + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE inf_action_tbl(i) in ('D')
AND inf_process_status_tbl(i) in ('S','W')
AND article_version_id = inf_article_version_id_tbl(i);
UPDATE OKC_ARTICLE_VERSIONS VO SET (
ARTICLE_TEXT,
PROVISION_YN,
INSERT_BY_REFERENCE,
LOCK_TEXT,
GLOBAL_YN,
ARTICLE_STATUS,
START_DATE,
END_DATE,
DISPLAY_NAME,
ARTICLE_DESCRIPTION,
DEFAULT_SECTION,
REFERENCE_SOURCE,
REFERENCE_TEXT,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
ADDITIONAL_INSTRUCTIONS,
DATE_PUBLISHED,
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,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE) = (
SELECT
g_temp_clob_tbl(i),
inf_provision_yn_tbl(i),
inf_insert_by_reference_tbl(i),
inf_lock_text_tbl(i),
inf_global_yn_tbl(i),
inf_article_status_tbl(i),
trunc(inf_start_date_tbl(i)),
trunc(inf_end_date_tbl(i)),
inf_display_name_tbl(i),
inf_article_description_tbl(i),
inf_default_section_tbl(i),
inf_reference_source_tbl(i),
inf_reference_text_tbl(i),
inf_ver_reference_code_tbl(i),
inf_ver_reference_id1_tbl(i),
inf_ver_reference_id2_tbl(i),
inf_instructions_tbl(i),
inf_date_published_tbl(i),
l_program_id,
l_program_login_id,
l_program_appl_id,
l_request_id,
inf_attribute_category_tbl(i),
inf_attribute1_tbl(i),
inf_attribute2_tbl(i),
inf_attribute3_tbl(i),
inf_attribute4_tbl(i),
inf_attribute5_tbl(i),
inf_attribute6_tbl(i),
inf_attribute7_tbl(i),
inf_attribute8_tbl(i),
inf_attribute9_tbl(i),
inf_attribute10_tbl(i),
inf_attribute11_tbl(i),
inf_attribute12_tbl(i),
inf_attribute13_tbl(i),
inf_attribute14_tbl(i),
inf_attribute15_tbl(i),
vi.object_version_number+1,
l_user_id,
l_login_id,
sysdate
FROM OKC_ART_INTERFACE_ALL VI
WHERE inf_action_tbl(i) = 'U'
AND nvl(process_status, 'E') in ('E')
AND inf_process_status_tbl(i) in ('S', 'W')
AND VI.interface_id = inf_interface_id_tbl(i))
WHERE vo.article_version_id = inf_article_version_id_tbl(i)
AND inf_process_status_tbl(i) in ('S','W')
AND inf_action_tbl(i) = 'U';
UPDATE OKC_ART_INTERFACE_ALL
SET
-- We don't want to update process_status to 'S' or 'W' in validation_mode
-- because it is not going to be picked up in next run if we do so
PROCESS_STATUS = decode(p_validate_only||inf_process_status_tbl(i)||l_bulk_failed,
'NEN','E',
'NSN','S',
'NWN','W',
'NEY','E',
'NSY',NULL,
'NWY',NULL,
'YEY','E',
'YEN','E',
'NFY','E',
'YFY','E',
'NFN','E',
'YFN','E',NULL),
ARTICLE_VERSION_NUMBER = inf_article_version_number_tbl(i),
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = inf_object_version_number_tbl(i) + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
interface_id = inf_interface_id_tbl(i);
p_msg_name => 'OKC_ART_INT_UPDATE_FAILED');
insert_error_array(
x_return_status => api_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF (api_return_status = l_insert_errors_error) THEN
NULL;
inf_interface_id_tbl.DELETE;
inf_batch_number_tbl.DELETE;
inf_object_version_number_tbl.DELETE;
inf_article_title_tbl.DELETE;
inf_org_id_tbl.DELETE;
inf_process_status_tbl.DELETE;
inf_action_tbl.DELETE;
inf_article_number_tbl.DELETE;
inf_article_intent_tbl.DELETE;
inf_article_language_tbl.DELETE;
inf_article_type_tbl.DELETE;
inf_article_id_tbl.DELETE;
inf_art_reference_code_tbl.DELETE;
inf_art_reference_id1_tbl.DELETE;
inf_art_reference_id2_tbl.DELETE;
inf_article_version_number_tbl.DELETE;
inf_article_text_tbl.DELETE;
inf_provision_yn_tbl.DELETE;
inf_insert_by_reference_tbl.DELETE;
inf_lock_text_tbl.DELETE;
inf_global_yn_tbl.DELETE;
inf_article_status_tbl.DELETE;
inf_start_date_tbl.DELETE;
inf_end_date_tbl.DELETE;
inf_display_name_tbl.DELETE;
inf_article_description_tbl.DELETE;
inf_date_approved_tbl.DELETE;
inf_default_section_tbl.DELETE;
inf_reference_source_tbl.DELETE;
inf_reference_text_tbl.DELETE;
inf_ver_reference_code_tbl.DELETE;
inf_ver_reference_id1_tbl.DELETE;
inf_ver_reference_id2_tbl.DELETE;
inf_instructions_tbl.DELETE;
inf_date_published_tbl.DELETE;
inf_attribute_category_tbl.DELETE;
inf_attribute1_tbl.DELETE;
inf_attribute2_tbl.DELETE;
inf_attribute3_tbl.DELETE;
inf_attribute4_tbl.DELETE;
inf_attribute5_tbl.DELETE;
inf_attribute6_tbl.DELETE;
inf_attribute7_tbl.DELETE;
inf_attribute8_tbl.DELETE;
inf_attribute9_tbl.DELETE;
inf_attribute10_tbl.DELETE;
inf_attribute11_tbl.DELETE;
inf_attribute12_tbl.DELETE;
inf_attribute13_tbl.DELETE;
inf_attribute14_tbl.DELETE;
inf_attribute15_tbl.DELETE;
inf_article_version_id_tbl.DELETE;
inf_earlier_version_id_tbl.DELETE;
inf_adoption_type_tbl.DELETE;
err_batch_process_id_tbl.DELETE;
err_article_title_tbl.DELETE;
err_interface_id_tbl.DELETE;
err_error_number_tbl.DELETE;
err_object_version_number_tbl.DELETE;
err_error_type_tbl.DELETE;
err_entity_tbl.DELETE;
err_error_description_tbl.DELETE;
g_temp_clob_tbl.DELETE;
UPDATE OKC_ART_INT_BATPROCS_ALL
SET
TOTAL_ROWS_PROCESSED = l_tot_rows_processed,
TOTAL_ROWS_FAILED = l_tot_rows_failed,
TOTAL_ROWS_WARNED = l_tot_rows_warned,
END_DATE = SYSDATE,
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
BATCH_PROCESS_ID = l_batch_process_id
AND ENTITY = 'Clause';
insert_error_array(
x_return_status => api_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
commit; -- Final commit for status update
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
g_temp_clob_tbl.DELETE;
WHEN l_insert_errors_exception THEN
--
-- In this exception handling, we don't insert error array again
-- because error happend in the module
--
IF (l_debug = 'Y') THEN
okc_debug.log('500: Leaving Articles_Import because of EXCEPTION in insert_error_array: '||sqlerrm, 2);
g_temp_clob_tbl.DELETE;
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
g_temp_clob_tbl.DELETE;
l_insert_errors_exception EXCEPTION;
l_insert_errors_error CONSTANT VARCHAR2(1) := 'X';
SELECT
VARINT.INTERFACE_ID ,
VARINT.BATCH_NUMBER ,
VARINT.OBJECT_VERSION_NUMBER ,
RTRIM(VARINT.VARIABLE_CODE) VARIABLE_CODE ,
VARINT.VARIABLE_NAME ,
VARINT.PROCESS_STATUS ,
VARINT.ACTION ,
VARINT.LANGUAGE ,
VARINT.DESCRIPTION,
VARINT.VARIABLE_TYPE,
VARINT.VARIABLE_INTENT,
VARINT.CONTRACT_EXPERT_YN,
VARINT.XPRT_VALUE_SET_NAME,
VARINT.DISABLED_YN,
VARINT.EXTERNAL_YN,
VARINT.VARIABLE_DATATYPE,
VARINT.APPLICATION_ID,
VARINT.VALUE_SET_NAME,
VARINT.VARIABLE_DEFAULT_VALUE,
VARINT.ORIG_SYSTEM_REFERENCE_CODE,
VARINT.ORIG_SYSTEM_REFERENCE_ID1,
VARINT.ORIG_SYSTEM_REFERENCE_ID2,
VARINT.DATE_PUBLISHED,
VARINT.ATTRIBUTE_CATEGORY,
VARINT.ATTRIBUTE1 ,
VARINT.ATTRIBUTE2 ,
VARINT.ATTRIBUTE3 ,
VARINT.ATTRIBUTE4 ,
VARINT.ATTRIBUTE5 ,
VARINT.ATTRIBUTE6 ,
VARINT.ATTRIBUTE7 ,
VARINT.ATTRIBUTE8 ,
VARINT.ATTRIBUTE9 ,
VARINT.ATTRIBUTE10 ,
VARINT.ATTRIBUTE11 ,
VARINT.ATTRIBUTE12 ,
VARINT.ATTRIBUTE13 ,
VARINT.ATTRIBUTE14 ,
VARINT.ATTRIBUTE15 ,
VAR.VARIABLE_CODE EXISTING_CODE,
VAR.DATE_PUBLISHED EXISTING_DPUBLISHED,
VARTL.LANGUAGE EXISTING_LANGUAGE
FROM OKC_VARIABLES_INTERFACE VARINT, OKC_BUS_VARIABLES_B VAR,
OKC_BUS_VARIABLES_TL VARTL
WHERE nvl(PROCESS_STATUS,'*') NOT IN ('W', 'S')
AND BATCH_NUMBER = cp_batch_number
AND RTRIM(VARINT.VARIABLE_CODE) = VAR.VARIABLE_CODE(+)
AND RTRIM(VARINT.VARIABLE_CODE) = VARTL.VARIABLE_CODE(+)
AND VARINT.LANGUAGE = VARTL.LANGUAGE(+)
ORDER BY RTRIM(VARINT.VARIABLE_CODE) ASC;
SELECT '1' FROM OKC_BUS_VARIABLES_B
WHERE variable_code = cp_var_code;
SELECT
'1'
FROM OKC_BUS_VARIABLES_B B,OKC_BUS_VARIABLES_TL TL
WHERE B.VARIABLE_CODE = TL.VARIABLE_CODE AND
B.VARIABLE_INTENT = cp_var_intent AND
TL.VARIABLE_NAME = cp_var_name;
SELECT
'1'
FROM OKC_BUS_VARIABLES_B B,OKC_BUS_VARIABLES_TL TL
WHERE B.VARIABLE_CODE = TL.VARIABLE_CODE AND
B.VARIABLE_CODE = cp_var_code;
SELECT
FLX.FLEX_VALUE_SET_ID ,
DECODE(FORMAT_TYPE,'C','V','X','D',FORMAT_TYPE) FORMAT_TYPE
FROM FND_FLEX_VALUE_SETS FLX
WHERE FLX.FLEX_VALUE_SET_NAME = cp_valset_name;
SELECT '1'
FROM OKC_VALUESETS_INTERFACE
WHERE flex_value_set_name = cp_valset_name
AND batch_number = cp_batch_number
AND nvl(process_status,'X') not in ('E');
SELECT '1'
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I','B')
AND language_code = lang_code;
l_context := 'INSERTING NEW VARIABLE INTO B TABLE';
INSERT INTO OKC_BUS_VARIABLES_B(
VARIABLE_CODE,
OBJECT_VERSION_NUMBER,
VARIABLE_TYPE,
EXTERNAL_YN,
VARIABLE_INTENT,
CONTRACT_EXPERT_YN,
DISABLED_YN,
VARIABLE_DATATYPE,
APPLICATION_ID,
VALUE_SET_ID,
VARIABLE_DEFAULT_VALUE,
XPRT_VALUE_SET_NAME,
DATE_PUBLISHED,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
-- 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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT
vinf_variable_code_tbl(i),
1.0,
vinf_variable_type_tbl(i),
vinf_external_yn_tbl(i),
vinf_variable_intent_tbl(i),
vinf_contract_expert_yn_tbl(i),
vinf_disabled_yn_tbl(i),
vinf_variable_datatype_tbl(i),
vinf_application_id_tbl(i),
vinf_value_set_id_tbl(i),
vinf_var_default_value_tbl(i),
vinf_xprt_value_set_name_tbl(i),
vinf_date_published_tbl(i),
vinf_system_reference_code_tbl(i),
vinf_system_reference_id1_tbl(i),
vinf_system_reference_id2_tbl(i),
-- l_program_id,
-- l_program_login_id,
-- l_program_appl_id,
-- l_request_id,
vinf_attribute_category_tbl(i),
vinf_attribute1_tbl(i),
vinf_attribute2_tbl(i),
vinf_attribute3_tbl(i),
vinf_attribute4_tbl(i),
vinf_attribute5_tbl(i),
vinf_attribute6_tbl(i),
vinf_attribute7_tbl(i),
vinf_attribute8_tbl(i),
vinf_attribute9_tbl(i),
vinf_attribute10_tbl(i),
vinf_attribute11_tbl(i),
vinf_attribute12_tbl(i),
vinf_attribute13_tbl(i),
vinf_attribute14_tbl(i),
vinf_attribute15_tbl(i),
l_user_id,
sysdate,
l_user_id,
l_login_id,
sysdate
FROM DUAL
WHERE vinf_action_tbl(i) = 'N' and
vinf_process_status_tbl(i) in ('S', 'W') and
vinf_language_tbl(i) = USERENV('LANG');
l_context := 'INSERTING VARIABLE INTO TL TABLE';
INSERT INTO OKC_BUS_VARIABLES_TL(
VARIABLE_CODE,
VARIABLE_NAME,
LANGUAGE,
SOURCE_LANG,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT
vinf_variable_code_tbl(i),
vinf_variable_name_tbl(i),
vinf_language_tbl(i),
userenv('LANG'),
vinf_description_tbl(i),
l_user_id,
sysdate,
l_user_id,
l_login_id,
sysdate
FROM OKC_BUS_VARIABLES_B
WHERE vinf_action_tbl(i) IN ('N')
AND vinf_process_status_tbl(i) in ('S', 'W')
AND variable_code = vinf_variable_code_tbl(i);
UPDATE OKC_BUS_VARIABLES_B
SET DATE_PUBLISHED = vinf_date_published_tbl(i),
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE vinf_action_tbl(i) = ('U')
AND vinf_process_status_tbl(i) in ('S','W')
AND variable_code = vinf_variable_code_tbl(i)
AND vinf_existing_language_tbl(i) IS NOT NULL;
UPDATE OKC_BUS_VARIABLES_TL
SET DESCRIPTION = vinf_description_tbl(i),
--PROGRAM_ID = l_program_id,
--REQUEST_ID = l_request_id,
--PROGRAM_LOGIN_ID = l_program_login_id,
--PROGRAM_APPLICATION_ID = l_program_appl_id,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE vinf_action_tbl(i) = ('U')
AND vinf_process_status_tbl(i) in ('S','W')
AND variable_code = vinf_variable_code_tbl(i)
AND vinf_existing_language_tbl(i) IS NOT NULL;
l_context := 'INSERTING VARIABLE INTO TL TABLE FOR ACTION U';
INSERT INTO OKC_BUS_VARIABLES_TL(
VARIABLE_CODE,
VARIABLE_NAME,
LANGUAGE,
SOURCE_LANG,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT
vinf_variable_code_tbl(i),
vinf_variable_name_tbl(i),
vinf_language_tbl(i),
userenv('LANG'),
vinf_description_tbl(i),
l_user_id,
sysdate,
l_user_id,
l_login_id,
sysdate
FROM OKC_BUS_VARIABLES_B
WHERE vinf_action_tbl(i) = ('U')
AND vinf_process_status_tbl(i) in ('S', 'W')
AND variable_code = vinf_variable_code_tbl(i)
AND vinf_existing_language_tbl(i) IS NULL;
UPDATE OKC_BUS_VARIABLES_B
SET DISABLED_YN = vinf_disabled_yn_tbl(i),
--PROGRAM_ID = l_program_id,
--REQUEST_ID = l_request_id,
--PROGRAM_LOGIN_ID = l_program_login_id,
--PROGRAM_APPLICATION_ID = l_program_appl_id,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE vinf_action_tbl(i) = ('D')
AND vinf_process_status_tbl(i) in ('S','W')
AND variable_code = vinf_variable_code_tbl(i);
UPDATE OKC_VARIABLES_INTERFACE
SET
-- We don't want to update process_status to 'S' or 'W' in validation_mode
-- because it is not going to be picked up in next run if we do so
PROCESS_STATUS = decode(p_validate_only||vinf_process_status_tbl(i)||l_bulk_failed,
'NEN','E',
'NSN','S',
'NWN','W',
'NEY','E',
'NSY',NULL,
'NWY',NULL,
'YEY','E',
'YEN','E',
'NFY','E',
'YFY','E',
'NFN','E',
'YFN','E',NULL),
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = vinf_object_version_number_tbl(i) + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
interface_id = vinf_interface_id_tbl(i);
p_msg_name => 'OKC_VAR_INT_UPDATE_FAILED');
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF (x_return_status = l_insert_errors_error) THEN
NULL;
vinf_interface_id_tbl.DELETE;
vinf_batch_number_tbl.DELETE;
vinf_object_version_number_tbl.DELETE;
vinf_variable_code_tbl.DELETE;
vinf_variable_name_tbl.DELETE;
vinf_process_status_tbl.DELETE;
vinf_action_tbl.DELETE;
vinf_language_tbl.DELETE;
vinf_description_tbl.DELETE;
vinf_variable_type_tbl.DELETE;
vinf_variable_intent_tbl.DELETE;
vinf_contract_expert_yn_tbl.DELETE;
vinf_xprt_value_set_name_tbl.DELETE;
vinf_disabled_yn_tbl.DELETE;
vinf_external_yn_tbl.DELETE;
vinf_variable_datatype_tbl.DELETE;
vinf_application_id_tbl.DELETE;
vinf_value_set_name_tbl.DELETE;
vinf_var_default_value_tbl.DELETE;
vinf_system_reference_code_tbl.DELETE;
vinf_system_reference_id1_tbl.DELETE;
vinf_system_reference_id2_tbl.DELETE;
vinf_date_published_tbl.DELETE;
vinf_attribute_category_tbl.DELETE;
vinf_attribute1_tbl.DELETE;
vinf_attribute2_tbl.DELETE;
vinf_attribute3_tbl.DELETE;
vinf_attribute4_tbl.DELETE;
vinf_attribute5_tbl.DELETE;
vinf_attribute6_tbl.DELETE;
vinf_attribute7_tbl.DELETE;
vinf_attribute8_tbl.DELETE;
vinf_attribute9_tbl.DELETE;
vinf_attribute10_tbl.DELETE;
vinf_attribute11_tbl.DELETE;
vinf_attribute12_tbl.DELETE;
vinf_attribute13_tbl.DELETE;
vinf_attribute14_tbl.DELETE;
vinf_attribute15_tbl.DELETE;
vinf_value_set_id_tbl.DELETE;
vinf_existing_code_tbl.DELETE;
vinf_existing_dpublished_tbl.DELETE;
vinf_existing_language_tbl.DELETE;
err_batch_process_id_tbl.DELETE;
err_article_title_tbl.DELETE;
err_interface_id_tbl.DELETE;
err_error_number_tbl.DELETE;
err_object_version_number_tbl.DELETE;
err_error_type_tbl.DELETE;
err_entity_tbl.DELETE;
err_error_description_tbl.DELETE;
SAVEPOINT bulklanginsert;
okc_debug.log('1000: Entering Insert Missing Lang Records', 2);
INSERT INTO OKC_BUS_VARIABLES_TL (
VARIABLE_CODE,
VARIABLE_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
)
SELECT
VART.VARIABLE_CODE,
VART.VARIABLE_NAME,
VART.DESCRIPTION,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id,
LANG.LANGUAGE_CODE,
VART.SOURCE_LANG
FROM OKC_BUS_VARIABLES_TL VART,
FND_LANGUAGES LANG,
OKC_VARIABLES_INTERFACE VARI
WHERE LANG.INSTALLED_FLAG IN ('I', 'B')
AND VART.VARIABLE_CODE = RTRIM(VARI.VARIABLE_CODE)
AND NVL(VARI.PROCESS_STATUS,'*') = 'S'
AND VARI.BATCH_NUMBER = p_batch_number
AND VARI.ACTION <> 'D'
AND NOT EXISTS
(SELECT NULL
FROM OKC_BUS_VARIABLES_TL VART1
WHERE VART1.VARIABLE_CODE = VART.VARIABLE_CODE
AND VART1.LANGUAGE = LANG.LANGUAGE_CODE)
AND VART.ROWID = (SELECT MIN(VART2.ROWID)
FROM OKC_BUS_VARIABLES_TL VART2
WHERE VART2.VARIABLE_CODE = VART.VARIABLE_CODE)
AND VARI.ROWID = (SELECT MIN(VARI1.ROWID)
FROM OKC_VARIABLES_INTERFACE VARI1
WHERE RTRIM(VARI1.VARIABLE_CODE) = RTRIM(VARI.VARIABLE_CODE)
AND VARI1.ACTION <> 'D'
AND VARI1.BATCH_NUMBER = VARI.BATCH_NUMBER
AND NVL(VARI1.PROCESS_STATUS,'*') = 'S');
okc_debug.log('1100: Leaving Insert Missing Lang Records', 2);
okc_debug.log('1200: Leaving Missing langauge Insert because of EXCEPTION: '||l_context||sqlerrm, 2);
ROLLBACK TO SAVEPOINT bulklanginsert;
UPDATE OKC_ART_INT_BATPROCS_ALL
SET
TOTAL_ROWS_PROCESSED = l_tot_rows_processed,
TOTAL_ROWS_FAILED = l_tot_rows_failed,
TOTAL_ROWS_WARNED = l_tot_rows_warned,
END_DATE = SYSDATE,
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
BATCH_PROCESS_ID = l_batch_process_id
AND ENTITY = 'Variable';
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
commit; -- Final commit for status update
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
WHEN l_insert_errors_exception THEN
--
-- In this exception handling, we don't insert error array again
-- because error happend in the module
--
IF (l_debug = 'Y') THEN
okc_debug.log('5000: Leaving Variables_Import because of EXCEPTION in insert_error_array: '||sqlerrm, 2);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_insert_errors_exception EXCEPTION;
l_insert_errors_error CONSTANT VARCHAR2(1) := 'X';
SELECT
RELINT.INTERFACE_ID ,
RELINT.BATCH_NUMBER ,
RELINT.OBJECT_VERSION_NUMBER ,
RELINT.SOURCE_ARTICLE_TITLE ,
RELINT.TARGET_ARTICLE_TITLE ,
RELINT.ORG_ID,
RELINT.RELATIONSHIP_TYPE ,
RELINT.PROCESS_STATUS ,
RELINT.ACTION ,
RELS.ARTICLE_INTENT SOURCE_INTENT,
RELS.ARTICLE_ID SOURCE_ARTICLE_ID ,
RELT.ARTICLE_INTENT TARGET_INTENT,
RELT.ARTICLE_ID TARGET_ARTICLE_ID
FROM OKC_ART_RELS_INTERFACE RELINT,
OKC_ARTICLES_ALL RELS, OKC_ARTICLES_ALL RELT
WHERE nvl(PROCESS_STATUS,'*') NOT IN ('W', 'S')
AND BATCH_NUMBER = cp_batch_number
AND RELINT.ORG_ID = cp_local_org_id
AND RELINT.SOURCE_ARTICLE_TITLE = RELS.ARTICLE_TITLE(+)
AND RELINT.ORG_ID = RELS.ORG_ID(+)
AND RELINT.TARGET_ARTICLE_TITLE = RELT.ARTICLE_TITLE(+)
AND RELINT.ORG_ID = RELT.ORG_ID(+)
ORDER BY RELINT.SOURCE_ARTICLE_TITLE,RELINT.TARGET_ARTICLE_TITLE ASC;
SELECT '1' FROM OKC_ARTICLE_RELATNS_ALL
WHERE source_article_id = cp_src_article_id
AND target_article_id = cp_tar_article_id
AND org_id = cp_org_id
AND relationship_type = cp_rel_type;
SELECT '1'
FROM HR_ORGANIZATION_INFORMATION_V orgi, HR_ORGANIZATION_UNITS_V orgu
WHERE orgi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
AND orgi.organization_id = orgu.organization_id
AND orgi.organization_id = cp_org_id;
SELECT provision_yn FROM OKC_ARTICLE_VERSIONS
WHERE article_id = cp_article_id
AND article_version_number = 1;
SELECT '1'
FROM OKC_ART_INTERFACE_ALL
WHERE article_title = cp_article_title
AND org_id = cp_org_id
AND batch_number = cp_batch_number
AND nvl(process_status,'X') not in ('E');
l_context := 'INSERTING FIRST NEW RELATIONSHIP INTO TABLE';
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
rinf_source_article_id_tbl(i),
rinf_target_article_id_tbl(i),
rinf_org_id_tbl(i),
rinf_relationship_type_tbl(i),
1.0,
l_user_id,
sysdate,
l_user_id,
l_login_id,
sysdate
FROM DUAL
WHERE rinf_action_tbl(i) = 'N' and
rinf_process_status_tbl(i) in ('S', 'W') ;
INSERT INTO OKC_ARTICLE_RELATNS_ALL(
TARGET_ARTICLE_ID,
SOURCE_ARTICLE_ID,
ORG_ID,
RELATIONSHIP_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
SELECT
rinf_source_article_id_tbl(i),
rinf_target_article_id_tbl(i),
rinf_org_id_tbl(i),
rinf_relationship_type_tbl(i),
1.0,
l_user_id,
sysdate,
l_user_id,
l_login_id,
sysdate
FROM DUAL
WHERE rinf_action_tbl(i) = 'N' and
rinf_process_status_tbl(i) in ('S', 'W') ;
l_context := 'DELETE RELATIONSHIP FROM TABLE';
DELETE FROM OKC_ARTICLE_RELATNS_ALL
WHERE
SOURCE_ARTICLE_ID = rinf_source_article_id_tbl(i) AND
TARGET_ARTICLE_ID = rinf_target_article_id_tbl(i) AND
ORG_ID = rinf_org_id_tbl(i) AND
RELATIONSHIP_TYPE = rinf_relationship_type_tbl(i) AND
rinf_action_tbl(i) = 'D' AND
rinf_process_status_tbl(i) in ('S', 'W') ;
DELETE FROM OKC_ARTICLE_RELATNS_ALL
WHERE
TARGET_ARTICLE_ID = rinf_source_article_id_tbl(i) AND
SOURCE_ARTICLE_ID = rinf_target_article_id_tbl(i) AND
ORG_ID = rinf_org_id_tbl(i) AND
RELATIONSHIP_TYPE = rinf_relationship_type_tbl(i) AND
rinf_action_tbl(i) = 'D' AND
rinf_process_status_tbl(i) in ('S', 'W') ;
UPDATE OKC_ART_RELS_INTERFACE
SET
-- We don't want to update process_status to 'S' or 'W' in validation_mode
-- because it is not going to be picked up in next run if we do so
PROCESS_STATUS = decode(p_validate_only||rinf_process_status_tbl(i)||l_bulk_failed,
'NEN','E',
'NSN','S',
'NWN','W',
'NEY','E',
'NSY',NULL,
'NWY',NULL,
'YEY','E',
'YEN','E',
'NFY','E',
'YFY','E',
'NFN','E',
'YFN','E',NULL),
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = rinf_object_version_number_tbl(i) + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
interface_id = rinf_interface_id_tbl(i);
p_msg_name => 'OKC_REL_INT_UPDATE_FAILED');
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF (x_return_status = l_insert_errors_error) THEN
NULL;
rinf_interface_id_tbl.DELETE;
rinf_batch_number_tbl.DELETE;
rinf_object_version_number_tbl.DELETE;
rinf_source_article_title_tbl.DELETE;
rinf_target_article_title_tbl.DELETE;
rinf_org_id_tbl.DELETE;
rinf_relationship_type_tbl.DELETE;
rinf_process_status_tbl.DELETE;
rinf_action_tbl.DELETE;
rinf_source_intent_tbl.DELETE;
rinf_source_article_id_tbl.DELETE;
rinf_target_intent_tbl.DELETE;
rinf_target_article_id_tbl.DELETE;
err_batch_process_id_tbl.DELETE;
err_article_title_tbl.DELETE;
err_interface_id_tbl.DELETE;
err_error_number_tbl.DELETE;
err_object_version_number_tbl.DELETE;
err_error_type_tbl.DELETE;
err_entity_tbl.DELETE;
err_error_description_tbl.DELETE;
UPDATE OKC_ART_INT_BATPROCS_ALL
SET
TOTAL_ROWS_PROCESSED = l_tot_rows_processed,
TOTAL_ROWS_FAILED = l_tot_rows_failed,
TOTAL_ROWS_WARNED = l_tot_rows_warned,
END_DATE = SYSDATE,
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
BATCH_PROCESS_ID = l_batch_process_id
AND ENTITY = 'Relationship' ;
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
commit; -- Final commit for status update
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
WHEN l_insert_errors_exception THEN
--
-- In this exception handling, we don't insert error array again
-- because error happend in the module
--
IF (l_debug = 'Y') THEN
okc_debug.log('5000: Leaving Relationships_Import because of EXCEPTION in insert_error_array: '||sqlerrm, 2);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_insert_errors_exception EXCEPTION;
l_insert_errors_error CONSTANT VARCHAR2(1) := 'X';
SELECT
VSINT.INTERFACE_ID ,
VSINT.BATCH_NUMBER ,
VSINT.OBJECT_VERSION_NUMBER ,
VSINT.FLEX_VALUE_SET_NAME ,
VSINT.VALIDATION_TYPE ,
VSINT.PROCESS_STATUS ,
VSINT.ACTION ,
VSINT.FORMAT_TYPE ,
VSINT.MAXIMUM_SIZE ,
VSINT.DESCRIPTION ,
VSINT.MINIMUM_VALUE ,
VSINT.MAXIMUM_VALUE ,
VSINT.NUMBER_PRECISION ,
VSINT.UPPERCASE_ONLY_FLAG ,
VSINT.NUMBER_ONLY_FLAG
FROM OKC_VALUESETS_INTERFACE VSINT
WHERE nvl(PROCESS_STATUS,'*') NOT IN ('W', 'S')
AND BATCH_NUMBER = cp_batch_number
ORDER BY VSINT.FLEX_VALUE_SET_NAME ASC;
l_context := 'INSERTING NEW VALUESETS INTO TABLE';
UPDATE OKC_VALUESETS_INTERFACE
SET
-- We don't want to update process_status to 'S' or 'W' in validation_mode
-- because it is not going to be picked up in next run if we do so
PROCESS_STATUS = decode(p_validate_only||vsinf_process_status_tbl(i)||l_bulk_failed,
'NEN','E',
'NSN','S',
'NWN','W',
'NEY','E',
'NSY',NULL,
'NWY',NULL,
'YEY','E',
'YEN','E',
'NFY','E',
'YFY','E',
'NFN','E',
'YFN','E',NULL),
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = vsinf_obj_ver_number_tbl(i) + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
interface_id = vsinf_interface_id_tbl(i);
p_msg_name => 'OKC_VALSET_INT_UPDATE_FAILED');
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF (x_return_status = l_insert_errors_error) THEN
NULL;
vsinf_interface_id_tbl.DELETE;
vsinf_batch_number_tbl.DELETE;
vsinf_obj_ver_number_tbl.DELETE;
vsinf_flex_value_set_name_tbl.DELETE;
vsinf_validation_type_tbl.DELETE;
vsinf_process_status_tbl.DELETE;
vsinf_action_tbl.DELETE;
vsinf_format_type_tbl.DELETE;
vsinf_maximum_size_tbl.DELETE;
vsinf_description_tbl.DELETE;
vsinf_minimum_value_tbl.DELETE;
vsinf_maximum_value_tbl.DELETE;
vsinf_number_precision_tbl.DELETE;
vsinf_uppercase_only_flag_tbl.DELETE;
vsinf_number_only_flag_tbl.DELETE;
err_batch_process_id_tbl.DELETE;
err_article_title_tbl.DELETE;
err_interface_id_tbl.DELETE;
err_error_number_tbl.DELETE;
err_object_version_number_tbl.DELETE;
err_error_type_tbl.DELETE;
err_entity_tbl.DELETE;
err_error_description_tbl.DELETE;
UPDATE OKC_ART_INT_BATPROCS_ALL
SET
TOTAL_ROWS_PROCESSED = l_tot_rows_processed,
TOTAL_ROWS_FAILED = l_tot_rows_failed,
TOTAL_ROWS_WARNED = l_tot_rows_warned,
END_DATE = SYSDATE,
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
BATCH_PROCESS_ID = l_batch_process_id
AND ENTITY = 'Valueset';
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
commit; -- Final commit for status update
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
WHEN l_insert_errors_exception THEN
--
-- In this exception handling, we don't insert error array again
-- because error happend in the module
--
IF (l_debug = 'Y') THEN
okc_debug.log('5000: Leaving Fnd_flex_value_sets_Import because of EXCEPTION in insert_error_array: '||sqlerrm, 2);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
l_insert_errors_exception EXCEPTION;
l_insert_errors_error CONSTANT VARCHAR2(1) := 'X';
SELECT
VINT.INTERFACE_ID ,
VINT.BATCH_NUMBER ,
VINT.OBJECT_VERSION_NUMBER ,
VINT.FLEX_VALUE_SET_NAME ,
VINT.FLEX_VALUE ,
VINT.PROCESS_STATUS ,
VINT.ACTION ,
VINT.ENABLED_FLAG ,
VINT.START_DATE_ACTIVE ,
VINT.END_DATE_ACTIVE ,
VINT.LANGUAGE ,
VINT.DESCRIPTION ,
VINT.FLEX_VALUE_MEANING ,
VS.FLEX_VALUE_SET_ID ,
VS.VALIDATION_TYPE ,
VS.FORMAT_TYPE ,
VS.MAXIMUM_SIZE ,
VS.NUMBER_PRECISION ,
VS.ALPHANUMERIC_ALLOWED_FLAG,
VS.UPPERCASE_ONLY_FLAG ,
VS.NUMERIC_MODE_ENABLED_FLAG,
VS.MINIMUM_VALUE ,
VS.MAXIMUM_VALUE ,
TO_NUMBER(NULL) FLEX_VALUE_ID
FROM OKC_VS_VALUES_INTERFACE VINT,FND_FLEX_VALUE_SETS VS
WHERE VINT.FLEX_VALUE_SET_NAME = VS.FLEX_VALUE_SET_NAME
AND nvl(PROCESS_STATUS,'*') NOT IN ('W', 'S')
AND BATCH_NUMBER = cp_batch_number
UNION ALL
SELECT
VINT.INTERFACE_ID ,
VINT.BATCH_NUMBER ,
VINT.OBJECT_VERSION_NUMBER ,
VINT.FLEX_VALUE_SET_NAME ,
VINT.FLEX_VALUE ,
VINT.PROCESS_STATUS ,
VINT.ACTION ,
VINT.ENABLED_FLAG ,
VINT.START_DATE_ACTIVE ,
VINT.END_DATE_ACTIVE ,
VINT.LANGUAGE ,
VINT.DESCRIPTION ,
VINT.FLEX_VALUE_MEANING ,
TO_NUMBER(NULL) FLEX_VALUE_SET_ID ,
VS.VALIDATION_TYPE ,
VS.FORMAT_TYPE ,
VS.MAXIMUM_SIZE ,
VS.NUMBER_PRECISION ,
TO_CHAR(NULL) ALPHANUMERIC_ALLOWED_FLAG,
VS.UPPERCASE_ONLY_FLAG ,
VS.NUMBER_ONLY_FLAG NUMERIC_MODE_ENABLED_FLAG,
VS.MINIMUM_VALUE ,
VS.MAXIMUM_VALUE ,
TO_NUMBER(NULL) FLEX_VALUE_ID
FROM OKC_VS_VALUES_INTERFACE VINT,OKC_VALUESETS_INTERFACE VS
WHERE VINT.FLEX_VALUE_SET_NAME = VS.FLEX_VALUE_SET_NAME (+)
AND VINT.BATCH_NUMBER = VS.BATCH_NUMBER (+)
AND nvl(VS.PROCESS_STATUS,'*') NOT IN ('E')
AND VINT.BATCH_NUMBER = cp_batch_number
AND NOT EXISTS
( SELECT 1 FROM FND_FLEX_VALUE_SETS FVS
WHERE FVS.FLEX_VALUE_SET_NAME = VINT.FLEX_VALUE_SET_NAME)
ORDER BY FLEX_VALUE_SET_NAME,FLEX_VALUE ASC;
SELECT
B.FLEX_VALUE_SET_ID ,
B.FLEX_VALUE_ID ,
B.FLEX_VALUE ,
B.START_DATE_ACTIVE ,
B.END_DATE_ACTIVE
FROM FND_FLEX_VALUES B,FND_FLEX_VALUES_TL T
WHERE B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
AND T.LANGUAGE = userenv('LANG')
AND B.FLEX_VALUE = l_flex_value
AND B.FLEX_VALUE_SET_ID = l_flex_value_set_id;
SELECT '1'
FROM OKC_VALUESETS_INTERFACE
WHERE flex_value_set_name = cp_vs_name
AND batch_number = cp_batch_number
AND nvl(process_status,'X') not in ('E');
l_context := 'INSERTING NEW VALUES INTO TABLE';
FND_FLEX_VAL_API.UPDATE_INDEPENDENT_VSET_VALUE(
P_FLEX_VALUE_SET_NAME => vinf_flex_value_set_name_tbl(i),
P_FLEX_VALUE => vinf_flex_value_tbl(i),
--P_DESCRIPTION => vinf_description_tbl(i),
--P_ENABLED_FLAG => vinf_enabled_flag_tbl(i),
--P_START_DATE_ACTIVE => vinf_start_date_active_tbl(i),
P_END_DATE_ACTIVE => vinf_end_date_active_tbl(i),
--P_SUMMARY_FLAG => 'N',
--P_STRUCTURED_HIERARCHY_LEVEL => NULL,
--P_HIERARCHY_LEVEL => NULL,
X_STORAGE_VALUE => l_storage_value);
UPDATE OKC_VS_VALUES_INTERFACE
SET
-- We don't want to update process_status to 'S' or 'W' in validation_mode
-- because it is not going to be picked up in next run if we do so
PROCESS_STATUS = decode(p_validate_only||vinf_process_status_tbl(i)||l_bulk_failed,
'NEN','E',
'NSN','S',
'NWN','W',
'NEY','E',
'NSY',NULL,
'NWY',NULL,
'YEY','E',
'YEN','E',
'NFY','E',
'YFY','E',
'NFN','E',
'YFN','E',NULL),
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = vinf_obj_ver_number_tbl(i) + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
interface_id = vinf_interface_id_tbl(i);
p_msg_name => 'OKC_VAL_INT_UPDATE_FAILED');
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF (x_return_status = l_insert_errors_error) THEN
NULL;
vinf_interface_id_tbl.DELETE;
vinf_batch_number_tbl.DELETE;
vinf_obj_ver_number_tbl.DELETE;
vinf_flex_value_set_name_tbl.DELETE;
vinf_flex_value_tbl.DELETE;
vinf_process_status_tbl.DELETE;
vinf_action_tbl.DELETE;
vinf_enabled_flag_tbl.DELETE;
vinf_start_date_active_tbl.DELETE;
vinf_end_date_active_tbl.DELETE;
vinf_language_tbl.DELETE;
vinf_description_tbl.DELETE;
vinf_flex_value_meaning_tbl.DELETE;
vinf_flex_value_id_tbl.DELETE;
vinf_value_set_id_tbl.DELETE;
vinf_format_type_tbl.DELETE;
vinf_maximum_size_tbl.DELETE;
vinf_number_precision_tbl.DELETE;
vinf_alphanum_allowed_tbl.DELETE;
vinf_uppercase_only_tbl.DELETE;
vinf_num_mode_enabled_tbl.DELETE;
vinf_maximum_value_tbl.DELETE;
vinf_minimum_value_tbl.DELETE;
err_batch_process_id_tbl.DELETE;
err_article_title_tbl.DELETE;
err_interface_id_tbl.DELETE;
err_error_number_tbl.DELETE;
err_object_version_number_tbl.DELETE;
err_error_type_tbl.DELETE;
err_entity_tbl.DELETE;
err_error_description_tbl.DELETE;
UPDATE OKC_ART_INT_BATPROCS_ALL
SET
TOTAL_ROWS_PROCESSED = l_tot_rows_processed,
TOTAL_ROWS_FAILED = l_tot_rows_failed,
TOTAL_ROWS_WARNED = l_tot_rows_warned,
END_DATE = SYSDATE,
PROGRAM_ID = l_program_id,
REQUEST_ID = l_request_id,
PROGRAM_LOGIN_ID = l_program_login_id,
PROGRAM_APPLICATION_ID = l_program_appl_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
LAST_UPDATE_DATE = SYSDATE
WHERE
BATCH_PROCESS_ID = l_batch_process_id
AND ENTITY = 'Value';
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
commit; -- Final commit for status update
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
WHEN l_insert_errors_exception THEN
--
-- In this exception handling, we don't insert error array again
-- because error happend in the module
--
IF (l_debug = 'Y') THEN
okc_debug.log('5000: Leaving Fnd_flex_values_Import because of EXCEPTION in insert_error_array: '||sqlerrm, 2);
insert_error_array(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);