68: ,p_tdb_information19 in varchar2
69: ,p_tdb_information20 in varchar2 )
70: IS
71:
72: CURSOR bg_to (pp_event_id ota_events.event_id%TYPE) IS
73: SELECT hao.business_group_id,
74: evt.organization_id,
75: evt.currency_code,
76: evt.course_start_date,
78: evt.Title,
79: evt.owner_id,
80: evt.activity_version_id,
81: evt.offering_id
82: FROM OTA_EVENTS_VL evt,
83: HR_ALL_ORGANIZATION_UNITS hao
84: WHERE evt.event_id = pp_event_id
85: AND evt.organization_id = hao.organization_id (+); -- Bug 2213009
86:
86:
87:
88: Cursor Get_Event_status is
89: Select event_status, maximum_internal_attendees
90: from OTA_EVENTS
91: WHERE EVENT_ID = TO_NUMBER(p_event_id);
92:
93: CURSOR get_existing_internal IS
94: SELECT count(*)
101:
102:
103: CURSOR c_get_price_basis is
104: SELECT nvl(price_basis,NULL)
105: FROM ota_events
106: where event_id = p_event_id;
107:
108: CURSOR csr_user(p_owner_id in number) IS
109: SELECT
119: FROM OTA_ACTIVITY_VERSIONS_TL
120: WHERE activity_version_id = p_activity_version_id
121: AND language=userenv('LANG');
122:
123: l_price_basis OTA_EVENTS.price_basis%TYPE;
124:
125: l_person_details OTA_TRNG_ENROLL_SS.csr_person_to_enroll_details%ROWTYPE;
126: --
127: l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
165: -- l_item_key wf_items.item_key%type;
166: l_called_from varchar2(80);
167: l_business_group_id_to hr_all_organization_units.organization_id%type;
168: l_sponsor_organization_id hr_all_organization_units.organization_id%type;
169: l_event_currency_code ota_events.currency_code%type;
170: l_event_title ota_events.title%type;
171: l_course_start_date ota_events.course_start_date%type;
172: l_course_end_date ota_events.course_end_date%type;
173: l_owner_id ota_events.owner_id%type;
166: l_called_from varchar2(80);
167: l_business_group_id_to hr_all_organization_units.organization_id%type;
168: l_sponsor_organization_id hr_all_organization_units.organization_id%type;
169: l_event_currency_code ota_events.currency_code%type;
170: l_event_title ota_events.title%type;
171: l_course_start_date ota_events.course_start_date%type;
172: l_course_end_date ota_events.course_end_date%type;
173: l_owner_id ota_events.owner_id%type;
174: l_activity_version_id ota_activity_versions.activity_version_id%type;
167: l_business_group_id_to hr_all_organization_units.organization_id%type;
168: l_sponsor_organization_id hr_all_organization_units.organization_id%type;
169: l_event_currency_code ota_events.currency_code%type;
170: l_event_title ota_events.title%type;
171: l_course_start_date ota_events.course_start_date%type;
172: l_course_end_date ota_events.course_end_date%type;
173: l_owner_id ota_events.owner_id%type;
174: l_activity_version_id ota_activity_versions.activity_version_id%type;
175: l_version_name ota_activity_versions.version_name%type;
168: l_sponsor_organization_id hr_all_organization_units.organization_id%type;
169: l_event_currency_code ota_events.currency_code%type;
170: l_event_title ota_events.title%type;
171: l_course_start_date ota_events.course_start_date%type;
172: l_course_end_date ota_events.course_end_date%type;
173: l_owner_id ota_events.owner_id%type;
174: l_activity_version_id ota_activity_versions.activity_version_id%type;
175: l_version_name ota_activity_versions.version_name%type;
176: l_owner_username fnd_user.user_name%type;
169: l_event_currency_code ota_events.currency_code%type;
170: l_event_title ota_events.title%type;
171: l_course_start_date ota_events.course_start_date%type;
172: l_course_end_date ota_events.course_end_date%type;
173: l_owner_id ota_events.owner_id%type;
174: l_activity_version_id ota_activity_versions.activity_version_id%type;
175: l_version_name ota_activity_versions.version_name%type;
176: l_owner_username fnd_user.user_name%type;
177: --Bug#2197997 commenting out completeactivity call.
175: l_version_name ota_activity_versions.version_name%type;
176: l_owner_username fnd_user.user_name%type;
177: --Bug#2197997 commenting out completeactivity call.
178: -- l_return boolean;
179: l_offering_id ota_events.offering_id%type;
180: l_booking_status_used varchar2(20);
181:
182: -- l_approval_req_flag Varchar2(20);
183:
1544: --|--------------------------------------------------------------------------|
1545:
1546: FUNCTION CHK_FOR_SECURE_EVT (
1547: p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
1548: , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE)
1549: RETURN VARCHAR2
1550: IS
1551:
1552: CURSOR C_GET_SECURE_FLAG is
1550: IS
1551:
1552: CURSOR C_GET_SECURE_FLAG is
1553: SELECT secure_event_flag,organization_id
1554: from ota_events
1555: where event_id = p_event_id;
1556:
1557: CURSOR C_GET_ORG_ID is
1558: SELECT organization_id
1562: effective_end_date;
1563:
1564:
1565: l_secure_flag varchar2(1);
1566: l_evt_organization_id OTA_EVENTS.ORGANIZATION_ID%TYPE;
1567: l_per_organization_id PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE;
1568: l_return_value varchar2(2000);
1569:
1570: BEGIN
1609: --|--------------------------------------------------------------------------|
1610:
1611: FUNCTION CHK_DELEGATE_OK_FOR_EVENT (
1612: p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
1613: , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
1614: , p_event_start_date IN OTA_EVENTS.COURSE_START_DATE%TYPE
1615: )
1616: RETURN VARCHAR2
1617:
1610:
1611: FUNCTION CHK_DELEGATE_OK_FOR_EVENT (
1612: p_delegate_id IN PER_PEOPLE_F.PERSON_ID%TYPE
1613: , p_event_id IN OTA_EVENTS.EVENT_ID%TYPE
1614: , p_event_start_date IN OTA_EVENTS.COURSE_START_DATE%TYPE
1615: )
1616: RETURN VARCHAR2
1617:
1618: IS
1624:
1625: CURSOR csr_event_start_date IS
1626: SELECT course_start_date
1627: -- FROM otv_scheduled_events
1628: FROM ota_events
1629: WHERE event_id = p_event_id and
1630: --Bug#2201434 SELFPACED event_type included.
1631: -- event_type='SCHEDULED' and
1632: event_type in ('SCHEDULED','SELFPACED') and
1888: funcmode IN VARCHAR2,
1889: resultout OUT nocopy VARCHAR2 )
1890: IS
1891:
1892: CURSOR user_name(p_event_id OTA_EVENTS.event_id%TYPE) IS
1893: SELECT usr.user_name,
1894: evt.offering_id
1895: FROM OTA_EVENTS evt,
1896: FND_USER USR
1891:
1892: CURSOR user_name(p_event_id OTA_EVENTS.event_id%TYPE) IS
1893: SELECT usr.user_name,
1894: evt.offering_id
1895: FROM OTA_EVENTS evt,
1896: FND_USER USR
1897: WHERE evt.event_id = p_event_id and
1898: usr.employee_id = evt.owner_id;
1899:
1910: l_api_from VARCHAR2(4000);
1911: l_api_to VARCHAR2(4000);
1912: l_event_id NUMBER;
1913: l_user_name FND_USER.USER_NAME%TYPE;
1914: l_offering_id ota_events.offering_id%type;
1915: l_booking_id ota_delegate_bookings.booking_id%type;
1916: l_version_name ota_activity_versions.version_name%type;
1917: l_notification_text varchar2(2000);
1918: l_status_type ota_booking_status_types.type%type;
2049: --|--< CHK_FOR_RESTRICTED_EVT >-------------------------------------------|
2050: --|--------------------------------------------------------------------------|
2051:
2052: FUNCTION CHK_FOR_RESTRICTED_EVT (
2053: p_event_id IN OTA_EVENTS.EVENT_ID%TYPE)
2054: RETURN VARCHAR2
2055: IS
2056:
2057: CURSOR C_GET_EVT_DETAILS is
2055: IS
2056:
2057: CURSOR C_GET_EVT_DETAILS is
2058: SELECT EVT.public_event_flag,EVT.maximum_internal_attendees
2059: from ota_events EVT, ota_event_associations EVA
2060: where EVT.event_id = p_event_id and
2061: EVT.event_id = EVA.event_id and
2062: EVA.customer_id is not null;
2063:
2060: where EVT.event_id = p_event_id and
2061: EVT.event_id = EVA.event_id and
2062: EVA.customer_id is not null;
2063:
2064: l_public_event_flag OTA_EVENTS.PUBLIC_EVENT_FLAG%TYPE;
2065: l_maximum_internal_attendees OTA_EVENTS.MAXIMUM_INTERNAL_ATTENDEES%TYPE;
2066: l_return_value varchar2(10);
2067:
2068: BEGIN
2061: EVT.event_id = EVA.event_id and
2062: EVA.customer_id is not null;
2063:
2064: l_public_event_flag OTA_EVENTS.PUBLIC_EVENT_FLAG%TYPE;
2065: l_maximum_internal_attendees OTA_EVENTS.MAXIMUM_INTERNAL_ATTENDEES%TYPE;
2066: l_return_value varchar2(10);
2067:
2068: BEGIN
2069: