1 Package DT_Api As
2 /* $Header: dtapiapi.pkh 120.0 2005/05/27 23:09:51 appldev noship $ */
3 -- ----------------------------------------------------------------------------
4 -- |------------------------< Return_Max_End_Date >---------------------------|
5 -- ----------------------------------------------------------------------------
6 --
7 -- PRIVATE
8 --
9 -- Description: Function returns the maximum effective_end_date for the
10 -- specified table and primary key.
11 -- NOTE: if the maximum end date doesn't exist (i.e. no rows
12 -- exist for the specified table, key values) then we
13 -- return the null value.
14 -- ----------------------------------------------------------------------------
15 Function Return_Max_End_Date
16 (p_base_table_name in varchar2,
17 p_base_key_column in varchar2,
18 p_base_key_value in number)
19 Return Date;
20 -- ----------------------------------------------------------------------------
21 -- |-------------------------< Find_DT_Upd_Modes >----------------------------|
22 -- ----------------------------------------------------------------------------
23 --
24 -- PUBLIC
25 --
26 -- Description: Returns corresponding boolean values for the respective DT
27 -- update modes.
28 --
29 -- ----------------------------------------------------------------------------
30 Procedure Find_DT_Upd_Modes
31 (p_effective_date in date,
32 p_base_table_name in varchar2,
33 p_base_key_column in varchar2,
34 p_base_key_value in number,
35 p_correction out NOCOPY boolean,
36 p_update out NOCOPY boolean,
37 p_update_override out NOCOPY boolean,
38 p_update_change_insert out NOCOPY boolean);
39 -- ----------------------------------------------------------------------------
40 -- |-------------------------< Find_DT_Del_Modes >----------------------------|
41 -- ----------------------------------------------------------------------------
42 --
43 -- PUBLIC
44 --
45 -- Description: Returns corresponding boolean values for the respective DT
46 -- delete modes.
47 --
48 -- ----------------------------------------------------------------------------
49 Procedure Find_DT_Del_Modes
50 (p_effective_date in date,
51 p_base_table_name in varchar2,
52 p_base_key_column in varchar2,
53 p_base_key_value in number,
54 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
55 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
56 p_parent_key_value1 in number default hr_api.g_number,
57 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
58 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
59 p_parent_key_value2 in number default hr_api.g_number,
60 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
61 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
62 p_parent_key_value3 in number default hr_api.g_number,
63 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
64 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
65 p_parent_key_value4 in number default hr_api.g_number,
66 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
67 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
68 p_parent_key_value5 in number default hr_api.g_number,
69 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
70 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
71 p_parent_key_value6 in number default hr_api.g_number,
72 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
73 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
74 p_parent_key_value7 in number default hr_api.g_number,
75 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
76 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
77 p_parent_key_value8 in number default hr_api.g_number,
78 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
79 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
80 p_parent_key_value9 in number default hr_api.g_number,
81 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
82 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
83 p_parent_key_value10 in number default hr_api.g_number,
84 p_zap out NOCOPY boolean,
85 p_delete out NOCOPY boolean,
86 p_future_change out NOCOPY boolean,
87 p_delete_next_change out NOCOPY boolean);
88 -- ----------------------------------------------------------------------------
89 -- |----------------------------< Validate_DT_Mode >--------------------------|
90 -- ----------------------------------------------------------------------------
91 --
92 -- PUBLIC
93 --
94 -- Description: Validates and returns the validation start and end dates for
95 -- the DateTrack mode provided.
96 --
97 -- ----------------------------------------------------------------------------
98 Procedure Validate_DT_Mode
99 (p_datetrack_mode in varchar2,
100 p_effective_date in date,
101 p_base_table_name in varchar2,
102 p_base_key_column in varchar2,
103 p_base_key_value in number,
104 p_parent_table_name1 in varchar2 default hr_api.g_varchar2,
105 p_parent_key_column1 in varchar2 default hr_api.g_varchar2,
106 p_parent_key_value1 in number default hr_api.g_number,
107 p_parent_table_name2 in varchar2 default hr_api.g_varchar2,
108 p_parent_key_column2 in varchar2 default hr_api.g_varchar2,
109 p_parent_key_value2 in number default hr_api.g_number,
110 p_parent_table_name3 in varchar2 default hr_api.g_varchar2,
111 p_parent_key_column3 in varchar2 default hr_api.g_varchar2,
112 p_parent_key_value3 in number default hr_api.g_number,
113 p_parent_table_name4 in varchar2 default hr_api.g_varchar2,
114 p_parent_key_column4 in varchar2 default hr_api.g_varchar2,
115 p_parent_key_value4 in number default hr_api.g_number,
116 p_parent_table_name5 in varchar2 default hr_api.g_varchar2,
117 p_parent_key_column5 in varchar2 default hr_api.g_varchar2,
118 p_parent_key_value5 in number default hr_api.g_number,
119 p_parent_table_name6 in varchar2 default hr_api.g_varchar2,
120 p_parent_key_column6 in varchar2 default hr_api.g_varchar2,
121 p_parent_key_value6 in number default hr_api.g_number,
122 p_parent_table_name7 in varchar2 default hr_api.g_varchar2,
123 p_parent_key_column7 in varchar2 default hr_api.g_varchar2,
124 p_parent_key_value7 in number default hr_api.g_number,
125 p_parent_table_name8 in varchar2 default hr_api.g_varchar2,
126 p_parent_key_column8 in varchar2 default hr_api.g_varchar2,
127 p_parent_key_value8 in number default hr_api.g_number,
128 p_parent_table_name9 in varchar2 default hr_api.g_varchar2,
129 p_parent_key_column9 in varchar2 default hr_api.g_varchar2,
130 p_parent_key_value9 in number default hr_api.g_number,
131 p_parent_table_name10 in varchar2 default hr_api.g_varchar2,
132 p_parent_key_column10 in varchar2 default hr_api.g_varchar2,
133 p_parent_key_value10 in number default hr_api.g_number,
134 p_child_table_name1 in varchar2 default hr_api.g_varchar2,
135 p_child_key_column1 in varchar2 default hr_api.g_varchar2,
136 p_child_table_name2 in varchar2 default hr_api.g_varchar2,
137 p_child_key_column2 in varchar2 default hr_api.g_varchar2,
138 p_child_table_name3 in varchar2 default hr_api.g_varchar2,
139 p_child_key_column3 in varchar2 default hr_api.g_varchar2,
140 p_child_table_name4 in varchar2 default hr_api.g_varchar2,
141 p_child_key_column4 in varchar2 default hr_api.g_varchar2,
142 p_child_table_name5 in varchar2 default hr_api.g_varchar2,
143 p_child_key_column5 in varchar2 default hr_api.g_varchar2,
144 p_child_table_name6 in varchar2 default hr_api.g_varchar2,
145 p_child_key_column6 in varchar2 default hr_api.g_varchar2,
146 p_child_table_name7 in varchar2 default hr_api.g_varchar2,
147 p_child_key_column7 in varchar2 default hr_api.g_varchar2,
148 p_child_table_name8 in varchar2 default hr_api.g_varchar2,
149 p_child_key_column8 in varchar2 default hr_api.g_varchar2,
150 p_child_table_name9 in varchar2 default hr_api.g_varchar2,
151 p_child_key_column9 in varchar2 default hr_api.g_varchar2,
152 p_child_table_name10 in varchar2 default hr_api.g_varchar2,
153 p_child_key_column10 in varchar2 default hr_api.g_varchar2,
154 p_child_alt_base_key_column1 in varchar2 default null,
155 p_child_alt_base_key_column2 in varchar2 default null,
156 p_child_alt_base_key_column3 in varchar2 default null,
157 p_child_alt_base_key_column4 in varchar2 default null,
158 p_child_alt_base_key_column5 in varchar2 default null,
159 p_child_alt_base_key_column6 in varchar2 default null,
160 p_child_alt_base_key_column7 in varchar2 default null,
161 p_child_alt_base_key_column8 in varchar2 default null,
162 p_child_alt_base_key_column9 in varchar2 default null,
163 p_child_alt_base_key_column10 in varchar2 default null,
164 p_enforce_foreign_locking in boolean default true,
165 p_validation_start_date out NOCOPY date,
166 p_validation_end_date out NOCOPY date);
167 -- ----------------------------------------------------------------------------
168 -- |------------------------< Validate_DT_Upd_Mode >--------------------------|
169 -- ----------------------------------------------------------------------------
170 --
171 -- PUBLIC
172 --
173 -- Description: Function returns TRUE is update mode is valid.
174 --
175 -- ----------------------------------------------------------------------------
176 Function Validate_DT_Upd_Mode(p_datetrack_mode in varchar2) Return Boolean;
177 -- ----------------------------------------------------------------------------
178 -- |------------------------< Validate_DT_Del_Mode >--------------------------|
179 -- ----------------------------------------------------------------------------
180 --
181 -- PUBLIC
182 --
183 -- Description: Function returns TRUE is delete mode is valid.
184 --
185 -- ----------------------------------------------------------------------------
186 Function Validate_DT_Del_Mode(p_datetrack_mode in varchar2) Return Boolean;
187 -- ----------------------------------------------------------------------------
188 -- |------------------------< Validate_DT_Upd_Mode >--------------------------|
189 -- ----------------------------------------------------------------------------
190 --
191 -- PUBLIC
192 --
193 -- Description: Validates the datetrack update mode.
194 --
195 -- ----------------------------------------------------------------------------
196 Procedure Validate_DT_Upd_Mode(p_datetrack_mode in varchar2);
197 -- ----------------------------------------------------------------------------
198 -- |------------------------< Validate_DT_Del_Mode >--------------------------|
199 -- ----------------------------------------------------------------------------
200 --
201 -- PUBLIC
202 --
203 -- Description: Validates the datetrack delete mode.
204 --
205 -- ----------------------------------------------------------------------------
206 Procedure Validate_DT_Del_Mode(p_datetrack_mode in varchar2);
207 -- ----------------------------------------------------------------------------
208 -- |-----------------------< Get_Object_Version_Number >----------------------|
209 -- ----------------------------------------------------------------------------
210 --
211 -- PUBLIC
212 --
213 -- Description: Function will return the next object version number to be used
214 -- within datetrack for an insert or update dml operation. The
215 -- returned object version number will be determined by taking
216 -- the maximum object version number for the datetracked rows
217 -- and then incrementing by 1. All datetrack modes will call
218 -- this function except 'ZAP'.
219 --
220 -- ----------------------------------------------------------------------------
221 Function Get_Object_Version_Number
222 (p_base_table_name in varchar2,
223 p_base_key_column in varchar2,
224 p_base_key_value in number)
225 Return Number;
226 -- ----------------------------------------------------------------------------
227 --
228 -- PUBLIC
229 --
230 -- Description: Function returns a boolean value. TRUE will be set if
231 -- row exists for the specified table between the from and to
232 -- dates else FALSE will be returned.
233 --
234 -- ----------------------------------------------------------------------------
235 Function Rows_Exist
236 (p_base_table_name in varchar2,
237 p_base_key_column in varchar2,
238 p_base_key_value in number,
239 p_from_date in date,
240 p_to_date in date default hr_api.g_eot)
241 Return Boolean;
242 -- ----------------------------------------------------------------------------
243 --
244 -- PUBLIC
245 --
246 -- Description: This function is used to determine if datetrack rows exist
247 -- between the from and to date specified.
248 -- If the datetrack rows do exist for the duration then a TRUE
249 -- value will be returned else FALSE will be returned.
250 -- If the p_base_key_value is null then this function will assume
251 -- that an optional relationship is in force and will return TRUE
252 --
253 -- ----------------------------------------------------------------------------
254 Function Check_Min_Max_Dates
255 (p_base_table_name in varchar2,
256 p_base_key_column in varchar2,
257 p_base_key_value in number,
258 p_from_date in date,
259 p_to_date in date)
260 Return Boolean;
261 -- ----------------------------------------------------------------------------
262 --
263 -- PUBLIC
264 --
265 -- Description:
266 -- This procedure is used to determine which datetrack update modes are
267 -- allowed as of a specific date for particular entity and row. This
268 -- procedure will return a value 1 for each of the DateTrack
269 -- update modes. Also returns the validation date range for each of the
270 -- applicable datetrack mode.
271 --
272 -- ----------------------------------------------------------------------------
273 procedure Find_DT_Upd_Modes_And_Dates
274 (p_effective_date in date
275 ,p_base_table_name in varchar2
276 ,p_base_key_column in varchar2
277 ,p_base_key_value in number
278 ,p_correction out nocopy boolean
279 ,p_update out nocopy boolean
280 ,p_update_override out nocopy boolean
281 ,p_update_change_insert out nocopy boolean
282 ,p_correction_start_date out nocopy date
283 ,p_correction_end_date out nocopy date
284 ,p_update_start_date out nocopy date
285 ,p_update_end_date out nocopy date
286 ,p_override_start_date out nocopy date
287 ,p_override_end_date out nocopy date
288 ,p_upd_chg_start_date out nocopy date
289 ,p_upd_chg_end_date out nocopy date
290 );
291 -- ----------------------------------------------------------------------------
292 --
293 -- PUBLIC
294 --
295 -- Description:
296 -- This procedure is used to determine which datetrack delete modes are
297 -- allowed as of a specific date for particular entity and row. This
298 -- procedure will return a value 1 for each of the valid DateTrack
299 -- delete modes. Also returns the validation date range for each of the
300 -- applicable datetrack mode.
301 --
302 -- ----------------------------------------------------------------------------
303 procedure Find_DT_Del_Modes_And_Dates
304 (p_effective_date in date
305 ,p_base_table_name in varchar2
306 ,p_base_key_column in varchar2
307 ,p_base_key_value in number
308 ,p_parent_table_name1 in varchar2 default hr_api.g_varchar2
309 ,p_parent_key_column1 in varchar2 default hr_api.g_varchar2
310 ,p_parent_key_value1 in number default hr_api.g_number
311 ,p_parent_table_name2 in varchar2 default hr_api.g_varchar2
312 ,p_parent_key_column2 in varchar2 default hr_api.g_varchar2
313 ,p_parent_key_value2 in number default hr_api.g_number
314 ,p_parent_table_name3 in varchar2 default hr_api.g_varchar2
315 ,p_parent_key_column3 in varchar2 default hr_api.g_varchar2
316 ,p_parent_key_value3 in number default hr_api.g_number
317 ,p_parent_table_name4 in varchar2 default hr_api.g_varchar2
318 ,p_parent_key_column4 in varchar2 default hr_api.g_varchar2
319 ,p_parent_key_value4 in number default hr_api.g_number
320 ,p_parent_table_name5 in varchar2 default hr_api.g_varchar2
321 ,p_parent_key_column5 in varchar2 default hr_api.g_varchar2
322 ,p_parent_key_value5 in number default hr_api.g_number
323 ,p_parent_table_name6 in varchar2 default hr_api.g_varchar2
324 ,p_parent_key_column6 in varchar2 default hr_api.g_varchar2
325 ,p_parent_key_value6 in number default hr_api.g_number
326 ,p_parent_table_name7 in varchar2 default hr_api.g_varchar2
327 ,p_parent_key_column7 in varchar2 default hr_api.g_varchar2
328 ,p_parent_key_value7 in number default hr_api.g_number
329 ,p_parent_table_name8 in varchar2 default hr_api.g_varchar2
330 ,p_parent_key_column8 in varchar2 default hr_api.g_varchar2
331 ,p_parent_key_value8 in number default hr_api.g_number
332 ,p_parent_table_name9 in varchar2 default hr_api.g_varchar2
333 ,p_parent_key_column9 in varchar2 default hr_api.g_varchar2
334 ,p_parent_key_value9 in number default hr_api.g_number
335 ,p_parent_table_name10 in varchar2 default hr_api.g_varchar2
336 ,p_parent_key_column10 in varchar2 default hr_api.g_varchar2
337 ,p_parent_key_value10 in number default hr_api.g_number
338 ,p_zap out nocopy boolean
339 ,p_delete out nocopy boolean
340 ,p_future_change out nocopy boolean
341 ,p_delete_next_change out nocopy boolean
342 ,p_zap_start_date out nocopy date
343 ,p_zap_end_date out nocopy date
344 ,p_delete_start_date out nocopy date
345 ,p_delete_end_date out nocopy date
346 ,p_del_future_start_date out nocopy date
347 ,p_del_future_end_date out nocopy date
348 ,p_del_next_start_date out nocopy date
349 ,p_del_next_end_date out nocopy date
350 );
351
352
353 End DT_Api;