[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_OPEN_INTERFACE_PVT
Source
1 PACKAGE BODY EGO_ITEM_OPEN_INTERFACE_PVT AS
2 /* $Header: EGOPOPIB.pls 120.45.12020000.2 2012/08/28 03:05:46 lanhuang ship $ */
3
4 G_SUCCESS CONSTANT NUMBER := 0;
5 G_WARNING CONSTANT NUMBER := 1;
6 G_ERROR CONSTANT NUMBER := 2;
7
8 PROCEDURE item_open_interface_process(
9 ERRBUF OUT NOCOPY VARCHAR2
10 ,RETCODE OUT NOCOPY VARCHAR2
11 ,p_org_id IN NUMBER
12 ,p_all_org IN NUMBER := 1
13 ,p_val_item_flag IN NUMBER := 1
14 ,p_pro_item_flag IN NUMBER := 1
15 ,p_del_rec_flag IN NUMBER := 1
16 ,p_xset_id IN NUMBER := -999
17 ,p_run_mode IN NUMBER := 1
18 ,p_prog_appid IN NUMBER := -1
19 ,p_prog_id IN NUMBER := -1
20 ,p_request_id IN NUMBER := -1
21 ,p_user_id IN NUMBER := -1
22 ,p_login_id IN NUMBER := -1
23 ,p_commit_flag IN NUMBER := 1
24 ,p_default_flag IN NUMBER DEFAULT 1) IS
25
26 l_retcode VARCHAR2(100);
27 l_source_system_id EGO_IMPORT_BATCHES_B.source_system_id%TYPE;
28 l_import_xref_only EGO_IMPORT_OPTION_SETS.import_xref_only%TYPE;
29 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
30 l_request_id NUMBER;
31 l_pro_flag_3 NUMBER;
32 l_enabled_for_data_pool VARCHAR2(1);
33 l_item_interface_rec EGO_IMPORT_USER_HOOKS.ITEM_INTERFACE_REC;
34
35 BEGIN
36 INV_EGO_REVISION_VALIDATE.Set_Process_Control('EGO_ITEM_BULKLOAD');
37 RETCODE := G_SUCCESS;
38
39 BEGIN
40 SELECT batch.source_system_id, NVL(opt.import_xref_only,'N'), NVL(opt.ENABLED_FOR_DATA_POOL, 'N')
41 INTO l_source_system_id, l_import_xref_only, l_enabled_for_data_pool
42 FROM ego_import_batches_b batch
43 ,ego_import_option_sets opt
44 WHERE batch.batch_id = p_xset_id
45 AND batch.batch_id = opt.batch_id;
46 EXCEPTION
47 WHEN OTHERS THEN
48 l_source_system_id := EGO_IMPORT_PVT.get_pdh_source_system_id;
49 l_import_xref_only := 'N';
50 l_enabled_for_data_pool := 'N';
51 END;
52
53 IF l_source_system_id <> EGO_IMPORT_PVT.get_pdh_source_system_id AND l_import_xref_only = 'Y' THEN
54 IF l_inv_debug_level IN(101, 102) THEN
55 INVPUTLI.info('Calling EGO_IMPORT_PVT.Process_SSXref_Intf_Rows');
56 END IF;
57 EGO_IMPORT_PVT.Process_SSXref_Intf_Rows(
58 ERRBUF => ERRBUF
59 ,RETCODE => l_retcode
60 ,p_data_set_id => p_xset_id);
61
62 IF l_inv_debug_level IN(101, 102) THEN
63 INVPUTLI.info('Returned EGO_IMPORT_PVT.Process_SSXref_Intf_Rows '||l_retcode);
64 INVPUTLI.info(ERRBUF);
65 END IF;
66 -- Bug: 5565750
67 IF ( p_commit_flag = 1 AND NVL(l_retcode, 0) IN (0, 1) ) THEN
68 IF l_inv_debug_level IN(101, 102) THEN
69 INVPUTLI.info('EGO_IMPORT_PVT.Process_SSXref_Intf_Rows => COMMITING');
70 INVPUTLI.info(ERRBUF);
71 END IF;
72 COMMIT;
73 END IF;
74 RETCODE := l_retcode;
75 ELSE --l_source_system_id <> EGO_IMPORT_PVT.get_pdh_source_system_id AND l_import_xref_only = 'Y' THEN
76 --Adding this If condition as
77 --Java concurrent program calls EGOPOPIB as PL/SQL
78 --routine and not a concurrent request and passes
79 --request id as parameter
80 IF p_request_id = -1 OR p_request_id IS NULL THEN
81 l_request_id := fnd_global.conc_request_id;
82 ELSE
83 l_request_id := p_request_id;
84 END IF;
85
86 -- Bug#8833123
87 -- Calling the Item Import User Hooks
88 l_item_interface_rec.org_id := p_org_id;
89 l_item_interface_rec.set_process_id := p_xset_id;
90 l_item_interface_rec.request_id := l_request_id;
91 l_item_interface_rec.commit_flag := p_commit_flag;
92
93 EGO_IMPORT_USER_HOOKS.Default_LC_and_Item_Status
94 (ERRBUF => ERRBUF,
95 RETCODE => l_retcode,
96 p_item_interface_rec => l_item_interface_rec
97 );
98
99 IF l_retcode NOT IN (0, 1) THEN
100 RETCODE := G_ERROR;
101 END IF;
102
103 -- IF batch is enabled for data pool, then calling IOI in validation mode
104 -- and then calling Validate_Timestamp_In_Batch i.e. phase-2 validation
105 IF l_enabled_for_data_pool = 'Y' AND NVL(p_pro_item_flag, 0) = 1 THEN
106 IF l_inv_debug_level IN(101, 102) THEN
107 INVPUTLI.info('Calling INVPOPIF.inopinp_open_interface_process in validation mode - run mode -> '||p_run_mode);
108 END IF;
109 l_retcode := INVPOPIF.inopinp_open_interface_process(
110 org_id => p_org_id
111 ,all_org => p_all_org
112 ,val_item_flag => 1
113 ,pro_item_flag => 2
114 ,del_rec_flag => 2
115 ,prog_appid => p_prog_appid
116 ,prog_id => fnd_global.conc_program_id
117 ,request_id => l_request_id
118 ,user_id => p_user_id
119 ,login_id => fnd_global.conc_login_id
120 ,xset_id => p_xset_id
121 ,commit_flag => p_commit_flag
122 ,run_mode => p_run_mode
123 ,err_text => ERRBUF);
124
125 IF l_inv_debug_level IN(101, 102) THEN
126 INVPUTLI.info(' RETCODE for INVPOPIF.inopinp_open_interface_process in validation mode - run mode -> '||p_run_mode ||':'||l_retcode);
127 INVPUTLI.info(ERRBUF);
128 END IF;
129
130 IF l_retcode NOT IN (0, 1) THEN
131 RETCODE := G_ERROR;
132 END IF;
133
134 IF l_inv_debug_level IN(101, 102) THEN
135 INVPUTLI.info('Calling EGO_IMPORT_UTIL_PVT.Validate_Timestamp_In_Batch');
136 END IF;
137
138 EGO_IMPORT_UTIL_PVT.Validate_Timestamp_In_Batch(
139 RETCODE => l_retcode
140 ,ERRBUF => ERRBUF
141 ,p_batch_id => p_xset_id);
142
143 IF l_inv_debug_level IN(101, 102) THEN
144 INVPUTLI.info(' RETCODE for EGO_IMPORT_UTIL_PVT.Validate_Timestamp_In_Batch - '||l_retcode);
145 INVPUTLI.info(ERRBUF);
146 END IF;
147
148 IF l_retcode NOT IN (0, 1) THEN
149 RETCODE := G_ERROR;
150 END IF;
151 END IF; --IF l_enabled_for_data_pool = 'Y' THEN
152
153 -- Call INV Item open interface.
154 -- Note: Categories prg is called from INV IOI
155 IF l_inv_debug_level IN(101, 102) THEN
156 INVPUTLI.info('Calling INVPOPIF.inopinp_open_interface_process - run mode -> '||p_run_mode);
157 END IF;
158 l_retcode := INVPOPIF.inopinp_open_interface_process(
159 org_id => p_org_id
160 ,all_org => p_all_org
161 ,val_item_flag => p_val_item_flag
162 ,pro_item_flag => p_pro_item_flag
163 ,del_rec_flag => p_del_rec_flag
164 ,prog_appid => p_prog_appid
165 ,prog_id => fnd_global.conc_program_id
166 ,request_id => l_request_id
167 ,user_id => p_user_id
168 ,login_id => fnd_global.conc_login_id
169 ,xset_id => p_xset_id
170 ,default_flag => p_default_flag
171 ,commit_flag => p_commit_flag
172 ,run_mode => p_run_mode
173 ,err_text => ERRBUF);
174
175 IF l_inv_debug_level IN(101, 102) THEN
176 INVPUTLI.info(' RETCODE for INVPOPIF.inopinp_open_interface_process - run mode -> '||p_run_mode ||':'||l_retcode);
177 INVPUTLI.info(ERRBUF);
178 END IF;
179
180 IF l_retcode NOT IN (0, 1) THEN
181 /* Bug 5257590 - Checking for run time exceptions so status can be set to ERROR */
182 RETCODE := G_ERROR;
183 ELSE
184 -- LANHUANG - Bug fix 14500090 on 27AUG2012
185 -- cases of l_retcode = 1 have to be reported, as warning
186 IF l_retcode =1 THEN
187 RETCODE := G_WARNING;
188 END IF;
189
190 /* Bug 5257590 - Checking for validation errors so status can be set to WARNING */
191 -- Bug: 5529588 - performance issue. Re-writing sql.
192 BEGIN
193 SELECT 1 INTO l_pro_flag_3
194 FROM mtl_system_items_interface
195 WHERE process_flag = 3
196 AND request_id = l_request_id
197 AND set_process_id = p_xset_id
198 AND rownum = 1;
199 EXCEPTION WHEN NO_DATA_FOUND THEN
200 l_pro_flag_3 := 0;
201 END;
202
203 /* bug 12603272 if no row in mtl_system_items_interface marks as error status, check mtl_item_revisions_interface */
204 IF l_inv_debug_level IN(101, 102) THEN
205 INVPUTLI.info('l_pro_flag_3 from table mtl_system_items_interface ' || l_pro_flag_3 );
206 END IF;
207
208 if l_pro_flag_3 = 0 then
209 SELECT count(*) INTO l_pro_flag_3
210 FROM mtl_item_revisions_interface
211 WHERE process_flag = 3
212 AND request_id = l_request_id
213 AND rownum = 1;
214 IF l_inv_debug_level IN(101, 102) THEN
215 INVPUTLI.info('l_pro_flag_3 from table mtl_item_revisions_interface ' || l_pro_flag_3 );
216 END IF;
217 end if ;
218
219 IF l_pro_flag_3 > 0 THEN
220 IF l_inv_debug_level IN(101, 102) THEN
221 INVPUTLI.info('Validation errors occured during Import Items from EGO_ITEM_OPEN_INTERFACE_PVT.item_open_interface_process' );
222 END IF;
223 ERRBUF := 'Validation errors occured during Import Item';
224 RETCODE := G_WARNING;
225
226 -- LANHUANG - Bug fix 14500090 on 27AUG2012
227 -- commenting out following 2 code lines because RECODE is set as G_SUCCESS
228 -- by default, having it assigned again would overwrite values
229 -- previously set
230 -- ELSE
231 -- RETCODE := G_SUCCESS;
232 END IF;
233
234 END IF; --IF l_retcode NOT IN (0, 1) THEN
235 END IF; -- Xref import only
236
237 INV_EGO_REVISION_VALIDATE.Set_Process_Control(NULL);
238 --Now returning the highest status recieved and stored in l_retcode
239 EXCEPTION
240 WHEN OTHERS THEN
241 INV_EGO_REVISION_VALIDATE.Set_Process_Control(NULL);
242 IF l_inv_debug_level IN(101, 102) THEN
243 INVPUTLI.info('WHEN-OTHERS-EXCEPTION item_open_interface_process: ' ||SQLCODE);
244 INVPUTLI.info(SQLERRM);
245 END IF;
246 ERRBUF := 'Unexpected error in item_open_interface_process: '||SQLERRM;
247 RETCODE := G_ERROR;
248 END item_open_interface_process;
249
250 --4717744 : All item entities in a new prg
251 PROCEDURE process_item_entities(
252 ERRBUF OUT NOCOPY VARCHAR2
253 ,RETCODE OUT NOCOPY VARCHAR2
254 ,p_del_rec_flag IN NUMBER := 1
255 ,p_xset_id IN NUMBER := -999
256 ,p_request_id IN NUMBER := -1
257 ,p_call_uda_process IN BOOLEAN DEFAULT TRUE -- Bug 12635842
258 ) IS
259
260 CURSOR c_get_revisions IS
261 SELECT intf.inventory_item_id
262 ,intf.organization_id
263 ,intf.revision_id
264 ,intf.revision
265 FROM mtl_item_revisions_interface intf
266 WHERE intf.set_process_id = p_xset_id
267 AND intf.transaction_type = 'CREATE'
268 AND intf.request_id = p_request_id
269 AND intf.process_flag = 7
270 AND intf.revision_id is not null
271 /* Bug 7675166 added this validation as revision_id is passed as null in case new revision needs to be created with existing items for item effective AG from excel*/
272 AND NOT EXISTS (SELECT NULL
273 FROM mtl_parameters param
274 WHERE param.organization_id = intf.organization_id
275 AND param.starting_revision = intf.revision);
276
277 CURSOR c_get_effective_revision(cp_inventory_item_id NUMBER
278 ,cp_organization_id NUMBER
279 ,cp_revision VARCHAR2) IS
280 SELECT revision_id
281 FROM mtl_item_revisions_b
282 WHERE inventory_item_id = cp_inventory_item_id
283 AND organization_id = cp_organization_id
284 AND revision < cp_revision
285 AND implementation_date IS NOT NULL
286 AND effectivity_date <= sysdate
287 ORDER BY effectivity_date desc;
288
289 l_source_revision_id mtl_item_revisions_b.revision_id%TYPE;
290 l_return_status VARCHAR2(100);
291 l_error_code NUMBER;
292 l_msg_count NUMBER ;
293 l_msg_data VARCHAR2(100);
294 l_pk_item_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
295 l_pk_item_rev_pairs_src EGO_COL_NAME_VALUE_PAIR_ARRAY;
296 l_pk_item_rev_pairs_dst EGO_COL_NAME_VALUE_PAIR_ARRAY;
297
298 l_temp_message VARCHAR2(2000);
299 l_retcode VARCHAR2(100);
300 l_source_system_id EGO_IMPORT_BATCHES_B.source_system_id%TYPE;
301 l_import_xref_only EGO_IMPORT_OPTION_SETS.import_xref_only%TYPE;
302 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
303 err_msg VARCHAR2(300); --Bug: 5473796
304 l_batch_id NUMBER := p_xset_id;
305 l_enabled_for_data_pool VARCHAR2(1);
306 BEGIN
307 BEGIN
308 SELECT batch.source_system_id, NVL(opt.import_xref_only,'N'), NVL(opt.ENABLED_FOR_DATA_POOL,'N')
309 INTO l_source_system_id, l_import_xref_only, l_enabled_for_data_pool
310 FROM ego_import_batches_b batch
311 ,ego_import_option_sets opt
312 WHERE batch.batch_id = p_xset_id
313 AND batch.batch_id = opt.batch_id;
314 EXCEPTION
315 WHEN OTHERS THEN
316 l_source_system_id := EGO_IMPORT_PVT.get_pdh_source_system_id;
317 l_import_xref_only := 'N';
318 l_enabled_for_data_pool := 'N';
319 END;
320
321 IF NOT( l_source_system_id <> EGO_IMPORT_PVT.get_pdh_source_system_id AND l_import_xref_only = 'Y' ) THEN
322 --Calling Item Intersections Import
323 IF l_inv_debug_level IN(101, 102) THEN
324 INVPUTLI.info('Calling EGO_ITEM_ASSOCIATIONS_PUB.Import_Item_Associations');
325 END IF;
326
327 EGO_ITEM_ASSOCIATIONS_PUB.Import_Item_Associations
328 ( p_api_version => 1.0,
329 x_batch_id => l_batch_id,
330 x_errbuf => ERRBUF,
331 x_retcode => RETCODE
332 );
333
334 IF l_inv_debug_level IN(101, 102) THEN
335 INVPUTLI.info('Returned EGO_ITEM_ASSOCIATIONS_PUB.Import_Item_Associations - '||RETCODE);
336 INVPUTLI.info(ERRBUF);
337 END IF;
338
339 l_retcode := RETCODE;
340
341 --Calling Item People prg
342 IF l_inv_debug_level IN(101, 102) THEN
343 INVPUTLI.info('Calling EGO_ITEM_PEOPLE_IMPORT_PKG.LOAD_INTERFACE_LINES');
344 END IF;
345
346 EGO_ITEM_PEOPLE_IMPORT_PKG.LOAD_INTERFACE_LINES(
347 X_ERRBUFF => ERRBUF
348 ,X_RETCODE => RETCODE
349 ,p_data_set_id => p_xset_id
350 ,p_delete_lines => p_del_rec_flag);
351
352 IF l_inv_debug_level IN(101, 102) THEN
353 INVPUTLI.info('Returned EGO_ITEM_PEOPLE_IMPORT_PKG.LOAD_INTERFACE_LINES '||RETCODE);
354 INVPUTLI.info(ERRBUF);
355 END IF;
356
357 l_retcode := RETCODE;
358
359 --Calling AML prg
360 IF l_inv_debug_level IN(101, 102) THEN
361 INVPUTLI.info('Calling EGO_ITEM_AML_PVT.LOAD_INTERFACE_LINES');
362 END IF;
363
364 EGO_ITEM_AML_PVT.LOAD_INTERFACE_LINES(
365 ERRBUF => ERRBUF
366 ,RETCODE => RETCODE
367 ,p_data_set_id => p_xset_id
368 ,p_delete_line_type => p_del_rec_flag
369 ,p_mode =>'NORMAL'
370 ,P_perform_security_check => FND_API.G_TRUE);
371
372 IF l_inv_debug_level IN(101, 102) THEN
373 INVPUTLI.info('Returned EGO_ITEM_AML_PVT.LOAD_INTERFACE_LINES '||RETCODE);
374 INVPUTLI.info(ERRBUF);
375 END IF;
376 IF RETCODE > l_retcode THEN
377 l_retcode := RETCODE;
378 END IF;
379
380 --Bug 5498078 : Defaulting UDA's during revision creation.
381 --code for this is shifted to EGOVIMUB.pls
382
383 --Calling user attr+gtin prg
384 -- Bug 12635842 : In case of SKU creation from API we do not want to process the UDAs as UDA processing will be done as a separate call.
385 IF(p_call_uda_process) THEN -- Bug 12635842 : Call UDA code if the p_call_uda_process is TRUE
386 IF l_inv_debug_level IN(101, 102) THEN
387 INVPUTLI.info('Calling EGO_ITEM_USER_ATTRS_CP_PUB.PROCESS_ITEM_USER_ATTRS_DATA');
388 EGO_ITEM_USER_ATTRS_CP_PUB.PROCESS_ITEM_USER_ATTRS_DATA(
389 ERRBUF => ERRBUF
390 ,RETCODE => RETCODE
391 ,p_data_set_id => p_xset_id
392 ,p_debug_level => 3
393 ,p_is_id_validations_reqd => FND_API.G_FALSE /* Fix for bug#9660659 */
394 );
395 ELSE
396 EGO_ITEM_USER_ATTRS_CP_PUB.PROCESS_ITEM_USER_ATTRS_DATA(
397 ERRBUF => ERRBUF
398 ,RETCODE => RETCODE
399 ,p_data_set_id => p_xset_id
400 ,p_is_id_validations_reqd => FND_API.G_FALSE /* Fix for bug#9660659 */
401 );
402
403 END IF;
404
405 IF l_inv_debug_level IN(101, 102) THEN
406 INVPUTLI.info('Returned EGO_ITEM_USER_ATTRS_CP_PUB.PROCESS_ITEM_USER_ATTRS_DATA '||RETCODE);
407 INVPUTLI.info(ERRBUF);
408 END IF;
409 IF RETCODE > l_retcode THEN
410 l_retcode := RETCODE;
411 END IF;
412 END IF; -- Bug 12635842 : End of IF(p_call_uda_process)
413 END IF; -- Xref import only
414
415 --Now returning the highest status recieved and stored in l_retcode
416 RETCODE := l_retcode;
417 EXCEPTION
418 WHEN OTHERS THEN
419 IF l_inv_debug_level IN(101, 102) THEN
420 INVPUTLI.info('WHEN-OTHERS-EXCEPTION process_item_entities: ' ||SQLCODE);
421 INVPUTLI.info(SQLERRM);
422 END IF;
423 RETCODE := G_ERROR;
424 ERRBUF := 'Unexpected error in process_item_entities: '||SQLERRM;
425 END process_item_entities;
426
427 ------------------------------------------------------------------------------------
428 /*
429 Procedure for Displaying Error in the Concurrent Log.
430 In case the Error Page is not working, helps in Debugging.
431 Fix for Bug#4540712 (RSOUNDAR)
432
433 param p_entity_name:Entity for which the Error is reported.
434 param p_table_name :Table from which the Error is generated.
435 param p_selectQuery:Query for getting ITEM_NUMBER,ORGANIZATION_CODE,ERROR_MESSAGE
436 from the respective interface tables calling this API.
437 param p_request_id :Request ID of the transaction.
438 param x_return_status:Returns the unexpected error encountered during processing.
439 param x_msg_count: Indicates how many messages exist on ERROR_HANDLER
440 message stack upon completion of processing.
441 param x_msg_data:Contains message in ERROR_HANDLER message stack
442 upon completion of processing.
443 */
444 --------------------------------------------------------------------------------------
445 PROCEDURE Write_Error_into_ConcurrentLog (
446 p_entity_name IN VARCHAR2,
447 p_table_name IN VARCHAR2,
448 p_selectQuery IN VARCHAR2,
449 p_request_id IN NUMBER,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_msg_data OUT NOCOPY VARCHAR2 ) IS
453
454 l_dyn_sql VARCHAR2(10000);
455 l_temp_text VARCHAR2(2000);
456 l_item_number VARCHAR2(81);
457 l_org_code VARCHAR2(3);
458 l_error_msg VARCHAR2(2000);
459 l_flash_heading BOOLEAN;
460
461 TYPE DYNAMIC_CUR IS REF CURSOR;
462 c_error_result DYNAMIC_CUR;
463
464 BEGIN
465
466 l_dyn_sql := p_selectQuery;
467 l_flash_heading := TRUE;
468 x_msg_count := 0;
469 x_msg_data := NULL;
470 OPEN c_error_result FOR l_dyn_sql USING p_request_id;
471 LOOP
472 FETCH c_error_result into l_item_number,l_org_code,l_error_msg;
473 EXIT WHEN c_error_result%NOTFOUND;
474 IF l_flash_heading THEN
475 l_flash_heading := FALSE;
476 l_temp_text := 'Entity Name: '||p_entity_name||' Table Name: '||p_table_name||FND_GLOBAL.Local_Chr(10);
477 FND_FILE.put_line(FND_FILE.LOG,'*Error Messages*'||FND_GLOBAL.Local_Chr(10)||l_temp_text);
478 l_temp_text:=' Item_Number '||' Org_Code '||' Message';
479 FND_FILE.put_line(FND_FILE.LOG,l_temp_text);
480 END IF;
481 FND_FILE.put_line(FND_FILE.LOG,l_item_number||' '||l_org_code||' '||l_error_msg);
482 END LOOP;
483 IF c_error_result%ISOPEN THEN
484 CLOSE c_error_result;
485 END IF;
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487
488 EXCEPTION
489 WHEN OTHERS THEN
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
492 FND_MESSAGE.Set_Token('PKG_NAME', 'EGO_ITEM_OPEN_INTERFACE_PVT');
493 FND_MESSAGE.Set_Token('API_NAME', 'Write_Error_into_ConcurrentLog');
494 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
495 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
496 ,p_count => x_msg_count
497 ,p_data => x_msg_data);
498 END Write_Error_into_ConcurrentLog;
499
500 --------------------------------------------------------------------
501 -- EGO Concurrent Wrapper API for INV Concurrent API for processing
502 -- Item Category Assignments (from MTL_ITEM_CATEGORIES_INTERFACE)
503 --
504 -- Fix for Bug# 3616946 (PPEDDAMA)
505 -- Removed the parameters: Upload Processed Records and Delete
506 -- Processed Records from UI. So, defaulting the values in this API:
507 -- Upload Processed Records = 1 (Yes)
508 -- Delete Processed Records = 0 (No)
509 --------------------------------------------------------------------
510
511 PROCEDURE process_Item_Category_records(
512 ERRBUF OUT NOCOPY VARCHAR2
513 ,RETCODE OUT NOCOPY VARCHAR2
514 ,p_rec_set_id IN NUMBER
515 ,p_upload_rec_flag IN NUMBER := 1
516 ,p_delete_rec_flag IN NUMBER := 0
517 ,p_commit_flag IN NUMBER := 1
518 ,p_prog_appid IN NUMBER := NULL
519 ,p_prog_id IN NUMBER := NULL
520 ,p_request_id IN NUMBER := NULL
521 ,p_user_id IN NUMBER := NULL
522 ,p_login_id IN NUMBER := NULL) IS
523
524 BEGIN
525 INV_EGO_REVISION_VALIDATE.Set_Process_Control('EGO_ITEM_BULKLOAD');
526
527 INV_ITEM_CATEGORY_OI.process_Item_Category_records(
528 ERRBUF => ERRBUF
529 ,RETCODE => RETCODE
530 ,p_rec_set_id => p_rec_set_id
531 ,p_upload_rec_flag => p_upload_rec_flag
532 ,p_delete_rec_flag => p_delete_rec_flag
533 ,p_commit_flag => p_commit_flag
534 ,p_prog_appid => p_prog_appid
535 ,p_prog_id => fnd_global.conc_program_id
536 ,p_request_id => fnd_global.conc_request_id --4105841
537 ,p_user_id => p_user_id
538 ,p_login_id => fnd_global.conc_login_id);
539
540 INV_EGO_REVISION_VALIDATE.Set_Process_Control(NULL);
541 END process_Item_Category_records;
542
543 ------------------------------------------------------------------------------------
544 /*
545 Procedure for Applying the specfied template to the specified interface row.
546 */
547 ------------------------------------------------------------------------------------
548
549 FUNCTION apply_multiple_template( p_template_id IN NUMBER
550 ,p_org_id IN NUMBER
551 ,p_all_org IN NUMBER := 2
552 ,p_prog_appid IN NUMBER := -1
553 ,p_prog_id IN NUMBER := -1
554 ,p_request_id IN NUMBER := -1
555 ,p_user_id IN NUMBER := -1
556 ,p_login_id IN NUMBER := -1
557 ,p_xset_id IN NUMBER := -999
558 ,x_err_text IN OUT NOCOPY VARCHAR2)
559 RETURN INTEGER
560 AS
561 l_ret_status NUMBER;
562 dumm_status NUMBER := 0;
563 BEGIN
564 /* Set the template id passed to the Function in the interface row */
565 UPDATE mtl_system_items_interface
566 SET template_id = p_template_id
567 WHERE process_flag = 1
568 AND set_process_id = p_xset_id
569 AND((p_all_org = 1) or (organization_id = p_org_id));
570
571 /* Call method to apply template attributes to the rows */
572
573 l_ret_status := INVPULI2.copy_template_attributes( org_id => p_org_id
574 ,all_org => p_all_org
575 ,prog_appid => p_prog_appid
576 ,prog_id => p_prog_id
577 ,request_id => p_request_id
578 ,user_id => p_user_id
579 ,login_id => p_login_id
580 ,xset_id => p_xset_id
581 ,err_text => x_err_text);
582
583 /* Set the template id back to null in the interface row to avoid reapplication */
584 UPDATE mtl_system_items_interface
585 SET template_id = null
586 WHERE process_flag = 1
587 AND set_process_id = p_xset_id
588 AND((p_all_org = 1) or (organization_id = p_org_id));
589
590 RETURN(l_ret_status);
591
592 EXCEPTION
593 WHEN others THEN
594 x_err_text := 'Unexpected Error ' || SQLERRM || ' occured during template application';
595 return(SQLCODE);
596 END apply_multiple_template;
597
598
599 -------------------------------------------------------------------
600 -- In this method we call methods for copying
601 -- 1. Item People
602 -- 2. Item LC Project
603 -- 3. Item Attachments
604 -------------------------------------------------------------------
605 PROCEDURE Post_Import_Defaulting(ERRBUF OUT NOCOPY VARCHAR2,
606 RETCODE OUT NOCOPY VARCHAR2,
607 p_batch_id IN NUMBER,
608 p_del_rec_flag IN NUMBER := 1)
609 IS
610 l_retcode VARCHAR2(100);
611 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
612 l_source_system_id EGO_IMPORT_BATCHES_B.source_system_id%TYPE;
613 l_import_xref_only EGO_IMPORT_OPTION_SETS.import_xref_only%TYPE;
614 l_enabled_for_data_pool VARCHAR2(1);
615 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
616 err_msg VARCHAR2(300); --Bug: 5473796
617 l_temp_message VARCHAR2(2000);
618 BEGIN
619 BEGIN
620 SELECT batch.source_system_id, NVL(opt.import_xref_only,'N'), NVL(opt.ENABLED_FOR_DATA_POOL,'N')
621 INTO l_source_system_id, l_import_xref_only, l_enabled_for_data_pool
622 FROM
623 ego_import_batches_b batch,
624 ego_import_option_sets opt
625 WHERE batch.batch_id = p_batch_id
626 AND batch.batch_id = opt.batch_id;
627 EXCEPTION
628 WHEN OTHERS THEN
629 l_source_system_id := EGO_IMPORT_PVT.get_pdh_source_system_id;
630 l_import_xref_only := 'N';
631 l_enabled_for_data_pool := 'N';
632 END;
633
634 IF NOT( l_source_system_id <> EGO_IMPORT_PVT.get_pdh_source_system_id AND l_import_xref_only = 'Y' ) THEN
635 --Calling GTIN bulkloader
636 IF l_inv_debug_level IN(101, 102) THEN
637 INVPUTLI.info('Calling EGO_IMPORT_PVT.Process_Gtin_Intf_Rows');
638 END IF;
639
640 EGO_IMPORT_PVT.Process_Gtin_Intf_Rows(
641 ERRBUF => ERRBUF
642 ,RETCODE => RETCODE
643 ,p_data_set_id => p_batch_id);
644
645 IF l_inv_debug_level IN(101, 102) THEN
646 INVPUTLI.info('Returned EGO_IMPORT_PVT.Process_Gtin_Intf_Rows '||RETCODE);
647 INVPUTLI.info(ERRBUF);
648 END IF;
649
650 IF RETCODE > l_retcode THEN
651 l_retcode := RETCODE;
652 END IF;
653
654 --Calling updation of inbound message timestamp
655 IF l_enabled_for_data_pool = 'Y' THEN
656 IF l_inv_debug_level IN(101, 102) THEN
657 INVPUTLI.info('Calling EGO_IMPORT_UTIL_PVT.Update_Timestamp_In_Prod');
658 END IF;
659
660 EGO_IMPORT_UTIL_PVT.Update_Timestamp_In_Prod(
661 ERRBUF => ERRBUF
662 ,RETCODE => RETCODE
663 ,p_batch_id => p_batch_id);
664
665 IF l_inv_debug_level IN(101, 102) THEN
666 INVPUTLI.info('Returned EGO_IMPORT_UTIL_PVT.Update_Timestamp_In_Prod '||RETCODE);
667 INVPUTLI.info(ERRBUF);
668 END IF;
669
670 IF RETCODE > l_retcode THEN
671 l_retcode := RETCODE;
672 END IF;
673 END IF; -- IF l_enabled_for_data_pool = 'Y' THEN
674
675 --Calling Item People Defaulting
676 IF l_inv_debug_level IN(101, 102) THEN
677 INVPUTLI.info('Calling EGO_IMPORT_UTIL_PVT.Default_Item_People');
678 END IF;
679
680 EGO_IMPORT_UTIL_PVT.Default_Item_People(
681 RETCODE => RETCODE
682 ,ERRBUF => ERRBUF
683 ,p_batch_id => p_batch_id);
684
685 IF l_inv_debug_level IN(101, 102) THEN
686 INVPUTLI.info('Returned EGO_IMPORT_UTIL_PVT.Default_Item_People '||RETCODE);
687 INVPUTLI.info(ERRBUF);
688 END IF;
689 IF RETCODE > l_retcode THEN
690 l_retcode := RETCODE;
691 END IF;
692
693 --Calling Item LC Project Defaulting
694 IF l_inv_debug_level IN(101, 102) THEN
695 INVPUTLI.info('Calling EGO_IMPORT_UTIL_PVT.Copy_LC_Projects');
696 END IF;
697
698 EGO_IMPORT_UTIL_PVT.Copy_LC_Projects(
699 RETCODE => RETCODE
700 ,ERRBUF => ERRBUF
701 ,p_batch_id => p_batch_id);
702
703 IF l_inv_debug_level IN(101, 102) THEN
704 INVPUTLI.info('Returned EGO_IMPORT_UTIL_PVT.Copy_LC_Projects '||RETCODE);
705 INVPUTLI.info(ERRBUF);
706 END IF;
707 IF RETCODE > l_retcode THEN
708 l_retcode := RETCODE;
709 END IF;
710
711 --Calling Item Attachments Copy
712 IF l_inv_debug_level IN(101, 102) THEN
713 INVPUTLI.info('Calling EGO_IMPORT_UTIL_PVT.Copy_Attachments');
714 END IF;
715
716 EGO_IMPORT_UTIL_PVT.Copy_Attachments(
717 RETCODE => RETCODE
718 ,ERRBUF => ERRBUF
719 ,p_batch_id => p_batch_id);
720
721 IF l_inv_debug_level IN(101, 102) THEN
722 INVPUTLI.info('Returned EGO_IMPORT_UTIL_PVT.Copy_Attachments '||RETCODE);
723 INVPUTLI.info(ERRBUF);
724 END IF;
725 IF RETCODE > l_retcode THEN
726 l_retcode := RETCODE;
727 END IF;
728
729 --Cleaning up dirty SKU entries
730 IF l_inv_debug_level IN(101, 102) THEN
731 INVPUTLI.info('Calling EGO_IMPORT_UTIL_PVT.Clean_Dirty_SKUs');
732 END IF;
733
734 EGO_IMPORT_UTIL_PVT.Clean_Dirty_SKUs(
735 RETCODE => RETCODE
736 ,ERRBUF => ERRBUF
737 ,p_batch_id => p_batch_id);
738
739 IF l_inv_debug_level IN(101, 102) THEN
740 INVPUTLI.info('Returned EGO_IMPORT_UTIL_PVT.Clean_Dirty_SKUs '||RETCODE);
741 INVPUTLI.info(ERRBUF);
742 END IF;
743 IF RETCODE > l_retcode THEN
744 l_retcode := RETCODE;
745 END IF;
746
747 --calling copy people from style to SKU for newly added people
748 IF l_inv_debug_level IN(101, 102) THEN
749 INVPUTLI.info('Calling EGO_IMPORT_UTIL_PVT.Copy_Item_People_From_Style');
750 END IF;
751
752 EGO_IMPORT_UTIL_PVT.Copy_Item_People_From_Style(
753 RETCODE => RETCODE
754 ,ERRBUF => ERRBUF
755 ,p_batch_id => p_batch_id);
756
757 IF l_inv_debug_level IN(101, 102) THEN
758 INVPUTLI.info('Returned EGO_IMPORT_UTIL_PVT.Copy_Item_People_From_Style '||RETCODE);
759 INVPUTLI.info(ERRBUF);
760 END IF;
761 IF RETCODE > l_retcode THEN
762 l_retcode := RETCODE;
763 END IF;
764
765
766 --Bug: 5473976 Interface rows will be deleted here not in IOI
767 IF p_del_rec_flag = 1 THEN
768 IF l_inv_debug_level IN(101, 102) THEN
769 INVPUTLI.info('Deleting interface records');
770 END IF;
771 l_retcode := INVPOPIF.indelitm_delete_item_oi (err_text => err_msg,
772 xset_id => p_batch_id);
773
774 IF l_inv_debug_level IN(101, 102) THEN
775 INVPUTLI.info('Returned INVPOPIF.indelitm_delete_item_oi '||err_msg);
776 END IF;
777 IF RETCODE > l_retcode THEN
778 l_retcode := RETCODE;
779 END IF;
780 END IF;
781 --End Bug: 5473976
782 END IF;
783 ---------------------------------------------
784 --For Error Link Display in the Conc. Req Log
785 --Bug# 4540712 (RSOUNDAR)
786 ---------------------------------------------
787 IF NVL(l_request_id, -1) <> -1 THEN
788 FND_MESSAGE.SET_NAME('EGO','EGO_ITEM_BULK_ERRS_LINKTXT1');
789 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
790 FND_MESSAGE.SET_NAME('EGO','EGO_ITEMBULK_HOSTANDPORT');
791 l_temp_message := rtrim(FND_PROFILE.VALUE('APPS_FRAMEWORK_AGENT'), '/');--FND_MESSAGE.GET;
792 FND_MESSAGE.SET_NAME('EGO','EGO_ITEM_BULK_ERRS_LINK');
793 FND_MESSAGE.SET_TOKEN('HOST_AND_PORT', l_temp_message);
794 FND_MESSAGE.SET_TOKEN('CONC_REQ_ID', l_request_id);
795 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
796
797 FND_FILE.put_line(FND_FILE.LOG, 'Following items got processed in this batch');
798 FND_FILE.put_line(FND_FILE.LOG, '----------------------------------------------------');
799 FND_FILE.put_line(FND_FILE.LOG, 'TRANSACTION TYPE ORGANIZATION CODE ITEM NUMBER');
800 FND_FILE.put_line(FND_FILE.LOG, '----------------------------------------------------');
801
802
803 --
804 -- Bug 11901255. Log not showing assigned items.
805 -- Commenting out the predicate that restricts the
806 -- results only to master organization.
807 -- sreharih. Thu Mar 24 12:45:06 PDT 2011
808 --
809 FOR i IN (SELECT msii.TRANSACTION_TYPE, msii.ITEM_NUMBER, NVL(msii.ORGANIZATION_CODE, mp.ORGANIZATION_CODE) AS ORGANIZATION_CODE
810 FROM MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
811 WHERE msii.SET_PROCESS_ID = p_batch_id
812 AND msii.REQUEST_ID = l_request_id
813 AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
814 --AND mp.MASTER_ORGANIZATION_ID = mp.ORGANIZATION_ID Bug 11901255
815 AND msii.PROCESS_FLAG = 7
816 AND NVL(msii.CONFIRM_STATUS, 'X') NOT IN ('CFC', 'CFM', 'FMR', 'UFN', 'UFS', 'UFM', 'FK', 'FEX')
817 )
818 LOOP
819 FND_FILE.put_line(FND_FILE.LOG, RPAD(i.TRANSACTION_TYPE, 16, ' ') ||' '||RPAD(i.ORGANIZATION_CODE, 17, ' ') ||' '||i.ITEM_NUMBER);
820 END LOOP;
821 FND_FILE.put_line(FND_FILE.LOG, '----------------------------------------------------');
822 END IF;
823 EXCEPTION
824 WHEN OTHERS THEN
825 IF l_inv_debug_level IN(101, 102) THEN
826 INVPUTLI.info('WHEN-OTHERS-EXCEPTION Post_Import_Defaulting: ' ||SQLCODE);
827 INVPUTLI.info(SQLERRM);
828 END IF;
829 RETCODE := G_ERROR;
830 ERRBUF := 'Unexpected error in Post_Import_Defaulting: '||SQLERRM;
831 END Post_Import_Defaulting;
832
833 END EGO_ITEM_OPEN_INTERFACE_PVT;