1 PACKAGE BODY IGS_OR_PRC_CWLK AS
2 /* $Header: IGSOR13B.pls 115.7 2003/10/22 05:07:19 ssaleem ship $ */
3
4 -- declaring constants for the different status
5 g_stat_pend CONSTANT VARCHAR2(1) := '2';
6 g_stat_err CONSTANT VARCHAR2(1) := '3';
7 g_stat_prc CONSTANT VARCHAR2(1) := '1';
8
9 PROCEDURE transfer_data(errbuf IN OUT NOCOPY VARCHAR2,
10 retcode IN OUT NOCOPY NUMBER,
11 p_org_id NUMBER) AS
12 /******************************************************************
13
14 Created By: Amit Gairola
15
16 Date Created By: 13-07-2001
17
18 Purpose: The procedure imports the Crosswalk Interface data into the OSS
19 Crosswalk Data tables
20
21 Known limitations,enhancements,remarks:
22
23 Change History
24
25 Who When What
26 ssaleem 22-SEP-2003 Made the following changes for IGS.L
27 a) Deletion of completed records is done
28 b) Commit is issued for every 100 records
29 Savepoint is declared for every record and
30 rollback is issued until the last processed
31 record in case of an error
32 c) Successful logging messages for alternate IDs
33 are removed
34 pkpatel 15-JAN-2003 Bug No: 2528605
35 Made the alt_id_value UPPER while inserting.
36 ***************************************************************** */
37 l_rowid VARCHAR2(25);
38 l_cwlk_id IGS_OR_CWLK.Crosswalk_Id%TYPE;
39 l_cwlk_dtl_id IGS_OR_CWLK_DTL.Crosswalk_Dtl_Id%TYPE;
40 l_status IGS_OR_CWLK_INT.Status%TYPE;
41 l_exception BOOLEAN := FALSE;
42 l_records_processed NUMBER(3);
43
44 l_in_proc_alt_id igs_or_cwlk_dtl_int.alt_id_type%TYPE;
45 l_in_proc_alt_value igs_or_cwlk_dtl_int.alt_id_value%TYPE;
46
47 -- Cursor for fetching the records from IGS_OR_CWLK_INT table
48 -- based on the status passed as input
49 CURSOR cur_cwlk_int(cp_stat VARCHAR2) IS
50 SELECT *
51 FROM IGS_OR_CWLK_INT
52 WHERE status = cp_stat;
53
54 -- Cursor for fetching the records from the Crosswalk Detail Interface
55 -- tables based on the Crosswalk Interface Id from the Crosswalk table
56 CURSOR cur_cwlk_dtl_int(cp_cwlk_int_id IGS_OR_CWLK_INT.Interface_Crosswalk_Id%TYPE) IS
57 SELECT *
58 FROM IGS_OR_CWLK_DTL_INT
59 WHERE interface_crosswalk_id = cp_cwlk_int_id;
60
61 FUNCTION validate_alt_id(p_alternate_id_type IN igs_or_org_alt_idtyp.org_alternate_id_type%TYPE)
62 RETURN BOOLEAN AS
63
64 CURSOR rowid_cur(cp_close_ind igs_or_org_alt_idtyp.close_ind%TYPE,
65 cp_inst_flag igs_or_org_alt_idtyp.inst_flag%TYPE,
66 cp_org_alternate_id_type igs_or_org_alt_idtyp.org_alternate_id_type%TYPE) IS
67 SELECT rowid
68 FROM igs_or_org_alt_idtyp
69 WHERE org_alternate_id_type = cp_org_alternate_id_type AND
70 CLOSE_IND = cp_close_ind AND
71 inst_flag = cp_inst_flag;
72
73 rowid_rec rowid_cur%ROWTYPE;
74 BEGIN
75
76 OPEN rowid_cur('N','Y',p_alternate_id_type);
77 FETCH rowid_cur INTO rowid_rec;
78 IF (rowid_cur%FOUND) THEN
79 CLOSE rowid_cur;
80 RETURN(TRUE);
81 ELSE
82 CLOSE rowid_cur;
83 RETURN(FALSE);
84 END IF;
85
86 END validate_alt_id;
87 BEGIN
88
89 -- Set the Org Id
90 IGS_GE_GEN_003.Set_Org_Id(p_org_id);
91 -- Initialising the variables
92 l_exception := FALSE;
93 l_status := g_stat_pend;
94 l_records_processed := 0;
95
96 -- Loop through the Crosswalk Interface table for the records
97 -- which have the status as Pending
98 FOR cwlkrec IN cur_cwlk_int(g_stat_pend) LOOP
99 BEGIN
100
101 SAVEPOINT sp_record;
102
103 -- Call the Insert Row procedure of the table handler for the
104 -- Crosswalk table
105 l_rowid := NULL;
106 l_cwlk_id := NULL;
107 IGS_OR_CWLK_PKG.Insert_Row(x_rowid => l_rowid,
108 x_crosswalk_id => l_cwlk_id,
109 x_institution_code => NULL,
110 x_institution_name => cwlkrec.institution_name);
111
112 -- Log appropriate messages in the Log file of the Concurrent Manager
113 FND_MESSAGE.Set_Name('IGS',
114 'IGS_OR_IMP_INST_NAME');
115 FND_MESSAGE.Set_Token('INST_NAME',
116 cwlkrec.institution_name);
117 FND_FILE.Put_Line(FND_FILE.Log,
118 FND_MESSAGE.Get);
119
120 -- Loop through the Crosswalk Detail Interface table based on the
121 -- Crosswalk Id passed from the Crosswalk Interface table(previous cursor)
122
123 FOR cwlkdtlrec IN cur_cwlk_dtl_int(cwlkrec.interface_crosswalk_id) LOOP
124
125 -- Check if the Alt_Id_Type exists in the IGS_OR_ORG_ALT_IDTYP table
126 IF NOT validate_alt_id(cwlkdtlrec.alt_id_type) THEN
127
128 -- If it does not exist, then log the appropriate message in the Concurrent Manager logfile
129 FND_MESSAGE.Set_Name('IGS',
130 'IGS_OR_INVALID_ALT_ID_TYP');
131 FND_MESSAGE.Set_Token('ALT_ID_TYPE',
132 cwlkdtlrec.alt_id_type);
133 FND_FILE.Put_Line(FND_FILE.Log,
134 FND_MESSAGE.Get);
135
136 -- Raise the exception so that the record can be updated to Status 3
137 APP_EXCEPTION.RAISE_EXCEPTION;
138 END IF;
139
140 -- Call the Insert Row tablehandler for the Crosswalk Detail
141 l_rowid := NULL;
142 l_cwlk_dtl_id := NULL;
143
144 l_in_proc_alt_id := cwlkdtlrec.alt_id_type;
145 l_in_proc_alt_value := UPPER(cwlkdtlrec.alt_id_value);
146
147 IGS_OR_CWLK_DTL_PKG.Insert_Row(x_rowid => l_rowid,
148 x_crosswalk_dtl_id => l_cwlk_dtl_id,
149 x_crosswalk_id => l_cwlk_id,
150 x_alt_id_type => cwlkdtlrec.alt_id_type,
151 x_alt_id_value => UPPER(cwlkdtlrec.alt_id_value));
152
153 END LOOP;
154 EXCEPTION
155 WHEN Others THEN
156
157 -- If the exception is raised, then
158 -- rollback the transaction and log the message in the
159 -- Concurrent Manager's log file.Set the Exception variable
160 -- to TRUE for appropriate updation of the Interface record
161 ROLLBACK TO sp_record;
162 FND_FILE.Put_Line(FND_FILE.Log,
163 FND_MESSAGE.Get || ' ' || l_in_proc_alt_id || '-' || l_in_proc_alt_value);
164 l_exception := TRUE;
165 END;
166
167 -- If the Exception variable is set to True, then update the status variable to error
168 -- else update to Processed.
169 IF l_exception THEN
170 l_status := g_stat_err;
171 ELSE
172 l_status := g_stat_prc;
173 END IF;
174
175 -- Call the Update DML for updating the status appropriately
176 UPDATE IGS_OR_CWLK_INT
177 SET status = l_status
178 WHERE interface_crosswalk_id = cwlkrec.interface_crosswalk_id;
179
180 -- Issue Commit for every 100 records
181 l_records_processed := l_records_processed + 1;
182
183 IF l_records_processed = 100 THEN
184 l_records_processed := 0;
185 commit;
186 END IF;
187
188 -- Reset the variable to FALSE
189 l_exception := FALSE;
190 END LOOP;
191
192 -- Modification made for IGS.L, delete records that are successfully inserted/updated
193 DELETE FROM IGS_OR_CWLK_DTL_INT DTL
194 WHERE EXISTS
195 (SELECT 1
196 FROM IGS_OR_CWLK_INT MSTR
197 WHERE MSTR.STATUS = '1' AND
198 MSTR.INTERFACE_CROSSWALK_ID = DTL.INTERFACE_CROSSWALK_ID);
199
200 DELETE FROM IGS_OR_CWLK_INT WHERE STATUS = '1';
201
202 commit;
203
204 EXCEPTION
205 WHEN Others THEN
206 retcode := 2;
207 IGS_GE_MSG_STACK.Conc_Exception_Hndl;
208 END transfer_data;
209 END igs_or_prc_cwlk;
[Home] [Help]