[Home] [Help]
PACKAGE BODY: APPS.PA_TASK_TYPE_PVT
Source
1 PACKAGE BODY PA_TASK_TYPE_PVT AS
2 /*$Header: PATTPVTB.pls 120.1 2005/08/19 17:06:31 mwasowic noship $*/
3
4 g_module_name VARCHAR2(100) := 'PA_TASK_TYPE_PVT'; --Bug 3279978 FPM Development
5
6 INV_ARG_EXC EXCEPTION;
7
8 PROCEDURE create_task_type
9 (p_task_type IN pa_task_types.task_type%TYPE
10 ,p_start_date_active IN pa_task_types.start_date_active%TYPE
11 ,p_end_date_active IN pa_task_types.end_date_active%TYPE := NULL
12 ,p_description IN pa_task_types.description%TYPE := NULL
13 ,p_task_type_class_code IN pa_task_types.task_type_class_code%TYPE
14 ,p_initial_status_code IN pa_task_types.initial_status_code%TYPE := NULL
15 ,p_prog_entry_enable_flag IN pa_task_types.prog_entry_enable_flag%TYPE := NULL
16 ,p_prog_entry_req_flag IN pa_task_types.prog_entry_req_flag%TYPE := NULL
17 ,p_initial_progress_status_code IN pa_task_types.initial_progress_status_code%TYPE := NULL
18 ,p_task_prog_entry_page_id IN pa_task_types.task_progress_entry_page_id%TYPE := NULL
19 ,p_task_prog_entry_page_name IN pa_page_layouts.page_name%TYPE := NULL
20 ,p_wq_enable_flag IN pa_task_types.wq_enable_flag%TYPE := NULL
21 ,p_work_item_code IN pa_task_types.work_item_code%TYPE := NULL
22 ,p_uom_code IN pa_task_types.uom_code%TYPE := NULL
23 ,p_actual_wq_entry_code IN pa_task_types.actual_wq_entry_code%TYPE := NULL
24 ,p_percent_comp_enable_flag IN pa_task_types.percent_comp_enable_flag%TYPE := NULL
25 ,p_base_percent_comp_deriv_code IN pa_task_types.base_percent_comp_deriv_code%TYPE := NULL
26 ,p_task_weighting_deriv_code IN pa_task_types.task_weighting_deriv_code%TYPE := NULL
27 ,p_remain_effort_enable_flag IN pa_task_types.remain_effort_enable_flag%TYPE := NULL
28 ,p_attribute_category IN pa_task_types.attribute_category%TYPE := NULL
29 ,p_attribute1 IN pa_task_types.attribute1%TYPE := NULL
30 ,p_attribute2 IN pa_task_types.attribute2%TYPE := NULL
31 ,p_attribute3 IN pa_task_types.attribute3%TYPE := NULL
32 ,p_attribute4 IN pa_task_types.attribute4%TYPE := NULL
33 ,p_attribute5 IN pa_task_types.attribute5%TYPE := NULL
34 ,p_attribute6 IN pa_task_types.attribute6%TYPE := NULL
35 ,p_attribute7 IN pa_task_types.attribute7%TYPE := NULL
36 ,p_attribute8 IN pa_task_types.attribute8%TYPE := NULL
37 ,p_attribute9 IN pa_task_types.attribute9%TYPE := NULL
38 ,p_attribute10 IN pa_task_types.attribute10%TYPE := NULL
39 ,p_attribute11 IN pa_task_types.attribute11%TYPE := NULL
40 ,p_attribute12 IN pa_task_types.attribute12%TYPE := NULL
41 ,p_attribute13 IN pa_task_types.attribute13%TYPE := NULL
42 ,p_attribute14 IN pa_task_types.attribute14%TYPE := NULL
43 ,p_attribute15 IN pa_task_types.attribute15%TYPE := NULL
44 ,p_object_type IN pa_task_types.object_type%TYPE := 'PA_TASKS' -- 3279978 : Added Object Type and Progress Rollup Method
45 ,p_wf_item_type IN pa_task_types.wf_item_type%TYPE :=NULL
46 ,p_wf_process IN pa_task_types.wf_process%TYPE :=NULL
47 ,p_wf_lead_days IN pa_task_types.wf_start_lead_days%TYPE :=NULL
48 ,x_task_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
49 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
50 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
51 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
52 )
53 IS
54
55 l_msg_index_out NUMBER;
56
57 BEGIN
58
59 -- Initialize the return status to success
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61
62 --Log Message
63 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TASK_TYPE_PVT.Create_Task_Type.begin'
64 ,x_msg => 'Beginning of Create_Task_Type pvt'
65 ,x_log_level => 5);
66
67
68 PA_TASK_TYPE_PKG.insert_row
69 (p_task_type => p_task_type
70 ,p_start_date_active => p_start_date_active
71 ,p_end_date_active => p_end_date_active
72 ,p_description => p_description
73 ,p_task_type_class_code => p_task_type_class_code
74 ,p_initial_status_code => p_initial_status_code
75 ,p_prog_entry_enable_flag => p_prog_entry_enable_flag
76 ,p_prog_entry_req_flag => p_prog_entry_req_flag
77 ,p_initial_progress_status_code => p_initial_progress_status_code
78 ,p_task_prog_entry_page_id => p_task_prog_entry_page_id
79 ,p_task_prog_entry_page_name => p_task_prog_entry_page_name --this parameter was missing ,Hence inserted
80 ,p_wq_enable_flag => p_wq_enable_flag
81 ,p_work_item_code => p_work_item_code
82 ,p_uom_code => p_uom_code
83 ,p_actual_wq_entry_code => p_actual_wq_entry_code
84 ,p_percent_comp_enable_flag => p_percent_comp_enable_flag
85 ,p_base_percent_comp_deriv_code => p_base_percent_comp_deriv_code
86 ,p_task_weighting_deriv_code => p_task_weighting_deriv_code
87 ,p_remain_effort_enable_flag => p_remain_effort_enable_flag
88 ,p_attribute_category => p_attribute_category
89 ,p_attribute1 => p_attribute1
90 ,p_attribute2 => p_attribute2
91 ,p_attribute3 => p_attribute3
92 ,p_attribute4 => p_attribute4
93 ,p_attribute5 => p_attribute5
94 ,p_attribute6 => p_attribute6
95 ,p_attribute7 => p_attribute7
96 ,p_attribute8 => p_attribute8
97 ,p_attribute9 => p_attribute9
98 ,p_attribute10 => p_attribute10
99 ,p_attribute11 => p_attribute11
100 ,p_attribute12 => p_attribute12
101 ,p_attribute13 => p_attribute13
102 ,p_attribute14 => p_attribute14
103 ,p_attribute15 => p_attribute15
104 ,p_object_type => p_object_type -- 3279978 : Added Object Type and Progress Rollup Method
105 ,p_wf_item_type => p_wf_item_type
106 ,p_wf_process => p_wf_process
107 ,p_wf_lead_days => p_wf_lead_days
108 ,x_task_type_id => x_task_type_id
109 ,x_return_status => x_return_status
110 ,x_msg_count => x_msg_count
111 ,x_msg_data => x_msg_data);
112
113 EXCEPTION
114 WHEN OTHERS THEN
115 -- Set the excetption Message and the stack
116 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TASK_TYPE_PVT.Create_Task_Type'
117 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
118 --
119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
120 RAISE;
121
122 END Create_Task_Type;
123
124
125 PROCEDURE Update_Task_Type
126 ( p_task_type_id IN pa_task_types.task_type_id%TYPE
127 ,p_task_type IN pa_task_types.task_type%TYPE
128 ,p_start_date_active IN pa_task_types.start_date_active%TYPE
129 ,p_end_date_active IN pa_task_types.end_date_active%TYPE := NULL
130 ,p_description IN pa_task_types.description%TYPE := NULL
131 ,p_task_type_class_code IN pa_task_types.task_type_class_code%TYPE
132 ,p_initial_status_code IN pa_task_types.initial_status_code%TYPE := NULL
133 ,p_prog_entry_enable_flag IN pa_task_types.prog_entry_enable_flag%TYPE := NULL
134 ,p_prog_entry_req_flag IN pa_task_types.prog_entry_req_flag%TYPE := NULL
135 ,p_initial_progress_status_code IN pa_task_types.initial_progress_status_code%TYPE := NULL
136 ,p_task_prog_entry_page_id IN pa_task_types.task_progress_entry_page_id%TYPE := NULL
137 ,p_task_prog_entry_page_name IN pa_page_layouts.page_name%TYPE := NULL
138 ,p_wq_enable_flag IN pa_task_types.wq_enable_flag%TYPE := NULL
139 ,p_work_item_code IN pa_task_types.work_item_code%TYPE := NULL
140 ,p_uom_code IN pa_task_types.uom_code%TYPE := NULL
141 ,p_actual_wq_entry_code IN pa_task_types.actual_wq_entry_code%TYPE := NULL
142 ,p_percent_comp_enable_flag IN pa_task_types.percent_comp_enable_flag%TYPE := NULL
143 ,p_base_percent_comp_deriv_code IN pa_task_types.base_percent_comp_deriv_code%TYPE := NULL
144 ,p_task_weighting_deriv_code IN pa_task_types.task_weighting_deriv_code%TYPE := NULL
145 ,p_remain_effort_enable_flag IN pa_task_types.remain_effort_enable_flag%TYPE := NULL
146 ,p_attribute_category IN pa_task_types.attribute_category%TYPE := NULL
147 ,p_attribute1 IN pa_task_types.attribute1%TYPE := NULL
148 ,p_attribute2 IN pa_task_types.attribute2%TYPE := NULL
149 ,p_attribute3 IN pa_task_types.attribute3%TYPE := NULL
150 ,p_attribute4 IN pa_task_types.attribute4%TYPE := NULL
151 ,p_attribute5 IN pa_task_types.attribute5%TYPE := NULL
152 ,p_attribute6 IN pa_task_types.attribute6%TYPE := NULL
153 ,p_attribute7 IN pa_task_types.attribute7%TYPE := NULL
154 ,p_attribute8 IN pa_task_types.attribute8%TYPE := NULL
155 ,p_attribute9 IN pa_task_types.attribute9%TYPE := NULL
156 ,p_attribute10 IN pa_task_types.attribute10%TYPE := NULL
157 ,p_attribute11 IN pa_task_types.attribute11%TYPE := NULL
158 ,p_attribute12 IN pa_task_types.attribute12%TYPE := NULL
159 ,p_attribute13 IN pa_task_types.attribute13%TYPE := NULL
160 ,p_attribute14 IN pa_task_types.attribute14%TYPE := NULL
161 ,p_attribute15 IN pa_task_types.attribute15%TYPE := NULL
162 ,p_object_type IN pa_task_types.object_type%TYPE := 'PA_TASKS' -- 3279978 : Added Object Type and Progress Rollup Method
163 ,p_wf_item_type IN pa_task_types.wf_item_type%TYPE :=NULL
164 ,p_wf_process IN pa_task_types.wf_process%TYPE :=NULL
165 ,p_wf_lead_days IN pa_task_types.wf_start_lead_days%TYPE :=NULL
166 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
167 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
168 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
169 )
170
171 IS
172
173 l_msg_index_out NUMBER;
174
175 BEGIN
176
177 -- Initialize the return status to success
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 --Log Message
181 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TASK_TYPES_PVT.Update_Task_Type.begin'
182 ,x_msg => 'Beginning of Update_Task_Type pvt'
183 ,x_log_level => 5);
184
185
186 PA_TASK_TYPE_PKG.update_row
187 (p_task_type_id => p_task_type_id
188 ,p_task_type => p_task_type
189 ,p_start_date_active => p_start_date_active
190 ,p_end_date_active => p_end_date_active
191 ,p_description => p_description
192 ,p_task_type_class_code => p_task_type_class_code
193 ,p_initial_status_code => p_initial_status_code
194 ,p_prog_entry_enable_flag => p_prog_entry_enable_flag
195 ,p_prog_entry_req_flag => p_prog_entry_req_flag
196 ,p_initial_progress_status_code => p_initial_progress_status_code
197 ,p_task_prog_entry_page_id => p_task_prog_entry_page_id
198 ,p_wq_enable_flag => p_wq_enable_flag
199 ,p_work_item_code => p_work_item_code
200 ,p_uom_code => p_uom_code
201 ,p_actual_wq_entry_code => p_actual_wq_entry_code
202 ,p_percent_comp_enable_flag => p_percent_comp_enable_flag
203 ,p_base_percent_comp_deriv_code => p_base_percent_comp_deriv_code
204 ,p_task_weighting_deriv_code => p_task_weighting_deriv_code
205 ,p_remain_effort_enable_flag => p_remain_effort_enable_flag
206 ,p_attribute_category => p_attribute_category
207 ,p_attribute1 => p_attribute1
208 ,p_attribute2 => p_attribute2
209 ,p_attribute3 => p_attribute3
210 ,p_attribute4 => p_attribute4
211 ,p_attribute5 => p_attribute5
212 ,p_attribute6 => p_attribute6
213 ,p_attribute7 => p_attribute7
214 ,p_attribute8 => p_attribute8
215 ,p_attribute9 => p_attribute9
216 ,p_attribute10 => p_attribute10
217 ,p_attribute11 => p_attribute11
218 ,p_attribute12 => p_attribute12
219 ,p_attribute13 => p_attribute13
220 ,p_attribute14 => p_attribute14
221 ,p_attribute15 => p_attribute15
222 ,p_object_type => p_object_type -- 3279978 : Added Object Type and Progress Rollup Method
223 ,p_wf_item_type => p_wf_item_type
224 ,p_wf_process => p_wf_process
225 ,p_wf_lead_days => p_wf_lead_days
226 ,x_return_status => x_return_status
227 ,x_msg_count => x_msg_count
228 ,x_msg_data => x_msg_data);
229
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 -- Set the excetption Message and the stack
234 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TASK_TYPE_PVT.Update_Task_Type'
235 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
236 --
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
238 RAISE;
239
240 END Update_Task_Type;
241
242
243 PROCEDURE Delete_Task_Type
244 (p_Task_Type_id IN pa_task_types.Task_Type_id%TYPE := NULL
245 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
246 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
247 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
248 )
249 IS
250
251 l_msg_index_out NUMBER;
252
253 BEGIN
254
255 -- Initialize the return status to success
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257
258 --Log Message
259 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_task_types_PVT.Delete_Task_Type.begin'
260 ,x_msg => 'Beginning of Delete_Task_Type pvt'
261 ,x_log_level => 5);
262
263
264 PA_TASK_TYPE_PKG.delete_row
265 (p_task_type_id => p_task_type_id
266 ,x_return_status => x_return_status
267 ,x_msg_count => x_msg_count
268 ,x_msg_data => x_msg_data);
269
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 -- Set the excetption Message and the stack
274 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TASK_TYPE_PVT.Delete_Task_Type'
275 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
277 RAISE;
278
279 END Delete_Task_Type;
280
281 -- Procedure : CREATE_DELIVERABLE_TYPE
282 -- Type : private Procedure
283 -- Purpose : This is the private API used to create the deliverable type .
284 -- Note : If the deliverable type name is unique and date range is valid then
285 -- this API places call to the table handler insert_row in PA_TASK_TYPES_PKG
286 -- Assumptions : None
287 -- List of parameters other than standard IN and OUT parameters
288 -- Parameters Type Null? Default Value Description and Purpose
289 -- --------------------------- ------------------------------------------ ------ -------------- ----------------------------
290 --P_deliverable_type_name PA_TASK_TYPES.TASK_TYPE%TYPE N Deliverable Type Name
291 --P_prog_entry_enable_flag PA_TASK_TYPES.PROG_ENTRY_ENABLE_FLAG%TYPE Y 'N' Progress Entry Flag
292 --P_initial_deliverable_status_code PA_TASK_TYPES.INITIAL_STATUS_CODE%TYPE N 'DLVR_NOT_STARTED'Initial Deliverable Status
293 --P_deliverable_type_class_code PA_TASK_TYPES.TASK_TYPE_CLASS_CODE%TYPE Y 'ITEM' Deliverable Type Class
294 --P_enable_deliverable_actions PA_TASK_TYPES.ENABLE_DLVR_ACTIONS%TYPE Y 'N' Enable Deliverable Action
295 --P_effective_from PA_TASK_TYPES.START_DATE_ACTIVE%TYPE N Effective from date
296 --p_effective_to PA_TASK_TYPES. END_DATE_ACTIVE %TYPE Y NULL Effective to date
297 --P_description PA_TASK_TYPES.DESCRIPTION%TYPE Y NULL Description
298 --P_deliverable_type_id PA_TASK_TYPES.TASK_TYPE_ID%TPE Y NULL
299
300
301 PROCEDURE CREATE_DELIVERABLE_TYPE
302 (p_api_version IN NUMBER := 1.0
303 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
304 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
305 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
306 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
307 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
308 ,p_debug_mode IN VARCHAR2 := 'N'
309 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
310 ,p_deliverable_type_name IN PA_TASK_TYPES.TASK_TYPE%TYPE
311 ,p_prog_entry_enable_flag IN PA_TASK_TYPES.PROG_ENTRY_ENABLE_FLAG%TYPE := 'N'
312 ,p_initial_deliverable_status IN PA_TASK_TYPES.INITIAL_STATUS_CODE%TYPE := 'DLVR_NOT_STARTED'
313 ,p_deliverable_type_class IN PA_TASK_TYPES.TASK_TYPE_CLASS_CODE%TYPE := 'ITEM'
314 ,p_enable_dlvr_actions_flag IN PA_TASK_TYPES.ENABLE_DLVR_ACTIONS_FLAG%TYPE := 'N'
315 ,p_effective_from IN PA_TASK_TYPES.START_DATE_ACTIVE%TYPE
316 ,p_effective_to IN PA_TASK_TYPES. END_DATE_ACTIVE %TYPE := NULL
317 ,p_description IN PA_TASK_TYPES.DESCRIPTION%TYPE := NULL
318 ,p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE := NULL
319 ,p_record_version_number IN PA_TASK_TYPES.RECORD_VERSION_NUMBER%TYPE := 1
320 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
321 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
322 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
323 )
324 IS
325 l_msg_index_out NUMBER;
326 l_debug_mode VARCHAR2(1);
327
328 l_return_status VARCHAR2(1);
329 l_msg_count NUMBER;
330 l_data VARCHAR2(2000);
331 l_msg_data VARCHAR2(2000);
332 l_task_type_id_dummy PA_TASK_TYPES.TASK_TYPE_ID%TYPE ;
333
334 l_debug_level2 CONSTANT NUMBER := 2;
335 l_debug_level3 CONSTANT NUMBER := 3;
336 l_debug_level4 CONSTANT NUMBER := 4;
337 l_debug_level5 CONSTANT NUMBER := 5;
338
339 BEGIN
340 -- Initialize the return status to success
341 x_return_status := FND_API.G_RET_STS_SUCCESS;
342
343 --Define the savepoint
344 IF (p_commit = FND_API.G_TRUE)
345 THEN
346 SAVEPOINT CREATE_DELIVERABLE_TYPE;
347 END IF;
348
349 --Log message
350 l_debug_mode :=p_debug_mode;
351
352 IF l_debug_mode = 'Y'
353 THEN
354 PA_DEBUG.set_curr_function( p_function => 'CREATE_DELIVERABLE_TYPE',
355 p_debug_mode => l_debug_mode );
356 END IF;
357
358 IF l_debug_mode = 'Y'
359 THEN
360 PA_DEBUG.g_err_stage:= 'CREATE_DELIVERABLE_TYPE : Printing Input parameters';
361 PA_DEBUG.WRITE(g_module_name,Pa_Debug.g_err_stage,
362 l_debug_level3);
363 PA_DEBUG.WRITE(g_module_name,'p_deliverable_type_name'||':'||p_deliverable_type_name,
364 l_debug_level3);
365 PA_DEBUG.WRITE(g_module_name,'p_prog_entry_enable_flag'||':'||p_prog_entry_enable_flag,
366 l_debug_level3);
367 PA_DEBUG.WRITE(g_module_name,'p_initial_deliverable_status'||':'||p_initial_deliverable_status,
368 l_debug_level3);
369 PA_DEBUG.WRITE(g_module_name,'p_deliverable_type_class'||':'||p_deliverable_type_class,
370 l_debug_level3);
371 PA_DEBUG.WRITE(g_module_name,'p_enable_dlvr_actions_flag'||':'||p_enable_dlvr_actions_flag,
372 l_debug_level3);
373 PA_DEBUG.WRITE(g_module_name,'p_effective_from'||':'||p_effective_from,
374 l_debug_level3);
375 PA_DEBUG.WRITE(g_module_name,'p_effective_to'||':'||p_effective_to,
376 l_debug_level3);
377 PA_DEBUG.WRITE(g_module_name,'p_description'||':'||p_description,
378 l_debug_level3);
379 PA_DEBUG.WRITE(g_module_name,'p_deliverable_type_id'||':'||p_deliverable_type_id,
380 l_debug_level3);
381 END IF;
382
383 -- Initialize the Message Stack
384 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
385 FND_MSG_PUB.initialize;
386 END IF;
387
388 --Check whether any of p_deliverable_type_name,start date is null
389 --If it is null,then raise Invalid parameter exception
390 IF (p_deliverable_type_name IS NULL) OR (p_effective_from IS NULL)THEN
391 RAISE INV_ARG_EXC;
392 END IF;
393
394 --Check if Deliverable Type name is unique
395 l_return_status := PA_DELIVERABLE_UTILS.IS_DLV_TYPE_NAME_UNIQUE (p_deliverable_type_name);
396 IF(l_return_status = 'N')
397 THEN
398 PA_UTILS.ADD_MESSAGE
399 (p_app_short_name => 'PA',
400 p_msg_name => 'PA_DLV_TYPE_EXISTS');
401 x_return_status :=FND_API.G_RET_STS_ERROR;
402 END IF;
403
404 PA_DEBUG.WRITE(g_module_name,'After Unique Chk',
405 l_debug_level3);
406 --Check if the date range is valid
407 l_return_status :=PA_DELIVERABLE_UTILS.IS_EFF_FROM_TO_DATE_VALID(p_effective_from,p_effective_to);
408 IF(l_return_status = 'N')
409 THEN
410 PA_UTILS.ADD_MESSAGE
411 (p_app_short_name => 'PA',
412 p_msg_name => 'PA_TT_INVALID_DATES');
413 x_return_status :=FND_API.G_RET_STS_ERROR;
414 END IF;
415
416 IF(x_return_status = FND_API.G_RET_STS_ERROR)
417 THEN
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420
421 PA_DEBUG.WRITE(g_module_name,'Before Insert',
422 l_debug_level3);
423 --Call the PA_TASK_TYPE_PKG.insert_row
424 PA_TASK_TYPE_PKG.insert_row
425 (p_task_type_id => p_deliverable_type_id
426 ,p_task_type => p_deliverable_type_name
427 ,p_start_date_active => p_effective_from
428 ,p_end_date_active => p_effective_to
429 ,p_description => p_description
430 ,p_task_type_class_code => p_deliverable_type_class
431 ,p_initial_status_code => p_initial_deliverable_status
432 ,p_prog_entry_enable_flag => p_prog_entry_enable_flag
433 ,p_enable_dlvr_actions_flag => p_enable_dlvr_actions_flag
434 ,p_object_type => 'PA_DLVR_TYPES'
435 ,p_record_version_number => p_record_version_number
436 ,x_task_type_id => l_task_type_id_dummy
437 ,x_return_status => x_return_status
438 ,x_msg_count => x_msg_count
439 ,x_msg_data => x_msg_data
440 );
441
442 PA_DEBUG.WRITE(g_module_name,'After Insert',
443 l_debug_level3);
444 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
445 THEN
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448
449 EXCEPTION
450 -- Set the exception Message and the stack
451 WHEN FND_API.G_EXC_ERROR THEN
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 l_msg_count := FND_MSG_PUB.count_msg;
454
455 -- Rollback to the savepoint defined
456 IF p_commit = FND_API.G_TRUE
457 THEN
458 ROLLBACK TO CREATE_DELIVERABLE_TYPE;
459 END IF;
460
461 IF l_msg_count = 1 AND x_msg_data IS NULL
462 THEN
463 Pa_Interface_Utils_Pub.get_messages
464 ( p_encoded => Fnd_Api.G_TRUE
465 , p_msg_index => 1
466 , p_msg_count => l_msg_count
467 , p_msg_data => l_msg_data
468 , p_data => l_data
469 , p_msg_index_out => l_msg_index_out);
470 x_msg_data := l_data;
471 x_msg_count := l_msg_count;
472 ELSE
473 x_msg_count := l_msg_count;
474 END IF;
475
476 IF l_debug_mode = 'Y'
477 THEN
478 Pa_Debug.reset_curr_function;
479 END IF;
480 RETURN;
481
482 WHEN INV_ARG_EXC THEN
483 x_return_status := Fnd_Api.G_RET_STS_ERROR;
484 x_msg_count := 1;
485 x_msg_data := 'PA_TASK_TYPE_PVT : CREATE_DELIVERABLE_TYPE : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
486
487 IF p_commit = FND_API.G_TRUE
488 THEN
489 ROLLBACK TO CREATE_DELIVERABLE_TYPE;
490 END IF;
491
492
493 Fnd_Msg_Pub.add_exc_msg
494 ( p_pkg_name => 'PA_TASK_TYPE_PVT'
495 , p_procedure_name => 'CREATE_DELIVERABLE_TYPE'
496 , p_error_text => x_msg_data);
497
498 IF l_debug_mode = 'Y'
499 THEN
500 Pa_Debug.g_err_stage:= 'Error'||x_msg_data;
501 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
502 l_debug_level5);
503 Pa_Debug.reset_curr_function;
504 END IF;
505 RAISE;
506
507 WHEN OTHERS THEN
508 -- Set the exception Message and the stack
509 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
510 x_msg_count := 1;
511 x_msg_data := SQLERRM;
512
513 --Rollback to the savepoint defined
514 IF p_commit = FND_API.G_TRUE
515 THEN
516 ROLLBACK TO CREATE_DELIVERABLE_TYPE;
517 END IF;
518
519 Fnd_Msg_Pub.add_exc_msg
520 ( p_pkg_name => 'PA_TASK_TYPE_PVT'
521 , p_procedure_name => 'CREATE_DELIVERABLE_TYPE'
522 , p_error_text => x_msg_data);
523
524 IF l_debug_mode = 'Y'
525 THEN
526 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
527 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
528 l_debug_level5);
529 Pa_Debug.reset_curr_function;
530 END IF;
531 RAISE;
532
533 END CREATE_DELIVERABLE_TYPE;
534
535 -- Procedure : UPDATE_DELIVERABLE_TYPE
536 -- Type : Private Procedure
537 -- Purpose : This is the private API used to update the deliverable type .
538 -- Note : 1)If there is a change in the deliverable type name,check for uniqueness
539 -- 2)If there is a change in the date range,check for validity
540 -- 3)If the Deliverable Type Class has changed and it is in use,throw error
541 -- 4)If Enable Deliverable actions was previously checked and now unchecked
542 -- check whether there exists any deliverable of this type which has actions
543 -- associated with it.If Yes ,Throw Error.Else delete those actions
544 -- 5)If the Enable Progress Entry changes from Checked to UNCHECKED
545 -- then check whether any deliverable of this type p_deliverable_type_id
546 -- has been associated with a Deliverable-based task.If Yes,Throw error
547 -- Assumptions : None
548 -- List of parameters other than standard IN and OUT parameters
549 -- Parameters Type Null? Default Value Description and Purpose
550 -- --------------------------- -------- ------ --------------- ----------------------------
551 --P_deliverable_type_name PA_TASK_TYPES.TASK_TYPE%TYPE N Deliverable Type Name
552 --P_prog_entry_enable_flag PA_TASK_TYPES.PROG_ENTRY_ENABLE_FLAG%TYPE Y 'N' Progress Entry Flag
553 --P_initial_deliverable_status_code PA_TASK_TYPES.INITIAL_STATUS_CODE%TYPE Y 'DLVR_NOT_STARTED'Initial Deliverable Status
554 --P_enable_deliverable_actions PA_TASK_TYPES.ENABLE_DLVR_ACTIONS%TYPE Y 'N' Enable Deliverable Action
555 --P_effective_from PA_TASK_TYPES.START_DATE_ACTIVE%TYPE N Effective from date
556 --p_effective_to PA_TASK_TYPES. END_DATE_ACTIVE %TYPE Y NULL Effective to date
557 --P_description PA_TASK_TYPES.DESCRIPTION%TYPE Y NULL Description
558 --P_deliverable_type_id PA_TASK_TYPES.TASK_TYPE_ID%TPE N
559
560 PROCEDURE UPDATE_DELIVERABLE_TYPE
561 (p_api_version IN NUMBER := 1.0
562 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
563 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
564 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
565 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
566 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
567 ,p_debug_mode IN VARCHAR2 := 'N'
568 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
569 ,p_deliverable_type_name IN PA_TASK_TYPES.TASK_TYPE%TYPE
570 ,p_prog_entry_enable_flag IN PA_TASK_TYPES.PROG_ENTRY_ENABLE_FLAG%TYPE := 'N'
571 ,p_initial_deliverable_status IN PA_TASK_TYPES.INITIAL_STATUS_CODE%TYPE := 'DLVR_NOT_STARTED'
572 ,p_deliverable_type_class IN PA_TASK_TYPES.TASK_TYPE_CLASS_CODE%TYPE := 'ITEM'
573 ,p_enable_dlvr_actions_flag IN PA_TASK_TYPES.ENABLE_DLVR_ACTIONS_FLAG%TYPE := 'N'
574 ,p_effective_from IN PA_TASK_TYPES.START_DATE_ACTIVE%TYPE
575 ,p_effective_to IN PA_TASK_TYPES. END_DATE_ACTIVE %TYPE := NULL
576 ,p_description IN PA_TASK_TYPES.DESCRIPTION%TYPE := NULL
577 ,p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE
578 ,p_record_version_number IN PA_TASK_TYPES.RECORD_VERSION_NUMBER%TYPE
579 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
580 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
581 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
582 )
583 IS
584 l_msg_index_out NUMBER;
585 l_debug_mode VARCHAR2(1);
586
587 l_dummy VARCHAR2(1);
588 l_return_status VARCHAR2(1);
589 l_msg_count NUMBER;
590 l_data VARCHAR2(2000);
591 l_msg_data VARCHAR2(2000);
592
593 l_debug_level2 CONSTANT NUMBER := 2;
594 l_debug_level3 CONSTANT NUMBER := 3;
595 l_debug_level4 CONSTANT NUMBER := 4;
596 l_debug_level5 CONSTANT NUMBER := 5;
597
598 l_allow_prog_entry_disable VARCHAR2(1) := 'N' ; -- Bug 3627161
599 --This cursor is defined to find existence of any progress related records
600 -- for a deliverable of type p_deliverable_type_id
601
602 Cursor c_progress_rec_exists IS
603 SELECT 'X'
604 FROM dual
605 WHERE EXISTS
606 (SELECT proj_element_id
607 FROM pa_proj_elements
608 WHERE TYPE_ID=p_deliverable_type_id
609 AND OBJECT_TYPE='PA_DELIVERABLES'
610 AND 'Y' = (pa_deliverable_utils.IS_DELIVERABLE_HAS_PROGRESS(PROJECT_ID,PROJ_ELEMENT_ID)));
611
612 --The cursor takes the original values from the database
613
614 Cursor c_original_value_rec IS
615 SELECT TASK_TYPE
616 ,START_DATE_ACTIVE
617 ,END_DATE_ACTIVE
618 ,INITIAL_STATUS_CODE
619 ,TASK_TYPE_CLASS_CODE
620 ,ENABLE_DLVR_ACTIONS_FLAG
621 ,PROG_ENTRY_ENABLE_FLAG
622 FROM PA_TASK_TYPES
623 WHERE TASK_TYPE_ID = p_deliverable_type_id
624 AND OBJECT_TYPE = 'PA_DLVR_TYPES';
625
626 l_original_value_rec c_original_value_rec%ROWTYPE ;
627
628 BEGIN
629
630 -- Initialize the return status to success
631 x_return_status := FND_API.G_RET_STS_SUCCESS;
632 --Log message
633 l_debug_mode := p_debug_mode;
634
635 IF l_debug_mode = 'Y'
636 THEN
637 PA_DEBUG.set_curr_function( p_function => 'UPDATE_DELIVERABLE_TYPE',
638 p_debug_mode => l_debug_mode );
639 END IF;
640
641 IF l_debug_mode = 'Y'
642 THEN
643 PA_DEBUG.g_err_stage:= 'PA_TASK_TYPES_PVT.UPDATE_DELIVERABLE_TYPE : Printing Input parameters';
644 PA_DEBUG.WRITE(g_module_name,Pa_Debug.g_err_stage,
645 l_debug_level3);
646 PA_DEBUG.WRITE(g_module_name,'p_deliverable_type_name'||':'||p_deliverable_type_name,
647 l_debug_level3);
648 PA_DEBUG.WRITE(g_module_name,'p_prog_entry_enable_flag'||':'||p_prog_entry_enable_flag,
649 l_debug_level3);
650 PA_DEBUG.WRITE(g_module_name,'p_initial_deliverable_status'||':'||p_initial_deliverable_status,
651 l_debug_level3);
652 PA_DEBUG.WRITE(g_module_name,'p_deliverable_type_class'||':'||p_deliverable_type_class,
653 l_debug_level3);
654 PA_DEBUG.WRITE(g_module_name,'p_enable_dlvr_actions_flag'||':'||p_enable_dlvr_actions_flag,
655 l_debug_level3);
656 PA_DEBUG.WRITE(g_module_name,'p_effective_from'||':'||p_effective_from,
657 l_debug_level3);
658 PA_DEBUG.WRITE(g_module_name,'p_effective_to'||':'||p_effective_to,
659 l_debug_level3);
660 PA_DEBUG.WRITE(g_module_name,'p_description'||':'||p_description,
661 l_debug_level3);
662 PA_DEBUG.WRITE(g_module_name,'p_deliverable_type_id'||':'||p_deliverable_type_id,
663 l_debug_level3);
664 PA_DEBUG.WRITE(g_module_name,'p_record_version_number'||':'||p_record_version_number,
665 l_debug_level3);
666
667 END IF;
668
669 -- Initialize the Message Stack
670 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE))
671 THEN
672 FND_MSG_PUB.initialize;
673 END IF;
674
675 -- Save point
676 IF (p_commit = FND_API.G_TRUE)
677 THEN
678 savepoint UPDATE_DELIVERABLE_TYPE;
679 END IF;
680
681 PA_DEBUG.WRITE(g_module_name,'After issuing save point in UPDATE_DLV_TYPE :PA_TASK_TYPES_PVT',
682 l_debug_level3);
683
684 --the cursor contents into local variable
685 OPEN c_original_value_rec;
686 FETCH c_original_value_rec into l_original_value_rec ;
687 CLOSE c_original_value_rec;
688
689 PA_DEBUG.WRITE(g_module_name,'After fetching the cursor value in to local variable',
690 l_debug_level3);
691
692 --Check whether Deliverable Type Id or record version number or deliverable type name or start date is null.
693 --If Yes,then due to some internal error it is lost,so throw error
694 IF (p_deliverable_type_id IS NULL) OR (p_record_version_number IS NULL)
695 OR (p_deliverable_type_name IS NULL) OR (p_effective_from IS NULL)
696 THEN
697 IF l_debug_mode = 'Y'
698 THEN
699 Pa_Debug.g_err_stage:= 'PA_TASK_TYPES_PVT : UPDATE_DELIVERABLE_TYPE : The DeliverableTypeID/NAME/Startdate/RecVersionNumber is null';
700 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
701 l_debug_level3);
702 END IF;
703 RAISE INV_ARG_EXC;
704 END IF;
705
706 -- If the Deliverable Type Name has changed
707 -- then check for unique name
708 IF l_original_value_rec.TASK_TYPE <> p_deliverable_type_name
709 THEN
710 l_return_status := PA_DELIVERABLE_UTILS.IS_DLV_TYPE_NAME_UNIQUE (p_deliverable_type_name);
711 IF(l_return_status = 'N')
712 THEN
713 PA_UTILS.ADD_MESSAGE
714 (p_app_short_name => 'PA'
715 ,p_msg_name => 'PA_DLV_TYPE_EXISTS');
716
717 x_return_status :=FND_API.G_RET_STS_ERROR;
718 END IF;
719 END IF;
720 PA_DEBUG.WRITE(g_module_name,'After Checking for unique name',
721 l_debug_level3);
722
723 --If the date values have changed then check for valid dates
724
725 IF TRUNC(p_effective_to) IS NOT NULL
726 THEN
727 IF (TRUNC(l_original_value_rec.START_DATE_ACTIVE) <> TRUNC(p_effective_from)) OR (l_original_value_rec.END_DATE_ACTIVE IS NULL)
728 OR (l_original_value_rec.END_DATE_ACTIVE IS NOT NULL AND TRUNC(l_original_value_rec.END_DATE_ACTIVE) <> TRUNC(p_effective_to))
729 THEN
730 l_return_status :=PA_DELIVERABLE_UTILS.IS_EFF_FROM_TO_DATE_VALID(p_effective_from,p_effective_to);
731 IF(l_return_status = 'N')
732 THEN
733 PA_UTILS.ADD_MESSAGE
734 (p_app_short_name => 'PA'
735 ,p_msg_name => 'PA_TT_INVALID_DATES');
736
737 x_return_status :=FND_API.G_RET_STS_ERROR;
738 END IF;
739 END IF;
740 END IF;
741 PA_DEBUG.WRITE(g_module_name,'After checking for invalid dates',
742 l_debug_level3);
743
744 --If the Deliverable Type Class has changed then check whether it is in use
745 --If Yes,Throw Error
746 If l_original_value_rec.TASK_TYPE_CLASS_CODE <> p_deliverable_type_class
747 THEN
748 l_return_status :=PA_DELIVERABLE_UTILS.IS_DLV_TYPE_IN_USE(p_deliverable_type_id);
749 If (l_return_status='Y' )
750 THEN
751 PA_UTILS.ADD_MESSAGE
752 (p_app_short_name => 'PA'
753 ,p_msg_name => 'PA_DLV_TYPE_CLASS_IN_USE');
754
755 x_return_status :=FND_API.G_RET_STS_ERROR;
756 END IF;
757 END IF;
758
759 --If the Enable Progress Entry changes from Checked to UNCHECKED then
760 --1) check whether any deliverable of this type p_deliverable_type_id
761 --has been associated with a Deliverable-based task
762 --If Yes,Throw error
763
764 --2)check whether any deliverable of this type has progress records
765 --If Yes,throw Error
766 IF p_prog_entry_enable_flag = 'N' AND l_original_value_rec.PROG_ENTRY_ENABLE_FLAG = 'Y'
767 THEN
768 -- Bug#3555460
769 -- Relaxed the check that enable progress entry cannot
770 -- cannot be unchecked if deliverbale type is used by
771 -- deliverables which are associated to deliverable based task.
772
773 -- l_return_status :=PA_DELIVERABLE_UTILS.IS_DLV_BASED_ASSCN_EXISTS(p_deliverable_type_id);
774 -- IF(l_return_status='Y')
775 -- THEN
776 -- PA_UTILS.ADD_MESSAGE
777 -- (p_app_short_name => 'PA'
778 -- ,p_msg_name => 'PA_DLV_BASED_ASSN_EXISTS');
779
780 -- x_return_status :=FND_API.G_RET_STS_ERROR;
781 -- END IF;
782
783 OPEN c_progress_rec_exists;
784 FETCH c_progress_rec_exists INTO l_dummy;
785
786 IF c_progress_rec_exists%found
787 THEN
788 PA_UTILS.Add_Message( p_app_short_name => 'PA'
789 ,p_msg_name => 'PA_DLV_TYPE_PROG_ERR');
790 x_return_status := FND_API.G_RET_STS_ERROR;
791
792 IF l_debug_mode = 'Y'
793 THEN
794 Pa_Debug.g_err_stage:= ' Error:PA_DLV_TYPE_PROG_ERR';
795 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
796 l_debug_level5);
797 END IF;
798 ELSE -- Bug 3627161
799 l_allow_prog_entry_disable := 'Y' ;
800 END IF;
801
802 CLOSE c_progress_rec_exists;
803
804 -- Bug 3627161 If Unchecking the Enable Progress Entry Flag is allowed ,
805 -- (i.e) If no progress records exist for any of the deliverables of this type,then
806 -- Null Out the Progress weight for all deliverables of this type
807
808 IF l_allow_prog_entry_disable = 'Y' THEN
809 UPDATE PA_PROJ_ELEMENTS SET PROGRESS_WEIGHT = NULL
810 WHERE type_id = p_deliverable_type_id
811 AND OBJECT_TYPE = 'PA_DELIVERABLES'
812 AND PROGRESS_WEIGHT IS NOT NULL ;
813 END IF ;
814
815 END IF;
816
817 --If the Enable Deliverable Actions changes from CHECKED to UNCHECKED
818 --then check whether there exists any deliverable of this type which has actions associated with it
819 --If Yes ,Throw Error
820 --Else delete those actions
821 IF p_enable_dlvr_actions_flag = 'N' AND l_original_value_rec.ENABLE_DLVR_ACTIONS_FLAG = 'Y'
822 THEN
823 l_return_status :=PA_DELIVERABLE_UTILS.IS_DLV_ACTIONS_EXISTS(p_deliverable_type_id);
824 IF (l_return_status='Y')
825 THEN
826 PA_UTILS.ADD_MESSAGE
827 (p_app_short_name => 'PA'
828 ,p_msg_name => 'PA_DLV_ACTION_EXISTS');
829
830 x_return_status:=FND_API.G_RET_STS_ERROR;
831 ELSE
832
833 --before placing call to API check for any errors
834 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
835 THEN
836 RAISE FND_API.G_EXC_ERROR;
837 END IF;
838
839 --Place a Call to Delete Deliverable Actions
840 PA_ACTIONS_PUB.DELETE_DLV_ACTIONS_IN_BULK
841 (p_api_version => p_api_version
842 ,p_init_msg_list => FND_API.G_FALSE
843 ,p_commit => p_commit
844 ,p_validate_only => p_validate_only
845 ,p_validation_level => p_validation_level
846 ,p_calling_module => p_calling_module
847 ,p_debug_mode => l_debug_mode
848 ,p_max_msg_count => p_max_msg_count
849 ,p_object_id => p_deliverable_type_id
850 ,p_object_type => 'PA_DLVR_TYPES'
851 ,x_return_status => x_return_status
852 ,x_msg_data => x_msg_data
853 ,x_msg_count => x_msg_count);
854
855 --After API execution check for errors
856 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
857 THEN
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860 END IF;
861 END IF;
862
863 IF(x_return_status=FND_API.G_RET_STS_ERROR)
864 THEN
865 RAISE FND_API.G_EXC_ERROR;
866 END IF;
867
868 --Call PA_TASK_TYPE_PKG.update_row
869 PA_TASK_TYPE_PKG.update_row
870 (p_task_type_id => p_deliverable_type_id
871 ,p_task_type => p_deliverable_type_name
872 ,p_start_date_active => p_effective_from
873 ,p_end_date_active => p_effective_to
874 ,p_description => p_description
875 ,p_task_type_class_code => p_deliverable_type_class
876 ,p_initial_status_code => p_initial_deliverable_status
877 ,p_prog_entry_enable_flag => p_prog_entry_enable_flag
878 ,p_enable_dlvr_actions_flag => p_enable_dlvr_actions_flag
879 ,p_object_type => 'PA_DLVR_TYPES'
880 ,p_record_version_number => p_record_version_number
881 ,x_return_status => x_return_status
882 ,x_msg_count => x_msg_count
883 ,x_msg_data => x_msg_data
884 );
885 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
886 THEN
887 RAISE FND_API.G_EXC_ERROR;
888 END IF;
889
890 EXCEPTION
891
892 WHEN FND_API.G_EXC_ERROR THEN
893 x_return_status := FND_API.G_RET_STS_ERROR;
894 l_msg_count := FND_MSG_PUB.count_msg;
895
896 IF p_commit = FND_API.G_TRUE
897 THEN
898 ROLLBACK TO UPDATE_DELIVERABLE_TYPE;
899 END IF;
900
901 IF c_original_value_rec%ISOPEN
902 THEN
903 CLOSE c_original_value_rec;
904 END IF;
905
906 IF c_progress_rec_exists%ISOPEN
907 THEN
908 CLOSE c_progress_rec_exists;
909 END IF;
910
911 IF l_msg_count = 1 AND x_msg_data IS NULL
912 THEN
913 Pa_Interface_Utils_Pub.get_messages
914 ( p_encoded => Fnd_Api.G_TRUE
915 , p_msg_index => 1
916 , p_msg_count => l_msg_count
917 , p_msg_data => l_msg_data
918 , p_data => l_data
919 , p_msg_index_out => l_msg_index_out);
920 x_msg_data := l_data;
921 x_msg_count := l_msg_count;
922 ELSE
923 x_msg_count := l_msg_count;
924 END IF;
925
926 IF l_debug_mode = 'Y'
927 THEN
928 Pa_Debug.reset_curr_function;
929 END IF;
930
931 WHEN INV_ARG_EXC THEN
932 x_return_status := Fnd_Api.G_RET_STS_ERROR;
933 x_msg_count := 1;
934 x_msg_data := 'PA_TASK_TYPE_PVT : UPDATE_DELIVERABLE_TYPE : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
935
936 IF p_commit = FND_API.G_TRUE
937 THEN
938 ROLLBACK TO UPDATE_DELIVERABLE_TYPE;
939 END IF;
940
941 IF c_original_value_rec%ISOPEN
942 THEN
943 CLOSE c_original_value_rec;
944 END IF;
945
946 IF c_progress_rec_exists%ISOPEN
947 THEN
948 CLOSE c_progress_rec_exists;
949 END IF;
950
951 Fnd_Msg_Pub.add_exc_msg
952 ( p_pkg_name => 'PA_TASK_TYPE_PVT'
953 , p_procedure_name => 'UPDATE_DELIVERABLE_TYPE'
954 , p_error_text => x_msg_data);
955
956 IF l_debug_mode = 'Y'
957 THEN
958 Pa_Debug.g_err_stage:= 'Error'||x_msg_data;
959 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
960 l_debug_level5);
961 Pa_Debug.reset_curr_function;
962 END IF;
963 RAISE;
964
965 WHEN OTHERS THEN
966 -- Set the exception Message and the stack
967 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
968 x_msg_count := 1;
969 x_msg_data := SQLERRM;
970
971 IF p_commit = FND_API.G_TRUE
972 THEN
973 ROLLBACK TO UPDATE_DELIVERABLE_TYPE;
974 END IF;
975
976 IF c_original_value_rec%ISOPEN
977 THEN
978 CLOSE c_original_value_rec;
979 END IF;
980
981 IF c_progress_rec_exists%ISOPEN
982 THEN
983 CLOSE c_progress_rec_exists;
984 END IF;
985
986 Fnd_Msg_Pub.add_exc_msg
987 (p_pkg_name => 'PA_TASK_TYPE_PVT'
988 , p_procedure_name => 'UPDATE_DELIVERABLE_TYPE'
989 , p_error_text => x_msg_data);
990
991 IF l_debug_mode = 'Y'
992 THEN
993 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
994 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
995 l_debug_level5);
996 Pa_Debug.reset_curr_function;
997 END IF;
998 RAISE;
999
1000 END UPDATE_DELIVERABLE_TYPE;
1001
1002 -- Procedure : DELETE_DELIVERABLE_TYPE
1003 -- Type : Private Procedure
1004 -- Purpose : This is the private API used to delete the deliverable type .
1005 -- Note : This procedure places call to delete deliverable actions API
1006 -- to delete the actions which are associated with the deliverable Type
1007 -- and then deletes the deliverable type by placing call to PA_TASK_TYPE_PKG.delete_row
1008 -- Assumptions : None
1009 -- List of parameters other than standard IN and OUT parameters
1010 -- Parameters Type Null? Description and Purpose
1011 -- --------------------------- ------------------------------- -------- -----------------------------------
1012 --P_deliverable_type_id PA_TASK_TYPES.TASK_TYPE_ID%TYPE N Deliverable Type Id
1013 --p_record_version_number PA_TASK_TYPES.RECORD_VERSION_NUMBER%TYPE N Record Version Number
1014
1015 PROCEDURE DELETE_DELIVERABLE_TYPE
1016 (p_api_version IN NUMBER := 1.0
1017 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1018 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1019 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1020 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1021 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1022 ,p_debug_mode IN VARCHAR2 := 'N'
1023 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1024 ,p_deliverable_type_id IN pa_task_types.Task_Type_id%TYPE
1025 ,p_record_version_number IN PA_TASK_TYPES.RECORD_VERSION_NUMBER%TYPE
1026 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1027 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1028 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1029 )
1030 IS
1031 l_msg_index_out NUMBER;
1032 l_debug_mode VARCHAR2(1);
1033
1034 l_msg_count NUMBER;
1035 l_data VARCHAR2(2000);
1036 l_msg_data VARCHAR2(2000);
1037 l_return_status VARCHAR2(1);
1038
1039 l_debug_level2 CONSTANT NUMBER := 2;
1040 l_debug_level3 CONSTANT NUMBER := 3;
1041 l_debug_level4 CONSTANT NUMBER := 4;
1042 l_debug_level5 CONSTANT NUMBER := 5;
1043 BEGIN
1044 -- Initialize the return status to success
1045 x_return_status := FND_API.G_RET_STS_SUCCESS;
1046
1047 --Log message
1048 l_debug_mode :=p_debug_mode;
1049
1050 IF l_debug_mode = 'Y'
1051 THEN
1052 PA_DEBUG.set_curr_function( p_function => 'DELETE_DELIVERABLE_TYPE',
1053 p_debug_mode => l_debug_mode );
1054 END IF;
1055 --savepoint
1056 IF (p_commit = FND_API.G_TRUE)
1057 THEN
1058 savepoint DELETE_DELIVERABLE_TYPE;
1059 END IF;
1060
1061 IF l_debug_mode = 'Y'
1062 THEN
1063 PA_DEBUG.g_err_stage:= 'PA_TASK_TYPE_PVT.DELETE_DELIVERABLE_TYPE : Printing Input parameters';
1064 PA_DEBUG.WRITE(g_module_name,Pa_Debug.g_err_stage,
1065 l_debug_level3);
1066 PA_DEBUG.WRITE(g_module_name,'p_deliverable_type_id'||':'||p_deliverable_type_id,
1067 l_debug_level3);
1068 PA_DEBUG.WRITE(g_module_name,'p_record_version_number'||':'||p_record_version_number,
1069 l_debug_level3);
1070 END IF;
1071
1072 --Initialize the Message Stack
1073 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE))
1074 THEN
1075 FND_MSG_PUB.initialize;
1076 END IF;
1077
1078 IF l_debug_mode = 'Y'
1079 THEN
1080 PA_DEBUG.g_err_stage:= 'BEFORE CALLING PA_ACTIONS_PUB.DELETE_DLV_ACTIONS';
1081 PA_DEBUG.WRITE(g_module_name,Pa_Debug.g_err_stage,
1082 l_debug_level3);
1083 END IF;
1084 --Call Delete Deliverable Actions API
1085 PA_ACTIONS_PUB.DELETE_DLV_ACTIONS_IN_BULK
1086 (p_api_version => p_api_version
1087 ,p_init_msg_list => FND_API.G_FALSE
1088 ,p_commit => p_commit
1089 ,p_validate_only => p_validate_only
1090 ,p_validation_level => p_validation_level
1091 ,p_calling_module => p_calling_module
1092 ,p_debug_mode => l_debug_mode
1093 ,p_max_msg_count => p_max_msg_count
1094 ,p_object_id => p_deliverable_type_id
1095 ,p_object_type => 'PA_DLVR_TYPES'
1096 ,x_return_status => x_return_status
1097 ,x_msg_data => x_msg_data
1098 ,x_msg_count => x_msg_count);
1099
1100 IF l_debug_mode = 'Y'
1101 THEN
1102 PA_DEBUG.g_err_stage:= 'AFTER RETURNING FROM PA_ACTIONS_PUB.DELETE_DLV_ACTIONS';
1103 PA_DEBUG.WRITE(g_module_name,Pa_Debug.g_err_stage,
1104 l_debug_level3);
1105 END IF;
1106
1107 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1108 THEN
1109 RAISE FND_API.G_EXC_ERROR;
1110 END IF;
1111
1112 --Call PA_TASK_TYPE_PKG.DELETE_ROW(p_deliverable_type_id)
1113 PA_TASK_TYPE_PKG.delete_row
1114 (p_task_type_id => p_deliverable_type_id
1115 ,p_record_version_number => p_record_version_number
1116 ,x_return_status => x_return_status
1117 ,x_msg_data => x_msg_data
1118 ,x_msg_count => x_msg_count);
1119
1120 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1121 THEN
1122 RAISE FND_API.G_EXC_ERROR;
1123 END IF;
1124 EXCEPTION
1125
1126 WHEN FND_API.G_EXC_ERROR THEN
1127 x_return_status := FND_API.G_RET_STS_ERROR;
1128 l_msg_count := FND_MSG_PUB.count_msg;
1129
1130 IF p_commit = FND_API.G_TRUE
1131 THEN
1132 ROLLBACK TO DELETE_DELIVERABLE_TYPE;
1133 END IF;
1134
1135 IF l_msg_count = 1 AND x_msg_data IS NULL
1136 THEN
1137 Pa_Interface_Utils_Pub.get_messages
1138 ( p_encoded => Fnd_Api.G_TRUE
1139 , p_msg_index => 1
1140 , p_msg_count => l_msg_count
1141 , p_msg_data => l_msg_data
1142 , p_data => l_data
1143 , p_msg_index_out => l_msg_index_out);
1144 x_msg_data := l_data;
1145 x_msg_count := l_msg_count;
1146 ELSE
1147 x_msg_count := l_msg_count;
1148 END IF;
1149
1150 IF l_debug_mode = 'Y'
1151 THEN
1152 Pa_Debug.reset_curr_function;
1153 END IF;
1154
1155 WHEN OTHERS THEN
1156 -- Set the exception Message and the stack
1157 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1158 x_msg_count := 1;
1159 x_msg_data := SQLERRM;
1160
1161 IF p_commit = FND_API.G_TRUE
1162 THEN
1163 ROLLBACK TO DELETE_DELIVERABLE_TYPE;
1164 END IF;
1165
1166 Fnd_Msg_Pub.add_exc_msg
1167 ( p_pkg_name => 'PA_TASK_TYPE_PVT'
1168 , p_procedure_name => 'DELETE_DELIVERABLE_TYPE'
1169 , p_error_text => x_msg_data);
1170
1171 IF l_debug_mode = 'Y'
1172 THEN
1173 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1174 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1175 l_debug_level5);
1176 Pa_Debug.reset_curr_function;
1177 END IF;
1178 RAISE;
1179 END DELETE_DELIVERABLE_TYPE;
1180 END PA_TASK_TYPE_PVT;