[Home] [Help]
PACKAGE BODY: APPS.WIP_TRANSACTION_PVT
Source
1 PACKAGE BODY WIP_Transaction_PVT AS
2 /* $Header: WIPVTXNB.pls 120.1 2005/12/13 11:33:05 kboonyap noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WIP_Transaction_PVT';
7
8 -- Process_OSP_Transaction
9
10 PROCEDURE Process_OSP_Transaction
11 ( p_OSP_rec IN WIP_Transaction_PUB.Res_Rec_Type
12 , p_validation_level IN NUMBER DEFAULT COMPLETE
13 , p_return_status OUT NOCOPY VARCHAR2
14 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
15 , p_msg_count OUT NOCOPY NUMBER
16 , p_msg_data OUT NOCOPY VARCHAR2
17 )
18 IS
19 l_direct_item_flag NUMBER;
20 l_return_status VARCHAR2(1);
21 l_OSP_rec WIP_Transaction_PUB.Res_Rec_Type :=
22 WIP_Transaction_PUB.G_MISS_RES_REC;
23 l_ShopFloorMove_rec WIP_Transaction_PUB.ShopFloorMove_Rec_Type :=
24 WIP_Transaction_PUB.G_MISS_SHOPFLOORMOVE_REC;
25
26 /* this variable is passed as a value for the p_old_res_rec
27 in the procedure wip_validate_res.Attributes. Enhancement No: 2665334
28 */
29
30 l_old_OSP_rec WIP_Transaction_PUB.Res_Rec_Type :=
31 WIP_Transaction_PUB.G_MISS_RES_REC;
32
33 l_old_ShopFloorMove_rec WIP_Transaction_PUB.Shopfloormove_Rec_Type :=
34 WIP_Transaction_PUB.G_MISS_SHOPFLOORMOVE_REC;
35
36 l_log_level NUMBER := fnd_log.g_current_runtime_level;
37 l_error_msg VARCHAR2(240);
38 l_process_phase VARCHAR2(3);
39 l_params wip_logger.param_tbl_t;
40
41 BEGIN
42 l_process_phase := '1';
43 -- write parameter value to log file
44 IF (l_log_level <= wip_constants.trace_logging) THEN
45 l_params(1).paramName := 'p_validation_level';
46 l_params(1).paramValue := p_validation_level;
47 l_params(2).paramName := 'p_init_msg_list';
48 l_params(2).paramValue := p_init_msg_list;
49
50 wip_logger.entryPoint(p_procName => 'WIP_Transaction_PVT.Process_OSP_Transaction',
51 p_params => l_params,
52 x_returnStatus => l_return_status);
53 END IF;
54 -- Initialize message list if p_init_msg_list is set to TRUE.
55 IF FND_API.to_Boolean( p_init_msg_list ) THEN
56 FND_MSG_PUB.initialize;
57 END IF;
58
59 -- Initialize API return status to success
60 p_return_status := FND_API.G_RET_STS_SUCCESS;
61 l_OSP_rec := p_OSP_rec;
62 l_process_phase := '2';
63 -- Check for direct items
64 CST_eamCost_PUB.check_if_direct_item (
65 p_api_version => 1.0,
66 p_validation_level => p_validation_level,
67 p_interface_txn_id => l_OSP_rec.rcv_transaction_id,
68 x_direct_item_flag => l_direct_item_flag,
69 x_return_status => p_return_status,
70 x_msg_count => p_msg_count,
71 x_msg_data => p_msg_data);
72
73 if (p_return_status <> fnd_api.g_ret_sts_success) then
74 Raise fnd_api.g_exc_unexpected_error ;
75 end if;
76
77 if (l_direct_item_flag = 1) then
78 l_process_phase := '3';
79 CST_eamCost_PUB.process_direct_item_txn (
80 p_api_version => 1.0,
81 p_validation_level => p_validation_level,
82 p_directItem_rec => l_osp_rec,
83 x_directItem_rec => l_osp_rec,
84 x_return_status => p_return_status,
85 x_msg_count => p_msg_count,
86 x_msg_data => p_msg_data);
87
88 if (p_return_status <> fnd_api.g_ret_sts_success) then
89 Raise fnd_api.g_exc_unexpected_error ;
90 end if;
91 else
92 l_process_phase := '4';
93 --Default the OSP Record
94 WIP_Default_Res.Attributes(
95 p_Res_rec => l_OSP_rec
96 ,x_Res_rec => l_OSP_rec);
97 end if;
98 l_process_phase := '5';
99 l_OSP_rec := WIP_Res_Util.Convert_Miss_To_Null(l_OSP_rec);
100 l_process_phase := '6';
101 --Validate the OSP Record
102 WIP_Validate_Res.Attributes(x_return_status => l_return_status
103 ,p_Res_rec => l_OSP_rec
104 ,p_validation_level => p_validation_level
105 ,p_old_Res_rec => l_old_OSP_rec);
106
107 l_process_phase := '7';
108 if (l_return_status <> FND_API.G_RET_STS_ERROR) then
109 WIP_Validate_Res.Entity(
110 x_return_status => l_return_status
111 ,p_Res_rec => l_OSP_rec
112 ,p_validation_level => p_validation_level
113 ,p_old_Res_rec => l_old_OSP_rec);
114 end if;
115 l_process_phase := '8';
116 --Create the OSP record (resource transaction)
117 if (l_return_status <> FND_API.G_RET_STS_ERROR) then
118 WIP_Res_Util.Insert_Row(p_Res_rec => l_OSP_rec);
119 end if;
120 l_process_phase := '9';
121 if (l_direct_item_flag <> 1) then
122 --Perform the Move transaction if the autocharge is PO Move
123 -- do not do this if direct item
124 if (l_return_status <> FND_API.G_RET_STS_ERROR) then
125 IF l_OSP_rec.autocharge_type = WIP_CONSTANTS.PO_MOVE THEN
126 l_process_phase := '10';
127 --Default the ShopFloorMove record
128 WIP_Default_ShopFloorMove.Attributes(
129 p_ShopFloorMove_rec => l_ShopFloorMove_rec,
130 x_ShopFloorMove_rec => l_ShopFloorMove_rec,
131 p_OSP_rec => l_OSP_rec);
132 l_process_phase := '11';
133 l_ShopFloorMove_rec :=
134 WIP_ShopFloorMove_Util.Convert_Miss_To_Null(l_ShopFloorMove_rec);
135 l_process_phase := '12';
136 --Validate the ShopFloorMove record
137 WIP_Validate_ShopFloorMove.Attributes(
138 x_return_status => l_return_status,
139 p_ShopFloorMove_rec => l_ShopFloorMove_rec,
140 p_validation_level => p_validation_level
141 ,p_old_ShopFloorMove_rec => l_old_ShopFloorMove_rec);
142 l_process_phase := '13';
143 if (l_return_status <> FND_API.G_RET_STS_ERROR) then
144 l_process_phase := '14';
145 WIP_Validate_ShopFloorMove.Entity(
146 x_return_status => l_return_status,
147 p_ShopFloorMove_rec => l_ShopFloorMove_rec,
148 p_validation_level => p_validation_level
149 ,p_old_ShopFloorMove_rec => l_old_ShopFloorMove_rec);
150 end if;
151 l_process_phase := '15';
152 --Create the ShopFloorMove record
153 if (l_return_status <> FND_API.G_RET_STS_ERROR) then
154 WIP_ShopFloorMove_Util.Insert_Row(
155 p_ShopFloorMove_rec => l_ShopFloorMove_rec);
156 end if;
157 l_process_phase := '16';
158 end if; -- if autocharge_type = PO_MOVE
159 END IF;
160 END IF; -- end of check for direct items
161
162 FND_MSG_PUB.Count_And_Get
163 ( p_encoded => FND_API.G_FALSE ,
164 p_count => p_msg_count ,
165 p_data => p_msg_data
166 );
167 p_return_status := l_return_status;
168 -- write to the log file
169 IF (l_log_level <= wip_constants.trace_logging) THEN
170 wip_logger.exitPoint(p_procName => 'WIP_Transaction_PVT.Process_OSP_Transaction',
171 p_procReturnStatus => p_return_status,
172 p_msg => 'procedure complete',
173 x_returnStatus => l_return_status);
174 END IF;
175 EXCEPTION
176
177 WHEN FND_API.G_EXC_ERROR THEN
178 l_OSP_rec.return_status := FND_API.G_RET_STS_ERROR;
179
180 p_return_status := l_OSP_rec.return_status ;
181
182 FND_MSG_PUB.Count_And_Get
183 ( p_count => p_msg_count
184 , p_data => p_msg_data
185 );
186 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
187 ' unexpected error: ' || p_msg_data;
188 IF (l_log_level <= wip_constants.trace_logging) THEN
189 wip_logger.exitPoint(
190 p_procName => 'WIP_Transaction_PVT.Process_OSP_Transaction',
191 p_procReturnStatus => p_return_status,
192 p_msg => l_error_msg,
193 x_returnStatus => l_return_status);
194 END IF;
195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
196
197 l_OSP_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 p_return_status := l_OSP_rec.return_status ;
199
200 FND_MSG_PUB.Count_And_Get
201 ( p_count => p_msg_count
202 , p_data => p_msg_data
203 );
204 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
205 ' unexpected error: ' || p_msg_data;
206 IF (l_log_level <= wip_constants.trace_logging) THEN
207 wip_logger.exitPoint(
208 p_procName => 'WIP_Transaction_PVT.Process_OSP_Transaction',
209 p_procReturnStatus => p_return_status,
210 p_msg => l_error_msg,
211 x_returnStatus => l_return_status);
212 END IF;
213
214 WHEN OTHERS THEN
215
216 l_OSP_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217
218 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
219 THEN
220 FND_MSG_PUB.Add_Exc_Msg
221 ( G_PKG_NAME
222 , 'Process_OSP_Transaction' || SQLERRM
223 );
224 END IF;
225 p_return_status := l_OSP_rec.return_status ;
226
227 FND_MSG_PUB.Count_And_Get
228 ( p_count => p_msg_count
229 , p_data => p_msg_data
230 );
231 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
232 ' unexpected error: ' || SQLERRM;
233 IF (l_log_level <= wip_constants.trace_logging) THEN
234 wip_logger.exitPoint(
235 p_procName => 'WIP_Transaction_PVT.Process_OSP_Transaction',
236 p_procReturnStatus => p_return_status,
237 p_msg => l_error_msg,
238 x_returnStatus => l_return_status);
239 END IF;
240 END Process_OSP_Transaction;
241
242
243 -- Start of Comments
244 -- API name Get_Transaction
245 -- Type Private
246 -- Function
247 --
248 -- Pre-reqs
249 --
250 -- Parameters
251 --
252 -- Version Current version = 1.0
253 -- Initial version = 1.0
254 --
255 -- Notes
256 --
257 -- End of Comments
258
259 PROCEDURE Get_Transaction
260 ( p_api_version_number IN NUMBER
261 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
262 , x_return_status OUT NOCOPY VARCHAR2
263 , x_msg_count OUT NOCOPY NUMBER
264 , x_msg_data OUT NOCOPY VARCHAR2
265 , p_dummy IN VARCHAR2
266 , x_WIPTransaction_tbl OUT NOCOPY WIP_Transaction_PUB.Wiptransaction_Tbl_Type
267 , x_Res_tbl OUT NOCOPY WIP_Transaction_PUB.Res_Tbl_Type
268 , x_ShopFloorMove_tbl OUT NOCOPY WIP_Transaction_PUB.Shopfloormove_Tbl_Type
269 )
270 IS
271 l_api_version_number CONSTANT NUMBER := 1.0;
272 l_api_name CONSTANT VARCHAR2(30):= 'Get_Transaction';
273 l_WIPTransaction_tbl WIP_Transaction_PUB.Wiptransaction_Tbl_Type;
274 l_Res_tbl WIP_Transaction_PUB.Res_Tbl_Type;
275 l_x_Res_tbl WIP_Transaction_PUB.Res_Tbl_Type;
276 l_ShopFloorMove_tbl WIP_Transaction_PUB.Shopfloormove_Tbl_Type;
277 l_x_ShopFloorMove_tbl WIP_Transaction_PUB.Shopfloormove_Tbl_Type;
278 BEGIN
279
280 -- Standard call to check for call compatibility
281
282 IF NOT FND_API.Compatible_API_Call
283 ( l_api_version_number
284 , p_api_version_number
285 , l_api_name
286 , G_PKG_NAME
287 )
288 THEN
289 NULL;
290 END IF;
291
292 -- Initialize message list.
293
294 IF FND_API.to_Boolean(p_init_msg_list) THEN
295 FND_MSG_PUB.initialize;
296 END IF;
297
298 -- Get WIPTransaction ( parent = WIPTransaction )
299 -- Since I am using WIP Transactions as a dummy parent, the following lines of code don't make sense.
300 /*
301 l_WIPTransaction_tbl := WIP_Wiptransaction_Util.Query_Rows
302 ( p_dummy => p_dummy
303 );
304
305 -- Loop over WIPTransaction's children
306
307 FOR I1 IN 1..l_WIPTransaction_tbl.COUNT LOOP
308
309 -- Get OSP ( parent = WIPTransaction )
310
311 l_OSP_tbl := WIP_Res_Util.Query_Rows
312 ( p_dummy => l_WIPTransaction_tbl(I1).dummy
313 );
314
315 FOR I2 IN 1..l_Res_tbl.COUNT LOOP
316 l_Res_tbl(I2).WIPTransaction_Index := I1;
317 l_x_Res_tbl
318 (l_x_Res_tbl.COUNT + 1) := l_Res_tbl(I2);
319 END LOOP;
320
321
322 -- Get ShopFloorMove ( parent = WIPTransaction )
323
324 l_ShopFloorMove_tbl := WIP_Shopfloormove_Util.Query_Rows
325 ( p_dummy => l_WIPTransaction_tbl(I1).dummy
326 );
327
328 FOR I2 IN 1..l_ShopFloorMove_tbl.COUNT LOOP
329 l_ShopFloorMove_tbl(I2).WIPTransaction_Index := I1;
330 l_x_ShopFloorMove_tbl
331 (l_x_ShopFloorMove_tbl.COUNT + 1) := l_ShopFloorMove_tbl(I2);
332 END LOOP;
333
334 END LOOP;
335
336 */
337 -- Load out parameters
338
339 x_WIPTransaction_tbl := l_WIPTransaction_tbl;
340 x_Res_tbl := l_x_Res_tbl;
341 x_ShopFloorMove_tbl := l_x_ShopFloorMove_tbl;
342
343 -- Set return status
344
345 x_return_status := FND_API.G_RET_STS_SUCCESS;
346
347 -- Get message count and data
348
349 FND_MSG_PUB.Count_And_Get
350 ( p_count => x_msg_count
351 , p_data => x_msg_data
352 );
353
354
355 EXCEPTION
356
357 WHEN FND_API.G_EXC_ERROR THEN
358
359 x_return_status := FND_API.G_RET_STS_ERROR;
360
361 -- Get message count and data
362
363 FND_MSG_PUB.Count_And_Get
364 ( p_count => x_msg_count
365 , p_data => x_msg_data
366 );
367
368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
369
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
371
372 -- Get message count and data
373
374 FND_MSG_PUB.Count_And_Get
375 ( p_count => x_msg_count
376 , p_data => x_msg_data
377 );
378
379 WHEN OTHERS THEN
380
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
382
383 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
384 THEN
385 FND_MSG_PUB.Add_Exc_Msg
386 ( G_PKG_NAME
387 , 'Get_Transaction'
388 );
389 END IF;
390
391 -- Get message count and data
392
393 FND_MSG_PUB.Count_And_Get
394 ( p_count => x_msg_count
395 , p_data => x_msg_data
396 );
397
398 END Get_Transaction;
399
400
401 -- ----------------------------
402 -- Process_Resource_Transaction
403 -- ----------------------------
404
405 PROCEDURE Process_Resource_Transaction
406 ( p_res_txn_rec IN WIP_Transaction_PUB.Res_Rec_Type
407 , p_validation_level IN NUMBER DEFAULT COMPLETE
408 , p_return_status OUT NOCOPY VARCHAR2
409 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
410 , p_msg_count OUT NOCOPY NUMBER
411 , p_msg_data OUT NOCOPY VARCHAR2
412 )
413 IS
414 l_return_status VARCHAR2(1);
415 l_res_txn_rec WIP_Transaction_PUB.Res_Rec_Type;
416
417 /* this variable is passed as a value for the p_old_res_rec
418 in the procedure wip_validate_res.Attributes. Enhancement No: 2665334
419 */
420
421 l_old_OSP_rec WIP_Transaction_PUB.Res_Rec_Type :=
422 WIP_Transaction_PUB.G_MISS_RES_REC;
423
424 l_log_level NUMBER := fnd_log.g_current_runtime_level;
425 l_error_msg VARCHAR2(240);
426 l_process_phase VARCHAR2(3);
427 l_params wip_logger.param_tbl_t;
428 BEGIN
429 l_process_phase := '1';
430 -- write parameter value to log file
431 IF (l_log_level <= wip_constants.trace_logging) THEN
432 l_params(1).paramName := 'p_validation_level';
433 l_params(1).paramValue := p_validation_level;
434 l_params(2).paramName := 'p_init_msg_list';
435 l_params(2).paramValue := p_init_msg_list;
436
437 wip_logger.entryPoint(p_procName => 'WIP_Transaction_PVT.Process_Resource_Transaction',
438 p_params => l_params,
439 x_returnStatus => l_return_status);
440 END IF;
441 -- Initialize message list if p_init_msg_list is set to TRUE.
442 IF FND_API.to_Boolean( p_init_msg_list ) THEN
443 FND_MSG_PUB.initialize;
444 END IF;
445
446 -- Initialize API return status to success
447 l_return_status := FND_API.G_RET_STS_SUCCESS;
448
449 l_res_txn_rec := p_res_txn_rec;
450 l_process_phase := '2';
451 --Default the Resource Record
452 WIP_Default_Res.Attributes(
453 p_Res_rec => l_res_txn_rec
454 ,x_Res_rec => l_res_txn_rec);
455 l_process_phase := '3';
456
457 WIP_Res_Util.Print_Record(l_res_txn_rec);
458 l_process_phase := '4';
459 l_res_txn_rec := WIP_Res_Util.Convert_Miss_To_Null(l_res_txn_rec);
460 l_process_phase := '5';
461 -- Validate the Resource Record in 2 stages :
462 -- 1. The Resource Record Attributes
463 -- 2. The Resource Record Entity as such
464
465 WIP_Validate_Res.Attributes(
466 x_return_status => l_return_status
467 ,p_Res_rec => l_res_txn_rec
468 ,p_validation_level => p_validation_level
469 ,p_old_Res_rec => l_old_OSP_rec);
470
471 l_process_phase := '6';
472 WIP_Validate_Res.Entity(
473 x_return_status => l_return_status
474 ,p_Res_rec => l_res_txn_rec
475 ,p_validation_level => p_validation_level
476 ,p_old_Res_rec => l_old_OSP_rec);
477
478 l_process_phase := '7';
479 --Create the Resource record (resource transaction)
480 WIP_Res_Util.Insert_Row(p_Res_rec => l_res_txn_rec);
481 l_process_phase := '8';
482 FND_MSG_PUB.Count_And_Get
483 ( p_encoded => FND_API.G_FALSE ,
484 p_count => p_msg_count ,
485 p_data => p_msg_data
486 );
487
488 p_return_status := l_return_status;
489 -- write to the log file
490 IF (l_log_level <= wip_constants.trace_logging) THEN
491 wip_logger.exitPoint(p_procName => 'WIP_Transaction_PVT.Process_Resource_Transaction',
492 p_procReturnStatus => p_return_status,
493 p_msg => 'procedure complete',
494 x_returnStatus => l_return_status);
495 END IF;
496 EXCEPTION
497
498 WHEN FND_API.G_EXC_ERROR THEN
499 l_res_txn_rec.return_status := FND_API.G_RET_STS_ERROR;
500 l_error_msg := 'process_phase = ' || l_process_phase || ';';
501 IF (l_log_level <= wip_constants.trace_logging) THEN
502 wip_logger.exitPoint(
503 p_procName => 'WIP_Transaction_PVT.Process_Resource_Transaction',
504 p_procReturnStatus => p_return_status,
505 p_msg => l_error_msg,
506 x_returnStatus => l_return_status);
507 END IF;
508 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
509 l_res_txn_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
510 l_error_msg := 'process_phase = ' || l_process_phase || ';';
511 IF (l_log_level <= wip_constants.trace_logging) THEN
512 wip_logger.exitPoint(
513 p_procName => 'WIP_Transaction_PVT.Process_Resource_Transaction',
514 p_procReturnStatus => p_return_status,
515 p_msg => l_error_msg,
516 x_returnStatus => l_return_status);
517 END IF;
518 WHEN OTHERS THEN
519 l_res_txn_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520
521 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
522 THEN
523 FND_MSG_PUB.Add_Exc_Msg
524 ( G_PKG_NAME
525 , 'Process_Resource_Transaction' || SQLERRM
526 );
527 END IF;
528 l_error_msg := 'process_phase = ' || l_process_phase || ';' ||
529 ' unexpected error: ' || SQLERRM;
530 IF (l_log_level <= wip_constants.trace_logging) THEN
531 wip_logger.exitPoint(
532 p_procName => 'WIP_Transaction_PVT.Process_Resource_Transaction',
533 p_procReturnStatus => p_return_status,
534 p_msg => l_error_msg,
535 x_returnStatus => l_return_status);
536 END IF;
537 END Process_Resource_Transaction;
538
539
540
541 END WIP_Transaction_PVT;