[Home] [Help]
PACKAGE BODY: APPS.PAY_MWS_RPDEF
Source
1 package body pay_mws_rpdef as
2 /* $Header: pymwsrpd.pkb 115.2 99/07/17 06:17:24 porting ship $ */
3
4 procedure setup is
5
6 /* Report Definitions */
7
8 l_report_type VARCHAR2(30);
9 l_report_format VARCHAR2(30);
10 l_report_qualifier VARCHAR2(30);
11 l_desc VARCHAR2(250);
12
13 /* Block Definitions */
14
15 lt_B_mag_block_id numeric_data_type_table;
16 /* Note: lt_B_mag_block_id holds the id's for the current format being
17 processed */
18 lt_B_block_name char30_data_type_table;
19 lt_B_cursor_name char250_data_type_table;
20 lt_B_no_column_ret numeric_data_type_table;
21 lt_B_validate boolean_data_type_table;
22
23 /* Formula Definitions */
24
25 lt_F_formula_name char250_data_type_table;
26 lt_F_mag_block_id numeric_data_type_table;
27 -- used to index into lt_B_mag_block_id
28 lt_F_next_block_id numeric_data_type_table;
29 lt_F_last_run_exec_mode char30_data_type_table;
30 lt_F_overflow_mode char30_data_type_table;
31 lt_F_sequence numeric_data_type_table;
32 lt_F_frequency numeric_data_type_table;
33 lt_F_validate boolean_data_type_table;
34
35 l_record_total number;
36 -- # of record defs for a format
37 l_block_total number;
38 -- # of block defs for a format
39 l_formula_id number;
40 -- Holds the id of a formula.
41 l_main_block_flag VARCHAR2(10);
42 l_block_count number;
43 l_formula_count number;
44 li_btab number := 1;
45 li_ftab number := 1;
46 l_f_id number;
47 l_mag_block_id number;
48 l_next_block_id number;
49 l_message VARCHAR2(200);
50
51 begin
52
53 l_block_total := 6;
54 l_record_total := 9;
55 l_report_type := 'MWSMR';
56 l_report_format := 'MWSR';
57 l_report_qualifier := 'FED';
58 l_desc := 'Multiple Worksite Magnetic Report';
59
60 /* Block 1 : Transmitter Block */
61 lt_B_block_name(li_btab) := 'US_MWS_TRANSMITTER';
62 lt_B_cursor_name(li_btab) := 'pay_mws_magtape_reporting.us_mws_transmitter';
63 lt_B_no_column_ret(li_btab) := 26;
64 lt_B_validate(li_btab) := false;
65
66 /* Block 2 : State Block */
67 li_btab := li_btab + 1;
68 lt_B_block_name(li_btab) := 'US_MWS_STATE';
69 lt_B_cursor_name(li_btab) := 'pay_mws_magtape_reporting.us_mws_state';
70 lt_B_no_column_ret(li_btab) := 4;
71 lt_B_validate(li_btab) := false;
72
73
74 /* Block 3 : SUI Block */
75 li_btab := li_btab + 1;
76 lt_B_block_name(li_btab) := 'US_MWS_SUI';
77 lt_B_cursor_name(li_btab) := 'pay_mws_magtape_reporting.us_mws_sui';
78 lt_B_no_column_ret(li_btab) := 6;
79 lt_B_validate(li_btab) := false;
80
81 /* Block 4 : Worksite Block */
82 li_btab := li_btab + 1;
83 lt_B_block_name(li_btab) := 'US_MWS_WORKSITE';
84 lt_B_cursor_name(li_btab) := 'pay_mws_magtape_reporting.us_mws_worksite';
85 lt_B_no_column_ret(li_btab) := 24;
86 lt_B_validate(li_btab) := false;
87
88 /* Block 5 : Worksite Organization Block */
89 li_btab := li_btab + 1;
90 lt_B_block_name(li_btab) := 'US_MWS_WORKSITE_ORGANIZATION';
91 lt_B_cursor_name(li_btab) := 'pay_mws_magtape_reporting.us_mws_worksite_organization';
92 lt_B_no_column_ret(li_btab) := 4;
93 lt_B_validate(li_btab) := false;
94
95 /* Block 6 : Organization Employees Block */
96 li_btab := li_btab + 1;
97 lt_B_block_name(li_btab) := 'US_MWS_ORGANIZATION_EMPLOYEES';
98 lt_B_cursor_name(li_btab) := 'pay_mws_magtape_reporting.us_mws_organization_employees';
99 lt_B_no_column_ret(li_btab) := 10;
100 lt_B_validate(li_btab) := false;
101
102
103 /* Record definitions. Describe sequence of records, hierarchy and the
104 structure of each record ( by formula ).
105 */
106 /* Formula to get the Transmitter details */
107 lt_F_formula_name(li_ftab) := 'US_MWS_TRANSMITTER';
108 lt_F_mag_block_id(li_ftab) := 1;
109 lt_F_next_block_id(li_ftab) := 2;
110 lt_F_last_run_exec_mode(li_ftab) := 'N';
111 lt_F_overflow_mode(li_ftab) := 'N';
112 lt_F_sequence(li_ftab) := 1;
113 lt_F_frequency(li_ftab) := NULL;
114 lt_F_validate(li_ftab) := false;
115
116 /* Formula to write the Transmitter record */
117 li_ftab := li_ftab + 1;
118 lt_F_formula_name(li_ftab) := 'US_MWS_TRANS_TOTALS';
119 lt_F_mag_block_id(li_ftab) := 1;
120 lt_F_next_block_id(li_ftab) := NULL;
121 lt_F_last_run_exec_mode(li_ftab) := 'N';
122 lt_F_overflow_mode(li_ftab) := 'N';
123 lt_F_sequence(li_ftab) := 2;
124 lt_F_frequency(li_ftab) := NULL;
125 lt_F_validate(li_ftab) := false;
126
127 /* Formula to get the state */
128 li_ftab := li_ftab + 1;
129 lt_F_formula_name(li_ftab) := 'US_MWS_GET_STATE';
130 lt_F_mag_block_id(li_ftab) := 2;
131 lt_F_next_block_id(li_ftab) := 3;
132 lt_F_last_run_exec_mode(li_ftab) := 'N';
133 lt_F_overflow_mode(li_ftab) := 'N';
134 lt_F_sequence(li_ftab) := 1;
135 lt_F_frequency(li_ftab) := NULL;
136 lt_F_validate(li_ftab) := false;
137
138 /* Formula to get the SUI A/C*/
139 li_ftab := li_ftab + 1;
140 lt_F_formula_name(li_ftab) := 'US_MWS_GET_SUI';
141 lt_F_mag_block_id(li_ftab) := 3;
142 lt_F_next_block_id(li_ftab) := 4;
143 lt_F_last_run_exec_mode(li_ftab) := 'N';
144 lt_F_overflow_mode(li_ftab) := 'N';
145 lt_F_sequence(li_ftab) := 1;
146 lt_F_frequency(li_ftab) := NULL;
147 lt_F_validate(li_ftab) := false;
148
149 /* Formula to write the SUI totals record */
150 li_ftab := li_ftab + 1;
151 lt_F_formula_name(li_ftab) := 'US_MWS_SUI_TOTALS';
152 lt_F_mag_block_id(li_ftab) := 3;
153 lt_F_next_block_id(li_ftab) := NULL;
154 lt_F_last_run_exec_mode(li_ftab) := 'N';
155 lt_F_overflow_mode(li_ftab) := 'N';
156 lt_F_sequence(li_ftab) := 2;
157 lt_F_frequency(li_ftab) := NULL;
158 lt_F_validate(li_ftab) := false;
159
160 /* Formula to get the worksite */
161 li_ftab := li_ftab + 1;
162 lt_F_formula_name(li_ftab) := 'US_MWS_GET_WORKSITE';
163 lt_F_mag_block_id(li_ftab) := 4;
164 lt_F_next_block_id(li_ftab) := 5;
165 lt_F_last_run_exec_mode(li_ftab) := 'N';
166 lt_F_overflow_mode(li_ftab) := 'N';
167 lt_F_sequence(li_ftab) := 1;
168 lt_F_frequency(li_ftab) := NULL;
169 lt_F_validate(li_ftab) := false;
170
171 /* Formula to write the worksite totals record*/
172 li_ftab := li_ftab + 1;
173 lt_F_formula_name(li_ftab) := 'US_MWS_WORKSITE_TOTALS';
174 lt_F_mag_block_id(li_ftab) := 4;
175 lt_F_next_block_id(li_ftab) := NULL;
176 lt_F_last_run_exec_mode(li_ftab) := 'N';
177 lt_F_overflow_mode(li_ftab) := 'N';
178 lt_F_sequence(li_ftab) := 2;
179 lt_F_frequency(li_ftab) := NULL;
180 lt_F_validate(li_ftab) := false;
181
182 /* Formula to get the Organizations for the worksite*/
183 li_ftab := li_ftab + 1;
184 lt_F_formula_name(li_ftab) := 'US_MWS_GET_ORGANIZATION';
185 lt_F_mag_block_id(li_ftab) := 5;
186 lt_F_next_block_id(li_ftab) := 6;
187 lt_F_last_run_exec_mode(li_ftab) := 'N';
188 lt_F_overflow_mode(li_ftab) := 'N';
189 lt_F_sequence(li_ftab) := 1;
190 lt_F_frequency(li_ftab) := NULL;
191 lt_F_validate(li_ftab) := false;
192
193 /* Formula to get the employees for the Organization */
194 li_ftab := li_ftab + 1;
195 lt_F_formula_name(li_ftab) := 'US_MWS_GET_EMPLOYEES';
196 lt_F_mag_block_id(li_ftab) := 6;
197 lt_F_next_block_id(li_ftab) := NULL;
198 lt_F_last_run_exec_mode(li_ftab) := 'N';
199 lt_F_overflow_mode(li_ftab) := 'N';
200 lt_F_sequence(li_ftab) := 1;
201 lt_F_frequency(li_ftab) := NULL;
202 lt_F_validate(li_ftab) := false;
203
204
205 /* Delete existing block and record definitions for the MWS Report */
206
207 hr_utility.trace('Insert definition for ' || l_report_format);
208 hr_utility.trace(l_desc);
209
210 hr_utility.trace('Deleting magnetic records...');
211
212 delete from PAY_MAGNETIC_RECORDS
213 where MAGNETIC_BLOCK_ID in
214 (select mgb.MAGNETIC_BLOCK_ID
215 from PAY_MAGNETIC_BLOCKS mgb
216 where mgb.REPORT_FORMAT = l_report_format);
217
218 hr_utility.trace('Deleting magnetic blocks');
219
220 delete from PAY_MAGNETIC_BLOCKS mgb
221 where mgb.REPORT_FORMAT = l_report_format;
222
223 hr_utility.trace('Deleting report format mappings...');
224
225 delete from PAY_REPORT_FORMAT_MAPPINGS_F rfm
226 where rfm.REPORT_FORMAT = l_report_format;
227
228 /* Insert into pay_report_format_mappings_f */
229
230 hr_utility.trace('Inserting the report qualifier : '||
231 l_report_qualifier);
232
233 insert into PAY_REPORT_FORMAT_MAPPINGS_F
234 ( REPORT_TYPE,
235 REPORT_QUALIFIER,
236 REPORT_CATEGORY,
237 REPORT_FORMAT,
238 EFFECTIVE_START_DATE,
239 EFFECTIVE_END_DATE )
240 values ( l_report_type,
241 l_report_qualifier,
242 'RT',
243 l_report_format,
244 c_start_date,
245 c_end_date );
246
247 /* Insert blocks into PAY_MAGNETIC_BLOCKS and get the magnetic block id
248 for each of the block in lt_B_mag_block_id */
249
250 for l_block_count IN 1..l_block_total loop
251
252 hr_utility.trace('Inserting block ' || lt_B_block_name(l_block_count));
253 l_message := 'Error inserting block ' || lt_B_block_name(l_block_count);
254
255 /* Only the first block can be the main block */
256 if l_block_count = 1 then
257 l_main_block_flag := 'Y';
258 else l_main_block_flag := 'N';
259 end if;
260
261 Pay_Mgb_Ins.Ins
262 ( p_magnetic_block_id => lt_B_mag_block_id(l_block_count),
263 p_block_name => lt_B_block_name(l_block_count),
264 p_main_block_flag => l_main_block_flag,
265 p_report_format => l_report_format,
266 p_cursor_name => lt_B_cursor_name(l_block_count),
267 p_no_column_returned => lt_B_no_column_ret(l_block_count),
268 p_validate => lt_B_validate(l_block_count));
269 end loop;
270
271 /* Insert Formulas into PAY_MAGNETIC_RECORDS */
272
273 for l_formula_count in 1..l_record_total loop
274
275 hr_utility.trace('Inserting record def. for formula '
276 || lt_F_formula_name(l_formula_count));
277
278 l_message:='Error inserting record def. for formula '
279 || lt_F_formula_name(l_formula_count);
280
281 l_f_id := Pay_Mag_Utils.Lookup_Formula
282 ( p_session_date => c_start_date,
283 p_business_group_id => NULL,
284 p_legislation_code =>'US',
285 p_formula_name => lt_F_formula_name(l_formula_count));
286
287 if l_f_id is NULL then
288 hr_utility.trace('Could not find formula id');
289 else hr_utility.trace('Successfully found formula id');
290 end if;
291
292 /* Since lt_F_mag_block_id is used to index into lt_B_mag_block_id,
293 take care of NULL values */
294
295 if lt_F_mag_block_id(l_formula_count) is NULL then
296 hr_utility.trace( 'Error :NULL block id not allowed');
297 else l_mag_block_id := lt_B_mag_block_id(lt_F_mag_block_id(l_formula_count));
298 end if;
299
300 if lt_F_next_block_id(l_formula_count) is NULL then
301 l_next_block_id := NULL;
302 else l_next_block_id := lt_B_mag_block_id(lt_F_next_block_id(l_formula_count));
303 end if;
304
305 hr_utility.trace( 'Formula_id = '|| l_f_id);
306 hr_utility.trace( 'magnetic_block_id = '|| l_mag_block_id);
307 hr_utility.trace( 'next_block_id = '|| l_next_block_id);
308 hr_utility.trace( 'last_run_exec_mode = '|| lt_F_last_run_exec_mode(l_formula_count));
309 hr_utility.trace( 'overflow_mode = '|| lt_F_overflow_mode(l_formula_count));
310 hr_utility.trace( 'sequence = '|| lt_F_sequence(l_formula_count));
311 hr_utility.trace( 'frequency = '|| lt_F_frequency(l_formula_count));
312
313 Pay_Mgr_Ins.Ins
314 ( p_formula_id => l_f_id,
315 p_magnetic_block_id => l_mag_block_id,
316 p_next_block_id => l_next_block_id,
317 p_last_run_executed_mode => lt_F_last_run_exec_mode(l_formula_count),
318 p_overflow_mode => lt_F_overflow_mode(l_formula_count),
319 p_sequence => lt_F_sequence(l_formula_count),
320 p_frequency => lt_F_frequency(l_formula_count),
321 p_validate => lt_F_validate(l_formula_count));
322
323 end loop;
324
325 hr_utility.trace('Successfully created '|| l_report_format
326 || ' format mapping..');
327
328 hr_utility.trace('Issuing commit ...');
329 commit;
330
331 exception
332 when others then
333 hr_utility.trace( l_message||' - ORA '||to_char(SQLCODE));
334 hr_utility.trace(l_message || ' - Ora: '|| to_char(sqlcode));
335
336 hr_utility.trace('END REPORT DEFINITION PROCESS ');
337 end setup;
338
339 end pay_mws_rpdef;