[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* INSERTS ARE DONE AGAINST THE FOLLOWING TABLES */
/* Insert into Wf_Activities_B and Wf_Activities_Tl */
/* Insert/Updates into AMW_Process */
/* Insert/Updates into Wf_Process_Activities */
/* Updates amw_processes_interface, with error messages */
/* Deleting successful production inserts, based on profile */
/* */
/*****************************************************************************/
--
-- Used FOR EXCEPTION processing
--
G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
v_process_exist_no_update VARCHAR2(2000);
select parent_process_name,process_interface_id,PARENT_PROCESS_CODE
from amw_processes_interface
where batch_id=p_batch_id
--WORKAROUND FOR NULL CODES ...
and NVL(parent_process_CODE,'-1234567890') not in (
select PROCESS_CODE
from amw_LATEST_REVISIONS_v);
select count(1)
into l_process_name
from amw_processes_interface
where batch_id=p_batch_id
and process_display_name = l_parent_name.parent_process_name
AND PROCESS_CODE IN (
SELECT PARENT_PROCESS_CODE
FROM AMW_PROCESSES_INTERFACE
WHERE BATCH_ID=P_BATCH_ID
AND PARENT_PROCESS_CODE NOT IN (SELECT PROCESS_CODE
FROM AMW_LATEST_REVISIONS_v)
);
SELECT BATCH_ID
,PROCESS_INTERFACE_ID
,SIGNIFICANT_PROCESS_FLAG
,NVL(STANDARD_PROCESS_FLAG,'N') AS STANDARD_PROCESS_FLAG
,NVL(APPROVAL_STATUS,'D') AS APPROVAL_STATUS
,PROCESSED_FLAG
,ERROR_FLAG
,INTERFACE_STATUS
,PROCESS_CATEGORY
,NVL(REVISE_PROCESS_FLAG,'R') AS REVISE_PROCESS_FLAG
,PARENT_PROCESS_NAME
,PROCESS_DISPLAY_NAME
,PARENT_PROCESS_ID
,PROCESS_TYPE
,CONTROL_ACTIVITY_TYPE
,PROCESS_CODE
,PROCESS_SEQUENCE_NUMBER
,PARENT_PROCESS_CODE
,ATTACHMENT_URL
FROM AMW_PROCESSES_INTERFACE
WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
AND processed_flag IS NULL
AND error_flag IS NULL
order by process_interface_id asc;
SELECT BATCH_ID
,PROCESS_INTERFACE_ID
,SIGNIFICANT_PROCESS_FLAG
,NVL(STANDARD_PROCESS_FLAG,'Y') AS STANDARD_PROCESS_FLAG
,NVL(APPROVAL_STATUS,'D') AS APPROVAL_STATUS
,PROCESSED_FLAG
,ERROR_FLAG
,INTERFACE_STATUS
,PROCESS_CATEGORY
,NVL(REVISE_PROCESS_FLAG,'R') AS REVISE_PROCESS_FLAG
,PARENT_PROCESS_NAME
,PROCESS_DISPLAY_NAME
,PARENT_PROCESS_ID
,PROCESS_TYPE
,CONTROL_ACTIVITY_TYPE
,PROCESS_CODE
,PROCESS_SEQUENCE_NUMBER
,PARENT_PROCESS_CODE
,ATTACHMENT_URL
,CERTIFICATION_STATUS
---12.29.2004 NPANANDI: ADDED STD VAR d COLS
,STANDARD_VARIATION
,CLASSIFICATION
---04.22.2005 npanandi: added 3 owner columns below
,process_owner_id
,finance_owner_id
,application_owner_id
FROM AMW_PROCESSES_INTERFACE
WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
AND processed_flag IS NULL
AND error_flag IS NULL
order by process_interface_id asc;
SELECT BATCH_ID
,PROCESS_INTERFACE_ID
,PROCESSED_FLAG
,ERROR_FLAG
,INTERFACE_STATUS
,NVL(REVISE_PROCESS_FLAG,'R') AS REVISE_PROCESS_FLAG
,PARENT_PROCESS_NAME
,PROCESS_DISPLAY_NAME
,PARENT_PROCESS_ID
,PROCESS_TYPE
,PROCESS_CODE
,NVL(PROCESS_SEQUENCE_NUMBER,-100) AS PROCESS_SEQUENCE_NUMBER
,PARENT_PROCESS_CODE
FROM AMW_PROCESSES_INTERFACE
WHERE created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
AND processed_flag IS NULL
AND error_flag IS NULL
order by process_interface_id asc;
SELECT BATCH_ID
,PROCESS_INTERFACE_ID
,PROCESSED_FLAG
,ERROR_FLAG
,INTERFACE_STATUS
,NVL(REVISE_PROCESS_FLAG,'R') AS REVISE_PROCESS_FLAG
,PARENT_PROCESS_NAME
,PROCESS_DISPLAY_NAME
,PARENT_PROCESS_ID
,PROCESS_TYPE
,PROCESS_CODE
,NVL(PROCESS_SEQUENCE_NUMBER,-100) AS PROCESS_SEQUENCE_NUMBER
,PARENT_PROCESS_CODE
,NVL(APPROVAL_STATUS,'D') AS APPROVAL_STATUS
FROM AMW_PROCESSES_INTERFACE
WHERE CREATED_BY=DECODE(p_user_id, NULL, created_by, p_user_id)
AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
AND processed_flag IS NULL
AND error_flag IS NULL
order by process_interface_id asc;
e_process_exist_no_update EXCEPTION;
select count(process_id) into l_count_processes from amw_process_vl;
SELECT PROCESS_ID,ITEM_TYPE
INTO L_PROCESS_ID,L_PROCESS_ITEM_TYPE
FROM AMW_PROCESS_VL
WHERE END_DATE IS NULL
AND PROCESS_CODE=L_PROCESS_CODE;
SELECT PROCESS_ID,ITEM_TYPE
INTO L_PARENT_PROCESS_ID,L_PARENT_PROCESS_ITEM_TYPE
FROM AMW_PROCESS_VL
WHERE END_DATE IS NULL
AND PROCESS_CODE=L_PARENT_PROCESS_CODE;
SELECT 1
INTO L_HIER_DENORM_CHK
FROM AMW_PROC_HIERARCHY_DENORM
WHERE PARENT_CHILD_ID=L_PARENT_PROCESS_ID
AND PROCESS_ID=L_PROCESS_ID
AND UP_DOWN_IND='D';
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: insert records Start '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
FOR INSERT_REC IN C_GET_INTF_ROWS LOOP
---HERE IF ALL ABOVE CHECKS ARE FINE, THEN ALL PROCESS CODES,
---PROCESS DISPLAY NAMES AND THE PROCESS ITEM TYPES ARE KNOWN
--- .... SO START PROCESSING, WE HAVE TO WORRY ONLY ABOUT THE
---PROCESS AND IT'S ATTRIBUTES HERE, NOT CARE FOR THE
---PARENT PROCESS ASSOCIATIONS YET
--SET THESE VALUES TO NULL FOR THIS PARTICULAR LOOP,
--LEST PARAM VALUES GET CORRUPTED ....
L_PROCESS_CODE := NULL;
L_PROCESS_CODE := INSERT_REC.PROCESS_CODE;
L_PARENT_PROCESS_CODE := INSERT_REC.PARENT_PROCESS_CODE;
SELECT PROCESS_ID,ITEM_TYPE
INTO L_PROCESS_ID,L_PROCESS_ITEM_TYPE
FROM AMW_PROCESS_VL
WHERE END_DATE IS NULL
AND PROCESS_CODE=L_PROCESS_CODE;
SELECT PROCESS_ID,ITEM_TYPE
INTO L_PARENT_PROCESS_ID,L_PARENT_PROCESS_ITEM_TYPE
FROM AMW_PROCESS_VL
WHERE END_DATE IS NULL
AND PROCESS_CODE=L_PARENT_PROCESS_CODE;
L_PROCESS_REC.SIGNIFICANT_PROCESS_FLAG := INSERT_REC.SIGNIFICANT_PROCESS_FLAG;
L_PROCESS_REC.STANDARD_PROCESS_FLAG := INSERT_REC.STANDARD_PROCESS_FLAG;
L_PROCESS_REC.CERTIFICATION_STATUS := INSERT_REC.CERTIFICATION_STATUS;
L_PROCESS_REC.PROCESS_CATEGORY := INSERT_REC.PROCESS_CATEGORY;
L_PROCESS_REC.PROCESS_TYPE := INSERT_REC.PROCESS_TYPE;
L_PROCESS_REC.CONTROL_ACTIVITY_TYPE := INSERT_REC.CONTROL_ACTIVITY_TYPE;
L_PROCESS_REC.DISPLAY_NAME := INSERT_REC.PROCESS_DISPLAY_NAME;
L_PROCESS_REC.ATTACHMENT_URL := INSERT_REC.ATTACHMENT_URL;
L_PROCESS_REC.STANDARD_VARIATION := INSERT_REC.STANDARD_VARIATION;
L_PROCESS_REC.CLASSIFICATION := INSERT_REC.CLASSIFICATION;
l_process_rec.process_owner_id := insert_rec.process_owner_id;
l_process_rec.finance_owner_id := insert_rec.finance_owner_id;
l_process_rec.application_owner_id := insert_rec.application_owner_id;
L_PROCESS_REC.PROCESS_CODE := INSERT_REC.PROCESS_CODE;
INSERT_AMW_PROCESS(
P_PROCESS_REC => L_PROCESS_REC
,X_RETURN_STATUS => LX_RETURN_STATUS
,X_MSG_COUNT => LX_MSG_COUNT
,X_MSG_DATA => LX_MSG_DATA
);
update_interface_with_error (v_err_msg
,'AMW_PROCESS_VL'
,INSERT_REC.PROCESS_INTERFACE_ID);
IF(L_PROCESS_ID IS NOT NULL AND INSERT_REC.REVISE_PROCESS_FLAG='R' and not v_error_found) THEN
--THIS PROCESS EXISTS, SO REVISE, IF NECESSARY, AND THEN UPDATE ...
AMW_RL_HIERARCHY_PKG.REVISE_PROCESS_IF_NECESSARY(
P_PROCESS_ID => L_PROCESS_ID
);
--- security check during updates
/**
l_has_access := check_function(
p_function => 'AMW_UPD_RL_PROC'
,p_object_name => 'AMW_PROCESS_APPR_ETTY'
,p_instance_pk1_value => L_PROCESS_ID
,p_user_id => fnd_global.user_id);
v_err_msg := 'Cannot update this Process';
update_interface_with_error (v_err_msg
,'AMW_PROCESS_VL'
,INSERT_REC.PROCESS_INTERFACE_ID);
update_interface_with_error (v_err_msg
,'AMW_PROCESS_VL'
,INSERT_REC.PROCESS_INTERFACE_ID);
-- for a delete
---03.02.2005 npanandi: added check in the IF condition for v_error_found
IF(L_PROCESS_ID IS NOT NULL AND L_PARENT_PROCESS_ID IS NOT NULL AND INSERT_REC.REVISE_PROCESS_FLAG='D' and not v_error_found) THEN
--SET THESE VALUES APPROPRIATELY ....
lx_return_status := FND_API.G_RET_STS_SUCCESS;
AMW_RL_HIERARCHY_PKG.DELETE_CHILD(
P_PARENT_PROCESS_ID => L_PARENT_PROCESS_ID
,P_CHILD_PROCESS_ID => L_PROCESS_ID
,X_RETURN_STATUS => LX_RETURN_STATUS
,X_MSG_COUNT => LX_MSG_COUNT
,X_MSG_DATA => LX_MSG_COUNT);
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: insert records End '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
FOR INSERT_LINKS IN C_CREATE_LINKS LOOP
--SET THESE VALUES TO NULL FOR THIS PARTICULAR LOOP,
--LEST PARAM VALUES GET CORRUPTED ....
L_PROCESS_CODE := NULL;
L_PROCESS_CODE := INSERT_LINKS.PROCESS_CODE;
L_PARENT_PROCESS_CODE := INSERT_LINKS.PARENT_PROCESS_CODE;
L_PROCESS_SEQUENCE_NUMBER := INSERT_LINKS.PROCESS_SEQUENCE_NUMBER;
SELECT PROCESS_ID,ITEM_TYPE
INTO L_PROCESS_ID,L_PROCESS_ITEM_TYPE
FROM AMW_LATEST_REVISIONS_v
WHERE PROCESS_CODE=L_PROCESS_CODE;
SELECT PROCESS_ID,ITEM_TYPE
INTO L_PARENT_PROCESS_ID,L_PARENT_PROCESS_ITEM_TYPE
FROM AMW_LATEST_REVISIONS_v
WHERE PROCESS_CODE=L_PARENT_PROCESS_CODE;
if(L_PARENT_PROCESS_ID is not null and L_PROCESS_ID is not null and INSERT_LINKS.revise_process_flag='R' and not v_error_found)then
AMW_RL_HIERARCHY_PKG.add_WEBADI_HIERARCHY_LINKS(
P_CHILD_ORDER_NUMBER => L_PROCESS_SEQUENCE_NUMBER
,P_PARENT_PROCESS_ID => L_PARENT_PROCESS_ID
,P_CHILD_PROCESS_ID => L_PROCESS_ID
,X_RETURN_STATUS => LX_RETURN_STATUS
,X_MSG_COUNT => LX_MSG_COUNT
,X_MSG_DATA => LX_MSG_DATA);
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: update denorm, count Start '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => -1);
AMW_RL_HIERARCHY_PKG.update_all_latest_rc_counts(p_mode => 'RC');
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: update denorm, count End '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: insert Approvals Start '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
FOR INSERT_APPR IN C_CREATE_APPROVALS LOOP
L_PROCESS_CODE := NULL;
L_PROCESS_CODE := INSERT_APPR.PROCESS_CODE;
L_PARENT_PROCESS_CODE := INSERT_APPR.PARENT_PROCESS_CODE;
L_PROCESS_APPROVAL_STATUS := INSERT_APPR.APPROVAL_STATUS;
L_INTF_ID := INSERT_APPR.PROCESS_INTERFACE_ID;
L_REVISE_FLAG := INSERT_APPR.REVISE_PROCESS_FLAG;
p_batch_id => insert_appr.batch_id
,p_process_interface_id => insert_appr.process_interface_id);
SELECT PROCESS_ID,ITEM_TYPE,approval_status,display_name
INTO L_PROCESS_ID,L_PROCESS_ITEM_TYPE,l_db_proc_appr_status,l_db_process_display_name
FROM AMW_LATEST_REVISIONS_v
WHERE PROCESS_CODE=L_PROCESS_CODE;
update_interface_with_error (v_err_msg,v_table_name,L_INTF_ID);
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: insert Approvals End '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: update approved denorm Start '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
amw_rl_hierarchy_pkg.update_approved_denorm(-1);
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: update approved denorm End '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: update approved Risk/Ctrl count Start '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
amw_rl_hierarchy_pkg.update_appr_control_counts;
amw_rl_hierarchy_pkg.update_appr_risk_counts;
fnd_file.put_line(fnd_file.LOG, 'TIMECHECK: update approved Risk/Ctrl count End '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
UPDATE amw_processes_interface
SET processed_flag = l_process_flag
,last_update_date = SYSDATE
,last_updated_by = v_user_id
WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG,'err in update process flag: '||SUBSTR (SQLERRM, 1,200));
DELETE FROM amw_processes_interface WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG,'err in delete interface records: '||SUBSTR (SQLERRM,1, 200));
UPDATE AMW_PROCESSES_INTERFACE
SET ERROR_FLAG='Y'
,INTERFACE_STATUS='Upwards Interface hierarchy defined for this Process is Invalid'
WHERE PROCESS_INTERFACE_ID=V_INTERFACE_ID;
UPDATE AMW_PROCESSES_INTERFACE
SET ERROR_FLAG='Y'
,INTERFACE_STATUS='Please enter valid Process Code for this new Process'
WHERE PROCESS_INTERFACE_ID=L_INTF_ID;
UPDATE AMW_PROCESSES_INTERFACE
SET ERROR_FLAG='Y'
,INTERFACE_STATUS='Process Approval status is Draft for Process(es)
below this Process in the Risk Library'
WHERE PROCESS_INTERFACE_ID=L_INTF_ID;
UPDATE amw_processes_interface
SET error_flag = 'Y'
,interface_status = v_err_msg
WHERE batch_id = p_batch_id;
when e_process_exist_no_update THEN
BEGIN
---rollback;
IF v_process_exist_no_update is null THEN
v_process_exist_no_update := FND_MESSAGE.GET_STRING('AMW','AMW_PROCESS_EXIST');
UPDATE amw_processes_interface
SET error_flag = 'Y'
,interface_status = v_process_exist_no_update
WHERE batch_id = p_batch_id;
fnd_file.put_line (fnd_file.LOG, 'unexpected EXCEPTION in handling e_process_exist_no_update: '||sqlerrm);
UPDATE amw_processes_interface
SET error_flag = 'Y'
,interface_status = v_invalid_hierarchy_msg
WHERE batch_id = p_batch_id;
UPDATE amw_processes_interface
SET error_flag = 'Y'
---,interface_status = 'Invalid hierarchy FOR this row'
,interface_status = v_err_msg
WHERE batch_id = p_batch_id
and process_interface_id = v_new_parent_interface_id;
UPDATE amw_processes_interface
SET error_flag = 'Y'
,interface_status = v_no_import_privilege_msg
WHERE batch_id = p_batch_id;
SELECT COUNT(*) INTO L_INTF_ID
from amw_processes_interface api
where (api.revise_process_flag='R'
or API.REVISE_PROCESS_FLAG IS NULL)
and api.process_code is null
and api.batch_id=P_BATCH_ID
and exists (select 1
from amw_latest_revisions_v
where display_name=api.process_display_name);
SELECT PROCESS_INTERFACE_ID INTO L_INTF_ID
from amw_processes_interface api
where (api.revise_process_flag='R'
or API.REVISE_PROCESS_FLAG IS NULL)
and api.process_code is null
and api.batch_id=P_BATCH_ID
and exists (select 1
from amw_latest_revisions_v
where display_name=api.process_display_name)
AND ROWNUM <=1;
SELECT PROCESS_INTERFACE_ID
,PROCESS_CODE
,PROCESS_DISPLAY_NAME
,REVISE_PROCESS_FLAG
,PARENT_PROCESS_CODE
,PARENT_PROCESS_NAME
FROM AMW_PROCESSES_INTERFACE
WHERE BATCH_ID=P_BATCH_ID
AND PROCESS_CODE IS NULL
AND PROCESS_DISPLAY_NAME NOT IN (SELECT DISPLAY_NAME
FROM AMW_LATEST_REVISIONS_v);
select process_interface_id
,parent_process_name
,revise_process_flag
from amw_processes_interface api
where batch_id = p_batch_id
and parent_process_code is null
and exists (select 1
from amw_latest_revisions_v
where display_name = api.parent_process_name);
select process_interface_id
,process_display_name
,revise_process_flag
from amw_processes_interface api
where batch_id = p_batch_id
and process_code is null
and exists (select 1
from amw_latest_revisions_v
where display_name = api.process_display_name);
UPDATE AMW_PROCESSES_INTERFACE
SET PROCESS_CODE=L_PRC_CODE
WHERE PROCESS_INTERFACE_ID=L_INTF_ID
AND BATCH_ID=P_BATCH_ID;
UPDATE AMW_PROCESSES_INTERFACE
SET PARENT_PROCESS_CODE=L_PRC_CODE
WHERE BATCH_ID=P_BATCH_ID
AND PARENT_PROCESS_NAME=L_DISP_NAME;
select process_code
into l_process_code
from amw_latest_revisions_v
where display_name = c.parent_process_name;
---do the update of the interface table here
update amw_processes_interface
set parent_process_code = l_process_code
,last_update_date = sysdate
where process_interface_id = c.process_interface_id
and batch_id = p_batch_id;
v_err_msg := 'Please select valid Parent Process Code for this Process';
update_interface_with_error (v_err_msg,v_table_name,L_INTF_ID);
v_err_msg := 'Please select valid Parent Process Code for this Process';
update_interface_with_error (v_err_msg,v_table_name,L_INTF_ID);
select process_code
into l_process_code
from amw_latest_revisions_v
where display_name = c1.process_display_name;
update amw_processes_interface
set process_code = l_process_code
,last_update_date = sysdate
where process_interface_id = c1.process_interface_id
and batch_id = p_batch_id;
v_err_msg := 'Please select valid Process Code for this Process';
update_interface_with_error (v_err_msg,v_table_name,L_INTF_ID);
v_err_msg := 'Please select valid Process Code for this Process';
update_interface_with_error (v_err_msg,v_table_name,L_INTF_ID);
SELECT PARENT_PROCESS_CODE
,PROCESS_INTERFACE_ID
FROM AMW_PROCESSES_INTERFACE
WHERE PROCESS_CODE=P_PARENT_PROCESS_CODE
AND BATCH_ID=P_BATCH_ID;
select parent_process_name
,process_interface_id
from amw_processes_interface
where process_display_name = p_process_display_name
and batch_id = p_batch_id;
PROCEDURE update_interface_with_error (
p_err_msg IN VARCHAR2
,p_table_name IN VARCHAR2
,p_interface_id IN NUMBER
)
IS
l_interface_status amw_processes_interface.interface_status%TYPE;
ROLLBACK; -- rollback any inserts done during the current LOOP process
SELECT interface_status
INTO l_interface_status
FROM amw_processes_interface
WHERE process_interface_id = p_interface_id;
UPDATE amw_processes_interface
SET interface_status =
l_interface_status
-- || 'Error Msg: '
|| p_err_msg
-- || ' Table Name: '
-- || p_table_name
|| '**'
,error_flag = 'Y'
WHERE process_interface_id = p_interface_id;
END update_interface_with_error;
SELECT 'Y'
FROM fnd_responsibility r, fnd_compiled_menu_functions m, fnd_FORm_functions f
WHERE r.responsibility_id = fnd_global.resp_id
AND r.application_id=fnd_global.resp_appl_id
AND r.menu_id = m.menu_id
AND m.function_id = f.function_id
AND f.function_name = p_function_name;
SELECT 'Y'
FROM fnd_resp_functions rf, fnd_FORm_functions f
WHERE rf.application_id = fnd_global.resp_appl_id
AND rf.responsibility_id = fnd_global.resp_id
AND rf.rule_type = 'F'
AND rf.action_id = f.function_id
AND f.function_name = p_function_name;
SELECT 'Y'
FROM AMW_PROCESS_VL
WHERE PROCESS_CODE=p_PARENT_PROCESS_CODE;
SELECT 'Y'
FROM AMW_PROC_HIERARCHY_DENORM
WHERE PROCESS_ID IN (SELECT PROCESS_ID
FROM AMW_PROCESS_VL
WHERE PROCESS_CODE=p_PARENT_child_CODE)
AND PARENT_CHILD_ID IN (SELECT PROCESS_ID
FROM AMW_PROCESS_VL
WHERE PROCESS_CODE=p_PROCESS_CODE)
AND UP_DOWN_IND='U';
SELECT distinct 'Y'
FROM AMW_LATEST_REVISIONS_V ALRV
,AMW_PROC_HIERARCHY_DENORM APHD
WHERE APHD.UP_DOWN_IND='D'
AND ALRV.PROCESS_ID=APHD.PARENT_CHILD_ID
AND APHD.PROCESS_ID IN (SELECT ALRV.PROCESS_ID
FROM AMW_LATEST_REVISIONS_V ALRV
,AMW_PROCESSES_INTERFACE API
WHERE API.PROCESS_INTERFACE_ID=P_PROCESS_INTERFACE_ID
AND API.PROCESS_CODE=ALRV.PROCESS_CODE
AND API.PROCESS_DISPLAY_NAME=ALRV.display_name)
AND APHD.PARENT_CHILD_ID NOT IN (SELECT ALRV.PROCESS_ID
FROM AMW_LATEST_REVISIONS_V ALRV
,AMW_PROCESSES_INTERFACE API
WHERE API.BATCH_ID=P_BATCH_ID
AND API.PROCESS_CODE=ALRV.PROCESS_CODE
AND API.PROCESS_DISPLAY_NAME=ALRV.display_name
AND API.APPROVAL_STATUS='A');
PROCEDURE INSERT_AMW_PROCESS(
P_PROCESS_REC IN AMW_PROCESS_REC
,X_RETURN_STATUS OUT NOCOPY VARCHAR2
,X_MSG_COUNT OUT NOCOPY NUMBER
,X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
l_process_id number;
select amw_process_s.nextval into l_process_id from dual;
select amw_process_s.nextval into l_process_rev_id from dual;
select amw_process_name_s.nextval into l_process_name from dual;
insert into amw_process (
PROCESS_ID,
ITEM_TYPE,
NAME,
PROCESS_CODE,
REVISION_NUMBER,
PROCESS_REV_ID,
APPROVAL_STATUS,
START_DATE,
SIGNIFICANT_PROCESS_FLAG,
STANDARD_PROCESS_FLAG,
CERTIFICATION_STATUS,
PROCESS_CATEGORY,
STANDARD_VARIATION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
PROCESS_TYPE,
CONTROL_ACTIVITY_TYPE,
---12.29.2004 NPANANDI: ADDED STD VAR d COLS
CLASSIFICATION
) VALUES (
l_process_id,
P_PROCESS_REC.item_type,
l_process_name,
P_PROCESS_REC.process_code,
1,
l_process_rev_id,
'D',
sysdate,
P_PROCESS_REC.significant_process_flag,
P_PROCESS_REC.standard_process_flag,
P_PROCESS_REC.certification_status,
P_PROCESS_REC.process_category,
l_standard_variation,
sysdate,
G_USER_ID,
sysdate,
G_USER_ID,
G_LOGIN_ID,
1,
P_PROCESS_REC.process_type,
P_PROCESS_REC.control_activity_type,
---12.29.2004 NPANANDI: ADDED STD VAR d COLS
P_PROCESS_REC.CLASSIFICATION
);
insert into amw_process_names_tl (
process_id
,revision_number
,process_rev_id
,display_name
,description
,language
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,object_version_number
)
select
l_process_id
,1
,l_process_rev_id
,P_PROCESS_REC.display_name
,P_PROCESS_REC.description
,L.LANGUAGE_CODE
,USERENV('LANG')
,sysdate
,g_user_id
,sysdate
,g_user_id
,g_login_id
,1
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B');
--SELECT fnd_documents_s.nextval INTO L_DOCUMENT_ID FROM DUAL;
SELECT MAX(SEQ_NUM) INTO L_SEQ_NUM FROM FND_ATTACHED_DOCUMENTS WHERE ENTITY_NAME='AMW_PROCESS' AND PK1_VALUE=l_process_rev_id;
END INSERT_AMW_PROCESS;
SELECT PROCESS_REV_ID,process_id
INTO L_PROCESS_REV_ID,l_process_id
FROM AMW_LATEST_REVISIONS_v
WHERE PROCESS_ID=P_PROCESS_REC.PROCESS_ID;
select 1
into L_IS_STD_VAR
from amw_process ap
where (select process_id
from amw_process
where process_rev_id = ap.standard_variation) =
(select process_id
from amw_process
where process_rev_id = L_PROCESS_REV_ID)
and ap.end_date is null;
UPDATE AMW_PROCESS
SET APPROVAL_STATUS=P_PROCESS_REC.APPROVAL_STATUS
,SIGNIFICANT_PROCESS_FLAG=P_PROCESS_REC.SIGNIFICANT_PROCESS_FLAG
---,STANDARD_PROCESS_FLAG=P_PROCESS_REC.STANDARD_PROCESS_FLAG
,STANDARD_PROCESS_FLAG=l_STANDARD_PROCESS_FLAG
,CERTIFICATION_STATUS=P_PROCESS_REC.CERTIFICATION_STATUS
,PROCESS_CATEGORY=P_PROCESS_REC.PROCESS_CATEGORY
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
,PROCESS_TYPE=P_PROCESS_REC.PROCESS_TYPE
,CONTROL_ACTIVITY_TYPE=P_PROCESS_REC.CONTROL_ACTIVITY_TYPE
,LAST_UPDATE_DATE=SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
---12.29.2004 NPANANDI: ADDED STD VARIATION COL UPDATE
,STANDARD_VARIATION=L_STANDARD_VARIATION
WHERE PROCESS_ID=P_PROCESS_REC.PROCESS_ID
AND END_DATE IS NULL;
select classification
into l_classification
from amw_process
where process_id=p_process_rec.process_id
and end_date is null;
select w.work_type_id into l_classification_new from amw_work_types_b w,
amw_work_categories_b c
where w.work_type_code = 'AMW_UNDEF'
and w.category_id = c.category_id
and category_code = 'RL_PROCESS';
update amw_process
set classification = p_process_rec.classification
,last_update_date = sysdate
,last_updated_by = g_user_id
,last_update_login = g_login_id
where process_id=p_process_rec.process_id
and end_date is null;
/*** fnd_file.put_line(fnd_file.LOG, '^^^^^^^^^^^^^^^^ Am out of the updates' ); ***/
update AMW_PROCESS_NAMES_TL
set DISPLAY_NAME = P_PROCESS_REC.DISPLAY_NAME
---03.28.2005 npanandi: commented out update on description
---bug 4241577 fix
---,DESCRIPTION = P_PROCESS_REC.DESCRIPTION
---03.28.2005 npanandi: bugfix ends
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY=G_USER_ID
,LAST_UPDATE_LOGIN=G_LOGIN_ID
,SOURCE_LANG = userenv('LANG')
,OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
where PROCESS_REV_ID = L_PROCESS_REV_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
--SELECT fnd_documents_s.nextval INTO L_DOCUMENT_ID FROM DUAL;
SELECT MAX(SEQ_NUM) INTO L_SEQ_NUM FROM FND_ATTACHED_DOCUMENTS WHERE ENTITY_NAME='AMW_PROCESS' AND PK1_VALUE=l_process_rev_id;
select person_party_id
from fnd_user
where user_id=p_user_id;
select user_name from fnd_user where user_id=p_user_id;
select count(party_name)
into l_count_owners
from amw_owner_roles_v
where role_name=p_role_name
and pk1_value=to_char(p_pk1_value);
select grant_guid
into l_grant_guid
from amw_owner_roles_v
where role_name=p_role_name
and pk1_value=to_char(p_pk1_value);