[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 hr_operating_units ou, hr_organization_information oi
WHERE mo_global.check_access (ou.organization_id) = 'Y'
AND oi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
AND oi.organization_id = ou.organization_id
AND NVL (date_to, SYSDATE) >= SYSDATE
AND ou.organization_id = p_org_id;
g_clause_update_allowed VARCHAR2 (1);
g_deliverable_update_allowed VARCHAR2 (1);
g_headerinfo_update_allowed VARCHAR2 (1);
PROCEDURE update_template (
p_template_rec IN OUT NOCOPY terms_template_rec_type
);
PROCEDURE update_tmpl_usage (
p_template_id IN NUMBER,
p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
);
PROCEDURE delete_tmpl_usage (
p_template_id IN NUMBER,
p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
);
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_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 'TEMPLATE' = p_document_type
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 = evts.bus_doc_type
AND ( doctypes.document_type IN (
SELECT target_response_doc_type
FROM okc_bus_doc_types_b
WHERE document_type_class =
delcomb.document_type_class
AND target_response_doc_type IS NOT NULL)
OR doctypes.show_in_lov_yn = 'Y'
)
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 'TEMPLATE' = p_document_type
AND 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.show_in_lov_yn = 'Y'
AND doctypes.document_type = evts.bus_doc_type
AND ( evts.start_end_qualifier = 'BOTH'
OR evts.start_end_qualifier = 'END'
);
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 repeating_frequency_uom = p_repeating_frequency_uom
AND relative_st_date_uom = p_relative_st_date_uom
AND relative_end_date_uom = p_relative_end_date_uom
AND start_evt_before_after = p_start_evt_before_after
AND end_evt_before_after = p_end_evt_before_after;
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 template_id, intent, status_code,
start_date, end_date, org_id
INTO g_template_id, g_template_intent, g_template_status_code,
g_template_start_date, g_template_end_date, g_template_org_id
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
g_clause_update_allowed := 'Y';
g_deliverable_update_allowed := 'Y';
g_headerinfo_update_allowed := 'Y';
g_clause_update_allowed := 'N';
g_deliverable_update_allowed := 'N';
g_headerinfo_update_allowed := 'Y';
g_headerinfo_update_allowed := 'N';
SELECT 'Y'
INTO l_flag
FROM okc_terms_templates_all
WHERE template_id = p_template_id AND org_id = p_org_id;
SELECT 'Y'
FROM okc_sections_b
WHERE document_type = 'TEMPLATE'
AND document_id = p_template_id
AND ID = p_scn_id;
SELECT 'Y'
FROM okc_articles_all
WHERE 1 = 1
AND standard_yn = 'Y'
AND org_id = p_org_id
AND article_intent = p_intent
AND article_id = p_article_id;
SELECT 'Y'
FROM okc_articles_all art,
okc_article_versions ver,
okc_article_adoptions adp
WHERE art.article_id = ver.article_id
AND art.standard_yn = 'Y'
AND ver.global_yn = 'Y'
AND ver.article_status = 'APPROVED'
AND adp.global_article_version_id = ver.article_version_id
AND adp.adoption_type = 'ADOPTED'
AND adp.adoption_status = 'APPROVED'
AND art.article_id = p_article_id
AND art.article_intent = p_intent
AND adp.local_org_id = p_org_id;
SELECT 'Y'
INTO l_valid_bus_doc_type
FROM okc_bus_doc_types_b bus, fnd_lookups fnd
WHERE bus.document_type <> 'TEMPLATE'
AND DECODE (bus.document_type_class,
'REPOSITORY', NVL (bus.enable_contract_terms_yn, 'N'),
NVL (bus.show_in_lov_yn, 'Y')
) = 'Y'
AND bus.intent = fnd.lookup_code
AND fnd.lookup_type = 'OKC_TERMS_INTENT'
AND bus.intent = l_tmpl_intent
AND bus.document_type = l_bus_doc_type;
p_mode IN VARCHAR2 DEFAULT 'UPDATE'
)
RETURN VARCHAR2
IS
l_valid_tmpl_usage_id VARCHAR2 (1) := 'Y';
SELECT *
INTO l_db_rec
FROM okc_allowed_tmpl_usages
WHERE template_id = p_template_id
AND allowed_tmpl_usages_id = p_tmpl_usage_rec.allowed_tmpl_usages_id;
IF p_mode = 'UPDATE'
THEN
IF (p_tmpl_usage_rec.document_type = okc_api.g_miss_char)
THEN
p_tmpl_usage_rec.document_type := l_db_rec.document_type;
SELECT 'X'
FROM hr_operating_units ou, hr_organization_information oi
WHERE mo_global.check_access (ou.organization_id) = 'Y'
AND oi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
AND oi.organization_id = ou.organization_id
AND NVL (date_to, SYSDATE) >= SYSDATE
AND ou.organization_id = p_org_id;
PROCEDURE update_template (
p_template_rec IN OUT NOCOPY terms_template_rec_type
)
IS
l_template_db_rec okc_terms_templates_all%ROWTYPE;
IF p_template_rec.last_update_login = okc_api.g_miss_num
THEN
p_template_rec.last_update_login :=
p_template_db_rec.last_update_login;
IF p_template_rec.last_updated_by = okc_api.g_miss_num
THEN
p_template_rec.last_updated_by :=
p_template_db_rec.last_updated_by;
IF p_template_rec.last_update_date = okc_api.g_miss_date
THEN
p_template_rec.last_update_date :=
p_template_db_rec.last_update_date;
SELECT *
INTO l_template_db_rec
FROM okc_terms_templates_all
WHERE template_id = p_template_rec.template_id;
IF g_headerinfo_update_allowed = 'N'
THEN
l_progress := '020';
END IF; -- g_headerinfo_update_allowed = 'N'
okc_terms_templates_grp.update_template
(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
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_template_name => p_template_rec.template_name,
p_template_id => p_template_rec.template_id,
p_working_copy_flag => p_template_rec.working_copy_flag,
p_intent => p_template_rec.intent,
p_status_code => p_template_rec.status_code,
p_start_date => p_template_rec.start_date,
p_end_date => p_template_rec.end_date,
p_global_flag => p_template_rec.global_flag,
p_parent_template_id => p_template_rec.parent_template_id,
p_print_template_id => p_template_rec.print_template_id,
p_contract_expert_enabled => p_template_rec.contract_expert_enabled,
p_xprt_clause_mandatory_flag => p_template_rec.xprt_clause_mandatory_flag,
p_xprt_scn_code => p_template_rec.xprt_scn_code,
p_template_model_id => p_template_rec.template_model_id,
p_instruction_text => p_template_rec.instruction_text,
p_tmpl_numbering_scheme => p_template_rec.tmpl_numbering_scheme,
p_description => p_template_rec.description,
p_approval_wf_key => p_template_rec.approval_wf_key,
p_cz_export_wf_key => p_template_rec.cz_export_wf_key,
p_orig_system_reference_code => p_template_rec.orig_system_reference_code,
p_orig_system_reference_id1 => p_template_rec.orig_system_reference_id1,
p_orig_system_reference_id2 => p_template_rec.orig_system_reference_id2,
p_org_id => p_template_rec.org_id,
p_attribute_category => p_template_rec.attribute_category,
p_attribute1 => p_template_rec.attribute1,
p_attribute2 => p_template_rec.attribute2,
p_attribute3 => p_template_rec.attribute3,
p_attribute4 => p_template_rec.attribute4,
p_attribute5 => p_template_rec.attribute5,
p_attribute6 => p_template_rec.attribute6,
p_attribute7 => p_template_rec.attribute7,
p_attribute8 => p_template_rec.attribute8,
p_attribute9 => p_template_rec.attribute9,
p_attribute10 => p_template_rec.attribute10,
p_attribute11 => p_template_rec.attribute11,
p_attribute12 => p_template_rec.attribute12,
p_attribute13 => p_template_rec.attribute13,
p_attribute14 => p_template_rec.attribute14,
p_attribute15 => p_template_rec.attribute15,
p_translated_from_tmpl_id => p_template_rec.translated_from_tmpl_id,
p_language => p_template_rec.LANGUAGE,
p_object_version_number => p_template_rec.object_version_number
);
END update_template;
PROCEDURE update_template (
p_template_tbl IN OUT NOCOPY okc_imp_terms_templates_pvt.terms_template_tbl_type,
p_commit IN VARCHAR2 := fnd_api.g_false
)
IS
l_success_count NUMBER := 0;
l_proc VARCHAR2 (60) := 'UPDATE_TEMPLATE';
SAVEPOINT update_template_sp;
update_template (p_template_rec => p_template_tbl (i));
ROLLBACK TO update_template_sp;
ROLLBACK TO update_template_sp;
ROLLBACK TO update_template_sp;
ROLLBACK TO update_template_sp;
END update_template;
/* SELECT okc_sections_b_s.NEXTVAL
INTO p_section_rec.ID
FROM DUAL; */
IF p_section_rec.last_updated_by = okc_api.g_miss_num
THEN
p_section_rec.last_updated_by := fnd_global.user_id;
IF p_section_rec.last_update_date = okc_api.g_miss_date
THEN
p_section_rec.last_update_date := SYSDATE;
IF p_section_rec.last_update_login = okc_api.g_miss_num
THEN
p_section_rec.last_update_login := fnd_global.login_id;
IF g_clause_update_allowed <> 'Y'
THEN
okc_api.set_message (p_app_name => g_app_name,
p_msg_name => 'OKC_I_TEMP_STS_NO_INS_OBJ',
p_token1 => 'STATUS',
p_token1_value => g_template_status_code,
p_token2 => 'OBJECT',
p_token2_value => 'SECTION'
);
SELECT
INTO p_article_rec.ID
FROM DUAL;
IF p_article_rec.last_updated_by = okc_api.g_miss_num
THEN
p_article_rec.last_updated_by := fnd_global.user_id;
IF p_article_rec.last_update_date = okc_api.g_miss_date
THEN
p_article_rec.last_update_date := SYSDATE;
IF p_article_rec.last_update_login = okc_api.g_miss_num
THEN
p_article_rec.last_update_login := fnd_global.login_id;
IF g_clause_update_allowed <> 'Y'
THEN
okc_api.set_message (p_app_name => g_app_name,
p_msg_name => 'OKC_I_TEMP_STS_NO_INS_OBJ',
p_token1 => 'STATUS',
p_token1_value => g_template_status_code,
p_token2 => 'OBJECT',
p_token2_value => 'ARTICLE'
);
INSERT INTO okc_k_articles_b
(ID, sav_sae_id,
sav_sav_release, sbt_code,
cat_type, chr_id,
cle_id, cat_id,
dnz_chr_id,
object_version_number,
created_by, creation_date,
last_updated_by,
last_update_date, fulltext_yn,
last_update_login,
attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15,
security_group_id, old_id,
document_type, document_id,
source_flag, mandatory_yn,
scn_id, label,
display_sequence,
amendment_description,
article_version_id,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2,
amendment_operation_code,
summary_amend_operation_code,
change_nonstd_yn,
print_text_yn, ref_article_id,
ref_article_version_id,
orig_article_id,
last_amended_by,
last_amendment_date,
mandatory_rwa
)
VALUES (okc_k_articles_b_s.NEXTVAL, p_article_rec.sav_sae_id,
p_article_rec.sav_sav_release, p_article_rec.sbt_code,
p_article_rec.cat_type, p_article_rec.chr_id,
p_article_rec.cle_id, p_article_rec.cat_id,
p_article_rec.dnz_chr_id,
p_article_rec.object_version_number,
p_article_rec.created_by, p_article_rec.creation_date,
p_article_rec.last_updated_by,
p_article_rec.last_update_date, p_article_rec.fulltext_yn,
p_article_rec.last_update_login,
p_article_rec.attribute_category,
p_article_rec.attribute1, p_article_rec.attribute2,
p_article_rec.attribute3, p_article_rec.attribute4,
p_article_rec.attribute5, p_article_rec.attribute6,
p_article_rec.attribute7, p_article_rec.attribute8,
p_article_rec.attribute9, p_article_rec.attribute10,
p_article_rec.attribute11, p_article_rec.attribute12,
p_article_rec.attribute13, p_article_rec.attribute14,
p_article_rec.attribute15,
p_article_rec.security_group_id, p_article_rec.old_id,
p_article_rec.document_type, p_article_rec.document_id,
p_article_rec.source_flag, p_article_rec.mandatory_yn,
p_article_rec.scn_id, p_article_rec.label,
p_article_rec.display_sequence,
p_article_rec.amendment_description,
p_article_rec.article_version_id,
p_article_rec.orig_system_reference_code,
p_article_rec.orig_system_reference_id1,
p_article_rec.orig_system_reference_id2,
p_article_rec.amendment_operation_code,
p_article_rec.summary_amend_operation_code,
p_article_rec.change_nonstd_yn,
p_article_rec.print_text_yn, p_article_rec.ref_article_id,
p_article_rec.ref_article_version_id,
p_article_rec.orig_article_id,
p_article_rec.last_amended_by,
p_article_rec.last_amendment_date,
p_article_rec.mandatory_rwa
)
RETURNING ID
INTO p_article_rec.ID;
PROCEDURE delete_articles (
p_template_id IN NUMBER,
p_k_article_id_tbl IN k_article_id_tbl_type,
p_commit IN VARCHAR2 := fnd_api.g_false,
x_k_article_id_tbl OUT NOCOPY k_article_id_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_proc VARCHAR2 (60) := 'DELETE_ARTICLES';
IF g_clause_update_allowed <> 'Y'
THEN
okc_api.set_message (p_app_name => g_app_name,
p_msg_name => 'OKC_I_TEMP_STS_NO_DEL_OBJ',
p_token1 => 'STATUS',
p_token1_value => g_template_status_code,
p_token2 => 'OBJECT',
p_token2_value => 'ARTICLE'
);
DELETE FROM okc_k_articles_b
WHERE ID = p_k_article_id_tbl (i)
AND document_type = 'TEMPLATE'
AND document_id = p_template_id
RETURNING ID
BULK COLLECT INTO x_k_article_id_tbl;
END delete_articles;
PROCEDURE delete_sections (
p_template_id IN NUMBER,
p_section_id_tbl IN section_id_tbl_type,
p_commit IN VARCHAR2 := fnd_api.g_false,
x_section_id_tbl OUT NOCOPY section_id_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--l_k_article_id_tbl k_article_id_tbl_type
l_proc VARCHAR2 (60) := 'DELETE_SECTIONS';
IF g_clause_update_allowed <> 'Y'
THEN
okc_api.set_message (p_app_name => g_app_name,
p_msg_name => 'OKC_I_TEMP_STS_NO_DEL_OBJ',
p_token1 => 'STATUS',
p_token1_value => g_template_status_code,
p_token2 => 'OBJECT',
p_token2_value => 'SECTION'
);
DELETE FROM okc_sections_b
WHERE ID = p_section_id_tbl (i)
AND document_type = 'TEMPLATE'
AND document_id = p_template_id
RETURNING ID
BULK COLLECT INTO x_section_id_tbl;
DELETE FROM okc_k_articles_b
WHERE scn_id = x_section_id_tbl (i)
AND document_type = 'TEMPLATE'
AND document_id = p_template_id;
END delete_sections;
SELECT okc_deliverable_id_s.NEXTVAL
INTO p_deliverable_rec.deliverable_id
FROM DUAL;
IF p_deliverable_rec.last_updated_by = okc_api.g_miss_num
THEN
p_deliverable_rec.last_updated_by := fnd_global.user_id;
IF p_deliverable_rec.last_update_date = okc_api.g_miss_date
THEN
p_deliverable_rec.last_update_date := SYSDATE;
IF p_deliverable_rec.last_update_login = okc_api.g_miss_num
THEN
p_deliverable_rec.last_update_login := fnd_global.login_id;
//1. user selects fixed start date radio and enters relative start date duration
//2. user selects relative start date radio and enters fixed start date
//3. user selects fixed end date radio and enters relative end date duration
//4. user selects relative end date radio and enters fixed end date
*/
IF ( p_deliverable_rec.recurring_yn = 'N'
AND ( p_deliverable_rec.relative_end_date_duration IS NOT NULL
OR p_deliverable_rec.fixed_end_date IS NOT NULL
OR p_deliverable_rec.repeating_duration IS NOT NULL
)
)
OR ( p_deliverable_rec.relative_st_date_event_id IS NULL
AND p_deliverable_rec.relative_st_date_duration IS NOT NULL
)
OR ( p_deliverable_rec.relative_st_date_event_id IS NOT NULL
AND p_deliverable_rec.fixed_start_date IS NOT NULL
)
OR ( p_deliverable_rec.relative_end_date_event_id IS NULL
AND p_deliverable_rec.relative_end_date_duration IS NOT NULL
)
OR ( p_deliverable_rec.relative_end_date_event_id IS NOT NULL
AND p_deliverable_rec.fixed_end_date IS NOT NULL
)
THEN
okc_api.set_message
(p_app_name => g_app_name,
p_msg_name => 'OKC_DEL_INCONSISTENT_DUE_DATES'
);
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;
IF g_deliverable_update_allowed <> 'Y'
THEN
okc_api.set_message (p_app_name => g_app_name,
p_msg_name => 'OKC_I_TEMP_STS_NO_INS_OBJ',
p_token1 => 'STATUS',
p_token1_value => g_template_status_code,
p_token2 => 'OBJECT',
p_token2_value => 'DELIVERABLE'
);
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,
raise_completion_event_yn
)
VALUES (p_deliverable_rec.deliverable_id,
p_deliverable_rec.business_document_type,
p_deliverable_rec.business_document_id,
TO_CHAR (p_deliverable_rec.business_document_id),
-- business_document_number,
p_deliverable_rec.deliverable_type,
p_deliverable_rec.responsible_party,
p_deliverable_rec.internal_party_contact_id,
p_deliverable_rec.external_party_contact_id,
p_deliverable_rec.deliverable_name,
p_deliverable_rec.description, p_deliverable_rec.comments,
p_deliverable_rec.display_sequence,
p_deliverable_rec.fixed_due_date_yn,
p_deliverable_rec.actual_due_date,
p_deliverable_rec.print_due_date_msg_name,
p_deliverable_rec.recurring_yn,
p_deliverable_rec.notify_prior_due_date_value,
p_deliverable_rec.notify_prior_due_date_uom,
p_deliverable_rec.notify_prior_due_date_yn,
p_deliverable_rec.notify_completed_yn,
p_deliverable_rec.notify_overdue_yn,
p_deliverable_rec.notify_escalation_yn,
p_deliverable_rec.notify_escalation_value,
p_deliverable_rec.notify_escalation_uom,
p_deliverable_rec.escalation_assignee,
p_deliverable_rec.amendment_operation,
p_deliverable_rec.prior_notification_id,
p_deliverable_rec.amendment_notes,
p_deliverable_rec.completed_notification_id,
p_deliverable_rec.overdue_notification_id,
p_deliverable_rec.escalation_notification_id,
p_deliverable_rec.LANGUAGE,
p_deliverable_rec.original_deliverable_id,
p_deliverable_rec.requester_id,
p_deliverable_rec.external_party_id,
p_deliverable_rec.recurring_del_parent_id,
p_deliverable_rec.business_document_version,
p_deliverable_rec.relative_st_date_duration,
p_deliverable_rec.relative_st_date_uom,
p_deliverable_rec.relative_st_date_event_id,
p_deliverable_rec.relative_end_date_duration,
p_deliverable_rec.relative_end_date_uom,
p_deliverable_rec.relative_end_date_event_id,
p_deliverable_rec.repeating_day_of_month,
p_deliverable_rec.repeating_day_of_week,
p_deliverable_rec.repeating_frequency_uom,
p_deliverable_rec.repeating_duration,
p_deliverable_rec.fixed_start_date,
p_deliverable_rec.fixed_end_date,
p_deliverable_rec.manage_yn,
p_deliverable_rec.internal_party_id,
p_deliverable_rec.deliverable_status,
p_deliverable_rec.status_change_notes,
p_deliverable_rec.created_by,
p_deliverable_rec.creation_date,
p_deliverable_rec.last_updated_by,
p_deliverable_rec.last_update_date,
p_deliverable_rec.last_update_login,
p_deliverable_rec.object_version_number,
p_deliverable_rec.attribute_category,
p_deliverable_rec.attribute1,
p_deliverable_rec.attribute2,
p_deliverable_rec.attribute3,
p_deliverable_rec.attribute4,
p_deliverable_rec.attribute5,
p_deliverable_rec.attribute6,
p_deliverable_rec.attribute7,
p_deliverable_rec.attribute8,
p_deliverable_rec.attribute9,
p_deliverable_rec.attribute10,
p_deliverable_rec.attribute11,
p_deliverable_rec.attribute12,
p_deliverable_rec.attribute13,
p_deliverable_rec.attribute14,
p_deliverable_rec.attribute15,
p_deliverable_rec.disable_notifications_yn,
p_deliverable_rec.last_amendment_date,
p_deliverable_rec.business_document_line_id,
p_deliverable_rec.external_party_site_id,
p_deliverable_rec.start_event_date,
p_deliverable_rec.end_event_date,
p_deliverable_rec.summary_amend_operation_code,
p_deliverable_rec.external_party_role,
p_deliverable_rec.pay_hold_prior_due_date_yn,
p_deliverable_rec.pay_hold_prior_due_date_value,
p_deliverable_rec.pay_hold_prior_due_date_uom,
p_deliverable_rec.pay_hold_overdue_yn,
p_deliverable_rec.raise_completion_event_yn
);
PROCEDURE delete_deliverables (
p_template_id IN NUMBER,
p_deliverable_id_tbl IN deliverable_id_tbl_type,
p_commit IN VARCHAR2 := fnd_api.g_false,
x_deliverable_id_tbl OUT NOCOPY deliverable_id_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--l_k_article_id_tbl k_article_id_tbl_type
l_proc VARCHAR2 (60) := 'DELETE_DELIVERABLES';
IF g_deliverable_update_allowed <> 'Y'
THEN
okc_api.set_message (p_app_name => g_app_name,
p_msg_name => 'OKC_I_TEMP_STS_NO_DEL_OBJ',
p_token1 => 'STATUS',
p_token1_value => g_template_status_code,
p_token2 => 'OBJECT',
p_token2_value => 'DELIVERABLE'
);
DELETE FROM okc_deliverables
WHERE deliverable_id = p_deliverable_id_tbl (i)
AND business_document_type = 'TEMPLATE'
AND business_document_id = p_template_id
RETURNING deliverable_id
BULK COLLECT INTO x_deliverable_id_tbl;
END delete_deliverables;
SELECT intent
FROM okc_bus_doc_types_b
WHERE document_type = p_tmpl_usage_rec.document_type;
IF g_headerinfo_update_allowed = 'N'
THEN
l_progress := '020';
IF (p_mode = 'UPDATE' OR p_mode = 'DELETE')
THEN
IF p_tmpl_usage_rec.allowed_tmpl_usages_id IS NULL
THEN
okc_api.set_message (g_app_name,
okc_api.g_required_value,
okc_api.g_col_name_token,
'ALLOWED_TMPL_USAGES_ID'
);
IF p_mode <> 'DELETE'
THEN
-- Validate document Type
IF p_tmpl_usage_rec.document_type IS NULL
THEN
okc_api.set_message (g_app_name,
okc_api.g_required_value,
okc_api.g_col_name_token,
'DOCUMENT_TYPE'
);
IF p_tmpl_usage_rec.last_update_login = okc_api.g_miss_num
THEN
p_tmpl_usage_rec.last_update_login := fnd_global.login_id;
IF p_tmpl_usage_rec.last_updated_by = okc_api.g_miss_num
THEN
p_tmpl_usage_rec.last_updated_by := fnd_global.user_id;
IF p_tmpl_usage_rec.last_update_date = okc_api.g_miss_date
THEN
p_tmpl_usage_rec.last_update_date := SYSDATE;
PROCEDURE update_tmpl_usage (
p_template_id IN NUMBER,
p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
)
IS
l_proc VARCHAR2 (60) := 'UPDATE_TMPL_USAGE';
validate_tmpl_usage (p_template_id, p_tmpl_usage_rec, 'UPDATE');
okc_allowed_tmpl_usages_grp.update_allowed_tmpl_usages
(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
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_template_id => p_template_id,
p_document_type => p_tmpl_usage_rec.document_type,
p_default_yn => p_tmpl_usage_rec.default_yn,
p_allowed_tmpl_usages_id => p_tmpl_usage_rec.allowed_tmpl_usages_id,
p_attribute_category => p_tmpl_usage_rec.attribute_category,
p_attribute1 => p_tmpl_usage_rec.attribute1,
p_attribute2 => p_tmpl_usage_rec.attribute2,
p_attribute3 => p_tmpl_usage_rec.attribute3,
p_attribute4 => p_tmpl_usage_rec.attribute4,
p_attribute5 => p_tmpl_usage_rec.attribute5,
p_attribute6 => p_tmpl_usage_rec.attribute6,
p_attribute7 => p_tmpl_usage_rec.attribute7,
p_attribute8 => p_tmpl_usage_rec.attribute8,
p_attribute9 => p_tmpl_usage_rec.attribute9,
p_attribute10 => p_tmpl_usage_rec.attribute10,
p_attribute11 => p_tmpl_usage_rec.attribute11,
p_attribute12 => p_tmpl_usage_rec.attribute12,
p_attribute13 => p_tmpl_usage_rec.attribute13,
p_attribute14 => p_tmpl_usage_rec.attribute14,
p_attribute15 => p_tmpl_usage_rec.attribute15,
p_object_version_number => p_tmpl_usage_rec.object_version_number
);
END update_tmpl_usage;
PROCEDURE update_tmpl_usage (
p_template_id IN NUMBER,
p_tmpl_usage_tbl IN OUT NOCOPY tmpl_usage_tbl_type,
p_commit IN VARCHAR2 := fnd_api.g_false
)
IS
l_error_message VARCHAR2 (2000);
SAVEPOINT update_tmpl_usage_sp;
update_tmpl_usage (p_template_id => p_template_id,
p_tmpl_usage_rec => p_tmpl_usage_tbl
(i)
);
ROLLBACK TO update_tmpl_usage_sp;
END update_tmpl_usage;
PROCEDURE delete_tmpl_usage (
p_template_id IN NUMBER,
p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
)
IS
l_proc VARCHAR2 (60) := 'DELETE_TMPL_USAGE';
validate_tmpl_usage (p_template_id, p_tmpl_usage_rec, 'DELETE');
okc_allowed_tmpl_usages_grp.delete_allowed_tmpl_usages
(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
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_allowed_tmpl_usages_id => p_tmpl_usage_rec.allowed_tmpl_usages_id,
p_object_version_number => p_tmpl_usage_rec.object_version_number
);
END delete_tmpl_usage;
PROCEDURE delete_tmpl_usage (
p_template_id IN NUMBER,
p_tmpl_usage_tbl IN OUT NOCOPY tmpl_usage_tbl_type,
p_commit IN VARCHAR2 := fnd_api.g_false
)
IS
l_error_message VARCHAR2 (2000);
SAVEPOINT delete_tmpl_usage_sp;
delete_tmpl_usage (p_template_id => p_template_id,
p_tmpl_usage_rec => p_tmpl_usage_tbl
(i)
);
ROLLBACK TO delete_tmpl_usage_sp;
ROLLBACK TO delete_tmpl_usage_sp;
END delete_tmpl_usage;