[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_req_po
(
p_api_version IN NUMBER,
p_req_header_id IN PO_TBL_NUMBER,
p_req_line_id IN PO_TBL_NUMBER,
p_po_header_id IN PO_TBL_NUMBER := NULL,
p_po_release_id IN PO_TBL_NUMBER := NULL,
p_po_line_id IN PO_TBL_NUMBER := NULL,
p_po_line_location_id IN PO_TBL_NUMBER := NULL,
p_quantity IN PO_TBL_NUMBER := NULL,
p_secondary_quantity IN PO_TBL_NUMBER := NULL,
p_need_by_date IN PO_TBL_DATE := NULL,
p_ship_to_location_id IN PO_TBL_NUMBER := NULL,
p_sales_order_update_date IN PO_TBL_DATE := NULL,
p_preferred_grade IN PO_TBL_VARCHAR240 := NULL, --
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR(30) := 'UPDATE_REQ_PO';
SAVEPOINT PO_OM_GRP_UPDATE_REQ_PO_SP;
SELECT drop_ship_flag
INTO l_drop_ship_flag
FROM po_line_locations_all
WHERE line_location_id = p_po_line_location_id(i);
SELECT puom.unit_of_measure,
suom.unit_of_measure
INTO l_so_request_unit_of_measure,
l_so_request_secondary_uom
FROM oe_order_lines_all ol,
oe_drop_ship_sources ds,
mtl_units_of_measure puom,
mtl_units_of_measure suom
WHERE ol.line_id=ds.line_id
AND ds.line_location_id= p_po_line_location_id(i)
and ol.order_quantity_uom= puom.uom_code
and ol.ordered_quantity_uom2=suom.uom_code(+);
Select item_id ,
unit_meas_lookup_code ,
secondary_uom
INTO l_item_id,
l_po_request_unit_of_measure,
l_po_request_secondary_uom
from po_lines_all
WHERE po_line_id = p_po_line_id(i);
SELECT unit_meas_lookup_code, secondary_unit_of_measure
INTO l_request_unit_of_measure, l_request_secondary_uom
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id(i);
SELECT round((p_quantity(i) * l_uom_conversion_rate),5) + nvl(sum(nvl(quantity_delivered, 0)), 0)
INTO l_quantity_ordered(i)
FROM po_distributions_all
WHERE line_location_id = p_po_line_location_id(i);
SELECT round((p_secondary_quantity(i) * l_suom_conversion_rate),5) + NVL(SUM(NVL(secondary_quantity_received,0)),0)
INTO l_secondary_quantity_ordered(i)
FROM po_line_locations_all
WHERE line_location_id = p_po_line_location_id(i);
p_sales_order_update_date => p_sales_order_update_date(i) );
SELECT drop_ship_flag
INTO l_drop_ship_flag
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id(i);
SELECT puom.unit_of_measure,
suom.unit_of_measure
INTO l_so_request_unit_of_measure,
l_so_request_secondary_uom
FROM oe_order_lines_all ol,
oe_drop_ship_sources ds,
mtl_units_of_measure puom,
mtl_units_of_measure suom
WHERE ol.line_id=ds.line_id
AND ds.requisition_line_id= p_req_line_id(i)
and ol.order_quantity_uom= puom.uom_code
and ol.ordered_quantity_uom2=suom.uom_code(+);
Select item_id ,
unit_meas_lookup_code ,
secondary_unit_of_measure
INTO l_item_id,
l_request_unit_of_measure,
l_request_secondary_uom
from po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id(i);
SELECT round((p_quantity(i) * l_uom_conversion_rate),5) + nvl(sum(nvl(
quantity_delivered, 0)), 0)
INTO l_quantity_ordered(i)
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id(i);
SELECT round((p_secondary_quantity(i) * l_suom_conversion_rate),5) + NVL(SUM(NVL(
secondary_quantity_received,0)),0)
INTO l_secondary_quantity_ordered(i)
FROM po_requisition_lines_all
WHERE requisition_line_id = p_req_line_id(i);
SELECT org_id
INTO l_document_org_id
FROM po_requisition_headers_all
WHERE requisition_header_id = l_cur_req_header_id;
PO_REQ_DOCUMENT_UPDATE_GRP.update_requisition (
p_api_version => 1.0,
p_req_changes => l_req_changes,
p_update_source => 'OM',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
ROLLBACK TO PO_OM_GRP_UPDATE_REQ_PO_SP;
ROLLBACK TO PO_OM_GRP_UPDATE_REQ_PO_SP;
ROLLBACK TO PO_OM_GRP_UPDATE_REQ_PO_SP;
END update_req_po;
SELECT RELEASE_TYPE,
org_id -- Bug 3362534
INTO l_doc_subtype,
l_document_org_id
from PO_RELEASES_ALL
WHERE po_release_id = p_po_release_id(i);
SELECT TYPE_LOOKUP_CODE,
org_id -- Bug 3362534
INTO l_doc_subtype,
l_document_org_id
from PO_HEADERS_ALL
WHERE po_header_id = p_po_header_id(i);
SELECT approved_date,
drop_ship_flag -- Bug 3639067
INTO l_approved_date,
l_drop_ship_flag
FROM po_line_locations_all
WHERE line_location_id = p_po_line_location_id(i);
IF (l_approved_date IS NULL) THEN -- Delete the shipment.
l_progress := '030';
p_delete_record => PO_DOCUMENT_UPDATE_GRP.G_PARAMETER_YES );
SELECT PO_SESSION_GT_S.nextval
INTO l_session_gt_key
FROM dual;
INSERT INTO po_session_gt
(key, num1, num2)
SELECT l_session_gt_key, PRL.requisition_line_id, PRL.requisition_header_id
FROM po_requisition_lines_all PRL
WHERE PRL.requisition_line_id = p_req_line_id(i)
AND PRL.drop_ship_flag = 'Y';
SELECT GT.num1, GT.num2
BULK COLLECT INTO l_req_line_id, l_req_header_id
FROM po_session_gt GT
WHERE key = l_session_gt_key;
SELECT org_id, authorization_status
INTO l_document_org_id, l_authorization_status
FROM po_releases_all
WHERE po_release_id = p_chg.po_release_id;
SELECT org_id, authorization_status
INTO l_document_org_id, l_authorization_status
FROM po_headers_all
WHERE po_header_id = p_chg.po_header_id;
PO_DOCUMENT_UPDATE_GRP.update_document (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => x_return_status,
p_changes => p_chg,
p_run_submission_checks => l_run_submission_checks,
p_launch_approvals_flag => l_launch_approvals_flag,
p_buyer_id => null,
p_update_source => PO_DOCUMENT_UPDATE_GRP.G_UPDATE_SOURCE_OM,
p_override_date => null,
x_api_errors => l_errors,
p_approval_background_flag => PO_DOCUMENT_UPDATE_GRP.G_PARAMETER_NO
);
UPDATE po_line_locations_all PLL
SET drop_ship_flag = ''
WHERE PLL.line_location_id = p_entity_id_tbl(i);
p_message => '# of updated rows: ' || SQL%ROWCOUNT
);
SELECT PO_SESSION_GT_S.nextval
INTO l_seq_id
FROM DUAL;
INSERT INTO po_session_gt
( key,
num1,
num2 )
VALUES
( l_seq_id,
p_entity_id_tbl(i),
l_order_tbl(i) );
SELECT DECODE (PH.po_header_id,
NULL, 'Y', -- po is deleted
DECODE (PH.closed_code,
'FINALLY CLOSED', 'Y', -- po is finally closed
DECODE (PH.cancel_flag,
'Y', 'Y', -- po is cancelled
'N')))
BULK COLLECT INTO x_purge_allowed_tbl
FROM po_session_gt PSG,
po_headers_all PH,
po_line_locations_all PLL
WHERE PSG.key = l_seq_id
AND PSG.num1 = PLL.line_location_id (+)
AND PLL.po_header_id = PH.po_header_id (+)
ORDER BY PSG.num2 asc;
DELETE FROM po_session_gt WHERE key = l_seq_id;