[Home] [Help]
PACKAGE BODY: APPS.EAM_IMPORT_WORKORDERS
Source
1 PACKAGE BODY eam_import_workorders AS
2 /* $Header: EAMIMPWB.pls 120.3 2008/06/06 21:59:33 jvittes noship $ */
3
4
5 /*********************************************************
6 Wrapper procedure on top of WO API.This is used to update valid imported workorders and its related entities
7 ************************************************/
8 PROCEDURE import_workorders
9 (
10 p_commit IN VARCHAR2 := FND_API.G_FALSE,
11 p_eam_wo_tbl IN EAM_PROCESS_WO_PUB.eam_wo_tbl_type,
12 p_eam_wo_relations_tbl IN EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type,
13 p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type,
14 p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type,
15 p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type,
16 p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
17 p_eam_mat_req_tbl IN EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type,
18 p_eam_direct_items_tbl IN EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type,
19 p_eam_request_tbl IN EAM_PROCESS_WO_PUB.eam_request_tbl_type,
20 p_eam_wo_comp_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type,
21 p_eam_meter_reading_tbl IN EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type,
22 p_eam_counter_prop_tbl IN EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type,
23 p_eam_wo_comp_rebuild_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type,
24 p_eam_wo_comp_mr_read_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type,
25 x_wip_entity_id OUT NOCOPY NUMBER,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER
28 )
29 IS
30 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
31 l_eam_wo_tbl_p EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
32 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
33
34 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
35 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
36 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
37
38 l_eam_op_tbl_p EAM_PROCESS_WO_PUB.eam_op_tbl_type;
39 l_eam_res_tbl_p EAM_PROCESS_WO_PUB.eam_res_tbl_type;
40 l_eam_res_inst_tbl_p EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
41
42 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
43 l_eam_res_rec EAM_PROCESS_WO_PUB.eam_res_rec_type;
44 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
45
46 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
47 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
48 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
49 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
50 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
51
52 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
53 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
54 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
55 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
56 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
57 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
58 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
59 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
60 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
61
62 l_eam_wo_relations_tbl_out EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
63 l_eam_wo_rec_out EAM_PROCESS_WO_PUB.eam_wo_rec_type;
64 l_eam_wo_tbl_out EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
65 l_eam_op_tbl_out EAM_PROCESS_WO_PUB.eam_op_tbl_type;
66 l_eam_op_network_tbl_out EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
67 l_eam_res_tbl_out EAM_PROCESS_WO_PUB.eam_res_tbl_type;
68 l_eam_res_usage_tbl_out EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
69 l_eam_res_inst_tbl_out EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
70 l_eam_sub_res_tbl_out EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
71 l_eam_mat_req_tbl_out EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
72 l_eam_direct_items_tbl_out EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
73 l_eam_wo_comp_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
74 l_eam_wo_quality_tbl_out EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
75 l_eam_meter_reading_tbl_out EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
76 l_eam_counter_prop_tbl_out EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
77 l_eam_wo_comp_rebuild_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
78 l_eam_wo_comp_mr_read_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
79 l_eam_op_comp_tbl_out EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
80 l_eam_request_tbl_out EAM_PROCESS_WO_PUB.eam_request_tbl_type;
81 l_eam_wo_comp_rec_out EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
82
83 l_wip_entity_id NUMBER;
84 l_op_index NUMBER :=1;
85 l_res_index NUMBER :=1;
86 l_res_inst_index NUMBER :=1;
87
88 l_return_status VARCHAR2(1);
89 l_msg_count NUMBER;
90 l_output_dir VARCHAR2(512);
91
92 BEGIN
93
94 /* get output directory path from database */
95 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
96
97 l_eam_wo_tbl := p_eam_wo_tbl;
98
99 l_eam_op_tbl := p_eam_op_tbl;
100 l_eam_res_tbl := p_eam_res_tbl;
101 l_eam_res_inst_tbl := p_eam_res_inst_tbl;
102
103
104 IF l_eam_wo_tbl.COUNT > 0 THEN
105 FOR J IN l_eam_wo_tbl.FIRST..l_eam_wo_tbl.LAST LOOP
106 SAVEPOINT import_workorders;
107 l_eam_wo_rec := l_eam_wo_tbl(J);
108
109 l_op_index := 1;
110 l_res_index := 1;
111 l_res_inst_index := 1;
112
113
114 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
115 l_eam_wo_rec.batch_id := 1;
116 l_eam_wo_rec.header_id := 1;
117
118 l_wip_entity_id := l_eam_wo_rec.wip_entity_id;
119 l_eam_wo_rec.attribute15 := NULL; -- this att was made to import for diverting the flow from rossetta api
120 l_eam_wo_tbl_p(1) := l_eam_wo_rec; -- tsp
121 --g_eam_wo_tbl0(1) := g_eam_wo_rec1;
122
123
124
125 IF(l_eam_op_tbl.Count>0) THEN
126 IF(l_eam_op_tbl_p.Count >0) THEN
127 l_eam_op_tbl_p.delete(l_eam_op_tbl_p.FIRST,l_eam_op_tbl_p.last); -- remove all prev job's elements
128 END IF;
129 FOR O IN l_eam_op_tbl.FIRST..l_eam_op_tbl.LAST LOOP
130 l_eam_op_rec := l_eam_op_tbl(O);
131 IF(l_eam_op_rec.WIP_ENTITY_ID = l_wip_entity_id) THEN
132 l_eam_op_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
133 l_eam_op_tbl_p(l_op_index) := l_eam_op_rec; --tsp
134 l_op_index := l_op_index+1;
135 END IF;
136 END LOOP;
137 END IF;
138
139 IF(l_eam_res_tbl.Count >0) THEN
140 IF(l_eam_res_tbl_p.Count > 0)THEN
141 l_eam_res_tbl_p.delete(l_eam_res_tbl_p.first,l_eam_res_tbl_p.last); -- remove all prev job's elements
142 END IF;
143 FOR R IN l_eam_res_tbl.FIRST..l_eam_res_tbl.LAST LOOP
144 l_eam_res_rec := l_eam_res_tbl(R);
145 IF(l_eam_res_rec.WIP_ENTITY_ID = l_wip_entity_id) THEN
146 l_eam_res_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
147 l_eam_res_tbl_p(l_res_index) := l_eam_res_rec;
148 l_res_index := l_res_index+1;
149 END IF;
150 END LOOP;
151 END IF;
152
153 IF(l_eam_res_inst_tbl.Count>0) THEN
154 IF(l_eam_res_inst_tbl_p.Count > 0) THEN
155 l_eam_res_inst_tbl_p.delete(l_eam_res_inst_tbl_p.first,l_eam_res_inst_tbl_p.last); -- remove all prev job's elements
156 END IF;
157
158 FOR I IN l_eam_res_inst_tbl.FIRST..l_eam_res_inst_tbl.LAST LOOP
159 l_eam_res_inst_rec := l_eam_res_inst_tbl(I);
160 IF(l_eam_res_inst_rec.WIP_ENTITY_ID = l_wip_entity_id) THEN
161 l_eam_res_inst_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
162 l_eam_res_inst_tbl_p(l_res_inst_index) := l_eam_res_inst_rec;
163 l_res_inst_index := l_res_inst_index+1;
164 END IF;
165 END LOOP;
166 END IF;
167
168 x_wip_entity_id := NULL;
169 EAM_PROCESS_WO_PUB.PROCESS_WO(
170 p_bo_identifier =>'EAM'
171 , p_api_version_number => 1.0
172 , p_init_msg_list => TRUE
173 , p_eam_wo_rec => l_eam_wo_rec
174 , p_eam_op_tbl => l_eam_op_tbl_p
175 , p_eam_op_network_tbl => l_eam_op_network_tbl
176 , p_eam_res_tbl => l_eam_res_tbl_p
177 , p_eam_res_inst_tbl => l_eam_res_inst_tbl_p
178 , p_eam_res_usage_tbl => p_eam_res_usage_tbl
179 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
180 , p_eam_mat_req_tbl => p_eam_mat_req_tbl
181 , p_eam_direct_items_tbl => p_eam_direct_items_tbl
182 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
183 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
184 , p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
185 , p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
186 , p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl
187 , p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl
188 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
189 , p_eam_request_tbl => p_eam_request_tbl
190 , x_eam_wo_rec => l_eam_wo_rec_out
191 , x_eam_op_tbl => l_eam_op_tbl_out
192 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
193 , x_eam_res_tbl => l_eam_res_tbl_out
194 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
195 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
196 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
197 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
198 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_out
199 , x_eam_wo_comp_rec => l_eam_wo_comp_rec_out
200 , x_eam_wo_quality_tbl => l_eam_wo_quality_tbl_out
201 , x_eam_meter_reading_tbl => l_eam_meter_reading_tbl_out
202 , x_eam_counter_prop_tbl => l_eam_counter_prop_tbl_out
203 , x_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl_out
204 , x_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl_out
205 , x_eam_op_comp_tbl => l_eam_op_comp_tbl_out
206 , x_eam_request_tbl => l_eam_request_tbl_out
207 , x_return_status => x_return_status
208 , x_msg_count => x_msg_count
209 , p_commit => 'N'
210 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
211 , p_output_dir => l_output_dir
212 , p_debug_filename => 'createupdatewo.log'
213 , p_debug_file_mode => 'W'
214 );
215
216 IF(x_return_status='S') THEN
217 IF p_commit = FND_API.G_TRUE THEN
218 COMMIT WORK;
219 end if;
220 IF(l_eam_wo_tbl_out IS NOT NULL AND l_eam_wo_tbl_out.COUNT>0) THEN
221 x_wip_entity_id := l_eam_wo_tbl_out(l_eam_wo_tbl_out.FIRST).wip_entity_id;
222 ELSIF(l_eam_wo_rec_out.wip_entity_id IS NOT NULL) THEN
223 x_wip_entity_id := l_eam_wo_rec_out.wip_entity_id;
224 END IF;
225 END IF;
226
227 IF(x_return_status <> 'S') THEN
228 ROLLBACK TO import_workorders;
229 END IF;
230
231 END LOOP;
232 END IF;
233
234 END import_workorders;
235
236 END eam_import_workorders;
237