[Home] [Help]
PACKAGE BODY: APPS.PA_STATUSES_PVT
Source
1 PACKAGE BODY PA_STATUSES_PVT as
2 /* $Header: PARSTAVB.pls 120.1 2005/08/19 17:00:43 mwasowic noship $ */
3 -- Start of Comments
4 -- Package name : PA_STATUSES_PVT
5 -- Purpose : Private Package for table PA_PROJECT_STATUSES
6 -- History : 07-JUL-2000 Mohnish Created
7 -- 11-OCT-2000 Partha serveroutput is removed
8 -- 14-FEB-2003 mrajput Bug2778408 : Added api Process_Phase_Code_Delete
9 --
10
11 -- NOTE :
12 -- : Subprogram Name Type
13 -- : ------------------ -----------------------
14 -- : delete_status_pvt PL/SQL procedure
15
16 -- End of Comments
17 --Global constants to be used in error messages
18 G_PKG_NAME CONSTANT VARCHAR2(30) :='PA_STATUSES_PVT';
19 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
20 G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.login_id;
21
22 -- ============================================================================
23
24 PROCEDURE delete_status_pvt
25 ( p_api_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM --- (Bug 1851096)
26 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE -- (Bug 1851096)
27 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
28 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
29 ,p_max_msg_count IN NUMBER
30 ,p_pa_project_status_code IN VARCHAR2
31 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
32 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
33 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
34 ,x_allow_deletion_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
35 )
36 IS
37 l_api_name CONSTANT VARCHAR2(30) := 'delete_status';
38 l_err_code NUMBER;
39 l_err_stage VARCHAR2(120);
40 l_err_stack VARCHAR2(630);
41 l_status_type VARCHAR2(30);
42 l_resp_id NUMBER := 0;
43 l_user_id NUMBER := 0;
44 x_err_code NUMBER := 0;
45 x_err_stage VARCHAR2(30);
46 x_err_stack VARCHAR2(630);
47
48 /* Added for Bug 2778408 */
49 l_msg_count NUMBER;
50 l_data VARCHAR2(2000);
51 l_msg_data VARCHAR2(2000);
52 l_return_status VARCHAR2(1);
53 l_errorcode NUMBER;
54 l_msg_index_out NUMBER;
55
56 BEGIN
57
58 -- Standard begin of API savepoint
59
60 SAVEPOINT delete_status_pub;
61
62 -- Standard call to check for call compatibility.
63
64 IF NOT FND_API.Compatible_API_Call (
65 g_api_version_number ,
66 p_api_version_number ,
67 l_api_name ,
68 G_PKG_NAME ) THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72
73 -- Initialize the message table if requested.
74
75 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79
80 -- Set API return status to success
81
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83 l_resp_id := FND_GLOBAL.Resp_id;
84 l_user_id := FND_GLOBAL.User_id;
85
86
87 -- Check the status code passed
88 IF p_pa_project_status_code is NULL THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END IF;
91
92 -- get the status_type for the current status_code
93 select status_type
94 into l_status_type
95 from pa_project_statuses
96 where project_status_code=p_pa_project_status_code;
97
98 -- check if the status is deletable, ie. not being used anywhere
99 pa_project_stus_utils.allow_status_deletion(
100 p_pa_project_status_code
101 , l_status_type
102 , x_err_code
103 , x_err_stage
104 , x_err_stack
105 , x_allow_deletion_flag);
106
107 -- call the table handler of PA_PROJECT_STATUSES, PA_PROJECT_STATUS_CONTROLS
108 -- and PA_NEXT_ALLOW_STATUSES for delete if there is no error in the above call
109 IF (x_allow_deletion_flag = 'Y') THEN
110 /* Start Changes for Bug 2778408 */
111 IF (l_status_type='PHASE') THEN
112
113 PA_EGO_WRAPPER_PUB.process_phase_code_delete(
114 p_api_version => p_api_version_number ,
115 p_phase_code => p_pa_project_status_code ,
116 p_init_msg_list => p_init_msg_list ,
117 p_commit => p_commit ,
118 x_return_status => l_return_status ,
119 x_errorcode => l_errorcode ,
120 x_msg_count => l_msg_count ,
121 x_msg_data => l_msg_data);
122
123 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
124 l_msg_count := FND_MSG_PUB.count_msg;
125 If l_msg_count > 0 THEN
126 x_msg_count := l_msg_count;
127 If l_msg_count = 1 THEN
128 pa_interface_utils_pub.get_messages
129 (p_encoded => FND_API.G_TRUE ,
130 p_msg_index => 1 ,
131 p_msg_count => l_msg_count ,
132 p_msg_data => l_msg_data ,
133 p_data => l_data ,
134 p_msg_index_out => l_msg_index_out
135 );
136 x_msg_data := l_data;
137 End if;
138 End if;
139 RAISE FND_API.G_EXC_ERROR;
140 END IF;
141 End IF;
142 /* End of Changes for Bug 2778408 */
143 PA_PROJECT_STATUSES_PKG.delete_row(p_pa_project_status_code);
144 PA_PROJECT_STATUS_CONTROLS_PKG.delete_row(p_pa_project_status_code);
145 PA_NEXT_ALLOW_STATUSES_PKG.delete_row(p_pa_project_status_code);
146 ELSE
147 x_msg_data := x_err_stage;
148 ROLLBACK TO delete_status_pub;
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151
152 COMMIT;
153
154 EXCEPTION
155
156 WHEN FND_API.G_EXC_ERROR THEN
157 ROLLBACK TO delete_status_pub;
158 x_return_status := FND_API.G_RET_STS_ERROR;
159 FND_MSG_PUB.Count_And_Get
160 ( p_count => x_msg_count ,
161 p_data => x_msg_data );
162
163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164 ROLLBACK TO delete_status_pub;
165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
166 FND_MSG_PUB.Count_And_Get
167 ( p_count => x_msg_count ,
168 p_data => x_msg_data );
169
170 WHEN ROW_ALREADY_LOCKED THEN
171 ROLLBACK TO delete_status_pub;
172 x_return_status := FND_API.G_RET_STS_ERROR;
173
174 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
175 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
176 FND_MESSAGE.SET_TOKEN('ENTITY', p_pa_project_status_code);
177 FND_MSG_PUB.ADD;
178 END IF;
179 FND_MSG_PUB.Count_And_Get
180 ( p_count => x_msg_count ,
181 p_data => x_msg_data );
182
183 WHEN OTHERS THEN
184 ROLLBACK TO delete_status_pub;
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
187 FND_MSG_PUB.add_exc_msg
188 ( p_pkg_name => G_PKG_NAME
189 , p_procedure_name => l_api_name
190 , p_error_text => SUBSTR(SQLERRM, 1, 240) );
191 FND_MSG_PUB.add;
192 END IF;
193
194 FND_MSG_PUB.Count_And_Get
195 ( p_count => x_msg_count ,
196 p_data => x_msg_data );
197
198 END delete_status_pvt;
199
200 --------------------------------------------------------------------------------
201
202 end PA_STATUSES_PVT;