[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(shipment_header_id) -- if we ever have 2 shipments with the same combo
INTO p_header_record.header_record.receipt_header_id
FROM rcv_shipment_headers
WHERE NVL(vendor_site_id, -9999) = NVL(p_header_record.header_record.vendor_site_id, -9999)
AND vendor_id = p_header_record.header_record.vendor_id
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id
AND shipment_num = p_header_record.header_record.shipment_num
AND shipped_date >= ADD_MONTHS(p_header_record.header_record.shipped_date, -12);
SELECT MAX(rti.to_organization_code)
INTO x_to_organization_code
FROM rcv_transactions_interface rti
WHERE rti.header_interface_id = x_header_interface_id;
/* ksareddy RVCTP performance fix 2481798 - select from mtl_parameters instead
SELECT MAX(ORG.ORGANIZATION_CODE)
INTO X_TO_ORGANIZATION_CODE
FROM RCV_TRANSACTIONS_INTERFACE RTI,
ORG_ORGANIZATION_DEFINITIONS ORG
WHERE RTI.HEADER_INTERFACE_ID = X_HEADER_INTERFACE_ID
AND ORG.ORGANIZATION_ID = RTI.TO_ORGANIZATION_ID;
SELECT MAX(mtl.organization_code)
INTO x_to_organization_code
FROM rcv_transactions_interface rti,
mtl_parameters mtl
WHERE rti.header_interface_id = x_header_interface_id
AND mtl.organization_id = rti.to_organization_id;
SELECT MAX(org.organization_code)
INTO x_to_organization_code
FROM rcv_transactions_interface rti,
hr_locations hl,
mtl_parameters org
-- BugFix 5219284, replaced org_organization_definitions with mtl_parameters for better performance.
WHERE rti.header_interface_id = x_header_interface_id
AND ( rti.ship_to_location_code = hl.location_code
OR rti.ship_to_location_id = hl.location_id)
AND hl.inventory_organization_id = org.organization_id;
SELECT MAX(rti.shipment_header_id),MAX(rti.shipment_num),MAX(rti.document_num)
INTO x_shipment_header_id,x_shipment_num,x_document_num
FROM rcv_transactions_interface rti
WHERE rti.header_interface_id = x_header_interface_id;
SELECT MAX(rsh.shipment_header_id)
INTO x_shipment_header_id
FROM rcv_shipment_headers rsh
WHERE rsh.shipment_num = x_shipment_num;
SELECT MAX(rsl.to_organization_id)
INTO x_to_organization_id /* Bug#3909973 - (2) */
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = x_shipment_header_id
AND (x_document_num is null or x_document_num = rsl.line_num);
SELECT primary_unit_of_measure
--NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
--NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
INTO x_cascaded_table(n).primary_unit_of_measure
--x_cascaded_table(n).use_mtl_lot,
--x_cascaded_table(n).use_mtl_serial
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
SELECT muom.uom_code
INTO x_cascaded_table(n).uom_code
FROM mtl_units_of_measure muom
WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
SELECT (next_receipt_num + 1)
INTO p_header_record.header_record.receipt_num
FROM rcv_parameters
WHERE organization_id = p_header_record.header_record.ship_to_organization_id
FOR UPDATE OF next_receipt_num;
SELECT COUNT(*)
INTO l_count
FROM rcv_shipment_headers
WHERE receipt_num = p_header_record.header_record.receipt_num
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
UPDATE rcv_parameters
SET next_receipt_num = p_header_record.header_record.receipt_num
WHERE organization_id = p_header_record.header_record.ship_to_organization_id;
PROCEDURE default_last_update_info(
p_header_record IN OUT NOCOPY rcv_roi_preprocessor.header_rec_type
) IS
BEGIN
/* last_update_date */
IF p_header_record.header_record.last_update_date IS NULL THEN
p_header_record.header_record.last_update_date := x_sysdate;
asn_debug.put_line('defaulting last update date');
/* last_updated_by */
IF p_header_record.header_record.last_updated_by IS NULL THEN
p_header_record.header_record.last_updated_by := fnd_global.user_id;
asn_debug.put_line('defaulting last update by');
/* last_update_login */
IF p_header_record.header_record.last_update_login IS NULL THEN
p_header_record.header_record.last_update_login := fnd_global.login_id;
asn_debug.put_line('defaulting last update login');
END default_last_update_info;
SELECT rcv_shipment_headers_s.NEXTVAL
INTO p_header_record.header_record.receipt_header_id
FROM SYS.DUAL;
SELECT COUNT(*)
INTO v_count
FROM rcv_transactions_interface rti
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
AND ( rti.auto_transact_code IN('RECEIVE', 'DELIVER')
OR rti.transaction_type IN('RECEIVE', 'DELIVER'));
SELECT user_defined_receipt_num_code
INTO v_rcv_type
FROM rcv_parameters
WHERE organization_id = p_header_record.header_record.ship_to_organization_id;
SELECT MAX(hr_locations_all.location_id),
COUNT(*)
INTO x_location_id,
x_count
FROM hr_locations_all
WHERE hr_locations_all.inventory_organization_id = p_header_record.header_record.ship_to_organization_id
AND NVL(hr_locations_all.inactive_date, x_sysdate + 1) > x_sysdate
AND NVL(hr_locations_all.receiving_site_flag, 'N') = 'Y';
SELECT MAX(rti.po_header_id),
MAX(document_num)
INTO x_po_header_id,
x_document_num
FROM rcv_transactions_interface rti
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id;
SELECT po_header_id
INTO x_po_header_id
FROM po_headers
WHERE segment1 = x_document_num
AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED');
SELECT COUNT(*)
INTO temp_count
FROM oe_drop_ship_sources
WHERE po_header_id = x_po_header_id;
SELECT COUNT(*)
INTO x_count
FROM rcv_shipment_headers
WHERE rcv_shipment_headers.receipt_num = p_header_record.header_record.receipt_num
AND ship_to_org_id = p_header_record.header_record.ship_to_organization_id;
SELECT COUNT(*)
INTO x_count
FROM rcv_transactions_interface rti,
rcv_headers_interface rhi
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
AND rhi.header_interface_id = rti.header_interface_id
AND ( ( rti.to_organization_code IS NOT NULL
AND rti.to_organization_code <> p_header_record.header_record.ship_to_organization_code)
OR ( rti.to_organization_id IS NOT NULL
AND rti.to_organization_id <> p_header_record.header_record.ship_to_organization_id)
);
SELECT COUNT(*)
INTO x_count
FROM rcv_transactions_interface rti,
hr_locations hl,
mtl_parameters org
-- BugFix 5219284, replaced org_organization_definitions with mtl_parameters for better performance.
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
AND rti.to_organization_code IS NULL
AND rti.to_organization_id IS NULL
AND rti.ship_to_location_id IS NOT NULL
AND rti.ship_to_location_id = hl.location_id
AND hl.inventory_organization_id = org.organization_id
AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
SELECT COUNT(*)
INTO x_count
FROM rcv_transactions_interface rti,
hr_locations hl,
mtl_parameters org
-- BugFix 5219284, replaced org_organization_definitions with mtl_parameters for better performance.
WHERE rti.header_interface_id = p_header_record.header_record.header_interface_id
AND rti.to_organization_code IS NULL
AND rti.to_organization_id IS NULL
AND rti.ship_to_location_code IS NOT NULL
AND rti.ship_to_location_code = hl.location_code
AND hl.inventory_organization_id = org.organization_id
AND org.organization_code <> p_header_record.header_record.ship_to_organization_code;
SELECT po_line_id,
po_line_location_id po_shipment_line_id
FROM rcv_transactions_interface
WHERE header_interface_id = p_header_record.header_record.header_interface_id;
select shipping_control
from po_headers_all
where po_header_id = (select po_header_id
from rcv_transactions_interface
where header_interface_id = p_header_record.header_record.header_interface_id
and rownum=1); --Bugfix 5844039
SELECT NVL(MAX(inventory_item_id), -9999)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_cascaded_table(n).item_id;
SELECT NVL(MAX(inventory_item_id), -9999)
INTO x_inventory_item
FROM mtl_system_items
WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
AND inventory_item_id = x_cascaded_table(n).item_id;
SELECT stock_enabled_flag,
inventory_item_flag
INTO l_stock_enabled_flag,
l_inventory_item_flag
FROM mtl_system_items
WHERE organization_id = x_cascaded_table(n).to_organization_id
AND inventory_item_id = x_cascaded_table(n).item_id;
* check below. Similarly changed the select statement and the
* check for nvl(max(item_id),0).
*/
SELECT NVL(MAX(organization_id), -9999)
INTO x_organization_id
FROM mtl_system_items
WHERE inventory_item_id = x_cascaded_table(n).item_id
AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
SELECT NVL(MAX(item_id), -9999)
INTO x_item_id_po
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id
AND item_id = x_cascaded_table(n).item_id;
SELECT NVL(MAX(item_id), -9999)
INTO x_item_id_po
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id
AND item_id = x_cascaded_table(n).item_id;
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_cascaded_table(n).substitute_item_id
AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_system_items
WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
AND inventory_item_id = x_cascaded_table(n).substitute_item_id
AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
/* SELECT NVL(MIN(allow_substitute_receipts_flag),'N')
INTO x_allow_sub_flag
FROM (SELECT allow_substitute_receipts_flag
FROM mtl_system_items
WHERE inventory_item_id = (SELECT item_id
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id)
AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id)
UNION ALL
SELECT allow_substitute_receipts_flag
FROM po_line_locations
WHERE line_location_id = x_cascaded_table(n).po_line_location_id);
SELECT NVL(MIN(allow_substitute_receipts_flag),'N')
INTO x_allow_sub_flag
FROM (SELECT msi.allow_substitute_receipts_flag
FROM mtl_system_items msi,
po_lines_all pl
WHERE msi.inventory_item_id = pl.item_id
AND pl.po_line_id = x_cascaded_table(n).po_line_id
AND msi.organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id)
UNION ALL
SELECT allow_substitute_receipts_flag
FROM po_line_locations
WHERE line_location_id = x_cascaded_table(n).po_line_location_id);
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_cascaded_table(n).substitute_item_id
AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id);
SELECT NVL(MAX(vendor_id), 0)
INTO x_vendor_id
FROM po_vendors
WHERE vendor_id = x_cascaded_table(n).vendor_id
AND allow_substitute_receipts_flag = 'Y';
/* SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_related_items
WHERE inventory_item_id = (SELECT item_id
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id)
AND related_item_id = x_cascaded_table(n).substitute_item_id
AND relationship_type_id = 2; -- substitute items
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_related_items mri,
po_lines_all pl
WHERE mri.inventory_item_id = pl.item_id
AND pl.po_line_id = x_cascaded_table(n).po_line_id
AND mri.related_item_id = x_cascaded_table(n).substitute_item_id
AND mri.relationship_type_id = 2; -- substitute items
/* SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_related_items
WHERE related_item_id = (SELECT item_id
FROM po_lines
WHERE po_line_id = x_cascaded_table(n).po_line_id)
AND inventory_item_id = x_cascaded_table(n).substitute_item_id
AND reciprocal_flag = 'Y'
AND relationship_type_id = 2;
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_related_items mri,
po_lines_all pl
WHERE mri.related_item_id = pl.item_id
AND pl.po_line_id = x_cascaded_table(n).po_line_id
AND mri.inventory_item_id = x_cascaded_table(n).substitute_item_id
AND mri.reciprocal_flag = 'Y'
AND mri.relationship_type_id = 2;
SELECT DECODE(msi.revision_qty_control_code,
1, 'N',
2, 'Y',
'N'
)
INTO x_revision_control_flag
FROM mtl_system_items msi
WHERE inventory_item_id = l_active_item_id
AND organization_id = x_cascaded_table(n).to_organization_id;
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_item_revisions
WHERE inventory_item_id = l_active_item_id
AND organization_id = NVL(x_cascaded_table(n).to_organization_id, organization_id)
AND revision = x_cascaded_table(n).item_revision;