[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT unit_of_measure
INTO l_unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code;
SELECT wdd.lpn_id
into l_lpn_id
FROM wsh_delivery_Details wdd, wsh_delivery_assignments wda
WHERE wdd.delivery_detail_id(+) = wda.parent_delivery_detail_id
AND wda.delivery_detail_id = p_wdd_id;
DELETE FROM rcv_transactions_interface
WHERE interface_transaction_id = p_bkup_rti_id;
asn_debug.put_line('DELETED RTI');
DELETE FROM rcv_lots_interface
WHERE interface_transaction_id = p_bkup_rti_id;
asn_debug.put_line('DELETED RLI');
DELETE FROM rcv_serials_interface
WHERE interface_transaction_id = p_bkup_rti_id;
asn_debug.put_line('DELETED RSI');
DELETE FROM mtl_transaction_lots_temp
WHERE product_transaction_id = p_bkup_rti_id;
asn_debug.put_line('DELETED MTLT');
DELETE FROM mtl_serial_numbers_temp
WHERE product_transaction_id = p_bkup_rti_id;
asn_debug.put_line('DELETED MSNT');
SELECT lpn_id,
interface_transaction_id,
to_organization_id
INTO l_lpn_id,
l_new_rti_id,
l_new_org_id
FROM rcv_transactions_interface
WHERE group_id = p_group_id
AND interface_source_line_id = p_wdd_rec.delivery_detail_id;
SELECT rtv_rti.interface_transaction_id,
rtv_rti.transaction_type,
rtv_rti.item_id,
rtv_rti.item_revision,
rtv_rti.use_mtl_serial,
rtv_rti.use_mtl_lot,
rtv_rti.to_organization_id,
new_rti.from_subinventory,
new_rti.from_locator_id,
new_rti.transfer_lpn_id,
new_rti.uom_code,
sum(new_rti.quantity) quantity
FROM rcv_transactions_interface new_rti,
rcv_transactions_interface rtv_rti,
wsh_delivery_Details wdd
WHERE new_rti.group_id = p_group_id
AND new_rti.interface_source_line_id IS NOT NULL
AND new_rti.transfer_lpn_id IS NOT NULL
AND new_rti.processing_mode_code = 'ONLINE'
AND new_rti.interface_source_line_id = wdd.delivery_detail_id
AND rtv_rti.interface_transaction_id = wdd.source_line_id
AND rtv_rti.group_id = wdd.source_header_id
AND rtv_rti.processing_status_code = 'WSH_INTERFACED'
AND wdd.source_code = 'RTV'
AND wdd.container_flag = 'N'
GROUP BY rtv_rti.interface_transaction_id, rtv_rti.transaction_type,new_rti.transfer_lpn_id,
rtv_rti.item_id,rtv_rti.item_revision,rtv_rti.use_mtl_serial, rtv_rti.use_mtl_lot,
rtv_rti.to_organization_id, new_rti.from_subinventory,new_rti.from_locator_id,
new_rti.uom_code;
SELECT *
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_interface_txn_id;
SELECT *
INTO rti_rec
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_txn_id;
select subinventory_code,locator_id
into l_subinventory, l_locator_id
from wms_license_plate_numbers
where lpn_id = rti_rec.TRANSFER_LPN_ID;
l_wdd_tbl(1).last_update_date := rti_rec.last_update_date;
l_wdd_tbl(1).last_update_login := rti_rec.last_update_login;
l_wdd_tbl(1).last_updated_by := rti_rec.last_updated_by;
SELECT substr (nvl(max(to_number(source_line_number)),0.1)+1, 1, instr(nvl(max(to_number(source_line_number)),0.1)+1,'.')-1) || '.1'
INTO l_wdd_tbl(1).source_line_number
FROM wsh_delivery_details
WHERE source_header_number = to_char(rti_rec.group_id)
AND source_code = 'RTV';
SELECT invoice_currency_code
INTO l_currency
FROM ap_supplier_sites_all
WHERE vendor_id = rti_rec.vendor_id
AND vendor_site_id = rti_rec.vendor_site_id;
SELECT currency_code, set_of_books_id
INTO l_functional_currency, l_sob_id
FROM cst_organization_definitions
WHERE organization_id = p_return_org_id;
SELECT NVL (pll.price_override, pol.unit_price)
INTO l_price
FROM po_line_locations_all pll,
po_lines_all pol
WHERE pol.po_line_id = pll.po_line_id
AND pol.po_line_id = rti_rec.po_line_id
AND pll.line_location_id = rti_rec.po_line_location_id;
SELECT msi.primary_uom_code,
msi.primary_unit_of_measure,
msi.weight_uom_code,
msi.unit_weight,
wsh_wv_utils.convert_uom
(msi.weight_uom_code,
msi.weight_uom_code,
(msi.unit_weight * wsh_wv_utils.convert_uom( rti_rec.uom_code,
msi.primary_uom_code,
rti_rec.quantity,
rti_rec.item_id) ),
rti_rec.item_id) WEIGHT,
msi.volume_uom_code,
msi.unit_volume,
wsh_wv_utils.convert_uom
(msi.volume_uom_code,
msi.volume_uom_code,
(msi.unit_volume * wsh_wv_utils.convert_uom( rti_rec.uom_code,
msi.primary_uom_code,
rti_rec.quantity,
rti_rec.item_id) ),
rti_rec.item_id) VOLUME
INTO l_wdd_tbl(1).requested_quantity_uom,
l_primary_uom,
l_wdd_tbl(1).weight_uom_code,
l_wdd_tbl(1).unit_weight,
l_wdd_tbl(1).net_weight,
l_wdd_tbl(1).volume_uom_code,
l_wdd_tbl(1).unit_volume,
l_wdd_tbl(1).volume
FROM mtl_system_items msi
WHERE msi.inventory_item_id = rti_rec.item_id
AND msi.organization_id = p_return_org_id;
wsh_interface_grp.create_update_delivery_detail
( p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_wdd_tbl,
p_IN_rec => l_IN_rec,
x_OUT_rec => l_OUT_rec );
wms_return_sv.Create_Update_Containers_RTV
( x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_interface_txn_id => rti_rec.interface_transaction_id,
p_wdd_table => l_wdd_tbl);
wsh_interface_grp.create_update_delivery_detail
( p_api_version_number => 1.0,
p_init_msg_list => NULL,
p_commit => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_wdd_tbl,
p_IN_rec => l_IN_rec,
x_OUT_rec => l_OUT_rec );
wms_return_sv.Create_Update_Containers_RTV
( x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_interface_txn_id => rti_rec.interface_transaction_id,
p_wdd_table => l_wdd_tbl);
SELECT wdd.*
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
rcv_transactions_interface rti
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag <> 'Y'
AND wdd.container_flag = 'N'
AND wdd.source_line_id = rti.interface_transaction_id
AND rti.processing_status_code = 'WSH_INTERFACED'
AND wdd.organization_id = rti.to_organization_id
AND rti.shipment_line_id IS NULL
ORDER BY source_line_id, source_line_number;
SELECT wdd.*
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
rcv_transactions_interface rti
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag <> 'Y'
AND wdd.container_flag = 'N'
AND wdd.source_line_id = rti.interface_transaction_id
AND rti.processing_status_code = 'WSH_INTERFACED'
AND wdd.organization_id = rti.to_organization_id
AND rti.shipment_line_id IS NOT NULL
AND NOT EXISTS (SELECT 1 from rcv_transactions rt
WHERE rt.transaction_type = 'RETURN TO VENDOR'
AND rt.interface_source_line_id = wdd.delivery_detail_id)
ORDER BY source_line_id, source_line_number;
SELECT wdd.*
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
rcv_transactions_interface rti
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag <> 'Y'
AND wdd.container_flag = 'N'
AND wdd.source_line_id = rti.interface_transaction_id
AND rti.processing_status_code = 'WSH_INTERFACED'
AND wdd.organization_id <> rti.to_organization_id
AND rti.shipment_line_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions mmt
WHERE mmt.picking_line_id = wdd.delivery_detail_id)
ORDER BY source_line_id, source_line_number;
SELECT wdd.*
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
rcv_transactions_interface rti
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag <> 'Y'
AND wdd.container_flag = 'N'
AND wdd.source_line_id = rti.interface_transaction_id
AND rti.processing_status_code = 'WSH_INTERFACED'
AND wdd.organization_id <> rti.to_organization_id
AND rti.shipment_line_id IS NOT NULL
AND EXISTS (SELECT 1
FROM mtl_material_transactions mmt
WHERE mmt.picking_line_id = wdd.delivery_detail_id)
AND NOT EXISTS (SELECT 1 from rcv_transactions rt
WHERE rt.transaction_type = 'RETURN TO VENDOR'
AND rt.interface_source_line_id = wdd.delivery_detail_id)
ORDER BY source_line_id, source_line_number;
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
rcv_transactions rt
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag <> 'Y'
AND wdd.container_flag = 'N'
AND wdd.delivery_detail_id = rt.interface_source_line_id
AND rt.transaction_type = 'RETURN TO VENDOR'
FOR UPDATE OF inv_interfaced_flag nowait;
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
mtl_material_transactions mmt
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag <> 'Y'
AND wdd.container_flag = 'N'
AND wdd.delivery_detail_id = mmt.picking_line_id
AND mmt.transaction_type_id = 1005
FOR UPDATE OF inv_interfaced_flag nowait;
SELECT mtl_material_transactions_s.nextval INTO l_header_id FROM DUAL; -- Bug 11831232
perform_post_TM_updates ('INV', p_delivery_id);
SELECT rcv_interface_groups_s.nextval INTO l_group_id FROM DUAL; -- Bug 11831232
perform_post_TM_updates ('RCV', p_delivery_id);
SELECT mtl_material_transactions_s.nextval INTO l_header_id FROM DUAL; -- Bug 11831232
SELECT rcv_interface_groups_s.nextval INTO l_group_id FROM DUAL; -- Bug 11831232
perform_post_TM_updates ('RCV', p_delivery_id);
WSH_INTEGRATION.update_delivery_details
( p_detail_rows => l_detail_rows,
x_return_status => l_return_status);
SELECT COUNT(*)
INTO l_rcv_count
FROM rcv_transactions_interface
WHERE group_id = p_group_id;
SELECT COUNT(*)
INTO l_rcv_count
FROM rcv_transactions
WHERE group_id = p_group_id;
SELECT *
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_wdd_rec.transaction_temp_id;
SELECT rt.subinventory, rt.locator_id, rt.organization_id, rti.rma_reference, mp.WMS_ENABLED_FLAG -- Bug 12974284
INTO l_deliver_subinv, l_deliver_locator, l_receipt_org, l_txn_reference, l_wms_rec_org -- Bug 12974284
FROM rcv_transactions rt,
rcv_transactions_interface rti,
mtl_parameters mp
WHERE rt.transaction_id = rti.parent_transaction_id
AND rti.interface_transaction_id = p_wdd_rec.source_line_id
AND rt.transaction_type = 'DELIVER'
AND mp.organization_id = rt.organization_id;
SELECT ap_accrual_account
INTO l_account_id
FROM mtl_parameters
WHERE organization_id = p_wdd_rec.organization_id;
SELECT currency_code, operating_unit
INTO l_functional_currency, l_ou_id
FROM cst_organization_definitions
WHERE organization_id = p_wdd_rec.organization_id;
SELECT wts.actual_departure_date
INTO l_txn_date
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wnd.initial_pickup_location_id = wts.stop_location_id
AND wnd.delivery_id = p_delivery_id;
SELECT rma_reference
INTO l_txn_reference
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_wdd_rec.source_line_id; -- Bug 12974284
SELECT mtl_material_transactions_s.nextval INTO l_temp_id FROM DUAL; -- Bug 11831232
INSERT INTO mtl_transactions_interface
( transaction_header_id,
transaction_interface_id,
source_code,
transaction_source_name,
source_header_id,
source_line_id,
picking_line_id,
process_flag,
validation_required,
transaction_mode,
lock_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_item_id,
revision,
transaction_quantity,
transaction_uom,
secondary_transaction_quantity, -- Bug 12768025
secondary_uom_code, -- Bug 12768025
transaction_date,
organization_id,
transfer_organization,
subinventory_code,
transfer_subinventory,
locator_id,
transfer_locator,
transaction_source_type_id,
transaction_type_id,
transaction_action_id,
distribution_account_id,
currency_code,
transaction_cost,
transaction_reference, -- Bug 12974284
lpn_id, -- RTV2 rtv project phase 2
transfer_lpn_id, -- RTV2 rtv project phase 2
content_lpn_id -- RTV2 rtv project phase 2
)
SELECT p_header_id,
l_temp_id,
p_txn_desc,
p_wdd_rec.source_header_number,
p_wdd_rec.source_header_id,
p_wdd_rec.source_line_id,
p_wdd_rec.delivery_detail_id,
1,
2,
3,
2,
sysdate,
p_wdd_rec.last_updated_by,
sysdate,
p_wdd_rec.created_by,
p_wdd_rec.last_update_login,
p_wdd_rec.inventory_item_id,
p_wdd_rec.revision,
decode(p_txn_desc,'Direct Transfer',p_wdd_rec.shipped_quantity, 'Issue out', p_wdd_rec.shipped_quantity * -1),
p_wdd_rec.requested_quantity_uom,
decode(p_txn_desc,'Direct Transfer',p_wdd_rec.shipped_quantity2, 'Issue out', p_wdd_rec.shipped_quantity2 * -1), -- Bug 12768025
p_wdd_rec.requested_quantity_uom2, -- Bug 12768025
l_txn_date,
p_wdd_rec.organization_id,
l_receipt_org,
p_wdd_rec.subinventory,
decode(p_txn_desc,'Direct Transfer', l_deliver_subinv, 'Issue out', NULL),
p_wdd_rec.locator_id,
decode(p_txn_desc,'Direct Transfer', l_deliver_locator, 'Issue out', NULL),
13,
decode(p_txn_desc,'Direct Transfer', 3, 'Issue out', 1005),
decode(p_txn_desc,'Direct Transfer', 3, 'Issue out', 1),
l_account_id,
decode(p_txn_desc,'Direct Transfer', NULL, 'Issue out', l_functional_currency),
l_txn_cost,
l_txn_reference, -- Bug 12974284
l_lpn_id, -- RTV2 rtv project phase 2
l_transfer_lpn_id, -- RTV2 rtv project phase 2
l_transfer_lpn_id -- RTV2 rtv project phase 2
FROM DUAL;
asn_debug.put_line('Inserted MTI with transaction_interface_id : ' || l_temp_id);
SELECT *
INTO rti_rec
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_wdd_rec.source_line_id;
SELECT WMS_ENABLED_FLAG
INTO l_rec_wms_org
FROM mtl_parameters
WHERE organization_id = rti_rec.to_organization_id;
SELECT revision_qty_control_code
INTO l_rev_control
FROM mtl_system_items msi
WHERE msi.organization_id = rti_rec.to_organization_id
AND msi.inventory_item_id = rti_rec.item_id;
SELECT rt.subinventory, rt.locator_id
INTO l_from_subinventory, l_from_locator_id
FROM rcv_transactions rt
WHERE rt.transaction_id = rti_rec.parent_transaction_id
AND rt.transaction_type = 'DELIVER';
SELECT wts.actual_departure_date
INTO l_txn_date
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wnd.initial_pickup_location_id = wts.stop_location_id
AND wnd.delivery_id = p_delivery_id;
SELECT rcv_transactions_interface_s.nextval INTO l_rti_id FROM DUAL; -- Bug 11831232
INSERT INTO rcv_transactions_interface
( receipt_source_code,
interface_transaction_id,
interface_source_line_id,
group_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
source_document_code,
destination_type_code,
transaction_date,
quantity,
unit_of_measure,
secondary_quantity,
secondary_unit_of_measure,
primary_quantity,
primary_unit_of_measure,
uom_code,
shipment_header_id,
shipment_line_id,
substitute_unordered_code,
employee_id,
parent_transaction_id,
inspection_status_code,
inspection_quality_code,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_revision_num,
po_unit_price,
currency_code,
currency_conversion_rate,
currency_conversion_date,
currency_conversion_type,
routing_header_id,
routing_step_id,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
transaction_type,
location_id,
processing_status_code,
processing_mode_code,
transaction_status_code,
category_id,
vendor_lot_num,
reason_id,
item_id,
item_revision,
to_organization_id,
deliver_to_location_id,
destination_context,
vendor_id,
deliver_to_person_id,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
from_organization_id,
receipt_exception_flag,
department_code,
item_description,
movement_id,
use_mtl_lot,
use_mtl_serial,
rma_reference,
ussgl_transaction_code,
government_context,
vendor_site_id,
oe_order_header_id,
oe_order_line_id,
customer_id,
customer_site_id,
create_debit_memo_flag,
lpn_id,
transfer_lpn_id,
lpn_group_id,
from_subinventory,
from_locator_id,
subinventory,
locator_id,
org_id,
lcm_shipment_line_id,
unit_landed_cost,
validation_flag
)
VALUES
( rti_rec.receipt_source_code,
l_rti_id,
p_wdd_rec.delivery_detail_id,
p_group_id,
sysdate,
rti_rec.last_updated_by,
rti_rec.created_by,
sysdate,
rti_rec.last_update_login,
rti_rec.source_document_code,
rti_rec.destination_type_code,
l_txn_date,
p_wdd_rec.shipped_quantity,
l_shipped_uom,
p_wdd_rec.shipped_quantity2,
l_shipped_uom2,
l_primary_qty,
rti_rec.primary_unit_of_measure,
p_wdd_rec.requested_quantity_uom, -- Bug 14340673
rti_rec.shipment_header_id,
rti_rec.shipment_line_id,
rti_rec.substitute_unordered_code,
rti_rec.employee_id,
rti_rec.parent_transaction_id,
rti_rec.inspection_status_code,
rti_rec.inspection_quality_code,
rti_rec.po_header_id,
rti_rec.po_release_id,
rti_rec.po_line_id,
rti_rec.po_line_location_id,
rti_rec.po_distribution_id,
rti_rec.po_revision_num,
rti_rec.po_unit_price,
rti_rec.currency_code,
rti_rec.currency_conversion_rate,
rti_rec.currency_conversion_date,
rti_rec.currency_conversion_type,
rti_rec.routing_header_id,
rti_rec.routing_step_id,
rti_rec.comments,
rti_rec.attribute_category,
rti_rec.attribute1,
rti_rec.attribute2,
rti_rec.attribute3,
rti_rec.attribute4,
rti_rec.attribute5,
rti_rec.attribute6,
rti_rec.attribute7,
rti_rec.attribute8,
rti_rec.attribute9,
rti_rec.attribute10,
rti_rec.attribute11,
rti_rec.attribute12,
rti_rec.attribute13,
rti_rec.attribute14,
rti_rec.attribute15,
rti_rec.transaction_type,
rti_rec.location_id,
'PENDING',
'ONLINE',
'PENDING',
rti_rec.category_id,
rti_rec.vendor_lot_num,
rti_rec.reason_id,
rti_rec.item_id,
rti_rec.item_revision,
rti_rec.to_organization_id,
rti_rec.deliver_to_location_id,
rti_rec.destination_context,
rti_rec.vendor_id,
rti_rec.deliver_to_person_id,
rti_rec.wip_entity_id,
rti_rec.wip_line_id,
rti_rec.wip_repetitive_schedule_id,
rti_rec.wip_operation_seq_num,
rti_rec.wip_resource_seq_num,
rti_rec.bom_resource_id,
rti_rec.from_organization_id,
rti_rec.receipt_exception_flag,
rti_rec.department_code,
rti_rec.item_description,
rti_rec.movement_id,
rti_rec.use_mtl_lot,
rti_rec.use_mtl_serial,
rti_rec.rma_reference,
rti_rec.ussgl_transaction_code,
rti_rec.government_context,
rti_rec.vendor_site_id,
rti_rec.oe_order_header_id,
rti_rec.oe_order_line_id,
rti_rec.customer_id,
rti_rec.customer_site_id,
rti_rec.create_debit_memo_flag,
rti_rec.lpn_id,
rti_rec.transfer_lpn_id,
rti_rec.lpn_group_id,
l_from_subinventory,
l_from_locator_id,
rti_rec.subinventory,
rti_rec.locator_id,
rti_rec.org_id,
rti_rec.lcm_shipment_line_id,
rti_rec.unit_landed_cost,
'Y'
);
asn_debug.put_line('Inserted RTI with transaction_interface_id : ' || l_rti_id);
SELECT *
FROM wsh_serial_numbers
WHERE delivery_detail_id = p_wdd_rec.delivery_detail_id;
l_lot_inserted BOOLEAN := FALSE;
l_serial_inserted BOOLEAN := FALSE;
SELECT msi.lot_control_code, msi.serial_number_control_code,
rti.to_organization_id, rti.item_id
INTO l_lot_control, l_serial_control, l_rti_org_id, l_rti_item_id
FROM mtl_system_items msi,
rcv_transactions_interface rti
WHERE msi.organization_id = rti.to_organization_id
AND msi.inventory_item_id = rti.item_id
AND rti.interface_transaction_id = p_parent_id;
SELECT mtl_material_transactions_s.nextval INTO l_temp_id FROM DUAL; -- Bug 11831232
INSERT INTO mtl_transaction_lots_interface
( transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
lot_number,
transaction_quantity,
primary_quantity,
product_code,
product_transaction_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
SELECT l_temp_id,
sysdate,
p_wdd_rec.last_updated_by,
sysdate,
p_wdd_rec.created_by,
p_wdd_rec.last_update_login,
p_wdd_rec.lot_number,
p_wdd_rec.shipped_quantity,
p_wdd_rec.shipped_quantity,
mtlt.product_code,
l_prod_txn_id,
mtlt.attribute_category,
mtlt.attribute1,
mtlt.attribute2,
mtlt.attribute3,
mtlt.attribute4,
mtlt.attribute5,
mtlt.attribute6,
mtlt.attribute7,
mtlt.attribute8,
mtlt.attribute9,
mtlt.attribute10,
mtlt.attribute11,
mtlt.attribute12,
mtlt.attribute13,
mtlt.attribute14,
mtlt.attribute15
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id (+) = p_wdd_rec.source_line_id
AND mtlt.lot_number = p_wdd_rec.lot_number;
l_lot_inserted := TRUE;
asn_debug.put_line('Inserted MTLI for Lot# : ' || p_wdd_rec.lot_number);
IF (l_lot_inserted) THEN
SELECT mtl_material_transactions_s.nextval INTO l_serial_temp_id FROM DUAL; -- Bug 11831232
SELECT mtl_material_transactions_s.nextval INTO l_serial_temp_id FROM DUAL; -- Bug 11831232
UPDATE mtl_serial_numbers
SET current_organization_id = l_rti_org_id
WHERE inventory_item_id = l_rti_item_id
AND current_organization_id = p_wdd_rec.organization_id
AND serial_number between wsn_rec.fm_serial_number and nvl(wsn_rec.to_serial_number,wsn_rec.fm_serial_number) -- Bug 10120533
AND length(serial_number) = length(wsn_rec.fm_serial_number); -- Bug 10120533
INSERT INTO mtl_serial_numbers_interface
( transaction_interface_id,
product_code,
product_transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
serial_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,
territory_code,
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
)
SELECT
l_serial_temp_id,
'RCV',
l_prod_txn_id,
sysdate,
wsn_rec.last_updated_by,
sysdate,
wsn_rec.created_by,
wsn_rec.last_update_login,
wsn_rec.fm_serial_number,
wsn_rec.to_serial_number,
wsn_rec.attribute_category,
wsn_rec.attribute1,
wsn_rec.attribute2,
wsn_rec.attribute3,
wsn_rec.attribute4,
wsn_rec.attribute5,
wsn_rec.attribute6,
wsn_rec.attribute7,
wsn_rec.attribute8,
wsn_rec.attribute9,
wsn_rec.attribute10,
wsn_rec.attribute11,
wsn_rec.attribute12,
wsn_rec.attribute13,
wsn_rec.attribute14,
wsn_rec.attribute15,
wsn_rec.serial_attribute_category,
wsn_rec.c_attribute1,
wsn_rec.c_attribute2,
wsn_rec.c_attribute3,
wsn_rec.c_attribute4,
wsn_rec.c_attribute5,
wsn_rec.c_attribute6,
wsn_rec.c_attribute7,
wsn_rec.c_attribute8,
wsn_rec.c_attribute9,
wsn_rec.c_attribute10,
wsn_rec.c_attribute11,
wsn_rec.c_attribute12,
wsn_rec.c_attribute13,
wsn_rec.c_attribute14,
wsn_rec.c_attribute15,
wsn_rec.c_attribute16,
wsn_rec.c_attribute17,
wsn_rec.c_attribute18,
wsn_rec.c_attribute19,
wsn_rec.c_attribute20,
wsn_rec.d_attribute1,
wsn_rec.d_attribute2,
wsn_rec.d_attribute3,
wsn_rec.d_attribute4,
wsn_rec.d_attribute5,
wsn_rec.d_attribute6,
wsn_rec.d_attribute7,
wsn_rec.d_attribute8,
wsn_rec.d_attribute9,
wsn_rec.d_attribute10,
wsn_rec.n_attribute1,
wsn_rec.n_attribute2,
wsn_rec.n_attribute3,
wsn_rec.n_attribute4,
wsn_rec.n_attribute5,
wsn_rec.n_attribute6,
wsn_rec.n_attribute7,
wsn_rec.n_attribute8,
wsn_rec.n_attribute9,
wsn_rec.n_attribute10,
wsn_rec.territory_code,
wsn_rec.time_since_new,
wsn_rec.cycles_since_new,
wsn_rec.time_since_overhaul,
wsn_rec.cycles_since_overhaul,
wsn_rec.time_since_repair,
wsn_rec.cycles_since_repair,
wsn_rec.time_since_visit,
wsn_rec.cycles_since_visit,
wsn_rec.time_since_mark,
wsn_rec.cycles_since_mark,
wsn_rec.number_of_repairs
FROM dual;
l_serial_inserted := TRUE;
asn_debug.put_line('Inserted MSNI : fm_serial_number : ' || wsn_rec.fm_serial_number || ' and to_serial_number : ' || wsn_rec.to_serial_number );
IF (l_lot_inserted AND l_serial_inserted) THEN
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = l_serial_temp_id
WHERE transaction_interface_id = l_temp_id;
PROCEDURE NAME: perform_post_TM_updates ()
===========================================================================*/
PROCEDURE perform_post_TM_updates
( p_TM_source IN VARCHAR2,
p_delivery_id IN NUMBER) IS
-- Cursor for picking successfully processed return RT lines
CURSOR wdd_rt_cursor IS
SELECT wdd.delivery_detail_id,
wdd.inventory_item_id,
wdd.shipped_quantity,
wdd.requested_quantity_uom shipped_uom_code,
wdd.shipped_quantity2,
wdd.requested_quantity_uom2 shipped_uom_code2,
rti.interface_transaction_id bkup_rti_id,
rti.quantity bkup_rti_quantity,
rti.unit_of_measure bkup_rti_uom,
rti.primary_unit_of_measure bkup_rti_puom,
rti.secondary_unit_of_measure bkup_rti_suom,
rti.source_doc_unit_of_measure bkup_rti_src_uom
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
rcv_transactions rt,
rcv_transactions_interface rti
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag <> 'Y'
AND wdd.container_flag = 'N'
AND wdd.delivery_detail_id = rt.interface_source_line_id
AND rt.transaction_type = 'RETURN TO VENDOR'
AND wdd.source_line_id = rti.interface_transaction_id
AND rti.processing_status_code = 'WSH_INTERFACED'
ORDER BY bkup_rti_id, delivery_detail_id
FOR UPDATE;
SELECT wdd.delivery_detail_id,
wdd.inventory_item_id,
wdd.shipped_quantity,
wdd.requested_quantity_uom shipped_uom_code,
wdd.shipped_quantity2,
wdd.requested_quantity_uom2 shipped_uom_code2,
rti.interface_transaction_id bkup_rti_id,
rti.quantity bkup_rti_quantity,
rti.unit_of_measure bkup_rti_uom,
rti.primary_unit_of_measure bkup_rti_puom,
rti.secondary_unit_of_measure bkup_rti_suom,
rti.source_doc_unit_of_measure bkup_rti_src_uom
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
mtl_material_transactions mmt,
rcv_transactions_interface rti
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.source_code = 'RTV'
AND wdd.released_status = 'C'
AND wdd.inv_interfaced_flag <> 'Y'
AND wdd.container_flag = 'N'
AND wdd.delivery_detail_id = mmt.picking_line_id
AND wdd.source_line_id = rti.interface_transaction_id
AND rti.processing_status_code = 'WSH_INTERFACED'
ORDER BY bkup_rti_id, delivery_detail_id
FOR UPDATE;
asn_debug.put_line('perform_post_TM_updates for returned RTs');
asn_debug.put_line('perform_post_TM_updates for issued out MMTs');
END perform_post_TM_updates;
UPDATE rcv_transactions_interface
SET quantity = l_txn_qty,
secondary_quantity = secondary_quantity - l_shipped_qty2,
primary_quantity = primary_quantity - l_primary_qty,
source_doc_quantity = l_src_uom_qty
WHERE interface_transaction_id = p_bkup_rti_id;
wms_return_sv.perform_post_TM_wms_updates
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_trx_interface_id => p_bkup_rti_id,
p_ship_flag => l_ship_flag);
asn_debug.put_line('Unexpected exception in adjust_lot_data while calling wms_return_sv.perform_post_TM_wms_updates');
DELETE FROM mtl_serial_numbers_interface
WHERE product_transaction_id IN
(SELECT interface_transaction_id
FROM rcv_transactions_interface
WHERE group_id = p_group_id
AND processing_mode_code = 'ONLINE');
DELETE FROM mtl_transaction_lots_interface
WHERE product_transaction_id IN
(SELECT interface_transaction_id
FROM rcv_transactions_interface
WHERE group_id = p_group_id
AND processing_mode_code = 'ONLINE');
DELETE FROM rcv_transactions_interface
WHERE group_id = p_group_id
AND processing_mode_code = 'ONLINE';
SELECT *
FROM wsh_delivery_details
WHERE source_code = 'RTV'
AND source_line_id = p_src_line_id
AND released_status = 'D'
AND container_flag = 'N';
SELECT distinct wdd1.lpn_id,
wdd2.delivery_detail_id,
wdd1.delivery_detail_id lpn_wdd_id
FROM wsh_delivery_details wdd1,
wsh_delivery_Details wdd2,
wsh_delivery_assignments wda
WHERE wdd2.source_code = 'RTV'
AND wdd2.source_line_id = p_src_line_id
AND wdd1.container_flag = 'Y'
AND wdd1.lpn_id is not null
AND wdd2.container_flag = 'N'
AND wdd1.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd2.delivery_detail_id = wda.delivery_detail_id
ORDER BY lpn_wdd_id;
SELECT item_id,
quantity,
unit_of_measure,
primary_unit_of_measure,
secondary_unit_of_measure,
source_doc_unit_of_measure,
transfer_lpn_id, -- RTV2 rtv project phase 2
group_id -- RTV2 rtv project phase 2
INTO l_item_id,
l_rti_qty,
l_rti_uom,
l_rti_puom,
l_rti_suom,
l_rti_src_uom,
l_transfer_lpn_id, -- RTV2 rtv project phase 2
l_rtv_order -- RTV2 rtv project phase 2
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_rti_id_tbl(i)
AND transaction_type = 'RETURN TO VENDOR';
SELECT max(source_header_id), max(requested_quantity_uom)
INTO l_source_header_id,
l_wdd_uom_code
FROM wsh_delivery_details
WHERE source_line_id = p_rti_id_tbl(i)
AND source_code = 'RTV';
WSH_INTERFACE.Update_Shipping_Attributes
( p_source_code => 'RTV',
p_changed_attributes => l_changed_attributes,
x_return_status => l_return_status);
SELECT count(1)
INTO l_count
FROM wsh_delivery_assignments
WHERE parent_delivery_detail_id = l_wdd_lpns(indx).lpn_wdd_id;
wsh_container_actions.delete_containers
(p_container_id => l_wdd_lpns(indx).lpn_wdd_id,
x_return_status => l_return_status);
UPDATE rcv_transactions_interface
SET quantity = l_rti_new_qty,
primary_quantity = l_rti_new_pqty,
secondary_quantity = l_rti_new_sqty,
source_doc_quantity = l_rti_new_src_qty
WHERE interface_transaction_id = p_rti_id_tbl(i);
asn_debug.put_line('wsh_interface.update_shipping_attributes returned error!');
po_message_s.sql_error('wsh_interface.update_shipping_attributes', l_msg_data, sqlcode);
asn_debug.put_line('whs_container_actions.delete_containers returned error!');