API to Create Events or Classes

DECLARE

   l_effective_date          DATE := TO_DATE ('01-JAN-2014');

   l_event_id                NUMBER := NULL;

   l_activity_version_id     NUMBER := NULL;

   l_business_group_id       NUMBER := 1107;

   l_object_version_number   NUMBER := NULL;

   l_title                   VARCHAR2 (2000) := 'Microsoft Excel';

   l_offering_id             NUMBER := NULL;

   l_timezone_code           VARCHAR2 (500) := NULL;

BEGIN

   -- Get Activity Version

   BEGIN

      SELECT activity_version_id

        INTO l_activity_version_id

        FROM ota_activity_versions

       WHERE UPPER (version_name) = 'MICROSOFT OFFICE';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_activity_version_id := NULL;

   END;

   -- Get Offering ID

   BEGIN

      SELECT ofr.offering_id

        INTO l_offering_id

        FROM ota_offerings_tl otl, ota_offerings ofr

       WHERE     otl.offering_id = ofr.offering_id

             AND UPPER (otl.name) = 'E-LEARNING ARABIC'

             AND otl.language = 'US';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_offering_id := NULL;

   END;

   -- Get Time Zone

   BEGIN

      SELECT timezone_code

        INTO l_timezone_code

        FROM fnd_timezones_tl

       WHERE UPPER (name) = 'DUBAI' AND language = 'US';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_timezone_code := NULL;

   END;

   ota_event_api.

    create_class (p_effective_date             => l_effective_date,

                  p_event_id                   => l_event_id,

                  p_activity_version_id        => l_activity_version_id,

                  p_business_group_id          => l_business_group_id,

                  p_organization_id            => l_business_group_id,

                  p_event_type                 => 'SCHEDULED',

                  p_object_version_number      => l_object_version_number,

                  p_title                      => l_title,

                  p_course_start_date          => TO_DATE ('01-JAN-2014'),

                  p_course_end_date            => TO_DATE ('31-DEC-2014'),

                  p_enrolment_start_date       => TO_DATE ('01-DEC-2013'),

                  p_enrolment_end_date         => TO_DATE ('31-DEC-2014'),

                  p_book_independent_flag      => 'N',

                  p_public_event_flag          => 'Y',      -- Restricted Enrollment Flag

                  p_secure_event_flag          => 'N',          -- Secure Enrollment Flag

                  p_evt_information_category   => NULL,

                  p_evt_information1           => 0,

                  p_evt_information2           => 0,

                  p_evt_information5           => 0,

                  p_event_status               => 'N', -- "N" ->Normal or - "P"-> Planned

                  p_price_basis                => 'N',            -- No Charge

                  p_timezone                   => l_timezone_code,

                  p_parent_offering_id         => l_offering_id,

                  p_validate                   => FALSE,

                  p_event_availability         => 'ALL');

   COMMIT;

   DBMS_OUTPUT.put_line (l_event_id || ' has been created Successfully !!!!');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;