1 PACKAGE BODY AS_SALES_LEAD_RANKS_PUB AS
2 /* #$Header: asxprnkb.pls 120.0 2005/06/09 17:39:49 appldev noship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_LEAD_RANKS_PUB
5 -- Purpose : to add ranks into AS_SALES_LEAD_RANKS_B and _TL
6 -- History : 07/24/2000 raverma created
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AS_SALES_LEAD_RANKS_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxprnkb.pls';
13 /*
14 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
16 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
17 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
18 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
19 G_VERSION_NUM NUMBER := 2.0;
20 */
21
22 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
23 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
24 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
25 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
26
27 PROCEDURE Create_Rank (p_api_version IN NUMBER := 2.0,
28 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
29 p_commit IN VARCHAR2 := FND_API.G_FALSE,
30 p_validation_level IN NUMBER :=
31 FND_API.G_VALID_LEVEL_FULL,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 p_sales_lead_rank_rec IN AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type,
36 x_sales_lead_rank_id OUT NOCOPY NUMBER) IS
37
38 l_api_name CONSTANT VARCHAR2(30) := 'Create_Rank';
39 l_api_version CONSTANT NUMBER := 2.0;
40 l_return_status VARCHAR2(1);
41 l_msg_count NUMBER;
42 l_msg_data VARCHAR2(32767);
43 l_sales_lead_rank_rec AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type;
44 l_rank_id NUMBER;
45 l_dummy CHAR(1);
46
47 CURSOR c1 IS SELECT 'X' FROM AS_SALES_LEAD_RANKS_B
48 WHERE rank_id = p_sales_lead_rank_rec.RANK_ID;
49
50 BEGIN
51 -- Standard start of API savepoint
52 SAVEPOINT Create_Rank_PVT;
53
54 -- Standard call to check for call compatibility
55 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58
59 -- Initialize message list if p_init_msg_list is set to TRUE
60 IF FND_API.To_Boolean(p_init_msg_list) THEN
61 FND_MSG_PUB.initialize;
62 END IF;
63
64 -- Initialize API return status to success
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 -- API body
68 l_sales_lead_rank_rec := p_sales_lead_rank_rec;
69 AS_SALES_LEAD_RANKS_PVT.Create_Rank (p_api_version => l_api_version,
70 p_commit => p_commit,
71 x_return_status => l_return_status,
72 x_msg_count => l_msg_count,
73 x_msg_data => l_msg_data,
74 p_sales_lead_rank_rec => l_sales_lead_rank_rec,
75 x_sales_lead_rank_id => l_rank_id);
76 x_sales_lead_rank_id := l_rank_id;
77 x_return_status := l_return_status;
78 --dbms_output.put_line('public API returns ' || l_return_status);
79 -- End of API body
80
81 -- Standard check of p_commit
82 IF FND_API.To_Boolean(p_commit) THEN
83 COMMIT WORK;
84 END IF;
85
86 -- Standard call to get message count and if count is 1, get message info
87 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
88 EXCEPTION
89
90 WHEN FND_API.G_EXC_ERROR THEN
91 ROLLBACK TO Create_Rank_PVT;
92 x_return_status := FND_API.G_RET_STS_ERROR;
93 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
94
95 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
96 ROLLBACK TO Create_Rank_PVT;
97 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
98 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
99
100 WHEN OTHERS THEN
101 ROLLBACK TO Create_Rank_PVT;
102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
103 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
104 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
105 END IF;
106 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
107
108 END Create_Rank;
109
110 PROCEDURE Update_Rank (p_api_version IN NUMBER := 2.0,
111 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
112 p_commit IN VARCHAR2 := FND_API.G_FALSE,
113 p_validation_level IN NUMBER :=
114 FND_API.G_VALID_LEVEL_FULL,
115 x_return_status OUT NOCOPY VARCHAR2,
116 x_msg_count OUT NOCOPY NUMBER,
117 x_msg_data OUT NOCOPY VARCHAR2,
118 p_sales_lead_rank_rec IN AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type) IS
119
120 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rank';
121 l_api_version CONSTANT NUMBER := 2.0;
122 l_return_status VARCHAR2(1);
123 l_msg_count NUMBER;
124 l_msg_data VARCHAR2(32767);
125 l_sales_lead_rank_rec AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type;
126 l_dummy CHAR(1);
127
128 CURSOR c1 IS SELECT 'X' FROM AS_SALES_LEAD_RANKS_B
129 WHERE rank_id = p_sales_lead_rank_rec.RANK_ID;
130
131 BEGIN
132 -- Standard start of API savepoint
133 SAVEPOINT Update_Rank_PVT;
134
135 -- Standard call to check for call compatibility
136 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 END IF;
139
140 -- Initialize message list if p_init_msg_list is set to TRUE
141 IF FND_API.To_Boolean(p_init_msg_list) THEN
142 FND_MSG_PUB.initialize;
143 END IF;
144
145 l_sales_lead_rank_rec := p_sales_lead_rank_rec;
146
147 -- Initialize API return status to success
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 -- API body
151 AS_SALES_LEAD_RANKS_PVT.Update_Rank (p_api_version => l_api_version,
152 p_commit => p_commit,
153 x_return_status => l_return_status,
154 x_msg_count => l_msg_count,
155 x_msg_data => l_msg_data,
156 p_sales_lead_rank_rec => l_sales_lead_rank_rec);
157 x_return_status := l_return_status;
158 -- End of API body
159
160 -- Standard check of p_commit
161 IF FND_API.To_Boolean(p_commit) THEN
162 COMMIT WORK;
163 END IF;
164
165 -- Standard call to get message count and if count is 1, get message info
166 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
167 EXCEPTION
168 WHEN FND_API.G_EXC_ERROR THEN
169 ROLLBACK TO Update_Rank_PVT;
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
172
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 ROLLBACK TO Update_Rank_PVT;
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
177
178 WHEN OTHERS THEN
179 ROLLBACK TO Update_Rank_PVT;
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
182 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
183 END IF;
184 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
185 END Update_Rank;
186
187 Procedure Delete_Rank (p_api_version IN NUMBER := 2.0,
188 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
189 p_commit IN VARCHAR2 := FND_API.G_FALSE,
190 p_validation_level IN NUMBER :=
191 FND_API.G_VALID_LEVEL_FULL,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 p_sales_lead_rank_id IN NUMBER) IS
196
197 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rank';
198 l_api_version CONSTANT NUMBER := 2.0;
199 l_return_status VARCHAR2(1);
200 l_msg_count NUMBER;
201 l_msg_data VARCHAR2(32767);
202 l_sales_lead_rank_id NUMBER;
203 l_dummy CHAR(1);
204
205 BEGIN
206 -- Standard start of API savepoint
207 SAVEPOINT Delete_Rank_PVT;
208
209 -- Standard call to check for call compatibility
210 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213
214 -- Initialize message list if p_init_msg_list is set to TRUE
215 IF FND_API.To_Boolean(p_init_msg_list) THEN
216 FND_MSG_PUB.initialize;
217 END IF;
218
219 l_sales_lead_rank_id := p_sales_lead_rank_id;
220
221 -- Initialize API return status to success
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223
224 --dbms_output.put_line('before delete pvt');
225 -- API body
226 AS_SALES_LEAD_RANKS_PVT.Delete_Rank (p_api_version => 2.0,
227 p_commit => p_commit,
228 x_return_status => l_return_status,
229 x_msg_count => l_msg_count,
230 x_msg_data => l_msg_data,
231 p_sales_lead_rank_id => l_sales_lead_rank_id);
232 x_return_status := l_return_status;
233 -- End of API body
234
235 -- Standard check of p_commit
236 IF FND_API.To_Boolean(p_commit) THEN
237 COMMIT WORK;
238 --dbms_output.put_line('after delete pvt - commit');
239 END IF;
240
241 -- Standard call to get message count and if count is 1, get message info
242 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
243 EXCEPTION
244 WHEN FND_API.G_EXC_ERROR THEN
245 ROLLBACK TO Delete_Rank_PVT;
246 x_return_status := FND_API.G_RET_STS_ERROR;
247 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
248
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
250 ROLLBACK TO Delete_Rank_PVT;
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
253
254 WHEN OTHERS THEN
255 ROLLBACK TO Delete_Rank_PVT;
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
258 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
259 END IF;
260 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
261 END DELETE_RANK;
262
263 END;