The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_key
( l_hash_value IN NUMBER
, l_insert_key_rec IN keyrectyp
, x_pick_slip_number OUT NOCOPY NUMBER
, x_error_message OUT NOCOPY VARCHAR2
) IS
BEGIN
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM DUAL;
g_pskey_table(l_hash_value) := l_insert_key_rec;
x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.INSERT_KEY';
inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.INSERT_KEY',3);
END insert_key;
, x_insert_key_rec OUT NOCOPY keyrectyp
, x_error_message OUT NOCOPY VARCHAR2
, p_dest_subinventory IN VARCHAR2 DEFAULT NULL
, p_dest_locator_id IN NUMBER DEFAULT NULL
, p_project_id IN NUMBER DEFAULT NULL
, p_task_id IN NUMBER DEFAULT NULL
) IS
l_hash_string VARCHAR2(2000) := NULL;
x_insert_key_rec.grouping_rule_id := g_rule_table(p_rule_index).grouping_rule_id;
x_insert_key_rec.header_id := p_header_id;
x_insert_key_rec.customer_id := p_customer_id;
x_insert_key_rec.ship_method_code := p_ship_method_code;
x_insert_key_rec.ship_to_loc_id := p_ship_to_loc_id;
x_insert_key_rec.shipment_priority := p_shipment_priority;
x_insert_key_rec.trip_stop_id := p_trip_stop_id;
x_insert_key_rec.delivery_id := p_delivery_id;
x_insert_key_rec.subinventory := p_subinventory;
x_insert_key_rec.locator_id := p_locator_id;
x_insert_key_rec.inventory_item_id := p_inventory_item_id;
x_insert_key_rec.lot_number := p_lot_number;
x_insert_key_rec.revision := p_revision;
x_insert_key_rec.dest_subinventory := p_dest_subinventory;
x_insert_key_rec.dest_locator_id := p_dest_locator_id;
x_insert_key_rec.project_id := p_project_id;
x_insert_key_rec.task_id := p_task_id;
x_insert_key_rec.organization_id := p_org_id;
SELECT NVL(order_number_flag, 'N')
, NVL(customer_flag, 'N')
, NVL(ship_to_flag, 'N')
, NVL(carrier_flag, 'N')
, NVL(shipment_priority_flag, 'N')
, NVL(trip_stop_flag, 'N')
, NVL(delivery_flag, 'N')
, NVL(subinventory_flag, 'N')
, NVL(locator_flag, 'N')
, NVL(dest_sub_flag, 'N')
, NVL(dest_loc_flag, 'N')
, NVL(project_flag, 'N')
, NVL(task_flag, 'N')
, NVL(item_flag, 'N')
, NVL(revision_flag, 'N')
, NVL(lot_flag, 'N')
, NVL(pick_method, '-99')
FROM wsh_pick_grouping_rules
WHERE pick_grouping_rule_id = v_pgr_id;
SELECT NVL(pick_slip_lines, -1)
FROM wsh_shipping_parameters
WHERE organization_id = v_org_id;
l_insert_key_rec keyrectyp;
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM dual;
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM DUAL;
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM DUAL;
, x_insert_key_rec => l_insert_key_rec
, x_error_message => x_error_message
, p_dest_subinventory => p_dest_subinventory
, p_dest_locator_id => p_dest_locator_id
, p_project_id => p_project_id
, p_task_id => p_task_id
);
insert_key(
l_hash_value => l_hash_value
, l_insert_key_rec => l_insert_key_rec
, x_pick_slip_number => x_pick_slip_number
, x_error_message => x_error_message
);
, x_insert_key_rec => l_insert_key_rec
, x_error_message => x_error_message
, p_dest_subinventory => p_dest_subinventory
, p_dest_locator_id => p_dest_locator_id
, p_project_id => p_project_id
, p_task_id => p_task_id
);
g_pskey_table.DELETE(l_hash_value);
insert_key(
l_hash_value => l_hash_value
, l_insert_key_rec => l_insert_key_rec
, x_pick_slip_number => x_pick_slip_number
, x_error_message => x_error_message
);
PROCEDURE insert_key
( l_hash_value IN NUMBER
, l_insert_key_rec IN wipkeyrectyp
, x_pick_slip_number OUT NOCOPY NUMBER
, x_error_message OUT NOCOPY VARCHAR2
) IS
BEGIN
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM DUAL;
g_wip_pskey_table(l_hash_value) := l_insert_key_rec;
x_error_message := 'Error occurred in INV_PR_PICK_NUMBER.INSERT_KEY';
inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.INSERT_KEY',3);
END insert_key;
, x_insert_key_rec OUT NOCOPY wipkeyrectyp
, x_error_message OUT NOCOPY VARCHAR2
) IS
l_hash_string VARCHAR2(2000) := NULL;
x_insert_key_rec.grouping_rule_id := g_rule_table(p_rule_index).grouping_rule_id;
x_insert_key_rec.wip_entity_id := p_wip_entity_id;
x_insert_key_rec.rep_schedule_id := p_rep_schedule_id;
x_insert_key_rec.operation_seq_num := p_operation_seq_num;
x_insert_key_rec.dept_id := p_dept_id;
x_insert_key_rec.push_or_pull := p_push_or_pull;
x_insert_key_rec.supply_subinventory := p_supply_subinventory;
x_insert_key_rec.supply_locator_id := p_supply_locator_id;
x_insert_key_rec.project_id := p_project_id;
x_insert_key_rec.task_id := p_task_id;
x_insert_key_rec.src_subinventory := p_src_subinventory;
x_insert_key_rec.src_locator_id := p_src_locator_id;
x_insert_key_rec.inventory_item_id := p_inventory_item_id;
x_insert_key_rec.lot_number := p_lot_number;
x_insert_key_rec.revision := p_revision;
x_insert_key_rec.organization_id := p_org_id;
SELECT NVL(job_schedule_flag, 'N')
, NVL(operation_flag, 'N')
, NVL(department_flag, 'N')
, NVL(push_vs_pull_flag, 'N')
, NVL(dest_sub_flag, 'N')
, NVL(dest_loc_flag, 'N')
, NVL(project_flag, 'N')
, NVL(task_flag, 'N')
, NVL(subinventory_flag, 'N')
, NVL(locator_flag, 'N')
, NVL(item_flag, 'N')
, NVL(revision_flag, 'N')
, NVL(lot_flag, 'N')
, NVL(pick_method, '-99')
FROM wsh_pick_grouping_rules
WHERE pick_grouping_rule_id = v_pgr_id;
l_insert_key_rec wipkeyrectyp;
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM DUAL;
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM DUAL;
, x_insert_key_rec => l_insert_key_rec
, x_error_message => x_error_message
);
insert_key(
l_hash_value => l_hash_value
, l_insert_key_rec => l_insert_key_rec
, x_pick_slip_number => x_pick_slip_number
, x_error_message => x_error_message
);
, x_insert_key_rec => l_insert_key_rec
, x_error_message => x_error_message
);
insert_key(
l_hash_value => l_hash_value
, l_insert_key_rec => l_insert_key_rec
, x_pick_slip_number => x_pick_slip_number
, x_error_message => x_error_message
);
PROCEDURE delete_wip_ps_tbl IS
BEGIN
g_wip_pskey_table.DELETE;
inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.DELETE_WIP_PS_TBL', 3);
END delete_wip_ps_tbl;
INSERT INTO mtl_pick_slip_numbers
( id
, pick_slip_batch_id
, pick_slip_count
, pick_slip_identifier
, pick_slip_number
, status
) VALUES ( mtl_pick_slip_numbers_s.nextval
, l_batch_id
, l_pick_slip_count
, p_hash_string
, wsh_pick_slip_numbers_s.nextval
, l_pick_slip_status
)
RETURNING pick_slip_number
INTO l_pick_slip_number;
inv_log_util.trace('Inserted pick slip # ' || l_pick_slip_number,
'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
inv_log_util.trace('INSERT failed, row already exists',
'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
SELECT pick_slip_number
, pick_slip_count
INTO l_pick_slip_number
, l_pick_slip_count
FROM mtl_pick_slip_numbers
WHERE pick_slip_identifier = p_hash_string
AND status = 1
FOR UPDATE WAIT 5;
inv_log_util.trace('No data found, so retrying INSERT',
'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
SELECT wsh_pick_slip_numbers_s.nextval
INTO l_pick_slip_number
FROM dual;
inv_log_util.trace('Failed to INSERT or LOCK pick slip record. Returning next value: '
|| l_pick_slip_number,
'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
UPDATE mtl_pick_slip_numbers
SET pick_slip_count = pick_slip_count + 1
, status = l_pick_slip_status
WHERE pick_slip_identifier = p_hash_string
AND status = 1
RETURNING pick_slip_count INTO l_pick_slip_count;
inv_log_util.trace('Updated count to ' || l_pick_slip_count ||
', status to ' || l_pick_slip_status,
'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
SELECT NVL(order_number_flag, 'N')
, NVL(customer_flag, 'N')
, NVL(ship_to_flag, 'N')
, NVL(carrier_flag, 'N')
, NVL(shipment_priority_flag, 'N')
, NVL(trip_stop_flag, 'N')
, NVL(delivery_flag, 'N')
, NVL(subinventory_flag, 'N')
, NVL(locator_flag, 'N')
, NVL(dest_sub_flag, 'N')
, NVL(dest_loc_flag, 'N')
, NVL(project_flag, 'N')
, NVL(task_flag, 'N')
, NVL(item_flag, 'N')
, NVL(revision_flag, 'N')
, NVL(lot_flag, 'N')
, NVL(pick_method, '-99')
FROM wsh_pick_grouping_rules
WHERE pick_grouping_rule_id = v_pgr_id;
SELECT NVL(pick_slip_lines, -1)
FROM wsh_shipping_parameters
WHERE organization_id = v_org_id;
l_insert_key_rec keyrectyp;
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM dual;
SELECT wsh_pick_slip_numbers_s.NEXTVAL
INTO x_pick_slip_number
FROM DUAL;
--<< check if deletion from the table is required, if so we can delete instead of update >>
END IF;