[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SALESREPS_PVT
Source
1 PACKAGE BODY JTF_RS_SALESREPS_PVT AS
2 /* $Header: jtfrsvsb.pls 120.4 2011/02/07 08:59:36 rgokavar 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 --Bug11719792
1012 --In Salesrep Update mode when there is no value in p_start_date_active parameter
1013 --validating G_MISS_DATE instead of actual Start Date i.e salesrep_rec.start_date_active
1014
1015 /* Validating Dates */
1016
1017
1018 jtf_resource_utl.validate_input_dates(
1019 --l_start_date_active
1020 salesrep_rec.start_date_active
1021 ,l_end_date_active
1022 ,x_return_status);
1023 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1024 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1025 RAISE FND_API.G_EXC_ERROR;
1026 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1028 END IF;
1029
1030 END IF;
1031
1032 /* Record is being updated, check that salesrep number does not already exist, for any other salesrep Id */
1033 IF (l_salesrep_number IS NOT NULL) THEN
1034 OPEN c_salesrep_number;
1035 FETCH c_salesrep_number INTO c_val;
1036 IF (c_salesrep_number%FOUND) THEN
1037 fnd_message.set_name('JTF', 'JTF_RS_ERR_SALESREP_NUMBER');
1038 fnd_message.set_token('P_SALESREP_NUMBER', l_salesrep_number);
1039 fnd_msg_pub.add;
1040 CLOSE c_salesrep_number;
1041 RAISE fnd_api.g_exc_error;
1042 END IF;
1043 CLOSE c_salesrep_number;
1044 ELSE
1045 fnd_message.set_name('JTF', 'JTF_RS_SALESREP_NUMBER_NULL');
1046 fnd_msg_pub.add;
1047 CLOSE c_salesrep_number;
1048 RAISE fnd_api.g_exc_error;
1049
1050 END IF;
1051
1052 /* Locking the row before updating */
1053 BEGIN
1054 jtf_rs_salesreps_pkg.lock_row(
1055 x_salesrep_id => l_salesrep_id,
1056 x_org_id => l_org_id,
1057 x_object_version_number => l_object_version_number
1058 );
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1062 fnd_msg_pub.add;
1063 RAISE fnd_api.g_exc_error;
1064 END;
1065
1066
1067 BEGIN
1068
1069 /* Increment Object version number */
1070
1071 l_object_version_number := l_object_version_number + 1;
1072
1073 /* Invoke table handler to insert into JTF_RS__SALESREPS */
1074
1075 jtf_rs_salesreps_pkg.update_row(
1076 X_SALESREP_ID => l_salesrep_id,
1077 X_RESOURCE_ID => salesrep_rec.resource_id,
1078 X_SALES_CREDIT_TYPE_ID => salesrep_rec.sales_credit_type_id,
1079 X_NAME => salesrep_rec.name,
1080 X_STATUS => salesrep_rec.status,
1081 X_START_DATE_ACTIVE => salesrep_rec.start_date_active,
1082 X_END_DATE_ACTIVE => salesrep_rec.end_date_active,
1083 X_GL_ID_REV => salesrep_rec.gl_id_rev,
1084 X_GL_ID_FREIGHT => salesrep_rec.gl_id_freight,
1085 X_GL_ID_REC => salesrep_rec.gl_id_rec,
1086 X_SET_OF_BOOKS_ID => salesrep_rec.set_of_books_id,
1087 X_SALESREP_NUMBER => salesrep_rec.salesrep_number,
1088 X_EMAIL_ADDRESS => salesrep_rec.email_address,
1089 X_WH_UPDATE_DATE => salesrep_rec.wh_update_date,
1090 X_PERSON_ID => salesrep_rec.person_id,
1091 X_SALES_TAX_GEOCODE => salesrep_rec.sales_tax_geocode,
1092 X_SALES_TAX_INSIDE_CITY_LIMITS => salesrep_rec.sales_tax_inside_city_limits,
1093 X_ORG_ID => l_org_id,
1094 X_OBJECT_VERSION_NUMBER => l_object_version_number,
1095 X_ATTRIBUTE_CATEGORY => salesrep_rec.attribute_category,
1096 X_ATTRIBUTE2 => salesrep_rec.attribute2,
1097 X_ATTRIBUTE3 => salesrep_rec.attribute3,
1098 X_ATTRIBUTE4 => salesrep_rec.attribute4,
1099 X_ATTRIBUTE5 => salesrep_rec.attribute5,
1100 X_ATTRIBUTE6 => salesrep_rec.attribute6,
1101 X_ATTRIBUTE7 => salesrep_rec.attribute7,
1102 X_ATTRIBUTE8 => salesrep_rec.attribute8,
1103 X_ATTRIBUTE9 => salesrep_rec.attribute9,
1104 X_ATTRIBUTE10 => salesrep_rec.attribute10,
1105 X_ATTRIBUTE11 => salesrep_rec.attribute11,
1106 X_ATTRIBUTE12 => salesrep_rec.attribute12,
1107 X_ATTRIBUTE13 => salesrep_rec.attribute13,
1108 X_ATTRIBUTE14 => salesrep_rec.attribute14,
1109 X_ATTRIBUTE15 => salesrep_rec.attribute15,
1110 X_ATTRIBUTE1 => salesrep_rec.attribute1,
1111 X_LAST_UPDATE_DATE => SYSDATE,
1112 X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
1113 X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
1114 );
1115
1116 p_object_version_number := l_object_version_number;
1117
1118 EXCEPTION
1119 WHEN NO_DATA_FOUND THEN
1120 /* dbms_output.put_line('Error in Table Haandler'); */
1121 CLOSE c_salesrep_update;
1122
1123 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
1124 fnd_msg_pub.add;
1125
1126 RAISE fnd_api.g_exc_error;
1127
1128 END;
1129
1130 CLOSE c_salesrep_update;
1131
1132 /* Make the post processing call to the user hooks */
1133
1134 /* Post Call to the Customer Type User Hook */
1135
1136 IF jtf_usr_hks.ok_to_execute(
1137 'JTF_RS_SALESREPS_PVT',
1138 'UPDATE_SALESREP',
1139 'A',
1140 'C')
1141 THEN
1142 jtf_rs_salesreps_cuhk.update_salesrep_post(
1143 p_salesrep_id => l_salesrep_id,
1144 p_sales_credit_type_id => l_sales_credit_type_id,
1145 p_name => l_name,
1146 p_status => l_status,
1147 p_start_date_active => l_start_date_active,
1148 p_end_date_active => l_end_date_active,
1149 p_gl_id_rev => l_gl_id_rev,
1150 p_gl_id_freight => l_gl_id_freight,
1151 p_gl_id_rec => l_gl_id_rec,
1152 p_set_of_books_id => l_set_of_books_id,
1153 p_salesrep_number => l_salesrep_number,
1154 p_email_address => l_email_address,
1155 p_wh_update_date => l_wh_update_date,
1156 p_sales_tax_geocode => l_sales_tax_geocode,
1157 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
1158 x_return_status => x_return_status);
1159
1160 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1161
1162 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1163 fnd_msg_pub.add;
1164
1165 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1166 RAISE FND_API.G_EXC_ERROR;
1167 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1169 END IF;
1170
1171 END IF;
1172 END IF;
1173
1174 /* Post Call to the Vertical Type User Hook */
1175
1176 IF jtf_usr_hks.ok_to_execute(
1177 'JTF_RS_SALESREPS_PVT',
1178 'UPDATE_SALESREP',
1179 'A',
1180 'V')
1181 THEN
1182 jtf_rs_salesreps_vuhk.update_salesrep_post(
1183 p_salesrep_id => l_salesrep_id,
1184 p_sales_credit_type_id => l_sales_credit_type_id,
1185 p_name => l_name,
1186 p_status => l_status,
1187 p_start_date_active => l_start_date_active,
1188 p_end_date_active => l_end_date_active,
1189 p_gl_id_rev => l_gl_id_rev,
1190 p_gl_id_freight => l_gl_id_freight,
1191 p_gl_id_rec => l_gl_id_rec,
1192 p_set_of_books_id => l_set_of_books_id,
1193 p_salesrep_number => l_salesrep_number,
1194 p_email_address => l_email_address,
1195 p_wh_update_date => l_wh_update_date,
1196 p_sales_tax_geocode => l_sales_tax_geocode,
1197 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
1198 x_return_status => x_return_status);
1199
1200 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1201
1202 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1203 fnd_msg_pub.add;
1204
1205 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1206 RAISE FND_API.G_EXC_ERROR;
1207 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1209 END IF;
1210
1211 END IF;
1212 END IF;
1213
1214 /* Post Call to the Internal Type User Hook */
1215
1216 IF jtf_usr_hks.ok_to_execute(
1217 'JTF_RS_SALESREPS_PVT',
1218 'UPDATE_SALESREP',
1219 'A',
1220 'I')
1221 THEN
1222 jtf_rs_salesreps_iuhk.update_salesrep_post(
1223 p_salesrep_id => l_salesrep_id,
1224 p_sales_credit_type_id => l_sales_credit_type_id,
1225 p_name => l_name,
1226 p_status => l_status,
1227 p_start_date_active => l_start_date_active,
1228 p_end_date_active => l_end_date_active,
1229 p_gl_id_rev => l_gl_id_rev,
1230 p_gl_id_freight => l_gl_id_freight,
1231 p_gl_id_rec => l_gl_id_rec,
1232 p_set_of_books_id => l_set_of_books_id,
1233 p_salesrep_number => l_salesrep_number,
1234 p_email_address => l_email_address,
1235 p_wh_update_date => l_wh_update_date,
1236 p_sales_tax_geocode => l_sales_tax_geocode,
1237 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
1238 x_return_status => x_return_status);
1239
1240 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1241
1242 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1243 fnd_msg_pub.add;
1244
1245 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1246 RAISE FND_API.G_EXC_ERROR;
1247 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 END IF;
1250
1251 END IF;
1252 END IF;
1253
1254 /* Standard call for Message Generation */
1255
1256 IF jtf_usr_hks.ok_to_execute(
1257 'JTF_RS_SALESREPS_PVT',
1258 'UPDATE_SALESREP',
1259 'M',
1260 'M')
1261 THEN
1262
1263 IF (jtf_rs_salesreps_cuhk.ok_to_generate_msg(
1264 p_salesrep_id => l_salesrep_id,
1265 x_return_status => x_return_status) )
1266 THEN
1267
1268 /* Get the bind data id for the Business Object Instance */
1269
1270 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1271
1272
1273 /* Set bind values for the bind variables in the Business Object SQL */
1274
1275 jtf_usr_hks.load_bind_data(l_bind_data_id, 'salesrep_id', l_salesrep_id, 'S', 'N');
1276
1277
1278 /* Call the message generation API */
1279
1280 jtf_usr_hks.generate_message(
1281 p_prod_code => 'JTF',
1282 p_bus_obj_code => 'RS_SRP',
1283 p_action_code => 'D',
1284 p_bind_data_id => l_bind_data_id,
1285 x_return_code => x_return_status);
1286
1287
1288 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1289
1290
1291 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1292 fnd_msg_pub.add;
1293
1294 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1295 RAISE FND_API.G_EXC_ERROR;
1296 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1298 END IF;
1299
1300
1301 END IF;
1302
1303 END IF;
1304
1305 END IF;
1306
1307 IF fnd_api.to_Boolean(p_commit) THEN
1308 COMMIT WORK;
1309 END IF;
1310
1311 /* Standard call to get message count and if count is 1, get message info. */
1312 FND_MSG_PUB.Count_And_Get
1313 (p_count => x_msg_count,
1314 p_data => x_msg_data
1315 );
1316
1317 EXCEPTION
1318 WHEN fnd_api.g_exc_error THEN
1319 ROLLBACK TO update_salesrep_pvt;
1320 x_return_status := fnd_api.g_ret_sts_error;
1321 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1322 p_data => x_msg_data);
1323 WHEN fnd_api.g_exc_unexpected_error THEN
1324 ROLLBACK TO update_salesrep_pvt;
1325 x_return_status := fnd_api.g_ret_sts_unexp_error;
1326 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1327 p_data => x_msg_data);
1328 WHEN OTHERS THEN
1329 ROLLBACK TO update_salesrep_pvt;
1330 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1331 fnd_message.set_token('P_SQLCODE',SQLCODE);
1332 fnd_message.set_token('P_SQLERRM',SQLERRM);
1333 fnd_message.set_token('P_API_NAME', l_api_name);
1334 FND_MSG_PUB.add;
1335 x_return_status := fnd_api.g_ret_sts_unexp_error;
1336 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1337 p_data => x_msg_data);
1338
1339 End update_salesrep;
1340
1341 End JTF_RS_SALESREPS_PVT;