[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(1)
INTO X_row_exists
FROM PO_DOCUMENT_TYPES PODT
WHERE PODT.DOCUMENT_TYPE_CODE = X_doc_type
AND PODT.DOCUMENT_SUBTYPE = X_doc_subtype
AND (X_doc_agent_id = X_agent_id
OR (PODT.SECURITY_LEVEL_CODE = 'PUBLIC'
AND PODT.access_level_code = 'FULL')
OR (PODT.SECURITY_LEVEL_CODE = 'PURCHASING'
AND PODT.access_level_code = 'FULL'
AND EXISTS
(SELECT 'Is the user an agent'
FROM PO_AGENTS POA
WHERE POA.AGENT_ID = X_agent_id
AND sysdate BETWEEN POA.start_date_active
AND nvl(POA.end_date_active,sysdate+1)))
OR (PODT.SECURITY_LEVEL_CODE = 'HIERARCHY'
AND PODT.access_level_code = 'FULL'
AND X_agent_id IN
(SELECT POEH.SUPERIOR_ID
FROM PO_EMPLOYEE_HIERARCHIES POEH,
PO_SYSTEM_PARAMETERS POSP
WHERE POEH.EMPLOYEE_ID = X_doc_agent_id
AND POEH.POSITION_STRUCTURE_ID =
POSP.SECURITY_POSITION_STRUCTURE_ID)));
SELECT COUNT(1)
INTO X_row_exists
FROM PO_DOCUMENT_TYPES PODT
WHERE PODT.DOCUMENT_TYPE_CODE = X_doc_type
AND PODT.DOCUMENT_SUBTYPE = X_doc_subtype
AND (X_doc_agent_id = X_agent_id
OR (PODT.SECURITY_LEVEL_CODE = 'PUBLIC'
AND PODT.access_level_code IN ('MODIFY','FULL'))
OR (PODT.SECURITY_LEVEL_CODE = 'PURCHASING'
AND PODT.access_level_code IN ('MODIFY','FULL')
AND EXISTS
(SELECT 'Is the user an agent'
FROM PO_AGENTS POA
WHERE POA.AGENT_ID = X_agent_id
AND sysdate BETWEEN POA.start_date_active
AND nvl(POA.end_date_active,sysdate+1)))
OR (PODT.SECURITY_LEVEL_CODE = 'HIERARCHY'
AND PODT.access_level_code IN ('MODIFY','FULL')
AND X_agent_id IN
(SELECT POEH.SUPERIOR_ID
FROM PO_EMPLOYEE_HIERARCHIES POEH,
PO_SYSTEM_PARAMETERS POSP
WHERE POEH.EMPLOYEE_ID = X_doc_agent_id
AND POEH.POSITION_STRUCTURE_ID =
POSP.SECURITY_POSITION_STRUCTURE_ID)));
po_req_lines_sv.update_reqs_lines_incomplete (X_req_header_id,
X_req_line_id,
X_req_control_error_rc,
X_oe_installed_flag);
po_reqs_control_sv.update_reqs_status (X_req_header_id,
X_req_line_id,
X_agent_id,
X_req_doc_type,
X_req_doc_subtype,
X_req_control_action,
X_req_control_reason,
X_req_action_date,
X_encumbrance_flag,
X_oe_installed_flag,
X_req_control_error_rc);
PROCEDURE NAME: update_reqs_status
===========================================================================*/
PROCEDURE update_reqs_status
(X_req_header_id IN NUMBER,
X_req_line_id IN NUMBER,
X_agent_id IN NUMBER,
X_req_doc_type IN VARCHAR2,
X_req_doc_subtype IN VARCHAR2,
X_req_control_action IN VARCHAR2,
X_req_control_reason IN VARCHAR2,
X_req_action_date IN DATE,
X_encumbrance_flag IN VARCHAR2,
X_oe_installed_flag IN VARCHAR2,
X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
X_progress VARCHAR2(3) := NULL;
po_req_dist_sv.update_reqs_distributions (X_req_header_id,
X_req_line_id,
X_req_control_action,
X_req_action_date,
X_req_control_error_rc);
to that requisition should be deleted irrespective of the status
of the requisition. Hence commenting out the condition below.
*/
/* IF ((X_req_header_auth_status = 'APPROVED') OR (X_req_header_auth_status = 'RETURNED')) THEN
*/
IF X_req_line_id IS NULL THEN
X_supply_action := 'Remove_Req_Supply';
po_req_lines_sv.update_reqs_lines_status (X_req_header_id,
X_req_line_id,
X_req_control_action,
X_req_control_reason,
X_req_action_date,
X_oe_installed_flag,
X_req_control_error_rc);
po_reqs_sv.update_reqs_header_status (X_req_header_id,
X_req_line_id,
X_req_control_action,
X_req_control_reason,
X_req_action_history_code,
X_req_control_error_rc);
DELETE FROM PO_ACTION_HISTORY
WHERE object_id = X_req_header_id
AND object_type_code = X_req_doc_type
AND action_code IS NULL;
po_forward_sv1.insert_action_history (X_req_header_id,
X_req_doc_type,
X_req_doc_subtype,
NULL,
X_req_action_history_code,
sysdate,
X_agent_id,
NULL,
X_req_control_reason,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
fnd_global.user_id,
fnd_global.login_id);
po_forward_sv1.update_action_history (X_req_header_id,
X_req_doc_type,
NULL,
X_req_action_history_code,
X_req_control_reason,
fnd_global.user_id,
fnd_global.login_id);
po_forward_sv1.insert_action_history (X_req_header_id,
X_req_doc_type,
X_req_doc_subtype,
NULL,
X_req_action_history_code,
sysdate,
X_agent_id,
NULL,
X_req_control_reason,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
fnd_global.user_id,
fnd_global.login_id);
/* po_notifications_sv1.delete_po_notif (x_Req_doc_type,
X_req_header_id); */
SELECT wf_item_type,wf_item_key
INTO x_item_type,x_item_key
FROM PO_REQUISITION_HEADERS
WHERE requisition_header_id = x_req_header_id;
po_message_s.sql_error('update_reqs_status', x_progress, sqlcode);
END update_reqs_status;
UPDATE MTL_SUPPLY
SET quantity = 0,
change_flag = 'Y'
WHERE supply_type_code = 'REQ'
AND req_header_id = X_supply_id;
/* bug 404433: - Update the MTL_SUPPLY table in two steps to keep
** the code compatible as in R10 userexit.
** The delete step is to fire the MRP triggers.
*/
DELETE FROM MTL_SUPPLY
WHERE supply_type_code = 'REQ'
AND quantity = 0
AND change_flag = 'Y'
AND req_header_id = X_supply_id;
UPDATE MTL_SUPPLY
SET quantity = 0,
change_flag = 'Y'
WHERE supply_type_code = 'REQ'
AND req_line_id = X_supply_id;
DELETE FROM MTL_SUPPLY
WHERE supply_type_code = 'REQ'
AND quantity = 0
AND change_flag = 'Y'
AND req_line_id = X_supply_id;