[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* FUNCTION: insert_interface_code
* Description:
* Helper routing to create a new record in RCV_TRANSACTIONS_INTERFACE
*
* Input Parameters:
* p_rcv_transactions_rec - Record containing the values for RTI
*
* Returns: NUMBER - transaction_interface_id
*---------------------------------------------------------------------------*/
FUNCTION insert_interface_code (
p_rcv_transaction_rec INV_RCV_STD_DELIVER_APIS.RCVTXN_TRANSACTION_REC_TP
)
RETURN NUMBER IS
l_interface_transaction_id NUMBER;
print_debug('Entered insert_interface_code 10 : ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_interface_transaction_id
FROM sys.DUAL;
SELECT rt.movement_id
, rt.project_id
, rt.task_id
INTO l_movement_id
, l_project_id
, l_task_id
FROM rcv_transactions rt
WHERE rt.transaction_id = l_parent_transaction_id
AND ROWNUM < 2;
INSERT INTO rcv_transactions_interface
(
receipt_source_code
, interface_transaction_id
, group_id
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, interface_source_code
, source_document_code
, destination_type_code
, transaction_date
, quantity
, unit_of_measure
, 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
, requisition_line_id
, req_distribution_id
, routing_header_id
, routing_step_id
, packing_slip
, vendor_item_num
, 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
, primary_quantity
, primary_unit_of_measure
, uom_code
-- OPM COnvergence
, SECONDARY_QUANTITY
, SECONDARY_UNIT_OF_MEASURE
, SECONDARY_UOM_CODE
, item_id
, item_revision
, to_organization_id
, deliver_to_location_id
, destination_context
, vendor_id
, deliver_to_person_id
, subinventory
, locator_id
, wip_entity_id
, wip_line_id
, wip_repetitive_schedule_id
, wip_operation_seq_num
, wip_resource_seq_num
, bom_resource_id
, use_mtl_lot
, use_mtl_serial
, movement_id
, currency_conversion_date
, currency_conversion_type
, qa_collection_id
, ussgl_transaction_code
, government_context
, vendor_site_id
, oe_order_header_id
, oe_order_line_id
, customer_id
, customer_site_id
, put_away_rule_id
, put_away_strategy_id
, lpn_id
, transfer_lpn_id
, cost_group_id
, mmtt_temp_id
, mobile_txn
, transfer_cost_group_id
, validation_flag
, lpn_group_id
, project_id
, task_id
, org_id --
, from_subinventory
, from_locator_id
)
VALUES (
p_rcv_transaction_rec.receipt_source_code --receipt source code
, l_interface_transaction_id --interface txn id
, l_group_id --group id
, l_user_id --created_by
, SYSDATE --creation_date
, l_user_id --last_updated_by
, SYSDATE --last_update_date
, l_logon_id --last_update_login
, 'RCV' --interface_source_code
, p_rcv_transaction_rec.source_document_code --source_document_code
, p_rcv_transaction_rec.destination_type_code --destination_type_code
, l_trx_date --transaction_date
, p_rcv_transaction_rec.transaction_quantity --quantity
, p_rcv_transaction_rec.transaction_uom --unit_of_measure
, p_rcv_transaction_rec.shipment_header_id --shipment_header_id
, p_rcv_transaction_rec.shipment_line_id --shipment_line_id
, p_rcv_transaction_rec.substitute_unordered_code --substitute_unordered_code
, l_employee_id --employee_id
, l_parent_transaction_id --parent_transaction_id
, p_rcv_transaction_rec.inspection_status_code --inspection_status_code
, p_rcv_transaction_rec.inspection_quality_code --inspection_quality_code
, p_rcv_transaction_rec.po_header_id --po_header_id
, p_rcv_transaction_rec.po_release_id --po_release_id
, p_rcv_transaction_rec.po_line_id --po_line_id
, p_rcv_transaction_rec.po_line_location_id --po_line_location_id
, p_rcv_transaction_rec.po_distribution_id --po_distribution_id
, p_rcv_transaction_rec.po_revision_num --po_revision_num
, p_rcv_transaction_rec.po_unit_price --po_unit_price
, p_rcv_transaction_rec.currency_code --currency_code
, p_rcv_transaction_rec.currency_conversion_rate --currency_conversion_rate
, p_rcv_transaction_rec.req_line_id --requsition_line_id
, p_rcv_transaction_rec.req_distribution_id --req_distribution_id
, p_rcv_transaction_rec.routing_id --routing_header_id
, p_rcv_transaction_rec.routing_step_id --routing_step_id
, p_rcv_transaction_rec.packing_slip --packing_slip
, p_rcv_transaction_rec.vendor_item_number --vendor_item_num
, p_rcv_transaction_rec.comments --comments
, p_rcv_transaction_rec.attribute_category --attribute_category
, p_rcv_transaction_rec.attribute1 --attribute1
, p_rcv_transaction_rec.attribute2 --attribute2
, p_rcv_transaction_rec.attribute3 --attribute3
, p_rcv_transaction_rec.attribute4 --attribute4
, p_rcv_transaction_rec.attribute5 --attribute5
, p_rcv_transaction_rec.attribute6 --attribute6
, p_rcv_transaction_rec.attribute7 --attribute7
, p_rcv_transaction_rec.attribute8 --attribute8
, p_rcv_transaction_rec.attribute9 --attribute9
, p_rcv_transaction_rec.attribute10 --attribute10
, p_rcv_transaction_rec.attribute11 --attribute11
, p_rcv_transaction_rec.attribute12 --attribute12
, p_rcv_transaction_rec.attribute13 --attribute13
, p_rcv_transaction_rec.attribute14 --attribute14
, p_rcv_transaction_rec.attribute15 --attribute15
, p_rcv_transaction_rec.transaction_type --transaction_type
, p_rcv_transaction_rec.location_id --location_id
, 'PENDING' --processing_status_code
, l_transaction_mode --processing_mode_code
, 'PENDING' --transaction_status_code
, p_rcv_transaction_rec.category_id --category_id
, p_rcv_transaction_rec.vendor_lot_num --vendor_lot_num
, p_rcv_transaction_rec.reason_id --reason_id
, p_rcv_transaction_rec.primary_quantity --primary_quantity
, p_rcv_transaction_rec.primary_uom --primary_unit_of_measure
, g_transfer_uom_code --uom_code
-- OPM COnvergence
, DECODE(p_rcv_transaction_rec.sec_transaction_quantity, FND_API.G_MISS_NUM, NULL)
, p_rcv_transaction_rec.secondary_uom
, p_rcv_transaction_rec.secondary_uom_code
, p_rcv_transaction_rec.item_id --item_id
, p_rcv_transaction_rec.item_revision --item_revision
, p_rcv_transaction_rec.to_organization_id --to_organization_id
, p_rcv_transaction_rec.deliver_to_location_id --deliver_to_location_id
, l_destination_context --destination_context
, p_rcv_transaction_rec.vendor_id --vendor_id
, p_rcv_transaction_rec.deliver_to_person_id --deliver_to_persion_id
, p_rcv_transaction_rec.subinventory_dsp --subinventory
, p_rcv_transaction_rec.locator_id --locator_id
, p_rcv_transaction_rec.wip_entity_id --wip_entity_id
, p_rcv_transaction_rec.wip_line_id --wip_line_id
, p_rcv_transaction_rec.wip_repetitive_schedule_id --wip_repetitive_schedule_id
, p_rcv_transaction_rec.wip_operation_seq_num --wip_operation_seq_num
, p_rcv_transaction_rec.wip_resource_seq_num --wip_resource_seq_num
, l_bom_resource_id --bom_resource_id
, p_rcv_transaction_rec.lot_control_code --use_mtl_lot
, p_rcv_transaction_rec.serial_number_control_code --use_mtl_serial
, l_movement_id --movement_id
, l_currency_conv_date --currency_conversion_date
, p_rcv_transaction_rec.currency_conversion_type --currency_conversion_type
, p_rcv_transaction_rec.qa_collection_id --qa_collection_id
, p_rcv_transaction_rec.ussgl_transaction_code --ussgl_transaction_date
, p_rcv_transaction_rec.government_context --government_context
, p_rcv_transaction_rec.vendor_site_id --vendor_site_id
, p_rcv_transaction_rec.oe_order_header_id --oe_order_header_id
, p_rcv_transaction_rec.oe_order_line_id --oe_order_line_id
, p_rcv_transaction_rec.customer_id --customer_id
, p_rcv_transaction_rec.customer_site_id --customer_site_id
, p_rcv_transaction_rec.put_away_rule_id --put_away_rule_id
, p_rcv_transaction_rec.put_away_strategy_id --put_away_strategy_id
, p_rcv_transaction_rec.lpn_id --lpn_id
, p_rcv_transaction_rec.transfer_lpn_id --transfer_lpn_id
, p_rcv_transaction_rec.cost_group_id --cost_group_id
, p_rcv_transaction_rec.mmtt_temp_id --mmtt_temp_id
, 'Y' --mobile_txn
, p_rcv_transaction_rec.transfer_cost_group_id --transfer_cost_group_id
, l_validation_flag --validation_flag
, l_lpn_group_id --lpn_group_id
, l_project_id --project_id
, l_task_id --task_id
, l_operating_unit_id --
, p_rcv_transaction_rec.from_subinventory_code --for matching non-lpn materials
, p_rcv_transaction_rec.from_locator_id --for matching non-lpn materials
);
print_debug('Error inserting into RTI. SQLCODE:'||SQLCODE||' SQLERRM:'||Sqlerrm,1);
print_debug('insert_interface_code completed successfully. Generated RTID: ' || l_interface_transaction_id, 4);
'INV_RCV_STD_TRANSFER_APIS.INSERT_INTERFACE_CODE', 10, SQLCODE);
END insert_interface_code;
* Call the helper routine to insert the RTI record
* Update the Lots and serial interface records with the RTI record created
*
* Output Parameters:
* x_return_status
* Return status indicating Success (S), Error (E), Unexpected Error (U)
* x_msg_count
* Number of messages in message list
* x_msg_data
* Stacked messages text
*
* Input Parameters:
* p_rcv_transaction_rec - Record type for RTI with quantities initialized
* p_rcvtxn_rec - Record type for the parent RCV Transaction
* p_parent_txn_id - Transaction ID of the parent transaction
* p_organization_id - Organization ID
* p_item_id - Item ID
* p_revision - Item Revision
* p_subinventory_code - Destination receiving subinventory code
* p_locator_id - Destination receiving locator ID
* p_lot_control_code - Lot Control Code of the item
* p_serial_control_code - Serial Control Code of the item
* p_original_rti_id - Interface Transaction Id for lot/serial split
* p_original_temp_id - Transaction Temp ID of the MMTT being putaway
* p_lpn_id - LPN ID of the move order line
* p_transfer_lpn_id - LPN ID of the LPN being dropped into
* p_doc_type - Document Type (PO/RMA/INTSHIP)
*
* Returns: NONE
*---------------------------------------------------------------------------*/
PROCEDURE populate_transfer_rti_values (
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_rcv_transaction_rec IN OUT NOCOPY INV_RCV_STD_DELIVER_APIS.rcvtxn_transaction_rec_tp
, p_rcvtxn_rec IN OUT NOCOPY INV_RCV_STD_DELIVER_APIS.rcvtxn_enter_rec_cursor_rec
, p_parent_txn_id IN NUMBER
, p_organization_id IN NUMBER
, p_item_id IN NUMBER
, p_revision IN VARCHAR2
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_lot_control_code IN NUMBER
, p_serial_control_code IN NUMBER
, p_original_rti_id IN NUMBER DEFAULT NULL
, p_original_temp_id IN NUMBER DEFAULT NULL
, p_lpn_id IN NUMBER DEFAULT NULL
, p_transfer_lpn_id IN NUMBER DEFAULT NULL
, p_doc_type IN VARCHAR2 DEFAULT NULL ) IS
--Local variables
l_content_lpn_id NUMBER; --Content LPN ID
SELECT cost_group_id
, put_away_rule_id
, put_away_strategy_id
INTO p_rcv_transaction_rec.cost_group_id
, p_rcv_transaction_rec.put_away_rule_id
, p_rcv_transaction_rec.put_away_strategy_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_original_temp_id;
SELECT cost_group_id
INTO p_rcv_transaction_rec.transfer_cost_group_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_rcvtxn_rec.shipment_line_id;
SELECT location_id
INTO l_location_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_organization_id;
print_debug('Inserting the RTI for TRANSFER transaction', 4);
l_interface_txn_id := insert_interface_code (
p_rcv_transaction_rec => p_rcv_transaction_rec);
SELECT count(1)
INTO l_msni_count
FROM mtl_serial_numbers_interface
WHERE product_transaction_id = p_original_rti_id
AND product_code = 'RCV';
' . Update MTLI.serial_txn_temp_id TO NULL', 4);
UPDATE mtl_transaction_lots_interface
SET product_transaction_id = l_interface_txn_id
, serial_transaction_temp_id = DECODE(p_doc_type, 'RMA', serial_transaction_temp_id,
decode(l_msni_count, 0, NULL, serial_transaction_temp_id))
WHERE product_code = 'RCV'
AND product_transaction_id = p_original_rti_id;
--First update the lot interface records
UPDATE mtl_transaction_lots_interface
SET product_transaction_id = l_interface_txn_id
WHERE product_code = 'RCV'
AND product_transaction_id = p_original_rti_id;
print_debug('Updated ' || SQL%ROWCOUNT || ' lot records', 4);
UPDATE mtl_serial_numbers_interface
SET product_transaction_id = l_interface_txn_id
WHERE product_code = 'RCV'
AND product_transaction_id = p_original_rti_id;
print_debug('Updated ' || SQL%ROWCOUNT || ' serial records', 4);
--Update the serial interface records
UPDATE mtl_serial_numbers_interface
SET product_transaction_id = l_interface_txn_id
WHERE product_code = 'RCV'
AND product_transaction_id = p_original_rti_id;
print_debug('Updated ' || SQL%ROWCOUNT || ' serial records', 4);
* p_transfer_quantity - Quantity selected for transfer
* p_transfer_uom_code - Transaction (MMTT) UOM Code
* p_primary_uom_code - Item's Primary UOM Code
* x_avail_transfer_qty - Quantity available to transfer in Txn UOM
* x_avail_primary_qty - Available transfer quantity in primary UOM
*
* Returns: NONE
*---------------------------------------------------------------------------*/
PROCEDURE get_avail_quantity_to_transfer(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_parent_txn_id IN NUMBER
, p_organization_id IN NUMBER
, p_item_id IN NUMBER
, p_lpn_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_transfer_quantity IN NUMBER
, p_receipt_source_code IN VARCHAR2
, p_transfer_uom_code IN VARCHAR2
, p_primary_uom_code IN VARCHAR2
, x_avail_transfer_qty OUT NOCOPY NUMBER
, x_avail_primary_qty OUT NOCOPY NUMBER ) IS
--Local Variables
l_parent_txn_id NUMBER; --Parent Transaction Id
SELECT count(1)
INTO l_lpn_count
FROM rcv_supply rs
WHERE rs.rcv_transaction_id = p_parent_txn_id
AND rs.lpn_id = p_lpn_id;
SELECT count(1)
INTO l_lot_count
FROM rcv_lots_supply rls
WHERE rls.lot_num = p_lot_number
AND rls.transaction_id = p_parent_txn_id;
SELECT uom_code
INTO l_rcvtxn_uom_code
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND unit_of_measure = l_rcvtxn_uom;
SELECT rs.from_organization_id from_organization_id
, rs.to_organization_id to_organization_id
, rt.source_document_code source_document_code
, rsh.receipt_source_code receipt_source_code
, rs.rcv_transaction_id rcv_transaction_id
, rt.transaction_date transaction_date
, rt.transaction_type transaction_type
, rt.primary_unit_of_measure primary_unit_of_measure
, rt.primary_quantity primary_quantity
, rs.po_header_id po_header_id
, rt.po_revision_num po_revision_num
, rs.po_release_id po_release_id
, rsh.vendor_id vendor_id
, rt.vendor_site_id vendor_site_id
, rs.po_line_id po_line_id
, rt.po_unit_price po_unit_price
, rsl.category_id category_id
, rs.item_id item_id
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, rs.item_revision item_revision
, rs.po_line_location_id po_line_location_id
, to_number(NULL) po_distribution_id
, rt.employee_id employee_id
, rsl.comments comments
, to_number(NULL) req_header_id
, to_number(NULL) req_line_id
, rs.shipment_header_id shipment_header_id
, rs.shipment_line_id shipment_line_id
, rsh.packing_slip packing_slip
, rsl.government_context government_context
, rsl.ussgl_transaction_code ussgl_transaction_code
, rt.inspection_status_code inspection_status_code
, rt.inspection_quality_code inspectin_quality_code
, rt.vendor_lot_num vendor_lot_num
, pol.vendor_product_num vendor_item_number
, rt.substitute_unordered_code substitute_unordered_code
, rt.routing_header_id routing_id
, rt.routing_step_id routing_step_id
, rt.reason_id reason_id
, rt.currency_code currency_code
, rt.currency_conversion_rate currency_conversion_rate
, rt.currency_conversion_date currency_conversion_date
, rt.currency_conversion_type currency_conversion_type
, to_number(NULL) req_distribution_id
, rs.destination_type_code destination_type_code_hold
, rs.destination_type_code final_destination_type_code
, rt.location_id location_id
, to_number(NULL) final_deliver_to_person_id
, to_number(NULL) final_deliver_to_location_id
, rsl.to_subinventory subinventory
, NVL(pol.un_number_id,
msi.un_number_id) un_number_id
, NVL(pol.hazard_class_id,
msi.hazard_class_id) hazard_class_id
, rs.creation_date creation_date
, rt.attribute_category attribute_category
, rt.attribute1 attribute1
, rt.attribute2 attribute2
, rt.attribute3 attribute3
, rt.attribute4 attribute4
, rt.attribute5 attribute5
, rt.attribute6 attribute6
, rt.attribute7 attribute7
, rt.attribute8 attribute8
, rt.attribute9 attribute9
, rt.attribute10 attribute10
, rt.attribute11 attribute11
, rt.attribute12 attribute12
, rt.attribute13 attribute13
, rt.attribute14 attribute14
, rt.attribute15 attribute15
, rt.qa_collection_id qa_collection_id
, to_number(NULL) oe_order_header_id
, to_number(NULL) oe_order_line_id
, rsh.customer_id customer_id
, rsh.customer_site_id customer_site_id
, to_number(NULL) wip_entity_id
, to_number(NULL) po_operation_seq_num
, to_number(NULL) po_resource_seq_num
, to_number(NULL) wip_repetitive_schedule_id
, to_number(NULL) wip_line_id
, to_number(NULL) bom_resource_id
, to_char(NULL) final_subinventory
, rt.secondary_quantity secondary_quantity --OPM Convergence
, rt.secondary_unit_of_measure secondary_uom --OPM Convergence
--The following columns are needed for matching in cases where no LPN is involved
, rs.to_subinventory from_subinventory_code
, rs.to_locator_id from_locator_id
FROM rcv_transactions rt
, rcv_supply rs
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines pol
, mtl_system_items msi
WHERE rs.rcv_transaction_id = v_rcv_txn_id
AND rs.to_organization_id = p_organization_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rs.po_line_location_id = v_po_line_location_id
AND rs.shipment_line_id = rsl.shipment_line_id
AND rs.shipment_header_id = rsh.shipment_header_id
AND rs.po_line_id = pol.po_line_id
AND msi.organization_id = p_organization_id
AND msi.inventory_item_id = rs.item_id;
SELECT primary_unit_of_measure
, primary_uom_code
-- , secondary_unit_of_measure
, secondary_uom_code --OPM Convergence
INTO l_primary_uom
, l_primary_uom_code
-- , l_secondary_uom ----OPM Convergence
, l_secondary_uom_code --OPM Convergence
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT unit_of_measure
INTO l_transfer_uom
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND uom_code = l_transfer_uom_code;
SELECT unit_of_measure
INTO l_sec_transfer_uom
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND uom_code = l_sec_transfer_uom_code;
SELECT rs.from_organization_id from_organization_id
, rs.to_organization_id to_organization_id
, rt.source_document_code source_document_code
, rsh.receipt_source_code receipt_source_code
, rs.rcv_transaction_id rcv_transaction_id
, rt.transaction_date transaction_date
, rt.transaction_type transaction_type
, rt.primary_unit_of_measure primary_unit_of_measure
, rt.primary_quantity primary_quantity
, to_number(NULL) po_header_id
, to_number(NULL) po_revision_num
, to_number(NULL) po_release_id
, rsh.vendor_id vendor_id
, rt.vendor_site_id vendor_site_id
, to_number(NULL) po_line_id
, to_number(NULL) po_unit_price
, rsl.category_id category_id
, rs.item_id item_id
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, rs.item_revision item_revision
, to_number(NULL) po_line_location_id
, to_number(NULL) po_distribution_id
, rt.employee_id employee_id
, rsl.comments comments
, to_number(NULL) req_header_id
, to_number(NULL) req_line_id
, rs.shipment_header_id shipment_header_id
, rs.shipment_line_id shipment_line_id
, rsh.packing_slip packing_slip
, rsl.government_context government_context
, rsl.ussgl_transaction_code ussgl_transaction_code
, rt.inspection_status_code inspection_status_code
, rt.inspection_quality_code inspectin_quality_code
, rt.vendor_lot_num vendor_lot_num
, '' vendor_item_number
, rt.substitute_unordered_code substitute_unordered_code
, rt.routing_header_id routing_id
, rt.routing_step_id routing_step_id
, rt.reason_id reason_id
, rt.currency_code currency_code
, rt.currency_conversion_rate currency_conversion_rate
, rt.currency_conversion_date currency_conversion_date
, rt.currency_conversion_type currency_conversion_type
, rsl.req_distribution_id req_distribution_id
, rs.destination_type_code destination_type_code_hold
, rs.destination_type_code final_destination_type_code
, rt.location_id location_id
, to_number(NULL) final_deliver_to_person_id
, to_number(NULL) final_deliver_to_location_id
, rsl.to_subinventory subinventory
, msi.un_number_id un_number_id
, msi.hazard_class_id hazard_class_id
, rs.creation_date creation_date
, rt.attribute_category attribute_category
, rt.attribute1 attribute1
, rt.attribute2 attribute2
, rt.attribute3 attribute3
, rt.attribute4 attribute4
, rt.attribute5 attribute5
, rt.attribute6 attribute6
, rt.attribute7 attribute7
, rt.attribute8 attribute8
, rt.attribute9 attribute9
, rt.attribute10 attribute10
, rt.attribute11 attribute11
, rt.attribute12 attribute12
, rt.attribute13 attribute13
, rt.attribute14 attribute14
, rt.attribute15 attribute15
, rt.qa_collection_id qa_collection_id
, to_number(NULL) oe_order_header_id
, to_number(NULL) oe_order_line_id
, rsh.customer_id customer_id
, rsh.customer_site_id customer_site_id
, to_number(NULL) wip_entity_id
, to_number(NULL) po_operation_seq_num
, to_number(NULL) po_resource_seq_num
, to_number(NULL) wip_repetitive_schedule_id
, to_number(NULL) wip_line_id
, to_number(NULL) bom_resource_id
, to_char(NULL) final_subinventory
, rt.secondary_quantity secondary_quantity --OPM Convergence
, rt.secondary_unit_of_measure secondary_uom --OPM Convergence
--The following columns are needed for matching in cases where no LPN is involved
, rs.to_subinventory from_subinventory_code
, rs.to_locator_id from_locator_id
FROM rcv_transactions rt
, rcv_supply rs
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, mtl_system_items msi
WHERE rs.rcv_transaction_id = v_rcv_txn_id
AND rs.to_organization_id = p_organization_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rs.shipment_line_id = v_shipment_line_id
AND rs.shipment_line_id = rsl.shipment_line_id
AND rs.shipment_header_id = rsh.shipment_header_id
AND msi.organization_id = p_organization_id
AND msi.inventory_item_id = rs.item_id;
SELECT primary_unit_of_measure
, primary_uom_code
-- , secondary_unit_of_measure
, secondary_uom_code
INTO l_primary_uom
, l_primary_uom_code
--- , l_secondary_uom
, l_secondary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT unit_of_measure
INTO l_transfer_uom
FROM mtl_item_uoms_view
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND uom_code = l_transfer_uom_code;
SELECT unit_of_measure
INTO l_sec_transfer_uom
FROM mtl_item_uoms_view
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND uom_code = l_sec_transfer_uom_code;
SELECT rs.from_organization_id from_organization_id
, rs.to_organization_id to_organization_id
, rt.source_document_code source_document_code
, rsh.receipt_source_code receipt_source_code
, rs.rcv_transaction_id rcv_transaction_id
, rt.transaction_date transaction_date
, rt.transaction_type transaction_type
, rt.primary_unit_of_measure primary_unit_of_measure
, rt.primary_quantity primary_quantity
, to_number(NULL) po_header_id
, to_number(NULL) po_revision_num
, to_number(NULL) po_release_id
, rsh.vendor_id vendor_id
, rt.vendor_site_id vendor_site_id
, to_number(NULL) po_line_id
, to_number(NULL) po_unit_price
, rsl.category_id category_id
, rs.item_id item_id
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, rs.item_revision item_revision
, to_number(NULL) po_line_location_id
, to_number(NULL) po_distribution_id
, rt.employee_id employee_id
, rsl.comments comments
, to_number(NULL) req_header_id
, to_number(NULL) req_line_id
, rs.shipment_header_id shipment_header_id
, rs.shipment_line_id shipment_line_id
, rsh.packing_slip packing_slip
, rsl.government_context government_context
, rsl.ussgl_transaction_code ussgl_transaction_code
, rt.inspection_status_code inspection_status_code
, rt.inspection_quality_code inspectin_quality_code
, rt.vendor_lot_num vendor_lot_num
, '' vendor_item_number
, rt.substitute_unordered_code substitute_unordered_code
, rt.routing_header_id routing_id
, rt.routing_step_id routing_step_id
, rt.reason_id reason_id
, rt.currency_code currency_code
, rt.currency_conversion_rate currency_conversion_rate
, rt.currency_conversion_date currency_conversion_date
, rt.currency_conversion_type currency_conversion_type
, rsl.req_distribution_id req_distribution_id
, rs.destination_type_code destination_type_code_hold
, to_number(NULL) final_destination_type_code
, rt.location_id location_id
, to_number(NULL) final_deliver_to_person_id
, to_number(NULL) final_deliver_to_location_id
, rsl.to_subinventory subinventory
, msi.un_number_id un_number_id
, msi.hazard_class_id hazard_class_id
, rs.creation_date creation_date
, rt.attribute_category attribute_category
, rt.attribute1 attribute1
, rt.attribute2 attribute2
, rt.attribute3 attribute3
, rt.attribute4 attribute4
, rt.attribute5 attribute5
, rt.attribute6 attribute6
, rt.attribute7 attribute7
, rt.attribute8 attribute8
, rt.attribute9 attribute9
, rt.attribute10 attribute10
, rt.attribute11 attribute11
, rt.attribute12 attribute12
, rt.attribute13 attribute13
, rt.attribute14 attribute14
, rt.attribute15 attribute15
, rt.qa_collection_id qa_collection_id
, rs.oe_order_header_id oe_order_header_id
, rs.oe_order_line_id oe_order_line_id
, rsh.customer_id customer_id
, rsh.customer_site_id customer_site_id
, to_number(NULL) wip_entity_id
, to_number(NULL) po_operation_seq_num
, to_number(NULL) po_resource_seq_num
, to_number(NULL) wip_repetitive_schedule_id
, to_number(NULL) wip_line_id
, to_number(NULL) bom_resource_id
, to_char(NULL) final_subinventory
, rt.secondary_quantity secondary_quantity --OPM Convergence
, rt.secondary_unit_of_measure secondary_uom --OPM Convergence
--The following columns are needed for matching in cases where no LPN is involved
, rs.to_subinventory from_subinventory_code
, rs.to_locator_id from_locator_id
FROM rcv_transactions rt
, rcv_supply rs
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, mtl_system_items msi
WHERE rs.rcv_transaction_id = v_rcv_txn_id
AND rs.to_organization_id = p_organization_id
AND rs.rcv_transaction_id = rt.transaction_id
AND rs.oe_order_line_id = v_oe_order_line_id
AND rs.shipment_line_id = rsl.shipment_line_id
AND rs.shipment_header_id = rsh.shipment_header_id
AND msi.organization_id = p_organization_id
AND msi.inventory_item_id = rs.item_id;
SELECT primary_unit_of_measure
, primary_uom_code
-- , secondary_unit_of_measure --OPM Convergence
, secondary_uom_code
INTO l_primary_uom
, l_primary_uom_code
-- , l_secondary_uom
, l_secondary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT unit_of_measure
INTO l_transfer_uom
FROM mtl_item_uoms_view
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND uom_code = l_transfer_uom_code;
SELECT unit_of_measure
INTO l_sec_transfer_uom
FROM mtl_item_uoms_view
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND uom_code = l_sec_transfer_uom_code;
SELECT secondary_uom_code
INTO l_sec_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_organization_id
AND (hoi.org_information_context || '') = 'Accounting Information';
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM sys.dual;
SELECT po_header_id
, po_release_id
, po_line_id
INTO l_po_header_id
, l_po_release_id
, l_po_line_id
FROM po_line_locations
WHERE line_location_id = p_reference_id;
SELECT shipment_header_id
INTO l_shipment_header_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_reference_id;
SELECT header_id
INTO l_oe_order_header_id
FROM oe_order_lines_all
WHERE line_id = p_reference_id;
FUNCTION insert_mtli_helper(
p_txn_if_id IN OUT NOCOPY NUMBER
, p_lot_number IN VARCHAR2
, p_txn_qty IN NUMBER
, p_prm_qty IN NUMBER
, p_item_id IN NUMBER
, p_org_id IN NUMBER
, p_serial_temp_id IN NUMBER
, p_product_txn_id IN NUMBER
, p_secondary_quantity IN NUMBER --OPM Convergence
, p_secondary_uom IN NUMBER --OPM Convergence
) RETURN BOOLEAN IS
--Local variables
l_lot_status_id NUMBER;
SELECT expiration_date
, status_id
INTO l_expiration_date
, l_lot_status_id
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_if_id
FROM sys.dual;
print_debug('insert_mtli_helper: Error retrieving from seq.',1);
print_debug('insert_mtli_helper: SQLCODE: '||SQLCODE||' SQLERRM:'||Sqlerrm,1);
print_debug('insert_mtli_helper: l_txn_if_id: '||l_txn_if_id,1);
inv_rcv_integration_pvt.insert_mtli
(p_product_transaction_id => l_product_txn_id
,p_product_code => l_prod_code
,p_interface_id => l_txn_if_id
,p_org_id => p_org_id
,p_item_id => p_item_id
,p_lot_number => p_lot_number
,p_transaction_quantity => p_txn_qty
,p_primary_quantity => p_prm_qty
,p_serial_interface_id => p_serial_temp_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_sec_qty => p_secondary_quantity
);
print_debug('insert_mtli_helper: Error occurred while creating interface lots: ' || l_msg_data,1);
print_debug('Exception occurred in insert_mtli_helper: ',1);
END insert_mtli_helper;
FUNCTION insert_msni_helper(
p_txn_if_id IN OUT NOCOPY NUMBER
, p_serial_number IN VARCHAR2
, p_item_id IN NUMBER
, p_org_id IN NUMBER
, p_product_txn_id IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
--Local variables
l_serial_status_id NUMBER;
SELECT status_id
INTO l_serial_status_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_item_id;
inv_rcv_integration_apis.insert_msni(
p_api_version => 1.0
, p_init_msg_lst => l_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_transaction_interface_id => l_txn_if_id
, p_fm_serial_number => p_serial_number
, p_to_serial_number => p_serial_number
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_status_id => l_serial_status_id
, p_product_transaction_id => l_product_txn_id
, p_product_code => l_prod_code
, p_att_exist => l_yes
, p_update_msn => l_no);
print_debug('insert_msni_helper: Error occurred while creating interface serials: ' || l_msg_data,1);
print_debug('insert_msni_helper: msni '||p_txn_if_id||' inserted for serial '||p_serial_number,1);
print_debug('Exception occurred in insert_msni_helper: ',1);
END insert_msni_helper;
SELECT distinct
rsl.source_document_code source_document_code
,rsl.po_line_location_id po_line_location_id
,rsl.po_distribution_id po_distribution_id
,rsl.shipment_line_id shipment_line_id
,rsl.oe_order_line_id oe_order_line_id
,rsh.receipt_source_code receipt_source_code
,rss.serial_num serial_num
,rt.uom_code uom_code
,rss.transaction_id rcv_transaction_id
,rss.lot_num lot_num
,rs.secondary_quantity secondary_quantity
,msni.transaction_interface_id transaction_interface_id
,rsl.asn_line_flag asn_line_flag
FROM rcv_supply rs,
rcv_transactions rt,
rcv_serials_supply rss,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
mtl_serial_numbers_interface msni
WHERE rs.item_id = p_item_id
AND Nvl(rs.item_revision,nvl(p_revision,'@@@')) = nvl(p_revision,'@@@')
AND rs.to_organization_id = p_organization_id
AND nvl(rs.lpn_id,-1) = nvl(p_lpn_id,-1)
AND rs.rcv_transaction_id = rt.transaction_id
AND msni.product_code = 'RCV'
AND msni.product_transaction_id = p_original_rti_id
AND rss.serial_num between msni.fm_serial_number and msni.to_serial_number
AND nvl(rss.lot_num,'@$#_') = nvl(p_lot_number, '@$#_')
AND rss.supply_type_code = 'RECEIVING'
AND rs.shipment_line_id = rsl.shipment_line_id
AND rs.rcv_transaction_id = rss.transaction_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND decode(rt.routing_header_id, 2,
decode(rt.inspection_status_code,'NOT INSPECTED',1, 'ACCEPTED',2,'REJECTED', 3)
,-1) = nvl(p_inspection_status, -1)
ORDER BY rcv_transaction_id
;
SELECT
rsl.source_document_code source_document_code
,rsl.po_line_location_id po_line_location_id
,rsl.po_distribution_id po_distribution_id
,rsl.shipment_line_id shipment_line_id
,rsl.oe_order_line_id oe_order_line_id
,rs.supply_source_id supply_source_id
,rs.rcv_transaction_id rcv_transaction_id
,rsh.receipt_source_code receipt_source_code
,rt.uom_code uom_code
,rs.secondary_quantity secondary_quantity
/*Bug 5511398:In case the receipt transcation for WMS enabled org is done via
desktop, then there will not be data in RLS table,in which case fetch the qty
from rcv_supply .*/
/*,Nvl(rls.primary_quantity,0) lot_prim_qty
,Nvl(rls.quantity,0) lot_qty*/
,nvl(rls.primary_quantity,rs.to_org_primary_quantity) lot_prim_qty
,nvl(rls.quantity,rs.quantity) lot_qty
,decode(rt.uom_code, p_transfer_uom_code, 1, 2) ORDERING1
,decode(rt.uom_code, p_transfer_uom_code, (p_transfer_quantity - rs.quantity), 0) ORDERING2
,rsl.asn_line_flag asn_line_flag
FROM rcv_supply rs,
rcv_transactions rt,
rcv_shipment_lines rsl,
rcv_lots_supply rls,
rcv_shipment_headers rsh
WHERE rs.item_id = p_item_id
AND Nvl(rs.item_revision,nvl(p_revision,'@@@@')) = nvl(p_revision,'@@@@')
AND rs.to_organization_id = p_organization_id
AND nvl(rs.lpn_id,-1) = nvl(p_lpn_id,-1)
AND nvl(rt.subinventory,'@$#_') = nvl(v_from_sub,'@$#_')
AND nvl(rt.locator_id,-1) = nvl(v_from_locator_id, -1)
AND nvl(rt.transfer_lpn_id,-1) = nvl(p_lpn_id,-1)
AND rs.rcv_transaction_id = rt.transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rs.supply_type_code = 'RECEIVING'
AND rls.transaction_id (+) = rs.supply_source_id
AND nvl(rls.lot_num, '@$#_') = nvl(p_lot_number, '@$#_')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND decode(rt.routing_header_id, 2,
decode(rt.inspection_status_code,'NOT INSPECTED',1, 'ACCEPTED',2,'REJECTED', 3)
,-1) = nvl(p_inspection_status, -1)
--Bug 5331779 - Begin change
--Adding the following to make sure that we do not pickup RS with serial numbers
AND NOT exists
(SELECT '1' FROM rcv_serials_supply rss
WHERE rss.transaction_id = rs.supply_source_id
AND rss.supply_type_code = 'RECEIVING')
--Bug 5331779-End change
ORDER BY ORDERING1, ORDERING2
;
l_qty_to_insert NUMBER;
l_lot_sec_qty_to_insert NUMBER;
l_mmtt_id_to_insert NUMBER;
select subinventory_code
, locator_id
into l_from_sub
,l_from_locator_id
from wms_license_plate_numbers wlpn
where wlpn.lpn_id = p_lpn_id;
SELECT SUM(primary_quantity)
, SUM(transaction_quantity)
INTO l_processed_lot_prim_qty
, l_processed_lot_qty
FROM mtl_transaction_lots_interface
WHERE product_code = 'RCV'
AND product_transaction_id
IN (SELECT interface_transaction_id
FROM rcv_transactions_interface
WHERE parent_transaction_id = l_rtv_rec.rcv_transaction_id
)
AND lot_number = p_lot_number;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_new_intf_id
FROM dual;
l_qty_to_insert := inv_convert.inv_um_convert( item_id => p_item_id,
precision => 6,
from_quantity => l_avail_qty,
from_unit => l_rtv_rec.uom_code,
to_unit => p_transfer_uom_code,
from_name => null,
to_name => null );
print_debug('QTY TO INSERT1: = '||L_QTY_TO_INSERT , 1);
l_qty_to_insert := l_avail_qty;
print_debug('QTY TO INSERT2: = '||L_QTY_TO_INSERT , 1);
,x_new_mmtt_id => l_mmtt_id_to_insert
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_new_rti_info(1).quantity := L_qty_to_insert;
l_qty_to_insert := inv_convert.inv_um_convert(
item_id => p_item_id,
precision => 6,
from_quantity => l_remaining_prim_qty,
from_unit => l_primary_uom_code,
to_unit => p_transfer_uom_code,
from_name => null,
to_name => null );
print_debug('QTY TO INSERT3: = ' || L_QTY_TO_INSERT , 1);
UPDATE mtl_transaction_lots_interface
SET product_transaction_id = l_new_intf_id
WHERE product_transaction_id = p_original_rti_id
AND product_code = 'RCV';
l_mmtt_id_to_insert := p_original_temp_id;
l_secondary_quantity := p_sec_transfer_quantity * (l_qty_to_insert/p_transfer_quantity);
, p_transfer_quantity => l_qty_to_insert
, p_transfer_uom_code => p_transfer_uom_code
, p_lot_control_code => p_lot_control_code
, p_serial_control_code => p_serial_control_code
, p_original_rti_id => l_new_intf_id
, p_original_temp_id => l_mmtt_id_to_insert
, p_lot_number => p_lot_number
, p_lpn_id => p_lpn_id
, p_transfer_lpn_id => p_transfer_lpn_id
, p_sec_transfer_quantity => l_secondary_quantity
, p_sec_transfer_uom_code => p_sec_transfer_uom_code
);
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_new_intf_id
FROM dual;
select mtl_material_transactions_s.nextval
into l_ser_txn_temp_id from dual;
l_result := insert_msni_helper(
p_txn_if_id => l_rti_tb(l_txn_id)(l_lot_num).serial_intf_id
, p_serial_number => l_serial_rec.serial_num
, p_org_id => p_organization_id
, p_item_id => p_item_id
, p_product_txn_id => l_rti_tb(l_txn_id)(l_lot_num).rti_id
);
print_debug('Failure while Inserting MSNI records - lot and serial controlled item',1);
print_debug('insert_msni_helper: Error occurred while creating interface serials: ' || l_msg_data,1);
l_qty_to_insert := l_rti_tb(k)(l).quantity;
l_qty_to_insert := inv_convert.inv_um_convert
( item_id => p_item_id,
precision => 6,
from_quantity => l_rti_tb(k)(l).quantity,
from_unit => l_primary_uom_code,
to_unit => p_transfer_uom_code,
from_name => null,
to_name => null );
print_debug(' qty to insert = ' || l_qty_to_insert, 1);
IF l_avail_qty < l_qty_to_insert THEN
-- FAIL THE TXN NOT ENOUGH QTY AVAIABLE TO TRANSACT
IF (l_debug = 1) THEN
print_debug('Avaiable Qty is less than Txn Qty ' , 1);
,x_new_mmtt_id => l_mmtt_id_to_insert
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_mmtt_id_to_insert := p_original_temp_id;
select secondary_transaction_quantity
into l_original_lot_sec_qty
from mtl_transaction_lots_interface mtli
where mtli.lot_number = p_lot_number
and mtli.product_code = 'RCV'
and mtli.product_transaction_id = p_original_rti_id ;
l_lot_sec_qty_to_insert := l_original_lot_sec_qty * (l_qty_to_insert / p_transfer_quantity);
print_debug('Lot Secondary qty to insert = ' || l_lot_sec_qty_to_insert, 1);
print_debug('BEFORE CALLING THE insert_mtli_helper API ', 1);
print_debug(' p_txn_qty => '|| l_qty_to_insert,1);
print_debug(' p_secondary_quantit=> '|| l_lot_sec_qty_to_insert,1);
l_result := insert_mtli_helper
(p_txn_if_id => l_lot_temp_id
, p_lot_number => l_rti_tb(k)(l).lot_number
, p_txn_qty => l_qty_to_insert
, p_prm_qty => l_rti_tb(k)(l).quantity
, p_item_id => p_item_id
, p_org_id => p_organization_id
, p_serial_temp_id => l_rti_tb(k)(l).serial_intf_id
, p_product_txn_id => l_rti_tb(k)(l).rti_id
, p_secondary_quantity => l_lot_sec_qty_to_insert --OPM Convergence
, p_secondary_uom => p_sec_transfer_uom_code); --OPM Convergence
print_debug('Failure while Inserting MTLI records - lot and serial controlled item',1);
l_secondary_quantity := p_sec_transfer_quantity * (l_qty_to_insert/p_transfer_quantity);
, p_transfer_quantity => l_qty_to_insert
, p_transfer_uom_code => p_transfer_uom_code
, p_lot_control_code => p_lot_control_code
, p_serial_control_code => p_serial_control_code
, p_original_rti_id => l_new_intf_id
, p_original_temp_id => l_mmtt_id_to_insert
, p_lot_number => p_lot_number
, p_lpn_id => p_lpn_id
, p_transfer_lpn_id => p_transfer_lpn_id
, p_sec_transfer_quantity => l_secondary_quantity
, p_sec_transfer_uom_code => p_sec_transfer_uom_code
);