[Home] [Help]
PACKAGE BODY: APPS.EAM_IMPORT_WORKORDERS
Source
1 PACKAGE BODY eam_import_workorders AS
2 /* $Header: EAMIMPWB.pls 120.3.12010000.6 2009/10/30 13:46:37 srkotika ship $ */
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_asset_serial_number VARCHAR2(30); /*bug 892512*/
85 l_op_index NUMBER :=1;
86 l_res_index NUMBER :=1;
87 l_res_inst_index NUMBER :=1;
88
89 l_return_status VARCHAR2(1);
90 l_msg_count NUMBER;
91 l_output_dir VARCHAR2(512);
92
93 BEGIN
94
95 /* get output directory path from database */
96 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
97
98 l_eam_wo_tbl := p_eam_wo_tbl;
99
100 l_eam_op_tbl := p_eam_op_tbl;
101 l_eam_res_tbl := p_eam_res_tbl;
102 l_eam_res_inst_tbl := p_eam_res_inst_tbl;
103
104
105 IF l_eam_wo_tbl.COUNT > 0 THEN
106 FOR J IN l_eam_wo_tbl.FIRST..l_eam_wo_tbl.LAST LOOP
107 SAVEPOINT import_workorders;
108 l_eam_wo_rec := l_eam_wo_tbl(J);
109
110 l_op_index := 1;
111 l_res_index := 1;
112 l_res_inst_index := 1;
113
114
115 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
116 l_eam_wo_rec.batch_id := 1;
117 l_eam_wo_rec.header_id := 1;
118
119 l_wip_entity_id := l_eam_wo_rec.wip_entity_id;
120 l_eam_wo_rec.attribute15 := NULL; -- this att was made to import for diverting the flow from rossetta api, remove this after adding a new var in l_eam_wo_rec
121
122 /*bug 8925120 l_eam_wo_rec.asset_number has instance number pushed from primavera, change it to (msn)serial_number*/
123 SELECT asset_number INTO l_asset_serial_number FROM wip_discrete_jobs
124 WHERE wip_entity_id= l_wip_entity_id AND organization_id=l_eam_wo_rec.organization_id;
125
126 l_eam_wo_rec.asset_number := l_asset_serial_number;
127 /*bug 8925120 end */
128
129
130 l_eam_wo_tbl_p(1) := l_eam_wo_rec; -- tsp
131 --g_eam_wo_tbl0(1) := g_eam_wo_rec1;
132
133
134
135 IF(l_eam_op_tbl.Count>0) THEN
136 IF(l_eam_op_tbl_p.Count >0) THEN
137 l_eam_op_tbl_p.delete(l_eam_op_tbl_p.FIRST,l_eam_op_tbl_p.last); -- remove all prev job's elements
138 END IF;
139 FOR O IN l_eam_op_tbl.FIRST..l_eam_op_tbl.LAST LOOP
140 l_eam_op_rec := l_eam_op_tbl(O);
141 IF(l_eam_op_rec.WIP_ENTITY_ID = l_wip_entity_id) THEN
142 l_eam_op_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
143 l_eam_op_tbl_p(l_op_index) := l_eam_op_rec; --tsp
144 l_op_index := l_op_index+1;
145 END IF;
146 END LOOP;
147 END IF;
148
149 IF(l_eam_res_tbl.Count >0) THEN
150 IF(l_eam_res_tbl_p.Count > 0)THEN
151 l_eam_res_tbl_p.delete(l_eam_res_tbl_p.first,l_eam_res_tbl_p.last); -- remove all prev job's elements
152 END IF;
153 FOR R IN l_eam_res_tbl.FIRST..l_eam_res_tbl.LAST LOOP
154 l_eam_res_rec := l_eam_res_tbl(R);
155 IF(l_eam_res_rec.WIP_ENTITY_ID = l_wip_entity_id) THEN
156 l_eam_res_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
157 l_eam_res_tbl_p(l_res_index) := l_eam_res_rec;
158 l_res_index := l_res_index+1;
159 END IF;
160 END LOOP;
161 END IF;
162
163 IF(l_eam_res_inst_tbl.Count>0) THEN
164 IF(l_eam_res_inst_tbl_p.Count > 0) THEN
165 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
166 END IF;
167
168 FOR I IN l_eam_res_inst_tbl.FIRST..l_eam_res_inst_tbl.LAST LOOP
169 l_eam_res_inst_rec := l_eam_res_inst_tbl(I);
170 IF(l_eam_res_inst_rec.WIP_ENTITY_ID = l_wip_entity_id) THEN
171 /* l_eam_res_inst_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_SYNC; /*modified for primavera*/
172
173 l_eam_res_inst_tbl_p(l_res_inst_index) := l_eam_res_inst_rec;
174 l_res_inst_index := l_res_inst_index+1;
175 END IF;
176 END LOOP;
177 END IF;
178
179 x_wip_entity_id := NULL;
180 EAM_PROCESS_WO_PUB.PROCESS_WO(
181 p_bo_identifier =>'EAM'
182 , p_api_version_number => 1.0
183 , p_init_msg_list => TRUE
184 , p_eam_wo_rec => l_eam_wo_rec
185 , p_eam_op_tbl => l_eam_op_tbl_p
186 , p_eam_op_network_tbl => l_eam_op_network_tbl
187 , p_eam_res_tbl => l_eam_res_tbl_p
188 , p_eam_res_inst_tbl => l_eam_res_inst_tbl_p
189 , p_eam_res_usage_tbl => p_eam_res_usage_tbl
190 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
191 , p_eam_mat_req_tbl => p_eam_mat_req_tbl
192 , p_eam_direct_items_tbl => p_eam_direct_items_tbl
193 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
194 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
195 , p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
196 , p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
197 , p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl
198 , p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl
199 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
200 , p_eam_request_tbl => p_eam_request_tbl
201 , x_eam_wo_rec => l_eam_wo_rec_out
202 , x_eam_op_tbl => l_eam_op_tbl_out
203 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
204 , x_eam_res_tbl => l_eam_res_tbl_out
205 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
206 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
207 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
208 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
209 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_out
210 , x_eam_wo_comp_rec => l_eam_wo_comp_rec_out
211 , x_eam_wo_quality_tbl => l_eam_wo_quality_tbl_out
212 , x_eam_meter_reading_tbl => l_eam_meter_reading_tbl_out
213 , x_eam_counter_prop_tbl => l_eam_counter_prop_tbl_out
214 , x_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl_out
215 , x_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl_out
216 , x_eam_op_comp_tbl => l_eam_op_comp_tbl_out
217 , x_eam_request_tbl => l_eam_request_tbl_out
218 , x_return_status => x_return_status
219 , x_msg_count => x_msg_count
220 , p_commit => 'N'
221 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
222 , p_output_dir => l_output_dir
223 , p_debug_filename => 'createupdatewo.log'
224 , p_debug_file_mode => 'W'
225 );
226
227 IF(x_return_status='S') THEN
228 IF p_commit = FND_API.G_TRUE THEN
229 COMMIT WORK;
230 end if;
231 IF(l_eam_wo_tbl_out IS NOT NULL AND l_eam_wo_tbl_out.COUNT>0) THEN
232 x_wip_entity_id := l_eam_wo_tbl_out(l_eam_wo_tbl_out.FIRST).wip_entity_id;
233 ELSIF(l_eam_wo_rec_out.wip_entity_id IS NOT NULL) THEN
234 x_wip_entity_id := l_eam_wo_rec_out.wip_entity_id;
235 END IF;
236 END IF;
237
238 IF(x_return_status <> 'S') THEN
239 ROLLBACK TO import_workorders;
240 END IF;
241
242 END LOOP;
243 END IF;
244
245 END import_workorders;
246
247 END eam_import_workorders;
248