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