[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DOCUMENT_TYPE_CLASS
FROM okc_bus_doc_types_vl
WHERE DOCUMENT_TYPE = p_doc_type;
PROCEDURE delete_deliverables( p_del_id_tbl IN deliverable_tbl_type
, p_mode IN VARCHAR2 DEFAULT NULL
)
IS
CURSOR cur_attach (cdrl_id VARCHAR2)
IS
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 = OKC_DELIVERABLE_PROCESS_PVT.G_ENTITY_NAME
AND att.pk1_value = cdrl_id;
DELETE FROM okc_del_status_history
WHERE deliverable_id = p_del_id_tbl(i);
fnd_attached_documents3_pkg.delete_row (
X_attached_document_id => attach_doc_id_tbl(j),
X_datatype_id => datatype_id_tbl(j),
delete_document_flag => 'Y' );
DELETE FROM okc_deliverables
WHERE deliverable_id = p_del_id_tbl(i);
UPDATE okc_deliverables
SET amendment_operation = 'DELETED'
, summary_amend_operation_code = 'DELETED'
WHERE deliverable_id = p_del_id_tbl(i)
;
END delete_deliverables;
Select LOOKUP_CODE
from fnd_lookup_values_vl e
where lookup_type = 'PO_CLM_EXHIBIT_NUMBER'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN Trunc(START_DATE_ACTIVE) AND Nvl(END_DATE_ACTIVE,SYSDATE+1)
AND (
( ('SOURCING' = p_doc_class)
AND
(NOT EXISTS (SELECT 'Y'
FROM pon_auction_exhibit_details n
WHERE n.AUCTION_HEADER_ID=p_doc_id
AND n.EXHIBIT_NUMBER= e.LOOKUP_CODE
)
) -- NOT EXISTS
) -- SOURCING
OR
(
('PO' = p_doc_class)
AND
(
( SubStr(p_doc_type,-3) <> 'MOD'
AND NOT EXISTS (SELECT 'Y'
FROM po_exhibit_details p
WHERE p.po_HEADER_ID=p_doc_id
AND p.EXHIBIT_NAME= e.LOOKUP_CODE)
) -- NORMAL CASE
OR
( SubStr(p_doc_type,-3) = 'MOD'
AND NOT EXISTS (SELECT 'Y'
FROM po_exhibit_details_merge_v p
WHERE p.draft_ID=p_doc_id
AND p.EXHIBIT_NAME= e.LOOKUP_CODE)
) -- MOD CASE
) -- AND
) --OR
) -- AND
order by length(lookup_code), lookup_code;
SELECT deliverable_id,orig_system_reference_id1
FROM okc_deliverables
WHERE business_document_type = p_doc_type
and business_document_id = p_doc_id
AND business_document_version = Nvl(p_doc_version,-99)
AND exhibit_code=p_target_exhibit
AND del_category_code = 'CDRL' ;
SELECT Count(1) INTO l_src_cdrl_count
FROM okc_deliverables
WHERE business_document_type = p_doc_type
and business_document_id =p_doc_id
AND business_document_version = Nvl(p_doc_version,-99)
and DEL_CATEGORY_CODE = 'CDRL'
and exhibit_code = p_src_exhibit;
SELECT NEXT_ELIN_SET
BULK COLLECT INTO data_item_num_tbl
FROM
(SELECT p_target_exhibit
||Decode( Length(p_target_exhibit)+Length(OKC_CDRL_PVT.DECIMAL_TO_ELIN(X)),4,'',3,'0',2,'00')
||OKC_CDRL_PVT.DECIMAL_TO_ELIN(X)
AS NEXT_ELIN_SET
FROM
(SELECT LEVEL AS X
FROM dual
CONNECT BY LEVEL <=
(l_src_cdrl_count)
)
)
WHERE ROWNUM<=l_src_cdrl_count
ORDER BY NEXT_ELIN_SET
;
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
--, amendment_notes
, 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
-- Who col
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, object_version_number
-- DFF
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, disable_notifications_yn
-- , last_amendment_date
, business_document_line_id
, external_party_site_id
, start_event_date
, end_event_date
-- , summary_amend_operation_code
, external_party_role
, pay_hold_prior_due_date_yn
, pay_hold_prior_due_date_value
, pay_hold_prior_due_date_uom
, pay_hold_overdue_yn
, completion_date
, 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
)
SELECT OKC_DELIVERABLE_ID_S.NEXTVAL
,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
--, amendment_notes
, LANGUAGE
,OKC_DELIVERABLE_ID_S.CURRVAL
, 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
-- WHO cols
, fnd_global.user_id --created_by
, SYSDATE --creation_date
, fnd_global.user_id --last_updated_by
, SYSDATE --last_update_date
, fnd_global.login_id --last_update_login
, 1 --object_version_number
-- DFF
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, disable_notifications_yn
-- , last_amendment_date
, business_document_line_id
, external_party_site_id
, start_event_date
, end_event_date
-- , summary_amend_operation_code
, external_party_role
, pay_hold_prior_due_date_yn
, pay_hold_prior_due_date_value
, pay_hold_prior_due_date_uom
, pay_hold_overdue_yn
, completion_date
, raise_completion_event_yn
, 'COPY'
, deliverable_id
-- , orig_system_reference_id2
, del_category_code
, p_target_exhibit
-- , REPLACE(data_item_number,p_src_exhibit,p_target_exhibit)
, price_group
, estimated_price
, uda_template_id
, schedule_type
FROM okc_deliverables
WHERE business_document_type = p_doc_type
AND business_document_id = p_doc_id
AND business_document_version = Nvl(p_doc_version,-99)
AND exhibit_code = p_src_exhibit
AND del_category_code = 'CDRL'
;
UPDATE okc_deliverables
SET data_item_number=data_item_num_tbl(i)
WHERE deliverable_id= new_del_id_tbl(i);
X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id
);
PROCEDURE delete_cdrl_for_exhibits
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_commit IN VARCHAR2:=FND_API.G_FALSE,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_doc_version IN NUMBER,
p_mode IN VARCHAR2 DEFAULT NULL,
p_exhibit_tbl IN exhibit_tbl_type,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
del_id_tbl deliverable_tbl_type;
SELECT deliverable_id
FROM okc_deliverables
WHERE business_document_type = p_doc_type
AND business_document_id = p_doc_id
AND business_document_version = Nvl(p_doc_version,-99)
AND exhibit_code = p_exhibit_code
AND del_category_code='CDRL';
l_api_name varchar2(30) := 'delete_cdrl_for_exhibits';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: delete_cdrl_for_exhibits');
delete_deliverables(del_id_tbl,p_mode);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'999: leaving delete_cdrl_for_exhibits');
END delete_cdrl_for_exhibits;
pon_exhibits_pkg.INSERT_CDRL_EXHIBIT_DETAILS (
p_auction_header_id => p_doc_id,
p_exhibit_number => p_exhibit_code,
x_return_status => l_return_status,
x_return_msg => l_msg_data
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: After Calling pon_exhibits_pkg.INSERT_CDRL_EXHIBIT_DETAILS');
po_clm_okc_integ_pkg.insert_exhibits
(
p_document_type_tbl => l_document_type_tbl,
p_document_id_tbl => l_document_id_tbl,
p_exhibit_name_tbl => l_exhibit_name_tbl,
p_exhibit_description_tbl => l_exhibit_description_tbl,
p_revision_num_tbl => l_revision_num_tbl,
p_is_cdrl_tbl => l_is_cdrl_tbl,
x_return_status => l_return_status,
x_return_msg => l_msg_data
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: After Calling po_clm_okc_integ_pkg.insert_exhibits');
PROCEDURE delete_exhibit_for_cdrl
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_commit IN VARCHAR2:=FND_API.G_FALSE,
p_doc_class IN VARCHAR2,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_doc_version IN NUMBER,
p_mode IN VARCHAR2,
p_exhibit_code IN VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_validate_before_delete IN VARCHAR2 DEFAULT 'Y',
p_deliverable_id IN NUMBER DEFAULT NULL
)
IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'delete_exhibit_for_cdrl';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: delete_exhibit_for_cdrl');
SAVEPOINT g_delete_exhibit_for_cdrl;
IF p_validate_before_delete = 'Y' THEN
SELECT Count(1)
INTO l_cdrl_count
FROM okc_deliverables
WHERE business_document_type = p_doc_type
AND business_document_id = p_doc_id
AND business_document_version = Nvl(p_doc_version,-99)
AND exhibit_code = p_exhibit_code
AND ( p_deliverable_id IS NULL OR
(p_deliverable_id <> deliverable_id)
);
IF ( p_validate_before_delete = 'N' OR
( p_validate_before_delete= 'Y' AND l_cdrl_count <= 0)
) THEN
-- SOURCING
IF l_doc_class = 'SOURCING' THEN
pon_exhibits_pkg.DELETE_CDRL_EXHIBIT_DETAILS (
p_auction_header_id => p_doc_id,
p_exhibit_number => p_exhibit_code,
x_return_status => x_return_status
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: After Calling pon_exhibits_pkg.DELETE_CDRL_EXHIBIT_DETAILS');
po_clm_okc_integ_pkg.delete_exhibits (
p_document_type_tbl => p_document_type_tbl,
p_document_id_tbl => p_document_id_tbl,
p_exhibit_name_tbl => p_exhibit_name_tbl,
p_is_cdrl_tbl => p_is_cdrl_tbl,
x_return_status => x_return_status,
x_return_msg => x_return_msg
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: After Calling po_clm_okc_integ_pkg.delete_exhibits');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'999: leaving delete_exhibit_for_cdrl');
ROLLBACK TO g_delete_exhibit_for_cdrl;
FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving delete_exhibit_for_cdrl with G_EXC_ERROR');
ROLLBACK TO g_delete_exhibit_for_cdrl;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving delete_exhibit_for_cdrl with G_EXC_UNEXPECTED_ERROR ');
ROLLBACK TO g_delete_exhibit_for_cdrl;
FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving delete_exhibit_for_cdrl with OTHERS EXCEPTION '||SQLERRM);
END delete_exhibit_for_cdrl;
line_num_tbl_qry := 'SELECT Nvl(Max(ROWNUM),0)+1
FROM (SELECT OKC_CDRL_PVT.ELIN_TO_DECIMAL(SubStr(data_item_number,'|| exhibit_len||'+1,4-'||exhibit_len||')) elin_decimal FROM okc_deliverables
where business_document_type = :1 and business_document_id =:2
and business_document_version = -99 and DEL_CATEGORY_CODE = :3
and exhibit_code = :4 order by 1) WHERE ELIN_DECIMAL=rownum';
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 => p_doc_class,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_doc_version => p_doc_version,
p_mode => p_mode,
p_exhibit_code => p_old_exhibit_code,
x_msg_data => x_return_status,
x_msg_count => x_msg_count,
x_return_status => x_return_status,
p_deliverable_id => p_deliverable_id);
SELECT DISTINCT exhibit_code
FROM okc_deliverables;
SELECT DISTINCT c.exhibit_code
BULK COLLECT INTO l_exhibit_tbl
FROM okc_deliverables c,
TABLE(l_okc_cdrl_ids) d
WHERE Value(d)=c.deliverable_id;
SELECT Count(1) INTO l_existing_cdrl_count
FROM okc_deliverables
WHERE business_document_type = p_doc_type
and business_document_id =p_doc_id
AND business_document_version = Nvl(p_doc_version,-99)
and DEL_CATEGORY_CODE = 'CDRL'
and exhibit_code = p_exhibit_code;
SELECT NEXT_ELIN_SET
BULK COLLECT INTO data_item_num_tbl
FROM
(SELECT p_exhibit_code
||Decode( Length(p_exhibit_code)+Length(OKC_CDRL_PVT.DECIMAL_TO_ELIN(X)),4,'',3,'0',2,'00')
||OKC_CDRL_PVT.DECIMAL_TO_ELIN(X)
AS NEXT_ELIN_SET
FROM
(SELECT LEVEL AS X
FROM dual
CONNECT BY LEVEL <=
(l_input_cdrl_count + l_existing_cdrl_count)
)
)
WHERE NEXT_ELIN_SET
NOT IN (SELECT data_item_number
FROM okc_deliverables
WHERE business_document_type = p_doc_type
and business_document_id =p_doc_id
AND business_document_version = Nvl(p_doc_version,-99)
and DEL_CATEGORY_CODE = 'CDRL'
and exhibit_code = p_exhibit_code
)
and ROWNUM<=l_input_cdrl_count
ORDER BY NEXT_ELIN_SET
;
UPDATE okc_deliverables
SET exhibit_code= p_exhibit_code
,data_item_number=data_item_num_tbl(i)
WHERE deliverable_id= p_cdrl_tbl(i);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' Updated table with the new values. ' || data_item_num_tbl.Count);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' Calling delete API for deleting Exhibit ' || l_exhibit_tbl (i));
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 => p_doc_class,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_doc_version => p_doc_version,
p_mode => p_mode,
p_exhibit_code => l_exhibit_tbl(i),
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status
);
PROCEDURE delete_doc_exhibits (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_commit IN VARCHAR2:=FND_API.G_FALSE,
p_doc_class IN VARCHAR2 DEFAULT NULL,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_doc_version IN NUMBER DEFAULT NULL,
p_mode IN VARCHAR2 DEFAULT NULL,
p_retain_lock_deliverables_yn IN VARCHAR2 DEFAULT 'N',
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR cur_exhibits
IS
SELECT DISTINCT exhibit_code
FROM okc_deliverables
WHERE business_document_type = p_doc_type
AND business_document_id = p_doc_id
AND DEL_CATEGORY_CODE = 'CDRL'
AND (p_retain_lock_deliverables_yn = 'N'
OR
(p_retain_lock_deliverables_yn = 'Y'
AND amendment_operation IS NULL)
) ;
SELECT DISTINCT exhibit_code
FROM okc_deliverables
WHERE business_document_type = p_doc_type
AND business_document_id = p_doc_id
AND DEL_CATEGORY_CODE = 'CDRL'
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)
);
SELECT DOCUMENT_TYPE_CLASS
FROM okc_bus_doc_types_vl
WHERE DOCUMENT_TYPE = p_doc_type;
l_api_name VARCHAR2(60) := 'delete_doc_exhibits';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Starting delete_doc_exhibits');
SAVEPOINT g_delete_doc_exhibit;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name, l_progress|| ' Calling delete API for deleting Exhibit ' || l_exhibit_tbl (i));
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 => l_bus_doc_class,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_doc_version => p_doc_version,
p_mode => p_mode,
p_exhibit_code => l_exhibit_tbl(i),
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status,
p_validate_before_delete => 'N'
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving delete_doc_exhibits');
ROLLBACK TO g_delete_doc_exhibit;
END delete_doc_exhibits;
SELECT 'Y'
FROM okc_deliverables
WHERE business_document_type = p_document_type
AND business_document_id = p_document_id
AND business_document_version=Nvl(p_doc_version,-99)
AND del_category_code='CDRL';
l_exhibit_tbl_for_delete exhibit_tbl_type;
SELECT DISTINCT exhibit_code
BULK COLLECT INTO l_doc_exhibit_tbl
FROM okc_deliverables
WHERE business_document_type = p_doc_type
AND business_document_id = p_doc_id
AND business_document_version=Nvl(p_doc_version,-99)
AND del_category_code='CDRL'
AND exhibit_code IS NOT NULL;
l_exhibit_tbl_for_delete (k) := l_doc_exhibit_tbl(i);
delete_cdrl_for_exhibits
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_doc_version => p_doc_version,
p_mode => p_mode,
p_exhibit_tbl => l_exhibit_tbl_for_delete,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status
);