[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT po_header_id, type_lookup_code
INTO g_po_id, p_doc_type /* The value we were looking for. */
FROM po_headers_all
WHERE segment1 = p_po_code
AND org_id = p_org_id;
PROCEDURE Update_PoLine(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_po_code IN VARCHAR2,
p_org_id IN VARCHAR2,
p_release_id IN VARCHAR2 := NULL,
p_line_num IN NUMBER,
p_doc_type IN VARCHAR2,
p_quantity IN NUMBER,
p_amount IN NUMBER
) AS
/* Business object constants. */
l_api_name CONSTANT VARCHAR2(30) := 'Update_PoLine';
SELECT po_line_id,closed_code
FROM po_lines_all
WHERE po_header_id = g_po_id
AND line_num = p_line_num;
SELECT po_release_id
FROM po_releases_all
WHERE po_header_id = p_header_id
AND release_num = p_release_num
AND org_id = p_org;
l_lltab.delete;
SELECT line_location_id,
quantity,
quantity_received,
quantity_accepted,
quantity_billed,
qty_rcv_tolerance,
qty_rcv_exception_code,
closed_code,
receive_close_tolerance,
NVL(need_by_date, promised_date) process_date,
0 new_quantity,
0 changed
FROM po_line_locations_all
WHERE po_line_id = l_rec.po_line_id
ORDER BY process_date;
SELECT line_location_id,
quantity,
quantity_received,
quantity_accepted,
quantity_billed,
qty_rcv_tolerance,
qty_rcv_exception_code,
closed_code,
receive_close_tolerance,
NVL(promised_date, need_by_date) process_date,
0 new_quantity,
0 changed
FROM po_line_locations_all
WHERE po_line_id = l_rec.po_line_id
ORDER BY process_date DESC;
SELECT line_location_id,
quantity,
quantity_received,
quantity_accepted,
quantity_billed,
qty_rcv_tolerance,
qty_rcv_exception_code,
closed_code,
receive_close_tolerance,
NVL(need_by_date, promised_date) process_date,
0 new_quantity,
0 changed
FROM po_line_locations_all
WHERE po_release_id = l_release_id
AND po_header_id = g_po_id
ORDER BY process_date;
SELECT line_location_id,
quantity,
quantity_received,
quantity_accepted,
quantity_billed,
qty_rcv_tolerance,
qty_rcv_exception_code,
closed_code,
receive_close_tolerance,
NVL(promised_date, need_by_date) process_date,
0 new_quantity,
0 changed
FROM po_line_locations_all
WHERE po_release_id = l_release_id
AND po_header_id = g_po_id
ORDER BY process_date DESC;
SELECT po_distribution_id,
quantity_ordered,
quantity_billed,
quantity_delivered,
amount_billed
FROM po_distributions_all
WHERE line_location_id = p_line_loc_id;
l_disttab.DELETE;
/* Ok time to update the table.. */
ITG_Debug.msg('AAD', 'Update po_distributions_all ...');
UPDATE po_distributions_all
SET quantity_delivered = l_disttab(i).quantity_delivered,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE po_distribution_id = l_disttab(i).po_distribution_id;
UPDATE po_distributions_all
SET quantity_billed = l_disttab(i).quantity_billed,
amount_billed = NVL(amount_billed,0) +
(l_disttab(i).quantity_billed / p_quantity) *
p_total_amount,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE po_distribution_id = l_disttab(i).po_distribution_id;
SELECT requisition_line_id,
quantity,
quantity_delivered
FROM po_requisition_lines_all
WHERE line_location_id IN (
SELECT line_location_id
FROM po_line_locations_all
WHERE po_line_id = l_rec.po_line_id)
ORDER BY need_by_date;
l_reqlltab.DELETE;
/* Time to update the table */
ITG_Debug.msg('ARL', 'Updating po_requisition_lines_all ...');
UPDATE po_requisition_lines_all
SET quantity_delivered = l_reqlltab(i).quantity_delivered,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE requisition_line_id = l_reqlltab(i).requisition_line_id;
ITG_Debug.msg('PRD', 'update po_line_locations_all');
UPDATE po_line_locations_all
SET quantity_received = l_lltab(i).quantity_received,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE line_location_id = l_lltab(i).line_location_id;
UPDATE po_line_locations_all
SET quantity_accepted = l_lltab(i).quantity_accepted,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE line_location_id = l_lltab(i).line_location_id;
ITG_Debug.msg('PID', 'Update po_line_locations_all');
/* Update the table for the quantity billed */
ITG_Debug.msg('PI', 'Update po_line_locations_all ...');
UPDATE po_line_locations_all
SET quantity_billed = l_lltab(i).quantity_billed,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE line_location_id = l_lltab(i).line_location_id;
SAVEPOINT Update_PoLine_PVT;
ROLLBACK TO Update_PoLine_PVT;
ROLLBACK TO Update_PoLine_PVT;
END Update_PoLine;