[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SALESREPS_PVT
Source
1 PACKAGE BODY JTF_RS_SALESREPS_PVT AS
2 /* $Header: jtfrsvsb.pls 120.1 2005/06/07 23:03:26 baianand ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_RS_SALESREPS_PVT';
6
7 /* Procedure to create salesreps */
8
9 PROCEDURE Create_salesrep(
10 P_API_VERSION IN NUMBER,
11 P_INIT_MSG_LIST IN VARCHAR2,
12 P_COMMIT IN VARCHAR2,
13 P_RESOURCE_ID IN JTF_RS_SALESREPS.RESOURCE_ID%TYPE,
14 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
15 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE,
16 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE,
17 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE,
18 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE,
19 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE,
20 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE,
21 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE,
22 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE,
23 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE,
24 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE,
25 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE,
26 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE,
27 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE,
28 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE,
29 P_ATTRIBUTE_CATEGORY IN JTF_RS_SALESREPS.ATTRIBUTE_CATEGORY%TYPE,
30 P_ATTRIBUTE1 IN JTF_RS_SALESREPS.ATTRIBUTE1%TYPE,
31 P_ATTRIBUTE2 IN JTF_RS_SALESREPS.ATTRIBUTE2%TYPE,
32 P_ATTRIBUTE3 IN JTF_RS_SALESREPS.ATTRIBUTE3%TYPE,
33 P_ATTRIBUTE4 IN JTF_RS_SALESREPS.ATTRIBUTE4%TYPE,
34 P_ATTRIBUTE5 IN JTF_RS_SALESREPS.ATTRIBUTE5%TYPE,
35 P_ATTRIBUTE6 IN JTF_RS_SALESREPS.ATTRIBUTE6%TYPE,
36 P_ATTRIBUTE7 IN JTF_RS_SALESREPS.ATTRIBUTE7%TYPE,
37 P_ATTRIBUTE8 IN JTF_RS_SALESREPS.ATTRIBUTE8%TYPE,
38 P_ATTRIBUTE9 IN JTF_RS_SALESREPS.ATTRIBUTE9%TYPE,
39 P_ATTRIBUTE10 IN JTF_RS_SALESREPS.ATTRIBUTE10%TYPE,
40 P_ATTRIBUTE11 IN JTF_RS_SALESREPS.ATTRIBUTE11%TYPE,
41 P_ATTRIBUTE12 IN JTF_RS_SALESREPS.ATTRIBUTE12%TYPE,
42 P_ATTRIBUTE13 IN JTF_RS_SALESREPS.ATTRIBUTE13%TYPE,
43 P_ATTRIBUTE14 IN JTF_RS_SALESREPS.ATTRIBUTE14%TYPE,
44 P_ATTRIBUTE15 IN JTF_RS_SALESREPS.ATTRIBUTE15%TYPE,
45 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
46 X_MSG_COUNT OUT NOCOPY NUMBER,
47 X_MSG_DATA OUT NOCOPY VARCHAR2,
48 X_SALESREP_ID OUT NOCOPY JTF_RS_SALESREPS.SALESREP_ID%TYPE
49 )
50 IS
51 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SALESREP';
52 l_api_version CONSTANT NUMBER := 1.0;
53 l_rowid ROWID;
54 l_resource_id jtf_rs_salesreps.resource_id%type := p_resource_id;
55 l_sales_credit_type_id jtf_rs_salesreps.sales_credit_type_id%type := p_sales_credit_type_id;
56 l_name jtf_rs_salesreps.name%type := p_name;
57 l_status jtf_rs_salesreps.status%type := p_status;
58 l_start_date_active jtf_rs_salesreps.start_date_active%type := p_start_date_active;
59 l_end_date_active jtf_rs_salesreps.end_date_active%type := p_end_date_active;
60 l_gl_id_rev jtf_rs_salesreps.gl_id_rev%type := p_gl_id_rev;
61 l_gl_id_freight jtf_rs_salesreps.gl_id_freight%type := p_gl_id_freight;
62 l_gl_id_rec jtf_rs_salesreps.gl_id_rec%type := p_gl_id_rec;
63 l_set_of_books_id jtf_rs_salesreps.set_of_books_id%type := p_set_of_books_id;
64 l_salesrep_number jtf_rs_salesreps.salesrep_number%type := p_salesrep_number;
65 l_email_address jtf_rs_salesreps.email_address%type := p_email_address;
66 l_wh_update_date jtf_rs_salesreps.wh_update_date%type := p_wh_update_date;
67 l_sales_tax_geocode jtf_rs_salesreps.sales_tax_geocode%type := p_sales_tax_geocode;
68 l_sales_tax_inside_city_limits jtf_rs_salesreps.sales_tax_inside_city_limits%type := p_sales_tax_inside_city_limits;
69 l_category jtf_rs_resource_extns.category%type;
70 l_person_id jtf_rs_salesreps.person_id%type;
71 l_salesrep_id jtf_rs_salesreps.salesrep_id%type;
72 l_org_id jtf_rs_salesreps.org_id%type := p_org_id;
73 l_msg_data VARCHAR2(2000);
74 l_msg_count NUMBER;
75 l_check_char VARCHAR2(1);
76 l_check_dup_id VARCHAR2(1);
77 l_bind_data_id NUMBER;
78
79 CURSOR c_category(l_resource_id jtf_rs_resource_extns.resource_id%type) is
80 SELECT category,source_id
81 FROM jtf_rs_resource_extns
82 WHERE resource_id = l_resource_id;
83
84 CURSOR c_jtf_rs_salesreps(l_rowid IN ROWID) IS
85 SELECT 'Y'
86 FROM jtf_rs_salesreps
87 WHERE rowid = l_rowid;
88
89 CURSOR c_dup_salesrep_id (l_salesrep_id IN jtf_rs_salesreps.salesrep_id%type,
90 l_org_id IN jtf_rs_salesreps.org_id%type )
91 IS
92 SELECT 'X'
93 FROM jtf_rs_salesreps
94 WHERE salesrep_id = l_salesrep_id
95 AND nvl(org_id,-99) = nvl(l_org_id,-99);
96
97 CURSOR c_dup_resource(l_resource_id jtf_rs_resource_extns.resource_id%type,
98 l_org_id jtf_rs_salesreps.org_id%type) IS
99 SELECT 'Y'
100 FROM jtf_rs_salesreps
101 WHERE resource_id = l_resource_id
102 AND nvl(org_id,-99) = nvl(l_org_id,-99);
103
104 resource_exists varchar2(1);
105
106 BEGIN
107
108 -- dbms_output.put_line ('Inside the Create Salesrep PVT API');
109
110 SAVEPOINT create_salesrep_pvt;
111
112 x_return_status := fnd_api.g_ret_sts_success;
113
114 /* Standard call to check for call compatibility. */
115 IF NOT fnd_api.Compatible_api_call( l_api_version,
116 p_api_version,
117 l_api_name,
118 G_PKG_NAME)
119 THEN
120 RAISE fnd_api.g_exc_unexpected_error;
121 END IF;
122
123 IF fnd_api.to_Boolean(p_init_msg_list) THEN
124 fnd_msg_pub.initialize;
125 END IF;
126
127 /* Make the pre processing call to the user hooks */
128
129 /* Pre Call to the Customer Type User Hook */
130
131 IF jtf_usr_hks.ok_to_execute(
132 'JTF_RS_SALESREPS_PVT',
133 'CREATE_SALESREP',
134 'B',
135 'C')
136 THEN
137 jtf_rs_salesreps_cuhk.create_salesrep_pre(
138 p_resource_id => l_resource_id,
139 p_sales_credit_type_id => l_sales_credit_type_id,
140 p_name => l_name,
141 p_status => l_status,
142 p_start_date_active => l_start_date_active,
143 p_end_date_active => l_end_date_active,
144 p_gl_id_rev => l_gl_id_rev,
145 p_gl_id_freight => l_gl_id_freight,
146 p_gl_id_rec => l_gl_id_rec,
147 p_set_of_books_id => l_set_of_books_id,
148 p_salesrep_number => l_salesrep_number,
149 p_email_address => l_email_address,
150 p_wh_update_date => l_wh_update_date,
151 p_sales_tax_geocode => l_sales_tax_geocode,
152 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
153 x_return_status => x_return_status);
154
155 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
156
157 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
158 fnd_msg_pub.add;
159
160 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
161 RAISE FND_API.G_EXC_ERROR;
162 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
164 END IF;
165
166
167 END IF;
168 END IF;
169
170 /* Pre Call to the Vertical Type User Hook */
171
172 IF jtf_usr_hks.ok_to_execute(
173 'JTF_RS_SALESREPS_PVT',
174 'CREATE_SALESREP',
175 'B',
176 'V')
177 THEN
178
179 jtf_rs_salesreps_vuhk.create_salesrep_pre(
180 p_resource_id => l_resource_id,
181 p_sales_credit_type_id => l_sales_credit_type_id,
182 p_name => l_name,
183 p_status => l_status,
184 p_start_date_active => l_start_date_active,
185 p_end_date_active => l_end_date_active,
186 p_gl_id_rev => l_gl_id_rev,
187 p_gl_id_freight => l_gl_id_freight,
188 p_gl_id_rec => l_gl_id_rec,
189 p_set_of_books_id => l_set_of_books_id,
190 p_salesrep_number => l_salesrep_number,
191 p_email_address => l_email_address,
192 p_wh_update_date => l_wh_update_date,
193 p_sales_tax_geocode => l_sales_tax_geocode,
194 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
195 x_return_status => x_return_status);
196
197 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
198
199 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
200 fnd_msg_pub.add;
201
202 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
203 RAISE FND_API.G_EXC_ERROR;
204 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 END IF;
207
208
209 END IF;
210 END IF;
211
212
213 /* Pre Call to the Internal Type User Hook */
214
215 IF jtf_usr_hks.ok_to_execute(
216 'JTF_RS_SALESREPS_PVT',
217 'CREATE_SALESREP',
218 'B',
219 'I')
220 THEN
221
222 jtf_rs_salesreps_iuhk.create_salesrep_pre(
223 p_resource_id => l_resource_id,
224 p_sales_credit_type_id => l_sales_credit_type_id,
225 p_name => l_name,
226 p_status => l_status,
227 p_start_date_active => l_start_date_active,
228 p_end_date_active => l_end_date_active,
229 p_gl_id_rev => l_gl_id_rev,
230 p_gl_id_freight => l_gl_id_freight,
231 p_gl_id_rec => l_gl_id_rec,
232 p_set_of_books_id => l_set_of_books_id,
233 p_salesrep_number => l_salesrep_number,
234 p_email_address => l_email_address,
235 p_wh_update_date => l_wh_update_date,
236 p_sales_tax_geocode => l_sales_tax_geocode,
237 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
238 x_return_status => x_return_status);
239
240 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
241
242 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
243 fnd_msg_pub.add;
244
245 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
246 RAISE FND_API.G_EXC_ERROR;
247 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
249 END IF;
250
251
252 END IF;
253 END IF;
254
255
256 /* Starting API body */
257
258 jtf_resource_utl.validate_input_dates(
259 l_start_date_active
260 ,l_end_date_active
261 ,x_return_status);
262
263 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
264
265 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
266 RAISE FND_API.G_EXC_ERROR;
267 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
269 END IF;
270
271 END IF;
272
273 OPEN c_category(l_resource_id);
274 FETCH c_category INTO l_category,l_person_id;
275 IF c_category%NOTFOUND THEN
276 CLOSE c_category;
277 fnd_message.set_name('JTF','JTF_RS_INVALID_RESOURCE');
278 fnd_message.set_token('P_RESOURCE_ID',l_resource_id);
279 fnd_msg_pub.add;
280 RAISE fnd_api.g_exc_error;
281 END IF;
282 CLOSE c_category;
283
284 -- select to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' '
285 -- ,null,substrb(userenv('CLIENT_INFO'),1,10)))
286 -- into l_org_id
287 -- from dual;
288
289 -- dbms_output.put_line('Org id before validation : '|| l_org_id);
290 l_org_id := MO_GLOBAL.get_valid_org(p_org_id);
291 IF l_org_id is NULL THEN
292 -- dbms_output.put_line('Org id is Null');
293 x_return_status := FND_API.G_RET_STS_ERROR;
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296 -- dbms_output.put_line('Org id is : '||l_org_id);
297
298 IF MO_UTILS.Get_Multi_Org_Flag = 'Y' and l_org_id is NULL THEN
299 fnd_message.set_name('JTF','JTF_RS_ORG_CONTEXT_NOT_SET');
300 fnd_msg_pub.add;
301 RAISE fnd_api.g_exc_error;
302 END IF;
303
304 -- l_org_id := fnd_profile.value('ORG_ID');
305
306 /** IF (l_category = 'OTHER') OR (l_category = 'TBH') OR (l_category = 'EMPLOYEE') OR (l_category = 'PARTY') OR (l_category = 'PARTNER') OR (l_category = 'SUPPLIER_CONTACT') THEN **/
307 jtf_resource_utl.validate_salesrep_number(l_salesrep_number,
308 l_org_id,
309 x_return_status);
310 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
311 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
312 RAISE FND_API.G_EXC_ERROR;
313 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 END IF;
316 END IF;
317 -- END IF;
318
319 OPEN c_dup_resource(l_resource_id, l_org_id);
320 FETCH c_dup_resource into resource_exists;
321 IF c_dup_resource%FOUND THEN
322 CLOSE c_dup_resource;
323 fnd_message.set_name('JTF','JTF_RS_DUP_RES_SALESPERSON');
324 fnd_msg_pub.add;
325 RAISE fnd_api.g_exc_error;
326 END IF;
327 CLOSE c_dup_resource;
328
329 /* This portion of the code was modified to accomodate the calls to Migration API */
330 /* Check if the Global Variable Flag for Salesrep ID is Y or N */
331
332 -- dbms_output.put_line ('Before checkin the Global flag in PVT API');
333
334 IF G_SRP_ID_PVT_FLAG = 'Y' THEN
335
336 /* Get the next value of the Salesrep_id from the sequence. */
337
338 LOOP
339 SELECT jtf_rs_salesreps_s.nextval
340 INTO l_salesrep_id
341 FROM dual;
342 --dbms_output.put_line ('After Select - Salesrep ID ' || l_salesrep_id);
343
344 OPEN c_dup_salesrep_id (l_salesrep_id, l_org_id);
345 FETCH c_dup_salesrep_id INTO l_check_dup_id;
346 EXIT WHEN c_dup_salesrep_id%NOTFOUND;
347 CLOSE c_dup_salesrep_id;
348 END LOOP;
349 CLOSE c_dup_salesrep_id;
350
351 ELSE
352 l_salesrep_id := JTF_RS_SALESREPS_PUB.G_SALESREP_ID;
353 l_org_id := JTF_RS_SALESREPS_PUB.G_ORG_ID;
354
355 END IF;
356
357 /* Calling table handler to insert salesrep */
358 jtf_rs_salesreps_pkg.insert_row(
359 X_ROWID => l_rowid,
360 X_SALESREP_ID => l_salesrep_id,
361 X_RESOURCE_ID => l_resource_id,
362 X_SALES_CREDIT_TYPE_ID => l_sales_credit_type_id,
363 X_NAME => l_name,
364 X_STATUS => l_status,
365 X_START_DATE_ACTIVE => l_start_date_active,
366 X_END_DATE_ACTIVE => l_end_date_active,
367 X_ORG_ID => l_org_id,
368 X_GL_ID_REV => l_gl_id_rev,
369 X_GL_ID_FREIGHT => l_gl_id_freight,
370 X_GL_ID_REC => l_gl_id_rec,
371 X_SET_OF_BOOKS_ID => l_set_of_books_id,
372 X_SALESREP_NUMBER => l_salesrep_number,
373 X_EMAIL_ADDRESS => l_email_address,
374 X_WH_UPDATE_DATE => l_wh_update_date,
375 X_PERSON_ID => l_person_id,
376 X_SALES_TAX_GEOCODE => l_sales_tax_geocode,
377 X_SALES_TAX_INSIDE_CITY_LIMITS => l_sales_tax_inside_city_limits,
378 X_ATTRIBUTE_CATEGORY => p_attribute_category,
379 X_ATTRIBUTE2 => p_attribute2,
380 X_ATTRIBUTE3 => p_attribute3,
381 X_ATTRIBUTE4 => p_attribute4,
382 X_ATTRIBUTE5 => p_attribute5,
383 X_ATTRIBUTE6 => p_attribute6,
384 X_ATTRIBUTE7 => p_attribute7,
385 X_ATTRIBUTE8 => p_attribute8,
386 X_ATTRIBUTE9 => p_attribute9,
387 X_ATTRIBUTE10 => p_attribute10,
388 X_ATTRIBUTE11 => p_attribute11,
389 X_ATTRIBUTE12 => p_attribute12,
390 X_ATTRIBUTE13 => p_attribute13,
391 X_ATTRIBUTE14 => p_attribute14,
392 X_ATTRIBUTE15 => p_attribute15,
393 X_ATTRIBUTE1 => p_attribute1,
394 X_CREATION_DATE => SYSDATE,
395 X_CREATED_BY => jtf_resource_utl.created_by,
396 X_LAST_UPDATE_DATE => SYSDATE,
397 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
398 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
399 );
400
401 OPEN c_jtf_rs_salesreps(l_rowid);
402 FETCH c_jtf_rs_salesreps INTO l_check_char;
403 IF c_jtf_rs_salesreps%NOTFOUND THEN
404
405 CLOSE c_jtf_rs_salesreps;
406
407 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
408 fnd_msg_pub.add;
409
410 RAISE fnd_api.g_exc_error;
411
412 ELSE
413 x_salesrep_id := l_salesrep_id;
414 CLOSE c_jtf_rs_salesreps;
415 END IF;
416
417 /* Make the post processing call to the user hooks */
418
419 /* Post Call to the Customer Type User Hook */
420
421 IF jtf_usr_hks.ok_to_execute(
422 'JTF_RS_SALESREPS_PVT',
423 'CREATE_SALESREP',
424 'A',
425 'C')
426 THEN
427 jtf_rs_salesreps_cuhk.create_salesrep_post(
428 p_salesrep_id => l_salesrep_id,
429 p_resource_id => l_resource_id,
430 p_sales_credit_type_id => l_sales_credit_type_id,
431 p_name => l_name,
432 p_status => l_status,
433 p_start_date_active => l_start_date_active,
434 p_end_date_active => l_end_date_active,
435 p_gl_id_rev => l_gl_id_rev,
436 p_gl_id_freight => l_gl_id_freight,
437 p_gl_id_rec => l_gl_id_rec,
438 p_set_of_books_id => l_set_of_books_id,
439 p_salesrep_number => l_salesrep_number,
440 p_email_address => l_email_address,
441 p_wh_update_date => l_wh_update_date,
442 p_sales_tax_geocode => l_sales_tax_geocode,
443 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
444 x_return_status => x_return_status);
445
446 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
447
448 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
449 fnd_msg_pub.add;
450
451 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
452 RAISE FND_API.G_EXC_ERROR;
453 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 END IF;
456
457 END IF;
458 END IF;
459
460 /* Post Call to the Vertical Type User Hook */
461
462 IF jtf_usr_hks.ok_to_execute(
463 'JTF_RS_SALESREPS_PVT',
464 'CREATE_SALESREP',
465 'A',
466 'V')
467 THEN
468 jtf_rs_salesreps_vuhk.create_salesrep_post(
469 p_salesrep_id => l_salesrep_id,
470 p_resource_id => l_resource_id,
471 p_sales_credit_type_id => l_sales_credit_type_id,
472 p_name => l_name,
473 p_status => l_status,
474 p_start_date_active => l_start_date_active,
475 p_end_date_active => l_end_date_active,
476 p_gl_id_rev => l_gl_id_rev,
477 p_gl_id_freight => l_gl_id_freight,
478 p_gl_id_rec => l_gl_id_rec,
479 p_set_of_books_id => l_set_of_books_id,
480 p_salesrep_number => l_salesrep_number,
481 p_email_address => l_email_address,
482 p_wh_update_date => l_wh_update_date,
483 p_sales_tax_geocode => l_sales_tax_geocode,
484 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
485 x_return_status => x_return_status);
486
487 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
488
489 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
490 fnd_msg_pub.add;
491
492 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
493 RAISE FND_API.G_EXC_ERROR;
494 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 END IF;
497
498 END IF;
499 END IF;
500
501 /* Post Call to the Internal Type User Hook */
502
503 IF jtf_usr_hks.ok_to_execute(
504 'JTF_RS_SALESREPS_PVT',
505 'CREATE_SALESREP',
506 'A',
507 'I')
508 THEN
509 jtf_rs_salesreps_iuhk.create_salesrep_post(
510 p_salesrep_id => l_salesrep_id,
511 p_resource_id => l_resource_id,
512 p_sales_credit_type_id => l_sales_credit_type_id,
513 p_name => l_name,
514 p_status => l_status,
515 p_start_date_active => l_start_date_active,
516 p_end_date_active => l_end_date_active,
517 p_gl_id_rev => l_gl_id_rev,
518 p_gl_id_freight => l_gl_id_freight,
519 p_gl_id_rec => l_gl_id_rec,
520 p_set_of_books_id => l_set_of_books_id,
521 p_salesrep_number => l_salesrep_number,
522 p_email_address => l_email_address,
523 p_wh_update_date => l_wh_update_date,
524 p_sales_tax_geocode => l_sales_tax_geocode,
525 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
526 x_return_status => x_return_status);
527
528 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
529
530 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
531 fnd_msg_pub.add;
532
533 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
534 RAISE FND_API.G_EXC_ERROR;
535 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537 END IF;
538
539 END IF;
540 END IF;
541
542
543 /* Standard call for Message Generation */
544
545 IF jtf_usr_hks.ok_to_execute(
546 'JTF_RS_SALESREPS_PVT',
547 'CREATE_SALESREP',
548 'M',
549 'M')
550 THEN
551
552 IF (jtf_rs_salesreps_cuhk.ok_to_generate_msg(
553 p_salesrep_id => l_salesrep_id,
554 x_return_status => x_return_status) )
555 THEN
556
557 /* Get the bind data id for the Business Object Instance */
558
559 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
560
561
562 /* Set bind values for the bind variables in the Business Object SQL */
563
564 jtf_usr_hks.load_bind_data(l_bind_data_id, 'salesrep_id', l_salesrep_id, 'S', 'N');
565
566
567 /* Call the message generation API */
568
569 jtf_usr_hks.generate_message(
570 p_prod_code => 'JTF',
571 p_bus_obj_code => 'RS_SRP',
572 p_action_code => 'I',
573 p_bind_data_id => l_bind_data_id,
574 x_return_code => x_return_status);
575
576
577 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
578
579
580 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
581 fnd_msg_pub.add;
582
583 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
584 RAISE FND_API.G_EXC_ERROR;
585 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587 END IF;
588
589
590 END IF;
591
592 END IF;
593
594 END IF;
595
596 IF fnd_api.to_Boolean(p_commit) THEN
597 COMMIT WORK;
598 END IF;
599
600 /* Standard call to get message count and if count is 1, get message info. */
601 FND_MSG_PUB.Count_And_Get
602 (p_count => x_msg_count,
603 p_data => x_msg_data
604 );
605
606 EXCEPTION
607
608 WHEN fnd_api.g_exc_error THEN
609 ROLLBACK TO create_salesrep_pvt;
610 x_return_status := fnd_api.g_ret_sts_error;
611 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
612 p_data => x_msg_data);
613 WHEN fnd_api.g_exc_unexpected_error THEN
614 ROLLBACK TO create_salesrep_pvt;
615 x_return_status := fnd_api.g_ret_sts_unexp_error;
616 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
617 p_data => x_msg_data);
618 WHEN OTHERS THEN
619 ROLLBACK TO create_salesrep_pvt;
620 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
621 fnd_message.set_token('P_SQLCODE',SQLCODE);
622 fnd_message.set_token('P_SQLERRM',SQLERRM);
623 fnd_message.set_token('P_API_NAME', l_api_name);
624 FND_MSG_PUB.add;
625 x_return_status := fnd_api.g_ret_sts_unexp_error;
626 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
627 p_data => x_msg_data);
628
629 End Create_salesrep;
630
631 PROCEDURE create_salesrep_migrate(
632 P_API_VERSION IN NUMBER,
633 P_INIT_MSG_LIST IN VARCHAR2,
634 P_COMMIT IN VARCHAR2,
635 P_RESOURCE_ID IN JTF_RS_SALESREPS.RESOURCE_ID%TYPE,
636 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
637 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE,
638 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE,
639 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE,
640 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE,
641 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE,
642 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE,
643 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE,
644 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE,
645 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE,
646 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE,
647 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE,
648 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE,
649 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE,
650 P_ATTRIBUTE_CATEGORY IN JTF_RS_SALESREPS.ATTRIBUTE_CATEGORY%TYPE,
651 P_ATTRIBUTE1 IN JTF_RS_SALESREPS.ATTRIBUTE1%TYPE,
652 P_ATTRIBUTE2 IN JTF_RS_SALESREPS.ATTRIBUTE2%TYPE,
653 P_ATTRIBUTE3 IN JTF_RS_SALESREPS.ATTRIBUTE3%TYPE,
654 P_ATTRIBUTE4 IN JTF_RS_SALESREPS.ATTRIBUTE4%TYPE,
655 P_ATTRIBUTE5 IN JTF_RS_SALESREPS.ATTRIBUTE5%TYPE,
656 P_ATTRIBUTE6 IN JTF_RS_SALESREPS.ATTRIBUTE6%TYPE,
657 P_ATTRIBUTE7 IN JTF_RS_SALESREPS.ATTRIBUTE7%TYPE,
658 P_ATTRIBUTE8 IN JTF_RS_SALESREPS.ATTRIBUTE8%TYPE,
659 P_ATTRIBUTE9 IN JTF_RS_SALESREPS.ATTRIBUTE9%TYPE,
660 P_ATTRIBUTE10 IN JTF_RS_SALESREPS.ATTRIBUTE10%TYPE,
661 P_ATTRIBUTE11 IN JTF_RS_SALESREPS.ATTRIBUTE11%TYPE,
662 P_ATTRIBUTE12 IN JTF_RS_SALESREPS.ATTRIBUTE12%TYPE,
663 P_ATTRIBUTE13 IN JTF_RS_SALESREPS.ATTRIBUTE13%TYPE,
664 P_ATTRIBUTE14 IN JTF_RS_SALESREPS.ATTRIBUTE14%TYPE,
665 P_ATTRIBUTE15 IN JTF_RS_SALESREPS.ATTRIBUTE15%TYPE,
666 P_SALESREP_ID IN JTF_RS_SALESREPS.SALESREP_ID%TYPE,
667 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE,
668 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
669 X_MSG_COUNT OUT NOCOPY NUMBER,
670 X_MSG_DATA OUT NOCOPY VARCHAR2,
671 X_SALESREP_ID OUT NOCOPY JTF_RS_SALESREPS.SALESREP_ID%TYPE
672 ) IS
673
674 BEGIN
675
676 G_SRP_ID_PVT_FLAG := 'N';
677
678 jtf_rs_salesreps_pvt.create_salesrep (
679 P_API_VERSION => P_API_VERSION,
680 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
681 P_COMMIT => P_COMMIT,
682 P_RESOURCE_ID => P_RESOURCE_ID,
683 P_SALES_CREDIT_TYPE_ID => P_SALES_CREDIT_TYPE_ID,
684 P_NAME => P_NAME,
685 P_STATUS => P_STATUS,
686 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
687 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
688 P_GL_ID_REV => P_GL_ID_REV,
689 P_GL_ID_FREIGHT => P_GL_ID_FREIGHT,
690 P_GL_ID_REC => P_GL_ID_REC,
691 P_SET_OF_BOOKS_ID => P_SET_OF_BOOKS_ID,
692 P_SALESREP_NUMBER => P_SALESREP_NUMBER,
693 P_EMAIL_ADDRESS => P_EMAIL_ADDRESS,
694 P_WH_UPDATE_DATE => P_WH_UPDATE_DATE,
695 P_SALES_TAX_GEOCODE => P_SALES_TAX_GEOCODE,
696 P_SALES_TAX_INSIDE_CITY_LIMITS => P_SALES_TAX_INSIDE_CITY_LIMITS ,
697 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY ,
698 P_ATTRIBUTE1 => P_ATTRIBUTE1,
699 P_ATTRIBUTE2 => P_ATTRIBUTE2,
700 P_ATTRIBUTE3 => P_ATTRIBUTE3,
701 P_ATTRIBUTE4 => P_ATTRIBUTE4,
702 P_ATTRIBUTE5 => P_ATTRIBUTE5,
703 P_ATTRIBUTE6 => P_ATTRIBUTE6,
704 P_ATTRIBUTE7 => P_ATTRIBUTE7,
705 P_ATTRIBUTE8 => P_ATTRIBUTE8,
706 P_ATTRIBUTE9 => P_ATTRIBUTE9,
707 P_ATTRIBUTE10 => P_ATTRIBUTE10,
708 P_ATTRIBUTE11 => P_ATTRIBUTE11,
709 P_ATTRIBUTE12 => P_ATTRIBUTE12,
710 P_ATTRIBUTE13 => P_ATTRIBUTE13,
711 P_ATTRIBUTE14 => P_ATTRIBUTE14,
712 P_ATTRIBUTE15 => P_ATTRIBUTE15,
713 X_RETURN_STATUS => X_RETURN_STATUS,
714 X_MSG_COUNT => X_MSG_COUNT,
715 X_MSG_DATA => X_MSG_DATA,
716 X_SALESREP_ID => X_SALESREP_ID
717 );
718
719 END create_salesrep_migrate;
720
721
722 PROCEDURE Update_salesrep(
723 P_API_VERSION IN NUMBER,
724 P_INIT_MSG_LIST IN VARCHAR2,
725 P_COMMIT IN VARCHAR2,
726 P_SALESREP_ID IN JTF_RS_SALESREPS.SALESREP_ID%TYPE,
727 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
728 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE,
729 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE,
730 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE,
731 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE,
732 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE,
733 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE,
734 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE,
735 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE,
736 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE,
737 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE,
738 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE,
739 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE,
740 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE,
741 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE,
742 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_SALESREPS.OBJECT_VERSION_NUMBER%TYPE,
743 P_ATTRIBUTE_CATEGORY IN JTF_RS_SALESREPS.ATTRIBUTE_CATEGORY%TYPE,
744 P_ATTRIBUTE1 IN JTF_RS_SALESREPS.ATTRIBUTE1%TYPE,
745 P_ATTRIBUTE2 IN JTF_RS_SALESREPS.ATTRIBUTE2%TYPE,
746 P_ATTRIBUTE3 IN JTF_RS_SALESREPS.ATTRIBUTE3%TYPE,
747 P_ATTRIBUTE4 IN JTF_RS_SALESREPS.ATTRIBUTE4%TYPE,
748 P_ATTRIBUTE5 IN JTF_RS_SALESREPS.ATTRIBUTE5%TYPE,
749 P_ATTRIBUTE6 IN JTF_RS_SALESREPS.ATTRIBUTE6%TYPE,
750 P_ATTRIBUTE7 IN JTF_RS_SALESREPS.ATTRIBUTE7%TYPE,
751 P_ATTRIBUTE8 IN JTF_RS_SALESREPS.ATTRIBUTE8%TYPE,
752 P_ATTRIBUTE9 IN JTF_RS_SALESREPS.ATTRIBUTE9%TYPE,
753 P_ATTRIBUTE10 IN JTF_RS_SALESREPS.ATTRIBUTE10%TYPE,
754 P_ATTRIBUTE11 IN JTF_RS_SALESREPS.ATTRIBUTE11%TYPE,
755 P_ATTRIBUTE12 IN JTF_RS_SALESREPS.ATTRIBUTE12%TYPE,
756 P_ATTRIBUTE13 IN JTF_RS_SALESREPS.ATTRIBUTE13%TYPE,
757 P_ATTRIBUTE14 IN JTF_RS_SALESREPS.ATTRIBUTE14%TYPE,
758 P_ATTRIBUTE15 IN JTF_RS_SALESREPS.ATTRIBUTE15%TYPE,
759 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
760 X_MSG_COUNT OUT NOCOPY NUMBER,
761 X_MSG_DATA OUT NOCOPY VARCHAR2
762 )
763 IS
764
765 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SALESREP';
766 l_api_version CONSTANT NUMBER := 1.0;
767 l_rowid ROWID;
768 l_salesrep_id jtf_rs_salesreps.salesrep_id%type := p_salesrep_id;
769 l_resource_id jtf_rs_salesreps.resource_id%type;
770 l_sales_credit_type_id jtf_rs_salesreps.sales_credit_type_id%type := p_sales_credit_type_id
771 ;
772 l_name jtf_rs_salesreps.name%type := p_name;
773 l_status jtf_rs_salesreps.status%type := p_status;
774 l_start_date_active jtf_rs_salesreps.start_date_active%type := p_start_date_active;
775 l_end_date_active jtf_rs_salesreps.end_date_active%type := p_end_date_active;
776 l_gl_id_rev jtf_rs_salesreps.gl_id_rev%type := p_gl_id_rev;
777 l_gl_id_freight jtf_rs_salesreps.gl_id_freight%type := p_gl_id_freight;
778 l_gl_id_rec jtf_rs_salesreps.gl_id_rec%type := p_gl_id_rec;
779 l_set_of_books_id jtf_rs_salesreps.set_of_books_id%type := p_set_of_books_id;
780 l_salesrep_number jtf_rs_salesreps.salesrep_number%type := p_salesrep_number;
781 l_email_address jtf_rs_salesreps.email_address%type := p_email_address;
782 l_wh_update_date jtf_rs_salesreps.wh_update_date%type := p_wh_update_date;
783 l_sales_tax_geocode jtf_rs_salesreps.sales_tax_geocode%type := p_sales_tax_geocode;
784 l_sales_tax_inside_city_limits jtf_rs_salesreps.sales_tax_inside_city_limits%type := p_sales_tax_inside_city_limits;
785 l_org_id jtf_rs_salesreps.org_id%type := p_org_id;
786 l_object_version_number jtf_rs_salesreps.object_version_number%type := p_object_version_number;
787 c_val varchar2(1);
788 l_msg_data VARCHAR2(2000);
789 x_var varchar2(1);
790 l_msg_count NUMBER;
791 l_bind_data_id NUMBER;
792
793 CURSOR c_salesrep_update(l_salesrep_id jtf_rs_salesreps.salesrep_id%type, l_org_id jtf_rs_salesreps.org_id%type) is
794 SELECT salesrep_id,
795 resource_id,
796 DECODE(p_sales_credit_type_id,fnd_api.g_miss_num,sales_credit_type_id,p_sales_credit_type_id) sales_credit_type_id,
797 DECODE(p_name,fnd_api.g_miss_char,name,p_name) name,
798 DECODE(p_status,fnd_api.g_miss_char,status,p_status) status,
799 DECODE(p_start_date_active,fnd_api.g_miss_date,start_date_active,p_start_date_active) start_date_active,
800 DECODE(p_end_date_active,fnd_api.g_miss_date,end_date_active,p_end_date_active) end_date_active,
801 DECODE(p_gl_id_rev,fnd_api.g_miss_num,gl_id_rev,p_gl_id_rev) gl_id_rev,
802 DECODE(p_gl_id_freight,fnd_api.g_miss_num,gl_id_freight,p_gl_id_freight) gl_id_freight,
803 DECODE(p_gl_id_rec,fnd_api.g_miss_num,gl_id_rec,p_gl_id_rec) gl_id_rec,
804 DECODE(p_set_of_books_id,fnd_api.g_miss_num,set_of_books_id,p_set_of_books_id) set_of_books_id,
805 DECODE(p_salesrep_number,fnd_api.g_miss_char,salesrep_number,p_salesrep_number) salesrep_number,
806 DECODE(p_email_address,fnd_api.g_miss_char,email_address,p_email_address) email_address,
807 DECODE(p_wh_update_date,fnd_api.g_miss_date,wh_update_date,p_wh_update_date) wh_update_date,
808 person_id,
809 DECODE(p_sales_tax_geocode,fnd_api.g_miss_char,sales_tax_geocode,p_sales_tax_geocode) sales_tax_geocode,
810 DECODE(p_sales_tax_inside_city_limits,fnd_api.g_miss_char,sales_tax_inside_city_limits,p_sales_tax_inside_city_limits) sales_tax_inside_city_limits,
811 org_id,
812 DECODE(p_object_version_number,fnd_api.g_miss_char,object_version_number,p_object_version_number) object_version_number,
813 DECODE(p_attribute_category,fnd_api.g_miss_char,attribute_category,p_attribute_category) attribute_category,
814 DECODE(p_attribute1,fnd_api.g_miss_char,attribute1,p_attribute1) attribute1,
815 DECODE(p_attribute2,fnd_api.g_miss_char,attribute2,p_attribute2) attribute2,
816 DECODE(p_attribute3,fnd_api.g_miss_char,attribute3,p_attribute3) attribute3,
817 DECODE(p_attribute4,fnd_api.g_miss_char,attribute4,p_attribute4) attribute4,
818 DECODE(p_attribute5,fnd_api.g_miss_char,attribute5,p_attribute5) attribute5,
819 DECODE(p_attribute6,fnd_api.g_miss_char,attribute6,p_attribute6) attribute6,
820 DECODE(p_attribute7,fnd_api.g_miss_char,attribute7,p_attribute7) attribute7,
821 DECODE(p_attribute8,fnd_api.g_miss_char,attribute8,p_attribute8) attribute8,
822 DECODE(p_attribute9,fnd_api.g_miss_char,attribute9,p_attribute9) attribute9,
823 DECODE(p_attribute10,fnd_api.g_miss_char,attribute10,p_attribute10) attribute10,
824 DECODE(p_attribute11,fnd_api.g_miss_char,attribute11,p_attribute11) attribute11,
825 DECODE(p_attribute12,fnd_api.g_miss_char,attribute12,p_attribute12) attribute12,
826 DECODE(p_attribute13,fnd_api.g_miss_char,attribute13,p_attribute13) attribute13,
827 DECODE(p_attribute14,fnd_api.g_miss_char,attribute14,p_attribute14) attribute14,
828 DECODE(p_attribute15,fnd_api.g_miss_char,attribute15,p_attribute15) attribute15
829 FROM jtf_rs_salesreps
830 WHERE salesrep_id = l_salesrep_id
831 AND nvl(org_id,-99) = nvl(l_org_id,-99);
832 salesrep_rec c_salesrep_update%ROWTYPE;
833
834 CURSOR c_salesrep_number IS
835 SELECT 'X'
836 FROM jtf_rs_salesreps
837 WHERE salesrep_number = l_salesrep_number
838 AND nvl(org_id,-99) = nvl(l_org_id,-99)
839 AND salesrep_id <> p_salesrep_id;
840
841
842 BEGIN
843
844
845 SAVEPOINT update_salesrep_pvt;
846
847 x_return_status := fnd_api.g_ret_sts_success;
848
849 /* Standard call to check for call compatibility. */
850 IF NOT fnd_api.Compatible_api_call( l_api_version,
851 p_api_version,
852 l_api_name,
853 G_PKG_NAME)
854 THEN
855 RAISE fnd_api.g_exc_unexpected_error;
856 END IF;
857
858 IF fnd_api.to_Boolean(p_init_msg_list) THEN
859 fnd_msg_pub.initialize;
860 END IF;
861
862
863 -- dbms_output.put_line('Org id before validation : '|| l_org_id);
864 l_org_id := MO_GLOBAL.get_valid_org(p_org_id);
865 IF l_org_id is NULL THEN
866 -- dbms_output.put_line('Org id is Null');
867 x_return_status := FND_API.G_RET_STS_ERROR;
868 RAISE FND_API.G_EXC_ERROR;
869 END IF;
870 -- dbms_output.put_line('Org id is : '||l_org_id);
871
872 /* Make the pre processing call to the user hooks */
873
874 /* Pre Call to the Customer Type User Hook */
875
876 IF jtf_usr_hks.ok_to_execute(
877 'JTF_RS_SALESREPS_PVT',
878 'UPDATE_SALESREP',
879 'B',
880 'C')
881 THEN
882 jtf_rs_salesreps_cuhk.update_salesrep_pre(
883 p_salesrep_id => l_salesrep_id,
884 p_sales_credit_type_id => l_sales_credit_type_id,
885 p_name => l_name,
886 p_status => l_status,
887 p_start_date_active => l_start_date_active,
888 p_end_date_active => l_end_date_active,
889 p_gl_id_rev => l_gl_id_rev,
890 p_gl_id_freight => l_gl_id_freight,
891 p_gl_id_rec => l_gl_id_rec,
892 p_set_of_books_id => l_set_of_books_id,
893 p_salesrep_number => l_salesrep_number,
894 p_email_address => l_email_address,
895 p_wh_update_date => l_wh_update_date,
896 p_sales_tax_geocode => l_sales_tax_geocode,
897 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
898 x_return_status => x_return_status);
899
900 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
901
902 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
903 fnd_msg_pub.add;
904 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
905 RAISE FND_API.G_EXC_ERROR;
906 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
908 END IF;
909
910 END IF;
911 END IF;
912
913 /* Pre Call to the Vertical Type User Hook */
914
915 IF jtf_usr_hks.ok_to_execute(
916 'JTF_RS_SALESREPS_PVT',
917 'UPDATE_SALESREP',
918 'B',
919 'V')
920 THEN
921
922 jtf_rs_salesreps_vuhk.update_salesrep_pre(
923 p_salesrep_id => l_salesrep_id,
924 p_sales_credit_type_id => l_sales_credit_type_id,
925 p_name => l_name,
926 p_status => l_status,
927 p_start_date_active => l_start_date_active,
928 p_end_date_active => l_end_date_active,
929 p_gl_id_rev => l_gl_id_rev,
930 p_gl_id_freight => l_gl_id_freight,
931 p_gl_id_rec => l_gl_id_rec,
932 p_set_of_books_id => l_set_of_books_id,
933 p_salesrep_number => l_salesrep_number,
934 p_email_address => l_email_address,
935 p_wh_update_date => l_wh_update_date,
936 p_sales_tax_geocode => l_sales_tax_geocode,
937 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
938 x_return_status => x_return_status);
939
940 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
941
942 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
943 fnd_msg_pub.add;
944
945 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
946 RAISE FND_API.G_EXC_ERROR;
947 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
949 END IF;
950
951 END IF;
952 END IF;
953
954
955 /* Pre Call to the Internal Type User Hook */
956
957 IF jtf_usr_hks.ok_to_execute(
958 'JTF_RS_SALESREPS_PVT',
959 'UPDATE_SALESREP',
960 'B',
961 'I')
962 THEN
963
964 jtf_rs_salesreps_iuhk.update_salesrep_pre(
965 p_salesrep_id => l_salesrep_id,
966 p_sales_credit_type_id => l_sales_credit_type_id,
967 p_name => l_name,
968 p_status => l_status,
969 p_start_date_active => l_start_date_active,
970 p_end_date_active => l_end_date_active,
971 p_gl_id_rev => l_gl_id_rev,
972 p_gl_id_freight => l_gl_id_freight,
973 p_gl_id_rec => l_gl_id_rec,
974 p_set_of_books_id => l_set_of_books_id,
975 p_salesrep_number => l_salesrep_number,
976 p_email_address => l_email_address,
977 p_wh_update_date => l_wh_update_date,
978 p_sales_tax_geocode => l_sales_tax_geocode,
979 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
980 x_return_status => x_return_status);
981
982 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
983
984 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
985 fnd_msg_pub.add;
986
987 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
988 RAISE FND_API.G_EXC_ERROR;
989 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
991 END IF;
992
993 END IF;
994 END IF;
995
996
997 /* Starting API body */
998
999
1000 OPEN c_salesrep_update(l_salesrep_id,l_org_id);
1001 FETCH c_salesrep_update INTO salesrep_rec;
1002
1003 IF c_salesrep_update%NOTFOUND THEN
1004 CLOSE c_salesrep_update;
1005 fnd_message.set_name('JTF','JTF_RS_INVALID_SALESREP_ID');
1006 fnd_message.set_token('P_SALESREP_ID',l_salesrep_id);
1007 fnd_msg_pub.add;
1008 RAISE fnd_api.g_exc_error;
1009
1010 END IF;
1011
1012
1013 /* Validating Dates */
1014
1015
1016 jtf_resource_utl.validate_input_dates(
1017 l_start_date_active
1018 ,l_end_date_active
1019 ,x_return_status);
1020 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1021 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1022 RAISE FND_API.G_EXC_ERROR;
1023 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1025 END IF;
1026
1027 END IF;
1028
1029 /* Record is being updated, check that salesrep number does not already exist, for any other salesrep Id */
1030 IF (l_salesrep_number IS NOT NULL) THEN
1031 OPEN c_salesrep_number;
1032 FETCH c_salesrep_number INTO c_val;
1033 IF (c_salesrep_number%FOUND) THEN
1034 fnd_message.set_name('JTF', 'JTF_RS_ERR_SALESREP_NUMBER');
1035 fnd_message.set_token('P_SALESREP_NUMBER', l_salesrep_number);
1036 fnd_msg_pub.add;
1037 CLOSE c_salesrep_number;
1038 RAISE fnd_api.g_exc_error;
1039 END IF;
1040 CLOSE c_salesrep_number;
1041 ELSE
1042 fnd_message.set_name('JTF', 'JTF_RS_SALESREP_NUMBER_NULL');
1043 fnd_msg_pub.add;
1044 CLOSE c_salesrep_number;
1045 RAISE fnd_api.g_exc_error;
1046
1047 END IF;
1048
1049 /* Locking the row before updating */
1050 BEGIN
1051 jtf_rs_salesreps_pkg.lock_row(
1052 x_salesrep_id => l_salesrep_id,
1053 x_org_id => l_org_id,
1054 x_object_version_number => l_object_version_number
1055 );
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1059 fnd_msg_pub.add;
1060 RAISE fnd_api.g_exc_error;
1061 END;
1062
1063
1064 BEGIN
1065
1066 /* Increment Object version number */
1067
1068 l_object_version_number := l_object_version_number + 1;
1069
1070 /* Invoke table handler to insert into JTF_RS__SALESREPS */
1071
1072 jtf_rs_salesreps_pkg.update_row(
1073 X_SALESREP_ID => l_salesrep_id,
1074 X_RESOURCE_ID => salesrep_rec.resource_id,
1075 X_SALES_CREDIT_TYPE_ID => salesrep_rec.sales_credit_type_id,
1076 X_NAME => salesrep_rec.name,
1077 X_STATUS => salesrep_rec.status,
1078 X_START_DATE_ACTIVE => salesrep_rec.start_date_active,
1079 X_END_DATE_ACTIVE => salesrep_rec.end_date_active,
1080 X_GL_ID_REV => salesrep_rec.gl_id_rev,
1081 X_GL_ID_FREIGHT => salesrep_rec.gl_id_freight,
1082 X_GL_ID_REC => salesrep_rec.gl_id_rec,
1083 X_SET_OF_BOOKS_ID => salesrep_rec.set_of_books_id,
1084 X_SALESREP_NUMBER => salesrep_rec.salesrep_number,
1085 X_EMAIL_ADDRESS => salesrep_rec.email_address,
1086 X_WH_UPDATE_DATE => salesrep_rec.wh_update_date,
1087 X_PERSON_ID => salesrep_rec.person_id,
1088 X_SALES_TAX_GEOCODE => salesrep_rec.sales_tax_geocode,
1089 X_SALES_TAX_INSIDE_CITY_LIMITS => salesrep_rec.sales_tax_inside_city_limits,
1090 X_ORG_ID => l_org_id,
1091 X_OBJECT_VERSION_NUMBER => l_object_version_number,
1092 X_ATTRIBUTE_CATEGORY => salesrep_rec.attribute_category,
1093 X_ATTRIBUTE2 => salesrep_rec.attribute2,
1094 X_ATTRIBUTE3 => salesrep_rec.attribute3,
1095 X_ATTRIBUTE4 => salesrep_rec.attribute4,
1096 X_ATTRIBUTE5 => salesrep_rec.attribute5,
1097 X_ATTRIBUTE6 => salesrep_rec.attribute6,
1098 X_ATTRIBUTE7 => salesrep_rec.attribute7,
1099 X_ATTRIBUTE8 => salesrep_rec.attribute8,
1100 X_ATTRIBUTE9 => salesrep_rec.attribute9,
1101 X_ATTRIBUTE10 => salesrep_rec.attribute10,
1102 X_ATTRIBUTE11 => salesrep_rec.attribute11,
1103 X_ATTRIBUTE12 => salesrep_rec.attribute12,
1104 X_ATTRIBUTE13 => salesrep_rec.attribute13,
1105 X_ATTRIBUTE14 => salesrep_rec.attribute14,
1106 X_ATTRIBUTE15 => salesrep_rec.attribute15,
1107 X_ATTRIBUTE1 => salesrep_rec.attribute1,
1108 X_LAST_UPDATE_DATE => SYSDATE,
1109 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
1110 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
1111 );
1112
1113 p_object_version_number := l_object_version_number;
1114
1115 EXCEPTION
1116 WHEN NO_DATA_FOUND THEN
1117 /* dbms_output.put_line('Error in Table Haandler'); */
1118 CLOSE c_salesrep_update;
1119
1120 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
1121 fnd_msg_pub.add;
1122
1123 RAISE fnd_api.g_exc_error;
1124
1125 END;
1126
1127 CLOSE c_salesrep_update;
1128
1129 /* Make the post processing call to the user hooks */
1130
1131 /* Post Call to the Customer Type User Hook */
1132
1133 IF jtf_usr_hks.ok_to_execute(
1134 'JTF_RS_SALESREPS_PVT',
1135 'UPDATE_SALESREP',
1136 'A',
1137 'C')
1138 THEN
1139 jtf_rs_salesreps_cuhk.update_salesrep_post(
1140 p_salesrep_id => l_salesrep_id,
1141 p_sales_credit_type_id => l_sales_credit_type_id,
1142 p_name => l_name,
1143 p_status => l_status,
1144 p_start_date_active => l_start_date_active,
1145 p_end_date_active => l_end_date_active,
1146 p_gl_id_rev => l_gl_id_rev,
1147 p_gl_id_freight => l_gl_id_freight,
1148 p_gl_id_rec => l_gl_id_rec,
1149 p_set_of_books_id => l_set_of_books_id,
1150 p_salesrep_number => l_salesrep_number,
1151 p_email_address => l_email_address,
1152 p_wh_update_date => l_wh_update_date,
1153 p_sales_tax_geocode => l_sales_tax_geocode,
1154 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
1155 x_return_status => x_return_status);
1156
1157 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1158
1159 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1160 fnd_msg_pub.add;
1161
1162 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1163 RAISE FND_API.G_EXC_ERROR;
1164 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166 END IF;
1167
1168 END IF;
1169 END IF;
1170
1171 /* Post Call to the Vertical Type User Hook */
1172
1173 IF jtf_usr_hks.ok_to_execute(
1174 'JTF_RS_SALESREPS_PVT',
1175 'UPDATE_SALESREP',
1176 'A',
1177 'V')
1178 THEN
1179 jtf_rs_salesreps_vuhk.update_salesrep_post(
1180 p_salesrep_id => l_salesrep_id,
1181 p_sales_credit_type_id => l_sales_credit_type_id,
1182 p_name => l_name,
1183 p_status => l_status,
1184 p_start_date_active => l_start_date_active,
1185 p_end_date_active => l_end_date_active,
1186 p_gl_id_rev => l_gl_id_rev,
1187 p_gl_id_freight => l_gl_id_freight,
1188 p_gl_id_rec => l_gl_id_rec,
1189 p_set_of_books_id => l_set_of_books_id,
1190 p_salesrep_number => l_salesrep_number,
1191 p_email_address => l_email_address,
1192 p_wh_update_date => l_wh_update_date,
1193 p_sales_tax_geocode => l_sales_tax_geocode,
1194 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
1195 x_return_status => x_return_status);
1196
1197 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1198
1199 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1200 fnd_msg_pub.add;
1201
1202 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1203 RAISE FND_API.G_EXC_ERROR;
1204 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1206 END IF;
1207
1208 END IF;
1209 END IF;
1210
1211 /* Post Call to the Internal Type User Hook */
1212
1213 IF jtf_usr_hks.ok_to_execute(
1214 'JTF_RS_SALESREPS_PVT',
1215 'UPDATE_SALESREP',
1216 'A',
1217 'I')
1218 THEN
1219 jtf_rs_salesreps_iuhk.update_salesrep_post(
1220 p_salesrep_id => l_salesrep_id,
1221 p_sales_credit_type_id => l_sales_credit_type_id,
1222 p_name => l_name,
1223 p_status => l_status,
1224 p_start_date_active => l_start_date_active,
1225 p_end_date_active => l_end_date_active,
1226 p_gl_id_rev => l_gl_id_rev,
1227 p_gl_id_freight => l_gl_id_freight,
1228 p_gl_id_rec => l_gl_id_rec,
1229 p_set_of_books_id => l_set_of_books_id,
1230 p_salesrep_number => l_salesrep_number,
1231 p_email_address => l_email_address,
1232 p_wh_update_date => l_wh_update_date,
1233 p_sales_tax_geocode => l_sales_tax_geocode,
1234 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
1235 x_return_status => x_return_status);
1236
1237 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1238
1239 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1240 fnd_msg_pub.add;
1241
1242 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1243 RAISE FND_API.G_EXC_ERROR;
1244 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1246 END IF;
1247
1248 END IF;
1249 END IF;
1250
1251 /* Standard call for Message Generation */
1252
1253 IF jtf_usr_hks.ok_to_execute(
1254 'JTF_RS_SALESREPS_PVT',
1255 'UPDATE_SALESREP',
1256 'M',
1257 'M')
1258 THEN
1259
1260 IF (jtf_rs_salesreps_cuhk.ok_to_generate_msg(
1261 p_salesrep_id => l_salesrep_id,
1262 x_return_status => x_return_status) )
1263 THEN
1264
1265 /* Get the bind data id for the Business Object Instance */
1266
1267 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1268
1269
1270 /* Set bind values for the bind variables in the Business Object SQL */
1271
1272 jtf_usr_hks.load_bind_data(l_bind_data_id, 'salesrep_id', l_salesrep_id, 'S', 'N');
1273
1274
1275 /* Call the message generation API */
1276
1277 jtf_usr_hks.generate_message(
1278 p_prod_code => 'JTF',
1279 p_bus_obj_code => 'RS_SRP',
1280 p_action_code => 'D',
1281 p_bind_data_id => l_bind_data_id,
1282 x_return_code => x_return_status);
1283
1284
1285 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1286
1287
1288 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1289 fnd_msg_pub.add;
1290
1291 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1292 RAISE FND_API.G_EXC_ERROR;
1293 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1295 END IF;
1296
1297
1298 END IF;
1299
1300 END IF;
1301
1302 END IF;
1303
1304 IF fnd_api.to_Boolean(p_commit) THEN
1305 COMMIT WORK;
1306 END IF;
1307
1308 /* Standard call to get message count and if count is 1, get message info. */
1309 FND_MSG_PUB.Count_And_Get
1310 (p_count => x_msg_count,
1311 p_data => x_msg_data
1312 );
1313
1314 EXCEPTION
1315 WHEN fnd_api.g_exc_error THEN
1316 ROLLBACK TO update_salesrep_pvt;
1317 x_return_status := fnd_api.g_ret_sts_error;
1318 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1319 p_data => x_msg_data);
1320 WHEN fnd_api.g_exc_unexpected_error THEN
1321 ROLLBACK TO update_salesrep_pvt;
1322 x_return_status := fnd_api.g_ret_sts_unexp_error;
1323 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1324 p_data => x_msg_data);
1325 WHEN OTHERS THEN
1326 ROLLBACK TO update_salesrep_pvt;
1327 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1328 fnd_message.set_token('P_SQLCODE',SQLCODE);
1329 fnd_message.set_token('P_SQLERRM',SQLERRM);
1330 fnd_message.set_token('P_API_NAME', l_api_name);
1331 FND_MSG_PUB.add;
1332 x_return_status := fnd_api.g_ret_sts_unexp_error;
1333 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1334 p_data => x_msg_data);
1335
1336 End update_salesrep;
1337
1338 End JTF_RS_SALESREPS_PVT;