The following lines contain the word 'select', 'insert', 'update' or 'delete':
select TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:',''))
from fnd_grants grants,
fnd_objects obj,
fnd_menus granted_menu
where obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
AND grants.object_id = obj.object_id
AND grants.grantee_type ='USER'
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = to_char(p_ORGANIZATION_ID)
AND grants.instance_pk2_value = to_char(p_PROCESS_ID)
AND grants.grantee_key like 'HZ_PARTY%'
AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
and granted_menu.menu_name = 'AMW_ORG_PROC_OWNER_ROLE';
select parent_process_id
from amw_execution_scope
where entity_type = 'BUSIPROC_CERTIFICATION'
and entity_id = p_certification_id
and organization_id = p_organization_id
and process_id = p_process_id;
select TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:',''))
from fnd_grants grants,
fnd_objects obj,
fnd_menus granted_menu
where obj.obj_name = 'AMW_ORGANIZATION'
AND grants.object_id = obj.object_id
AND grants.grantee_type ='USER'
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = to_char(p_ORGANIZATION_ID)
AND grants.grantee_key like 'HZ_PARTY%'
AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
and granted_menu.menu_name = 'AMW_ORG_MANAGER_ROLE';
SELECT to_number(REPLACE(grants.grantee_key, 'HZ_PARTY:', '')) process_owner_id
FROM fnd_grants grants,
fnd_objects obj,
fnd_menus granted_menu
WHERE obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'USER'
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = to_char(p_organization_id)
AND grants.instance_pk2_value = to_char(p_process_id)
AND grants.grantee_key LIKE 'HZ_PARTY%'
AND nvl(grants.end_date, sysdate + 1) >= TRUNC(sysdate)
AND grants.menu_id = granted_menu.menu_id
AND granted_menu.menu_name = 'AMW_ORG_PROC_OWNER_ROLE';
SELECT parent_process_id
FROM amw_execution_scope
WHERE entity_type = 'BUSIPROC_CERTIFICATION'
AND entity_id = p_certification_id
AND organization_id = p_organization_id
AND process_id = p_process_id;
SELECT to_number(REPLACE(grants.grantee_key, 'HZ_PARTY:', '')) org_owner_id
FROM fnd_grants grants,
fnd_objects obj,
fnd_menus granted_menu
WHERE obj.obj_name = 'AMW_ORGANIZATION'
AND grants.object_id = obj.object_id
AND grants.grantee_type = 'USER'
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = to_char(p_organization_id)
AND grants.grantee_key LIKE 'HZ_PARTY%'
AND nvl(grants.end_date, sysdate + 1) >= TRUNC(sysdate)
AND grants.menu_id = granted_menu.menu_id
AND granted_menu.menu_name = 'AMW_ORG_MANAGER_ROLE';
select certification_id
from amw_certification_b
where OBJECT_TYPE='PROCESS'
and (LAST_REMINDER_DATE is null
OR (trunc(LAST_REMINDER_DATE) + CERTIFICATION_REMINDER <= trunc(SYSDATE)))
and CERTIFICATION_STATUS = 'ACTIVE';
select distinct organization_id
from amw_execution_scope exscope
where exscope.ENTITY_TYPE = 'BUSIPROC_CERTIFICATION'
and exscope.ENTITY_ID = c_cert_id
and exscope.level_id > 3
and not exists (select 'Y'
from amw_opinions_v opinion
where opinion.PK1_VALUE = exscope.ENTITY_ID
and opinion.PK2_VALUE = exscope.ORGANIZATION_ID
and opinion.PK3_VALUE = exscope.PROCESS_ID
and opinion.object_name = 'AMW_ORG_PROCESS'
and opinion.OPINION_TYPE_CODE = 'CERTIFICATION');
select distinct process_id
from amw_execution_scope exscope
where exscope.ENTITY_TYPE = 'BUSIPROC_CERTIFICATION'
and exscope.ENTITY_ID = c_cert_id
and exscope.organization_id = c_org_id
and exscope.level_id > 3
and not exists (select 'Y'
from amw_opinions_v opinion
where opinion.PK1_VALUE = exscope.ENTITY_ID
and opinion.PK2_VALUE = exscope.ORGANIZATION_ID
and opinion.PK3_VALUE = exscope.PROCESS_ID
and opinion.object_name = 'AMW_ORG_PROCESS'
and opinion.OPINION_TYPE_CODE = 'CERTIFICATION');
l_owner_tbl.delete;
g_ownerlist_tbl.delete; /* hyuen bug 5098058 */
g_proc_owner_tbl.delete;
AMW_PROCCERT_REMINDER_PKG.update_lastreminder_date
(p_certificaion_id => cert_rec.certification_id ,
x_return_status => x_return_status);
PROCEDURE update_lastreminder_date(p_certificaion_id IN number, x_return_status OUT NOCOPY VARCHAR2)
is
begin
fnd_file.put_line(fnd_file.LOG,
'Going to update LAST_REMINDER_DATE for '||p_certificaion_id);
update amw_certification_b
set LAST_REMINDER_DATE= sysdate
where CERTIFICATION_ID = p_certificaion_id;
'Updates LAST_REMINDER_DATE for '||p_certificaion_id);
'unexpected error in update_lastreminder_date: '||sqlerrm);
END update_lastreminder_date;
select certification_name
from amw_certification_vl
where certification_id=c_cert_id;
select employee_id
from amw_employees_current_v
where party_id = c_party_id;
PROCEDURE send_reminder_selected_procs(
p_organization_id IN Number,
p_entity_id IN Number,
p_process_id In Number,
x_return_status OUT NOCOPY VARCHAR2)
is
CURSOR Get_Pending_Cert_Proc IS
SELECT distinct scp.organization_id, scp.PROCESS_ID
FROM amw_execution_scope scp
where not exists (select 'Y'
from amw_opinions_v opinion
where opinion.PK1_VALUE = scp.ENTITY_ID
and opinion.PK2_VALUE = scp.ORGANIZATION_ID
and opinion.PK3_VALUE = scp.PROCESS_ID
and opinion.object_name = 'AMW_ORG_PROCESS'
and opinion.OPINION_TYPE_CODE = 'CERTIFICATION')
start with scp.ENTITY_ID=p_entity_id
and scp.ENTITY_TYPE='BUSIPROC_CERTIFICATION'
and scp.PROCESS_ID=p_process_id
and scp.ORGANIZATION_ID=p_organization_id
connect by PRIOR scp.PROCESS_ID=scp.PARENT_PROCESS_ID
and PRIOR scp.ENTITY_ID=scp.ENTITY_ID
AND PRIOR scp.ORGANIZATION_ID=scp.ORGANIZATION_ID
and PRIOR scp.ENTITY_TYPE=scp.ENTITY_TYPE;
select distinct
TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:',''))
process_owner_id
from amw_execution_scope exscope,
fnd_grants grants,
fnd_objects obj,
fnd_menus granted_menu
where exscope.ENTITY_TYPE = 'BUSIPROC_CERTIFICATION'
and exscope.ENTITY_ID = p_entity_id
and obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
AND grants.object_id = obj.object_id
AND grants.grantee_type ='USER'
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = to_char(exscope.ORGANIZATION_ID)
AND grants.instance_pk2_value = to_char(exscope.PROCESS_ID)
AND grants.grantee_key like 'HZ_PARTY%'
AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
AND grants.menu_id = granted_menu.menu_id
and granted_menu.menu_name = 'AMW_ORG_PROC_OWNER_ROLE'
and not exists (select 'Y'
from amw_opinions_v opinion
where opinion.PK1_VALUE = exscope.ENTITY_ID
and opinion.PK2_VALUE = exscope.ORGANIZATION_ID
and opinion.PK3_VALUE = exscope.PROCESS_ID
and opinion.object_name = 'AMW_ORG_PROCESS'
and opinion.OPINION_TYPE_CODE = 'CERTIFICATION')
and (exscope.organization_id, exscope.PROCESS_ID) in
(select exscopeB.organization_id, exscopeB.PROCESS_ID
from amw_execution_scope exscopeB
start with exscopeB.ENTITY_ID=p_entity_id
and exscopeB.ENTITY_TYPE='BUSIPROC_CERTIFICATION'
and exscopeB.PROCESS_ID=p_process_id
and exscopeB.ORGANIZATION_ID=p_organization_id
connect by
PRIOR exscopeB.PROCESS_ID=exscopeB.PARENT_PROCESS_ID
and PRIOR exscopeB.ENTITY_ID=exscopeB.ENTITY_ID
AND PRIOR exscopeB.ORGANIZATION_ID=exscopeB.ORGANIZATION_ID
and PRIOR exscopeB.ENTITY_TYPE=exscopeB.ENTITY_TYPE);
g_proc_owner_tbl.delete;
g_ownerlist_tbl.delete; /* hyuen bug 5098058 */
END send_reminder_selected_procs;