[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT need_by_date,
ship_to_location_id,
ship_to_organization_id,
consigned_flag
FROM po_line_locations_all
WHERE po_line_id = p_po_line_id_to_compare;
SELECT 'ADD'
INTO l_line_action_tbl(i)
FROM po_lines_all
WHERE po_header_id = p_add_to_po_header_id
AND line_num = p_po_line_number_tbl(i);
SELECT index_num1 -- requisition line ID
FROM po_session_gt
WHERE key = c_key
AND index_num2 = c_po_line_num
AND index_num1 <> c_current_req_line_id;
INSERT INTO po_session_gt(
key, -- unique key
index_num1, -- req line ID
index_num2 -- PO line num
)
VALUES (l_key, p_req_line_id_tbl(i), p_po_line_number_tbl(i));
SELECT progress_payment_flag
INTO l_progress_payment_flag
FROM po_doc_style_headers
WHERE style_id = p_style_id;
SELECT NVL(
(SELECT 'Y'
FROM dual
WHERE EXISTS(
-- Select all doc builder requisition lines that have the same PO line
-- number as the current line in the loop.
SELECT 'doc builder lines with same PO line number'
FROM po_session_gt POSGT2
WHERE POSGT2.index_num1 <> POSGT.index_num1 -- Not the current line
AND POSGT2.index_num2 = POSGT.index_num2 -- Same PO line number
)
OR EXISTS(
-- Select all PO lines that have the same PO line number as the
-- current line in the loop.
SELECT 'PO lines with same PO line number'
FROM po_lines_all
WHERE po_header_id = p_add_to_po_header_id
AND line_num = POSGT.index_num2 -- Same PO line number
)),
'N' -- NVL to 'N' if no other req/PO lines with same PO line number
)
BULK COLLECT INTO l_line_combined_flag_tbl
FROM po_session_gt POSGT
WHERE key = l_key;
SELECT po_line_id
INTO l_po_line_id_to_compare
FROM po_lines_all
WHERE po_header_id = p_add_to_po_header_id
AND line_num = l_po_line_num;
DELETE FROM po_session_gt
WHERE key = l_key;
DELETE FROM po_session_gt
WHERE key = l_key;
SELECT PRL.item_id,
PRL.need_by_date,
PRL.destination_organization_id,
PRL.deliver_to_location_id
INTO l_item_id,
l_req_line_delivery_info.need_by_date,
l_req_line_delivery_info.ship_to_organization_id,
l_req_line_delivery_info.ship_to_location_id
FROM po_requisition_lines_all PRL
WHERE PRL.requisition_line_id = p_req_line_id;
SELECT PRL.item_id,
PRL.item_description,
PRL.item_revision,
PRL.order_type_lookup_code,
PRL.purchase_basis,
PRL.matching_basis,
PRL.preferred_grade,
PRL.unit_meas_lookup_code,
PRL.transaction_reason_code,
DECODE(
PRL.document_type_code,
'CONTRACT',
PRL.blanket_po_header_id,
NULL
), -- contract ID
DECODE(
PRL.document_type_code,
'CONTRACT',
NULL,
PRL.blanket_po_header_id
), -- source document ID
DECODE(
PRL.document_type_code,
'CONTRACT',
NULL,
SRC_DOC_LINE.po_line_id
), -- source document line ID
NULL, -- cancel flag N/A for req line
NULL, -- closed code N/A for req line
PRL.supplier_ref_number
INTO l_req_line_info
FROM po_requisition_lines_all PRL,
po_lines_all SRC_DOC_LINE
WHERE PRL.requisition_line_id = p_req_line_id
AND SRC_DOC_LINE.po_header_id(+) = PRL.blanket_po_header_id
AND SRC_DOC_LINE.line_num(+) = PRL.blanket_po_line_num;
SELECT item_id,
item_description,
item_revision,
order_type_lookup_code,
purchase_basis,
matching_basis,
preferred_grade,
unit_meas_lookup_code,
transaction_reason_code,
contract_id,
from_header_id, -- source document ID
from_line_id, -- source document line ID
cancel_flag,
closed_code,
supplier_ref_number
INTO l_po_line_info
FROM po_lines_all
WHERE po_line_id = p_po_line_id;
SELECT requisition_header_id
INTO l_req_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id_tbl(i);
INSERT INTO po_session_gt
( key
, num1
, num2
)
SELECT DISTINCT
l_key
, prl.requisition_line_id
, pol.line_num
FROM po_requisition_lines_all prl
, po_lines_all pol
, po_line_locations_all pll
, po_lines_all src_line
WHERE pol.po_header_id = p_add_to_po_header_id
AND pll.po_line_id = pol.po_line_id
AND prl.requisition_line_id = p_req_line_id_tbl(i)
AND decode ( prl.item_id
, pol.item_id, 1, 0) = 1
AND ((prl.item_id IS NOT NULL OR pol.item_id IS NOT NULL)
OR decode(
prl.item_description,
pol.item_description, 1, 0) = 1)
AND decode ( prl.item_revision
, pol.item_revision, 1, 0) = 1
AND decode ( prl.line_type_id
, pol.line_type_id, 1, 0) = 1
AND decode ( prl.preferred_grade
, pol.preferred_grade, 1, 0) = 1
AND decode ( prl.unit_meas_lookup_code
, pol.unit_meas_lookup_code, 1, 0) = 1
AND decode ( prl.transaction_reason_code
, pol.transaction_reason_code, 1, 0) = 1
AND decode ( prl.supplier_ref_number
, pol.supplier_ref_number, 1, 0) = 1
AND ( ( l_need_by_grouping_profile = 'N' )
OR ( decode ( trunc(prl.need_by_date,'MI')
, trunc(pll.need_by_date,'MI'), 1, 0) = 1 ) )
AND ( ( l_ship_to_grouping_profile = 'N' )
OR ( decode ( prl.destination_organization_id
, pll.ship_to_organization_id, 1, 0) = 1 ) )
AND ( ( prl.document_type_code <> 'CONTRACT' )
OR ( decode ( prl.blanket_po_header_id
, pol.contract_id, 1, 0) = 1 ) )
AND src_line.po_header_id (+) = prl.blanket_po_header_id
AND src_line.line_num (+) = prl.blanket_po_line_num
AND ( ( p_builder_agreement_id IS NOT NULL )
OR ( ( decode ( prl.blanket_po_header_id
, pol.from_header_id, 1, 0) = 1 )
AND ( decode ( src_line.po_line_id
, pol.from_line_id, 1, 0) = 1 ) ) )
AND nvl(pol.cancel_flag, 'N') <> 'Y'
AND nvl(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND nvl(pll.consigned_flag, 'N') = p_consigned_flag_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_key
RETURNING num1, num2
BULK COLLECT INTO x_req_line_id_tbl, x_po_line_num_tbl;
SELECT nvl(max(line_num), 0)
INTO x_max_po_line_num
FROM po_lines_all
WHERE po_header_id = p_po_header_id;
SELECT item_id
INTO l_item_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id_tbl(i);