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_PRD_ENROLLMENT_API SQL Statements

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

Line: 228


procedure update_cert_prd_enrollment
  (p_effective_date               in     date
  ,p_cert_prd_enrollment_id       in     number
  ,p_object_version_number        in out nocopy number
  ,p_cert_enrollment_id           in     number
  ,p_period_status_code           in     varchar2
  ,p_completion_date              in     date      default hr_api.g_date
  ,p_cert_period_start_date       in     date      default hr_api.g_date
  ,p_cert_period_end_date         in     date      default hr_api.g_date
  ,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_expiration_date              in     date      default hr_api.g_date
  ,p_validate                     in     boolean          default false
   ) is
  --
  -- Declare cursors and local variables
  --
CURSOR csr_crt IS
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
        , cre.earliest_enroll_date
        , cre.expiration_date
from ota_certifications_b b,
     ota_cert_enrollments cre
where b.certification_id = cre.certification_id
  and cre.cert_enrollment_id = p_cert_enrollment_id;

Line: 289


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

Line: 301


select
        max(cpe.expiration_date)
from ota_cert_prd_enrollments cpe,
     ota_cert_enrollments cre
where cpe.cert_enrollment_id = cre.cert_enrollment_id
  and cre.cert_enrollment_id = p_cert_enrollment_id;

Line: 309


select
        cpe.expiration_date
from ota_cert_prd_enrollments cpe
where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id;

Line: 314


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

Line: 325


l_update_cre_dates_flag varchar2(1) := 'N';

Line: 335


  savepoint UPDATE_cert_prd_enrollment;

Line: 404


          l_update_cre_dates_flag := 'Y';

Line: 415


       	  l_update_cre_dates_flag := 'Y';

Line: 420


      if (l_update_cre_dates_flag = 'Y') then
	  ota_cert_enrollment_api.update_cert_enrollment
	      (p_effective_date               => sysdate
	       ,p_cert_enrollment_id           => p_cert_enrollment_id
	       ,p_certification_id             => rec_crt.certification_id
	       ,p_object_version_number        => l_cert_enrl_rec.object_version_number
	       ,p_certification_status_code    => l_cert_enrl_rec.certification_status_code
	       ,p_is_history_flag              => l_cert_enrl_rec.is_history_flag
	       ,p_completion_date              => p_completion_date
	       ,p_expiration_date              => l_expiration_date
	       ,p_earliest_enroll_date         => l_earliest_enroll_date
		   );

Line: 437


  OTA_CERT_PRD_ENROLLMENT_bk2.update_cert_prd_enrollment_b
  (  p_effective_date               => p_effective_date
    ,p_cert_prd_enrollment_id       => p_cert_prd_enrollment_id
    ,p_object_version_number        => p_object_version_number
    ,p_cert_enrollment_id           => p_cert_enrollment_id
    ,p_period_status_code           => p_period_status_code
    ,p_completion_date              => p_completion_date
    ,p_cert_period_start_date       => p_cert_period_start_date
    ,p_cert_period_end_date         => p_cert_period_end_date
    ,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_expiration_date              => p_expiration_date
    );

Line: 474


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

Line: 521


  OTA_CERT_PRD_ENROLLMENT_bk2.update_cert_prd_enrollment_a
  (  p_effective_date               => p_effective_date
    ,p_cert_prd_enrollment_id       => p_cert_prd_enrollment_id
    ,p_object_version_number        => p_object_version_number
    ,p_cert_enrollment_id           => p_cert_enrollment_id
    ,p_period_status_code           => p_period_status_code
    ,p_completion_date              => p_completion_date
    ,p_cert_period_start_date       => p_cert_period_start_date
    ,p_cert_period_end_date         => p_cert_period_end_date
    ,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_expiration_date              => p_expiration_date
    );

Line: 558


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

Line: 597


    ota_competence_ss.create_wf_process(p_process     =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
           p_itemtype         =>'HRSSA',
           p_person_id     => l_cert_enrl_rec.person_id,
           p_eventid       =>null,
           p_learningpath_ids => null,
            p_certification_id => l_cert_enrl_rec.certification_id ,
           p_itemkey    =>l_item_key);

Line: 632


    rollback to UPDATE_cert_prd_enrollment;

Line: 644


    rollback to UPDATE_cert_prd_enrollment;

Line: 648


end update_cert_prd_enrollment;

Line: 654


procedure delete_cert_prd_enrollment
  (p_cert_prd_enrollment_id        in     number
  ,p_object_version_number         in     number
  ,p_validate                      in     boolean  default false

  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc                    varchar2(72) := g_package||'DELETE_cert_prd_enrollment';

Line: 671


  savepoint DELETE_cert_prd_enrollment;

Line: 678


  OTA_CERT_PRD_ENROLLMENT_bk3.delete_cert_prd_enrollment_b
  (p_cert_prd_enrollment_id         => p_cert_prd_enrollment_id
    ,p_object_version_number        => p_object_version_number
    );

Line: 685


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

Line: 702


  OTA_CERT_PRD_ENROLLMENT_bk3.delete_cert_prd_enrollment_a
  (p_cert_prd_enrollment_id         => p_cert_prd_enrollment_id
    ,p_object_version_number        => p_object_version_number
    );

Line: 709


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

Line: 731


    rollback to DELETE_cert_prd_enrollment;

Line: 742


    rollback to DELETE_cert_prd_enrollment;

Line: 745


end delete_cert_prd_enrollment;

Line: 758


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,
     ota_cert_enrollments cre
where cre.certification_id = b.certification_id
  and cre.cert_enrollment_id = p_cert_enrollment_id;

Line: 780


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

Line: 832


    ota_cert_enrollment_api.update_cert_enrollment
			(p_effective_date => trunc(sysdate)
			,p_cert_enrollment_id           => p_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_cert_enrl_rec.certification_status_code
			,p_is_history_flag              => 'N'
			,p_earliest_enroll_date         => l_earliest_enroll_date
			);