1 PACKAGE BODY igs_uc_mv_imp_errcd AS
2 /* $Header: IGSUC34B.pls 120.1 2006/02/08 19:55:49 anwest noship $ */
3
4 /*===============================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===============================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL spec for package: IGS_UC_MV_IMP_ERRCD |
11 | |
12 | NOTES |
13 | This is a part of Concurrent Requeset Set which updates the main table |
14 | with the data uploaded into the temporary table from flat file by the 1st|
15 | part of this concurrent request set. |
16 | |
17 | HISTORY |
18 | Who When What |
19 | rgangara 05-Nov-2002 Create Version as part of Small systems support|
20 | Enhancement (UCFD02). Bug 2643048 |
21 | ayedubat 09-DEC-2002 added to TO_NUMBER to err_proc_rec.error_code |
22 | column as error codes are numeric data. |
23 | Also added the update row call if record already|
24 | found for the bug fix: 2700307 |
25 | pmarada 13-Jun-2003 Removed the igs_uc_refcodetype table references |
26 | and removed profile references as per the |
27 | UCFD203-Multiple cycles, bug 2669208 |
28 | anwest 18-Jan-06 Bug# 4950285 R12 Disable OSS Mandate |
29 *==============================================================================*/
30
31
32 PROCEDURE imp_error_codes (
33 errbuf OUT NOCOPY VARCHAR2,
34 retcode OUT NOCOPY NUMBER
35 ) IS
36 /*
37 || Created By : rgangara
38 || Created On : 05-Nov-2002
39 || Purpose : This is main Procedure which will transfer the Error Codes data
40 || from the temporary table to the main UCAS interface table.
41 || Known limitations, enhancements or remarks :
42 || Change History :
43 || Who When What
44 || anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
45 || (reverse chronological order - newest change first)
46 */
47
48 -- Declare all the local variables, cursors
49
50 -- Cursor to pick records from temp table to be processed.
51 CURSOR Err_proc_cur IS
52 SELECT error_code,
53 code_text
54 FROM igs_uc_mv_errcd_int;
55
56 -- Cursor to check for existence of 'EC' in Ref code types table.
57 CURSOR refcode_types_cur IS
58 SELECT lookup_code
59 FROM igs_lookup_values
60 WHERE lookup_type = 'IGS_UC_CODE_TYPES'
61 AND lookup_code = 'EC';
62
63 -- Cursor to check whether the CODE already exists in ref codes table.
64 CURSOR refcodes_cur (p_code igs_uc_ref_codes.code%TYPE) IS
65 SELECT rowid,
66 code_type,
67 code
68 FROM igs_uc_ref_codes
69 WHERE code_type = 'EC'
70 AND code = p_code;
71
72 refcode_types_rec refcode_types_cur%ROWTYPE;
73 refcodes_rec refcodes_cur%ROWTYPE;
74 l_rowid VARCHAR2(26) := NULL;
75 l_success_cnt NUMBER := 0;
76
77 BEGIN
78
79 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
80 IGS_GE_GEN_003.SET_ORG_ID;
81
82 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
83 retcode := 0;
84
85 -- check for 'EC' lookup code exists in the lookups or not
86 OPEN refcode_types_cur;
87 FETCH refcode_types_cur INTO refcode_types_rec;
88 IF refcode_types_cur%NOTFOUND THEN
89 FND_MESSAGE.SET_NAME('IGS', 'IGS_UC_REFCODETYP_NOT_EXISTS');
90 FND_MESSAGE.SET_TOKEN('CODE_TYPE','EC');
91 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
92 RETCODE := 2;
93 RETURN;
94 END IF;
95 CLOSE refcode_types_cur;
96
97 -- Insert Error Code data into Ref Codes table.
98 FOR err_proc_rec IN err_proc_cur
99 LOOP
100 -- check to see whether the code is NULL. If NULL then ignore such records.
101 IF err_proc_rec.error_code is NOT NULL THEN
102
103 BEGIN
104
105 -- Check to see whether the current Code already exists. If not found then insert
106 OPEN refcodes_cur( TO_NUMBER(err_proc_rec.error_code) );
107 FETCH refcodes_cur INTO refcodes_rec;
108
109 IF refcodes_cur%NOTFOUND THEN
110 l_rowid := NULL;
111
112 --Call the TBH of the main table to insert a new error code.
113 igs_uc_ref_codes_pkg.Insert_Row (
114 X_ROWID => l_rowid,
115 X_CODE_TYPE => 'EC',
116 X_CODE => TO_NUMBER(err_proc_rec.error_code),
117 X_CODE_TEXT => err_proc_rec.code_text,
118 X_IMPORTED => 'Y',
119 X_MODE => 'R' );
120 l_success_cnt := l_success_cnt + 1;
121
122 ELSE
123 --Call the TBH of the main table to update the existing error code.
124 igs_uc_ref_codes_pkg.update_row (
125 X_ROWID => refcodes_rec.rowid,
126 X_CODE_TYPE => refcodes_rec.code_type,
127 X_CODE => refcodes_rec.code,
128 X_CODE_TEXT => err_proc_rec.code_text,
129 X_IMPORTED => 'Y',
130 X_MODE => 'R' );
131 l_success_cnt := l_success_cnt + 1;
132
133 END IF;
134 CLOSE refcodes_cur;
135
136 EXCEPTION
137 WHEN VALUE_ERROR THEN
138 FND_MESSAGE.SET_NAME('IGS', 'IGS_UC_NON_NUMERIC_ERR_CD');
139 FND_MESSAGE.SET_TOKEN('ERR_CODE',err_proc_rec.error_code);
140 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
141 RETCODE := 2;
142 RETURN;
143 END ;
144
145 END IF;
146
147 END LOOP;
148
149 -- Print Number of records successfully transferred
150 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
151 FND_MESSAGE.SET_NAME('IGS', 'IGS_UC_MV_LOAD_SUCCESS');
152 FND_MESSAGE.SET_TOKEN('CNT', l_success_cnt);
153 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
154
155 -- commit the data;
156 COMMIT;
157 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
158
159 EXCEPTION
160
161 WHEN app_exception.record_lock_exception THEN
162 ROLLBACK;
163 retcode := 2;
164 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
165 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
166
167 WHEN OTHERS THEN
168 ROLLBACK;
169 RETCODE := 2;
170 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
171 FND_MESSAGE.SET_TOKEN('NAME','IGS_UC_MV_IMP_ERRCD.IMP_ERROR_CODES - '||SQLERRM);
172 ERRBUF := FND_MESSAGE.GET;
173 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
174 END imp_error_codes;
175
176 END igs_uc_mv_imp_errcd;
[Home] [Help]