[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_ALL POLL -- Bug 8659519
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 nvl(cancel_flag,'N')<>'Y' --Added for bug 13036681
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;
x_last_update_login po_requisition_lines.last_update_login%TYPE := fnd_global.login_id;
x_last_updated_by po_requisition_lines.last_updated_by%TYPE := fnd_global.user_id;
x_last_update_date po_requisition_lines.last_update_date%TYPE := SYSDATE;
TYPE last_update_login_tb IS TABLE OF po_requisition_lines.last_update_login%TYPE INDEX BY PLS_INTEGER;
TYPE last_updated_by_tb IS TABLE OF po_requisition_lines.last_updated_by%TYPE INDEX BY PLS_INTEGER;
TYPE last_update_date_tb IS TABLE OF po_requisition_lines.last_update_date%TYPE INDEX BY PLS_INTEGER;
last_update_login_v last_update_login_tb;
last_updated_by_v last_updated_by_tb;
last_update_date_v last_update_date_tb;
CURSOR cancel_cursor IS SELECT
nvl(X_req_line_id, requisition_line_id),
nvl(X_cancel_flag, cancel_flag),
nvl(X_cancel_date, cancel_date),
nvl(X_cancel_reason, cancel_reason),
Nvl(X_closed_code, closed_code),
nvl(X_closed_reason, closed_reason),
nvl(X_closed_date, closed_date),
decode(X_cancel_flag,'Y',null,contractor_status), -- Bug 3495679
x_last_update_login,
x_last_updated_by,
x_last_update_date,
decode(X_cancel_flag, 'Y', Decode(SOURCE_TYPE_CODE,'INVENTORY',
Decode(TRANSFERRED_TO_OE_FLAG,'Y',
OE_ORDER_IMPORT_INTEROP_PUB.Get_Cancelled_Qty(
X_order_source_id,
to_char(X_req_header_id),
Nvl(to_char(X_req_line_id), requisition_line_id)),quantity),
quantity - quantity_delivered), quantity_cancelled)
FROM po_requisition_lines
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 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))
*/
--Bug 6849650 - When cancelling from Header level, the cancelled quantity was not updated correctly for
--internal requisition. In case of Cancel done from header leve, line_id will be null and hence cancelled
--quantity was always taken as null as per previous logic. Changed the logic to get the cancelled quantity.
--Also, used bulk collect to improve performance in case of large requisitions.
OPEN cancel_cursor;
last_update_login_v,
last_updated_by_v,
last_update_date_v,
quantity_cancelled_v
LIMIT 2500;
UPDATE po_requisition_lines SET
cancel_flag = cancel_flag_v(indx),
cancel_date = cancel_date_v(indx),
cancel_reason = cancel_reason_v(indx),
closed_code = closed_code_v(indx),
closed_reason = closed_reason_v(indx),
closed_date = closed_date_v(indx),
contractor_status = contractor_status_v(indx),
reqs_in_pool_flag = DECODE(X_terminal_performed,
1,NULL,
reqs_in_pool_flag), --
last_update_login = last_update_login_v(indx),
last_updated_by = last_updated_by_v(indx),
last_update_date = last_update_date_v(indx),
quantity_cancelled = quantity_cancelled_v(indx)
WHERE requisition_line_id = requisition_line_id_v(indx);
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' /* commenting this condition for bug 16240233 to update dist quantity for internal reqs also */
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;
l_deleted_line_list PO_TBL_NUMBER;
l_deleted_dist_list PO_TBL_NUMBER;
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 rl -- Bug 3592153
SET rl.line_location_id = NULL,
--reqs_in_pool_flag = 'Y', -- Bug 2781027 resetting the reqs in pool flag
--Bug 9976204.Set the reqs_in_pool_flag back to Y only if the Req is APPROVED or PRE-APPROVED
rl.reqs_in_pool_flag = (SELECT Decode(rh.authorization_status,'APPROVED','Y','PRE-APPROVED','Y',rl.reqs_in_pool_flag)
FROM po_requisition_headers_all rh
WHERE rh.requisition_header_id = rl.requisition_header_id),
rl.last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
rl.last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
rl.last_update_date = sysdate -- Bug5623016 (updating who column)
WHERE rl.line_location_id in (SELECT pll.line_location_id
FROM po_line_locations_all pll --Bug 8777237: Looking into the base table instead of po_line_locations
WHERE pll.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 rl -- Bug 3592153
SET rl.line_location_id = NULL,
--reqs_in_pool_flag = 'Y', -- Bug 2781027
--Bug 9976204.Set the reqs_in_pool_flag back to Y only if the Req is APPROVED or PRE-APPROVED
rl.reqs_in_pool_flag = (SELECT Decode(rh.authorization_status,'APPROVED','Y','PRE-APPROVED','Y',rl.reqs_in_pool_flag)
FROM po_requisition_headers_all rh
WHERE rh.requisition_header_id = rl.requisition_header_id),
rl.last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
rl.last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
rl.last_update_date = sysdate -- Bug5623016 (upda ting who column)
WHERE rl.line_location_id in (SELECT pll.line_location_id
FROM po_line_locations_all pll--Bug 8777237: Looking into the base table instead of po_line_locations
WHERE pll.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 rl -- Bug 3592153
SET rl.line_location_id = NULL,
-- reqs_in_pool_flag = 'Y', -- Bug 2781027
--Bug 9976204.Set the reqs_in_pool_flag back to Y only if the Req is APPROVED or PRE-APPROVED
rl.reqs_in_pool_flag = (SELECT Decode(rh.authorization_status,'APPROVED','Y','PRE-APPROVED','Y',rl.reqs_in_pool_flag)
FROM po_requisition_headers_all rh
WHERE rh.requisition_header_id = rl.requisition_header_id
-- Bug 15875473
AND rl.line_location_id in (SELECT pll.line_location_id FROM po_line_locations_all pll WHERE
pll.po_line_id = X_entity_id)
-- Bug 15875473
),
rl.last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
rl.last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
rl.last_update_date = sysdate -- Bug5623016 (updating who column)
WHERE rl.line_location_id IN (SELECT pll.line_location_id
FROM po_line_locations_all pll --Bug 8777237: Looking into the base table instead of po_line_locations
WHERE pll.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 rl -- Bug 3592153
SET rl.line_location_id = NULL,
--reqs_in_pool_flag = 'Y', -- Bug 2781027
--Bug 9976204.Set the reqs_in_pool_flag back to Y only if the Req is APPROVED or PRE-APPROVED
rl.reqs_in_pool_flag = (SELECT Decode(rh.authorization_status,'APPROVED','Y','PRE-APPROVED','Y',rl.reqs_in_pool_flag)
FROM po_requisition_headers_all rh
WHERE rh.requisition_header_id = rl.requisition_header_id),
rl.last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
rl.last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
rl.last_update_date = SYSDATE -- Bug5623016 (updating who column)
WHERE rl.line_location_id IN (SELECT pll.line_location_id
FROM po_line_locations_all pll --Bug 8777237: Looking into the base table instead of po_line_locations
WHERE pll.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.po_line_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'
OR DECODE(
(select prh.clm_mipr_type
from po_requisition_headers_all prh
where prh.requisition_header_id = prl.requisition_header_id),
'MIPR_OWN', NVL((select prh.clm_mipr_acknowledged_flag
from po_requisition_headers_all prh
where prh.requisition_header_id = prl.requisition_header_id),'N'),
'MIPR_OTHERS', (select 'Y'
from po_requisition_headers_all prh
where prh.requisition_header_id = prl.requisition_header_id
AND ICX_DATATEMPLATE_PKG.GET_MIPR_ACCEPTANCE_STATUS(
prl.requisition_header_id) = 'ACCEPT'), 'Y') <> 'Y'
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;
PROCEDURE NAME: update_req_for_linked_po_count
DESCRIPTION: It'll update the linked po count of the requsitions those are linked to the
deleted entities(Line, Schedule, Distribution) and mark(insert into GT table) the req line's
Clin(AutoCreate and SoftLinked) and Pslin(SoftLinked) for further processing
in the next step, to update the req pool flag for its whole strucure and send it back to pool
===========================================================================*/
PROCEDURE update_req_for_linked_po_count(
X_entity_id IN PO_TBL_NUMBER,
X_entity IN VARCHAR2)
IS
l_unlinked_reqLine_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
l_api_name CONSTANT VARCHAR(60) := 'po.plsql.PO_REQ_LINES_SV.UPDATE_REQ_FOR_LINKED_PO_COUNT';
|| x_progress, 'Entered update_req_for_linked_po_count Procedure. Entity ' || X_entity );
SELECT requisition_line_id, linked_po_count
BULK COLLECT INTO l_unlinked_reqLine_ids, l_unlinked_po_count
FROM po_requisition_lines_all reqline, TABLE(X_entity_id) entity
WHERE (reqline.clm_info_flag = 'Y' OR reqline.clm_option_indicator = 'O'
OR reqLine.par_draft_id IS NOT NULL) --To process PAR lines without any distribution
-- Dod - Exclude Info Funded Slins
AND NOT EXISTS ( SELECT 1 FROM po_req_distributions_all prd
WHERE prd.info_line_id = reqline.requisition_line_id)
AND reqline.po_line_id = entity.column_value;
SELECT reqdist.requisition_line_id , Count(1)
BULK COLLECT INTO l_non_infofunded_slin_ids, l_non_infofunded_slin_count
FROM po_distributions_all pod, po_req_distributions_all reqdist, TABLE(X_entity_id) entity
WHERE pod.req_distribution_id = reqdist.distribution_id
AND reqdist.info_line_id IS NULL
AND pod.po_distribution_id = entity.column_value
GROUP BY reqdist.requisition_line_id ;
SELECT reqdist.info_line_id , Min(reqdist.requisition_line_id), Count(1)
BULK COLLECT INTO l_info_funded_slin_ids, l_infofunded_slin_grp_ids, l_info_funded_slin_count
FROM po_distributions_all pod, po_req_distributions_all reqdist, TABLE(X_entity_id) entity
WHERE pod.req_distribution_id = reqdist.distribution_id
AND reqdist.info_line_id IS NOT NULL
AND pod.po_distribution_id = entity.column_value
GROUP BY reqdist.info_line_id ;
SELECT reqdist.requisition_line_id , Count(1)
BULK COLLECT INTO l_non_infofunded_slin_ids, l_non_infofunded_slin_count
FROM po_distributions_draft_all pod, po_req_distributions_all reqdist, TABLE(X_entity_id) entity
WHERE pod.req_distribution_id = reqdist.distribution_id
AND reqdist.info_line_id IS NULL
AND pod.po_distribution_id = entity.column_value
AND change_status = 'NEW'
GROUP BY reqdist.requisition_line_id ;
SELECT reqdist.info_line_id , Min(reqdist.requisition_line_id), Count(1)
BULK COLLECT INTO l_info_funded_slin_ids, l_infofunded_slin_grp_ids, l_info_funded_slin_count
FROM po_distributions_draft_all pod, po_req_distributions_all reqdist, TABLE(X_entity_id) entity
WHERE pod.req_distribution_id = reqdist.distribution_id
AND reqdist.info_line_id IS NOT NULL
AND pod.po_distribution_id = entity.column_value
AND change_status = 'NEW'
GROUP BY reqdist.info_line_id ;
UPDATE po_requisition_lines_all
SET linked_po_count = linked_po_count - l_unlinked_po_count(i),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE requisition_line_id = l_unlinked_reqLine_ids(i);
'Linked Po Count Updated.'||SQL%ROWCOUNT);
UPDATE po_requisition_lines_all
SET po_line_id = NULL, line_location_id = NULL
WHERE requisition_line_id = l_unlinked_reqLine_ids(i)
AND linked_po_count = 0;
'Po Line Id and Po Line Loc Id Updated Po Count Update.'||SQL%ROWCOUNT);
SELECT requisition_line_id
BULK COLLECT INTO l_pclin_ids
FROM po_requisition_lines_all rl, TABLE(l_unlinked_reqLine_ids) entity
WHERE rl.requisition_line_id = entity.column_value
AND group_line_id IS NULL
AND Nvl(clm_info_flag,'N') <> 'Y' AND Nvl(clm_option_indicator,'X') <> 'O';
UPDATE po_requisition_lines_all
SET po_line_id = NULL, line_location_id = NULL , linked_po_count = 0
WHERE group_line_id = l_pclin_ids(i);
'Slins for PriceClin ,Po Line Id and Po Line Loc Id Updated Po Count Update.'||SQL%ROWCOUNT);
SELECT group_line_id
BULK COLLECT INTO l_infofd_group_line_ids
FROM po_requisition_lines_all rl, TABLE(l_infofunded_slin_grp_ids) entity
WHERE group_line_id = entity.column_value
GROUP BY group_line_id HAVING Sum(Nvl(linked_po_count,0)) = 0;
UPDATE po_requisition_lines_all
SET po_line_id = NULL, line_location_id = NULL , linked_po_count = 0
WHERE requisition_line_id = l_infofd_group_line_ids(i);
INSERT INTO po_session_gt(
index_char1,
index_num1, -- ReqLineId
index_num2, -- ReqHeadreId
num1, --GroupLineId
num2, -- CLmBaseLineNum
num3, -- LinkedPOCount
num10 -- Strcuture Id (Would be used to identify the structure)
)
SELECT 'UNLINKED_REQUISITIONS',
requisition_line_id,
requisition_header_id,
group_line_id,
clm_base_line_num,
linked_po_count,
requisition_line_id
FROM po_requisition_lines_all l
WHERE requisition_line_id = l_infofd_group_line_ids(i);
'Inserted into GT.'||SQL%ROWCOUNT);
INSERT INTO po_session_gt(
index_char1,
index_num1, -- ReqLineId
index_num2, -- ReqHeadreId
num1, --GroupLineId
num2, -- CLmBaseLineNum
num3, -- LinkedPOCount
num10 -- Strcuture Id (Would be used to identify the structure)
)
SELECT 'UNLINKED_REQUISITIONS',
requisition_line_id,
requisition_header_id,
group_line_id,
clm_base_line_num,
linked_po_count,
requisition_line_id
FROM po_requisition_lines_all l
WHERE requisition_line_id = l_unlinked_reqLine_ids(i)
AND (
(group_line_id IS NULL ) -- All Clins (AutoCreate and SoftLinked)
OR
(group_line_id IS NOT NULL AND po_line_id = -1) -- PSlin for SoftLinked
);
'Inserted into GT.'||SQL%ROWCOUNT);
|| x_progress, 'Completed update_req_for_linked_po_count Procedure. Entity ' || X_entity );
p_procedure_name => 'update_req_for_linked_po_count' || '.' || x_progress
);
END update_req_for_linked_po_count;
PROCEDURE NAME: update_par_draft_line_status
DESCRIPTION: This procedure will update the DRAFT_LINE_STATUS of the PAR lines
to appropriate status once deleted from mod.
===========================================================================*/
PROCEDURE update_par_draft_line_status
(p_reqLine_ids IN PO_TBL_NUMBER,
p_reqHeader_ids IN PO_TBL_NUMBER
)
IS
l_api_name CONSTANT VARCHAR(60) := 'po.plsql.PO_REQ_LINES_SV.update_par_draft_line_status';
|| x_progress, 'Entered Procedure.update_par_draft_line_status');
UPDATE po_lines_draft_all
SET DRAFT_LINE_STATUS = 'COMPLETED'
WHERE (po_line_id, draft_id) IN ( SELECT par_line_id, par_draft_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_reqLine_ids(i)
AND requisition_header_id = p_reqHeader_ids(i)
AND assignment_number IS NULL
);
UPDATE po_lines_draft_all
SET DRAFT_LINE_STATUS = 'ASSIGNED'
WHERE (po_line_id, draft_id) IN ( SELECT par_line_id, par_draft_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_reqLine_ids(i)
AND requisition_header_id = p_reqHeader_ids(i)
AND assignment_number IS NOT NULL
);
|| x_progress, 'Completed update_par_draft_line_status Procedure.');
END update_par_draft_line_status;
PROCEDURE NAME: update_reqs_in_pool_flag
DESCRIPTION: This procedure will update the ReqsInPoolFlag of the requisition lines
by checking its entire clin-slin structure for any linked POs.
===========================================================================*/
PROCEDURE update_reqs_in_pool_flag
IS
l_api_name CONSTANT VARCHAR(60) := 'po.plsql.PO_REQ_LINES_SV.UPDATE_REQS_IN_POOL_FLAG';
l_update_req_pool VARCHAR2(1) := 'N';
|| x_progress, 'Entered Procedure.update_reqs_in_pool_flag');
INSERT INTO po_session_gt gt3 (index_char1, index_num1, index_num2, num1, num2, num3, num10)
SELECT 'UNLINKED_REQUISITIONS', r.requisition_line_id, r.requisition_header_id, r.group_line_id, r.clm_base_line_num, r.linked_po_count, num10
FROM po_requisition_lines_all r,
po_session_gt gt
WHERE
r.requisition_header_id = gt.index_num2
AND gt.index_char1 = 'UNLINKED_REQUISITIONS'
AND ( r.requisition_line_id = gt.index_num1
OR r.group_line_id = gt.index_num1
OR r.clm_base_line_num = gt.index_num1
OR r.requisition_line_id = gt.num1
OR r.group_line_id = gt.num1
OR r.clm_base_line_num = gt.num1
OR r.requisition_line_id = gt.num2
OR r.group_line_id = gt.num2
OR r.clm_base_line_num = gt.num2
)
AND NOT EXISTS (SELECT 1 FROM po_session_gt gt2
WHERE gt2.index_num1 = r.requisition_line_id
AND r.requisition_header_id = gt2.index_num2
AND gt2.index_char1 = 'UNLINKED_REQUISITIONS'
AND gt2.num10 = gt.num10
);
SELECT distinct
index_num1,
index_num2
BULK COLLECT INTO
l_eligible_reqLine_ids,
l_eligible_reqHeader_ids
FROM po_session_gt ogt
WHERE index_char1 = 'UNLINKED_REQUISITIONS'
AND num10 IN (SELECT num10 FROM po_session_gt igt
WHERE igt.index_char1 = 'UNLINKED_REQUISITIONS'
GROUP BY num10 HAVING sum(Nvl(num3,0)) <= 0
) ;
UPDATE po_requisition_lines_all
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 requisition_line_id = l_eligible_reqLine_ids(i)
AND requisition_header_id = l_eligible_reqHeader_ids(i) ;
update_par_draft_line_status(p_reqLine_ids => l_eligible_reqLine_ids,
p_reqHeader_ids => l_eligible_reqHeader_ids );
DELETE po_session_gt WHERE index_char1 = 'UNLINKED_REQUISITIONS';
p_procedure_name => 'update_reqs_in_pool_flag' || '.' || x_progress
);
DELETE po_session_gt WHERE index_char1 = 'UNLINKED_REQUISITIONS';
END update_reqs_in_pool_flag ;