1 PACKAGE flm_execution_util AUTHID CURRENT_USER AS
2 /* $Header: FLMEXUTS.pls 120.9.12020000.2 2012/07/13 10:56:42 sisankar ship $ */
3
4 FUNCTION get_view_all_schedules(
5 p_organization_id IN NUMBER,
6 p_line_id IN NUMBER,
7 p_operation_id IN NUMBER
8 ) RETURN VARCHAR2;
9
10 FUNCTION get_data_entry_mode(
11 p_organization_id IN NUMBER,
12 p_line_id IN NUMBER,
13 p_operation_id IN NUMBER
14 ) RETURN VARCHAR2;
15
16 FUNCTION view_all_schedules(i_op_seq_id IN NUMBER) RETURN VARCHAR2;
17
18 FUNCTION workstation_enabled(i_op_seq_id IN NUMBER) RETURN VARCHAR2;
19
20 /******************************************************************
21 * To get workstation_enabled flag for given preference by *
22 * (org_id, line_id, operation_id). If the pref. does not exist, *
23 * retrieve it from its upper-leve; if the upper-level does not *
24 * exist, return the default flag 'Y' *
25 ******************************************************************/
26 PROCEDURE get_workstation_enabled(
27 p_organization_id IN NUMBER,
28 p_line_id IN NUMBER,
29 p_operation_id IN NUMBER,
30 p_init_msg_list IN VARCHAR2,
31 x_workstation_enabled OUT NOCOPY VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2
35 );
36
37 /**********************************************
38 * This function checks whether an operation *
39 * of a flow schedule is eligible to be worked*
40 * on and/or completed. *
41 **********************************************/
42 FUNCTION Operation_Eligible(i_org_id number,
43 i_wip_entity_id number,
44 i_std_op_id number) RETURN VARCHAR2;
45
46 FUNCTION operation_eligible_completion(i_org_id number,
47 i_wip_entity_id number,
48 i_std_op_id number) RETURN VARCHAR2;
49
50 FUNCTION operation_eligible_vas_n(
51 i_org_id IN NUMBER,
52 i_wip_entity_id IN NUMBER,
53 i_op_seq_id number) return varchar2;
54
55 PROCEDURE complete_operation(i_org_id number,
56 i_wip_entity_id number,
57 i_op_seq_id number,
58 i_next_op_id number);
59
60
61 G_SUPPLY_TYPE_PHANTOM CONSTANT NUMBER := 6;
62 G_OP_TYPE_LINEOP CONSTANT NUMBER := 3;
63 G_OP_TYPE_EVENT CONSTANT NUMBER := 1;
64 G_INHERIT_PHANTOM_YES NUMBER := 1;
65 G_INHERIT_PHANTOM_NO NUMBER := 2;
66 G_REF_DESIG_SEPARATOR VARCHAR2(1) := ',';
67 G_REF_DESIG_TERMINATOR VARCHAR2(3) := '...';
68 G_REF_DESIG_MAX_COUNT NUMBER := 3;
69 G_BFLUSH_OPTION_ACT_PRI NUMBER := 1;
70 G_BFLUSH_OPTION_ALL NUMBER := 2;
71
72 TYPE FLM_CUST_ATTRIBUTE IS RECORD
73 (
74 ATTRIBUTE_NAME VARCHAR2(240),
75 ATTRIBUTE_VALUE VARCHAR2(2000)
76 );
77
78 TYPE FLM_CUST_ATTRIBUTE_TBL IS TABLE OF FLM_CUST_ATTRIBUTE
79 INDEX BY BINARY_INTEGER;
80
81 /******************************************************************
82 * To get the customized attributes for lineop/event *
83 ******************************************************************/
84 PROCEDURE get_custom_attributes (p_wip_entity_id IN NUMBER,
85 p_op_seq_id IN NUMBER,
86 p_op_type IN NUMBER, --1event,2process,3lineop
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2,
90 x_cust_attrib_tab OUT NOCOPY System.FlmCustomPropRecTab);
91
92
93 /******************************************************************
94 * Public API to get the customized attributes for lineop/event *
95 * User only need to modify this procedure acc to requirments *
96 * *
97 * DESCRIPTION OF PARAMETERS *
98 * *
99 * p_version_number : This stores the version number of the API. *
100 * It is seeded as 1.0. You can use this to *
101 * keep track of the current version. *
102 * p_wip_entity : wip entity id of flow schedule *
103 * p_op_seq_id : op sequence id of this operation/event *
104 * p_op_type : operation type 1=event, 2=process, *
105 * 3=lineop *
106 * p_cust_attrib_tab : table of records to keep attributes *
107 * see definition of FLM_CUST_ATTRIBUTE_TBL *
108 * x_return_status : 'S' for success, 'E' for error *
109 * x_msg_count : total number of messages *
110 * x_msg_data : return messages *
111 * *
112 ******************************************************************/
113 PROCEDURE get_attributes (p_api_version_number IN NUMBER,
114 p_wip_entity_id IN NUMBER,
115 p_op_seq_id IN NUMBER,
116 p_op_type IN NUMBER,
117 p_cust_attrib_tab OUT NOCOPY FLM_CUST_ATTRIBUTE_TBL,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_msg_count OUT NOCOPY NUMBER,
120 x_msg_data OUT NOCOPY VARCHAR2);
121
122 /**********************************************
123 * This function checks whether a component is*
124 * all the way child of any phantom *
125 **********************************************/
126 FUNCTION check_phantom (p_top_bill_seq_id NUMBER,
127 p_explosion_type VARCHAR2,
128 p_org_id IN NUMBER,
129 p_comp_seq_id IN NUMBER,
130 p_sort_order IN VARCHAR2) RETURN NUMBER;
131
132 /**********************************************
133 * This function finds out the current rev for*
134 * a given component. Used by workstation *
135 * components tab *
136 **********************************************/
137 FUNCTION get_current_rev (p_org_id NUMBER,
138 p_component_item_id NUMBER) RETURN VARCHAR2;
139
140
141 /**********************************************
142 * This function returns the string of *
143 * reference designators for a component *
144 **********************************************/
145 FUNCTION get_reference_designator(p_comp_seq_id NUMBER) RETURN VARCHAR2;
146
147
148 /**********************************************
149 * This procedure calls the pick release api *
150 * and return the pass/fail status *
151 * return_status = 'S' for success *
152 * return_status = 'F' for fail *
153 **********************************************/
154 procedure pick_release(p_wip_entity_id NUMBER,
155 p_org_id NUMBER,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_data OUT NOCOPY VARCHAR2);
158
159
160 /****************************************************
161 * This function finds out if the current move *
162 * is within from primary path or from feeder line *
163 * return_status = 'Y' for feeder move *
164 * return_status = 'N' for primary path move *
165 ***************************************************/
166 function is_move_from_feeder(p_from_op_seq_id NUMBER,
167 p_to_op_seq_id NUMBER) return VARCHAR2;
168
169
170
171 procedure generate_serial_to_record(p_org_id IN NUMBER,
172 p_wip_entity_id IN NUMBER,
173 p_primary_item_id IN NUMBER,
174 p_gen_qty IN NUMBER,
175 x_ret_code OUT NOCOPY VARCHAR2,
176 x_msg_buf OUT NOCOPY VARCHAR2);
177
178
179
180 PROCEDURE generate_lot_to_record (p_org_id IN NUMBER,
181 p_primary_item_id IN NUMBER,
182 o_lot_number OUT NOCOPY VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2);
186
187 TYPE operation_seq_tbl_type IS TABLE OF NUMBER
188 INDEX BY BINARY_INTEGER;
189
190 PROCEDURE get_eligible_ops (p_org_id IN NUMBER,
191 p_line_id IN NUMBER,
192 p_rtg_seq_id IN NUMBER,
193 p_wip_entity_id IN NUMBER,
194 x_lop_tbl OUT NOCOPY operation_seq_tbl_type);
195
196 FUNCTION get_backflush_option(p_org_id IN NUMBER, p_line_id IN NUMBER) RETURN NUMBER;
197
198
199 PROCEDURE get_backflush_comps(
200 p_wip_ent_id in number default NULL,
201 p_line_id in number default NULL,
202 p_assyID in number,
203 p_orgID in number,
204 p_qty in number,
205 p_altBomDesig in varchar2,
206 p_altOption in number,
207 p_bomRevDate in date default NULL,
208 p_txnDate in date,
209 p_projectID in number,
210 p_taskID in number,
211 p_toOpSeqNum in number,
212 p_altRoutDesig in varchar2,
213 x_compInfo in out nocopy system.wip_lot_serial_obj_t,
214 x_returnStatus out nocopy varchar2);
215
216
217 PROCEDURE default_comp_lot_serials(
218 p_wip_ent_id in number default NULL,
219 p_line_id in number default NULL,
220 p_assyID in number,
221 p_orgID in number,
222 p_qty in number,
223 p_altBomDesig in varchar2,
224 p_altOption in number,
225 p_bomRevDate in date default NULL,
226 p_txnDate in date,
227 p_projectID in number,
228 p_taskID in number,
229 p_toOpSeqNum in number,
230 p_altRoutDesig in varchar2,
231 x_compTbl in out nocopy system.wip_lot_serial_obj_t,
232 x_returnStatus out nocopy varchar2);
233
234 PROCEDURE merge_backflush_comps(
235 p_wip_ent_id in number default NULL,
236 p_line_id in number default NULL,
237 p_assyID in number,
238 p_orgID in number,
239 p_qty in number,
240 p_altBomDesig in varchar2,
241 p_altOption in number,
242 p_bomRevDate in date default NULL,
243 p_txnDate in date,
244 p_projectID in number,
245 p_taskID in number,
246 p_toOpSeqNum in number,
247 p_rtg_seq_id in number,
248 x_compTbl in out nocopy system.wip_lot_serial_obj_t,
249 x_returnStatus out nocopy varchar2);
250
251
252 FUNCTION scheduleRecordedDetailsExist(orgId Number, wipEntId Number)
253 return VARCHAR2;
254
255 FUNCTION scheduleRecordedDetailsExist(orgId Number, schNum Varchar2)
256 return VARCHAR2;
257
258 FUNCTION kanban_card_activity_exist(p_wip_entity_id IN NUMBER)
259 RETURN NUMBER;
260
261 PROCEDURE exp_ser_single_op(p_org_id IN NUMBER, p_wip_entity_id NUMBER,
262 p_operation_seq_num NUMBER);
263
264
265 PROCEDURE exp_ser_single_item(p_org_id IN NUMBER, p_wip_entity_id NUMBER,
266 p_operation_seq_num NUMBER, p_inventory_item_id NUMBER);
267
268 PROCEDURE exp_ser_single_range(p_org_id IN NUMBER, p_wip_entity_id NUMBER,
269 p_operation_seq_num NUMBER, p_inventory_item_id NUMBER, p_fm_serial VARCHAR2,
270 p_to_serial VARCHAR2, p_parent_serial_number VARCHAR2, p_lot_number VARCHAR2);
271
272 FUNCTION get_single_assy_ser(p_org_id IN NUMBER, p_inv_item_id IN NUMBER)
273 RETURN VARCHAR2;
274
275 FUNCTION get_single_assy_lot(p_org_id IN NUMBER, p_inv_item_id IN NUMBER)
276 RETURN VARCHAR2;
277
278 FUNCTION get_txn_bfcomp_cnt(txn_intf_id NUMBER)
279 RETURN NUMBER;
280
281 FUNCTION get_ser_range_cnt(p_fm_serial VARCHAR2, p_to_serial VARCHAR2)
282 RETURN NUMBER;
283
284 FUNCTION non_txncomp_exist(p_wip_entity_id IN NUMBER, p_org_id IN NUMBER)
285 RETURN NUMBER;
286
287 /*Added for bugfix 6152984 */
288 /****************************************************
289 * This function finds out if any event is *
290 * attached to the operation seq based on passed *
291 * std op in the routing for this schedule *
292 * return_status = 'Y' for One or more Event Exist *
293 * return_status = 'N' for No event exist *
294 ***************************************************/
295 function event_exist(p_org_id NUMBER,
296 p_wip_entity_id NUMBER,
297 p_std_op_id NUMBER) return VARCHAR2;
298
299
300 END flm_execution_util;
301
302
303
304
305
[Home] [Help]