[Home] [Help]
PACKAGE: APPS.WIP_WS_DL_UTIL
Source
1 package WIP_WS_DL_UTIL as
2 /* $Header: wipwsdls.pls 120.5.12010000.2 2008/10/23 09:45:54 rajaiswa 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_required in varchar2 default null --Bug -7364131
150 );
151
152 procedure build_dispatch_list_order_by
153 (
154 p_resp_key varchar2,
155 p_org_id number,
156 p_dept_id number,
157 x_order_by_columns in out nocopy varchar2,
158 x_order_by_clause in out nocopy varchar2
159 );
160
161 procedure build_dispatch_list_where
162 (
163 p_resp_key varchar2,
164 p_org_id number,
165 p_dept_id number,
166 p_resource_id number,
167 p_instance_assigned number,
168 p_instance_id number,
169 p_serial_number varchar2,
170 p_list_mode number,
171 p_from_date date,
172 p_to_date date,
173 p_job_type number,
174 p_component_id number,
175 p_bind_number number,
176 x_where_clause in out nocopy varchar2,
177 x_bind_variables in out nocopy varchar2,
178 x_required in varchar2 --Bug -7364131
179 );
180
181 procedure expedite
182 (
183 p_wip_entity_id number,
184 p_op_seq_num number,
185 x_status in out nocopy varchar2,
186 x_msg_count in out nocopy number,
187 x_msg in out nocopy number
188 );
189
190 procedure unexpedite
191 (
192 p_wip_entity_id number,
193 p_op_seq_num number,
194 x_status in out nocopy varchar2,
195 x_msg_count in out nocopy number,
196 x_msg in out nocopy number
197 );
198
199 procedure batch_move_add(
200 p_index number,
201 p_wip_entity_id number,
202 p_wip_entity_name varchar2,
203 p_op_seq varchar2,
204 p_move_qty number,
205 p_scrap_qty number,
206 p_assy_serial varchar2 default null,
207 x_return_status out nocopy varchar2
208 );
209
210 procedure batch_move_process
211 (
212 p_resp_key varchar2,
213 p_org_id number,
214 p_dept_id number,
215 p_employee_id number,
216 x_return_status out nocopy varchar2
217 );
218
219 function get_shift_capacity
220 (
221 p_org_id number,
222 p_dept_id number,
223 p_resource_id number,
224 p_shift_seq number,
225 p_shift_num number
226 ) return number;
227
228 function get_cap_num_ns_jobs
229 (
230 p_resp_key varchar2,
231 p_org_id number,
232 p_department_id number,
233 p_resource_id number,
234 p_shift_num number,
235 p_from_date date,
236 p_to_date date
237 ) return number;
238
239 function get_cap_resource_avail
240 (
241 p_org_id number,
242 p_department_id number,
243 p_resource_id number,
244 p_shift_num number
245 ) return number;
246
247 function get_cap_resource_required
248 (
249 p_resp_key varchar2,
250 p_org_id number,
251 p_department_id number,
252 p_resource_id number,
253 p_shift_num number,
254 p_from_date date,
255 p_to_date date
256
257 ) return number;
258
259 function is_jobop_completed
260 (
261 p_resp_key varchar2,
262 p_wip_entity_id number,
263 p_op_seq number
264 ) return varchar2;
265
266 end WIP_WS_DL_UTIL;
267