[Home] [Help]
PACKAGE BODY: APPS.PA_RES_LIST_ASSIGNMENTS
Source
1 PACKAGE BODY PA_RES_LIST_ASSIGNMENTS AS
2 /* $Header: PARLASMB.pls 120.2.12010000.2 2008/11/03 11:08:44 rballamu ship $ */
3
4 Procedure Create_Rl_Assgmt (X_Project_id In Number,
5 X_Resource_list_id In Number,
6 X_Resource_list_Assgmt_id Out NOCOPY Number, --File.Sql.39 bug 4440895
7 X_err_code IN Out NOCOPY Number, --File.Sql.39 bug 4440895
8 X_err_stage IN Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
9 x_err_stack IN Out NOCOPY Varchar2 ) Is --File.Sql.39 bug 4440895
10 W_Assgmt_Id Number := 0;
11 W_num Number := 0;
12 Old_stack varchar2(630);
13 W_End_Date Date;
14 W_User_id Number := 0;
15 W_Login_id Number := 0;
16 Cursor Proj_Cur is
17 Select Project_Id from Pa_Projects_all -- changed for bug 6743458
18 where Project_id = X_Project_Id;
19 Cursor Res_List_Cur is
20 Select End_Date_Active from PA_RESOURCE_LISTS
21 where Resource_list_Id = X_Resource_List_id;
22 Begin
23 X_Resource_list_Assgmt_id := 0;
24 W_User_id := To_Number(FND_PROFILE.VALUE('USER_ID'));
25 W_login_id := To_Number(FND_PROFILE.VALUE('LOGIN_ID'));
26 X_err_code := 0;
27 Old_Stack := X_Err_Stack;
28 X_err_stack := X_err_stack ||'->PA_RES_LIST_ASSIGNMENTS.Create_Rl_Asgmt';
29 x_err_stage := 'Select Project_id from Pa_Projects '||
30 To_Char(X_project_id);
31 Open Proj_Cur;
32 Fetch Proj_Cur into W_Num;
33 If Proj_Cur%NOTFOUND Then
34 x_err_code := 10;
35 x_err_stage := 'PA_RE_PROJ_NOT_FOUND';
36 Close Proj_Cur;
37 Return;
38 End If;
39 Close Proj_Cur;
40 x_err_stage := 'Select End_date_Active from Pa_Resource_lists '||
41 To_Char(X_resource_list_id);
42 Open Res_List_Cur;
43 Fetch Res_List_Cur into W_End_Date;
44 If Res_list_Cur%NOTFOUND Then
45 x_err_code := 11;
46 x_err_stage := 'PA_RE_RL_NOT_FOUND';
47 Close Res_list_Cur;
48 Return;
49 End If;
50 If W_End_Date is not Null Then
51 If Trunc(W_End_date) < Trunc(Sysdate) Then
52 x_err_code := 12;
53 x_err_stage := 'PA_RE_RL_INACTIVE';
54 Close Res_list_Cur;
55 Return;
56 End If;
57 End If;
58 Close Res_List_Cur;
59 x_err_stage := ' Get_Rl_assgmt ';
60 Get_Rl_Assgmt (X_Project_id,
61 X_Resource_list_id,
62 W_Assgmt_Id,
63 X_err_code,
64 X_err_stage,
65 x_err_stack);
66 If W_Assgmt_Id = 0 Then
67 x_err_stage := 'Select pa_resource_list_assignments_s.nextval';
68 Select Pa_Resource_list_Assignments_s.nextval into W_num
69 from Dual;
70 x_err_stage := 'Insert into Pa_Resource_list_assignments ';
71 Insert into Pa_Resource_list_Assignments
72 (RESOURCE_LIST_ASSIGNMENT_ID,RESOURCE_LIST_ID,PROJECT_ID,
73 RESOURCE_LIST_CHANGED_FLAG,LAST_UPDATED_BY,LAST_UPDATE_DATE,
74 CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
75 RESOURCE_LIST_ACCUMULATED_FLAG) Values
76 (W_Num,X_Resource_list_id,X_project_id,'N',Nvl(W_user_id,-1),
77 Trunc(Sysdate),Trunc(Sysdate),
78 Nvl(W_user_id ,-1),Nvl(W_login_id,-1),'N');
79 X_Resource_list_Assgmt_id := W_num;
80 Else
81 X_Resource_list_Assgmt_id := W_Assgmt_Id;
82 End If;
83 X_err_stack := Old_Stack;
84
85 Exception
86 When Others then
87 X_err_code := SQLCODE;
88 return;
89
90 End ;
91
92 Procedure Get_Rl_Assgmt (X_Project_id In Number,
93 X_Resource_list_id In Number,
94 X_Resource_list_Assgmt_id Out NOCOPY Number, --File.Sql.39 bug 4440895
95 X_err_code IN Out NOCOPY Number, --File.Sql.39 bug 4440895
96 X_err_stage IN Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
97 x_err_stack IN Out NOCOPY Varchar2 ) Is --File.Sql.39 bug 4440895
98 W_Num Number := 0;
99 Old_stack varchar2(630);
100 Cursor Res_List_Asgmt_Cur is
101 Select Resource_list_Assignment_Id from PA_RESOURCE_LIST_ASSIGNMENTS
102 where Resource_list_Id = X_Resource_List_id and
103 Project_id = X_Project_id;
104 Begin
105 X_err_code := 0;
106 Old_Stack := X_Err_Stack;
107 X_err_stack := X_err_stack ||'->PA_RES_LIST_ASSIGNMENTS.Get_Rl_Asgmt';
108 x_err_stage := ' Select Resource_List_Assignment_id from '||
109 ' PA_RESOURCE_LIST_ASSIGNMENTS '||
110 To_Char(X_Resource_List_Id);
111 Open Res_List_Asgmt_Cur;
112 Fetch Res_List_Asgmt_Cur into W_Num;
113 If Res_List_Asgmt_Cur%NOTFOUND Then
114 X_Resource_list_Assgmt_id := 0;
115 Else
116 X_Resource_list_Assgmt_id := W_num;
117
118 End If;
119 Close Res_List_Asgmt_Cur;
120 X_err_stack := Old_Stack;
121 Exception
122 When Others then
123 X_err_code := SQLCODE;
124 return;
125 End ;
126
127
128 Procedure Create_Rl_Uses (X_Project_id In Number,
129 X_Resource_list_Assgmt_id In Number,
130 X_Use_Code In Varchar2,
131 X_err_code IN Out NOCOPY Number, --File.Sql.39 bug 4440895
132 X_err_stage IN Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
133 x_err_stack IN Out NOCOPY Varchar2 ) Is --File.Sql.39 bug 4440895
134 W_Assgmt_Id Number := 0;
135 W_Asgmt_Res_list_id Number := 0;
136 W_usage_Res_list_id Number := 0;
137 W_project_id Number := 0;
138 W_Row_id Varchar2(19);
139 W_num Number := 0;
140 W_budget_Type_Yn Varchar2(1);
141 Old_stack varchar2(630);
142 W_User_id Number := 0;
143 W_Login_id Number := 0;
144 W_insert_Flag Char(1) := 'N';
145 W_Update_Flag Char(1) := 'N';
146 W_Found_Flag Char(1) := 'N';
147 X_row_id Varchar2(19);
148 Cursor Res_List_Asgmt_Cur is
149 Select Project_id,Resource_list_Id from
150 PA_RESOURCE_LIST_ASSIGNMENTS
151 Where Resource_list_Assignment_Id = X_Resource_list_Assgmt_id;
152 Cursor Res_List_Use_Code_Cur is
153 Select Budget_Type_Yn from PA_RESOURCE_LIST_USE_CODES_V where
154 List_Use_Code = X_Use_Code;
155 Cursor Res_list_uses_cur is
156 Select Row_id,Resource_List_Id
157 from Pa_Resource_list_uses_v where Project_id = X_Project_id and
158 Use_Code = X_Use_Code ;
159
160 Begin
161 W_User_id := To_Number(FND_PROFILE.VALUE('USER_ID'));
162 W_login_id := To_Number(FND_PROFILE.VALUE('LOGIN_ID'));
163 X_err_code := 0;
164 Old_Stack := X_Err_Stack;
165 X_err_stack := X_err_stack ||'->PA_RES_LIST_ASSIGNMENTS.Create_Rl_Uses';
166 x_err_stage := 'Select Project_id,Resource_list_Id '||
167 ' from Pa_Resource_list_Assignments '||
168 To_Char(X_Resource_list_Assgmt_id);
169 Open Res_List_Asgmt_Cur;
170 Fetch Res_List_Asgmt_Cur into W_Project_id,W_Asgmt_Res_list_id;
171 If Res_List_Asgmt_Cur%NOTFOUND Then
172 x_err_code := 10;
173 x_err_stage := 'PA_RE_ASSGMT_NOT_FOUND';
174 Close Res_List_Asgmt_Cur;
175 Return;
176 End If;
177 Close Res_List_Asgmt_Cur;
178 x_err_stage := 'Select Budget_Type_Yn from '||
179 ' PA_RESOURCE_LIST_USE_CODES_V '||
180 To_Char(W_Asgmt_Res_list_id);
181 Open Res_List_Use_Code_Cur;
182 Fetch Res_List_Use_Code_Cur into W_Budget_type_yn;
183 If Res_List_Use_Code_Cur%NOTFOUND Then
184 x_err_code := 11;
185 x_err_stage := 'PA_RE_USE_CODE_NOT_FOUND';
186 Close Res_List_Use_Code_Cur;
187 Return;
188 End If;
189 Close Res_List_Use_Code_Cur;
190 x_err_stage := ' Select Row_id,Resource_List_id from '||
191 ' PA_RESOURCE_LIST_ASSIGNMENTS '||
192 To_Char(X_Project_Id);
193 Open Res_list_uses_cur;
194 Fetch Res_list_uses_cur into W_Row_id,W_usage_Res_list_id;
195 If Res_list_uses_cur%NOTFOUND Then
196 Close Res_list_uses_cur;
197 W_insert_flag := 'Y';
198 GoTo Ins_para;
199 End If;
200 If W_usage_Res_list_id <> W_Asgmt_Res_list_id Then
201 If W_Budget_type_yn = 'Y' Then
202 x_err_stage := ' Update Pa_Resource_List_Uses ';
203 Update Pa_Resource_List_uses
204 Set Resource_list_Assignment_id =
205 X_Resource_list_Assgmt_id
206 Where RowId = W_Row_Id;
207 Close Res_list_uses_cur;
208 Return;
209 Else
210 W_Insert_Flag := 'Y';
211 End If;
212 Else
213 Close Res_list_uses_cur;
214 Return;
215 End If;
216 Close Res_list_uses_cur;
217 <<Ins_para>>
218 If W_Insert_Flag = 'Y' Then
219 x_err_stage := ' Insert into Pa_Resource_List_Uses ';
220 Insert into Pa_Resource_list_uses
221 (RESOURCE_LIST_ASSIGNMENT_ID,USE_CODE,DEFAULT_FLAG,
222 LAST_UPDATED_BY,LAST_UPDATE_DATE,
223 CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN) Values
224 (X_Resource_list_Assgmt_id,X_Use_Code,'N',
225 Nvl(W_user_id,-1), Trunc(Sysdate),
226 Trunc(Sysdate),Nvl(W_user_id,-1),Nvl(W_login_id,-1));
227 End If;
228 X_err_stack := Old_Stack;
229 Exception
230 When Others then
231 X_err_code := SQLCODE;
232 return;
233 End ;
234 Procedure Delete_Rl_Uses (X_Resource_list_Assgmt_id In Number,
235 X_Use_Code IN Varchar2,
236 X_err_code IN Out NOCOPY Number, --File.Sql.39 bug 4440895
237 X_err_stage IN Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
238 x_err_stack IN Out NOCOPY Varchar2 ) Is --File.Sql.39 bug 4440895
239
240 W_Assgmt_Id Number := 0;
241 Old_stack varchar2(630);
242 Cursor Res_List_Asgmt_Cur is
243 Select Resource_list_Assignment_Id from PA_RESOURCE_LIST_USES_V
244 where Resource_list_Assignment_id = X_Resource_list_Assgmt_id;
245
246 Begin
247 X_err_code := 0;
248 Old_Stack := X_Err_Stack;
249 X_err_stack := X_err_stack ||'->PA_RES_LIST_ASSIGNMENTS.Delete_Rl_Uses';
250 x_err_stage := ' Delete from PA_RESOURCE_LIST_USES';
251 Delete from PA_RESOURCE_LIST_USES Where
252 Resource_List_Assignment_id = X_Resource_list_Assgmt_id and
253 Use_Code = X_Use_Code;
254 If SQL%NOTFOUND Then
255 X_err_stage := 'PA_RE_ASSGMT_NOT_FOUND' ;
256 x_err_code := 10;
257 Return;
258 End If;
259 x_err_stage :=
260 ' Select Resource_list_Assignment_Id from PA_RESOURCE_LIST_USES_V '
261 || To_Char(X_Resource_list_Assgmt_id);
262 Open Res_List_Asgmt_Cur;
263 Fetch Res_List_Asgmt_Cur into W_Assgmt_Id;
264 If Res_List_Asgmt_Cur%NOTFOUND Then
265 x_err_stage := 'Delete from Pa_Resource_list_assignments ';
266 Delete from PA_RESOURCE_LIST_ASSIGNMENTS where
267 RESOURCE_LIST_ASSIGNMENT_ID = X_Resource_list_Assgmt_id;
268 End If;
269 Close Res_List_Asgmt_Cur;
270 X_err_stack := Old_Stack;
271 Exception
272 When Others then
273 X_err_code := SQLCODE;
274 return;
275 End ;
276
277 Procedure Delete_Rl_Assgmt(X_Resource_list_Assgmt_id In Number,
278 X_err_code IN Out NOCOPY Number, --File.Sql.39 bug 4440895
279 X_err_stage IN Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
280 x_err_stack IN Out NOCOPY Varchar2 ) Is --File.Sql.39 bug 4440895
281
282 W_Assgmt_Id Number := 0;
283 Old_stack varchar2(630);
284 Cursor Res_List_Asgmt_Cur is
285 Select Resource_list_Assignment_Id from PA_RESOURCE_LIST_USES
286 where Resource_list_Assignment_id = X_Resource_list_Assgmt_id;
287
288 Begin
289 X_err_code := 0;
290 Old_Stack := X_Err_Stack;
291 X_err_stack := X_err_stack ||'->PA_RES_LIST_ASSIGNMENTS.Delete_Rl_Asgmt';
292 x_err_stage :=
293 ' Select Resource_list_Assignment_Id from PA_RESOURCE_LIST_USES '
294 || To_Char(X_Resource_list_Assgmt_id);
295 Open Res_List_Asgmt_Cur;
296 Fetch Res_List_Asgmt_Cur into W_Assgmt_Id;
297 If Res_List_Asgmt_Cur%NOTFOUND Then
298 x_err_stage := 'Delete from Pa_Resource_list_assignments ';
299 Delete from PA_RESOURCE_LIST_ASSIGNMENTS where
300 RESOURCE_LIST_ASSIGNMENT_ID = X_Resource_list_Assgmt_id;
301 Else
302 x_err_code := 10;
303 x_err_stage := 'PA_RE_RL_USES_FOUND';
304 Close Res_List_Asgmt_Cur;
305 return;
306 End If;
307 Close Res_List_Asgmt_Cur;
308 X_err_stack := Old_Stack;
309 Exception
310 When Others then
311 X_err_code := SQLCODE;
312 return;
313 End ;
314 END;