1 PACKAGE BODY EGO_CHANGE_USER_ATTRS_PUB AS
2 /* $Header: EGOCHUAB.pls 120.3 2007/04/09 17:07:09 prgopala ship $ */
3
4
5
6 ------------------------------
7 -- Private Global Variables --
8 ------------------------------
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_CHANGE_USER_ATTRS_PUB';
12 G_API_VERSION NUMBER := 1.0;
13 G_ITEM_NAME VARCHAR2(20);
14 G_FUNCTION_NAME FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE := 'ENG_EDIT_CHANGE';
15
16 /*** The following two variables are for Error_Handler ***/
17 G_ENTITY_ID NUMBER;
18 G_ENTITY_CODE CONSTANT VARCHAR2(30) := 'CHANGE_USER_ATTRS_ENTITY_CODE';
19 G_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
20 G_PROGAM_APPLICATION_ID NUMBER := FND_GLOBAL.PROG_APPL_ID;
21 G_PROGAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
22 G_USER_NAME FND_USER.USER_NAME%TYPE := FND_GLOBAL.USER_NAME;
23 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
24 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
25 G_HZ_PARTY_ID VARCHAR2(30);
26 G_NO_CURRVAL_YET EXCEPTION;
27 G_NO_USER_NAME_TO_VALIDATE EXCEPTION;
28 G_NO_CHANGE_TO_VALIDATE EXCEPTION;
29 g_app_name VARCHAR2(3) := 'EGO'; --3070807
30 g_plsql_err VARCHAR2(17) := 'EGO_PLSQL_ERR';
31 g_pkg_name_token VARCHAR2(8) := 'PKG_NAME';
32 g_api_name_token VARCHAR2(8) := 'API_NAME';
33 g_sql_err_msg_token VARCHAR2(11) := 'SQL_ERR_MSG';
34
35 -------------------------------------
36 -- Pragma for Data Set ID function --
37 -------------------------------------
38 PRAGMA EXCEPTION_INIT (G_NO_CURRVAL_YET, -08002);
39
40
41
42 ----------------
43 -- Procedures --
44 ----------------
45
46 ----------------------------------------------------------------------
47 PROCEDURE Process_Change_User_Attrs_Data
48 (
49 ERRBUF OUT NOCOPY VARCHAR2
50 ,RETCODE OUT NOCOPY VARCHAR2
51 ,p_data_set_id IN NUMBER
52 ,p_debug_level IN NUMBER DEFAULT 0
53 ,p_purge_successful_lines IN VARCHAR2 DEFAULT FND_API.G_FALSE
54 ) IS
55
56 l_error_message_name VARCHAR2(30);
57 l_entity_index_counter NUMBER := 0;
58 l_header_or_line_counter NUMBER := 1;
59 l_header_row_exists BOOLEAN := FALSE;
60 l_line_row_exists BOOLEAN := FALSE;
61 l_prev_loop_org_id NUMBER;
62 l_prev_loop_change_id NUMBER;
63 l_prev_loop_change_line_id NUMBER;
64 l_prev_loop_row_identifier NUMBER;
65 l_at_start_of_instance BOOLEAN;
66 l_can_edit_this_instance VARCHAR2(1);
67 l_token_table ERROR_HANDLER.Token_Tbl_Type;
68 l_could_edit_prev_instance VARCHAR2(1);
69 l_at_start_of_row BOOLEAN;
70 p_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
71 p_line_pk_col_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
72 p_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
73 p_line_class_code_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
74 p_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
75 p_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
76 l_failed_row_id_buffer VARCHAR2(32767);
77 l_failed_row_id_list VARCHAR2(32767);
78 l_return_status VARCHAR2(1);
79 l_errorcode NUMBER;
80 l_msg_count NUMBER;
81 l_msg_data VARCHAR2(1000);
82 l_dynamic_sql VARCHAR2(32767);
83 p_line_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
84 p_line_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
85
86 -------------------------------------------------------------------------
87 -- For finding ChangeID using Organization ID and Change Number --
88 -------------------------------------------------------------------------
89 CURSOR change_num_to_id_cursor (cp_data_set_id IN NUMBER)
90 IS
91 SELECT DISTINCT ORGANIZATION_ID
92 ,CHANGE_NUMBER
93 FROM ENG_CHG_USR_ATR_INTERFACE
94 WHERE DATA_SET_ID = cp_data_set_id
95 AND PROCESS_STATUS = G_PS_IN_PROCESS
96 AND CHANGE_NUMBER IS NOT NULL
97 AND CHANGE_ID IS NULL;
98
99 ---------------------------------------------------------------
100 -- For reporting errors for all of the four conversion steps --
101 ---------------------------------------------------------------
102 CURSOR error_case_cursor (cp_data_set_id IN NUMBER)
103 IS
104 SELECT DISTINCT ORGANIZATION_CODE
105 ,ORGANIZATION_ID
106 ,CHANGE_NUMBER
107 ,CHANGE_ID
108 ,CHANGE_MGMT_TYPE_CODE
109 ,CHANGE_TYPE_ID
110 ,ROW_IDENTIFIER
111 ,CHANGE_LINE_ID
112 FROM ENG_CHG_USR_ATR_INTERFACE
113 WHERE DATA_SET_ID = cp_data_set_id
114 AND PROCESS_STATUS = G_PS_ERROR;
115
116 -------------------------------------------------------------------
117 -- For processing all rows that passed the four conversion steps --
118 -------------------------------------------------------------------
119 CURSOR data_set_cursor_header (cp_data_set_id IN NUMBER)
120 IS
121 SELECT TRANSACTION_ID
122 ,PROCESS_STATUS
123 ,ORGANIZATION_CODE
124 ,CHANGE_NUMBER
125 ,CHANGE_MGMT_TYPE_CODE
126 ,CHANGE_LINE_SEQUENCE_NUMBER
127 ,ATTR_GROUP_INT_NAME
128 ,ROW_IDENTIFIER
129 ,ATTR_INT_NAME
130 ,ATTR_VALUE_STR
131 ,ATTR_VALUE_NUM
132 ,ATTR_VALUE_DATE
133 ,ATTR_DISP_VALUE
134 ,TRANSACTION_TYPE
135 ,ORGANIZATION_ID
136 ,CHANGE_ID
137 ,CHANGE_TYPE_ID
138 ,ATTR_GROUP_ID
139 ,CHANGE_LINE_ID
140 FROM ENG_CHG_USR_ATR_INTERFACE
141 WHERE DATA_SET_ID = cp_data_set_id
142 AND CHANGE_LINE_ID is NULL
143 AND PROCESS_STATUS = G_PS_IN_PROCESS
144 ORDER BY ORGANIZATION_ID, CHANGE_ID,(DECODE (UPPER(TRANSACTION_TYPE),
145 'DELETE', 1,
146 'UPDATE', 2,
147 'SYNC', 3,
148 'CREATE', 4, 5)), ROW_IDENTIFIER;
149
150
151 CURSOR data_set_cursor_line (cp_data_set_id IN NUMBER)
152 IS
153 SELECT TRANSACTION_ID
154 ,PROCESS_STATUS
155 ,ORGANIZATION_CODE
156 ,CHANGE_NUMBER
157 ,CHANGE_MGMT_TYPE_CODE
158 ,CHANGE_LINE_SEQUENCE_NUMBER
159 ,ATTR_GROUP_INT_NAME
160 ,ROW_IDENTIFIER
161 ,ATTR_INT_NAME
162 ,ATTR_VALUE_STR
163 ,ATTR_VALUE_NUM
164 ,ATTR_VALUE_DATE
165 ,ATTR_DISP_VALUE
166 ,TRANSACTION_TYPE
167 ,ORGANIZATION_ID
168 ,CHANGE_ID
169 ,CHANGE_TYPE_ID
170 ,ATTR_GROUP_ID
171 ,CHANGE_LINE_ID
172 FROM ENG_CHG_USR_ATR_INTERFACE
173 WHERE DATA_SET_ID = cp_data_set_id
174 AND CHANGE_LINE_ID is NOT NULL
175 AND PROCESS_STATUS = G_PS_IN_PROCESS
176 ORDER BY ORGANIZATION_ID, CHANGE_LINE_ID,(DECODE (UPPER(TRANSACTION_TYPE),
177 'DELETE', 1,
178 'UPDATE', 2,
179 'SYNC', 3,
180 'CREATE', 4, 5)), ROW_IDENTIFIER;
181
182 CURSOR c_CheckChange(l_change_id NUMBER) IS
183 SELECT status_type,
184 approval_status_type
185 FROM eng_engineering_changes
186 WHERE change_id = l_change_id;
187
188 CURSOR c_CheckLine(l_change_line_id NUMBER) IS
189 SELECT status_code
190 FROM eng_change_lines
191 WHERE change_line_id = l_change_line_id;
192 BEGIN
193
194 -----------------------------------------------
195 -- Set this global variable once per session --
196 -----------------------------------------------
197 IF (G_HZ_PARTY_ID IS NULL) THEN
198 IF (G_USER_NAME IS NOT NULL) THEN
199
200 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
201 INTO G_HZ_PARTY_ID
202 FROM EGO_PEOPLE_V
203 WHERE USER_NAME = G_USER_NAME;
204
205 ELSE
206
207 RAISE G_NO_USER_NAME_TO_VALIDATE;
208
209 END IF;
210 END IF;
211
212
213
214 --======================--
215 -- ERROR_HANDLER SET-UP --
216 --======================--
217
218 ERROR_HANDLER.Initialize();
219 ERROR_HANDLER.Set_Bo_Identifier(EGO_USER_ATTRS_DATA_PVT.G_BO_IDENTIFIER);
220
221 -----------------------------------------------------------
222 -- If we're debugging, we have to set up a Debug session --
223 -----------------------------------------------------------
224 IF (p_debug_level > 0) THEN
225
226 EGO_USER_ATTRS_DATA_PVT.Set_Up_Debug_Session(G_ENTITY_ID, G_ENTITY_CODE,p_debug_level);
227
228 END IF;
229
230 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Concurrent Program', 1);
231
232
233
234 --===================================--
235 -- GETTING THE INTERFACE TABLE READY --
236 --===================================--
237
238 ---------------------------------------------------------------------
239 -- Mark all rows we'll be processing, and null out user input for --
240 -- the ORGANIZATION_ID column (so we can validate Organizations); --
241 -- also update Concurrent Request information for better tracking --
242 -- and update the "WHO" columns on the assumption that the current --
243 -- user is also the person who loaded this data set into the table --
244 ---------------------------------------------------------------------
245 UPDATE ENG_CHG_USR_ATR_INTERFACE
246 SET PROCESS_STATUS = G_PS_IN_PROCESS
247 ,ORGANIZATION_ID = NULL
248 ,CHANGE_TYPE_ID = NULL
249 ,CHANGE_ID = NULL
250 ,CHANGE_LINE_ID = NULL
251 ,REQUEST_ID = G_REQUEST_ID
252 ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
253 ,PROGRAM_ID = G_PROGAM_ID
254 ,PROGRAM_UPDATE_DATE = SYSDATE
255 ,CREATED_BY = G_USER_ID
256 ,LAST_UPDATED_BY = G_USER_ID
257 ,LAST_UPDATE_LOGIN = G_LOGIN_ID
258 WHERE DATA_SET_ID = p_data_set_id;
259
260
261 --==================================--
262 -- THE THREE PRELIMINARY CONVERSIONS --
263 --==================================--
264
265 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting conversions', 1);
266
267 ------------------------------------------------------------------
268 -- 1). Convert Organization Code to Organization ID
269 ------------------------------------------------------------------
270 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Org Code conversion', 2);
271
272 UPDATE ENG_CHG_USR_ATR_INTERFACE UAI
273 SET UAI.ORGANIZATION_ID = (SELECT MP.ORGANIZATION_ID
274 FROM MTL_PARAMETERS MP
275 WHERE MP.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE)
276 WHERE UAI.DATA_SET_ID = p_data_set_id
277 AND UAI.ORGANIZATION_CODE IS NOT NULL
278 AND EXISTS(SELECT MP2.ORGANIZATION_ID
279 FROM MTL_PARAMETERS MP2
280 WHERE MP2.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE);
281
282 ---------------------------------------------------------------------
283 -- Mark as errors all rows where we didn't get an Organization ID --
284 -- (marking errors as we go avoids further processing of bad rows) --
285 ---------------------------------------------------------------------
286 -- UPDATE ENG_CHG_USR_ATR_INTERFACE
287 -- SET PROCESS_STATUS = G_PS_ERROR
288 -- WHERE DATA_SET_ID = p_data_set_id
289 -- AND PROCESS_STATUS = G_PS_IN_PROCESS
290 -- AND ORGANIZATION_ID IS NULL;
291
292 -- Joseph George : Bug Fix for Change Management Import Bulk Loading
293 -- Bug No : 2873555, Base Bug
294
295 UPDATE ENG_CHG_USR_ATR_INTERFACE
296 SET PROCESS_STATUS = G_PS_ERROR
297 WHERE ROW_IDENTIFIER IN (SELECT DISTINCT ROW_IDENTIFIER
298 FROM ENG_CHG_USR_ATR_INTERFACE
299 WHERE DATA_SET_ID = p_data_set_id
300 AND PROCESS_STATUS = G_PS_IN_PROCESS
301 AND ORGANIZATION_ID IS NULL)
302 AND DATA_SET_ID = p_data_set_id;
303
304
305 -------------------------------------------------------------------------
306 -- 2). Convert Change Number to Change Id: this cursor selects --
307 -- distinct Organization ID and Change Number combinations (among those --
308 -- rows that are still valid, meaning we won't have any null Org IDs) --
309 -- and gets the Change ID for each combination also gets change Line Id
310 -- from change_line_sequence_number
311 -------------------------------------------------------------------------
312 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Number conversion', 2);
313
314 UPDATE ENG_CHG_USR_ATR_INTERFACE UAI
315 SET UAI.CHANGE_ID =
316 (SELECT EEC.CHANGE_ID
317 FROM ENG_ENGINEERING_CHANGES EEC
318 WHERE EEC.ORGANIZATION_ID = UAI.ORGANIZATION_ID
319 AND EEC.CHANGE_NOTICE = UAI.CHANGE_NUMBER
320 AND EEC.CHANGE_MGMT_TYPE_CODE = UAI.CHANGE_MGMT_TYPE_CODE
321 AND UAI.CHANGE_LINE_SEQUENCE_NUMBER IS NULL)
322 WHERE UAI.DATA_SET_ID = p_data_set_id
323 AND PROCESS_STATUS = G_PS_IN_PROCESS
324 AND EXISTS(SELECT EEC.CHANGE_ID
325 FROM ENG_ENGINEERING_CHANGES EEC
326 WHERE EEC.ORGANIZATION_ID = UAI.ORGANIZATION_ID
327 AND EEC.CHANGE_NOTICE = UAI.CHANGE_NUMBER
328 AND EEC.CHANGE_MGMT_TYPE_CODE = UAI.CHANGE_MGMT_TYPE_CODE
329 AND UAI.CHANGE_LINE_SEQUENCE_NUMBER IS NULL);
330
331
332 UPDATE ENG_CHG_USR_ATR_INTERFACE UAI SET
333 UAI.CHANGE_LINE_ID=
334 (SELECT ELV.CHANGE_LINE_ID
335 FROM ENG_CHANGE_LINES ELV,ENG_ENGINEERING_CHANGES EEC
336 WHERE
337 EEC.CHANGE_ID = ELV.CHANGE_ID
338 AND EEC.CHANGE_NOTICE = UAI.CHANGE_NUMBER
339 AND EEC.ORGANIZATION_ID = UAI.ORGANIZATION_ID
340 AND ELV.SEQUENCE_NUMBER = UAI.CHANGE_LINE_SEQUENCE_NUMBER
341 ),
342 UAI.CHANGE_ID=
343 (SELECT ELV.CHANGE_ID
344 FROM ENG_CHANGE_LINES ELV, ENG_ENGINEERING_CHANGES EEC
345 WHERE
346 EEC.CHANGE_ID = ELV.CHANGE_ID
347 AND EEC.CHANGE_NOTICE = UAI.CHANGE_NUMBER
348 AND EEC.ORGANIZATION_ID = UAI.ORGANIZATION_ID
349 AND ELV.SEQUENCE_NUMBER = UAI.CHANGE_LINE_SEQUENCE_NUMBER
350 )
351 WHERE
352 UAI.DATA_SET_ID = p_data_set_id
353 AND PROCESS_STATUS = G_PS_IN_PROCESS
354 AND EXISTS
355 (SELECT ELV.CHANGE_LINE_ID
356 FROM ENG_CHANGE_LINES ELV,ENG_ENGINEERING_CHANGES EEC
357 WHERE
358 EEC.CHANGE_ID = ELV.CHANGE_ID
359 AND EEC.CHANGE_NOTICE = UAI.CHANGE_NUMBER
360 AND EEC.ORGANIZATION_ID = UAI.ORGANIZATION_ID
361 AND ELV.SEQUENCE_NUMBER = UAI.CHANGE_LINE_SEQUENCE_NUMBER
362 );
363
364 ---------------------------------------------------------------------------
365 -- Mark as errors all rows where we didn't get Change_id --
366 -- (as always, ignoring rows that errored out earlier in our processing) --
367 ---------------------------------------------------------------------------
368 -- UPDATE ENG_CHG_USR_ATR_INTERFACE
369 -- SET PROCESS_STATUS = G_PS_ERROR
370 -- WHERE DATA_SET_ID = p_data_set_id
371 -- AND PROCESS_STATUS = G_PS_IN_PROCESS
372 -- AND CHANGE_ID IS NULL
373 -- AND CHANGE_LINE_ID IS NULL;
374
375 -- Joseph George : Bug Fix for Change Management Import Bulk Loading
376 -- Bug No : 2873555, Base Bug
377
378 UPDATE ENG_CHG_USR_ATR_INTERFACE
379 SET PROCESS_STATUS = G_PS_ERROR
380 WHERE ROW_IDENTIFIER IN (SELECT DISTINCT ROW_IDENTIFIER
381 FROM ENG_CHG_USR_ATR_INTERFACE
382 WHERE DATA_SET_ID = p_data_set_id
383 AND PROCESS_STATUS = G_PS_IN_PROCESS
384 AND CHANGE_ID IS NULL
385 AND CHANGE_LINE_SEQUENCE_NUMBER IS NULL)
386 AND DATA_SET_ID = p_data_set_id;
387
388
389 UPDATE ENG_CHG_USR_ATR_INTERFACE
390 SET PROCESS_STATUS = G_PS_ERROR
391 WHERE ROW_IDENTIFIER IN (SELECT DISTINCT ROW_IDENTIFIER
392 FROM ENG_CHG_USR_ATR_INTERFACE
393 WHERE DATA_SET_ID = p_data_set_id
394 AND PROCESS_STATUS = G_PS_IN_PROCESS
395 AND CHANGE_LINE_ID IS NULL
396 AND CHANGE_LINE_SEQUENCE_NUMBER IS NOT NULL)
397 AND DATA_SET_ID = p_data_set_id;
398
399 ------------------------------------------------------
400 -- 4). Find the Change Type Id for each Change and Change Line--
401 ------------------------------------------------------
402 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Type Id conversion', 2);
403
404 UPDATE ENG_CHG_USR_ATR_INTERFACE UAI
405 SET UAI.CHANGE_TYPE_ID =
406 (SELECT EEC.CHANGE_ORDER_TYPE_ID
407 FROM ENG_ENGINEERING_CHANGES EEC
408 WHERE EEC.ORGANIZATION_ID = UAI.ORGANIZATION_ID
409 AND EEC.CHANGE_ID = UAI.CHANGE_ID
410 AND UAI.CHANGE_LINE_SEQUENCE_NUMBER IS NULL)
411 WHERE UAI.DATA_SET_ID = p_data_set_id
412 AND PROCESS_STATUS = G_PS_IN_PROCESS
413 AND EXISTS(SELECT EEC.CHANGE_ORDER_TYPE_ID
414 FROM ENG_ENGINEERING_CHANGES EEC
415 WHERE EEC.ORGANIZATION_ID = UAI.ORGANIZATION_ID
416 AND EEC.CHANGE_ID = UAI.CHANGE_ID
417 AND UAI.CHANGE_LINE_SEQUENCE_NUMBER IS NULL);
418
419
420 UPDATE ENG_CHG_USR_ATR_INTERFACE UAI
421 SET UAI.CHANGE_TYPE_ID =
422 (SELECT EEC.CHANGE_TYPE_ID
423 FROM ENG_CHANGE_LINES_VL EEC
424 WHERE
425 EEC.CHANGE_LINE_ID = UAI.CHANGE_LINE_ID
426 )
427 WHERE UAI.DATA_SET_ID = p_data_set_id
428 AND PROCESS_STATUS = G_PS_IN_PROCESS
429 AND UAI.CHANGE_LINE_SEQUENCE_NUMBER IS NOT NULL;
430
431 ---------------------------------------------------------------------------
432 -- Mark as errors all rows where we didn't get Change Type ID --
433 -- (as always, ignoring rows that errored out earlier in our processing) --
434 ---------------------------------------------------------------------------
435 -- UPDATE ENG_CHG_USR_ATR_INTERFACE
436 -- SET PROCESS_STATUS = G_PS_ERROR
437 -- WHERE DATA_SET_ID = p_data_set_id
438 -- AND PROCESS_STATUS = G_PS_IN_PROCESS
439 -- AND CHANGE_TYPE_ID IS NULL;
440
441 -- Joseph George : Bug Fix for Change Management Import Bulk Loading
442 -- Bug No : 2873555, Base Bug
443
444 UPDATE ENG_CHG_USR_ATR_INTERFACE
445 SET PROCESS_STATUS = G_PS_ERROR
446 WHERE ROW_IDENTIFIER IN (SELECT DISTINCT ROW_IDENTIFIER
447 FROM ENG_CHG_USR_ATR_INTERFACE
448 WHERE DATA_SET_ID = p_data_set_id
449 AND PROCESS_STATUS = G_PS_IN_PROCESS
450 AND CHANGE_TYPE_ID IS NULL )
451 AND DATA_SET_ID = p_data_set_id;
452
453 --========================================--
454 -- ERROR REPORTING FOR FAILED CONVERSIONS --
455 --========================================--
456 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting conversion error-reporting', 1);
457
458 --------------------------------------------------------------------------
459 -- We fetch representative rows marked as errors and add error messages --
460 -- explaining the point in our conversion process at which each failed; --
461 -- note that to avoid multiple error messages for the same missing data --
462 -- we use DISTINCT in our cursor query and thus should only get one row --
463 -- for each ROW_IDENTIFIER (since Org Code, Item Number, Revision and --
464 -- Catalog Group Name should be the same for a given ROW_IDENTIFIER). --
465 --------------------------------------------------------------------------
466 FOR error_rec IN error_case_cursor(p_data_set_id)
467 LOOP
468
469 -------------------------------------------------------
470 -- 1). If Org ID is null we failed at the first step --
471 -------------------------------------------------------
472 IF (error_rec.ORGANIZATION_ID IS NULL) THEN
473
474 l_token_table(1).TOKEN_NAME := 'ORG_CODE';
475 l_token_table(1).TOKEN_VALUE := error_rec.ORGANIZATION_CODE;
476
477 -- Bug 2779881 Changed EGO_EF_BL_ORG_ID_ERR to ENG_EF_BL_ORG_ID_ERR
478
479 l_error_message_name := 'ENG_EF_BL_ORG_ID_ERR';
480
481 ----------------------------------------------------------------------------
482 -- 2). If Org ID is not null but Change ID is, we failed at the second step --
483 ----------------------------------------------------------------------------
484 ELSIF (error_rec.CHANGE_ID IS NULL AND error_rec.CHANGE_LINE_ID IS NULL) THEN
485
486 l_token_table(1).TOKEN_NAME := 'CHANGE_NUMBER';
487 l_token_table(1).TOKEN_VALUE := error_rec.CHANGE_NUMBER;
488 l_token_table(2).TOKEN_NAME := 'ORG_CODE';
489 l_token_table(2).TOKEN_VALUE := error_rec.ORGANIZATION_CODE;
490
491 -- Bug 2779881 Changed EGO_EF_BL_CHANGE_ID_ERR to ENG_EF_BL_ORG_ID_ERR
492
493 l_error_message_name := 'ENG_EF_BL_CHANGE_ID_ERR';
494
495 ---------------------------------------------------------------------------------
496 -- 3). If we got everything but Change Type Id, we failed at the fourth step --
497 ---------------------------------------------------------------------------------
498 ELSIF (error_rec.CHANGE_TYPE_ID IS NULL) THEN
499
500 l_token_table(1).TOKEN_NAME := 'CHANGE_MGMT_CODE';
501 l_token_table(1).TOKEN_VALUE := error_rec.CHANGE_MGMT_TYPE_CODE;
502 l_token_table(2).TOKEN_NAME := 'CHANGE_NUMBER';
503 l_token_table(2).TOKEN_VALUE := error_rec.CHANGE_NUMBER;
504 l_token_table(3).TOKEN_NAME := 'ORG_CODE';
505 l_token_table(3).TOKEN_VALUE := error_rec.ORGANIZATION_CODE;
506
507 -- Bug 2779881 Changed EGO_EF_BL_CHANGE_TYPE_ID_ERR to ENG_EF_BL_CHANGE_TYPE_ID_ERR
508
509 l_error_message_name := 'ENG_EF_BL_CHANGE_TYPE_ID_ERR';
510
511 END IF;
512
513 -- Bug 2779881 Changed Application EGO to ENG
514
515 ERROR_HANDLER.Add_Error_Message(
516 p_message_name => l_error_message_name
517 ,p_application_id => 'ENG'
518 ,p_token_tbl => l_token_table
519 ,p_message_type => FND_API.G_RET_STS_ERROR
520 ,p_row_identifier => error_rec.ROW_IDENTIFIER
521 ,p_entity_id => G_ENTITY_ID
522 ,p_entity_code => G_ENTITY_CODE
523 );
524
525 END LOOP;
526
527 --=====================================--
528 -- LOOP PROCESSING OF STILL-VALID ROWS --
529 --=====================================--
530 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting loop processing of valid rows', 1);
531
532 ------------------------------------------------------------------
533 -- The interface table stores the Attribute data in a redundant --
534 -- form; we loop through its rows flattening the data out and --
535 -- building appropriate objects so that every time we reach the --
536 -- end of a row subset for a particular Item instance, we can --
537 -- call EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data() with --
538 -- the accumulated objects we've built in previous loops. --
539 ------------------------------------------------------------------
540
541
542 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac before calling Header loop', 1);
543
544 FOR attr_rec IN data_set_cursor_header(p_data_set_id)
545 LOOP
546 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside Header loop', 1);
547 l_header_row_exists := TRUE;
548
549 ------------------------------------------------------
550 -- Figure out whether we're starting a new instance --
551 ------------------------------------------------------
552 l_at_start_of_instance := (l_prev_loop_org_id IS NULL OR
553 l_prev_loop_org_id <> attr_rec.ORGANIZATION_ID OR
554 l_prev_loop_change_id <> attr_rec.CHANGE_ID);
555
556 IF (l_at_start_of_instance) THEN
557 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside Header loop start_of_instance', 1);
558
559 l_entity_index_counter := l_entity_index_counter + 1;
560
561 ------------------------------------------------------------------
562 -- Determine whether the current user has sufficient privileges --
563 -- to update User Attribute values for the current instance; if --
564 -- not, we won't process any rows for the current instance, and --
565 -- we'll move on to the next instance (because the user may or --
566 -- may not have sufficient privileges on the next instance) --
567 ------------------------------------------------------------------
568 l_could_edit_prev_instance := l_can_edit_this_instance;
569 --uncommenting the code for bug 5239327
570 G_ITEM_NAME := 'ENG_CHANGE';
571 l_can_edit_this_instance := EGO_DATA_SECURITY.Check_Function(
572 p_api_version => G_API_VERSION
573 ,p_function => G_FUNCTION_NAME
574 ,p_object_name => G_ITEM_NAME
575 ,p_instance_pk1_value => attr_rec.CHANGE_ID
576 -- ,p_instance_pk2_value => attr_rec.ORGANIZATION_ID
577 ,p_user_name => G_HZ_PARTY_ID
578 );
579 --uncommenting the code for bug 5239327
580 -- l_can_edit_this_instance := 'T';
581
582 FOR ECO IN c_CheckChange(attr_rec.CHANGE_ID)
583 LOOP
584 IF (ECO.status_type = 5 OR ECO.status_type = 6 OR ECO.status_type = 7 OR ECO.approval_status_type = 3 OR ECO.approval_status_type = 5) THEN
585 l_can_edit_this_instance := 'F';
586 END IF;
587 END LOOP;
588 --------------------------------------------------------------------
589 -- We do an inverted IF check so that we can catch the case where --
590 -- l_can_edit_this_instance is NULL and report an error message --
591 --------------------------------------------------------------------
592 IF (l_can_edit_this_instance = 'T') THEN
593
594 NULL;
595
596 ELSE
597
598 -------------------------------------------------------------------------
599 -- Update the status of all rows for this instance to reflect the fact --
600 -- that the entire instance has a security error; we would prefer to --
601 -- do this update row-by-row rather than as a manual update, because --
602 -- updating a cursor means only doing a single update when the cursor --
603 -- is released, whereas doing our own update for each failed instance --
604 -- results in a DML per instance; however, we cannot do so because our --
605 -- call to Process_User_Attrs_Data includes a commit, and FOR UPDATE --
606 -- cursors can't handle commits done in mid-looping. --
607 -------------------------------------------------------------------------
608
609 UPDATE ENG_CHG_USR_ATR_INTERFACE
610 SET PROCESS_STATUS = G_PS_ERROR
611 WHERE DATA_SET_ID = p_data_set_id
612 AND ORGANIZATION_ID = attr_rec.ORGANIZATION_ID
613 AND CHANGE_ID = attr_rec.CHANGE_ID;
614
615 ------------------------------------------------
616 -- We add the error message once per instance --
617 ------------------------------------------------
618 IF (l_can_edit_this_instance = 'F') THEN
619
620 -- Bug 2779881 Changed EGO_EF_BL_NO_PRIVS_ON_INSTANCE to ENG_EF_BL_NO_PRIVS_ON_INSTANCE
621
622 l_error_message_name := 'ENG_EF_BL_NO_PRIVS_ON_INSTANCE';
623
624 ELSE
625
626 -- Bug 2779881 Changed EGO_EF_BL_PRIV_CHECK_ERROR to ENG_EF_BL_PRIV_CHECK_ERROR
627
628 l_error_message_name := 'ENG_EF_BL_PRIV_CHECK_ERROR';
629
630 END IF;
631
632 l_token_table(1).TOKEN_NAME := 'USER_NAME';
633 l_token_table(1).TOKEN_VALUE := G_USER_NAME;
634 l_token_table(2).TOKEN_NAME := 'CHANGE_NUMBER';
635 l_token_table(2).TOKEN_VALUE := attr_rec.CHANGE_NUMBER;
636 l_token_table(3).TOKEN_NAME := 'ORG_CODE';
637 l_token_table(3).TOKEN_VALUE := attr_rec.ORGANIZATION_CODE;
638
639 ERROR_HANDLER.Add_Error_Message(
640 p_message_name => l_error_message_name
641 ,p_application_id => 'ENG'
642 ,p_token_tbl => l_token_table
643 ,p_message_type => FND_API.G_RET_STS_ERROR
644 ,p_entity_id => G_ENTITY_ID
645 ,p_entity_index => l_entity_index_counter
646 ,p_entity_code => G_ENTITY_CODE
647 );
648
649 END IF;
650
651 ------------------------------------------------------------------
652 -- The VERY first loop through, we want to build arrays for the --
653 -- Primary Key columns and the Classification Code columns; for --
654 -- every subsequent instance, we just update the values. --
655 -- We also build Attr Row and Attr Data tables the first time --
656 -- through, which we then clear out (rather than re-allocating) --
657 -- at the start of all subsequent instances. --
658 ------------------------------------------------------------------
659 G_ITEM_NAME := 'ENG_CHANGE';
660 IF (l_prev_loop_org_id IS NULL) THEN
661 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside Header loop l_prev_loop_org_id IS NULL', 1);
662
663 p_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
664 EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_ID', attr_rec.CHANGE_ID)
665 );
666 p_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
667 EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_TYPE_ID', attr_rec.CHANGE_TYPE_ID));
668 p_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
669 p_attributes_data_table := EGO_USER_ATTR_DATA_TABLE();
670
671 ELSE
672
673 IF (l_could_edit_prev_instance = 'T') THEN
674
675 -------------------------------------------------------------------------
676 -- Since this is the start of an instance other than the first, we are --
677 -- ready to process the data we've collected for the previous instance --
678 -- (note that since we're always calling for the previous instance, we --
679 -- will need one final call after we're done looping through all rows; --
680 -- note also that we make sure the user passed the security check) --
681 -------------------------------------------------------------------------
682 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside Header loop previous instance processing ', 1);
683 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
684 (
685 p_api_version => G_API_VERSION
686 ,p_object_name => G_ITEM_NAME
687 ,p_attributes_row_table => p_attributes_row_table
688 ,p_attributes_data_table => p_attributes_data_table
689 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
690 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
691 ,p_entity_id => G_ENTITY_ID
692 ,p_entity_index => l_entity_index_counter
693 ,p_entity_code => G_ENTITY_CODE
694 ,p_debug_level => p_debug_level
695 ,p_commit => FND_API.G_TRUE
696 ,x_failed_row_id_list => l_failed_row_id_buffer
697 ,x_return_status => l_return_status
698 ,x_errorcode => l_errorcode
699 ,x_msg_count => l_msg_count
700 ,x_msg_data => l_msg_data
701 );
702
703 ------------------------------------------------------------------------
704 -- If any rows for this instance failed, we add their ROW_IDENTIFIERs --
705 -- to a master list that we will eventually use to mark as errors all --
706 -- rows whose ROW_IDENTIFIERs appear in the list --
707 ------------------------------------------------------------------------
708 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
709
710 l_failed_row_id_list := l_failed_row_id_list || l_failed_row_id_buffer || ',';
711
712 END IF;
713 END IF;
714
715 ------------------------------------------------------------------
716 -- Now we update the Primary Key and Classification Code column --
717 -- values, and we clear out the Attr Row and Attr Data tables --
718 ------------------------------------------------------------------
719
720 p_pk_column_name_value_pairs(1).VALUE := attr_rec.CHANGE_ID;
721 -- p_pk_column_name_value_pairs(2).VALUE := attr_rec.ORGANIZATION_ID;
722 p_class_code_name_value_pairs(1).VALUE := attr_rec.CHANGE_TYPE_ID;
723
724 p_attributes_row_table.DELETE;
725 p_attributes_data_table.DELETE;
726
727 END IF;
728 END IF;
729
730 IF (l_can_edit_this_instance = 'T') THEN
731 -----------------------------------------------------
732 -- Figure out whether we're now starting a new row --
733 -----------------------------------------------------
734 l_at_start_of_row := (l_prev_loop_row_identifier IS NULL OR
735 (l_prev_loop_row_identifier <> attr_rec.ROW_IDENTIFIER) OR
736 (l_prev_loop_row_identifier = attr_rec.ROW_IDENTIFIER AND l_prev_loop_change_id <> attr_rec.CHANGE_ID));
737
738 -------------------------------------------
739 -- Build an Attr Row Object for each row --
740 -------------------------------------------
741 IF (l_at_start_of_row) THEN
742 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside Header loop starting a new row ', 1);
743 p_attributes_row_table.EXTEND();
744
745 p_attributes_row_table(p_attributes_row_table.LAST) := EGO_USER_ATTR_ROW_OBJ(
746 attr_rec.ROW_IDENTIFIER
747 ,attr_rec.ATTR_GROUP_ID
748 ,703
749 ,'ENG_CHANGEMGMT_GROUP'
750 ,attr_rec.ATTR_GROUP_INT_NAME
751 ,null
752 ,null
753 ,null
754 ,null
755 ,null
756 ,null
757 ,attr_rec.TRANSACTION_TYPE
758 );
759
760 END IF;
761
762 ---------------------------------------------------------------
763 -- Add an Attr Data object to the Attr Data table every time --
764 ---------------------------------------------------------------
765 p_attributes_data_table.EXTEND();
766 p_attributes_data_table(p_attributes_data_table.LAST) := EGO_USER_ATTR_DATA_OBJ(
767 attr_rec.ROW_IDENTIFIER
768 ,attr_rec.ATTR_INT_NAME
769 ,attr_rec.ATTR_VALUE_STR
770 ,attr_rec.ATTR_VALUE_NUM
771 ,attr_rec.ATTR_VALUE_DATE
772 ,attr_rec.ATTR_DISP_VALUE
773 ,null --Bug 2775504 Amanjit added parameter for argument ATTR_UNIT_OF_MEASURE
774 ,attr_rec.TRANSACTION_ID
775 );
776 END IF;
777
778 ------------------------------------------------------
779 -- Update these variables for the next loop through --
780 ------------------------------------------------------
781 l_prev_loop_org_id := attr_rec.ORGANIZATION_ID;
782 l_prev_loop_change_id := attr_rec.CHANGE_ID;
783 l_prev_loop_row_identifier := attr_rec.ROW_IDENTIFIER;
784 END LOOP;
785
786
787 -----------------------------------------------------------
788 -- We have to call this procedure one last time with the --
789 -- data we collected in our loops for the last instance; --
790 -- this time we pass p_log_errors as TRUE so we can log --
791 -- all errors accumulated through our previous loops --
792 -----------------------------------------------------------
793 IF (l_can_edit_this_instance = 'T'and l_header_row_exists) THEN
794
795 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
796 (
797 p_api_version => G_API_VERSION
798 ,p_object_name => G_ITEM_NAME
799 ,p_attributes_row_table => p_attributes_row_table
800 ,p_attributes_data_table => p_attributes_data_table
801 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
802 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
803 ,p_entity_id => G_ENTITY_ID
804 ,p_entity_index => l_entity_index_counter
805 ,p_entity_code => G_ENTITY_CODE
806 ,p_debug_level => p_debug_level
807 ,p_commit => FND_API.G_TRUE
808 ,x_failed_row_id_list => l_failed_row_id_buffer
809 ,x_return_status => l_return_status
810 ,x_errorcode => l_errorcode
811 ,x_msg_count => l_msg_count
812 ,x_msg_data => l_msg_data
813 );
814
815
816 l_header_row_exists := FALSE;
817 l_prev_loop_org_id := NULL;
818 l_prev_loop_change_id := NULL;
819 l_prev_loop_row_identifier := NULL;
820
821 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Done with processing of final Change instance', 1);
822
823 ------------------------------------------------------------------------
824 -- If any rows for this instance failed, we add their ROW_IDENTIFIERs --
825 -- to our master list, which we will then use to mark as errors all --
826 -- rows whose ROW_IDENTIFIERs appear in the list --
827 ------------------------------------------------------------------------
828 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
829
830 l_failed_row_id_list := l_failed_row_id_list || l_failed_row_id_buffer || ',';
831
832 END IF;
833 END IF;
834 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac before calling Line loop', 1);
835
836 FOR attr_rec IN data_set_cursor_line(p_data_set_id)
837 LOOP
838 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside Line loop', 1);
839 l_line_row_exists := TRUE;
840
841 ------------------------------------------------------
842 -- Figure out whether we're starting a new instance --
843 ------------------------------------------------------
844 l_at_start_of_instance := (l_prev_loop_org_id IS NULL OR
845 l_prev_loop_org_id <> attr_rec.ORGANIZATION_ID OR
846 l_prev_loop_change_line_id <> attr_rec.CHANGE_LINE_ID);
847
848 IF (l_at_start_of_instance) THEN
849 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside IF (l_at_start_of_instance) loop', 1);
850
851 l_entity_index_counter := l_entity_index_counter + 1;
852
853 ------------------------------------------------------------------
854 -- Determine whether the current user has sufficient privileges --
855 -- to update User Attribute values for the current instance; if --
856 -- not, we won't process any rows for the current instance, and --
857 -- we'll move on to the next instance (because the user may or --
858 -- may not have sufficient privileges on the next instance) --
859 ------------------------------------------------------------------
860 l_could_edit_prev_instance := l_can_edit_this_instance;
861 /*
862 l_can_edit_this_instance := EGO_DATA_SECURITY.Check_Function(
863 p_api_version => G_API_VERSION
864 ,p_function => G_FUNCTION_NAME
865 ,p_object_name => G_ITEM_NAME
866 ,p_instance_pk1_value => attr_rec.CHANGE_ID
867 ,p_instance_pk2_value => attr_rec.ORGANIZATION_ID
868 ,p_user_name => G_HZ_PARTY_ID
869 );
870 */
871 l_can_edit_this_instance := 'T';
872 FOR ECO IN c_CheckChange(attr_rec.CHANGE_ID)
873 LOOP
874 IF (ECO.status_type = 5 OR ECO.status_type = 6 OR ECO.status_type = 7 OR ECO.approval_status_type = 3 OR ECO.approval_status_type = 5) THEN
875 l_can_edit_this_instance := 'F';
876 END IF;
877 END LOOP;
878
879 FOR ECO IN c_CheckLine(attr_rec.CHANGE_LINE_ID)
880 LOOP
881 IF (ECO.status_code = 5 OR ECO.status_code = 11) THEN
882 l_can_edit_this_instance := 'F';
883 END IF;
884 END LOOP;
885 --------------------------------------------------------------------
886 -- We do an inverted IF check so that we can catch the case where --
887 -- l_can_edit_this_instance is NULL and report an error message --
888 --------------------------------------------------------------------
889 IF (l_can_edit_this_instance = 'T') THEN
890
891 NULL;
892
893 ELSE
894
895 -------------------------------------------------------------------------
896 -- Update the status of all rows for this instance to reflect the fact --
897 -- that the entire instance has a security error; we would prefer to --
898 -- do this update row-by-row rather than as a manual update, because --
899 -- updating a cursor means only doing a single update when the cursor --
900 -- is released, whereas doing our own update for each failed instance --
901 -- results in a DML per instance; however, we cannot do so because our --
902 -- call to Process_User_Attrs_Data includes a commit, and FOR UPDATE --
903 -- cursors can't handle commits done in mid-looping. --
904 -------------------------------------------------------------------------
905
906 UPDATE ENG_CHG_USR_ATR_INTERFACE
907 SET PROCESS_STATUS = G_PS_ERROR
908 WHERE DATA_SET_ID = p_data_set_id
909 AND ORGANIZATION_ID = attr_rec.ORGANIZATION_ID
910 AND CHANGE_LINE_ID = attr_rec.CHANGE_LINE_ID;
911
912 ------------------------------------------------
913 -- We add the error message once per instance --
914 ------------------------------------------------
915 IF (l_can_edit_this_instance = 'F') THEN
916
917 -- Bug 2779881 Changed EGO_EF_BL_NO_PRIVS_ON_INSTANCE to ENG_EF_BL_NO_PRIVS_ON_INSTANCE
918
919 l_error_message_name := 'ENG_EF_BL_NO_PRIVS_ON_INSTANCE';
920
921 ELSE
922
923 -- Bug 2779881 Changed EGO_EF_BL_PRIV_CHECK_ERROR to ENG_EF_BL_PRIV_CHECK_ERROR
924
925 l_error_message_name := 'ENG_EF_BL_PRIV_CHECK_ERROR';
926
927 END IF;
928
929 l_token_table(1).TOKEN_NAME := 'USER_NAME';
930 l_token_table(1).TOKEN_VALUE := G_USER_NAME;
931 l_token_table(2).TOKEN_NAME := 'CHANGE_NUMBER';
932 l_token_table(2).TOKEN_VALUE := attr_rec.CHANGE_NUMBER;
933 l_token_table(3).TOKEN_NAME := 'ORG_CODE';
934 l_token_table(3).TOKEN_VALUE := attr_rec.ORGANIZATION_CODE;
935
936 ERROR_HANDLER.Add_Error_Message(
937 p_message_name => l_error_message_name
938 ,p_application_id => 'ENG'
939 ,p_token_tbl => l_token_table
940 ,p_message_type => FND_API.G_RET_STS_ERROR
941 ,p_entity_id => G_ENTITY_ID
942 ,p_entity_index => l_entity_index_counter
943 ,p_entity_code => G_ENTITY_CODE
944 );
945
946 END IF;
947
948 ------------------------------------------------------------------
949 -- The VERY first loop through, we want to build arrays for the --
950 -- Primary Key columns and the Classification Code columns; for --
951 -- every subsequent instance, we just update the values. --
952 -- We also build Attr Row and Attr Data tables the first time --
953 -- through, which we then clear out (rather than re-allocating) --
954 -- at the start of all subsequent instances. --
955 -----------------------------------------------------------------
956 G_ITEM_NAME := 'ENG_CHANGE_LINE';
957 IF (l_prev_loop_org_id IS NULL ) THEN
958
959 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside setting p_line_pk_col_name_value_pairs', 1);
960
961 p_line_pk_col_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
962 EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_LINE_ID', attr_rec.CHANGE_LINE_ID)
963 );
964
965 p_line_class_code_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
966 EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_TYPE_ID', attr_rec.CHANGE_TYPE_ID)
967
968 );
969 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside intilizing attribute and data table ', 1);
970 p_line_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
971 p_line_attributes_data_table := EGO_USER_ATTR_DATA_TABLE();
972
973 ELSE
974
975 IF (l_could_edit_prev_instance = 'T') THEN
976
977 -------------------------------------------------------------------------
978 -- Since this is the start of an instance other than the first, we are --
979 -- ready to process the data we've collected for the previous instance --
980 -- (note that since we're always calling for the previous instance, we --
981 -- will need one final call after we're done looping through all rows; --
982 -- note also that we make sure the user passed the security check) --
983 -------------------------------------------------------------------------
984 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside process the data collected for the previous instance ', 1);
985 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
986 (
987 p_api_version => G_API_VERSION
988 ,p_object_name => G_ITEM_NAME
989 ,p_attributes_row_table => p_line_attributes_row_table
990 ,p_attributes_data_table => p_line_attributes_data_table
991 ,p_pk_column_name_value_pairs => p_line_pk_col_name_value_pairs
992 ,p_class_code_name_value_pairs => p_line_class_code_value_pairs
993 ,p_entity_id => G_ENTITY_ID
994 ,p_entity_index => l_entity_index_counter
995 ,p_entity_code => G_ENTITY_CODE
996 ,p_debug_level => p_debug_level
997 ,p_commit => FND_API.G_TRUE
998 ,x_failed_row_id_list => l_failed_row_id_buffer
999 ,x_return_status => l_return_status
1000 ,x_errorcode => l_errorcode
1001 ,x_msg_count => l_msg_count
1002 ,x_msg_data => l_msg_data
1003 );
1004
1005 ------------------------------------------------------------------------
1006 -- If any rows for this instance failed, we add their ROW_IDENTIFIERs --
1007 -- to a master list that we will eventually use to mark as errors all --
1008 -- rows whose ROW_IDENTIFIERs appear in the list --
1009 ------------------------------------------------------------------------
1010 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1011
1012 l_failed_row_id_list := l_failed_row_id_list || l_failed_row_id_buffer || ',';
1013
1014 END IF;
1015 END IF;
1016
1017 ------------------------------------------------------------------
1018 -- Now we update the Primary Key and Classification Code column --
1019 -- values, and we clear out the Attr Row and Attr Data tables --
1020 ------------------------------------------------------------------
1021 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside updating Primary key and classification ', 1);
1022
1023 p_line_pk_col_name_value_pairs(1).VALUE := attr_rec.CHANGE_LINE_ID;
1024
1025 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside updating value_pairs(1)iCHANGE_LINE_ID ', 1);
1026 -- p_line_pk_col_name_value_pairs(2).VALUE := attr_rec.ORGANIZATION_ID;
1027
1028 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside updating value_pairs(2) ORGANIZATION_ID ', 1);
1029 p_line_class_code_value_pairs(1).VALUE := attr_rec.CHANGE_TYPE_ID;
1030
1031 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac inside deleting attribute and data tables ', 1);
1032
1033 p_line_attributes_row_table.DELETE;
1034 p_line_attributes_data_table.DELETE;
1035 END IF;
1036 END IF;
1037
1038 IF (l_can_edit_this_instance = 'T') THEN
1039 -----------------------------------------------------
1040 -- Figure out whether we're now starting a new row --
1041 -----------------------------------------------------
1042 l_at_start_of_row := (l_prev_loop_row_identifier IS NULL OR
1043 l_prev_loop_row_identifier <> attr_rec.ROW_IDENTIFIER OR
1044 l_prev_loop_change_line_id <> attr_rec.CHANGE_LINE_ID);
1045
1046 -------------------------------------------
1047 -- Build an Attr Row Object for each row --
1048 -------------------------------------------
1049 IF (l_at_start_of_row) THEN
1050 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac Before extending the attributes_row_table :',3);
1051
1052 p_line_attributes_row_table.EXTEND();
1053 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac after extending the attributes_row_table :',3);
1054
1055 p_line_attributes_row_table(p_line_attributes_row_table.LAST) := EGO_USER_ATTR_ROW_OBJ(
1056 attr_rec.ROW_IDENTIFIER
1057 ,attr_rec.ATTR_GROUP_ID
1058 ,703
1059 ,'ENG_LINEMGMT_GROUP'
1060 ,attr_rec.ATTR_GROUP_INT_NAME
1061 ,null
1062 ,null
1063 ,null
1064 ,null
1065 ,null
1066 ,null ,attr_rec.TRANSACTION_TYPE
1067 );
1068
1069 END IF;
1070
1071 ---------------------------------------------------------------
1072 -- Add an Attr Data object to the Attr Data table every time --
1073 ---------------------------------------------------------------
1074 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac before extending attribute tabl ', 1);
1075
1076 p_line_attributes_data_table.EXTEND();
1077 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac before after attribute tabl ', 1);
1078 p_line_attributes_data_table(p_line_attributes_data_table.LAST) := EGO_USER_ATTR_DATA_OBJ(
1079 attr_rec.ROW_IDENTIFIER
1080 ,attr_rec.ATTR_INT_NAME
1081 ,attr_rec.ATTR_VALUE_STR
1082 ,attr_rec.ATTR_VALUE_NUM
1083 ,attr_rec.ATTR_VALUE_DATE
1084 ,attr_rec.ATTR_DISP_VALUE
1085 ,null --Bug 2775504 Amanjit added parameter for argument ATTR_UNIT_OF_MEASURE
1086 ,attr_rec.TRANSACTION_ID
1087 );
1088 END IF;
1089
1090 ------------------------------------------------------
1091 -- Update these variables for the next loop through --
1092 ------------------------------------------------------
1093 l_prev_loop_org_id := attr_rec.ORGANIZATION_ID;
1094 l_prev_loop_change_line_id := attr_rec.CHANGE_LINE_ID;
1095 l_prev_loop_row_identifier := attr_rec.ROW_IDENTIFIER;
1096
1097 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac before end of line loop', 1);
1098 END LOOP;
1099
1100
1101 -----------------------------------------------------------
1102 -- We have to call this procedure one last time with the --
1103 -- data we collected in our loops for the last instance; --
1104 -- this time we pass p_log_errors as TRUE so we can log --
1105 -- all errors accumulated through our previous loops --
1106 -----------------------------------------------------------
1107 IF (l_can_edit_this_instance = 'T'AND l_line_row_exists ) THEN
1108 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Sac before final calling :',3);
1109
1110
1111 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
1112 (
1113 p_api_version => G_API_VERSION
1114 ,p_object_name => G_ITEM_NAME
1115 ,p_attributes_row_table => p_line_attributes_row_table
1116 ,p_attributes_data_table => p_line_attributes_data_table
1117 ,p_pk_column_name_value_pairs => p_line_pk_col_name_value_pairs
1118 ,p_class_code_name_value_pairs => p_line_class_code_value_pairs
1119 ,p_entity_id => G_ENTITY_ID
1120 ,p_entity_index => l_entity_index_counter
1121 ,p_entity_code => G_ENTITY_CODE
1122 ,p_debug_level => p_debug_level
1123 ,p_commit => FND_API.G_TRUE
1124 ,x_failed_row_id_list => l_failed_row_id_buffer
1125 ,x_return_status => l_return_status
1126 ,x_errorcode => l_errorcode
1127 ,x_msg_count => l_msg_count
1128 ,x_msg_data => l_msg_data
1129 );
1130
1131 l_line_row_exists := FALSE;
1132 l_prev_loop_org_id := NULL;
1133 l_prev_loop_change_line_id := NULL;
1134 l_prev_loop_row_identifier := NULL;
1135
1136
1137
1138 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Done with processing of final line instance', 1);
1139
1140 ------------------------------------------------------------------------
1141 -- If any rows for this instance failed, we add their ROW_IDENTIFIERs --
1142 -- to our master list, which we will then use to mark as errors all --
1143 -- rows whose ROW_IDENTIFIERs appear in the list --
1144 ------------------------------------------------------------------------
1145 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1146
1147 l_failed_row_id_list := l_failed_row_id_list || l_failed_row_id_buffer || ',';
1148
1149 END IF;
1150 END IF;
1151
1152 IF (LENGTH(l_failed_row_id_list) > 0) THEN
1153
1154 -----------------------------------------------------------------------
1155 -- Strip off any trailing ',' from the failed ROW_IDENTIFIER list... --
1156 -----------------------------------------------------------------------
1157 l_failed_row_id_list := SUBSTR(l_failed_row_id_list, 1, LENGTH(l_failed_row_id_list) - LENGTH(','));
1158
1159 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('List of all ROW_IDENTIFIERs that failed: '||l_failed_row_id_list, 3);
1160
1161 ---------------------------------------------------------------
1162 -- ...and then use it to mark as errors all rows in the list --
1163 -- (note that we have to use dynamic SQL because 1). static --
1164 -- SQL treats the failed Row ID list as a string instead of --
1165 -- a list of numbers, and 2). bulk-binding would cause us to --
1166 -- execute one SQL statement per failed Row ID. Dynamic SQL --
1167 -- only executes one SQL statement for a given call to our --
1168 -- concurrent program--so the fact that our failed Row IDs --
1169 -- aren't passed as a bind variable doesn't matter, because --
1170 -- the statement won't get parsed more than once anyway). --
1171 ---------------------------------------------------------------
1172 l_dynamic_sql := 'UPDATE ENG_CHG_USR_ATR_INTERFACE'||
1173 ' SET PROCESS_STATUS = '|| G_PS_ERROR ||
1174 ' WHERE DATA_SET_ID = :1'||
1175 ' AND ROW_IDENTIFIER IN ('|| l_failed_row_id_list || ')';
1176
1177 EXECUTE IMMEDIATE l_dynamic_sql USING p_data_set_id;
1178
1179 END IF;
1180
1181 IF (FND_API.To_Boolean(p_purge_successful_lines)) THEN
1182 -----------------------------------------------
1183 -- Delete all successful rows from the table --
1184 -- (they're the only rows still in process) --
1185 -----------------------------------------------
1186 DELETE FROM ENG_CHG_USR_ATR_INTERFACE
1187 WHERE DATA_SET_ID = p_data_set_id
1188 AND PROCESS_STATUS = G_PS_IN_PROCESS;
1189 ELSE
1190 ----------------------------------------------
1191 -- Mark all rows we've processed as success --
1192 -- if they weren't marked as failure above --
1193 ----------------------------------------------
1194 UPDATE ENG_CHG_USR_ATR_INTERFACE
1195 SET PROCESS_STATUS = G_PS_SUCCESS
1196 WHERE DATA_SET_ID = p_data_set_id
1197 AND PROCESS_STATUS = G_PS_IN_PROCESS;
1198 END IF;
1199
1200 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Done with Change Concurrent Program', 1);
1201
1202 -------------------------------------------------------------------
1203 -- Finally, we log any errors that we've accumulated throughout --
1204 -- our conversions and looping (including all errors encountered --
1205 -- within our Business Object's processing) --
1206 -------------------------------------------------------------------
1207 ERROR_HANDLER.Log_Error(
1208 p_write_err_to_inttable => 'Y'
1209 ,p_write_err_to_conclog => 'Y'
1210 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
1211 );
1212
1213 COMMIT WORK;
1214
1215 EXCEPTION
1216 WHEN G_NO_USER_NAME_TO_VALIDATE THEN
1217
1218 ERROR_HANDLER.Add_Error_Message(
1219 p_message_name => 'EGO_EF_NO_NAME_TO_VALIDATE'
1220 ,p_application_id => 'EGO'
1221 ,p_message_type => FND_API.G_RET_STS_ERROR
1222 ,p_entity_id => G_ENTITY_ID
1223 ,p_entity_code => G_ENTITY_CODE
1224 );
1225
1226 ---------------------------------------------------------------
1227 -- No matter what the error, we want to make sure everything --
1228 -- we've logged gets to the appropriate error locations --
1229 ---------------------------------------------------------------
1230 ERROR_HANDLER.Log_Error(
1231 p_write_err_to_inttable => 'Y'
1232 ,p_write_err_to_conclog => 'Y'
1233 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
1234 );
1235
1236 WHEN OTHERS THEN
1237
1238 ERROR_HANDLER.Add_Error_Message(
1239 p_message_text => 'Unexpected error in '||G_PKG_NAME||'.Process_Change_User_Attrs_Data: '||SQLERRM
1240 ,p_application_id => 'EGO'
1241 ,p_message_type => FND_API.G_RET_STS_ERROR
1242 ,p_entity_id => G_ENTITY_ID
1243 ,p_entity_code => G_ENTITY_CODE);
1244
1245 ---------------------------------------------------------------
1246 -- No matter what the error, we want to make sure everything --
1247 -- we've logged gets to the appropriate error locations --
1248 ---------------------------------------------------------------
1249 ERROR_HANDLER.Log_Error(
1250 p_write_err_to_inttable => 'Y'
1251 ,p_write_err_to_conclog => 'Y'
1252 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug());
1253
1254 END Process_Change_User_Attrs_Data;
1255
1256 ----------------------------------------------------------------------
1257 FUNCTION Get_Current_Data_Set_Id
1258 RETURN NUMBER
1259 IS
1260
1261 l_curr_data_set_id NUMBER;
1262
1263 BEGIN
1264
1265 --------------------------------------------------------------------------
1266 -- This function returns the current value of the Data Set ID sequence; --
1267 -- if the sequence doesn't yet have a value this session, we make one --
1268 --------------------------------------------------------------------------
1269 SELECT EGO_IUA_DATA_SET_ID_S.CURRVAL INTO l_curr_data_set_id FROM DUAL;
1270 RETURN l_curr_data_set_id;
1271
1272 EXCEPTION
1273 WHEN G_NO_CURRVAL_YET THEN
1274
1275 SELECT ENG_CUA_DATA_SET_ID_S.NEXTVAL INTO l_curr_data_set_id FROM DUAL;
1276 RETURN l_curr_data_set_id;
1277
1278 END Get_Current_Data_Set_Id;
1279 ----------------------------------------------------------------------
1280 PROCEDURE Process_Change_User_Attrs
1281 (
1282 p_api_version IN NUMBER := 1.0 --bug 2775504 Amanjit p_api_version Defaulted to 1.0 earlier value was G_API_VERSION
1283 , p_init_msg_list IN BOOLEAN := FALSE
1284 , x_return_status OUT NOCOPY VARCHAR2
1285 , x_msg_count OUT NOCOPY NUMBER
1286 , p_bo_identifier IN VARCHAR2 := 'ECO'
1287 , p_change_number IN VARCHAR2
1288 , p_change_mgmt_type_code IN VARCHAR2
1289 , p_Organization_Code IN VARCHAR2
1290 , p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE
1291 , p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
1292 , p_debug IN VARCHAR2 := 'N'
1293 , p_output_dir IN VARCHAR2 := NULL
1294 , p_debug_filename IN VARCHAR2 := 'ECO_BO_Debug.log'
1295 ) IS
1296
1297 l_entity_index_counter NUMBER := 1;
1298 l_change_id NUMBER;
1299 l_change_type_id NUMBER;
1300 l_can_edit_this_instance VARCHAR2(1);
1301 l_token_table ERROR_HANDLER.Token_Tbl_Type;
1302 p_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1303 p_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1304 l_failed_row_id_buffer VARCHAR2(32767);
1305 l_failed_row_id_list VARCHAR2(32767);
1306 l_return_status VARCHAR2(1);
1307 l_errorcode NUMBER;
1308 l_msg_count NUMBER;
1309 l_msg_data VARCHAR2(1000);
1310 l_dynamic_sql VARCHAR2(32767);
1311 p_debug_level NUMBER := 3;
1312 l_attr_row_table EGO_USER_ATTR_ROW_TABLE;
1313
1314 cursor chg_csr is
1315 select EEC.CHANGE_ID,EEC.CHANGE_ORDER_TYPE_ID
1316 from ENG_ENGINEERING_CHANGES EEC,MTL_PARAMETERS MP
1317 where EEC.CHANGE_MGMT_TYPE_CODE = p_change_mgmt_type_code
1318 AND EEC.CHANGE_NOTICE = p_change_number
1319 AND MP.ORGANIZATION_CODE = p_Organization_Code
1320 AND EEC.ORGANIZATION_ID = MP.ORGANIZATION_ID;
1321
1322 BEGIN
1323 -----------------------------------------------
1324 -- Set this global variable once per session --
1325 -----------------------------------------------
1326 IF (G_HZ_PARTY_ID IS NULL) THEN
1327 IF (G_USER_NAME IS NOT NULL) THEN
1328
1329 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
1330 INTO G_HZ_PARTY_ID
1331 FROM EGO_PEOPLE_V
1332 WHERE USER_NAME = G_USER_NAME;
1333
1334 ELSE
1335
1336 RAISE G_NO_USER_NAME_TO_VALIDATE;
1337
1338 END IF;
1339 END IF;
1340 --======================--
1341 -- ERROR_HANDLER SET-UP --
1342 --======================--
1343
1344 ERROR_HANDLER.Initialize();
1345 ERROR_HANDLER.Set_Bo_Identifier(p_bo_identifier);
1346
1347
1348 -----------------------------------------------------------
1349 -- If we're debugging, we have to set up a Debug session --
1350 -----------------------------------------------------------
1351 IF (p_debug_level > 0) THEN
1352
1353 EGO_USER_ATTRS_DATA_PVT.Set_Up_Debug_Session(G_ENTITY_ID, G_ENTITY_CODE);
1354
1355 END IF;
1356
1357 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Instance Program', 1);
1358
1359
1360 OPEN chg_csr;
1361 FETCH chg_csr INTO l_change_id,l_change_type_id;
1362
1363 if (chg_csr%NOTFOUND) then
1364
1365 RAISE G_NO_CHANGE_TO_VALIDATE;
1366 else
1367 G_ITEM_NAME := 'ENG_CHANGE';
1368
1369 p_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1370 EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_ID', l_change_id));
1371
1372 p_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1373 EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_TYPE_ID', l_change_type_id)
1374 );
1375 -- customer needs to populate ROW_IDENTIFIER,ATTR_GROUP_NAME,TRANSACTION_TYPE
1376 -- attributes.
1377 IF (p_attributes_row_table IS NOT NULL) THEN
1378 l_attr_row_table := EGO_USER_ATTR_ROW_TABLE();
1379 FOR i IN 1..p_attributes_row_table.COUNT LOOP
1380 l_attr_row_table.EXTEND();
1381 l_attr_row_table(i) := EGO_USER_ATTR_ROW_OBJ(
1382 p_attributes_row_table(i).ROW_IDENTIFIER
1383 ,p_attributes_row_table(i).ATTR_GROUP_ID
1384 ,703
1385 ,'ENG_CHANGEMGMT_GROUP'
1386 ,p_attributes_row_table(i).ATTR_GROUP_NAME
1387 ,null
1388 ,null
1389 ,null
1390 ,null
1391 ,null
1392 ,null ,p_attributes_row_table(i).TRANSACTION_TYPE
1393 );
1394 END LOOP;
1395 END IF;
1396
1397 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
1398 (
1399 p_api_version => p_api_version
1400 ,p_object_name => G_ITEM_NAME
1401 ,p_attributes_row_table => l_attr_row_table
1402 ,p_attributes_data_table => p_attributes_data_table
1403 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1404 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1405 ,p_entity_id => G_ENTITY_ID
1406 ,p_entity_index => l_entity_index_counter
1407 ,p_entity_code => G_ENTITY_CODE
1408 ,p_debug_level => p_debug_level
1409 ,p_commit => FND_API.G_TRUE
1410 ,x_failed_row_id_list => l_failed_row_id_buffer
1411 ,x_return_status => l_return_status
1412 ,x_errorcode => l_errorcode
1413 ,x_msg_count => l_msg_count
1414 ,x_msg_data => l_msg_data
1415 );
1416 END IF;
1417
1418 END Process_Change_User_Attrs;
1419
1420 PROCEDURE Process_Change_Line_User_Attrs
1421 (
1422 p_api_version IN NUMBER := 1.0 --bug 2775504 Amanjit p_api_version Defaulted to 1.0 earlier value was G_API_VERSION
1423 , p_init_msg_list IN BOOLEAN := FALSE
1424 , x_return_status OUT NOCOPY VARCHAR2
1425 , x_msg_count OUT NOCOPY NUMBER
1426 , p_bo_identifier IN VARCHAR2 := 'ECO'
1427 ,p_change_number IN VARCHAR2
1428 ,p_change_mgmt_type_code IN VARCHAR2
1429 ,p_Organization_Code IN VARCHAR2
1430 ,p_change_line_sequence_number IN NUMBER
1431 ,p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE
1432 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
1433 , p_debug IN VARCHAR2 := 'N'
1434 , p_output_dir IN VARCHAR2 := NULL
1435 , p_debug_filename IN VARCHAR2 := 'ECO_BO_Debug.log'
1436 ) IS
1437
1438 l_entity_index_counter NUMBER := 1;
1439 l_change_line_id NUMBER;
1440 l_change_type_id NUMBER;
1441 l_token_table ERROR_HANDLER.Token_Tbl_Type;
1442 l_can_edit_this_instance VARCHAR2(1);
1443 p_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1444 p_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1445 l_failed_row_id_buffer VARCHAR2(32767);
1446 l_failed_row_id_list VARCHAR2(32767);
1447 l_return_status VARCHAR2(1);
1448 l_errorcode NUMBER;
1449 l_msg_count NUMBER;
1450 l_msg_data VARCHAR2(1000);
1451 l_dynamic_sql VARCHAR2(32767);
1452 p_debug_level NUMBER := 3;
1453 l_attr_row_table EGO_USER_ATTR_ROW_TABLE;
1454
1455 cursor chgline_csr is
1456 select EEV.CHANGE_LINE_ID,EEV.CHANGE_TYPE_ID
1457 from ENG_CHANGE_LINES EEV,ENG_ENGINEERING_CHANGES EEC,MTL_PARAMETERS MP
1458 where EEC.CHANGE_ID = EEV.CHANGE_ID
1459 AND EEC.CHANGE_NOTICE = p_change_number
1460 AND MP.ORGANIZATION_CODE = p_Organization_Code
1461 AND EEC.ORGANIZATION_ID = MP.ORGANIZATION_ID
1462 AND EEV.SEQUENCE_NUMBER = p_change_line_sequence_number;
1463 BEGIN
1464 -----------------------------------------------
1465 -- Set this global variable once per session --
1466 -----------------------------------------------
1467 IF (G_HZ_PARTY_ID IS NULL) THEN
1468 IF (G_USER_NAME IS NOT NULL) THEN
1469
1470 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
1471 INTO G_HZ_PARTY_ID
1472 FROM EGO_PEOPLE_V
1473 WHERE USER_NAME = G_USER_NAME;
1474
1475 ELSE
1476
1477 RAISE G_NO_USER_NAME_TO_VALIDATE;
1478
1479 END IF;
1480 END IF;
1481 --======================--
1482 -- ERROR_HANDLER SET-UP --
1483 --======================--
1484
1485 ERROR_HANDLER.Initialize();
1486 ERROR_HANDLER.Set_Bo_Identifier(p_bo_identifier);
1487
1488
1489 -----------------------------------------------------------
1490 -- If we're debugging, we have to set up a Debug session --
1491 -----------------------------------------------------------
1492 IF (p_debug_level > 0) THEN
1493
1494 EGO_USER_ATTRS_DATA_PVT.Set_Up_Debug_Session(G_ENTITY_ID, G_ENTITY_CODE);
1495
1496 END IF;
1497
1498 EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Change Line Instance Program', 1);
1499
1500
1501 OPEN chgline_csr;
1502 FETCH chgline_csr INTO l_change_line_id,l_change_type_id;
1503
1504 if (chgline_csr%NOTFOUND) then
1505
1506 RAISE G_NO_CHANGE_TO_VALIDATE;
1507 else
1508 G_ITEM_NAME := 'ENG_CHANGE_LINE';
1509
1510 p_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1511 EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_LINE_ID', l_change_line_id)
1512 );
1513
1514 p_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1515 EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_TYPE_ID', l_change_type_id)
1516 );
1517
1518 -- customer needs to populate ROW_IDENTIFIER,ATTR_GROUP_NAME,TRANSACTION_TYPE
1519 -- attributes.
1520 IF (p_attributes_row_table IS NOT NULL) THEN
1521 l_attr_row_table := EGO_USER_ATTR_ROW_TABLE();
1522 FOR i IN 1..p_attributes_row_table.COUNT LOOP
1523 l_attr_row_table.EXTEND();
1524 l_attr_row_table(i) := EGO_USER_ATTR_ROW_OBJ(
1525 p_attributes_row_table(i).ROW_IDENTIFIER
1526 ,p_attributes_row_table(i).ATTR_GROUP_ID
1527 ,703
1528 ,'ENG_LINEMGMT_GROUP'
1529 ,p_attributes_row_table(i).ATTR_GROUP_NAME
1530 ,null
1531 ,null
1532 ,null
1533 ,null
1534 ,null
1535 ,null ,p_attributes_row_table(i).TRANSACTION_TYPE
1536 );
1537 END LOOP;
1538 END IF;
1539 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
1540 (
1541 p_api_version => p_api_version
1542 ,p_object_name => G_ITEM_NAME
1543 ,p_attributes_row_table => l_attr_row_table
1544 ,p_attributes_data_table => p_attributes_data_table
1545 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1546 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1547 ,p_entity_id => G_ENTITY_ID
1548 ,p_entity_index => l_entity_index_counter
1549 ,p_entity_code => G_ENTITY_CODE
1550 ,p_debug_level => p_debug_level
1551 ,p_commit => FND_API.G_TRUE
1552 ,x_failed_row_id_list => l_failed_row_id_buffer
1553 ,x_return_status => l_return_status
1554 ,x_errorcode => l_errorcode
1555 ,x_msg_count => l_msg_count
1556 ,x_msg_data => l_msg_data
1557 );
1558 END IF;
1559
1560 END Process_Change_Line_User_Attrs;
1561 ---------------------------------------------------------------
1562 -- Check before deleting an attribute group assoc ----
1563 ---------------------------------------------------------------
1564 --Begin of Bug:3070807
1565 PROCEDURE Check_Delete_Associations
1566 (
1567 p_api_version IN NUMBER
1568 ,p_association_id IN NUMBER
1569 ,p_classification_code IN VARCHAR2
1570 ,p_data_level IN VARCHAR2
1571 ,p_attr_group_id IN NUMBER
1572 ,p_application_id IN NUMBER
1573 ,p_attr_group_type IN VARCHAR2
1574 ,p_attr_group_name IN VARCHAR2
1575 ,p_enabled_code IN VARCHAR2
1576 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
1577 ,x_ok_to_delete OUT NOCOPY VARCHAR2
1578 ,x_return_status OUT NOCOPY VARCHAR2
1579 ,x_errorcode OUT NOCOPY NUMBER
1580 ,x_msg_count OUT NOCOPY NUMBER
1581 ,x_msg_data OUT NOCOPY VARCHAR2
1582 )
1583 IS
1584
1585 l_api_version CONSTANT NUMBER := 1.0;
1586 l_count NUMBER;
1587 l_api_name CONSTANT VARCHAR2(30) := 'Check_Delete';
1588 l_message VARCHAR2(4000);
1589 l_attr_group_id VARCHAR2(40);
1590 l_dynamic_sql VARCHAR2(2000);
1591 l_attr_display_name VARCHAR2(250);
1592
1593
1594
1595 BEGIN
1596
1597
1598
1599 -- Initialize message list if p_init_msg_list is set to TRUE
1600 IF FND_API.To_Boolean(p_init_msg_list) THEN
1601 FND_MSG_PUB.Initialize;
1602 END IF;
1603
1604 SELECT
1605 attr_group_disp_name INTO l_attr_display_name
1606 FROM
1607 ego_obj_attr_grp_assocs_v
1608 WHERE association_id = p_association_id;
1609
1610 --Check if there are any entries for in EGO_PAGE_ENTRIES_V
1611 SELECT
1612 COUNT(*) INTO l_count
1613 FROM
1614 EGO_PAGE_ENTRIES_V
1615 WHERE
1616 ASSOCIATION_ID = p_association_id;
1617
1618 IF (l_count > 0)
1619 THEN
1620 x_ok_to_delete := FND_API.G_FALSE;
1621 l_message := 'EGO_ASSOCIATED_AG_IN_USE';
1622 FND_MESSAGE.Set_Name(g_app_name, l_message);
1623 FND_MESSAGE.Set_Token('ATTR_GROUP_NAME', l_attr_display_name);
1624 FND_MSG_PUB.Add;
1625 x_return_status := FND_API.G_RET_STS_ERROR;
1626
1627 END IF;
1628
1629 IF (l_count = 0) THEN
1630 l_attr_group_id := '''' || p_attr_group_id || '%''';
1631 -- check if this ag is used to create any search criterias
1632
1633 l_dynamic_sql := ' SELECT COUNT(*) ' ||
1634 ' FROM ' ||
1635 ' AK_CRITERIA cols, ' ||
1636 ' EGO_CRITERIA_TEMPLATES_V criterions ' ||
1637 ' WHERE cols.customization_code = criterions.customization_code ' ||
1638 ' AND criterions.classification1 = :1 ' ||
1639 ' AND cols.attribute_code LIKE :2' ;
1640 -- BUG 5097794 Replaced LITERALS with BINDS
1641 EXECUTE IMMEDIATE l_dynamic_sql INTO l_count USING p_classification_code , l_attr_group_id||'%' ;
1642 IF (l_count > 0)
1643 THEN
1644 x_ok_to_delete := FND_API.G_FALSE;
1645 l_message := 'EGO_ASSOCIATED_AG_IN_USE';
1646 FND_MESSAGE.Set_Name(g_app_name, l_message);
1647 FND_MESSAGE.Set_Token('ATTR_GROUP_NAME', l_attr_display_name);
1648 FND_MSG_PUB.Add;
1649 x_return_status := FND_API.G_RET_STS_ERROR;
1650 END IF;
1651 IF (l_count = 0) THEN
1652 -- check if this ag is used to create any result formats
1653 l_dynamic_sql := ' SELECT COUNT(*) ' ||
1654 ' FROM ' ||
1655 ' EGO_RESULTS_FORMAT_COLUMNS_V cols, ' ||
1656 ' EGO_RESULTS_FORMAT_V resultFormat ' ||
1657 ' WHERE cols.customization_code = resultFormat.customization_code ' ||
1658 ' AND resultFormat.classification1 = :1' ||
1659 ' AND cols.attribute_code LIKE :2' ;
1660
1661 -- BUG 5097794 Replaced LITERALS with BINDS
1662 EXECUTE IMMEDIATE l_dynamic_sql INTO l_count USING p_classification_code , l_attr_group_id||'%' ;
1663
1664 IF (l_count > 0)
1665 THEN
1666 x_ok_to_delete := FND_API.G_FALSE;
1667 l_message := 'EGO_ASSOCIATED_AG_IN_USE';
1668 FND_MESSAGE.Set_Name(g_app_name, l_message);
1669 FND_MESSAGE.Set_Token('ATTR_GROUP_NAME', l_attr_display_name);
1670 FND_MSG_PUB.Add;
1671 x_return_status := FND_API.G_RET_STS_ERROR;
1672 END IF;
1673 END IF; --if no search criteria exist
1674 END IF; -- no page entry exist
1675 FND_MSG_PUB.Count_And_Get(
1676 p_encoded => FND_API.G_FALSE,
1677 p_count => x_msg_count,
1678 p_data => x_msg_data
1679 );
1680
1681 IF (l_message IS NULL) THEN
1682 x_return_status := FND_API.G_RET_STS_SUCCESS;
1683 x_ok_to_delete := FND_API.G_TRUE;
1684 END IF;
1685 EXCEPTION
1686 WHEN OTHERS THEN
1687 x_ok_to_delete := FND_API.G_FALSE;
1688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1689 FND_MESSAGE.Set_Name(g_app_name, g_plsql_err);
1690 FND_MESSAGE.Set_Token(g_pkg_name_token, g_pkg_name);
1691 FND_MESSAGE.Set_Token(g_api_name_token, l_api_name);
1692 FND_MESSAGE.Set_Token(g_sql_err_msg_token, SQLERRM);
1693 FND_MSG_PUB.Add;
1694
1695
1696 END Check_Delete_Associations;
1697 --End of Bug:3070807
1698
1699
1700 END EGO_CHANGE_USER_ATTRS_PUB;
1701
[Home] [Help]