[Home] [Help]
PACKAGE BODY: APPS.AMW_CREATE_LINES_PKG
Source
1 PACKAGE BODY AMW_CREATE_LINES_PKG AS
2 /* $Header: amwcrlnb.pls 120.2 2006/09/21 23:36:44 npanandi noship $ */
3 /*****************************************************************************/
4 /*****************************************************************************/
5 /*****************************************************************************/
6 /* Major Functionality of the followIng procedure includes: */
7 /* Reads the amw_risk-ctrl_interface table */
8 /* following tables: */
9 /* INSERTS OR UPDATES ARE DONE AGAINIST THE FOLLOWING TABLES */
10 /* Insert into ENG_CHANGE_SUBJECTS */
11 /* Insert into ENG_CHANGE_LINES_B and ENG_CHANGE_LINES_TL */
12 /* */
13 /*****************************************************************************/
14 --
15 -- Used for exception processing
16 --
17
18 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
19 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
20 /*****************************************************************************/
21 PROCEDURE CREATE_LINES (
22 P_CHANGE_ID IN NUMBER
23 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
24 ,X_MSG_COUNT OUT NOCOPY VARCHAR2
25 ,X_MSG_DATA OUT NOCOPY VARCHAR2
26 )
27 IS
28 /****************************************************/
29 CURSOR C_ENTITY_NAME IS
30 SELECT ENTITY_NAME
31 ,PK1_VALUE
32 ,PK2_VALUE
33 ,PK3_VALUE
34 ,PK4_VALUE
35 ,PK5_VALUE
36 FROM ENG_CHANGE_SUBJECTS
37 WHERE CHANGE_LINE_ID IS NULL
38 AND SUBJECT_LEVEL=1
39 AND CHANGE_ID=P_CHANGE_ID;
40
41 L_ENTITY_NAME C_ENTITY_NAME%ROWTYPE;
42 LX_RETURN_STATUS VARCHAR2(30);
43 LX_MSG_COUNT NUMBER;
44 LX_MSG_DATA VARCHAR2(2000);
45
46 e_no_import_access EXCEPTION;
47 e_invalid_requestor_id EXCEPTION;
48 l_init_msg_list CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
49 ---02.15.2004 npanandi: added below variable to hold ProcessApprovalOption parameter value
50 l_approval_option varchar2(10) default NULL;
51 BEGIN
52 x_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 -- Initialize message list if p_init_msg_list is set to TRUE.
55 IF FND_API.to_Boolean( l_init_msg_list ) THEN
56 FND_MSG_PUB.initialize;
57 END IF;
58
59 ---02.16.2005 npanandi: need to delete existing lines for this changeRequest
60 ---and re-create rows with new process / risk / control associations
61 delete from eng_change_lines_tl where change_line_id in (select change_line_id from eng_change_lines where change_id=p_change_id);
62 delete from eng_change_lines where change_id=p_change_id;
63 delete from eng_change_subjects where change_line_id is not null and change_id=p_change_id;
64
65 OPEN C_ENTITY_NAME;
66 FETCH C_ENTITY_NAME INTO L_ENTITY_NAME;
67 CLOSE C_ENTITY_NAME;
68
69 IF(L_ENTITY_NAME.ENTITY_NAME = 'AMW_REVISION_ETTY') THEN
70 --02.15.2004 npanandi: added below SQL to get the
71 --ProcessApprovalOption parameter value for RiskLibrary ctx
72 l_approval_option := amw_utility_pvt.get_parameter(-1, 'PROCESS_APPROVAL_OPTION');
73
74 CREATE_LINES_RL(
75 P_CHANGE_ID => P_CHANGE_ID
76 ,P_PK1 => L_ENTITY_NAME.PK1_VALUE
77 ,P_PK2 => L_ENTITY_NAME.PK2_VALUE
78 ,P_PK3 => L_ENTITY_NAME.PK3_VALUE
79 ,P_PK4 => L_ENTITY_NAME.PK4_VALUE
80 ,P_PK5 => L_ENTITY_NAME.PK5_VALUE
81 ,P_ENTITY_NAME => L_ENTITY_NAME.ENTITY_NAME
82 ,p_approval_option => l_approval_option
83 );
84 ELSE
85 --02.15.2004 npanandi: added below SQL to get the
86 --ProcessApprovalOption parameter value for RiskLibrary ctx
87 l_approval_option := amw_utility_pvt.get_parameter(L_ENTITY_NAME.PK1_VALUE, 'PROCESS_APPROVAL_OPTION');
88
89 CREATE_LINES_ORG(
90 P_CHANGE_ID => P_CHANGE_ID
91 ,P_PK1 => L_ENTITY_NAME.PK1_VALUE
92 ,P_PK2 => L_ENTITY_NAME.PK2_VALUE
93 ,P_PK3 => L_ENTITY_NAME.PK3_VALUE
94 ,P_PK4 => L_ENTITY_NAME.PK4_VALUE
95 ,P_PK5 => L_ENTITY_NAME.PK5_VALUE
96 ,P_ENTITY_NAME => L_ENTITY_NAME.ENTITY_NAME
97 ,p_approval_option => l_approval_option
98 );
99 END IF;
100 EXCEPTION
101 WHEN others THEN
102 rollback;
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
104 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
105 p_count => x_msg_count,
106 p_data => x_msg_data);
107 --fnd_file.put_line (fnd_file.LOG, 'unexpected exception in create_lines: '||sqlerrm);
108 END CREATE_LINES;
109
110 /*****************************************************************************/
111 PROCEDURE CREATE_LINES_RL (
112 P_CHANGE_ID IN NUMBER
113 ,P_PK1 IN NUMBER
114 ,P_PK2 IN NUMBER
115 ,P_PK3 IN NUMBER
116 ,P_PK4 IN NUMBER
117 ,P_PK5 IN NUMBER
118 ,P_ENTITY_NAME IN VARCHAR2 --AMW_REVISION_ETTY for Risk Library Approvals
119 --02.15.2005 npanandi: added below parameter for ProcessApprovalOption parameter value
120 ,p_approval_option in varchar2
121 )
122 IS
123 /****************************************************/
124 --02.15.2004 npanandi: cursor to get downward Proceses which will
125 --also get approved alongwith the Change Request for the Current Process
126 --this is valid only if the ProcessApprovalOption parameter is 'B'
127 cursor c_draft_child_processes is
128 select parent_child_id as process_id
129 ,a.DISPLAY_NAME
130 ,a.DESCRIPTION
131 ,a.REVISION_NUMBER
132 ,a.approval_status
133 from amw_proc_hierarchy_denorm d, amw_process_vl a
134 where d.process_id = p_pk1 --processId
135 and up_down_ind = 'D'
136 and hierarchy_type = 'L'
137 and a.process_id = d.PARENT_CHILD_ID
138 and a.end_date is null
139 and a.approval_status <> 'A'
140 and a.process_id in (select child_process_id
141 from AMW_curr_app_HIERARCHY_rl_V
142 where parent_process_id=p_pk1);
143
144 ---02.15.2004 npanandi: cursor to get processes for AddProcess lineType
145 ---only 1 level below i.e. immediate child processes
146 cursor c_add_process is
147 SELECT ALR.PROCESS_ID
148 ,ALR.NAME
149 ,ALR.DISPLAY_NAME
150 ,ALR.DESCRIPTION
151 ,ALR.PROCESS_CODE
152 --,alr.revision_number
153 FROM AMW_LATEST_REVISIONS_V ALR
154 ,AMW_LATEST_HIERARCHY_RL_V APHD
155 WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
156 AND APHD.parent_PROCESS_ID=p_pk1 --processId
157 minus
158 SELECT ALR.PROCESS_ID
159 ,ALR.NAME
160 ,ALR.DISPLAY_NAME
161 ,ALR.DESCRIPTION
162 ,ALR.PROCESS_CODE
163 --,alr.revision_number
164 FROM AMW_LATEST_REVISIONS_V ALR
165 ,AMW_CURR_APP_HIERARCHY_RL_V APHD
166 WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
167 AND APHD.parent_PROCESS_ID=p_pk1; --processId
168
169 ---02.15.2004 npanandi: cursor to get processes for DeleteProcess lineType
170 ---only 1 level below i.e. immediate child processes
171 cursor c_delete_process is
172 SELECT ALR.PROCESS_ID
173 ,ALR.NAME
174 ,ALR.DISPLAY_NAME
175 ,ALR.DESCRIPTION
176 ,ALR.PROCESS_CODE
177 --,alr.revision_number
178 FROM AMW_LATEST_REVISIONS_V ALR
179 ,AMW_CURR_APP_HIERARCHY_RL_V APHD
180 WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
181 AND APHD.parent_PROCESS_ID=p_pk1 --processId
182 minus
183 SELECT ALR.PROCESS_ID
184 ,ALR.NAME
185 ,ALR.DISPLAY_NAME
186 ,ALR.DESCRIPTION
187 ,ALR.PROCESS_CODE
188 --,alr.revision_number
189 FROM AMW_LATEST_REVISIONS_V ALR
190 ,AMW_LATEST_HIERARCHY_RL_V APHD
191 WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
192 AND APHD.parent_PROCESS_ID=p_pk1; --processId
193
194 ---02.16.2004 npanandi: cursor to get risks for AddRisk lineType
195 ---only current Process risk additions
196 cursor c_add_risk is
197 (select arav.risk_id
198 ,ARAV.NAME
199 ,ARAV.DESCRIPTION
200 FROM AMW_RISKS_ALL_VL ARAV
201 ,AMW_RISK_ASSOCIATIONS ARA
202 WHERE ARA.RISK_ID=ARAV.RISK_ID
203 AND ARAV.LATEST_REVISION_FLAG='Y'
204 AND ARA.OBJECT_TYPE='PROCESS'
205 AND ARA.DELETION_DATE IS NULL
206 AND ARA.PK1=p_pk1) --processId
207 minus
208 (select arav.risk_id
209 ,ARAV.NAME
210 ,ARAV.DESCRIPTION
211 FROM AMW_RISKS_ALL_VL ARAV
212 ,AMW_RISK_ASSOCIATIONS ARA
213 WHERE ARA.RISK_ID=ARAV.RISK_ID
214 AND ARAV.LATEST_REVISION_FLAG='Y'
215 AND ARA.OBJECT_TYPE='PROCESS'
216 AND ARA.APPROVAL_DATE IS not NULL
217 and ara.DELETION_APPROVAL_DATE is null
218 AND ARA.PK1=p_pk1); --processId
219
220 ---02.16.2004 npanandi: cursor to get risks for DeleteRisk lineType
221 ---only current Process risk deletions
222 cursor c_delete_risk is
223 (select arav.risk_id
224 ,ARAV.NAME
225 ,ARAV.DESCRIPTION
226 FROM AMW_RISKS_ALL_VL ARAV
227 ,AMW_RISK_ASSOCIATIONS ARA
228 WHERE ARA.RISK_ID=ARAV.RISK_ID
229 AND ARAV.LATEST_REVISION_FLAG='Y'
230 AND ARA.OBJECT_TYPE='PROCESS'
231 AND ARA.APPROVAL_DATE IS not NULL
232 and ara.DELETION_APPROVAL_DATE is null
233 AND ARA.PK1=p_pk1) --processId
234 minus
235 (select arav.risk_id
236 ,ARAV.NAME
237 ,ARAV.DESCRIPTION
238 FROM AMW_RISKS_ALL_VL ARAV
239 ,AMW_RISK_ASSOCIATIONS ARA
240 WHERE ARA.RISK_ID=ARAV.RISK_ID
241 AND ARAV.LATEST_REVISION_FLAG='Y'
242 AND ARA.OBJECT_TYPE='PROCESS'
243 AND ARA.DELETION_DATE IS NULL
244 AND ARA.PK1=p_pk1); --processId
245
246 ---02.16.2004 npanandi: cursor to get risks for AddControl lineType
247 ---only the current Process control additions
248 cursor c_add_ctrl is
249 (select acav.control_id
250 ,acav.NAME
251 ,acav.DESCRIPTION
252 FROM AMW_CONTROLS_ALL_VL acav
253 ,AMW_CONTROL_ASSOCIATIONS aca
254 WHERE aca.control_id=acav.control_id
255 AND acav.LATEST_REVISION_FLAG='Y'
256 AND aca.OBJECT_TYPE='RISK'
257 AND aca.DELETION_DATE IS NULL
258 AND aca.PK1=p_pk1)
259 minus
260 (select acav.control_id
261 ,acav.NAME
262 ,acav.DESCRIPTION
263 FROM AMW_CONTROLS_ALL_VL acav
264 ,AMW_CONTROL_ASSOCIATIONS aca
265 WHERE aca.control_id=acav.control_id
266 AND acav.LATEST_REVISION_FLAG='Y'
267 AND aca.OBJECT_TYPE='RISK'
268 AND aca.approval_DATE IS not NULL
269 and aca.deletion_approval_date is null
270 AND aca.PK1=p_pk1); --processId
271
272 ---02.16.2004 npanandi: cursor to get controls for DeleteControl lineType
273 ---only the current Process control deletions
274 cursor c_delete_ctrl is
275 (select acav.control_id
276 ,acav.NAME
277 ,acav.DESCRIPTION
278 FROM AMW_CONTROLS_ALL_VL acav
279 ,AMW_CONTROL_ASSOCIATIONS aca
280 WHERE aca.control_id=acav.control_id
281 AND acav.LATEST_REVISION_FLAG='Y'
282 AND aca.OBJECT_TYPE='RISK'
283 AND aca.approval_DATE IS not NULL
284 and aca.deletion_approval_date is null
285 AND aca.PK1=p_pk1) --processId
286 minus
287 (select acav.control_id
288 ,acav.NAME
289 ,acav.DESCRIPTION
290 FROM AMW_CONTROLS_ALL_VL acav
291 ,AMW_CONTROL_ASSOCIATIONS aca
292 WHERE aca.control_id=acav.control_id
293 AND acav.LATEST_REVISION_FLAG='Y'
294 AND aca.OBJECT_TYPE='RISK'
295 AND aca.DELETION_DATE IS NULL
296 AND aca.PK1=p_pk1); --processId
297
298 L_SEQ_NUM_INCR NUMBER;
299 L_CHANGE_LINE_ID NUMBER;
300 L_CHANGE_SUBJECT_ID NUMBER;
301 L_LINE_TYPE_ID NUMBER;
302 LX_ROW_ID VARCHAR2(255);
303
304 --02.02.2005 npanandi: added below vars for Delete Line Types
305 L_DELETE_CHANGE_SUBJECT_ID NUMBER;
306 L_DELETE_LINE_TYPE_ID NUMBER;
307
308 LX_RETURN_STATUS VARCHAR2(30);
309 LX_MSG_COUNT NUMBER;
310 LX_MSG_DATA VARCHAR2(2000);
311
312 e_no_import_access EXCEPTION;
313 e_invalid_requestor_id EXCEPTION;
314 l_revision_number number;
315 BEGIN
316 ---dbms_output.put_line( 'p_approval_option: '||p_approval_option );
317
318 /* 02.15.2004 npanandi: added below if stmnt/cursor loop
319 for creating lines for all downward processes which are
320 also undergoing Change alongwith the parent Process for which
321 the Change Request is made
322 */
323 --if p_approval_option = 'B'--> Automatically Approve all descendants
324 --if p_approval_option = 'A'--> Approval Independent of Descendant's Approval Status
325 if(p_approval_option = 'B')then
326 for draft_proc_rec in c_draft_child_processes loop
327 L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
328
329 --get the line_type_id for this child_approval_process_line_type
330 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
331 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
332
333 --- ***************************** WARNING STARTS *****************************
334 --- change the values of the parameters below after seeding correct lineType
335 --- ***************************** WARNING ENDS *****************************
336 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_APPR_PROCESS_ETTY','AMW_REVISION_ETTY');
337
338 process_lines(
339 p_change_id => p_change_id
340 ,p_seq_num_incr => l_seq_num_incr
341 ,p_line_type_id => l_line_type_id
342 ,p_name => draft_proc_rec.display_name
343 ,p_description => draft_proc_rec.description
344 ,p_entity_name1 => 'AMW_LINE_APPR_PROCESS_ETTY'
345 ,p_entity_name2 => 'AMW_REVISION_ETTY'
346 ,p_pk1_value => draft_proc_rec.process_id
347 ,p_pk2_value => draft_proc_rec.revision_number
348 );
349
350 /**
351 CREATE_CHANGE_REQUEST_LINES(
352 P_CHANGE_ID => P_CHANGE_ID
353 ,p_seq_num_incr => L_SEQ_NUM_INCR
354 ,p_line_type_id => L_LINE_TYPE_ID
355 ,p_name => draft_proc_rec.DISPLAY_NAME
356 ,p_description => draft_proc_rec.DESCRIPTION
357 ,x_change_line_id => l_change_line_id);
358
359 CREATE_SUBJECT_LINES(
360 p_change_id => P_CHANGE_ID
361 ,p_change_line_id => L_CHANGE_LINE_ID
362 ,p_entity_name => 'AMW_LINE_PROCESS_ETTY'
363 ,p_pk1_value => draft_proc_rec.process_id
364 ,p_pk2_value => draft_proc_rec.revision_number
365 ,p_subject_level => 1);
366
367 CREATE_SUBJECT_LINES(
368 p_change_id => P_CHANGE_ID
369 ,p_change_line_id => L_CHANGE_LINE_ID
370 ,p_entity_name => 'AMW_REVISION_ETTY'
371 ,p_pk1_value => draft_proc_rec.process_id
372 ,p_pk2_value => draft_proc_rec.revision_number
373 ,p_subject_level => 2);
374 **/
375 end loop;
376 end if;
377
378 ---Create Lines for All downward Processes
379 FOR add_PROC_REC IN C_add_PROCESS LOOP
380 L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
381
382 --GET THE LINE_TYPE_ID FOR PROCESS LINE TYPE
383 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
384 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
385
386 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_PROCESS_ETTY','AMW_REVISION_ETTY');
387 /*select ecot.change_order_type_id
388 ,ESE.SUBJECT_ID
389 INTO L_LINE_TYPE_ID
390 ,L_CHANGE_SUBJECT_ID
391 from eng_change_order_types ecot
392 ,eng_subject_entities ese
393 where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
394 and ecot.SUBJECT_ID=ese.subject_id
395 AND ESE.ENTITY_NAME='AMW_LINE_PROCESS_ETTY'
396 AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
397 AND ESE.SUBJECT_LEVEL=1;*/
398
399 ---02.16.2005 npanandi: below SQL gives the revisionNumber
400 ---of this process from the AmwLatestHierarchyV view
401 --- ****** reason why RevisionNumber is not incl. in Cursor stmt: ******
402 ---there may be an immediate child Process, which is also in Draft status
403 ---in this case, the AddProcess Cursor's 'minus' clause causes problems in
404 ---revisionNumber data, and incorrect resultSet is obtained.
405 select revision_number into l_revision_number
406 from amw_latest_revisions_v
407 where process_id=add_proc_rec.process_id;
408
409 process_lines(
410 p_change_id => p_change_id
411 ,p_seq_num_incr => l_seq_num_incr
412 ,p_line_type_id => l_line_type_id
413 ,p_name => add_proc_rec.display_name
414 ,p_description => add_proc_rec.description
415 ,p_entity_name1 => 'AMW_LINE_PROCESS_ETTY'
416 ,p_entity_name2 => 'AMW_REVISION_ETTY'
417 ,p_pk1_value => add_proc_rec.process_id
418 ,p_pk2_value => l_revision_number
419 );
420 /*
421 CREATE_CHANGE_REQUEST_LINES(
422 P_CHANGE_ID => P_CHANGE_ID
423 ,p_seq_num_incr => L_SEQ_NUM_INCR
424 ,p_line_type_id => L_LINE_TYPE_ID
425 ,p_name => add_PROC_REC.DISPLAY_NAME
426 ,p_description => add_PROC_REC.DESCRIPTION
427 ,x_change_line_id => l_change_line_id);
428
429 CREATE_SUBJECT_LINES(
430 p_change_id => P_CHANGE_ID
431 ,p_change_line_id => L_CHANGE_LINE_ID
432 ,p_entity_name => 'AMW_LINE_PROCESS_ETTY'
433 ,p_pk1_value => add_proc_rec.process_id
434 ,p_pk2_value => add_proc_rec.revision_number
435 ,p_subject_level => 1);
436
437 CREATE_SUBJECT_LINES(
438 p_change_id => P_CHANGE_ID
439 ,p_change_line_id => L_CHANGE_LINE_ID
440 ,p_entity_name => 'AMW_REVISION_ETTY'
441 ,p_pk1_value => add_proc_rec.process_id
442 ,p_pk2_value => add_proc_rec.revision_number
443 ,p_subject_level => 2);
444 */
445 END LOOP;
446
447 ---02.16.2005 npanandi: added below loop for DeleteProcess lineTypes
448 for delete_proc_rec in c_delete_process loop
449 l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
450 ---02.02.2005 npanandi: added below stmnt
451 ---get the line_type_id for 'Delete Process' lineType
452 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
453 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
454
455 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_DEL_PROCESS_ETTY','AMW_REVISION_ETTY');
456 /*select ecot.change_order_type_id
457 ,ESE.SUBJECT_ID
458 INTO L_DELETE_LINE_TYPE_ID
459 ,L_DELETE_CHANGE_SUBJECT_ID
460 from eng_change_order_types ecot
461 ,eng_subject_entities ese
462 where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
463 and ecot.SUBJECT_ID=ese.subject_id
464 AND ESE.ENTITY_NAME='AMW_LINE_DEL_PROCESS_ETTY'
465 AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
466 AND ESE.SUBJECT_LEVEL=1;*/
467 ---02.02.2005 npanandi: ends above stmnt
468
469 ---02.16.2005 npanandi: below SQL gives the revisionNumber
470 ---of this process from the AmwCurrAppHierarchyV view
471 --- ****** reason why RevisionNumber is not incl. in Cursor stmt: ******
472 ---there may be an immediate child Process, which is also in Draft status
473 ---in this case, the DeleteProcess Cursor's 'minus' clause causes problems in
474 ---revisionNumber data, and incorrect resultSet is obtained.
475 select child_revision_number into l_revision_number
476 from amw_curr_app_hierarchy_rl_v
477 where parent_process_id=p_pk1 --parentProcessId
478 and child_process_id=delete_proc_rec.process_id; --childProcessId
479
480 process_lines(
481 p_change_id => p_change_id
482 ,p_seq_num_incr => l_seq_num_incr
483 ,p_line_type_id => l_line_type_id
484 ,p_name => delete_proc_rec.display_name
485 ,p_description => delete_proc_rec.description
486 ,p_entity_name1 => 'AMW_LINE_DEL_PROCESS_ETTY'
487 ,p_entity_name2 => 'AMW_REVISION_ETTY'
488 ,p_pk1_value => delete_proc_rec.process_id
489 ,p_pk2_value => l_revision_number
490 );
491 end loop;
492
493 --loop for AddRisk lineType
494 FOR add_RISK_REC IN c_add_risk LOOP
495 L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
496
497 --GET THE LINE_TYPE_ID FOR RISK LINE TYPE
498 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR RISK LINES
499 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR RISK LINES
500 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_RISK_ETTY','AMW_REVISION_ETTY');
501 /*select ecot.change_order_type_id
502 ,ESE.SUBJECT_ID
503 INTO L_LINE_TYPE_ID
504 ,L_CHANGE_SUBJECT_ID
505 from eng_change_order_types ecot
506 ,eng_subject_entities ese
507 where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
508 and ecot.SUBJECT_ID=ese.subject_id
509 AND ESE.ENTITY_NAME='AMW_LINE_RISK_ETTY'
510 AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
511 AND ESE.SUBJECT_LEVEL=1;*/
512
513 process_lines(
514 p_change_id => p_change_id
515 ,p_seq_num_incr => l_seq_num_incr
516 ,p_line_type_id => l_line_type_id
517 ,p_name => add_risk_rec.name
518 ,p_description => add_risk_rec.description
519 ,p_entity_name1 => 'AMW_LINE_RISK_ETTY'
520 ,p_entity_name2 => 'AMW_REVISION_ETTY'
521 ,p_pk1_value => add_risk_rec.risk_id
522 );
523
524 /*
525 CREATE_CHANGE_REQUEST_LINES(
526 P_CHANGE_ID => P_CHANGE_ID
527 ,p_seq_num_incr => L_SEQ_NUM_INCR
528 ,p_line_type_id => L_LINE_TYPE_ID
529 ,p_name => RISK_REC.NAME
530 ,p_description => risk_REC.DESCRIPTION
531 ,x_change_line_id => l_change_line_id);
532
533 CREATE_SUBJECT_LINES(
534 p_change_id => P_CHANGE_ID
535 ,p_change_line_id => L_CHANGE_LINE_ID
536 ,p_entity_name => 'AMW_LINE_RISK_ETTY'
537 ,p_pk1_value => risk_rec.risk_id
538 ,p_subject_level => 1);
539
540 CREATE_SUBJECT_LINES(
541 p_change_id => P_CHANGE_ID
542 ,p_change_line_id => L_CHANGE_LINE_ID
543 ,p_entity_name => 'AMW_REVISION_ETTY'
544 ,p_pk1_value => risk_rec.risk_id
545 ,p_subject_level => 2);*/
546 END LOOP;
547
548 --loop for DeleteRisk lineType
549 for delete_risk_rec in c_delete_risk loop
550 l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
551
552 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
553 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
554 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_DEL_RISK_ETTY','AMW_REVISION_ETTY');
555
556 process_lines(
557 p_change_id => p_change_id
558 ,p_seq_num_incr => l_seq_num_incr
559 ,p_line_type_id => l_line_type_id
560 ,p_name => delete_risk_rec.name
561 ,p_description => delete_risk_rec.description
562 ,p_entity_name1 => 'AMW_LINE_DEL_RISK_ETTY'
563 ,p_entity_name2 => 'AMW_REVISION_ETTY'
564 ,p_pk1_value => delete_risk_rec.risk_id
565 );
566 end loop;
567
568 --loop for AddControl lineType
569 for add_ctrl_rec in c_add_ctrl loop
570 l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
571
572 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
573 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
574 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_CTRL_ETTY','AMW_REVISION_ETTY');
575
576 process_lines(
577 p_change_id => p_change_id
578 ,p_seq_num_incr => l_seq_num_incr
579 ,p_line_type_id => l_line_type_id
580 ,p_name => add_ctrl_rec.name
581 ,p_description => add_ctrl_rec.description
582 ,p_entity_name1 => 'AMW_LINE_CTRL_ETTY'
583 ,p_entity_name2 => 'AMW_REVISION_ETTY'
584 ,p_pk1_value => add_ctrl_rec.control_id
585 );
586 end loop;
587
588 --loop for DeleteControl lineType
589 for delete_ctrl_rec in c_delete_ctrl loop
590 l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
591
592 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
593 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
594 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_DEL_CTRL_ETTY','AMW_REVISION_ETTY');
595
596 process_lines(
597 p_change_id => p_change_id
598 ,p_seq_num_incr => l_seq_num_incr
599 ,p_line_type_id => l_line_type_id
600 ,p_name => delete_ctrl_rec.name
601 ,p_description => delete_ctrl_rec.description
602 ,p_entity_name1 => 'AMW_LINE_DEL_CTRL_ETTY'
603 ,p_entity_name2 => 'AMW_REVISION_ETTY'
604 ,p_pk1_value => delete_ctrl_rec.control_id
605 );
606 end loop;
607 /**
608 --Create Lines for All Controls
609 FOR CTRL_REC IN C_ASSOCIATED_CTRLS LOOP
610 L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
611
612 --GET THE LINE_TYPE_ID FOR RISK LINE TYPE
613 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR CONTROL LINES
614 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR CONTROL LINES
615 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_CTRL_ETTY','AMW_REVISION_ETTY');
616 select ecot.change_order_type_id
617 ,ESE.SUBJECT_ID
618 INTO L_LINE_TYPE_ID
619 ,L_CHANGE_SUBJECT_ID
620 from eng_change_order_types ecot
621 ,eng_subject_entities ese
622 where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
623 and ecot.SUBJECT_ID=ese.subject_id
624 AND ESE.ENTITY_NAME='AMW_LINE_CTRL_ETTY'
625 AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
626 AND ESE.SUBJECT_LEVEL=1;
627
628 ---02.02.2005 npanandi: added below stmnt
629 ---get the line_type_id for 'Delete Control' lineType
630 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE CONTROL LINES
631 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE CONTROL LINES
632 l_line_type_id := get_line_type_id('AMW_PROCESS_RL_APPROVAL','AMW_LINE_DEL_CTRL_ETTY','AMW_REVISION_ETTY');
633 select ecot.change_order_type_id
634 ,ESE.SUBJECT_ID
635 INTO L_DELETE_LINE_TYPE_ID
636 ,L_DELETE_CHANGE_SUBJECT_ID
637 from eng_change_order_types ecot
638 ,eng_subject_entities ese
639 where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
640 and ecot.SUBJECT_ID=ese.subject_id
641 AND ESE.ENTITY_NAME='AMW_LINE_DEL_CTRL_ETTY'
642 AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
643 AND ESE.SUBJECT_LEVEL=1;
644 ---02.02.2005 npanandi: ends above stmnt
645
646 CREATE_CHANGE_REQUEST_LINES(
647 P_CHANGE_ID => P_CHANGE_ID
648 ,p_seq_num_incr => L_SEQ_NUM_INCR
649 ,p_line_type_id => L_LINE_TYPE_ID
650 ,p_name => CTRL_REC.NAME
651 ,p_description => CTRL_REC.DESCRIPTION
652 ,x_change_line_id => l_change_line_id);
653
654 CREATE_SUBJECT_LINES(
655 p_change_id => P_CHANGE_ID
656 ,p_change_line_id => L_CHANGE_LINE_ID
657 ,p_entity_name => 'AMW_LINE_CTRL_ETTY'
658 ,p_pk1_value => ctrl_rec.control_id
659 ,p_subject_level => 1);
660
661 CREATE_SUBJECT_LINES(
662 p_change_id => P_CHANGE_ID
663 ,p_change_line_id => L_CHANGE_LINE_ID
664 ,p_entity_name => 'AMW_REVISION_ETTY'
665 ,p_pk1_value => ctrl_rec.control_id
666 ,p_subject_level => 2);
667 END LOOP;
668 **/
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 ROLLBACK;
673
674 END CREATE_LINES_RL;
675
676 /*****************************************************************************/
677 PROCEDURE CREATE_LINES_ORG (
678 P_CHANGE_ID IN NUMBER
679 ,P_PK1 IN NUMBER --ORGANIZATION_ID
680 ,P_PK2 IN NUMBER --PROCESS_ID
681 ,P_PK3 IN NUMBER --REVISION_NUMBER
682 ,P_PK4 IN NUMBER
683 ,P_PK5 IN NUMBER
684 ,P_ENTITY_NAME IN VARCHAR2 --AMW_REVISION_ETTY for Risk Library Approvals
685 --02.15.2005 npanandi: added below parameter for ProcessApprovalOption parameter value
686 ,p_approval_option in varchar2
687 )
688 IS
689 /****************************************************/
690 --02.15.2004 npanandi: cursor to get downward Proceses which will
691 --also get approved alongwith the Change Request for the Current Process
692 --this is valid only if the ProcessApprovalOption parameter is 'B'
693 cursor c_draft_org_child_processes is
694 SELECT apo.process_id process_id,
695 apo.display_name display_name,
696 apo.revision_number revision_number,
697 apo.approval_status approval_status,
698 apo.description description
699 from amw_process_organization_vl apo, amw_latest_hierarchies alh
700 where
701 alh.organization_id = p_pk1
702 and alh.parent_id = p_pk2
703 and apo.process_id = alh.child_id
704 and apo.end_date is null
705 and apo.approval_date is null;
706
707 --ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
708 /*
709 select parent_child_id as process_id
710 ,a.DISPLAY_NAME
711 ,a.REVISION_NUMBER
712 ,a.approval_status
713 ,a.description
714 from amw_org_hierarchy_denorm d, amw_latest_rev_org_v a
715 where d.process_id = p_pk2 --processId
716 and d.organization_id = p_pk1 --organizationId
717 and up_down_ind = 'D'
718 and hierarchy_type = 'L'
719 and a.process_id = d.PARENT_CHILD_ID
720 and a.organization_id = p_pk1 --organizationId
721 and a.end_date is null
722 and a.approval_status <> 'A'
723 and a.process_id in (select child_process_id
724 from AMW_curr_app_HIERARCHY_ORG_V
725 where parent_process_id=p_pk2
726 and child_organization_id=p_pk1);
727 */
728 ---02.17.2004 npanandi: cursor to get processes for AddProcess lineType
729 ---only 1 level below i.e. immediate child processes
730 cursor c_add_process is
731 SELECT ALR.PROCESS_ID
732 ,ALR.DISPLAY_NAME
733 ,ALR.DESCRIPTION
734 ,ALR.PROCESS_CODE
735 --,alr.revision_number
736 FROM amw_latest_rev_org_v ALR
737 ,AMW_LATEST_HIERARCHY_ORG_V APHD
738 WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
739 and alr.ORGANIZATION_ID=p_pk1 --organizationId
740 and aphd.CHILD_ORGANIZATION_ID=p_pk1 --organizationId
741 AND APHD.parent_PROCESS_ID=p_pk2 --processId
742 minus
743 SELECT ALR.PROCESS_ID
744 ,ALR.DISPLAY_NAME
745 ,ALR.DESCRIPTION
746 ,ALR.PROCESS_CODE
747 --,alr.revision_number
748 FROM amw_latest_rev_org_v ALR
749 ,AMW_CURR_APP_HIERARCHY_ORG_V APHD
750 WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
751 and alr.ORGANIZATION_ID=p_pk1 --organizationId
752 and aphd.CHILD_ORGANIZATION_ID=p_pk1 --organizationId
753 AND APHD.parent_PROCESS_ID=p_pk2; --processId
754
755 ---02.17.2004 npanandi: cursor to get processes for DeleteProcess lineType
756 ---only 1 level below i.e. immediate child processes
757 cursor c_delete_process is
758 SELECT ALR.PROCESS_ID
759 ,ALR.DISPLAY_NAME
760 ,ALR.DESCRIPTION
761 ,ALR.PROCESS_CODE
762 --,alr.revision_number
763 FROM amw_latest_rev_org_v ALR
764 ,AMW_CURR_APP_HIERARCHY_ORG_V APHD
765 WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
766 and alr.ORGANIZATION_ID=p_pk1 --organizationId
767 and aphd.CHILD_ORGANIZATION_ID=p_pk1 --organizationId
768 AND APHD.parent_PROCESS_ID=p_pk2 --processId
769 minus
770 SELECT ALR.PROCESS_ID
771 ,ALR.DISPLAY_NAME
772 ,ALR.DESCRIPTION
773 ,ALR.PROCESS_CODE
774 --,alr.revision_number
775 FROM amw_latest_rev_org_v ALR
776 ,AMW_LATEST_HIERARCHY_ORG_V APHD
777 WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
778 and alr.ORGANIZATION_ID=p_pk1 --organizationId
779 and aphd.CHILD_ORGANIZATION_ID=p_pk1 --organizationId
780 AND APHD.parent_PROCESS_ID=p_pk2; --processId
781
782 cursor c_add_risk is
783 (select arav.risk_id
784 ,ARAV.NAME
785 ,ARAV.DESCRIPTION
786 FROM AMW_RISKS_ALL_VL ARAV
787 ,AMW_RISK_ASSOCIATIONS ARA
788 WHERE ARA.RISK_ID=ARAV.RISK_ID
789 AND ARAV.LATEST_REVISION_FLAG='Y'
790 AND ARA.OBJECT_TYPE='PROCESS_ORG'
791 AND ARA.DELETION_DATE IS NULL
792 AND ARA.PK1=p_pk1 --organizationId
793 and ara.pk2=p_pk2) --processId
794 minus
795 (select arav.risk_id
796 ,ARAV.NAME
797 ,ARAV.DESCRIPTION
798 FROM AMW_RISKS_ALL_VL ARAV
799 ,AMW_RISK_ASSOCIATIONS ARA
800 WHERE ARA.RISK_ID=ARAV.RISK_ID
801 AND ARAV.LATEST_REVISION_FLAG='Y'
802 AND ARA.OBJECT_TYPE='PROCESS_ORG'
803 AND ARA.APPROVAL_DATE IS not NULL
804 and ara.DELETION_APPROVAL_DATE is null
805 and ara.pk1=p_pk1 --organizationId
806 AND ARA.PK2=p_pk2); --processId
807
808 cursor c_delete_risk is
809 (select arav.risk_id
810 ,ARAV.NAME
811 ,ARAV.DESCRIPTION
812 FROM AMW_RISKS_ALL_VL ARAV
813 ,AMW_RISK_ASSOCIATIONS ARA
814 WHERE ARA.RISK_ID=ARAV.RISK_ID
815 AND ARAV.LATEST_REVISION_FLAG='Y'
816 AND ARA.OBJECT_TYPE='PROCESS_ORG'
817 AND ARA.APPROVAL_DATE IS not NULL
818 and ara.DELETION_APPROVAL_DATE is null
819 and ara.pk1=p_pk1 --organizationId
820 AND ARA.PK2=p_pk2) --processId
821 minus
822 (select arav.risk_id
823 ,ARAV.NAME
824 ,ARAV.DESCRIPTION
825 FROM AMW_RISKS_ALL_VL ARAV
826 ,AMW_RISK_ASSOCIATIONS ARA
827 WHERE ARA.RISK_ID=ARAV.RISK_ID
828 AND ARAV.LATEST_REVISION_FLAG='Y'
829 AND ARA.OBJECT_TYPE='PROCESS_ORG'
830 AND ARA.DELETION_DATE IS NULL
831 AND ARA.PK1=p_pk1 --organizationId
832 and ara.pk2=p_pk2); --processId
833
834 cursor c_add_ctrl is
835 (select acav.control_id
836 ,acav.NAME
837 ,acav.DESCRIPTION
838 FROM AMW_CONTROLS_ALL_VL acav
839 ,AMW_CONTROL_ASSOCIATIONS aca
840 WHERE aca.control_id=acav.control_id
841 AND acav.LATEST_REVISION_FLAG='Y'
842 AND aca.OBJECT_TYPE='RISK_ORG'
843 AND aca.DELETION_DATE IS NULL
844 AND aca.PK1=p_pk1 ---organizationId
845 and aca.pk2=p_pk2) --processId
846 minus
847 (select acav.control_id
848 ,acav.NAME
849 ,acav.DESCRIPTION
850 FROM AMW_CONTROLS_ALL_VL acav
851 ,AMW_CONTROL_ASSOCIATIONS aca
852 WHERE aca.control_id=acav.control_id
853 AND acav.LATEST_REVISION_FLAG='Y'
854 AND aca.OBJECT_TYPE='RISK_ORG'
855 AND aca.approval_DATE IS not NULL
856 and aca.deletion_approval_date is null
857 AND aca.PK1=p_pk1 ---organizationId
858 and aca.pk2=p_pk2); --processId
859
860 cursor c_delete_ctrl is
861 (select acav.control_id
862 ,acav.NAME
863 ,acav.DESCRIPTION
864 FROM AMW_CONTROLS_ALL_VL acav
865 ,AMW_CONTROL_ASSOCIATIONS aca
866 WHERE aca.control_id=acav.control_id
867 AND acav.LATEST_REVISION_FLAG='Y'
868 AND aca.OBJECT_TYPE='RISK_ORG'
869 AND aca.approval_DATE IS not NULL
870 and aca.deletion_approval_date is null
871 AND aca.PK1=p_pk1 ---organizationId
872 and aca.pk2=p_pk2) --processId
873 minus
874 (select acav.control_id
875 ,acav.NAME
876 ,acav.DESCRIPTION
877 FROM AMW_CONTROLS_ALL_VL acav
878 ,AMW_CONTROL_ASSOCIATIONS aca
879 WHERE aca.control_id=acav.control_id
880 AND acav.LATEST_REVISION_FLAG='Y'
881 AND aca.OBJECT_TYPE='RISK_ORG'
882 AND aca.DELETION_DATE IS NULL
883 AND aca.PK1=p_pk1 ---organizationId
884 and aca.pk2=p_pk2); --processId
885
886 ---CURSOR TO GET ALL CONTROLS FOR A GIVEN PROCESS IN ORG
887 CURSOR C_ASSOCIATED_CTRLS IS
888 SELECT ACAV.CONTROL_ID
889 ,ACAV.NAME
890 ,ACAV.DESCRIPTION
891 FROM AMW_CONTROLS_ALL_VL ACAV
892 ,AMW_CONTROL_ASSOCIATIONS ACA
893 ,(select distinct child_id from
894 amw_latest_hierarchies
895 START WITH CHILD_ID = p_pk2 AND ORGANIZATION_ID = p_pk1
896 CONNECT BY PRIOR CHILD_ID = PARENT_ID
897 and organization_id = p_pk1 ) AOHD
898 WHERE ACA.PK1 = p_pk1
899 AND AOHD.CHILD_ID=ACA.PK2
900 AND ACA.OBJECT_TYPE='RISK_ORG'
901 AND ACA.DELETION_DATE IS NULL
902 AND ACA.CONTROL_ID=ACAV.CONTROL_ID
903 AND ACAV.CURR_APPROVED_FLAG='Y' ;
904
905 --ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
906 /*
907 CURSOR C_ASSOCIATED_CTRLS IS
908 SELECT ACAV.CONTROL_ID
909 ,ACAV.NAME
910 ,ACAV.DESCRIPTION
911 FROM AMW_CONTROLS_ALL_VL ACAV
912 ,AMW_CONTROL_ASSOCIATIONS ACA
913 ,AMW_ORG_HIERARCHY_DENORM AOHD
914 WHERE AOHD.UP_DOWN_IND='D'
915 AND AOHD.ORGANIZATION_ID=ACA.PK1
916 AND AOHD.PARENT_CHILD_ID=ACA.PK2
917 AND ACA.OBJECT_TYPE='RISK_ORG'
918 AND ACA.DELETION_DATE IS NULL
919 AND ACA.CONTROL_ID=ACAV.CONTROL_ID
920 AND ACAV.CURR_APPROVED_FLAG='Y'
921 AND AOHD.ORGANIZATION_ID=P_PK1
922 AND AOHD.PROCESS_ID=P_PK2
923 --NPANANDI 12.16.2004: ADDED BELOW TO RESTRICT ROWS RETURNED
924 --TO BE THOSE FROM LATEST HIERARCHY
925 AND AOHD.HIERARCHY_TYPE='L'
926 UNION
927 SELECT ACAV.CONTROL_ID
928 ,ACAV.NAME
929 ,ACAV.DESCRIPTION
930 FROM AMW_CONTROLS_ALL_VL ACAV
931 ,AMW_CONTROL_ASSOCIATIONS ACA
932 WHERE ACA.OBJECT_TYPE='RISK_ORG'
933 AND ACA.DELETION_DATE IS NULL
934 AND ACAV.LATEST_REVISION_FLAG='Y'
935 AND ACAV.CONTROL_ID=ACA.CONTROL_ID
936 AND ACA.PK1=P_PK1 --ORGID
937 AND ACA.PK2=P_PK2; --PROCESSID
938 */
939
940 L_SEQ_NUM_INCR NUMBER;
941 L_CHANGE_LINE_ID NUMBER;
942 L_CHANGE_SUBJECT_ID NUMBER;
943 L_LINE_TYPE_ID NUMBER;
944 LX_ROW_ID VARCHAR2(255);
945
946 LX_RETURN_STATUS VARCHAR2(30);
947 LX_MSG_COUNT NUMBER;
948 LX_MSG_DATA VARCHAR2(2000);
949
950 --02.02.2005 npanandi: added below vars for Delete Line Types
951 L_DELETE_CHANGE_SUBJECT_ID NUMBER;
952 L_DELETE_LINE_TYPE_ID NUMBER;
953
954 e_no_import_access EXCEPTION;
955 e_invalid_requestor_id EXCEPTION;
956
957 l_revision_number number;
958 BEGIN
959 /* 02.15.2004 npanandi: added below if stmnt/cursor loop
960 for creating lines for all downward processes which are
961 also undergoing Change alongwith the parent Process for which
962 the Change Request is made
963 */
964 --if p_approval_option = 'B'--> Automatically Approve all descendants
965 --if p_approval_option = 'A'--> Approval Independent of Descendant's Approval Status
966 if(p_approval_option = 'B')then
967 for draft_proc_rec in c_draft_org_child_processes loop
968 L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
969
970 --get the line_type_id for this child_approval_process_line_type
971 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
972 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
973
974 --- ***************************** WARNING STARTS *****************************
975 --- change the values of the parameters below after seeding correct lineType
976 --- ***************************** WARNING ENDS *****************************
977 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_APPR_PROC_ETTY','AMW_ORG_REV_ETTY');
978
979 process_lines(
980 p_change_id => p_change_id
981 ,p_seq_num_incr => l_seq_num_incr
982 ,p_line_type_id => l_line_type_id
983 ,p_name => draft_proc_rec.display_name
984 ,p_description => draft_proc_rec.description
985 ,p_entity_name1 => 'AMW_ORG_LINE_APPR_PROC_ETTY'
986 ,p_entity_name2 => 'AMW_ORG_REV_ETTY'
987 ,p_pk1_value => p_pk1 ---organizationId
988 ,p_pk2_value => draft_proc_rec.process_id --processId
989 ,p_pk3_value => draft_proc_rec.revision_number --revisionNumber
990 );
991 end loop;
992 end if;
993
994 --02.17.2005 npanandi: loop for AddProcess LineType
995 for add_proc_rec in c_add_process loop
996 l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
997
998 --GET THE LINE_TYPE_ID FOR PROCESS LINE TYPE
999 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
1000 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
1001 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_PROCESS_ETTY','AMW_ORG_REV_ETTY');
1002
1003 ---02.17.2005 npanandi: below SQL gives the revisionNumber
1004 ---of this process from the AmwLatestRevOrgV view
1005 --- ****** reason why RevisionNumber is not incl. in Cursor stmt: ******
1006 ---there may be an immediate child Process, which is also in Draft status
1007 ---in this case, the AddProcess Cursor's 'minus' clause causes problems in
1008 ---revisionNumber data, and incorrect resultSet is obtained.
1009 select revision_number into l_revision_number
1010 from amw_latest_rev_org_v
1011 where organization_id=p_pk1
1012 and process_id=add_proc_rec.process_id;
1013
1014 process_lines(
1015 p_change_id => p_change_id
1016 ,p_seq_num_incr => l_seq_num_incr
1017 ,p_line_type_id => l_line_type_id
1018 ,p_name => add_proc_rec.display_name
1019 ,p_description => add_proc_rec.description
1020 ,p_entity_name1 => 'AMW_ORG_LINE_PROCESS_ETTY'
1021 ,p_entity_name2 => 'AMW_ORG_REV_ETTY'
1022 ,p_pk1_value => p_pk1 ---organizationId
1023 ,p_pk2_value => add_proc_rec.process_id --processId
1024 ,p_pk3_value => l_revision_number --revisionNumber
1025 );
1026 end loop;
1027
1028 ---02.17.2005 npanandi: added below loop for DeleteProcess lineTypes
1029 for delete_proc_rec in c_delete_process loop
1030 l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
1031
1032 ---02.02.2005 npanandi: added below stmnt
1033 ---get the line_type_id for 'Delete Process' lineType
1034 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
1035 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
1036
1037 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_DEL_PROCESS_ETTY','AMW_ORG_REV_ETTY');
1038 ---02.02.2005 npanandi: ends above stmnt
1039
1040 ---02.17.2005 npanandi: below SQL gives the revisionNumber
1041 ---of this process from the AmwCurrAppHierarchyOrgV view
1042 --- ****** reason why RevisionNumber is not incl. in Cursor stmt: ******
1043 ---there may be an immediate child Process, which is also in Draft status
1044 ---in this case, the DeleteProcess Cursor's 'minus' clause causes problems in
1045 ---revisionNumber data, and incorrect resultSet is obtained.
1046 select child_revision_number into l_revision_number
1047 from amw_curr_app_hierarchy_org_v
1048 where child_organization_id=p_pk1 --organizationId
1049 and parent_process_id=p_pk2 --parentProcessId
1050 and child_process_id=delete_proc_rec.process_id; --childProcessId;
1051
1052 process_lines(
1053 p_change_id => p_change_id
1054 ,p_seq_num_incr => l_seq_num_incr
1055 ,p_line_type_id => l_line_type_id
1056 ,p_name => delete_proc_rec.display_name
1057 ,p_description => delete_proc_rec.description
1058 ,p_entity_name1 => 'AMW_ORG_LINE_DEL_PROCESS_ETTY'
1059 ,p_entity_name2 => 'AMW_ORG_REV_ETTY'
1060 ,p_pk1_value => p_pk1 --organizationId
1061 ,p_pk2_value => delete_proc_rec.process_id
1062 ,p_pk3_value => l_revision_number
1063 );
1064 end loop;
1065
1066 --02.17.2005 npanandi: loop for AddRisk lineType
1067 FOR add_RISK_REC IN c_add_risk LOOP
1068 L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
1069
1070 --GET THE LINE_TYPE_ID FOR RISK LINE TYPE
1071 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR RISK LINES
1072 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR RISK LINES
1073 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_RISK_ETTY','AMW_ORG_REV_ETTY');
1074
1075 process_lines(
1076 p_change_id => p_change_id
1077 ,p_seq_num_incr => l_seq_num_incr
1078 ,p_line_type_id => l_line_type_id
1079 ,p_name => add_risk_rec.name
1080 ,p_description => add_risk_rec.description
1081 ,p_entity_name1 => 'AMW_ORG_LINE_RISK_ETTY'
1082 ,p_entity_name2 => 'AMW_ORG_REV_ETTY'
1083 ,p_pk1_value => add_risk_rec.risk_id
1084 );
1085 END LOOP;
1086
1087 --02.17.2005 npanandi: loop for DeleteRisk lineType
1088 FOR delete_RISK_REC IN c_delete_risk LOOP
1089 L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
1090
1091 --GET THE LINE_TYPE_ID FOR RISK LINE TYPE
1092 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR RISK LINES
1093 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR RISK LINES
1094 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_DEL_RISK_ETTY','AMW_ORG_REV_ETTY');
1095
1096 process_lines(
1097 p_change_id => p_change_id
1098 ,p_seq_num_incr => l_seq_num_incr
1099 ,p_line_type_id => l_line_type_id
1100 ,p_name => delete_risk_rec.name
1101 ,p_description => delete_risk_rec.description
1102 ,p_entity_name1 => 'AMW_ORG_LINE_DEL_RISK_ETTY'
1103 ,p_entity_name2 => 'AMW_ORG_REV_ETTY'
1104 ,p_pk1_value => delete_risk_rec.risk_id
1105 );
1106 END LOOP;
1107
1108 --02.17.2005 npanandi: loop for AddControl lineType
1109 for add_ctrl_rec in c_add_ctrl loop
1110 l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
1111
1112 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
1113 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
1114 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_CTRL_ETTY','AMW_ORG_REV_ETTY');
1115
1116 process_lines(
1117 p_change_id => p_change_id
1118 ,p_seq_num_incr => l_seq_num_incr
1119 ,p_line_type_id => l_line_type_id
1120 ,p_name => add_ctrl_rec.name
1121 ,p_description => add_ctrl_rec.description
1122 ,p_entity_name1 => 'AMW_ORG_LINE_CTRL_ETTY'
1123 ,p_entity_name2 => 'AMW_ORG_REV_ETTY'
1124 ,p_pk1_value => add_ctrl_rec.control_id
1125 );
1126 end loop;
1127
1128 --02.17.2005 npanandi: loop for DeleteControl lineType
1129 for delete_ctrl_rec in c_delete_ctrl loop
1130 l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
1131
1132 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
1133 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
1134 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_DEL_CTRL_ETTY','AMW_ORG_REV_ETTY');
1135
1136 process_lines(
1137 p_change_id => p_change_id
1138 ,p_seq_num_incr => l_seq_num_incr
1139 ,p_line_type_id => l_line_type_id
1140 ,p_name => delete_ctrl_rec.name
1141 ,p_description => delete_ctrl_rec.description
1142 ,p_entity_name1 => 'AMW_ORG_LINE_DEL_CTRL_ETTY'
1143 ,p_entity_name2 => 'AMW_ORG_REV_ETTY'
1144 ,p_pk1_value => delete_ctrl_rec.control_id
1145 );
1146 end loop;
1147 /*
1148 --Create Lines for All Controls
1149 FOR CTRL_REC IN C_ASSOCIATED_CTRLS LOOP
1150 L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
1151
1152 --GET THE LINE_TYPE_ID FOR PROCESS LINE TYPE
1153 L_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
1154 L_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR PROCESS LINES
1155 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_CTRL_ETTY','AMW_ORG_REV_ETTY');
1156 select ecot.change_order_type_id
1157 ,ESE.SUBJECT_ID
1158 INTO L_LINE_TYPE_ID
1159 ,L_CHANGE_SUBJECT_ID
1160 from eng_change_order_types ecot
1161 ,eng_subject_entities ese
1162 where ecot.change_mgmt_type_code='AMW_PROCESS_ORG_APPROVAL'
1163 and ecot.SUBJECT_ID=ese.subject_id
1164 AND ESE.ENTITY_NAME='AMW_ORG_LINE_CTRL_ETTY'
1165 AND ESE.PARENT_ENTITY_NAME='AMW_ORG_REV_ETTY'
1166 AND ESE.SUBJECT_LEVEL=1;
1167
1168 ---02.02.2005 npanandi: added below stmnt
1169 ---get the line_type_id for 'Delete Control' lineType
1170 L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
1171 L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
1172 l_line_type_id := get_line_type_id('AMW_PROCESS_ORG_APPROVAL','AMW_ORG_LINE_DEL_CTRL_ETTY','AMW_ORG_REV_ETTY');
1173 select ecot.change_order_type_id
1174 ,ESE.SUBJECT_ID
1175 INTO L_DELETE_LINE_TYPE_ID
1176 ,L_DELETE_CHANGE_SUBJECT_ID
1177 from eng_change_order_types ecot
1178 ,eng_subject_entities ese
1179 where ecot.change_mgmt_type_code='AMW_PROCESS_ORG_APPROVAL'
1180 and ecot.SUBJECT_ID=ese.subject_id
1181 AND ESE.ENTITY_NAME='AMW_ORG_LINE_DEL_CTRL_ETTY'
1182 AND ESE.PARENT_ENTITY_NAME='AMW_ORG_REV_ETTY'
1183 AND ESE.SUBJECT_LEVEL=1;
1184 ---02.02.2005 npanandi: ends above stmnt
1185
1186 CREATE_CHANGE_REQUEST_LINES(
1187 P_CHANGE_ID => P_CHANGE_ID
1188 ,p_seq_num_incr => L_SEQ_NUM_INCR
1189 ,p_line_type_id => L_LINE_TYPE_ID
1190 ,p_name => CTRL_REC.NAME
1191 ,p_description => CTRL_REC.DESCRIPTION
1192 ,x_change_line_id => l_change_line_id);
1193
1194 CREATE_SUBJECT_LINES(
1195 p_change_id => P_CHANGE_ID
1196 ,p_change_line_id => L_CHANGE_LINE_ID
1197 ,p_entity_name => 'AMW_ORG_LINE_CTRL_ETTY'
1198 ,p_pk1_value => CTRL_REC.control_id
1199 ,p_subject_level => 1);
1200
1201 CREATE_SUBJECT_LINES(
1202 p_change_id => P_CHANGE_ID
1203 ,p_change_line_id => L_CHANGE_LINE_ID
1204 ,p_entity_name => 'AMW_ORG_REV_ETTY'
1205 ,p_pk1_value => CTRL_REC.control_id
1206 ,p_subject_level => 2);
1207
1208 ---CREATE_SUBJECT_LINES(P_CHANGE_ID,L_CHANGE_LINE_ID,'AMW_ORG_LINE_CTRL_ETTY',1);
1209 ---CREATE_SUBJECT_LINES(P_CHANGE_ID,L_CHANGE_LINE_ID,'AMW_ORG_REV_ETTY',2);
1210 END LOOP;
1211 */
1212
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 ROLLBACK;
1216
1217 END CREATE_LINES_ORG;
1218
1219 ---
1220 ---02.03.2005 npanandi: added below method
1221 ---
1222 PROCEDURE CREATE_CHANGE_REQUEST_LINES(
1223 P_CHANGE_ID IN NUMBER
1224 ,p_seq_num_incr IN NUMBER
1225 ,p_line_type_id IN number
1226 ,p_name in varchar2
1227 ,p_description in varchar2
1228 ,x_change_line_id out nocopy number)
1229 IS
1230 l_change_line_id NUMBER;
1231 LX_ROW_ID VARCHAR2(255);
1232 BEGIN
1233 --get the changeLineId sequence value
1234 SELECT ENG_CHANGE_LINES_S.NEXTVAL
1235 INTO L_CHANGE_LINE_ID
1236 FROM DUAL;
1237
1238 ENG_CHANGE_LINES_PKG.INSERT_ROW(
1239 X_ROWID => LX_ROW_ID
1240 ,X_CHANGE_LINE_ID => L_CHANGE_LINE_ID
1241 ,X_REQUEST_ID => NULL
1242 ,X_CHANGE_ID => P_CHANGE_ID
1243 ,X_SEQUENCE_NUMBER => p_SEQ_NUM_INCR
1244 ,X_CHANGE_TYPE_ID => p_LINE_TYPE_ID
1245 ,X_STATUS_CODE => '11'
1246 ,X_ASSIGNEE_ID => NULL --DON'T NEED SINCE LINES AREN'T ASSIGNED
1247 ,X_NEED_BY_DATE => NULL
1248 ,X_ORIGINAL_SYSTEM_REFERENCE => NULL
1249 ,X_NAME => p_name
1250 ,X_DESCRIPTION => p_description
1251 ,X_SCHEDULED_DATE => NULL
1252 ,X_IMPLEMENTATION_DATE => sysdate
1253 ,X_CANCELATION_DATE => NULL
1254 ,X_CREATION_DATE => SYSDATE
1255 ,X_CREATED_BY => G_USER_ID
1256 ,X_LAST_UPDATE_DATE => SYSDATE
1257 ,X_LAST_UPDATED_BY => G_USER_ID
1258 ,X_LAST_UPDATE_LOGIN => G_LOGIN_ID
1259 ,X_PROGRAM_ID => NULL
1260 ,X_PROGRAM_APPLICATION_ID => NULL
1261 ,X_PROGRAM_UPDATE_DATE => NULL
1262 ,X_APPROVAL_STATUS_TYPE => NULL
1263 ,X_APPROVAL_DATE => NULL
1264 ,X_APPROVAL_REQUEST_DATE => NULL
1265 ,X_ROUTE_ID => NULL
1266 ,X_REQUIRED_FLAG => NULL
1267 ,X_COMPLETE_BEFORE_STATUS_CODE => NULL
1268 ,X_START_AFTER_STATUS_CODE => NULL
1269 );
1270
1271 x_change_line_id := l_change_line_id;
1272 END CREATE_CHANGE_REQUEST_LINES;
1273 --02.03.2005 npanandi: end CreateChangeRequestLines procedure
1274
1275 PROCEDURE CREATE_SUBJECT_LINES(
1276 P_CHANGE_ID IN NUMBER
1277 ,P_CHANGE_LINE_ID IN NUMBER
1278 ,P_ENTITY_NAME IN VARCHAR2
1279 --02.03.2005 npanandi: added pk1 to pk5 to populate for Process/Risk/Ctrl Lines
1280 ,p_pk1_value in number
1281 ,p_pk2_value in number
1282 ,p_pk3_value in number
1283 ,p_pk4_value in number
1284 ,p_pk5_value in number
1285 ,P_SUBJECT_LEVEL IN NUMBER)
1286 IS
1287 L_CHANGE_SUBJECT_ID NUMBER;
1288 BEGIN
1289 --Insert into Eng_Change_Subjects
1290 SELECT ENG_CHANGE_SUBJECTS_S.NEXTVAL
1291 INTO L_CHANGE_SUBJECT_ID
1292 FROM DUAL;
1293
1294 INSERT INTO ENG_CHANGE_SUBJECTS (
1295 CHANGE_SUBJECT_ID
1296 ,CHANGE_ID
1297 ,CHANGE_LINE_ID
1298 ,ENTITY_NAME
1299 --02.03.2005 npanandi: added pk1 to pk5 to populate for Process/Risk/Ctrl Lines
1300 ,pk1_value
1301 ,pk2_value
1302 ,pk3_value
1303 ,pk4_value
1304 ,pk5_value
1305 ,SUBJECT_LEVEL
1306 ,LAST_UPDATE_DATE
1307 ,LAST_UPDATED_BY
1308 ,CREATION_DATE
1309 ,CREATED_BY
1310 ,LAST_UPDATE_LOGIN
1311 ) VALUES (
1312 L_CHANGE_SUBJECT_ID
1313 ,P_CHANGE_ID
1314 ,P_CHANGE_LINE_ID
1315 ,P_ENTITY_NAME
1316 --02.03.2005 npanandi: added pk1 to pk5 to populate for Process/Risk/Ctrl Lines
1317 ,p_pk1_value
1318 ,p_pk2_value
1319 ,p_pk3_value
1320 ,p_pk4_value
1321 ,p_pk5_value
1322 ,P_SUBJECT_LEVEL
1323 ,SYSDATE
1324 ,G_USER_ID
1325 ,SYSDATE
1326 ,G_USER_ID
1327 ,G_LOGIN_ID
1328 );
1329 END CREATE_SUBJECT_LINES;
1330
1331 ---
1332 ---02.16.2005 npanandi: added method to create lines in
1333 ---EngChangeLinesB, EngChangeLinesTl and insert rows in EngChangeSubjects tables
1334 ---
1335 PROCEDURE process_lines(
1336 P_CHANGE_ID IN NUMBER
1337 ,p_seq_num_incr in number
1338 ,p_line_type_id in number
1339 ,p_name in varchar2
1340 ,p_description in varchar2
1341 ,P_ENTITY_NAME1 IN VARCHAR2
1342 ,P_ENTITY_NAME2 IN VARCHAR2
1343 ,p_pk1_value in number
1344 ,p_pk2_value in number
1345 ,p_pk3_value in number
1346 ,p_pk4_value in number
1347 ,p_pk5_value in number)
1348 IS
1349 l_change_line_id number;
1350 BEGIN
1351 /*
1352 dbms_output.put_line( 'p_change_id: '||p_change_id||', p_seq_num_incr: '||p_seq_num_incr);
1353 dbms_output.put_line( 'p_line_type_id: '||p_line_type_id||', p_name: '||p_name||', p_description: '||p_description);
1354 dbms_output.put_line( 'p_entity_name1: '||p_entity_name1||', p_entity_name2: '||p_entity_name2);
1355 dbms_output.put_line( 'p_pk1_value: '||p_pk1_value||', p_pk2_value: '||p_pk2_value||', p_pk3_value: '||p_pk3_value||', p_pk4_value: '||p_pk4_value||', p_pk5_value: '||p_pk5_value);
1356 dbms_output.put_line( '*******************************************************' );
1357 */
1358
1359 CREATE_CHANGE_REQUEST_LINES(
1360 P_CHANGE_ID => P_CHANGE_ID
1361 ,p_seq_num_incr => p_seq_num_incr
1362 ,p_line_type_id => p_line_type_id
1363 ,p_name => p_name
1364 ,p_description => p_description
1365 ,x_change_line_id => l_change_line_id);
1366
1367 CREATE_SUBJECT_LINES(
1368 p_change_id => P_CHANGE_ID
1369 ,p_change_line_id => L_CHANGE_LINE_ID
1370 ,p_entity_name => p_entity_name1
1371 ,p_pk1_value => p_pk1_value
1372 ,p_pk2_value => p_pk2_value
1373 ,p_pk3_value => p_pk3_value
1374 ,p_subject_level => 1);
1375
1376 CREATE_SUBJECT_LINES(
1377 p_change_id => P_CHANGE_ID
1378 ,p_change_line_id => L_CHANGE_LINE_ID
1379 ,p_entity_name => p_entity_name2
1380 ,p_pk1_value => p_pk1_value
1381 ,p_pk2_value => p_pk2_value
1382 ,p_pk3_value => p_pk3_value
1383 ,p_subject_level => 2);
1384
1385 END process_lines;
1386
1387 --
1388 --02.15.2004 npanandi: added below function to get lineTypeId
1389 --given ChangeMgmtTypeCode, EntityName, ParentEntityName
1390 --
1391 FUNCTION get_line_type_id(
1392 p_change_mgmt_type_code IN varchar2
1393 ,p_entity_name in varchar2
1394 ,p_parent_entity_name IN VARCHAR2) RETURN number
1395 IS
1396 L_LINE_TYPE_ID number;
1397 BEGIN
1398 select ecot.change_order_type_id
1399 INTO L_LINE_TYPE_ID
1400 from eng_change_order_types ecot
1401 ,eng_subject_entities ese
1402 where ecot.change_mgmt_type_code=p_change_mgmt_type_code
1403 and ecot.SUBJECT_ID=ese.subject_id
1404 AND ESE.ENTITY_NAME=p_entity_name
1405 AND ESE.PARENT_ENTITY_NAME=p_parent_entity_name
1406 AND ESE.SUBJECT_LEVEL=1;
1407
1408 return L_LINE_TYPE_ID;
1409 END get_line_type_id;
1410
1411 END AMW_CREATE_LINES_PKG;