[Home] [Help]
PACKAGE BODY: APPS.EGO_BULKLOAD_ENTITIES
Source
1 PACKAGE BODY EGO_BULKLOAD_ENTITIES AS
2 /* $Header: EGOBKUPB.pls 120.13 2007/07/12 12:14:08 rsoundar ship $ */
3
4 -------------------------------------------------------------------------
5 -- Global constants
6 -------------------------------------------------------------------------
7 G_DATA_ROWS_READY_FOR_API NUMBER;
8 G_DATA_ROWS_UPLOADED_NEW NUMBER;
9 G_ITEM_API NUMBER;
10 G_BOM_API NUMBER;
11 --
12 -- defining return statuses
13 --
14 G_RET_STS_SUCCESS VARCHAR2(1);
15 G_RET_STS_WARNING VARCHAR2(1);
16 G_RET_STS_ERROR VARCHAR2(1);
17 G_RET_STS_UNEXP_ERROR VARCHAR2(1);
18 -------------------------------------------------------------------------
19 -- Debug Profile option used to write Error_Handler.Write_Debug --
20 -- Profile option name = INV_DEBUG_TRACE ; --
21 -- User Profile Option Name = INV: Debug Trace --
22 -- Values: 1 (True) ; 0 (False) --
23 -- NOTE: This better than MRP_DEBUG which is used at many places. --
24 -------------------------------------------------------------------------
25 G_DEBUG VARCHAR2(10);
26
27 -----------------------------------------------
28 -- Write Debug statements to Concurrent Log --
29 -----------------------------------------------
30 PROCEDURE Write_Debug (p_msg IN VARCHAR2) IS
31 l_err_msg VARCHAR2(240);
32 BEGIN
33 -- If Profile set to TRUE --
34 IF (G_DEBUG = 1) THEN
35 FND_FILE.put_line(FND_FILE.LOG, p_msg);
36 END IF;
37 -- sri_debug('EGOBKUPB: '||p_msg);
38 EXCEPTION
39 WHEN OTHERS THEN
40 l_err_msg := SUBSTRB(SQLERRM, 1,240);
41 FND_FILE.put_line(FND_FILE.LOG, 'LOGGING SQL ERROR => '||l_err_msg);
42 END Write_Debug;
43
44
45 ------------------------------------------------
46 -- Defining the constants used in the program --
47 ------------------------------------------------
48 PROCEDURE SetProcessConstants IS
49 BEGIN
50 G_DATA_ROWS_READY_FOR_API := 1;
51 G_DATA_ROWS_UPLOADED_NEW := 0;
52 G_ITEM_API := 10;
53 G_BOM_API := 20;
54
55 -- G_CONC_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := '0';
56 -- G_CONC_RET_STS_WARNING CONSTANT VARCHAR2(1) := '1';
57 -- G_CONC_RET_STS_ERROR CONSTANT VARCHAR2(1) := '2';
58
59 G_RET_STS_SUCCESS := FND_API.G_RET_STS_SUCCESS;
60 G_RET_STS_WARNING := 'W';
61 G_RET_STS_ERROR := FND_API.G_RET_STS_ERROR;
62 G_RET_STS_UNEXP_ERROR := FND_API.G_RET_STS_UNEXP_ERROR;
63
64 ------------------------------------------------------------------------
65 -- Debug Profile option used to write Error_Handler.Write_Debug --
66 -- Profile option name = INV_DEBUG_TRACE ; --
67 -- User Profile Option Name = INV: Debug Trace --
68 -- Values: 1 (True) ; 0 (False) --
69 -- NOTE: This better than MRP_DEBUG which is used at many places. --
70 ------------------------------------------------------------------------
71 G_DEBUG := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
72
73 END SetProcessConstants;
74
75 PROCEDURE updateUploadedRowsToIntfTable (p_result_format_usage_id IN NUMBER)
76 IS
77 l_intf_status_tobe_process NUMBER;
78 l_intf_status_upload_done NUMBER;
79
80 BEGIN
81 l_intf_status_tobe_process := 1;
82 l_intf_status_upload_done := 99;
83 -- the bulkload line status must be changed
84 -- to uploaded to appropriate interface tables
85 UPDATE EGO_BULKLOAD_INTF
86 SET process_status = l_intf_status_upload_done
87 WHERE resultfmt_usage_id = p_result_format_usage_id
88 AND process_status = l_intf_status_tobe_process;
89 EXCEPTION
90 WHEN OTHERS THEN
91 NULL;
92 END updateUploadedRowsToIntfTable;
93 ---------------------------------------------------------------------
94 -- Main Concurrent Program API called by Excel Loaders --
95 -- Currently handles Item and BOM related Bulkload. --
96 ---------------------------------------------------------------------
97 PROCEDURE BulkLoadEntities(
98 ERRBUF OUT NOCOPY VARCHAR2,
99 RETCODE OUT NOCOPY VARCHAR2,
100 result_format_usage_id IN NUMBER,
101 user_id IN NUMBER,
102 language IN VARCHAR2,
103 resp_id IN NUMBER,
104 appl_id IN NUMBER,
105 p_start_upload IN VARCHAR2,
106 p_data_set_id IN NUMBER
107 )
108 IS
109
110 l_region_application_id NUMBER;
111 l_customization_application_id NUMBER;
112 l_region_code VARCHAR2(30);
113 Current_Error_Code VARCHAR2(20);
114 conc_status BOOLEAN;
115 l_target_api_call NUMBER;
116 l_debug VARCHAR2(80);
117 l_errbuf VARCHAR2(2000);
118 l_retcode VARCHAR2(2000);
119
120 CURSOR find_object_type( p_rf_id number) is
121 SELECT CUSTOMIZATION_APPLICATION_ID,
122 REGION_APPLICATION_ID,
123 REGION_CODE
124 FROM EGO_RESULTS_FMT_USAGES
125 WHERE RESULTFMT_USAGE_ID = p_rf_id;
126
127 ---------------------------------------------------------------------
128 -- Enable Debug BOOLEAN Value. --
129 -- Only when this value is TRUE, then write the Logging statements --
130 ---------------------------------------------------------------------
131 l_Enable_Debug BOOLEAN := FALSE;
132
133 BEGIN
134
135 SetProcessConstants();
136
137 l_region_application_id := 0;
138 l_customization_application_id := 0;
139 Current_Error_Code := NULL;
140 l_target_api_call :=0;
141
142 ----------------------------------------------------------
143 -- Enable Logging, only if Debug Profile is set to TRUE --
144 ----------------------------------------------------------
145 IF (G_DEBUG = 1) THEN
146 l_Enable_Debug := TRUE;
147 END IF;
148
149 ----------------------------------------------------------
150 -- Print the list of conc program parameters
151 ----------------------------------------------------------
152 Write_Debug('Following are the parameters to the program EGO_BULKLOAD_ENTITIES.BulkLoadEntities ');
153 Write_Debug('************************************************************');
154 Write_Debug('RESULT_FORMAT_USAGE_ID : '||to_char(result_format_usage_id));
155 Write_Debug('USER_ID : '||to_char(user_id));
156 Write_Debug('LANGUAGE : '||language);
157 Write_Debug('RESP_ID : '|| to_char(resp_id));
158 Write_Debug('APPL_ID : '||to_char(appl_id));
159 Write_Debug('************************************************************');
160 Write_Debug('Following are the other important values: ');
161 Write_Debug('Login id : '||to_char(FND_GLOBAL.login_id));
162 Write_Debug('Program Application Id : '||to_char(FND_GLOBAL.prog_appl_id));
163 Write_Debug('Concurrent Program Id : '||to_char(FND_GLOBAL.conc_program_id));
164
165 UPDATE EGO_BULKLOAD_INTF
166 SET
167 PROCESS_STATUS = G_DATA_ROWS_READY_FOR_API,
168 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
169 REQUEST_ID = FND_GLOBAL.conc_request_id,
170 PROGRAM_APPLICATION_ID = FND_GLOBAL.prog_appl_id,
171 PROGRAM_ID = FND_GLOBAL.conc_program_id
172 WHERE RESULTFMT_USAGE_ID = result_format_usage_id
173 AND process_status = G_DATA_ROWS_UPLOADED_NEW;
174
175 FOR C1 IN find_object_type(result_format_usage_id)
176 LOOP
177 l_region_application_id := C1.REGION_APPLICATION_ID;
178 l_customization_application_id := C1.CUSTOMIZATION_APPLICATION_ID;
179 l_region_code := C1.REGION_CODE;
180
181 EXIT WHEN find_object_type%NOTFOUND;
182 IF l_customization_application_id = 431
183 AND l_region_application_id = 431
184 AND substr(l_region_code,1,8) = 'EGO_ITEM' THEN
185 l_target_api_call := G_ITEM_API;
186 ELSIF l_customization_application_id = 431
187 AND l_region_application_id = 702
188 AND substr(l_region_code,1,4) = 'BOM_' THEN
189 l_target_api_call := G_BOM_API;
190 ELSE
191 Write_Debug('THE REGN CODE IS ' || l_region_code);
192 END IF;
193
194 END LOOP; --end: FOR C1 IN find_object_type(result_format_usage_id)
195
196 IF l_target_api_call = G_ITEM_API THEN
197
198 FND_FILE.PUT_LINE( FND_FILE.LOG,'Calling Item Bulkload API....');
199 ----------------------------------------------------------------------
200 -- Process Item Interface Lines
201 ----------------------------------------------------------------------
202 EGO_ITEM_BULKLOAD_PKG.process_item_interface_lines
203 (
204 p_resultfmt_usage_id => result_format_usage_id,
205 p_user_id => user_id,
206 p_language_code => language,
207 p_caller_identifier => EGO_ITEM_BULKLOAD_PKG.G_ITEM,
208 p_conc_request_id => FND_GLOBAL.conc_request_id,
209 p_start_upload => p_start_upload,
210 p_data_set_id => p_data_set_id,
211 x_errbuff => l_errbuf,
212 x_retcode => l_retcode
213 );
214
215 ELSIF l_target_api_call = G_BOM_API THEN
216
217 FND_FILE.PUT_LINE( FND_FILE.LOG,'Calling Item Bulkload API AND THEN BOM BULKLOAD....');
218 EGO_ITEM_BULKLOAD_PKG.process_item_interface_lines
219 (
220 p_resultfmt_usage_id => result_format_usage_id,
221 p_user_id => user_id,
222 p_language_code => language,
223 p_caller_identifier => EGO_ITEM_BULKLOAD_PKG.G_BOM,
224 p_conc_request_id => FND_GLOBAL.conc_request_id,
225 p_start_upload => p_start_upload,
226 p_data_set_id => p_data_set_id,
227 x_errbuff => l_errbuf,
228 x_retcode => l_retcode
229 );
230 ---------------------------------------------------------------------
231 -- Return CODE FOR ITEM IF ANY
232 ---------------------------------------------------------------------
233 Write_Debug('ITEM ERRORS IF ANY ARE CODE='|| l_retcode);
234 Write_Debug('ITEM ERRORS IF ANY ARE BUFFER='|| l_errbuf);
235
236 IF NVL(l_retcode ,G_RET_STS_SUCCESS) NOT IN
237 ( G_RET_STS_SUCCESS
238 , G_RET_STS_WARNING
239 ) THEN
240 updateUploadedRowsToIntfTable (p_result_format_usage_id => result_format_usage_id);
241 ERRBUF := l_errbuf;
242 RETCODE := l_retcode;
243 RETURN;
244 END IF;
245
246 ---------------------------------------------------------------------
247 -- Setting PROCESS_STATUS to 1 Prior to Calling BOM APIs
248 ---------------------------------------------------------------------
249 UPDATE EGO_BULKLOAD_INTF
250 SET PROCESS_STATUS = 1
251 WHERE RESULTFMT_USAGE_ID = result_format_usage_id;
252
253 COMMIT;
254
255 /*
256 SNELLOLI: CALL THE BOM API
257 after setting the process status to 1 again
258 has to check for errors later
259 UPDATE EGO_BULKLOAD_INTF SET PROCESS_STATUS = 1
260 WHERE RESULTFMT_USAGE_ID = result_format_usage_id;
261 commit;
262 */
263 FND_FILE.PUT_LINE( FND_FILE.LOG,'Calling BOM BULKLOAD AFTER UPDATION');
264 Bom_import_pub.Process_Structure_Data
265 ( p_batch_id => p_data_set_id,
266 p_resultfmt_usage_id => result_format_usage_id,
267 p_user_id => user_id,
268 p_conc_request_id => FND_GLOBAL.conc_request_id,
269 p_language_code => language,
270 p_start_upload => FND_API.G_FALSE, -- always send FALSE here
271 x_errbuff => l_errbuf,
272 x_retcode => l_retcode
273 );
274
275 END IF; --end: IF l_target_api_call = G_ITEM_API THEN
276
277 -------------------------------------------------------------------------
278 -- Setting the last values for l_errbuf, l_retcode to the Concurrent
279 -- Program return values. These last values carry the Latest status
280 -- of the Concurrent Program.
281 -------------------------------------------------------------------------
282 updateUploadedRowsToIntfTable (p_result_format_usage_id => result_format_usage_id);
283 ERRBUF := NVL(l_errbuf, G_RET_STS_SUCCESS);
284 RETCODE := l_retcode;
285
286 Write_Debug('EGO_BULKLOAD_ENTITIES.BulkLoadEntities RETCODE => '|| RETCODE);
287 Write_Debug('EGO_BULKLOAD_ENTITIES.BulkLoadEntities ERRBUF => '|| ERRBUF);
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 FND_FILE.PUT_LINE(FND_FILE.LOG,'Others '||SQLCODE || ':'||SQLERRM);
292 RETCODE := G_RET_STS_ERROR;
293 Current_Error_Code := To_Char(SQLCODE);
294 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', Current_Error_Code);
295
296 END BulkLoadEntities;
297
298
299
300 /*
301 * This Procedure is called from Excel Import
302 * it will launch the concurrent program and call API to update
303 * the request id into ego_import_batches_b table
304 */
305 PROCEDURE Run_Import_Program(
306 p_resultfmt_usage_id IN NUMBER,
307 p_user_id IN NUMBER,
308 p_language IN VARCHAR2,
309 p_resp_id IN NUMBER,
310 p_appl_id IN NUMBER,
311 p_run_from IN VARCHAR2,
312 p_create_new_batch IN VARCHAR2,
313 p_batch_id IN NUMBER,
314 p_batch_name IN VARCHAR2,
315 p_auto_imp_on_data_load IN VARCHAR2,
316 p_auto_match_on_data_load IN VARCHAR2,
317 p_change_order_option IN VARCHAR2,
318 p_add_all_items_to_CO IN VARCHAR2,
319 p_change_order_category IN VARCHAR2,
320 p_change_order_type IN VARCHAR2,
321 p_change_order_name IN VARCHAR2,
322 p_change_order_number IN VARCHAR2,
323 p_change_order_desc IN VARCHAR2,
324 p_schedule_date IN DATE,
325 p_nir_option IN VARCHAR2,
326 x_request_id OUT NOCOPY NUMBER)
327 IS
328 l_request_id NUMBER;
329 l_imp_request_id NUMBER;
330 l_match_request_id NUMBER;
331 BEGIN
332 Write_Debug('EGO_BULKLOAD_ENTITIES.Run_Import_Program: p_schedule_date: ' || p_schedule_date);
333 Write_Debug('EGO_BULKLOAD_ENTITIES.Run_Import_Program: formatted date - p_schedule_date: ' || to_char(p_schedule_date, 'YYYY/MM/DD HH24:MI:SS'));
334 l_request_id := FND_REQUEST.Submit_Request
335 (
336 application => 'EGO'
337 , program => 'EGOIJAVA'
338 , argument1 => p_resultfmt_usage_id
339 , argument2 => p_user_id
340 , argument3 => p_language
341 , argument4 => p_resp_id
342 , argument5 => p_appl_id
343 , argument6 => p_run_from
344 , argument7 => p_create_new_batch
345 , argument8 => p_batch_id
346 , argument9 => p_batch_name
347 , argument10 => p_auto_imp_on_data_load
348 , argument11 => p_auto_match_on_data_load
349 , argument12 => p_change_order_option
350 , argument13 => p_add_all_items_to_CO
351 , argument14 => p_change_order_category
352 , argument15 => p_change_order_type
353 , argument16 => p_change_order_name
354 , argument17 => p_change_order_number
355 , argument18 => p_change_order_desc
356 , argument19 => to_char(p_schedule_date, 'YYYY/MM/DD HH24:MI:SS')
357 , argument20 => NULL
358 , argument21 => p_nir_option
359 );
360
361 x_request_id := l_request_id;
362
363 IF l_request_id > 0 THEN
364 IF p_auto_imp_on_data_load = 'Y' THEN
365 l_imp_request_id := l_request_id;
366 END IF;
367 IF p_auto_match_on_data_load = 'Y' THEN
368 l_match_request_id := l_request_id;
369 END IF;
370
371 EGO_IMPORT_PVT.Update_Request_Id_To_Batch(
372 p_import_request_id => l_imp_request_id,
373 p_match_request_id => l_match_request_id,
374 p_batch_id => p_batch_id);
375 END IF; --IF l_request_id > 0 THEN
376 COMMIT;
377 END Run_Import_Program;
378
379 END ;