[Home] [Help]
PACKAGE BODY: APPS.EGO_FUNCTIONS_BULKLOAD_PVT
Source
1 PACKAGE BODY EGO_FUNCTIONS_BULKLOAD_PVT AS
2 /* $Header: EGOVFNBB.pls 120.0.12010000.7 2010/05/17 12:48:59 snandana noship $ */
3
4 ---------------------------------------------------------------------------------------------
5 -- Procedure Name: write_debug --
6 -- This procedure will log debug messages to the Concurrent Request log file. --
7 -- Parameters: --
8 -- IN --
9 -- message: Debug message to be logged --
10 ---------------------------------------------------------------------------------------------
11 PROCEDURE write_debug(message VARCHAR2)
12 IS
13 BEGIN
14 --Following commented statement is to print debug messages, while testing/debugging this package standalone.
15 --dbms_output.Put_line('DEBUG: '
16 -- ||message);
17 ego_metadata_bulkload_pvt.write_debug(message);
18
19 END;
20
21 --Following commented procedure is to print error messages, while testing/debugging this package standalone.
22 --PROCEDURE Log_error(message VARCHAR2)
23 --IS
24 --BEGIN
25 --dbms_output.Put_line('ERROR: '
26 -- ||message);
27 --END;
28
29 ---------------------------------------------------------------------------------------------
30 -- Procedure Name: import_functions_intf --
31 -- This is the main procedure that will be called while running Matadata Import Concurrent --
32 -- Program, to process Functions and Function Parameters. --
33 -- Parameters: --
34 -- IN --
35 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
36 -- processed in a batch. --
37 -- OUT --
38 -- x_return_status: Return status. Can be S or U (Unexpected Error). --
39 -- x_return_msg: Stores the error message, if unexpected error occurs. --
40 ---------------------------------------------------------------------------------------------
41 PROCEDURE import_functions_intf(p_set_process_id IN NUMBER,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_return_msg OUT NOCOPY VARCHAR2) IS
44 l_proc_name VARCHAR2(30) := 'import_functions_intf';
45 BEGIN
46 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
47 x_return_status := fnd_api.G_RET_STS_SUCCESS;
48 --Following commented statement is to initialize global message list, while testing/debugging this package standalone.
49 --ERROR_HANDLER.Initialize();
50 process_functions_conc_flow(p_set_process_id);
51 process_func_params_conc_flow(p_set_process_id);
52 --Following commented procedure call is to write errors to mtl_interfce_errors table,
53 --while testing/debugging this package standalone.
54 --ERROR_HANDLER.Log_Error(
55 -- p_write_err_to_inttable => 'Y'
56 -- ,p_write_err_to_conclog => 'Y');
57 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
58 EXCEPTION
59 WHEN OTHERS THEN
60 x_return_msg := G_PCK_NAME||'.'||l_proc_name||'->'||' Unexpected error occurred: '||SQLERRM;
61 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
62 END import_functions_intf;
63
64 ---------------------------------------------------------------------------------------------
65 -- Procedure Name: delete_processed_functions --
66 -- This procedure will be called at end by Matadata Import Concurrent Program, --
67 -- to delete processed rows from Functions and Function Parameters interface tables. --
68 -- Parameters: --
69 -- IN --
70 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
71 -- belongs to a particular batch. --
72 -- OUT --
73 -- x_return_status: Return status. Can be S or U (Unexpected Error). --
74 -- x_return_msg: Stores the error message, if unexpected error occurs. --
75 ---------------------------------------------------------------------------------------------
76 /* Bug 9653987. Update x_return_status and x_return_msg to send them back to the calling function. */
77 PROCEDURE delete_processed_functions(p_set_process_id IN NUMBER,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_return_msg OUT NOCOPY VARCHAR2) IS
80 l_proc_name VARCHAR2(30) := 'delete_processed_functions';
81 BEGIN
82 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
83 x_return_status := fnd_api.G_RET_STS_SUCCESS;
84 DELETE FROM ego_functions_interface WHERE (p_set_process_id IS NULL OR set_process_id=p_set_process_id)
85 AND process_status=G_SUCCESS_RECORD;
86 DELETE FROM ego_func_params_interface WHERE (p_set_process_id IS NULL OR set_process_id=p_set_process_id)
87 AND process_status=G_SUCCESS_RECORD;
88 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
89 EXCEPTION
90 WHEN OTHERS THEN
91 x_return_msg := G_PCK_NAME||'.'||l_proc_name||'->'||' Unexpected error occurred: '||SQLERRM;
92 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
93 END delete_processed_functions;
94
95 ---------------------------------------------------------------------------------------------
96 -- Procedure Name: construct_function --
97 -- This procedure will validate transaction type and the key columns that can identify --
98 -- a function and also converts SYNC transaction to either CREATE or UPDATE, --
99 -- if the validation succeeds. --
100 -- Parameters: --
101 -- IN OUT --
102 -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
103 ---------------------------------------------------------------------------------------------
104 PROCEDURE construct_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
105 invalid_function_id NUMBER(1);
106 invalid_internal_name NUMBER(1);
107 l_proc_name VARCHAR2(30) := 'construct_function';
108 BEGIN
109 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
110 IF (func_header_rec.transaction_type IS NULL
111 OR (func_header_rec.transaction_type <> G_CREATE_TRANSACTION
112 AND func_header_rec.transaction_type <> G_UPDATE_TRANSACTION
113 AND func_header_rec.transaction_type <> G_DELETE_TRANSACTION
114 AND func_header_rec.transaction_type <> G_SYNC_TRANSACTION)) THEN
115 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
116 ': (FID, FNAME) = (' || func_header_rec.function_id ||
117 ', '|| func_header_rec.internal_name || '): Invalid Transaction Type.');
118 ERROR_HANDLER.Add_Error_Message(
119 p_message_name => 'EGO_TRANS_TYPE'
120 ,p_application_id => 'EGO'
121 ,p_message_type => FND_API.G_RET_STS_ERROR
122 ,p_row_identifier => func_header_rec.transaction_id
123 ,p_entity_code => G_ENTITY_ICC_FN
124 ,p_table_name => G_FUNCTIONS_TAB
125 );
126 func_header_rec.process_status:=G_ERROR_RECORD;
127 END IF;
128
129 /* Convert SYNC to CREATE/UPDATE. Validate function_id and internal_name for SYNC, UPDATE and DELETE transaction types. */
130 IF ( func_header_rec.transaction_type = G_UPDATE_TRANSACTION
131 OR func_header_rec.transaction_type = G_SYNC_TRANSACTION
132 OR func_header_rec.transaction_type = G_DELETE_TRANSACTION ) THEN
133 IF ( func_header_rec.function_id IS NOT NULL ) THEN
134 BEGIN
135 invalid_function_id := 0;
136
137 SELECT internal_name
138 INTO func_header_rec.internal_name
139 FROM ego_functions_b
140 WHERE ( function_id = func_header_rec.function_id );
141 EXCEPTION
142 WHEN no_data_found THEN
143 invalid_function_id := 1;
144 END;
145 IF (invalid_function_id=1) THEN
146 IF (func_header_rec.transaction_type=G_SYNC_TRANSACTION) THEN
147 func_header_rec.transaction_type:=G_CREATE_TRANSACTION;
148 ELSE
149 --Log_error('Invalid Function ID.');
150 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
151 ': (FID, FNAME) = (' || func_header_rec.function_id ||
152 ', '|| func_header_rec.internal_name || '): Invalid Function ID.');
153 ERROR_HANDLER.Add_Error_Message(
154 p_message_name => 'EGO_EF_FUNC_ID_ERR'
155 ,p_application_id => 'EGO'
156 ,p_message_type => FND_API.G_RET_STS_ERROR
157 ,p_row_identifier => func_header_rec.transaction_id
158 ,p_entity_code => G_ENTITY_ICC_FN
159 ,p_table_name => G_FUNCTIONS_TAB
160 );
161 func_header_rec.process_status:=G_ERROR_RECORD;
162 END IF;
163 ELSE
164 IF (func_header_rec.transaction_type=G_SYNC_TRANSACTION) THEN
165 func_header_rec.transaction_type:=G_UPDATE_TRANSACTION;
166 END IF;
167 END IF;
168 ELSIF ( func_header_rec.internal_name IS NOT NULL ) THEN
169 BEGIN
170 invalid_internal_name := 0;
171
172 SELECT function_id
173 INTO func_header_rec.function_id
174 FROM ego_functions_b
175 WHERE ( internal_name = func_header_rec.internal_name );
176 EXCEPTION
177 WHEN no_data_found THEN
178 invalid_internal_name := 1;
179 END;
180 IF (invalid_internal_name=1) THEN
181 IF (func_header_rec.transaction_type=G_SYNC_TRANSACTION) THEN
182 func_header_rec.transaction_type:=G_CREATE_TRANSACTION;
183 ELSE
184 --Log_error('Invalid Function Internal Name.');
185 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
186 ': (FID, FNAME) = (' || func_header_rec.function_id ||
187 ', '|| func_header_rec.internal_name || '): Invalid Function Internal Name.');
188 ERROR_HANDLER.Add_Error_Message(
189 p_message_name => 'EGO_EF_FUNC_INT_NAME_INVL'
190 ,p_application_id => 'EGO'
191 ,p_message_type => FND_API.G_RET_STS_ERROR
192 ,p_row_identifier => func_header_rec.transaction_id
193 ,p_entity_code => G_ENTITY_ICC_FN
194 ,p_table_name => G_FUNCTIONS_TAB
195 );
196 func_header_rec.process_status:=G_ERROR_RECORD;
197 END IF;
198 ELSE
199 IF (func_header_rec.transaction_type=G_SYNC_TRANSACTION) THEN
203 ELSE
200 func_header_rec.transaction_type:=G_UPDATE_TRANSACTION;
201 END IF;
202 END IF;
204 --Log_error('Either Function ID or Internal Name must be provided.');
205 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
206 ': (FID, FNAME) = (' || func_header_rec.function_id ||
207 ', '|| func_header_rec.internal_name || '): Either Function ID or Internal Name must be provided.');
208 ERROR_HANDLER.Add_Error_Message(
209 p_message_name => 'EGO_EF_FUNC_ID_INT_NAME_ERR'
210 ,p_application_id => 'EGO'
211 ,p_message_type => FND_API.G_RET_STS_ERROR
212 ,p_row_identifier => func_header_rec.transaction_id
213 ,p_entity_code => G_ENTITY_ICC_FN
214 ,p_table_name => G_FUNCTIONS_TAB
215 );
216 func_header_rec.process_status := G_ERROR_RECORD;
217 END IF;
218 END IF;
219 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
220 END construct_function;
221
222 ---------------------------------------------------------------------------------------------
223 -- Procedure Name: validate_function --
224 -- This procedure will perform the remaining validations (excluding the validations done --
225 -- on key columns in construct_function) based on the transaction type. --
226 -- Parameters: --
227 -- IN OUT --
228 -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
229 ---------------------------------------------------------------------------------------------
230 PROCEDURE validate_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
231 temporary_record ego_functions_b%ROWTYPE;
232 temporary_record_tl ego_functions_tl%ROWTYPE;
233 valid_function_type NUMBER(1);
234 duplicate_internal_name NUMBER(1);
235 function_is_used NUMBER(1);
236 error_count NUMBER;
237 l_proc_name VARCHAR2(30) := 'validate_function';
238
239 l_token_table ERROR_HANDLER.Token_Tbl_Type;
240 BEGIN
241 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
242 /* Validations for CREATE transaction type. */
243 IF ( func_header_rec.transaction_type = G_CREATE_TRANSACTION
244 AND func_header_rec.process_status <> G_ERROR_RECORD ) THEN
245 /* 1. Validating Function Type. */
246 IF ( func_header_rec.function_type IS NOT NULL ) THEN
247 BEGIN
248 SELECT 1
249 INTO valid_function_type
250 FROM fnd_lookup_values
251 WHERE ( lookup_type = 'EGO_EF_FUNCTION_TYPE'
252 AND language = Userenv('LANG')
253 AND lookup_code = func_header_rec.function_type );
254 EXCEPTION
255 WHEN no_data_found THEN
256 valid_function_type := 0;
257 END;
258 IF (valid_function_type=0) THEN
259 --Log_error('Invalid Function Type.');
260 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
261 ': (FID, FNAME) = (' || func_header_rec.function_id ||
262 ', '|| func_header_rec.internal_name || '): Invalid Function Type.');
263 ERROR_HANDLER.Add_Error_Message(
264 p_message_name => 'EGO_EF_FUNC_TYPE_ERR'
265 ,p_application_id => 'EGO'
266 ,p_message_type => FND_API.G_RET_STS_ERROR
267 ,p_row_identifier => func_header_rec.transaction_id
268 ,p_entity_code => G_ENTITY_ICC_FN
269 ,p_table_name => G_FUNCTIONS_TAB
270 );
271 func_header_rec.process_status:=G_ERROR_RECORD;
272 END IF;
273 END IF;
274
275 /* 2. Validation of all mandatory columns. */
276 IF ( func_header_rec.internal_name IS NULL
277 OR func_header_rec.display_name IS NULL
278 OR func_header_rec.function_info_1 IS NULL
279 OR func_header_rec.function_type IS NULL
280 OR ( ( func_header_rec.function_type = 'J'
281 OR func_header_rec.function_type = 'P' )
282 AND func_header_rec.function_info_2 IS NULL ) ) THEN
283 --Log_error('One of the mandatory columns is missed.');
284 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
285 ': (FID, FNAME) = (' || func_header_rec.function_id ||
286 ', '|| func_header_rec.internal_name || '): One of the mandatory columns is missed.');
287 ERROR_HANDLER.Add_Error_Message(
288 p_message_name => 'EGO_EF_FUNC_REQ_COLS_ERR'
289 ,p_application_id => 'EGO'
290 ,p_message_type => FND_API.G_RET_STS_ERROR
291 ,p_row_identifier => func_header_rec.transaction_id
292 ,p_entity_code => G_ENTITY_ICC_FN
293 ,p_table_name => G_FUNCTIONS_TAB
294 );
295 func_header_rec.process_status := G_ERROR_RECORD;
296 END IF;
297
301 INTO duplicate_internal_name
298 /* 3. Make sure that Function Internal Name is unique. */
299 BEGIN
300 SELECT 1
302 FROM ego_functions_b
303 WHERE ( internal_name = func_header_rec.internal_name );
304 EXCEPTION
305 WHEN no_data_found THEN
306 duplicate_internal_name := 0;
307 END;
308
309 IF ( duplicate_internal_name = 1 ) THEN
310 --Log_error('Duplicate Function Internal Name.');
311 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
312 ': (FID, FNAME) = (' || func_header_rec.function_id ||
313 ', '|| func_header_rec.internal_name || '): Duplicate Function Internal Name.');
314 ERROR_HANDLER.Add_Error_Message(
315 p_message_name => 'EGO_EF_FUNC_INT_NAME_ERR'
316 ,p_application_id => 'EGO'
317 ,p_message_type => FND_API.G_RET_STS_ERROR
318 ,p_row_identifier => func_header_rec.transaction_id
319 ,p_entity_code => G_ENTITY_ICC_FN
320 ,p_table_name => G_FUNCTIONS_TAB
321 );
322
323 func_header_rec.process_status := G_ERROR_RECORD;
324 END IF;
325
326 /* Validations for UPDATE transaction type. */
327 ELSIF ( func_header_rec.transaction_type = G_UPDATE_TRANSACTION
328 AND func_header_rec.process_status <> G_ERROR_RECORD ) THEN
329 /* Fetch the existing data from base tables and merge it with interface table row func_header_rec. */
330 SELECT *
331 INTO temporary_record
332 FROM ego_functions_b
333 WHERE ( function_id = func_header_rec.function_id );
334
335 SELECT *
336 INTO temporary_record_tl
337 FROM ego_functions_tl
338 WHERE ( function_id = func_header_rec.function_id )
339 AND language = Userenv('LANG');
340
341 /* 1. Validating Function Type. */
342 IF ( func_header_rec.function_type IS NULL ) THEN
343 func_header_rec.function_type := temporary_record.function_type;
344 ELSIF ( func_header_rec.function_type = G_NULL_CHAR ) THEN
345 func_header_rec.function_type := NULL;
346 ELSIF ( func_header_rec.function_type <> temporary_record.function_type ) THEN
347 --Log_error('Function Type can not be modified.');
348 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
349 ': (FID, FNAME) = (' || func_header_rec.function_id ||
350 ', '|| func_header_rec.internal_name || '): Function Type can not be modified.');
351 ERROR_HANDLER.Add_Error_Message(
352 p_message_name => 'EGO_EF_FUNC_TYPE_UPD'
353 ,p_application_id => 'EGO'
354 ,p_message_type => FND_API.G_RET_STS_ERROR
355 ,p_row_identifier => func_header_rec.transaction_id
356 ,p_entity_code => G_ENTITY_ICC_FN
357 ,p_table_name => G_FUNCTIONS_TAB
358 );
359 func_header_rec.process_status := G_ERROR_RECORD;
360 END IF;
361
362 IF ( func_header_rec.function_info_1 IS NULL ) THEN
363 func_header_rec.function_info_1 := temporary_record.function_info_1;
364 ELSIF ( func_header_rec.function_info_1 = G_NULL_CHAR ) THEN
365 func_header_rec.function_info_1 := NULL;
366 END IF;
367
368 IF ( func_header_rec.function_info_2 IS NULL ) THEN
369 func_header_rec.function_info_2 := temporary_record.function_info_2;
370 ELSIF ( func_header_rec.function_info_2 = G_NULL_CHAR ) THEN
371 func_header_rec.function_info_2 := NULL;
372 END IF;
373
374 IF ( func_header_rec.display_name IS NULL ) THEN
375 func_header_rec.display_name := temporary_record_tl.display_name;
376 ELSIF ( func_header_rec.display_name = G_NULL_CHAR ) THEN
377 func_header_rec.display_name := NULL;
378 END IF;
379
380 IF ( func_header_rec.description IS NULL ) THEN
381 func_header_rec.description := temporary_record_tl.description;
382 ELSIF ( func_header_rec.description = G_NULL_CHAR ) THEN
383 func_header_rec.description := NULL;
384 END IF;
385
386 /* 2. Validation of all mandatory columns. */
387 IF ( func_header_rec.internal_name IS NULL
388 OR func_header_rec.display_name IS NULL
389 OR func_header_rec.function_info_1 IS NULL
390 OR func_header_rec.function_type IS NULL
391 OR ( ( func_header_rec.function_type = 'J'
392 OR func_header_rec.function_type = 'P' )
393 AND func_header_rec.function_info_2 IS NULL ) ) THEN
394 --Log_error('One of the mandatory columns is missed.');
395 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
396 ': (FID, FNAME) = (' || func_header_rec.function_id ||
397 ', '|| func_header_rec.internal_name || '): One of the mandatory columns is missed.');
398 ERROR_HANDLER.Add_Error_Message(
399 p_message_name => 'EGO_EF_FUNC_REQ_COLS_ERR'
400 ,p_application_id => 'EGO'
401 ,p_message_type => FND_API.G_RET_STS_ERROR
402 ,p_row_identifier => func_header_rec.transaction_id
406 func_header_rec.process_status := G_ERROR_RECORD;
403 ,p_entity_code => G_ENTITY_ICC_FN
404 ,p_table_name => G_FUNCTIONS_TAB
405 );
407 END IF;
408
409
410 /* Validations for DELETE transaction type. */
411 ELSIF ( func_header_rec.transaction_type = G_DELETE_TRANSACTION
412 AND func_header_rec.process_status <> G_ERROR_RECORD ) THEN
413 BEGIN
414 /* 1. Check if the function is used for Item Number or Description generation of the ICC. */
415 SELECT 1
416 INTO function_is_used
417 FROM dual
418 WHERE EXISTS (SELECT *
419 FROM ego_actions_b
420 WHERE ( function_id = func_header_rec.function_id ));
421 EXCEPTION
422 WHEN no_data_found THEN
423 function_is_used := 0;
424 END;
425 /* 2. Check if the function is used in the Actions on Attribute Groups associated with ICC. */
426 IF (function_is_used=0) THEN
427 BEGIN
428 SELECT 1 INTO function_is_used
429 FROM dual
430 WHERE EXISTS (SELECT * FROM ego_action_displays_b WHERE (prompt_function_id=func_header_rec.function_id
431 AND visibility_func_id=func_header_rec.function_id));
432 EXCEPTION
433 WHEN no_data_found THEN
434 function_is_used:=0;
435 END;
436 END IF;
437 IF (function_is_used=1) THEN
438 --Log_error('Function is in use.');
439 l_token_table(1).token_name := 'FUNC_NAME';
440 l_token_table(1).token_value := func_header_rec.internal_name;
441 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
442 ': (FID, FNAME) = (' || func_header_rec.function_id ||
443 ', '|| func_header_rec.internal_name || '): Function is in use.');
444 ERROR_HANDLER.Add_Error_Message(
445 p_message_name => 'EGO_EF_FUNC_IN_USE'
446 ,p_application_id => 'EGO'
447 ,p_token_tbl => l_token_table
448 ,p_message_type => FND_API.G_RET_STS_ERROR
449 ,p_row_identifier => func_header_rec.transaction_id
450 ,p_entity_code => G_ENTITY_ICC_FN
451 ,p_table_name => G_FUNCTIONS_TAB
452 );
453 func_header_rec.process_status:=G_ERROR_RECORD;
454 END IF;
455 END IF;
456 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
457 END validate_function;
458
459 ---------------------------------------------------------------------------------------------
460 -- Procedure Name: transact_function --
461 -- This procedure will update the base table, with the data in func_header_rec, only if --
462 -- there are no validation errors (process_status<>3), based on transaction type. --
463 -- Parameters: --
464 -- IN OUT --
465 -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
466 ---------------------------------------------------------------------------------------------
467 PROCEDURE transact_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE) IS
468 l_proc_name VARCHAR2(30) := 'transact_function';
469 BEGIN
470 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
471 /* Insert into base table, if a row does not have any errors. */
472 IF ( func_header_rec.process_status <> G_ERROR_RECORD ) THEN
473 IF (func_header_rec.transaction_type=G_CREATE_TRANSACTION) then
474 SELECT ego_functions_s.nextval
475 INTO func_header_rec.function_id
476 FROM dual;
477
478 INSERT INTO ego_functions_b
479 (function_id,
480 internal_name,
481 function_type,
482 function_info_1,
483 function_info_2,
484 created_by,
485 creation_date,
486 last_updated_by,
487 last_update_date,
488 last_update_login)
489 VALUES (func_header_rec.function_id,
490 func_header_rec.internal_name,
491 func_header_rec.function_type,
492 func_header_rec.function_info_1,
493 func_header_rec.function_info_2,
494 G_USER_ID,
495 SYSDATE,
496 G_USER_ID,
497 SYSDATE,
498 G_LOGIN_ID);
499
500 INSERT INTO ego_functions_tl
501 (function_id,
502 display_name,
503 description,
504 language,
505 source_lang,
506 created_by,
507 creation_date,
508 last_updated_by,
509 last_update_date,
510 last_update_login)
511 SELECT func_header_rec.function_id,
512 func_header_rec.display_name,
513 func_header_rec.description,
514 language_code,
518 G_USER_ID,
515 Userenv('LANG'),
516 G_USER_ID,
517 SYSDATE,
519 SYSDATE,
520 G_LOGIN_ID
521 FROM fnd_languages l
522 WHERE installed_flag IN ( 'I', 'B' );
523
524 ELSIF ( func_header_rec.transaction_type = G_UPDATE_TRANSACTION ) THEN
525 UPDATE ego_functions_b
526 SET function_type = func_header_rec.function_type,
527 function_info_1 = func_header_rec.function_info_1,
528 function_info_2 = func_header_rec.function_info_2,
529 last_updated_by = G_USER_ID,
530 last_update_date = SYSDATE,
531 last_update_login = G_LOGIN_ID
532 WHERE ( function_id = func_header_rec.function_id );
533
534 UPDATE ego_functions_tl
535 SET display_name = func_header_rec.display_name,
536 description = func_header_rec.description,
537 last_updated_by = G_USER_ID,
538 last_update_date = SYSDATE,
539 last_update_login = G_LOGIN_ID
540 WHERE ( function_id = func_header_rec.function_id )
541 AND Userenv('LANG') IN ( language, source_lang );
542
543 ELSIF (func_header_rec.transaction_type = G_DELETE_TRANSACTION) THEN
544 DELETE ego_functions_b WHERE (function_id=func_header_rec.function_id);
545 DELETE ego_functions_tl WHERE (function_id=func_header_rec.function_id);
546 /* Bug 9647937. Delete rows from ego_func_params_tl table, before deleting rows from
547 ego_func_params_b table. */
548 DELETE ego_func_params_tl WHERE func_param_id IN (SELECT func_param_id FROM ego_func_params_b
549 WHERE (function_id=func_header_rec.function_id));
550 DELETE ego_func_params_b WHERE (function_id=func_header_rec.function_id);
551 END IF;
552 func_header_rec.process_status := G_SUCCESS_RECORD;
553 END IF;
554 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
555 END transact_function;
556
557 ---------------------------------------------------------------------------------------------
558 -- Procedure Name: update_intfc_functions --
559 -- This will be invoked in Concurrent Request flow. --
560 -- This procedure will update the interface table back after processing the records. --
561 -- Parameters: --
562 -- IN OUT --
563 -- ego_func_tbl_values - Represents a table of type ego_functions_interface%ROWTYPE. --
564 ---------------------------------------------------------------------------------------------
565 /* Bug 9701271. Changing the implementation, as reference like table(bulk_index).field in FORALL statement
566 are not supported, before 11g release.*/
567 PROCEDURE update_intfc_functions(ego_func_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_function_tbl_type) IS
568 l_proc_name VARCHAR2(30) := 'update_intfc_functions';
569 transaction_id_table Dbms_Sql.number_table;
570 BEGIN
571 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
572
573 FOR i IN 1 .. ego_func_tbl_values.Count LOOP
574 transaction_id_table(i) := ego_func_tbl_values(i).transaction_id;
575 ego_func_tbl_values(i).last_update_date := SYSDATE;
576 ego_func_tbl_values(i).program_update_date := SYSDATE;
577 END LOOP;
578
579 /* Update the interface table back. */
580 FORALL i IN 1 .. ego_func_tbl_values.COUNT
581 UPDATE ego_functions_interface
582 SET ROW = ego_func_tbl_values(i)
583 WHERE ( transaction_id = transaction_id_table(i) );
584 /* Commit after updating the interface table back. */
585 COMMIT;
586 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
587 END update_intfc_functions;
588
589
590 ---------------------------------------------------------------------------------------------
591 -- Procedure Name: process_functions --
592 -- This procedure will process all the functions one by one. Technically, it will call the --
593 -- previous three functions. --
594 -- Parameters: --
595 -- IN OUT --
596 -- ego_func_tbl_values - Represents a table of type ego_functions_interface%ROWTYPE. --
597 -- p_commit - Indicates whether to commit the work or not. This parameter --
598 -- has significance only in public API flow. --
599 ---------------------------------------------------------------------------------------------
600 PROCEDURE process_functions(ego_func_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_function_tbl_type,
601 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE)
602 IS
603 l_proc_name VARCHAR2(30) := 'process_functions';
604 BEGIN
605 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
606 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Processing '
607 || ego_func_tbl_values.COUNT
608 || ' records');
609
610 ERROR_HANDLER.Set_Bo_Identifier(G_BO_IDENTIFIER_ICC);
611
612 FOR i IN 1 .. ego_func_tbl_values.COUNT LOOP
613 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Processing (TID, TTYPE) = (' ||
614 Ego_func_tbl_values(i).transaction_id || ', ' || Ego_func_tbl_values(i).transaction_type ||
618 construct_function(ego_func_tbl_values(i));
615 '): (FID, FNAME) = (' || Ego_func_tbl_values(i).function_id ||
616 ', '|| Ego_func_tbl_values(i).internal_name || ').');
617
619 validate_function(ego_func_tbl_values(i));
620 transact_function(ego_func_tbl_values(i));
621 IF (p_commit = FND_API.G_TRUE) THEN
622 COMMIT;
623 END IF;
624 END LOOP;
625
626 END process_functions;
627
628 ---------------------------------------------------------------------------------------------
629 -- Procedure Name: initialize_functions --
630 -- This procedure will intialize functions interface table with by updating the --
631 -- "WHO" columns, transaction_id and convering the transction_type to upper case. --
632 -- Parameters: --
633 -- IN --
634 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
635 -- belongs to a particular batch. --
636 ---------------------------------------------------------------------------------------------
637 PROCEDURE initialize_functions(p_set_process_id IN NUMBER)
638 IS
639 l_proc_name VARCHAR2(30) := 'initialize_functions';
640 BEGIN
641 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
642 UPDATE ego_functions_interface
643 SET transaction_id = mtl_system_items_interface_s.nextval,
644 transaction_type = Upper(transaction_type),
645 last_update_date = sysdate,
646 last_updated_by = G_USER_ID,
647 request_id = G_REQUEST_ID,
648 program_application_id = G_PROGRAM_APPLICATION_ID,
649 program_id = G_PROGRAM_ID,
650 program_update_date = SYSDATE
651 WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
652 AND process_status = G_PROCESS_RECORD AND transaction_id IS NULL);
653 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
654 END initialize_functions;
655
656 ---------------------------------------------------------------------------------------------
657 -- Procedure Name: process_functions_conc_flow --
658 -- This will be invoked in Concurrent Request flow. --
659 -- This procedure will read the data in chunks from ego_functions_interface table, --
660 -- processes them by calling process_functions() and then updates interface table --
661 -- back by calling update_intfc_functions(). --
662 -- Parameters: --
663 -- IN --
664 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
665 -- processed in a batch. --
666 ---------------------------------------------------------------------------------------------
667 PROCEDURE process_functions_conc_flow(p_set_process_id IN NUMBER)
668 IS
669 ego_func_tbl_values ego_metadata_pub.ego_function_tbl_type;
670 l_proc_name VARCHAR2(30) := 'process_functions_conc_flow';
671 record_count NUMBER;
672 BEGIN
673 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
674
675 /* Bug 9671972. Return if there are no rows to process. */
676 SELECT Count(*) INTO record_count
677 FROM ego_functions_interface
678 WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
679 AND process_status = G_PROCESS_RECORD );
680
681 IF (record_count=0) THEN
682 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Return as there are no Function records to process');
683 RETURN;
684 END IF;
685
686 initialize_functions(p_set_process_id);
687 bulk_validate_functions(p_set_process_id);
688
689 OPEN ego_func_tbl(p_set_process_id);
690 BEGIN
691 LOOP
692 FETCH ego_func_tbl BULK COLLECT INTO ego_func_tbl_values LIMIT 2000;
693 process_functions(ego_func_tbl_values);
694 update_intfc_functions(ego_func_tbl_values);
695 EXIT WHEN ego_func_tbl_values.COUNT < 2000;
696 END LOOP;
697 CLOSE ego_func_tbl;
698 EXCEPTION
699 WHEN OTHERS THEN
700 CLOSE ego_func_tbl;
701 RAISE;
702 END;
703 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
704 END process_functions_conc_flow;
705
706 ----------------------------------------------------------------------------------------------
707 -- Procedure Name: process_functions_conc_flow --
708 -- This will be invoked in Concurrent Request flow. --
709 -- This procedure will do bulk validations. --
710 -- Parameters: --
711 -- IN --
712 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
713 -- processed in a batch. --
714 ----------------------------------------------------------------------------------------------
715 PROCEDURE bulk_validate_functions(p_set_process_id IN NUMBER)
716 IS
717 message_name fnd_new_messages.message_name%TYPE;
718 message_text fnd_new_messages.message_text%TYPE;
722
719 l_proc_name VARCHAR2(30) := 'bulk_validate_functions';
720 BEGIN
721 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
723 message_name := 'EGO_TRANS_TYPE';
724 FND_MESSAGE.SET_NAME('EGO',message_name );
725 message_text := FND_MESSAGE.GET;
726
727 /* Error out the rows with Transaction Type null and other than CREATE, UPDATE, DELETE, SYNC */
728 INSERT INTO mtl_interface_errors
729 (unique_id,
730 transaction_id,
731 table_name,
732 message_name,
733 error_message,
734 bo_identifier,
735 entity_identifier,
736 message_type,
737 creation_date,
738 created_by,
739 last_updated_by,
740 last_update_date,
741 last_update_login,
742 request_id,
743 program_application_id,
744 program_id,
745 program_update_date)
746 SELECT mtl_system_items_interface_s.nextval,
747 transaction_id,
748 G_FUNCTIONS_TAB,
749 message_name,
750 message_text,
751 G_BO_IDENTIFIER_ICC,
752 G_ENTITY_ICC_FN,
753 fnd_api.g_ret_sts_error,
754 SYSDATE,
755 G_USER_ID,
756 G_USER_ID,
757 SYSDATE,
758 G_LOGIN_ID,
759 G_REQUEST_ID,
760 G_PROGRAM_APPLICATION_ID,
761 G_PROGRAM_ID,
762 SYSDATE
763 FROM ego_functions_interface
764 WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
765 OR transaction_type IS NULL )
766 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
767 AND process_status = G_PROCESS_RECORD;
768
769 UPDATE ego_functions_interface
770 SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
771 WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
772 OR transaction_type IS NULL )
773 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
774 AND process_status = G_PROCESS_RECORD;
775
776 /* For UPDATE and DELETE transactions, validate function_id if it is not null. */
777 message_name := 'EGO_EF_FUNC_ID_ERR';
778 FND_MESSAGE.SET_NAME('EGO',message_name );
779 message_text := FND_MESSAGE.GET;
780
781 INSERT INTO mtl_interface_errors
782 (unique_id,
783 transaction_id,
784 table_name,
785 message_name,
786 error_message,
787 bo_identifier,
788 entity_identifier,
789 message_type,
790 creation_date,
791 created_by,
792 last_updated_by,
793 last_update_date,
794 last_update_login,
795 request_id,
796 program_application_id,
797 program_id,
798 program_update_date)
799 SELECT mtl_system_items_interface_s.nextval,
800 transaction_id,
801 G_FUNCTIONS_TAB,
802 message_name,
803 message_text,
804 G_BO_IDENTIFIER_ICC,
805 G_ENTITY_ICC_FN,
806 fnd_api.g_ret_sts_error,
807 SYSDATE,
808 G_USER_ID,
809 G_USER_ID,
810 SYSDATE,
811 G_LOGIN_ID,
812 G_REQUEST_ID,
813 G_PROGRAM_APPLICATION_ID,
814 G_PROGRAM_ID,
815 SYSDATE
816 FROM ego_functions_interface i
817 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
818 AND NOT EXISTS (SELECT function_id
819 FROM ego_functions_b b
820 WHERE ( b.function_id = i.function_id ))
821 AND function_id IS NOT NULL
822 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
823 AND process_status = G_PROCESS_RECORD;
824
825 UPDATE ego_functions_interface i
826 SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
827 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
828 AND NOT EXISTS (SELECT *
829 FROM ego_functions_b b
830 WHERE ( b.function_id = i.function_id ))
831 AND function_id IS NOT NULL
832 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
833 AND process_status = G_PROCESS_RECORD;
834
835 /* For UPDATE and DELETE transactions, validate internal_name if it is not null and
836 function_id is not provided. */
837 message_name := 'EGO_EF_FUNC_INT_NAME_INVL';
838 FND_MESSAGE.SET_NAME('EGO',message_name );
839 message_text := FND_MESSAGE.GET;
840
841 INSERT INTO mtl_interface_errors
842 (unique_id,
843 transaction_id,
844 table_name,
845 message_name,
846 error_message,
847 bo_identifier,
848 entity_identifier,
849 message_type,
850 creation_date,
851 created_by,
852 last_updated_by,
853 last_update_date,
854 last_update_login,
858 program_update_date)
855 request_id,
856 program_application_id,
857 program_id,
859 SELECT mtl_system_items_interface_s.nextval,
860 transaction_id,
861 G_FUNCTIONS_TAB,
862 message_name,
863 message_text,
864 G_BO_IDENTIFIER_ICC,
865 G_ENTITY_ICC_FN,
866 fnd_api.g_ret_sts_error,
867 SYSDATE,
868 G_USER_ID,
869 G_USER_ID,
870 SYSDATE,
871 G_LOGIN_ID,
872 G_REQUEST_ID,
873 G_PROGRAM_APPLICATION_ID,
874 G_PROGRAM_ID,
875 SYSDATE
876 FROM ego_functions_interface i
877 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
878 AND NOT EXISTS (SELECT internal_name
879 FROM ego_functions_b b
880 WHERE ( b.internal_name = i.internal_name )
881 UNION
882 SELECT internal_name
883 FROM ego_functions_interface ii
884 WHERE ( ii.internal_name = i.internal_name )
885 AND transaction_type = G_CREATE_TRANSACTION
886 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
887 AND process_status = G_PROCESS_RECORD)
888 AND function_id IS NULL
889 AND internal_name IS NOT NULL
890 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
891 AND process_status = G_PROCESS_RECORD;
892
893 UPDATE ego_functions_interface i
894 SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
895 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
896 AND NOT EXISTS (SELECT internal_name
897 FROM ego_functions_b b
898 WHERE ( b.internal_name = i.internal_name )
899 UNION
900 SELECT internal_name
901 FROM ego_functions_interface ii
902 WHERE ( ii.internal_name = i.internal_name )
903 AND transaction_type = G_CREATE_TRANSACTION
904 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
905 AND process_status = G_PROCESS_RECORD)
906 AND function_id IS NULL
907 AND internal_name IS NOT NULL
908 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
909 AND process_status = G_PROCESS_RECORD;
910
911 /* For UPDATE and DELETE transactions, error out rows that do not have both function_id and internal_name. */
912 message_name := 'EGO_EF_FUNC_ID_INT_NAME_ERR';
913 FND_MESSAGE.SET_NAME('EGO',message_name );
914 message_text := FND_MESSAGE.GET;
915
916 INSERT INTO mtl_interface_errors
917 (unique_id,
918 transaction_id,
919 table_name,
920 message_name,
921 error_message,
922 bo_identifier,
923 entity_identifier,
924 message_type,
925 creation_date,
926 created_by,
927 last_updated_by,
928 last_update_date,
929 last_update_login,
930 request_id,
931 program_application_id,
932 program_id,
933 program_update_date)
934 SELECT mtl_system_items_interface_s.nextval,
935 transaction_id,
936 G_FUNCTIONS_TAB,
937 message_name,
938 message_text,
939 G_BO_IDENTIFIER_ICC,
940 G_ENTITY_ICC_FN,
941 fnd_api.g_ret_sts_error,
942 SYSDATE,
943 G_USER_ID,
944 G_USER_ID,
945 SYSDATE,
946 G_LOGIN_ID,
947 G_REQUEST_ID,
948 G_PROGRAM_APPLICATION_ID,
949 G_PROGRAM_ID,
950 SYSDATE
951 FROM ego_functions_interface
952 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
953 AND function_id IS NULL
954 AND internal_name IS NULL
955 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
956 AND process_status = G_PROCESS_RECORD;
957
958 UPDATE ego_functions_interface i
959 SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
960 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
961 AND function_id IS NULL
962 AND internal_name IS NULL
963 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
964 AND process_status = G_PROCESS_RECORD;
965
966 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
967 END bulk_validate_functions;
968
969 ---------------------------------------------------------------------------------------------
970 -- Procedure Name: construct_func_param --
971 -- This procedure will validate transaction type and the key columns that can identify --
972 -- a function parameter and also converts SYNC transaction to either CREATE or UPDATE, --
973 -- if the validation succeeds. --
974 -- Parameters: --
978 PROCEDURE construct_func_param(func_param_rec IN OUT NOCOPY ego_func_params_interface%ROWTYPE)
975 -- IN OUT --
976 -- func_param_rec - Represents a row of type ego_func_params_interface%ROWTYPE. --
977 ---------------------------------------------------------------------------------------------
979 IS
980 invalid_function_id NUMBER(1);
981 invalid_func_param_id NUMBER(1);
982 invalid_function_internal_name NUMBER(1);
983 invalid_internal_name NUMBER(1);
984 l_proc_name VARCHAR2(30) := 'construct_func_param';
985 BEGIN
986 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
987 IF (func_param_rec.transaction_type IS NULL
988 OR (func_param_rec.transaction_type <> G_CREATE_TRANSACTION
989 AND func_param_rec.transaction_type <> G_UPDATE_TRANSACTION
990 AND func_param_rec.transaction_type <> G_DELETE_TRANSACTION
991 AND func_param_rec.transaction_type <> G_SYNC_TRANSACTION)) THEN
992 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
993 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
994 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
995 ', '||func_param_rec.internal_name||'): Invalid Transaction Type.');
996 ERROR_HANDLER.Add_Error_Message(
997 p_message_name => 'EGO_TRANS_TYPE'
998 ,p_application_id => 'EGO'
999 ,p_message_type => FND_API.G_RET_STS_ERROR
1000 ,p_row_identifier => func_param_rec.transaction_id
1001 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1002 ,p_table_name => G_FUNC_PARAMS_TAB
1003 );
1004 func_param_rec.process_status:=G_ERROR_RECORD;
1005 END IF;
1006
1007 /* Validate that Function ID and Function Internal Name.*/
1008 IF ( func_param_rec.function_id IS NOT NULL ) THEN
1009 BEGIN
1010 invalid_function_id := 0;
1011
1012 SELECT internal_name
1013 INTO func_param_rec.function_internal_name
1014 FROM ego_functions_b
1015 WHERE ( function_id = func_param_rec.function_id );
1016 EXCEPTION
1017 WHEN no_data_found THEN
1018 invalid_function_id := 1;
1019 END;
1020 IF (invalid_function_id=1) THEN
1021 --Log_error('Invalid Function ID.');
1022 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1023 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1024 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1025 ', '||func_param_rec.internal_name||'): Invalid Function ID.');
1026 ERROR_HANDLER.Add_Error_Message(
1027 p_message_name => 'EGO_EF_FUNC_ID_ERR'
1028 ,p_application_id => 'EGO'
1029 ,p_message_type => FND_API.G_RET_STS_ERROR
1030 ,p_row_identifier => func_param_rec.transaction_id
1031 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1032 ,p_table_name => G_FUNC_PARAMS_TAB
1033 );
1034 func_param_rec.process_status:=G_ERROR_RECORD;
1035 END IF;
1036 ELSIF ( func_param_rec.function_internal_name IS NOT NULL ) THEN
1037 BEGIN
1038 invalid_function_internal_name := 0;
1039
1040 SELECT function_id
1041 INTO func_param_rec.function_id
1042 FROM ego_functions_b
1043 WHERE ( internal_name = func_param_rec.function_internal_name );
1044 EXCEPTION
1045 WHEN no_data_found THEN
1046 invalid_function_internal_name := 1;
1047 END;
1048 IF (invalid_function_internal_name=1) THEN
1049 --Log_error('Invalid Function Internal Name.');
1050 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1051 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1052 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1053 ', '||func_param_rec.internal_name||'): Invalid Function Internal Name.');
1054 ERROR_HANDLER.Add_Error_Message(
1055 p_message_name => 'EGO_EF_FUNC_INT_NAME_INVL'
1056 ,p_application_id => 'EGO'
1057 ,p_message_type => FND_API.G_RET_STS_ERROR
1058 ,p_row_identifier => func_param_rec.transaction_id
1059 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1060 ,p_table_name => G_FUNC_PARAMS_TAB
1061 );
1062 func_param_rec.process_status:=G_ERROR_RECORD;
1063 END IF;
1064 ELSE
1065 --Log_error('Either Function ID or Function Internal Name must be provided.');
1066 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1067 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1068 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1069 ', '||func_param_rec.internal_name||'): Either Function ID or Function Internal Name must be provided.');
1070 ERROR_HANDLER.Add_Error_Message(
1074 ,p_row_identifier => func_param_rec.transaction_id
1071 p_message_name => 'EGO_EF_FUNC_ID_INT_NAME_ERR'
1072 ,p_application_id => 'EGO'
1073 ,p_message_type => FND_API.G_RET_STS_ERROR
1075 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1076 ,p_table_name => G_FUNC_PARAMS_TAB
1077 );
1078 func_param_rec.process_status := G_ERROR_RECORD;
1079 END IF;
1080
1081 /* Convert SYNC to CREATE/UPDATE. Validate func_parm_id and internal_name for SYNC, UPDATE and DELETE transaction types.
1082 We need to consider Function ID also to validate. */
1083 IF ( (func_param_rec.transaction_type = G_UPDATE_TRANSACTION
1084 OR func_param_rec.transaction_type = G_SYNC_TRANSACTION
1085 OR func_param_rec.transaction_type = G_DELETE_TRANSACTION)
1086 AND func_param_rec.process_status <> G_ERROR_RECORD ) THEN
1087 IF ( func_param_rec.func_param_id IS NOT NULL ) THEN
1088 BEGIN
1089 invalid_func_param_id := 0;
1090
1091 SELECT internal_name
1092 INTO func_param_rec.internal_name
1093 FROM ego_func_params_b
1094 WHERE ( function_id = func_param_rec.function_id
1095 AND func_param_id = func_param_rec.func_param_id );
1096 EXCEPTION
1097 WHEN no_data_found THEN
1098 invalid_func_param_id := 1;
1099 END;
1100 IF (invalid_func_param_id=1) THEN
1101 IF (func_param_rec.transaction_type=G_SYNC_TRANSACTION) THEN
1102 func_param_rec.transaction_type:=G_CREATE_TRANSACTION;
1103 ELSE
1104 --Log_error('Invalid Parameter ID for the given Function ID or Function Internal Name.');
1105 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1106 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1107 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1108 ', '||func_param_rec.internal_name||'): Invalid Parameter ID for the given Function ID or Function Internal Name.');
1109 ERROR_HANDLER.Add_Error_Message(
1110 p_message_name => 'EGO_EF_FP_ID_ERR'
1111 ,p_application_id => 'EGO'
1112 ,p_message_type => FND_API.G_RET_STS_ERROR
1113 ,p_row_identifier => func_param_rec.transaction_id
1114 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1115 ,p_table_name => G_FUNC_PARAMS_TAB
1116 );
1117 func_param_rec.process_status:=G_ERROR_RECORD;
1118 END IF;
1119 ELSE
1120 IF (func_param_rec.transaction_type=G_SYNC_TRANSACTION) THEN
1121 func_param_rec.transaction_type:=G_UPDATE_TRANSACTION;
1122 END IF;
1123 END IF;
1124 ELSIF ( func_param_rec.internal_name IS NOT NULL ) THEN
1125 BEGIN
1126 invalid_internal_name := 0;
1127
1128 SELECT func_param_id
1129 INTO func_param_rec.func_param_id
1130 FROM ego_func_params_b
1131 WHERE ( function_id = func_param_rec.function_id
1132 AND internal_name = func_param_rec.internal_name );
1133 EXCEPTION
1134 WHEN no_data_found THEN
1135 invalid_internal_name := 1;
1136 END;
1137 IF (invalid_internal_name=1) THEN
1138 IF (func_param_rec.transaction_type=G_SYNC_TRANSACTION) THEN
1139 func_param_rec.transaction_type:=G_CREATE_TRANSACTION;
1140 ELSE
1141 --Log_error('Invalid Parameter Internal Name for the given Function ID or Function Internal Name.');
1142 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1143 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1144 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1145 ', '||func_param_rec.internal_name||'): Invalid Parameter Internal Name for the given Function ID or Function Internal Name.');
1146 ERROR_HANDLER.Add_Error_Message(
1147 p_message_name => 'EGO_EF_FP_INT_NAME_INVL'
1148 ,p_application_id => 'EGO'
1149 ,p_message_type => FND_API.G_RET_STS_ERROR
1150 ,p_row_identifier => func_param_rec.transaction_id
1151 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1152 ,p_table_name => G_FUNC_PARAMS_TAB
1153 );
1154 func_param_rec.process_status:=G_ERROR_RECORD;
1155 END IF;
1156 ELSE
1157 IF (func_param_rec.transaction_type=G_SYNC_TRANSACTION) THEN
1158 func_param_rec.transaction_type:=G_UPDATE_TRANSACTION;
1159 END IF;
1160 END IF;
1161 ELSE
1162 --Log_error('Either Parameter ID or Parameter Internal Name must be provided.');
1163 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1164 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1165 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1169 ,p_application_id => 'EGO'
1166 ', '||func_param_rec.internal_name||'): Either Parameter ID or Parameter Internal Name must be provided.');
1167 ERROR_HANDLER.Add_Error_Message(
1168 p_message_name => 'EGO_EF_FP_ID_INT_NAME_ERR'
1170 ,p_message_type => FND_API.G_RET_STS_ERROR
1171 ,p_row_identifier => func_param_rec.transaction_id
1172 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1173 ,p_table_name => G_FUNC_PARAMS_TAB
1174 );
1175 func_param_rec.process_status := G_ERROR_RECORD;
1176 END IF;
1177 END IF;
1178 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
1179 END construct_func_param;
1180
1181 ---------------------------------------------------------------------------------------------
1182 -- Procedure Name: validate_func_param --
1183 -- This procedure will perform the remaining validations (excluding the validations done --
1184 -- on key columns in construct_func_param) based on the transaction type. --
1185 -- Parameters: --
1186 -- IN OUT --
1187 -- func_param_rec - Represents a row of type ego_func_params_interface%ROWTYPE. --
1188 ---------------------------------------------------------------------------------------------
1189 PROCEDURE validate_func_param(func_param_rec IN OUT NOCOPY ego_func_params_interface%ROWTYPE)
1190 IS
1191 x_function_type ego_functions_b.function_type%TYPE;
1192 x_lookup_code fnd_lookup_values.lookup_code%TYPE;
1193 valid_data_type NUMBER(1);
1194 valid_param_type NUMBER(1);
1195 duplicate_internal_name NUMBER(1);
1196 duplicate_sequence NUMBER(1);
1197 temporary_record ego_func_params_b%ROWTYPE;
1198 temporary_record_tl ego_func_params_tl%ROWTYPE;
1199 l_proc_name VARCHAR2(30) := 'validate_func_param';
1200 BEGIN
1201 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
1202 /* Validations for CREATE transaction type. */
1203 IF ( func_param_rec.transaction_type = G_CREATE_TRANSACTION
1204 AND func_param_rec.process_status <> G_ERROR_RECORD ) THEN
1205 /* 1. Validation for Data Type. */
1206 SELECT function_type
1207 INTO x_function_type
1208 FROM ego_functions_b
1209 WHERE ( function_id = func_param_rec.function_id );
1210
1211 IF ( func_param_rec.data_type IS NOT NULL ) THEN
1212 IF ( x_function_type = 'P' ) THEN
1213 x_lookup_code := 'EGO_EF_FUNC_PARAM_DATA_TYPE_P';
1214 ELSIF ( x_function_type = 'J' ) THEN
1215 x_lookup_code := 'EGO_EF_FUNC_PARAM_DATA_TYPE_J';
1216 ELSIF ( x_function_type = 'S' ) THEN
1217 x_lookup_code := 'EGO_EF_FUNC_PARAM_DATA_TYPE_S';
1218 END IF;
1219
1220 BEGIN
1221 SELECT 1
1222 INTO valid_data_type
1223 FROM fnd_lookup_values
1224 WHERE ( lookup_type = x_lookup_code
1225 AND language = Userenv('LANG')
1226 AND lookup_code = func_param_rec.data_type );
1227 EXCEPTION
1228 WHEN no_data_found THEN
1229 valid_data_type := 0;
1230 END;
1231
1232 IF ( valid_data_type = 0 ) THEN
1233 --Log_error('Invalid Data Type.');
1234 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1235 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1236 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1237 ', '||func_param_rec.internal_name||'): Invalid Data Type.');
1238 ERROR_HANDLER.Add_Error_Message(
1239 p_message_name => 'EGO_PUB_INVALID_DATATYPE'
1240 ,p_application_id => 'EGO'
1241 ,p_message_type => FND_API.G_RET_STS_ERROR
1242 ,p_row_identifier => func_param_rec.transaction_id
1243 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1244 ,p_table_name => G_FUNC_PARAMS_TAB
1245 );
1246 func_param_rec.process_status := G_ERROR_RECORD;
1247 END IF;
1248 END IF;
1249 /* 2. Validation for Parameter Type. If Function Type is URL, Parameter Type must be Input.
1250 If Data Type is Error Array, Parameter Type must be Output. The outer if is not required. */
1251 IF ( func_param_rec.param_type IS NOT NULL ) THEN
1252 valid_param_type := 1;
1253 IF ( x_function_type = 'S' ) THEN
1254 IF ( func_param_rec.param_type <> 'I' ) THEN
1255 valid_param_type := 0;
1256 END IF;
1257 ELSIF ( func_param_rec.data_type = 'E' ) THEN
1258 IF ( func_param_rec.param_type <> 'O' ) THEN
1259 valid_param_type := 0;
1260 END IF;
1261 ELSE
1262 BEGIN
1263 SELECT 1
1264 INTO valid_param_type
1265 FROM fnd_lookup_values
1266 WHERE ( lookup_type = 'EGO_EF_FUNC_PARAM_TYPE'
1267 AND language = Userenv('LANG')
1268 AND lookup_code = func_param_rec.param_type );
1269 EXCEPTION
1273 END IF;
1270 WHEN no_data_found THEN
1271 valid_param_type := 0;
1272 END;
1274 IF (valid_param_type=0) THEN
1275 --Log_error('Invalid Parameter Type.');
1276 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1277 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1278 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1279 ', '||func_param_rec.internal_name||'): Invalid Parameter Type.');
1280 ERROR_HANDLER.Add_Error_Message(
1281 p_message_name => 'EGO_EF_PARAM_TYPE_ERR'
1282 ,p_application_id => 'EGO'
1283 ,p_message_type => FND_API.G_RET_STS_ERROR
1284 ,p_row_identifier => func_param_rec.transaction_id
1285 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1286 ,p_table_name => G_FUNC_PARAMS_TAB
1287 );
1288 func_param_rec.process_status:=G_ERROR_RECORD;
1289 END IF;
1290 END IF;
1291
1292 /* 3. Validation for Internal Name. */
1293 BEGIN
1294 SELECT 1
1295 INTO duplicate_internal_name
1296 FROM ego_func_params_b
1297 WHERE ( function_id = func_param_rec.function_id
1298 AND internal_name = func_param_rec.internal_name );
1299 EXCEPTION
1300 WHEN no_data_found THEN
1301 duplicate_internal_name := 0;
1302 END;
1303
1304 IF ( duplicate_internal_name = 1 ) THEN
1305 --Log_error('Duplicate Parameter Internal Name.');
1306 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1307 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1308 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1309 ', '||func_param_rec.internal_name||'): Duplicate Parameter Internal Name.');
1310 ERROR_HANDLER.Add_Error_Message(
1311 p_message_name => 'EGO_EF_FP_INT_NAME_ERR'
1312 ,p_application_id => 'EGO'
1313 ,p_message_type => FND_API.G_RET_STS_ERROR
1314 ,p_row_identifier => func_param_rec.transaction_id
1315 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1316 ,p_table_name => G_FUNC_PARAMS_TAB
1317 );
1318 func_param_rec.process_status := G_ERROR_RECORD;
1319 END IF;
1320
1321 /* 4. Validation for Sequence. */
1322 BEGIN
1323 SELECT 1
1324 INTO duplicate_sequence
1325 FROM ego_func_params_b
1326 WHERE ( function_id = func_param_rec.function_id
1327 AND SEQUENCE = func_param_rec.SEQUENCE );
1328 EXCEPTION
1329 WHEN no_data_found THEN
1330 duplicate_sequence := 0;
1331 END;
1332
1333 IF ( duplicate_sequence = 1 ) THEN
1334 --Log_error('Duplicate Sequence.');
1335 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1336 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1337 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1338 ', '||func_param_rec.internal_name||'): Duplicate Sequence.');
1339 ERROR_HANDLER.Add_Error_Message(
1340 p_message_name => 'EGO_EF_FP_DUPLICATE_SEQ_ERR'
1341 ,p_application_id => 'EGO'
1342 ,p_message_type => FND_API.G_RET_STS_ERROR
1343 ,p_row_identifier => func_param_rec.transaction_id
1344 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1345 ,p_table_name => G_FUNC_PARAMS_TAB
1346 );
1347 func_param_rec.process_status := G_ERROR_RECORD;
1348 END IF;
1349
1350 /* 5. Validation for Mandatory columns. */
1351 IF ( func_param_rec.internal_name IS NULL
1352 OR func_param_rec.display_name IS NULL
1353 OR func_param_rec.SEQUENCE IS NULL
1354 OR func_param_rec.data_type IS NULL
1355 OR func_param_rec.param_type IS NULL ) THEN
1356 --Log_error('Mandatory columns must be provided.');
1357 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1358 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1359 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1360 ', '||func_param_rec.internal_name||'): Mandatory columns must be provided.');
1361 ERROR_HANDLER.Add_Error_Message(
1362 p_message_name => 'EGO_EF_FP_REQ_COLS_ERR'
1363 ,p_application_id => 'EGO'
1364 ,p_message_type => FND_API.G_RET_STS_ERROR
1365 ,p_row_identifier => func_param_rec.transaction_id
1366 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1367 ,p_table_name => G_FUNC_PARAMS_TAB
1368 );
1369 func_param_rec.process_status := G_ERROR_RECORD;
1370 END IF;
1374 SELECT *
1371
1372 ELSIF ( func_param_rec.transaction_type = G_UPDATE_TRANSACTION
1373 AND func_param_rec.process_status <> G_ERROR_RECORD ) THEN
1375 INTO temporary_record
1376 FROM ego_func_params_b
1377 WHERE ( func_param_id = func_param_rec.func_param_id );
1378
1379 SELECT *
1380 INTO temporary_record_tl
1381 FROM ego_func_params_tl
1382 WHERE ( func_param_id = func_param_rec.func_param_id )
1383 AND language = Userenv('LANG');
1384
1385 /* 1. Validating Data Type. */
1386 IF ( func_param_rec.data_type IS NULL ) THEN
1387 func_param_rec.data_type := temporary_record.data_type;
1388 ELSIF ( func_param_rec.data_type = G_NULL_CHAR ) THEN
1389 func_param_rec.data_type := NULL;
1390 ELSIF ( func_param_rec.data_type <> temporary_record.data_type ) THEN
1391 --Log_error('Data Type can not be modified.');
1392 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1393 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1394 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1395 ', '||func_param_rec.internal_name||'): Data Type can not be modified.');
1396 ERROR_HANDLER.Add_Error_Message(
1397 p_message_name => 'EGO_EF_DATA_TYPE_UPD'
1398 ,p_application_id => 'EGO'
1399 ,p_message_type => FND_API.G_RET_STS_ERROR
1400 ,p_row_identifier => func_param_rec.transaction_id
1401 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1402 ,p_table_name => G_FUNC_PARAMS_TAB
1403 );
1404 func_param_rec.process_status := G_ERROR_RECORD;
1405 END IF;
1406
1407 /* 2. Validating Parameter Type. */
1408 IF ( func_param_rec.param_type IS NULL ) THEN
1409 func_param_rec.param_type := temporary_record.param_type;
1410 ELSIF ( func_param_rec.param_type = G_NULL_CHAR ) THEN
1411 func_param_rec.param_type := NULL;
1412 ELSIF ( func_param_rec.param_type <> temporary_record.param_type ) THEN
1413 --Log_error('Parameter Type can not be modified.');
1414 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1415 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1416 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1417 ', '||func_param_rec.internal_name||'): Parameter Type can not be modified.');
1418 ERROR_HANDLER.Add_Error_Message(
1419 p_message_name => 'EGO_EF_PARAM_TYPE_UPD'
1420 ,p_application_id => 'EGO'
1421 ,p_message_type => FND_API.G_RET_STS_ERROR
1422 ,p_row_identifier => func_param_rec.transaction_id
1423 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1424 ,p_table_name => G_FUNC_PARAMS_TAB
1425 );
1426 func_param_rec.process_status := G_ERROR_RECORD;
1427 END IF;
1428
1429 IF ( func_param_rec.display_name IS NULL ) THEN
1430 func_param_rec.display_name := temporary_record_tl.display_name;
1431 ELSIF ( func_param_rec.display_name = G_NULL_CHAR ) THEN
1432 func_param_rec.display_name := NULL;
1433 END IF;
1434
1435 /* Bug 9653987. Validate the Sequence, only if it is changed. */
1436 IF ( func_param_rec.SEQUENCE IS NULL ) THEN
1437 func_param_rec.SEQUENCE := temporary_record.SEQUENCE;
1438 ELSIF ( func_param_rec.SEQUENCE = G_NULL_NUM ) THEN
1439 func_param_rec.SEQUENCE := NULL;
1440 ELSIF ( func_param_rec.SEQUENCE <> temporary_record.SEQUENCE) THEN
1441 BEGIN
1442 SELECT 1
1443 INTO duplicate_sequence
1444 FROM ego_func_params_b
1445 WHERE ( function_id = func_param_rec.function_id
1446 AND SEQUENCE = func_param_rec.SEQUENCE );
1447 EXCEPTION
1448 WHEN no_data_found THEN
1449 duplicate_sequence := 0;
1450 END;
1451 IF (duplicate_sequence=1) THEN
1452 --Log_error('Duplicate Sequence.');
1453 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1454 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1455 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1456 ', '||func_param_rec.internal_name||'): Duplicate Sequence.');
1457 ERROR_HANDLER.Add_Error_Message(
1458 p_message_name => 'EGO_EF_FP_DUPLICATE_SEQ_ERR'
1459 ,p_application_id => 'EGO'
1460 ,p_message_type => FND_API.G_RET_STS_ERROR
1461 ,p_row_identifier => func_param_rec.transaction_id
1462 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1463 ,p_table_name => G_FUNC_PARAMS_TAB
1464 );
1465 func_param_rec.process_status:=G_ERROR_RECORD;
1466 END IF;
1467 END IF;
1468
1469 /* 3. Validation for Mandatory columns. */
1470 IF ( func_param_rec.internal_name IS NULL
1471 OR func_param_rec.display_name IS NULL
1475 --Log_error('Mandatory columns must be provided.');
1472 OR func_param_rec.SEQUENCE IS NULL
1473 OR func_param_rec.data_type IS NULL
1474 OR func_param_rec.param_type IS NULL ) THEN
1476 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
1477 ': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
1478 ', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
1479 ', '||func_param_rec.internal_name||'): Mandatory columns must be provided.');
1480 ERROR_HANDLER.Add_Error_Message(
1481 p_message_name => 'EGO_EF_FP_REQ_COLS_ERR'
1482 ,p_application_id => 'EGO'
1483 ,p_message_type => FND_API.G_RET_STS_ERROR
1484 ,p_row_identifier => func_param_rec.transaction_id
1485 ,p_entity_code => G_ENTITY_ICC_FN_PARAM
1486 ,p_table_name => G_FUNC_PARAMS_TAB
1487 );
1488 func_param_rec.process_status := G_ERROR_RECORD;
1489 END IF;
1490 /* No futher validations required for DELETE transaction. */
1491 END IF;
1492 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
1493 END validate_func_param;
1494
1495 ---------------------------------------------------------------------------------------------
1496 -- Procedure Name: transact_func_param --
1497 -- This procedure will update the base table, with the data in func_param_rec, only if --
1498 -- there are no validation errors (process_status<>3), based on transaction type. --
1499 -- Parameters: --
1500 -- IN OUT --
1501 -- func_param_rec - Represents a row of type ego_func_params_interface%ROWTYPE. --
1502 ---------------------------------------------------------------------------------------------
1503 PROCEDURE transact_func_param(func_param_rec IN OUT NOCOPY ego_func_params_interface%ROWTYPE) IS
1504 l_proc_name VARCHAR2(30) := 'transact_func_param';
1505 BEGIN
1506 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
1507 IF ( func_param_rec.process_status <> G_ERROR_RECORD ) THEN
1508 IF (func_param_rec.transaction_type=G_CREATE_TRANSACTION) THEN
1509 SELECT ego_func_params_s.nextval
1510 INTO func_param_rec.func_param_id
1511 FROM dual;
1512 INSERT INTO ego_func_params_b
1513 (function_id,
1514 func_param_id,
1515 SEQUENCE,
1516 internal_name,
1517 data_type,
1518 param_type,
1519 created_by,
1520 creation_date,
1521 last_updated_by,
1522 last_update_login,
1523 last_update_date)
1524 VALUES (func_param_rec.function_id,
1525 func_param_rec.func_param_id,
1526 func_param_rec.SEQUENCE,
1527 func_param_rec.internal_name,
1528 func_param_rec.data_type,
1529 func_param_rec.param_type,
1530 G_USER_ID,
1531 SYSDATE,
1532 G_USER_ID,
1533 G_LOGIN_ID,
1534 SYSDATE);
1535 INSERT INTO ego_func_params_tl
1536 (func_param_id,
1537 display_name,
1538 language,
1539 source_lang,
1540 created_by,
1541 creation_date,
1542 last_updated_by,
1543 last_update_login,
1544 last_update_date)
1545 SELECT func_param_rec.func_param_id,
1546 func_param_rec.display_name,
1547 language_code,
1548 Userenv('LANG'),
1549 G_USER_ID,
1550 SYSDATE,
1551 G_USER_ID,
1552 G_LOGIN_ID,
1553 SYSDATE
1554 FROM fnd_languages l
1555 WHERE installed_flag IN ( 'I', 'B' );
1556
1557 ELSIF ( func_param_rec.transaction_type = G_UPDATE_TRANSACTION ) THEN
1558 UPDATE ego_func_params_b
1559 SET SEQUENCE = func_param_rec.SEQUENCE,
1560 data_type = func_param_rec.data_type,
1561 param_type = func_param_rec.param_type,
1562 last_updated_by = G_USER_ID,
1563 last_update_login = G_LOGIN_ID,
1564 last_update_date = SYSDATE
1565 WHERE ( func_param_id = func_param_rec.func_param_id );
1566
1567 UPDATE ego_func_params_tl
1568 SET display_name = func_param_rec.display_name,
1569 last_updated_by = G_USER_ID,
1570 last_update_login = G_LOGIN_ID,
1571 last_update_date = SYSDATE
1572 WHERE ( func_param_id = func_param_rec.func_param_id )
1573 AND Userenv('LANG') IN ( language, source_lang );
1574
1575 ELSIF (func_param_rec.transaction_type = G_DELETE_TRANSACTION ) THEN
1576 /* No validations for DELETE transaction. */
1577 DELETE ego_func_params_b
1578 WHERE ( func_param_id = func_param_rec.func_param_id );
1579
1580 DELETE ego_func_params_tl
1584 WHERE ( func_param_id = func_param_rec.func_param_id );
1581 WHERE ( func_param_id = func_param_rec.func_param_id );
1582
1583 DELETE ego_mappings_b
1585 END IF;
1586 func_param_rec.process_status := G_SUCCESS_RECORD;
1587 END IF;
1588 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
1589 END transact_func_param;
1590
1591 ---------------------------------------------------------------------------------------------
1592 -- Procedure Name: update_intfc_func_params --
1593 -- This will be invoked in Concurrent Request flow. --
1594 -- This procedure will update the interface table back after processing the records. --
1595 -- Parameters: --
1596 -- IN OUT --
1597 -- ego_func_param_tbl_values - Represents a table of type --
1598 -- ego_func_params_interface%ROWTYPE. --
1599 ---------------------------------------------------------------------------------------------
1600 /* Bug 9701271. Changing the implementation, as reference like table(bulk_index).field in FORALL statement
1601 are not supported, before 11g release.*/
1602 PROCEDURE update_intfc_func_params(ego_func_param_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_func_param_tbl_type)
1603 IS
1604 l_proc_name VARCHAR2(30) := 'update_intfc_func_params';
1605 transaction_id_table Dbms_Sql.number_table;
1606 BEGIN
1607 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
1608
1609 FOR i IN 1 .. ego_func_param_tbl_values.Count LOOP
1610 transaction_id_table(i) := ego_func_param_tbl_values(i).transaction_id;
1611 ego_func_param_tbl_values(i).last_update_date := SYSDATE;
1612 ego_func_param_tbl_values(i).program_update_date := SYSDATE;
1613 END LOOP;
1614
1615 /* Update the interface table back */
1616 FORALL i IN 1 .. ego_func_param_tbl_values.COUNT
1617 UPDATE ego_func_params_interface
1618 SET ROW = ego_func_param_tbl_values(i)
1619 WHERE ( transaction_id = transaction_id_table(i) );
1620 /* Commit after updating the interface table back. */
1621 COMMIT;
1622 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
1623 END update_intfc_func_params;
1624
1625 ---------------------------------------------------------------------------------------------
1626 -- Procedure Name: process_func_params --
1627 -- This procedure will process all the function parameters one by one. Technically, it --
1628 -- will call the previous three functions. --
1629 -- Parameters: --
1630 -- IN OUT --
1631 -- ego_func_param_tbl_values - Represents a table of type --
1632 -- ego_func_params_interface%ROWTYPE. --
1633 -- p_commit - Indicates whether to commit the work or not. This parameter --
1634 -- has significance only in public API flow. --
1635 ---------------------------------------------------------------------------------------------
1636 PROCEDURE process_func_params(ego_func_param_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_func_param_tbl_type,
1637 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE)
1638 IS
1639 l_proc_name VARCHAR2(30) := 'process_func_params';
1640 BEGIN
1641 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
1642 ERROR_HANDLER.Set_Bo_Identifier(G_BO_IDENTIFIER_ICC);
1643
1644 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Processing '
1645 || ego_func_param_tbl_values.COUNT
1646 || ' records');
1647
1648 FOR i IN 1 .. ego_func_param_tbl_values.COUNT LOOP
1649 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Processing (TID, TYPE) = (' ||
1650 Ego_func_param_tbl_values(i).transaction_id || ', ' || Ego_func_param_tbl_values(i).transaction_type ||
1651 '): (FID, FNAME, FPID, FPNAME) = (' || Ego_func_param_tbl_values(i).function_id ||', '|| Ego_func_param_tbl_values(i).function_internal_name ||
1652 ', '||Ego_func_param_tbl_values(i).func_param_id ||', '|| Ego_func_param_tbl_values(i).internal_name ||').');
1653
1654 construct_func_param(ego_func_param_tbl_values(i));
1655 validate_func_param(ego_func_param_tbl_values(i));
1656 transact_func_param(ego_func_param_tbl_values(i));
1657
1658 IF (p_commit = FND_API.G_TRUE) THEN
1659 COMMIT;
1660 END IF;
1661 END LOOP;
1662 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
1663 END process_func_params;
1664
1665 ---------------------------------------------------------------------------------------------
1666 -- Procedure Name: initialize_func_params --
1667 -- This procedure will intialize function parameters interface table with by updating the --
1668 -- "WHO" columns, transaction_id and convering the transction_type to upper case. --
1669 -- Parameters: --
1670 -- IN --
1671 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
1672 -- belongs to a particular batch. --
1673 ---------------------------------------------------------------------------------------------
1677 BEGIN
1674 PROCEDURE initialize_func_params(p_set_process_id IN NUMBER)
1675 IS
1676 l_proc_name VARCHAR2(30) := 'initialize_func_params';
1678 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
1679 UPDATE ego_func_params_interface
1680 SET transaction_id = mtl_system_items_interface_s.nextval,
1681 transaction_type = Upper(transaction_type),
1682 last_update_date = SYSDATE,
1683 last_updated_by = G_USER_ID,
1684 request_id = G_REQUEST_ID,
1685 program_application_id = G_PROGRAM_APPLICATION_ID,
1686 program_id = G_PROGRAM_ID,
1687 program_update_date = SYSDATE
1688 WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1689 AND process_status = G_PROCESS_RECORD AND transaction_id IS NULL);
1690 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
1691 END initialize_func_params;
1692
1693 ---------------------------------------------------------------------------------------------
1694 -- Procedure Name: process_func_params_conc_flow --
1695 -- This will be invoked in Concurrent Request flow. --
1696 -- This procedure will read the data in chunks from ego_func_params_interface table, --
1697 -- processes them by calling process_func_params() and then updates interface table --
1698 -- back by calling update_intfc_func_params(). --
1699 -- Parameters: --
1700 -- IN --
1701 -- p_set_process_id: ID to identify the rows (in ego_func_params_interface table) to be --
1702 -- processed in a batch. --
1703 ---------------------------------------------------------------------------------------------
1704 PROCEDURE process_func_params_conc_flow(p_set_process_id IN NUMBER)
1705 IS
1706 ego_func_param_tbl_values ego_metadata_pub.ego_func_param_tbl_type;
1707 l_proc_name VARCHAR2(30) := 'process_func_params_conc_flow';
1708 record_count NUMBER;
1709 BEGIN
1710 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
1711
1712 /* Bug 9671972. Return if there are no rows to process. */
1713 SELECT Count(*) INTO record_count
1714 FROM ego_func_params_interface
1715 WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1716 AND process_status = G_PROCESS_RECORD );
1717
1718 IF (record_count=0) THEN
1719 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Return as there are no Function Parameter records to process');
1720 RETURN;
1721 END IF;
1722
1723 initialize_func_params(p_set_process_id);
1724 bulk_validate_func_params(p_set_process_id);
1725
1726 OPEN ego_func_param_tbl(p_set_process_id);
1727 BEGIN
1728 LOOP
1729 FETCH ego_func_param_tbl BULK COLLECT INTO ego_func_param_tbl_values LIMIT 2000;
1730 process_func_params(ego_func_param_tbl_values);
1731 update_intfc_func_params(ego_func_param_tbl_values);
1732 EXIT WHEN ego_func_param_tbl_values.COUNT < 2000;
1733 END LOOP;
1734 CLOSE ego_func_param_tbl;
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 CLOSE ego_func_param_tbl;
1738 RAISE;
1739 END;
1740 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
1741 END process_func_params_conc_flow;
1742
1743 ----------------------------------------------------------------------------------------------
1744 -- Procedure Name: bulk_validate_func_params --
1745 -- This will be invoked in Concurrent Request flow. --
1746 -- This procedure will do bulk validations. --
1747 -- Parameters: --
1748 -- IN --
1749 -- p_set_process_id: ID to identify the rows (in ego_func_params_interface table) to be --
1750 -- processed in a batch. --
1751 ----------------------------------------------------------------------------------------------
1752 PROCEDURE bulk_validate_func_params(p_set_process_id IN NUMBER) IS
1753 message_name fnd_new_messages.message_name%TYPE;
1754 message_text fnd_new_messages.message_text%TYPE;
1755 l_proc_name VARCHAR2(30) := 'bulk_validate_func_params';
1756 BEGIN
1757 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Entered into the procedure');
1758 /* Error out the rows with Transaction Type null and other than CREATE, UPDATE, DELETE, SYNC */
1759 message_name := 'EGO_TRANS_TYPE';
1760 FND_MESSAGE.SET_NAME('EGO',message_name );
1761 message_text := FND_MESSAGE.GET;
1762
1763 INSERT INTO mtl_interface_errors
1764 (unique_id,
1765 transaction_id,
1766 table_name,
1767 message_name,
1768 error_message,
1769 bo_identifier,
1770 entity_identifier,
1771 message_type,
1772 creation_date,
1773 created_by,
1774 last_updated_by,
1775 last_update_date,
1776 last_update_login,
1777 request_id,
1778 program_application_id,
1779 program_id,
1780 program_update_date)
1781 SELECT mtl_system_items_interface_s.nextval,
1782 transaction_id,
1783 G_FUNC_PARAMS_TAB,
1784 message_name,
1785 message_text,
1789 SYSDATE,
1786 g_bo_identifier_icc,
1787 G_ENTITY_ICC_FN_PARAM,
1788 fnd_api.g_ret_sts_error,
1790 G_USER_ID,
1791 G_USER_ID,
1792 SYSDATE,
1793 G_LOGIN_ID,
1794 G_REQUEST_ID,
1795 G_PROGRAM_APPLICATION_ID,
1796 G_PROGRAM_ID,
1797 SYSDATE
1798 FROM ego_func_params_interface
1799 WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
1800 OR transaction_type IS NULL )
1801 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1802 AND process_status = G_PROCESS_RECORD;
1803
1804 UPDATE ego_func_params_interface
1805 SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
1806 WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
1807 OR transaction_type IS NULL )
1808 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1809 AND process_status = G_PROCESS_RECORD;
1810
1811 /* Validate function_id, if it is not null. */
1812 message_name := 'EGO_EF_FUNC_ID_ERR';
1813 FND_MESSAGE.SET_NAME('EGO',message_name );
1814 message_text := FND_MESSAGE.GET;
1815
1816 INSERT INTO mtl_interface_errors
1817 (unique_id,
1818 transaction_id,
1819 table_name,
1820 message_name,
1821 error_message,
1822 bo_identifier,
1823 entity_identifier,
1824 message_type,
1825 creation_date,
1826 created_by,
1827 last_updated_by,
1828 last_update_date,
1829 last_update_login,
1830 request_id,
1831 program_application_id,
1832 program_id,
1833 program_update_date)
1834 SELECT mtl_system_items_interface_s.nextval,
1835 transaction_id,
1836 G_FUNC_PARAMS_TAB,
1837 message_name,
1838 message_text,
1839 g_bo_identifier_icc,
1840 G_ENTITY_ICC_FN_PARAM,
1841 fnd_api.g_ret_sts_error,
1842 SYSDATE,
1843 G_USER_ID,
1844 G_USER_ID,
1845 SYSDATE,
1846 G_LOGIN_ID,
1847 G_REQUEST_ID,
1848 G_PROGRAM_APPLICATION_ID,
1849 G_PROGRAM_ID,
1850 SYSDATE
1851 FROM ego_func_params_interface i
1852 WHERE NOT EXISTS (SELECT *
1853 FROM ego_functions_b b
1854 WHERE ( b.function_id = i.function_id ))
1855 AND function_id IS NOT NULL
1856 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1857 AND process_status = G_PROCESS_RECORD;
1858
1859 UPDATE ego_func_params_interface i
1860 SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
1861 WHERE NOT EXISTS (SELECT *
1862 FROM ego_functions_b b
1863 WHERE ( b.function_id = i.function_id ))
1864 AND function_id IS NOT NULL
1865 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1866 AND process_status = G_PROCESS_RECORD;
1867
1868 /* Validate validate internal_name if it is not null and function_id is not provided.
1869 We do not need to look into ego_functions_interface table for Internal Name, as we call
1870 this function only after processing ego_functions_interface table */
1871 message_name := 'EGO_INVALID_FUNC_INT_NAME';
1872 FND_MESSAGE.SET_NAME('EGO',message_name );
1873 message_text := FND_MESSAGE.GET;
1874
1875 INSERT INTO mtl_interface_errors
1876 (unique_id,
1877 transaction_id,
1878 table_name,
1879 message_name,
1880 error_message,
1881 bo_identifier,
1882 entity_identifier,
1883 message_type,
1884 creation_date,
1885 created_by,
1886 last_updated_by,
1887 last_update_date,
1888 last_update_login,
1889 request_id,
1890 program_application_id,
1891 program_id,
1892 program_update_date)
1893 SELECT mtl_system_items_interface_s.nextval,
1894 transaction_id,
1895 G_FUNC_PARAMS_TAB,
1896 message_name,
1897 message_text,
1898 g_bo_identifier_icc,
1899 G_ENTITY_ICC_FN_PARAM,
1900 fnd_api.g_ret_sts_error,
1901 SYSDATE,
1902 G_USER_ID,
1903 G_USER_ID,
1904 SYSDATE,
1905 G_LOGIN_ID,
1906 G_REQUEST_ID,
1907 G_PROGRAM_APPLICATION_ID,
1908 G_PROGRAM_ID,
1909 SYSDATE
1910 FROM ego_func_params_interface i
1911 WHERE NOT EXISTS (SELECT internal_name
1912 FROM ego_functions_b b
1913 WHERE ( b.internal_name = i.function_internal_name )
1914 )
1915 AND function_id IS NULL
1916 AND function_internal_name IS NOT NULL
1917 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1918 AND process_status = G_PROCESS_RECORD;
1919
1920 UPDATE ego_func_params_interface i
1921 SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
1925 )
1922 WHERE NOT EXISTS (SELECT internal_name
1923 FROM ego_functions_b b
1924 WHERE ( b.internal_name = i.function_internal_name )
1926 AND function_id IS NULL
1927 AND function_internal_name IS NOT NULL
1928 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1929 AND process_status = G_PROCESS_RECORD;
1930
1931 /* Error out rows that do not have both function_id and internal_name. */
1932 message_name := 'EGO_EF_FUNC_ID_INT_NAME_ERR';
1933 FND_MESSAGE.SET_NAME('EGO',message_name );
1934 message_text := FND_MESSAGE.GET;
1935
1936 INSERT INTO mtl_interface_errors
1937 (unique_id,
1938 transaction_id,
1939 table_name,
1940 message_name,
1941 error_message,
1942 bo_identifier,
1943 entity_identifier,
1944 message_type,
1945 creation_date,
1946 created_by,
1947 last_updated_by,
1948 last_update_date,
1949 last_update_login,
1950 request_id,
1951 program_application_id,
1952 program_id,
1953 program_update_date)
1954 SELECT mtl_system_items_interface_s.nextval,
1955 transaction_id,
1956 G_FUNC_PARAMS_TAB,
1957 message_name,
1958 message_text,
1959 g_bo_identifier_icc,
1960 G_ENTITY_ICC_FN_PARAM,
1961 fnd_api.g_ret_sts_error,
1962 SYSDATE,
1963 G_USER_ID,
1964 G_USER_ID,
1965 SYSDATE,
1966 G_LOGIN_ID,
1967 G_REQUEST_ID,
1968 G_PROGRAM_APPLICATION_ID,
1969 G_PROGRAM_ID,
1970 SYSDATE
1971 FROM ego_func_params_interface
1972 WHERE function_id IS NULL
1973 AND function_internal_name IS NULL
1974 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1975 AND process_status = G_PROCESS_RECORD;
1976
1977 UPDATE ego_func_params_interface i
1978 SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
1979 WHERE function_id IS NULL
1980 AND function_internal_name IS NULL
1981 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
1982 AND process_status = G_PROCESS_RECORD;
1983
1984
1985 /* For UPDATE and DELETE transactions, validate Parameter ID if it is not null. */
1986 message_name := 'EGO_EF_FP_ID_ERR';
1987 FND_MESSAGE.SET_NAME('EGO',message_name );
1988 message_text := FND_MESSAGE.GET;
1989
1990 INSERT INTO mtl_interface_errors
1991 (unique_id,
1992 transaction_id,
1993 table_name,
1994 message_name,
1995 error_message,
1996 bo_identifier,
1997 entity_identifier,
1998 message_type,
1999 creation_date,
2000 created_by,
2001 last_updated_by,
2002 last_update_date,
2003 last_update_login,
2004 request_id,
2005 program_application_id,
2006 program_id,
2007 program_update_date)
2008 SELECT mtl_system_items_interface_s.nextval,
2009 transaction_id,
2010 G_FUNC_PARAMS_TAB,
2011 message_name,
2012 message_text,
2013 g_bo_identifier_icc,
2014 G_ENTITY_ICC_FN_PARAM,
2015 fnd_api.g_ret_sts_error,
2016 SYSDATE,
2017 G_USER_ID,
2018 G_USER_ID,
2019 SYSDATE,
2020 G_LOGIN_ID,
2021 G_REQUEST_ID,
2022 G_PROGRAM_APPLICATION_ID,
2023 G_PROGRAM_ID,
2024 SYSDATE
2025 FROM ego_func_params_interface i
2026 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
2027 AND NOT EXISTS (SELECT *
2028 FROM ego_func_params_b b
2029 WHERE ( b.func_param_id = i.func_param_id )
2030 AND ((b.function_id = i.function_id AND i.function_id IS NOT NULL) OR
2031 (b.function_id IN (SELECT function_id FROM ego_functions_b
2032 WHERE (internal_name=i.function_internal_name))
2033 AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
2034 ) )
2035 AND func_param_id IS NOT NULL
2036 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
2037 AND process_status = G_PROCESS_RECORD;
2038
2039 UPDATE ego_func_params_interface i
2040 SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
2041 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
2042 AND NOT EXISTS (SELECT *
2043 FROM ego_func_params_b b
2044 WHERE ( b.func_param_id = i.func_param_id )
2045 AND ((b.function_id = i.function_id AND i.function_id IS NOT NULL) OR
2046 (b.function_id IN (SELECT function_id FROM ego_functions_b
2047 WHERE (internal_name=i.function_internal_name))
2048 AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
2049 ) )
2050 AND func_param_id IS NOT NULL
2051 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
2052 AND process_status = G_PROCESS_RECORD;
2053
2054 /* For UPDATE and DELETE transactions, validate Parameter Name if it is not null
2055 and Parameter Id is null. */
2056
2057 message_name := 'EGO_EF_FP_INT_NAME_INVL';
2058 FND_MESSAGE.SET_NAME('EGO',message_name );
2059 message_text := FND_MESSAGE.GET;
2060
2061 /* Bug 9647937. Modified the condition to validate Parameter Internal Name if it is not null
2062 and Parameter Id is null. Also, added logic to validate Parameter Internal Name
2063 against ego_func_params_interface_table also. */
2064
2065 /* Bug 9671972. Changing the Where clause to directly compare function_internal_name
2066 instead of function_id, while validating against ego_func_params_interface_table. */
2067
2068 INSERT INTO mtl_interface_errors
2069 (unique_id,
2070 transaction_id,
2071 table_name,
2072 message_name,
2073 error_message,
2074 bo_identifier,
2075 entity_identifier,
2076 message_type,
2077 creation_date,
2078 created_by,
2079 last_updated_by,
2080 last_update_date,
2081 last_update_login,
2082 request_id,
2083 program_application_id,
2084 program_id,
2085 program_update_date)
2086 SELECT mtl_system_items_interface_s.nextval,
2087 transaction_id,
2088 G_FUNC_PARAMS_TAB,
2089 message_name,
2090 message_text,
2091 g_bo_identifier_icc,
2092 G_ENTITY_ICC_FN_PARAM,
2093 fnd_api.g_ret_sts_error,
2094 SYSDATE,
2095 G_USER_ID,
2096 G_USER_ID,
2097 SYSDATE,
2098 G_LOGIN_ID,
2099 G_REQUEST_ID,
2100 G_PROGRAM_APPLICATION_ID,
2101 G_PROGRAM_ID,
2102 SYSDATE
2103 FROM ego_func_params_interface i
2104 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
2105 AND NOT EXISTS (SELECT internal_name
2106 FROM ego_func_params_b b
2107 WHERE ( b.internal_name = i.internal_name )
2108 AND ((b.function_id = i.function_id AND i.function_id IS NOT NULL) OR
2109 (b.function_id IN (SELECT function_id FROM ego_functions_b
2110 WHERE (internal_name=i.function_internal_name))
2111 AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
2112 )
2113 UNION
2114 SELECT internal_name
2115 FROM ego_func_params_interface ii
2116 WHERE (ii.internal_name = i.internal_name)
2117 AND ((ii.function_id = i.function_id AND i.function_id IS NOT NULL) OR
2118 (ii.function_internal_name = i.function_internal_name
2119 AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
2120 )
2121 AND transaction_type = G_CREATE_TRANSACTION
2122 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
2123 AND process_status = G_PROCESS_RECORD
2124 )
2125 AND func_param_id IS NULL
2126 AND internal_name IS NOT NULL
2127 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
2128 AND process_status = G_PROCESS_RECORD;
2129
2130 UPDATE ego_func_params_interface i
2131 SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
2132 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
2133 AND NOT EXISTS (SELECT internal_name
2134 FROM ego_func_params_b b
2135 WHERE ( b.internal_name = i.internal_name )
2136 AND ((b.function_id = i.function_id AND i.function_id IS NOT NULL) OR
2137 (b.function_id IN (SELECT function_id FROM ego_functions_b
2138 WHERE (internal_name=i.function_internal_name))
2139 AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
2140 )
2141 UNION
2142 SELECT internal_name
2143 FROM ego_func_params_interface ii
2144 WHERE (ii.internal_name = i.internal_name)
2145 AND ((ii.function_id = i.function_id AND i.function_id IS NOT NULL) OR
2146 (ii.function_internal_name = i.function_internal_name
2147 AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
2148 )
2149 AND transaction_type = G_CREATE_TRANSACTION
2150 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
2151 AND process_status = G_PROCESS_RECORD
2152 )
2153 AND func_param_id IS NULL
2154 AND internal_name IS NOT NULL
2155 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
2156 AND process_status = G_PROCESS_RECORD;
2157
2158 /* For UPDATE and DELETE transactions, error out rows that do not have both func_param_id and internal_name. */
2159 message_name := 'EGO_EF_FP_ID_INT_NAME_ERR';
2160 FND_MESSAGE.SET_NAME('EGO',message_name );
2161 message_text := FND_MESSAGE.GET;
2162
2163 INSERT INTO mtl_interface_errors
2164 (unique_id,
2165 transaction_id,
2166 table_name,
2167 message_name,
2168 error_message,
2169 bo_identifier,
2170 entity_identifier,
2171 message_type,
2172 creation_date,
2173 created_by,
2174 last_updated_by,
2175 last_update_date,
2176 last_update_login,
2177 request_id,
2178 program_application_id,
2179 program_id,
2180 program_update_date)
2181 SELECT mtl_system_items_interface_s.nextval,
2182 transaction_id,
2183 G_FUNC_PARAMS_TAB,
2184 message_name,
2185 message_text,
2186 g_bo_identifier_icc,
2187 G_ENTITY_ICC_FN_PARAM,
2188 fnd_api.g_ret_sts_error,
2189 SYSDATE,
2190 G_USER_ID,
2191 G_USER_ID,
2192 SYSDATE,
2193 G_LOGIN_ID,
2194 G_REQUEST_ID,
2195 G_PROGRAM_APPLICATION_ID,
2196 G_PROGRAM_ID,
2197 SYSDATE
2198 FROM ego_func_params_interface
2199 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
2200 AND func_param_id IS NULL
2201 AND internal_name IS NULL
2202 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
2203 AND process_status = G_PROCESS_RECORD;
2204
2205 UPDATE ego_func_params_interface
2206 SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
2207 WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
2208 AND func_param_id IS NULL
2209 AND internal_name IS NULL
2210 AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
2211 AND process_status = G_PROCESS_RECORD;
2212 write_debug(G_PCK_NAME||'.'||l_proc_name||'->'||' Exiting from the procedure');
2213 END bulk_validate_func_params;
2214 END EGO_FUNCTIONS_BULKLOAD_PVT;