[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*) into l_del_row_count
FROM okc_del_status_history
WHERE deliverable_id = p_deliverable_id
AND deliverable_status = p_deliverable_status;
SELECT deliverable_status into l_del_status
FROM okc_deliverables
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version
AND recurring_del_parent_id = p_del_id
AND rownum = 1;
SELECT count(*) into l_del_row_count
FROM okc_deliverables
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version
AND recurring_del_parent_id = p_del_id;
select okc_deliverable_id_s.nextval
INTO delInstanceRecTab(j).deliverable_id from dual;
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id);
delStsTab(st_hist_count).last_updated_by:= Fnd_Global.User_Id;
delStsTab(st_hist_count).last_update_date := sysdate;
delStsTab(st_hist_count).last_update_login := Fnd_Global.Login_Id;
delStsTab1(st_hist_count1).last_updated_by:= Fnd_Global.User_Id;
delStsTab1(st_hist_count1).last_update_date := sysdate;
delStsTab1(st_hist_count1).last_update_login := Fnd_Global.Login_Id;
delInstanceRecTab(j).last_updated_by:= Fnd_Global.User_Id;
delInstanceRecTab(j).last_update_date := sysdate;
delInstanceRecTab(j).last_update_login := Fnd_Global.Login_Id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'604: Bulk Inserting Instances Records ');
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
)
VALUES (
delInstanceRecTab(i).DELIVERABLE_ID,
delInstanceRecTab(i).BUSINESS_DOCUMENT_TYPE ,
delInstanceRecTab(i).BUSINESS_DOCUMENT_ID ,
delInstanceRecTab(i).BUSINESS_DOCUMENT_NUMBER ,
delInstanceRecTab(i).DELIVERABLE_TYPE ,
delInstanceRecTab(i).RESPONSIBLE_PARTY ,
delInstanceRecTab(i).INTERNAL_PARTY_CONTACT_ID ,
delInstanceRecTab(i).EXTERNAL_PARTY_CONTACT_ID ,
delInstanceRecTab(i).DELIVERABLE_NAME ,
delInstanceRecTab(i).DESCRIPTION ,
delInstanceRecTab(i).COMMENTS ,
delInstanceRecTab(i).DISPLAY_SEQUENCE ,
delInstanceRecTab(i).FIXED_DUE_DATE_YN ,
delInstanceRecTab(i).ACTUAL_DUE_DATE ,
delInstanceRecTab(i).PRINT_DUE_DATE_MSG_NAME ,
delInstanceRecTab(i).RECURRING_YN ,
delInstanceRecTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE ,
delInstanceRecTab(i).NOTIFY_PRIOR_DUE_DATE_UOM ,
delInstanceRecTab(i).NOTIFY_PRIOR_DUE_DATE_YN ,
delInstanceRecTab(i).NOTIFY_COMPLETED_YN ,
delInstanceRecTab(i).NOTIFY_OVERDUE_YN ,
delInstanceRecTab(i).NOTIFY_ESCALATION_YN ,
delInstanceRecTab(i).NOTIFY_ESCALATION_VALUE ,
delInstanceRecTab(i).NOTIFY_ESCALATION_UOM ,
delInstanceRecTab(i).ESCALATION_ASSIGNEE ,
delInstanceRecTab(i).AMENDMENT_OPERATION ,
delInstanceRecTab(i).PRIOR_NOTIFICATION_ID ,
delInstanceRecTab(i).AMENDMENT_NOTES ,
delInstanceRecTab(i).COMPLETED_NOTIFICATION_ID ,
delInstanceRecTab(i).OVERDUE_NOTIFICATION_ID ,
delInstanceRecTab(i).ESCALATION_NOTIFICATION_ID ,
delInstanceRecTab(i).LANGUAGE ,
delInstanceRecTab(i).ORIGINAL_DELIVERABLE_ID ,
delInstanceRecTab(i).REQUESTER_ID ,
delInstanceRecTab(i).EXTERNAL_PARTY_ID ,
delInstanceRecTab(i).EXTERNAL_PARTY_ROLE ,
delInstanceRecTab(i).RECURRING_DEL_PARENT_ID ,
delInstanceRecTab(i).BUSINESS_DOCUMENT_VERSION ,
delInstanceRecTab(i).RELATIVE_ST_DATE_DURATION ,
delInstanceRecTab(i).RELATIVE_ST_DATE_UOM ,
delInstanceRecTab(i).RELATIVE_ST_DATE_EVENT_ID ,
delInstanceRecTab(i).RELATIVE_END_DATE_DURATION ,
delInstanceRecTab(i).RELATIVE_END_DATE_UOM ,
delInstanceRecTab(i).RELATIVE_END_DATE_EVENT_ID ,
delInstanceRecTab(i).REPEATING_DAY_OF_MONTH ,
delInstanceRecTab(i).REPEATING_DAY_OF_WEEK ,
delInstanceRecTab(i).REPEATING_FREQUENCY_UOM ,
delInstanceRecTab(i).REPEATING_DURATION ,
delInstanceRecTab(i).FIXED_START_DATE ,
delInstanceRecTab(i).FIXED_END_DATE ,
delInstanceRecTab(i).MANAGE_YN ,
delInstanceRecTab(i).INTERNAL_PARTY_ID ,
delInstanceRecTab(i).DELIVERABLE_STATUS ,
delInstanceRecTab(i).STATUS_CHANGE_NOTES ,
delInstanceRecTab(i).CREATED_BY ,
delInstanceRecTab(i).CREATION_DATE ,
delInstanceRecTab(i).LAST_UPDATED_BY ,
delInstanceRecTab(i).LAST_UPDATE_DATE ,
delInstanceRecTab(i).LAST_UPDATE_LOGIN ,
delInstanceRecTab(i).OBJECT_VERSION_NUMBER ,
delInstanceRecTab(i).ATTRIBUTE_CATEGORY ,
delInstanceRecTab(i).ATTRIBUTE1 ,
delInstanceRecTab(i).ATTRIBUTE2 ,
delInstanceRecTab(i).ATTRIBUTE3 ,
delInstanceRecTab(i).ATTRIBUTE4 ,
delInstanceRecTab(i).ATTRIBUTE5 ,
delInstanceRecTab(i).ATTRIBUTE6 ,
delInstanceRecTab(i).ATTRIBUTE7 ,
delInstanceRecTab(i).ATTRIBUTE8 ,
delInstanceRecTab(i).ATTRIBUTE9 ,
delInstanceRecTab(i).ATTRIBUTE10 ,
delInstanceRecTab(i).ATTRIBUTE11 ,
delInstanceRecTab(i).ATTRIBUTE12 ,
delInstanceRecTab(i).ATTRIBUTE13 ,
delInstanceRecTab(i).ATTRIBUTE14 ,
delInstanceRecTab(i).ATTRIBUTE15 ,
delInstanceRecTab(i).DISABLE_NOTIFICATIONS_YN ,
delInstanceRecTab(i).LAST_AMENDMENT_DATE ,
delInstanceRecTab(i).BUSINESS_DOCUMENT_LINE_ID ,
delInstanceRecTab(i).EXTERNAL_PARTY_SITE_ID ,
delInstanceRecTab(i).START_EVENT_DATE ,
delInstanceRecTab(i).END_EVENT_DATE ,
delInstanceRecTab(i).SUMMARY_AMEND_OPERATION_CODE,
delInstanceRecTab(i).PAY_HOLD_PRIOR_DUE_DATE_VALUE,
delInstanceRecTab(i).PAY_HOLD_PRIOR_DUE_DATE_UOM,
delInstanceRecTab(i).PAY_HOLD_PRIOR_DUE_DATE_YN,
delInstanceRecTab(i).PAY_HOLD_OVERDUE_YN
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'604b: Now inserting Status history records for INACTIVE');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'604b: Now inserting Status history records for OPEN');
SELECT business_event_code, before_after into x_event_name, x_before_after
FROM OKC_BUS_DOC_EVENTS_B
WHERE bus_doc_event_id = p_event_id;
SELECT business_event_code, before_after into x_event_name, x_before_after
FROM OKC_BUS_DOC_EVENTS_B
WHERE bus_doc_event_id = p_event_id
AND (start_end_qualifier = 'BOTH' or start_end_qualifier = 'START');
SELECT *
FROM okc_deliverables del
WHERE del.business_document_id = p_bus_doc_id
AND del.business_document_version = p_bus_doc_version
AND del.business_document_type = p_bus_doc_type
AND del.deliverable_status = 'INACTIVE'
AND del.actual_due_date is NULL
AND del.recurring_del_parent_id is NULL
AND (del.amendment_operation is NULL OR del.amendment_operation <> 'DELETED')
AND (del.summary_amend_operation_code is NULL OR del.summary_amend_operation_code <> 'DELETED')
AND del.deliverable_type in (select delTypes.deliverable_type_code from
okc_bus_doc_types_b busDocTypes,
okc_del_bus_doc_combxns delTypes
WHERE busDocTypes.document_type = del.business_document_type
AND delTypes.document_type_class = busDocTypes.document_type_class
AND del.deliverable_type = delTypes.deliverable_type_code)
AND (G_FALSE = p_cancel_flag OR ( del.RELATIVE_ST_DATE_EVENT_ID in
(select docEvents.BUS_DOC_EVENT_ID
from okc_bus_doc_events_b docEvents
where docEvents.BUSINESS_EVENT_CODE = p_event_code
))
);
SELECT deliverable_id FROM OKC_DELIVERABLES
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version
AND recurring_del_parent_id = p_del_id
AND TRUNC(actual_due_date) > TRUNC(p_actual_date);
SELECT deliverable_id FROM OKC_DELIVERABLES
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version
AND recurring_del_parent_id = p_del_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;
delRecTab(i).amendment_operation = 'UPDATED') OR
(delRecTab(i).summary_amend_operation_code is not null AND
delRecTab(i).summary_amend_operation_code = 'UPDATED')) 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,'106: Recurring Definition = '||delRecTab(i).deliverable_id||' with Amendment Operation as '||delRecTab(i).amendment_operation);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Hence Delete Remaining Instances '||l_recurr_end_date);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances for count '||delIds.count);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances '||x_return_status);
OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
p_doc_id => p_bus_doc_id,
p_doc_type => p_bus_doc_type,
p_doc_version => p_bus_doc_version,
p_Conditional_Delete_Flag => 'Y',
p_delid_tab => delIds,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
' Finished delete_del_instances for count '||delIds.count);
' Finished OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances '
||x_return_status);
' Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances for count '
||delIds.count);
OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
p_doc_id => p_bus_doc_id,
p_doc_type => p_bus_doc_type,
p_doc_version => p_bus_doc_version,
p_Conditional_Delete_Flag => 'N',
p_delid_tab => delIds,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
' Finished OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances for count '||delIds.count);
END IF; --- recurring deliverable is newly created or instances are deleted
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'113: Buld update started');
UPDATE okc_deliverables
SET
actual_due_date = deliverableDueDates(i),
start_event_date = deliverableStartEventDates(i),
end_event_date = deliverableEndEventDates(i),
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login = Fnd_Global.Login_Id
WHERE deliverable_id = deliverableIds(i);
* Update deliverables, re-resolve deliverables for the dates passed by
* bus doc api, assumed to be changed.
*/
PROCEDURE updateDeliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN Varchar2,
p_bus_doc_id IN NUMBER,
p_bus_doc_type IN VARCHAR2,
p_bus_doc_version IN NUMBER,
p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
--- Define cursor to fetch already resolved deliverables
-- update cursor for bug#4069955
-- Updated the cusror bug 5018624
CURSOR del_cur IS
SELECT *
FROM okc_deliverables del
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version
AND (
(fixed_due_date_yn = 'N'
AND (amendment_operation is NULL OR amendment_operation <> 'DELETED')
AND (summary_amend_operation_code is NULL OR summary_amend_operation_code <> 'DELETED')
AND recurring_YN ='N'
AND actual_due_date is not null
AND recurring_del_parent_id is null)
OR
(recurring_YN = 'Y'
AND recurring_del_parent_id is null
AND del.deliverable_type in ( select d.deliverable_type_code
from okc_bus_doc_types_b bd,
okc_del_bus_doc_combxns d
WHERE bd.document_type = del.business_document_type
AND d.document_type_class = bd.document_type_class
AND del.deliverable_type = d.deliverable_type_code ))
);
SELECT deliverable_id FROM OKC_DELIVERABLES
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version
AND recurring_del_parent_id = p_del_id;
SELECT deliverable_id FROM OKC_DELIVERABLES
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version
AND recurring_del_parent_id = p_del_id
AND TRUNC(actual_due_date) > TRUNC(p_actual_date);
l_api_name CONSTANT VARCHAR2(30) := 'updateDeliverables';
SAVEPOINT g_update_del_GRP;
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;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'111: Recurring Deliverable = '||delRecTab(i).deliverable_id||' instances to be Deleted Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances');
OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
p_doc_id => p_bus_doc_id,
p_doc_type => p_bus_doc_type,
p_doc_version => p_bus_doc_version,
p_Conditional_Delete_Flag => 'Y',
p_delid_tab => delIds,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'112: Recurring Deliverable = '||delRecTab(i).deliverable_id||' FINISHED OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances'||x_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'123: Recurring Deliverable = '||delRecTab(i).deliverable_id||'Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances');
OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
p_doc_id => p_bus_doc_id,
p_doc_type => p_bus_doc_type,
p_doc_version => p_bus_doc_version,
p_Conditional_Delete_Flag => 'Y',
p_delid_tab => delIds,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'124: Recurring Deliverable = '||delRecTab(i).deliverable_id||'Finished OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances'||x_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'128: Bulk Update Started');
UPDATE okc_deliverables
SET
actual_due_date = deliverableDueDates(i),
start_event_date = deliverableStartEventDates(i),
end_event_date = deliverableEndEventDates(i),
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login = Fnd_Global.Login_Id
WHERE deliverable_id = deliverableIds(i);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateDeliverables');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
ROLLBACK TO g_update_del_GRP;
END; -- updateDeliverables
UPDATE OKC_DELIVERABLES set DISABLE_NOTIFICATIONS_YN = 'N'
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version;
* Update buyer on deliverables for given business document id and type.
*/
PROCEDURE updateBuyerOnDeliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN Varchar2,
p_bus_doc_id IN NUMBER,
p_bus_doc_type IN VARCHAR2,
p_bus_doc_version IN NUMBER,
p_original_buyer_id IN NUMBER,
p_new_buyer_id IN NUMBER,
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) := 'updateBuyerOnDeliverables';
SAVEPOINT g_update_del_GRP;
UPDATE OKC_DELIVERABLES
set internal_party_contact_id = p_new_buyer_id,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = p_bus_doc_version
AND internal_party_contact_id = p_original_buyer_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateDeliverables');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
ROLLBACK TO g_update_del_GRP;
END; -- updateDeliverables
* Update buyer on deliverables for given business document id and type.
*/
PROCEDURE updateBuyerOnDeliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN Varchar2,
p_bus_docs_tbl IN BUSDOCS_TBL_TYPE,
p_original_buyer_id IN NUMBER,
p_new_buyer_id IN NUMBER,
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) := 'updateBuyerDeliverables';
SAVEPOINT g_update2_del_GRP;
UPDATE OKC_DELIVERABLES
set internal_party_contact_id = p_new_buyer_id,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE internal_party_contact_id = p_original_buyer_id
AND business_document_id = l_bus_doc_ids(j)
AND business_document_type = l_bus_doc_types(j)
AND business_document_version = l_bus_doc_versions(j);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateBuyerDeliverables');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateBuyerDeliverables Unexpected ERROR');
ROLLBACK TO g_update2_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateBuyerDeliverables Unexpected ERROR');
ROLLBACK TO g_update2_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateBuyerDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
ROLLBACK TO g_update2_del_GRP;
END; -- updateBuyerDeliverables
* Update internal contact on deliverables for given business document id and type.
*/
PROCEDURE updateIntContactOnDeliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN Varchar2,
p_bus_doc_id IN NUMBER,
p_bus_doc_type IN VARCHAR2,
p_bus_doc_version IN NUMBER,
p_original_internal_contact_id IN NUMBER,
p_new_internal_contact_id IN NUMBER,
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) := 'updateIntContactOnDeliverables';
SAVEPOINT g_update_del_GRP;
UPDATE OKC_DELIVERABLES
set internal_party_contact_id = p_new_internal_contact_id,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version IN (-99, p_bus_doc_version)
AND internal_party_contact_id = p_original_internal_contact_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateIntContactOnDeliverables');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateIntContactOnDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateIntContactOnDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateIntContactOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
ROLLBACK TO g_update_del_GRP;
END; -- updateIntContactOnDeliverables
* Update internal contact on deliverables for given set of business document id and type.
*/
PROCEDURE updateIntContactOnDeliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN Varchar2,
p_bus_docs_tbl IN BUSDOCS_TBL_TYPE,
p_original_internal_contact_id IN NUMBER,
p_new_internal_contact_id IN NUMBER,
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) := 'updateIntContactOnDeliverables';
SAVEPOINT g_update2_del_GRP;
UPDATE OKC_DELIVERABLES
set internal_party_contact_id = p_new_internal_contact_id,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE internal_party_contact_id = p_original_internal_contact_id
AND business_document_id = l_bus_doc_ids(j)
AND business_document_type = l_bus_doc_types(j)
AND business_document_version IN (l_bus_doc_versions(j),-99);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateIntContactOnDeliverables');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateIntContactOnDeliverables Unexpected ERROR');
ROLLBACK TO g_update2_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateIntContactOnDeliverables Unexpected ERROR');
ROLLBACK TO g_update2_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateIntContactOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
ROLLBACK TO g_update2_del_GRP;
END; -- updateIntContactOnDeliverables
* This procedure updates external party id and site id
* on deliverables for given draft version of business document.
*/
PROCEDURE updateExtPartyOnDeliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_bus_doc_id IN NUMBER,
p_bus_doc_type IN VARCHAR2,
p_external_party_id IN NUMBER,
p_external_party_site_id IN NUMBER,
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) := 'updateExtPartyOnDeliverables';
SAVEPOINT g_update_del_GRP;
UPDATE OKC_DELIVERABLES
SET external_party_id = p_external_party_id,
external_party_site_id = p_external_party_site_id,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE business_document_id = p_bus_doc_id
AND business_document_type = p_bus_doc_type
AND business_document_version = -99;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateExtPartyOnDeliverables ');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateExtPartyOnDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateExtPartyOnDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateExtPartyOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
ROLLBACK TO g_update_del_GRP;
END; -- updateExtPartyOnDeliverables
* This procedure updates external party id and site id
* on deliverables for given version of business document.
* Used for Supplier Merge
* 15-JUN-2004 pnayani - bug#3691985 Supplier merge for Sourcing not working
*/
PROCEDURE updateExtPartyOnDeliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_document_class IN VARCHAR2,
p_from_external_party_id IN NUMBER,
p_from_external_party_site_id IN NUMBER,
p_to_external_party_id IN NUMBER,
p_to_external_party_site_id IN NUMBER,
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) := 'updateExtPartyOnDeliverables';
SELECT deliverable_id,external_party_site_id
FROM okc_deliverables
where external_party_id = p_from_external_party_id
and business_document_type IN (select document_type
from okc_bus_doc_types_b
where document_type_class = p_document_class);
SAVEPOINT g_update_del_GRP;
delIdTab.delete;
delExtSiteIdTab.delete;
UPDATE okc_deliverables
SET external_party_id = p_to_external_party_id,
external_party_site_id = delExtSiteIdTab(i),
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE deliverable_id = delIdTab(i);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateExtPartyOnDeliverables ');
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateExtPartyOnDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateExtPartyOnDeliverables Unexpected ERROR');
ROLLBACK TO g_update_del_GRP;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateExtPartyOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
ROLLBACK TO g_update_del_GRP;
select
deliverable.deliverable_status,
deliverable.status_change_notes,
deliverable.deliverable_id,
deliverable.notify_completed_yn
from okc_deliverables deliverable
where
deliverable.deliverable_status <> 'INACTIVE' and
business_document_id = p_bus_doc_id and
business_document_type = p_bus_doc_type and
business_document_version = p_bus_doc_version and
deliverable.deliverable_status <>
(select status_history_inner.deliverable_status
from okc_del_status_history status_history_inner
where status_history_inner.deliverable_id = deliverable.deliverable_id
and status_history_inner.deliverable_status <> 'INACTIVE'
and status_history_inner.status_change_date = (select max(status_change_date)
from okc_del_status_history
where deliverable_id = deliverable.deliverable_id and
deliverable_status <> 'INACTIVE'));
select
deliverable.deliverable_status,
deliverable.status_change_notes,
deliverable.deliverable_id,
status_history.status_change_date
from
okc_deliverables deliverable,
okc_del_status_history status_history
where
deliverable.deliverable_status <> 'INACTIVE' and
business_document_id = p_bus_doc_id and
business_document_type = p_bus_doc_type and
business_document_version = p_bus_doc_version and
status_history.deliverable_status = deliverable.deliverable_status and
status_history.deliverable_id = deliverable.deliverable_id and
(deliverable.status_change_notes <> status_history.status_change_notes OR status_history.status_change_notes IS NULL) and
status_history.status_change_date = (select max(status_history_inner.status_change_date)
from okc_del_status_history status_history_inner
where status_history_inner.deliverable_id = deliverable.deliverable_id and status_history_inner.deliverable_status <> 'INACTIVE');
update okc_del_status_history
set status_change_notes = status_notes_rec.status_change_notes
where status_change_date = status_notes_rec.status_change_date and
deliverable_id = status_notes_rec.deliverable_id and
deliverable_status = status_notes_rec.deliverable_status;
delStsTab(k).last_update_date := sysdate;
delStsTab(k).last_updated_by := fnd_global.user_id;
delStsTab(k).last_update_login := fnd_global.login_Id;
select OKC_WF_NOTIFY_S1.nextval into l_key from dual;
SELECT 'X'
FROM okc_deliverables
WHERE business_document_type = p_busdoc_type
AND business_document_id = p_busdoc_id
AND business_document_version =-99
AND UPPER(external_party_role) = UPPER(p_external_party_role)
AND external_party_id = p_external_party_id;
SELECT 'X'
FROM okc_deliverables
WHERE business_document_type = p_busdoc_type
AND business_document_id = p_busdoc_id
AND business_document_version = p_busdoc_version
AND manage_yn = 'Y';
* 11.5.10+ This procedure updates external party id and site id
* on deliverables for given class of business document.
* This API is for HZ party Merge process, it handles site merge
* within a customer
*/
PROCEDURE mergeExtPartyOnDeliverables (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_document_class IN VARCHAR2,
p_from_external_party_id IN NUMBER,
p_from_external_party_site_id IN NUMBER,
p_to_external_party_id IN NUMBER,
p_to_external_party_site_id IN NUMBER,
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) := 'mergeExtPartyOnDeliverables';
SELECT deliverable_id,external_party_site_id
FROM okc_deliverables
where external_party_id = NVL(p_from_external_party_id,external_party_id)
and external_party_role <> 'SUPPLIER_ORG'
and business_document_type IN (select document_type
from okc_bus_doc_types_b
where document_type_class = p_document_class);
SAVEPOINT g_update_del_GRP;
delIdTab.delete;
UPDATE okc_deliverables
SET external_party_id = NVL(p_to_external_party_id,external_party_id),
external_party_site_id = p_to_external_party_site_id,
last_updated_by= Fnd_Global.User_Id,
last_update_date = sysdate,
last_update_login=Fnd_Global.Login_Id
WHERE deliverable_id = delIdTab(i);
ROLLBACK TO g_update_del_GRP;
ROLLBACK TO g_update_del_GRP;
ROLLBACK TO g_update_del_GRP;
SELECT
DELIVERABLE_ID,
PAY_HOLD_PRIOR_DUE_DATE_YN,
PAY_HOLD_PRIOR_DUE_DATE_VALUE,
PAY_HOLD_PRIOR_DUE_DATE_UOM,
PAY_HOLD_OVERDUE_YN,
ACTUAL_DUE_DATE
FROM okc_deliverables
WHERE business_document_id = p_bus_doc_id
AND business_document_version = p_bus_doc_version
AND business_document_type = 'PO_STANDARD'
AND deliverable_type = 'CONTRACTUAL'
AND responsible_party = 'SUPPLIER_ORG'
AND (PAY_HOLD_PRIOR_DUE_DATE_YN = 'Y' OR PAY_HOLD_OVERDUE_YN = 'Y')
AND deliverable_status NOT IN ('COMPLETED','CANCELLED','INACTIVE');
select add_months(del_cur.actual_due_date,-del_cur.PAY_HOLD_PRIOR_DUE_DATE_VALUE)
INTO l_effective_beforedue_date from dual;
SELECT
PAY_HOLD_PRIOR_DUE_DATE_YN,
PAY_HOLD_PRIOR_DUE_DATE_VALUE,
PAY_HOLD_PRIOR_DUE_DATE_UOM,
PAY_HOLD_OVERDUE_YN,
ACTUAL_DUE_DATE
FROM okc_deliverables
WHERE deliverable_id = p_deliverable_id
AND business_document_type = 'PO_STANDARD'
AND deliverable_type = 'CONTRACTUAL'
AND responsible_party = 'SUPPLIER_ORG'
AND (PAY_HOLD_PRIOR_DUE_DATE_YN = 'Y' OR PAY_HOLD_OVERDUE_YN = 'Y')
AND deliverable_status NOT IN ('COMPLETED','CANCELLED','INACTIVE');