1 PACKAGE BODY pa_hr_resource AS
2 /* $Header: PAHRRESB.pls 120.1 2005/08/19 16:33:51 mwasowic noship $ */
3 --
4 --
5 PROCEDURE check_person_reference (p_person_id IN number,
6 Error_Message OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
7 Reference_Exist OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
8 IS
9 reference_exists exception;
10 dummy1 varchar2(1);
11 r_id pa_resource_txn_attributes.resource_id%type; /* Added for Bug#2738741 */
12 l_ret_stat varchar2(1); /* these variables are added for bug#2738741 */
13 msg_cnt number;
14 msg_data varchar2(2000);
15 forecastitem_err exception;
16
17 cursor resource_txn( p_person_id number ) is
18 select resource_id /* Bug 2738741 - Changed from null to resource_id */
19 from PA_RESOURCE_TXN_ATTRIBUTES pa
20 where pa.person_id = P_PERSON_ID;
21
22 /* Bug#2738741 - Added this new cursor resource_list_ref for checking in pa_resource_list_members
23 which stores the members of a resource list */
24
25 CURSOR resource_list_ref(p_resource_id number) IS
26 SELECT 'x' FROM pa_resource_list_members
27 WHERE resource_id = p_resource_id;
28
29 -- Bug 4116995 - check if person used in RBS
30 CURSOR rbs_person_ref(p_person_id number) IS
31 SELECT 'x' FROM pa_rbs_elements
32 WHERE person_id = p_person_id;
33
34 BEGIN
35
36 -- Bug 4116995 - First check if person used in RBS
37 Error_Message := 'PA_HR_PER_RBS_REF';
38 OPEN rbs_person_ref(p_person_id);
39 FETCH rbs_person_ref INTO dummy1;
40 IF rbs_person_ref%found THEN
41 CLOSE rbs_person_ref;
42 raise reference_exists;
43 END IF;
44 CLOSE rbs_person_ref;
45
46
47 /* Bug#2738741 - Modified the code here to check in cursor pa_resource_list_members
48 and raise error if records in the cursor. If not delete records from pa_resources_denorm,
49 pa_resource_txn_attributes, pa_resources */
50
51 Error_Message := 'PA_HR_PER_RES_TXN_ATTR';
52 OPEN resource_txn(p_person_id);
53 FETCH resource_txn INTO r_id; /* Bug#2738741 - Commenting dummy1 */
54 IF resource_txn%found THEN
55 OPEN resource_list_ref(r_id);
56 FETCH resource_list_ref INTO dummy1;
57 IF resource_list_ref%found THEN
58 CLOSE resource_txn;
59 CLOSE resource_list_ref;
60 raise reference_exists;
61 END IF;
62 CLOSE resource_list_ref;
63
64 /* Bug#2738741 - Delete from resource tables and denorm table as no reference */
65
66 /* Bug#2738741 - Added code to check if prm is installed, if so delete from pa_resources_denorm
67 and called delete API for forecast items */
68
69 -- Bug 4092769 - Remove PJR license check.
70 -- IF (pa_install.is_prm_licensed = 'Y') THEN
71
72 PA_FORECASTITEM_PVT.Delete_FI(p_resource_id => r_id,
73 x_return_status => l_ret_stat,
74 x_msg_count => msg_cnt,
75 x_msg_data => msg_data);
76 IF (l_ret_stat <> FND_API.G_RET_STS_SUCCESS) THEN
77 Raise Forecastitem_err;
78 END IF;
79
80 DELETE FROM pa_resources_denorm WHERE resource_id = r_id;
81 -- END IF;
82
83 DELETE FROM pa_resource_txn_attributes where resource_id = r_id;
84
85 DELETE FROM pa_resources WHERE resource_id = r_id;
86
87 END IF;
88
89 CLOSE resource_txn;
90
91 Reference_Exist := 'N';
92 Error_Message := NULL;
93 EXCEPTION
94 WHEN reference_exists THEN
95 Reference_Exist := 'Y';
96 WHEN forecastitem_err THEN
97 raise;
98 WHEN others THEN
99 raise;
100 END check_person_reference;
101
102 PROCEDURE check_job_reference (p_job_id IN number,
103 Error_Message OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
104 Reference_Exist OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
105 IS
106 reference_exists exception;
107 dummy1 varchar2(1);
108
109 cursor resource_txn( p_job_id number ) is
110 select null
111 from PA_RESOURCE_TXN_ATTRIBUTES pa
112 where pa.job_id = P_JOB_ID;
113
114 cursor resource_map( p_job_id number ) is
115 select null
116 from PA_RESOURCE_MAPS pa
117 where pa.job_id = P_JOB_ID;
118
119 cursor chk_plan_res_rbs(p_job_id IN NUMBER) IS
120 SELECT 'Y'
121 FROM dual
122 WHERE exists (select 'Y' from pa_resource_list_members
123 where job_id = p_job_id
124 UNION
125 select 'Y' from pa_rbs_elements
126 where job_id = p_job_id);
127
128 BEGIN
129 /* Bug no.2432494: error message below was changed from PA_HR_JOB_RSRC_TRN_ATTR
130 to PA_HR_JOB_RSRC_TXN_ATTR */
131 Error_Message := 'PA_HR_JOB_RSRC_TXN_ATTR';
132 OPEN resource_txn(p_job_id);
133 FETCH resource_txn INTO dummy1;
134 IF resource_txn%found THEN
135 CLOSE resource_txn;
136 raise reference_exists;
137 END IF;
138 CLOSE resource_txn;
139
140 Error_Message := 'PA_HR_JOB_RES_MAP_DET';
141 OPEN resource_map(p_job_id);
142 FETCH resource_map INTO dummy1;
143 IF resource_map%found THEN
144 CLOSE resource_map;
145 raise reference_exists;
146 END IF;
147
148 Error_Message := 'PA_HR_JOB_RES_FDN_EXISTS';
149 OPEN chk_plan_res_rbs(p_job_id);
150 FETCH chk_plan_res_rbs INTO dummy1;
151 IF chk_plan_res_rbs%found THEN
152 CLOSE chk_plan_res_rbs;
153 raise reference_exists;
154 END IF;
155
156 Reference_Exist := 'N';
157 Error_Message := NULL;
158 EXCEPTION
159 WHEN reference_exists THEN
160 Reference_Exist := 'Y';
161 WHEN others THEN
162 raise;
163 END check_job_reference;
164
165 --
166 END pa_hr_resource;
[Home] [Help]