The following lines contain the word 'select', 'insert', 'update' or 'delete':
ELSIF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_UPDATE
OR l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_DELETE
THEN
l_Sourcing_Rule_rec.db_flag := FND_API.G_TRUE;
IF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_DELETE THEN
MRP_Validate_Sourcing_Rule.Entity_Delete
( x_return_status => l_return_status
, p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
);
IF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_DELETE THEN
MRP_Sourcing_Rule_Handlers.Delete_Row
( p_Sourcing_Rule_Id => l_Sourcing_Rule_rec.Sourcing_Rule_Id
);
l_Sourcing_Rule_rec.last_update_date := SYSDATE;
l_Sourcing_Rule_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Sourcing_Rule_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_UPDATE THEN
MRP_Sourcing_Rule_Handlers.Update_Row (l_Sourcing_Rule_rec);
MRP_Sourcing_Rule_Handlers.Insert_Row (l_Sourcing_Rule_rec);
ELSIF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_UPDATE
OR l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_DELETE
THEN
l_Receiving_Org_rec.db_flag := FND_API.G_TRUE;
IF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_DELETE THEN
MRP_Validate_Receiving_Org.Entity_Delete
( x_return_status => l_return_status
, p_Receiving_Org_rec => l_Receiving_Org_rec
);
IF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_DELETE THEN
MRP_Receiving_Org_Handlers.Delete_Row
( p_Sr_Receipt_Id => l_Receiving_Org_rec.Sr_Receipt_Id
);
l_Receiving_Org_rec.last_update_date := SYSDATE;
l_Receiving_Org_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Receiving_Org_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_UPDATE THEN
MRP_Receiving_Org_Handlers.Update_Row (l_Receiving_Org_rec);
MRP_Receiving_Org_Handlers.Insert_Row (l_Receiving_Org_rec);
ELSIF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_UPDATE
OR l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_DELETE
THEN
l_Shipping_Org_rec.db_flag := FND_API.G_TRUE;
IF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_DELETE THEN
MRP_Validate_Shipping_Org.Entity_Delete
( x_return_status => l_return_status
, p_Shipping_Org_rec => l_Shipping_Org_rec
);
IF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_DELETE THEN
MRP_Shipping_Org_Handlers.Delete_Row
( p_Sr_Source_Id => l_Shipping_Org_rec.Sr_Source_Id
);
l_Shipping_Org_rec.last_update_date := SYSDATE;
l_Shipping_Org_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Shipping_Org_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_UPDATE THEN
MRP_Shipping_Org_Handlers.Update_Row (l_Shipping_Org_rec);
MRP_Shipping_Org_Handlers.Insert_Row (l_Shipping_Org_rec);
l_sourcing_rule_rec.operation <> MRP_GLOBALS.G_OPR_DELETE
THEN
FOR I IN 1..l_Receiving_Org_tbl.COUNT LOOP
l_Receiving_Org_rec := l_Receiving_Org_tbl(I);
SELECT count(*)
INTO l_count
FROM MRP_SR_RECEIPT_ORG RO1,
MRP_SR_RECEIPT_ORG RO2
WHERE RO1.sourcing_rule_id =
l_sourcing_rule_rec.sourcing_rule_id
AND RO1.sr_receipt_id = l_Receiving_Org_rec.sr_receipt_id
AND RO2.sourcing_rule_id = RO1.sourcing_rule_id
AND RO2.sr_receipt_id <> RO1.sr_receipt_id
/** Bug 2257098
AND RO1.EFFECTIVE_DATE >= RO2.EFFECTIVE_DATE
AND RO1.EFFECTIVE_DATE <
NVL(RO2.DISABLE_DATE, RO1.EFFECTIVE_DATE + 1);
l_sourcing_rule_rec.operation <> MRP_GLOBALS.G_OPR_DELETE
THEN
l_tot_alloc_percent := 0;
UPDATE mrp_sourcing_rules
SET planning_active = 2
WHERE sourcing_rule_id =
l_sourcing_rule_rec.sourcing_rule_id;
SELECT 1 INTO org_exists
FROM mtl_interorg_parameters
WHERE to_organization_id = l_Receiving_Org_rec.receipt_organization_id
AND from_organization_id = l_Shipping_Org_rec.source_organization_id;
SELECT count(*)
INTO l_count
FROM MRP_SR_SOURCE_ORG SO1,
MRP_SR_SOURCE_ORG SO2
WHERE SO1.sr_receipt_id =
l_Shipping_Org_rec.sr_receipt_id
AND SO1.sr_source_id = l_Shipping_Org_rec.sr_source_id
AND SO2.sr_receipt_id = SO1.sr_receipt_id
AND SO2.sr_source_id <> SO1.sr_source_id
AND NVL(SO2.rank, -999) = NVL(SO1.rank,-9999);
SELECT organization_id
INTO l_organization_id
FROM mrp_cust_sup_org_v
WHERE supplier_id = to_char(l_Shipping_Org_rec.vendor_id)
AND supplier_site_id = to_char(l_Shipping_Org_rec.vendor_site_id);
UPDATE mrp_sr_source_org
SET source_organization_id = l_organization_id
WHERE sr_source_id = l_Shipping_Org_rec.sr_source_id;
UPDATE mrp_sourcing_rules
SET planning_active = 2
WHERE sourcing_rule_id =
l_sourcing_rule_rec.sourcing_rule_id;
l_sourcing_rule_rec.operation <> MRP_GLOBALS.G_OPR_DELETE
THEN
-- Every sourcing rule should have at least one receiving org
-- and every receiving org should have at least one source org
SELECT count(*)
INTO l_count
FROM MRP_SR_RECEIPT_ORG
WHERE sourcing_rule_id = l_sourcing_rule_rec.sourcing_rule_id;
SELECT count(*)
INTO l_count
FROM MRP_SR_SOURCE_ORG
WHERE sr_receipt_id = l_Receiving_Org_tbl(I).Sr_Receipt_Id;