[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* INSERTS OR UPDATES ARE DONE AGAINIST THE FOLLOWING TABLES */
/* Insert into ENG_CHANGE_SUBJECTS */
/* Insert into ENG_CHANGE_LINES_B and ENG_CHANGE_LINES_TL */
/* */
/*****************************************************************************/
--
-- Used for exception processing
--
G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
SELECT ENTITY_NAME
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
FROM ENG_CHANGE_SUBJECTS
WHERE CHANGE_LINE_ID IS NULL
AND SUBJECT_LEVEL=1
AND CHANGE_ID=P_CHANGE_ID;
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);
delete from eng_change_lines where change_id=p_change_id;
delete from eng_change_subjects where change_line_id is not null and change_id=p_change_id;
select parent_child_id as process_id
,a.DISPLAY_NAME
,a.DESCRIPTION
,a.REVISION_NUMBER
,a.approval_status
from amw_proc_hierarchy_denorm d, amw_process_vl a
where d.process_id = p_pk1 --processId
and up_down_ind = 'D'
and hierarchy_type = 'L'
and a.process_id = d.PARENT_CHILD_ID
and a.end_date is null
and a.approval_status <> 'A'
and a.process_id in (select child_process_id
from AMW_curr_app_HIERARCHY_rl_V
where parent_process_id=p_pk1);
SELECT ALR.PROCESS_ID
,ALR.NAME
,ALR.DISPLAY_NAME
,ALR.DESCRIPTION
,ALR.PROCESS_CODE
--,alr.revision_number
FROM AMW_LATEST_REVISIONS_V ALR
,AMW_LATEST_HIERARCHY_RL_V APHD
WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
AND APHD.parent_PROCESS_ID=p_pk1 --processId
minus
SELECT ALR.PROCESS_ID
,ALR.NAME
,ALR.DISPLAY_NAME
,ALR.DESCRIPTION
,ALR.PROCESS_CODE
--,alr.revision_number
FROM AMW_LATEST_REVISIONS_V ALR
,AMW_CURR_APP_HIERARCHY_RL_V APHD
WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
AND APHD.parent_PROCESS_ID=p_pk1; --processId
cursor c_delete_process is
SELECT ALR.PROCESS_ID
,ALR.NAME
,ALR.DISPLAY_NAME
,ALR.DESCRIPTION
,ALR.PROCESS_CODE
--,alr.revision_number
FROM AMW_LATEST_REVISIONS_V ALR
,AMW_CURR_APP_HIERARCHY_RL_V APHD
WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
AND APHD.parent_PROCESS_ID=p_pk1 --processId
minus
SELECT ALR.PROCESS_ID
,ALR.NAME
,ALR.DISPLAY_NAME
,ALR.DESCRIPTION
,ALR.PROCESS_CODE
--,alr.revision_number
FROM AMW_LATEST_REVISIONS_V ALR
,AMW_LATEST_HIERARCHY_RL_V APHD
WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
AND APHD.parent_PROCESS_ID=p_pk1; --processId
(select arav.risk_id
,ARAV.NAME
,ARAV.DESCRIPTION
FROM AMW_RISKS_ALL_VL ARAV
,AMW_RISK_ASSOCIATIONS ARA
WHERE ARA.RISK_ID=ARAV.RISK_ID
AND ARAV.LATEST_REVISION_FLAG='Y'
AND ARA.OBJECT_TYPE='PROCESS'
AND ARA.DELETION_DATE IS NULL
AND ARA.PK1=p_pk1) --processId
minus
(select arav.risk_id
,ARAV.NAME
,ARAV.DESCRIPTION
FROM AMW_RISKS_ALL_VL ARAV
,AMW_RISK_ASSOCIATIONS ARA
WHERE ARA.RISK_ID=ARAV.RISK_ID
AND ARAV.LATEST_REVISION_FLAG='Y'
AND ARA.OBJECT_TYPE='PROCESS'
AND ARA.APPROVAL_DATE IS not NULL
and ara.DELETION_APPROVAL_DATE is null
AND ARA.PK1=p_pk1); --processId
cursor c_delete_risk is
(select arav.risk_id
,ARAV.NAME
,ARAV.DESCRIPTION
FROM AMW_RISKS_ALL_VL ARAV
,AMW_RISK_ASSOCIATIONS ARA
WHERE ARA.RISK_ID=ARAV.RISK_ID
AND ARAV.LATEST_REVISION_FLAG='Y'
AND ARA.OBJECT_TYPE='PROCESS'
AND ARA.APPROVAL_DATE IS not NULL
and ara.DELETION_APPROVAL_DATE is null
AND ARA.PK1=p_pk1) --processId
minus
(select arav.risk_id
,ARAV.NAME
,ARAV.DESCRIPTION
FROM AMW_RISKS_ALL_VL ARAV
,AMW_RISK_ASSOCIATIONS ARA
WHERE ARA.RISK_ID=ARAV.RISK_ID
AND ARAV.LATEST_REVISION_FLAG='Y'
AND ARA.OBJECT_TYPE='PROCESS'
AND ARA.DELETION_DATE IS NULL
AND ARA.PK1=p_pk1); --processId
(select acav.control_id
,acav.NAME
,acav.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL acav
,AMW_CONTROL_ASSOCIATIONS aca
WHERE aca.control_id=acav.control_id
AND acav.LATEST_REVISION_FLAG='Y'
AND aca.OBJECT_TYPE='RISK'
AND aca.DELETION_DATE IS NULL
AND aca.PK1=p_pk1)
minus
(select acav.control_id
,acav.NAME
,acav.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL acav
,AMW_CONTROL_ASSOCIATIONS aca
WHERE aca.control_id=acav.control_id
AND acav.LATEST_REVISION_FLAG='Y'
AND aca.OBJECT_TYPE='RISK'
AND aca.approval_DATE IS not NULL
and aca.deletion_approval_date is null
AND aca.PK1=p_pk1); --processId
cursor c_delete_ctrl is
(select acav.control_id
,acav.NAME
,acav.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL acav
,AMW_CONTROL_ASSOCIATIONS aca
WHERE aca.control_id=acav.control_id
AND acav.LATEST_REVISION_FLAG='Y'
AND aca.OBJECT_TYPE='RISK'
AND aca.approval_DATE IS not NULL
and aca.deletion_approval_date is null
AND aca.PK1=p_pk1) --processId
minus
(select acav.control_id
,acav.NAME
,acav.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL acav
,AMW_CONTROL_ASSOCIATIONS aca
WHERE aca.control_id=acav.control_id
AND acav.LATEST_REVISION_FLAG='Y'
AND aca.OBJECT_TYPE='RISK'
AND aca.DELETION_DATE IS NULL
AND aca.PK1=p_pk1); --processId
L_DELETE_CHANGE_SUBJECT_ID NUMBER;
L_DELETE_LINE_TYPE_ID NUMBER;
/*select ecot.change_order_type_id
,ESE.SUBJECT_ID
INTO L_LINE_TYPE_ID
,L_CHANGE_SUBJECT_ID
from eng_change_order_types ecot
,eng_subject_entities ese
where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
and ecot.SUBJECT_ID=ese.subject_id
AND ESE.ENTITY_NAME='AMW_LINE_PROCESS_ETTY'
AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
AND ESE.SUBJECT_LEVEL=1;*/
select revision_number into l_revision_number
from amw_latest_revisions_v
where process_id=add_proc_rec.process_id;
for delete_proc_rec in c_delete_process loop
l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
/*select ecot.change_order_type_id
,ESE.SUBJECT_ID
INTO L_DELETE_LINE_TYPE_ID
,L_DELETE_CHANGE_SUBJECT_ID
from eng_change_order_types ecot
,eng_subject_entities ese
where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
and ecot.SUBJECT_ID=ese.subject_id
AND ESE.ENTITY_NAME='AMW_LINE_DEL_PROCESS_ETTY'
AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
AND ESE.SUBJECT_LEVEL=1;*/
---in this case, the DeleteProcess Cursor's 'minus' clause causes problems in
---revisionNumber data, and incorrect resultSet is obtained.
select child_revision_number into l_revision_number
from amw_curr_app_hierarchy_rl_v
where parent_process_id=p_pk1 --parentProcessId
and child_process_id=delete_proc_rec.process_id; --childProcessId
,p_name => delete_proc_rec.display_name
,p_description => delete_proc_rec.description
,p_entity_name1 => 'AMW_LINE_DEL_PROCESS_ETTY'
,p_entity_name2 => 'AMW_REVISION_ETTY'
,p_pk1_value => delete_proc_rec.process_id
,p_pk2_value => l_revision_number
);
/*select ecot.change_order_type_id
,ESE.SUBJECT_ID
INTO L_LINE_TYPE_ID
,L_CHANGE_SUBJECT_ID
from eng_change_order_types ecot
,eng_subject_entities ese
where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
and ecot.SUBJECT_ID=ese.subject_id
AND ESE.ENTITY_NAME='AMW_LINE_RISK_ETTY'
AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
AND ESE.SUBJECT_LEVEL=1;*/
for delete_risk_rec in c_delete_risk loop
l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
,p_name => delete_risk_rec.name
,p_description => delete_risk_rec.description
,p_entity_name1 => 'AMW_LINE_DEL_RISK_ETTY'
,p_entity_name2 => 'AMW_REVISION_ETTY'
,p_pk1_value => delete_risk_rec.risk_id
);
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
for delete_ctrl_rec in c_delete_ctrl loop
l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
,p_name => delete_ctrl_rec.name
,p_description => delete_ctrl_rec.description
,p_entity_name1 => 'AMW_LINE_DEL_CTRL_ETTY'
,p_entity_name2 => 'AMW_REVISION_ETTY'
,p_pk1_value => delete_ctrl_rec.control_id
);
select ecot.change_order_type_id
,ESE.SUBJECT_ID
INTO L_LINE_TYPE_ID
,L_CHANGE_SUBJECT_ID
from eng_change_order_types ecot
,eng_subject_entities ese
where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
and ecot.SUBJECT_ID=ese.subject_id
AND ESE.ENTITY_NAME='AMW_LINE_CTRL_ETTY'
AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
AND ESE.SUBJECT_LEVEL=1;
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE CONTROL LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE CONTROL LINES
select ecot.change_order_type_id
,ESE.SUBJECT_ID
INTO L_DELETE_LINE_TYPE_ID
,L_DELETE_CHANGE_SUBJECT_ID
from eng_change_order_types ecot
,eng_subject_entities ese
where ecot.change_mgmt_type_code='AMW_PROCESS_RL_APPROVAL'
and ecot.SUBJECT_ID=ese.subject_id
AND ESE.ENTITY_NAME='AMW_LINE_DEL_CTRL_ETTY'
AND ESE.PARENT_ENTITY_NAME='AMW_REVISION_ETTY'
AND ESE.SUBJECT_LEVEL=1;
SELECT apo.process_id process_id,
apo.display_name display_name,
apo.revision_number revision_number,
apo.approval_status approval_status,
apo.description description
from amw_process_organization_vl apo, amw_latest_hierarchies alh
where
alh.organization_id = p_pk1
and alh.parent_id = p_pk2
and apo.process_id = alh.child_id
and apo.end_date is null
and apo.approval_date is null;
select parent_child_id as process_id
,a.DISPLAY_NAME
,a.REVISION_NUMBER
,a.approval_status
,a.description
from amw_org_hierarchy_denorm d, amw_latest_rev_org_v a
where d.process_id = p_pk2 --processId
and d.organization_id = p_pk1 --organizationId
and up_down_ind = 'D'
and hierarchy_type = 'L'
and a.process_id = d.PARENT_CHILD_ID
and a.organization_id = p_pk1 --organizationId
and a.end_date is null
and a.approval_status <> 'A'
and a.process_id in (select child_process_id
from AMW_curr_app_HIERARCHY_ORG_V
where parent_process_id=p_pk2
and child_organization_id=p_pk1);
SELECT ALR.PROCESS_ID
,ALR.DISPLAY_NAME
,ALR.DESCRIPTION
,ALR.PROCESS_CODE
--,alr.revision_number
FROM amw_latest_rev_org_v ALR
,AMW_LATEST_HIERARCHY_ORG_V APHD
WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
and alr.ORGANIZATION_ID=p_pk1 --organizationId
and aphd.CHILD_ORGANIZATION_ID=p_pk1 --organizationId
AND APHD.parent_PROCESS_ID=p_pk2 --processId
minus
SELECT ALR.PROCESS_ID
,ALR.DISPLAY_NAME
,ALR.DESCRIPTION
,ALR.PROCESS_CODE
--,alr.revision_number
FROM amw_latest_rev_org_v ALR
,AMW_CURR_APP_HIERARCHY_ORG_V APHD
WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
and alr.ORGANIZATION_ID=p_pk1 --organizationId
and aphd.CHILD_ORGANIZATION_ID=p_pk1 --organizationId
AND APHD.parent_PROCESS_ID=p_pk2; --processId
cursor c_delete_process is
SELECT ALR.PROCESS_ID
,ALR.DISPLAY_NAME
,ALR.DESCRIPTION
,ALR.PROCESS_CODE
--,alr.revision_number
FROM amw_latest_rev_org_v ALR
,AMW_CURR_APP_HIERARCHY_ORG_V APHD
WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
and alr.ORGANIZATION_ID=p_pk1 --organizationId
and aphd.CHILD_ORGANIZATION_ID=p_pk1 --organizationId
AND APHD.parent_PROCESS_ID=p_pk2 --processId
minus
SELECT ALR.PROCESS_ID
,ALR.DISPLAY_NAME
,ALR.DESCRIPTION
,ALR.PROCESS_CODE
--,alr.revision_number
FROM amw_latest_rev_org_v ALR
,AMW_LATEST_HIERARCHY_ORG_V APHD
WHERE APHD.CHILD_process_ID=ALR.PROCESS_ID
and alr.ORGANIZATION_ID=p_pk1 --organizationId
and aphd.CHILD_ORGANIZATION_ID=p_pk1 --organizationId
AND APHD.parent_PROCESS_ID=p_pk2; --processId
(select arav.risk_id
,ARAV.NAME
,ARAV.DESCRIPTION
FROM AMW_RISKS_ALL_VL ARAV
,AMW_RISK_ASSOCIATIONS ARA
WHERE ARA.RISK_ID=ARAV.RISK_ID
AND ARAV.LATEST_REVISION_FLAG='Y'
AND ARA.OBJECT_TYPE='PROCESS_ORG'
AND ARA.DELETION_DATE IS NULL
AND ARA.PK1=p_pk1 --organizationId
and ara.pk2=p_pk2) --processId
minus
(select arav.risk_id
,ARAV.NAME
,ARAV.DESCRIPTION
FROM AMW_RISKS_ALL_VL ARAV
,AMW_RISK_ASSOCIATIONS ARA
WHERE ARA.RISK_ID=ARAV.RISK_ID
AND ARAV.LATEST_REVISION_FLAG='Y'
AND ARA.OBJECT_TYPE='PROCESS_ORG'
AND ARA.APPROVAL_DATE IS not NULL
and ara.DELETION_APPROVAL_DATE is null
and ara.pk1=p_pk1 --organizationId
AND ARA.PK2=p_pk2); --processId
cursor c_delete_risk is
(select arav.risk_id
,ARAV.NAME
,ARAV.DESCRIPTION
FROM AMW_RISKS_ALL_VL ARAV
,AMW_RISK_ASSOCIATIONS ARA
WHERE ARA.RISK_ID=ARAV.RISK_ID
AND ARAV.LATEST_REVISION_FLAG='Y'
AND ARA.OBJECT_TYPE='PROCESS_ORG'
AND ARA.APPROVAL_DATE IS not NULL
and ara.DELETION_APPROVAL_DATE is null
and ara.pk1=p_pk1 --organizationId
AND ARA.PK2=p_pk2) --processId
minus
(select arav.risk_id
,ARAV.NAME
,ARAV.DESCRIPTION
FROM AMW_RISKS_ALL_VL ARAV
,AMW_RISK_ASSOCIATIONS ARA
WHERE ARA.RISK_ID=ARAV.RISK_ID
AND ARAV.LATEST_REVISION_FLAG='Y'
AND ARA.OBJECT_TYPE='PROCESS_ORG'
AND ARA.DELETION_DATE IS NULL
AND ARA.PK1=p_pk1 --organizationId
and ara.pk2=p_pk2); --processId
(select acav.control_id
,acav.NAME
,acav.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL acav
,AMW_CONTROL_ASSOCIATIONS aca
WHERE aca.control_id=acav.control_id
AND acav.LATEST_REVISION_FLAG='Y'
AND aca.OBJECT_TYPE='RISK_ORG'
AND aca.DELETION_DATE IS NULL
AND aca.PK1=p_pk1 ---organizationId
and aca.pk2=p_pk2) --processId
minus
(select acav.control_id
,acav.NAME
,acav.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL acav
,AMW_CONTROL_ASSOCIATIONS aca
WHERE aca.control_id=acav.control_id
AND acav.LATEST_REVISION_FLAG='Y'
AND aca.OBJECT_TYPE='RISK_ORG'
AND aca.approval_DATE IS not NULL
and aca.deletion_approval_date is null
AND aca.PK1=p_pk1 ---organizationId
and aca.pk2=p_pk2); --processId
cursor c_delete_ctrl is
(select acav.control_id
,acav.NAME
,acav.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL acav
,AMW_CONTROL_ASSOCIATIONS aca
WHERE aca.control_id=acav.control_id
AND acav.LATEST_REVISION_FLAG='Y'
AND aca.OBJECT_TYPE='RISK_ORG'
AND aca.approval_DATE IS not NULL
and aca.deletion_approval_date is null
AND aca.PK1=p_pk1 ---organizationId
and aca.pk2=p_pk2) --processId
minus
(select acav.control_id
,acav.NAME
,acav.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL acav
,AMW_CONTROL_ASSOCIATIONS aca
WHERE aca.control_id=acav.control_id
AND acav.LATEST_REVISION_FLAG='Y'
AND aca.OBJECT_TYPE='RISK_ORG'
AND aca.DELETION_DATE IS NULL
AND aca.PK1=p_pk1 ---organizationId
and aca.pk2=p_pk2); --processId
SELECT ACAV.CONTROL_ID
,ACAV.NAME
,ACAV.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL ACAV
,AMW_CONTROL_ASSOCIATIONS ACA
,(select distinct child_id from
amw_latest_hierarchies
START WITH CHILD_ID = p_pk2 AND ORGANIZATION_ID = p_pk1
CONNECT BY PRIOR CHILD_ID = PARENT_ID
and organization_id = p_pk1 ) AOHD
WHERE ACA.PK1 = p_pk1
AND AOHD.CHILD_ID=ACA.PK2
AND ACA.OBJECT_TYPE='RISK_ORG'
AND ACA.DELETION_DATE IS NULL
AND ACA.CONTROL_ID=ACAV.CONTROL_ID
AND ACAV.CURR_APPROVED_FLAG='Y' ;
SELECT ACAV.CONTROL_ID
,ACAV.NAME
,ACAV.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL ACAV
,AMW_CONTROL_ASSOCIATIONS ACA
,AMW_ORG_HIERARCHY_DENORM AOHD
WHERE AOHD.UP_DOWN_IND='D'
AND AOHD.ORGANIZATION_ID=ACA.PK1
AND AOHD.PARENT_CHILD_ID=ACA.PK2
AND ACA.OBJECT_TYPE='RISK_ORG'
AND ACA.DELETION_DATE IS NULL
AND ACA.CONTROL_ID=ACAV.CONTROL_ID
AND ACAV.CURR_APPROVED_FLAG='Y'
AND AOHD.ORGANIZATION_ID=P_PK1
AND AOHD.PROCESS_ID=P_PK2
--NPANANDI 12.16.2004: ADDED BELOW TO RESTRICT ROWS RETURNED
--TO BE THOSE FROM LATEST HIERARCHY
AND AOHD.HIERARCHY_TYPE='L'
UNION
SELECT ACAV.CONTROL_ID
,ACAV.NAME
,ACAV.DESCRIPTION
FROM AMW_CONTROLS_ALL_VL ACAV
,AMW_CONTROL_ASSOCIATIONS ACA
WHERE ACA.OBJECT_TYPE='RISK_ORG'
AND ACA.DELETION_DATE IS NULL
AND ACAV.LATEST_REVISION_FLAG='Y'
AND ACAV.CONTROL_ID=ACA.CONTROL_ID
AND ACA.PK1=P_PK1 --ORGID
AND ACA.PK2=P_PK2; --PROCESSID
L_DELETE_CHANGE_SUBJECT_ID NUMBER;
L_DELETE_LINE_TYPE_ID NUMBER;
select revision_number into l_revision_number
from amw_latest_rev_org_v
where organization_id=p_pk1
and process_id=add_proc_rec.process_id;
for delete_proc_rec in c_delete_process loop
l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
---in this case, the DeleteProcess Cursor's 'minus' clause causes problems in
---revisionNumber data, and incorrect resultSet is obtained.
select child_revision_number into l_revision_number
from amw_curr_app_hierarchy_org_v
where child_organization_id=p_pk1 --organizationId
and parent_process_id=p_pk2 --parentProcessId
and child_process_id=delete_proc_rec.process_id; --childProcessId;
,p_name => delete_proc_rec.display_name
,p_description => delete_proc_rec.description
,p_entity_name1 => 'AMW_ORG_LINE_DEL_PROCESS_ETTY'
,p_entity_name2 => 'AMW_ORG_REV_ETTY'
,p_pk1_value => p_pk1 --organizationId
,p_pk2_value => delete_proc_rec.process_id
,p_pk3_value => l_revision_number
);
FOR delete_RISK_REC IN c_delete_risk LOOP
L_SEQ_NUM_INCR := NVL(L_SEQ_NUM_INCR,0)+10;
,p_name => delete_risk_rec.name
,p_description => delete_risk_rec.description
,p_entity_name1 => 'AMW_ORG_LINE_DEL_RISK_ETTY'
,p_entity_name2 => 'AMW_ORG_REV_ETTY'
,p_pk1_value => delete_risk_rec.risk_id
);
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
for delete_ctrl_rec in c_delete_ctrl loop
l_seq_num_incr := nvl(l_seq_num_incr,0)+10;
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE RISK LINES
,p_name => delete_ctrl_rec.name
,p_description => delete_ctrl_rec.description
,p_entity_name1 => 'AMW_ORG_LINE_DEL_CTRL_ETTY'
,p_entity_name2 => 'AMW_ORG_REV_ETTY'
,p_pk1_value => delete_ctrl_rec.control_id
);
select ecot.change_order_type_id
,ESE.SUBJECT_ID
INTO L_LINE_TYPE_ID
,L_CHANGE_SUBJECT_ID
from eng_change_order_types ecot
,eng_subject_entities ese
where ecot.change_mgmt_type_code='AMW_PROCESS_ORG_APPROVAL'
and ecot.SUBJECT_ID=ese.subject_id
AND ESE.ENTITY_NAME='AMW_ORG_LINE_CTRL_ETTY'
AND ESE.PARENT_ENTITY_NAME='AMW_ORG_REV_ETTY'
AND ESE.SUBJECT_LEVEL=1;
L_DELETE_LINE_TYPE_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
L_DELETE_CHANGE_SUBJECT_ID := NULL; --SET THIS TO NULL FOR DELETE PROCESS LINES
select ecot.change_order_type_id
,ESE.SUBJECT_ID
INTO L_DELETE_LINE_TYPE_ID
,L_DELETE_CHANGE_SUBJECT_ID
from eng_change_order_types ecot
,eng_subject_entities ese
where ecot.change_mgmt_type_code='AMW_PROCESS_ORG_APPROVAL'
and ecot.SUBJECT_ID=ese.subject_id
AND ESE.ENTITY_NAME='AMW_ORG_LINE_DEL_CTRL_ETTY'
AND ESE.PARENT_ENTITY_NAME='AMW_ORG_REV_ETTY'
AND ESE.SUBJECT_LEVEL=1;
SELECT ENG_CHANGE_LINES_S.NEXTVAL
INTO L_CHANGE_LINE_ID
FROM DUAL;
ENG_CHANGE_LINES_PKG.INSERT_ROW(
X_ROWID => LX_ROW_ID
,X_CHANGE_LINE_ID => L_CHANGE_LINE_ID
,X_REQUEST_ID => NULL
,X_CHANGE_ID => P_CHANGE_ID
,X_SEQUENCE_NUMBER => p_SEQ_NUM_INCR
,X_CHANGE_TYPE_ID => p_LINE_TYPE_ID
,X_STATUS_CODE => '11'
,X_ASSIGNEE_ID => NULL --DON'T NEED SINCE LINES AREN'T ASSIGNED
,X_NEED_BY_DATE => NULL
,X_ORIGINAL_SYSTEM_REFERENCE => NULL
,X_NAME => p_name
,X_DESCRIPTION => p_description
,X_SCHEDULED_DATE => NULL
,X_IMPLEMENTATION_DATE => sysdate
,X_CANCELATION_DATE => NULL
,X_CREATION_DATE => SYSDATE
,X_CREATED_BY => G_USER_ID
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => G_USER_ID
,X_LAST_UPDATE_LOGIN => G_LOGIN_ID
,X_PROGRAM_ID => NULL
,X_PROGRAM_APPLICATION_ID => NULL
,X_PROGRAM_UPDATE_DATE => NULL
,X_APPROVAL_STATUS_TYPE => NULL
,X_APPROVAL_DATE => NULL
,X_APPROVAL_REQUEST_DATE => NULL
,X_ROUTE_ID => NULL
,X_REQUIRED_FLAG => NULL
,X_COMPLETE_BEFORE_STATUS_CODE => NULL
,X_START_AFTER_STATUS_CODE => NULL
);
SELECT ENG_CHANGE_SUBJECTS_S.NEXTVAL
INTO L_CHANGE_SUBJECT_ID
FROM DUAL;
INSERT INTO ENG_CHANGE_SUBJECTS (
CHANGE_SUBJECT_ID
,CHANGE_ID
,CHANGE_LINE_ID
,ENTITY_NAME
--02.03.2005 npanandi: added pk1 to pk5 to populate for Process/Risk/Ctrl Lines
,pk1_value
,pk2_value
,pk3_value
,pk4_value
,pk5_value
,SUBJECT_LEVEL
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
) VALUES (
L_CHANGE_SUBJECT_ID
,P_CHANGE_ID
,P_CHANGE_LINE_ID
,P_ENTITY_NAME
--02.03.2005 npanandi: added pk1 to pk5 to populate for Process/Risk/Ctrl Lines
,p_pk1_value
,p_pk2_value
,p_pk3_value
,p_pk4_value
,p_pk5_value
,P_SUBJECT_LEVEL
,SYSDATE
,G_USER_ID
,SYSDATE
,G_USER_ID
,G_LOGIN_ID
);
select ecot.change_order_type_id
INTO L_LINE_TYPE_ID
from eng_change_order_types ecot
,eng_subject_entities ese
where ecot.change_mgmt_type_code=p_change_mgmt_type_code
and ecot.SUBJECT_ID=ese.subject_id
AND ESE.ENTITY_NAME=p_entity_name
AND ESE.PARENT_ENTITY_NAME=p_parent_entity_name
AND ESE.SUBJECT_LEVEL=1;