[Home] [Help]
PACKAGE BODY: APPS.PA_PROJ_STAT_ACTSET
Source
1 PACKAGE BODY PA_PROJ_STAT_ACTSET AS
2 --$Header: PAASPSB.pls 115.18 2003/04/08 18:46:59 mwasowic noship $
3
4
5 PROCEDURE process_action_set (
6 p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE
7 , p_action_set_id IN NUMBER
8 , p_action_set_template_flag IN pa_action_sets.action_set_template_flag%TYPE :=NULL
9 , x_return_status OUT NOCOPY VARCHAR2
10 ) IS
11
12 l_line_number_tbl pa_action_set_utils.number_tbl_type;
13 l_line_id_tbl pa_action_set_utils.number_tbl_type;
14 l_line_cond_id_tbl pa_action_set_utils.number_tbl_type;
15 l_line_cond_date_tbl pa_action_set_utils.date_tbl_type;
16 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
17 l_loop_cnt NUMBER;
18
19 BEGIN
20 --initialize error stack
21 PA_DEBUG.init_err_stack('PA_PROJ_STAT_ACTSET.process_action_set');
22 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_APROJ_STAT_ACTSET.process_action_set.begin'
23 ,x_Msg => 'in PA_PROJ_STAT_ACTSET.process_action_set'
24 ,x_Log_Level => 6);
25
26
27 --initialize return status
28 x_return_status := l_return_status;
29 --per Xiaoyuan: always return S because errors are ignored and action set
30 --is added - even without lines Aug 21,2002, bug 2521929
31 IF p_action_set_id is NULL THEN
32 --PA_UTILS.Add_Message (p_app_short_name => 'PA'
33 -- ,p_msg_name => 'PA_NULL_ACTION_SET_ID');
34 --x_return_status := 'E';
35 --dbms_output.put_line('IN pa_proj_stat_actset.process_action_set, p_action_set_id is NULL');
36 return;
37 END IF;
38
39
40 --dbms_output.put_line( 'PROJ_STAT_ACTSET process_action_set BEGIN' );
41 -- get all action lines and conditions of the object
42 SELECT line.action_set_line_id,
43 cond.action_set_line_condition_id
44 BULK COLLECT INTO l_line_id_tbl,
45 l_line_cond_id_tbl
46 FROM pa_action_set_lines line,
47 pa_action_set_line_cond cond
48 WHERE line.action_set_id = p_action_set_id
49 AND line.action_set_line_id = cond.action_set_line_id;
50 --FORALL loop_cnt IN l_line_id_tbl.FIRST .. l_line_id_tbl.LAST
51
52 IF l_line_id_tbl.count > 0 THEN
53 FOR loop_cnt IN l_line_id_tbl.FIRST .. l_line_id_tbl.LAST LOOP
54 --dbms_output.put_line( 'Line Number '|| loop_cnt );
55 l_line_number_tbl(loop_cnt) := loop_cnt;
56 END LOOP;
57
58 --order lines for both template and project action set
59 PA_ACTION_SETS_PVT.Bulk_Update_Line_Number(
60 p_action_set_line_id_tbl => l_line_id_tbl
61 ,p_line_number_tbl => l_line_number_tbl
62 ,x_return_status => x_return_status
63 );
64 END IF;
65
66 --Do not update condition date, it's null for repeating actions
67 -- if (p_action_set_template_flag = 'N') then
68 -- FOR loop_cnt IN l_line_cond_id_tbl.FIRST .. l_line_cond_id_tbl.LAST LOOP
69 -- --dbms_output.put_line( loop_cnt ||' cond date '|| sysdate );
70 -- l_line_cond_date_tbl(loop_cnt) := sysdate;
71 -- END LOOP;
72 --
73 -- PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
74 -- p_action_line_condition_id_tbl => l_line_cond_id_tbl
75 -- ,p_condition_date_tbl => l_line_cond_date_tbl
76 -- ,x_return_status => l_return_status
77 -- );
78 -- if l_return_status <> FND_API.G_RET_STS_SUCCESS then
79 -- x_return_status := FND_API.G_RET_STS_ERROR;
80 -- end if;
81 -- END IF;
82
83 PA_DEBUG.RESET_ERR_STACK;
84
85 -- Put any message text from message stack into the Message ARRAY
86 EXCEPTION
87 WHEN OTHERS THEN
88 -- Set the excetption Message and the stack
89 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJ_STAT_ACTSET.process_action_set'
90 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
91 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
92 RAISE;
93
94 END process_action_set;
95
96 /*----------------------------------------------------------------------------------------
97 -----------------------------------------------------------------------------------------*/
98 PROCEDURE perform_action_set_line(
99 p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE := 'PA_PROJ_STATUS_REPORT'
100 ,p_action_set_details_rec IN pa_action_sets%ROWTYPE
101 ,p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
102 ,p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
103 ,x_action_line_audit_tbl OUT NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type
104 ,x_action_line_result_code OUT NOCOPY VARCHAR2)
105
106 IS
107 CURSOR c_report_info(cp_layout_id NUMBER) IS
108 SELECT l.NEXT_REPORTING_DATE report_date
109 ,l.object_id project_id
110 ,p.project_status_code proj_status_code
111 ,l.effective_from effective_from
112 ,l.effective_to effective_to
113 FROM pa_object_page_layouts l
114 ,pa_projects_all p
115 WHERE l.object_page_layout_id = cp_layout_id
116 AND l.object_id = p.project_id;
117
118 cp_rpt_info c_report_info%ROWTYPE;
119
120 l_api_name VARCHAR2(30) := 'PA_PROJ_STAT_ACTSET';
121 l_project_id pa_projects_all.project_id%TYPE;
122 l_report_type_id pa_object_page_layouts.object_page_layout_id%TYPE;
123 l_report_date pa_object_page_layouts.next_reporting_date%TYPE;
124 l_project_status pa_projects_all.project_status_code%TYPE;
125 l_action_performed VARCHAR2(1) := 'N';
126 l_return_status VARCHAR2(1);
127 l_msg_count NUMBER;
128 l_msg_data fnd_new_messages.message_name%TYPE;
129 l_action_is_repeating BOOLEAN := TRUE;
130 l_msg_index_out NUMBER;
131 l_today DATE := TRUNC(sysdate);
132 l_effective_from DATE;
133 l_effective_to DATE;
134
135
136 BEGIN
137
138 pa_debug.init_err_stack('PA_PROJ_STAT_ACTSET:perform_action_set_line');
139 x_action_line_result_code := pa_action_set_utils.G_NOT_PERFORMED ;
140 g_action_line_audit_tbl.DELETE;
141
142 if validate_action_type_code(p_action_set_type_code) = FALSE then
143 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
144 ,p_token1 => p_action_set_type_code
145 ,p_msg_name => 'PA_INVALID_ACTION_TYPE');
146 PA_DEBUG.RESET_ERR_STACK;
147 return;
148 end if;
149
150 --per Xiouyuan,hard-coding statu code, bug 2383406
151 if p_action_set_line_rec.status_code = 'REVERSE_PENDING' then
152 x_action_line_result_code := pa_action_set_utils.G_REVERSED_DEFAULT_AUDIT;
153 PA_DEBUG.RESET_ERR_STACK;
154 return;
155 end if;
156
157
158 OPEN c_report_info(p_action_set_details_rec.object_id);
159 FETCH c_report_info INTO cp_rpt_info;
160 if c_report_info%NOTFOUND then
161 CLOSE c_report_info;
162 PA_ACTION_SET_UTILS.Add_Message ( p_app_short_name => 'PA'
163 ,p_msg_name => 'PA_INVALID_PROJECT_ID'); --existing msg
164 PA_DEBUG.RESET_ERR_STACK;
165 return;
166 end if ;
167
168 CLOSE c_report_info;
169
170 l_project_id := cp_rpt_info.project_id;
171 l_project_status := cp_rpt_info.proj_status_code;
172 l_report_date := cp_rpt_info.report_date;
173 l_effective_from := cp_rpt_info.effective_from;
174 l_effective_to := cp_rpt_info.effective_to;
175
176 -- report type effective date range check
177 if nvl(l_effective_from, l_today) > l_today or nvl(l_effective_to,l_today) < l_today then
178 PA_DEBUG.RESET_ERR_STACK;
179 return;
180 end if;
181
182 if (l_project_id is null or l_project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) then
183 PA_ACTION_SET_UTILS.Add_Message ( p_app_short_name => 'PA'
184 ,p_msg_name => 'PA_NO_PROJECT_ID'); --existing message
185 PA_DEBUG.RESET_ERR_STACK;
186 return;
187 end if ;
188
189
190 if (project_dates_valid(l_project_id)= 'N') then
191 PA_DEBUG.RESET_ERR_STACK;
192 return;
193 end if;
194
195 if (action_allowed_for_status(l_project_id,l_project_status) = FALSE ) then
196 PA_DEBUG.RESET_ERR_STACK;
197 return;
198 end if;
199
200
201 if (ok_to_perform_action(
202 l_report_date
203 ,p_action_set_line_rec
204 ,p_action_line_conditions_tbl)) then
205 l_msg_count := 0;
206 perform_selected_action(
207 p_project_id => l_project_id
208 ,p_report_type_id => l_report_type_id
209 ,p_layout_id => p_action_set_details_rec.object_id
210 ,p_action_set_type_code => p_action_set_type_code
211 ,p_action_set_line_rec => p_action_set_line_rec
212 ,p_action_line_conditions_tbl => p_action_line_conditions_tbl
213 ,x_action_performed => l_action_performed
214 ,x_return_status => l_return_status
215 ,x_msg_count => l_msg_count
216 ,x_msg_data => l_msg_data);
217 end if;
218 if (l_action_performed = 'Y') then
219 l_action_is_repeating := is_action_repeating(p_action_line_conditions_tbl);
220 if (l_action_is_repeating) then
221 x_action_line_result_code := pa_action_set_utils.G_PERFORMED_ACTIVE;
222 else
223 x_action_line_result_code := pa_action_set_utils.G_PERFORMED_COMPLETE;
224 end if;
225 x_action_line_audit_tbl := PA_PROJ_STAT_ACTSET.g_action_line_audit_tbl;
226
227 else
228 if nvl(l_msg_count,0) > 0 then
229 x_action_line_result_code := pa_action_set_utils.G_NOT_PERFORMED ;
230 end if;
231 end if;
232
233
234 PA_DEBUG.RESET_ERR_STACK;
235
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJ_STAT_ACTSET.perform_action_set_line'
240 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
241 x_action_line_result_code := pa_action_set_utils.G_NOT_PERFORMED;
242 RAISE;
243
244 END perform_action_set_line;
245
246
247 /*-----------------------------------------------------------------------------------*/
248 FUNCTION validate_action_type_code (
249 p_action_set_type_code IN VARCHAR2
250 ) return BOOLEAN
251 IS
252 BEGIN
253
254 if (p_action_set_type_code is NULL or p_action_set_type_code <> 'PA_PROJ_STATUS_REPORT' ) then
255 return FALSE;
256 else
257 return TRUE;
258 end if;
259
260 END validate_action_type_code;
261
262 FUNCTION action_allowed_for_status (
263 p_project_id IN NUMBER
264 ,p_project_status IN VARCHAR2
265 ) return BOOLEAN
266
267 IS
268 --l_project_status VARCHAR2(30);--mwxx REMOVE!!!
269 BEGIN
270 --l_project_status := 'APPROVED'; --mwxx REMOVE!!!
271 if PA_PROJECT_UTILS.check_prj_stus_action_allowed
272 (p_project_status,'PA_PROJ_STATUS_REPORT') <> 'Y' then
273 return FALSE;
274 end if;
275 return TRUE ;
276 END action_allowed_for_status;
277
278
279 FUNCTION project_dates_valid (
280 p_project_id IN NUMBER
281 ) return VARCHAR2
282 IS
283 l_sysdate DATE := TRUNC(sysdate);
284 l_start_date DATE := NULL;
285 l_end_date DATE := NULL;
286 BEGIN
287 l_start_date := PA_PROJECT_DATES_UTILS.get_project_start_date(p_project_id);
288 l_end_date := PA_PROJECT_DATES_UTILS.get_project_finish_date(p_project_id);
289 if (l_start_date is null or TRUNC(l_start_date) > l_sysdate) then
290 return 'N'; --do not process this one, project not started
291 end if;
292 if (l_end_date is not null and TRUNC(l_end_date) <= l_sysdate) then
293 return 'N'; --do not process this one, project is finished
294 end if;
295
296 return 'Y';
297
298 EXCEPTION
299 when OTHERS then
300 return 'N';
301 END project_dates_valid;
302
303
304 FUNCTION ok_to_perform_action (
305 p_report_date IN pa_object_page_layouts.next_reporting_date%TYPE
306 ,p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
307 ,p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
308 ) return BOOLEAN
309 IS
310 l_condition_date DATE := TRUNC(sysdate);
311 l_days NUMBER := 0;
312 BEGIN
313 -- valid action codes are:
314 --PA_PROJ_STATUS_REPORT_MISS
315 --PA_PROJ_STATUS_REPORT_NEXT
316 if (p_action_line_conditions_tbl(p_action_line_conditions_tbl.COUNT).condition_code = 'PA_PROJ_STATUS_REPORT_BEFORE') then
317 l_days := 0 - p_action_line_conditions_tbl(p_action_line_conditions_tbl.COUNT).condition_attribute1;
318 else if (p_action_line_conditions_tbl(p_action_line_conditions_tbl.COUNT).condition_code = 'PA_PROJ_STATUS_REPORT_AFTER') then
319 l_days := p_action_line_conditions_tbl(p_action_line_conditions_tbl.COUNT).condition_attribute1;
320 end if;
321 end if;
322 l_condition_date := TRUNC(p_report_date) + l_days;
323
324 if (l_condition_date = TRUNC(SYSDATE)) then
325 if (pa_action_set_utils.get_last_performed_date
326 (p_action_set_line_rec.action_set_line_id) = TRUNC(sysdate)) then
327 return FALSE;
328 else
329 return TRUE;
330 end if;
331 else
332 return FALSE;
333 end if;
334
335 END ok_to_perform_action;
336
337
338
339 FUNCTION is_action_repeating(
340 p_action_line_conditions_tbl pa_action_set_utils.action_line_cond_tbl_type) return BOOLEAN
341 IS
342 BEGIN
343 --if (p_action_line_conditions_tbl(1).condition_attribute2 is NULL ) then
344 -- return FALSE;
345 -- end if;
346
347 return TRUE;
348 END is_action_repeating;
349
350
351 PROCEDURE validate_action_set_line (
352 p_action_set_type_code IN VARCHAR2 := 'PA_PROJ_STATUS_REPORT'
353 , p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
354 , p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
355 , x_return_status OUT NOCOPY VARCHAR2
356 ) IS
357
358 BEGIN
359 x_return_status := 'S';
360 END validate_action_set_line;
361
362 PROCEDURE validate_action_set (
363 p_action_set_type_code IN VARCHAR2 := 'PA_PROJ_STATUS_REPORT'
364 , p_action_set_id IN NUMBER
365 , p_action_set_template_flag IN VARCHAR2
366 , x_return_status OUT NOCOPY VARCHAR2
367 ) IS
368 BEGIN
369 x_return_status := 'S';
370 END validate_action_set;
371
372 /*---------------------------------------------------------------------------------------------*/
373 PROCEDURE perform_selected_action(
374 p_project_id IN NUMBER
375 ,p_report_type_id IN NUMBER
376 ,p_layout_id IN NUMBER
377 ,p_action_set_type_code IN VARCHAR2
378 ,p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
379 ,p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
380 ,x_action_performed OUT NOCOPY VARCHAR2
381 ,x_return_status OUT NOCOPY VARCHAR2
382 ,x_msg_count OUT NOCOPY NUMBER
383 ,x_msg_data OUT NOCOPY VARCHAR2)
384
385 IS
386 l_return_status VARCHAR2(1) := 'S';
387 l_msg_count NUMBER := 0;
388 l_msg_data VARCHAR2(2000);
389 l_sysdate DATE := TRUNC(sysdate);
390 l_action_line_audit_tbl pa_action_set_utils.insert_audit_lines_tbl_type;
391 l_cnt NUMBER := 0;
392
393 BEGIN
394 /*Currently we are passing PA_OBJECT_PAGE_LAYOUT as object type so the
395 workflow API can use it to find distribution list.
396 This object type is required by the API that retrieves the distribution list */
397 x_action_performed := 'N'; --in case there are no reminders to be sent today
398 PA_PROGRESS_REPORT_WORKFLOW.start_action_set_workflow(
399 p_item_type => 'PAWFPPRA'
400 , p_process_name => p_action_set_line_rec.action_code
401 , p_object_type => 'PA_OBJECT_PAGE_LAYOUT'--'PA_PROJ_STATUS_REPORTS'
402 , p_object_id => p_layout_id
403 , p_action_set_line_rec => p_action_set_line_rec
404 , p_action_line_conditions_tbl => p_action_line_conditions_tbl
405 , x_action_line_audit_tbl => l_action_line_audit_tbl
406 , x_return_status => l_return_status
407 , x_msg_count => l_msg_count
408 , x_msg_data => l_msg_data
409 );
410 if (nvl(l_msg_count,0) = 0) then
411 x_action_performed := 'Y';
412 end if;
413 g_action_line_audit_tbl := l_action_line_audit_tbl;
414
415
416 x_return_status := l_return_status;
417 x_msg_count := l_msg_count;
418 x_msg_data := l_msg_data;
419
420 EXCEPTION
421 WHEN OTHERS THEN
422 RAISE;
423
424 -- Set the exception Message and the stack
425 --FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_proj_stat_actset.perform_selected_action'
426 -- ,p_procedure_name => PA_DEBUG.G_Err_Stack );
427 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
428 -- x_msg_count := 1;
429 -- x_msg_data := substr(SQLERRM,1,2000);
430 END perform_selected_action;
431
432 PROCEDURE copy_action_sets(
433 p_project_id_from IN NUMBER
434 ,p_project_id_to IN NUMBER
435 ,x_return_status OUT NOCOPY VARCHAR2
436 ,x_msg_count OUT NOCOPY NUMBER
437 ,x_msg_data OUT NOCOPY VARCHAR2) IS
438
439 Cursor c_action_set_ids
440 is
441 /*
442 SELECT lt.object_page_layout_id object_page_layout_id
443 ,ast.action_set_id action_set_id
444 ,lt.report_type_id report_type_id
445 FROM pa_object_page_layouts lt
446 ,pa_action_sets ast
447 WHERE ast.object_type = 'PA_PROJ_STATUS_REPORTS'
448 AND lt.page_type_code = 'PPR'
449 AND lt.object_id = p_project_id_from --c_proj_id_from
450 AND ast.object_id = lt.object_page_layout_id;
451 */
452 SELECT lt.object_page_layout_id object_page_layout_id
453 ,pa_action_set_utils.get_action_set_id
454 ('PA_PROJ_STATUS_REPORT','PA_PROJ_STATUS_REPORTS',lt.object_page_layout_id) action_set_id
455 ,lt.report_type_id report_type_id
456 FROM pa_object_page_layouts lt
457 WHERE lt.page_type_code = 'PPR'
458 AND lt.object_id = p_project_id_from
459 and pa_action_set_utils.get_action_set_id
460 ('PA_PROJ_STATUS_REPORT','PA_PROJ_STATUS_REPORTS',lt.object_page_layout_id) is not null;
461
462
463 Cursor c_new_proj_layout_ids(c_proj_id_to NUMBER, rep_type_id NUMBER)
464 is
465 SELECT object_page_layout_id
466 FROM pa_object_page_layouts
467 WHERE object_id = c_proj_id_to
468 AND object_type = 'PA_PROJECTS'
469 AND report_type_id = rep_type_id
470 AND page_type_code = 'PPR';
471
472 cp_layout_id c_new_proj_layout_ids%ROWTYPE;
473
474 l_new_action_set_id NUMBER;
475 l_action_set_id NUMBER;
476 loop_cnt NUMBER;
477 l_commit_flag VARCHAR2(1) := 'Y';
478
479 BEGIN
480
481 x_return_status := fnd_api.g_ret_sts_success;
482 PA_DEBUG.init_err_stack('PA_PROJ_STAT_ACTSET.copy_action_sets');
483 savepoint copy_proj_action_sets;
484
485
486 FOR c_action_set_rec in c_action_set_ids LOOP
487
488 OPEN c_new_proj_layout_ids(p_project_id_to, c_action_set_rec.report_type_id);
489 FETCH c_new_proj_layout_ids into cp_layout_id;
490 if c_new_proj_layout_ids%FOUND then
491
492 pa_action_sets_pub.apply_action_set
493 (p_action_set_id => c_action_set_rec.action_set_id
494 ,p_object_type => 'PA_PROJ_STATUS_REPORTS'
495 ,p_object_id => cp_layout_id.object_page_layout_id
496 ,p_validate_only => FND_API.G_FALSE
497 ,x_new_action_set_id => l_new_action_set_id
498 ,x_return_status => x_return_status
499 ,x_msg_count => x_msg_count
500 ,x_msg_data => x_msg_data);
501
502 close c_new_proj_layout_ids;
503 if x_return_status <> fnd_api.g_ret_sts_success then
504 l_commit_flag := 'N';
505 end if;
506 else
507 close c_new_proj_layout_ids;
508 end if;
509
510 END LOOP;
511
512
513 if l_commit_flag = 'N' then
514 ROLLBACK TO copy_proj_action_sets;
515 end if;
516
517 PA_DEBUG.Reset_Err_Stack;
518
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 ROLLBACK TO copy_proj_action_sets;
523 -- Set the exception Message and the stack
524 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_proj_stat_actset.copy_action_sets'
525 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
527 RAISE;
528
529 END copy_action_sets;
530
531 PROCEDURE delete_action_set
532 (p_action_set_id IN pa_action_sets.action_set_id%TYPE := NULL
533 ,p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE := 'PA_PROJ_STATUS_REPORT'
534 ,p_object_type IN pa_action_sets.object_type%TYPE := 'PA_PROJ_STATUS_REPORTS'
535 ,p_object_id IN pa_action_sets.object_id%TYPE := NULL
536 ,p_record_version_number IN pa_action_sets.record_version_number%TYPE := NULL
537 ,p_api_version IN NUMBER := 1.0
538 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
539 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
540 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
541 ,x_return_status OUT NOCOPY VARCHAR2
542 ,x_msg_count OUT NOCOPY NUMBER
543 ,x_msg_data OUT NOCOPY VARCHAR2
544 ) IS
545 /*
546 CURSOR c_action_set_info(cp_object_id NUMBER, cp_type pa_action_sets.action_set_type_code%TYPE,
547 cp_obj pa_action_sets.object_type%TYPE ) IS
548
549 select action_set_id,record_version_number
550 from pa_action_sets
551 where object_id = cp_object_id
552 AND object_type = cp_obj
553 AND action_set_type_code = cp_type;
554 */
555 CURSOR c_action_set_info(cp_act_set_id NUMBER ) IS
556 select record_version_number
557 from pa_action_sets
558 where action_set_id = cp_act_set_id;
559
560
561 l_record_version_number NUMBER;
562 l_action_set_id NUMBER;
563 cp_action_set_info c_action_set_info%ROWTYPE;
564
565 BEGIN
566 PA_DEBUG.init_err_stack('PA_PROJ_STAT_ACTSET.delete_action_set');
567 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_PROJ_STAT_ACTSET.delete_action_set.begin'
568 ,x_Msg => 'in PA_PROJ_STAT_ACTSET.delete_action_set'
569 ,x_Log_Level => 6);
570
571 x_return_status := 'S';
572 l_action_set_id := p_action_set_id;
573 l_record_version_number := p_record_version_number;
574
575 IF l_action_set_id is NULL THEN
576 l_action_set_id := pa_action_set_utils.get_action_set_id(p_action_set_type_code
577 ,p_object_type
578 ,p_object_id);
579 END IF;
580
581 IF l_record_version_number is NULL THEN
582 OPEN c_action_set_info(l_action_set_id);
583 FETCH c_action_set_info INTO cp_action_set_info;
584
585 --Return success when action set not found since we're trying to delete it
586 IF c_action_set_info%NOTFOUND THEN
587 CLOSE c_action_set_info;
588 RETURN;
589 END IF;
590 l_record_version_number := cp_action_set_info.record_version_number;
591 CLOSE c_action_set_info;
592 END IF;
593
594 PA_ACTION_SETS_PUB.delete_action_set
595 (p_action_set_id => l_action_set_id
596 ,p_action_set_type_code => p_action_set_type_code
597 ,p_object_type => p_object_type
598 ,p_object_id => p_object_id
599 ,p_init_msg_list => FND_API.G_TRUE
600 ,p_record_version_number => l_record_version_number
601 ,p_commit => p_commit
602 ,p_validate_only => p_validate_only
603 ,x_return_status => x_return_status
604 ,x_msg_count => x_msg_count
605 ,x_msg_data => x_msg_data);
606
607
608 PA_DEBUG.RESET_ERR_STACK;
609
610
611 EXCEPTION
612
613 WHEN OTHERS THEN
614 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJ_STAT_ACTSET.delete_action_set'
615 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
616
617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
618 RAISE;
619
620 END delete_action_set;
621
622
623
624 PROCEDURE update_action_set
625 (p_action_set_id IN pa_action_sets.action_set_id%TYPE := NULL
626 ,p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE := 'PA_PROJ_STATUS_REPORT'
627 ,p_object_type IN pa_action_sets.object_type%TYPE := 'PA_PROJ_STATUS_REPORTS'
628 ,p_object_id IN pa_action_sets.object_id%TYPE := NULL
629 ,p_perform_action_set_flag IN VARCHAR2 := 'N'
630 ,p_record_version_number IN pa_action_sets.record_version_number%TYPE := NULL
631 ,p_api_version IN NUMBER := 1.0
632 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
633 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
634 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
635 ,x_new_action_set_id OUT NOCOPY NUMBER
636 ,x_return_status OUT NOCOPY VARCHAR2
637 ,x_msg_count OUT NOCOPY NUMBER
638 ,x_msg_data OUT NOCOPY VARCHAR2
639 ) IS
640
641
642 CURSOR c_action_set_info(cp_act_set_id NUMBER ) IS
643 select record_version_number
644 from pa_action_sets
645 where action_set_id = cp_act_set_id;
646
647
648 l_record_version_number NUMBER;
649 l_curr_action_set_id NUMBER := NULL;
650 cp_action_set_info c_action_set_info%ROWTYPE;
651 l_new_action_set_id NUMBER := NULL;
652
653
654 BEGIN
655 PA_DEBUG.init_err_stack('PA_PROJ_STAT_ACTSET.update_action_set');
656 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_PROJ_STAT_ACTSET.update_action_set.begin'
657 ,x_Msg => 'in PA_PROJ_STAT_ACTSET.update_action_set'
658 ,x_Log_Level => 6);
659
660 x_return_status := 'S';
661 --l_record_version_number := p_record_version_number;
662 l_curr_action_set_id := pa_action_set_utils.get_action_set_id(p_action_set_type_code
663 ,p_object_type
664 ,p_object_id);
665
666 IF l_curr_action_set_id is NOT NULL THEN
667 --Find an existing action set attached to this page layout id
668 OPEN c_action_set_info(l_curr_action_set_id);
669 FETCH c_action_set_info INTO cp_action_set_info;
670
671 --Return success when action set not found when we're trying to delete it
672 IF c_action_set_info%NOTFOUND THEN
673 CLOSE c_action_set_info;
674 l_curr_action_set_id := NULL;
675 ELSE
676 l_record_version_number := cp_action_set_info.record_version_number;
677 CLOSE c_action_set_info;
678 END IF;
679 END IF;
680
681
682 IF p_action_set_id is NULL AND l_curr_action_set_id is NOT NULL THEN
683 PA_ACTION_SETS_PUB.delete_action_set
684 (p_action_set_id => l_curr_action_set_id
685 ,p_action_set_type_code => p_action_set_type_code
686 ,p_object_type => p_object_type
687 ,p_object_id => p_object_id
688 ,p_init_msg_list => p_init_msg_list
689 ,p_record_version_number => l_record_version_number
690 ,p_commit => p_commit
691 ,p_validate_only => p_validate_only
692 ,x_return_status => x_return_status
693 ,x_msg_count => x_msg_count
694 ,x_msg_data => x_msg_data);
695 ELSE
696 IF p_action_set_id is NOT NULL AND l_curr_action_set_id is NULL THEN
697 PA_ACTION_SETS_PUB.apply_action_set
698 (p_action_set_id => p_action_set_id
699 ,p_object_type => p_object_type
700 ,p_object_id => p_object_id
701 ,p_perform_action_set_flag => p_perform_action_set_flag
702 ,p_init_msg_list => p_init_msg_list
703 ,p_commit => p_commit
704 ,p_validate_only => p_validate_only
705 ,x_new_action_set_id => x_new_action_set_id
706 ,x_return_status => x_return_status
707 ,x_msg_count => x_msg_count
708 ,x_msg_data => x_msg_data);
709
710 ELSE
711 IF p_action_set_id is NOT NULL AND l_curr_action_set_id is NOT NULL THEN
712 IF l_curr_action_set_id = p_action_set_id THEN
713 RETURN;
714 END IF;
715
716 PA_ACTION_SETS_PUB.replace_action_set
717 (p_current_action_set_id => l_curr_action_set_id
718 ,p_action_set_type_code => p_action_set_type_code
719 ,p_object_type => p_object_type
720 ,p_object_id => p_object_id
721 ,p_record_version_number => l_record_version_number
722 ,p_new_action_set_id => p_action_set_id
723 ,p_init_msg_list => p_init_msg_list
724 ,p_commit => p_commit
725 ,p_validate_only => p_validate_only
726 ,x_new_action_set_id => x_new_action_set_id
727 ,x_return_status => x_return_status
728 ,x_msg_count => x_msg_count
729 ,x_msg_data => x_msg_data);
730
731 END IF;
732 END IF;
733 END IF;
734
735 PA_DEBUG.RESET_ERR_STACK;
736
737
738 EXCEPTION
739
740 WHEN OTHERS THEN
741 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJ_STAT_ACTSET.update_action_set'
742 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
743
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
745 RAISE;
746
747 END update_action_set;
748
749
750 /*
751 select billing_cycle_id,billing_cycle_name
752 2 from pa_billing_cycles;
753 BILLING_CYCLE_ID BILLING_CYCLE_NAME
754 ---------------- ------------------------------
755 1 Billing cycle days : 28
756 2 Billing cycle days : 35
757 21 First Day
758 22 Last Weekday of Month
759 23 Weekday Each Week
760
761 */
762
763
764 END;