eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AMW_PROCCERT_REMINDER_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 38


      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';

Line: 54


      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;

Line: 62


      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';

Line: 137


  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';

Line: 153


  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;

Line: 161


  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';

Line: 211


      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';

Line: 219


      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');

Line: 234


      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');

Line: 267


      l_owner_tbl.delete;

Line: 268


      g_ownerlist_tbl.delete;   /* hyuen bug 5098058 */

Line: 270


        g_proc_owner_tbl.delete;

Line: 314


      AMW_PROCCERT_REMINDER_PKG.update_lastreminder_date
                 (p_certificaion_id => cert_rec.certification_id ,
		  x_return_status => x_return_status);

Line: 330


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);

Line: 336


   update amw_certification_b
   set    LAST_REMINDER_DATE= sysdate
   where  CERTIFICATION_ID = p_certificaion_id;

Line: 343


                'Updates LAST_REMINDER_DATE for '||p_certificaion_id);

Line: 347


		'unexpected error in update_lastreminder_date: '||sqlerrm);

Line: 348


END update_lastreminder_date;

Line: 363


       select certification_name
       from   amw_certification_vl
       where  certification_id=c_cert_id;

Line: 368


       select employee_id
       from   amw_employees_current_v
       where  party_id = c_party_id;

Line: 480


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;

Line: 507


      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);

Line: 557


   g_proc_owner_tbl.delete;

Line: 558


   g_ownerlist_tbl.delete;   /* hyuen bug 5098058 */

Line: 611


END send_reminder_selected_procs;