[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_ASSOCIATIONS_PUB
Source
1 PACKAGE BODY ego_item_associations_pub AS
2 /* $Header: EGOPIASB.pls 120.37 2011/07/18 09:40:34 nendrapu ship $ */
3
4 G_FILE_NAME VARCHAR2(12);
5 G_PKG_NAME VARCHAR2(30);
6
7 G_USER_ID fnd_user.user_id%TYPE;
8 G_PARTY_ID hz_parties.party_id%TYPE;
9 G_LOGIN_ID fnd_user.last_update_login%TYPE;
10 G_REQUEST_ID NUMBER;
11 G_PROG_APPID ego_item_associations_intf.program_application_id%TYPE;
12 G_PROG_ID ego_item_associations_intf.program_id%TYPE;
13 G_SYSDATE fnd_user.creation_date%TYPE;
14 G_SESSION_LANG VARCHAR2(99);
15 G_DATA_LEVEL_NAMES VARCHAR2_TBL_TYPE;
16 G_LOG_TIMESTAMP_FORMAT VARCHAR2(25) := 'DD-MM-YYYY HH24:MI:SS';
17 G_SKIP_SECURIY_CHECK NUMBER := -99;
18
19 PROCEDURE write_log_message( p_message IN VARCHAR2
20 , p_add_timestamp IN BOOLEAN DEFAULT TRUE )
21 IS
22 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level;
23 l_message VARCHAR2(3800);
24 BEGIN
25 IF l_inv_debug_level IN(101, 102) THEN
26 IF LENGTH(p_message) > 3800 THEN
27 FOR i IN 1..( CEIL(LENGTH(p_message)/3800) ) LOOP
28 l_message := SUBSTR(p_message, ( 3800*(i-1) + 1 ), 3800 );
29 INVPUTLI.info( ( CASE
30 WHEN p_add_timestamp THEN to_char( systimestamp, G_LOG_TIMESTAMP_FORMAT ) || ': '
31 ELSE ''
32 END )
33 || l_message );
34 END LOOP;
35 ELSE
36 INVPUTLI.info( ( CASE
37 WHEN p_add_timestamp THEN to_char( systimestamp, G_LOG_TIMESTAMP_FORMAT ) || ': '
38 ELSE ''
39 END )
40 || p_message );
41 END IF;
42 END IF;
43 END write_log_message;
44
45 /*
46 -- Start of comments
47 -- API name : set_globals
48 -- Type : Private.
49 -- Function : Sets the global constant values used in this package.
50 -- Pre-reqs : None.
51 -- Version : Initial version 1.0
52 -- Notes : Sets the global constant values used in this package.
53 -- 1. G_USER_ID - user id
54 -- 2. G_SYSDATE - Creation Date and Update Date
55 -- 3. G_LOGIN_ID - Login which is used to create/update.
56 -- End of comments
57 */
58 PROCEDURE set_globals IS
59 BEGIN
60 --
61 -- file names
62 --
63 G_FILE_NAME := NVL(G_FILE_NAME,'EGOPIASB.pls');
64 G_PKG_NAME := NVL(G_PKG_NAME,'EGO_ITEM_ASSOCIATIONS_PUB');
65 --
66 -- user values
67 --
68 G_USER_ID := FND_GLOBAL.user_id;
69 G_LOGIN_ID := FND_GLOBAL.login_id;
70 G_REQUEST_ID := NVL(FND_GLOBAL.conc_request_id, -1);
71 G_PROG_APPID := FND_GLOBAL.prog_appl_id;
72 G_PROG_ID := FND_GLOBAL.conc_program_id;
73 G_SYSDATE := NVL(G_SYSDATE,SYSDATE);
74 G_SESSION_LANG := USERENV('LANG');
75 G_PARTY_ID := -1;
76 G_DATA_LEVEL_NAMES(1) := G_ITEM_SUP_LEVEL_NAME;
77 G_DATA_LEVEL_NAMES(2) := G_ITEM_SUP_SITE_LEVEL_NAME;
78 G_DATA_LEVEL_NAMES(3) := G_ITEM_SUP_SITE_ORG_LEVEL_NAME;
79 write_log_message(' After setting globals ' );
80 BEGIN
81 SELECT party_id
82 INTO G_PARTY_ID
83 FROM ego_user_v
84 WHERE USER_ID = G_USER_ID;
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 SELECT party_id, user_id
88 INTO G_PARTY_ID, G_USER_ID
89 FROM ego_user_v
90 WHERE USER_NAME = FND_GLOBAL.USER_NAME;
91 END;
92 EXCEPTION
93 WHEN OTHERS THEN
94 write_log_message(' After setting globals end' || SQLERRM);
95 END set_globals;
96
97 /*
98 -- Start of comments
99 -- API name : initialize
100 -- Type : Private.
101 -- Function : Initializes the import flow.
102 -- Pre-reqs : None.
103 -- Parameters :
104 -- IN : p_batch_id IN NUMBER Required
105 -- Version : Initial version 1.0
106 -- Notes : Initializes the import flow.
107 -- 1. Change the transaction type to UPPER
108 -- 2. Initialize the records to be G_REC_IN_PROCESS
109 -- where current status G_REC_TO_BE_PROCESSED
110 -- 3. Set Missing Required value if any of the following are null
111 -- i) Org Code and Org Id
112 -- ii) Item Number and Item Id
113 -- iii) pk1_value and pk1_name
114 -- iv) (Item-SupplierSite or Item-SupplierSite-Org)
115 -- and (pk2 value and pk2 name is null)
116 --
117 -- End of comments
118 */
119 PROCEDURE initialize( p_batch_id IN NUMBER )
120 IS
121 BEGIN
122 FOR l_null_tx_rec IN ( SELECT ROWID
123 FROM ego_item_associations_intf
124 WHERE batch_id = p_batch_id
125 AND process_flag = G_REC_TO_BE_PROCESSED
126 AND transaction_id IS NULL
127 )
128 LOOP
129 UPDATE ego_item_associations_intf
130 SET transaction_id = mtl_system_items_interface_s.nextval
131 WHERE ROWID = l_null_tx_rec.ROWID;
132 END LOOP;
133 -- Set the process flag to in process
134 UPDATE ego_item_associations_intf
135 SET process_flag = G_REC_IN_PROCESS
136 WHERE batch_id = p_batch_id
137 AND process_flag = G_REC_TO_BE_PROCESSED;
138 -- Check Required Values based on Transaction Type
139
140 -- Atleast one of the item value and one of the org value should be populated
141 UPDATE ego_item_associations_intf
142 SET process_flag = G_REC_MISSING_REQ_VALUE
143 WHERE batch_id = p_batch_id
144 AND process_flag = G_REC_IN_PROCESS
145 AND transaction_type = G_CREATE
146 AND ( ( inventory_item_id IS NULL AND item_number IS NULL )
147 OR ( organization_id IS NULL AND organization_code IS NULL)
148 );
149 UPDATE ego_item_associations_intf
150 SET process_flag = G_REC_MISSING_REQ_VALUE
151 WHERE batch_id = p_batch_id
152 AND process_flag = G_REC_IN_PROCESS
153 AND transaction_type = G_CREATE
154 AND ( ( pk1_value IS NULL AND supplier_name IS NULL AND supplier_number IS NULL )
155 OR ( ( data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
156 OR data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL )
157 AND ( pk2_value IS NULL AND supplier_site_name IS NULL ) )
158 );
159
160 END initialize;
161
162 /*
163 -- Start of comments
164 -- API name : convert_values_to_ids
165 -- Type : Private.
166 -- Function : Converts user entered values to system ids.
167 -- Pre-reqs : None.
168 -- Parameters :
169 -- IN : p_batch_id IN NUMBER Required
170 -- Version : Initial version 1.0
171 -- Notes : Converts user entered values to system ids.
172 -- 1. Convert master org id and master org code
173 -- (Item-Supplier and Item-SupplierSite)
174 -- 2. Convert org id and org code (Item-SupplierSite-Org)
175 -- 3. Convert Item Id and Item Number
176 -- 4. Convert pk1 and pk2 values
177 -- 5. Convert SYNC to CREATE/UPDATE
178 -- 6. Populate UPDATE/DELETE records with association id
179 -- 7. Validate Duplicate
180 -- 8. After conversion set the record to error in following cases
181 -- i) If org is null
182 -- ii) If item is null
183 -- iii) If pk1 value is null
184 -- iv) If pk2 value is null for item-site and item-site-org
185 -- iv) association id is null for UPDATE/DELETE
186 -- End of comments
187 */
188 PROCEDURE convert_values_to_ids( p_batch_id IN NUMBER )
189 IS
190 BEGIN
191 /* Do not convert name to ids, which is required only for error reporting.
192 -- Convert the master org values. Convert Organization Id to Organization Code
193 UPDATE ego_item_associations_intf eiai
194 SET organization_code = ( SELECT mp.organization_code
195 FROM mtl_parameters mp
196 WHERE mp.organization_id = eiai.organization_id
197 AND mp.master_organization_id = mp.organization_id)
198 WHERE eiai.batch_id = p_batch_id
199 AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
200 AND eiai.process_flag = G_REC_IN_PROCESS
201 AND eiai.organization_code IS NULL
202 AND eiai.organization_id IS NOT NULL;
203
204 -- Convert the org values. Convert Organization Id to Organization Code
205 UPDATE ego_item_associations_intf eiai
206 SET organization_code = ( SELECT mp.organization_code
207 FROM mtl_parameters mp
208 WHERE mp.organization_id = eiai.organization_id)
209 WHERE eiai.batch_id = p_batch_id
210 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
211 AND eiai.process_flag = G_REC_IN_PROCESS
212 AND eiai.organization_code IS NULL
213 AND eiai.organization_id IS NOT NULL;
214
215 -- check whether item value converion is required..
216 UPDATE ego_item_associations_intf eiai
217 SET item_number = ( SELECT concatenated_segments
218 FROM mtl_system_items_b_kfv msibk
219 WHERE msibk.organization_id = eiai.organization_id
220 AND msibk.inventory_item_id = eiai.inventory_item_id
221 )
222 WHERE eiai.batch_id = p_batch_id
223 AND eiai.process_flag = G_REC_IN_PROCESS
224 AND eiai.item_number IS NULL
225 AND eiai.inventory_item_id IS NOT NULL;
226 */
227 UPDATE ego_item_associations_intf eiai
228 SET inventory_item_id = ( SELECT inventory_item_id
229 FROM mtl_system_items_b_kfv msibk
230 WHERE msibk.organization_id = eiai.organization_id
231 AND msibk.concatenated_segments = eiai.item_number
232 )
233 WHERE eiai.batch_id = p_batch_id
234 AND eiai.process_flag = G_REC_IN_PROCESS
235 AND eiai.inventory_item_id IS NULL
236 AND eiai.item_number IS NOT NULL;
237 /*
238 UPDATE ego_item_associations_intf eiai
239 SET (supplier_number,supplier_name) = ( SELECT segment1, vendor_name
240 FROM ap_suppliers aas
241 WHERE aas.vendor_id = eiai.pk1_value
242 )
243 WHERE eiai.batch_id = p_batch_id
244 AND eiai.process_flag = G_REC_IN_PROCESS
245 AND eiai.supplier_number IS NULL
246 AND eiai.pk1_value IS NOT NULL;
247 UPDATE ego_item_associations_intf eiai
248 SET supplier_site_name = ( SELECT vendor_site_code
249 FROM ap_supplier_sites_all asa
250 WHERE asa.vendor_site_id = eiai.pk2_value
251 AND asa.org_id = fnd_profile.value('ORG_ID')
252 )
253 WHERE eiai.batch_id = p_batch_id
254 AND eiai.process_flag = G_REC_IN_PROCESS
255 AND eiai.supplier_site_name IS NULL
256 AND eiai.pk2_value IS NOT NULL;
257
258 UPDATE ego_item_associations_intf eiai
259 SET item_number = ( SELECT concatenated_segments
260 FROM mtl_system_items_b_kfv msibk
261 WHERE msibk.organization_id = eiai.organization_id
262 AND msibk.inventory_item_id = eiai.inventory_item_id
263 )
264 WHERE eiai.batch_id = p_batch_id
265 AND eiai.process_flag = G_REC_IN_PROCESS
266 AND eiai.item_number IS NULL
267 AND eiai.inventory_item_id IS NOT NULL;
268 */
269
270 UPDATE ego_item_associations_intf eiai
271 SET association_id = ( SELECT eia.association_id
272 FROM ego_item_associations eia
273 WHERE eia.data_level_id = eiai.data_level_id
274 AND eia.organization_id = eiai.organization_id
275 AND eia.inventory_item_id = eiai.inventory_item_id
276 AND eia.pk1_value = eiai.pk1_value
277 AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
278 )
279 WHERE eiai.batch_id = p_batch_id
280 AND eiai.process_flag = G_REC_IN_PROCESS
281 AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE );
282 /*
283 UPDATE ego_item_associations_intf eiai1
284 SET process_flag = G_REC_DUPLICATE
285 WHERE eiai1.batch_id = p_batch_id
286 AND EXISTS
287 (
288 SELECT 1
289 FROM ego_item_associations_intf eiai2
290 WHERE eiai2.transaction_type = eiai1.transaction_type
291 AND eiai2.batch_id = eiai1.batch_id
292 AND eiai2.transaction_id <> eiai1.transaction_id
293 AND eiai2.inventory_item_id = eiai1.inventory_item_id
294 AND eiai2.organization_id = eiai1.organization_id
295 AND eiai2.data_level_id = eiai1.data_level_id
296 AND eiai2.pk1_value = eiai1.pk1_value
297 AND NVL(eiai1.pk2_value,-1) = NVL(eiai2.pk2_value,-1)
298 );
299 */
300 UPDATE ego_item_associations_intf eiai
301 SET process_flag = G_REC_INVALID_MASTER_ORG
302 WHERE eiai.batch_id = p_batch_id
303 AND eiai.process_flag = G_REC_IN_PROCESS
304 AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
305 AND ( eiai.organization_id IS NULL
306 OR NOT EXISTS
307 (
308 SELECT 1
309 FROM mtl_parameters mp
310 WHERE mp.master_organization_id = eiai.organization_id
311 )
312 );
313 UPDATE ego_item_associations_intf eiai
314 SET process_flag = G_REC_INVALID_ORG
315 WHERE eiai.batch_id = p_batch_id
316 AND eiai.process_flag = G_REC_IN_PROCESS
317 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
318 AND ( eiai.organization_id IS NULL
319 OR NOT EXISTS
320 (
321 SELECT 1
322 FROM mtl_parameters mp
323 WHERE mp.organization_id = eiai.organization_id
324 )
325 );
326 UPDATE ego_item_associations_intf eiai
327 SET process_flag = G_REC_INVALID_ITEM
328 WHERE eiai.batch_id = p_batch_id
329 AND eiai.process_flag = G_REC_IN_PROCESS
330 AND ( eiai.inventory_item_id IS NULL
331 OR NOT EXISTS
332 (
333 SELECT 1
334 FROM mtl_system_items_b msib
335 WHERE msib.inventory_item_id = eiai.inventory_item_id
336 AND msib.organization_id = eiai.organization_id
337 )
338 );
339 UPDATE ego_item_associations_intf eiai
340 SET process_flag = G_REC_INVALID_PK1_VALUE
341 WHERE eiai.batch_id = p_batch_id
342 AND eiai.process_flag = G_REC_IN_PROCESS
343 AND ( eiai.pk1_value IS NULL
344 OR NOT EXISTS
345 (
346 SELECT 1
347 FROM ap_suppliers aas
348 WHERE aas.vendor_id = eiai.pk1_value
349 and NVL(aas.end_date_active,SYSDATE+1) > SYSDATE --bug11072046
350 )
351 );
352 UPDATE ego_item_associations_intf eiai
353 SET process_flag = G_REC_INVALID_PK2_VALUE
354 WHERE eiai.batch_id = p_batch_id
355 AND eiai.process_flag = G_REC_IN_PROCESS
356 AND ( eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
357 OR
358 eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL -- BUG 6322084
359 )
360 AND ( eiai.pk2_value IS NULL
361 OR NOT EXISTS
362 (
363 SELECT 1
364 FROM ap_supplier_sites_all assa
365 WHERE assa.vendor_site_id = eiai.pk2_value
366 --AND assa.vendor_id = eiai.vendor_id
367 AND assa.vendor_id = eiai.pk1_value
368 AND nvl(assa.inactive_date,SYSDATE + 1)>SYSDATE --BUG 11072046
369 AND assa.org_id = fnd_profile.value('ORG_ID')
370 )
371 );
372 UPDATE ego_item_associations_intf eiai
373 SET process_flag = G_REC_ASSOCIATION_NOT_EXISTS
374 WHERE eiai.batch_id = p_batch_id
375 AND eiai.process_flag = G_REC_IN_PROCESS
376 AND ( eiai.transaction_type = G_UPDATE OR eiai.transaction_type = G_DELETE )
377 AND eiai.association_id IS NULL;
378 UPDATE ego_item_associations_intf eiai
379 SET process_flag = G_REC_INVALID_ASSOC_TYPE
380 WHERE eiai.batch_id = p_batch_id
381 AND eiai.process_flag = G_REC_IN_PROCESS
382 AND NOT EXISTS
383 (
384 SELECT 1
385 FROM ego_data_level_b edlb
386 WHERE edlb.data_level_id = eiai.data_level_id
387 );
388 UPDATE ego_item_associations_intf eiai
389 SET process_flag = G_REC_INVALID_STATUS
390 WHERE eiai.batch_id = p_batch_id
391 AND eiai.process_flag = G_REC_IN_PROCESS
392 AND eiai.status_code IS NOT NULL
393 AND NOT EXISTS
394 (
395 SELECT 1
396 FROM fnd_lookups fl
397 WHERE fl.lookup_type = 'EGO_ASSOCIATION_STATUS'
398 AND fl.lookup_code = eiai.status_code
399 );
400 UPDATE ego_item_associations_intf eiai
401 SET process_flag = G_REC_INVALID_PRIMARY
402 WHERE eiai.batch_id = p_batch_id
403 AND eiai.process_flag = G_REC_IN_PROCESS
404 AND eiai.primary_flag IS NOT NULL
405 AND eiai.primary_flag NOT IN(G_DEFAULT_PRIMARY_FLAG, G_PRIMARY);
406
407 END convert_values_to_ids;
408
409 /*
410 -- Start of comments
411 -- API name : check_security
412 -- Type : Private.
413 -- Function : Performs the security check.
414 -- Pre-reqs : None.
415 -- Parameters :
416 -- IN : p_batch_id IN NUMBER Required
417 -- Version : Initial version 1.0
418 -- Notes : Performs the security check.
419 -- Check create privilege(EGO_CREATE_ITEM_SUP_ASSIGN) for CREATE
420 -- Check edit privilege(EGO_EDIT_ITEM_SUP_ASSIGN) for UPDATE/DELETE
421 -- Check for supplier access for external user
422 -- End of comments
423 */
424 PROCEDURE check_security(p_batch_id IN NUMBER)
425 IS
426 l_assoc_create_priv VARCHAR2(30);
427 l_assoc_edit_priv VARCHAR2(30);
428 l_item_org_edit_priv VARCHAR2(30);
429 x_return_status VARCHAR2(1);
430 l_sec_predicate VARCHAR2(32767);
431 l_dynamic_sql VARCHAR2(32767);
432 l_msg_data VARCHAR2(2000);
433 l_vendor_contact VARCHAR2(1);
434 BEGIN
435 l_assoc_create_priv := 'EGO_CREATE_ITEM_SUP_ASSIGN';
436 l_assoc_edit_priv := 'EGO_EDIT_ITEM_SUP_ASSIGN';
437 l_item_org_edit_priv := 'EGO_EDIT_ITEM_ORG_ASSIGN';
438
439 EGO_DATA_SECURITY.get_security_predicate
440 (p_api_version => 1.0
441 ,p_function => l_assoc_create_priv
442 ,p_object_name => 'EGO_ITEM'
443 ,p_user_name => 'HZ_PARTY:'||TO_CHAR(G_PARTY_ID)
444 ,p_statement_type => 'EXISTS'
445 ,p_pk1_alias => 'eiai.inventory_item_id'
446 ,p_pk2_alias => 'eiai.organization_id'
447 ,p_pk3_alias => NULL
448 ,p_pk4_alias => NULL
449 ,p_pk5_alias => NULL
450 ,x_predicate => l_sec_predicate
451 ,x_return_status => x_return_status );
452
453 IF x_return_status IN ('T','F') THEN
454 IF l_sec_predicate IS NOT NULL THEN
455 l_dynamic_sql :=
456 ' UPDATE ego_item_associations_intf eiai ' ||
457 ' SET process_flag = '||G_REC_NO_CREATE_ASSOC_PRIV ||
458 ' WHERE batch_id = :p_batch_id '||
459 ' AND process_flag = '||G_REC_IN_PROCESS||
460 ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
461 ' AND transaction_type = '''||G_CREATE||''' AND NOT '|| l_sec_predicate;
462 EXECUTE IMMEDIATE l_dynamic_sql
463 USING IN p_batch_id;
464 END IF;
465 ELSE
466 l_msg_data := FND_MESSAGE.GET_ENCODED();
467 fnd_message.set_name ('EGO', 'EGO_PLSQL_ERR');
468 fnd_message.set_token ('PKG_NAME', G_PKG_NAME);
469 fnd_message.set_token ('API_NAME', 'check_security');
470 fnd_message.set_token ('SQL_ERR_MSG', l_msg_data);
471 APP_EXCEPTION.RAISE_EXCEPTION();
472 END IF;
473 EGO_DATA_SECURITY.get_security_predicate
474 (p_api_version => 1.0
475 ,p_function => l_assoc_edit_priv
476 ,p_object_name => 'EGO_ITEM'
477 ,p_user_name => 'HZ_PARTY:'||TO_CHAR(G_PARTY_ID)
478 ,p_statement_type => 'EXISTS'
479 ,p_pk1_alias => 'eiai.inventory_item_id'
480 ,p_pk2_alias => 'eiai.organization_id'
481 ,p_pk3_alias => NULL
482 ,p_pk4_alias => NULL
483 ,p_pk5_alias => NULL
484 ,x_predicate => l_sec_predicate
485 ,x_return_status => x_return_status );
486 IF x_return_status IN ('T','F') THEN
487 IF l_sec_predicate IS NOT NULL THEN
488 l_dynamic_sql :=
489 ' UPDATE ego_item_associations_intf eiai ' ||
490 ' SET process_flag = '||G_REC_NO_EDIT_ASSOC_PRIV ||
491 ' WHERE batch_id = :p_batch_id '||
492 ' AND process_flag = '||G_REC_IN_PROCESS||
493 ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
494 ' AND ( transaction_type = '''||G_UPDATE||''' OR transaction_type = '''||G_DELETE||''' )'||
495 ' AND NOT '|| l_sec_predicate;
496 EXECUTE IMMEDIATE l_dynamic_sql
497 USING IN p_batch_id;
498 END IF;
499 ELSE
500 l_msg_data := FND_MESSAGE.GET_ENCODED();
501 fnd_message.set_name ('EGO', 'EGO_PLSQL_ERR');
502 fnd_message.set_token ('PKG_NAME', G_PKG_NAME);
503 fnd_message.set_token ('API_NAME', 'check_security');
504 fnd_message.set_token ('SQL_ERR_MSG', l_msg_data);
505 APP_EXCEPTION.RAISE_EXCEPTION();
506 END IF;
507 EGO_DATA_SECURITY.get_security_predicate
508 (p_api_version => 1.0
509 ,p_function => l_item_org_edit_priv
510 ,p_object_name => 'EGO_ITEM'
511 ,p_user_name => 'HZ_PARTY:'||TO_CHAR(G_PARTY_ID)
512 ,p_statement_type => 'EXISTS'
513 ,p_pk1_alias => 'eiai.inventory_item_id'
514 ,p_pk2_alias => 'eiai.organization_id'
515 ,p_pk3_alias => NULL
516 ,p_pk4_alias => NULL
517 ,p_pk5_alias => NULL
518 ,x_predicate => l_sec_predicate
519 ,x_return_status => x_return_status );
520 IF x_return_status IN ('T','F') THEN
521 IF l_sec_predicate IS NOT NULL THEN
522 l_dynamic_sql :=
523 ' UPDATE ego_item_associations_intf eiai ' ||
524 ' SET process_flag = '||G_REC_NO_EDIT_ITEM_ORG_PRIV ||
525 ' WHERE batch_id = :p_batch_id '||
526 ' AND data_level_id = 43105 '||
527 ' AND process_flag = '||G_REC_IN_PROCESS||
528 ' AND eiai.created_by <> '||G_SKIP_SECURIY_CHECK||
529 ' AND ( transaction_type = '''||G_UPDATE||''' OR transaction_type = '''||G_DELETE||''' )'||
530 ' AND NOT '|| l_sec_predicate;
531 EXECUTE IMMEDIATE l_dynamic_sql
532 USING IN p_batch_id;
533 END IF;
534 ELSE
535 l_msg_data := FND_MESSAGE.GET_ENCODED();
536 fnd_message.set_name ('EGO', 'EGO_PLSQL_ERR');
537 fnd_message.set_token ('PKG_NAME', G_PKG_NAME);
538 fnd_message.set_token ('API_NAME', 'check_security');
539 fnd_message.set_token ('SQL_ERR_MSG', l_msg_data);
540 APP_EXCEPTION.RAISE_EXCEPTION();
541 END IF;
542
543 IF ego_item_associations_util.is_supplier_contact(G_PARTY_ID) = FND_API.G_TRUE THEN
544 UPDATE ego_item_associations_intf eiai
545 SET process_flag = G_REC_NO_SUPPL_ACCESS_PRIV
546 WHERE eiai.batch_id = p_batch_id
547 AND eiai.process_flag = G_REC_IN_PROCESS
548 AND NOT EXISTS
549 (
550 SELECT 1
551 FROM ego_vendor_v evv
552 WHERE evv.vendor_id = eiai.pk1_value
553 AND evv.user_id = G_USER_ID
554 );
555 END IF;
556 END check_security;
557
558 /*
559 -- Start of comments
560 -- API name : validate_associations
561 -- Type : Private.
562 -- Function : Validates associaitons based on association type.
563 -- Pre-reqs : None.
564 -- Parameters :
565 -- IN : p_batch_id IN NUMBER Required
566 -- Version : Initial version 1.0
567 -- Notes : Validates associaitons based on association type.
568 -- Check whether already exists
569 -- Check whether site exists for the supplier
570 -- Check whether Primary is active
571 -- Check whether site already assigned for item-site-org
572 -- Check whether item is assigned in org
573 -- Check whether duplicate primary in the same batch
574 -- Set other primary to 'N'
575 -- End of comments
576 */
577 PROCEDURE validate_associations(p_batch_id IN NUMBER)
578 IS
579 BEGIN
580 UPDATE ego_item_associations_intf eiai
581 SET process_flag = G_REC_ORG_NO_ACCESS
582 WHERE eiai.batch_id = p_batch_id
583 AND eiai.process_flag = G_REC_IN_PROCESS
584 AND NOT EXISTS
585 (
586 SELECT 1
587 FROM org_access_view oav
588 WHERE oav.organization_id = eiai.organization_id
589 AND oav.responsibility_id = FND_PROFILE.Value('RESP_ID')
590 AND oav.resp_application_id = FND_PROFILE.Value('RESP_APPL_ID')
591 );
592 UPDATE ego_item_associations_intf eiai
593 SET process_flag = G_REC_ALREADY_ASSIGNED
594 WHERE eiai.batch_id = p_batch_id
595 AND eiai.process_flag = G_REC_IN_PROCESS
596 AND eiai.transaction_type = G_CREATE
597 AND EXISTS
598 (
599 SELECT 1
600 FROM ego_item_associations eia
601 WHERE eia.inventory_item_id = eiai.inventory_item_id
602 AND eia.organization_id = eiai.organization_id
603 AND eia.data_level_id = eiai.data_level_id
604 AND eia.pk1_value = eiai.pk1_value
605 AND ( ( eia.pk2_value IS NULL AND eiai.pk2_value IS NULL )
606 OR ( eia.pk2_value = eiai.pk2_value )
607 )
608 );
609 UPDATE ego_item_associations_intf eiai
610 SET process_flag = G_REC_ASSOC_SITE_NOT_EXISTS
611 WHERE eiai.batch_id = p_batch_id
612 AND eiai.process_flag = G_REC_IN_PROCESS
613 AND eiai.transaction_type = G_CREATE
614 AND NOT EXISTS
615 (
616 SELECT 1
617 FROM ap_supplier_sites_all assa
618 WHERE assa.vendor_id = eiai.pk1_value
619 AND assa.org_id = fnd_profile.value('ORG_ID')
620 AND nvl(assa.inactive_date,SYSDATE + 1)>SYSDATE --BUG11072046
621 );
622 UPDATE ego_item_associations_intf eiai
623 SET process_flag = G_REC_ASSOC_ITEM_NOT_IN_ORG
624 WHERE eiai.batch_id = p_batch_id
625 AND eiai.process_flag = G_REC_IN_PROCESS
626 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
627 AND eiai.transaction_type = G_CREATE
628 AND NOT EXISTS
629 (
630 SELECT 1
631 FROM mtl_system_items_b_kfv msibk
632 WHERE msibk.inventory_item_id = eiai.inventory_item_id
633 AND msibk.organization_id = eiai.organization_id
634 );
635 -- Primary has been set in Create-Create or Create-Update or Update - Update
636 /*
637 UPDATE ego_item_associations_intf eiai
638 SET process_flag = G_REC_DUPLICATE_PRIMARY
639 WHERE eiai.batch_id = p_batch_id
640 AND eiai.process_flag = G_REC_IN_PROCESS
641 AND eiai.primary_flag = 'Y'
642 AND ( eiai.transaction_type = G_CREATE OR eiai.transaction_type = G_UPDATE)
643 AND exists
644 (
645 SELECT 1
646 FROM ego_item_associations_intf eiai2
647 WHERE eiai2.batch_id = eiai.batch_id
648 AND eiai2.process_flag = eiai.process_flag
649 AND eiai2.primary_flag = eiai.primary_flag
650 AND eiai2.inventory_item_id = eiai.inventory_item_id
651 AND eiai2.organization_id = eiai.organization_id
652 AND eiai2.data_level_id = eiai.data_level_id
653 AND eiai2.pk1_value = eiai.pk1_value
654 AND NVL(eiai2.pk2_value,-1) = NVL(eiai.pk2_value,-1)
655 AND ( eiai2.transaction_type = G_CREATE OR eiai2.transaction_type = G_UPDATE )
656 AND eiai2.ROWID <> eiai.ROWID
657 );
658 */
659 -- If more than one row have primary flag set, then unset for all other records
660 -- except the last one (mode CREATE)
661 UPDATE ego_item_associations_intf eiai
662 SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
663 WHERE eiai.batch_id = p_batch_id
664 AND eiai.process_flag = G_REC_IN_PROCESS
665 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
666 AND eiai.transaction_type = G_CREATE
667 AND eiai.ROWID NOT IN
668 (
669 SELECT MAX(eiai2.ROWID)
670 FROM ego_item_associations_intf eiai2
671 WHERE eiai2.batch_id = p_batch_id
672 AND eiai2.process_flag = G_REC_IN_PROCESS
673 AND eiai2.primary_flag = G_PRIMARY
674 AND eiai2.transaction_type = G_CREATE
675 AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
676 GROUP BY eiai2.inventory_item_id, eiai2.organization_id
677 HAVING count(*) >= 1
678 );
679 UPDATE ego_item_associations_intf eiai
680 SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
681 WHERE eiai.batch_id = p_batch_id
682 AND eiai.process_flag = G_REC_IN_PROCESS
683 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
684 AND eiai.transaction_type = G_CREATE
685 AND eiai.ROWID NOT IN
686 (
687 SELECT MAX(eiai2.ROWID)
688 FROM ego_item_associations_intf eiai2
689 WHERE eiai2.batch_id = p_batch_id
690 AND eiai2.process_flag = G_REC_IN_PROCESS
691 AND eiai2.primary_flag = G_PRIMARY
692 AND eiai2.transaction_type = G_CREATE
693 AND eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
694 GROUP BY eiai2.inventory_item_id, eiai2.organization_id, eiai2.pk1_value
695 HAVING count(*) >= 1
696 );
697
698 -- If more than one row have primary flag set, then unset for all other records
699 -- except the last one (mode UPDATE)
700 UPDATE ego_item_associations_intf eiai
701 SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
702 WHERE eiai.batch_id = p_batch_id
703 AND eiai.process_flag = G_REC_IN_PROCESS
704 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
705 AND eiai.transaction_type = G_UPDATE
706 AND eiai.ROWID NOT IN
707 (
708 SELECT MAX(eiai2.ROWID)
709 FROM ego_item_associations_intf eiai2
710 WHERE eiai2.batch_id = p_batch_id
711 AND eiai2.process_flag = G_REC_IN_PROCESS
712 AND eiai2.primary_flag = G_PRIMARY
713 AND eiai2.transaction_type = G_UPDATE
714 AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
715 GROUP BY eiai2.inventory_item_id, eiai2.organization_id
716 HAVING count(*) >= 1
717 )
718 AND eiai.primary_flag = G_PRIMARY; -- fix for bug#8995869
719
720 UPDATE ego_item_associations_intf eiai
721 SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
722 WHERE eiai.batch_id = p_batch_id
723 AND eiai.process_flag = G_REC_IN_PROCESS
724 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
725 AND eiai.transaction_type = G_UPDATE
726 AND eiai.ROWID NOT IN
727 (
728 SELECT MAX(eiai2.ROWID)
729 FROM ego_item_associations_intf eiai2
730 WHERE eiai2.batch_id = p_batch_id
731 AND eiai2.process_flag = G_REC_IN_PROCESS
732 AND eiai2.primary_flag = G_PRIMARY
733 AND eiai2.transaction_type = G_UPDATE
734 AND eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
735 GROUP BY eiai2.inventory_item_id, eiai2.organization_id, eiai2.pk1_value
736 HAVING count(*) >= 1
737 )
738 AND eiai.primary_flag = G_PRIMARY; -- fix for bug#8995869
739
740
741 -- If the both CREATE and UPDATE has primary flag set then unset for CREATE operations
742 -- because UPDATE is the last operation to be performed
743 UPDATE ego_item_associations_intf eiai
744 SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
745 WHERE eiai.batch_id = p_batch_id
746 AND eiai.process_flag = G_REC_IN_PROCESS
747 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
748 AND eiai.transaction_type = G_CREATE
749 AND EXISTS
750 (
751 SELECT 1
752 FROM ego_item_associations_intf eiai2
753 WHERE eiai2.batch_id = p_batch_id
754 AND eiai2.process_flag = G_REC_IN_PROCESS
755 AND eiai2.inventory_item_id = eiai.inventory_item_id
756 AND eiai2.organization_id = eiai.organization_id
757 AND eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
758 AND eiai2.primary_flag = G_PRIMARY
759 AND eiai2.transaction_type = G_UPDATE
760 );
761
762 UPDATE ego_item_associations_intf eiai
763 SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
764 WHERE eiai.batch_id = p_batch_id
765 AND eiai.process_flag = G_REC_IN_PROCESS
766 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
767 AND eiai.transaction_type = G_CREATE
768 AND EXISTS
769 (
770 SELECT 1
771 FROM ego_item_associations_intf eiai2
772 WHERE eiai2.batch_id = p_batch_id
773 AND eiai2.process_flag = G_REC_IN_PROCESS
774 AND eiai2.inventory_item_id = eiai.inventory_item_id
775 AND eiai2.organization_id = eiai.organization_id
776 AND eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
777 AND eiai2.pk1_value = eiai.pk1_value
778 AND eiai2.primary_flag = G_PRIMARY
779 AND eiai2.transaction_type = G_UPDATE
780 );
781
782 UPDATE ego_item_associations eia
783 SET primary_flag = G_DEFAULT_PRIMARY_FLAG
784 WHERE primary_flag = G_PRIMARY
785 AND eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
786 AND EXISTS
787 ( SELECT 1
788 FROM ego_item_associations_intf eiai
789 WHERE eiai.inventory_item_id = eia.inventory_item_id
790 AND eiai.organization_id = eia.organization_id
791 AND eiai.batch_id = p_batch_id
792 AND eiai.process_flag = G_REC_IN_PROCESS
793 AND eiai.primary_flag = G_PRIMARY
794 AND eiai.transaction_type IN (G_CREATE, G_UPDATE)
795 AND eiai.data_level_id = eia.data_level_id
796 )
797 AND NOT EXISTS
798 ( SELECT 1
799 FROM ego_item_associations_intf eiai
800 WHERE eiai.inventory_item_id = eia.inventory_item_id
801 AND eiai.organization_id = eia.organization_id
802 AND eiai.pk1_value = eia.pk1_value
803 AND eiai.batch_id = p_batch_id
804 AND eiai.process_flag = G_REC_IN_PROCESS
805 AND eiai.primary_flag = G_PRIMARY
806 AND eiai.transaction_type IN (G_CREATE, G_UPDATE)
807 AND eiai.data_level_id = eia.data_level_id
808 );
809 -- Bug 6931470: fix performance issue, using EXIST and IN to substitute UNION operation
810 -- AND EXISTS
811 -- ( SELECT 1
812 -- FROM ego_item_associations_intf eiai
813 -- WHERE eiai.inventory_item_id = eia.inventory_item_id
814 -- AND eiai.organization_id = eia.organization_id
815 -- AND eiai.pk1_value <> eia.pk1_value
816 -- AND eiai.batch_id = p_batch_id
817 -- AND eiai.process_flag = G_REC_IN_PROCESS
818 -- AND eiai.primary_flag = G_PRIMARY
819 -- AND eiai.transaction_type = G_UPDATE
820 -- AND eiai.data_level_id = eia.data_level_id
821 -- UNION ALL
822 -- SELECT 1
823 -- FROM ego_item_associations_intf eiai
824 -- WHERE eiai.inventory_item_id = eia.inventory_item_id
825 -- AND eiai.organization_id = eia.organization_id
826 -- AND eiai.pk1_value <> eia.pk1_value
827 -- AND eiai.batch_id = p_batch_id
828 -- AND eiai.process_flag = G_REC_IN_PROCESS
829 -- AND eiai.primary_flag = G_PRIMARY
830 -- AND eiai.transaction_type = G_CREATE
831 -- AND eiai.data_level_id = eia.data_level_id
832 -- );
833 UPDATE ego_item_associations eia
834 SET primary_flag = G_DEFAULT_PRIMARY_FLAG
835 WHERE eia.primary_flag = G_PRIMARY
836 AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
837 AND EXISTS
838 ( SELECT 1
839 FROM ego_item_associations_intf eiai
840 WHERE eiai.inventory_item_id = eia.inventory_item_id
841 AND eiai.organization_id = eia.organization_id
842 AND eiai.pk1_value = eia.pk1_value
843 AND eiai.batch_id = p_batch_id
844 AND eiai.process_flag = G_REC_IN_PROCESS
845 AND eiai.primary_flag = G_PRIMARY
846 AND eiai.transaction_type IN (G_CREATE, G_UPDATE)
847 AND eiai.data_level_id = eia.data_level_id
848 )
849 AND NOT EXISTS
850 ( SELECT 1
851 FROM ego_item_associations_intf eiai
852 WHERE eiai.inventory_item_id = eia.inventory_item_id
853 AND eiai.organization_id = eia.organization_id
854 AND eiai.pk1_value = eia.pk1_value
855 AND eiai.pk2_value = eia.pk2_value
856 AND eiai.batch_id = p_batch_id
857 AND eiai.process_flag = G_REC_IN_PROCESS
858 AND eiai.primary_flag = G_PRIMARY
859 AND eiai.transaction_type IN (G_CREATE, G_UPDATE)
860 AND eiai.data_level_id = eia.data_level_id
861 );
862 -- Bug 6931470: fix performance issue, using EXIST and IN to substitute UNION operation
863 -- AND EXISTS
864 -- ( SELECT 1
865 -- FROM ego_item_associations_intf eiai
866 -- WHERE eiai.inventory_item_id = eia.inventory_item_id
867 -- AND eiai.organization_id = eia.organization_id
868 -- AND eiai.pk1_value = eia.pk1_value
869 -- AND eiai.pk2_value <> eia.pk2_value
870 -- AND eiai.batch_id = p_batch_id
871 -- AND eiai.process_flag = G_REC_IN_PROCESS
872 -- AND eiai.primary_flag = G_PRIMARY
873 -- AND eiai.transaction_type = G_UPDATE
874 -- AND eiai.data_level_id = eia.data_level_id
875 -- UNION ALL
876 -- SELECT 1
877 -- FROM ego_item_associations_intf eiai
878 -- WHERE eiai.inventory_item_id = eia.inventory_item_id
879 -- AND eiai.organization_id = eia.organization_id
880 -- AND eiai.pk1_value = eia.pk1_value
881 -- AND eiai.pk2_value <> eia.pk2_value
882 -- AND eiai.batch_id = p_batch_id
883 -- AND eiai.process_flag = G_REC_IN_PROCESS
884 -- AND eiai.primary_flag = G_PRIMARY
885 -- AND eiai.transaction_type = G_CREATE
886 -- AND eiai.data_level_id = eia.data_level_id
887 -- );
888 UPDATE ego_item_associations_intf eiai
889 SET process_flag = G_REC_PRIMARY_NOT_ACTIVE
890 WHERE eiai.batch_id = p_batch_id
891 AND eiai.process_flag = G_REC_IN_PROCESS
892 AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
893 AND ( eiai.transaction_type = G_CREATE OR eiai.transaction_type = G_UPDATE )
894 AND ( ( eiai.primary_flag = G_PRIMARY
895 AND (( eiai.status_code <> G_ACTIVE
896 AND eiai.status_code IS NOT NULL ) -- Both attrs are from interface
897 OR EXISTS (SELECT 1 -- Primary flag is being updated and Staus inactive in prod
898 FROM ego_item_associations eia
899 WHERE eia.inventory_item_id = eiai.inventory_item_id
900 AND eia.organization_id = eiai.organization_id
901 AND eia.data_level_id = eiai.data_level_id
902 AND eia.pk1_value = eiai.pk1_value
903 AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
904 AND eiai.status_code IS NULL
905 AND eia.status_code <> G_ACTIVE
906 )
907 )
908 )
909 OR EXISTS -- Status being updated and primary flag is set in prod
910 (
911 SELECT 1
912 FROM ego_item_associations eia
913 WHERE eia.inventory_item_id = eiai.inventory_item_id
914 AND eia.organization_id = eiai.organization_id
915 AND eia.data_level_id = eiai.data_level_id
916 AND eia.pk1_value = eiai.pk1_value
917 AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
918 AND eiai.primary_flag IS NULL
919 AND eiai.status_code <> G_ACTIVE
920 AND eia.primary_flag = G_PRIMARY
921 )
922 );
923 UPDATE ego_item_associations_intf eiai
924 SET process_flag = G_REC_PARENT_NOT_ACTIVE
925 WHERE eiai.batch_id = p_batch_id
926 AND eiai.process_flag = G_REC_IN_PROCESS
927 AND eiai.status_code = G_ACTIVE
928 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
929 AND NOT EXISTS
930 (
931 SELECT 1
932 FROM ego_item_associations eia
933 WHERE eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
934 AND eia.inventory_item_id = eiai.inventory_item_id
935 AND eia.organization_id = eiai.organization_id
936 AND eia.pk1_value = eiai.pk1_value
937 AND eia.status_code = G_ACTIVE
938 UNION ALL
939 SELECT 1
940 FROM ego_item_associations_intf eiai2
941 WHERE eiai2.data_level_id = G_ITEM_SUPPLIER_LEVEL
942 AND eiai2.inventory_item_id = eiai.inventory_item_id
943 AND eiai2.organization_id = eiai.organization_id
944 AND eiai2.pk1_value = eiai.pk1_value
945 AND eiai2.batch_id = p_batch_id
946 AND eiai2.process_flag = G_REC_IN_PROCESS
947 AND eiai2.status_code = G_ACTIVE
948 );
949 UPDATE ego_item_associations_intf eiai
950 SET process_flag = G_REC_PARENT_NOT_ACTIVE
951 WHERE eiai.batch_id = p_batch_id
952 AND eiai.process_flag = G_REC_IN_PROCESS
953 AND eiai.status_code = G_ACTIVE
954 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
955 AND NOT EXISTS
956 (
957 SELECT 1
958 FROM ego_item_associations eia, mtl_parameters mp
959 WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
960 AND eia.inventory_item_id = eiai.inventory_item_id
961 AND eia.organization_id = mp.master_organization_id
962 AND mp.organization_id = eiai.organization_id
963 AND eia.pk1_value = eiai.pk1_value
964 AND eia.pk2_value = eiai.pk2_value
965 AND eia.status_code = G_ACTIVE
966 UNION ALL
967 SELECT 1
968 FROM ego_item_associations_intf eiai2, mtl_parameters mp
969 WHERE eiai2.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
970 AND eiai2.inventory_item_id = eiai.inventory_item_id
971 AND eiai2.organization_id = mp.master_organization_id
972 AND mp.organization_id = eiai.organization_id
973 AND eiai2.pk1_value = eiai.pk1_value
974 AND eiai2.pk2_value = eiai.pk2_value
975 AND eiai2.batch_id = p_batch_id
976 AND eiai2.process_flag = G_REC_IN_PROCESS -- Means there is no validation error
977 AND eiai2.status_code = G_ACTIVE
978 );
979 UPDATE ego_item_associations_intf eiai
980 SET process_flag = G_REC_SUPPLIER_NOT_ASSIGNED
981 WHERE eiai.batch_id = p_batch_id
982 AND eiai.process_flag = G_REC_IN_PROCESS
983 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
984 AND eiai.transaction_type = G_CREATE
985 AND NOT EXISTS
986 (
987 SELECT 1
988 FROM ego_item_associations eia, mtl_parameters mp
989 WHERE eia.inventory_item_id = eiai.inventory_item_id
990 AND eia.organization_id = mp.master_organization_id
991 AND mp.organization_id = eiai.organization_id
992 AND eia.data_level_id = G_ITEM_SUPPLIER_LEVEL
993 AND eia.pk1_value = eiai.pk1_value
994 AND eia.pk2_value IS NULL
995 UNION ALL
996 SELECT 1
997 FROM ego_item_associations_intf eiai1, mtl_parameters mp
998 WHERE eiai1.inventory_item_id = eiai.inventory_item_id
999 AND eiai1.organization_id = mp.master_organization_id
1000 AND mp.organization_id = eiai.organization_id
1001 AND eiai1.data_level_id = G_ITEM_SUPPLIER_LEVEL
1002 AND eiai1.pk1_value = eiai.pk1_value
1003 AND eiai1.pk2_value IS NULL
1004 AND eiai1.process_flag = G_REC_IN_PROCESS -- means there is not validation error
1005 AND eiai1.batch_id = p_batch_id -- BUG 6322084
1006 AND eiai1.transaction_type = G_CREATE -- BUG 6322084
1007 );
1008 UPDATE ego_item_associations_intf eiai
1009 SET process_flag = G_REC_SITE_NOT_ASSIGNED
1010 WHERE eiai.batch_id = p_batch_id
1011 AND eiai.process_flag = G_REC_IN_PROCESS
1012 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1013 AND eiai.transaction_type = G_CREATE
1014 AND NOT EXISTS
1015 (
1016 SELECT 1
1017 FROM ego_item_associations eia, mtl_parameters mp
1018 WHERE eia.inventory_item_id = eiai.inventory_item_id
1019 AND eia.organization_id = mp.master_organization_id
1020 AND mp.organization_id = eiai.organization_id
1021 AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1022 AND eia.pk1_value = eiai.pk1_value
1023 AND eia.pk2_value = eiai.pk2_value
1024 UNION ALL
1025 SELECT 1
1026 FROM ego_item_associations_intf eiai1, mtl_parameters mp
1027 WHERE eiai1.inventory_item_id = eiai.inventory_item_id
1028 AND eiai1.organization_id = mp.master_organization_id
1029 AND mp.organization_id = eiai.organization_id
1030 AND eiai1.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1031 AND eiai1.pk1_value = eiai.pk1_value
1032 AND eiai1.pk2_value = eiai.pk2_value
1033 AND eiai1.process_flag = G_REC_IN_PROCESS -- means there is not validation error
1034 AND eiai1.batch_id = p_batch_id -- BUG 6322084
1035 AND eiai1.transaction_type = G_CREATE -- BUG 6322084
1036 );
1037 END validate_associations;
1038
1039 /*
1040 -- Start of comments
1041 -- API name : perform_delete
1042 -- Type : Private.
1043 -- Function : Performs Delete for 'DELETE' transaction type records.
1044 -- Pre-reqs : None.
1045 -- Parameters :
1046 -- IN : p_batch_id IN NUMBER Required
1047 -- Version : Initial version 1.0
1048 -- Notes : Performs Delete for 'DELETE' transaction type records.
1049 -- delete item-site-org with delete
1050 -- delete item-site-org associated with sites
1051 -- delete item-site-org associated with supplier
1052 -- delete item-site with delete
1053 -- delete item-site associated with supplier
1054 -- delete item-supplier with delete
1055 -- End of comments
1056 */
1057 PROCEDURE perform_delete(p_batch_id IN NUMBER)
1058 IS
1059 BEGIN
1060 -- Delete Item-Site-Org
1061 DELETE
1062 FROM ego_item_associations eia
1063 WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1064 AND EXISTS
1065 (
1066 SELECT 1
1067 FROM ego_item_associations_intf eiai
1068 WHERE eiai.association_id = eia.association_id
1069 AND eiai.batch_id = p_batch_id
1070 AND eiai.process_flag = G_REC_IN_PROCESS
1071 AND eiai.transaction_type = G_DELETE
1072 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1073 );
1074 -- Delete Item-Site-Org associated with Sites
1075 DELETE
1076 FROM ego_item_associations eia
1077 WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1078 AND EXISTS
1079 (
1080 SELECT 1
1081 FROM ego_item_associations_intf eiai, mtl_parameters mp
1082 WHERE eiai.inventory_item_id = eia.inventory_item_id
1083 AND eiai.batch_id = p_batch_id
1084 AND eiai.process_flag = G_REC_IN_PROCESS
1085 AND eiai.organization_id = mp.master_organization_id
1086 AND mp.organization_id = eia.organization_id
1087 AND eiai.pk1_value = eia.pk1_value
1088 AND eiai.pk2_value = eia.pk2_value
1089 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1090 AND eiai.transaction_type = G_DELETE
1091 );
1092 -- Delete Item-Site-Org associations with sites of supplier
1093 DELETE
1094 FROM ego_item_associations eia
1095 WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1096 AND EXISTS
1097 (
1098 SELECT 1
1099 FROM ego_item_associations_intf eiai, mtl_parameters mp
1100 WHERE eiai.inventory_item_id = eia.inventory_item_id
1101 AND eiai.batch_id = p_batch_id
1102 AND eiai.process_flag = G_REC_IN_PROCESS
1103 AND eiai.organization_id = mp.master_organization_id
1104 AND mp.organization_id = eia.organization_id
1105 AND eiai.pk1_value = eia.pk1_value
1106 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1107 AND eiai.transaction_type = G_DELETE
1108 );
1109 DELETE
1110 FROM ego_item_associations eia
1111 WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1112 AND EXISTS
1113 (
1114 SELECT 1
1115 FROM ego_item_associations_intf eiai, mtl_parameters mp
1116 WHERE eiai.association_id = eia.association_id
1117 AND eiai.batch_id = p_batch_id
1118 AND eiai.process_flag = G_REC_IN_PROCESS
1119 AND eiai.organization_id = mp.master_organization_id
1120 AND mp.organization_id = eia.organization_id
1121 AND eiai.pk1_value = eia.pk1_value
1122 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1123 AND eiai.transaction_type = G_DELETE
1124 );
1125 DELETE
1126 FROM ego_item_associations eia
1127 WHERE eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1128 AND EXISTS
1129 (
1130 SELECT 1
1131 FROM ego_item_associations_intf eiai, mtl_parameters mp
1132 WHERE eiai.inventory_item_id = eia.inventory_item_id
1133 AND eiai.batch_id = p_batch_id
1134 AND eiai.process_flag = G_REC_IN_PROCESS
1135 AND eiai.organization_id = mp.master_organization_id
1136 AND mp.organization_id = eia.organization_id
1137 AND eiai.pk1_value = eia.pk1_value
1138 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1139 AND eiai.transaction_type = G_DELETE
1140 );
1141 DELETE
1142 FROM ego_item_associations eia
1143 WHERE EXISTS
1144 (
1145 SELECT 1
1146 FROM ego_item_associations_intf eiai
1147 WHERE eiai.association_id = eia.association_id
1148 AND eiai.batch_id = p_batch_id
1149 AND eiai.process_flag = G_REC_IN_PROCESS
1150 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1151 AND eiai.transaction_type = G_DELETE
1152 );
1153 END perform_delete;
1154
1155 /*
1156 -- Start of comments
1157 -- API name : perform_create
1158 -- Type : Private.
1159 -- Function : Performs create for 'CREATE' transaction type records.
1160 -- Pre-reqs : None.
1161 -- Parameters :
1162 -- IN : p_batch_id IN NUMBER Required
1163 -- Version : Initial version 1.0
1164 -- Notes : Performs create for 'CREATE' transaction type records.
1165 -- insert from interface table which has process_flag as in process
1166 -- End of comments
1167 */
1168 PROCEDURE perform_create (p_batch_id IN NUMBER )
1169 IS
1170 BEGIN
1171 INSERT INTO
1172 ego_item_associations
1173 (
1174 association_id,
1175 organization_id,
1176 inventory_item_id,
1177 pk1_value,
1178 pk2_value,
1179 data_level_id,
1180 status_code,
1181 primary_flag,
1182 created_by,
1183 creation_date,
1184 last_updated_by,
1185 last_update_date,
1186 last_update_login,
1187 request_id,
1188 program_application_id,
1189 program_id,
1190 program_update_date
1191 )
1192 SELECT ego_item_associations_s.NEXTVAL,
1193 eiai.organization_id,
1194 eiai.inventory_item_id,
1195 eiai.pk1_value,
1196 CASE
1197 WHEN eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL THEN
1198 NULL
1199 ELSE
1200 eiai.pk2_value
1201 END AS pk2_value,
1202 eiai.data_level_id,
1203 NVL(eiai.status_code,G_DEFAULT_STATUS_CODE),
1204 CASE
1205 WHEN eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL THEN
1206 NULL
1207 ELSE
1208 NVL(eiai.primary_flag,G_DEFAULT_PRIMARY_FLAG)
1209 END AS primary_flag,
1210 G_USER_ID,
1211 G_SYSDATE,
1212 G_USER_ID,
1213 G_SYSDATE,
1214 G_LOGIN_ID,
1215 G_REQUEST_ID,
1216 G_PROG_APPID,
1217 G_PROG_ID,
1218 G_SYSDATE
1219 FROM ego_item_associations_intf eiai
1220 WHERE eiai.batch_id = p_batch_id
1221 AND eiai.process_flag = G_REC_IN_PROCESS
1222 AND eiai.transaction_type = G_CREATE
1223 AND NOT EXISTS
1224 (
1225 SELECT 1
1226 FROM ego_item_associations eia1
1227 WHERE eia1.inventory_item_id = eiai.inventory_item_id
1228 AND eia1.organization_id = eiai.organization_id
1229 AND eia1.data_level_id = eiai.data_level_id
1230 AND eia1.pk1_value = eiai.pk1_value
1231 AND NVL(eia1.pk2_value,-1) = NVL(eiai.pk2_value,-1)
1232 );
1233
1234 END perform_create;
1235
1236 /*
1237 -- Start of comments
1238 -- API name : perform_update
1239 -- Type : Private.
1240 -- Function : Performs update for 'UPDATE' transaction type records.
1241 -- Pre-reqs : None.
1242 -- Parameters :
1243 -- IN : p_batch_id IN NUMBER Required
1244 -- Version : Initial version 1.0
1245 -- Notes : Performs update for 'UPDATE' transaction type records.
1246 -- update primary flag and status code with who columns
1247 -- End of comments
1248 */
1249 PROCEDURE perform_update ( p_batch_id IN NUMBER )
1250 IS
1251 BEGIN
1252 UPDATE ego_item_associations eia
1253 SET (primary_flag, status_code, last_updated_by, last_update_date, last_update_login, request_id) =
1254 ( SELECT NVL(eiai.primary_flag, eia.primary_flag)
1255 ,NVL(eiai.status_code, eia.status_code)
1256 ,G_USER_ID
1257 ,G_SYSDATE
1258 ,G_LOGIN_ID
1259 ,G_REQUEST_ID
1260 FROM ego_item_associations_intf eiai
1261 WHERE eiai.association_id = eia.association_id
1262 AND eiai.batch_id = p_batch_id
1263 AND eiai.process_flag = G_REC_IN_PROCESS
1264 AND eiai.transaction_type = G_UPDATE
1265 AND ROWNUM = 1
1266 )
1267 WHERE EXISTS
1268 (
1269 SELECT 1
1270 FROM ego_item_associations_intf eiai
1271 WHERE eiai.batch_id = p_batch_id
1272 AND eiai.process_flag = G_REC_IN_PROCESS
1273 AND eiai.association_id = eia.association_id
1274 AND eiai.transaction_type = G_UPDATE
1275 );
1276 -- Update the status to child associations
1277 UPDATE ego_item_associations eia
1278 SET (status_code, primary_flag, last_updated_by, last_update_date, last_update_login, request_id) =
1279 ( SELECT eiai.status_code
1280 ,G_DEFAULT_PRIMARY_FLAG
1281 ,G_USER_ID
1282 ,G_SYSDATE
1283 ,G_LOGIN_ID
1284 ,G_REQUEST_ID
1285 FROM ego_item_associations_intf eiai
1286 WHERE eiai.inventory_item_id = eia.inventory_item_id
1287 AND eiai.organization_id = eia.organization_id
1288 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1289 AND eiai.pk1_value = eia.pk1_value
1290 AND eiai.batch_id = p_batch_id
1291 AND eiai.process_flag = G_REC_IN_PROCESS
1292 AND ROWNUM = 1
1293 )
1294 WHERE EXISTS
1295 (
1296 SELECT 1
1297 FROM ego_item_associations_intf eiai
1298 WHERE eiai.inventory_item_id = eia.inventory_item_id
1299 AND eiai.organization_id = eia.organization_id
1300 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1301 -- AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL -- Bug#6927009
1302 AND eiai.pk1_value = eia.pk1_value
1303 AND eiai.batch_id = p_batch_id
1304 AND eiai.process_flag = G_REC_IN_PROCESS
1305 AND eiai.status_code <> G_ACTIVE
1306 )
1307 AND eia.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL; --Bug#6927009
1308 UPDATE ego_item_associations eia
1309 SET (status_code, last_updated_by, last_update_date, last_update_login, request_id) =
1310 ( SELECT eiai.status_code
1311 ,G_USER_ID
1312 ,G_SYSDATE
1313 ,G_LOGIN_ID
1314 ,G_REQUEST_ID
1315 FROM ego_item_associations_intf eiai, mtl_parameters mp
1316 WHERE eiai.inventory_item_id = eia.inventory_item_id
1317 AND eiai.organization_id = mp.master_organization_id
1318 AND mp.organization_id = eia.organization_id
1319 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1320 AND eiai.pk1_value = eia.pk1_value
1321 AND eiai.batch_id = p_batch_id
1322 AND eiai.process_flag = G_REC_IN_PROCESS
1323 AND ROWNUM = 1
1324 )
1325 WHERE EXISTS
1326 (
1327 SELECT 1
1328 FROM ego_item_associations_intf eiai, mtl_parameters mp
1329 WHERE eiai.inventory_item_id = eia.inventory_item_id
1330 AND eiai.organization_id = mp.master_organization_id
1331 AND eia.organization_id = mp.organization_id
1332 AND eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL
1333 -- AND eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL --Bug#6927009
1334 AND eiai.pk1_value = eia.pk1_value
1335 AND eiai.batch_id = p_batch_id
1336 AND eiai.process_flag = G_REC_IN_PROCESS
1337 AND eiai.status_code <> G_ACTIVE
1338 )
1339 AND eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL; --Bug#6927009
1340
1341 UPDATE ego_item_associations eia
1342 SET (status_code, last_updated_by, last_update_date, last_update_login, request_id) =
1343 ( SELECT eiai.status_code
1344 ,G_USER_ID
1345 ,G_SYSDATE
1346 ,G_LOGIN_ID
1347 ,G_REQUEST_ID
1348 FROM ego_item_associations_intf eiai, mtl_parameters mp
1349 WHERE eiai.inventory_item_id = eia.inventory_item_id
1350 AND eiai.organization_id = mp.master_organization_id
1351 AND eia.organization_id = mp.organization_id
1352 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1353 AND eiai.pk1_value = eia.pk1_value
1354 AND eiai.pk2_value = eia.pk2_value
1355 AND eiai.batch_id = p_batch_id
1356 AND eiai.process_flag = G_REC_IN_PROCESS
1357 AND ROWNUM = 1
1358 )
1359 WHERE EXISTS
1360 (
1361 SELECT 1
1362 FROM ego_item_associations_intf eiai, mtl_parameters mp
1363 WHERE eiai.inventory_item_id = eia.inventory_item_id
1364 AND eiai.organization_id = mp.master_organization_id
1365 AND mp.organization_id = eia.organization_id
1366 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1367 -- AND eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL --Bug#6927009
1368 AND eiai.pk1_value = eia.pk1_value
1369 AND eiai.pk2_value = eia.pk2_value
1370 AND eiai.batch_id = p_batch_id
1371 AND eiai.process_flag = G_REC_IN_PROCESS
1372 AND eiai.status_code <> G_ACTIVE
1373 )
1374 AND eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL; --Bug#6927009
1375 END perform_update;
1376
1377 /*
1378 -- Start of comments
1379 -- API name : insert_errors
1380 -- Type : Private.
1381 -- Function : Insert specific error messages for error records.
1382 -- Pre-reqs : None.
1383 -- Parameters :
1384 -- IN : p_batch_id IN NUMBER Required
1385 -- Version : Initial version 1.0
1386 -- Notes : Insert specific error messages for error records.
1387 -- 1. set the message names based on record status value
1388 -- 2. insert the errors
1389 -- End of comments
1390 */
1391 PROCEDURE insert_errors (p_batch_id IN NUMBER)
1392 IS
1393 CURSOR l_err_rows_csr(p_batch_id IN NUMBER)
1394 IS
1395 SELECT process_flag, transaction_id, organization_id,
1396 CASE
1397 WHEN data_level_id IS NOT NULL
1398 THEN
1399 (
1400 SELECT user_data_level_name
1401 FROM ego_data_level_tl edlt
1402 WHERE edlt.data_level_id = eiai.data_level_id
1403 AND edlt.language = USERENV('LANG')
1404 )
1405 ELSE
1406 (
1407 SELECT user_data_level_name
1408 FROM ego_data_level_vl eldt
1409 WHERE eldt.application_id = 431
1410 AND eldt.attr_group_type = 'EGO_ITEMMGMT_GROUP'
1411 AND eldt.data_level_name = eiai.data_level_name
1412 )
1413 END user_data_level_name,
1414 CASE
1415 WHEN supplier_name IS NOT NULL
1416 THEN supplier_name
1417 ELSE
1418 (
1419 SELECT vendor_name
1420 FROM ap_suppliers aas
1421 WHERE aas.vendor_id = eiai.pk1_value
1422 )
1423 END AS supplier_name,
1424 CASE
1425 WHEN supplier_site_name IS NOT NULL
1426 THEN supplier_site_name
1427 ELSE
1428 (
1429 SELECT vendor_site_code
1430 FROM ap_supplier_sites_all assa
1431 WHERE assa.vendor_site_id = eiai.pk2_value
1432 )
1433 END AS supplier_site_name,
1434 data_level_name
1435 FROM ego_item_associations_intf eiai
1436 WHERE eiai.batch_id = p_batch_id
1437 AND eiai.process_flag >= G_REC_MISSING_REQ_VALUE;
1438 l_err_msg VARCHAR2(2000);
1439 BEGIN
1440 FOR l_err_rec IN l_err_rows_csr(p_batch_id)
1441 LOOP
1442 IF l_err_rec.process_flag = G_REC_MISSING_REQ_VALUE THEN
1443 fnd_message.set_name('EGO','EGO_ASSOC_MISSING_REQ_VALUE');
1444 ELSIF l_err_rec.process_flag = G_REC_INVALID_TRAN_TYPE THEN
1445 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_TRAN_TYPE');
1446 ELSIF l_err_rec.process_flag = G_REC_INVALID_MASTER_ORG THEN
1447 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_MASTER_ORG');
1448 ELSIF l_err_rec.process_flag = G_REC_INVALID_ORG THEN
1449 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_ORG');
1450 ELSIF l_err_rec.process_flag = G_REC_INVALID_ITEM THEN
1451 fnd_message.set_name('EGO','EGO_ASSOC_ITEM_NOT_IN_ORG');
1452 ELSIF l_err_rec.process_flag = G_REC_INVALID_PK1_VALUE THEN
1453 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_PK1_VALUE');
1454 ELSIF l_err_rec.process_flag = G_REC_INVALID_PK2_VALUE THEN
1455 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_PK2_VALUE');
1456 ELSIF l_err_rec.process_flag = G_REC_INVALID_ASSOC_TYPE THEN
1457 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_ASSOC_TYPE');
1458 ELSIF l_err_rec.process_flag = G_REC_INVALID_STATUS THEN
1459 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_STATUS');
1460 ELSIF l_err_rec.process_flag = G_REC_INVALID_PRIMARY THEN
1461 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_PRIMARY');
1462 ELSIF l_err_rec.process_flag = G_REC_ASSOCIATION_NOT_EXISTS THEN
1463 fnd_message.set_name('EGO','EGO_ASSOC_NOT_EXISTS');
1464 ELSIF l_err_rec.process_flag = G_REC_ALREADY_ASSIGNED THEN
1465 fnd_message.set_name('EGO','EGO_ASSOC_ALREADY_ASSIGNED');
1466 ELSIF l_err_rec.process_flag = G_REC_ASSOC_SITE_NOT_EXISTS THEN
1467 fnd_message.set_name('EGO','EGO_ASSOC_INVALID_PK2_VALUE');
1468 ELSIF l_err_rec.process_flag = G_REC_ASSOC_ITEM_NOT_IN_ORG THEN
1469 fnd_message.set_name('EGO','EGO_ASSOC_ITEM_NOT_IN_ORG');
1470 ELSIF l_err_rec.process_flag = G_REC_PARENT_NOT_ASSIGNED THEN
1471 fnd_message.set_name('EGO','EGO_ASSOC_PARENT_NOT_ASSIGNED');
1472 ELSIF l_err_rec.process_flag = G_REC_PARENT_NOT_ACTIVE THEN
1473 fnd_message.set_name('EGO','EGO_ASSOC_PARENT_NOT_ACTIVE');
1474 ELSIF l_err_rec.process_flag = G_REC_PRIMARY_NOT_ACTIVE THEN
1475 fnd_message.set_name('EGO','EGO_ASSOC_PRIMARY_NOT_ACTIVE');
1476 /*
1477 ELSIF l_err_rec.process_flag = G_REC_DUPLICATE THEN
1478 fnd_message.set_name('EGO','EGO_ASSOC_DUPLICATE');
1479 fnd_message.set_token('TRANSACTION_ID',l_err_rec.transaction_id);
1480 fnd_message.set_token('ITEM_NAME',l_err_rec.item_number);
1481 fnd_message.set_token('ORG_CODE',l_err_rec.organization_code);
1482 fnd_message.set_token('DATA_LEVEL',l_err_rec.data_level_id); -- Do we allow to enter user enterable field
1483 */
1484 ELSIF l_err_rec.process_flag = G_REC_NO_CREATE_ASSOC_PRIV THEN
1485 fnd_message.set_name('EGO','EGO_ASSOC_NO_CREATE_ASSOC_PRIV');
1486 ELSIF l_err_rec.process_flag = G_REC_NO_EDIT_ASSOC_PRIV THEN
1487 fnd_message.set_name('EGO','EGO_ASSOC_NO_EDIT_ASSOC_PRIV');
1488 ELSIF l_err_rec.process_flag = G_REC_NO_SUPPL_ACCESS_PRIV THEN
1489 fnd_message.set_name('EGO','EGO_ASSOC_NO_SUPPL_ACCESS_PRIV');
1490 ELSIF l_err_rec.process_flag = G_REC_SUPPLIER_NOT_ASSIGNED THEN
1491 fnd_message.set_name('EGO','EGO_ASSOC_SUPPL_NOT_ASSIGNED');
1492 ELSIF l_err_rec.process_flag = G_REC_SITE_NOT_ASSIGNED THEN
1493 fnd_message.set_name('EGO','EGO_ASSOC_SITE_NOT_ASSIGNED');
1494 ELSIF l_err_rec.process_flag = G_REC_NO_EDIT_ITEM_ORG_PRIV THEN
1495 fnd_message.set_name('EGO','EGO_ASSOC_NO_EDIT_ITEMORG_PRIV');
1496 /*
1497 ELSIF l_err_rec.process_flag = G_REC_DUPLICATE_PRIMARY THEN
1498 fnd_message.set_name('EGO','EGO_ASSOC_DUPLICATE_PRIMARY');
1499 fnd_message.set_token('TRANSACTION_ID',l_err_rec.transaction_id);
1500 fnd_message.set_token('PK1_VALUE',l_err_rec.pk1_value);
1501 fnd_message.set_token('PK2_VALUE',l_err_rec.pk2_value);
1502 */
1503 END IF;
1504 l_err_msg := l_err_rec.user_data_level_name || ':::'||l_err_rec.supplier_name||':::';
1505 IF l_err_rec.data_level_name <> G_ITEM_SUP_LEVEL_NAME THEN
1506 l_err_msg := l_err_msg || l_err_rec.supplier_site_name || ':::';
1507 END IF;
1508 l_err_msg := l_err_msg||fnd_message.get();
1509 fnd_msg_pub.add;
1510 INSERT INTO mtl_interface_errors
1511 ( transaction_id
1512 , organization_id
1513 , error_message
1514 , message_type
1515 , table_name
1516 , bo_identifier
1517 , last_update_date
1518 , last_updated_by
1519 , creation_date
1520 , created_by
1521 , request_id
1522 )
1523 VALUES
1524 (
1525 l_err_rec.transaction_id
1526 , l_err_rec.organization_id
1527 , l_err_msg
1528 , 'E'
1529 , 'EGO_ITEM_ASSOCIATIONS_INTF'
1530 , 'ITEM_ASSOC'
1531 , G_SYSDATE
1532 , G_USER_ID
1533 , G_SYSDATE
1534 , G_USER_ID
1535 , G_REQUEST_ID
1536 );
1537 /*
1538 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1539 fnd_log.string(log_level => p_log_level
1540 ,module => G_DEBUG_LOG_HEAD||p_module
1541 ,message => p_message
1542 );
1543 END IF;
1544 */
1545 --
1546 -- writing to concurrent log
1547 --
1548 /*
1549 IF G_REQUEST_ID <> -1
1550 --AND p_log_level >= G_DEBUG_LEVEL_PROCEDURE
1551 THEN
1552 FND_FILE.put_line(which => FND_FILE.LOG
1553 ,buff => '['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')
1554 ||'] '||p_message);
1555 END IF;
1556 */
1557
1558
1559 END LOOP;
1560 UPDATE ego_item_associations_intf
1561 SET process_flag = G_REC_SUCCESS
1562 WHERE batch_id = p_batch_id
1563 AND process_flag = G_REC_IN_PROCESS;
1564 UPDATE ego_item_associations_intf
1565 SET process_flag = G_REC_ERROR
1566 WHERE batch_id = p_batch_id
1567 AND process_flag >= G_REC_UNEXPECTED_ERROR;
1568 END insert_errors;
1569
1570 -- Start of comments
1571 -- API name : pre_process
1572 -- Type : Private.
1573 -- Function :
1574 -- Pre-reqs : None
1575 -- Parameters :
1576 -- IN : p_api_version IN NUMBER Required
1577 -- p_batch_id IN NUMBER Required
1578 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
1579 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
1580 -- Version : Initial version 1.0
1581 -- Notes : i) Inserts rows into ego_item_associations_intf for the new SKU's which
1582 -- are getting created.
1583 -- ii) Inserts rows into ego_item_associations_intf for the pack hierarchy
1584 -- if there exists a packaging hierarchy for the item association's item.
1585 -- iii) Converts the processing independent values to Ids
1586 -- a) Master Org Code and Master Org Id for ITEM_SUP and ITEM_SUP_SITE
1587 -- b) Org Code and Org Id for ITEM_SUP_SITE_ORG
1588 -- c) Convert Pk1_Name and Pk2_Name
1589 --
1590 -- End of comments
1591 PROCEDURE pre_process
1592 (
1593 p_api_version IN NUMBER
1594 ,p_batch_id IN NUMBER
1595 ,x_return_status OUT NOCOPY VARCHAR2
1596 ,x_msg_count OUT NOCOPY NUMBER
1597 ,x_msg_data OUT NOCOPY VARCHAR2
1598 )
1599 IS
1600 l_organization_id NUMBER;
1601 l_api_name CONSTANT VARCHAR2(30) := 'pre_process';
1602 l_api_version CONSTANT NUMBER := 1.0;
1603 l_data_level_id NUMBER := NULL;
1604 l_default_option_code VARCHAR2(50) := NULL;
1605 BEGIN
1606 SAVEPOINT pre_process_pub;
1607 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1608 set_globals();
1609 write_log_message(' ego_item_associations_pub.pre_process Batch Id ' || p_batch_id);
1610 -- Set the transaction type to UPPER case..
1611 UPDATE ego_item_associations_intf
1612 SET transaction_type = UPPER(transaction_type)
1613 WHERE batch_id = p_batch_id
1614 AND process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH);
1615 UPDATE ego_item_associations_intf eiai
1616 SET data_level_id = G_ITEM_SUPPLIER_LEVEL
1617 WHERE eiai.batch_id = p_batch_id
1618 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1619 AND eiai.data_level_name = G_ITEM_SUP_LEVEL_NAME
1620 AND eiai.transaction_type = G_CREATE;
1621 UPDATE ego_item_associations_intf eiai
1622 SET data_level_id = G_ITEM_SUPPLIERSITE_LEVEL
1623 WHERE eiai.batch_id = p_batch_id
1624 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1625 AND eiai.data_level_name = G_ITEM_SUP_SITE_LEVEL_NAME
1626 AND eiai.transaction_type = G_CREATE;
1627 UPDATE ego_item_associations_intf eiai
1628 SET data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1629 WHERE eiai.batch_id = p_batch_id
1630 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1631 AND eiai.data_level_name = G_ITEM_SUP_SITE_ORG_LEVEL_NAME
1632 AND eiai.transaction_type = G_CREATE;
1633
1634 -- Convert the master org values. Convert Organization Code to Organization Id
1635 UPDATE ego_item_associations_intf eiai
1636 SET organization_id = ( SELECT mp.organization_id
1637 FROM mtl_parameters mp
1638 WHERE mp.organization_code = eiai.organization_code
1639 AND mp.master_organization_id = mp.organization_id)
1640 WHERE eiai.batch_id = p_batch_id
1641 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1642 AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL )
1643 AND eiai.organization_id IS NULL
1644 AND eiai.organization_code IS NOT NULL;
1645
1646 -- Correct the organization id to master organization for double intersections
1647 UPDATE ego_item_associations_intf eiai
1648 SET organization_id = ( SELECT mp.master_organization_id
1649 FROM mtl_parameters mp
1650 WHERE mp.organization_id = eiai.organization_id)
1651 WHERE eiai.batch_id = p_batch_id
1652 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1653 AND ( eiai.data_level_id = G_ITEM_SUPPLIER_LEVEL OR eiai.data_level_id = G_ITEM_SUPPLIERSITE_LEVEL );
1654
1655 -- Convert the master org values. Convert Organization Code to Organization Id
1656 UPDATE ego_item_associations_intf eiai
1657 SET organization_id = ( SELECT mp.organization_id
1658 FROM mtl_parameters mp
1659 WHERE mp.organization_code = eiai.organization_code)
1660 WHERE eiai.batch_id = p_batch_id
1661 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1662 AND eiai.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
1663 AND eiai.organization_id IS NULL
1664 AND eiai.organization_code IS NOT NULL;
1665 -- Convert the organization id to code in order to throw the errors for rules
1666 UPDATE ego_item_associations_intf eiai
1667 SET organization_code = ( SELECT mp.organization_code
1668 FROM mtl_parameters mp
1669 WHERE mp.organization_id = eiai.organization_id
1670 )
1671 WHERE eiai.batch_id = p_batch_id
1672 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1673 AND eiai.organization_code IS NULL
1674 AND eiai.organization_id IS NOT NULL;
1675
1676 UPDATE ego_item_associations_intf eiai
1677 SET pk1_value = ( SELECT vendor_id
1678 FROM ap_suppliers aas
1679 WHERE aas.segment1 = eiai.supplier_number
1680 and NVL(aas.end_date_active,SYSDATE+1) > SYSDATE --bug11072046
1681 )
1682 WHERE eiai.batch_id = p_batch_id
1683 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1684 AND eiai.pk1_value IS NULL
1685 AND eiai.supplier_number IS NOT NULL;
1686 UPDATE ego_item_associations_intf eiai
1687 SET pk1_value = ( SELECT vendor_id
1688 FROM ap_suppliers aas
1689 WHERE aas.vendor_name = eiai.supplier_name
1690 and NVL(aas.end_date_active,SYSDATE+1) > SYSDATE --bug11072046
1691 )
1692 WHERE eiai.batch_id = p_batch_id
1693 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1694 AND eiai.pk1_value IS NULL
1695 AND eiai.supplier_name IS NOT NULL
1696 AND eiai.supplier_number IS NULL;
1697 UPDATE ego_item_associations_intf eiai
1698 SET supplier_name = ( SELECT vendor_name
1699 FROM ap_suppliers aas
1700 WHERE aas.vendor_id = eiai.pk1_value
1701 )
1702 WHERE eiai.batch_id = p_batch_id
1703 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1704 AND eiai.supplier_name IS NULL
1705 AND eiai.pk1_value IS NOT NULL;
1706 UPDATE ego_item_associations_intf eiai
1707 SET pk2_value = NVL(
1708 ( SELECT vendor_site_id
1709 FROM ap_suppliers aas, ap_supplier_sites_all asa
1710 WHERE aas.vendor_id = asa.vendor_id
1711 AND asa.vendor_site_code = eiai.supplier_site_name
1712 AND asa.vendor_id = eiai.pk1_value -- BUG 6322084
1713 AND asa.org_id = fnd_profile.value('ORG_ID')
1714 AND nvl(asa.inactive_date,SYSDATE + 1)>SYSDATE --BUG11072046
1715 )
1716 , -1)
1717 WHERE eiai.batch_id = p_batch_id
1718 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1719 AND eiai.pk2_value IS NULL
1720 AND eiai.supplier_site_name IS NOT NULL;
1721 UPDATE ego_item_associations_intf eiai
1722 SET supplier_site_name = ( SELECT vendor_site_code
1723 FROM ap_supplier_sites_all asa
1724 WHERE asa.vendor_site_id = eiai.pk2_value
1725 )
1726 WHERE eiai.batch_id = p_batch_id
1727 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1728 AND eiai.supplier_site_name IS NULL
1729 AND eiai.pk2_value IS NOT NULL;
1730
1731 UPDATE ego_item_associations_intf eiai
1732 SET inventory_item_id = ( SELECT inventory_item_id
1733 FROM mtl_system_items_b_kfv msibk
1734 WHERE msibk.organization_id = eiai.organization_id
1735 AND msibk.concatenated_segments = eiai.item_number
1736 )
1737 WHERE eiai.batch_id = p_batch_id
1738 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1739 AND eiai.inventory_item_id IS NULL
1740 AND eiai.item_number IS NOT NULL;
1741 -- If Row EXISTS in production the its UPDATE
1742 UPDATE ego_item_associations_intf eiai
1743 SET transaction_type = G_UPDATE
1744 WHERE eiai.batch_id = p_batch_id
1745 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1746 AND eiai.transaction_type = G_SYNC
1747 AND EXISTS
1748 (
1749 SELECT 1
1750 FROM ego_item_associations eia
1751 WHERE eia.inventory_item_id = eiai.inventory_item_id
1752 AND eia.organization_id = eiai.organization_id
1753 AND eia.data_level_id = eiai.data_level_id
1754 AND eia.pk1_value = eiai.pk1_value
1755 AND NVL(eia.pk2_value,-1) = NVL(eiai.pk2_value,-1)
1756 );
1757 -- Rest of the SYNC are CREATE
1758 UPDATE ego_item_associations_intf eiai
1759 SET transaction_type = G_CREATE
1760 WHERE eiai.batch_id = p_batch_id
1761 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1762 AND eiai.transaction_type = G_SYNC;
1763
1764 UPDATE ego_item_associations_intf eiai
1765 SET eiai.process_flag = G_REC_INVALID_TRAN_TYPE
1766 WHERE eiai.batch_id = p_batch_id
1767 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1768 AND eiai.transaction_type NOT IN (G_CREATE, G_UPDATE, G_DELETE);
1769
1770 -- Bug 6438461. Default the Status and Primary Flag if it is null.
1771 UPDATE ego_item_associations_intf eiai
1772 SET eiai.status_code = G_DEFAULT_STATUS_CODE
1773 WHERE eiai.batch_id = p_batch_id
1774 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1775 AND eiai.status_code IS NULL
1776 AND eiai.transaction_type = G_CREATE;
1777
1778 UPDATE ego_item_associations_intf eiai
1779 SET eiai.primary_flag = G_DEFAULT_PRIMARY_FLAG
1780 WHERE eiai.batch_id = p_batch_id
1781 AND eiai.process_flag IN (G_REC_TO_BE_PROCESSED, G_REC_BEFORE_MATCH)
1782 AND eiai.primary_flag IS NULL
1783 AND eiai.transaction_type = G_CREATE;
1784
1785 FND_MSG_PUB.Count_And_Get
1786 ( p_count => x_msg_count,
1787 p_data => x_msg_data
1788 );
1789 write_log_message(' ego_item_associations_pub.pre_process Msg Count ' || x_msg_count);
1790 write_log_message(' ego_item_associations_pub.pre_process Msg Data ' || x_msg_data);
1791 write_log_message(' ego_item_associations_pub.pre_process Return Status ' || x_return_status);
1792 EXCEPTION
1793 WHEN fnd_api.G_EXC_ERROR THEN
1794 ROLLBACK TO pre_process_pub;
1795 x_return_status := fnd_api.G_RET_STS_ERROR ;
1796 FND_MSG_PUB.Count_And_Get
1797 ( p_count => x_msg_count ,
1798 p_data => x_msg_data
1799 );
1800 write_log_message(' ego_item_associations_pub.pre_process Error Msg Count ' || x_msg_count);
1801 write_log_message(' ego_item_associations_pub.pre_process Error Msg Data ' || x_msg_data);
1802 write_log_message(' ego_item_associations_pub.pre_process Error Return Status ' || x_return_status);
1803 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1804 --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1805 ROLLBACK TO pre_process_pub;
1806 UPDATE ego_item_associations_intf
1807 SET process_flag = G_REC_UNEXPECTED_ERROR
1808 WHERE batch_id = p_batch_id
1809 AND process_flag = G_REC_TO_BE_PROCESSED;
1810 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1811 FND_MSG_PUB.Count_And_Get
1812 ( p_count => x_msg_count ,
1813 p_data => x_msg_data
1814 );
1815 write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Msg Count ' || x_msg_count);
1816 write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Msg Data ' || x_msg_data);
1817 write_log_message(' ego_item_associations_pub.pre_process Unexpected Error Return Status ' || x_return_status);
1818 WHEN OTHERS THEN
1819 --dbms_output.put_line(' SQLERRM ' || SQLERRM);
1820 ROLLBACK TO pre_process_pub;
1821 UPDATE ego_item_associations_intf
1822 SET process_flag = G_REC_UNEXPECTED_ERROR
1823 WHERE batch_id = p_batch_id
1824 AND process_flag = G_REC_TO_BE_PROCESSED;
1825 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1826 IF FND_MSG_PUB.Check_Msg_Level
1827 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1828 THEN
1829 FND_MSG_PUB.Add_Exc_Msg
1830 ( G_PKG_NAME,
1831 l_api_name
1832 );
1833 END IF;
1834 FND_MSG_PUB.Count_And_Get
1835 ( p_count => x_msg_count,
1836 p_data => x_msg_data
1837 );
1838 write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Count ' || x_msg_count);
1839 write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Msg Data ' || x_msg_data);
1840 write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS Return Status ' || x_return_status);
1841 write_log_message(' ego_item_associations_pub.pre_process WHEN OTHERS SQLERRM ' || SQLERRM);
1842 END pre_process;
1843
1844 -- Start of comments
1845 -- API name : import_item_associations
1846 -- Type : Public.
1847 -- Function : Imports the item associations into the systems.
1848 -- Pre-reqs : i) pre_process should have been called.
1849 -- ii) Rows needs to be populated in EGO.EGO_ITEM_ASSOCIATIONS_INTF.
1850 -- iii) Errors will be grouped based on concurrent program's request id or batch_id.
1851 -- Query the errors using batch_id for non-concurrent program flows and create a batch or use
1852 -- unique batch id in order to group the errors properly.
1853 -- Parameters :
1854 -- IN : p_api_version IN NUMBER Required
1855 -- IN OUT : x_batch_id IN OUT NOCOPY Optional
1856 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
1857 -- x_msg_count OUT NOCOPY NUMBER
1858 -- x_msg_data OUT NOCOPY VARCHAR2
1859 -- Version : Current version 1.0
1860 -- Initial version 1.0
1861 -- Notes :
1862 -- x_batch_id IN OUT NOCOPY Optional if p_data_from_temp_table is not set
1863 -- Returns batch_id of the batch if its not passed.
1864 -- x_return_status OUT NOCOPY VARCHAR2(1) Return status of the program
1865 -- S - Success, E - Error, U - Unexpected Error
1866 -- End of comments
1867 PROCEDURE import_item_associations
1868 ( p_api_version IN NUMBER
1869 ,x_batch_id IN OUT NOCOPY VARCHAR2
1870 ,x_return_status OUT NOCOPY VARCHAR2
1871 ,x_msg_count OUT NOCOPY NUMBER
1872 ,x_msg_data OUT NOCOPY VARCHAR2
1873 )
1874 IS
1875 l_api_name CONSTANT VARCHAR2(30) := 'import_item_associations';
1876 l_api_version CONSTANT NUMBER := 1.0;
1877 --l_msg_count NUMBER := 0;
1878 --l_msg_data VARCHAR2(2000);
1879 BEGIN
1880 -- Set the Global Variables
1881 set_globals();
1882 -- Standard Start of API savepoint
1883 SAVEPOINT import_item_associations_pub;
1884 -- Standard call to check for call compatibility.
1885 IF NOT fnd_api.Compatible_API_Call ( l_api_version,
1886 p_api_version,
1887 l_api_name,
1888 G_PKG_NAME )
1889 THEN
1890 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
1891 END IF;
1892 -- Initialize message list if p_init_msg_list is set to TRUE.
1893 /*
1894 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
1895 FND_MSG_PUB.initialize;
1896 END IF;
1897 */
1898 FND_MSG_PUB.initialize;
1899 -- Initialize API return status to success
1900 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1901 write_log_message(' ego_item_associations_pub.import_item_associations Batch Id ' || x_batch_id);
1902 -- API body
1903
1904 -- Set records status as in process
1905 initialize(x_batch_id);
1906 -- Convert value to ID
1907 convert_values_to_ids(x_batch_id);
1908 -- Check Security
1909 check_security(x_batch_id);
1910 -- validate
1911 validate_associations(x_batch_id);
1912 -- perform_delete
1913 perform_delete(x_batch_id);
1914 -- perform_create
1915 perform_create(x_batch_id);
1916 -- perform_update
1917 perform_update(x_batch_id);
1918 -- insert errors
1919 insert_errors(x_batch_id);
1920
1921 COMMIT WORK;
1922 -- Standard call to get message count and if count is 1, get message info.
1923 FND_MSG_PUB.Count_And_Get
1924 ( p_count => x_msg_count,
1925 p_data => x_msg_data
1926 );
1927 write_log_message(' ego_item_associations_pub.import_item_associations Msg Count ' || x_msg_count);
1928 write_log_message(' ego_item_associations_pub.import_item_associations Msg Data ' || x_msg_data);
1929 write_log_message(' ego_item_associations_pub.import_item_associations Return Status ' || x_return_status);
1930 EXCEPTION
1931 WHEN fnd_api.G_EXC_ERROR THEN
1932 ROLLBACK TO import_item_associations_pub;
1933 x_return_status := fnd_api.G_RET_STS_ERROR ;
1934 FND_MSG_PUB.Count_And_Get
1935 ( p_count => x_msg_count ,
1936 p_data => x_msg_data
1937 );
1938 write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Count ' || x_msg_count);
1939 write_log_message(' ego_item_associations_pub.import_item_associations Error Msg Data ' || x_msg_data);
1940 write_log_message(' ego_item_associations_pub.import_item_associations Error Return Status ' || x_return_status);
1941 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
1942 ROLLBACK TO import_item_associations_pub;
1943 UPDATE ego_item_associations_intf
1944 SET process_flag = G_REC_UNEXPECTED_ERROR
1945 WHERE batch_id = x_batch_id
1946 AND process_flag = G_REC_TO_BE_PROCESSED;
1947 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1948 FND_MSG_PUB.Count_And_Get
1949 ( p_count => x_msg_count ,
1950 p_data => x_msg_data
1951 );
1952 write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Count ' || x_msg_count);
1953 write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Msg Data ' || x_msg_data);
1954 write_log_message(' ego_item_associations_pub.import_item_associations Unexpected Error Return Status ' || x_return_status);
1955 WHEN OTHERS THEN
1956 ROLLBACK TO import_item_associations_pub;
1957 UPDATE ego_item_associations_intf
1958 SET process_flag = G_REC_UNEXPECTED_ERROR
1959 WHERE batch_id = x_batch_id
1960 AND process_flag = G_REC_TO_BE_PROCESSED;
1961 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
1962 IF FND_MSG_PUB.Check_Msg_Level
1963 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1964 THEN
1965 FND_MSG_PUB.Add_Exc_Msg
1966 ( G_PKG_NAME,
1967 l_api_name
1968 );
1969 END IF;
1970 FND_MSG_PUB.Count_And_Get
1971 ( p_count => x_msg_count,
1972 p_data => x_msg_data
1973 );
1974 write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS SQLERRM ' || SQLERRM);
1975 write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Count ' || x_msg_count);
1976 write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msggggg Data ' || x_msg_data);
1977 write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Msg Data ' || x_msg_data);
1978 write_log_message(' ego_item_associations_pub.import_item_associations WHEN OTHERS Return Status ' || x_return_status);
1979 END import_item_associations;
1980
1981 -- Start of comments
1982 -- API name : import_item_associations
1983 -- Type : private.
1984 -- Function : Imports the item associations in the excel import flow.
1985 -- Pre-reqs :
1986 -- i) Rows needs to be populated in EGO.EGO_ITEM_ASSOCIATIONS_INTF if the data is not from temp tables.
1987 -- ii) Errors will be grouped based on concurrent program's request id.
1988 -- Parameters :
1989 -- IN : p_api_version IN NUMBER Required
1990 -- IN OUT : x_batch_id IN OUT NOCOPY Optional
1991 -- OUT : x_errbuf OUT NOCOPY VARCHAR2
1992 -- x_retcode OUT NOCOPY VARCHAR2
1993 -- Version : Current version 1.0
1994 -- Initial version 1.0
1995 -- Notes :
1996 -- x_errbuf Returns the single error message if it is else null.
1997 -- x_retcode 0 - Success, 1 - Warning, 2 - Error
1998 -- End of comments
1999 PROCEDURE import_item_associations
2000 (
2001 p_api_version IN NUMBER
2002 ,x_batch_id IN OUT NOCOPY VARCHAR2
2003 ,x_errbuf OUT NOCOPY VARCHAR2
2004 ,x_retcode OUT NOCOPY VARCHAR2
2005 )
2006 IS
2007 l_return_status VARCHAR2(1);
2008 l_msg_count NUMBER;
2009 BEGIN
2010 import_item_associations
2011 (
2012 p_api_version => p_api_version
2013 ,x_batch_id => x_batch_id
2014 ,x_return_status =>l_return_status
2015 ,x_msg_count => l_msg_count
2016 ,x_msg_data => x_errbuf
2017 );
2018 IF ( l_return_status = fnd_api.G_RET_STS_SUCCESS ) THEN
2019 x_retcode := '0';
2020 ELSIF ( l_return_status = fnd_api.G_RET_STS_ERROR ) THEN
2021 x_retcode := '2';
2022 END IF;
2023 END import_item_associations;
2024
2025
2026 -- Start of comments
2027 -- API name : copy_associations_to_items
2028 -- Type : Private.
2029 -- Function : Insert interface rows for associations for the target items.
2030 -- Pre-reqs : To Item Numbers are all new items. So no associations exist.
2031 -- Parameters :
2032 -- IN : p_api_version IN NUMBER Required
2033 -- p_batch_id IN NUMBER Required
2034 -- p_src_item_id IN NUMBER Required
2035 -- p_data_level_names IN VARCHAR2_TBL_TYPE Required
2036 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
2037 -- x_msg_count OUT NOCOPY NUMBER
2038 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
2039 -- Version : Initial version 1.0
2040 -- Notes : Note text
2041 --
2042 -- End of comments
2043 PROCEDURE copy_associations_to_items
2044 (
2045 p_api_version IN NUMBER
2046 ,p_batch_id IN NUMBER
2047 ,p_src_item_id IN NUMBER
2048 ,p_data_level_names IN VARCHAR2_TBL_TYPE
2049 ,x_return_status OUT NOCOPY VARCHAR2
2050 ,x_msg_count OUT NOCOPY NUMBER
2051 ,x_msg_data OUT NOCOPY VARCHAR2
2052 )
2053 IS
2054 l_src_entity_sql VARCHAR2(32767);
2055 l_dst_entity_sql VARCHAR2(32767);
2056 l_master_org_id NUMBER;
2057 l_api_name CONSTANT VARCHAR2(30) := 'copy_associations_to_items';
2058 l_api_version CONSTANT NUMBER := 1.0;
2059 l_data_level_id NUMBER := NULL;
2060 BEGIN
2061 -- Set the Global Variables
2062 set_globals();
2063 /*
2064 BEGIN
2065 SELECT master_organization_id
2066 INTO l_master_org_id
2067 FROM mtl_parameters
2068 WHERE organization_id = p_from_org_id;
2069 EXCEPTION
2070 WHEN NO_DATA_FOUND THEN
2071 x_return_status := fnd_api.G_RET_STS_ERROR;
2072 RETURN;
2073 END;
2074 */
2075 -- Initialize API return status to success
2076 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2077 SAVEPOINT copy_associations_to_items_pub;
2078 write_log_message(' Before looping data levels ');
2079 FOR I IN p_data_level_names.FIRST..p_data_level_names.LAST
2080 LOOP
2081 write_log_message(' p_data_level_names(I) ' || p_data_level_names(I));
2082 IF ( p_data_level_names(I) = G_ITEM_SUP_LEVEL_NAME ) THEN
2083 l_data_level_id := G_ITEM_SUPPLIER_LEVEL;
2084 ELSIF ( p_data_level_names(I) = G_ITEM_SUP_SITE_LEVEL_NAME ) THEN
2085 l_data_level_id := G_ITEM_SUPPLIERSITE_LEVEL;
2086 ELSIF ( p_data_level_names(I) = G_ITEM_SUP_SITE_ORG_LEVEL_NAME ) THEN
2087 l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2088 END IF;
2089 write_log_message(' l_data_level_id ' || l_data_level_id);
2090 INSERT INTO ego_item_associations_intf
2091 (
2092 BATCH_ID
2093 ,ITEM_NUMBER
2094 ,INVENTORY_ITEM_ID
2095 ,ORGANIZATION_ID
2096 ,PK1_VALUE
2097 ,PK2_VALUE
2098 ,DATA_LEVEL_ID
2099 ,PRIMARY_FLAG
2100 ,STATUS_CODE
2101 ,TRANSACTION_TYPE
2102 ,PROCESS_FLAG
2103 ,TRANSACTION_ID
2104 ,SOURCE_SYSTEM_REFERENCE
2105 ,SOURCE_SYSTEM_ID
2106 ,BUNDLE_ID
2107 ,REQUEST_ID
2108 )
2109 SELECT p_batch_id
2110 ,msii.item_number
2111 ,msii.inventory_item_id
2112 ,msii.organization_id
2113 ,eia.pk1_value
2114 ,eia.pk2_value
2115 ,eia.data_level_id
2116 ,eia.primary_flag
2117 ,eia.status_code
2118 ,G_CREATE
2119 ,G_REC_TO_BE_PROCESSED
2120 ,msii.transaction_id
2121 ,msii.source_system_reference
2122 ,msii.source_system_id
2123 ,msii.bundle_id
2124 ,G_REQUEST_ID
2125 FROM ego_item_associations eia
2126 ,mtl_system_items_interface msii
2127 ,mtl_parameters mp
2128 WHERE eia.inventory_item_id = p_src_item_id
2129 -- AND msii.organization_id = mp.organization_id Copy_Item_Id will be populated only for master org items
2130 --AND mp.organization_id = mp.master_organization_id Copy all triple intersections
2131 AND msii.set_process_id = p_batch_id
2132 AND msii.copy_item_id = p_src_item_id
2133 AND eia.data_level_id = l_data_level_id
2134 AND msii.process_flag = G_REC_TO_BE_PROCESSED
2135 AND eia.organization_id = msii.organization_id
2136 AND msii.organization_id = mp.organization_id;
2137 write_log_message(' ego_item_associations_pub.copy_associations_to_items after insert ');
2138 END LOOP;
2139 FND_MSG_PUB.Count_And_Get
2140 ( p_count => x_msg_count,
2141 p_data => x_msg_data
2142 );
2143 write_log_message(' ego_item_associations_pub.copy_associations_to_items Msg Count ' || x_msg_count);
2144 write_log_message(' ego_item_associations_pub.copy_associations_to_items Msg Data ' || x_msg_data);
2145 write_log_message(' ego_item_associations_pub.copy_associations_to_items Return Status ' || x_return_status);
2146 EXCEPTION
2147 WHEN fnd_api.G_EXC_ERROR THEN
2148 ROLLBACK TO copy_associations_to_items_pub;
2149 x_return_status := fnd_api.G_RET_STS_ERROR ;
2150 FND_MSG_PUB.Count_And_Get
2151 ( p_count => x_msg_count ,
2152 p_data => x_msg_data
2153 );
2154 write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Count ' || x_msg_count);
2155 write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Msg Data ' || x_msg_data);
2156 write_log_message(' ego_item_associations_pub.copy_associations_to_items Error Return Status ' || x_return_status);
2157 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2158 ROLLBACK TO copy_associations_to_items_pub;
2159 UPDATE ego_item_associations_intf
2160 SET process_flag = G_REC_UNEXPECTED_ERROR
2161 WHERE batch_id = p_batch_id
2162 AND process_flag = G_REC_TO_BE_PROCESSED;
2163 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2164 FND_MSG_PUB.Count_And_Get
2165 ( p_count => x_msg_count ,
2166 p_data => x_msg_data
2167 );
2168 write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Count ' || x_msg_count);
2169 write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Msg Data ' || x_msg_data);
2170 write_log_message(' ego_item_associations_pub.copy_associations_to_items Unexpected Error Return Status ' || x_return_status);
2171 WHEN OTHERS THEN
2172 ROLLBACK TO copy_associations_to_items_pub;
2173 UPDATE ego_item_associations_intf
2174 SET process_flag = G_REC_UNEXPECTED_ERROR
2175 WHERE batch_id = p_batch_id
2176 AND process_flag = G_REC_TO_BE_PROCESSED;
2177 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2178 IF FND_MSG_PUB.Check_Msg_Level
2179 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2180 THEN
2181 FND_MSG_PUB.Add_Exc_Msg
2182 ( G_PKG_NAME,
2183 l_api_name
2184 );
2185 END IF;
2186 FND_MSG_PUB.Count_And_Get
2187 ( p_count => x_msg_count,
2188 p_data => x_msg_data
2189 );
2190 write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Count ' || x_msg_count);
2191 write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Msg Data ' || x_msg_data);
2192 write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS Return Status ' || x_return_status);
2193 write_log_message(' ego_item_associations_pub.copy_associations_to_items WHEN OTHERS SQLERRM ' || SQLERRM);
2194 END copy_associations_to_items;
2195
2196 -- Start of comments
2197 -- API name : copy_from_style_to_SKUs
2198 -- Type : Private.
2199 -- Function : Insert interface rows for associations of the style items
2200 -- to the corresponding SKUs.
2201 -- Pre-reqs : None
2202 -- Parameters :
2203 -- IN : p_api_version IN NUMBER Required
2204 -- p_batch_id IN NUMBER Required
2205 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
2206 -- x_msg_count OUT NOCOPY NUMBER
2207 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
2208 -- Version : Initial version 1.0
2209 -- Notes : Note text
2210 --
2211 -- End of comments
2212 PROCEDURE copy_from_style_to_SKUs
2213 (
2214 p_api_version IN NUMBER
2215 ,p_batch_id IN NUMBER
2216 ,x_return_status OUT NOCOPY VARCHAR2
2217 ,x_msg_count OUT NOCOPY NUMBER
2218 ,x_msg_data OUT NOCOPY VARCHAR2
2219 ,p_msii_miri_process_flag IN NUMBER DEFAULT 1 -- Bug 12635842
2220 )
2221 IS
2222 l_api_name CONSTANT VARCHAR2(30) := 'copy_from_style_to_SKUs';
2223 l_api_version CONSTANT NUMBER := 1.0;
2224 l_data_level_id NUMBER := NULL;
2225 l_default_option_code VARCHAR2(50) := NULL;
2226 BEGIN
2227 -- Set the Global Variables
2228 set_globals();
2229 -- Initialize API return status to success
2230 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2231 SAVEPOINT copy_from_style_to_SKUs_pub;
2232 FOR I IN G_DATA_LEVEL_NAMES.FIRST..G_DATA_LEVEL_NAMES.LAST
2233 LOOP
2234 IF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_LEVEL_NAME ) THEN
2235 l_data_level_id := G_ITEM_SUPPLIER_LEVEL;
2236 l_default_option_code := G_ASSIGN_STYLE_SUP_SUPSITE;
2237 ELSIF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_SITE_LEVEL_NAME ) THEN
2238 l_data_level_id := G_ITEM_SUPPLIERSITE_LEVEL;
2239 l_default_option_code := G_ASSIGN_STYLE_SUP_SUPSITE;
2240 ELSIF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_SITE_ORG_LEVEL_NAME ) THEN
2241 l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2242 l_default_option_code := G_ASSIGN_STYLE_SS_ORG;
2243 IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2244 INSERT INTO ego_item_associations_intf
2245 (
2246 BATCH_ID
2247 ,ORGANIZATION_ID
2248 ,ORGANIZATION_CODE
2249 ,ITEM_NUMBER
2250 ,INVENTORY_ITEM_ID
2251 ,PK1_VALUE
2252 ,PK2_VALUE
2253 ,DATA_LEVEL_ID
2254 ,PRIMARY_FLAG
2255 ,STATUS_CODE
2256 ,TRANSACTION_TYPE
2257 ,PROCESS_FLAG
2258 ,TRANSACTION_ID
2259 ,SOURCE_SYSTEM_REFERENCE
2260 ,SOURCE_SYSTEM_ID
2261 ,BUNDLE_ID
2262 ,REQUEST_ID
2263 ,CREATED_BY -- Bug 6459846
2264 )
2265 SELECT p_batch_id
2266 ,mp.organization_id
2267 ,mp.organization_code
2268 ,msii.item_number
2269 ,msii.inventory_item_id
2270 ,eia.pk1_value
2271 ,eia.pk2_value
2272 ,eia.data_level_id
2273 ,eia.primary_flag
2274 ,eia.status_code
2275 ,G_CREATE
2276 ,G_REC_TO_BE_PROCESSED
2277 ,msii.transaction_id
2278 ,msii.source_system_reference
2279 ,msii.source_system_id
2280 ,msii.bundle_id
2281 ,G_REQUEST_ID
2282 ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2283 FROM ego_item_associations eia
2284 ,mtl_system_items_interface msii
2285 ,mtl_parameters mp
2286 ,mtl_system_items_interface msii2
2287 WHERE eia.inventory_item_id = msii2.style_item_id
2288 AND msii.organization_id = mp.organization_id
2289 AND msii.set_process_id = p_batch_id
2290 AND eia.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
2291 AND msii.process_flag = p_msii_miri_process_flag -- Bug 12635842
2292 AND eia.organization_id = mp.organization_id
2293 AND msii.inventory_item_id = msii2.inventory_item_id
2294 AND msii2.organization_id = mp.master_organization_id
2295 AND msii2.style_item_id IS NOT NULL
2296 AND msii2.set_process_id = p_batch_id
2297 AND msii2.process_flag = p_msii_miri_process_flag -- Bug 12635842
2298 AND NOT EXISTS
2299 (
2300 SELECT 1
2301 FROM ego_item_associations_intf eiai1
2302 WHERE eiai1.inventory_item_id = msii.inventory_item_id
2303 AND eiai1.organization_id = msii.organization_id
2304 AND eiai1.data_level_id = G_ITEM_SUPPLIERSITE_ORG_LEVEL
2305 AND eiai1.batch_id = p_batch_id
2306 AND eiai1.process_flag = G_REC_TO_BE_PROCESSED
2307 AND eiai1.pk1_value = eia.pk1_value
2308 AND NVL(eiai1.pk2_value,-1) = NVL(eia.pk2_value,-1)
2309 UNION ALL
2310 SELECT 1
2311 FROM ego_item_associations eia2
2312 WHERE eia2.inventory_item_id = msii.inventory_item_id
2313 AND eia2.organization_id = msii.organization_id
2314 AND eia2.data_level_id = eia.data_level_id
2315 AND eia2.pk1_value = eia.pk1_value
2316 AND NVL(eia2.pk2_value,-1) = NVL(eia.pk2_value,-1)
2317 );
2318 END IF;
2319 END IF;
2320 --dbms_output.put_line(' Defaulting option ' || ego_common_pvt.get_option_value(l_default_option_code) );
2321 IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2322 INSERT INTO ego_item_associations_intf
2323 (
2324 BATCH_ID
2325 ,ORGANIZATION_ID
2326 ,ORGANIZATION_CODE
2327 ,ITEM_NUMBER
2328 ,INVENTORY_ITEM_ID
2329 ,PK1_VALUE
2330 ,PK2_VALUE
2331 ,DATA_LEVEL_ID
2332 ,PRIMARY_FLAG
2333 ,STATUS_CODE
2334 ,TRANSACTION_TYPE
2335 ,PROCESS_FLAG
2336 ,TRANSACTION_ID
2337 ,SOURCE_SYSTEM_REFERENCE
2338 ,SOURCE_SYSTEM_ID
2339 ,BUNDLE_ID
2340 ,REQUEST_ID
2341 ,CREATED_BY -- Bug 6459846
2342 )
2343 SELECT p_batch_id
2344 ,mp.organization_id
2345 ,mp.organization_code
2346 ,msii.item_number
2347 ,msii.inventory_item_id
2348 ,eia.pk1_value
2349 ,eia.pk2_value
2350 ,eia.data_level_id
2351 ,eia.primary_flag
2352 ,eia.status_code
2353 ,G_CREATE
2354 ,G_REC_TO_BE_PROCESSED
2355 ,msii.transaction_id
2356 ,msii.source_system_reference
2357 ,msii.source_system_id
2358 ,msii.bundle_id
2359 ,G_REQUEST_ID
2360 ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2361 FROM ego_item_associations eia
2362 ,mtl_system_items_interface msii
2363 ,mtl_parameters mp
2364 WHERE eia.inventory_item_id = msii.style_item_id
2365 AND msii.organization_id = mp.organization_id
2366 --AND mp.organization_id = mp.master_organization_id
2367 AND msii.set_process_id = p_batch_id
2368 AND eia.data_level_id = l_data_level_id
2369 AND msii.process_flag = p_msii_miri_process_flag -- Bug 12635842
2370 AND eia.organization_id = mp.organization_id
2371 AND NOT EXISTS
2372 (
2373 SELECT 1
2374 FROM ego_item_associations_intf eiai1
2375 WHERE eiai1.inventory_item_id = msii.inventory_item_id
2376 AND eiai1.organization_id = msii.organization_id
2377 AND eiai1.data_level_id = l_data_level_id
2378 AND eiai1.batch_id = p_batch_id
2379 AND eiai1.process_flag = G_REC_TO_BE_PROCESSED
2380 AND eiai1.pk1_value = eia.pk1_value
2381 AND NVL(eiai1.pk2_value,-1) = NVL(eia.pk2_value,-1)
2382 UNION ALL
2383 SELECT 1
2384 FROM ego_item_associations eia2
2385 WHERE eia2.inventory_item_id = msii.inventory_item_id
2386 AND eia2.organization_id = msii.organization_id
2387 AND eia2.data_level_id = eia.data_level_id
2388 AND eia2.pk1_value = eia.pk1_value
2389 AND NVL(eia2.pk2_value,-1) = NVL(eia.pk2_value,-1)
2390 );
2391 -- Copy the rows to existing SKUs
2392 INSERT INTO ego_item_associations_intf
2393 (
2394 BATCH_ID
2395 ,ORGANIZATION_ID
2396 ,ORGANIZATION_CODE
2397 ,ITEM_NUMBER
2398 ,INVENTORY_ITEM_ID
2399 ,PK1_VALUE
2400 ,SUPPLIER_NAME
2401 ,SUPPLIER_NUMBER
2402 ,PK2_VALUE
2403 ,SUPPLIER_SITE_NAME
2404 ,DATA_LEVEL_ID
2405 ,DATA_LEVEL_NAME
2406 ,PRIMARY_FLAG
2407 ,STATUS_CODE
2408 ,TRANSACTION_TYPE
2409 ,PROCESS_FLAG
2410 ,TRANSACTION_ID
2411 ,SOURCE_SYSTEM_REFERENCE
2412 ,SOURCE_SYSTEM_ID
2413 ,BUNDLE_ID
2414 ,REQUEST_ID
2415 ,CREATED_BY -- Bug 6459846
2416 )
2417 SELECT p_batch_id
2418 ,eiai1.organization_id
2419 ,eiai1.organization_code
2420 ,msibk.concatenated_segments
2421 ,msibk.inventory_item_id
2422 ,eiai1.pk1_value
2423 ,eiai1.supplier_name
2424 ,eiai1.supplier_number
2425 ,eiai1.pk2_value
2426 ,eiai1.supplier_site_name
2427 ,eiai1.data_level_id
2428 ,eiai1.data_level_name
2429 ,eiai1.primary_flag
2430 ,eiai1.status_code
2431 ,G_CREATE
2432 ,G_REC_TO_BE_PROCESSED
2433 ,mtl_system_items_interface_s.NEXTVAL
2434 ,NULL
2435 ,NULL
2436 ,NULL
2437 ,G_REQUEST_ID
2438 ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2439 FROM ego_item_associations_intf eiai1
2440 ,mtl_system_items_b_kfv msibk
2441 ,mtl_parameters mp
2442 WHERE eiai1.inventory_item_id = msibk.style_item_id
2443 AND eiai1.organization_id = msibk.organization_id
2444 AND msibk.organization_id = mp.organization_id
2445 -- AND mp.organization_id = mp.master_organization_id
2446 AND eiai1.batch_id = p_batch_id
2447 AND eiai1.data_level_id = l_data_level_id
2448 AND eiai1.process_flag = G_REC_SUCCESS
2449 AND NOT EXISTS
2450 (
2451 SELECT 1
2452 FROM ego_item_associations_intf eiai2
2453 WHERE eiai2.inventory_item_id = msibk.inventory_item_id
2454 AND eiai2.organization_id = msibk.organization_id
2455 AND eiai2.data_level_id = l_data_level_id
2456 AND eiai2.batch_id = p_batch_id
2457 AND eiai2.process_flag = G_REC_TO_BE_PROCESSED
2458 AND eiai2.pk1_value = eiai1.pk1_value
2459 AND NVL(eiai2.pk2_value,-1) = NVL(eiai1.pk2_value,-1)
2460 UNION ALL
2461 SELECT 1
2462 FROM ego_item_associations eia2
2463 WHERE eia2.inventory_item_id = msibk.inventory_item_id
2464 AND eia2.organization_id = msibk.organization_id
2465 AND eia2.data_level_id = eiai1.data_level_id
2466 AND eia2.pk1_value = eiai1.pk1_value
2467 AND NVL(eia2.pk2_value,-1) = NVL(eiai1.pk2_value,-1)
2468 );
2469 END IF;
2470 END LOOP;
2471 FND_MSG_PUB.Count_And_Get
2472 ( p_count => x_msg_count,
2473 p_data => x_msg_data
2474 );
2475 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Msg Count ' || x_msg_count);
2476 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Msg Data ' || x_msg_data);
2477 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Return Status ' || x_return_status);
2478 EXCEPTION
2479 WHEN fnd_api.G_EXC_ERROR THEN
2480 ROLLBACK TO copy_from_style_to_SKUs_pub;
2481 x_return_status := fnd_api.G_RET_STS_ERROR ;
2482 FND_MSG_PUB.Count_And_Get
2483 ( p_count => x_msg_count ,
2484 p_data => x_msg_data
2485 );
2486 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Msg Count ' || x_msg_count);
2487 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Msg Data ' || x_msg_data);
2488 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Error Return Status ' || x_return_status);
2489 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2490 --dbms_output.put_line(' Error Msg ' || SQLERRM);
2491 ROLLBACK TO copy_from_style_to_SKUs_pub;
2492 UPDATE ego_item_associations_intf
2493 SET process_flag = G_REC_UNEXPECTED_ERROR
2494 WHERE batch_id = p_batch_id
2495 AND process_flag = G_REC_TO_BE_PROCESSED;
2496 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2497 FND_MSG_PUB.Count_And_Get
2498 ( p_count => x_msg_count ,
2499 p_data => x_msg_data
2500 );
2501 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Msg Count ' || x_msg_count);
2502 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Msg Data ' || x_msg_data);
2503 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs Unexpected Error Return Status ' || x_return_status);
2504 WHEN OTHERS THEN
2505 --dbms_output.put_line(' Error Msg ' || SQLERRM);
2506 ROLLBACK TO copy_from_style_to_SKUs_pub;
2507 UPDATE ego_item_associations_intf
2508 SET process_flag = G_REC_UNEXPECTED_ERROR
2509 WHERE batch_id = p_batch_id
2510 AND process_flag = G_REC_TO_BE_PROCESSED;
2511 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2512 IF FND_MSG_PUB.Check_Msg_Level
2513 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2514 THEN
2515 FND_MSG_PUB.Add_Exc_Msg
2516 ( G_PKG_NAME,
2517 l_api_name
2518 );
2519 END IF;
2520 FND_MSG_PUB.Count_And_Get
2521 ( p_count => x_msg_count,
2522 p_data => x_msg_data
2523 );
2524 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Count ' || x_msg_count);
2525 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Msg Data ' || x_msg_data);
2526 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS Return Status ' || x_return_status);
2527 write_log_message(' ego_item_associations_pub.copy_from_style_to_SKUs WHEN OTHERS SQLERRM ' || SQLERRM);
2528 END copy_from_style_to_SKUs;
2529
2530 -- Start of comments
2531 -- API name : copy_to_packs
2532 -- Type : Private.
2533 -- Function : Insert interface rows for associations of the pack items
2534 -- to the corresponding pack hierarchy.
2535 -- Pre-reqs : None
2536 -- Parameters :
2537 -- IN : p_api_version IN NUMBER Required
2538 -- p_batch_id IN NUMBER Required
2539 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
2540 -- x_msg_count OUT NOCOPY NUMBER
2541 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
2542 -- Version : Initial version 1.0
2543 -- Notes : Note text
2544 --
2545 -- End of comments
2546 PROCEDURE copy_to_packs
2547 (
2548 p_api_version IN NUMBER
2549 ,p_batch_id IN NUMBER
2550 ,x_return_status OUT NOCOPY VARCHAR2
2551 ,x_msg_count OUT NOCOPY NUMBER
2552 ,x_msg_data OUT NOCOPY VARCHAR2
2553 )
2554 IS
2555 l_api_name CONSTANT VARCHAR2(30) := 'copy_to_packs';
2556 l_api_version CONSTANT NUMBER := 1.0;
2557 l_data_level_id NUMBER := NULL;
2558 l_default_option_code VARCHAR2(50) := NULL;
2559 l_explode_grp_id NUMBER;
2560 l_err_message VARCHAR2(2000);
2561 l_err_code NUMBER;
2562 CURSOR l_expl_csr
2563 IS
2564 SELECT component_item_id
2565 FROM bom_explosions_v;
2566 CURSOR l_pack_item_csr(p_batch_id IN NUMBER)
2567 IS
2568 SELECT msibk.inventory_item_id
2569 ,msibk.organization_id
2570 FROM ego_item_associations_intf eiai, mtl_system_items_b_kfv msibk
2571 WHERE msibk.concatenated_segments = eiai.item_number
2572 AND msibk.organization_id = eiai.organization_id
2573 AND eiai.batch_id = p_batch_id
2574 AND eiai.process_flag = G_REC_TO_BE_PROCESSED
2575 AND EXISTS
2576 (
2577 SELECT 1
2578 FROM bom_structures_b bsb
2579 WHERE bsb.assembly_item_id = msibk.inventory_item_id
2580 AND bsb.assembly_item_id = msibk.inventory_item_id
2581 AND bsb.organization_id = eiai.organization_id
2582 AND bsb.bill_sequence_id = bsb.common_bill_sequence_id
2583 AND bsb.alternate_bom_designator = ego_item_associations_pub.G_PACK_STR_NAME
2584 );
2585 BEGIN
2586 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2587 -- Set the Global Variables
2588 set_globals();
2589 -- Initialize API return status to success
2590 SAVEPOINT copy_to_packs_pub;
2591 FOR I IN G_DATA_LEVEL_NAMES.FIRST..G_DATA_LEVEL_NAMES.LAST
2592 LOOP
2593 IF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_LEVEL_NAME ) THEN
2594 l_data_level_id := G_ITEM_SUPPLIER_LEVEL;
2595 l_default_option_code := G_ASSIGN_PACK_SUPPLIER;
2596 ELSIF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_SITE_LEVEL_NAME ) THEN
2597 l_data_level_id := G_ITEM_SUPPLIERSITE_LEVEL;
2598 l_default_option_code := G_ASSIGN_PACK_SUP_SITE;
2599 ELSIF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_SITE_ORG_LEVEL_NAME ) THEN
2600 l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2601 l_default_option_code := G_ASSIGN_PACK_SS_ORG;
2602 END IF;
2603 --dbms_output.put_line(' Defaulting option ' || ego_common_pvt.get_option_value(l_default_option_code) );
2604 FOR l_pack_rec IN l_pack_item_csr(p_batch_id)
2605 LOOP
2606 bom_exploder_pub.exploder_userexit(
2607 org_id => l_pack_rec.organization_id,
2608 grp_id => l_explode_grp_id,
2609 levels_to_explode => 60,
2610 bom_or_eng => 2,
2611 impl_flag => 1,
2612 explode_option => 2,
2613 rev_date => G_SYSDATE,
2614 alt_desg => G_PACK_STR_NAME,
2615 pk_value1 => l_pack_rec.inventory_item_id,
2616 pk_value2 => l_pack_rec.organization_id,
2617 err_msg => l_err_message,
2618 error_code => l_err_code,
2619 unit_number => NULL
2620 );
2621 FOR l_item_rec IN l_expl_csr
2622 LOOP
2623 FOR I IN G_DATA_LEVEL_NAMES.FIRST..G_DATA_LEVEL_NAMES.LAST
2624 LOOP
2625 IF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_LEVEL_NAME ) THEN
2626 l_data_level_id := G_ITEM_SUPPLIER_LEVEL;
2627 l_default_option_code := G_ASSIGN_PACK_SUPPLIER;
2628 ELSIF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_SITE_LEVEL_NAME ) THEN
2629 l_data_level_id := G_ITEM_SUPPLIERSITE_LEVEL;
2630 l_default_option_code := G_ASSIGN_PACK_SUP_SITE;
2631 ELSIF ( G_DATA_LEVEL_NAMES(I) = G_ITEM_SUP_SITE_ORG_LEVEL_NAME ) THEN
2632 l_data_level_id := G_ITEM_SUPPLIERSITE_ORG_LEVEL;
2633 l_default_option_code := G_ASSIGN_PACK_SS_ORG;
2634 END IF;
2635 IF ego_common_pvt.get_option_value(l_default_option_code) = 'Y' THEN
2636 INSERT INTO ego_item_associations_intf
2637 (
2638 BATCH_ID
2639 ,SOURCE_SYSTEM_REFERENCE
2640 ,ITEM_NUMBER
2641 ,INVENTORY_ITEM_ID
2642 ,ORGANIZATION_ID
2643 ,ORGANIZATION_CODE
2644 ,PK1_VALUE
2645 ,SUPPLIER_NAME
2646 ,SUPPLIER_NUMBER
2647 ,PK2_VALUE
2648 ,SUPPLIER_SITE_NAME
2649 ,DATA_LEVEL_ID
2650 ,DATA_LEVEL_NAME
2651 ,PRIMARY_FLAG
2652 ,STATUS_CODE
2653 ,TRANSACTION_TYPE
2654 ,PROCESS_FLAG
2655 ,TRANSACTION_ID
2656 ,REQUEST_ID
2657 ,CREATED_BY -- Bug 6459846
2658 )
2659 SELECT p_batch_id
2660 ,NULL
2661 ,msibk.concatenated_segments
2662 ,msibk.inventory_item_id
2663 ,eiai.organization_id
2664 ,eiai.organization_code
2665 ,eiai.pk1_value
2666 ,eiai.supplier_name
2667 ,eiai.supplier_number
2668 ,eiai.pk2_value
2669 ,eiai.supplier_site_name
2670 ,eiai.data_level_id
2671 ,eiai.data_level_name
2672 ,eiai.primary_flag
2673 ,eiai.status_code
2674 ,G_CREATE
2675 ,G_REC_TO_BE_PROCESSED
2676 ,mtl_system_items_interface_s.NEXTVAL
2677 ,G_REQUEST_ID
2678 ,G_SKIP_SECURIY_CHECK -- Bug 6459846
2679 FROM ego_item_associations_intf eiai, mtl_system_items_b_kfv msibk,
2680 mtl_parameters mp
2681 WHERE eiai.inventory_item_id = l_pack_rec.inventory_item_id
2682 AND eiai.organization_id = mp.organization_id
2683 --AND mp.master_organization_id = mp.organization_id
2684 AND eiai.batch_id = p_batch_id
2685 AND eiai.process_flag = G_REC_TO_BE_PROCESSED
2686 AND mp.organization_id = l_pack_rec.organization_id
2687 AND eiai.data_level_id = l_data_level_id
2688 AND msibk.inventory_item_id = l_item_rec.component_item_id
2689 AND msibk.organization_id = mp.organization_id
2690 AND NOT EXISTS
2691 (
2692 SELECT 1
2693 FROM ego_item_associations_intf eiai1
2694 WHERE eiai1.inventory_item_id = msibk.inventory_item_id
2695 AND eiai1.organization_id = msibk.organization_id
2696 AND eiai1.data_level_id = l_data_level_id
2697 AND eiai1.batch_id = p_batch_id
2698 AND eiai1.process_flag = G_REC_TO_BE_PROCESSED
2699 AND eiai1.pk1_value = eiai.pk1_value
2700 AND NVL(eiai1.pk2_value,-1) = NVL(eiai.pk2_value,-1)
2701 UNION ALL
2702 SELECT 1
2703 FROM ego_item_associations eia2
2704 WHERE eia2.inventory_item_id = msibk.inventory_item_id
2705 AND eia2.organization_id = msibk.organization_id
2706 AND eia2.data_level_id = l_data_level_id
2707 AND eia2.pk1_value = eiai.pk1_value
2708 AND NVL(eia2.pk2_value,-1) = NVL(eiai.pk2_value,-1)
2709 );
2710 END IF;
2711 END LOOP;
2712 END LOOP;
2713 END LOOP;
2714 END LOOP;
2715 FND_MSG_PUB.Count_And_Get
2716 ( p_count => x_msg_count,
2717 p_data => x_msg_data
2718 );
2719 write_log_message(' ego_item_associations_pub.copy_to_packs Msg Count ' || x_msg_count);
2720 write_log_message(' ego_item_associations_pub.copy_to_packs Msg Data ' || x_msg_data);
2721 write_log_message(' ego_item_associations_pub.copy_to_packs Return Status ' || x_return_status);
2722 EXCEPTION
2723 WHEN fnd_api.G_EXC_ERROR THEN
2724 ROLLBACK TO copy_to_packs_pub;
2725 x_return_status := fnd_api.G_RET_STS_ERROR ;
2726 FND_MSG_PUB.Count_And_Get
2727 ( p_count => x_msg_count ,
2728 p_data => x_msg_data
2729 );
2730 write_log_message(' ego_item_associations_pub.copy_to_packs Error Msg Count ' || x_msg_count);
2731 write_log_message(' ego_item_associations_pub.copy_to_packs Error Msg Data ' || x_msg_data);
2732 write_log_message(' ego_item_associations_pub.copy_to_packs Error Return Status ' || x_return_status);
2733 WHEN fnd_api.G_EXC_UNEXPECTED_ERROR THEN
2734 --dbms_output.put_line(' Error Msg ' || SQLERRM);
2735 ROLLBACK TO copy_to_packs_pub;
2736 UPDATE ego_item_associations_intf
2737 SET process_flag = G_REC_UNEXPECTED_ERROR
2738 WHERE batch_id = p_batch_id
2739 AND process_flag = G_REC_TO_BE_PROCESSED;
2740 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2741 FND_MSG_PUB.Count_And_Get
2742 ( p_count => x_msg_count ,
2743 p_data => x_msg_data
2744 );
2745 write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Msg Count ' || x_msg_count);
2746 write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Msg Data ' || x_msg_data);
2747 write_log_message(' ego_item_associations_pub.copy_to_packs Unexpected Error Return Status ' || x_return_status);
2748 WHEN OTHERS THEN
2749 --dbms_output.put_line(' Error Msg ' || SQLERRM);
2750 ROLLBACK TO copy_to_packs_pub;
2751 UPDATE ego_item_associations_intf
2752 SET process_flag = G_REC_UNEXPECTED_ERROR
2753 WHERE batch_id = p_batch_id
2754 AND process_flag = G_REC_TO_BE_PROCESSED;
2755 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR ;
2756 IF FND_MSG_PUB.Check_Msg_Level
2757 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2758 THEN
2759 FND_MSG_PUB.Add_Exc_Msg
2760 ( G_PKG_NAME,
2761 l_api_name
2762 );
2763 END IF;
2764 FND_MSG_PUB.Count_And_Get
2765 ( p_count => x_msg_count,
2766 p_data => x_msg_data
2767 );
2768 write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Count ' || x_msg_count);
2769 write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Msg Data ' || x_msg_data);
2770 write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS Return Status ' || x_return_status);
2771 write_log_message(' ego_item_associations_pub.copy_to_packs WHEN OTHERS SQLERRM ' || SQLERRM);
2772 END copy_to_packs;
2773
2774 END ego_item_associations_pub;