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.
PACKAGE: APPS.OTA_TRAINING_RECORD

Source


1 PACKAGE ota_training_record AUTHID CURRENT_USER AS
2 /* $Header: ottraqry.pkh 120.0.12010000.7 2009/05/05 07:45:01 dparthas noship $ */
3 /*#
4 * This is the source file to query certification and course details
5 * @rep:scope public
6 * @rep:product ota
7 * @rep:displayname OTA_TRAINING_RECORD
8 */
9 TYPE cert_description_rectype IS RECORD
10     (
11         certification_id                ota_certifications_b.certification_id%TYPE,
12         initial_completion_date         ota_certifications_b.initial_completion_date%TYPE,
13         initial_completion_duration     ota_certifications_b.initial_completion_duration%TYPE,
14         initial_compl_duration_units    ota_certifications_b.initial_compl_duration_units%TYPE,
15         renewal_duration                ota_certifications_b.renewal_duration%TYPE,
16         renewal_duration_units          ota_certifications_b.renewal_duration_units%TYPE,
17         notify_days_before_expire       ota_certifications_b.notify_days_before_expire%TYPE,
18         validity_duration               ota_certifications_b.validity_duration%TYPE,
19         validity_duration_units         ota_certifications_b.validity_duration_units%TYPE,
20         renewable_flag_code             ota_certifications_b.renewable_flag%TYPE,
21         renewable_flag_meaning          hr_lookups.meaning%TYPE,
22         start_date_active               ota_certifications_b.start_date_active%TYPE,
23         end_date_active                 ota_certifications_b.end_date_active%TYPE,
24         name                            ota_certifications_tl.name%TYPE,
25         description                     ota_certifications_tl.description%TYPE,
26         objectives                      ota_certifications_tl.objectives%TYPE,
27         purpose                         ota_certifications_tl.purpose%TYPE,
28         keywords                        ota_certifications_tl.keywords%TYPE,
29         initial_period_comments         ota_certifications_tl.initial_period_comments%TYPE,
30         renewal_period_comments         ota_certifications_tl.renewal_period_comments%TYPE,
31         certification_status_code       ota_cert_enrollments.certification_status_code%TYPE,
32         cert_status_meaning             hr_lookups.meaning%TYPE,
33         period_status_code              ota_cert_prd_enrollments.period_status_code%TYPE,
34         period_status_meaning           hr_lookups.meaning%TYPE,
35         expiration_date                 ota_cert_enrollments.expiration_date%TYPE,
36         earliest_enroll_date            ota_cert_enrollments.earliest_enroll_date%TYPE,
37         cert_period_start_date          ota_cert_prd_enrollments.cert_period_start_date%TYPE,
38         cert_period_end_date            ota_cert_prd_enrollments.cert_period_end_date%TYPE,
39         cert_enrollment_id              ota_cert_enrollments.cert_enrollment_id%TYPE,
40         cert_prd_enrollment_id          ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
41         cre_completion_date             ota_cert_enrollments.completion_date%TYPE
42     );
43 
44 TYPE cert_comp_rectype IS RECORD(
45         competence_id               per_competence_elements.competence_id%TYPE,
46         competence_name             per_competences_tl.name%TYPE,
47         proficiency_level_id        per_competence_elements.proficiency_level_id%TYPE,
48         proficiency_level_name      varchar2(100),
49         effective_date_from         per_competence_elements.effective_date_from%TYPE,
50         effective_date_to           per_competence_elements.effective_date_to%TYPE,
51         object_id                   per_competence_elements.object_id%TYPE,
52         business_group_id           per_competence_elements.business_group_id%TYPE
53     );
54 
55 TYPE cert_component_rectype IS RECORD
56     (
57         cert_mbr_enrollment_id      ota_cert_mbr_enrollments.cert_mbr_enrollment_id%TYPE,
58         activity_version_id         ota_activity_versions_vl.activity_version_id%TYPE,
59         member_status_code          ota_cert_mbr_enrollments.member_status_code%TYPE,
60         course_name                 ota_activity_versions_vl.version_name%TYPE,
61         completion_date             ota_cert_mbr_enrollments.completion_date%TYPE,
62         member_status_meaning       hr_lookups.meaning%TYPE,
63         enrollment_details_icon     varchar2(100),
64         version_code                ota_activity_versions_vl.version_code%TYPE,
65         activity_version_name       ota_activity_versions_vl.version_name%TYPE,
66         start_date                  ota_activity_versions_vl.start_date%TYPE,
67         end_date                    ota_activity_versions_vl.end_date%TYPE,
68         certification_member_id     ota_certification_members.certification_member_id%TYPE,
69         member_sequence             ota_certification_members.member_sequence%TYPE,
70         event_id                    ota_events.event_id%TYPE,
71         perf_status                 ota_booking_status_types_tl.name%TYPE,
72         cert_prd_enrollment_id      ota_cert_mbr_enrollments.cert_prd_enrollment_id%TYPE,
73         cert_enrollment_id          ota_cert_enrollments.cert_enrollment_id%TYPE,
74         certification_id            ota_cert_enrollments.certification_id%TYPE,
75         site_address                varchar2(255),
76         site_short_name             varchar2(255),
77         fnd_user_name               varchar2(255),
78         encoded_site_address        varchar2(32767),
79         classroom_id                ota_events.offering_id%TYPE
80     );
81 
82 TYPE cert_competencies_tabletype IS TABLE OF cert_comp_rectype NOT NULL ;
83 TYPE components_tabletype IS TABLE OF cert_component_rectype NOT NULL ;
84 
85 
86 TYPE certification_rectype IS RECORD
87     (
88         cert_name                   ota_certifications_tl.name%TYPE,
89         certification_id            ota_cert_enrollments.certification_id%TYPE,
90         certification_status_code   ota_cert_enrollments.certification_status_code%TYPE,
91         cert_status_meaning         varchar2(240),
92         period_status_code          ota_cert_prd_enrollments.period_status_code%TYPE,
93         period_status_meaning       hr_lookups.meaning%TYPE,
94         cert_period_start_date      ota_cert_prd_enrollments.cert_period_start_date%TYPE,
95         cert_period_end_date        ota_cert_prd_enrollments.cert_period_end_date%TYPE,
96         cre_completion_date         ota_cert_prd_enrollments.completion_date%TYPE,
97         person_id                   ota_cert_enrollments.person_id%TYPE,
98         contact_id                  ota_cert_enrollments.contact_id%TYPE,
99         cert_enrollment_id          ota_cert_enrollments.cert_enrollment_id%TYPE,
100         cert_prd_enrollment_id      ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
101         is_history_flag             ota_cert_enrollments.is_history_flag%TYPE,
102         renewable_flag              ota_certifications_b.renewable_flag%TYPE,
103         is_period_renewable         VARCHAR2(1),
104         earliest_enroll_date        ota_cert_enrollments.earliest_enroll_date%TYPE,
105         expiration_date             ota_cert_prd_enrollments.expiration_date%TYPE,
106         start_date_active           ota_certifications_b.start_date_active%TYPE,
107         end_date_active             ota_certifications_b.end_date_active%TYPE,
108         cert_enrollment_id2         ota_cert_enrollments.cert_enrollment_id%TYPE,
109         cert_description            cert_description_rectype,
110         cert_competencies           cert_competencies_tabletype,
111         cert_components             components_tabletype,
112         event_action                VARCHAR2(50)
113     );
114 
115 TYPE certification_tabletype IS TABLE OF certification_rectype NOT NULL;
116 
117 TYPE event_rectype IS RECORD
118         (
119         event_id OTA_EVENTS_V.event_id%TYPE,
120         object_version_number OTA_EVENTS_V.object_version_number%TYPE,
121         business_group_id OTA_EVENTS_V.business_group_id%TYPE,
122         title OTA_EVENTS_V.title%TYPE,
123         course_start_date OTA_EVENTS_V.course_start_date%TYPE,
124         course_start_time OTA_EVENTS_V.course_start_time%TYPE,
125         course_end_date OTA_EVENTS_V.course_end_date%TYPE,
126         course_end_time OTA_EVENTS_V.course_end_time%TYPE,
127         duration OTA_EVENTS_V.duration%TYPE,
128         duration_units OTA_EVENTS_V.duration_units%TYPE,
129         enrolment_start_date OTA_EVENTS_V.enrolment_start_date%TYPE,
130         enrolment_end_date OTA_EVENTS_V.enrolment_end_date%TYPE,
131         resource_booking_flag OTA_EVENTS_V.resource_booking_flag%TYPE,
132         public_event_flag OTA_EVENTS_V.public_event_flag%TYPE,
133         minimum_attendees OTA_EVENTS_V.minimum_attendees%TYPE,
134         maximum_attendees OTA_EVENTS_V.maximum_attendees%TYPE,
135         maximum_internal_attendees OTA_EVENTS_V.maximum_internal_attendees%TYPE,
136         standard_price OTA_EVENTS_V.standard_price%TYPE,
137         parent_event_id OTA_EVENTS_V.parent_event_id%TYPE,
138         book_independent_flag OTA_EVENTS_V.book_independent_flag%TYPE,
139         actual_cost OTA_EVENTS_V.actual_cost%TYPE,
140         budget_cost OTA_EVENTS_V.budget_cost%TYPE,
141         budget_currency_code OTA_EVENTS_V.budget_currency_code%TYPE,
142         created_by OTA_EVENTS_V.created_by%TYPE,
143         creation_date OTA_EVENTS_V.creation_date%TYPE,
144         last_updated_by OTA_EVENTS_V.last_updated_by%TYPE,
145         last_update_login OTA_EVENTS_V.last_update_login%TYPE,
146         last_update_date OTA_EVENTS_V.last_update_date%TYPE,
147         comments OTA_EVENTS_V.comments%TYPE,
148         evt_information_category OTA_EVENTS_V.evt_information_category%TYPE,
149         evt_information1 OTA_EVENTS_V.evt_information1%TYPE,
150         evt_information2 OTA_EVENTS_V.evt_information2%TYPE,
151         evt_information3 OTA_EVENTS_V.evt_information3%TYPE,
152         evt_information4 OTA_EVENTS_V.evt_information4%TYPE,
153         evt_information5 OTA_EVENTS_V.evt_information5%TYPE,
154         evt_information6 OTA_EVENTS_V.evt_information6%TYPE,
155         evt_information7 OTA_EVENTS_V.evt_information7%TYPE,
156         evt_information8 OTA_EVENTS_V.evt_information8%TYPE,
157         evt_information9 OTA_EVENTS_V.evt_information9%TYPE,
158         evt_information10 OTA_EVENTS_V.evt_information10%TYPE,
159         evt_information11 OTA_EVENTS_V.evt_information11%TYPE,
160         evt_information12 OTA_EVENTS_V.evt_information12%TYPE,
161         evt_information13 OTA_EVENTS_V.evt_information13%TYPE,
162         evt_information14 OTA_EVENTS_V.evt_information14%TYPE,
163         evt_information15 OTA_EVENTS_V.evt_information15%TYPE,
164         evt_information16 OTA_EVENTS_V.evt_information16%TYPE,
165         evt_information17 OTA_EVENTS_V.evt_information17%TYPE,
166         evt_information18 OTA_EVENTS_V.evt_information18%TYPE,
167         evt_information19 OTA_EVENTS_V.evt_information19%TYPE,
168         evt_information20 OTA_EVENTS_V.evt_information20%TYPE,
169         secure_event_flag OTA_EVENTS_V.secure_event_flag%TYPE,
170         organization_id OTA_EVENTS_V.organization_id%TYPE,
171         organization_name OTA_EVENTS_V.organization_name%TYPE,
172         centre OTA_EVENTS_V.centre%TYPE,
173         centre_meaning OTA_EVENTS_V.centre_meaning%TYPE,
174         currency_code OTA_EVENTS_V.currency_code%TYPE,
175         development_event_type OTA_EVENTS_V.development_event_type%TYPE,
176         development_event_type_meaning OTA_EVENTS_V.development_event_type_meaning%TYPE,
177         language_code OTA_EVENTS_V.language_code%TYPE,
178         language_description OTA_EVENTS_V.language_description%TYPE,
179         price_basis OTA_EVENTS_V.price_basis%TYPE,
180         programme_code OTA_EVENTS_V.programme_code%TYPE,
181         programme_code_meaning OTA_EVENTS_V.programme_code_meaning%TYPE,
182         event_status OTA_EVENTS_V.event_status%TYPE,
183         event_status_meaning OTA_EVENTS_V.event_status_meaning%TYPE,
184         activity_name OTA_EVENTS_V.activity_name%TYPE,
185         activity_version_id OTA_EVENTS_V.activity_version_id%TYPE,
186         activity_version_name OTA_EVENTS_V.activity_version_name%TYPE,
187         event_type OTA_EVENTS_V.event_type%TYPE,
188         event_type_meaning OTA_EVENTS_V.event_type_meaning%TYPE,
189         invoiced_amount OTA_EVENTS_V.invoiced_amount%TYPE,
190         user_status OTA_EVENTS_V.user_status%TYPE,
191         user_status_meaning OTA_EVENTS_V.user_status_meaning%TYPE,
192         vendor_id OTA_EVENTS_V.vendor_id%TYPE,
193         vendor_name OTA_EVENTS_V.vendor_name%TYPE,
194         project_id OTA_EVENTS_V.project_id%TYPE,
195         project_name OTA_EVENTS_V.project_name%TYPE,
196         project_number OTA_EVENTS_V.project_number%TYPE,
197         line_id OTA_EVENTS_V.line_id%TYPE,
198         org_id OTA_EVENTS_V.org_id%TYPE,
199         owner_id OTA_EVENTS_V.owner_id%TYPE,
200         training_center_id OTA_EVENTS_V.training_center_id%TYPE,
201         location_id OTA_EVENTS_V.location_id%TYPE,
202         offering_id OTA_EVENTS_V.offering_id%TYPE,
203         timezone OTA_EVENTS_V.timezone%TYPE,
204         inventory_item_id OTA_EVENTS_V.inventory_item_id%TYPE,
205         parent_offering_id OTA_EVENTS_V.parent_offering_id%TYPE,
206         data_source OTA_EVENTS_V.data_source%TYPE
207         );
208 
209 TYPE event_tabletype IS TABLE OF event_rectype NOT NULL;
210 
211 TYPE booking_rectype IS RECORD
212     (
213     delegate_person_id            ota_delegate_bookings.delegate_person_id%TYPE,
214     status                        ota_booking_status_types_tl.name%TYPE,
215     player_status                 hr_lookups.meaning%TYPE,
216     booking_id                    ota_delegate_bookings.booking_id%TYPE,
217     is_history_flag               ota_delegate_bookings.is_history_flag%TYPE,
218     date_status_changed           ota_delegate_bookings.date_status_changed%TYPE,
219     successful_attendance_flag    ota_delegate_bookings.successful_attendance_flag%TYPE,
220     is_mandatory_enrollment       ota_delegate_bookings.is_mandatory_enrollment%TYPE
221     );
222 TYPE activity_rectype IS RECORD
223     (
224         activity_version_id         ota_activity_versions_vl.activity_version_id%TYPE,
225         activity_version_name       ota_activity_versions_vl.version_name%TYPE,
226         activity_description        ota_activity_versions_vl.description%TYPE,
227         activity_objectives         ota_activity_versions_vl.objectives%TYPE,
228         activity_audience           ota_activity_versions_vl.intended_audience%TYPE,
229         activity_keywords           ota_activity_versions_vl.keywords%TYPE,
230         tav_information_category    ota_activity_versions_vl.tav_information_category%TYPE,
231         tav_information1            ota_activity_versions_vl.tav_information1%TYPE,
232         tav_information2            ota_activity_versions_vl.tav_information2%TYPE,
233         tav_information3            ota_activity_versions_vl.tav_information3%TYPE,
234         tav_information4            ota_activity_versions_vl.tav_information4%TYPE,
235         tav_information5            ota_activity_versions_vl.tav_information5%TYPE,
236         tav_information6            ota_activity_versions_vl.tav_information6%TYPE,
237         tav_information7            ota_activity_versions_vl.tav_information7%TYPE,
238         tav_information8            ota_activity_versions_vl.tav_information8%TYPE,
239         tav_information9            ota_activity_versions_vl.tav_information9%TYPE,
240         tav_information10           ota_activity_versions_vl.tav_information10%TYPE,
241         tav_information11           ota_activity_versions_vl.tav_information11%TYPE,
242         tav_information12           ota_activity_versions_vl.tav_information12%TYPE,
243         tav_information13           ota_activity_versions_vl.tav_information13%TYPE,
244         tav_information14           ota_activity_versions_vl.tav_information14%TYPE,
245         tav_information15           ota_activity_versions_vl.tav_information15%TYPE,
246         tav_information16           ota_activity_versions_vl.tav_information16%TYPE,
247         tav_information17           ota_activity_versions_vl.tav_information17%TYPE,
248         tav_information18           ota_activity_versions_vl.tav_information18%TYPE,
249         tav_information19           ota_activity_versions_vl.tav_information19%TYPE,
250         tav_information20           ota_activity_versions_vl.tav_information20%TYPE,
251         activity_version_code       ota_activity_versions_vl.version_code%TYPE,
252         success_criteria            hr_lookups.meaning%TYPE,
253         professional_credits        ota_activity_versions_vl.professional_credits%TYPE,
254         professional_credit_meaning hr_lookups.meaning%TYPE,
255         controlling_person_id       ota_activity_versions.controlling_person_id%type,
256         events                      event_tabletype,
257         booking                     booking_rectype,
258         event_action                VARCHAR2(50)
259     );
260 
261 TYPE activity_tabletype IS TABLE OF activity_rectype NOT NULL;
262 
263 TYPE query_options IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
264 
265 TYPE cert_query_input_rectype IS RECORD
266     (
267         person_id                   ota_cert_enrollments.person_id%TYPE            DEFAULT NULL,
268         start_person_id             ota_cert_enrollments.person_id%TYPE            DEFAULT NULL,
269         end_person_id               ota_cert_enrollments.person_id%TYPE            DEFAULT NULL,
270         certification_id            ota_certifications_b.certification_id%TYPE     DEFAULT NULL,
271         cert_enrollment_id          ota_cert_enrollments.cert_enrollment_id%TYPE   DEFAULT NULL,
272         view_history                BOOLEAN                                        DEFAULT FALSE,
273         options                     query_options
274     );
275 
276 TYPE course_query_input_rectype IS RECORD
277     (
278         person_id                   ota_delegate_bookings.delegate_person_id%TYPE     DEFAULT NULL,
279         start_person_id             ota_cert_enrollments.person_id%TYPE               DEFAULT NULL,
280         end_person_id               ota_cert_enrollments.person_id%TYPE               DEFAULT NULL,
281         course_id                   ota_activity_versions.activity_id%TYPE            DEFAULT NULL,
282         delegate_booking_id         ota_delegate_bookings.booking_id%TYPE             DEFAULT NULL,
283         view_history                BOOLEAN                                           DEFAULT FALSE,
284         options                     query_options
285     );
286 /*#
287 * This is procedure for querying certificate details.
288 * @rep:displayname Get Certification Details
289 * @rep:category BUSINESS_ENTITY OTA_CERTIFICATION
290 * @rep:scope public
291 * @rep:lifecycle active
292 */
293 PROCEDURE get_certification_details(  p_query_options               IN   cert_query_input_rectype,
294                                       p_certifications              OUT NOCOPY certification_tabletype);
295 /*#
296 * This is procedure for querying course details.
297 * @rep:displayname Get Training Details
298 * @rep:category BUSINESS_ENTITY OTA_CERTIFICATION
299 * @rep:scope public
300 * @rep:lifecycle active
301 */
302 PROCEDURE get_training_details(p_query_options               IN   course_query_input_rectype,
303                                p_training                    OUT NOCOPY activity_tabletype,
304                                p_certifications              OUT NOCOPY certification_tabletype,
305                                p_ispartofcertification       OUT NOCOPY BOOLEAN);
306 
307 invalid_cert_enrollment_id  EXCEPTION;
308 invalid_delegate_booking_id EXCEPTION;
309 invalid_course_id           EXCEPTION;
310 invalid_person_id           EXCEPTION;
311 invalid_certification_id    EXCEPTION;
312 END ota_training_record;