[Home] [Help]
PACKAGE BODY: APPS.EAM_REQUIREMENTS_PKG
Source
1 PACKAGE BODY EAM_REQUIREMENTS_PKG as
2 /* $Header: EAMMRTHB.pls 120.3 2005/10/06 01:41:50 mmaduska noship $ */
3
4
5 PROCEDURE Pre_Insert IS
6 BEGIN
7 null;
8 END Pre_Insert;
9
10
11 PROCEDURE Insert_Row (X_row_id IN OUT NOCOPY VARCHAR2,
12 X_inventory_item_id IN NUMBER,
13 X_organization_id IN NUMBER,
14 X_wip_entity_id IN NUMBER,
15 X_operation_seq_num IN NUMBER,
16 X_repetitive_schedule_id IN NUMBER,
17 X_last_update_date IN DATE,
18 X_last_updated_by IN NUMBER,
19 X_creation_date IN DATE,
20 X_created_by IN NUMBER,
21 X_last_update_login IN NUMBER,
22 X_department_id IN NUMBER,
23 X_wip_supply_type IN NUMBER,
24 X_date_required IN DATE,
25 X_required_quantity IN NUMBER,
26 X_quantity_issued IN NUMBER,
27 X_quantity_per_assembly IN NUMBER,
28 X_comments IN VARCHAR2,
29 X_supply_subinventory IN VARCHAR2,
30 X_supply_locator_id IN NUMBER,
31 X_mrp_net_flag IN NUMBER,
32 X_mps_required_quantity IN NUMBER,
33 X_mps_date_required IN DATE,
34 X_attribute_category IN VARCHAR2,
35 X_attribute1 IN VARCHAR2,
36 X_attribute2 IN VARCHAR2,
37 X_attribute3 IN VARCHAR2,
38 X_attribute4 IN VARCHAR2,
39 X_attribute5 IN VARCHAR2,
40 X_attribute6 IN VARCHAR2,
41 X_attribute7 IN VARCHAR2,
42 X_attribute8 IN VARCHAR2,
43 X_attribute9 IN VARCHAR2,
44 X_attribute10 IN VARCHAR2,
45 X_attribute11 IN VARCHAR2,
46 X_attribute12 IN VARCHAR2,
47 X_attribute13 IN VARCHAR2,
48 X_attribute14 IN VARCHAR2,
49 X_attribute15 IN VARCHAR2,
50 X_auto_request_material IN VARCHAR2,
51 X_L_EAM_MAT_REC OUT NOCOPY EAM_PROCESS_WO_PUB.eam_mat_req_rec_type,
52 X_material_shortage_flag OUT NOCOPY VARCHAR2,
53 X_material_shortage_check_date OUT NOCOPY DATE
54 ) IS
55
56 l_return_status VARCHAR2(30) := '';
57 l_msg_count NUMBER := 0;
58 l_msg_data VARCHAR2(2000) := '';
59 l_status_type NUMBER;
60 l_material_issue_by_mo VARCHAR2(1);
61 l_output_dir VARCHAR2(512);
62 l_error_message VARCHAR2(1000);
63
64 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type ;
65 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
66
67 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
68 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
69 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
70 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
71 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
72 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
73 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
74 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
75 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
76 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
77 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
78 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
79 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
80 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
81 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
82 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
83
84 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
85 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
86 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
87 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
88 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
89 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
90 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
91 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
92 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
93 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
94 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
95 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
96 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
97 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
98 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
99 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
100 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
101
102 CURSOR C IS
103 SELECT rowid
104 FROM WIP_REQUIREMENT_OPERATIONS
105 WHERE inventory_item_id = X_inventory_item_id
106 AND organization_id = X_organization_id
107 AND wip_entity_id = X_wip_entity_id
108 AND operation_seq_num = X_operation_seq_num;
109 BEGIN
110
111 /* get output directory path from database */
112 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
113
114
115 l_eam_mat_req_rec.header_id := X_wip_entity_id;
116 l_eam_mat_req_rec.batch_id := 1;
117 l_eam_mat_req_rec.wip_entity_id := X_wip_entity_id;
118 l_eam_mat_req_rec.organization_id := X_organization_id;
119 l_eam_mat_req_rec.operation_seq_num := X_operation_seq_num;
120 l_eam_mat_req_rec.inventory_item_id := X_inventory_item_id;
121 l_eam_mat_req_rec.quantity_per_assembly := X_quantity_per_assembly;
122 l_eam_mat_req_rec.department_id := X_department_id;
123 l_eam_mat_req_rec.wip_supply_type := X_wip_supply_type;
124 l_eam_mat_req_rec.date_required := X_date_required;
125 l_eam_mat_req_rec.required_quantity := X_required_quantity;
126 l_eam_mat_req_rec.quantity_issued := X_quantity_issued;
127 l_eam_mat_req_rec.supply_subinventory := X_supply_subinventory;
128 l_eam_mat_req_rec.supply_locator_id := X_supply_locator_id;
129 l_eam_mat_req_rec.mrp_net_flag := X_mrp_net_flag;
130 l_eam_mat_req_rec.mps_required_quantity := X_mps_required_quantity;
131 l_eam_mat_req_rec.mps_date_required := X_mps_date_required;
132 l_eam_mat_req_rec.component_sequence_id := null;
133 l_eam_mat_req_rec.comments := X_comments;
134 l_eam_mat_req_rec.attribute_category := X_attribute_category;
135 l_eam_mat_req_rec.attribute1 := X_attribute1;
136 l_eam_mat_req_rec.attribute2 := X_attribute2;
137 l_eam_mat_req_rec.attribute3 := X_attribute3;
138 l_eam_mat_req_rec.attribute4 := X_attribute4;
139 l_eam_mat_req_rec.attribute5 := X_attribute5;
140 l_eam_mat_req_rec.attribute6 := X_attribute6;
141 l_eam_mat_req_rec.attribute7 := X_attribute7;
142 l_eam_mat_req_rec.attribute8 := X_attribute8;
143 l_eam_mat_req_rec.attribute9 := X_attribute9;
144 l_eam_mat_req_rec.attribute10 := X_attribute10;
145 l_eam_mat_req_rec.attribute11 := X_attribute11;
146 l_eam_mat_req_rec.attribute12 := X_attribute12;
147 l_eam_mat_req_rec.attribute13 := X_attribute13;
148 l_eam_mat_req_rec.attribute14 := X_attribute14;
149 l_eam_mat_req_rec.attribute15 := X_attribute15;
150 l_eam_mat_req_rec.auto_request_material := X_auto_request_material;
151 l_eam_mat_req_rec.suggested_vendor_name := null;
152 l_eam_mat_req_rec.vendor_id := null;
153 l_eam_mat_req_rec.unit_price := null;
154 l_eam_mat_req_rec.request_id := null;
155 l_eam_mat_req_rec.program_application_id := null;
156 l_eam_mat_req_rec.program_id := null;
157 l_eam_mat_req_rec.program_update_date := sysdate;
158 l_eam_mat_req_rec.return_status := null;
159 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
160
161 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
162
163 begin
164 EAM_PROCESS_WO_PUB.Process_WO
165 ( p_bo_identifier => 'EAM'
166 , p_init_msg_list => TRUE
167 , p_api_version_number => 1.0
168 , p_commit => 'N'
169 , p_eam_wo_rec => l_eam_wo_rec
170 , p_eam_op_tbl => l_eam_op_tbl
171 , p_eam_op_network_tbl => l_eam_op_network_tbl
172 , p_eam_res_tbl => l_eam_res_tbl
173 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
174 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
175 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
176 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
177 , p_eam_direct_items_tbl => l_eam_di_tbl
178 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
179 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
180 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
181 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
182 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
183 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
184 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
185 , p_eam_request_tbl => l_eam_request_tbl
186 , x_eam_wo_rec => l_out_eam_wo_rec
187 , x_eam_op_tbl => l_out_eam_op_tbl
188 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
189 , x_eam_res_tbl => l_out_eam_res_tbl
190 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
191 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
192 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
193 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
194 , x_eam_direct_items_tbl => l_out_eam_di_tbl
195 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
196 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
197 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
198 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
199 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
200 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
201 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
202 , x_eam_request_tbl => l_out_eam_request_tbl
203 , x_return_status => l_return_status
204 , x_msg_count => l_msg_count
205 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
206 , p_debug_filename => 'insertmrthb.log'
207 , p_output_dir => l_output_dir
208 , p_debug_file_mode => 'w'
209 );
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 EAM_WORKORDER_UTIL_PKG.show_mesg;
214 APP_EXCEPTION.RAISE_EXCEPTION;
215 END;
216
217 IF (l_return_status = 'S') THEN
218 X_L_EAM_MAT_REC := l_out_eam_mat_req_tbl(1);
219 x_material_shortage_flag := l_out_eam_wo_rec.material_shortage_flag;
220 x_material_shortage_check_date := l_out_eam_wo_rec.material_shortage_check_date;
221 ELSE
222 EAM_WORKORDER_UTIL_PKG.show_mesg;
223 APP_EXCEPTION.RAISE_EXCEPTION;
224 END IF;
225
226
227 OPEN C;
228 FETCH C INTO X_row_id;
229 IF (C%NOTFOUND) THEN
230 CLOSE C;
231 RAISE NO_DATA_FOUND;
232 END IF;
233 CLOSE C;
234
235 END Insert_Row;
236
237
238
239 PROCEDURE Update_Row (X_row_id IN VARCHAR2,
240 X_inventory_item_id IN NUMBER,
241 X_organization_id IN NUMBER,
242 X_wip_entity_id IN NUMBER,
243 X_operation_seq_num IN NUMBER,
244 X_repetitive_schedule_id IN NUMBER,
245 X_last_update_date IN DATE,
246 X_last_updated_by IN NUMBER,
247 X_last_update_login IN NUMBER,
248 X_department_id IN NUMBER,
249 X_wip_supply_type IN NUMBER,
250 X_date_required IN DATE,
251 X_required_quantity IN NUMBER,
252 X_quantity_issued IN NUMBER,
253 X_quantity_per_assembly IN NUMBER,
254 X_comments IN VARCHAR2,
255 X_supply_subinventory IN VARCHAR2,
256 X_supply_locator_id IN NUMBER,
257 X_mrp_net_flag IN NUMBER,
258 X_mps_required_quantity IN NUMBER,
259 X_mps_date_required IN DATE,
260 X_attribute_category IN VARCHAR2,
261 X_attribute1 IN VARCHAR2,
262 X_attribute2 IN VARCHAR2,
263 X_attribute3 IN VARCHAR2,
264 X_attribute4 IN VARCHAR2,
265 X_attribute5 IN VARCHAR2,
266 X_attribute6 IN VARCHAR2,
267 X_attribute7 IN VARCHAR2,
268 X_attribute8 IN VARCHAR2,
269 X_attribute9 IN VARCHAR2,
270 X_attribute10 IN VARCHAR2,
271 X_attribute11 IN VARCHAR2,
272 X_attribute12 IN VARCHAR2,
273 X_attribute13 IN VARCHAR2,
274 X_attribute14 IN VARCHAR2,
275 X_attribute15 IN VARCHAR2,
276 X_auto_request_material IN VARCHAR2,
277 X_L_EAM_MAT_REC OUT NOCOPY EAM_PROCESS_WO_PUB.eam_mat_req_rec_type,
278 X_material_shortage_flag OUT NOCOPY VARCHAR2,
279 X_material_shortage_check_date OUT NOCOPY DATE
280 )IS
281
282 l_return_status VARCHAR2(30) := '';
283 l_msg_count NUMBER := 0;
284 l_msg_data VARCHAR2(2000) := '';
285 l_req_qty NUMBER := 0;
286 l_status_type NUMBER;
287 l_material_issue_by_mo VARCHAR2(1);
288
289 l_output_dir VARCHAR2(512);
290 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type ;
291 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
292
293 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
294 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
295 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
296 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
297 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
298 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
299 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
300 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
301 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
302 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
303 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
304 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
305 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
306 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
307 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
308 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
309
310 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
311 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
312 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
313 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
314 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
315 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
316 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
317 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
318 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
319 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
320 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
321 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
322 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
323 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
324 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
325 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
326 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
327
328 BEGIN
329 /* get output directory path from database */
330 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
331
332
333
334 l_eam_mat_req_rec.header_id := X_wip_entity_id;
335 l_eam_mat_req_rec.batch_id := 1;
336 l_eam_mat_req_rec.wip_entity_id := X_wip_entity_id;
337 l_eam_mat_req_rec.organization_id := X_organization_id;
338 l_eam_mat_req_rec.operation_seq_num := X_operation_seq_num;
339 l_eam_mat_req_rec.inventory_item_id := X_inventory_item_id;
340 l_eam_mat_req_rec.quantity_per_assembly := X_quantity_per_assembly;
341 l_eam_mat_req_rec.department_id := X_department_id;
342 l_eam_mat_req_rec.wip_supply_type := X_wip_supply_type;
343 l_eam_mat_req_rec.date_required := X_date_required;
344 l_eam_mat_req_rec.required_quantity := X_required_quantity;
345 l_eam_mat_req_rec.quantity_issued := X_quantity_issued;
346 l_eam_mat_req_rec.supply_subinventory := X_supply_subinventory;
347 l_eam_mat_req_rec.supply_locator_id := X_supply_locator_id;
348 l_eam_mat_req_rec.mrp_net_flag := X_mrp_net_flag;
349 l_eam_mat_req_rec.mps_required_quantity := X_mps_required_quantity;
350 l_eam_mat_req_rec.mps_date_required := X_mps_date_required;
351 l_eam_mat_req_rec.component_sequence_id := null;
352 l_eam_mat_req_rec.comments := X_comments;
353 l_eam_mat_req_rec.attribute_category := X_attribute_category;
354 l_eam_mat_req_rec.attribute1 := X_attribute1;
355 l_eam_mat_req_rec.attribute2 := X_attribute2;
356 l_eam_mat_req_rec.attribute3 := X_attribute3;
357 l_eam_mat_req_rec.attribute4 := X_attribute4;
358 l_eam_mat_req_rec.attribute5 := X_attribute5;
359 l_eam_mat_req_rec.attribute6 := X_attribute6;
360 l_eam_mat_req_rec.attribute7 := X_attribute7;
361 l_eam_mat_req_rec.attribute8 := X_attribute8;
362 l_eam_mat_req_rec.attribute9 := X_attribute9;
363 l_eam_mat_req_rec.attribute10 := X_attribute10;
364 l_eam_mat_req_rec.attribute11 := X_attribute11;
365 l_eam_mat_req_rec.attribute12 := X_attribute12;
366 l_eam_mat_req_rec.attribute13 := X_attribute13;
367 l_eam_mat_req_rec.attribute14 := X_attribute14;
368 l_eam_mat_req_rec.attribute15 := X_attribute15;
369 l_eam_mat_req_rec.auto_request_material := X_auto_request_material;
370 l_eam_mat_req_rec.suggested_vendor_name := null;
371 l_eam_mat_req_rec.vendor_id := null;
372 l_eam_mat_req_rec.unit_price := null;
373 l_eam_mat_req_rec.request_id := null;
374 l_eam_mat_req_rec.program_application_id := null;
375 l_eam_mat_req_rec.program_id := null;
376 l_eam_mat_req_rec.program_update_date := sysdate;
377 l_eam_mat_req_rec.return_status := null;
378 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
379
380 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
381 EAM_PROCESS_WO_PUB.Process_WO
382 ( p_bo_identifier => 'EAM'
383 , p_init_msg_list => TRUE
384 , p_api_version_number => 1.0
385 , p_commit => 'N'
386 , p_eam_wo_rec => l_eam_wo_rec
387 , p_eam_op_tbl => l_eam_op_tbl
388 , p_eam_op_network_tbl => l_eam_op_network_tbl
389 , p_eam_res_tbl => l_eam_res_tbl
390 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
391 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
392 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
393 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
394 , p_eam_direct_items_tbl => l_eam_di_tbl
395 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
396 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
397 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
398 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
399 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
400 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
401 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
402 , p_eam_request_tbl => l_eam_request_tbl
403 , x_eam_wo_rec => l_out_eam_wo_rec
404 , x_eam_op_tbl => l_out_eam_op_tbl
405 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
406 , x_eam_res_tbl => l_out_eam_res_tbl
407 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
408 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
409 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
410 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
411 , x_eam_direct_items_tbl => l_out_eam_di_tbl
412 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
413 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
414 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
415 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
416 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
417 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
418 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
419 , x_eam_request_tbl => l_out_eam_request_tbl
420 , x_return_status => l_return_status
421 , x_msg_count => l_msg_count
422 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
423 , p_debug_filename => 'updatemrthb.log'
424 , p_output_dir => l_output_dir
425 , p_debug_file_mode => 'w'
426 );
427 IF (l_return_status = 'S') THEN
428 X_L_EAM_MAT_REC := l_out_eam_mat_req_tbl(1);
429 x_material_shortage_flag := l_out_eam_wo_rec.material_shortage_flag;
430 x_material_shortage_check_date := l_out_eam_wo_rec.material_shortage_check_date;
431 ELSE
432 EAM_WORKORDER_UTIL_PKG.show_mesg;
433 APP_EXCEPTION.RAISE_EXCEPTION;
434 END IF;
435
436
437 END Update_Row;
438
439
440 PROCEDURE Lock_Row( X_row_id IN VARCHAR2,
441 X_inventory_item_id IN NUMBER,
442 X_organization_id IN NUMBER,
443 X_wip_entity_id IN NUMBER,
444 X_operation_seq_num IN NUMBER,
445 X_department_id IN NUMBER,
446 X_wip_supply_type IN NUMBER,
447 X_date_required IN DATE,
448 X_required_quantity IN NUMBER,
449 X_quantity_issued IN NUMBER,
450 X_quantity_per_assembly IN NUMBER,
451 X_comments IN VARCHAR2,
452 X_supply_subinventory IN VARCHAR2,
453 X_supply_locator_id IN NUMBER,
454 X_mrp_net_flag IN NUMBER,
455 X_attribute_category IN VARCHAR2,
456 X_attribute1 IN VARCHAR2,
457 X_attribute2 IN VARCHAR2,
458 X_attribute3 IN VARCHAR2,
459 X_attribute4 IN VARCHAR2,
460 X_attribute5 IN VARCHAR2,
461 X_attribute6 IN VARCHAR2,
462 X_attribute7 IN VARCHAR2,
463 X_attribute8 IN VARCHAR2,
464 X_attribute9 IN VARCHAR2,
465 X_attribute10 IN VARCHAR2,
466 X_attribute11 IN VARCHAR2,
467 X_attribute12 IN VARCHAR2,
468 X_attribute13 IN VARCHAR2,
469 X_attribute14 IN VARCHAR2,
470 X_attribute15 IN VARCHAR2,
471 X_auto_request_material IN VARCHAR2) IS
472 CURSOR C IS
473 SELECT *
474 FROM WIP_REQUIREMENT_OPERATIONS
475 WHERE rowid = X_row_id
476 FOR UPDATE of Wip_Entity_Id NOWAIT;
477 Recinfo C%ROWTYPE;
478 BEGIN
479 OPEN C;
480 FETCH C INTO Recinfo;
481 IF (C%NOTFOUND) THEN
482 CLOSE C;
483 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
484 FND_MESSAGE.RAISE_ERROR;
485 APP_EXCEPTION.RAISE_EXCEPTION;
486 END IF;
487 CLOSE C;
488
489 IF (Recinfo.inventory_item_id = X_Inventory_Item_Id)
490 AND (Recinfo.organization_id = X_Organization_Id)
491 AND (Recinfo.wip_entity_id = X_Wip_Entity_Id)
492 AND (Recinfo.operation_seq_num = X_Operation_Seq_Num)
493 AND (nvl(Recinfo.department_id, -1) =
494 nvl(X_Department_Id, -1))
495 AND (nvl(Recinfo.wip_supply_type, -1) =
496 nvl(X_Wip_Supply_Type, -1))
497 AND (Recinfo.date_required = X_Date_Required OR
498 (Recinfo.date_required is NULL AND
499 X_Date_Required is NULL))
500 AND (Recinfo.required_quantity = X_Required_Quantity)
501 AND (Recinfo.quantity_issued = X_Quantity_Issued OR
502 (Recinfo.quantity_issued = 0 AND
503 X_Quantity_Issued IS NULL))
504 AND (Recinfo.quantity_per_assembly = X_Quantity_Per_Assembly)
505 AND (nvl(Recinfo.comments, 'xxxx') =
506 nvl(X_Comments, 'xxxx'))
507 AND (nvl(Recinfo.supply_subinventory, 'xxxx') =
508 nvl(X_Supply_Subinventory, 'xxxx'))
509 AND (nvl(Recinfo.supply_locator_id, -1) =
510 nvl(X_Supply_Locator_Id, -1))
511 AND (Recinfo.mrp_net_flag = X_Mrp_Net_Flag)
512 AND (nvl(Recinfo.attribute_category, 'xxxx') =
513 nvl(X_Attribute_Category, 'xxxx'))
514 AND (nvl(Recinfo.attribute1, 'xxxx') =
515 nvl(X_Attribute1, 'xxxx'))
516 AND (nvl(Recinfo.attribute2, 'xxxx') =
517 nvl(X_Attribute2, 'xxxx'))
518 AND (nvl(Recinfo.attribute3, 'xxxx') =
519 nvl(X_Attribute3, 'xxxx'))
520 AND (nvl(Recinfo.attribute4, 'xxxx') =
521 nvl(X_Attribute4, 'xxxx'))
522 AND (nvl(Recinfo.attribute5, 'xxxx') =
523 nvl(X_Attribute5, 'xxxx'))
524 AND (nvl(Recinfo.attribute6, 'xxxx') =
525 nvl(X_Attribute6, 'xxxx'))
526 AND (nvl(Recinfo.attribute7, 'xxxx') =
527 nvl(X_Attribute7, 'xxxx'))
528 AND (nvl(Recinfo.attribute8, 'xxxx') =
529 nvl(X_Attribute8, 'xxxx'))
530 AND (nvl(Recinfo.attribute9, 'xxxx') =
531 nvl(X_Attribute9, 'xxxx'))
532 AND (nvl(Recinfo.attribute10, 'xxxx') =
533 nvl(X_Attribute10, 'xxxx'))
534 AND (nvl(Recinfo.attribute11, 'xxxx') =
535 nvl(X_Attribute11, 'xxxx'))
536 AND (nvl(Recinfo.attribute12, 'xxxx') =
537 nvl(X_Attribute12, 'xxxx'))
538 AND (nvl(Recinfo.attribute13, 'xxxx') =
539 nvl(X_Attribute13, 'xxxx'))
540 AND (nvl(Recinfo.attribute14, 'xxxx') =
541 nvl(X_Attribute14, 'xxxx'))
542 AND (nvl(Recinfo.attribute15, 'xxxx') =
543 nvl(X_Attribute15, 'xxxx'))
544 AND (nvl(Recinfo.auto_request_material, 'T') =
545 nvl(X_auto_request_material, 'T'))
546 THEN
547 RETURN;
548 ELSE
549 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
550 FND_MESSAGE.RAISE_ERROR;
551 APP_EXCEPTION.RAISE_EXCEPTION;
552 END IF;
553 END Lock_Row;
554
555
556 PROCEDURE Delete_Row(X_row_id IN VARCHAR2,
557 X_material_shortage_flag OUT NOCOPY VARCHAR2,
558 X_material_shortage_check_date OUT NOCOPY DATE) IS
559
560 l_wip_entity_id NUMBER := 0;
561 l_org_id NUMBER := 0;
562 l_return_status VARCHAR2(30) := '';
563 l_msg_count NUMBER := 0;
564 l_msg_data VARCHAR2(30) := '';
565 l_operation_seq_num NUMBER;
566 l_inventory_item_id NUMBER;
567 l_wip_entity_type NUMBER;
568 l_status_type NUMBER;
569 l_material_issue_by_mo VARCHAR2(1);
570 l_output_dir VARCHAR2(512);
571
572 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type ;
573 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
574 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
575 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
576 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
577 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
578 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
579 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
580 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
581 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
582 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
583 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
584 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
585 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
586 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
587 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
588 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
589 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
590
591 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
592 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
593 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
594 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
595 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
596 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
597 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
598 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
599 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
600 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
601 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
602 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
603 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
604 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
605 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
606 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
607 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
608
609
610 BEGIN
611 /* get output directory path from database */
612 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
613
614
615 SELECT wip_entity_id, organization_id, operation_seq_num, inventory_item_id
616 INTO l_wip_entity_id, l_org_id, l_operation_seq_num, l_inventory_item_id
617 FROM WIP_REQUIREMENT_OPERATIONS
618 WHERE rowid = X_row_id;
619
620 l_eam_mat_req_rec.header_id := l_wip_entity_id;
621 l_eam_mat_req_rec.batch_id := 1;
622 l_eam_mat_req_rec.row_id := null;
623 l_eam_mat_req_rec.wip_entity_id := l_wip_entity_id;
624 l_eam_mat_req_rec.organization_id := l_org_id;
625 l_eam_mat_req_rec.operation_seq_num := l_operation_seq_num;
626 l_eam_mat_req_rec.inventory_item_id := l_inventory_item_id;
627 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_DELETE;
628
629 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
630 EAM_PROCESS_WO_PUB.Process_WO
631 ( p_bo_identifier => 'EAM'
632 , p_init_msg_list => TRUE
633 , p_api_version_number => 1.0
634 , p_commit => 'N'
635 , p_eam_wo_rec => l_eam_wo_rec
636 , p_eam_op_tbl => l_eam_op_tbl
637 , p_eam_op_network_tbl => l_eam_op_network_tbl
638 , p_eam_res_tbl => l_eam_res_tbl
639 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
640 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
641 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
642 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
643 , p_eam_direct_items_tbl => l_eam_di_tbl
644 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
645 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
646 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
647 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
648 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
649 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
650 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
651 , p_eam_request_tbl => l_eam_request_tbl
652 , x_eam_wo_rec => l_out_eam_wo_rec
653 , x_eam_op_tbl => l_out_eam_op_tbl
654 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
655 , x_eam_res_tbl => l_out_eam_res_tbl
656 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
657 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
658 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
659 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
660 , x_eam_direct_items_tbl => l_out_eam_di_tbl
661 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
662 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
663 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
664 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
665 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
666 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
667 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
668 , x_eam_request_tbl => l_out_eam_request_tbl
669 , x_return_status => l_return_status
670 , x_msg_count => l_msg_count
671 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
672 , p_debug_filename => 'delmrthb.log'
673 , p_output_dir => l_output_dir
674 , p_debug_file_mode => 'w'
675 );
676 IF (l_return_status = 'S') THEN
677 COMMIT;
678 x_material_shortage_flag := l_out_eam_wo_rec.material_shortage_flag;
679 x_material_shortage_check_date := l_out_eam_wo_rec.material_shortage_check_date;
680 ELSE
681 EAM_WORKORDER_UTIL_PKG.show_mesg;
682 APP_EXCEPTION.RAISE_EXCEPTION;
683 END IF;
684 EXCEPTION
685 WHEN OTHERS THEN
686 EAM_WORKORDER_UTIL_PKG.show_mesg;
687 APP_EXCEPTION.RAISE_EXCEPTION;
688
689
690 END Delete_Row;
691
692
693
694 --
695 -- baroy - API to delete a requirements row (called from SS View Materials Page)
696 --
697 PROCEDURE Delete_Row_SS(
698 p_api_version IN NUMBER,
699 p_init_msg_list IN VARCHAR2,
700 p_commit IN VARCHAR2,
701 p_validate_only IN VARCHAR2,
702 p_record_version_number IN NUMBER,
703 x_return_status OUT NOCOPY VARCHAR2,
704 x_msg_count OUT NOCOPY NUMBER,
705 x_msg_data OUT NOCOPY VARCHAR2,
706 p_inventory_item_id IN NUMBER,
707 p_organization_id IN NUMBER,
708 p_wip_entity_id IN NUMBER,
709 p_operation_seq_num IN NUMBER) IS
710
711 l_material VARCHAR2(40);
712 l_msg_count NUMBER;
713 l_msg_data VARCHAR2(250);
714 l_return_status VARCHAR2(250);
715 l_data VARCHAR2(250);
716 l_msg_index_out NUMBER;
717 l_quantity_issued NUMBER := 0;
718 l_wip_entity_type NUMBER;
719 l_status_type NUMBER;
720 l_material_issue_by_mo VARCHAR2(1);
721
722 /* Commenting out for implementing WO API */
723 /* CURSOR pending_txns_cur IS
724 SELECT 1
725 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
726 WHERE operation_seq_num = p_operation_seq_num
727 AND inventory_item_id = p_inventory_item_id
728 AND transaction_source_id = p_wip_entity_id
729 AND organization_id = p_organization_id
730 AND process_flag <> 'N'
731 AND posting_flag <> 'N';
732 CURSOR mtl_del_cur IS
733 SELECT 1
734 FROM MTL_MATERIAL_TRANSACTIONS
735 WHERE operation_seq_num = p_operation_seq_num
736 AND inventory_item_id = p_inventory_item_id
737 AND transaction_source_id = p_wip_entity_id
738 AND organization_id = p_organization_id; */
739
740 l_output_dir VARCHAR2(512);
741
742 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
743 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
744 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
745 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
746 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
747 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
748 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
749 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
750 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
751 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
752 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
753 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
754 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
755 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
756 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
757 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
758 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
759 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
760
761 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
762 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
763 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
764 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
765 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
766 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
767 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
768 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
769 l_out_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
770 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
771 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
772 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
773 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
774 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
775 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
776 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
777 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
778
779
780 BEGIN
781
782 IF p_commit = FND_API.G_TRUE THEN
783 SAVEPOINT delete_row_ss;
784 END IF;
785
786 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
787 FND_MSG_PUB.initialize;
788 END IF;
789
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791
792
793
794
795 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
796 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
797 l_eam_mat_req_rec.organization_id := p_organization_id;
798 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
799 l_eam_mat_req_rec.inventory_item_id := p_inventory_item_id;
800
801 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
802
803 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
804
805
806 EAM_PROCESS_WO_PUB.Process_WO
807 ( p_bo_identifier => 'EAM'
808 , p_init_msg_list => TRUE
809 , p_api_version_number => 1.0
810 , p_eam_wo_rec => l_eam_wo_rec
811 , p_eam_op_tbl => l_eam_op_tbl
812 , p_eam_op_network_tbl => l_eam_op_network_tbl
813 , p_eam_res_tbl => l_eam_res_tbl
814 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
815 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
816 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
817 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
818 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
819 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
820 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
821 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
822 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
823 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
824 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
825 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
826 , p_eam_request_tbl => l_eam_request_tbl
827 , x_eam_wo_rec => l_out_eam_wo_rec
828 , x_eam_op_tbl => l_out_eam_op_tbl
829 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
830 , x_eam_res_tbl => l_out_eam_res_tbl
831 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
832 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
833 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
834 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
835 , x_eam_direct_items_tbl => l_out_eam_direct_items_tbl
836 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
837 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
838 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
839 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
840 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
841 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
842 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
843 , x_eam_request_tbl => l_out_eam_request_tbl
844 , x_return_status => x_return_status
845 , x_msg_count => x_msg_count
846 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
847 , p_debug_filename => 'delmrss.log'
848 , p_output_dir => l_output_dir
849 );
850
851
852 /* Commented out for implementing the WO API */
853 /*
854 DELETE FROM WIP_REQUIREMENT_OPERATIONS
855 WHERE wip_entity_id = p_wip_entity_id
856 and organization_id = p_organization_id
857 and inventory_item_id = p_inventory_item_id
858 and operation_seq_num = p_operation_seq_num; */
859 /* IF (SQL%NOTFOUND) THEN
860 RAISE NO_DATA_FOUND;
861
862 ELSE */
863
864 /* Commented out for implementing the WO API */
865 -- if validate not passed then raise error
866 /* l_msg_count := FND_MSG_PUB.count_msg;
867 IF l_msg_count = 1 THEN
868 eam_execution_jsp.Get_Messages
869 (p_encoded => FND_API.G_FALSE,
870 p_msg_index => 1,
871 p_msg_count => l_msg_count,
872 p_msg_data => nvl(l_msg_data,FND_API.g_MISS_CHAR),
873 p_data => l_data,
874 p_msg_index_out => l_msg_index_out);
875 x_msg_count := l_msg_count;
876 x_msg_data := l_msg_data;
877 ELSE
878 x_msg_count := l_msg_count;
879 END IF;
880
881 IF l_msg_count > 0 THEN
882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
883 RAISE FND_API.G_EXC_ERROR;
884 END IF;
885 */
886
887
888 IF FND_API.TO_BOOLEAN(P_COMMIT)
889 AND x_return_status = 'S' THEN
890 COMMIT WORK;
891 END IF;
892
893 EXCEPTION
894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
895 IF p_commit = FND_API.G_TRUE THEN
896 ROLLBACK TO delete_row_ss;
897 END IF;
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899 WHEN FND_API.G_EXC_ERROR THEN
900 IF p_commit = FND_API.G_TRUE THEN
901 ROLLBACK TO delete_row_ss;
902 END IF;
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904 WHEN OTHERS THEN
905 IF p_commit = FND_API.G_TRUE THEN
906 ROLLBACK TO delete_row_ss;
907 END IF;
908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
909
910 END Delete_Row_SS;
911
912
913
914 -- API to delete a description based item requirements row (called from SS View Materials Page)
915 --
916 PROCEDURE Delete_Desc_Row_SS(
917 p_api_version IN NUMBER,
918 p_init_msg_list IN VARCHAR2,
919 p_commit IN VARCHAR2,
920 p_validate_only IN VARCHAR2,
921 p_record_version_number IN NUMBER,
922 x_return_status OUT NOCOPY VARCHAR2,
923 x_msg_count OUT NOCOPY NUMBER,
924 x_msg_data OUT NOCOPY VARCHAR2,
925 p_di_sequence_id IN NUMBER,
926 p_organization_id IN NUMBER,
927 p_wip_entity_id IN NUMBER,
928 p_operation_seq_num IN NUMBER) IS
929
930 l_material VARCHAR2(40);
931 l_msg_count NUMBER;
932 l_msg_data VARCHAR2(250);
933 l_return_status VARCHAR2(250);
934 l_data VARCHAR2(250);
935 l_msg_index_out NUMBER;
936 l_quantity_issued NUMBER := 0;
937 l_wip_entity_type NUMBER;
938 l_status_type NUMBER;
939 l_material_issue_by_mo VARCHAR2(1);
940
941 l_output_dir VARCHAR2(512);
942
943 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
944 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
945 l_eam_direct_item_rec EAM_PROCESS_WO_PUB.eam_direct_items_rec_type;
946 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
947 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
948 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
949 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
950 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
951 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
952 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
953 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
954 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
955 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
956 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
957 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
958 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
959 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
960 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
961 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
962
963 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
964 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
965 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
966 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
967 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
968 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
969 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
970 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
971 l_out_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
972 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
973 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
974 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
975 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
976 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
977 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
978 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
979 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
980
981 BEGIN
982
983 IF p_commit = FND_API.G_TRUE THEN
984 SAVEPOINT delete_desc_row_ss;
985 END IF;
986
987 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
988 FND_MSG_PUB.initialize;
989 END IF;
990
991 x_return_status := FND_API.G_RET_STS_SUCCESS;
992 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
993
994
995 l_eam_direct_item_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
996 l_eam_direct_item_rec.wip_entity_id := p_wip_entity_id;
997 l_eam_direct_item_rec.organization_id := p_organization_id;
998 l_eam_direct_item_rec.operation_seq_num := p_operation_seq_num;
999 l_eam_direct_item_rec.direct_item_sequence_id := p_di_sequence_id;
1000
1001 l_eam_direct_items_tbl(1) := l_eam_direct_item_rec;
1002
1003 EAM_PROCESS_WO_PUB.Process_WO
1004 ( p_bo_identifier => 'EAM'
1005 , p_init_msg_list => TRUE
1006 , p_api_version_number => 1.0
1007 , p_eam_wo_rec => l_eam_wo_rec
1008 , p_eam_op_tbl => l_eam_op_tbl
1009 , p_eam_op_network_tbl => l_eam_op_network_tbl
1010 , p_eam_res_tbl => l_eam_res_tbl
1011 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1012 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1013 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1014 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1015 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
1016 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
1017 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1018 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1019 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1020 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1021 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1022 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1023 , p_eam_request_tbl => l_eam_request_tbl
1024 , x_eam_wo_rec => l_out_eam_wo_rec
1025 , x_eam_op_tbl => l_out_eam_op_tbl
1026 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
1027 , x_eam_res_tbl => l_out_eam_res_tbl
1028 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
1029 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
1030 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
1031 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
1032 , x_eam_direct_items_tbl => l_out_eam_direct_items_tbl
1033 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
1034 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1035 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1036 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1037 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1038 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1039 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1040 , x_eam_request_tbl => l_out_eam_request_tbl
1041 , x_return_status => x_return_status
1042 , x_msg_count => x_msg_count
1043 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1044 , p_debug_filename => 'deldiss.log'
1045 , p_output_dir => l_output_dir
1046 );
1047
1048
1049
1050 IF FND_API.TO_BOOLEAN(P_COMMIT)
1051 AND x_return_status = 'S' THEN
1052 COMMIT WORK;
1053 END IF;
1054
1055 EXCEPTION
1056 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1057 IF p_commit = FND_API.G_TRUE THEN
1058 ROLLBACK TO delete_desc_row_ss;
1059 END IF;
1060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1061 WHEN FND_API.G_EXC_ERROR THEN
1062 IF p_commit = FND_API.G_TRUE THEN
1063 ROLLBACK TO delete_desc_row_ss;
1064 END IF;
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1066 WHEN OTHERS THEN
1067 IF p_commit = FND_API.G_TRUE THEN
1068 ROLLBACK TO delete_desc_row_ss;
1069 END IF;
1070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071
1072 END Delete_Desc_Row_SS;
1073
1074
1075
1076
1077
1078 END EAM_REQUIREMENTS_PKG;