[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_mmtt_for_wip(
x_return_status OUT NOCOPY VARCHAR2
, p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
, p_grouping_rule_id IN NUMBER
);
SELECT entity_type
FROM wip_entities
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_org_id;
g_mo_line_stat_tbl.DELETE;
SELECT DECODE(NVL(mo_pick_confirm_required, 2), 1, fnd_api.g_false, 2, fnd_api.g_true, fnd_api.g_true)
INTO l_auto_pick_confirm
FROM mtl_parameters
WHERE organization_id = l_organization_id;
print_debug('Delete Line '||l_line_tbl(i).line_id, 'RELEASE_PICK_BATCH');
l_line_tbl.DELETE(i);
l_trolin_val_tbl.DELETE(i);
l_line_tbl.DELETE;
g_mo_line_stat_tbl.DELETE;
inv_pr_pick_slip_number.delete_wip_ps_tbl;
SELECT header_id, move_order_type, request_number
INTO l_mo_header_id, l_mo_type, l_mo_number
FROM mtl_txn_request_headers
WHERE header_id = p_mo_line_tbl(l_line_index).header_id;
SELECT revision_qty_control_code, lot_control_code
INTO l_revision_control_code, l_lot_control_code
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = l_mo_line.inventory_item_id;
l_qtree_backup_tbl.DELETE;
l_qtree_backup_tbl.DELETE;
SELECT quantity, NVL(quantity_detailed,0), line_status -- added for bug 9895550
INTO l_quantity, l_detail_quantity, l_line_status -- added for bug 9895550
FROM mtl_txn_request_lines
WHERE line_id = l_mo_line.line_id;
SELECT NVL(SUM(transaction_quantity), 0), NVL(SUM(primary_quantity), 0)
INTO l_transaction_quantity, l_primary_quantity
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mo_line.line_id;
l_qtree_backup_tbl.DELETE(l_mo_line.inventory_item_id);
UPDATE mtl_txn_request_lines
SET quantity = 0, quantity_detailed = 0, line_status = 5 ,status_date =sysdate --BUG 6932648
WHERE line_id = l_mo_line.line_id;
l_qtree_backup_tbl.DELETE;
UPDATE mtl_txn_request_lines
SET quantity = l_transaction_quantity,
primary_quantity = l_primary_quantity -- added for bug 9895550
WHERE line_id = l_mo_line.line_id;
UPDATE mtl_txn_request_lines
SET line_status = 5 ,status_date =sysdate --BUG 6932648
WHERE line_id = l_mo_line.line_id;
DELETE FROM mtl_txn_request_lines mtrl
WHERE line_status = 5
AND line_id = l_mol_id_tbl(ii);
fnd_msg_pub.delete_msg();
SELECT request_number
INTO l_request_number
FROM mtl_txn_request_lines_v
WHERE line_id = p_mo_line_rec.line_id;
SELECT NVL(SUM(primary_quantity), 0), COUNT(*)
INTO l_quantity_detailed, l_num_detail_recs
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_mo_line_rec.line_id;
inv_trolin_util.update_row(p_mo_line_rec);
SELECT primary_uom_code INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = p_mo_line_rec.organization_id
AND inventory_item_id = p_mo_line_rec.inventory_item_id;
inv_trolin_util.update_row(p_mo_line_rec);
update_mmtt_for_wip(x_return_status, p_mo_line_rec, p_grouping_rule_id);
PROCEDURE update_mol_for_wip
( x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_move_order_line_id IN NUMBER
, p_op_seq_num IN NUMBER
) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
print_debug('p_move_order_line_id => '|| p_move_order_line_id, 'update_mol_for_wip');
print_debug('p_op_seq_num => '|| p_op_seq_num, 'update_mol_for_wip');
UPDATE mtl_txn_request_lines mol
SET mol.txn_source_line_id = p_op_seq_num
WHERE mol.line_id = p_move_order_line_id
AND EXISTS( SELECT ''
FROM mtl_txn_request_headers moh
WHERE moh.header_id = mol.header_id
AND move_order_type = 5);
print_debug('No move order lines being updated', 'update_mol_for_wip');
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.trx_source_line_id = p_op_seq_num
WHERE mmtt.move_order_line_id = p_move_order_line_id
AND EXISTS( SELECT ''
FROM mtl_txn_request_headers moh, mtl_txn_request_lines mol
WHERE mol.line_id = mmtt.move_order_line_id
AND mol.header_id = moh.header_id
AND move_order_type = 5);
END update_mol_for_wip;
PROCEDURE update_mmtt_for_wip(
x_return_status OUT NOCOPY VARCHAR2
, p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
, p_grouping_rule_id IN NUMBER
) IS
l_wip_entity_type NUMBER;
SELECT transaction_temp_id, revision, subinventory_code, locator_id, transfer_subinventory, transfer_to_location, pick_slip_number
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_mo_line_rec.line_id;
print_debug('Error Occurred while getting WIP Attributes','UPDATE_MMTT_FOR_WIP');
print_debug('Error occurred in getting the Pick Slip Number: '|| l_msg_data, 'UPDATE_WITH_PICK_SLIP');
SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL INTO l_pick_slip_number FROM DUAL;
UPDATE mtl_material_transactions_temp
SET transaction_source_id = p_mo_line_rec.txn_source_id
, trx_source_line_id = p_mo_line_rec.txn_source_line_id
, demand_source_header_id = p_mo_line_rec.txn_source_id
, demand_source_line = p_mo_line_rec.txn_source_line_id
, transaction_source_type_id = inv_globals.g_sourcetype_wip
, transaction_type_id = p_mo_line_rec.transaction_type_id
, transaction_action_id = inv_globals.g_action_issue
, wip_entity_type = l_wip_entity_type
, repetitive_line_id = l_repetitive_line_id
, operation_seq_num = p_mo_line_rec.txn_source_line_id
, department_id = l_department_id
, department_code = l_department_code
, lock_flag = 'N'
, primary_switch = l_index
, wip_supply_type = 1
, negative_req_flag = SIGN(transaction_quantity)
, required_flag = '1'
, pick_slip_number = l_pick_slip_number
WHERE transaction_temp_id = c_mmtt_rec.transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_source_id = p_mo_line_rec.txn_source_id
, trx_source_line_id = p_mo_line_rec.txn_source_line_id
, demand_source_header_id = p_mo_line_rec.txn_source_id
, demand_source_line = p_mo_line_rec.txn_source_line_id
, transaction_source_type_id = inv_globals.g_sourcetype_inventory
, transaction_type_id = p_mo_line_rec.transaction_type_id
, transaction_action_id = inv_globals.g_action_subxfr
, wip_entity_type = l_wip_entity_type
, wip_supply_type = NULL -- Bug#2057540
, pick_slip_number = l_pick_slip_number
WHERE transaction_temp_id = c_mmtt_rec.transaction_temp_id;
print_debug('Exception Occurred: Code = ' || SQLCODE || ' : Error '|| SQLERRM, 'UPDATE_MMTT_FOR_WIP');
END update_mmtt_for_wip;
SELECT entity_type
FROM wip_entities
WHERE wip_entity_id = p_wip_entity_id;
SELECT line_id
FROM wip_repetitive_schedules
WHERE repetitive_schedule_id = p_rep_schedule_id
AND organization_id = p_organization_id
AND wip_entity_id = p_wip_entity_id;
SELECT decode(wip_supply_type,1,'PUSH',2,'PULL',3,'PULL')
FROM wip_requirement_operations
WHERE p_rep_schedule_id IS null
AND wip_entity_id = p_wip_entity_id
AND inventory_item_id = p_inventory_item_id
AND operation_seq_num = p_operation_seq_num
AND organization_id = p_organization_id
UNION ALL
SELECT decode(wip_supply_type,1,'PUSH',2,'PULL',3,'PULL')
FROM wip_requirement_operations
WHERE p_rep_schedule_id IS NOT NULL
AND wip_entity_id = p_wip_entity_id
AND inventory_item_id = p_inventory_item_id
AND operation_seq_num = p_operation_seq_num
AND organization_id = p_organization_id
AND repetitive_schedule_id = p_rep_schedule_id;
SELECT wo.department_id, bd.department_code
FROM wip_operations wo, bom_departments bd
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wo.operation_seq_num = p_operation_seq_num
AND bd.department_id = wo.department_id;
SELECT wo.department_id, bd.department_code
FROM wip_operations wo, bom_departments bd
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.organization_id = p_organization_id
AND wo.operation_seq_num = p_operation_seq_num
AND wo.repetitive_schedule_id = p_rep_schedule_id
AND bd.department_id = wo.department_id;
SELECT bos.department_id, bd.department_code
FROM bom_departments bd
, bom_operation_sequences bos
, bom_operational_routings bor
, wip_flow_schedules wfs
WHERE wfs.wip_entity_id = p_wip_entity_id
AND wfs.organization_id = p_organization_id
AND bor.assembly_item_id = wfs.primary_item_id
AND bor.organization_id = wfs.organization_id
AND (bor.alternate_routing_designator = wfs.alternate_routing_designator
OR (wfs.alternate_routing_designator IS NULL
AND bor.alternate_routing_designator IS NULL))
AND bos.routing_sequence_id = bor.routing_sequence_id
AND bos.operation_type = 1
AND bos.effectivity_date >= SYSDATE
AND bd.department_id = bos.department_id;
print_debug('Couldnt determine Entity Type for EntityID = '|| p_wip_entity_id, 'UPDATE_MMTT_FOR_WIP');
print_debug('Repetitive Schedule ID cannot be null for Entity Type 2', 'UPDATE_MMTT_FOR_WIP');
print_debug('Unable to determine RepLineID for RepSchID '||p_rep_schedule_id, 'UPDATE_MMTT_FOR_WIP');
SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL INTO x_pick_slip_number FROM DUAL;