[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SALESREPS_PUB
Source
1 PACKAGE BODY jtf_rs_salesreps_pub AS
2 /* $Header: jtfrspsb.pls 120.4 2005/10/17 17:18:37 nsinghai ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing Salesreps , like
6 create and update Salesreps.
7 Its main procedures are as following:
8 Create Salesreps
9 Update Salesreps
10 This package valoidates the input parameters to these procedures and then
11 to do business validations and to do actual inserts and updates into tables.
12 ******************************************************************************************/
13
14 /* Package Varianles */
15
16 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_SALESREPS_PUB';
17
18 /* Procedure to create the Salesreps
19 based on input values passed by calling routines. */
20
21 PROCEDURE create_salesrep
22 (P_API_VERSION IN NUMBER,
23 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
24 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
25 P_RESOURCE_ID IN JTF_RS_SALESREPS.RESOURCE_ID%TYPE,
26 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
27 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE DEFAULT NULL,
28 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE DEFAULT NULL,
29 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE DEFAULT SYSDATE,
30 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
31 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE DEFAULT FND_API.G_MISS_NUM,
32 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE DEFAULT NULL,
33 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE DEFAULT NULL,
34 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE DEFAULT NULL,
35 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL,
36 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE DEFAULT NULL,
37 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE DEFAULT NULL,
38 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE DEFAULT NULL,
39 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE DEFAULT NULL,
40 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE DEFAULT NULL,
41 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
42 X_MSG_COUNT OUT NOCOPY NUMBER,
43 X_MSG_DATA OUT NOCOPY VARCHAR2,
44 X_SALESREP_ID OUT NOCOPY JTF_RS_SALESREPS.SALESREP_ID%TYPE
45 ) IS
46
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SALESREP';
49 l_resource_id jtf_rs_salesreps.resource_id%type := p_resource_id;
50 l_sales_credit_type_id jtf_rs_salesreps.sales_credit_type_id%type := p_sales_credit_type_id;
51 l_name jtf_rs_salesreps.name%type := p_name;
52 l_status jtf_rs_salesreps.status%type := p_status;
53 l_start_date_active jtf_rs_salesreps.start_date_active%type := p_start_date_active;
54 l_end_date_active jtf_rs_salesreps.end_date_active%type := p_end_date_active;
55 l_org_id jtf_rs_salesreps.org_id%type := p_org_id;
56 l_gl_id_rev jtf_rs_salesreps.gl_id_rev%type := p_gl_id_rev;
57 l_gl_id_freight jtf_rs_salesreps.gl_id_freight%type := p_gl_id_freight;
58 l_gl_id_rec jtf_rs_salesreps.gl_id_rec%type := p_gl_id_rec;
59 l_set_of_books_id jtf_rs_salesreps.set_of_books_id%type := p_set_of_books_id;
60 l_salesrep_number jtf_rs_salesreps.salesrep_number%type := p_salesrep_number;
61 l_email_address jtf_rs_salesreps.email_address%type := p_email_address;
62 l_wh_update_date jtf_rs_salesreps.wh_update_date%type := p_wh_update_date;
63 l_sales_tax_geocode jtf_rs_salesreps.sales_tax_geocode%type := p_sales_tax_geocode;
64 l_sales_tax_inside_city_limits jtf_rs_salesreps.sales_tax_inside_city_limits%type := p_sales_tax_inside_city_limits;
65 -- added for NOCOPY
66 l_resource_id_out jtf_rs_salesreps.resource_id%type;
67
68 BEGIN
69
70 SAVEPOINT create_salesreps_pub;
71
72 x_return_status := fnd_api.g_ret_sts_success;
73
74 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
75 RAISE fnd_api.g_exc_unexpected_error;
76 END IF;
77
78 IF fnd_api.to_boolean(p_init_msg_list) THEN
79 fnd_msg_pub.initialize;
80 END IF;
81
82
83 /* Validate Resource */
84
85 jtf_resource_utl.validate_resource_number(
86 p_resource_id => l_resource_id,
87 p_resource_number => NULL,
88 x_return_status => x_return_status,
89 x_resource_id => l_resource_id_out);
90 -- added for NOCOPY
91 l_resource_id := l_resource_id_out;
92
93
94 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
95 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
96 RAISE FND_API.G_EXC_ERROR;
97 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100 END IF;
101
102
103 /* Validate Sales Credit type Id */
104
105 jtf_resource_utl.validate_sales_credit_type(
106 p_sales_credit_type_id => l_sales_credit_type_id,
107 x_return_status => x_return_status
108 );
109
110 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
111 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
112 RAISE FND_API.G_EXC_ERROR;
113 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116 END IF;
117
118 /* Validate start date */
119 /*
120 IF l_start_date_active IS NULL THEN
121 x_return_status := fnd_api.g_ret_sts_unexp_error;
122 RAISE fnd_api.g_exc_unexpected_error;
123 END IF;
124 */
125
126 /*
127 Validate Salesrep dates
128 Created by Nishant on 17-Oct-2005 to fix bug 4354269. It will validate both
129 start date and end date against resource start date and end date.
130 */
131 jtf_resource_utl.validate_salesrep_dates
132 (P_ID => l_resource_id,
133 P_ORG_ID => l_org_id,
134 P_SRP_START_DATE => l_start_date_active,
135 P_SRP_END_DATE => l_end_date_active,
136 P_CR_UPD_MODE => 'C',
137 X_RETURN_STATUS => x_return_status);
138
139 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
140 RAISE FND_API.G_EXC_ERROR;
141 END IF;
142
143 /* Check the Global Variable for SalesrepID, and call the appropriate Private API */
144
145 -- dbms_output.put_line ('Before setting the global flag in create_resource');
146
147 IF G_SRP_ID_PUB_FLAG = 'Y' THEN
148
149 -- dbms_output.put_line ('After setting the global flag in create_resource');
150
151
152 /* Calling Private API to insert salesrep */
153
154 jtf_rs_salesreps_pvt.create_salesrep(
155 P_API_VERSION => 1,
156 P_INIT_MSG_LIST => fnd_api.g_false,
157 P_COMMIT => fnd_api.g_false,
158 P_RESOURCE_ID => l_resource_id,
159 P_SALES_CREDIT_TYPE_ID => l_sales_credit_type_id,
160 P_NAME => l_name,
161 P_STATUS => l_status,
162 P_START_DATE_ACTIVE => l_start_date_active,
163 P_END_DATE_ACTIVE => l_end_date_active,
164 P_ORG_ID => l_org_id,
165 P_GL_ID_REV => l_gl_id_rev,
166 P_GL_ID_FREIGHT => l_gl_id_freight,
167 P_GL_ID_REC => l_gl_id_rec,
168 P_SET_OF_BOOKS_ID => l_set_of_books_id,
169 P_SALESREP_NUMBER => l_salesrep_number,
170 P_EMAIL_ADDRESS => l_email_address,
171 P_WH_UPDATE_DATE => l_wh_update_date,
172 P_SALES_TAX_GEOCODE => l_sales_tax_geocode,
173 P_SALES_TAX_INSIDE_CITY_LIMITS => l_sales_tax_inside_city_limits,
174 X_RETURN_STATUS => x_return_status,
175 X_MSG_COUNT => x_msg_count,
176 X_MSG_DATA => x_msg_data,
177 X_SALESREP_ID => x_salesrep_id
178 );
179 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
180 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
181 RAISE FND_API.G_EXC_ERROR;
182 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 END IF;
185 END IF;
186
187 ELSE
188
189 /* Call the private procedure for Migration. */
190 jtf_rs_salesreps_pvt.create_salesrep_migrate(
191 P_API_VERSION => 1,
192 P_INIT_MSG_LIST => fnd_api.g_false,
193 P_COMMIT => fnd_api.g_false,
194 P_RESOURCE_ID => l_resource_id,
195 P_SALES_CREDIT_TYPE_ID => l_sales_credit_type_id,
196 P_NAME => l_name,
197 P_STATUS => l_status,
198 P_START_DATE_ACTIVE => l_start_date_active,
199 P_END_DATE_ACTIVE => l_end_date_active,
200 P_GL_ID_REV => l_gl_id_rev,
201 P_GL_ID_FREIGHT => l_gl_id_freight,
202 P_GL_ID_REC => l_gl_id_rec,
203 P_SET_OF_BOOKS_ID => l_set_of_books_id,
204 P_SALESREP_NUMBER => l_salesrep_number,
205 P_EMAIL_ADDRESS => l_email_address,
206 P_WH_UPDATE_DATE => l_wh_update_date,
207 P_SALES_TAX_GEOCODE => l_sales_tax_geocode,
208 P_SALES_TAX_INSIDE_CITY_LIMITS => l_sales_tax_inside_city_limits,
209 P_SALESREP_ID => G_SALESREP_ID,
210 P_ORG_ID => G_ORG_ID,
211 P_ATTRIBUTE1 => G_ATTRIBUTE1,
212 P_ATTRIBUTE2 => G_ATTRIBUTE2,
213 P_ATTRIBUTE3 => G_ATTRIBUTE3,
214 P_ATTRIBUTE4 => G_ATTRIBUTE4,
215 P_ATTRIBUTE5 => G_ATTRIBUTE5,
216 P_ATTRIBUTE6 => G_ATTRIBUTE6,
217 P_ATTRIBUTE7 => G_ATTRIBUTE7,
218 P_ATTRIBUTE8 => G_ATTRIBUTE8,
219 P_ATTRIBUTE9 => G_ATTRIBUTE9,
220 P_ATTRIBUTE10 => G_ATTRIBUTE10,
221 P_ATTRIBUTE11 => G_ATTRIBUTE11,
222 P_ATTRIBUTE12 => G_ATTRIBUTE12,
223 P_ATTRIBUTE13 => G_ATTRIBUTE13,
224 P_ATTRIBUTE14 => G_ATTRIBUTE14,
225 P_ATTRIBUTE15 => G_ATTRIBUTE15,
226 P_ATTRIBUTE_CATEGORY => G_ATTRIBUTE_CATEGORY,
227 X_RETURN_STATUS => x_return_status,
228 X_MSG_COUNT => x_msg_count,
229 X_MSG_DATA => x_msg_data,
230 X_SALESREP_ID => x_salesrep_id
231 );
232 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
233 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
234 RAISE FND_API.G_EXC_ERROR;
235 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
237 END IF;
238
239 END IF;
240
241 END IF;
242
243 IF fnd_api.to_Boolean(p_commit) THEN
244 COMMIT WORK;
245 END IF;
246
247 /* Standard call to get message count and if count is 1, get message info. */
248 FND_MSG_PUB.Count_And_Get
249 (p_count => x_msg_count,
250 p_data => x_msg_data
251 );
252
253 EXCEPTION
254
255 WHEN fnd_api.g_exc_error THEN
256 ROLLBACK TO create_salesreps_pub;
257 x_return_status := fnd_api.g_ret_sts_error;
258 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
259 p_data => x_msg_data);
260 WHEN fnd_api.g_exc_unexpected_error THEN
261 ROLLBACK TO create_salesreps_pub;
262 x_return_status := fnd_api.g_ret_sts_unexp_error;
263 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
264 p_data => x_msg_data);
265 WHEN OTHERS THEN
266 ROLLBACK TO create_salesreps_pub;
267 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
268 fnd_message.set_token('P_SQLCODE',SQLCODE);
269 fnd_message.set_token('P_SQLERRM',SQLERRM);
270 fnd_message.set_token('P_API_NAME', l_api_name);
271 FND_MSG_PUB.add;
272 x_return_status := fnd_api.g_ret_sts_unexp_error;
273 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
274 p_data => x_msg_data);
275
276 END create_salesrep;
277
278
279 PROCEDURE create_salesrep_migrate
280 (P_API_VERSION IN NUMBER,
281 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
282 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
283 P_RESOURCE_ID IN JTF_RS_SALESREPS.RESOURCE_ID%TYPE,
284 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
285 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE DEFAULT NULL,
286 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE DEFAULT NULL,
287 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE DEFAULT SYSDATE,
288 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
289 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE DEFAULT NULL,
290 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE DEFAULT NULL,
291 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE DEFAULT NULL,
292 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL,
293 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE DEFAULT NULL,
294 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE DEFAULT NULL,
295 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE DEFAULT NULL,
296 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE DEFAULT NULL,
297 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE DEFAULT NULL,
298 P_SALESREP_ID IN JTF_RS_SALESREPS.SALESREP_ID%TYPE,
299 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE,
300 P_ATTRIBUTE_CATEGORY IN JTF_RS_SALESREPS.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
301 P_ATTRIBUTE1 IN JTF_RS_SALESREPS.ATTRIBUTE1%TYPE DEFAULT NULL,
302 P_ATTRIBUTE2 IN JTF_RS_SALESREPS.ATTRIBUTE2%TYPE DEFAULT NULL,
303 P_ATTRIBUTE3 IN JTF_RS_SALESREPS.ATTRIBUTE3%TYPE DEFAULT NULL,
304 P_ATTRIBUTE4 IN JTF_RS_SALESREPS.ATTRIBUTE4%TYPE DEFAULT NULL,
305 P_ATTRIBUTE5 IN JTF_RS_SALESREPS.ATTRIBUTE5%TYPE DEFAULT NULL,
306 P_ATTRIBUTE6 IN JTF_RS_SALESREPS.ATTRIBUTE6%TYPE DEFAULT NULL,
307 P_ATTRIBUTE7 IN JTF_RS_SALESREPS.ATTRIBUTE7%TYPE DEFAULT NULL,
308 P_ATTRIBUTE8 IN JTF_RS_SALESREPS.ATTRIBUTE8%TYPE DEFAULT NULL,
309 P_ATTRIBUTE9 IN JTF_RS_SALESREPS.ATTRIBUTE9%TYPE DEFAULT NULL,
310 P_ATTRIBUTE10 IN JTF_RS_SALESREPS.ATTRIBUTE10%TYPE DEFAULT NULL,
311 P_ATTRIBUTE11 IN JTF_RS_SALESREPS.ATTRIBUTE11%TYPE DEFAULT NULL,
312 P_ATTRIBUTE12 IN JTF_RS_SALESREPS.ATTRIBUTE12%TYPE DEFAULT NULL,
313 P_ATTRIBUTE13 IN JTF_RS_SALESREPS.ATTRIBUTE13%TYPE DEFAULT NULL,
314 P_ATTRIBUTE14 IN JTF_RS_SALESREPS.ATTRIBUTE14%TYPE DEFAULT NULL,
315 P_ATTRIBUTE15 IN JTF_RS_SALESREPS.ATTRIBUTE15%TYPE DEFAULT NULL,
316 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
317 X_MSG_COUNT OUT NOCOPY NUMBER,
318 X_MSG_DATA OUT NOCOPY VARCHAR2,
319 X_SALESREP_ID OUT NOCOPY JTF_RS_SALESREPS.SALESREP_ID%TYPE
320 ) IS
321
322 BEGIN
323
324 --dbms_output.put_line ('Inside the create_salesrep_migrate pub body');
325
326 JTF_RS_SALESREPS_PUB.G_SRP_ID_PUB_FLAG := 'N';
327 JTF_RS_SALESREPS_PUB.G_SALESREP_ID := P_SALESREP_ID;
328 JTF_RS_SALESREPS_PUB.G_ORG_ID := P_ORG_ID;
329 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE1 := P_ATTRIBUTE1;
330 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE2 := P_ATTRIBUTE2;
331 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE3 := P_ATTRIBUTE3;
332 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE4 := P_ATTRIBUTE4;
333 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE5 := P_ATTRIBUTE5;
334 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE6 := P_ATTRIBUTE6;
335 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE7 := P_ATTRIBUTE7;
336 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE8 := P_ATTRIBUTE8;
337 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE9 := P_ATTRIBUTE9;
338 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE10 := P_ATTRIBUTE10;
339 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE11 := P_ATTRIBUTE11;
340 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE12 := P_ATTRIBUTE12;
341 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE13 := P_ATTRIBUTE13;
342 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE14 := P_ATTRIBUTE14;
343 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE15 := P_ATTRIBUTE15;
344 JTF_RS_SALESREPS_PUB.G_ATTRIBUTE_CATEGORY := P_ATTRIBUTE_CATEGORY;
345
346 --dbms_output.put_line ('After assigning values to the Global variables');
347
348 jtf_rs_salesreps_pub.create_salesrep (
349 P_API_VERSION => P_API_VERSION,
350 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
351 P_COMMIT => P_COMMIT,
352 P_RESOURCE_ID => P_RESOURCE_ID,
353 P_SALES_CREDIT_TYPE_ID => P_SALES_CREDIT_TYPE_ID,
354 P_NAME => P_NAME,
355 P_STATUS => P_STATUS,
356 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
357 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
358 P_GL_ID_REV => P_GL_ID_REV,
359 P_GL_ID_FREIGHT => P_GL_ID_FREIGHT,
360 P_GL_ID_REC => P_GL_ID_REC,
361 P_SET_OF_BOOKS_ID => P_SET_OF_BOOKS_ID,
362 P_SALESREP_NUMBER => P_SALESREP_NUMBER,
363 P_EMAIL_ADDRESS => P_EMAIL_ADDRESS,
364 P_WH_UPDATE_DATE => P_WH_UPDATE_DATE,
365 P_SALES_TAX_GEOCODE => P_SALES_TAX_GEOCODE,
366 P_SALES_TAX_INSIDE_CITY_LIMITS => P_SALES_TAX_INSIDE_CITY_LIMITS,
367 X_RETURN_STATUS => X_RETURN_STATUS,
368 X_MSG_COUNT => X_MSG_COUNT,
369 X_MSG_DATA => X_MSG_DATA,
370 X_SALESREP_ID => X_SALESREP_ID
371 );
372
373 END create_salesrep_migrate;
374
375 /* Procedure to update the Salesreps
376 based on input values passed by calling routines. */
377
378 PROCEDURE update_salesrep
379 (P_API_VERSION IN NUMBER,
380 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
381 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
382 P_SALESREP_ID IN JTF_RS_SALESREPS.SALESREP_ID%TYPE,
383 P_SALES_CREDIT_TYPE_ID IN JTF_RS_SALESREPS.SALES_CREDIT_TYPE_ID%TYPE,
384 P_NAME IN JTF_RS_SALESREPS.NAME%TYPE DEFAULT FND_API.G_MISS_CHAR,
385 P_STATUS IN JTF_RS_SALESREPS.STATUS%TYPE DEFAULT FND_API.G_MISS_CHAR,
386 P_START_DATE_ACTIVE IN JTF_RS_SALESREPS.START_DATE_ACTIVE%TYPE DEFAULT FND_API.G_MISS_DATE,
387 P_END_DATE_ACTIVE IN JTF_RS_SALESREPS.END_DATE_ACTIVE%TYPE DEFAULT FND_API.G_MISS_DATE,
388 P_GL_ID_REV IN JTF_RS_SALESREPS.GL_ID_REV%TYPE DEFAULT FND_API.G_MISS_NUM,
389 P_GL_ID_FREIGHT IN JTF_RS_SALESREPS.GL_ID_FREIGHT%TYPE DEFAULT FND_API.G_MISS_NUM,
390 P_GL_ID_REC IN JTF_RS_SALESREPS.GL_ID_REC%TYPE DEFAULT FND_API.G_MISS_NUM,
391 P_SET_OF_BOOKS_ID IN JTF_RS_SALESREPS.SET_OF_BOOKS_ID%TYPE DEFAULT FND_API.G_MISS_NUM,
392 P_SALESREP_NUMBER IN JTF_RS_SALESREPS.SALESREP_NUMBER%TYPE DEFAULT FND_API.G_MISS_CHAR,
393 P_EMAIL_ADDRESS IN JTF_RS_SALESREPS.EMAIL_ADDRESS%TYPE DEFAULT FND_API.G_MISS_CHAR,
394 P_WH_UPDATE_DATE IN JTF_RS_SALESREPS.WH_UPDATE_DATE%TYPE DEFAULT FND_API.G_MISS_DATE,
395 P_SALES_TAX_GEOCODE IN JTF_RS_SALESREPS.SALES_TAX_GEOCODE%TYPE DEFAULT FND_API.G_MISS_CHAR,
396 P_SALES_TAX_INSIDE_CITY_LIMITS IN JTF_RS_SALESREPS.SALES_TAX_INSIDE_CITY_LIMITS%TYPE DEFAULT FND_API.G_MISS_CHAR,
397 P_ORG_ID IN JTF_RS_SALESREPS.ORG_ID%TYPE,
398 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_SALESREPS.OBJECT_VERSION_NUMBER%TYPE,
399 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
400 X_MSG_COUNT OUT NOCOPY NUMBER,
401 X_MSG_DATA OUT NOCOPY VARCHAR2
402 ) IS
403
404 l_api_version CONSTANT NUMBER := 1.0;
405 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SALESREP';
406 l_salesrep_id jtf_rs_salesreps.salesrep_id%type := p_salesrep_id;
407 l_sales_credit_type_id jtf_rs_salesreps.sales_credit_type_id%type := p_sales_credit_type_id;
408 l_name jtf_rs_salesreps.name%type := p_name;
409 l_status jtf_rs_salesreps.status%type := p_status;
410 l_start_date_active jtf_rs_salesreps.start_date_active%type := p_start_date_active;
411 l_end_date_active jtf_rs_salesreps.end_date_active%type := p_end_date_active;
412 l_gl_id_rev jtf_rs_salesreps.gl_id_rev%type := p_gl_id_rev;
413 l_gl_id_freight jtf_rs_salesreps.gl_id_freight%type := p_gl_id_freight;
414 l_gl_id_rec jtf_rs_salesreps.gl_id_rec%type := p_gl_id_rec;
415 l_set_of_books_id jtf_rs_salesreps.set_of_books_id%type := p_set_of_books_id;
416 l_salesrep_number jtf_rs_salesreps.salesrep_number%type := p_salesrep_number;
417 l_email_address jtf_rs_salesreps.email_address%type := p_email_address;
418 l_wh_update_date jtf_rs_salesreps.wh_update_date%type := p_wh_update_date;
419 l_sales_tax_geocode jtf_rs_salesreps.sales_tax_geocode%type := p_sales_tax_geocode;
420 l_sales_tax_inside_city_limits jtf_rs_salesreps.sales_tax_inside_city_limits%type := p_sales_tax_inside_city_limits;
421 l_org_id jtf_rs_salesreps.org_id%type := p_org_id;
422 l_object_version_number jtf_rs_salesreps.object_version_number%type := p_object_version_number;
423
424
425 BEGIN
426
427 SAVEPOINT update_salesreps_pub;
428
429 x_return_status := fnd_api.g_ret_sts_success;
430
431 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
432 RAISE fnd_api.g_exc_unexpected_error;
433 END IF;
434
435 IF fnd_api.to_boolean(p_init_msg_list) THEN
436 fnd_msg_pub.initialize;
437 END IF;
438
439
440 /* Validate Salesrep */
441
442 jtf_resource_utl.validate_salesrep_id(
443 p_salesrep_id => l_salesrep_id,
444 p_org_id => l_org_id,
445 x_return_status => x_return_status
446 );
447
448 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
449 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
450 RAISE FND_API.G_EXC_ERROR;
451 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453 END IF;
454 END IF;
455
456 /* Validate Sales Credit type Id */
457
458 jtf_resource_utl.validate_sales_credit_type(
459 p_sales_credit_type_id => l_sales_credit_type_id,
460 x_return_status => x_return_status
461 );
462
463 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
464 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
465 RAISE FND_API.G_EXC_ERROR;
466 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
468 END IF;
469 END IF;
470
471 /* Validate start date */
472 /*
473 IF l_start_date_active IS NULL THEN
474 x_return_status := fnd_api.g_ret_sts_unexp_error;
475 RAISE fnd_api.g_exc_unexpected_error;
476 END IF;
477 */
478
479 /*
480 Validate Salesrep dates
481 Created by Nishant on 17-Oct-2005 to fix bug 4354269. It will validate both
482 start date and end date against resource start date and end date.
483 */
484 jtf_resource_utl.validate_salesrep_dates
485 (P_ID => l_salesrep_id,
486 P_ORG_ID => l_org_id,
487 P_SRP_START_DATE => l_start_date_active,
488 P_SRP_END_DATE => l_end_date_active,
489 P_CR_UPD_MODE => 'U',
490 X_RETURN_STATUS => x_return_status);
491
492 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495
496 /* Calling Private API to insert salesrep */
497 jtf_rs_salesreps_pvt.update_salesrep(
498 P_API_VERSION => 1,
499 P_INIT_MSG_LIST => fnd_api.g_false,
500 P_COMMIT => fnd_api.g_false,
501 P_SALESREP_ID => l_salesrep_id,
502 P_SALES_CREDIT_TYPE_ID => l_sales_credit_type_id,
503 P_NAME => l_name,
504 P_STATUS => l_status,
505 P_START_DATE_ACTIVE => l_start_date_active,
506 P_END_DATE_ACTIVE => l_end_date_active,
507 P_GL_ID_REV => l_gl_id_rev,
508 P_GL_ID_FREIGHT => l_gl_id_freight,
509 P_GL_ID_REC => l_gl_id_rec,
510 P_SET_OF_BOOKS_ID => l_set_of_books_id,
511 P_SALESREP_NUMBER => l_salesrep_number,
512 P_EMAIL_ADDRESS => l_email_address,
513 P_WH_UPDATE_DATE => l_wh_update_date,
514 P_SALES_TAX_GEOCODE => l_sales_tax_geocode,
515 P_SALES_TAX_INSIDE_CITY_LIMITS => l_sales_tax_inside_city_limits,
516 P_ORG_ID => l_org_id,
517 P_OBJECT_VERSION_NUMBER => l_object_version_number,
518 X_RETURN_STATUS => x_return_status,
519 X_MSG_COUNT => x_msg_count,
520 X_MSG_DATA => x_msg_data
521 );
522
523 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
524 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
525 RAISE FND_API.G_EXC_ERROR;
526 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
528 END IF;
529 END IF;
530
531 IF fnd_api.to_Boolean(p_commit) THEN
532 COMMIT WORK;
533 END IF;
534
535 /* Standard call to get message count and if count is 1, get message info. */
536 FND_MSG_PUB.Count_And_Get
537 (p_count => x_msg_count,
538 p_data => x_msg_data
539 );
540
541 EXCEPTION
542
543 WHEN fnd_api.g_exc_error THEN
544 ROLLBACK TO update_salesreps_pub;
545 x_return_status := fnd_api.g_ret_sts_error;
546 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
547 p_data => x_msg_data);
548 WHEN fnd_api.g_exc_unexpected_error THEN
549 ROLLBACK TO update_salesreps_pub;
550 x_return_status := fnd_api.g_ret_sts_unexp_error;
551 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
552 p_data => x_msg_data);
553 WHEN OTHERS THEN
554 ROLLBACK TO update_salesreps_pub;
555 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
556 fnd_message.set_token('P_SQLCODE',SQLCODE);
557 fnd_message.set_token('P_SQLERRM',SQLERRM);
558 fnd_message.set_token('P_API_NAME', l_api_name);
559 FND_MSG_PUB.add;
560 x_return_status := fnd_api.g_ret_sts_unexp_error;
561 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
562 p_data => x_msg_data);
563
564 END update_salesrep;
565
566
567 END JTF_RS_SALESREPS_PUB;