1 PACKAGE igs_ad_interface_pkg AS
2 /* $Header: IGSAIB3S.pls 120.1 2005/09/22 23:52:16 appldev ship $ */
3
4
5 PROCEDURE set_column_values (
6 p_action IN VARCHAR2,
7 x_rowid IN VARCHAR2 DEFAULT NULL,
8 x_person_number IN VARCHAR2 DEFAULT NULL,
9 x_org_id IN NUMBER DEFAULT NULL,
10 x_interface_id IN NUMBER DEFAULT NULL,
11 x_batch_id IN NUMBER DEFAULT NULL,
12 x_source_type_id IN NUMBER DEFAULT NULL,
13 x_surname IN VARCHAR2 DEFAULT NULL,
14 x_middle_name IN VARCHAR2 DEFAULT NULL,
15 x_given_names IN VARCHAR2 DEFAULT NULL,
16 x_preferred_given_name IN VARCHAR2 DEFAULT NULL,
17 x_sex IN VARCHAR2 DEFAULT NULL,
18 x_birth_dt IN DATE DEFAULT NULL,
19 x_title IN VARCHAR2 DEFAULT NULL,
20 x_suffix IN VARCHAR2 DEFAULT NULL,
21 x_pre_name_adjunct IN VARCHAR2 DEFAULT NULL,
22 x_level_of_qual IN NUMBER DEFAULT NULL,
23 x_proof_of_insurance IN VARCHAR2 DEFAULT NULL,
24 x_proof_of_immun IN VARCHAR2 DEFAULT NULL,
25 x_pref_alternate_id IN VARCHAR2 DEFAULT NULL,
26 x_person_id IN NUMBER DEFAULT NULL,
27 x_status IN VARCHAR2 DEFAULT NULL,
28 x_military_service_reg IN VARCHAR2 DEFAULT NULL,
29 x_veteran IN VARCHAR2 DEFAULT NULL,
30 x_match_ind IN VARCHAR2 DEFAULT NULL,
31 x_person_match_ind IN VARCHAR2 DEFAULT NULL,
32 x_error_code IN VARCHAR2 DEFAULT NULL,
33 x_record_status IN VARCHAR2 DEFAULT NULL,
34 x_interface_run_id IN NUMBER DEFAULT NULL,
35 x_attribute_category IN VARCHAR2 DEFAULT NULL,
36 x_attribute1 IN VARCHAR2 DEFAULT NULL,
37 x_attribute2 IN VARCHAR2 DEFAULT NULL,
38 x_attribute3 IN VARCHAR2 DEFAULT NULL,
39 x_attribute4 IN VARCHAR2 DEFAULT NULL,
40 x_attribute5 IN VARCHAR2 DEFAULT NULL,
41 x_attribute6 IN VARCHAR2 DEFAULT NULL,
42 x_attribute7 IN VARCHAR2 DEFAULT NULL,
43 x_attribute8 IN VARCHAR2 DEFAULT NULL,
44 x_attribute9 IN VARCHAR2 DEFAULT NULL,
45 x_attribute10 IN VARCHAR2 DEFAULT NULL,
46 x_attribute11 IN VARCHAR2 DEFAULT NULL,
47 x_attribute12 IN VARCHAR2 DEFAULT NULL,
48 x_attribute13 IN VARCHAR2 DEFAULT NULL,
49 x_attribute14 IN VARCHAR2 DEFAULT NULL,
50 x_attribute15 IN VARCHAR2 DEFAULT NULL,
51 x_attribute16 IN VARCHAR2 DEFAULT NULL,
52 x_attribute17 IN VARCHAR2 DEFAULT NULL,
53 x_attribute18 IN VARCHAR2 DEFAULT NULL,
54 x_attribute19 IN VARCHAR2 DEFAULT NULL,
55 x_attribute20 IN VARCHAR2 DEFAULT NULL,
56 x_creation_date IN DATE DEFAULT NULL,
57 x_created_by IN NUMBER DEFAULT NULL,
58 x_last_update_date IN DATE DEFAULT NULL,
59 x_last_updated_by IN NUMBER DEFAULT NULL,
60 x_last_update_login IN NUMBER DEFAULT NULL,
61 x_person_type_code IN VARCHAR2 DEFAULT NULL,
62 x_funnel_status IN VARCHAR2 DEFAULT NULL,
63 x_birth_city IN VARCHAR2 DEFAULT NULL,
64 x_birth_country IN VARCHAR2 DEFAULT NULL,
65 x_attribute21 IN VARCHAR2 DEFAULT NULL,
66 x_attribute22 IN VARCHAR2 DEFAULT NULL,
67 x_attribute23 IN VARCHAR2 DEFAULT NULL,
68 x_attribute24 IN VARCHAR2 DEFAULT NULL
69 ) ;
70
71 PROCEDURE Check_Constraints (
72 Column_Name IN VARCHAR2 DEFAULT NULL,
73 Column_Value IN VARCHAR2 DEFAULT NULL );
74
75 PROCEDURE Check_Child_Existance;
76
77 FUNCTION Get_PK_For_Validation (
78 x_interface_id IN NUMBER
79 ) RETURN BOOLEAN ;
80
81
82
83 PROCEDURE insert_row (
84 x_rowid IN OUT NOCOPY VARCHAR2,
85 x_person_number IN VARCHAR2,
86 x_org_id IN NUMBER DEFAULT NULL,
87 x_interface_id IN NUMBER,
88 x_batch_id IN NUMBER,
89 x_source_type_id IN NUMBER,
90 x_surname IN VARCHAR2,
91 x_middle_name IN VARCHAR2,
92 x_given_names IN VARCHAR2,
93 x_preferred_given_name IN VARCHAR2,
94 x_sex IN VARCHAR2,
95 x_birth_dt IN DATE,
96 x_title IN VARCHAR2,
97 x_suffix IN VARCHAR2,
98 x_pre_name_adjunct IN VARCHAR2,
99 x_level_of_qual IN NUMBER,
100 x_proof_of_insurance IN VARCHAR2,
101 x_proof_of_immun IN VARCHAR2,
102 x_pref_alternate_id IN VARCHAR2,
103 x_person_id IN NUMBER,
104 x_status IN VARCHAR2,
105 x_military_service_reg IN VARCHAR2,
106 x_veteran IN VARCHAR2,
107 x_match_ind IN VARCHAR2,
108 x_person_match_ind IN VARCHAR2,
109 x_error_code IN VARCHAR2,
110 x_record_status IN VARCHAR2,
111 x_interface_run_id IN NUMBER,
112 x_attribute_category IN VARCHAR2,
113 x_attribute1 IN VARCHAR2,
114 x_attribute2 IN VARCHAR2,
115 x_attribute3 IN VARCHAR2,
116 x_attribute4 IN VARCHAR2,
117 x_attribute5 IN VARCHAR2,
118 x_attribute6 IN VARCHAR2,
119 x_attribute7 IN VARCHAR2,
120 x_attribute8 IN VARCHAR2,
121 x_attribute9 IN VARCHAR2,
122 x_attribute10 IN VARCHAR2,
123 x_attribute11 IN VARCHAR2,
124 x_attribute12 IN VARCHAR2,
125 x_attribute13 IN VARCHAR2,
126 x_attribute14 IN VARCHAR2,
127 x_attribute15 IN VARCHAR2,
128 x_attribute16 IN VARCHAR2,
129 x_attribute17 IN VARCHAR2,
130 x_attribute18 IN VARCHAR2,
131 x_attribute19 IN VARCHAR2,
132 x_attribute20 IN VARCHAR2,
133 x_mode IN VARCHAR2 DEFAULT NULL,
134 x_person_type_code IN VARCHAR2 DEFAULT NULL,
135 x_funnel_status IN VARCHAR2 DEFAULT NULL,
136 x_birth_city IN VARCHAR2 DEFAULT NULL,
137 x_birth_country IN VARCHAR2 DEFAULT NULL,
138 x_attribute21 IN VARCHAR2 DEFAULT NULL,
139 x_attribute22 IN VARCHAR2 DEFAULT NULL,
140 x_attribute23 IN VARCHAR2 DEFAULT NULL,
141 x_attribute24 IN VARCHAR2 DEFAULT NULL
142 );
143
144 PROCEDURE lock_row (
145 x_rowid IN VARCHAR2,
146 x_person_number IN VARCHAR2,
147 x_org_id IN NUMBER DEFAULT NULL,
148 x_interface_id IN NUMBER,
149 x_batch_id IN NUMBER,
150 x_source_type_id IN NUMBER,
151 x_surname IN VARCHAR2,
152 x_middle_name IN VARCHAR2,
153 x_given_names IN VARCHAR2,
154 x_preferred_given_name IN VARCHAR2,
155 x_sex IN VARCHAR2,
156 x_birth_dt IN DATE,
157 x_title IN VARCHAR2,
158 x_suffix IN VARCHAR2,
159 x_pre_name_adjunct IN VARCHAR2,
160 x_level_of_qual IN NUMBER,
161 x_proof_of_insurance IN VARCHAR2,
162 x_proof_of_immun IN VARCHAR2,
163 x_pref_alternate_id IN VARCHAR2,
164 x_person_id IN NUMBER,
165 x_status IN VARCHAR2,
166 x_military_service_reg IN VARCHAR2,
167 x_veteran IN VARCHAR2,
168 x_match_ind IN VARCHAR2,
169 x_person_match_ind IN VARCHAR2,
170 x_error_code IN VARCHAR2,
171 x_record_status IN VARCHAR2,
172 x_interface_run_id IN NUMBER,
173 x_attribute_category IN VARCHAR2,
174 x_attribute1 IN VARCHAR2,
175 x_attribute2 IN VARCHAR2,
176 x_attribute3 IN VARCHAR2,
177 x_attribute4 IN VARCHAR2,
178 x_attribute5 IN VARCHAR2,
179 x_attribute6 IN VARCHAR2,
180 x_attribute7 IN VARCHAR2,
181 x_attribute8 IN VARCHAR2,
182 x_attribute9 IN VARCHAR2,
183 x_attribute10 IN VARCHAR2,
184 x_attribute11 IN VARCHAR2,
185 x_attribute12 IN VARCHAR2,
186 x_attribute13 IN VARCHAR2,
187 x_attribute14 IN VARCHAR2,
188 x_attribute15 IN VARCHAR2,
189 x_attribute16 IN VARCHAR2,
190 x_attribute17 IN VARCHAR2,
191 x_attribute18 IN VARCHAR2,
192 x_attribute19 IN VARCHAR2,
193 x_attribute20 IN VARCHAR2,
194 x_person_type_code IN VARCHAR2 DEFAULT NULL,
195 x_funnel_status IN VARCHAR2 DEFAULT NULL,
196 x_birth_city IN VARCHAR2 DEFAULT NULL,
197 x_birth_country IN VARCHAR2 DEFAULT NULL,
198 x_attribute21 IN VARCHAR2 DEFAULT NULL,
199 x_attribute22 IN VARCHAR2 DEFAULT NULL,
200 x_attribute23 IN VARCHAR2 DEFAULT NULL,
201 x_attribute24 IN VARCHAR2 DEFAULT NULL
202 );
203
204 PROCEDURE update_row (
205 x_rowid IN VARCHAR2,
206 x_person_number IN VARCHAR2,
207 x_org_id IN NUMBER DEFAULT NULL,
208 x_interface_id IN NUMBER,
209 x_batch_id IN NUMBER,
210 x_source_type_id IN NUMBER,
211 x_surname IN VARCHAR2,
212 x_middle_name IN VARCHAR2,
213 x_given_names IN VARCHAR2,
214 x_preferred_given_name IN VARCHAR2,
215 x_sex IN VARCHAR2,
216 x_birth_dt IN DATE,
217 x_title IN VARCHAR2,
218 x_suffix IN VARCHAR2,
219 x_pre_name_adjunct IN VARCHAR2,
220 x_level_of_qual IN NUMBER,
221 x_proof_of_insurance IN VARCHAR2,
222 x_proof_of_immun IN VARCHAR2,
223 x_pref_alternate_id IN VARCHAR2,
224 x_person_id IN NUMBER,
225 x_status IN VARCHAR2,
226 x_military_service_reg IN VARCHAR2,
227 x_veteran IN VARCHAR2,
228 x_match_ind IN VARCHAR2,
229 x_person_match_ind IN VARCHAR2,
230 x_error_code IN VARCHAR2,
231 x_record_status IN VARCHAR2,
232 x_interface_run_id IN NUMBER,
233 x_attribute_category IN VARCHAR2,
234 x_attribute1 IN VARCHAR2,
235 x_attribute2 IN VARCHAR2,
236 x_attribute3 IN VARCHAR2,
237 x_attribute4 IN VARCHAR2,
238 x_attribute5 IN VARCHAR2,
239 x_attribute6 IN VARCHAR2,
240 x_attribute7 IN VARCHAR2,
241 x_attribute8 IN VARCHAR2,
242 x_attribute9 IN VARCHAR2,
243 x_attribute10 IN VARCHAR2,
244 x_attribute11 IN VARCHAR2,
245 x_attribute12 IN VARCHAR2,
246 x_attribute13 IN VARCHAR2,
247 x_attribute14 IN VARCHAR2,
248 x_attribute15 IN VARCHAR2,
249 x_attribute16 IN VARCHAR2,
250 x_attribute17 IN VARCHAR2,
251 x_attribute18 IN VARCHAR2,
252 x_attribute19 IN VARCHAR2,
253 x_attribute20 IN VARCHAR2,
254 x_mode IN VARCHAR2 DEFAULT NULL,
255 x_person_type_code IN VARCHAR2 DEFAULT NULL,
256 x_funnel_status IN VARCHAR2 DEFAULT NULL,
257 x_birth_city IN VARCHAR2 DEFAULT NULL,
258 x_birth_country IN VARCHAR2 DEFAULT NULL,
259 x_attribute21 IN VARCHAR2 DEFAULT NULL,
263 );
260 x_attribute22 IN VARCHAR2 DEFAULT NULL,
261 x_attribute23 IN VARCHAR2 DEFAULT NULL,
262 x_attribute24 IN VARCHAR2 DEFAULT NULL
264
265 PROCEDURE add_row (
266 x_rowid IN OUT NOCOPY VARCHAR2,
267 x_person_number IN VARCHAR2,
268 x_org_id IN NUMBER DEFAULT NULL,
269 x_interface_id IN NUMBER,
270 x_batch_id IN NUMBER,
271 x_source_type_id IN NUMBER,
272 x_surname IN VARCHAR2,
273 x_middle_name IN VARCHAR2,
274 x_given_names IN VARCHAR2,
275 x_preferred_given_name IN VARCHAR2,
276 x_sex IN VARCHAR2,
277 x_birth_dt IN DATE,
278 x_title IN VARCHAR2,
279 x_suffix IN VARCHAR2,
280 x_pre_name_adjunct IN VARCHAR2,
281 x_level_of_qual IN NUMBER,
282 x_proof_of_insurance IN VARCHAR2,
283 x_proof_of_immun IN VARCHAR2,
284 x_pref_alternate_id IN VARCHAR2,
285 x_person_id IN NUMBER,
286 x_status IN VARCHAR2,
287 x_military_service_reg IN VARCHAR2,
288 x_veteran IN VARCHAR2,
289 x_match_ind IN VARCHAR2,
290 x_person_match_ind IN VARCHAR2,
291 x_error_code IN VARCHAR2,
292 x_record_status IN VARCHAR2,
293 x_interface_run_id IN NUMBER,
294 x_attribute_category IN VARCHAR2,
295 x_attribute1 IN VARCHAR2,
296 x_attribute2 IN VARCHAR2,
297 x_attribute3 IN VARCHAR2,
298 x_attribute4 IN VARCHAR2,
299 x_attribute5 IN VARCHAR2,
300 x_attribute6 IN VARCHAR2,
301 x_attribute7 IN VARCHAR2,
302 x_attribute8 IN VARCHAR2,
303 x_attribute9 IN VARCHAR2,
304 x_attribute10 IN VARCHAR2,
305 x_attribute11 IN VARCHAR2,
306 x_attribute12 IN VARCHAR2,
307 x_attribute13 IN VARCHAR2,
308 x_attribute14 IN VARCHAR2,
309 x_attribute15 IN VARCHAR2,
310 x_attribute16 IN VARCHAR2,
311 x_attribute17 IN VARCHAR2,
312 x_attribute18 IN VARCHAR2,
313 x_attribute19 IN VARCHAR2,
314 x_attribute20 IN VARCHAR2,
315 x_mode IN VARCHAR2 DEFAULT NULL,
316 x_person_type_code IN VARCHAR2 DEFAULT NULL,
317 x_funnel_status IN VARCHAR2 DEFAULT NULL,
318 x_birth_city IN VARCHAR2 DEFAULT NULL,
319 x_birth_country IN VARCHAR2 DEFAULT NULL,
320 x_attribute21 IN VARCHAR2 DEFAULT NULL,
321 x_attribute22 IN VARCHAR2 DEFAULT NULL,
322 x_attribute23 IN VARCHAR2 DEFAULT NULL,
323 x_attribute24 IN VARCHAR2 DEFAULT NULL
324 );
325
326 PROCEDURE delete_row (
327 x_rowid IN VARCHAR2
328 );
329
330 PROCEDURE before_dml (
331 p_action IN VARCHAR2,
332 x_rowid IN VARCHAR2 DEFAULT NULL,
333 x_person_number IN VARCHAR2 DEFAULT NULL,
334 x_org_id IN NUMBER DEFAULT NULL,
335 x_interface_id IN NUMBER DEFAULT NULL,
336 x_batch_id IN NUMBER DEFAULT NULL,
337 x_source_type_id IN NUMBER DEFAULT NULL,
338 x_surname IN VARCHAR2 DEFAULT NULL,
339 x_middle_name IN VARCHAR2 DEFAULT NULL,
340 x_given_names IN VARCHAR2 DEFAULT NULL,
341 x_preferred_given_name IN VARCHAR2 DEFAULT NULL,
342 x_sex IN VARCHAR2 DEFAULT NULL,
343 x_birth_dt IN DATE DEFAULT NULL,
344 x_title IN VARCHAR2 DEFAULT NULL,
345 x_suffix IN VARCHAR2 DEFAULT NULL,
346 x_pre_name_adjunct IN VARCHAR2 DEFAULT NULL,
347 x_level_of_qual IN NUMBER DEFAULT NULL,
348 x_proof_of_insurance IN VARCHAR2 DEFAULT NULL,
349 x_proof_of_immun IN VARCHAR2 DEFAULT NULL,
350 x_pref_alternate_id IN VARCHAR2 DEFAULT NULL,
351 x_person_id IN NUMBER DEFAULT NULL,
352 x_status IN VARCHAR2 DEFAULT NULL,
356 x_person_match_ind IN VARCHAR2 DEFAULT NULL,
353 x_military_service_reg IN VARCHAR2 DEFAULT NULL,
354 x_veteran IN VARCHAR2 DEFAULT NULL,
355 x_match_ind IN VARCHAR2 DEFAULT NULL,
357 x_error_code IN VARCHAR2 DEFAULT NULL,
358 x_record_status IN VARCHAR2 DEFAULT NULL,
359 x_interface_run_id IN NUMBER DEFAULT NULL,
360 x_attribute_category IN VARCHAR2 DEFAULT NULL,
361 x_attribute1 IN VARCHAR2 DEFAULT NULL,
362 x_attribute2 IN VARCHAR2 DEFAULT NULL,
363 x_attribute3 IN VARCHAR2 DEFAULT NULL,
364 x_attribute4 IN VARCHAR2 DEFAULT NULL,
365 x_attribute5 IN VARCHAR2 DEFAULT NULL,
366 x_attribute6 IN VARCHAR2 DEFAULT NULL,
367 x_attribute7 IN VARCHAR2 DEFAULT NULL,
368 x_attribute8 IN VARCHAR2 DEFAULT NULL,
369 x_attribute9 IN VARCHAR2 DEFAULT NULL,
370 x_attribute10 IN VARCHAR2 DEFAULT NULL,
371 x_attribute11 IN VARCHAR2 DEFAULT NULL,
372 x_attribute12 IN VARCHAR2 DEFAULT NULL,
373 x_attribute13 IN VARCHAR2 DEFAULT NULL,
374 x_attribute14 IN VARCHAR2 DEFAULT NULL,
375 x_attribute15 IN VARCHAR2 DEFAULT NULL,
376 x_attribute16 IN VARCHAR2 DEFAULT NULL,
377 x_attribute17 IN VARCHAR2 DEFAULT NULL,
378 x_attribute18 IN VARCHAR2 DEFAULT NULL,
379 x_attribute19 IN VARCHAR2 DEFAULT NULL,
380 x_attribute20 IN VARCHAR2 DEFAULT NULL,
381 x_creation_date IN DATE DEFAULT NULL,
382 x_created_by IN NUMBER DEFAULT NULL,
383 x_last_update_date IN DATE DEFAULT NULL,
384 x_last_updated_by IN NUMBER DEFAULT NULL,
385 x_last_update_login IN NUMBER DEFAULT NULL,
386 x_person_type_code IN VARCHAR2 DEFAULT NULL,
387 x_funnel_status IN VARCHAR2 DEFAULT NULL,
388 x_birth_city IN VARCHAR2 DEFAULT NULL,
389 x_birth_country IN VARCHAR2 DEFAULT NULL,
390 x_attribute21 IN VARCHAR2 DEFAULT NULL,
391 x_attribute22 IN VARCHAR2 DEFAULT NULL,
392 x_attribute23 IN VARCHAR2 DEFAULT NULL,
393 x_attribute24 IN VARCHAR2 DEFAULT NULL
394 );
395
396 END igs_ad_interface_pkg;