[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select cost_group
into l_cost_group
from cst_cost_groups
where cost_group_id = l_cost_group_id_tbl(i);
SELECT inventory_item_id, organization_id,transfer_organization,cost_group_id, nvl(transfer_cost,0),
abs(primary_quantity), transaction_action_id
INTO l_item_id,l_org_id,l_xfer_org_id, l_cg_id,l_transfer_cost,l_pri_qty, l_trx_action_id
FROM Mtl_Material_Transactions_Temp
WHERE transaction_temp_id = p_mmtt_temp_id;
SELECT NVL(primary_cost_method,1) INTO l_primary_cost_method
FROM MTL_PARAMETERS
WHERE organization_id = l_org_id ;
SELECT NVL(matl_interorg_transfer_code,1) , interorg_trnsfr_charge_percent
INTO l_interorg_xfer_code , l_interorg_charge_prct
FROM mtl_interorg_parameters
WHERE from_organization_id = l_org_id
AND to_organization_id = l_xfer_org_id ;
SELECT NVL(ccicv.item_cost, 0) INTO l_item_cost
FROM cst_cg_item_costs_view ccicv
WHERE ccicv.inventory_item_id= l_item_id
AND ccicv.organization_id= l_org_id
AND ccicv.cost_group_id = l_cg_id ;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET TRANSFER_COST = l_transfer_cost
WHERE TRANSACTION_TEMP_ID = p_mmtt_temp_id;
print_debug('Calculate_Transfer_Cost : Updated MMTT with transfer cost ..Exiting API.');
select mmtt.*
from mtl_material_transactions_temp mmtt
WHERE
transaction_header_id = p_transaction_header_id
AND PROCESS_FLAG = 'Y'
AND NVL(TRANSACTION_STATUS,1) <> 2 ; /* 2STEP */
UPDATE mtl_material_transactions_temp
SET transaction_temp_id = mtl_material_transactions_s.NEXTVAL
WHERE transaction_header_id = p_transaction_header_id
AND transaction_temp_id IS NULL;
SELECT default_cost_group_id
INTO l_org_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_mmtt_rec.organization_id;
print_debug('After selecting the default cost group');
SELECT default_cost_group_id
INTO l_tfr_org_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_mmtt_rec.transfer_organization;
print_debug('After selecting the default transfer cost group');
print_debug('Return Status before update' ||x_return_status );
UPDATE mtl_material_transactions_temp
SET cost_group_id = l_org_cost_group_id,
transfer_cost_group_id = l_tfr_org_cost_group_id
WHERE transaction_temp_id = p_mmtt_rec.transaction_temp_id;
print_debug('Return Status after update' ||x_return_status );
SELECT fob_point
INTO l_fob_point
FROM mtl_interorg_parameters
WHERE from_organization_id =
Decode(p_mmtt_rec.transaction_action_id,
inv_globals.g_action_intransitreceipt,
p_mmtt_rec.transfer_organization,
p_mmtt_rec.organization_id)
AND to_organization_id =
Decode(p_mmtt_rec.transaction_action_id,
inv_globals.g_action_intransitreceipt,
p_mmtt_rec.organization_id,
p_mmtt_rec.transfer_organization);
SELECT project_id INTO l_to_project_id
FROM mtl_item_locations
WHERE inventory_location_id = p_mmtt_rec.transfer_to_location
AND organization_id = p_mmtt_rec.transfer_organization;
UPDATE mtl_material_transactions_temp
SET transfer_cost_group_id = NULL
WHERE
transaction_temp_id = p_mmtt_rec.transaction_temp_id;
SELECT project_id INTO l_from_project_id
FROM mtl_item_locations
WHERE inventory_location_id = p_mmtt_rec.locator_id
AND organization_id = p_mmtt_rec.organization_id;
SELECT project_id INTO l_to_project_id
FROM mtl_item_locations
WHERE inventory_location_id = p_mmtt_rec.transfer_to_location
AND organization_id = l_transfer_organization_id;
SELECT from_cost_group_id,
to_cost_group_id,
from_subinventory_code,
to_subinventory_code,
from_locator_id,
to_locator_id
INTO l_cost_Group_id,
l_transfer_cost_group_id,
l_subinventory_code,
l_transfer_subinventory,
l_from_locator_id,
l_to_locator_id
FROM mtl_txn_request_lines
WHERE line_id = l_line_id;
SELECT default_cost_group_id,
primary_cost_method
INTO l_tfr_org_cost_group_id,
l_tfr_primary_cost_method
FROM mtl_parameters
WHERE organization_id = l_transfer_organization_id;
SELECT project_id INTO l_from_project_id
FROM mtl_item_locations
WHERE inventory_location_id = l_from_locator_id AND
organization_id = l_organization_id;
SELECT project_id INTO l_to_project_id
FROM mtl_item_locations
WHERE inventory_location_id = l_to_locator_id AND
organization_id = l_transfer_organization_id;
SELECT default_cost_group_id,
primary_cost_method
INTO l_org_cost_group_id,
l_primary_cost_method
FROM mtl_parameters
WHERE organization_id = l_organization_id;
SELECT cost_group_id
INTO l_transfer_cost_group_id
FROM rcv_shipment_lines rsl,
rcv_transactions rt
WHERE rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_id = p_mmtt_rec.source_line_id;
SELECT default_cost_group_id
INTO l_cost_group_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_subinventory_code
AND organization_id = l_organization_id
AND default_cost_group_id IS NOT NULL;
select default_cost_group_id
into l_transfer_cost_group_id
from mtl_secondary_inventories
where secondary_inventory_name = l_transfer_subinventory
and organization_id = l_transfer_organization_id
and default_cost_group_id is not null;
print_debug('update the mmtt with cost group');
update mtl_material_transactions_temp
set cost_Group_id = l_cost_group_id,
transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
where transaction_temp_id = l_line_id;
print_debug('update the mtl_txn_request_lines with cost group ' ||
l_cost_group_id || ' and ' || l_transfer_cost_group_id);
update mtl_txn_request_lines
set from_cost_Group_id = l_cost_group_id,
to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
where line_id = l_line_id;
print_debug('update the mmtt with transfer cost group null');
UPDATE mtl_material_transactions_temp
SET transfer_cost_group_id = NULL
WHERE transaction_temp_id = l_line_id;
print_debug('update the mtl_txn_request_lines with to cost group null');
UPDATE mtl_txn_request_lines
SET to_cost_group_id = null
WHERE line_id = l_line_id;
print_debug('update the mmtt with cost group');
UPDATE mtl_material_transactions_temp
SET cost_group_id = l_cost_group_id
WHERE transaction_temp_id = l_line_id;
print_debug('update the mtl_txn_request_lines with cost group ' ||
l_cost_group_id || ' and ' || l_transfer_cost_group_id);
UPDATE mtl_txn_request_lines
SET from_cost_group_id = l_cost_group_id
WHERE line_id = l_line_id;
print_debug('update the mmtt with cost group');
UPDATE mtl_material_transactions_temp
SET cost_Group_id = l_cost_group_id,
transfer_cost_group_id = l_transfer_cost_group_id
WHERE transaction_temp_id = l_line_id;
print_debug('update the mmtt with cost group '|| l_cost_group_id);
UPDATE mtl_material_transactions_temp
SET cost_group_id = l_cost_group_id
--transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
WHERE transaction_temp_id = l_line_id;
print_debug('update the mtl_txn_request_lines with cost group ' || l_cost_group_id);
UPDATE mtl_txn_request_lines
SET from_cost_group_id = l_cost_group_id
--to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
WHERE line_id = l_line_id;
print_debug('update the mmtt with cost group');
UPDATE mtl_material_transactions_temp
SET cost_group_id = l_cost_group_id,
transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
WHERE transaction_temp_id = l_line_id;
print_debug('update the mtl_txn_request_lines with cost group ' ||
l_cost_group_id || ' and ' || l_transfer_cost_group_id);
UPDATE mtl_txn_request_lines
SET from_cost_group_id = l_cost_group_id,
to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
WHERE line_id = l_line_id;
print_debug('update the mmtt with from cost group of null');
print_debug('update the mmtt with tfr cost group of null');
UPDATE mtl_material_transactions_temp
SET cost_group_id = NULL,
transfer_cost_group_id = null
WHERE transaction_temp_id = l_line_id;
print_debug('update the mtl_txn_request_lines with from cost group null');
print_debug('update the mtl_txn_request_lines with tfr cost group null');
UPDATE mtl_txn_request_lines
SET to_cost_group_id = NULL,
from_cost_group_id = null
WHERE line_id = l_line_id;
UPDATE mtl_txn_request_lines
SET
from_cost_group_id = null
WHERE line_id = l_line_id;
UPDATE mtl_material_transactions_temp
SET cost_group_id = NULL
WHERE transaction_temp_id = l_line_id;
UPDATE mtl_txn_request_lines
SET
from_cost_group_id = null
WHERE line_id = l_line_id;
UPDATE mtl_material_transactions_temp
SET cost_group_id = NULL
WHERE transaction_temp_id = l_line_id;
print_debug('update the mmtt with transfer cost group null');
UPDATE mtl_material_transactions_temp
SET transfer_cost_group_id = NULL
WHERE transaction_temp_id = l_line_id;
print_debug('update the mtl_txn_request_lines with transfer cost group null');
UPDATE mtl_txn_request_lines
SET to_cost_group_id = null
WHERE line_id = l_line_id;
SELECT default_cost_group_id
INTO l_transfer_cost_group_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_transfer_subinventory
AND organization_id = l_transfer_organization_id
AND default_cost_group_id IS NOT NULL;
print_debug('update the mmtt with cost group');
UPDATE mtl_material_transactions_temp
SET transfer_cost_group_id = l_transfer_cost_group_id
WHERE transaction_temp_id = l_line_id;
print_debug('update the mtl_txn_request_lines with cost group ' ||
l_cost_group_id || ' and ' || l_transfer_cost_group_id);
UPDATE mtl_txn_request_lines
SET to_cost_group_id = l_transfer_cost_group_id
WHERE line_id = l_line_id;
print_debug('calling inv_cost_group_update.cost_group_update');
inv_cost_group_update.cost_group_update
(p_transaction_rec => p_mmtt_rec,
p_fob_point => l_fob_point,
p_transfer_wms_org => l_transfer_wms_org_flag,
p_tfr_primary_cost_method => l_tfr_primary_cost_method,
p_tfr_org_cost_group_id => l_tfr_org_cost_group_id,
p_from_project_id => l_from_project_id,
p_to_project_id => l_to_project_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('calling inv_cost_group_update.cost_group_update');
inv_cost_group_update.cost_group_update
(p_transaction_rec => p_mmtt_rec,
p_fob_point => l_fob_point,
p_transfer_wms_org => l_transfer_wms_org_flag,
p_tfr_primary_cost_method => l_tfr_primary_cost_method,
p_tfr_org_cost_group_id => l_tfr_org_cost_group_id,
p_from_project_id => l_from_project_id,
p_to_project_id => l_to_project_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
print_debug('update the mmtt with cost group');
UPDATE mtl_material_transactions_temp
SET transfer_cost_group_id = l_transfer_cost_group_id
WHERE transaction_temp_id = l_line_id;
SELECT ccg.cost_group_id, ccg.cost_group
INTO x_cost_group_id, x_cost_group
FROM cst_cost_groups ccg, mtl_onhand_quantities_detail moq
WHERE ccg.cost_group_id = moq.cost_group_id
AND (moq.lot_number = p_lot_number
OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
AND (moq.revision = p_revision
OR (p_revision IS NULL AND moq.revision IS NULL))
AND moq.inventory_item_id = p_inventory_item_id
AND ( (p_locator_id IS NOT NULL AND moq.locator_id = p_locator_id)
OR (p_locator_id IS NULL AND moq.locator_id IS NULL))
AND moq.subinventory_code = p_subinventory_code
AND moq.organization_id = p_organization_id
/* Bug 4662985 -Modifying the condition to fetch records with containerized_flag as null also
AND moq.containerized_flag = 2 -- Loose Items only */
AND NVL(moq.containerized_flag, 2) = 2 -- Loose Items only
/*End of fix for Bug 4662985 */
GROUP BY ccg.cost_group_id, ccg.cost_group;
SELECT ccg.cost_group_id, ccg.cost_group
INTO x_cost_group_id, x_cost_group
FROM cst_cost_groups ccg, mtl_serial_numbers msn
WHERE ccg.cost_group_id = msn.cost_group_id
AND (msn.lot_number = p_lot_number
OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
AND (msn.revision = p_revision
OR (p_revision IS NULL AND msn.revision IS NULL))
AND msn.inventory_item_id = p_inventory_item_id
AND ( (p_locator_id IS NOT NULL AND msn.current_locator_id = p_locator_id)
OR (p_locator_id IS NULL AND msn.current_locator_id IS NULL))
AND msn.current_subinventory_code = p_subinventory_code
AND msn.current_organization_id = p_organization_id
--Bug 4248777- Added the condition to check for the serial number also.
AND msn.serial_number=p_serial_number
AND msn.lpn_id is null --Added this to restrict the query.
--End of fix for Bug 4248777
GROUP BY ccg.cost_group_id, ccg.cost_group;
SELECT ccg.cost_group_id, ccg.cost_group
INTO x_cost_group_id, x_cost_group
FROM cst_cost_groups ccg, wms_lpn_contents wlc,
wms_license_plate_numbers wlpn
WHERE ccg.cost_group_id = wlc.cost_group_id
AND (wlc.lot_number = p_lot_number
OR (p_lot_number IS NULL AND wlc.lot_number IS NULL))
AND (wlc.revision = p_revision
OR (p_revision IS NULL AND wlc.revision IS NULL))
AND wlc.inventory_item_id = p_inventory_item_id
AND wlc.parent_lpn_id = wlpn.lpn_id
AND wlpn.locator_id = p_locator_id
AND wlpn.subinventory_code = p_subinventory_code
AND wlpn.organization_id = p_organization_id
AND wlpn.lpn_id = p_lpn_id
GROUP BY ccg.cost_group_id, ccg.cost_group;
SELECT ccg.cost_group_id, ccg.cost_group
INTO x_cost_group_id, x_cost_group
FROM cst_cost_groups ccg, mtl_serial_numbers msn
WHERE ccg.cost_group_id = msn.cost_group_id
AND (msn.lot_number = p_lot_number
OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
AND (msn.revision = p_revision
OR (p_revision IS NULL AND msn.revision IS NULL))
AND msn.lpn_id = p_lpn_id
AND msn.inventory_item_id = p_inventory_item_id
AND msn.current_locator_id = p_locator_id
AND msn.current_subinventory_code = p_subinventory_code
AND msn.current_organization_id = p_organization_id
--Bug 4248777-Added the condition for the serial number also
AND msn.serial_number = p_serial_number
--End of fix for Bug 4248777
GROUP BY ccg.cost_group_id, ccg.cost_group;