[Home] [Help]
PACKAGE BODY: APPS.EAM_CONSTRUCTION_EST_PUB
Source
1 PACKAGE BODY EAM_CONSTRUCTION_EST_PUB AS
2 /* $Header: EAMPCESB.pls 120.0.12010000.5 2009/01/03 00:03:31 devijay noship $ */
3 -- Start of Comments
4 -- Package name : EAM_CONSTRUCTION_EST_PUB
5 -- Purpose : Spec of package EAM_CONSTRUCTION_EST_PUB
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_CONSTRUCTION_EST_PUB';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMPCESB.pls';
12
13 PROCEDURE EXPLODE_INITIAL_ESTIMATE(
14 p_api_version IN NUMBER := 1.0
15 , p_init_msg_list IN VARCHAR2 := 'F'
16 , p_commit IN VARCHAR2
17 , p_estimate_id IN NUMBER
18 , x_ce_msg_tbl OUT NOCOPY EAM_EST_DATASTRUCTURES_PUB.EAM_CE_MESSAGE_TBL
19 , x_return_status OUT NOCOPY VARCHAR2
20 , x_msg_count OUT NOCOPY NUMBER
21 , x_msg_data OUT NOCOPY VARCHAR2)
22 IS
23 BEGIN
24 EAM_CONSTRUCTION_EST_PVT.EXPLODE_INITIAL_ESTIMATE(
25 p_api_version => p_api_version,
26 p_init_msg_list => p_init_msg_list,
27 p_commit => p_commit,
28 p_estimate_id => p_estimate_id,
29 x_ce_msg_tbl => x_ce_msg_tbl,
30 x_return_status => x_return_status,
31 x_msg_count => x_msg_count,
32 x_msg_data => x_msg_data
33 );
34 EXCEPTION
35 WHEN OTHERS THEN
36 x_return_status := 'E';
37 END EXPLODE_INITIAL_ESTIMATE;
38
39 PROCEDURE INSERT_PARENT_WO_LINE(
40 p_api_version IN NUMBER := 1.0
41 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
42 , p_commit IN VARCHAR2
43 , p_estimate_id IN NUMBER
44 , p_parent_wo_line_rec IN EAM_EST_DATASTRUCTURES_PUB.EAM_CE_PARENT_WO_REC
45 , x_return_status OUT NOCOPY VARCHAR2
46 , x_msg_count OUT NOCOPY NUMBER
47 , x_msg_data OUT NOCOPY VARCHAR2
48 )
49 IS
50 BEGIN
51 EAM_CONSTRUCTION_EST_PVT.INSERT_PARENT_WO_LINE(
52 p_api_version => p_api_version
53 , p_init_msg_list => p_init_msg_list
54 , p_commit => p_commit
55 , p_estimate_id => p_estimate_id
56 , p_parent_wo_line_rec => p_parent_wo_line_rec
57 , x_return_status => x_return_status
58 , x_msg_count => x_msg_count
59 , x_msg_data => x_msg_data );
60 EXCEPTION
61 WHEN OTHERS THEN
62 x_return_status := 'E';
63 END INSERT_PARENT_WO_LINE;
64
65 PROCEDURE DELETE_WO_LINE(
66 p_api_version IN NUMBER := 1.0
67 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
68 , p_commit IN VARCHAR2
69 , p_work_order_line_id IN NUMBER
70 , x_return_status OUT NOCOPY VARCHAR2
71 , x_msg_count OUT NOCOPY NUMBER
72 , x_msg_data OUT NOCOPY VARCHAR2
73 )
74 IS
75 BEGIN
76 EAM_CONSTRUCTION_EST_PVT.DELETE_WO_LINE(
77 p_api_version => p_api_version
78 , p_init_msg_list => p_init_msg_list
79 , p_commit => p_commit
80 , p_work_order_line_id => p_work_order_line_id
81 , x_return_status => x_return_status
82 , x_msg_count => x_msg_count
83 , x_msg_data => x_msg_data );
84 EXCEPTION
85 WHEN OTHERS THEN
86 x_return_status := 'E';
87 END DELETE_WO_LINE;
88
89 PROCEDURE CREATE_CU_WORKORDERS(
90 p_api_version IN NUMBER := 1.0
91 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
92 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
93 ,p_estimate_id IN NUMBER
94 ,x_return_status OUT NOCOPY VARCHAR2
95 ,x_msg_count OUT NOCOPY NUMBER
96 ,x_msg_data OUT NOCOPY VARCHAR2
97 ,p_organization_id IN NUMBER
98 ,p_debug_filename IN VARCHAR2 := 'EAM_CU_DEBUG.log'
99 ,p_debug_file_mode IN VARCHAR2 := 'w'
100 )
101 IS
102 CURSOR GET_WOKRODER_NAME (p_wip_entityid IN NUMBER) IS
103 SELECT WIP_ENTITY_NAME
104 FROM WIP_ENTITIES
105 WHERE WIP_ENTITY_ID = p_wip_entityid;
106
107 CURSOR EST_WORKORDERS IS
108 SELECT DISTINCT ESTIMATE_WORK_ORDER_ID
109 FROM EAM_CE_WORK_ORDER_LINES
110 WHERE ESTIMATE_ID = p_estimate_id
111 AND ORGANIZATION_ID = p_organization_id;
112
113 l_wip_name_rec GET_WOKRODER_NAME%ROWTYPE;
114 l_est_wo_rec EST_WORKORDERS%ROWTYPE;
115
116 BEGIN
117 -- Call private CREATE_CU_WORKORDERS to create work orders
118 EAM_CONSTRUCTION_EST_PVT.CREATE_CU_WORKORDERS(
119 p_api_version => p_api_version
120 ,p_init_msg_list => p_init_msg_list
121 ,p_commit => p_commit
122 ,p_estimate_id => p_estimate_id
123 ,x_return_status => x_return_status
124 ,x_msg_count => x_msg_count
125 ,x_msg_data => x_msg_data
126 ,p_organization_id => p_organization_id
127 ,p_debug_filename => p_debug_filename
128 ,p_debug_file_mode => p_debug_file_mode);
129
130 IF nvl(x_return_status,'S') <> 'S' THEN
131 -- Log error, but continue processing
132 x_return_status := 'E';
133 RAISE FND_API.G_EXC_ERROR;
134 END IF; -- nvl(l_return_status,'S') <> 'S'
135
136 -- Now that work orders are created, need to handle condition
137 -- where work order number was not provided, but assigned
138 -- automaticially by the work order API
139 FOR l_est_wo_rec IN EST_WORKORDERS
140 LOOP
141 IF NVL(l_est_wo_rec.ESTIMATE_WORK_ORDER_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
142 -- Find the wip entity name for the estimate work order id
143 OPEN GET_WOKRODER_NAME(l_est_wo_rec.ESTIMATE_WORK_ORDER_ID);
144 FETCH GET_WOKRODER_NAME INTO l_wip_name_rec;
145 CLOSE GET_WOKRODER_NAME;
146
147 -- Update EAM_CE_WORK_ORDER_LINES with the wip entity name
148 UPDATE EAM_CE_WORK_ORDER_LINES SET WORK_ORDER_NUMBER = l_wip_name_rec.WIP_ENTITY_NAME
149 WHERE ESTIMATE_ID = p_estimate_id
150 AND ORGANIZATION_ID = p_organization_id
151 AND ESTIMATE_WORK_ORDER_ID = l_est_wo_rec.ESTIMATE_WORK_ORDER_ID;
152
153 END IF; -- NVL(l_est_wo_rec.ESTIMATE_WORK_ORDER_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
154 END LOOP; -- FOR l_est_wo_rec IN EST_WORKORDERS
155
156 IF NVL(p_commit,'F') = 'T' THEN
157 COMMIT;
158 END IF;
159
160 x_return_status := 'S';
161 EXCEPTION
162 WHEN FND_API.G_EXC_ERROR THEN
163 x_return_status := 'E';
164 WHEN OTHERS THEN
165 x_return_status := 'E';
166 END CREATE_CU_WORKORDERS;
167
168 PROCEDURE INSERT_ALL_WO_LINES(
169 p_api_version IN NUMBER := 1.0
170 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
171 , p_commit IN VARCHAR2
172 , p_estimate_id IN NUMBER
173 , p_eam_ce_wo_lines_tbl IN EAM_EST_DATASTRUCTURES_PUB.EAM_CE_WORK_ORDER_LINES_TBL
174 , x_return_status OUT NOCOPY VARCHAR2
175 , x_msg_count OUT NOCOPY NUMBER
176 , x_msg_data OUT NOCOPY VARCHAR2)
177 IS
178 BEGIN
179 EAM_CONSTRUCTION_EST_PVT.INSERT_ALL_WO_LINES(
180 p_api_version => p_api_version
181 , p_init_msg_list => p_init_msg_list
182 , p_commit => p_commit
183 , p_estimate_id => p_estimate_id
184 , p_eam_ce_wo_lines_tbl => p_eam_ce_wo_lines_tbl
185 , x_return_status => x_return_status
186 , x_msg_count => x_msg_count
187 , x_msg_data => x_msg_data);
188 EXCEPTION
189 WHEN OTHERS THEN
190 x_return_status := 'E';
191 END INSERT_ALL_WO_LINES;
192
193 PROCEDURE GET_CU_RECS(
194 p_api_version IN NUMBER,
195 p_commit IN VARCHAR2,
196 p_init_msg_list IN VARCHAR2,
197 p_validation_level IN NUMBER,
198 p_org_id IN NUMBER,
199 px_cu_tbl IN OUT NOCOPY EAM_EST_DATASTRUCTURES_PUB.EAM_CONSTRUCTION_UNITS_TBL,
200 x_return_status OUT NOCOPY VARCHAR2,
201 x_msg_count OUT NOCOPY NUMBER,
202 x_msg_data OUT NOCOPY VARCHAR2)
203 IS
204 BEGIN
205 EAM_CONSTRUCTION_EST_PVT.GET_CU_RECS(
206 p_api_version => p_api_version,
207 p_commit => p_commit,
208 p_init_msg_list => p_init_msg_list,
209 p_validation_level => p_validation_level,
210 p_org_id => p_org_id,
211 px_cu_tbl => px_cu_tbl,
212 x_return_status => x_return_status,
213 x_msg_count => x_msg_count,
214 x_msg_data => x_msg_data);
215 EXCEPTION
216 WHEN OTHERS THEN
217 x_return_status := 'E';
218 END GET_CU_RECS;
219
220 PROCEDURE GET_CU_ACTIVITIES(
221 p_api_version IN NUMBER,
222 p_commit IN VARCHAR2,
223 p_init_msg_list IN VARCHAR2,
224 p_validation_level IN NUMBER,
225 p_cu_id IN NUMBER,
226 x_activities_tbl OUT NOCOPY EAM_EST_DATASTRUCTURES_PUB.EAM_ESTIMATE_ASSOCIATIONS_TBL,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2)
230 IS
231 BEGIN
232 EAM_CONSTRUCTION_EST_PVT.GET_CU_ACTIVITIES(
233 p_api_version => p_api_version,
234 p_commit => p_commit,
235 p_init_msg_list => p_init_msg_list,
236 p_validation_level => p_validation_level,
237 p_cu_id => p_cu_id,
238 x_activities_tbl => x_activities_tbl,
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data);
242 EXCEPTION
243 WHEN OTHERS THEN
244 x_return_status := 'E';
245 END GET_CU_ACTIVITIES;
246
247 PROCEDURE CREATE_ESTIMATE(
248 p_api_version IN NUMBER,
249 p_commit IN VARCHAR2,
250 p_init_msg_list IN VARCHAR2,
251 p_validation_level IN NUMBER,
252 px_estimate_rec IN OUT NOCOPY EAM_EST_DATASTRUCTURES_PUB.EAM_CONSTRUCTION_ESTIMATE_REC,
253 x_return_status OUT NOCOPY VARCHAR2,
254 x_msg_count OUT NOCOPY NUMBER,
255 x_msg_data OUT NOCOPY VARCHAR2)
256 IS
257 BEGIN
258 EAM_CONSTRUCTION_EST_PVT.CREATE_ESTIMATE(
259 p_api_version => p_api_version,
260 p_commit => p_commit,
261 p_init_msg_list => p_init_msg_list,
262 p_validation_level => p_validation_level,
263 px_estimate_rec => px_estimate_rec,
264 x_return_status => x_return_status,
265 x_msg_count => x_msg_count,
266 x_msg_data => x_msg_data);
267 EXCEPTION
268 WHEN OTHERS THEN
269 x_return_status := 'E';
270 END CREATE_ESTIMATE;
271
272 PROCEDURE UPDATE_ESTIMATE(
273 p_api_version IN NUMBER,
274 p_commit IN VARCHAR2,
275 p_init_msg_list IN VARCHAR2,
276 p_validation_level IN NUMBER,
277 p_estimate_rec IN EAM_EST_DATASTRUCTURES_PUB.EAM_CONSTRUCTION_ESTIMATE_REC,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2)
281 IS
282 BEGIN
283 EAM_CONSTRUCTION_EST_PVT.UPDATE_ESTIMATE(
284 p_api_version => p_api_version,
285 p_commit => p_commit,
286 p_init_msg_list => p_init_msg_list,
287 p_validation_level => p_validation_level,
288 p_estimate_rec => p_estimate_rec,
289 x_return_status => x_return_status,
290 x_msg_count => x_msg_count,
291 x_msg_data => x_msg_data);
292 EXCEPTION
293 WHEN OTHERS THEN
294 x_return_status := 'E';
295 END UPDATE_ESTIMATE;
296
297 PROCEDURE SET_ACTIVITIES_FOR_CE(
298 p_api_version IN NUMBER,
302 p_ce_id IN NUMBER,
299 p_commit IN VARCHAR2,
300 p_init_msg_list IN VARCHAR2,
301 p_validation_level IN NUMBER,
303 px_activities_tbl IN OUT NOCOPY EAM_EST_DATASTRUCTURES_PUB.EAM_ESTIMATE_ASSOCIATIONS_TBL,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2)
307 IS
308 BEGIN
309 EAM_CONSTRUCTION_EST_PVT.SET_ACTIVITIES_FOR_CE(
310 p_api_version => p_api_version,
311 p_commit => p_commit,
312 p_init_msg_list => p_init_msg_list,
313 p_validation_level => p_validation_level,
314 p_ce_id => p_ce_id,
315 px_activities_tbl => px_activities_tbl,
316 x_return_status => x_return_status,
317 x_msg_count => x_msg_count,
318 x_msg_data => x_msg_data);
319 EXCEPTION
320 WHEN OTHERS THEN
321 x_return_status := 'E';
322 END SET_ACTIVITIES_FOR_CE;
323
324 PROCEDURE UPDATE_CE_WO_LNS_BY_GROUP_OPT(
325 p_api_version IN NUMBER,
326 p_commit IN VARCHAR2,
327 p_init_msg_list IN VARCHAR2,
328 p_validation_level IN NUMBER,
329 p_ce_wo_defaults IN EAM_EST_DATASTRUCTURES_PUB.EAM_CE_WO_DEFAULTS_REC,
330 x_return_status OUT NOCOPY VARCHAR2,
331 x_msg_count OUT NOCOPY NUMBER,
332 x_msg_data OUT NOCOPY VARCHAR2)
333 IS
334 BEGIN
335 EAM_CONSTRUCTION_EST_PVT.UPDATE_CE_WO_LNS_BY_GROUP_OPT(
336 p_api_version => p_api_version,
337 p_commit => p_commit,
338 p_init_msg_list => p_init_msg_list,
339 p_validation_level => p_validation_level,
340 p_ce_wo_defaults => p_ce_wo_defaults,
341 x_return_status => x_return_status,
342 x_msg_count => x_msg_count,
343 x_msg_data => x_msg_data);
344 EXCEPTION
345 WHEN OTHERS THEN
346 x_return_status := 'E';
347 END UPDATE_CE_WO_LNS_BY_GROUP_OPT;
348
349 PROCEDURE COPY_EST_WORKBENCH(
350 p_api_version IN NUMBER := 1.0
351 , p_init_msg_list IN VARCHAR2 := 'F'
352 , p_commit IN VARCHAR2
353 , p_src_estimate_id IN NUMBER
354 , p_org_id IN NUMBER
355 , p_cpy_estimate_id OUT NOCOPY NUMBER
356 , x_return_status OUT NOCOPY VARCHAR2
357 , x_msg_count OUT NOCOPY NUMBER
358 , x_msg_data OUT NOCOPY VARCHAR2
359 )
360 IS
361 BEGIN
362 EAM_CONSTRUCTION_EST_PVT.COPY_EST_WORKBENCH(
363 p_api_version => p_api_version
364 , p_init_msg_list => p_init_msg_list
365 , p_commit => p_commit
366 , p_src_estimate_id => p_src_estimate_id
367 , p_org_id => p_org_id
368 , p_cpy_estimate_id => p_cpy_estimate_id
369 , x_return_status => x_return_status
370 , x_msg_count => x_msg_count
371 , x_msg_data => x_msg_data
372 );
373 EXCEPTION
374 WHEN OTHERS THEN
375 x_return_status := 'E';
376 END COPY_EST_WORKBENCH;
377
378
379 End EAM_CONSTRUCTION_EST_PUB;