1 PACKAGE ARH_CLASSIFICATION_PKG AUTHID CURRENT_USER AS
2 /*$Header: ARCLAASS.pls 115.2 2002/12/30 18:20:59 hyu noship $*/
3
4 /*------------------------------------------------------+
5 | Name : Create_Code_assignment |
6 | |
7 | Description : |
8 | Wrapper on the top TCA V2 API for |
9 | Code assignment creation . |
10 | |
11 | Parameter : |
12 | From the record type |
13 | HZ_CLASSIFCIATION_V2PUB.CODE_ASSIGNEMENT_REC_TYPE |
14 | p_owner_table_name table using classification |
15 | p_owner_table_id id frm that table |
16 | p_class_category class_category |
17 | p_class_code class code |
18 | p_start_date_active start date of the assignment|
19 | p_end_date_active end date of the assignment |
20 | p_primary_flag primary Y or N |
21 | p_content_source_type origin of the assugnment |
22 | p_status status |
23 | p_created_by_module creation module |
24 | p_rank for hierarchy assignment |
25 | p_application_id application |
26 | x_code_assignment_id OUT assignment id |
27 | x_return_status OUT status execution |
28 | x_msg_count OUT number of error met |
29 | x_msg_data OUT the error message |
30 +------------------------------------------------------*/
31 PROCEDURE Create_Code_assignment
32 ( p_owner_table_name IN VARCHAR2,
33 p_owner_table_id IN NUMBER,
34 p_class_category IN VARCHAR2,
35 p_class_code IN VARCHAR2,
36 p_start_date_active IN DATE DEFAULT SYSDATE,
37 p_end_date_active IN DATE,
38 p_primary_flag IN VARCHAR2,
39 p_content_source_type IN VARCHAR2,
40 p_status IN VARCHAR2 DEFAULT 'A',
41 p_created_by_module IN VARCHAR2 DEFAULT 'TCA_FORM_WRAPPER',
42 p_rank IN VARCHAR2 DEFAULT NULL,
43 p_application_id IN NUMBER DEFAULT 222,
44 x_code_assignment_id OUT NOCOPY NUMBER,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2 );
48
49 /*------------------------------------------------------+
50 | Name : Update_Code_assignment |
51 | |
52 | Description : |
53 | Wrapper on the top TCA V2 API for |
54 | Code assignment updation . |
55 | |
56 | Parameter : |
57 | From the record type |
58 | HZ_CLASSIFCIATION_V2PUB.CODE_ASSIGNEMENT_REC_TYPE |
59 | p_class_category class_category |
60 | p_class_code class code |
61 | p_start_date_active start date of the assignment|
62 | p_end_date_active end date of the assignment |
63 | p_primary_flag primary Y or N |
64 | p_content_source_type origin of the assugnment |
65 | p_status status |
66 | p_rank for hierarchy assignment |
67 | x_object_version_number record vesrion |
68 | x_code_assignment_id OUT assignment id |
69 | x_return_status OUT status execution |
70 | x_msg_count OUT number of error met |
71 | x_msg_data OUT the error message |
72 +------------------------------------------------------*/
73 PROCEDURE Update_Code_assignment
74 ( p_code_assignment_id IN NUMBER,
75 p_class_category IN VARCHAR2,
76 p_class_code IN VARCHAR2,
77 p_start_date_active IN DATE,
78 p_end_date_active IN DATE,
79 p_content_source_type IN VARCHAR2,
80 p_primary_flag IN VARCHAR2,
81 p_status IN VARCHAR2,
82 p_rank IN NUMBER,
83 x_object_version_number IN OUT NOCOPY NUMBER,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_msg_count OUT NOCOPY NUMBER,
86 x_msg_data OUT NOCOPY VARCHAR2 );
87
88 /*------------------------------------------------------+
89 | Name : is_assignment_active_today |
90 | |
91 | Description : |
92 | Check if there is any assignment today |
93 | |
94 | Parameter : |
95 | p_owner_table_name table using classification |
96 | p_owner_table_id id frm that table |
97 | p_class_category class_category |
98 | p_class_code class code |
99 | Return : |
100 | Y if there are any |
101 | N otherwise |
102 +------------------------------------------------------*/
103 FUNCTION is_assignment_active_today
104 ( p_owner_table_name IN VARCHAR2,
105 p_owner_table_id IN NUMBER,
106 p_class_category IN VARCHAR2,
107 p_class_Code IN VARCHAR2)
108 RETURN VARCHAR2;
109
110 /*------------------------------------------------------+
111 | Name : exist_assignment_not_ended |
112 | |
113 | Description : |
114 | Check if there is any assignment without end date |
115 | |
116 | Parameter : |
117 | p_owner_table_name table using classification |
118 | p_owner_table_id id from that table |
119 | p_class_category class_category |
120 | p_class_code class code |
121 | Return : |
122 | Y if there are any |
123 | N otherwise |
124 +------------------------------------------------------*/
125 FUNCTION exist_assignment_not_ended
126 ( p_owner_table_name IN VARCHAR2,
127 p_owner_table_id IN NUMBER,
128 p_class_category IN VARCHAR2,
129 p_class_code IN VARCHAR2)
130 RETURN VARCHAR2;
131
132 /*------------------------------------------------------+
133 | Name : exist_at_least_nb_assig |
137 | |
134 | |
135 | Description : |
136 | Check if there are more than a number of assignments|
138 | Parameter : |
139 | p_owner_table_name table using classification |
140 | p_owner_table_id id frm that table |
141 | p_class_category class_category |
142 | p_class_code class code |
143 | p_nb Number of assignment |
144 | Return : |
145 | Y if there are any |
146 | N otherwise |
147 +------------------------------------------------------*/
148 FUNCTION exist_at_least_nb_assig
149 ( p_owner_table_name IN VARCHAR2,
150 p_owner_table_id IN NUMBER,
151 p_class_category IN VARCHAR2,
152 p_class_code IN VARCHAR2,
153 p_nb IN NUMBER DEFAULT 2)
154 RETURN VARCHAR2;
155
156
157 /*-------------------------------------------------------+
158 | Name : is_between |
159 | |
160 | Description : |
161 | Check if datex is between date1 and date2 inclusively|
162 | or exclusive |
163 | INC = inclusive |
164 | EXC = Exclusive |
165 | |
166 | Parameter : |
167 | datex DATE |
168 | date1 DATE |
169 | date2 DATE |
170 | inc_exc1 VARCHAR2 in 'INC','EXC' |
171 | inc_exc2 VARCHAR2 in 'INC','EXC' |
172 | Return : |
173 | 'Y' if datex is between date1 and date2 |
174 | 'N' otherwise |
175 +-------------------------------------------------------*/
176 FUNCTION is_between
177 ( datex IN DATE,
178 date1 IN DATE,
179 date2 IN DATE,
180 inc_exc1 IN VARCHAR2 DEFAULT 'INC',
181 inc_exc2 IN VARCHAR2 DEFAULT 'INC')
182 RETURN VARCHAR2;
183
184
188 | Description : |
185 /*------------------------------------------------------+
186 | Name : is_overlap |
187 | |
189 | check if period (s1 e1) overlaps (s2 e2) |
190 | exclusive or inclusively |
191 | |
192 | Parameter : |
193 | s1 DATE |
194 | e1 DATE |
195 | s2 DATE |
196 | e2 DATE |
197 | inc_exc VARCHAR2 in 'INC', 'EXC' |
198 | Return : |
199 | 'Y' overlap |
200 | 'N' otherwise |
201 +------------------------------------------------------*/
202 FUNCTION is_overlap
203 (s1 IN DATE,
204 e1 IN DATE,
205 s2 IN DATE,
206 e2 IN DATE,
207 inc_exc IN VARCHAR2)
208 RETURN VARCHAR2;
209
210 /*------------------------------------------------------+
211 | Name : exist_overlap_assignment |
212 | |
213 | Description : |
217 | Parameter : |
214 | check if there are any assignment overlapping |
215 | a time period |
216 | |
218 | p_owner_table_name table using classification |
219 | p_owner_table_id id frm that table |
220 | p_class_category class_category |
221 | p_class_code class code |
222 | p_start_date_active start date of the assignment|
223 | p_end_date_active end date of the assignment |
224 | p_mode INSERT or UPDATE |
225 | p_code_assignment_id assignment id |
226 | Return : |
227 | 'Y' overlap |
228 | 'N' otherwise |
229 +------------------------------------------------------*/
230 FUNCTION exist_overlap_assignment
231 ( p_owner_table_name IN VARCHAR2 DEFAULT NULL,
232 p_owner_table_id IN NUMBER DEFAULT NULL,
233 p_class_category IN VARCHAR2 DEFAULT NULL,
234 p_class_Code IN VARCHAR2 DEFAULT NULL,
235 p_start_date_active IN DATE,
236 p_end_date_active IN DATE,
237 p_mode IN VARCHAR2,
238 p_code_assignment_id IN NUMBER DEFAULT NULL)
239 RETURN VARCHAR2;
240
241 /*------------------------------------------------------+
242 | Name : assig_after_this_date |
246 | date |
243 | |
244 | Description : |
245 | check if there are any assignment start after a |
247 | |
248 | Parameter : |
249 | p_owner_table_name table using classification |
250 | p_owner_table_id id frm that table |
251 | p_class_category class_category |
252 | p_class_code class code |
253 | p_start_date_active start date of the assignment|
254 | Return : |
255 | 'Y' if there are any |
256 | 'N' otherwise |
257 +------------------------------------------------------*/
258 FUNCTION assig_after_this_date
259 ( p_owner_table_name IN VARCHAR2 DEFAULT NULL,
260 p_owner_table_id IN NUMBER DEFAULT NULL,
261 p_class_category IN VARCHAR2 DEFAULT NULL,
262 p_class_Code IN VARCHAR2 DEFAULT NULL,
263 p_start_date_active IN DATE)
264 RETURN VARCHAR2;
265
266 END;