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