[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION insert_interface_code(
l_rcv_transaction_rec IN OUT NOCOPY rcvtxn_transaction_rec_tp
, p_organization_id IN NUMBER)
RETURN NUMBER IS
l_receipt_source_code VARCHAR2(30) := l_rcv_transaction_rec.receipt_source_code;
print_debug('entering insert_interface_code 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SELECT reason_id
INTO l_reason_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_mmtt_temp_id;
print_debug('l_reason_id '||l_reason_id||' Updated to RTI,Transaction Interface Id'||l_interface_transaction_id,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;
* Insert these two additional columns in RTI
*/
IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
l_validation_flag := 'Y';
print_debug('insert_inspect_rec_rti: validation_flag : ' || l_validation_flag || ', lpn_group_id: ' || l_lpn_group_id, 4);
SELECT muom.uom_code
INTO l_uom_code
FROM mtl_units_of_measure muom
WHERE muom.unit_of_measure = l_uom;
select backorder_delivery_detail_id into l_wip_entity_id
from mtl_txn_request_lines mtrl , mtl_material_transactions_temp mmtt
where mmtt.transaction_temp_id = l_mmtt_temp_id
and mtrl.line_id = mmtt.move_order_line_id
and mtrl.CROSSDOCK_TYPE = 2;
INSERT INTO rcv_transactions_interface
(
receipt_source_code
, interface_transaction_id
, GROUP_ID
, last_update_date
, last_updated_by
, created_by
, creation_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
, 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
, secondary_quantity -- Bug 13344122
, secondary_uom_code -- Bug 13344122
, validation_flag
, lpn_group_id
, project_id
, task_id
, org_id --
, uom_code
)
VALUES (
l_receipt_source_code
, l_interface_transaction_id
, l_group_id
, SYSDATE
, l_user_id
, l_user_id
, SYSDATE
, l_logon_id
, 'RCV'
, l_source_document_code
, l_dest_type_code
, l_transaction_date
, l_quantity
, l_uom
, l_shipment_hdr_id
, l_shipment_line_id
, l_substitute_code
, l_employee_id
, l_parent_transaction_id
, l_inspection_code
, l_quality_code
, l_po_hdr_id
, l_po_release_id
, l_po_line_id
, l_po_line_location_id
, l_po_dist_id
, l_po_rev_num
, l_po_unit_price
, l_currency_code
, l_currency_conv_rate
, l_req_line_id
, l_req_dist_id
, l_routing_id
, l_routing_step_id
, l_packing_slip
, l_vendor_item_num
, l_comments
, l_attribute_category
, l_attribute1
, l_attribute2
, l_attribute3
, l_attribute4
, l_attribute5
, l_attribute6
, l_attribute7
, l_attribute8
, l_attribute9
, l_attribute10
, l_attribute11
, l_attribute12
, l_attribute13
, l_attribute14
, l_attribute15
, l_transaction_type
, l_location_id
, 'PENDING'
, l_processor_value
, 'PENDING'
, l_category_id
, l_vendor_lot
, l_reason_id
, l_primary_qty
, l_primary_uom
, l_item_id
, l_item_revision
, l_org_id
, l_deliver_to_location_id
, l_dest_context
, l_vendor_id
, l_deliver_to_person_id
, l_subinventory
, l_locator_id
, l_wip_entity_id
, l_wip_line_id
, l_wip_repetitive_schd_id
, l_wip_operation_seq_num
, l_wip_resource_seq_num
, l_bom_resource_id
, l_mtl_lot
, l_mtl_serial
, l_movement_id
, TRUNC(l_currency_conv_date)
, l_currency_conv_type
, l_qa_collection_id
, l_ussgl_transaction_code
, l_government_context
, l_vendor_site_id
, l_oe_order_header_id
, l_oe_order_line_id
, l_customer_id
, l_customer_site_id
, l_put_away_rule_id
, l_put_away_strategy_id
, l_lpn_id
, l_transfer_lpn_id
, l_cost_group_id
, l_mmtt_temp_id
, 'Y'
, -- MOBILE_TXN
l_transfer_cost_group_id
, l_secondary_quantity -- Bug 13344122
, l_secondary_uom_code -- Bug 13344122
, l_validation_flag
, l_lpn_group_id
, l_project_id
, l_task_id
, l_operating_unit_id --
, l_uom_code
);
SELECT mp.lcm_enabled_flag
INTO v_lcm_enabled_org
FROM mtl_parameters mp
WHERE mp.organization_id = l_org_id;
SELECT rp.pre_receive
INTO v_pre_receive
FROM rcv_parameters rp
WHERE rp.organization_id = l_org_id;
SELECT LCM_SHIPMENT_LINE_ID, UNIT_LANDED_COST
INTO v_lcm_ship_line_id, v_unit_landed_cost
FROM rcv_shipment_lines
WHERE shipment_line_id = l_shipment_line_id;
UPDATE rcv_transactions_interface
SET lcm_shipment_line_id = v_lcm_ship_line_id,
unit_landed_cost = v_unit_landed_cost
WHERE interface_transaction_id = l_interface_transaction_id
AND to_organization_id = l_org_id;
print_debug('exiting insert_interface_code 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
END insert_interface_code;
PROCEDURE insert_lot_serial(
p_lot_serial_break_tbl IN inv_rcv_common_apis.trans_rec_tb_tp
, p_transaction_temp_id IN NUMBER
, p_lot_control_code IN NUMBER
, p_serial_control_code IN NUMBER
, p_interface_transaction_id IN NUMBER
) IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
print_debug('entering insert_lot_serial 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
INSERT INTO rcv_lots_interface
(
interface_transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, lot_num
, quantity
, transaction_date
, expiration_date
, primary_quantity
, item_id
, shipment_line_id
)
SELECT rti.interface_transaction_id
, rti.last_update_date
, rti.last_updated_by
, rti.creation_date
, rti.created_by
, rti.last_update_login
, rti.request_id
, rti.program_application_id
, rti.program_id
, rti.program_update_date
, mtlt.lot_number
, mtlt.transaction_quantity
, rti.transaction_date
, mtlt.lot_expiration_date
, mtlt.primary_quantity
, rti.item_id
, rti.shipment_line_id
FROM rcv_transactions_interface rti, mtl_transaction_lots_temp mtlt
WHERE rti.interface_transaction_id = p_interface_transaction_id
AND mtlt.transaction_temp_id = rti.interface_transaction_id;
INSERT INTO rcv_serials_interface
(
interface_transaction_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_date
, fm_serial_num
, to_serial_num
, serial_prefix
, lot_num
, vendor_serial_num
, vendor_lot_num
, item_id
, organization_id
)
SELECT rti.interface_transaction_id
, rti.last_update_date
, rti.last_updated_by
, rti.creation_date
, rti.created_by
, rti.last_update_login
, rti.request_id
, rti.program_application_id
, rti.program_id
, rti.program_update_date
, rti.transaction_date
, mtst.fm_serial_number
, mtst.to_serial_number
, mtst.serial_prefix
, mtlt.lot_number
, NULL
, rti.vendor_lot_num
, rti.item_id
, rti.to_organization_id
FROM rcv_transactions_interface rti, mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp mtst
WHERE rti.interface_transaction_id = p_interface_transaction_id
AND mtlt.transaction_temp_id = rti.interface_transaction_id
AND mtlt.serial_transaction_temp_id = mtst.transaction_temp_id;
INSERT INTO rcv_serials_interface
(
interface_transaction_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_date
, fm_serial_num
, to_serial_num
, serial_prefix
, lot_num
, vendor_serial_num
, vendor_lot_num
, item_id
, organization_id
)
SELECT rti.interface_transaction_id
, rti.last_update_date
, rti.last_updated_by
, rti.creation_date
, rti.created_by
, rti.last_update_login
, rti.request_id
, rti.program_application_id
, rti.program_id
, rti.program_update_date
, rti.transaction_date
, mtst.fm_serial_number
, mtst.to_serial_number
, mtst.serial_prefix
, NULL
, NULL
, rti.vendor_lot_num
, rti.item_id
, rti.to_organization_id
FROM rcv_transactions_interface rti, mtl_serial_numbers_temp mtst
WHERE rti.interface_transaction_id = p_interface_transaction_id
AND mtst.transaction_temp_id = rti.interface_transaction_id;
print_debug('exiting insert_lot_serial 10: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
END insert_lot_serial;
SELECT displayed_field
, lookup_code
INTO l_final_destination_type_dsp
, l_final_destination_type_code
FROM po_lookup_codes
WHERE lookup_code = 'INVENTORY'
AND lookup_type = 'RCV DESTINATION TYPE';
SELECT lpn_id
, transfer_lpn_id
, content_lpn_id
, cost_group_id
, put_away_rule_id
, put_away_strategy_id
INTO p_rcv_transaction_rec.lpn_id
, p_rcv_transaction_rec.transfer_lpn_id
, l_content_lpn_id
, 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_txn_temp_id;
SELECT cost_group_id
INTO p_rcv_transaction_rec.transfer_cost_group_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_rcv_rcvtxn_rec.shipment_line_id;
SELECT NVL(lpn_controlled_flag, 1)
INTO l_lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_organization_id;
SELECT project_id
, task_id
INTO l_project_id
, l_task_id
FROM po_req_distributions
WHERE requisition_line_id = l_req_line_id;
SELECT project_id
, task_id
INTO l_project_id
, l_task_id
FROM oe_order_lines_all
WHERE line_id = l_oe_order_line_id;
SELECT nvl(rsh.asn_type, 'NNN')
INTO l_asn_line_flag
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = p_rcv_rcvtxn_rec.shipment_line_id;
SELECT lookup_code
INTO l_tmp_destination_code
FROM po_lookup_codes
WHERE lookup_code = 'EXPENSE'
AND lookup_type = 'RCV DESTINATION TYPE';
SELECT lookup_code
INTO l_tmp_destination_code
FROM po_lookup_codes
WHERE lookup_code = 'INVENTORY'
AND lookup_type = 'RCV DESTINATION TYPE';
SELECT NVL(match_option, 'P')
INTO l_matchflag
FROM po_line_locations_all
WHERE line_location_id = l_linelocationid;
SELECT currency_conversion_date
, currency_conversion_rate
INTO l_ratedate
, l_rate
FROM rcv_transactions
WHERE transaction_id = l_rcvtrxid;
l_interface_transaction_id := insert_interface_code(
p_rcv_transaction_rec
, p_organization_id);
insert_lot_serial(
l_lot_serial_break_tbl
, p_transaction_temp_id
, p_lot_control_code
, p_serial_control_code
, l_interface_transaction_id);
UPDATE rcv_transactions_interface
SET transaction_status_code = 'PENDING'
, processing_status_code = 'PENDING'
WHERE parent_transaction_id = l_rcv_transaction_id
AND GROUP_ID = l_group_id
AND transaction_status_code = 'INSPECTION'
AND processing_status_code = 'INSPECTION'
AND transaction_type IN('ACCEPT', 'REJECT');
SELECT rsup.from_organization_id from_organization_id
, rsup.to_organization_id to_organization_id
, rt.source_document_code source_document_code
, rsh.receipt_source_code receipt_source_code
, rsup.rcv_transaction_id rcv_transaction_id
, rt.transaction_date transaction_date
, rt.transaction_type transaction_type
, rt.primary_unit_of_measure primary_uom
, rt.primary_quantity primary_quantity
, rsup.po_header_id po_header_id
, rt.po_revision_num po_revision_num
, rsup.po_release_id po_release_id
, rsh.vendor_id vendor_id
, rt.vendor_site_id vendor_site_id
, rsup.po_line_id po_line_id
, rt.po_unit_price po_unit_price
, rsl.category_id category_id
, rsup.item_id item_id
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, rsup.item_revision item_revision
, rsup.po_line_location_id po_line_location_id
, rt.po_distribution_id po_distribution_id
, rt.employee_id employee_id
, rsl.comments comments
, rsup.req_header_id req_header_id
, rsup.req_line_id req_line_id
, rsup.shipment_header_id shipment_header_id
, rsup.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 inspection_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
, pod.rate currency_conversion_rate
, pod.rate_date currency_conversion_date
, rt.currency_conversion_type currency_conversion_type
, rsl.req_distribution_id req_distribution_id
, rsup.destination_type_code destination_type_code_hold
, pod.destination_type_code final_destination_type_code
, rt.location_id location_id
, pod.deliver_to_person_id final_deliver_to_person_id
, pod.deliver_to_location_id 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
, rsup.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
, rsup.oe_order_header_id oe_order_header_id
, rt.oe_order_line_id oe_order_line_id
, rsh.customer_id customer_id
, rsh.customer_site_id customer_site_id
, pod.wip_entity_id wip_entity_id
, pod.wip_operation_seq_num po_operation_seq_num
, pod.wip_resource_seq_num po_resource_seq_num
, pod.wip_repetitive_schedule_id wip_repetitive_schedule_id
, pod.wip_line_id wip_line_id
, pod.bom_resource_id bom_resource_id
, pod.destination_subinventory final_subinventory
, rt.SECONDARY_QUANTITY --OPM Convergence
, rt.SECONDARY_UNIT_OF_MEASURE --OPM Convergence
--The following columns are needed for matching in cases where no LPN is involved
, rsup.to_subinventory from_subinventory_code
, rsup.to_locator_id from_locator_id
FROM rcv_transactions rt
, rcv_supply rsup
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, mtl_system_items msi
, po_lines pol
, po_distributions pod
WHERE rsup.rcv_transaction_id = v_rcv_txn_id
AND rsup.to_organization_id = p_organization_id
AND pod.line_location_id = rsup.po_line_location_id
AND pod.po_distribution_id = v_po_distribution_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rsh.shipment_header_id = rsup.shipment_header_id
AND pol.po_line_id = rsup.po_line_id
AND msi.organization_id = rsup.to_organization_id
AND msi.inventory_item_id = rsup.item_id
ORDER BY rt.transaction_date DESC;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT primary_unit_of_measure
INTO g_rcvtxn_match_table_gross(g_rcvtxn_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_id;
SELECT rsup.from_organization_id from_organization_id
, rsup.to_organization_id to_organization_id
, rt.source_document_code source_document_code
, rsh.receipt_source_code receipt_source_code
, rsup.rcv_transaction_id rcv_transaction_id
, rt.transaction_date transaction_date
, rt.transaction_type transaction_type
, rt.primary_unit_of_measure primary_uom
, rt.primary_quantity primary_quantity
, rsup.po_header_id po_header_id
, rt.po_revision_num po_revision_num
, rsup.po_release_id po_release_id
, rsh.vendor_id vendor_id
, rt.vendor_site_id vendor_site_id
, rsup.po_line_id po_line_id
, rt.po_unit_price po_unit_price
, rsl.category_id category_id
, rsup.item_id item_id
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, rsup.item_revision item_revision
, rsup.po_line_location_id po_line_location_id
, rt.po_distribution_id po_distribution_id
, rt.employee_id employee_id
, rsl.comments comments
, rsup.req_header_id req_header_id
, rsup.req_line_id req_line_id
, rsup.shipment_header_id shipment_header_id
, rsup.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 inspection_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
, pod.rate currency_conversion_rate
, pod.rate_date currency_conversion_date
, rt.currency_conversion_type currency_conversion_type
, rsl.req_distribution_id req_distribution_id
, rsup.destination_type_code destination_type_code_hold
, pod.destination_type_code final_destination_type_code
, rt.location_id location_id
, pod.deliver_to_person_id final_deliver_to_person_id
, pod.deliver_to_location_id 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
, rsup.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
, rsup.oe_order_header_id oe_order_header_id
, rt.oe_order_line_id oe_order_line_id
, rsh.customer_id customer_id
, rsh.customer_site_id customer_site_id
, pod.wip_entity_id wip_entity_id
, pod.wip_operation_seq_num po_operation_seq_num
, pod.wip_resource_seq_num po_resource_seq_num
, pod.wip_repetitive_schedule_id wip_repetitive_schedule_id
, pod.wip_line_id wip_line_id
, pod.bom_resource_id bom_resource_id
, pod.destination_subinventory final_subinventory
, rt.SECONDARY_QUANTITY --OPM Convergence
, rt.SECONDARY_UNIT_OF_MEASURE --OPM Convergence
--The following columns are needed for matching in cases where no LPN is involved
, rsup.to_subinventory from_subinventory_code
, rsup.to_locator_id from_locator_id
FROM rcv_transactions rt
, rcv_supply rsup
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, mtl_system_items msi
, po_lines pol
, po_distributions pod
WHERE rsup.rcv_transaction_id = v_rcv_txn_id
AND rsup.to_organization_id = p_organization_id
AND pod.line_location_id = rsup.po_line_location_id
AND pod.po_distribution_id = v_po_distribution_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rsh.shipment_header_id = rsup.shipment_header_id
AND pol.po_line_id = rsup.po_line_id
AND msi.organization_id = rsup.to_organization_id
AND msi.inventory_item_id = rsup.item_id
ORDER BY rt.transaction_date DESC;
SELECT rsup.from_organization_id from_organization_id
, rsup.to_organization_id to_organization_id
, rt.source_document_code source_document_code
, rsh.receipt_source_code receipt_source_code
, rsup.rcv_transaction_id rcv_transaction_id
, rt.transaction_date transaction_date
, rt.transaction_type transaction_type
, rt.primary_unit_of_measure primary_uom
, rt.primary_quantity primary_quantity
, rsup.po_header_id po_header_id
, rt.po_revision_num po_revision_num
, rsup.po_release_id po_release_id
, rsh.vendor_id vendor_id
, rt.vendor_site_id vendor_site_id
, rsup.po_line_id po_line_id
, rt.po_unit_price po_unit_price
, rsl.category_id category_id
, rsup.item_id item_id
, null
, null
, rsup.item_revision item_revision
, rsup.po_line_location_id po_line_location_id
, rt.po_distribution_id po_distribution_id
, rt.employee_id employee_id
, rsl.comments comments
, rsup.req_header_id req_header_id
, rsup.req_line_id req_line_id
, rsup.shipment_header_id shipment_header_id
, rsup.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 inspection_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
, pod.rate currency_conversion_rate
, pod.rate_date currency_conversion_date
, rt.currency_conversion_type currency_conversion_type
, rsl.req_distribution_id req_distribution_id
, rsup.destination_type_code destination_type_code_hold
, pod.destination_type_code final_destination_type_code
, rt.location_id location_id
, pod.deliver_to_person_id final_deliver_to_person_id
, pod.deliver_to_location_id final_deliver_to_location_id
, rsl.to_subinventory subinventory
, un_number_id un_number_id
, hazard_class_id hazard_class_id
, rsup.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
, rsup.oe_order_header_id oe_order_header_id
, rt.oe_order_line_id oe_order_line_id
, rsh.customer_id customer_id
, rsh.customer_site_id customer_site_id
, pod.wip_entity_id wip_entity_id
, pod.wip_operation_seq_num po_operation_seq_num
, pod.wip_resource_seq_num po_resource_seq_num
, pod.wip_repetitive_schedule_id wip_repetitive_schedule_id
, pod.wip_line_id wip_line_id
, pod.bom_resource_id bom_resource_id
, pod.destination_subinventory final_subinventory
, rt.SECONDARY_QUANTITY --OPM Convergence
, rt.SECONDARY_UNIT_OF_MEASURE --OPM Convergence
--The following columns are needed for matching in cases where no LPN is involved
, rsup.to_subinventory from_subinventory_code
, rsup.to_locator_id from_locator_id
FROM rcv_transactions rt
, rcv_supply rsup
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines pol
, po_distributions pod
WHERE rsup.rcv_transaction_id = v_rcv_txn_id
AND rsup.to_organization_id = p_organization_id
AND pod.line_location_id = rsup.po_line_location_id
AND pod.po_distribution_id = v_po_distribution_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rsh.shipment_header_id = rsup.shipment_header_id
AND pol.po_line_id = rsup.po_line_id
ORDER BY rt.transaction_date DESC;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT primary_unit_of_measure
INTO g_rcvtxn_match_table_gross(g_rcvtxn_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_id;
SELECT item_description
INTO g_rcvtxn_match_table_gross(g_rcvtxn_detail_index).item_desc
FROM po_lines_all pla
WHERE pla.po_header_id =g_rcvtxn_match_table_gross(g_rcvtxn_detail_index).po_header_id
AND pla.po_line_id =g_rcvtxn_match_table_gross(g_rcvtxn_detail_index).po_line_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 = p_rcvtxn_uom
AND ROWNUM < 2;
SELECT uom_code
INTO l_rcvtxn_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_rcvtxn_uom
AND ROWNUM < 2;
SELECT uom_code
INTO l_prim_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = l_rcvtxn_match_table_detail(match_result_count).primary_unit_of_measure
AND ROWNUM < 2;
-- then simply insert RTI with the MMTT column as null
IF p_original_txn_temp_id IS NULL OR l_rcvtxn_match_table_detail.COUNT = 1 THEN
l_new_txn_temp_id := p_original_txn_temp_id;
SELECT move_order_line_id
INTO l_orig_mol_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_original_txn_temp_id;
SELECT transaction_temp_id
INTO l_new_txn_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mo_splt_tb(1).line_id;
print_debug('create_po_rcvtxn_intf_rec RTI to be inserted with mmtt id: ' ||
l_new_txn_temp_id || ' p_transaction_temp_id = ' ||
p_transaction_temp_id,4);
* Call the split_lot API to split the lots and serials inserted from the UI
* based on the quantity of each RTI record
*/
IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
IF (p_lot_control_code > 1 OR p_serial_control_code > 1) THEN
--BUG 3326408
IF (p_lot_control_code > 1 AND p_serial_control_code = 6) THEN
IF (l_debug = 1) THEN
print_debug('create_po_rcvtxn_intf_rec 65.3: serial_control_code IS 6, need TO NULL OUT mtli', 4);
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
SELECT rsup.from_organization_id from_organization_id
, rsup.to_organization_id to_organization_id
, rt.source_document_code source_document_code
, rsh.receipt_source_code receipt_source_code
, rsup.rcv_transaction_id rcv_transaction_id
, rt.transaction_date transaction_date
, rt.transaction_type transaction_type
, rt.primary_unit_of_measure primary_uom
, rt.primary_quantity primary_quantity
, rsup.po_header_id po_header_id
, rt.po_revision_num po_revision_num
, rsup.po_release_id po_release_id
, rsh.vendor_id vendor_id
, rt.vendor_site_id vendor_site_id
, rsup.po_line_id po_line_id
, rt.po_unit_price po_unit_price
, rsl.category_id category_id
, rsup.item_id item_id
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, rsup.item_revision item_revision
, rsup.po_line_location_id po_line_location_id
, rt.po_distribution_id po_distribution_id
, rt.employee_id employee_id
, rsl.comments comments
, rsup.req_header_id req_header_id
, rsup.req_line_id req_line_id
, rsup.shipment_header_id shipment_header_id
, rsup.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 inspection_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
, rsup.destination_type_code destination_type_code_hold
, rsup.destination_type_code final_destination_type_code
, rt.location_id location_id
, rsl.deliver_to_person_id final_deliver_to_person_id
, rsl.deliver_to_location_id final_deliver_to_location_id
, rsl.to_subinventory subinventory
, msi.un_number_id un_number_id
, msi.hazard_class_id hazard_class_id
, rsup.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
, rsup.oe_order_header_id oe_order_header_id
, rt.oe_order_line_id oe_order_line_id
, rsh.customer_id customer_id
, rsh.customer_site_id customer_site_id
, NULL wip_entity_id
, NULL po_operation_seq_num
, NULL po_resource_seq_num
, NULL wip_repetitive_schedule_id
, NULL wip_line_id
, NULL bom_resource_id
, NULL final_subinventory
, rt.SECONDARY_QUANTITY --OPM Convergence
, rt.SECONDARY_UNIT_OF_MEASURE --OPM Convergence
--The following columns are needed for matching in cases where no LPN is involved
, rsup.to_subinventory from_subinventory_code
, rsup.to_locator_id from_locator_id
FROM rcv_transactions rt
, rcv_supply rsup
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, mtl_system_items msi
WHERE rsup.rcv_transaction_id = v_rcv_txn_id
AND rsup.shipment_line_id = v_shipment_line_id
AND rsup.to_organization_id = p_organization_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rsh.shipment_header_id = rsup.shipment_header_id
AND msi.organization_id = rsup.to_organization_id
AND msi.inventory_item_id = rsup.item_id
ORDER BY rt.transaction_date DESC;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT asn_type
INTO l_asn_type
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id;
SELECT receipt_source_code
INTO l_source_code
FROM rcv_shipment_headers
WHERE shipment_header_id = p_shipment_header_id;
SELECT lot_number INTO l_lot_number
FROM mtl_transaction_lots_interface
WHERE product_transaction_id = p_transaction_temp_id ;
SELECT primary_unit_of_measure
INTO g_rcvtxn_match_table_gross(g_rcvtxn_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_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 = p_rcvtxn_uom
AND ROWNUM < 2;
SELECT unit_of_measure,
uom_code
INTO l_rcv_transaction_rec.secondary_uom,
l_rcv_transaction_rec.secondary_uom_code
FROM MTL_UNITS_OF_MEASURE_VL
WHERE unit_of_measure = p_secondary_uom
OR uom_code = p_secondary_uom;
-- then simply insert RTI with the MMTT column as null
IF p_original_txn_temp_id IS NULL OR l_rcvtxn_match_table_detail.COUNT = 1 THEN
l_new_txn_temp_id := p_original_txn_temp_id;
SELECT move_order_line_id
INTO l_orig_mol_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_original_txn_temp_id;
SELECT transaction_temp_id
INTO l_new_txn_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mo_splt_tb(1).line_id;
print_debug('create_int_shp_rcvtxn_intf_rec RTI to be inserted with mmtt id: ' ||
l_new_txn_temp_id || ' p_transaction_temp_id = ' ||
p_transaction_temp_id,4);
print_debug('create_int_shp_rcvtxn_intf_rec 125 - before update_rcv_serials_supply' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
* No updates to rcv_serials_supply if INV J and PO J are installed
*/
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
IF l_rcv_rcvtxn_rec.req_line_id IS NOT NULL
AND p_serial_control_code NOT IN(1, 6) THEN
-- update rss for req
update_rcv_serials_supply(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => x_message
, p_shipment_line_id => l_rcv_rcvtxn_rec.shipment_line_id
);
print_debug('create_int_shp_rcvtxn_intf_rec: 127 - before update_rcv_serials_supply' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
print_debug('create_int_shp_rcvtxn_intf_rec: 127.6 - INV and PO patch levels are J or higher. No update to rcv_supply', 4);
* Call the split_lot API to split the lots and serials inserted from the UI
* based on the quantity of each RTI record
*/
IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
l_msni_count := 0;
SELECT count(1)
INTO l_msni_count
FROM mtl_serial_numbers_interface
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = NULL
WHERE product_transaction_id = p_transaction_temp_id
AND product_code = 'RCV';
SELECT rsup.from_organization_id from_organization_id
, rsup.to_organization_id to_organization_id
, rt.source_document_code source_document_code
, rsh.receipt_source_code receipt_source_code
, rsup.rcv_transaction_id rcv_transaction_id
, rt.transaction_date transaction_date
, rt.transaction_type transaction_type
, rt.primary_unit_of_measure primary_uom
, rt.primary_quantity primary_quantity
, rsup.po_header_id po_header_id
, rt.po_revision_num po_revision_num
, rsup.po_release_id po_release_id
, rsh.vendor_id vendor_id
, rt.vendor_site_id vendor_site_id
, rsup.po_line_id po_line_id
, rt.po_unit_price po_unit_price
, rsl.category_id category_id
, rsup.item_id item_id
, msi.serial_number_control_code serial_number_control_code
, msi.lot_control_code lot_control_code
, rsup.item_revision item_revision
, rsup.po_line_location_id po_line_location_id
, rt.po_distribution_id po_distribution_id
, rt.employee_id employee_id
, rsl.comments comments
, rsup.req_header_id req_header_id
, rsup.req_line_id req_line_id
, rsup.shipment_header_id shipment_header_id
, rsup.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 inspection_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
, rsup.destination_type_code destination_type_code_hold
, rsup.destination_type_code final_destination_type_code
, rt.location_id location_id
, rsl.deliver_to_person_id final_deliver_to_person_id
, rsl.deliver_to_location_id final_deliver_to_location_id
, rsl.to_subinventory subinventory
, msi.un_number_id un_number_id
, msi.hazard_class_id hazard_class_id
, rsup.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
, rsup.oe_order_header_id oe_order_header_id
, rt.oe_order_line_id oe_order_line_id
, rsh.customer_id customer_id
, rsh.customer_site_id customer_site_id
, NULL wip_entity_id
, NULL po_operation_seq_num
, NULL po_resource_seq_num
, NULL wip_repetitive_schedule_id
, NULL wip_line_id
, NULL bom_resource_id
, NULL final_subinventory
, rt.SECONDARY_QUANTITY --OPM Convergence
, rt.SECONDARY_UNIT_OF_MEASURE --OPM Convergence
--The following columns are needed for matching in cases where no LPN is involved
, rsup.to_subinventory from_subinventory_code
, rsup.to_locator_id from_locator_id
FROM rcv_transactions rt
, rcv_supply rsup
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, mtl_system_items msi
WHERE rsup.rcv_transaction_id = v_rcv_txn_id
AND rsup.oe_order_line_id = v_order_line_id
AND rsup.to_organization_id = p_organization_id
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rsh.shipment_header_id = rsup.shipment_header_id
AND msi.organization_id = rsup.to_organization_id
AND msi.inventory_item_id = rsup.item_id
ORDER BY rt.transaction_date DESC;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT primary_unit_of_measure
INTO g_rcvtxn_match_table_gross(g_rcvtxn_detail_index).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = p_item_id
AND mtl_system_items.organization_id = p_organization_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 = p_rcvtxn_uom
AND ROWNUM < 2;
-- then simply insert RTI with the MMTT column as null
IF p_original_txn_temp_id IS NULL OR l_rcvtxn_match_table_detail.COUNT = 1 THEN
l_new_txn_temp_id := p_original_txn_temp_id;
SELECT move_order_line_id
INTO l_orig_mol_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_original_txn_temp_id;
SELECT transaction_temp_id
INTO l_new_txn_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mo_splt_tb(1).line_id;
print_debug('create_rma_rcvtxn_intf_rec RTI to be inserted with mmtt id: ' ||
l_new_txn_temp_id || ' p_transaction_temp_id = ' ||
p_transaction_temp_id,4);
* Call the split_lot API to split the lots and serials inserted from the UI
* based on the quantity of each RTI record
*/
IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
(inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
IF (p_lot_control_code > 1 OR p_serial_control_code > 1) THEN
l_split_lot_serial_ok := inv_rcv_integration_apis.split_lot_serial(
p_api_version => 1.0
, p_init_msg_lst => FND_API.G_FALSE
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => x_message
, p_new_rti_info => l_new_rti_info);
SELECT rsup.po_header_id po_header_id
,rsup.oe_order_header_id oe_order_header_id
FROM rcv_shipment_headers rsh
,rcv_supply rsup
WHERE rsh.receipt_num = p_receipt_num
AND rsh.shipment_header_id = rsup.shipment_header_id
AND rsup.to_organization_id = p_organization_id
AND rsup.item_id = p_item_id
AND Nvl(rsup.ITEM_REVISION, '@@##') = Nvl(p_revision, '@@##'); /* Bug 13598673 to pick right po_header_id*/
/* SELECT MIN(rti.interface_transaction_id) */
SELECT MAX(rti.interface_transaction_id)
FROM rcv_transactions_interface rti
WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
GROUP BY rti.lpn_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 MIN(rti.interface_transaction_id) */
SELECT MAX(rti.interface_transaction_id)
FROM rcv_transactions_interface rti
WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
GROUP BY rti.lpn_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 DISTINCT rti.interface_transaction_id
FROM rcv_transactions_interface rti
WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_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 unit_of_measure
INTO l_rcvtxn_uom
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND uom_code = p_rcvtxn_uom_code
AND ROWNUM < 2;
SELECT po_header_id
, po_line_id
, po_release_id
INTO l_po_header_id
, l_po_line_id
, l_po_release_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;
g_rcvtxn_match_table_gross.DELETE;
PROCEDURE update_rcv_serials_supply(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_shipment_line_id IN NUMBER
) IS
l_progress VARCHAR2(10);
print_debug('Enter update_rcv_serials_supply 10' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
SAVEPOINT sp_update_rss;
SELECT rsi.fm_serial_num
INTO l_serial_number
FROM rcv_serials_interface rsi, rcv_transactions_interface rti
WHERE rti.shipment_line_id = p_shipment_line_id
AND rti.interface_transaction_id = rsi.interface_transaction_id;
UPDATE rcv_serials_supply
SET shipment_line_id = (SELECT shipment_line_id
FROM rcv_serials_supply
WHERE serial_num = l_serial_number)
WHERE shipment_line_id = p_shipment_line_id;
UPDATE rcv_serials_supply
SET shipment_line_id = p_shipment_line_id
WHERE serial_num = l_serial_number;
print_debug('Complete update_rcv_serials_supply 40' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
inv_mobile_helper_functions.sql_error('INV_rcv_std_deliver_apis.update_rcv_serials_supply', l_progress, SQLCODE);
print_debug('Exception in update_rcv_serials_supply 50' || SQLCODE || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 1);
END update_rcv_serials_supply;
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
--Bug 5250046: Removed the nvl and Modified the condition on item_revision.
AND (p_revision is null or rs.item_revision = 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_transaction_temp_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
,Nvl(rls.primary_quantity,0) lot_prim_qty
,Nvl(rls.quantity,0) lot_qty
,decode(rt.uom_code, p_rcvtxn_uom_code, 1, 2) ORDERING1
,decode(rt.uom_code, p_rcvtxn_uom_code, (p_rcvtxn_qty - 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
--Bug 5250046: Removed the nvl and Modified the condition on item_revision.
AND (p_revision is null or rs.item_revision = 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)
--4502518 Issue 26: redundant check. Also, it will fail
-- when putting lines that have been corrected
-- 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)
AND --14133874
((rsl.shipment_line_id = p_reference_id AND p_reference='SHIPMENT_LINE_ID')
OR
(rsl.po_line_location_id = p_reference_id AND p_reference='PO_LINE_LOCATION_ID')
OR
(rsl.oe_order_line_id = p_reference_id AND p_reference='ORDER_LINE_ID')
OR
(rsl.po_distribution_id = p_reference_id AND p_reference='PO_DISTRIBUTION_ID') --bug 14734002
)
--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 UOM_CODE INTO l_rtv_rec.uom_code FROM
mtl_item_uoms_view WHERE organization_id=p_organization_id
AND inventory_item_id = p_item_id
AND unit_of_measure =
( SELECT unit_of_measure FROM RCV_TRANSACTIONS
WHERE TRANSACTION_ID = l_rtv_rec.rcv_transaction_id);
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 => 5,
from_quantity => l_avail_qty,
from_unit => l_rtv_rec.uom_code,
to_unit => p_rcvtxn_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 => 5,
from_quantity => l_remaining_prim_qty,
from_unit => l_primary_uom_code,
to_unit => p_rcvtxn_uom_code,
from_name => null,
to_name => null ); */
l_qty_to_insert := l_remaining_qty; --10328780
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_transaction_temp_id
AND product_code = 'RCV';
l_mmtt_id_to_insert := p_original_txn_temp_id;
l_secondary_quantity := p_secondary_quantity * (l_qty_to_insert/p_rcvtxn_qty);
, p_rcvtxn_qty => L_qty_to_insert
, p_rcvtxn_uom_code => p_rcvtxn_uom_code
, p_transaction_temp_id => l_new_intf_id
, p_lot_control_code => p_lot_control_code
, p_serial_control_code => p_serial_control_code
, p_original_txn_temp_id => l_mmtt_id_to_insert
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_inspection_status_code => p_inspection_status_code
, p_lpn_id => p_lpn_id
, p_transfer_lpn_id => p_transfer_lpn_id
, p_lot_number => p_lot_number
, p_parent_txn_id => l_rtv_rec.rcv_transaction_id
, p_secondary_quantity => l_secondary_quantity --OPM Integration
, p_secondary_uom => p_secondary_uom --OPM Integration
);
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 => 5,
from_quantity => l_rti_tb(k)(l).quantity,
from_unit => l_primary_uom_code,
to_unit => p_rcvtxn_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('l_avail_qty: ' || l_avail_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_txn_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_transaction_temp_id ;
l_lot_sec_qty_to_insert := l_original_lot_sec_qty * (l_qty_to_insert / p_rcvtxn_qty);
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_secondary_uom); --OPM Convergence
print_debug('Failure while Inserting MTLI records - lot and serial controlled item',1);
l_secondary_quantity := p_secondary_quantity * (l_qty_to_insert/p_rcvtxn_qty);
, p_rcvtxn_qty => L_qty_to_insert
, p_rcvtxn_uom_code => p_rcvtxn_uom_code
, p_transaction_temp_id => l_new_intf_id
, p_lot_control_code => p_lot_control_code
, p_serial_control_code => p_serial_control_code
, p_original_txn_temp_id => l_mmtt_id_to_insert
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_inspection_status_code => p_inspection_status_code
, p_lpn_id => p_lpn_id
, p_transfer_lpn_id => p_transfer_lpn_id
, p_lot_number => p_lot_number
, p_parent_txn_id => l_parent_txn_id
, p_secondary_quantity => l_secondary_quantity --OPM Integration
, p_secondary_uom => p_secondary_uom --OPM Integration
);