[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Count(1)
INTO l_count
FROM rcv_transactions_interface
WHERE transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR'
AND source_document_code = 'PO'
AND (po_header_id = x_po_header_id OR (document_num = x_po_num AND org_id = x_Org_id))
AND (nvl(x_Release_id, po_release_id) = po_release_id OR nvl(x_Release_num, release_num) = release_num);
SELECT Count(1)
INTO l_count
FROM rcv_transactions_interface
WHERE transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR'
AND source_document_code = 'PO'
AND ( po_header_id = nvl(x_po_header_id,po_header_id)
OR (document_num = nvl(x_po_num,document_num) AND org_id = nvl(x_Org_id,org_id)) )
AND (nvl(x_Release_id, po_release_id) = po_release_id OR nvl(x_Release_num, release_num) = release_num)
AND ( po_line_id = x_po_line_id OR document_line_num = x_po_line_num
OR item_id = x_item_id OR item_num = x_item_num OR item_description = x_Item_description);
SELECT Count(1)
INTO l_count
FROM rcv_transactions_interface
WHERE transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR'
AND source_document_code = 'PO'
AND ( po_header_id = nvl(x_po_header_id,po_header_id)
OR (document_num = nvl(x_po_num,document_num) AND org_id = nvl(x_Org_id,org_id)) )
AND (nvl(x_Release_id, po_release_id) = po_release_id OR nvl(x_Release_num, release_num) = release_num)
AND ( po_line_id = nvl(x_po_line_id,po_line_id) OR document_line_num = nvl(x_po_line_num,document_line_num)
OR ( item_id = nvl(x_item_id,item_id) OR item_num = nvl(x_item_num,item_num)
OR item_description = nvl(item_description,x_Item_description) ) )
AND (po_line_location_id = x_Shipment_line_id OR document_shipment_line_num = x_shipment_num);
SELECT DISTINCT shipment_header_id
FROM (
SELECT rsl.shipment_header_id, rsl.shipment_line_id, rsl.quantity_received
FROM rcv_transactions rt,
rcv_shipment_lines rsl,
po_line_locations_all poll
WHERE rt.transaction_type IN ('RECEIVE', 'MATCH')
AND rt.shipment_line_id = rsl.shipment_line_id
AND rsl.po_line_location_id = poll.line_location_id
AND rsl.po_header_id = poll.po_header_id
AND rt.po_line_location_id = poll.line_location_id
AND rt.po_header_id = poll.po_header_id
AND poll.match_option = 'R'
AND rt.source_document_code = 'PO'
AND rt.quantity IS NOT NULL
AND rt.po_header_id = p_po_header_id
GROUP BY rsl.shipment_header_id, rsl.shipment_line_id, rsl.quantity_received
HAVING rsl.quantity_received > Sum(Nvl(rt.quantity_billed, 0))
UNION
SELECT rsl.shipment_header_id, rsl.shipment_line_id, rsl.amount_received
FROM rcv_transactions rt,
rcv_shipment_lines rsl,
po_line_locations_all poll
WHERE rt.transaction_type IN ('RECEIVE', 'MATCH')
AND rt.shipment_line_id = rsl.shipment_line_id
AND rsl.po_line_location_id = poll.line_location_id
AND rsl.po_header_id = poll.po_header_id
AND rt.po_line_location_id = poll.line_location_id
AND rt.po_header_id = poll.po_header_id
AND poll.match_option = 'R'
AND rt.source_document_code = 'PO'
AND rt.amount IS NOT NULL
AND rt.po_header_id = p_po_header_id
GROUP BY rsl.shipment_header_id, rsl.shipment_line_id, rsl.amount_received
HAVING rsl.amount_received > Sum(Nvl(rt.amount_billed, 0)) ) ;
SELECT rsh.shipment_header_id,
rsh.receipt_num,
Min(rt.transaction_date)
FROM rcv_transactions rt,
rcv_shipment_headers rsh
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rt.po_header_id = p_po_header_id
AND rt.source_document_code = 'PO'
AND rt.transaction_type IN ('RECEIVE', 'MATCH')
AND rt.transaction_date >= p_date
GROUP BY rsh.shipment_header_id,
rsh.receipt_num;
SELECT rsh.receipt_num, Min(rt.transaction_date)
INTO l_receipt_number, l_receipt_date
FROM rcv_shipment_headers rsh,
rcv_transactions rt
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rt.transaction_type IN ('RECEIVE', 'MATCH')
AND rsh.shipment_header_id = l_shipment_header_id
GROUP BY rsh.receipt_num;
SELECT Nvl(Sum(Nvl(rsl.quantity_received, 0) * NVL(poll.price_override, pol.unit_price) ), 0)
INTO l_receipt_amount
FROM rcv_shipment_lines rsl,
po_line_locations_all poll,
po_lines_all pol
WHERE rsl.po_line_location_id = poll.line_location_id
AND rsl.po_line_id = pol.po_line_id
AND pol.po_line_id = poll.po_line_id
AND rsl.quantity_shipped IS NOT NULL
AND rsl.shipment_header_id = l_shipment_header_id ;
SELECT Nvl(Sum(Nvl(rsl.amount_received, 0)), 0)
INTO l_receipt_amount
FROM rcv_shipment_lines rsl
WHERE rsl.quantity_shipped IS NULL
AND rsl.shipment_header_id = l_shipment_header_id ;
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;
of the selected documents, generate an exception */
l_progress := '050';
SELECT count(*)
INTO l_rti_count
FROM rcv_transactions_interface rti
WHERE rti.po_header_id = l_po_header_id
OR ( (rti.receipt_source_code = 'VENDOR' OR rti.source_document_code = 'PO')
AND EXISTS
( SELECT 1
FROM po_headers_all poh
WHERE type_lookup_code IN('STANDARD', 'BLANKET', 'SCHEDULED')
AND poh.segment1 = rti.document_num
AND poh.org_id = Nvl(rti.org_id, poh.org_id)
AND poh.po_header_id = l_po_header_id )
);
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;
SELECT Sum(Nvl(rsl.quantity_received, 0) * NVL(poll.price_override, pol.unit_price) )
INTO l_receipt_amount
FROM rcv_shipment_lines rsl,
po_line_locations_all poll,
po_lines_all pol
WHERE rsl.po_line_location_id = poll.line_location_id
AND rsl.po_line_id = pol.po_line_id
AND pol.po_line_id = poll.po_line_id
AND rsl.quantity_shipped IS NOT NULL
AND rsl.shipment_header_id = l_shipment_header_id ;
SELECT Sum(Nvl(rsl.amount_received, 0))
INTO l_receipt_amount
FROM rcv_shipment_lines rsl
WHERE rsl.quantity_shipped IS NULL
AND rsl.shipment_header_id = l_shipment_header_id ;
SELECT po_multi_mod_val_results_s.nextval
INTO l_multi_mod_val_result_id
FROM dual;