1 PACKAGE BODY IGR_IMP_002 AS
2 /* $Header: IGSRT13B.pls 120.0 2005/06/01 21:44:35 appldev noship $ */
3 /* ------------------------------------------------------------------------------------------------------------------------
4 || Created By : rbezawad
5 || Created On : 27-Feb-05
6 || Purpose : Extract of IGR related references from Admissions Import process packages (IGSAD79B.pls and IGSAD93B.pls)
7 || to get rid of probable compilation errors for non-IGR customers.
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || WHO WHEN WHAT
11 9-Mar-05 rbezawad Modified for APC Integration Build. Bug: 3973942.
12 Also obsoelted usage of Entry Status/Program/Unit Set code columns.
13 ---------------------------------------------------------------------------------------------------------------------------*/
14
15 -- These are the package variables to hold the value of whether the particular category is included or not.
16 g_inquiry_inst_inc BOOLEAN := FALSE;
17 g_inquiry_dtls_inc BOOLEAN := FALSE;
18 g_inquiry_acad_int_inc BOOLEAN := FALSE;
19 g_inquiry_pkg_itm_inc BOOLEAN := FALSE;
20 g_inquiry_info_type_inc BOOLEAN := FALSE;
21 g_inquiry_char_inc BOOLEAN := FALSE;
22
23
24 PROCEDURE update_parent_record_status ( p_interface_run_id IN NUMBER ) AS
25 /*************************************************************
26 Created By : rbezawad
27 Date Created By : 27-Feb-05
28 Purpose : Procedure to set the IGR_I_APPL_INT.STATUS value to Warning (status='4') when IGR_I_APPL_INT record
29 is processed successfully (status='1') but processing of any of the child interface records is not
30 successful (status<>'1'). Also set the IGS_AD_INTERFACE.STATUS to Error (Status='3') when processing any
31 of the child interface records (IGR_I_APPL_INT) is not successful (status<>'1').
32 Know limitations, enhancements or remarks
33 Change History
34 Who When What
35 (reverse chronological order - newest change first)
36 ***************************************************************/
37 BEGIN
38
39 -- Based upon inquiry child
40 UPDATE igr_i_appl_int iappl
41 SET status = '4',
42 error_code = 'E347',
43 error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
44 WHERE status = '1'
45 AND interface_run_id = p_interface_run_id
46 AND (
47 EXISTS (SELECT 1 FROM igr_i_lines_int WHERE status <> '1' AND interface_inq_appl_id = iappl.interface_inq_appl_id)
48 OR EXISTS (SELECT 1 FROM igr_i_pkg_int WHERE status <> '1' AND interface_inq_appl_id = iappl.interface_inq_appl_id)
49 OR EXISTS (SELECT 1 FROM igr_i_info_int WHERE status <> '1' AND interface_inq_appl_id = iappl.interface_inq_appl_id)
50 OR EXISTS (SELECT 1 FROM igr_i_char_int WHERE status <> '1' AND interface_inq_appl_id = iappl.interface_inq_appl_id)
51 );
52 COMMIT;
53
54 -- Based upon person child
55 UPDATE igs_ad_interface ad
56 SET record_status = '3',
57 status = '4',
58 error_code = 'E347'
59 WHERE status = '1'
60 AND interface_run_id = p_interface_run_id
61 AND (EXISTS (SELECT 1 FROM igr_i_appl_int WHERE status <> '1' AND interface_id = ad.interface_id));
62 COMMIT;
63
64 END update_parent_record_status;
65
66
67 PROCEDURE prc_ad_category (p_source_type_id IN NUMBER,
68 p_interface_run_id IN NUMBER,
69 p_enable_log IN VARCHAR2,
70 p_schema IN VARCHAR2
71 ) AS
72 /*************************************************************
73 Created By : rbezawad
74 Date Created By : 27-Feb-05
75 Purpose : This procedure is used to call the procedures in related inquiry source category (INQUIRY_INSTANCE)
76 to import each entity (INQUIRY_DETAILS, INQUIRY_ACADEMIC_INTEREST, INQUIRY_PACKAGE_ITEMS, INQUIRY_INFORMATION_TYPES
77 and INQUIRY_CHARACTERISTICS).
78 Know limitations, enhancements or remarks
79 Change History
80 Who When What
81 (reverse chronological order - newest change first)
82 ***************************************************************/
83
84 l_meaning igs_lookup_values.meaning%TYPE;
85
86 BEGIN
87
88 g_inquiry_inst_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'INQUIRY_INSTANCE');
89 IF g_inquiry_inst_inc THEN
90 g_inquiry_dtls_inc := TRUE;
91 g_inquiry_acad_int_inc := TRUE;
92 g_inquiry_pkg_itm_inc := TRUE;
93 g_inquiry_info_type_inc := TRUE;
94 g_inquiry_char_inc := TRUE;
95 ELSE
96 g_inquiry_dtls_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'INQUIRY_DETAILS');
97 g_inquiry_acad_int_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'INQUIRY_ACADEMIC_INTEREST');
98 g_inquiry_pkg_itm_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'INQUIRY_PACKAGE_ITEMS');
99 g_inquiry_info_type_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'INQUIRY_INFORMATION_TYPES');
100 g_inquiry_char_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'INQUIRY_CHARACTERISTICS');
101 END IF;
102
103 IF g_inquiry_inst_inc THEN
104 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'INQUIRY_INSTANCE', 8405);
105
106 IF p_enable_log = 'Y' THEN
107 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
108 p_token_name => 'TYPE_NAME',
109 p_token_value => l_meaning);
110 END IF;
111 -- Would need to process all inquiry entities since INQUIRY_INSTANCE includes all entities
112 -- Processing would take place through the below mentioned category handling
113 -- g_inquiry_dtls_inc (INQUIRY_DETAILS)
114 -- g_inquiry_acad_int_inc (INQUIRY_ACADEMIC_INTEREST)
115 -- g_inquiry_pkg_itm_inc (INQUIRY_PACKAGE_ITEMS)
116 -- g_inquiry_info_type_inc (INQUIRY_INFORMATION_TYPES)
117 -- g_inquiry_char_inc (INQUIRY_CHARACTERISTICS)
118 END IF; -- g_inquiry_inst_inc
119
120 IF g_inquiry_dtls_inc THEN
121 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'INQUIRY_DETAILS', 8405);
122
123 IF p_enable_log = 'Y' THEN
124 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
125 p_token_name => 'TYPE_NAME',
126 p_token_value => l_meaning);
127 END IF;
128
129 -- Populating the interface table with the interface_run_id value
130 UPDATE igr_i_appl_int a
131 SET interface_run_id = p_interface_run_id,
132 person_id = (SELECT person_id
133 FROM igs_ad_interface
134 WHERE interface_id = a.interface_id)
135 WHERE interface_id IN (SELECT interface_id
136 FROM igs_ad_interface
137 WHERE interface_run_id = p_interface_run_id
138 AND status IN ('1','4'));
139
140 -- If record failed only due to child record failure
141 -- then set status back to 1 and nullify error code/text
142 UPDATE igr_i_appl_int
143 SET error_code = NULL,
144 error_text = NULL,
145 status = '1'
146 WHERE interface_run_id = p_interface_run_id
147 AND error_code = 'E347'
148 AND status = '4';
149
150 -- Gather statistics of the table
151 FND_STATS.GATHER_TABLE_STATS(ownname => p_schema,
152 tabname => 'IGR_I_APPL_INT',
153 cascade => TRUE);
154
155 -- Call category entity import procedure
156 igr_imp_003.process_person_inquiry (p_interface_run_id => p_interface_run_id,
157 p_source_type_id => p_source_type_id,
158 p_enable_log => p_enable_log,
159 p_rule => 'N'); -- Update not yet supported
160
161 END IF; -- g_inquiry_dtls_inc
162
163 IF g_inquiry_acad_int_inc THEN
164 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'INQUIRY_ACADEMIC_INTEREST', 8405);
165
166 IF p_enable_log = 'Y' THEN
167 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
168 p_token_name => 'TYPE_NAME',
169 p_token_value => l_meaning);
170 END IF;
171
172 -- Populating the interface table with the interface_run_id value
173 UPDATE igr_i_lines_int a
174 SET interface_run_id = p_interface_run_id,
175 (person_id,enquiry_appl_number,inquiry_date,inquiry_source_type,sales_lead_id)
176 = (SELECT person_id,enquiry_appl_number,inquiry_dt,inquiry_source_type,sales_lead_id
177 FROM igr_i_appl_int
178 WHERE interface_inq_appl_id = a.interface_inq_appl_id)
179 WHERE interface_inq_appl_id IN (SELECT interface_inq_appl_id
180 FROM igr_i_appl_int
181 WHERE interface_run_id = p_interface_run_id
182 AND status IN ('1','4'));
183
184 -- Gather statistics of the table
185 FND_STATS.GATHER_TABLE_STATS(ownname => p_schema,
186 tabname => 'IGR_I_LINES_INT',
187 cascade => TRUE);
188
189 -- Call category entity import procedure
190 igr_imp_003.process_inquiry_lines (p_interface_run_id => p_interface_run_id,
191 p_enable_log => p_enable_log,
192 p_rule => 'N'); -- Update not yet supported
193
194 END IF; -- g_inquiry_acad_int_inc
195
196 IF g_inquiry_pkg_itm_inc THEN
197 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'INQUIRY_PACKAGE_ITEMS', 8405);
198
199 IF p_enable_log = 'Y' THEN
200 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
201 p_token_name => 'TYPE_NAME',
202 p_token_value => l_meaning);
203 END IF;
204
205 -- Populating the interface table with the interface_run_id value
206 UPDATE igr_i_pkg_int a
207 SET interface_run_id = p_interface_run_id,
208 (person_id,enquiry_appl_number,sales_lead_id)
209 = (SELECT person_id,enquiry_appl_number,sales_lead_id
210 FROM igr_i_appl_int
211 WHERE interface_inq_appl_id = a.interface_inq_appl_id)
212 WHERE interface_inq_appl_id IN (SELECT interface_inq_appl_id
213 FROM igr_i_appl_int
214 WHERE interface_run_id = p_interface_run_id
215 AND status IN ('1','4'));
216
217 -- Gather statistics of the table
218 FND_STATS.GATHER_TABLE_STATS(ownname => p_schema,
219 tabname => 'IGR_I_PKG_INT',
220 cascade => TRUE);
221
222 -- Call category entity import procedure
223 igr_imp_004.prc_inq_pkg (p_interface_run_id => p_interface_run_id,
224 p_enable_log => p_enable_log,
225 p_rule => 'N'); -- Update not yet supported
226
227 END IF; -- g_inquiry_pkg_itm_inc
228
229 IF g_inquiry_info_type_inc THEN
230 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'INQUIRY_INFORMATION_TYPES', 8405);
231
232 IF p_enable_log = 'Y' THEN
233 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
234 p_token_name => 'TYPE_NAME',
235 p_token_value => l_meaning);
236 END IF;
237
238 -- Populating the interface table with the interface_run_id value
239 UPDATE igr_i_info_int a
240 SET interface_run_id = p_interface_run_id,
241 (person_id,enquiry_appl_number)
242 = (SELECT person_id,enquiry_appl_number
243 FROM igr_i_appl_int
244 WHERE interface_inq_appl_id = a.interface_inq_appl_id)
245 WHERE interface_inq_appl_id IN (SELECT interface_inq_appl_id
246 FROM igr_i_appl_int
247 WHERE interface_run_id = p_interface_run_id
248 AND status IN ('1','4'));
249
250 -- Gather statistics of the table
251 FND_STATS.GATHER_TABLE_STATS(ownname => p_schema,
252 tabname => 'IGR_I_INFO_INT',
253 cascade => TRUE);
254
255 -- Call category entity import procedure
256 igr_imp_004.prc_inq_info (p_interface_run_id => p_interface_run_id,
257 p_enable_log => p_enable_log,
258 p_rule => 'N'); -- Update not yet supported
259
260 END IF; -- g_inquiry_info_type_inc
261
262 IF g_inquiry_char_inc THEN
263 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'INQUIRY_CHARACTERISTICS', 8405);
264
265 IF p_enable_log = 'Y' THEN
266 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
267 p_token_name => 'TYPE_NAME',
268 p_token_value => l_meaning);
269 END IF;
270
271 -- Populating the interface table with the interface_run_id value
272 UPDATE igr_i_char_int a
273 SET interface_run_id = p_interface_run_id,
274 (person_id,enquiry_appl_number)
275 = (SELECT person_id,enquiry_appl_number
276 FROM igr_i_appl_int
277 WHERE interface_inq_appl_id = a.interface_inq_appl_id)
278 WHERE interface_inq_appl_id IN (SELECT interface_inq_appl_id
279 FROM igr_i_appl_int
280 WHERE interface_run_id = p_interface_run_id
281 AND status IN ('1','4'));
282
283 -- Gather statistics of the table
284 FND_STATS.GATHER_TABLE_STATS(ownname => p_schema,
285 tabname => 'IGR_I_CHAR_INT',
286 cascade => TRUE);
287
288 -- Call category entity import procedure
289 igr_imp_004.prc_inq_char (p_interface_run_id => p_interface_run_id,
290 p_enable_log => p_enable_log,
291 p_rule => 'N'); -- Update not yet supported
292
293 END IF; -- g_inquiry_char_inc
294
295 END prc_ad_category;
296
297
298 PROCEDURE del_cmpld_rct_records ( p_source_type_id IN NUMBER,
299 p_interface_run_id IN NUMBER ) AS
300 /*************************************************************
301 Created By : rbezawad
302 Date Created By : 27-Feb-05
303 Purpose : Procedure is used to delete the records from the recruitment interface tables, which are processed successfully.
304 Know limitations, enhancements or remarks
305 Change History
306 Who When What
307 (reverse chronological order - newest change first)
308 ***************************************************************/
309
310 BEGIN
311
312 IF g_inquiry_acad_int_inc THEN
313 DELETE FROM igr_i_lines_int
314 WHERE status = '1'
315 AND interface_run_id = p_interface_run_id;
316 COMMIT;
317 END IF; -- g_inquiry_acad_int_inc
318
319 IF g_inquiry_pkg_itm_inc THEN
320 DELETE FROM igr_i_pkg_int
321 WHERE status = '1'
322 AND interface_run_id = p_interface_run_id;
323 COMMIT;
324 END IF; -- g_inquiry_pkg_itm_inc
325
326 IF g_inquiry_info_type_inc THEN
327 DELETE FROM igr_i_info_int
328 WHERE status = '1'
329 AND interface_run_id = p_interface_run_id;
330 COMMIT;
331 END IF; -- g_inquiry_info_type_inc
332
333 IF g_inquiry_char_inc THEN
334 DELETE FROM igr_i_char_int
335 WHERE status = '1'
336 AND interface_run_id = p_interface_run_id;
337 COMMIT;
338 END IF; -- g_inquiry_char_inc
339
340 IF g_inquiry_dtls_inc THEN
341 DELETE FROM igr_i_appl_int
342 WHERE status = '1'
343 AND interface_run_id = p_interface_run_id;
344 COMMIT;
345 END IF; -- g_inquiry_dtls_inc
346
347 END del_cmpld_rct_records;
348
349
350 END IGR_IMP_002;
[Home] [Help]