[Home] [Help]
PACKAGE BODY: APPS.HZ_CLASSIFICATION_V2PUB
Source
1 PACKAGE BODY HZ_CLASSIFICATION_V2PUB AS
2 /*$Header: ARH2CLSB.pls 120.48.12010000.3 2010/03/19 08:25:03 rgokavar ship $ */
3
4 ----------------------------------
5 -- declaration of global variables
6 ----------------------------------
7
8 G_DEBUG BOOLEAN := FALSE;
9
10 ------------------------------------
11 -- declaration of private procedures
12 ------------------------------------
13
14 PROCEDURE enable_debug;
15
16 PROCEDURE disable_debug;
17
18 PROCEDURE do_create_class_category(
19 p_class_cat_rec IN OUT NOCOPY CLASS_CATEGORY_REC_TYPE,
20 x_return_status IN OUT NOCOPY VARCHAR2
21 );
22
23 PROCEDURE do_update_class_category(
24 p_class_cat_rec IN OUT NOCOPY CLASS_CATEGORY_REC_TYPE,
25 p_object_version_number IN OUT NOCOPY NUMBER,
26 x_return_status IN OUT NOCOPY VARCHAR2
27 );
28
29 PROCEDURE do_create_class_code_relation(
30 p_class_code_rel_rec IN OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE,
31 x_return_status IN OUT NOCOPY VARCHAR2
32 );
33
34 PROCEDURE do_update_class_code_relation(
35 p_class_code_rel_rec IN OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE,
36 p_object_version_number IN OUT NOCOPY NUMBER,
37 x_return_status IN OUT NOCOPY VARCHAR2
38 );
39
40 PROCEDURE do_create_code_assignment(
41 p_code_assignment_rec IN OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE,
42 x_return_status IN OUT NOCOPY VARCHAR2
43 );
44
45 PROCEDURE do_update_code_assignment(
46 p_code_assignment_rec IN OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE,
47 p_object_version_number IN OUT NOCOPY NUMBER,
48 x_return_status IN OUT NOCOPY VARCHAR2
49 );
50
51 PROCEDURE do_create_class_category_use(
52 p_class_category_use_rec IN OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
53 x_return_status IN OUT NOCOPY VARCHAR2
54 );
55
56 PROCEDURE do_update_class_category_use(
57 p_class_category_use_rec IN OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
58 p_object_version_number IN OUT NOCOPY NUMBER,
59 x_return_status IN OUT NOCOPY VARCHAR2
60 );
61
62 PROCEDURE get_current_class_category(
63 p_class_category IN VARCHAR2,
64 x_class_cat_rec OUT NOCOPY CLASS_CATEGORY_REC_TYPE
65 );
66
67 PROCEDURE get_curr_class_code_rel(
68 p_class_code_rel_rec IN CLASS_CODE_RELATION_REC_TYPE,
69 x_class_code_rel_rec OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE
70 );
71
72 PROCEDURE get_current_code_assignment(
73 p_code_assignment_id IN NUMBER,
74 x_code_assignment_rec OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE
75 );
76
77 FUNCTION is_industrial_class(
78 p_class_category IN VARCHAR2
79 ) RETURN VARCHAR2;
80 --------------------------------------
81 -- private procedures and functions
82 --------------------------------------
83 /**
84 * PRIVATE FUNCTION is_industrial_class
85 * RETURN Value : 'Y' (if industrial classification)
86 * 'N' (if non-industrial classification)
87 *
88 * DESCRIPTION
89 * Check if it is industrial classification or not
90 *
91 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
92 *
93 * MODIFICATION HISTORY
94 *
95 * 07-25-2007 Nishant Singhai o Created (for Bug 6059383)
96 *
97 */
98 FUNCTION is_industrial_class(
99 p_class_category IN VARCHAR2
100 ) RETURN VARCHAR2 IS
101
102 l_yes_no VARCHAR2(10);
103
104 CURSOR c_check_industrial_group (l_class_category VARCHAR2) IS
105 SELECT 'Y'
106 FROM hz_code_assignments
107 WHERE owner_table_name = 'HZ_CLASS_CATEGORIES'
108 AND class_category = 'CLASS_CATEGORY_GROUP'
109 AND class_code = 'INDUSTRIAL_GROUP'
110 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1)
111 AND NVL(status,'A') = 'A'
112 AND owner_table_key_1 = l_class_category;
113
114 BEGIN
115 l_yes_no := 'N';
116
117 OPEN c_check_industrial_group (p_class_category);
118 FETCH c_check_industrial_group INTO l_yes_no;
119 CLOSE c_check_industrial_group;
120
121 RETURN l_yes_no;
122
123 END is_industrial_class;
124
125 /**
126 * PRIVATE PROCEDURE enable_debug
127 *
128 * DESCRIPTION
129 * Turn on debug mode.
130 *
131 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
132 * HZ_UTILITY_V2PUB.enable_debug
133 *
134 * MODIFICATION HISTORY
135 *
136 * 07-23-2001 Jianying Huang o Created.
137 *
138 */
139
140 PROCEDURE enable_debug IS
141
142 BEGIN
143
144 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
145 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
146 THEN
147 HZ_UTILITY_V2PUB.enable_debug;
148 G_DEBUG := TRUE;
149 END IF;
150
151 END enable_debug;
152
153 /**
154 * PRIVATE PROCEDURE disable_debug
155 *
156 * DESCRIPTION
157 * Turn off debug mode.
158 *
159 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
160 * HZ_UTILITY_V2PUB.disable_debug
161 *
162 * MODIFICATION HISTORY
163 *
164 * 07-23-2001 Jianying Huang o Created.
165 *
166 */
167
168 PROCEDURE disable_debug IS
169
170 BEGIN
171
172 IF G_DEBUG THEN
173 HZ_UTILITY_V2PUB.disable_debug;
174 G_DEBUG := FALSE;
175 END IF;
176
177 END disable_debug;
178
179 /*
180 This flag is used internaly.Indicates that if the data in the HZ_CLASS_CODE_DENORM table for this class
181 category is valid. Y for valid data. N for stale data. As long as anything
182 changed in class category tables, we need to set this flag to 'N'.
183 Data can berefreshed by running the Refresh of Classification Denormalization concurrent
184 program.
185 */
186 PROCEDURE set_frozen_flag(p_class_category in varchar2) is
187 begin
188
189 update hz_class_categories
190 set frozen_flag = 'N'
191 where class_category = p_class_category
192 and (frozen_flag = 'Y' or frozen_flag is null);
193
194 end set_frozen_flag;
195
196 /*===========================================================================+
197 | PROCEDURE
198 | do_create_class_category
199 |
200 | DESCRIPTION
201 | Creates class category
202 |
203 | SCOPE - PRIVATE
204 |
205 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
206 |
207 | ARGUMENTS : IN:
208 | OUT:
209 | IN/ OUT:
210 | p_class_cat_rec
211 | x_return_status
212 |
213 | RETURNS : NONE
214 |
215 | NOTES
216 |
217 | MODIFICATION HISTORY
218 +===========================================================================*/
219
220 PROCEDURE do_create_class_category(
221 p_class_cat_rec IN OUT NOCOPY CLASS_CATEGORY_REC_TYPE,
222 x_return_status IN OUT NOCOPY VARCHAR2
223 ) IS
224
225 l_rowid ROWID := NULL;
226
227 BEGIN
228
229 HZ_CLASS_VALIDATE_V2PUB.validate_class_category(
230 p_class_cat_rec,
231 'C',
232 x_return_status);
233
234 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
235 RAISE FND_API.G_EXC_ERROR;
236 END IF;
237
238 If HZ_CLASS_VALIDATE_V2PUB.is_valid_delimiter(p_class_cat_rec.class_category,p_class_cat_rec.delimiter)='N'
239 then
240 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DELIMITER');
241 FND_MSG_PUB.ADD;
242 RAISE FND_API.G_EXC_ERROR;
243 end if;
244
245 HZ_CLASS_CATEGORIES_PKG.Insert_Row (
246 X_CLASS_CATEGORY => p_class_cat_rec.class_category,
247 X_ALLOW_MULTI_PARENT_FLAG => p_class_cat_rec.allow_multi_parent_flag,
248 X_ALLOW_MULTI_ASSIGN_FLAG => p_class_cat_rec.allow_multi_assign_flag,
249 X_ALLOW_LEAF_NODE_ONLY_FLAG => p_class_cat_rec.allow_leaf_node_only_flag,
250 X_OBJECT_VERSION_NUMBER => 1,
251 X_CREATED_BY_MODULE => p_class_cat_rec.created_by_module,
252 X_APPLICATION_ID => p_class_cat_rec.application_id,
253 X_DELIMITER => p_class_cat_rec.delimiter
254 );
255
256 set_frozen_flag(p_class_cat_rec.class_category);
257
258 END;
259
260 /*===========================================================================+
261 | PROCEDURE
262 | do_update_class_category
263 |
264 | DESCRIPTION
265 | Updates class category
266 |
267 | SCOPE - PRIVATE
268 |
269 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
270 |
271 | ARGUMENTS : IN:
272 | OUT:
273 | IN/ OUT:
274 | p_class_cat_rec
275 | p_last_update_date
276 | x_return_status
277 |
278 | RETURNS : NONE
279 |
280 | NOTES
281 |
282 | MODIFICATION HISTORY
283 |
284 +===========================================================================*/
285
286 PROCEDURE do_update_class_category(
287 p_class_cat_rec IN OUT NOCOPY CLASS_CATEGORY_REC_TYPE,
288 p_object_version_number IN OUT NOCOPY NUMBER,
289 x_return_status IN OUT NOCOPY VARCHAR2
290 ) IS
291
292 l_object_version_number NUMBER;
293 l_rowid ROWID;
294 l_allow_leaf_node_only_flag VARCHAR2(1);
295 l_delimiter VARCHAR2(1);
296 l_allow_multi_parent_flag VARCHAR2(1);
297 BEGIN
298
299 -- check whether record has been updated by another user. If not, lock it.
300 BEGIN
301 SELECT OBJECT_VERSION_NUMBER,
302 ROWID, allow_leaf_node_only_flag,delimiter,allow_multi_parent_flag
303 INTO l_object_version_number,
307 FOR UPDATE OF CLASS_CATEGORY NOWAIT;
304 l_rowid,l_allow_leaf_node_only_flag,l_delimiter,l_allow_multi_parent_flag
305 FROM HZ_CLASS_CATEGORIES
306 WHERE CLASS_CATEGORY = p_class_cat_rec.class_category
308
309 IF NOT ((p_object_version_number is null and l_object_version_number is null)
310 OR (p_object_version_number = l_object_version_number))
311 THEN
312 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
313 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_CLASS_CATEGORIES');
314 FND_MSG_PUB.ADD;
315 RAISE FND_API.G_EXC_ERROR;
316 END IF;
317
318 p_object_version_number := nvl(l_object_version_number, 1) + 1;
319
320 EXCEPTION WHEN NO_DATA_FOUND THEN
321 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
322 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_CLASS_CATEGORIES');
323 FND_MESSAGE.SET_TOKEN('VALUE', 'p_class_cat_rec.class_category');
324 FND_MSG_PUB.ADD;
325 RAISE FND_API.G_EXC_ERROR;
326 END;
327
328 -- call for validations.
329 HZ_CLASS_VALIDATE_V2PUB.validate_class_category(p_class_cat_rec, 'U', x_return_status);
330
331 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
332 RAISE FND_API.G_EXC_ERROR;
333 END IF;
334
335 if p_class_cat_rec.delimiter is not null
336 and (nvl(l_delimiter,fnd_api.g_miss_char) <> p_class_cat_rec.delimiter)
337 then
338 If HZ_CLASS_VALIDATE_V2PUB.is_valid_delimiter(p_class_cat_rec.class_category,p_class_cat_rec.delimiter)='N'
339 then
340 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DELIMITER');
341 FND_MSG_PUB.ADD;
342 RAISE FND_API.G_EXC_ERROR;
343 end if;
344 end if;
345
346 if (p_class_cat_rec.allow_leaf_node_only_flag is not null
347 and (nvl(l_allow_leaf_node_only_flag,fnd_api.g_miss_char) <> p_class_cat_rec.allow_leaf_node_only_flag))
348 or (p_class_cat_rec.delimiter is not null
349 and (nvl(l_delimiter,fnd_api.g_miss_char) <> p_class_cat_rec.delimiter))
350 or (p_class_cat_rec.allow_multi_parent_flag is not null
351 and (nvl(l_allow_multi_parent_flag,fnd_api.g_miss_char) <> p_class_cat_rec.allow_multi_parent_flag))
352 then
353 set_frozen_flag(p_class_cat_rec.class_category);
354 end if;
355
356
357 -- call to table-handler.
358 HZ_CLASS_CATEGORIES_PKG.Update_Row (
359 X_CLASS_CATEGORY => p_class_cat_rec.class_category,
360 X_ALLOW_MULTI_PARENT_FLAG => p_class_cat_rec.allow_multi_parent_flag,
361 X_ALLOW_MULTI_ASSIGN_FLAG => p_class_cat_rec.allow_multi_assign_flag,
362 X_ALLOW_LEAF_NODE_ONLY_FLAG => p_class_cat_rec.allow_leaf_node_only_flag,
363 X_OBJECT_VERSION_NUMBER => p_object_version_number,
364 X_CREATED_BY_MODULE => p_class_cat_rec.created_by_module,
365 X_APPLICATION_ID => p_class_cat_rec.application_id,
366 X_DELIMITER => p_class_cat_rec.delimiter
367 );
368
369 END;
370
371
372 /*===========================================================================+
373 | PROCEDURE
374 | do_create_class_code_relation
375 |
376 | DESCRIPTION
377 | Creates class code relation
378 |
379 | SCOPE - PRIVATE
380 |
381 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
382 |
383 | ARGUMENTS : IN:
384 | OUT:
385 | IN/ OUT:
386 | p_class_code_rel_rec
387 | x_return_status
388 |
389 | RETURNS : NONE
390 |
391 | NOTES
392 |
393 | MODIFICATION HISTORY
394 |
395 +===========================================================================*/
396
397 PROCEDURE do_create_class_code_relation(
398 p_class_code_rel_rec IN OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE,
399 x_return_status IN OUT NOCOPY VARCHAR2
400 ) IS
401
402 l_rowid ROWID := NULL;
403
404 BEGIN
405
406 -- Bug 3816590. Default start_date_active to sysdate if user has not passed any value.
407
408 If p_class_code_rel_rec.start_date_active is null
409 OR p_class_code_rel_rec.start_date_active = fnd_api.g_miss_date
410 then
411 p_class_code_rel_rec.start_date_active := sysdate;
412 end if;
413
414 HZ_CLASS_VALIDATE_V2PUB.validate_class_code_relation(
415 p_class_code_rel_rec,
416 'C',
417 x_return_status);
418
419 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
420 RAISE FND_API.G_EXC_ERROR;
421 END IF;
422
423 HZ_CLASS_CODE_RELATIONS_PKG.Insert_Row (
424 X_CLASS_CATEGORY => p_class_code_rel_rec.class_category,
425 X_CLASS_CODE => p_class_code_rel_rec.class_code,
426 X_SUB_CLASS_CODE => p_class_code_rel_rec.sub_class_code,
427 X_START_DATE_ACTIVE => p_class_code_rel_rec.start_date_active,
428 X_END_DATE_ACTIVE => p_class_code_rel_rec.end_date_active,
429 X_OBJECT_VERSION_NUMBER => 1,
430 X_CREATED_BY_MODULE => p_class_code_rel_rec.created_by_module,
431 X_APPLICATION_ID => p_class_code_rel_rec.application_id
432 );
433
437 /*===========================================================================+
434 set_frozen_flag(p_class_code_rel_rec.class_category);
435 END;
436
438 | PROCEDURE
439 | do_update_class_code_relation
440 |
441 | DESCRIPTION
442 | Updates class code relation
443 |
444 | SCOPE - PRIVATE
445 |
446 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
447 |
448 | ARGUMENTS : IN:
449 | OUT:
450 | IN/ OUT:
451 | p_class_code_rel_rec
452 | p_last_update_date
453 | x_return_status
454 |
455 | RETURNS : NONE
456 |
457 | NOTES
458 |
459 | MODIFICATION HISTORY
460 |
461 +===========================================================================*/
462
463 PROCEDURE do_update_class_code_relation(
464 p_class_code_rel_rec IN OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE,
465 p_object_version_number IN OUT NOCOPY NUMBER,
466 x_return_status IN OUT NOCOPY VARCHAR2
467 ) IS
468
469 l_object_version_number NUMBER;
470 l_rowid ROWID;
471 l_end_date_active DATE;
472 BEGIN
473
474 -- check whether record has been updated by another user. If not, lock it.
475 BEGIN
476 SELECT OBJECT_VERSION_NUMBER,
477 ROWID,END_DATE_ACTIVE
478 INTO l_object_version_number,
479 l_rowid,l_end_date_active
480 FROM HZ_CLASS_CODE_RELATIONS
481 WHERE CLASS_CATEGORY = p_class_code_rel_rec.class_category
482 AND CLASS_CODE = p_class_code_rel_rec.class_code
483 AND SUB_CLASS_CODE = p_class_code_rel_rec.sub_class_code
484 AND START_DATE_ACTIVE = p_class_code_rel_rec.START_DATE_ACTIVE
485 FOR UPDATE OF CLASS_CATEGORY, CLASS_CODE, SUB_CLASS_CODE NOWAIT;
486
487 IF NOT ((p_object_version_number is null and l_object_version_number is null)
488 OR (p_object_version_number = l_object_version_number))
489 THEN
490 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
491 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_CLASS_CODE_RELATIONS');
492 FND_MSG_PUB.ADD;
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495
496 p_object_version_number := nvl(l_object_version_number, 1) + 1;
497
498 EXCEPTION WHEN NO_DATA_FOUND THEN
499 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
500 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_CLASS_CODE_RELATIONS');
501 FND_MESSAGE.SET_TOKEN('VALUE', 'p_class_code_rel_rec.class_category');
502 FND_MSG_PUB.ADD;
503 RAISE FND_API.G_EXC_ERROR;
504 END;
505
506 -- call for validations.
507 HZ_CLASS_VALIDATE_V2PUB.validate_class_code_relation(
508 p_class_code_rel_rec,
509 'U',
510 x_return_status);
511
512 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
513 RAISE FND_API.G_EXC_ERROR;
514 END IF;
515
516 if p_class_code_rel_rec.end_date_active is not null
517 and (nvl(l_end_date_active,fnd_api.g_miss_date) <> p_class_code_rel_rec.end_date_active)
518 then
519 set_frozen_flag(p_class_code_rel_rec.class_category);
520 end if;
521
522 -- call to table-handler.
523 HZ_CLASS_CODE_RELATIONS_PKG.Update_Row (
524 X_CLASS_CATEGORY => p_class_code_rel_rec.class_category,
525 X_CLASS_CODE => p_class_code_rel_rec.class_code,
526 X_SUB_CLASS_CODE => p_class_code_rel_rec.sub_class_code,
527 X_START_DATE_ACTIVE => p_class_code_rel_rec.start_date_active,
528 X_END_DATE_ACTIVE => p_class_code_rel_rec.end_date_active,
529 X_OBJECT_VERSION_NUMBER => p_object_version_number,
530 X_CREATED_BY_MODULE => p_class_code_rel_rec.created_by_module,
531 X_APPLICATION_ID => p_class_code_rel_rec.application_id
532 );
533
534 END;
535
536
537 /*===========================================================================+
538 | PROCEDURE
539 | do_create_code_assignment
540 |
541 | DESCRIPTION
542 | Creates code assignment
543 |
544 | SCOPE - PRIVATE
545 |
546 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
547 |
548 | ARGUMENTS : IN:
549 | OUT:
550 | IN/ OUT:
551 | p_code_assignment_rec
552 | x_return_status
553 |
554 | RETURNS : NONE
555 |
556 | NOTES
557 |
558 | MODIFICATION HISTORY
559 | May-29-2003 The API needs to be protected by Data security Bug 2963010
560 | Sep-09-2003 Rajib Ranjan Borah o The API defaults the value of content_source_type
561 | to 'USER_ENTERED'.Bug Number -2824772.
562 | 20-Nov-2003 Ramesh Ch Bug No: 3216842. Denormalized SIC_CODE and SIC_CODE_TYPE columns into
563 | HZ_PARTIES and HZ_ORGANIZATION_PROFILES tables for ORGANIZATION party
564 | primary code assignments of 1972 SIC,1977 SIC,1987 SIC and NAICS_1997
565 | class category type.
566 | 05-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
570
567 | Passed the value of actual_content_source to
568 | table_handler.
569 +===========================================================================*/
571 PROCEDURE do_create_code_assignment(
572 p_code_assignment_rec IN OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE,
573 x_return_status IN OUT NOCOPY VARCHAR2
574 ) IS
575
576 l_rowid ROWID := NULL;
577
578 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
579 dss_msg_count NUMBER := 0;
580 dss_msg_data VARCHAR2(2000):= null;
581 l_test_security VARCHAR2(1):= 'F';
582
583 l_object_version_number NUMBER;
584 l_organization_name VARCHAR2(360);
585 x_profile_id NUMBER;
586 l_organization_rec hz_party_v2pub.organization_rec_type;
587 l_party_rec hz_party_v2pub.party_rec_type;
588
589 --Bug No: 3216842
590
591 CURSOR c_party_type(c_party_id NUMBER) IS
592 SELECT party_type,object_version_number,
593 sic_code_type,sic_code /* Bug 4156312 */
594 FROM HZ_PARTIES
595 WHERE party_id=c_party_id;
596
597 l_party_type HZ_PARTIES.party_type%TYPE :=NULL;
598
599 -- End Of 3216842.
600
601 -- Bug 4156312
602 l_sic_code_type HZ_PARTIES.sic_code_type%TYPE;
603 l_sic_code HZ_PARTIES.sic_code%TYPE;
604
605
606 BEGIN
607
608 -- Bug 3070461. Default start_date_active to sysdate if user has not passed any value.
609 If p_code_assignment_rec.start_date_active is null OR p_code_assignment_rec.start_date_active = fnd_api.g_miss_date then
610 p_code_assignment_rec.start_date_active := sysdate;
611 end if;
612
613
614 --Bug Number 2824772 . API should default the 'content_source_type' column value to'USER_ENTERED'
615
616 IF p_code_assignment_rec.content_source_type IS NULL
617 OR
618 p_code_assignment_rec.content_source_type = FND_API.G_MISS_CHAR
619 THEN
620 p_code_assignment_rec.content_source_type :='USER_ENTERED';
621 END IF;
622
623
624
625 HZ_CLASS_VALIDATE_V2PUB.validate_code_assignment(
626 p_code_assignment_rec,
627 'C',
628 x_return_status);
629
630 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
631 RAISE FND_API.G_EXC_ERROR;
632 END IF;
633
634 --Bug 2830772: For 'NACE' lookup type, if the content_source_type is not
635 --'USER_ENTERED', even if the lookup_code is passed incorrectly with respect
636 --to decimal point, the value that needs to be stored in the column is the value
637 --that is present in the lookup.
638 IF( p_code_assignment_rec.actual_content_source <> 'USER_ENTERED'
639 AND
640 p_code_assignment_rec.class_category = 'NACE'
641 )
642 THEN
643 SELECT lookup_code
644 INTO p_code_assignment_rec.class_code
645 FROM fnd_lookup_values
646 WHERE replace(lookup_code, '.', '') = replace(p_code_assignment_rec.class_code, '.', '')
647 AND lookup_type='NACE'
648 AND rownum = 1;
649 END IF;
650
651
652
653 HZ_CODE_ASSIGNMENTS_PKG.Insert_Row (
654 X_CODE_ASSIGNMENT_ID => p_code_assignment_rec.code_assignment_id,
655 X_OWNER_TABLE_NAME => p_code_assignment_rec.owner_table_name,
656 X_OWNER_TABLE_ID => p_code_assignment_rec.owner_table_id,
657 X_OWNER_TABLE_KEY_1 => p_code_assignment_rec.owner_table_key_1,
658 X_OWNER_TABLE_KEY_2 => p_code_assignment_rec.owner_table_key_2,
659 X_OWNER_TABLE_KEY_3 => p_code_assignment_rec.owner_table_key_3,
660 X_OWNER_TABLE_KEY_4 => p_code_assignment_rec.owner_table_key_4,
661 X_OWNER_TABLE_KEY_5 => p_code_assignment_rec.owner_table_key_5,
662 X_CLASS_CATEGORY => p_code_assignment_rec.class_category,
663 X_CLASS_CODE => p_code_assignment_rec.class_code,
664 X_PRIMARY_FLAG => p_code_assignment_rec.primary_flag,
665 X_CONTENT_SOURCE_TYPE => p_code_assignment_rec.content_source_type,
666 X_START_DATE_ACTIVE => p_code_assignment_rec.start_date_active,
667 X_END_DATE_ACTIVE => p_code_assignment_rec.end_date_active,
668 X_STATUS => p_code_assignment_rec.status,
669 X_OBJECT_VERSION_NUMBER => 1,
670 X_CREATED_BY_MODULE => p_code_assignment_rec.created_by_module,
671 X_RANK => p_code_assignment_rec.rank,
672 X_APPLICATION_ID => p_code_assignment_rec.application_id,
673 -- SSM SST Integration and Extension
674 X_ACTUAL_CONTENT_SOURCE => p_code_assignment_rec.actual_content_source
675 );
676
677 -- VJN INTRODUCED CHANGE
678 -- THE CALL OUT TO DSS SHOULD HAPPEN ONLY IF THE CODE ASSIGNMENT
679 -- IS FOR HZ_PARTIES. IN OTHER WORDS, THE CODE ASSIGNMENT IS
680 -- SECURED ONLY AT THE PARTY LEVEL AND NOT THE DETAILS.
681 --
682 -- Bug 3818648: do dss check in party context only. check dss
683 -- profile before call test_instance.
684 --
685 IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' AND
686 p_code_assignment_rec.owner_table_name = 'HZ_PARTIES'
687 THEN
688 ---Bug 2963010 make table HZ_CODE_ASSIGNMENTS protected by Data Security
692 p_operation_code => 'INSERT',
689 ---Check if the DSS security is granted to the user
690 l_test_security :=
691 hz_dss_util_pub.test_instance(
693 p_db_object_name => 'HZ_CODE_ASSIGNMENTS',
694 p_instance_pk1_value => p_code_assignment_rec.code_assignment_id,
695 p_user_name => fnd_global.user_name,
696 x_return_status => dss_return_status,
697 x_msg_count => dss_msg_count,
698 x_msg_data => dss_msg_data);
699
700 if dss_return_status <> fnd_api.g_ret_sts_success THEN
701 RAISE FND_API.G_EXC_ERROR;
702 end if;
703
704 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
705 --
706 -- Bug 3835601: replaced the dss message with a more user friendly message
707 --
708 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
709 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
710 hz_dss_util_pub.get_display_name('HZ_CODE_ASSIGNMENTS', null));
711 FND_MSG_PUB.ADD;
712 RAISE FND_API.G_EXC_ERROR;
713 end if;
714 END IF;
715
716
717 -- Bug No: 4091181. Modified logic to denormalize SIC_CODE and SIC_CODE_TYPE to
718 -- HZ_PARTIES and HZ_ORGANIZATION_PROFILES tables for ORGANIZATION party
719 -- Primary code assignments of 1972 SIC,1977 SIC,1987 SIC and NAICS_1997
720 -- class category type.
721 IF UPPER(p_code_assignment_rec.owner_table_name) = 'HZ_PARTIES'
722 AND
723 -- Bug 6059383 : Denormalize for all industrial class and not only the hard coded values
724 -- p_code_assignment_rec.class_category in ('1972 SIC' , '1977 SIC' , '1987 SIC' , 'NAICS_1997')
725 is_industrial_class(p_code_assignment_rec.class_category) = 'Y'
726 THEN
727 IF
728 p_code_assignment_rec.primary_flag='Y'
729 AND
730 p_code_assignment_rec.start_date_active<=sysdate
731 AND
732 (
733 p_code_assignment_rec.end_date_active is NULL
734 OR p_code_assignment_rec.end_date_active=fnd_api.g_miss_date
735 OR p_code_assignment_rec.end_date_active>sysdate
736 )
737 THEN
738 OPEN c_party_type(p_code_assignment_rec.owner_table_id);
739 FETCH c_party_type INTO l_party_type,l_object_version_number,l_sic_code_type,l_sic_code;
740 CLOSE c_party_type;
741
742 IF (l_party_type='ORGANIZATION'
743 /* Bug 4156312 */
744 AND (
745 (l_sic_code_type is null and l_sic_code is null)
746 OR
747 (l_sic_code_type is NOT NULL
748 AND l_sic_code is NOT NULL
749 AND (p_code_assignment_rec.class_category<>l_sic_code_type
750 OR p_code_assignment_rec.class_code<>l_sic_code)
751 )))
752 THEN
753
754 l_party_rec.party_id := p_code_assignment_rec.owner_table_id;
755 l_organization_rec.SIC_CODE_TYPE := p_code_assignment_rec.class_category;
756 l_organization_rec.SIC_CODE := p_code_assignment_rec.class_code;
757 l_organization_rec.party_rec := l_party_rec;
758
759 l_organization_rec.actual_content_source:=p_code_assignment_rec.actual_content_source;
760
761 --Call to Update organization to update both HZ_PARTIES AND HZ_ORGANIZATION_PROFILES
762
763 HZ_PARTY_V2PUB.update_organization(
764 'T',
765 l_organization_rec,
766 l_object_version_number,
767 x_profile_id,
768 x_return_status,
769 dss_msg_count,
770 dss_msg_data);
771 IF x_return_status <> fnd_api.g_ret_sts_success THEN
772 RAISE FND_API.G_EXC_ERROR;
773 END IF;
774 END IF;
775 END IF;
776 END IF;
777
778 -- Bug 4091181. Modified logic to denormalize class code for 'CUSTOMER_CATEGORY'
779 -- class category to HZ_PARTIES
780
781 IF UPPER(p_code_assignment_rec.owner_table_name) = 'HZ_PARTIES'
782 AND
783 p_code_assignment_rec.class_category='CUSTOMER_CATEGORY'
784 THEN
785 IF
786 p_code_assignment_rec.primary_flag='Y'
787 AND
788 p_code_assignment_rec.start_date_active<=sysdate
789 AND
790 (
791 p_code_assignment_rec.end_date_active is NULL
792 OR p_code_assignment_rec.end_date_active=fnd_api.g_miss_date
793 OR p_code_assignment_rec.end_date_active>sysdate
794 )
795 THEN
796 update hz_parties
797 set category_code = p_code_assignment_rec.class_code,
798 last_update_date = hz_utility_v2pub.last_update_date,
799 last_updated_by = hz_utility_v2pub.last_updated_by,
800 last_update_login = hz_utility_v2pub.last_update_login
801 where party_id = p_code_assignment_rec.owner_table_id;
802
803 --Bug9058492
804 OPEN c_party_type(p_code_assignment_rec.owner_table_id);
805 FETCH c_party_type INTO l_party_type,l_object_version_number,l_sic_code_type,l_sic_code;
806 CLOSE c_party_type;
807
808 IF l_party_type = 'PERSON' THEN
809 HZ_DQM_SYNC.sync_person(p_code_assignment_rec.owner_table_id, 'C');
810 ELSIF l_party_type = 'ORGANIZATION' THEN
811 HZ_DQM_SYNC.sync_org(p_code_assignment_rec.owner_table_id, 'C');
815 END IF;
812 END IF;
813
814 END IF;
816
817
818
819
820 END;
821
822 /*===========================================================================+
823 | PROCEDURE
824 | do_update_code_assignment
825 |
826 | DESCRIPTION
827 | Updates code assignment
828 |
829 | SCOPE - PRIVATE
830 |
831 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
832 |
833 | ARGUMENTS : IN:
834 | OUT:
835 | IN/ OUT:
836 | p_code_assignment_rec
837 | p_last_update_date
838 | x_return_status
839 |
840 | RETURNS : NONE
841 |
842 | NOTES
843 |
844 | MODIFICATION HISTORY
845 |
846 | 20-Nov-2003 Ramesh Ch Bug No: 3216842. Denormalized SIC_CODE and SIC_CODE_TYPE columns into
847 | HZ_PARTIES and HZ_ORGANIZATION_PROFILES tables for ORGANIZATION party
848 | primary code assignments of 1972 SIC,1977 SIC,1987 SIC and NAICS_1997
849 | class category type.
850 | 05-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
851 | Passed the value of actual_content_source to
852 | table_handler.
853 +===========================================================================*/
854
855 PROCEDURE do_update_code_assignment(
856 p_code_assignment_rec IN OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE,
857 p_object_version_number IN OUT NOCOPY NUMBER,
858 x_return_status IN OUT NOCOPY VARCHAR2
859 ) IS
860
861 l_object_version_number NUMBER;
862 l_rowid ROWID;
863
864 l_owner_table_name VARCHAR2(30);
865 l_owner_table_id NUMBER;
866 l_class_category VARCHAR2(30);
867 l_class_code VARCHAR2(30);
868 l_organization_name VARCHAR2(360);
869 x_profile_id NUMBER;
870 dss_msg_count NUMBER := 0;
871 dss_msg_data VARCHAR2(2000):= null;
872
873 l_organization_rec hz_party_v2pub.organization_rec_type;
874 l_party_rec hz_party_v2pub.party_rec_type;
875
876 --Commented code for Bug No. 4091181.
877 --Bug No: 3216842
878 /*
879 CURSOR c_old_code_values(c_code_assignment_id NUMBER) IS
880 SELECT owner_table_id,owner_table_name,class_category,
881 class_code,primary_flag, start_date_active, end_date_active
882 FROM hz_code_assignments
883 WHERE code_assignment_id = c_code_assignment_id;
884 */
885
886 CURSOR c_party_type(c_party_id NUMBER) IS
887 SELECT party_type,object_version_number,
888 sic_code_type,sic_code /* Bug 4156312 */
889 FROM HZ_PARTIES
890 WHERE party_id=c_party_id;
891
892
893 -- Bug 4059298.
894 CURSOR c_new_denorm(p_party_id NUMBER,p_code_id NUMBER) Is
895 SELECT class_category,class_code
896 from (
897 select code_assignment_id,class_category,class_code
898 from hz_code_assignments a
899 --where class_category in ('1972 SIC','1977 SIC','1987 SIC','NAICS_1997') -- Bug 6059383
900 WHERE owner_table_id = p_party_id
901 and sysdate between start_date_active and nvl(end_date_active,sysdate+1)
902 and primary_flag='Y'
903 and code_assignment_id < p_code_id
904 -- Added for Bug 6059383 (remove hard coding for industrial classification)
905 AND EXISTS (SELECT NULL FROM hz_code_assignments b
906 WHERE a.class_category = b.owner_table_key_1
907 AND b.owner_table_name = 'HZ_CLASS_CATEGORIES'
908 AND b.class_category = 'CLASS_CATEGORY_GROUP'
909 AND b.class_code = 'INDUSTRIAL_GROUP'
910 AND SYSDATE BETWEEN b.start_date_active AND NVL(b.end_date_active,SYSDATE+1)
911 AND NVL(b.status,'A') = 'A'
912 )
913 order by code_assignment_id desc
914 )
915 where rownum=1;
916
917 l_party_type HZ_PARTIES.party_type%TYPE;
918 l_primary_flag HZ_CODE_ASSIGNMENTS.PRIMARY_FLAG%TYPE;
919 l_denorm_flag BOOLEAN :=FALSE;
920 -- End Of 3216842.
921
922 l_category_code VARCHAR2(30);
923 l_start_date DATE;
924 l_end_date DATE;
925
926 -- Bug 4091181
927 l_actual_content_src HZ_CODE_ASSIGNMENTS.actual_content_source%TYPE;
928
929 -- Bug 4156312
930 l_sic_code_type HZ_PARTIES.sic_code_type%TYPE;
931 l_sic_code HZ_PARTIES.sic_code%TYPE;
932
933 -- Bug 4693719 : Added for local assignment
934 l_acs HZ_CODE_ASSIGNMENTS.actual_content_source%TYPE;
935
936 BEGIN
937
938 -- check whether record has been updated by another user. If not, lock it.
939 BEGIN
940 -- Bug 4091181. Modified the statement to remove use of cursor
941 -- c_old_code_values.
942 SELECT OBJECT_VERSION_NUMBER,owner_table_id, owner_table_name,
943 class_category, class_code,primary_flag, start_date_active,
944 end_date_active,ROWID,actual_content_source
945 INTO l_object_version_number,l_owner_table_id,l_owner_table_name,
946 l_class_category,l_class_code,l_primary_flag, l_start_date,
947 l_end_date,l_rowid,l_actual_content_src
948 FROM HZ_CODE_ASSIGNMENTS
949 WHERE CODE_ASSIGNMENT_ID = p_code_assignment_rec.code_assignment_id
950 FOR UPDATE OF CODE_ASSIGNMENT_ID NOWAIT;
951
952 IF NOT ((p_object_version_number is null and l_object_version_number is null)
953 --Bug 4260943
954 OR (p_object_version_number is not null and
955 l_object_version_number is not null and
956 p_object_version_number = l_object_version_number))
957 THEN
958 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
959 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_CODE_ASSIGNMENTS');
960 FND_MSG_PUB.ADD;
961 RAISE FND_API.G_EXC_ERROR;
962 END IF;
963
964 p_object_version_number := nvl(l_object_version_number, 1) + 1;
965
966 EXCEPTION WHEN NO_DATA_FOUND THEN
967 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
968 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_CODE_ASSIGNMENTS');
969 FND_MESSAGE.SET_TOKEN('VALUE', 'p_code_assignment_rec.code_assignment_id');
970 FND_MSG_PUB.ADD;
971 RAISE FND_API.G_EXC_ERROR;
972 END;
973
974 -- call for validations.
975 HZ_CLASS_VALIDATE_V2PUB.validate_code_assignment(
976 p_code_assignment_rec,
977 'U',
978 x_return_status);
979
980 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
981 RAISE FND_API.G_EXC_ERROR;
982 END IF;
983
984 --Bug 2830772: For 'NACE' lookup type, if the content_source_type is not
985 --'USER_ENTERED', even if the lookup_code is passed incorrectly with respect
986 --to decimal point, the value that needs to be stored in the column is the value
987 --that is present in the lookup.
988 IF( p_code_assignment_rec.actual_content_source <> 'USER_ENTERED'
989 AND
990 p_code_assignment_rec.class_category = 'NACE'
991 )
992 THEN
993 SELECT lookup_code
994 INTO p_code_assignment_rec.class_code
995 FROM fnd_lookup_values
996 WHERE replace(lookup_code, '.', '') = replace(nvl(p_code_assignment_rec.class_code,l_class_code), '.', '')
997 AND lookup_type='NACE'
998 AND rownum = 1;
999 END IF;
1000
1001 --Commented code for Bug No. 4091181.
1002
1003 /*
1004 ---Bug no :3216842
1005
1006 OPEN c_old_code_values(p_code_assignment_rec.code_assignment_id);
1007 FETCH c_old_code_values INTO l_owner_table_id,l_owner_table_name,l_class_category,
1008 l_class_code,l_primary_flag, l_start_date, l_end_date;
1009 CLOSE c_old_code_values;
1010
1011 --End of :3216842
1012 */
1013 -- Bug 4693719 : pass NULL if secure data is not updated
1014 IF HZ_UTILITY_V2PUB.G_UPDATE_ACS = 'Y' THEN
1015 l_acs := nvl(p_code_assignment_rec.actual_content_source, 'USER_ENTERED');
1016 ELSE
1017 l_acs := NULL;
1018 END IF;
1019
1020 -- call to table-handler.
1021 HZ_CODE_ASSIGNMENTS_PKG.Update_Row (
1022 X_Rowid => l_rowid,
1023 X_CODE_ASSIGNMENT_ID => p_code_assignment_rec.code_assignment_id,
1024 X_OWNER_TABLE_NAME => p_code_assignment_rec.owner_table_name,
1025 X_OWNER_TABLE_ID => p_code_assignment_rec.owner_table_id,
1026 X_OWNER_TABLE_KEY_1 => p_code_assignment_rec.owner_table_key_1,
1027 X_OWNER_TABLE_KEY_2 => p_code_assignment_rec.owner_table_key_2,
1028 X_OWNER_TABLE_KEY_3 => p_code_assignment_rec.owner_table_key_3,
1029 X_OWNER_TABLE_KEY_4 => p_code_assignment_rec.owner_table_key_4,
1030 X_OWNER_TABLE_KEY_5 => p_code_assignment_rec.owner_table_key_5,
1031 X_CLASS_CATEGORY => p_code_assignment_rec.class_category,
1032 X_CLASS_CODE => p_code_assignment_rec.class_code,
1033 X_PRIMARY_FLAG => p_code_assignment_rec.primary_flag,
1034 X_CONTENT_SOURCE_TYPE => p_code_assignment_rec.content_source_type,
1035 X_START_DATE_ACTIVE => p_code_assignment_rec.start_date_active,
1036 X_END_DATE_ACTIVE => p_code_assignment_rec.end_date_active,
1037 X_STATUS => p_code_assignment_rec.status,
1038 X_OBJECT_VERSION_NUMBER => p_object_version_number,
1039 X_CREATED_BY_MODULE => p_code_assignment_rec.created_by_module,
1040 X_RANK => p_code_assignment_rec.rank,
1041 X_APPLICATION_ID => p_code_assignment_rec.application_id,
1042 -- SSM SST Integration and Extension
1043 -- Bug 4693719 : Pass correct value for ACS
1044 X_ACTUAL_CONTENT_SOURCE => l_acs
1045 );
1046
1047 -- Bug No: 4091181. Modified logic to denormalize SIC_CODE and SIC_CODE_TYPE to
1048 -- HZ_PARTIES and HZ_ORGANIZATION_PROFILES tables for ORGANIZATION party
1049 -- Primary code assignments of 1972 SIC,1977 SIC,1987 SIC and NAICS_1997
1050 -- class category type.
1051
1052
1053 IF UPPER(nvl(p_code_assignment_rec.owner_table_name,l_owner_table_name)) = 'HZ_PARTIES'
1054 AND
1055 -- Bug 6059383 : Denormalize for all industrial class and not only the hard coded values
1056 -- nvl(p_code_assignment_rec.class_category,l_class_category)
1057 -- in ('1972 SIC' , '1977 SIC' , '1987 SIC' , 'NAICS_1997')
1058 is_industrial_class(NVL(p_code_assignment_rec.class_category,l_class_category)) = 'Y'
1059 THEN
1060 IF
1061 (
1062 nvl(p_code_assignment_rec.primary_flag,l_primary_flag)='Y'
1063 AND
1064 (
1065 ( nvl(p_code_assignment_rec.end_date_active,l_end_date)<= SYSDATE
1066 AND nvl(p_code_assignment_rec.end_date_active,l_end_date)<> fnd_api.g_miss_date
1067 )
1068 OR
1069 nvl(p_code_assignment_rec.start_date_active,l_start_date)> SYSDATE
1070
1071 )
1072 )
1073 OR (p_code_assignment_rec.primary_flag='N' AND l_primary_flag='Y')
1074 THEN
1075
1076 -- Bug 4059298.
1077 OPEN c_party_type(nvl(p_code_assignment_rec.owner_table_id,l_owner_table_id));
1078 FETCH c_party_type INTO l_party_type,l_object_version_number,l_sic_code_type,l_sic_code;
1079 CLOSE c_party_type;
1080
1081 IF l_party_type='ORGANIZATION'
1082 THEN
1083 OPEN c_new_denorm(l_owner_table_id,p_code_assignment_rec.code_assignment_id);
1084 FETCH c_new_denorm into l_organization_rec.SIC_CODE_TYPE ,l_organization_rec.SIC_CODE ;
1085 IF c_new_denorm%NOTFOUND
1086 THEN
1087 l_organization_rec.SIC_CODE_TYPE := fnd_api.g_miss_char;
1088 l_organization_rec.SIC_CODE := fnd_api.g_miss_char;
1089 END IF;
1090 CLOSE c_new_denorm;
1091
1092 l_party_rec.party_id := l_owner_table_id;
1093 l_organization_rec.party_rec := l_party_rec;
1094
1095 l_organization_rec.actual_content_source:=l_actual_content_src;
1096
1097 --Call to Update organization to update both HZ_PARTIES AND HZ_ORGANIZATION_PROFILES
1098
1099 HZ_PARTY_V2PUB.update_organization(
1100 'T',
1101 l_organization_rec,
1102 l_object_version_number,
1103 x_profile_id,
1104 x_return_status,
1105 dss_msg_count,
1106 dss_msg_data);
1107
1108 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1109 RAISE FND_API.G_EXC_ERROR;
1110 END IF;
1111 END IF;
1112 ELSIF
1113 nvl(p_code_assignment_rec.primary_flag,l_primary_flag)='Y'
1114 AND
1115 nvl(p_code_assignment_rec.start_date_active,l_start_date)<=sysdate
1116 AND
1117 (
1118 nvl(p_code_assignment_rec.end_date_active,l_end_date) is NULL
1119 OR nvl(p_code_assignment_rec.end_date_active,l_end_date)=fnd_api.g_miss_date
1120 OR nvl(p_code_assignment_rec.end_date_active,l_end_date)>sysdate
1121 )
1122 THEN
1123 OPEN c_party_type(nvl(p_code_assignment_rec.owner_table_id,l_owner_table_id));
1124 FETCH c_party_type INTO l_party_type,l_object_version_number,l_sic_code_type,l_sic_code;
1125 CLOSE c_party_type;
1126
1127
1128 IF (l_party_type='ORGANIZATION'
1129 /* Bug 4156312 */
1130 AND (
1134 AND l_sic_code is NOT NULL
1131 (l_sic_code_type is null and l_sic_code is null)
1132 OR
1133 (l_sic_code_type is NOT NULL
1135 AND (nvl(p_code_assignment_rec.class_category,l_class_category)<>l_sic_code_type
1136 OR nvl(p_code_assignment_rec.class_code,l_class_code)<>l_sic_code)
1137 )))
1138 THEN
1139
1140 l_party_rec.party_id := l_owner_table_id;
1141 l_organization_rec.SIC_CODE_TYPE := nvl(p_code_assignment_rec.class_category,l_class_category);
1142 l_organization_rec.SIC_CODE := nvl(p_code_assignment_rec.class_code,l_class_code);
1143 l_organization_rec.party_rec := l_party_rec;
1144
1145 l_organization_rec.actual_content_source:=l_actual_content_src;
1146
1147 --Call to Update organization to update both HZ_PARTIES AND HZ_ORGANIZATION_PROFILES
1148
1149 HZ_PARTY_V2PUB.update_organization(
1150 'T',
1151 l_organization_rec,
1152 l_object_version_number,
1153 x_profile_id,
1154 x_return_status,
1155 dss_msg_count,
1156 dss_msg_data);
1157 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1158 RAISE FND_API.G_EXC_ERROR;
1159 END IF;
1160 END IF;
1161 END IF;
1162 END IF;
1163
1164
1165 -- Bug 4091181. Modified logic to denormalize class code for 'CUSTOMER_CATEGORY'
1166 -- class category to HZ_PARTIES
1167
1168 IF UPPER(nvl(p_code_assignment_rec.owner_table_name,l_owner_table_name)) = 'HZ_PARTIES'
1169 AND
1170 nvl(p_code_assignment_rec.class_category,l_class_category)='CUSTOMER_CATEGORY'
1171 THEN
1172 IF
1173 (
1174 nvl(p_code_assignment_rec.primary_flag,l_primary_flag)='Y'
1175 AND
1176 (
1177 ( nvl(p_code_assignment_rec.end_date_active,l_end_date)<= SYSDATE
1178 AND nvl(p_code_assignment_rec.end_date_active,l_end_date)<> fnd_api.g_miss_date
1179 )
1180 OR
1181 nvl(p_code_assignment_rec.start_date_active,l_start_date)> SYSDATE
1182
1183 )
1184 )
1185 OR (p_code_assignment_rec.primary_flag='N' AND l_primary_flag='Y')
1186 THEN
1187 -- terminating
1188 UPDATE HZ_PARTIES
1189 SET CATEGORY_CODE = NULL,
1190 last_update_date = hz_utility_v2pub.last_update_date,
1191 last_updated_by = hz_utility_v2pub.last_updated_by,
1192 last_update_login = hz_utility_v2pub.last_update_login
1193 WHERE PARTY_ID = nvl(p_code_assignment_rec.owner_table_id,l_owner_table_id)
1194 AND CATEGORY_CODE = nvl(p_code_assignment_rec.class_code,l_class_code);
1195 ELSIF
1196 nvl(p_code_assignment_rec.primary_flag,l_primary_flag)='Y'
1197 AND
1198 nvl(p_code_assignment_rec.start_date_active,l_start_date)<=sysdate
1199 AND
1200 (
1201 nvl(p_code_assignment_rec.end_date_active,l_end_date) is NULL
1202 OR nvl(p_code_assignment_rec.end_date_active,l_end_date)=fnd_api.g_miss_date
1203 OR nvl(p_code_assignment_rec.end_date_active,l_end_date)>sysdate
1204 )
1205 THEN
1206 update hz_parties
1207 set category_code = nvl(p_code_assignment_rec.class_code,l_class_code),
1208 last_update_date = hz_utility_v2pub.last_update_date,
1209 last_updated_by = hz_utility_v2pub.last_updated_by,
1210 last_update_login = hz_utility_v2pub.last_update_login
1211 where party_id = l_owner_table_id;
1212
1213 --Bug9058492
1214 OPEN c_party_type(nvl(p_code_assignment_rec.owner_table_id,l_owner_table_id));
1215 FETCH c_party_type INTO l_party_type,l_object_version_number,l_sic_code_type,l_sic_code;
1216 CLOSE c_party_type;
1217
1218
1219 IF l_party_type = 'PERSON' THEN
1220 HZ_DQM_SYNC.sync_person(p_code_assignment_rec.owner_table_id, 'U');
1221 ELSIF l_party_type = 'ORGANIZATION' THEN
1222 HZ_DQM_SYNC.sync_org(p_code_assignment_rec.owner_table_id, 'U');
1223 END IF;
1224
1225
1226 END IF;
1227 END IF;
1228
1229
1230
1231 END;
1232
1233
1234 /*===========================================================================+
1235 | PROCEDURE
1236 | do_create_class_category_use
1237 |
1238 | DESCRIPTION
1239 | Create class category use
1240 |
1241 | SCOPE - PRIVATE
1242 |
1243 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1244 |
1245 | ARGUMENTS : IN:
1246 | OUT:
1247 | IN/ OUT:
1248 | p_class_category_use_rec
1249 | p_last_update_date
1250 | x_return_status
1251 |
1252 | RETURNS : NONE
1253 |
1254 | NOTES
1255 |
1256 | MODIFICATION HISTORY
1257 |
1258 +===========================================================================*/
1259
1260 PROCEDURE do_create_class_category_use(
1261 p_class_category_use_rec IN OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
1262 x_return_status IN OUT NOCOPY VARCHAR2
1263 ) IS
1264
1265 l_rowid ROWID := NULL;
1266
1267 BEGIN
1268
1272 x_return_status);
1269 HZ_CLASS_VALIDATE_V2PUB.validate_class_category_use(
1270 p_class_category_use_rec,
1271 'C',
1273
1274 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1275 RAISE FND_API.G_EXC_ERROR;
1276 END IF;
1277
1278 HZ_CLASS_CATEGORY_USES_PKG.Insert_Row (
1279 X_CLASS_CATEGORY => p_class_category_use_rec.class_category,
1280 X_OWNER_TABLE => p_class_category_use_rec.owner_table,
1281 X_COLUMN_NAME => p_class_category_use_rec.column_name,
1282 X_ADDITIONAL_WHERE_CLAUSE => p_class_category_use_rec.additional_where_clause,
1283 X_OBJECT_VERSION_NUMBER => 1,
1284 X_CREATED_BY_MODULE => p_class_category_use_rec.created_by_module,
1285 X_APPLICATION_ID => p_class_category_use_rec.application_id
1286 );
1287
1288 END do_create_class_category_use;
1289
1290
1291 /*===========================================================================+
1292 | PROCEDURE
1293 | do_update_class_category_use
1294 |
1295 | DESCRIPTION
1296 | Updates class category use
1297 |
1298 | SCOPE - PRIVATE
1299 |
1300 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1301 |
1302 | ARGUMENTS : IN:
1303 | OUT:
1304 | IN/ OUT:
1305 | p_class_category_use_rec
1306 | p_last_update_date
1307 | x_return_status
1308 |
1309 | RETURNS : NONE
1310 |
1311 | NOTES
1312 |
1313 | MODIFICATION HISTORY
1314 |
1315 +===========================================================================*/
1316
1317 PROCEDURE do_update_class_category_use(
1318 p_class_category_use_rec IN OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
1319 p_object_version_number IN OUT NOCOPY NUMBER,
1320 x_return_status IN OUT NOCOPY VARCHAR2)
1321 IS
1322
1323 l_object_version_number NUMBER;
1324 l_rowid ROWID;
1325
1326 BEGIN
1327
1328 -- check whether record has been updated by another user. If not, lock it.
1329 BEGIN
1330 SELECT OBJECT_VERSION_NUMBER,
1331 ROWID
1332 INTO l_object_version_number,
1333 l_rowid
1334 FROM HZ_CLASS_CATEGORY_USES
1335 WHERE CLASS_CATEGORY = p_class_category_use_rec.class_category
1336 AND OWNER_TABLE = p_class_category_use_rec.owner_table
1337 FOR UPDATE OF CLASS_CATEGORY, OWNER_TABLE, COLUMN_NAME, ADDITIONAL_WHERE_CLAUSE NOWAIT;
1338
1339 IF NOT ((p_object_version_number is null and l_object_version_number is null)
1340 OR (p_object_version_number = l_object_version_number))
1341 THEN
1342 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1343 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_CLASS_CATEGORY_USES');
1344 FND_MSG_PUB.ADD;
1345 RAISE FND_API.G_EXC_ERROR;
1346 END IF;
1347
1348 p_object_version_number := nvl(l_object_version_number, 1) + 1;
1349
1350 EXCEPTION WHEN NO_DATA_FOUND THEN
1351 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1352 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_CLASS_CATEGORY_USES');
1353 FND_MESSAGE.SET_TOKEN('VALUE', 'p_class_category_use_rec.class_category');
1354 FND_MSG_PUB.ADD;
1355 RAISE FND_API.G_EXC_ERROR;
1356 END;
1357
1358 -- call for validations.
1359 HZ_CLASS_VALIDATE_V2PUB.validate_class_category_use(
1360 p_class_category_use_rec,
1361 'U',
1362 x_return_status);
1363
1364 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1365 RAISE FND_API.G_EXC_ERROR;
1366 END IF;
1367
1368 -- call to table-handler.
1369 HZ_CLASS_CATEGORY_USES_PKG.Update_Row (
1370 X_CLASS_CATEGORY => p_class_category_use_rec.class_category,
1371 X_OWNER_TABLE => p_class_category_use_rec.owner_table,
1372 X_COLUMN_NAME => p_class_category_use_rec.column_name,
1373 X_ADDITIONAL_WHERE_CLAUSE => p_class_category_use_rec.additional_where_clause,
1374 X_OBJECT_VERSION_NUMBER => p_object_version_number,
1375 X_CREATED_BY_MODULE => p_class_category_use_rec.created_by_module,
1376 X_APPLICATION_ID => p_class_category_use_rec.application_id
1377 );
1378
1379 END do_update_class_category_use;
1380
1381 --------------------------------------
1382 -- public procedures and functions
1383 --------------------------------------
1384
1385 /**
1386 * PROCEDURE create_class_category
1387 *
1388 * DESCRIPTION
1389 * Creates class category.
1390 *
1391 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1392 * HZ_BUSINESS_EVENT_V2PVT.create_class_category_event
1393 *
1394 * ARGUMENTS
1395 * IN:
1396 * p_init_msg_list Initialize message stack if it is set to
1397 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1398 * p_class_category_rec Class category record.
1399 * IN/OUT:
1400 * OUT:
1401 * x_return_status Return status after the call. The status can
1402 * be FND_API.G_RET_STS_SUCCESS (success),
1403 * FND_API.G_RET_STS_ERROR (error),
1407 *
1404 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1405 * x_msg_count Number of messages in message stack.
1406 * x_msg_data Message text if x_msg_count is 1.
1408 * NOTES
1409 *
1410 * MODIFICATION HISTORY
1411 *
1412 * 07-23-2001 Indrajit Sen o Created.
1413 *
1414 */
1415
1416 PROCEDURE create_class_category(
1417 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1418 p_class_category_rec IN CLASS_CATEGORY_REC_TYPE,
1419 x_return_status OUT NOCOPY VARCHAR2,
1420 x_msg_count OUT NOCOPY NUMBER,
1421 x_msg_data OUT NOCOPY VARCHAR2
1422 ) IS
1423
1424 l_class_cat_rec CLASS_CATEGORY_REC_TYPE:= p_class_category_rec;
1425
1426 BEGIN
1427
1428 -- standard start of API savepoint
1429 SAVEPOINT create_class_category;
1430
1431 -- initialize message list if p_init_msg_list is set to TRUE.
1432 IF FND_API.to_Boolean(p_init_msg_list) THEN
1433 FND_MSG_PUB.initialize;
1434 END IF;
1435
1436 -- initialize API return status to success.
1437 x_return_status := FND_API.G_RET_STS_SUCCESS;
1438
1439 -- call to business logic.
1440 do_create_class_category(
1441 l_class_cat_rec,
1442 x_return_status);
1443
1444 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1445
1446 -- Invoke business event system.
1447 --Bug 4743141.
1448 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
1449 HZ_BUSINESS_EVENT_V2PVT.create_class_category_event (
1450 l_class_cat_rec );
1451 END IF;
1452
1453 END IF;
1454
1455 -- standard call to get message count and if count is 1, get message info.
1456 FND_MSG_PUB.Count_And_Get(
1457 p_encoded => FND_API.G_FALSE,
1458 p_count => x_msg_count,
1459 p_data => x_msg_data);
1460
1461 EXCEPTION
1462 WHEN FND_API.G_EXC_ERROR THEN
1463 ROLLBACK TO create_class_category;
1464 x_return_status := FND_API.G_RET_STS_ERROR;
1465 FND_MSG_PUB.Count_And_Get(
1466 p_encoded => FND_API.G_FALSE,
1467 p_count => x_msg_count,
1468 p_data => x_msg_data);
1469
1470 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1471 ROLLBACK TO create_class_category;
1472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1473 FND_MSG_PUB.Count_And_Get(
1474 p_encoded => FND_API.G_FALSE,
1475 p_count => x_msg_count,
1476 p_data => x_msg_data);
1477
1478 WHEN OTHERS THEN
1479 ROLLBACK TO create_class_category;
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1482 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1483 FND_MSG_PUB.ADD;
1484 FND_MSG_PUB.Count_And_Get(
1485 p_encoded => FND_API.G_FALSE,
1486 p_count => x_msg_count,
1487 p_data => x_msg_data);
1488 END create_class_category;
1489
1490 /**
1491 * PROCEDURE update_class_category
1492 *
1493 * DESCRIPTION
1494 * Updates class category.
1495 *
1496 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1497 * HZ_BUSINESS_EVENT_V2PVT.update_class_category_event
1498 *
1499 * ARGUMENTS
1500 * IN:
1501 * p_init_msg_list Initialize message stack if it is set to
1502 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1503 * p_class_category_rec Class category record.
1504 * IN/OUT:
1505 * p_object_version_number Used for locking the being updated record.
1506 * OUT:
1507 * x_return_status Return status after the call. The status can
1508 * be FND_API.G_RET_STS_SUCCESS (success),
1509 * FND_API.G_RET_STS_ERROR (error),
1510 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1511 * x_msg_count Number of messages in message stack.
1512 * x_msg_data Message text if x_msg_count is 1.
1513 *
1514 * NOTES
1515 *
1516 * MODIFICATION HISTORY
1517 *
1518 * 07-23-2001 Indrajit Sen o Created.
1519 *
1520 */
1521
1522 PROCEDURE update_class_category (
1523 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1524 p_class_category_rec IN CLASS_CATEGORY_REC_TYPE,
1525 p_object_version_number IN OUT NOCOPY NUMBER,
1526 x_return_status OUT NOCOPY VARCHAR2,
1527 x_msg_count OUT NOCOPY NUMBER,
1528 x_msg_data OUT NOCOPY VARCHAR2
1529 ) IS
1530
1531 l_class_cat_rec CLASS_CATEGORY_REC_TYPE := p_class_category_rec;
1532 l_old_class_cat_rec CLASS_CATEGORY_REC_TYPE;
1533
1534 BEGIN
1535
1536 -- standard start of API savepoint
1537 SAVEPOINT update_class_category;
1538
1539 -- initialize message list if p_init_msg_list is set to TRUE.
1540 IF FND_API.to_Boolean(p_init_msg_list) THEN
1541 FND_MSG_PUB.initialize;
1542 END IF;
1543
1544 -- initialize API return status to success.
1545 x_return_status := FND_API.G_RET_STS_SUCCESS;
1546
1547 -- Get old records. Will be used by business event system.
1548 get_class_category_rec (
1552 x_msg_count => x_msg_count,
1549 p_class_category => l_class_cat_rec.class_category,
1550 x_class_category_rec => l_old_class_cat_rec,
1551 x_return_status => x_return_status,
1553 x_msg_data => x_msg_data );
1554
1555 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1556 RAISE FND_API.G_EXC_ERROR;
1557 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1559 END IF;
1560
1561 -- call to business logic.
1562 do_update_class_category(
1563 l_class_cat_rec,
1564 p_object_version_number,
1565 x_return_status);
1566
1567 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1568
1569 -- Invoke business event system.
1570 --Bug 4743141.
1571 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
1572 HZ_BUSINESS_EVENT_V2PVT.update_class_category_event (
1573 l_class_cat_rec,
1574 l_old_class_cat_rec );
1575 END IF;
1576
1577 END IF;
1578
1579 -- standard call to get message count and if count is 1, get message info.
1580 FND_MSG_PUB.Count_And_Get(
1581 p_encoded => FND_API.G_FALSE,
1582 p_count => x_msg_count,
1583 p_data => x_msg_data);
1584
1585 EXCEPTION
1586 WHEN FND_API.G_EXC_ERROR THEN
1587 ROLLBACK TO update_class_category;
1588 x_return_status := FND_API.G_RET_STS_ERROR;
1589 FND_MSG_PUB.Count_And_Get(
1590 p_encoded => FND_API.G_FALSE,
1591 p_count => x_msg_count,
1592 p_data => x_msg_data);
1593
1594 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1595 ROLLBACK TO update_class_category;
1596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1597 FND_MSG_PUB.Count_And_Get(
1598 p_encoded => FND_API.G_FALSE,
1599 p_count => x_msg_count,
1600 p_data => x_msg_data);
1601
1602 WHEN OTHERS THEN
1603 ROLLBACK TO update_class_category;
1604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1605 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1606 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1607 FND_MSG_PUB.ADD;
1608 FND_MSG_PUB.Count_And_Get(
1609 p_encoded => FND_API.G_FALSE,
1610 p_count => x_msg_count,
1611 p_data => x_msg_data);
1612
1613 END update_class_category;
1614
1615 /**
1616 * PROCEDURE create_class_code_relation
1617 *
1618 * DESCRIPTION
1619 * Creates class code relationship.
1620 *
1621 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1622 * HZ_BUSINESS_EVENT_V2PVT.create_class_code_rel_event
1623 *
1624 * ARGUMENTS
1625 * IN:
1626 * p_init_msg_list Initialize message stack if it is set to
1627 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1628 * p_class_code_relation_rec Class code relation record.
1629 * IN/OUT:
1630 * OUT:
1631 * x_return_status Return status after the call. The status can
1632 * be FND_API.G_RET_STS_SUCCESS (success),
1633 * FND_API.G_RET_STS_ERROR (error),
1634 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1635 * x_msg_count Number of messages in message stack.
1636 * x_msg_data Message text if x_msg_count is 1.
1637 *
1638 * NOTES
1639 *
1640 * MODIFICATION HISTORY
1641 *
1642 * 07-23-2001 Indrajit Sen o Created.
1643 *
1644 */
1645
1646 PROCEDURE create_class_code_relation(
1647 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1648 p_class_code_relation_rec IN CLASS_CODE_RELATION_REC_TYPE,
1649 x_return_status OUT NOCOPY VARCHAR2,
1650 x_msg_count OUT NOCOPY NUMBER,
1651 x_msg_data OUT NOCOPY VARCHAR2
1652 ) IS
1653
1654 l_class_code_rel_rec CLASS_CODE_RELATION_REC_TYPE := p_class_code_relation_rec;
1655
1656 BEGIN
1657
1658 -- standard start of API savepoint
1659 SAVEPOINT create_class_code_relation;
1660
1661 -- initialize message list if p_init_msg_list is set to TRUE.
1662 IF FND_API.to_Boolean(p_init_msg_list) THEN
1663 FND_MSG_PUB.initialize;
1664 END IF;
1665
1666 -- initialize API return status to success.
1667 x_return_status := FND_API.G_RET_STS_SUCCESS;
1668 -- call to business logic.
1669 do_create_class_code_relation(
1670 l_class_code_rel_rec,
1671 x_return_status);
1672
1673 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1674
1675 -- Invoke business event system.
1676 --Bug 4743141.
1677 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
1678 HZ_BUSINESS_EVENT_V2PVT.create_class_code_rel_event (
1679 l_class_code_rel_rec );
1680 END IF;
1681
1682 END IF;
1683
1684 -- standard call to get message count and if count is 1, get message info.
1685 FND_MSG_PUB.Count_And_Get(
1686 p_encoded => FND_API.G_FALSE,
1687 p_count => x_msg_count,
1688 p_data => x_msg_data);
1689
1690 EXCEPTION
1691 WHEN FND_API.G_EXC_ERROR THEN
1692 ROLLBACK TO create_class_code_relation;
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694 FND_MSG_PUB.Count_And_Get(
1695 p_encoded => FND_API.G_FALSE,
1696 p_count => x_msg_count,
1697 p_data => x_msg_data);
1698
1699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1700 ROLLBACK TO create_class_code_relation;
1701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1702 FND_MSG_PUB.Count_And_Get(
1703 p_encoded => FND_API.G_FALSE,
1704 p_count => x_msg_count,
1705 p_data => x_msg_data);
1706
1707 WHEN OTHERS THEN
1708 ROLLBACK TO create_class_code_relation;
1709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1710 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1711 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1712 FND_MSG_PUB.ADD;
1713 FND_MSG_PUB.Count_And_Get(
1714 p_encoded => FND_API.G_FALSE,
1715 p_count => x_msg_count,
1716 p_data => x_msg_data);
1717 END create_class_code_relation;
1718
1719 /**
1720 * PROCEDURE update_class_code_relation
1721 *
1722 * DESCRIPTION
1723 * Updates class code relation.
1724 *
1725 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1726 * HZ_BUSINESS_EVENT_V2PVT.update_class_code_rel_event
1727 *
1728 * ARGUMENTS
1729 * IN:
1730 * p_init_msg_list Initialize message stack if it is set to
1731 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1732 * p_class_code_relation_rec Class code relation record.
1733 * IN/OUT:
1734 * p_object_version_number Used for locking the being updated record.
1735 * OUT:
1736 * x_return_status Return status after the call. The status can
1737 * be FND_API.G_RET_STS_SUCCESS (success),
1738 * FND_API.G_RET_STS_ERROR (error),
1739 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1740 * x_msg_count Number of messages in message stack.
1741 * x_msg_data Message text if x_msg_count is 1.
1742 *
1743 * NOTES
1744 *
1745 * MODIFICATION HISTORY
1746 *
1747 * 07-23-2001 Indrajit Sen o Created.
1748 *
1749 */
1750
1751 PROCEDURE update_class_code_relation(
1752 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1753 p_class_code_relation_rec IN CLASS_CODE_RELATION_REC_TYPE,
1754 p_object_version_number IN OUT NOCOPY NUMBER,
1755 x_return_status OUT NOCOPY VARCHAR2,
1756 x_msg_count OUT NOCOPY NUMBER,
1757 x_msg_data OUT NOCOPY VARCHAR2
1758 ) IS
1759
1760 l_class_code_rel_rec CLASS_CODE_RELATION_REC_TYPE:= p_class_code_relation_rec;
1761 l_old_class_code_rel_rec CLASS_CODE_RELATION_REC_TYPE;
1762
1763 BEGIN
1764
1765 -- standard start of API savepoint
1766 SAVEPOINT update_class_code_relation;
1767
1768 -- initialize message list if p_init_msg_list is set to TRUE.
1769 IF FND_API.to_Boolean(p_init_msg_list) THEN
1770 FND_MSG_PUB.initialize;
1771 END IF;
1772
1773 -- initialize API return status to success.
1774 x_return_status := FND_API.G_RET_STS_SUCCESS;
1775
1776 -- Get old records. Will be used by business event system.
1777 get_class_code_relation_rec (
1778 p_class_category => l_class_code_rel_rec.class_category,
1779 p_class_code => l_class_code_rel_rec.class_code,
1780 p_sub_class_code => l_class_code_rel_rec.sub_class_code,
1781 p_start_date_active => l_class_code_rel_rec.start_date_active,
1782 x_class_code_relation_rec => l_old_class_code_rel_rec,
1783 x_return_status => x_return_status,
1784 x_msg_count => x_msg_count,
1785 x_msg_data => x_msg_data );
1786
1787 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1788 RAISE FND_API.G_EXC_ERROR;
1789 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1791 END IF;
1792
1793 -- call to business logic.
1797 x_return_status);
1794 do_update_class_code_relation(
1795 l_class_code_rel_rec,
1796 p_object_version_number,
1798
1799 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1800
1801 -- Invoke business event system.
1802 --Bug 4743141.
1803 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
1804 HZ_BUSINESS_EVENT_V2PVT.update_class_code_rel_event (
1805 l_class_code_rel_rec,
1806 l_old_class_code_rel_rec );
1807 END IF;
1808
1809 END IF;
1810
1811 -- standard call to get message count and if count is 1, get message info.
1812 FND_MSG_PUB.Count_And_Get(
1813 p_encoded => FND_API.G_FALSE,
1814 p_count => x_msg_count,
1815 p_data => x_msg_data);
1816
1817 EXCEPTION
1818 WHEN FND_API.G_EXC_ERROR THEN
1819 ROLLBACK TO update_class_code_relation;
1820 x_return_status := FND_API.G_RET_STS_ERROR;
1821 FND_MSG_PUB.Count_And_Get(
1822 p_encoded => FND_API.G_FALSE,
1823 p_count => x_msg_count,
1824 p_data => x_msg_data);
1825
1826 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1827 ROLLBACK TO update_class_code_relation;
1828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1829 FND_MSG_PUB.Count_And_Get(
1830 p_encoded => FND_API.G_FALSE,
1831 p_count => x_msg_count,
1832 p_data => x_msg_data);
1833
1834 WHEN OTHERS THEN
1835 ROLLBACK TO update_class_code_relation;
1836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1837 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1838 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1839 FND_MSG_PUB.ADD;
1840 FND_MSG_PUB.Count_And_Get(
1841 p_encoded => FND_API.G_FALSE,
1842 p_count => x_msg_count,
1843 p_data => x_msg_data);
1844
1845 END update_class_code_relation;
1846
1847 /**
1848 * PROCEDURE create_code_assignment
1849 *
1850 * DESCRIPTION
1851 * Creates code assignment.
1852 *
1853 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1854 * HZ_BUSINESS_EVENT_V2PVT.create_code_assignment_event
1855 *
1856 * ARGUMENTS
1857 * IN:
1858 * p_init_msg_list Initialize message stack if it is set to
1859 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1860 * p_code_assignement_rec Code assignment record.
1861 * IN/OUT:
1862 * OUT:
1863 * x_return_status Return status after the call. The status can
1864 * be FND_API.G_RET_STS_SUCCESS (success),
1865 * FND_API.G_RET_STS_ERROR (error),
1866 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1867 * x_msg_count Number of messages in message stack.
1868 * x_msg_data Message text if x_msg_count is 1.
1869 * x_code_assignment_id Code assignment ID.
1870 *
1871 * NOTES
1872 *
1873 * MODIFICATION HISTORY
1874 *
1875 * 07-23-2001 Indrajit Sen o Created.
1876 * 01-05-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
1877 * New column ACTUAL_CONTENT_SOURCE is
1878 * added in HZ_CODE_ASSIGNMENTS.
1879 * Called HZ_MIXNM_UTILITY.AssignDataSourceDuringCreation
1880 * to check for user creation privilege and
1881 * to ensure that proper values are set to
1882 * content_source_type / actual_content_source.
1883
1884
1885 */
1886
1887 PROCEDURE create_code_assignment(
1888 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1889 p_code_assignment_rec IN CODE_ASSIGNMENT_REC_TYPE,
1890 x_return_status OUT NOCOPY VARCHAR2,
1891 x_msg_count OUT NOCOPY NUMBER,
1892 x_msg_data OUT NOCOPY VARCHAR2,
1893 x_code_assignment_id OUT NOCOPY NUMBER
1894 )
1895 IS
1896
1897 l_code_assignment_rec CODE_ASSIGNMENT_REC_TYPE:= p_code_assignment_rec;
1898 l_entity_attr_id NUMBER;
1899 l_is_datasource_selected VARCHAR2(1);
1900
1901 BEGIN
1902
1903 -- standard start of API savepoint
1904 SAVEPOINT create_code_assignment;
1905
1906 -- initialize message list if p_init_msg_list is set to TRUE.
1907 IF FND_API.to_Boolean(p_init_msg_list) THEN
1908 FND_MSG_PUB.initialize;
1909 END IF;
1910
1911 -- initialize API return status to success.
1912 x_return_status := FND_API.G_RET_STS_SUCCESS;
1913
1914 -- SSM SST Integration and Extension
1915 HZ_MIXNM_UTILITY.AssignDataSourceDuringCreation (
1916 p_entity_name => 'HZ_CODE_ASSIGNMENTS',
1917 p_entity_attr_id => l_entity_attr_id ,
1918 p_mixnmatch_enabled => NULL,
1919 p_selected_datasources => NULL,
1920 p_content_source_type => l_code_assignment_rec.content_source_type,
1921 p_actual_content_source => l_code_assignment_rec.actual_content_source,
1922 x_is_datasource_selected => l_is_datasource_selected,
1926
1923 x_return_status => x_return_status,
1924 p_api_version => 'V2'
1925 );
1927 -- call to business logic.
1928 do_create_code_assignment(
1929 l_code_assignment_rec,
1930 x_return_status);
1931
1932 -- assign out NOCOPY param
1933 x_code_assignment_id := l_code_assignment_rec.code_assignment_id;
1934
1935 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1936 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
1937 -- Invoke business event system.
1938 HZ_BUSINESS_EVENT_V2PVT.create_code_assignment_event (
1939 l_code_assignment_rec );
1940 END IF;
1941
1942 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1943 HZ_POPULATE_BOT_PKG.pop_hz_code_assignments(
1944 p_operation => 'I',
1945 p_code_assignment_id => x_code_assignment_id);
1946 END IF;
1947 END IF;
1948
1949 -- standard call to get message count and if count is 1, get message info.
1950 FND_MSG_PUB.Count_And_Get(
1951 p_encoded => FND_API.G_FALSE,
1952 p_count => x_msg_count,
1953 p_data => x_msg_data);
1954
1955 EXCEPTION
1956 WHEN FND_API.G_EXC_ERROR THEN
1957 ROLLBACK TO create_code_assignment;
1958 x_return_status := FND_API.G_RET_STS_ERROR;
1959 FND_MSG_PUB.Count_And_Get(
1960 p_encoded => FND_API.G_FALSE,
1961 p_count => x_msg_count,
1962 p_data => x_msg_data);
1963
1964 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1965 ROLLBACK TO create_code_assignment;
1966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1967 FND_MSG_PUB.Count_And_Get(
1968 p_encoded => FND_API.G_FALSE,
1969 p_count => x_msg_count,
1970 p_data => x_msg_data);
1971
1972 WHEN OTHERS THEN
1973 ROLLBACK TO create_code_assignment;
1974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1975 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1976 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1977 FND_MSG_PUB.ADD;
1978 FND_MSG_PUB.Count_And_Get(
1979 p_encoded => FND_API.G_FALSE,
1980 p_count => x_msg_count,
1981 p_data => x_msg_data);
1982 END create_code_assignment;
1983
1984 /**
1985 * PROCEDURE update_code_assignment
1986 *
1987 * DESCRIPTION
1988 * Updates code assignment.
1989 *
1990 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1991 * HZ_BUSINESS_EVENT_V2PVT.update_code_assignment_event
1992 *
1993 * ARGUMENTS
1994 * IN:
1995 * p_init_msg_list Initialize message stack if it is set to
1996 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1997 * p_code_assignment_rec Code assignment record.
1998 * IN/OUT:
1999 * p_object_version_number Used for locking the being updated record.
2000 * OUT:
2001 * x_return_status Return status after the call. The status can
2002 * be FND_API.G_RET_STS_SUCCESS (success),
2003 * FND_API.G_RET_STS_ERROR (error),
2004 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2005 * x_msg_count Number of messages in message stack.
2006 * x_msg_data Message text if x_msg_count is 1.
2007 *
2008 * NOTES
2009 *
2010 * MODIFICATION HISTORY
2011 *
2012 * 07-23-2001 Indrajit Sen o Created.
2013 * 29-MAY-2003 Jyoti Pandey o Make the API protected by Data security Bug 2963010
2014 */
2015
2016 PROCEDURE update_code_assignment (
2017 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
2018 p_code_assignment_rec IN CODE_ASSIGNMENT_REC_TYPE,
2019 p_object_version_number IN OUT NOCOPY NUMBER,
2020 x_return_status OUT NOCOPY VARCHAR2,
2021 x_msg_count OUT NOCOPY NUMBER,
2022 x_msg_data OUT NOCOPY VARCHAR2
2023 ) IS
2024
2025 l_code_assignment_rec CODE_ASSIGNMENT_REC_TYPE := p_code_assignment_rec;
2026 l_old_code_assignment_rec CODE_ASSIGNMENT_REC_TYPE;
2027
2028 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2029 dss_msg_count NUMBER := 0;
2030 dss_msg_data VARCHAR2(2000):= null;
2031 l_test_security VARCHAR2(1):= 'F';
2032
2033 BEGIN
2034
2035 -- standard start of API savepoint
2036 SAVEPOINT update_code_assignment;
2037
2038 -- initialize message list if p_init_msg_list is set to TRUE.
2039 IF FND_API.to_Boolean(p_init_msg_list) THEN
2040 FND_MSG_PUB.initialize;
2041 END IF;
2042
2043 -- initialize API return status to success.
2044 x_return_status := FND_API.G_RET_STS_SUCCESS;
2045
2046 -- Get old records. Will be used by business event system.
2047 get_code_assignment_rec (
2048 p_code_assignment_id => l_code_assignment_rec.code_assignment_id,
2049 x_code_assignment_rec => l_old_code_assignment_rec,
2050 x_return_status => x_return_status,
2051 x_msg_count => x_msg_count,
2052 x_msg_data => x_msg_data );
2053
2054 -- Bug:2154581
2055 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2056 IF l_code_assignment_rec.start_date_active IS NULL OR
2060 END IF;
2057 l_code_assignment_rec.start_date_active = FND_API.G_MISS_DATE THEN
2058 l_code_assignment_rec.start_date_active := l_old_code_assignment_rec.start_date_active;
2059 END IF;
2061 --
2062 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2063 RAISE FND_API.G_EXC_ERROR;
2064 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2065 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2066 END IF;
2067
2068 ---Bug 2963010 make table HZ_CODE_ASSIGNMENTS protected by Data Security
2069 ---Check if the DSS security is granted to the user
2070 --
2071 -- Bug 3818648: do dss check in party context only. check dss
2072 -- profile before call test_instance.
2073 --
2074 IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' AND
2075 l_old_code_assignment_rec.owner_table_name = 'HZ_PARTIES'
2076 THEN
2077 l_test_security :=
2078 hz_dss_util_pub.test_instance(
2079 p_operation_code => 'UPDATE',
2080 p_db_object_name => 'HZ_CODE_ASSIGNMENTS',
2081 p_instance_pk1_value => l_code_assignment_rec.code_assignment_id,
2082 p_user_name => fnd_global.user_name,
2083 x_return_status => dss_return_status,
2084 x_msg_count => dss_msg_count,
2085 x_msg_data => dss_msg_data);
2086
2087 if dss_return_status <> fnd_api.g_ret_sts_success THEN
2088 RAISE FND_API.G_EXC_ERROR;
2089 end if;
2090
2091 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
2092 --
2093 -- Bug 3835601: replaced the dss message with a more user friendly message
2094 --
2095 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
2096 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
2097 hz_dss_util_pub.get_display_name('HZ_CODE_ASSIGNMENTS', null)); FND_MSG_PUB.ADD;
2098 RAISE FND_API.G_EXC_ERROR;
2099 end if;
2100 END IF;
2101
2102 --Call to business logic.
2103 do_update_code_assignment(
2104 l_code_assignment_rec,
2105 p_object_version_number,
2106 x_return_status);
2107
2108 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2109 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
2110 -- Invoke business event system.
2111 HZ_BUSINESS_EVENT_V2PVT.update_code_assignment_event (
2112 l_code_assignment_rec,
2113 l_old_code_assignment_rec );
2114 END IF;
2115
2116 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2117 HZ_POPULATE_BOT_PKG.pop_hz_code_assignments(
2118 p_operation => 'U',
2119 p_code_assignment_id => l_code_assignment_rec.code_assignment_id);
2120 END IF;
2121 END IF;
2122
2123 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
2124 -- standard call to get message count and if count is 1, get message info.
2125 FND_MSG_PUB.Count_And_Get(
2126 p_encoded => FND_API.G_FALSE,
2127 p_count => x_msg_count,
2128 p_data => x_msg_data);
2129
2130 EXCEPTION
2131 WHEN FND_API.G_EXC_ERROR THEN
2132 ROLLBACK TO update_code_assignment;
2133 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
2134 x_return_status := FND_API.G_RET_STS_ERROR;
2135 FND_MSG_PUB.Count_And_Get(
2136 p_encoded => FND_API.G_FALSE,
2137 p_count => x_msg_count,
2138 p_data => x_msg_data);
2139
2140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2141 ROLLBACK TO update_code_assignment;
2142 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
2143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2144 FND_MSG_PUB.Count_And_Get(
2145 p_encoded => FND_API.G_FALSE,
2146 p_count => x_msg_count,
2147 p_data => x_msg_data);
2148
2149 WHEN OTHERS THEN
2150 ROLLBACK TO update_code_assignment;
2151 HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
2152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2153 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2154 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2155 FND_MSG_PUB.ADD;
2156 FND_MSG_PUB.Count_And_Get(
2157 p_encoded => FND_API.G_FALSE,
2158 p_count => x_msg_count,
2159 p_data => x_msg_data);
2160
2161 END update_code_assignment;
2162
2163 /**
2164 * PROCEDURE set_primary_code_assignment
2165 *
2166 * DESCRIPTION
2167 * Sets primary code assignment.
2168 *
2169 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2170 *
2171 * ARGUMENTS
2172 * IN:
2173 * p_init_msg_list Initialize message stack if it is set to
2174 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2175 * p_owner_table_name Owner table name.
2176 * p_owner_table_id Owner table ID.
2177 * p_class_category Class category.
2178 * p_class_code Class code.
2179 * p_content_source_type Contact source type.
2180 * p_created_by_module Created_by_module
2181 * IN/OUT:
2182 * OUT:
2183 * x_code_assignment_id Code assignment ID.
2184 * x_return_status Return status after the call. The status can
2185 * be FND_API.G_RET_STS_SUCCESS (success),
2186 * FND_API.G_RET_STS_ERROR (error),
2190 *
2187 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2188 * x_msg_count Number of messages in message stack.
2189 * x_msg_data Message text if x_msg_count is 1.
2191 * NOTES
2192 *
2193 * MODIFICATION HISTORY
2194 *
2195 * 07-23-2001 Indrajit Sen o Created.
2196 * 12-Sep-2008 Sudhir Gokavarapu o Modified Bug 6982657.
2197 *
2198 */
2199
2200 PROCEDURE set_primary_code_assignment(
2201 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2202 p_owner_table_name IN VARCHAR2,
2203 p_owner_table_id IN NUMBER,
2204 p_class_category IN VARCHAR2,
2205 p_class_code IN VARCHAR2,
2206 p_content_source_type IN VARCHAR2,
2207 p_created_by_module IN VARCHAR2, /* Bug 3856348 */
2208 x_code_assignment_id OUT NOCOPY NUMBER,
2209 x_return_status OUT NOCOPY VARCHAR2,
2210 x_msg_count OUT NOCOPY NUMBER,
2211 x_msg_data OUT NOCOPY VARCHAR2
2212 ) IS
2213
2214 l_count NUMBER;
2215 l_rec HZ_CODE_ASSIGNMENTS%ROWTYPE;
2216 l_code_assign_rec CODE_ASSIGNMENT_REC_TYPE;
2217
2218 CURSOR c_code_assign(
2219 p_owner_table_name IN VARCHAR2
2220 , p_owner_table_id IN NUMBER
2221 , p_class_category IN VARCHAR2
2222 , p_content_source_type IN VARCHAR2
2223 )
2224 IS SELECT * FROM HZ_CODE_ASSIGNMENTS
2225 WHERE
2226 owner_table_name = p_owner_table_name AND
2227 owner_table_id = p_owner_table_id AND
2228 class_category = p_class_category AND
2229 actual_content_source = NVL(p_content_source_type, HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE) AND
2230 (end_date_active is null
2231 OR sysdate between start_date_active and end_date_active);
2232
2233 --FOR UPDATE OF end_date_active;
2234
2235 BEGIN
2236 --Standard start of API savepoint
2237 SAVEPOINT set_primary_code_assign;
2238
2239 --Initialize message list if p_init_msg_list is set to TRUE.
2240 IF FND_API.to_Boolean(p_init_msg_list) THEN
2241 FND_MSG_PUB.initialize;
2242 END IF;
2243
2244 --Initialize API return status to success.
2245 x_return_status := FND_API.G_RET_STS_SUCCESS;
2246
2247 -- the assignment exists?
2248 l_count := 0;
2249 OPEN c_code_assign(
2250 p_owner_table_name
2251 , p_owner_table_id
2252 , p_class_category
2253 , p_content_source_type
2254 );
2255 LOOP
2256 FETCH c_code_assign INTO l_rec;
2257 EXIT WHEN c_code_assign%NOTFOUND;
2258 IF (l_rec.PRIMARY_FLAG = 'Y') AND
2259 (l_rec.class_code = p_class_code)
2260 AND
2261 (
2262 (l_rec.end_date_active IS NULL) OR
2263 (l_rec.end_date_active > SYSDATE)
2264 )
2265 THEN
2266 -- AN ACTIVE ONE EXISTS
2267 l_count := l_count + 1;
2268 x_code_assignment_id := l_rec.code_assignment_id;
2269 EXIT;
2270 END IF;
2271
2272 IF (l_rec.PRIMARY_FLAG = 'Y' AND
2273 l_rec.class_code <> p_class_code)
2274 THEN
2275 -- terminate original primary assignment
2276 UPDATE HZ_CODE_ASSIGNMENTS SET
2277 --end_date_active = l_rec.start_date_active
2278
2279 -- Bug 3614582 : end date with sysdate - (1 second)
2280 end_date_active = SYSDATE - 1/(24*60*60)
2281 WHERE code_assignment_id = l_rec.code_assignment_id;
2282 --WHERE CURRENT OF c_code_assign;
2283
2284 -- Bug 3876180
2285 IF(p_class_code=fnd_api.g_miss_char)
2286 THEN
2287 l_count := l_count + 1;
2288 x_code_assignment_id := l_rec.code_assignment_id;
2289 END IF;
2290 END IF;
2291
2292 IF (l_rec.PRIMARY_FLAG = 'N' AND
2293 l_rec.class_code = p_class_code)
2294 THEN
2295 -- terminate original non-primary assignment
2296 UPDATE HZ_CODE_ASSIGNMENTS SET
2297 --end_date_active = l_rec.start_date_active
2298 -- Bug 3614582 : end date with sysdate - (1 second)
2299 end_date_active = SYSDATE - 1/(24*60*60)
2300 WHERE code_assignment_id = l_rec.code_assignment_id;
2301 --WHERE CURRENT OF c_code_assign;
2302 END IF;
2303 END LOOP;
2304 CLOSE c_code_assign;
2305 -- Bug 6982657
2306 IF (l_count = 0) AND (NVL(p_class_code,fnd_api.g_miss_char) <> fnd_api.g_miss_char)
2307 THEN
2308 l_code_assign_rec.owner_table_name := p_owner_table_name;
2309 l_code_assign_rec.owner_table_id := p_owner_table_id;
2310 l_code_assign_rec.class_category := p_class_category;
2311 l_code_assign_rec.class_code := p_class_code;
2312 l_code_assign_rec.primary_flag := 'Y';
2313 l_code_assign_rec.actual_content_source := p_content_source_type;
2314 l_code_assign_rec.start_date_active := SYSDATE;
2315 l_code_assign_rec.end_date_active := NULL;
2316 -- Bug 3856348
2317 l_code_assign_rec.created_by_module := p_created_by_module;
2318
2319 do_create_code_assignment(
2320 l_code_assign_rec,
2321 x_return_status);
2322 -- assign out NOCOPY param
2323 x_code_assignment_id := l_code_assign_rec.code_assignment_id;
2324 ELSE
2325 -- already created, skip the call
2326 NULL;
2327 END IF;
2328
2329 --Standard call to get message count and if count is 1, get message info.
2333 p_data => x_msg_data);
2330 FND_MSG_PUB.Count_And_Get(
2331 p_encoded => FND_API.G_FALSE,
2332 p_count => x_msg_count,
2334
2335 EXCEPTION
2336 WHEN FND_API.G_EXC_ERROR THEN
2337 ROLLBACK TO set_primary_code_assign;
2338 x_return_status := FND_API.G_RET_STS_ERROR;
2339 FND_MSG_PUB.Count_And_Get(
2340 p_encoded => FND_API.G_FALSE,
2341 p_count => x_msg_count,
2342 p_data => x_msg_data);
2343
2344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2345 ROLLBACK TO set_primary_code_assign;
2346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2347 FND_MSG_PUB.Count_And_Get(
2348 p_encoded => FND_API.G_FALSE,
2349 p_count => x_msg_count,
2350 p_data => x_msg_data);
2351
2352 WHEN OTHERS THEN
2353 ROLLBACK TO set_primary_code_assign;
2354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2355 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2356 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2357 FND_MSG_PUB.ADD;
2358 FND_MSG_PUB.Count_And_Get(
2359 p_encoded => FND_API.G_FALSE,
2360 p_count => x_msg_count,
2361 p_data => x_msg_data);
2362 END;
2363
2364 /**
2365 * PROCEDURE create_class_category_use
2366 *
2367 * DESCRIPTION
2368 * Creates class category use.
2369 *
2370 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2371 * HZ_BUSINESS_EVENT_V2PVT.create_class_cat_use_event
2372 *
2373 * ARGUMENTS
2374 * IN:
2375 * p_init_msg_list Initialize message stack if it is set to
2376 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2377 * p_class_category_use_rec Class category use record.
2378 * IN/OUT:
2379 * OUT:
2380 * x_return_status Return status after the call. The status can
2381 * be FND_API.G_RET_STS_SUCCESS (success),
2382 * FND_API.G_RET_STS_ERROR (error),
2383 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2384 * x_msg_count Number of messages in message stack.
2385 * x_msg_data Message text if x_msg_count is 1.
2386 *
2387 * NOTES
2388 *
2389 * MODIFICATION HISTORY
2390 *
2391 * 07-23-2001 Indrajit Sen o Created.
2392 *
2393 */
2394
2395 PROCEDURE create_class_category_use(
2396 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2397 p_class_category_use_rec IN CLASS_CATEGORY_USE_REC_TYPE,
2398 x_return_status OUT NOCOPY VARCHAR2,
2399 x_msg_count OUT NOCOPY NUMBER,
2400 x_msg_data OUT NOCOPY VARCHAR2
2401 ) IS
2402
2403 l_class_category_use_rec CLASS_CATEGORY_USE_REC_TYPE := p_class_category_use_rec;
2404
2405 BEGIN
2406
2407 -- standard save point
2408 SAVEPOINT create_class_category_use;
2409
2410 -- initialize message list if p_init_msg_list is TRUE
2411 IF FND_API.to_Boolean(p_init_msg_list) THEN
2412 FND_MSG_PUB.INITIALIZE;
2413 END IF;
2414
2415 -- initialize API return status to SUCCESS
2416 x_return_status := FND_API.G_RET_STS_SUCCESS;
2417
2418 -- call to business logic
2419 do_create_class_category_use(
2420 l_class_category_use_rec,
2421 x_return_status);
2422
2423 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2424
2425 -- Invoke business event system.
2426 --Bug 4743141.
2427 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
2428 HZ_BUSINESS_EVENT_V2PVT.create_class_cat_use_event (
2429 l_class_category_use_rec );
2430 END IF;
2431
2432 END IF;
2433
2434 -- standard Call to get message count and if count is 1 get message info.
2435 FND_MSG_PUB.count_and_get(
2436 p_encoded => fnd_api.g_false,
2437 p_count => x_msg_count,
2438 p_data => x_msg_data );
2439
2440 EXCEPTION
2441 WHEN FND_API.G_EXC_ERROR THEN
2442 ROLLBACK TO create_class_category_use;
2443 x_return_status := FND_API.G_RET_STS_ERROR;
2444 FND_MSG_PUB.Count_And_Get(
2445 p_encoded => FND_API.G_FALSE,
2446 p_count => x_msg_count,
2447 p_data => x_msg_data );
2448
2449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2450 ROLLBACK TO create_class_category_use;
2451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2452 FND_MSG_PUB.Count_And_Get(
2453 p_encoded => FND_API.G_FALSE,
2454 p_count => x_msg_count,
2455 p_data => x_msg_data );
2456
2457 WHEN OTHERS THEN
2458 ROLLBACK TO create_class_category_use;
2459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2460 FND_MESSAGE.Set_Name('AR','HZ_API_OTHERS_EXCEP');
2461 FND_MESSAGE.Set_Token('ERROR',SQLERRM);
2462 FND_MSG_PUB.Count_And_Get(
2463 p_encoded => FND_API.G_FALSE,
2464 p_count => x_msg_count,
2465 p_data => x_msg_data );
2466
2467 END create_class_category_use;
2468
2469 /**
2470 * PROCEDURE update_class_category_use
2474 *
2471 *
2472 * DESCRIPTION
2473 * Updates class category use.
2475 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2476 * HZ_BUSINESS_EVENT_V2PVT.update_class_cat_use_event
2477 *
2478 * ARGUMENTS
2479 * IN:
2480 * p_init_msg_list Initialize message stack if it is set to
2481 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2482 * p_class_category_use_rec Class category use record.
2483 * IN/OUT:
2484 * p_object_version_number Used for locking the being updated record.
2485 * OUT:
2486 * x_return_status Return status after the call. The status can
2487 * be FND_API.G_RET_STS_SUCCESS (success),
2488 * FND_API.G_RET_STS_ERROR (error),
2489 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2490 * x_msg_count Number of messages in message stack.
2491 * x_msg_data Message text if x_msg_count is 1.
2492 *
2493 * NOTES
2494 *
2495 * MODIFICATION HISTORY
2496 *
2497 * 07-23-2001 Indrajit Sen o Created.
2498 *
2499 */
2500
2501 PROCEDURE update_class_category_use(
2502 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
2503 p_class_category_use_rec IN CLASS_CATEGORY_USE_REC_TYPE,
2504 p_object_version_number IN OUT NOCOPY NUMBER,
2505 x_return_status OUT NOCOPY VARCHAR2,
2506 x_msg_count OUT NOCOPY NUMBER,
2507 x_msg_data OUT NOCOPY VARCHAR2
2508 ) IS
2509
2510 l_class_cat_use_rec CLASS_CATEGORY_USE_REC_TYPE := p_class_category_use_rec;
2511 l_old_class_cat_use_rec CLASS_CATEGORY_USE_REC_TYPE;
2512
2513 BEGIN
2514
2515 -- standard start of API savepoint
2516 SAVEPOINT update_class_category_use;
2517
2518 -- initialize message list if p_init_msg_list is set to TRUE.
2519 IF FND_API.to_Boolean(p_init_msg_list) THEN
2520 FND_MSG_PUB.initialize;
2521 END IF;
2522
2523 -- Initialize API return status to success.
2524 x_return_status := FND_API.G_RET_STS_SUCCESS;
2525
2526 -- Get old records. Will be used by business event system.
2527 get_class_category_use_rec (
2528 p_class_category => l_class_cat_use_rec.class_category,
2529 p_owner_table => l_class_cat_use_rec.owner_table,
2530 x_class_category_use_rec => l_old_class_cat_use_rec,
2531 x_return_status => x_return_status,
2532 x_msg_count => x_msg_count,
2533 x_msg_data => x_msg_data );
2534
2535 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2536 RAISE FND_API.G_EXC_ERROR;
2537 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2539 END IF;
2540
2541 -- call to business logic.
2542 do_update_class_category_use(
2543 l_class_cat_use_rec,
2544 p_object_version_number,
2545 x_return_status);
2546
2547 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2548
2549 -- Invoke business event system.
2550 --Bug 4743141.
2551 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
2552 HZ_BUSINESS_EVENT_V2PVT.update_class_cat_use_event (
2553 l_class_cat_use_rec,
2554 l_old_class_cat_use_rec );
2555 END IF;
2556
2557 END IF;
2558
2559 -- standard call to get message count and if count is 1, get message info.
2560 FND_MSG_PUB.Count_And_Get(
2561 p_encoded => FND_API.G_FALSE,
2562 p_count => x_msg_count,
2563 p_data => x_msg_data);
2564
2565 EXCEPTION
2566 WHEN FND_API.G_EXC_ERROR THEN
2567 ROLLBACK TO update_class_category_use;
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569
2570 FND_MSG_PUB.Count_And_Get(
2571 p_encoded => FND_API.G_FALSE,
2572 p_count => x_msg_count,
2573 p_data => x_msg_data );
2574
2575 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2576 ROLLBACK TO update_class_category_use;
2577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2578
2579 FND_MSG_PUB.Count_And_Get(
2580 p_encoded => FND_API.G_FALSE,
2581 p_count => x_msg_count,
2582 p_data => x_msg_data );
2583
2584 WHEN OTHERS THEN
2585 ROLLBACK TO update_class_category_use;
2586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2587
2588 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2589 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2590 FND_MSG_PUB.ADD;
2591
2592 FND_MSG_PUB.Count_And_Get(
2593 p_encoded => FND_API.G_FALSE,
2594 p_count => x_msg_count,
2595 p_data => x_msg_data );
2596
2597 END update_class_category_use;
2598
2599 /*===========================================================================+
2600 | PROCEDURE
2601 | get_current_class_category
2602 |
2603 | DESCRIPTION
2604 | Gets class category of current record.
2605 |
2606 | SCOPE - PRIVATE
2607 |
2608 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2609 |
2610 | ARGUMENTS : IN:
2611 | p_class_category
2612 | OUT:
2613 | x_class_cat_rec
2614 | IN/ OUT:
2615 |
2616 | RETURNS : NONE
2617 |
2618 | NOTES
2619 |
2620 | MODIFICATION HISTORY
2621 |
2625 p_class_category IN VARCHAR2,
2622 +===========================================================================*/
2623
2624 PROCEDURE get_current_class_category(
2626 x_class_cat_rec OUT NOCOPY CLASS_CATEGORY_REC_TYPE
2627 ) IS
2628 BEGIN
2629 SELECT
2630 class_category,
2631 allow_multi_assign_flag,
2632 allow_multi_parent_flag,
2633 allow_leaf_node_only_flag
2634 INTO
2635 x_class_cat_rec.class_category,
2636 x_class_cat_rec.allow_multi_assign_flag,
2637 x_class_cat_rec.allow_multi_parent_flag,
2638 x_class_cat_rec.allow_leaf_node_only_flag
2639 FROM hz_class_categories
2640 WHERE class_category = p_class_category;
2641 END;
2642
2643
2644 /*===========================================================================+
2645 | PROCEDURE
2646 | get_curr_class_code_rel
2647 |
2648 | DESCRIPTION
2649 | Gets class code relation of current record.
2650 |
2651 | SCOPE - PRIVATE
2652 |
2653 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2654 |
2655 | ARGUMENTS : IN:
2656 | p_class_code_rel_rec
2657 | OUT:
2658 | x_class_code_rel_rec
2659 | IN/ OUT:
2660 |
2661 | RETURNS : NONE
2662 |
2663 | NOTES
2664 |
2665 | MODIFICATION HISTORY
2666 |
2667 +===========================================================================*/
2668
2669 PROCEDURE get_curr_class_code_rel(
2670 p_class_code_rel_rec IN CLASS_CODE_RELATION_REC_TYPE,
2671 x_class_code_rel_rec OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE
2672 ) IS
2673 BEGIN
2674 SELECT
2675 class_category,
2676 class_code,
2677 sub_class_code,
2678 start_date_active,
2679 end_date_active
2680 INTO
2681 x_class_code_rel_rec.class_category,
2682 x_class_code_rel_rec.class_code,
2683 x_class_code_rel_rec.sub_class_code,
2684 x_class_code_rel_rec.start_date_active,
2685 x_class_code_rel_rec.end_date_active
2686 FROM hz_class_code_relations
2687 WHERE
2688 class_category = p_class_code_rel_rec.class_category AND
2689 class_code = p_class_code_rel_rec.class_code AND
2690 sub_class_code = p_class_code_rel_rec.sub_class_code AND
2691 start_date_active = p_class_code_rel_rec.start_date_active;
2692 END;
2693
2694
2695
2696
2697 --HYU
2698
2699 /*===========================================================================+
2700 | PROCEDURE
2701 | get_curr_class_category_use
2702 | DESCRIPTION
2703 | Gets class category uses of current record.
2704 | SCOPE - PRIVATE
2705 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2706 | ARGUMENTS : IN:
2707 | p_class_category_use_rec
2708 | OUT:
2709 | x_class_category_use_rec
2710 | IN/ OUT:
2711 | RETURNS : NONE
2712 | NOTES
2713 | MODIFICATION HISTORY
2714 | Herve Yu 19-JAN-2001 Created
2715 +===========================================================================*/
2716
2717 PROCEDURE get_curr_class_category_use
2718 (p_class_category_use_rec IN CLASS_CATEGORY_USE_REC_TYPE,
2719 x_class_category_use_rec OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE)
2720 IS
2721 BEGIN
2722 SELECT class_category,
2723 owner_table,
2724 additional_where_clause
2725 INTO x_class_category_use_rec.class_category,
2726 x_class_category_use_rec.owner_table,
2727 x_class_category_use_rec.additional_where_clause
2728 FROM hz_class_category_uses
2729 WHERE class_category = p_class_category_use_rec.class_category
2730 AND owner_table = p_class_category_use_rec.owner_table;
2731 END get_curr_class_category_use;
2732
2733
2734 /*===========================================================================+
2735 | PROCEDURE
2736 | get_current_code_assignmen
2737 | DESCRIPTION
2738 | Gets code assignment of current record.
2739 | SCOPE - PRIVATE
2740 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2741 | ARGUMENTS : IN:
2742 | p_code_assignment_id
2743 | OUT:
2744 | x_code_assignment_rec
2745 | IN/ OUT:
2746 | RETURNS : NONE
2747 | NOTES
2748 | MODIFICATION HISTORY
2749 +===========================================================================*/
2750
2751 PROCEDURE get_current_code_assignment(
2752 p_code_assignment_id IN NUMBER,
2753 x_code_assignment_rec OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE
2754 ) IS
2755 BEGIN
2756 SELECT
2757 code_assignment_id,
2758 owner_table_name,
2759 owner_table_id,
2760 class_category,
2761 class_code,
2762 primary_flag,
2763 content_source_type,
2764 start_date_active,
2765 end_date_active,
2766 rank
2767
2768 INTO
2769 x_code_assignment_rec.code_assignment_id ,
2770 x_code_assignment_rec.owner_table_name,
2771 x_code_assignment_rec.owner_table_id,
2772 x_code_assignment_rec.class_category,
2773 x_code_assignment_rec.class_code,
2774 x_code_assignment_rec.primary_flag,
2775 x_code_assignment_rec.content_source_type,
2776 x_code_assignment_rec.start_date_active,
2777 x_code_assignment_rec.end_date_active,
2778 x_code_assignment_rec.rank
2779 FROM hz_code_assignments
2783 /**
2780 WHERE code_assignment_id = p_code_assignment_id;
2781 END;
2782
2784 * PROCEDURE get_class_category_rec
2785 *
2786 * DESCRIPTION
2787 * Gets class category record.
2788 *
2789 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2790 * HZ_CLASS_CATEGORIES_PKG.Select_Row
2791 *
2792 * ARGUMENTS
2793 * IN:
2794 * p_init_msg_list Initialize message stack if it is set to
2795 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2796 * p_class_category Class category name.
2797 * IN/OUT:
2798 * OUT:
2799 * x_class_category_rec Returned class category record.
2800 * x_return_status Return status after the call. The status can
2801 * be FND_API.G_RET_STS_SUCCESS (success),
2802 * FND_API.G_RET_STS_ERROR (error),
2803 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2804 * x_msg_count Number of messages in message stack.
2805 * x_msg_data Message text if x_msg_count is 1.
2806 *
2807 * NOTES
2808 *
2809 * MODIFICATION HISTORY
2810 *
2811 * 07-23-2001 Indrajit Sen o Created.
2812 *
2813 */
2814
2815 PROCEDURE get_class_category_rec (
2816 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2817 p_class_category IN VARCHAR2,
2818 x_class_category_rec OUT NOCOPY CLASS_CATEGORY_REC_TYPE,
2819 x_return_status OUT NOCOPY VARCHAR2,
2820 x_msg_count OUT NOCOPY NUMBER,
2821 x_msg_data OUT NOCOPY VARCHAR2
2822 ) IS
2823
2824 BEGIN
2825
2826 --Initialize message list if p_init_msg_list is set to TRUE.
2827 IF FND_API.to_Boolean(p_init_msg_list) THEN
2828 FND_MSG_PUB.initialize;
2829 END IF;
2830
2831 --Initialize API return status to success.
2832 x_return_status := FND_API.G_RET_STS_SUCCESS;
2833
2834 --Check whether primary key has been passed in.
2835 IF p_class_category IS NULL OR
2836 p_class_category = FND_API.G_MISS_CHAR THEN
2837 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2838 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'class_category' );
2839 FND_MSG_PUB.ADD;
2840 RAISE FND_API.G_EXC_ERROR;
2841 END IF;
2842
2843 x_class_category_rec.class_category := p_class_category;
2844
2845 HZ_CLASS_CATEGORIES_PKG.Select_Row (
2846 X_CLASS_CATEGORY => x_class_category_rec.class_category,
2847 X_ALLOW_MULTI_PARENT_FLAG => x_class_category_rec.allow_multi_parent_flag,
2848 X_ALLOW_MULTI_ASSIGN_FLAG => x_class_category_rec.allow_multi_assign_flag,
2849 X_ALLOW_LEAF_NODE_ONLY_FLAG => x_class_category_rec.allow_leaf_node_only_flag,
2850 X_CREATED_BY_MODULE => x_class_category_rec.created_by_module,
2851 X_APPLICATION_ID => x_class_category_rec.application_id,
2852 X_DELIMITER => x_class_category_rec.delimiter
2853 );
2854
2855 --Standard call to get message count and if count is 1, get message info.
2856 FND_MSG_PUB.Count_And_Get(
2857 p_encoded => FND_API.G_FALSE,
2858 p_count => x_msg_count,
2859 p_data => x_msg_data );
2860
2861 EXCEPTION
2862 WHEN FND_API.G_EXC_ERROR THEN
2863 x_return_status := FND_API.G_RET_STS_ERROR;
2864
2865 FND_MSG_PUB.Count_And_Get(
2866 p_encoded => FND_API.G_FALSE,
2867 p_count => x_msg_count,
2868 p_data => x_msg_data );
2869
2870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2872
2873 FND_MSG_PUB.Count_And_Get(
2874 p_encoded => FND_API.G_FALSE,
2875 p_count => x_msg_count,
2876 p_data => x_msg_data );
2877
2878 WHEN OTHERS THEN
2879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2880
2881 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2882 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2883 FND_MSG_PUB.ADD;
2884
2885 FND_MSG_PUB.Count_And_Get(
2886 p_encoded => FND_API.G_FALSE,
2887 p_count => x_msg_count,
2888 p_data => x_msg_data );
2889
2890 END get_class_category_rec;
2891
2892 /**
2893 * PROCEDURE get_class_category_use_rec
2894 *
2895 * DESCRIPTION
2896 * Gets class category use record.
2897 *
2898 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2899 * HZ_CLASS_CATEGORY_USES_PKG.Select_Row
2900 *
2901 * ARGUMENTS
2902 * IN:
2903 * p_init_msg_list Initialize message stack if it is set to
2904 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2905 * p_class_category Class category name.
2906 * p_owner_table Owner table name.
2907 * IN/OUT:
2908 * OUT:
2909 * x_class_category_use_rec Returned class category use record.
2910 * x_return_status Return status after the call. The status can
2911 * be FND_API.G_RET_STS_SUCCESS (success),
2912 * FND_API.G_RET_STS_ERROR (error),
2913 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2914 * x_msg_count Number of messages in message stack.
2915 * x_msg_data Message text if x_msg_count is 1.
2916 *
2917 * NOTES
2918 *
2919 * MODIFICATION HISTORY
2920 *
2924
2921 * 07-23-2001 Indrajit Sen o Created.
2922 *
2923 */
2925 PROCEDURE get_class_category_use_rec(
2926 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2927 p_class_category IN VARCHAR2,
2928 p_owner_table IN VARCHAR2,
2929 x_class_category_use_rec OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
2930 x_return_status OUT NOCOPY VARCHAR2,
2931 x_msg_count OUT NOCOPY NUMBER,
2932 x_msg_data OUT NOCOPY VARCHAR2
2933 ) IS
2934
2935 BEGIN
2936
2937 --Initialize message list if p_init_msg_list is set to TRUE.
2938 IF FND_API.to_Boolean(p_init_msg_list) THEN
2939 FND_MSG_PUB.initialize;
2940 END IF;
2941
2942 --Initialize API return status to success.
2943 x_return_status := FND_API.G_RET_STS_SUCCESS;
2944
2945 --Check whether primary key has been passed in.
2946 IF p_class_category IS NULL OR
2947 p_class_category = FND_API.G_MISS_CHAR THEN
2948 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2949 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'class_category' );
2950 FND_MSG_PUB.ADD;
2951 RAISE FND_API.G_EXC_ERROR;
2952 END IF;
2953
2954 IF p_owner_table IS NULL OR
2955 p_owner_table = FND_API.G_MISS_CHAR THEN
2956 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2957 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'owner_table' );
2958 FND_MSG_PUB.ADD;
2959 RAISE FND_API.G_EXC_ERROR;
2960 END IF;
2961
2962 x_class_category_use_rec.class_category := p_class_category;
2963 x_class_category_use_rec.owner_table := p_owner_table;
2964
2965 HZ_CLASS_CATEGORY_USES_PKG.Select_Row (
2966 X_CLASS_CATEGORY => x_class_category_use_rec.class_category,
2967 X_OWNER_TABLE => x_class_category_use_rec.owner_table,
2968 X_COLUMN_NAME => x_class_category_use_rec.column_name,
2969 X_ADDITIONAL_WHERE_CLAUSE => x_class_category_use_rec.additional_where_clause,
2970 X_CREATED_BY_MODULE => x_class_category_use_rec.created_by_module,
2971 X_APPLICATION_ID => x_class_category_use_rec.application_id
2972 );
2973
2974 --Standard call to get message count and if count is 1, get message info.
2975 FND_MSG_PUB.Count_And_Get(
2976 p_encoded => FND_API.G_FALSE,
2977 p_count => x_msg_count,
2978 p_data => x_msg_data );
2979
2980 EXCEPTION
2981 WHEN FND_API.G_EXC_ERROR THEN
2982 x_return_status := FND_API.G_RET_STS_ERROR;
2983
2984 FND_MSG_PUB.Count_And_Get(
2985 p_encoded => FND_API.G_FALSE,
2986 p_count => x_msg_count,
2987 p_data => x_msg_data );
2988
2989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2991
2992 FND_MSG_PUB.Count_And_Get(
2993 p_encoded => FND_API.G_FALSE,
2994 p_count => x_msg_count,
2995 p_data => x_msg_data );
2996
2997 WHEN OTHERS THEN
2998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2999
3000 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3001 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3002 FND_MSG_PUB.ADD;
3003
3004 FND_MSG_PUB.Count_And_Get(
3005 p_encoded => FND_API.G_FALSE,
3006 p_count => x_msg_count,
3007 p_data => x_msg_data );
3008
3009 END get_class_category_use_rec;
3010
3011 /**
3012 * PROCEDURE get_class_code_relation_rec
3013 *
3014 * DESCRIPTION
3015 * Gets class code relation record.
3016 *
3017 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3018 * HZ_CLASS_CODE_RELATIONS_PKG.Select_Row
3019 *
3020 * ARGUMENTS
3021 * IN:
3022 * p_init_msg_list Initialize message stack if it is set to
3023 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3024 * p_class_category Class category name.
3025 * p_class_code Class code.
3026 * p_sub_class_code Sub class code.
3027 * p_start_date_active Start date active.
3028 * IN/OUT:
3029 * OUT:
3030 * x_class_code_relation_rec Returned class code relation record.
3031 * x_return_status Return status after the call. The status can
3032 * be FND_API.G_RET_STS_SUCCESS (success),
3033 * FND_API.G_RET_STS_ERROR (error),
3034 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3035 * x_msg_count Number of messages in message stack.
3036 * x_msg_data Message text if x_msg_count is 1.
3037 *
3038 * NOTES
3039 *
3040 * MODIFICATION HISTORY
3041 *
3042 * 07-23-2001 Indrajit Sen o Created.
3043 *
3044 */
3045
3046 PROCEDURE get_class_code_relation_rec(
3047 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3048 p_class_category IN VARCHAR2,
3049 p_class_code IN VARCHAR2,
3050 p_sub_class_code IN VARCHAR2,
3051 p_start_date_active IN DATE,
3052 x_class_code_relation_rec OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE,
3053 x_return_status OUT NOCOPY VARCHAR2,
3054 x_msg_count OUT NOCOPY NUMBER,
3055 x_msg_data OUT NOCOPY VARCHAR2
3056 ) IS
3057
3061 IF FND_API.to_Boolean(p_init_msg_list) THEN
3058 BEGIN
3059
3060 --Initialize message list if p_init_msg_list is set to TRUE.
3062 FND_MSG_PUB.initialize;
3063 END IF;
3064
3065 --Initialize API return status to success.
3066 x_return_status := FND_API.G_RET_STS_SUCCESS;
3067
3068 --Check whether primary key has been passed in.
3069 IF p_class_category IS NULL OR
3070 p_class_category = FND_API.G_MISS_CHAR THEN
3071 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3072 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'class_category' );
3073 FND_MSG_PUB.ADD;
3074 RAISE FND_API.G_EXC_ERROR;
3075 END IF;
3076
3077 IF p_class_code IS NULL OR
3078 p_class_code = FND_API.G_MISS_CHAR THEN
3079 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3080 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'class_code' );
3081 FND_MSG_PUB.ADD;
3082 RAISE FND_API.G_EXC_ERROR;
3083 END IF;
3084
3085 IF p_sub_class_code IS NULL OR
3086 p_sub_class_code = FND_API.G_MISS_CHAR THEN
3087 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3088 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'sub_class_code' );
3089 FND_MSG_PUB.ADD;
3090 RAISE FND_API.G_EXC_ERROR;
3091 END IF;
3092
3093 IF p_start_date_active IS NULL OR
3094 p_start_date_active = FND_API.G_MISS_DATE THEN
3095 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3096 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'start_date_active' );
3097 FND_MSG_PUB.ADD;
3098 RAISE FND_API.G_EXC_ERROR;
3099 END IF;
3100
3101 x_class_code_relation_rec.class_category := p_class_category;
3102 x_class_code_relation_rec.class_code := p_class_code;
3103 x_class_code_relation_rec.sub_class_code := p_sub_class_code;
3104 x_class_code_relation_rec.start_date_active := p_start_date_active;
3105
3106 HZ_CLASS_CODE_RELATIONS_PKG.Select_Row (
3107 X_CLASS_CATEGORY => x_class_code_relation_rec.class_category,
3108 X_CLASS_CODE => x_class_code_relation_rec.class_code,
3109 X_SUB_CLASS_CODE => x_class_code_relation_rec.sub_class_code,
3110 X_START_DATE_ACTIVE => x_class_code_relation_rec.start_date_active,
3111 X_END_DATE_ACTIVE => x_class_code_relation_rec.end_date_active,
3112 X_CREATED_BY_MODULE => x_class_code_relation_rec.created_by_module,
3113 X_APPLICATION_ID => x_class_code_relation_rec.application_id
3114 );
3115
3116 --Standard call to get message count and if count is 1, get message info.
3117 FND_MSG_PUB.Count_And_Get(
3118 p_encoded => FND_API.G_FALSE,
3119 p_count => x_msg_count,
3120 p_data => x_msg_data );
3121
3122 EXCEPTION
3123 WHEN FND_API.G_EXC_ERROR THEN
3124 x_return_status := FND_API.G_RET_STS_ERROR;
3125
3126 FND_MSG_PUB.Count_And_Get(
3127 p_encoded => FND_API.G_FALSE,
3128 p_count => x_msg_count,
3129 p_data => x_msg_data );
3130
3131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3133
3134 FND_MSG_PUB.Count_And_Get(
3135 p_encoded => FND_API.G_FALSE,
3136 p_count => x_msg_count,
3137 p_data => x_msg_data );
3138
3139 WHEN OTHERS THEN
3140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3141
3142 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3143 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3144 FND_MSG_PUB.ADD;
3145
3146 FND_MSG_PUB.Count_And_Get(
3147 p_encoded => FND_API.G_FALSE,
3148 p_count => x_msg_count,
3149 p_data => x_msg_data );
3150
3151 END get_class_code_relation_rec;
3152
3153 /**
3154 * PROCEDURE get_code_assignment_rec
3155 *
3156 * DESCRIPTION
3157 * Gets code assignment record.
3158 *
3159 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3160 * HZ_CODE_ASSIGNMENTS_PKG.Select_Row
3161 *
3162 * ARGUMENTS
3163 * IN:
3164 * p_init_msg_list Initialize message stack if it is set to
3165 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3166 * p_code_assignment_id Code assignment ID.
3167 * IN/OUT:
3168 * OUT:
3169 * x_code_assignment_rec Returned code assignment record.
3170 * x_return_status Return status after the call. The status can
3171 * be FND_API.G_RET_STS_SUCCESS (success),
3172 * FND_API.G_RET_STS_ERROR (error),
3173 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3174 * x_msg_count Number of messages in message stack.
3175 * x_msg_data Message text if x_msg_count is 1.
3176 *
3177 * NOTES
3178 *
3179 * MODIFICATION HISTORY
3180 *
3181 * 07-23-2001 Indrajit Sen o Created.
3182 * 01-05-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
3183 * Added actual_content_source in call to select_row
3184 *
3185 *
3186 */
3187
3188 PROCEDURE get_code_assignment_rec (
3189 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3190 p_code_assignment_id IN NUMBER,
3191 x_code_assignment_rec OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE,
3192 x_return_status OUT NOCOPY VARCHAR2,
3193 x_msg_count OUT NOCOPY NUMBER,
3194 x_msg_data OUT NOCOPY VARCHAR2
3195 ) IS
3196
3197 BEGIN
3198
3199 --Initialize message list if p_init_msg_list is set to TRUE.
3200 IF FND_API.to_Boolean(p_init_msg_list) THEN
3201 FND_MSG_PUB.initialize;
3202 END IF;
3203
3204 --Initialize API return status to success.
3205 x_return_status := FND_API.G_RET_STS_SUCCESS;
3206
3207 --Check whether primary key has been passed in.
3208 IF p_code_assignment_id IS NULL OR
3209 p_code_assignment_id = FND_API.G_MISS_NUM THEN
3210 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3211 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'code_assignment_id' );
3212 FND_MSG_PUB.ADD;
3213 RAISE FND_API.G_EXC_ERROR;
3214 END IF;
3215
3216 x_code_assignment_rec.code_assignment_id := p_code_assignment_id;
3217
3218 HZ_CODE_ASSIGNMENTS_PKG.Select_Row (
3219 X_CODE_ASSIGNMENT_ID => x_code_assignment_rec.code_assignment_id,
3220 X_OWNER_TABLE_NAME => x_code_assignment_rec.owner_table_name,
3221 X_OWNER_TABLE_ID => x_code_assignment_rec.owner_table_id,
3222 X_OWNER_TABLE_KEY_1 => x_code_assignment_rec.owner_table_key_1,
3223 X_OWNER_TABLE_KEY_2 => x_code_assignment_rec.owner_table_key_2,
3224 X_OWNER_TABLE_KEY_3 => x_code_assignment_rec.owner_table_key_3,
3225 X_OWNER_TABLE_KEY_4 => x_code_assignment_rec.owner_table_key_4,
3226 X_OWNER_TABLE_KEY_5 => x_code_assignment_rec.owner_table_key_5,
3227 X_CLASS_CATEGORY => x_code_assignment_rec.class_category,
3228 X_CLASS_CODE => x_code_assignment_rec.class_code,
3229 X_PRIMARY_FLAG => x_code_assignment_rec.primary_flag,
3230 X_CONTENT_SOURCE_TYPE => x_code_assignment_rec.content_source_type,
3231 X_START_DATE_ACTIVE => x_code_assignment_rec.start_date_active,
3232 X_END_DATE_ACTIVE => x_code_assignment_rec.end_date_active,
3233 X_STATUS => x_code_assignment_rec.status,
3234 X_CREATED_BY_MODULE => x_code_assignment_rec.created_by_module,
3235 X_RANK => X_code_assignment_rec.rank,
3236 X_APPLICATION_ID => x_code_assignment_rec.application_id,
3237 -- SSM SST Integration and Extension
3238 X_ACTUAL_CONTENT_SOURCE => x_code_assignment_rec.actual_content_source
3239
3240 );
3241
3242 --Standard call to get message count and if count is 1, get message info.
3243 FND_MSG_PUB.Count_And_Get(
3244 p_encoded => FND_API.G_FALSE,
3245 p_count => x_msg_count,
3246 p_data => x_msg_data );
3247
3248 EXCEPTION
3249 WHEN FND_API.G_EXC_ERROR THEN
3250 x_return_status := FND_API.G_RET_STS_ERROR;
3251
3252 FND_MSG_PUB.Count_And_Get(
3253 p_encoded => FND_API.G_FALSE,
3254 p_count => x_msg_count,
3255 p_data => x_msg_data );
3256
3257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3259
3260 FND_MSG_PUB.Count_And_Get(
3261 p_encoded => FND_API.G_FALSE,
3262 p_count => x_msg_count,
3263 p_data => x_msg_data );
3264
3265 WHEN OTHERS THEN
3266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3267
3268 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3269 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3270 FND_MSG_PUB.ADD;
3271
3272 FND_MSG_PUB.Count_And_Get(
3273 p_encoded => FND_API.G_FALSE,
3274 p_count => x_msg_count,
3275 p_data => x_msg_data );
3276
3277 END get_code_assignment_rec;
3278
3279
3280 /**
3281 * FUNCTION is_valid_category
3282 *
3283 * DESCRIPTION
3284 * ERS No: 2074686. The fucntion checks if a given id can be assigned to a class_category and
3285 * owner_table. It returns 'T' if party_id can be assigned or 'F' else.
3286 *
3287 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3288 *
3289 * ARGUMENTS
3290 * IN:
3291 * p_owner_table Owner table name.
3292 * p_class_category Name of class category
3293 * p_id id (party_id or a party_relationship_id)
3294 * IN/OUT:
3295 * OUT:
3296 *
3297 * NOTES
3298 *
3299 * MODIFICATION HISTORY
3300 *
3301 * 02-14-2002 Anupam Bordia o Created.
3302 * 02-18-2002 Anupam Bordia o Altered signature to remove OUT NOCOPY parameters so that the
3303 * function can be used within a SQL.
3304 * 03-27-2002 Anupam Bordia o Bug#2284235 Parses additional_where_clause conditionally.
3305 * 02-03-2003 Sreedhar Mohan o Rewritten the function as part of new HZ.K changes.
3306 * 03-17-2003 Sreedhar Mohan o Bug 2829644: Changed the cursor query to compare upper
3307 * on both sides of the comparison.
3308 */
3309
3310 FUNCTION IS_VALID_CATEGORY(
3311 p_owner_table VARCHAR2,
3312 p_class_category VARCHAR2,
3313 p_id NUMBER := FND_API.G_MISS_NUM,
3314 p_key_1 VARCHAR2 := FND_API.G_MISS_CHAR,
3315 p_key_2 VARCHAR2 := FND_API.G_MISS_CHAR,
3316 p_key_3 VARCHAR2 := FND_API.G_MISS_CHAR,
3317 p_key_4 VARCHAR2 := FND_API.G_MISS_CHAR,
3318 p_key_5 VARCHAR2 := FND_API.G_MISS_CHAR
3319 )
3320 RETURN VARCHAR2
3321 IS
3322 --Bug 2824942: Modified the cursor to verify additional_where_clause from hz_class_category uses
3323 CURSOR get_category_uses_info is
3324 SELECT h.owner_table,
3325 upper(trim(h.additional_where_clause)),
3326 f.pk1_column_name,
3327 f.pk2_column_name,
3328 f.pk3_column_name,
3329 f.pk4_column_name,
3330 f.pk5_column_name
3331 FROM hz_class_category_uses h,
3332 fnd_objects f
3333 WHERE upper(f.database_object_name) = upper(h.owner_table)
3334 AND class_category = p_class_category
3335 AND owner_table = p_owner_table;
3336
3337 bool VARCHAR2(1) := 'F';
3338 l_database_object_name VARCHAR2(30):= FND_API.G_MISS_CHAR;
3339 l_pk1_column_name VARCHAR2(30):= FND_API.G_MISS_CHAR;
3340 l_pk2_column_name VARCHAR2(30):= FND_API.G_MISS_CHAR;
3341 l_pk3_column_name VARCHAR2(30):= FND_API.G_MISS_CHAR;
3342 l_pk4_column_name VARCHAR2(30):= FND_API.G_MISS_CHAR;
3343 l_pk5_column_name VARCHAR2(30):= FND_API.G_MISS_CHAR;
3344 l_owner_table VARCHAR2(30);
3345
3346 p_key VARCHAR2(30);
3347 l_sql VARCHAR2(4000);
3348 l_where_clause VARCHAR2(4000);
3349 check_for_where VARCHAR2(10) ;
3350 l_additional_where_clause VARCHAR2(4000) := null;
3351
3352 BEGIN
3353
3354 OPEN get_category_uses_info;
3355 FETCH get_category_uses_info INTO l_owner_table, l_additional_where_clause,
3356 l_pk1_column_name, l_pk2_column_name, l_pk3_column_name,
3357 l_pk4_column_name, l_pk5_column_name;
3358 IF (get_category_uses_info%NOTFOUND)THEN
3359 RETURN bool;
3360 END IF;
3361
3362 check_for_where := substrb(l_additional_where_clause,1, 6);
3366
3363 IF (check_for_where = 'WHERE ') THEN
3364 l_additional_where_clause := substrb(l_additional_where_clause,6);
3365 END IF;
3367 CLOSE get_category_uses_info;
3368
3369 --p_id and p_key_1 are mutually exclusive
3370 IF (p_id IS NULL OR p_id = FND_API.G_MISS_NUM) THEN
3371 p_key := p_key_1;
3372 ELSE
3373 p_key := TO_CHAR(p_id);
3374 END IF;
3375
3376 BEGIN
3377
3378 IF l_pk5_column_name IS NOT NULL AND l_pk5_column_name <> FND_API.G_MISS_CHAR
3379 THEN
3380 IF l_additional_where_clause is not null THEN
3381 l_sql := 'SELECT ''T'' ' ||
3382 ' FROM ' || p_owner_table ||
3383 ' WHERE ' || l_additional_where_clause ||
3384 ' AND ' || l_pk1_column_name || '=:1 ' ||
3385 ' AND ' || l_pk2_column_name || '=:2 ' ||
3386 ' AND ' || l_pk3_column_name || '=:3 ' ||
3387 ' AND ' || l_pk4_column_name || '=:4 ' ||
3388 ' AND ' || l_pk5_column_name || '=:5 ' ||
3389 ' AND ROWNUM = 1';
3390 ELSE
3391 l_sql := 'SELECT ''T'' ' ||
3392 ' FROM ' || p_owner_table ||
3393 ' WHERE ' || l_pk1_column_name || '=:1 ' ||
3394 ' AND ' || l_pk2_column_name || '=:2 ' ||
3395 ' AND ' || l_pk3_column_name || '=:3 ' ||
3396 ' AND ' || l_pk4_column_name || '=:4 ' ||
3397 ' AND ' || l_pk5_column_name || '=:5 ' ||
3398 ' AND ROWNUM = 1';
3399 END IF;
3400 EXECUTE IMMEDIATE l_sql into bool using p_key,
3401 nvl(p_key_2,FND_API.G_MISS_CHAR),
3402 nvl(p_key_3,FND_API.G_MISS_CHAR),
3403 nvl(p_key_4,FND_API.G_MISS_CHAR),
3404 nvl(p_key_5,FND_API.G_MISS_CHAR);
3405 ELSIF l_pk4_column_name IS NOT NULL AND l_pk4_column_name <> FND_API.G_MISS_CHAR
3406 THEN
3407 IF l_additional_where_clause is not null THEN
3408 l_sql := 'SELECT ''T'' ' ||
3409 ' FROM ' || p_owner_table ||
3410 ' WHERE ' || l_additional_where_clause ||
3411 ' AND ' || l_pk1_column_name || '=:1 ' ||
3412 ' AND ' || l_pk2_column_name || '=:2 ' ||
3413 ' AND ' || l_pk3_column_name || '=:3 ' ||
3414 ' AND ' || l_pk4_column_name || '=:4 ' ||
3415 ' AND ROWNUM = 1';
3416 ELSE
3417 l_sql := 'SELECT ''T'' ' ||
3418 ' FROM ' || p_owner_table ||
3419 ' WHERE ' || l_pk1_column_name || '=:1 ' ||
3420 ' AND ' || l_pk2_column_name || '=:2 ' ||
3421 ' AND ' || l_pk3_column_name || '=:3 ' ||
3422 ' AND ' || l_pk4_column_name || '=:4 ' ||
3423 ' AND ROWNUM = 1';
3424 END IF;
3425 EXECUTE IMMEDIATE l_sql into bool using p_key,
3426 nvl(p_key_2,FND_API.G_MISS_CHAR),
3427 nvl(p_key_3,FND_API.G_MISS_CHAR),
3428 nvl(p_key_4,FND_API.G_MISS_CHAR);
3429 ELSIF l_pk3_column_name IS NOT NULL AND l_pk3_column_name <> FND_API.G_MISS_CHAR
3430 THEN
3431 IF l_additional_where_clause is not null THEN
3432 l_sql := 'SELECT ''T'' ' ||
3433 ' FROM ' || p_owner_table ||
3434 ' WHERE ' || l_additional_where_clause ||
3435 ' AND ' || l_pk1_column_name || '=:1 ' ||
3436 ' AND ' || l_pk2_column_name || '=:2 ' ||
3437 ' AND ' || l_pk3_column_name || '=:3 ' ||
3438 ' AND ROWNUM = 1';
3439 ELSE
3440 l_sql := 'SELECT ''T'' ' ||
3441 ' FROM ' || p_owner_table ||
3442 ' WHERE ' || l_pk1_column_name || '=:1 ' ||
3443 ' AND ' || l_pk2_column_name || '=:2 ' ||
3444 ' AND ' || l_pk3_column_name || '=:3 ' ||
3445 ' AND ROWNUM = 1';
3446 END IF;
3447 EXECUTE IMMEDIATE l_sql into bool using p_key,
3448 nvl(p_key_2,FND_API.G_MISS_CHAR),
3449 nvl(p_key_3,FND_API.G_MISS_CHAR);
3450 ELSIF l_pk2_column_name IS NOT NULL AND l_pk2_column_name <> FND_API.G_MISS_CHAR
3451 THEN
3452 IF l_additional_where_clause is not null THEN
3453 l_sql := 'SELECT ''T'' ' ||
3454 ' FROM ' || p_owner_table ||
3455 ' WHERE ' || l_additional_where_clause ||
3456 ' AND ' || l_pk1_column_name || '=:1 ' ||
3457 ' AND ' || l_pk2_column_name || '=:2 ' ||
3458 ' AND ROWNUM = 1';
3459 ELSE
3460 l_sql := 'SELECT ''T'' ' ||
3461 ' FROM ' || p_owner_table ||
3462 ' WHERE ' || l_pk1_column_name || '=:1 ' ||
3463 ' AND ' || l_pk2_column_name || '=:2 ' ||
3464 ' AND ROWNUM = 1';
3465 END IF;
3466 EXECUTE IMMEDIATE l_sql into bool using p_key,
3467 nvl(p_key_2,FND_API.G_MISS_CHAR);
3468 ELSIF (l_pk1_column_name IS NOT NULL AND l_pk1_column_name <> FND_API.G_MISS_CHAR)
3469 THEN
3470 IF l_additional_where_clause is not null THEN
3471 l_sql := 'SELECT ''T'' ' ||
3472 ' FROM ' || p_owner_table ||
3473 ' WHERE ' || l_additional_where_clause ||
3474 ' AND ' || l_pk1_column_name || '=:1 ' ||
3475 ' AND ROWNUM = 1';
3476 ELSE
3477 l_sql := 'SELECT ''T'' ' ||
3478 ' FROM ' || p_owner_table ||
3479 ' WHERE ' || l_pk1_column_name || '=:1 ' ||
3483 EXECUTE IMMEDIATE l_sql into bool using p_key;
3480 ' AND ROWNUM = 1';
3481
3482 END IF;
3484 END IF;
3485 EXCEPTION WHEN OTHERS THEN
3486 bool := 'F';
3487 END;
3488
3489 RETURN bool;
3490
3491 END IS_VALID_CATEGORY;
3492
3493 /*
3494 FUNCTION IS_VALID_CATEGORY(p_owner_table VARCHAR2,
3495 p_class_category VARCHAR2,
3496 p_id NUMBER
3497 )
3498 RETURN VARCHAR2
3499 IS
3500 CURSOR get_query_info is
3501 SELECT upper(trim(additional_where_clause)), column_name
3502 FROM hz_class_category_uses
3503 WHERE class_category = p_class_category
3504 AND owner_table = p_owner_table;
3505
3506 bool VARCHAR2(1) := 'F';
3507 l_additional_where_clause VARCHAR2(4000) := null;
3508 l_column_name VARCHAR2(240) := null;
3509 l_sql VARCHAR2(4000);
3510 check_for_where VARCHAR2(10) ;
3511
3512 BEGIN
3513 OPEN get_query_info;
3514 FETCH get_query_info INTO l_additional_where_clause, l_column_name;
3515 IF (get_query_info%NOTFOUND)THEN
3516 RETURN bool;
3517 END IF;
3518
3519 check_for_where := substrb(l_additional_where_clause,1, 6);
3520 IF (check_for_where = 'WHERE ') THEN
3521 l_additional_where_clause := substrb(l_additional_where_clause,6);
3522 END IF;
3523
3524 CLOSE get_query_info;
3525 IF l_additional_where_clause is not null THEN
3526 l_sql := 'SELECT ''T'' ' ||
3527 ' FROM '|| p_owner_table ||
3528 ' WHERE '||l_column_name||' = :1 and '||
3529 l_additional_where_clause||' and rownum = 1';
3530 ELSE
3531 l_sql := 'SELECT ''T'' ' ||
3532 'FROM '|| p_owner_table || ' ' ||
3533 'WHERE '||l_column_name||'= :1 and rownum = 1';
3534 END IF;
3535 BEGIN
3536 EXECUTE IMMEDIATE l_sql into bool using p_id;
3537 EXCEPTION WHEN OTHERS THEN
3538 bool := 'F';
3539 END;
3540
3541 RETURN bool;
3542 END IS_VALID_CATEGORY;
3543 */
3544 /**
3545 * PROCEDURE create_class_code
3546 *
3547 * DESCRIPTION
3548 * This is a wrapper on top of FND_LOOKUP_VALUES_PKG.insert_row. It also
3549 * updates frozen flag and validate class code meaning.
3550 *
3551 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3552 *
3553 * ARGUMENTS
3554 * IN:
3555 * p_init_msg_list Initialize message stack if it is set to
3556 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3557 * p_class_code_rec Lookup value related columns
3558 * IN/OUT:
3559 * OUT:
3560 * x_return_status Return status after the call. The status can
3561 * be FND_API.G_RET_STS_SUCCESS (success),
3562 * FND_API.G_RET_STS_ERROR (error),
3563 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3564 * x_msg_count Number of messages in message stack.
3565 * x_msg_data Message text if x_msg_count is 1.
3566 *
3567 * NOTES
3568 *
3569 * MODIFICATION HISTORY
3570 *
3571 * 05-28-2002 Amy Wu o Created.
3572 * 07-01-2003 Dhaval Mehta Bug 2960224 : Added validation to TYPE against active
3573 * class categories.
3574 * 20-Sep-2007 Manivannan J Bug 6158794 : Added validation to TYPE against
3575 * classification code and classification meaning.
3576 */
3577
3578 PROCEDURE create_class_code(
3579 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3580 p_class_code_rec IN CLASS_CODE_REC_TYPE,
3581 x_return_status OUT NOCOPY VARCHAR2,
3582 x_msg_count OUT NOCOPY NUMBER,
3583 x_msg_data OUT NOCOPY VARCHAR2
3584 ) is
3585 row_id varchar2(64);
3586 l_class_code_rec CLASS_CODE_REC_TYPE := p_class_code_rec;
3587 begin
3588 savepoint create_class_code;
3589
3590 -- initialize message list if p_init_msg_list is set to TRUE.
3591 IF FND_API.to_Boolean(p_init_msg_list) THEN
3592 FND_MSG_PUB.initialize;
3593 END IF;
3594
3595 -- Initialize return status to SUCCESS
3596 x_return_status := FND_API.G_RET_STS_SUCCESS;
3597
3598 If HZ_CLASS_VALIDATE_V2PUB.is_valid_class_code_meaning(l_class_code_rec.type,l_class_code_rec.meaning)='N'
3599 then
3600 FND_MESSAGE.SET_NAME('AR', 'HZ_MODIFY_CLASS_CODE_MEANING');
3601 FND_MSG_PUB.ADD;
3602 RAISE FND_API.G_EXC_ERROR;
3603 end if;
3604
3605 --Bug fix 2783498
3606 IF ( l_class_code_rec.start_date_active IS NULL OR
3607 l_class_code_rec.start_date_active = FND_API.G_MISS_DATE ) THEN
3608 l_class_code_rec.start_date_active := SYSDATE;
3609 END IF;
3610
3611 IF l_class_code_rec.end_date_active = FND_API.G_MISS_DATE THEN
3612 l_class_code_rec.end_date_active := TO_DATE(NULL);
3613 END IF;
3614
3615 -- Bug 2960224 :Added validation to TYPE against active class categories.
3616
3617 HZ_CLASS_VALIDATE_V2PUB.check_existence_class_category(l_class_code_rec.type, x_return_status);
3618
3619 if(x_return_status = fnd_api.g_ret_sts_error) then
3620 RAISE FND_API.G_EXC_ERROR;
3621
3622 -- Bug 6158794: Added validation to TYPE against classification code and classification meaning.
3623 end if;
3624
3625 HZ_CLASS_VALIDATE_V2PUB.chk_exist_cls_catgry_type_code(l_class_code_rec.type,l_class_code_rec.code,0,222,x_return_status);
3626 if(x_return_status = fnd_api.g_ret_sts_error) then
3630 HZ_CLASS_VALIDATE_V2PUB.chk_exist_clas_catgry_typ_mng(l_class_code_rec.type,l_class_code_rec.meaning,0,222,x_return_status);
3627 RAISE FND_API.G_EXC_ERROR;
3628 end if;
3629
3631 if(x_return_status = fnd_api.g_ret_sts_error) then
3632 RAISE FND_API.G_EXC_ERROR;
3633
3634 end if;
3635
3636 Fnd_Lookup_Values_Pkg.Insert_Row(
3637 X_ROWID => row_id,
3638 X_LOOKUP_TYPE => l_class_code_rec.type,
3639 X_SECURITY_GROUP_ID => 0,
3640 X_VIEW_APPLICATION_ID => 222,
3641 X_LOOKUP_CODE => l_class_code_rec.code,
3642 X_TAG => null,
3643 X_ATTRIBUTE_CATEGORY => l_class_code_rec.attribute_category,
3644 X_ATTRIBUTE1 => l_class_code_rec.attribute1,
3645 X_ATTRIBUTE2 => l_class_code_rec.attribute2,
3646 X_ATTRIBUTE3 => l_class_code_rec.attribute3,
3647 X_ATTRIBUTE4 => l_class_code_rec.attribute4,
3648 X_ENABLED_FLAG => l_class_code_rec.enabled_flag,
3649 X_START_DATE_ACTIVE => l_class_code_rec.start_date_active,
3650 X_END_DATE_ACTIVE => l_class_code_rec.end_date_active,
3651 X_TERRITORY_CODE => null,
3652 X_ATTRIBUTE5 => l_class_code_rec.attribute5,
3653 X_ATTRIBUTE6 => l_class_code_rec.attribute6,
3654 X_ATTRIBUTE7 => l_class_code_rec.attribute7,
3655 X_ATTRIBUTE8 => l_class_code_rec.attribute8,
3656 X_ATTRIBUTE9 => l_class_code_rec.attribute9,
3657 X_ATTRIBUTE10 => l_class_code_rec.attribute10,
3658 X_ATTRIBUTE11 => l_class_code_rec.attribute11,
3659 X_ATTRIBUTE12 => l_class_code_rec.attribute12,
3660 X_ATTRIBUTE13 => l_class_code_rec.attribute13,
3661 X_ATTRIBUTE14 => l_class_code_rec.attribute14,
3662 X_ATTRIBUTE15 => l_class_code_rec.attribute15,
3663 X_MEANING => l_class_code_rec.meaning,
3664 X_DESCRIPTION => l_class_code_rec.description,
3665 X_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
3666 X_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
3667 X_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
3668 X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
3669 X_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN);
3670
3671 set_frozen_flag(l_class_code_rec.type);
3672
3673 -- Bug 5053099: Raise business events.
3674 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3675 -- Invoke business event system.
3676 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
3677 HZ_BUSINESS_EVENT_V2PVT.create_class_code_event (
3678 l_class_code_rec );
3679 END IF;
3680 END IF;
3681
3682 EXCEPTION
3683
3684 WHEN FND_API.G_EXC_ERROR THEN
3685 ROLLBACK TO create_class_code;
3686 x_return_status := FND_API.G_RET_STS_ERROR;
3687 FND_MSG_PUB.Count_And_Get(
3688 p_encoded => FND_API.G_FALSE,
3689 p_count => x_msg_count,
3690 p_data => x_msg_data);
3691
3692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3693 ROLLBACK TO create_class_code ;
3694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695 FND_MSG_PUB.Count_And_Get(
3696 p_encoded => FND_API.G_FALSE,
3697 p_count => x_msg_count,
3698 p_data => x_msg_data);
3699
3700 WHEN OTHERS THEN
3701 ROLLBACK TO create_class_code;
3702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3703 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3704 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3705 FND_MSG_PUB.ADD;
3706 FND_MSG_PUB.Count_And_Get(
3707 p_encoded => FND_API.G_FALSE,
3708 p_count => x_msg_count,
3709 p_data => x_msg_data);
3710
3711
3712
3713
3714 end create_class_code;
3715
3716 /**
3717 * PROCEDURE update_class_code
3718 *
3719 * DESCRIPTION
3720 * This is a wrapper on top of FND_LOOKUP_VALUES_PKG.update_row. It also
3721 * updates frozen flag and validate class code meaning.
3722 *
3723 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3724 *
3725 * ARGUMENTS
3726 * IN:
3727 * p_init_msg_list Initialize message stack if it is set to
3728 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3729 * p_class_code_rec Lookup value related columns
3730 * IN/OUT:
3731 * p_object_version_number Used for locking the being updated record.
3732 * OUT:
3733 * x_return_status Return status after the call. The status can
3734 * be FND_API.G_RET_STS_SUCCESS (success),
3735 * FND_API.G_RET_STS_ERROR (error),
3736 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3737 * x_msg_count Number of messages in message stack.
3738 * x_msg_data Message text if x_msg_count is 1.
3739 *
3740 * NOTES
3741 *
3742 * MODIFICATION HISTORY
3743 *
3744 * 05-28-2002 Amy Wu o Created.
3745 *
3746 */
3747
3748 PROCEDURE update_class_code(
3749 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
3750 p_class_code_rec IN CLASS_CODE_REC_TYPE,
3751 p_object_version_number IN OUT NOCOPY NUMBER,
3752 x_return_status OUT NOCOPY VARCHAR2,
3753 x_msg_count OUT NOCOPY NUMBER,
3754 x_msg_data OUT NOCOPY VARCHAR2
3755 ) is
3756
3757 l_class_code_rec CLASS_CODE_REC_TYPE := p_class_code_rec;
3761 savepoint update_class_code;
3758 l_end_date_active DATE;
3759 l_start_date_active DATE;
3760 begin
3762
3763 -- initialize message list if p_init_msg_list is set to TRUE.
3764 IF FND_API.to_Boolean(p_init_msg_list) THEN
3765 FND_MSG_PUB.initialize;
3766 END IF;
3767
3768 --Bug fix 2783498
3769 SELECT start_date_active,end_date_active into l_start_date_active,l_end_date_active
3770 FROM fnd_lookup_values_vl val
3771 WHERE val.lookup_type = l_class_code_rec.type
3772 AND val.lookup_code = l_class_code_rec.code
3773 AND rownum = 1
3774 FOR UPDATE OF LOOKUP_CODE NOWAIT;
3775
3776 IF l_class_code_rec.start_date_active IS NULL THEN
3777 l_class_code_rec.start_date_active := l_start_date_active;
3778 ELSIF l_class_code_rec.start_date_active = FND_API.G_MISS_DATE THEN
3779 l_class_code_rec.start_date_active := SYSDATE;
3780 END IF;
3781
3782 IF l_class_code_rec.end_date_active IS NULL THEN
3783 l_class_code_rec.end_date_active := l_end_date_active;
3784 elsif l_class_code_rec.end_date_active = FND_API.G_MISS_DATE THEN
3785 l_class_code_rec.end_date_active := TO_DATE(NULL);
3786 END IF;
3787
3788
3789 -- Initialize return status to SUCCESS
3790 x_return_status := FND_API.G_RET_STS_SUCCESS;
3791
3792 if (l_class_code_rec.meaning is not null and l_class_code_rec.meaning <> fnd_api.g_miss_char)
3793 then
3794 If HZ_CLASS_VALIDATE_V2PUB.is_valid_class_code_meaning(l_class_code_rec.type,l_class_code_rec.meaning)='N'
3795 then
3796 FND_MESSAGE.SET_NAME('AR', 'HZ_MODIFY_CLASS_CODE_MEANING');
3797 FND_MSG_PUB.ADD;
3798 RAISE FND_API.G_EXC_ERROR;
3799 end if;
3800 end if;
3801
3802 Fnd_Lookup_Values_Pkg.Update_Row(
3803 X_LOOKUP_TYPE => l_class_code_rec.type,
3804 X_SECURITY_GROUP_ID => 0,
3805 X_VIEW_APPLICATION_ID => 222,
3806 X_LOOKUP_CODE => l_class_code_rec.code,
3807 X_TAG => null,
3808 X_ATTRIBUTE_CATEGORY => l_class_code_rec.attribute_category,
3809 X_ATTRIBUTE1 => l_class_code_rec.attribute1,
3810 X_ATTRIBUTE2 => l_class_code_rec.attribute2,
3811 X_ATTRIBUTE3 => l_class_code_rec.attribute3,
3812 X_ATTRIBUTE4 => l_class_code_rec.attribute4,
3813 X_ENABLED_FLAG => l_class_code_rec.enabled_flag,
3814 X_START_DATE_ACTIVE => l_class_code_rec.start_date_active,
3815 X_END_DATE_ACTIVE => l_class_code_rec.end_date_active,
3816 X_TERRITORY_CODE => null,
3817 X_ATTRIBUTE5 => l_class_code_rec.attribute5,
3818 X_ATTRIBUTE6 => l_class_code_rec.attribute6,
3819 X_ATTRIBUTE7 => l_class_code_rec.attribute7,
3820 X_ATTRIBUTE8 => l_class_code_rec.attribute8,
3821 X_ATTRIBUTE9 => l_class_code_rec.attribute9,
3822 X_ATTRIBUTE10 => l_class_code_rec.attribute10,
3823 X_ATTRIBUTE11 => l_class_code_rec.attribute11,
3824 X_ATTRIBUTE12 => l_class_code_rec.attribute12,
3825 X_ATTRIBUTE13 => l_class_code_rec.attribute13,
3826 X_ATTRIBUTE14 => l_class_code_rec.attribute14,
3827 X_ATTRIBUTE15 => l_class_code_rec.attribute15,
3828 X_MEANING => l_class_code_rec.meaning,
3829 X_DESCRIPTION => l_class_code_rec.description,
3830 X_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
3831 X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
3832 X_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN);
3833
3834 set_frozen_flag(l_class_code_rec.type);
3835
3836 -- Bug 5053099: Raise businss events.
3837 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3838 -- Invoke business event system.
3839 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
3840 HZ_BUSINESS_EVENT_V2PVT.update_class_code_event (
3841 p_class_code_rec => l_class_code_rec,
3842 p_old_class_code_rec => NULL );
3843 END IF;
3844 END IF;
3845
3846 EXCEPTION
3847
3848 WHEN FND_API.G_EXC_ERROR THEN
3849 ROLLBACK TO update_class_code;
3850 x_return_status := FND_API.G_RET_STS_ERROR;
3851 FND_MSG_PUB.Count_And_Get(
3852 p_encoded => FND_API.G_FALSE,
3853 p_count => x_msg_count,
3854 p_data => x_msg_data);
3855
3856 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3857 ROLLBACK TO update_class_code ;
3858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3859 FND_MSG_PUB.Count_And_Get(
3860 p_encoded => FND_API.G_FALSE,
3861 p_count => x_msg_count,
3862 p_data => x_msg_data);
3863
3864 WHEN OTHERS THEN
3865 ROLLBACK TO update_class_code;
3866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3867 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3868 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3869 FND_MSG_PUB.ADD;
3870 FND_MSG_PUB.Count_And_Get(
3871 p_encoded => FND_API.G_FALSE,
3872 p_count => x_msg_count,
3873 p_data => x_msg_data);
3874
3875
3876 end update_class_code;
3877
3878 -- This procedure v2_copy_class_category is used only for the HTML Admin UI
3879 -- This is used to "copy" class category, class codes, and their relations
3880 -- onto a new class category.
3881
3882 PROCEDURE v2_copy_class_category (
3883 p_class_category IN VARCHAR2,
3884 p_copy_class_category IN VARCHAR2,
3885 x_return_status IN OUT NOCOPY VARCHAR2
3886 ) IS
3887
3888 x_class_category_rec
3889 HZ_CLASSIFICATION_V2PUB.CLASS_CATEGORY_REC_TYPE;
3890 x_class_code_rec HZ_CLASSIFICATION_V2PUB.CLASS_CODE_REC_TYPE;
3891 x_class_code_relation_rec
3892 HZ_CLASSIFICATION_V2PUB.CLASS_CODE_RELATION_REC_TYPE;
3893
3894 l_lookup_type VARCHAR2(30);
3895 l_lookup_code VARCHAR2(30);
3896 l_meaning VARCHAR2(80);
3897 l_description VARCHAR2(240);
3898 l_start_date_active DATE;
3899 l_end_date_active DATE;
3900 l_enabled_flag VARCHAR2(1);
3901
3902 x_msg_count NUMBER;
3903 x_msg_data VARCHAR2(2000);
3904
3905 --Create cursor for class code
3906 CURSOR C_codes_cursor
3907 IS
3908 SELECT LOOKUP_CODE,
3909 MEANING,
3910 DESCRIPTION,
3911 START_DATE_ACTIVE,
3912 END_DATE_ACTIVE,
3913 ENABLED_FLAG
3914 FROM FND_LOOKUP_VALUES
3915 WHERE LOOKUP_TYPE = p_class_category;
3916
3917 --Create cursor for class code relations
3918 CURSOR C_codes_rel_cursor
3919 IS
3920 SELECT CLASS_CODE,
3921 SUB_CLASS_CODE,
3922 START_DATE_ACTIVE,
3923 END_DATE_ACTIVE
3924 FROM HZ_CLASS_CODE_RELATIONS
3925 WHERE CLASS_CATEGORY = p_class_category;
3926
3927
3928 BEGIN
3929
3930 --Copying of Class codes
3931 FOR codeInfo IN C_codes_cursor LOOP
3932 x_class_code_rec.TYPE := p_copy_class_category;
3933 x_class_code_rec.CODE := codeInfo.LOOKUP_CODE;
3934 x_class_code_rec.MEANING := codeInfo.MEANING;
3935 x_class_code_rec.DESCRIPTION := codeInfo.DESCRIPTION;
3936 x_class_code_rec.START_DATE_ACTIVE := codeInfo.START_DATE_ACTIVE;
3937 x_class_code_rec.END_DATE_ACTIVE := codeInfo.END_DATE_ACTIVE;
3938 x_class_code_rec.ENABLED_FLAG := codeInfo.ENABLED_FLAG;
3939
3940 --Now call the API to create class code
3941 HZ_CLASSIFICATION_V2PUB.Create_Class_Code(
3942 'T',
3943 x_class_code_rec,
3944 x_return_status,
3945 x_msg_count,
3946 x_msg_data);
3947 --Output the results
3948 END LOOP;
3949
3950 --Copying of Class code relations
3951 FOR relInfo IN C_codes_rel_cursor LOOP
3952 x_class_code_relation_rec.CLASS_CATEGORY := p_copy_class_category;
3953 x_class_code_relation_rec.CLASS_CODE := relInfo.CLASS_CODE;
3954 x_class_code_relation_rec.SUB_CLASS_CODE := relInfo.SUB_CLASS_CODE;
3955 x_class_code_relation_rec.START_DATE_ACTIVE := relInfo.START_DATE_ACTIVE;
3956 x_class_code_relation_rec.END_DATE_ACTIVE := relInfo.END_DATE_ACTIVE;
3957 x_class_code_relation_rec.APPLICATION_ID := 222;
3958 x_class_code_relation_rec.CREATED_BY_MODULE := 'HTML_ADMIN_UI';
3959
3960 --Now call the API to create class code relation
3961 HZ_CLASSIFICATION_V2PUB.Create_Class_Code_Relation(
3962 'T',
3963 x_class_code_relation_rec,
3964 x_return_status,
3965 x_msg_count,
3966 x_msg_data);
3967 --Output the results
3968 END LOOP;
3969
3970 EXCEPTION
3971 WHEN FND_API.G_EXC_ERROR THEN
3972 x_return_status := FND_API.G_RET_STS_ERROR;
3973 FND_MSG_PUB.Count_And_Get(
3974 p_encoded => FND_API.G_FALSE,
3975 p_count => x_msg_count,
3976 p_data => x_msg_data);
3977
3978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3980 FND_MSG_PUB.Count_And_Get(
3981 p_encoded => FND_API.G_FALSE,
3982 p_count => x_msg_count,
3983 p_data => x_msg_data);
3984
3985 WHEN OTHERS THEN
3986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3987 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3988 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3989 FND_MSG_PUB.ADD;
3990 FND_MSG_PUB.Count_And_Get(
3991 p_encoded => FND_API.G_FALSE,
3992 p_count => x_msg_count,
3993 p_data => x_msg_data);
3994
3995 END v2_copy_class_category;
3996
3997 END HZ_CLASSIFICATION_V2PUB;