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_TRAINING_PLAN_UPGRADE dependencies on OTA_LP_MEMBER_ENROLLMENTS

Line 8: LP_MAP_TABLE_NAME_M_E constant varchar2(30) := 'OTA_LP_MEMBER_ENROLLMENTS';

4: -- LP_MAP_UPGRADE_ID constant number(2) := -2;
5: OTA_MIG_FAIL_ID constant number(2) := -1;
6: LP_MAP_TABLE_NAME_E constant varchar2(30) := 'OTA_LP_ENROLLMENTS';
7: LP_MAP_TABLE_NAME_M constant varchar2(30) := 'OTA_LEARNING_PATH_MEMBERS';
8: LP_MAP_TABLE_NAME_M_E constant varchar2(30) := 'OTA_LP_MEMBER_ENROLLMENTS';
9: LP_UPGRADE_NAME constant varchar2(30) := 'OTLPUPG';
10: LP_LOG_TYPE_I constant varchar2(30) := 'I'; -- log type is Infornation
11: LP_LOG_TYPE_N constant varchar2(30) := 'N'; -- log type is Internal
12: LP_LOG_TYPE_E constant varchar2(30) := 'E';-- log type is Error

Line 815: l_add_struct_d(l_add_struct_d.count) := 'OTA_LP_MEMBER_ENROLLMENTS';

811:
812:
813: BEGIN
814: l_add_struct_d.extend(1);
815: l_add_struct_d(l_add_struct_d.count) := 'OTA_LP_MEMBER_ENROLLMENTS';
816: hr_dflex_utility.create_ignore_df_validation(p_rec => l_add_struct_d);
817:
818:
819: if not is_lp_mem_enr_migrated(p_training_plan_member_id) then

Line 1235: l_member_status_code ota_lp_member_enrollments.member_status_code%type;

1231: l_err_msg varchar2(2000);
1232: l_rows_processed number;
1233: l_end_date date;
1234:
1235: l_member_status_code ota_lp_member_enrollments.member_status_code%type;
1236: l_old_status ota_lp_member_enrollments.member_status_code%type;
1237: l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
1238: l_date_status_changed ota_delegate_bookings.date_status_changed%type;
1239: --

Line 1236: l_old_status ota_lp_member_enrollments.member_status_code%type;

1232: l_rows_processed number;
1233: l_end_date date;
1234:
1235: l_member_status_code ota_lp_member_enrollments.member_status_code%type;
1236: l_old_status ota_lp_member_enrollments.member_status_code%type;
1237: l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
1238: l_date_status_changed ota_delegate_bookings.date_status_changed%type;
1239: --
1240: CURSOR csr_get_tp IS

Line 1420: --Create a record in table ota_lp_member_enrollments

1416: ,p_upgrade_id => p_update_id
1417: ,p_training_plan_member_id => tpm_rec.training_plan_member_id
1418: ,p_training_plan_id => tp_rec.training_plan_id);
1419:
1420: --Create a record in table ota_lp_member_enrollments
1421: if l_learning_path_member_id is not null then
1422: create_lp_member_enrollment(
1423: p_lp_member_enrollment_id => l_lp_member_enrollment_id
1424: ,p_lp_enrollment_id => l_lp_enrollment_id

Line 1523: l_old_status ota_lp_member_enrollments.member_status_code%type;

1519: l_end_date date;
1520: l_member_status_type_flag boolean;
1521: l_status_code ota_lp_enrollments.path_status_code%type;
1522: l_status_type_id varchar2(30);
1523: l_old_status ota_lp_member_enrollments.member_status_code%type;
1524: l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
1525: l_date_status_changed ota_delegate_bookings.date_status_changed%type;
1526: l_name varchar2(80);
1527: l_lang_len number;

Line 1531: l_member_status_code ota_lp_member_enrollments.member_status_code%type;

1527: l_lang_len number;
1528: MAX_NAME_LEN constant number := 80;
1529: MAX_DATA_TRUNC_LEN constant number := 10;
1530: l_display_to_learner_flag ota_learning_paths.display_to_learner_flag%type;
1531: l_member_status_code ota_lp_member_enrollments.member_status_code%type;
1532: --
1533:
1534:
1535:

Line 1933: l_member_status_code ota_lp_member_enrollments.member_status_code%type;

1929: l_completion_date date;
1930: l_rows_processed number;
1931: l_end_date date;
1932:
1933: l_member_status_code ota_lp_member_enrollments.member_status_code%type;
1934: l_old_status ota_lp_member_enrollments.member_status_code%type;
1935: l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
1936: l_date_status_changed ota_delegate_bookings.date_status_changed%type;
1937: l_err_code varchar2(72);

Line 1934: l_old_status ota_lp_member_enrollments.member_status_code%type;

1930: l_rows_processed number;
1931: l_end_date date;
1932:
1933: l_member_status_code ota_lp_member_enrollments.member_status_code%type;
1934: l_old_status ota_lp_member_enrollments.member_status_code%type;
1935: l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
1936: l_date_status_changed ota_delegate_bookings.date_status_changed%type;
1937: l_err_code varchar2(72);
1938: l_err_msg varchar2(2000);

Line 2137: -- Updating the status of ota_lp_enrollments and ota_lp_member_enrollments table.

2133:
2134: -- ----------------------------------------------------------------------------
2135: -- |-----------------------< update_enrollment_status >-----------------------|
2136: -- ----------------------------------------------------------------------------
2137: -- Updating the status of ota_lp_enrollments and ota_lp_member_enrollments table.
2138: /*
2139: PROCEDURE update_enrollment_status is
2140: begin
2141: UPDATE ota_lp_enrollments

Line 2146: UPDATE ota_lp_member_enrollments

2142: SET path_status_code = 'COMPLETED'
2143: where path_status_code = 'OTA_COMPLETED';
2144:
2145:
2146: UPDATE ota_lp_member_enrollments
2147: SET member_status_code = decode(member_status_code,'OTA_COMPLETED','COMPLETED'
2148: ,'OTA_PLANNED','PLANNED','OTA_AWAITING_APPROVAL','AWAITING_APPROVAL'
2149: ,member_status_code);
2150:

Line 2163: FROM ota_lp_member_enrollments lpme

2159: IS
2160: CURSOR csr_member_status is
2161: SELECT decode(member_status_code, 'PLANNED', 0,'ACTIVE', 0, 'AWAITING_APPROVAL',0, 'COMPLETED',1,
2162: 'CANCELLED',2) top_status
2163: FROM ota_lp_member_enrollments lpme
2164: WHERE lpme.lp_enrollment_id = p_lp_enrollment_id
2165: ORDER BY top_status;
2166: l_is_complete varchar2(1) := 'F';
2167: begin

Line 2191: l_member_status_code ota_lp_member_enrollments.member_status_code%type;

2187: l_lp_enrollment_id number;
2188: l_learning_path_id number;
2189: l_tpm_changed boolean;
2190: l_lpm_status_changed boolean;
2191: l_member_status_code ota_lp_member_enrollments.member_status_code%type;
2192: l_old_status ota_lp_member_enrollments.member_status_code%type;
2193: l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
2194: l_date_status_changed ota_delegate_bookings.date_status_changed%type;
2195: l_dummy number;

Line 2192: l_old_status ota_lp_member_enrollments.member_status_code%type;

2188: l_learning_path_id number;
2189: l_tpm_changed boolean;
2190: l_lpm_status_changed boolean;
2191: l_member_status_code ota_lp_member_enrollments.member_status_code%type;
2192: l_old_status ota_lp_member_enrollments.member_status_code%type;
2193: l_max_date_status_changed ota_delegate_bookings.date_status_changed%type;
2194: l_date_status_changed ota_delegate_bookings.date_status_changed%type;
2195: l_dummy number;
2196: l_status ota_booking_status_types.type%type;

Line 2222: FROM ota_lp_member_enrollments lpme,ota_learning_path_members lpm

2218: -- Get all the Learning Path Components that are in Active or Planned Status.
2219: CURSOR csr_get_lpme(p_lp_enrollment_id in number) IS
2220: SELECT lpm.activity_version_id, lpme.lp_member_enrollment_id, lpme.member_status_code,
2221: lpme.object_version_number
2222: FROM ota_lp_member_enrollments lpme,ota_learning_path_members lpm
2223: WHERE lpme.learning_path_member_id = lpm.learning_path_member_id
2224: AND lpme.lp_enrollment_id = p_lp_enrollment_id
2225: -- AND lpm.learning_path_id = p_learning_path_id
2226: AND lpme.member_status_code IN ('PLANNED','ACTIVE');

Line 2245: SELECT 1 from ota_lp_member_enrollments lpme

2241:
2242: */
2243:
2244: cursor csr_get_member_status(P_LP_ENROLLMENT_ID IN NUMBER) is
2245: SELECT 1 from ota_lp_member_enrollments lpme
2246: WHERE lpme.member_status_code in ('ACTIVE','PLANNED')
2247: AND lpme.LP_ENROLLMENT_ID = P_LP_ENROLLMENT_ID;
2248:
2249: cursor csr_get_completed_lpms is

Line 2251: FROM ota_lp_member_enrollments lpme

2247: AND lpme.LP_ENROLLMENT_ID = P_LP_ENROLLMENT_ID;
2248:
2249: cursor csr_get_completed_lpms is
2250: SELECT lpme.lp_member_enrollment_id
2251: FROM ota_lp_member_enrollments lpme
2252: WHERE lpme.member_status_code = 'COMPLETED'
2253: AND lpme.LP_ENROLLMENT_ID between p_start_pkid and p_end_pkid;
2254: --
2255: cursor csr_get_lpm_completion_date(p_lpm_enrollment_id IN NUMBER) IS

Line 2258: ota_lp_member_enrollments lpme,

2254: --
2255: cursor csr_get_lpm_completion_date(p_lpm_enrollment_id IN NUMBER) IS
2256: SELECT trunc(min(nvl(tdb.date_status_changed,tdb.date_booking_placed))) completion_date
2257: FROM ota_delegate_bookings tdb,
2258: ota_lp_member_enrollments lpme,
2259: ota_learning_path_members lpm,
2260: ota_lp_enrollments lpe,
2261: ota_events evt,
2262: ota_booking_status_types bst

Line 2276: FROM ota_lp_member_enrollments lpme

2272:
2273: cursor csr_get_lp_completion_info(p_lp_enrollment_id IN NUMBER) IS
2274: SELECT trunc(max(lpme.completion_date)) completion_date,
2275: count(lpme.lp_member_enrollment_id) completed_courses
2276: FROM ota_lp_member_enrollments lpme
2277: WHERE lpme.member_status_code = 'COMPLETED'
2278: AND lpme.lp_enrollment_id = p_lp_enrollment_id;
2279:
2280: Cursor csr_get_attrib_personal_tps is

Line 2316: /* update the enrollment status of ota_lp_enrollments and ota_lp_member_enrollments */

2312:
2313:
2314: BEGIN
2315:
2316: /* update the enrollment status of ota_lp_enrollments and ota_lp_member_enrollments */
2317: --update_enrollment_status;
2318:
2319: update ota_lp_enrollments lpe
2320: set lpe.no_of_mandatory_courses = (select count(lpme.learning_path_member_id)

Line 2321: from ota_lp_member_enrollments lpme

2317: --update_enrollment_status;
2318:
2319: update ota_lp_enrollments lpe
2320: set lpe.no_of_mandatory_courses = (select count(lpme.learning_path_member_id)
2321: from ota_lp_member_enrollments lpme
2322: where lpme.lp_enrollment_id = lpe.lp_enrollment_id);
2323:
2324:
2325:

Line 2349: UPDATE ota_lp_member_enrollments

2345: l_lpm_enrollment_id := lpm_rec.lp_member_enrollment_id;
2346: OPEN csr_get_lpm_completion_date(l_lpm_enrollment_id);
2347: FETCH csr_get_lpm_completion_date INTO l_completion_date;
2348: IF csr_get_lpm_completion_date%FOUND THEN
2349: UPDATE ota_lp_member_enrollments
2350: SET completion_date = l_completion_date
2351: WHERE lp_member_enrollment_id = l_lpm_enrollment_id;
2352: END IF;
2353: CLOSE csr_get_lpm_completion_date;

Line 2446: migrate_dff_contexts('OTA_TRAINING_PLAN_MEMBERS','OTA_LP_MEMBER_ENROLLMENTS','ATTRIBUTE','ATTRIBUTE',p_update_id);

2442: /* Create DFF context for LPME from personal TPM */
2443: Open csr_get_attrib_personal_tpm;
2444: Fetch csr_get_attrib_personal_tpm into l_flex_val;
2445: if l_flex_val is not null then
2446: migrate_dff_contexts('OTA_TRAINING_PLAN_MEMBERS','OTA_LP_MEMBER_ENROLLMENTS','ATTRIBUTE','ATTRIBUTE',p_update_id);
2447: end if;
2448: Close csr_get_attrib_personal_tpm;
2449: SELECT nvl(count(1),0)
2450: INTO l_rows_processed