[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
l_delete_flag_tbl.extend(1);
l_delete_flag_tbl(1) := 'N';
l_delete_flag_tbl,
l_record_already_exist_tbl);
update po_headers_draft_all
set control_action = p_action
where draft_id = p_draft_id;
PO_DRAFTS_PVT.update_draft_status(p_draft_id,status);
PO_DRAFT_MERGE_PKG.delete_entity_locks(
p_draft_id => p_draft_id,
x_return_status => x_return_status
);
SELECT NVL (l_conterms_exist_flag, 'N'), po_header_id, type_lookup_code
INTO l_conterms_exist_flag, l_po_header_id, l_type_lookup_code
FROM po_headers_draft_all
WHERE draft_id = p_draft_id;
SELECT modification_number
INTO l_mod_number
FROM po_drafts
WHERE draft_id = p_draft_id;
SELECT item_key
BULK COLLECT INTO l_item_key_tbl
FROM wf_items wfi
WHERE wfi.user_key = To_Char(p_draft_id) AND item_type = 'POCMODNT';
SELECT po_line_id
BULK COLLECT INTO l_withdrawn_line_list
FROM po_lines_draft_all
WHERE draft_id = p_draft_id;
SELECT po_distribution_id
BULK COLLECT INTO l_withdrawn_dist_list
FROM po_distributions_draft_all
WHERE draft_id = p_draft_id;
update_header_dummy_req(p_draft_id => p_draft_id);
PO_REQ_LINES_SV.update_req_for_linked_po_count (l_withdrawn_line_list, 'LINE');
PO_REQ_LINES_SV.update_req_for_linked_po_count (l_withdrawn_dist_list, 'DISTRIBUTION MOD');
PO_REQ_LINES_SV.update_reqs_in_pool_flag;
SELECT po_line_id
BULK COLLECT INTO l_withdrawn_par_line_list
FROM po_lines_draft_all pld
WHERE draft_id = p_draft_id
AND EXISTS (SELECT 'PAR Line' FROM po_lines_draft_all
WHERE mod_line_id = pld.po_line_id
);
SELECT po_distribution_id
BULK COLLECT INTO l_withdrawn_par_dist_list
FROM po_distributions_draft_all
WHERE draft_id = p_draft_id
AND par_distribution_id IS NOT NULL;
update_header_dummy_req(p_draft_id => p_draft_id);
PO_REQ_LINES_SV.update_req_for_linked_po_count (l_withdrawn_par_line_list,
'LINE');
PO_REQ_LINES_SV.update_req_for_linked_po_count (l_withdrawn_par_dist_list,
'DISTRIBUTION MOD');
PO_REQ_LINES_SV.update_reqs_in_pool_flag;
PROCEDURE NAME: update_header_dummy_req
DESCRIPTION: This procedure will update the ReqsInPoolFlag of the dummy
header req when mod is deleted.
===========================================================================*/
PROCEDURE update_header_dummy_req(p_draft_id IN NUMBER)
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_header_dummy_req';
UPDATE po_requisition_lines_all prl
SET reqs_in_pool_flag = 'Y',
linked_po_count = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE par_line_id IS NULL
AND clm_info_flag = 'Y'
AND par_draft_id = ( SELECT phd.draft_id
FROM po_headers_draft_all phd, po_requisition_headers_all prh
WHERE phd.mod_draft_id = p_draft_id
AND prh.requisition_header_id = prl.requisition_header_id
AND Nvl(prh.par_flag, 'N') = 'Y'
);
PO_LOG.stmt(d_module, d_position, 'Dummy Header Req Updated Count ', SQL%ROWCOUNT);
END update_header_dummy_req;
PROCEDURE process_mod_delete_action(p_draft_id in number,
result out NOCOPY varchar2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'process_mod_delete_action';
l_deleted_line_list PO_TBL_NUMBER;
l_deleted_dist_list PO_TBL_NUMBER;
PO_DRAFT_MERGE_PKG.delete_entity_locks( p_draft_id => p_draft_id,
x_return_status => l_return_status);
SELECT item_key
BULK COLLECT INTO l_item_key_tbl
FROM wf_items wfi
WHERE wfi.user_key = To_Char(p_draft_id) AND wfi.item_type = 'POCMODNT';
SELECT 'PURCHASING', TYPE_LOOKUP_CODE, STYLE_ID,
nvl(conterms_exist_flag, 'N'), po_header_id, type_lookup_code
INTO l_functional_area_code, l_type_lookup_code,
l_document_style_id, l_conterms_exist_flag, l_po_header_id, l_type_lookup_code
FROM po_headers_draft_all
WHERE draft_id = p_draft_id;
delete_uda_data(p_draft_id => p_draft_id,
p_entity_id => l_po_header_id,
p_entity_level => 'PO_HEADER',
x_return_status => l_return_status);
SELECT DECODE(draft_type,
'MOD', 'Y',
'PAR', 'N')
INTO l_release_locks_yn
FROM PO_DRAFTS
WHERE draft_id = p_draft_id;
OKC_TERMS_UTIL_GRP.delete_doc
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, p_doc_id => p_draft_id
, p_doc_type => l_contract_document_type
, p_validate_commit => FND_API.G_FALSE
, p_release_locks_yn => 'Y'
, x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
);
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
( 'PO_HEADERS',
l_po_header_id,
p_draft_id, '', '', '', 'Y');
l_deleted_line_list := PO_TBL_NUMBER();
l_deleted_dist_list := PO_TBL_NUMBER();
SELECT po_line_id
BULK COLLECT INTO l_deleted_line_list
FROM po_lines_draft_all
WHERE draft_id = p_draft_id;
SELECT po_distribution_id
BULK COLLECT INTO l_deleted_dist_list
FROM po_distributions_draft_all
WHERE draft_id = p_draft_id;
PO_LOG.stmt(d_module,d_position,'l_deleted_line_list',l_deleted_line_list);
PO_LOG.stmt(d_module,d_position,'l_deleted_dist_list',l_deleted_dist_list);
IF ( l_deleted_line_list.Count > 0) THEN
PO_REQ_LINES_SV.update_req_for_linked_po_count (l_deleted_line_list, 'LINE');
IF ( l_deleted_dist_list.Count > 0) THEN
PO_REQ_LINES_SV.update_req_for_linked_po_count (l_deleted_dist_list, 'DISTRIBUTION MOD');
IF (l_deleted_line_list.Count > 0 OR l_deleted_dist_list.Count > 0) THEN
PO_REQ_LINES_SV.update_reqs_in_pool_flag;
update_header_dummy_req(p_draft_id => p_draft_id);
OKC_REP_CONTRACT_IMP_PUB.delete_cancel_contract(p_api_version => '1.0',
p_commit => fnd_api.g_false,
p_document_rec => p_document_rec,
p_contract_type => 'REP_SBCR',
p_contract_id => null,
x_msg_data => l_msg_data ,
x_msg_count =>l_msg_count,
x_return_status => l_return_status);
END process_mod_delete_action;
procedure delete_uda_data(p_draft_id IN Number,
p_entity_id IN Number,
p_entity_level IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'delete_uda_data';
DELETE FROM po_headers_all_ext_b
WHERE draft_id = p_draft_id
AND po_header_id = p_entity_id;
DELETE FROM po_headers_all_ext_tl
WHERE draft_id = p_draft_id
AND po_header_id = p_entity_id;
DELETE FROM po_lines_all_ext_b
WHERE (draft_id = p_draft_id --main record for Mod
AND po_line_id IN (SELECT po_line_id
FROM po_lines_draft_all
WHERE po_header_id = p_entity_id
AND draft_id = p_draft_id))
OR (draft_id = -p_draft_id -- delta record for Mod complex pricing attribute
AND po_line_id IN (SELECT -po_line_id
FROM po_lines_draft_all
WHERE po_header_id = p_entity_id
AND draft_id = p_draft_id)
AND pk1_value = -p_draft_id)
OR (draft_id = -1 -- old record for Mod Complex pricing.
AND pk1_value = p_draft_id --: pk1_value is the p_draft_id not -1
AND po_line_id IN (SELECT po_Line_id
FROM po_lines_draft_all
WHERE po_header_id = p_entity_id
AND draft_id = p_draft_id));
DELETE FROM po_lines_all_ext_tl
WHERE (draft_id = p_draft_id --main record for Mod
AND po_line_id IN (SELECT po_line_id
FROM po_lines_draft_all
WHERE po_header_id = p_entity_id
AND draft_id = p_draft_id))
OR (draft_id = -p_draft_id -- delta record for Mod complex pricing attribute
AND po_line_id IN (SELECT -po_line_id
FROM po_lines_draft_all
WHERE po_header_id = p_entity_id
AND draft_id = p_draft_id)
AND pk1_value = -p_draft_id)
OR (draft_id = -1 -- old record for Mod Complex pricing.
AND pk1_value = p_draft_id --: pk1_value is the p_draft_id not -1
AND po_line_id IN (SELECT po_Line_id
FROM po_lines_draft_all
WHERE po_header_id = p_entity_id
AND draft_id = p_draft_id));
DELETE FROM po_line_locations_all_ext_b
WHERE draft_id = p_draft_id
AND line_location_id IN(SELECT line_location_id
FROM po_line_locations_draft_all
WHERE po_header_id = p_entity_id
AND draft_id = p_draft_id);
DELETE FROM po_line_locations_all_ext_tl
WHERE draft_id = p_draft_id
AND line_location_id IN(SELECT line_location_id
FROM po_line_locations_draft_all
WHERE po_header_id = p_entity_id
AND draft_id = p_draft_id);
DELETE FROM po_lines_all_ext_b
WHERE ( draft_id = p_draft_id -- main record for Mod
AND po_line_id = p_entity_id)
OR ( draft_id = -p_draft_id -- delta record for complex pricing
AND po_line_id = -p_entity_id
AND pk1_value = -p_draft_id)
OR ( draft_id = -1 -- old record for complex pricing
AND po_line_id = p_entity_id
AND pk1_value = p_draft_id);
DELETE FROM po_line_locations_all_ext_b
WHERE draft_id = p_draft_id
AND line_location_id IN (SELECT line_location_id
FROM po_line_locations_draft_all
WHERE po_line_id = p_entity_id
AND draft_id = p_draft_id);
DELETE FROM po_lines_all_ext_tl
WHERE ( draft_id = p_draft_id -- main record for Mod
AND po_line_id = p_entity_id)
OR ( draft_id = -p_draft_id -- delta record for complex pricing
AND po_line_id = -p_entity_id
AND pk1_value = -p_draft_id)
OR ( draft_id = -1 -- old record for complex pricing
AND po_line_id = p_entity_id
AND pk1_value = p_draft_id);
DELETE FROM po_line_locations_all_ext_tl
WHERE draft_id = p_draft_id
AND line_location_id IN (SELECT line_location_id
FROM po_line_locations_draft_all
WHERE po_line_id = p_entity_id
AND draft_id = p_draft_id);
DELETE FROM po_line_locations_all_ext_b
WHERE draft_id = p_draft_id
AND line_location_id = p_entity_id;
DELETE FROM po_line_locations_all_ext_tl
WHERE draft_id = p_draft_id
AND line_location_id = p_entity_id;
END delete_uda_data;
l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
PO_LINES_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_po_line_id => p_po_line_id
);
PO_LINE_LOCATIONS_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_line_location_id => NULL
);
PO_DISTRIBUTIONS_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_po_distribution_id => NULL
);
PO_PRICE_DIFF_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_price_differential_id => NULL
);
PO_ATTR_VALUES_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_attribute_values_id => NULL
);
PO_ATTR_VALUES_TLP_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_attribute_values_tlp_id => NULL
);
PO_PRICE_ADJ_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_price_adjustment_id => NULL
);
l_delete_flag_tbl.extend(1);
l_delete_flag_tbl(1) := 'N';
l_delete_flag_tbl,
l_record_already_exist_tbl);
update po_lines_draft_all
set control_action = p_action
where draft_id = p_draft_id;
l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
PO_LINE_LOCATIONS_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_line_location_id => p_po_line_loc_id
);
l_delete_flag_tbl.extend(1);
l_delete_flag_tbl(1) := 'N';
l_delete_flag_tbl,
l_record_already_exist_tbl);
update po_line_locations_draft_all
set control_action = p_action
where draft_id = p_draft_id;
SELECT type_lookup_code
INTO l_doc_subtype
FROM po_headers_all
WHERE po_header_id = p_doc_header_id;
SELECT item_id
INTO l_item_id
FROM po_lines_merge_v -- Replaced po_lines_all
WHERE po_line_id = p_doc_level_id
AND draft_id = p_draft_id;
SELECT 'N'
INTO l_ship_invalid_for_ctrl_actn
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM po_line_locations_all poll1,
po_line_locations_all poll2
WHERE poll1.line_location_id = p_doc_level_id
AND poll1.po_line_id = poll2.po_line_id
AND NVL(poll2.cancel_flag,'N') <> 'Y'
AND NVL(poll2.payment_type, 'NULL') NOT IN ('ADVANCE', 'DELIVERY') --
AND NVL(poll2.closed_code, PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN)
<> PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
AND poll2.line_location_id <> p_doc_level_id);
IF(l_mode = 'UPDATE'
AND NOT (l_current_action LIKE 'CANCEL%'
OR l_current_action LIKE '%HOLD%')) THEN
NULL;
ELSIF(l_mode = 'UPDATE' AND l_current_action = 'CANCEL PO') THEN
d_pos := 100;
PO_LOG.stmt(d_module,d_pos,l_current_action || 'is replaced by CANCEL for update mode');
END IF; --l_mode = 'UPDATE'
SELECT prh.requisition_header_id,
podc.default_approval_path_id,
prh.authorization_status,
prh.org_id
INTO l_req_header_id,
l_approval_id,
l_req_authorization_status,
l_org_id
FROM po_requisition_headers_all prh,
po_document_types_all podc
WHERE prh.segment1 = To_char(p_draft_id)
AND podc.document_type_code = 'REQUISITION'
AND podc.document_subtype = prh.type_lookup_code
AND podc.org_id = prh.org_id;
UPDATE po_lines_draft_all
SET draft_line_status = 'WITHDRAWN'
WHERE draft_id = p_draft_id
AND draft_line_status NOT IN( 'MOD_CREATED', 'PO_CREATED' );
SELECT DISTINCT Decode (draft_line_status,
NULL, 'COMPLETED',
draft_line_status)
INTO line_status_code
FROM (SELECT draft_line_status,
Rank () over ( ORDER BY Decode (draft_line_status,
'ASSIGNED', 1,
'MOD_CREATED', 2,
'PO_CREATED', 3,
'RETURNED', 4,
'WITHDRAWN', 5,
'COMPLETED', 6) ASC) priority
FROM po_lines_draft_all
WHERE draft_id = p_draft_id)
WHERE priority = 1;
UPDATE po_drafts
SET status = 'WITHDRAWN'
WHERE draft_id = p_draft_id;
SELECT sequence_num,
object_revision_num,
approval_path_id
INTO l_sequence_num,
l_object_rev_num,
l_approval_path_id
FROM po_action_history
WHERE object_id = p_draft_id
AND object_type_code = 'PO'
AND sequence_num = (SELECT max(sequence_num)
FROM po_action_history
WHERE object_id = p_draft_id
AND Object_Type_Code = 'PO'
AND object_sub_type_code = 'POST_AWARD_REQUEST')
AND object_sub_type_code = 'POST_AWARD_REQUEST';
INSERT INTO PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(p_draft_id,
'PO', -- doc_type
'POST_AWARD_REQUEST', -- doc_subtype
l_sequence_num + 1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
'WITHDRAW', -- action_code
sysdate, --action_date
p_employee_id,
NULL,
l_object_rev_num,
fnd_global.login_id,
0,
0,
0,
'',
l_approval_path_id,
'' );
SELECT item_key
bulk collect INTO l_item_key_tbl
FROM wf_items wfi
WHERE wfi.user_key = To_char(p_draft_id)
AND item_type = 'POCMODNT';