1 PACKAGE igs_pe_stat_pkg AS
2 /* $Header: IGSNI48S.pls 120.1 2006/02/17 06:52:36 gmaheswa noship $ */
3 ------------------------------------------------------------------
4 -- Change History
5 --
6 -- Bug ID : 2000408
7 -- who when what
8 -- npalanis 11-SEP-2002 bug - 2608360
9 -- the pe code classes for religion , soci eco status and further education
10 -- has been transferred to lookups so new columns are added in igs_pe_stat_details table
11 -- to store codes therefore the tbh is modified.
12 -- CDCRUZ Sep 24,2002 New Col's added for
13 -- Person DLD
14 -- Columns Added - MATR_CAL_TYPE/MATR_SEQUENCE_NUMBER/INIT_CAL_TYPE/INIT_SEQUENCE_NUMBER
15 -- RECENT_CAL_TYPE/RECENT_SEQUENCE_NUMBER/CATALOG_CAL_TYPE/CATALOG_SEQUENCE_NUMBER
16 --
17 ------------------------------------------------------------------
18
19 procedure INSERT_ROW (
20 X_action IN VARCHAR2 ,
21 X_ROWID IN OUT NOCOPY VARCHAR2,
22 X_PERSON_ID IN NUMBER ,
23 X_ETHNIC_ORIGIN_ID IN VARCHAR2 ,
24 X_MARITAL_STATUS IN VARCHAR2 ,
25 X_MARITAL_STAT_EFFECT_DT IN DATE ,
26 X_ANN_FAMILY_INCOME IN NUMBER ,
27 X_NUMBER_IN_FAMILY IN NUMBER ,
28 X_CONTENT_SOURCE_TYPE IN VARCHAR2 ,
29 X_INTERNAL_FLAG IN VARCHAR2 ,
30 X_PERSON_NUMBER IN VARCHAR2 ,
31 X_EFFECTIVE_START_DATE IN DATE ,
32 X_effective_end_date IN DATE ,
33 X_ethnic_origin IN VARCHAR2 ,
34 X_religion IN VARCHAR2 ,
35 X_next_to_kin IN VARCHAR2 ,
36 X_next_to_kin_meaning IN VARCHAR2 ,
37 X_place_of_birth IN VARCHAR2 ,
38 X_socio_eco_status IN VARCHAR2 ,
39 X_socio_eco_status_desc IN VARCHAR2 ,
40 X_further_education IN VARCHAR2 ,
41 X_further_education_desc IN VARCHAR2 ,
42 X_in_state_tuition IN VARCHAR2 ,
43 X_tuition_st_Date IN DATE ,
44 X_tuition_end_date IN DATE ,
45 X_person_initials IN VARCHAR2,
46 X_primary_contact_id IN NUMBER ,
47 X_personal_income IN NUMBER ,
48 X_head_of_household_flag IN VARCHAR2 ,
49 X_content_source_number IN VARCHAR2 ,
50 x_hz_parties_ovn IN OUT NOCOPY NUMBER,
51 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
52 X_ATTRIBUTE1 IN VARCHAR2 ,
53 X_ATTRIBUTE2 IN VARCHAR2 ,
54 X_ATTRIBUTE3 IN VARCHAR2 ,
55 X_ATTRIBUTE4 IN VARCHAR2 ,
56 X_ATTRIBUTE5 IN VARCHAR2 ,
57 X_ATTRIBUTE6 IN VARCHAR2 ,
58 X_ATTRIBUTE7 IN VARCHAR2 ,
59 X_ATTRIBUTE8 IN VARCHAR2 ,
60 X_ATTRIBUTE9 IN VARCHAR2 ,
61 X_ATTRIBUTE10 IN VARCHAR2 ,
62 X_ATTRIBUTE11 IN VARCHAR2 ,
63 X_ATTRIBUTE12 IN VARCHAR2 ,
64 X_ATTRIBUTE13 IN VARCHAR2 ,
65 X_ATTRIBUTE14 IN VARCHAR2 ,
66 X_ATTRIBUTE15 IN VARCHAR2 ,
67 X_ATTRIBUTE16 IN VARCHAR2 ,
68 X_ATTRIBUTE17 IN VARCHAR2 ,
69 X_ATTRIBUTE18 IN VARCHAR2 ,
70 X_ATTRIBUTE19 IN VARCHAR2 ,
71 X_ATTRIBUTE20 IN VARCHAR2 ,
72 X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
73 X_GLOBAL_ATTRIBUTE1 IN VARCHAR2 ,
74 X_GLOBAL_ATTRIBUTE2 IN VARCHAR2 ,
75 X_GLOBAL_ATTRIBUTE3 IN VARCHAR2 ,
76 X_GLOBAL_ATTRIBUTE4 IN VARCHAR2 ,
77 X_GLOBAL_ATTRIBUTE5 IN VARCHAR2 ,
78 X_GLOBAL_ATTRIBUTE6 IN VARCHAR2 ,
79 X_GLOBAL_ATTRIBUTE7 IN VARCHAR2 ,
80 X_GLOBAL_ATTRIBUTE8 IN VARCHAR2 ,
81 X_GLOBAL_ATTRIBUTE9 IN VARCHAR2 ,
82 X_GLOBAL_ATTRIBUTE10 IN VARCHAR2 ,
83 X_GLOBAL_ATTRIBUTE11 IN VARCHAR2 ,
84 X_GLOBAL_ATTRIBUTE12 IN VARCHAR2 ,
85 X_GLOBAL_ATTRIBUTE13 IN VARCHAR2 ,
86 X_GLOBAL_ATTRIBUTE14 IN VARCHAR2 ,
87 X_GLOBAL_ATTRIBUTE15 IN VARCHAR2 ,
88 X_GLOBAL_ATTRIBUTE16 IN VARCHAR2 ,
89 X_GLOBAL_ATTRIBUTE17 IN VARCHAR2 ,
90 X_GLOBAL_ATTRIBUTE18 IN VARCHAR2 ,
91 X_GLOBAL_ATTRIBUTE19 IN VARCHAR2 ,
92 X_GLOBAL_ATTRIBUTE20 IN VARCHAR2 ,
93 X_PARTY_LAST_UPDATE_DATE IN OUT NOCOPY DATE ,
94 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER ,
95 X_MATR_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
96 X_MATR_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
97 X_INIT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
98 X_INIT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
99 X_RECENT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
100 X_RECENT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
101 X_CATALOG_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
102 X_CATALOG_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
103 Z_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
104 Z_MSG_COUNT OUT NOCOPY NUMBER ,
105 Z_MSG_DATA OUT NOCOPY VARCHAR2,
106 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 DEFAULT NULL
107
108
109 );
110
111
112 procedure UPDATE_ROW (
113 X_action IN VARCHAR2 ,
114 X_ROWID IN VARCHAR2 ,
115 X_PERSON_ID IN NUMBER ,
116 X_ETHNIC_ORIGIN_ID IN VARCHAR2 ,
117 X_MARITAL_STATUS IN VARCHAR2 ,
118 X_MARITAL_STAT_EFFECT_DT IN DATE ,
119 X_ANN_FAMILY_INCOME IN NUMBER ,
120 X_NUMBER_IN_FAMILY IN NUMBER ,
121 X_CONTENT_SOURCE_TYPE IN VARCHAR2 ,
122 X_INTERNAL_FLAG IN VARCHAR2 ,
123 X_PERSON_NUMBER IN VARCHAR2 ,
124 X_EFFECTIVE_START_DATE IN DATE ,
125 X_effective_end_date IN DATE ,
126 X_ethnic_origin IN VARCHAR2 ,
127 X_religion IN VARCHAR2 ,
128 X_next_to_kin IN VARCHAR2 ,
129 X_next_to_kin_meaning IN VARCHAR2 ,
130 X_place_of_birth IN VARCHAR2 ,
131 X_socio_eco_status IN VARCHAR2 ,
132 X_socio_eco_status_desc IN VARCHAR2 ,
133 X_further_education IN VARCHAR2 ,
134 X_further_education_desc IN VARCHAR2 ,
135 X_in_state_tuition IN VARCHAR2 ,
136 X_tuition_st_Date IN DATE ,
137 X_tuition_end_date IN DATE ,
138 X_person_initials IN VARCHAR2,
139 X_primary_contact_id IN NUMBER ,
140 X_personal_income IN NUMBER ,
141 X_head_of_household_flag IN VARCHAR2 ,
142 X_content_source_number IN VARCHAR2 ,
143 x_hz_parties_ovn IN OUT NOCOPY NUMBER,
144 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
145 X_ATTRIBUTE1 IN VARCHAR2 ,
146 X_ATTRIBUTE2 IN VARCHAR2 ,
147 X_ATTRIBUTE3 IN VARCHAR2 ,
148 X_ATTRIBUTE4 IN VARCHAR2 ,
149 X_ATTRIBUTE5 IN VARCHAR2 ,
150 X_ATTRIBUTE6 IN VARCHAR2 ,
151 X_ATTRIBUTE7 IN VARCHAR2 ,
152 X_ATTRIBUTE8 IN VARCHAR2 ,
153 X_ATTRIBUTE9 IN VARCHAR2 ,
154 X_ATTRIBUTE10 IN VARCHAR2 ,
155 X_ATTRIBUTE11 IN VARCHAR2 ,
156 X_ATTRIBUTE12 IN VARCHAR2 ,
157 X_ATTRIBUTE13 IN VARCHAR2 ,
158 X_ATTRIBUTE14 IN VARCHAR2 ,
159 X_ATTRIBUTE15 IN VARCHAR2 ,
160 X_ATTRIBUTE16 IN VARCHAR2 ,
161 X_ATTRIBUTE17 IN VARCHAR2 ,
162 X_ATTRIBUTE18 IN VARCHAR2 ,
163 X_ATTRIBUTE19 IN VARCHAR2 ,
164 X_ATTRIBUTE20 IN VARCHAR2 ,
165 X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
166 X_GLOBAL_ATTRIBUTE1 IN VARCHAR2 ,
167 X_GLOBAL_ATTRIBUTE2 IN VARCHAR2 ,
168 X_GLOBAL_ATTRIBUTE3 IN VARCHAR2 ,
169 X_GLOBAL_ATTRIBUTE4 IN VARCHAR2 ,
170 X_GLOBAL_ATTRIBUTE5 IN VARCHAR2 ,
171 X_GLOBAL_ATTRIBUTE6 IN VARCHAR2 ,
172 X_GLOBAL_ATTRIBUTE7 IN VARCHAR2 ,
173 X_GLOBAL_ATTRIBUTE8 IN VARCHAR2 ,
174 X_GLOBAL_ATTRIBUTE9 IN VARCHAR2 ,
175 X_GLOBAL_ATTRIBUTE10 IN VARCHAR2 ,
176 X_GLOBAL_ATTRIBUTE11 IN VARCHAR2 ,
177 X_GLOBAL_ATTRIBUTE12 IN VARCHAR2 ,
178 X_GLOBAL_ATTRIBUTE13 IN VARCHAR2 ,
179 X_GLOBAL_ATTRIBUTE14 IN VARCHAR2 ,
180 X_GLOBAL_ATTRIBUTE15 IN VARCHAR2 ,
181 X_GLOBAL_ATTRIBUTE16 IN VARCHAR2 ,
182 X_GLOBAL_ATTRIBUTE17 IN VARCHAR2 ,
183 X_GLOBAL_ATTRIBUTE18 IN VARCHAR2 ,
184 X_GLOBAL_ATTRIBUTE19 IN VARCHAR2 ,
185 X_GLOBAL_ATTRIBUTE20 IN VARCHAR2 ,
186 X_PARTY_LAST_UPDATE_DATE IN OUT NOCOPY DATE ,
187 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER ,
188 X_MATR_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
189 X_MATR_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
190 X_INIT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
191 X_INIT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
192 X_RECENT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
193 X_RECENT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
194 X_CATALOG_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
195 X_CATALOG_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
196 Z_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
197 Z_MSG_COUNT OUT NOCOPY NUMBER ,
198 Z_MSG_DATA OUT NOCOPY VARCHAR2,
199 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 DEFAULT NULL
200 );
201
202
203 PROCEDURE Before_DML (
204 X_action IN VARCHAR2 DEFAULT NULL,
205 X_ROWID IN OUT NOCOPY VARCHAR2 ,
206 X_PERSON_ID IN NUMBER DEFAULT NULL,
207 X_ETHNIC_ORIGIN_ID IN VARCHAR2 DEFAULT NULL,
208 X_MARITAL_STATUS IN VARCHAR2 DEFAULT NULL,
209 X_MARITAL_STAT_EFFECT_DT IN DATE DEFAULT NULL,
210 X_ANN_FAMILY_INCOME IN NUMBER DEFAULT NULL,
211 X_NUMBER_IN_FAMILY IN NUMBER DEFAULT NULL,
212 X_CONTENT_SOURCE_TYPE IN VARCHAR2 DEFAULT NULL,
213 X_INTERNAL_FLAG IN VARCHAR2 DEFAULT NULL,
214 X_PERSON_NUMBER IN VARCHAR2 DEFAULT NULL,
215 X_EFFECTIVE_START_DATE IN DATE DEFAULT NULL,
216 X_effective_end_date IN DATE DEFAULT NULL,
217 X_ethnic_origin IN VARCHAR2 DEFAULT NULL,
218 X_religion IN VARCHAR2 DEFAULT NULL,
219 X_next_to_kin IN VARCHAR2 DEFAULT NULL,
220 X_next_to_kin_meaning IN VARCHAR2 DEFAULT NULL,
221 X_place_of_birth IN VARCHAR2 DEFAULT NULL,
222 X_socio_eco_status IN VARCHAR2 DEFAULT NULL,
223 X_socio_eco_status_desc IN VARCHAR2 DEFAULT NULL,
224 X_further_education IN VARCHAR2 DEFAULT NULL,
225 X_further_education_desc IN VARCHAR2 DEFAULT NULL,
226 X_in_state_tuition IN VARCHAR2 DEFAULT NULL,
227 X_tuition_st_Date IN DATE DEFAULT NULL,
228 X_tuition_end_date IN DATE DEFAULT NULL,
229 X_person_initials IN VARCHAR2 DEFAULT NULL,
230 X_primary_contact_id IN NUMBER DEFAULT NULL,
231 X_personal_income IN NUMBER DEFAULT NULL,
232 X_head_of_household_flag IN VARCHAR2 DEFAULT NULL,
233 X_content_source_number IN VARCHAR2 DEFAULT NULL,
234 x_hz_parties_ovn IN NUMBER DEFAULT NULL,
235 X_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
236 X_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
237 X_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
238 X_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
239 X_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
240 X_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
241 X_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
242 X_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
243 X_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
244 X_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
245 X_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
246 X_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
247 X_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
248 X_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
249 X_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
250 X_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
251 X_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
252 X_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
253 X_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
254 X_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
255 X_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
256 X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
257 X_GLOBAL_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
258 X_GLOBAL_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
259 X_GLOBAL_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
260 X_GLOBAL_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
261 X_GLOBAL_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
262 X_GLOBAL_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
263 X_GLOBAL_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
264 X_GLOBAL_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
265 X_GLOBAL_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
266 X_GLOBAL_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
267 X_GLOBAL_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
268 X_GLOBAL_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
269 X_GLOBAL_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
270 X_GLOBAL_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
271 X_GLOBAL_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
272 X_GLOBAL_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
273 X_GLOBAL_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
274 X_GLOBAL_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
275 X_GLOBAL_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
276 X_GLOBAL_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
277 X_MATR_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
278 X_MATR_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
279 X_INIT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
280 X_INIT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
281 X_RECENT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
282 X_RECENT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
283 X_CATALOG_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
284 X_CATALOG_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
285 X_PARTY_LAST_UPDATE_DATE IN OUT NOCOPY DATE ,
286 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER,
287 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 DEFAULT NULL
288
289 );
290
291 END igs_pe_stat_pkg;