1 PACKAGE WIP_WS_TIME_ENTRY AUTHID CURRENT_USER AS
2 /* $Header: wipwstes.pls 120.12.12020000.6 2013/01/07 14:23:44 sjallipa ship $ */
3
4 -- Insert a record into the wip_resource_actual_times table.
5 PROCEDURE record_insert(
6 p_time_entry_id in number,
7 p_organization_id in number,
8 p_wip_entity_id in number,
9 p_operation_seq_num in number,
10 p_resource_id in number,
11 p_resource_seq_num in number,
12 p_instance_id in number,
13 p_serial_number in varchar2,
14 p_last_update_date in date,
15 p_last_updated_by in number,
16 p_creation_date in date,
17 p_created_by in number,
18 p_last_update_login in number,
19 p_object_version_num in number,
20 p_time_entry_mode in number,
21 p_cost_flag in varchar2,
22 p_add_to_rtg in varchar2,
23 p_status_type in number,
24 p_start_date in date,
25 p_end_date in date,
26 p_projected_completion_date in date,
27 p_duration in number,
28 p_uom_code in varchar2,
29 p_employee_id in number,
30 x_time_entry_id out NOCOPY number,
31 x_return_status out NOCOPY varchar2);
32
33 -- Update a record in the wip_resource_actual_times table.
34 PROCEDURE record_update(
35 p_time_entry_id in number,
36 p_organization_id in number,
37 p_wip_entity_id in number,
38 p_operation_seq_num in number,
39 p_resource_id in number,
40 p_resource_seq_num in number,
41 p_instance_id in number,
42 p_serial_number in varchar2,
43 p_last_update_date in date,
44 p_last_updated_by in number,
45 p_creation_date in date,
46 p_created_by in number,
47 p_last_update_login in number,
48 p_object_version_num in number,
49 p_time_entry_mode in number,
50 p_cost_flag in varchar2,
51 p_add_to_rtg in varchar2,
52 p_status_type in number,
53 p_start_date in date,
54 p_end_date in date,
55 p_projected_completion_date in date,
56 p_duration in number,
57 p_uom_code in varchar2,
58 p_employee_id in number,
59 x_return_status out NOCOPY varchar2);
60
61 -- Delete a record from the wip_resource_actual_times table.
62 PROCEDURE record_delete(
63 p_time_entry_id in number,
64 p_object_version_num in number,
65 x_return_status out NOCOPY varchar2);
66
67 -- Delete a record from the wip_resource_actual_times table.
68 PROCEDURE record_delete(
69 p_wip_entity_id in number,
70 p_operation_seq_num in number,
71 p_employee_id in number,
72 x_return_status out NOCOPY varchar2);
73
74 -- Process records on report resource usages page.
75 PROCEDURE process_time_records_resource(p_organization_id in number);
76
77 -- Process records on report my time page.
78 PROCEDURE process_time_records_my_time(p_organization_id in number,
79 p_instance_id IN NUMBER);
80 -- Bug 14288080
81 -- Process records on report my time page for logged in employee.
82 PROCEDURE process_time_my_time_for_emp(p_organization_id in number,
83 p_emp_id IN NUMBER);
84 -- Process records on move page.
85 PROCEDURE process_time_records_move(p_wip_entity_id IN NUMBER,
86 p_from_op IN NUMBER,
87 p_to_op IN NUMBER);
88
89 -- Process records on report job operation page.
90 PROCEDURE process_time_records_job_op(p_wip_entity_id IN NUMBER,
91 p_operation_seq_num IN NUMBER,
92 p_instance_id IN NUMBER);
93
94 PROCEDURE process_time_records(p_wip_entity_id IN NUMBER,
95 p_completed_op IN NUMBER,
96 p_instance_id IN NUMBER,
97 p_time_entry_source IN VARCHAR2);
98
99 -- Is UOM time based?
100 FUNCTION is_time_uom(p_uom_code IN VARCHAR2) return VARCHAR2;
101
102 -- Get the value for time entry mode.
103 FUNCTION get_time_entry_mode(p_wip_entity_id IN NUMBER,
104 p_operation_seq_num IN NUMBER) return NUMBER;
105
106 -- Get the value for cost_flag.
107 FUNCTION get_cost_flag(p_wip_entity_id IN NUMBER,
108 p_operation_seq_num IN NUMBER,
109 p_resource_seq_num IN NUMBER,
110 p_time_entry_source IN VARCHAR2) return VARCHAR2;
111
112 -- Get the value for add_to_rtg.
113 FUNCTION get_add_to_rtg_flag(p_wip_entity_id IN NUMBER,
114 p_operation_seq_num IN NUMBER,
115 p_resource_seq_num IN NUMBER,
116 p_cost_flag IN VARCHAR2,
117 p_time_entry_source IN VARCHAR2) return VARCHAR2;
118
119 -- Get Organization Id and Department Id.
120 PROCEDURE get_org_dept_ids(p_wip_entity_id IN NUMBER,
121 p_operation_seq_num IN NUMBER,
122 x_organization_id out NOCOPY NUMBER,
123 x_department_id out NOCOPY NUMBER);
124
125 -- Update the value of actual start date in wdj, wo and wor tables
126 PROCEDURE update_actual_start_dates(p_wip_entity_id IN NUMBER,
127 p_operation_seq_num IN NUMBER,
128 p_resource_seq_num IN NUMBER);
129
130 -- Update the value of actual completion date in wo and wor tables
131 PROCEDURE update_actual_completion_dates(p_wip_entity_id IN NUMBER,
132 p_operation_seq_num IN NUMBER,
133 p_resource_seq_num IN NUMBER);
134
135 -- Update the value of projected completion date in wo and wor tables.
136 PROCEDURE update_proj_completion_dates(p_organization_id IN NUMBER,
137 p_wip_entity_id IN NUMBER,
138 p_operation_seq_num IN NUMBER,
139 p_resource_seq_num IN NUMBER,
140 p_resource_id IN NUMBER,
141 p_start_date IN DATE);
142
143 -- Get the on/off status of the job.
144 FUNCTION get_job_on_off_status(p_wip_entity_id IN NUMBER,
145 p_operation_seq_num IN NUMBER) return VARCHAR2;
146
147 -- Bug 15945996 Get the job on/off status of all the opeations of given job
148 FUNCTION is_job_on(p_wip_entity_id IN NUMBER) return VARCHAR2;
149
150 -- Set job on.
151 PROCEDURE job_on(p_wip_entity_id IN NUMBER,
152 p_operation_seq_num IN NUMBER,
153 p_employee_id IN NUMBER,
154 x_status out NOCOPY VARCHAR2,
155 x_msg_count out NOCOPY NUMBER,
156 x_msg out NOCOPY VARCHAR2);
157
158 -- Set job off.
159 PROCEDURE job_off(p_wip_entity_id IN NUMBER,
160 p_operation_seq_num IN NUMBER,
161 x_status out NOCOPY VARCHAR2,
162 x_msg_count out NOCOPY NUMBER,
163 x_msg out NOCOPY VARCHAR2);
164
165 -- Set clock in.
166 PROCEDURE clock_in(p_wip_entity_id IN NUMBER,
167 p_operation_seq_num IN NUMBER,
168 p_responsibility_key IN VARCHAR2,
169 p_dept_id IN NUMBER,
170 p_employee_id IN NUMBER,
171 p_instance_id IN NUMBER,
172 p_resource_id IN NUMBER,
173 p_resource_seq_num IN NUMBER,
174 x_status out NOCOPY VARCHAR2,
175 x_msg_count out NOCOPY NUMBER,
176 x_msg out NOCOPY VARCHAR2);
177
178 -- Set clock out.
179 PROCEDURE clock_out(p_wip_entity_id IN NUMBER,
180 p_operation_seq_num IN NUMBER,
181 p_responsibility_key IN VARCHAR2,
182 p_dept_id IN NUMBER,
183 p_employee_id IN NUMBER,
184 p_instance_id IN NUMBER,
185 p_resource_id IN NUMBER,
186 p_resource_seq_num IN NUMBER,
187 x_status out NOCOPY VARCHAR2,
188 x_msg_count out NOCOPY NUMBER,
189 x_msg out NOCOPY VARCHAR2);
190
191 -- Set undo clock in.
192 PROCEDURE undo_clock_in(p_wip_entity_id IN NUMBER,
193 p_operation_seq_num IN NUMBER,
194 p_responsibility_key IN VARCHAR2,
195 p_dept_id IN NUMBER,
196 p_employee_id IN NUMBER,
197 p_instance_id IN NUMBER,
198 p_resource_id IN NUMBER,
199 p_resource_seq_num IN NUMBER,
200 x_status out NOCOPY VARCHAR2,
201 x_msg_count out NOCOPY NUMBER,
202 x_msg out NOCOPY VARCHAR2);
203 --Set Shift In
204 --Bug12747250 Change Employee_ID type to varchar because badgeNumber can be alphanumberic
205 PROCEDURE shift_in(p_wip_employee_id IN VARCHAR2,
206 p_org_id IN NUMBER,
207 x_status OUT nocopy VARCHAR2
208 );
209 --Set Shift Out
210 --Bug12747250 Change Employee_ID type to varchar because badgeNumber can be alphanumberic
211 PROCEDURE shift_out(p_wip_employee_id IN VARCHAR2,
212 p_org_id IN NUMBER,
213 x_status out NOCOPY VARCHAR2
214 );
215
216 --Set Undo Shift In
217 --Bug12747250 Change Employee_ID type to varchar because badgeNumber can be alphanumberic
218 PROCEDURE undo_shift_in(p_wip_employee_id IN VARCHAR2,
219 p_org_id IN NUMBER,
220 x_status out NOCOPY VARCHAR2
221 );
222 --User Mode Shift functionality
223
224 --Set Shift In
225 PROCEDURE shift_in_UM(p_wip_employee_id IN NUMBER,
226 p_org_id IN NUMBER,
227 x_status OUT nocopy VARCHAR2
228 );
229 --Set Shift Out
230 PROCEDURE shift_out_UM(p_wip_employee_id IN NUMBER,
231 p_org_id IN NUMBER,
232 x_status out NOCOPY VARCHAR2
233 );
234
235 --Set Undo Shift In
236 PROCEDURE undo_shift_in_UM(p_wip_employee_id IN NUMBER,
237 p_org_id IN NUMBER,
238 x_status out NOCOPY VARCHAR2
239 );
240
241
242
243 -- Get last operation quantity.
244 FUNCTION get_last_op_qty(p_wip_entity_id IN NUMBER,
245 p_operation_seq_num IN NUMBER) return NUMBER;
246
247 -- Get last job quantity.
248 FUNCTION get_last_job_qty(p_wip_entity_id IN NUMBER,
249 p_operation_seq_num IN NUMBER) return NUMBER;
250
251 -- Get the instance id.
252 FUNCTION get_instance_id(p_org_id IN NUMBER,
253 p_employee_id IN NUMBER) return NUMBER;
254
255 -- Check pending clockouts.
256 FUNCTION is_clock_pending(p_wip_entity_id IN NUMBER,
257 p_operation_seq_num IN NUMBER) return VARCHAR2;
258 -- Bug12747250 Badge Number can be alphanumeric
259 FUNCTION is_emp_clock_out_pending(p_employee_number IN VARCHAR2,
260 p_organization_id IN NUMBER,
261 p_user_mode IN VARCHAR2) return NUMBER;
262 --Add for Bug 10097774
263 FUNCTION get_actual_resource_rate(p_org_id IN NUMBER,
264 p_emp_id IN NUMBER,
265 p_txn_date IN DATE) return NUMBER;
266 --Add for Bug 10097774
267 PROCEDURE derive_insert_wcti(p_organization_id in number,
268 p_wip_entity_id in number,
269 p_operation_seq_num in number,
270 p_resource_seq_num in number,
271 p_resource_id in number,
272 p_entry_id in number,
273 p_employee_id in number,
274 p_duration in number,
275 p_txn_uom in varchar2,
276 p_hour_uom in varchar2,
277 x_return_status out NOCOPY varchar2);
278
279 /*ER 12961989, Arrow Enhancement
280 get total number of jobs that have been clocked in
281 */
282 FUNCTION get_total_cl_jobs(
283 p_emp_id in number,
284 p_org_id in number) return number;
285
286 /*ER 12961989, Arrow Enhancement
287 get currently clocked in job
288 */
289 PROCEDURE get_cur_clocked_job(
290 p_emp_id in number,
291 p_org_id in number,
292 x_entity_id out NOCOPY NUMBER,
293 x_op_seq out NOCOPY NUMBER,
294 x_start_date out NOCOPY DATE,
295 x_ret_status out NOCOPY varchar2);
296
297 END WIP_WS_TIME_ENTRY;