1 PACKAGE BODY EAM_DIRECT_ITEMS_DEFAULT_PVT AS
2 /* $Header: EAMVDIDB.pls 115.2 2003/09/26 03:48:56 baroy noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVDIDB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Spec of package EAM_DIRECT_ITEMS_DEFAULT_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 15-SEP-2003 Basanth Roy Initial Creation
21 ***************************************************************************/
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_DIRECT_ITEMS_DEFAULT_PVT';
23
24
25 /********************************************************************
26 * Function : get_di_seq_id
27 * Return : NUMBER
28 * Purpose : Function will return direct_item_sequence_id
29 *
30 **********************************************************************/
31
32 FUNCTION get_di_seq_id
33 RETURN NUMBER
34 IS
35 l_di_seq_id NUMBER := NULL;
36 BEGIN
37
38 SELECT wip_eam_di_seq_id_s.nextval
39 INTO l_di_seq_id
40 FROM sys.dual;
41
42 RETURN l_di_seq_id;
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 RETURN NULL;
47
48 END get_di_seq_id;
49
50
51
52 /********************************************************************
53 * Procedure : get_flex_eam_direct_items
54 * Return : NUMBER
55 **********************************************************************/
56
57
58 PROCEDURE get_flex_eam_direct_items
59 ( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
60 , x_eam_direct_items_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
61 )
62 IS
63 BEGIN
64
65 -- In the future call Flex APIs for defaults
66 x_eam_direct_items_rec := p_eam_direct_items_rec;
67
68 IF p_eam_direct_items_rec.attribute_category =FND_API.G_MISS_CHAR THEN
69 x_eam_direct_items_rec.attribute_category := NULL;
70 END IF;
71
72 IF p_eam_direct_items_rec.attribute1 = FND_API.G_MISS_CHAR THEN
73 x_eam_direct_items_rec.attribute1 := NULL;
74 END IF;
75
76 IF p_eam_direct_items_rec.attribute2 = FND_API.G_MISS_CHAR THEN
77 x_eam_direct_items_rec.attribute2 := NULL;
78 END IF;
79
80 IF p_eam_direct_items_rec.attribute3 = FND_API.G_MISS_CHAR THEN
81 x_eam_direct_items_rec.attribute3 := NULL;
82 END IF;
83
84 IF p_eam_direct_items_rec.attribute4 = FND_API.G_MISS_CHAR THEN
85 x_eam_direct_items_rec.attribute4 := NULL;
86 END IF;
87
88 IF p_eam_direct_items_rec.attribute5 = FND_API.G_MISS_CHAR THEN
89 x_eam_direct_items_rec.attribute5 := NULL;
90 END IF;
91
92 IF p_eam_direct_items_rec.attribute6 = FND_API.G_MISS_CHAR THEN
93 x_eam_direct_items_rec.attribute6 := NULL;
94 END IF;
95
96 IF p_eam_direct_items_rec.attribute7 = FND_API.G_MISS_CHAR THEN
97 x_eam_direct_items_rec.attribute7 := NULL;
98 END IF;
99
100 IF p_eam_direct_items_rec.attribute8 = FND_API.G_MISS_CHAR THEN
101 x_eam_direct_items_rec.attribute8 := NULL;
102 END IF;
103
104 IF p_eam_direct_items_rec.attribute9 = FND_API.G_MISS_CHAR THEN
105 x_eam_direct_items_rec.attribute9 := NULL;
106 END IF;
107
108 IF p_eam_direct_items_rec.attribute10 = FND_API.G_MISS_CHAR THEN
109 x_eam_direct_items_rec.attribute10 := NULL;
110 END IF;
111
112 IF p_eam_direct_items_rec.attribute11 = FND_API.G_MISS_CHAR THEN
113 x_eam_direct_items_rec.attribute11 := NULL;
114 END IF;
115
116 IF p_eam_direct_items_rec.attribute12 = FND_API.G_MISS_CHAR THEN
117 x_eam_direct_items_rec.attribute12 := NULL;
118 END IF;
119
120 IF p_eam_direct_items_rec.attribute13 = FND_API.G_MISS_CHAR THEN
121 x_eam_direct_items_rec.attribute13 := NULL;
122 END IF;
123
124 IF p_eam_direct_items_rec.attribute14 = FND_API.G_MISS_CHAR THEN
125 x_eam_direct_items_rec.attribute14 := NULL;
126 END IF;
127
128 IF p_eam_direct_items_rec.attribute15 = FND_API.G_MISS_CHAR THEN
129 x_eam_direct_items_rec.attribute15 := NULL;
130 END IF;
131
135 /*********************************************************************
132 END get_flex_eam_direct_items;
133
134
136 * Procedure : Attribute_Defaulting
137 * Parameters IN : Direct Items record
138 * Parameters OUT NOCOPY: Direct Items record after defaulting
139 * Mesg_Token_Table
140 * Return_Status
141 * Purpose : Attribute Defaulting will default the necessary null
142 * attribute with appropriate values.
143 **********************************************************************/
144
145 PROCEDURE Attribute_Defaulting
146 ( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
147 , x_eam_direct_items_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
148 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
149 , x_return_status OUT NOCOPY VARCHAR2
150 )
151 IS
152 l_out_eam_direct_items_rec EAM_PROCESS_WO_PUB.eam_direct_items_rec_type;
153 BEGIN
154
155 x_eam_direct_items_rec := p_eam_direct_items_rec;
156 -- x_eam_direct_items_rec := p_eam_direct_items_rec;
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158
159 IF p_eam_direct_items_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_CREATE
160 then
161 -- Defaulting direct_item_sequence_id
162 x_eam_direct_items_rec.direct_item_sequence_id := get_di_seq_id;
163 END IF;
164
165 -- Defaulting AUTO_REQUEST_MATERIAL flag.
166 IF p_eam_direct_items_rec.auto_request_material IS NULL OR
167 p_eam_direct_items_rec.auto_request_material = FND_API.G_MISS_CHAR
168 THEN
169 x_eam_direct_items_rec.auto_request_material := 'Y';
170 END IF;
171
172 -- Defaulting department_id
173 IF (p_eam_direct_items_rec.department_id IS NULL OR
174 p_eam_direct_items_rec.department_id = FND_API.G_MISS_NUM) AND
175 p_eam_direct_items_rec.operation_seq_num is not null AND
176 p_eam_direct_items_rec.organization_id is not null AND
177 p_eam_direct_items_rec.wip_entity_id is not null
178 THEN
179 IF p_eam_direct_items_rec.operation_seq_num = 1 THEN
180 x_eam_direct_items_rec.department_id := null;
181 ELSE
182 select department_id into x_eam_direct_items_rec.department_id
183 from wip_operations
184 where wip_entity_id = p_eam_direct_items_rec.wip_entity_id
185 and organization_id = p_eam_direct_items_rec.organization_id
186 and operation_seq_num = p_eam_direct_items_rec.operation_seq_num;
187
188 END IF;
189 END IF;
190
191 l_out_eam_direct_items_rec := x_eam_direct_items_rec;
192
193 get_flex_eam_direct_items
194 ( p_eam_direct_items_rec => x_eam_direct_items_rec
195 , x_eam_direct_items_rec => l_out_eam_direct_items_rec
196 );
197
198 x_eam_direct_items_rec := l_out_eam_direct_items_rec;
199
200 EXCEPTION
201 WHEN OTHERS THEN
205 , x_mesg_token_Tbl => x_mesg_token_tbl
202 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
203 ( p_message_name => NULL
204 , p_message_text => G_PKG_NAME || SQLERRM
206 );
207
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
209
210 END Attribute_Defaulting;
211
212
213 /******************************************************************
214 * Procedure : Populate_Null_Columns
215 * Parameters IN : Direct Items column record
216 * Old Direct Items Column Record
217 * Parameters OUT NOCOPY: Direct Items column record after populating
218 * Purpose : This procedure will look at the columns that the user
219 * has not filled in and will assign those columns a
220 * value from the old record.
221 * This procedure is not called for CREATE
222 ********************************************************************/
223 PROCEDURE Populate_Null_Columns
224 ( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
225 , p_old_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
226 , x_eam_direct_items_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
227 )
228 IS
229 BEGIN
230 x_eam_direct_items_rec := p_eam_direct_items_rec;
231 -- x_eam_direct_items_rec := p_eam_direct_items_rec;
232
233 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Processing null columns prior update'); END IF;
234
235
236 IF p_eam_direct_items_rec.description IS NULL OR
237 p_eam_direct_items_rec.description = FND_API.G_MISS_CHAR
238 THEN
239 x_eam_direct_items_rec.description := p_old_eam_direct_items_rec.description;
240 END IF;
241
242 IF p_eam_direct_items_rec.purchasing_category_id IS NULL OR
243 p_eam_direct_items_rec.purchasing_category_id = FND_API.G_MISS_NUM
244 THEN
245 x_eam_direct_items_rec.purchasing_category_id := p_old_eam_direct_items_rec.purchasing_category_id;
246 END IF;
247
248 IF p_eam_direct_items_rec.uom IS NULL OR
249 p_eam_direct_items_rec.uom = FND_API.G_MISS_CHAR
250 THEN
251 x_eam_direct_items_rec.uom := p_old_eam_direct_items_rec.uom;
252 END IF;
253
254 IF p_eam_direct_items_rec.suggested_vendor_name IS NULL OR
255 p_eam_direct_items_rec.suggested_vendor_name = FND_API.G_MISS_CHAR
256 THEN
257 x_eam_direct_items_rec.suggested_vendor_name := p_old_eam_direct_items_rec.suggested_vendor_name;
258 END IF;
259
260 IF p_eam_direct_items_rec.suggested_vendor_id IS NULL OR
261 p_eam_direct_items_rec.suggested_vendor_id = FND_API.G_MISS_NUM
262 THEN
263 x_eam_direct_items_rec.suggested_vendor_id := p_old_eam_direct_items_rec.suggested_vendor_id;
264 END IF;
265
266 IF p_eam_direct_items_rec.suggested_vendor_site IS NULL OR
267 p_eam_direct_items_rec.suggested_vendor_site = FND_API.G_MISS_CHAR
268 THEN
269 x_eam_direct_items_rec.suggested_vendor_site := p_old_eam_direct_items_rec.suggested_vendor_site;
270 END IF;
271
272 IF p_eam_direct_items_rec.suggested_vendor_site_id IS NULL OR
273 p_eam_direct_items_rec.suggested_vendor_site_id = FND_API.G_MISS_NUM
274 THEN
275 x_eam_direct_items_rec.suggested_vendor_site_id := p_old_eam_direct_items_rec.suggested_vendor_site_id;
276 END IF;
277
278 IF p_eam_direct_items_rec.suggested_vendor_contact IS NULL OR
279 p_eam_direct_items_rec.suggested_vendor_contact = FND_API.G_MISS_CHAR
280 THEN
281 x_eam_direct_items_rec.suggested_vendor_contact := p_old_eam_direct_items_rec.suggested_vendor_contact;
282 END IF;
283
284 IF p_eam_direct_items_rec.suggested_vendor_contact_id IS NULL OR
285 p_eam_direct_items_rec.suggested_vendor_contact_id = FND_API.G_MISS_NUM
286 THEN
287 x_eam_direct_items_rec.suggested_vendor_contact_id := p_old_eam_direct_items_rec.suggested_vendor_contact_id;
288 END IF;
289
290 IF p_eam_direct_items_rec.suggested_vendor_phone IS NULL OR
291 p_eam_direct_items_rec.suggested_vendor_phone = FND_API.G_MISS_CHAR
292 THEN
293 x_eam_direct_items_rec.suggested_vendor_phone := p_old_eam_direct_items_rec.suggested_vendor_phone;
294 END IF;
295
296 IF p_eam_direct_items_rec.suggested_vendor_item_num IS NULL OR
297 p_eam_direct_items_rec.suggested_vendor_item_num = FND_API.G_MISS_CHAR
298 THEN
299 x_eam_direct_items_rec.suggested_vendor_item_num := p_old_eam_direct_items_rec.suggested_vendor_item_num;
300 END IF;
301
302 IF p_eam_direct_items_rec.unit_price IS NULL OR
303 p_eam_direct_items_rec.unit_price = FND_API.G_MISS_NUM
304 THEN
305 x_eam_direct_items_rec.unit_price := p_old_eam_direct_items_rec.unit_price;
306 END IF;
307
308 IF p_eam_direct_items_rec.department_id IS NULL OR
309 p_eam_direct_items_rec.department_id = FND_API.G_MISS_NUM
310 THEN
314
311 x_eam_direct_items_rec.department_id := p_old_eam_direct_items_rec.department_id;
312 END IF;
313
315 IF p_eam_direct_items_rec.need_by_date IS NULL OR
316 p_eam_direct_items_rec.need_by_date = FND_API.G_MISS_DATE
317 THEN
318 x_eam_direct_items_rec.need_by_date := p_old_eam_direct_items_rec.need_by_date;
319 END IF;
320
321 IF p_eam_direct_items_rec.required_quantity IS NULL OR
322 p_eam_direct_items_rec.required_quantity = FND_API.G_MISS_NUM
323 THEN
324 x_eam_direct_items_rec.required_quantity := p_old_eam_direct_items_rec.required_quantity;
325 END IF;
326
327 --
328 -- Populate Null or missng flex field columns
329 --
330 IF p_eam_direct_items_rec.attribute_category IS NULL OR
331 p_eam_direct_items_rec.attribute_category = FND_API.G_MISS_CHAR
332 THEN
333 x_eam_direct_items_rec.attribute_category := p_old_eam_direct_items_rec.attribute_category;
334
335 END IF;
336
337 IF p_eam_direct_items_rec.attribute1 = FND_API.G_MISS_CHAR OR
338 p_eam_direct_items_rec.attribute1 IS NULL
339 THEN
340 x_eam_direct_items_rec.attribute1 := p_old_eam_direct_items_rec.attribute1;
341 END IF;
342
343 IF p_eam_direct_items_rec.attribute2 = FND_API.G_MISS_CHAR OR
344 p_eam_direct_items_rec.attribute2 IS NULL
345 THEN
346 x_eam_direct_items_rec.attribute2 := p_old_eam_direct_items_rec.attribute2;
347 END IF;
348
349 IF p_eam_direct_items_rec.attribute3 = FND_API.G_MISS_CHAR OR
350 p_eam_direct_items_rec.attribute3 IS NULL
351 THEN
352 x_eam_direct_items_rec.attribute3 := p_old_eam_direct_items_rec.attribute3;
353 END IF;
354
355 IF p_eam_direct_items_rec.attribute4 = FND_API.G_MISS_CHAR OR
356 p_eam_direct_items_rec.attribute4 IS NULL
357 THEN
358 x_eam_direct_items_rec.attribute4 := p_old_eam_direct_items_rec.attribute4;
359 END IF;
360
361 IF p_eam_direct_items_rec.attribute5 = FND_API.G_MISS_CHAR OR
362 p_eam_direct_items_rec.attribute5 IS NULL
363 THEN
364 x_eam_direct_items_rec.attribute5 := p_old_eam_direct_items_rec.attribute5;
365 END IF;
366
367 IF p_eam_direct_items_rec.attribute6 = FND_API.G_MISS_CHAR OR
368 p_eam_direct_items_rec.attribute6 IS NULL
369 THEN
370 x_eam_direct_items_rec.attribute6 := p_old_eam_direct_items_rec.attribute6;
371 END IF;
372
373 IF p_eam_direct_items_rec.attribute7 = FND_API.G_MISS_CHAR OR
374 p_eam_direct_items_rec.attribute7 IS NULL
375 THEN
376 x_eam_direct_items_rec.attribute7 := p_old_eam_direct_items_rec.attribute7;
377 END IF;
378
379 IF p_eam_direct_items_rec.attribute8 = FND_API.G_MISS_CHAR OR
380 p_eam_direct_items_rec.attribute8 IS NULL
381 THEN
382 x_eam_direct_items_rec.attribute8 := p_old_eam_direct_items_rec.attribute8;
383 END IF;
384
385 IF p_eam_direct_items_rec.attribute9 = FND_API.G_MISS_CHAR OR
386 p_eam_direct_items_rec.attribute9 IS NULL
387 THEN
388 x_eam_direct_items_rec.attribute9 := p_old_eam_direct_items_rec.attribute9;
389 END IF;
390
391 IF p_eam_direct_items_rec.attribute10 = FND_API.G_MISS_CHAR OR
392 p_eam_direct_items_rec.attribute10 IS NULL
393 THEN
394 x_eam_direct_items_rec.attribute10 := p_old_eam_direct_items_rec.attribute10;
395 END IF;
396
397 IF p_eam_direct_items_rec.attribute11 = FND_API.G_MISS_CHAR OR
398 p_eam_direct_items_rec.attribute11 IS NULL
399 THEN
400 x_eam_direct_items_rec.attribute11 := p_old_eam_direct_items_rec.attribute11;
401 END IF;
402
403 IF p_eam_direct_items_rec.attribute12 = FND_API.G_MISS_CHAR OR
404 p_eam_direct_items_rec.attribute12 IS NULL
405 THEN
406 x_eam_direct_items_rec.attribute12 := p_old_eam_direct_items_rec.attribute12;
407 END IF;
408
409 IF p_eam_direct_items_rec.attribute13 = FND_API.G_MISS_CHAR OR
410 p_eam_direct_items_rec.attribute13 IS NULL
411 THEN
412 x_eam_direct_items_rec.attribute13 := p_old_eam_direct_items_rec.attribute13;
413 END IF;
414
415 IF p_eam_direct_items_rec.attribute14 = FND_API.G_MISS_CHAR OR
416 p_eam_direct_items_rec.attribute14 IS NULL
417 THEN
418 x_eam_direct_items_rec.attribute14 := p_old_eam_direct_items_rec.attribute14;
419 END IF;
420
421 IF p_eam_direct_items_rec.attribute15 = FND_API.G_MISS_CHAR OR
422 p_eam_direct_items_rec.attribute15 IS NULL
423 THEN
424 x_eam_direct_items_rec.attribute15 := p_old_eam_direct_items_rec.attribute15;
425 END IF;
426
427 IF p_eam_direct_items_rec.auto_request_material = FND_API.G_MISS_CHAR OR
431 END IF;
428 p_eam_direct_items_rec.auto_request_material IS NULL
429 THEN
430 x_eam_direct_items_rec.auto_request_material := p_old_eam_direct_items_rec.auto_request_material;
432
433
434 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Done processing null columns prior update'); END IF;
435
436
437 END Populate_Null_Columns;
438
439
440
441 /******************************************************************
442 * Procedure : GetDI_In_Op1
443 * Parameters IN : Direct Items table
444 * Organization_ID
445 Wip_Entity_Id
446 * Parameters OUT NOCOPY: Direct Items table after populating
447 * Purpose : This procedure will find all the direct items that are
448 * in operation seq num 1 and append them to the table.
449 * This procedure is called only when there exists any DI in operation 1
450 ********************************************************************/
451
452 PROCEDURE GetDI_In_Op1
453 ( p_eam_direct_items_tbl IN EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type
454 , p_organization_id IN NUMBER
455 , p_wip_entity_id IN NUMBER
456 , x_eam_direct_items_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type
457 )
458 IS
459 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type := p_eam_direct_items_tbl;
460 k NUMBER := l_eam_direct_items_tbl.COUNT ;
461
462 CURSOR DIRECT_ITEMS_CUR IS
463 SELECT
464 WIP_ENTITY_ID WIP_ENTITY_ID
465 , ORGANIZATION_ID ORGANIZATION_ID
466 , OPERATION_SEQ_NUM OPERATION_SEQ_NUM
467 , DIRECT_ITEM_SEQUENCE_ID DIRECT_ITEM_SEQUENCE_ID
468 , 2 TRANSACTION_TYPE
469 FROM wip_eam_direct_items
470 WHERE organization_id = p_organization_id
471 and wip_entity_id = p_wip_entity_id
472 and operation_seq_num = 1;
473
474
475 BEGIN
476
477 FOR direc IN DIRECT_ITEMS_CUR LOOP
478 k := k + 1 ;
479
480 l_eam_direct_items_tbl(k).WIP_ENTITY_ID := direc.WIP_ENTITY_ID;
481 l_eam_direct_items_tbl(k).ORGANIZATION_ID := direc.ORGANIZATION_ID;
482 l_eam_direct_items_tbl(k).OPERATION_SEQ_NUM := direc.OPERATION_SEQ_NUM;
483 l_eam_direct_items_tbl(k).DIRECT_ITEM_SEQUENCE_ID := direc.DIRECT_ITEM_SEQUENCE_ID;
484 l_eam_direct_items_tbl(k).TRANSACTION_TYPE := direc.TRANSACTION_TYPE ;
485
486 END LOOP;
487 x_eam_direct_items_tbl := l_eam_direct_items_tbl ;
488
489 END GetDI_In_Op1 ;
490
491
492 /******************************************************************
493 * Procedure : Change_OpSeqNum1
494 * Parameters IN : Direct Items column record
495 * Operation Sequence Number
496 Department Id
497 * Parameters OUT NOCOPY: Direct Items column record after changing
498 * Purpose : This procedure will change the operation seq num from 1
499 * to the newly created operation ( p_operation_seq_num )
500 * and accordingly the department id
501 * This procedure is called only when there exists any direct items in operation 1
502 ********************************************************************/
503 PROCEDURE Change_OpSeqNum1
504 ( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
505 , p_operation_seq_num IN NUMBER
506 , p_department_id IN NUMBER
507 , x_eam_direct_items_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
508 )
509 IS
510 l_eam_direct_items_rec EAM_PROCESS_WO_PUB.eam_direct_items_rec_type := p_eam_direct_items_rec;
511
512 BEGIN
513
514 IF ( l_eam_direct_items_rec.operation_seq_num = 1 ) THEN
515 l_eam_direct_items_rec.operation_seq_num := p_operation_seq_num ;
516 l_eam_direct_items_rec.department_id := p_department_id ;
517 END IF;
518
519 x_eam_direct_items_rec := l_eam_direct_items_rec ;
520
521 END Change_OpSeqNum1;
522
523
524
525 END EAM_DIRECT_ITEMS_DEFAULT_PVT;
[Home] [Help]