[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SRP_TERRITORIES_PVT
Source
1 PACKAGE BODY jtf_rs_srp_territories_pvt AS
2 /* $Header: jtfrsvib.pls 120.0 2005/05/11 08:23:02 appldev ship $ */
3
4 /*****************************************************************************************
5 This is a private API that caller will invoke.
6 It provides procedures for managing resource salesrep territories, like
7 create and update resource salesrep territories, from other modules.
8 Its main procedures are as following:
9 Create Resource Salesrep Territories
10 Update Resource Salesrep Territories
11 Calls to these procedures will invoke calls to table handlers (jtf_rs_srp_territories_pkg)
12 which do the actual inserts, updates and deletes into tables.
13 ******************************************************************************************/
14
15 /* Package variables. */
16
17 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_SRP_TERRITORIES_PVT';
18
19 /* Procedure to create the resource salesrep territories
20 based on input values passed by calling routines. */
21
22 PROCEDURE create_rs_srp_territories
23 (P_API_VERSION IN NUMBER,
24 P_INIT_MSG_LIST IN VARCHAR2,
25 P_COMMIT IN VARCHAR2,
26 P_SALESREP_ID IN JTF_RS_SRP_TERRITORIES.SALESREP_ID%TYPE,
27 P_TERRITORY_ID IN JTF_RS_SRP_TERRITORIES.TERRITORY_ID%TYPE,
28 P_STATUS IN JTF_RS_SRP_TERRITORIES.STATUS%TYPE,
29 P_WH_UPDATE_DATE IN JTF_RS_SRP_TERRITORIES.WH_UPDATE_DATE%TYPE,
30 P_START_DATE_ACTIVE IN JTF_RS_SRP_TERRITORIES.START_DATE_ACTIVE%TYPE,
31 P_END_DATE_ACTIVE IN JTF_RS_SRP_TERRITORIES.END_DATE_ACTIVE%TYPE,
32 P_ATTRIBUTE2 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE2%TYPE,
33 P_ATTRIBUTE3 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE3%TYPE,
34 P_ATTRIBUTE4 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE4%TYPE,
35 P_ATTRIBUTE5 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE5%TYPE,
36 P_ATTRIBUTE6 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE6%TYPE,
37 P_ATTRIBUTE7 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE7%TYPE,
38 P_ATTRIBUTE8 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE8%TYPE,
39 P_ATTRIBUTE9 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE9%TYPE,
40 P_ATTRIBUTE1 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE1%TYPE,
41 P_ATTRIBUTE10 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE10%TYPE,
42 P_ATTRIBUTE11 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE11%TYPE,
43 P_ATTRIBUTE12 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE12%TYPE,
44 P_ATTRIBUTE13 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE13%TYPE,
45 P_ATTRIBUTE14 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE14%TYPE,
46 P_ATTRIBUTE15 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE15%TYPE,
47 P_ATTRIBUTE_CATEGORY IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE_CATEGORY%TYPE,
48 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
49 X_MSG_COUNT OUT NOCOPY NUMBER,
50 X_MSG_DATA OUT NOCOPY VARCHAR2,
51 X_SALESREP_TERRITORY_ID OUT NOCOPY JTF_RS_SRP_TERRITORIES.SALESREP_TERRITORY_ID%TYPE
52 )IS
53
54 l_api_version CONSTANT NUMBER := 1.0;
55 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RS_SRP_TERRITORIES';
56 l_rowid ROWID;
57 l_salesrep_id jtf_rs_srp_territories.salesrep_id%type := p_salesrep_id;
58 l_territory_id jtf_rs_srp_territories.territory_id%type := p_territory_id;
59 l_start_date_active jtf_rs_srp_territories.start_date_active%type := p_start_date_active;
60 l_end_date_active jtf_rs_srp_territories.end_date_active%type := p_end_date_active;
61 l_wh_update_date jtf_rs_srp_territories.wh_update_date%type := p_wh_update_date;
62 l_status jtf_rs_srp_territories.status%type := p_status;
63 l_salesrep_territory_id jtf_rs_srp_territories.salesrep_territory_id%type;
64 l_attribute1 jtf_rs_srp_territories.attribute1%type := p_attribute1;
65 l_attribute2 jtf_rs_srp_territories.attribute2%type := p_attribute2;
66 l_attribute3 jtf_rs_srp_territories.attribute3%type := p_attribute3;
67 l_attribute4 jtf_rs_srp_territories.attribute4%type := p_attribute4;
68 l_attribute5 jtf_rs_srp_territories.attribute5%type := p_attribute5;
69 l_attribute6 jtf_rs_srp_territories.attribute6%type := p_attribute6;
70 l_attribute7 jtf_rs_srp_territories.attribute7%type := p_attribute7;
71 l_attribute8 jtf_rs_srp_territories.attribute8%type := p_attribute8;
72 l_attribute9 jtf_rs_srp_territories.attribute9%type := p_attribute9;
73 l_attribute10 jtf_rs_srp_territories.attribute10%type := p_attribute10;
74 l_attribute11 jtf_rs_srp_territories.attribute11%type := p_attribute11;
75 l_attribute12 jtf_rs_srp_territories.attribute12%type := p_attribute12;
76 l_attribute13 jtf_rs_srp_territories.attribute13%type := p_attribute13;
77 l_attribute14 jtf_rs_srp_territories.attribute14%type := p_attribute14;
78 l_attribute15 jtf_rs_srp_territories.attribute15%type := p_attribute15;
79 l_attribute_category jtf_rs_srp_territories.attribute_category%type := p_attribute_category;
80 l_msg_data VARCHAR2(2000);
81 l_msg_count NUMBER;
82 l_salesrep_start_date DATE;
83 l_salesrep_end_date DATE;
84 l_territory_start_date DATE;
85 l_territory_end_date DATE;
86 l_check_count NUMBER;
87 l_check_char VARCHAR2(1);
88 l_bind_data_id NUMBER;
89
90 CURSOR c_jtf_rs_srp_territories( l_rowid IN ROWID ) IS
91 SELECT 'Y'
92 FROM jtf_rs_srp_territories
93 WHERE ROWID = l_rowid;
94
95 CURSOR c_salesrep_details( l_salesrep_id IN NUMBER ) IS
96 SELECT start_date_active,
97 end_date_active
98 FROM jtf_rs_salesreps
99 WHERE SALESREP_ID = l_salesrep_id;
100
101 CURSOR c_territory_details( l_territory_id IN NUMBER ) IS
102 SELECT start_date_active,
103 end_date_active
104 FROM ra_territories
105 WHERE TERRITORY_ID = l_territory_id;
106
107 BEGIN
108
109 SAVEPOINT create_rs_srp_territories_pvt;
110 x_return_status := fnd_api.g_ret_sts_success;
111 --DBMS_OUTPUT.put_line(' Started Create Salesrep Territories Pvt ');
112
113 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
114 RAISE fnd_api.g_exc_unexpected_error;
115 END IF;
116
117 IF fnd_api.to_boolean(p_init_msg_list) THEN
118 fnd_msg_pub.initialize;
119 END IF;
120
121 --Make the pre processing call to the user hooks
122
123 --Pre Call to the Customer Type User Hook
124
125 IF jtf_usr_hks.ok_to_execute(
126 'JTF_RS_SRP_TERRITORIES_PVT',
127 'CREATE_RS_SRP_TERRITORIES',
128 'B',
129 'C')
130 THEN
131 jtf_rs_srp_territories_cuhk.create_rs_srp_territories_pre(
132 P_SALESREP_ID => l_salesrep_id,
133 P_TERRITORY_ID => l_territory_id,
134 P_STATUS => l_status,
135 P_WH_UPDATE_DATE => l_wh_update_date,
136 P_START_DATE_ACTIVE => l_start_date_active,
137 P_END_DATE_ACTIVE => l_end_date_active,
138 X_RETURN_STATUS => x_return_status,
139 X_MSG_COUNT => x_msg_count,
140 X_MSG_DATA => x_msg_data
141 );
142
143 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
144 x_return_status := fnd_api.g_ret_sts_unexp_error;
145 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
146 fnd_msg_pub.add;
147 RAISE fnd_api.g_exc_unexpected_error;
148 END IF;
149 END IF;
150
151 --Pre Call to the Vertical Type User Hook
152
153 IF jtf_usr_hks.ok_to_execute(
154 'JTF_RS_SRP_TERRITORIES_PVT',
155 'CREATE_RS_SRP_TERRITORIES',
156 'B',
157 'V')
158 THEN
159 jtf_rs_srp_territories_vuhk.create_rs_srp_territories_pre(
160 P_SALESREP_ID => l_salesrep_id,
161 P_TERRITORY_ID => l_territory_id,
162 P_STATUS => l_status,
163 P_WH_UPDATE_DATE => l_wh_update_date,
164 P_START_DATE_ACTIVE => l_start_date_active,
165 P_END_DATE_ACTIVE => l_end_date_active,
166 X_RETURN_STATUS => x_return_status,
167 X_MSG_COUNT => x_msg_count,
168 X_MSG_DATA => x_msg_data
169 );
170
171 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
172 x_return_status := fnd_api.g_ret_sts_unexp_error;
173 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
174 fnd_msg_pub.add;
175 RAISE fnd_api.g_exc_unexpected_error;
176 END IF;
177 END IF;
178
179 --Pre Call to the Internal Type User Hook
180
181 IF jtf_usr_hks.ok_to_execute(
182 'JTF_RS_SRP_TERRITORIES_PVT',
183 'CREATE_RS_SRP_TERRITORIES',
184 'B',
185 'I')
186 THEN
187 jtf_rs_srp_territories_iuhk.create_rs_srp_territories_pre(
188 P_SALESREP_ID => l_salesrep_id,
189 P_TERRITORY_ID => l_territory_id,
190 P_STATUS => l_status,
191 P_WH_UPDATE_DATE => l_wh_update_date,
192 P_START_DATE_ACTIVE => l_start_date_active,
193 P_END_DATE_ACTIVE => l_end_date_active,
194 X_RETURN_STATUS => x_return_status,
195 X_MSG_COUNT => x_msg_count,
196 X_MSG_DATA => x_msg_data
197 );
198 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
199 x_return_status := fnd_api.g_ret_sts_unexp_error;
200 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
201 fnd_msg_pub.add;
202 RAISE fnd_api.g_exc_unexpected_error;
203 END IF;
204 END IF;
205
206
207 --Validate if a Salesrep Territory Id already exists for the given Salesrep Id, Territory Id
208 l_check_count := 0;
209
210 SELECT count(*)
211 INTO l_check_count
212 FROM jtf_rs_srp_territories
213 WHERE SALESREP_ID = l_salesrep_id
214 AND TERRITORY_ID = l_territory_id;
215
216 IF l_check_count > 0 THEN
217 --dbms_output.put_line('Salesrep Territory Id already exists for the given Salesrep Id and Territory Id');
218 x_return_status := fnd_api.g_ret_sts_error;
219 fnd_message.set_name('JTF', 'JTF_RS_SRP_TERR_ID_EXISTS');
220 fnd_msg_pub.add;
221 RAISE fnd_api.g_exc_unexpected_error;
222 END IF;
223
224 --Date Validations
225 JTF_RESOURCE_UTL.VALIDATE_INPUT_DATES(
226 p_start_date_active => l_start_date_active,
227 p_end_date_active => l_end_date_active,
228 x_return_status => x_return_status
229 );
230 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
231 x_return_status := fnd_api.g_ret_sts_unexp_error;
232 RAISE fnd_api.g_exc_unexpected_error;
233 END IF;
234 --End of Date Validations
235
236 --Get the Salesrep Details for the given Salesrep Id
237 OPEN c_salesrep_details(l_salesrep_id);
238 FETCH c_salesrep_details INTO l_salesrep_start_date, l_salesrep_end_date;
239 IF c_salesrep_details%NOTFOUND THEN
240 --dbms_output.put_line('Salesrep information not found');
241 x_return_status := fnd_api.g_ret_sts_unexp_error;
242 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SALESREP_ID');
243 fnd_message.set_token('P_SALESREP_ID', l_salesrep_id);
244 fnd_msg_pub.add;
245 CLOSE c_salesrep_details;
246 RAISE fnd_api.g_exc_unexpected_error;
247 END IF;
248
249 --Validate that the Salesrep start date is less than the passed start date active
250 IF l_start_date_active < l_salesrep_start_date THEN
251 --dbms_output.put_line('Start date active cannot be less than Salesrep Start Date');
252 x_return_status := fnd_api.g_ret_sts_error;
253 fnd_message.set_name('JTF', 'JTF_RS_SRP_INVALID_START_DATE');
254 fnd_message.set_token('P_SALESREP_START_DATE', l_salesrep_start_date);
255 fnd_msg_pub.add;
256 RAISE fnd_api.g_exc_unexpected_error;
257 END IF;
258
259 --Validate that the passed end date is not Null and less than Salesrep End Date
260 IF l_salesrep_end_date is NOT NULL THEN
261 IF l_end_date_active is NULL THEN
262 --dbms_output.put_line ('End date active cannot be Null as Salesrep has an End date');
263 x_return_status := fnd_api.g_ret_sts_error;
264 fnd_message.set_name('JTF', 'JTF_RS_SRP_END_DATE_NULL');
265 fnd_message.set_token('P_SALESREP_END_DATE', l_salesrep_end_date);
266 fnd_msg_pub.add;
267 RAISE fnd_api.g_exc_unexpected_error;
268 ELSIF p_end_date_active > l_salesrep_end_date THEN
269 --dbms_output.put_line('End date active cannot be greater than Salesrep End Date');
270 x_return_status := fnd_api.g_ret_sts_error;
271 fnd_message.set_name('JTF', 'JTF_RS_SRP_INVALID_END_DATE');
272 fnd_message.set_token('P_SALESREP_END_DATE', l_salesrep_end_date);
273 fnd_msg_pub.add;
274 RAISE fnd_api.g_exc_unexpected_error;
275 END IF;
276 END IF;
277
278 --Get the Territory Details for the given Territory Id
279 OPEN c_territory_details(p_territory_id);
280 FETCH c_territory_details INTO l_territory_start_date, l_territory_end_date;
281 IF c_territory_details%NOTFOUND THEN
282 --dbms_output.put_line('Territory information not found');
283 x_return_status := fnd_api.g_ret_sts_unexp_error;
284 fnd_message.set_name('JTF', 'JTF_RS_INVALID_TERRITORY_ID');
285 fnd_message.set_token('P_TERRITORY_ID', p_territory_id);
286 fnd_msg_pub.add;
287 CLOSE c_territory_details;
288 RAISE fnd_api.g_exc_unexpected_error;
289 END IF;
290
291 --Validate that the Territory start date is less than the passed start date active
292 IF p_start_date_active < l_territory_start_date THEN
293 --dbms_output.put_line('Start date active cannot be less than Territory Start Date');
294 x_return_status := fnd_api.g_ret_sts_error;
295 fnd_message.set_name('JTF', 'JTF_RS_TER_INVALID_START_DATE');
296 fnd_message.set_token('P_TERRITORY_START_DATE', l_territory_start_date);
297 fnd_msg_pub.add;
298 RAISE fnd_api.g_exc_unexpected_error;
299 END IF;
300
301 --Validate that the passed end date is not Null and less than Territory End Date
302 IF l_territory_end_date is NOT NULL THEN
303 IF l_end_date_active is NULL THEN
304 --dbms_output.put_line ('End date active cannot be Null as Territory has an End date');
305 x_return_status := fnd_api.g_ret_sts_error;
306 fnd_message.set_name('JTF', 'JTF_RS_TER_END_DATE_NULL');
307 fnd_message.set_token('P_TERRITORY_END_DATE', l_territory_end_date);
308 fnd_msg_pub.add;
309 RAISE fnd_api.g_exc_unexpected_error;
310 ELSIF l_end_date_active > l_territory_end_date THEN
311 --dbms_output.put_line('End date active cannot be greater than Territory End Date');
312 x_return_status := fnd_api.g_ret_sts_error;
313 fnd_message.set_name('JTF', 'JTF_RS_TER_INVALID_END_DATE');
314 fnd_message.set_token('P_TERRITORY_END_DATE', l_territory_end_date);
315 fnd_msg_pub.add;
316 RAISE fnd_api.g_exc_unexpected_error;
317 END IF;
318 END IF;
319
320 --Get the next value of the Salesrep_Territory_Id from the sequence
321 SELECT jtf_rs_srp_territories_s.nextval
322 INTO l_salesrep_territory_id
323 FROM dual;
324
325 --Insert the row into the table by calling the table handler
326 jtf_rs_srp_territories_pkg.insert_row(
327 X_ROWID => l_rowid,
328 X_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
329 X_SALESREP_ID => l_salesrep_id,
330 X_TERRITORY_ID => l_territory_id,
331 X_STATUS => l_status,
332 X_START_DATE_ACTIVE => l_start_date_active,
333 X_END_DATE_ACTIVE => l_end_date_active,
334 X_WH_UPDATE_DATE => l_wh_update_date,
335 X_ATTRIBUTE_CATEGORY => l_attribute_category,
336 X_ATTRIBUTE2 => l_attribute2,
337 X_ATTRIBUTE3 => l_attribute3,
338 X_ATTRIBUTE4 => l_attribute4,
339 X_ATTRIBUTE5 => l_attribute5,
340 X_ATTRIBUTE6 => l_attribute6,
341 X_ATTRIBUTE7 => l_attribute7,
342 X_ATTRIBUTE8 => l_attribute8,
343 X_ATTRIBUTE9 => l_attribute9,
344 X_ATTRIBUTE10 => l_attribute10,
345 X_ATTRIBUTE11 => l_attribute11,
346 X_ATTRIBUTE12 => l_attribute12,
347 X_ATTRIBUTE13 => l_attribute13,
348 X_ATTRIBUTE14 => l_attribute14,
349 X_ATTRIBUTE15 => l_attribute15,
350 X_ATTRIBUTE1 => l_attribute1,
351 X_CREATION_DATE => sysdate,
352 X_CREATED_BY => jtf_resource_utl.created_by,
353 X_LAST_UPDATE_DATE => sysdate,
354 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
355 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
356 );
357
358 --dbms_output.put_line('Inserted Row');
359 OPEN c_jtf_rs_srp_territories(l_rowid);
360 FETCH c_jtf_rs_srp_territories INTO l_check_char;
361 IF c_jtf_rs_srp_territories%NOTFOUND THEN
362 --dbms_output.put_line('Error in Table Handler');
363 x_return_status := fnd_api.g_ret_sts_unexp_error;
364 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
365 fnd_msg_pub.add;
366 CLOSE c_jtf_rs_srp_territories;
367 RAISE fnd_api.g_exc_unexpected_error;
368 ELSE
369 --dbms_output.put_line('Salesrep Territory Successfully Created');
370 x_salesrep_territory_id := l_salesrep_territory_id;
371 END IF;
372
373 --Close the cursors
374 CLOSE c_salesrep_details;
375 CLOSE c_territory_details;
376 CLOSE c_jtf_rs_srp_territories;
377
378 --Make the post processing call to the user hooks
379
380 --Post Call to the Customer Type User Hook
381
382 IF jtf_usr_hks.ok_to_execute(
383 'JTF_RS_SRP_TERRITORIES_PVT',
384 'CREATE_RS_SRP_TERRITORIES',
385 'A',
386 'C')
387 THEN
388 jtf_rs_srp_territories_cuhk.create_rs_srp_territories_post(
389 P_SALESREP_ID => l_salesrep_id,
390 P_TERRITORY_ID => l_territory_id,
391 P_STATUS => l_status,
392 P_WH_UPDATE_DATE => l_wh_update_date,
393 P_START_DATE_ACTIVE => l_start_date_active,
394 P_END_DATE_ACTIVE => l_end_date_active,
395 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
396 X_RETURN_STATUS => x_return_status,
397 X_MSG_COUNT => x_msg_count,
398 X_MSG_DATA => x_msg_data
399 );
400
401 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
402 x_return_status := fnd_api.g_ret_sts_unexp_error;
403 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
404 fnd_msg_pub.add;
405 RAISE fnd_api.g_exc_unexpected_error;
406 END IF;
407 END IF;
408
409 --Post Call to the Vertical Type User Hook
410
411 IF jtf_usr_hks.ok_to_execute(
412 'JTF_RS_SRP_TERRITORIES_PVT',
413 'CREATE_RS_SRP_TERRITORIES',
414 'A',
415 'V')
416 THEN
417 jtf_rs_srp_territories_vuhk.create_rs_srp_territories_post(
418 P_SALESREP_ID => l_salesrep_id,
419 P_TERRITORY_ID => l_territory_id,
420 P_STATUS => l_status,
421 P_WH_UPDATE_DATE => l_wh_update_date,
422 P_START_DATE_ACTIVE => l_start_date_active,
423 P_END_DATE_ACTIVE => l_end_date_active,
424 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
425 X_RETURN_STATUS => x_return_status,
426 X_MSG_COUNT => x_msg_count,
427 X_MSG_DATA => x_msg_data
428 );
429
430 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
431 x_return_status := fnd_api.g_ret_sts_unexp_error;
432 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
433 fnd_msg_pub.add;
434 RAISE fnd_api.g_exc_unexpected_error;
435 END IF;
436 END IF;
437
438 --Post Call to the Internal Type User Hook
439
440 IF jtf_usr_hks.ok_to_execute(
441 'JTF_RS_SRP_TERRITORIES_PVT',
442 'CREATE_RS_SRP_TERRITORIES',
443 'A',
444 'I')
445 THEN
446 jtf_rs_srp_territories_iuhk.create_rs_srp_territories_post(
447 P_SALESREP_ID => l_salesrep_id,
448 P_TERRITORY_ID => l_territory_id,
449 P_STATUS => l_status,
450 P_WH_UPDATE_DATE => l_wh_update_date,
451 P_START_DATE_ACTIVE => l_start_date_active,
452 P_END_DATE_ACTIVE => l_end_date_active,
453 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
454 X_RETURN_STATUS => x_return_status,
455 X_MSG_COUNT => x_msg_count,
456 X_MSG_DATA => x_msg_data
457 );
458 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
459 x_return_status := fnd_api.g_ret_sts_unexp_error;
460 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
461 fnd_msg_pub.add;
462 RAISE fnd_api.g_exc_unexpected_error;
463 END IF;
464 END IF;
465
466 /* Standard call for Message Generation */
467
468 IF jtf_usr_hks.ok_to_execute(
469 'JTF_RS_SRP_TERRITORIES_PVT',
470 'CREATE_RS_SRP_TERRITORIES',
471 'M',
472 'M')
473 THEN
474 IF (jtf_rs_srp_territories_cuhk.ok_to_generate_msg(
475 p_salesrep_territory_id => l_salesrep_territory_id,
476 x_return_status => x_return_status) )
477 THEN
478
479 /* Get the bind data id for the Business Object Instance */
480 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
481
482 /* Set bind values for the bind variables in the Business Object SQL */
483 jtf_usr_hks.load_bind_data(l_bind_data_id, 'salesrep_territory_id', l_salesrep_territory_id, 'S', 'N');
484
485 /* Call the message generation API */
486 jtf_usr_hks.generate_message(
487 p_prod_code => 'JTF',
488 p_bus_obj_code => 'RS_SRT',
489 p_action_code => 'I',
490 p_bind_data_id => l_bind_data_id,
491 x_return_code => x_return_status);
492
493 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
494 --dbms_output.put_line('Returned Error status from the Message Generation API');
495 x_return_status := fnd_api.g_ret_sts_unexp_error;
496 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
497 fnd_msg_pub.add;
498 RAISE fnd_api.g_exc_unexpected_error;
499 END IF;
500 END IF;
501 END IF;
502
503 IF fnd_api.to_boolean(p_commit) THEN
504 COMMIT WORK;
505 END IF;
506 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
507
508 EXCEPTION
509 WHEN fnd_api.g_exc_unexpected_error THEN
510 --DBMS_OUTPUT.put_line (' ========================================== ');
511 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
512 ROLLBACK TO create_rs_srp_territories_pvt;
513 x_return_status := fnd_api.g_ret_sts_unexp_error;
514 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
515 WHEN OTHERS THEN
516 --DBMS_OUTPUT.put_line (' ========================================== ');
517 --DBMS_OUTPUT.put_line (' =========== Raised Others in Create Group_member Pvt ============= ');
518 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
519 ROLLBACK TO create_rs_srp_territories_pvt;
520 x_return_status := fnd_api.g_ret_sts_unexp_error;
521 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
522
523 END create_rs_srp_territories;
524
525 --Procedure to update the resource salesrep territories based on input values passed by calling routines
526
527 PROCEDURE update_rs_srp_territories(
528 P_API_VERSION IN NUMBER,
529 P_INIT_MSG_LIST IN VARCHAR2,
530 P_COMMIT IN VARCHAR2,
531 P_SALESREP_TERRITORY_ID IN JTF_RS_SRP_TERRITORIES.SALESREP_TERRITORY_ID%TYPE,
532 P_STATUS IN JTF_RS_SRP_TERRITORIES.STATUS%TYPE,
533 P_WH_UPDATE_DATE IN JTF_RS_SRP_TERRITORIES.WH_UPDATE_DATE%TYPE,
534 P_START_DATE_ACTIVE IN JTF_RS_SRP_TERRITORIES.START_DATE_ACTIVE%TYPE,
535 P_END_DATE_ACTIVE IN JTF_RS_SRP_TERRITORIES.END_DATE_ACTIVE%TYPE,
536 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_SRP_TERRITORIES.OBJECT_VERSION_NUMBER%TYPE,
537 P_ATTRIBUTE2 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE2%TYPE,
538 P_ATTRIBUTE3 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE3%TYPE,
539 P_ATTRIBUTE4 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE4%TYPE,
540 P_ATTRIBUTE5 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE5%TYPE,
541 P_ATTRIBUTE6 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE6%TYPE,
542 P_ATTRIBUTE7 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE7%TYPE,
543 P_ATTRIBUTE8 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE8%TYPE,
544 P_ATTRIBUTE9 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE9%TYPE,
545 P_ATTRIBUTE1 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE1%TYPE,
546 P_ATTRIBUTE10 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE10%TYPE,
547 P_ATTRIBUTE11 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE11%TYPE,
548 P_ATTRIBUTE12 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE12%TYPE,
549 P_ATTRIBUTE13 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE13%TYPE,
550 P_ATTRIBUTE14 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE14%TYPE,
551 P_ATTRIBUTE15 IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE15%TYPE,
552 P_ATTRIBUTE_CATEGORY IN JTF_RS_SRP_TERRITORIES.ATTRIBUTE_CATEGORY%TYPE,
553 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
554 X_MSG_COUNT OUT NOCOPY NUMBER,
555 X_MSG_DATA OUT NOCOPY VARCHAR2
556 )IS
557
558 l_api_version CONSTANT NUMBER := 1.0;
559 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RS_SRP_TERRITORIES';
560 l_status jtf_rs_srp_territories.status%type := p_status;
561 l_wh_update_date jtf_rs_srp_territories.wh_update_date%type := p_wh_update_date;
562 l_salesrep_id jtf_rs_srp_territories.salesrep_id%type;
563 l_territory_id jtf_rs_srp_territories.territory_id%type;
564 l_start_date_active jtf_rs_srp_territories.start_date_active%type := p_start_date_active;
565 l_end_date_active jtf_rs_srp_territories.end_date_active%type := p_end_date_active;
566 l_salesrep_territory_id jtf_rs_srp_territories.salesrep_territory_id%type := p_salesrep_territory_id;
567 l_object_version_number jtf_rs_srp_territories.object_version_number%type := p_object_version_number;
568 l_attribute1 jtf_rs_srp_territories.attribute1%type := p_attribute1;
569 l_attribute2 jtf_rs_srp_territories.attribute2%type := p_attribute2;
570 l_attribute3 jtf_rs_srp_territories.attribute3%type := p_attribute3;
571 l_attribute4 jtf_rs_srp_territories.attribute4%type := p_attribute4;
572 l_attribute5 jtf_rs_srp_territories.attribute5%type := p_attribute5;
573 l_attribute6 jtf_rs_srp_territories.attribute6%type := p_attribute6;
574 l_attribute7 jtf_rs_srp_territories.attribute7%type := p_attribute7;
575 l_attribute8 jtf_rs_srp_territories.attribute8%type := p_attribute8;
576 l_attribute9 jtf_rs_srp_territories.attribute9%type := p_attribute9;
577 l_attribute10 jtf_rs_srp_territories.attribute10%type := p_attribute10;
578 l_attribute11 jtf_rs_srp_territories.attribute11%type := p_attribute11;
579 l_attribute12 jtf_rs_srp_territories.attribute12%type := p_attribute12;
580 l_attribute13 jtf_rs_srp_territories.attribute13%type := p_attribute13;
581 l_attribute14 jtf_rs_srp_territories.attribute14%type := p_attribute14;
582 l_attribute15 jtf_rs_srp_territories.attribute15%type := p_attribute15;
583 l_attribute_category jtf_rs_srp_territories.attribute_category%type := p_attribute_category;
584 l_msg_data VARCHAR2(2000);
585 l_msg_count NUMBER;
586 l_salesrep_start_date DATE;
587 l_salesrep_end_date DATE;
588 l_territory_start_date DATE;
589 l_territory_end_date DATE;
590 l_check_count NUMBER;
591 l_check_char VARCHAR2(1);
592 l_bind_data_id NUMBER;
593
594 CURSOR c_salesrep_territory_id( l_salesrep_territory_id IN NUMBER) IS
595 SELECT salesrep_id, territory_id
596 FROM jtf_rs_srp_territories
597 WHERE salesrep_territory_id = l_salesrep_territory_id;
598
599 CURSOR c_salesrep_territory_update( l_salesrep_territory_id IN NUMBER ) IS
600 SELECT
601 DECODE(p_start_date_active, fnd_api.g_miss_date, start_date_active, p_start_date_active) l_start_date_active,
602 DECODE(p_end_date_active, fnd_api.g_miss_date, end_date_active, p_end_date_active) l_end_date_active,
603 DECODE(p_status, fnd_api.g_miss_char, status, p_status) l_status,
604 DECODE(p_wh_update_date, fnd_api.g_miss_date, wh_update_date, p_wh_update_date) l_wh_update_date,
605 DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
606 DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
607 DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
608 DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
609 DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
610 DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
611 DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
612 DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
613 DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
614 DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
615 DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
616 DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
617 DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
618 DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
619 DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
620 DECODE(p_attribute_category,fnd_api.g_miss_char, attribute1, p_attribute_category) l_attribute_category
621 FROM jtf_rs_srp_territories
622 WHERE salesrep_territory_id = l_salesrep_territory_id;
623
624 salesrep_territory_rec c_salesrep_territory_update%ROWTYPE;
625
626 CURSOR c_salesrep_details( l_salesrep_territory_id IN NUMBER ) IS
627 SELECT jrs.start_date_active,
628 jrs.end_date_active
629 FROM jtf_rs_salesreps jrs, jtf_rs_srp_territories jst
630 WHERE jrs.salesrep_id = jst.salesrep_id
631 AND jst.salesrep_territory_id = l_salesrep_territory_id;
632
633 CURSOR c_territory_details( l_salesrep_territory_id IN NUMBER ) IS
634 SELECT rt.start_date_active,
635 rt.end_date_active
636 FROM ra_territories rt, jtf_rs_srp_territories jst
637 WHERE rt.territory_id = jst.territory_id
638 AND jst.salesrep_territory_id = l_salesrep_territory_id;
639
640 BEGIN
641 SAVEPOINT update_rs_srp_territories_pvt;
642 x_return_status := fnd_api.g_ret_sts_success;
643 --DBMS_OUTPUT.put_line(' Started Update Salesrep Territories Pvt ');
644
645 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
646 RAISE fnd_api.g_exc_unexpected_error;
647 END IF;
648
649 IF fnd_api.to_boolean(p_init_msg_list) THEN
650 fnd_msg_pub.initialize;
651 END IF;
652
653 --Make the pre processing call to the user hooks
654
655 --Pre Call to the Customer Type User Hook
656
657 IF jtf_usr_hks.ok_to_execute(
658 'JTF_RS_SRP_TERRITORIES_PVT',
659 'UPDATE_RS_SRP_TERRITORIES',
660 'B',
661 'C')
662 THEN
663 jtf_rs_srp_territories_cuhk.update_rs_srp_territories_pre(
664 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
665 P_STATUS => l_status,
666 P_WH_UPDATE_DATE => l_wh_update_date,
667 P_START_DATE_ACTIVE => l_start_date_active,
668 P_END_DATE_ACTIVE => l_end_date_active,
669 X_RETURN_STATUS => x_return_status,
670 X_MSG_COUNT => x_msg_count,
671 X_MSG_DATA => x_msg_data
672 );
673
674 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
675 x_return_status := fnd_api.g_ret_sts_unexp_error;
676 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
677 fnd_msg_pub.add;
678 RAISE fnd_api.g_exc_unexpected_error;
679 END IF;
680 END IF;
681
682 --Pre Call to the Vertical Type User Hook
683
684 IF jtf_usr_hks.ok_to_execute(
685 'JTF_RS_SRP_TERRITORIES_PVT',
686 'UPDATE_RS_SRP_TERRITORIES',
687 'B',
688 'V')
689 THEN
690 jtf_rs_srp_territories_vuhk.update_rs_srp_territories_pre(
691 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
692 P_STATUS => l_status,
693 P_WH_UPDATE_DATE => l_wh_update_date,
694 P_START_DATE_ACTIVE => l_start_date_active,
695 P_END_DATE_ACTIVE => l_end_date_active,
696 X_RETURN_STATUS => x_return_status,
697 X_MSG_COUNT => x_msg_count,
698 X_MSG_DATA => x_msg_data
699 );
700
701 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
702 x_return_status := fnd_api.g_ret_sts_unexp_error;
703 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
704 fnd_msg_pub.add;
705 RAISE fnd_api.g_exc_unexpected_error;
706 END IF;
707 END IF;
708
709 --Pre Call to the Internal Type User Hook
710
711 IF jtf_usr_hks.ok_to_execute(
712 'JTF_RS_SRP_TERRITORIES_PVT',
713 'UPDATE_RS_SRP_TERRITORIES',
714 'B',
715 'I')
716 THEN
717 jtf_rs_srp_territories_iuhk.update_rs_srp_territories_pre(
718 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
719 P_STATUS => l_status,
720 P_WH_UPDATE_DATE => l_wh_update_date,
721 P_START_DATE_ACTIVE => l_start_date_active,
722 P_END_DATE_ACTIVE => l_end_date_active,
723 X_RETURN_STATUS => x_return_status,
724 X_MSG_COUNT => x_msg_count,
725 X_MSG_DATA => x_msg_data
726 );
727
728 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
729 x_return_status := fnd_api.g_ret_sts_unexp_error;
730 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
731 fnd_msg_pub.add;
732 RAISE fnd_api.g_exc_unexpected_error;
733 END IF;
734 END IF;
735
736
737 --Validate if the Salerep_Id, Territory_Id exist for the given Salesrep Territory Id. */
738 OPEN c_salesrep_territory_id(l_salesrep_territory_id);
739 FETCH c_salesrep_territory_id INTO l_salesrep_id, l_territory_id;
740 IF c_salesrep_territory_id%NOTFOUND THEN
741 --dbms_output.put_line('Salesrep Id, Territory Id do not exist for the given Salesrep Territory Id');
742 CLOSE c_salesrep_territory_id;
743 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SRP_TERR_ID');
744 fnd_message.set_token('P_SALESREP_TERRITORY_ID', l_salesrep_territory_id);
745 fnd_msg_pub.add;
746 x_return_status := fnd_api.g_ret_sts_unexp_error;
747 RAISE fnd_api.g_exc_unexpected_error;
748 END IF;
749
750 --Validate Salerep Territory for Update
751 OPEN c_salesrep_territory_update(l_salesrep_territory_id);
752 FETCH c_salesrep_territory_update INTO salesrep_territory_rec;
753 IF c_salesrep_territory_update%NOTFOUND THEN
754 CLOSE c_salesrep_territory_update;
755 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SRP_TERR_ID');
756 fnd_message.set_token('P_SALESREP_TERRITORY_ID', l_salesrep_territory_id);
757 fnd_msg_pub.add;
758 x_return_status := fnd_api.g_ret_sts_unexp_error;
759 RAISE fnd_api.g_exc_unexpected_error;
760 END IF;
761
762 --Date Validations
763 IF (p_start_date_active <>FND_API.G_MISS_DATE OR p_end_date_active <> FND_API.G_MISS_DATE) THEN
764 JTF_RESOURCE_UTL.VALIDATE_INPUT_DATES(
765 p_start_date_active => l_start_date_active,
766 p_end_date_active => l_end_date_active,
767 x_return_status => x_return_status
768 );
769 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
770 x_return_status := fnd_api.g_ret_sts_unexp_error;
771 RAISE fnd_api.g_exc_unexpected_error;
772 END IF;
773 END IF;
774 --End of Date Validations
775
776 --Get the salesrep details for the member record
777 OPEN c_salesrep_details(l_salesrep_territory_id);
778 FETCH c_salesrep_details INTO l_salesrep_start_date, l_salesrep_end_date;
779 IF c_salesrep_details%NOTFOUND THEN
780 --dbms_output.put_line('Salesrep information not found for the given Salesrep Territory Id');
781 x_return_status := fnd_api.g_ret_sts_unexp_error;
782 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SRP_TERR_ID');
783 fnd_message.set_token('P_SALESREP_TERRITORY_ID', l_salesrep_territory_id);
784 fnd_msg_pub.add;
785 CLOSE c_salesrep_details;
786 RAISE fnd_api.g_exc_unexpected_error;
787 END IF;
788
789 --Validate that the Salesrep start date is less than the passed start date active
790 IF p_start_date_active <>FND_API.G_MISS_DATE THEN
791 IF l_start_date_active < l_salesrep_start_date THEN
792 --dbms_output.put_line('Start date active cannot be less than Salesrep Start Date');
793 x_return_status := fnd_api.g_ret_sts_error;
794 fnd_message.set_name('JTF', 'JTF_RS_SRP_INVALID_START_DATE');
795 fnd_message.set_token('P_SALESREP_START_DATE', l_salesrep_start_date);
796 fnd_msg_pub.add;
797 RAISE fnd_api.g_exc_unexpected_error;
798 END IF;
799 END IF;
800
801 --Validate that the passed end date is not Null and less than Salesrep End Date
802 IF p_end_date_active <>FND_API.G_MISS_DATE THEN
803 IF l_salesrep_end_date is NOT NULL THEN
804 IF l_end_date_active is NULL THEN
805 --dbms_output.put_line ('End date active cannot be Null as Salesrep has an End date');
806 x_return_status := fnd_api.g_ret_sts_error;
807 fnd_message.set_name('JTF', 'JTF_RS_SRP_END_DATE_NULL');
808 fnd_message.set_token('P_SALESREP_END_DATE', l_salesrep_end_date);
809 fnd_msg_pub.add;
810 RAISE fnd_api.g_exc_unexpected_error;
811 ELSIF l_end_date_active > l_salesrep_end_date THEN
812 --dbms_output.put_line('End date active cannot be greater than Salesrep End Date');
813 x_return_status := fnd_api.g_ret_sts_error;
814 fnd_message.set_name('JTF', 'JTF_RS_SRP_INVALID_END_DATE');
815 fnd_message.set_token('P_SALESREP_END_DATE', l_salesrep_end_date);
816 fnd_msg_pub.add;
817 RAISE fnd_api.g_exc_unexpected_error;
818 END IF;
819 END IF;
820 END IF;
821
822 --Get the territory details for the member record
823 OPEN c_territory_details(l_salesrep_territory_id);
824 FETCH c_territory_details INTO l_territory_start_date, l_territory_end_date;
825 IF c_territory_details%NOTFOUND THEN
826 --dbms_output.put_line('Territory information not found');
827 x_return_status := fnd_api.g_ret_sts_unexp_error;
828 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SRP_TERR_ID');
829 fnd_message.set_token('P_SALESREP_TERRITORY_ID', l_salesrep_territory_id);
830 fnd_msg_pub.add;
831 CLOSE c_territory_details;
832 RAISE fnd_api.g_exc_unexpected_error;
833 END IF;
834
835 --Validate that the Territory start date is less than the passed start date active
836 IF p_start_date_active <>FND_API.G_MISS_DATE THEN
837 IF l_start_date_active < l_territory_start_date THEN
838 --dbms_output.put_line('Start date active cannot be less than Territory Start Date');
839 x_return_status := fnd_api.g_ret_sts_error;
840 fnd_message.set_name('JTF', 'JTF_RS_SRP_INVALID_START_DATE');
841 fnd_message.set_token('P_TERRITORY_START_DATE', l_territory_start_date);
842 fnd_msg_pub.add;
843 RAISE fnd_api.g_exc_unexpected_error;
844 END IF;
845 END IF;
846
847 --Validate that the passed end date is not Null and less than Territory End Date
848 IF p_start_date_active <>FND_API.G_MISS_DATE THEN
849 IF l_territory_end_date is NOT NULL THEN
850 IF l_end_date_active is NULL THEN
851 --dbms_output.put_line ('End date active cannot be Null as Territory has an End date');
852 x_return_status := fnd_api.g_ret_sts_error;
853 fnd_message.set_name('JTF', 'JTF_RS_SRP_END_DATE_NULL');
854 fnd_message.set_token('P_TERRITORY_END_DATE', l_territory_end_date);
855 fnd_msg_pub.add;
856 RAISE fnd_api.g_exc_unexpected_error;
857 ELSIF p_end_date_active > l_territory_end_date THEN
858 --dbms_output.put_line('End date active cannot be greater than Territory End Date');
859 x_return_status := fnd_api.g_ret_sts_error;
860 fnd_message.set_name('JTF', 'JTF_RS_SRP_INVALID_END_DATE');
861 fnd_message.set_token('P_TERRITORY_END_DATE', l_territory_end_date);
862 fnd_msg_pub.add;
863 RAISE fnd_api.g_exc_unexpected_error;
864 END IF;
865 END IF;
866 END IF;
867
868 --Lock the row in the table by calling the table handler
869 jtf_rs_srp_territories_pkg.lock_row(
870 X_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
871 X_OBJECT_VERSION_NUMBER => l_object_version_number
872 );
873
874 --Update the Object Version Number by Incrementing It
875 l_object_version_number := p_object_version_number+1;
876
877
878 BEGIN
879 --Update the row in the table by calling the table handler
880 jtf_rs_srp_territories_pkg.update_row(
881 X_SALESREP_TERRITORY_ID => p_salesrep_territory_id,
882 X_SALESREP_ID => l_salesrep_id,
883 X_TERRITORY_ID => l_territory_id,
884 X_STATUS => salesrep_territory_rec.l_status,
885 X_START_DATE_ACTIVE => salesrep_territory_rec.l_start_date_active,
886 X_END_DATE_ACTIVE => salesrep_territory_rec.l_end_date_active,
887 X_WH_UPDATE_DATE => salesrep_territory_rec.l_wh_update_date,
888 X_OBJECT_VERSION_NUMBER => l_object_version_number,
889 X_ATTRIBUTE_CATEGORY => salesrep_territory_rec.l_attribute_category,
890 X_ATTRIBUTE2 => salesrep_territory_rec.l_attribute2,
891 X_ATTRIBUTE3 => salesrep_territory_rec.l_attribute3,
892 X_ATTRIBUTE4 => salesrep_territory_rec.l_attribute4,
893 X_ATTRIBUTE5 => salesrep_territory_rec.l_attribute5,
894 X_ATTRIBUTE6 => salesrep_territory_rec.l_attribute6,
895 X_ATTRIBUTE7 => salesrep_territory_rec.l_attribute7,
896 X_ATTRIBUTE8 => salesrep_territory_rec.l_attribute8,
897 X_ATTRIBUTE9 => salesrep_territory_rec.l_attribute9,
898 X_ATTRIBUTE10 => salesrep_territory_rec.l_attribute10,
899 X_ATTRIBUTE11 => salesrep_territory_rec.l_attribute11,
900 X_ATTRIBUTE12 => salesrep_territory_rec.l_attribute12,
901 X_ATTRIBUTE13 => salesrep_territory_rec.l_attribute13,
902 X_ATTRIBUTE14 => salesrep_territory_rec.l_attribute14,
903 X_ATTRIBUTE15 => salesrep_territory_rec.l_attribute15,
904 X_ATTRIBUTE1 => salesrep_territory_rec.l_attribute1,
905 X_LAST_UPDATE_DATE => sysdate,
906 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
907 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
908 );
909
910 p_object_version_number := l_object_version_number;
911
912 EXCEPTION
913 WHEN NO_DATA_FOUND THEN
914 --dbms_output.put_line('Error in Table Handler');
915 CLOSE c_salesrep_territory_update;
916 x_return_status := fnd_api.g_ret_sts_unexp_error;
917 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
918 fnd_msg_pub.add;
919 RAISE fnd_api.g_exc_unexpected_error;
920 END;
921 --dbms_output.put_line('Salesrep Territory Successfully Updated');
922
923 --Close the cursors
924 --CLOSE c_salesrep_territory_update;
925 --CLOSE c_salesrep_details;
926 --CLOSE c_salesrep_details;
927 --CLOSE c_salesrep_territory_id;
928
929 --Make the post processing call to the user hooks
930
931 --Post Call to the Customer Type User Hook
932
933 IF jtf_usr_hks.ok_to_execute(
934 'JTF_RS_SRP_TERRITORIES_PVT',
935 'UPDATE_RS_SRP_TERRITORIES',
936 'A',
937 'C')
938 THEN
939 jtf_rs_srp_territories_cuhk.update_rs_srp_territories_post(
940 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
941 P_STATUS => l_status,
942 P_WH_UPDATE_DATE => l_wh_update_date,
943 P_START_DATE_ACTIVE => l_start_date_active,
944 P_END_DATE_ACTIVE => l_end_date_active,
945 X_RETURN_STATUS => x_return_status,
946 X_MSG_COUNT => x_msg_count,
947 X_MSG_DATA => x_msg_data
948 );
949
950 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
951 x_return_status := fnd_api.g_ret_sts_unexp_error;
952 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
953 fnd_msg_pub.add;
954 RAISE fnd_api.g_exc_unexpected_error;
955 END IF;
956 END IF;
957
958 --Post Call to the Vertical Type User Hook
959
960 IF jtf_usr_hks.ok_to_execute(
961 'JTF_RS_SRP_TERRITORIES_PVT',
962 'UPDATE_RS_SRP_TERRITORIES',
963 'A',
964 'V')
965 THEN
966 jtf_rs_srp_territories_vuhk.update_rs_srp_territories_post(
967 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
968 P_STATUS => l_status,
969 P_WH_UPDATE_DATE => l_wh_update_date,
970 P_START_DATE_ACTIVE => l_start_date_active,
971 P_END_DATE_ACTIVE => l_end_date_active,
972 X_RETURN_STATUS => x_return_status,
973 X_MSG_COUNT => x_msg_count,
974 X_MSG_DATA => x_msg_data
975 );
976
977 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
978 x_return_status := fnd_api.g_ret_sts_unexp_error;
979 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
980 fnd_msg_pub.add;
981 RAISE fnd_api.g_exc_unexpected_error;
982 END IF;
983 END IF;
984
985 --Post Call to the Internal Type User Hook
986
987 IF jtf_usr_hks.ok_to_execute(
988 'JTF_RS_SRP_TERRITORIES_PVT',
989 'UPDATE_RS_SRP_TERRITORIES',
990 'A',
991 'I')
992 THEN
993 jtf_rs_srp_territories_iuhk.update_rs_srp_territories_post(
994 P_SALESREP_TERRITORY_ID => l_salesrep_territory_id,
995 P_STATUS => l_status,
996 P_WH_UPDATE_DATE => l_wh_update_date,
997 P_START_DATE_ACTIVE => l_start_date_active,
998 P_END_DATE_ACTIVE => l_end_date_active,
999 X_RETURN_STATUS => x_return_status,
1000 X_MSG_COUNT => x_msg_count,
1001 X_MSG_DATA => x_msg_data
1002 );
1003
1004 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1005 x_return_status := fnd_api.g_ret_sts_unexp_error;
1006 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1007 fnd_msg_pub.add;
1008 RAISE fnd_api.g_exc_unexpected_error;
1009 END IF;
1010 END IF;
1011
1012 /* Standard call for Message Generation */
1013
1014 IF jtf_usr_hks.ok_to_execute(
1015 'JTF_RS_SRP_TERRITORIES_PVT',
1016 'UPDATE_RS_SRP_TERRITORIES',
1017 'M',
1018 'M')
1019 THEN
1020 IF (jtf_rs_srp_territories_cuhk.ok_to_generate_msg(
1021 p_salesrep_territory_id => l_salesrep_territory_id,
1022 x_return_status => x_return_status) )
1023 THEN
1024
1025 /* Get the bind data id for the Business Object Instance */
1026 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1027 /* Set bind values for the bind variables in the Business Object SQL */
1028 jtf_usr_hks.load_bind_data(l_bind_data_id, 'salesrep_territory_id', p_salesrep_territory_id, 'S', 'N');
1029
1030 /* Call the message generation API */
1031 jtf_usr_hks.generate_message(
1032 p_prod_code => 'JTF',
1033 p_bus_obj_code => 'RS_SRT',
1034 p_action_code => 'U',
1035 p_bind_data_id => l_bind_data_id,
1036 x_return_code => x_return_status);
1037
1038 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1039 --dbms_output.put_line('Returned Error status from the Message Generation API');
1040 x_return_status := fnd_api.g_ret_sts_unexp_error;
1041 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1042 fnd_msg_pub.add;
1043 RAISE fnd_api.g_exc_unexpected_error;
1044 END IF;
1045 END IF;
1046 END IF;
1047
1048
1049 IF fnd_api.to_boolean(p_commit) THEN
1050 COMMIT WORK;
1051 END IF;
1052 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1053
1054 EXCEPTION
1055 WHEN fnd_api.g_exc_unexpected_error THEN
1056 --DBMS_OUTPUT.put_line (' ========================================== ');
1057 --DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error =============== ');
1058 ROLLBACK TO update_rs_srp_territories_pvt;
1059 x_return_status := fnd_api.g_ret_sts_unexp_error;
1060 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1061 WHEN OTHERS THEN
1062 --DBMS_OUTPUT.put_line (' ========================================== ');
1063 --DBMS_OUTPUT.put_line (' =========== Raised Others in Update Salesrep Territories Pvt ============= ');
1064 --DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
1065 ROLLBACK TO update_rs_srp_territories_pvt;
1066 x_return_status := fnd_api.g_ret_sts_unexp_error;
1067 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1068
1069 END update_rs_srp_territories;
1070
1071 END jtf_rs_srp_territories_pvt;