[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_amend_code_deleted CONSTANT VARCHAR2 (30) := 'DELETED';
g_amend_code_updated CONSTANT VARCHAR2 (30) := 'UPDATED';
'SELECT ''Y'' FROM '
|| l_from_table
|| ' WHERE 1 =1 AND '
|| l_entity_pk1_column
|| ' = '
|| l_entity_pk1_n;
'SELECT LOCK_by_document_type, LOCK_by_document_id FROM okc_k_entity_locks WHERE entity_name = '
|| ''''
|| p_entity_name
|| ''''
|| ' and entity_pk1 = '
|| ''''
|| p_entity_pk1
|| '''';
SELECT lock_by_entity_id, lock_by_document_type,
lock_by_document_id
FROM okc_k_entity_locks
WHERE entity_name = p_entity_name
AND entity_pk1 = p_entity_pk1
;
INSERT INTO okc_k_entity_locks
(k_entity_lock_id, entity_name, entity_pk1, entity_pk2,
entity_pk3, entity_pk4, entity_pk5, lock_by_entity_id,
lock_by_document_type, lock_by_document_id,
object_version_number, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
SELECT okc_k_entity_locks_s.NEXTVAL, p_entity_name, p_entity_pk1,
p_entity_pk2, p_entity_pk3, p_entity_pk4, p_entity_pk5,
p_lock_by_entity_id, p_lock_by_document_type, p_lock_by_document_id,
1, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id
FROM DUAL
WHERE NOT EXISTS (
SELECT 'Y'
FROM okc_k_entity_locks
WHERE entity_name = p_entity_name
AND lock_by_document_type = p_lock_by_document_type
AND lock_by_document_id = p_lock_by_document_id
AND lock_by_entity_id = p_lock_by_entity_id);
okc_k_entity_locks_pkg.insert_row
(p_entity_name => p_entity_name,
p_entity_pk1 => p_entity_pk1,
p_entity_pk2 => p_entity_pk2,
p_entity_pk3 => p_entity_pk3,
p_entity_pk4 => p_entity_pk4,
p_entity_pk5 => p_entity_pk5,
p_lock_by_entity_id => p_lock_by_entity_id,
p_lock_by_document_type => p_lock_by_document_type,
p_lock_by_document_id => p_lock_by_document_id,
p_object_version_number => 1,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.login_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_k_entity_lock_id => x_k_entity_lock_id
);
p_update_from_doc_type IN VARCHAR2,
p_update_from_doc_id IN NUMBER,
p_update_to_doc_type IN VARCHAR2,
p_update_to_doc_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1;
SELECT SOURCE.orig_system_reference_id1 old_id, target.ID new_id , klock.LOCK_by_entity_id
FROM okc_k_articles_b SOURCE -- Modification Doc
,
okc_k_articles_b target -- Base Doc
,
okc_k_entity_locks klock -- Locks Table
WHERE SOURCE.document_type = p_update_from_doc_type
AND SOURCE.document_id = p_update_from_doc_id
AND target.document_type = p_update_to_doc_type
AND target.document_id = p_update_to_doc_id
AND target.orig_system_reference_id1 = SOURCE.ID
AND klock.entity_name = G_CLAUSE_ENTITY
AND klock.entity_pk1 = TO_CHAR (SOURCE.orig_system_reference_id1);
SELECT SOURCE.orig_system_reference_id1 old_id, target.ID new_id, klock.lock_by_entity_id
FROM okc_sections_b SOURCE -- Modification Doc
,
okc_sections_b target -- Base Doc
,
okc_k_entity_locks klock -- Locks Table
WHERE SOURCE.document_type = p_update_from_doc_type
AND SOURCE.document_id = p_update_from_doc_id
AND target.document_type = p_update_to_doc_type
AND target.document_id = p_update_to_doc_id
AND target.orig_system_reference_id1 = SOURCE.ID
AND klock.entity_name = G_SECTION_ENTITY
AND klock.entity_pk1 = TO_CHAR (SOURCE.orig_system_reference_id1);
SELECT SOURCE.orig_system_reference_id1 old_id, target.ID new_id
FROM okc_sections_b SOURCE -- Modification Doc
,
okc_sections_b target -- Base Doc
,
okc_k_entity_locks klock -- Locks Table
WHERE SOURCE.document_type = p_update_from_doc_type
AND SOURCE.document_id = p_update_from_doc_id
AND target.document_type = p_update_to_doc_type
AND target.document_id = p_update_to_doc_id
AND target.orig_system_reference_id1 = SOURCE.ID
AND klock.entity_name = 'DUMMYSEC'
AND klock.entity_pk1 = TO_CHAR (SOURCE.orig_system_reference_id1);
SELECT SOURCE.orig_system_reference_id1 old_id, target.deliverable_id new_id , klock.LOCK_by_entity_id
FROM okc_deliverables SOURCE -- Modification Doc
,
okc_deliverables target -- Base Doc
,
okc_k_entity_locks klock -- Locks Table
WHERE SOURCE.business_document_type = p_update_from_doc_type
AND SOURCE.business_document_id = p_update_from_doc_id
AND target.business_document_type = p_update_to_doc_type
AND target.business_document_id = p_update_to_doc_id
AND target.orig_system_reference_id1 = SOURCE.DELIVERABLE_ID
AND klock.entity_name = 'DELIVERABLE'
AND klock.entity_pk1 = TO_CHAR (SOURCE.orig_system_reference_id1);
p_doc_type => p_update_from_doc_type,
p_doc_id => p_update_from_doc_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE okc_k_entity_locks
SET entity_pk1 = TO_CHAR (l_new_clause_id_tbl (i)),
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE entity_pk1 = TO_CHAR (l_old_clause_id_tbl (i))
AND entity_name = G_CLAUSE_ENTITY;
UPDATE OKC_K_ARTICLES_B
SET orig_system_reference_id1 = l_new_clause_id_tbl(i)
WHERE id = l_upd_clause_id_tbl(i);
UPDATE okc_k_entity_locks
SET entity_pk1 = l_new_scn_id_tbl (i)
WHERE entity_pk1 = l_old_scn_id_tbl (i) AND entity_name = G_SECTION_ENTITY;
UPDATE okc_sections_b
SET orig_system_reference_id1 = l_new_scn_id_tbl(i)
WHERE id = l_upd_sec_id_tbl(i);
UPDATE okc_k_entity_locks
SET entity_pk1 = l_new_scn_id_tbl (i)
WHERE entity_pk1 = l_old_scn_id_tbl (i)
AND entity_name = 'DUMMYSEC';
UPDATE okc_k_entity_locks
SET entity_pk1 = TO_CHAR (l_new_dlvbl_id_tbl (i)),
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE entity_pk1 = TO_CHAR (l_old_dlvbl_id_tbl (i))
AND entity_name = G_DLVBL_ENTITY;
UPDATE OKC_DELIVERABLES
SET orig_system_reference_id1 = l_new_dlvbl_id_tbl(i)
WHERE deliverable_id = l_upd_dlvbl_id_tbl(i);
DELETE FROM okc_k_entity_locks
WHERE lock_by_document_type = p_doc_type
AND lock_by_document_id = p_doc_id;
SELECT 'Y'
FROM okc_k_entity_locks
WHERE entity_name = p_entity_name
AND lock_by_document_type = p_lock_by_document_type
AND lock_by_document_id = p_lock_by_document_id;
okc_k_entity_locks_pkg.delete_row
(p_entity_name => p_entity_name,
p_entity_pk1 => p_entity_pk1,
p_entity_pk2 => p_entity_pk2,
p_entity_pk3 => p_entity_pk3,
p_entity_pk4 => p_entity_pk4,
p_entity_pk5 => p_entity_pk5,
p_lock_by_entity_id => p_lock_by_entity_id,
p_lock_by_document_type => p_lock_by_document_type,
p_lock_by_document_id => p_lock_by_document_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PROCEDURE delete_clause (
p_doc_clause_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
x_return_status := g_ret_sts_success;
'1000: Entered delete_clause : ' || p_doc_clause_id
);
okc_k_art_variables_pvt.delete_set (x_return_status => x_return_status,
p_cat_id => p_doc_clause_id
);
'1010: After Call to okc_k_art_variables_pvt.delete_set : '
|| x_return_status
);
okc_k_articles_pvt.delete_row (x_return_status => x_return_status,
p_id => p_doc_clause_id,
p_object_version_number => NULL
);
'1000: Completed delete_clause with status '
|| x_return_status
);
'0000: Exception in delete_clause ' || SQLERRM
);
END delete_clause;
SELECT sav_sae_id, (SELECT standard_yn
FROM okc_articles_all
WHERE article_id = sav_sae_id) standard_yn,
kart.attribute_category, kart.attribute1, kart.attribute2,
kart.attribute3, kart.attribute4, kart.attribute5,
kart.attribute6, kart.attribute7, kart.attribute8,
kart.attribute9, kart.attribute10, kart.attribute11,
kart.attribute12, kart.attribute13, kart.attribute14,
kart.attribute15, kart.source_flag, kart.article_version_id,
kart.change_nonstd_yn, scn_id,
'COPY' orig_system_reference_code,
Decode (p_keep_orig_ref,'Y', kart.orig_system_reference_id1,kart.ID )orig_system_reference_id1, kart.mandatory_yn,
kart.mandatory_rwa, kart.label, kart.display_sequence,
kart.ref_article_id, kart.ref_article_version_id,
kart.orig_article_id, NULL amend_operation_code,
kart.orig_system_reference_id1 src_orig_system_reference_id1
FROM okc_k_articles_b kart
WHERE kart.ID = p_source_doc_clause_id;
SELECT scn_id, orig_system_reference_id1
FROM okc_k_articles_b
WHERE ID = cp_clause_id;
SELECT orig_system_reference_code, orig_system_reference_id1,
amendment_operation_code
FROM okc_sections_b
WHERE ID = p_scn_id
AND document_type = p_target_document_type
AND document_id = p_target_document_id; */
SELECT id,amendment_operation_code
FROM okc_sections_b
WHERE 1=1
AND orig_system_reference_id1 = cp_orig_sys_ref_id1
AND document_type = p_target_document_type
AND document_id = p_target_document_id;
IF NVL (l_target_doc_scn1_rec.amendment_operation_code, '?') = 'DELETED'
THEN
okc_api.set_message
(p_app_name => g_app_name,
p_msg_name => 'OKC_RVRT_SEC_BEFORE_ART'
);
INSERT INTO okc_k_articles_b
(ID, sav_sae_id,
document_type, document_id,
chr_id,
dnz_chr_id,
source_flag,
mandatory_yn,
mandatory_rwa, scn_id,
label, amendment_description, amendment_operation_code,
article_version_id,
change_nonstd_yn,
orig_system_reference_code,
orig_system_reference_id1, orig_system_reference_id2,
display_sequence,
attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15, print_text_yn,
ref_article_id,
ref_article_version_id, object_version_number,
created_by, creation_date, last_updated_by,
last_update_login, last_update_date,
orig_article_id
)
VALUES (okc_k_articles_b_s.NEXTVAL, l_doc_clause_rec.sav_sae_id,
p_target_document_type, p_target_document_id,
DECODE (p_target_document_type,
'OKC_BUY', p_target_document_id,
'OKC_SELL', p_target_document_id,
'OKO', p_target_document_id,
'OKS', p_target_document_id,
'OKE_BUY', p_target_document_id,
'OKE_SELL', p_target_document_id,
'OKL', p_target_document_id,
NULL
),
DECODE (p_target_document_type,
'OKC_BUY', p_target_document_id,
'OKC_SELL', p_target_document_id,
'OKO', p_target_document_id,
'OKS', p_target_document_id,
'OKE_BUY', p_target_document_id,
'OKE_SELL', p_target_document_id,
'OKL', p_target_document_id,
NULL
),
l_doc_clause_rec.source_flag,
l_doc_clause_rec.mandatory_yn,
l_doc_clause_rec.mandatory_rwa, l_doc_clause_rec.scn_id,
l_doc_clause_rec.label, NULL, NULL,
DECODE (p_target_document_type,
okc_terms_util_grp.g_tmpl_doc_type, NULL,
l_doc_clause_rec.article_version_id
),
l_doc_clause_rec.change_nonstd_yn,
l_doc_clause_rec.orig_system_reference_code,
l_doc_clause_rec.orig_system_reference_id1, NULL,
l_doc_clause_rec.display_sequence,
l_doc_clause_rec.attribute_category,
l_doc_clause_rec.attribute1, l_doc_clause_rec.attribute2,
l_doc_clause_rec.attribute3, l_doc_clause_rec.attribute4,
l_doc_clause_rec.attribute5, l_doc_clause_rec.attribute6,
l_doc_clause_rec.attribute7, l_doc_clause_rec.attribute8,
l_doc_clause_rec.attribute9, l_doc_clause_rec.attribute10,
l_doc_clause_rec.attribute11,
l_doc_clause_rec.attribute12,
l_doc_clause_rec.attribute13,
l_doc_clause_rec.attribute14,
l_doc_clause_rec.attribute15, NULL,
l_doc_clause_rec.ref_article_id,
l_doc_clause_rec.ref_article_version_id, 1,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
fnd_global.login_id, SYSDATE,
l_doc_clause_rec.orig_article_id
)
RETURNING ID
INTO x_new_doc_clause_id;
SELECT 'Y'
FROM okc_sections_b
WHERE ID = cp_scn_id
AND NVL (amendment_operation_code, '?') <> 'DELETED'; */
delete_clause (p_doc_clause_id => p_target_doc_clause_id,
x_return_status => x_return_status
);
SELECT config_header_id, config_revision_number, valid_config_yn,
orig_system_reference_code, orig_system_reference_id1
FROM okc_template_usages
WHERE document_type = cp_document_type
AND document_id = cp_document_id;
SELECT ID
FROM okc_k_articles_b
WHERE document_type = cp_document_type
AND document_id = cp_document_id
AND source_flag = 'R';
SELECT 'Y'
FROM cz_config_hdrs
WHERE config_hdr_id = c_config_header_id
AND config_rev_nbr = c_config_rev_nbr;
SELECT TO_CHAR (orig_system_reference_id1)
FROM okc_k_articles_b
WHERE document_type = cp_document_type
AND document_id = cp_document_id
AND source_flag = 'R'
AND orig_system_reference_id1 IS NOT NULL;
DELETE FROM okc_k_entity_locks
WHERE lock_by_document_type = p_target_document_type
AND lock_by_document_id = p_target_document_id
AND entity_name = okc_k_entity_locks_grp.g_clause_entity
AND entity_pk1 = l_clause_tbl (i);
DELETE FROM okc_k_articles_b
WHERE document_type = p_target_document_type
AND document_id = p_target_document_id
AND source_flag = 'R'
;
DELETE FROM okc_xprt_doc_ques_response
WHERE doc_id = p_target_document_id
AND doc_type = p_target_document_type;
okc_xprt_cz_int_pvt.delete_configuration
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_config_header_id => l_old_config_header_id,
p_config_rev_nbr => l_old_config_revision_number,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
END IF; -- delete the old configuration
INSERT INTO okc_xprt_doc_ques_response(doc_question_response_id, doc_id, doc_type, question_id, response)
(SELECT okc_xprt_doc_ques_response_s.NEXTVAL, p_target_document_id, p_target_document_type, question_id, response
FROM okc_xprt_doc_ques_response WHERE doc_id = l_src_document_id AND doc_type = l_src_document_type );
UPDATE okc_template_usages
SET contract_expert_finish_flag = (SELECT contract_expert_finish_flag FROM okc_template_usages WHERE document_type = l_src_document_type AND document_id = l_src_document_id)
WHERE document_type = p_target_document_type
AND document_id = p_target_document_id;
UPDATE okc_template_usages
SET config_header_id = l_new_config_header_id,
config_revision_number = l_new_config_rev_nbr,
valid_config_yn = l_src_valid_config_yn
WHERE document_type = p_target_document_type
AND document_id = p_target_document_id;
SELECT p_target_doc_clause_id cat_id, var.variable_code,
busvar.variable_type, busvar.external_yn,
busvar.value_set_id, var.variable_value,
var.variable_value_id, var.override_global_yn,
var.mr_variable_html, var.mr_variable_xml, busvar.mrv_flag
FROM okc_k_art_variables var,
okc_k_articles_b kart,
okc_k_articles_b kart_tar,
okc_bus_variables_b busvar
WHERE kart.ID = p_source_doc_clause_id
AND var.cat_id = kart.ID
AND busvar.variable_code = var.variable_code;
INSERT INTO okc_k_art_variables
(cat_id, variable_code, variable_type,
external_yn, attribute_value_set_id,
variable_value, variable_value_id,
override_global_yn, mr_variable_html,
mr_variable_xml, object_version_number,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
)
VALUES (cat_tbl (i), var_tbl (i), var_type_tbl (i),
external_yn_tbl (i), value_set_id_tbl (i),
var_value_tbl (i), var_value_id_tbl (i),
override_global_yn_tbl (i), mrvariablehtml_tbl (i),
mrvariablexml_tbl (i), 1,
SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.user_id, fnd_global.login_id
);
'1020: Inserted records into okc_k_art_variables ||'
);
SELECT ID, scn_id parent_scn_id, scn_code, amendment_operation_code,
orig_system_reference_id1, heading, label, section_sequence ,
amendment_description,
summary_amend_operation_code
FROM okc_sections_b
WHERE ID = cp_scn_id;
IS SELECT ID, scn_id parent_scn_id, scn_code, amendment_operation_code,
orig_system_reference_id1, heading, label, section_sequence
FROM okc_sections_b
WHERE orig_system_reference_id1 = cp_scn_id
AND document_type = p_target_document_type
AND document_id=p_target_document_id;
IF l_parent_scn_details_rec.amendment_operation_code = 'DELETED'
THEN
okc_api.set_message
(p_app_name => g_app_name,
p_msg_name => 'OKC_RVRT_P_SEC_BEFORE_SEC',
p_token1 => 'SEC_NAME',
p_token1_value => l_parent_scn_details_rec.heading
);
UPDATE okc_sections_b
SET amendment_description = NULL,
amendment_operation_code = NULL,
summary_amend_operation_code = NULL,
scn_id = l_parent_scn_id,
last_amended_by = NULL,
last_amendment_date = NULL
WHERE ID = p_target_doc_section_id;
UPDATE okc_sections_b
SET amendment_description = l_par_scn_details_rec.amendment_description,
amendment_operation_code = l_par_scn_details_rec.amendment_operation_code,
summary_amend_operation_code = l_par_scn_details_rec.summary_amend_operation_code,
scn_id = p_scn_id,
last_amended_by = fnd_global.user_id,
last_amendment_date = SYSDATE
WHERE ID = p_target_doc_section_id;
SELECT okc_sections_b_s.NEXTVAL
INTO x_new_section_id
FROM DUAL;
INSERT INTO okc_sections_b tar_sec
(ID, chr_id, heading, description, document_type,
document_id, scn_id, orig_system_reference_code,
orig_system_reference_id1, orig_system_reference_id2,
section_sequence, label, print_yn, 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_date, last_update_login,
amendment_description,
amendment_operation_code,
summary_amend_operation_code, last_amended_by,last_amendment_date
)
SELECT x_new_section_id,
DECODE (p_target_document_type,
'OKC_BUY', p_target_document_id,
'OKC_SELL', p_target_document_id,
'OKO', p_target_document_id,
'OKS', p_target_document_id,
'OKE_BUY', p_target_document_id,
'OKE_SELL', p_target_document_id,
'OKL', p_target_document_id,
NULL
),
heading, description, p_target_document_type,
p_target_document_id, l_parent_scn_id, 'COPY', Decode(p_keep_orig_ref,'Y',orig_system_reference_id1,ID), Decode(p_keep_orig_ref,'Y',orig_system_reference_id2,null),
section_sequence, label, print_yn, attribute_category,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9,
attribute10, attribute11, attribute12, attribute13,
attribute14, attribute15,
1,fnd_global.user_id,SYSDATE,fnd_global.user_id,SYSDATE,fnd_global.login_id
,Decode(p_keep_orig_ref,'Y',amendment_description,null)
,Decode(p_keep_orig_ref,'Y',amendment_operation_code,null)
,Decode(p_keep_orig_ref,'Y',summary_amend_operation_code,null)
,Decode(p_keep_orig_ref,'Y',fnd_global.user_id,null)
,Decode(p_keep_orig_ref,'Y',sysdate,To_Date(NULL))
FROM okc_sections_b
WHERE ID = p_source_section_id;
UPDATE okc_sections_b
SET scn_id = x_new_section_id
WHERE document_type = p_target_document_type
AND document_id = p_target_document_id
AND scn_id = p_target_doc_section_id;
UPDATE okc_k_articles_b
SET scn_id = x_new_section_id
WHERE document_type = p_target_document_type
AND document_id = p_target_document_id
AND scn_id = p_target_doc_section_id;
SELECT scn_code, ROWID
FROM okc_sections_b
WHERE ID = p_target_doc_section_id;
DELETE FROM okc_sections_tl
WHERE ID = p_target_doc_section_id;
DELETE FROM okc_sections_b
WHERE ROWID = l_cur_tgt_rec.ROWID;
SELECT * FROM okc_deliverables WHERE deliverable_id= p_source_deliverable_id;
INSERT INTO okc_deliverables
(DELIVERABLE_ID,
BUSINESS_DOCUMENT_TYPE ,
BUSINESS_DOCUMENT_ID ,
BUSINESS_DOCUMENT_NUMBER ,
DELIVERABLE_TYPE ,
RESPONSIBLE_PARTY ,
INTERNAL_PARTY_CONTACT_ID ,
EXTERNAL_PARTY_CONTACT_ID ,
DELIVERABLE_NAME ,
DESCRIPTION ,
COMMENTS ,
DISPLAY_SEQUENCE ,
FIXED_DUE_DATE_YN ,
ACTUAL_DUE_DATE ,
PRINT_DUE_DATE_MSG_NAME ,
RECURRING_YN ,
NOTIFY_PRIOR_DUE_DATE_VALUE ,
NOTIFY_PRIOR_DUE_DATE_UOM ,
NOTIFY_PRIOR_DUE_DATE_YN ,
NOTIFY_COMPLETED_YN ,
NOTIFY_OVERDUE_YN ,
NOTIFY_ESCALATION_YN ,
NOTIFY_ESCALATION_VALUE ,
NOTIFY_ESCALATION_UOM ,
ESCALATION_ASSIGNEE ,
AMENDMENT_OPERATION ,
PRIOR_NOTIFICATION_ID ,
AMENDMENT_NOTES ,
COMPLETED_NOTIFICATION_ID ,
OVERDUE_NOTIFICATION_ID ,
ESCALATION_NOTIFICATION_ID ,
LANGUAGE ,
ORIGINAL_DELIVERABLE_ID ,
REQUESTER_ID ,
EXTERNAL_PARTY_ID ,
EXTERNAL_PARTY_ROLE ,
RECURRING_DEL_PARENT_ID ,
BUSINESS_DOCUMENT_VERSION ,
RELATIVE_ST_DATE_DURATION ,
RELATIVE_ST_DATE_UOM ,
RELATIVE_ST_DATE_EVENT_ID ,
RELATIVE_END_DATE_DURATION ,
RELATIVE_END_DATE_UOM ,
RELATIVE_END_DATE_EVENT_ID ,
REPEATING_DAY_OF_MONTH ,
REPEATING_DAY_OF_WEEK ,
REPEATING_FREQUENCY_UOM ,
REPEATING_DURATION ,
FIXED_START_DATE ,
FIXED_END_DATE ,
MANAGE_YN ,
INTERNAL_PARTY_ID ,
DELIVERABLE_STATUS ,
STATUS_CHANGE_NOTES ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
DISABLE_NOTIFICATIONS_YN ,
LAST_AMENDMENT_DATE ,
BUSINESS_DOCUMENT_LINE_ID ,
EXTERNAL_PARTY_SITE_ID ,
START_EVENT_DATE ,
END_EVENT_DATE ,
SUMMARY_AMEND_OPERATION_CODE,
PAY_HOLD_PRIOR_DUE_DATE_VALUE,
PAY_HOLD_PRIOR_DUE_DATE_UOM,
PAY_HOLD_PRIOR_DUE_DATE_YN,
PAY_HOLD_OVERDUE_YN,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2,
RAISE_COMPLETION_EVENT_YN,
del_category_code,
exhibit_code,
data_item_number,
price_group,
estimated_price
, uda_template_id
, schedule_type
)
VALUES (
OKC_DELIVERABLE_ID_S.nextval,
del_rec.BUSINESS_DOCUMENT_TYPE ,
del_rec.BUSINESS_DOCUMENT_ID ,
del_rec.BUSINESS_DOCUMENT_NUMBER ,
del_rec.DELIVERABLE_TYPE ,
del_rec.RESPONSIBLE_PARTY ,
del_rec.INTERNAL_PARTY_CONTACT_ID ,
del_rec.EXTERNAL_PARTY_CONTACT_ID ,
del_rec.DELIVERABLE_NAME ,
del_rec.DESCRIPTION ,
del_rec.COMMENTS ,
del_rec.DISPLAY_SEQUENCE ,
del_rec.FIXED_DUE_DATE_YN ,
del_rec.ACTUAL_DUE_DATE ,
del_rec.PRINT_DUE_DATE_MSG_NAME ,
del_rec.RECURRING_YN ,
del_rec.NOTIFY_PRIOR_DUE_DATE_VALUE ,
del_rec.NOTIFY_PRIOR_DUE_DATE_UOM ,
del_rec.NOTIFY_PRIOR_DUE_DATE_YN ,
del_rec.NOTIFY_COMPLETED_YN ,
del_rec.NOTIFY_OVERDUE_YN ,
del_rec.NOTIFY_ESCALATION_YN ,
del_rec.NOTIFY_ESCALATION_VALUE ,
del_rec.NOTIFY_ESCALATION_UOM ,
del_rec.ESCALATION_ASSIGNEE ,
del_rec.AMENDMENT_OPERATION ,
del_rec.PRIOR_NOTIFICATION_ID ,
del_rec.AMENDMENT_NOTES ,
del_rec.COMPLETED_NOTIFICATION_ID ,
del_rec.OVERDUE_NOTIFICATION_ID ,
del_rec.ESCALATION_NOTIFICATION_ID ,
del_rec.LANGUAGE ,
del_rec.ORIGINAL_DELIVERABLE_ID ,
del_rec.REQUESTER_ID ,
del_rec.EXTERNAL_PARTY_ID ,
del_rec.EXTERNAL_PARTY_ROLE ,
del_rec.RECURRING_DEL_PARENT_ID ,
del_rec.BUSINESS_DOCUMENT_VERSION ,
del_rec.RELATIVE_ST_DATE_DURATION ,
del_rec.RELATIVE_ST_DATE_UOM ,
del_rec.RELATIVE_ST_DATE_EVENT_ID ,
del_rec.RELATIVE_END_DATE_DURATION ,
del_rec.RELATIVE_END_DATE_UOM ,
del_rec.RELATIVE_END_DATE_EVENT_ID ,
del_rec.REPEATING_DAY_OF_MONTH ,
del_rec.REPEATING_DAY_OF_WEEK ,
del_rec.REPEATING_FREQUENCY_UOM ,
del_rec.REPEATING_DURATION ,
del_rec.FIXED_START_DATE ,
del_rec.FIXED_END_DATE ,
del_rec.MANAGE_YN ,
del_rec.INTERNAL_PARTY_ID ,
del_rec.DELIVERABLE_STATUS ,
del_rec.STATUS_CHANGE_NOTES ,
del_rec.CREATED_BY ,
del_rec.CREATION_DATE ,
del_rec.LAST_UPDATED_BY ,
del_rec.LAST_UPDATE_DATE ,
del_rec.LAST_UPDATE_LOGIN ,
del_rec.OBJECT_VERSION_NUMBER ,
del_rec.ATTRIBUTE_CATEGORY ,
del_rec.ATTRIBUTE1 ,
del_rec.ATTRIBUTE2 ,
del_rec.ATTRIBUTE3 ,
del_rec.ATTRIBUTE4 ,
del_rec.ATTRIBUTE5 ,
del_rec.ATTRIBUTE6 ,
del_rec.ATTRIBUTE7 ,
del_rec.ATTRIBUTE8 ,
del_rec.ATTRIBUTE9 ,
del_rec.ATTRIBUTE10 ,
del_rec.ATTRIBUTE11 ,
del_rec.ATTRIBUTE12 ,
del_rec.ATTRIBUTE13 ,
del_rec.ATTRIBUTE14 ,
del_rec.ATTRIBUTE15 ,
del_rec.DISABLE_NOTIFICATIONS_YN ,
del_rec.LAST_AMENDMENT_DATE ,
del_rec.BUSINESS_DOCUMENT_LINE_ID ,
del_rec.EXTERNAL_PARTY_SITE_ID ,
del_rec.START_EVENT_DATE ,
del_rec.END_EVENT_DATE ,
del_rec.SUMMARY_AMEND_OPERATION_CODE,
del_rec.PAY_HOLD_PRIOR_DUE_DATE_VALUE,
del_rec.PAY_HOLD_PRIOR_DUE_DATE_UOM,
del_rec.PAY_HOLD_PRIOR_DUE_DATE_YN,
del_rec.PAY_HOLD_OVERDUE_YN,
del_rec.orig_system_reference_code,
del_rec.orig_system_reference_id1,
del_rec.orig_system_reference_id2,
del_rec.RAISE_COMPLETION_EVENT_YN,
del_rec.del_category_code,
del_rec.exhibit_code,
del_rec.data_item_number,
del_rec.price_group,
del_rec.estimated_price,
del_rec.uda_template_id,
del_rec.schedule_type
)returning deliverable_id INTO l_new_target_del_id ;
DELETE FROM okc_deliverables
WHERE deliverable_id= p_target_deliverable_id;
DELETE FROM okc_k_entity_locks
WHERE k_entity_lock_id = p_K_ENTITY_LOCK_ID;
l_sql := 'SELECT ' ||l_doc_num_col_name || ' FROM ' || l_from_table || ' WHERE 1=1 ';
SELECT okc_terms_util_pvt.get_article_name(kart.sav_sae_id, kart.article_version_id)
INTO l_title
FROM okc_k_articles_b kart
WHERE id=To_Number(p_entity_pk1);
SELECT heading
INTO l_title
FROM okc_sections_b
WHERE id=To_Number(p_entity_pk1);
SELECT deliverable_name
INTO l_title
FROM okc_deliverables
WHERE deliverable_id=To_Number(p_entity_pk1);
SELECT orig_system_reference_code, To_Char(orig_system_reference_id1)
FROM okc_template_usages
WHERE document_type = p_doc_type
AND document_id= p_doc_id;
SELECT src.document_type, To_Char(src.document_id)
FROM okc_sections_b tgt,
okc_sections_b src
WHERE tgt.document_type= p_doc_type
AND tgt.document_id= p_doc_id
AND tgt.orig_system_reference_code = 'COPY'
AND src.id=tgt.orig_system_reference_id1;
SELECT entity_name, entity_pk1, entity_pk2, entity_pk3, entity_pk4, entity_pk5, lock_by_entity_id, lock_by_document_type, lock_by_document_id
FROM OKC_K_ENTITY_LOCKS
WHERE k_entity_lock_id = p_k_entity_lock_id;
SELECT 'Y'
INTO l_lock_exists
FROM okc_k_entity_locks
WHERE entity_name=G_CLAUSE_ENTITY
AND entity_pk1=To_Char(p_src_kart_id)
AND lock_by_document_id <> p_tgt_document_id;
SELECT document_type,document_id
FROM okc_k_Articles_b
WHERE id= cp_article_id;
SELECT document_type,document_id
FROM okc_sections_b
WHERE id= cp_sec_id;
SELECT orig_system_reference_code, orig_system_reference_id1
FROM okc_template_usages
WHERE document_type = p_tgt_document_type
AND document_id = p_tgt_document_id;
SELECT business_document_type, business_document_id
FROM okc_deliverables
WHERE deliverable_id = cp_del_id;
SELECT lock_by_document_type,lock_by_document_id
INTO l_lock_by_doc_type,l_lock_by_document_id
FROM okc_k_entity_locks
WHERE entity_name=p_entity_name
AND entity_pk1=To_Char(p_src_entity_id);
SELECT 'Y'
INTO l_lock_exists
FROM okc_k_entity_locks
WHERE entity_name=G_SECTION_ENTITY
AND entity_pk1=To_Char(p_src_ksec_id)
AND lock_by_document_id <> p_tgt_document_id;
SELECT 'Y'
INTO l_lock_exists
FROM okc_k_entity_locks
WHERE entity_name='DELIVERABLE'
AND entity_pk1=To_Char(p_src_dlvbl_id)
AND lock_by_document_id <> p_tgt_document_id;
SELECT orig_system_reference_code, orig_system_reference_id1
FROM okc_template_usages
WHERE document_type = p_tgt_document_type
AND document_id = p_tgt_document_id;
SELECT 'Y'
INTO l_lock_exists
FROM okc_k_entity_locks
WHERE entity_name='XPRT'
AND entity_pk1= To_Char(l_src_doc_id)
AND entity_pk2 = l_src_doc_type
AND lock_by_document_id <> p_tgt_document_id;
SELECT 'Y'
FROM okc_k_art_variables v,
okc_k_articles_b k
WHERE k.id = v.cat_id
AND v.variable_code = p_variable_code
AND k.document_type = p_tgt_document_type
AND k.document_id = p_tgt_document_id
AND k.orig_system_reference_code = 'COPY'
AND EXISTS (SELECT 'Lock Exists'
FROM okc_k_entity_locks lck
WHERE lck.entity_name=G_CLAUSE_ENTITY
AND lck.entity_pk1 = To_Char(k.orig_system_reference_id1)
AND lock_by_document_id <> p_tgt_document_id)
;
SELECT 'Y'
INTO l_lock_exists
FROM okc_k_entity_locks
WHERE entity_name='XPRT'
AND entity_pk1=To_Char(l_src_doc_id)
AND entity_pk2=l_src_doc_type
AND lock_by_document_id <> p_doc_id;
SELECT 'Y'
INTO l_lock_exists
FROM okc_k_entity_locks lck, okc_sections_b sec
WHERE lck.entity_name=G_SECTION_ENTITY
AND lck.entity_pk1= To_Char(sec.id)
AND sec.document_type= l_src_doc_type
AND sec.document_id= l_src_doc_id
AND lck.lock_by_document_id <> p_doc_id;
SELECT 'Y'
INTO l_lock_exists
FROM okc_k_entity_locks lck, okc_k_articles_b kart
WHERE lck.entity_name=G_CLAUSE_ENTITY
AND lck.entity_pk1= To_Char(kart.id)
AND kart.document_type= l_src_doc_type
AND kart.document_id= l_src_doc_id
AND lck.lock_by_document_id <> p_doc_id;
SELECT user_name
FROM fnd_user
WHERE user_id = cp_user_id;
SELECT NAME
FROM okc_bus_doc_types_tl
WHERE document_type = cp_doc_type AND LANGUAGE = USERENV ('Lang');
p_update_from_doc_type IN VARCHAR2, -- Mod document
p_update_from_doc_id IN NUMBER, -- Mod document
p_update_to_doc_type IN VARCHAR2, -- Award document
p_update_to_doc_id IN NUMBER, -- Award document
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
-- Sections
CURSOR cur_sec_ids
IS
SELECT tgtAward.id,srcMod.orig_system_reference_id1
FROM
okc_sections_b tgtAward
,okc_sections_b srcMod
WHERE srcMod.document_type= p_update_from_doc_type
AND srcMod.document_id= p_update_from_doc_id
AND tgtAward.document_type= p_update_to_doc_type
AND tgtAward.document_id= p_update_to_doc_id
AND tgtAward.orig_system_reference_id1= SrcMod.id
AND srcMod.orig_system_reference_id1 IS NOT NULL; -- for newly added sections on a mod, this column would be having null value.
SELECT tgtAward.id,srcMod.orig_system_reference_id1
FROM
okc_k_articles_b tgtAward
,okc_k_articles_b srcMod
WHERE srcMod.document_type= p_update_from_doc_type
AND srcMod.document_id= p_update_from_doc_id
AND tgtAward.document_type= p_update_to_doc_type
AND tgtAward.document_id= p_update_to_doc_id
AND tgtAward.orig_system_reference_id1= SrcMod.id
AND srcMod.orig_system_reference_id1 IS NOT NULL;
SELECT tgtAward.deliverable_id,srcMod.orig_system_reference_id1
FROM
okc_deliverables tgtAward
,okc_deliverables srcMod
WHERE srcMod.business_document_type= p_update_from_doc_type
AND srcMod.business_document_id= p_update_from_doc_id
AND tgtAward.business_document_type= p_update_to_doc_type
AND tgtAward.business_document_id= p_update_to_doc_id
AND tgtAward.orig_system_reference_id1= SrcMod.deliverable_id
AND srcMod.orig_system_reference_id1 IS NOT NULL;
'100: p_update_from_doc_type : ' || p_update_from_doc_type
);
'100: p_update_from_doc_id : ' || p_update_from_doc_id
);
'100: p_update_to_doc_type : ' || p_update_to_doc_type
);
'100: p_update_to_doc_id : ' || p_update_to_doc_id
);
update okc_sections_b
set id= l_src_ref_id_tbl(i)
where id=l_tgt_id_tbl(i);
UPDATE okc_sections_b
SET scn_id= l_src_ref_id_tbl(i)
WHERE scn_id= l_tgt_id_tbl(i)
AND document_type= p_update_to_doc_type
AND document_id=p_update_from_doc_id;
UPDATE okc_k_articles_b
SET scn_id= l_src_ref_id_tbl(i)
WHERE scn_id= l_tgt_id_tbl(i)
AND document_type= p_update_to_doc_type
AND document_id=p_update_from_doc_id;
update okc_k_articles_b
set cat_id= l_src_ref_id_tbl(i)
where id=l_tgt_id_tbl(i);
update okc_k_art_variables
set cat_id= l_src_ref_id_tbl(i)
where cat_id=l_tgt_id_tbl(i);
update OKC_K_ART_VAR_EXT_B
set cat_id= l_src_ref_id_tbl(i)
where cat_id=l_tgt_id_tbl(i);
update OKC_K_ART_VAR_EXT_tl
set cat_id= l_src_ref_id_tbl(i)
where cat_id=l_tgt_id_tbl(i);
UPDATE okc_deliverables
SET deliverable_id = l_src_ref_id_tbl(i)
WHERE deliverable_id = l_tgt_id_tbl(i);
p_doc_type => p_update_from_doc_type,
p_doc_id => p_update_from_doc_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT 'Y'
FROM okc_sections_b
WHERE document_type= p_doc_type
AND document_id= p_doc_id
AND amendment_operation_code IS NOT NULL;
SELECT 'Y'
FROM okc_k_articles_b
WHERE document_type= p_doc_type
AND document_id= p_doc_id
AND amendment_operation_code IS NOT NULL;
SELECT 'Y'
FROM okc_k_articles_b
WHERE document_type= p_doc_type
AND document_id= p_doc_id
AND amendment_operation_code IS NOT NULL;
SELECT entity
INTO l_entity
FROM
(SELECT 'SECTION' entity, orig_system_reference_id1 , amendment_operation_code, summary_amend_operation_code
FROM okc_sections_b
WHERE document_type= p_doc_type
AND document_id= p_doc_id
AND summary_amend_operation_code IN ('UPDATED','DELETED')
UNION ALL
SELECT 'CLAUSE' entity, orig_system_reference_id1 , amendment_operation_code, summary_amend_operation_code
FROM okc_k_articles_b
WHERE document_type= p_doc_type
AND document_id = p_doc_id
AND summary_amend_operation_code IN ('UPDATED','DELETED')
UNION ALL
SELECT 'DELIVERABLE' entity, orig_system_reference_id1 , amendment_operation, summary_amend_operation_code
FROM okc_deliverables
WHERE business_document_type= p_doc_type
AND business_document_id = p_doc_id
AND business_document_version = -99
AND summary_amend_operation_code IN ('UPDATED','DELETED')
) src, okc_k_entity_locks LcK
WHERE lck.entity_name = src. entity
AND lck.entity_pk1 = To_Char(orig_system_reference_id1)
AND ROWNUM=1;
SELECT deliverable_id,
amendment_operation,
summary_amend_operation_code,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2
FROM okc_deliverables
WHERE business_document_type = p_src_doc_type
AND business_document_id = p_src_doc_id
AND business_document_version = -99
AND amendment_operation IS NOT NULL
;
SELECT deliverable_id
FROM okc_deliverables
WHERE business_document_type = p_target_doc_type
AND business_document_id = p_target_doc_id
AND business_document_version = -99
AND orig_system_reference_id1 = cp_deliverable_id;
SELECT id,
amendment_operation_code,
summary_amend_operation_code,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2,
scn_id
FROM okc_k_articles_b
WHERE document_type = p_src_doc_type
AND document_id = p_src_doc_id
AND amendment_operation_code IS NOT NULL;
SELECT id
FROM okc_k_articles_b
WHERE document_type = p_target_doc_type
AND document_id = p_target_doc_id
AND orig_system_reference_id1 = cp_kart_id;
SELECT id,
amendment_operation_code,
summary_amend_operation_code,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2,
scn_id
FROM okc_sections_b
WHERE document_type = p_src_doc_type
AND document_id = p_src_doc_id
AND amendment_operation_code IS NOT NULL
ORDER BY Nvl(scn_id,-99),id;
SELECT id
FROM okc_sections_b
WHERE document_type = p_target_doc_type
AND document_id = p_target_doc_id
AND orig_system_reference_id1 = cp_scn_id;
IF del_rec.summary_amend_operation_code in ('UPDATED','DELETED')
THEN
lock_entity( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_COMMIT => P_COMMIT,
-----------------
P_ENTITY_NAME => G_DLVBL_ENTITY,
P_ENTITY_PK1 => del_rec.orig_system_reference_id1,
P_LOCK_BY_ENTITY_ID => l_target_del_id,
P_LOCK_BY_DOCUMENT_TYPE => p_target_doc_type,
P_LOCK_BY_DOCUMENT_ID => p_target_doc_id,
---------------------
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
IF clause_rec.summary_amend_operation_code in ('UPDATED','DELETED')
THEN
lock_entity( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_COMMIT => P_COMMIT,
-----------------
P_ENTITY_NAME => G_CLAUSE_ENTITY,
P_ENTITY_PK1 => clause_rec.orig_system_reference_id1,
P_LOCK_BY_ENTITY_ID => l_target_kart_id,
P_LOCK_BY_DOCUMENT_TYPE => p_target_doc_type,
P_LOCK_BY_DOCUMENT_ID => p_target_doc_id,
---------------------
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
IF section_rec.summary_amend_operation_code IN ('UPDATED','DELETED')
THEN
lock_entity( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_COMMIT => P_COMMIT,
-----------------
P_ENTITY_NAME => G_SECTION_ENTITY,
P_ENTITY_PK1 => section_rec.orig_system_reference_id1,
P_LOCK_BY_ENTITY_ID => l_target_scn_id,
P_LOCK_BY_DOCUMENT_TYPE => p_target_doc_type,
P_LOCK_BY_DOCUMENT_ID => p_target_doc_id,
---------------------
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);