1 PACKAGE BODY as_status_pub AS
2 /* $Header: asxpstab.pls 115.5 2003/01/28 23:10:11 geliu ship $ */
3
4 -- Declare Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AS_STATUSES_PUB';
6
7 -- Start of Comments
8 --
9 -- API Name : create_status
10 -- Type : Public
11 -- Function : To create status codes using the table handler
12 -- Pre-Reqs : The table handler API AS_STATUSES_PKG.INSERT_ROW
13 -- should exist
14 -- Parameters :
15 -- IN : p_api_version_number IN NUMBER
16 -- p_init_msg_list IN VARCHAR2
17 -- p_commit IN VARCHAR2
18 -- p_validation_level IN NUMBER
19 -- p_status_rec IN STATUS_Rec_Type
20 -- OUT : x_return_status OUT VARCHAR2
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2
23 -- Version : 2.0
24 -- Purpose : To create status codes in as_statuses_b,_tl,_vl tables
25 -- Notes : This procedure is a public procedure called using the
26 -- public API as_status_pub to create status codes.
27 --
28 -- End of Comments :
29
30 PROCEDURE create_status (
31 p_api_version_number IN NUMBER,
32 p_init_msg_list IN VARCHAR2 ,
33 p_commit IN VARCHAR2 ,
34 p_validation_level IN NUMBER ,
35 p_status_rec IN STATUS_Rec_Type ,
36 x_return_status OUT VARCHAR2,
37 x_msg_count OUT NUMBER,
38 x_msg_data OUT VARCHAR2)
39 IS
40 -- Declaration of local variables and cursors
41 l_api_version NUMBER := p_api_version_number;
42 l_api_name CONSTANT VARCHAR(30) DEFAULT 'CREATE_STATUS';
43 l_count NUMBER;
44 l_row_id VARCHAR2(2000) DEFAULT ' ';
45 l_creation_date DATE ;
46 l_created_by NUMBER ;
47 l_last_update_date DATE ;
48 l_last_updated_by NUMBER ;
49 l_last_update_login NUMBER ;
50 l_status_rank NUMBER ;
51 l_attribute_category VARCHAR2(30) ;
52 l_attribute1 VARCHAR2(150) ;
53 l_attribute2 VARCHAR2(150) ;
54 l_attribute3 VARCHAR2(150) ;
55 l_attribute4 VARCHAR2(150) ;
56 l_attribute5 VARCHAR2(150) ;
57 l_attribute6 VARCHAR2(150) ;
58 l_attribute7 VARCHAR2(150) ;
59 l_attribute8 VARCHAR2(150) ;
60 l_attribute9 VARCHAR2(150) ;
61 l_attribute10 VARCHAR2(150) ;
62 l_attribute11 VARCHAR2(150) ;
63 l_attribute12 VARCHAR2(150) ;
64 l_attribute13 VARCHAR2(150) ;
65 l_attribute14 VARCHAR2(150) ;
66 l_attribute15 VARCHAR2(150) ;
67 l_description VARCHAR2(240) ;
68
69 -- Cursor for checking duplicate status code
70 CURSOR status_dup_cur(p_status_code IN VARCHAR2) IS
71 SELECT 1
72 FROM as_statuses_vl
73 WHERE TRIM(NLS_UPPER(status_code)) = p_status_code; -- trimmed value passed while opening
74
75 BEGIN
76 -- Standard start of api save point
77 SAVEPOINT create_status;
78
79 -- Initialize Message List
80 IF fnd_api.to_boolean (p_init_msg_list)
81 THEN
82 fnd_msg_pub.initialize;
83 END IF;
84
85 -- Standard Call to check api compatibility
86 IF NOT fnd_api.compatible_api_call (
87 l_api_version,
88 p_api_version_number,
89 l_api_name,
90 G_PKG_NAME
91 )
92 THEN
93 fnd_message.set_name('AS', 'AS_INVALID_VERSION');
94 fnd_msg_pub.add;
95 END IF;
96
97 -- Initialize api return status
98 x_return_status := fnd_api.g_ret_sts_success;
99
100 -- Check if the version number passed is 2.0 else error out
101 IF p_api_version_number <> 2.0
102 THEN
103 fnd_message.set_name('AS', 'AS_INVALID_VERSION');
104 fnd_msg_pub.add;
105 END IF;
106
107
108 -- API Body
109 -- Begin Validation Section
110 -- Check for valid values being passed.
111
112 IF (p_status_rec.enabled_flag NOT IN ('Y','N')
113 OR p_status_rec.enabled_flag = FND_API.G_MISS_CHAR
114 OR TRIM(p_status_rec.enabled_flag) IS NULL)
115 THEN
116 fnd_message.set_name('AS', 'AS_ENABLED_FLAG_INVALID');
117 fnd_msg_pub.add;
118 END IF;
119 IF (p_status_rec.lead_flag NOT IN ('Y','N')
120 OR p_status_rec.lead_flag = FND_API.G_MISS_CHAR
121 OR TRIM(p_status_rec.lead_flag) IS NULL)
122 THEN
123 fnd_message.set_name('AS', 'AS_LEAD_FLAG_INVALID');
124 fnd_msg_pub.add;
125 END IF;
126 IF (p_status_rec.opp_flag NOT IN ('Y','N')
127 OR p_status_rec.opp_flag = FND_API.G_MISS_CHAR
128 OR TRIM(p_status_rec.opp_flag) IS NULL)
129 THEN
130 fnd_message.set_name('AS', 'AS_OPP_FLAG_INVALID');
131 fnd_msg_pub.add;
132 END IF;
133 IF (p_status_rec.opp_open_status_flag NOT IN ('Y','N')
134 OR TRIM(p_status_rec.opp_open_status_flag) = FND_API.G_MISS_CHAR
135 OR TRIM(p_status_rec.opp_open_status_flag) IS NULL)
136 THEN
137 fnd_message.set_name('AS', 'AS_OPP_OPEN_FLAG_INVALID');
138 fnd_msg_pub.add;
139 END IF;
140 IF (p_status_rec.opp_decision_date_flag NOT IN ('Y','N')
141 OR p_status_rec.opp_decision_date_flag = FND_API.G_MISS_CHAR
142 OR TRIM(p_status_rec.opp_decision_date_flag) IS NULL)
143 THEN
144 fnd_message.set_name('AS', 'AS_DECISION_FLAG_INVALID');
145 fnd_msg_pub.add;
146 END IF;
147 IF (p_status_rec.forecast_rollup_flag NOT IN ('Y','N')
148 OR p_status_rec.forecast_rollup_flag = FND_API.G_MISS_CHAR
149 OR TRIM(p_status_rec.forecast_rollup_flag) IS NULL)
150 THEN
151 fnd_message.set_name('AS', 'AS_FORECAST_FLAG_INVALID');
152 fnd_msg_pub.add;
153 END IF;
154
155 IF (p_status_rec.win_loss_indicator NOT IN ('W','L')
156 OR p_status_rec.win_loss_indicator= FND_API.G_MISS_CHAR
157 OR TRIM(p_status_rec.win_loss_indicator) IS NULL)
158 THEN
159 fnd_message.set_name('AS', 'AS_WIN_IND_INVALID');
160 fnd_msg_pub.add;
161 END IF;
162
163 -- Check if the required field Meaning is passed
164 IF (p_status_rec.meaning = FND_API.G_MISS_CHAR
165 OR TRIM(p_status_rec.meaning) IS NULL)
166 THEN
167 fnd_message.set_name('AS', 'AS_MEANING');
168 fnd_msg_pub.add;
169 END IF;
170
171 -- End Validation Section
172
173 -- Check for duplicate or uniqueness of the status code
174 -- Open the cursor and fetch the record.
175
176 OPEN status_dup_cur(TRIM(NLS_UPPER(p_status_rec.status_code)));
177 FETCH status_dup_cur INTO l_count;
178 IF (status_dup_cur%FOUND)
179 THEN
180 fnd_message.set_name('AS', 'AS_DUPLICATE_STATUS_CODE');
181 fnd_message.set_token('STATUS_CODE', p_status_rec.status_code);
182 fnd_msg_pub.add;
183 CLOSE status_dup_cur;
184 END IF;
185 CLOSE status_dup_cur;
186
187 -- Check if the Who columns have any values otherwise default them.
188
189 IF (p_status_rec.creation_date = FND_API.G_MISS_DATE)
190 OR TRIM(p_status_rec.creation_date) IS NULL
191 THEN
192 l_creation_date := sysdate;
193 ELSE
194 l_creation_date := p_status_rec.creation_date ;
195 END IF;
196 IF (p_status_rec.created_by = FND_API.G_MISS_NUM)
197 OR TRIM(p_status_rec.created_by) IS NULL
198 THEN
199 l_created_by := fnd_global.user_id;
200 ELSE
201 l_created_by := p_status_rec.created_by ;
202 END IF;
203 IF (p_status_rec.last_update_date = FND_API.G_MISS_DATE)
204 OR TRIM(p_status_rec.last_update_date) IS NULL
205 THEN
206 l_last_update_date := sysdate;
207 ELSE
208 l_last_update_date := p_status_rec.last_update_date;
209 END IF;
210 IF (p_status_rec.last_update_login = FND_API.G_MISS_NUM)
211 OR TRIM(p_status_rec.last_update_login) IS NULL
212 THEN
213 l_last_update_login := fnd_global.login_id;
214 ELSE
215 l_last_update_login := p_status_rec.last_update_login;
216 END IF;
217 IF (p_status_rec.last_updated_by = FND_API.G_MISS_NUM)
218 OR TRIM(p_status_rec.last_updated_by) IS NULL
219 THEN
220 l_last_updated_by := fnd_global.user_id;
221 ELSE
222 l_last_updated_by := p_status_rec.last_updated_by ;
223 END IF;
224
225 -- Check all optional fields if they have g_miss values then
226 -- replace those with null before inserting.
227
228 IF p_status_rec.status_rank = FND_API.G_MISS_NUM
229 THEN
230 l_status_rank := NULL;
231 ELSE
232 l_status_rank := p_status_rec.status_rank;
233 END IF;
234 IF p_status_rec.attribute_category = FND_API.G_MISS_CHAR
235 THEN
236 l_attribute_category := NULL;
237 ELSE
238 l_attribute_category :=p_status_rec.attribute_category;
239 END IF;
240 IF p_status_rec.attribute1 = FND_API.G_MISS_CHAR
241 THEN
242 l_attribute1 := NULL;
243 ELSE
244 l_attribute1 := p_status_rec.attribute1 ;
245 END IF;
246 IF p_status_rec.attribute2 = FND_API.G_MISS_CHAR
247 THEN
248 l_attribute2 := NULL;
249 ELSE
250 l_attribute2 := p_status_rec.attribute2 ;
251 END IF;
252 IF p_status_rec.attribute3 = FND_API.G_MISS_CHAR
253 THEN
254 l_attribute3 := NULL;
255 ELSE
256 l_attribute3 := p_status_rec.attribute3 ;
257 END IF;
258 IF p_status_rec.attribute4 = FND_API.G_MISS_CHAR
259 THEN
260 l_attribute4 := NULL;
261 ELSE
262 l_attribute4 := p_status_rec.attribute4 ;
263 END IF;
264 IF p_status_rec.attribute5 = FND_API.G_MISS_CHAR
265 THEN
266 l_attribute5 := NULL;
267 ELSE
268 l_attribute5 := p_status_rec.attribute5 ;
269 END IF;
270 IF p_status_rec.attribute6 = FND_API.G_MISS_CHAR
271 THEN
272 l_attribute6 := NULL;
273 ELSE
274 l_attribute6 := p_status_rec.attribute6 ;
275 END IF;
276 IF p_status_rec.attribute7 = FND_API.G_MISS_CHAR
277 THEN
278 l_attribute7 := NULL;
279 ELSE
280 l_attribute7 := p_status_rec.attribute7 ;
281 END IF;
282 IF p_status_rec.attribute8 = FND_API.G_MISS_CHAR
283 THEN
284 l_attribute8 := NULL;
285 ELSE
286 l_attribute8 := p_status_rec.attribute8 ;
287 END IF;
288 IF p_status_rec.attribute9 = FND_API.G_MISS_CHAR
289 THEN
290 l_attribute9 := NULL;
291 ELSE
292 l_attribute9 := p_status_rec.attribute9 ;
293 END IF;
294 IF p_status_rec.attribute10 = FND_API.G_MISS_CHAR
295 THEN
296 l_attribute10 := NULL;
297 ELSE
298 l_attribute10 := p_status_rec.attribute10 ;
299 END IF;
300 IF p_status_rec.attribute11 = FND_API.G_MISS_CHAR
301 THEN
302 l_attribute11 := NULL;
303 ELSE
304 l_attribute11 := p_status_rec.attribute11 ;
305 END IF;
306 IF p_status_rec.attribute12 = FND_API.G_MISS_CHAR
307 THEN
308 l_attribute12 := NULL;
309 ELSE
310 l_attribute12 := p_status_rec.attribute12 ;
311 END IF;
312 IF p_status_rec.attribute13 = FND_API.G_MISS_CHAR
313 THEN
314 l_attribute13 := NULL;
315 ELSE
316 l_attribute13 := p_status_rec.attribute13 ;
317 END IF;
318 IF p_status_rec.attribute14 = FND_API.G_MISS_CHAR
319 THEN
320 l_attribute14 := NULL;
321 ELSE
322 l_attribute14 := p_status_rec.attribute14 ;
323 END IF;
324 IF p_status_rec.attribute15 = FND_API.G_MISS_CHAR
325 THEN
326 l_attribute15 := NULL;
327 ELSE
328 l_attribute15 := p_status_rec.attribute15 ;
329 END IF;
330 IF p_status_rec.description = FND_API.G_MISS_CHAR
331 THEN
332 l_description := NULL;
333 ELSE
334 l_description := p_status_rec.description;
335 END IF;
336
337 -- END OF CHECK OF ALL OPTIONAL FIELD FOR g_miss_values
338
339 -- Check for open_status and win_loss validations
340 -- Perform this check before every insert of the status code
341 -- if opp_open_status_flag is Y and win_loss_indicator is NULL or
342 -- if opp_open_status_flag is N and win_loss_indicator is not in W or L
343 -- then insert into as_statuses_b and as_statuses_tl using api.
344 -- else error out with a message.
345
346 IF NOT ((p_status_rec.opp_open_status_flag = 'Y' AND
347 p_status_rec.win_loss_indicator IS NULL)
348 OR (p_status_rec.opp_open_status_flag = 'N' AND
349 p_status_rec.win_loss_indicator IN ('W','L')))
350 THEN
351 fnd_message.set_name('AS', 'AS_INVALID_WL_STATUS_COMBO');
352 fnd_msg_pub.add;
353 END IF;
354
355 IF (FND_MSG_PUB.COUNT_MSG > 0)
356 THEN
357 fnd_message.set_name('AS', 'AS_STATUS_INSERT_FAILED');
358 fnd_msg_pub.add;
359 RAISE fnd_api.g_exc_error;
360 END IF;
361
362
363 -- Insert into as_statuses_b and as_statuses_tl using api
364 -- AS_STATUSES_PKG.INSERT_ROW
365
366 AS_STATUSES_PKG.INSERT_ROW(
367 l_row_id,
368 p_status_rec.status_code,
369 p_status_rec.enabled_flag,
370 p_status_rec.lead_flag,
371 p_status_rec.opp_flag,
372 p_status_rec.opp_open_status_flag,
373 p_status_rec.opp_decision_date_flag,
374 p_status_rec.status_rank,
375 p_status_rec.forecast_rollup_flag,
376 p_status_rec.win_loss_indicator,
377 NULL,
378 l_attribute_category,
379 l_attribute1,
380 l_attribute2,
381 l_attribute3,
382 l_attribute4,
383 l_attribute5,
384 l_attribute6,
385 l_attribute7,
386 l_attribute8,
387 l_attribute9,
388 l_attribute10,
389 l_attribute11,
390 l_attribute12,
391 l_attribute13,
392 l_attribute14,
393 l_attribute15,
394 p_status_rec.meaning,
395 l_description,
396 l_creation_date,
397 l_created_by,
398 l_last_update_date,
399 l_last_updated_by,
400 l_last_update_login);
401
402 -- Standard Check for p_commit
403 IF fnd_api.to_boolean (p_commit)
404 THEN
405 COMMIT WORK;
406 END IF;
407
408 -- Standard call to get message count and if count is 1, get message infor.
409 fnd_msg_pub.count_and_get(
410 p_count => x_msg_count,
411 p_data => x_msg_data
412 );
413
414 -- Handling all the exceptions
415 EXCEPTION
416 WHEN fnd_api.g_exc_error THEN
417 ROLLBACK TO create_status;
418 x_return_status := fnd_api.g_ret_sts_error;
419 fnd_msg_pub.count_and_get (
420 p_count => x_msg_count,
421 p_data => x_msg_data
422 );
423 WHEN fnd_api.g_exc_unexpected_error THEN
424 ROLLBACK TO create_status;
425 x_return_status := fnd_api.g_ret_sts_unexp_error;
426 fnd_msg_pub.count_and_get (
427 p_count => x_msg_count,
428 p_data => x_msg_data
429 );
430 WHEN OTHERS THEN
431 ROLLBACK TO create_status;
432 x_return_status := fnd_api.g_ret_sts_unexp_error;
433 fnd_msg_pub.count_and_get (
434 p_count => x_msg_count,
435 p_data => x_msg_data
436 );
437 END create_status;
438
439 -- Start of Comments
440 --
441 -- API Name : update_status
442 -- Type : Public
443 -- Function : To update the status code using the table handler
444 -- Pre-Reqs : Table Handler AS_STATUSES_PKG.UPDATE_ROW should exist
445 -- Parameters :
446 -- IN : p_api_version_number IN NUMBER
447 -- p_init_msg_list IN VARCHAR2
448 -- p_commit IN VARCHAR2
449 -- p_validation_level IN NUMBER
450 -- p_status_rec IN STATUS_Rec_Type
451 -- OUT : x_return_status OUT VARCHAR2
452 -- x_msg_count OUT NUMBER
453 -- x_msg_data OUT VARCHAR2
454 -- Version : 2.0
455 -- Purpose : To update the status codes in as_statuses_b,tl,vl table
456 -- Notes : This procedure is a public procedure called using the
457 -- public API as_status_pub to update status codes.
458 --
459 -- End of Comments :
460
461 PROCEDURE update_status (
462 p_api_version_number IN NUMBER,
463 p_init_msg_list IN VARCHAR2 ,
464 p_commit IN VARCHAR2 ,
465 p_validation_level IN NUMBER,
466 p_status_rec IN STATUS_Rec_Type ,
467 x_return_status OUT VARCHAR2,
468 x_msg_count OUT NUMBER,
469 x_msg_data OUT VARCHAR2)
470
471 IS
472 -- Declaration of local variables and cursors
473 l_api_version NUMBER:= p_api_version_number;
474 l_api_name CONSTANT VARCHAR(30) DEFAULT 'UPDATE_STATUS';
475
476 l_enabled_flag VARCHAR2(1) ;
477 l_lead_flag VARCHAR2(1) ;
478 l_opp_flag VARCHAR2(1) ;
479 l_opp_open_status_flag VARCHAR2(1) ;
480 l_opp_decision_date_flag VARCHAR2(1) ;
481 l_forecast_rollup_flag VARCHAR2(1) ;
482 l_win_loss_indicator VARCHAR2(1) ;
483
484 v_enabled_flag VARCHAR2(1) ;
485 v_lead_flag VARCHAR2(1) ;
486 v_opp_flag VARCHAR2(1) ;
487 v_opp_open_status_flag VARCHAR2(1) ;
488 v_opp_decision_date_flag VARCHAR2(1) ;
489 v_forecast_rollup_flag VARCHAR2(1) ;
490 v_win_loss_indicator VARCHAR2(1) ;
491 l_last_update_date DATE ;
492 v_last_update_date DATE ;
493 l_last_updated_by NUMBER ;
494 l_last_update_login NUMBER ;
495 l_status_rank NUMBER ;
496 l_attribute_category VARCHAR2(30) ;
497 l_attribute1 VARCHAR2(150) ;
498 l_attribute2 VARCHAR2(150) ;
499 l_attribute3 VARCHAR2(150) ;
500 l_attribute4 VARCHAR2(150) ;
501 l_attribute5 VARCHAR2(150) ;
502 l_attribute6 VARCHAR2(150) ;
503 l_attribute7 VARCHAR2(150) ;
504 l_attribute8 VARCHAR2(150) ;
505 l_attribute9 VARCHAR2(150) ;
506 l_attribute10 VARCHAR2(150) ;
507 l_attribute11 VARCHAR2(150) ;
508 l_attribute12 VARCHAR2(150) ;
509 l_attribute13 VARCHAR2(150) ;
510 l_attribute14 VARCHAR2(150) ;
511 l_attribute15 VARCHAR2(150) ;
512 l_meaning VARCHAR2(240) ;
513 l_description VARCHAR2(240) ;
514 v_status_rank NUMBER ;
515 v_attribute_category VARCHAR2(30) ;
516 v_attribute1 VARCHAR2(150) ;
517 v_attribute2 VARCHAR2(150) ;
518 v_attribute3 VARCHAR2(150) ;
519 v_attribute4 VARCHAR2(150) ;
520 v_attribute5 VARCHAR2(150) ;
521 v_attribute6 VARCHAR2(150) ;
522 v_attribute7 VARCHAR2(150) ;
523 v_attribute8 VARCHAR2(150) ;
524 v_attribute9 VARCHAR2(150) ;
525 v_attribute10 VARCHAR2(150) ;
526 v_attribute11 VARCHAR2(150) ;
527 v_attribute12 VARCHAR2(150) ;
528 v_attribute13 VARCHAR2(150) ;
529 v_attribute14 VARCHAR2(150) ;
530 v_attribute15 VARCHAR2(150) ;
531 v_meaning VARCHAR2(240) ;
532 v_description VARCHAR2(240) ;
533 l_current_last_update_date DATE;
534
535
536 CURSOR get_update_row_cur(p_status_code in VARCHAR2) IS
537 SELECT last_update_date,
538 enabled_flag,
539 lead_flag,
540 opp_flag,
541 opp_open_status_flag ,
542 opp_decision_date_flag,
543 forecast_rollup_flag,
544 win_loss_indicator,
545 attribute_category,
546 attribute1,
547 attribute2,
548 attribute3,
549 attribute4,
550 attribute5,
551 attribute6,
552 attribute7,
553 attribute8,
554 attribute9,
555 attribute10,
556 attribute11,
557 attribute12,
558 attribute13,
559 attribute14,
560 attribute15,
561 meaning,
562 description,
563 status_rank
564 FROM as_statuses_vl
565 WHERE TRIM(NLS_UPPER(status_code)) = p_status_code; -- trimmed value passed while opening
566
567 CURSOR lock_row_for_update(p_status_code in VARCHAR2) IS
568 SELECT last_update_date
569 FROM as_statuses_vl
570 WHERE TRIM(NLS_UPPER(status_code)) = p_status_code; -- trimmed value passed while opening
571
572
573
574 BEGIN
575
576 -- Standard start of api save point
577 SAVEPOINT update_status;
578
579 -- Initialize Message List
580 IF fnd_api.to_boolean (p_init_msg_list)
581 THEN
582 fnd_msg_pub.initialize;
583 END IF;
584
585 -- Standard Call to check api compatibility
586 IF NOT fnd_api.compatible_api_call (
587 l_api_version,
588 p_api_version_number,
589 l_api_name,
590 G_PKG_NAME
591 )
592 THEN
593 fnd_message.set_name('AS', 'AS_INVALID_VERSION');
594 fnd_msg_pub.add;
595 END IF;
596
597 -- Initialize api return status
598 x_return_status := fnd_api.g_ret_sts_success;
599
600 -- Check if the version number passed is 2.0 else error out
601 IF p_api_version_number <> 2.0
602 THEN
603 fnd_message.set_name('AS', 'AS_INVALID_VERSION');
604 fnd_msg_pub.add;
605 END IF;
606
607
608 -- API Body
609 -- Fetch all the column values into local variables from the database before
610 -- checking to see if the value passed in the input paramater was G_MISS or not.
611 -- If the input parameter was G_MISS then replace the value with the fetched value
612 -- from the database
613
614 OPEN get_update_row_cur(TRIM(NLS_UPPER(p_status_rec.status_code)));
615 FETCH get_update_row_cur
616 INTO l_last_update_date,
617 l_enabled_flag,
618 l_lead_flag,
619 l_opp_flag,
620 l_opp_open_status_flag ,
621 l_opp_decision_date_flag,
622 l_forecast_rollup_flag,
623 l_win_loss_indicator,
624 l_attribute_category,
625 l_attribute1,
626 l_attribute2,
627 l_attribute3,
628 l_attribute4,
629 l_attribute5,
630 l_attribute6,
631 l_attribute7,
632 l_attribute8,
633 l_attribute9,
634 l_attribute10,
635 l_attribute11,
636 l_attribute12,
637 l_attribute13,
638 l_attribute14,
639 l_attribute15,
640 l_meaning,
641 l_description,
642 l_status_rank;
643
644 IF get_update_row_cur%NOTFOUND
645 THEN
646 CLOSE get_update_row_cur;
647 RAISE fnd_api.g_exc_unexpected_error;
648 END IF;
649
650 -- checking for G_MISS..and replace with original values if necessary
651 -- Validation for open_status_flag and win loss indicator needs to be done here..
652
653 IF p_status_rec.opp_open_status_flag = FND_API.G_MISS_CHAR
654 THEN
655 v_opp_open_status_flag := l_opp_open_status_flag;
656 ELSE
657 IF p_status_rec.opp_open_status_flag = 'Y'
658 THEN
659 v_opp_open_status_flag := p_status_rec.opp_open_status_flag ;
660 v_win_loss_indicator := NULL;
661 ELSIF p_status_rec.opp_open_status_flag = 'N'
662 THEN
663 v_opp_open_status_flag := p_status_rec.opp_open_status_flag ;
664 IF p_status_rec.win_loss_indicator = FND_API.G_MISS_CHAR
665 OR TRIM(p_status_rec.win_loss_indicator) IS NULL
666 THEN
667 v_win_loss_indicator := l_win_loss_indicator;
668 ELSIF v_win_loss_indicator NOT IN ('W','L')
669 AND v_win_loss_indicator IS NOT NULL
670 THEN
671 fnd_message.set_name('AS', 'AS_WIN_IND_INVALID');
672 fnd_msg_pub.add;
673 ELSE
674 v_win_loss_indicator := p_status_rec.win_loss_indicator;
675 END IF;
676 ELSE
677 fnd_message.set_name('AS', 'AS_OPP_OPEN_FLAG_INVALID');
678 fnd_msg_pub.add;
679 END IF;
680 END IF;
681
682 IF p_status_rec.enabled_flag = FND_API.G_MISS_CHAR
683 THEN
684 v_enabled_flag := l_enabled_flag;
685 ELSE
686 v_enabled_flag := p_status_rec.enabled_flag ;
687 END IF;
688 IF p_status_rec.lead_flag = FND_API.G_MISS_CHAR
689 THEN
690 v_lead_flag := l_lead_flag ;
691 ELSE
692 v_lead_flag := p_status_rec.lead_flag ;
693 END IF;
694 IF p_status_rec.opp_flag = FND_API.G_MISS_CHAR
695 THEN
696 v_opp_flag := l_opp_flag ;
697 ELSE
698 v_opp_flag := p_status_rec.opp_flag;
699 END IF;
700 IF p_status_rec.opp_decision_date_flag = FND_API.G_MISS_CHAR
701 THEN
702 v_opp_decision_date_flag := l_opp_decision_date_flag ;
703 ELSE
704 v_opp_decision_date_flag := p_status_rec.opp_decision_date_flag ;
705 END IF;
706 IF p_status_rec.forecast_rollup_flag = FND_API.G_MISS_CHAR
707 THEN
708 v_forecast_rollup_flag := l_forecast_rollup_flag ;
709 ELSE
710 v_forecast_rollup_flag := p_status_rec.forecast_rollup_flag ;
711 END IF;
712 IF p_status_rec.status_rank = FND_API.G_MISS_NUM
713 THEN
714 v_status_rank := l_status_rank;
715 ELSE
716 v_status_rank := p_status_rec.status_rank ;
717 END IF;
718 IF p_status_rec.attribute_category = FND_API.G_MISS_CHAR
719 THEN
720 v_attribute_category := l_attribute_category;
721 ELSE
722 v_attribute_category := p_status_rec.attribute_category;
723 END IF;
724 IF p_status_rec.attribute1 = FND_API.G_MISS_CHAR
725 THEN
726 v_attribute1 := l_attribute1 ;
727 ELSE
728 v_attribute1 := p_status_rec.attribute1;
729 END IF;
730 IF p_status_rec.attribute2 = FND_API.G_MISS_CHAR
731 THEN
732 v_attribute2 := l_attribute2 ;
733 ELSE
734 v_attribute2 := p_status_rec.attribute2;
735 END IF;
736 IF p_status_rec.attribute3 = FND_API.G_MISS_CHAR
737 THEN
738 v_attribute3 := l_attribute3 ;
739 ELSE
740 v_attribute3 := p_status_rec.attribute3 ;
741 END IF;
742 IF p_status_rec.attribute4 = FND_API.G_MISS_CHAR
743 THEN
744 v_attribute4 := l_attribute4 ;
745 ELSE
746 v_attribute4 := p_status_rec.attribute4;
747 END IF;
748 IF p_status_rec.attribute5 = FND_API.G_MISS_CHAR
749 THEN
750 v_attribute5 := l_attribute5 ;
751 ELSE
752 v_attribute5 := p_status_rec.attribute5;
753 END IF;
754 IF p_status_rec.attribute6 = FND_API.G_MISS_CHAR
755 THEN
756 v_attribute6 := l_attribute6 ;
757 ELSE
758 v_attribute6 := p_status_rec.attribute6;
759 END IF;
760 IF p_status_rec.attribute7 = FND_API.G_MISS_CHAR
761 THEN
762 v_attribute7 := l_attribute7 ;
763 ELSE
764 v_attribute7 := p_status_rec.attribute7;
765 END IF;
766 IF p_status_rec.attribute8 = FND_API.G_MISS_CHAR
767 THEN
768 v_attribute8 := l_attribute8 ;
769 ELSE
770 v_attribute8 := p_status_rec.attribute8;
771 END IF;
772 IF p_status_rec.attribute9 = FND_API.G_MISS_CHAR
773 THEN
774 v_attribute9 := l_attribute9 ;
775 ELSE
776 v_attribute9 := p_status_rec.attribute9;
777 END IF;
778 IF p_status_rec.attribute10 = FND_API.G_MISS_CHAR
779 THEN
780 v_attribute10 := l_attribute10 ;
781 ELSE
782 v_attribute10 := p_status_rec.attribute10;
783 END IF;
784 IF p_status_rec.attribute11 = FND_API.G_MISS_CHAR
785 THEN
786 v_attribute11 := l_attribute11 ;
787 ELSE
788 v_attribute11 := p_status_rec.attribute11;
789 END IF;
790 IF p_status_rec.attribute12 = FND_API.G_MISS_CHAR
791 THEN
792 v_attribute12 := l_attribute12 ;
793 ELSE
794 v_attribute12 := p_status_rec.attribute12;
795 END IF;
796 IF p_status_rec.attribute13 = FND_API.G_MISS_CHAR
797 THEN
798 v_attribute13 := l_attribute13 ;
799 ELSE
800 v_attribute13 := p_status_rec.attribute13;
801 END IF;
802 IF p_status_rec.attribute14 = FND_API.G_MISS_CHAR
803 THEN
804 v_attribute14 := l_attribute14 ;
805 ELSE
806 v_attribute14 := p_status_rec.attribute14;
807 END IF;
808 IF p_status_rec.attribute15 = FND_API.G_MISS_CHAR
809 THEN
810 v_attribute15 := l_attribute15 ;
811 ELSE
812 v_attribute15 := p_status_rec.attribute15;
813 END IF;
814 IF p_status_rec.meaning = FND_API.G_MISS_CHAR
815 THEN
816 v_meaning := l_meaning ;
817 ELSE
818 v_meaning := p_status_rec.meaning;
819 END IF;
820 IF p_status_rec.description = FND_API.G_MISS_CHAR
821 THEN
822 v_description := l_description ;
823 ELSE
824 v_description := p_status_rec.description;
825 END IF;
826
827 -- Check to see if values were passed for the who colums
828 -- if not default them.
829 IF p_status_rec.last_update_date = FND_API.G_MISS_DATE
830 OR TRIM(p_status_rec.last_update_date) IS NULL
831 THEN
832 l_last_update_date := SYSDATE;
833 ELSE
834 l_last_update_date := p_status_rec.last_update_date ;
835 END IF;
836
837 IF p_status_rec.last_update_login = FND_API.G_MISS_NUM
838 OR TRIM(p_status_rec.last_update_login) IS NULL
839 THEN
840 l_last_update_login := fnd_global.login_id;
841 ELSE
842 l_last_update_login := p_status_rec.last_update_login;
843 END IF;
844
845 IF p_status_rec.last_updated_by = FND_API.G_MISS_NUM
846 OR TRIM(p_status_rec.last_updated_by) IS NULL
847 THEN
848 l_last_updated_by := fnd_global.user_id;
849 ELSE
850 l_last_updated_by := p_status_rec.last_updated_by;
851 END IF;
852
853 -- Check for valid values being passed
854
855 IF TRIM(p_status_rec.win_loss_indicator) NOT IN ('W','L', NULL)
856 THEN
857 fnd_message.set_name('AS', 'AS_WIN_IND_INVALID');
858 fnd_msg_pub.add;
859 ELSE
860 v_win_loss_indicator:=p_status_rec.win_loss_indicator;
861 END IF;
862 IF v_enabled_flag NOT IN ('Y','N')
863 THEN
864 fnd_message.set_name('AS', 'AS_ENABLED_FLAG_INVALID');
865 fnd_msg_pub.add;
866 END IF;
867 IF v_lead_flag NOT IN ('Y','N')
868 THEN
869 fnd_message.set_name('AS', 'AS_LEAD_FLAG_INVALID');
870 fnd_msg_pub.add;
871 END IF;
872 IF v_opp_flag NOT IN ('Y','N')
873 THEN
874 fnd_message.set_name('AS', 'AS_OPP_FLAG_INVALID');
875 fnd_msg_pub.add;
876 END IF;
877 IF v_opp_decision_date_flag NOT IN ('Y','N')
878 THEN
879 fnd_message.set_name('AS', 'AS_DECISION_FLAG_INVALID');
880 fnd_msg_pub.add;
881 END IF;
882 IF v_forecast_rollup_flag NOT IN ('Y','N')
883 THEN
884 fnd_message.set_name('AS', 'AS_FORECAST_FLAG_INVALID');
885 fnd_msg_pub.add;
886 END IF;
887
888 -- if opp_open_status_flag is Y and win_loss_indicator IS NULL else
889 -- if opp_open_status_flag is N and win_loss_indicator not in W or L
890 -- then update into as_statuses_b and as_statuses_tl using api.
891
892 IF NOT ((v_opp_open_status_flag = 'Y' AND
893 TRIM(v_win_loss_indicator) IS NULL)
894 OR (v_opp_open_status_flag ='N' AND
895 v_win_loss_indicator IN ('W','L')))
896 THEN
897 fnd_message.set_name('AS', 'AS_INVALID_WL_STATUS_COMBO');
898 fnd_message.set_token('WIN_LOSS_IND', p_status_rec.win_loss_indicator);
899 fnd_msg_pub.add;
900 END IF;
901
902 IF (FND_MSG_PUB.COUNT_MSG > 0)
903 THEN
904 fnd_message.set_name('AS', 'AS_STATUS_UPDATE_FAILED');
905 fnd_msg_pub.add;
906 RAISE fnd_api.g_exc_error;
907 END IF;
908
909 -- Lock the row for update. Check to see if the fetched value is same still.
910 -- If they are same then update the record else give a message that the row has been
911 -- updated by others.
912
913 OPEN lock_row_for_update(TRIM(NLS_UPPER(p_status_rec.status_code)));
914 FETCH lock_row_for_update INTO l_current_last_update_date;
915 IF lock_row_for_update%NOTFOUND
916 THEN
917 CLOSE lock_row_for_update;
918 RAISE fnd_api.g_exc_unexpected_error;
919 END IF;
920
921 IF l_last_update_date <> l_current_last_update_date
922 THEN
923 fnd_message.set_name('AS', 'AS_RECORD_UPDATED');
924 fnd_msg_pub.add;
925 RAISE fnd_api.g_exc_error;
926 END IF;
927
928 AS_STATUSES_PKG.UPDATE_ROW(
929 p_status_rec.status_code,
930 v_enabled_flag,
931 v_lead_flag,
932 v_opp_flag,
933 v_opp_open_status_flag,
934 v_opp_decision_date_flag,
935 v_status_rank,
936 v_forecast_rollup_flag,
937 v_win_loss_indicator,
938 NULL,
939 v_attribute_category,
940 v_attribute1,
941 v_attribute2,
942 v_attribute3,
943 v_attribute4,
944 v_attribute5,
945 v_attribute6,
946 v_attribute7,
947 v_attribute8,
948 v_attribute9,
949 v_attribute10,
950 v_attribute11,
951 v_attribute12,
952 v_attribute13,
953 v_attribute14,
954 v_attribute15,
955 v_meaning,
956 v_description,
957 l_last_update_date,
958 l_last_updated_by,
959 l_last_update_login);
960
961
962 -- Standard Check for p_commit
963 IF fnd_api.to_boolean (p_commit)
964 THEN
965 COMMIT WORK;
966 END IF;
967
968 -- Close all opened cursors
969 CLOSE get_update_row_cur; -- closed after update...
970 CLOSE lock_row_for_update ;
971
972
973 -- Standard call to get message count and if count is 1, get message infor.
974 fnd_msg_pub.count_and_get(
975 p_count => x_msg_count,
976 p_data => x_msg_data
977 );
978
979 -- Handling all Exceptions
980 EXCEPTION
981 WHEN fnd_api.g_exc_error THEN
982 ROLLBACK TO update_status;
983 x_return_status := fnd_api.g_ret_sts_error;
984 fnd_msg_pub.count_and_get (
985 p_count => x_msg_count,
986 p_data => x_msg_data
987 );
988 WHEN fnd_api.g_exc_unexpected_error THEN
989 ROLLBACK TO update_status;
990 x_return_status := fnd_api.g_ret_sts_unexp_error;
991 fnd_msg_pub.count_and_get (
992 p_count => x_msg_count,
993 p_data => x_msg_data
994 );
995 WHEN OTHERS THEN
996 ROLLBACK TO update_status;
997 x_return_status := fnd_api.g_ret_sts_unexp_error;
998 fnd_msg_pub.count_and_get (
999 p_count => x_msg_count,
1000 p_data => x_msg_data
1001 );
1002
1003 END update_status;
1004 END as_status_pub;
[Home] [Help]