1 PACKAGE BODY igs_uc_export_ucas_pkg AS
2 /* $Header: IGSUC27B.pls 120.5 2006/02/08 19:53:02 anwest ship $ */
3 l_abbrev_use VARCHAR2(1) DEFAULT NULL;
4 l_rowid VARCHAR2(26) DEFAULT NULL;
5
6
7
8 PROCEDURE export_data(Errbuf OUT NOCOPY VARCHAR2,
9 Retcode OUT NOCOPY NUMBER,
10 p_contact IN VARCHAR2,
11 p_program_details IN VARCHAR2,
12 p_keywords IN VARCHAR2,
13 p_abbreviations IN VARCHAR2,
14 p_ucas_transactions IN VARCHAR2,
15 p_gttr_transactions IN VARCHAR2,
16 p_nmas_transactions IN VARCHAR2) IS
17 /********************************************************************************
18 Created By : vbandaru
19 Date Created By : 23-JAN-2002
20 Purpose :
21
22 Know limitations, enhancements or remarks
23 Change History
24 Who When What
25 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
26 jbaber 16-Aug-05 Modified for UC307 - HERCULES Small Systems Support
27 jbaber 12-Jul-05 Modified for UC315 - UCAS Support 2006
28 Replaced reference to igs_uc_cvcontrol_2003_v with igs_uc_ucas_control
29 Removed references to export_inst_conts, export_inst_cnt_grp, export_crse_dets,
30 export_crse_vac_ops, export_crse_keywrds and export_offer_abbrev as these are no
31 longer supported by UCAS as updateable views.
32 pmarada 03-Jul-03 Modified as per the UCFD203 build. bug 2669208
33
34 (reverse chronological order - newest change first)
35 ********************************************************************************/
36 CURSOR cur_cycle IS
37 SELECT MAX(current_cycle) current_cycle , MAX(configured_cycle) configured_cycle
38 FROM igs_uc_defaults ;
39
40 cur_cycle_rec cur_cycle%ROWTYPE;
41
42 CURSOR cur_control (cp_system_code igs_uc_ucas_control.system_code%TYPE) IS
43 SELECT TO_NUMBER(LPAD(entry_year,4,200))
44 FROM igs_uc_ucas_control
45 WHERE system_code = cp_system_code
46 AND ucas_cycle = cur_cycle_rec.configured_cycle;
47
48
49 l_entry_year igs_uc_defaults.configured_cycle%TYPE;
50
51 CURSOR cur_interface(cp_system_code igs_uc_ucas_control.system_code%TYPE) IS
52 SELECT ucas_interface FROM igs_uc_cyc_defaults
53 WHERE ucas_cycle = cur_cycle_rec.configured_cycle
54 AND system_code = cp_system_code;
55 l_interface igs_uc_cyc_defaults.ucas_interface%TYPE;
56
57 validate_cycle BOOLEAN := TRUE;
58
59 BEGIN
60
61 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
62 IGS_GE_GEN_003.SET_ORG_ID;
63
64 -- Get current and configured cycle
65 OPEN cur_cycle;
66 FETCH cur_cycle INTO cur_cycle_rec;
67 CLOSE cur_cycle;
68
69 IF cur_cycle_rec.configured_cycle IS NULL OR cur_cycle_rec.current_cycle IS NULL THEN
70 fnd_message.set_name('IGS','IGS_UC_CYCLE_NOT_FOUND');
71 errbuf := fnd_message.get;
72 fnd_file.put_line(fnd_file.log, errbuf);
73 retcode := 2 ;
74 RETURN ;
75 END IF ;
76
77 -- Validate FTUG current and configured cycle
78 IF (p_ucas_transactions = 'Y') THEN
79 OPEN cur_control('U');
80 FETCH cur_control INTO l_entry_year;
81 CLOSE cur_control;
82 -- If current cycle and configured cycle are same then write the transaction else log the message
83 IF NVL(l_entry_year,0) <> cur_cycle_rec.configured_cycle THEN
84 fnd_message.set_name('IGS','IGS_UC_CYCLES_NOT_SYNC');
85 fnd_message.set_token('UCAS_CYCLE',cur_cycle_rec.configured_cycle);
86 fnd_message.set_token('HERC_CYCLE',l_entry_year);
87 fnd_message.set_token('SYSTEM_CODE','UCAS');
88 fnd_file.put_line(fnd_file.log, fnd_message.get);
89 errbuf := fnd_message.get ;
90 validate_cycle := FALSE;
91 END IF;
92 END IF;
93
94 -- Validate GTTR current and configured cycle
95 IF (p_gttr_transactions = 'Y') THEN
96 OPEN cur_control('G');
97 FETCH cur_control INTO l_entry_year;
98 CLOSE cur_control;
99 -- If current cycle and configured cycle are same then write the transaction else log the message
100 IF NVL(l_entry_year,0) <> cur_cycle_rec.configured_cycle THEN
101 fnd_message.set_name('IGS','IGS_UC_CYCLES_NOT_SYNC');
102 fnd_message.set_token('UCAS_CYCLE',cur_cycle_rec.configured_cycle);
103 fnd_message.set_token('HERC_CYCLE',l_entry_year);
104 fnd_message.set_token('SYSTEM_CODE','GTTR');
105 fnd_file.put_line(fnd_file.log, fnd_message.get);
106 errbuf := fnd_message.get ;
107 validate_cycle := FALSE;
108 END IF;
109 END IF;
110
111 -- Validate NMAS current and configured cycle
112 IF (p_nmas_transactions = 'Y') THEN
113 OPEN cur_control('N');
114 FETCH cur_control INTO l_entry_year;
115 CLOSE cur_control;
116 -- If current cycle and configured cycle are same then write the transaction else log the message
117 IF NVL(l_entry_year,0) <> cur_cycle_rec.configured_cycle THEN
118 fnd_message.set_name('IGS','IGS_UC_CYCLES_NOT_SYNC');
119 fnd_message.set_token('UCAS_CYCLE',cur_cycle_rec.configured_cycle);
120 fnd_message.set_token('HERC_CYCLE',l_entry_year);
121 fnd_message.set_token('SYSTEM_CODE','NMAS');
122 fnd_file.put_line(fnd_file.log, fnd_message.get);
123 errbuf := fnd_message.get ;
124 validate_cycle := FALSE;
125 END IF;
126 END IF;
127
128 -- If any of the validations failed then exit process
129 IF NOT validate_cycle THEN
130 retcode := 2;
131 RETURN;
132 END IF;
133
134 -- Export FTUG transactions if required
135 IF (p_ucas_transactions = 'Y') THEN
136 -- Make sure interface is set to H
137 OPEN cur_interface ('U');
138 FETCH cur_interface INTO l_interface;
139 CLOSE cur_interface;
140 IF l_interface = 'H' THEN
141 --Exporting Transactions
142 fnd_file.put_line(fnd_file.LOG, ' ');
143 fnd_message.set_name( 'IGS','IGS_UC_EXP_TRANSACTIONS');
144 fnd_message.set_token('SYSTEM_CODE','UCAS');
145 fnd_file.put_line (FND_FILE.LOG,fnd_message.get);
146 igs_uc_tran_processor_pkg.trans_write('U',Errbuf,Retcode);
147 ELSE
148 -- FTUG interface is MARVIN so log warning.
149 fnd_message.set_name('IGS','IGS_UC_MARVIN_INTERFACE');
150 fnd_message.set_token('SYSTEM_CODE','UCAS');
151 fnd_message.set_token('PROCESS','export');
152 fnd_file.put_line(fnd_file.log,fnd_message.get );
153 retcode := 1;
154 END IF;
155 END IF;
156
157 -- Export GTTR transactions if required
158 IF (p_gttr_transactions = 'Y') THEN
159 -- Make sure interface is set to H
160 OPEN cur_interface ('G');
161 FETCH cur_interface INTO l_interface;
162 CLOSE cur_interface;
163 IF l_interface = 'H' THEN
164 --Exporting Transactions
165 fnd_file.put_line(fnd_file.LOG, ' ');
166 fnd_message.set_name( 'IGS','IGS_UC_EXP_TRANSACTIONS');
167 fnd_message.set_token('SYSTEM_CODE','GTTR');
168 fnd_file.put_line (FND_FILE.LOG,fnd_message.get);
169 igs_uc_tran_processor_pkg.trans_write('G',Errbuf,Retcode);
170 ELSE
171 -- GTTR interface is MARVIN so log warning.
172 fnd_message.set_name('IGS','IGS_UC_MARVIN_INTERFACE');
173 fnd_message.set_token('SYSTEM_CODE','GTTR');
174 fnd_message.set_token('PROCESS','export');
175 fnd_file.put_line(fnd_file.log,fnd_message.get);
176 retcode := 1;
177 END IF;
178 END IF;
179
180 -- Export NMAS transactions if required
181 IF (p_nmas_transactions = 'Y') THEN
182 -- Make sure interface is set to H
183 OPEN cur_interface ('N');
184 FETCH cur_interface INTO l_interface;
185 CLOSE cur_interface;
186 IF l_interface = 'H' THEN
187 --Exporting Transactions
188 fnd_file.put_line(fnd_file.LOG, ' ');
189 fnd_message.set_name( 'IGS','IGS_UC_EXP_TRANSACTIONS');
190 fnd_message.set_token('SYSTEM_CODE','NMAS');
191 fnd_file.put_line (FND_FILE.LOG,fnd_message.get);
192 igs_uc_tran_processor_pkg.trans_write('N',Errbuf,Retcode);
193 ELSE
194 -- NMAS interface is MARVIN so log warning.
195 fnd_message.set_name('IGS','IGS_UC_MARVIN_INTERFACE');
196 fnd_message.set_token('SYSTEM_CODE','NMAS');
197 fnd_message.set_token('PROCESS','export');
198 fnd_file.put_line(fnd_file.log,fnd_message.get );
199 retcode := 1;
200 END IF;
201 END IF;
202
203 COMMIT;
204
205 EXCEPTION
206
207 WHEN OTHERS THEN
208 ROLLBACK;
209 retcode := 2;
210 fnd_message.set_name( 'IGS','IGS_GE_UNHANDLED_EXP');
211 fnd_message.set_token('NAME','IGS_UC_EXPORT_UCAS_PKG.EXPORT_DATA'||' - '||SQLERRM);
212 errbuf := fnd_message.get;
213 igs_ge_msg_stack.conc_exception_hndl;
214
215 END export_data;
216
217 END igs_uc_export_ucas_pkg;