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