[Home] [Help]
690: AND meaning = p_meaning
691: AND SYSDATE BETWEEN start_date_active
692: AND NVL(end_date_active,SYSDATE);
693: END IF;
694: x_return_status := FND_API.G_RET_STS_SUCCESS;
695: EXCEPTION
696: WHEN NO_DATA_FOUND THEN
697: x_return_status := FND_API.G_RET_STS_ERROR;
698: WHEN TOO_MANY_ROWS THEN
693: END IF;
694: x_return_status := FND_API.G_RET_STS_SUCCESS;
695: EXCEPTION
696: WHEN NO_DATA_FOUND THEN
697: x_return_status := FND_API.G_RET_STS_ERROR;
698: WHEN TOO_MANY_ROWS THEN
699: x_return_status := FND_API.G_RET_STS_ERROR;
700: WHEN OTHERS THEN
701: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
695: EXCEPTION
696: WHEN NO_DATA_FOUND THEN
697: x_return_status := FND_API.G_RET_STS_ERROR;
698: WHEN TOO_MANY_ROWS THEN
699: x_return_status := FND_API.G_RET_STS_ERROR;
700: WHEN OTHERS THEN
701: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
702: RAISE;
703: END;
697: x_return_status := FND_API.G_RET_STS_ERROR;
698: WHEN TOO_MANY_ROWS THEN
699: x_return_status := FND_API.G_RET_STS_ERROR;
700: WHEN OTHERS THEN
701: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
702: RAISE;
703: END;
704: --
705: -- To Process Display Perod for Days
3979: -- Parameters :
3980: --
3981: -- Standard IN Parameters :
3982: -- p_api_version IN NUMBER Required
3983: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3984: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3985: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3986: -- Based on this flag, the API will set the default attributes.
3987: -- p_module_type In VARCHAR2 Default NULL
3980: --
3981: -- Standard IN Parameters :
3982: -- p_api_version IN NUMBER Required
3983: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3984: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3985: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3986: -- Based on this flag, the API will set the default attributes.
3987: -- p_module_type In VARCHAR2 Default NULL
3988: -- This will be null.
3981: -- Standard IN Parameters :
3982: -- p_api_version IN NUMBER Required
3983: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3984: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3985: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3986: -- Based on this flag, the API will set the default attributes.
3987: -- p_module_type In VARCHAR2 Default NULL
3988: -- This will be null.
3989: -- Standard out Parameters :
3998: --
3999: --
4000: PROCEDURE Search_Scheduled_Visits (
4001: p_api_version IN NUMBER,
4002: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4003: p_commit IN VARCHAR2 := FND_API.g_false,
4004: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4005: p_module_type IN VARCHAR2 := 'JSP',
4006: p_search_visits_Rec IN search_visits_rec_type,
3999: --
4000: PROCEDURE Search_Scheduled_Visits (
4001: p_api_version IN NUMBER,
4002: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4003: p_commit IN VARCHAR2 := FND_API.g_false,
4004: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4005: p_module_type IN VARCHAR2 := 'JSP',
4006: p_search_visits_Rec IN search_visits_rec_type,
4007: x_scheduled_visit_tbl OUT NOCOPY scheduled_visits_tbl,
4000: PROCEDURE Search_Scheduled_Visits (
4001: p_api_version IN NUMBER,
4002: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4003: p_commit IN VARCHAR2 := FND_API.g_false,
4004: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
4005: p_module_type IN VARCHAR2 := 'JSP',
4006: p_search_visits_Rec IN search_visits_rec_type,
4007: x_scheduled_visit_tbl OUT NOCOPY scheduled_visits_tbl,
4008: x_display_rec OUT NOCOPY display_rec_type,
4087: IF G_DEBUG='Y' THEN
4088: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_schedule_pvt. Search Scheduled Visits','+SPANT+');
4089: END IF;
4090: -- Standard call to check for call compatibility.
4091: IF FND_API.to_boolean(p_init_msg_list)
4092: THEN
4093: FND_MSG_PUB.initialize;
4094: END IF;
4095: -- Initialize API return status to success
4092: THEN
4093: FND_MSG_PUB.initialize;
4094: END IF;
4095: -- Initialize API return status to success
4096: x_return_status := FND_API.G_RET_STS_SUCCESS;
4097: -- Initialize message list if p_init_msg_list is set to TRUE.
4098: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4099: p_api_version,
4100: l_api_name,G_PKG_NAME)
4094: END IF;
4095: -- Initialize API return status to success
4096: x_return_status := FND_API.G_RET_STS_SUCCESS;
4097: -- Initialize message list if p_init_msg_list is set to TRUE.
4098: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4099: p_api_version,
4100: l_api_name,G_PKG_NAME)
4101: THEN
4102: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4098: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4099: p_api_version,
4100: l_api_name,G_PKG_NAME)
4101: THEN
4102: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4103: END IF;
4104: --
4105: IF G_DEBUG='Y' THEN
4106: AHL_DEBUG_PUB.debug( 'l_search_visits_Rec.start_date'||l_search_visits_Rec.start_date);
4108: --
4109: ---------------------start API Body------------------------------------
4110: -- Get org id from org name or vice versa
4111: IF (l_search_visits_Rec.ORG_ID IS NOT NULL AND
4112: l_search_visits_Rec.ORG_ID <> FND_API.G_MISS_NUM) OR
4113: (l_search_visits_Rec.ORG_NAME IS NOT NULL AND
4114: l_search_visits_Rec.ORG_NAME <> FND_API.G_MISS_CHAR)
4115: THEN
4116: --
4110: -- Get org id from org name or vice versa
4111: IF (l_search_visits_Rec.ORG_ID IS NOT NULL AND
4112: l_search_visits_Rec.ORG_ID <> FND_API.G_MISS_NUM) OR
4113: (l_search_visits_Rec.ORG_NAME IS NOT NULL AND
4114: l_search_visits_Rec.ORG_NAME <> FND_API.G_MISS_CHAR)
4115: THEN
4116: --
4117: OPEN l_org_id_cur(l_search_visits_Rec.ORG_ID,l_search_visits_Rec.ORG_NAME);
4118: FETCH l_org_id_cur INTO l_search_visits_Rec.ORG_ID,l_search_visits_Rec.ORG_NAME;
4120: Fnd_Message.set_name('AHL', 'AHL_LTP_INVALID_ORG');
4121: Fnd_Message.Set_Token('ORG',NVL(l_search_visits_Rec.ORG_NAME,l_search_visits_Rec.ORG_ID));
4122: Fnd_Msg_Pub.ADD;
4123: CLOSE l_org_id_cur;
4124: RAISE Fnd_Api.G_EXC_ERROR;
4125: --
4126: END IF;
4127: CLOSE l_org_id_cur;
4128: --
4128: --
4129: END IF;
4130: --For Space Category
4131: IF l_search_visits_Rec.space_category_mean IS NOT NULL AND
4132: l_search_visits_Rec.space_category_mean <> Fnd_Api.G_MISS_CHAR
4133: THEN
4134: Check_lookup_name_Or_Id (
4135: p_lookup_type => 'AHL_LTP_SPACE_CATEGORY',
4136: p_lookup_code => NULL,
4142: IF NVL(l_return_status, 'X') <> 'S'
4143: THEN
4144: Fnd_Message.SET_NAME('AHL','AHL_LTP_SP_CATEGORY_NOT_EXIST');
4145: Fnd_Msg_Pub.ADD;
4146: RAISE Fnd_Api.G_EXC_ERROR;
4147: END IF;
4148: ELSE
4149: -- Id presents
4150: IF l_search_visits_Rec.space_category IS NOT NULL AND
4147: END IF;
4148: ELSE
4149: -- Id presents
4150: IF l_search_visits_Rec.space_category IS NOT NULL AND
4151: l_search_visits_Rec.space_category <> Fnd_Api.G_MISS_CHAR
4152: THEN
4153: l_search_visits_Rec.space_category := l_search_visits_Rec.space_category;
4154: END IF;
4155: END IF;
4155: END IF;
4156:
4157: --For Visit type
4158: IF l_search_visits_Rec.visit_type_mean IS NOT NULL AND
4159: l_search_visits_Rec.visit_type_mean <> Fnd_Api.G_MISS_CHAR
4160: THEN
4161: Check_lookup_name_Or_Id (
4162: p_lookup_type => 'AHL_PLANNING_VISIT_TYPE',
4163: p_lookup_code => NULL,
4169: IF NVL(l_return_status, 'X') <> 'S'
4170: THEN
4171: Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_INVALID');
4172: Fnd_Msg_Pub.ADD;
4173: RAISE Fnd_Api.G_EXC_ERROR;
4174: END IF;
4175: ELSE
4176: -- Id presents
4177: IF l_search_visits_Rec.visit_type_code IS NOT NULL AND
4174: END IF;
4175: ELSE
4176: -- Id presents
4177: IF l_search_visits_Rec.visit_type_code IS NOT NULL AND
4178: l_search_visits_Rec.visit_type_code <> Fnd_Api.G_MISS_CHAR
4179: THEN
4180: l_search_visits_Rec.visit_type_code := l_search_visits_Rec.visit_type_code;
4181: END IF;
4182: END IF;
4182: END IF;
4183:
4184: -- AnRaj: Changed the code by splitting the cursor, perf bug 5208300, index was not being hit because of OR
4185: -- For Item based on the name,
4186: IF (l_search_visits_Rec.item_description IS NOT NULL AND l_search_visits_Rec.item_description <> Fnd_Api.G_MISS_CHAR)
4187: THEN
4188: OPEN l_item_name_cur(l_search_visits_Rec.item_description);
4189: FETCH l_item_name_cur INTO l_search_visits_rec.item_id;
4190: IF l_item_name_cur%NOTFOUND THEN
4191: Fnd_Message.set_name('AHL', 'AHL_LTP_ITEM_NOT_EXIST');
4192: Fnd_Message.Set_Token('ITEM',NVL(l_search_visits_Rec.ITEM_DESCRIPTION,l_search_visits_Rec.ITEM_ID));
4193: Fnd_Msg_Pub.ADD;
4194: CLOSE l_item_name_cur;
4195: RAISE Fnd_Api.G_EXC_ERROR;
4196: END IF;
4197: CLOSE l_item_name_cur;
4198: END IF;
4199:
4196: END IF;
4197: CLOSE l_item_name_cur;
4198: END IF;
4199:
4200: IF (l_search_visits_Rec.item_id IS NOT NULL AND l_search_visits_Rec.item_id <> Fnd_Api.G_MISS_NUM)
4201: THEN
4202: OPEN l_item_id_cur (l_search_visits_Rec.item_id);
4203: FETCH l_item_id_cur INTO l_search_visits_rec.item_id;
4204: IF l_item_id_cur%NOTFOUND THEN
4205: Fnd_Message.set_name('AHL', 'AHL_LTP_ITEM_NOT_EXIST');
4206: Fnd_Message.Set_Token('ITEM',NVL(l_search_visits_Rec.ITEM_DESCRIPTION,l_search_visits_Rec.ITEM_ID));
4207: Fnd_Msg_Pub.ADD;
4208: CLOSE l_item_id_cur;
4209: RAISE Fnd_Api.G_EXC_ERROR;
4210: END IF;
4211: CLOSE l_item_id_cur;
4212: END IF;
4213: -- AnRaj: Bug fix for 5208300 end.
4213: -- AnRaj: Bug fix for 5208300 end.
4214:
4215: --For Display Period
4216: IF l_search_visits_Rec.display_period_mean IS NOT NULL AND
4217: l_search_visits_Rec.display_period_mean <> Fnd_Api.G_MISS_CHAR
4218: THEN
4219: Check_lookup_name_Or_Id (
4220: p_lookup_type => 'AHL_LTP_DISPLAY_PERIOD',
4221: p_lookup_code => NULL,
4227: IF NVL(l_return_status, 'X') <> 'S'
4228: THEN
4229: Fnd_Message.SET_NAME('AHL','AHL_LTP_DISPLAY_INVALID');
4230: Fnd_Msg_Pub.ADD;
4231: RAISE Fnd_Api.G_EXC_ERROR;
4232: END IF;
4233: ELSE
4234: -- Id presents
4235: IF l_search_visits_Rec.display_period_code IS NOT NULL AND
4232: END IF;
4233: ELSE
4234: -- Id presents
4235: IF l_search_visits_Rec.display_period_code IS NOT NULL AND
4236: l_search_visits_Rec.display_period_code <> Fnd_Api.G_MISS_CHAR
4237: THEN
4238: l_search_visits_Rec.display_period_code := l_search_visits_Rec.display_period_code;
4239: END IF;
4240: END IF;
4251: IF get_plan_id_cur%NOTFOUND THEN
4252: Fnd_Message.set_name('AHL', 'AHL_LTP_INVALID_PLAN');
4253: Fnd_Msg_Pub.ADD;
4254: CLOSE get_plan_id_cur;
4255: RAISE Fnd_Api.G_EXC_ERROR;
4256: END IF;
4257: CLOSE get_plan_id_cur;
4258: --
4259: l_start_date := trunc(l_search_visits_Rec.start_date);
4512: l_msg_count := Fnd_Msg_Pub.count_msg;
4513:
4514: IF l_msg_count > 0 THEN
4515: X_msg_count := l_msg_count;
4516: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4517: RAISE Fnd_Api.G_EXC_ERROR;
4518: END IF;
4519:
4520: --Standard check for commit
4513:
4514: IF l_msg_count > 0 THEN
4515: X_msg_count := l_msg_count;
4516: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4517: RAISE Fnd_Api.G_EXC_ERROR;
4518: END IF;
4519:
4520: --Standard check for commit
4521: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4517: RAISE Fnd_Api.G_EXC_ERROR;
4518: END IF;
4519:
4520: --Standard check for commit
4521: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4522: COMMIT;
4523: END IF;
4524: -- Debug info
4525: IF G_DEBUG='Y' THEN
4528: Ahl_Debug_Pub.disable_debug;
4529: END IF;
4530:
4531: EXCEPTION
4532: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4533: ROLLBACK TO Search_Scheduled_Visits;
4534: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4535: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4536: p_count => x_msg_count,
4530:
4531: EXCEPTION
4532: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4533: ROLLBACK TO Search_Scheduled_Visits;
4534: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4535: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4536: p_count => x_msg_count,
4537: p_data => x_msg_data);
4538:
4531: EXCEPTION
4532: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4533: ROLLBACK TO Search_Scheduled_Visits;
4534: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4535: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4536: p_count => x_msg_count,
4537: p_data => x_msg_data);
4538:
4539: IF G_DEBUG='Y' THEN
4542: AHL_DEBUG_PUB.debug( 'ahl_ltp_space_schedule_pvt.Search Scheduled Visits','+SPANT+');
4543: -- Check if API is called in debug mode. If yes, disable debug.
4544: AHL_DEBUG_PUB.disable_debug;
4545: END IF;
4546: WHEN FND_API.G_EXC_ERROR THEN
4547: ROLLBACK TO Search_Scheduled_Visits;
4548: X_return_status := FND_API.G_RET_STS_ERROR;
4549: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4550: p_count => x_msg_count,
4544: AHL_DEBUG_PUB.disable_debug;
4545: END IF;
4546: WHEN FND_API.G_EXC_ERROR THEN
4547: ROLLBACK TO Search_Scheduled_Visits;
4548: X_return_status := FND_API.G_RET_STS_ERROR;
4549: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4550: p_count => x_msg_count,
4551: p_data => X_msg_data);
4552: IF G_DEBUG='Y' THEN
4545: END IF;
4546: WHEN FND_API.G_EXC_ERROR THEN
4547: ROLLBACK TO Search_Scheduled_Visits;
4548: X_return_status := FND_API.G_RET_STS_ERROR;
4549: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4550: p_count => x_msg_count,
4551: p_data => X_msg_data);
4552: IF G_DEBUG='Y' THEN
4553:
4559: AHL_DEBUG_PUB.disable_debug;
4560: END IF;
4561: WHEN OTHERS THEN
4562: ROLLBACK TO Search_Scheduled_Visits;
4563: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4564: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4565: THEN
4566: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
4567: p_procedure_name => 'SEARCH_SCHEDULED_VISITS',
4566: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
4567: p_procedure_name => 'SEARCH_SCHEDULED_VISITS',
4568: p_error_text => SUBSTR(SQLERRM,1,240));
4569: END IF;
4570: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4571: p_count => x_msg_count,
4572: p_data => X_msg_data);
4573: IF G_DEBUG='Y' THEN
4574:
4607:
4608: --
4609: l_api_name CONSTANT VARCHAR2(30) := 'GET_VISIT_DURATION';
4610: l_api_version CONSTANT NUMBER := 1.0;
4611: l_init_msg_list VARCHAR2(30) := fnd_api.g_true;
4612: l_msg_count NUMBER;
4613: l_return_status VARCHAR2(1);
4614: l_msg_data VARCHAR2(2000);
4615: l_visit_start_time DATE;
4630: IF G_DEBUG='Y' THEN
4631: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_schedule_pvt.get_visit_duration','+SPSL+');
4632: END IF;
4633: -- Standard call to check for call compatibility.
4634: IF FND_API.to_boolean(l_init_msg_list)
4635: THEN
4636: FND_MSG_PUB.initialize;
4637: END IF;
4638: -- Initialize API return status to success
4635: THEN
4636: FND_MSG_PUB.initialize;
4637: END IF;
4638: -- Initialize API return status to success
4639: x_return_status := FND_API.G_RET_STS_SUCCESS;
4640: -- Initialize message list if p_init_msg_list is set to TRUE.
4641: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4642: l_api_version,
4643: l_api_name,G_PKG_NAME)
4637: END IF;
4638: -- Initialize API return status to success
4639: x_return_status := FND_API.G_RET_STS_SUCCESS;
4640: -- Initialize message list if p_init_msg_list is set to TRUE.
4641: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4642: l_api_version,
4643: l_api_name,G_PKG_NAME)
4644: THEN
4645: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4641: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4642: l_api_version,
4643: l_api_name,G_PKG_NAME)
4644: THEN
4645: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4646: END IF;
4647: --
4648: IF G_DEBUG='Y' THEN
4649: AHL_DEBUG_PUB.debug( 'inside get visit duration :'||p_visit_id);
4708: -- Check if API is called in debug mode. If yes, disable debug.
4709: Ahl_Debug_Pub.disable_debug;
4710: END IF;
4711: EXCEPTION
4712: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4713: ROLLBACK TO get_visit_duration;
4714: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4715: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4716: p_count => x_msg_count,
4710: END IF;
4711: EXCEPTION
4712: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4713: ROLLBACK TO get_visit_duration;
4714: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4715: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4716: p_count => x_msg_count,
4717: p_data => x_msg_data);
4718: IF G_DEBUG='Y' THEN
4711: EXCEPTION
4712: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4713: ROLLBACK TO get_visit_duration;
4714: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4715: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4716: p_count => x_msg_count,
4717: p_data => x_msg_data);
4718: IF G_DEBUG='Y' THEN
4719:
4723: -- Check if API is called in debug mode. If yes, disable debug.
4724: AHL_DEBUG_PUB.disable_debug;
4725: END IF;
4726:
4727: WHEN FND_API.G_EXC_ERROR THEN
4728: ROLLBACK TO get_visit_duration;
4729: X_return_status := FND_API.G_RET_STS_ERROR;
4730: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4731: p_count => x_msg_count,
4725: END IF;
4726:
4727: WHEN FND_API.G_EXC_ERROR THEN
4728: ROLLBACK TO get_visit_duration;
4729: X_return_status := FND_API.G_RET_STS_ERROR;
4730: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4731: p_count => x_msg_count,
4732: p_data => X_msg_data);
4733: IF G_DEBUG='Y' THEN
4726:
4727: WHEN FND_API.G_EXC_ERROR THEN
4728: ROLLBACK TO get_visit_duration;
4729: X_return_status := FND_API.G_RET_STS_ERROR;
4730: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4731: p_count => x_msg_count,
4732: p_data => X_msg_data);
4733: IF G_DEBUG='Y' THEN
4734:
4741: END IF;
4742:
4743: WHEN OTHERS THEN
4744: ROLLBACK TO get_visit_duration;
4745: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4746: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4747: THEN
4748: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
4749: p_procedure_name => 'GET_VISIT_DURATION',
4748: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
4749: p_procedure_name => 'GET_VISIT_DURATION',
4750: p_error_text => SUBSTR(SQLERRM,1,240));
4751: END IF;
4752: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4753: p_count => x_msg_count,
4754: p_data => X_msg_data);
4755: IF G_DEBUG='Y' THEN
4756:
4789: l_api_version CONSTANT NUMBER := 1.0;
4790: l_msg_count NUMBER;
4791: l_return_status VARCHAR2(1);
4792: l_msg_data VARCHAR2(2000);
4793: l_init_msg_list VARCHAR2(30) := FND_API.g_true;
4794: l_visit_start_time DATE;
4795: l_visit_end_time DATE;
4796:
4797: BEGIN
4805: IF G_DEBUG='Y' THEN
4806: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_schedule_pvt.get_visit_end_date','+SUAVL+');
4807: END IF;
4808: -- Standard call to check for call compatibility.
4809: IF FND_API.to_boolean(l_init_msg_list)
4810: THEN
4811: FND_MSG_PUB.initialize;
4812: END IF;
4813: -- Initialize API return status to success
4810: THEN
4811: FND_MSG_PUB.initialize;
4812: END IF;
4813: -- Initialize API return status to success
4814: x_return_status := FND_API.G_RET_STS_SUCCESS;
4815: -- Initialize message list if p_init_msg_list is set to TRUE.
4816: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4817: l_api_version,
4818: l_api_name,G_PKG_NAME)
4812: END IF;
4813: -- Initialize API return status to success
4814: x_return_status := FND_API.G_RET_STS_SUCCESS;
4815: -- Initialize message list if p_init_msg_list is set to TRUE.
4816: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4817: l_api_version,
4818: l_api_name,G_PKG_NAME)
4819: THEN
4820: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4816: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4817: l_api_version,
4818: l_api_name,G_PKG_NAME)
4819: THEN
4820: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4821: END IF;
4822: --
4823: IF G_DEBUG='Y' THEN
4824: AHL_DEBUG_PUB.debug( 'inside get visit end date :'||p_visit_id);
4823: IF G_DEBUG='Y' THEN
4824: AHL_DEBUG_PUB.debug( 'inside get visit end date :'||p_visit_id);
4825: END IF;
4826: --
4827: IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM )THEN
4828: OPEN get_visit_cur(p_visit_id);
4829: FETCH get_visit_cur INTO l_visit_start_time;
4830: CLOSE get_visit_cur;
4831: --
4858: Ahl_Debug_Pub.disable_debug;
4859: END IF;
4860:
4861: EXCEPTION
4862: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4863: ROLLBACK TO get_visit_end_date;
4864: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4865: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4866: p_count => x_msg_count,
4860:
4861: EXCEPTION
4862: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4863: ROLLBACK TO get_visit_end_date;
4864: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4865: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4866: p_count => x_msg_count,
4867: p_data => x_msg_data);
4868: IF G_DEBUG='Y' THEN
4861: EXCEPTION
4862: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4863: ROLLBACK TO get_visit_end_date;
4864: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4865: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4866: p_count => x_msg_count,
4867: p_data => x_msg_data);
4868: IF G_DEBUG='Y' THEN
4869:
4873: -- Check if API is called in debug mode. If yes, disable debug.
4874: AHL_DEBUG_PUB.disable_debug;
4875: END IF;
4876:
4877: WHEN FND_API.G_EXC_ERROR THEN
4878: ROLLBACK TO get_visit_end_date;
4879: X_return_status := FND_API.G_RET_STS_ERROR;
4880: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4881: p_count => x_msg_count,
4875: END IF;
4876:
4877: WHEN FND_API.G_EXC_ERROR THEN
4878: ROLLBACK TO get_visit_end_date;
4879: X_return_status := FND_API.G_RET_STS_ERROR;
4880: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4881: p_count => x_msg_count,
4882: p_data => X_msg_data);
4883: IF G_DEBUG='Y' THEN
4876:
4877: WHEN FND_API.G_EXC_ERROR THEN
4878: ROLLBACK TO get_visit_end_date;
4879: X_return_status := FND_API.G_RET_STS_ERROR;
4880: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4881: p_count => x_msg_count,
4882: p_data => X_msg_data);
4883: IF G_DEBUG='Y' THEN
4884: -- Debug info.
4890: END IF;
4891:
4892: WHEN OTHERS THEN
4893: ROLLBACK TO get_visit_end_date;
4894: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4895: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4896: THEN
4897: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
4898: p_procedure_name => 'GET_VISIT_END_DATE',
4897: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
4898: p_procedure_name => 'GET_VISIT_END_DATE',
4899: p_error_text => SUBSTR(SQLERRM,1,240));
4900: END IF;
4901: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4902: p_count => x_msg_count,
4903: p_data => X_msg_data);
4904: IF G_DEBUG='Y' THEN
4905:
4925: l_api_version CONSTANT NUMBER := 1.0;
4926: l_msg_count NUMBER;
4927: l_return_status VARCHAR2(1);
4928: l_msg_data VARCHAR2(2000);
4929: l_init_msg_list VARCHAR2(30) := FND_API.g_true;
4930: l_commit VARCHAR2(10);
4931: l_validation_level NUMBER := FND_API.g_valid_level_full;
4932: --
4933: l_count1 NUMBER;
4927: l_return_status VARCHAR2(1);
4928: l_msg_data VARCHAR2(2000);
4929: l_init_msg_list VARCHAR2(30) := FND_API.g_true;
4930: l_commit VARCHAR2(10);
4931: l_validation_level NUMBER := FND_API.g_valid_level_full;
4932: --
4933: l_count1 NUMBER;
4934: l_count2 NUMBER;
4935: l_date DATE;
4962: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_schedule_pvt.get_visit_due_by_date','+SPSL+');
4963: --
4964: END IF;
4965: -- Standard call to check for call compatibility.
4966: IF FND_API.to_boolean(l_init_msg_list)
4967: THEN
4968: FND_MSG_PUB.initialize;
4969: END IF;
4970: -- Initialize API return status to success
4967: THEN
4968: FND_MSG_PUB.initialize;
4969: END IF;
4970: -- Initialize API return status to success
4971: x_return_status := FND_API.G_RET_STS_SUCCESS;
4972: -- Initialize message list if p_init_msg_list is set to TRUE.
4973: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4974: l_api_version,
4975: l_api_name,G_PKG_NAME)
4969: END IF;
4970: -- Initialize API return status to success
4971: x_return_status := FND_API.G_RET_STS_SUCCESS;
4972: -- Initialize message list if p_init_msg_list is set to TRUE.
4973: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4974: l_api_version,
4975: l_api_name,G_PKG_NAME)
4976: THEN
4977: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4973: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4974: l_api_version,
4975: l_api_name,G_PKG_NAME)
4976: THEN
4977: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4978: END IF;
4979: --
4980: IF G_DEBUG='Y' THEN
4981: AHL_DEBUG_PUB.debug( 'inside visit id due by date :'||p_visit_id);
5008: l_msg_count := Fnd_Msg_Pub.count_msg;
5009:
5010: IF l_msg_count > 0 THEN
5011: X_msg_count := l_msg_count;
5012: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5013: RAISE Fnd_Api.G_EXC_ERROR;
5014: END IF;
5015: -- Debug info
5016: IF G_DEBUG='Y' THEN
5009:
5010: IF l_msg_count > 0 THEN
5011: X_msg_count := l_msg_count;
5012: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5013: RAISE Fnd_Api.G_EXC_ERROR;
5014: END IF;
5015: -- Debug info
5016: IF G_DEBUG='Y' THEN
5017: Ahl_Debug_Pub.debug( 'End of private api Get visit due by date','+SPSL+');
5018: -- Check if API is called in debug mode. If yes, disable debug.
5019: Ahl_Debug_Pub.disable_debug;
5020: END IF;
5021: EXCEPTION
5022: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5023: ROLLBACK TO get_visit_due_by_date;
5024: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5025: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5026: p_count => x_msg_count,
5020: END IF;
5021: EXCEPTION
5022: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5023: ROLLBACK TO get_visit_due_by_date;
5024: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5025: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5026: p_count => x_msg_count,
5027: p_data => x_msg_data);
5028: IF G_DEBUG='Y' THEN
5021: EXCEPTION
5022: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5023: ROLLBACK TO get_visit_due_by_date;
5024: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5025: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5026: p_count => x_msg_count,
5027: p_data => x_msg_data);
5028: IF G_DEBUG='Y' THEN
5029: AHL_DEBUG_PUB.log_app_messages (
5031: AHL_DEBUG_PUB.debug( 'ahl_ltp_space_schedule_pvt.Get visit due by date','+SPSL+');
5032: -- Check if API is called in debug mode. If yes, disable debug.
5033: AHL_DEBUG_PUB.disable_debug;
5034: END IF;
5035: WHEN FND_API.G_EXC_ERROR THEN
5036: ROLLBACK TO get_visit_due_by_date;
5037: X_return_status := FND_API.G_RET_STS_ERROR;
5038: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5039: p_count => x_msg_count,
5033: AHL_DEBUG_PUB.disable_debug;
5034: END IF;
5035: WHEN FND_API.G_EXC_ERROR THEN
5036: ROLLBACK TO get_visit_due_by_date;
5037: X_return_status := FND_API.G_RET_STS_ERROR;
5038: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5039: p_count => x_msg_count,
5040: p_data => X_msg_data);
5041: IF G_DEBUG='Y' THEN
5034: END IF;
5035: WHEN FND_API.G_EXC_ERROR THEN
5036: ROLLBACK TO get_visit_due_by_date;
5037: X_return_status := FND_API.G_RET_STS_ERROR;
5038: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5039: p_count => x_msg_count,
5040: p_data => X_msg_data);
5041: IF G_DEBUG='Y' THEN
5042:
5048: AHL_DEBUG_PUB.disable_debug;
5049: END IF;
5050: WHEN OTHERS THEN
5051: ROLLBACK TO get_visit_due_by_date;
5052: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5053: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5054: THEN
5055: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
5056: p_procedure_name => 'GET_VISIT_DUE_BY_DATE',
5055: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
5056: p_procedure_name => 'GET_VISIT_DUE_BY_DATE',
5057: p_error_text => SUBSTR(SQLERRM,1,240));
5058: END IF;
5059: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5060: p_count => x_msg_count,
5061: p_data => X_msg_data);
5062: IF G_DEBUG='Y' THEN
5063:
5082: l_api_version CONSTANT NUMBER := 1.0;
5083: l_msg_count NUMBER;
5084: l_return_status VARCHAR2(1);
5085: l_msg_data VARCHAR2(2000);
5086: l_init_msg_list VARCHAR2(30) := FND_API.g_true;
5087: l_commit VARCHAR2(10);
5088: l_validation_level NUMBER := FND_API.g_valid_level_full;
5089: l_visit_end_date DATE;
5090: BEGIN
5084: l_return_status VARCHAR2(1);
5085: l_msg_data VARCHAR2(2000);
5086: l_init_msg_list VARCHAR2(30) := FND_API.g_true;
5087: l_commit VARCHAR2(10);
5088: l_validation_level NUMBER := FND_API.g_valid_level_full;
5089: l_visit_end_date DATE;
5090: BEGIN
5091: -- Standard Start of API savepoint
5092: SAVEPOINT Derive_visit_end_date;
5101: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_schedule_pvt Derive visit end date','+SPSL+');
5102: --
5103: END IF;
5104: -- Standard call to check for call compatibility.
5105: IF FND_API.to_boolean(l_init_msg_list)
5106: THEN
5107: FND_MSG_PUB.initialize;
5108: END IF;
5109: -- Initialize API return status to success
5106: THEN
5107: FND_MSG_PUB.initialize;
5108: END IF;
5109: -- Initialize API return status to success
5110: x_return_status := FND_API.G_RET_STS_SUCCESS;
5111: -- Initialize message list if p_init_msg_list is set to TRUE.
5112: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
5113: l_api_version,
5114: l_api_name,G_PKG_NAME)
5108: END IF;
5109: -- Initialize API return status to success
5110: x_return_status := FND_API.G_RET_STS_SUCCESS;
5111: -- Initialize message list if p_init_msg_list is set to TRUE.
5112: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
5113: l_api_version,
5114: l_api_name,G_PKG_NAME)
5115: THEN
5116: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5112: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
5113: l_api_version,
5114: l_api_name,G_PKG_NAME)
5115: THEN
5116: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5117: END IF;
5118: --
5119: IF G_DEBUG='Y' THEN
5120: AHL_DEBUG_PUB.debug( 'inside derieve visit end date :');
5129: x_return_status => l_return_status,
5130: x_msg_count => l_msg_count,
5131: x_msg_data => l_msg_data);
5132:
5133: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5134: l_msg_count := FND_MSG_PUB.count_msg;
5135: IF l_msg_count > 0 THEN
5136: RAISE FND_API.G_EXC_ERROR;
5137: END IF;
5132:
5133: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5134: l_msg_count := FND_MSG_PUB.count_msg;
5135: IF l_msg_count > 0 THEN
5136: RAISE FND_API.G_EXC_ERROR;
5137: END IF;
5138: END IF;
5139:
5140: IF l_return_status = 'S' THEN
5151: Ahl_Debug_Pub.disable_debug;
5152: END IF;
5153:
5154: EXCEPTION
5155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5156: ROLLBACK TO Derive_visit_end_date;
5157: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5159: p_count => x_msg_count,
5153:
5154: EXCEPTION
5155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5156: ROLLBACK TO Derive_visit_end_date;
5157: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5159: p_count => x_msg_count,
5160: p_data => x_msg_data);
5161: IF G_DEBUG='Y' THEN
5154: EXCEPTION
5155: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5156: ROLLBACK TO Derive_visit_end_date;
5157: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5158: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5159: p_count => x_msg_count,
5160: p_data => x_msg_data);
5161: IF G_DEBUG='Y' THEN
5162:
5166: -- Check if API is called in debug mode. If yes, disable debug.
5167: AHL_DEBUG_PUB.disable_debug;
5168: END IF;
5169:
5170: WHEN FND_API.G_EXC_ERROR THEN
5171: ROLLBACK TO Derive_visit_end_date;
5172: X_return_status := FND_API.G_RET_STS_ERROR;
5173: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5174: p_count => x_msg_count,
5168: END IF;
5169:
5170: WHEN FND_API.G_EXC_ERROR THEN
5171: ROLLBACK TO Derive_visit_end_date;
5172: X_return_status := FND_API.G_RET_STS_ERROR;
5173: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5174: p_count => x_msg_count,
5175: p_data => X_msg_data);
5176: IF G_DEBUG='Y' THEN
5169:
5170: WHEN FND_API.G_EXC_ERROR THEN
5171: ROLLBACK TO Derive_visit_end_date;
5172: X_return_status := FND_API.G_RET_STS_ERROR;
5173: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5174: p_count => x_msg_count,
5175: p_data => X_msg_data);
5176: IF G_DEBUG='Y' THEN
5177:
5184: END IF;
5185:
5186: WHEN OTHERS THEN
5187: ROLLBACK TO Derive_visit_end_date;
5188: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5189: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5190: THEN
5191: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
5192: p_procedure_name => 'DERIEVE_VISIT_END_DATE',
5191: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
5192: p_procedure_name => 'DERIEVE_VISIT_END_DATE',
5193: p_error_text => SUBSTR(SQLERRM,1,240));
5194: END IF;
5195: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
5196: p_count => x_msg_count,
5197: p_data => X_msg_data);
5198: IF G_DEBUG='Y' THEN
5199:
5215: -- Parameters :
5216: --
5217: -- Standard IN Parameters :
5218: -- p_api_version IN NUMBER Required
5219: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
5220: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
5221: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
5222: -- Based on this flag, the API will set the default attributes.
5223: -- p_module_type In VARCHAR2 Default NULL
5216: --
5217: -- Standard IN Parameters :
5218: -- p_api_version IN NUMBER Required
5219: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
5220: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
5221: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
5222: -- Based on this flag, the API will set the default attributes.
5223: -- p_module_type In VARCHAR2 Default NULL
5224: -- This will be null.
5217: -- Standard IN Parameters :
5218: -- p_api_version IN NUMBER Required
5219: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
5220: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
5221: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
5222: -- Based on this flag, the API will set the default attributes.
5223: -- p_module_type In VARCHAR2 Default NULL
5224: -- This will be null.
5225: -- Standard out Parameters :
5233: --
5234: --
5235: PROCEDURE Get_Visit_Details (
5236: p_api_version IN NUMBER,
5237: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
5238: p_commit IN VARCHAR2 := FND_API.g_false,
5239: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
5240: p_module_type IN VARCHAR2 := 'JSP',
5241: p_search_visits_Rec IN search_visits_rec_type,
5234: --
5235: PROCEDURE Get_Visit_Details (
5236: p_api_version IN NUMBER,
5237: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
5238: p_commit IN VARCHAR2 := FND_API.g_false,
5239: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
5240: p_module_type IN VARCHAR2 := 'JSP',
5241: p_search_visits_Rec IN search_visits_rec_type,
5242: x_visit_details_tbl OUT NOCOPY visit_details_tbl,
5235: PROCEDURE Get_Visit_Details (
5236: p_api_version IN NUMBER,
5237: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
5238: p_commit IN VARCHAR2 := FND_API.g_false,
5239: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
5240: p_module_type IN VARCHAR2 := 'JSP',
5241: p_search_visits_Rec IN search_visits_rec_type,
5242: x_visit_details_tbl OUT NOCOPY visit_details_tbl,
5243: x_return_status OUT NOCOPY VARCHAR2,
5492: IF G_DEBUG='Y' THEN
5493: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_schedule_pvt Get visit details','+SPSL+');
5494: END IF;
5495: -- Standard call to check for call compatibility.
5496: IF FND_API.to_boolean(p_init_msg_list)
5497: THEN
5498: FND_MSG_PUB.initialize;
5499: END IF;
5500: -- Initialize API return status to success
5497: THEN
5498: FND_MSG_PUB.initialize;
5499: END IF;
5500: -- Initialize API return status to success
5501: x_return_status := FND_API.G_RET_STS_SUCCESS;
5502: -- Initialize message list if p_init_msg_list is set to TRUE.
5503: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
5504: l_api_version,
5505: l_api_name,G_PKG_NAME)
5499: END IF;
5500: -- Initialize API return status to success
5501: x_return_status := FND_API.G_RET_STS_SUCCESS;
5502: -- Initialize message list if p_init_msg_list is set to TRUE.
5503: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
5504: l_api_version,
5505: l_api_name,G_PKG_NAME)
5506: THEN
5507: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5503: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
5504: l_api_version,
5505: l_api_name,G_PKG_NAME)
5506: THEN
5507: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5508: END IF;
5509: --
5510: --
5511: IF G_DEBUG='Y' THEN
5517: AHL_DEBUG_PUB.debug( 'inside visit detailsITEMDES :'||l_search_visits_Rec.item_description);
5518: END IF;
5519: -- Get org id from org name or vice versa
5520: IF (l_search_visits_Rec.ORG_ID IS NOT NULL AND
5521: l_search_visits_Rec.ORG_ID <> FND_API.G_MISS_NUM) OR
5522: (l_search_visits_Rec.ORG_NAME IS NOT NULL AND
5523: l_search_visits_Rec.ORG_NAME <> FND_API.G_MISS_CHAR)
5524: THEN
5525: --
5519: -- Get org id from org name or vice versa
5520: IF (l_search_visits_Rec.ORG_ID IS NOT NULL AND
5521: l_search_visits_Rec.ORG_ID <> FND_API.G_MISS_NUM) OR
5522: (l_search_visits_Rec.ORG_NAME IS NOT NULL AND
5523: l_search_visits_Rec.ORG_NAME <> FND_API.G_MISS_CHAR)
5524: THEN
5525: --
5526: OPEN l_org_id_cur(l_search_visits_Rec.ORG_ID,l_search_visits_Rec.ORG_NAME);
5527: FETCH l_org_id_cur INTO l_search_visits_Rec.ORG_ID,l_search_visits_Rec.ORG_NAME;
5529: Fnd_Message.set_name('AHL', 'AHL_LTP_INVALID_ORG');
5530: Fnd_Message.Set_Token('ORG',NVL(l_search_visits_Rec.ORG_NAME,l_search_visits_Rec.ORG_ID));
5531: Fnd_Msg_Pub.ADD;
5532: CLOSE l_org_id_cur;
5533: RAISE Fnd_Api.G_EXC_ERROR;
5534: --
5535: END IF;
5536: CLOSE l_org_id_cur;
5537: --
5543: END IF;
5544:
5545: -- Get Dept id from dept name or vice versa
5546: IF ((l_search_visits_Rec.DEPARTMENT_ID IS NOT NULL AND
5547: l_search_visits_Rec.DEPARTMENT_ID <> FND_API.G_MISS_NUM) OR
5548: (l_search_visits_Rec.DEPARTMENT_NAME IS NOT NULL AND
5549: l_search_visits_Rec.DEPARTMENT_NAME <> FND_API.G_MISS_CHAR))
5550: THEN
5551: --
5545: -- Get Dept id from dept name or vice versa
5546: IF ((l_search_visits_Rec.DEPARTMENT_ID IS NOT NULL AND
5547: l_search_visits_Rec.DEPARTMENT_ID <> FND_API.G_MISS_NUM) OR
5548: (l_search_visits_Rec.DEPARTMENT_NAME IS NOT NULL AND
5549: l_search_visits_Rec.DEPARTMENT_NAME <> FND_API.G_MISS_CHAR))
5550: THEN
5551: --
5552: OPEN l_dept_id_cur(l_search_visits_Rec.ORG_ID,
5553: l_search_visits_Rec.department_id,
5559: Fnd_Message.set_name('AHL', 'AHL_LTP_INVALID_DEPT');
5560: Fnd_Message.Set_Token('DEPT',NVL(l_search_visits_Rec.DEPARTMENT_NAME,l_search_visits_Rec.DEPARTMENT_ID));
5561: Fnd_Msg_Pub.ADD;
5562: CLOSE l_dept_id_cur;
5563: RAISE Fnd_Api.G_EXC_ERROR;
5564: --
5565: END IF;
5566: CLOSE l_dept_id_cur;
5567: --
5568: END IF;
5569: --
5570: --For Space Category
5571: IF l_search_visits_Rec.space_category_mean IS NOT NULL AND
5572: l_search_visits_Rec.space_category_mean <> Fnd_Api.G_MISS_CHAR
5573: THEN
5574: Check_lookup_name_Or_Id (
5575: p_lookup_type => 'AHL_LTP_SPACE_CATEGORY',
5576: p_lookup_code => NULL,
5582: IF NVL(l_return_status, 'X') <> 'S'
5583: THEN
5584: Fnd_Message.SET_NAME('AHL','AHL_LTP_SP_CATEGORY_NOT_EXIST');
5585: Fnd_Msg_Pub.ADD;
5586: RAISE Fnd_Api.G_EXC_ERROR;
5587: END IF;
5588: ELSE
5589: -- Id presents
5590: IF l_search_visits_Rec.space_category IS NOT NULL AND
5587: END IF;
5588: ELSE
5589: -- Id presents
5590: IF l_search_visits_Rec.space_category IS NOT NULL AND
5591: l_search_visits_Rec.space_category <> Fnd_Api.G_MISS_CHAR
5592: THEN
5593: l_search_visits_Rec.space_category := l_search_visits_Rec.space_category;
5594: END IF;
5595: END IF;
5595: END IF;
5596:
5597: --For Visit type
5598: IF l_search_visits_Rec.visit_type_mean IS NOT NULL AND
5599: l_search_visits_Rec.visit_type_mean <> Fnd_Api.G_MISS_CHAR
5600: THEN
5601: Check_lookup_name_Or_Id (
5602: p_lookup_type => 'AHL_PLANNING_VISIT_TYPE',
5603: p_lookup_code => NULL,
5609: IF NVL(l_return_status, 'X') <> 'S'
5610: THEN
5611: Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_INVALID');
5612: Fnd_Msg_Pub.ADD;
5613: RAISE Fnd_Api.G_EXC_ERROR;
5614: END IF;
5615: ELSE
5616: -- Id presents
5617: IF l_search_visits_Rec.visit_type_code IS NOT NULL AND
5614: END IF;
5615: ELSE
5616: -- Id presents
5617: IF l_search_visits_Rec.visit_type_code IS NOT NULL AND
5618: l_search_visits_Rec.visit_type_code <> Fnd_Api.G_MISS_CHAR
5619: THEN
5620: l_search_visits_Rec.visit_type_code := l_search_visits_Rec.visit_type_code;
5621: END IF;
5622: END IF;
5622: END IF;
5623:
5624: --For Display Period
5625: IF l_search_visits_Rec.display_period_mean IS NOT NULL AND
5626: l_search_visits_Rec.display_period_mean <> Fnd_Api.G_MISS_CHAR
5627: THEN
5628: Check_lookup_name_Or_Id (
5629: p_lookup_type => 'AHL_LTP_DISPLAY_PERIOD',
5630: p_lookup_code => NULL,
5636: IF NVL(l_return_status, 'X') <> 'S'
5637: THEN
5638: Fnd_Message.SET_NAME('AHL','AHL_LTP_DISPLAY_INVALID');
5639: Fnd_Msg_Pub.ADD;
5640: RAISE Fnd_Api.G_EXC_ERROR;
5641: END IF;
5642: ELSE
5643: -- Id presents
5644: IF l_search_visits_Rec.display_period_code IS NOT NULL AND
5641: END IF;
5642: ELSE
5643: -- Id presents
5644: IF l_search_visits_Rec.display_period_code IS NOT NULL AND
5645: l_search_visits_Rec.display_period_code <> Fnd_Api.G_MISS_CHAR
5646: THEN
5647: l_search_visits_Rec.display_period_code := l_search_visits_Rec.display_period_code;
5648: END IF;
5649: END IF;
5650:
5651: --
5652: -- Get space id or space name
5653: IF ((l_search_visits_Rec.space_name IS NOT NULL AND
5654: l_search_visits_Rec.space_name <> FND_API.G_MISS_CHAR) OR
5655: (l_search_visits_Rec.space_id IS NOT NULL AND
5656: l_search_visits_Rec.space_id <> FND_API.G_MISS_NUM)) THEN
5657: --
5658: OPEN l_space_id_cur(l_search_visits_Rec.SPACE_ID,l_search_visits_Rec.SPACE_NAME,
5652: -- Get space id or space name
5653: IF ((l_search_visits_Rec.space_name IS NOT NULL AND
5654: l_search_visits_Rec.space_name <> FND_API.G_MISS_CHAR) OR
5655: (l_search_visits_Rec.space_id IS NOT NULL AND
5656: l_search_visits_Rec.space_id <> FND_API.G_MISS_NUM)) THEN
5657: --
5658: OPEN l_space_id_cur(l_search_visits_Rec.SPACE_ID,l_search_visits_Rec.SPACE_NAME,
5659: l_search_visits_Rec.DEPARTMENT_ID);
5660: FETCH l_space_id_cur INTO l_search_visits_Rec.SPACE_ID,l_search_visits_Rec.SPACE_NAME;
5662: Fnd_Message.set_name('AHL', 'AHL_LTP_INVALID_SPACE');
5663: Fnd_Message.Set_Token('SPACE',NVL(l_search_visits_Rec.SPACE_NAME,l_search_visits_Rec.SPACE_ID));
5664: Fnd_Msg_Pub.ADD;
5665: CLOSE l_space_id_cur;
5666: RAISE Fnd_Api.G_EXC_ERROR;
5667: --
5668: END IF;
5669: CLOSE l_space_id_cur;
5670: --
5670: --
5671: END IF;
5672:
5673: -- AnRaj: Changed the code by splitting the cursor, perf bug 5208300, index was not being hit because of OR
5674: IF (l_search_visits_Rec.item_description IS NOT NULL AND l_search_visits_Rec.item_description <> Fnd_Api.G_MISS_CHAR)
5675: THEN
5676: OPEN l_item_name_cur(l_search_visits_Rec.item_description);
5677: FETCH l_item_name_cur INTO l_search_visits_rec.item_id,l_search_visits_rec.item_description;
5678: IF l_item_name_cur%NOTFOUND THEN
5679: Fnd_Message.set_name('AHL', 'AHL_LTP_ITEM_NOT_EXIST');
5680: Fnd_Message.Set_Token('ITEM',NVL(l_search_visits_Rec.ITEM_DESCRIPTION,l_search_visits_Rec.ITEM_ID));
5681: Fnd_Msg_Pub.ADD;
5682: CLOSE l_item_name_cur;
5683: RAISE Fnd_Api.G_EXC_ERROR;
5684: END IF;
5685: CLOSE l_item_name_cur;
5686: END IF;
5687:
5684: END IF;
5685: CLOSE l_item_name_cur;
5686: END IF;
5687:
5688: IF (l_search_visits_Rec.item_id IS NOT NULL AND l_search_visits_Rec.item_id <> Fnd_Api.G_MISS_NUM)
5689: THEN
5690: OPEN l_item_id_cur (l_search_visits_Rec.item_id);
5691: FETCH l_item_id_cur INTO l_search_visits_rec.item_id,l_search_visits_rec.item_description;
5692: IF l_item_id_cur%NOTFOUND THEN
5693: Fnd_Message.set_name('AHL', 'AHL_LTP_ITEM_NOT_EXIST');
5694: Fnd_Message.Set_Token('ITEM',NVL(l_search_visits_Rec.ITEM_DESCRIPTION,l_search_visits_Rec.ITEM_ID));
5695: Fnd_Msg_Pub.ADD;
5696: CLOSE l_item_id_cur;
5697: RAISE Fnd_Api.G_EXC_ERROR;
5698: END IF;
5699: CLOSE l_item_id_cur;
5700: END IF;
5701: -- AnRaj: Bug fix for 5208300 end.
5706: IF get_plan_id_cur%NOTFOUND THEN
5707: Fnd_Message.set_name('AHL', 'AHL_LTP_INVALID_PLAN');
5708: Fnd_Msg_Pub.ADD;
5709: CLOSE get_plan_id_cur;
5710: RAISE Fnd_Api.G_EXC_ERROR;
5711: END IF;
5712: CLOSE get_plan_id_cur;
5713: --Assign dates
5714: l_start_period := trunc(l_search_visits_Rec.start_period);
6060: l_msg_count := Fnd_Msg_Pub.count_msg;
6061:
6062: IF l_msg_count > 0 THEN
6063: X_msg_count := l_msg_count;
6064: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6065: RAISE Fnd_Api.G_EXC_ERROR;
6066: END IF;
6067: -- Debug info
6068: IF G_DEBUG='Y' THEN
6061:
6062: IF l_msg_count > 0 THEN
6063: X_msg_count := l_msg_count;
6064: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6065: RAISE Fnd_Api.G_EXC_ERROR;
6066: END IF;
6067: -- Debug info
6068: IF G_DEBUG='Y' THEN
6069: Ahl_Debug_Pub.debug( 'End of private api Get visit details','+SPSL+');
6072: --
6073: END IF;
6074:
6075: EXCEPTION
6076: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6077: ROLLBACK TO Get_visit_details;
6078: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6079: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6080: p_count => x_msg_count,
6074:
6075: EXCEPTION
6076: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6077: ROLLBACK TO Get_visit_details;
6078: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6079: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6080: p_count => x_msg_count,
6081: p_data => x_msg_data);
6082: IF G_DEBUG='Y' THEN
6075: EXCEPTION
6076: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6077: ROLLBACK TO Get_visit_details;
6078: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6079: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6080: p_count => x_msg_count,
6081: p_data => x_msg_data);
6082: IF G_DEBUG='Y' THEN
6083:
6087: -- Check if API is called in debug mode. If yes, disable debug.
6088: AHL_DEBUG_PUB.disable_debug;
6089: END IF;
6090:
6091: WHEN FND_API.G_EXC_ERROR THEN
6092: ROLLBACK TO Get_visit_details;
6093: X_return_status := FND_API.G_RET_STS_ERROR;
6094: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6095: p_count => x_msg_count,
6089: END IF;
6090:
6091: WHEN FND_API.G_EXC_ERROR THEN
6092: ROLLBACK TO Get_visit_details;
6093: X_return_status := FND_API.G_RET_STS_ERROR;
6094: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6095: p_count => x_msg_count,
6096: p_data => X_msg_data);
6097: IF G_DEBUG='Y' THEN
6090:
6091: WHEN FND_API.G_EXC_ERROR THEN
6092: ROLLBACK TO Get_visit_details;
6093: X_return_status := FND_API.G_RET_STS_ERROR;
6094: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6095: p_count => x_msg_count,
6096: p_data => X_msg_data);
6097: IF G_DEBUG='Y' THEN
6098:
6104: AHL_DEBUG_PUB.disable_debug;
6105: END IF;
6106: WHEN OTHERS THEN
6107: ROLLBACK TO Get_visit_details;
6108: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6109: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6110: THEN
6111: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
6112: p_procedure_name => 'GET_VISIT_DETAILS',
6111: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_SCHEDULE_PVT',
6112: p_procedure_name => 'GET_VISIT_DETAILS',
6113: p_error_text => SUBSTR(SQLERRM,1,240));
6114: END IF;
6115: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
6116: p_count => x_msg_count,
6117: p_data => X_msg_data);
6118: IF G_DEBUG='Y' THEN
6119:
6153: AHL_DEBUG_PUB.enable_debug;
6154: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_schedule_pvt Get derived end date','+SPSL+');
6155: END IF;
6156: --
6157: IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM )THEN
6158: OPEN get_visit_cur(p_visit_id);
6159: FETCH get_visit_cur INTO l_start_date_time;
6160: CLOSE get_visit_cur;
6161: IF l_start_date_time IS NOT NULL THEN