1 PACKAGE EAM_WORKORDER_UTIL_PKG AUTHID CURRENT_USER as
2 /* $Header: EAMWOUTS.pls 120.8.12020000.2 2012/11/06 13:55:14 vpasupur ship $ */
3
4 TYPE t_bom_record IS RECORD (
5 component_sequence_id NUMBER,
6 component_item_id NUMBER,
7 component_item VARCHAR2(80),
8 description VARCHAR2(240),
9 component_quantity NUMBER,
10 component_yield NUMBER,
11 uom VARCHAR2(3),
12 wip_supply_type NUMBER,
13 wip_supply_type_disp VARCHAR2(30),
14 auto_request_material VARCHAR2(1)
15 );
16
17
18 TYPE t_component_record IS RECORD (
19 component_item VARCHAR2(81),
20 component_item_id NUMBER,
21 start_effective_date DATE,
22 operation_sequence_number NUMBER,
23 quantity_per_assembly NUMBER,
24 wip_supply_type NUMBER,
25 supply_subinventory VARCHAR2(30),
26 supply_locator_id NUMBER,
27 supply_locator_name VARCHAR2(81)
28 );
29
30
31 TYPE t_optime_record IS RECORD (
32 operation_seq_num NUMBER,
33 time_shift NUMBER
34 );
35
36 TYPE t_workflow_record IS RECORD (
37 seq_no NUMBER,
38 approver VARCHAR2(360),
39 status VARCHAR2(50),
40 status_date DATE,
41 email VARCHAR2(240),
42 telephone VARCHAR2(60),
43 comments VARCHAR2(4000)
44 );
45
46
47 TYPE t_bom_table IS TABLE OF t_bom_record
48 INDEX BY BINARY_INTEGER;
49 TYPE t_component_table IS TABLE OF t_component_record
50 INDEX BY BINARY_INTEGER;
51 TYPE t_optime_table IS TABLE OF t_optime_record
52 INDEX BY BINARY_INTEGER;
53 TYPE t_workflow_table IS TABLE OF t_workflow_record
54 INDEX BY BINARY_INTEGER;
55
56
57 PROCEDURE retrieve_asset_bom(
58 i_organization_id IN NUMBER,
59 i_asset_number IN VARCHAR2,
60 i_asset_group_id IN NUMBER,
61 p_context IN VARCHAR2, -- stocked inventory or non-stocked inventory
62 o_bom_table OUT NOCOPY t_bom_table,
63 o_error_code OUT NOCOPY NUMBER);
64
65
66 /* o_error_code: 0 success
67 1 partial success(some comp exist)
68 2 failure
69 */
70 PROCEDURE copy_to_bom(
71 i_organization_id IN NUMBER,
72 i_organization_code IN VARCHAR2,
73 i_asset_number IN VARCHAR2,
74 i_asset_group_id IN NUMBER,
75 i_component_table IN t_component_table,
76 o_error_code OUT NOCOPY NUMBER);
77
78
79 /* Adjust Times
80 Different Level
81 */
82 PROCEDURE adjust_resources(i_wip_entity_id IN NUMBER);
83
84 PROCEDURE adjust_operations(
85 i_wip_entity_id IN NUMBER,
86 i_operation_table IN t_optime_table);
87
88 PROCEDURE adjust_workorder(
89 i_wip_entity_id IN NUMBER,
90 i_shift IN NUMBER);
91
92
93 FUNCTION dependency_violated( i_wip_entity_id IN NUMBER)
94 RETURN BOOLEAN;
95
96 /**
97 * This function should be called when turning the status of a work order to
98 * released or on-hold. It will make sure that there is only one such job for
99 * a given asset number(rebuild item)/asset activity association.
100 * For the parameter p_rebuild_flag, you should pass in 'Y' for rebuild job and
101 * 'N' or NULL for normal work orders.
102 * It returns: 0 -- ok
103 * 1 -- there is already one such job so the user can't do it
104 */
105 FUNCTION check_released_onhold_allowed(
106 p_rebuild_flag in varchar2,
107 p_org_id in number,
108 p_item_id in number,
109 p_serial_number in varchar2,
110 p_activity_id in number) RETURN NUMBER;
111
112 /**
113 * For getting responsibility id
114 *
115 */
116 FUNCTION menu_has_function (
117 p_menu_id IN NUMBER,
118 p_function_id IN NUMBER
119 ) RETURN NUMBER;
120
121 FUNCTION get_ip_resp_id (
122 p_user_id IN NUMBER
123 ) RETURN NUMBER;
124
125 --Function to return Responsibility Id for Maint. Super User.
126 --This returns -1 if resp. is not assigned to current user
127 FUNCTION Get_Eam_Resp_Id
128 RETURN NUMBER;
129
130
131 FUNCTION Resource_Schedulable(X_Hour_UOM_Code VARCHAR2,
132 X_Unit_Of_Measure VARCHAR2) RETURN NUMBER ;
133
134
135 PROCEDURE UNRELEASE(x_org_id IN NUMBER,
136 x_wip_id IN NUMBER,
137 x_rep_id IN NUMBER DEFAULT -1,
138 x_line_id IN NUMBER DEFAULT -1,
139 x_ent_type IN NUMBER);
140
141
142 procedure create_default_operation
143 ( p_organization_id IN NUMBER
144 ,p_wip_entity_id IN NUMBER
145 );
146
147 /* bug no 3349197 */
148 PROCEDURE CK_MATERIAL_ALLOC_ON_HOLD(
149 x_org_id IN NUMBER,
150 x_wip_id IN NUMBER,
151 x_rep_id IN NUMBER,
152 x_line_id IN NUMBER,
153 x_ent_type IN NUMBER,
154 x_return_status OUT NOCOPY VARCHAR2
155 );
156
157 --Fix for 3360801.Added the following procedure to show the messages from the api
158 /********************************************************************
159 * Procedure : show_mesg
160 * Purpose : Procedure will concatenate all the messages
161 from the workorder api and return 1 string
162 *********************************************************************/
163 PROCEDURE show_mesg;
164
165 --Fix for 3360801.the following procedure will return a directory to get the log directory path
166 PROCEDURE log_path(
167 x_output_dir OUT NOCOPY VARCHAR2
168 );
169
170 -- Fix for Bug 3489907
171 /*
172 * Procedure :- Check_open_txns
173 * Purpose :- For a given work order,it will return(l_return_status)
174 * 0 for No pending txns
175 * 1 for Open PO/requisitions
176 * 2 for Pending Material Txns
177 * 3 for Pending Operation Txns
178 */
179 Procedure Check_open_txns(p_org_id IN NUMBER,
180 p_wip_id IN NUMBER,
181 p_ent_type IN NUMBER,
182 p_return_status OUT NOCOPY NUMBER,
183 p_return_string OUT NOCOPY VARCHAR2 /* Added for bug#5335940 */);
184 /*
185 * Procedure :- Cancel
186 * Specification:- For a given work order,it will show error
187 * if there are some open po/reqs
188 *
189 *Note:- Unlike Unrelease process, there is no check on prior transactions
190 * i.e. transactions already happened.
191 */
192 PROCEDURE CANCEL(p_org_id IN NUMBER,
193 p_wip_id IN NUMBER,
194 x_return_status OUT NOCOPY NUMBER,
195 x_return_string OUT NOCOPY VARCHAR2 /* Added for bug#5335940 */);
196
197 /* Function to get rebuild description in eam_work_orders_v*/
198
199 FUNCTION get_rebuild_description( p_rebuild_item_id NUMBER, p_organization_id NUMBER)
200 return VARCHAR2;
201
202 PROCEDURE get_workflow_details( p_item_type IN STRING,
203 p_item_key IN STRING,
204 x_workflow_table OUT NOCOPY t_workflow_table);
205
206 PROCEDURE callCostEstimatorSS(
207 p_api_version IN NUMBER := 1.0,
208 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
209 p_commit IN VARCHAR2 := FND_API.G_FALSE,
210 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
211 p_wip_entity_id IN NUMBER,
212 p_organization_id IN NUMBER,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_msg_count OUT NOCOPY NUMBER,
215 x_msg_data OUT NOCOPY VARCHAR2
216 ) ;
217 TYPE REPLACE_REBUILD_REC_TYPE IS RECORD
218 (
219 p_instance_id NUMBER := NULL
220 );
221 TYPE REPLACE_REBUILD_TBL_TYPE IS TABLE OF REPLACE_REBUILD_REC_TYPE INDEX BY BINARY_INTEGER;
222
223 PROCEDURE GET_REPLACED_REBUILDS(
224 p_wip_entity_id IN NUMBER,
225 p_organization_id IN NUMBER,
226 x_replaced_rebuild_tbl OUT NOCOPY REPLACE_REBUILD_TBL_TYPE,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_error_message OUT NOCOPY VARCHAR2
229 );
230
231
232 FUNCTION get_msu_resp_id( p_user_id IN NUMBER) RETURN NUMBER;
233
234
235 END EAM_WORKORDER_UTIL_PKG;