[Home] [Help]
PACKAGE BODY: APPS.IGS_OR_INST_IMP_002
Source
1 PACKAGE BODY IGS_OR_INST_IMP_002 AS
2 /* $Header: IGSOR15B.pls 120.2 2005/09/28 00:21:48 appldev ship $ */
3 /* Change History
4 | who when what
5
6 | npalanis 15-feb-2002 Bug ID -2225917 : SWCR008 IGS_OR_GEN_012_PKG.CREATE_ACCOUNT call
7 | is removed .
8 | KUMMA 15-jul-2002 Bug 2446067: in call to CREATE_ORGANIZATION and UPDATE_ORGANIZATION ,
9 | converted the NEW_INSTITUTION_CD to upper.
10 | In call to IGS_PE_HZ_PARTIES_PKG.INSERT_ROW if the local_ind and os_ind are null
11 | then put the default value of 'N'. In call to IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW if
12 | local_ind and os_ind are null then substitute them with database older values
13 | OI_LOCAL_INSTITUTION_IND and OI_OS_IND
14 | pkpatel 31-JUL-2002 Bug No: 2461744
15 | Removed the UPPER check for INSTITUTION_CD
16 | pkpatel 25-OCT-2002 Bug No: 2613704
17 | Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
18 | npalanis 27-OCT-2002 Bug No: 2613704
19 | Modified create_alternate_id
20 | pkpatel 2-DEC-2002 Bug No: 2599109
21 | Added column birth_city, birth_country in the call to TBH igs_pe_hz_parties_pkg
22 | ssawhney 30-APR-2003 V2API - OVN implementation , create/update_institution procs modified
23 | vrathi 28-MAY-2003 Bug No: 2961982 Replaced update_row with add_row
24 | ssaleem 25-SEP-2003 IGS.L patch the following changes are made
25 | 1. Logging mechanism introduced, FND_FILE.PUT_LINE replaced with methods
26 | in FND_LOG package
27 | 2. Cursors that used variables in the SELECT statements were replaced with
28 | cursor parameters. Respective changes have been made in the places where
29 | the cursors are opened
30 | 3. In the import process, it is made sure that NULL values does not replace
31 | existing values in the table. NULL check has been added while calling
32 | IGS_OR_GEN_012_PKG.UPDATE_ORGANIZATION and igs_pe_hz_parties_pkg.ADD_row
33 | mmkumar 18-Jul-2005 modified calls to igs_pe_hz_parties insert_row and add row
34 */
35
36 PROCEDURE create_institution (
37 p_inst_rec IN IGS_OR_INST_INT%ROWTYPE,
38 p_instcode OUT NOCOPY VARCHAR2,
39 p_errind OUT NOCOPY VARCHAR2,
40 p_error_code OUT NOCOPY VARCHAR2,
41 p_error_text OUT NOCOPY VARCHAR2)
42
43 AS
44 /*************************************************************
45 Created By :samaresh
46 Date Created By : 17-JUL-2001
47 Purpose : This Procedure creates a New Institution
48 Know limitations, enhancements or remarks
49 Change History
50 Who When What
51 SMVK 05-Feb-2002 Added Fund Authorization to
52 IGS_PE_HZ_PARTIES_PKG.INSERT_ROW calls
53 as per enhancement bug no.2191470.
54 kumma 14-JUN-2002 Uncommented the call to message IGS_OR_INST_CRT_FAIL,
55 IGS_OR_AUTOGEN_FAIL, 2410165
56 kumma 26-JUN-2002 Passed NULL for values INSITUTION_CD AND OU_START_DT
57 Inside call to IGS_PE_HZ_PARTIES_PKG.INSERT_ROW AND UPDATE_ROW, Bug 2425349
58 kumma 28-jun-2002 set the error indicator to 'Y' inside exception handling code
59 kumma 15-JUL-2002 In call to CREATE_ORGANIZATION converted the NEW_INSTITUTION_CD to upper.
60 In call to IGS_PE_HZ_PARTIES_PKG.INSERT_ROW if the local_ind and os_ind are null
61 then put the default value of 'N'. Bug 2446067
62 pkpatel 31-JUL-2002 Bug No: 2461744
63 Removed the UPPER check for INSTITUTION_CD
64 pkpatel 25-OCT-2002 Bug No: 2613704
65 Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
66 ssawhney 30-APR-2003 V2API - OVN implementation.
67 skpandey 27-SEP-2005 Bug: 3663505
68 Description: Added ATTRIBUTES 21 TO 24 to store additional information in IGS_OR_GEN_012_PKG call
69 ***************************************************************/
70 l_return_status VARCHAR2(1);
71 l_msg_data VARCHAR2(2000);
72 l_msg_dt VARCHAR2(2000);
73 l_rowid VARCHAR2(25);
74 l_party_id hz_parties.party_id%TYPE;
75 l_success VARCHAR2(1);
76 l_ovn hz_parties.object_version_number%TYPE;
77 l_err_cd igs_or_inst_int.error_code%TYPE;
78
79
80 CURSOR c_inst_code(cp_party_id NUMBER) IS
81 SELECT party_number
82 FROM hz_parties
83 WHERE party_id = cp_party_id;
84
85
86 BEGIN
87 -- Call the Autogenerate Logic to check for the Profile Value of Hz_Generate_Party_Number
88 autoGenerateLogic(p_inst_rec, l_success,l_err_cd);
89 IF l_success = 'Y' THEN
90 -- Create Organization
91 IGS_OR_GEN_012_PKG.CREATE_ORGANIZATION (
92 p_institution_cd => p_inst_rec.NEW_INSTITUTION_CD,
93 p_name => p_inst_rec.NAME,
94 p_status => 'A',
95 p_attribute_category => p_inst_rec.ATTRIBUTE_CATEGORY,
96 p_attribute1 => p_inst_rec.ATTRIBUTE1,
97 p_attribute2 => p_inst_rec.ATTRIBUTE2,
98 p_attribute3 => p_inst_rec.ATTRIBUTE3,
99 p_attribute4 => p_inst_rec.ATTRIBUTE4,
100 p_attribute5 => p_inst_rec.ATTRIBUTE5,
101 p_attribute6 => p_inst_rec.ATTRIBUTE6,
102 p_attribute7 => p_inst_rec.ATTRIBUTE7,
103 p_attribute8 => p_inst_rec.ATTRIBUTE8,
104 p_attribute9 => p_inst_rec.ATTRIBUTE9,
105 p_attribute10 => p_inst_rec.ATTRIBUTE10,
106 p_attribute11 => p_inst_rec.ATTRIBUTE11,
107 p_attribute12 => p_inst_rec.ATTRIBUTE12,
108 p_attribute13 => p_inst_rec.ATTRIBUTE13,
109 p_attribute14 => p_inst_rec.ATTRIBUTE14,
110 p_attribute15 => p_inst_rec.ATTRIBUTE15,
111 p_attribute16 => p_inst_rec.ATTRIBUTE16,
112 p_attribute17 => p_inst_rec.ATTRIBUTE17,
113 p_attribute18 => p_inst_rec.ATTRIBUTE18,
114 p_attribute19 => p_inst_rec.ATTRIBUTE19,
115 p_attribute20 => p_inst_rec.ATTRIBUTE20,
116 p_return_status => l_return_status,
117 p_msg_data => l_msg_data,
118 p_party_id => l_party_id,
119 p_object_version_number => l_ovn,
120 p_attribute21 => p_inst_rec.ATTRIBUTE21,
121 p_attribute22 => p_inst_rec.ATTRIBUTE22,
122 p_attribute23 => p_inst_rec.ATTRIBUTE23,
123 p_attribute24 => p_inst_rec.ATTRIBUTE24
124 );
125
126 IF l_return_status IN ('E','U') THEN
127 --Log a message to the Log File that the Create of Organisation failed
128
129 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
130 FND_MESSAGE.Set_Name('IGS','IGS_OR_INST_IMP_FAIL');
131 FND_MESSAGE.Set_Token('INT_ID',p_inst_rec.interface_id);
132 FND_MESSAGE.Set_Token('ERROR_CODE',' ');
133 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
134 'igs.plsql.igs_or_inst_imp_002.create_institution.retstatfail',
135 FND_MESSAGE.Get || '-' || l_msg_data ,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
136 END IF;
137
138
139 p_error_code:= 'E003';
140 p_error_text:= l_msg_data;
141 p_errind := 'Y';
142 RETURN;
143 ELSE
144 -- Create Record in the overflow Table
145 p_errind := 'N';
146 OPEN c_inst_code(l_party_id);
147 FETCH c_inst_code INTO p_instcode;
148 CLOSE c_inst_code;
149 BEGIN
150 IGS_PE_HZ_PARTIES_PKG.INSERT_ROW (
151 x_rowid => l_rowid,
152 x_mode => 'R',
153 x_party_id => l_party_id,
154 x_deceased_ind => NULL,
155 x_archive_exclusion_ind => NULL,
156 x_archive_dt => NULL,
157 x_purge_exclusion_ind => NULL,
158 x_purge_dt => NULL,
159 x_oracle_username => NULL,
160 x_proof_of_ins => NULL,
161 x_proof_of_immu => NULL,
162 x_level_of_qual => NULL,
163 x_military_service_reg => NULL,
164 x_veteran => NULL,
165 x_institution_cd => NULL,
166 x_oi_local_institution_ind => NVL(p_inst_rec.LOCAL_INSTITUTION_IND,'N'),
167 x_oi_os_ind => NVL(p_inst_rec.OS_IND,'N'),
168 x_oi_govt_institution_cd => p_inst_rec.GOVT_INSTITUTION_CD,
169 x_oi_inst_control_type => p_inst_rec.INST_CONTROL_TYPE,
170 x_oi_institution_type => p_inst_rec.INSTITUTION_TYPE,
171 x_oi_institution_status => p_inst_rec.INSTITUTION_STATUS,
172 x_ou_start_dt => NULL,
173 x_ou_end_dt => NULL,
174 x_ou_member_type => NULL,
175 x_ou_org_status => NULL,
176 x_ou_org_type => NULL,
177 x_inst_org_ind => 'I',
178 x_inst_priority_cd => p_inst_rec.INST_PRIORITY_CD,
179 x_inst_eps_code => p_inst_rec.EPS_CODE,
180 x_inst_phone_country_code => p_inst_rec.PHONE_COUNTRY,
181 x_inst_phone_area_code => p_inst_rec.PHONE_AREA,
182 x_inst_phone_number => p_inst_rec.PHONE_NUMBER,
183 x_adv_studies_classes => p_inst_rec.ADV_STUDIES_CLASSES,
184 x_honors_classes => p_inst_rec.HONORS_CLASSES,
185 x_class_size => p_inst_rec.CLASS_SIZE,
186 x_sec_school_location_id => p_inst_rec.SEC_SCHOOL_LOCATION_ID,
187 x_percent_plan_higher_edu => p_inst_rec.PERCENT_PLAN_HIGHER_EDU,
188 x_fund_authorization => NULL,
189 x_birth_city => NULL,
190 x_birth_country => NULL,
191 x_oss_org_unit_cd => p_instcode --mmkumar, party number change
192 );
193 EXCEPTION
194 WHEN OTHERS THEN
195 p_error_code:= 'E044';
196 p_error_text:= NULL;
197 p_errind := 'Y';
198
199 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
200 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
201 'igs.plsql.igs_or_inst_imp_002.create_institution.exc1',
202 FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
203 END IF;
204 END;
205 END IF;
206
207 ELSE -- l_success = N
208 --Log a message to the Log File that the Create of Institution failed
209 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
210 FND_MESSAGE.Set_Name('IGS','IGS_OR_INST_IMP_FAIL');
211 FND_MESSAGE.Set_Token('INT_ID',p_inst_rec.interface_id);
212 FND_MESSAGE.Set_Token('ERROR_CODE',' ');
213 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
214 'igs.plsql.igs_or_inst_imp_002.create_institution.autogenfail',
215 FND_MESSAGE.Get || '-' || l_msg_data ,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
216 END IF;
217 p_error_code:=l_err_cd;
218 p_error_text:=NULL;
219 p_errind := 'Y';
220 END IF; -- If l_success = Y
221
222 EXCEPTION
223 WHEN OTHERS THEN
224 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
225 FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
226 FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.create_institution');
227 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
228 'igs.plsql.igs_or_inst_imp_002.create_institution.exc2',
229 FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
230 END IF;
231
232 p_error_code:= 'E003';
233 p_error_text:= NULL;
234 p_errind := 'Y';
235
236 END create_institution;
237
238 PROCEDURE create_crosswalk_master (
239 p_inst_code IN VARCHAR2,
240 p_inst_name IN VARCHAR2,
241 p_errind OUT NOCOPY VARCHAR2,
242 p_crswalk_id OUT NOCOPY NUMBER )
243
244 AS
245 /*************************************************************
246 Created By :samaresh
247 Date Created By : 17-JUL-2001
248 Purpose : This Procedure creates a New Record in the Crosswalk
249 master table
250 Know limitations, enhancements or remarks
251 Change History
252 Who When What
253 kumma 16-JUL-2002 Converted the p_inst_code to upper,2446067
254 pkpatel 31-JUL-2002 Bug No: 2461744
255 Removed the UPPER check for INSTITUTION_CD
256 ***************************************************************/
257
258 l_rowid VARCHAR2(25);
259 l_crswalk_id igs_or_cwlk.crosswalk_id%TYPE;
260
261 BEGIN
262
263 IGS_OR_CWLK_PKG.INSERT_ROW (
264 x_rowid => l_rowid,
265 x_crosswalk_id => l_crswalk_id,
266 x_institution_code => p_inst_code,
267 x_institution_name => p_inst_name
268 );
269 p_crswalk_id := l_crswalk_id;
270 p_errind := 'N';
271 EXCEPTION
272 WHEN OTHERS THEN
273 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
274 FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
275 FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.create_crosswalk_master');
276 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
277 'igs.plsql.igs_or_inst_imp_002.create_crosswalk_master.others',
278 FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
279 END IF;
280 p_errind := 'Y';
281
282 END create_crosswalk_master;
283
284 PROCEDURE create_crosswalk_detail (
285 p_crwlkid IN NUMBER,
286 p_datasrc IN VARCHAR2,
287 p_dataval IN VARCHAR2,
288 p_errind OUT NOCOPY VARCHAR2 )
289 AS
290 /*************************************************************
291 Created By :samaresh
292 Date Created By : 17-JUL-2001
293 Purpose : This Procedure creates a New Record in the Crosswalk
294 Detail table
295 Know limitations, enhancements or remarks
296 Change History
297 Who When What
298 pkpatel 30-DEC-2002 Bug No: 2729628
299 Modified the cursor chk_dup to remove the COUNT
300 ***************************************************************/
301
302 CURSOR chk_dup(cp_data_src VARCHAR2,cp_data_val VARCHAR2) IS
303 SELECT 'Y'
304 FROM IGS_OR_CWLK_DTL
305 WHERE ALT_ID_TYPE = cp_data_src AND
306 ALT_ID_VALUE = cp_data_val;
307
308 l_rowid VARCHAR2(25);
309 l_exists VARCHAR2(1);
310 l_crswalkdtl_id igs_or_cwlk_dtl.crosswalk_dtl_id%TYPE;
311
312 BEGIN
313
314 OPEN chk_dup(p_datasrc,p_dataval);
315 FETCH chk_dup INTO l_exists;
316 CLOSE chk_dup;
317
318 IF l_exists IS NULL THEN
319 IGS_OR_CWLK_DTL_PKG.INSERT_ROW (
320 x_rowid => l_rowid,
321 x_crosswalk_dtl_id => l_crswalkdtl_id,
322 x_crosswalk_id => p_crwlkid,
323 x_alt_id_type => p_datasrc,
324 x_alt_id_value => p_dataval,
325 x_mode => 'R');
326 END IF;
327 p_errind := 'N';
328 EXCEPTION
329 WHEN OTHERS THEN
330 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
331 FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
332 FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.create_crosswalk_detail');
333 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
334 'igs.plsql.igs_or_inst_imp_002.create_crosswalk_detail.others',
335 FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
336 END IF;
337 p_errind := 'Y';
338 END create_crosswalk_detail;
339
340 PROCEDURE create_alternate_id (
341 p_instcd IN VARCHAR2,
342 p_altidtype IN VARCHAR2,
343 p_altidval IN VARCHAR2,
344 p_error_code OUT NOCOPY VARCHAR2,
345 p_errind OUT NOCOPY VARCHAR2 )
346 AS
347 /*************************************************************
348 Created By :samaresh
349 Date Created By : 17-JUL-2001
350 Purpose : This Procedure creates a New Record in the IGS_OR_ORG_ALT_IDS table
351 Know limitations, enhancements or remarks
352 Change History
353 Who When What
354 ssawhney 30-APR-2003 V2API - OVN implementation
355 pkpatel 6-JAN-2003 Bug No: 2528605
356 Modified the logic to insert the alternate id. It will create a new alternate ID
357 if there is no ACTIVE alternate id present.
358 kumma 16-jul-2002 changed the cursor c_partyid to put upper on cp_instcd,2446067
359 pkpatel 31-JUL-2002 Bug No: 2461744
360 Removed the UPPER check for INSTITUTION_CD
361 npalanis 27-OCT-2002 Bug No: 2613704
362 Added the parameter p_error_code and added the check for overlap of alternate ID
363 ***************************************************************/
364
365 CURSOR c_exists (cp_alt_id_type igs_or_org_alt_ids.org_alternate_id_type%TYPE,
366 cp_alt_id_val VARCHAR2,
367 cp_instcd igs_or_org_alt_ids.org_structure_id%TYPE,
368 cp_structure_type igs_or_org_alt_ids.org_structure_type%TYPE,
369 cp_end_date VARCHAR2) IS
370 SELECT org_alternate_id
371 FROM igs_or_org_alt_ids
372 WHERE org_alternate_id_type = cp_alt_id_type
373 AND org_structure_id = cp_instcd
374 AND org_structure_type = cp_structure_type
375 AND ( SYSDATE BETWEEN start_date AND NVL(end_date,TO_DATE(cp_end_date,'YYYY/MM/DD')) );
376
377 --mmkumar, party number impact, changed the following cursor to resolve the foreign key via igs_pe_hz_parties
378 CURSOR c_partyid (cp_instcd VARCHAR2) IS
379 SELECT hp.party_id
380 FROM HZ_PARTIES hp, igs_pe_hz_parties ihp
381 WHERE ihp.oss_org_unit_cd = cp_instcd and
382 ihp.party_id = hp.party_id;
383
384 l_rowid VARCHAR2(25);
385 l_partyid hz_parties.party_id%TYPE;
386 l_org_alternate_id igs_or_org_alt_ids.org_alternate_id%TYPE;
387
388 BEGIN
389 p_error_code := NULL;
390 OPEN c_exists (p_altidtype,p_altidval,p_instcd,'INSTITUTE','4712/12/31');
391 FETCH c_exists INTO l_org_alternate_id;
392 CLOSE c_exists;
393
394 IF l_org_alternate_id IS NULL THEN
395
396 OPEN c_partyid(p_instcd);
397 FETCH c_partyid INTO l_partyid;
398 CLOSE c_partyid;
399
400 IGS_OR_ORG_ALT_IDS_PKG.INSERT_ROW(
401 x_rowid => l_rowid,
402 x_org_structure_id => p_instcd,
403 x_org_structure_type => 'INSTITUTE',
404 x_org_alternate_id_type => p_altidtype,
405 x_org_alternate_id => p_altidval,
406 x_start_date => SYSDATE,
407 x_end_date => NULL,
408 x_mode => 'R');
409 p_errind := 'N';
410 ELSE
411
412 -- If there is already active alternate ID present for this ID type and the import process
413 -- is trying to import for different alternate ID then throw an error.
414
415 IF l_org_alternate_id <> p_altidval THEN
416 p_error_code := 'E053';
417 p_errind := 'Y';
418 END IF;
419 END IF;
420 EXCEPTION
421 WHEN OTHERS THEN
422 p_error_code := 'E005';
423 p_errind := 'Y';
424 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
425 FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
426 FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.create_alternate_id');
427 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
428 'igs.plsql.igs_or_inst_imp_002.create_alternate_id.others',
429 FND_MESSAGE.Get ||'-' || p_instcd || '-' || p_altidtype || '-' || p_altidval || SQLERRM,
430 NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
431 END IF;
432
433 END create_alternate_id;
434
435 PROCEDURE update_institution(
436 p_instcd IN VARCHAR2,
437 p_instrec IN IGS_OR_INST_INT%ROWTYPE,
438 p_errind OUT NOCOPY VARCHAR2,
439 p_error_code OUT NOCOPY VARCHAR2,
440 p_error_text OUT NOCOPY VARCHAR2 )
441 AS
442 /*************************************************************
443 Created By :samaresh
444 Date Created By : 17-JUL-2001
445 Purpose : This Procedure Updates an Institution Record
446 Know limitations, enhancements or remarks
447 Change History
448 Who When What
449 SMVK 05-Feb-2002 Added Fund Authorization to
450 IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW calls
451 as per enhancement bug no.2191470.
452 kumma 14-JUN-2002 Uncommented the call to message IGS_OR_INST_UPD_FAIL, 2410165
453 kumma 15-JUL-2002 In call to UPDATE_ORGANIZATION ,
454 converted the INSTITUTION_CD to upper.
455 In call to IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW if the local_ind and os_ind are null
456 then substituted them witholder values of database. Bug 2446067
457 pkpatel 31-JUL-2002 Bug No: 2461744
458 Removed the UPPER check for INSTITUTION_CD
459 pkpatel 25-OCT-2002 Bug No: 2613704
460 Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
461 ssawhney 30-APR-2003 V2API - OVN implementation.
462 vrathi 28-MAY-2003 Bug No: 2961982 Replaced update_row with add_row
463 ssaleem 26-SEP-2003 NULL check has been added while calling
464 IGS_OR_GEN_012_PKG.UPDATE_ORGANIZATION and igs_pe_hz_parties_pkg.ADD_row,
465 it is made sure that NULL values does not replace
466 existing values in the table.
467 skpandey 27-SEP-2005 Bug: 3663505
468 Description: Added ATTRIBUTES 21 TO 24 to store additional information in IGS_OR_GEN_012_PKG call
469 ***************************************************************/
470 CURSOR c_partyid (cp_instcd VARCHAR2) IS
471 SELECT hp.*
472 FROM HZ_PARTIES HP, igs_pe_hz_parties ihp
473 WHERE ihp.oss_org_unit_cd = cp_instcd and
474 hp.party_id = ihp.party_id;
475
476 CURSOR c_getrow (cp_partyid NUMBER) IS
477 SELECT rowid ,PP.*
478 FROM IGS_PE_HZ_PARTIES PP
479 WHERE party_id = cp_partyid;
480
481
482 l_msg_data VARCHAR2(2000);
483 l_return_status VARCHAR2(1);
484 l_rowid VARCHAR2(25);
485 l_ovn hz_parties.object_version_number%TYPE;
486 l_party_rec c_partyid%ROWTYPE;
487 l_getrow_rec c_getrow%ROWTYPE;
488
489 BEGIN
490
491 OPEN c_partyid(p_instcd);
492 FETCH c_partyid INTO l_party_rec;
493 CLOSE c_partyid;
494
495 l_ovn := l_party_rec.object_version_number;
496
497 IGS_OR_GEN_012_PKG.UPDATE_ORGANIZATION (
498 p_party_id => l_party_rec.party_id,
499 p_institution_cd => p_instcd,
500 p_name => NVL(p_instrec.NAME,l_party_rec.party_name),
501 p_status => l_party_rec.status,
502 p_last_update => l_party_rec.last_update_date,
503 p_attribute_category => NVL(p_instrec.ATTRIBUTE_CATEGORY, l_party_rec.attribute_category),
504 p_attribute1 => NVL(p_instrec.ATTRIBUTE1,l_party_rec.attribute1),
505 p_attribute2 => NVL(p_instrec.ATTRIBUTE2,l_party_rec.attribute2),
506 p_attribute3 => NVL(p_instrec.ATTRIBUTE3,l_party_rec.attribute3),
507 p_attribute4 => NVL(p_instrec.ATTRIBUTE4,l_party_rec.attribute4),
508 p_attribute5 => NVL(p_instrec.ATTRIBUTE5,l_party_rec.attribute5),
509 p_attribute6 => NVL(p_instrec.ATTRIBUTE6,l_party_rec.attribute6),
510 p_attribute7 => NVL(p_instrec.ATTRIBUTE7,l_party_rec.attribute7),
511 p_attribute8 => NVL(p_instrec.ATTRIBUTE8,l_party_rec.attribute8),
512 p_attribute9 => NVL(p_instrec.ATTRIBUTE9,l_party_rec.attribute9),
513 p_attribute10 => NVL(p_instrec.ATTRIBUTE10,l_party_rec.attribute10),
514 p_attribute11 => NVL(p_instrec.ATTRIBUTE11,l_party_rec.attribute11),
515 p_attribute12 => NVL(p_instrec.ATTRIBUTE12,l_party_rec.attribute12),
516 p_attribute13 => NVL(p_instrec.ATTRIBUTE13,l_party_rec.attribute13),
517 p_attribute14 => NVL(p_instrec.ATTRIBUTE14,l_party_rec.attribute14),
518 p_attribute15 => NVL(p_instrec.ATTRIBUTE15,l_party_rec.attribute15),
519 p_attribute16 => NVL(p_instrec.ATTRIBUTE16,l_party_rec.attribute16),
520 p_attribute17 => NVL(p_instrec.ATTRIBUTE17,l_party_rec.attribute17),
521 p_attribute18 => NVL(p_instrec.ATTRIBUTE18,l_party_rec.attribute18),
522 p_attribute19 => NVL(p_instrec.ATTRIBUTE19,l_party_rec.attribute19),
523 p_attribute20 => NVL(p_instrec.ATTRIBUTE20,l_party_rec.attribute20),
524 p_return_status => l_return_status,
525 p_msg_data => l_msg_data,
526 p_object_version_number => l_ovn,
527 p_attribute21 => NVL(p_instrec.ATTRIBUTE21,l_party_rec.attribute21),
528 p_attribute22 => NVL(p_instrec.ATTRIBUTE22,l_party_rec.attribute22),
529 p_attribute23 => NVL(p_instrec.ATTRIBUTE23,l_party_rec.attribute23),
530 p_attribute24 => NVL(p_instrec.ATTRIBUTE24,l_party_rec.attribute24)
531 );
532
533 IF l_return_status IN ('E','U') THEN
534 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
535 FND_MESSAGE.Set_Name('IGS','IGS_OR_INST_IMP_FAIL');
536 FND_MESSAGE.Set_Token('INT_ID',p_instrec.interface_id);
537 FND_MESSAGE.Set_Token('ERROR_CODE',' ');
538 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
539 'igs.plsql.igs_or_inst_imp_002.update_institution.updatefail',
540 FND_MESSAGE.Get || '-' || l_msg_data ,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
541 END IF;
542
543 p_error_code:= 'E002';
544 p_error_text:= l_msg_data;
545 p_errind := 'Y';
546 return;
547 ELSE
548 p_errind := 'N';
549 -- Call the Update of Igs_Pe_Hz_Parties Table
550 OPEN c_getrow(l_party_rec.party_id);
551 FETCH c_getrow INTO l_getrow_rec;
552 CLOSE c_getrow;
553 BEGIN
554
555 igs_pe_hz_parties_pkg.ADD_row (
556 x_mode => 'R',
557 x_rowid => l_rowid,
558 x_party_id => l_party_rec.party_id,
559 x_deceased_ind => l_getrow_rec.DECEASED_IND,
560 x_archive_exclusion_ind => l_getrow_rec.ARCHIVE_EXCLUSION_IND,
561 x_archive_dt => l_getrow_rec.ARCHIVE_DT,
562 x_purge_exclusion_ind => l_getrow_rec.PURGE_EXCLUSION_IND,
563 x_purge_dt => l_getrow_rec.PURGE_DT,
564 x_oracle_username => l_getrow_rec.ORACLE_USERNAME,
565 x_proof_of_ins => l_getrow_rec.PROOF_OF_INS,
566 x_proof_of_immu => l_getrow_rec.PROOF_OF_IMMU,
567 x_level_of_qual => l_getrow_rec.LEVEL_OF_QUAL,
568 x_military_service_reg => l_getrow_rec.MILITARY_SERVICE_REG,
569 x_veteran => l_getrow_rec.VETERAN,
570 x_institution_cd => NULL,
571 x_oi_local_institution_ind => NVL(l_getrow_rec.OI_LOCAL_INSTITUTION_IND,'N'),
572 x_oi_os_ind => NVL(p_instrec.OS_IND, l_getrow_rec.OI_os_ind),
573 x_oi_govt_institution_cd => NVL(p_instrec.GOVT_INSTITUTION_CD,l_getrow_rec.OI_GOVT_INSTITUTION_CD),
574 x_oi_inst_control_type => p_instrec.INST_CONTROL_TYPE,
575 x_oi_institution_type => p_instrec.INSTITUTION_TYPE,
576 x_oi_institution_status => NVL(p_instrec.INSTITUTION_STATUS,l_getrow_rec.OI_INSTITUTION_STATUS),
577 x_ou_start_dt => NULL,
578 x_ou_end_dt => l_getrow_rec.OU_END_DT,
579 x_ou_member_type => l_getrow_rec.OU_MEMBER_TYPE,
580 x_ou_org_status => l_getrow_rec.OU_ORG_STATUS,
581 x_ou_org_type => l_getrow_rec.OU_ORG_TYPE,
582 x_inst_org_ind => 'I' ,
583 x_inst_priority_cd => NVL(p_instrec.INST_PRIORITY_CD,l_getrow_rec.INST_PRIORITY_CD),
584 x_inst_eps_code => NVL(p_instrec.EPS_CODE,l_getrow_rec.INST_EPS_CODE),
585 x_inst_phone_country_code => NVL(p_instrec.PHONE_COUNTRY,l_getrow_rec.INST_PHONE_COUNTRY_CODE),
586 x_inst_phone_area_code => NVL(p_instrec.PHONE_AREA,l_getrow_rec.INST_PHONE_AREA_CODE),
587 x_inst_phone_number => NVL(p_instrec.PHONE_NUMBER,l_getrow_rec.INST_PHONE_NUMBER),
588 x_adv_studies_classes => NVL(p_instrec.ADV_STUDIES_CLASSES,l_getrow_rec.ADV_STUDIES_CLASSES),
589 x_honors_classes => NVL(p_instrec.HONORS_CLASSES,l_getrow_rec.HONORS_CLASSES),
590 x_class_size => NVL(p_instrec.CLASS_SIZE,l_getrow_rec.CLASS_SIZE),
591 x_sec_school_location_id => NVL(p_instrec.SEC_SCHOOL_LOCATION_ID,l_getrow_rec.SEC_SCHOOL_LOCATION_ID),
592 x_percent_plan_higher_edu => NVL(p_instrec.PERCENT_PLAN_HIGHER_EDU,l_getrow_rec.PERCENT_PLAN_HIGHER_EDU),
593 x_fund_authorization => l_getrow_rec.fund_authorization,
594 x_pe_info_verify_time => l_getrow_rec.pe_info_verify_time,
595 x_birth_city => l_getrow_rec.birth_city,
596 x_birth_country => l_getrow_rec.birth_country,
597 x_oss_org_unit_cd => l_getrow_rec.oss_org_unit_cd --mmkumar, party number impact
598 );
599 EXCEPTION
600 WHEN OTHERS THEN
601 p_error_code:= 'E045';
602 p_error_text:= NULL;
603 p_errind := 'Y';
604 END;
605 END IF;
606 EXCEPTION
607 WHEN OTHERS THEN
608 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
609 FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
610 FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.Update Institution');
611 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
612 'igs.plsql.igs_or_inst_imp_002.update_institution.addrowfail',
613 FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
614 END IF;
615
616 p_error_code:= 'E002';
617 p_error_text:= NULL;
618 p_errind := 'Y';
619
620 END update_institution;
621
622 PROCEDURE update_crosswalk_master (
623 p_cwlkid IN NUMBER,
624 p_instcd IN VARCHAR2,
625 p_errind OUT NOCOPY VARCHAR2)
626 AS
627 /*************************************************************
628 Created By :samaresh
629 Date Created By : 17-JUL-2001
630 Purpose : This Procedure Updates a record in the Crosswalk Master
631 with the Institution Code
632 Know limitations, enhancements or remarks
633 Change History
634 Who When What
635 kumma 16-JUL-2002 changed the institution_cd to upper,2446067
636 pkpatel 31-JUL-2002 Bug No: 2461744
637 Removed the UPPER check for INSTITUTION_CD
638 ***************************************************************/
639 CURSOR c_getrow (cp_cwlkid NUMBER) IS
640 SELECT rowid ,ORC.*
641 FROM IGS_OR_CWLK ORC
642 WHERE crosswalk_id = cp_cwlkid;
643
644 l_rowid VARCHAR2(25);
645 l_getrow_rec c_getrow%ROWTYPE;
646
647 BEGIN
648 OPEN c_getrow(p_cwlkid);
649 FETCH c_getrow INTO l_getrow_rec;
650 CLOSE c_getrow;
651 IGS_OR_CWLK_PKG.update_row (
652 x_rowid => l_getrow_rec.rowid,
653 x_crosswalk_id => p_cwlkid,
654 x_institution_code => p_instcd,
655 x_institution_name => l_getrow_rec.institution_name,
656 x_mode => 'R' );
657 p_errind := 'N';
658
659 EXCEPTION
660 WHEN OTHERS THEN
661 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
662 FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
663 FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.Update Crosswalk Master');
664 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
665 'igs.plsql.igs_or_inst_imp_002.update_crosswalk_master.others',
666 FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
667 END IF;
668 p_errind := 'Y';
669 END update_crosswalk_master;
670
671 PROCEDURE autoGenerateLogic(
672 p_inst_rec IN IGS_OR_INST_INT%ROWTYPE,
673 p_success OUT NOCOPY VARCHAR2,
674 p_err_cd OUT NOCOPY VARCHAR2)
675 AS
676 /*************************************************************
677 Created By :samaresh
678 Date Created By : 17-JUL-2001
679 Purpose : This Procedure checks the Profile Value
680 of hz_generate_party_number
681 Know limitations, enhancements or remarks
682 Change History
683 Who When What
684 ***************************************************************/
685 l_generate_party_number VARCHAR2(1);
686
687 BEGIN
688 l_generate_party_number := FND_PROFILE.VALUE('HZ_GENERATE_PARTY_NUMBER');
689 IF l_generate_party_number = 'Y' AND p_inst_rec.new_institution_cd IS NOT NULL THEN
690 p_err_cd:='E008';
691 p_success := 'N';
692 ELSIF l_generate_party_number = 'N' AND p_inst_rec.new_institution_cd IS NULL THEN
693 p_err_cd:='E009';
694 p_success := 'N';
695 ELSE
696 p_success := 'Y';
697 END IF;
698 EXCEPTION
699 WHEN OTHERS THEN
700
701 IF IGS_OR_INST_IMP_001.gb_write_exception_log2 THEN
702 FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
703 FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.Auto Generate Logic');
704 FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
705 'igs.plsql.igs_or_inst_imp_002.autoGenerateLogic.others',
706 FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
707 END IF;
708 p_success := 'N';
709
710 END autoGenerateLogic;
711
712 END IGS_OR_INST_IMP_002;