[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, g_po_startup_value.update_date
, g_po_startup_value.inv_status
, g_po_startup_value.po_status
, g_po_startup_value.qa_status
, g_po_startup_value.wip_status
, g_po_startup_value.pa_status
, g_po_startup_value.oe_status
, g_po_startup_value.override_routing
, g_po_startup_value.transaction_mode
, g_po_startup_value.receipt_traveller
, g_po_startup_value.receipt_num_code
, g_po_startup_value.receipt_num_type
, g_po_startup_value.po_num_type
, g_po_startup_value.coa_id
, g_po_startup_value.allow_express
, g_po_startup_value.allow_cascade
, g_po_startup_value.org_locator_control
, g_po_startup_value.negative_inv_receipt_code
, g_po_startup_value.gl_set_of_bks_id
, g_po_startup_value.blind_receiving_flag
, g_po_startup_value.allow_unordered
, g_po_startup_value.display_inverse_rate
, g_po_startup_value.currency_code
, g_po_startup_value.project_reference_enabled
, g_po_startup_value.project_control_level
, g_po_startup_value.effectivity_control
, g_po_startup_value.employee_id
, g_po_startup_value.wms_install_status
, g_po_startup_value.wms_purchased
, l_message
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO g_rcv_global_var.transaction_header_id
FROM DUAL;
PROCEDURE insert_mtlt(p_mtlt_rec mtl_transaction_lots_temp%ROWTYPE) IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, transaction_quantity
, primary_quantity
, lot_number
, lot_expiration_date
, ERROR_CODE
, serial_transaction_temp_id
, group_header_id
, put_away_rule_id
, pick_rule_id
, description
, vendor_id
, supplier_lot_number
, territory_code
, --country_of_origin,
origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, --kill_date,
best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_name
, SECONDARY_QUANTITY --OPM Convergence
, SECONDARY_UNIT_OF_MEASURE --OPM Convergence
)
VALUES (
p_mtlt_rec.transaction_temp_id
, p_mtlt_rec.last_update_date
, p_mtlt_rec.last_updated_by
, p_mtlt_rec.creation_date
, p_mtlt_rec.created_by
, p_mtlt_rec.last_update_login
, p_mtlt_rec.request_id
, p_mtlt_rec.program_application_id
, p_mtlt_rec.program_id
, p_mtlt_rec.program_update_date
, p_mtlt_rec.transaction_quantity
, p_mtlt_rec.primary_quantity
, p_mtlt_rec.lot_number
, p_mtlt_rec.lot_expiration_date
, p_mtlt_rec.ERROR_CODE
, p_mtlt_rec.serial_transaction_temp_id
, p_mtlt_rec.group_header_id
, p_mtlt_rec.put_away_rule_id
, p_mtlt_rec.pick_rule_id
, p_mtlt_rec.description
, p_mtlt_rec.vendor_id
, p_mtlt_rec.supplier_lot_number
, p_mtlt_rec.territory_code
, --p_mtlt_rec.country_of_origin,
p_mtlt_rec.origination_date
, p_mtlt_rec.date_code
, p_mtlt_rec.grade_code
, p_mtlt_rec.change_date
, p_mtlt_rec.maturity_date
, p_mtlt_rec.status_id
, p_mtlt_rec.retest_date
, p_mtlt_rec.age
, p_mtlt_rec.item_size
, p_mtlt_rec.color
, p_mtlt_rec.volume
, p_mtlt_rec.volume_uom
, p_mtlt_rec.place_of_origin
, --p_mtlt_rec.kill_date,
p_mtlt_rec.best_by_date
, p_mtlt_rec.LENGTH
, p_mtlt_rec.length_uom
, p_mtlt_rec.recycled_content
, p_mtlt_rec.thickness
, p_mtlt_rec.thickness_uom
, p_mtlt_rec.width
, p_mtlt_rec.width_uom
, p_mtlt_rec.curl_wrinkle_fold
, p_mtlt_rec.lot_attribute_category
, p_mtlt_rec.c_attribute1
, p_mtlt_rec.c_attribute2
, p_mtlt_rec.c_attribute3
, p_mtlt_rec.c_attribute4
, p_mtlt_rec.c_attribute5
, p_mtlt_rec.c_attribute6
, p_mtlt_rec.c_attribute7
, p_mtlt_rec.c_attribute8
, p_mtlt_rec.c_attribute9
, p_mtlt_rec.c_attribute10
, p_mtlt_rec.c_attribute11
, p_mtlt_rec.c_attribute12
, p_mtlt_rec.c_attribute13
, p_mtlt_rec.c_attribute14
, p_mtlt_rec.c_attribute15
, p_mtlt_rec.c_attribute16
, p_mtlt_rec.c_attribute17
, p_mtlt_rec.c_attribute18
, p_mtlt_rec.c_attribute19
, p_mtlt_rec.c_attribute20
, p_mtlt_rec.d_attribute1
, p_mtlt_rec.d_attribute2
, p_mtlt_rec.d_attribute3
, p_mtlt_rec.d_attribute4
, p_mtlt_rec.d_attribute5
, p_mtlt_rec.d_attribute6
, p_mtlt_rec.d_attribute7
, p_mtlt_rec.d_attribute8
, p_mtlt_rec.d_attribute9
, p_mtlt_rec.d_attribute10
, p_mtlt_rec.n_attribute1
, p_mtlt_rec.n_attribute2
, p_mtlt_rec.n_attribute3
, p_mtlt_rec.n_attribute4
, p_mtlt_rec.n_attribute5
, p_mtlt_rec.n_attribute6
, p_mtlt_rec.n_attribute7
, p_mtlt_rec.n_attribute8
, p_mtlt_rec.n_attribute9
, p_mtlt_rec.n_attribute10
, p_mtlt_rec.vendor_name
, p_mtlt_rec.SECONDARY_QUANTITY --OPM Convergence
, p_mtlt_rec.SECONDARY_UNIT_OF_MEASURE --OPM Convergence
);
END insert_mtlt;
SELECT wlc.inventory_item_id
FROM wms_lpn_contents wlc, wms_license_plate_numbers wln
WHERE wln.lpn_id = wlc.parent_lpn_id
AND lpn_id IN(SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id);
SELECT wlc.inventory_item_id
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id
IN ( SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id);
SELECT 'N'
INTO l_lot_ser_flag
FROM po_requisition_headers prh, po_requisition_lines prl, rcv_shipment_lines rsl, mtl_system_items msi1, mtl_system_items msi2
WHERE prh.requisition_header_id = p_req_header_id
AND prl.requisition_header_id = prh.requisition_header_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND rsl.item_id = msi1.inventory_item_id
AND rsl.item_id = l_item_id
AND msi1.organization_id = rsl.from_organization_id
AND(
(NVL(msi1.lot_control_code, 1) = 1
AND NVL(msi2.lot_control_code, 1) = 2)
OR(NVL(msi1.serial_number_control_code, 1) IN(1, 6)
AND NVL(msi2.serial_number_control_code, 1) IN(2, 5))
OR(NVL(msi1.revision_qty_control_code, 1) = 1
AND NVL(msi2.revision_qty_control_code, 1) = 2)
)
AND rsl.item_id = msi2.inventory_item_id
AND msi2.organization_id = rsl.to_organization_id
AND ROWNUM = 1;
SELECT 'N'
INTO l_lot_ser_flag
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh, mtl_system_items msi1, mtl_system_items msi2
WHERE rsh.shipment_header_id = p_shipment_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.item_id = msi1.inventory_item_id
AND msi1.organization_id = rsl.from_organization_id
AND rsl.item_id = l_item_id
AND(
(NVL(msi1.lot_control_code, 1) = 1
AND NVL(msi2.lot_control_code, 1) = 2)
OR(NVL(msi1.serial_number_control_code, 1) IN(1, 6)
AND NVL(msi2.serial_number_control_code, 1) IN(2, 5))
OR(NVL(msi1.revision_qty_control_code, 1) = 1
AND NVL(msi2.revision_qty_control_code, 1) = 2)
)
AND rsl.item_id = msi2.inventory_item_id
AND msi2.organization_id = rsl.to_organization_id
AND ROWNUM = 1;
PROCEDURE insert_msnt(p_msnt_rec mtl_serial_numbers_temp%ROWTYPE) IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, vendor_serial_number
, vendor_lot_number
, fm_serial_number
, to_serial_number
, serial_prefix
, ERROR_CODE
, group_header_id
, parent_serial_number
, end_item_unit_number
, serial_attribute_category
, territory_code
, --country_of_origin,
origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
)
VALUES (
p_msnt_rec.transaction_temp_id
, p_msnt_rec.last_update_date
, p_msnt_rec.last_updated_by
, p_msnt_rec.creation_date
, p_msnt_rec.created_by
, p_msnt_rec.last_update_login
, p_msnt_rec.request_id
, p_msnt_rec.program_application_id
, p_msnt_rec.program_id
, p_msnt_rec.program_update_date
, p_msnt_rec.vendor_serial_number
, p_msnt_rec.vendor_lot_number
, p_msnt_rec.fm_serial_number
, p_msnt_rec.to_serial_number
, p_msnt_rec.serial_prefix
, p_msnt_rec.ERROR_CODE
, p_msnt_rec.group_header_id
, p_msnt_rec.parent_serial_number
, p_msnt_rec.end_item_unit_number
, p_msnt_rec.serial_attribute_category
, p_msnt_rec.territory_code
, --p_msnt_rec.country_of_origin,
p_msnt_rec.origination_date
, p_msnt_rec.c_attribute1
, p_msnt_rec.c_attribute2
, p_msnt_rec.c_attribute3
, p_msnt_rec.c_attribute4
, p_msnt_rec.c_attribute5
, p_msnt_rec.c_attribute6
, p_msnt_rec.c_attribute7
, p_msnt_rec.c_attribute8
, p_msnt_rec.c_attribute9
, p_msnt_rec.c_attribute10
, p_msnt_rec.c_attribute11
, p_msnt_rec.c_attribute12
, p_msnt_rec.c_attribute13
, p_msnt_rec.c_attribute14
, p_msnt_rec.c_attribute15
, p_msnt_rec.c_attribute16
, p_msnt_rec.c_attribute17
, p_msnt_rec.c_attribute18
, p_msnt_rec.c_attribute19
, p_msnt_rec.c_attribute20
, p_msnt_rec.d_attribute1
, p_msnt_rec.d_attribute2
, p_msnt_rec.d_attribute3
, p_msnt_rec.d_attribute4
, p_msnt_rec.d_attribute5
, p_msnt_rec.d_attribute6
, p_msnt_rec.d_attribute7
, p_msnt_rec.d_attribute8
, p_msnt_rec.d_attribute9
, p_msnt_rec.d_attribute10
, p_msnt_rec.n_attribute1
, p_msnt_rec.n_attribute2
, p_msnt_rec.n_attribute3
, p_msnt_rec.n_attribute4
, p_msnt_rec.n_attribute5
, p_msnt_rec.n_attribute6
, p_msnt_rec.n_attribute7
, p_msnt_rec.n_attribute8
, p_msnt_rec.n_attribute9
, p_msnt_rec.n_attribute10
, p_msnt_rec.status_id
, p_msnt_rec.time_since_new
, p_msnt_rec.cycles_since_new
, p_msnt_rec.time_since_overhaul
, p_msnt_rec.cycles_since_overhaul
, p_msnt_rec.time_since_repair
, p_msnt_rec.cycles_since_repair
, p_msnt_rec.time_since_visit
, p_msnt_rec.cycles_since_visit
, p_msnt_rec.time_since_mark
, p_msnt_rec.cycles_since_mark
, p_msnt_rec.number_of_repairs
);
END insert_msnt;
SELECT *
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_original_tid;
insert_msnt(l_msnt_rec); -- insert one line with new to-ser-number and new txn_id
UPDATE mtl_serial_numbers_temp
SET fm_serial_number = l_new_ser
WHERE transaction_temp_id = l_transaction_temp_id
AND fm_serial_number = l_from_ser
AND to_serial_number = l_to_ser;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_new_transaction_temp_id
WHERE transaction_temp_id = l_transaction_temp_id
AND fm_serial_number = l_from_ser
AND to_serial_number = l_to_ser;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_new_transaction_temp_id
WHERE transaction_temp_id = l_transaction_temp_id
AND fm_serial_number = l_from_ser
AND to_serial_number = l_to_ser;
SELECT ROWID
, transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, transaction_quantity
, secondary_quantity --invconv kkillams
, primary_quantity
, lot_number
, lot_expiration_date
, ERROR_CODE
, serial_transaction_temp_id
, group_header_id
, put_away_rule_id
, pick_rule_id
, description
, vendor_id
, supplier_lot_number
, territory_code
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_name
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_original_tid
ORDER BY DECODE(
inv_rcv_common_apis.g_order_lots_by
, inv_rcv_common_apis.g_order_lots_by_exp_date, lot_expiration_date
, inv_rcv_common_apis.g_order_lots_by_creation_date, creation_date
, lot_expiration_date
);
, l_mtlt_rec.last_update_date
, l_mtlt_rec.last_updated_by
, l_mtlt_rec.creation_date
, l_mtlt_rec.created_by
, l_mtlt_rec.last_update_login
, l_mtlt_rec.request_id
, l_mtlt_rec.program_application_id
, l_mtlt_rec.program_id
, l_mtlt_rec.program_update_date
, l_mtlt_rec.transaction_quantity
, l_mtlt_rec.secondary_quantity --invconv kkillams
, l_mtlt_rec.primary_quantity
, l_mtlt_rec.lot_number
, l_mtlt_rec.lot_expiration_date
, l_mtlt_rec.ERROR_CODE
, l_mtlt_rec.serial_transaction_temp_id
, l_mtlt_rec.group_header_id
, l_mtlt_rec.put_away_rule_id
, l_mtlt_rec.pick_rule_id
, l_mtlt_rec.description
, l_mtlt_rec.vendor_id
, l_mtlt_rec.supplier_lot_number
, l_mtlt_rec.territory_code
, l_mtlt_rec.origination_date
, l_mtlt_rec.date_code
, l_mtlt_rec.grade_code
, l_mtlt_rec.change_date
, l_mtlt_rec.maturity_date
, l_mtlt_rec.status_id
, l_mtlt_rec.retest_date
, l_mtlt_rec.age
, l_mtlt_rec.item_size
, l_mtlt_rec.color
, l_mtlt_rec.volume
, l_mtlt_rec.volume_uom
, l_mtlt_rec.place_of_origin
, l_mtlt_rec.best_by_date
, l_mtlt_rec.LENGTH
, l_mtlt_rec.length_uom
, l_mtlt_rec.recycled_content
, l_mtlt_rec.thickness
, l_mtlt_rec.thickness_uom
, l_mtlt_rec.width
, l_mtlt_rec.width_uom
, l_mtlt_rec.curl_wrinkle_fold
, l_mtlt_rec.lot_attribute_category
, l_mtlt_rec.c_attribute1
, l_mtlt_rec.c_attribute2
, l_mtlt_rec.c_attribute3
, l_mtlt_rec.c_attribute4
, l_mtlt_rec.c_attribute5
, l_mtlt_rec.c_attribute6
, l_mtlt_rec.c_attribute7
, l_mtlt_rec.c_attribute8
, l_mtlt_rec.c_attribute9
, l_mtlt_rec.c_attribute10
, l_mtlt_rec.c_attribute11
, l_mtlt_rec.c_attribute12
, l_mtlt_rec.c_attribute13
, l_mtlt_rec.c_attribute14
, l_mtlt_rec.c_attribute15
, l_mtlt_rec.c_attribute16
, l_mtlt_rec.c_attribute17
, l_mtlt_rec.c_attribute18
, l_mtlt_rec.c_attribute19
, l_mtlt_rec.c_attribute20
, l_mtlt_rec.d_attribute1
, l_mtlt_rec.d_attribute2
, l_mtlt_rec.d_attribute3
, l_mtlt_rec.d_attribute4
, l_mtlt_rec.d_attribute5
, l_mtlt_rec.d_attribute6
, l_mtlt_rec.d_attribute7
, l_mtlt_rec.d_attribute8
, l_mtlt_rec.d_attribute9
, l_mtlt_rec.d_attribute10
, l_mtlt_rec.n_attribute1
, l_mtlt_rec.n_attribute2
, l_mtlt_rec.n_attribute3
, l_mtlt_rec.n_attribute4
, l_mtlt_rec.n_attribute5
, l_mtlt_rec.n_attribute6
, l_mtlt_rec.n_attribute7
, l_mtlt_rec.n_attribute8
, l_mtlt_rec.n_attribute9
, l_mtlt_rec.n_attribute10
, l_mtlt_rec.vendor_name;
print_debug('insert_mtlt',1);
insert_mtlt(l_mtlt_rec); -- insert one line with new quantity and new txn_id
print_debug('Update 1 mtl_transaction_lots_temp',1);
UPDATE mtl_transaction_lots_temp
SET primary_quantity = l_primary_quantity
, transaction_quantity = l_transaction_quantity
, secondary_quantity = l_sec_transaction_quantity
WHERE transaction_temp_id = l_transaction_temp_id
AND lot_number = l_lot_number
AND ROWID = l_rowid;
print_debug('Update 2 mtl_transaction_lots_temp',1);
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = l_new_transaction_temp_id
WHERE transaction_temp_id = l_transaction_temp_id
AND lot_number = l_lot_number
AND ROWID = l_rowid;
print_debug('Update 3 mtl_transaction_lots_temp',1);
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = l_new_transaction_temp_id
WHERE transaction_temp_id = l_transaction_temp_id
AND lot_number = l_lot_number
AND ROWID = l_rowid;
SELECT ROWID
, transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, transaction_quantity
, secondary_quantity --invconv kkillams
, primary_quantity
, lot_number
, lot_expiration_date
, ERROR_CODE
, serial_transaction_temp_id
, group_header_id
, put_away_rule_id
, pick_rule_id
, description
, vendor_id
, supplier_lot_number
, territory_code
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, status_id
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_name
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_original_tid
ORDER BY DECODE(
inv_rcv_common_apis.g_order_lots_by
, inv_rcv_common_apis.g_order_lots_by_exp_date, lot_expiration_date
, inv_rcv_common_apis.g_order_lots_by_creation_date, creation_date
, lot_expiration_date
);
, l_mtlt_rec.last_update_date
, l_mtlt_rec.last_updated_by
, l_mtlt_rec.creation_date
, l_mtlt_rec.created_by
, l_mtlt_rec.last_update_login
, l_mtlt_rec.request_id
, l_mtlt_rec.program_application_id
, l_mtlt_rec.program_id
, l_mtlt_rec.program_update_date
, l_mtlt_rec.transaction_quantity
, l_mtlt_rec.secondary_quantity --invconv kkillams
, l_mtlt_rec.primary_quantity
, l_mtlt_rec.lot_number
, l_mtlt_rec.lot_expiration_date
, l_mtlt_rec.ERROR_CODE
, l_mtlt_rec.serial_transaction_temp_id
, l_mtlt_rec.group_header_id
, l_mtlt_rec.put_away_rule_id
, l_mtlt_rec.pick_rule_id
, l_mtlt_rec.description
, l_mtlt_rec.vendor_id
, l_mtlt_rec.supplier_lot_number
, l_mtlt_rec.territory_code
, l_mtlt_rec.origination_date
, l_mtlt_rec.date_code
, l_mtlt_rec.grade_code
, l_mtlt_rec.change_date
, l_mtlt_rec.maturity_date
, l_mtlt_rec.status_id
, l_mtlt_rec.retest_date
, l_mtlt_rec.age
, l_mtlt_rec.item_size
, l_mtlt_rec.color
, l_mtlt_rec.volume
, l_mtlt_rec.volume_uom
, l_mtlt_rec.place_of_origin
, l_mtlt_rec.best_by_date
, l_mtlt_rec.LENGTH
, l_mtlt_rec.length_uom
, l_mtlt_rec.recycled_content
, l_mtlt_rec.thickness
, l_mtlt_rec.thickness_uom
, l_mtlt_rec.width
, l_mtlt_rec.width_uom
, l_mtlt_rec.curl_wrinkle_fold
, l_mtlt_rec.lot_attribute_category
, l_mtlt_rec.c_attribute1
, l_mtlt_rec.c_attribute2
, l_mtlt_rec.c_attribute3
, l_mtlt_rec.c_attribute4
, l_mtlt_rec.c_attribute5
, l_mtlt_rec.c_attribute6
, l_mtlt_rec.c_attribute7
, l_mtlt_rec.c_attribute8
, l_mtlt_rec.c_attribute9
, l_mtlt_rec.c_attribute10
, l_mtlt_rec.c_attribute11
, l_mtlt_rec.c_attribute12
, l_mtlt_rec.c_attribute13
, l_mtlt_rec.c_attribute14
, l_mtlt_rec.c_attribute15
, l_mtlt_rec.c_attribute16
, l_mtlt_rec.c_attribute17
, l_mtlt_rec.c_attribute18
, l_mtlt_rec.c_attribute19
, l_mtlt_rec.c_attribute20
, l_mtlt_rec.d_attribute1
, l_mtlt_rec.d_attribute2
, l_mtlt_rec.d_attribute3
, l_mtlt_rec.d_attribute4
, l_mtlt_rec.d_attribute5
, l_mtlt_rec.d_attribute6
, l_mtlt_rec.d_attribute7
, l_mtlt_rec.d_attribute8
, l_mtlt_rec.d_attribute9
, l_mtlt_rec.d_attribute10
, l_mtlt_rec.n_attribute1
, l_mtlt_rec.n_attribute2
, l_mtlt_rec.n_attribute3
, l_mtlt_rec.n_attribute4
, l_mtlt_rec.n_attribute5
, l_mtlt_rec.n_attribute6
, l_mtlt_rec.n_attribute7
, l_mtlt_rec.n_attribute8
, l_mtlt_rec.n_attribute9
, l_mtlt_rec.n_attribute10
, l_mtlt_rec.vendor_name;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_mtlt_rec.serial_transaction_temp_id
FROM DUAL;
insert_mtlt(l_mtlt_rec); -- insert one line with new quantity and new txn_id
UPDATE mtl_transaction_lots_temp
SET primary_quantity = l_primary_quantity
, transaction_quantity = l_transaction_quantity
, secondary_quantity = l_sec_transaction_quantity
WHERE transaction_temp_id = l_transaction_temp_id
AND lot_number = l_lot_number
AND ROWID = l_rowid;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = l_new_transaction_temp_id
WHERE transaction_temp_id = l_transaction_temp_id
AND lot_number = l_lot_number
AND ROWID = l_rowid;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = l_new_transaction_temp_id
WHERE transaction_temp_id = l_transaction_temp_id
AND lot_number = l_lot_number
AND ROWID = l_rowid;
UPDATE rcv_parameters
SET next_receipt_num = next_receipt_num + 1
WHERE organization_id = p_organization_id;
SELECT TO_CHAR(next_receipt_num)
INTO x_receipt_num
FROM rcv_parameters
WHERE organization_id = p_organization_id;
SELECT 1
INTO l_receipt_exists
FROM rcv_shipment_headers rsh
WHERE receipt_num = x_receipt_num
AND ship_to_org_id = p_organization_id;
RAISE; -- ? multi row selected
SELECT to_char(next_receipt_num + 1)
INTO l_temp_rcpt_num
FROM rcv_parameters
WHERE organization_id = p_organization_id
FOR UPDATE OF next_receipt_num;
SELECT COUNT(1)
INTO l_receipt_exists
FROM rcv_shipment_headers rsh
WHERE rsh.receipt_num = l_temp_rcpt_num
AND rsh.ship_to_org_id = p_organization_id ;
UPDATE rcv_parameters
SET next_receipt_num = l_temp_rcpt_num
WHERE organization_id = p_organization_id ;
g_lot_status_tb.DELETE;
inv_rcv_std_rcpt_apis.g_rcpt_match_table_gross.DELETE;
inv_rcv_std_deliver_apis.g_rcvtxn_match_table_gross.DELETE;
fnd_msg_pub.delete_msg;
inv_label_pvt1.g_rcv_label_print_rec_tb.DELETE;
, x_update_date OUT NOCOPY DATE
, x_inv_status OUT NOCOPY VARCHAR2
, x_po_status OUT NOCOPY VARCHAR2
, x_qa_status OUT NOCOPY VARCHAR2
, x_wip_status OUT NOCOPY VARCHAR2
, x_pa_status OUT NOCOPY VARCHAR2
, x_oe_status OUT NOCOPY VARCHAR2
, x_override_routing OUT NOCOPY VARCHAR2
, x_transaction_mode OUT NOCOPY VARCHAR2
, x_receipt_traveller OUT NOCOPY VARCHAR2
, x_receipt_num_code OUT NOCOPY VARCHAR2
, x_receipt_num_type OUT NOCOPY VARCHAR2
, x_po_num_type OUT NOCOPY VARCHAR2
, x_coa_id OUT NOCOPY NUMBER
, x_allow_express OUT NOCOPY VARCHAR2
, x_allow_cascade OUT NOCOPY VARCHAR2
, x_org_locator_control OUT NOCOPY NUMBER
, x_negative_inv_receipt_code OUT NOCOPY NUMBER
, x_gl_set_of_bks_id OUT NOCOPY VARCHAR2
, x_blind_receiving_flag OUT NOCOPY VARCHAR2
, x_allow_unordered OUT NOCOPY VARCHAR2
, x_display_inverse_rate OUT NOCOPY VARCHAR2
, x_currency_code OUT NOCOPY VARCHAR2
, x_project_reference_enabled OUT NOCOPY NUMBER
, x_project_control_level OUT NOCOPY NUMBER
, x_effectivity_control OUT NOCOPY NUMBER
, x_employee_id OUT NOCOPY NUMBER
, x_wms_install_status OUT NOCOPY VARCHAR2
, x_wms_purchased OUT NOCOPY VARCHAR2
, x_message OUT NOCOPY VARCHAR2
) IS
l_org_id NUMBER := p_org_id;
SELECT ood.set_of_books_id
, sob.currency_code
INTO x_sob_id
, x_currency_code
FROM org_organization_definitions ood, gl_sets_of_books sob
WHERE organization_id = p_org_id
AND sob.set_of_books_id = ood.set_of_books_id;
SELECT TO_NUMBER(hoi.org_information1)
, sob.currency_code
INTO x_sob_id
, x_currency_code
FROM hr_organization_information hoi, gl_sets_of_books sob
WHERE hoi.organization_id = p_org_id
AND (hoi.org_information_context || '') = 'Accounting Information'
AND sob.set_of_books_id = to_number(hoi.org_information1);
SELECT location_code
INTO x_org_location
FROM hr_locations hrl, hr_organization_units hou
WHERE hou.location_id = hrl.location_id
AND hou.organization_id = p_org_id;
, x_update_date
, x_coa_id
, x_org_locator_control
, x_negative_inv_receipt_code
, x_gl_set_of_bks_id
, x_blind_receiving_flag
, x_allow_unordered
);
SELECT user_defined_receipt_num_code
, manual_receipt_num_type
INTO x_receipt_num_code
, x_receipt_num_type
FROM rcv_parameters
WHERE organization_id = p_org_id;
SELECT ood.set_of_books_id
, sob.currency_code
INTO g_po_startup_value.sob_id
, g_po_startup_value.currency_code
FROM org_organization_definitions ood, gl_sets_of_books sob
WHERE organization_id = p_organization_id
AND sob.set_of_books_id = ood.set_of_books_id;
SELECT TO_NUMBER(hoi.org_information1)
, sob.currency_code
INTO g_po_startup_value.sob_id
, g_po_startup_value.currency_code
FROM hr_organization_information hoi, gl_sets_of_books sob
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information'
AND sob.set_of_books_id = to_number(hoi.org_information1);
SELECT location_code
INTO x_org_location
FROM hr_locations hrl, hr_organization_units hou
WHERE hou.location_id = hrl.location_id
AND hou.organization_id = p_organization_id;
SELECT NVL(stock_locator_control_code, 1)
INTO x_org_locator_control
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT NVL(manual_po_num_type, 'ALPHANUMERIC')
INTO x_manual_po_num_type
FROM po_system_parameters
WHERE ROWNUM = 1;
/* Bug 3812507: Changing the select query in the cursors po_ll_routing_cur
and pod_dest_context_cur to improve performance */
CURSOR po_ll_routing_cur IS
-- SELECT Nvl(poll.receiving_routing_id, 1) Value 1 should not be selected
-- in case of Nvl, otherwise the
-- searching mechanism will not
-- go to item/org level.
SELECT poll.receiving_routing_id
-- p_po_release_id is null and p_po_line_id is null
FROM po_line_locations poll, po_lines pol
WHERE pol.po_header_id = p_po_header_id
AND poll.po_line_id = pol.po_line_id
AND p_po_release_id is NULL
AND p_po_line_id is null
AND (pol.item_id = p_item_id OR (p_item_id IS NULL
AND pol.item_id IS NULL AND pol.item_description = p_item_desc))
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN')NOT IN ('CLOSED','CLOSED FOR RECEIVING','FINALLY CLOSED')
/*Fix for bug #4755862*/
AND poll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
UNION ALL
SELECT poll.receiving_routing_id
-- p_po_release_id is null and p_po_line_id is not null
FROM po_line_locations poll, po_lines pol
WHERE poll.po_header_id = p_po_header_id
AND poll.po_line_id = pol.po_line_id
AND p_po_release_id is NULL
AND (p_po_line_id is not null AND poll.po_line_id = p_po_line_id)
AND (pol.ITEM_ID = p_item_id OR (p_item_id IS NULL
AND pol.item_id IS NULL AND pol.item_description = p_item_desc ))
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') NOT IN ('CLOSED','CLOSED FOR RECEIVING','FINALLY CLOSED')
/*Fix for bug #4755862*/
AND poll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
UNION ALL
SELECT poll.receiving_routing_id
-- p_po_release_id is not null
FROM po_line_locations poll, po_lines pol
WHERE poll.po_header_id = p_po_header_id
AND poll.po_line_id = pol.po_line_id
AND (p_po_release_id is NOT NULL AND poll.po_release_id = p_po_release_id)
AND (p_po_line_id is null or poll.po_line_id = p_po_line_id)
AND (pol.item_id = p_item_id OR (p_item_id IS NULL
AND pol.item_id IS NULL AND pol.item_description = p_item_desc))
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') NOT IN ('CLOSED','CLOSED FOR RECEIVING','FINALLY CLOSED')
/*Fix for bug #4755862*/
AND poll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED');
SELECT poll.receiving_routing_id
FROM po_line_locations poll, po_lines pol
WHERE poll.po_header_id = p_po_header_id
AND NVL(poll.po_release_id, -1) = NVL(p_po_release_id, NVL(poll.po_release_id, -1))
AND NVL(poll.po_line_id, -1) = NVL(p_po_line_id, NVL(poll.po_line_id, -1))
--AND pol.item_id = p_item_id
AND (pol.item_id = p_item_id
OR (p_item_id IS NULL
AND pol.item_id IS NULL
AND pol.item_description = p_item_desc
)
)
AND pol.po_line_id = poll.po_line_id
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED');
SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
-- p_po_release_id is null and p_po_line_id is null
FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
WHERE POL.PO_HEADER_ID = p_po_header_id
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID) -- Bug 8242448
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND p_po_release_id is NULL
AND p_po_line_id is NULL
AND (POL.ITEM_ID = p_item_id OR (p_item_id IS NULL
AND POL.ITEM_ID IS NULL AND POL.ITEM_DESCRIPTION = p_item_desc ))
AND NVL(POLL.APPROVED_FLAG,'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG,'N') = 'N'
/*Added for Bug#7281141- getting the distributions against only the open line locations
AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' */
AND NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','CLOSED FOR RECEIVING','FINALLY CLOSED')
AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' )
UNION ALL
SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
-- p_po_release_id is null and p_po_line_id is not null
FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
WHERE POLL.PO_HEADER_ID = p_po_header_id
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID) -- Bug 8242448
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND p_po_release_id is NULL
AND (p_po_line_id is NOT NULL AND POLL.PO_LINE_ID = p_po_line_id)
AND (POL.ITEM_ID = p_item_id OR (p_item_id IS NULL
AND POL.ITEM_ID IS NULL AND POL.ITEM_DESCRIPTION = p_item_desc ))
AND NVL(POLL.APPROVED_FLAG,'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG,'N') = 'N'
/*Added for Bug#7281141- getting the distributions against only the open line locations
AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' */
AND NVL(POLL.CLOSED_CODE,'OPEN') NOT IN ('CLOSED','CLOSED FOR RECEIVING','FINALLY CLOSED')
AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' )
UNION ALL
SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
-- p_po_release_id is not NULL
FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
WHERE POLL.PO_HEADER_ID = p_po_header_id
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID) -- Bug 8242448
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND (p_po_release_id is NOT NULL AND POLL.PO_RELEASE_ID = p_po_release_id)
AND (p_po_line_id is null or poll.po_line_id = p_po_line_id)
AND (pol.item_id = p_item_id OR (p_item_id IS NULL
AND pol.item_id IS NULL AND pol.item_description = p_item_desc))
AND NVL(POLL.APPROVED_FLAG,'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG,'N') = 'N'
AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' );
SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
WHERE POL.PO_HEADER_ID = p_po_header_id
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID)
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND p_po_release_id is NULL
AND p_po_line_id is NULL
AND (POL.ITEM_ID = p_item_id OR (p_item_id IS NULL
AND POL.ITEM_ID IS NULL AND POL.ITEM_DESCRIPTION = p_item_desc ))
AND NVL(POLL.APPROVED_FLAG,'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG,'N') = 'N'
AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' )
UNION ALL
SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
WHERE POLL.PO_HEADER_ID = p_po_header_id
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID)
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND p_po_release_id is NULL
AND (p_po_line_id is NOT NULL AND POLL.PO_LINE_ID = p_po_line_id)
AND (POL.ITEM_ID = p_item_id OR (p_item_id IS NULL
AND POL.ITEM_ID IS NULL AND POL.ITEM_DESCRIPTION = p_item_desc ))
AND NVL(POLL.APPROVED_FLAG,'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG,'N') = 'N'
AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' )
UNION ALL
SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
WHERE POLL.PO_HEADER_ID = p_po_header_id
AND POLL.PO_LINE_ID = POL.PO_LINE_ID
AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID)
AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND (p_po_release_id is NOT NULL AND POLL.PO_RELEASE_ID = p_po_release_id)
AND (p_po_line_id is null or poll.po_line_id = p_po_line_id)
AND (pol.item_id = p_item_id OR (p_item_id IS NULL
AND pol.item_id IS NULL AND pol.item_description = p_item_desc))
AND NVL(POLL.APPROVED_FLAG,'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG,'N') = 'N'
AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' );
SELECT DISTINCT Nvl(pod.destination_type_code,pod.destination_context)
FROM po_distributions pod, po_lines pol, po_line_locations poll
WHERE pod.po_header_id = p_po_header_id
AND NVL(poll.po_release_id, -1) = NVL(p_po_release_id, NVL(poll.po_release_id, -1))
AND NVL(poll.po_line_id, -1) = NVL(p_po_line_id, NVL(poll.po_line_id, -1))
--AND pol.item_id = p_item_id
AND pod.line_location_id = poll.line_location_id
AND (pol.item_id = p_item_id
OR (p_item_id IS NULL
AND pol.item_id IS NULL
AND pol.item_description = p_item_desc
)
)
AND pol.po_line_id = poll.po_line_id
AND NVL(poll.approved_flag, 'N') = 'Y'
AND NVL(poll.cancel_flag, 'N') = 'N'
AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED');
SELECT NVL(p_po_header_id, rsl.po_header_id) po_header_id
, rsl.po_line_id po_line_id
, rsl.po_release_id po_release_id
, rsl.item_id item_id
, rsl.routing_header_id rsl_routing_id -- 14354069
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.po_header_id = NVL(p_po_header_id, rsl.po_header_id)
AND(EXISTS(SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.source_line_id = rsl.po_line_id
AND wlc.parent_lpn_id = p_lpn_id)
OR p_lpn_id IS NULL);
SELECT NVL(p_po_header_id, rsl.po_header_id) po_header_id
, rsl.po_line_id po_line_id
, rsl.po_release_id po_release_id
, rsl.item_id item_id
, rsl.routing_header_id rsl_routing_id -- 14354069
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.po_header_id = NVL(p_po_header_id, rsl.po_header_id)
AND (( ( rsl.asn_lpn_id IS NOT NULL
AND rsl.asn_lpn_id in
(SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
start with lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id
)
)
OR (rsl.asn_lpn_id IS NULL
AND exists (SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.source_line_id = rsl.po_line_id
AND wlc.parent_lpn_id = p_lpn_id)
)
)
OR
(p_lpn_id IS NULL)
);
SELECT NVL(routing_header_id, 1)
, po_header_id
, po_line_id -- Bug 8242448
, po_release_id -- Bug 8242448
FROM rcv_shipment_lines
WHERE shipment_header_id = p_shipment_header_id
AND(
(item_id IS NULL
AND p_item_id IS NULL
AND item_description = p_item_desc
AND source_document_code = 'PO')
OR (item_id = NVL(p_item_id, item_id))
);
SELECT 'Y'
INTO l_is_expense
FROM po_requisition_lines prl
, rcv_shipment_lines rsl
WHERE prl.requisition_line_id = rsl.requisition_line_id
AND prl.destination_type_code = 'EXPENSE'
AND rsl.shipment_header_id = p_shipment_header_id
AND rsl.item_id = NVL(p_item_id, rsl.item_id)
AND ROWNUM = 1;
/* select item level controls that are not specifid at the purchase
** order level
*/
IF (NVL(p_item_id, 0) <> 0) THEN
BEGIN
IF (l_debug = 1) THEN
print_debug('p_item_id ' || TO_CHAR(p_item_id) || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SELECT NVL(x_default_routing_id, receiving_routing_id)
INTO x_default_routing_id
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
** select vendor level controls not defined in the previous levels
*/
IF (NVL(p_vendor_id, 0) <> 0) THEN
BEGIN
SELECT NVL(x_default_routing_id, receiving_routing_id)
INTO x_default_routing_id
FROM po_vendors
WHERE vendor_id = p_vendor_id;
** select organization level controls not defined in the previous levels
*/
BEGIN
SELECT NVL(x_default_routing_id, NVL(receiving_routing_id, 1))
INTO x_default_routing_id
FROM rcv_parameters
WHERE organization_id = p_organization_id;
SELECT NVL(project_reference_enabled, 2)
INTO l_pjm_org
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT project_id,task_id
INTO l_project_id,l_task_id
FROM mtl_txn_request_lines
WHERE lpn_id = p_lpn_id
AND ROWNUM=1;
SELECT project_id,task_id
INTO l_project_id,l_task_id
FROM rcv_transactions_interface
WHERE transfer_lpn_id = p_lpn_id
AND transaction_type = 'RECEIVE'
AND transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR'
AND ROWNUM=1;
SELECT return_inspection_requirement
INTO x_routing_id
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
select RMA_RECEIPT_ROUTING_ID
into x_routing_id
from mtl_client_parameters
WHERE client_code = l_client_code;
select rma_receipt_routing_id
into x_routing_id
from rcv_parameters
where organization_id = p_organization_id;
select rma_receipt_routing_id
into x_routing_id
from rcv_parameters
where organization_id = p_organization_id;
SELECT NVL(lpn_context, 5)
, NVL(subinventory_code, '@@@')
, NVL(locator_id, -1)
INTO l_lpn_context
, l_sub
, l_locator_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT 1
INTO l_lpn_loaded
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wms_dispatched_tasks wdt, mtl_txn_request_lines mtrl
WHERE wdt.move_order_line_id = mtrl.line_id
AND wdt.organization_id = p_organization_id
AND wdt.status = 4
AND mtrl.lpn_id IN
(
SELECT wlpn1.lpn_id
FROM wms_license_plate_numbers wlpn1
WHERE wlpn1.outermost_lpn_id =
(
SELECT outermost_lpn_id
FROM wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_id = p_lpn_id
)
)
);
SELECT NVL(restrict_locators_code, 2)
, NVL(restrict_subinventories_code, 2)
INTO l_restrict_locator_code
, l_restrict_sub_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT 1
INTO l_dummy
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_item_sub_inventories mis
WHERE mis.organization_id = p_organization_id
AND mis.inventory_item_id = p_item_id
AND mis.secondary_inventory = l_sub
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, l_transaction_type, NULL, NULL, p_organization_id
, p_item_id, l_sub, NULL, NULL, NULL, 'Z') = 'Y');
SELECT 1
INTO l_dummy
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_secondary_inventories msi
WHERE msi.organization_id = p_organization_id
AND NVL(msi.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND msi.secondary_inventory_name = l_sub
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, l_transaction_type, NULL, NULL, p_organization_id
, p_item_id, l_sub, NULL, NULL, NULL, 'Z') = 'Y');
SELECT 1
INTO l_dummy
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_secondary_locators msl
WHERE msl.organization_id = p_organization_id
AND msl.inventory_item_id = p_item_id
AND msl.subinventory_code = l_sub
AND msl.secondary_locator = l_locator_id
AND inv_material_status_grp.is_status_applicable(
'TRUE'
, NULL
, l_transaction_type
, NULL
, NULL
, p_organization_id
, p_item_id
, l_sub
, l_locator_id
, NULL
, NULL
, 'L'
) = 'Y');
SELECT 1
INTO l_dummy
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_item_locations mil
WHERE mil.organization_id = p_organization_id
AND mil.subinventory_code = l_sub
AND NVL(mil.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE)
AND mil.inventory_location_id = l_locator_id
AND inv_material_status_grp.is_status_applicable(
'TRUE'
, NULL
, l_transaction_type
, NULL
, NULL
, p_organization_id
, p_item_id
, l_sub
, l_locator_id
, NULL
, NULL
, 'L'
) = 'Y');
UPDATE wms_license_plate_numbers
SET lpn_context = DECODE(x_routing_id, 3, 1, 3)
WHERE lpn_id = p_lpn_id;
SELECT lpn_id
INTO p_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_lpn;
PROCEDURE insert_lot(
p_transaction_temp_id IN OUT NOCOPY NUMBER
, p_created_by IN NUMBER
, p_transaction_qty IN NUMBER
, p_primary_qty IN NUMBER
, p_lot_number IN VARCHAR2
, p_expiration_date IN DATE
, p_status_id IN NUMBER := NULL
, x_serial_transaction_temp_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, p_secondary_quantity IN NUMBER --OPM Convergence
) IS
l_return NUMBER;
print_debug('Enter insert_lot: 10:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
/* For Bug#2266537. check if the lot being inserted is already there in MTLT
with the same temp_id. If so then the quantity of the lot is updated
instead of generating a new lot.*/
IF p_transaction_temp_id IS NOT NULL THEN
BEGIN
SELECT 1
, serial_transaction_temp_id
INTO l_count
, x_serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = p_lot_number
AND ROWNUM = 1;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity + p_transaction_qty
, primary_quantity = primary_quantity + p_primary_qty
WHERE transaction_temp_id = p_transaction_temp_id
AND lot_number = p_lot_number;
SELECT mtl_material_transactions_s.NEXTVAL
INTO p_transaction_temp_id
FROM DUAL;
inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id => p_transaction_temp_id
, p_user_id => p_created_by
, p_lot_number => p_lot_number
, p_trx_qty => p_transaction_qty
, p_pri_qty => p_primary_qty
, p_exp_date => p_expiration_date
, p_status_id => p_status_id
, x_ser_trx_id => x_serial_transaction_temp_id
, x_proc_msg => x_msg_data
, p_secondary_qty => p_secondary_quantity --OPM Convergence
);
print_debug('Exitting insert_lot : 60 ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('inv_rcv_common_apis.insert_lot', l_progress, SQLCODE);
print_debug('Exitting insert_lot - other exception:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'insert_lot');
END insert_lot;
PROCEDURE insert_serial(
p_serial_transaction_temp_id IN OUT NOCOPY NUMBER
, p_org_id IN NUMBER
, p_item_id IN NUMBER
, p_rev IN VARCHAR2
, p_lot IN VARCHAR2
, p_txn_src_id IN NUMBER
, p_txn_action_id IN NUMBER
, p_created_by IN NUMBER
, p_from_serial IN VARCHAR2
, p_to_serial IN VARCHAR2
, p_status_id IN NUMBER := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_return NUMBER;
print_debug('Enter insert_serial: 10:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SAVEPOINT rcv_insert_serial_sp;
SELECT 1
INTO l_count
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
WHERE (
(p_from_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
AND get_serial_Length(p_from_serial) = get_serial_Length(msnt.fm_serial_number)
AND get_serial_Length(msnt.fm_serial_number) = get_serial_Length(Nvl(msnt.to_serial_number,msnt.fm_serial_number)))
OR
(p_to_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
AND get_serial_Length(p_to_serial) = get_serial_Length(msnt.fm_serial_number)
AND get_serial_Length(msnt.fm_serial_number) = get_serial_Length(Nvl(msnt.to_serial_number,msnt.fm_serial_number)))
)
AND mmtt.inventory_item_id = p_item_id
AND mmtt.organization_id = p_org_id
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id);
SELECT mtl_material_transactions_s.NEXTVAL
INTO p_serial_transaction_temp_id
FROM DUAL;
inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id => p_serial_transaction_temp_id
, p_user_id => p_created_by
, p_fm_ser_num => p_from_serial
, p_to_ser_num => p_to_serial
, p_status_id => p_status_id
, x_proc_msg => x_msg_data
);
UPDATE mtl_serial_numbers
SET group_mark_id = p_serial_transaction_temp_id
WHERE inventory_item_id = p_item_id
AND serial_number BETWEEN p_from_serial AND p_to_serial
AND LENGTH(serial_number) = LENGTH(p_from_serial);
print_debug('Insert serial vals' || p_item_id || ':' || p_from_serial || ':' || p_to_serial, 4);
print_debug('Insert serial, inserted with ' || p_serial_transaction_temp_id || ':' || l_success, 4);
print_debug('Exitting insert_serial : 90 ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
ROLLBACK TO rcv_insert_serial_sp;
print_debug('Exitting insert_serial - execution error:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('inv_rcv_common_apis.insert_serial', l_progress, SQLCODE);
print_debug('Exitting insert_serial - other exception:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'insert_serial');
END insert_serial;
SELECT COUNT (DISTINCT oel.order_quantity_uom)
INTO l_count
FROM oe_order_lines_all oel, oe_order_headers_all oeh
WHERE oel.header_id = p_order_header_id
--AND oel.ordered_item_id = p_item_id -- commented for Bug 12640725
AND oel.inventory_item_id = p_item_id -- added for Bug 12640725
AND NVL (OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) = p_organization_id
AND OEL.LINE_CATEGORY_CODE = 'RETURN'
AND oel.cancelled_flag = 'N'
AND oel.open_flag = 'Y'
AND oel.booked_flag = 'Y'
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
AND OEL.ORDERED_QUANTITY > NVL (OEL.SHIPPED_QUANTITY, 0)
AND oeh.header_id = oel.header_id
AND OEH.OPEN_FLAG = 'Y';
SELECT inv_ui_item_lovs.get_conversion_rate (oel.order_quantity_uom,
p_organization_id,
--oel.ordered_item_id) -- commented for Bug 12640725
oel.inventory_item_id) -- added for Bug 12640725
INTO x_uom_code
FROM oe_order_lines_all oel
WHERE oel.header_id = p_order_header_id
--AND oel.ordered_item_id = p_item_id -- commented for Bug 12640725
AND oel.inventory_item_id = p_item_id -- added for Bug 12640725
AND oel.line_category_code = 'RETURN'
AND oel.cancelled_flag = 'N'
AND oel.open_flag = 'Y'
AND oel.booked_flag = 'Y'
AND oel.flow_status_code = 'AWAITING_RETURN'
AND EXISTS (SELECT 1
FROM oe_order_headers_all oeh
WHERE oeh.open_flag = 'Y' AND oeh.header_id = oel.header_id)
AND ROWNUM = 1;
SELECT COUNT (DISTINCT rsl.unit_of_measure)
INTO l_count
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.unit_of_measure IS NOT NULL
AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
AND rsl.item_id = p_item_id
AND rsl.asn_line_flag = 'Y'
AND rsl.to_organization_id = p_organization_id;
SELECT inv_ui_item_lovs.get_conversion_rate (mum.uom_code, p_organization_id, rsl.item_id)
INTO x_uom_code
FROM rcv_shipment_lines rsl, mtl_units_of_measure mum
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.unit_of_measure IS NOT NULL
AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
AND rsl.item_id = p_item_id
AND mum.unit_of_measure(+) = rsl.unit_of_measure
AND rsl.asn_line_flag = 'Y'
AND rsl.to_organization_id = p_organization_id
AND ROWNUM = 1;
SELECT mum.uom_code, mum.uom_class
INTO x_uom_code, l_class
FROM rcv_shipment_lines rsl, mtl_units_of_measure mum
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.unit_of_measure IS NOT NULL
AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
AND rsl.item_description = p_item_desc
AND mum.unit_of_measure(+) = rsl.unit_of_measure
AND rsl.asn_line_flag = 'Y'
AND rsl.to_organization_id = p_organization_id
AND ROWNUM = 1;
SELECT INV_UI_RCV_LOVS.get_conversion_rate_expense (muom.uom_code,
p_organization_id,
0,
x_uom_code)
INTO x_uom_code
FROM mtl_uom_conversions_val_v muc, mtl_units_of_measure muom
WHERE muc.uom_class = l_class
AND muc.item_id = 0
AND NVL (muc.disable_date, SYSDATE + 1) > SYSDATE
AND muc.unit_of_measure = muom.unit_of_measure
AND NVL (muom.disable_date, SYSDATE + 1) > SYSDATE
AND muom.uom_code LIKE (x_uom_code)
ORDER BY muc.unit_of_measure;
SELECT mum.uom_code, mum.uom_class
INTO x_uom_code, l_class
FROM rcv_shipment_lines rsl, mtl_units_of_measure mum
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.unit_of_measure IS NOT NULL
AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
AND rsl.item_id IS NULL
AND mum.UNIT_OF_MEASURE(+) = rsl.unit_of_measure
AND RSL.ASN_LINE_FLAG = 'Y'
AND rsl.TO_ORGANIZATION_ID = p_organization_id
AND ROWNUM = 1;
SELECT INV_UI_RCV_LOVS.get_conversion_rate_expense (muom.uom_code,
p_organization_id,
0,
x_uom_code)
INTO x_uom_code
FROM mtl_uom_conversions_val_v muc, mtl_units_of_measure muom
WHERE muc.uom_class = l_class
AND muc.item_id = 0
AND NVL (muc.disable_date, SYSDATE + 1) > SYSDATE
AND muc.unit_of_measure = muom.unit_of_measure
AND NVL (muom.disable_date, SYSDATE + 1) > SYSDATE
AND muom.uom_code LIKE (x_uom_code)
ORDER BY muc.unit_of_measure;
SELECT mum.uom_code, mum.uom_class
INTO x_uom_code, l_class
FROM po_lines pol, mtl_units_of_measure mum
WHERE pol.po_header_id = p_po_header_id
AND pol.unit_meas_lookup_code IS NOT NULL
AND pol.line_num = p_line_no
AND pol.unit_meas_lookup_code = mum.unit_of_measure
AND pol.po_line_id IN (SELECT poll.po_line_id
FROM po_line_locations_all poll, po_lines_all po
WHERE poll.po_header_id = po.po_header_id
AND Nvl(poll.approved_flag,'N') = 'Y'
AND Nvl(poll.cancel_flag,'N') = 'N'
AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')
AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
AND poll.ship_to_organization_id = p_organization_id
AND poll.po_line_id = po.po_line_id
AND po.po_header_id = p_po_header_id)
AND ROWNUM=1;
SELECT INV_UI_RCV_LOVS.get_conversion_rate_expense(muom.uom_code,p_organization_id ,0,x_uom_code )
into x_uom_code
from
mtl_uom_conversions_val_v muc ,
mtl_units_of_measure muom
where muc.uom_class = l_class
and muc.item_id = 0
and nvl(muc.disable_date,sysdate+1)>sysdate
and muc.unit_of_measure = muom.unit_of_measure
and nvl(muom.disable_date,sysdate+1) > sysdate
and muom.uom_code like (x_uom_code)
order by muc.unit_of_measure;
SELECT mum.uom_code, mum.uom_class
INTO x_uom_code, l_class
FROM po_lines pol, mtl_units_of_measure mum
WHERE pol.po_header_id = p_po_header_id
AND pol.unit_meas_lookup_code IS NOT NULL
AND pol.unit_meas_lookup_code = mum.unit_of_measure
AND pol.item_description = p_item_desc
AND pol.po_line_id IN (SELECT poll.po_line_id
FROM po_line_locations_all poll, po_lines_all po
WHERE poll.po_header_id = po.po_header_id
AND Nvl(poll.approved_flag,'N') = 'Y'
AND Nvl(poll.cancel_flag,'N') = 'N'
AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')
AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
AND poll.ship_to_organization_id = p_organization_id
AND poll.po_line_id = po.po_line_id
AND po.po_header_id = p_po_header_id)
AND ROWNUM=1;
SELECT INV_UI_RCV_LOVS.get_conversion_rate_expense(muom.uom_code, p_organization_id ,0,x_uom_code )
into x_uom_code
from
mtl_uom_conversions_val_v muc ,
mtl_units_of_measure muom
where muc.uom_class = l_class
and muc.item_id = 0
and nvl(muc.disable_date,sysdate+1)>sysdate
and muc.unit_of_measure = muom.unit_of_measure
and nvl(muom.disable_date,sysdate+1) > sysdate
and muom.uom_code like (x_uom_code)
order by muc.unit_of_measure;
SELECT COUNT(DISTINCT pol.unit_meas_lookup_code)
INTO l_count
FROM po_lines pol
WHERE pol.po_header_id = p_po_header_id
AND pol.unit_meas_lookup_code IS NOT NULL
AND pol.item_id = p_item_id
AND pol.po_line_id IN (SELECT poll.po_line_id
FROM po_line_locations_all poll, po_lines_all po
WHERE poll.po_header_id = po.po_header_id
AND Nvl(poll.approved_flag,'N') = 'Y'
AND Nvl(poll.cancel_flag,'N') = 'N'
AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')
AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
AND poll.ship_to_organization_id = p_organization_id
AND poll.po_line_id = po.po_line_id
AND po.item_id = p_item_id
AND po.po_header_id = p_po_header_id);
SELECT inv_ui_item_lovs.get_conversion_rate(mum.uom_code,
p_organization_id,
pol.item_id)
INTO x_uom_code
FROM po_lines pol
, mtl_units_of_measure mum
WHERE pol.po_header_id = p_po_header_id
AND pol.unit_meas_lookup_code IS NOT NULL
AND pol.item_id = p_item_id
AND mum.UNIT_OF_MEASURE(+) = pol.UNIT_MEAS_LOOKUP_CODE
AND pol.po_line_id IN (SELECT poll.po_line_id
FROM po_line_locations_all poll, po_lines_all po
WHERE poll.po_header_id = po.po_header_id
AND Nvl(poll.approved_flag,'N') = 'Y'
AND Nvl(poll.cancel_flag,'N') = 'N'
AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')
AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
AND poll.ship_to_organization_id = p_organization_id
AND poll.po_line_id = po.po_line_id
AND po.item_id = p_item_id
AND po.po_header_id = p_po_header_id)
AND ROWNUM = 1;
SELECT NVL(subinventory_code, '@@@')
, NVL(locator_id, -1)
, lpn_context
INTO x_sub_code
, l_locator_id
, l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
select count(*) into l_count_lpn
from wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id;
SELECT NVL(project_reference_enabled, 2)
INTO l_is_pjm_org
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT mil.project_id
, mil.task_id
INTO l_loc_project_id
, l_loc_task_id
FROM mtl_item_locations mil
WHERE mil.organization_id = p_organization_id
AND mil.inventory_location_id = l_locator_id;
SELECT NVL(subinventory, '@@@')
, NVL(locator_id, -1)
, auto_transact_code
INTO x_sub_code
, l_locator_id
, l_auto_transact_code
FROM rcv_transactions_interface
WHERE transfer_lpn_id = p_lpn_id
AND transaction_type = 'RECEIVE'
AND transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR'
AND ROWNUM = 1;
SELECT COUNT(DISTINCT pod.destination_subinventory)
INTO l_count
FROM po_distributions pod
WHERE pod.po_header_id = p_po_header_id
AND pod.po_line_id = NVL(p_po_line_id, pod.po_line_id)
AND NVL(pod.po_release_id, -1) = NVL(p_po_release_id, NVL(pod.po_release_id, -1))
AND pod.destination_subinventory IS NOT NULL
AND pod.po_line_id IN(SELECT pol.po_line_id
FROM po_lines pol
WHERE pol.item_id = p_item_id);
SELECT pod.destination_subinventory, nvl(kanban_card_id, -999) --Bug 4671198
INTO x_sub_code, l_kanban_card_id --Bug 4671198
FROM po_distributions pod
WHERE pod.po_header_id = p_po_header_id
AND pod.po_line_id = NVL(p_po_line_id, pod.po_line_id)
AND NVL(pod.po_release_id, -1) = NVL(p_po_release_id, NVL(pod.po_release_id, -1))
AND pod.destination_subinventory IS NOT NULL
AND pod.po_line_id IN(SELECT pol.po_line_id
FROM po_lines pol
WHERE pol.item_id = p_item_id)
AND ROWNUM = 1;
SELECT NVL(locator_id, -1)
INTO l_locator_id
FROM mtl_kanban_cards
WHERE kanban_card_id = l_kanban_card_id
AND subinventory_name = x_sub_code;
SELECT COUNT(DISTINCT rsl.to_subinventory)
INTO l_count
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.item_id = NVL(p_item_id, rsl.item_id)
AND rsl.to_subinventory IS NOT NULL;
SELECT rsl.to_subinventory
, NVL(rsl.locator_id, -1)
INTO x_sub_code
, l_locator_id
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.item_id = NVL(p_item_id, rsl.item_id)
AND rsl.to_subinventory IS NOT NULL
AND ROWNUM = 1;
SELECT subinventory_code
INTO x_sub_code
FROM mtl_item_sub_defaults
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND default_type = 2;
SELECT mild.locator_id
INTO l_locator_id
FROM mtl_item_loc_defaults mild, mtl_item_locations mil
WHERE mild.inventory_item_id = p_item_id
AND mild.organization_id = p_organization_id
AND mild.subinventory_code = x_sub_code
AND mil.inventory_location_id = mild.locator_id
AND(p_project_id IS NULL
OR(p_project_id = -9999
AND mil.project_id IS NULL)
OR mil.project_id = p_project_id)
AND NVL(mil.task_id, -1) = NVL(p_task_id, NVL(mil.task_id, -1))
AND mild.default_type = 2;
SELECT mild.locator_id
INTO l_locator_id
FROM mtl_item_loc_defaults mild, mtl_item_locations mil
WHERE mild.inventory_item_id = p_item_id
AND mild.organization_id = p_organization_id
AND mild.subinventory_code = x_sub_code
AND mil.inventory_location_id = mild.locator_id
AND(p_project_id IS NULL
OR(p_project_id = -9999
AND mil.project_id IS NULL)
OR mil.project_id = p_project_id)
AND NVL(mil.task_id, -1) = NVL(p_task_id, NVL(mil.task_id, -1))
AND mild.default_type = 2;
SELECT inv_project.get_locsegs(inventory_location_id, organization_id)
INTO x_locator_segs
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND inventory_location_id = l_locator_id;
SELECT distinct(hl.location_code)
INTO x_location_code
FROM po_distributions_all pda,po_lines_all pol, po_line_locations_all pll, hr_locations hl
WHERE pda.po_header_id = pll.po_header_id
AND pda.line_location_id = pll.line_location_id
AND pda.po_line_id = pll.po_line_id
AND pda.po_header_id = pol.po_header_id
AND pda.po_line_id = pol.po_line_id
AND pda.deliver_to_location_id = hl.location_id
AND pda.po_header_id = p_po_header_id
AND pda.destination_organization_id = p_organization_id
AND pda.po_line_id = NVL(p_po_line_id, pda.po_line_id)
AND pll.po_line_id = NVL(p_po_line_id, pll.po_line_id)
AND NVL(pda.po_release_id, -1) = NVL(p_po_release_id, NVL(pda.po_release_id, -1))
AND NVL(pll.po_release_id, -1) = NVL(p_po_release_id, NVL(pll.po_release_id, -1))
AND Nvl(pol.item_id,-9999) = NVL(p_item_id,Nvl(pol.item_id,-9999))
AND pll.receiving_routing_id = 3;
SELECT distinct( hl.location_code )
INTO x_location_code
FROM hr_locations hl, po_line_locations poll,po_lines pol
WHERE hl.location_id = poll.ship_to_location_id
AND poll.po_header_id = pol.po_header_id
AND poll.po_line_id = pol.po_line_id
AND poll.po_header_id = p_po_header_id
AND poll.ship_to_organization_id= p_organization_id
AND poll.po_line_id = NVL(p_po_line_id, poll.po_line_id)
AND NVL(poll.po_release_id, -1) = NVL(p_po_release_id, NVL(poll.po_release_id, -1))
AND NVL(poll.po_release_id, -1) = NVL(p_po_release_id, NVL(poll.po_release_id, -1))
AND Nvl(poll.approved_flag,'N') = 'Y'
AND Nvl(poll.cancel_flag,'N') = 'N'
AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')
AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED')
AND Nvl(pol.item_id,-9999) = NVL(p_item_id,Nvl(pol.item_id,-9999)); --BUG 4500676
SELECT distinct( hl.location_code )
into x_location_code
FROM hr_locations hl, rcv_shipment_lines rsl
WHERE hl.location_id = nvl(rsl.deliver_to_location_id,rsl.ship_to_location_id)--bug10349270 for ASN receipt,we should get the default location against RSL.ship_to_location
AND rsl.shipment_header_id = p_shipment_header_id
AND rsl.item_id = NVL(p_item_id, rsl.item_id)
AND rsl.to_organization_id = p_organization_id
AND NVL(rsl.asn_lpn_id,-1) = NVL(p_from_lpn_id,NVL(rsl.asn_lpn_id,-1))
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED');
SELECT DISTINCT (pol.item_revision)
INTO x_revision_code
FROM po_line_locations poll,po_lines pol
WHERE pol.po_header_id = p_po_header_id
AND NVL(poll.po_line_id,-1) = NVL(p_po_line_id, NVL(poll.po_line_id, -1))
AND NVL(pol.item_id,-1) = NVL(p_item_id,NVL(pol.item_id, -1))
AND poll.ship_to_organization_id= p_organization_id
AND NVL(poll.po_release_id, -1) = NVL(p_po_release_id, NVL(poll.po_release_id, -1))
AND pol.po_line_id = poll.po_line_id
AND NVL(poll.po_release_id, -1) = NVL(p_po_release_id, NVL(poll.po_release_id, -1))
AND Nvl(poll.approved_flag,'N') = 'Y'
AND Nvl(poll.cancel_flag,'N') = 'N'
AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')
AND poll.shipment_type IN ('STANDARD','BLANKET','SCHEDULED');
SELECT 'Y'
INTO l_rcvreq_use_intship
FROM dual
WHERE EXISTS ( SELECT 1
FROM rcv_shipment_lines
WHERE shipment_header_id = l_shipment_header_id
AND requisition_line_id IS NOT NULL
AND source_document_code = 'REQ'
);
SELECT DISTINCT(rsl.item_revision)
INTO x_revision_code
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
AND rsl.to_organization_id = p_organization_id
AND rsl.item_id = NVL(p_item_id,rsl.item_id)
AND rsl.source_document_code = DECODE (p_document_type, 'INTSHIP', l_doc_type, 'REQ' ,'REQ', 'ASN','PO', 'REQ' )
AND rsl.shipment_line_status_code in ('EXPECTED','PARTIALLY RECEIVED');
SELECT DISTINCT(OEL.item_revision)
INTO x_revision_code
FROM
OE_ORDER_LINES_all OEL,
OE_ORDER_HEADERS_all OEH
WHERE OEL.LINE_CATEGORY_CODE='RETURN'
AND OEL.INVENTORY_ITEM_ID = p_item_id
AND nvl(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) = p_organization_id
AND OEL.HEADER_ID = OEH.HEADER_ID
AND OEH.HEADER_ID = p_oe_order_header_id
AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY,0)
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN';
UPDATE mtl_lot_numbers
SET (VENDOR_ID,
GRADE_CODE,
ORIGINATION_DATE,
DATE_CODE,
STATUS_ID,
CHANGE_DATE,
AGE,
RETEST_DATE,
MATURITY_DATE,
LOT_ATTRIBUTE_CATEGORY,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
CURL_WRINKLE_FOLD,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE10,
SUPPLIER_LOT_NUMBER,
N_ATTRIBUTE9,
TERRITORY_CODE,
vendor_name,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
) =
(SELECT VENDOR_ID,
GRADE_CODE,
ORIGINATION_DATE,
DATE_CODE,
STATUS_ID,
CHANGE_DATE,
AGE,
RETEST_DATE,
MATURITY_DATE,
LOT_ATTRIBUTE_CATEGORY,
ITEM_SIZE,
COLOR,
VOLUME,
VOLUME_UOM,
PLACE_OF_ORIGIN,
BEST_BY_DATE,
LENGTH,
LENGTH_UOM,
RECYCLED_CONTENT,
THICKNESS,
THICKNESS_UOM,
WIDTH,
WIDTH_UOM,
CURL_WRINKLE_FOLD,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE10,
SUPPLIER_LOT_NUMBER,
N_ATTRIBUTE9,
TERRITORY_CODE,
vendor_name,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM mtl_lot_numbers
WHERE organization_id = p_from_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number)
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
PROCEDURE insert_dynamic_lot(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_expiration_date IN OUT NOCOPY DATE
, p_transaction_temp_id IN NUMBER DEFAULT NULL
, p_transaction_action_id IN NUMBER DEFAULT NULL
, p_transfer_organization_id IN NUMBER DEFAULT NULL
, p_status_id IN NUMBER
, p_update_status IN VARCHAR2 := 'FALSE'
, x_object_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_parent_lot_number IN VARCHAR2 DEFAULT NULL -- bug 10176719 - inserting parent lot number
, p_origination_type IN NUMBER DEFAULT NULL -- bug 15896641 - inserting origination type
) IS
l_exists VARCHAR2(7) := 'FALSE';
print_debug('Inside insert_dynamic_lot', 4);
/*Added select for bug 3853202*/
IF p_transfer_organization_id IS NOT NULL THEN
BEGIN
SELECT STATUS_ID
INTO l_status_id
FROM MTL_LOT_NUMBERS
WHERE LOT_NUMBER = p_lot_number
AND ORGANIZATION_ID = p_transfer_organization_id
AND INVENTORY_ITEM_ID = p_inventory_item_id;
SELECT lot_status_enabled --Added select for bug3998321
INTO l_status_enabled
FROM
mtl_system_items
WHERE
inventory_item_id=p_inventory_item_id and
organization_id=p_transfer_organization_id;
SELECT lot_status_enabled --Added select for bug4035918
INTO l_dest_status_enabled
FROM
mtl_system_items
WHERE
inventory_item_id=p_inventory_item_id and
organization_id=p_organization_id;
SELECT 'TRUE'
INTO l_exists
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
print_debug('Lot uniqueness passed so inserting lot:' || l_exists, 4);
inv_lot_api_pub.insertlot(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_lot_number => p_lot_number
, p_expiration_date => p_expiration_date
, p_transaction_temp_id => p_transaction_temp_id
, p_transaction_action_id => p_transaction_action_id
, p_transfer_organization_id => p_transfer_organization_id
, x_object_id => x_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_parent_lot_number => p_parent_lot_number --bug 10176719 - inserting parent lot number
, p_origination_type => p_origination_type --bug 15896641 - inserting origination type
);
print_debug('Lot uniqueness did not pass so not inserting lot', 4);
AND(p_update_status = 'TRUE'))
OR (p_transfer_organization_id IS NOT NULL AND
l_exists = 'FALSE' AND l_status_enabled = 'Y' AND l_dest_status_enabled = 'Y')) THEN --Added OR condition for bug 3853202, Added l_status_enabled bug3998321
--Added l_dest_status_enabled = 'Y' bug4035918
inv_material_status_grp.update_status(
p_api_version_number => p_api_version
, p_init_msg_lst => NULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_update_method => inv_material_status_pub.g_update_method_receive
, p_status_id => l_status_id --Changed from p_status_id bug3853202
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_sub_code => NULL
, p_locator_id => NULL
, p_lot_number => p_lot_number
, p_serial_number => NULL
, p_to_serial_number => NULL
, p_object_type => 'O'
);
END insert_dynamic_lot;
PROCEDURE insert_range_serial(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_from_serial_number IN VARCHAR2
, p_to_serial_number IN VARCHAR2
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_primary_lot_quantity IN NUMBER
, p_transaction_action_id IN NUMBER
, p_current_status IN NUMBER
, p_serial_status_id IN NUMBER
, p_update_serial_status IN VARCHAR2
, p_inspection_required IN NUMBER DEFAULT NULL
, p_hdr_id IN NUMBER
, p_from_lpn_id IN NUMBER
, p_to_lpn_id IN NUMBER
, p_primary_uom_code IN VARCHAR2
, p_call_pack_unpack IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_subinventory IN VARCHAR2 DEFAULT NULL
, p_locator_id IN NUMBER DEFAULT NULL
) IS
l_object_id NUMBER;
print_debug('Enter insert_range_serial: 10:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SAVEPOINT rcv_insert_range_serial_sp;
SELECT COUNT(1)
INTO l_serial_packed_in_other_lpn
FROM mtl_serial_numbers msn
WHERE msn.current_status IN (5, 7)
AND EXISTS(SELECT 'x'
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_context NOT IN(5, 6, 7)
AND wlpn.lpn_id = msn.lpn_id)
AND msn.lpn_id IS NOT NULL
AND msn.serial_number BETWEEN p_from_serial_number AND p_to_serial_number
AND Length(msn.serial_number) = Length(p_from_serial_number)
AND Length(p_from_serial_number) = Length(Nvl(p_to_serial_number,p_from_serial_number))
AND msn.inventory_item_id = p_inventory_item_id
AND ROWNUM = 1;
print_debug('Insert_range_serial: Serial Number already Packed/Received with a Diff LPN '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
, 1);
* want to update certain columns like lpn_id, inspection_status,
* subinventory, locator etc. for the serial number.
* So have declared a new flag p_rcv_serial_flag which should be passed as Y
* to skip the updates to the serial
* If either INV or PO J is not installed, then this flag would be set to
* the value 'N' so that the updates continue as usual.
*/
IF ((inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) OR
(inv_rcv_common_apis.g_po_patch_level < inv_rcv_common_apis.g_patchset_j_po)) THEN
l_inv_po_j_higher := FALSE;
inv_serial_number_pub.insert_range_serial(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_from_serial_number => p_from_serial_number
, p_to_serial_number => p_to_serial_number
, p_initialization_date => SYSDATE
, p_completion_date => NULL
, p_ship_date => NULL
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_current_locator_id => NULL
, p_subinventory_code => NULL
, p_trx_src_id => NULL
, p_unit_vendor_id => NULL
, p_vendor_lot_number => NULL
, p_vendor_serial_number => NULL
, p_receipt_issue_type => NULL
, p_txn_src_id => NULL
, p_txn_src_name => NULL
, p_txn_src_type_id => NULL
, p_transaction_id => NULL
, p_current_status => p_current_status
, p_parent_item_id => NULL
, p_parent_serial_number => NULL
, p_cost_group_id => NULL
, p_transaction_action_id => p_transaction_action_id
, p_transaction_temp_id => NULL
, p_status_id => NULL
, p_inspection_status => p_inspection_required
, x_object_id => l_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rcv_serial_flag => l_rcv_serial_flag
);
* Similarly, need not mark the serials since it would be done in the insert_msni
* API upon creating the MSNI interface records
*/
IF (l_inv_po_j_higher = FALSE) THEN
IF p_update_serial_status = 'TRUE' THEN
l_progress := '40';
inv_material_status_grp.update_status(
p_api_version_number => p_api_version
, p_init_msg_lst => NULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_update_method => inv_material_status_pub.g_update_method_receive
, p_status_id => p_serial_status_id
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_sub_code => NULL
, p_locator_id => NULL
, p_lot_number => p_lot_number
, p_serial_number => p_from_serial_number
, p_to_serial_number => p_to_serial_number
, p_object_type => 'S'
);
print_debug('insert_range_serial: INV and PO patchset levels are J or higher. No packunpack from UI. No marking from here', 4);
print_debug('Exit insert_range_serial 90:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
ROLLBACK TO rcv_insert_range_serial_sp;
print_debug('Exitting insert_range_serial - execution error:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
ROLLBACK TO rcv_insert_range_serial_sp;
print_debug('Exitting insert_range_serial - unexpected error:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
ROLLBACK TO rcv_insert_range_serial_sp;
print_debug('Exitting insert_range_serial - other exceptions:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('INV_RCV_COMMON_APIS.insert_range_serial', l_progress, SQLCODE);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'insert_range_serial');
PROCEDURE update_serial_status(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_from_serial_number IN VARCHAR2
, p_to_serial_number IN VARCHAR2
, p_current_status IN NUMBER
, p_serial_status_id IN NUMBER
, p_update_serial_status IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_primary_lot_quantity IN NUMBER
, p_inspection_required IN NUMBER
, p_hdr_id IN NUMBER
, p_from_lpn_id IN NUMBER
, p_to_lpn_id IN NUMBER
, p_revision IN VARCHAR2
, p_primary_uom_code IN VARCHAR2
, p_call_pack_unpack IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_subinventory IN VARCHAR2 DEFAULT NULL
, p_locator_id IN NUMBER DEFAULT NULL
, p_txn_src_id IN VARCHAR2 DEFAULT NULL
) IS
l_from_ser_number NUMBER;
print_debug('Enter update_serial_status: 10:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SAVEPOINT rcv_update_serial_sp;
print_debug('Update Serial Status : RMA and restrict rcpt ser is Set', 1);
print_debug('Update Serial Status : Failed in getting serial control code ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
print_debug('Update Serial Status : Before Duplicate Serial Check , RMA and Serial Ctrl as SALES ISSUE', 1);
SELECT '1'
INTO l_txn_cnt
FROM DUAL
WHERE EXISTS(
SELECT '1'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND current_status = 1
AND serial_number BETWEEN p_from_serial_number AND p_to_serial_number
AND Length(serial_number) = Length(p_from_serial_number)
AND Length(p_from_serial_number) = Length(Nvl(p_to_serial_number, p_from_serial_number))
AND last_txn_source_type_id = 12);
print_debug('Update_serial_status: After Duplicate Serial Check , RMA and Serial Ctrl as SALES ISSUE Failed Here', 1);
* If INV and PO patchset levels are "J" or higher, then do not call update statis
* from UI since it would be handled by the receiving TM.
*/
IF ((inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) OR
(inv_rcv_common_apis.g_po_patch_level < inv_rcv_common_apis.g_patchset_j_po)) THEN
UPDATE mtl_serial_numbers
SET previous_status = current_status
, current_status = p_current_status
, inspection_status = p_inspection_required
, lot_number = p_lot_number
, revision = p_revision
, current_organization_id = p_organization_id
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id;
print_debug('update_serial_status: INV and PO patchset levels are J or higher.', 4);
print_debug('update_serial_status: Updating revision lot_number if serial code of the item is predefined and current status is defined but not used', 4);
UPDATE mtl_serial_numbers
SET lot_number = p_lot_number
, revision = p_revision
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id
AND current_status IN (1, 4, 5, 6);
IF p_update_serial_status = 'TRUE' THEN
l_progress := '70';
inv_material_status_grp.update_status(
p_api_version_number => p_api_version
, p_init_msg_lst => NULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_update_method => inv_material_status_pub.g_update_method_receive
, p_status_id => p_serial_status_id
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_sub_code => NULL
, p_locator_id => NULL
, p_lot_number => p_lot_number
, p_serial_number => l_cur_serial_number
, p_to_serial_number => NULL
, p_object_type => 'S'
);
* Similarly, need not mark the serials since it would be done in the insert_msni
* API upon creating the MSNI interface records
*/
IF ((inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) OR
(inv_rcv_common_apis.g_po_patch_level < inv_rcv_common_apis.g_patchset_j_po)) THEN
serial_check.inv_mark_serial(
from_serial_number => p_from_serial_number
, to_serial_number => p_to_serial_number
, item_id => p_inventory_item_id
, org_id => p_organization_id
, hdr_id => p_hdr_id
, temp_id => NULL
, lot_temp_id => NULL
, success => l_success
);
print_debug('update_serial_status: INV and PO patchset levels are J or higher. No packunpack from UI. No marking from here', 4);
print_debug('Exit update_serial_status 140:' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
ROLLBACK TO rcv_update_serial_sp;
print_debug('Exitting update_serial_status - execution error:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
ROLLBACK TO rcv_update_serial_sp;
print_debug('Exitting update_serial_status - unexpected error:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
ROLLBACK TO rcv_update_serial_sp;
print_debug('Exitting update_serial_status - other exceptions:' || l_progress || ' ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('INV_RCV_COMMON_APIS.update_serial_status', l_progress, SQLCODE);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'update_serial_status');
END update_serial_status;
, p_update_status IN VARCHAR2 := 'FALSE'
, p_is_new_lot IN VARCHAR2 := 'TRUE'
, p_call_pack_unpack IN VARCHAR2 := 'FALSE'
, p_from_lpn_id IN NUMBER
, p_to_lpn_id IN NUMBER
, p_revision IN VARCHAR2
, p_lot_primary_qty IN NUMBER
, p_primary_uom_code IN VARCHAR2
, p_transaction_uom_code IN VARCHAR2 DEFAULT NULL
, x_object_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_subinventory IN VARCHAR2 DEFAULT NULL
, p_locator_id IN NUMBER DEFAULT NULL
, p_lot_secondary_qty IN NUMBER --OPM Convergence
, p_secondary_uom_code IN VARCHAR2 --OPM Convergence
, p_parent_lot_number IN VARCHAR2 DEFAULT NULL --bug 10176719 - inserting parent lot number
, p_origination_type IN NUMBER DEFAULT NULL --bug 15896641 - inserting origination type
) IS
l_progress VARCHAR2(10);
insert_dynamic_lot(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_lot_number => p_lot_number
, p_expiration_date => p_expiration_date
, p_transaction_temp_id => p_transaction_temp_id
, p_transaction_action_id => p_transaction_action_id
, p_transfer_organization_id => p_transfer_organization_id
, p_status_id => p_status_id
, p_update_status => p_update_status
, x_object_id => x_object_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_parent_lot_number => p_parent_lot_number -- bug 10176719 - inserting parent lot number
, p_origination_type => p_origination_type -- bug 15896641 - inserting origination type
);
SELECT rcv_interface_groups_s.NEXTVAL
INTO g_rcv_global_var.interface_group_id
FROM DUAL;
SELECT DISTINCT rsl.shipment_header_id
, rsl.from_organization_id
INTO x_shipment_header_id
, x_from_org_id
FROM rcv_shipment_lines rsl
WHERE item_id = p_item_id
AND to_organization_id = p_organization_id
AND EXISTS(
SELECT 1
FROM po_requisition_lines prl, rcv_transactions rt, rcv_supply rs
WHERE prl.requisition_header_id = p_requiition_header_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND prl.item_id = p_item_id
AND prl.source_type_code = 'INVENTORY'
AND rs.req_line_id = prl.requisition_line_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rt.transaction_type <> 'UNORDERED'
AND rs.quantity > 0
AND rs.supply_type_code = 'RECEIVING'
AND rs.to_organization_id = p_organization_id
AND rt.organization_id = p_organization_id
AND(
EXISTS(
SELECT 1
FROM rcv_transactions rt1
WHERE rt1.transaction_id = rt.transaction_id
AND rt1.inspection_status_code <> 'NOT INSPECTED'
AND rt1.routing_header_id = 2)
OR rt.routing_header_id <> 2
OR rt.routing_header_id IS NULL
));
SELECT DISTINCT rsl.shipment_header_id
, rsl.from_organization_id
INTO x_shipment_header_id
, x_from_org_id
FROM rcv_shipment_lines rsl, wms_lpn_contents wlc,wms_license_plate_numbers wln
WHERE rsl.item_id = wlc.inventory_item_id
AND to_organization_id = p_organization_id
AND wln.lpn_id IN ( SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id)
AND wlc.parent_lpn_id = wln.lpn_id
AND EXISTS(
SELECT 1
FROM po_requisition_lines_all prl, mtl_supply ms
WHERE prl.requisition_header_id = p_requiition_header_id
AND prl.requisition_header_id = ms.req_header_id
AND prl.requisition_line_id = ms.req_line_id
AND ms.supply_type_code = 'SHIPMENT'
AND ms.quantity > 0
AND ms.supply_source_id = rsl.shipment_line_id
AND prl.item_id = wlc.inventory_item_id
AND prl.item_id = ms.item_id);
SELECT DISTINCT rsl.shipment_header_id
, rsl.from_organization_id
INTO x_shipment_header_id
, x_from_org_id
FROM rcv_shipment_lines rsl, wms_lpn_contents wlc
WHERE rsl.item_id = wlc.inventory_item_id
AND to_organization_id = p_organization_id
AND wlc.parent_lpn_id IN ( SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id)
AND EXISTS(
SELECT 1
FROM po_requisition_lines_all prl, mtl_supply ms
WHERE prl.requisition_header_id = p_requiition_header_id
AND prl.requisition_header_id = ms.req_header_id
AND prl.requisition_line_id = ms.req_line_id
AND ms.supply_type_code = 'SHIPMENT'
AND ms.quantity > 0
AND ms.supply_source_id = rsl.shipment_line_id
AND prl.item_id = wlc.inventory_item_id
AND prl.item_id = ms.item_id);
SELECT DISTINCT rsl.shipment_header_id
, rsl.from_organization_id
INTO x_shipment_header_id
, x_from_org_id
FROM rcv_shipment_lines rsl
WHERE item_id = p_item_id
AND to_organization_id = p_organization_id
AND EXISTS(
SELECT 1
FROM po_requisition_lines prl
WHERE prl.requisition_header_id = p_requiition_header_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND prl.item_id = p_item_id);
SELECT DISTINCT rsl.shipment_header_id
, rsl.from_organization_id
INTO x_shipment_header_id
, x_from_org_id
FROM rcv_shipment_lines rsl
WHERE item_id = p_item_id
AND to_organization_id = p_organization_id
AND EXISTS(
SELECT 1
FROM po_requisition_lines prl, rcv_transactions rt, rcv_supply rs
WHERE prl.requisition_header_id = p_requiition_header_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND prl.item_id = p_item_id
AND prl.source_type_code = 'INVENTORY'
AND rs.req_line_id = prl.requisition_line_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rt.transaction_type <> 'UNORDERED'
AND rs.quantity > 0
AND rs.supply_type_code = 'RECEIVING'
AND rs.to_organization_id = p_organization_id
AND rt.organization_id = p_organization_id
AND(EXISTS(
SELECT 1
FROM rcv_transactions rt1
WHERE rt1.transaction_id = rt.transaction_id
AND rt1.inspection_status_code = 'NOT INSPECTED'
AND rt1.routing_header_id = 2)
));
SELECT serial_number_control_code
INTO x_serial_control
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_from_org_id;
SELECT COUNT(COUNT(*)) -- get the no of project/tasks combinations
INTO l_project_tasks_count
FROM po_distributions_all pod, po_lines_all pol
WHERE pod.po_header_id = p_po_header_id
AND pod.po_line_id = NVL(p_po_line_id, pod.po_line_id)
AND pod.project_id IS NOT NULL
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND (p_item_id IS NULL OR pol.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND pol.item_revision IS NOT NULL
AND pol.item_revision = p_item_rev)
)
GROUP BY pod.project_id, pod.task_id;
SELECT COUNT(*)
INTO l_distributions_count
FROM po_distributions_all pod, po_lines_all pol
WHERE pod.po_header_id = p_po_header_id
AND pod.po_line_id = NVL(p_po_line_id, pod.po_line_id)
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND (p_item_id IS NULL OR pol.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND pol.item_revision IS NOT NULL
AND pol.item_revision = p_item_rev)
);
SELECT COUNT(COUNT(*))
INTO l_project_tasks_count
FROM po_distributions_all pod, rcv_shipment_lines rsl
WHERE pod.po_header_id = rsl.po_header_id
AND rsl.shipment_header_id = p_shipment_header_id
AND (p_item_id IS NULL OR rsl.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR rsl.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND rsl.item_revision IS NOT NULL
AND rsl.item_revision = p_item_rev)
)
AND pod.project_id IS NOT NULL
GROUP BY project_id, task_id;
SELECT COUNT(COUNT(*))
INTO l_distributions_count
FROM po_distributions_all pod, rcv_shipment_lines rsl
WHERE pod.po_header_id = rsl.po_header_id
AND rsl.po_line_id = pod.po_line_id(+)
AND rsl.po_line_location_id = pod.line_location_id(+)
AND rsl.shipment_header_id = p_shipment_header_id
AND (p_item_id IS NULL OR rsl.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR rsl.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND rsl.item_revision IS NOT NULL
AND rsl.item_revision = p_item_rev)
)
GROUP BY project_id, task_id;
SELECT COUNT(COUNT(*))
INTO l_project_tasks_count
FROM po_req_distributions_all pod, po_requisition_lines_all pol
WHERE pol.requisition_header_id = p_req_header_id
AND pod.requisition_line_id = pol.requisition_line_id
AND pod.project_id IS NOT NULL
AND (p_item_id IS NULL OR pol.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND pol.item_revision IS NOT NULL
AND pol.item_revision = p_item_rev)
)
GROUP BY project_id, task_id;
SELECT COUNT(*)
INTO l_distributions_count
FROM po_req_distributions_all pod, po_requisition_lines_all pol
WHERE pol.requisition_header_id = p_req_header_id
AND pod.requisition_line_id = pol.requisition_line_id
AND (p_item_id IS NULL OR pol.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND pol.item_revision IS NOT NULL
AND pol.item_revision = p_item_rev)
);
SELECT COUNT(COUNT(*))
INTO l_project_tasks_count
FROM po_req_distributions_all pod, po_requisition_lines_all pol
WHERE pol.requisition_header_id = p_req_header_id
AND pod.requisition_line_id = pol.requisition_line_id
AND pod.project_id IS NOT NULL
AND (p_item_id IS NULL OR pol.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND pol.item_revision IS NOT NULL
AND pol.item_revision = p_item_rev)
)
GROUP BY project_id, task_id;
SELECT COUNT(*)
INTO l_distributions_count
FROM po_req_distributions_all pod, po_requisition_lines_all pol
WHERE pol.requisition_header_id = p_req_header_id
AND pod.requisition_line_id = pol.requisition_line_id
AND (p_item_id IS NULL OR pol.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND pol.item_revision IS NOT NULL
AND pol.item_revision = p_item_rev)
);
SELECT COUNT(COUNT(*))
INTO l_project_tasks_count
FROM oe_order_lines l
WHERE l.line_category_code = 'RETURN'
AND l.header_id = p_oe_header_id
AND l.project_id IS NOT NULL
AND (p_item_id IS NULL OR l.inventory_item_id = p_item_id)
AND ( (p_item_rev IS NULL OR l.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND l.item_revision IS NOT NULL
AND l.item_revision = p_item_rev)
)
GROUP BY project_id, task_id;
SELECT COUNT(*)
INTO l_distributions_count
FROM oe_order_lines l
WHERE l.line_category_code = 'RETURN'
AND l.header_id = p_oe_header_id
AND (p_item_id IS NULL OR l.inventory_item_id = p_item_id)
AND ( (p_item_rev IS NULL OR l.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND l.item_revision IS NOT NULL
AND l.item_revision = p_item_rev)
);
SELECT COUNT(COUNT(*)) -- get the no of project/tasks combinations
INTO l_project_tasks_count
FROM po_distributions_all pod, po_lines_all pol
WHERE pod.po_header_id = p_po_header_id
AND pod.project_id IS NOT NULL
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND (p_item_id IS NULL OR pol.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND pol.item_revision IS NOT NULL
AND pol.item_revision = p_item_rev)
)
GROUP BY pod.project_id, pod.task_id;
SELECT COUNT(*)
INTO l_distributions_count
FROM po_distributions_all pod, po_lines_all pol
WHERE pod.po_header_id = p_po_header_id
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND (p_item_id IS NULL OR pol.item_id = p_item_id)
AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND pol.item_revision IS NOT NULL
AND pol.item_revision = p_item_rev)
);
SELECT COUNT(COUNT(*))
INTO l_project_tasks_count
FROM oe_order_lines l
WHERE l.line_category_code = 'RETURN'
AND l.header_id = p_oe_header_id
AND l.project_id IS NOT NULL
AND (p_item_id IS NULL OR l.inventory_item_id = p_item_id)
AND ( (p_item_rev IS NULL OR l.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND l.item_revision IS NOT NULL
AND l.item_revision = p_item_rev)
)
GROUP BY project_id, task_id;
SELECT COUNT(*)
INTO l_distributions_count
FROM oe_order_lines l
WHERE l.line_category_code = 'RETURN'
AND l.header_id = p_oe_header_id
AND (p_item_id IS NULL OR l.inventory_item_id = p_item_id)
AND ( (p_item_rev IS NULL OR l.item_revision IS NULL) OR
(p_item_rev IS NOT NULL AND l.item_revision IS NOT NULL
AND l.item_revision = p_item_rev)
);
SELECT DISTINCT rsl.from_organization_id
INTO l_from_org_id
FROM rcv_shipment_lines rsl
WHERE item_id = p_item_id
AND to_organization_id = p_to_org_id
AND shipment_header_id = p_ship_head_id
AND EXISTS(
SELECT 1
FROM po_requisition_lines prl
WHERE prl.requisition_header_id = p_requisition_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND prl.item_id = p_item_id);
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_cross_reference;
SELECT cross_reference
INTO l_cross_reference
FROM mtl_cross_references
WHERE cross_reference = p_cross_reference
AND cross_reference_type = p_cross_reference_type
AND organization_id = p_organization_id;
INSERT INTO mtl_cross_references
(
inventory_item_id
, organization_id
, cross_reference_type
, cross_reference
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, org_independent_flag
)
VALUES (
p_inventory_item_id
, p_organization_id
, p_cross_reference_type
, p_cross_reference
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, 'N'
);
SELECT lot_control_code
INTO x_lot_control
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_from_org_id;
SELECT DISTINCT rsl.from_organization_id
INTO l_from_org_id
FROM rcv_shipment_lines rsl
WHERE item_id = p_item_id
AND to_organization_id = p_to_org_id
AND shipment_header_id = p_ship_head_id
AND EXISTS(
SELECT 1
FROM po_requisition_lines prl
WHERE prl.requisition_header_id = p_requisition_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND prl.item_id = p_item_id);
SELECT pod.task_id
INTO l_task_id
FROM po_distributions_all pod, po_lines_all pol
WHERE pod.po_header_id = p_po_header_id
AND pod.po_line_id = NVL(p_po_line_id, pod.po_line_id)
AND pod.project_id = p_project_id
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND(p_item_id IS NULL
OR pol.item_id = p_item_id)
AND(p_item_rev IS NULL
OR pol.item_revision = p_item_rev);
SELECT pod.task_id
INTO l_task_id
FROM po_distributions_all pod, rcv_shipment_lines rsl
WHERE pod.po_header_id = rsl.po_header_id
AND rsl.po_line_id = pod.po_line_id(+)
AND rsl.po_line_location_id = pod.line_location_id(+)
AND rsl.shipment_header_id = p_shipment_header_id
AND pod.project_id = p_project_id
AND(p_item_id IS NULL
OR rsl.item_id = p_item_id)
AND(p_item_rev IS NULL
OR rsl.item_revision = p_item_rev);
SELECT pod.task_id
INTO l_task_id
FROM po_req_distributions_all pod, po_requisition_lines_all pol
WHERE pol.requisition_header_id = p_req_header_id
AND pod.requisition_line_id = pol.requisition_line_id
AND pod.project_id = p_project_id
AND(p_item_id IS NULL
OR pol.item_id = p_item_id)
AND(p_item_rev IS NULL
OR pol.item_revision = p_item_rev);
SELECT l.task_id
INTO l_task_id
FROM oe_order_lines l
WHERE l.line_category_code = 'RETURN'
AND l.header_id = p_oe_header_id
AND l.project_id = p_project_id
AND(p_item_id IS NULL
OR l.inventory_item_id = p_item_id)
AND(p_item_rev IS NULL
OR l.item_revision = p_item_rev);
SELECT pod.task_id
INTO l_task_id
FROM po_distributions_all pod, po_lines_all pol
WHERE pod.po_header_id = p_po_header_id
AND pod.project_id = p_project_id
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND(p_item_id IS NULL
OR pol.item_id = p_item_id)
AND(p_item_rev IS NULL
OR pol.item_revision = p_item_rev)
GROUP BY pod.project_id, pod.task_id;
SELECT task_id
INTO l_task_id
FROM oe_order_lines l
WHERE l.line_category_code = 'RETURN'
AND l.header_id = p_oe_header_id
AND l.project_id = p_project_id
AND(p_item_id IS NULL
OR l.inventory_item_id = p_item_id)
AND(p_item_rev IS NULL
OR l.item_revision = p_item_rev)
GROUP BY project_id, task_id;
/*SELECT 'Y'
INTO x_lpn_flag
FROM wms_license_plate_numbers lpn, wms_lpn_contents wlc
WHERE lpn.lpn_id = wlc.parent_lpn_id
AND lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers wln
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id);*/
/*SELECT 'Y'
INTO x_lpn_flag
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers wln
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id);*/
SELECT 'Y'
INTO x_lpn_flag
FROM wms_license_plate_numbers wln
WHERE EXISTS (SELECT '1'
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = wln.lpn_id)
START WITH wln.lpn_id = p_lpn_id
CONNECT BY wln.parent_lpn_id = PRIOR wln.lpn_id;
SELECT 'Y'
INTO x_lpn_flag
FROM wms_license_plate_numbers lpn, wms_lpn_contents wlc, rcv_transactions_interface rti
WHERE lpn.lpn_id = wlc.parent_lpn_id
AND lpn.lpn_id = rti.lpn_id
AND rti.transaction_status_code = 'PENDING'
AND lpn.lpn_id IN(SELECT lpn_id
FROM wms_license_plate_numbers wln
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id);
SELECT lpn_context
, subinventory_code
, locator_id
INTO x_lpn_context
, l_sub_code
, l_locator_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT subinventory
, locator_id
, location_id
, auto_transact_code
INTO l_sub_code
, l_locator_id
, l_location_id
, l_auto_transact_code
FROM rcv_transactions_interface
WHERE transfer_lpn_id = p_lpn_id
AND transaction_type = 'RECEIVE'
AND transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR'
AND ROWNUM = 1;
SELECT to_subinventory
, to_locator_id
, location_id
INTO l_sub_code
, l_locator_id
, l_location_id
FROM rcv_supply
WHERE lpn_id = p_lpn_id
AND to_organization_id = p_organization_id
AND ROWNUM = 1;
SELECT location_code
, location_id
INTO x_location_code
, x_location_id
from hr_locations hl
WHERE EXISTS
( SELECT 1
FROM mtl_secondary_inventories msi
WHERE organization_id = p_organization_id
AND secondary_inventory_name = l_sub_code
AND msi.location_id = hl.location_id)
AND ROWNUM = 1;
SELECT location_code
, location_id
INTO x_location_code
, x_location_id
FROM hr_locations hl
WHERE location_id = l_location_id
AND ROWNUM = 1;
SELECT inv_project.get_locsegs(inventory_location_id, organization_id)
INTO x_locator_segs
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND inventory_location_id = l_locator_id;
SELECT 'N' INTO x_lpn_flag
FROM rcv_transactions_interface
WHERE lpn_id IN ( SELECT lpn_id FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id)
AND transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR'
AND ROWNUM = 1 ;
SELECT segment1
INTO l_req_num
FROM po_requisition_headers_all
WHERE requisition_header_id = p_req_id;
SELECT header_id
INTO l_order_header_id
FROM oe_order_headers_all
WHERE orig_sys_document_ref = l_req_num
AND order_source_id = 10;
SELECT 'Y' INTO x_lpn_flag
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd1
WHERE wdd.lpn_id IN
(SELECT lpn_id FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id)
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wda.delivery_detail_id = wdd1.delivery_detail_id
AND NVL(wdd.line_direction,'O') IN ('O','IO')
AND wdd1.source_header_id = l_order_header_id
AND ROWNUM = 1;
FOR l_lpn_rec IN (SELECT lpn_id FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id) LOOP
BEGIN
SELECT 'N' INTO x_lpn_flag
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd1
WHERE wdd.lpn_id = l_lpn_rec.lpn_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wda.delivery_detail_id = wdd1.delivery_detail_id
AND NVL(wdd.line_direction,'O') IN ('O','IO')
AND wdd1.source_header_id <> l_order_header_id
AND ROWNUM = 1;
SELECT 1 INTO x_count_of_lpns
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd1
WHERE wdd.lpn_id NOT IN
(SELECT lpn_id FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id)
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wda.delivery_detail_id = wdd1.delivery_detail_id
AND NVL(wdd.line_direction,'O') IN ('O','IO')
AND wdd1.source_header_id = l_order_header_id
AND NOT EXISTS
(SELECT lpn_id FROM rcv_transactions_interface
WHERE lpn_id = wdd.lpn_id
AND transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR')
AND ROWNUM = 1;
SELECT 1 INTO x_count_of_lpns
FROM wms_license_plate_numbers wlpn1, rcv_shipment_headers rsh
WHERE rsh.shipment_num = p_shipment_num
AND wlpn1.source_name = rsh.shipment_num
AND ((wlpn1.lpn_context = 6 AND wlpn1.organization_id = rsh.organization_id) OR
(wlpn1.lpn_context = 7 AND wlpn1.organization_id = rsh.ship_to_org_id))
AND EXISTS (SELECT wlpn2.lpn_id
FROM wms_license_plate_numbers wlpn2
START WITH wlpn2.lpn_id = wlpn1.lpn_id
CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
INTERSECT
SELECT rsl.asn_lpn_id
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND NOT exists (SELECT 1
FROM rcv_transactions_interface rti
WHERE rti.lpn_id = rsl.asn_lpn_id
AND rti.transfer_lpn_id = rsl.asn_lpn_id
AND rti.to_organization_id = rsl.to_organization_id
AND rti.processing_status_code <> 'ERROR'
AND rti.transaction_status_code <> 'ERROR'
)
AND rsl.asn_lpn_id NOT IN (SELECT wlpn3.lpn_id
FROM wms_license_plate_numbers wlpn3
START WITH wlpn3.lpn_id = p_lpn_id
CONNECT BY PRIOR wlpn3.lpn_id = wlpn3.parent_lpn_id
)
);
inv_rcv_std_rcpt_apis.g_rcpt_lot_qty_rec_tb.DELETE;
Select org_id
into l_operating_unit_id
from po_headers_all
where po_header_id = p_po_header_id;
Select org_id
into l_operating_unit_id
from po_requisition_lines_all
where requisition_line_id = p_req_line_id;
Select org_id
into l_operating_unit_id
from oe_order_headers_all
where header_id = p_oe_order_header_id;
SELECT count(DISTINCT rsl.shipment_header_id)
INTO x_open_shipments
FROM rcv_shipment_lines rsl, po_requisition_lines prl
WHERE to_organization_id = p_organization_id
AND nvl(rsl.shipment_line_status_code, ' ') <> 'FULLY RECEIVED'
AND prl.requisition_header_id = p_requisition_header_id
AND rsl.requisition_line_id = prl.requisition_line_id;
SELECT DISTINCT mum.uom_code
INTO l_uom_code
FROM rcv_transactions rt , rcv_shipment_lines rsl, mtl_units_of_measure mum
WHERE rt.transaction_type = 'RECEIVE'
AND rsl.item_id = p_item_id
AND rt.organization_id = p_organization_id
AND rsl.shipment_header_id = rt.shipment_header_id
AND rt.unit_of_measure IS NOT NULL
AND rt.shipment_header_id = p_shipment_header_id
AND mum.unit_of_measure(+) = rt.unit_of_measure;