1 PACKAGE pay_events_wrapper AUTHID DEFINER AS
2 /* $Header: pyevtwrp.pkh 115.10 2003/07/08 05:41:58 exjones noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 2002 Oracle Corporation UK |
6 | Thames Valley Park, Reading, UK |
7 | All rights reserved. |
8 +======================================================================+
9 File Name : pyevtwrp.pkh
10
11 Description : A wrapper on top of the Payroll Events Model interpreter
12 for use with the summarisation collection programs which
13 are used to increase the performance of the Daily Business
14 Intelligence portlet queries.
15
16 Change History
17 --------------
18 Name Date Version Bug Text
19 -------------- ----------- ------- ------- -----------------------------
20 Ed Jones 31-May-2002 115.0 Initial (stub) version
21 Ed Jones 31-May-2002 115.1 Added dbdrv lines
22 Ed Jones 31-May-2002 115.2 Corrected invalid dbdrv lines
23 Ed Jones 14-Jun-2002 115.3 Replaced stubs with proper
24 code
25 Ed Jones 02-Jul-2002 115.4 Added refresh period start
26 and end dates to the
27 get_refresh_periods routine
28 Ed Jones 23-Jul-2002 115.5 Add debugging mode and allow
29 messages to be sent to conc.
30 manager log file
31 Ed Jones 12-Aug-2002 115.6 Add new exception for when no
32 supervisor changes are found
33 Ed Jones 27-Mar-2003 115.7 2870801 Changes to cater for date
34 track updates to supervisor
35 as well as corrections
36 Ed Jones 02-Jun-2003 115.8 2984406 Removed event group name as
37 a parameter to internal modules.
38 Global ID is used instead.
39 Ed Jones 07-Jul-2003 115.9 Move various cursors to be visible
40 at package level, for use by
41 diagnostics routines.
42 Made dt update SQL building function
43 accesible for this reason too.
44 3033981 Add element_entry_id as parameter
45 to get_event_details and to
46 assignment table type.
47 Ed Jones 07-Jul-2003 115.10 Remove 'show errors' for gscc
48 ========================================================================
49 */
50 --
51 -- < CONSTANTS > ---------------------------------------------------------
52 --
53 -- Global constant so that the supervisor_id which indicates
54 -- "all supervisors" can be referenced consistently
55 c_ALL_SUPERVISORS_ID CONSTANT NUMBER(15) := -1;
56 c_BLANK_LOCATION_ID CONSTANT NUMBER(15) := -1;
57 --
58 -- < CUSTOM TYPE DEFINITIONS > -------------------------------------------
59 --
60 -- Table of records to return the time periods, by supervisor, which need
61 -- refreshing
62 TYPE t_summary_refresh_tab_rec IS RECORD(
63 supervisor_id per_all_assignments_f.supervisor_id%type,
64 effective_start_date DATE,
65 effective_end_date DATE,
66 location_id per_all_assignments_f.location_id%type
67 );
68 TYPE t_summary_refresh_tab_type IS
69 TABLE OF t_summary_refresh_tab_rec
70 INDEX BY BINARY_INTEGER;
71 --
72 -- Simple table to record a list of assignment ID and effective dates
73 TYPE t_assignment_id_tab_rec IS RECORD(
74 assignment_id per_all_assignments_f.assignment_id%TYPE,
75 element_entry_id pay_element_entries_f.element_entry_id%TYPE,
76 effective_start_date DATE,
77 effective_end_date DATE
78 );
79 --
80 TYPE t_assignment_id_tab_type IS
81 TABLE OF t_assignment_id_tab_rec
82 INDEX BY BINARY_INTEGER;
83 --
84 -- < CURSORS > -----------------------------------------------------------
85 --
86 -- Return types for packaged cursors and references
87 TYPE csr_return IS RECORD (
88 assignment_or_supervisor_id NUMBER(9),
89 table_or_location_id NUMBER(9),
90 generic_surrogate_key VARCHAR2(2000),
91 effective_start_date DATE,
92 effective_end_date DATE
93 );
94 TYPE csr_dyn_ref IS REF CURSOR;
95 --
96 CURSOR csr_inserts_deletes(p_evt IN NUMBER,p_st IN DATE,p_en IN DATE) RETURN csr_return;
97 CURSOR csr_dt_corrections(p_evt IN NUMBER,p_st IN DATE,p_en IN DATE) RETURN csr_return;
98 CURSOR csr_supv_corrections(cp_evt IN NUMBER,cp_st IN DATE,cp_en IN DATE,cp_str IN VARCHAR2) RETURN csr_return;
99 CURSOR csr_table_list(p_evt IN NUMBER) RETURN csr_return;
100 --
101 -- < EXCEPTIONS > --------------------------------------------------------
102 --
103 -- User defined exception to indicate various fatal errors
104 feature_not_supported EXCEPTION;
105 mismatch_when_summarizing EXCEPTION;
106 event_group_not_found EXCEPTION;
107 no_assignments_supplied EXCEPTION;
108 dated_table_cache_miss EXCEPTION;
109 dated_table_cache_empty EXCEPTION;
110 no_assignment_events_found EXCEPTION;
111 missing_dates_in_all_record EXCEPTION;
112 missing_dates_for_specific EXCEPTION;
113 no_supervisor_corrections EXCEPTION;
114 --
115 -- < MAIN TOP LEVEL PROCEDURE > -------------------------------------------
116 --
117 -- This is the procedure that normal developers should call.
118 -- =========================================================
119 -- Pass in the event group name, the start and end date of the desired
120 -- refresh period (in real-time, not effective dates, e.g. the date the
121 -- last refresh was run and the current system date)
122 --
123 -- It will pass back a list of supervisors and the date range across which
124 -- each of those supervisors should be refreshed.
125 --
126 -- If there is a record passed back with a supervisor_id set to
127 -- c_ALL_SUPERVISORS_ID then you should refresh your summary for all
128 -- supervisors between the dates specified in that record.
129 --
130 -- A record like this will be passed back when some event occurrs on a
131 -- table from which we cannot get back to a supervisor_id, e.g. the
132 -- Workforce Measurement Value base metric (or whatever it's called these
133 -- days), and it indicates that something's happened in this date range
134 -- which affects the summary data values for all supervisors.
135 --
136 -- You can safely run through all records and process them discretely
137 -- since any overlaps of specific supervisors with this "refresh all"
138 -- time period will be removed before the summary refresh details are
139 -- passed back.
140 PROCEDURE get_summaries_affected(
141 p_event_group IN VARCHAR2,
142 p_start_date IN DATE,
143 p_end_date IN DATE,
144 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
145 p_location_stripe IN BOOLEAN DEFAULT FALSE,
146 p_raise_no_data IN BOOLEAN DEFAULT FALSE
147 );
148 --
149 -- < FUNCTIONS > ---------------------------------------------------------
150 --
151 -- Return the "all supervisors" id, for use in SQL statements
152 FUNCTION all_supervisors_id RETURN NUMBER;
153 PRAGMA RESTRICT_REFERENCES(all_supervisors_id,WNDS,RNDS);
154 --
155 -- Return the "blank location" id, for use in SQL statements
156 FUNCTION blank_location_id RETURN NUMBER;
157 PRAGMA RESTRICT_REFERENCES(blank_location_id,WNDS,RNDS);
158 --
159 -- Get the event group ID based on its name
160 FUNCTION get_event_group_id(p_event_group_name IN VARCHAR2) RETURN NUMBER;
161 --
162 -- Return the ID of the element entries table
163 FUNCTION get_element_entry_table_id RETURN NUMBER;
164 --
165 -- Build the SQL statement to get date track update information
166 FUNCTION build_csr_dt_updates(p_dtid IN NUMBER,p_dtname IN VARCHAR2,p_eeid IN NUMBER) RETURN VARCHAR2;
167 --
168 -- Initialise the event group cache
169 PROCEDURE init_event_group_cache(p_event_group_name IN VARCHAR2);
170 --
171 -- Get the elapsed time of the last run, in seconds to the nearest 100th
172 FUNCTION get_elapsed_time RETURN NUMBER;
173 FUNCTION get_elapsed_time_text RETURN VARCHAR2;
174 --
175 -- Functions to simplify looping over the last returned table of refresh records
176 FUNCTION next_record RETURN BOOLEAN;
177 FUNCTION current_record RETURN NUMBER;
178 --
179 -- < PROCEDURES > --------------------------------------------------------
180 --
181 -- Switch on or off client debugging.
182 -- !! Stubbed out, since client debugging (i.e. dbms_output) isn't allowed !!
183 PROCEDURE set_client_debugging(p_on IN BOOLEAN);
184 --
185 -- Replacement for the above - allow logging to concurrent manager log
186 -- files (else hr_utility.trace) and switch debugging messages on
187 PROCEDURE set_concurrent_logging(p_on IN BOOLEAN);
188 PROCEDURE set_debugging(p_on IN BOOLEAN);
189 --
190 -- Write out a message, either by fnd_file.put_line, or hr_utility.trace
191 -- depending on what's passed to set_concurrent_logging, dbg only writes
192 -- a message if you've passed TRUE to set_debugging
193 PROCEDURE msg(p_text IN VARCHAR2);
194 PROCEDURE dbg(p_text IN VARCHAR2);
195 --
196 -- Get a list of the assignments that have events recorded for them.
197 -- * See note 1
198 PROCEDURE get_assignments_affected(
199 p_start_date IN DATE,
200 p_end_date IN DATE,
201 p_assignments IN OUT NOCOPY t_assignment_id_tab_type
202 );
203 --
204 -- Get the refresh summary data based on a list of assignment IDs
205 -- * See note 1
206 PROCEDURE get_refresh_periods(
207 p_assignments IN OUT NOCOPY t_assignment_id_tab_type,
208 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
209 p_start_date IN DATE,
210 p_end_date IN DATE,
211 p_location_stripe IN BOOLEAN DEFAULT FALSE
212 );
213 --
214 -- Remove specific supervisor refresh records (or the portions of them)
215 -- that overlap the "all" refresh period
216 -- * See note 1
217 PROCEDURE de_dupe_refresh_periods(
218 p_summary_refresh_temp IN OUT NOCOPY t_summary_refresh_tab_type,
219 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
220 p_all_supv IN OUT NOCOPY BOOLEAN,
221 p_out_num IN OUT NOCOPY NUMBER,
222 p_all_start IN OUT NOCOPY DATE,
223 p_all_end IN OUT NOCOPY DATE,
224 p_want_location IN BOOLEAN DEFAULT FALSE
225 );
226 --
227 -- Get the payroll event details based on an assignment ID
228 -- * See note 1
229 PROCEDURE get_event_details(
230 p_start_date IN DATE,
231 p_end_date IN DATE,
232 p_assignment_id IN NUMBER,
233 p_element_entry_id IN NUMBER,
234 p_detailed_output IN OUT NOCOPY pay_interpreter_pkg.t_detailed_output_table_type,
235 p_proration_dates IN OUT NOCOPY pay_interpreter_pkg.t_proration_dates_table_type
236 );
237 --
241 p_detailed_output IN pay_interpreter_pkg.t_detailed_output_table_type,
238 -- Process the detailed information that get_event_details returned
239 -- * See note 1
240 PROCEDURE process_event_details(
242 p_proration_dates IN pay_interpreter_pkg.t_proration_dates_table_type,
243 p_summary_refresh IN OUT NOCOPY t_summary_refresh_tab_type,
244 p_location_stripe IN BOOLEAN DEFAULT FALSE
245 );
246 --
247 -- < NOTES > -------------------------------------------------------------
248 --
249 -- Note 1:
250 -- These are really a internal procedures and shouldn't be called by a
251 -- normal developer. They're exposed here for debugging purposes and in
252 -- case anyone needs to get more detailed information about the events
253 --
254 END pay_events_wrapper;
[Home] [Help]