[Home] [Help]
62: WHERE a1.record_type = 'C'
63: and a1.resource_id = p_resource_id
64: and a1.percent >= l_avl_profile
65: and sysdate < (select min(resource_effective_start_date)
66: from pa_resources_denorm res
67: where a1.resource_id = res.resource_id);
68:
69:
70: BEGIN
209: -------------------------------------------------------------------------------+
210: --
211: -- PROCEDURE : Insert_resource_denorm
212: -- DESCRIPTION : This Procedure inserts the record 'p_resource_denorm_rec'
213: -- to the table 'PA_RESOURCES_DENORM' after some appropriate
214: -- validations.
215: --
216: --------------------------------------------------------------------------------+
217: PROCEDURE Insert_resource_denorm
220: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
221: x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
222: IS
223: l_resource_denorm_rec PA_RESOURCE_PVT.Resource_denorm_Rec_type;
224: l_resource_id pa_resources_denorm.resource_id%TYPE := NULL;
225: l_return_status VARCHAR2(1);
226: l_err_msg_code fnd_new_messages.message_name%TYPE;
227: l_msg_index_out NUMBER;
228: l_rows_exists VARCHAR2(1);
265: SELECT 'Y'
266: INTO l_rows_exists
267: FROM dual
268: WHERE EXISTS (SELECT 'Y'
269: FROM pa_resources_denorm
270: WHERE l_resource_denorm_rec.person_id = person_id
271: AND l_resource_denorm_rec.job_id = job_id);
272:
273: IF l_rows_exists = 'Y' THEN
297: --and sysdate between effective_start_date and effective_end_date;
298: --
299: -- insert the record to the table 'PA_RESOURCE_DENORM'
300: --
301: INSERT into pa_resources_denorm
302: (
303: PERSON_ID,
304: RESOURCE_ID,
305: RESOURCE_NAME,
447: -----------------------------------------------------------------------------------+
448: --
449: -- PROCEDURE : Update_resource_denorm
450: -- DESCRIPTION: This Function updates record(s) in the table
451: -- 'PA_RESOURCES_DENORM' after some appropriate validations.
452: --
453: -----------------------------------------------------------------------------------+
454: PROCEDURE Update_resource_denorm (
455: p_resource_denorm_old_rec IN Resource_denorm_Rec_type,
494: -------------------------------------------------------------------------
495: -- If resource_effective_start_date has been passed, update an individual
496: -- record after some appropriate validations.
497: -- Since the combination of person_id and resource_effective_start_date is
498: -- the primary key in the table 'PA_RESOURCES_DENORM', resource_effective
499: -- _start_date should not be null for individual record update.
500: -------------------------------------------------------------------------
501: IF l_resource_denorm_new_rec.resource_effective_start_date <> FND_API.G_MISS_DATE AND
502: l_resource_denorm_new_rec.resource_effective_start_date is not null THEN
603:
604: ---------------------------------------------------------------------------------------
605: --
606: -- PROCEDURE : update_single_res_denorm_rec
607: -- DESCRIPTION: This procedure updates single record in the table 'PA_RESOURCES_DENORM'.
608: --
609: ---------------------------------------------------------------------------------------
610: PROCEDURE update_single_res_denorm_rec(
611: p_resource_denorm_rec IN resource_denorm_rec_type,
629: RETURN;
630: END IF;
631:
632:
633: UPDATE pa_resources_denorm
634: SET
635: resource_type = DECODE(p_resource_denorm_rec.resource_type, FND_API.G_MISS_CHAR,
636: resource_type,p_resource_denorm_rec.resource_type),
637: resource_organization_id = DECODE(p_resource_denorm_rec.resource_organization_ID,
689: ------------------------------------------------------------------------------------
690: --
691: -- PROCEDURE : update_person_res_denorm_recs
692: -- DESCRIPTION: This procedure updates multiple records which are related to a
693: -- person in the table 'PA_RESOURCES_DENORM'. This procedure usually
694: -- is used to update resource_name.
695: --
696: ------------------------------------------------------------------------------------
697: PROCEDURE update_person_res_denorm_recs
721: p_new_resource_denorm_rec => p_resource_denorm_rec,
722: x_return_status => l_return_status);
723:
724:
725: UPDATE pa_resources_denorm
726: SET
727: resource_name = DECODE(p_resource_denorm_rec.resource_name, FND_API.G_MISS_CHAR,
728: resource_name,p_resource_denorm_rec.resource_name),
729: resource_country_code = DECODE(p_resource_denorm_rec.resource_country_code, FND_API.G_MISS_CHAR,
765: PROCEDURE syncronize_manager_name
766: (p_new_resource_denorm_rec IN resource_denorm_rec_type,
767: x_return_status OUT NOCOPY VARCHAR) --File.Sql.39 bug 4440895
768: IS
769: l_old_resource_name pa_resources_denorm.resource_name%TYPE;
770: l_new_resource_name pa_resources_denorm.resource_name%TYPE := p_new_resource_denorm_rec.resource_name;
771: l_name_changed VARCHAR2(1):='N'; -- 5336386
772: BEGIN
773: -- Initialize the return status to success
766: (p_new_resource_denorm_rec IN resource_denorm_rec_type,
767: x_return_status OUT NOCOPY VARCHAR) --File.Sql.39 bug 4440895
768: IS
769: l_old_resource_name pa_resources_denorm.resource_name%TYPE;
770: l_new_resource_name pa_resources_denorm.resource_name%TYPE := p_new_resource_denorm_rec.resource_name;
771: l_name_changed VARCHAR2(1):='N'; -- 5336386
772: BEGIN
773: -- Initialize the return status to success
774: x_return_status := FND_API.G_RET_STS_SUCCESS;
780:
781: -- 5336386 : Commented below select and added new
782: --SELECT DISTINCT resource_name
783: --INTO l_old_resource_name
784: --FROM pa_resources_denorm
785: --WHERE person_id=p_new_resource_denorm_rec.person_id;
786:
787: SELECT 'Y' INTO l_name_changed
788: FROM dual
787: SELECT 'Y' INTO l_name_changed
788: FROM dual
789: WHERE exists
790: (SELECT 'Y'
791: FROM pa_resources_denorm
792: WHERE person_id = p_new_resource_denorm_rec.person_id
793: AND resource_name <> l_new_resource_name) ;
794:
795:
798:
799: -- if the resource_name has been updated, we need to syncronize manager_name if he
800: -- is manager of someone.
801:
802: UPDATE pa_resources_denorm
803: SET manager_name = l_new_resource_name
804: WHERE manager_id = p_new_resource_denorm_rec.person_id;
805: --AND resource_effective_end_date > sysdate;
806: END IF;
821:
822: ------------------------------------------------------------------------------------
823: -- PROCEDURE : update_job_res_denorm_recs
824: -- DESCRIPTION: This procedure update multiple records which are related to a
825: -- job in the table 'PA_RESOURCES_DENORM'.
826: ------------------------------------------------------------------------------------
827: PROCEDURE update_job_res_denorm_recs
828: (p_resource_denorm_rec IN resource_denorm_rec_type,
829: p_start_rowid IN rowid default NULL,
835: -- Initialize the return status to success
836: x_return_status := FND_API.G_RET_STS_SUCCESS;
837:
838: -- fixed the bug 1559045 here
839: UPDATE pa_resources_denorm
840: SET
841: resource_job_level = DECODE(p_resource_denorm_rec.resource_job_level, FND_API.G_MISS_NUM,
842: resource_job_level,p_resource_denorm_rec.resource_job_level),
843: employee_flag = DECODE(p_resource_denorm_rec.employee_flag, FND_API.G_MISS_CHAR,
870:
871: ------------------------------------------------------------------------------------
872: --
873: -- PROCEDURE : delete_resource_denorm
874: -- DESCRIPTION: This procedure deletes a record in the table 'PA_RESOURCES_DENORM'.
875: --
876: ------------------------------------------------------------------------------------
877: PROCEDURE delete_resource_denorm
878: ( p_person_id IN pa_resources_denorm.person_id%type,
874: -- DESCRIPTION: This procedure deletes a record in the table 'PA_RESOURCES_DENORM'.
875: --
876: ------------------------------------------------------------------------------------
877: PROCEDURE delete_resource_denorm
878: ( p_person_id IN pa_resources_denorm.person_id%type,
879: p_res_effective_start_date IN pa_resources_denorm.resource_effective_start_date%type,
880: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
881: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
882: x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
875: --
876: ------------------------------------------------------------------------------------
877: PROCEDURE delete_resource_denorm
878: ( p_person_id IN pa_resources_denorm.person_id%type,
879: p_res_effective_start_date IN pa_resources_denorm.resource_effective_start_date%type,
880: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
881: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
882: x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
883: IS
880: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
881: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
882: x_msg_count OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
883: IS
884: -- l_manager_id pa_resources_denorm.manager_id%TYPE;
885: -- l_msg_index_out NUMBER;
886: -- l_return_status VARCHAR2(1);
887: -- l_err_msg_code fnd_new_messages.message_name%TYPE;
888:
890: -- Initialize the return status to success
891: x_return_status := FND_API.G_RET_STS_SUCCESS;
892:
893:
894: DELETE FROM PA_RESOURCES_DENORM
895: WHERE person_id = p_person_id
896: AND resource_effective_start_date = p_res_effective_start_date
897: AND resource_effective_end_date > trunc(sysdate);
898:
931: , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
932: , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
933: ) IS
934:
935: l_resource_country_code pa_resources_denorm.resource_country_code%TYPE;
936: l_resource_country pa_resources_denorm.resource_country%TYPE;
937: l_resource_region pa_resources_denorm.resource_region%TYPE;
938: l_resource_city pa_resources_denorm.resource_city%TYPE;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
932: , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
933: ) IS
934:
935: l_resource_country_code pa_resources_denorm.resource_country_code%TYPE;
936: l_resource_country pa_resources_denorm.resource_country%TYPE;
937: l_resource_region pa_resources_denorm.resource_region%TYPE;
938: l_resource_city pa_resources_denorm.resource_city%TYPE;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940: l_employee_flag pa_resources_denorm.employee_flag%TYPE;
933: ) IS
934:
935: l_resource_country_code pa_resources_denorm.resource_country_code%TYPE;
936: l_resource_country pa_resources_denorm.resource_country%TYPE;
937: l_resource_region pa_resources_denorm.resource_region%TYPE;
938: l_resource_city pa_resources_denorm.resource_city%TYPE;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940: l_employee_flag pa_resources_denorm.employee_flag%TYPE;
941: l_billable_flag pa_resources_denorm.billable_flag%TYPE;
934:
935: l_resource_country_code pa_resources_denorm.resource_country_code%TYPE;
936: l_resource_country pa_resources_denorm.resource_country%TYPE;
937: l_resource_region pa_resources_denorm.resource_region%TYPE;
938: l_resource_city pa_resources_denorm.resource_city%TYPE;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940: l_employee_flag pa_resources_denorm.employee_flag%TYPE;
941: l_billable_flag pa_resources_denorm.billable_flag%TYPE;
942: l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
935: l_resource_country_code pa_resources_denorm.resource_country_code%TYPE;
936: l_resource_country pa_resources_denorm.resource_country%TYPE;
937: l_resource_region pa_resources_denorm.resource_region%TYPE;
938: l_resource_city pa_resources_denorm.resource_city%TYPE;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940: l_employee_flag pa_resources_denorm.employee_flag%TYPE;
941: l_billable_flag pa_resources_denorm.billable_flag%TYPE;
942: l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
943: l_schedulable_flag pa_resources_denorm.schedulable_flag%TYPE;
936: l_resource_country pa_resources_denorm.resource_country%TYPE;
937: l_resource_region pa_resources_denorm.resource_region%TYPE;
938: l_resource_city pa_resources_denorm.resource_city%TYPE;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940: l_employee_flag pa_resources_denorm.employee_flag%TYPE;
941: l_billable_flag pa_resources_denorm.billable_flag%TYPE;
942: l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
943: l_schedulable_flag pa_resources_denorm.schedulable_flag%TYPE;
944: l_return_status VARCHAR2(1);
937: l_resource_region pa_resources_denorm.resource_region%TYPE;
938: l_resource_city pa_resources_denorm.resource_city%TYPE;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940: l_employee_flag pa_resources_denorm.employee_flag%TYPE;
941: l_billable_flag pa_resources_denorm.billable_flag%TYPE;
942: l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
943: l_schedulable_flag pa_resources_denorm.schedulable_flag%TYPE;
944: l_return_status VARCHAR2(1);
945: l_error_msg_code VARCHAR2(2000);
938: l_resource_city pa_resources_denorm.resource_city%TYPE;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940: l_employee_flag pa_resources_denorm.employee_flag%TYPE;
941: l_billable_flag pa_resources_denorm.billable_flag%TYPE;
942: l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
943: l_schedulable_flag pa_resources_denorm.schedulable_flag%TYPE;
944: l_return_status VARCHAR2(1);
945: l_error_msg_code VARCHAR2(2000);
946: l_msg_count NUMBER;
939: l_resource_job_level pa_resources_denorm.resource_job_level%TYPE;
940: l_employee_flag pa_resources_denorm.employee_flag%TYPE;
941: l_billable_flag pa_resources_denorm.billable_flag%TYPE;
942: l_utilization_flag pa_resources_denorm.utilization_flag%TYPE;
943: l_schedulable_flag pa_resources_denorm.schedulable_flag%TYPE;
944: l_return_status VARCHAR2(1);
945: l_error_msg_code VARCHAR2(2000);
946: l_msg_count NUMBER;
947: l_first_time VARCHAR2(1) := 'Y';
945: l_error_msg_code VARCHAR2(2000);
946: l_msg_count NUMBER;
947: l_first_time VARCHAR2(1) := 'Y';
948: l_null_location VARCHAR2(1) := 'N';
949: l_rec_start_date pa_resources_denorm.resource_effective_start_date%TYPE;
950: l_rec_end_date pa_resources_denorm.resource_effective_end_date%TYPE;
951:
952: CURSOR denormalized_locations (l_person_id IN NUMBER, l_asgn_start_date IN DATE, l_asgn_end_date IN DATE ) IS
953: SELECT date_from,
946: l_msg_count NUMBER;
947: l_first_time VARCHAR2(1) := 'Y';
948: l_null_location VARCHAR2(1) := 'N';
949: l_rec_start_date pa_resources_denorm.resource_effective_start_date%TYPE;
950: l_rec_end_date pa_resources_denorm.resource_effective_end_date%TYPE;
951:
952: CURSOR denormalized_locations (l_person_id IN NUMBER, l_asgn_start_date IN DATE, l_asgn_end_date IN DATE ) IS
953: SELECT date_from,
954: date_to
969: -- Initialize the return status to success
970: x_return_status := FND_API.G_RET_STS_SUCCESS;
971:
972:
973: -- The delete logic deletes all overlapping records in pa_resources_denorm
974: -- for the incoming assignment record
975: -- This logic also works with location denormalization changes.
976: DELETE FROM pa_resources_denorm
977: WHERE person_id = p_resource_source_id
972:
973: -- The delete logic deletes all overlapping records in pa_resources_denorm
974: -- for the incoming assignment record
975: -- This logic also works with location denormalization changes.
976: DELETE FROM pa_resources_denorm
977: WHERE person_id = p_resource_source_id
978: AND resource_effective_start_date <= p_assignment_end_date
979: AND resource_effective_end_date >= p_assignment_start_date
980: ;
1028: END IF;
1029:
1030: -- Location denormalization changes involve the following
1031: -- Code loops through all the different location records of a HR assignment
1032: -- and creates 1 record in pa_resources_denorm for every different location
1033: -- of a HR assignment
1034: OPEN denormalized_locations(p_resource_source_id, p_assignment_start_date, p_assignment_end_date);
1035:
1036: LOOP
1059: IF l_null_location = 'Y' THEN
1060:
1061: l_rec_start_date := p_assignment_start_date;
1062:
1063: INSERT INTO PA_RESOURCES_DENORM ( person_id
1064: , resource_id
1065: , resource_name
1066: , resource_type
1067: , resource_org_id
1168: x_return_status => l_return_status,
1169: x_error_message_code => l_error_msg_code);
1170:
1171: /* Bug 5689674: Added the below exception handling to throw an error without
1172: proceeding on inserting into pa_resources_denorm. This will show the
1173: error as an exception in the report output. */
1174: IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1175: IF l_error_msg_code IS NOT NULL THEN
1176: pa_utils.add_message
1180: RAISE FND_API.G_EXC_ERROR;
1181: END IF;
1182: /* End of changes for Bug 5689674 */
1183:
1184: INSERT INTO PA_RESOURCES_DENORM ( person_id
1185: , resource_id
1186: , resource_name
1187: , resource_type
1188: , resource_org_id
1319: -- FUNCTION : Get_Manager_Id
1320: -- DESCRIPTION: This function gets the manager_id for a given project. It returns
1321: -- null if there is no manager. Also sets the following global variables
1322: -- g_manager_name - manager name
1323: -- g_manager_resource_id - manager's resource_id from pa_resources_denorm
1324: -- This API is used specifically in views pa_project_open_assns_v
1325: -- and pa_project_open_assns_staff_v
1326: --
1327: ------------------------------------------------------------------------------------
1339: AND trunc(sysdate) between parties.start_date_active AND NVL(parties.end_date_active,trunc(sysdate) + 1);
1340:
1341: select resource_name
1342: into g_manager_name
1343: from pa_resources_denorm
1344: where person_id = l_party_id
1345: and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
1346:
1347: select resource_id
1345: and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
1346:
1347: select resource_id
1348: into g_manager_resource_id
1349: from pa_resources_denorm
1350: where person_id = l_party_id
1351: and trunc(sysdate) between resource_effective_start_date and resource_effective_end_date;
1352:
1353: g_project_id := p_project_id; /* Bug#2604495 Setting the Global variable's value */
2648: --earlier than min. resource start date
2649: --------------------------------------------------
2650: select min(RESOURCE_EFFECTIVE_START_DATE)
2651: into l_earliest_start_date
2652: from pa_resources_denorm
2653: where RESOURCE_ID = p_resource_id;
2654:
2655: update pa_res_availability
2656: set start_date = l_earliest_start_date
2750: BEGIN
2751:
2752: select min(resource_effective_start_date)
2753: into l_start_date
2754: from pa_resources_denorm
2755: where resource_id = p_resource_id;
2756:
2757: /*bug 3229929 modified min(resource_effective_end_date) to max(resource_effective_end_date) */
2758: select max(resource_effective_end_date)
2756:
2757: /*bug 3229929 modified min(resource_effective_end_date) to max(resource_effective_end_date) */
2758: select max(resource_effective_end_date)
2759: into l_end_date
2760: from pa_resources_denorm
2761: where resource_id = p_resource_id;
2762:
2763: EXCEPTION
2764: WHEN NO_DATA_FOUND THEN
2834: CURSOR resources IS
2835: select resource_id,
2836: min(resource_effective_start_date) start_date,
2837: max(resource_effective_end_date) end_date
2838: from pa_resources_denorm
2839: group by resource_id;
2840:
2841: CURSOR redundant_resources IS -- Added for bug 7316435
2842: select resource_id
2840:
2841: CURSOR redundant_resources IS -- Added for bug 7316435
2842: select resource_id
2843: from pa_res_availability
2844: where resource_id not in (Select resource_id from pa_resources_denorm)
2845: and resource_id <> -1;
2846:
2847: l_res_count Number; --Added for bug 4928773
2848: