[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: lock_row_for_status_update
===========================================================================*/
PROCEDURE lock_row_for_buyer_update (x_rowid IN VARCHAR2)
IS
CURSOR C IS
SELECT *
FROM po_requisition_lines
WHERE rowid = x_rowid
FOR UPDATE of requisition_line_id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PO_MESSAGE_S.SQL_ERROR('LOCK_ROW_FOR_BUYER_UPDATE', x_progress, sqlcode);
PROCEDURE NAME: delete_line
===========================================================================*/
PROCEDURE delete_line(X_line_id IN NUMBER,
X_mode IN VARCHAR2,
X_transferred_to_oe_flag OUT NOCOPY VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
SELECT rowid
INTO x_rowid
FROM po_requisition_lines
WHERE requisition_line_id = x_line_id;
** DEBUG: We need to delete attachments.
*/
/*
** Delete the children before deleting the line.
*/
x_progress := '020';
po_req_lines_sv.delete_children(X_line_id, X_mode);
** Delete the requisition line.
*/
x_progress := '030';
po_requisition_lines_pkg1.delete_row(x_rowid, x_transferred_to_oe_flag);
po_message_s.sql_error('delete_line', x_progress, sqlcode);
END delete_line;
PROCEDURE NAME: delete_children
===========================================================================*/
PROCEDURE delete_children(X_line_id IN NUMBER,
X_mode IN VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
** DEBUG. Call to delete attachments.
*/
-- dbms_output.put_line('After call to delete attachments');
DELETE FROM po_req_distributions
WHERE requisition_line_id = X_line_id;
po_message_s.sql_error('delete_children', x_progress, sqlcode);
END delete_children;
SELECT COUNT(1)
INTO X_row_exists
FROM PO_REQUISITION_LINES PORL,
PO_LINE_LOCATIONS POLL
WHERE PORL.requisition_header_id = X_req_header_id
AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id)
AND PORL.line_location_id = POLL.line_location_id
AND PORL.line_location_id is NOT NULL
AND (nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND nvl(POLL.cancel_flag, 'N') = 'N');
Select requisition_line_id
From po_requisition_lines
Where requisition_line_id = nvl(X_req_line_id, requisition_line_id)
And requisition_header_id = X_req_header_id
And source_type_code = 'INVENTORY';
/* SELECT COUNT(1)
INTO X_row_exists
FROM PO_REQUISITION_HEADERS PORH, PO_REQUISITION_LINES PORL,
PO_SYSTEM_PARAMETERS POSP
WHERE PORH.requisition_header_id = X_req_header_id
AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id)
AND PORH.requisition_header_id = PORL.requisition_header_id
AND PORL.source_type_code = 'INVENTORY'
AND OE_ORDER_IMPORT_INTEROP_PUB .Get_Open_Qty(POSP.order_source_id,
PORH.segment1,
PORL.line_num ) > 0 ;
SELECT COUNT(1)
INTO X_row_exists
FROM PO_REQUISITION_HEADERS PORH, PO_REQUISITION_LINES PORL,
PO_SYSTEM_PARAMETERS POSP
WHERE PORH.requisition_header_id = X_req_header_id
AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id)
AND PORH.requisition_header_id = PORL.requisition_header_id
AND PORL.source_type_code = 'INVENTORY'
AND OE_ORDER_IMPORT_INTEROP_PUB .Get_Open_Qty(POSP.order_source_id,
PORH.requisition_header_id,
PORL.requisition_line_id ) > 0 ;
SELECT COUNT(1)
INTO X_row_exists
FROM PO_REQUISITION_LINES PORL
WHERE PORL.requisition_header_id = X_req_header_id
AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id)
AND PORL.source_type_code = 'INVENTORY'
AND nvl(PORL.cancel_flag, 'N') = 'N'
AND nvl(PORL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND PORL.quantity_delivered < nvl(PORL.quantity_received,0);
(select nvl(sum(quantity_received),0)
from RCV_SHIPMENT_LINES RSL
where RSL.requisition_line_id = PORL.requisition_line_id);
PROCEDURE NAME: update_reqs_lines_incomplete
===========================================================================*/
PROCEDURE update_reqs_lines_incomplete
(X_req_header_id IN NUMBER,
X_req_line_id IN NUMBER,
X_req_control_error_rc IN OUT NOCOPY VARCHAR2,
X_oe_installed_flag IN VARCHAR2) IS
X_progress VARCHAR2(3) := NULL;
** update it on the Req line before calling the code to reverse
** encumbrance.
*/
BEGIN
select order_source_id
into X_order_source_id
from po_system_parameters;
/* The following SQL statement is optimized to update either
** 1. all document lines - if header_id is passed or,
** 2. one document line - if both header_id and line_id are passed.
*/
X_progress := '010';
UPDATE PO_REQUISITION_LINES
SET cancel_flag = 'I',
quantity_cancelled = NVL(X_quantity_cancelled, quantity_cancelled),
reqs_in_pool_flag = NULL, --
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate
WHERE requisition_header_id = X_req_header_id
AND requisition_line_id = nvl(X_req_line_id, requisition_line_id)
AND nvl(cancel_flag, 'N') IN ('N', 'I')
AND nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';
po_message_s.sql_error('update_reqs_lines_incomplete', X_progress, sqlcode);
po_message_s.sql_error('update_reqs_lines_incomplete', X_progress, sqlcode);
END update_reqs_lines_incomplete;
PROCEDURE NAME: update_reqs_lines_status
===========================================================================*/
PROCEDURE update_reqs_lines_status
(X_req_header_id IN NUMBER,
X_req_line_id IN NUMBER,
X_req_control_action IN VARCHAR2,
X_req_control_reason IN VARCHAR2,
X_req_action_date IN DATE,
X_oe_installed_flag IN VARCHAR2,
X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
X_progress VARCHAR2(3) := NULL;
** update it on the Req line.
*/
BEGIN
select order_source_id
into X_order_source_id
from po_system_parameters;
/* The following SQL statement is optimized to update either
** 1. all document lines - if only header_id is passed.
** 2. one document line - if line_id is also passed.
*/
/* Bug 4036549 - changed the below sql assignment from
quantity_cancelled = nvl(X_quantity_cancelled, quantity_cancelled) to
quantity_cancelled = nvl(X_quantity_cancelled, decode(X_cancel_flag,'Y',quantity,quantity_cancelled))
*/
UPDATE PO_REQUISITION_LINES
SET cancel_flag = nvl(X_cancel_flag, cancel_flag),
cancel_date = nvl(X_cancel_date, cancel_date),
cancel_reason = nvl(X_cancel_reason, cancel_reason),
closed_code = nvl(X_closed_code, closed_code),
closed_reason = nvl(X_closed_reason, closed_reason),
closed_date = nvl(X_closed_date, closed_date),
contractor_status = decode(X_cancel_flag,'Y',null,contractor_status), -- Bug 3495679
reqs_in_pool_flag = DECODE(X_terminal_performed,
1,NULL,
reqs_in_pool_flag), --
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
quantity_cancelled = nvl(X_quantity_cancelled,
decode(X_cancel_flag, 'Y', quantity, quantity_cancelled)
)
WHERE requisition_header_id = X_req_header_id
AND requisition_line_id = nvl(X_req_line_id, requisition_line_id)
AND nvl(cancel_flag, 'N') IN ('N', 'I')
AND nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';
UPDATE PO_REQ_DISTRIBUTIONS
SET req_line_quantity = 0
WHERE requisition_line_id IN
(SELECT requisition_line_id
FROM po_requisition_lines PORL
WHERE PORL.requisition_header_id = X_req_header_id
AND nvl(PORL.cancel_flag,'N') = 'Y'
AND PORL.source_type_code = 'VENDOR'
AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id));
po_message_s.sql_error('update_reqs_lines_status', X_progress, sqlcode);
END update_reqs_lines_status;
SELECT s.po_header_id, s.po_release_id, s.po_line_id, s.line_location_id,
rl.requisition_header_id, rl.requisition_line_id
BULK COLLECT INTO l_po_header_id, l_po_release_id, l_po_line_id, l_line_location_id,
L_req_header_id, l_req_line_id
FROM po_line_locations s, po_requisition_lines rl
WHERE s.line_location_id = rl.line_location_id and nvl(rl.drop_ship_flag, 'N') = 'Y'
AND s.po_header_id = X_entity_id;
UPDATE po_requisition_lines_all -- Bug 3592153
SET line_location_id = NULL,
reqs_in_pool_flag = 'Y', -- Bug 2781027 resetting the reqs in pool flag
last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
last_update_date = sysdate -- Bug5623016 (updating who column)
WHERE line_location_id in (SELECT line_location_id
FROM po_line_locations
WHERE po_header_id = X_entity_id);
SELECT s.po_header_id, s.po_release_id, s.po_line_id, s.line_location_id,
rl.requisition_header_id, rl.requisition_line_id
BULK COLLECT INTO l_po_header_id, l_po_release_id, l_po_line_id, l_line_location_id,
L_req_header_id, l_req_line_id
FROM po_line_locations s, po_requisition_lines rl
WHERE s.line_location_id = rl.line_location_id and nvl(rl.drop_ship_flag, 'N') = 'Y'
AND s.po_release_id = X_entity_id;
UPDATE po_requisition_lines_all -- Bug 3592153
SET line_location_id = NULL,
reqs_in_pool_flag = 'Y', -- Bug 2781027
last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
last_update_date = sysdate -- Bug5623016 (updating who column)
WHERE line_location_id in (SELECT line_location_id
FROM po_line_locations
WHERE po_release_id = X_entity_id);
SELECT s.po_header_id, s.po_release_id, s.po_line_id, s.line_location_id,
rl.requisition_header_id, rl.requisition_line_id
BULK COLLECT INTO l_po_header_id, l_po_release_id, l_po_line_id, l_line_location_id,
L_req_header_id, l_req_line_id
FROM po_line_locations s, po_requisition_lines rl
WHERE s.line_location_id = rl.line_location_id and nvl(rl.drop_ship_flag, 'N') = 'Y'
AND s.po_line_id = X_entity_id;
UPDATE po_requisition_lines_all -- Bug 3592153
SET line_location_id = NULL,
reqs_in_pool_flag = 'Y', -- Bug 2781027
last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
last_update_date = sysdate -- Bug5623016 (updating who column)
WHERE line_location_id in (SELECT line_location_id
FROM po_line_locations
WHERE po_line_id = X_entity_id);
SELECT s.po_header_id, s.po_release_id, s.po_line_id, s.line_location_id,
rl.requisition_header_id, rl.requisition_line_id
BULK COLLECT INTO l_po_header_id, l_po_release_id, l_po_line_id, l_line_location_id,
L_req_header_id, l_req_line_id
FROM po_line_locations s, po_requisition_lines rl
WHERE s.line_location_id = rl.line_location_id and nvl(rl.drop_ship_flag, 'N') = 'Y'
AND s.line_location_id = X_entity_id;
UPDATE po_requisition_lines_all -- Bug 3592153
SET line_location_id = NULL,
reqs_in_pool_flag = 'Y', -- Bug 2781027
last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
last_update_date = sysdate -- Bug5623016 (updating who column)
WHERE line_location_id in (SELECT line_location_id
FROM po_line_locations
WHERE line_location_id = X_entity_id);
OE_DROP_SHIP_GRP.Update_Drop_Ship_links(
p_api_version => 1.0,
p_po_header_id => l_po_header_id(i),
p_po_release_id => l_po_release_id(i),
p_po_line_id => l_po_line_id(i),
p_po_line_location_id => l_line_location_id(i),
p_new_req_hdr_id => l_req_header_id(i),
p_new_req_line_id => l_req_line_id(i),
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
'After Call to OE_DROP_SHIP_GRP.Update_Drop_Ship_links RetStatus: ' || l_return_status
|| 'POHeader:' || l_po_header_id(i) || ' Release:' || l_po_release_id(i)
|| ' Line:' || l_po_line_id(i) || ' LineLoc:' || l_line_location_id(i)
|| ' ReqHdr:' || l_req_header_id(i) || ' ReqLine:' || l_req_line_id(i));
PROCEDURE NAME: update_transferred_to_oe_flag
===========================================================================*/
PROCEDURE update_transferred_to_oe_flag(X_req_hdr_id IN NUMBER,
X_transferred_to_oe_flag OUT NOCOPY VARCHAR2)
IS
x_progress VARCHAR2(3) := NULL;
SELECT count(*)
INTO x_inv_count
FROM po_requisition_lines prl
WHERE prl.requisition_header_id = x_req_hdr_id
AND prl.source_type_code = 'INVENTORY';
only if it is NULL. Need not update the flag, if it is already 'Y'or 'N' */
SELECT transferred_to_oe_flag
INTO x_flag
FROM po_requisition_headers
WHERE requisition_header_id = x_req_hdr_id;
** Update the flag on requisition headers.
*/
x_progress := '030';
po_reqs_sv.update_oe_flag (X_req_hdr_id, X_flag);
po_message_s.sql_error('update_transferred_to_oe_flag',
x_progress, sqlcode);
END update_transferred_to_oe_flag;
PROCEDURE NAME: update_reqs_in_pool_flag
===========================================================================*/
PROCEDURE update_reqs_in_pool_flag
( x_req_line_id IN NUMBER ,
x_req_header_id IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2
)
IS
x_progress VARCHAR2(3) := NULL;
UPDATE po_requisition_lines_all prl
SET prl.reqs_in_pool_flag =
CASE
WHEN NVL(prl.cancel_flag,'N') = 'Y'
OR NVL(prl.closed_code,'OPEN') = 'FINALLY CLOSED'
OR NVL(prl.line_location_id,-999) <> -999
OR NVL(prl.modified_by_agent_flag,'N') = 'Y'
OR prl.at_sourcing_flag = 'Y'
OR prl.source_type_code <> 'VENDOR'
OR NVL((SELECT prh.authorization_status
FROM PO_REQUISITION_HEADERS_ALL prh
WHERE prh.requisition_header_id = prl.requisition_header_id)
, 'INCOMPLETE') <> 'APPROVED'
OR NVL((SELECT prh.contractor_status
FROM PO_REQUISITION_HEADERS_ALL prh
WHERE prh.requisition_header_id = prl.requisition_header_id)
, 'NOT APPLICABLE') = 'PENDING'
THEN
NULL
ELSE
'Y'
END
, prl.last_update_date = SYSDATE
, prl.last_updated_by = FND_GLOBAL.USER_ID
, prl.last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
PRL.Requisition_Line_ID in (
SELECT SUB.Requisition_Line_ID
FROM PO_REQUISITION_LINES_ALL SUB
WHERE SUB.Requisition_Header_Id = x_req_header_id
AND x_req_line_id IS NULL
UNION ALL
SELECT SUB2.Requisition_Line_ID
FROM PO_REQUISITION_LINES_ALL SUB2
WHERE SUB2.Requisition_Line_Id = x_req_line_id
);
PO_MESSAGE_S.sql_error('UPDATE_REQS_IN_POOL_FLAG',x_progress,sqlcode);
END update_reqs_in_pool_flag;
select order_source_id
into X_order_source_id
from po_system_parameters;
SELECT segment1
INTO X_req_num
FROM po_requisition_headers_all
WHERE requisition_header_id = X_req_header_id;
SELECT COUNT(1)
INTO X_row_exists
FROM MTL_SUPPLY
WHERE req_header_id = X_req_header_id
AND req_line_id = NVL(X_req_line_id, req_line_id)
AND supply_type_code = 'SHIPMENT'
AND quantity > 0;