[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inventory_item_id
INTO l_inventory_item_id
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
ELSIF p_operation = 'UPDATE' OR
p_operation = 'DELETE' THEN
IF l_debug_level > 0 THEN
OE_DEBUG_PUB.Add('Operation is UPDATE/DELETE',3);
SELECT mtl_msi.bom_item_type
,mtl_msi.replenish_to_order_flag
,mtl_msi.config_model_type
INTO l_bom_item_type
,l_replenish_to_order_flag
,l_config_model_type
FROM mtl_system_items mtl_msi
WHERE mtl_msi.inventory_item_id = l_inventory_item_id
AND mtl_msi.organization_id=OE_SYS_PARAMETERS.Value('MASTER_ORGANIZATION_ID',p_org_id); --Bug 5524710
SELECT top_model_line_id,ato_line_id
INTO l_top_model_line_id,l_ato_line_id
FROM oe_order_lines
WHERE line_id = p_line_id;
SELECT mtl_msi.config_model_type
INTO l_config_model_type
FROM mtl_system_items mtl_msi, oe_order_lines oe_l
WHERE oe_l.line_id = l_top_model_line_id
AND oe_l.inventory_item_id = mtl_msi.inventory_item_id
AND mtl_msi.organization_id = OE_SYS_PARAMETERS.Value('MASTER_ORGANIZATION_ID',p_org_id); --Bug 5524710
(p_line_rec.operation=OE_GLOBALS.G_OPR_UPDATE AND
NOT OE_Globals.Equal(p_line_rec.ordered_quantity,
p_old_line_rec.ordered_quantity)) OR
p_line_rec.operation=OE_GLOBALS.G_OPR_DELETE) THEN
IF ( p_line_rec.cancelled_flag = 'Y' AND p_line_rec.ordered_quantity = 0 AND (OE_CONFIG_UTIL.Cascade_Changes_Flag = 'Y' OR l_top_container_model = 'Y') )
OR
( p_line_rec.ordered_quantity = 0 and nvl(p_line_rec.model_remnant_flag, 'N') = 'Y' ) --OR condition Added for fp bug 5662532
THEN
IF l_debug_level > 0 THEN
OE_DEBUG_PUB.Add('Note: Booked MACD Order Cancel Allowed',3);
SELECT nvl(comms_nl_trackable_flag, 'N')
INTO l_ib_trackable_flag
FROM mtl_system_items
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = OE_SYS_PARAMETERS.Value('MASTER_ORGANIZATION_ID');
p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG = 'Y' THEN
x_return_status := FND_API.G_RET_STS_ERROR;
IF p_line_rec.operation = OE_GLOBALS.G_OPR_DELETE AND
OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG = 'Y' THEN
IF NVL(p_line_rec.booked_flag,'N')='N' THEN
BEGIN
SELECT 1
INTO l_delta
FROM cz_config_details_v
WHERE config_delta = 0
AND config_hdr_id = p_line_rec.config_header_id
AND config_rev_nbr = p_line_rec.config_rev_nbr
AND config_item_id = p_line_rec.configuration_id;
OE_DEBUG_PUB.Add('ERRM: No Data Found when selecting config delta',3);
SELECT description
INTO l_description
FROM cz_config_details_v cz_czv, mtl_system_items mtl_msi
WHERE cz_czv.inventory_item_id = mtl_msi.inventory_item_id
AND cz_czv.config_delta <> 0 --implies change
AND cz_czv.config_hdr_id = p_line_rec.config_header_id
AND cz_czv.config_rev_nbr = p_line_rec.config_rev_nbr
AND cz_czv.config_item_id = p_line_rec.configuration_id;
FND_MESSAGE.SET_NAME('ONT','ONT_TSO_DELETE_NOT_ALLOWED');
OE_DEBUG_PUB.Add('ERRM: Line changed in CZ, delete not allowed',3);
END IF; --operation=delete
p_line_rec.operation=OE_GLOBALS.G_OPR_UPDATE THEN
x_return_status := FND_API.G_RET_STS_ERROR;
SELECT cz_hdr.baseline_rev_nbr
INTO l_baseline_rev_nbr
FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
cz_config_items czi
WHERE oe_line.top_model_line_id = p_top_model_line_id
AND czi.config_hdr_id = oe_line.config_header_id
AND czi.config_rev_nbr = oe_line.config_rev_nbr
AND czi.config_item_id = oe_line.configuration_id
AND cz_hdr.config_hdr_id = czi.instance_hdr_id
AND cz_hdr.config_rev_nbr = czi.instance_rev_nbr
AND cz_hdr.baseline_rev_nbr is not NULL
AND rownum = 1;
SELECT czi.config_delta
INTO l_baseline_rev_nbr
FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
cz_config_items czi
WHERE oe_line.top_model_line_id = p_top_model_line_id
AND czi.config_hdr_id = oe_line.config_header_id
AND czi.config_rev_nbr = oe_line.config_rev_nbr
AND czi.config_item_id = oe_line.configuration_id
AND nvl(czi.config_delta, 0) > 0
AND cz_hdr.config_hdr_id = czi.instance_hdr_id
AND cz_hdr.config_rev_nbr = czi.instance_rev_nbr
AND cz_hdr.baseline_rev_nbr is not NULL
AND rownum = 1;
SELECT cz_hdr.baseline_rev_nbr
INTO l_baseline_rev_nbr
FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
cz_config_items czi
WHERE oe_line.line_id = p_line_id
AND czi.config_hdr_id = oe_line.config_header_id
AND czi.config_rev_nbr = oe_line.config_rev_nbr
AND czi.config_item_id = oe_line.configuration_id
AND cz_hdr.config_hdr_id = czi.instance_hdr_id
AND cz_hdr.config_rev_nbr = czi.instance_rev_nbr
AND cz_hdr.baseline_rev_nbr is NOT NULL;
SELECT czi.config_delta
INTO l_baseline_rev_nbr
FROM oe_order_lines oe_line,
cz_config_items czi
WHERE oe_line.line_id = p_line_id
AND czi.config_hdr_id = oe_line.config_header_id
AND czi.config_rev_nbr = oe_line.config_rev_nbr
AND czi.config_item_id = oe_line.configuration_id
AND nvl(czi.config_delta, 0) > 0;
SELECT cz_hdr.baseline_rev_nbr
INTO l_baseline_rev_nbr
FROM cz_config_hdrs cz_hdr, cz_config_items czi
WHERE czi.config_hdr_id = p_line_rec.config_header_id
AND czi.config_rev_nbr = p_line_rec.config_rev_nbr
AND czi.config_item_id = p_line_rec.configuration_id
AND cz_hdr.config_hdr_id = czi.instance_hdr_id
AND cz_hdr.config_rev_nbr = czi.instance_rev_nbr
AND cz_hdr.baseline_rev_nbr is NOT NULL;
SELECT czi.config_delta
INTO l_baseline_rev_nbr
--bug3667985 fix
--FROM cz_config_hdrs cz_hdr, cz_config_items czi
FROM cz_config_items czi
WHERE czi.config_hdr_id = p_line_rec.config_header_id
AND czi.config_rev_nbr = p_line_rec.config_rev_nbr
AND czi.config_item_id = p_line_rec.configuration_id
AND nvl(czi.config_delta, 0) > 0;
SELECT oe_ol.line_id
,oe_ol.config_header_id
,oe_ol.config_rev_nbr
,oe_ol.configuration_id
FROM oe_order_lines oe_ol
,cz_config_details_v cz_det
WHERE oe_ol.top_model_line_id = p_top_model_line_id
AND cz_det.config_delta = 0
AND cz_det.config_hdr_id = oe_ol.config_header_id
AND cz_det.config_rev_nbr = oe_ol.config_rev_nbr
AND cz_det.config_item_id = oe_ol.configuration_id
AND oe_ol.line_id <> oe_ol.top_model_line_id
AND oe_ol.open_flag = 'Y'
ORDER BY option_number desc;
SELECT description
INTO l_description
FROM oe_order_lines oe_oel, mtl_system_items mtl_msi
WHERE oe_oel.line_id = p_line_id
AND oe_oel.inventory_item_id = mtl_msi.inventory_item_id
AND oe_oel.org_id = mtl_msi.organization_id;
SELECT config_header_id
,config_rev_nbr
,header_id
INTO l_top_config_header_id
,l_top_config_rev_nbr
,l_header_id
FROM oe_order_lines oe_l
WHERE oe_l.line_id = p_top_model_line_id;
SELECT line_id
,ato_line_id
,top_model_line_id
FROM oe_order_lines
WHERE header_id = p_header_id
AND top_model_line_id IS NOT NULL
AND line_id = top_model_line_id;
SELECT 'A'
INTO l_config_mode
FROM oe_order_lines
WHERE line_id = p_top_model_line_id
AND config_header_id IS NOT NULL;
SELECT creation_date
,header_id
,inventory_item_id
INTO l_config_creation_date
,l_header_id
,l_model_inv_item_id
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
oe_debug_pub.add('Other exception in select from oe-order_lines',3);
oe_debug_pub.add('RMV: After select from order_lines...',3);
l_line_tbl(I).operation := 'UPDATE';
SELECT line_id
INTO l_line_tbl(I).line_id
FROM oe_order_lines
WHERE header_id = l_line_tbl(I).header_id
AND config_header_id = l_line_tbl(I).config_header_id
AND config_rev_nbr = l_line_tbl(I).config_rev_nbr;
SELECT config_header_id
,config_rev_nbr
,inventory_item_id
INTO l_config_header_id
,l_config_rev_nbr
,l_inventory_item_id
FROM oe_order_lines
WHERE line_id = p_top_model_line_id;
OE_DEBUG_PUB.Add('Error during select errmsg:'||sqlerrm,1);
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_x_header_id
AND open_flag = 'Y'
AND top_model_line_id = line_id
AND ATO_LINE_ID IS NULL;
/* If the p_header_id is passed it means that the header already exists in the system and the newely passed data should be inserted into existing order.
We cannot use the procedure populate_tso_order_lines as that procedure creates container model for the newly passed instances,
but actually the container may already exists in that order. We need to have the logic to identify the existance of the container
model for the passed in instance, if the container exists then the instances should be added to the same or else create the new
container. For example, container "A" was fulfilled and that created instances I1 to I10. First time if the user creates the
order with instance I1, system should create the container "A" and added the I1 to the same. Later if user picks I4 from IB,
system should add the I4 to the existing container model instead of creating a new vcontainer recored in that order.
We will create an API named create_TSO_order_lines procedure to add the lines into existing order.*/
IF l_debug_level > 0 THEN
oe_debug_pub.add('Before calling oe_config_tso_pvt.create_tso_order_lines ',2);
l_instance_tbl.DELETE(J);
l_instance_tbl.DELETE(I);
-- Delete the parent table and also clear the top model so that we do not carry the value.
l_top_model_line_Id := null;
l_parent_exists_instance_tbl.delete;
--If the model does not exists then transfer the record to l_no_paranet_instance_tbl and delete the record.
--commented for BUG#7376452
--The table type being evaluated is l_no_parent_instance_tbl and the conter we use is of l_parent_exists_instance_tbl
--so whenever we evaluate for any standard line only the last line is evaluated evrytime as the conter always remains at 0
--l_no_parent_instance_tbl(l_parent_exists_instance_tbl.count + 1) := l_instance_tbl(I);
l_no_parent_instance_tbl.delete;
IF nvl(p_macd_action, l_instance_tbl(I).action) = 'UPDATE' THEN
l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
Elsif nvl(p_macd_action, l_instance_tbl(I).action) = 'DELETE' THEN
l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
IF nvl(p_macd_action, l_instance_tbl(j).action) = 'UPDATE' THEN
l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
ELSIF nvl(p_macd_action, l_instance_tbl(j).action) = 'DELETE' THEN
l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
l_instance_tbl.DELETE(J);
l_instance_tbl.DELETE(I);
Select config_header_id, config_rev_nbr
Into l_top_config_header_id, l_top_config_rev_nbr
From oe_order_lines_all
Where line_id = l_top_model_line_id;
oe_debug_pub.add(' Line SELECT: '|| SQLERRM , 1 ) ;
-- Delete the parent table and also clear the top model so that we do not carry the value.
l_top_model_line_Id := null;
l_parent_exists_instance_tbl.delete;
IF nvl(p_macd_action, l_line_tbl(M).operation) in ('DELETE', 'DISCONTINUE') THEN
-- If the config details are passed on the line record copy the same to local variables.
-- Or else query the config details for the lines table.
l_config_header_id := l_line_tbl(M).config_header_id;
Select config_header_id, config_rev_nbr, configuration_id
Into l_config_header_id, l_config_rev_nbr, l_config_item_id
From oe_order_lines_all
Where line_id = l_line_tbl(M).line_id;
END IF; -- Delete;
IF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'UPDATE' THEN
l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DELETE' THEN
l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
IF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'UPDATE' THEN
l_config_item_rec.operation := CZ_CF_API.bv_operation_update;
ELSIF nvl(p_macd_action,p_x_line_tbl(J).operation) = 'DELETE' THEN
l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
l_config_item_rec.operation := CZ_CF_API.bv_operation_delete;
l_instance_tbl.DELETE(J);
l_instance_tbl.DELETE(I);
Select config_header_id, config_rev_nbr
Into l_top_config_header_id, l_top_config_rev_nbr
From oe_order_lines_all
Where line_id = l_top_model_line_id;
-- Delete the parent table and also clear the top model so that we do not carry the value.
IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
l_parent_exists_instance_tbl.delete;
Select cz.config_hdr_id, cz.config_rev_nbr, substr(cz.component_code, 1,instr(cz.component_code,'-')-1),
oe.top_model_line_id
Into l_config_hdr_id, l_config_rev_nbr, L_item_id,x_top_model_line_id
from cz_config_details_v cz, oe_order_lines_all oe
where cz.instance_hdr_id = p_config_instance_hdr_id
and oe.config_header_id = cz.config_hdr_id
and oe.config_rev_nbr = cz.config_rev_nbr
and oe.top_model_line_id = oe.line_id
and oe.header_id = p_header_id
and component_instance_type = 'I'
and rownum = 1;
Select config_hdr_id, config_rev_nbr, substr(component_code, 1,instr(component_code,'-')-1)
Into l_config_hdr_id, l_config_rev_nbr, L_item_id
from cz_config_details_v
where instance_hdr_id = p_config_instance_hdr_id
and instance_rev_nbr = p_config_instance_rev_number
and component_instance_type = 'I';
Select top_model_line_id
Into x_top_model_line_id
From oe_order_lines_all
Where header_id = p_header_id
-- And config_header_id = l_config_hdr_id
-- And config_rev_nbr = l_config_rev_nbr
AND open_flag = 'Y'
AND inventory_item_id = l_item_id
And top_model_line_id = line_id
AND rownum = 1;
IF p_macd_action in ('DELETE', 'DISCONTINUE') then
Begin
SELECT cz_hdr.baseline_rev_nbr
INTO l_baseline_rev_nbr
FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
cz_config_details_v czv
WHERE oe_line.top_model_line_id = p_top_model_line_id
AND oe_line.configuration_id = P_instance_item_id
AND czv.config_hdr_id = oe_line.config_header_id
AND czv.config_rev_nbr = oe_line.config_rev_nbr
AND czv.config_item_id = oe_line.configuration_id
AND cz_hdr.config_hdr_id = czv.instance_hdr_id
AND cz_hdr.config_rev_nbr = czv.instance_rev_nbr
AND cz_hdr.baseline_rev_nbr IS NOT NULL
AND rownum = 1;
p_macd_action = 'DELETE' THEN
oe_debug_pub.add('Before raising error',2);
SELECT configuration_id, component_code
INTO x_config_item_id, x_component_code
FROM oe_order_lines_all oe_line
WHERE oe_line.top_model_line_id = p_top_model_line_id
AND oe_line.configuration_id = p_instance_item_id
AND rownum = 1;
If p_macd_action in ('DELETE', 'DISCONTINUE') then
Begin
SELECT cz_hdr.baseline_rev_nbr
INTO l_baseline_rev_nbr
FROM cz_config_hdrs cz_hdr, oe_order_lines oe_line,
cz_config_details_v czv
WHERE oe_line.line_id = p_line_id
AND czv.config_hdr_id = oe_line.config_header_id
AND czv.config_rev_nbr = oe_line.config_rev_nbr
AND czv.config_item_id = oe_line.configuration_id
AND cz_hdr.config_hdr_id = czv.instance_hdr_id
AND cz_hdr.config_rev_nbr = czv.instance_rev_nbr
AND cz_hdr.baseline_rev_nbr IS NOT NULL
AND rownum = 1;
p_macd_action = 'DELETE' THEN
oe_debug_pub.add('Base line rev number greater than 0',2);