[Home] [Help]
PACKAGE: APPS.WIP_WS_DL_UTIL
Source
1 package WIP_WS_DL_UTIL AUTHID CURRENT_USER as
2 /* $Header: wipwsdls.pls 120.7.12020000.2 2012/07/18 18:17:52 ankohli ship $ */
3
4 LIST_MODE_SCHEDULED constant number := 1;
5 LIST_MODE_CURRENT constant number := 2;
6 LIST_MODE_UPSTREAM constant number := 3;
7
8 WIP_WS_DEFAULT_DL_TYPE constant number := 8;
9
10 WP_JOB_STATUS constant number := 16;
11 WP_DL_ORDERING_CRITERIA constant number := 12;
12
13 WP_READY_STATUS_CRITERIA constant number := 21;
14 WP_READY_STATUS_JOB_STATUS constant number := 1;
15 WP_READY_STATUS_EXCEPTIONS constant number := 2;
16 WP_READY_STATUS_COMP_AVAIL constant number := 3;
17 WP_READY_STATUS_SF_STATUS constant number := 4;
18 WP_READY_STATUS_QTY constant number := 5;
19
20 WP_INCLUDE_COMPLETE_QTY constant number := 15;
21 WP_VALUE_YES constant varchar2(1) := '1';
22 WP_VALUE_DIRECTION_DOWN constant varchar2(1) := '2';
23
24 l_move_table wip_batch_move.move_table;
25
26 procedure get_first_calendar_date
27 (
28 l_cal_code varchar2,
29 p_date date,
30 x_seq out nocopy number,
31 x_start_date out nocopy date,
32 x_end_date out nocopy date
33 );
34
35 function get_first_shift_id
36 (
37 p_org_id number,
38 p_dept_id number,
39 p_resource_id number
40 ) return varchar2;
41
42 procedure get_first_dept_resource_shift
43 (
44 p_cal_code varchar2,
45 p_dept_id number,
46 p_resource_id number,
47 p_date date,
48 x_shift_seq out nocopy number,
49 x_shift_num out nocopy number,
50 x_shift_start_date out nocopy date,
51 x_shift_end_date out nocopy date
52 );
53
54 procedure get_first_shift
55 (
56 p_cal_code varchar2,
57 p_dept_id number,
58 p_resource_id number,
59 p_date date,
60 x_shift_seq out nocopy number,
61 x_shift_num out nocopy number,
62 x_shift_start_date out nocopy date,
63 x_shift_end_date out nocopy date
64 );
65
66 function get_col_job_on_name(p_employee_id number) return varchar2;
67
68 function get_col_total_prior_qty(p_wip_entity_id number, p_op_seq number) return number;
69
70 function get_col_customer(p_org_id number, p_wip_entity_id number) return varchar2;
71
72 function get_col_sales_order(p_org_id number, p_wip_entity_id number) return varchar2;
73
74 function get_col_shift_id
75 (
76 p_org_id number,
77 p_dept_id number,
78 p_resource_id number,
79 p_op_date date,
80 p_expedited varchar2,
81 p_first_shift_id varchar2,
82 p_first_shift_end_date date
83 ) return varchar2;
84
85 function get_col_exception(p_wip_entity_id number, p_op_seq number) return varchar;
86
87 function get_col_project(p_wip_entity_id number) return varchar;
88
89 function get_col_task(p_wip_entity_id number) return varchar;
90
91 function get_col_resource_setup(p_wip_entity_id number, p_op_seq number) return varchar;
92
93 function get_col_res_usage_req
94 (
95 p_wip_entity_id number,
96 p_op_seq number,
97 p_dept_id number,
98 p_resource_id number,
99 p_resource_seq_num number
100 ) return number;
101
102 function get_col_component_uom(p_org_id number, p_comp_id number) return varchar2;
103
104 function get_col_component_usage
105 (
106 p_org_id number,
107 p_wip_entity_id number,
108 p_op_seq number,
109 p_comp_id number
110 ) return number;
111
112
113 function get_col_ready_status
114 (
115 p_resp_key varchar2,
116 p_org_id number,
117 p_dept_id number,
118 p_wip_entity_id number,
119 p_op_seq_num number
120 ) return varchar2;
121
122 function get_jobop_queue_run_qty(p_wip_entity_id number, p_op_seq_num number) return number;
123
124 function get_jobop_shopfloor_status(p_wip_entity_id number, p_op_seq_num number) return varchar2;
125
126 function get_jobop_num_exceptions(p_wip_entity_id number, p_op_seq_num number) return number;
127
128 function get_job_released_status(p_wip_entity_id number) return varchar2;
129
130 procedure build_dispatch_list_sql
131 (
132 p_resp_key varchar2,
133 p_org_id number,
134 p_dept_id number,
135 p_resource_id number,
136 p_instance_option number,
137 p_instance_id number,
138 p_serial_number varchar2,
139 p_list_mode number,
140 p_from_date date,
141 p_to_date date,
142 p_job_type number,
143 p_component_id number,
144 p_bind_number number,
145 x_where_clause in out nocopy varchar2,
146 x_bind_variables in out nocopy varchar2,
147 x_order_by_columns in out nocopy varchar2,
148 x_order_by_clause in out nocopy varchar2,
149 x_bind_var_num in out nocopy number, --Bug 12800454
150 x_required in varchar2 default null --Bug -7364131
151
152 );
153
154 procedure build_dispatch_list_order_by
155 (
156 p_resp_key varchar2,
157 p_org_id number,
158 p_dept_id number,
159 x_order_by_columns in out nocopy varchar2,
160 x_order_by_clause in out nocopy varchar2
161 );
162
163 procedure build_dispatch_list_where
164 (
165 p_resp_key varchar2,
166 p_org_id number,
167 p_dept_id number,
168 p_resource_id number,
169 p_instance_assigned number,
170 p_instance_id number,
171 p_serial_number varchar2,
172 p_list_mode number,
173 p_from_date date,
174 p_to_date date,
175 p_job_type number,
176 p_component_id number,
177 p_bind_number number,
178 x_where_clause in out nocopy varchar2,
179 x_bind_variables in out nocopy varchar2,
180 x_bind_var_num in out nocopy number, --Bug 12800454
181 x_required in varchar2 --Bug -7364131
182
183 );
184
185 procedure expedite
186 (
187 p_wip_entity_id number,
188 p_op_seq_num number,
189 x_status in out nocopy varchar2,
190 x_msg_count in out nocopy number,
191 x_msg in out nocopy number
192 );
193
194 procedure unexpedite
195 (
196 p_wip_entity_id number,
197 p_op_seq_num number,
198 x_status in out nocopy varchar2,
199 x_msg_count in out nocopy number,
200 x_msg in out nocopy number
201 );
202
203 procedure batch_move_add(
204 p_index number,
205 p_wip_entity_id number,
206 p_wip_entity_name varchar2,
207 p_op_seq varchar2,
208 p_move_qty number,
209 p_scrap_qty number,
210 p_assy_serial varchar2 default null,
211 x_return_status out nocopy varchar2
212 );
213
214 procedure batch_move_process
215 (
216 p_resp_key varchar2,
217 p_org_id number,
218 p_dept_id number,
219 p_employee_id number,
220 x_return_status out nocopy varchar2
221 );
222
223 function get_shift_capacity
224 (
225 p_org_id number,
226 p_dept_id number,
227 p_resource_id number,
228 p_shift_seq number,
229 p_shift_num number
230 ) return number;
231
232 function get_cap_num_ns_jobs
233 (
234 p_resp_key varchar2,
235 p_org_id number,
236 p_department_id number,
237 p_resource_id number,
238 p_shift_num number,
239 p_from_date date,
240 p_to_date date
241 ) return number;
242
243 function get_cap_resource_avail
244 (
245 p_org_id number,
246 p_department_id number,
247 p_resource_id number,
248 p_shift_num number,
249 p_from_date date -- Fix bug 9392379
250 ) return number;
251
252 function get_cap_resource_required
253 (
254 p_resp_key varchar2,
255 p_org_id number,
256 p_department_id number,
257 p_resource_id number,
258 p_shift_num number,
259 p_from_date date,
260 p_to_date date
261
262 ) return number;
263
264 function is_jobop_completed
265 (
266 p_resp_key varchar2,
267 p_wip_entity_id number,
268 p_op_seq number
269 ) return varchar2;
270
271 end WIP_WS_DL_UTIL;