9:
10: --
11: -- Set up the PL/SQL tables used to hold values returned from Bulk Collect
12: --
13: type person_id_list is table of per_all_people_f.person_id%type;
14: TYPE effective_start_date_list IS TABLE OF per_all_people_f.effective_start_date%TYPE;
15: TYPE effective_end_date_list IS TABLE OF per_all_people_f.effective_end_Date%TYPE;
16: -- Fix 3386992 Start
17: type first_name_list is table of per_all_people_f.first_name%type;
10: --
11: -- Set up the PL/SQL tables used to hold values returned from Bulk Collect
12: --
13: type person_id_list is table of per_all_people_f.person_id%type;
14: TYPE effective_start_date_list IS TABLE OF per_all_people_f.effective_start_date%TYPE;
15: TYPE effective_end_date_list IS TABLE OF per_all_people_f.effective_end_Date%TYPE;
16: -- Fix 3386992 Start
17: type first_name_list is table of per_all_people_f.first_name%type;
18: type middle_names_list is table of per_all_people_f.middle_names%type;
11: -- Set up the PL/SQL tables used to hold values returned from Bulk Collect
12: --
13: type person_id_list is table of per_all_people_f.person_id%type;
14: TYPE effective_start_date_list IS TABLE OF per_all_people_f.effective_start_date%TYPE;
15: TYPE effective_end_date_list IS TABLE OF per_all_people_f.effective_end_Date%TYPE;
16: -- Fix 3386992 Start
17: type first_name_list is table of per_all_people_f.first_name%type;
18: type middle_names_list is table of per_all_people_f.middle_names%type;
19: type last_name_list is table of per_all_people_f.last_name%type;
13: type person_id_list is table of per_all_people_f.person_id%type;
14: TYPE effective_start_date_list IS TABLE OF per_all_people_f.effective_start_date%TYPE;
15: TYPE effective_end_date_list IS TABLE OF per_all_people_f.effective_end_Date%TYPE;
16: -- Fix 3386992 Start
17: type first_name_list is table of per_all_people_f.first_name%type;
18: type middle_names_list is table of per_all_people_f.middle_names%type;
19: type last_name_list is table of per_all_people_f.last_name%type;
20: type known_as_list is table of per_all_people_f.known_as%type;
21: type title_list is table of per_all_people_f.title%type;
14: TYPE effective_start_date_list IS TABLE OF per_all_people_f.effective_start_date%TYPE;
15: TYPE effective_end_date_list IS TABLE OF per_all_people_f.effective_end_Date%TYPE;
16: -- Fix 3386992 Start
17: type first_name_list is table of per_all_people_f.first_name%type;
18: type middle_names_list is table of per_all_people_f.middle_names%type;
19: type last_name_list is table of per_all_people_f.last_name%type;
20: type known_as_list is table of per_all_people_f.known_as%type;
21: type title_list is table of per_all_people_f.title%type;
22: type suffix_list is table of per_all_people_f.suffix%type;
15: TYPE effective_end_date_list IS TABLE OF per_all_people_f.effective_end_Date%TYPE;
16: -- Fix 3386992 Start
17: type first_name_list is table of per_all_people_f.first_name%type;
18: type middle_names_list is table of per_all_people_f.middle_names%type;
19: type last_name_list is table of per_all_people_f.last_name%type;
20: type known_as_list is table of per_all_people_f.known_as%type;
21: type title_list is table of per_all_people_f.title%type;
22: type suffix_list is table of per_all_people_f.suffix%type;
23: type date_of_birth_list is table of per_all_people_f.date_of_birth%type;
16: -- Fix 3386992 Start
17: type first_name_list is table of per_all_people_f.first_name%type;
18: type middle_names_list is table of per_all_people_f.middle_names%type;
19: type last_name_list is table of per_all_people_f.last_name%type;
20: type known_as_list is table of per_all_people_f.known_as%type;
21: type title_list is table of per_all_people_f.title%type;
22: type suffix_list is table of per_all_people_f.suffix%type;
23: type date_of_birth_list is table of per_all_people_f.date_of_birth%type;
24: type business_group_id_list is table of per_all_people_f.business_group_id%type;
17: type first_name_list is table of per_all_people_f.first_name%type;
18: type middle_names_list is table of per_all_people_f.middle_names%type;
19: type last_name_list is table of per_all_people_f.last_name%type;
20: type known_as_list is table of per_all_people_f.known_as%type;
21: type title_list is table of per_all_people_f.title%type;
22: type suffix_list is table of per_all_people_f.suffix%type;
23: type date_of_birth_list is table of per_all_people_f.date_of_birth%type;
24: type business_group_id_list is table of per_all_people_f.business_group_id%type;
25: type full_name_list is table of per_all_people_f.full_name%type;
18: type middle_names_list is table of per_all_people_f.middle_names%type;
19: type last_name_list is table of per_all_people_f.last_name%type;
20: type known_as_list is table of per_all_people_f.known_as%type;
21: type title_list is table of per_all_people_f.title%type;
22: type suffix_list is table of per_all_people_f.suffix%type;
23: type date_of_birth_list is table of per_all_people_f.date_of_birth%type;
24: type business_group_id_list is table of per_all_people_f.business_group_id%type;
25: type full_name_list is table of per_all_people_f.full_name%type;
26: -- Fix 3386992 End
19: type last_name_list is table of per_all_people_f.last_name%type;
20: type known_as_list is table of per_all_people_f.known_as%type;
21: type title_list is table of per_all_people_f.title%type;
22: type suffix_list is table of per_all_people_f.suffix%type;
23: type date_of_birth_list is table of per_all_people_f.date_of_birth%type;
24: type business_group_id_list is table of per_all_people_f.business_group_id%type;
25: type full_name_list is table of per_all_people_f.full_name%type;
26: -- Fix 3386992 End
27:
20: type known_as_list is table of per_all_people_f.known_as%type;
21: type title_list is table of per_all_people_f.title%type;
22: type suffix_list is table of per_all_people_f.suffix%type;
23: type date_of_birth_list is table of per_all_people_f.date_of_birth%type;
24: type business_group_id_list is table of per_all_people_f.business_group_id%type;
25: type full_name_list is table of per_all_people_f.full_name%type;
26: -- Fix 3386992 End
27:
28: --
21: type title_list is table of per_all_people_f.title%type;
22: type suffix_list is table of per_all_people_f.suffix%type;
23: type date_of_birth_list is table of per_all_people_f.date_of_birth%type;
24: type business_group_id_list is table of per_all_people_f.business_group_id%type;
25: type full_name_list is table of per_all_people_f.full_name%type;
26: -- Fix 3386992 End
27:
28: --
29: -- Declare the variables based on this new type
66: cursor csr_get_people_in_leg (p_legislation_code varchar2) is
67: select person_id,
68: effective_start_date,
69: effective_end_date
70: from per_all_people_f per, per_business_groups bg
71: where per.business_group_id = bg.business_group_id
72: and bg.legislation_code = p_legislation_code;
73:
74: -- Fix 3386992 Start
86: suffix,
87: date_of_birth,
88: per.business_group_id,
89: full_name
90: from per_all_people_f per, per_business_groups bg
91: where per.business_group_id = bg.business_group_id
92: and bg.legislation_code = p_legislation_code;
93: -- Fix 3386992 End
94:
190: FORALL j IN l_person_id_list.FIRST..l_person_id_list.LAST
191:
192: -- Touching the rows by update statement so that the actual
193: -- Full name and Order name is derived by logic in
194: -- trigger PER_ALL_PEOPLE_F_NAME
195: --
196:
197: UPDATE per_all_people_f
198: SET last_name = last_name
193: -- Full name and Order name is derived by logic in
194: -- trigger PER_ALL_PEOPLE_F_NAME
195: --
196:
197: UPDATE per_all_people_f
198: SET last_name = last_name
199: WHERE person_id = l_person_id_list(j)
200: and effective_start_date = l_effective_start_date_list(j)
201: and effective_end_date = l_effective_end_date_list(j);
259: FORALL j IN l_person_id_list.FIRST..l_person_id_list.LAST
260:
261: -- Touching the rows by update statement so that the actual
262: -- Full name and Order name is derived by logic in
263: -- trigger PER_ALL_PEOPLE_F_NAME
264: --
265:
266: UPDATE per_all_people_f
267: SET full_name = l_full_name_list(j) -- Fix 3386992
262: -- Full name and Order name is derived by logic in
263: -- trigger PER_ALL_PEOPLE_F_NAME
264: --
265:
266: UPDATE per_all_people_f
267: SET full_name = l_full_name_list(j) -- Fix 3386992
268: WHERE person_id = l_person_id_list(j)
269: and effective_start_date = l_effective_start_date_list(j)
270: and effective_end_date = l_effective_end_date_list(j);