[Home] [Help]
PACKAGE BODY: APPS.EGO_PAGES_BULKLOAD_PVT
Source
1 PACKAGE BODY ego_pages_bulkload_pvt AS
2 /* $Header: EGOVPGBB.pls 120.10.12020000.2 2012/10/03 08:49:32 pnagasur ship $ */
3
4 /*This Procedure is used to initialize certain column values in the interface table.
5 Used in the interface flow.
6 x_return_status OUT NOCOPY parameter that returns the status*/
7 PROCEDURE Initialize (x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
8 IS
9 lv_smt NUMBER; --Statement counter
10 lv_proc VARCHAR2(30) := 'Initialize';
11 BEGIN
12 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entered Initialize procedure');
13
14 x_return_status := G_RET_STS_SUCCESS;
15
16 lv_smt := 1;
17
18 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
19
20 /*Sets the EGO Application ID*/
21 SELECT application_id
22 INTO G_EGO_APPLICATION_ID
23 FROM fnd_application
24 WHERE application_short_name = 'EGO';
25
26 /*Sets the EGO_ITEM OBJECT_ID*/
27 SELECT object_id
28 INTO G_OBJECT_ID
29 FROM fnd_objects
30 WHERE obj_name = 'EGO_ITEM';
31
32 lv_smt := 2;
33
34 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
35
36 /*Sets the Transaction_id and upper case the transaction_type for pages interface table*/
37 UPDATE ego_pages_interface
38 SET transaction_id = mtl_system_items_interface_s.nextval,
39 transaction_type = Upper(transaction_type),
40 created_by = G_USER_ID,
41 creation_date = SYSDATE,
42 last_updated_by = G_USER_ID,
43 last_update_date = SYSDATE,
44 last_update_login = G_LOGIN_ID,
45 request_id = G_REQUEST_ID,
46 program_application_id = G_PROG_APPL_ID,
47 program_id = G_PROGRAM_ID,
48 program_update_date = SYSDATE
49 WHERE transaction_id IS NULL
50 AND process_status = G_PROCESS_RECORD
51 AND ( ( G_SET_PROCESS_ID IS NULL )
52 OR ( set_process_id = G_SET_PROCESS_ID ) );
53
54 SELECT COUNT(1) INTO G_PAGES_COUNT
55 FROM ego_pages_interface
56 WHERE process_status = G_PROCESS_RECORD
57 AND ( ( G_SET_PROCESS_ID IS NULL )
58 OR ( set_process_id = G_SET_PROCESS_ID ) );
59
60 lv_smt := 2;
61
62 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
63
64 /*Sets the Transaction_id and upper case the transaction_type for page entries interface table*/
65 UPDATE ego_page_entries_interface
66 SET transaction_id = mtl_system_items_interface_s.nextval,
67 transaction_type = Upper(transaction_type),
68 created_by = G_USER_ID,
69 creation_date = SYSDATE,
70 last_updated_by = G_USER_ID,
71 last_update_date = SYSDATE,
72 last_update_login = G_LOGIN_ID,
73 request_id = G_REQUEST_ID,
74 program_application_id = G_PROG_APPL_ID,
75 program_id = G_PROGRAM_ID,
76 program_update_date = SYSDATE
77 WHERE transaction_id IS NULL
78 AND process_status = G_PROCESS_RECORD
79 AND ( ( G_SET_PROCESS_ID IS NULL )
80 OR ( set_process_id = G_SET_PROCESS_ID ) );
81
82 SELECT COUNT(1) INTO G_PAGE_ENTRIES_COUNT
83 FROM ego_page_entries_interface
84 WHERE process_status = G_PROCESS_RECORD
85 AND ( ( G_SET_PROCESS_ID IS NULL )
86 OR ( set_process_id = G_SET_PROCESS_ID ) );
87
88 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Initialize procedure');
89 EXCEPTION
90 WHEN OTHERS THEN
91 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Initialize - Exception when others'||SQLERRM);
92
93 x_return_status := G_RET_STS_UNEXP_ERROR;
94
95 x_return_msg := 'ego_pages_bulkload_pvt.Initialize - '||SQLERRM;
96
97 RETURN;
98 END Initialize;
99
100 /*This procedure is used to validate the transaction type for all the interface tables.
101 Used in the interface flow.
102 x_return_status OUT NOCOPY parameter that returns the status*/
103 PROCEDURE Validate_transaction_type (x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
104 IS
105 lv_smt NUMBER; --Statement counter
106 lv_proc VARCHAR2(30) := 'Validate_transaction_type';
107 BEGIN
108 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entered Validate_transaction_type');
109
110 x_return_status := G_RET_STS_SUCCESS;
111
112 /* Update the interface records with process_status 3 and insert into
113 MTL_INTERFACE_ERRORS if TRANSACTION_TYPE passed is incorrect*/
114 G_MESSAGE_NAME := 'EGO_TRANS_TYPE_INVALID';
115
116 fnd_message.Set_name(G_EGO_APPLICATION_ID, 'EGO_TRANS_TYPE_INVALID');
117
118 G_MESSAGE_TEXT := fnd_message.get;
119
120 lv_smt := 1;
121
122 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
123
124 INSERT INTO mtl_interface_errors
125 (transaction_id,
126 unique_id,
127 organization_id,
128 column_name,
129 table_name,
130 bo_identifier,
131 entity_identifier,
132 message_name,
133 error_message,
134 last_update_date,
135 last_updated_by,
136 creation_date,
137 created_by,
138 last_update_login,
139 request_id,
140 program_application_id,
141 program_id,
142 program_update_date)
143 SELECT transaction_id,
144 mtl_system_items_interface_s.nextval,
145 NULL,
146 NULL,
147 G_ENTITY_PG_TAB,
148 G_BO_IDENTIFIER_PG,
149 G_ENTITY_PG,
150 G_MESSAGE_NAME,
151 G_MESSAGE_TEXT,
152 Nvl(last_update_date, SYSDATE),
153 Nvl(last_updated_by, G_USER_ID),
154 Nvl(creation_date, SYSDATE),
155 Nvl(created_by, G_USER_ID),
156 Nvl(last_update_login, G_USER_ID),
157 G_REQUEST_ID,
158 Nvl(program_application_id, G_PROG_APPL_ID),
159 Nvl(program_id, G_PROGRAM_ID),
160 Nvl(program_update_date, SYSDATE)
161 FROM ego_pages_interface
162 WHERE (transaction_type IS NULL
163 OR transaction_type NOT IN ( G_OPR_CREATE, G_OPR_UPDATE, G_OPR_DELETE, G_OPR_SYNC ))
164 AND transaction_id IS NOT NULL
165 AND process_status = G_PROCESS_RECORD
166 AND ( ( G_SET_PROCESS_ID IS NULL )
167 OR ( set_process_id = G_SET_PROCESS_ID ) );
168
169 UPDATE ego_pages_interface
170 SET process_status = G_ERROR_RECORD,
171 last_updated_by = G_USER_ID,
172 last_update_date = SYSDATE,
173 last_update_login = G_LOGIN_ID
174 WHERE (transaction_type IS NULL
175 OR transaction_type NOT IN ( G_OPR_CREATE, G_OPR_UPDATE, G_OPR_DELETE, G_OPR_SYNC ))
176 AND transaction_id IS NOT NULL
177 AND process_status = G_PROCESS_RECORD
178 AND ( ( G_SET_PROCESS_ID IS NULL )
179 OR ( set_process_id = G_SET_PROCESS_ID ) );
180
181 lv_smt := 2;
182
183 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
184
185 INSERT INTO mtl_interface_errors
186 (transaction_id,
187 unique_id,
188 organization_id,
189 column_name,
190 table_name,
191 bo_identifier,
192 entity_identifier,
193 message_name,
194 error_message,
195 last_update_date,
196 last_updated_by,
197 creation_date,
198 created_by,
199 last_update_login,
200 request_id,
201 program_application_id,
202 program_id,
203 program_update_date)
204 SELECT transaction_id,
205 mtl_system_items_interface_s.nextval,
206 NULL,
207 NULL,
208 G_ENTITY_ENT_TAB,
209 G_BO_IDENTIFIER_PG,
210 G_ENTITY_ENT,
211 G_MESSAGE_NAME,
212 G_MESSAGE_TEXT,
213 Nvl(last_update_date, SYSDATE),
214 Nvl(last_updated_by, G_USER_ID),
215 Nvl(creation_date, SYSDATE),
216 Nvl(created_by, G_USER_ID),
217 Nvl(last_update_login, G_USER_ID),
218 G_REQUEST_ID,
219 Nvl(program_application_id, G_PROG_APPL_ID),
220 Nvl(program_id, G_PROGRAM_ID),
221 Nvl(program_update_date, SYSDATE)
222 FROM ego_page_entries_interface
223 WHERE (transaction_type IS NULL
224 OR transaction_type NOT IN ( G_OPR_CREATE, G_OPR_UPDATE, G_OPR_SYNC, G_OPR_DELETE ))
225 AND transaction_id IS NOT NULL
226 AND process_status = G_PROCESS_RECORD
227 AND ( ( G_SET_PROCESS_ID IS NULL )
228 OR ( set_process_id = G_SET_PROCESS_ID ) );
229
230 UPDATE ego_page_entries_interface
231 SET process_status = G_ERROR_RECORD,
232 last_updated_by = G_USER_ID,
233 last_update_date = SYSDATE,
234 last_update_login = G_LOGIN_ID
235 WHERE (transaction_type IS NULL
236 OR transaction_type NOT IN ( G_OPR_CREATE, G_OPR_UPDATE, G_OPR_SYNC, G_OPR_DELETE ))
237 AND transaction_id IS NOT NULL
238 AND process_status = G_PROCESS_RECORD
239 AND ( ( G_SET_PROCESS_ID IS NULL )
240 OR ( set_process_id = G_SET_PROCESS_ID ) );
241
242 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Validate_transaction_type');
243 EXCEPTION
244 WHEN OTHERS THEN
245 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Validate_transaction_type Exception when others'||SQLERRM);
246
247 x_return_status := G_RET_STS_UNEXP_ERROR;
248
249 x_return_msg := 'ego_pages_bulkload_pvt.Validate_transaction_type - '||SQLERRM;
250
251 RETURN;
252 END Validate_transaction_type;
253
254 /*This procedure is used for value to ID conversion for Pages.
255 Used in the interface flow.
256 x_return_status OUT NOCOPY parameter that returns the status*/
257 PROCEDURE value_to_id_pages (x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
258 IS
259 lv_smt NUMBER; --Statement counter
260 lv_proc VARCHAR2(30) := 'value_to_id_pages';
261 BEGIN
262 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering Value_to_id_pg');
263
264 x_return_status := G_RET_STS_SUCCESS;
265
266 /*Get the ICC ID from the Concatenated ICC Name*/
267 lv_smt := 1;
268
269 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
270
271 UPDATE ego_pages_interface epi
272 SET classification_code = EGO_ICC_BULKLOAD_PVT.Get_Catalog_Group_Id(classification_name,'FIND_COMBINATION'),
273 last_updated_by = G_USER_ID,
274 last_update_date = SYSDATE,
275 last_update_login = G_LOGIN_ID
276 WHERE classification_name IS NOT NULL
277 AND classification_code IS NULL
278 AND process_status = G_PROCESS_RECORD
279 AND ( ( G_SET_PROCESS_ID IS NULL )
280 OR ( set_process_id = G_SET_PROCESS_ID ) );
281
282 /*Get the page ID and set SYNC to UPDATE*/
283 lv_smt := 2;
284
285 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
286
287 UPDATE ego_pages_interface epi
288 SET page_id = (SELECT page_id
289 FROM ego_pages_b
290 WHERE object_id = G_OBJECT_ID
291 AND internal_name = epi.internal_name
292 AND classification_code = epi.classification_code),
293 last_updated_by = G_USER_ID,
294 last_update_date = SYSDATE,
295 last_update_login = G_LOGIN_ID
296 WHERE transaction_type <> G_OPR_CREATE
297 AND ( ( page_id IS NULL
298 AND internal_name IS NOT NULL
299 AND classification_code IS NOT NULL )
300 OR page_id IS NOT NULL )
301 AND process_status = G_PROCESS_RECORD
302 AND ( ( G_SET_PROCESS_ID IS NULL )
303 OR ( set_process_id = G_SET_PROCESS_ID ) );
304
305 /*Set SYNC to CREATE and UPDATE*/
306 lv_smt := 3;
307
308 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
309
310 UPDATE ego_pages_interface epi
311 SET transaction_type = decode(page_id,NULL,G_OPR_CREATE,G_OPR_UPDATE),
312 last_updated_by = G_USER_ID,
313 last_update_date = SYSDATE,
314 last_update_login = G_LOGIN_ID
315 WHERE transaction_type = G_OPR_SYNC
316 AND process_status = G_PROCESS_RECORD
317 AND ( ( G_SET_PROCESS_ID IS NULL )
318 OR ( set_process_id = G_SET_PROCESS_ID ) );
319
320 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_pg');
321 EXCEPTION
322 WHEN OTHERS THEN
323 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pages Exception when others'||SQLERRM);
324
325 x_return_status := G_RET_STS_UNEXP_ERROR;
326
327 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pages - '||SQLERRM;
328
329 RETURN;
330 END value_to_id_pages;
331
332 /*This procedure is used for the value to ID conversion for Page Entries.
333 Used in the interface flow.
334 x_return_status OUT NOCOPY parameter that returns the status*/
335 PROCEDURE value_to_id_pg_entries (x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
336 IS
337 lv_smt NUMBER; --Statement counter
338 lv_proc VARCHAR2(30) := 'value_to_id_pg_entries';
339 BEGIN
340
341 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering value_to_id_pg_entries');
342
343 /*Get the ICC ID from the Concatenated ICC Name*/
344 lv_smt := 1;
345
346 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
347
348 x_return_status := G_RET_STS_SUCCESS;
349
350 UPDATE ego_page_entries_interface epi
351 SET classification_code = EGO_ICC_BULKLOAD_PVT.Get_Catalog_Group_Id(classification_name,'FIND_COMBINATION'),
352 last_updated_by = G_USER_ID,
353 last_update_date = SYSDATE,
354 last_update_login = G_LOGIN_ID
355 WHERE classification_name IS NOT NULL
356 AND classification_code IS NULL
357 AND process_status = G_PROCESS_RECORD
358 AND ( ( G_SET_PROCESS_ID IS NULL )
359 OR ( set_process_id = G_SET_PROCESS_ID ) );
360
361 /*Get the page ID*/
362 lv_smt := 2;
363
364 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
365 --pnagasur: Modified for Bug 13022991
366 UPDATE ego_page_entries_interface epi
367 SET page_id = (SELECT page_id
368 FROM ego_pages_b
369 WHERE object_id = G_OBJECT_ID
370 AND internal_name = epi.internal_name
371 AND classification_code IN ( SELECT PARENT_CATALOG_GROUP_ID FROM EGO_ITEM_CAT_DENORM_HIER
372 WHERE CHILD_CATALOG_GROUP_ID = epi.classification_code)),
373 last_updated_by = G_USER_ID,
374 last_update_date = SYSDATE,
375 last_update_login = G_LOGIN_ID
376 WHERE page_id IS NULL
377 AND internal_name IS NOT NULL
378 AND classification_code IS NOT NULL
379 AND process_status = G_PROCESS_RECORD
380 AND ( ( G_SET_PROCESS_ID IS NULL )
381 OR ( set_process_id = G_SET_PROCESS_ID ) );
382
383 /*Sets the old_attr_group_id*/
384 lv_smt := 3;
385
386 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
387
388 UPDATE ego_page_entries_interface epi
389 SET old_attr_group_id = (SELECT attr_group_id
390 FROM ego_fnd_dsc_flx_ctx_ext
391 WHERE application_id = (SELECT application_id
392 FROM fnd_application
393 WHERE application_short_name = 'EGO')
394 AND descriptive_flexfield_name IN ('EGO_ITEMMGMT_GROUP','EGO_MASTER_ITEMS') /*Added EGO_MASTER_ITEMS for bug 9950697*/
395 AND descriptive_flex_context_code = epi.old_attr_group_name),
396 last_updated_by = G_USER_ID,
397 last_update_date = SYSDATE,
398 last_update_login = G_LOGIN_ID
399 WHERE old_attr_group_id IS NULL
400 AND old_attr_group_name IS NOT NULL
401 AND process_status = G_PROCESS_RECORD
402 AND ( ( G_SET_PROCESS_ID IS NULL )
403 OR ( set_process_id = G_SET_PROCESS_ID ) );
404
405 /*Sets the new_attr_group_id*/
406 lv_smt := 4;
407
408 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
409
410 UPDATE ego_page_entries_interface epi
411 SET new_attr_group_id = (SELECT attr_group_id
412 FROM ego_fnd_dsc_flx_ctx_ext
413 WHERE application_id = (SELECT application_id
414 FROM fnd_application
415 WHERE application_short_name = 'EGO')
416 AND descriptive_flexfield_name IN ('EGO_ITEMMGMT_GROUP','EGO_MASTER_ITEMS') /*Added EGO_MASTER_ITEMS for bug 9950697*/
417 AND descriptive_flex_context_code = epi.new_attr_group_name),
418 last_updated_by = G_USER_ID,
419 last_update_date = SYSDATE,
420 last_update_login = G_LOGIN_ID
421 WHERE new_attr_group_id IS NULL
422 AND new_attr_group_name IS NOT NULL
423 AND process_status = G_PROCESS_RECORD
424 AND ( ( G_SET_PROCESS_ID IS NULL )
425 OR ( set_process_id = G_SET_PROCESS_ID ) );
426
427 lv_smt := 5;
428
429 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
430
431 /*Sets the old_association_id*/
432 UPDATE ego_page_entries_interface epi
433 SET old_association_id = (SELECT association_id
434 FROM EGO_OBJ_AG_ASSOCS_B
435 WHERE classification_code IN ( SELECT PARENT_CATALOG_GROUP_ID
436 FROM EGO_ITEM_CAT_DENORM_HIER
437 WHERE CHILD_CATALOG_GROUP_ID = epi.classification_code)
438 AND attr_group_id = epi.old_attr_group_id
439 AND data_level = (SELECT data_level
440 FROM ego_pages_b
441 WHERE page_id = epi.page_id)),
442 last_updated_by = G_USER_ID,
443 last_update_date = SYSDATE,
444 last_update_login = G_LOGIN_ID
445 WHERE old_association_id IS NULL
446 AND old_attr_group_id IS NOT NULL
447 AND classification_code IS NOT NULL
448 AND process_status = G_PROCESS_RECORD
449 AND ( ( G_SET_PROCESS_ID IS NULL )
450 OR ( set_process_id = G_SET_PROCESS_ID ) );
451
452 lv_smt := 6;
453
454 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 6');
455
456 /*Sets the new_association_id*/
457 UPDATE ego_page_entries_interface epi
458 SET new_association_id = (SELECT association_id
459 FROM EGO_OBJ_AG_ASSOCS_B
460 WHERE classification_code IN ( SELECT PARENT_CATALOG_GROUP_ID
461 FROM EGO_ITEM_CAT_DENORM_HIER
462 WHERE CHILD_CATALOG_GROUP_ID = epi.classification_code)
463 AND attr_group_id = epi.new_attr_group_id
464 AND data_level = (SELECT data_level
465 FROM ego_pages_b
466 WHERE page_id = epi.page_id)),
467 last_updated_by = G_USER_ID,
468 last_update_date = SYSDATE,
469 last_update_login = G_LOGIN_ID
470 WHERE new_association_id IS NULL
471 AND new_attr_group_id IS NOT NULL
472 AND classification_code IS NOT NULL
473 AND process_status = G_PROCESS_RECORD
474 AND ( ( G_SET_PROCESS_ID IS NULL )
475 OR ( set_process_id = G_SET_PROCESS_ID ) );
476
477 /*Sets the SYNC to UPDATE*/
478 lv_smt := 7;
479
480 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 7');
481
482 UPDATE ego_page_entries_interface epi
483 SET transaction_type = G_OPR_UPDATE,
484 last_updated_by = G_USER_ID,
485 last_update_date = SYSDATE,
486 last_update_login = G_LOGIN_ID
487 WHERE EXISTS (SELECT 1
488 FROM ego_page_entries_b
489 WHERE page_id = epi.page_id
490 AND association_id = epi.old_association_id)
491 AND transaction_type = G_OPR_SYNC
492 AND process_status = G_PROCESS_RECORD
493 AND ( ( G_SET_PROCESS_ID IS NULL )
494 OR ( set_process_id = G_SET_PROCESS_ID ) );
495
496 /*Sets the SYNC to CREATE*/
497 lv_smt := 8;
498
499 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 8');
500
501 UPDATE ego_page_entries_interface epi
502 SET transaction_type = G_OPR_CREATE,
503 last_updated_by = G_USER_ID,
504 last_update_date = SYSDATE,
505 last_update_login = G_LOGIN_ID
506 WHERE transaction_type = G_OPR_SYNC
507 AND process_status = G_PROCESS_RECORD
508 AND ( ( G_SET_PROCESS_ID IS NULL )
509 OR ( set_process_id = G_SET_PROCESS_ID ) );
510
511 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit value_to_id_pg_entries');
512 EXCEPTION
513 WHEN OTHERS THEN
514 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pg_entries Exception when others'||SQLERRM);
515
516 x_return_status := G_RET_STS_UNEXP_ERROR;
517
518 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pg_entries - '||SQLERRM;
519
520 RETURN;
521 END value_to_id_pg_entries;
522
523 /*This procedure is used for constructing the records for pages.
524 Used in the interface flow.
525 x_return_status OUT NOCOPY parameter that returns the status*/
526 PROCEDURE construct_pages (x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
527 IS
528 lv_smt NUMBER; --Statement counter
529 lv_proc VARCHAR2(30) := 'construct_pages';
530 BEGIN
531 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering construct_pages');
532
533 /*Set the Page internal name when the page id is given*/
534 lv_smt := 1;
535
536 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
537
538 x_return_status := G_RET_STS_SUCCESS;
539
540 UPDATE ego_pages_interface epi
541 SET (internal_name, classification_code) = (SELECT internal_name, classification_code
542 FROM ego_pages_v
543 WHERE page_id = epi.page_id),
544 last_updated_by = G_USER_ID,
545 last_update_date = SYSDATE,
546 last_update_login = G_LOGIN_ID
547 WHERE page_id IS NOT NULL
548 AND process_status = G_PROCESS_RECORD
549 AND ( ( G_SET_PROCESS_ID IS NULL )
550 OR ( set_process_id = G_SET_PROCESS_ID ) );
551
552 /*Sets the classification name if the classification code is given*/
553 lv_smt := 2;
554
555 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
556
557 UPDATE ego_pages_interface epi
558 SET classification_name = (SELECT icc_kfv.concatenated_segments
559 FROM mtl_item_catalog_groups_kfv icc_kfv
560 WHERE icc_kfv.item_catalog_group_id = epi.classification_code),
561 last_updated_by = G_USER_ID,
562 last_update_date = SYSDATE,
563 last_update_login = G_LOGIN_ID
564 WHERE classification_code IS NOT NULL
565 AND process_status = G_PROCESS_RECORD
566 AND ( ( G_SET_PROCESS_ID IS NULL )
567 OR ( set_process_id = G_SET_PROCESS_ID ) );
568
569 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit construct_pages');
570 EXCEPTION
571 WHEN OTHERS THEN
572 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_pages Exception when others'||SQLERRM);
573
574 x_return_status := G_RET_STS_UNEXP_ERROR;
575
576 x_return_msg := 'ego_pages_bulkload_pvt.construct_pages - '||SQLERRM;
577
578 RETURN;
579 END construct_pages;
580
581 /*This procedure is used for constrcting the records for page entries.
582 Used in the interface flow.
583 x_return_status OUT NOCOPY parameter that returns the status*/
584 PROCEDURE construct_pg_entries (x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
585 IS
586 lv_smt NUMBER; --Statement counter
587 lv_proc VARCHAR2(30) := 'construct_pg_entries';
588 BEGIN
589
590 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering construct_pg_entries');
591
592 /*Sets the internal_name and the classification_code with the page_id is given*/
593
594 lv_smt := 1;
595
596 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
597
598 x_return_status := G_RET_STS_SUCCESS;
599 --pnagasur : Modified for Bug 13022991
600 UPDATE ego_page_entries_interface epi
601 SET ( internal_name ) = (SELECT internal_name
602 FROM ego_pages_b
603 WHERE page_id = epi.page_id),
604 last_updated_by = G_USER_ID,
605 last_update_date = SYSDATE,
606 last_update_login = G_LOGIN_ID
607 WHERE page_id IS NOT NULL
608 AND process_status = G_PROCESS_RECORD
609 AND ( ( G_SET_PROCESS_ID IS NULL )
610 OR ( set_process_id = G_SET_PROCESS_ID ) );
611
612 /*Sets the classification name if the classification code is given*/
613 lv_smt := 2;
614
615 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
616
617 UPDATE ego_page_entries_interface epi
618 SET classification_name = (SELECT icc_kfv.concatenated_segments
619 FROM mtl_item_catalog_groups_kfv icc_kfv
620 WHERE icc_kfv.item_catalog_group_id = epi.classification_code),
621 last_updated_by = G_USER_ID,
622 last_update_date = SYSDATE,
623 last_update_login = G_LOGIN_ID
624 WHERE classification_code IS NOT NULL
625 AND process_status = G_PROCESS_RECORD
626 AND ( ( G_SET_PROCESS_ID IS NULL )
627 OR ( set_process_id = G_SET_PROCESS_ID ) );
628
629 /*Sets the old_attr_group_id and old_attr_group_name, if the old_association_id is given*/
630 lv_smt := 3;
631
632 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
633
634 UPDATE ego_page_entries_interface epi
635 SET ( old_attr_group_id, old_attr_group_name ) = (SELECT attr_group_id,
636 attr_group_name
637 FROM ego_obj_attr_grp_assocs_v
638 WHERE association_id = epi.old_association_id),
639 last_updated_by = G_USER_ID,
640 last_update_date = SYSDATE,
641 last_update_login = G_LOGIN_ID
642 WHERE old_association_id IS NOT NULL
643 AND process_status = G_PROCESS_RECORD
644 AND ( ( G_SET_PROCESS_ID IS NULL )
645 OR ( set_process_id = G_SET_PROCESS_ID ) );
646
647 /*Sets the new_attr_group_id and new_attr_group_name, if the new_association_id is given*/
648 lv_smt := 4;
649
650 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
651
652 UPDATE ego_page_entries_interface epi
653 SET ( new_attr_group_id, new_attr_group_name ) = (SELECT attr_group_id,
654 attr_group_name
655 FROM ego_obj_attr_grp_assocs_v
656 WHERE association_id = epi.new_association_id),
657 last_updated_by = G_USER_ID,
658 last_update_date = SYSDATE,
659 last_update_login = G_LOGIN_ID
660 WHERE new_association_id IS NOT NULL
661 AND process_status = G_PROCESS_RECORD
662 AND ( ( G_SET_PROCESS_ID IS NULL )
663 OR ( set_process_id = G_SET_PROCESS_ID ) );
664
665 /*Sets the old_attr_group_name and old_association_id, if the old_attr_group_id is given*/
666 lv_smt := 5;
667
668 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
669
670 UPDATE ego_page_entries_interface epi
671 SET ( old_association_id, old_attr_group_name ) = (SELECT association_id,
672 attr_group_name
673 FROM ego_obj_attr_grp_assocs_v
674 WHERE attr_group_id = epi.old_attr_group_id
675 AND classification_code = epi.classification_code),
676 last_updated_by = G_USER_ID,
677 last_update_date = SYSDATE,
678 last_update_login = G_LOGIN_ID
679 WHERE old_attr_group_id IS NOT NULL
680 AND old_attr_group_name IS NULL
681 AND old_association_id IS NULL
682 AND process_status = G_PROCESS_RECORD
683 AND ( ( G_SET_PROCESS_ID IS NULL )
684 OR ( set_process_id = G_SET_PROCESS_ID ) );
685
686 /*Sets the new_attr_group_name and new_association_id, if the new_attr_group_id is given*/
687 lv_smt := 5;
688
689 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
690
691 UPDATE ego_page_entries_interface epi
692 SET ( new_association_id, new_attr_group_name ) = (SELECT association_id,
693 attr_group_name
694 FROM ego_obj_attr_grp_assocs_v
695 WHERE attr_group_id = epi.new_attr_group_id),
696 last_updated_by = G_USER_ID,
697 last_update_date = SYSDATE,
698 last_update_login = G_LOGIN_ID
699 WHERE new_attr_group_id IS NOT NULL
700 AND new_attr_group_name IS NULL
701 AND new_association_id IS NULL
702 AND process_status = G_PROCESS_RECORD
703 AND ( ( G_SET_PROCESS_ID IS NULL )
704 OR ( set_process_id = G_SET_PROCESS_ID ) );
705
706 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit construct_pg_entries');
707 EXCEPTION
708 WHEN OTHERS THEN
709 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_pg_entries Exception when others'||SQLERRM);
710
711 x_return_status := G_RET_STS_UNEXP_ERROR;
712
713 x_return_msg := 'ego_pages_bulkload_pvt.construct_pg_entries - '||SQLERRM;
714
715 RETURN;
716 END construct_pg_entries;
717
718 /*This procedure is used to construct the records for page pl/sql table.
719 p_pg_tbl IN OUT NOCOPY Pages table
720 x_return_status OUT NOCOPY parameter that returns the status*/
721 PROCEDURE construct_page_tbl(
722 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
723 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
724 IS
725 lv_proc VARCHAR2(30) := 'construct_page_tbl';
726 BEGIN
727 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering construct_page_tbl');
728
729 x_return_status := G_RET_STS_SUCCESS;
730
731 FOR i IN p_pg_tbl.FIRST.. p_pg_tbl.LAST LOOP
732 IF (p_pg_tbl(i).process_status = G_PROCESS_RECORD) THEN
733 /*Set the Page internal name when the page id is given*/
734 IF (p_pg_tbl(i).page_id IS NOT NULL) THEN
735 BEGIN
736 SELECT internal_name, classification_code INTO p_pg_tbl(i).internal_name, p_pg_tbl(i).classification_code
737 FROM ego_pages_v
738 WHERE page_id = p_pg_tbl(i).page_id;
739 EXCEPTION
740 WHEN NO_DATA_FOUND THEN
741 x_return_status := G_RET_STS_ERROR;
742
743 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_pg_tbl(i).transaction_id||' (PG) = ('||p_pg_tbl(i).internal_name||'). ' ||'Page is not defined in the system');
744
745 error_handler.Add_error_message(p_message_name => 'EGO_PG_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
746 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
747 p_row_identifier => p_pg_tbl(i).transaction_id,
748 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
749 WHEN OTHERS THEN
750 x_return_status := G_RET_STS_UNEXP_ERROR;
751
752 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_pg_tbl(i).transaction_id||' (PG) = ('||p_pg_tbl(i).internal_name||'). ' ||'Exception: '||SQLERRM);
753
754 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
755 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
756 p_row_identifier => p_pg_tbl(i).transaction_id,
757 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
758
759 x_return_msg := 'ego_pages_bulkload_pvt.construct_page_tbl smt 1 - '||SQLERRM;
760
761 RETURN;
762
763 END;
764 END IF;
765
766 /*Sets the classification name if the classification code is given*/
767 IF (p_pg_tbl(i).classification_code IS NOT NULL) THEN
768 BEGIN
769 SELECT icc_kfv.concatenated_segments INTO p_pg_tbl(i).classification_name
770 FROM mtl_item_catalog_groups_kfv icc_kfv
771 WHERE icc_kfv.item_catalog_group_id = p_pg_tbl(i).classification_code;
772 EXCEPTION
773 WHEN NO_DATA_FOUND THEN
774 x_return_status := G_RET_STS_ERROR;
775
776 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_pg_tbl(i).transaction_id||' (PG,ICC) = ('||p_pg_tbl(i).internal_name||','||p_pg_tbl(i).classification_code||'). ' ||'ICC is not defined in the system');
777
778 error_handler.Add_error_message(p_message_name => 'EGO_PG_ICC_INVALID',p_application_id => G_EGO_APPLICATION_ID,
779 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
780 p_row_identifier => p_pg_tbl(i).transaction_id,
781 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
782 WHEN OTHERS THEN
783 x_return_status := G_RET_STS_UNEXP_ERROR;
784
785 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_pg_tbl(i).transaction_id||' (PG,ICC) = ('||p_pg_tbl(i).internal_name||','||p_pg_tbl(i).classification_code||'). ' ||'Exception: '||SQLERRM);
786
787 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
788 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
789 p_row_identifier => p_pg_tbl(i).transaction_id,
790 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
791
792 x_return_msg := 'ego_pages_bulkload_pvt.construct_page_tbl smt 2 - '||SQLERRM;
793
794 RETURN;
795 END;
796 END IF;
797 END IF;
798 END LOOP;
799
800 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit construct_page_tbl');
801 EXCEPTION
802 WHEN OTHERS THEN
803 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_page_tbl Exception when others'||SQLERRM);
804
805 x_return_status := G_RET_STS_UNEXP_ERROR;
806
807 x_return_msg := 'ego_pages_bulkload_pvt.construct_page_tbl - '||SQLERRM;
808
809 RETURN;
810 END construct_page_tbl;
811
812 /*This procedure is used sed to construct the records for page entries pl/sql table.
813 Used in the API flow.
814 p_ent_tbl IN OUT NOCOPY Page Entries table
815 x_return_status OUT NOCOPY parameter that returns the status*/
816 PROCEDURE construct_pg_entries_tbl(
817 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
818 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
819 IS
820 lv_proc VARCHAR2(30) := 'construct_pg_entries_tbl';
821 BEGIN
822 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering construct_pg_entries_tbl');
823
824 x_return_status := G_RET_STS_SUCCESS;
825
826 FOR i IN p_ent_tbl.FIRST.. p_ent_tbl.LAST LOOP
827 IF (p_ent_tbl(i).process_status = G_PROCESS_RECORD) THEN
828 /*Set the Page internal name when the page id is given*/
829 IF (p_ent_tbl(i).page_id IS NOT NULL) THEN
830 BEGIN
831 SELECT internal_name, classification_code INTO p_ent_tbl(i).internal_name, p_ent_tbl(i).classification_code
832 FROM ego_pages_v
833 WHERE page_id = p_ent_tbl(i).page_id;
834 EXCEPTION
835 WHEN NO_DATA_FOUND THEN
836 x_return_status := G_RET_STS_ERROR;
837
838 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG) = ('||p_ent_tbl(i).internal_name||'). ' ||'Page is not defined in the system');
839
840 error_handler.Add_error_message(p_message_name => 'EGO_PG_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
841 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
842 p_row_identifier => p_ent_tbl(i).transaction_id,
843 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
844 WHEN OTHERS THEN
845 x_return_status := G_RET_STS_UNEXP_ERROR;
846
847 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG) = ('||p_ent_tbl(i).internal_name||'). ' ||'Exception: '||SQLERRM);
848
849 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
850 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
851 p_row_identifier => p_ent_tbl(i).transaction_id,
852 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
853
854 x_return_msg := 'ego_pages_bulkload_pvt.construct_pg_entries_tbl smt 1 - '||SQLERRM;
855
856 RETURN;
857
858 END;
859 END IF;
860
861 /*Sets the classification name if the classification code is given*/
862 IF (p_ent_tbl(i).classification_code IS NOT NULL) THEN
863 BEGIN
864 SELECT icc_kfv.concatenated_segments INTO p_ent_tbl(i).classification_name
865 FROM mtl_item_catalog_groups_kfv icc_kfv
866 WHERE icc_kfv.item_catalog_group_id = p_ent_tbl(i).classification_code;
867 EXCEPTION
868 WHEN NO_DATA_FOUND THEN
869 x_return_status := G_RET_STS_ERROR;
870
871 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,ICC) = ('||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).classification_code||'). ' ||'ICC is not defined in the system');
872
873 error_handler.Add_error_message(p_message_name => 'EGO_PG_ICC_INVALID',p_application_id => G_EGO_APPLICATION_ID,
874 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
875 p_row_identifier => p_ent_tbl(i).transaction_id,
876 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
877 WHEN OTHERS THEN
878 x_return_status := G_RET_STS_UNEXP_ERROR;
879
880 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_pg_entries_tbl exception when others smt 2');
881
882 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
883 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
884 p_row_identifier => p_ent_tbl(i).transaction_id,
885 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
886
887 x_return_msg := 'ego_pages_bulkload_pvt.construct_pg_entries_tbl smt 2 - '||SQLERRM;
888
889 RETURN;
890 END;
891 END IF;
892
893 /*Sets the old_attr_group_id and old_attr_group_name, if the old_association_id is given*/
894 IF (p_ent_tbl(i).old_association_id IS NOT NULL) THEN
895 BEGIN
896 SELECT attr_group_id,attr_group_name INTO p_ent_tbl(i).old_attr_group_id, p_ent_tbl(i).old_attr_group_name
897 FROM ego_obj_attr_grp_assocs_v
898 WHERE association_id = p_ent_tbl(i).old_association_id;
899 EXCEPTION
900 WHEN NO_DATA_FOUND THEN
901 x_return_status := G_RET_STS_ERROR;
902
903 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,AG) = ('||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).old_association_id||'). ' ||'Attribute Association is not defined in the system');
904
905 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
906 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
907 p_row_identifier => p_ent_tbl(i).transaction_id,
908 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
909 WHEN OTHERS THEN
910 x_return_status := G_RET_STS_UNEXP_ERROR;
911
912 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_pg_entries_tbl exception when others smt 3');
913
914 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
915 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
916 p_row_identifier => p_ent_tbl(i).transaction_id,
917 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
918
919 x_return_msg := 'ego_pages_bulkload_pvt.construct_pg_entries_tbl smt 3 - '||SQLERRM;
920
921 RETURN;
922 END;
923 END IF;
924
925 /*Sets the new_attr_group_id and new_attr_group_name, if the new_association_id is given*/
926 IF (p_ent_tbl(i).old_association_id IS NOT NULL) THEN
927 BEGIN
928 SELECT attr_group_id,attr_group_name INTO p_ent_tbl(i).new_attr_group_id, p_ent_tbl(i).new_attr_group_name
929 FROM ego_obj_attr_grp_assocs_v
930 WHERE association_id = p_ent_tbl(i).new_association_id;
931 EXCEPTION
932 WHEN NO_DATA_FOUND THEN
933 x_return_status := G_RET_STS_ERROR;
934
935 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,ICC) = ('||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).new_association_id||'). ' ||'Attribute Association is not defined in the system');
936
937 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
938 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
939 p_row_identifier => p_ent_tbl(i).transaction_id,
940 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
941 WHEN OTHERS THEN
942 x_return_status := G_RET_STS_UNEXP_ERROR;
943
944 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_pg_entries_tbl exception when others smt 4');
945
946 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
947 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
948 p_row_identifier => p_ent_tbl(i).transaction_id,
949 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
950
951 x_return_msg := 'ego_pages_bulkload_pvt.construct_pg_entries_tbl smt 4 - '||SQLERRM;
952
953 RETURN;
954 END;
955 END IF;
956
957 /*Sets the old_attr_group_name and old_association_id, if the old_attr_group_id is given*/
958 IF (p_ent_tbl(i).old_attr_group_id IS NOT NULL AND p_ent_tbl(i).old_attr_group_name IS NULL AND p_ent_tbl(i).old_association_id IS NULL) THEN
959 BEGIN
960 SELECT association_id,attr_group_name INTO p_ent_tbl(i).old_association_id, p_ent_tbl(i).old_attr_group_name
961 FROM ego_obj_attr_grp_assocs_v
962 WHERE attr_group_id = p_ent_tbl(i).old_attr_group_id
963 AND classification_code = p_ent_tbl(i).classification_code;
964 EXCEPTION
965 WHEN NO_DATA_FOUND THEN
966 x_return_status := G_RET_STS_ERROR;
967
968 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,AG) = ('||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).old_association_id||'). ' ||'Attribute Association is not defined in the system');
969
970 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
971 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
972 p_row_identifier => p_ent_tbl(i).transaction_id,
973 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
974 WHEN OTHERS THEN
975 x_return_status := G_RET_STS_UNEXP_ERROR;
976
977 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_pg_entries_tbl exception when others smt 5');
978
979 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
980 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
981 p_row_identifier => p_ent_tbl(i).transaction_id,
982 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
983
984 x_return_msg := 'ego_pages_bulkload_pvt.construct_pg_entries_tbl smt 5 - '||SQLERRM;
985
986 RETURN;
987 END;
988 END IF;
989
990 /*Sets the new_attr_group_name and new_association_id, if the new_attr_group_id is given*/
991 IF (p_ent_tbl(i).new_attr_group_id IS NOT NULL AND p_ent_tbl(i).new_attr_group_name IS NULL AND p_ent_tbl(i).new_association_id IS NULL) THEN
992 BEGIN
993 SELECT association_id,attr_group_name INTO p_ent_tbl(i).new_association_id, p_ent_tbl(i).new_attr_group_name
994 FROM ego_obj_attr_grp_assocs_v
995 WHERE attr_group_id = p_ent_tbl(i).new_attr_group_id
996 AND classification_code = p_ent_tbl(i).classification_code;
997 EXCEPTION
998 WHEN NO_DATA_FOUND THEN
999 x_return_status := G_RET_STS_ERROR;
1000
1001 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,ICC) = ('||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).new_association_id||'). ' ||'Attribute Association is not defined in the system');
1002
1003 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1004 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1005 p_row_identifier => p_ent_tbl(i).transaction_id,
1006 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1007 WHEN OTHERS THEN
1008 x_return_status := G_RET_STS_UNEXP_ERROR;
1009
1010 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_pg_entries_tbl exception when others smt 6');
1011
1012 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1013 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
1014 p_row_identifier => p_ent_tbl(i).transaction_id,
1015 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1016
1017 x_return_msg := 'ego_pages_bulkload_pvt.construct_pg_entries_tbl smt 6 - '||SQLERRM;
1018
1019 RETURN;
1020 END;
1021 END IF;
1022
1023 END IF;
1024 END LOOP;
1025 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit construct_pg_entries_tbl');
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'construct_pg_entries_tbl Exception when others'||SQLERRM);
1029
1030 x_return_status := G_RET_STS_UNEXP_ERROR;
1031
1032 x_return_msg := 'ego_pages_bulkload_pvt.construct_pg_entries_tbl - '||SQLERRM;
1033
1034 RETURN;
1035 END construct_pg_entries_tbl;
1036
1037
1038 /*This procedure is used for value to ID conversion for pages.
1039 Used in the API flow.
1040 p_pg_tbl IN OUT NOCOPY Pages table
1041 x_return_status OUT NOCOPY parameter that returns the status*/
1042 PROCEDURE value_to_id_page_tbl (p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
1043 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
1044 IS
1045 lv_proc VARCHAR2(30) := 'value_to_id_page_tbl';
1046 BEGIN
1047 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering value_to_id_page_tbl');
1048
1049 x_return_status := G_RET_STS_SUCCESS;
1050
1051 FOR i IN p_pg_tbl.FIRST.. p_pg_tbl.LAST LOOP
1052 IF (p_pg_tbl(i).process_status = G_PROCESS_RECORD) THEN
1053 /*Get the ICC ID from the Concatenated ICC Name*/
1054 IF (p_pg_tbl(i).classification_name IS NOT NULL AND p_pg_tbl(i).classification_code IS NULL) THEN
1055 p_pg_tbl(i).classification_code := EGO_ICC_BULKLOAD_PVT.Get_Catalog_Group_Id(p_pg_tbl(i).classification_name,'FIND_COMBINATION');
1056 END IF;
1057
1058 /*Get the page ID and set SYNC to UPDATE*/
1059 IF (p_pg_tbl(i).transaction_type <> G_OPR_CREATE) THEN
1060 IF (p_pg_tbl(i).page_id IS NULL AND p_pg_tbl(i).internal_name IS NOT NULL AND p_pg_tbl(i).classification_code IS NOT NULL) THEN
1061 BEGIN
1062 SELECT page_id INTO p_pg_tbl(i).page_id
1063 FROM ego_pages_b
1064 WHERE object_id = G_OBJECT_ID
1065 AND internal_name = p_pg_tbl(i).internal_name
1066 AND classification_code = p_pg_tbl(i).classification_code;
1067
1068 IF (p_pg_tbl(i).transaction_type = G_OPR_SYNC) THEN
1069 p_pg_tbl(i).transaction_type := G_OPR_UPDATE;
1070 END IF;
1071 EXCEPTION
1072 WHEN NO_DATA_FOUND THEN
1073 IF (p_pg_tbl(i).transaction_type = G_OPR_SYNC) THEN
1074 p_pg_tbl(i).transaction_type := G_OPR_CREATE;
1075 ELSE
1076 x_return_status := G_RET_STS_ERROR;
1077
1078 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page is not defined in the system');
1079
1080 error_handler.Add_error_message(p_message_name => 'EGO_PG_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1081 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1082 p_row_identifier => p_pg_tbl(i).transaction_id,
1083 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
1084 END IF;
1085 WHEN OTHERS THEN
1086 x_return_status := G_RET_STS_UNEXP_ERROR;
1087
1088 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_page_tbl exception when others smt 1');
1089
1090 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1091 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
1092 p_row_identifier => p_pg_tbl(i).transaction_id,
1093 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
1094
1095 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_page_tbl smt 1 - '||SQLERRM;
1096
1097 RETURN;
1098 END;
1099 END IF;
1100 END IF;
1101 END IF;
1102 END LOOP;
1103
1104 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit value_to_id_page_tbl');
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_page_tbl Exception when others'||SQLERRM);
1108
1109 x_return_status := G_RET_STS_UNEXP_ERROR;
1110
1111 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_page_tbl - '||SQLERRM;
1112
1113 RETURN;
1114 END value_to_id_page_tbl;
1115
1116 /*This procedure is used for value to ID conversion for page entries.
1117 Used in the API flow.
1118 p_ent_tbl IN OUT NOCOPY Page Entries table
1119 x_return_status OUT NOCOPY parameter that returns the status*/
1120 PROCEDURE value_to_id_pg_entries_tbl (p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
1121 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
1122 IS
1123 lv_proc VARCHAR2(30) := 'value_to_id_pg_entries_tbl';
1124 lv_flag VARCHAR2(1);
1125 BEGIN
1126 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering value_to_id_pg_entries_tbl');
1127
1128 x_return_status := G_RET_STS_SUCCESS;
1129
1130 FOR i IN p_ent_tbl.FIRST.. p_ent_tbl.LAST LOOP
1131 IF (p_ent_tbl(i).process_status = G_PROCESS_RECORD) THEN
1132 /*Get the ICC ID from the Concatenated ICC Name*/
1133 IF (p_ent_tbl(i).classification_name IS NOT NULL AND p_ent_tbl(i).classification_code IS NULL) THEN
1134 p_ent_tbl(i).classification_code := EGO_ICC_BULKLOAD_PVT.Get_Catalog_Group_Id(p_ent_tbl(i).classification_name,'FIND_COMBINATION');
1135 END IF;
1136
1137 /*Get the page ID*/
1138 IF (p_ent_tbl(i).page_id IS NULL AND p_ent_tbl(i).internal_name IS NOT NULL AND p_ent_tbl(i).classification_code IS NOT NULL) THEN
1139 BEGIN
1140 SELECT page_id INTO p_ent_tbl(i).page_id
1141 FROM ego_pages_b
1142 WHERE object_id = G_OBJECT_ID
1143 AND internal_name = p_ent_tbl(i).internal_name
1144 AND classification_code = p_ent_tbl(i).classification_code;
1145 EXCEPTION
1146 WHEN NO_DATA_FOUND THEN
1147 x_return_status := G_RET_STS_ERROR;
1148
1149 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page is not defined in the system');
1150
1151 error_handler.Add_error_message(p_message_name => 'EGO_PG_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1152 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1153 p_row_identifier => p_ent_tbl(i).transaction_id,
1154 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1155 WHEN OTHERS THEN
1156 x_return_status := G_RET_STS_UNEXP_ERROR;
1157
1158 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pg_entries_tbl exception when others smt 1');
1159
1160 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1161 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
1162 p_row_identifier => p_ent_tbl(i).transaction_id,
1163 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1164
1165 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pg_entries_tbl smt 1 - '||SQLERRM;
1166
1167 RETURN;
1168 END;
1169 END IF;
1170
1171 /*Sets the old_attr_group_id*/
1172 IF (p_ent_tbl(i).old_attr_group_id IS NULL AND p_ent_tbl(i).old_attr_group_name IS NOT NULL) THEN
1173 BEGIN
1174 SELECT attr_group_id INTO p_ent_tbl(i).old_attr_group_id
1175 FROM ego_fnd_dsc_flx_ctx_ext
1176 WHERE application_id = (SELECT application_id
1177 FROM fnd_application
1178 WHERE application_short_name = 'EGO')
1179 AND descriptive_flexfield_name IN ('EGO_ITEMMGMT_GROUP','EGO_MASTER_ITEMS') /*Added EGO_MASTER_ITEMS for bug 9950697*/
1180 AND descriptive_flex_context_code = p_ent_tbl(i).old_attr_group_name;
1181 EXCEPTION
1182 WHEN NO_DATA_FOUND THEN
1183 x_return_status := G_RET_STS_ERROR;
1184
1185 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Association does not exist');
1186
1187 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1188 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1189 p_row_identifier => p_ent_tbl(i).transaction_id,
1190 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1191 WHEN OTHERS THEN
1192 x_return_status := G_RET_STS_UNEXP_ERROR;
1193
1194 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pg_entries_tbl exception when others smt 2');
1195
1196 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1197 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
1198 p_row_identifier => p_ent_tbl(i).transaction_id,
1199 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1200
1201 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pg_entries_tbl smt 2 - '||SQLERRM;
1202
1203 RETURN;
1204
1205 END;
1206 END IF;
1207
1208 /*Sets the new_attr_group_id*/
1209 IF (p_ent_tbl(i).new_attr_group_id IS NULL AND p_ent_tbl(i).new_attr_group_name IS NOT NULL) THEN
1210 BEGIN
1211 SELECT attr_group_id INTO p_ent_tbl(i).new_attr_group_id
1212 FROM ego_fnd_dsc_flx_ctx_ext
1213 WHERE application_id = (SELECT application_id
1214 FROM fnd_application
1215 WHERE application_short_name = 'EGO')
1216 AND descriptive_flexfield_name IN ('EGO_ITEMMGMT_GROUP','EGO_MASTER_ITEMS') /*Added EGO_MASTER_ITEMS for bug 9950697*/
1217 AND descriptive_flex_context_code = p_ent_tbl(i).new_attr_group_name;
1218 EXCEPTION
1219 WHEN NO_DATA_FOUND THEN
1220 x_return_status := G_RET_STS_ERROR;
1221
1222 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Association does not exist');
1223
1224 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1225 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1226 p_row_identifier => p_ent_tbl(i).transaction_id,
1227 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1228 WHEN OTHERS THEN
1229 x_return_status := G_RET_STS_UNEXP_ERROR;
1230
1231 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pg_entries_tbl exception when others smt 3');
1232
1233 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1234 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
1235 p_row_identifier => p_ent_tbl(i).transaction_id,
1236 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1237
1238 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pg_entries_tbl smt 3 - '||SQLERRM;
1239
1240 RETURN;
1241
1242 END;
1243 END IF;
1244
1245 /*Sets the old_attr_group_id*/
1246 IF (p_ent_tbl(i).old_attr_group_id IS NOT NULL AND p_ent_tbl(i).classification_code IS NOT NULL) THEN
1247 BEGIN
1248 SELECT association_id INTO p_ent_tbl(i).old_association_id
1249 FROM EGO_OBJ_AG_ASSOCS_B
1250 WHERE classification_code = p_ent_tbl(i).classification_code
1251 AND attr_group_id = p_ent_tbl(i).old_attr_group_id;
1252 EXCEPTION
1253 WHEN NO_DATA_FOUND THEN
1254 x_return_status := G_RET_STS_ERROR;
1255
1256 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Association does not exist');
1257
1258 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1259 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1260 p_row_identifier => p_ent_tbl(i).transaction_id,
1261 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1262 WHEN OTHERS THEN
1263 x_return_status := G_RET_STS_UNEXP_ERROR;
1264
1265 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pg_entries_tbl exception when others smt 4');
1266
1267 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1268 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
1269 p_row_identifier => p_ent_tbl(i).transaction_id,
1270 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1271
1272 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pg_entries_tbl smt 4 - '||SQLERRM;
1273
1274 RETURN;
1275
1276 END;
1277 END IF;
1278
1279 /*Sets the new_attr_group_id*/
1280 IF (p_ent_tbl(i).new_attr_group_id IS NOT NULL AND p_ent_tbl(i).classification_code IS NOT NULL) THEN
1281 BEGIN
1282 SELECT association_id INTO p_ent_tbl(i).new_association_id
1283 FROM EGO_OBJ_AG_ASSOCS_B
1284 WHERE classification_code = p_ent_tbl(i).classification_code
1285 AND attr_group_id = p_ent_tbl(i).new_attr_group_id;
1286 EXCEPTION
1287 WHEN NO_DATA_FOUND THEN
1288 x_return_status := G_RET_STS_ERROR;
1289
1290 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Association does not exist');
1291
1292 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1293 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1294 p_row_identifier => p_ent_tbl(i).transaction_id,
1295 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1296 WHEN OTHERS THEN
1297 x_return_status := G_RET_STS_UNEXP_ERROR;
1298
1299 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pg_entries_tbl exception when others smt 5');
1300
1301 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1302 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
1303 p_row_identifier => p_ent_tbl(i).transaction_id,
1304 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1305
1306 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pg_entries_tbl smt 5 - '||SQLERRM;
1307
1308 RETURN;
1309
1310 END;
1311 END IF;
1312
1313 /*Sets the SYNC to CREATE or UPDATE*/
1314 IF (p_ent_tbl(i).transaction_type = G_OPR_SYNC) THEN
1315 BEGIN
1316 SELECT 'Y' INTO lv_flag
1317 FROM ego_page_entries_b
1318 WHERE page_id = p_ent_tbl(i).page_id
1319 AND association_id = p_ent_tbl(i).old_association_id;
1320
1321 p_ent_tbl(i).transaction_type := G_OPR_UPDATE;
1322 EXCEPTION
1323 WHEN NO_DATA_FOUND THEN
1324 p_ent_tbl(i).transaction_type := G_OPR_DELETE;
1325 WHEN OTHERS THEN
1326 x_return_status := G_RET_STS_UNEXP_ERROR;
1327
1328 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pg_entries_tbl exception when others smt 6');
1329
1330 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1331 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
1332 p_row_identifier => p_ent_tbl(i).transaction_id,
1333 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1334
1335 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pg_entries_tbl smt 6 - '||SQLERRM;
1336
1337 RETURN;
1338 END;
1339 END IF;
1340 END IF;
1341 END LOOP;
1342
1343 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit value_to_id_pg_entries_tbl');
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'value_to_id_pg_entries_tbl Exception when others'||SQLERRM);
1347
1348 x_return_status := G_RET_STS_UNEXP_ERROR;
1349
1350 x_return_msg := 'ego_pages_bulkload_pvt.value_to_id_pg_entries_tbl - '||SQLERRM;
1351
1352 RETURN;
1353 END value_to_id_pg_entries_tbl;
1354
1355 /*This procedure is used for bulk validation of the pages.
1356 Used in the interface flow.
1357 x_return_status OUT NOCOPY parameter that returns the status*/
1358 PROCEDURE Bulk_validate_pages (x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
1359 IS
1360 lv_smt NUMBER; --Statement counter
1361 lv_proc VARCHAR2(30) := 'Bulk_validate_pages';
1362 BEGIN
1363 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entered Bulk_validate_pages');
1364
1365 x_return_status := G_RET_STS_SUCCESS;
1366
1367 Value_to_id_pages(x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
1368
1369 Construct_pages(x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
1370
1371 lv_smt := 1;
1372
1373 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1.1');
1374
1375 /* Update the interface records with process_status 3 and insert into
1376 MTL_INTERFACE_ERRORS if the ICC name passed is invalid*/
1377 G_MESSAGE_NAME := 'EGO_ICC_ID_INVALID';
1378
1379 fnd_message.Set_name(G_EGO_APPLICATION_ID, 'EGO_ICC_ID_INVALID');
1380
1381 G_MESSAGE_TEXT := fnd_message.get;
1382
1383 INSERT INTO mtl_interface_errors
1384 (transaction_id,
1385 unique_id,
1386 organization_id,
1387 column_name,
1388 table_name,
1389 bo_identifier,
1390 entity_identifier,
1391 message_name,
1392 error_message,
1393 last_update_date,
1394 last_updated_by,
1395 creation_date,
1396 created_by,
1397 last_update_login,
1398 request_id,
1399 program_application_id,
1400 program_id,
1401 program_update_date)
1402 SELECT transaction_id,
1403 mtl_system_items_interface_s.nextval,
1404 NULL,
1405 NULL,
1406 G_ENTITY_PG_TAB,
1407 G_BO_IDENTIFIER_PG,
1408 G_ENTITY_PG,
1409 G_MESSAGE_NAME,
1410 G_MESSAGE_TEXT,
1411 Nvl(last_update_date, SYSDATE),
1412 Nvl(last_updated_by, G_USER_ID),
1413 Nvl(creation_date, SYSDATE),
1414 Nvl(created_by, G_USER_ID),
1415 Nvl(last_update_login, G_USER_ID),
1416 G_REQUEST_ID,
1417 Nvl(program_application_id, G_PROG_APPL_ID),
1418 Nvl(program_id, G_PROGRAM_ID),
1419 Nvl(program_update_date, SYSDATE)
1420 FROM ego_pages_interface epi
1421 WHERE (classification_code IS NULL OR classification_name IS NULL)
1422 AND transaction_id IS NOT NULL
1423 AND process_status = G_PROCESS_RECORD
1424 AND ( ( G_SET_PROCESS_ID IS NULL )
1425 OR ( set_process_id = G_SET_PROCESS_ID ) );
1426
1427 UPDATE ego_pages_interface
1428 SET process_status = G_ERROR_RECORD,
1429 last_updated_by = G_USER_ID,
1430 last_update_date = SYSDATE,
1431 last_update_login = G_LOGIN_ID
1432 WHERE (classification_code IS NULL OR classification_name IS NULL)
1433 AND transaction_id IS NOT NULL
1434 AND process_status = G_PROCESS_RECORD
1435 AND ( ( G_SET_PROCESS_ID IS NULL )
1436 OR ( set_process_id = G_SET_PROCESS_ID ) );
1437
1438 lv_smt := 1.1;
1439
1440 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1.1');
1441
1442 /* Update the interface records with process_status 3 and insert into
1443 MTL_INTERFACE_ERRORS if the page does not exist for update and delete flow*/
1444 G_MESSAGE_NAME := 'EGO_EF_ATTR_PAGE_NOT_FOUND';
1445
1446 fnd_message.Set_name(G_EGO_APPLICATION_ID, 'EGO_EF_ATTR_PAGE_NOT_FOUND');
1447
1448 G_MESSAGE_TEXT := fnd_message.get;
1449
1450 INSERT INTO mtl_interface_errors
1451 (transaction_id,
1452 unique_id,
1453 organization_id,
1454 column_name,
1455 table_name,
1456 bo_identifier,
1457 entity_identifier,
1458 message_name,
1459 error_message,
1460 last_update_date,
1461 last_updated_by,
1462 creation_date,
1463 created_by,
1464 last_update_login,
1465 request_id,
1466 program_application_id,
1467 program_id,
1468 program_update_date)
1469 SELECT transaction_id,
1470 mtl_system_items_interface_s.nextval,
1471 NULL,
1472 NULL,
1473 G_ENTITY_PG_TAB,
1474 G_BO_IDENTIFIER_PG,
1475 G_ENTITY_PG,
1476 G_MESSAGE_NAME,
1477 G_MESSAGE_TEXT,
1478 Nvl(last_update_date, SYSDATE),
1479 Nvl(last_updated_by, G_USER_ID),
1480 Nvl(creation_date, SYSDATE),
1481 Nvl(created_by, G_USER_ID),
1482 Nvl(last_update_login, G_USER_ID),
1483 G_REQUEST_ID,
1484 Nvl(program_application_id, G_PROG_APPL_ID),
1485 Nvl(program_id, G_PROGRAM_ID),
1486 Nvl(program_update_date, SYSDATE)
1487 FROM ego_pages_interface epi
1488 WHERE (page_id IS NULL OR internal_name IS NULL)
1489 AND transaction_type <> G_OPR_CREATE
1490 AND transaction_id IS NOT NULL
1491 AND process_status = G_PROCESS_RECORD
1492 AND ( ( G_SET_PROCESS_ID IS NULL )
1493 OR ( set_process_id = G_SET_PROCESS_ID ) );
1494
1495 UPDATE ego_pages_interface
1496 SET process_status = G_ERROR_RECORD,
1497 last_updated_by = G_USER_ID,
1498 last_update_date = SYSDATE,
1499 last_update_login = G_LOGIN_ID
1500 WHERE (page_id IS NULL OR internal_name IS NULL)
1501 AND transaction_type <> G_OPR_CREATE
1502 AND transaction_id IS NOT NULL
1503 AND process_status = G_PROCESS_RECORD
1504 AND ( ( G_SET_PROCESS_ID IS NULL )
1505 OR ( set_process_id = G_SET_PROCESS_ID ) );
1506
1507 lv_smt := 1.1;
1508
1509 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1.1');
1510
1511 /* Update the interface records with process_status 3 and insert into
1512 MTL_INTERFACE_ERRORS if the page with the internal name already exists in the system*/
1513 G_MESSAGE_NAME := 'EGO_INTERNAL_NAME_EXISTS';
1514
1515 fnd_message.Set_name(G_EGO_APPLICATION_ID, 'EGO_INTERNAL_NAME_EXISTS');
1516
1517 G_MESSAGE_TEXT := fnd_message.get;
1518
1519 INSERT INTO mtl_interface_errors
1520 (transaction_id,
1521 unique_id,
1522 organization_id,
1523 column_name,
1524 table_name,
1525 bo_identifier,
1526 entity_identifier,
1527 message_name,
1528 error_message,
1529 last_update_date,
1530 last_updated_by,
1531 creation_date,
1532 created_by,
1533 last_update_login,
1534 request_id,
1535 program_application_id,
1536 program_id,
1537 program_update_date)
1538 SELECT transaction_id,
1539 mtl_system_items_interface_s.nextval,
1540 NULL,
1541 NULL,
1542 G_ENTITY_PG_TAB,
1543 G_BO_IDENTIFIER_PG,
1544 G_ENTITY_PG,
1545 G_MESSAGE_NAME,
1546 G_MESSAGE_TEXT,
1547 Nvl(last_update_date, SYSDATE),
1548 Nvl(last_updated_by, G_USER_ID),
1549 Nvl(creation_date, SYSDATE),
1550 Nvl(created_by, G_USER_ID),
1551 Nvl(last_update_login, G_USER_ID),
1552 G_REQUEST_ID,
1553 Nvl(program_application_id, G_PROG_APPL_ID),
1554 Nvl(program_id, G_PROGRAM_ID),
1555 Nvl(program_update_date, SYSDATE)
1556 FROM ego_pages_interface epi
1557 WHERE EXISTS (SELECT 1
1558 FROM EGO_PAGES_B
1559 WHERE internal_name = epi.internal_name
1560 AND classification_code = epi.classification_code)
1561 AND transaction_type = G_OPR_CREATE
1562 AND transaction_id IS NOT NULL
1563 AND process_status = G_PROCESS_RECORD
1564 AND ( ( G_SET_PROCESS_ID IS NULL )
1565 OR ( set_process_id = G_SET_PROCESS_ID ) );
1566
1567 UPDATE ego_pages_interface epi
1568 SET process_status = G_ERROR_RECORD,
1569 last_updated_by = G_USER_ID,
1570 last_update_date = SYSDATE,
1571 last_update_login = G_LOGIN_ID
1572 WHERE EXISTS (SELECT 1
1573 FROM EGO_PAGES_B
1574 WHERE internal_name = epi.internal_name
1575 AND classification_code = epi.classification_code)
1576 AND transaction_type = G_OPR_CREATE
1577 AND transaction_id IS NOT NULL
1578 AND process_status = G_PROCESS_RECORD
1579 AND ( ( G_SET_PROCESS_ID IS NULL )
1580 OR ( set_process_id = G_SET_PROCESS_ID ) );
1581
1582 lv_smt := 2;
1583
1584 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
1585
1586 /* Update the interface records with process_status 3 and insert into
1587 MTL_INTERFACE_ERRORS if SEQUENCE IS already existing for the ICC*/
1588 G_MESSAGE_NAME := 'EGO_PG_SEQ_DUP';
1589
1590 fnd_message.Set_name(G_EGO_APPLICATION_ID, 'EGO_PG_SEQ_DUP');
1591
1592 G_MESSAGE_TEXT := fnd_message.get;
1593
1594 INSERT INTO mtl_interface_errors
1595 (transaction_id,
1596 unique_id,
1597 organization_id,
1598 column_name,
1599 table_name,
1600 bo_identifier,
1601 entity_identifier,
1602 message_name,
1603 error_message,
1604 last_update_date,
1605 last_updated_by,
1606 creation_date,
1607 created_by,
1608 last_update_login,
1609 request_id,
1610 program_application_id,
1611 program_id,
1612 program_update_date)
1613 SELECT transaction_id,
1614 mtl_system_items_interface_s.nextval,
1615 NULL,
1616 NULL,
1617 G_ENTITY_PG_TAB,
1618 G_BO_IDENTIFIER_PG,
1619 G_ENTITY_PG,
1620 G_MESSAGE_NAME,
1621 G_MESSAGE_TEXT,
1622 Nvl(last_update_date, SYSDATE),
1623 Nvl(last_updated_by, G_USER_ID),
1624 Nvl(creation_date, SYSDATE),
1625 Nvl(created_by, G_USER_ID),
1626 Nvl(last_update_login, G_USER_ID),
1627 G_REQUEST_ID,
1628 Nvl(program_application_id, G_PROG_APPL_ID),
1629 Nvl(program_id, G_PROGRAM_ID),
1630 Nvl(program_update_date, SYSDATE)
1631 FROM ego_pages_interface epi
1632 WHERE EXISTS (SELECT 1
1633 FROM ego_pages_b
1634 WHERE classification_code = epi.classification_code
1635 AND SEQUENCE = epi.SEQUENCE
1636 AND page_id <> nvl(epi.page_id,-1))
1637 AND transaction_id IS NOT NULL
1638 AND process_status = G_PROCESS_RECORD
1639 AND ( ( G_SET_PROCESS_ID IS NULL )
1640 OR ( set_process_id = G_SET_PROCESS_ID ) );
1641
1642 UPDATE ego_pages_interface epi
1643 SET process_status = G_ERROR_RECORD,
1644 last_updated_by = G_USER_ID,
1645 last_update_date = SYSDATE,
1646 last_update_login = G_LOGIN_ID
1647 WHERE EXISTS (SELECT 1
1648 FROM ego_pages_b
1649 WHERE classification_code = epi.classification_code
1650 AND SEQUENCE = epi.SEQUENCE
1651 AND page_id <> nvl(epi.page_id,-1))
1652 AND transaction_id IS NOT NULL
1653 AND process_status = G_PROCESS_RECORD
1654 AND ( ( G_SET_PROCESS_ID IS NULL )
1655 OR ( set_process_id = G_SET_PROCESS_ID ) );
1656
1657 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Bulk_validate_pages');
1658 EXCEPTION
1659 WHEN OTHERS THEN
1660 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Bulk_validate_pages Exception when others'||SQLERRM);
1661
1662 x_return_status := G_RET_STS_UNEXP_ERROR;
1663
1664 x_return_msg := 'ego_pages_bulkload_pvt.Bulk_validate_pages - '||SQLERRM;
1665
1666 RETURN;
1667 END Bulk_validate_pages;
1668
1669 /*This procedure is used for the bulk validation for the page entries.
1670 Used in the interface flow.
1671 x_return_status OUT NOCOPY parameter that returns the status*/
1672 PROCEDURE bulk_validate_pg_entries (x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
1673 IS
1674 lv_smt NUMBER; --Statement counter
1675 lv_proc VARCHAR2(30) := 'bulk_validate_pg_entries';
1676 BEGIN
1677 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entered bulk_validate_pg_entries');
1678
1679 x_return_status := G_RET_STS_SUCCESS;
1680
1681 Value_to_id_pg_entries(x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
1682
1683 Construct_pg_entries(x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
1684
1685 lv_smt := 1;
1686
1687 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
1688
1689 /* Update the interface records with process_status 3 and insert into
1690 MTL_INTERFACE_ERRORS if flow is UPDATE or DELETE and the page entry does not exist*/
1691 G_MESSAGE_NAME := 'EGO_PG_ENT_NOT_EXIST';
1692
1693 fnd_message.Set_name(G_EGO_APPLICATION_ID, 'EGO_PG_ENT_NOT_EXIST');
1694
1695 G_MESSAGE_TEXT := fnd_message.get;
1696
1697 INSERT INTO mtl_interface_errors
1698 (transaction_id,
1699 unique_id,
1700 organization_id,
1701 column_name,
1702 table_name,
1703 bo_identifier,
1704 entity_identifier,
1705 message_name,
1706 error_message,
1707 last_update_date,
1708 last_updated_by,
1709 creation_date,
1710 created_by,
1711 last_update_login,
1712 request_id,
1713 program_application_id,
1714 program_id,
1715 program_update_date)
1716 SELECT transaction_id,
1717 mtl_system_items_interface_s.nextval,
1718 NULL,
1719 NULL,
1720 G_ENTITY_ENT_TAB,
1721 G_BO_IDENTIFIER_PG,
1722 G_ENTITY_ENT,
1723 G_MESSAGE_NAME,
1724 G_MESSAGE_TEXT,
1725 Nvl(last_update_date, SYSDATE),
1726 Nvl(last_updated_by, G_USER_ID),
1727 Nvl(creation_date, SYSDATE),
1728 Nvl(created_by, G_USER_ID),
1729 Nvl(last_update_login, G_USER_ID),
1730 G_REQUEST_ID,
1731 Nvl(program_application_id, G_PROG_APPL_ID),
1732 Nvl(program_id, G_PROGRAM_ID),
1733 Nvl(program_update_date, SYSDATE)
1734 FROM ego_page_entries_interface epei
1735 WHERE NOT EXISTS (SELECT 1
1736 FROM ego_page_entries_b
1737 WHERE page_id = epei.page_id
1738 AND association_id = epei.old_association_id
1739 AND classification_code = epei.classification_code)
1740 AND transaction_type <> G_OPR_CREATE
1741 AND transaction_id IS NOT NULL
1742 AND process_status = G_PROCESS_RECORD
1743 AND ( ( G_SET_PROCESS_ID IS NULL )
1744 OR ( set_process_id = G_SET_PROCESS_ID ) );
1745
1746 UPDATE ego_page_entries_interface epi
1747 SET process_status = G_ERROR_RECORD,
1748 last_updated_by = G_USER_ID,
1749 last_update_date = SYSDATE,
1750 last_update_login = G_LOGIN_ID
1751 WHERE NOT EXISTS (SELECT 1
1752 FROM ego_page_entries_b
1753 WHERE page_id = epi.page_id
1754 AND association_id = epi.old_association_id
1755 AND classification_code = epi.classification_code)
1756 AND transaction_type <> G_OPR_CREATE
1757 AND transaction_id IS NOT NULL
1758 AND process_status = G_PROCESS_RECORD
1759 AND ( ( G_SET_PROCESS_ID IS NULL )
1760 OR ( set_process_id = G_SET_PROCESS_ID ) );
1761
1762 lv_smt := 2;
1763
1764 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
1765
1766 /* Update the interface records with process_status 3 and insert into
1767 MTL_INTERFACE_ERRORS if SEQ already exists in the page*/
1768 G_MESSAGE_NAME := 'EGO_PAGE_ENTRY_SAME_SEQ';
1769
1770 fnd_message.Set_name(G_EGO_APPLICATION_ID, 'EGO_PAGE_ENTRY_SAME_SEQ');
1771
1772 G_MESSAGE_TEXT := fnd_message.get;
1773
1774 INSERT INTO mtl_interface_errors
1775 (transaction_id,
1776 unique_id,
1777 organization_id,
1778 column_name,
1779 table_name,
1780 bo_identifier,
1781 entity_identifier,
1782 message_name,
1783 error_message,
1784 last_update_date,
1785 last_updated_by,
1786 creation_date,
1787 created_by,
1788 last_update_login,
1789 request_id,
1790 program_application_id,
1791 program_id,
1792 program_update_date)
1793 SELECT transaction_id,
1794 mtl_system_items_interface_s.nextval,
1795 NULL,
1796 NULL,
1797 G_ENTITY_ENT_TAB,
1798 G_BO_IDENTIFIER_PG,
1799 G_ENTITY_ENT,
1800 G_MESSAGE_NAME,
1801 G_MESSAGE_TEXT,
1802 Nvl(last_update_date, SYSDATE),
1803 Nvl(last_updated_by, G_USER_ID),
1804 Nvl(creation_date, SYSDATE),
1805 Nvl(created_by, G_USER_ID),
1806 Nvl(last_update_login, G_USER_ID),
1807 G_REQUEST_ID,
1808 Nvl(program_application_id, G_PROG_APPL_ID),
1809 Nvl(program_id, G_PROGRAM_ID),
1810 Nvl(program_update_date, SYSDATE)
1811 FROM ego_page_entries_interface epi
1812 WHERE EXISTS (SELECT 1
1813 FROM ego_page_entries_b
1814 WHERE page_id = epi.page_id
1815 AND sequence = epi.sequence)
1816 AND transaction_id IS NOT NULL
1817 AND transaction_type = G_OPR_CREATE /*Added this for bug 9733398*/
1818 AND process_status = G_PROCESS_RECORD
1819 AND ( ( G_SET_PROCESS_ID IS NULL )
1820 OR ( set_process_id = G_SET_PROCESS_ID ) );
1821
1822 UPDATE ego_page_entries_interface epi
1823 SET process_status = G_ERROR_RECORD,
1824 last_updated_by = G_USER_ID,
1825 last_update_date = SYSDATE,
1826 last_update_login = G_LOGIN_ID
1827 WHERE EXISTS (SELECT 1
1828 FROM ego_page_entries_b
1829 WHERE page_id = epi.page_id
1830 AND sequence = epi.sequence)
1831 AND transaction_id IS NOT NULL
1832 AND transaction_type = G_OPR_CREATE /*Added this for bug 9733398*/
1833 AND process_status = G_PROCESS_RECORD
1834 AND ( ( G_SET_PROCESS_ID IS NULL )
1835 OR ( set_process_id = G_SET_PROCESS_ID ) );
1836
1837 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit bulk_validate_pg_entries');
1838 EXCEPTION
1839 WHEN OTHERS THEN
1840 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'bulk_validate_pg_entries Exception when others'||SQLERRM);
1841
1842 x_return_status := G_RET_STS_UNEXP_ERROR;
1843
1844 x_return_status := G_RET_STS_UNEXP_ERROR;
1845
1846 x_return_msg := 'ego_pages_bulkload_pvt.bulk_validate_pg_entries - '||SQLERRM;
1847
1848 RETURN;
1849 END bulk_validate_pg_entries;
1850
1851
1852 /*This procedure is used to handle the additional validations for Pages.
1853 Used in the API flow.
1854 p_pg_tbl IN OUT NOCOPY Pages table
1855 x_return_status OUT NOCOPY parameter that returns the status*/
1856 PROCEDURE Additional_pg_validations (p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
1857 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
1858 IS
1859 lv_smt NUMBER; --Statement counter
1860 lv_flag VARCHAR2(1);
1861 lv_proc VARCHAR2(30) := 'Additional_pg_validations';
1862 BEGIN
1863 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering Additional_pg_validations');
1864
1865 x_return_status := G_RET_STS_SUCCESS;
1866
1867 FOR i IN p_pg_tbl.FIRST.. p_pg_tbl.LAST LOOP
1868 IF (p_pg_tbl(i).process_status = G_PROCESS_RECORD) THEN
1869
1870 lv_smt := 1;
1871 /*For the update or delete flow if the page is not existing in the system then Error out*/
1872 IF(p_pg_tbl(i).transaction_type <> G_OPR_CREATE AND p_pg_tbl(i).page_id IS NULL) THEN
1873 x_return_status := G_RET_STS_ERROR;
1874
1875 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page is not defined in the system');
1876
1877 error_handler.Add_error_message(p_message_name => 'EGO_PG_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1878 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1879 p_row_identifier => p_pg_tbl(i).transaction_id,
1880 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
1881 END IF;
1882
1883 lv_smt := 2;
1884 /*Error OUT if the same sequence exists in the system for the ICC*/
1885 BEGIN
1886 SELECT 'Y' INTO lv_flag
1887 FROM ego_pages_b
1888 WHERE classification_code = p_pg_tbl(i).classification_code
1889 AND SEQUENCE = p_pg_tbl(i).SEQUENCE
1890 AND page_id <> NVL(p_pg_tbl(i).page_id,-1);
1891
1892 x_return_status := G_RET_STS_ERROR;
1893
1894 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Sequence exists in the system');
1895
1896 error_handler.Add_error_message(p_message_name => 'EGO_PG_SEQ_DUP',p_application_id => G_EGO_APPLICATION_ID,
1897 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1898 p_row_identifier => p_pg_tbl(i).transaction_id,
1899 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
1900 EXCEPTION
1901 WHEN NO_DATA_FOUND THEN
1902 NULL;
1903 WHEN OTHERS THEN
1904 x_return_status := G_RET_STS_UNEXP_ERROR;
1905
1906 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Additional_pg_validations exception when others smt 2');
1907
1908 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1909 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1910 p_row_identifier => p_pg_tbl(i).transaction_id,
1911 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
1912
1913 x_return_msg := 'ego_pages_bulkload_pvt.Additional_pg_validations smt 2 - '||SQLERRM;
1914
1915 RETURN;
1916 END;
1917
1918 lv_smt := 3;
1919 /*Convert transaction type to upper case*/
1920 SELECT Upper(p_pg_tbl(i).transaction_type)
1921 INTO p_pg_tbl(i).transaction_type
1922 FROM dual;
1923 /*check for invalid transaction type for PG*/
1924 IF (p_pg_tbl(i).transaction_type = G_OPR_CREATE
1925 OR p_pg_tbl(i).transaction_type = G_OPR_UPDATE
1926 OR p_pg_tbl(i).transaction_type = G_OPR_DELETE
1927 OR p_pg_tbl(i).transaction_type = G_OPR_SYNC) THEN
1928 NULL;
1929 ELSE
1930 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Transaction type is invalid for PG');
1931
1932 p_pg_tbl(i).process_status := G_ERROR_RECORD;
1933
1934 x_return_status := G_RET_STS_ERROR;
1935
1936 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Invalid transaction type for PG');
1937
1938 error_handler.Add_error_message(p_message_name => 'EGO_TRANS_TYPE_INVALID',p_application_id => 'EGO',
1939 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
1940 p_row_identifier => p_pg_tbl(i).transaction_id,
1941 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
1942 END IF;
1943 END IF;
1944 END LOOP;
1945
1946 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Additional_pg_validations');
1947 EXCEPTION
1948 WHEN OTHERS THEN
1949 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Additional_pg_validations Exception when others'||SQLERRM);
1950
1951 x_return_status := G_RET_STS_UNEXP_ERROR;
1952
1953 x_return_msg := 'ego_pages_bulkload_pvt.Additional_pg_validations - '||SQLERRM;
1954
1955 RETURN;
1956 END Additional_pg_validations;
1957
1958 /*This procedure is used to handle the additional validations for Page Entries
1959 Used in the API flow.
1960 p_ent_tbl IN OUT NOCOPY Page Entries table
1961 x_return_status OUT NOCOPY parameter that returns the status*/
1962 PROCEDURE Additional_ent_validations (p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
1963 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
1964 IS
1965 lv_smt NUMBER; --Statement counter
1966 lv_flag VARCHAR2(1);
1967 lv_proc VARCHAR2(30) := 'Additional_ent_validations';
1968 BEGIN
1969 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering Additional_ent_validations');
1970
1971 x_return_status := G_RET_STS_SUCCESS;
1972
1973 FOR i IN p_ent_tbl.FIRST.. p_ent_tbl.LAST LOOP
1974 IF (p_ent_tbl(i).process_status = G_PROCESS_RECORD) THEN
1975 /*Error out if flow is UPDATE or DELETE and the page entry does not exist*/
1976 IF (p_ent_tbl(i).transaction_type <> G_OPR_CREATE) THEN
1977 BEGIN
1978 SELECT 'Y' INTO lv_flag
1979 FROM ego_page_entries_b
1980 WHERE page_id = p_ent_tbl(i).page_id
1981 AND association_id = p_ent_tbl(i).old_association_id
1982 AND classification_code = p_ent_tbl(i).classification_code;
1983 EXCEPTION
1984 WHEN NO_DATA_FOUND THEN
1985 x_return_status := G_RET_STS_ERROR;
1986
1987 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Sequence exists in the system');
1988
1989 error_handler.Add_error_message(p_message_name => 'EGO_PG_ENT_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
1990 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
1991 p_row_identifier => p_ent_tbl(i).transaction_id,
1992 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
1993 WHEN OTHERS THEN
1994 x_return_status := G_RET_STS_UNEXP_ERROR;
1995
1996 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Additional_ent_validations exception when others smt 1');
1997
1998 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
1999 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
2000 p_row_identifier => p_ent_tbl(i).transaction_id,
2001 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2002
2003 x_return_msg := 'ego_pages_bulkload_pvt.Additional_ent_validations smt 1- '||SQLERRM;
2004
2005 RETURN;
2006 END;
2007 END IF;
2008
2009 lv_smt := 2;
2010 /*Error if SEQ already exists in the page*/
2011 BEGIN
2012 SELECT 'Y' INTO lv_flag
2013 FROM ego_page_entries_b
2014 WHERE page_id = p_ent_tbl(i).page_id
2015 AND sequence = p_ent_tbl(i).sequence;
2016
2017 IF (lv_flag = 'Y') THEN
2018 x_return_status := G_RET_STS_ERROR;
2019
2020 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Sequence exists in the system for the same page');
2021
2022 error_handler.Add_error_message(p_message_name => 'EGO_PAGE_ENTRY_SAME_SEQ',p_application_id => G_EGO_APPLICATION_ID,
2023 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2024 p_row_identifier => p_ent_tbl(i).transaction_id,
2025 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2026 END IF;
2027 EXCEPTION
2028 WHEN NO_DATA_FOUND THEN
2029 NULL;
2030 WHEN OTHERS THEN
2031 x_return_status := G_RET_STS_UNEXP_ERROR;
2032
2033 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Additional_ent_validations exception when others smt 2');
2034
2035 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
2036 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
2037 p_row_identifier => p_ent_tbl(i).transaction_id,
2038 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2039
2040 x_return_msg := 'ego_pages_bulkload_pvt.Additional_ent_validations smt 2 - '||SQLERRM;
2041
2042 RETURN;
2043 END;
2044
2045 lv_smt := 3;
2046 /*Convert transaction type to upper case*/
2047 SELECT Upper(p_ent_tbl(i).transaction_type)
2048 INTO p_ent_tbl(i).transaction_type
2049 FROM dual;
2050 /*check for invalid transaction type for PG*/
2051 IF (p_ent_tbl(i).transaction_type = G_OPR_CREATE
2052 OR p_ent_tbl(i).transaction_type = G_OPR_UPDATE
2053 OR p_ent_tbl(i).transaction_type = G_OPR_DELETE
2054 OR p_ent_tbl(i).transaction_type = G_OPR_SYNC) THEN
2055 NULL;
2056 ELSE
2057 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Transaction type is invalid for PG Entries');
2058
2059 p_ent_tbl(i).process_status := G_ERROR_RECORD;
2060
2061 x_return_status := G_RET_STS_ERROR;
2062
2063 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Invalid transaction type for PG Entries');
2064
2065 error_handler.Add_error_message(p_message_name => 'EGO_TRANS_TYPE_INVALID',p_application_id => 'EGO',
2066 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2067 p_row_identifier => p_ent_tbl(i).transaction_id,
2068 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2069 END IF;
2070 END IF;
2071 END LOOP;
2072
2073 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Additional_ent_validations');
2074 EXCEPTION
2075 WHEN OTHERS THEN
2076 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Additional_ent_validations Exception when others'||SQLERRM);
2077
2078 x_return_status := G_RET_STS_UNEXP_ERROR;
2079
2080 x_return_msg := 'ego_pages_bulkload_pvt.Additional_ent_validations - '||SQLERRM;
2081
2082 RETURN;
2083 END Additional_ent_validations;
2084
2085
2086 /*This procedure is used to handle the common validations pertaining to Pages.
2087 Used in the both the flows.
2088 p_pg_tbl IN OUT NOCOPY Pages table
2089 x_return_status OUT NOCOPY parameter that returns the status*/
2090 PROCEDURE Common_pg_validations (p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
2091 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
2092 IS
2093 lv_smt NUMBER; --Statement counter
2094 lv_proc VARCHAR2(30) := 'Common_pg_validations';
2095 BEGIN
2096 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entered Common_pg_validations');
2097
2098 x_return_status := G_RET_STS_SUCCESS;
2099
2100 FOR i IN p_pg_tbl.FIRST.. p_pg_tbl.LAST LOOP
2101 IF (p_pg_tbl(i).process_status = G_PROCESS_RECORD) THEN
2102 /*Error OUT if all the mandatory columns are not populated for the create flow*/
2103 IF (p_pg_tbl(i).transaction_type = G_OPR_CREATE
2104 AND (p_pg_tbl(i).display_name IS NULL
2105 OR p_pg_tbl(i).internal_name IS NULL
2106 OR p_pg_tbl(i).classification_code IS NULL
2107 OR p_pg_tbl(i).data_level IS NULL
2108 OR p_pg_tbl(i).sequence IS NULL)) THEN
2109 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_pg_tbl(i).transaction_id||' (PG,ICC) = ('
2110 ||p_pg_tbl(i).internal_name||','||p_pg_tbl(i).classification_code||'). ' ||'Mandatory columns for create flow are not populated');
2111
2112 p_pg_tbl(i).process_status := G_ERROR_RECORD;
2113
2114 error_handler.Add_error_message(p_message_name => 'EGO_PG_MANDATORY',p_application_id => G_EGO_APPLICATION_ID,
2115 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2116 p_row_identifier => p_pg_tbl(i).transaction_id,
2117 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2118
2119 END IF;
2120
2121 /*Error out if the data level populated is invalid*/
2122 IF ((p_pg_tbl(i).data_level = G_DL_ITEM_LEVEL
2123 OR p_pg_tbl(i).data_level = G_DL_ITEM_REV_LEVEL
2124 OR p_pg_tbl(i).data_level = G_DL_ITEM_ORG
2125 OR p_pg_tbl(i).data_level = G_DL_ITEM_SUP
2126 OR p_pg_tbl(i).data_level = G_DL_ITEM_SUP_SITE
2127 OR p_pg_tbl(i).data_level = G_DL_ITEM_SUP_SITE_ORG)) THEN
2128 NULL;
2129 ELSIF (p_pg_tbl(i).transaction_type <> G_OPR_DELETE) THEN
2130 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_pg_tbl(i).transaction_id||' (PG,DL) = ('
2131 ||p_pg_tbl(i).internal_name||','||p_pg_tbl(i).data_level||'). ' ||'Data Level passed is invalid');
2132
2133 p_pg_tbl(i).process_status := G_ERROR_RECORD;
2134
2135 error_handler.Add_error_message(p_message_name => 'EGO_DL_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
2136 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2137 p_row_identifier => p_pg_tbl(i).transaction_id,
2138 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2139 END IF;
2140
2141 /*Error out if ICC id or name passed is invalid populated is invalid*/
2142 IF (p_pg_tbl(i).classification_code IS NULL OR p_pg_tbl(i).classification_name IS NULL) THEN
2143 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_pg_tbl(i).transaction_id||' (PG,ICC) = ('
2144 ||p_pg_tbl(i).internal_name||','||p_pg_tbl(i).classification_code||'). ' ||'ICC code or name passed is invalid');
2145
2146 p_pg_tbl(i).process_status := G_ERROR_RECORD;
2147
2148 error_handler.Add_error_message(p_message_name => 'EGO_PG_ICC_INVALID',p_application_id => G_EGO_APPLICATION_ID,
2149 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2150 p_row_identifier => p_pg_tbl(i).transaction_id,
2151 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2152 END IF;
2153 END IF;
2154 END LOOP;
2155 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Common_pg_validations');
2156 EXCEPTION
2157 WHEN OTHERS THEN
2158 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Common_pg_validations Exception when others'||SQLERRM);
2159
2160 x_return_status := G_RET_STS_UNEXP_ERROR;
2161
2162 x_return_msg := 'ego_pages_bulkload_pvt.Common_pg_validations - '||SQLERRM;
2163
2164 RETURN;
2165 END Common_pg_validations;
2166
2167 /*This procedure is used to handle the common validations pertaining to page entries.
2168 Used in both flows.
2169 p_ent_tbl IN OUT NOCOPY Page Entries table
2170 x_return_status OUT NOCOPY parameter that returns the status*/
2171 PROCEDURE Common_ent_validations (p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
2172 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
2173 IS
2174 lv_smt NUMBER; --Statement counter
2175 lv_proc VARCHAR2(30) := 'Common_ent_validations';
2176 BEGIN
2177 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entered Common_ent_validations');
2178
2179 x_return_status := G_RET_STS_SUCCESS;
2180
2181 FOR i IN p_ent_tbl.FIRST.. p_ent_tbl.LAST LOOP
2182 IF (p_ent_tbl(i).process_status = G_PROCESS_RECORD) THEN
2183 /*Error out if the page id or the internal name or the classification code is null*/
2184 lv_smt := 1;
2185
2186 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
2187
2188 IF (p_ent_tbl(i).page_id IS NULL OR p_ent_tbl(i).internal_name IS NULL OR p_ent_tbl(i).classification_code IS NULL) THEN
2189 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG) = ('
2190 ||p_ent_tbl(i).internal_name||'). ' ||'Page does not exist');
2191
2192 p_ent_tbl(i).process_status := G_ERROR_RECORD;
2193
2194 error_handler.Add_error_message(p_message_name => 'EGO_PG_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
2195 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2196 p_row_identifier => p_ent_tbl(i).transaction_id,
2197 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2198 END IF;
2199
2200 /*Error out if the classification code or the classification name is null*/
2201 lv_smt := 2;
2202
2203 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
2204
2205 IF (p_ent_tbl(i).classification_code IS NULL OR p_ent_tbl(i).classification_name IS NULL) THEN
2206 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,ICC) = ('
2207 ||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).classification_code||'). ' ||'ICC does not exist');
2208
2209 p_ent_tbl(i).process_status := G_ERROR_RECORD;
2210
2211 error_handler.Add_error_message(p_message_name => 'EGO_PG_ICC_INVALID',p_application_id => G_EGO_APPLICATION_ID,
2212 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2213 p_row_identifier => p_ent_tbl(i).transaction_id,
2214 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2215 END IF;
2216
2217 /*Error out if the old_association_id or the old_attr_group_id or the old_attr_group_name is null*/
2218 lv_smt := 3;
2219
2220 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
2221
2222 IF (p_ent_tbl(i).old_association_id IS NULL AND (p_ent_tbl(i).old_attr_group_id IS NOT NULL OR p_ent_tbl(i).old_attr_group_name IS NOT NULL)) THEN
2223 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,AG) = ('
2224 ||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).old_association_id||'). ' ||'Old Association does not exist');
2225
2226 p_ent_tbl(i).process_status := G_ERROR_RECORD;
2227
2228 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
2229 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2230 p_row_identifier => p_ent_tbl(i).transaction_id,
2231 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2232 END IF;
2233
2234 /*Error out if the new_association_id or the new_attr_group_id or the new_attr_group_name is null*/
2235 lv_smt := 4;
2236
2237 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
2238
2239 IF (p_ent_tbl(i).new_association_id IS NULL AND (p_ent_tbl(i).new_attr_group_id IS NOT NULL OR p_ent_tbl(i).new_attr_group_name IS NOT NULL)) THEN
2240 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,ICC) = ('
2241 ||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).new_association_id||'). ' ||'New association does not exist');
2242
2243 p_ent_tbl(i).process_status := G_ERROR_RECORD;
2244
2245 error_handler.Add_error_message(p_message_name => 'EGO_PG_ASSOC_NOT_EXIST',p_application_id => G_EGO_APPLICATION_ID,
2246 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2247 p_row_identifier => p_ent_tbl(i).transaction_id,
2248 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2249 END IF;
2250
2251 /*Error out if the mandatory columns for the create flow are not present*/
2252 IF (p_ent_tbl(i).transaction_type = G_OPR_CREATE AND (p_ent_tbl(i).page_id IS NULL
2253 OR p_ent_tbl(i).old_association_id IS NULL
2254 OR p_ent_tbl(i).sequence IS NULL
2255 OR p_ent_tbl(i).classification_code IS NULL)) THEN
2256 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,ICC) = ('
2257 ||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).classification_code||'). ' ||'Mandatory columns for Page Entry creation is not populated');
2258
2259 p_ent_tbl(i).process_status := G_ERROR_RECORD;
2260
2261 error_handler.Add_error_message(p_message_name => 'EGO_PG_ENT_MANDATORY',p_application_id => G_EGO_APPLICATION_ID,
2262 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2263 p_row_identifier => p_ent_tbl(i).transaction_id,
2264 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2265 END IF;
2266
2267
2268 /*Checks whether the attribute group is already associated to the page.*/
2269 lv_smt := 5;
2270
2271 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
2272
2273 IF (p_ent_tbl(i).transaction_type = G_OPR_CREATE
2274 AND p_ent_tbl(i).old_association_id IS NOT NULL) THEN
2275 DECLARE
2276 lv_count NUMBER;
2277 BEGIN
2278 SELECT COUNT(1) INTO lv_count
2279 FROM EGO_PAGE_ENTRIES_B
2280 WHERE page_id = p_ent_tbl(i).page_id
2281 AND association_id = p_ent_tbl(i).old_association_id;
2282
2283 IF (lv_count = 1) THEN
2284 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_ent_tbl(i).transaction_id||' (PG,AG) = ('
2285 ||p_ent_tbl(i).internal_name||','||p_ent_tbl(i).old_association_id||'). ' ||'Cannot associate the same AG twice.');
2286
2287 p_ent_tbl(i).process_status := G_ERROR_RECORD;
2288
2289 error_handler.Add_error_message(p_message_name => 'EGO_ATTRIBUTE_GROUP_CONSTRAINT',p_application_id => G_EGO_APPLICATION_ID,
2290 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2291 p_row_identifier => p_ent_tbl(i).transaction_id,
2292 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2293 END IF;
2294 EXCEPTION
2295 WHEN OTHERS THEN
2296 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exception'||SQLERRM);
2297
2298 p_ent_tbl(i).process_status := G_RET_STS_UNEXP_ERROR;
2299
2300 error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => G_EGO_APPLICATION_ID,
2301 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_UNEXP_ERROR,
2302 p_row_identifier => p_ent_tbl(i).transaction_id,
2303 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2304
2305 x_return_msg := 'ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exception'||SQLERRM;
2306
2307 RETURN;
2308 END;
2309 END IF;
2310
2311 END IF;
2312 END LOOP;
2313 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Common_ent_validations');
2314 EXCEPTION
2315 WHEN OTHERS THEN
2316 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Common_ent_validations Exception when others'||SQLERRM);
2317
2318 x_return_status := G_RET_STS_UNEXP_ERROR;
2319
2320 x_return_msg := 'ego_pages_bulkload_pvt.Common_ent_validations - '||SQLERRM;
2321
2322 RETURN;
2323 END Common_ent_validations;
2324
2325 /*This is the main procedure that is called by the interface flow.
2326 p_set_process_id IN set_process_id
2327 x_return_status OUT NOCOPY parameter that returns the status
2328 x_return_msg OUT NOCOPY parameter that returns the error message*/
2329 PROCEDURE import_pg_intf ( p_set_process_id IN VARCHAR2,
2330 x_return_status OUT NOCOPY VARCHAR2,
2331 x_return_msg OUT NOCOPY VARCHAR2)
2332 IS
2333 /*Cursor to load plsql table from ego_pages_interface table*/
2334 CURSOR c_pg IS
2335 SELECT *
2336 FROM ego_pages_interface
2337 WHERE process_status = G_PROCESS_RECORD
2338 AND (p_set_process_id IS NULL
2339 OR set_process_id = p_set_process_id);
2340 /*Cursor to load plsql table from ego_page_entries_interface table*/
2341 CURSOR c_ent IS
2342 SELECT *
2343 FROM ego_page_entries_interface
2344 WHERE process_status = G_PROCESS_RECORD
2345 AND (p_set_process_id IS NULL
2346 OR set_process_id = p_set_process_id);
2347
2348 l_ego_pg_tbl ego_metadata_pub.ego_pg_tbl;
2349 l_ego_ent_tbl ego_metadata_pub.ego_ent_tbl;
2350 lv_proc VARCHAR2(30) := 'import_pg_intf';
2351 BEGIN
2352 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entered import_pg_intf.');
2353
2354 x_return_status := G_RET_STS_SUCCESS;
2355
2356 error_handler.Set_bo_identifier(G_BO_IDENTIFIER_PG);
2357
2358 G_SET_PROCESS_ID := p_set_process_id;
2359
2360 Initialize(x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2361
2362 IF (G_PAGES_COUNT <> 0 OR G_PAGE_ENTRIES_COUNT <> 0) THEN
2363 Validate_transaction_type(x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2364
2365 IF (G_PAGES_COUNT <> 0) THEN
2366 Bulk_validate_pages(x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2367
2368 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Load and process PG');
2369
2370 /*Load PL/SQL tables for PG and then call processing method*/
2371 OPEN c_pg;
2372
2373 LOOP
2374 FETCH c_pg BULK COLLECT INTO l_ego_pg_tbl LIMIT 2000;
2375
2376 IF (l_ego_pg_tbl.COUNT <> 0) THEN
2377 Process_pg(p_pg_tbl => l_ego_pg_tbl, x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2378
2379 update_intf_pages(p_pg_tbl => l_ego_pg_tbl, x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2380
2381 COMMIT;
2382 END IF;
2383 EXIT WHEN l_ego_pg_tbl.COUNT < 2000;
2384 END LOOP;
2385
2386 CLOSE c_pg;
2387
2388 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'PG processed');
2389 END IF;
2390 IF (G_PAGE_ENTRIES_COUNT <> 0) THEN
2391 Bulk_validate_pg_entries(x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2392
2393 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Load and process Page Entries');
2394
2395 /*Load PL/SQL tables for Page Entries and then call processing method*/
2396 OPEN c_ent;
2397
2398 LOOP
2399 FETCH c_ent BULK COLLECT INTO l_ego_ent_tbl LIMIT 2000;
2400
2401 IF (l_ego_ent_tbl.COUNT <> 0) THEN
2402 Process_ent(p_ent_tbl => l_ego_ent_tbl, x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2403
2404 update_intf_pg_entries(p_ent_tbl => l_ego_ent_tbl, x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2405
2406 COMMIT;
2407 END IF;
2408 EXIT WHEN l_ego_ent_tbl.COUNT < 2000;
2409 END LOOP;
2410
2411 CLOSE c_ent;
2412
2413 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page Entries Processed');
2414 END IF;
2415 END IF;
2416
2417 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit import_pg_intf.');
2418 EXCEPTION
2419 WHEN OTHERS THEN
2420 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'import_pg_intf Exception when others'||SQLERRM);
2421
2422 x_return_status := G_RET_STS_UNEXP_ERROR;
2423
2424 x_return_msg := 'ego_pages_bulkload_pvt.import_pg_intf - '||SQLERRM;
2425
2426 RETURN;
2427 END import_pg_intf;
2428
2429
2430 /*This the main procedure called by the public API to create pages.
2431 p_pg_tbl IN OUT NOCOPY Pages table
2432 p_commit IN controls whether commit to be executed or not
2433 x_return_status OUT NOCOPY parameter that returns the status
2434 x_return_msg OUT NOCOPY parameter that returns the error message*/
2435 PROCEDURE Process_pages ( p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
2436 p_commit IN BOOLEAN DEFAULT true,
2437 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
2438 IS
2439 lv_proc VARCHAR2(30) := 'Process_pages';
2440 BEGIN
2441 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering Process_pages');
2442
2443 x_return_status := G_RET_STS_SUCCESS;
2444
2445 error_handler.Set_bo_identifier(G_BO_IDENTIFIER_PG);
2446
2447 G_COMMIT := p_commit;
2448
2449 IF (p_pg_tbl.COUNT <> 0) THEN
2450
2451 /*Sets the EGO_ITEM OBJECT_ID*/
2452 SELECT object_id
2453 INTO G_OBJECT_ID
2454 FROM fnd_objects
2455 WHERE obj_name = 'EGO_ITEM';
2456
2457 /*Calls Additional Validations*/
2458 Additional_pg_validations(p_pg_tbl, x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2459
2460 /*Send the PG table for processing*/
2461 Process_pg(p_pg_tbl, x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2462 END IF;
2463 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Process_pages');
2464 EXCEPTION
2465 WHEN OTHERS THEN
2466 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Process_pages Exception when others'||SQLERRM);
2467
2468 x_return_status := G_RET_STS_UNEXP_ERROR;
2469
2470 x_return_msg := 'ego_pages_bulkload_pvt.Process_pages - '||SQLERRM;
2471
2472 RETURN;
2473 END Process_pages;
2474
2475 /*This the main procedure called by the public API to create pages.
2476 p_ent_tbl IN OUT NOCOPY Pages Entries table
2477 p_commit IN controls whether commit to be executed or not
2478 x_return_status OUT NOCOPY parameter that returns the status
2479 x_return_msg OUT NOCOPY parameter that returns the error message*/
2480 PROCEDURE Process_pg_entries (p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
2481 p_commit IN BOOLEAN DEFAULT true,
2482 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
2483 IS
2484 lv_proc VARCHAR2(30) := 'Process_pg_entries';
2485 BEGIN
2486 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering Process_pg_entries');
2487
2488 x_return_status := G_RET_STS_SUCCESS;
2489
2490 error_handler.Set_bo_identifier(G_BO_IDENTIFIER_PG);
2491
2492 G_COMMIT := p_commit;
2493
2494 IF (p_ent_tbl.COUNT <> 0) THEN
2495 /*Sets the EGO_ITEM OBJECT_ID*/
2496 SELECT object_id
2497 INTO G_OBJECT_ID
2498 FROM fnd_objects
2499 WHERE obj_name = 'EGO_ITEM';
2500
2501 /*Calls Additional Validations*/
2502 Additional_ent_validations(p_ent_tbl, x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2503
2504 /*Send the Page Entries table for processing*/
2505 Process_ent(p_ent_tbl, x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2506 END IF;
2507 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Process_pg_entries');
2508 EXCEPTION
2509 WHEN OTHERS THEN
2510 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Process_pg_entries Exception when others'||SQLERRM);
2511
2512 x_return_status := G_RET_STS_UNEXP_ERROR;
2513
2514 x_return_msg := 'ego_pages_bulkload_pvt.Process_pg_entries - '||SQLERRM;
2515
2516 RETURN;
2517 END Process_pg_entries;
2518
2519 /*This procedure is used to process the Pages.
2520 Used by both the flows.
2521 p_pg_tbl IN OUT NOCOPY Pages table
2522 x_return_status OUT NOCOPY parameter that returns the status
2523 x_return_msg OUT NOCOPY parameter that returns the error message*/
2524 PROCEDURE Process_pg (p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
2525 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
2526 IS
2527 x_page_id NUMBER;
2528 x_errorcode NUMBER;
2529 x_msg_count NUMBER;
2530 x_msg_data VARCHAR2(2000);
2531 lv_count_pg NUMBER;
2532 lv_count_seq NUMBER;
2533 lv_proc VARCHAR2(30) := 'Process_pg';
2534 BEGIN
2535 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering Process_pg');
2536
2537 x_return_status := G_RET_STS_SUCCESS;
2538
2539 Common_pg_validations(p_pg_tbl => p_pg_tbl, x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2540
2541 handle_null_pg(p_pg_tbl => p_pg_tbl, x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2542
2543 FOR i IN p_pg_tbl.first..p_pg_tbl.last LOOP
2544 IF ( P_pg_tbl(i).process_status = G_PROCESS_RECORD
2545 AND P_pg_tbl(i).transaction_type = G_OPR_CREATE ) THEN
2546 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Create Page');
2547
2548 /*Check if the page with the internal name already exists. This repeated check is required*/
2549 SELECT count(1) INTO lv_count_pg
2550 FROM EGO_PAGES_B
2551 WHERE internal_name = P_pg_tbl(i).internal_name
2552 AND classification_code = P_pg_tbl(i).classification_code;
2553
2554 /*Check if the sequence for the page is already used. This repeated check is also required*/
2555 SELECT count(1) INTO lv_count_seq
2556 FROM ego_pages_b
2557 WHERE classification_code = P_pg_tbl(i).classification_code
2558 AND SEQUENCE = P_pg_tbl(i).SEQUENCE;
2559
2560 IF (lv_count_pg > 0) THEN
2561 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page already exists');
2562
2563 p_pg_tbl(i).process_status := G_ERROR_RECORD;
2564
2565 error_handler.Add_error_message(p_message_name => 'EGO_INTERNAL_NAME_EXISTS',p_application_id => G_EGO_APPLICATION_ID,
2566 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2567 p_row_identifier => p_pg_tbl(i).transaction_id,
2568 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2569 ELSIF (lv_count_seq > 0) THEN
2570 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page sequence already exists');
2571
2572 p_pg_tbl(i).process_status := G_ERROR_RECORD;
2573
2574 error_handler.Add_error_message(p_message_name => 'EGO_PG_SEQ_DUP',p_application_id => G_EGO_APPLICATION_ID,
2575 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2576 p_row_identifier => p_pg_tbl(i).transaction_id,
2577 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2578 ELSE
2579
2580 ego_ext_fwk_pub.Create_page ( p_api_version => 1.0,
2581 p_page_id => NULL,
2582 p_object_id => G_OBJECT_ID,
2583 p_classification_code => P_pg_tbl(i).classification_code,
2584 p_data_level => P_pg_tbl(i).data_level,
2585 p_internal_name => P_pg_tbl(i).internal_name,
2586 p_display_name => P_pg_tbl(i).display_name,
2587 p_description => P_pg_tbl(i).description,
2588 p_sequence => P_pg_tbl(i).SEQUENCE,
2589 p_init_msg_list => fnd_api.g_false,
2590 p_commit => fnd_api.g_false,
2591 x_page_id => x_page_id,
2592 x_return_status => x_return_status,
2593 x_errorcode => x_errorcode,
2594 x_msg_count => x_msg_count,
2595 x_msg_data => x_msg_data);
2596 IF (x_return_status = G_RET_STS_SUCCESS) THEN
2597 P_pg_tbl(i).page_id := x_page_id;
2598
2599 P_pg_tbl(i).process_status := G_SUCCESS_RECORD;
2600 ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2601 x_return_msg := x_msg_data;
2602
2603 RETURN;
2604 ELSE
2605 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Error in creating PG');
2606
2607 P_pg_tbl(i).process_status := G_ERROR_RECORD;
2608
2609 G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
2610 G_TOKEN_TABLE(1).Token_Value := G_ENTITY_PG;
2611 G_TOKEN_TABLE(2).Token_Name := 'Transaction_Type';
2612 G_TOKEN_TABLE(2).Token_Value := P_pg_tbl(i).transaction_type;
2613 G_TOKEN_TABLE(3).Token_Name := 'Package_Name';
2614 G_TOKEN_TABLE(3).Token_Value := 'ego_ext_fwk_pub';
2615 G_TOKEN_TABLE(4).Token_Name := 'Proc_Name';
2616 G_TOKEN_TABLE(4).Token_Value := 'Create_page';
2617
2618 error_handler.Add_error_message(p_message_name => 'EGO_ENTITY_API_FAILED',p_application_id => G_EGO_APPLICATION_ID,
2619 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2620 p_row_identifier => P_pg_tbl(i).transaction_id,
2621 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2622
2623 error_handler.Add_error_message(p_message_text => x_msg_data,p_application_id => G_EGO_APPLICATION_ID,
2624 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2625 p_row_identifier => P_pg_tbl(i).transaction_id,
2626 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2627
2628 G_TOKEN_TABLE.DELETE;
2629 END IF;
2630 END IF;
2631 ELSIF ( P_pg_tbl(i).process_status = G_PROCESS_RECORD
2632 AND P_pg_tbl(i).transaction_type = G_OPR_UPDATE ) THEN
2633 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Update Page');
2634
2635 ego_ext_fwk_pub.Update_page ( p_api_version => 1.0,
2636 p_page_id => P_pg_tbl(i).page_id,
2637 p_internal_name => P_pg_tbl(i).internal_name,
2638 p_display_name => P_pg_tbl(i).display_name,
2639 p_description => P_pg_tbl(i).description,
2640 p_sequence => P_pg_tbl(i).SEQUENCE,
2641 p_init_msg_list => fnd_api.g_false,
2642 p_commit => fnd_api.g_false,
2643 p_is_nls_mode => fnd_api.g_false,
2644 x_return_status => x_return_status,
2645 x_errorcode => x_errorcode,
2646 x_msg_count => x_msg_count,
2647 x_msg_data => x_msg_data);
2648
2649 IF (x_return_status = G_RET_STS_SUCCESS) THEN
2650 P_pg_tbl(i).process_status := G_SUCCESS_RECORD;
2651 ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2652 x_return_msg := x_msg_data;
2653
2654 RETURN;
2655 ELSE
2656 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Error in creating PG');
2657
2658 P_pg_tbl(i).process_status := G_ERROR_RECORD;
2659
2660 G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
2661 G_TOKEN_TABLE(1).Token_Value := G_ENTITY_PG;
2662 G_TOKEN_TABLE(2).Token_Name := 'Transaction_Type';
2663 G_TOKEN_TABLE(2).Token_Value := P_pg_tbl(i).transaction_type;
2664 G_TOKEN_TABLE(3).Token_Name := 'Package_Name';
2665 G_TOKEN_TABLE(3).Token_Value := 'ego_ext_fwk_pub';
2666 G_TOKEN_TABLE(4).Token_Name := 'Proc_Name';
2667 G_TOKEN_TABLE(4).Token_Value := 'Update_page';
2668
2669 error_handler.Add_error_message(p_message_name => 'EGO_ENTITY_API_FAILED',p_application_id => G_EGO_APPLICATION_ID,
2670 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2671 p_row_identifier => P_pg_tbl(i).transaction_id,
2672 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2673
2674 error_handler.Add_error_message(p_message_text => x_msg_data,p_application_id => G_EGO_APPLICATION_ID,
2675 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2676 p_row_identifier => P_pg_tbl(i).transaction_id,
2677 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2678
2679 G_TOKEN_TABLE.DELETE;
2680 END IF;
2681 ELSIF ( P_pg_tbl(i).process_status = G_PROCESS_RECORD
2682 AND P_pg_tbl(i).transaction_type = G_OPR_DELETE ) THEN
2683 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Delete Page');
2684
2685 ego_ext_fwk_pub.Delete_page ( p_api_version => 1.0,
2686 p_object_id => G_OBJECT_ID,
2687 p_classification_code => P_pg_tbl(i).classification_code,
2688 p_internal_name => P_pg_tbl(i).internal_name,
2689 p_init_msg_list => fnd_api.g_false,
2690 p_commit => fnd_api.g_false,
2691 x_return_status => x_return_status,
2692 x_errorcode => x_errorcode,
2693 x_msg_count => x_msg_count,
2694 x_msg_data => x_msg_data);
2695
2696 IF (x_return_status = G_RET_STS_SUCCESS) THEN
2697 P_pg_tbl(i).process_status := G_SUCCESS_RECORD;
2698 ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2699 x_return_msg := x_msg_data;
2700
2701 RETURN;
2702 ELSE
2703 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Error in creating PG');
2704
2705 P_pg_tbl(i).process_status := G_ERROR_RECORD;
2706
2707 G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
2708 G_TOKEN_TABLE(1).Token_Value := G_ENTITY_PG;
2709 G_TOKEN_TABLE(2).Token_Name := 'Transaction_Type';
2710 G_TOKEN_TABLE(2).Token_Value := P_pg_tbl(i).transaction_type;
2711 G_TOKEN_TABLE(3).Token_Name := 'Package_Name';
2712 G_TOKEN_TABLE(3).Token_Value := 'ego_ext_fwk_pub';
2713 G_TOKEN_TABLE(4).Token_Name := 'Proc_Name';
2714 G_TOKEN_TABLE(4).Token_Value := 'Delete_page';
2715
2716 error_handler.Add_error_message(p_message_name => 'EGO_ENTITY_API_FAILED',p_application_id => G_EGO_APPLICATION_ID,
2717 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2718 p_row_identifier => P_pg_tbl(i).transaction_id,
2719 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2720
2721 error_handler.Add_error_message(p_message_text => x_msg_data,p_application_id => G_EGO_APPLICATION_ID,
2722 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2723 p_row_identifier => P_pg_tbl(i).transaction_id,
2724 p_entity_code => G_ENTITY_PG,p_table_name => G_ENTITY_PG_TAB);
2725
2726 G_TOKEN_TABLE.DELETE;
2727 END IF;
2728
2729 END IF;
2730 END LOOP;
2731
2732 IF ( G_COMMIT = true AND G_FLOW_TYPE = G_EGO_MD_API) THEN
2733 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Commit Process_pg');
2734
2735 COMMIT;
2736 END IF;
2737
2738 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Process_pg');
2739 EXCEPTION
2740 WHEN OTHERS THEN
2741 x_return_status := G_RET_STS_UNEXP_ERROR;
2742
2743 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Process_pg Exception when others'||SQLERRM);
2744
2745 x_return_msg := 'ego_pages_bulkload_pvt.Process_pg - '||SQLERRM;
2746
2747 RETURN;
2748 END Process_pg;
2749
2750 /*This procedure is used to process the page entries.
2751 Used in both flows.
2752 p_ent_tbl IN OUT NOCOPY Page Entries table
2753 x_return_status OUT NOCOPY parameter that returns the status
2754 x_return_msg OUT NOCOPY parameter that returns the error message*/
2755 PROCEDURE Process_ent (p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
2756 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
2757 IS
2758 x_page_id NUMBER;
2759 x_errorcode NUMBER;
2760 x_msg_count NUMBER;
2761 x_msg_data VARCHAR2(2000);
2762 lv_count_ag NUMBER;
2763 lv_count_seq NUMBER;
2764 lv_proc VARCHAR2(30) := 'Process_ent';
2765 BEGIN
2766 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering Process_ent');
2767
2768 x_return_status := G_RET_STS_SUCCESS;
2769
2770 Common_ent_validations(p_ent_tbl => p_ent_tbl, x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2771
2772 handle_null_pg_entries(p_ent_tbl => p_ent_tbl, x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2773
2774 FOR i IN p_ent_tbl.first..p_ent_tbl.last LOOP
2775 IF ( P_ent_tbl(i).process_status = G_PROCESS_RECORD
2776 AND P_ent_tbl(i).transaction_type = G_OPR_CREATE ) THEN
2777 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Create Page Entity');
2778
2779 /*check if the same AG is being reassociated to the page. This repeat check is required*/
2780 SELECT count(1) INTO lv_count_ag
2781 FROM EGO_PAGE_ENTRIES_B
2782 WHERE page_id = P_ent_tbl(i).page_id
2783 AND classification_code = P_ent_tbl(i).classification_code
2784 AND association_id = P_ent_tbl(i).old_association_id;
2785
2786 /*check if the same sequence is already used. This repeat check is required.*/
2787 SELECT count(1) INTO lv_count_seq
2788 FROM ego_page_entries_b
2789 WHERE page_id = P_ent_tbl(i).page_id
2790 AND sequence = P_ent_tbl(i).sequence;
2791
2792 IF (lv_count_ag > 0) THEN
2793 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page Entry association already exists');
2794
2795 P_ent_tbl(i).process_status := G_ERROR_RECORD;
2796
2797 error_handler.Add_error_message(p_message_name => 'EGO_PG_AG_DUP',p_application_id => G_EGO_APPLICATION_ID,
2798 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2799 p_row_identifier => P_ent_tbl(i).transaction_id,
2800 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2801 ELSIF (lv_count_seq > 0) THEN
2802 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page Entry sequence already exists');
2803
2804 P_ent_tbl(i).process_status := G_ERROR_RECORD;
2805
2806 error_handler.Add_error_message(p_message_name => 'EGO_PAGE_ENTRY_SAME_SEQ',p_application_id => G_EGO_APPLICATION_ID,
2807 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2808 p_row_identifier => P_ent_tbl(i).transaction_id,
2809 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2810 ELSE
2811
2812 ego_ext_fwk_pub.Create_page_entry ( p_api_version => 1.0,
2813 p_page_id => P_ent_tbl(i).page_id,
2814 p_association_id => P_ent_tbl(i).old_association_id,
2815 p_sequence => P_ent_tbl(i).SEQUENCE,
2816 p_classification_code => P_ent_tbl(i).classification_code,
2817 p_init_msg_list => fnd_api.g_false,
2818 p_commit => fnd_api.g_false,
2819 x_return_status => x_return_status,
2820 x_errorcode => x_errorcode,
2821 x_msg_count => x_msg_count,
2822 x_msg_data => x_msg_data);
2823
2824 IF (x_return_status = G_RET_STS_SUCCESS) THEN
2825 P_ent_tbl(i).process_status := G_SUCCESS_RECORD;
2826 ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2827 x_return_msg := x_msg_data;
2828
2829 RETURN;
2830 ELSE
2831 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Error in creating PG');
2832
2833 P_ent_tbl(i).process_status := G_ERROR_RECORD;
2834
2835 G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
2836 G_TOKEN_TABLE(1).Token_Value := G_ENTITY_ENT;
2837 G_TOKEN_TABLE(2).Token_Name := 'Transaction_Type';
2838 G_TOKEN_TABLE(2).Token_Value := P_ent_tbl(i).transaction_type;
2839 G_TOKEN_TABLE(3).Token_Name := 'Package_Name';
2840 G_TOKEN_TABLE(3).Token_Value := 'ego_ext_fwk_pub';
2841 G_TOKEN_TABLE(4).Token_Name := 'Proc_Name';
2842 G_TOKEN_TABLE(4).Token_Value := 'Create_page_entry';
2843
2844 error_handler.Add_error_message(p_message_name => 'EGO_ENTITY_API_FAILED',p_application_id => G_EGO_APPLICATION_ID,
2845 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2846 p_row_identifier => P_ent_tbl(i).transaction_id,
2847 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2848
2849 error_handler.Add_error_message(p_message_text => x_msg_data,p_application_id => G_EGO_APPLICATION_ID,
2850 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2851 p_row_identifier => P_ent_tbl(i).transaction_id,
2852 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2853
2854 G_TOKEN_TABLE.DELETE;
2855 END IF;
2856 END IF;
2857 ELSIF ( P_ent_tbl(i).process_status = G_PROCESS_RECORD
2858 AND P_ent_tbl(i).transaction_type = G_OPR_UPDATE ) THEN
2859 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Update Page Entity');
2860
2861 /*Added for bug 9733398*/
2862 /*check if the same sequence is already used.*/
2863 SELECT count(1) INTO lv_count_seq
2864 FROM ego_page_entries_b
2865 WHERE page_id = P_ent_tbl(i).page_id
2866 AND sequence = P_ent_tbl(i).sequence
2867 AND association_id <> P_ent_tbl(i).old_association_id;
2868
2869 IF (lv_count_seq > 0) THEN
2870 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Page Entry sequence already exists');
2871
2872 P_ent_tbl(i).process_status := G_ERROR_RECORD;
2873
2874 error_handler.Add_error_message(p_message_name => 'EGO_PAGE_ENTRY_SAME_SEQ',p_application_id => G_EGO_APPLICATION_ID,
2875 p_token_tbl => G_TOKEN_TABLE,p_message_type => G_RET_STS_ERROR,
2876 p_row_identifier => P_ent_tbl(i).transaction_id,
2877 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2878 ELSE
2879 /*End of comment for bug 9733398*/
2880 ego_ext_fwk_pub.Update_page_entry ( p_api_version => 1.0,
2881 p_page_id => P_ent_tbl(i).page_id,
2882 p_new_association_id => NVL(P_ent_tbl(i).new_association_id,P_ent_tbl(i).old_association_id),
2883 p_old_association_id => P_ent_tbl(i).old_association_id,
2884 p_sequence => P_ent_tbl(i).SEQUENCE,
2885 p_init_msg_list => fnd_api.g_false,
2886 p_commit => fnd_api.g_false,
2887 x_return_status => x_return_status,
2888 x_errorcode => x_errorcode,
2889 x_msg_count => x_msg_count,
2890 x_msg_data => x_msg_data);
2891
2892 IF (x_return_status = G_RET_STS_SUCCESS) THEN
2893 P_ent_tbl(i).process_status := G_SUCCESS_RECORD;
2894 ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2895 x_return_msg := x_msg_data;
2896
2897 RETURN;
2898 ELSE
2899 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Error in creating PG');
2900
2901 P_ent_tbl(i).process_status := G_ERROR_RECORD;
2902
2903 G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
2904 G_TOKEN_TABLE(1).Token_Value := G_ENTITY_ENT;
2905 G_TOKEN_TABLE(2).Token_Name := 'Transaction_Type';
2906 G_TOKEN_TABLE(2).Token_Value := P_ent_tbl(i).transaction_type;
2907 G_TOKEN_TABLE(3).Token_Name := 'Package_Name';
2908 G_TOKEN_TABLE(3).Token_Value := 'ego_ext_fwk_pub';
2909 G_TOKEN_TABLE(4).Token_Name := 'Proc_Name';
2910 G_TOKEN_TABLE(4).Token_Value := 'Update_page_entry';
2911
2912 error_handler.Add_error_message(p_message_name => 'EGO_ENTITY_API_FAILED',p_application_id => G_EGO_APPLICATION_ID,
2913 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2914 p_row_identifier => P_ent_tbl(i).transaction_id,
2915 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2916
2917 error_handler.Add_error_message(p_message_text => x_msg_data,p_application_id => G_EGO_APPLICATION_ID,
2918 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2919 p_row_identifier => P_ent_tbl(i).transaction_id,
2920 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2921
2922 G_TOKEN_TABLE.DELETE;
2923 END IF;
2924 END IF; /*Added for bug 9733398*/
2925 ELSIF ( P_ent_tbl(i).process_status = G_PROCESS_RECORD
2926 AND P_ent_tbl(i).transaction_type = G_OPR_DELETE ) THEN
2927 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Delete Page Entity');
2928
2929 ego_ext_fwk_pub.Delete_page_entry ( p_api_version => 1.0,
2930 p_page_id => P_ent_tbl(i).page_id,
2931 p_association_id => P_ent_tbl(i).old_association_id,
2932 p_classification_code => P_ent_tbl(i).classification_code,
2933 p_init_msg_list => fnd_api.g_false,
2934 p_commit => fnd_api.g_false,
2935 x_return_status => x_return_status,
2936 x_errorcode => x_errorcode,
2937 x_msg_count => x_msg_count,
2938 x_msg_data => x_msg_data);
2939
2940 IF (x_return_status = G_RET_STS_SUCCESS) THEN
2941 P_ent_tbl(i).process_status := G_SUCCESS_RECORD;
2942 ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2943 x_return_msg := x_msg_data;
2944
2945 RETURN;
2946 ELSE
2947 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Error in creating PG');
2948
2949 P_ent_tbl(i).process_status := G_ERROR_RECORD;
2950
2951 G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
2952 G_TOKEN_TABLE(1).Token_Value := G_ENTITY_ENT;
2953 G_TOKEN_TABLE(2).Token_Name := 'Transaction_Type';
2954 G_TOKEN_TABLE(2).Token_Value := P_ent_tbl(i).transaction_type;
2955 G_TOKEN_TABLE(3).Token_Name := 'Package_Name';
2956 G_TOKEN_TABLE(3).Token_Value := 'ego_ext_fwk_pub';
2957 G_TOKEN_TABLE(4).Token_Name := 'Proc_Name';
2958 G_TOKEN_TABLE(4).Token_Value := 'Delete_page_entry';
2959
2960 error_handler.Add_error_message(p_message_name => 'EGO_ENTITY_API_FAILED',p_application_id => G_EGO_APPLICATION_ID,
2961 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2962 p_row_identifier => P_ent_tbl(i).transaction_id,
2963 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2964
2965 error_handler.Add_error_message(p_message_text => x_msg_data,p_application_id => G_EGO_APPLICATION_ID,
2966 p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
2967 p_row_identifier => P_ent_tbl(i).transaction_id,
2968 p_entity_code => G_ENTITY_ENT,p_table_name => G_ENTITY_ENT_TAB);
2969
2970 G_TOKEN_TABLE.DELETE;
2971 END IF;
2972
2973 END IF;
2974 END LOOP;
2975
2976 IF ( G_COMMIT = true AND G_FLOW_TYPE = G_EGO_MD_API ) THEN
2977 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Commit Process_ent');
2978
2979 COMMIT;
2980 END IF;
2981
2982 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit Process_ent');
2983 EXCEPTION
2984 WHEN OTHERS THEN
2985 x_return_status := G_RET_STS_UNEXP_ERROR;
2986
2987 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Process_ent Exception when others'||SQLERRM);
2988
2989 x_return_msg := 'ego_pages_bulkload_pvt.Process_ent - '||SQLERRM;
2990
2991 RETURN;
2992 END Process_ent;
2993
2994 /*This procedure is used to update the Pages interface table.
2995 Used in the interface flow.
2996 p_pg_tbl IN OUT NOCOPY Pages table
2997 x_return_status OUT NOCOPY parameter that returns the status
2998 x_return_msg OUT NOCOPY parameter that returns the error message*/
2999 PROCEDURE update_intf_pages (p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
3000 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
3001 IS
3002 lv_proc VARCHAR2(30) := 'update_intf_pages';
3003 trans_id dbms_sql.number_table;
3004 BEGIN
3005 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_pages');
3006
3007 x_return_status := G_RET_STS_SUCCESS;
3008
3009 FOR i IN p_pg_tbl.FIRST..p_pg_tbl.LAST LOOP
3010 trans_id(i) := p_pg_tbl(i).transaction_id;
3011 END LOOP;
3012
3013 FORALL i IN p_pg_tbl.first..p_pg_tbl.last
3014 UPDATE ego_pages_interface
3015 SET ROW = P_pg_tbl(i)
3016 WHERE transaction_id = trans_id(i);
3017
3018 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_pages');
3019 EXCEPTION
3020 WHEN OTHERS THEN
3021 x_return_status := G_RET_STS_UNEXP_ERROR;
3022
3023 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'update_intf_pages Exception when others'||SQLERRM);
3024
3025 x_return_msg := 'ego_pages_bulkload_pvt.update_intf_pages - '||SQLERRM;
3026
3027 RETURN;
3028 END update_intf_pages;
3029
3030 /*This procedure is used to update the page entries interface table.
3031 Used in the interface flow.
3032 p_ent_tbl IN OUT NOCOPY Page Entries table
3033 x_return_status OUT NOCOPY parameter that returns the status
3034 x_return_msg OUT NOCOPY parameter that returns the error message*/
3035 PROCEDURE update_intf_pg_entries (p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
3036 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
3037 IS
3038 lv_proc VARCHAR2(30) := 'update_intf_pg_entries';
3039 trans_id dbms_sql.number_table;
3040 BEGIN
3041 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_pg_entries');
3042
3043 x_return_status := G_RET_STS_SUCCESS;
3044
3045 FOR i IN p_ent_tbl.FIRST..p_ent_tbl.LAST LOOP
3046 trans_id(i) := p_ent_tbl(i).transaction_id;
3047 END LOOP;
3048
3049 FORALL i IN p_ent_tbl.first..p_ent_tbl.last
3050 UPDATE ego_page_entries_interface
3051 SET ROW = P_ent_tbl(i)
3052 WHERE transaction_id = trans_id(i);
3053
3054 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_pg_entries');
3055
3056
3057 EXCEPTION
3058 WHEN OTHERS THEN
3059 x_return_status := G_RET_STS_UNEXP_ERROR;
3060
3061 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'update_intf_pg_entries Exception when others'||SQLERRM);
3062
3063 x_return_msg := 'ego_pages_bulkload_pvt.update_intf_pg_entries - '||SQLERRM;
3064
3065 RETURN;
3066 END update_intf_pg_entries;
3067
3068 /*This procedure is used to delete processed records from the pages interface
3069 Used in the interface flow.
3070 x_set_process_id IN Set Process ID
3071 x_return_status OUT NOCOPY parameter that returns the status
3072 x_return_msg OUT NOCOPY parameter that returns the error message*/
3073 PROCEDURE delete_processed_pages(
3074 x_set_process_id IN NUMBER,
3075 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
3076 IS
3077 lv_proc VARCHAR2(30) := 'delete_processed_pages';
3078 BEGIN
3079 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_pages');
3080
3081 x_return_status := G_RET_STS_SUCCESS;
3082
3083 DELETE FROM ego_pages_interface
3084 WHERE process_status = G_SUCCESS_RECORD
3085 AND (x_set_process_id IS NULL
3086 OR set_process_id = x_set_process_id);
3087
3088 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_pages');
3089 EXCEPTION
3090 WHEN OTHERS THEN
3091 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'delete_processed_pages Exception when others'||SQLERRM);
3092
3093 x_return_status := G_RET_STS_UNEXP_ERROR;
3094
3095 x_return_msg := 'ego_pages_bulkload_pvt.delete_processed_pages - '||SQLERRM;
3096
3097 RETURN;
3098 END delete_processed_pages;
3099
3100 /*This procedure is used to deleted processed records from the page entries interface
3101 Used in the interface flow.
3102 x_set_process_id IN Set Process ID
3103 x_return_status OUT NOCOPY parameter that returns the status
3104 x_return_msg OUT NOCOPY parameter that returns the error message*/
3105 PROCEDURE delete_processed_pg_entries(
3106 x_set_process_id IN NUMBER,
3107 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
3108 IS
3109 lv_proc VARCHAR2(30) := 'delete_processed_pg_entries';
3110 BEGIN
3111 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_pg_entries');
3112
3113 x_return_status := G_RET_STS_SUCCESS;
3114
3115 DELETE FROM ego_page_entries_interface
3116 WHERE process_status = G_SUCCESS_RECORD
3117 AND (x_set_process_id IS NULL
3118 OR set_process_id = x_set_process_id);
3119
3120 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_pg_entries');
3121 EXCEPTION
3122 WHEN OTHERS THEN
3123 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'delete_processed_pg_entries Exception when others'||SQLERRM);
3124
3125 x_return_status := G_RET_STS_UNEXP_ERROR;
3126
3127 x_return_msg := 'ego_pages_bulkload_pvt.delete_processed_pg_entries - '||SQLERRM;
3128
3129 RETURN;
3130 END delete_processed_pg_entries;
3131
3132 /*This procedure is used in the update flow to handle null values for PG
3133 Used in the interface and API flow.
3134 p_pg_tbl IN OUT NOCOPY Pages plsql table
3135 x_return_status OUT NOCOPY parameter that returns the status
3136 x_return_msg OUT NOCOPY parameter that returns the error message*/
3137 PROCEDURE handle_null_pg(
3138 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
3139 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
3140 IS
3141 lv_sequence NUMBER;
3142 /* Fix for bug 12356534 - changed the width of below vars from 40chars to match their columns in EGO_PAGES_TL */
3143 lv_display_name EGO_PAGES_TL.DISPLAY_NAME%TYPE;
3144 lv_description EGO_PAGES_TL.DESCRIPTION%TYPE;
3145 lv_proc VARCHAR2(30) := 'handle_null_pg';
3146 BEGIN
3147 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering handle_null_pg');
3148
3149 x_return_status := G_RET_STS_SUCCESS;
3150
3151 FOR i IN p_pg_tbl.FIRST..p_pg_tbl.LAST LOOP
3152 IF (p_pg_tbl(i).process_status = G_PROCESS_RECORD
3153 AND p_pg_tbl(i).transaction_type = G_OPR_UPDATE) THEN
3154 BEGIN
3155 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 1');
3156
3157 SELECT sequence INTO lv_sequence
3158 FROM EGO_PAGES_B
3159 WHERE page_id = p_pg_tbl(i).page_id;
3160
3161 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 2');
3162
3163 SELECT display_name, description INTO lv_display_name, lv_description
3164 FROM EGO_PAGES_TL
3165 WHERE page_id = p_pg_tbl(i).page_id
3166 AND USERENV('LANG') = LANGUAGE;
3167
3168 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 3');
3169 IF (p_pg_tbl(i).display_name IS NULL or p_pg_tbl(i).display_name = G_NULL_CHAR) THEN
3170 p_pg_tbl(i).display_name := lv_display_name;
3171 END IF;
3172 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 4');
3173 IF (p_pg_tbl(i).description = G_NULL_CHAR) THEN
3174 write_debug('1');
3175 p_pg_tbl(i).description := NULL;
3176 ELSIF (p_pg_tbl(i).description IS NULL) THEN
3177 write_debug('2');
3178 p_pg_tbl(i).description := lv_description;
3179 END IF;
3180 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 5');
3181 IF (p_pg_tbl(i).sequence IS NULL OR p_pg_tbl(i).sequence = G_NULL_NUM) THEN
3182 p_pg_tbl(i).sequence := lv_sequence;
3183 END IF;
3184 EXCEPTION
3185 WHEN OTHERS THEN
3186 x_return_status := G_RET_STS_UNEXP_ERROR;
3187
3188 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'handle_null_pg exception when others smt 1');
3189
3190 x_return_msg := 'ego_pages_bulkload_pvt.handle_null_pg - '||SQLERRM;
3191
3192 RETURN;
3193 END;
3194 END IF;
3195 END LOOP;
3196 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_pg');
3197 EXCEPTION
3198 WHEN OTHERS THEN
3199 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'handle_null_pg Exception when others'||SQLERRM);
3200 END handle_null_pg;
3201
3202
3203 /*This procedure is used in the update flow to handle null values for PG Entries
3204 Used in the interface and API flow.
3205 p_ent_tbl IN OUT NOCOPY Page Entries plsql table
3206 x_return_status OUT NOCOPY parameter that returns the status
3207 x_return_msg OUT NOCOPY parameter that returns the error message*/
3208 PROCEDURE handle_null_pg_entries(
3209 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
3210 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
3211 IS
3212 lv_sequence NUMBER;
3213 lv_proc VARCHAR2(30) := 'handle_null_pg_entries';
3214 BEGIN
3215 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering handle_null_pg_entries');
3216
3217 x_return_status := G_RET_STS_SUCCESS;
3218
3219 FOR i IN p_ent_tbl.FIRST..p_ent_tbl.LAST LOOP
3220 IF (p_ent_tbl(i).process_status = G_PROCESS_RECORD
3221 AND p_ent_tbl(i).transaction_type = G_OPR_UPDATE) THEN
3222 BEGIN
3223 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 1');
3224
3225 SELECT sequence INTO lv_sequence
3226 FROM EGO_PAGE_ENTRIES_B
3227 WHERE page_id = p_ent_tbl(i).page_id
3228 AND association_id = p_ent_tbl(i).old_association_id;
3229
3230 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 2');
3231
3232 IF (p_ent_tbl(i).sequence IS NULL or p_ent_tbl(i).sequence = G_NULL_NUM) THEN
3233 p_ent_tbl(i).sequence := lv_sequence;
3234 END IF;
3235 EXCEPTION
3236 WHEN OTHERS THEN
3237 x_return_status := G_RET_STS_UNEXP_ERROR;
3238
3239 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'handle_null_pg_entries exception when others smt 1');
3240
3241 x_return_msg := 'ego_pages_bulkload_pvt.handle_null_pg_entries - '||SQLERRM;
3242
3243 RETURN;
3244 END;
3245 END IF;
3246 END LOOP;
3247 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_pg_entries');
3248 EXCEPTION
3249 WHEN OTHERS THEN
3250 write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'handle_null_pg_entries Exception when others'||SQLERRM);
3251 END handle_null_pg_entries;
3252
3253 /*This procedure will log debug messages
3254 x_msg IN Input message name*/
3255 Procedure write_debug(x_msg IN VARCHAR2)
3256 IS
3257 BEGIN
3258 ego_metadata_bulkload_pvt.write_debug(x_msg);
3259 --debug_proc(x_msg);
3260 EXCEPTION
3261 WHEN OTHERS THEN
3262 NULL;
3263 END write_debug;
3264 END ego_pages_bulkload_pvt;