1 Package Body Pa_Plan_RL_Formats_Pvt as
2 /* $Header: PARRFTVB.pls 120.1 2005/08/24 14:55:33 appldev noship $ */
3
4 /****************************************************
5 * Procedure : Create_Plan_RL_Format
6 * Description : This is a Pvt procedure which takes in
7 * parameters from
8 * Pa_Plan_RL_Formats_Pub.Create_Plan_RL_Format
9 * proc.
10 ****************************************************/
11 Procedure Create_Plan_RL_Format(
12 P_Res_List_Id IN NUMBER,
13 P_Res_Format_Id IN NUMBER,
14 X_Plan_RL_Format_Id OUT NOCOPY NUMBER,
15 X_Record_Version_Number OUT NOCOPY NUMBER,
16 X_Return_Status OUT NOCOPY VARCHAR2,
17 X_Msg_Count OUT NOCOPY NUMBER,
18 X_Msg_Data OUT NOCOPY VARCHAR2)
19
20 Is
21
22 /***************************************************
23 * Bug : 3473679
24 * Description : We are selecting the res_type_code as well
25 * In this cursor because, for Work_planned
26 * enabled flag being Yes, we need to be restrictive
27 * while creating Plan formats for certain
28 * res_type_code and not all.
29 ********************************************************/
30 Cursor c1 is
31 Select
32 b.res_type_code,a.Res_Type_Id, a.resource_class_id
33 From
34 Pa_Res_Formats_B a,pa_res_types_b b
35 Where
36 Res_Format_Id = P_Res_Format_Id
37 AND a.res_type_id = b.res_type_id;
38
39 CURSOR chk_job_format(P_Res_Format_Id IN NUMBER) IS
40 SELECT 'Y'
41 FROM Pa_Res_Formats_B fmt,
42 pa_res_types_b typ
43 WHERE fmt.Res_Format_Id = P_Res_Format_Id
44 AND fmt.res_type_id = typ.res_type_id
45 AND typ.res_type_code = 'JOB';
46
47 Cursor c2 (P_Res_Type_Id IN Number,
48 P_Res_Class_Id IN Number) is
49 Select 'Y'
50 From Pa_Res_Formats_b fmt
51 Where fmt.Res_Type_Id = P_Res_Type_Id
52 And fmt.Resource_Class_Id = P_Res_Class_Id
53 And exists (Select 'Y'
54 From Pa_Plan_RL_Formats prf
55 where prf.Resource_List_Id = P_Res_List_Id
56 and prf.Res_Format_Id = fmt.Res_Format_Id);
57
58 Cursor c3 (P_Resource_List_Id IN Number) Is
59 Select
60 Use_For_WP_Flag
61 From
62 pa_resource_lists_all_bg
63 Where
64 Resource_List_Id = P_Resource_List_Id;
65
66 CURSOR chk_format_exists (p_resource_list_id IN Number,
67 p_res_format_id IN Number) IS
68 SELECT 'Y'
69 FROM Pa_Plan_RL_Formats
70 WHERE Resource_List_Id = p_resource_list_id
71 AND Res_Format_Id = p_res_format_id;
72
73 l_Res_Type_Id Number := Null;
74 l_Res_Type_code Varchar2(30):= Null;
75 l_Res_Class_Id Number := Null;
76 l_Dummy_Flag Varchar2(1) := Null;
77 l_exists Varchar2(1) := 'N';
78 l_job_format Varchar2(1) := 'N';
79 l_jg_id Number := Null;
80 l_WP_Enabled_Flag Varchar2(1) := Null;
81 WP_ERROR Exception;
82 JOB_FMT_ERR Exception;
83 BAD_FORMAT_ID Exception;
84
85 Begin
86 /**************************************************************************
87 * Logic:
88 * The below logic applies only for the foll res_type_codes
89 * 'NAMED_PERSON','BOM_LABOR','BOM_EQUIPMENT' 'NON_LABOR_RESOURCE',
90 * 'INVENTORY_ITEM'
91 * Suppose this resource list has 3 formats associated to it already
92 * (formats 1, 2 and 3).
93 * Now the user is trying to add a fourth format, format 4, and used for
94 * WP flag = Y.
95 * The validation is that the res_type_id of format 4 (from pa_res_formats_b)
96 * cannot be the
97 * same as the res_type_id for each of formats 1, 2 and 3. So, first we
98 * have to get all the
99 * formats associated to the list (from pa_plan_rl_formats), and for each
100 * of them, we have
101 * to get the res_type_id from pa_res_formats_b, and compare to the
102 * res_type_id for the p_format_id we are trying to add.
103 * If they do, then raise an error message "PA_PLAN_RL_FORMAT_WP_ERR"
104 **************************************************************************/
105 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
106 X_Msg_Count := 0;
107 X_Msg_Data := Null;
108
109
110 Open c3(P_Resource_List_Id => P_Res_List_Id);
111 Fetch c3 Into l_WP_Enabled_Flag;
112 Close c3;
113
114 If l_WP_Enabled_Flag = 'Y' Then
115
116 Open c1;
117 Fetch c1 into l_res_type_code,l_Res_Type_Id, l_Res_Class_Id;
118 Close c1;
119
120 If l_Res_Type_Id is Not Null Then
121 /***************************************************
122 * Bug : 3473679
123 * Description : We need to fo the below checks
124 * only if the res_type code is any of
125 * the mentioned.
126 ********************************************************/
127 IF l_res_type_code IN
128 ('NAMED_PERSON','BOM_LABOR','BOM_EQUIPMENT',
129 'NON_LABOR_RESOURCE','INVENTORY_ITEM')
130 THEN
131 Open c2(P_Res_Type_Id => l_Res_Type_Id,
132 P_Res_Class_Id => l_Res_Class_Id);
133 Fetch c2 Into l_Dummy_Flag;
134
135 If c2%NotFound Then
136
137 Null;
138
139 Else
140
141 Raise WP_ERROR;
142
143 End If;
144 Close c2;
145 END IF;
146
147 End If;
148
149 End If; -- l_WP_Enabled_Flag = 'Y'
150
151 -- Add validation to prevent the adding of Job formats
152 -- to a planning resource list without Job Group specified
153 -- Bug 4496596
154 l_job_format := 'N';
155 l_jg_id := NULL;
156 OPEN chk_job_format(P_Res_Format_Id => P_Res_Format_Id);
157 FETCH chk_job_format INTO l_job_format;
158 CLOSE chk_job_format;
159
160 IF l_job_format = 'Y' THEN
161 BEGIN
162 SELECT job_group_id
163 INTO l_jg_id
164 FROM pa_resource_lists_all_bg
165 WHERE resource_list_id = p_res_list_id;
166
167 EXCEPTION WHEN NO_DATA_FOUND THEN
168 l_jg_id := NULL;
169 END;
170
171 IF l_jg_id IS NULL THEN
172 RAISE JOB_FMT_ERR;
173 END IF;
174 END IF;
175
176 l_exists := 'N';
177 Open chk_format_exists(P_Resource_List_Id => P_Res_List_Id,
178 P_Res_Format_Id => P_Res_Format_Id);
179 Fetch chk_format_exists Into l_exists;
180 Close chk_format_exists;
181
182 IF l_exists <> 'Y' THEN
183 Pa_Plan_RL_Formats_Pkg.Insert_Row(
184 P_Resource_List_Id => P_Res_List_id,
185 P_Res_Format_Id => P_Res_Format_Id,
186 P_Last_Update_Date => SysDate,
187 P_Last_Updated_By => Fnd_Global.User_Id,
188 P_Creation_Date => SysDate,
189 P_Created_By => Fnd_Global.User_Id,
190 P_Last_Update_Login => Fnd_Global.Login_Id,
191 X_Plan_RL_Format_Id => X_Plan_RL_Format_Id,
192 X_Record_Version_Number => X_Record_Version_Number);
193 END IF;
194
195 /************************************************
196 * Check the Commit flag. if it is true then Commit.
197 ***********************************************/
198 Exception
199 When BAD_FORMAT_ID Then
200 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
201 X_Msg_Count := 1;
202 X_Msg_Data := 'PA_PLN_RL_FORMAT_BAD_FMT_ID';
203 X_Plan_RL_Format_Id := Null;
204 X_Record_Version_Number := Null;
205 Pa_Utils.Add_Message
206 (P_App_Short_Name => 'PA',
207 P_Msg_Name => 'PA_PLN_RL_FORMAT_BAD_FMT_ID',
208 p_token1 => 'PLAN_RES',
209 p_value1 => NULL);
210
211 When WP_ERROR Then
212 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
213 X_Msg_Count := 1;
214 X_Msg_Data := 'PA_PLAN_RL_FORMAT_WP_ERR';
215 X_Plan_RL_Format_Id := Null;
216 X_Record_Version_Number := Null;
217 Close c2;
218 Pa_Utils.Add_Message
219 (P_App_Short_Name => 'PA',
220 P_Msg_Name => 'PA_PLAN_RL_FORMAT_WP_ERR');
221
222 When JOB_FMT_ERR Then
223 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
224 X_Msg_Count := 1;
225 X_Msg_Data := 'PA_PLAN_RL_FORMAT_JOB_ERR';
226 X_Plan_RL_Format_Id := Null;
227 X_Record_Version_Number := Null;
228 Pa_Utils.Add_Message
229 (P_App_Short_Name => 'PA',
230 P_Msg_Name => 'PA_PLAN_RL_FORMAT_JOB_ERR');
231 When NO_DATA_FOUND Then
232 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
233 X_Msg_Count := 1;
234 X_Msg_Data := 'PA_PLN_RL_FMT_NOT_CREATED';
235 X_Plan_RL_Format_Id := Null;
236 X_Record_Version_Number := Null;
237 Close c2;
238 Pa_Utils.Add_Message
239 (P_App_Short_Name => 'PA',
240 P_Msg_Name => 'PA_PLN_RL_FMT_NOT_CREATED');
241
242 When Others Then
243 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
244 X_Plan_RL_Format_Id := Null;
245 X_Record_Version_Number := Null;
246 Fnd_Msg_Pub.Add_Exc_Msg(
247 P_Pkg_Name => 'Pa_Plan_RL_Formats_Pub',
248 P_Procedure_Name => 'Create_Plan_RL_Format');
249
250 Raise;
251
252 End Create_Plan_RL_Format;
253 /**********************************/
254 /****************************************************
255 * Procedure : Delete_Plan_RL_Format
256 * Description : This is a Pvt procedure which takes in
257 * parameters from
258 * Pa_Plan_RL_Formats_Pub.Delete_Plan_RL_Format
259 * proc.
260 ****************************************************/
261 Procedure Delete_Plan_RL_Format (
262 P_Res_List_Id IN NUMBER DEFAULT NULL,
263 P_Res_Format_Id IN NUMBER DEFAULT NULL,
264 P_Plan_RL_Format_Id IN NUMBER DEFAULT NULL,
265 X_Return_Status OUT NOCOPY VARCHAR2,
266 X_Msg_Count OUT NOCOPY NUMBER,
267 X_Msg_Data OUT NOCOPY VARCHAR2)
268
269 Is
270
271 Cursor c1 (P_Resource_Format_Id IN Number,
272 P_Resource_List_Id IN Number) is
273 Select
274 'Y'
275 From
276 Dual
277 Where Exists (
278 Select
279 'Y'
280 From
281 Pa_Resource_List_Members
282 Where
283 Res_Format_Id = P_Resource_Format_Id
284 And Resource_List_Id = P_Resource_List_Id);
285
286 Cursor c2 is
287 Select
288 Res_Format_Id,
289 Resource_List_Id
290 From
291 Pa_Plan_RL_Formats
292 Where
293 Plan_RL_Format_Id = P_Plan_RL_Format_Id;
294
295 l_Dummy_Flag Varchar2(1) := 'N';
296 l_Res_Format_Id Number := Null;
297 l_Res_List_Id Number := Null;
298 DEL_ERROR Exception;
299 NULL_FORMAT_ID Exception;
300 NULL_LIST_ID Exception;
301 BAD_PLAN_RL_FORMAT_ID Exception;
302 FMTUSED_ERROR Exception;
303
304 Begin
305 -- Checks whether there are any planning resources for this format before it can be deleted.
306 -- Check from pa_resource_list_members where res_format_id = p_res_format_id.
307 -- You need to check if any exists, and if so, raise an error:
308
309 Fnd_Msg_Pub.Initialize;
310
311 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
312 X_Msg_Count := 0;
313 X_Msg_Data := Null;
314
315 If P_Res_Format_Id is Null and P_Plan_RL_Format_Id is Null Then
316
317 Raise NULL_FORMAT_ID;
318
319 End If;
320
321 If P_Res_List_Id is Null and P_Plan_RL_Format_Id is Null Then
322
323 Raise NULL_LIST_ID;
324
325 End If;
326
327 If P_Res_Format_Id is Null and P_Res_List_Id is Null Then
328
329
330 Open c2;
331 Fetch c2 Into l_Res_Format_Id, l_Res_List_Id;
332 Close c2;
333
334 If l_Res_Format_Id is Null Then
335
336 Raise BAD_PLAN_RL_FORMAT_ID;
337
338 End If;
339
340 Else
341
342 l_Res_Format_Id := P_Res_Format_Id;
343 l_Res_List_Id := P_Res_List_Id;
344
345 End If;
346
347 -- For bug 3747114
348 IF (pa_assignment_utils.Check_Res_Format_Used_For_TR(
349 p_res_format_id => l_Res_Format_Id,
350 p_resource_list_id => l_Res_List_Id)) = 'Y' Then
351
352 Raise FMTUSED_ERROR;
353
354 End If; --End of bug 3747114
355
356 Open c1(P_Resource_Format_Id => l_Res_Format_Id,
357 P_Resource_List_Id => l_Res_List_Id);
358 Fetch c1 Into l_Dummy_Flag;
359 Close c1;
360
361 If l_dummy_Flag = 'Y' Then
362
363 Raise DEL_ERROR;
364
365 Else
366
367 Pa_Plan_RL_Formats_Pkg.Delete_Row (
368 P_Res_List_Id => P_Res_List_Id,
369 P_Res_Format_Id => P_Res_Format_Id,
370 P_Plan_RL_Format_Id => P_Plan_RL_Format_Id);
371
372 End If;
373
374 EXCEPTION
375 When NULL_FORMAT_ID Then
376 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
377 X_Msg_Count := 1;
378 X_Msg_Data := 'PA_PLN_RL_FORMAT_NULL_FMT_ID';
379 Pa_Utils.Add_Message
380 (P_App_Short_Name => 'PA',
381 P_Msg_Name => 'PA_PLN_RL_FORMAT_NULL_FMT_ID');
382
383 When NULL_LIST_ID Then
384 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
385 X_Msg_Count := 1;
386 X_Msg_Data := 'PA_PLN_RL_FORMAT_NULL_LST_ID';
387 Pa_Utils.Add_Message
388 (P_App_Short_Name => 'PA',
389 P_Msg_Name => 'PA_PLN_RL_FORMAT_NULL_LST_ID');
390
391 When BAD_PLAN_RL_FORMAT_ID Then
392 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
393 X_Msg_Count := 1;
394 X_Msg_Data := 'PA_PLN_RL_FORMAT_BAD_FMT_ID';
395 Pa_Utils.Add_Message
396 (P_App_Short_Name => 'PA',
397 P_Msg_Name => 'PA_PLN_RL_FORMAT_BAD_FMT_ID',
398 p_token1 => 'PLAN_RES',
399 p_value1 => NULL);
400
401 When DEL_ERROR Then
402 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
403 X_Msg_Count := 1;
404 X_Msg_Data := 'PA_PLAN_RL_FORMAT_DEL_ERR';
405 Pa_Utils.Add_Message
406 (P_App_Short_Name => 'PA',
407 P_Msg_Name => 'PA_PLAN_RL_FORMAT_DEL_ERR');
408
409 When FMTUSED_ERROR Then
410 X_Return_Status := Fnd_Api.G_Ret_Sts_Error;
411 X_Msg_Count := 1;
412 X_Msg_Data := 'PA_PLAN_RL_FORMAT_USED_ERR';
413 Pa_Utils.Add_Message
414 (P_App_Short_Name => 'PA',
415 P_Msg_Name => 'PA_PLAN_RL_FORMAT_USED_ERR');
416
417 When Others Then
418 X_Return_Status := Fnd_Api.G_Ret_Sts_UnExp_Error;
419 Fnd_Msg_Pub.Add_Exc_Msg(
420 P_Pkg_Name => 'Pa_Plan_RL_Formats_Pvt',
421 P_Procedure_Name => 'Delete_Plan_RL_Format');
422
423 Raise;
424
425 End Delete_Plan_RL_Format;
426
427 END Pa_Plan_RL_Formats_Pvt ;
[Home] [Help]