[Home] [Help]
PACKAGE BODY: APPS.PA_RESOURCE_SETUP_PVT
Source
1 PACKAGE BODY PA_RESOURCE_SETUP_PVT AS
2 /* $Header: PARESTVB.pls 120.2 2006/06/30 21:51:02 ramurthy noship $ */
3
4
5 -- API name : update_addition_staff_info
6 -- Type : Public procedure
7 -- Pre-reqs : None
8 -- Return Value : N/A
9 li_message_level NUMBER := 1;
10
11
12 PROCEDURE UPDATE_ADDITIONAL_STAFF_INFO
13 (p_api_version IN NUMBER := 1.0 ,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
15 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
16 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
17 p_project_id IN NUMBER ,
18 p_calendar_id IN NUMBER := FND_API.G_MISS_NUM ,
19 p_role_list_id IN NUMBER := FND_API.G_MISS_NUM ,
20 p_adv_action_set_id IN NUMBER := FND_API.G_MISS_NUM ,
21 p_start_adv_action_set_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
22 p_record_version_number IN NUMBER ,
23 p_initial_team_template_id IN NUMBER := FND_API.G_MISS_NUM , -- added for bug 2607631
24 p_proj_req_res_format_id IN NUMBER := FND_API.G_MISS_NUM ,
25 p_proj_asgmt_res_format_id IN NUMBER := FND_API.G_MISS_NUM ,
26 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
27 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
28 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
29 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
30 IS
31
32
33 l_error_msg_code VARCHAR2(250);
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(250);
36 l_data VARCHAR2(250);
37 l_msg_index_out NUMBER;
38 l_record_version_number PA_PROJECTS_ALL.RECORD_VERSION_NUMBER%TYPE;
39 l_dummy VARCHAR2(1);
40 l_debug_mode VARCHAR2(10);
41
42 BEGIN
43
44 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
45
46 --pr_msg('Begin '||p_initial_team_template_id);
47 --pr_msg('Begin '||p_role_list_id);
48
49 IF l_debug_mode = 'Y' THEN
50 pa_debug.write(x_module => 'pa.plsql.PA_RESOURCE_SETUP_PVT.update_addition_staff_info'
51 ,x_msg => 'adv_id='||p_adv_action_set_id||
52 ' req_format='||p_proj_req_res_format_id||
53 ' asgmt_format='||p_proj_asgmt_res_format_id
54 ,x_log_level => li_message_level);
55 END IF;
56
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 IF p_commit = FND_API.G_TRUE THEN
60 SAVEPOINT update_addition_staff_info;
61 END IF;
62
63 if p_validate_only <> FND_API.G_TRUE then
64 BEGIN
65
66 SELECT 'x' INTO l_dummy
67 FROM pa_projects
68 WHERE project_id = p_project_id
69 AND record_version_number = p_record_version_number
70 FOR UPDATE OF record_version_number NOWAIT;
71
72 EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN
73
74 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
75 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
76 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
77 x_return_status := 'E' ;
78
79 WHEN NO_DATA_FOUND THEN
80
81 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
82 p_msg_name => 'PA_XC_RECORD_CHANGED');
83 x_msg_data := 'PA_XC_RECORD_CHANGED';
84 x_return_status := 'E' ;
85
86 WHEN OTHERS THEN
87
88 IF SQLCODE = -54 THEN
89 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
90 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
91 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
92 x_return_status := 'E' ;
93 ELSE
94 RAISE;
95 END IF;
96
97 END;
98 else
99
100 BEGIN
101 SELECT 'x' INTO l_dummy
102 FROM pa_projects
103 WHERE project_id = p_project_id
104 AND record_version_number = p_record_version_number;
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
108 p_msg_name => 'PA_XC_RECORD_CHANGED');
109 x_msg_data := 'PA_XC_RECORD_CHANGED';
110 x_return_status := 'E' ;
111 WHEN OTHERS THEN
112 IF SQLCODE = -54 THEN
113 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
114 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
115 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
116 x_return_status := 'E' ;
117 Else
118 raise;
119 END IF;
120 END;
121 end if;
122 l_msg_count := FND_MSG_PUB.count_msg;
123
124 IF l_msg_count > 0 THEN
125 x_msg_count := l_msg_count;
126 x_return_status := 'E';
127 RAISE FND_API.G_EXC_ERROR;
128 END IF;
129
130 -- write your program logic from here
131
132
133 IF NOT FND_API.TO_BOOLEAN(p_validate_only) THEN
134
135 -- update the project table
136 UPDATE pa_projects_all
137 SET record_version_number = record_version_number + 1
138 ,calendar_id = decode(p_calendar_id, FND_API.G_MISS_NUM, calendar_id, p_calendar_id)
139 ,role_list_id = decode(p_role_list_id, FND_API.G_MISS_NUM, role_list_id, p_role_list_id)
140 ,adv_action_set_id = decode(p_adv_action_set_id, FND_API.G_MISS_NUM, adv_action_set_id, p_adv_action_set_id)
141 ,start_adv_action_set_flag = decode(p_start_adv_action_set_flag, FND_API.G_MISS_CHAR, start_adv_action_set_flag, p_start_adv_action_set_flag)
142 ,initial_team_template_id = DECODE(p_initial_team_template_id, FND_API.G_MISS_NUM, initial_team_template_id, p_initial_team_template_id) -- added for bug 2607631
143 ,proj_req_res_format_id = DECODE(p_proj_req_res_format_id, FND_API.G_MISS_NUM, proj_req_res_format_id, p_proj_req_res_format_id)
144 ,proj_asgmt_res_format_id = DECODE(p_proj_asgmt_res_format_id, FND_API.G_MISS_NUM, proj_asgmt_res_format_id, p_proj_asgmt_res_format_id)
145 WHERE project_id = p_project_id;
146 END IF;
147
148 EXCEPTION
149
150 WHEN FND_API.G_EXC_ERROR THEN
151
152 IF p_commit = FND_API.G_TRUE THEN
153 ROLLBACK TO update_addition_staff_info;
154 END IF;
155 x_return_status := FND_API.G_RET_STS_ERROR;
156
157 WHEN OTHERS THEN
158 IF p_commit = FND_API.G_TRUE THEN
159 ROLLBACK TO update_addition_staff_info;
160 END IF;
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RESOURCE_SETUP_PUB',
163 p_procedure_name => 'UPDATE_ADDITIONAL_STAFF_INFO',
164 p_error_text => SUBSTRB(SQLERRM,1,240));
165 raise;
166
167 END UPDATE_ADDITIONAL_STAFF_INFO;
168
169 END PA_RESOURCE_SETUP_PVT;