[Home] [Help]
PACKAGE BODY: APPS.EAM_COPY_BOM_PKG
Source
1 PACKAGE BODY EAM_COPY_BOM_PKG as
2 /* $Header: EAMCPBMB.pls 120.2.12020000.4 2012/10/05 09:13:59 vpasupur ship $ */
3
4 /*
5 Procedure to copy materials from workorder to bom
6 -- p_organization_id Organization Id
7 -- p_organization_code Organization Code
8 -- p_asset_number Asset Number
9 -- p_asset_group_id Inventory Item Id
10 -- p_component_table Table of workorder materials
11 -- x_error_code 0 success
12 1 some of components are already in the asset bom
13 2 error in the bom api
14 */
15 PROCEDURE copy_to_bom(
16 p_organization_id IN NUMBER,
17 p_organization_code IN VARCHAR2,
18 p_asset_number IN VARCHAR2,
19 p_asset_group_id IN NUMBER,
20 p_component_table IN t_component_table,
21 x_error_code OUT NOCOPY NUMBER)
22 IS
23 l_component_table EAM_WORKORDER_UTIL_PKG.t_component_table;
24 l_index NUMBER;
25 i NUMBER;
26 l_error_code NUMBER;
27 BEGIN
28
29 i:= 1;
30
31 --Copy the information from the input table to table type of EAM_WORKORDER_UTIL_PKG.t_component_table
32 l_index := p_component_table.FIRST;
33 loop
34
35 l_component_table(i).component_item := p_component_table(l_index).component_item;
36 l_component_table(i).component_item_id:= p_component_table(l_index).component_item_id;
37 l_component_table(i).start_effective_date:= SYSDATE;
38 l_component_table(i).operation_sequence_number:= p_component_table(l_index).operation_sequence_number;
39 l_component_table(i).quantity_per_assembly:= p_component_table(l_index).quantity_per_assembly;
40 l_component_table(i).wip_supply_type:= p_component_table(l_index).wip_supply_type;
41 l_component_table(i).supply_subinventory := p_component_table(l_index).supply_subinventory;
42 l_component_table(i).supply_locator_id:= p_component_table(l_index).supply_locator_id;
43 l_component_table(i).supply_locator_name:= p_component_table(l_index).supply_locator_name;
44
45 exit when l_index = p_component_table.LAST;
46 l_index := p_component_table.NEXT(l_index);
47 i := i+1;
48 end loop;
49
50
51 EAM_WORKORDER_UTIL_PKG.copy_to_bom(
52 p_organization_id,
53 p_organization_code,
54 p_asset_number,
55 p_asset_group_id,
56 l_component_table,
57 x_error_code);
58
59 END copy_to_bom;
60
61 /*
62 Procedure to copy materials from the asset bom to workorder
63 -- p_organization_id Organization Id
64 -- p_wip_entity_id Wip Entity Id
65 -- p_operation_seq_num Operation to which materials are to be copied
66 -- p_department_id Department
67 -- p_bom_table Table of bom materials
68 -- x_error_code S success
69 U error
70 E error
71 */
72 PROCEDURE retrieve_asset_bom(
73 p_organization_id IN NUMBER,
74 p_wip_entity_id IN NUMBER,
75 p_operation_seq_num IN NUMBER,
76 p_department_id IN NUMBER,
77 p_bom_table IN t_bom_table,
78 x_error_code OUT NOCOPY VARCHAR2)
79 IS
80 l_index NUMBER;
81 l_msg_count NUMBER;
82 i NUMBER;
83 l_output_dir VARCHAR2(512);
84
85 l_material_req_table EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
86
87 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
88 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
89 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
90 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
91 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
92 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
93 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
94 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
95 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
96 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
97 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
98 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
99 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
100 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
101 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
102 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
103
104
105 l_out_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
106 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
107 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
108 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
109 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
110 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
111 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
112 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
113 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
114 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
115 l_eam_wo_relations_tbl1 EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
116 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
117 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
118 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
119 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
120 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
121 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
122 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
123 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
124 l_effectivity_control NUMBER; -- Bug 14539580
125
126 BEGIN
127 /* get output directory path from database */
128 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
129
130
131 i := 1;
132
133 --Copy the information from the input table to table type of EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type
134 l_index := p_bom_table.FIRST;
135 loop
136
137 l_material_req_table(i).batch_id := 1;
138 l_material_req_table(i).header_id :=p_wip_entity_id;
139 l_material_req_table(i).transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
140 l_material_req_table(i).wip_entity_id := p_wip_entity_id;
141 l_material_req_table(i).organization_id := p_organization_id;
142 l_material_req_table(i).operation_seq_num := p_operation_seq_num;
143 l_material_req_table(i).inventory_item_id :=p_bom_table(l_index).component_item_id;
144 l_material_req_table(i).quantity_per_assembly := p_bom_table(l_index).component_quantity;
145 l_material_req_table(i).department_id :=p_department_id;
146 l_material_req_table(i).wip_supply_type := p_bom_table(l_index).wip_supply_type;
147
148 /* Added for bug#5679199 Start */
149 BEGIN
150 SELECT first_unit_start_date
151 INTO l_material_req_table(i).date_required
152 FROM wip_operations
153 WHERE wip_entity_id = p_wip_entity_id
154 AND operation_seq_num = p_operation_seq_num
155 AND organization_id = p_organization_id;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 l_material_req_table(i).date_required :=SYSDATE;
159 END;
160
161 /* Added for bug#5679199 End */
162
163 l_material_req_table(i).required_quantity := p_bom_table(l_index).component_quantity;
164
165 -- strt Bug 14539580
166 SELECT msi.effectivity_control into l_effectivity_control
167 FROM mtl_system_items msi,
168 wip_discrete_jobs wdj
169 WHERE msi.inventory_item_id = nvl(wdj.asset_group_id,wdj.rebuild_item_id)
170 AND msi.organization_id = wdj.organization_id
171 AND wdj.wip_entity_id=p_wip_entity_id
172 AND wdj.organization_id = p_organization_id;
173
174 if l_effectivity_control=1 then -- Dates effectivity
175 SELECT auto_request_material INTO l_material_req_table(i).AUTO_REQUEST_MATERIAL
176 FROM bom_components_b bc,
177 bom_structures_b bs,
178 wip_discrete_jobs wdj
179 WHERE bc.component_item_id= p_bom_table(l_index).component_item_id
180 AND (bc.effectivity_date <= sysdate and (bc.disable_date >= sysdate or bc.disable_date is null))
181 AND bc.bill_sequence_id = bs.bill_sequence_id
182 AND bs.assembly_item_id= nvl(wdj.asset_group_id,wdj.rebuild_item_id)
183 AND bs.organization_id=p_organization_id
184 AND wdj.wip_entity_id=p_wip_entity_id
185 AND rownum<2;
186
187 elsif l_effectivity_control=2 then -- Unit/Serial effectivity
188 SELECT auto_request_material INTO l_material_req_table(i).AUTO_REQUEST_MATERIAL
189 FROM bom_components_b bc,
190 bom_structures_b bs,
191 wip_discrete_jobs wdj
195 AND bc.bill_sequence_id = bs.bill_sequence_id
192 WHERE bc.component_item_id = p_bom_table(l_index).component_item_id
193 AND bc.from_end_item_unit_number <= nvl(wdj.asset_number,wdj.rebuild_serial_number)
194 AND (bc.to_end_item_unit_number >=nvl(wdj.asset_number,wdj.rebuild_serial_number) OR bc.to_end_item_unit_number IS NULL)
196 AND bs.assembly_item_id= nvl(wdj.asset_group_id,wdj.rebuild_item_id)
197 AND bs.organization_id=p_organization_id
198 AND wdj.wip_entity_id=p_wip_entity_id
199 AND rownum<2;
200 end if;
201 --end Bug 14539580
202
203 --Start of bug 13920228
204 /*populating unit price for non stock items*/
205 BEGIN
206 SELECT LIST_PRICE_PER_UNIT
207 INTO l_material_req_table(i).unit_price
208 FROM mtl_system_items_vl
209 WHERE inventory_item_id=p_bom_table(l_index).component_item_id
210 AND stock_enabled_flag='N'
211 AND organization_id = p_organization_id;
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 l_material_req_table(i).unit_price :=NULL;
215 END;
216 --End of bug 13920228
217
218
219 exit when l_index = p_bom_table.LAST;
220 l_index := p_bom_table.NEXT(l_index);
221 i := i+1;
222 end loop;
223
224
225 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
226 ( p_bo_identifier => 'EAM'
227 , p_init_msg_list => TRUE
228 , p_api_version_number => 1.0
229 , p_eam_wo_tbl => l_eam_wo_tbl
230 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
231 , p_eam_op_tbl => l_eam_op_tbl
232 , p_eam_op_network_tbl => l_eam_op_network_tbl
233 , p_eam_res_tbl => l_eam_res_tbl
234 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
235 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
236 , p_eam_mat_req_tbl => l_material_req_table
237 , p_eam_direct_items_tbl => l_eam_di_tbl
238 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
239 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
240 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
241 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
242 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
243 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
244 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
245 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
246 , p_eam_request_tbl => l_eam_request_tbl
247 , x_eam_wo_tbl => l_out_eam_wo_tbl
248 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
249 , x_eam_op_tbl => l_out_eam_op_tbl
250 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
251 , x_eam_res_tbl => l_out_eam_res_tbl
252 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
253 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
254 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
255 , x_eam_direct_items_tbl => l_out_eam_di_tbl
256 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
257 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
258 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
259 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
260 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
261 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
262 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
263 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
264 , x_eam_request_tbl => l_out_eam_request_tbl
265 , x_return_status => x_error_code
266 , x_msg_count => l_msg_count
267 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
268 , p_debug_filename => 'copytoreq.log'
269 , p_output_dir => l_output_dir
270 , p_commit => 'N'
271 , p_debug_file_mode => 'W'
272 );
273 IF(x_error_code='S') THEN
274 COMMIT;
275 END IF;
276
277 END retrieve_asset_bom;
278
279
280 END EAM_COPY_BOM_PKG;