[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_flag NUMBER;
g_insert_flag NUMBER;
g_updated_prior BOOLEAN := FALSE; -- Bug 6371673
SELECT cycle_count_header_name,
cycle_count_header_id,
description,
inventory_adjustment_account,
orientation_code,
onhand_visible_flag,
zero_count_flag,
disable_date,
approval_option_code,
automatic_recount_flag,
unscheduled_count_entry,
approval_tolerance_positive,
approval_tolerance_negative,
cost_tolerance_positive,
cost_tolerance_negative,
hit_miss_tolerance_positive,
hit_miss_tolerance_negative,
serial_count_option,
serial_detail_option,
serial_adjustment_option,
serial_discrepancy_option,
container_adjustment_option,
container_discrepancy_option,
container_enabled_flag,
cycle_count_type,
schedule_empty_locations
FROM mtl_cycle_count_headers
WHERE organization_id = p_organization_id
AND trunc(nvl(disable_date, sysdate+1)) > trunc(sysdate) --Changed for bug 5519506
AND cycle_count_header_name LIKE ( p_cycle_count )
AND ( ( cycle_count_header_id IN (
SELECT UNIQUE cycle_count_header_id
FROM mtl_cycle_count_entries
WHERE organization_id = p_organization_id
AND entry_status_code IN ( 1, 3 ) )
)
OR NVL ( unscheduled_count_entry, 2 ) = 1
)
ORDER BY 1;
PROCEDURE update_count_list_sequence (
p_organization_id NUMBER,
p_cycle_count_header_id NUMBER,
x_count_list_sequence OUT NOCOPY NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT NVL ( MAX ( count_list_sequence ), 0 ) + 1
INTO l_count_list_sequence
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id;
UPDATE mtl_cycle_count_headers
SET next_user_count_sequence = l_count_list_sequence + 1
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id;
END update_count_list_sequence;
SELECT *
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory
AND NVL ( locator_id, -99999 ) = NVL ( p_locator_id, -99999 )
AND NVL ( parent_lpn_id, -99999 ) = NVL ( p_parent_lpn_id, -99999 )
AND inventory_item_id = p_inventory_item_id
AND NVL ( revision, '@@@@@' ) = NVL ( p_revision, '@@@@@' )
AND NVL ( lot_number, '@@@@@' ) = NVL ( p_lot_number, '@@@@@' )
AND NVL ( serial_number, '@@@@@' ) =
NVL ( l_current_serial, '@@@@@' )
AND entry_status_code IN ( 1, 3 );
SELECT *
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory
AND NVL ( locator_id, -99999 ) = NVL ( p_locator_id, -99999 )
AND NVL ( parent_lpn_id, -99999 ) = NVL ( p_parent_lpn_id, -99999 )
AND inventory_item_id = p_inventory_item_id
AND NVL ( revision, '@@@@@' ) = NVL ( p_revision, '@@@@@' )
AND NVL ( lot_number, '@@@@@' ) = NVL ( p_lot_number, '@@@@@' )
AND entry_status_code IN ( 1, 3 );
SELECT *
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id
AND NVL ( parent_lpn_id, -99999 ) = NVL ( p_parent_lpn_id, -99999 )
AND inventory_item_id = p_inventory_item_id
AND NVL ( revision, '@@@@@' ) = NVL ( p_revision, '@@@@@' )
AND NVL ( lot_number, '@@@@@' ) = NVL ( p_lot_number, '@@@@@' )
AND NVL ( serial_number, '@@@@@' ) =
NVL ( l_current_serial, '@@@@@' )
AND entry_status_code IN ( 1, 3 );
SELECT *
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id
AND NVL ( parent_lpn_id, -99999 ) = NVL ( p_parent_lpn_id, -99999 )
AND inventory_item_id = p_inventory_item_id
AND NVL ( revision, '@@@@@' ) = NVL ( p_revision, '@@@@@' )
AND NVL ( lot_number, '@@@@@' ) = NVL ( p_lot_number, '@@@@@' )
AND entry_status_code IN ( 1, 3 );
SELECT *
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND serial_number = NVL ( l_current_serial, '@@@@@' )
AND entry_status_code IN ( 1, 3 );
SELECT MIN ( cycle_count_entry_id )
INTO l_dispatched_task
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory
AND NVL ( locator_id, -99999 ) = NVL ( p_locator_id, -99999 )
AND inventory_item_id = p_inventory_item_id
AND NVL ( revision, '@@@@@' ) = NVL ( p_revision, '@@@@@' )
AND entry_status_code IN ( 1, 3 );
SELECT COUNT ( * )
INTO l_dispatched_count
FROM wms_dispatched_tasks
WHERE task_type = 3
AND organization_id = p_organization_id
AND transaction_temp_id = l_dispatched_task
AND person_id <> NVL ( g_employee_id, -999 );
pre_update ( );
update_row ( );
SELECT NVL ( serial_discrepancy_option, 2 ),
NVL ( container_discrepancy_option, 2 )
INTO l_serial_discrepancy,
l_container_discrepancy
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id;
pre_update ( );
update_row ( );
print_debug ( 'Unscheduled single serial entry to be inserted'
);
/*update_count_list_sequence
(p_organization_id => p_organization_id,
p_cycle_count_header_id => p_cycle_count_header_id,
x_count_list_sequence => l_count_list_sequence);*/
g_cc_entry.last_update_date := SYSDATE;
g_cc_entry.last_updated_by := p_user_id;
g_cc_entry.last_update_login := g_login_id;
pre_insert ( );
insert_row ( );
pre_update ( );
update_row ( );
SELECT NVL ( serial_discrepancy_option, 2 ),
NVL ( container_discrepancy_option, 2 )
INTO l_serial_discrepancy,
l_container_discrepancy
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id;
pre_update ( );
update_row ( );
pre_update ( );
update_row ( );
SELECT NVL ( container_discrepancy_option, 2 )
INTO l_container_discrepancy
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id;
pre_update ( );
update_row ( );
print_debug ( 'Unscheduled non-serial entry to be inserted'
);
/*update_count_list_sequence
(p_organization_id => p_organization_id,
p_cycle_count_header_id => p_cycle_count_header_id,
x_count_list_sequence => l_count_list_sequence);*/
g_cc_entry.last_update_date := SYSDATE;
g_cc_entry.last_updated_by := p_user_id;
g_cc_entry.last_update_login := g_login_id;
pre_insert ( );
insert_row ( );
PROCEDURE delete_wdt(
p_cycle_count_header_id IN NUMBER ,
p_organization_id IN NUMBER ,
p_subinventory IN VARCHAR2 ,
p_locator_id IN NUMBER ,
p_parent_lpn_id IN NUMBER ,
p_inventory_item_id IN NUMBER ,
p_revision IN VARCHAR2 ,
p_lot_number IN VARCHAR2 ,
p_from_serial_number IN VARCHAR2 ,
p_to_serial_number IN VARCHAR2 ,
p_count_quantity IN NUMBER ,
p_count_uom IN VARCHAR2 ,
p_unscheduled_count_entry IN NUMBER ,
p_user_id IN NUMBER ,
p_cost_group_id IN NUMBER )
IS
l_cycle_count_entry_id NUMBER;
SELECT cycle_count_entry_id
FROM mtl_cycle_count_entries mcce, wms_dispatched_tasks wdt
WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.cycle_count_entry_id = wdt.transaction_temp_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND mcce.locator_id = p_locator_id
AND NVL (mcce.parent_lpn_id, -99999) = NVL ( p_parent_lpn_id, -99999)
AND mcce.inventory_item_id = NVL (p_inventory_item_id, mcce.inventory_item_id)
AND NVL (mcce.revision, '@@@@@' ) = NVL (p_revision , '@@@@@' )
AND NVL (mcce.lot_number, '@@@@@' ) = NVL ( p_lot_number, '@@@@@' )
AND NVL (mcce.serial_number, '@@@@@' ) = NVL ( p_from_serial_number, '@@@@@' )
AND NVL (mcce.serial_number, '@@@@@' ) = NVL ( p_to_serial_number, '@@@@@' )
AND mcce.entry_status_code IN (2, 4, 5 );
print_debug ( '***In delete_wdt ***');
DELETE FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = l_cycle_count_entry_id;
print_debug('** Deleted wms_dispatched_tasks record with transaction_temp_id : ' || l_cycle_count_entry_id);
print_debug ( 'Exiting delete_wdt');
END delete_wdt;
PROCEDURE insert_row
IS
l_return_status VARCHAR2 ( 300 );
print_debug ( '***insert_row***' );
SELECT outermost_lpn_id
INTO g_cc_entry.outermost_lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = g_cc_entry.parent_lpn_id;
INSERT INTO mtl_cycle_count_entries
( cycle_count_entry_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
count_list_sequence,
count_date_first,
count_date_current,
count_date_prior,
count_date_dummy,
counted_by_employee_id_first,
counted_by_employee_id_current,
counted_by_employee_id_prior,
counted_by_employee_id_dummy,
count_uom_first,
count_uom_current,
count_uom_prior,
count_quantity_first,
count_quantity_current,
count_quantity_prior,
inventory_item_id,
subinventory,
entry_status_code,
count_due_date,
organization_id,
cycle_count_header_id,
number_of_counts,
locator_id,
adjustment_quantity,
adjustment_date,
adjustment_amount,
item_unit_cost,
inventory_adjustment_account,
approval_date,
approver_employee_id,
revision,
lot_number,
lot_control,
system_quantity_first,
system_quantity_current,
system_quantity_prior,
reference_first,
reference_current,
reference_prior,
primary_uom_quantity_first,
primary_uom_quantity_current,
primary_uom_quantity_prior,
count_type_code,
transaction_reason_id,
request_id,
program_application_id,
program_id,
program_update_date,
approval_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
serial_number,
serial_detail,
approval_condition,
neg_adjustment_quantity,
neg_adjustment_amount,
export_flag,
task_priority,
standard_operation_id,
parent_lpn_id,
outermost_lpn_id,
cost_group_id
-- INVCONV, NSRIVAST
,secondary_uom_quantity_first
,secondary_uom_quantity_current
,secondary_uom_quantity_prior
,count_secondary_uom_first
,count_secondary_uom_current
,count_secondary_uom_prior
-- INVCONV, NSRIVAST
)
VALUES ( g_cc_entry.cycle_count_entry_id,
g_cc_entry.last_update_date,
g_cc_entry.last_updated_by,
g_cc_entry.creation_date,
g_cc_entry.created_by,
g_cc_entry.last_update_login,
g_cc_entry.count_list_sequence,
g_cc_entry.count_date_first,
g_cc_entry.count_date_current,
g_cc_entry.count_date_prior,
g_cc_entry.count_date_dummy,
g_cc_entry.counted_by_employee_id_first,
g_cc_entry.counted_by_employee_id_current,
g_cc_entry.counted_by_employee_id_prior,
g_cc_entry.counted_by_employee_id_dummy,
g_cc_entry.count_uom_first,
g_cc_entry.count_uom_current,
g_cc_entry.count_uom_prior,
g_cc_entry.count_quantity_first,
g_cc_entry.count_quantity_current,
g_cc_entry.count_quantity_prior,
g_cc_entry.inventory_item_id,
g_cc_entry.subinventory,
g_cc_entry.entry_status_code,
g_cc_entry.count_due_date,
g_cc_entry.organization_id,
g_cc_entry.cycle_count_header_id,
g_cc_entry.number_of_counts,
g_cc_entry.locator_id,
g_cc_entry.adjustment_quantity,
g_cc_entry.adjustment_date,
g_cc_entry.adjustment_amount,
g_cc_entry.item_unit_cost,
g_cc_entry.inventory_adjustment_account,
g_cc_entry.approval_date,
g_cc_entry.approver_employee_id,
g_cc_entry.revision,
g_cc_entry.lot_number,
g_cc_entry.lot_control,
g_cc_entry.system_quantity_first,
g_cc_entry.system_quantity_current,
g_cc_entry.system_quantity_prior,
g_cc_entry.reference_first,
g_cc_entry.reference_current,
g_cc_entry.reference_prior,
g_cc_entry.primary_uom_quantity_first,
g_cc_entry.primary_uom_quantity_current,
g_cc_entry.primary_uom_quantity_prior,
g_cc_entry.count_type_code,
g_cc_entry.transaction_reason_id,
g_cc_entry.request_id,
g_cc_entry.program_application_id,
g_cc_entry.program_id,
g_cc_entry.program_update_date,
g_cc_entry.approval_type,
g_cc_entry.attribute_category,
g_cc_entry.attribute1,
g_cc_entry.attribute2,
g_cc_entry.attribute3,
g_cc_entry.attribute4,
g_cc_entry.attribute5,
g_cc_entry.attribute6,
g_cc_entry.attribute7,
g_cc_entry.attribute8,
g_cc_entry.attribute9,
g_cc_entry.attribute10,
g_cc_entry.attribute11,
g_cc_entry.attribute12,
g_cc_entry.attribute13,
g_cc_entry.attribute14,
g_cc_entry.attribute15,
LTRIM ( RTRIM ( g_cc_entry.serial_number ) ),
/* BUG2842145*/
g_cc_entry.serial_detail,
g_cc_entry.approval_condition,
g_cc_entry.neg_adjustment_quantity,
g_cc_entry.neg_adjustment_amount,
g_cc_entry.export_flag,
g_cc_entry.task_priority,
g_cc_entry.standard_operation_id,
g_cc_entry.parent_lpn_id,
g_cc_entry.outermost_lpn_id,
g_cc_entry.cost_group_id
-- INVCONV, NSRIVAST
,g_cc_entry.secondary_uom_quantity_first
,g_cc_entry.secondary_uom_quantity_current
,g_cc_entry.secondary_uom_quantity_prior
,g_cc_entry.count_secondary_uom_first
,g_cc_entry.count_secondary_uom_current
,g_cc_entry.count_secondary_uom_prior
-- INVCONV, NSRIVAST
);
END insert_row;
PROCEDURE update_row
IS
l_return_status VARCHAR2 ( 300 );
print_debug ( '***update_row***' );
g_cc_entry.last_update_date := SYSDATE;
g_cc_entry.last_updated_by := g_user_id;
g_cc_entry.last_update_login := g_login_id;
SELECT outermost_lpn_id
INTO g_cc_entry.outermost_lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = g_cc_entry.parent_lpn_id;
UPDATE mtl_cycle_count_entries
SET last_update_date = g_cc_entry.last_update_date,
last_updated_by = g_cc_entry.last_updated_by,
last_update_login = g_cc_entry.last_update_login,
count_list_sequence = g_cc_entry.count_list_sequence,
count_date_first = g_cc_entry.count_date_first,
count_date_current = g_cc_entry.count_date_current,
count_date_prior = g_cc_entry.count_date_prior,
count_date_dummy = g_cc_entry.count_date_dummy,
counted_by_employee_id_first =
g_cc_entry.counted_by_employee_id_first,
counted_by_employee_id_current =
g_cc_entry.counted_by_employee_id_current,
counted_by_employee_id_prior =
g_cc_entry.counted_by_employee_id_prior,
counted_by_employee_id_dummy =
g_cc_entry.counted_by_employee_id_dummy,
count_uom_first = g_cc_entry.count_uom_first,
count_uom_current = g_cc_entry.count_uom_current,
count_uom_prior = g_cc_entry.count_uom_prior,
count_quantity_first = g_cc_entry.count_quantity_first,
count_quantity_current = g_cc_entry.count_quantity_current,
count_quantity_prior = g_cc_entry.count_quantity_prior,
inventory_item_id = g_cc_entry.inventory_item_id,
subinventory = g_cc_entry.subinventory,
entry_status_code = g_cc_entry.entry_status_code,
count_due_date = g_cc_entry.count_due_date,
organization_id = g_cc_entry.organization_id,
cycle_count_header_id = g_cc_entry.cycle_count_header_id,
number_of_counts = g_cc_entry.number_of_counts,
locator_id = g_cc_entry.locator_id,
adjustment_quantity = g_cc_entry.adjustment_quantity,
adjustment_date = g_cc_entry.adjustment_date,
adjustment_amount = g_cc_entry.adjustment_amount,
item_unit_cost = g_cc_entry.item_unit_cost,
inventory_adjustment_account =
g_cc_entry.inventory_adjustment_account,
approval_date = g_cc_entry.approval_date,
approver_employee_id = g_cc_entry.approver_employee_id,
revision = g_cc_entry.revision,
lot_number = g_cc_entry.lot_number,
lot_control = g_cc_entry.lot_control,
system_quantity_first = g_cc_entry.system_quantity_first,
system_quantity_current = g_cc_entry.system_quantity_current,
system_quantity_prior = g_cc_entry.system_quantity_prior,
reference_first = g_cc_entry.reference_first,
reference_current = g_cc_entry.reference_current,
reference_prior = g_cc_entry.reference_prior,
primary_uom_quantity_first = g_cc_entry.primary_uom_quantity_first,
primary_uom_quantity_current =
g_cc_entry.primary_uom_quantity_current,
primary_uom_quantity_prior = g_cc_entry.primary_uom_quantity_prior,
count_type_code = g_cc_entry.count_type_code,
transaction_reason_id = g_cc_entry.transaction_reason_id,
approval_type = g_cc_entry.approval_type,
attribute_category = g_cc_entry.attribute_category,
attribute1 = g_cc_entry.attribute1,
attribute2 = g_cc_entry.attribute2,
attribute3 = g_cc_entry.attribute3,
attribute4 = g_cc_entry.attribute4,
attribute5 = g_cc_entry.attribute5,
attribute6 = g_cc_entry.attribute6,
attribute7 = g_cc_entry.attribute7,
attribute8 = g_cc_entry.attribute8,
attribute9 = g_cc_entry.attribute9,
attribute10 = g_cc_entry.attribute10,
attribute11 = g_cc_entry.attribute11,
attribute12 = g_cc_entry.attribute12,
attribute13 = g_cc_entry.attribute13,
attribute14 = g_cc_entry.attribute14,
attribute15 = g_cc_entry.attribute15,
serial_number = g_cc_entry.serial_number,
serial_detail = g_cc_entry.serial_detail,
approval_condition = g_cc_entry.approval_condition,
neg_adjustment_quantity = g_cc_entry.neg_adjustment_quantity,
neg_adjustment_amount = g_cc_entry.neg_adjustment_amount,
parent_lpn_id = g_cc_entry.parent_lpn_id,
outermost_lpn_id = g_cc_entry.outermost_lpn_id,
cost_group_id = g_cc_entry.cost_group_id
-- INVCONV, NSRIVAST
,secondary_uom_quantity_first = g_cc_entry.secondary_uom_quantity_first ,
secondary_uom_quantity_current = g_cc_entry.secondary_uom_quantity_current,
secondary_uom_quantity_prior = g_cc_entry.secondary_uom_quantity_prior ,
count_secondary_uom_first = g_cc_entry.count_secondary_uom_first,
count_secondary_uom_current = g_cc_entry.count_secondary_uom_current,
count_secondary_uom_prior = g_cc_entry.count_secondary_uom_prior,
-- INVCONV, NSRIVAST
-- nsinghi Bug#6052831 START
secondary_adjustment_quantity = g_cc_entry.secondary_adjustment_quantity,
secondary_system_qty_current = g_cc_entry.secondary_system_qty_current,
secondary_system_qty_first = g_cc_entry.secondary_system_qty_first,
secondary_system_qty_prior = g_cc_entry.secondary_system_qty_prior
-- nsinghi Bug#6052831 END
WHERE cycle_count_entry_id = g_cc_entry.cycle_count_entry_id;
END update_row;
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT abc_class_id
INTO l_abc_class_id
FROM mtl_cycle_count_items
WHERE cycle_count_header_id = l_cycle_count_header_id
AND inventory_item_id = l_inventory_item_id;
SELECT approval_tolerance_positive,
approval_tolerance_negative
INTO l_item_app_tol_pos,
l_item_app_tol_neg
FROM mtl_cycle_count_items
WHERE cycle_count_header_id = l_cycle_count_header_id
AND inventory_item_id = l_inventory_item_id;
SELECT approval_tolerance_positive,
approval_tolerance_negative,
cost_tolerance_positive,
cost_tolerance_negative
INTO l_class_app_tol_pos,
l_class_app_tol_neg,
l_class_cost_tol_pos,
l_class_cost_tol_neg
FROM mtl_cycle_count_classes
WHERE abc_class_id = l_abc_class_id
AND cycle_count_header_id = l_cycle_count_header_id;
SELECT NVL ( approval_tolerance_positive, -1 ),
NVL ( approval_tolerance_negative, -1 ),
NVL ( cost_tolerance_positive, -1 ),
NVL ( cost_tolerance_negative, -1 )
INTO l_head_app_tol_pos,
l_head_app_tol_neg,
l_head_cost_tol_pos,
l_head_cost_tol_neg
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = l_cycle_count_header_id
AND organization_id = l_organization_id;
IF ( g_cc_entry.entry_status_code = 3 ) AND g_updated_prior = FALSE THEN -- Modified for Bug 6371673
current_to_prior ( );
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT positive_measurement_error,
negative_measurement_error,
parent_lpn_id
INTO l_pos_meas_err,
l_neg_meas_err,
l_parent_lpn_id
FROM mtl_cycle_count_entries_v
WHERE cycle_count_entry_id = g_cc_entry.cycle_count_entry_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( container_enabled_flag, 2 ),
NVL ( container_adjustment_option, 2 ),
NVL ( container_discrepancy_option, 2 )
INTO l_container_enabled_flag,
l_container_adjustment_option,
l_container_discrepancy_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( approval_option_code, 1 )
INTO l_approval_option_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
final_preupdate_logic ( );
SELECT NVL ( approval_option_code, 1 )
INTO l_approval_option_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( container_enabled_flag, 2 ),
NVL ( container_adjustment_option, 2 ),
NVL ( container_discrepancy_option, 2 )
INTO l_container_enabled_flag,
l_container_adjustment_option,
l_container_discrepancy_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( approval_option_code, 1 ),
NVL ( automatic_recount_flag, 2 ),
NVL ( maximum_auto_recounts, 0 ),
NVL ( days_until_late, 0 ),
NVL ( serial_count_option, 1 )
INTO l_approval_option_code,
l_auto_recount_flag,
l_max_recounts,
l_days_until_late,
l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( container_enabled_flag, 2 ),
NVL ( container_adjustment_option, 2 ),
NVL ( container_discrepancy_option, 2 )
INTO l_container_enabled_flag,
l_container_adjustment_option,
l_container_discrepancy_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT serial_number_control_code
INTO l_serial_number_ctrl_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
PROCEDURE pre_insert
IS
l_number_of_counts NUMBER := NVL ( g_cc_entry.number_of_counts, 0 );
print_debug ( '***pre_insert***' );
SELECT serial_number_control_code
INTO l_serial_number_ctrl_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_detail_option, 1 ),
NVL ( serial_count_option, 1 )
INTO l_serial_detail_option,
l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT mtl_cycle_count_entries_s.NEXTVAL
INTO l_cc_entry_id
FROM DUAL;
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
print_debug('Called from pre_insert ');
final_preupdate_logic ( );
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET LOCATOR_ID = l_locator_id
WHERE CYCLE_COUNT_ID = l_cc_entry_id
AND TRANSACTION_SOURCE_ID = l_cc_header_id
AND LOCATOR_ID = -1;
UPDATE MTL_CC_SERIAL_NUMBERS
SET
UNIT_STATUS_CURRENT = DECODE((NVL(POS_ADJUSTMENT_QTY,0) -
NVL(NEG_ADJUSTMENT_QTY,0)), 1, 2, -1, 1, UNIT_STATUS_CURRENT),
POS_ADJUSTMENT_QTY = 0,
NEG_ADJUSTMENT_QTY = 0,
APPROVAL_CONDITION = NULL
WHERE CYCLE_COUNT_ENTRY_ID = l_cc_entry_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
END pre_insert;
PROCEDURE pre_update
IS
l_number_of_counts NUMBER := NVL ( g_cc_entry.number_of_counts, 0 );
print_debug ( '***pre_update***' );
SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 ),
lpn_context
INTO l_lpn_subinv,
l_lpn_locator_id,
l_lpn_context
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = g_cc_entry.parent_lpn_id ;
SELECT serial_number_control_code
INTO l_serial_number_ctrl_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_detail_option, 1 ),
NVL ( serial_count_option, 1 )
INTO l_serial_detail_option,
l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( number_of_counts, 0 )
INTO l_old_num_counts
FROM mtl_cycle_count_entries
WHERE cycle_count_entry_id = l_cc_entry_id;
FND_MESSAGE.SET_NAME ( 'INV', 'INV_DUPLICATE_COUNT_UPDATE' );
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
g_updated_prior := TRUE; -- Bug 6371673
print_debug('from pre_update : 1');
final_preupdate_logic ( );
g_updated_prior := FALSE; -- Bug 6371673
SELECT SUM ( DECODE ( UNIT_STATUS_CURRENT, 1, 1, 0 ) )
INTO l_total_serial_num_cnt
FROM MTL_CC_SERIAL_NUMBERS
WHERE CYCLE_COUNT_ENTRY_ID = l_cc_entry_id;
print_debug ( 'Multiple entry has been completed so call final_preupdate_logic'
);
print_debug('from pre_update : 2');
final_preupdate_logic ( );
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
UPDATE MTL_CC_SERIAL_NUMBERS
SET
UNIT_STATUS_CURRENT = DECODE((NVL(POS_ADJUSTMENT_QTY,0) -
NVL(NEG_ADJUSTMENT_QTY,0)), 1, 2, -1, 1, UNIT_STATUS_CURRENT),
POS_ADJUSTMENT_QTY = 0,
NEG_ADJUSTMENT_QTY = 0,
APPROVAL_CONDITION = NULL
WHERE CYCLE_COUNT_ENTRY_ID = l_cc_entry_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
END pre_update;
PROCEDURE final_preupdate_logic
IS
l_entry_status_code NUMBER := g_cc_entry.entry_status_code;
print_debug ( '***final_preupdate_logic***' );
SELECT primary_uom_code
INTO l_to_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT serial_number_control_code
INTO l_serial_number_ctrl_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_count_option, 1 ),
NVL ( inventory_adjustment_account, -1 )
INTO l_serial_count_option,
l_txn_acct_id
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_item_id AND organization_id = l_org_id;
delete_reservation ( );
SELECT negative_inv_receipt_code
INTO l_neg_inv_rcpt_code --Negative Balance 1:Allowed 2:Disallowed
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT serial_number_control_code,
lot_control_code,
revision_qty_control_code
INTO v_ser_code,
v_lot_code,
v_rev_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id AND organization_id = l_org_id;
SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 )
INTO l_lpn_subinv,
l_lpn_locator_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = l_lpn_id;
/* Bug 5725198-Added the check for LPN being counted to update quantity tree */
IF (( l_lpn_id IS NOT NULL ) AND (l_lpn_subinv <> '###' AND l_lpn_locator_id <> -99)) THEN
inv_quantity_tree_pub.update_quantities ( p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_tree_mode => 1,
p_is_revision_control => v_is_rev_controlled,
p_is_lot_control => v_is_lot_controlled,
p_is_serial_control => v_is_ser_controlled,
p_demand_source_type_id => NULL,
p_revision => l_rev,
p_lot_number => l_lot_num,
p_subinventory_code => l_lpn_subinv,
p_locator_id => l_lpn_locator_id,
p_onhand_source => 3,
p_containerized => 0,
p_primary_quantity => ABS ( l_adjustment_quantity
),
p_secondary_quantity => ABS ( l_sec_adjustment_quantity ), -- nsinghi bug#6052831
p_quantity_type => 5,
x_qoh => x_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => x_att,
x_atr => l_atr,
p_lpn_id => l_lpn_id,
-- nsinghi bug#6052831 START
x_sqoh => l_sqoh,
x_srqoh => l_srqoh,
x_sqr => l_sqr,
x_sqs => l_sqs,
x_satt => l_satt,
x_satr => l_satr
-- nsinghi bug#6052831 END
);
inv_quantity_tree_pub.update_quantities ( p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_tree_mode => 1,
p_is_revision_control => v_is_rev_controlled,
p_is_lot_control => v_is_lot_controlled,
p_is_serial_control => v_is_ser_controlled,
p_demand_source_type_id => NULL,
p_revision => l_rev,
p_lot_number => l_lot_num,
p_subinventory_code => l_sub,
p_locator_id => l_locator_id,
p_onhand_source => 3,
p_containerized => 0,
p_primary_quantity => ABS ( l_adjustment_quantity
),
p_secondary_quantity => ABS ( l_sec_adjustment_quantity ), -- nsinghi bug#6052831
p_quantity_type => 5,
x_qoh => x_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => x_att,
x_atr => l_atr,
p_lpn_id => NULL, --added for lpn reservation
-- nsinghi bug#6052831 START
x_sqoh => l_sqoh,
x_srqoh => l_srqoh,
x_sqr => l_sqr,
x_sqs => l_sqs,
x_satt => l_satt,
x_satr => l_satr
-- nsinghi bug#6052831 END
);
/* SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 )
INTO l_lpn_subinv,
l_lpn_locator_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = l_lpn_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_header_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_temp_id
FROM DUAL;
SELECT auto_serial_alpha_prefix
INTO l_serial_prefix
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id;
SELECT NVL ( REVISION, 'XXX' ),
NVL ( LOT_NUMBER, 'X' ),
CURRENT_STATUS,
CURRENT_SUBINVENTORY_CODE,
NVL ( CURRENT_LOCATOR_ID, 0 ),
NVL ( LPN_ID, -99 )
INTO l_msn_revision,
l_msn_lot_number,
l_current_status,
l_msn_subinv,
l_msn_locator_id,
l_msn_lpn_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND INVENTORY_ITEM_ID = g_cc_entry.inventory_item_id
AND CURRENT_ORGANIZATION_ID = g_cc_entry.organization_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_temp_id
FROM DUAL;
SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 )
INTO l_lpn_subinv,
l_lpn_locator_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = l_lpn_id;
SELECT COUNT ( * )
INTO l_temp_lpn_count
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_txn_header_id
AND inventory_item_id = -1
AND content_lpn_id = l_lpn_id
AND transaction_source_id = l_cc_header_id
AND cycle_count_id IS NULL;
print_debug ( 'The LPN sub xfer record has already been inserted into MMTT'
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_temp_id
FROM DUAL;
END final_preupdate_logic;
PROCEDURE delete_reservation
IS
l_mtl_reservation_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE
:= INV_CC_RESERVATIONS_PVT.Define_Reserv_Rec_Type;
print_debug ( '***delete_reservation***' );
/* Delete only cycle count reservation */
l_mtl_reservation_rec.demand_source_type_id := 9;
SELECT NVL (subinventory_code, '###' ),
NVL (locator_id, -99 )
INTO l_lpn_subinv,
l_lpn_locator_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = g_cc_entry.parent_lpn_id;
print_debug ( 'Calling Delete_All_Reservation with the following values for the reservation record:'
);
INV_CC_RESERVATIONS_PVT.Delete_All_Reservation ( p_api_version_number => 1.0,
p_init_msg_lst => l_init_msg_lst,
p_mtl_reservation_rec => l_mtl_reservation_rec,
x_error_code => l_error_code,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END delete_reservation;
SELECT COUNT ( * )
INTO l_count
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = l_cc_header_id
AND organization_id = l_org_id
AND inventory_item_id = l_item_id
AND subinventory = l_sub
AND entry_status_code IN ( 1, 2, 3 )
-- AND nvl(export_flag,2) = 2
AND ( l_locator_id IS NULL
OR locator_id = l_locator_id )
AND ( l_revision IS NULL
OR revision = l_revision )
AND ( l_lot IS NULL
OR lot_NUMBER = l_lot )
AND ( l_cc_serial_number IS NULL
OR serial_number = l_cc_serial_number
)
AND ( l_cost_group_id IS NULL
OR cost_group_id = l_cost_group_id)
AND NVL(parent_lpn_id,-1 ) = NVL(l_lpn_id, -1);
SELECT cycle_count_entry_id,
entry_status_code,
inventory_item_id
FROM MTL_CYCLE_COUNT_ENTRIES_V
WHERE cycle_count_header_id = l_cc_header_id
-- AND nvl(export_flag,2) = 2
AND serial_number_control_code IN ( 2, 5 );
SELECT COUNT ( * )
INTO l_temp_count
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_txn_header_id;
/*SELECT NVL(serial_count_option, 1)
INTO l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = l_cc_header_id
AND organization_id = l_org_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE inventory_item_id = l_inventory_item_id
AND current_organization_id = l_org_id
AND serial_number IN
(SELECT serial_number
FROM mtl_cc_serial_numbers
WHERE cycle_count_entry_id = l_cc_entry_id);
g_update_flag := 2;
g_insert_flag := 2;
l_last_updated_by NUMBER := g_cc_entry.last_updated_by;
l_last_update_login NUMBER := g_cc_entry.last_update_login;
SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 ),
lpn_context
INTO l_lpn_subinv,
l_lpn_locator_id,
l_lpn_context
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = l_lpn_id ;
SELECT primary_uom_code,
serial_number_control_code
INTO l_from_uom,
l_serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_count_option, 1 )
INTO l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
INTO l_primary_sys_qty
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND NVL ( containerized_flag, 2 ) = 2
AND subinventory_code = l_sub
AND NVL ( lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( locator_id, -2 ) = NVL ( l_loc, -2 )
AND NVL ( cost_group_id, -9 ) = NVL ( l_cost_group_id, -9 );
SELECT NVL ( SUM ( quantity ), 0 )
INTO l_loaded_sys_qty
FROM WMS_LOADED_QUANTITIES_V
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND NVL ( containerized_flag, 2 ) = 2
AND subinventory_code = l_sub
AND NVL ( lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( locator_id, -2 ) = NVL ( l_loc, -2 )
--Bug# 3071372
--AND NVL ( cost_group_id, -9 ) = NVL ( l_cost_group_id, -9 )
AND qty_type = 'LOADED'
AND lpn_id IS NULL
AND content_lpn_id IS NULL; -- bug 2640378
SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
INTO l_primary_sys_qty
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND NVL ( containerized_flag, 2 ) = 2
AND subinventory_code = l_sub
AND NVL ( lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( locator_id, -2 ) = NVL ( l_loc, -2 );
SELECT NVL ( SUM ( DECODE ( msn.current_status, 3, 1, 0 ) ), 0 )
INTO l_primary_sys_qty
FROM mtl_serial_numbers msn
WHERE msn.serial_number = NVL ( l_serial_number, serial_number )
AND msn.inventory_item_id = l_item_id
AND msn.current_organization_id = l_org_id
AND msn.current_subinventory_code = l_sub
AND NVL ( msn.lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( msn.revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( msn.current_locator_id, -2 ) = NVL ( l_loc, -2 )
AND msn.lpn_id IS NULL
AND is_serial_loaded ( l_org_id,
l_item_id,
NVL ( l_serial_number, serial_number ),
NULL
) = 2;
SELECT mtl_cycle_count_entries_s.NEXTVAL
INTO l_cycle_count_entry_id
FROM DUAL;
INSERT INTO MTL_CC_SERIAL_NUMBERS
( CYCLE_COUNT_ENTRY_ID,
SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
SELECT l_cycle_count_entry_id,
SERIAL_NUMBER,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_last_updated_by,
l_last_update_login
FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = l_item_id
AND msn.current_organization_id = l_org_id
AND msn.current_subinventory_code = l_sub
AND NVL ( msn.lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( msn.revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( msn.current_locator_id, -2 ) = NVL ( l_loc, -2 )
AND msn.current_status = 3
AND NVL ( msn.lpn_id, -99999 ) = NVL ( l_lpn_id, -99999 )
AND NOT EXISTS (
SELECT 'x'
FROM MTL_CC_SERIAL_NUMBERS
WHERE CYCLE_COUNT_ENTRY_ID = l_cycle_count_entry_id
AND SERIAL_NUMBER = msn.SERIAL_NUMBER );
l_last_updated_by NUMBER := g_cc_entry.last_updated_by;
l_last_update_login NUMBER := g_cc_entry.last_update_login;
SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 ),
lpn_context
INTO l_lpn_subinv,
l_lpn_locator_id,
l_lpn_context
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = l_lpn_id ;
SELECT primary_uom_code,
serial_number_control_code
INTO l_from_uom,
l_serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_count_option, 1 )
INTO l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
, NVL ( SUM ( secondary_transaction_quantity ), 0 ) -- nsinghi bug#6052831
INTO l_primary_sys_qty
, l_secondary_sys_qty -- nsinghi bug#6052831
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND NVL ( containerized_flag, 2 ) = 2
AND subinventory_code = l_sub
AND NVL ( lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( locator_id, -2 ) = NVL ( l_loc, -2 )
AND NVL ( cost_group_id, -9 ) = NVL ( l_cost_group_id, -9 );
SELECT NVL ( SUM ( quantity ), 0 )
, NVL ( SUM ( secondary_quantity ), 0 ) -- nsinghi bug#6052831
INTO l_loaded_sys_qty
, l_loaded_sec_sys_qty -- nsinghi bug#6052831
FROM WMS_LOADED_QUANTITIES_V
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND NVL ( containerized_flag, 2 ) = 2
AND subinventory_code = l_sub
AND NVL ( lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( locator_id, -2 ) = NVL ( l_loc, -2 )
--Bug# 3071372
--AND NVL ( cost_group_id, -9 ) = NVL ( l_cost_group_id, -9 )
AND qty_type = 'LOADED'
AND lpn_id IS NULL
AND content_lpn_id IS NULL; -- bug 2640378
SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
, NVL ( SUM ( secondary_transaction_quantity ), 0 ) -- nsinghi bug#6052831
INTO l_primary_sys_qty
, l_secondary_sys_qty -- nsinghi bug#6052831
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND NVL ( containerized_flag, 2 ) = 2
AND subinventory_code = l_sub
AND NVL ( lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( locator_id, -2 ) = NVL ( l_loc, -2 );
SELECT NVL ( SUM ( DECODE ( msn.current_status, 3, 1, 0 ) ), 0 )
INTO l_primary_sys_qty
FROM mtl_serial_numbers msn
WHERE msn.serial_number = NVL ( l_serial_number, serial_number )
AND msn.inventory_item_id = l_item_id
AND msn.current_organization_id = l_org_id
AND msn.current_subinventory_code = l_sub
AND NVL ( msn.lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( msn.revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( msn.current_locator_id, -2 ) = NVL ( l_loc, -2 )
AND msn.lpn_id IS NULL
AND is_serial_loaded ( l_org_id,
l_item_id,
NVL ( l_serial_number, serial_number ),
NULL
) = 2;
SELECT mtl_cycle_count_entries_s.NEXTVAL
INTO l_cycle_count_entry_id
FROM DUAL;
INSERT INTO MTL_CC_SERIAL_NUMBERS
( CYCLE_COUNT_ENTRY_ID,
SERIAL_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
SELECT l_cycle_count_entry_id,
SERIAL_NUMBER,
SYSDATE,
l_last_updated_by,
SYSDATE,
l_last_updated_by,
l_last_update_login
FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = l_item_id
AND msn.current_organization_id = l_org_id
AND msn.current_subinventory_code = l_sub
AND NVL ( msn.lot_number, 'XX' ) = NVL ( l_lot, 'XX' )
AND NVL ( msn.revision, 'XXX' ) = NVL ( l_rev, 'XXX' )
AND NVL ( msn.current_locator_id, -2 ) = NVL ( l_loc, -2 )
AND msn.current_status = 3
AND NVL ( msn.lpn_id, -99999 ) = NVL ( l_lpn_id, -99999 )
AND NOT EXISTS (
SELECT 'x'
FROM MTL_CC_SERIAL_NUMBERS
WHERE CYCLE_COUNT_ENTRY_ID = l_cycle_count_entry_id
AND SERIAL_NUMBER = msn.SERIAL_NUMBER );
SELECT primary_uom_code
INTO l_to_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( ccicv.item_cost, 0 )
INTO l_item_cost
FROM cst_cg_item_costs_view ccicv,
mtl_parameters mp
WHERE l_locator_id IS NULL
AND ccicv.organization_id = in_org_id
AND ccicv.inventory_item_id = in_item_id
AND ccicv.organization_id = mp.organization_id
/* Bug 5555367 - Modified the condition
AND ccicv.cost_group_id =
DECODE ( mp.primary_cost_method,
1, 1,
NVL ( l_cost_group_id, 1 )
)
*/
AND ccicv.cost_group_id =
DECODE ( mp.primary_cost_method,
1, 1,
NVL ( l_cost_group_id, mp.default_cost_group_id)
)
UNION ALL
SELECT NVL ( ccicv.item_cost, 0 )
FROM mtl_item_locations mil,
cst_cg_item_costs_view ccicv,
mtl_parameters mp
WHERE l_locator_id IS NOT NULL
AND mil.organization_id = in_org_id
AND mil.inventory_location_id = l_locator_id
AND mil.project_id IS NULL
AND ccicv.organization_id = mil.organization_id
AND ccicv.inventory_item_id = in_item_id
AND ccicv.organization_id = mp.organization_id
/* Bug 5555367 - Modified the condition
AND ccicv.cost_group_id =
DECODE ( mp.primary_cost_method,
1, 1,
NVL ( l_cost_group_id, 1 )
)
*/
AND ccicv.cost_group_id =
DECODE ( mp.primary_cost_method,
1, 1,
NVL ( l_cost_group_id, mp.default_cost_group_id)
)
UNION ALL
SELECT NVL ( ccicv.item_cost, 0 )
FROM mtl_item_locations mil,
mrp_project_parameters mrp,
cst_cg_item_costs_view ccicv,
mtl_parameters mp
WHERE l_locator_id IS NOT NULL
AND mil.organization_id = in_org_id
AND mil.inventory_location_id = l_locator_id
AND mil.project_id IS NOT NULL
AND mrp.organization_id = mil.organization_id
AND mrp.project_id = mil.project_id
AND ccicv.organization_id = mil.organization_id
AND ccicv.inventory_item_id = in_item_id
AND ccicv.organization_id = mp.organization_id
AND ccicv.cost_group_id =
DECODE ( mp.primary_cost_method,
1, 1,
NVL ( mrp.costing_group_id, 1 )
);
SELECT lot_control_code
INTO l_lot_control_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT COUNT ( * )
INTO l_serial_count
FROM mtl_serial_numbers_temp s,
wms_loaded_quantities_v wl
WHERE s.transaction_temp_id = wl.transaction_temp_id
AND p_serial_number BETWEEN s.fm_serial_number
AND s.to_serial_number;
SELECT COUNT ( * )
INTO l_serial_count
FROM mtl_serial_numbers_temp s,
wms_loaded_quantities_v wl,
mtl_transaction_lots_temp l
WHERE wl.transaction_temp_id = l.transaction_temp_id
AND s.transaction_temp_id = l.serial_transaction_temp_id
AND p_serial_number BETWEEN fm_serial_number
AND to_serial_number;
SELECT COUNT ( * )
INTO l_serial_count
FROM mtl_serial_numbers s,
wms_loaded_quantities_v wl
WHERE s.lpn_id = p_lpn_id
AND NVL ( wl.content_lpn_id, NVL ( wl.lpn_id, -1 ) ) = s.lpn_id
AND s.serial_number = p_serial_number
AND s.current_organization_id = p_organization_id
AND s.inventory_item_id = p_inventory_item_id;
l_last_updated_by NUMBER := g_cc_entry.last_updated_by;
l_last_update_login NUMBER := g_cc_entry.last_update_login;
SELECT NVL ( serial_count_option, 1 ),
NVL ( serial_detail_option, 1 )
INTO l_serial_count_option,
l_serial_detail_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT serial_number_control_code
INTO l_serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT MIN ( NVL ( number_of_counts, 0 ) )
INTO l_number_of_counts
FROM mtl_cc_serial_numbers
WHERE cycle_count_entry_id = l_cycle_count_entry_id
GROUP BY cycle_count_entry_id;
SELECT MIN ( number_of_counts )
INTO l_number_of_counts
FROM mtl_cc_serial_numbers
WHERE cycle_count_entry_id = l_cycle_count_entry_id
GROUP BY cycle_count_entry_id;
SELECT NVL ( serial_count_option, 1 ),
NVL ( serial_adjustment_option, 2 ),
NVL ( automatic_recount_flag, 2 ),
NVL ( maximum_auto_recounts, 0 ),
NVL ( days_until_late , 0 ),
--Bug 6978840
NVL ( approval_option_code , 3)
INTO l_serial_count_option,
l_serial_adjustment_option,
l_automatic_recount_flag,
l_maximum_auto_recounts,
l_days_until_late,
l_approval_option_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT COUNT ( * )
INTO l_count
FROM MTL_CC_SERIAL_NUMBERS
WHERE serial_number = l_serial_number
AND cycle_count_entry_id = l_cycle_count_entry_id;
final_preupdate_logic ( );
final_preupdate_logic ( );
final_preupdate_logic ( );
/* Deletes the serial info from mtl_cc_Serial_numbers in case of an Issue transaction */
PROCEDURE delete_Serial_entry(p_serial_number IN VARCHAR2, p_cc_header_id IN NUMBER, p_cycle_count_entry_id IN NUMBER) IS
BEGIN
DELETE FROM mtl_cc_Serial_numbers
WHERE serial_number = p_serial_number
AND cycle_count_entry_id IN
(SELECT cycle_count_entry_id
FROM mtl_cycle_count_entries
WHERE cycle_Count_header_id = p_cc_header_id
AND entry_status_code IN (1,3))
AND cycle_count_entry_id <> p_cycle_Count_entry_id;
print_debug('Exception while trying to delete serial number ' || g_cc_Serial_entry.serial_number);
END delete_serial_entry;
SELECT NVL ( serial_count_option, 1 ),
NVL ( serial_adjustment_option, 2 ),
NVL ( automatic_recount_flag, 2 ),
NVL ( maximum_auto_recounts, 0 ),
NVL ( days_until_late , 0 ),
--Bug 6978840
NVL ( approval_option_code , 3)
INTO l_serial_count_option,
l_serial_adjustment_option,
l_automatic_recount_flag,
l_maximum_auto_recounts,
l_days_until_late,
l_approval_option_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( SUM ( DECODE ( msn.current_status, 3, 1, 0 ) ), 0 )
INTO l_multiple_count
FROM mtl_serial_numbers msn
WHERE msn.serial_number = g_cc_serial_entry.serial_number
AND msn.inventory_item_id = g_cc_entry.inventory_item_id
AND msn.current_organization_id = g_cc_entry.organization_id
AND msn.current_subinventory_code = g_cc_entry.subinventory
AND NVL ( msn.lot_number, 'XX' ) =
NVL ( g_cc_entry.lot_number, 'XX' )
AND NVL ( msn.revision, 'XXX' ) =
NVL ( g_cc_entry.revision, 'XXX' )
AND NVL ( msn.current_locator_id, -2 ) =
NVL ( g_cc_entry.locator_id, -2 )
AND msn.lpn_id IS NULL --Bug# 3646068
AND is_serial_loaded ( g_cc_entry.organization_id,
g_cc_entry.inventory_item_id,
g_cc_serial_entry.serial_number,
NULL
) = 2;
SELECT NVL ( SUM ( DECODE ( msn.current_status, 3, 1, 0 ) ), 0 )
INTO l_multiple_count
FROM mtl_serial_numbers msn
WHERE msn.serial_number = g_cc_serial_entry.serial_number
AND msn.inventory_item_id = g_cc_entry.inventory_item_id
AND msn.current_organization_id = g_cc_entry.organization_id
AND NVL ( msn.lot_number, 'XX' ) =
NVL ( g_cc_entry.lot_number, 'XX' )
AND NVL ( msn.revision, 'XXX' ) =
NVL ( g_cc_entry.revision, 'XXX' )
AND msn.lpn_id = g_cc_entry.parent_lpn_id
AND is_serial_loaded ( g_cc_entry.organization_id,
g_cc_entry.inventory_item_id,
g_cc_serial_entry.serial_number,
g_cc_entry.parent_lpn_id
) = 2;
SELECT primary_uom_code
INTO l_primary_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
final_preupdate_logic ( );
final_preupdate_logic ( );
final_preupdate_logic ( );
final_preupdate_logic ( );
final_preupdate_logic ( );
SELECT NVL ( serial_count_option, 1 ),
NVL ( serial_detail_option, 1 ),
NVL ( serial_discrepancy_option, 2 )
INTO l_serial_count_option,
l_serial_detail,
l_serial_discrepancy
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT serial_number_control_code
INTO l_serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT SERIAL_NUMBER_TYPE
INTO l_serial_number_type
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_org_id;
SELECT CURRENT_SUBINVENTORY_CODE,
NVL ( CURRENT_LOCATOR_ID, 0 )
INTO l_msn_subinv,
l_msn_locator_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = u1
AND INVENTORY_ITEM_ID = g_cc_entry.inventory_item_id
AND CURRENT_ORGANIZATION_ID = g_cc_entry.organization_id;
SELECT 1,
current_status
INTO serial_count,
l_current_status
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND INVENTORY_ITEM_ID = l_item_id
AND CURRENT_ORGANIZATION_ID = l_org_id
AND CURRENT_STATUS IN ( 1, 3 );
SELECT primary_uom_code
INTO l_to_uom
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( inventory_adjustment_account, -1 )
INTO l_txn_acct_id
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_item_id AND organization_id = l_org_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_header_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_temp_id
FROM DUAL;
SELECT auto_serial_alpha_prefix
INTO l_serial_prefix
FROM mtl_system_items
WHERE inventory_item_id = l_item_id AND organization_id = l_org_id;
delete_reservation ( );
SELECT negative_inv_receipt_code
INTO l_neg_inv_rcpt_code --Negative Balance 1:Allowed 2:Disallowed
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT serial_number_control_code,
lot_control_code,
revision_qty_control_code
INTO v_ser_code,
v_lot_code,
v_rev_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id;
inv_quantity_tree_pub.update_quantities ( p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_tree_mode => 1,
p_is_revision_control => v_is_rev_controlled,
p_is_lot_control => v_is_lot_controlled,
p_is_serial_control => v_is_ser_controlled,
p_demand_source_type_id => NULL,
p_revision => l_rev,
p_lot_number => l_lot_num,
p_subinventory_code => l_sub,
p_locator_id => l_locator_id,
p_onhand_source => 3,
p_containerized => 0,
p_primary_quantity => ABS ( l_adjustment_quantity
),
p_quantity_type => 5,
x_qoh => x_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => x_att,
x_atr => l_atr,
p_lpn_id => NULL --added for lpn reservation
);
SELECT NVL ( REVISION, 'XXX' ),
NVL ( LOT_NUMBER, 'X' ),
CURRENT_STATUS,
CURRENT_SUBINVENTORY_CODE,
NVL ( CURRENT_LOCATOR_ID, 0 ),
NVL ( LPN_ID, -99 )
INTO l_msn_revision,
l_msn_lot_number,
l_current_status,
l_msn_subinv,
l_msn_locator_id,
l_msn_lpn_id
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = l_serial_number
AND INVENTORY_ITEM_ID = g_cc_entry.inventory_item_id
AND CURRENT_ORGANIZATION_ID = g_cc_entry.organization_id;
print_debug('Calling delete_Serial_entry 1');
delete_serial_entry(l_serial_number,l_cc_header_id,l_cycle_count_entry_id); --3595723 Delete the serial info from mtl_cc_Serial_numbers
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_temp_id
FROM DUAL;
print_debug('Calling delete_Serial_entry 2');
delete_serial_entry(l_serial_number,l_cc_header_id,l_cycle_count_entry_id); --3595723 Delete the serial info from mtl_cc_Serial_numbers
PROCEDURE update_serial_row
IS
l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
print_debug ( '***update_serial_row***' );
g_cc_serial_entry.last_update_date := SYSDATE;
g_cc_serial_entry.last_updated_by := g_user_id;
g_cc_serial_entry.last_update_login := g_login_id;
UPDATE MTL_CC_SERIAL_NUMBERS
SET last_update_date = g_cc_serial_entry.last_update_date,
last_updated_by = g_cc_serial_entry.last_updated_by,
last_update_login = g_cc_serial_entry.last_update_login,
number_of_counts = g_cc_serial_entry.number_of_counts,
unit_status_current = g_cc_serial_entry.unit_status_current,
unit_status_prior = g_cc_serial_entry.unit_status_prior,
unit_status_first = g_cc_serial_entry.unit_status_first,
approval_condition = g_cc_serial_entry.approval_condition,
pos_adjustment_qty = g_cc_serial_entry.pos_adjustment_qty,
neg_adjustment_qty = g_cc_serial_entry.neg_adjustment_qty
WHERE cycle_count_entry_id = g_cc_entry.cycle_count_entry_id
AND ( ( serial_number = g_cc_serial_entry.serial_number )
OR ( serial_number IS NULL
AND g_cc_serial_entry.serial_number IS NULL
)
);
END update_serial_row;
SELECT serial_number_control_code
INTO l_serial_number_ctrl_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_count_option, 1 )
INTO l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT serial_number_control_code
INTO l_serial_number_ctrl_code
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_count_option, 1 )
INTO l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_number, '@@@@@' ),
inventory_item_id
INTO l_current_serial,
l_current_item
FROM mtl_cycle_count_entries
WHERE cycle_count_entry_id = cycle_cnt_entry_id;
SELECT mec.full_name,
fus.employee_id
INTO l_employee_full_name,
l_employee_id
FROM mtl_employees_current_view mec,
fnd_user fus
WHERE fus.user_id = l_user_id
AND mec.employee_id = fus.employee_id
AND mec.organization_id = l_org_id;
SELECT fus.employee_id
INTO l_employee_id
FROM fnd_user fus
WHERE fus.user_id = l_user_id;
SELECT *
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = p_parent_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT *
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = l_current_lpn
AND NVL ( serial_summary_entry, 2 ) = 2;
SELECT *
FROM MTL_SERIAL_NUMBERS
WHERE lpn_id = l_current_lpn;
SELECT *
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = l_current_lpn AND serial_summary_entry = 1;
SELECT NVL ( SUM ( quantity ), 0 )
FROM WMS_LOADED_QUANTITIES_V WLQV
WHERE WLQV.inventory_item_id = p_inventory_item_id
AND WLQV.organization_id = p_organization_id
AND (lpn_id = p_lpn_id OR content_lpn_id = p_lpn_id )
AND qty_type = 'LOADED';
SELECT NVL ( serial_count_option, 1 )
INTO l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id;
SELECT COUNT ( * )
INTO l_temp_count
FROM mtl_cycle_count_items
WHERE inventory_item_id = v_lpn_id.inventory_item_id
AND cycle_count_header_id = p_cycle_count_header_id;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_lpn_id.inventory_item_id
AND organization_id = v_lpn_id.organization_id;
SELECT COUNT ( * )
INTO l_temp_count
FROM mtl_cycle_count_items
WHERE inventory_item_id = v_lpn_content.inventory_item_id
AND cycle_count_header_id = p_cycle_count_header_id;
SELECT COUNT ( * )
INTO l_temp_count
FROM mtl_cycle_count_items
WHERE inventory_item_id =
v_lpn_serial_content.inventory_item_id
AND cycle_count_header_id = p_cycle_count_header_id;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id =
v_lpn_serial_content.inventory_item_id
AND organization_id =
v_lpn_serial_content.current_organization_id;
SELECT
lpn_context
INTO l_lpn_context
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_parent_lpn_id ;
SELECT COUNT ( * )
INTO l_temp_count
FROM mtl_cycle_count_items
WHERE inventory_item_id =
v_lpn_multiple_serial.inventory_item_id
AND cycle_count_header_id = p_cycle_count_header_id;
SELECT default_cost_group_id
INTO l_default_cost_group_id
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory;
SELECT NVL ( default_cost_group_id, -999 )
INTO l_default_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT NVL ( cost_group_id, -999 )
INTO l_cost_group_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id
AND NVL ( revision, '@@@@@' ) = NVL ( p_revision, '@@@@@' )
AND NVL ( lot_number, '@@@@@' ) = NVL ( p_lot_number, '@@@@@' )
AND current_organization_id = p_organization_id
--AND current_subinventory_code = p_subinventory
--AND NVL(current_locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL ( lpn_id, -99999 ) = NVL ( p_parent_lpn_id, -99999 );
SELECT DISTINCT NVL ( cost_group_id, -999 ) --bug3687177
INTO l_cost_group_id
FROM wms_lpn_contents
WHERE parent_lpn_id = p_parent_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL ( revision, '@@@@@' ) = NVL ( p_revision, '@@@@@' )
AND NVL ( lot_number, '@@@@@' ) = NVL ( p_lot_number, '@@@@@' )
AND NVL ( serial_summary_entry, 2 ) = 2;
SELECT DISTINCT NVL ( cost_group_id, -999 )
INTO l_cost_group_id
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = p_inventory_item_id
AND NVL ( revision, '@@@@@' ) =
NVL ( p_revision, '@@@@@' )
AND NVL ( lot_number, '@@@@@' ) =
NVL ( p_lot_number, '@@@@@' )
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory
AND NVL ( locator_id, -99999 ) =
NVL ( p_locator_id, -99999 )
AND NVL ( containerized_flag, 2 ) = 2;
SELECT *
FROM mtl_cc_serial_numbers
WHERE cycle_count_entry_id = g_cc_entry.cycle_count_entry_id;
SELECT group_mark_id
INTO l_group_mark_id
FROM mtl_serial_numbers
WHERE serial_number = g_cc_serial_entry.serial_number
AND current_organization_id = g_cc_entry.organization_id
AND inventory_item_id = g_cc_entry.inventory_item_id;
SELECT primary_uom_code
INTO g_count_uom
FROM mtl_system_items
WHERE inventory_item_id = g_cc_entry.inventory_item_id
AND organization_id = g_cc_entry.organization_id;
SELECT NVL ( serial_adjustment_option, 2 ), NVL ( automatic_recount_flag, 2 ),
NVL ( maximum_auto_recounts, 0 ), NVL ( days_until_late , 0 ),
--Bug 6978840
NVL( approval_option_code , 3)
INTO l_serial_adjustment_option, l_automatic_recount_flag, l_maximum_auto_recounts, l_days_until_late, l_approval_option_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id;
SELECT group_mark_id
INTO l_group_mark_id
FROM mtl_serial_numbers
WHERE serial_number = g_cc_serial_entry.serial_number
AND current_organization_id = g_cc_entry.organization_id
AND inventory_item_id = g_cc_entry.inventory_item_id;
update_serial_row ( );
update_serial_row ( );
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number = g_cc_serial_entry.serial_number
AND current_organization_id = g_cc_entry.organization_id
AND inventory_item_id = g_cc_entry.inventory_item_id;
SELECT NVL ( approval_option_code, 1 )
INTO l_approval_option_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = g_cc_entry.cycle_count_header_id
AND organization_id = g_cc_entry.organization_id;
SELECT COUNT ( * )
INTO x_count
FROM mtl_cycle_count_entries
WHERE cycle_count_header_id = p_cycle_count_header_id
AND entry_status_code IN ( 1, 3 )
AND NVL ( export_flag, 2 ) = 2;
PROCEDURE insert_dynamic_lot (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_expiration_date IN OUT NOCOPY DATE,
p_transaction_temp_id IN NUMBER,
p_transaction_action_id IN NUMBER,
p_transfer_organization_id IN NUMBER,
p_status_id IN NUMBER,
p_update_status IN VARCHAR2,
x_object_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
print_debug ( '***Calling insert_dynamic_lot***' );
print_debug ( 'Calling insertlot' );
inv_lot_api_pub.insertlot ( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_lot_number => p_lot_number,
p_expiration_date => p_expiration_date,
p_transaction_temp_id => p_transaction_temp_id,
p_transaction_action_id => p_transaction_action_id,
p_transfer_organization_id => p_transfer_organization_id,
x_object_id => x_object_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
print_debug ( 'insertLot was not called successfully' );
print_debug ( 'insertLot was called successfully' );
AND ( p_update_status = 'TRUE' )
) THEN
IF ( l_debug = 1 ) THEN
print_debug ( 'Update the status of the new lot' );
inv_material_status_grp.update_status ( p_api_version_number => p_api_version,
p_init_msg_lst => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_update_method => inv_material_status_pub.g_update_method_receive,
p_status_id => p_status_id,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_sub_code => NULL,
p_locator_id => NULL,
p_lot_number => p_lot_number,
p_serial_number => NULL,
p_to_serial_number => NULL,
p_object_type => 'O'
);
print_debug ( 'update_status was not called successfully' );
END insert_dynamic_lot;
PROCEDURE update_serial_status (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_from_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_current_status IN NUMBER,
p_serial_status_id IN NUMBER,
p_update_serial_status IN VARCHAR2,
p_lot_number IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_from_ser_number NUMBER;
print_debug ( 'Enter update_serial_status: 10:'
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
SAVEPOINT count_update_serial_sp;
UPDATE mtl_serial_numbers
SET previous_status = current_status,
current_status = p_current_status,
lot_number = p_lot_number,
current_organization_id = p_organization_id
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id;
IF p_update_serial_status = 'TRUE' THEN
l_progress := '70';
inv_material_status_grp.update_status ( p_api_version_number => p_api_version,
p_init_msg_lst => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_update_method => inv_material_status_pub.g_update_method_receive,
p_status_id => p_serial_status_id,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_sub_code => NULL,
p_locator_id => NULL,
p_lot_number => p_lot_number,
p_serial_number => l_cur_serial_number,
p_to_serial_number => NULL,
p_object_type => 'S'
);
print_debug ( 'Exit update_serial_status 110:'
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
ROLLBACK TO count_update_serial_sp;
print_debug ( 'Exitting update_serial_status - execution error:'
|| l_progress
|| ' '
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
ROLLBACK TO count_update_serial_sp;
print_debug ( 'Exitting update_serial_status - unexpected error:'
|| l_progress
|| ' '
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
ROLLBACK TO count_update_serial_sp;
print_debug ( 'Exitting update_serial_status - other exceptions:'
|| l_progress
|| ' '
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
inv_mobile_helper_functions.sql_error ( 'INV_RCV_COMMON_APIS.update_serial_status',
l_progress,
SQLCODE
);
fnd_msg_pub.add_exc_msg ( g_pkg_name, 'update_serial_status' );
END update_serial_status;
PROCEDURE insert_range_serial (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_from_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_revision IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_current_status IN NUMBER,
p_serial_status_id IN NUMBER,
p_update_serial_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_object_id NUMBER;
print_debug ( 'Enter insert_range_serial: 10:'
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
SAVEPOINT count_insert_range_serial_sp;
inv_serial_number_pub.insert_range_serial ( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_from_serial_number => p_from_serial_number,
p_to_serial_number => p_to_serial_number,
p_initialization_date => SYSDATE,
p_completion_date => NULL,
p_ship_date => NULL,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_current_locator_id => NULL,
p_subinventory_code => NULL,
p_trx_src_id => NULL,
p_unit_vendor_id => NULL,
p_vendor_lot_number => NULL,
p_vendor_serial_number => NULL,
p_receipt_issue_type => NULL,
p_txn_src_id => NULL,
p_txn_src_name => NULL,
p_txn_src_type_id => NULL,
p_transaction_id => NULL,
p_current_status => p_current_status,
p_parent_item_id => NULL,
p_parent_serial_number => NULL,
p_cost_group_id => NULL,
p_transaction_action_id => NULL,
p_transaction_temp_id => NULL,
p_status_id => NULL,
p_inspection_status => NULL,
x_object_id => l_object_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF p_update_serial_status = 'TRUE' THEN
l_progress := '40';
inv_material_status_grp.update_status ( p_api_version_number => p_api_version,
p_init_msg_lst => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_update_method => inv_material_status_pub.g_update_method_receive,
p_status_id => p_serial_status_id,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_sub_code => NULL,
p_locator_id => NULL,
p_lot_number => p_lot_number,
p_serial_number => p_from_serial_number,
p_to_serial_number => p_to_serial_number,
p_object_type => 'S'
);
print_debug ( 'Exit insert_range_serial 90:'
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
ROLLBACK TO count_insert_range_serial_sp;
print_debug ( 'Exitting insert_range_serial - execution error:'
|| l_progress
|| ' '
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
ROLLBACK TO count_insert_range_serial_sp;
print_debug ( 'Exitting insert_range_serial - unexpected error:'
|| l_progress
|| ' '
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
ROLLBACK TO count_insert_range_serial_sp;
print_debug ( 'Exitting insert_range_serial - other exceptions:'
|| l_progress
|| ' '
|| TO_CHAR ( SYSDATE, 'YYYY-MM-DD HH:DD:SS' )
);
inv_mobile_helper_functions.sql_error ( 'INV_RCV_COMMON_APIS.insert_range_serial',
l_progress,
SQLCODE
);
fnd_msg_pub.add_exc_msg ( g_pkg_name, 'insert_range_serial' );
END insert_range_serial;
SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 ),
lpn_context
INTO l_lpn_subinv,
l_lpn_locator_id,
l_lpn_context
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_parent_lpn_id ;
SELECT primary_uom_code,
serial_number_control_code
INTO l_primary_uom,
l_serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
INTO x_system_quantity
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND NVL ( containerized_flag, 2 ) = 2
AND subinventory_code = p_subinventory
AND NVL ( locator_id, -99 ) = NVL ( p_locator_id, -99 )
AND ( NVL ( lot_number, 'XX' ) = NVL ( p_lot_number, 'XX' )
OR p_lot_number IS NULL
) -- Lot number might not have been entered yet
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
SELECT NVL ( SUM ( quantity ), 0 )
INTO l_loaded_sys_qty
FROM WMS_LOADED_QUANTITIES_V
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND NVL ( containerized_flag, 2 ) = 2
AND subinventory_code = p_subinventory
AND NVL ( locator_id, -99 ) = NVL ( p_locator_id, -99 )
AND ( NVL ( lot_number, 'XX' ) = NVL ( p_lot_number, 'XX' )
OR p_lot_number IS NULL
)
-- Lot number might not have been entered yet
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND qty_type = 'LOADED'
AND lpn_id IS NULL
AND content_lpn_id IS NULL; -- bug 2640378
SELECT nvl(sum(primary_quantity),0) --BUG3026540
INTO x_system_quantity
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = p_parent_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND ( NVL ( lot_number, 'XX' ) =
NVL ( p_lot_number, 'XX' )
OR p_lot_number IS NULL
)
-- Lot number might not have been entered yet
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND NVL ( serial_summary_entry, 2 ) = 2;
SELECT NVL ( SUM ( quantity ), 0 )
INTO l_loaded_sys_qty
FROM wms_loaded_quantities_v
WHERE NVL ( lpn_id, NVL ( content_lpn_id, -1 ) ) = p_parent_lpn_id
and inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
SELECT NVL ( SUM ( DECODE ( current_status, 3, 1, 0 ) ), 0 )
INTO x_system_quantity
FROM mtl_serial_numbers
WHERE lpn_id IS NULL
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND current_subinventory_code = p_subinventory
AND NVL ( current_locator_id, -99 ) = NVL ( p_locator_id, -99 )
AND ( NVL ( lot_number, 'XX' ) = NVL ( p_lot_number, 'XX' )
OR p_lot_number IS NULL
)
-- Lot number might not have been entered yet
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
select count(*)
into l_loaded_sys_qty
from mtl_serial_numbers_temp msnt, wms_loaded_quantities_v wl
where ((msnt.transaction_temp_id = wl.transaction_temp_id
and wl.lot_number is null) or
(msnt.transaction_temp_id = wl.serial_transaction_temp_id
and wl.lot_number is not null)
)
and wl.containerized_flag = 2
and wl.inventory_item_id = p_inventory_item_id
and wl.subinventory_code = p_subinventory
and nvl(wl.locator_id,-99) = nvl(p_locator_id,-99)
and (nvl(wl.lot_number,'@@@') = nvl(p_lot_number,'@@@')
or p_lot_number is null)
and nvl(wl.revision,'##') = nvl(p_revision,'##');
SELECT COUNT ( * )
INTO x_system_quantity
FROM mtl_serial_numbers
WHERE lpn_id = p_parent_lpn_id
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND ( NVL ( lot_number, 'XX' ) = NVL ( p_lot_number, 'XX' )
OR p_lot_number IS NULL
)
-- Lot number might not have been entered yet
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
select distinct wl.quantity
into l_loaded_sys_qty
from mtl_serial_numbers msn, wms_loaded_quantities_v wl
where msn.lpn_id = nvl(wl.content_lpn_id,nvl(wl.lpn_id,-1))
and wl.containerized_flag = 1
and msn.inventory_item_id = wl.inventory_item_id
and msn.current_organization_id = wl.ORGANIZATION_ID
and wl.inventory_item_id = p_inventory_item_id
and wl.organization_id = p_organization_id
and msn.lpn_id = p_parent_lpn_id
and (nvl(msn.lot_number,'@@@') = nvl(wl.lot_number,'@@@') or
p_lot_number is null)
AND NVL ( wl.revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
SELECT wdt.transaction_temp_id
FROM wms_dispatched_tasks wdt
WHERE wdt.person_id = l_employee_id
AND wdt.task_type = 3
AND NOT EXISTS (
SELECT 'ACTIVE_TASK'
FROM wms_dispatchable_tasks_v
WHERE wms_task_type_id = 3
AND task_id = wdt.transaction_temp_id );
SELECT DISTINCT NVL ( person_id, -999 )
INTO l_employee_id
FROM wms_dispatched_tasks_history
WHERE transaction_id = p_transaction_temp_id AND task_type = 3;
print_debug ( 'Inserting record into tasks history table' );
INSERT INTO WMS_DISPATCHED_TASKS_HISTORY
( task_id,
transaction_id,
organization_id,
user_task_type,
person_id,
effective_start_date,
effective_end_date,
equipment_id,
equipment_instance,
person_resource_id,
machine_resource_id,
status,
dispatched_time,
loaded_time,
drop_off_time,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
task_type,
priority,
task_group_id
)
SELECT task_id,
transaction_temp_id,
organization_id,
user_task_type,
person_id,
effective_start_date,
effective_end_date,
equipment_id,
equipment_instance,
person_resource_id,
machine_resource_id,
6,
dispatched_time,
loaded_time,
drop_off_time,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
task_type,
priority,
task_group_id
FROM WMS_DISPATCHED_TASKS
WHERE TRANSACTION_TEMP_ID = l_task_temp_id AND TASK_TYPE = 3;
DELETE FROM WMS_DISPATCHED_TASKS
WHERE TRANSACTION_TEMP_ID = l_task_temp_id AND TASK_TYPE = 3;