1 PACKAGE BODY EAM_DIRECT_ITEMS_DEFAULT_PVT AS
2 /* $Header: EAMVDIDB.pls 120.1 2009/11/19 09:29:12 vchidura ship $ */
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
132 END get_flex_eam_direct_items;
133
134
135 /*********************************************************************
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
202 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
203 ( p_message_name => NULL
204 , p_message_text => G_PKG_NAME || SQLERRM
205 , x_mesg_token_Tbl => x_mesg_token_tbl
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 IF p_eam_direct_items_rec.description IS NULL OR
236 p_eam_direct_items_rec.description = FND_API.G_MISS_CHAR
237 THEN
238 x_eam_direct_items_rec.description := p_old_eam_direct_items_rec.description;
239 END IF;
240
241 IF p_eam_direct_items_rec.purchasing_category_id IS NULL OR
242 p_eam_direct_items_rec.purchasing_category_id = FND_API.G_MISS_NUM
243 THEN
244 x_eam_direct_items_rec.purchasing_category_id := p_old_eam_direct_items_rec.purchasing_category_id;
245 END IF;
246
247 IF p_eam_direct_items_rec.uom IS NULL OR
248 p_eam_direct_items_rec.uom = FND_API.G_MISS_CHAR
249 THEN
250 x_eam_direct_items_rec.uom := p_old_eam_direct_items_rec.uom;
251 END IF;
252
253 IF p_eam_direct_items_rec.suggested_vendor_name IS NULL OR
254 p_eam_direct_items_rec.suggested_vendor_name = FND_API.G_MISS_CHAR
255 THEN
256 x_eam_direct_items_rec.suggested_vendor_name := p_old_eam_direct_items_rec.suggested_vendor_name;
257 END IF;
258
259 IF p_eam_direct_items_rec.suggested_vendor_id IS NULL OR
260 p_eam_direct_items_rec.suggested_vendor_id = FND_API.G_MISS_NUM
261 THEN
262 x_eam_direct_items_rec.suggested_vendor_id := p_old_eam_direct_items_rec.suggested_vendor_id;
263 END IF;
264
265 IF p_eam_direct_items_rec.suggested_vendor_site IS NULL OR
266 p_eam_direct_items_rec.suggested_vendor_site = FND_API.G_MISS_CHAR
267 THEN
268 x_eam_direct_items_rec.suggested_vendor_site := p_old_eam_direct_items_rec.suggested_vendor_site;
269 END IF;
270
271 IF p_eam_direct_items_rec.suggested_vendor_site_id IS NULL OR
272 p_eam_direct_items_rec.suggested_vendor_site_id = FND_API.G_MISS_NUM
273 THEN
274 x_eam_direct_items_rec.suggested_vendor_site_id := p_old_eam_direct_items_rec.suggested_vendor_site_id;
275 END IF;
276
277 IF p_eam_direct_items_rec.suggested_vendor_contact IS NULL OR
278 p_eam_direct_items_rec.suggested_vendor_contact = FND_API.G_MISS_CHAR
279 THEN
280 x_eam_direct_items_rec.suggested_vendor_contact := p_old_eam_direct_items_rec.suggested_vendor_contact;
281 END IF;
282
283 IF p_eam_direct_items_rec.suggested_vendor_contact_id IS NULL OR
284 p_eam_direct_items_rec.suggested_vendor_contact_id = FND_API.G_MISS_NUM
285 THEN
286 x_eam_direct_items_rec.suggested_vendor_contact_id := p_old_eam_direct_items_rec.suggested_vendor_contact_id;
287 END IF;
288
289 IF p_eam_direct_items_rec.suggested_vendor_phone IS NULL OR
290 p_eam_direct_items_rec.suggested_vendor_phone = FND_API.G_MISS_CHAR
291 THEN
292 x_eam_direct_items_rec.suggested_vendor_phone := p_old_eam_direct_items_rec.suggested_vendor_phone;
293 END IF;
294
295 IF p_eam_direct_items_rec.suggested_vendor_item_num IS NULL OR
296 p_eam_direct_items_rec.suggested_vendor_item_num = FND_API.G_MISS_CHAR
297 THEN
301 IF p_eam_direct_items_rec.unit_price IS NULL OR
298 x_eam_direct_items_rec.suggested_vendor_item_num := p_old_eam_direct_items_rec.suggested_vendor_item_num;
299 END IF;
300
302 p_eam_direct_items_rec.unit_price = FND_API.G_MISS_NUM
303 THEN
304 x_eam_direct_items_rec.unit_price := p_old_eam_direct_items_rec.unit_price;
305 END IF;
306
307 IF p_eam_direct_items_rec.department_id IS NULL OR
308 p_eam_direct_items_rec.department_id = FND_API.G_MISS_NUM
309 THEN
310 x_eam_direct_items_rec.department_id := p_old_eam_direct_items_rec.department_id;
311 END IF;
312
313
314 IF p_eam_direct_items_rec.need_by_date IS NULL OR
315 p_eam_direct_items_rec.need_by_date = FND_API.G_MISS_DATE
316 THEN
317 x_eam_direct_items_rec.need_by_date := p_old_eam_direct_items_rec.need_by_date;
318 END IF;
319
320 IF p_eam_direct_items_rec.required_quantity IS NULL OR
321 p_eam_direct_items_rec.required_quantity = FND_API.G_MISS_NUM
322 THEN
323 x_eam_direct_items_rec.required_quantity := p_old_eam_direct_items_rec.required_quantity;
324 END IF;
325
326 --
327 -- Populate Null or missng flex field columns
328 --
329
330 --Changed the code for 8969942
331 IF p_eam_direct_items_rec.attribute_category IS NULL
332 THEN
333 x_eam_direct_items_rec.attribute_category := p_old_eam_direct_items_rec.attribute_category;
334 END IF;
335
336 IF p_eam_direct_items_rec.attribute1 IS NULL
337 THEN
338 x_eam_direct_items_rec.attribute1 := p_old_eam_direct_items_rec.attribute1;
339 END IF;
340
341 IF p_eam_direct_items_rec.attribute2 IS NULL
342 THEN
343 x_eam_direct_items_rec.attribute2 := p_old_eam_direct_items_rec.attribute2;
344 END IF;
345
346 IF p_eam_direct_items_rec.attribute3 IS NULL
347 THEN
348 x_eam_direct_items_rec.attribute3 := p_old_eam_direct_items_rec.attribute3;
349 END IF;
350
351 IF p_eam_direct_items_rec.attribute4 IS NULL
352 THEN
353 x_eam_direct_items_rec.attribute4 := p_old_eam_direct_items_rec.attribute4;
354 END IF;
355
356 IF p_eam_direct_items_rec.attribute5 IS NULL
357 THEN
358 x_eam_direct_items_rec.attribute5 := p_old_eam_direct_items_rec.attribute5;
359 END IF;
360
361 IF p_eam_direct_items_rec.attribute6 IS NULL
362 THEN
363 x_eam_direct_items_rec.attribute6 := p_old_eam_direct_items_rec.attribute6;
364 END IF;
365
366 IF p_eam_direct_items_rec.attribute7 IS NULL
367 THEN
368 x_eam_direct_items_rec.attribute7 := p_old_eam_direct_items_rec.attribute7;
369 END IF;
370
371 IF p_eam_direct_items_rec.attribute8 IS NULL
372 THEN
373 x_eam_direct_items_rec.attribute8 := p_old_eam_direct_items_rec.attribute8;
374 END IF;
375
376 IF p_eam_direct_items_rec.attribute9 IS NULL
377 THEN
378 x_eam_direct_items_rec.attribute9 := p_old_eam_direct_items_rec.attribute9;
379 END IF;
380
381 IF p_eam_direct_items_rec.attribute10 IS NULL
382 THEN
383 x_eam_direct_items_rec.attribute10 := p_old_eam_direct_items_rec.attribute10;
384 END IF;
385
386 IF p_eam_direct_items_rec.attribute11 IS NULL
387 THEN
388 x_eam_direct_items_rec.attribute11 := p_old_eam_direct_items_rec.attribute11;
389 END IF;
390
391 IF p_eam_direct_items_rec.attribute12 IS NULL
392 THEN
393 x_eam_direct_items_rec.attribute12 := p_old_eam_direct_items_rec.attribute12;
394 END IF;
395
396 IF p_eam_direct_items_rec.attribute13 IS NULL
397 THEN
398 x_eam_direct_items_rec.attribute13 := p_old_eam_direct_items_rec.attribute13;
399 END IF;
400
401 IF p_eam_direct_items_rec.attribute14 IS NULL
402 THEN
403 x_eam_direct_items_rec.attribute14 := p_old_eam_direct_items_rec.attribute14;
404 END IF;
405
406 IF p_eam_direct_items_rec.attribute15 IS NULL
407 THEN
408 x_eam_direct_items_rec.attribute15 := p_old_eam_direct_items_rec.attribute15;
409 END IF;
410
411 IF p_eam_direct_items_rec.auto_request_material = FND_API.G_MISS_CHAR OR
412 p_eam_direct_items_rec.auto_request_material IS NULL
413 THEN
414 x_eam_direct_items_rec.auto_request_material := p_old_eam_direct_items_rec.auto_request_material;
415 END IF;
416
417
418 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Done processing null columns prior update'); END IF;
419
420
421 END Populate_Null_Columns;
422
423
424
425 /******************************************************************
426 * Procedure : GetDI_In_Op1
427 * Parameters IN : Direct Items table
428 * Organization_ID
432 * in operation seq num 1 and append them to the table.
429 Wip_Entity_Id
430 * Parameters OUT NOCOPY: Direct Items table after populating
431 * Purpose : This procedure will find all the direct items that are
433 * This procedure is called only when there exists any DI in operation 1
434 ********************************************************************/
435
436 PROCEDURE GetDI_In_Op1
437 ( p_eam_direct_items_tbl IN EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type
438 , p_organization_id IN NUMBER
439 , p_wip_entity_id IN NUMBER
440 , x_eam_direct_items_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type
441 )
442 IS
443 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type := p_eam_direct_items_tbl;
444 k NUMBER := l_eam_direct_items_tbl.COUNT ;
445
446 CURSOR DIRECT_ITEMS_CUR IS
447 SELECT
448 WIP_ENTITY_ID WIP_ENTITY_ID
449 , ORGANIZATION_ID ORGANIZATION_ID
450 , OPERATION_SEQ_NUM OPERATION_SEQ_NUM
451 , DIRECT_ITEM_SEQUENCE_ID DIRECT_ITEM_SEQUENCE_ID
452 , 2 TRANSACTION_TYPE
453 FROM wip_eam_direct_items
454 WHERE organization_id = p_organization_id
455 and wip_entity_id = p_wip_entity_id
456 and operation_seq_num = 1;
457
458
459 BEGIN
460
461 FOR direc IN DIRECT_ITEMS_CUR LOOP
462 k := k + 1 ;
463
464 l_eam_direct_items_tbl(k).WIP_ENTITY_ID := direc.WIP_ENTITY_ID;
465 l_eam_direct_items_tbl(k).ORGANIZATION_ID := direc.ORGANIZATION_ID;
466 l_eam_direct_items_tbl(k).OPERATION_SEQ_NUM := direc.OPERATION_SEQ_NUM;
467 l_eam_direct_items_tbl(k).DIRECT_ITEM_SEQUENCE_ID := direc.DIRECT_ITEM_SEQUENCE_ID;
468 l_eam_direct_items_tbl(k).TRANSACTION_TYPE := direc.TRANSACTION_TYPE ;
469
470 END LOOP;
471 x_eam_direct_items_tbl := l_eam_direct_items_tbl ;
472
473 END GetDI_In_Op1 ;
474
475
476 /******************************************************************
477 * Procedure : Change_OpSeqNum1
478 * Parameters IN : Direct Items column record
479 * Operation Sequence Number
480 Department Id
481 * Parameters OUT NOCOPY: Direct Items column record after changing
482 * Purpose : This procedure will change the operation seq num from 1
483 * to the newly created operation ( p_operation_seq_num )
484 * and accordingly the department id
485 * This procedure is called only when there exists any direct items in operation 1
486 ********************************************************************/
487 PROCEDURE Change_OpSeqNum1
488 ( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
489 , p_operation_seq_num IN NUMBER
490 , p_department_id IN NUMBER
491 , x_eam_direct_items_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
492 )
493 IS
494 l_eam_direct_items_rec EAM_PROCESS_WO_PUB.eam_direct_items_rec_type := p_eam_direct_items_rec;
495
496 BEGIN
497
498 IF ( l_eam_direct_items_rec.operation_seq_num = 1 ) THEN
499 l_eam_direct_items_rec.operation_seq_num := p_operation_seq_num ;
500 l_eam_direct_items_rec.department_id := p_department_id ;
501 END IF;
502
503 x_eam_direct_items_rec := l_eam_direct_items_rec ;
504
505 END Change_OpSeqNum1;
506
507
508
509 END EAM_DIRECT_ITEMS_DEFAULT_PVT;
[Home] [Help]