[Home] [Help]
PACKAGE BODY: APPS.HR_DM_RANGE
Source
1 PACKAGE BODY hr_dm_range AS
2 /* $Header: perdmrng.pkb 120.0 2005/05/31 17:13:08 appldev noship $ */
3
4
5 /*---------------------------- PUBLIC ROUTINES ---------------------------*/
6
7 -- ------------------------- main ------------------------
8 -- Description: This is the range phase slave. It reads an item from the
9 -- hr_dm_phase_items table for the range phase and calls the appropriate
10 -- TDS package to populate the table hr_dm_migration_ranges.
11 --
12 --
13 -- Input Parameters
14 -- p_migration_id - of current migration
15 --
16 -- p_concurrent_process - Y if program called from CM, otherwise
17 -- N prevents message logging
18 --
19 -- p_last_migration_date - date of last sucessful migration
20 --
21 -- p_process_number - process number given to slave process by
22 -- master process. The first process gets
23 -- number 1, second gets number 2 and so on
24 -- the maximum nuber being equal to the
25 -- number of threads.
26 --
27 --
28 -- Output Parameters
29 -- errbuf - buffer for output message (for CM manager)
30 --
31 -- retcode - program return code (for CM manager)
32 --
33 --
34 -- ------------------------------------------------------------------------
35
36
37 --
38 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
39 retcode OUT NOCOPY NUMBER,
40 p_migration_id IN NUMBER,
41 p_concurrent_process IN VARCHAR2 DEFAULT 'Y',
42 p_last_migration_date IN DATE,
43 p_process_number IN NUMBER
44 ) IS
45 --
46
47 l_current_phase_status VARCHAR2(30);
48 l_phase_id NUMBER;
49 e_fatal_error EXCEPTION;
50 l_fatal_error_message VARCHAR2(200);
51 l_table_name VARCHAR2(30);
52 l_status VARCHAR2(30);
53 l_phase_item_id NUMBER;
54 l_business_group_id NUMBER;
55 l_migration_type VARCHAR2(30);
56 l_string VARCHAR2(500);
57 l_short_name VARCHAR2(30);
58 l_no_of_threads NUMBER;
59 l_cursor NUMBER;
60 l_return_value NUMBER;
61
62
63
64
65 CURSOR csr_get_pi IS
66 SELECT pi.phase_item_id, pi.table_name, pi.status, tbl.short_name
67 FROM hr_dm_phase_items pi,
68 hr_dm_tables tbl
69 WHERE pi.status = 'NS'
70 AND mod(pi.phase_item_id,l_no_of_threads) + 1 = p_process_number
71 AND pi.phase_id = l_phase_id
72 AND pi.table_name = tbl.table_name;
73
74 CURSOR csr_migration_info IS
75 SELECT business_group_id, migration_type
76 FROM hr_dm_migrations
77 WHERE migration_id = p_migration_id;
78
79
80 --
81 BEGIN
82 --
83
84 -- initialize messaging (only for concurrent processing)
85 IF (p_concurrent_process = 'Y') THEN
86 hr_dm_utility.message_init;
87 END IF;
88
89 hr_dm_utility.message('ROUT','entry:hr_dm_range.main', 5);
90 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
91 ')(p_last_migration_date - ' ||
92 p_last_migration_date || ')', 10);
93
94 -- get the current phase_id
95 l_phase_id := hr_dm_utility.get_phase_id('R', p_migration_id);
96
97 -- get the business_group_id and migration_type
98 OPEN csr_migration_info;
99 LOOP
100 FETCH csr_migration_info INTO l_business_group_id, l_migration_type;
101 EXIT WHEN csr_migration_info%NOTFOUND;
102 END LOOP;
103 CLOSE csr_migration_info;
104
105 -- find the number of threads to use
106 l_no_of_threads := hr_dm_utility.number_of_threads(l_business_group_id);
107
108 -- loop until either range phase is in error or all range phase items have
109 -- been processed
110
111 LOOP
112 -- get status of range phase, is phase completed?
113 -- if null returned, then assume it is NS.
114 l_current_phase_status := NVL(hr_dm_utility.get_phase_status('R',
115 p_migration_id), 'NS');
116
117 -- if status is error, then raise an exception
118 IF (l_current_phase_status = 'E') THEN
119 l_fatal_error_message := 'Current phase in error - slave exiting';
120 RAISE e_fatal_error;
121 END IF;
122
123 -- fetch a row from the phase items table
124 OPEN csr_get_pi;
125 FETCH csr_get_pi INTO l_phase_item_id, l_table_name,
126 l_status, l_short_name;
127 EXIT WHEN csr_get_pi%NOTFOUND;
128 CLOSE csr_get_pi;
129 -- update status to started
130 hr_dm_utility.update_phase_items(p_new_status => 'S',
131 p_id => l_phase_item_id);
132
133 -- send info on current table to logfile
134 hr_dm_utility.message('INFO','Processing - ' || l_table_name, 13);
135
136 -- call calculate ranges code in TDS package
137
138 -- build parameter string
139 l_string := 'begin hrdmd_' || l_short_name || '.calculate_ranges( ' ||
140 l_business_group_id || ', ''' ||
141 p_last_migration_date || ''', ' ||
142 l_phase_item_id || ', ' ||
143 l_no_of_threads || '); end;';
144
145 l_cursor := dbms_sql.open_cursor;
146 dbms_sql.parse(l_cursor, l_string, dbms_sql.native);
147 l_return_value := dbms_sql.execute(l_cursor);
148 dbms_sql.close_cursor(l_cursor);
149
150
151 -- update status to completed
152 hr_dm_utility.update_phase_items(p_new_status => 'C',
153 p_id => l_phase_item_id);
154
155 END LOOP;
156
157
158 -- set up return values to concurrent manager
159 retcode := 0;
160 errbuf := 'No errors - examine logfiles for detailed reports.';
161
162
163 hr_dm_utility.message('INFO','Range - main controller', 15);
164 hr_dm_utility.message('SUMM','Range - main controller', 20);
165 hr_dm_utility.message('ROUT','exit:hr_dm_range.main', 25);
166 hr_dm_utility.message('PARA','(retcode - ' || retcode ||
167 ')(errbuf - ' || errbuf || ')', 30);
168
169 -- error handling
170 EXCEPTION
171 WHEN e_fatal_error THEN
172 retcode := 0;
173 errbuf := 'An error occurred during the migration - examine logfiles ' ||
174 'for detailed reports.';
175 hr_dm_utility.error(SQLCODE,'hr_dm_range.main',l_fatal_error_message,'R');
176 WHEN OTHERS THEN
177 retcode := 2;
178 errbuf := 'An error occurred during the migration - examine logfiles for '
179 || 'detailed reports.';
180 -- update status to error
181 hr_dm_utility.update_phase_items(p_new_status => 'E',
182 p_id => l_phase_item_id);
183 hr_dm_utility.error(SQLCODE,'hr_dm_range.main','(none)','R');
184
185
186 --
187 END main;
188 --
189
190
191
192 END hr_dm_range;