[Home] [Help]
PACKAGE BODY: APPS.HR_DU_MASTER
Source
1 PACKAGE BODY hr_du_master AS
2 /* $Header: perdumas.pkb 115.13 2002/11/28 16:01:15 apholt noship $ */
3
4
5 /*-------------------------- PRIVATE ROUTINES ----------------------------*/
6
7
8 -- -------------------------- create_upload -------------------------------
9 -- Description: Create the entry in hr_du_uploads for the migration
10 --
11 --
12 --
13 -- Input Parameters
14 -- r_upload_data - upload record
15 --
16 --
17 -- Output Parameters
18 -- r_upload_data - upload record
19 --
20 --
21 --
22 -- ------------------------------------------------------------------------
23
24 --
25 PROCEDURE create_upload(r_upload_data IN OUT NOCOPY r_upload_rec) IS
26 --
27
28 CURSOR csr_module IS
29 SELECT module_id
30 FROM hr_du_modules
31 WHERE module_name = 'Spreadsheet-to-lines';
32
33 CURSOR csr_upload IS
34 SELECT hr_du_uploads_s.currval
35 FROM dual;
36
37 l_module_id NUMBER;
38
39 --
40 BEGIN
41 --
42
43 hr_du_utility.message('ROUT','entry:hr_du_master.create_upload', 5);
44 hr_du_utility.message('PARA','(r_upload_data - record)', 10);
45
46 -- get the module id for the spreedsheet input module
47 OPEN csr_module;
48 FETCH csr_module INTO l_module_id;
49 CLOSE csr_module;
50
51 hr_du_utility.message('INFO','performing insert', 15);
52
53 INSERT INTO hr_du_uploads (
54 UPLOAD_ID,
55 MODULE_ID,
56 BATCH_ID,
57 SOURCE,
58 STATUS,
59 DATA_INPUT_STATUS,
60 DATA_REFERENCE_COMPLETE,
61 BUSINESS_GROUP_NAME,
62 DO_REQUEST_ID,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_LOGIN,
66 CREATED_BY,
67 CREATION_DATE
68 )
69 SELECT
70 hr_du_uploads_s.nextval,
71 l_module_id,
72 null,
73 r_upload_data.filename,
74 'NS',
75 'NS',
76 null,
77 null,
78 FND_GLOBAL.CONC_REQUEST_ID,
79 sysdate,
80 1,
81 1,
82 1,
83 sysdate
84 FROM sys.dual;
85
86 COMMIT;
87
88
89 -- get the upload id
90 OPEN csr_upload;
91 FETCH csr_upload INTO r_upload_data.upload_id;
92 CLOSE csr_upload;
93
94
95 hr_du_utility.message('INFO','created upload', 15);
96 hr_du_utility.message('SUMM','created upload', 20);
97 hr_du_utility.message('ROUT','exit:hr_du_master.create_upload', 25);
98 hr_du_utility.message('PARA','(none)', 30);
99
100
101 -- error handling
102 EXCEPTION
103 WHEN OTHERS THEN
104 -- update status to error
105 hr_du_utility.update_uploads(p_new_status => 'E',
106 p_id => r_upload_data.upload_id);
107 hr_du_utility.error(SQLCODE,'hr_du_master.create_upload','(none)','R');
108 RAISE;
109
110 --
111 END create_upload;
112 --
113
114
115
116 /*---------------------------- PUBLIC ROUTINES --------------------------*/
117
118 -- ------------------------------- main -----------------------------------
119 -- Description: This is the main controller code which is called from the
120 -- UI. It in turn calls the uploader module required.
121 --
122 --
123 --
124 -- Input Parameters
125 -- p_filename - of current upload
126 --
127 --
128 -- Output Parameters
129 -- errbuf - buffer for output message (for CM manager)
130 --
131 -- retcode - program return code (for CM manager)
132 --
133 --
134 -- ------------------------------------------------------------------------
135
136 --
137 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
138 retcode OUT NOCOPY NUMBER,
139 p_filename IN VARCHAR2,
140 p_login VARCHAR2 DEFAULT 'Y') IS
141 --
142
143 e_fatal_error EXCEPTION;
144 l_process VARCHAR2(1000) := 'initialization';
145 r_upload_data r_upload_rec;
146
147
148 --
149 BEGIN
150 --
151
152 IF p_login = 'Y' THEN
153 -- initialize messaging
154 hr_du_utility.message_init;
155 END IF;
156
157 hr_du_utility.message('ROUT','entry:hr_du_master.main', 5);
158 hr_du_utility.message('PARA','(p_filename - ' ||
159 p_filename || ')', 10);
160
161
162 -- update record with filename
163 r_upload_data.filename := p_filename;
164
165 -- create the entry in hr_du_uploads
166 create_upload(r_upload_data);
167
168
169 --From the spread sheet to the HR_DU_UPLOAD_LINES
170 l_process := 'spreadsheet input - rollback';
171 hr_du_utility.message('INFO',l_process, 15);
172 hr_du_di_insert.rollback(r_upload_data.upload_id);
173
174 l_process := 'spreadsheet input - process';
175 hr_du_utility.message('INFO',l_process, 15);
176 hr_du_di_insert.ordered_sequence(r_upload_data.upload_id);
177
178
179
180 -- For the PC to CP conversion
181 l_process := 'PC to CP conversion - rollback';
182 hr_du_utility.message('INFO',l_process, 15);
183 hr_du_dp_pc_conversion.ROLLBACK(r_upload_data.upload_id);
184
185 l_process := 'PC to CP conversion - validate';
186 hr_du_utility.message('INFO',l_process, 15);
187 hr_du_dp_pc_conversion.VALIDATE(r_upload_data.upload_id);
188
189 l_process := 'PC to CP conversion - process';
190 hr_du_utility.message('INFO',l_process, 15);
191 hr_du_dp_pc_conversion.INSERT_API_MODULE_IDS(r_upload_data.upload_id);
192
193
194
195
196 -- To Data Pump HR_PUMP_BATCH_LINES
197 l_process := 'transfer to datapump tables - rollback';
198 hr_du_utility.message('INFO',l_process, 15);
199 hr_du_do_datapump.ROLLBACK(r_upload_data.upload_id);
200
201 l_process := 'transfer to datapump tables - validate';
202 hr_du_utility.message('INFO',l_process, 15);
203 hr_du_do_datapump.VALIDATE(r_upload_data.upload_id);
204
205 l_process := 'transfer to datapump tables - process';
206 hr_du_utility.message('INFO',l_process, 15);
207 hr_du_do_datapump.MAIN(r_upload_data.upload_id);
208
209
210
211 -- set up return values to concurrent manager
212 retcode := 0;
213 errbuf := 'No errors - examine logfiles for detailed reports.';
214
215
216 hr_du_utility.message('INFO','Main controller', 15);
217 hr_du_utility.message('SUMM','Main controller', 20);
218 hr_du_utility.message('ROUT','exit:hr_du_master.main', 25);
219 hr_du_utility.message('PARA','(retcode - ' || retcode ||
220 ')(errbuf - ' || errbuf || ')', 30);
221
222
223 -- error handling
224 EXCEPTION
225 WHEN e_fatal_error THEN
226 retcode := 2;
227 errbuf := 'An error occurred during the upload - examine logfiles' ||
228 ' for detailed reports. Current process is ' ||
229 l_process || '.';
230 hr_du_utility.error(SQLCODE,'hr_du_master.main',
231 l_process,'R');
232 hr_du_utility.update_uploads(p_new_status => 'E',p_id => r_upload_data.upload_id);
233 WHEN OTHERS THEN
234 retcode := 2;
235 errbuf := 'An error occurred during the upload - examine logfiles' ||
236 ' for detailed reports. Current process is ' ||
237 l_process || '.';
238 hr_du_utility.error(SQLCODE,'hr_du_master.main','(none)','R');
239 hr_du_utility.update_uploads(p_new_status => 'E',p_id => r_upload_data.upload_id);
240
241 --
242 END main;
243 --
244
245
246
247 end hr_du_master;