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