[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SRP_TERRITORIES_PUB
Source
1 PACKAGE BODY jtf_rs_srp_territories_pub AS
2 /* $Header: jtfrspib.pls 120.0 2005/05/11 08:21:11 appldev ship $ */
3
4 /*****************************************************************************************
5 This is a public API that caller will invoke.
6 It provides procedures for managing resource salesrep territories, like
7 create, update and delete resource salesrep territories from other modules.
8 Its main procedures are as following:
9 Create Resource Salesrep Territories
10 Update Resource Salesrep Territories
11 Delete Resource Salesrep Territories
12 Calls to these procedures will invoke procedures from jtf_rs_srp_territories_pvt
13 to do business validations and to do actual inserts, updates and deletes into tables.
14 ******************************************************************************************/
15
16 /* Package variables. */
17
18 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_SRP_TERRITORIES_PUB';
19
20 /* Procedure to create the resource salesrep territories
21 based on input values passed by calling routines. */
22
23 PROCEDURE create_rs_srp_territories
24 (P_API_VERSION IN NUMBER,
25 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
26 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
27 P_SALESREP_ID IN JTF_RS_SRP_TERRITORIES.SALESREP_ID%TYPE,
28 P_TERRITORY_ID IN JTF_RS_SRP_TERRITORIES.TERRITORY_ID%TYPE,
29 P_STATUS IN JTF_RS_SRP_TERRITORIES.STATUS%TYPE DEFAULT NULL,
30 P_WH_UPDATE_DATE IN JTF_RS_SRP_TERRITORIES.WH_UPDATE_DATE%TYPE DEFAULT NULL,
31 P_START_DATE_ACTIVE IN JTF_RS_SRP_TERRITORIES.START_DATE_ACTIVE%TYPE,
32 P_END_DATE_ACTIVE IN JTF_RS_SRP_TERRITORIES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
33 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
34 X_MSG_COUNT OUT NOCOPY NUMBER,
35 X_MSG_DATA OUT NOCOPY VARCHAR2,
36 X_SALESREP_TERRITORY_ID OUT NOCOPY JTF_RS_SRP_TERRITORIES.SALESREP_TERRITORY_ID%TYPE
37 ) IS
38
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RS_SRP_TERRITORIES';
41 l_salesrep_id jtf_rs_srp_territories.salesrep_id%type := p_salesrep_id;
42 l_territory_id jtf_rs_srp_territories.territory_id%type := p_territory_id;
43 l_status jtf_rs_srp_territories.status%type := p_status;
44 l_wh_update_date jtf_rs_srp_territories.wh_update_date%type := p_wh_update_date;
45 l_start_date_active jtf_rs_srp_territories.start_date_active%type := p_start_date_active;
46 l_end_date_active jtf_rs_srp_territories.end_date_active%type := p_end_date_active;
47 l_salesrep_territory_id jtf_rs_srp_territories.salesrep_territory_id%type;
48 l_org_id number;
49
50
51 CURSOR c_salesrep_id IS
52 SELECT salesrep_id
53 FROM jtf_rs_salesreps
54 WHERE salesrep_id = l_salesrep_id;
55 l_salesrep jtf_rs_srp_territories.salesrep_id%type ;
56
57
58 BEGIN
59 SAVEPOINT create_rs_srp_territories_pub;
60 x_return_status := fnd_api.g_ret_sts_success;
61 --DBMS_OUTPUT.put_line(' Started Create RS SRP Territories Pub ');
62
63 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
64 RAISE fnd_api.g_exc_unexpected_error;
65 END IF;
66
67 IF fnd_api.to_boolean(p_init_msg_list) THEN
68 fnd_msg_pub.initialize;
69 END IF;
70
71 --Put in all the Validations here
72
73 --Validate the Salesrep Id
74
75 IF p_salesrep_id IS NULL THEN
76 -- dbms_output.put_line('Salesrep Id is null');
77 fnd_message.set_name('JTF', 'JTF_RS_SALESREP_ID_NULL');
78 fnd_msg_pub.add;
79 x_return_status := fnd_api.g_ret_sts_unexp_error;
80 END IF;
81 IF p_salesrep_id IS NOT NULL THEN
82 OPEN c_salesrep_id;
83 FETCH c_salesrep_id INTO l_salesrep;
84 IF c_salesrep_id%NOTFOUND THEN
85 -- dbms_output.put_line('Invalid Salesrep Id');
86 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SALESREP_ID');
87 fnd_message.set_token('P_SALESREP_ID', p_salesrep_id);
88 fnd_msg_pub.add;
89 x_return_status := fnd_api.g_ret_sts_unexp_error;
90 END IF;
91 CLOSE c_salesrep_id;
92 END IF;
93
94 /* jtf_resource_utl.validate_salesrep_id(
95 p_salesrep_id => l_salesrep_id,
96 p_org_id => l_org_id,
97 x_return_status => x_return_status
98 );
99 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
100 x_return_status := fnd_api.g_ret_sts_unexp_error;
101 RAISE fnd_api.g_exc_unexpected_error;
102 END IF;
103 */
104 --End of Salesrep Id Validation
105
106 --Validate the Territory Id
107 jtf_resource_utl.validate_territory_id(
108 p_territory_id => l_territory_id,
109 x_return_status => x_return_status
110 );
111 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
112 x_return_status := fnd_api.g_ret_sts_unexp_error;
113 RAISE fnd_api.g_exc_unexpected_error;
114 END IF;
115 --End of Territory Id Validation
116
117 --Call the Private API for create_rs_srp_territories
118
119 jtf_rs_srp_territories_pvt.create_rs_srp_territories
120 (P_API_VERSION => 1,
121 P_INIT_MSG_LIST => fnd_api.g_false,
122 P_COMMIT => fnd_api.g_false,
123 P_SALESREP_ID => l_salesrep_id,
124 P_TERRITORY_ID => l_territory_id,
125 P_STATUS => l_status,
126 P_WH_UPDATE_DATE => l_wh_update_date,
127 P_START_DATE_ACTIVE => l_start_date_active,
128 P_END_DATE_ACTIVE => l_end_date_active,
129 X_RETURN_STATUS => x_return_status,
130 X_MSG_COUNT => x_msg_count,
131 X_MSG_DATA => x_msg_data,
132 X_SALESREP_TERRITORY_ID => x_salesrep_territory_id
133 );
134
135 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
136 --dbms_output.put_line('Failed status from call to private procedure');
137 RAISE fnd_api.g_exc_unexpected_error;
138 END IF;
139
140 IF fnd_api.to_boolean(p_commit) THEN
141 COMMIT WORK;
142 END IF;
143 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
144
145 EXCEPTION
146 WHEN fnd_api.g_exc_unexpected_error THEN
147 --DBMS_OUTPUT.put_line (' ========================================== ');
148 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
149 ROLLBACK TO create_rs_srp_territories_pub;
150 x_return_status := fnd_api.g_ret_sts_unexp_error;
151 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
152 WHEN OTHERS THEN
153 --DBMS_OUTPUT.put_line (' ========================================== ');
154 --DBMS_OUTPUT.put_line (' =========== Raised Others in Create Salesrep Territories Pub ============= ');
155 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
156 ROLLBACK TO create_rs_srp_territories_pub;
157 x_return_status := fnd_api.g_ret_sts_unexp_error;
158 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
159
160 END create_rs_srp_territories;
161
162 --Procedure to update the resource salesrep territories based on input values passed by calling routines
163
164 PROCEDURE update_rs_srp_territories
165 (P_API_VERSION IN NUMBER,
166 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
167 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
168 P_SALESREP_ID IN JTF_RS_SRP_TERRITORIES.SALESREP_ID%TYPE,
169 P_TERRITORY_ID IN JTF_RS_SRP_TERRITORIES.TERRITORY_ID%TYPE,
170 P_STATUS IN JTF_RS_SRP_TERRITORIES.STATUS%TYPE DEFAULT FND_API.G_MISS_CHAR,
171 P_WH_UPDATE_DATE IN JTF_RS_SRP_TERRITORIES.WH_UPDATE_DATE%TYPE DEFAULT FND_API.G_MISS_DATE,
172 P_START_DATE_ACTIVE IN JTF_RS_SRP_TERRITORIES.START_DATE_ACTIVE%TYPE DEFAULT FND_API.G_MISS_DATE,
173 P_END_DATE_ACTIVE IN JTF_RS_SRP_TERRITORIES.END_DATE_ACTIVE%TYPE DEFAULT FND_API.G_MISS_DATE,
174 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_SRP_TERRITORIES.OBJECT_VERSION_NUMBER%TYPE,
175 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
176 X_MSG_COUNT OUT NOCOPY NUMBER,
177 X_MSG_DATA OUT NOCOPY VARCHAR2
178 ) IS
179
180 l_api_version CONSTANT NUMBER := 1.0;
181 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RS_SRP_TERRITORIES';
182 l_salesrep_id jtf_rs_srp_territories.salesrep_id%type := p_salesrep_id;
183 l_territory_id jtf_rs_srp_territories.territory_id%type := p_territory_id;
184 l_status jtf_rs_srp_territories.status%type := p_status;
185 l_wh_update_date jtf_rs_srp_territories.wh_update_date%type := p_wh_update_date;
186 l_start_date_active jtf_rs_srp_territories.start_date_active%type := p_start_date_active;
187 l_end_date_active jtf_rs_srp_territories.end_date_active%type := p_end_date_active;
188 l_attribute1 jtf_rs_srp_territories.attribute1%type;
189 l_attribute2 jtf_rs_srp_territories.attribute2%type;
190 l_attribute3 jtf_rs_srp_territories.attribute3%type;
191 l_attribute4 jtf_rs_srp_territories.attribute4%type;
192 l_attribute5 jtf_rs_srp_territories.attribute5%type;
193 l_attribute6 jtf_rs_srp_territories.attribute6%type;
194 l_attribute7 jtf_rs_srp_territories.attribute7%type;
195 l_attribute8 jtf_rs_srp_territories.attribute8%type;
196 l_attribute9 jtf_rs_srp_territories.attribute9%type;
197 l_attribute10 jtf_rs_srp_territories.attribute10%type;
198 l_attribute11 jtf_rs_srp_territories.attribute11%type;
199 l_attribute12 jtf_rs_srp_territories.attribute12%type;
200 l_attribute13 jtf_rs_srp_territories.attribute13%type;
201 l_attribute14 jtf_rs_srp_territories.attribute14%type;
202 l_attribute15 jtf_rs_srp_territories.attribute15%type;
203 l_attribute_category jtf_rs_srp_territories.attribute_category%type;
204 l_salesrep_territory_id jtf_rs_srp_territories.salesrep_territory_id%type;
205 l_object_version_number jtf_rs_srp_territories.object_version_number%type := p_object_version_number;
206 l_org_id number;
207
208 CURSOR c_salesrep_id IS
209 SELECT salesrep_id
210 FROM jtf_rs_salesreps
211 WHERE salesrep_id = l_salesrep_id;
212 l_salesrep jtf_rs_srp_territories.salesrep_id%type ;
213
214 BEGIN
215 SAVEPOINT update_rs_srp_territories_pub;
216 x_return_status := fnd_api.g_ret_sts_success;
217 --DBMS_OUTPUT.put_line(' Started Update Salesrep Territory Pub ');
218
219 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
220 RAISE fnd_api.g_exc_unexpected_error;
221 END IF;
222
223 IF fnd_api.to_boolean(p_init_msg_list) THEN
224 fnd_msg_pub.initialize;
225 END IF;
226
227 --Put all Validations here
228
229 --Validate the Salesrep Id
230
231 IF p_salesrep_id IS NULL THEN
232 -- dbms_output.put_line('Salesrep Id is null');
233 fnd_message.set_name('JTF', 'JTF_RS_SALESREP_ID_NULL');
234 fnd_msg_pub.add;
235 x_return_status := fnd_api.g_ret_sts_unexp_error;
236 END IF;
237 IF p_salesrep_id IS NOT NULL THEN
238 OPEN c_salesrep_id;
239 FETCH c_salesrep_id INTO l_salesrep;
240 IF c_salesrep_id%NOTFOUND THEN
241 -- dbms_output.put_line('Invalid Salesrep Id');
242 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SALESREP_ID');
243 fnd_message.set_token('P_SALESREP_ID', p_salesrep_id);
244 fnd_msg_pub.add;
245 x_return_status := fnd_api.g_ret_sts_unexp_error;
246 END IF;
247 CLOSE c_salesrep_id;
248 END IF;
249
250 /* jtf_resource_utl.validate_salesrep_id(
251 p_salesrep_id => l_salesrep_id,
252 x_return_status => x_return_status
253 );
254 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
255 x_return_status := fnd_api.g_ret_sts_unexp_error;
256 RAISE fnd_api.g_exc_unexpected_error;
257 END IF;
258 */
259 --End of Salesrep Id Validation
260
261 --Validate the Territory Id
262 jtf_resource_utl.validate_territory_id(
263 p_territory_id => l_territory_id,
264 x_return_status => x_return_status
265 );
266 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
267 x_return_status := fnd_api.g_ret_sts_unexp_error;
268 RAISE fnd_api.g_exc_unexpected_error;
269 END IF;
270 --End of Territory Id Validation
271
272 SELECT salesrep_territory_id INTO l_salesrep_territory_id
273 FROM jtf_rs_srp_territories
274 WHERE salesrep_id = l_salesrep_id
275 AND territory_id = l_territory_id;
276
277 --Call the private procedure for update
278
279 jtf_rs_srp_territories_pvt.update_rs_srp_territories
280 (P_API_VERSION => 1,
281 P_INIT_MSG_LIST => fnd_api.g_false,
282 P_COMMIT => fnd_api.g_false,
283 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
284 P_STATUS => l_status,
285 P_WH_UPDATE_DATE => l_wh_update_date,
286 P_START_DATE_ACTIVE => l_start_date_active,
287 P_END_DATE_ACTIVE => l_end_date_active,
288 P_OBJECT_VERSION_NUMBER => l_object_version_number,
289 X_RETURN_STATUS => x_return_status,
290 X_MSG_COUNT => x_msg_count,
291 X_MSG_DATA => x_msg_data
292 );
293
294 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
295 --dbms_output.put_line('Failed status from call to private procedure');
296 RAISE fnd_api.g_exc_unexpected_error;
297 END IF;
298 IF fnd_api.to_boolean(p_commit) THEN
299 COMMIT WORK;
300 END IF;
301
302 p_object_version_number := l_object_version_number;
303
304 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
305 EXCEPTION
306 WHEN fnd_api.g_exc_unexpected_error THEN
307 --DBMS_OUTPUT.put_line (' ========================================== ');
308 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
309 ROLLBACK TO update_rs_srp_territories_pub;
310 x_return_status := fnd_api.g_ret_sts_unexp_error;
311 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
312 WHEN OTHERS THEN
313 --DBMS_OUTPUT.put_line (' ========================================== ');
314 --DBMS_OUTPUT.put_line (' =========== Raised Others in Update Salesrep Territories Pub ============= ');
315 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
316 ROLLBACK TO update_rs_srp_territories_pub;
317 x_return_status := fnd_api.g_ret_sts_unexp_error;
318 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
319
320 END update_rs_srp_territories;
321
322 END jtf_rs_srp_territories_pub;