1 PACKAGE BODY PA_Resources_Pkg AS
2 /* $Header: PARESOTB.pls 120.1.12010000.2 2008/12/17 11:42:25 jsundara ship $ */
3 -- Standard Table Handler procedures for PA_RESOURCES table
4 PROCEDURE Insert_row (
5 X_Row_Id IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 X_RESOURCE_ID IN NUMBER,
7 X_NAME IN VARCHAR2,
8 X_DESCRIPTION IN VARCHAR2,
9 X_RESOURCE_TYPE_ID IN NUMBER,
10 X_UNIT_OF_MEASURE IN VARCHAR2,
11 X_ROLLUP_QUANTITY_FLAG IN VARCHAR2,
12 X_START_DATE_ACTIVE IN DATE,
13 X_END_DATE_ACTIVE IN DATE,
14 X_TRACK_AS_LABOR_FLAG IN VARCHAR2,
15 X_LAST_UPDATE_DATE IN DATE,
16 X_LAST_UPDATED_BY IN NUMBER,
17 X_CREATION_DATE IN DATE,
18 X_CREATED_BY IN NUMBER,
19 X_LAST_UPDATE_LOGIN IN NUMBER,
20 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
21 X_ATTRIBUTE1 IN VARCHAR2)
22 IS
23 CURSOR RES_CUR IS Select Rowid from PA_RESOURCES
24 Where Resource_Id = X_Resource_Id;
25 BEGIN
26 Insert Into PA_RESOURCES (
27 RESOURCE_ID,
28 NAME,
29 DESCRIPTION,
30 RESOURCE_TYPE_ID,
31 UNIT_OF_MEASURE,
32 ROLLUP_QUANTITY_FLAG,
33 START_DATE_ACTIVE,
34 END_DATE_ACTIVE,
35 TRACK_AS_LABOR_FLAG,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_LOGIN,
41 ATTRIBUTE_CATEGORY,
42 ATTRIBUTE1)
43 Values (
44 X_RESOURCE_ID,
45 X_NAME,
46 X_DESCRIPTION,
47 X_RESOURCE_TYPE_ID,
48 X_UNIT_OF_MEASURE,
49 X_ROLLUP_QUANTITY_FLAG,
50 X_START_DATE_ACTIVE,
51 X_END_DATE_ACTIVE,
52 X_TRACK_AS_LABOR_FLAG,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATED_BY,
55 X_CREATION_DATE,
56 X_CREATED_BY,
57 X_LAST_UPDATE_LOGIN,
58 X_ATTRIBUTE_CATEGORY,
59 X_ATTRIBUTE1);
60 Open Res_Cur;
61 Fetch Res_Cur Into X_Row_Id;
62 If (Res_Cur%NOTFOUND) then
63 Close Res_Cur;
64 Raise NO_DATA_FOUND;
65 End If;
66 Close Res_Cur;
67 END Insert_Row;
68 Procedure Update_Row (
69 X_Row_Id IN VARCHAR2,
70 X_RESOURCE_ID IN NUMBER,
71 X_NAME IN VARCHAR2,
72 X_DESCRIPTION IN VARCHAR2,
73 X_RESOURCE_TYPE_ID IN NUMBER,
74 X_UNIT_OF_MEASURE IN VARCHAR2,
75 X_ROLLUP_QUANTITY_FLAG IN VARCHAR2,
76 X_START_DATE_ACTIVE IN DATE,
77 X_END_DATE_ACTIVE IN DATE,
78 X_TRACK_AS_LABOR_FLAG IN VARCHAR2,
79 X_LAST_UPDATE_DATE IN DATE,
80 X_LAST_UPDATED_BY IN NUMBER,
81 X_LAST_UPDATE_LOGIN IN NUMBER,
82 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
83 X_ATTRIBUTE1 IN VARCHAR2) IS
84 Begin
85 Update PA_RESOURCES
86 SET
87 NAME = X_Name,
88 DESCRIPTION = X_Description,
89 RESOURCE_TYPE_ID = X_Resource_Type_Id,
90 UNIT_OF_MEASURE = X_Unit_Of_Measure,
91 ROLLUP_QUANTITY_FLAG = X_Rollup_Quantity_Flag,
92 START_DATE_ACTIVE = X_Start_Date_Active,
93 END_DATE_ACTIVE = X_End_Date_Active,
94 TRACK_AS_LABOR_FLAG = X_Track_As_Labor_Flag,
95 LAST_UPDATE_DATE = X_Last_Update_Date,
96 LAST_UPDATED_BY = X_Last_Updated_By,
97 LAST_UPDATE_LOGIN = X_Last_Update_Login,
98 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
99 ATTRIBUTE1 = X_ATTRIBUTE1
100 Where RowId = X_Row_Id;
101 If SQL%NOTFOUND Then
102 Raise NO_DATA_FOUND;
103 End If;
104 End Update_Row;
105 Procedure Delete_Row (X_Row_Id In Varchar2) Is
106 Begin
107 Delete from PA_RESOURCES Where RowId = X_Row_Id;
108 If SQL%NOTFOUND Then
109 Raise NO_DATA_FOUND;
110 End If;
111 End Delete_Row;
112 Procedure Lock_Row (
113 X_Row_Id IN VARCHAR2,
114 X_RESOURCE_ID IN NUMBER,
115 X_NAME IN VARCHAR2,
116 X_DESCRIPTION IN VARCHAR2,
117 X_RESOURCE_TYPE_ID IN NUMBER,
118 X_UNIT_OF_MEASURE IN VARCHAR2,
119 X_ROLLUP_QUANTITY_FLAG IN VARCHAR2,
120 X_START_DATE_ACTIVE IN DATE,
121 X_END_DATE_ACTIVE IN DATE,
122 X_TRACK_AS_LABOR_FLAG IN VARCHAR2,
123 X_LAST_UPDATE_DATE IN DATE,
124 X_LAST_UPDATED_BY IN NUMBER,
125 X_LAST_UPDATE_LOGIN IN NUMBER,
126 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
127 X_ATTRIBUTE1 IN VARCHAR2) IS
128 CURSOR C Is
129 Select * From PA_RESOURCES WHERE ROWID = X_ROW_ID
130 For Update of RESOURCE_ID NOWAIT;
131 Recinfo C%ROWTYPE;
132 Begin
133 OPEN C;
134 FETCH C INTO Recinfo;
135 If (C%NOTFOUND) THEN
136 Close C;
137 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
138 APP_EXCEPTION.RAISE_EXCEPTION;
139 END If;
140 CLOSE C;
141 If (
142 (X_RESOURCE_ID = Recinfo.RESOURCE_ID ) AND
143 (X_NAME = RecInfo.Name ) AND
144 ((X_DESCRIPTION = RecInfo.Description) OR
145 (Recinfo.Description is Null)) AND
146 (X_RESOURCE_TYPE_ID = Recinfo.Resource_Type_Id) AND
147 ((X_UNIT_OF_MEASURE = Recinfo.Unit_Of_Measure) OR
148 (Recinfo.Unit_Of_Measure is Null ) ) AND
149 (X_ROLLUP_QUANTITY_FLAG = Recinfo.Rollup_Quantity_Flag) AND
150 (X_START_DATE_ACTIVE = Recinfo.Start_Date_Active) AND
151 ((X_END_DATE_ACTIVE =
152 Recinfo.End_Date_Active) OR (Recinfo.End_Date_Active is Null)) AND
153 (X_TRACK_AS_LABOR_FLAG = Recinfo.Track_As_Labor_Flag) AND
154 ((X_ATTRIBUTE_CATEGORY = Recinfo.Attribute_Category) OR
155 (Recinfo.Attribute_Category Is Null ))
156 AND
157 ((X_ATTRIBUTE1 = Recinfo.Attribute1) OR
158 (Recinfo.Attribute1 is Null ))
159 )
160 Then
161 Return;
162 Else
163 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
164 APP_EXCEPTION.RAISE_EXCEPTION;
165 END If;
166
167 End Lock_Row ;
168
169 /* Added function get_resource_name for bug 1299456 */
170 FUNCTION Get_Resource_Name(P_Resource_Id IN NUMBER,
171 P_resource_type_id IN NUMBER
172 ) RETURN VARCHAR2 IS
173 P_Resource_Type_Code VARCHAR2(30);
174 P_organization_name VARCHAR2(60);
175 P_job_name VARCHAR2(60);
176 P_employee_name VARCHAR2(60);
177
178 /* Bug Fix Code for Bug#2487415 UTF8. Changing the variable type to %TYPE. Modified the bug fix of
179 2178043 */
180 P_vendor_name po_vendors.vendor_name%TYPE;
181
182 P_Name VARCHAR2(80); -- Bug 2191972, incresed the size from 60 to 80.
183 /*Commented the code for bug# 4143659 as this is not required.
184 P_START_DATE_ACTIVE pa_resources.start_date_active%TYPE; */
185 l_uncateg_rl_name pa_resource_list_members.alias%TYPE; /* added the variable for bug 2738156 */
186 BEGIN
187 select resource_type_code
188 into P_Resource_Type_Code
189 from pa_resource_types
190 where resource_type_id = P_resource_type_id;
191
192 /*Added for bug 1807084 to get star_date_active */
193
194 /* Commented the code for bug# 4143659 as this is not required.
195 Select start_date_active
196 into p_start_date_active
197 from pa_resources
198 Where resource_id = P_resource_id; */
199
200 if P_Resource_Type_Code = 'ORGANIZATION' then
201 select substr(org.name,1,60)
202 into P_organization_name
203 from hr_all_organization_units org --For 1807084 hr_all_organization_units
204 ,pa_resource_txn_attributes prta
205 where prta.organization_id = org.organization_id
206 and prta.resource_id = P_resource_id;
207
208 RETURN P_organization_name;
209
210 elsif
211 P_Resource_Type_Code = 'JOB' then
212 select SUBSTR(pj.name,1,60)
213 into P_job_name
214 from per_jobs pj -- For 1807084, per_jobs replaces pa_jobs_res_v
215 ,pa_resource_txn_attributes prta
216 where prta.job_id = pj.job_id
217 and prta.resource_id = P_resource_id;
218
219 RETURN P_job_name;
220
221 elsif
222 P_Resource_Type_Code = 'EMPLOYEE' then
223 select substr(pe.full_name,1,60)
224 into P_employee_name
225 from per_all_people_f pe --For 1807084, per_all_people_f replaces pa_employees_res_v
226 ,pa_resource_txn_attributes prta
227 where prta.person_id = pe.person_id
228 and prta.resource_id = P_resource_id
229 /* Commented the below condition and added new and condition for bug# 4143659
230 and P_start_date_active between pe.effective_start_date and pe.effective_end_date */
231 and trunc(sysdate) between trunc(pe.effective_start_date) and trunc(pe.effective_end_date)
232 and (pe.employee_number is not NULL OR
233 pe.npw_number is not NULL);
234
235 return P_employee_name;
236
237 elsif
238 P_Resource_Type_Code = 'VENDOR' then
239 select pv.vendor_name -- Bug#2178043
240 into P_vendor_name
241 from po_vendors pv --For 1807084, po_vendors replaces pa_vendors_res_v
242 ,pa_resource_txn_attributes prta
243 where prta.vendor_id = pv.vendor_id
244 and prta.resource_id = P_resource_id;
245
246 return P_vendor_name;
247
248 elsif P_Resource_Type_Code = 'UNCATEGORIZED' then /* added this elsif condition for bug 2738156 */
249
250 select distinct m.alias /* bug 7615636 */
251 into l_uncateg_rl_name
252 from pa_resources r,
253 pa_resource_list_members m,
254 pa_resource_lists_all_bg rl,
255 pa_implementations i
256 where rl.uncategorized_flag = 'Y'
257 and rl.resource_list_id = m.resource_list_id
258 and m.resource_id = r.resource_id
259 and rl.business_group_id = i.business_group_id;
260
261 return l_uncateg_rl_name;
262 else
263 select substr(name,1,80) -- Bug 2191972 , replaced 60 with 80
264 into P_name
265 from pa_resources
266 where resource_id = P_resource_id;
267
268 return P_name;
269 end if;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 RAISE;
274 END Get_Resource_Name;
275
276 Function Get_Resource_List_Member_Name(p_resource_list_member_Id IN pa_resource_list_members.resource_list_member_id%TYPE)
277 RETURN VARCHAR2
278 IS
279 l_resource_id pa_resource_list_members.resource_id%TYPE;
280 l_resource_type_id pa_resource_list_members.resource_type_id%TYPE;
281 BEGIN
282
283 select resource_id, resource_type_id
284 into l_resource_id,l_resource_type_id
285 from pa_resource_list_members
286 where resource_list_member_id = p_resource_list_member_Id;
287
288 return Get_Resource_Name(l_resource_id, l_resource_type_id);
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 RAISE;
293 End Get_Resource_List_Member_Name;
294
295 END PA_Resources_Pkg;
[Home] [Help]