1 PACKAGE BODY EGO_TA_BULKLOAD_PVT AS
2 /* $Header: EGOVTABB.pls 120.6 2011/04/19 20:08:57 sreharih noship $ */
3
4 -----=================Import_TA_Intf===============------
5
6 PROCEDURE Import_TA_Intf(
7 p_api_version IN NUMBER,
8 p_set_process_id IN NUMBER,
9 p_item_catalog_group_id IN NUMBER,
10 p_icc_version_number_intf IN NUMBER,
11 p_icc_version_number_act IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_return_msg OUT NOCOPY VARCHAR2)
14 IS
15
16 l_return_status VARCHAR2(1);
17 l_return_msg VARCHAR2(2000);
18 l_ta_intf_tbl TA_Intf_Tbl;
19 l_err_rec NUMBER :=0;
20 l_rec_exists NUMBER :=0;
21 l_proc_name VARCHAR2(200) := 'Import_TA_Intf';
22 BEGIN
23
24 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Import_TA_Intf');
25 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Setting G_FLOW_TYPE=G_EGO_MD_INTF');
26 -- Initializing message --
27 FND_MSG_PUB.INITIALIZE;
28 -- setting flow type as interface
29 G_FLOW_TYPE:=G_EGO_MD_INTF;
30 x_return_status:=G_RET_STS_SUCCESS;
31
32 /* has to be called through ICC versioning bulk validations.
33 -- Initializing transaction_id, Upper(Transaction_type and
34 -- setting G_APPLICATION_ID
35 Initialize(p_set_process_id,l_return_status);
36 --Bulk Validation on Interface table
37 Bulk_Validate_Trans_Attrs(p_set_process_id);
38 --*/
39 BEGIN
40 -- if any of the TA errored out during bulk,throw error
41 SELECT 1 INTO l_err_rec
42 FROM EGO_TRANS_ATTRS_VERS_INTF
43 WHERE item_catalog_group_id= p_item_catalog_group_id
44 AND icc_version_number = p_icc_version_number_intf
45 AND process_status=G_ERROR_RECORD --3
46 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
47 AND ROWNUM=1;
48 EXCEPTION
49 WHEN No_Data_Found THEN
50 l_err_rec:=0;
51 END;
52
53 IF l_err_rec=1 THEN
54 x_return_status:=G_RET_STS_ERROR;
55 RETURN ;
56 ELSE
57 -- checking if records are there to process or not
58 SELECT Count(1) INTO l_rec_exists
59 FROM EGO_TRANS_ATTRS_VERS_INTF
60 WHERE item_catalog_group_id= p_item_catalog_group_id
61 AND icc_version_number= p_icc_version_number_intf
62 AND process_status=G_PROCESS_RECORD
63 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
64 AND ROWNUM=1;
65
66 IF l_rec_exists=0 THEN -- if no record exists for TA
67 x_return_status:=G_RET_STS_SUCCESS;
68 RETURN;
69 ELSE
70 -- Loading records in pl-sql
71 Load_Trans_Attrs_recs(p_set_process_id => p_set_process_id,
72 p_item_catalog_group_id => p_item_catalog_group_id,
73 p_icc_version_number_intf => p_icc_version_number_intf,
74 p_icc_version_number_act => p_icc_version_number_act,
75 x_ta_intf_tbl => l_ta_intf_tbl,
76 x_return_status => l_return_status ,
77 x_return_msg => l_return_msg);
78
79 -- Process_ta-- Main API which takes record table and do final transaction.
80 Process_Trans_Attrs(p_api_version => p_api_version,
81 p_ta_intf_tbl => l_ta_intf_tbl,
82 p_item_catalog_group_id => p_item_catalog_group_id,
83 p_icc_version_number_intf => p_icc_version_number_intf,
84 p_icc_version_number_act => p_icc_version_number_act,
85 x_return_status => l_return_status ,
86 x_return_msg => l_return_msg);
87
88 x_return_status:=l_return_status;
89 x_return_msg:= l_return_msg;
90 -- RETURN; -- no need to return from here.
91 END IF ; --l_rec_exists=0
92 END IF;--l_err_rec=1
93 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Import_TA_Intf');
94 EXCEPTION
95 WHEN OTHERS THEN
96 x_return_status:=G_RET_STS_UNEXP_ERROR;
97 x_return_msg:= G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
98 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Import_TA_Intf');
99 END Import_TA_Intf ;
100
101 ----=================Initialize =========-------
102
103 PROCEDURE Initialize(
104 p_set_process_id IN NUMBER,
105 x_return_status OUT NOCOPY VARCHAR2)
106 IS
107 l_proc_name VARCHAR2(200) := 'Initialize';
108 BEGIN
109 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Initialize');
110
111 /*Sets the EGO application ID*/
112 SELECT application_id
113 INTO G_APPLICATION_ID
114 FROM fnd_application
115 WHERE application_short_name = G_APP_NAME;
116
117
118 /*Sets the Transaction_id and upper case the transaction_type*/
119 UPDATE EGO_TRANS_ATTRS_VERS_INTF
120 SET transaction_id = mtl_system_items_interface_s.nextval,
121 transaction_type = Upper(transaction_type),
122 created_by = Nvl(created_by,g_user_id),
123 creation_date = Nvl(creation_date,SYSDATE),
124 last_updated_by = G_USER_ID,
125 last_update_date = SYSDATE,
126 last_update_login = G_LOGIN_ID,
127 /* bug 9752139*/
128 request_id = G_REQUEST_ID,
129 program_application_id = G_PROG_APPL_ID,
130 program_id = G_PROGRAM_ID ,
131 program_update_date = SYSDATE
132 WHERE transaction_id IS NULL
133 AND process_status = G_PROCESS_RECORD
134 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
135
136 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||SQL%ROWCOUNT||' Rows Initialize');
137 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Initialize');
138 EXCEPTION
139 WHEN OTHERS THEN
140 x_return_status:=G_RET_STS_ERROR;
141 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'When Others Exception while Initialize');
142 END Initialize;
143
144 --=================Bulk_Validate_Trans_Attrs========
145 PROCEDURE Bulk_Validate_Trans_Attrs (p_set_process_id IN NUMBER)
146 IS
147 l_proc_name VARCHAR2(200) := 'Bulk_Validate_Trans_Attrs';
148 BEGIN
149 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entered Bulk_Validate_Trans_Attrs');
150 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking valid transaction types');
151
152 /* Invalid Transaction type */
153 G_MESSAGE_NAME := 'EGO_TRANS_TYPE_INVALID';
154 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
155 G_MESSAGE_TEXT := fnd_message.get;
156
157 INSERT INTO mtl_interface_errors(
158 transaction_id,
159 unique_id,
160 organization_id,
161 column_name,
162 table_name,
163 message_name,
164 error_message,
165 BO_IDENTIFIER,
166 ENTITY_IDENTIFIER,
167 last_update_date,
168 last_updated_by,
169 creation_date,
170 created_by,
171 last_update_login,
172 request_id,
173 program_application_id,
174 program_id,
175 program_update_date)
176 SELECT transaction_id,
177 mtl_system_items_interface_s.nextval,
178 NULL,
179 NULL,
180 'EGO_TRANS_ATTRS_VERS_INTF',
181 G_MESSAGE_NAME,
182 G_MESSAGE_TEXT,
183 G_BO_IDENTIFIER,
184 G_ENTITY_IDENTIFIER,
185 NVL(last_update_date,SYSDATE),
186 NVL(last_updated_by,G_USER_ID),
187 NVL(creation_date,SYSDATE),
188 NVL(created_by,G_USER_ID),
189 NVL(last_update_login,G_LOGIN_ID),
190 G_REQUEST_ID,
191 NVL(program_application_id,G_PROG_APPL_ID),
192 NVL(program_id,G_PROGRAM_ID),
193 NVL(program_update_date,SYSDATE)
194 FROM EGO_TRANS_ATTRS_VERS_INTF
195 WHERE ((transaction_type is NULL) or (transaction_type NOT IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)))
196 AND transaction_id IS NOT NULL
197 AND process_status = G_PROCESS_RECORD
198 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
199
200 /* Invalid Transaction type */
201 UPDATE EGO_TRANS_ATTRS_VERS_INTF
202 SET process_status =G_ERROR_RECORD,
203 last_updated_by = G_USER_ID,
204 last_update_date = SYSDATE,
205 last_update_login = G_LOGIN_ID
206 WHERE ((transaction_type is NULL) or (transaction_type NOT IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC))) /* bug 9752139 */
207 AND transaction_id IS NOT NULL
208 AND process_status=G_PROCESS_RECORD
209 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
210 -------------------------------------------
211 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Calling Value_to_Id for ICC,Value Set and Attr name');
212 /* calling value to id for icc,valueset and attr name to id conversion*/
213
214 Value_to_Id(p_set_process_id);
215
216 -------------------------------------------
217 G_MESSAGE_NAME := 'EGO_NOT_SUPP_TRANS';
218 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
219 G_MESSAGE_TEXT := fnd_message.get;
220 -- restricting updation and deletion right now as we are not supporting them for now
221 -- commenting this will allow update and delete valid cases work properly
222 INSERT INTO mtl_interface_errors(
223 transaction_id,
224 unique_id,
225 organization_id,
226 column_name,
227 table_name,
228 message_name,
229 error_message,
230 BO_IDENTIFIER,
231 ENTITY_IDENTIFIER,
232 last_update_date,
233 last_updated_by,
234 creation_date,
235 created_by,
236 last_update_login,
237 request_id,
238 program_application_id,
239 program_id,
240 program_update_date)
241 SELECT transaction_id,
242 mtl_system_items_interface_s.nextval,
243 NULL,
244 NULL,
245 'EGO_TRANS_ATTRS_VERS_INTF',
246 G_MESSAGE_NAME,
247 G_MESSAGE_TEXT,
248 G_BO_IDENTIFIER,
249 G_ENTITY_IDENTIFIER,
250 NVL(last_update_date,SYSDATE),
251 NVL(last_updated_by,G_USER_ID),
252 NVL(creation_date,SYSDATE),
253 NVL(created_by,G_USER_ID),
254 NVL(last_update_login,G_LOGIN_ID),
255 G_REQUEST_ID,
256 NVL(program_application_id,G_PROG_APPL_ID),
257 NVL(program_id,G_PROGRAM_ID),
258 NVL(program_update_date,SYSDATE)
259 FROM EGO_TRANS_ATTRS_VERS_INTF
260 WHERE transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
261 AND transaction_id IS NOT NULL
262 AND process_status = G_PROCESS_RECORD
263 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
264
265 /* not supporting now */
266 UPDATE EGO_TRANS_ATTRS_VERS_INTF
267 SET process_status =G_ERROR_RECORD,
268 last_updated_by = G_USER_ID,
269 last_update_date = SYSDATE,
270 last_update_login = G_LOGIN_ID
271 WHERE transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
272 AND transaction_id IS NOT NULL
273 AND process_status=G_PROCESS_RECORD
274 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
275 ---------------------------------------------------------
276 G_MESSAGE_NAME := 'EGO_NOT_SUPP_DFT_CREATE';
277 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
278 G_MESSAGE_TEXT := fnd_message.get;
279 -- for now not supporting creation of TA for draft.
280 -- commenting this will allow create TA on draft version
281 INSERT INTO mtl_interface_errors(
282 transaction_id,
283 unique_id,
284 organization_id,
285 column_name,
286 table_name,
287 message_name,
288 error_message,
289 BO_IDENTIFIER,
290 ENTITY_IDENTIFIER,
291 last_update_date,
292 last_updated_by,
293 creation_date,
294 created_by,
295 last_update_login,
296 request_id,
297 program_application_id,
298 program_id,
299 program_update_date)
300 SELECT transaction_id,
301 mtl_system_items_interface_s.nextval,
302 NULL,
303 NULL,
304 'EGO_TRANS_ATTRS_VERS_INTF',
305 G_MESSAGE_NAME,
306 G_MESSAGE_TEXT,
307 G_BO_IDENTIFIER,
308 G_ENTITY_IDENTIFIER,
309 NVL(last_update_date,SYSDATE),
310 NVL(last_updated_by,G_USER_ID),
311 NVL(creation_date,SYSDATE),
312 NVL(created_by,G_USER_ID),
313 NVL(last_update_login,G_LOGIN_ID),
314 G_REQUEST_ID,
315 NVL(program_application_id,G_PROG_APPL_ID),
316 NVL(program_id,G_PROGRAM_ID),
317 NVL(program_update_date,SYSDATE)
318 FROM EGO_TRANS_ATTRS_VERS_INTF
319 WHERE transaction_type IN (G_CREATE)
320 AND transaction_id IS NOT NULL
321 AND icc_version_number=0
322 AND process_status = G_PROCESS_RECORD
323 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
324
325 /* not supporting now */
326 UPDATE EGO_TRANS_ATTRS_VERS_INTF
327 SET process_status =G_ERROR_RECORD,
328 last_updated_by = G_USER_ID,
329 last_update_date = SYSDATE,
330 last_update_login = G_LOGIN_ID
331 WHERE transaction_type IN (G_CREATE)
332 AND transaction_id IS NOT NULL
333 AND icc_version_number=0
334 AND process_status=G_PROCESS_RECORD
335 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
336
337 --------------------------------------------------
338 --ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Checking ICC name provided doesnt exists in table');
339
340 /* commented as now decided to process ICC one by one
341 so not validated ICC at table level as that not be there and set to 3*/
342 /*G_MESSAGE_NAME := 'EGO_ITEMCATALOG_INVALID';
343 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
344 G_MESSAGE_TEXT := fnd_message.get;
345 INSERT INTO mtl_interface_errors(
346 transaction_id,
347 unique_id,
348 organization_id,
349 column_name,
350 table_name,
351 message_name,
352 error_message,
353 BO_IDENTIFIER,
354 ENTITY_IDENTIFIER,
355 last_update_date,
356 last_updated_by,
357 creation_date,
358 created_by,
359 last_update_login,
360 request_id,
361 program_application_id,
362 program_id,
363 program_update_date)
364 SELECT transaction_id,
365 mtl_system_items_interface_s.nextval,
366 NULL,
367 NULL,
368 'EGO_TRANS_ATTRS_VERS_INTF',
369 G_MESSAGE_NAME,
370 G_MESSAGE_TEXT,
371 G_BO_IDENTIFIER,
372 G_ENTITY_IDENTIFIER,
373 NVL(last_update_date,SYSDATE),
374 NVL(last_updated_by,G_USER_ID),
375 NVL(creation_date,SYSDATE),
376 NVL(created_by,G_USER_ID),
377 NVL(last_update_login,G_LOGIN_ID),
378 G_REQUEST_ID,
379 NVL(program_application_id,G_PROG_APPL_ID),
380 NVL(program_id,G_PROGRAM_ID),
381 NVL(program_update_date,SYSDATE)
382 FROM EGO_TRANS_ATTRS_VERS_INTF
383 WHERE item_catalog_group_id IS NULL
384 AND item_catalog_group_name IS NOT NULL
385 AND transaction_id IS NOT NULL
386 AND process_status = G_PROCESS_RECORD
387 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
388
389 UPDATE EGO_TRANS_ATTRS_VERS_INTF
390 SET process_status = G_ERROR_RECORD,
391 last_updated_by = G_USER_ID,
392 last_update_date = SYSDATE,
393 last_update_login = G_LOGIN_ID
394 WHERE item_catalog_group_id IS NULL
395 AND item_catalog_group_name IS NOT NULL
396 AND transaction_id IS NOT NULL
397 AND process_status=G_PROCESS_RECORD
398 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));*/
399
400 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if both icc_id and icc_name are null');
401
402 G_MESSAGE_NAME := 'EGO_ICC_REQUIRED_FIELD';
403 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
404 G_MESSAGE_TEXT := fnd_message.get;
405 /* if both icc_id and icc_name are null*/
406 INSERT INTO mtl_interface_errors(
407 transaction_id,
408 unique_id,
409 organization_id,
410 column_name,
411 table_name,
412 message_name,
413 error_message,
414 BO_IDENTIFIER,
415 ENTITY_IDENTIFIER,
416 last_update_date,
417 last_updated_by,
418 creation_date,
419 created_by,
420 last_update_login,
421 request_id,
422 program_application_id,
423 program_id,
424 program_update_date)
425 SELECT transaction_id,
426 mtl_system_items_interface_s.nextval,
427 NULL,
428 NULL,
429 'EGO_TRANS_ATTRS_VERS_INTF',
430 G_MESSAGE_NAME,
431 G_MESSAGE_TEXT,
432 G_BO_IDENTIFIER,
433 G_ENTITY_IDENTIFIER,
434 NVL(last_update_date,SYSDATE),
435 NVL(last_updated_by,G_USER_ID),
436 NVL(creation_date,SYSDATE),
437 NVL(created_by,G_USER_ID),
438 NVL(last_update_login,G_LOGIN_ID),
439 G_REQUEST_ID,
440 NVL(program_application_id,G_PROG_APPL_ID),
441 NVL(program_id,G_PROGRAM_ID),
442 NVL(program_update_date,SYSDATE)
443 FROM EGO_TRANS_ATTRS_VERS_INTF
444 WHERE item_catalog_group_id IS NULL
445 AND item_catalog_group_name IS NULL
446 AND transaction_id IS NOT NULL
447 AND process_status = G_PROCESS_RECORD
448 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
449
450 /* if both icc_id and icc_name are null*/
451 UPDATE EGO_TRANS_ATTRS_VERS_INTF
452 SET process_status = G_ERROR_RECORD,
453 last_updated_by = G_USER_ID,
454 last_update_date = SYSDATE,
455 last_update_login = G_LOGIN_ID
456 WHERE item_catalog_group_id IS NULL
457 AND item_catalog_group_name IS NULL
458 AND transaction_id IS NOT NULL
459 AND process_status=G_PROCESS_RECORD
460 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
461
462
463 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if passed icc_id dosent exists');
464
465 G_MESSAGE_NAME := 'EGO_ITEMCATALOG_INVALID';
466 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
467 G_MESSAGE_TEXT := fnd_message.get;
468 /* If passed icc_id dosent exists*/
469 INSERT INTO mtl_interface_errors(
470 transaction_id,
471 unique_id,
472 organization_id,
473 column_name,
474 table_name,
475 message_name,
476 error_message,
477 BO_IDENTIFIER,
478 ENTITY_IDENTIFIER,
479 last_update_date,
480 last_updated_by,
481 creation_date,
482 created_by,
483 last_update_login,
484 request_id,
485 program_application_id,
486 program_id,
487 program_update_date)
488 SELECT transaction_id,
489 mtl_system_items_interface_s.nextval,
490 NULL,
491 NULL,
492 'EGO_TRANS_ATTRS_VERS_INTF',
493 G_MESSAGE_NAME,
494 G_MESSAGE_TEXT,
495 G_BO_IDENTIFIER,
496 G_ENTITY_IDENTIFIER,
497 NVL(last_update_date,SYSDATE),
498 NVL(last_updated_by,G_USER_ID),
499 NVL(creation_date,SYSDATE),
500 NVL(created_by,G_USER_ID),
501 NVL(last_update_login,G_LOGIN_ID),
502 G_REQUEST_ID,
503 NVL(program_application_id,G_PROG_APPL_ID),
504 NVL(program_id,G_PROGRAM_ID),
505 NVL(program_update_date,SYSDATE)
506 FROM EGO_TRANS_ATTRS_VERS_INTF ETAVI
507 WHERE ETAVI.item_catalog_group_id IS NOT NULL
508 AND NOT EXISTS (
509 SELECT 1
510 FROM mtl_item_catalog_groups micg
511 WHERE micg.ITEM_CATALOG_GROUP_ID=ETAVI.ITEM_CATALOG_GROUP_ID)
512 AND ETAVI.transaction_id IS NOT NULL
513 AND ETAVI.process_status = G_PROCESS_RECORD
514 AND ((p_set_process_id IS NULL) OR (ETAVI.set_process_id=p_set_process_id));
515
516 /* If passed icc_id dosent exists*/
517 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVI
518 SET ETAVI.process_status = G_ERROR_RECORD,
519 ETAVI.last_updated_by = G_USER_ID,
520 ETAVI.last_update_date = SYSDATE,
521 ETAVI.last_update_login = G_LOGIN_ID
522 WHERE ETAVI.item_catalog_group_id IS NOT NULL
523 AND NOT EXISTS (
524 SELECT 1
525 FROM mtl_item_catalog_groups micg
526 WHERE micg.ITEM_CATALOG_GROUP_ID=ETAVI.ITEM_CATALOG_GROUP_ID)
527 AND ETAVI.transaction_id IS NOT NULL
528 AND ETAVI.process_status = G_PROCESS_RECORD
529 AND ((p_set_process_id IS NULL) OR (ETAVI.set_process_id=p_set_process_id));
530 -------------------------------------------
531 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if VS name provided doesnt exists');
532
533 G_MESSAGE_NAME := 'EGO_EF_NO_VALUE_SETS_FOUND';
534 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
535 G_MESSAGE_TEXT := fnd_message.get;
536 /*if VS name provided doesnt exists in table */
537 INSERT INTO mtl_interface_errors(
538 transaction_id,
539 unique_id,
540 organization_id,
541 column_name,
542 table_name,
543 message_name,
544 error_message,
545 BO_IDENTIFIER,
546 ENTITY_IDENTIFIER,
547 last_update_date,
548 last_updated_by,
549 creation_date,
550 created_by,
551 last_update_login,
552 request_id,
553 program_application_id,
554 program_id,
555 program_update_date)
556 SELECT transaction_id,
557 mtl_system_items_interface_s.nextval,
558 NULL,
559 NULL,
560 'EGO_TRANS_ATTRS_VERS_INTF',
561 G_MESSAGE_NAME,
562 G_MESSAGE_TEXT,
563 G_BO_IDENTIFIER,
564 G_ENTITY_IDENTIFIER,
565 NVL(last_update_date,SYSDATE),
566 NVL(last_updated_by,G_USER_ID),
567 NVL(creation_date,SYSDATE),
568 NVL(created_by,G_USER_ID),
569 NVL(last_update_login,G_LOGIN_ID),
570 G_REQUEST_ID,
571 NVL(program_application_id,G_PROG_APPL_ID),
572 NVL(program_id,G_PROGRAM_ID),
573 NVL(program_update_date,SYSDATE)
574 FROM EGO_TRANS_ATTRS_VERS_INTF
575 WHERE value_set_id IS NULL
576 AND Value_set_name IS NOT NULL
577 AND transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
578 AND transaction_id IS NOT NULL
579 AND process_status = G_PROCESS_RECORD
580 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
581
582
583 /*if value set name provided doesnt exists in table */
584 UPDATE EGO_TRANS_ATTRS_VERS_INTF
585 SET process_status = G_ERROR_RECORD,
586 last_updated_by = G_USER_ID,
587 last_update_date = SYSDATE,
588 last_update_login = G_LOGIN_ID
589 WHERE value_set_id IS NULL
590 AND Value_set_name IS NOT NULL
591 AND transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
592 AND transaction_id IS NOT NULL
593 AND process_status = G_PROCESS_RECORD
594 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
595
596
597 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if given value set id is valid or not');
598
599 G_MESSAGE_NAME := 'EGO_EF_NO_VALUE_SETS_FOUND';
600 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
601 G_MESSAGE_TEXT := fnd_message.get;
602 INSERT INTO mtl_interface_errors(
603 transaction_id,
604 unique_id,
605 organization_id,
606 column_name,
607 table_name,
608 message_name,
609 error_message,
610 BO_IDENTIFIER,
611 ENTITY_IDENTIFIER,
612 last_update_date,
613 last_updated_by,
614 creation_date,
615 created_by,
616 last_update_login,
617 request_id,
618 program_application_id,
619 program_id,
620 program_update_date)
621 SELECT transaction_id,
622 mtl_system_items_interface_s.nextval,
623 NULL,
624 NULL,
625 'EGO_TRANS_ATTRS_VERS_INTF',
626 G_MESSAGE_NAME,
627 G_MESSAGE_TEXT,
628 G_BO_IDENTIFIER,
629 G_ENTITY_IDENTIFIER,
630 NVL(last_update_date,SYSDATE),
631 NVL(last_updated_by,G_USER_ID),
632 NVL(creation_date,SYSDATE),
633 NVL(created_by,G_USER_ID),
634 NVL(last_update_login,G_LOGIN_ID),
635 G_REQUEST_ID,
636 NVL(program_application_id,G_PROG_APPL_ID),
637 NVL(program_id,G_PROGRAM_ID),
638 NVL(program_update_date,SYSDATE)
639 FROM EGO_TRANS_ATTRS_VERS_INTF ETAVT
640 WHERE value_set_id IS NOT NULL
641 AND NOT EXISTS (
642 SELECT 1
643 FROM fnd_flex_value_sets ffvs
644 WHERE ffvs.flex_value_set_id = ETAVT.value_set_id)
645 AND transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
646 AND ETAVT.transaction_id IS NOT NULL
647 AND ETAVT.process_status = G_PROCESS_RECORD
648 AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
649
650 /*given value set id is valid or not*/
651 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
652 SET ETAVT.process_status =G_ERROR_RECORD,
653 ETAVT.last_updated_by = G_USER_ID,
654 ETAVT.last_update_date = SYSDATE,
655 ETAVT.last_update_login = G_LOGIN_ID
656 WHERE ETAVT.value_set_id IS NOT NULL
657 AND NOT EXISTS (
658 SELECT 1
659 FROM fnd_flex_value_sets
660 WHERE flex_value_set_id = ETAVT.value_set_id)
661 AND transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
662 AND ETAVT.transaction_id IS NOT NULL
663 AND ETAVT.process_status = G_PROCESS_RECORD
664 AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
665
666 -------------------------------------------
667 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if given attr_name is valid');
668
669 G_MESSAGE_NAME := 'EGO_ATTR_NOT_EXISTS';
670 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
671 G_MESSAGE_TEXT := fnd_message.get;
672 /* Attrname provided is valid or not*/
673 INSERT INTO mtl_interface_errors(
674 transaction_id,
675 unique_id,
676 organization_id,
677 column_name,
678 table_name,
679 message_name,
680 error_message,
681 BO_IDENTIFIER,
682 ENTITY_IDENTIFIER,
683 last_update_date,
684 last_updated_by,
685 creation_date,
686 created_by,
687 last_update_login,
688 request_id,
689 program_application_id,
690 program_id,
691 program_update_date)
692 SELECT transaction_id,
693 mtl_system_items_interface_s.nextval,
694 NULL,
695 NULL,
696 'EGO_TRANS_ATTRS_VERS_INTF',
697 G_MESSAGE_NAME,
698 G_MESSAGE_TEXT,
699 G_BO_IDENTIFIER,
700 G_ENTITY_IDENTIFIER,
701 NVL(last_update_date,SYSDATE),
702 NVL(last_updated_by,G_USER_ID),
703 NVL(creation_date,SYSDATE),
704 NVL(created_by,G_USER_ID),
705 NVL(last_update_login,G_LOGIN_ID),
706 G_REQUEST_ID,
707 NVL(program_application_id,G_PROG_APPL_ID),
708 NVL(program_id,G_PROGRAM_ID),
709 NVL(program_update_date,SYSDATE)
710 FROM EGO_TRANS_ATTRS_VERS_INTF
711 WHERE attr_id IS NULL
712 AND attr_name IS NOT NULL
713 AND transaction_type IN (G_UPDATE,G_DELETE)
714 AND transaction_id IS NOT NULL
715 AND process_status = G_PROCESS_RECORD
716 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
717
718 /*if attr name provided doesnt exists in table */
719 UPDATE EGO_TRANS_ATTRS_VERS_INTF
720 SET process_status = G_ERROR_RECORD,
721 last_updated_by = G_USER_ID,
722 last_update_date = SYSDATE,
723 last_update_login = G_LOGIN_ID
724 WHERE attr_id IS NULL
725 AND attr_name IS NOT NULL
726 AND transaction_type IN (G_UPDATE,G_DELETE)
727 AND transaction_id IS NOT NULL
728 AND process_status = G_PROCESS_RECORD
729 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
730
731 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'if attr_name is given with sync and not exists- convert to Create');
732 /* converting sync to create or update */
733 /* if attr_name is given with sync and not exists */
734 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
735 SET transaction_type = G_CREATE,
736 last_updated_by = G_USER_ID,
737 last_update_date = SYSDATE,
738 last_update_login = G_LOGIN_ID
739 WHERE attr_id IS NULL
740 AND attr_name IS NOT NULL
741 AND transaction_type =G_SYNC
742 AND transaction_id IS NOT NULL
743 AND process_status = G_PROCESS_RECORD
744 AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
745
746 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if given attr_id is valid or not');
747
748 G_MESSAGE_NAME := 'EGO_INVALID_TA';
749 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
750 G_MESSAGE_TEXT := fnd_message.get;
751 /*given attr_id is invalid or not*/
752 INSERT INTO mtl_interface_errors(
753 transaction_id,
754 unique_id,
755 organization_id,
756 column_name,
757 table_name,
758 message_name,
759 error_message,
760 BO_IDENTIFIER,
761 ENTITY_IDENTIFIER,
762 last_update_date,
763 last_updated_by,
764 creation_date,
765 created_by,
766 last_update_login,
767 request_id,
768 program_application_id,
769 program_id,
770 program_update_date)
771 SELECT transaction_id,
772 mtl_system_items_interface_s.nextval,
773 NULL,
774 NULL,
775 'EGO_TRANS_ATTRS_VERS_INTF',
776 G_MESSAGE_NAME,
777 G_MESSAGE_TEXT,
778 G_BO_IDENTIFIER,
779 G_ENTITY_IDENTIFIER,
780 NVL(last_update_date,SYSDATE),
781 NVL(last_updated_by,G_USER_ID),
782 NVL(creation_date,SYSDATE),
783 NVL(created_by,G_USER_ID),
784 NVL(last_update_login,G_LOGIN_ID),
785 G_REQUEST_ID,
786 NVL(program_application_id,G_PROG_APPL_ID),
787 NVL(program_id,G_PROGRAM_ID),
788 NVL(program_update_date,SYSDATE)
789 FROM EGO_TRANS_ATTRS_VERS_INTF ETAVT
790 WHERE attr_id IS NOT NULL
791 AND NOT EXISTS (
792 SELECT 1
793 FROM EGO_TRANS_ATTR_VERS_B ETAVB
794 WHERE ETAVB.attr_id = ETAVT.attr_id
795 AND ETAVB.item_catalog_group_id=ETAVT.item_catalog_group_id
796 AND ETAVB.icc_version_number=0)
797 AND ETAVT.transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
798 AND ETAVT.transaction_id IS NOT NULL
799 AND ETAVT.process_status = G_PROCESS_RECORD
800 AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
801
802 /*given attr id is valid or not*/
803 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
804 SET ETAVT.process_status = G_ERROR_RECORD,
805 ETAVT.last_updated_by = G_USER_ID,
806 ETAVT.last_update_date = SYSDATE,
807 ETAVT.last_update_login = G_LOGIN_ID
808 WHERE attr_id IS NOT NULL
809 AND NOT EXISTS (
810 SELECT 1
811 FROM EGO_TRANS_ATTR_VERS_B ETAVB
812 WHERE ETAVB.attr_id = ETAVT.attr_id
813 AND ETAVB.item_catalog_group_id=ETAVT.item_catalog_group_id
814 AND ETAVB.icc_version_number=0)
815 AND ETAVT.transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
816 AND ETAVT.transaction_id IS NOT NULL
817 AND ETAVT.process_status = G_PROCESS_RECORD
818 AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
819
820 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if attr_name given with sync - convert to Update');
821 /* if attr_name is given with sync and exists */
822 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
823 SET transaction_type = G_UPDATE,
824 last_updated_by = G_USER_ID,
825 last_update_date = SYSDATE,
826 last_update_login = G_LOGIN_ID
827 WHERE attr_id IS NOT NULL
828 AND EXISTS (
829 SELECT 1
830 FROM EGO_TRANS_ATTR_VERS_B ETAVB
831 WHERE ETAVB.attr_id = ETAVT.attr_id
832 AND ETAVB.item_catalog_group_id=ETAVT.item_catalog_group_id
833 AND ETAVB.icc_version_number=0)
834 --AND attr_name IS NOT NULL
835 AND transaction_type =G_SYNC
836 AND transaction_id IS NOT NULL
837 AND process_status = G_PROCESS_RECORD
838 AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
839
840 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Getting attr_name if only display_name is given');
841 /* if attr_display name and sync given and it got converted into
842 update as here we are sure we got the attr_id so populating attr_name
843 if not given*/
844 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
845 SET attr_name= (SELECT attr_name
846 FROM ego_attrs_v EAV
847 WHERE EAV.attr_id= ETAVT.attr_id
848 )
849 WHERE ETAVT.attr_id IS NOT NULL
850 AND ETAVT.attr_display_name IS NOT NULL
851 AND ETAVT.transaction_type IN (G_UPDATE)
852 AND ETAVT.process_status = G_PROCESS_RECORD
853 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
854
855 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Getting other metadata from main table if not provided by user to complete the record');
856 /* to copy rest of the meta data for same attr_id,icc_id and version */
857 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
858 SET (uom_class,
859 default_value,
860 rejected_value,
861 required_flag,
862 readonly_flag,
863 hidden_flag,
864 searchable_flag,
865 check_eligibility,
866 value_set_id,
867 attr_display_name,
868 sequence) =
869 (SELECT nvl(a.uom_class,b.uom_class),
870 nvl(a.default_value,b.default_value),
871 nvl(a.rejected_value,b.rejected_value),
872 nvl(a.required_flag,b.required_flag),
873 nvl(a.readonly_flag,b.readonly_flag),
874 nvl(a.hidden_flag,b.hidden_flag),
875 nvl(a.searchable_flag,b.searchable_flag),
876 nvl(a.check_eligibility,b.check_eligibility),
877 nvl(a.value_set_id,b.value_set_id),
878 nvl(a.attr_display_name,b.attr_display_name),
879 b.sequence
880 FROM EGO_TRANS_ATTRS_VERS_INTF a,EGO_TRANS_ATTR_VERS_B b
881 WHERE a.attr_id=b.attr_id
882 AND a.item_catalog_group_id=b.item_catalog_group_id
883 AND b.icc_version_number=0
884 AND a.attr_id=ETAVT.attr_id)
885 WHERE attr_id IS NOT NULL
886 AND ETAVT.attr_name IS NOT NULL
887 AND ETAVT.transaction_type IN (G_UPDATE)
888 AND ETAVT.process_status = G_PROCESS_RECORD
889 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
890
891 -------------------------------------------
892 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking given attr_display name is valid or not.');
893
894 G_MESSAGE_NAME := 'EGO_ATTR_DISP_NAME_MISSING';
895 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
896 G_MESSAGE_TEXT := fnd_message.get;
897 /* given attr_display name is valid or not and getting id or not */
898
899 INSERT INTO mtl_interface_errors(
900 transaction_id,
901 unique_id,
902 organization_id,
903 column_name,
904 table_name,
905 message_name,
906 error_message,
907 BO_IDENTIFIER,
908 ENTITY_IDENTIFIER,
909 last_update_date,
910 last_updated_by,
911 creation_date,
912 created_by,
913 last_update_login,
914 request_id,
915 program_application_id,
916 program_id,
917 program_update_date)
918 SELECT transaction_id,
919 mtl_system_items_interface_s.nextval,
920 NULL,
921 NULL,
922 'EGO_TRANS_ATTRS_VERS_INTF',
923 G_MESSAGE_NAME,
924 G_MESSAGE_TEXT,
925 G_BO_IDENTIFIER,
926 G_ENTITY_IDENTIFIER,
927 NVL(last_update_date,SYSDATE),
928 NVL(last_updated_by,G_USER_ID),
929 NVL(creation_date,SYSDATE),
930 NVL(created_by,G_USER_ID),
931 NVL(last_update_login,G_LOGIN_ID),
932 G_REQUEST_ID,
933 NVL(program_application_id,G_PROG_APPL_ID),
934 NVL(program_id,G_PROGRAM_ID),
935 NVL(program_update_date,SYSDATE)
936 FROM EGO_TRANS_ATTRS_VERS_INTF
937 WHERE attr_id IS NULL
938 AND attr_display_name IS NOT NULL
939 AND transaction_type IN (G_UPDATE,G_DELETE)
940 AND transaction_id IS NOT NULL
941 AND process_status = G_PROCESS_RECORD
942 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
943
944
945 /*if attr disp name provided doesnt exists in table */
946 UPDATE EGO_TRANS_ATTRS_VERS_INTF
947 SET process_status = G_ERROR_RECORD,
948 last_updated_by = G_USER_ID,
949 last_update_date = SYSDATE,
950 last_update_login = G_LOGIN_ID
951 WHERE attr_id IS NULL
952 AND attr_display_name IS NOT NULL
953 AND transaction_type IN (G_UPDATE,G_DELETE)
954 AND transaction_id IS NOT NULL
955 AND process_status = G_PROCESS_RECORD
956 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
957
958 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'If attr_display_name is given with sync and not exists- convert to Create .');
959 /* converting sync to create or update */
960 /* if attr_display_name is given with sync and not exists */
961 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
962 SET transaction_type = G_CREATE,
963 last_updated_by = G_USER_ID,
964 last_update_date = SYSDATE,
965 last_update_login = G_LOGIN_ID
966 WHERE attr_id IS NULL
967 AND attr_display_name IS NOT NULL
968 AND transaction_type =G_SYNC
969 AND transaction_id IS NOT NULL
970 AND process_status = G_PROCESS_RECORD
971 AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
972
973 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if All Attr_name, Attr_Display_Name,Attr_id are NULL For Upd,Del,Sync');
974 /* if attr_name is null,attr_display_name is null and attr_id is null
975 with sync then error out */
976
977 UPDATE EGO_TRANS_ATTRS_VERS_INTF
978 SET process_status =G_ERROR_RECORD,
979 last_updated_by = G_USER_ID,
980 last_update_date = SYSDATE,
981 last_update_login = G_LOGIN_ID
982 WHERE attr_id IS NULL
983 AND attr_display_name IS NULL
984 AND attr_name IS NULL
985 AND transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
986 AND transaction_id IS NOT NULL
987 AND process_status = G_PROCESS_RECORD
988 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
989
990 -------------------------------------------
991 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if Association_id is Valid or not');
992
993 G_MESSAGE_NAME := 'EGO_TA_ASSOC_FAILED';
994 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
995 G_MESSAGE_TEXT := fnd_message.get;
996 /* Association id not able to convert in case of upate,delete,sync
997 or association id provided doesnt exists in table*/
998
999 INSERT INTO mtl_interface_errors(
1000 transaction_id,
1001 unique_id,
1002 organization_id,
1003 column_name,
1004 table_name,
1005 message_name,
1006 error_message,
1007 BO_IDENTIFIER,
1008 ENTITY_IDENTIFIER,
1009 last_update_date,
1010 last_updated_by,
1011 creation_date,
1012 created_by,
1013 last_update_login,
1014 request_id,
1015 program_application_id,
1016 program_id,
1017 program_update_date)
1018 SELECT transaction_id,
1019 mtl_system_items_interface_s.nextval,
1020 NULL,
1021 NULL,
1022 'EGO_TRANS_ATTRS_VERS_INTF',
1023 G_MESSAGE_NAME,
1024 G_MESSAGE_TEXT,
1025 G_BO_IDENTIFIER,
1026 G_ENTITY_IDENTIFIER,
1027 NVL(last_update_date,SYSDATE),
1028 NVL(last_updated_by,G_USER_ID),
1029 NVL(creation_date,SYSDATE),
1030 NVL(created_by,G_USER_ID),
1031 NVL(last_update_login,G_LOGIN_ID),
1032 G_REQUEST_ID,
1033 NVL(program_application_id,G_PROG_APPL_ID),
1034 NVL(program_id,G_PROGRAM_ID),
1035 NVL(program_update_date,SYSDATE)
1036 FROM EGO_TRANS_ATTRS_VERS_INTF
1037 WHERE attr_id IS NOT NULL
1038 AND item_catalog_group_id IS NOT NULL
1039 AND association_id IS NULL
1040 AND transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
1041 AND transaction_id IS NOT NULL
1042 AND process_status = G_PROCESS_RECORD
1043 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
1044
1045
1046 /*if not able to convert association id from given attr_id and icc_id
1047 in case of upd,del,sync */
1048 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1049 SET process_status =G_ERROR_RECORD,
1050 last_updated_by = G_USER_ID,
1051 last_update_date = SYSDATE,
1052 last_update_login = G_LOGIN_ID
1053 WHERE attr_id IS NOT NULL
1054 AND item_catalog_group_id IS NOT NULL
1055 AND association_id IS NULL
1056 AND transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
1057 AND transaction_id IS NOT NULL
1058 AND process_status = G_PROCESS_RECORD
1059 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
1060
1061
1062 G_MESSAGE_NAME := 'EGO_ASSOC_ID_MISSING';
1063 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
1064 G_MESSAGE_TEXT := fnd_message.get;
1065 /*given association is valid or not*/
1066 INSERT INTO mtl_interface_errors(
1067 transaction_id,
1068 unique_id,
1069 organization_id,
1070 column_name,
1071 table_name,
1072 message_name,
1073 error_message,
1074 BO_IDENTIFIER,
1075 ENTITY_IDENTIFIER,
1076 last_update_date,
1077 last_updated_by,
1078 creation_date,
1079 created_by,
1080 last_update_login,
1081 request_id,
1082 program_application_id,
1083 program_id,
1084 program_update_date)
1085 SELECT transaction_id,
1086 mtl_system_items_interface_s.nextval,
1087 NULL,
1088 NULL,
1089 'EGO_TRANS_ATTRS_VERS_INTF',
1090 G_MESSAGE_NAME,
1091 G_MESSAGE_TEXT,
1092 G_BO_IDENTIFIER,
1093 G_ENTITY_IDENTIFIER,
1094 NVL(last_update_date,SYSDATE),
1095 NVL(last_updated_by,G_USER_ID),
1096 NVL(creation_date,SYSDATE),
1097 NVL(created_by,G_USER_ID),
1098 NVL(last_update_login,G_LOGIN_ID),
1099 G_REQUEST_ID,
1100 NVL(program_application_id,G_PROG_APPL_ID),
1101 NVL(program_id,G_PROGRAM_ID),
1102 NVL(program_update_date,SYSDATE)
1103 FROM EGO_TRANS_ATTRS_VERS_INTF ETAVT
1104 WHERE attr_id IS NOT NULL
1105 AND item_catalog_group_id IS NOT NULL
1106 AND association_id IS NOT NULL
1107 AND NOT EXISTS (
1108 SELECT 1
1109 FROM EGO_TRANS_ATTR_VERS_B ETAVB
1110 WHERE ETAVB.association_id = ETAVT.association_id
1111 AND ETAVB.item_Catalog_group_id= ETAVT.item_Catalog_group_id
1112 AND ETAVB.icc_version_number=0)
1113 AND ETAVT.transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
1114 AND ETAVT.transaction_id IS NOT NULL
1115 AND ETAVT.process_status = G_PROCESS_RECORD
1116 AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
1117
1118 /*if association provided and it is not valid*/
1119 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1120 SET process_status =G_ERROR_RECORD,
1121 last_updated_by = G_USER_ID,
1122 last_update_date = SYSDATE,
1123 last_update_login = G_LOGIN_ID
1124 WHERE attr_id IS NOT NULL
1125 AND item_catalog_group_id IS NOT NULL
1126 AND association_id IS NOT NULL
1127 AND NOT EXISTS (
1128 SELECT 1
1129 FROM EGO_TRANS_ATTR_VERS_B ETAVB
1130 WHERE ETAVB.association_id = ETAVT.association_id
1131 AND ETAVB.item_Catalog_group_id= ETAVT.item_Catalog_group_id
1132 AND ETAVB.icc_version_number=0)
1133 AND transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
1134 AND transaction_id IS NOT NULL
1135 AND process_status = G_PROCESS_RECORD
1136 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
1137
1138 -------------------------------------------
1139
1140 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking Metadata Flag Values');
1141
1142 G_MESSAGE_NAME := 'EGO_METADATA_FLAGS_INVALID';
1143 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
1144 G_MESSAGE_TEXT := fnd_message.get;
1145
1146 INSERT INTO mtl_interface_errors(
1147 transaction_id,
1148 unique_id,
1149 organization_id,
1150 column_name,
1151 table_name,
1152 message_name,
1153 error_message,
1154 BO_IDENTIFIER,
1155 ENTITY_IDENTIFIER,
1156 last_update_date,
1157 last_updated_by,
1158 creation_date,
1159 created_by,
1160 last_update_login,
1161 request_id,
1162 program_application_id,
1163 program_id,
1164 program_update_date)
1165 SELECT transaction_id,
1166 mtl_system_items_interface_s.nextval,
1167 NULL,
1168 NULL,
1169 'EGO_TRANS_ATTRS_VERS_INTF',
1170 G_MESSAGE_NAME,
1171 G_MESSAGE_TEXT,
1172 G_BO_IDENTIFIER,
1173 G_ENTITY_IDENTIFIER,
1174 NVL(last_update_date,SYSDATE),
1175 NVL(last_updated_by,G_USER_ID),
1176 NVL(creation_date,SYSDATE),
1177 NVL(created_by,G_USER_ID),
1178 NVL(last_update_login,G_LOGIN_ID),
1179 G_REQUEST_ID,
1180 NVL(program_application_id,G_PROG_APPL_ID),
1181 NVL(program_id,G_PROGRAM_ID),
1182 NVL(program_update_date,SYSDATE)
1183 FROM EGO_TRANS_ATTRS_VERS_INTF
1184 WHERE transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
1185 AND transaction_id IS NOT NULL
1186 AND ((required_flag IS NOT NULL) OR (readonly_flag IS NOT NULL)
1187 OR (hidden_flag IS NOT NULL) OR (searchable_flag IS NOT NULL)
1188 OR (check_eligibility IS NOT NULL))
1189 AND ( (required_flag NOT IN ('Y','N')) OR (readonly_flag NOT IN ('Y','N')) OR (hidden_flag NOT IN ('Y','N')) OR (searchable_flag NOT IN ('Y','N'))
1190 OR (check_eligibility NOT IN ('Y','N')))
1191 AND process_status = G_PROCESS_RECORD
1192 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
1193
1194 /*IF any of the flag has value other than 'Y' or 'N'*/
1195 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1196 SET process_status = G_ERROR_RECORD,
1197 last_updated_by = G_USER_ID,
1198 last_update_date = SYSDATE,
1199 last_update_login = G_LOGIN_ID
1200 WHERE ((required_flag IS NOT NULL) OR (readonly_flag IS NOT NULL)
1201 OR (hidden_flag IS NOT NULL) OR (searchable_flag IS NOT NULL)
1202 OR (check_eligibility IS NOT NULL))
1203 AND ((Upper(required_flag) NOT IN ('Y','N')) OR (Upper(readonly_flag) NOT IN ('Y','N'))
1204 OR (Upper(hidden_flag) NOT IN ('Y','N')) OR (Upper(searchable_flag) NOT IN ('Y','N'))
1205 OR (Upper(check_eligibility) NOT IN ('Y','N')))
1206 AND transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
1207 AND transaction_id IS NOT NULL
1208 AND process_status = G_PROCESS_RECORD
1209 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
1210
1211 -------------------------------------------
1212
1213 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking Valid Values for data type while CREATE');
1214
1215 G_MESSAGE_NAME := 'EGO_DATA_TYPE_INVALID';
1216 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
1217 G_MESSAGE_TEXT := fnd_message.get;
1218
1219 INSERT INTO mtl_interface_errors(
1220 transaction_id,
1221 unique_id,
1222 organization_id,
1223 column_name,
1224 table_name,
1225 message_name,
1226 error_message,
1227 BO_IDENTIFIER,
1228 ENTITY_IDENTIFIER,
1229 last_update_date,
1230 last_updated_by,
1231 creation_date,
1232 created_by,
1233 last_update_login,
1234 request_id,
1235 program_application_id,
1236 program_id,
1237 program_update_date)
1238 SELECT transaction_id,
1239 mtl_system_items_interface_s.nextval,
1240 NULL,
1241 NULL,
1242 'EGO_TRANS_ATTRS_VERS_INTF',
1243 G_MESSAGE_NAME,
1244 G_MESSAGE_TEXT,
1245 G_BO_IDENTIFIER,
1246 G_ENTITY_IDENTIFIER,
1247 NVL(last_update_date,SYSDATE),
1248 NVL(last_updated_by,G_USER_ID),
1249 NVL(creation_date,SYSDATE),
1250 NVL(created_by,G_USER_ID),
1251 NVL(last_update_login,G_LOGIN_ID),
1252 G_REQUEST_ID,
1253 NVL(program_application_id,G_PROG_APPL_ID),
1254 NVL(program_id,G_PROGRAM_ID),
1255 NVL(program_update_date,SYSDATE)
1256 FROM EGO_TRANS_ATTRS_VERS_INTF
1257 WHERE transaction_type IN (G_CREATE)
1258 AND transaction_id IS NOT NULL
1259 AND data_type IS NOT NULL
1260 AND Upper(data_type) NOT IN ('C','A','N','X','Y')
1261 AND process_status = G_PROCESS_RECORD
1262 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
1263
1264 /*if association provided and it is not valid*/
1265 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1266 SET process_status = G_ERROR_RECORD,
1267 last_updated_by = G_USER_ID,
1268 last_update_date = SYSDATE,
1269 last_update_login = G_LOGIN_ID
1270 WHERE data_type IS NOT NULL
1271 AND Upper(data_type) NOT IN ('C','A','N','X','Y')
1272 AND transaction_type IN (G_CREATE)
1273 AND transaction_id IS NOT NULL
1274 AND process_status = G_PROCESS_RECORD
1275 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
1276
1277 -------------------------------------------
1278
1279 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking Association_id and attr_id should be null while T_Type is CREATE');
1280
1281 G_MESSAGE_NAME := 'EGO_INCRRCT_VAL_ASSO_ATTR_ID';
1282 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
1283 G_MESSAGE_TEXT := fnd_message.get;
1284
1285 INSERT INTO mtl_interface_errors(
1286 transaction_id,
1287 unique_id,
1288 organization_id,
1289 column_name,
1290 table_name,
1291 message_name,
1292 error_message,
1293 BO_IDENTIFIER,
1294 ENTITY_IDENTIFIER,
1295 last_update_date,
1296 last_updated_by,
1297 creation_date,
1298 created_by,
1299 last_update_login,
1300 request_id,
1301 program_application_id,
1302 program_id,
1303 program_update_date)
1304 SELECT transaction_id,
1305 mtl_system_items_interface_s.nextval,
1306 NULL,
1307 NULL,
1308 'EGO_TRANS_ATTRS_VERS_INTF',
1309 G_MESSAGE_NAME,
1310 G_MESSAGE_TEXT,
1311 G_BO_IDENTIFIER,
1312 G_ENTITY_IDENTIFIER,
1313 NVL(last_update_date,SYSDATE),
1314 NVL(last_updated_by,G_USER_ID),
1315 NVL(creation_date,SYSDATE),
1316 NVL(created_by,G_USER_ID),
1317 NVL(last_update_login,G_LOGIN_ID),
1318 G_REQUEST_ID,
1319 NVL(program_application_id,G_PROG_APPL_ID),
1320 NVL(program_id,G_PROGRAM_ID),
1321 NVL(program_update_date,SYSDATE)
1322 FROM EGO_TRANS_ATTRS_VERS_INTF
1323 WHERE transaction_type IN (G_CREATE)
1324 AND transaction_id IS NOT NULL
1325 AND process_status = G_PROCESS_RECORD
1326 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1327 AND ((Association_id IS NOT NULL) OR (Attr_id IS NOT NULL));
1328
1329 /* Association_id and Attr_id should be NULL if CREATING TA */
1330 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1331 SET process_status = G_ERROR_RECORD,
1332 last_updated_by = G_USER_ID,
1333 last_update_date = SYSDATE,
1334 last_update_login = G_LOGIN_ID
1335 WHERE transaction_type IN (G_CREATE)
1336 AND transaction_id IS NOT NULL
1337 AND process_status = G_PROCESS_RECORD
1338 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1339 AND ((Association_id IS NOT NULL) OR (Attr_id IS NOT NULL));
1340
1341 -------------------------------------------
1342 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking ATTR_NAME,ATTR_DISPLAY_NAME,SEQUENCE Should not be null while CREATE');
1343
1344 G_MESSAGE_NAME := 'EGO_TA_PK_NULL';
1345 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
1346 G_MESSAGE_TEXT := fnd_message.get;
1347 /* ATTR_NAME,ATTR_DISPLAY_NAME,SEQUENCE Should not be null if CREATE*/
1348 INSERT INTO mtl_interface_errors(
1349 transaction_id,
1350 unique_id,
1351 organization_id,
1352 column_name,
1353 table_name,
1354 message_name,
1355 error_message,
1356 BO_IDENTIFIER,
1357 ENTITY_IDENTIFIER,
1358 last_update_date,
1359 last_updated_by,
1360 creation_date,
1361 created_by,
1362 last_update_login,
1363 request_id,
1364 program_application_id,
1365 program_id,
1366 program_update_date)
1367 SELECT transaction_id,
1368 mtl_system_items_interface_s.nextval,
1369 NULL,
1370 NULL,
1371 'EGO_TRANS_ATTRS_VERS_INTF',
1372 G_MESSAGE_NAME,
1373 G_MESSAGE_TEXT,
1374 G_BO_IDENTIFIER,
1375 G_ENTITY_IDENTIFIER,
1376 NVL(last_update_date,SYSDATE),
1377 NVL(last_updated_by,G_USER_ID),
1378 NVL(creation_date,SYSDATE),
1379 NVL(created_by,G_USER_ID),
1380 NVL(last_update_login,G_LOGIN_ID),
1381 G_REQUEST_ID,
1382 NVL(program_application_id,G_PROG_APPL_ID),
1383 NVL(program_id,G_PROGRAM_ID),
1384 NVL(program_update_date,SYSDATE)
1385 FROM EGO_TRANS_ATTRS_VERS_INTF
1386 WHERE transaction_type IN (G_CREATE)
1387 AND transaction_id IS NOT NULL
1388 AND process_status = G_PROCESS_RECORD
1389 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1390 AND ((attr_name IS NULL) OR (attr_display_name IS NULL) OR (SEQUENCE IS NULL)) ;
1391
1392 /* ATTR_NAME,ATTR_DISPLAY_NAME,SEQUENCE Should not be null if CREATE*/
1393 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1394 SET process_status = G_ERROR_RECORD,
1395 last_updated_by = G_USER_ID,
1396 last_update_date = SYSDATE,
1397 last_update_login = G_LOGIN_ID
1398 WHERE transaction_type IN (G_CREATE)
1399 AND transaction_id IS NOT NULL
1400 AND process_status=G_PROCESS_RECORD
1401 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1402 AND ((attr_name IS NULL) OR (attr_display_name IS NULL) OR (SEQUENCE IS NULL));
1403
1404 -------------------------------------------------
1405 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if ICC version is NULL while CREATE');
1406
1407 G_MESSAGE_NAME := 'EGO_ICC_VERSION_NULL';
1408 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
1409 G_MESSAGE_TEXT := fnd_message.get;
1410 /* ICC_VERSION NUMBER should not be NULL in CREATE flow */
1411 INSERT INTO mtl_interface_errors(
1412 transaction_id,
1413 unique_id,
1414 organization_id,
1415 column_name,
1416 table_name,
1417 message_name,
1418 error_message,
1419 BO_IDENTIFIER,
1420 ENTITY_IDENTIFIER,
1421 last_update_date,
1422 last_updated_by,
1423 creation_date,
1424 created_by,
1425 last_update_login,
1426 request_id,
1427 program_application_id,
1428 program_id,
1429 program_update_date)
1430 SELECT transaction_id,
1431 mtl_system_items_interface_s.nextval,
1432 NULL,
1433 NULL,
1434 'EGO_TRANS_ATTRS_VERS_INTF',
1435 G_MESSAGE_NAME,
1436 G_MESSAGE_TEXT,
1437 G_BO_IDENTIFIER,
1438 G_ENTITY_IDENTIFIER,
1439 NVL(last_update_date,SYSDATE),
1440 NVL(last_updated_by,G_USER_ID),
1441 NVL(creation_date,SYSDATE),
1442 NVL(created_by,G_USER_ID),
1443 NVL(last_update_login,G_LOGIN_ID),
1444 G_REQUEST_ID,
1445 NVL(program_application_id,G_PROG_APPL_ID),
1446 NVL(program_id,G_PROGRAM_ID),
1447 NVL(program_update_date,SYSDATE)
1448 FROM EGO_TRANS_ATTRS_VERS_INTF
1449 WHERE transaction_type IN (G_CREATE)
1450 AND transaction_id IS NOT NULL
1451 AND process_status = G_PROCESS_RECORD
1452 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1453 AND icc_version_number IS NULL;
1454 --AND item_catalog_group_id IS NOT NULL;/* bug 9752139 */
1455
1456 /* ICC_VERSION NUMBER should not be NULL in CREATE flow */
1457 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1458 SET process_status = G_ERROR_RECORD,
1459 last_updated_by = G_USER_ID,
1460 last_update_date = SYSDATE,
1461 last_update_login = G_LOGIN_ID
1462 WHERE transaction_type IN (G_CREATE)
1463 AND transaction_id IS NOT NULL
1464 AND process_status = G_PROCESS_RECORD
1465 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1466 AND icc_version_number IS NULL;
1467 --AND item_catalog_group_id IS NOT NULL; /* bug 9752139 */
1468
1469 -------------------------------------------------
1470 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Defaulting Data Type and Display Flag while CREATE');
1471
1472 /*Defaulting data_type to'C' if not passed*/
1473 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1474 SET data_type = G_CHAR_DATA_TYPE
1475 WHERE transaction_type=G_CREATE
1476 AND transaction_id IS NOT NULL
1477 AND process_status = G_PROCESS_RECORD
1478 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
1479 AND data_type IS NULL;
1480
1481 /*Sets the display_flag as 'T'*/
1482 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1483 SET display_flag = 'T'
1484 WHERE transaction_type=G_CREATE
1485 AND transaction_id IS NOT NULL
1486 AND process_status = G_PROCESS_RECORD
1487 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
1488
1489 /*Sets the Metadata_level as 'ICC'*/
1490 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1491 SET Metadata_level = 'ICC'
1492 WHERE transaction_type=G_CREATE
1493 AND transaction_id IS NOT NULL
1494 AND process_status = G_PROCESS_RECORD
1495 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
1496
1497 /*Assigning icc_version_no as 0 if not provided in update and delete*/
1498 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1499 SET icc_version_number = 0
1500 WHERE transaction_type IN (G_UPDATE,G_DELETE)
1501 AND transaction_id IS NOT NULL
1502 AND process_status = G_PROCESS_RECORD
1503 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
1504 AND icc_version_number IS NULL;
1505
1506
1507 -------------------------------------------------
1508 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Icc_Version_Number greater than zero not allowed while UPDATE and DELETE');
1509
1510 G_MESSAGE_NAME := 'EGO_VER_GR_ZERO';
1511 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
1512 G_MESSAGE_TEXT := fnd_message.get;
1513 /* Icc_Version_Number greater than zero not allowed while UPDATE and DELETE*/
1514 INSERT INTO mtl_interface_errors(
1515 transaction_id,
1516 unique_id,
1517 organization_id,
1518 column_name,
1519 table_name,
1520 message_name,
1521 error_message,
1522 BO_IDENTIFIER,
1523 ENTITY_IDENTIFIER,
1524 last_update_date,
1525 last_updated_by,
1526 creation_date,
1527 created_by,
1528 last_update_login,
1529 request_id,
1530 program_application_id,
1531 program_id,
1532 program_update_date)
1533 SELECT transaction_id,
1534 mtl_system_items_interface_s.nextval,
1535 NULL,
1536 NULL,
1537 'EGO_TRANS_ATTRS_VERS_INTF',
1538 G_MESSAGE_NAME,
1539 G_MESSAGE_TEXT,
1540 G_BO_IDENTIFIER,
1541 G_ENTITY_IDENTIFIER,
1542 NVL(last_update_date,SYSDATE),
1543 NVL(last_updated_by,G_USER_ID),
1544 NVL(creation_date,SYSDATE),
1545 NVL(created_by,G_USER_ID),
1546 NVL(last_update_login,G_LOGIN_ID),
1547 G_REQUEST_ID,
1548 NVL(program_application_id,G_PROG_APPL_ID),
1549 NVL(program_id,G_PROGRAM_ID),
1550 NVL(program_update_date,SYSDATE)
1551 FROM EGO_TRANS_ATTRS_VERS_INTF
1552 WHERE transaction_type IN (G_UPDATE,G_DELETE)
1553 AND transaction_id IS NOT NULL
1554 AND process_status = G_PROCESS_RECORD
1555 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1556 AND icc_version_number>0;
1557
1558 /* Icc_Version_Number greater than zero not allowed while UPDATE and DELETE*/
1559 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1560 SET process_status = G_ERROR_RECORD,
1561 last_updated_by = G_USER_ID,
1562 last_update_date = SYSDATE,
1563 last_update_login = G_LOGIN_ID
1564 WHERE transaction_type IN (G_UPDATE,G_DELETE)
1565 AND transaction_id IS NOT NULL
1566 AND process_status=G_PROCESS_RECORD
1567 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1568 AND Nvl(icc_version_number,0)>0;
1569
1570 -------------------------------------------------
1571 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Association_id and attr_id should not be null for DELETE');
1572
1573 G_MESSAGE_NAME := 'EGO_ASSO_ATTR_NULL_DEL';
1574 FND_MESSAGE.SET_NAME('EGO',G_MESSAGE_NAME);
1575 G_MESSAGE_TEXT := fnd_message.get;
1576 /*Association_id and attr_id should not be null for DELETE*/
1577 INSERT INTO mtl_interface_errors(
1578 transaction_id,
1579 unique_id,
1580 organization_id,
1581 column_name,
1582 table_name,
1583 message_name,
1584 error_message,
1585 BO_IDENTIFIER,
1586 ENTITY_IDENTIFIER,
1587 last_update_date,
1588 last_updated_by,
1589 creation_date,
1590 created_by,
1591 last_update_login,
1592 request_id,
1593 program_application_id,
1594 program_id,
1595 program_update_date)
1596 SELECT transaction_id,
1597 mtl_system_items_interface_s.nextval,
1598 NULL,
1599 NULL,
1600 'EGO_TRANS_ATTRS_VERS_INTF',
1601 G_MESSAGE_NAME,
1602 G_MESSAGE_TEXT,
1603 G_BO_IDENTIFIER,
1604 G_ENTITY_IDENTIFIER,
1605 NVL(last_update_date,SYSDATE),
1606 NVL(last_updated_by,G_USER_ID),
1607 NVL(creation_date,SYSDATE),
1608 NVL(created_by,G_USER_ID),
1609 NVL(last_update_login,G_LOGIN_ID),
1610 G_REQUEST_ID,
1611 NVL(program_application_id,G_PROG_APPL_ID),
1612 NVL(program_id,G_PROGRAM_ID),
1613 NVL(program_update_date,SYSDATE)
1614 FROM EGO_TRANS_ATTRS_VERS_INTF
1615 WHERE transaction_type IN (G_DELETE)
1616 AND transaction_id IS NOT NULL
1617 AND process_status = G_PROCESS_RECORD
1618 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1619 AND ((association_id IS NULL) OR (attr_id IS NULL));
1620
1621 /*Association_id and attr_id should not be null for DELETE */
1622 UPDATE EGO_TRANS_ATTRS_VERS_INTF
1623 SET process_status = G_ERROR_RECORD,
1624 last_updated_by = G_USER_ID,
1625 last_update_date = SYSDATE,
1626 last_update_login = G_LOGIN_ID
1627 WHERE transaction_type IN (G_DELETE)
1628 AND transaction_id IS NOT NULL
1629 AND process_status = G_PROCESS_RECORD
1630 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
1631 AND ((association_id IS NULL) OR (attr_id IS NULL));
1632
1633 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Bulk_Validate_Trans_Attrs ');
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception When Others Bulk_Validate_Trans_Attrs ');
1637 END Bulk_Validate_Trans_Attrs;
1638
1639 --================= Bulk_Validate_Trans_Attrs_ICC ========----
1640
1641 PROCEDURE Bulk_Validate_Trans_Attrs_ICC (
1642 p_set_process_id IN NUMBER,
1643 p_item_catalog_group_id IN NUMBER,
1644 p_item_catalog_group_name IN VARCHAR2)
1645 IS
1646 l_proc_name VARCHAR2(200) := 'Bulk_Validate_Trans_Attrs_ICC';
1647 BEGIN
1648
1649 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Bulk_Validate_Trans_Attrs_ICC');
1650 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Updating ICC name from ICC id');
1651 /* ICC name to ICC id conversion */
1652 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1653 SET item_catalog_group_id = p_item_catalog_group_id
1654 WHERE ETAVT.item_catalog_group_name IS NOT NULL
1655 AND ETAVT.item_catalog_group_id IS NULL
1656 AND ETAVT.process_status = G_PROCESS_RECORD
1657 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
1658 AND Upper(ETAVT.item_catalog_group_name) =Upper(p_item_catalog_group_name); -- added to make it ICC specific.
1659
1660 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' End Bulk_Validate_Trans_Attrs_ICC');
1661
1662 EXCEPTION
1663 WHEN OTHERS THEN
1664 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Bulk_Validate_Trans_Attrs_ICC');
1665 END Bulk_Validate_Trans_Attrs_ICC;
1666
1667
1668 --================= Value_to_Id ========----
1669
1670 PROCEDURE Value_to_Id(
1671 p_set_process_id IN NUMBER)
1672 IS
1673 l_proc_name VARCHAR2(200) := 'Value_to_Id';
1674 BEGIN
1675 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Value_to_Id');
1676 --ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Converting ICC name to ICC id');
1677 /* ICC name to ICC id conversion */
1678 /* NOt Req now are we are doing this in Bulk_Validate_Trans_Attrs_ICC */
1679 /*
1680 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1681 SET item_catalog_group_id = (SELECt icc_kfv.item_catalog_group_id
1682 FROM mtl_item_catalog_groups_kfv icc_kfv
1683 where Upper(icc_kfv.concatenated_segments) = Upper(ETAVT.item_catalog_group_name)
1684 )
1685 WHERE ETAVT.item_catalog_group_name IS NOT NULL
1686 AND ETAVT.item_catalog_group_id IS NULL
1687 --AND ETAVT.transaction_type IN (G_CREATE,G_UPDATE,G_DELETE_G_SYNC)
1688 AND ETAVT.process_status = G_PROCESS_RECORD
1689 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));*/
1690
1691 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Converting Value set name to value set id');
1692 /*Value set name to value set id conversion*/
1693 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1694 SET value_set_id = ( SELECT flex_value_set_id
1695 FROM fnd_flex_value_sets
1696 WHERE Upper(flex_value_set_name) = Upper(ETAVT.value_set_name)
1697 )
1698 WHERE ETAVT.value_set_name IS NOT NULL
1699 AND ETAVT.value_set_id IS NULL
1700 AND ETAVT.transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
1701 AND ETAVT.process_status = G_PROCESS_RECORD
1702 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
1703
1704 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Converting Attr_Name to Attr_id');
1705 /* Attr_Name to Attr_id conversion */
1706 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1707 SET (attr_id/*,attr_display_name,sequence*/) = ( SELECT attr_id/*,attr_display_name,sequence*/
1708 FROM ego_trans_attr_vers_b
1709 WHERE attr_id IN ( SELECT efdcue.attr_id
1710 FROM fnd_descr_flex_column_usages fdfcu,
1711 ego_fnd_df_col_usgs_ext efdcue
1712 WHERE fdfcu.application_id = efdcue.application_id
1713 AND fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
1714 AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
1715 AND fdfcu.application_column_name = efdcue.application_column_name
1716 AND fdfcu.application_id = G_APPLICATION_ID
1717 AND fdfcu.descriptive_flexfield_name = 'EGO_ITEM_TRANS_ATTR_GROUP'
1718 AND fdfcu.descriptive_flex_context_code IN (SELECT attr_group_name
1719 FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
1720 WHERE association_id in (SELECT association_id
1721 FROM EGO_OBJ_AG_ASSOCS_B
1722 WHERE classification_code=ETAVT.item_catalog_group_id)
1723 AND ATTR_GROUP_TYPE= 'EGO_ITEM_TRANS_ATTR_GROUP'
1724 )
1725 AND Upper(fdfcu.end_user_column_name) = Upper(ETAVT.attr_name)
1726 )
1727 AND item_catalog_group_id=ETAVT.item_catalog_group_id
1728 AND icc_version_number=0 -- we only allow update on draft
1729 )
1730 WHERE ETAVT.attr_name IS NOT NULL
1731 AND ETAVT.attr_id IS NULL
1732 AND ETAVT.transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
1733 AND ETAVT.process_status = G_PROCESS_RECORD
1734 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
1735
1736
1737 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Converting attr display name to attr_id ');
1738 /*attr display name to attr_id conversion*/
1739 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1740 SET attr_id = ( SELECT attr_id
1741 FROM EGO_TRANS_ATTR_VERS_B
1742 WHERE Upper(attr_display_name) = Upper(ETAVT.attr_display_name)
1743 AND item_catalog_group_id=ETAVT.item_catalog_group_id
1744 AND icc_version_number=0
1745 )
1746 WHERE ETAVT.attr_display_name IS NOT NULL
1747 AND ETAVT.attr_id IS NULL
1748 AND ETAVT.transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
1749 AND ETAVT.process_status = G_PROCESS_RECORD
1750 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
1751
1752 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Converting associaton_id from icc_id,attr_id ');
1753 /* associaton_id from icc_id */
1754 UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
1755 SET association_id = ( SELECT association_id
1756 FROM EGO_TRANS_ATTR_VERS_B
1757 WHERE attr_id= ETAVT.attr_id
1758 AND item_catalog_group_id=ETAVT.item_catalog_group_id
1759 AND icc_version_number=0
1760 )
1761 WHERE ETAVT.attr_id IS NOT NULL
1762 AND ETAVT.item_catalog_group_id IS NOT NULL
1763 AND ETAVT.association_id IS NULL
1764 AND ETAVT.icc_version_number=0
1765 AND ETAVT.transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
1766 AND ETAVT.process_status = G_PROCESS_RECORD
1767 AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
1768
1769 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Value_to_Id');
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Value_to_Id');
1773 END Value_to_Id;
1774
1775 --=================Load_Trans_Attrs_recs ========
1776
1777 PROCEDURE Load_Trans_Attrs_recs(
1778 p_set_process_id IN NUMBER,
1779 p_item_catalog_group_id IN NUMBER,
1780 p_icc_version_number_intf IN NUMBER,
1781 p_icc_version_number_act IN NUMBER,
1782 x_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
1783 x_return_status OUT NOCOPY VARCHAR2,
1784 x_return_msg OUT NOCOPY VARCHAR2)
1785 IS
1786 CURSOR c_ta IS
1787 SELECT *
1788 FROM ego_trans_attrs_vers_intf
1789 WHERE ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
1790 AND transaction_id IS NOT NULL
1791 AND process_status=G_PROCESS_RECORD
1792 AND item_catalog_group_id=p_item_catalog_group_id /* for integration with version*/
1793 AND icc_version_number= p_icc_version_number_intf
1794 ORDER BY transaction_type,icc_version_number;
1795
1796 l_proc_name VARCHAR2(30):= 'Load_Trans_Attrs_recs';
1797
1798 BEGIN
1799 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Load_Trans_Attrs_recs');
1800
1801 OPEN c_ta;
1802 FETCH c_ta BULK COLLECT INTO x_ta_intf_tbl; --LIMIT 2000;
1803 CLOSE c_ta;
1804 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Load_Trans_Attrs_recs.No of rec in intf pl-sql tbl :'||x_ta_intf_tbl.count );
1805
1806 EXCEPTION
1807 WHEN OTHERS THEN
1808 x_return_status:= G_RET_STS_UNEXP_ERROR;
1809 x_return_msg:= G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
1810 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception when Others in Load_Trans_Attrs_recs');
1811 END Load_Trans_Attrs_recs;
1812
1813 --=================convert_intf_rec_to_api_rec========
1814
1815 PROCEDURE convert_intf_rec_to_api_rec (
1816 p_ta_intf_tbl IN TA_Intf_Tbl,
1817 x_ego_ta_tbl OUT NOCOPY ego_tran_attr_tbl)
1818 IS
1819 l_proc_name VARCHAR2(200) := 'convert_intf_rec_to_api_rec';
1820 BEGIN
1821 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering convert_intf_rec_to_api_rec');
1822 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Converting interface rec to prod rec type');
1823 -- Initializing table type
1824 x_ego_ta_tbl:= EGO_TRAN_ATTR_TBL(NULL);
1825
1826 FOR i IN p_ta_intf_tbl.first..p_ta_intf_tbl.last LOOP
1827
1828 --x_ego_ta_tbl.extend;
1829 x_ego_ta_tbl(1):= ego_tran_attr_rec( p_ta_intf_tbl(i).Association_Id,
1830 p_ta_intf_tbl(i).Attr_Id,
1831 p_ta_intf_tbl(i).icc_version_number,
1832 p_ta_intf_tbl(i).revision_id,
1833 p_ta_intf_tbl(i).SEQUENCE,
1834 p_ta_intf_tbl(i).Value_Set_Id,
1835 p_ta_intf_tbl(i).Uom_Class,
1836 p_ta_intf_tbl(i).default_value,
1837 p_ta_intf_tbl(i).Rejected_Value,
1838 p_ta_intf_tbl(i).Required_Flag,
1839 p_ta_intf_tbl(i).Readonly_Flag,
1840 p_ta_intf_tbl(i).Hidden_Flag,
1841 p_ta_intf_tbl(i).Searchable_Flag,
1842 p_ta_intf_tbl(i).Check_Eligibility,
1843 p_ta_intf_tbl(i).Inventory_Item_Id,
1844 p_ta_intf_tbl(i).Organization_Id,
1845 p_ta_intf_tbl(i).Metadata_Level,
1846 p_ta_intf_tbl(i).Created_By,
1847 p_ta_intf_tbl(i).Creation_Date,
1848 p_ta_intf_tbl(i).Last_Updated_By,
1849 p_ta_intf_tbl(i).Last_Update_Date,
1850 p_ta_intf_tbl(i).Last_Update_Login,
1851 p_ta_intf_tbl(i).Program_Application_Id,
1852 p_ta_intf_tbl(i).Program_Id,
1853 p_ta_intf_tbl(i).Program_Update_Date,
1854 p_ta_intf_tbl(i).Request_Id,
1855 p_ta_intf_tbl(i).Item_Catalog_Group_Id,
1856 p_ta_intf_tbl(i).Attr_Name,
1857 p_ta_intf_tbl(i).Attr_Display_Name,
1858 p_ta_intf_tbl(i).Data_Type,
1859 p_ta_intf_tbl(i).display_flag,
1860 p_ta_intf_tbl(i).Value_Set_Name
1861 );
1862
1863 END LOOP;
1864 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End convert_intf_rec_to_api_rec');
1865 EXCEPTION
1866 WHEN OTHERS THEN
1867 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception when Others in convert_intf_rec_to_api_rec');
1868 END convert_intf_rec_to_api_rec;
1869
1870 --=================Process_Trans_Attrs ========
1871 PROCEDURE Process_Trans_Attrs (
1872 p_api_version IN NUMBER,
1873 p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
1874 p_item_catalog_group_id IN NUMBER,
1875 p_icc_version_number_intf IN NUMBER,
1876 p_icc_version_number_act IN NUMBER,
1877 x_return_status OUT NOCOPY VARCHAR2,
1878 x_return_msg OUT NOCOPY VARCHAR2)
1879 IS
1880 l_ta_intf_rec ego_trans_attrs_vers_intf%ROWTYPE;
1881 l_ta_intf_tbl TA_Intf_Tbl;
1882 l_return_status VARCHAR2(1);
1883 l_proc_name VARCHAR2(200):= 'Process_Trans_Attrs';
1884 BEGIN
1885 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Process_Trans_Attrs');
1886
1887 /* when coming from Public API*/
1888 IF G_FLOW_TYPE=G_EGO_MD_API THEN
1889 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'In to API Flow');
1890 l_ta_intf_tbl:= p_ta_intf_tbl;
1891
1892 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Calling Construct_TA');
1893 /* same as initialize and value_to_id */
1894 Construct_Trans_Attrs(p_api_version=>p_api_version,
1895 p_ta_intf_tbl => l_ta_intf_tbl,
1896 x_return_status => x_return_status,
1897 x_return_msg => x_return_msg);
1898
1899 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Calling Validate_TA');
1900 /* same as bulk validation*/
1901 Validate_Trans_Attrs(p_api_version => p_api_version,
1902 p_ta_intf_tbl => l_ta_intf_tbl,
1903 x_return_status => x_return_status,
1904 x_return_msg => x_return_msg);
1905
1906 p_ta_intf_tbl:= l_ta_intf_tbl;
1907 END IF;
1908 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Loop for Transact_TA record by record');
1909 -- loop to transact record by record
1910 FOR i IN p_ta_intf_tbl.first..p_ta_intf_tbl.last LOOP
1911 /* Assigning intf record to process*/
1912 l_ta_intf_rec:=p_ta_intf_tbl(i);
1913 -- changing icc_version_number to actual as passed
1914 IF l_ta_intf_rec.TRANSACTION_type=G_CREATE THEN
1915 l_ta_intf_rec.icc_version_number:=p_icc_version_number_act;
1916 END IF ;
1917
1918 Transact_Trans_Attrs(p_api_version =>p_api_version,
1919 p_ta_intf_rec =>l_ta_intf_rec,
1920 x_return_status =>x_return_status,
1921 x_return_msg =>x_return_msg);
1922 -- changing icc_version_number back to intf so no change in interface table
1923 IF l_ta_intf_rec.TRANSACTION_type=G_CREATE THEN
1924 l_ta_intf_rec.icc_version_number:=p_icc_version_number_intf;
1925 END IF ;
1926
1927 /*Assiging back to table after transact_ta */
1928 p_ta_intf_tbl(i):= l_ta_intf_rec;
1929 G_MESSAGE_TEXT:=x_return_msg;
1930
1931 IF (Nvl(x_return_status,G_RET_STS_SUCCESS)= G_RET_STS_ERROR)
1932 OR (p_ta_intf_tbl(i).process_status=G_ERROR_RECORD) THEN
1933 -- LOG the error in interface table
1934 G_TOKEN_TBL(1).Token_Name := 'Entity_Name';
1935 G_TOKEN_TBL(1).Token_Value := G_ENTITY_IDENTIFIER;
1936 G_TOKEN_TBL(2).Token_Name := 'Transaction_Type';
1937 G_TOKEN_TBL(2).Token_Value := p_ta_intf_tbl(i).transaction_type;
1938 G_TOKEN_TBL(3).Token_Name := 'Package_Name';
1939 G_TOKEN_TBL(3).Token_Value := 'EGO_TRANSACTION_ATTRS_PVT';
1940 G_TOKEN_TBL(4).Token_Name := 'Proc_Name';
1941 SELECT Decode(p_ta_intf_tbl(i).transaction_type,'CREATE','Create_Transaction_Attribute',
1942 'UPDATE','Update_Transaction_Attribute',
1943 'DELETE','Delete_Transaction_Attribute') INTO G_TOKEN_TBL(4).Token_Value
1944 FROM dual;
1945
1946 /* added p_addto_fnd_stack because in case of error ICC API will rollback the TA
1947 so messages will also get rollback. So if it get added to stack we can print and insert
1948 to interface_error table again*/
1949
1950 Error_Handler.Add_Error_Message
1951 (
1952 p_message_name => 'EGO_ENTITY_API_FAILED'
1953 ,p_application_id => G_APP_NAME
1954 ,p_message_type => G_RET_STS_ERROR
1955 ,p_entity_code => G_Entity_Identifier
1956 ,p_row_identifier => p_ta_intf_tbl(i).transaction_id
1957 ,p_table_name => G_Table_Name
1958 ,p_token_tbl => G_TOKEN_TBL
1959 ,p_addto_fnd_stack=> 'Y'
1960 );
1961
1962 G_TOKEN_TBL.DELETE;
1963
1964 RETURN;
1965 END IF ;
1966 END LOOP;
1967 /* if record successful then update the intf table with success(7)*/
1968 IF G_FLOW_TYPE=G_EGO_MD_INTF THEN
1969 Update_Intf_Trans_Attrs(p_ta_intf_tbl => p_ta_intf_tbl,
1970 x_return_status => l_return_status,
1971 x_return_msg => x_return_msg);
1972 END IF;
1973 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Process_Trans_Attrs');
1974 EXCEPTION
1975 WHEN OTHERS THEN
1976 x_return_status:= G_RET_STS_UNEXP_ERROR;
1977 x_return_msg:= G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
1978 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception when others Process_Trans_Attrs');
1979
1980 END Process_Trans_Attrs;
1981
1982 --=================Construct_Trans_Attrs ========
1983 /* This is same as initialize and value_to_id during interface flow */
1984 PROCEDURE Construct_Trans_Attrs(
1985 p_api_version IN NUMBER,
1986 p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
1987 x_return_status OUT NOCOPY VARCHAR2,
1988 x_return_msg OUT NOCOPY VARCHAR2)
1989 IS
1990 l_proc_name VARCHAR2(200):='Construct_Trans_Attrs';
1991 BEGIN
1992 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Construct_Trans_Attrs');
1993
1994 FOR i IN p_ta_intf_tbl.first..p_ta_intf_tbl.last LOOP
1995
1996 IF (p_ta_intf_tbl(i).process_status = G_PROCESS_RECORD) THEN
1997
1998
1999 /* setting G_APPLICATION_ID*/
2000 SELECT application_id INTO G_APPLICATION_ID
2001 FROM fnd_application
2002 WHERE application_short_name=G_APP_NAME;
2003
2004 /*Sets the transaction_id*/
2005 SELECT mtl_system_items_interface_s.NEXTVAL,Upper(p_ta_intf_tbl(i).transaction_type)
2006 INTO p_ta_intf_tbl(i).transaction_id,p_ta_intf_tbl(i).transaction_type
2007 FROM dual;
2008
2009 /* if not a valid transaction type*/
2010 IF p_ta_intf_tbl(i).transaction_type NOT IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC) THEN
2011 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Transaction Type '
2012 ||p_ta_intf_tbl(i).transaction_type
2013 ||'Is not Valid');
2014
2015 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2016 x_return_status := G_RET_STS_ERROR;
2017 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2018 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2019 p_row_identifier => P_ag_tbl(i).transaction_id,
2020 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2021 END IF;
2022
2023 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Icc_name to Icc_id conversion');
2024
2025 /* Getting ICC_id from icc_name */
2026 IF (p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
2027 AND p_ta_intf_tbl(i).item_catalog_group_id IS NULL
2028 AND p_ta_intf_tbl(i).item_catalog_group_name IS NOT NULL) THEN
2029
2030 BEGIN
2031 SELECt icc_kfv.item_catalog_group_id INTO p_ta_intf_tbl(i).item_catalog_group_id
2032 FROM mtl_item_catalog_groups_kfv icc_kfv
2033 WHERE Upper(icc_kfv.concatenated_segments) = Upper(p_ta_intf_tbl(i).item_catalog_group_name);
2034 EXCEPTION
2035 WHEN no_data_found THEN
2036 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ICC '
2037 ||p_ta_intf_tbl(i).item_catalog_group_name
2038 ||'does not exist in the system');
2039
2040 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2041 x_return_status := G_RET_STS_ERROR;
2042 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2043 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2044 p_row_identifier => P_ag_tbl(i).transaction_id,
2045 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2046 WHEN OTHERS THEN
2047 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ICC_NAME to ICC_ID Exception when others');
2048 x_return_status := G_RET_STS_UNEXP_ERROR;
2049 END;
2050 END IF;/*icc_name to icc_id */
2051
2052 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Value_set_id from Value_set_name');
2053 /* Getting value_set_id from value_set_name */
2054 IF (p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
2055 AND p_ta_intf_tbl(i).value_set_id IS NULL
2056 AND p_ta_intf_tbl(i).value_set_name IS NOT NULL) THEN
2057 BEGIN
2058 SELECT flex_value_set_id INTO p_ta_intf_tbl(i).value_set_id
2059 FROM fnd_flex_value_sets
2060 WHERE Upper(flex_value_set_name) = Upper(p_ta_intf_tbl(i).value_set_name);
2061 EXCEPTION
2062 WHEN no_data_found THEN
2063 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Value Set '
2064 ||p_ta_intf_tbl(i).value_set_name
2065 ||'does not exist in the system');
2066
2067 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2068 x_return_status := G_RET_STS_ERROR;
2069 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2070 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2071 p_row_identifier => P_ag_tbl(i).transaction_id,
2072 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2073 WHEN OTHERS THEN
2074 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Value Set Name to ID Exception when others');
2075 x_return_status := G_RET_STS_UNEXP_ERROR;
2076 END;
2077 END IF;/*value_set_name to value_set_id */
2078
2079 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Getting Attr_id from Attr_name');
2080 /* Getting Attr_id from Attr_name */
2081 IF (p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
2082 AND p_ta_intf_tbl(i).attr_id IS NULL
2083 AND p_ta_intf_tbl(i).attr_name IS NOT NULL) THEN
2084
2085 BEGIN
2086 SELECT attr_id INTO p_ta_intf_tbl(i).attr_id
2087 FROM EGO_TRANS_ATTR_VERS_B
2088 WHERE attr_id IN ( SELECT efdcue.attr_id
2089 FROM fnd_descr_flex_column_usages fdfcu,
2090 ego_fnd_df_col_usgs_ext efdcue
2091 WHERE fdfcu.application_id = efdcue.application_id
2092 AND fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
2093 AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
2094 AND fdfcu.application_column_name = efdcue.application_column_name
2095 AND fdfcu.application_id = G_APPLICATION_ID
2096 AND fdfcu.descriptive_flexfield_name = 'EGO_ITEM_TRANS_ATTR_GROUP'
2097 AND fdfcu.descriptive_flex_context_code IN (SELECT attr_group_name
2098 FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
2099 WHERE association_id in (SELECT association_id
2100 FROM EGO_OBJ_AG_ASSOCS_B
2101 WHERE classification_code=p_ta_intf_tbl(i).item_catalog_group_id)
2102 AND ATTR_GROUP_TYPE= 'EGO_ITEM_TRANS_ATTR_GROUP'
2103 )
2104 AND Upper(fdfcu.end_user_column_name) = Upper(p_ta_intf_tbl(i).attr_name)
2105 )
2106 AND item_catalog_group_id=p_ta_intf_tbl(i).item_catalog_group_id
2107 AND icc_version_number=0; -- we only allow update on draft;
2108
2109 EXCEPTION
2110 WHEN no_data_found THEN
2111 IF (p_ta_intf_tbl(i).transaction_type = G_SYNC) THEN
2112 p_ta_intf_tbl(i).transaction_type := G_CREATE;
2113 ELSE
2114 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Attribute Name'
2115 ||p_ta_intf_tbl(i).attr_name
2116 ||'does not exist in the system');
2117
2118 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2119 x_return_status := G_RET_STS_ERROR;
2120 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2121 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2122 p_row_identifier => P_ag_tbl(i).transaction_id,
2123 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2124 END IF;
2125 WHEN OTHERS THEN
2126 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Attribute Name to ID Exception when others');
2127 x_return_status := G_RET_STS_UNEXP_ERROR;
2128 END;
2129 END IF; /*attr_name to attr_id */
2130
2131 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Getting Attr_id from Attr_Display_name');
2132 /* Getting Attr_id from Attr_Display_name */
2133 IF (p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
2134 AND p_ta_intf_tbl(i).attr_id IS NULL
2135 AND p_ta_intf_tbl(i).attr_display_name IS NOT NULL
2136 AND p_ta_intf_tbl(i).attr_name IS NULL)/* extra condition becoz if attr_name is given then i could have*/
2137 THEN /* resolved sync and get the attr_id in previour attr_name to attr_id*/
2138
2139 BEGIN
2140 SELECT attr_id INTO p_ta_intf_tbl(i).attr_id
2141 FROM EGO_TRANS_ATTR_VERS_B
2142 WHERE Upper(attr_display_name) = Upper(p_ta_intf_tbl(i).attr_display_name)
2143 AND item_catalog_group_id=p_ta_intf_tbl(i).item_catalog_group_id
2144 AND icc_version_number=0;
2145
2146
2147 EXCEPTION
2148 WHEN no_data_found THEN
2149 IF (p_ta_intf_tbl(i).transaction_type = G_SYNC) THEN
2150 p_ta_intf_tbl(i).transaction_type := G_CREATE;
2151 ELSE
2152 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Attribute Display Name'
2153 ||p_ta_intf_tbl(i).attr_display_name
2154 ||'does not exist in the system');
2155
2156 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2157 x_return_status := G_RET_STS_ERROR;
2158 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2159 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2160 p_row_identifier => P_ag_tbl(i).transaction_id,
2161 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2162 END IF;
2163 WHEN OTHERS THEN
2164 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Attribute Name to ID Exception when others');
2165 x_return_status := G_RET_STS_UNEXP_ERROR;
2166 END;
2167 END IF; /*attr_display_name to attr_id */
2168
2169 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Getting associaton_id from icc_id and attr_id');
2170 /* Getting associaton_id from icc_id and attr_id, need this for update and delete*/
2171 IF (p_ta_intf_tbl(i).transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
2172 AND p_ta_intf_tbl(i).attr_id IS NOT NULL
2173 AND p_ta_intf_tbl(i).item_catalog_group_id IS NOT NULL
2174 AND p_ta_intf_tbl(i).association_id is NULL) THEN
2175
2176 BEGIN
2177 SELECT association_id INTO p_ta_intf_tbl(i).association_id
2178 FROM EGO_TRANS_ATTR_VERS_B
2179 WHERE attr_id= p_ta_intf_tbl(i).attr_id
2180 AND item_catalog_group_id= p_ta_intf_tbl(i).item_catalog_group_id
2181 AND icc_version_number=0;
2182
2183
2184 EXCEPTION
2185 WHEN no_data_found THEN
2186 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Not able to get association id frrom '
2187 ||p_ta_intf_tbl(i).attr_id ||' ' || p_ta_intf_tbl(i).item_catalog_group_id
2188 ||'Unexpected');
2189
2190 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2191 x_return_status := G_RET_STS_ERROR;
2192 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2193 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2194 p_row_identifier => P_ag_tbl(i).transaction_id,
2195 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2196 WHEN OTHERS THEN
2197 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Attribute Name to ID Exception when others');
2198 x_return_status := G_RET_STS_UNEXP_ERROR;
2199 END;
2200 END IF; /*association id from attr_id and icc_id */
2201 END IF;-- process status
2202 END LOOP;
2203 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Construct_Trans_Attrs');
2204
2205 EXCEPTION WHEN OTHERS THEN
2206 x_return_status:= G_RET_STS_UNEXP_ERROR;
2207 x_return_msg:= G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
2208 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception when others Construct_Trans_Attrs');
2209 END Construct_Trans_Attrs;
2210
2211 --=================Validate_Trans_Attrs ========
2212 /* This is same as Bulk Validation TA during interface flow */
2213 PROCEDURE Validate_Trans_Attrs(
2214 p_api_version IN NUMBER,
2215 p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
2216 x_return_status OUT NOCOPY VARCHAR2,
2217 x_return_msg OUT NOCOPY VARCHAR2)
2218 IS
2219 l_return_status VARCHAR2(1):=G_RET_STS_SUCCESS;
2220 l_msg_count NUMBER :=G_MISS_NUM;
2221 l_msg_data VARCHAR(2000):=G_MISS_CHAR;
2222 l_proc_name VARCHAR2(200):='Validate_Trans_Attrs';
2223
2224 l_id_exists NUMBER;
2225
2226 BEGIN
2227 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Validate_Trans_Attrs');
2228 x_return_status := G_RET_STS_SUCCESS;
2229 x_return_msg := G_MISS_CHAR;
2230
2231 FOR i IN p_ta_intf_tbl.first..p_ta_intf_tbl.last LOOP
2232 IF (p_ta_intf_tbl(i).process_status = G_PROCESS_RECORD
2233 AND p_ta_intf_tbl(i).TRANSACTION_ID IS NOT NULL
2234 ) THEN
2235
2236 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ICC_validations');
2237 /* ICC_validations*/
2238 IF p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC) THEN
2239 IF (p_ta_intf_tbl(i).item_catalog_group_id IS NULL
2240 AND p_ta_intf_tbl(i).item_catalog_group_name IS NULL) THEN /*if both ICC_ID and ICC_NAME is NULL*/
2241 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ERR : ICC Id and ICC_NAME both NULL ');
2242 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2243 x_return_status := G_RET_STS_ERROR;
2244 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2245 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2246 p_row_identifier => P_ag_tbl(i).transaction_id,
2247 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2248
2249 ELSIF (p_ta_intf_tbl(i).item_catalog_group_id IS NOT NULL) THEN /* if ICC_ID provided doesn't exists*/
2250
2251 BEGIN
2252 SELECT item_catalog_group_id INTO l_id_exists
2253 FROM mtl_item_catalog_groups micg
2254 WHERE micg.ITEM_CATALOG_GROUP_ID=p_ta_intf_tbl(i).item_catalog_group_id;
2255
2256 EXCEPTION
2257 WHEN no_data_found THEN
2258 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ICC '
2259 ||p_ta_intf_tbl(i).item_catalog_group_id
2260 ||'does not exist in the system');
2261
2262 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2263 x_return_status := G_RET_STS_ERROR;
2264 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2265 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2266 p_row_identifier => P_ag_tbl(i).transaction_id,
2267 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2268 WHEN OTHERS THEN
2269 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ICC_ID provided Exception when others');
2270 x_return_status := G_RET_STS_UNEXP_ERROR;
2271 END;
2272 END IF;
2273 END IF;-- If Transaction_type in ALL
2274
2275 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Value_set_Validations ');
2276 /* Value_set Validations*/
2277 IF p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_SYNC) THEN
2278 /* if given value_set_id not exists */
2279 IF (p_ta_intf_tbl(i).value_set_id IS NOT NULL) THEN
2280 BEGIN
2281 SELECT flex_value_set_id INTO l_id_exists
2282 FROM fnd_flex_value_sets ffvs
2283 WHERE ffvs.flex_value_set_id = p_ta_intf_tbl(i).value_set_id ;
2284
2285 EXCEPTION
2286 WHEN no_data_found THEN
2287 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Value Set Id '
2288 ||p_ta_intf_tbl(i).value_set_id
2289 ||'does not exist in the system');
2290
2291 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2292 x_return_status := G_RET_STS_ERROR;
2293 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2294 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2295 p_row_identifier => P_ag_tbl(i).transaction_id,
2296 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2297 WHEN OTHERS THEN
2298 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'value_Set_id provided Exception when others');
2299 x_return_status := G_RET_STS_UNEXP_ERROR;
2300 END;
2301 END IF;
2302 END IF;-- If Transaction_type in except delete
2303
2304 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'attr_id Validations and conversion of sync to create/update ');
2305 /* attr_id Validations*/
2306 IF p_ta_intf_tbl(i).transaction_type IN (G_UPDATE,G_DELETE,G_SYNC) THEN
2307 /* if given attr_id not exists */
2308 IF (p_ta_intf_tbl(i).attr_id IS NOT NULL) THEN
2309
2310 BEGIN
2311 SELECT attr_id INTO l_id_exists
2312 FROM EGO_TRANS_ATTR_VERS_B ETAVB
2313 WHERE ETAVB.attr_id = p_ta_intf_tbl(i).attr_id
2314 AND item_catalog_group_id=p_ta_intf_tbl(i).item_catalog_group_id
2315 AND icc_version_number=0;
2316
2317 IF (p_ta_intf_tbl(i).transaction_type = G_SYNC) THEN
2318 p_ta_intf_tbl(i).transaction_type := G_UPDATE;
2319 END IF;
2320 /* assigning defaults required for update*/
2321 IF p_ta_intf_tbl(i).attr_display_name IS NOT NULL THEN
2322 BEGIN
2323 SELECT attr_name INTO p_ta_intf_tbl(i).attr_name
2324 FROM ego_attrs_v EAV
2325 WHERE EAV.attr_id= p_ta_intf_tbl(i).attr_id;
2326 END ;
2327 END IF;
2328
2329 IF p_ta_intf_tbl(i).attr_name IS NOT NULL THEN
2330 BEGIN
2331 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'assining rest of defaults');
2332 SELECT nvl(p_ta_intf_tbl(i).uom_class,b.uom_class),nvl(p_ta_intf_tbl(i).default_value,b.default_value),
2333 nvl(p_ta_intf_tbl(i).rejected_value,b.rejected_value),nvl(p_ta_intf_tbl(i).required_flag,b.required_flag),
2334 nvl(p_ta_intf_tbl(i).readonly_flag,b.readonly_flag),nvl(p_ta_intf_tbl(i).hidden_flag,b.hidden_flag),
2335 nvl(p_ta_intf_tbl(i).searchable_flag,b.searchable_flag), nvl(p_ta_intf_tbl(i).check_eligibility,b.check_eligibility),
2336 nvl(p_ta_intf_tbl(i).value_set_id,b.value_set_id), nvl(p_ta_intf_tbl(i).attr_display_name,b.attr_display_name),b.SEQUENCE
2337 INTO
2338 p_ta_intf_tbl(i).uom_class,p_ta_intf_tbl(i).default_value,p_ta_intf_tbl(i).rejected_value,
2339 p_ta_intf_tbl(i).required_flag,p_ta_intf_tbl(i).readonly_flag,p_ta_intf_tbl(i).hidden_flag,
2340 p_ta_intf_tbl(i).searchable_flag, p_ta_intf_tbl(i).check_eligibility,
2341 p_ta_intf_tbl(i).value_set_id,p_ta_intf_tbl(i).attr_display_name,p_ta_intf_tbl(i).SEQUENCE
2342 FROM EGO_TRANS_ATTR_VERS_B b
2343 WHERE b.item_catalog_group_id=p_ta_intf_tbl(i).item_catalog_group_id
2344 AND b.icc_version_number=0
2345 AND b.attr_id= p_ta_intf_tbl(i).attr_id;
2346
2347 END ;
2348 END IF;
2349
2350 EXCEPTION
2351 WHEN no_data_found THEN
2352 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Attr Id provided '
2353 ||p_ta_intf_tbl(i).attr_id
2354 ||'does not exist in the system');
2355
2356 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2357 x_return_status := G_RET_STS_ERROR;
2358 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2359 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2360 p_row_identifier => P_ag_tbl(i).transaction_id,
2361 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2362 WHEN OTHERS THEN
2363 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'attr_id provided Exception when others');
2364 x_return_status := G_RET_STS_UNEXP_ERROR;
2365
2366 END;
2367
2368 ELSIF (p_ta_intf_tbl(i).attr_id IS NULL AND p_ta_intf_tbl(i).attr_name IS NULL AND p_ta_intf_tbl(i).attr_display_name IS NULL ) THEN
2369 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ALL Attr_id, Attr_name and Attr_Display Name cannot be NULL for UPDATE,DEL and SYNC');
2370 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2371 x_return_status := G_RET_STS_ERROR;
2372 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2373 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2374 p_row_identifier => P_ag_tbl(i).transaction_id,
2375 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2376
2377 END IF; -- if attr_id is not NULL
2378
2379 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'If association id provided is not valid');
2380 /* if association id provided is not valid*/
2381 IF ( p_ta_intf_tbl(i).association_id IS NOT NULL AND p_ta_intf_tbl(i).attr_id IS NOT NULL AND p_ta_intf_tbl(i).item_catalog_group_id IS NOT NULL) THEN
2382
2383 BEGIN
2384 SELECT association_id INTO l_id_exists
2385 FROM EGO_TRANS_ATTR_VERS_B ETAVB
2386 WHERE ETAVB.association_id = p_ta_intf_tbl(i).association_id
2387 AND item_catalog_group_id= p_ta_intf_tbl(i).item_catalog_group_id
2388 AND icc_version_number=0
2389 AND attr_id=p_ta_intf_tbl(i).attr_id;
2390
2391 EXCEPTION
2392 WHEN no_data_found THEN
2393 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Association provided '
2394 ||p_ta_intf_tbl(i).attr_id
2395 ||'does not exist in the system');
2396
2397 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2398 x_return_status := G_RET_STS_ERROR;
2399 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2400 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2401 p_row_identifier => P_ag_tbl(i).transaction_id,
2402 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2403 WHEN OTHERS THEN
2404 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'value_Set_id provided Exception when others');
2405 x_return_status := G_RET_STS_UNEXP_ERROR;
2406
2407 END;
2408 END IF;
2409 END IF;-- If transaction_type
2410
2411 /* ============ Specific Validations ======== */
2412 /* CREATE */
2413 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Create Specific Validations');
2414 IF p_ta_intf_tbl(i).transaction_type IN (G_CREATE) THEN
2415
2416 IF (p_ta_intf_tbl(i).Association_id IS NOT NULL
2417 OR p_ta_intf_tbl(i).Attr_id IS NOT NULL ) THEN /*if association_id or attr_id is NOT NULL*/
2418 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ERR : Association_Is and Attr_id shud be NULL for CREATE');
2419 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2420 x_return_status := G_RET_STS_ERROR;
2421 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2422 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2423 p_row_identifier => P_ag_tbl(i).transaction_id,
2424 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2425
2426
2427 ELSIF ((p_ta_intf_tbl(i).attr_name IS NULL)
2428 OR (p_ta_intf_tbl(i).attr_display_name IS NULL)
2429 OR (p_ta_intf_tbl(i).SEQUENCE IS NULL)) THEN /* if ICC_ID provided doesn't exists*/
2430 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Attr_Name and ATT_DISPLAY_NAM and Seq shud not be NULL for CREATE');
2431 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2432 x_return_status := G_RET_STS_ERROR;
2433 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2434 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2435 p_row_identifier => P_ag_tbl(i).transaction_id,
2436 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2437
2438 ELSIF (p_ta_intf_tbl(i).icc_version_number IS NULL) THEN /*If ICC version is NULL while create*/
2439 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ERR : ICC_VERSION_NUMBER cannot be NULL while CREATE');
2440 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2441 x_return_status := G_RET_STS_ERROR;
2442 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2443 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2444 p_row_identifier => P_ag_tbl(i).transaction_id,
2445 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2446
2447 END IF;
2448
2449 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Defaulting of data type and dispaly_name');
2450 /* Defaulting data type to 'C' if NULL */
2451 IF (p_ta_intf_tbl(i).data_type IS NULL) THEN
2452 p_ta_intf_tbl(i).data_type:='C';
2453 END IF ;
2454
2455 IF (Nvl(p_ta_intf_tbl(i).display_flag,'ZZZ') <> 'T') THEN
2456 p_ta_intf_tbl(i).display_flag:='T';
2457 END IF ;
2458 END IF;-- If Transaction_type CREATE
2459
2460 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Icc_Version_Number greater than zero not allowed while UPDATE and DELETE');
2461 /* Both UPDATE and DELETE NOT SYNC */
2462 IF p_ta_intf_tbl(i).transaction_type IN (G_UPDATE, G_DELETE) THEN
2463
2464 IF (p_ta_intf_tbl(i).icc_version_number>0) then /*Icc_Version_Number greater than zero not allowed while UPDATE and DELETE*/
2465
2466 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Icc_Version_Number greater than zero not allowed while UPDATE and DELETE');
2467 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2468 x_return_status := G_RET_STS_ERROR;
2469 /*error_handler.Add_error_message(p_message_name => 'EGO_ICC_INVALID',p_application_id => 'EGO',
2470 p_token_tbl => g_token_table,p_message_type => g_ret_sts_error,
2471 p_row_identifier => P_ag_tbl(i).transaction_id,
2472 p_entity_code => g_entity_ag,p_table_name => g_entity_ag_tab);*/
2473 END IF;
2474 END IF ;
2475
2476 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Association_id and attr_id should not be null for DELETE');
2477 /* DELETE*/
2478 IF p_ta_intf_tbl(i).transaction_type IN (G_DELETE) THEN
2479
2480 IF ((p_ta_intf_tbl(i).association_id IS NULL)
2481 OR (p_ta_intf_tbl(i).attr_id IS NULL)) THEN /*Association_id and attr_id should not be null for DELETE */
2482
2483 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ERR : Association_id and attr_id should not be null for DELETE');
2484 p_ta_intf_tbl(i).process_status := G_ERROR_RECORD;
2485 x_return_status := G_RET_STS_ERROR;
2486 END IF;
2487 END IF ;
2488
2489 END IF;
2490 END LOOP;
2491 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Validate_Trans_Attrs');
2492 EXCEPTION
2493 WHEN OTHERS THEN
2494 x_return_status:= G_RET_STS_UNEXP_ERROR;
2495 x_return_msg := G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
2496 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception when others Validate_Trans_Attrs');
2497 END Validate_Trans_Attrs;
2498
2499 --
2500 -- Bug 9980051. Added few supporting sub functions.
2501 -- For MIB we need to call create_inherited_attribute
2502 -- if user is trying to add inherited attribute.
2503 -- sreharih. Tue Apr 19 11:06:55 PDT 2011
2504 --
2505
2506 --
2507 -- Get start active date for given ICC and version.
2508 --
2509 FUNCTION get_start_active_date (p_item_catalog_group_id IN NUMBER,
2510 p_version_seq_id IN NUMBER) RETURN DATE IS
2511
2512 CURSOR c IS
2513 SELECT nvl(vers.start_active_date,SYSDATE)
2514 FROM ego_mtl_catalog_grp_vers_b vers
2515 WHERE vers.item_catalog_group_id = p_item_catalog_group_id
2516 AND vers.version_seq_id = p_version_seq_id;
2517
2518 l_ret ego_mtl_catalog_grp_vers_b.start_active_date%TYPE;
2519
2520 BEGIN
2521 OPEN c;
2522 FETCH c INTO l_ret;
2523 CLOSE c;
2524
2525 RETURN l_ret;
2526 END get_start_active_date;
2527
2528 --
2529 -- Check whether given ICC has parents.
2530 --
2531 FUNCTION has_parent (p_item_catalog_group_id IN NUMBER
2532 ) RETURN BOOLEAN IS
2533
2534 CURSOR c IS
2535 SELECT cat.parent_catalog_group_id
2536 FROM mtl_item_catalog_groups_b cat
2537 WHERE cat.item_catalog_group_id = p_item_catalog_group_id;
2538
2539 l_ret mtl_item_catalog_groups_b.parent_catalog_group_id%TYPE;
2540
2541 BEGIN
2542 OPEN c;
2543 FETCH c INTO l_ret;
2544 CLOSE c;
2545
2546 IF l_ret IS NOT NULL THEN
2547 RETURN true;
2548 ELSE
2549 RETURN false;
2550 END IF;
2551
2552 END has_parent;
2553
2554
2555 --
2556 -- Get latest version for the ICC effective after given active date
2557 --
2558 FUNCTION get_latest_version (
2559 p_item_catalog_group_id IN NUMBER,
2560 p_active_date IN DATE) RETURN NUMBER IS
2561
2562 CURSOR c IS
2563 SELECT version_seq_id
2564 FROM (SELECT version_seq_id
2565 FROM ego_mtl_catalog_grp_vers_b cat_vers
2566 WHERE cat_vers.item_catalog_group_id = p_item_catalog_group_id
2567 AND cat_vers.start_active_date <= p_active_date
2568 AND (cat_vers.end_active_date IS NULL
2569 OR cat_vers.end_active_date >= p_active_date)
2570 ORDER BY version_seq_id DESC)
2571 WHERE rownum = 1;
2572
2573 l_ret ego_mtl_catalog_grp_vers_b.version_seq_id%TYPE;
2574 BEGIN
2575 OPEN c;
2576 FETCH c INTO l_ret;
2577 CLOSE c;
2578
2579 RETURN l_ret;
2580
2581 END get_latest_version;
2582
2583 --
2584 -- Check whether given attribute is inherited from parent or not.
2585 -- If inherited it will also return the attr_id and association id.
2586 --
2587 FUNCTION is_inherited_attribute (p_item_catalog_group_id IN NUMBER,
2588 p_version_seq_id IN NUMBER,
2589 p_attr_name IN VARCHAR2,
2590 x_attr_id OUT NOCOPY NUMBER,
2591 x_association_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2592
2593 l_start_date ego_mtl_catalog_grp_vers_b.start_active_date%TYPE;
2594 l_latest_vers ego_mtl_catalog_grp_vers_b.version_seq_id%TYPE;
2595 l_attr_id ego_trans_attr_vers_b.attr_id%TYPE;
2596 l_association_id ego_trans_attr_vers_b.association_id%TYPE;
2597 l_proc_name CONSTANT VARCHAR2(30) := 'IS_INHERITED_ATTRIBUTE';
2598
2599 CURSOR c_parents IS
2600 SELECT heir.item_catalog_group_id,
2601 heir.lev
2602 FROM (SELECT item_catalog_group_id ,
2603 level lev
2604 FROM mtl_item_catalog_groups_b micg
2605 START WITH item_catalog_group_id = p_item_catalog_group_id
2606 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
2607 ) heir
2608 WHERE heir.lev > 1 -- only parents
2609 ORDER BY heir.lev; -- if same attribute exists in more than one parent, pick it from the least level
2610
2611 CURSOR c(c_item_catalog_group_id IN NUMBER,
2612 c_latest_ver IN NUMBER) IS
2613 SELECT ta.attr_id,
2614 ta.association_id
2615 FROM ego_trans_attr_vers_b ta,
2616 ego_fnd_df_col_usgs_ext attr ,
2617 fnd_descr_flex_column_usages flex_col
2618 WHERE 1=1
2619 AND ta.metadata_level = 'ICC'
2620 AND ta.item_catalog_group_id = c_item_catalog_group_id
2621 AND ta.icc_version_number = c_latest_ver
2622 AND ta.attr_id = attr.attr_id
2623 AND attr.descriptive_flex_context_code = flex_col.descriptive_flex_context_code
2624 AND attr.descriptive_flexfield_name = flex_col.descriptive_flexfield_name
2625 AND attr.application_column_name = flex_col.application_column_name
2626 AND flex_col.descriptive_flexfield_name = 'EGO_ITEM_TRANS_ATTR_GROUP'
2627 AND flex_col.end_user_column_name = p_attr_name;
2628
2629
2630 BEGIN
2631 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Entering method with params ' ||
2632 ' icc = ' || p_item_catalog_group_id ||
2633 ' attrbute name = ' || p_attr_name ||
2634 ' version = ' || p_version_seq_id);
2635
2636 l_start_date := get_start_active_date(p_item_catalog_group_id => p_item_catalog_group_id,
2637 p_version_seq_id => p_version_seq_id);
2638
2639 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Start date = ' || l_start_date);
2640
2641
2642 FOR c_rec IN c_parents LOOP
2643 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Checking for parent ' || c_rec.item_catalog_group_id || 'level ' || c_rec.lev);
2644
2645 l_latest_vers := get_latest_version(p_item_catalog_group_id => c_rec.item_catalog_group_id,
2646 p_active_date => l_start_date);
2647
2648 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Checking for parent ' || c_rec.item_catalog_group_id || 'latest version '|| l_latest_vers );
2649
2650 OPEN c( c_rec.item_catalog_group_id, l_latest_vers); -- check only on the latest active version of the parent
2651 FETCH c INTO l_attr_id, l_association_id;
2652 CLOSE c;
2653
2654 IF l_attr_id IS NOT NULL THEN
2655 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Attribute ' || l_attr_id || ' exists in parent so returning true' );
2656 x_attr_id := l_attr_id;
2657 x_association_id := l_association_id;
2658 RETURN true;
2659 END IF;
2660
2661 END LOOP;
2662
2663 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Returning false' );
2664 RETURN false;
2665
2666 END is_inherited_attribute;
2667
2668 --=================Transact_Trans_Attrs ========
2669
2670 PROCEDURE Transact_Trans_Attrs(
2671 p_api_version IN NUMBER,
2672 p_ta_intf_rec IN OUT NOCOPY ego_trans_attrs_vers_intf%ROWTYPE,
2673 x_return_status OUT NOCOPY VARCHAR2,
2674 x_return_msg OUT NOCOPY VARCHAR2)
2675 IS
2676 l_ego_ta_tbl EGO_TRAN_ATTR_TBL;
2677 l_ta_intf_tbl TA_Intf_Tbl;
2678
2679 l_return_status VARCHAR2(1):=G_RET_STS_SUCCESS;
2680 l_msg_count NUMBER :=G_MISS_NUM;
2681 l_msg_data VARCHAR(2000):=G_MISS_CHAR;
2682
2683 l_proc_name VARCHAR2(200):='Transact_Trans_Attrs';
2684
2685 l_is_child_icc NUMBER;
2686 l_is_ta_there NUMBER;
2687 l_inherited BOOLEAN; -- bug 9980051
2688 l_attr_id ego_trans_attr_vers_b.attr_id%TYPE; -- bug 9980051
2689 l_association_id ego_trans_attr_vers_b.association_id%TYPE; -- bug 9980051
2690 e_ta_int_name_exist EXCEPTION;
2691 e_ta_disp_name_exist EXCEPTION;
2692 e_ta_sequence_exist EXCEPTION;
2693
2694 BEGIN
2695 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Transact_Trans_Attrs');
2696 x_return_status := G_RET_STS_SUCCESS;
2697 x_return_msg := G_MISS_CHAR ;
2698
2699 -----================ /* MAIN LOGIC FOR TRANSACT */ =============------------
2700
2701 IF p_ta_intf_rec.process_status=G_PROCESS_RECORD THEN
2702
2703 IF p_ta_intf_rec.transaction_type=G_CREATE THEN
2704 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering CREATE');
2705
2706
2707 /* for passing only one record at a time to convert*/
2708 l_ta_intf_tbl(1):= p_ta_intf_rec;
2709
2710 /*calling proc to convert intf collection to original which calls create api*/
2711 convert_intf_rec_to_api_rec(p_ta_intf_tbl=>l_ta_intf_tbl,
2712 x_ego_ta_tbl=>l_ego_ta_tbl);
2713
2714
2715
2716 --
2717 -- Bug 9980051. If the user passed attribute is an inherited
2718 -- attribute then call create_inherited_attr API
2719 -- sreharih. Tue Apr 19 11:06:55 PDT 2011
2720 --
2721
2722 l_inherited := false;
2723 IF has_parent(p_item_catalog_group_id => p_ta_intf_rec.item_catalog_group_id) THEN
2724 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' The ICC ' || p_ta_intf_rec.item_catalog_group_id
2725 ||'has parents so check for inheritance ...');
2726
2727 IF is_inherited_attribute(p_item_catalog_group_id => p_ta_intf_rec.item_catalog_group_id,
2728 p_attr_name => p_ta_intf_rec.attr_name,
2729 p_version_seq_id => p_ta_intf_rec.icc_version_number,
2730 x_attr_id => l_attr_id,
2731 x_association_id => l_association_id) THEN
2732
2733 -- inherited just check for display name
2734 /* Check if att_disp_name already exist*/
2735 IF ( Check_TA_IS_INVALID (p_item_cat_group_id => p_ta_intf_rec.item_catalog_group_id,
2736 p_icc_version_number => p_ta_intf_rec.icc_version_number,
2737 p_attr_id => p_ta_intf_rec.attr_id,
2738 p_attr_disp_name => p_ta_intf_rec.attr_display_name) ) THEN
2739 p_ta_intf_rec.process_status:=G_ERROR_RECORD;
2740 RAISE e_ta_disp_name_exist;
2741 END IF;
2742
2743 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Attribute ' || p_ta_intf_rec.attr_name || ' is inherited so call Create_inherit API' );
2744 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Attr id ' || l_attr_id || ' association id ' || l_association_id );
2745 l_ego_ta_tbl(1).attrid := l_attr_id;
2746 l_ego_ta_tbl(1).associationid := l_association_id;
2747 ego_transaction_attrs_pvt.Create_Inherited_Trans_Attr (
2748 p_api_version => p_api_version,
2749 p_tran_attrs_tbl => l_ego_ta_tbl,
2750 x_return_status => l_return_status,
2751 x_msg_count => l_msg_count,
2752 x_msg_data => l_msg_data);
2753 l_inherited := true;
2754 END IF;
2755 END IF;
2756
2757 --
2758 -- Bug 9980051. If the user passed attribute is NOT an inherited
2759 -- attribute then call normal create_attr API
2760 -- sreharih. Tue Apr 19 11:06:55 PDT 2011
2761 --
2762
2763
2764 IF NOT l_inherited THEN
2765
2766 -- not inherited so check for all.
2767
2768 /* Check if att_int_name already exist*/
2769 IF ( Check_TA_IS_INVALID (p_item_cat_group_id => p_ta_intf_rec.item_catalog_group_id,
2770 p_icc_version_number => p_ta_intf_rec.icc_version_number,
2771 p_attr_id => p_ta_intf_rec.attr_id,
2772 p_attr_name => p_ta_intf_rec.attr_name) ) THEN
2773 p_ta_intf_rec.process_status:=G_ERROR_RECORD;
2774 RAISE e_ta_int_name_exist;
2775 END IF;
2776
2777 /* Check if att_disp_name already exist*/
2778 IF ( Check_TA_IS_INVALID (p_item_cat_group_id => p_ta_intf_rec.item_catalog_group_id,
2779 p_icc_version_number => p_ta_intf_rec.icc_version_number,
2780 p_attr_id => p_ta_intf_rec.attr_id,
2781 p_attr_disp_name => p_ta_intf_rec.attr_display_name) ) THEN
2782 p_ta_intf_rec.process_status:=G_ERROR_RECORD;
2783 RAISE e_ta_disp_name_exist;
2784 END IF;
2785
2786 /* Check if sequence already exist*/
2787 IF ( Check_TA_IS_INVALID (p_item_cat_group_id => p_ta_intf_rec.item_catalog_group_id,
2788 p_icc_version_number => p_ta_intf_rec.icc_version_number,
2789 p_attr_id => p_ta_intf_rec.attr_id,
2790 p_attr_sequence => p_ta_intf_rec.sequence) ) THEN
2791 p_ta_intf_rec.process_status:=G_ERROR_RECORD;
2792 RAISE e_ta_sequence_exist;
2793 END IF;
2794
2795
2796 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Call create for ICC: ' || p_ta_intf_rec.item_catalog_group_id ||
2797 ' attribute ' || p_ta_intf_rec.attr_id );
2798
2799 /* calling API to Create TA */
2800 ego_transaction_attrs_pvt.Create_Transaction_Attribute (
2801 p_api_version => p_api_version,
2802 p_tran_attrs_tbl => l_ego_ta_tbl,
2803 x_return_status => l_return_status,
2804 x_msg_count => l_msg_count,
2805 x_msg_data => l_msg_data);
2806 END IF;
2807
2808
2809 /* Check if x_return_status is sucess or not otherwise set process_status
2810 of l_ta_intf_rec with 3 and log error message*/
2811 IF (Nvl(l_return_status,G_RET_STS_SUCCESS) IN (G_RET_STS_ERROR,G_RET_STS_UNEXP_ERROR) )THEN
2812 p_ta_intf_rec.process_status:=G_ERROR_RECORD;
2813 x_return_status:= G_RET_STS_ERROR;
2814 x_return_msg:= l_msg_data;
2815 ELSE
2816 p_ta_intf_rec.process_status:=G_SUCCESS_RECORD;
2817 x_return_status:= G_RET_STS_SUCCESS;
2818 END IF;
2819
2820 ELSIF p_ta_intf_rec.transaction_type=G_UPDATE THEN
2821 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering UPDATE');
2822 /* for passing only one record at a time to convert*/
2823 l_ta_intf_tbl(1):= p_ta_intf_rec;
2824
2825 /*calling proc to convert intf collection to original which calls create api*/
2826 convert_intf_rec_to_api_rec(p_ta_intf_tbl=>l_ta_intf_tbl,
2827 x_ego_ta_tbl=>l_ego_ta_tbl);
2828
2829 BEGIN /* Checking if ICC is child ICC */
2830 SELECT 1 INTO l_is_child_icc
2831 FROM mtl_item_catalog_groups
2832 WHERE ITEM_CATALOG_GROUP_ID=p_ta_intf_rec.item_catalog_group_id
2833 AND PARENT_CATALOG_GROUP_ID IS NOT NULL;
2834
2835 EXCEPTION
2836 WHEN No_Data_Found THEN
2837 l_is_child_icc:=0;
2838 END;
2839
2840 IF l_is_child_icc=1 THEN
2841
2842 BEGIN /* Checking is TA thre for same icc, iccversion*/
2843 SELECT 1 INTO l_is_ta_there
2844 FROM EGO_TRANS_ATTR_VERS_B
2845 WHERE item_catalog_group_id=p_ta_intf_rec.item_catalog_group_id
2846 AND icc_version_number=p_ta_intf_rec.icc_version_number
2847 AND attr_id= p_ta_intf_rec.attr_id;
2848
2849 EXCEPTION
2850 WHEN No_Data_Found THEN
2851 l_is_ta_there:=0;
2852 END ;
2853
2854 IF l_is_ta_there=1 THEN /* if there then usual update */
2855
2856 ego_transaction_attrs_pvt.Update_Transaction_Attribute (
2857 p_api_version => p_api_version,
2858 p_tran_attrs_tbl => l_ego_ta_tbl,
2859 x_return_status => l_return_status,
2860 x_msg_count => l_msg_count,
2861 x_msg_data => l_msg_data);
2862 ELSE /* calling Create_Inherited_Trans_Attr*/
2863
2864 ego_transaction_attrs_pvt.Create_Inherited_Trans_Attr (
2865 p_api_version => p_api_version,
2866 p_tran_attrs_tbl => l_ego_ta_tbl,
2867 x_return_status => l_return_status,
2868 x_msg_count => l_msg_count,
2869 x_msg_data => l_msg_data);
2870 END IF ; --l_is_ta_there=1
2871
2872 ELSE
2873 /* calling API to Update TA */
2874 ego_transaction_attrs_pvt.Update_Transaction_Attribute (
2875 p_api_version => p_api_version,
2876 p_tran_attrs_tbl => l_ego_ta_tbl,
2877 x_return_status => l_return_status,
2878 x_msg_count => l_msg_count,
2879 x_msg_data => l_msg_data);
2880 END IF; --l_is_child_icc=1
2881
2882 /* Check if x_return_status is sucess or not otherwise set process_status
2883 of l_ta_intf_rec with 3 and log error message*/
2884 IF (Nvl(l_return_status,G_RET_STS_SUCCESS) IN (G_RET_STS_ERROR,G_RET_STS_UNEXP_ERROR) )THEN
2885 p_ta_intf_rec.process_status:=G_ERROR_RECORD;
2886 x_return_status:= G_RET_STS_ERROR;
2887 x_return_msg:= l_msg_data;
2888 ELSE
2889 p_ta_intf_rec.process_status:=G_SUCCESS_RECORD;
2890 x_return_status:= G_RET_STS_SUCCESS;
2891 END IF;
2892
2893 ELSIF p_ta_intf_rec.transaction_type=G_DELETE THEN
2894
2895 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering DELETE');
2896
2897 /* for passing only one record at a time to convert*/
2898 l_ta_intf_tbl(1):= p_ta_intf_rec;
2899
2900 /*calling proc to convert intf collection to original which calls create api*/
2901 convert_intf_rec_to_api_rec(p_ta_intf_tbl=>l_ta_intf_tbl,
2902 x_ego_ta_tbl=>l_ego_ta_tbl);
2903
2904 /* calling API to Delete TA */
2905 ego_transaction_attrs_pvt.Delete_Transaction_Attribute (
2906 p_api_version => p_api_version,
2907 p_association_id => l_ego_ta_tbl(1).associationid,
2908 p_attr_id => l_ego_ta_tbl(1).attrid,
2909 --p_tran_attrs_tbl => l_ego_ta_tbl,
2910 x_return_status => l_return_status,
2911 x_msg_count => l_msg_count,
2912 x_msg_data => l_msg_data);
2913 /* Check if x_return_status is sucess or not otherwise set process_status
2914 of l_ta_intf_rec with 3 and log error message*/
2915 IF (Nvl(l_return_status,G_RET_STS_SUCCESS) IN (G_RET_STS_ERROR,G_RET_STS_UNEXP_ERROR) )THEN
2916 p_ta_intf_rec.process_status:=G_ERROR_RECORD;
2917 x_return_status:= G_RET_STS_ERROR;
2918 x_return_msg:= l_msg_data;
2919 ELSE
2920 p_ta_intf_rec.process_status:=G_SUCCESS_RECORD;
2921 x_return_status:= G_RET_STS_SUCCESS;
2922 END IF;
2923 END IF ; -- transaction type checking;
2924 END IF; --l_ego_ta_rec.process_status=G_PROCESS_RECORD
2925
2926 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Transact_Trans_Attrs');
2927 EXCEPTION
2928 WHEN e_ta_int_name_exist THEN
2929 x_return_status := G_RET_STS_ERROR;
2930 Error_Handler.Add_Error_Message
2931 (
2932 p_message_name => 'EGO_EF_INTERNAL_NAME_UNIQUE'
2933 ,p_application_id => G_APP_NAME
2934 ,p_message_type => G_RET_STS_ERROR
2935 ,p_entity_code => G_Entity_Identifier
2936 ,p_row_identifier => p_ta_intf_rec.transaction_id
2937 ,p_table_name => G_Table_Name
2938 --,p_token_tbl => G_TOKEN_TBL
2939 ,p_addto_fnd_stack=> 'Y'
2940 );
2941
2942 WHEN e_ta_disp_name_exist THEN
2943 x_return_status := G_RET_STS_ERROR;
2944 Error_Handler.Add_Error_Message
2945 (
2946 p_message_name => 'EGO_TA_DISPLAY_NAME_UNIQUE'
2947 ,p_application_id => G_APP_NAME
2948 ,p_message_type => G_RET_STS_ERROR
2949 ,p_entity_code => G_Entity_Identifier
2950 ,p_row_identifier => p_ta_intf_rec.transaction_id
2951 ,p_table_name => G_Table_Name
2952 --,p_token_tbl => G_TOKEN_TBL
2953 ,p_addto_fnd_stack=> 'Y'
2954 );
2955
2956 WHEN e_ta_sequence_exist THEN
2957 x_return_status := G_RET_STS_ERROR;
2958 Error_Handler.Add_Error_Message
2959 (
2960 p_message_name => 'EGO_EF_CR_ATTR_DUP_SEQ_ERR'
2961 ,p_application_id => G_APP_NAME
2962 ,p_message_type => G_RET_STS_ERROR
2963 ,p_entity_code => G_Entity_Identifier
2964 ,p_row_identifier => p_ta_intf_rec.transaction_id
2965 ,p_table_name => G_Table_Name
2966 --,p_token_tbl => G_TOKEN_TBL
2967 ,p_addto_fnd_stack=> 'Y'
2968 );
2969 WHEN OTHERS THEN
2970 x_return_status:= G_RET_STS_UNEXP_ERROR;
2971 x_return_msg := G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
2972 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Transact_Trans_Attrs');
2973 END Transact_Trans_Attrs;
2974
2975 PROCEDURE Update_Intf_Trans_Attrs(
2976 p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
2977 x_return_status OUT NOCOPY VARCHAR2,
2978 x_return_msg OUT NOCOPY VARCHAR2)
2979 IS
2980 l_proc_name VARCHAR2(200):='Update_Intf_Trans_Attrs';
2981 trans_id dbms_sql.number_table; --bug 9701271
2982 BEGIN
2983 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Update_Intf_Trans_Attrs');
2984
2985 -- bug 9701271
2986 FOR i IN p_ta_intf_tbl.FIRST..p_ta_intf_tbl.LAST LOOP
2987 trans_id(i) := p_ta_intf_tbl(i).transaction_id;
2988 END LOOP;
2989
2990
2991 FORALL I IN p_ta_intf_tbl.first..p_ta_intf_tbl.last -- LOOP
2992 UPDATE EGO_TRANS_ATTRS_VERS_INTF
2993 SET ROW= p_ta_intf_tbl(i) -- bug 9701271
2994 WHERE
2995 transaction_id = trans_id(i);
2996
2997 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Update_Intf_Trans_Attrs');
2998
2999 EXCEPTION
3000 WHEN OTHERS THEN
3001 x_return_status:= G_RET_STS_UNEXP_ERROR;
3002 x_return_msg := G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
3003 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Update_Intf_Trans_Attrs');
3004
3005 END Update_Intf_Trans_Attrs;
3006
3007 /* This has to be called through ICC_versions if any of the TA fails we return from here.
3008 so Icc_version fails and we set all the TA's also to error*/
3009
3010 PROCEDURE Update_Intf_Err_Trans_Attrs(
3011 p_set_process_id IN NUMBER,
3012 p_item_catalog_group_id IN NUMBER,
3013 p_icc_version_number_intf IN NUMBER,
3014 x_return_status OUT NOCOPY VARCHAR2,
3015 x_return_msg OUT NOCOPY VARCHAR2)
3016 IS
3017 l_proc_name VARCHAR2(200) :='Update_Intf_Err_Trans_Attrs';
3018 BEGIN
3019 x_return_status:=G_RET_STS_SUCCESS;
3020 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Update_Intf_Err_Trans_Attrs');
3021
3022 UPDATE EGO_TRANS_ATTRS_VERS_INTF
3023 SET process_status= G_ERROR_RECORD
3024 WHERE item_catalog_group_id = p_item_catalog_group_id
3025 AND icc_version_number = p_icc_version_number_intf
3026 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
3027
3028 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Update_Intf_Err_Trans_Attrs');
3029
3030 EXCEPTION
3031 WHEN OTHERS THEN
3032 x_return_status:= G_RET_STS_UNEXP_ERROR;
3033 x_return_msg := G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
3034 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Update_Intf_Err_Trans_Attrs');
3035
3036 END Update_Intf_Err_Trans_Attrs;
3037
3038 /* This has to be called by main API of CP based on the parameter passed by user
3039 while running cp, if user says delete all processed records then this will get called */
3040
3041 PROCEDURE Delete_Processed_Trans_Attrs(
3042 p_set_process_id IN NUMBER,
3043 x_return_status OUT NOCOPY VARCHAR2,
3044 x_return_msg OUT NOCOPY VARCHAR2
3045 )
3046 IS
3047 l_proc_name varchar2(200):='Delete_Processed_Trans_Attrs';
3048 BEGIN
3049 x_return_status:=G_RET_STS_SUCCESS;
3050 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Delete_Processed_Trans_Attrs');
3051
3052 DELETE FROM EGO_TRANS_ATTRS_VERS_INTF
3053 WHERE process_status = G_SUCCESS_RECORD
3054 AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
3055
3056 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Delete_Processed_Trans_Attrs');
3057 EXCEPTION
3058 WHEN OTHERS THEN
3059 x_return_status:= G_RET_STS_UNEXP_ERROR;
3060 x_return_msg := G_PKG_NAME||'.'||l_proc_name||'.'||SQLERRM;
3061 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Delete_Processed_Trans_Attrs');
3062
3063 END Delete_Processed_Trans_Attrs;
3064
3065 --=================Check_TA_IS_INVALID===============--------
3066 FUNCTION Check_TA_IS_INVALID (
3067 p_item_cat_group_id IN NUMBER,
3068 p_icc_version_number IN NUMBER,
3069 p_attr_id IN NUMBER,
3070 p_attr_name IN VARCHAR2,
3071 p_attr_disp_name IN VARCHAR2,
3072 p_attr_sequence IN NUMBER
3073 )
3074 RETURN BOOLEAN
3075 IS
3076
3077 l_attr_id NUMBER;
3078 l_attr_name VARCHAR2(80);
3079 l_attr_disp_name VARCHAR2(80);
3080 l_attr_sequence NUMBER;
3081 l_ta_is_invalid BOOLEAN := FALSE;
3082 l_proc_name varchar2(200):='Check_TA_IS_INVALID';
3083 /**------Query to fetch all associated attribute with passed in ICC--------**/
3084 CURSOR cur_list
3085 IS
3086 SELECT item_catalog_group_id,
3087 icc_version_NUMBER ,
3088 SEQUENCE ,
3089 attr_display_name ,
3090 attr_name ,
3091 attr_id ,
3092 lev
3093 FROM
3094 (SELECT versions.item_catalog_group_id,
3095 versions.icc_version_NUMBER ,
3096 versions.SEQUENCE ,
3097 attrs.attr_display_name ,
3098 attrs.attr_name ,
3099 attrs.attr_id ,
3100 hier.lev
3101 FROM ego_obj_AG_assocs_b assocs ,
3102 ego_attrs_v attrs ,
3103 ego_attr_groups_v ag ,
3104 EGO_TRANS_ATTR_VERS_B versions ,
3105 mtl_item_catalog_groups_kfv icv ,
3106 (SELECT item_catalog_group_id ,
3107 LEVEL lev
3108 FROM mtl_item_catalog_groups_b START
3109 WITH item_catalog_group_id = p_item_cat_group_id CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
3110 ) hier
3111 WHERE ag.attr_group_type = 'EGO_ITEM_TRANS_ATTR_GROUP'
3112 AND assocs.attr_group_id = ag.attr_group_id
3113 AND assocs.classification_code = TO_CHAR(hier.item_catalog_group_id)
3114 AND attrs.attr_group_name = ag.attr_group_name
3115 AND TO_CHAR(icv.item_catalog_group_id) = assocs.classification_code
3116 AND TO_CHAR(versions.association_id) = assocs.association_id
3117 AND TO_CHAR(versions.item_catalog_group_id) = assocs.classification_code
3118 AND attrs.attr_id = versions.attr_id
3119 )
3120 WHERE
3121 (
3122 (
3123 LEV = 1
3124 AND ICC_VERSION_NUMBER = p_icc_version_number
3125 )
3126 OR
3127 (
3128 LEV <> 1
3129 AND
3130 (
3131 item_catalog_group_id, ICC_VERSION_NUMBER
3132 )
3133 IN
3134 (SELECT item_catalog_group_id,
3135 VERSION_SEQ_ID
3136 FROM EGO_MTL_CATALOG_GRP_VERS_B
3137 WHERE start_active_date <=
3138 (SELECT NVL(start_active_date,SYSDATE)
3139 FROM EGO_MTL_CATALOG_GRP_VERS_B
3140 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3141 AND VERSION_SEQ_ID = p_icc_version_number
3142 )
3143 AND NVL(end_active_date, sysdate) >=
3144 (SELECT NVL(start_active_date,SYSDATE)
3145 FROM EGO_MTL_CATALOG_GRP_VERS_B
3146 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3147 AND VERSION_SEQ_ID = p_icc_version_number
3148 )
3149 AND version_seq_id > 0
3150 )
3151 )
3152 ); --end CURSOR cur_list
3153
3154
3155 /**------Query to fetch overridden values for a transaction attribute------**/
3156 CURSOR cur_metadata
3157 IS
3158 SELECT *
3159 FROM
3160 (SELECT *
3161 FROM
3162 (SELECT versions.item_catalog_group_id,
3163 versions.ICC_VERSION_NUMBER ,
3164 versions.ATTR_ID ,
3165 versions.SEQUENCE ,
3166 versions.attr_display_name ,
3167 versions.metadata_level ,
3168 attrs.attr_name ,
3169 Hier.lev
3170 FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
3171 EGO_ATTRS_V ATTRS ,
3172 (SELECT ITEM_CATALOG_GROUP_ID ,
3173 LEVEL LEV
3174 FROM MTL_ITEM_CATALOG_GROUPS_B START
3175 WITH ITEM_CATALOG_GROUP_ID = p_item_cat_group_id CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
3176 ) HIER
3177 WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
3178 AND attrs.attr_id = versions.attr_id
3179 AND attrs.attr_group_type ='EGO_ITEM_TRANS_ATTR_GROUP'
3180 AND versions.metadata_level ='ICC'
3181 )
3182 WHERE
3183 (
3184 (
3185 LEV = 1
3186 AND ICC_VERSION_number = p_icc_version_number
3187 )
3188 OR
3189 (
3190 LEV <> 1
3191 AND
3192 (
3193 item_catalog_group_id, ICC_VERSION_NUMBER
3194 )
3195 IN
3196 (SELECT item_catalog_group_id,
3197 VERSION_SEQ_ID
3198 FROM EGO_MTL_CATALOG_GRP_VERS_B
3199 WHERE
3200 (
3201 item_catalog_group_id,start_active_date
3202 )
3203 IN
3204 (SELECT item_catalog_group_id,
3205 MAX(start_active_date) start_active_date
3206 FROM EGO_MTL_CATALOG_GRP_VERS_B
3207 WHERE NVL(end_active_date, sysdate) >=
3208 (SELECT NVL(start_active_date,SYSDATE)
3209 FROM EGO_MTL_CATALOG_GRP_VERS_B
3210 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3211 AND VERSION_SEQ_ID = p_icc_version_number
3212 )
3213 AND version_seq_id > 0
3214
3215 AND start_active_date <=
3216 (SELECT NVL(start_active_date,SYSDATE)
3217 FROM EGO_MTL_CATALOG_GRP_VERS_B
3218 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3219 AND VERSION_SEQ_ID = p_icc_version_number
3220 )
3221
3222
3223
3224 GROUP BY item_catalog_group_id
3225 HAVING MAX(start_active_date)<=
3226 (SELECT NVL(start_active_date,SYSDATE)
3227 FROM EGO_MTL_CATALOG_GRP_VERS_B
3228 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3229 AND VERSION_SEQ_ID = p_icc_version_number
3230 )
3231 )
3232 )
3233 )
3234 )
3235 )
3236 WHERE
3237 (
3238 lev,attr_id
3239 )
3240 IN
3241 (SELECT MIN(lev),
3242 attr_id
3243 FROM
3244 (SELECT versions.item_catalog_group_id,
3245 versions.ICC_VERSION_NUMBER ,
3246 versions.ATTR_ID ,
3247 versions.SEQUENCE ,
3248 versions.attr_display_name ,
3249 versions.metadata_level ,
3250 Hier.lev
3251 FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
3252 (SELECT ITEM_CATALOG_GROUP_ID ,
3253 LEVEL LEV
3254 FROM MTL_ITEM_CATALOG_GROUPS_B
3255 START WITH ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3256 CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
3257 ) HIER
3258 WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
3259 AND versions.metadata_level ='ICC'
3260 AND versions.attr_display_name IS NOT NULL
3261 )
3262 WHERE
3263 (
3264 (
3265 LEV =1
3266 AND ICC_VERSION_number = p_icc_version_number
3267 )
3268 OR
3269 (
3270 LEV <> 1
3271 AND
3272 (
3273 item_catalog_group_id, ICC_VERSION_NUMBER
3274 )
3275 IN
3276 (SELECT item_catalog_group_id,
3277 VERSION_SEQ_ID
3278 FROM EGO_MTL_CATALOG_GRP_VERS_B
3279 WHERE
3280 (
3281 item_catalog_group_id,start_active_date
3282 )
3283 IN
3284 (SELECT item_catalog_group_id,
3285 MAX(start_active_date) start_active_date
3286 FROM EGO_MTL_CATALOG_GRP_VERS_B
3287 WHERE NVL(end_active_date, sysdate) >=
3288 (SELECT NVL(start_active_date,SYSDATE)
3289 FROM EGO_MTL_CATALOG_GRP_VERS_B
3290 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3291 AND VERSION_SEQ_ID = p_icc_version_number
3292 )
3293 AND version_seq_id > 0
3294
3295
3296 AND start_active_date <=
3297 (SELECT NVL(start_active_date,SYSDATE)
3298 FROM EGO_MTL_CATALOG_GRP_VERS_B
3299 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3300 AND VERSION_SEQ_ID = p_icc_version_number
3301 )
3302
3303
3304
3305
3306 GROUP BY item_catalog_group_id
3307 HAVING MAX(start_active_date)<=
3308 (SELECT NVL(start_active_date,SYSDATE)
3309 FROM EGO_MTL_CATALOG_GRP_VERS_B
3310 WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
3311 AND VERSION_SEQ_ID = p_icc_version_number
3312 )
3313 )
3314 )
3315 )
3316 --AND metadata_level ='ICC'
3317 )
3318 GROUP BY attr_id
3319 )
3320 AND attr_id=l_attr_id
3321 AND attr_id<>Nvl(p_attr_id,0000); --end cur_metadata
3322 BEGIN
3323 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Check_TA_IS_INVALID');
3324 FOR i IN cur_list
3325 LOOP
3326 l_attr_id := i.attr_id;
3327 FOR j IN cur_metadata
3328 LOOP
3329 l_attr_name := j.attr_name;
3330 l_attr_disp_name := j.attr_display_name;
3331 l_attr_sequence := j.SEQUENCE;
3332
3333 /** Validate if any transaction atrribute exist with same
3334 internal name while creating/ updating a transaction attribute**/
3335 IF (p_attr_name IS NOT NULL ) THEN
3336 IF (p_attr_name= l_attr_name) THEN
3337 l_ta_is_invalid := TRUE;
3338 END IF; --IF (p_attr_name= l_attr_name) THEN
3339 END IF ; --IF (p_attr_name IS NOT NULL ) THEN
3340
3341 /** Validate if any transaction atrribute exist with same
3342 display name while creating/ updating a transaction attribute**/
3343 IF (p_attr_disp_name IS NOT NULL ) THEN
3344 IF (p_attr_disp_name= l_attr_disp_name) THEN
3345 l_ta_is_invalid := TRUE;
3346 END IF; --IF (p_attr_disp_name= l_attr_disp_name) THEN
3347 END IF; --IF (p_attr_disp_name IS NOT NULL ) THEN
3348
3349 /** Validate if any transaction atrribute exist with same
3350 sequence while creating/ updating a transaction attribute**/
3351 IF (p_attr_sequence IS NOT NULL ) THEN
3352 IF (p_attr_sequence = l_attr_sequence) THEN
3353 l_ta_is_invalid := TRUE;
3354 END IF; --IF (p_attr_sequence= l_attr_sequence) THEN
3355 END IF ; --IF (p_attr_sequence IS NOT NULL )
3356
3357 END LOOP;--FOR j IN cur_metadata
3358
3359 END LOOP; --FOR i IN cur_list
3360 RETURN l_ta_is_invalid;
3361 ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Check_TA_IS_INVALID');
3362 END Check_TA_IS_INVALID;
3363
3364 END EGO_TA_BULKLOAD_PVT;
[Home] [Help]