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.OTA_CERT_ENROLLMENT_API SQL Statements

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

Line: 246


procedure update_cert_enrollment
  (p_effective_date               in     date
  ,p_cert_enrollment_id           in     number
  ,p_object_version_number        in out nocopy number
  ,p_certification_id             in     number
  ,p_person_id                    in     number    default hr_api.g_number
  ,p_contact_id                   in     number    default hr_api.g_number
  ,p_certification_status_code    in     varchar2  default hr_api.g_varchar2
  ,p_completion_date              in     date      default hr_api.g_date
  ,p_unenrollment_date            in     date      default hr_api.g_date
  ,p_expiration_date              in     date      default hr_api.g_date
  ,p_earliest_enroll_date         in     date      default hr_api.g_date
  ,p_is_history_flag              in     varchar2  default hr_api.g_varchar2
  ,p_business_group_id            in     number    default hr_api.g_number
  ,p_attribute_category           in     varchar2  default hr_api.g_varchar2
  ,p_attribute1                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute2                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute3                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute4                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute5                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute6                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute7                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute8                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute9                   in     varchar2  default hr_api.g_varchar2
  ,p_attribute10                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute11                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute12                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute13                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute14                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute15                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute16                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute17                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute18                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute19                  in     varchar2  default hr_api.g_varchar2
  ,p_attribute20                  in     varchar2  default hr_api.g_varchar2
  ,p_enrollment_date	          in     date      default hr_api.g_date
  ,p_validate                     in boolean          default false
   ) is
  --
  -- Declare cursors and local variables
  --
  l_proc                    varchar2(72) := g_package||' Update Cert Enrollment';

Line: 296


  savepoint UPDATE_cert_enrollment;

Line: 300


  OTA_CERT_ENROLLMENT_bk2.update_cert_enrollment_b
  (  p_effective_date               => p_effective_date
    ,p_cert_enrollment_id           => p_cert_enrollment_id
    ,p_object_version_number        => p_object_version_number
    ,p_certification_id             => p_certification_id
    ,p_person_id                    => p_person_id
    ,p_contact_id                   => p_contact_id
    ,p_certification_status_code    => p_certification_status_code
    ,p_completion_date              => p_completion_date
    ,p_UNENROLLMENT_DATE            => p_UNENROLLMENT_DATE
    ,p_EXPIRATION_DATE              => p_EXPIRATION_DATE
    ,p_EARLIEST_ENROLL_DATE         => p_EARLIEST_ENROLL_DATE
    ,p_IS_HISTORY_FLAG              => p_IS_HISTORY_FLAG
    ,p_business_group_id            => p_business_group_id
    ,p_attribute_category           => p_attribute_category
    ,p_attribute1                   => p_attribute1
    ,p_attribute2                   => p_attribute2
    ,p_attribute3                   => p_attribute3
    ,p_attribute4                   => p_attribute4
    ,p_attribute5                   => p_attribute5
    ,p_attribute6                   => p_attribute6
    ,p_attribute7                   => p_attribute7
    ,p_attribute8                   => p_attribute8
    ,p_attribute9                   => p_attribute9
    ,p_attribute10                  => p_attribute10
    ,p_attribute11                  => p_attribute11
    ,p_attribute12                  => p_attribute12
    ,p_attribute13                  => p_attribute13
    ,p_attribute14                  => p_attribute14
    ,p_attribute15                  => p_attribute15
    ,p_attribute16                  => p_attribute16
    ,p_attribute17                  => p_attribute17
    ,p_attribute18                  => p_attribute18
    ,p_attribute19                  => p_attribute19
    ,p_attribute20                  => p_attribute20
    ,p_enrollment_date              => p_enrollment_date
    );

Line: 341


        (p_module_name => 'UPDATE_cert_enrollment'
        ,p_hook_type   => 'BP'
        );

Line: 392


  OTA_CERT_ENROLLMENT_bk2.update_cert_enrollment_a
  (  p_effective_date               => p_effective_date
    ,p_cert_enrollment_id           => p_cert_enrollment_id
    ,p_object_version_number        => p_object_version_number
    ,p_certification_id             => p_certification_id
    ,p_person_id                    => p_person_id
    ,p_contact_id                   => p_contact_id
    ,p_certification_status_code    => p_certification_status_code
    ,p_completion_date              => p_completion_date
    ,p_UNENROLLMENT_DATE            => p_UNENROLLMENT_DATE
    ,p_EXPIRATION_DATE              => p_EXPIRATION_DATE
    ,p_EARLIEST_ENROLL_DATE         => p_EARLIEST_ENROLL_DATE
    ,p_IS_HISTORY_FLAG              => p_IS_HISTORY_FLAG
    ,p_business_group_id            => p_business_group_id
    ,p_attribute_category           => p_attribute_category
    ,p_attribute1                   => p_attribute1
    ,p_attribute2                   => p_attribute2
    ,p_attribute3                   => p_attribute3
    ,p_attribute4                   => p_attribute4
    ,p_attribute5                   => p_attribute5
    ,p_attribute6                   => p_attribute6
    ,p_attribute7                   => p_attribute7
    ,p_attribute8                   => p_attribute8
    ,p_attribute9                   => p_attribute9
    ,p_attribute10                  => p_attribute10
    ,p_attribute11                  => p_attribute11
    ,p_attribute12                  => p_attribute12
    ,p_attribute13                  => p_attribute13
    ,p_attribute14                  => p_attribute14
    ,p_attribute15                  => p_attribute15
    ,p_attribute16                  => p_attribute16
    ,p_attribute17                  => p_attribute17
    ,p_attribute18                  => p_attribute18
    ,p_attribute19                  => p_attribute19
    ,p_attribute20                  => p_attribute20
    ,p_enrollment_date              => p_enrollment_date
    );

Line: 433


        (p_module_name => 'UPDATE_cert_enrollment'
        ,p_hook_type   => 'AP'
        );

Line: 457


    rollback to UPDATE_cert_enrollment;

Line: 469


    rollback to UPDATE_cert_enrollment;

Line: 473


end update_cert_enrollment;

Line: 479


procedure delete_cert_enrollment
  (p_cert_enrollment_id            in     number
  ,p_object_version_number         in     number
  ,p_validate                      in     boolean  default false

  ) is


  CURSOR get_person_info IS
  select person_id
  from ota_cert_enrollments
  where CERT_ENROLLMENT_ID = p_cert_enrollment_id;

Line: 494


  l_proc                    varchar2(72) := g_package||'DELETE_cert_enrollment';

Line: 509


  savepoint DELETE_cert_enrollment;

Line: 516


  OTA_CERT_ENROLLMENT_bk3.delete_cert_enrollment_b
  (p_cert_enrollment_id             => p_cert_enrollment_id
    ,p_object_version_number        => p_object_version_number
    );

Line: 523


        (p_module_name => 'DELETE_cert_enrollment'
        ,p_hook_type   => 'BP'
        );

Line: 540


  OTA_CERT_ENROLLMENT_bk3.delete_cert_enrollment_a
  (p_cert_enrollment_id             => p_cert_enrollment_id
    ,p_object_version_number        => p_object_version_number
    ,p_person_id                    => l_person_id
    );

Line: 548


        (p_module_name => 'DELETE_cert_enrollment'
        ,p_hook_type   => 'AP'
        );

Line: 570


    rollback to DELETE_cert_enrollment;

Line: 581


    rollback to DELETE_cert_enrollment;

Line: 584


end delete_cert_enrollment;

Line: 630


select
          b.certification_id certification_id
        , b.INITIAL_COMPLETION_DATE
        , b.INITIAL_COMPLETION_DURATION
        , b.INITIAL_COMPL_DURATION_UNITS
        , b.RENEWAL_DURATION
        , b.RENEWAL_DURATION_UNITS
        , b.NOTIFY_DAYS_BEFORE_EXPIRE
        , b.VALIDITY_DURATION
        , b.VALIDITY_DURATION_UNITS
        , b.RENEWABLE_FLAG
        , b.VALIDITY_START_TYPE
        , b.PUBLIC_FLAG
        , b.START_DATE_ACTIVE
        , b.END_DATE_ACTIVE
from ota_certifications_b b
where b.certification_id = p_certification_id;

Line: 649


select
          cre.cert_enrollment_id,
          cre.certification_id,
          cre.object_version_number,
          cre.certification_status_code
  from ota_cert_enrollments cre
 where cre.certification_id = p_certification_id
    and (cre.person_id = p_person_id or cre.contact_id = p_contact_id);

Line: 659


select cert_enrollment_id, certification_id, certification_status_code, object_version_number, completion_date
FROM ota_cert_enrollments
where cert_enrollment_id = p_cert_enrollment_id;

Line: 664


select cert_prd_enrollment_id, period_status_code, object_version_number, completion_date
FROM ota_cert_prd_enrollments
where cert_enrollment_id = p_cert_enrollment_id
and trunc(sysdate) between trunc(cert_period_start_date) and nvl(trunc(cert_period_end_date),trunc(sysdate))
and period_status_code = 'ACTIVE' --Bug#7705069
order by cert_prd_enrollment_id desc;

Line: 672


select cert_prd_enrollment_id
FROM ota_cert_prd_enrollments
where cert_enrollment_id = p_cert_enrollment_id;

Line: 874


           ota_cert_enrollment_api.update_cert_enrollment
      		          (p_effective_date               => sysdate
              		   ,p_cert_enrollment_id           => l_cert_enrollment_id
              		   ,p_certification_id             => p_certification_id
              		   ,p_object_version_number        => l_cert_enrl_rec.object_version_number
              		   ,p_certification_status_code    => l_certification_status_code
		               );

Line: 907


	     ota_cert_enrollment_api.update_cert_enrollment
	       		   (p_effective_date               => sysdate
	       		    ,p_cert_enrollment_id           => l_cert_enrollment_id
	       		    ,p_certification_id             => l_cert_enrl_rec.certification_id
	       		    ,p_object_version_number        => l_cert_enrl_rec.object_version_number
	       		    ,p_certification_status_code    => l_certification_status_code
                    ,p_is_history_flag              => p_is_history_flag
	       		    ,p_unenrollment_date            => null
	       		    ,p_attribute_category           => p_attribute_category
	 		        ,p_attribute1                   => p_attribute1
  	 		        ,p_attribute2                   => p_attribute2
	 		        ,p_attribute3                   => p_attribute3
	 		        ,p_attribute4                   => p_attribute4
	 		        ,p_attribute5                   => p_attribute5
	 		        ,p_attribute6                   => p_attribute6
	 		        ,p_attribute7                   => p_attribute7
	 		        ,p_attribute8                   => p_attribute8
	 		        ,p_attribute9                   => p_attribute9
	 		        ,p_attribute10                   => p_attribute10
	 		        ,p_attribute11                   => p_attribute11
	 		        ,p_attribute12                   => p_attribute12
	 		        ,p_attribute13                   => p_attribute13
	 		        ,p_attribute14                   => p_attribute14
	 		        ,p_attribute15                   => p_attribute15
	 		        ,p_attribute16                   => p_attribute16
	 		        ,p_attribute17                   => p_attribute17
	 		        ,p_attribute18                   => p_attribute18
	 		        ,p_attribute19                   => p_attribute19
                    		,p_attribute20                   => p_attribute20
                    ,p_enrollment_date               => trunc(nvl(p_enrollment_date, sysdate)));

Line: 954


	            --calculate period and mbr enroll status for existing data and update accordingly
	            ota_cpe_util.update_cpe_status(l_cert_prd_enrollment_id,
                                       p_certification_status_code => l_certification_status_code);

Line: 963


	           --update cre for exp date and earliest enroll date when creating new period
	           --calculate exp and earliest enrol dates
	           --for onetime certs there's no expiration
	           --for renewable certs calc based on initial_completion_date, INITIAL_COMPLETION_DURATION,
	           --validity_start_type and validity_duration
      	           OPEN csr_cert_enrl(l_recert_rec.cert_enrollment_id);

Line: 974


            	   ota_cert_enrollment_api.update_cert_enrollment
      		          (p_effective_date               => sysdate
              		   ,p_cert_enrollment_id           => l_cert_enrollment_id
              		   ,p_certification_id             => p_certification_id
              		   ,p_object_version_number        => l_cert_enrl_rec.object_version_number
              		   ,p_certification_status_code    => l_certification_status_code
                            ,p_is_history_flag              => p_is_history_flag
              		   ,p_earliest_enroll_date         => l_earliest_enroll_date
		               );