4 g_module_name VARCHAR2(100) := 'pa.plsql.pa_actions_pvt';
1 PACKAGE BODY PA_ACTIONS_PVT as
2 /* $Header: PAACTNVB.pls 120.4.12020000.2 2013/03/28 13:43:54 djambhek ship $ */
3
5
6 Invalid_Arg_Exc_Dlv EXCEPTION ;
7 g_dlvr_types CONSTANT PA_LOOKUPS.LOOKUP_CODE%TYPE := 'PA_DLVR_TYPES';
8 g_actions CONSTANT PA_LOOKUPS.LOOKUP_CODE%TYPE := 'PA_ACTIONS';
9 g_dlvr_type_to_action CONSTANT PA_LOOKUPS.LOOKUP_CODE%TYPE := 'DLVR_TYPE_TO_ACTION';
10 g_dlvr_to_action CONSTANT PA_LOOKUPS.LOOKUP_CODE%TYPE := 'DELIVERABLE_TO_ACTION';
11 g_billing CONSTANT PA_LOOKUPS.LOOKUP_CODE%TYPE := 'BILLING';
12 g_shipping CONSTANT PA_LOOKUPS.LOOKUP_CODE%TYPE := 'SHIPPING';
13 g_procurement CONSTANT PA_LOOKUPS.LOOKUP_CODE%TYPE := 'PROCUREMENT';
14
15 -- SubProgram : CREATE_DLV_ACTIONS_IN_BULK
16 -- Type : PROCEDURE
17 -- Purpose : Private API to create to Deliverable Actions
18 -- Note : Its a BULK API
19 -- Assumptions : None
20 -- Parameter IN/OUT Type Required Description and Purpose
21 -- --------------------------- --------- ---------- --------- ---------------------------
22 -- p_api_version IN NUMBER N Standard Parameter
23 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
24 -- p_commit IN VARCHAR2 N Standard Parameter
25 -- p_validate_only IN VARCHAR2 N Standard Parameter
26 -- p_validation_level IN NUMBER N Standard Parameter
27 -- p_calling_module IN VARCHAR2 N Standard Parameter
28 -- p_debug_mode IN VARCHAR2 N Standard Parameter
29 -- p_max_msg_count IN NUMBER N Standard Parameter
30 -- p_name_tbl IN PLSQL Table N Action Name
31 -- p_manager_person_id_tbl IN PLSQL Table N Manager Id
32 -- p_function_code_tbl IN PLSQL Table N Action Function
33 -- p_due_date_tbl IN PLSQL Table N Due Date
34 -- p_completed_flag_tbl IN PLSQL Table N Completed Flag
35 -- p_completion_date_tbl IN PLSQL Table N Completed Date
36 -- p_description_tbl IN PLSQL Table N Description
37 -- p_attribute_category_tbl IN PLSQL Table N DFF Field
38 -- p_attribute1_tbl IN PLSQL Table N DFF Filed
39 -- p_attribute2_tbl IN PLSQL Table N DFF Field
40 -- p_attribute3_tbl IN PLSQL Table N DFF Filed
41 -- p_attribute4_tbl IN PLSQL Table N DFF Field
42 -- p_attribute5_tbl IN PLSQL Table N DFF Filed
43 -- p_attribute6_tbl IN PLSQL Table N DFF Field
44 -- p_attribute7_tbl IN PLSQL Table N DFF Filed
45 -- p_attribute8_tbl IN PLSQL Table N DFF Field
46 -- p_attribute9_tbl IN PLSQL Table N DFF Filed
47 -- p_attribute10_tbl IN PLSQL Table N DFF Field
48 -- p_attribute11_tbl IN PLSQL Table N DFF Filed
49 -- p_attribute12_tbl IN PLSQL Table N DFF Field
50 -- p_attribute13_tbl IN PLSQL Table N DFF Filed
54 -- p_proj_element_id_tbl IN PLSQL Table N Action Element Id
51 -- p_attribute14_tbl IN PLSQL Table N DFF Field
52 -- p_attribute15_tbl IN PLSQL Table N DFF Filed
53 -- p_element_version_id_tbl IN PLSQL Table N Action VErsion Id
55 -- p_record_version_number_tbl IN PLSQL Table N Record Version NUmber
56 -- p_project_id IN NUMBER N Project Id
57 -- p_object_id IN NUMBER Y Parent Id
58 -- p_object_version_id IN NUMBER N Parent Version ID
59 -- p_object_type IN VARCHAR2 Y Parent Type
60 -- p_pm_source_code IN NUMBER N PM Source Code
61 -- p_pm_source_reference IN VARCHAR2 N PM Source Reference
62 -- p_carrying_out_organization_id IN VARCHAR2 N Carrying Out Org ID
63 -- x_return_status OUT VARCHAR2 N Mandatory Out Parameter
64 -- x_msg_count OUT NUMBER N Mandatory Out Parameter
65 -- x_msg_data OUT VARCHAR2 N Mandatory Out Parameter
66
67 PROCEDURE CREATE_DLV_ACTIONS_IN_BULK
68 (p_api_version IN NUMBER :=1.0
69 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
70 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
71 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
72 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
73 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
74 ,p_debug_mode IN VARCHAR2 :='N'
75 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
76 ,p_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
77 ,p_manager_person_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
78 ,p_function_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
79 ,p_due_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE()
80 ,p_completed_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM. PA_VARCHAR2_1_TBL_TYPE()
81 ,p_completion_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE()
82 ,p_description_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE()
83 ,p_attribute_category_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
84 ,p_attribute1_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
85 ,p_attribute2_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
86 ,p_attribute3_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
87 ,p_attribute4_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
88 ,p_attribute5_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
89 ,p_attribute6_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
90 ,p_attribute7_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
91 ,p_attribute8_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
92 ,p_attribute9_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
93 ,p_attribute10_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
94 ,p_attribute11_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
95 ,p_attribute12_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
96 ,p_attribute13_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
97 ,p_attribute14_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
98 ,p_attribute15_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
99 ,p_element_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
100 ,p_proj_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
101 ,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
102 ,p_project_id IN PA_PROJECTS_ALL.PROJECT_ID%TYPE := null
103 ,p_object_id IN PA_OBJECT_RELATIONSHIPS.OBJECT_ID_TO1%TYPE
104 ,p_object_version_id IN PA_OBJECT_RELATIONSHIPS.OBJECT_ID_TO1%TYPE := null
105 ,p_object_type IN PA_LOOKUPS.LOOKUP_CODE%TYPE
106 ,p_pm_source_code IN pa_proj_elements.pm_source_code%TYPE := null
107 ,p_pm_source_reference IN pa_proj_elements.pm_source_reference%TYPE := null
108 ,p_pm_source_reference_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE() -- added 3435905
109 ,p_carrying_out_organization_id IN pa_proj_elements.carrying_out_organization_id%TYPE := null
110 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
111 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
112 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
113 )
114 IS
115 l_proj_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
116 l_element_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
117 l_rel_subtype PA_OBJECT_RELATIONSHIPS.RELATIONSHIP_SUBTYPE%TYPE ;
118 l_msg_count NUMBER ;
119 l_data VARCHAR2(2000);
120 l_msg_data VARCHAR2(2000);
121 l_msg_index_out NUMBER;
125 x_msg_count := 0;
122 i NUMBER;
123 l_pm_source_reference_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE(); -- added for 3574730
124 BEGIN
126 x_return_status := FND_API.G_RET_STS_SUCCESS;
127
128 IF p_debug_mode = 'Y' THEN
129 PA_DEBUG.set_curr_function( p_function => 'CREATE_DLV_ACTIONS_IN_BULK'
130 ,p_debug_mode => p_debug_mode );
131 pa_debug.g_err_stage:= 'Inside CREATE_DLV_ACTIONS_IN_BULK ';
132 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
133 END IF;
134
135 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
136 FND_MSG_PUB.initialize;
137 END IF;
138
139 IF p_debug_mode = 'Y' THEN
140 pa_debug.write(g_module_name,'Printing Input Parameters ',3) ;
141 pa_debug.write(g_module_name,'#'||p_project_id||'#'||p_object_id||'#'||p_object_version_id||'#'||p_object_type||'#'||
142 p_pm_source_code||'#'||p_carrying_out_organization_id,3) ;
143
144 IF (p_calling_module = 'AMG') THEN
145 i := p_name_tbl.first();
146 WHILE i is not null loop
147 pa_debug.write(g_module_name,'#1'||p_name_tbl(i)||'#'||p_manager_person_id_tbl(i)||'#'||p_function_code_tbl(i)||'#'||p_due_date_tbl(i)||'#'||p_completed_flag_tbl(i)||'#'||p_pm_source_reference_tbl(i),3) ;
148 pa_debug.write(g_module_name,'#2'||p_completion_date_tbl(i)||'#'||p_description_tbl(i)||'#'||p_element_version_id_tbl(i)||'#'||p_proj_element_id_tbl(i)||'#'||p_record_version_number_tbl(i) ,3) ;
149
150 i := p_name_tbl.next(i);
151 end loop;
152 END IF; -- p_calling_module = 'AMG'
153 END IF;
154
155 IF nvl(p_name_tbl.LAST,0)> 0 THEN
156
157 -- populate the element table
158 IF p_debug_mode = 'Y' THEN
159 pa_debug.g_err_stage := 'Bulk inserting into PA_PROJ_ELEMENTS';
160 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
161 END IF;
162
163 -- 3574730 : not able to create deliverable, default actions copy is causing the problem
164 -- the below code must be removed once permanent fix is done
165 -- here, if calling module is self_service extend the p_pm_source_reference_tbl
166 -- this is required because, when ever this api is called in self_service mode
167 -- decode statement in the insert will try to evaluate p_pm_source_reference_tbl(i) value
168 -- so temparorily fix the issue, we are extending the table
169
170 -- when ever permanant fix is done, remove local varaible, the below code and in insert from local to input parameter
171
172 IF p_calling_module = 'SELF_SERVICE' THEN
173 FOR i IN p_name_tbl.FIRST..p_name_tbl.LAST
174 LOOP
175 l_pm_source_reference_tbl.extend;
176 l_pm_source_reference_tbl(i):= null; /* Bug # 3590235 */
177 END LOOP;
178 ELSE
179 FOR i IN p_name_tbl.FIRST..p_name_tbl.LAST
180 LOOP
181 l_pm_source_reference_tbl.extend; /* Bug # 3590235 */
182 l_pm_source_reference_tbl(i) := p_pm_source_reference_tbl(i) ;
183 END LOOP;
184 END IF;
185
186 -- 3574730 end
187
188 -- For action level record associated to Deliverable Type
189 -- Project id is populated as null .
190
191 FORALL i in p_name_tbl.FIRST..p_name_tbl.LAST
192 INSERT INTO PA_PROJ_ELEMENTS(
193 proj_element_id
194 ,project_id
195 ,object_type
196 ,name
197 ,element_number
198 ,creation_date
199 ,created_by
200 ,last_update_date
201 ,last_updated_by
202 ,description
203 ,status_code
204 ,function_code
205 ,pm_source_code
206 ,pm_source_reference
207 ,manager_person_id
208 ,carrying_out_organization_id
209 ,record_version_number
210 ,last_update_login
211 ,program_application_id
212 ,attribute_category
213 ,attribute1
214 ,attribute2
215 ,attribute3
216 ,attribute4
217 ,attribute5
218 ,attribute6
219 ,attribute7
220 ,attribute8
221 ,attribute9
222 ,attribute10
223 ,attribute11
224 ,attribute12
225 ,attribute13
226 ,attribute14
227 ,attribute15
228 ,source_object_id
229 ,source_object_type
230 )
231 VALUES
232 (
233 nvl(p_proj_element_id_tbl(i),pa_tasks_s.nextval )
234 ,nvl(p_project_id,-99)
235 ,g_actions
236 ,p_name_tbl(i)
237 ,pa_tasks_s.currval
238 ,SYSDATE
239 ,fnd_global.user_id
240 ,SYSDATE
241 ,fnd_global.user_id
242 ,p_description_tbl(i)
246 ,decode(p_calling_module, 'SELF_SERVICE', p_pm_source_reference , l_pm_source_reference_tbl(i)) -- added decode 3435905
243 ,decode(p_completed_flag_tbl(i),'Y','DLVR_COMPLETED','DLVR_IN_PROGRESS')
244 ,p_function_code_tbl(i)
245 ,p_pm_source_code
247 ,p_manager_person_id_tbl(i)
248 ,p_carrying_out_organization_id
249 ,1
250 ,fnd_global.login_id
251 ,fnd_global.prog_appl_id
252 ,p_attribute_category_tbl(i)
253 ,p_attribute1_tbl(i)
254 ,p_attribute2_tbl(i)
255 ,p_attribute3_tbl(i)
256 ,p_attribute4_tbl(i)
257 ,p_attribute5_tbl(i)
258 ,p_attribute6_tbl(i)
259 ,p_attribute7_tbl(i)
260 ,p_attribute8_tbl(i)
261 ,p_attribute9_tbl(i)
262 ,p_attribute10_tbl(i)
263 ,p_attribute11_tbl(i)
264 ,p_attribute12_tbl(i)
265 ,p_attribute13_tbl(i)
266 ,p_attribute14_tbl(i)
267 ,p_attribute15_tbl(i)
268 ,nvl(p_project_id,-99)
269 ,'PA_PROJECTS'
270 )
271 RETURNING proj_element_id
272 BULK COLLECT INTO l_proj_element_id_tbl ;
273
274
275 IF p_debug_mode = 'Y' THEN
276 pa_debug.g_err_stage := 'Bulk inserting into PA_PROJ_ELEMENT_VERSIONS';
277 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
278 END IF;
279
280
281 -- populate the element version table
282
283 -- From copy action API p_element_version_id_tbl will be passed
284 -- as null.Hence using nvl
285
286 FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
287 INSERT INTO PA_PROJ_ELEMENT_VERSIONS(
288 element_version_id
289 ,proj_element_id
290 ,object_type
291 ,project_id
292 ,creation_date
293 ,created_by
294 ,last_update_date
295 ,last_updated_by
296 ,record_version_number
297 ,last_update_login
298 ,source_object_id
299 ,source_object_type
300 )
301 VALUES
302 (
303 nvl(p_element_version_id_tbl(i),pa_proj_element_versions_s.nextval)
304 ,l_proj_element_id_tbl(i)
305 ,g_actions
306 ,nvl(p_project_id,-99)
307 ,SYSDATE
308 ,fnd_global.user_id
309 ,SYSDATE
310 ,fnd_global.user_id
311 ,1
312 ,fnd_global.login_id
313 ,nvl(p_project_id,-99)
314 ,'PA_PROJECTS'
315 )
316 RETURNING element_version_id
317 BULK COLLECT INTO l_element_version_id_tbl ;
318
319
320 IF p_debug_mode = 'Y' THEN
321 pa_debug.g_err_stage := 'Bulk inserting into PA_PROJ_ELEM_VER_SCHEDULE';
322 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
323 END IF;
324
325 -- populate the element version table
326 FORALL i in l_element_version_id_tbl.FIRST..l_element_version_id_tbl.LAST
327 INSERT INTO PA_PROJ_ELEM_VER_SCHEDULE(
328 pev_schedule_id
329 ,element_version_id
330 ,project_id
331 ,proj_element_id
332 ,creation_date
333 ,created_by
334 ,last_update_date
335 ,last_updated_by
336 ,last_update_login
337 ,scheduled_finish_date
338 ,actual_finish_date
339 ,record_version_number
340 ,source_object_id
341 ,source_object_type
342 )
343 VALUES
344 (
345 pa_proj_elem_ver_schedule_s.nextval
346 ,l_element_version_id_tbl(i)
347 ,nvl(p_project_id,-99)
348 ,l_proj_element_id_tbl(i)
349 ,SYSDATE
350 ,fnd_global.user_id
351 ,SYSDATE
352 ,fnd_global.user_id
353 ,fnd_global.login_id
354 ,p_due_date_tbl(i)
355 ,p_completion_date_tbl(i)
356 ,1
357 ,nvl(p_project_id,-99)
358 ,'PA_PROJECTS'
359 ) ;
360
361 IF p_object_type = g_dlvr_types THEN
362 l_rel_subtype := g_dlvr_type_to_action ;
363 ELSE
364 l_rel_subtype := g_dlvr_to_action ;
365 END IF;
366
367 IF p_debug_mode = 'Y' THEN
368 pa_debug.g_err_stage := 'Bulk inserting into PA_OBJECT_RELATIONSHIPS';
372 -- populate the object relationships table
369 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
370 END IF;
371
373 FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
374 INSERT INTO PA_OBJECT_RELATIONSHIPS(
375 object_relationship_id
376 ,object_type_from
377 ,object_id_from1
378 ,object_type_to
379 ,object_id_to1
380 ,relationship_type
381 ,created_by
382 ,creation_date
383 ,last_updated_by
384 ,last_update_date
385 ,object_id_from2
386 ,object_id_to2
387 ,relationship_subtype
388 ,record_version_number
389 ,last_update_login
390 )
391 VALUES
392 (
393 pa_object_relationships_s.nextval
394 ,p_object_type
395 ,p_object_version_id
396 ,g_actions
397 ,l_element_version_id_tbl(i)
398 ,'A'
399 ,fnd_global.user_id
400 ,SYSDATE
401 ,fnd_global.user_id
402 ,SYSDATE
403 ,p_object_id
404 ,l_proj_element_id_tbl(i)
405 ,l_rel_subtype
406 ,1
407 ,fnd_global.login_id
408 ) ;
409 END IF ;
410
411 IF p_debug_mode = 'Y' THEN --Added for bug 4945876
412 pa_debug.reset_curr_function;
413 END IF;
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 x_msg_count := 1;
419 x_msg_data := SQLERRM;
420 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_ACTIONS_PVT'
421 ,p_procedure_name => 'CREATE_DLV_ACTIONS_IN_BULK');
422 IF p_debug_mode = 'Y' THEN
423 pa_debug.g_err_stage := 'Exiting CREATE_DLV_ACTIONS_IN_BULK' ;
424 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
425 pa_debug.reset_curr_function;
426 END IF;
427 RAISE;
428 END CREATE_DLV_ACTIONS_IN_BULK ;
429
430 -- SubProgram : UPDATE_DLV_ACTIONS_IN_BULK
431 -- Type : PROCEDURE
432 -- Purpose : Private API to Update To Deliverable Actions
433 -- Note : Its a BULK API
434 -- Assumptions : None
435 -- Parameter IN/OUT Type Required Description and Purpose
436 -- --------------------------- --------- ---------- --------- ---------------------------
437 -- p_api_version IN NUMBER N Standard Parameter
438 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
439 -- p_commit IN VARCHAR2 N Standard Parameter
440 -- p_validate_only IN VARCHAR2 N Standard Parameter
441 -- p_validation_level IN NUMBER N Standard Parameter
442 -- p_calling_module IN VARCHAR2 N Standard Parameter
443 -- p_debug_mode IN VARCHAR2 N Standard Parameter
444 -- p_max_msg_count IN NUMBER N Standard Parameter
445 -- p_name_tbl IN PLSQL Table N Action Name
446 -- p_manager_person_id_tbl IN PLSQL Table N Manager Id
447 -- p_function_code_tbl IN PLSQL Table N Action Function
448 -- p_due_date_tbl IN PLSQL Table N Due Date
449 -- p_completed_flag_tbl IN PLSQL Table N Completed Flag
450 -- p_completion_date_tbl IN PLSQL Table N Completed Date
451 -- p_description_tbl IN PLSQL Table N Description
452 -- p_attribute_category_tbl IN PLSQL Table N DFF Field
453 -- p_attribute1_tbl IN PLSQL Table N DFF Filed
454 -- p_attribute2_tbl IN PLSQL Table N DFF Field
455 -- p_attribute3_tbl IN PLSQL Table N DFF Filed
456 -- p_attribute4_tbl IN PLSQL Table N DFF Field
457 -- p_attribute5_tbl IN PLSQL Table N DFF Filed
458 -- p_attribute6_tbl IN PLSQL Table N DFF Field
459 -- p_attribute7_tbl IN PLSQL Table N DFF Filed
460 -- p_attribute8_tbl IN PLSQL Table N DFF Field
461 -- p_attribute9_tbl IN PLSQL Table N DFF Filed
462 -- p_attribute10_tbl IN PLSQL Table N DFF Field
463 -- p_attribute11_tbl IN PLSQL Table N DFF Filed
464 -- p_attribute12_tbl IN PLSQL Table N DFF Field
465 -- p_attribute13_tbl IN PLSQL Table N DFF Filed
466 -- p_attribute14_tbl IN PLSQL Table N DFF Field
467 -- p_attribute15_tbl IN PLSQL Table N DFF Filed
468 -- p_element_version_id_tbl IN PLSQL Table N Action VErsion Id
469 -- p_proj_element_id_tbl IN PLSQL Table N Action Element Id
470 -- p_record_version_number_tbl IN PLSQL Table N Record Version NUmber
474 -- p_object_type IN VARCHAR2 Y Parent Type
471 -- p_project_id IN NUMBER N Project Id
472 -- p_object_id IN NUMBER Y Parent Id
473 -- p_object_version_id IN NUMBER N Parent Version ID
475 -- p_pm_source_code IN NUMBER N PM Source Code
476 -- p_pm_source_reference IN VARCHAR2 N PM Source Reference
477 -- p_carrying_out_organization_id IN VARCHAR2 N Carrying Out Org ID
478 -- x_return_status OUT VARCHAR2 N Mandatory Out Parameter
479 -- x_msg_count OUT NUMBER N Mandatory Out Parameter
480 -- x_msg_data OUT VARCHAR2 N Mandatory Out Parameter
481
482 PROCEDURE UPDATE_DLV_ACTIONS_IN_BULK
483 (p_api_version IN NUMBER :=1.0
484 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
485 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
486 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
487 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
488 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
489 ,p_debug_mode IN VARCHAR2 :='N'
490 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
491 ,p_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
492 ,p_manager_person_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
493 ,p_function_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
494 ,p_due_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE()
495 ,p_completed_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM. PA_VARCHAR2_1_TBL_TYPE()
496 ,p_completion_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE()
497 ,p_description_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE()
498 ,p_attribute_category_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
499 ,p_attribute1_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
500 ,p_attribute2_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
501 ,p_attribute3_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
502 ,p_attribute4_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
503 ,p_attribute5_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
504 ,p_attribute6_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
505 ,p_attribute7_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
506 ,p_attribute8_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
507 ,p_attribute9_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
508 ,p_attribute10_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
509 ,p_attribute11_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
510 ,p_attribute12_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
511 ,p_attribute13_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
512 ,p_attribute14_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
513 ,p_attribute15_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
514 ,p_element_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
515 ,p_proj_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
516 ,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
517 ,p_project_id IN PA_PROJECTS_ALL.PROJECT_ID%TYPE := null
518 ,p_object_id IN PA_OBJECT_RELATIONSHIPS.OBJECT_ID_TO1%TYPE := null -- 3578694 added default value
519 ,p_object_version_id IN PA_OBJECT_RELATIONSHIPS.OBJECT_ID_TO1%TYPE := null
520 ,p_object_type IN PA_LOOKUPS.LOOKUP_CODE%TYPE
521 ,p_pm_source_code IN pa_proj_elements.pm_source_code%TYPE := null
522 ,p_pm_source_reference IN pa_proj_elements.pm_source_reference%TYPE := null
523 ,p_carrying_out_organization_id IN pa_proj_elements.carrying_out_organization_id%TYPE := null
524 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
525 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
526 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
527 )
528 IS
529 l_msg_count NUMBER ;
530 l_data VARCHAR2(2000);
531 l_msg_data VARCHAR2(2000);
532 l_msg_index_out NUMBER;
533 BEGIN
534
535 x_msg_count := 0;
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 IF p_debug_mode = 'Y' THEN
539 PA_DEBUG.set_curr_function( p_function => 'CR_DLV_ACTIONS_IN_BULK'
540 ,p_debug_mode => p_debug_mode );
541 pa_debug.g_err_stage:= 'Inside UPDATE_DLV_ACTIONS_IN_BULK ';
542 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
543 END IF;
544
545 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
546 FND_MSG_PUB.initialize;
547 END IF;
548
549 -- Update attributes related to proj element table.
550 -- Following attributes related to PA_PROJ_ELEMENTS
551 -- are updateable from SS page
552 -- 1. Action Name
553 -- 2. Owner
554 -- 3. Function Code
558
555 -- 4. Description
556 -- 5. Completed Flag , mapped to status column
557 -- 6. DFF Fields
559 FORALL i in p_name_tbl.FIRST..p_name_tbl.LAST
560 UPDATE pa_proj_elements
561 SET name = p_name_tbl(i)
562 ,manager_person_id = p_manager_person_id_tbl(i)
563 ,description = p_description_tbl(i)
564 ,status_code = decode(p_completed_flag_tbl(i),'Y','DLVR_COMPLETED','DLVR_IN_PROGRESS')
565 ,function_code = p_function_code_tbl(i)
566 ,attribute_category = p_attribute_category_tbl(i)
567 ,attribute1 = p_attribute1_tbl(i)
568 ,attribute2 = p_attribute2_tbl(i)
569 ,attribute3 = p_attribute3_tbl(i)
570 ,attribute4 = p_attribute4_tbl(i)
571 ,attribute5 = p_attribute5_tbl(i)
572 ,attribute6 = p_attribute6_tbl(i)
573 ,attribute7 = p_attribute7_tbl(i)
574 ,attribute8 = p_attribute8_tbl(i)
575 ,attribute9 = p_attribute9_tbl(i)
576 ,attribute10 = p_attribute10_tbl(i)
577 ,attribute11 = p_attribute11_tbl(i)
578 ,attribute12 = p_attribute12_tbl(i)
579 ,attribute13 = p_attribute13_tbl(i)
580 ,attribute14 = p_attribute14_tbl(i)
581 ,attribute15 = p_attribute15_tbl(i)
582 ,record_version_number = nvl(record_version_number,0) + 1
583 ,last_update_date = SYSDATE
584 ,last_updated_by = fnd_global.user_id
585 ,last_update_login = fnd_global.login_id
586 WHERE proj_element_id = p_proj_element_id_tbl(i) ;
587
588
589 -- Update attributes related to proj elem ver schedule table.
590 -- Following attributes related to PA_PROJ_ELEM_VER_SVHEDULE
591 -- are updateable from SS page
592 -- 1. Due Date (Shedule Finish Date)
593 -- 2. Completion Date (Actual Finish Date)
594
595 FORALL i in p_element_version_id_tbl.FIRST..p_element_version_id_tbl.LAST
596 UPDATE PA_PROJ_ELEM_VER_SCHEDULE
597 SET scheduled_finish_date = p_due_date_tbl(i)
598 ,actual_finish_date = p_completion_date_tbl(i)
599 ,record_version_number = nvl(record_version_number,0) + 1
600 ,last_update_date = SYSDATE
601 ,last_updated_by = fnd_global.user_id
602 ,last_update_login = fnd_global.login_id
603 WHERE element_version_id = p_element_version_id_tbl(i) ;
604
605 -- 3941159 , added code to loop through actions and update event
606 -- date for billing actions
607
608 FOR i IN p_element_version_id_tbl.FIRST..p_element_version_id_tbl.LAST
609 LOOP
610
611 -- removed completion date not null check because, for billing action, completion check box can be
612 -- unchecked if billing event is not processed and changed completion date ( null value ) should be reflected in
613 -- pa_events table
614
615 IF p_function_code_tbl(i) = 'BILLING' THEN
616 PA_Billing_Wrkbnch_Events.Upd_Event_Comp_Date(
617 P_Deliverable_Id => p_object_version_id
618 ,P_Action_Id => p_element_version_id_tbl(i)
619 ,P_Event_Date => p_completion_date_tbl(i));
620 END IF;
621 END LOOP;
622
623 -- 3941159 end
624
625 IF p_debug_mode = 'Y' THEN
626 pa_debug.g_err_stage := 'Exiting UPDATE_DLV_ACTIONS_IN_BULK' ;
627 pa_debug.write(g_module_name, pa_debug.g_err_stage, 5);
628 pa_debug.reset_curr_function;
629 END IF;
630
631 EXCEPTION
632 WHEN OTHERS THEN
633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
634 x_msg_count := 1;
635 x_msg_data := SQLERRM;
636 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_ACTIONS_PVT'
637 ,p_procedure_name => 'UPDATE_DLV_ACTIONS_IN_BULK');
638 IF p_debug_mode = 'Y' THEN
639 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
640 pa_debug.write('UPDATE_DLV_ACTIONS_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
641 pa_debug.reset_curr_function;
642 END IF;
643 RAISE;
644 END UPDATE_DLV_ACTIONS_IN_BULK ;
645
646 -- SubProgram : DELETE_DLV_ACTIONS_IN_BULK
647 -- Type : PROCEDURE
648 -- Purpose : Private API to Delete To Deliverable Actions.This API will be called
649 -- when deliverable type is deleted. In such cases only object type and
650 -- object id will be passed .
651 -- Note : Its a BULK API
652 -- Assumptions : None
653 -- Parameter IN/OUT Type Required Description and Purpose
654 -- --------------------------- --------- ---------- --------- ---------------------------
655 -- p_api_version IN NUMBER N Standard Parameter
656 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
657 -- p_commit IN VARCHAR2 N Standard Parameter
658 -- p_validate_only IN VARCHAR2 N Standard Parameter
659 -- p_validation_level IN NUMBER N Standard Parameter
660 -- p_calling_module IN VARCHAR2 N Standard Parameter
661 -- p_debug_mode IN VARCHAR2 N Standard Parameter
662 -- p_max_msg_count IN NUMBER N Standard Parameter
666 -- p_object_id IN NUMBER Y Parent Id
663 -- p_element_version_id_tbl IN PLSQL Table N Action VErsion Id
664 -- p_proj_element_id_tbl IN PLSQL Table N Action Element Id
665 -- p_record_version_number_tbl IN PLSQL Table N Record Version NUmber
667 -- p_object_version_id IN NUMBER N Parent Version ID
668 -- p_object_type IN VARCHAR2 Y Parent Type
669 -- x_return_status OUT VARCHAR2 N Mandatory Out Parameter
670 -- x_msg_count OUT NUMBER N Mandatory Out Parameter
671 -- x_msg_data OUT VARCHAR2 N Mandatory Out Parameter
672
673 PROCEDURE DELETE_DLV_ACTIONS_IN_BULK
674 (p_api_version IN NUMBER :=1.0
675 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
676 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
677 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
678 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
679 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
680 ,p_debug_mode IN VARCHAR2 :='N'
681 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
682 ,p_object_type IN PA_LOOKUPS.LOOKUP_CODE%TYPE
683 ,p_object_id IN PA_OBJECT_RELATIONSHIPS.OBJECT_ID_TO1%TYPE
684 ,p_element_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
685 ,p_proj_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
686 ,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
687 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
688 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
689 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
690 )
691 IS
692
693 CURSOR get_element_id IS
694 SELECT object_id_to2,
695 object_relationship_id
696 FROM pa_object_relationships
697 WHERE object_id_from2 = p_object_id
698 AND object_type_from = p_object_type
699 AND object_type_to = g_actions
700 AND relationship_type = 'A';
701
702 l_proj_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
703 l_obj_relationship_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
704
705 -- Bug 3614361
706 l_elem_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
707 BEGIN
708 x_msg_count := 0;
709 x_return_status := FND_API.G_RET_STS_SUCCESS;
710
711 IF p_debug_mode = 'Y' THEN
712 PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_ACTIONS_IN_BULK'
713 ,p_debug_mode => p_debug_mode );
714 pa_debug.g_err_stage:= 'Inside DELETE_DLV_ACTIONS_IN_BULK ';
715 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
716 END IF;
717
718 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
719 FND_MSG_PUB.initialize;
720 END IF;
721
722 IF nvl(p_proj_element_id_tbl.LAST,0)=0 THEN
723
724 OPEN get_element_id ;
725 FETCH get_element_id BULK COLLECT INTO l_proj_element_id_tbl,l_obj_relationship_id_tbl ;
726 CLOSE get_element_id ;
727
728 IF nvl(l_proj_element_id_tbl.LAST,0) > 0 THEN
729 -- Delete from PA_PROJ_ELEMENTS Table
730 FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
731 DELETE FROM pa_proj_elements
732 WHERE proj_element_id = l_proj_element_id_tbl(i) ;
733
734 -- Delete from PA_PROJ_ELEMENT_VERSIONS Table
735 -- Bug 3614361 Including Returning Clause
736 FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
737 DELETE FROM pa_proj_element_versions
738 WHERE proj_element_id = l_proj_element_id_tbl(i)
739 RETURNING element_version_id
740 BULK COLLECT INTO l_elem_version_id_tbl;
741
742 -- Delete from PA_PROJ_ELEM_VER_SCHEDULE Table
743
744 /* Following Code has been commented for Performance Fix Bug # 3614361
745 Basing logic on Element Version Id will improve performance
746 in the below delete statement
747 FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
748 DELETE FROM pa_proj_elem_ver_schedule
749 WHERE proj_element_id = l_proj_element_id_tbl(i) ;
750 */
751
752 -- Bug 3614361 Start
753 FORALL i in l_elem_version_id_tbl.FIRST..l_elem_version_id_tbl.LAST
754 DELETE FROM pa_proj_elem_ver_schedule
755 WHERE element_version_id = l_elem_version_id_tbl(i) ;
756 -- 3614361 End
757
758 -- Delete from PA_OBJECT_RELATIONSHIPS table
759 FORALL i in l_obj_relationship_id_tbl.FIRST..l_obj_relationship_id_tbl.LAST
760 DELETE FROM PA_OBJECT_RELATIONSHIPS
761 WHERE object_relationship_id = l_obj_relationship_id_tbl(i) ;
762
763 END IF ;
764 END IF ;
765
766 -- Delete the entries from PA_OBJECT_RELATIONSHIPS table
767 IF nvl(p_proj_element_id_tbl.LAST,0)>0 THEN
768
769 IF p_debug_mode = 'Y' THEN
770 pa_debug.g_err_stage:='Delete entries from PA_OBJECT_RELATIONSHIPS table' ;
774 FORALL i in p_proj_element_id_tbl.FIRST..p_proj_element_id_tbl.LAST
771 pa_debug.write('DELETE_DLV_ACTIONS_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
772 END IF;
773
775 DELETE FROM PA_OBJECT_RELATIONSHIPS
776 WHERE OBJECT_ID_TO2 = p_proj_element_id_tbl(i)
777 AND OBJECT_ID_FROM2 = p_object_id ;
778 END IF ;
779
780 -- Delete the entries from PA_PROJ_ELEM_VER_SCHEDULE table
781 IF nvl(p_element_version_id_tbl.LAST,0)>0 THEN
782
783 IF p_debug_mode = 'Y' THEN
784 pa_debug.g_err_stage:='Delete entries from PA_PROJ_ELEM_VER_SCHEDULE table' ;
785 pa_debug.write('DELETE_DLV_ACTIONS_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
786 END IF;
787
788 FORALL i in p_element_version_id_tbl.FIRST..p_element_version_id_tbl.LAST
789 DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
790 WHERE element_version_id = p_element_version_id_tbl(i) ;
791
792 END IF ;
793
794 -- Delete the entries from PA_ELEMENT_VERSIONS table
795 IF nvl(p_element_version_id_tbl.LAST,0)>0 THEN
796
797 IF p_debug_mode = 'Y' THEN
798 pa_debug.g_err_stage:='Delete entries from PA_ELEMENT_VERSIONS table' ;
799 pa_debug.write('DELETE_DLV_ACTIONS_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
800 END IF;
801
802 FORALL i in p_element_version_id_tbl.FIRST..p_element_version_id_tbl.LAST
803 DELETE FROM PA_PROJ_ELEMENT_VERSIONS
804 WHERE element_version_id = p_element_version_id_tbl(i) ;
805
806 END IF ;
807
808
809 -- Delete the entries from PA_PROJ_ELEMENTS table
810 IF nvl(p_proj_element_id_tbl.LAST,0)>0 THEN
811
812 IF p_debug_mode = 'Y' THEN
813 pa_debug.g_err_stage:='Delete entries from PA_ELEMENT_VERSIONS table' ;
814 pa_debug.write('DELETE_DLV_ACTIONS_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
815 END IF;
816
817 FORALL i in p_proj_element_id_tbl.FIRST..p_proj_element_id_tbl.LAST
818 DELETE FROM PA_PROJ_ELEMENTS
819 WHERE proj_element_id = p_proj_element_id_tbl(i) ;
820
821 END IF ;
822
823 IF p_debug_mode = 'Y' THEN
824 pa_debug.g_err_stage := 'Exiting UPDATE_DLV_ACTIONS_IN_BULK' ;
825 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
826 pa_debug.reset_curr_function;
827 END IF;
828
829 EXCEPTION
830 WHEN OTHERS THEN
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 x_msg_count := 1;
833 x_msg_data := SQLERRM;
834 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_ACTIONS_PVT'
835 ,p_procedure_name => 'DELETE_DLV_ACTIONS_IN_BULK');
836 IF p_debug_mode = 'Y' THEN
837 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
838 pa_debug.write('DELETE_DLV_ACTIONS_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
839 pa_debug.reset_curr_function;
840 END IF;
841 RAISE;
842 END DELETE_DLV_ACTIONS_IN_BULK ;
843
844 -- SubProgram : DELETE_DLV_ACTION
845 -- Type : PROCEDURE
846 -- Purpose : Private API to Delete Deliverable Actions
847 -- Note :
848 -- Assumptions : None
849 -- Parameter IN/OUT Type Required Description and Purpose
850 -- --------------------------- --------- ---------- --------- ---------------------------
851 -- p_api_version IN NUMBER N Standard Parameter
852 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
853 -- p_commit IN VARCHAR2 N Standard Parameter
854 -- p_validate_only IN VARCHAR2 N Standard Parameter
855 -- p_validation_level IN NUMBER N Standard Parameter
856 -- p_calling_module IN VARCHAR2 N Standard Parameter
857 -- p_debug_mode IN VARCHAR2 N Standard Parameter
858 -- p_max_msg_count IN NUMBER N Standard Parameter
859 -- p_action_id IN NUMBER Y Action Id .
860 -- p_action_ver_id IN VARCHAR2 Y ACtion Ver Id.
861
862 PROCEDURE DELETE_DLV_ACTION
863 (p_api_version IN NUMBER :=1.0
864 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
865 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
866 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
867 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
868 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
869 ,p_debug_mode IN VARCHAR2 :='N'
870 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
871 ,p_action_id IN pa_proj_elements.proj_element_id%TYPE
872 ,p_action_ver_id IN pa_proj_element_versions.element_version_id%TYPE
873 ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
874 ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
875 ,p_function_code IN pa_proj_elements.function_code%TYPE
876 ,p_project_id IN pa_projects_all.project_id%TYPE
877 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
878 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
879 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
880 )
881 IS
882 l_action_del_allowed VARCHAR2(1) := 'Y';
883 l_msg_count NUMBER ;
887 l_name pa_proj_elements.name%TYPE ;
884 l_data VARCHAR2(2000);
885 l_msg_data VARCHAR2(2000);
886 l_msg_index_out NUMBER;
888 l_function_code pa_proj_elements.function_code%TYPE ;
889
890 l_dlv_ship_action_rec oke_amg_grp.dlv_ship_action_rec_type;
891 l_dlv_req_action_rec oke_amg_grp.dlv_req_action_rec_type;
892 l_dlv_ship_action_rec_b oke_amg_grp.dlv_ship_action_rec_type;
893 l_dlv_req_action_rec_b oke_amg_grp.dlv_req_action_rec_type;
894
895 CURSOR c_action_info (c_action_elt_id IN NUMBER )
896 IS
897 SELECT name
898 ,function_code
899 FROM pa_proj_elements
900 WHERE proj_element_id = c_action_elt_id
901 AND object_type = 'PA_ACTIONS';
902
903 BEGIN
904 x_msg_count := 0;
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
906
907 IF p_debug_mode = 'Y' THEN
908 PA_DEBUG.set_curr_function( p_function => 'PA_DELETE_DLV_ACTION'
909 ,p_debug_mode => p_debug_mode );
910 pa_debug.g_err_stage:= 'Inside DELETE_DLV_ACTION ';
911 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
912 END IF;
913
914 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
915 FND_MSG_PUB.initialize;
916 END IF;
917
918 PA_ACTIONS_PVT.IS_DELETE_ACTION_ALLOWED
919 (p_api_version => p_api_version
920 ,p_init_msg_list => FND_API.G_FALSE
921 ,p_commit => p_commit
922 ,p_validate_only => p_validate_only
923 ,p_validation_level => p_validation_level
924 ,p_calling_module => p_calling_module
925 ,p_debug_mode => p_debug_mode
926 ,p_max_msg_count => p_max_msg_count
927 ,p_action_id => p_action_id
928 ,p_action_ver_id => p_action_ver_id
929 ,p_dlv_element_id => p_dlv_element_id
930 ,p_dlv_version_id => p_dlv_version_id
931 ,p_function_code => p_function_code
932 ,p_project_id => p_project_id
933 ,x_action_del_allowed => l_action_del_allowed
934 ,x_return_status => x_return_status
935 ,x_msg_count => x_msg_count
936 ,x_msg_data => x_msg_data
937 ) ;
938
939 IF (l_action_del_allowed = 'N' OR x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
940 RAISE Invalid_Arg_Exc_Dlv ;
941 END IF ;
942
943 OPEN c_action_info(p_action_id) ;
944 FETCH c_action_info INTO l_name,l_function_code ;
945 CLOSE c_action_info ;
946
947 IF l_function_code = g_billing THEN
948
949 IF p_debug_mode = 'Y' THEN
950 pa_debug.g_err_stage:= 'Inside DELETE BILLING EVENTS ';
951 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
952 END IF;
953
954 -- Delete the billing event if its a billing function
955 PA_BILLING_WRKBNCH_EVENTS.DELETE_DELV_EVENT
956 ( P_Project_Id => p_project_id
957 ,P_Deliverable_Id => p_dlv_version_id
958 ,P_Action_Id => p_action_ver_id
959 ,P_Action_Name => l_name
960 ,X_Return_Status => x_return_status
961 ) ;
962 IF p_debug_mode = 'Y' THEN
963 pa_debug.write(g_module_name,'Returned from PA_BILLING_WRKBNCH_EVENTS.DELETE_DELV_EVENT ['||x_return_status||']',3) ;
964 END IF;
965
966 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
967 RAISE Invalid_Arg_Exc_Dlv ;
968 END IF ;
969
970 END IF ;
971
972 --Start Bug # 3431156
973
974 -- Delete the shipping or Procurement actions from OKE tables
975 IF l_function_code in( g_shipping,g_procurement) THEN
976 IF ( p_calling_module = 'SELF_SERVICE') THEN
977
978 -- removed oke's delete_action call, according to new stratergy, PA will not call oke's delete api
979 -- oke will set the row status to deleted in their AM method, which will be called by PA, and oke
980 -- will override EO's doDML method to handle delete case
981
982 -- OKE_DELIVERABLE_UTILS_PUB.DELETE_ACTION
983 -- ( P_Action_ID => p_action_ver_id
984 -- , X_Return_Status => x_return_status
985 -- , X_Msg_Count => x_msg_count
986 -- , X_Msg_Data => x_msg_data
987 -- ) ;
988
989 IF p_debug_mode = 'Y' THEN
990 pa_debug.write(g_module_name,'Returned from OKE_DELIVERABLE_UTILS_PUB.DELETE_ACTION ['||x_return_status||']',3) ;
991 END IF;
992
993 -- IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
994 -- RAISE Invalid_Arg_Exc_Dlv ;
995 -- END IF ;
996
997 ELSIF (p_calling_module = 'AMG' and l_function_code = g_procurement) THEN
998
999 l_dlv_req_action_rec.pa_action_id := p_action_ver_id;
1000
1001 oke_amg_grp.manage_dlv_action
1002 ( p_api_version => p_api_version
1003 , p_init_msg_list => FND_API.G_FALSE
1004 , p_commit => p_commit
1005 , p_action => 'DELETE'
1006 -- 3732873 earlier l_function_code was passed for p_dlv_action_type
1010 , p_master_inv_org_id => null
1007 -- and the value will be 'PROCUREMENT', but OKE expects this value to be 'REQ' for procurement
1008 -- if the value is not 'WSH' or 'REQ' , oke will throw the error message saying invalid action type
1009 , p_dlv_action_type => 'REQ'
1011 , p_item_dlv => null
1012 , p_dlv_ship_action_rec => l_dlv_ship_action_rec_b
1013 , p_dlv_req_action_rec => l_dlv_req_action_rec
1014 , x_return_status => x_return_status
1015 , x_msg_data => x_msg_data
1016 , x_msg_count => x_msg_count
1017 );
1018
1019 IF p_debug_mode = 'Y' THEN
1020 pa_debug.write(g_module_name,'Returned from oke_amg_grp.manage_dlv_action['||l_function_code||'] ['||x_return_status||']',3) ;
1021 END IF;
1022
1023 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1024 RAISE Invalid_Arg_Exc_Dlv ;
1025 END IF ;
1026
1027 ELSIF (p_calling_module = 'AMG' and l_function_code = g_shipping) THEN
1028
1029 l_dlv_ship_action_rec.pa_action_id := p_action_ver_id;
1030
1031 oke_amg_grp.manage_dlv_action
1032 ( p_api_version => p_api_version
1033 , p_init_msg_list => FND_API.G_FALSE
1034 , p_commit => p_commit
1035 , p_action => 'DELETE'
1036 -- 3732873 earlier l_function_code was passed for p_dlv_action_type
1037 -- and the value will be 'SHIPPING', but OKE expects this value to be 'WSH' for shipping
1038 -- if the value is not 'WSH' or 'REQ' , oke will throw the error message saying invalid action type
1039 , p_dlv_action_type => 'WSH'
1040 , p_master_inv_org_id => null
1041 , p_item_dlv => null
1042 , p_dlv_ship_action_rec => l_dlv_ship_action_rec
1043 , p_dlv_req_action_rec => l_dlv_req_action_rec_b
1044 , x_return_status => x_return_status
1045 , x_msg_data => x_msg_data
1046 , x_msg_count => x_msg_count
1047 );
1048
1049 IF p_debug_mode = 'Y' THEN
1050 pa_debug.write(g_module_name,'Returned from oke_amg_grp.manage_dlv_action['||l_function_code||'] ['||x_return_status||']',3) ;
1051 END IF;
1052
1053 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1054 RAISE Invalid_Arg_Exc_Dlv ;
1055 END IF ;
1056
1057 END IF; --p_calling_module = 'AMG'
1058 END IF ;
1059
1060 -- End Bug # 3431156
1061
1062 -- Delete from PA_PROJ_ELEMENTS Table
1063 DELETE FROM pa_proj_elements
1064 WHERE proj_element_id = p_action_id ;
1065
1066 -- Delete from PA_PROJ_ELEMENT_VERSIONS Table
1067 DELETE FROM pa_proj_element_versions
1068 WHERE element_version_id = p_action_ver_id ;
1069
1070 -- Delete from PA_PROJ_ELEM_VER_SCHEDULE Table
1071 DELETE FROM pa_proj_elem_ver_schedule
1072 WHERE element_version_id = p_action_ver_id ;
1073
1074 -- Delete from PA_OBJECT_RELATIONSHIPS table
1075 DELETE FROM PA_OBJECT_RELATIONSHIPS
1076 WHERE object_id_to2 = p_action_id
1077 and object_id_to1 = p_action_ver_id; -- Added condition for perf bug# 3964701
1078
1079 IF p_debug_mode = 'Y' THEN
1080 pa_debug.g_err_stage := 'Exiting DELETE_DLV_ACTION' ;
1081 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1082 pa_debug.reset_curr_function;
1083 END IF;
1084
1085 EXCEPTION
1086 WHEN Invalid_Arg_Exc_Dlv THEN
1087 x_return_status := FND_API.G_RET_STS_ERROR;
1088 l_msg_count := FND_MSG_PUB.count_msg;
1089 IF p_debug_mode = 'Y' THEN
1090 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_ACTION';
1091 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1092 END IF;
1093
1094 IF x_msg_count = 1 THEN
1095 PA_INTERFACE_UTILS_PUB.get_messages
1096 (p_encoded => FND_API.G_FALSE,
1097 p_msg_index => 1,
1098 p_msg_count => l_msg_count,
1099 p_msg_data => l_msg_data,
1100 p_data => l_data,
1101 p_msg_index_out => l_msg_index_out);
1102 x_msg_data := l_data;
1103 x_msg_count := l_msg_count;
1104 ELSE
1105 x_msg_count := l_msg_count;
1106 END IF;
1107 IF p_debug_mode = 'Y' THEN
1108 pa_debug.reset_curr_function;
1109 END IF ;
1110 RETURN;
1111 WHEN OTHERS THEN
1112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1113 x_msg_count := 1;
1114 x_msg_data := SQLERRM;
1115
1116 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_ACTIONS_PVT'
1117 ,p_procedure_name => 'DELETE_DLV_ACTION');
1118
1119 IF p_debug_mode = 'Y' THEN
1120 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1121 pa_debug.write(g_module_name, 'DELETE_DLV_ACTION: ' ||pa_debug.g_err_stage,5);
1122 pa_debug.reset_curr_function;
1123 END IF;
1124 RAISE;
1125 END DELETE_DLV_ACTION ;
1126
1127 PROCEDURE IS_DELETE_ACTION_ALLOWED
1128 (p_api_version IN NUMBER :=1.0
1129 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
1130 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
1131 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
1135 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1132 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
1133 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
1134 ,p_debug_mode IN VARCHAR2 :='N'
1136 ,p_action_id IN pa_proj_elements.proj_element_id%TYPE
1137 ,p_action_ver_id IN pa_proj_element_versions.element_version_id%TYPE
1138 ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
1139 ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
1140 ,p_function_code IN pa_proj_elements.function_code%TYPE
1141 ,p_project_id IN pa_projects_all.project_id%TYPE
1142 ,x_action_del_allowed IN VARCHAR2
1143 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1144 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1145 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1146 )
1147 IS
1148 l_err_message fnd_new_messages.message_name%TYPE ;
1149 l_shipping_initiated VARCHAR2(1) := 'N' ;
1150 l_proc_initiated VARCHAR2(1) := 'N' ;
1151
1152 BEGIN
1153 x_msg_count := 0;
1154 x_return_status := FND_API.G_RET_STS_SUCCESS;
1155
1156 IF p_debug_mode = 'Y' THEN
1157 PA_DEBUG.set_curr_function( p_function => 'IS_DELETE_ACTION_ALLOWED'
1158 ,p_debug_mode => p_debug_mode );
1159 pa_debug.g_err_stage:= 'Inside IS_DELETE_ACTION_ALLOWED ';
1160 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1161 END IF;
1162
1163 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1164 FND_MSG_PUB.initialize;
1165 END IF;
1166
1167 --Bug 3512346 The Following Check (CHECK_DELV_EVENT_PROCESSED) has been uncommented.Also,passing p_Action_ver_id for the API
1168 --This change has been done by avaithia on Apr 1st,2004 (This is not a part of actual resolution for the bug.This is just an
1169 --additional fix included as a part of Bug 3512346
1170
1171 IF p_function_code = g_billing THEN
1172 IF nvl(PA_BILLING_WRKBNCH_EVENTS.CHECK_DELV_EVENT_PROCESSED(p_project_id,p_dlv_version_id,p_action_ver_id),'Y') = 'Y' THEN /* changes done for bug 8890368 */
1173 l_err_message := 'PA_BILLING_ACTION_DEL_ERR' ; /* reverted the flag to 'Y' from 'N' in above for bug 9278197 */
1174 END IF ;
1175 pa_debug.g_err_stage := 'Exiting IS_DELETE_ACTION_ALLOWED' ;
1176 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1177 ELSIF p_function_code = g_shipping THEN
1178 -- 3570283 changed parameter from p_dlv_version_id to p_action_ver_id
1179 -- api is expecting action version id and was passed deliverable version id
1180
1181 -- 3555460 added validation for shipping action initiation
1182 -- if shipping is initiated and user is deleting shipping action,
1183 -- throw error message for shipping initiation
1184 -- else
1185 -- check for ready to ship flag for shipping action
1186 -- if ready to ship flag is set
1187 -- throw error message for ready to ship
1188 -- else
1189 -- allow shipping action deletion
1190 -- end if
1191
1192 l_shipping_initiated := nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(p_action_ver_id),'N') ;
1193
1194 IF l_shipping_initiated = 'Y' THEN
1195 l_err_message := 'PA_SHIP_ACTN_INIT_DEL_ERR';
1196 ELSE
1197 IF nvl(OKE_DELIVERABLE_UTILS_PUB.Ready_To_Ship_Yn(p_action_ver_id),'N') = 'Y' THEN
1198 l_err_message := 'PA_SHIPPING_ACTION_DEL_ERR' ;
1199 END IF ;
1200 END IF;
1201 ELSIF p_function_code = g_procurement THEN
1202 -- 3570283 changed parameter from p_dlv_version_id to p_action_ver_id
1203 -- api is expecting action version id and was passed deliverable version id
1204
1205 -- 3555460 added validation for procurement action initiation
1206 -- if procurement is initiated and user is deleting procurement action,
1207 -- throw error message for procurement initiation
1208 -- else
1209 -- check for ready to procure flag for procuremetn action
1210 -- if ready to procure flag is set
1211 -- throw error message for ready to procure
1212 -- else
1213 -- allow procurement action deletion
1214 -- end if
1215
1216 l_proc_initiated := nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(p_action_ver_id),'N') ;
1217
1218 IF l_proc_initiated = 'Y' THEN
1219 l_err_message := 'PA_PROC_ACTN_INIT_DEL_ERR';
1220 ELSE
1221 IF nvl(OKE_DELIVERABLE_UTILS_PUB.Ready_To_Procure_Yn(p_action_ver_id),'N') = 'Y' THEN
1222 l_err_message := 'PA_PROCUREMENT_ACTION_DEL_ERR' ;
1223 END IF ;
1224 END IF ;
1225 END IF ;
1226
1227 IF l_err_message IS NOT NULL THEN
1228 PA_UTILS.ADD_MESSAGE('PA',l_err_message);
1229 x_return_status := 'E';
1230 END IF ;
1231
1232 IF p_debug_mode = 'Y' THEN
1233 pa_debug.g_err_stage := 'Exiting IS_DELETE_ACTION_ALLOWED' ;
1234 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1235 pa_debug.reset_curr_function;
1236 END IF;
1237
1238 EXCEPTION
1239 WHEN OTHERS THEN
1240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1241 x_msg_count := 1;
1242 x_msg_data := SQLERRM;
1243
1244 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_ACTIONS_PVT'
1245 ,p_procedure_name => 'IS_DELETE_ACTION_ALLOWED');
1246
1247 IF p_debug_mode = 'Y' THEN
1248 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1249 pa_debug.write('IS_DELETE_ACTION_ALLOWED: ' || g_module_name,pa_debug.g_err_stage,5);
1250 pa_debug.reset_curr_function;
1251 END IF;
1252 RAISE;
1253 END IS_DELETE_ACTION_ALLOWED;
1254
1255 -- SubProgram : COPY_ACTIONS
1256 -- Type : PROCEDURE
1257 -- Purpose : Private API to Copy Actions From Source To Destination
1258 -- Note : Its a BULK API
1259 -- Assumptions : None
1260 -- Parameter IN/OUT Type Required Description and Purpose
1261 -- --------------------------- --------- ---------- --------- ---------------------------
1262 -- p_api_version IN NUMBER N Standard Parameter
1263 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
1264 -- p_commit IN VARCHAR2 N Standard Parameter
1265 -- p_validate_only IN VARCHAR2 N Standard Parameter
1266 -- p_validation_level IN NUMBER N Standard Parameter
1267 -- p_calling_module IN VARCHAR2 N Standard Parameter
1268 -- p_debug_mode IN VARCHAR2 N Standard Parameter
1269 -- p_max_msg_count IN NUMBER N Standard Parameter
1270 -- p_source_object_id IN NUMBER Y Source Object Id
1271 -- p_source_object_type IN VARCHAR2 Y Source Object Type
1272 -- p_target_object_id IN NUMBER Y Target Object Id
1273 -- p_target_object_type IN VARCHAR2 Y Target Object Type
1274 -- p_project_id IN NUMBER Y Project Id
1275 -- p_task_id IN NUMBER N Task Id
1276 -- p_pm_source_reference IN VARCHAR2 N PM Source Reference
1277 -- p_carrying_out_organization_id IN VARCHAR2 N Carrying Out Org ID
1278 -- p_insert_or_update IN VARCHAR2 N Identifies the API Mode
1279 -- x_return_status OUT VARCHAR2 N Mandatory Out Parameter
1280 -- x_msg_count OUT NUMBER N Mandatory Out Parameter
1281 -- x_msg_data OUT VARCHAR2 N Mandatory Out Parameter
1282
1283 PROCEDURE COPY_ACTIONS
1284 (p_api_version IN NUMBER :=1.0
1285 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
1286 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
1287 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
1288 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
1289 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
1290 ,p_debug_mode IN VARCHAR2 :='N'
1291 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1292 ,p_source_object_id IN pa_object_relationships.object_id_from2%TYPE
1293 ,p_source_object_type IN pa_object_relationships.object_type_from%TYPE
1294 ,p_target_object_id IN pa_object_relationships.object_id_from2%TYPE
1295 ,p_target_object_type IN pa_object_relationships.object_type_from%TYPE
1296 ,p_source_project_id IN pa_projects_all.project_id%TYPE
1297 ,p_target_project_id IN pa_projects_all.project_id%TYPE
1298 ,p_task_id IN pa_proj_elements.proj_element_id%TYPE := null
1299 ,p_task_ver_id IN pa_proj_element_versions.element_version_id%TYPE := null
1300 ,p_carrying_out_organization_id IN pa_proj_elements.carrying_out_organization_id%TYPE := null
1301 ,p_pm_source_reference IN pa_proj_elements.pm_source_reference%TYPE := null
1302 ,p_pm_source_code IN pa_proj_elements.pm_source_code%TYPE := null
1303 ,p_calling_mode IN VARCHAR2 := NULL -- Added for bug# 3911050
1304 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1305 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1306 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1307 )
1308 IS
1309
1310 --Bug 3614361
1311 l_proj_id pa_projects_all.project_id%TYPE;
1312
1313 CURSOR get_source_actions IS
1314 SELECT ppe.name
1315 ,ppe.manager_person_id
1316 ,ppe.function_code
1317 ,psc.scheduled_finish_date
1318 ,'N'
1319 -- Bug 3665911 Action Completion date should not get copied ,psc.actual_finish_date
1320 ,null -- Hence passing the completion date as null
1321 ,ppe.description
1322 ,ppe.attribute_category
1323 ,ppe.attribute1
1324 ,ppe.attribute2
1325 ,ppe.attribute3
1326 ,ppe.attribute4
1327 ,ppe.attribute5
1328 ,ppe.attribute6
1329 ,ppe.attribute7
1330 ,ppe.attribute8
1331 ,ppe.attribute9
1332 ,ppe.attribute10
1333 ,ppe.attribute11
1334 ,ppe.attribute12
1335 ,ppe.attribute13
1336 ,ppe.attribute14
1337 ,ppe.attribute15
1338 -- ,null --Commented for Bug # 3431156 --This corresponds to the target action version id
1339 -- ,null --Commented for Bug # 3431156 --This corresponds to target action projelementid
1343 ,pa_tasks_s.nextval -- Target Action Proj Element Id -- Included for Bug # 3431156
1340 ,ppe.proj_element_id --Source Action Proj Element Id -- Included for Bug # 3431156
1341 ,ppv.element_version_id --Source Action Version Id -- Included for Bug # 3431156
1342 ,pa_proj_element_versions_s.nextval -- Target Action Version Id -- Included for Bug # 3431156
1344 ,null -- record version number
1345 FROM pa_proj_elements ppe,
1346 pa_proj_element_versions ppv,
1347 pa_proj_elem_ver_schedule psc,
1348 pa_object_relationships obj,
1349 pa_projects_all pa,
1350 pa_project_types_all ppt
1351 WHERE obj.object_id_from2 = p_source_object_id
1352 AND obj.object_type_from = p_source_object_type
1353 AND obj.relationship_type = 'A'
1354 AND ppe.object_type = g_actions
1355 AND ppe.project_id = l_proj_id /*3614361*/
1356 AND ppv.project_id = l_proj_id /*3614361*/
1357 AND psc.project_id = l_proj_id /*3614361*/
1358 AND obj.object_id_to2 = ppe.proj_element_id
1359 AND ppe.proj_element_id = ppv.proj_element_id
1360 AND ppv.element_version_id = psc.element_version_id
1361 AND pa.project_id = p_target_project_id
1362 AND pa.project_type = ppt.project_type
1363 AND pa.org_id = ppt.org_id
1364 AND decode(ppt.project_type_class_code,'CONTRACT','X',ppe.function_code) <> 'BILLING'
1365 AND DECODE(ppe.function_code,'PROCUREMENT','N','SHIPPING','N',PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(pa.project_id))=PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(pa.project_id);--16551563
1366
1367
1368 l_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1369 l_mgr_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
1370 l_function_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE() ;
1371 l_due_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE() ;
1372 l_comp_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM. PA_VARCHAR2_1_TBL_TYPE() ;
1373 l_comp_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE() ;
1374 l_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
1375 l_element_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
1376 l_rec_ver_num_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
1377 l_description_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
1378 l_attribute_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE() ;
1379 l_attribute1_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1380 l_attribute2_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1381 l_attribute3_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1382 l_attribute4_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1383 l_attribute5_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1384 l_attribute6_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1385 l_attribute7_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1386 l_attribute8_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1387 l_attribute9_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1388 l_attribute10_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1389 l_attribute11_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1390 l_attribute12_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1391 l_attribute13_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1392 l_attribute14_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1393 l_attribute15_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
1394
1395 l_carrying_out_org_id PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE ;
1396 l_object_version_id PA_OBJECT_RELATIONSHIPS.OBJECT_ID_TO1%TYPE := null ; --The target Deliverable version id
1397
1398 --Start Bug # 3431156
1399 l_source_object_ver_id PA_OBJECT_RELATIONSHIPS.OBJECT_ID_FROM1%TYPE :=null ; --The source Deliverable version id
1400 l_source_action_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ; --The Source Action Id
1401 l_source_action_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE(); --The Source Action Version Id
1402 --End --Bug # 3431156
1403
1404 l_project_mode VARCHAR2(30) := 'PROJECT' ;
1405 l_dummy VARCHAR2(240) ;
1406
1407 l_debug_mode VARCHAR2(10);
1408 l_msg_count NUMBER ;
1409 l_data VARCHAR2(2000);
1410 l_msg_data VARCHAR2(2000);
1411 l_msg_index_out NUMBER;
1412
1413 l_action_type VARCHAR2(5); -- 3573134 added variable to set action type for
1414 -- oke actions
1415
1416 --Commented for Bug # 3431156
1417 /*CURSOR get_version_id
1418 IS
1419 SELECT element_version_id
1420 FROM pa_proj_element_versions
1421 WHERE proj_element_id = l_target_object_id
1422 AND object_type = 'PA_DELIVERABLES' ;*/
1423
1424 --Bug # 3431156 Modified the cursor to fetch
1428 SELECT element_version_id
1425 --either the source deliverable version id/target deliverable version id
1426 CURSOR get_version_id (l_dlv_id NUMBER)
1427 IS
1429 FROM pa_proj_element_versions
1430 WHERE proj_element_id = l_dlv_id
1431 AND object_type = 'PA_DELIVERABLES' ;
1432
1433 CURSOR get_project_mode
1434 IS
1435 SELECT 'TEMPLATE'
1436 FROM pa_projects_all
1437 WHERE project_id = p_target_project_id
1438 AND template_flag = 'Y';
1439 BEGIN
1440
1441 x_msg_count := 0;
1442 x_return_status := FND_API.G_RET_STS_SUCCESS;
1443
1444 IF p_debug_mode = 'Y' THEN
1445 PA_DEBUG.set_curr_function( p_function => 'COPY_ACTIONS'
1446 ,p_debug_mode => p_debug_mode );
1447 pa_debug.g_err_stage:= 'Inside COPY_ACTIONS ';
1448 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1449 END IF;
1450
1451 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1452 FND_MSG_PUB.initialize;
1453 END IF;
1454
1455 OPEN get_project_mode ;
1456 FETCH get_project_mode INTO l_project_mode ;
1457 CLOSE get_project_mode ;
1458
1459 --Fetch all the actions from the source to
1460 --PLSQL table.
1461 IF p_debug_mode = 'Y' THEN
1462 pa_debug.g_err_stage:= 'Before fetch:get_source_actions : ';
1463 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1464 END IF;
1465
1466 -- Bug 3614361
1467 IF p_source_object_type = g_dlvr_types THEN
1468 l_proj_id := -99 ;
1469 ELSE
1470 l_proj_id := p_source_project_id ;
1471 END IF;
1472 -- Bug 3614361
1473
1474 OPEN get_source_actions ;
1475 FETCH get_source_actions BULK COLLECT INTO
1476 l_name_tbl
1477 ,l_mgr_person_id_tbl
1478 ,l_function_code_tbl
1479 ,l_due_date_tbl
1480 ,l_comp_flag_tbl
1481 ,l_comp_date_tbl
1482 ,l_description_tbl
1483 ,l_attribute_category_tbl
1484 ,l_attribute1_tbl
1485 ,l_attribute2_tbl
1486 ,l_attribute3_tbl
1487 ,l_attribute4_tbl
1488 ,l_attribute5_tbl
1489 ,l_attribute6_tbl
1490 ,l_attribute7_tbl
1491 ,l_attribute8_tbl
1492 ,l_attribute9_tbl
1493 ,l_attribute10_tbl
1494 ,l_attribute11_tbl
1495 ,l_attribute12_tbl
1496 ,l_attribute13_tbl
1497 ,l_attribute14_tbl
1498 ,l_attribute15_tbl
1499 ,l_source_action_id_tbl --Source action id
1500 ,l_source_action_ver_id_tbl --Source action version id
1501 ,l_element_ver_id_tbl --target action version id (already available)
1502 ,l_element_id_tbl --target action id (already available)
1503 ,l_rec_ver_num_id_tbl ;
1504 CLOSE get_source_actions ;
1505
1506 IF p_debug_mode = 'Y' THEN
1507 pa_debug.g_err_stage:= 'After fetch:get_source_actions : ';
1508 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1509 pa_debug.g_err_stage:= ':l_name_tbl.LAST: '||l_name_tbl.LAST;
1510 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1511 END IF;
1512 --Following code commented for Bug # 3431156
1513 /* -- Get the element version id of the deliverable
1514 IF p_target_object_type = 'PA_DELIVERABLES' THEN
1515 OPEN get_version_id
1516 FETCH get_version_id INTO l_object_version_id ;
1517 CLOSE get_version_id ;
1518 END IF ;*/
1519
1520 --Passing p_target_object_id to the cursor as the cursor has been
1521 --modified for Bug # 3431156
1522
1523 -- Get the target element version id of the deliverable
1524 IF p_target_object_type = 'PA_DELIVERABLES' THEN
1525 OPEN get_version_id (p_target_object_id);
1526 FETCH get_version_id INTO l_object_version_id ;
1527 CLOSE get_version_id ;
1528 END IF ;
1529
1530 IF nvl(l_name_tbl.LAST,0)>0 THEN
1531 FOR i in l_name_tbl.FIRST..l_name_tbl.LAST LOOP
1532
1533 IF l_due_date_tbl(i) is NULL THEN
1534 -- Get default action date
1535 PA_DELIVERABLE_UTILS.GET_DEFAULT_ACTION_DATE
1536 ( p_dlvr_ver_id => l_object_version_id
1537 ,p_task_ver_id => p_task_ver_id
1538 ,p_project_mode => l_project_mode
1539 ,p_function_code => l_function_code_tbl(i)
1540 ,x_due_date => l_due_date_tbl(i)
1541 ) ;
1542 END IF ;
1543
1544 IF l_mgr_person_id_tbl(i) IS NULL THEN
1545 -- Get default action owner
1546 PA_DELIVERABLE_UTILS.GET_DEFAULT_ACTION_OWNER
1547 (p_dlvr_ver_id => l_object_version_id
1548 ,x_owner_id => l_mgr_person_id_tbl(i)
1549 ,x_owner_name => l_dummy
1550 ) ;
1551 END IF ;
1552 END LOOP ;
1553 END IF ;
1554 IF p_debug_mode = 'Y' THEN
1555 pa_debug.g_err_stage:= 'Before calling CREATE_DLV_ACTIONS_IN_BULK ';
1556 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1557 END IF;
1558
1562 (p_api_version => p_api_version
1559 -- If some thing is fetched call CREATE_DLV_ACTIONS_INBULK API
1560 IF nvl(l_name_tbl.LAST,0) > 0 THEN
1561 PA_ACTIONS_PVT.CREATE_DLV_ACTIONS_IN_BULK
1563 ,p_init_msg_list => p_init_msg_list
1564 ,p_commit => p_commit
1565 ,p_validate_only => p_validate_only
1566 ,p_validation_level => p_validation_level
1567 ,p_calling_module => p_calling_module
1568 ,p_debug_mode => p_debug_mode
1569 ,p_max_msg_count => p_max_msg_count
1570 ,p_name_tbl => l_name_tbl
1571 ,p_manager_person_id_tbl => l_mgr_person_id_tbl
1572 ,p_function_code_tbl => l_function_code_tbl
1573 ,p_due_date_tbl => l_due_date_tbl
1574 ,p_completed_flag_tbl => l_comp_flag_tbl
1575 ,p_completion_date_tbl => l_comp_date_tbl
1576 ,p_description_tbl => l_description_tbl
1577 ,p_attribute_category_tbl => l_attribute_category_tbl
1578 ,p_attribute1_tbl => l_attribute1_tbl
1579 ,p_attribute2_tbl => l_attribute2_tbl
1580 ,p_attribute3_tbl => l_attribute3_tbl
1581 ,p_attribute4_tbl => l_attribute4_tbl
1582 ,p_attribute5_tbl => l_attribute5_tbl
1583 ,p_attribute6_tbl => l_attribute6_tbl
1584 ,p_attribute7_tbl => l_attribute7_tbl
1585 ,p_attribute8_tbl => l_attribute8_tbl
1586 ,p_attribute9_tbl => l_attribute9_tbl
1587 ,p_attribute10_tbl => l_attribute10_tbl
1588 ,p_attribute11_tbl => l_attribute11_tbl
1589 ,p_attribute12_tbl => l_attribute12_tbl
1590 ,p_attribute13_tbl => l_attribute13_tbl
1591 ,p_attribute14_tbl => l_attribute14_tbl
1592 ,p_attribute15_tbl => l_attribute15_tbl
1593 ,p_element_version_id_tbl => l_element_ver_id_tbl
1594 ,p_proj_element_id_tbl => l_element_id_tbl
1595 ,p_record_version_number_tbl => l_rec_ver_num_id_tbl
1596 ,p_project_id => p_target_project_id
1597 ,p_object_id => p_target_object_id
1598 ,p_object_version_id => l_object_version_id
1599 ,p_object_type => p_target_object_type
1600 ,p_pm_source_code => p_pm_source_code
1601 ,p_pm_source_reference => p_pm_source_reference
1602 ,p_carrying_out_organization_id => p_carrying_out_organization_id
1603 ,x_return_status => x_return_status
1604 ,x_msg_count => x_msg_count
1605 ,x_msg_data => x_msg_data
1606 ) ;
1607
1608 END IF ;
1609
1610 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1611 RAISE Invalid_Arg_Exc_Dlv ;
1612 END IF ;
1613
1614 --Start Bug # 3431156
1615
1616 IF p_source_object_type = 'PA_DELIVERABLES' THEN
1617 -- Get the source element version id of the deliverable
1618 OPEN get_version_id (p_source_object_id);
1619 FETCH get_version_id INTO l_source_object_ver_id ;
1620 CLOSE get_version_id ;
1621
1622 IF p_debug_mode = 'Y' THEN
1623 pa_debug.g_err_stage:= 'The source element version id(deliverable)is :'|| l_source_object_ver_id;
1624 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1625 END IF;
1626
1627 --If something is fetched Call the Copy Action API of OKE
1628 IF nvl(l_name_tbl.LAST,0)>0 THEN
1629
1630 IF p_debug_mode = 'Y' THEN
1631 pa_debug.g_err_stage:= 'Before calling OKE_DELIVERABLE_UTILS_PUB.Copy_Action ';
1632 pa_debug.write(g_module_name,pa_debug.g_err_stage,5) ;
1633 END IF;
1634
1635 -- 3612702 : when deliverable actions are copied based on due date
1636 -- defaulting logic dates should be populated,
1637 -- added one parameter to take due date, oke will populate this date in their table
1638 -- and dlvr action due date and txn dates will be in synch when actions are copied
1639
1640 FOR i in l_name_tbl.FIRST..l_name_tbl.LAST LOOP
1641 OKE_DELIVERABLE_UTILS_PUB.Copy_Action
1642 ( P_Source_Project_ID => p_source_project_id
1643 , P_Target_Project_ID => p_target_project_id
1644 , P_Source_Deliverable_ID => l_source_object_ver_id
1645 , P_Target_Deliverable_ID => l_object_version_id
1646 , P_Source_Action_ID => l_source_action_ver_id_tbl(i)
1647 , P_Target_Action_ID => l_element_ver_id_tbl(i)
1648 , P_Target_Action_Name => l_name_tbl(i)
1649 , P_Target_Action_Date => l_due_date_tbl(i) -- 3612702 added new parameter
1650 , X_Return_Status => x_return_status
1651 , X_Msg_Count => x_msg_count
1652 , X_Msg_Data => x_msg_data );
1653 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1654 RAISE Invalid_Arg_Exc_Dlv ;
1655 END IF ;
1656
1657 END LOOP ;
1658
1662 END IF;
1659 IF p_debug_mode = 'Y' THEN
1660 pa_debug.g_err_stage:= 'After calling OKE_DELIVERABLE_UTILS_PUB.Copy_Action ';
1661 pa_debug.write(g_module_name,pa_debug.g_err_stage,5) ;
1663
1664 END IF ;
1665
1666 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1667 RAISE Invalid_Arg_Exc_Dlv ;
1668 END IF ;
1669
1670 -- 3573134 added else if statement for deliverable type
1671 -- when ever deliverable defaults actions from type , action entry for defaulted shipping and
1672 -- procurement should be created in oke table also
1673 -- if defaulted action is of type shipping or procurement, call default_action oke api to
1674 -- default it to oke table
1675
1676 ELSIF p_source_object_type = 'PA_DLVR_TYPES' THEN
1677
1678 -- 3911050 reverted back the old changes
1679 -- Added a condition to check p_calling_mode, if it is UPDATE then and then call oke default_action api
1680 -- to default action from dlvr type to dlvr in oke tables
1681 -- In case of CREATE, oke create deliverable api is defaulting actions from dlvr type in their tables
1682
1683 IF p_calling_mode = 'UPDATE' THEN
1684
1685 -- Get the target element version id of the deliverable
1686
1687 OPEN get_version_id (p_target_object_id);
1688 FETCH get_version_id INTO l_object_version_id ;
1689 CLOSE get_version_id ;
1690
1691 IF p_debug_mode = 'Y' THEN
1692 pa_debug.g_err_stage:= 'The target element version id(deliverable)is :'|| l_object_version_id;
1693 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1694 END IF;
1695
1696 --If something is fetched Call the Default_Action API of OKE
1697
1698 IF nvl(l_name_tbl.LAST,0)>0 THEN
1699
1700 IF p_debug_mode = 'Y' THEN
1701 pa_debug.g_err_stage:= 'Before calling OKE_DELIVERABLE_UTILS_PUB.Default_Action In Loop ';
1702 pa_debug.write(g_module_name,pa_debug.g_err_stage,5) ;
1703 END IF;
1704
1705 FOR i in l_name_tbl.FIRST..l_name_tbl.LAST LOOP
1706
1707 l_action_type := null;
1708
1709 IF l_function_code_tbl(i) = 'SHIPPING' THEN
1710 l_action_type := 'WSH';
1711 ELSIF l_function_code_tbl(i) = 'PROCUREMENT' THEN
1712 l_action_type := 'REQ';
1713 END IF;
1714
1715 IF l_action_type IS NOT NULL THEN
1716
1717 -- 3612702 : when deliverable actions are defaulted, based on due date
1718 -- defaulting logic dates should be populated,
1719 -- added one parameter to take due date, oke will populate this date in their table
1720 -- and dlvr action due date and txn dates will be in synch when actions are defaulted
1721
1722 OKE_DELIVERABLE_UTILS_PUB.Default_Action
1723 (
1724 P_Source_Code => 'PA'
1725 ,P_Action_Type => l_action_type
1726 ,P_Source_Action_Name => l_name_tbl(i)
1727 ,P_Source_Deliverable_ID => l_object_version_id
1728 ,P_Source_Action_ID => l_element_ver_id_tbl(i)
1729 ,P_Action_Date => l_due_date_tbl(i) -- 3612702 added new parameter
1730 );
1731 END IF;
1732
1733 END LOOP ;
1734
1735 IF p_debug_mode = 'Y' THEN
1736 pa_debug.g_err_stage:= 'After calling OKE_DELIVERABLE_UTILS_PUB.Default_Action In Loop';
1737 pa_debug.write(g_module_name,pa_debug.g_err_stage,5) ;
1738 END IF;
1739
1740 END IF ;
1741 END IF;
1742 END IF ;
1743 -- 3573134
1744
1745 --End Bug # 3431156
1746
1747 -- 3911050 end
1748
1749 IF p_debug_mode = 'Y' THEN
1750 pa_debug.g_err_stage := 'Exiting COPY_ACTIONS' ;
1751 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1752 pa_debug.reset_curr_function;
1756 WHEN Invalid_Arg_Exc_Dlv THEN
1753 END IF;
1754
1755 EXCEPTION
1757 x_return_status := FND_API.G_RET_STS_ERROR;
1758 l_msg_count := FND_MSG_PUB.count_msg;
1759 IF l_debug_mode = 'Y' THEN
1760 pa_debug.g_err_stage := 'inside invalid arg exception of COPY_ACTIONS';
1761 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1762 END IF;
1763
1764 IF l_msg_count = 1 THEN
1765 PA_INTERFACE_UTILS_PUB.get_messages
1766 (p_encoded => FND_API.G_TRUE,
1767 p_msg_index => 1,
1768 p_msg_count => l_msg_count,
1769 p_msg_data => l_msg_data,
1770 p_data => l_data,
1771 p_msg_index_out => l_msg_index_out);
1772 x_msg_data := l_data;
1773 x_msg_count := l_msg_count;
1774 ELSE
1775 x_msg_count := l_msg_count;
1776 END IF;
1777 IF l_debug_mode = 'Y' THEN
1778 pa_debug.reset_curr_function;
1779 END IF ;
1780 RETURN;
1781 WHEN OTHERS THEN
1782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1783 x_msg_count := 1;
1784 x_msg_data := SQLERRM;
1785
1786 IF get_source_actions%ISOPEN THEN
1787 CLOSE get_source_actions ;
1788 END IF ;
1789
1790 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_ACTIONS_PVT'
1791 ,p_procedure_name => 'COPY_ACTIONS');
1792 IF p_debug_mode = 'Y' THEN
1793 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1794 pa_debug.write('COPY_ACTIONS: ' || g_module_name,pa_debug.g_err_stage,5);
1795 pa_debug.reset_curr_function;
1796 END IF;
1797 RAISE;
1798 END COPY_ACTIONS;
1799 END PA_ACTIONS_PVT;
[Home] [Help]