[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** Debug: Needed to add all the columns selected in the distributions cursor
** so the definition of the shipments and distributions cursors were
** identical.
*/
/* 1887728 - IN ASN closed for receiving PO's were also being
received . In the Enter Receipts form the closed for
receiving PO's can be received only if Include Closed PO
profile option is set . Modified the cursors shipments,
count shipments, distributions,count distributions
to restrict the shipments and distributions based on the
profile option.
The fnd_profile.get_specfic(x,y,z,w) returns the value
of profile option starting from user. If there is no value
at the user value ,then the value at responsibility
level is returned and so on. */
x_include_closed_po VARCHAR2(1); -- Bug 1887728
SELECT pll.line_location_id,
pll.unit_meas_lookup_code,
pll.unit_of_measure_class,
NVL(pll.promised_date, pll.need_by_date) promised_date,
pll.ship_to_organization_id,
pll.quantity quantity_ordered,
pll.quantity_shipped,
pll.receipt_days_exception_code,
pll.qty_rcv_tolerance,
pll.qty_rcv_exception_code,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
NVL(pll.price_override, pl.unit_price) unit_price,
pll.match_option, -- 1845702
pl.category_id,
pl.item_description,
pl.po_line_id,
ph.currency_code,
ph.rate_type, -- 1845702
0 po_distribution_id,
0 code_combination_id,
0 req_distribution_id,
0 deliver_to_location_id,
0 deliver_to_person_id,
ph.rate_date rate_date, --1845702
ph.rate rate, --1845702
'' destination_type_code,
0 destination_organization_id,
'' destination_subinventory,
0 wip_entity_id,
0 wip_operation_seq_num,
0 wip_resource_seq_num,
0 wip_repetitive_schedule_id,
0 wip_line_id,
0 bom_resource_id,
'' ussgl_transaction_code,
pll.ship_to_location_id,
NVL(pll.enforce_ship_to_location_code, 'NONE') enforce_ship_to_location_code,
pl.item_id
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = header_id
AND pll.po_header_id = header_id
AND pl.line_num = NVL(v_po_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
AND pll.po_line_id = pl.po_line_id
AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'))
ORDER BY NVL(pll.promised_date, pll.need_by_date);
SELECT COUNT(*)
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = header_id
AND pll.po_header_id = header_id
AND pl.line_num = NVL(v_po_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
AND pll.po_line_id = pl.po_line_id
AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'));
***** was driving through PO_LINE_LOCATIONS_ALL. Modified the Select
***** statement so that it will drive through PO_HEADERS_ALL
***** followed by PO_LINES_ALL which is followed by PO_LINE_LOCATIONS_ALL
***** so that there is an improvement in performance.
*****/
CURSOR distributions(
header_id NUMBER,
v_item_id NUMBER,
v_po_line_num NUMBER,
v_po_release_id NUMBER,
v_shipment_num NUMBER,
v_distribution_num NUMBER,
v_ship_to_org_id NUMBER,
v_ship_to_location_id NUMBER,
v_vendor_product_num VARCHAR2
) IS
SELECT pll.line_location_id,
pll.unit_meas_lookup_code,
pll.unit_of_measure_class,
NVL(pll.promised_date, pll.need_by_date) promised_date,
pll.ship_to_organization_id,
pll.quantity quantity_ordered,
pll.quantity_shipped,
pll.receipt_days_exception_code,
pll.qty_rcv_tolerance,
pll.qty_rcv_exception_code,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
NVL(pll.price_override, pl.unit_price) unit_price,
pll.match_option, -- 1845702
pl.category_id,
pl.item_description,
pl.po_line_id,
ph.currency_code,
ph.rate_type, -- 1845702
pod.po_distribution_id,
pod.code_combination_id,
pod.req_distribution_id,
pod.deliver_to_location_id,
pod.deliver_to_person_id,
pod.rate_date,
pod.rate,
pod.destination_type_code,
pod.destination_organization_id,
pod.destination_subinventory,
pod.wip_entity_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.wip_repetitive_schedule_id,
pod.wip_line_id,
pod.bom_resource_id,
pod.ussgl_transaction_code,
pll.ship_to_location_id,
NVL(pll.enforce_ship_to_location_code, 'NONE') enforce_ship_to_location_code,
pl.item_id
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = header_id
AND pl.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND pod.line_location_id = pll.line_location_id
AND pl.line_num = NVL(v_po_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
AND pod.distribution_num = NVL(v_distribution_num, pod.distribution_num)
AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'))
ORDER BY NVL(pll.promised_date, pll.need_by_date);
***** Modified the Select statement so that it will drive through
***** PO_HEADERS_ALL followed by PO_LINES_ALL which is followed by
***** PO_LINE_LOCATIONS_ALL which in turn is followed by
***** PO_DISTRIBUTIONS_ALL so that there is an improvement in
***** Performance
*****/
CURSOR count_distributions(
header_id NUMBER,
v_item_id NUMBER,
v_po_line_num NUMBER,
v_po_release_id NUMBER,
v_shipment_num NUMBER,
v_distribution_num NUMBER,
v_ship_to_org_id NUMBER,
v_ship_to_location_id NUMBER,
v_vendor_product_num VARCHAR2
) IS
SELECT COUNT(*)
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = header_id
AND pl.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND pod.line_location_id = pll.line_location_id
AND pl.line_num = NVL(v_po_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(v_po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(v_shipment_num, pll.shipment_num)
AND pod.distribution_num = NVL(v_distribution_num, pod.distribution_num)
AND NVL(pl.item_id, 0) = NVL(v_item_id, NVL(pl.item_id, 0)) -- v_item_id could be null
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND ( ( NVL(x_include_closed_po, 'N') = 'Y'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED')
OR ( NVL(x_include_closed_po, 'N') = 'N'
AND (NVL(pll.closed_code, 'OPEN') NOT IN('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING'))))
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
AND pll.ship_to_organization_id = NVL(v_ship_to_org_id, pll.ship_to_organization_id)
AND pll.ship_to_location_id = NVL(v_ship_to_location_id, pll.ship_to_location_id)
AND NVL(pl.vendor_product_num, '-999') = NVL(v_vendor_product_num, NVL(pl.vendor_product_num, '-999'));
insert_into_table BOOLEAN := FALSE;
SELECT MAX(org.organization_code)
INTO x_to_organization_code
FROM hr_locations hl,
mtl_parameters org -- Bugfix 5217098
WHERE x_cascaded_table(n).ship_to_location_code = hl.location_code
AND hl.inventory_organization_id = org.organization_id;
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 line_num
INTO temp_cascaded_table(current_n).document_line_num
FROM po_lines
WHERE po_line_id = temp_cascaded_table(current_n).po_line_id;
SELECT shipment_num
INTO temp_cascaded_table(current_n).document_shipment_line_num
FROM po_line_locations
WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
SELECT distribution_num
INTO temp_cascaded_table(current_n).document_distribution_num
FROM po_distributions
WHERE po_distribution_id = temp_cascaded_table(current_n).po_distribution_id;
SELECT NVL(po_line_locations.qty_rcv_exception_code, 'NONE')
INTO x_qty_rcv_exception_code
FROM po_line_locations
WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
asn_debug.put_line('Need to insert into po_interface_errors');
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE(i);
asn_debug.put_line('Need to insert a row into po_interface_errors');
SELECT COUNT(*)
INTO x_temp_count
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(temp_cascaded_table(current_n).document_shipment_line_num, pll.shipment_num)
AND pll.po_line_id = pl.po_line_id;
SELECT NVL(pl.item_id, 0),
NVL(pll.approved_flag, 'N'),
NVL(pll.cancel_flag, 'N'),
NVL(pll.closed_code, 'OPEN'),
pll.shipment_type,
pll.ship_to_organization_id,
pll.ship_to_location_id,
NVL(pl.vendor_product_num, '-999')
INTO x_item_id,
x_approved_flag,
x_cancel_flag,
x_closed_code,
x_shipment_type,
x_ship_to_organization_id,
x_ship_to_location_id,
x_vendor_product_num
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(temp_cascaded_table(current_n).document_shipment_line_num, pll.shipment_num)
AND pll.po_line_id = pl.po_line_id;
SELECT COUNT(*)
INTO x_temp_count
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pll.line_location_id = pod.line_location_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(temp_cascaded_table(current_n).document_shipment_line_num, pll.shipment_num)
AND pll.po_line_id = pl.po_line_id
AND pod.distribution_num = NVL(temp_cascaded_table(current_n).document_distribution_num, pod.distribution_num);
SELECT NVL(pl.item_id, 0),
NVL(pll.approved_flag, 'N'),
NVL(pll.cancel_flag, 'N'),
NVL(pll.closed_code, 'OPEN'),
pll.shipment_type,
pll.ship_to_organization_id,
pll.ship_to_location_id,
NVL(pl.vendor_product_num, '-999')
INTO x_item_id,
x_approved_flag,
x_cancel_flag,
x_closed_code,
x_shipment_type,
x_ship_to_organization_id,
x_ship_to_location_id,
x_vendor_product_num
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = temp_cascaded_table(current_n).po_header_id
AND pll.po_header_id = ph.po_header_id
AND pll.line_location_id = pod.line_location_id
AND pl.line_num = NVL(temp_cascaded_table(current_n).document_line_num, pl.line_num)
AND NVL(pll.po_release_id, 0) = NVL(temp_cascaded_table(current_n).po_release_id, NVL(pll.po_release_id, 0))
AND pll.shipment_num = NVL(temp_cascaded_table(current_n).document_shipment_line_num, pll.shipment_num)
AND pll.po_line_id = pl.po_line_id
AND pod.distribution_num = NVL(temp_cascaded_table(current_n).document_distribution_num, pod.distribution_num);
temp_cascaded_table.DELETE(i);
SELECT muom.unit_of_measure
INTO temp_cascaded_table(current_n).primary_unit_of_measure
FROM mtl_units_of_measure muom,
mtl_units_of_measure tuom
WHERE tuom.unit_of_measure = temp_cascaded_table(current_n).unit_of_measure
AND tuom.uom_class = muom.uom_class
AND muom.base_uom_flag = 'Y';
SELECT primary_unit_of_measure
INTO temp_cascaded_table(current_n).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = temp_cascaded_table(current_n).item_id
AND mtl_system_items.organization_id = temp_cascaded_table(current_n).to_organization_id;
insert_into_table := FALSE;
insert_into_table := TRUE;
insert_into_table := TRUE;
IF rows_fetched = x_record_count THEN -- last row needs to be inserted anyway
-- so that the row can be used based on qty tolerance
-- checks
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Quantity is less then 0 but last record');
insert_into_table := TRUE;
insert_into_table := FALSE;
IF insert_into_table THEN
IF (x_first_trans) THEN
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('First Time ' || TO_CHAR(current_n));
Later reset cum_qty = cum_qty + primary_qty for each insert.Since we always
copy the previous record this should work pretty well */
temp_cascaded_table(current_n).vendor_cum_shipped_qty := temp_cascaded_table(current_n).vendor_cum_shipped_qty
- convert_into_correct_qty(x_bkp_qty,
temp_cascaded_table(current_n).unit_of_measure,
temp_cascaded_table(current_n).item_id,
temp_cascaded_table(current_n).primary_unit_of_measure
);
SELECT set_of_books_id
INTO rcv_transactions_interface_sv.x_set_of_books_id
FROM financials_system_parameters;
SELECT set_of_books_id
INTO x_sob_id
FROM financials_system_parameters;
SELECT NVL(MAX(hre.full_name), 'notfound')
INTO x_full_name
FROM hr_employees_current_v hre
WHERE ( hre.inactive_date IS NULL
OR hre.inactive_date > SYSDATE)
AND hre.employee_id = temp_cascaded_table(current_n).deliver_to_person_id;
SELECT ph.revision_num,
pl.line_num,
pl.item_description,
pll.tax_code_id,
pll.po_release_id,
pll.ship_to_location_id,
pll.ship_to_organization_id,
pll.shipment_num,
pll.receiving_routing_id,
pll.country_of_origin_code
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND ph.po_header_id = v_header_id
AND pl.po_line_id = v_line_id
AND pll.line_location_id = v_line_location_id
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED');
SELECT vendor_site_id
INTO x_vendor_site_id
FROM po_headers
WHERE po_header_id = x_cascaded_table(n).po_header_id
AND vendor_id = x_cascaded_table(n).vendor_id;
SELECT vendor_site_code
INTO x_vendor_site_code
FROM po_vendor_sites
WHERE vendor_site_id = x_vendor_site_id
AND vendor_id = x_cascaded_table(n).vendor_id;
SELECT NAME
INTO x_tax_name
FROM ap_tax_codes
WHERE ap_tax_codes.tax_id = default_po_info.tax_code_id;
SELECT set_of_books_id
INTO x_sob_id
FROM financials_system_parameters;
SELECT set_of_books_id
INTO x_sob_id
FROM financials_system_parameters;
SELECT NVL(pll.allow_substitute_receipts_flag, 'N')
INTO x_allow_substitute_receipts
FROM po_line_locations pll
WHERE pll.line_location_id = x_cascaded_table(n).po_line_location_id;
SELECT MAX(location_id)
INTO x_location_id_record.location_id
FROM hr_locations
WHERE location_code = x_location_id_record.location_code;
SELECT NVL(MAX(ml.inventory_location_id), -999)
INTO x_locator_id_record.locator_id
FROM mtl_item_locations_kfv ml
WHERE ml.concatenated_segments = x_locator_id_record.LOCATOR
AND ( ml.disable_date > SYSDATE
OR ml.disable_date IS NULL)
AND NVL(ml.subinventory_code, 'z') = NVL(x_locator_id_record.subinventory, 'z')
AND x_locator_id_record.to_organization_id = ml.organization_id;
SELECT MAX(routing_header_id)
INTO x_routing_header_id_record.routing_header_id
FROM rcv_routing_headers
WHERE routing_name = x_routing_header_id_record.routing_code;
SELECT MAX(routing_step_id)
INTO x_routing_step_id_record.routing_step_id
FROM rcv_routing_steps
WHERE step_name = x_routing_step_id_record.routing_step;
SELECT MAX(reason_id)
INTO x_reason_id_record.reason_id
FROM mtl_transaction_reasons
WHERE reason_name = x_reason_id_record.reason_name;
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 = x_item_revision_record.item_id
AND organization_id = x_item_revision_record.to_organization_id;
SELECT item_revision
INTO x_item_revision_record.item_revision
FROM po_lines
WHERE po_lines.po_line_id = x_item_revision_record.po_line_id;
SELECT COUNT(*)
INTO x_number_of_inv_dest
FROM po_distributions pd
WHERE pd.line_location_id = x_item_revision_record.po_line_location_id
AND pd.destination_type_code = 'INVENTORY';
SELECT COUNT(*)
INTO x_uom_count
FROM mtl_item_uoms_view
WHERE organization_id = v_cascaded_table(n).to_organization_id
AND inventory_item_id(+) = v_cascaded_table(n).substitute_item_id
AND unit_of_measure = v_cascaded_table(n).unit_of_measure;
SELECT MAX(primary_unit_of_measure)
INTO x_primary_uom
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = v_cascaded_table(n).item_id
AND mtl_system_items.organization_id = v_cascaded_table(n).to_organization_id;
SELECT MAX(po_header_id)
INTO x_po_header_id_record.po_header_id
FROM po_headers
WHERE segment1 = x_po_header_id_record.document_num
AND type_lookup_code IN('STANDARD', 'BLANKET', 'PLANNED'); -- Could be a quotation with same number
SELECT MIN(inventory_item_id),
MIN(primary_unit_of_measure),
MIN(lot_control_code), -- bug 608353
MIN(serial_number_control_code)
INTO x_item_id_record.item_id,
x_item_id_record.primary_unit_of_measure,
x_item_id_record.use_mtl_lot, -- bug 608353
x_item_id_record.use_mtl_serial
FROM mtl_item_flexfields
WHERE item_number = x_item_id_record.item_num
AND organization_id = x_item_id_record.to_organization_id;
SELECT MIN(inventory_item_id),
MIN(primary_unit_of_measure),
MIN(lot_control_code), -- bug 608353
MIN(serial_number_control_code)
INTO x_item_id_record.item_id,
x_item_id_record.primary_unit_of_measure,
x_item_id_record.use_mtl_lot,
x_item_id_record.use_mtl_serial
FROM mtl_item_flexfields
WHERE item_number = x_item_id_record.vendor_item_num
AND organization_id = x_item_id_record.to_organization_id;
SELECT MAX(inventory_item_id)
INTO x_sub_item_id_record.substitute_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = x_sub_item_id_record.substitute_item_num;
SELECT MAX(inventory_item_id)
INTO x_sub_item_id_record.substitute_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = x_sub_item_id_record.vendor_item_num;
SELECT po_line_id,
item_id
INTO x_po_line_id_record.po_line_id,
x_po_line_id_record.item_id
FROM po_lines
WHERE po_header_id = x_po_line_id_record.po_header_id
AND line_num = x_po_line_id_record.document_line_num;
SELECT inventory_organization_id
INTO x_organization_id
FROM hr_locations
WHERE location_id = p_hr_location_id;