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