[Home] [Help]
172: -- **************************************************************************************
173:
174: PROCEDURE Get_Available_Time_slot
175: ( p_api_version IN NUMBER,
176: p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
177: p_resource_id IN NUMBER,
178: p_resource_type IN VARCHAR2,
179: p_start_date IN DATE,
180: p_end_date IN DATE,
429: l_current_tbl_end date;
430:
431: BEGIN
432: -- Initialize API return status to success
433: x_return_status := FND_API.G_RET_STS_SUCCESS;
434:
435: -- Standard call to check for call compatibility.
436: IF NOT FND_API.Compatible_API_Call (l_api_version,
437: p_api_version,
432: -- Initialize API return status to success
433: x_return_status := FND_API.G_RET_STS_SUCCESS;
434:
435: -- Standard call to check for call compatibility.
436: IF NOT FND_API.Compatible_API_Call (l_api_version,
437: p_api_version,
438: l_api_name,
439: G_PKG_NAME)
440: THEN
437: p_api_version,
438: l_api_name,
439: G_PKG_NAME)
440: THEN
441: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442: END IF;
443:
444: -- Initialize message list if p_init_msg_list is set to TRUE.
445: IF FND_API.to_Boolean( p_init_msg_list ) THEN
441: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442: END IF;
443:
444: -- Initialize message list if p_init_msg_list is set to TRUE.
445: IF FND_API.to_Boolean( p_init_msg_list ) THEN
446: FND_MSG_PUB.initialize;
447: END IF;
448:
449: -- Call to check for required fields
804:
805:
806: EXCEPTION
807: when g_exc_required_fields_null then
808: x_return_status := fnd_api.g_ret_sts_error ;
809: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
810: fnd_message.set_token('P_PARAMETER', l_parameters);
811: fnd_msg_pub.add;
812: fnd_msg_pub.count_and_get ( p_count => x_msg_count
811: fnd_msg_pub.add;
812: fnd_msg_pub.count_and_get ( p_count => x_msg_count
813: , p_data => x_msg_data );
814:
815: when fnd_api.g_exc_unexpected_error then
816: x_return_status := fnd_api.g_ret_sts_unexp_error;
817: fnd_msg_pub.count_and_get ( p_count => x_msg_count
818: , p_data => x_msg_data );
819: when fnd_api.g_exc_error then
812: fnd_msg_pub.count_and_get ( p_count => x_msg_count
813: , p_data => x_msg_data );
814:
815: when fnd_api.g_exc_unexpected_error then
816: x_return_status := fnd_api.g_ret_sts_unexp_error;
817: fnd_msg_pub.count_and_get ( p_count => x_msg_count
818: , p_data => x_msg_data );
819: when fnd_api.g_exc_error then
820: x_return_status := fnd_api.g_ret_sts_unexp_error;
815: when fnd_api.g_exc_unexpected_error then
816: x_return_status := fnd_api.g_ret_sts_unexp_error;
817: fnd_msg_pub.count_and_get ( p_count => x_msg_count
818: , p_data => x_msg_data );
819: when fnd_api.g_exc_error then
820: x_return_status := fnd_api.g_ret_sts_unexp_error;
821: fnd_msg_pub.count_and_get ( p_count => x_msg_count
822: , p_data => x_msg_data );
823:
816: x_return_status := fnd_api.g_ret_sts_unexp_error;
817: fnd_msg_pub.count_and_get ( p_count => x_msg_count
818: , p_data => x_msg_data );
819: when fnd_api.g_exc_error then
820: x_return_status := fnd_api.g_ret_sts_unexp_error;
821: fnd_msg_pub.count_and_get ( p_count => x_msg_count
822: , p_data => x_msg_data );
823:
824: when G_EXC_INVALID_SLOT_DURATION then
821: fnd_msg_pub.count_and_get ( p_count => x_msg_count
822: , p_data => x_msg_data );
823:
824: when G_EXC_INVALID_SLOT_DURATION then
825: x_return_status := fnd_api.g_ret_sts_error ;
826: fnd_message.set_name('JTF','JTF_CAL_INVALID_DURATION');
827: fnd_msg_pub.add;
828: fnd_msg_pub.count_and_get ( p_count => x_msg_count
829: , p_data => x_msg_data );
827: fnd_msg_pub.add;
828: fnd_msg_pub.count_and_get ( p_count => x_msg_count
829: , p_data => x_msg_data );
830: when others then
831: x_return_status := fnd_api.g_ret_sts_unexp_error ;
832: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
833: fnd_message.set_token('ERROR_CODE',sqlcode);
834: fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
835: fnd_msg_pub.add;
842: -- ****************** Get Available Time **********************
843: --
844: PROCEDURE Get_Available_Time
845: ( p_api_version IN NUMBER,
846: p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
847: p_resource_id IN NUMBER,
848: p_resource_type IN VARCHAR2,
849: p_start_date IN DATE,
850: p_end_date IN DATE,
863: l_shift SHIFT_TBL_TYPE;
864:
865: BEGIN
866: -- Initialize API return status to success
867: x_return_status := FND_API.G_RET_STS_SUCCESS;
868:
869: -- Standard call to check for call compatibility.
870: IF NOT FND_API.Compatible_API_Call (l_api_version,
871: p_api_version,
866: -- Initialize API return status to success
867: x_return_status := FND_API.G_RET_STS_SUCCESS;
868:
869: -- Standard call to check for call compatibility.
870: IF NOT FND_API.Compatible_API_Call (l_api_version,
871: p_api_version,
872: l_api_name,
873: G_PKG_NAME)
874: THEN
871: p_api_version,
872: l_api_name,
873: G_PKG_NAME)
874: THEN
875: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876: END IF;
877:
878: -- Initialize message list if p_init_msg_list is set to TRUE.
879: IF FND_API.to_Boolean( p_init_msg_list ) THEN
875: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876: END IF;
877:
878: -- Initialize message list if p_init_msg_list is set to TRUE.
879: IF FND_API.to_Boolean( p_init_msg_list ) THEN
880: FND_MSG_PUB.initialize;
881: END IF;
882:
883: -- Call to check for required fields
903: x_msg_data => l_msg_data,
904: x_shift => x_shift
905: );
906:
907: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
908: THEN
909: x_return_status := l_return_status;
910: x_msg_count := l_msg_count;
911: x_msg_data := l_msg_data;
912: END IF;
913:
914: EXCEPTION
915: when g_exc_required_fields_null then
916: x_return_status := fnd_api.g_ret_sts_error ;
917: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
918: fnd_message.set_token('P_PARAMETER', l_parameters);
919: fnd_msg_pub.add;
920: fnd_msg_pub.count_and_get ( p_count => x_msg_count
919: fnd_msg_pub.add;
920: fnd_msg_pub.count_and_get ( p_count => x_msg_count
921: , p_data => x_msg_data );
922:
923: when fnd_api.g_exc_unexpected_error then
924: x_return_status := fnd_api.g_ret_sts_unexp_error;
925: fnd_msg_pub.count_and_get ( p_count => x_msg_count
926: , p_data => x_msg_data );
927: when fnd_api.g_exc_error then
920: fnd_msg_pub.count_and_get ( p_count => x_msg_count
921: , p_data => x_msg_data );
922:
923: when fnd_api.g_exc_unexpected_error then
924: x_return_status := fnd_api.g_ret_sts_unexp_error;
925: fnd_msg_pub.count_and_get ( p_count => x_msg_count
926: , p_data => x_msg_data );
927: when fnd_api.g_exc_error then
928: x_return_status := fnd_api.g_ret_sts_unexp_error;
923: when fnd_api.g_exc_unexpected_error then
924: x_return_status := fnd_api.g_ret_sts_unexp_error;
925: fnd_msg_pub.count_and_get ( p_count => x_msg_count
926: , p_data => x_msg_data );
927: when fnd_api.g_exc_error then
928: x_return_status := fnd_api.g_ret_sts_unexp_error;
929: fnd_msg_pub.count_and_get ( p_count => x_msg_count
930: , p_data => x_msg_data );
931:
924: x_return_status := fnd_api.g_ret_sts_unexp_error;
925: fnd_msg_pub.count_and_get ( p_count => x_msg_count
926: , p_data => x_msg_data );
927: when fnd_api.g_exc_error then
928: x_return_status := fnd_api.g_ret_sts_unexp_error;
929: fnd_msg_pub.count_and_get ( p_count => x_msg_count
930: , p_data => x_msg_data );
931:
932: when others then
929: fnd_msg_pub.count_and_get ( p_count => x_msg_count
930: , p_data => x_msg_data );
931:
932: when others then
933: x_return_status := fnd_api.g_ret_sts_unexp_error ;
934: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
935: fnd_message.set_token('ERROR_CODE',sqlcode);
936: fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
937: fnd_msg_pub.add;
943: -- ****************** Get Available Time Slot **********************
944: --
945: PROCEDURE Get_Available_Slot
946: (p_api_version IN NUMBER ,
947: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
948: p_resource_id IN NUMBER ,
949: p_resource_type IN VARCHAR2 ,
950: p_start_date_time IN DATE ,
951: p_end_date_time IN DATE ,
973: v_count number;
974:
975: BEGIN
976: -- Standard call to check for call compatibility.
977: IF NOT FND_API.Compatible_API_Call (l_api_version ,
978: p_api_version ,
979: l_api_name ,
980: G_PKG_NAME )
981: THEN
978: p_api_version ,
979: l_api_name ,
980: G_PKG_NAME )
981: THEN
982: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
983: END IF;
984:
985: -- Initialize message list if p_init_msg_list is set to TRUE.
986: IF FND_API.to_Boolean( p_init_msg_list ) THEN
982: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
983: END IF;
984:
985: -- Initialize message list if p_init_msg_list is set to TRUE.
986: IF FND_API.to_Boolean( p_init_msg_list ) THEN
987: FND_MSG_PUB.initialize;
988: END IF;
989:
990: -- Call to check for required fields
999: END IF;
1000:
1001:
1002: -- Initialize API return status to success
1003: x_return_status := FND_API.G_RET_STS_SUCCESS;
1004:
1005: Get_Available_Time_slot
1006: ( p_api_version => 1.0,
1007: p_resource_id => p_resource_id,
1015: x_msg_data => l_msg_data,
1016: x_shift => l_shift
1017: );
1018:
1019: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1020: THEN
1021: x_return_status := l_return_status;
1022: x_msg_count := l_msg_count;
1023: x_msg_data := l_msg_data;
1033: END IF;
1034: END IF;
1035:
1036: EXCEPTION
1037: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1038: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1039: FND_MSG_PUB.Count_And_Get
1040: ( p_count => x_msg_count,
1041: p_data => x_msg_data
1034: END IF;
1035:
1036: EXCEPTION
1037: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1038: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1039: FND_MSG_PUB.Count_And_Get
1040: ( p_count => x_msg_count,
1041: p_data => x_msg_data
1042: );
1040: ( p_count => x_msg_count,
1041: p_data => x_msg_data
1042: );
1043: WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
1044: x_return_status := FND_API.G_RET_STS_ERROR ;
1045: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
1046: fnd_message.set_token('P_PARAMETER', l_parameters);
1047: fnd_msg_pub.add;
1048: FND_MSG_PUB.Count_And_Get
1048: FND_MSG_PUB.Count_And_Get
1049: (p_count => x_msg_count,
1050: p_data => x_msg_data
1051: );
1052: when fnd_api.g_exc_error then
1053: x_return_status := fnd_api.g_ret_sts_unexp_error;
1054: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1055: , p_data => x_msg_data );
1056: WHEN OTHERS THEN
1049: (p_count => x_msg_count,
1050: p_data => x_msg_data
1051: );
1052: when fnd_api.g_exc_error then
1053: x_return_status := fnd_api.g_ret_sts_unexp_error;
1054: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1055: , p_data => x_msg_data );
1056: WHEN OTHERS THEN
1057: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1053: x_return_status := fnd_api.g_ret_sts_unexp_error;
1054: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1055: , p_data => x_msg_data );
1056: WHEN OTHERS THEN
1057: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1058: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
1059: fnd_message.set_token('ERROR_CODE',SQLCODE);
1060: fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
1061: fnd_msg_pub.add;
1069: -- ************* Get Resource Shifts *******************
1070: --
1071: PROCEDURE get_resource_shifts
1072: ( p_api_version in number
1073: , p_init_msg_list in varchar2 default fnd_api.g_false
1074: , p_resource_id in number
1075: , p_resource_type in varchar2
1076: , p_start_date in date
1077: , p_end_date in date
1179:
1180: l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
1181: l_res_Timezone_id Number;
1182:
1183: l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR ;
1184:
1185: l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
1186:
1187: BEGIN
1186:
1187: BEGIN
1188:
1189: -- standard call to check for call compatibility.
1190: if not fnd_api.compatible_api_call
1191: ( l_api_version
1192: , p_api_version
1193: , l_api_name
1194: , g_pkg_name )
1192: , p_api_version
1193: , l_api_name
1194: , g_pkg_name )
1195: then
1196: raise fnd_api.g_exc_unexpected_error;
1197: end if;
1198:
1199: -- initialize message list if p_init_msg_list is set to true.
1200: if fnd_api.to_boolean ( p_init_msg_list )
1196: raise fnd_api.g_exc_unexpected_error;
1197: end if;
1198:
1199: -- initialize message list if p_init_msg_list is set to true.
1200: if fnd_api.to_boolean ( p_init_msg_list )
1201: then
1202: fnd_msg_pub.initialize;
1203: end if;
1204:
1228: NULL;
1229: END;
1230:
1231: -- initialize api return status to success
1232: x_return_status := fnd_api.g_ret_sts_success;
1233:
1234: l_shift_date := trunc(p_start_date);
1235: while l_shift_date <= p_end_date Loop
1236:
1249: -- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
1250: and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
1251:
1252: -- Added for bug 3270116
1253: l_return_status := FND_API.G_RET_STS_SUCCESS;
1254:
1255: -- added for bug 1355824
1256: -- if condition added for bug 3270116 by ABRAINA
1257: IF Validate_Cal_Date(l_calendar_id, l_shift_date)
1505: end loop;
1506: end if; -- end of l_process
1507: exception
1508: when no_data_found then
1509: x_return_status := FND_API.G_RET_STS_ERROR ;
1510: -- Added for bug 3270116
1511: l_return_status := FND_API.G_RET_STS_ERROR ;
1512: -- end
1513: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
1507: exception
1508: when no_data_found then
1509: x_return_status := FND_API.G_RET_STS_ERROR ;
1510: -- Added for bug 3270116
1511: l_return_status := FND_API.G_RET_STS_ERROR ;
1512: -- end
1513: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
1514: fnd_message.set_token('P_CAL_NAME', l_calendar_name);
1515: fnd_msg_pub.add;
1521: end if; -- valid cal end if
1522:
1523: exception
1524: when no_data_found then
1525: x_return_status := FND_API.G_RET_STS_ERROR ;
1526: -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
1527: -- as for this exception we want to handle it.
1528: -- For a resource, even if a single shift is returned for a search window
1529: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1522:
1523: exception
1524: when no_data_found then
1525: x_return_status := FND_API.G_RET_STS_ERROR ;
1526: -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
1527: -- as for this exception we want to handle it.
1528: -- For a resource, even if a single shift is returned for a search window
1529: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1530: l_tbl.delete; -- to delete the record from TOR if no shift found
1543:
1544: -- see if shift constructs have been found
1545: if x_shift.count = 0
1546: then
1547: x_return_status := fnd_api.g_ret_sts_error ;
1548: -- Added for bug 3270116
1549: l_return_status := FND_API.G_RET_STS_ERROR ;
1550: -- end
1551: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
1545: if x_shift.count = 0
1546: then
1547: x_return_status := fnd_api.g_ret_sts_error ;
1548: -- Added for bug 3270116
1549: l_return_status := FND_API.G_RET_STS_ERROR ;
1550: -- end
1551: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
1552: fnd_msg_pub.add;
1553:
1565:
1566: -- Added for bug 3270116
1567: -- For a resource, even if a single shift is returned for any search window
1568: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1569: if l_return_status = FND_API.G_RET_STS_SUCCESS then
1570: x_return_status := FND_API.G_RET_STS_SUCCESS;
1571: else
1572: x_return_status := FND_API.G_RET_STS_ERROR ;
1573: end if;
1566: -- Added for bug 3270116
1567: -- For a resource, even if a single shift is returned for any search window
1568: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1569: if l_return_status = FND_API.G_RET_STS_SUCCESS then
1570: x_return_status := FND_API.G_RET_STS_SUCCESS;
1571: else
1572: x_return_status := FND_API.G_RET_STS_ERROR ;
1573: end if;
1574: -- end
1568: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1569: if l_return_status = FND_API.G_RET_STS_SUCCESS then
1570: x_return_status := FND_API.G_RET_STS_SUCCESS;
1571: else
1572: x_return_status := FND_API.G_RET_STS_ERROR ;
1573: end if;
1574: -- end
1575: EXCEPTION
1576: when g_exc_required_fields_null then
1573: end if;
1574: -- end
1575: EXCEPTION
1576: when g_exc_required_fields_null then
1577: x_return_status := fnd_api.g_ret_sts_error ;
1578: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
1579: fnd_message.set_token('P_PARAMETER', l_parameters);
1580: fnd_msg_pub.add;
1581: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1580: fnd_msg_pub.add;
1581: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1582: , p_data => x_msg_data );
1583:
1584: when fnd_api.g_exc_unexpected_error then
1585: x_return_status := fnd_api.g_ret_sts_unexp_error;
1586: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1587: , p_data => x_msg_data );
1588: when fnd_api.g_exc_error then
1581: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1582: , p_data => x_msg_data );
1583:
1584: when fnd_api.g_exc_unexpected_error then
1585: x_return_status := fnd_api.g_ret_sts_unexp_error;
1586: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1587: , p_data => x_msg_data );
1588: when fnd_api.g_exc_error then
1589: x_return_status := fnd_api.g_ret_sts_unexp_error;
1584: when fnd_api.g_exc_unexpected_error then
1585: x_return_status := fnd_api.g_ret_sts_unexp_error;
1586: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1587: , p_data => x_msg_data );
1588: when fnd_api.g_exc_error then
1589: x_return_status := fnd_api.g_ret_sts_unexp_error;
1590: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1591: , p_data => x_msg_data );
1592:
1585: x_return_status := fnd_api.g_ret_sts_unexp_error;
1586: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1587: , p_data => x_msg_data );
1588: when fnd_api.g_exc_error then
1589: x_return_status := fnd_api.g_ret_sts_unexp_error;
1590: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1591: , p_data => x_msg_data );
1592:
1593: when others then
1590: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1591: , p_data => x_msg_data );
1592:
1593: when others then
1594: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1595: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
1596: fnd_message.set_token('ERROR_CODE',sqlcode);
1597: fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
1598: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1605: --
1606:
1607: PROCEDURE get_resource_shifts
1608: ( p_api_version in number
1609: , p_init_msg_list in varchar2 default fnd_api.g_false
1610: , p_resource_id in number
1611: , p_resource_type in varchar2
1612: , p_start_date in date
1613: , p_end_date in date
1762:
1763: l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
1764: l_res_Timezone_id Number;
1765:
1766: l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR ;
1767:
1768: l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
1769:
1770: BEGIN
1768: l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
1769:
1770: BEGIN
1771: -- standard call to check for call compatibility.
1772: if not fnd_api.compatible_api_call
1773: ( l_api_version
1774: , p_api_version
1775: , l_api_name
1776: , g_pkg_name )
1774: , p_api_version
1775: , l_api_name
1776: , g_pkg_name )
1777: then
1778: raise fnd_api.g_exc_unexpected_error;
1779: end if;
1780:
1781: -- initialize message list if p_init_msg_list is set to true.
1782: if fnd_api.to_boolean ( p_init_msg_list )
1778: raise fnd_api.g_exc_unexpected_error;
1779: end if;
1780:
1781: -- initialize message list if p_init_msg_list is set to true.
1782: if fnd_api.to_boolean ( p_init_msg_list )
1783: then
1784: fnd_msg_pub.initialize;
1785: end if;
1786:
1796: end if;
1797:
1798:
1799: -- initialize api return status to success
1800: x_return_status := fnd_api.g_ret_sts_success;
1801:
1802: -- This code is added to get resource name to be printed in error message.
1803: -- Added for Bug 4063687
1804: BEGIN
1846: -- and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),to_date(get_g_miss_date,'DD/MM/RRRR'));
1847: and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
1848:
1849: -- Added for bug 3270116
1850: l_return_status := FND_API.G_RET_STS_SUCCESS;
1851:
1852: --added l_shift_date in valid_cal loop bug #1355824
1853: -- if condition added for bug 3270116 by ABRAINA
1854: IF Validate_Cal_Date(l_calendar_id, l_shift_date)
2228: end if; -- end of l_process
2229:
2230: exception
2231: when no_data_found then
2232: x_return_status := FND_API.G_RET_STS_ERROR ;
2233: -- Added for bug 3270116
2234: l_return_status := FND_API.G_RET_STS_ERROR ;
2235: -- end
2236: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
2230: exception
2231: when no_data_found then
2232: x_return_status := FND_API.G_RET_STS_ERROR ;
2233: -- Added for bug 3270116
2234: l_return_status := FND_API.G_RET_STS_ERROR ;
2235: -- end
2236: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
2237: fnd_message.set_token('P_CAL_NAME', l_calendar_name);
2238: fnd_msg_pub.add;
2243: end; --2
2244: end if; -- valid cal end if
2245: exception
2246: when no_data_found then
2247: x_return_status := FND_API.G_RET_STS_ERROR ;
2248: -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
2249: -- as for this exception we want to handle it.
2250: -- For a resource, even if a single shift is returned in the search window
2251: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2244: end if; -- valid cal end if
2245: exception
2246: when no_data_found then
2247: x_return_status := FND_API.G_RET_STS_ERROR ;
2248: -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
2249: -- as for this exception we want to handle it.
2250: -- For a resource, even if a single shift is returned in the search window
2251: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2252: l_tbl.delete; -- to delete the record from TOR if no shift found
2266:
2267: -- see if shift constructs have been found
2268: if x_shift.count = 0
2269: then
2270: x_return_status := fnd_api.g_ret_sts_error ;
2271: -- Added for bug 3270116
2272: l_return_status := FND_API.G_RET_STS_ERROR ;
2273: -- end
2274: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
2268: if x_shift.count = 0
2269: then
2270: x_return_status := fnd_api.g_ret_sts_error ;
2271: -- Added for bug 3270116
2272: l_return_status := FND_API.G_RET_STS_ERROR ;
2273: -- end
2274: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
2275: fnd_msg_pub.add;
2276:
2288:
2289: -- Added for bug 3270116
2290: -- For a resource, even if a single shift is returned for any search window
2291: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2292: if l_return_status = FND_API.G_RET_STS_SUCCESS then
2293: x_return_status := FND_API.G_RET_STS_SUCCESS;
2294: else
2295: x_return_status := FND_API.G_RET_STS_ERROR ;
2296: end if;
2289: -- Added for bug 3270116
2290: -- For a resource, even if a single shift is returned for any search window
2291: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2292: if l_return_status = FND_API.G_RET_STS_SUCCESS then
2293: x_return_status := FND_API.G_RET_STS_SUCCESS;
2294: else
2295: x_return_status := FND_API.G_RET_STS_ERROR ;
2296: end if;
2297: -- end
2291: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2292: if l_return_status = FND_API.G_RET_STS_SUCCESS then
2293: x_return_status := FND_API.G_RET_STS_SUCCESS;
2294: else
2295: x_return_status := FND_API.G_RET_STS_ERROR ;
2296: end if;
2297: -- end
2298:
2299: EXCEPTION
2297: -- end
2298:
2299: EXCEPTION
2300: when g_exc_required_fields_null then
2301: x_return_status := fnd_api.g_ret_sts_error ;
2302: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2303: fnd_message.set_token('P_PARAMETER', l_parameters);
2304: fnd_msg_pub.add;
2305: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2304: fnd_msg_pub.add;
2305: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2306: , p_data => x_msg_data );
2307:
2308: when fnd_api.g_exc_unexpected_error then
2309: x_return_status := fnd_api.g_ret_sts_unexp_error;
2310: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2311: , p_data => x_msg_data );
2312: when fnd_api.g_exc_error then
2305: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2306: , p_data => x_msg_data );
2307:
2308: when fnd_api.g_exc_unexpected_error then
2309: x_return_status := fnd_api.g_ret_sts_unexp_error;
2310: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2311: , p_data => x_msg_data );
2312: when fnd_api.g_exc_error then
2313: x_return_status := fnd_api.g_ret_sts_unexp_error;
2308: when fnd_api.g_exc_unexpected_error then
2309: x_return_status := fnd_api.g_ret_sts_unexp_error;
2310: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2311: , p_data => x_msg_data );
2312: when fnd_api.g_exc_error then
2313: x_return_status := fnd_api.g_ret_sts_unexp_error;
2314: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2315: , p_data => x_msg_data );
2316: when others then
2309: x_return_status := fnd_api.g_ret_sts_unexp_error;
2310: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2311: , p_data => x_msg_data );
2312: when fnd_api.g_exc_error then
2313: x_return_status := fnd_api.g_ret_sts_unexp_error;
2314: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2315: , p_data => x_msg_data );
2316: when others then
2317: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2313: x_return_status := fnd_api.g_ret_sts_unexp_error;
2314: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2315: , p_data => x_msg_data );
2316: when others then
2317: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2318: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2319: fnd_message.set_token('ERROR_CODE',sqlcode);
2320: fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
2321: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2328:
2329: /**********************************************************************/
2330: PROCEDURE Is_Res_Available
2331: ( p_api_version IN NUMBER,
2332: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2333: p_resource_id IN NUMBER,
2334: p_resource_type IN VARCHAR2,
2335: p_start_date_time IN DATE,
2336: p_duration IN NUMBER,
2350: v_end_time date;
2351:
2352: BEGIN
2353: -- Standard call to check for call compatibility.
2354: IF NOT FND_API.Compatible_API_Call (l_api_version ,
2355: p_api_version ,
2356: l_api_name ,
2357: G_PKG_NAME )
2358: THEN
2355: p_api_version ,
2356: l_api_name ,
2357: G_PKG_NAME )
2358: THEN
2359: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2360: END IF;
2361:
2362: -- Initialize message list if p_init_msg_list is set to TRUE.
2363: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2359: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2360: END IF;
2361:
2362: -- Initialize message list if p_init_msg_list is set to TRUE.
2363: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2364: FND_MSG_PUB.initialize;
2365: END IF;
2366:
2367: -- Call to check for required fields
2374: RAISE G_EXC_REQUIRED_FIELDS_NULL;
2375: END IF;
2376:
2377: -- Initialize API return status to success
2378: x_return_status := FND_API.G_RET_STS_SUCCESS;
2379:
2380: Get_Available_Time
2381: ( p_api_version => 1.0,
2382: p_resource_id => p_resource_id,
2407: end loop;
2408: END IF;
2409:
2410: EXCEPTION
2411: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2412: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2413: FND_MSG_PUB.Count_And_Get
2414: ( p_count => x_msg_count,
2415: p_data => x_msg_data
2408: END IF;
2409:
2410: EXCEPTION
2411: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2412: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2413: FND_MSG_PUB.Count_And_Get
2414: ( p_count => x_msg_count,
2415: p_data => x_msg_data
2416: );
2414: ( p_count => x_msg_count,
2415: p_data => x_msg_data
2416: );
2417: WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
2418: x_return_status := FND_API.G_RET_STS_ERROR ;
2419: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2420: fnd_message.set_token('P_PARAMETER', l_parameters);
2421: fnd_msg_pub.add;
2422: FND_MSG_PUB.Count_And_Get
2423: (p_count => x_msg_count,
2424: p_data => x_msg_data
2425: );
2426: WHEN OTHERS THEN
2427: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2428: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2429: fnd_message.set_token('ERROR_CODE',SQLCODE);
2430: fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
2431: fnd_msg_pub.add;
2438: -- ********** Get Resource Schedule **************
2439: --
2440: PROCEDURE Get_Res_Schedule
2441: ( p_api_version IN NUMBER ,
2442: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2443: p_resource_id IN NUMBER ,
2444: p_resource_type IN VARCHAR2 ,
2445: p_start_date IN DATE ,
2446: p_end_date IN DATE ,
2612:
2613: BEGIN
2614:
2615: -- Standard call to check for call compatibility.
2616: IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2617: p_api_version ,
2618: l_api_name ,
2619: G_PKG_NAME )
2620: THEN
2617: p_api_version ,
2618: l_api_name ,
2619: G_PKG_NAME )
2620: THEN
2621: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2622: END IF;
2623: -- Initialize message list if p_init_msg_list is set to TRUE.
2624: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2625: FND_MSG_PUB.initialize;
2620: THEN
2621: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2622: END IF;
2623: -- Initialize message list if p_init_msg_list is set to TRUE.
2624: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2625: FND_MSG_PUB.initialize;
2626: END IF;
2627: -- Call to check for required fields
2628: IF not check_for_required_fields (p_resource_id =>p_resource_id,
2649: NULL;
2650: END;
2651:
2652: -- Initialize API return status to success
2653: x_return_status := FND_API.G_RET_STS_SUCCESS;
2654: --
2655: l_shift_date := trunc(p_start_date);
2656: x_shift.delete;
2657: While l_shift_date <= p_end_date Loop
2889: end if; -- end of l_process check
2890:
2891: exception
2892: when no_data_found then
2893: x_return_status := FND_API.G_RET_STS_ERROR ;
2894: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
2895: fnd_message.set_token('P_CAL_NAME', l_calendar_name);
2896: fnd_msg_pub.add;
2897: FND_MSG_PUB.Count_And_Get
2901: end; --(2)
2902: end if; --(1)
2903: exception
2904: when no_data_found then
2905: x_return_status := FND_API.G_RET_STS_ERROR ;
2906: fnd_message.set_name('JTF','JTF_CAL_RES_NO_CAL');
2907: fnd_message.set_token('P_SHIFT_DATE', l_shift_date);
2908: fnd_message.set_token('P_RES_NAME', l_resource_name);
2909: fnd_msg_pub.add;
2916: end loop;
2917: --
2918: -- Update return status to Success if there is atleast one available time slot
2919: IF x_shift.count > 0 and x_return_status = 'E' THEN
2920: x_return_status := FND_API.G_RET_STS_SUCCESS;
2921: END IF;
2922:
2923: if x_shift.count > 0
2924: then
2926: sort_tab(x_shift);
2927:
2928: end if;
2929: EXCEPTION
2930: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2931: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2932: FND_MSG_PUB.Count_And_Get
2933: ( p_count => x_msg_count,
2934: p_data => x_msg_data
2927:
2928: end if;
2929: EXCEPTION
2930: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2931: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2932: FND_MSG_PUB.Count_And_Get
2933: ( p_count => x_msg_count,
2934: p_data => x_msg_data
2935: );
2933: ( p_count => x_msg_count,
2934: p_data => x_msg_data
2935: );
2936: WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
2937: x_return_status := FND_API.G_RET_STS_ERROR ;
2938: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2939: fnd_message.set_token('P_PARAMETER', l_parameters);
2940: fnd_msg_pub.add;
2941: FND_MSG_PUB.Count_And_Get
2941: FND_MSG_PUB.Count_And_Get
2942: (p_count => x_msg_count,
2943: p_data => x_msg_data
2944: );
2945: when fnd_api.g_exc_error then
2946: x_return_status := fnd_api.g_ret_sts_unexp_error;
2947: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2948: , p_data => x_msg_data );
2949: WHEN OTHERS THEN
2942: (p_count => x_msg_count,
2943: p_data => x_msg_data
2944: );
2945: when fnd_api.g_exc_error then
2946: x_return_status := fnd_api.g_ret_sts_unexp_error;
2947: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2948: , p_data => x_msg_data );
2949: WHEN OTHERS THEN
2950: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2946: x_return_status := fnd_api.g_ret_sts_unexp_error;
2947: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2948: , p_data => x_msg_data );
2949: WHEN OTHERS THEN
2950: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2951: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2952: fnd_message.set_token('ERROR_CODE',SQLCODE);
2953: fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
2954: fnd_msg_pub.add;
2961:
2962: /***********************************************************/
2963: Function get_g_false return varchar2 is
2964: begin
2965: return(fnd_api.g_false);
2966: end get_g_false;
2967:
2968:
2969: function get_g_miss_num return number is
2967:
2968:
2969: function get_g_miss_num return number is
2970: begin
2971: return(fnd_api.g_miss_num);
2972: end get_g_miss_num;
2973:
2974: function get_g_miss_char return varchar2 is
2975: begin
2972: end get_g_miss_num;
2973:
2974: function get_g_miss_char return varchar2 is
2975: begin
2976: return(fnd_api.g_miss_char);
2977: end get_g_miss_char;
2978:
2979: function get_g_miss_date return date is
2980: begin
2977: end get_g_miss_char;
2978:
2979: function get_g_miss_date return date is
2980: begin
2981: return(fnd_api.g_miss_date);
2982: end get_g_miss_date;
2983:
2984: --Bug# 1344222
2985: FUNCTION check_resource_status(p_resource_id IN NUMBER,p_resource_type IN VARCHAR2) RETURN BOOLEAN IS
3024:
3025: BEGIN
3026:
3027: -- Standard call to check for call compatibility.
3028: IF NOT FND_API.Compatible_API_Call (l_api_version ,
3029: p_api_version ,
3030: l_api_name ,
3031: G_PKG_NAME )
3032: THEN
3029: p_api_version ,
3030: l_api_name ,
3031: G_PKG_NAME )
3032: THEN
3033: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3034: END IF;
3035:
3036: -- Initialize message list if p_init_msg_list is set to TRUE.
3037: IF FND_API.to_Boolean( p_init_msg_list ) THEN
3033: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3034: END IF;
3035:
3036: -- Initialize message list if p_init_msg_list is set to TRUE.
3037: IF FND_API.to_Boolean( p_init_msg_list ) THEN
3038: FND_MSG_PUB.initialize;
3039: END IF;
3040:
3041: -- Initialize API return status to success
3038: FND_MSG_PUB.initialize;
3039: END IF;
3040:
3041: -- Initialize API return status to success
3042: x_return_status := FND_API.G_RET_STS_SUCCESS;
3043:
3044: HZ_TIMEZONE_PUB.Get_Time( l_API_VERSION
3045: , l_INIT_MSG_LIST
3046: , P_Resource_TZ_Id
3053:
3054: Return x_Server_time;
3055:
3056: EXCEPTION
3057: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3058: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3059: FND_MSG_PUB.Count_And_Get
3060: ( p_count => x_msg_count,
3061: p_data => x_msg_data
3054: Return x_Server_time;
3055:
3056: EXCEPTION
3057: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3058: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3059: FND_MSG_PUB.Count_And_Get
3060: ( p_count => x_msg_count,
3061: p_data => x_msg_data
3062: );
3060: ( p_count => x_msg_count,
3061: p_data => x_msg_data
3062: );
3063: WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
3064: x_return_status := FND_API.G_RET_STS_ERROR ;
3065: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
3066: fnd_message.set_token('P_PARAMETER', l_parameters);
3067: fnd_msg_pub.add;
3068: FND_MSG_PUB.Count_And_Get
3069: (p_count => x_msg_count,
3070: p_data => x_msg_data
3071: );
3072: WHEN OTHERS THEN
3073: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3074: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
3075: fnd_message.set_token('ERROR_CODE',SQLCODE);
3076: fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
3077: fnd_msg_pub.add;