1 PACKAGE hz_class_validate_v2pub AS
2 /*$Header: ARH2CLVS.pls 120.3.12000000.2 2007/10/01 15:10:27 manjayar ship $ */
3 /*---------------------------------------------------------
4 -- Component for every entities in classification module-
5 ---------------------------------------------------------*/
6 PROCEDURE check_existence_class_category
7 -- Check if the class_Category exists
8 (p_class_category IN VARCHAR2,
9 x_return_status IN OUT NOCOPY VARCHAR2);
10
11 FUNCTION is_valid_delimiter(p_class_category in varchar2, p_delimiter in
12 varchar2) return varchar2;
13
14 FUNCTION is_valid_class_code_meaning(p_class_category in varchar2, p_meaning in
15 varchar2) return varchar2;
16
17 /*
18 FUNCTION result_caller
19 (pack VARCHAR2,
20 comp VARCHAR2,
21 code0 VARCHAR2 DEFAULT NULL,
22 code1 VARCHAR2 DEFAULT NULL,
23 code2 VARCHAR2 DEFAULT NULL,
24 code3 VARCHAR2 DEFAULT NULL,
25 code4 VARCHAR2 DEFAULT NULL,
26 code5 VARCHAR2 DEFAULT NULL,
27 code6 VARCHAR2 DEFAULT NULL,
28 code7 VARCHAR2 DEFAULT NULL,
29 code8 VARCHAR2 DEFAULT NULL,
30 code9 VARCHAR2 DEFAULT NULL,
31 date0 DATE DEFAULT NULL,
32 date1 DATE DEFAULT NULL,
33 date2 DATE DEFAULT NULL,
34 date3 DATE DEFAULT NULL,
35 date4 DATE DEFAULT NULL,
36 date5 DATE DEFAULT NULL,
37 date6 DATE DEFAULT NULL,
38 date7 DATE DEFAULT NULL,
39 text VARCHAR2 DEFAULT NULL)
40 RETURN VARCHAR2;
41 */
42
43 /* --Bug 3962783
44 procedure check_err(
45 x_return_status IN VARCHAR2
46 );
47 */
48
49 PROCEDURE check_start_end_active_dates(
50 p_start_date_active IN DATE,
51 p_end_date_active IN DATE,
52 x_return_status IN OUT NOCOPY VARCHAR2);
53
54
55 /*--------------------------------------
56 -- Validation for Hz_Class_Categories -
57 ---------------------------------------*/
58 FUNCTION exist_code_ass_not_node
59 -- This function answer to the question:
60 -- Return 'Y' if the category has one or more Non-Leaf-node Class Codes associated with instances of entities
61 -- active for to_date
62 -- 'N' otherwise
63 ( p_class_category IN VARCHAR2)
64 RETURN VARCHAR2;
65
66 FUNCTION exist_reverse_relation
67 -- Return 'Y' if the entered sub-code was defined as the parent-code of the entered class-code within that category
68 -- for active periods
69 -- 'N' otherwise
70 ( p_class_category IN VARCHAR2,
71 p_class_code IN VARCHAR2,
72 p_sub_class_code IN VARCHAR2,
73 p_start_date_active IN DATE,
74 p_end_date_active IN DATE)
75 RETURN VARCHAR2;
76
77 FUNCTION is_all_code_one_parent_only
78 -- Return Y if all class codes inside a category have no more than one parent for the current and futur period
79 -- N otherwise
80 (p_class_category VARCHAR2,
81 x_class_code IN OUT NOCOPY VARCHAR2,
82 x_class_code2 IN OUT NOCOPY VARCHAR2,
83 x_sub_class_code IN OUT NOCOPY VARCHAR2,
84 x_start_date_active IN OUT NOCOPY DATE,
85 x_end_date_active IN OUT NOCOPY DATE,
86 x_start_date_active2 IN OUT NOCOPY DATE,
87 x_end_date_active2 IN OUT NOCOPY DATE )
88 RETURN VARCHAR2;
89
90 FUNCTION is_all_inst_less_one_code
91 -- Return Y if all the instances of 1 entity has 0 to 1 code assigned
92 -- for 1 category, 1 content active to day or in the futur.
93 -- N otherwise
94 ( p_class_category VARCHAR2,
95 x_owner_table IN OUT NOCOPY VARCHAR2,
96 x_owner_table_id IN OUT NOCOPY VARCHAR2,
97 x_content_source_type IN OUT NOCOPY VARCHAR2,
98 x_class_code IN OUT NOCOPY VARCHAR2,
99 x_class_code2 IN OUT NOCOPY VARCHAR2,
100 x_start_date_active IN OUT NOCOPY DATE,
101 x_end_date_active IN OUT NOCOPY DATE,
102 x_start_date_active2 IN OUT NOCOPY DATE,
103 x_end_date_active2 IN OUT NOCOPY DATE )
104 RETURN VARCHAR2;
105
106 FUNCTION exist_class_category
107 -- Return Y if the class category exists
108 -- N otherwise
109 (p_class_category VARCHAR2 )
110 RETURN VARCHAR2;
111
112 /*===========================================================================+
113 | PROCEDURE |
114 | validate_class_category |
115 | DESCRIPTION |
116 | SCOPE - PRIVATE |
117 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
118 | ARGUMENTS : IN: p_class_cat_rec |
119 | create_update_flag |
120 | IN/ OUT: x_return_status |
121 | RETURNS : NONE |
122 | NOTES |
123 | MODIFICATION HISTORY |
124 | Young Li 22-JUN-00 Created |
125 +===========================================================================*/
126 procedure validate_class_category(
127 p_class_cat_rec IN HZ_CLASSIFICATION_V2PUB.CLASS_CATEGORY_REC_TYPE,
128 create_update_flag IN VARCHAR2,
129 x_return_status IN OUT NOCOPY VARCHAR2
130 );
131
132
133 /*-----------------------------------------
134 -- Validation for Hz_Class_Category_Uses -
135 ------------------------------------------*/
136 FUNCTION existence_couple_clacat_owntab
137 -- Return 'Y' if the couple exits
138 -- 'N' otherwise
139 ( p_create_update_flag IN VARCHAR2,
140 p_class_category IN VARCHAR2,
141 p_owner_table IN VARCHAR2 )
142 RETURN VARCHAR2;
143
144 /*===========================================================================+
145 | PROCEDURE |
146 | validate_class_category_use |
147 | DESCRIPTION |
148 | SCOPE - PRIVATE |
149 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
150 | ARGUMENTS : IN: |
151 | hz_classification_V2PUB.class_category_use_rec_type |
152 | create_update_flag |
153 | IN/ OUT: |
154 | x_return_status |
155 | RETURNS : NONE |
156 | MODIFICATION HISTORY |
157 | Herve Yu 18-JUN-01 Created |
158 +===========================================================================*/
159 PROCEDURE validate_class_category_use(
160 p_in_rec IN hz_classification_V2PUB.class_category_use_rec_type,
161 create_update_flag IN VARCHAR2,
162 x_return_status IN OUT NOCOPY VARCHAR2 );
163
164
165 /*-------------------------------------------
166 -- Validation for Hz_Class_Code_Assignment -
167 --------------------------------------------*/
168 FUNCTION date_betw_value_dates
169 -- Return 'Y' if p_date_active is between the active dates of the particular Class Code
170 -- 'N' otherwise
171 ( p_class_category IN VARCHAR2,
172 p_class_code IN VARCHAR2,
173 p_start_date_active IN DATE )
174 RETURN VARCHAR2;
175
176 FUNCTION instance_already_assigned
177 -- Return 'Y' If for ( 1 entity, 1 instance, 1 category , 1 content source, 1 period ),
178 -- we find at least 1 code different
179 -- Return 'N' otherwise
180 ( p_start_date_active DATE,
181 p_end_date_active DATE,
182 p_owner_table_name VARCHAR2,
183 p_owner_table_id VARCHAR2,
184 p_class_category VARCHAR2,
185 p_content_source_type VARCHAR2,
186 x_class_code IN OUT NOCOPY VARCHAR2,
187 x_start_date_active IN OUT NOCOPY DATE,
188 x_end_date_active IN OUT NOCOPY DATE)
189 RETURN VARCHAR2;
190
191 FUNCTION is_leaf_node_category
192 -- Return 'Y' if the Class Category entered has its ALLOW_LEAF_NODE_ONLY_FLAG to Y
193 -- 'N' otherwise
194 ( p_class_category IN VARCHAR2)
195 RETURN VARCHAR2;
196
197 FUNCTION is_categ_multi_assig
198 -- Return 'Y' if the category has its allow_multi_assign_flag to Y
199 -- 'N' otherwise
200 ( p_class_category VARCHAR2)
201 RETURN VARCHAR2;
202
203 FUNCTION is_assig_record_id_valid
204 -- Returns Y If the Record ID in the owner table associated with the category is valid
205 -- and x_reason will content 'Table.column=value is valid against category.'
206 -- Otherwise N and x_reason will content the message name to display
207 -- HZ_API_USE_TAB_CAT if there is no usage between the category and the table
208 -- HZ_API_CLA_CAT_WHERE if the value cannot be validate against the where_clause
209 -- Standard Oracle error message otherwise
210 ( p_owner_table_name IN VARCHAR2,
211 p_owner_table_id IN VARCHAR2,
212 p_class_category IN VARCHAR2,
213 x_reason IN OUT NOCOPY VARCHAR2,
214 x_column_name IN OUT NOCOPY VARCHAR2)
215 RETURN VARCHAR2;
216
217
218 FUNCTION sql_valid
219 -- Returns 1) Y if the statement retrieve at least 1 value
220 -- And x_result will contain this value.
221 -- 2) N if the statement retrieve non value
222 -- And x_result will contain 'NON_VALUE'
223 -- 3) N if the statement falls into Error
224 -- And x_result contain the standard Oracle message
225 ( i_str IN VARCHAR2,
226 x_result IN OUT NOCOPY VARCHAR2 )
227 RETURN VARCHAR2;
228
229 FUNCTION sql_str_build
230 -- Returns 1) Y if it successfully construct the statement
231 -- And x_statement will contain the statement
232 -- And x_column_name will contain the column_name
233 -- 2) N if it fails in constructing the statement
234 -- And x_statement will contain the reason
235 ( p_owner_table_name IN VARCHAR2,
236 p_owner_table_id IN VARCHAR2,
237 p_class_category IN VARCHAR2,
238 x_column_name IN OUT NOCOPY VARCHAR2,
239 x_statement IN OUT NOCOPY VARCHAR2)
240 RETURN VARCHAR2;
241
242 function exist_pk_code_assign
243 -- Return 'Y' if one code_assignment_id is found for
244 -- 1 owner_table,
245 -- 1 owner_table_id
246 -- 1 category
247 -- 1 code
248 -- 1 source content type
249 -- 1 start date active
250 -- 'N' otherwise
251 (p_owner_table_name varchar2,
252 p_owner_table_id varchar2,
253 p_class_category varchar2,
254 p_class_code varchar2,
255 p_content_source_type varchar2,
256 p_start_date_active date,
257 x_id in out NOCOPY varchar2,
258 x_end_date in out NOCOPY date)
259 return varchar2;
260
261 function exist_prim_assign
262 ( create_update_flag varchar2,
263 p_class_category varchar2,
264 p_owner_table_name varchar2,
265 p_owner_table_id varchar2,
266 p_content_source_type varchar2,
267 p_start_date_active date,
268 p_end_date_active date,
269 x_class_code in out NOCOPY varchar2,
270 x_start_date in out NOCOPY date,
271 x_end_date in out NOCOPY date )
272 return varchar2;
273
274 function exist_same_code_assign
275 ( create_update_flag varchar2,
276 p_class_category varchar2,
277 p_class_code varchar2,
278 p_owner_table_name varchar2,
279 p_owner_table_id varchar2,
280 p_content_source_type varchar2,
281 p_start_date_active date,
282 p_end_date_active date,
283 x_class_code in out NOCOPY varchar2,
284 x_start_date in out NOCOPY date,
285 x_end_date in out NOCOPY date )
286 return varchar2;
287
288 function exist_second_assign_same_code
289 ( create_update_flag varchar2,
290 p_class_category varchar2,
291 p_class_code varchar2,
292 p_owner_table_name varchar2,
293 p_owner_table_id varchar2,
294 p_content_source_type varchar2,
295 p_start_date_active date,
296 p_end_date_active date,
297 x_class_code in out NOCOPY varchar2,
298 x_start_date in out NOCOPY date,
299 x_end_date in out NOCOPY date )
300 return varchar2;
301
302 procedure cre_upd_code_ass_com
303 ( p_create_update_flag varchar2,
304 p_class_category varchar2,
305 p_class_code varchar2,
306 p_owner_table_name varchar2,
307 p_owner_table_id varchar2,
308 p_content_source_type varchar2,
309 p_primary_flag varchar2,
310 p_start_date_active date,
311 p_end_date_active date ,
312 x_return_status IN OUT NOCOPY VARCHAR2);
313
314
315 /*===========================================================================+
316 | PROCEDURE |
317 | validate_code_assignment |
318 | DESCRIPTION |
319 | SCOPE - PRIVATE |
320 | ARGUMENTS : IN: p_in_rec |
321 | create_update_flag |
322 | IN/ OUT: x_return_status |
323 | RETURNS : NONE |
324 | MODIFICATION HISTORY |
325 | Young Li 22-JUN-00 Created |
326 | Herve Yu 24-JAN-01 Modify => MULTI_ASSIGNMENT_FLAG |
327 | |
328 +===========================================================================*/
329 procedure validate_code_assignment(
330 p_in_rec IN HZ_CLASSIFICATION_V2PUB.CODE_ASSIGNMENT_REC_TYPE,
331 create_update_flag IN VARCHAR2,
332 x_return_status IN OUT NOCOPY VARCHAR2
333 );
334
335
336 /*-------------------------------------------
337 -- Validation for Hz_Class_Code_Relations -
338 --------------------------------------------*/
339 TYPE gen_rec IS RECORD( class_code VARCHAR2(30),
340 sub_class_code VARCHAR2(30),
341 start_date_active DATE,
342 end_date_active DATE,
343 generation NUMBER);
344
345 TYPE gen_list IS TABLE OF gen_rec INDEX BY BINARY_INTEGER;
346
347 FUNCTION parent_code
348 -- Return Y if the class code in the class category has already one parent
349 -- N otherwise
350 ( p_class_category VARCHAR2,
351 p_class_code VARCHAR2,
352 p_start_date_active DATE,
353 p_end_date_active DATE,
354 x_child_code IN OUT NOCOPY VARCHAR2,
355 x_start_date_active IN OUT NOCOPY DATE,
356 x_end_date_active IN OUT NOCOPY DATE)
357 RETURN VARCHAR2;
358
359 FUNCTION child_code
360 -- Return Y if the p_class_code in the p_class_category for that period has one or more parent
361 -- N otherwise
362 (p_class_category VARCHAR2,
363 p_class_code VARCHAR2,
364 p_start_date_active DATE,
365 p_end_date_active DATE,
366 x_parent_code IN OUT NOCOPY VARCHAR2,
367 x_start_date_active IN OUT NOCOPY DATE,
368 x_end_date_active IN OUT NOCOPY DATE)
369 RETURN VARCHAR2;
370
371 FUNCTION is_categ_multi_parent
372 -- Return 'Y' if the category has its allow_multi_parent_flag to Y
373 -- 'N' otherwise
374 ( p_class_category VARCHAR2)
375 RETURN VARCHAR2;
376
377 FUNCTION previous_generation
378 -- Return a Gen_List fill with the parents class_code of the in_tab(i).class_code
379 -- i rang from 1 to the dimension of tab. All the parent codes
380 -- of all the class codes contained in in_tab will be returned in Gen_list
381 -- Parameter : The array containing the class_codes
382 -- The Class Category in which we want to find the parent codes
383 -- The duration during which we want to search. Every parent code which relation
384 -- with the class codes contained in in_tab is out NOCOPY of range will no more be
385 -- considered as a parent.
386 --
387 (in_tab in gen_list,
388 in_class_category in varchar2,
389 in_date_start in date,
390 in_date_end in date default null,
391 in_generation in number)
392 RETURN gen_list;
393
394 FUNCTION next_generation
395 -- Return a Gen_List fill with the sub_class_code of the in_tab(i).class_code
396 -- i rang from 1 to the dimension of tab. All the sub codes
397 -- of all the class codes contained in in_tab will be returned in Gen_list
398 -- Parameter : The array containing the class_codes
399 -- The Class Category in which we want to find the sub codes
400 -- The duration during which we want to search. Every sub code which relation
401 -- with the class codes contained in in_tab is out NOCOPY of range will no more be
402 -- considered as a child.
403 (in_tab in gen_list,
404 in_class_category in varchar2,
405 in_date_start in date,
406 in_date_end in date default null,
407 in_generation in number)
408 RETURN gen_list;
409
410 FUNCTION tab_concatenated
411 -- Return a Gen_List concatenated from
412 -- in_tab1 and
413 -- in_tab2
414 ( in_tab1 in gen_list,
415 in_tab2 in gen_list)
416 RETURN gen_list;
417
418 FUNCTION exist_rec_in_list_poc
419 -- Return 'Y' if the Gen_Rec is in the Gen_List depending on a def of existence
420 -- if in_poc = 'CODE' will be considered as existence those Records which Class_Code is the same
421 -- if in_poc = 'SUB' will be considered as existence those Records which Sub_Class_Code is the same
422 -- 'N' Otherwise
423 (in_tab in gen_list,
424 in_rec in gen_rec,
425 in_poc in VARCHAR2)
426 RETURN VARCHAR2;
427
428 FUNCTION tab_normal_poc
429 -- Return a Gen_Rec which is a sub set of the in_tab to avoid redundancies
430 -- Redundoncies depend on the def of existence in_poc ['CODE','SUB']
431 (in_tab in gen_list,
432 in_poc in VARCHAR2)
433 RETURN gen_list;
434
435 FUNCTION set_of_parents
436 -- Return a Gen_List of all the ancestor of a given Class Code
437 (in_class_category in varchar2,
438 in_class_code in varchar2,
439 in_date_start in date,
440 in_date_end in date default null)
441 RETURN gen_list;
442
443 FUNCTION set_of_children
444 -- Return a Gen_list with all the decendants of a given Sub Class Code
445 (in_class_category in varchar2,
446 in_sub_class_code in varchar2,
447 in_date_start in date,
448 in_date_end in date default null)
449 RETURN gen_list;
450
451 FUNCTION is_cod1_ancest_cod2
452 -- Return 'Y' if cod1 is ancestor of cod2
453 -- 'N' otherwise
454 (in_class_category in varchar2,
455 in_class_code_1 in varchar2,
456 in_class_code_2 in varchar2,
457 in_date_start in date,
458 in_date_end in date default null)
459 RETURN varchar2;
460
461 FUNCTION is_cod1_descen_cod2
462 -- Return 'Y' if cod1 is descendant of cod2
463 -- 'N' otherwise
464 (in_class_category in varchar2,
465 in_class_code_1 in varchar2,
466 in_class_code_2 in varchar2,
467 in_date_start in date,
468 in_date_end in date default null)
469 RETURN varchar2;
470
471 Function exist_pk_relation
472 -- Return 'Y' if the relation Already exists
473 -- 'N' otherwise
474 ( p_class_category varchar2,
475 p_class_code varchar2,
476 p_sub_class_code varchar2,
477 p_start_date_active date,
478 x_end_date_active in out NOCOPY date)
479 return varchar2;
480
481 Function exist_overlap_relation
482 -- returns 'Y' if it exists a relation which overlap the one we entered
483 -- 'N' otehrwise
484 ( p_create_update_flag varchar2,
485 p_class_category varchar2,
486 p_class_code varchar2,
487 p_sub_class_code varchar2,
488 p_start_date_active date,
489 p_end_date_active date,
490 x_start_date_active in out NOCOPY date,
491 x_end_date_active in out NOCOPY date )
492 Return varchar2;
493
494 /*===========================================================================+
495 | PROCEDURE |
496 | validate_class_code_relation |
497 | DESCRIPTION |
498 | Validates class_code_relation. Checks for: |
499 | lookup types |
500 | mandatory columns |
501 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
502 | ARGUMENTS : IN: |
503 | p_in_rec |
504 | create_update_flag |
505 | OUT: |
506 | IN/ OUT: |
507 | x_return_status |
508 | MODIFICATION HISTORY |
509 | Young Li 22-JUN-00 Created |
510 | Herve Yu 23-JAN-01 add use of exist_reverse_relation |
511 +===========================================================================*/
512 procedure validate_class_code_relation(
513 p_in_rec IN HZ_CLASSIFICATION_V2PUB.CLASS_CODE_RELATION_REC_TYPE,
514 create_update_flag IN VARCHAR2,
515 x_return_status IN OUT NOCOPY VARCHAR2
516 );
517
518 FUNCTION is_overlap
519 -- Returns 'Y' if period [s1,e1] overlaps [s2,e2]
520 -- 'N' otherwise
521 -- NULL indicates infinite for END dates
522 (s1 DATE,
523 e1 DATE,
524 s2 DATE,
525 e2 DATE)
526 RETURN VARCHAR2;
527
528 -----------------------------------------------------------------
529 /**
530 * PROCEDURE chk_exist_cls_catgry_type_code
531 *
532 * DESCRIPTION
533 * This procedure is used to check existing record for class category type,
534 * class code, security group id, application id, and language combination
535 * which are difined in FND_LOOKUP_VALUES_U1.
536 *
537 * ARGUMENTS
538 * IN:
539 * p_class_category_type Related to class category type column
540 * p_class_category_code Related to class code column
541 * p_security_group_id Rleated to security group id column
542 * p_view_application_id Related to application id column
543 *
544 * IN/OUT:
545 * x_return_status Return status after the call. The status can
546 * be FND_API.G_RET_STS_ERROR (error)
547 *
548 * NOTES
549 *
550 * CREATION/MODIFICATION HISTORY
551 *
552 * 09-20-2007 Manivannan J o Created for Bug 6158794.
553 */
554 -----------------------------------------------------------------
555
556
557 PROCEDURE chk_exist_cls_catgry_type_code
558 (
559 p_class_category_type IN VARCHAR2,
560 p_class_category_code IN VARCHAR2,
561 p_security_group_id IN NUMBER,
562 p_view_application_id IN NUMBER,
563 x_return_status IN OUT NOCOPY VARCHAR2);
564
565 -----------------------------------------------------------------
566 /**
567 * PROCEDURE chk_exist_clas_catgry_typ_mng
568 *
569 * DESCRIPTION
570 * This procedure is used to check existing record for class category type
571 * , class meaning, security group id, application id, and language combination
572 * which are difined in FND_LOOKUP_VALUES_U2.
573 *
574 * ARGUMENTS
575 * IN:
576 * p_class_category_type Related to class category type column
577 * p_class_category_meaning Related to class meaning column
578 * p_security_group_id Rleated to security group id column
579 * p_view_application_id Related to application id column
580 *
581 * IN/OUT:
582 * x_return_status Return status after the call. The status can
583 * be FND_API.G_RET_STS_ERROR (error)
584 *
585 * NOTES
586 *
587 * CREATION/MODIFICATION HISTORY
588 *
589 * 09-20-2007 Manivannan J o Created for Bug 6158794.
590 */
591 -----------------------------------------------------------------
592
593
594 PROCEDURE chk_exist_clas_catgry_typ_mng
595 (
596 p_class_category_type IN VARCHAR2,
597 p_class_category_meaning IN VARCHAR2,
598 p_security_group_id IN NUMBER,
599 p_view_application_id IN NUMBER,
600 x_return_status IN OUT NOCOPY VARCHAR2);
601
602 END hz_class_validate_v2pub;
[Home] [Help]