[Home] [Help]
PACKAGE: APPS.IGS_PE_STAT_DETAILS_PKG
Source
1 PACKAGE igs_pe_stat_details_pkg AS
2 /* $Header: IGSNI72S.pls 120.1 2006/02/17 06:52:11 gmaheswa noship $ */
3
4 ------------------------------------------------------------------
5 -- Change History
6 -- npalanis 11-SEP-2002 bug - 2608360
7 -- igs_pe_code_classes is
8 -- removed due to transition of code
9 -- class to lookups , new columns added
10 -- for codes. the tbh are modified accordingly
11 --
12 -- Bug ID : 2000408
13 -- who when what
14 -- CDCRUZ Sep 24,2002 New Col added for
15 -- Person DLD
16 -- Columns Obsoleted - CRIMINAL_CONVICT/ACAD_DISMISSAL/NON_ACAD_DISMISSAL/COUNTRY_CD3
17 -- RES_STAT_ID/STATE_OF_RESIDENCE
18 -- Columns Added - MATR_CAL_TYPE/MATR_SEQUENCE_NUMBER/INIT_CAL_TYPE/INIT_SEQUENCE_NUMBER
19 -- RECENT_CAL_TYPE/RECENT_SEQUENCE_NUMBER/CATALOG_CAL_TYPE/CATALOG_SEQUENCE_NUMBER
20 -- Bayadav 31-Jan-2002 Bug number 2203778 .added descriptive flexfield columns (IGS_PE_PERS_STAT )
21 -- ssawhney Feb 5th, New col added person_id mandatory NOT NULL, will be a FK to HZ_PARTIES
22 -- person_profile_id obsoleted. 2203778
23 ------------------------------------------------------------------
24
25 PROCEDURE insert_row (
26 x_rowid IN OUT NOCOPY VARCHAR2,
27 x_person_id IN NUMBER,
28 -- x_person_profile_id IN NUMBER,
29 x_effective_start_date IN DATE,
30 x_effective_end_date IN DATE,
31 x_religion_cd IN VARCHAR2 DEFAULT NULL,
32 -- x_criminal_convict IN VARCHAR2 DEFAULT NULL,
33 -- x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
34 -- x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
35 -- x_country_cd3 IN VARCHAR2 DEFAULT NULL,
36 -- x_state_of_residence IN VARCHAR2 DEFAULT NULL,
37 -- x_resid_stat_id IN NUMBER DEFAULT NULL,
38 x_socio_eco_cd IN VARCHAR2 DEFAULT NULL,
39 x_next_to_kin IN VARCHAR2,
40 x_in_state_tuition IN VARCHAR2,
41 x_tuition_st_date IN DATE,
42 x_tuition_end_date IN DATE,
43 x_further_education_cd IN VARCHAR2 DEFAULT NULL,
44 X_MATR_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
45 X_MATR_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
46 X_INIT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
47 X_INIT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
48 X_RECENT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
49 X_RECENT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
50 X_CATALOG_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
51 X_CATALOG_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
52 x_mode IN VARCHAR2 DEFAULT 'R' ,
53 X_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
54 X_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
55 X_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
56 X_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
57 X_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
58 X_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
59 X_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
60 X_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
61 X_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
62 X_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
63 X_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
64 X_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
65 X_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
66 X_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
67 X_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
68 X_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
69 X_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
70 X_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
71 X_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
72 X_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
73 X_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
74 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 DEFAULT NULL
75 );
76
77 PROCEDURE lock_row (
78 x_rowid IN VARCHAR2,
79 x_person_id IN NUMBER,
80 -- x_person_profile_id IN NUMBER,
81 x_effective_start_date IN DATE,
82 x_effective_end_date IN DATE,
83 x_religion_cd IN VARCHAR2 DEFAULT NULL,
84 --x_criminal_convict IN VARCHAR2 DEFAULT NULL,
85 --x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
86 --x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
87 --x_country_cd3 IN VARCHAR2 DEFAULT NULL,
88 --x_state_of_residence IN VARCHAR2 DEFAULT NULL,
89 --x_resid_stat_id IN NUMBER DEFAULT NULL,
90 x_socio_eco_cd IN VARCHAR2 DEFAULT NULL,
91 x_next_to_kin IN VARCHAR2,
92 x_in_state_tuition IN VARCHAR2,
93 x_tuition_st_date IN DATE,
94 x_tuition_end_date IN DATE,
95 x_further_education_cd IN VARCHAR2 DEFAULT NULL,
96 X_MATR_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
97 X_MATR_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
98 X_INIT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
99 X_INIT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
100 X_RECENT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
101 X_RECENT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
102 X_CATALOG_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
103 X_CATALOG_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
104 X_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
105 X_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
106 X_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
107 X_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
108 X_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
109 X_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
110 X_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
111 X_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
112 X_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
113 X_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
114 X_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
115 X_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
116 X_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
117 X_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
118 X_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
119 X_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
120 X_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
121 X_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
122 X_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
123 X_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
124 X_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
125 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 DEFAULT NULL
126 );
127
128 PROCEDURE update_row (
129 x_rowid IN VARCHAR2,
130 x_person_id IN NUMBER,
131 -- x_person_profile_id IN NUMBER,
132 x_effective_start_date IN DATE,
133 x_effective_end_date IN DATE,
134 x_religion_cd IN VARCHAR2 DEFAULT NULL,
135 -- x_criminal_convict IN VARCHAR2 DEFAULT NULL,
136 -- x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
137 -- x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
138 -- x_country_cd3 IN VARCHAR2 DEFAULT NULL,
139 -- x_state_of_residence IN VARCHAR2 DEFAULT NULL,
140 -- x_resid_stat_id IN NUMBER DEFAULT NULL,
141 x_socio_eco_cd IN VARCHAR2 DEFAULT NULL,
142 x_next_to_kin IN VARCHAR2,
143 x_in_state_tuition IN VARCHAR2,
144 x_tuition_st_date IN DATE,
145 x_tuition_end_date IN DATE,
146 x_further_education_cd IN VARCHAR2 DEFAULT NULL,
147 X_MATR_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
148 X_MATR_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
149 X_INIT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
150 X_INIT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
151 X_RECENT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
152 X_RECENT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
153 X_CATALOG_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
154 X_CATALOG_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
155 x_mode IN VARCHAR2 DEFAULT 'R' ,
156 X_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
157 X_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
158 X_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
159 X_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
160 X_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
161 X_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
162 X_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
163 X_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
164 X_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
165 X_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
166 X_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
167 X_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
168 X_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
169 X_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
170 X_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
171 X_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
172 X_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
173 X_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
174 X_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
175 X_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
176 X_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
177 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 DEFAULT NULL
178 );
179
180 PROCEDURE add_row (
181 x_rowid IN OUT NOCOPY VARCHAR2,
182 x_person_id IN NUMBER,
183 -- x_person_profile_id IN NUMBER,
184 x_effective_start_date IN DATE,
185 x_effective_end_date IN DATE,
186 x_religion_cd IN VARCHAR2 DEFAULT NULL,
187 -- x_criminal_convict IN VARCHAR2 DEFAULT NULL,
188 -- x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
189 -- x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
190 -- x_country_cd3 IN VARCHAR2 DEFAULT NULL,
191 -- x_state_of_residence IN VARCHAR2 DEFAULT NULL,
192 -- x_resid_stat_id IN NUMBER DEFAULT NULL,
193 x_socio_eco_cd IN VARCHAR2 DEFAULT NULL,
194 x_next_to_kin IN VARCHAR2,
195 x_in_state_tuition IN VARCHAR2,
196 x_tuition_st_date IN DATE,
197 x_tuition_end_date IN DATE,
198 x_further_education_cd IN VARCHAR2 DEFAULT NULL,
199 X_MATR_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
200 X_MATR_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
201 X_INIT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
202 X_INIT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
203 X_RECENT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
204 X_RECENT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
205 X_CATALOG_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
206 X_CATALOG_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
207 x_mode IN VARCHAR2 DEFAULT 'R' ,
208 X_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
209 X_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
210 X_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
211 X_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
212 X_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
213 X_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
214 X_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
215 X_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
216 X_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
217 X_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
218 X_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
219 X_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
220 X_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
221 X_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
222 X_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
223 X_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
224 X_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
225 X_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
226 X_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
227 X_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
228 X_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
229 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 DEFAULT NULL
230 );
231
232 PROCEDURE delete_row (
233 x_rowid IN VARCHAR2,
234 x_mode IN VARCHAR2 DEFAULT 'R'
235 );
236
237 /* FUNCTION get_pk_for_validation (
238 x_person_profile_id IN NUMBER
239 ) RETURN BOOLEAN;
240 */
241
242 PROCEDURE GET_FK_IGS_CA_INST (
243 x_cal_type IN VARCHAR2,
244 x_ci_sequence_number NUMBER
245 );
246
247 PROCEDURE before_dml (
248 p_action IN VARCHAR2,
249 x_rowid IN VARCHAR2 DEFAULT NULL,
250 x_person_id IN NUMBER DEFAULT NULL,
251 -- x_person_profile_id IN NUMBER DEFAULT NULL,
252 x_effective_start_date IN DATE DEFAULT NULL,
253 x_effective_end_date IN DATE DEFAULT NULL,
254 x_religion_cd IN VARCHAR2 DEFAULT NULL,
255 -- x_criminal_convict IN VARCHAR2 DEFAULT NULL,
256 -- x_acad_dismissal IN VARCHAR2 DEFAULT NULL,
257 -- x_non_acad_dismissal IN VARCHAR2 DEFAULT NULL,
258 -- x_country_cd3 IN VARCHAR2 DEFAULT NULL,
259 -- x_state_of_residence IN VARCHAR2 DEFAULT NULL,
260 -- x_resid_stat_id IN NUMBER DEFAULT NULL,
261 x_socio_eco_cd IN VARCHAR2 DEFAULT NULL,
262 x_next_to_kin IN VARCHAR2 DEFAULT NULL,
263 x_in_state_tuition IN VARCHAR2 DEFAULT NULL,
264 x_tuition_st_date IN DATE DEFAULT NULL,
265 x_tuition_end_date IN DATE DEFAULT NULL,
266 x_further_education_cd IN VARCHAR2 DEFAULT NULL,
267 X_MATR_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
268 X_MATR_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
269 X_INIT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
270 X_INIT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
271 X_RECENT_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
272 X_RECENT_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
273 X_CATALOG_CAL_TYPE IN VARCHAR2 DEFAULT NULL ,
274 X_CATALOG_SEQUENCE_NUMBER IN NUMBER DEFAULT NULL,
275 x_creation_date IN DATE DEFAULT NULL,
276 x_created_by IN NUMBER DEFAULT NULL,
277 x_last_update_date IN DATE DEFAULT NULL,
278 x_last_updated_by IN NUMBER DEFAULT NULL,
279 x_last_update_login IN NUMBER DEFAULT NULL ,
280 X_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
281 X_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
282 X_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
283 X_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
284 X_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
285 X_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
286 X_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
287 X_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
288 X_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
289 X_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
290 X_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
291 X_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
292 X_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
293 X_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
294 X_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
295 X_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
296 X_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
297 X_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
298 X_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
299 X_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
300 X_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
301 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 DEFAULT NULL
302 );
303
304 END igs_pe_stat_details_pkg;