[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 = x_doc_type
AND deltypcomb.deliverable_type_code = x_deliverable_type;
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 = x_doc_type
AND deltypcomb.deliverable_type_code = x_deliverable_type;
select business_event_code, before_after
from okc_bus_doc_events_b
where bus_doc_event_id = x_sourceEventId;
select bus_doc_event_id
from okc_bus_doc_events_b
where business_event_code = x_sourceEventCode
and bus_doc_type = x_targetBusDocType
and before_after = x_sourceBeforeAfter;
select bus_doc_event_id
from okc_bus_doc_events_b
where business_event_code = x_sourceEventCode
and before_after = x_sourceBeforeAfter
and bus_doc_type in (select target_response_doc_type from okc_bus_doc_types_b
where document_type = x_targetBusDocType);
select document_type_class
from okc_bus_doc_types_b
where document_type = p_bus_doctype;
select
document_type_class
,intent
from
okc_bus_doc_types_b
where document_type = p_bus_doc_type;
select delTyp.internal_flag
from okc_deliverable_types_b delTyp,
okc_del_bus_doc_combxns delComb
where delTyp.deliverable_type_code = p_deliverable_type
and delComb.deliverable_type_code = delTyp.deliverable_type_code
and delComb.document_type_class = p_document_type_class;
SELECT business_event_code, event_name into x_event_name, x_event_full_name
FROM OKC_BUS_DOC_EVENTS_V
WHERE bus_doc_event_id = p_event_id;
SELECT 'X'
FROM fnd_attached_documents
WHERE entity_name = p_entity_name
AND pk1_value = p_pk1_value;
business document WHERE amendment_operation is NOT 'DELETE'.
(The reson of this check: In case of RFQ, amendments operation
and descriptions are maintained in the current copy,
hence all deletes are just soft deletes.
So the copy procedure should not copy deliverables which
were deleted from the RFQ during amendment).
3. Create instances of deliverables for p_target_doc_id
and p_target_doc_type, definition copied from
p_source_doc_id and p_source_doc_type.
Carry forward original deliverable id. Copy attachments.
***/
PROCEDURE copy_del_for_amendment (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_source_doc_id IN NUMBER,
p_source_doc_type IN VARCHAR2,
p_target_doc_id IN NUMBER,
p_target_doc_type IN VARCHAR2,
p_target_doc_number IN VARCHAR2,
p_reset_fixed_date_yn IN VARCHAR2 ,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_copy_del_attachments_yn IN VARCHAR2 default 'Y',
p_target_contractual_doctype IN Varchar2 default null)
IS
-- used '*' in place of column list because the datastructure
-- declared as table%ROWTYPE is structured based on column positions
-- in the database. When the cursor is selected into the datastructure
-- there is a mismatch.
CURSOR del_cur IS
SELECT *
FROM OKC_DELIVERABLES
WHERE business_document_id = p_source_doc_id
AND business_document_type = p_source_doc_type
AND NVL(amendment_operation,'NONE') <> 'DELETED'
AND manage_yn = 'N'
AND recurring_del_parent_id is null;
SELECT *
FROM okc_deliverables a
WHERE business_document_id = p_source_doc_id
AND business_document_type = p_source_doc_type
AND recurring_del_parent_id = x;
delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
select okc_deliverable_id_s.nextval INTO delNewTab(q).deliverable_id from dual;
delInsTab.DELETE;
delInsTab(m).LAST_UPDATED_BY:= del_ins_rec.LAST_UPDATED_BY;
delInsTab(m).LAST_UPDATE_DATE:= del_ins_rec.LAST_UPDATE_DATE;
delInsTab(m).LAST_UPDATE_LOGIN:= del_ins_rec.LAST_UPDATE_LOGIN;
select okc_deliverable_id_s.nextval INTO delNewTab(q).deliverable_id from dual;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'118: Before insert');
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 (
delNewTab(i).DELIVERABLE_ID,
delNewTab(i).BUSINESS_DOCUMENT_TYPE ,
delNewTab(i).BUSINESS_DOCUMENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_NUMBER ,
delNewTab(i).DELIVERABLE_TYPE ,
delNewTab(i).RESPONSIBLE_PARTY ,
delNewTab(i).INTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).EXTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).DELIVERABLE_NAME ,
delNewTab(i).DESCRIPTION ,
delNewTab(i).COMMENTS ,
delNewTab(i).DISPLAY_SEQUENCE ,
delNewTab(i).FIXED_DUE_DATE_YN ,
delNewTab(i).ACTUAL_DUE_DATE ,
delNewTab(i).PRINT_DUE_DATE_MSG_NAME ,
delNewTab(i).RECURRING_YN ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_UOM ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_YN ,
delNewTab(i).NOTIFY_COMPLETED_YN ,
delNewTab(i).NOTIFY_OVERDUE_YN ,
delNewTab(i).NOTIFY_ESCALATION_YN ,
delNewTab(i).NOTIFY_ESCALATION_VALUE ,
delNewTab(i).NOTIFY_ESCALATION_UOM ,
delNewTab(i).ESCALATION_ASSIGNEE ,
delNewTab(i).AMENDMENT_OPERATION ,
delNewTab(i).PRIOR_NOTIFICATION_ID ,
delNewTab(i).AMENDMENT_NOTES ,
delNewTab(i).COMPLETED_NOTIFICATION_ID ,
delNewTab(i).OVERDUE_NOTIFICATION_ID ,
delNewTab(i).ESCALATION_NOTIFICATION_ID ,
delNewTab(i).LANGUAGE ,
delNewTab(i).ORIGINAL_DELIVERABLE_ID ,
delNewTab(i).REQUESTER_ID ,
delNewTab(i).EXTERNAL_PARTY_ID ,
delNewTab(i).EXTERNAL_PARTY_ROLE ,
delNewTab(i).RECURRING_DEL_PARENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_VERSION ,
delNewTab(i).RELATIVE_ST_DATE_DURATION ,
delNewTab(i).RELATIVE_ST_DATE_UOM ,
delNewTab(i).RELATIVE_ST_DATE_EVENT_ID ,
delNewTab(i).RELATIVE_END_DATE_DURATION ,
delNewTab(i).RELATIVE_END_DATE_UOM ,
delNewTab(i).RELATIVE_END_DATE_EVENT_ID ,
delNewTab(i).REPEATING_DAY_OF_MONTH ,
delNewTab(i).REPEATING_DAY_OF_WEEK ,
delNewTab(i).REPEATING_FREQUENCY_UOM ,
delNewTab(i).REPEATING_DURATION ,
delNewTab(i).FIXED_START_DATE ,
delNewTab(i).FIXED_END_DATE ,
delNewTab(i).MANAGE_YN ,
delNewTab(i).INTERNAL_PARTY_ID ,
delNewTab(i).DELIVERABLE_STATUS ,
delNewTab(i).STATUS_CHANGE_NOTES ,
delNewTab(i).CREATED_BY ,
delNewTab(i).CREATION_DATE ,
delNewTab(i).LAST_UPDATED_BY ,
delNewTab(i).LAST_UPDATE_DATE ,
delNewTab(i).LAST_UPDATE_LOGIN ,
delNewTab(i).OBJECT_VERSION_NUMBER ,
delNewTab(i).ATTRIBUTE_CATEGORY ,
delNewTab(i).ATTRIBUTE1 ,
delNewTab(i).ATTRIBUTE2 ,
delNewTab(i).ATTRIBUTE3 ,
delNewTab(i).ATTRIBUTE4 ,
delNewTab(i).ATTRIBUTE5 ,
delNewTab(i).ATTRIBUTE6 ,
delNewTab(i).ATTRIBUTE7 ,
delNewTab(i).ATTRIBUTE8 ,
delNewTab(i).ATTRIBUTE9 ,
delNewTab(i).ATTRIBUTE10 ,
delNewTab(i).ATTRIBUTE11 ,
delNewTab(i).ATTRIBUTE12 ,
delNewTab(i).ATTRIBUTE13 ,
delNewTab(i).ATTRIBUTE14 ,
delNewTab(i).ATTRIBUTE15 ,
delNewTab(i).DISABLE_NOTIFICATIONS_YN ,
delNewTab(i).LAST_AMENDMENT_DATE ,
delNewTab(i).BUSINESS_DOCUMENT_LINE_ID ,
delNewTab(i).EXTERNAL_PARTY_SITE_ID ,
delNewTab(i).START_EVENT_DATE ,
delNewTab(i).END_EVENT_DATE ,
delNewTab(i).SUMMARY_AMEND_OPERATION_CODE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_VALUE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_UOM,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_YN,
delNewTab(i).PAY_HOLD_OVERDUE_YN
,delNewTab(i).orig_system_reference_code
, delNewTab(i).orig_system_reference_id1
, delNewTab(i).orig_system_reference_id2,
delNewTab(i).RAISE_COMPLETION_EVENT_YN
, delNewTab(i).del_category_code
, delNewTab(i).exhibit_code
, delNewTab(i).data_item_number
, delNewTab(i).price_group
, delNewTab(i).estimated_price
, delNewTab(i).uda_template_id
, delNewTab(i).schedule_type
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'119: New deliverables inserted');
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id
);
SELECT 'X'
FROM okc_bus_doc_events_b
WHERE bus_doc_type = p_bus_doc_type
AND bus_doc_event_id = p_event_id;
select 'Y'
FROM okc_bus_doc_types_b docType,
okc_del_bus_doc_combxns bdc
WHERE docType.document_type_class = bdc.document_type_class
AND docType.document_type = p_busdoc_type
and bdc.deliverable_type = p_del_type;
select 'Y'
FROM
okc_bus_doc_types_b doctyp
,okc_del_bus_doc_combxns deltyp
where
doctyp.document_type_class = deltyp.document_type_class
AND doctyp.document_type = p_busdoc_type
AND deltyp.deliverable_type_code = p_del_type;
business document WHERE amendment_operation is NOT 'DELETE'.
(The reason of this check: In case of RFQ, amendments operation
and descriptions are maintained in the current copy,
hence all deletes are just soft deletes.
So the copy procedure should not copy deliverables which
were deleted from the RFQ during amendment).
Bug#4126344
p_carry_forward_ext_party_yn: If set to Y carry forward following attributes
from source doc in busdoc to busdoc copy
external_party_contact_id,
external_party_id,
external_party_site_id,
external_party_role
Else reset from parameters
p_carry_forward_int_contact_yn: If set to Y carry forward following attributes from source doc in busdoc to busdoc copy
internal_party_contact_id,
***/
PROCEDURE copy_deliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_source_doc_id IN NUMBER,
p_source_doc_type IN VARCHAR2,
p_target_doc_id IN NUMBER,
p_target_doc_type IN VARCHAR2,
p_target_doc_number IN VARCHAR2,
p_target_contractual_doctype IN VARCHAR2 default null,
p_target_response_doctype IN VARCHAR2 default null,
p_initialize_status_yn IN VARCHAR2 default 'Y',
p_copy_del_attachments_yn IN VARCHAR2 default 'Y',
p_internal_party_id IN NUMBER default null,
p_reset_fixed_date_yn IN VARCHAR2 default 'N',
p_internal_contact_id IN NUMBER default null,
p_external_party_id IN NUMBER default null,
p_external_party_site_id IN NUMBER default null,
p_external_contact_id IN NUMBER default null,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_carry_forward_ext_party_yn IN VARCHAR2 default 'N',
p_carry_forward_int_contact_yn IN VARCHAR2 default 'Y'
--,p_add_only_amend_deliverables IN VARCHAR2 := 'N'
,P_RETAIN_LOCK_DELIVERABLES_YN IN VARCHAR2 DEFAULT 'N'
)
IS
CURSOR del_cur IS
SELECT *
FROM OKC_DELIVERABLES s
WHERE business_document_id = p_source_doc_id
AND business_document_version = -99
AND business_document_type = p_source_doc_type
AND NVL(amendment_operation,'NONE')<> 'DELETED'
AND NVL(summary_amend_operation_code,'NONE')<> 'DELETED'
AND recurring_del_parent_id is null
/* AND (( p_add_only_amend_deliverables = 'N')
OR
( p_add_only_amend_deliverables = 'Y'
AND amendment_operation IS NOT NULL
)) */
AND ( (p_source_doc_type <> 'TEMPLATE')
OR
( p_source_doc_type = 'TEMPLATE'
AND NOT EXISTS ( SELECT 'Y'
FROM okc_deliverables t
WHERE t.original_deliverable_id = s.original_deliverable_id
AND t.business_document_type = p_target_doc_type
AND t.business_document_id = p_target_doc_id
AND t.business_document_version = -99
)
)
)
AND (
(P_RETAIN_LOCK_DELIVERABLES_YN = 'N')
OR
( P_RETAIN_LOCK_DELIVERABLES_YN = 'Y'
AND NOT EXISTS ( SELECT 'LOCKEXISTS'
FROM okc_k_entity_locks
WHERE entity_name='DELIVERABLE'
AND entity_pk1 = To_Char(s.deliverable_id)
AND lock_by_document_type=p_target_doc_type
AND lock_by_document_id=p_target_doc_id
)
)
)
;
select resp.resp_party_code
from okc_resp_parties_b resp, okc_bus_doc_types_b busdoc
where resp.document_type_class = busdoc.document_type_class
and resp.intent = busdoc.intent
and resp.internal_external_flag = 'EXTERNAL'
and busdoc.document_type = p_target_doc_type;
SELECT pf.person_id contact_id
FROM per_all_workforce_v pf,fnd_user fu
WHERE fu.user_id = fnd_global.user_id
AND pf.person_id = fu.employee_id;
delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
SELECT bus_doc_event_id INTO l_ccc_event_id FROM okc_bus_doc_events_b
WHERE bus_doc_type = 'REP_CCC';
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id
from dual;
delNewTab(i).last_updated_by:= Fnd_Global.User_Id;
delNewTab(i).last_update_date := sysdate;
delNewTab(i).last_update_login:=Fnd_Global.Login_Id;
INSERT INTO okc_deliverables VALUES delNewTab(i);*/
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 (
delNewTab(i).DELIVERABLE_ID,
delNewTab(i).BUSINESS_DOCUMENT_TYPE ,
delNewTab(i).BUSINESS_DOCUMENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_NUMBER ,
delNewTab(i).DELIVERABLE_TYPE ,
delNewTab(i).RESPONSIBLE_PARTY ,
delNewTab(i).INTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).EXTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).DELIVERABLE_NAME ,
delNewTab(i).DESCRIPTION ,
delNewTab(i).COMMENTS ,
delNewTab(i).DISPLAY_SEQUENCE ,
delNewTab(i).FIXED_DUE_DATE_YN ,
delNewTab(i).ACTUAL_DUE_DATE ,
delNewTab(i).PRINT_DUE_DATE_MSG_NAME ,
delNewTab(i).RECURRING_YN ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_UOM ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_YN ,
delNewTab(i).NOTIFY_COMPLETED_YN ,
delNewTab(i).NOTIFY_OVERDUE_YN ,
delNewTab(i).NOTIFY_ESCALATION_YN ,
delNewTab(i).NOTIFY_ESCALATION_VALUE ,
delNewTab(i).NOTIFY_ESCALATION_UOM ,
delNewTab(i).ESCALATION_ASSIGNEE ,
delNewTab(i).AMENDMENT_OPERATION ,
delNewTab(i).PRIOR_NOTIFICATION_ID ,
delNewTab(i).AMENDMENT_NOTES ,
delNewTab(i).COMPLETED_NOTIFICATION_ID ,
delNewTab(i).OVERDUE_NOTIFICATION_ID ,
delNewTab(i).ESCALATION_NOTIFICATION_ID ,
delNewTab(i).LANGUAGE ,
delNewTab(i).ORIGINAL_DELIVERABLE_ID ,
delNewTab(i).REQUESTER_ID ,
delNewTab(i).EXTERNAL_PARTY_ID ,
delNewTab(i).EXTERNAL_PARTY_ROLE ,
delNewTab(i).RECURRING_DEL_PARENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_VERSION ,
delNewTab(i).RELATIVE_ST_DATE_DURATION ,
delNewTab(i).RELATIVE_ST_DATE_UOM ,
delNewTab(i).RELATIVE_ST_DATE_EVENT_ID ,
delNewTab(i).RELATIVE_END_DATE_DURATION ,
delNewTab(i).RELATIVE_END_DATE_UOM ,
delNewTab(i).RELATIVE_END_DATE_EVENT_ID ,
delNewTab(i).REPEATING_DAY_OF_MONTH ,
delNewTab(i).REPEATING_DAY_OF_WEEK ,
delNewTab(i).REPEATING_FREQUENCY_UOM ,
delNewTab(i).REPEATING_DURATION ,
delNewTab(i).FIXED_START_DATE ,
delNewTab(i).FIXED_END_DATE ,
delNewTab(i).MANAGE_YN ,
delNewTab(i).INTERNAL_PARTY_ID ,
delNewTab(i).DELIVERABLE_STATUS ,
delNewTab(i).STATUS_CHANGE_NOTES ,
delNewTab(i).CREATED_BY ,
delNewTab(i).CREATION_DATE ,
delNewTab(i).LAST_UPDATED_BY ,
delNewTab(i).LAST_UPDATE_DATE ,
delNewTab(i).LAST_UPDATE_LOGIN ,
delNewTab(i).OBJECT_VERSION_NUMBER ,
delNewTab(i).ATTRIBUTE_CATEGORY ,
delNewTab(i).ATTRIBUTE1 ,
delNewTab(i).ATTRIBUTE2 ,
delNewTab(i).ATTRIBUTE3 ,
delNewTab(i).ATTRIBUTE4 ,
delNewTab(i).ATTRIBUTE5 ,
delNewTab(i).ATTRIBUTE6 ,
delNewTab(i).ATTRIBUTE7 ,
delNewTab(i).ATTRIBUTE8 ,
delNewTab(i).ATTRIBUTE9 ,
delNewTab(i).ATTRIBUTE10 ,
delNewTab(i).ATTRIBUTE11 ,
delNewTab(i).ATTRIBUTE12 ,
delNewTab(i).ATTRIBUTE13 ,
delNewTab(i).ATTRIBUTE14 ,
delNewTab(i).ATTRIBUTE15 ,
delNewTab(i).DISABLE_NOTIFICATIONS_YN ,
delNewTab(i).LAST_AMENDMENT_DATE ,
delNewTab(i).BUSINESS_DOCUMENT_LINE_ID ,
delNewTab(i).EXTERNAL_PARTY_SITE_ID ,
delNewTab(i).START_EVENT_DATE ,
delNewTab(i).END_EVENT_DATE ,
delNewTab(i).SUMMARY_AMEND_OPERATION_CODE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_VALUE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_UOM,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_YN,
delNewTab(i).PAY_HOLD_OVERDUE_YN,
delNewTab(i).orig_system_reference_code,
delNewTab(i).orig_system_reference_id1,
delNewTab(i).orig_system_reference_id2,
delNewTab(i).RAISE_COMPLETION_EVENT_YN,
delNewTab(i).del_category_code,
delNewTab(i).exhibit_code,
delNewTab(i).data_item_number,
delNewTab(i).price_group,
delNewTab(i).estimated_price,
delNewTab(i).uda_template_id,
delNewTab(i).schedule_type
);
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id
);
document and deletes the definition on the current document.
3. Copies status history, attachments from the instance.
4. creation_date will be reset for recurring instances of deliverables
copied from currently managed version of document. The deliverable definitions and
onetime deliverable instances will carry forwad the creation_date from the definition.
bug#3702020 added following clauses to filter deleted deliverable
AND NVL(amendment_operation,'NONE')<> 'DELETED'
AND NVL(summary_amend_operation_code,'NONE')<> 'DELETED'
**/
PROCEDURE sync_deliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_current_docid IN NUMBER,
p_current_doctype IN VARCHAR2,
p_current_doc_version IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR del_cur IS
SELECT *
FROM okc_deliverables
WHERE business_document_id = p_current_docid
AND business_document_version = p_current_doc_version
AND business_document_type = p_current_doctype
AND NVL(amendment_operation,'NONE')<> 'DELETED'
AND NVL(summary_amend_operation_code,'NONE')<> 'DELETED'
AND manage_yn = 'N'
AND Nvl(schedule_type,'SYSTEM')='SYSTEM';
SELECT *
FROM okc_deliverables a
WHERE a.business_document_id = p_current_docid
AND a.business_document_type = p_current_doctype
AND a.business_document_version <> -99
AND a.original_deliverable_id = x
AND a.manage_yn = 'Y';
SELECT *
FROM okc_del_status_history
WHERE deliverable_id = X;
select start_event_date, end_event_date
from okc_deliverables
where deliverable_id = X;
delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
delInsTab.DELETE;
delInsTab(m).LAST_UPDATED_BY:= del_ins_rec.LAST_UPDATED_BY;
delInsTab(m).LAST_UPDATE_DATE:= del_ins_rec.LAST_UPDATE_DATE;
delInsTab(m).LAST_UPDATE_LOGIN:= del_ins_rec.LAST_UPDATE_LOGIN;
select okc_deliverable_id_s.nextval
INTO delNewTab(j).deliverable_id from dual;
Update okc_deliverables set start_event_date = event_date_rec.start_event_date,
end_event_date = event_date_rec.end_event_date
where deliverable_id = delRecTab(i).deliverable_id;
delInsTab.DELETE;
delInsTab(m).LAST_UPDATED_BY:= del_ins_rec.LAST_UPDATED_BY;
delInsTab(m).LAST_UPDATE_DATE:= del_ins_rec.LAST_UPDATE_DATE;
delInsTab(m).LAST_UPDATE_LOGIN:= del_ins_rec.LAST_UPDATE_LOGIN;
select okc_deliverable_id_s.nextval
INTO delNewTab(j).deliverable_id from dual;
delete_deliverable (
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
p_del_id => delRecTab(i).deliverable_id,
x_msg_data => l_msg_data ,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Insert new version records');
delNewTab(j).last_update_date := sysdate;
delNewTab(j).last_updated_by := FND_GLOBAL.User_id;
delNewTab(j).last_update_login:= Fnd_Global.Login_Id;
delHistTab(p).LAST_UPDATED_BY := delStsHist_rec.LAST_UPDATED_BY;
delHistTab(p).LAST_UPDATE_DATE := delStsHist_rec.LAST_UPDATE_DATE;
delHistTab(p).LAST_UPDATE_LOGIN := delStsHist_rec.LAST_UPDATE_LOGIN;
delHistTab(j).last_update_date := sysdate;
delHistTab(j).last_updated_by := FND_GLOBAL.User_id;
delHistTab(j).last_update_login:= Fnd_Global.Login_Id;
INSERT INTO okc_deliverables VALUES delNewTab(j);*/
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Before insert');
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,
RAISE_COMPLETION_EVENT_YN,
orig_system_reference_code, orig_system_reference_id1, orig_system_reference_id2, del_category_code, exhibit_code, data_item_number, price_group, estimated_price,
uda_template_id ,schedule_type
)
VALUES (
delNewTab(i).DELIVERABLE_ID,
delNewTab(i).BUSINESS_DOCUMENT_TYPE ,
delNewTab(i).BUSINESS_DOCUMENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_NUMBER ,
delNewTab(i).DELIVERABLE_TYPE ,
delNewTab(i).RESPONSIBLE_PARTY ,
delNewTab(i).INTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).EXTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).DELIVERABLE_NAME ,
delNewTab(i).DESCRIPTION ,
delNewTab(i).COMMENTS ,
delNewTab(i).DISPLAY_SEQUENCE ,
delNewTab(i).FIXED_DUE_DATE_YN ,
delNewTab(i).ACTUAL_DUE_DATE ,
delNewTab(i).PRINT_DUE_DATE_MSG_NAME ,
delNewTab(i).RECURRING_YN ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_UOM ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_YN ,
delNewTab(i).NOTIFY_COMPLETED_YN ,
delNewTab(i).NOTIFY_OVERDUE_YN ,
delNewTab(i).NOTIFY_ESCALATION_YN ,
delNewTab(i).NOTIFY_ESCALATION_VALUE ,
delNewTab(i).NOTIFY_ESCALATION_UOM ,
delNewTab(i).ESCALATION_ASSIGNEE ,
delNewTab(i).AMENDMENT_OPERATION ,
delNewTab(i).PRIOR_NOTIFICATION_ID ,
delNewTab(i).AMENDMENT_NOTES ,
delNewTab(i).COMPLETED_NOTIFICATION_ID ,
delNewTab(i).OVERDUE_NOTIFICATION_ID ,
delNewTab(i).ESCALATION_NOTIFICATION_ID ,
delNewTab(i).LANGUAGE ,
delNewTab(i).ORIGINAL_DELIVERABLE_ID ,
delNewTab(i).REQUESTER_ID ,
delNewTab(i).EXTERNAL_PARTY_ID ,
delNewTab(i).EXTERNAL_PARTY_ROLE ,
delNewTab(i).RECURRING_DEL_PARENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_VERSION ,
delNewTab(i).RELATIVE_ST_DATE_DURATION ,
delNewTab(i).RELATIVE_ST_DATE_UOM ,
delNewTab(i).RELATIVE_ST_DATE_EVENT_ID ,
delNewTab(i).RELATIVE_END_DATE_DURATION ,
delNewTab(i).RELATIVE_END_DATE_UOM ,
delNewTab(i).RELATIVE_END_DATE_EVENT_ID ,
delNewTab(i).REPEATING_DAY_OF_MONTH ,
delNewTab(i).REPEATING_DAY_OF_WEEK ,
delNewTab(i).REPEATING_FREQUENCY_UOM ,
delNewTab(i).REPEATING_DURATION ,
delNewTab(i).FIXED_START_DATE ,
delNewTab(i).FIXED_END_DATE ,
delNewTab(i).MANAGE_YN ,
delNewTab(i).INTERNAL_PARTY_ID ,
delNewTab(i).DELIVERABLE_STATUS ,
delNewTab(i).STATUS_CHANGE_NOTES ,
delNewTab(i).CREATED_BY ,
delNewTab(i).CREATION_DATE ,
delNewTab(i).LAST_UPDATED_BY ,
delNewTab(i).LAST_UPDATE_DATE ,
delNewTab(i).LAST_UPDATE_LOGIN ,
delNewTab(i).OBJECT_VERSION_NUMBER ,
delNewTab(i).ATTRIBUTE_CATEGORY ,
delNewTab(i).ATTRIBUTE1 ,
delNewTab(i).ATTRIBUTE2 ,
delNewTab(i).ATTRIBUTE3 ,
delNewTab(i).ATTRIBUTE4 ,
delNewTab(i).ATTRIBUTE5 ,
delNewTab(i).ATTRIBUTE6 ,
delNewTab(i).ATTRIBUTE7 ,
delNewTab(i).ATTRIBUTE8 ,
delNewTab(i).ATTRIBUTE9 ,
delNewTab(i).ATTRIBUTE10 ,
delNewTab(i).ATTRIBUTE11 ,
delNewTab(i).ATTRIBUTE12 ,
delNewTab(i).ATTRIBUTE13 ,
delNewTab(i).ATTRIBUTE14 ,
delNewTab(i).ATTRIBUTE15 ,
delNewTab(i).DISABLE_NOTIFICATIONS_YN ,
delNewTab(i).LAST_AMENDMENT_DATE ,
delNewTab(i).BUSINESS_DOCUMENT_LINE_ID ,
delNewTab(i).EXTERNAL_PARTY_SITE_ID ,
delNewTab(i).START_EVENT_DATE ,
delNewTab(i).END_EVENT_DATE ,
delNewTab(i).SUMMARY_AMEND_OPERATION_CODE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_VALUE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_UOM,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_YN,
delNewTab(i).PAY_HOLD_OVERDUE_YN,
delNewTab(i).RAISE_COMPLETION_EVENT_YN,
delNewTab(i).orig_system_reference_code,
delNewTab(i).orig_system_reference_id1,
delNewTab(i).orig_system_reference_id2,
delNewTab(i).del_category_code,
delNewTab(i).exhibit_code,
delNewTab(i).data_item_number,
delNewTab(i).price_group,
delNewTab(i).estimated_price,
delNewTab(i).uda_template_id,
delNewTab(i).schedule_type
);
delHistTab(p).LAST_UPDATED_BY := FND_GLOBAL.User_id;
delHistTab(p).LAST_UPDATE_DATE := sysdate;
delHistTab(p).LAST_UPDATE_LOGIN := Fnd_Global.Login_Id;*/
INSERT INTO okc_del_status_history (
deliverable_id,
deliverable_status,
status_changed_by,
status_change_date,
status_change_notes,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
delIdTab(j).del_id,
delStsHist_rec.DELIVERABLE_STATUS,
delStsHist_rec.STATUS_CHANGED_BY,
delStsHist_rec.STATUS_CHANGE_DATE,
delStsHist_rec.STATUS_CHANGE_NOTES,
delStsHist_rec.OBJECT_VERSION_NUMBER,
FND_GLOBAL.User_id,
sysdate,
FND_GLOBAL.User_id,
sysdate,
Fnd_Global.Login_Id);
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id
);
i.amendment operation is DELETE and summary amendment operation
is not null
a.Recurring del then copy both def and instances
b.One time del then copy only definition
ii.amendment operation is UPDATE or ADDED
a.Recurring del then copy both def and instances
b.One time del then copy only definition
4.Copy attachments
***/
PROCEDURE version_deliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_doc_id IN NUMBER,
p_doc_version IN NUMBER,
p_doc_type IN VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR del_cur IS
SELECT *
FROM okc_deliverables
WHERE business_document_id = p_doc_id
AND business_document_version = -99
AND business_document_type = p_doc_type
AND manage_yn = 'N';
SELECT count(deliverable_id) INTO delCount
FROM OKC_DELIVERABLES
WHERE business_document_id = p_doc_id
AND business_document_type = p_doc_type
AND business_document_version = p_doc_version;
delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
IF delRecTab(i).amendment_operation = 'DELETED' AND
delRecTab(i).summary_amend_operation_code is null THEN
-- Since the summary_amend_operation_code is null, the deliverable is not
-- existing in the signed contract, it was added and deleted in the intermediate
-- versions. So hard delete the record.
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
,'100: Amendment_operation_summary_code is null hard delete this deliverable'||
to_char(delRecTab(i).deliverable_id));
delete from okc_deliverables where deliverable_id = delRecTab(i).deliverable_id;*/
select okc_deliverable_id_s.nextval
INTO delNewTab(j).deliverable_id from dual;
BULK INSERT into okc_deliverables the new version of deliverables.
***/
IF delNewTab.COUNT <> 0 THEN
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Insert new version records');
delNewTab(j).last_update_date := sysdate;
delNewTab(j).last_updated_by := FND_GLOBAL.User_id;
delNewTab(j).last_update_login:= Fnd_Global.Login_Id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Before insert');
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 (
delNewTab(i).DELIVERABLE_ID,
delNewTab(i).BUSINESS_DOCUMENT_TYPE ,
delNewTab(i).BUSINESS_DOCUMENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_NUMBER ,
delNewTab(i).DELIVERABLE_TYPE ,
delNewTab(i).RESPONSIBLE_PARTY ,
delNewTab(i).INTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).EXTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).DELIVERABLE_NAME ,
delNewTab(i).DESCRIPTION ,
delNewTab(i).COMMENTS ,
delNewTab(i).DISPLAY_SEQUENCE ,
delNewTab(i).FIXED_DUE_DATE_YN ,
delNewTab(i).ACTUAL_DUE_DATE ,
delNewTab(i).PRINT_DUE_DATE_MSG_NAME ,
delNewTab(i).RECURRING_YN ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_UOM ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_YN ,
delNewTab(i).NOTIFY_COMPLETED_YN ,
delNewTab(i).NOTIFY_OVERDUE_YN ,
delNewTab(i).NOTIFY_ESCALATION_YN ,
delNewTab(i).NOTIFY_ESCALATION_VALUE ,
delNewTab(i).NOTIFY_ESCALATION_UOM ,
delNewTab(i).ESCALATION_ASSIGNEE ,
delNewTab(i).AMENDMENT_OPERATION ,
delNewTab(i).PRIOR_NOTIFICATION_ID ,
delNewTab(i).AMENDMENT_NOTES ,
delNewTab(i).COMPLETED_NOTIFICATION_ID ,
delNewTab(i).OVERDUE_NOTIFICATION_ID ,
delNewTab(i).ESCALATION_NOTIFICATION_ID ,
delNewTab(i).LANGUAGE ,
delNewTab(i).ORIGINAL_DELIVERABLE_ID ,
delNewTab(i).REQUESTER_ID ,
delNewTab(i).EXTERNAL_PARTY_ID ,
delNewTab(i).EXTERNAL_PARTY_ROLE ,
delNewTab(i).RECURRING_DEL_PARENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_VERSION ,
delNewTab(i).RELATIVE_ST_DATE_DURATION ,
delNewTab(i).RELATIVE_ST_DATE_UOM ,
delNewTab(i).RELATIVE_ST_DATE_EVENT_ID ,
delNewTab(i).RELATIVE_END_DATE_DURATION ,
delNewTab(i).RELATIVE_END_DATE_UOM ,
delNewTab(i).RELATIVE_END_DATE_EVENT_ID ,
delNewTab(i).REPEATING_DAY_OF_MONTH ,
delNewTab(i).REPEATING_DAY_OF_WEEK ,
delNewTab(i).REPEATING_FREQUENCY_UOM ,
delNewTab(i).REPEATING_DURATION ,
delNewTab(i).FIXED_START_DATE ,
delNewTab(i).FIXED_END_DATE ,
delNewTab(i).MANAGE_YN ,
delNewTab(i).INTERNAL_PARTY_ID ,
delNewTab(i).DELIVERABLE_STATUS ,
delNewTab(i).STATUS_CHANGE_NOTES ,
delNewTab(i).CREATED_BY ,
delNewTab(i).CREATION_DATE ,
delNewTab(i).LAST_UPDATED_BY ,
delNewTab(i).LAST_UPDATE_DATE ,
delNewTab(i).LAST_UPDATE_LOGIN ,
delNewTab(i).OBJECT_VERSION_NUMBER ,
delNewTab(i).ATTRIBUTE_CATEGORY ,
delNewTab(i).ATTRIBUTE1 ,
delNewTab(i).ATTRIBUTE2 ,
delNewTab(i).ATTRIBUTE3 ,
delNewTab(i).ATTRIBUTE4 ,
delNewTab(i).ATTRIBUTE5 ,
delNewTab(i).ATTRIBUTE6 ,
delNewTab(i).ATTRIBUTE7 ,
delNewTab(i).ATTRIBUTE8 ,
delNewTab(i).ATTRIBUTE9 ,
delNewTab(i).ATTRIBUTE10 ,
delNewTab(i).ATTRIBUTE11 ,
delNewTab(i).ATTRIBUTE12 ,
delNewTab(i).ATTRIBUTE13 ,
delNewTab(i).ATTRIBUTE14 ,
delNewTab(i).ATTRIBUTE15 ,
delNewTab(i).DISABLE_NOTIFICATIONS_YN ,
delNewTab(i).LAST_AMENDMENT_DATE ,
delNewTab(i).BUSINESS_DOCUMENT_LINE_ID ,
delNewTab(i).EXTERNAL_PARTY_SITE_ID ,
delNewTab(i).START_EVENT_DATE ,
delNewTab(i).END_EVENT_DATE ,
delNewTab(i).SUMMARY_AMEND_OPERATION_CODE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_VALUE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_UOM,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_YN,
delNewTab(i).PAY_HOLD_OVERDUE_YN
,delNewTab(i).orig_system_reference_code
,delNewTab(i).orig_system_reference_id1
,delNewTab(i).orig_system_reference_id2
,delNewTab(i).RAISE_COMPLETION_EVENT_YN
,delNewTab(i).del_category_code
, delNewTab(i).exhibit_code
, delNewTab(i).data_item_number
, delNewTab(i).price_group
, delNewTab(i).estimated_price
, delNewTab(i).uda_template_id
, delNewTab(i).schedule_type
);
INSERT INTO okc_deliverables VALUES delNewTab(j);*/
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id
);
UPDATE okc_deliverables SET amendment_operation = null,
--amendment_notes = null,
--last_amendment_date = null,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE deliverable_id = del_rec.deliverable_id;
SELECT deliverable_id
,amendment_operation
,summary_amend_operation_code
FROM okc_deliverables
WHERE business_document_id = p_doc_id
AND business_document_version = -99
AND business_document_type = p_doc_type
AND manage_yn = 'N';
IF def_rec.amendment_operation = 'DELETED' OR
def_rec.summary_amend_operation_code = 'DELETED' THEN
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,
'100: deleted deliverable: '||to_char(def_rec.deliverable_id));
delete from okc_deliverables
where deliverable_id = def_rec.deliverable_id;
DELETE FROM OKC_DELIVERABLES_EXT_B
WHERE deliverable_id = def_rec.deliverable_id;
DELETE FROM OKC_DELIVERABLES_EXT_TL
WHERE deliverable_id = def_rec.deliverable_id;
UPDATE okc_deliverables SET amendment_operation = null,
summary_amend_operation_code = null,
amendment_notes = null,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE deliverable_id = def_rec.deliverable_id;
,'100: updated deliverable: '||to_char(def_rec.deliverable_id));
IF def_rec.amendment_operation = 'DELETED' AND
def_rec.summary_amend_operation_code is null THEN
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,
'100: deleted deliverable: '||to_char(def_rec.deliverable_id));
delete from okc_deliverables
where deliverable_id = def_rec.deliverable_id;
DELETE FROM OKC_DELIVERABLES_EXT_B
WHERE deliverable_id = def_rec.deliverable_id;
DELETE FROM OKC_DELIVERABLES_EXT_TL
WHERE deliverable_id = def_rec.deliverable_id;
UPDATE okc_deliverables SET amendment_operation = null,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE deliverable_id = def_rec.deliverable_id;
,'100: updated deliverable: '||to_char(def_rec.deliverable_id));
SELECT deliverable_id
FROM okc_deliverables
WHERE business_document_id = p_doc_id
AND business_document_version =p_doc_version
AND business_document_type = p_doc_type
AND manage_yn = 'Y';
UPDATE okc_deliverables SET manage_yn = 'N',
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE deliverable_id = delIdTab(i);
INSERT INTO okc_del_status_history (
deliverable_id,
deliverable_status,
status_changed_by,
status_change_date,
status_change_notes,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
p_del_id,
p_deliverable_status,
null,
sysdate,
null,
1,
Fnd_Global.User_Id,
sysdate,
Fnd_Global.User_Id,
sysdate,
Fnd_Global.Login_Id);
SELECT deliverable_id
FROM okc_deliverables
WHERE business_document_id = p_doc_id
AND business_document_version =p_doc_version
AND business_document_type = p_doc_type
AND deliverable_status = p_current_status
AND actual_due_date is not null;
SELECT deliverable_id
FROM okc_deliverables
WHERE business_document_id = p_doc_id
AND business_document_version =p_doc_version
AND business_document_type = p_doc_type
AND NVL(relative_st_date_event_id,0) NOT IN (
select bus_doc_event_id
from okc_bus_doc_events_v
where business_event_code = p_cancel_event_code);
delStsTab(j).last_updated_by:= Fnd_Global.User_Id;
delStsTab(j).last_update_date := sysdate;
delStsTab(j).last_update_login := Fnd_Global.Login_Id;
UPDATE okc_deliverables
SET
deliverable_status = p_new_status,
manage_yn = p_manage_yn,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login = Fnd_Global.Login_Id
WHERE deliverable_id = delIdTab(i);
INSERT INTO okc_del_status_history VALUES delStsTab(i); */
INSERT INTO okc_del_status_history
(deliverable_id,
deliverable_status,
STATUS_CHANGED_BY,
status_change_date,
status_change_notes,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (delStsTab(i).DELIVERABLE_ID
,delStsTab(i).DELIVERABLE_STATUS
,delStsTab(i).STATUS_CHANGED_BY
,delStsTab(i).STATUS_CHANGE_DATE
,delStsTab(i).STATUS_CHANGE_NOTES
,delStsTab(i).OBJECT_VERSION_NUMBER
,delStsTab(i).CREATED_BY
,delStsTab(i).CREATION_DATE
,delStsTab(i).LAST_UPDATED_BY
,delStsTab(i).LAST_UPDATE_DATE
,delStsTab(i).LAST_UPDATE_LOGIN
);
/** Updated this procedure - 01/20/2004 by SASETHI
Commendted out code for changing deliverable status to CANCELLED when canceled
operation is called from Mng Deliverables GRP.
**/
-- disable deliverables that are currently being managed.
-- before activating deliverables on new version
disable_deliverables (
p_api_version => l_api_version ,
p_init_msg_list => FND_API.G_FALSE ,
p_doc_id => p_doc_id ,
p_doc_version => p_doc_version ,
p_doc_type => p_doc_type ,
x_msg_data => l_msg_data ,
x_msg_count => l_msg_count ,
x_return_status => l_return_status );
delStsTab(j).last_updated_by:= Fnd_Global.User_Id;
delStsTab(j).last_update_date := sysdate;
delStsTab(j).last_update_login := Fnd_Global.Login_Id;
UPDATE okc_deliverables
SET
deliverable_status = p_new_status,
manage_yn = 'N',
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login = Fnd_Global.Login_Id
WHERE deliverable_id = delIdTab(i);
INSERT INTO okc_del_status_history
(deliverable_id,
deliverable_status,
STATUS_CHANGED_BY,
status_change_date,
status_change_notes,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (delStsTab(i).DELIVERABLE_ID
,delStsTab(i).DELIVERABLE_STATUS
,delStsTab(i).STATUS_CHANGED_BY
,delStsTab(i).STATUS_CHANGE_DATE
,delStsTab(i).STATUS_CHANGE_NOTES
,delStsTab(i).OBJECT_VERSION_NUMBER
,delStsTab(i).CREATED_BY
,delStsTab(i).CREATION_DATE
,delStsTab(i).LAST_UPDATED_BY
,delStsTab(i).LAST_UPDATE_DATE
,delStsTab(i).LAST_UPDATE_LOGIN
);
Update deliverables with overdue_notification_id
***/
PROCEDURE overdue_del_notifier(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) :='overdue_del_notifier';
SELECT deliverable_id,
deliverable_name,
deliverable_type,
business_document_id,
business_document_version,
business_document_type,
business_document_number,
responsible_party,
external_party_contact_id,
internal_party_contact_id,
requester_id
FROM okc_deliverables
WHERE manage_yn = 'Y'
AND disable_notifications_yn = 'N'
AND notify_overdue_yn = 'Y'
AND overdue_notification_id is null
AND business_document_type <> 'TEMPLATE'
AND deliverable_status IN ('OPEN','REJECTED')
AND actual_due_date < trunc(sysdate); -- bug#3617906 removed trunc on actual due date
selected_dels del_cur_tbl;
FETCH del_cur BULK COLLECT INTO selected_dels
LIMIT l_batch_size;
EXIT WHEN selected_dels.COUNT = 0;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: selected_dels.COUNT is :'||to_char(selected_dels.COUNT));
FOR i IN selected_dels.FIRST..NVL(selected_dels.LAST, -1) LOOP
-- log messages in concurrent program log file
FND_FILE.PUT_LINE(FND_FILE.LOG,'Business Document: '||
selected_dels(i).business_document_type||'-'||selected_dels(i).business_document_number);
to_char(selected_dels(i).deliverable_id));
l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_OVERDUE_NTF_SUBJECT',selected_dels(i).business_document_type);
p_deliverable_id => selected_dels(i).deliverable_id,
p_deliverable_name => selected_dels(i).deliverable_name,
p_deliverable_type => selected_dels(i).deliverable_type,
p_business_document_id => selected_dels(i).business_document_id,
p_business_document_version => selected_dels(i).business_document_version,
p_business_document_type => selected_dels(i).business_document_type,
p_business_document_number => selected_dels(i).business_document_number,
p_resp_party => selected_dels(i).responsible_party,
p_external_contact => selected_dels(i).external_party_contact_id,
p_internal_contact => selected_dels(i).internal_party_contact_id,
p_requester_id => selected_dels(i).requester_id,
--p_msg_code => 'OKC_DEL_OVERDUE_NTF_SUBJECT',
p_msg_code => l_resolved_msg_name,
x_notification_id => l_notification_id,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
deliverable_ids(j) := selected_dels(i).deliverable_id;
UPDATE okc_deliverables
SET overdue_notification_id = overdue_ntf_ids(i),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.User_id,
last_update_login =Fnd_Global.Login_Id
WHERE deliverable_id = deliverable_ids(i);
Update deliverables with prior_notification_id
***/
PROCEDURE beforedue_del_notifier (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR del_cur IS
SELECT *
FROM okc_deliverables
WHERE manage_yn = 'Y'
AND disable_notifications_yn = 'N'
AND notify_prior_due_date_yn = 'Y'
AND prior_notification_id is null
AND business_document_type <> 'TEMPLATE'
AND deliverable_status IN ('OPEN','REJECTED')
AND trunc(actual_due_date) > trunc(sysdate);
delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
select add_months(delRecTab(i).actual_due_date,-delRecTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE)
INTO l_notification_date from dual;
delNtfTab(j).last_update_date := sysdate;
delNtfTab(j).last_updated_by := FND_GLOBAL.User_id;
delNtfTab(j).last_update_login:=Fnd_Global.Login_Id;
UPDATE okc_deliverables SET ROW = delNtfTab(i)
where deliverable_id = l_deliverable_id;
UPDATE okc_deliverables
SET prior_notification_id = delNtfTab(i).prior_notification_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.User_id,
last_update_login =Fnd_Global.Login_Id
where deliverable_id = delNtfTab(i).deliverable_id;
Update deliverables with escalation_notification_id
***/
PROCEDURE esc_del_notifier (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) :='esc_del_notifier';
SELECT *
FROM okc_deliverables
WHERE manage_yn = 'Y'
AND disable_notifications_yn = 'N'
AND notify_escalation_yn = 'Y'
AND escalation_assignee is not null
AND escalation_notification_id is null
AND business_document_type <> 'TEMPLATE'
AND deliverable_status IN ('OPEN','REJECTED')
AND actual_due_date < trunc(sysdate);
delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
select add_months(delRecTab(i).actual_due_date,delRecTab(i).NOTIFY_ESCALATION_VALUE)
INTO l_notification_date from dual;
delNtfTab(j).last_update_date := sysdate;
delNtfTab(j).last_updated_by := FND_GLOBAL.User_id;
delNtfTab(j).last_update_login:=Fnd_Global.Login_Id;
UPDATE okc_deliverables SET ROW = delNtfTab(i)
where deliverable_id = l_deliverable_id;
UPDATE okc_deliverables
SET escalation_notification_id = delNtfTab(i).escalation_notification_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.User_id,
last_update_login =Fnd_Global.Login_Id
where deliverable_id = delNtfTab(i).deliverable_id;
PROCEDURE delete_attachments (
p_entity_name IN VARCHAR2
,p_pk1_value IN VARCHAR2
,x_result OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) :='delete_attachments';
SELECT att.attached_document_id
,doc.datatype_id
FROM fnd_attached_documents att
,fnd_documents doc
WHERE att.document_id = doc.document_id
AND att.entity_name = p_entity_name
AND att.pk1_value = p_pk1_value;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside OKC_DELIVERABLE_PROCESS_PVT.delete_attachments');
fnd_attached_documents3_pkg.delete_row (
X_attached_document_id => att_rec.attached_document_id,
X_datatype_id => att_rec.datatype_id,
delete_document_flag => 'Y' );
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_attachments');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_attachments in OTHERS');
END delete_attachments;
1. This API performs bulk delete of deliverables for business documents.
Invoked by OKC_TERMS_UTIL_GRP.purge_documents
2. For each doc_type and doc_id in p_doc_table,
find the deliverables that belong to the business document,
delete the deliverable, status history and attachments.
***/
PROCEDURE purge_doc_deliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_doc_table IN OKC_TERMS_UTIL_GRP.doc_tbl_type,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) :='purge_doc_deliverables';
delete_deliverables (
p_api_version => l_api_version,
p_init_msg_list => OKC_API.G_FALSE,
p_doc_id => p_doc_table(i).doc_id,
p_doc_type => p_doc_table(i).doc_type,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
2. This API will select all deliverables for a given
business document type and version
3. Delete all deliverables along with the attachments and status history
***/
PROCEDURE delete_deliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_doc_id IN NUMBER,
p_doc_type IN VARCHAR2,
p_doc_version IN NUMBER DEFAULT NULL,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
,p_retain_lock_deliverables_yn IN VARCHAR2
,p_retain_exhibits IN VARCHAR2 := 'N')
IS
CURSOR del_cur IS
SELECT deliverable_id
FROM okc_deliverables
WHERE business_document_id = p_doc_id
AND business_document_type = p_doc_type
AND ( p_retain_lock_deliverables_yn = 'N'
OR
(p_retain_lock_deliverables_yn = 'Y'
AND amendment_operation IS NULL)
);
SELECT deliverable_id
FROM okc_deliverables
WHERE business_document_id = p_doc_id
AND business_document_type = p_doc_type
AND business_document_version = p_doc_version
AND ( p_retain_lock_deliverables_yn = 'N'
OR
(p_retain_lock_deliverables_yn = 'Y'
AND amendment_operation IS NULL)
);
l_api_name VARCHAR2(30) :='delete_deliverables';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside OKC_DELIVERABLE_PROCESS_PVT.delete_deliverables');
OKC_CDRL_PVT.delete_doc_exhibits (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_doc_class => NULL, -- Value will be Derived in the API
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_doc_version => p_doc_version,
p_mode => NULL,
p_retain_lock_deliverables_yn => p_retain_lock_deliverables_yn,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status
) ;
DELETE FROM okc_del_status_history
WHERE deliverable_id = delIdTab(i);
delete_attachments (
p_entity_name => G_ENTITY_NAME
,p_pk1_value => delIdTab(i)
,x_result => l_result);
DELETE FROM okc_deliverables
WHERE deliverable_id = delIdTab(i);
DELETE FROM OKC_DELIVERABLES_EXT_B
WHERE deliverable_id = delIdTab(i);
DELETE FROM OKC_DELIVERABLES_EXT_TL
WHERE deliverable_id = delIdTab(i);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_deliverables');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_deliverables with G_EXC_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_deliverables with G_EXC_UNEXPECTED_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_deliverables with G_EXC_UNEXPECTED_ERROR');
END delete_deliverables;
2. This API will select all deliverables for a given source template id
3. Update all deliverables on the target template Id
set the business_document_id = source template id
***/
PROCEDURE update_del_for_template_merge (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_base_template_id IN NUMBER,
p_working_template_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR del_cur IS
SELECT deliverable_id
FROM okc_deliverables
WHERE business_document_id = p_working_template_id
AND business_document_type = 'TEMPLATE';
l_api_name CONSTANT VARCHAR2(30) :='update_del_for_template_merge';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside OKC_DELIVERABLE_PROCESS_PVT.update_del_for_template_merge');
UPDATE okc_deliverables
SET business_document_id = p_base_template_id,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE deliverable_id = delIdTab(j);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.update_del_for_template_merge');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.update_del_for_template_merge with G_EXC_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.update_del_for_template_merge with G_EXC_UNEXPECTED_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.update_del_for_template_merge with G_EXC_UNEXPECTED_ERROR');
END update_del_for_template_merge;
SELECT NVL(MAX(del.last_amendment_date),MAX(del.creation_date))
FROM
okc_deliverables del
,okc_deliverable_types_b delType
WHERE del.business_document_id = p_busdoc_id
AND del.business_document_type = p_busdoc_type
AND del.recurring_del_parent_id is null
--AND manage_yn = 'N' commented to reproduce the bug#3667445
--AND business_document_version = -99 commented for bug#3641366
--AND deliverable_type not like '%INTERNAL%'; --Commented as part of changes for new table okc_deliverable_types_b
SELECT MAX(last_amendment_date)
FROM okc_deliverables
WHERE business_document_id = p_busdoc_id
AND business_document_type = p_busdoc_type
AND business_document_version = -99
AND deliverable_type not like '%INTERNAL%';*/
select employee_id from fnd_user
where employee_id = p_contact_id;
select email_address
from per_all_people_f
where person_id = p_contact_id
--and trunc(sysdate) < nvl(effective_end_date, trunc(sysdate + 1));
select 'X'
from per_all_people_f
where person_id = p_contact_id
and trunc(sysdate) < nvl(effective_end_date, trunc(sysdate + 1));
select 'X'
from
po_supplier_users_v
where user_party_id = p_contact_id
and po_vendor_id = p_party_id
and 'SUPPLIER_ORG' = p_party_role
UNION
SELECT 'X'
FROM hz_parties contact,
hz_relationships hr
WHERE hr.subject_id = contact.party_id
AND 'SUPPLIER_ORG' <> p_party_role
And hr.object_id = p_party_id
And contact.party_id = p_contact_id
AND hr.relationship_type = 'CONTACT'
AND hr.relationship_code = 'CONTACT_OF'
and hr.subject_type = 'PERSON'
and hr.object_type = 'ORGANIZATION'
and hr.subject_table_name ='HZ_PARTIES'
and hr.object_table_name ='HZ_PARTIES'
AND hr.status = 'A'
AND hr.start_date <= sysdate
AND nvl(hr.end_date, sysdate + 1) > sysdate;
select meaning into l_meaning
from fnd_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
EXECUTE IMMEDIATE 'SELECT enable_cdrl FROM po_system_parameters_all WHERE org_id=fnd_global.org_id ' INTO l_cdrl_enabled;
PROCEDURE update_error_table
(px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
p_qa_severity IN VARCHAR2,
p_qa_code IN VARCHAR2,
p_error_msg_name IN VARCHAR2,
p_del_variable_name IN VARCHAR2,
p_article_name IN VARCHAR2,
p_article_id IN NUMBER,
p_section_name IN VARCHAR2,
p_suggestion_msg_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_msg_txt VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(30) := 'update_error_table';
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'5002: Leaving update_error_table with unexpected error:'||SQLERRM);
End update_error_table;
select
art.document_id doc_id
,art.document_type doc_type
,av.variable_code variable_code
,art.sav_sae_id article_id
,art.article_version_id version_id
,art.scn_id section_id
,sec.label||' '||sec.heading section_name
,variables.variable_name variable_name
from
okc_k_art_variables av
,okc_k_articles_b art
,okc_sections_b sec
,okc_bus_variables_v variables
where
art.document_id = p_bus_doc_id and
art.document_type = p_bus_doc_type and
av.variable_type = 'D' and
av.cat_id = art.id and --ArtVariables to Articles
sec.id = art.scn_id and --Sections to Articles
variables.variable_code = av.variable_code and --Variables to ArtVariables
nvl (art.amendment_operation_code,'?') <> 'DELETED' and --fix for bug 3710697
nvl(art.summary_amend_operation_code,'?')<> 'DELETED'
order by av.variable_code, art.sav_sae_id;
select 'x'
from
okc_deliverables del
where
del.business_document_type = p_bus_doc_type
and del.business_document_id = p_bus_doc_id
and del.deliverable_type = p_del_type
and del.responsible_party = p_internal_org;
select 'x'
from
okc_deliverables del
where
del.business_document_type = p_bus_doc_type
and del.business_document_id = p_bus_doc_id
and del.deliverable_type = p_del_type
and del.responsible_party <> p_internal_org;
select 'x'
from
okc_deliverables del
where
del.business_document_type = p_bus_doc_type
and del.business_document_id = p_bus_doc_id
and del.deliverable_type = p_del_type;
select target_response_doc_type
from okc_bus_doc_types_b
where document_type = p_bus_doc_type;
update_error_table
(px_qa_result_tbl => l_qa_result_tbl,
p_qa_severity => p_severity,
p_qa_code => l_qa_code,
p_error_msg_name => l_resolved_msg_name,
p_del_variable_name => get_variables_REC.variable_name, -- get_variables_REC.del_variable_name,
p_article_id => get_variables_REC.article_id,
p_article_name => okc_terms_util_pvt.get_Article_Name( get_variables_REC.article_id ,get_variables_REC.version_id), -- get_variables_REC.article_name,
p_section_name => get_variables_REC.section_name,
p_suggestion_msg_name => l_resolved_msg_name2,
x_return_status => l_return_status );
select qa_code, severity_flag,enable_qa_yn
from okc_doc_qa_lists
where document_type = p_doc_type
and qa_code = p_qa_code;
select enabled_flag
from fnd_lookups
where lookup_type = l_lookup_type --bug 3814702, use variable for lookup_type
and lookup_code = p_lookup_code;
select *
from okc_deliverables
where business_document_type = p_doc_type
and business_document_id = p_doc_id
and business_document_version = -99
and deliverable_type in (select deltypes.deliverable_type_code from okc_bus_doc_types_b doctypes,
--okc_del_bus_doc_combxns deltypes
okc_deliverable_types_b deltypes
where doctypes.document_type=p_doc_type
and doctypes.document_type_class = deltypes.document_type_class)
and amendment_operation is not null;
select *
from okc_deliverables
where business_document_type = p_doc_type
and business_document_id = p_doc_id
and business_document_version = -99
and (amendment_operation is NULL OR amendment_operation <> 'DELETED')
and (summary_amend_operation_code is NULL OR summary_amend_operation_code <> 'DELETED')
and deliverable_type in (select deltypes.deliverable_type_code
from okc_bus_doc_types_b doctypes,
okc_del_bus_doc_combxns deltypes
where doctypes.document_type=p_doc_type
and doctypes.document_type_class = deltypes.document_type_class);
select *
from okc_deliverables
where business_document_type = p_doc_type
and business_document_id = p_doc_id
and business_document_version = -99
and (amendment_operation is NULL OR amendment_operation <> 'DELETED')
and (summary_amend_operation_code is NULL OR summary_amend_operation_code <> 'DELETED')
AND del_category_code='CDRL';
PROCEDURE delete_del_status_hist_attach(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_deliverable_id IN NUMBER,
p_bus_doc_id IN NUMBER,
p_bus_doc_version IN NUMBER,
p_bus_doc_type IN VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_version CONSTANT NUMBER := 1;
DELETE FROM okc_del_status_history
WHERE deliverable_id = p_deliverable_id;
delete_attachments (
p_entity_name => G_ENTITY_NAME
,p_pk1_value => p_deliverable_id
,x_result => l_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_status_hist_attach');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_status_hist_attach with G_EXC_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_status_hist_attach with G_EXC_UNEXPECTED_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_status_hist_attach with OTHERS EXCEPTION');
END delete_del_status_hist_attach;
SELECT deliverable_status
FROM okc_del_status_history
where deliverable_id = delid;
/*** This API deletes a given set of deliverables, attachments
and status change history for a busdoc. Used to delete only the instances
leaving the definition as it is. Called from update_deliverables group API ***/
PROCEDURE delete_del_instances(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_doc_id IN NUMBER,
p_doc_type IN VARCHAR2,
p_doc_version IN NUMBER DEFAULT NULL,
p_Conditional_Delete_Flag IN VARCHAR2 DEFAULT 'N',
p_delid_tab IN OKC_DELIVERABLE_PROCESS_PVT.delIdTabType,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_del_id NUMBER;
l_api_name CONSTANT VARCHAR2(30) :='delete_del_instances';
l_deleteInstances VARCHAR2(1) := 'Y';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances');
IF (p_Conditional_Delete_Flag = 'Y') then
If (delStatusUnchanged(p_Del_id => l_del_id) = 'Y') then
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: delStatusUnchanged is Y');
l_deleteInstances := 'Y';
l_deleteInstances := 'N';
l_deleteInstances := 'Y';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: l_deleteInstances:'||l_deleteInstances);
IF (l_deleteInstances = 'Y') then
-- delete deliverables status history
DELETE FROM okc_del_status_history
WHERE deliverable_id = l_del_id;
delete_attachments (
p_entity_name => G_ENTITY_NAME
,p_pk1_value => l_del_id
,x_result => l_result);
DELETE FROM okc_deliverables
WHERE deliverable_id = l_del_id;
DELETE FROM OKC_DELIVERABLES_EXT_B
WHERE deliverable_id = l_del_id;
DELETE FROM OKC_DELIVERABLES_EXT_TL
WHERE deliverable_id = l_del_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'102:Leaving delete_del_instances with Exception');
END delete_del_instances;
2. Select all deliverables for the Business Document. If deliverables exist then
a. Check each deliverable type
i. If only contractual deliverables exist then return CONTRACTUAL
ii. If only internal deliverables exist then return INTERNAL
iii.If both contractual and internal deliverables exist then return
CONTRACTUAL_AND_INTERNAL
3. If no deliverables exist then return NONE**/
/*** added new signature bug#3192512**/
FUNCTION deliverables_exist(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_doctype IN VARCHAR2,
p_docid IN NUMBER
) RETURN VARCHAR2
IS
-- updated cursor for bug#4069955
CURSOR del_cur IS
SELECT
del.deliverable_type
,delType.internal_flag
FROM
okc_deliverables del
,okc_deliverable_types_b delType
WHERE del.business_document_id = p_docid
AND del.business_document_type = p_doctype
AND del.business_document_version = -99
AND NVL(del.amendment_operation,'NONE') <> 'DELETED'
AND NVL(del.summary_amend_operation_code,'NONE') <> 'DELETED'
AND delType.deliverable_type_code = del.deliverable_type;
PROCEDURE delete_deliverable (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_del_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_result VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) :='delete_deliverable';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside OKC_DELIVERABLE_PROCESS_PVT.delete_deliverable'||to_char(p_del_id));
DELETE FROM okc_del_status_history
WHERE deliverable_id = p_del_id;
delete_attachments (
p_entity_name => G_ENTITY_NAME
,p_pk1_value => p_del_id
,x_result => l_result);
DELETE FROM okc_deliverables
WHERE deliverable_id = p_del_id;
DELETE FROM OKC_DELIVERABLES_EXT_B
WHERE deliverable_id = p_del_id;
DELETE FROM OKC_DELIVERABLES_EXT_TL
WHERE deliverable_id = p_del_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_deliverable');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_deliverable with G_EXC_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_deliverable with G_EXC_UNEXPECTED_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_deliverable with G_EXC_UNEXPECTED_ERROR');
END delete_deliverable;
/*** This procedure will delete all deliverables that have been
created by applying a particular template on a busdoc.
It selects all deliverables which have original_deliverable_id
belonging to the p_template_id and deletes them from the busdoc.
Parameter Details:
p_doc_id : Business document Id
p_doc_type : Business document type
***/
PROCEDURE delete_template_deliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_doc_id IN NUMBER,
p_doc_type IN VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
-- bug#4075168 changed the select "and original_deliverable_id IN ( select original_deliverable_id"
CURSOR del_cur IS
select deliverable_id
from okc_deliverables
where business_document_id = p_doc_id
and business_document_type = p_doc_type
and business_document_version = -99
and original_deliverable_id IN (
select original_deliverable_id
from okc_deliverables
where business_document_type = 'TEMPLATE');
select DISTINCT exhibit_code
from okc_deliverables
where business_document_id = p_doc_id
and business_document_type = p_doc_type
and business_document_version = -99
and original_deliverable_id IN (
select original_deliverable_id
from okc_deliverables
where business_document_type = 'TEMPLATE')
AND DEL_CATEGORY_CODE = 'CDRL'
AND exhibit_code IS NOT NULL;
l_api_name CONSTANT VARCHAR2(30) :='delete_template_deliverables';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: inside OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables');
delete_attachments (
p_entity_name => G_ENTITY_NAME
,p_pk1_value => delIdTab(i)
,x_result => l_result);
DELETE FROM okc_deliverables
WHERE deliverable_id = delIdTab(i);
DELETE FROM OKC_DELIVERABLES_EXT_B
WHERE deliverable_id = delIdTab(i);
DELETE FROM OKC_DELIVERABLES_EXT_TL
WHERE deliverable_id = delIdTab(i);
OKC_CDRL_PVT.delete_exhibit_for_cdrl (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_doc_class => NULL, -- Value will be Derived in the API
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_doc_version => -99,
p_mode => NULL,
p_exhibit_code => exhibit_code_tbl(i),
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status,
p_validate_before_delete => 'Y'
) ;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables with G_EXC_UNEXPECTED_ERROR');
END delete_template_deliverables;
2. Select all deliverables definitions (-99 version) for the Business Document.
If deliverables exist then
a. Check each deliverable type
i. If only contractual deliverables amended then return CONTRACTUAL
ii. If only internal deliverables amended then return INTERNAL
iii.If both contractual and internal deliverables amended then return
CONTRACTUAL_AND_INTERNAL
iv.If both contractual and sourcing deliverables amended then return
CONTRACTUAL_AND_SOURCING
v.If both sourcing and internal deliverables amended then return
SOURCING_AND_INTERNAL
vi. If sourcing deliverables are amended then return SOURCING
vii. If all deliverables are amended then return ALL
3. If no deliverables amended then return NONE
4. If error return null**/
/*** added new signature bug#3192512**/
FUNCTION deliverables_amended(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_doctype IN VARCHAR2,
p_docid IN NUMBER
)
RETURN VARCHAR2 IS
--do not consider internal deliverables inamendments.
-- updated the cursor to filter internal deliverables
-- updated cursor for bug#4069955
CURSOR del_cur IS
SELECT
del.amendment_operation
,del.deliverable_type
,delType.internal_flag
FROM
okc_deliverables del
,okc_deliverable_types_b delType
WHERE del.business_document_id = p_docid
AND del.business_document_type = p_doctype
AND del.business_document_version = -99
AND del.summary_amend_operation_code is not null
--AND deliverable_type not like '%INTERNAL%'; --Commented as part of changes for new table okc_deliverable_types_b
select TL.meaning
from okc_bus_doc_events_tl TL
where TL.bus_doc_event_id = X
and TL.LANGUAGE = userenv('LANG');
select meaning into l_day_of_week
from fnd_lookups
where lookup_type = 'DAY_OF_WEEK'
and lookup_code = p_repeating_day_of_week;
select meaning into l_day_of_month
from fnd_lookups
where lookup_type = 'OKC_DAY_OF_MONTH'
and lookup_code = p_repeating_day_of_month;
SELECT *
FROM OKC_DELIVERABLES
WHERE business_document_id = p_source_doc_id
AND business_document_type = p_source_doc_type
AND recurring_del_parent_id is null;
SELECT *
FROM okc_deliverables
WHERE business_document_id = p_source_doc_id
AND business_document_type = p_source_doc_type
AND recurring_del_parent_id = X;
SELECT *
FROM okc_del_status_history
WHERE deliverable_id = Y;
delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
select okc_deliverable_id_s.nextval
INTO delNewTab(j).deliverable_id from dual;
delNewTab(j).last_updated_by:= Fnd_Global.User_Id;
delNewTab(j).last_update_date := sysdate;
delNewTab(j).last_update_login:=Fnd_Global.Login_Id;
delInsTab.DELETE;
delInsTab(m).LAST_UPDATED_BY:= del_ins_rec.LAST_UPDATED_BY;
delInsTab(m).LAST_UPDATE_DATE:= del_ins_rec.LAST_UPDATE_DATE;
delInsTab(m).LAST_UPDATE_LOGIN:= del_ins_rec.LAST_UPDATE_LOGIN;
select okc_deliverable_id_s.nextval
INTO delNewTab(j).deliverable_id from dual;
delNewTab(j).last_updated_by:= Fnd_Global.User_Id;
delNewTab(j).last_update_date := sysdate;
delNewTab(j).last_update_login:=Fnd_Global.Login_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,
RAISE_COMPLETION_EVENT_YN,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2,
del_category_code,
exhibit_code,
data_item_number,
price_group,
estimated_price,
uda_template_id,
schedule_type
)
VALUES (
delNewTab(i).DELIVERABLE_ID,
delNewTab(i).BUSINESS_DOCUMENT_TYPE ,
delNewTab(i).BUSINESS_DOCUMENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_NUMBER ,
delNewTab(i).DELIVERABLE_TYPE ,
delNewTab(i).RESPONSIBLE_PARTY ,
delNewTab(i).INTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).EXTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).DELIVERABLE_NAME ,
delNewTab(i).DESCRIPTION ,
delNewTab(i).COMMENTS ,
delNewTab(i).DISPLAY_SEQUENCE ,
delNewTab(i).FIXED_DUE_DATE_YN ,
delNewTab(i).ACTUAL_DUE_DATE ,
delNewTab(i).PRINT_DUE_DATE_MSG_NAME ,
delNewTab(i).RECURRING_YN ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_UOM ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_YN ,
delNewTab(i).NOTIFY_COMPLETED_YN ,
delNewTab(i).NOTIFY_OVERDUE_YN ,
delNewTab(i).NOTIFY_ESCALATION_YN ,
delNewTab(i).NOTIFY_ESCALATION_VALUE ,
delNewTab(i).NOTIFY_ESCALATION_UOM ,
delNewTab(i).ESCALATION_ASSIGNEE ,
delNewTab(i).AMENDMENT_OPERATION ,
delNewTab(i).PRIOR_NOTIFICATION_ID ,
delNewTab(i).AMENDMENT_NOTES ,
delNewTab(i).COMPLETED_NOTIFICATION_ID ,
delNewTab(i).OVERDUE_NOTIFICATION_ID ,
delNewTab(i).ESCALATION_NOTIFICATION_ID ,
delNewTab(i).LANGUAGE ,
delNewTab(i).ORIGINAL_DELIVERABLE_ID ,
delNewTab(i).REQUESTER_ID ,
delNewTab(i).EXTERNAL_PARTY_ID ,
delNewTab(i).EXTERNAL_PARTY_ROLE ,
delNewTab(i).RECURRING_DEL_PARENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_VERSION ,
delNewTab(i).RELATIVE_ST_DATE_DURATION ,
delNewTab(i).RELATIVE_ST_DATE_UOM ,
delNewTab(i).RELATIVE_ST_DATE_EVENT_ID ,
delNewTab(i).RELATIVE_END_DATE_DURATION ,
delNewTab(i).RELATIVE_END_DATE_UOM ,
delNewTab(i).RELATIVE_END_DATE_EVENT_ID ,
delNewTab(i).REPEATING_DAY_OF_MONTH ,
delNewTab(i).REPEATING_DAY_OF_WEEK ,
delNewTab(i).REPEATING_FREQUENCY_UOM ,
delNewTab(i).REPEATING_DURATION ,
delNewTab(i).FIXED_START_DATE ,
delNewTab(i).FIXED_END_DATE ,
delNewTab(i).MANAGE_YN ,
delNewTab(i).INTERNAL_PARTY_ID ,
delNewTab(i).DELIVERABLE_STATUS ,
delNewTab(i).STATUS_CHANGE_NOTES ,
delNewTab(i).CREATED_BY ,
delNewTab(i).CREATION_DATE ,
delNewTab(i).LAST_UPDATED_BY ,
delNewTab(i).LAST_UPDATE_DATE ,
delNewTab(i).LAST_UPDATE_LOGIN ,
delNewTab(i).OBJECT_VERSION_NUMBER ,
delNewTab(i).ATTRIBUTE_CATEGORY ,
delNewTab(i).ATTRIBUTE1 ,
delNewTab(i).ATTRIBUTE2 ,
delNewTab(i).ATTRIBUTE3 ,
delNewTab(i).ATTRIBUTE4 ,
delNewTab(i).ATTRIBUTE5 ,
delNewTab(i).ATTRIBUTE6 ,
delNewTab(i).ATTRIBUTE7 ,
delNewTab(i).ATTRIBUTE8 ,
delNewTab(i).ATTRIBUTE9 ,
delNewTab(i).ATTRIBUTE10 ,
delNewTab(i).ATTRIBUTE11 ,
delNewTab(i).ATTRIBUTE12 ,
delNewTab(i).ATTRIBUTE13 ,
delNewTab(i).ATTRIBUTE14 ,
delNewTab(i).ATTRIBUTE15 ,
delNewTab(i).DISABLE_NOTIFICATIONS_YN ,
delNewTab(i).LAST_AMENDMENT_DATE ,
delNewTab(i).BUSINESS_DOCUMENT_LINE_ID ,
delNewTab(i).EXTERNAL_PARTY_SITE_ID ,
delNewTab(i).START_EVENT_DATE ,
delNewTab(i).END_EVENT_DATE ,
delNewTab(i).SUMMARY_AMEND_OPERATION_CODE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_VALUE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_UOM,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_YN,
delNewTab(i).PAY_HOLD_OVERDUE_YN,
delNewTab(i).RAISE_COMPLETION_EVENT_YN,
delNewTab(i).orig_system_reference_code,
delNewTab(i).orig_system_reference_id1,
delNewTab(i).orig_system_reference_id2,
delNewTab(i).del_category_code,
delNewTab(i).exhibit_code,
delNewTab(i).data_item_number,
delNewTab(i).price_group,
delNewTab(i).estimated_price,
delNewTab(i).uda_template_id,
delNewTab(i).schedule_type
);
INSERT INTO okc_del_status_history (
deliverable_id,
deliverable_status,
status_changed_by,
status_change_date,
status_change_notes,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
delIdTab(i).del_id,
delStsHist_rec.DELIVERABLE_STATUS,
delStsHist_rec.STATUS_CHANGED_BY,
delStsHist_rec.STATUS_CHANGE_DATE,
delStsHist_rec.STATUS_CHANGE_NOTES,
delStsHist_rec.OBJECT_VERSION_NUMBER,
FND_GLOBAL.User_id,
sysdate,
FND_GLOBAL.User_id,
sysdate,
Fnd_Global.Login_Id);
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id
);
INSERT INTO okc_del_status_history
(deliverable_id,
deliverable_status,
STATUS_CHANGED_BY,
status_change_date,
status_change_notes,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (p_del_st_hist_tab(i).DELIVERABLE_ID
,p_del_st_hist_tab(i).DELIVERABLE_STATUS
,p_del_st_hist_tab(i).STATUS_CHANGED_BY
,p_del_st_hist_tab(i).STATUS_CHANGE_DATE
,p_del_st_hist_tab(i).STATUS_CHANGE_NOTES
,p_del_st_hist_tab(i).OBJECT_VERSION_NUMBER
,p_del_st_hist_tab(i).CREATED_BY
,p_del_st_hist_tab(i).CREATION_DATE
,p_del_st_hist_tab(i).LAST_UPDATED_BY
,p_del_st_hist_tab(i).LAST_UPDATE_DATE
,p_del_st_hist_tab(i).LAST_UPDATE_LOGIN
);
SELECT
del.deliverable_type deliverable_type
,del.responsible_party responsible_party
from
okc_deliverables del
,okc_deliverable_types_b delType
where del.business_document_type = p_bus_doc_type
and del.business_document_id = p_bus_doc_id
and del.business_document_version = -99
and del.summary_amend_operation_code is not null
and del.deliverable_type = delType.deliverable_type_code
and delType.internal_flag = 'N';
This API will be invoked by the Create/Update/ViewOnly Deliverable pages to display the
name for an External Party. The External Party could be VENDOR_ID from PO_VENDORS or
PARTY_ID from HZ_PARTIES
Parameter Details:
p_external_party_id: Unique Identifier from PO_VENDORS or HZ_PARTIES
p_external_party_role: Resp_Party_Code from OKC_RESP_PARTIES
24-FEB-2005 pnayani -- bug#4201738 updated get_party_name to return null
***/
FUNCTION get_party_name(
p_external_party_id IN NUMBER,
p_external_party_role IN VARCHAR2) RETURN VARCHAR2 IS
CURSOR get_vendor_name IS
SELECT vendor_name
from po_vendors
where vendor_id = p_external_party_id;
SELECT party_name
from hz_parties
where party_id = p_external_party_id;
SELECT *
FROM OKC_DELIVERABLES
WHERE business_document_id = p_source_doc_id
AND business_document_version = -99
AND business_document_type = p_source_doc_type
AND NVL(amendment_operation,'NONE')<> 'DELETED'
AND NVL(summary_amend_operation_code,'NONE')<> 'DELETED'
AND recurring_del_parent_id is null;
delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
select okc_deliverable_id_s.nextval INTO delNewTab(j).deliverable_id from dual;
delNewTab(j).last_updated_by:= Fnd_Global.User_Id;
delNewTab(j).last_update_date := sysdate;
delNewTab(j).last_update_login:=Fnd_Global.Login_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,
RAISE_COMPLETION_EVENT_YN,
del_category_code,
uda_template_id,
schedule_type
)
VALUES (
delNewTab(i).DELIVERABLE_ID,
delNewTab(i).BUSINESS_DOCUMENT_TYPE ,
delNewTab(i).BUSINESS_DOCUMENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_NUMBER ,
delNewTab(i).DELIVERABLE_TYPE ,
delNewTab(i).RESPONSIBLE_PARTY ,
delNewTab(i).INTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).EXTERNAL_PARTY_CONTACT_ID ,
delNewTab(i).DELIVERABLE_NAME ,
delNewTab(i).DESCRIPTION ,
delNewTab(i).COMMENTS ,
delNewTab(i).DISPLAY_SEQUENCE ,
delNewTab(i).FIXED_DUE_DATE_YN ,
delNewTab(i).ACTUAL_DUE_DATE ,
delNewTab(i).PRINT_DUE_DATE_MSG_NAME ,
delNewTab(i).RECURRING_YN ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_UOM ,
delNewTab(i).NOTIFY_PRIOR_DUE_DATE_YN ,
delNewTab(i).NOTIFY_COMPLETED_YN ,
delNewTab(i).NOTIFY_OVERDUE_YN ,
delNewTab(i).NOTIFY_ESCALATION_YN ,
delNewTab(i).NOTIFY_ESCALATION_VALUE ,
delNewTab(i).NOTIFY_ESCALATION_UOM ,
delNewTab(i).ESCALATION_ASSIGNEE ,
delNewTab(i).AMENDMENT_OPERATION ,
delNewTab(i).PRIOR_NOTIFICATION_ID ,
delNewTab(i).AMENDMENT_NOTES ,
delNewTab(i).COMPLETED_NOTIFICATION_ID ,
delNewTab(i).OVERDUE_NOTIFICATION_ID ,
delNewTab(i).ESCALATION_NOTIFICATION_ID ,
delNewTab(i).LANGUAGE ,
delNewTab(i).ORIGINAL_DELIVERABLE_ID ,
delNewTab(i).REQUESTER_ID ,
delNewTab(i).EXTERNAL_PARTY_ID ,
delNewTab(i).EXTERNAL_PARTY_ROLE ,
delNewTab(i).RECURRING_DEL_PARENT_ID ,
delNewTab(i).BUSINESS_DOCUMENT_VERSION ,
delNewTab(i).RELATIVE_ST_DATE_DURATION ,
delNewTab(i).RELATIVE_ST_DATE_UOM ,
delNewTab(i).RELATIVE_ST_DATE_EVENT_ID ,
delNewTab(i).RELATIVE_END_DATE_DURATION ,
delNewTab(i).RELATIVE_END_DATE_UOM ,
delNewTab(i).RELATIVE_END_DATE_EVENT_ID ,
delNewTab(i).REPEATING_DAY_OF_MONTH ,
delNewTab(i).REPEATING_DAY_OF_WEEK ,
delNewTab(i).REPEATING_FREQUENCY_UOM ,
delNewTab(i).REPEATING_DURATION ,
delNewTab(i).FIXED_START_DATE ,
delNewTab(i).FIXED_END_DATE ,
delNewTab(i).MANAGE_YN ,
delNewTab(i).INTERNAL_PARTY_ID ,
delNewTab(i).DELIVERABLE_STATUS ,
delNewTab(i).STATUS_CHANGE_NOTES ,
delNewTab(i).CREATED_BY ,
delNewTab(i).CREATION_DATE ,
delNewTab(i).LAST_UPDATED_BY ,
delNewTab(i).LAST_UPDATE_DATE ,
delNewTab(i).LAST_UPDATE_LOGIN ,
delNewTab(i).OBJECT_VERSION_NUMBER ,
delNewTab(i).ATTRIBUTE_CATEGORY ,
delNewTab(i).ATTRIBUTE1 ,
delNewTab(i).ATTRIBUTE2 ,
delNewTab(i).ATTRIBUTE3 ,
delNewTab(i).ATTRIBUTE4 ,
delNewTab(i).ATTRIBUTE5 ,
delNewTab(i).ATTRIBUTE6 ,
delNewTab(i).ATTRIBUTE7 ,
delNewTab(i).ATTRIBUTE8 ,
delNewTab(i).ATTRIBUTE9 ,
delNewTab(i).ATTRIBUTE10 ,
delNewTab(i).ATTRIBUTE11 ,
delNewTab(i).ATTRIBUTE12 ,
delNewTab(i).ATTRIBUTE13 ,
delNewTab(i).ATTRIBUTE14 ,
delNewTab(i).ATTRIBUTE15 ,
delNewTab(i).DISABLE_NOTIFICATIONS_YN ,
delNewTab(i).LAST_AMENDMENT_DATE ,
delNewTab(i).BUSINESS_DOCUMENT_LINE_ID ,
delNewTab(i).EXTERNAL_PARTY_SITE_ID ,
delNewTab(i).START_EVENT_DATE ,
delNewTab(i).END_EVENT_DATE ,
delNewTab(i).SUMMARY_AMEND_OPERATION_CODE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_VALUE,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_UOM,
delNewTab(i).PAY_HOLD_PRIOR_DUE_DATE_YN,
delNewTab(i).PAY_HOLD_OVERDUE_YN,
delNewTab(i).RAISE_COMPLETION_EVENT_YN,
delNewTab(i).del_category_code,
delNewTab(i).uda_template_id,
delNewTab(i).schedule_type
);
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id
);
PROCEDURE deleteDeliverables(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_commit IN VARCHAR2:=FND_API.G_FALSE,
p_bus_doc_id IN NUMBER,
p_bus_doc_type IN VARCHAR2,
p_bus_doc_version IN NUMBER,
p_prev_del_active IN VARCHAR2 := 'N',
p_revert_dels IN VARCHAR2 := 'N',
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
-- Cursor to get deliverable id and original deliverable ids of deliverables of
-- the current version of a business document
CURSOR cur_vers_del_csr IS
SELECT deliverable_id,
original_deliverable_id
FROM okc_deliverables
WHERE business_document_id = p_bus_doc_id
AND business_document_version = -99
AND business_document_type = p_bus_doc_type;
SELECT 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_value,
pay_hold_prior_due_date_uom,
pay_hold_prior_due_date_yn,
pay_hold_overdue_yn,
raise_completion_event_yn,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2,
del_category_code,
uda_template_id,
schedule_type
FROM okc_deliverables
WHERE business_document_id = p_bus_doc_id
AND business_document_version = p_bus_doc_version - 1
AND business_document_type = p_bus_doc_type
AND recurring_del_parent_id IS NULL
AND NVL(amendment_operation, ' ') <> 'DELETED';
SELECT BUSINESS_DOCUMENT_TYPE FROM okc_deliverables
WHERE deliverable_id=delid;
l_api_name CONSTANT VARCHAR2(30) :='deleteDeliverables';
delete_deliverables(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_doc_id => p_bus_doc_id,
p_doc_type => p_bus_doc_type,
p_doc_version => -99,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
delete_deliverables(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_doc_id => p_bus_doc_id,
p_doc_type => p_bus_doc_type,
p_doc_version => -99,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select okc_deliverable_id_s.nextval INTO l_deliverable_id from dual;
'Inserting a Deliverable with deliverable id ' || l_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,
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_value,
pay_hold_prior_due_date_uom,
pay_hold_prior_due_date_yn,
pay_hold_overdue_yn,
raise_completion_event_yn,
orig_system_reference_code, orig_system_reference_id1, orig_system_reference_id2, del_category_code,
uda_template_id ,schedule_type
)
VALUES( l_deliverable_id,
prev_vers_del(i).business_document_type,
prev_vers_del(i).business_document_id,
prev_vers_del(i).business_document_number,
prev_vers_del(i).deliverable_type,
prev_vers_del(i).responsible_party,
prev_vers_del(i).internal_party_contact_id,
prev_vers_del(i).external_party_contact_id,
prev_vers_del(i).deliverable_name,
prev_vers_del(i).description,
prev_vers_del(i).comments,
prev_vers_del(i).display_sequence,
prev_vers_del(i).fixed_due_date_yn,
prev_vers_del(i).actual_due_date,
prev_vers_del(i).print_due_date_msg_name,
prev_vers_del(i).recurring_yn,
prev_vers_del(i).notify_prior_due_date_value,
prev_vers_del(i).notify_prior_due_date_uom,
prev_vers_del(i).notify_prior_due_date_yn,
prev_vers_del(i).notify_completed_yn,
prev_vers_del(i).notify_overdue_yn,
prev_vers_del(i).notify_escalation_yn,
prev_vers_del(i).notify_escalation_value,
prev_vers_del(i).notify_escalation_uom,
prev_vers_del(i).escalation_assignee,
prev_vers_del(i).amendment_operation,
prev_vers_del(i).prior_notification_id,
prev_vers_del(i).amendment_notes,
prev_vers_del(i).completed_notification_id,
prev_vers_del(i).overdue_notification_id,
prev_vers_del(i).escalation_notification_id,
prev_vers_del(i).language,
prev_vers_del(i).original_deliverable_id,
prev_vers_del(i).requester_id,
prev_vers_del(i).external_party_id,
prev_vers_del(i).recurring_del_parent_id,
-99,
prev_vers_del(i).relative_st_date_duration,
prev_vers_del(i).relative_st_date_uom,
prev_vers_del(i).relative_st_date_event_id,
prev_vers_del(i).relative_end_date_duration,
prev_vers_del(i).relative_end_date_uom,
prev_vers_del(i).relative_end_date_event_id,
prev_vers_del(i).repeating_day_of_month,
prev_vers_del(i).repeating_day_of_week,
prev_vers_del(i).repeating_frequency_uom,
prev_vers_del(i).repeating_duration,
prev_vers_del(i).fixed_start_date,
prev_vers_del(i).fixed_end_date,
prev_vers_del(i).manage_yn,
prev_vers_del(i).internal_party_id,
prev_vers_del(i).deliverable_status,
prev_vers_del(i).status_change_notes,
prev_vers_del(i).created_by,
prev_vers_del(i).creation_date,
prev_vers_del(i).last_updated_by,
prev_vers_del(i).last_update_date,
prev_vers_del(i).last_update_login,
prev_vers_del(i).object_version_number,
prev_vers_del(i).attribute_category,
prev_vers_del(i).attribute1,
prev_vers_del(i).attribute2,
prev_vers_del(i).attribute3,
prev_vers_del(i).attribute4,
prev_vers_del(i).attribute5,
prev_vers_del(i).attribute6,
prev_vers_del(i).attribute7,
prev_vers_del(i).attribute8,
prev_vers_del(i).attribute9,
prev_vers_del(i).attribute10,
prev_vers_del(i).attribute11,
prev_vers_del(i).attribute12,
prev_vers_del(i).attribute13,
prev_vers_del(i).attribute14,
prev_vers_del(i).attribute15,
prev_vers_del(i).disable_notifications_yn,
prev_vers_del(i).last_amendment_date,
prev_vers_del(i).business_document_line_id,
prev_vers_del(i).external_party_site_id,
prev_vers_del(i).start_event_date,
prev_vers_del(i).end_event_date,
prev_vers_del(i).summary_amend_operation_code,
prev_vers_del(i).external_party_role,
prev_vers_del(i).pay_hold_prior_due_date_value,
prev_vers_del(i).pay_hold_prior_due_date_uom,
prev_vers_del(i).pay_hold_prior_due_date_yn,
prev_vers_del(i).pay_hold_overdue_yn,
prev_vers_del(i).raise_completion_event_yn,
prev_vers_del(i).orig_system_reference_code,
prev_vers_del(i).orig_system_reference_id1,
prev_vers_del(i).orig_system_reference_id2,
prev_vers_del(i).del_category_code,
prev_vers_del(i).uda_template_id,
prev_vers_del(i).schedule_type);
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id);
delete_deliverables(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_doc_id => p_bus_doc_id,
p_doc_type => p_bus_doc_type,
p_doc_version => p_bus_doc_version - 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END deleteDeliverables;
/** This procedure will delete the deliverable attachments and deliverbale status history.
This will be claled from DeliverableEOImpl.java remove method() where the deliverbale gets deleted in the remoev method
and thsi method will be called to remove its corresponding attachments and history
*/
PROCEDURE delete_del_attach_and_history (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_del_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_result VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) :='delete_del_attach_and_history';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Inside OKC_DELIVERABLE_PROCESS_PVT.delete_del_attach_and_history'||to_char(p_del_id));
DELETE FROM okc_del_status_history
WHERE deliverable_id = p_del_id;
delete_attachments (
p_entity_name => G_ENTITY_NAME
,p_pk1_value => p_del_id
,x_result => l_result);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_attach_and_history');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_attach_and_history with G_EXC_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_attach_and_history with G_EXC_UNEXPECTED_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving OKC_DELIVERABLE_PROCESS_PVT.delete_del_attach_and_history with G_EXC_UNEXPECTED_ERROR');
END delete_del_attach_and_history;
SELECT object_id
INTO l_object_id
FROM fnd_objects
WHERE obj_name = 'OKC_DELIVERABLE';
SELECT data_level_id
INTO l_main_data_level_id
FROM ego_data_level_b
WHERE attr_group_type = 'OKC_DELIVERABLES_EXT_ATTRS';
select before_after,event_name
from okc_bus_doc_events_v
where bus_doc_event_id = x;