DBA Data[Home] [Help]

PACKAGE BODY: APPS.HRI_OPL_CMNTS

Source


1 PACKAGE BODY HRI_OPL_CMNTS AS
2 /* $Header: hriocmnt.pkb 120.3 2005/12/15 01:36:54 ddutta noship $ */
3 -- -----------------------------------------------------------------------------
4 --                         Multithreading Calls                               --
5 -- -----------------------------------------------------------------------------
6 -- This package uses the hri multithreading utility for processing.
7 -- The Multithreading Utility Provides the Framework for processing collection
8 -- using multiple threads. The sequence of operation performed by the utility are
9 --   a) Invoke the PRE_PROCESS procedure to initialize the global variables and
10 --      return a SQL based on which the processing ranges will be created.
11 --   b) Invoke the PROCESS_RANGE procedure to process the assignments in the range
12 --      This part is done by multiple threads. The utility passes the range_id along
13 --      with the starting and ending object_id for the range. This range is to be
14 --      by the procedure
15 --   c) Invoke the POST_PROCESS procedure to perform the post processing tasks
16 -- -----------------------------------------------------------------------------
17 --
18 --
19 -- Global Multi Threading Array
20 --
21 g_mthd_action_array      HRI_ADM_MTHD_ACTIONS%ROWTYPE;
22 --
23 -- Global variables representing parameters
24 --
25 g_refresh_start_date     DATE;
26 g_refresh_end_date       DATE;
27 g_full_refresh           VARCHAR2(5);
28 --
29 -- Global flag which determines whether debugging is turned on
30 --
31 g_debug_flag             VARCHAR2(5);
32 --
33 -- Whether called from a concurrent program
34 --
35 g_concurrent_flag         VARCHAR2(5);
36 -- ----------------------------------------------------------------------------
37 -- Inserts row into concurrent program log
38 --
39 --
40 PROCEDURE output(p_text  VARCHAR2) IS
41 BEGIN
42   --
43   IF (g_concurrent_flag = 'Y') THEN
44     --
45     -- Write to the concurrent request log
46     --
47     fnd_file.put_line(fnd_file.log, p_text);
48     --
49   ELSE
50     --
51     hr_utility.trace(p_text);
52     --
53   END IF;
54   --
55 END output;
56 --
57 -- -----------------------------------------------------------------------------
58 -- Inserts row into concurrent program log if debugging is enabled
59 -- -----------------------------------------------------------------------------
60 --
61 PROCEDURE dbg(p_text  VARCHAR2) IS
62 --
63 BEGIN
64 --
65   IF (g_debug_flag = 'Y' OR g_mthd_action_array.debug_flag = 'Y') THEN
66     --
67     -- Write to output
68     --
69     output(p_text);
70     --
71   END IF;
72 --
73 END dbg;
74 --
75 -- ----------------------------------------------------------------------------
76 -- Runs given sql statement dynamically without raising an exception
77 -- ----------------------------------------------------------------------------
78 --
79 PROCEDURE run_sql_stmt_noerr( p_sql_stmt   VARCHAR2 )
80 IS
81 --
82 BEGIN
83   --
84   EXECUTE IMMEDIATE p_sql_stmt;
85   --
86 EXCEPTION WHEN OTHERS THEN
87   --
88   output('Could not run the following sql:');
89   output(SUBSTR(p_sql_stmt,1,230));
90   --
91 END run_sql_stmt_noerr;
92 --
93 -- ----------------------------------------------------------------------------
94 -- SET_PARAMETERS
95 -- sets up parameters required for the process.
96 -- ----------------------------------------------------------------------------
97 --
98 PROCEDURE set_parameters(p_mthd_action_id  IN NUMBER
99                         ,p_mthd_range_id   IN NUMBER DEFAULT NULL) IS
100   --
101 BEGIN
102 --
103 -- If parameters haven't already been set, then set them
104 --
105   IF p_mthd_action_id IS NULL THEN
106     --
107     -- Called from test harness
108     --
109     g_refresh_start_date   := bis_common_parameters.get_global_start_date;
110     g_refresh_end_date     := hr_general.end_of_time;
111     g_full_refresh         := 'Y';
112     g_concurrent_flag      := 'Y';
113     g_debug_flag           := 'Y';
114     --
115   ELSIF (g_refresh_start_date IS NULL) THEN
116     --
117     g_mthd_action_array   := hri_opl_multi_thread.get_mthd_action_array(p_mthd_action_id);
118     g_refresh_start_date  := g_mthd_action_array.collect_from_date;
119     g_refresh_end_date    := hr_general.end_of_time;
120     g_full_refresh        := g_mthd_action_array.full_refresh_flag;
121     g_concurrent_flag     := 'Y';
122     g_debug_flag          := g_mthd_action_array.debug_flag;
123     --
124   --
125   END IF;
126 --
127 END set_parameters;
128 --
129 -- ----------------------------------------------------------------------------
130 -- PROCESS_RANGE
131 -- Processes actions and inserts data into summary table
132 -- This procedure is executed for every person in a chunk
133 -- ----------------------------------------------------------------------------
134 --
135 PROCEDURE process_range(p_start_object_id   IN NUMBER
136                        ,p_end_object_id     IN NUMBER )
137 IS
138   --
139   -- Variables to populate WHO Columns
140   --
141   l_current_time       DATE;
142   l_user_id            NUMBER;
143   --
144 BEGIN
145   --
146   dbg('Inside process_range');
147   dbg('range ='||p_start_object_id||' - '||p_end_object_id);
148   --
149   l_current_time       := SYSDATE;
150   l_user_id            := fnd_global.user_id;
151   --
152   INSERT INTO HRI_MB_CMNTS_CT (
153      HRI_MB_CMNTS_CT_ID
154     ,EFFECTIVE_START_DATE
155     ,EFFECTIVE_END_DATE
156     ,ASSIGNMENT_ID
157     ,ORGANIZATION_ID
158     ,JOB_ID
159     ,POSITION_ID
160     ,GRADE_ID
161     ,ELEMENT_TYPE_ID
162     ,INPUT_VALUE_ID
163     ,COST_ALLOCATION_KEYFLEX_ID
164     ,COMMITMENT_VALUE
165     ,CURRENCY_CODE
166     --
167     -- WHO Columns
168     --
169     ,last_update_date
170     ,last_update_login
171     ,last_updated_by
172     ,created_by
173     ,creation_date)
174    SELECT hri_mb_cmnts_ct_s.nextval
175          ,pec.commitment_start_date
176          ,pec.commitment_end_date
177          ,pec.assignment_id
178          ,paaf.organization_id
179          ,paaf.job_id
180          ,paaf.position_id
181          ,paaf.grade_id
182          ,pec.element_type_id
183          ,CASE WHEN nvl(pbce.salary_basis_flag,'N') = 'N' THEN pbce.element_input_value_id
184                WHEN nvl(pbce.salary_basis_flag,'N') = 'Y' THEN ppb.input_value_id
185            END  input_value_id
186          ,NULL cost_allocation_keyflex_id
187          ,pec.commitment_amount
188          ,petf.output_currency_code
189          ,SYSDATE
190          ,l_user_id
191          ,l_user_id
192          ,l_user_id
193          ,SYSDATE
194      FROM pqh_element_commitments pec,
195           pqh_bdgt_cmmtmnt_elmnts pbce,
196           per_all_assignments_f   paaf,
197           per_pay_bases           ppb,
198           pay_element_types_f     petf
199     WHERE pec.assignment_id           = paaf.assignment_id
200       AND pec.element_type_id         = petf.element_type_id
201       AND paaf.pay_basis_id           = ppb.pay_basis_id
202       AND pbce.element_type_id        = pec.element_type_id
203       AND pbce.budget_id              = (SELECT budget_id FROM pqh_budget_versions WHERE budget_version_id = pec.budget_version_id)
204       AND pbce.actual_commitment_type IN ('COMMITMENT','BOTH')
205       AND pec.commitment_start_date   BETWEEN petf.effective_start_date AND petf.effective_end_date
206       AND pec.commitment_start_date   BETWEEN paaf.effective_start_date AND paaf.effective_end_date
207       AND pec.assignment_id           BETWEEN p_start_object_id AND p_end_object_id
208       AND pec.commitment_start_date   BETWEEN g_refresh_start_date AND g_refresh_end_date;
209 
210   --
211   dbg(SQL%ROWCOUNT||' commitment records inserted into HRI_MB_CMNTS_CT');
212   dbg('Exiting process');
213   --
214 EXCEPTION
215   WHEN OTHERS THEN
216     --
217     output(sqlerrm);
218     --
219     -- RAISE;
220     --
221 --
222 END process_range;
223 --
224 --
225 -- ----------------------------------------------------------------------------
226 -- PRE_PROCESS
227 -- This procedure includes all the logic required for performing the pre_process
228 -- task of HRI multithreading utility. It drops the indexes and return the SQL
229 -- required for generating the ranges
230 -- ----------------------------------------------------------------------------
231 --
232 PROCEDURE PRE_PROCESS(
233 --
234   p_mthd_action_id              IN             NUMBER,
235   p_sqlstr                                 OUT NOCOPY VARCHAR2) IS
236   --
237   l_dummy1           VARCHAR2(2000);
238   l_dummy2           VARCHAR2(2000);
239   l_schema           VARCHAR2(400);
240 --
241 BEGIN
242 --
243 -- Record the process start
244 --
245   --
246   -- Set up the parameters
247   --
248   set_parameters( p_mthd_action_id => p_mthd_action_id );
249   --
250   -- Disable the WHO trigger
251   --
252   run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_CMNTS_CT_WHO DISABLE');
253   --
254   -- ---------------------------------------------------------------------------
255   --                       Full Refresh Section
256   -- ---------------------------------------------------------------------------
257   --
258   IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
259     --
260     -- If it's a full refresh
261     --
262     IF (g_full_refresh = 'Y') THEN
263       --
264       -- Drop Indexes
265       --
266       hri_utl_ddl.log_and_drop_indexes(
267                         p_application_short_name => 'HRI',
268                         p_table_name    => 'HRI_MB_CMNTS_CT',
269                         p_table_owner   => l_schema);
270       --
271       -- Truncate the table
272       --
273       EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_CMNTS_CT';
274       --
275       -- Select all people with employee assignments in the collection range.
276       -- The bind variable must be present for this sql to work when called
277       -- by PYUGEN, else itwill give error.
278       --
279       p_sqlstr :=
280           'SELECT  /*+ PARALLEL(asgn, DEFAULT, DEFAULT) */
281                    DISTINCT
282                    asgn.assignment_id object_id
283           FROM     per_all_assignments_f   asgn
284           WHERE    asgn.assignment_type in (''E'',''C'')
285           AND      asgn.effective_end_date >= to_date(''' ||
286                        to_char(g_refresh_start_date, 'DD-MM-YYYY') ||
287                        ''',''DD-MM-YYYY'') - 1
288           ORDER BY asgn.assignment_id';
289     --
290     --                    End of Full Refresh Section
291     -- -------------------------------------------------------------------------
292     --
293     -- -------------------------------------------------------------------------
294     --                   Start of Incremental Refresh Section
295     --
296     ELSE
297       --
298       -- Incremental Refresh will be supported later.
299       --
300       NULL;
301       --
302     --
303     --                 End of Incremental Refresh Section
304     -- -------------------------------------------------------------------------
305     --
306     END IF;
307     --
308   END IF;
309   --
310 --
311 END PRE_PROCESS;
312 --
313 -- ----------------------------------------------------------------------------
314 -- PROCESS_RANGE
315 -- This procedure is dynamically the HRI multithreading utility child threads
316 -- for processing the assignment ranges. The procedure invokes the overloaded
317 -- process_range procedure to process the range.
318 -- ----------------------------------------------------------------------------
319 --
320 PROCEDURE process_range(
321    errbuf                          OUT NOCOPY VARCHAR2
322   ,retcode                         OUT NOCOPY NUMBER
323   ,p_mthd_action_id            IN             NUMBER
324   ,p_mthd_range_id             IN             NUMBER
325   ,p_start_object_id           IN             NUMBER
326   ,p_end_object_id             IN             NUMBER)
327 IS
328   --
329   l_error_step        NUMBER;
330   --
331 BEGIN
332   --
333   -- Initialize the global variables
334   --
335   set_parameters(p_mthd_action_id   => p_mthd_action_id
336                 ,p_mthd_range_id    => p_mthd_range_id);
337   --
338   dbg('calling process_range for object range from '||p_start_object_id || ' to '|| p_end_object_id);
339   --
340   -- Depending on the refresh type call the corresponding refresh program
341   --
342   IF g_full_refresh = 'Y' THEN
343     --
344     process_range(p_start_object_id   => p_start_object_id
345                  ,p_end_object_id     => p_end_object_id);
346     --
347   ELSE
348     --
349     -- Incremental Refresh will be supported later.
350     --
351     NULL;
352     --
353   END IF;
354   --
355   errbuf  := 'SUCCESS';
356   retcode := 0;
357 EXCEPTION
358   WHEN others THEN
359    output('Error encountered while processing range ='||p_mthd_range_id );
360    output(sqlerrm);
361    errbuf := SQLERRM;
362    retcode := SQLCODE;
363    --
364    RAISE;
365    --
366 END process_range;
367 --
368 -- ----------------------------------------------------------------------------
369 -- POST_PROCESS
370 -- This procedure is dynamically invoked by the HRI Multithreading utility.
371 -- It finishes the processing by updating the BIS_REFRESH_LOG table
372 -- ----------------------------------------------------------------------------
373 --
374 PROCEDURE post_process (p_mthd_action_id NUMBER) IS
375   --
376   l_dummy1           VARCHAR2(2000);
377   l_dummy2           VARCHAR2(2000);
378   l_schema           VARCHAR2(400);
379   --
380 --
381 BEGIN
382   --
383   dbg('Inside post_process');
384   --
385   set_parameters(p_mthd_action_id);
386   --
387   hri_bpl_conc_log.record_process_start('HRI_OPL_CMNTS');
388   --
389   -- Collect stats for full refresh
390   --
391   IF (g_full_refresh = 'Y') THEN
392     --
393     IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
394       --
395       -- Create indexes
396       --
397       dbg('Full Refresh selected - Creating indexes');
398       --
399       hri_utl_ddl.recreate_indexes(
400                         p_application_short_name => 'HRI',
401                         p_table_name    => 'HRI_MB_CMNTS_CT',
402                         p_table_owner   => l_schema);
403       --
404       -- Collect the statistics only when the process is NOT invoked by a concurrent manager
405       --
406       IF fnd_global.conc_request_id is null THEN
407         --
408         dbg('Full Refresh selected - gathering stats');
409         fnd_stats.gather_table_stats(l_schema,'HRI_MB_CMNTS_CT');
410         --
411       END IF;
412       --
413     END IF;
414   --
415   ELSE
416     --
417     -- Incremental Refresh will be supported later.
418     --
419     NULL;
420     --
421   END IF;
422   --
423   -- Enable the WHO trigger on the fact table
424   --
425   dbg('Enabling the who trigger');
426   run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_CMNTS_CT_WHO ENABLE');
427   --
428   hri_bpl_conc_log.log_process_end(
429      p_status         => TRUE
430     ,p_period_from    => TRUNC(g_refresh_start_date)
431     ,p_period_to      => TRUNC(SYSDATE)
432     ,p_attribute1     => g_full_refresh);
433   --
434   dbg('Exiting post_process');
435   --
436 END post_process;
437 --
438 -- ----------------------------------------------------------------------------
439 -- LOAD_TABLE
440 -- This procedure can be called from the Test harness to populate the table.
441 -- ----------------------------------------------------------------------------
442 --
443 PROCEDURE load_table
444 IS
445   --
446   l_sqlstr     VARCHAR2(4000);
447   --
448   CURSOR c_range_cursor IS
449   SELECT mthd_range_id,
450          min(object_id) start_object_id,
451          max(object_id) end_object_id
452   FROM   (SELECT  hri_opl_multi_thread.get_next_mthd_range_id(rownum,200) mthd_range_id
453                   ,object_id
454           FROM    (SELECT DISTINCT asgn.assignment_id object_id
455                      FROM per_all_assignments_f   asgn
456                     WHERE asgn.assignment_type in ('E','C')
457                       AND asgn.effective_end_date >= g_refresh_start_date - 1
458                     ORDER BY asgn.assignment_id)
459           )
460   GROUP BY mthd_range_id;
461   --
462 BEGIN
463   --
464   dbg('Inside load_table');
465   --
466   -- Call Pre Process
467   --
468   pre_process(p_mthd_action_id             => null,
469               p_sqlstr                     => l_sqlstr);
470   --
471   -- Call Process Range
472   --
473   FOR l_range IN c_range_cursor LOOP
474     --
475     dbg('range ='||l_range.start_object_id|| ' - '||l_range.end_object_id );
476     process_range(p_start_object_id    => l_range.start_object_id
477                  ,p_end_object_id      => l_range.end_object_id);
478     --
479     COMMIT;
480     --
481   END LOOP;
482   --
483   -- Call Post Process
484   --
485   post_process (p_mthd_action_id => null);
486   --
487   dbg('Exiting load_table');
488   --
489 EXCEPTION
490   --
491   WHEN OTHERS THEN
492     --
493     output('Error in load_table = ');
494     output(SQLERRM);
495     RAISE;
496     --
497 END load_table;
498 --
499 END HRI_OPL_CMNTS;