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_TRNG_PLAN_UTIL_SS dependencies on OTA_TRAINING_PLANS

Line 14: Function chk_cancel_plan_ok(p_training_plan_id in ota_training_plans.training_plan_id%type)

10: -- ---------------------------------------------------------------------------
11: -- |----------------------< chk_cancel_plan_ok >--------------------------|
12: -- ---------------------------------------------------------------------------
13: --
14: Function chk_cancel_plan_ok(p_training_plan_id in ota_training_plans.training_plan_id%type)
15: return varchar2
16: is
17:
18: Cursor any_child is

Line 48: Function chk_complete_plan_ok(p_training_plan_id in ota_training_plans.training_plan_id%type)

44: -- |----------------------< chk_complete_plan_ok >--------------------------|
45: -- ---------------------------------------------------------------------------
46: --
47:
48: Function chk_complete_plan_ok(p_training_plan_id in ota_training_plans.training_plan_id%type)
49: return varchar2
50: IS
51:
52: Cursor any_child is

Line 95: FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,

91: -- ---------------------------------------------------------------------------
92: -- |----------------------< get_enroll_status >--------------------------|
93: -- ---------------------------------------------------------------------------
94: --
95: FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,
96: p_contact_id IN ota_training_plans.contact_id%TYPE,
97: p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE)
98: RETURN VARCHAR2 IS
99:

Line 96: p_contact_id IN ota_training_plans.contact_id%TYPE,

92: -- |----------------------< get_enroll_status >--------------------------|
93: -- ---------------------------------------------------------------------------
94: --
95: FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,
96: p_contact_id IN ota_training_plans.contact_id%TYPE,
97: p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE)
98: RETURN VARCHAR2 IS
99:
100:

Line 107: ota_training_plans tps

103: FROM ota_training_plan_members tpm,
104: ota_events evt,
105: ota_delegate_bookings tdb,
106: ota_booking_status_types bst,
107: ota_training_plans tps
108: WHERE tpm.activity_version_id=evt.activity_version_id
109: AND evt.event_id = tdb.event_id
110: AND bst.booking_status_type_id = tdb.booking_status_type_id
111: -- and bst.active_flag='Y'

Line 157: FUNCTION chk_login_person(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)

153: -- ---------------------------------------------------------------------------
154: -- |----------------------< chk_login_person >--------------------------|
155: -- ---------------------------------------------------------------------------
156: --
157: FUNCTION chk_login_person(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
158: RETURN VARCHAR2
159: IS
160:
161: l_person_id ota_training_plans.person_id%TYPE;

Line 161: l_person_id ota_training_plans.person_id%TYPE;

157: FUNCTION chk_login_person(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
158: RETURN VARCHAR2
159: IS
160:
161: l_person_id ota_training_plans.person_id%TYPE;
162: l_login_person ota_training_plans.person_id%TYPE;
163: l_login_customer NUMBER;
164: l_contact_id ota_training_plans.contact_id%TYPE;
165: l_proc VARCHAR2(72) := g_package|| 'chk_login_person';

Line 162: l_login_person ota_training_plans.person_id%TYPE;

158: RETURN VARCHAR2
159: IS
160:
161: l_person_id ota_training_plans.person_id%TYPE;
162: l_login_person ota_training_plans.person_id%TYPE;
163: l_login_customer NUMBER;
164: l_contact_id ota_training_plans.contact_id%TYPE;
165: l_proc VARCHAR2(72) := g_package|| 'chk_login_person';
166:

Line 164: l_contact_id ota_training_plans.contact_id%TYPE;

160:
161: l_person_id ota_training_plans.person_id%TYPE;
162: l_login_person ota_training_plans.person_id%TYPE;
163: l_login_customer NUMBER;
164: l_contact_id ota_training_plans.contact_id%TYPE;
165: l_proc VARCHAR2(72) := g_package|| 'chk_login_person';
166:
167: BEGIN
168:

Line 174: FROM ota_training_plans tps

170:
171: -- Modified for Bug#3479186
172: SELECT tps.person_id,tps.contact_id
173: INTO l_person_id, l_contact_id
174: FROM ota_training_plans tps
175: WHERE tps.training_plan_id = p_training_plan_id;
176:
177: SELECT employee_id, customer_id
178: INTO l_login_person, l_login_customer

Line 204: FUNCTION chk_src_func_tlntmgt(p_person_id IN ota_training_plans.person_id%TYPE

200: -- |----------------------< chk_src_func_TLNTMGT >--------------------------|
201: -- ---------------------------------------------------------------------------
202: --
203:
204: FUNCTION chk_src_func_tlntmgt(p_person_id IN ota_training_plans.person_id%TYPE
205: -- ,p_source_function in ota_training_plan_members.source_function%type
206: ,p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE
207: ,p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE
208: -- Added for Bug#3108246

Line 216: ota_training_plans tps

212:
213: CURSOR csr_get_TP IS
214: SELECT tps.Training_plan_id
215: FROM
216: ota_training_plans tps
217: WHERE
218: p_earliest_start_date >= tps.start_date
219: AND (tps.end_date IS NOT NULL AND p_target_completion_date <= tps.end_date)
220: AND tps.plan_source = 'TALENT_MGMT'

Line 294: FUNCTION chk_enrollment_exist ( p_person_id IN ota_training_plans.person_id%TYPE,

290: -- ---------------------------------------------------------------------------
291: -- |----------------------< chk_enrollment_exist >--------------------------|
292: -- ---------------------------------------------------------------------------
293:
294: FUNCTION chk_enrollment_exist ( p_person_id IN ota_training_plans.person_id%TYPE,
295: p_contact_id IN ota_training_plans.contact_id%TYPE,
296: p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE)
297: RETURN boolean
298: IS

Line 295: p_contact_id IN ota_training_plans.contact_id%TYPE,

291: -- |----------------------< chk_enrollment_exist >--------------------------|
292: -- ---------------------------------------------------------------------------
293:
294: FUNCTION chk_enrollment_exist ( p_person_id IN ota_training_plans.person_id%TYPE,
295: p_contact_id IN ota_training_plans.contact_id%TYPE,
296: p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE)
297: RETURN boolean
298: IS
299: CURSOR chk_enr IS

Line 354: FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,

350: -- DECODE(bst.type,'C','Z',bst.type): Decode is to get the Cancelled enrollments
351: -- in the end because the attended overrules all others and 'P', 'R', 'W'
352: -- overrules 'C'.
353:
354: FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,
355: p_contact_id IN ota_training_plans.contact_id%TYPE,
356: p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
357: p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
358: p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE,

Line 355: p_contact_id IN ota_training_plans.contact_id%TYPE,

351: -- in the end because the attended overrules all others and 'P', 'R', 'W'
352: -- overrules 'C'.
353:
354: FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,
355: p_contact_id IN ota_training_plans.contact_id%TYPE,
356: p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
357: p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
358: p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE,
359: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,

Line 359: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,

355: p_contact_id IN ota_training_plans.contact_id%TYPE,
356: p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
357: p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
358: p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE,
359: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
360: p_action IN VARCHAR2)
361: RETURN VARCHAR2 IS
362:
363:

Line 408: FROM ota_training_plans tps

404: ORDER BY status;
405:
406: CURSOR csr_plan_source IS
407: SELECT tps.plan_source
408: FROM ota_training_plans tps
409: where tps.training_plan_id = p_training_plan_id;
410:
411: l_proc VARCHAR2(72) := g_package|| 'get_enroll_status';
412:

Line 452: PROCEDURE modify_tpc_status_on_create(--p_person_id IN ota_training_plans.person_id%TYPE,

448: -- |----------------------< modify_tpc_status_on_create >--------------------------|
449: -- ---------------------------------------------------------------------------
450: --
451:
452: PROCEDURE modify_tpc_status_on_create(--p_person_id IN ota_training_plans.person_id%TYPE,
453: -- p_contact_id IN ota_training_plans.contact_id%TYPE,
454: p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
455: p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
456: p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,

Line 453: -- p_contact_id IN ota_training_plans.contact_id%TYPE,

449: -- ---------------------------------------------------------------------------
450: --
451:
452: PROCEDURE modify_tpc_status_on_create(--p_person_id IN ota_training_plans.person_id%TYPE,
453: -- p_contact_id IN ota_training_plans.contact_id%TYPE,
454: p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
455: p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
456: p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
457: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,

Line 457: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,

453: -- p_contact_id IN ota_training_plans.contact_id%TYPE,
454: p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
455: p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
456: p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
457: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
458: p_member_status_id OUT nocopy VARCHAR2)
459: IS
460:
461: l_proc VARCHAR2(72) := g_package|| 'modify_tpc_status_on_create';

Line 463: l_person_id OTA_TRAINING_PLANS.PERSON_ID%TYPE;

459: IS
460:
461: l_proc VARCHAR2(72) := g_package|| 'modify_tpc_status_on_create';
462: l_enroll_status VARCHAR2(30);
463: l_person_id OTA_TRAINING_PLANS.PERSON_ID%TYPE;
464: l_contact_id OTA_TRAINING_PLANS.CONTACT_ID%TYPE;
465:
466: BEGIN
467:

Line 464: l_contact_id OTA_TRAINING_PLANS.CONTACT_ID%TYPE;

460:
461: l_proc VARCHAR2(72) := g_package|| 'modify_tpc_status_on_create';
462: l_enroll_status VARCHAR2(30);
463: l_person_id OTA_TRAINING_PLANS.PERSON_ID%TYPE;
464: l_contact_id OTA_TRAINING_PLANS.CONTACT_ID%TYPE;
465:
466: BEGIN
467:
468: hr_utility.set_location('Entering:'|| l_proc, 10);

Line 473: FROM ota_training_plans tp

469:
470: -- Modified for Bug#3479186
471: SELECT tp.person_id, tp.contact_id
472: INTO l_person_id , l_contact_id
473: FROM ota_training_plans tp
474: where tp.training_plan_id = p_training_plan_id;
475:
476:
477: l_enroll_status := get_enroll_status(p_person_id => l_person_id,

Line 516: PROCEDURE modify_tpc_status_on_update(--p_person_id IN ota_training_plans.person_id%TYPE,

512: -- |----------------------< modify_tpc_status_on_update >--------------------------|
513: -- ---------------------------------------------------------------------------
514: --
515:
516: PROCEDURE modify_tpc_status_on_update(--p_person_id IN ota_training_plans.person_id%TYPE,
517: p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
518: p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
519: p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
520: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,

Line 520: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,

516: PROCEDURE modify_tpc_status_on_update(--p_person_id IN ota_training_plans.person_id%TYPE,
517: p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
518: p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
519: p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
520: p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
521: p_member_status_id OUT nocopy VARCHAR2)
522: IS
523:
524: l_proc VARCHAR2(72) := g_package|| 'modify_tpc_status_on_update';

Line 526: l_person_id OTA_TRAINING_PLANS.PERSON_ID%TYPE;

522: IS
523:
524: l_proc VARCHAR2(72) := g_package|| 'modify_tpc_status_on_update';
525: l_enroll_status VARCHAR2(30);
526: l_person_id OTA_TRAINING_PLANS.PERSON_ID%TYPE;
527: l_contact_id OTA_TRAINING_PLANS.CONTACT_ID%TYPE;
528:
529: BEGIN
530:

Line 527: l_contact_id OTA_TRAINING_PLANS.CONTACT_ID%TYPE;

523:
524: l_proc VARCHAR2(72) := g_package|| 'modify_tpc_status_on_update';
525: l_enroll_status VARCHAR2(30);
526: l_person_id OTA_TRAINING_PLANS.PERSON_ID%TYPE;
527: l_contact_id OTA_TRAINING_PLANS.CONTACT_ID%TYPE;
528:
529: BEGIN
530:
531: hr_utility.set_location('Entering:'|| l_proc, 10);

Line 536: FROM ota_training_plans tp

532:
533: -- Modified for Bug#3479186
534: SELECT tp.person_id, tp.contact_id
535: INTO l_person_id , l_contact_id
536: FROM ota_training_plans tp
537: where tp.training_plan_id = p_training_plan_id;
538:
539:
540: l_enroll_status := get_enroll_status(p_person_id => l_person_id,

Line 578: FUNCTION get_person_id(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)

574: -- ----------------------------------------------------------------------------
575: -- |---------------------------< get_person_id >----------------------------|
576: -- ----------------------------------------------------------------------------
577:
578: FUNCTION get_person_id(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
579: RETURN number
580: IS
581:
582: CURSOR csr_person_id IS

Line 584: FROM ota_training_plans

580: IS
581:
582: CURSOR csr_person_id IS
583: SELECT person_id
584: FROM ota_training_plans
585: WHERE training_plan_id = p_training_plan_id;
586:
587: l_person_id number(9) := 0;
588:

Line 606: PROCEDURE get_valid_enroll (p_person_id IN ota_training_plans.person_id%TYPE

602:
603: -- ----------------------------------------------------------------------------
604: -- |---------------------------< get_valid_enroll >-------------------------|
605: -- ----------------------------------------------------------------------------
606: PROCEDURE get_valid_enroll (p_person_id IN ota_training_plans.person_id%TYPE
607: , p_contact_id IN ota_training_plans.contact_id%TYPE
608: ,p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
609: ,p_return_status OUT nocopy VARCHAR2)
610: IS

Line 607: , p_contact_id IN ota_training_plans.contact_id%TYPE

603: -- ----------------------------------------------------------------------------
604: -- |---------------------------< get_valid_enroll >-------------------------|
605: -- ----------------------------------------------------------------------------
606: PROCEDURE get_valid_enroll (p_person_id IN ota_training_plans.person_id%TYPE
607: , p_contact_id IN ota_training_plans.contact_id%TYPE
608: ,p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
609: ,p_return_status OUT nocopy VARCHAR2)
610: IS
611: l_evt_type VARCHAR2(30);

Line 651: FUNCTION is_personal_trng_plan(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)

647: -- ---------------------------------------------------------------------------
648: -- |----------------------< is_personal_trng_plan >--------------------------|
649: -- ---------------------------------------------------------------------------
650: --
651: FUNCTION is_personal_trng_plan(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
652: RETURN BOOLEAN
653: IS
654:
655: l_person_id ota_training_plans.person_id%TYPE;

Line 655: l_person_id ota_training_plans.person_id%TYPE;

651: FUNCTION is_personal_trng_plan(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
652: RETURN BOOLEAN
653: IS
654:
655: l_person_id ota_training_plans.person_id%TYPE;
656: l_contact_id ota_training_plans.contact_id%TYPE;
657: l_proc VARCHAR2(72) := g_package|| 'is_personal_trng_plan';
658:
659: BEGIN

Line 656: l_contact_id ota_training_plans.contact_id%TYPE;

652: RETURN BOOLEAN
653: IS
654:
655: l_person_id ota_training_plans.person_id%TYPE;
656: l_contact_id ota_training_plans.contact_id%TYPE;
657: l_proc VARCHAR2(72) := g_package|| 'is_personal_trng_plan';
658:
659: BEGIN
660:

Line 665: FROM ota_training_plans tps

661: hr_utility.set_location(' Step:'|| l_proc, 10);
662:
663: SELECT tps.person_id, tps.contact_id
664: INTO l_person_id, l_contact_id
665: FROM ota_training_plans tps
666: WHERE tps.training_plan_id = p_training_plan_id;
667:
668: hr_utility.set_location(' Step:'|| l_proc, 20);
669:

Line 681: (p_training_plan_id ota_training_plans.training_plan_id%type)

677: RETURN g_is_per_trng_plan;
678: END is_personal_trng_plan;
679:
680: Procedure complete_plan
681: (p_training_plan_id ota_training_plans.training_plan_id%type)
682: is
683:
684: CURSOR csr_tp_update(csr_training_plan_id number)
685: IS

Line 690: FROM ota_training_plans otp

686: SELECT otp.name,
687: otp.object_version_number,
688: otp.time_period_id,
689: otp.budget_currency
690: FROM ota_training_plans otp
691: WHERE otp.training_plan_id = csr_training_plan_id;
692:
693:
694: l_name ota_training_plans.name%type;

Line 694: l_name ota_training_plans.name%type;

690: FROM ota_training_plans otp
691: WHERE otp.training_plan_id = csr_training_plan_id;
692:
693:
694: l_name ota_training_plans.name%type;
695: l_object_version_number ota_training_plans.object_version_number%type;
696: l_time_period_id ota_training_plans.time_period_id%type;
697: l_budget_currency ota_training_plans.budget_currency%type;
698: l_plan_status_type_id ota_training_plans.plan_status_type_id%type;

Line 695: l_object_version_number ota_training_plans.object_version_number%type;

691: WHERE otp.training_plan_id = csr_training_plan_id;
692:
693:
694: l_name ota_training_plans.name%type;
695: l_object_version_number ota_training_plans.object_version_number%type;
696: l_time_period_id ota_training_plans.time_period_id%type;
697: l_budget_currency ota_training_plans.budget_currency%type;
698: l_plan_status_type_id ota_training_plans.plan_status_type_id%type;
699:

Line 696: l_time_period_id ota_training_plans.time_period_id%type;

692:
693:
694: l_name ota_training_plans.name%type;
695: l_object_version_number ota_training_plans.object_version_number%type;
696: l_time_period_id ota_training_plans.time_period_id%type;
697: l_budget_currency ota_training_plans.budget_currency%type;
698: l_plan_status_type_id ota_training_plans.plan_status_type_id%type;
699:
700: BEGIN

Line 697: l_budget_currency ota_training_plans.budget_currency%type;

693:
694: l_name ota_training_plans.name%type;
695: l_object_version_number ota_training_plans.object_version_number%type;
696: l_time_period_id ota_training_plans.time_period_id%type;
697: l_budget_currency ota_training_plans.budget_currency%type;
698: l_plan_status_type_id ota_training_plans.plan_status_type_id%type;
699:
700: BEGIN
701: l_plan_status_type_id := 'OTA_COMPLETED';

Line 698: l_plan_status_type_id ota_training_plans.plan_status_type_id%type;

694: l_name ota_training_plans.name%type;
695: l_object_version_number ota_training_plans.object_version_number%type;
696: l_time_period_id ota_training_plans.time_period_id%type;
697: l_budget_currency ota_training_plans.budget_currency%type;
698: l_plan_status_type_id ota_training_plans.plan_status_type_id%type;
699:
700: BEGIN
701: l_plan_status_type_id := 'OTA_COMPLETED';
702: