1 PACKAGE hxt_otc_retrieval_interface AUTHID CURRENT_USER AS
2 /* $Header: hxtotcri.pkh 120.4.12020000.3 2013/04/04 15:07:40 asrajago ship $ */
3 TYPE t_field_name IS TABLE OF VARCHAR2 (80)
4 INDEX BY BINARY_INTEGER;
5
6 TYPE t_value IS TABLE OF VARCHAR2 (150)
7 INDEX BY BINARY_INTEGER;
8
9 TYPE t_segment IS TABLE OF VARCHAR2 (60)
10 INDEX BY BINARY_INTEGER;
11
12 -- Bug 12919783
13 -- New type and new global variables, used for marking OTLR details in RDB tables
14 -- correctly.
15 TYPE NUMTABLE IS TABLE OF NUMBER INDEX BY VARCHAR2(22);
16
17 TYPE NUMTAB IS TABLE OF NUMBER;
18
19 g_timid_tab NUMTABLE;
20 g_ovn_tab NUMTABLE;
21 g_retro_tab NUMTABLE;
22
23 g_old_retro_tab NUMTABLE;
24
25
26 -- Bug 8888777
27 -- Added new data type for storing Input Values.
28
29 TYPE IV_record IS RECORD
30 (
31 attribute1 VARCHAR2(400),
32 attribute2 VARCHAR2(400),
33 attribute3 VARCHAR2(400),
34 attribute4 VARCHAR2(400),
35 attribute5 VARCHAR2(400),
36 attribute6 VARCHAR2(400),
37 attribute7 VARCHAR2(400),
38 attribute8 VARCHAR2(400),
39 attribute9 VARCHAR2(400),
40 attribute10 VARCHAR2(400),
41 attribute11 VARCHAR2(400),
42 attribute12 VARCHAR2(400),
43 attribute13 VARCHAR2(400),
44 attribute14 VARCHAR2(400),
45 attribute15 VARCHAR2(400));
46
47
48 -- Bug 9774867
49 -- Changed the index type to VARCHAR2.
50
51 TYPE IV_TABLE IS TABLE OF IV_RECORD INDEX BY VARCHAR2(20);
52
53 g_iv_table IV_TABLE;
54
55
56 -- Bug 7415291
57 -- New data types for handling the re-explosion of timecard.
58 --------------------------------------------------------------------------------
59 -- A record type to handle earning policy id
60 -- and the start and end dates effective on the assignment.
61 TYPE earn_pol_rec IS RECORD
62 ( earn_pol_id NUMBER,
63 start_date DATE,
64 end_date DATE );
65
66 -- Create a table of the above type. Each person would have
67 -- one or more record as above, so this table is used to maintain it.
68
69 TYPE earn_pol_tab IS TABLE OF earn_pol_rec;
70
71 -- Create a record of this above table type.
72
73 TYPE assg_earn_pol_rec IS RECORD
74 (
75 ep_list earn_pol_tab
76 );
77
78 -- Create an associative array of the above type.
79 -- Each element in the array would have a record, whose only member is
80 -- a plsql table of record type earn_pol_rec. Meaning each
81 -- assignment will have its own plsql table of earning policies.
82
83 TYPE earn_pol_assoc_array IS TABLE OF assg_earn_pol_rec INDEX BY VARCHAR2(15);
84
85 -- Create a list of this associative array to be maintained globally.
86
87 g_earn_pol_list earn_pol_assoc_array;
88
89
90 -- Create a table of NUMBERs to store the elements.
91 TYPE element_tab IS TABLE OF NUMBER;
92
93 -- Create a record whose member is the plsql table of numbers.
94 TYPE eg_elements IS RECORD
95 (
96 element_list element_tab
97 );
98
99 -- Create an associative array of this record as above.
100
101 TYPE ep_eg_list IS TABLE OF eg_elements INDEX BY VARCHAR2(15);
102
103 -- Create an object of the above array. Each element in the array would have
104 -- a number type plsql table. Meaning each earning policy id will have its
105 -- own list of elements in the earning group.
106
107 g_earn_group_list ep_eg_list ;
108
109 --------------------------------------------------------------------------------
110
111
112 g_full_name VARCHAR2 (240);
113
114 FUNCTION get_employee_number (
115 p_person_id IN NUMBER,
116 p_effective_date IN DATE
117 )
118 RETURN VARCHAR2;
119
120 PROCEDURE get_assignment_id (
121 p_person_id IN NUMBER,
122 p_payroll_id OUT NOCOPY NUMBER,
123 p_bg_id OUT NOCOPY NUMBER,
124 p_assignment_id OUT NOCOPY NUMBER,
125 p_effective_date IN DATE
126 );
127
128 -- Bug 8888777
129 -- Added new paramete p_bb_id
130
131 PROCEDURE parse_attributes (
132 p_category IN OUT NOCOPY t_field_name,
133 p_field_name IN OUT NOCOPY t_field_name,
134 p_value IN OUT NOCOPY t_value,
135 p_context IN OUT NOCOPY t_field_name,
136 p_date_worked OUT NOCOPY DATE,
137 p_type IN VARCHAR2,
138 p_measure IN NUMBER,
139 p_start_time IN DATE,
140 p_stop_time IN DATE,
141 p_assignment_id IN NUMBER,
142 p_earn_policy OUT NOCOPY VARCHAR2, -- Bug 16598207
143 p_hours OUT NOCOPY NUMBER,
144 p_hours_type OUT NOCOPY VARCHAR2,
145 p_segment OUT NOCOPY t_segment,
146 p_project OUT NOCOPY VARCHAR2,
147 p_task OUT NOCOPY VARCHAR2,
148 p_STATE_NAME OUT NOCOPY VARCHAR2,
149 p_COUNTY_NAME OUT NOCOPY VARCHAR2,
150 p_CITY_NAME OUT NOCOPY VARCHAR2,
151 p_ZIP_CODE OUT NOCOPY VARCHAR2,
152 p_bb_id IN NUMBER DEFAULT 0
153 );
154
155 -- Bug 8888777
156 -- Added new paramete p_bb_id
157
158 PROCEDURE parse_attributes (
159 p_category IN OUT NOCOPY t_field_name,
160 p_field_name IN OUT NOCOPY t_field_name,
161 p_value IN OUT NOCOPY t_value,
162 p_context IN OUT NOCOPY t_field_name,
163 p_date_worked OUT NOCOPY DATE,
164 p_type IN VARCHAR2,
165 p_measure IN NUMBER,
166 p_start_time IN DATE,
167 p_stop_time IN DATE,
168 p_assignment_id IN NUMBER,
169 p_earn_policy OUT NOCOPY VARCHAR2, -- Bug 16598207
170 p_hours OUT NOCOPY NUMBER,
171 p_hours_type OUT NOCOPY VARCHAR2,
172 p_segment OUT NOCOPY t_segment,
173 p_amount OUT NOCOPY NUMBER,
174 p_hourly_rate OUT NOCOPY NUMBER,
175 p_rate_multiple OUT NOCOPY NUMBER,
176 p_project OUT NOCOPY VARCHAR2,
177 p_task OUT NOCOPY VARCHAR2,
178 p_STATE_NAME OUT NOCOPY VARCHAR2,
179 p_COUNTY_NAME OUT NOCOPY VARCHAR2,
180 p_CITY_NAME OUT NOCOPY VARCHAR2,
181 p_ZIP_CODE OUT NOCOPY VARCHAR2,
182 p_bb_id IN NUMBER DEFAULT 0
183 );
184
185 PROCEDURE find_existing_timecard (
186 p_payroll_id IN NUMBER,
187 p_date_worked IN DATE,
188 p_person_id IN NUMBER,
189 p_old_ovn IN NUMBER DEFAULT NULL,
190 p_bb_id IN NUMBER DEFAULT NULL,
191 p_time_summary_id OUT NOCOPY NUMBER,
192 p_time_sum_start_date OUT NOCOPY DATE,
193 p_time_sum_end_date OUT NOCOPY DATE,
194 p_tim_id OUT NOCOPY NUMBER
195 );
196
197 PROCEDURE transfer_to_otm (
198 p_bg_id IN NUMBER,
199 p_incremental IN VARCHAR2 DEFAULT 'Y',
200 p_start_date IN VARCHAR2,
201 p_end_date IN VARCHAR2,
202 p_where_clause IN VARCHAR2,
203 p_transfer_to_bee IN VARCHAR2 DEFAULT 'N',
204 p_retrieval_transaction_code IN VARCHAR2,
205 p_batch_ref IN VARCHAR2,
206 p_no_otm IN OUT NOCOPY VARCHAR2,
207 p_unique_params IN VARCHAR2,
208 p_since_date IN VARCHAR2
209 );
210
211
212 -- Bug 7415291
213 -- New function to check if timecard needs re-explosion
214 -- in case the change is only a delete.
215
216 FUNCTION chk_need_re_explosion (
217 p_assignment_id IN NUMBER,
218 p_date_worked IN DATE,
219 p_element_type_id IN NUMBER )
220 RETURN BOOLEAN ;
221
222
223 END hxt_otc_retrieval_interface;
[Home] [Help]