[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 doc_numbering_scheme
FROM
OKC_TEMPLATE_USAGES
WHERE document_type = p_document_type
AND document_id = p_document_id;
SELECT DOCUMENT_TYPE,
DOCUMENT_TYPE_CLASS,
NAME,
INTENT,
PROVISION_ALLOWED_YN,
ENABLE_DELIVERABLES_YN,
ENABLE_ATTACHMENTS_YN,
TARGET_RESPONSE_DOC_TYPE
FROM okc_bus_doc_types_vl
WHERE document_type = p_document_type;
SELECT TYPE_LOOKUP_CODE,
STATUS_LOOKUP_CODE,
ORG_ID,
SEGMENT1
FROM PO_HEADERS_ALL
WHERE po_header_id = p_document_id;
SELECT org_id,
order_number
FROM oe_order_headers_all
WHERE header_id = p_document_id;
SELECT org_id,
order_number
FROM oe_blanket_headers_all
WHERE header_id = p_document_id;
SELECT org_id,
contract_number
FROM okc_rep_contracts_all
WHERE contract_id = p_document_id;
SELECT org_id, Decode (contract_type, 'BLANKET', 'PA_BLANKET', 'CONTRACT' , 'PA_CONTRACT', 'BLANKET', 'PO_STANDARD',NULL) target_doc_type
FROM pon_auction_headers_all
WHERE auction_header_id = p_document_id;
l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
SELECT template_id,
template_name,
status_code,
start_date,
end_date,
intent,
org_id,
tmpl_numbering_scheme
FROM
OKC_TERMS_TEMPLATES_ALL TMP
WHERE
TMP.template_id = p_contract_tmpl_id;
SELECT template_id,
template_name,
status_code,
start_date,
end_date,
intent,
org_id,
tmpl_numbering_scheme
FROM
OKC_TERMS_TEMPLATES_ALL TMP
WHERE TMP.template_name = p_contract_tmpl_name
AND TMP.org_id = l_org_id;
SELECT 1
FROM
OKC_TEMPLATE_USAGES
WHERE document_type = p_document_type
AND document_id = p_document_id;
SELECT 1
FROM
OKC_ALLOWED_TMPL_USAGES
WHERE document_type = p_document_type
AND template_id = l_template_id;
SELECT fnd_lobs_s.nextval
FROM
DUAL;
SELECT fnd_attached_documents_s.nextval
FROM
DUAL;
SELECT category_id
FROM
FND_DOCUMENT_CATEGORIES
WHERE application_id = 510 AND name = 'OKC_REPO_CONTRACT' ;
SELECT nvl(poh.revision_num,-99)
FROM
po_headers_archive_all poa,
po_headers_all poh
WHERE poh.po_header_id = poa.po_header_id
AND poh.revision_num = poa.revision_num
AND poh.po_header_id = p_document_id;
SELECT intent FROM okc_bus_doc_types_b
WHERE document_type = p_document_type;
INSERT INTO fnd_lobs (
file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format )
VALUES (
l_fid,
l_attachment_file_name,
l_content_type,
sysdate,
null,
'OKCGTMGB',
null,
l_blob,
userenv('LANG'),
fnd_gfm.iana_to_oracle(fnd_gfm.get_iso_charset),
fnd_gfm.set_file_format(l_content_type));
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
fnd_attached_documents_pkg.insert_row(
x_rowid => l_rowid,
x_attached_document_id => l_new_attachment_id,
x_document_id => lf_document_id,
x_creation_date => sysdate,
x_created_by => fnd_global.user_id,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_seq_num => l_seq_num,
x_entity_name => 'OKC_CONTRACT_DOCS',
x_column1 => NULL,
x_pk1_value => p_document_type,
x_pk2_value => to_char(p_document_id),
x_pk3_value => to_char(l_business_document_version),
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => 6,
x_category_id => l_category_id,
x_security_type => 4,
x_publish_flag => 'N',
x_usage_type => NULL,
x_language => NULL,
x_description => l_attachment_file_desc,
x_file_name => l_attachment_file_name,
x_media_id => l_fid,
x_doc_attribute_category => NULL,
x_doc_attribute1 => NULL,
x_doc_attribute2 => NULL,
x_doc_attribute3 => NULL,
x_doc_attribute4 => NULL,
x_doc_attribute5 => NULL,
x_doc_attribute6 => NULL,
x_doc_attribute7 => NULL,
x_doc_attribute8 => NULL,
x_doc_attribute9 => NULL,
x_doc_attribute10 => NULL,
x_doc_attribute11 => NULL,
x_doc_attribute12 => NULL,
x_doc_attribute13 => NULL,
x_doc_attribute14 => NULL,
x_doc_attribute15 => NULL,
X_create_doc => 'Y'
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished FND_ATTACHED_DOCUMENTS_PKG.insert_row ');
okc_contract_docs_grp.Insert_Contract_Doc(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_business_document_type => p_document_type,
p_business_document_id => p_document_id,
p_business_document_version => l_business_document_version,
p_attached_document_id => l_attached_document_id,
p_external_visibility_flag => 'Y',
p_effective_from_type => p_document_type,
p_effective_from_id => p_document_id,
p_effective_from_version => l_business_document_version,
p_include_for_approval_flag => 'N',
p_create_fnd_attach => 'N',
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_program_update_date => NULL,
p_parent_attached_doc_id => NULL,
p_generated_flag => 'N',
p_delete_flag => 'N',
p_primary_contract_doc_flag => l_primary_contract_doc_flag,
p_mergeable_doc_flag => 'N',
p_versioning_flag => 'N',
x_business_document_type => lc_business_document_type,
x_business_document_id => lc_business_document_id,
x_business_document_version => lc_business_document_version,
x_attached_document_id => lc_attached_document_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished OKC_CONTRACT_DOCS_GRP.insert_contract_doc, return status'||x_return_status);
/*User will need this if they want to update th ePO terms columns*/
/*IF G_DOC_TYPE_REC.DOCUMENT_TYPE_CLASS = 'PO' THEN
PO_CONTERMS_UPGRADE_GRP. apply_template_change
( p_api_version => 1,
p_po_header_id => p_document_id,
p_conterms_articles_upd_date => sysdate,
p_conterms_deliv_upd_date => l_conterms_deliv_upd_date,
x_return_status => x_return_status);
l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
SELECT 1
FROM
OKC_TEMPLATE_USAGES
WHERE document_type = p_document_type
AND document_id = p_document_id;
SELECT fnd_lobs_s.nextval
FROM
DUAL;
SELECT fnd_attached_documents_s.nextval
FROM
DUAL;
SELECT category_id
FROM
FND_DOCUMENT_CATEGORIES
WHERE application_id = 510
AND name = l_category;
SELECT nvl(poh.revision_num,-99)
FROM
po_headers_archive_all poa,
po_headers_all poh
WHERE poh.po_header_id = poa.po_header_id
AND poh.revision_num = poa.revision_num
AND poh.po_header_id = p_document_id;
INSERT INTO fnd_lobs (
file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format )
VALUES (
l_fid,
l_attachment_file_name,
l_content_type,
sysdate,
null,
'OKCGTMGB',
null,
l_blob,
userenv('LANG'),
fnd_gfm.iana_to_oracle(fnd_gfm.get_iso_charset),
fnd_gfm.set_file_format(l_content_type));
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
fnd_attached_documents_pkg.insert_row(
x_rowid => l_rowid,
x_attached_document_id => l_new_attachment_id,
x_document_id => lf_document_id,
x_creation_date => sysdate,
x_created_by => fnd_global.user_id,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_seq_num => l_seq_num,
x_entity_name => 'OKC_CONTRACT_DOCS',
x_column1 => NULL,
x_pk1_value => p_document_type,
x_pk2_value => to_char(p_document_id),
x_pk3_value => to_char(l_business_document_version),
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => l_datatype_id,
x_category_id => l_category_id,
x_security_type => 4,
x_publish_flag => 'N',
x_usage_type => NULL,
x_language => NULL,
x_description => l_description,
x_file_name => l_attachment_file_name,
x_media_id => l_fid,
x_doc_attribute_category => NULL,
x_doc_attribute1 => NULL,
x_doc_attribute2 => NULL,
x_doc_attribute3 => NULL,
x_doc_attribute4 => NULL,
x_doc_attribute5 => NULL,
x_doc_attribute6 => NULL,
x_doc_attribute7 => NULL,
x_doc_attribute8 => NULL,
x_doc_attribute9 => NULL,
x_doc_attribute10 => NULL,
x_doc_attribute11 => NULL,
x_doc_attribute12 => NULL,
x_doc_attribute13 => NULL,
x_doc_attribute14 => NULL,
x_doc_attribute15 => NULL,
X_create_doc => 'Y'
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished FND_ATTACHED_DOCUMENTS_PKG.insert_row ');
okc_contract_docs_grp.Insert_Contract_Doc(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_business_document_type => p_document_type,
p_business_document_id => p_document_id,
p_business_document_version => l_business_document_version,
p_attached_document_id => l_attached_document_id,
p_external_visibility_flag => 'Y',
p_effective_from_type => p_document_type,
p_effective_from_id => p_document_id,
p_effective_from_version => l_business_document_version,
p_include_for_approval_flag => 'N',
p_create_fnd_attach => 'N',
p_program_id => NULL,
p_program_application_id => NULL,
p_request_id => NULL,
p_program_update_date => NULL,
p_parent_attached_doc_id => NULL,
p_generated_flag => 'N',
p_delete_flag => 'N',
p_primary_contract_doc_flag => l_primary_contract_doc_flag,
p_mergeable_doc_flag => 'N',
p_versioning_flag => 'N',
x_business_document_type => lc_business_document_type,
x_business_document_id => lc_business_document_id,
x_business_document_version => lc_business_document_version,
x_attached_document_id => lc_attached_document_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished OKC_CONTRACT_DOCS_GRP.insert_contract_doc, return status'||x_return_status);
SELECT id
FROM
OKC_SECTIONS_B SEC
WHERE SEC.ID = p_section_id
AND DOCUMENT_TYPE = p_document_type
AND DOCUMENT_ID = p_document_id;
SELECT id
FROM
OKC_SECTIONS_B SEC
WHERE SEC.heading = p_section_name
AND DOCUMENT_TYPE = p_document_type
AND DOCUMENT_ID = p_document_id;
SELECT
article_id,
article_title,
article_version_id,
article_version_number,
article_intent,
provision_yn,
article_status,
start_date,
end_date
FROM
OKC_ARTICLES_V ART
WHERE ART.article_version_id = p_clause_version_id;
SELECT
article_id,
article_title,
article_version_id,
article_version_number,
article_intent,
provision_yn,
article_status,
start_date,
end_date
FROM
okc_articles_local_v ART
WHERE ART.article_title = p_clause_title
AND ART.org_id = l_org_id
UNION ALL
SELECT
article_id,
article_title,
article_version_id,
article_version_number,
article_intent,
provision_yn,
article_status,
start_date,
end_date
FROM
okc_articles_global_v ART
WHERE ART.article_title = p_clause_title
AND ART.org_id = l_org_id;
SELECT nvl(max(section_sequence),0)
FROM OKC_SECTIONS_B
WHERE document_type = p_document_type
AND document_id = p_document_id
AND scn_id=lc_scn_id;
SELECT nvl(max(display_sequence),0)
FROM OKC_K_ARTICLES_B
WHERE document_type = p_document_type
AND document_id = p_document_id
AND scn_id=lc_scn_id;
SELECT intent FROM okc_bus_doc_types_b
WHERE document_type = p_document_type;
/*If display sequence is passed then insert the clause at that position only.
Else add it at the end*/
IF p_display_sequence IS NULL THEN
IF ls_ref_sequence >= la_ref_sequence THEN
l_ref_sequence := ls_ref_sequence + 10;
SELECT id
FROM
OKC_SECTIONS_B SEC
WHERE SEC.ID = p_section_id
AND DOCUMENT_TYPE = p_document_type
AND DOCUMENT_ID = p_document_id;
SELECT id
FROM
OKC_SECTIONS_B SEC
WHERE SEC.heading = p_section_name
AND DOCUMENT_TYPE = p_document_type
AND DOCUMENT_ID = p_document_id;
SELECT lookup_code,meaning,description
FROM
FND_LOOKUPS
WHERE lookup_type = 'OKC_ARTICLE_SECTION'
AND lookup_code = p_section_name;
SELECT max(section_sequence)
FROM
OKC_SECTIONS_B
WHERE document_id = p_document_id
AND document_type = p_document_type
AND scn_id IS NULL;
SELECT 1
FROM
OKC_TEMPLATE_USAGES
WHERE document_type = p_document_type
AND document_id = p_document_id;
/*Delete Doc will delete all the template associated to the document, the sections
and clauses associated, the deliverables and the contract documents. */
/*
PROCEDURE Delete_Doc (
p_api_version IN NUMBER,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER )
IS
CURSOR c_tmpl_usgs_exists IS
SELECT '!' FROM okc_template_usages
WHERE document_type = p_doc_type
AND document_id = p_doc_id;
okc_terms_util_grp.delete_doc(p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id);
END Delete_Doc;
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name =p_user_name;
SELECT b.RESPONSIBILITY_ID,b.APPLICATION_ID
INTO l_resp_id,l_resp_appl_id
FROM fnd_responsibility_tl tl, fnd_responsibility b
WHERE tl.responsibility_name = p_resp_name
AND LANGUAGE = UserEnv('LANG')
AND tl.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID;
select value.flex_value_id
--,value.description
from fnd_flex_values_vl value
where value.FLEX_VALUE_SET_ID = p_value_set_id
and value.flex_value = p_var_value;
select
val_tab.application_table_name,
val_tab.value_column_name,
val_tab.id_column_name,
val_tab.additional_where_clause,
val_tab.meaning_column_name
from fnd_flex_validation_tables val_tab
where val_tab.FLEX_VALUE_SET_ID = p_value_set_id ;
l_select_stmt VARCHAR2(2000);
l_select_stmt := ' SELECT ' || NVL(c2rec.id_column_name,null) ||' as Flex_value_id,'||
NVL(c2rec.value_column_name,'null') ||' as Flex_value,'||
NVL(c2rec.meaning_column_name,'null') ||' as Flex_meaning FROM '||
c2rec.application_table_name ;
l_select_stmt := l_select_stmt||' WHERE';
l_select_stmt :=l_select_stmt||' '|| c2rec.additional_where_clause;
l_select_stmt := 'SELECT FLEX_VALUE_ID FROM ('||l_select_stmt||') WHERE FLEX_VALUE = :1';
EXECUTE IMMEDIATE(l_select_stmt) INTO l_value_id USING p_var_value;
PROCEDURE update_variable_values(p_api_version IN NUMBER,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_k_clause_id IN NUMBER DEFAULT NULL,
p_clause_title IN VARCHAR2 DEFAULT NULL,
p_clause_version IN NUMBER DEFAULT NULL,
p_variable_name IN VARCHAR2,
p_variable_value IN VARCHAR2,
p_override_global_yn IN VARCHAR2,
p_global_variable_value IN VARCHAR2 := NULL,
p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
p_commit IN Varchar2 default FND_API.G_FALSE,
x_return_status OUT NOCOPY Varchar2,
x_msg_data OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number
)
IS
l_api_version CONSTANT NUMBER := 1;
SELECT b.variable_code, b.variable_type, b.external_yn, b.value_set_id
FROM okc_bus_variables_tl tl, okc_bus_variables_b b
WHERE tl.variable_name = p_variable_name
AND b.variable_code = tl.variable_code
AND b.variable_intent = p_var_intent
AND LANGUAGE = UserEnv('LANG');
SELECT value_set_id
FROM okc_bus_variables_b
WHERE variable_code = p_variable_code;
SELECT flex_value_id
FROM fnd_flex_values
WHERE flex_value = p_variable_value
AND FLEX_VALUE_SET_ID = p_attr_value_set_id;
SELECT intent FROM okc_bus_doc_types_b
WHERE document_type = p_doc_type;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered OKC_TERMS_MIGRATE_GRP.update_variable_values');
SAVEPOINT g_update_variable_values_GRP;
SELECT validation_type INTO l_validation_type
FROM fnd_flex_value_sets WHERE flex_value_set_id = l_attr_value_set_id;
SELECT kb.id INTO l_k_art_id
FROM okc_k_articles_b kb, okc_articles_all a , okc_article_versions v
WHERE a.article_title = p_clause_title AND a.org_id = G_CURRENT_ORG_ID
AND kb.document_type = p_doc_type AND kb.document_id = p_doc_id
AND v.article_version_number = p_clause_version AND a.article_id = v.article_id
AND kb.sav_sae_id = a.article_id AND kb.article_version_id = v.article_version_id;
SELECT 'Y' INTO l_var_exists
FROM okc_k_art_variables
WHERE cat_id = l_k_art_id
AND variable_code = l_variable_code;
OKC_K_ART_VARIABLES_PVT.insert_row(
x_return_status => x_return_status,
p_cat_id => l_k_art_id,
p_variable_code => l_variable_code,
p_variable_type => l_variable_type,
p_external_yn => l_external_yn,
p_variable_value_id => l_variable_value_id,
p_variable_value => p_variable_value,
p_attribute_value_set_id => l_attr_value_set_id,
p_override_global_yn => p_override_global_yn,
p_global_variable_value => p_global_variable_value,
p_global_var_value_id => l_global_var_value_id,
x_cat_id => l_x_cat_id,
x_variable_code => l_x_variable_code);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'300: Finished OKC_TERMS_MIGRATE_GRP.update_variable_values, return status'||x_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving update_variable_values');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving update_variable_values: OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO g_update_variable_values_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving update_variable_values: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO g_update_variable_values_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving update_variable_values because of EXCEPTION: '||sqlerrm);
ROLLBACK TO g_update_variable_values_GRP;
END update_variable_values;
SELECT template_id,
template_name,
status_code,
start_date,
end_date,
intent,
org_id,
tmpl_numbering_scheme
FROM
OKC_TERMS_TEMPLATES_ALL TMP
WHERE
TMP.template_id = p_contract_tmpl_id;
SELECT template_id,
template_name,
status_code,
start_date,
end_date,
intent,
org_id,
tmpl_numbering_scheme
FROM
OKC_TERMS_TEMPLATES_ALL TMP
WHERE TMP.template_name = p_contract_tmpl_name
AND TMP.org_id = l_org_id;
SELECT 1
FROM
OKC_TEMPLATE_USAGES
WHERE document_type = p_document_type
AND document_id = p_document_id;
SELECT template_id
FROM
OKC_TEMPLATE_USAGES
WHERE document_type = p_document_type
AND document_id = p_document_id
UNION ALL
SELECT template_id
FROM
OKC_MLP_TEMPLATE_USAGES
WHERE document_type = p_document_type
AND document_id = p_document_id;
SELECT 1
FROM
OKC_ALLOWED_TMPL_USAGES
WHERE document_type = p_document_type
AND template_id = l_template_id;
SELECT intent FROM okc_bus_doc_types_b
WHERE document_type = p_document_type;
SELECT user_id FROM fnd_user WHERE user_name LIKE p_user_name;
SELECT person_id FROM PER_ALL_PEOPLE_F
WHERE full_name = p_person_name;
okc_clm_pkg.insert_usages_row(p_document_type => p_document_type,
p_document_id => p_document_id,
p_template_id => l_tmpl_type_rec.template_id,
p_doc_numbering_scheme => l_tmpl_type_rec.tmpl_numbering_scheme,
p_document_number => g_document_number,
p_article_effective_date => SYSDATE,
p_config_header_id => Null,
p_config_revision_number => Null,
p_valid_config_yn => Null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT before_after
INTO x_before_after
FROM okc_bus_doc_events_b
WHERE bus_doc_event_id = p_event_id;
SELECT message_name
FROM okc_del_messages
WHERE 1 = 1
AND recurring_flag = p_recurring_flag
AND start_fixed_flag = p_start_fixed_flag
AND end_fixed_flag = p_end_fixed_flag
AND Nvl(repeating_frequency_uom,'a') = Nvl(p_repeating_frequency_uom, 'a')
AND Nvl(relative_st_date_uom,'a') = Nvl(p_relative_st_date_uom ,'a')
AND Nvl(relative_end_date_uom,'a') = Nvl(p_relative_end_date_uom,'a')
AND Nvl(start_evt_before_after,'a') = Nvl(p_start_evt_before_after,'a')
AND Nvl(end_evt_before_after,'a') = Nvl(p_end_evt_before_after,'a');
SELECT 'Y'
FROM per_all_people_f e
WHERE e.current_employee_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (e.effective_start_date,
SYSDATE - 1
)
AND NVL (e.effective_end_date,
SYSDATE + 1)
AND person_id = p_contact_id;
SELECT 'Y'
FROM per_all_people_f e
WHERE e.current_npw_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (e.effective_start_date,
SYSDATE - 1
)
AND NVL (e.effective_end_date,
SYSDATE + 1)
AND person_id = p_contact_id;
SELECT 'Y'
FROM okc_bus_doc_events_b start_event,
okc_bus_doc_events_b end_event
WHERE start_event.bus_doc_event_id = p_st_event_id --start event id
AND end_event.bus_doc_event_id = p_end_event_id --end event id
AND start_event.bus_doc_type = end_event.bus_doc_type
UNION
SELECT 'Y'
FROM okc_bus_doc_events_b start_event,
okc_bus_doc_events_b end_event,
okc_bus_doc_types_b end_type
WHERE start_event.bus_doc_event_id = p_st_event_id --start event id
AND end_event.bus_doc_event_id = p_end_event_id --end event id
AND end_type.document_type = end_event.bus_doc_type
AND start_event.bus_doc_type = end_type.target_response_doc_type
UNION
SELECT 'Y'
FROM okc_bus_doc_events_b start_event,
okc_bus_doc_events_b end_event,
okc_bus_doc_types_b start_type
WHERE start_event.bus_doc_event_id = p_st_event_id --start event id
AND end_event.bus_doc_event_id = p_end_event_id --end event id
AND start_type.document_type = start_event.bus_doc_type
AND end_event.bus_doc_type = start_type.target_response_doc_type;
SELECT 'Y'
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, SYSDATE - 1)
AND NVL (TRUNC (end_date_active),
TRUNC (SYSDATE)
);
SELECT 'Y', meaning
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, SYSDATE - 1)
AND NVL (TRUNC (end_date_active),
TRUNC (SYSDATE)
);
SELECT 'Y'
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, SYSDATE - 1)
AND NVL (TRUNC (end_date_active),
TRUNC (SYSDATE)
);
SELECT 'Y', meaning
FROM fnd_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, SYSDATE - 1)
AND NVL (TRUNC (end_date_active),
TRUNC (SYSDATE)
);
SELECT 'Y'
FROM okc_bus_doc_events_v evts,
okc_bus_doc_types_b doctypes,
okc_del_bus_doc_combxns delcomb,
okc_deliverable_types_b deltypes
WHERE evts.bus_doc_event_id = p_bus_doc_event_id
AND deltypes.deliverable_type_code = p_deliverable_type
AND doctypes.document_type_class = delcomb.document_type_class
AND deltypes.deliverable_type_code = delcomb.deliverable_type_code
AND docTypes.document_type = p_document_type
AND doctypes.document_type = evts.bus_doc_type
AND ( evts.start_end_qualifier = 'BOTH'
OR evts.start_end_qualifier = 'START'
)
UNION
SELECT 'Y'
FROM okc_bus_doc_events_v evts,
okc_bus_doc_types_b doctypes,
okc_del_bus_doc_combxns delcomb,
okc_deliverable_types_b deltypes
WHERE evts.bus_doc_event_id = p_bus_doc_event_id
AND deltypes.deliverable_type_code = p_deliverable_type
AND doctypes.document_type_class = delcomb.document_type_class
AND deltypes.deliverable_type_code = delcomb.deliverable_type_code
AND docTypes.document_type = G_DOC_TYPE_REC.TARGET_RESPONSE_DOC_TYPE
AND doctypes.document_type = evts.bus_doc_type
AND ( evts.start_end_qualifier = 'BOTH'
OR evts.start_end_qualifier = 'START'
)
UNION
SELECT 'Y'
FROM okc_bus_doc_events_v evts,
okc_bus_doc_types_b doctypes,
okc_del_bus_doc_combxns delcomb,
okc_deliverable_types_b deltypes
WHERE 1=1
AND evts.bus_doc_event_id = p_bus_doc_event_id
AND deltypes.deliverable_type_code = p_deliverable_type
AND doctypes.document_type_class = delcomb.document_type_class
AND deltypes.deliverable_type_code = delcomb.deliverable_type_code
AND doctypes.document_type = G_TARGET_DOC_TYPE
AND doctypes.document_type = evts.bus_doc_type
AND ( evts.start_end_qualifier = 'BOTH'
OR evts.start_end_qualifier = 'START')
;
select 'Y'
from okc_bus_doc_events_v evts, okc_bus_doc_types_b docTypes,
okc_del_bus_doc_combxns delComb,
okc_deliverable_types_b delTypes
WHERE evts.bus_doc_event_id = p_bus_doc_event_id
AND delTypes.deliverable_type_code = p_deliverable_type
AND docTypes.document_type_class = delComb.document_type_class
AND delTypes.deliverable_type_code = delComb.deliverable_type_code
AND docTypes.document_type = p_document_type
AND docTypes.document_type = evts.bus_doc_type
AND (evts.start_end_qualifier = 'BOTH' or evts.start_end_qualifier = 'END')
UNION
select 'Y'
from okc_bus_doc_events_v evts, okc_bus_doc_types_b docTypes,
okc_del_bus_doc_combxns delComb,
okc_deliverable_types_b delTypes
WHERE evts.bus_doc_event_id = p_bus_doc_event_id
AND delTypes.deliverable_type_code = p_deliverable_type --- :selectedDeliverableType
AND docTypes.document_type_class = delComb.document_type_class
AND delTypes.deliverable_type_code = delComb.deliverable_type_code
AND docTypes.document_type = G_TARGET_DOC_TYPE --- :targetDocumentType
AND docTypes.document_type = evts.bus_doc_type
AND (evts.start_end_qualifier = 'BOTH' or evts.start_end_qualifier = 'END')
UNION
select 'Y'
from okc_bus_doc_events_v evts, okc_bus_doc_types_b docTypes,
okc_del_bus_doc_combxns delComb,
okc_deliverable_types_b delTypes
WHERE evts.bus_doc_event_id = p_bus_doc_event_id
AND delTypes.deliverable_type_code = p_deliverable_type --- :selectedDeliverableType
AND docTypes.document_type_class = delComb.document_type_class
AND delTypes.deliverable_type_code = delComb.deliverable_type_code
AND docTypes.document_type = G_DOC_TYPE_REC.TARGET_RESPONSE_DOC_TYPE --- :targetResponseDocumentType
AND docTypes.document_type = evts.bus_doc_type
AND (evts.start_end_qualifier = 'BOTH' or evts.start_end_qualifier = 'END')
UNION
select 'Y'
from okc_bus_doc_events_v evts,
okc_bus_doc_types_b doc_types,
okc_del_bus_doc_combxns delComb,
okc_deliverable_types_b del_types
WHERE
evts.bus_doc_event_id = p_bus_doc_event_id --:start event id
and start_end_qualifier = 'START'
and doc_types.document_type = evts.bus_doc_type
and del_types.deliverable_type_code = p_deliverable_type --:deliverable type
AND doc_types.document_type_class = delComb.document_type_class
AND del_types.deliverable_type_code = delComb.deliverable_type_code
UNION
select 'Y'
FROM okc_bus_doc_events_v evts, okc_bus_doc_types_b docTypes,
okc_del_bus_doc_combxns delComb,
okc_deliverable_types_b delTypes
WHERE evts.bus_doc_event_id = p_bus_doc_event_id
AND delTypes.deliverable_type_code = p_deliverable_type --- :selectedDeliverableType
AND docTypes.document_type_class = delComb.document_type_class
AND delTypes.deliverable_type_code = delComb.deliverable_type_code
AND docTypes.TARGET_RESPONSE_doc_type = p_document_type --- :currentDocumentType
AND docTypes.document_type = evts.bus_doc_type
AND (evts.start_end_qualifier = 'BOTH' or evts.start_end_qualifier = 'END')
;
SELECT DOCUMENT_TYPE_CLASS, intent
FROM OKC_BUS_DOC_TYPES_B
WHERE document_type = p_doc_type;
SELECT 'Y' FROM okc_deliverable_types_b
WHERE deliverable_type_code = p_del_type;
SELECT 'Y'
FROM okc_resp_parties_b
WHERE intent = p_intent
AND document_type_class = p_bus_doc_class;
SELECT 'Y' FROM
hr_all_organization_units
WHERE organization_id = p_party_id;
SELECT 'Y' FROM hz_parties WHERE party_id = p_party_contact_id;
SELECT col_name INTO l_column_name from
(SELECT 'NOTIFY_ESCALATION_VALUE' col_name FROM dual WHERE p_deliverable_rec.NOTIFY_ESCALATION_VALUE IS NULL
UNION
SELECT 'NOTIFY_ESCALATION_UOM' col_name FROM dual WHERE p_deliverable_rec.NOTIFY_ESCALATION_UOM IS NULL
UNION
SELECT 'ESCALATION_ASSIGNEE' col_name FROM dual WHERE p_deliverable_rec.ESCALATION_ASSIGNEE IS NULL);
SELECT col_name INTO l_column_name from
(SELECT 'NOTIFY_PRIOR_DUE_DATE_UOM' col_name FROM dual WHERE p_deliverable_rec.NOTIFY_PRIOR_DUE_DATE_UOM IS NULL
UNION
SELECT 'NOTIFY_PRIOR_DUE_DATE_VALUE' col_name FROM dual WHERE p_deliverable_rec.NOTIFY_PRIOR_DUE_DATE_VALUE IS NULL
);
SELECT column_name
INTO l_column_name
FROM (SELECT 'RECURRING_YN' column_name
FROM DUAL
WHERE p_deliverable_rec.recurring_yn = 'Y'
UNION
SELECT 'RELATIVE_ST_DATE_DURATION' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_st_date_duration IS NOT NULL
UNION
SELECT 'RELATIVE_ST_DATE_UOM' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_st_date_uom IS NOT NULL
UNION
SELECT 'RELATIVE_ST_DATE_EVENT_ID' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_st_date_event_id IS NOT NULL
UNION
SELECT 'RELATIVE_END_DATE_DURATION' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_end_date_duration IS NOT NULL
UNION
SELECT 'RELATIVE_END_DATE_UOM' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_end_date_uom IS NOT NULL
UNION
SELECT 'RELATIVE_END_DATE_EVENT_ID' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_end_date_event_id IS NOT NULL
UNION
SELECT 'REPEATING_DAY_OF_MONTH' column_name
FROM DUAL
WHERE p_deliverable_rec.repeating_day_of_month IS NOT NULL
UNION
SELECT 'REPEATING_DAY_OF_WEEK' column_name
FROM DUAL
WHERE p_deliverable_rec.repeating_day_of_week IS NOT NULL
UNION
SELECT 'REPEATING_FREQUENCY_UOM' column_name
FROM DUAL
WHERE p_deliverable_rec.repeating_frequency_uom IS NOT NULL
UNION
SELECT 'REPEATING_DURATION' column_name
FROM DUAL
WHERE p_deliverable_rec.repeating_duration IS NOT NULL
UNION
SELECT 'FIXED_END_DATE' column_name
FROM DUAL
WHERE p_deliverable_rec.fixed_end_date IS NOT NULL)
WHERE ROWNUM = 1;
SELECT column_name
INTO l_column_name
FROM (SELECT 'RELATIVE_END_DATE_DURATION' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_end_date_duration IS NOT NULL
UNION
SELECT 'RELATIVE_END_DATE_UOM' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_end_date_uom IS NOT NULL
UNION
SELECT 'RELATIVE_END_DATE_EVENT_ID' column_name
FROM DUAL
WHERE p_deliverable_rec.relative_end_date_event_id IS NOT NULL
UNION
SELECT 'REPEATING_DAY_OF_MONTH' column_name
FROM DUAL
WHERE p_deliverable_rec.repeating_day_of_month IS NOT NULL
UNION
SELECT 'REPEATING_DAY_OF_WEEK' column_name
FROM DUAL
WHERE p_deliverable_rec.repeating_day_of_week IS NOT NULL
UNION
SELECT 'REPEATING_FREQUENCY_UOM' column_name
FROM DUAL
WHERE p_deliverable_rec.repeating_frequency_uom IS NOT NULL
UNION
SELECT 'REPEATING_DURATION' column_name
FROM DUAL
WHERE p_deliverable_rec.repeating_duration IS NOT NULL
UNION
SELECT 'FIXED_END_DATE' column_name
FROM DUAL
WHERE p_deliverable_rec.fixed_end_date IS NOT NULL
UNION
SELECT 'FIXED_START_DATE' column_name
FROM DUAL
WHERE p_deliverable_rec.fixed_start_date IS NOT NULL)
WHERE ROWNUM = 1;
select column_name INTO l_column_name from
(
select 'REPEATING_DURATION' column_name from dual where p_deliverable_rec.REPEATING_DURATION is null
union
select 'REPEATING_FREQUENCY_UOM' column_name from dual where p_deliverable_rec.REPEATING_FREQUENCY_UOM is null
union
select 'REPEATING_DAY_OF_WEEK' column_name from dual where p_deliverable_rec.REPEATING_FREQUENCY_UOM = 'WK' and p_deliverable_rec.REPEATING_DAY_OF_WEEK is null
union
select 'REPEATING_DAY_OF_MONTH' column_name from dual where p_deliverable_rec.REPEATING_FREQUENCY_UOM = 'MTH' and p_deliverable_rec.REPEATING_DAY_OF_MONTH is null
)
where rownum =1;
select column_name into l_column_name from
(
select 'RELATIVE_ST_DATE_DURATION' column_name from dual where p_deliverable_rec.RELATIVE_ST_DATE_DURATION IS NOT NULL
union
select 'RELATIVE_ST_DATE_UOM' column_name from dual where p_deliverable_rec.RELATIVE_ST_DATE_UOM is not null
union
select 'RELATIVE_ST_DATE_EVENT_ID' column_name from dual where p_deliverable_rec.RELATIVE_ST_DATE_EVENT_ID is not null
)
where rownum =1;
select column_name into l_column_name from
(
select 'RELATIVE_ST_DATE_DURATION' column_name from dual where p_deliverable_rec.RELATIVE_ST_DATE_DURATION IS NULL
union
select 'RELATIVE_ST_DATE_UOM' column_name from dual where p_deliverable_rec.RELATIVE_ST_DATE_UOM is NULL
)
where rownum =1;
select column_name into l_column_name from
(
select 'RELATIVE_END_DATE_DURATION' column_name from dual where p_deliverable_rec.RELATIVE_END_DATE_DURATION IS NOT NULL
union
select 'RELATIVE_END_DATE_UOM' column_name from dual where p_deliverable_rec.RELATIVE_END_DATE_UOM is not null
union
select 'RELATIVE_END_DATE_EVENT_ID' column_name from dual where p_deliverable_rec.RELATIVE_END_DATE_EVENT_ID is not null
)
where rownum =1;
select column_name into l_column_name from
(
select 'RELATIVE_END_DATE_DURATION' column_name from dual where p_deliverable_rec.RELATIVE_END_DATE_DURATION IS NULL
union
select 'RELATIVE_END_DATE_UOM' column_name from dual where p_deliverable_rec.RELATIVE_END_DATE_UOM is NULL
)
where rownum =1;
SELECT business_event_code, before_after, 'Y'
INTO l_starteventcode, l_startba, l_continue
FROM okc_bus_doc_events_b
WHERE bus_doc_event_id =
p_deliverable_rec.relative_st_date_event_id;
SELECT business_event_code, before_after, 'Y'
INTO l_endeventcode, l_endba, l_continue
FROM okc_bus_doc_events_b
WHERE bus_doc_event_id =
p_deliverable_rec.relative_end_date_event_id;
SELECT 'X'
FROM
okc_bus_doc_types_b doctyp,
okc_del_bus_doc_combxns deltypcomb
WHERE
doctyp.document_type_class = deltypcomb.document_type_class
AND doctyp.document_type = p_document_type
AND deltypcomb.deliverable_type_code = p_deliverable_rec.deliverable_type;
SELECT okc_deliverable_id_s.NEXTVAL
INTO p_deliverable_rec.deliverable_id
FROM DUAL;
p_deliverable_rec.last_updated_by := fnd_global.user_id;
p_deliverable_rec.last_update_date := SYSDATE;
p_deliverable_rec.last_update_login := fnd_global.login_id;
select okc_deliverable_id_s.nextval INTO delRecTab.deliverable_id from dual;
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,
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,
external_party_role,
pay_hold_prior_due_date_yn,
pay_hold_prior_due_date_value,
pay_hold_prior_due_date_uom,
pay_hold_overdue_yn
-- serukull changes
,orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2
-- serukull changes
,raise_completion_event_yn
)
VALUES (delRecTab.deliverable_id,
delRecTab.business_document_type,
delRecTab.business_document_id,
g_document_number,
delRecTab.deliverable_type,
delRecTab.responsible_party,
delRecTab.internal_party_contact_id,
delRecTab.external_party_contact_id,
delRecTab.deliverable_name,
delRecTab.description, p_deliverable_rec.comments,
delRecTab.display_sequence,
delRecTab.fixed_due_date_yn,
delRecTab.actual_due_date,
delRecTab.print_due_date_msg_name,
delRecTab.recurring_yn,
delRecTab.notify_prior_due_date_value,
delRecTab.notify_prior_due_date_uom,
delRecTab.notify_prior_due_date_yn,
delRecTab.notify_completed_yn,
delRecTab.notify_overdue_yn,
delRecTab.notify_escalation_yn,
delRecTab.notify_escalation_value,
delRecTab.notify_escalation_uom,
delRecTab.escalation_assignee,
delRecTab.amendment_operation,
delRecTab.prior_notification_id,
delRecTab.amendment_notes,
delRecTab.completed_notification_id,
delRecTab.overdue_notification_id,
delRecTab.escalation_notification_id,
delRecTab.LANGUAGE,
delRecTab.original_deliverable_id,
delRecTab.requester_id,
delRecTab.external_party_id,
delRecTab.recurring_del_parent_id,
delRecTab.business_document_version,
delRecTab.relative_st_date_duration,
delRecTab.relative_st_date_uom,
delRecTab.relative_st_date_event_id,
delRecTab.relative_end_date_duration,
delRecTab.relative_end_date_uom,
delRecTab.relative_end_date_event_id,
delRecTab.repeating_day_of_month,
delRecTab.repeating_day_of_week,
delRecTab.repeating_frequency_uom,
delRecTab.repeating_duration,
delRecTab.fixed_start_date,
delRecTab.fixed_end_date,
delRecTab.manage_yn,
delRecTab.internal_party_id,
delRecTab.deliverable_status,
delRecTab.status_change_notes,
delRecTab.created_by,
delRecTab.creation_date,
delRecTab.last_updated_by,
delRecTab.last_update_date,
delRecTab.last_update_login,
delRecTab.object_version_number,
delRecTab.attribute_category,
delRecTab.attribute1,
delRecTab.attribute2,
delRecTab.attribute3,
delRecTab.attribute4,
delRecTab.attribute5,
delRecTab.attribute6,
delRecTab.attribute7,
delRecTab.attribute8,
delRecTab.attribute9,
delRecTab.attribute10,
delRecTab.attribute11,
delRecTab.attribute12,
delRecTab.attribute13,
delRecTab.attribute14,
delRecTab.attribute15,
delRecTab.disable_notifications_yn,
delRecTab.last_amendment_date,
delRecTab.business_document_line_id,
delRecTab.external_party_site_id,
delRecTab.start_event_date,
delRecTab.end_event_date,
delRecTab.summary_amend_operation_code,
delRecTab.external_party_role,
delRecTab.pay_hold_prior_due_date_yn,
delRecTab.pay_hold_prior_due_date_value,
delRecTab.pay_hold_prior_due_date_uom,
delRecTab.pay_hold_overdue_yn
-- serukull changes
,delRecTab.orig_system_reference_code,
delRecTab.orig_system_reference_id1,
delRecTab.orig_system_reference_id2
-- serukull changes
,delRecTab.raise_completion_event_yn
);
SELECT
article_id,
article_title,
article_version_id,
article_version_number,
article_intent,
provision_yn,
article_status,
start_date,
end_date
FROM
OKC_ARTICLES_V ART
WHERE ART.article_version_id = p_clause_version_id;
SELECT
article_id,
article_title,
article_version_id,
article_version_number,
article_intent,
provision_yn,
article_status,
start_date,
end_date
FROM
okc_articles_local_v ART
WHERE ART.article_title = p_clause_title
AND ART.org_id = l_org_id
UNION ALL
SELECT
article_id,
article_title,
article_version_id,
article_version_number,
article_intent,
provision_yn,
article_status,
start_date,
end_date
FROM
okc_articles_global_v ART
WHERE ART.article_title = p_clause_title
AND ART.org_id = l_org_id;
SELECT id, object_version_number
FROM okc_k_articles_b
WHERE p_document_type = p_document_type
AND document_id = p_document_id
AND sav_sae_id = p_article_id;
/*Make x_return_status as null. So if any clauses get deleted it will change to 'S' and if any errors it will change to 'E' or 'U'.
If its null then no clauses got updated.*/
FOR c_get_doc_art_id_csr_rec IN c_get_doc_art_id_csr(l_cls_type_rec.article_id) LOOP
remove_clause_id_from_doc(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_mode => p_mode,
p_document_type => p_document_type,
p_document_id => p_document_id,
p_clause_id => c_get_doc_art_id_csr_rec.id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT object_version_number
FROM okc_k_articles_b
WHERE p_document_type = p_document_type
AND document_id = p_document_id
AND id = p_clause_id;
OKC_K_ARTICLES_GRP.delete_article(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_validate_commit => FND_API.G_FALSE,
p_validation_string => Null,
p_commit => FND_API.G_FALSE,
p_mode => p_mode,
p_id => p_clause_id,
p_object_version_number => l_object_version_number,
p_mandatory_clause_delete => 'Y',
p_super_user_yn => 'N',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
,p_lock_terms_yn => p_locking_enabled_yn
);