APPS.HXC_NOTIFICATION_HELPER SQL Statements

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

Line: 16

      SELECT has.run_recipient_extensions
        FROM hxc_approval_styles has, hxc_approval_comps hac
       WHERE hac.approval_comp_id = p_approval_comp_id
         AND has.approval_style_id = hac.approval_style_id;

Line: 47

   IS select count(1)  FROM hxc_approval_styles has,
                       hxc_approval_comps hac,
                       hxc_app_comp_notifications hacn,
                       hxc_app_comp_notif_usages hacnu
                 WHERE has.approval_style_id = p_approval_style_id
                   AND has.approval_style_id = hac.approval_style_id
                   AND hac.approval_comp_id = hacnu.approval_comp_id
                   AND hac.object_version_number = hacnu.approval_comp_ovn
                   AND hacnu.comp_notification_id = hacn.comp_notification_id
                   AND hacn.notification_action_code =
                       hxc_app_comp_notifications_api.
                         c_action_request_appr_resend
                   AND hacnu.enabled_flag = 'Y';

Line: 86

      SELECT notification_number_retries
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacnu.comp_notification_ovn = hacn.object_version_number
         AND hacn.notification_action_code =
                  hxc_app_comp_notifications_api.c_action_request_appr_resend
         AND hacnu.enabled_flag = 'Y';

Line: 119

      SELECT notification_timeout_value
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacnu.comp_notification_ovn = hacn.object_version_number
         AND hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND'
         AND hacn.notification_recipient_code = 'APPROVER'
         AND hacnu.enabled_flag = 'Y';

Line: 153

      SELECT hacn.notification_timeout_value
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacnu.comp_notification_ovn = hacn.object_version_number
         AND hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND'
         AND hacn.notification_recipient_code = 'PREPARER'
         AND hacnu.enabled_flag = 'Y';

Line: 187

      SELECT hacn.notification_timeout_value
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacnu.comp_notification_ovn = hacn.object_version_number
         AND hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND'
         AND hacn.notification_recipient_code = 'ADMIN'
         AND hacnu.enabled_flag = 'Y';

Line: 221

      SELECT COUNT (1)
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacnu.comp_notification_ovn = hacn.object_version_number
         AND hacn.notification_action_code = 'REQUEST-APPROVAL'
         AND hacn.notification_recipient_code = 'SUPERVISOR'
         AND hacnu.enabled_flag = 'Y';

Line: 262

      SELECT COUNT (1)
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacnu.comp_notification_ovn = hacn.object_version_number
         AND hacn.notification_action_code = 'SUBMISSION'
         AND hacn.notification_recipient_code = 'WORKER'
         AND hacnu.enabled_flag = 'Y';

Line: 305

      SELECT COUNT (1)
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacn.notification_action_code = 'AUTO-APPROVE'
         AND hacn.notification_recipient_code = 'WORKER'
         AND hacnu.enabled_flag = 'Y';

Line: 348

      SELECT COUNT (1)
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacn.notification_action_code = 'APPROVED'
         AND hacn.notification_recipient_code = 'PREPARER'
         AND hacnu.enabled_flag = 'Y';

Line: 391

      SELECT COUNT (1)
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacn.notification_action_code = 'REJECTED'
         AND hacn.notification_recipient_code = 'PREPARER'
         AND hacnu.enabled_flag = 'Y';

Line: 433

      SELECT COUNT (1)
        FROM hxc_approval_styles has,
             hxc_approval_comps hac,
             hxc_app_comp_notifications hacn,
             hxc_app_comp_notif_usages hacnu
       WHERE has.approval_style_id = p_approval_style_id
         AND has.approval_style_id = hac.approval_style_id
         AND hac.approval_comp_id = hacnu.approval_comp_id
         AND hac.object_version_number = hacnu.approval_comp_ovn
         AND hacnu.comp_notification_id = hacn.comp_notification_id
         AND hacn.notification_action_code = 'TRANSFER'
         AND hacn.notification_recipient_code = 'PREPARER'
         AND hacnu.enabled_flag = 'Y';

Line: 637

Procedure update_notification_records
 ( p_approval_style_id             in number
  ,p_approval_style_name           in varchar2
  ,p_timeouts_enabled              in varchar2
  ,p_number_retries        	   in number
  ,p_approver_timeout       	   in number
  ,p_preparer_timeout       	   in number
  ,p_admin_timeout          	   in number
  ,p_notify_supervisor             in varchar2
  ,p_notify_worker_on_submit       in varchar2
  ,p_notify_worker_on_aa           in varchar2
  ,p_notify_preparer_approved      in varchar2
  ,p_notify_preparer_rejected      in varchar2
  ,p_notify_preparer_transfer      in varchar2
 ) is
 l_comp_notification_id   hxc_app_comp_notifications.comp_notification_id%type;

Line: 669

 select
 approval_style_id,
 timeouts_enabled,
 number_retries,
 approver_timeout,
 preparer_timeout,
 admin_timeout,
 notify_supervisor,
 notify_worker_on_submit,
 notify_worker_on_aa,
 notify_preparer_approved,
 notify_preparer_rejected,
 notify_preparer_transfer
 from hxc_approval_styles_v
 where
 approval_style_id=p_approval_style_id;

Line: 694

 select hacn.comp_notification_id,hacn.object_version_number
 from
 hxc_approval_styles has,
 hxc_approval_comps hac,
 hxc_app_comp_notifications hacn,
 hxc_app_comp_notif_usages hacnu
 where
 has.approval_style_id = p_approval_style_id and
 has.approval_style_id = hac.approval_style_id and
 hac.approval_comp_id=hacnu.approval_comp_id and
 hac.object_version_number=hacnu.approval_comp_ovn and
 hacnu.comp_notification_id=hacn.comp_notification_id and
 hacnu.comp_notification_ovn=hacn.object_version_number and
 hacn.notification_action_code = p_notification_action_code and
 hacn.notification_recipient_code=p_notification_recipient_code;

Line: 718

   select  hacn.comp_notification_id, hacn.object_version_number
   from hxc_app_comp_notifications hacn where
   notification_number_retries=p_notification_number_retries and
   notification_timeout_value=p_notification_timeout_value and
   notification_action_code='REQUEST-APPROVAL-RESEND' and
   notification_recipient_code=p_notification_recipient_code and
   object_version_number=(select max(object_version_number)
                          from hxc_app_comp_notifications
                          where comp_notification_id=hacn.comp_notification_id);

Line: 741

 select count(1) from
 hxc_approval_comps hac,
 hxc_app_comp_notifications hacn,
 hxc_app_comp_notif_usages hacnu
 where
 hac.approval_comp_id not in (select hac.approval_comp_id
                        from hxc_approval_comps hac
                        where
                        approval_style_id = p_approval_style_id) and
 hac.approval_comp_id=hacnu.approval_comp_id and
 hacn.comp_notification_id=hacnu.comp_notification_id and
 hacn.object_version_number=hacnu.comp_notification_ovn and
 hacnu.comp_notification_id=p_comp_notification_id and
 hacnu.comp_notification_ovn=p_object_version_number;

Line: 758

is select hac.approval_comp_id,hac.object_version_number
from
hxc_approval_styles has,
hxc_approval_comps hac
where
has.name=p_approval_style and
has.approval_style_id = hac.approval_style_id and
hac.object_version_number = (SELECT MAX (object_version_number)
                                      FROM hxc_approval_comps
                                     WHERE approval_comp_id =hac.approval_comp_id);

Line: 770

 is select hacnu.approval_comp_id,hacnu.approval_comp_ovn,
 hacnu.comp_notification_id,hacnu.comp_notification_ovn from
 hxc_app_comp_notif_usages hacnu,
 hxc_approval_styles has,
 hxc_approval_comps hac,
 hxc_app_comp_notifications hacn
 where
 has.approval_style_id=p_approval_style_id and
 has.approval_style_id=hac.approval_style_id and
 hac.approval_comp_id=hacnu.approval_comp_id and
 hac.object_version_number=hacnu.approval_comp_ovn and
 hacnu.comp_notification_id=hacn.comp_notification_id and
 hacn.notification_action_code='REQUEST-APPROVAL-RESEND' and
 hacnu.enabled_flag='N';

Line: 808

   delete
   from hxc_app_comp_notif_usages where
   approval_comp_id=l_approval_comp_id and
   approval_comp_ovn=l_approval_comp_ovn and
   comp_notification_id=l_comp_notification_id and
   comp_notification_ovn=l_comp_notification_ovn;

Line: 884

       update hxc_app_comp_notif_usages
       set
       comp_notification_id  = l_comp_notif_id_exist,
       comp_notification_ovn = l_ovn_exist
       where
       approval_comp_id = l_approval_comp_id and
       approval_comp_ovn= l_approval_comp_ovn and
       comp_notification_id=l_comp_notification_id and
       comp_notification_ovn=l_object_version_number;

Line: 899

         hxc_app_comp_notifications_api.update_app_comp_notification
         (
            p_comp_notification_id         => l_comp_notification_id
           ,p_object_version_number        => l_object_version_number
           ,p_notification_number_retries  => p_number_retries
           ,p_notification_timeout_value   => p_approver_timeout
         );

Line: 924

          delete from hxc_app_comp_notif_usages
          where
          approval_comp_id  =l_approval_comp_id and
          approval_comp_ovn =l_approval_comp_ovn and
          comp_notification_id=l_comp_notification_id and
          comp_notification_ovn = l_object_version_number;

Line: 959

       update hxc_app_comp_notif_usages
       set
       comp_notification_id  = l_comp_notif_id_exist,
       comp_notification_ovn = l_ovn_exist
       where
       approval_comp_id = l_approval_comp_id and
       approval_comp_ovn= l_approval_comp_ovn and
       comp_notification_id=l_comp_notification_id and
       comp_notification_ovn=l_object_version_number;

Line: 976

         hxc_app_comp_notifications_api.update_app_comp_notification
         (
            p_comp_notification_id         => l_comp_notification_id
           ,p_object_version_number        => l_object_version_number
           ,p_notification_number_retries  => p_number_retries
           ,p_notification_timeout_value   => p_preparer_timeout
         );

Line: 1003

          delete from hxc_app_comp_notif_usages
          where
          approval_comp_id  =l_approval_comp_id and
          approval_comp_ovn =l_approval_comp_ovn and
          comp_notification_id=l_comp_notification_id and
          comp_notification_ovn = l_object_version_number;

Line: 1038

       update hxc_app_comp_notif_usages
       set
       comp_notification_id  = l_comp_notif_id_exist,
       comp_notification_ovn = l_ovn_exist
       where
       approval_comp_id = l_approval_comp_id and
       approval_comp_ovn= l_approval_comp_ovn and
       comp_notification_id=l_comp_notification_id and
       comp_notification_ovn=l_object_version_number;

Line: 1055

         hxc_app_comp_notifications_api.update_app_comp_notification
         (
            p_comp_notification_id         => l_comp_notification_id
           ,p_object_version_number        => l_object_version_number
           ,p_notification_number_retries  => p_number_retries
           ,p_notification_timeout_value   => p_admin_timeout
         );

Line: 1082

          delete from hxc_app_comp_notif_usages
          where
          approval_comp_id  =l_approval_comp_id and
          approval_comp_ovn =l_approval_comp_ovn and
          comp_notification_id=l_comp_notification_id and
          comp_notification_ovn = l_object_version_number;

Line: 1119

       update hxc_app_comp_notif_usages
       set
       comp_notification_id  = l_comp_notif_id_exist,
       comp_notification_ovn = l_ovn_exist
       where
       approval_comp_id = l_approval_comp_id and
       approval_comp_ovn= l_approval_comp_ovn and
       comp_notification_id=l_comp_notification_id and
       comp_notification_ovn=l_object_version_number;

Line: 1136

         hxc_app_comp_notifications_api.update_app_comp_notification
         (
            p_comp_notification_id         => l_comp_notification_id
           ,p_object_version_number        => l_object_version_number
           ,p_notification_timeout_value   => p_approver_timeout
         );

Line: 1162

          delete from hxc_app_comp_notif_usages
          where
          approval_comp_id  =l_approval_comp_id and
          approval_comp_ovn =l_approval_comp_ovn and
          comp_notification_id=l_comp_notification_id and
          comp_notification_ovn = l_object_version_number;

Line: 1199

       update hxc_app_comp_notif_usages
       set
       comp_notification_id  = l_comp_notif_id_exist,
       comp_notification_ovn = l_ovn_exist
       where
       approval_comp_id = l_approval_comp_id and
       approval_comp_ovn= l_approval_comp_ovn and
       comp_notification_id=l_comp_notification_id and
       comp_notification_ovn=l_object_version_number;

Line: 1216

         hxc_app_comp_notifications_api.update_app_comp_notification
         (
            p_comp_notification_id         => l_comp_notification_id
           ,p_object_version_number        => l_object_version_number
           ,p_notification_timeout_value   => p_preparer_timeout
         );

Line: 1242

          delete from hxc_app_comp_notif_usages
          where
          approval_comp_id  =l_approval_comp_id and
          approval_comp_ovn =l_approval_comp_ovn and
          comp_notification_id=l_comp_notification_id and
          comp_notification_ovn = l_object_version_number;

Line: 1280

       update hxc_app_comp_notif_usages
       set
       comp_notification_id  = l_comp_notif_id_exist,
       comp_notification_ovn = l_ovn_exist
       where
       approval_comp_id = l_approval_comp_id and
       approval_comp_ovn= l_approval_comp_ovn and
       comp_notification_id=l_comp_notification_id and
       comp_notification_ovn=l_object_version_number;

Line: 1296

         hxc_app_comp_notifications_api.update_app_comp_notification
         (
            p_comp_notification_id         => l_comp_notification_id
           ,p_object_version_number        => l_object_version_number
           ,p_notification_timeout_value   => p_admin_timeout
         );

Line: 1322

          delete from hxc_app_comp_notif_usages
          where
          approval_comp_id  =l_approval_comp_id and
          approval_comp_ovn =l_approval_comp_ovn and
          comp_notification_id=l_comp_notification_id and
          comp_notification_ovn = l_object_version_number;

Line: 1362

  update hxc_app_comp_notif_usages
    set
    enabled_flag='N' where
    approval_comp_id in (select approval_comp_id from hxc_approval_comps
                         where approval_style_id=p_approval_style_id) and
    comp_notification_id=l_comp_notification_id and
    comp_notification_ovn=l_object_version_number;

Line: 1393

   update hxc_app_comp_notif_usages
    set
    enabled_flag='N' where
    approval_comp_id in (select approval_comp_id from hxc_approval_comps
                         where approval_style_id=p_approval_style_id) and
    comp_notification_id=l_comp_notification_id and
    comp_notification_ovn=l_object_version_number;

Line: 1423

   update hxc_app_comp_notif_usages
    set
    enabled_flag='N' where
    approval_comp_id in (select approval_comp_id from hxc_approval_comps
                         where approval_style_id=p_approval_style_id) and
    comp_notification_id=l_comp_notification_id and
    comp_notification_ovn=l_object_version_number;

Line: 1454

   update hxc_app_comp_notif_usages
    set
    enabled_flag='N' where
    approval_comp_id in (select approval_comp_id from hxc_approval_comps
                         where approval_style_id=p_approval_style_id) and
    comp_notification_id=l_comp_notification_id and
    comp_notification_ovn=l_object_version_number;

Line: 1484

   update hxc_app_comp_notif_usages
    set
    enabled_flag='N' where
    approval_comp_id in (select approval_comp_id from hxc_approval_comps
                         where approval_style_id=p_approval_style_id) and
    comp_notification_id=l_comp_notification_id and
    comp_notification_ovn=l_object_version_number;

Line: 1514

   update hxc_app_comp_notif_usages
    set
    enabled_flag='N' where
    approval_comp_id in (select approval_comp_id from hxc_approval_comps
                         where approval_style_id=p_approval_style_id) and
    comp_notification_id=l_comp_notification_id and
    comp_notification_ovn=l_object_version_number;

Line: 1525

end update_notification_records;

Line: 1533

Procedure delete_notification_records
(
 p_approval_style_id in hxc_approval_styles.approval_style_id%type
) is


--
-- Cursor to fetch all the notifications associated with a approval_style
--
cursor csr_notif_id
(p_approval_style_id in number) is
select distinct hacn.comp_notification_id,hacn.object_version_number
from
hxc_approval_styles has,
hxc_approval_comps hac,
hxc_app_comp_notifications hacn,
hxc_app_comp_notif_usages hacnu
where
has.approval_style_id = p_approval_style_id and
has.approval_style_id = hac.approval_style_id and
hac.approval_comp_id=hacnu.approval_comp_id and
hac.object_version_number=hacnu.approval_comp_ovn and
hacnu.comp_notification_id=hacn.comp_notification_id and
hacnu.comp_notification_ovn=hacn.object_version_number;

Line: 1563

    select count(1) from
    hxc_approval_comps hac,
    hxc_app_comp_notifications hacn,
    hxc_app_comp_notif_usages hacnu
    where
    hac.approval_comp_id not in (select hac.approval_comp_id from hxc_approval_comps hac
    where approval_style_id = p_approval_style_id) and
    hac.approval_comp_id=hacnu.approval_comp_id and
    hacn.comp_notification_id=hacnu.comp_notification_id and
    hacn.object_version_number=hacnu.comp_notification_ovn and
    hacnu.comp_notification_id=p_comp_notification_id and
    hacnu.comp_notification_ovn=p_object_version_number;

Line: 1596

    hxc_app_comp_notifications_api.delete_app_comp_notification
         (
          p_comp_notification_id   =>  l_comp_notification_id
         ,p_object_version_number  =>  l_object_version_number
         );

Line: 1609

     delete from hxc_app_comp_notif_usages
     where
     approval_comp_id in
     (select approval_comp_id
     from hxc_approval_comps where
     approval_style_id=p_approval_style_id) and
     comp_notification_id = l_comp_notification_id and
     comp_notification_ovn = l_object_version_number;

Line: 1625

end delete_notification_records;

Line: 1650

select count(1) from
hxc_app_comp_notif_usages hacnu,
hxc_approval_comps hac,
hxc_approval_comps hac1
where
hac.approval_comp_id=p_approval_comp_id and
hac.approval_comp_id<>hac1.approval_comp_id and
hac.approval_style_id=hac1.approval_style_id and
hac1.approval_comp_id=hacnu.approval_comp_id;

Line: 1662

select hac.approval_comp_id from
hxc_approval_styles has,
hxc_approval_comps hac
where
has.approval_style_id=p_approval_style_id and
has.approval_style_id=hac.approval_style_id and
hac.approval_comp_id in (select approval_comp_id from hxc_app_comp_notif_usages);

Line: 1671

select hacnu.comp_notification_id,hacnu.comp_notification_ovn,hacnu.enabled_flag
from
hxc_approval_comps hac,
hxc_app_comp_notif_usages hacnu
where
hac.approval_comp_id=p_approval_comp_id and
hacnu.approval_comp_id=hac.approval_comp_id;

Line: 1681

if (p_dml_action = 'INSERT')
then
  open csr_chk_comps_usages(p_approval_comp_id);

Line: 1695

         insert into
         hxc_app_comp_notif_usages
         (
           approval_comp_id
          ,approval_comp_ovn
          ,comp_notification_id
          ,comp_notification_ovn
          ,enabled_flag
         )
         values
         (
           p_approval_comp_id
          ,p_approval_comp_ovn
          ,l_comp_notification_id
          ,l_comp_notification_ovn
          ,l_enabled_flag
         );

Line: 1716

elsif (p_dml_action = 'DELETE')
then

  --For deleting rows corresponding to ELA
  delete from
  hxc_app_comp_notif_usages
  where
  approval_comp_id in (select approval_comp_id from
                       hxc_approval_comps where
                       PARENT_COMP_ID=p_approval_comp_id and
                       PARENT_COMP_OVN=p_approval_comp_ovn);

Line: 1728

  delete from
  hxc_app_comp_notif_usages
  where
  approval_comp_id  = p_approval_comp_id and
  approval_comp_ovn = p_approval_comp_ovn;