[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,
371: l_current_tbl_end date;
372:
373: BEGIN
374: -- Initialize API return status to success
375: x_return_status := FND_API.G_RET_STS_SUCCESS;
376:
377: -- Standard call to check for call compatibility.
378: IF NOT FND_API.Compatible_API_Call (l_api_version,
379: p_api_version,
374: -- Initialize API return status to success
375: x_return_status := FND_API.G_RET_STS_SUCCESS;
376:
377: -- Standard call to check for call compatibility.
378: IF NOT FND_API.Compatible_API_Call (l_api_version,
379: p_api_version,
380: l_api_name,
381: G_PKG_NAME)
382: THEN
379: p_api_version,
380: l_api_name,
381: G_PKG_NAME)
382: THEN
383: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
384: END IF;
385:
386: -- Initialize message list if p_init_msg_list is set to TRUE.
387: IF FND_API.to_Boolean( p_init_msg_list ) THEN
383: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
384: END IF;
385:
386: -- Initialize message list if p_init_msg_list is set to TRUE.
387: IF FND_API.to_Boolean( p_init_msg_list ) THEN
388: FND_MSG_PUB.initialize;
389: END IF;
390:
391: -- Call to check for required fields
737:
738:
739: EXCEPTION
740: when g_exc_required_fields_null then
741: x_return_status := fnd_api.g_ret_sts_error ;
742: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
743: fnd_message.set_token('P_PARAMETER', l_parameters);
744: fnd_msg_pub.add;
745: fnd_msg_pub.count_and_get ( p_count => x_msg_count
744: fnd_msg_pub.add;
745: fnd_msg_pub.count_and_get ( p_count => x_msg_count
746: , p_data => x_msg_data );
747:
748: when fnd_api.g_exc_unexpected_error then
749: x_return_status := fnd_api.g_ret_sts_unexp_error;
750: fnd_msg_pub.count_and_get ( p_count => x_msg_count
751: , p_data => x_msg_data );
752: when fnd_api.g_exc_error then
745: fnd_msg_pub.count_and_get ( p_count => x_msg_count
746: , p_data => x_msg_data );
747:
748: when fnd_api.g_exc_unexpected_error then
749: x_return_status := fnd_api.g_ret_sts_unexp_error;
750: fnd_msg_pub.count_and_get ( p_count => x_msg_count
751: , p_data => x_msg_data );
752: when fnd_api.g_exc_error then
753: x_return_status := fnd_api.g_ret_sts_unexp_error;
748: when fnd_api.g_exc_unexpected_error then
749: x_return_status := fnd_api.g_ret_sts_unexp_error;
750: fnd_msg_pub.count_and_get ( p_count => x_msg_count
751: , p_data => x_msg_data );
752: when fnd_api.g_exc_error then
753: x_return_status := fnd_api.g_ret_sts_unexp_error;
754: fnd_msg_pub.count_and_get ( p_count => x_msg_count
755: , p_data => x_msg_data );
756:
749: x_return_status := fnd_api.g_ret_sts_unexp_error;
750: fnd_msg_pub.count_and_get ( p_count => x_msg_count
751: , p_data => x_msg_data );
752: when fnd_api.g_exc_error then
753: x_return_status := fnd_api.g_ret_sts_unexp_error;
754: fnd_msg_pub.count_and_get ( p_count => x_msg_count
755: , p_data => x_msg_data );
756:
757: when G_EXC_INVALID_SLOT_DURATION then
754: fnd_msg_pub.count_and_get ( p_count => x_msg_count
755: , p_data => x_msg_data );
756:
757: when G_EXC_INVALID_SLOT_DURATION then
758: x_return_status := fnd_api.g_ret_sts_error ;
759: fnd_message.set_name('JTF','JTF_CAL_INVALID_DURATION');
760: fnd_msg_pub.add;
761: fnd_msg_pub.count_and_get ( p_count => x_msg_count
762: , p_data => x_msg_data );
760: fnd_msg_pub.add;
761: fnd_msg_pub.count_and_get ( p_count => x_msg_count
762: , p_data => x_msg_data );
763: when others then
764: x_return_status := fnd_api.g_ret_sts_unexp_error ;
765: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
766: fnd_message.set_token('ERROR_CODE',sqlcode);
767: fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
768: fnd_msg_pub.add;
775: -- ****************** Get Available Time **********************
776: --
777: PROCEDURE Get_Available_Time
778: ( p_api_version IN NUMBER,
779: p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
780: p_resource_id IN NUMBER,
781: p_resource_type IN VARCHAR2,
782: p_start_date IN DATE,
783: p_end_date IN DATE,
796: l_shift SHIFT_TBL_TYPE;
797:
798: BEGIN
799: -- Initialize API return status to success
800: x_return_status := FND_API.G_RET_STS_SUCCESS;
801:
802: -- Standard call to check for call compatibility.
803: IF NOT FND_API.Compatible_API_Call (l_api_version,
804: p_api_version,
799: -- Initialize API return status to success
800: x_return_status := FND_API.G_RET_STS_SUCCESS;
801:
802: -- Standard call to check for call compatibility.
803: IF NOT FND_API.Compatible_API_Call (l_api_version,
804: p_api_version,
805: l_api_name,
806: G_PKG_NAME)
807: THEN
804: p_api_version,
805: l_api_name,
806: G_PKG_NAME)
807: THEN
808: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809: END IF;
810:
811: -- Initialize message list if p_init_msg_list is set to TRUE.
812: IF FND_API.to_Boolean( p_init_msg_list ) THEN
808: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809: END IF;
810:
811: -- Initialize message list if p_init_msg_list is set to TRUE.
812: IF FND_API.to_Boolean( p_init_msg_list ) THEN
813: FND_MSG_PUB.initialize;
814: END IF;
815:
816: -- Call to check for required fields
836: x_msg_data => l_msg_data,
837: x_shift => x_shift
838: );
839:
840: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
841: THEN
842: x_return_status := l_return_status;
843: x_msg_count := l_msg_count;
844: x_msg_data := l_msg_data;
845: END IF;
846:
847: EXCEPTION
848: when g_exc_required_fields_null then
849: x_return_status := fnd_api.g_ret_sts_error ;
850: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
851: fnd_message.set_token('P_PARAMETER', l_parameters);
852: fnd_msg_pub.add;
853: fnd_msg_pub.count_and_get ( p_count => x_msg_count
852: fnd_msg_pub.add;
853: fnd_msg_pub.count_and_get ( p_count => x_msg_count
854: , p_data => x_msg_data );
855:
856: when fnd_api.g_exc_unexpected_error then
857: x_return_status := fnd_api.g_ret_sts_unexp_error;
858: fnd_msg_pub.count_and_get ( p_count => x_msg_count
859: , p_data => x_msg_data );
860: when fnd_api.g_exc_error then
853: fnd_msg_pub.count_and_get ( p_count => x_msg_count
854: , p_data => x_msg_data );
855:
856: when fnd_api.g_exc_unexpected_error then
857: x_return_status := fnd_api.g_ret_sts_unexp_error;
858: fnd_msg_pub.count_and_get ( p_count => x_msg_count
859: , p_data => x_msg_data );
860: when fnd_api.g_exc_error then
861: x_return_status := fnd_api.g_ret_sts_unexp_error;
856: when fnd_api.g_exc_unexpected_error then
857: x_return_status := fnd_api.g_ret_sts_unexp_error;
858: fnd_msg_pub.count_and_get ( p_count => x_msg_count
859: , p_data => x_msg_data );
860: when fnd_api.g_exc_error then
861: x_return_status := fnd_api.g_ret_sts_unexp_error;
862: fnd_msg_pub.count_and_get ( p_count => x_msg_count
863: , p_data => x_msg_data );
864:
857: x_return_status := fnd_api.g_ret_sts_unexp_error;
858: fnd_msg_pub.count_and_get ( p_count => x_msg_count
859: , p_data => x_msg_data );
860: when fnd_api.g_exc_error then
861: x_return_status := fnd_api.g_ret_sts_unexp_error;
862: fnd_msg_pub.count_and_get ( p_count => x_msg_count
863: , p_data => x_msg_data );
864:
865: when others then
862: fnd_msg_pub.count_and_get ( p_count => x_msg_count
863: , p_data => x_msg_data );
864:
865: when others then
866: x_return_status := fnd_api.g_ret_sts_unexp_error ;
867: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
868: fnd_message.set_token('ERROR_CODE',sqlcode);
869: fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
870: fnd_msg_pub.add;
876: -- ****************** Get Available Time Slot **********************
877: --
878: PROCEDURE Get_Available_Slot
879: (p_api_version IN NUMBER ,
880: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
881: p_resource_id IN NUMBER ,
882: p_resource_type IN VARCHAR2 ,
883: p_start_date_time IN DATE ,
884: p_end_date_time IN DATE ,
906: v_count number;
907:
908: BEGIN
909: -- Standard call to check for call compatibility.
910: IF NOT FND_API.Compatible_API_Call (l_api_version ,
911: p_api_version ,
912: l_api_name ,
913: G_PKG_NAME )
914: THEN
911: p_api_version ,
912: l_api_name ,
913: G_PKG_NAME )
914: THEN
915: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916: END IF;
917:
918: -- Initialize message list if p_init_msg_list is set to TRUE.
919: IF FND_API.to_Boolean( p_init_msg_list ) THEN
915: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916: END IF;
917:
918: -- Initialize message list if p_init_msg_list is set to TRUE.
919: IF FND_API.to_Boolean( p_init_msg_list ) THEN
920: FND_MSG_PUB.initialize;
921: END IF;
922:
923: -- Call to check for required fields
932: END IF;
933:
934:
935: -- Initialize API return status to success
936: x_return_status := FND_API.G_RET_STS_SUCCESS;
937:
938: Get_Available_Time_slot
939: ( p_api_version => 1.0,
940: p_resource_id => p_resource_id,
948: x_msg_data => l_msg_data,
949: x_shift => l_shift
950: );
951:
952: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
953: THEN
954: x_return_status := l_return_status;
955: x_msg_count := l_msg_count;
956: x_msg_data := l_msg_data;
966: END IF;
967: END IF;
968:
969: EXCEPTION
970: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
971: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
972: FND_MSG_PUB.Count_And_Get
973: ( p_count => x_msg_count,
974: p_data => x_msg_data
967: END IF;
968:
969: EXCEPTION
970: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
971: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
972: FND_MSG_PUB.Count_And_Get
973: ( p_count => x_msg_count,
974: p_data => x_msg_data
975: );
973: ( p_count => x_msg_count,
974: p_data => x_msg_data
975: );
976: WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
977: x_return_status := FND_API.G_RET_STS_ERROR ;
978: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
979: fnd_message.set_token('P_PARAMETER', l_parameters);
980: fnd_msg_pub.add;
981: FND_MSG_PUB.Count_And_Get
981: FND_MSG_PUB.Count_And_Get
982: (p_count => x_msg_count,
983: p_data => x_msg_data
984: );
985: when fnd_api.g_exc_error then
986: x_return_status := fnd_api.g_ret_sts_unexp_error;
987: fnd_msg_pub.count_and_get ( p_count => x_msg_count
988: , p_data => x_msg_data );
989: WHEN OTHERS THEN
982: (p_count => x_msg_count,
983: p_data => x_msg_data
984: );
985: when fnd_api.g_exc_error then
986: x_return_status := fnd_api.g_ret_sts_unexp_error;
987: fnd_msg_pub.count_and_get ( p_count => x_msg_count
988: , p_data => x_msg_data );
989: WHEN OTHERS THEN
990: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
986: x_return_status := fnd_api.g_ret_sts_unexp_error;
987: fnd_msg_pub.count_and_get ( p_count => x_msg_count
988: , p_data => x_msg_data );
989: WHEN OTHERS THEN
990: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
991: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
992: fnd_message.set_token('ERROR_CODE',SQLCODE);
993: fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
994: fnd_msg_pub.add;
1002: -- ************* Get Resource Shifts *******************
1003: --
1004: PROCEDURE get_resource_shifts
1005: ( p_api_version in number
1006: , p_init_msg_list in varchar2 default fnd_api.g_false
1007: , p_resource_id in number
1008: , p_resource_type in varchar2
1009: , p_start_date in date
1010: , p_end_date in date
1112:
1113: l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
1114: l_res_Timezone_id Number;
1115:
1116: l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR ;
1117:
1118: l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
1119:
1120: BEGIN
1119:
1120: BEGIN
1121:
1122: -- standard call to check for call compatibility.
1123: if not fnd_api.compatible_api_call
1124: ( l_api_version
1125: , p_api_version
1126: , l_api_name
1127: , g_pkg_name )
1125: , p_api_version
1126: , l_api_name
1127: , g_pkg_name )
1128: then
1129: raise fnd_api.g_exc_unexpected_error;
1130: end if;
1131:
1132: -- initialize message list if p_init_msg_list is set to true.
1133: if fnd_api.to_boolean ( p_init_msg_list )
1129: raise fnd_api.g_exc_unexpected_error;
1130: end if;
1131:
1132: -- initialize message list if p_init_msg_list is set to true.
1133: if fnd_api.to_boolean ( p_init_msg_list )
1134: then
1135: fnd_msg_pub.initialize;
1136: end if;
1137:
1161: NULL;
1162: END;
1163:
1164: -- initialize api return status to success
1165: x_return_status := fnd_api.g_ret_sts_success;
1166:
1167: l_shift_date := trunc(p_start_date);
1168: while l_shift_date <= p_end_date Loop
1169:
1182: -- 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'));
1183: and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
1184:
1185: -- Added for bug 3270116
1186: l_return_status := FND_API.G_RET_STS_SUCCESS;
1187:
1188: -- added for bug 1355824
1189: -- if condition added for bug 3270116 by ABRAINA
1190: IF Validate_Cal_Date(l_calendar_id, l_shift_date)
1438: end loop;
1439: end if; -- end of l_process
1440: exception
1441: when no_data_found then
1442: x_return_status := FND_API.G_RET_STS_ERROR ;
1443: -- Added for bug 3270116
1444: l_return_status := FND_API.G_RET_STS_ERROR ;
1445: -- end
1446: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
1440: exception
1441: when no_data_found then
1442: x_return_status := FND_API.G_RET_STS_ERROR ;
1443: -- Added for bug 3270116
1444: l_return_status := FND_API.G_RET_STS_ERROR ;
1445: -- end
1446: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
1447: fnd_message.set_token('P_CAL_NAME', l_calendar_name);
1448: fnd_msg_pub.add;
1454: end if; -- valid cal end if
1455:
1456: exception
1457: when no_data_found then
1458: x_return_status := FND_API.G_RET_STS_ERROR ;
1459: -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
1460: -- as for this exception we want to handle it.
1461: -- For a resource, even if a single shift is returned for a search window
1462: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1455:
1456: exception
1457: when no_data_found then
1458: x_return_status := FND_API.G_RET_STS_ERROR ;
1459: -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
1460: -- as for this exception we want to handle it.
1461: -- For a resource, even if a single shift is returned for a search window
1462: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1463: l_tbl.delete; -- to delete the record from TOR if no shift found
1476:
1477: -- see if shift constructs have been found
1478: if x_shift.count = 0
1479: then
1480: x_return_status := fnd_api.g_ret_sts_error ;
1481: -- Added for bug 3270116
1482: l_return_status := FND_API.G_RET_STS_ERROR ;
1483: -- end
1484: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
1478: if x_shift.count = 0
1479: then
1480: x_return_status := fnd_api.g_ret_sts_error ;
1481: -- Added for bug 3270116
1482: l_return_status := FND_API.G_RET_STS_ERROR ;
1483: -- end
1484: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
1485: fnd_msg_pub.add;
1486:
1498:
1499: -- Added for bug 3270116
1500: -- For a resource, even if a single shift is returned for any search window
1501: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1502: if l_return_status = FND_API.G_RET_STS_SUCCESS then
1503: x_return_status := FND_API.G_RET_STS_SUCCESS;
1504: else
1505: x_return_status := FND_API.G_RET_STS_ERROR ;
1506: end if;
1499: -- Added for bug 3270116
1500: -- For a resource, even if a single shift is returned for any search window
1501: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1502: if l_return_status = FND_API.G_RET_STS_SUCCESS then
1503: x_return_status := FND_API.G_RET_STS_SUCCESS;
1504: else
1505: x_return_status := FND_API.G_RET_STS_ERROR ;
1506: end if;
1507: -- end
1501: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
1502: if l_return_status = FND_API.G_RET_STS_SUCCESS then
1503: x_return_status := FND_API.G_RET_STS_SUCCESS;
1504: else
1505: x_return_status := FND_API.G_RET_STS_ERROR ;
1506: end if;
1507: -- end
1508: EXCEPTION
1509: when g_exc_required_fields_null then
1506: end if;
1507: -- end
1508: EXCEPTION
1509: when g_exc_required_fields_null then
1510: x_return_status := fnd_api.g_ret_sts_error ;
1511: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
1512: fnd_message.set_token('P_PARAMETER', l_parameters);
1513: fnd_msg_pub.add;
1514: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1513: fnd_msg_pub.add;
1514: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1515: , p_data => x_msg_data );
1516:
1517: when fnd_api.g_exc_unexpected_error then
1518: x_return_status := fnd_api.g_ret_sts_unexp_error;
1519: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1520: , p_data => x_msg_data );
1521: when fnd_api.g_exc_error then
1514: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1515: , p_data => x_msg_data );
1516:
1517: when fnd_api.g_exc_unexpected_error then
1518: x_return_status := fnd_api.g_ret_sts_unexp_error;
1519: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1520: , p_data => x_msg_data );
1521: when fnd_api.g_exc_error then
1522: x_return_status := fnd_api.g_ret_sts_unexp_error;
1517: when fnd_api.g_exc_unexpected_error then
1518: x_return_status := fnd_api.g_ret_sts_unexp_error;
1519: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1520: , p_data => x_msg_data );
1521: when fnd_api.g_exc_error then
1522: x_return_status := fnd_api.g_ret_sts_unexp_error;
1523: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1524: , p_data => x_msg_data );
1525:
1518: x_return_status := fnd_api.g_ret_sts_unexp_error;
1519: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1520: , p_data => x_msg_data );
1521: when fnd_api.g_exc_error then
1522: x_return_status := fnd_api.g_ret_sts_unexp_error;
1523: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1524: , p_data => x_msg_data );
1525:
1526: when others then
1523: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1524: , p_data => x_msg_data );
1525:
1526: when others then
1527: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1528: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
1529: fnd_message.set_token('ERROR_CODE',sqlcode);
1530: fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
1531: fnd_msg_pub.count_and_get ( p_count => x_msg_count
1538: --
1539:
1540: PROCEDURE get_resource_shifts
1541: ( p_api_version in number
1542: , p_init_msg_list in varchar2 default fnd_api.g_false
1543: , p_resource_id in number
1544: , p_resource_type in varchar2
1545: , p_start_date in date
1546: , p_end_date in date
1695:
1696: l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
1697: l_res_Timezone_id Number;
1698:
1699: l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR ;
1700:
1701: l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
1702:
1703: BEGIN
1701: l_resource_name jtf_task_resources_vl.resource_name%TYPE;-- bug # 2418561
1702:
1703: BEGIN
1704: -- standard call to check for call compatibility.
1705: if not fnd_api.compatible_api_call
1706: ( l_api_version
1707: , p_api_version
1708: , l_api_name
1709: , g_pkg_name )
1707: , p_api_version
1708: , l_api_name
1709: , g_pkg_name )
1710: then
1711: raise fnd_api.g_exc_unexpected_error;
1712: end if;
1713:
1714: -- initialize message list if p_init_msg_list is set to true.
1715: if fnd_api.to_boolean ( p_init_msg_list )
1711: raise fnd_api.g_exc_unexpected_error;
1712: end if;
1713:
1714: -- initialize message list if p_init_msg_list is set to true.
1715: if fnd_api.to_boolean ( p_init_msg_list )
1716: then
1717: fnd_msg_pub.initialize;
1718: end if;
1719:
1729: end if;
1730:
1731:
1732: -- initialize api return status to success
1733: x_return_status := fnd_api.g_ret_sts_success;
1734:
1735: -- This code is added to get resource name to be printed in error message.
1736: -- Added for Bug 4063687
1737: BEGIN
1779: -- 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'));
1780: and l_shift_date between trunc(a.start_date_time) and nvl(trunc(a.end_date_time),l_shift_date);
1781:
1782: -- Added for bug 3270116
1783: l_return_status := FND_API.G_RET_STS_SUCCESS;
1784:
1785: --added l_shift_date in valid_cal loop bug #1355824
1786: -- if condition added for bug 3270116 by ABRAINA
1787: IF Validate_Cal_Date(l_calendar_id, l_shift_date)
2158: end if; -- end of l_process
2159:
2160: exception
2161: when no_data_found then
2162: x_return_status := FND_API.G_RET_STS_ERROR ;
2163: -- Added for bug 3270116
2164: l_return_status := FND_API.G_RET_STS_ERROR ;
2165: -- end
2166: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
2160: exception
2161: when no_data_found then
2162: x_return_status := FND_API.G_RET_STS_ERROR ;
2163: -- Added for bug 3270116
2164: l_return_status := FND_API.G_RET_STS_ERROR ;
2165: -- end
2166: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
2167: fnd_message.set_token('P_CAL_NAME', l_calendar_name);
2168: fnd_msg_pub.add;
2173: end; --2
2174: end if; -- valid cal end if
2175: exception
2176: when no_data_found then
2177: x_return_status := FND_API.G_RET_STS_ERROR ;
2178: -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
2179: -- as for this exception we want to handle it.
2180: -- For a resource, even if a single shift is returned in the search window
2181: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2174: end if; -- valid cal end if
2175: exception
2176: when no_data_found then
2177: x_return_status := FND_API.G_RET_STS_ERROR ;
2178: -- No Need to set l_return_status to FND_API.G_RET_STS_ERROR
2179: -- as for this exception we want to handle it.
2180: -- For a resource, even if a single shift is returned in the search window
2181: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2182: l_tbl.delete; -- to delete the record from TOR if no shift found
2196:
2197: -- see if shift constructs have been found
2198: if x_shift.count = 0
2199: then
2200: x_return_status := fnd_api.g_ret_sts_error ;
2201: -- Added for bug 3270116
2202: l_return_status := FND_API.G_RET_STS_ERROR ;
2203: -- end
2204: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
2198: if x_shift.count = 0
2199: then
2200: x_return_status := fnd_api.g_ret_sts_error ;
2201: -- Added for bug 3270116
2202: l_return_status := FND_API.G_RET_STS_ERROR ;
2203: -- end
2204: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFT_CONSTR_FOUND');
2205: fnd_msg_pub.add;
2206:
2218:
2219: -- Added for bug 3270116
2220: -- For a resource, even if a single shift is returned for any search window
2221: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2222: if l_return_status = FND_API.G_RET_STS_SUCCESS then
2223: x_return_status := FND_API.G_RET_STS_SUCCESS;
2224: else
2225: x_return_status := FND_API.G_RET_STS_ERROR ;
2226: end if;
2219: -- Added for bug 3270116
2220: -- For a resource, even if a single shift is returned for any search window
2221: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2222: if l_return_status = FND_API.G_RET_STS_SUCCESS then
2223: x_return_status := FND_API.G_RET_STS_SUCCESS;
2224: else
2225: x_return_status := FND_API.G_RET_STS_ERROR ;
2226: end if;
2227: -- end
2221: -- it will return 'S'. This is as per requirement from Field service and Gantt guys.
2222: if l_return_status = FND_API.G_RET_STS_SUCCESS then
2223: x_return_status := FND_API.G_RET_STS_SUCCESS;
2224: else
2225: x_return_status := FND_API.G_RET_STS_ERROR ;
2226: end if;
2227: -- end
2228:
2229: EXCEPTION
2227: -- end
2228:
2229: EXCEPTION
2230: when g_exc_required_fields_null then
2231: x_return_status := fnd_api.g_ret_sts_error ;
2232: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2233: fnd_message.set_token('P_PARAMETER', l_parameters);
2234: fnd_msg_pub.add;
2235: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2234: fnd_msg_pub.add;
2235: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2236: , p_data => x_msg_data );
2237:
2238: when fnd_api.g_exc_unexpected_error then
2239: x_return_status := fnd_api.g_ret_sts_unexp_error;
2240: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2241: , p_data => x_msg_data );
2242: when fnd_api.g_exc_error then
2235: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2236: , p_data => x_msg_data );
2237:
2238: when fnd_api.g_exc_unexpected_error then
2239: x_return_status := fnd_api.g_ret_sts_unexp_error;
2240: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2241: , p_data => x_msg_data );
2242: when fnd_api.g_exc_error then
2243: x_return_status := fnd_api.g_ret_sts_unexp_error;
2238: when fnd_api.g_exc_unexpected_error then
2239: x_return_status := fnd_api.g_ret_sts_unexp_error;
2240: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2241: , p_data => x_msg_data );
2242: when fnd_api.g_exc_error then
2243: x_return_status := fnd_api.g_ret_sts_unexp_error;
2244: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2245: , p_data => x_msg_data );
2246: when others then
2239: x_return_status := fnd_api.g_ret_sts_unexp_error;
2240: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2241: , p_data => x_msg_data );
2242: when fnd_api.g_exc_error then
2243: x_return_status := fnd_api.g_ret_sts_unexp_error;
2244: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2245: , p_data => x_msg_data );
2246: when others then
2247: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2243: x_return_status := fnd_api.g_ret_sts_unexp_error;
2244: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2245: , p_data => x_msg_data );
2246: when others then
2247: x_return_status := fnd_api.g_ret_sts_unexp_error ;
2248: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2249: fnd_message.set_token('ERROR_CODE',sqlcode);
2250: fnd_message.set_token('ERROR_MESSAGE',sqlerrm);
2251: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2258:
2259: /**********************************************************************/
2260: PROCEDURE Is_Res_Available
2261: ( p_api_version IN NUMBER,
2262: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2263: p_resource_id IN NUMBER,
2264: p_resource_type IN VARCHAR2,
2265: p_start_date_time IN DATE,
2266: p_duration IN NUMBER,
2280: v_end_time date;
2281:
2282: BEGIN
2283: -- Standard call to check for call compatibility.
2284: IF NOT FND_API.Compatible_API_Call (l_api_version ,
2285: p_api_version ,
2286: l_api_name ,
2287: G_PKG_NAME )
2288: THEN
2285: p_api_version ,
2286: l_api_name ,
2287: G_PKG_NAME )
2288: THEN
2289: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2290: END IF;
2291:
2292: -- Initialize message list if p_init_msg_list is set to TRUE.
2293: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2289: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2290: END IF;
2291:
2292: -- Initialize message list if p_init_msg_list is set to TRUE.
2293: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2294: FND_MSG_PUB.initialize;
2295: END IF;
2296:
2297: -- Call to check for required fields
2304: RAISE G_EXC_REQUIRED_FIELDS_NULL;
2305: END IF;
2306:
2307: -- Initialize API return status to success
2308: x_return_status := FND_API.G_RET_STS_SUCCESS;
2309:
2310: Get_Available_Time
2311: ( p_api_version => 1.0,
2312: p_resource_id => p_resource_id,
2337: end loop;
2338: END IF;
2339:
2340: EXCEPTION
2341: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2342: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2343: FND_MSG_PUB.Count_And_Get
2344: ( p_count => x_msg_count,
2345: p_data => x_msg_data
2338: END IF;
2339:
2340: EXCEPTION
2341: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2342: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2343: FND_MSG_PUB.Count_And_Get
2344: ( p_count => x_msg_count,
2345: p_data => x_msg_data
2346: );
2344: ( p_count => x_msg_count,
2345: p_data => x_msg_data
2346: );
2347: WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
2348: x_return_status := FND_API.G_RET_STS_ERROR ;
2349: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2350: fnd_message.set_token('P_PARAMETER', l_parameters);
2351: fnd_msg_pub.add;
2352: FND_MSG_PUB.Count_And_Get
2353: (p_count => x_msg_count,
2354: p_data => x_msg_data
2355: );
2356: WHEN OTHERS THEN
2357: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2358: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2359: fnd_message.set_token('ERROR_CODE',SQLCODE);
2360: fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
2361: fnd_msg_pub.add;
2368: -- ********** Get Resource Schedule **************
2369: --
2370: PROCEDURE Get_Res_Schedule
2371: ( p_api_version IN NUMBER ,
2372: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2373: p_resource_id IN NUMBER ,
2374: p_resource_type IN VARCHAR2 ,
2375: p_start_date IN DATE ,
2376: p_end_date IN DATE ,
2542:
2543: BEGIN
2544:
2545: -- Standard call to check for call compatibility.
2546: IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2547: p_api_version ,
2548: l_api_name ,
2549: G_PKG_NAME )
2550: THEN
2547: p_api_version ,
2548: l_api_name ,
2549: G_PKG_NAME )
2550: THEN
2551: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2552: END IF;
2553: -- Initialize message list if p_init_msg_list is set to TRUE.
2554: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2555: FND_MSG_PUB.initialize;
2550: THEN
2551: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2552: END IF;
2553: -- Initialize message list if p_init_msg_list is set to TRUE.
2554: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2555: FND_MSG_PUB.initialize;
2556: END IF;
2557: -- Call to check for required fields
2558: IF not check_for_required_fields (p_resource_id =>p_resource_id,
2579: NULL;
2580: END;
2581:
2582: -- Initialize API return status to success
2583: x_return_status := FND_API.G_RET_STS_SUCCESS;
2584: --
2585: l_shift_date := trunc(p_start_date);
2586: x_shift.delete;
2587: While l_shift_date <= p_end_date Loop
2819: end if; -- end of l_process check
2820:
2821: exception
2822: when no_data_found then
2823: x_return_status := FND_API.G_RET_STS_ERROR ;
2824: fnd_message.set_name('JTF','JTF_CAL_NO_SHIFTS');
2825: fnd_message.set_token('P_CAL_NAME', l_calendar_name);
2826: fnd_msg_pub.add;
2827: FND_MSG_PUB.Count_And_Get
2831: end; --(2)
2832: end if; --(1)
2833: exception
2834: when no_data_found then
2835: x_return_status := FND_API.G_RET_STS_ERROR ;
2836: fnd_message.set_name('JTF','JTF_CAL_RES_NO_CAL');
2837: fnd_message.set_token('P_SHIFT_DATE', l_shift_date);
2838: fnd_message.set_token('P_RES_NAME', l_resource_name);
2839: fnd_msg_pub.add;
2846: end loop;
2847: --
2848: -- Update return status to Success if there is atleast one available time slot
2849: IF x_shift.count > 0 and x_return_status = 'E' THEN
2850: x_return_status := FND_API.G_RET_STS_SUCCESS;
2851: END IF;
2852:
2853: if x_shift.count > 0
2854: then
2856: sort_tab(x_shift);
2857:
2858: end if;
2859: EXCEPTION
2860: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2861: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2862: FND_MSG_PUB.Count_And_Get
2863: ( p_count => x_msg_count,
2864: p_data => x_msg_data
2857:
2858: end if;
2859: EXCEPTION
2860: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2861: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2862: FND_MSG_PUB.Count_And_Get
2863: ( p_count => x_msg_count,
2864: p_data => x_msg_data
2865: );
2863: ( p_count => x_msg_count,
2864: p_data => x_msg_data
2865: );
2866: WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
2867: x_return_status := FND_API.G_RET_STS_ERROR ;
2868: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2869: fnd_message.set_token('P_PARAMETER', l_parameters);
2870: fnd_msg_pub.add;
2871: FND_MSG_PUB.Count_And_Get
2871: FND_MSG_PUB.Count_And_Get
2872: (p_count => x_msg_count,
2873: p_data => x_msg_data
2874: );
2875: when fnd_api.g_exc_error then
2876: x_return_status := fnd_api.g_ret_sts_unexp_error;
2877: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2878: , p_data => x_msg_data );
2879: WHEN OTHERS THEN
2872: (p_count => x_msg_count,
2873: p_data => x_msg_data
2874: );
2875: when fnd_api.g_exc_error then
2876: x_return_status := fnd_api.g_ret_sts_unexp_error;
2877: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2878: , p_data => x_msg_data );
2879: WHEN OTHERS THEN
2880: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2876: x_return_status := fnd_api.g_ret_sts_unexp_error;
2877: fnd_msg_pub.count_and_get ( p_count => x_msg_count
2878: , p_data => x_msg_data );
2879: WHEN OTHERS THEN
2880: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2881: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
2882: fnd_message.set_token('ERROR_CODE',SQLCODE);
2883: fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
2884: fnd_msg_pub.add;
2891:
2892: /***********************************************************/
2893: Function get_g_false return varchar2 is
2894: begin
2895: return(fnd_api.g_false);
2896: end get_g_false;
2897:
2898:
2899: function get_g_miss_num return number is
2897:
2898:
2899: function get_g_miss_num return number is
2900: begin
2901: return(fnd_api.g_miss_num);
2902: end get_g_miss_num;
2903:
2904: function get_g_miss_char return varchar2 is
2905: begin
2902: end get_g_miss_num;
2903:
2904: function get_g_miss_char return varchar2 is
2905: begin
2906: return(fnd_api.g_miss_char);
2907: end get_g_miss_char;
2908:
2909: function get_g_miss_date return date is
2910: begin
2907: end get_g_miss_char;
2908:
2909: function get_g_miss_date return date is
2910: begin
2911: return(fnd_api.g_miss_date);
2912: end get_g_miss_date;
2913:
2914: --Bug# 1344222
2915: FUNCTION check_resource_status(p_resource_id IN NUMBER,p_resource_type IN VARCHAR2) RETURN BOOLEAN IS
2954:
2955: BEGIN
2956:
2957: -- Standard call to check for call compatibility.
2958: IF NOT FND_API.Compatible_API_Call (l_api_version ,
2959: p_api_version ,
2960: l_api_name ,
2961: G_PKG_NAME )
2962: THEN
2959: p_api_version ,
2960: l_api_name ,
2961: G_PKG_NAME )
2962: THEN
2963: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2964: END IF;
2965:
2966: -- Initialize message list if p_init_msg_list is set to TRUE.
2967: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2963: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2964: END IF;
2965:
2966: -- Initialize message list if p_init_msg_list is set to TRUE.
2967: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2968: FND_MSG_PUB.initialize;
2969: END IF;
2970:
2971: -- Initialize API return status to success
2968: FND_MSG_PUB.initialize;
2969: END IF;
2970:
2971: -- Initialize API return status to success
2972: x_return_status := FND_API.G_RET_STS_SUCCESS;
2973:
2974: HZ_TIMEZONE_PUB.Get_Time( l_API_VERSION
2975: , l_INIT_MSG_LIST
2976: , P_Resource_TZ_Id
2983:
2984: Return x_Server_time;
2985:
2986: EXCEPTION
2987: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2988: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2989: FND_MSG_PUB.Count_And_Get
2990: ( p_count => x_msg_count,
2991: p_data => x_msg_data
2984: Return x_Server_time;
2985:
2986: EXCEPTION
2987: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2988: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2989: FND_MSG_PUB.Count_And_Get
2990: ( p_count => x_msg_count,
2991: p_data => x_msg_data
2992: );
2990: ( p_count => x_msg_count,
2991: p_data => x_msg_data
2992: );
2993: WHEN G_EXC_REQUIRED_FIELDS_NULL THEN
2994: x_return_status := FND_API.G_RET_STS_ERROR ;
2995: fnd_message.set_name('JTF','JTF_CAL_REQUIRED_PARAMETERS');
2996: fnd_message.set_token('P_PARAMETER', l_parameters);
2997: fnd_msg_pub.add;
2998: FND_MSG_PUB.Count_And_Get
2999: (p_count => x_msg_count,
3000: p_data => x_msg_data
3001: );
3002: WHEN OTHERS THEN
3003: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3004: fnd_message.set_name('JTF','JTF_CAL_UNEXPECTED_ERROR');
3005: fnd_message.set_token('ERROR_CODE',SQLCODE);
3006: fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
3007: fnd_msg_pub.add;