[Home] [Help]
PACKAGE BODY: APPS.WIP_RES_UTIL
Source
1 PACKAGE BODY WIP_Res_Util AS
2 /* $Header: WIPURESB.pls 120.1.12010000.2 2010/03/10 09:49:31 hliew ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WIP_Res_Util';
7
8 -- Function Convert_Miss_To_Null
9
10 FUNCTION Convert_Miss_To_Null
11 ( p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type
12 ) RETURN WIP_Transaction_PUB.Res_Rec_Type
13 IS
14 l_Res_rec WIP_Transaction_PUB.Res_Rec_Type := p_Res_rec;
15 BEGIN
16
17 IF l_Res_rec.acct_period_id = FND_API.G_MISS_NUM THEN
18 l_Res_rec.acct_period_id := NULL;
19 END IF;
20
21 IF l_Res_rec.activity_id = FND_API.G_MISS_NUM THEN
22 l_Res_rec.activity_id := NULL;
23 END IF;
24
25 IF l_Res_rec.activity_name = FND_API.G_MISS_CHAR THEN
26 l_Res_rec.activity_name := NULL;
27 END IF;
28
29 IF l_Res_rec.actual_resource_rate = FND_API.G_MISS_NUM THEN
30 l_Res_rec.actual_resource_rate := NULL;
31 END IF;
32
33 IF l_Res_rec.attribute1 = FND_API.G_MISS_CHAR THEN
34 l_Res_rec.attribute1 := NULL;
35 END IF;
36
37 IF l_Res_rec.attribute10 = FND_API.G_MISS_CHAR THEN
38 l_Res_rec.attribute10 := NULL;
39 END IF;
40
41 IF l_Res_rec.attribute11 = FND_API.G_MISS_CHAR THEN
42 l_Res_rec.attribute11 := NULL;
43 END IF;
44
45 IF l_Res_rec.attribute12 = FND_API.G_MISS_CHAR THEN
46 l_Res_rec.attribute12 := NULL;
47 END IF;
48
49 IF l_Res_rec.attribute13 = FND_API.G_MISS_CHAR THEN
50 l_Res_rec.attribute13 := NULL;
51 END IF;
52
53 IF l_Res_rec.attribute14 = FND_API.G_MISS_CHAR THEN
54 l_Res_rec.attribute14 := NULL;
55 END IF;
56
57 IF l_Res_rec.attribute15 = FND_API.G_MISS_CHAR THEN
58 l_Res_rec.attribute15 := NULL;
59 END IF;
60
61 IF l_Res_rec.attribute2 = FND_API.G_MISS_CHAR THEN
62 l_Res_rec.attribute2 := NULL;
63 END IF;
64
65 IF l_Res_rec.attribute3 = FND_API.G_MISS_CHAR THEN
66 l_Res_rec.attribute3 := NULL;
67 END IF;
68
69 IF l_Res_rec.attribute4 = FND_API.G_MISS_CHAR THEN
70 l_Res_rec.attribute4 := NULL;
71 END IF;
72
73 IF l_Res_rec.attribute5 = FND_API.G_MISS_CHAR THEN
74 l_Res_rec.attribute5 := NULL;
75 END IF;
76
77 IF l_Res_rec.attribute6 = FND_API.G_MISS_CHAR THEN
78 l_Res_rec.attribute6 := NULL;
79 END IF;
80
81 IF l_Res_rec.attribute7 = FND_API.G_MISS_CHAR THEN
82 l_Res_rec.attribute7 := NULL;
83 END IF;
84
85 IF l_Res_rec.attribute8 = FND_API.G_MISS_CHAR THEN
86 l_Res_rec.attribute8 := NULL;
87 END IF;
88
89 IF l_Res_rec.attribute9 = FND_API.G_MISS_CHAR THEN
90 l_Res_rec.attribute9 := NULL;
91 END IF;
92
93 IF l_Res_rec.attribute_category = FND_API.G_MISS_CHAR THEN
94 l_Res_rec.attribute_category := NULL;
95 END IF;
96
97 IF l_Res_rec.autocharge_type = FND_API.G_MISS_NUM THEN
98 l_Res_rec.autocharge_type := NULL;
99 END IF;
100
101 IF l_Res_rec.basis_type = FND_API.G_MISS_NUM THEN
102 l_Res_rec.basis_type := NULL;
103 END IF;
104
105 IF l_Res_rec.completion_transaction_id = FND_API.G_MISS_NUM THEN
106 l_Res_rec.completion_transaction_id := NULL;
107 END IF;
108
109 IF l_Res_rec.created_by = FND_API.G_MISS_NUM THEN
110 l_Res_rec.created_by := NULL;
111 END IF;
112
113 IF l_Res_rec.created_by_name = FND_API.G_MISS_CHAR THEN
114 l_Res_rec.created_by_name := NULL;
115 END IF;
116
117 IF l_Res_rec.creation_date = FND_API.G_MISS_DATE THEN
118 l_Res_rec.creation_date := NULL;
119 END IF;
120
121 IF l_Res_rec.currency_actual_rsc_rate = FND_API.G_MISS_NUM THEN
122 l_Res_rec.currency_actual_rsc_rate := NULL;
123 END IF;
124
125 IF l_Res_rec.currency_code = FND_API.G_MISS_CHAR THEN
126 l_Res_rec.currency_code := NULL;
127 END IF;
128
129 IF l_Res_rec.currency_conversion_date = FND_API.G_MISS_DATE THEN
130 l_Res_rec.currency_conversion_date := NULL;
131 END IF;
132
133 IF l_Res_rec.currency_conversion_rate = FND_API.G_MISS_NUM THEN
134 l_Res_rec.currency_conversion_rate := NULL;
135 END IF;
136
137 IF l_Res_rec.currency_conversion_type = FND_API.G_MISS_CHAR THEN
138 l_Res_rec.currency_conversion_type := NULL;
139 END IF;
140
141 IF l_Res_rec.department_code = FND_API.G_MISS_CHAR THEN
142 l_Res_rec.department_code := NULL;
143 END IF;
144
145 IF l_Res_rec.department_id = FND_API.G_MISS_NUM THEN
146 l_Res_rec.department_id := NULL;
147 END IF;
148
149 IF l_Res_rec.employee_id = FND_API.G_MISS_NUM THEN
150 l_Res_rec.employee_id := NULL;
151 END IF;
152
153 IF l_Res_rec.employee_num = FND_API.G_MISS_CHAR THEN
154 l_Res_rec.employee_num := NULL;
155 END IF;
156
157 IF l_Res_rec.entity_type = FND_API.G_MISS_NUM THEN
158 l_Res_rec.entity_type := NULL;
159 END IF;
160
161 IF l_Res_rec.group_id = FND_API.G_MISS_NUM THEN
162 l_Res_rec.group_id := NULL;
163 END IF;
164
165 IF l_Res_rec.last_updated_by = FND_API.G_MISS_NUM THEN
166 l_Res_rec.last_updated_by := NULL;
167 END IF;
168
169 IF l_Res_rec.last_updated_by_name = FND_API.G_MISS_CHAR THEN
170 l_Res_rec.last_updated_by_name := NULL;
171 END IF;
172
173 IF l_Res_rec.last_update_date = FND_API.G_MISS_DATE THEN
174 l_Res_rec.last_update_date := NULL;
175 END IF;
176
177 IF l_Res_rec.last_update_login = FND_API.G_MISS_NUM THEN
178 l_Res_rec.last_update_login := NULL;
179 END IF;
180
181 IF l_Res_rec.line_code = FND_API.G_MISS_CHAR THEN
182 l_Res_rec.line_code := NULL;
183 END IF;
184
185 IF l_Res_rec.line_id = FND_API.G_MISS_NUM THEN
186 l_Res_rec.line_id := NULL;
187 END IF;
188
189 IF l_Res_rec.move_transaction_id = FND_API.G_MISS_NUM THEN
190 l_Res_rec.move_transaction_id := NULL;
191 END IF;
192
193 IF l_Res_rec.operation_seq_num = FND_API.G_MISS_NUM THEN
194 l_Res_rec.operation_seq_num := NULL;
195 END IF;
196
197 IF l_Res_rec.organization_code = FND_API.G_MISS_CHAR THEN
198 l_Res_rec.organization_code := NULL;
199 END IF;
200
201 IF l_Res_rec.organization_id = FND_API.G_MISS_NUM THEN
202 l_Res_rec.organization_id := NULL;
203 END IF;
204
205 IF l_Res_rec.po_header_id = FND_API.G_MISS_NUM THEN
206 l_Res_rec.po_header_id := NULL;
207 END IF;
208
209 IF l_Res_rec.po_line_id = FND_API.G_MISS_NUM THEN
210 l_Res_rec.po_line_id := NULL;
211 END IF;
212
213 IF l_Res_rec.primary_item_id = FND_API.G_MISS_NUM THEN
214 l_Res_rec.primary_item_id := NULL;
215 END IF;
216
217 IF l_Res_rec.primary_quantity = FND_API.G_MISS_NUM THEN
218 l_Res_rec.primary_quantity := NULL;
219 END IF;
220
221 IF l_Res_rec.primary_uom = FND_API.G_MISS_CHAR THEN
222 l_Res_rec.primary_uom := NULL;
223 END IF;
224
225 IF l_Res_rec.primary_uom_class = FND_API.G_MISS_CHAR THEN
226 l_Res_rec.primary_uom_class := NULL;
227 END IF;
228
229 IF l_Res_rec.process_phase = FND_API.G_MISS_NUM THEN
230 l_Res_rec.process_phase := NULL;
231 END IF;
232
233 IF l_Res_rec.process_status = FND_API.G_MISS_NUM THEN
234 l_Res_rec.process_status := NULL;
235 END IF;
236
237 IF l_Res_rec.program_application_id = FND_API.G_MISS_NUM THEN
238 l_Res_rec.program_application_id := NULL;
239 END IF;
240
241 IF l_Res_rec.program_id = FND_API.G_MISS_NUM THEN
242 l_Res_rec.program_id := NULL;
243 END IF;
244
245 IF l_Res_rec.program_update_date = FND_API.G_MISS_DATE THEN
246 l_Res_rec.program_update_date := NULL;
247 END IF;
248
249 IF l_Res_rec.project_id = FND_API.G_MISS_NUM THEN
250 l_Res_rec.project_id := NULL;
251 END IF;
252
253 IF l_Res_rec.rcv_transaction_id = FND_API.G_MISS_NUM THEN
254 l_Res_rec.rcv_transaction_id := NULL;
255 END IF;
256
257 IF l_Res_rec.reason_id = FND_API.G_MISS_NUM THEN
258 l_Res_rec.reason_id := NULL;
259 END IF;
260
261 IF l_Res_rec.reason_name = FND_API.G_MISS_CHAR THEN
262 l_Res_rec.reason_name := NULL;
263 END IF;
264
265 IF l_Res_rec.receiving_account_id = FND_API.G_MISS_NUM THEN
266 l_Res_rec.receiving_account_id := NULL;
267 END IF;
268
269 IF l_Res_rec.reference = FND_API.G_MISS_CHAR THEN
270 l_Res_rec.reference := NULL;
271 END IF;
272
273 IF l_Res_rec.repetitive_schedule_id = FND_API.G_MISS_NUM THEN
274 l_Res_rec.repetitive_schedule_id := NULL;
275 END IF;
276
277 IF l_Res_rec.request_id = FND_API.G_MISS_NUM THEN
278 l_Res_rec.request_id := NULL;
279 END IF;
280
281 IF l_Res_rec.resource_code = FND_API.G_MISS_CHAR THEN
282 l_Res_rec.resource_code := NULL;
283 END IF;
284
285 IF l_Res_rec.resource_id = FND_API.G_MISS_NUM THEN
286 l_Res_rec.resource_id := NULL;
287 END IF;
288
289 IF l_Res_rec.resource_seq_num = FND_API.G_MISS_NUM THEN
290 l_Res_rec.resource_seq_num := NULL;
291 END IF;
292
293 IF l_Res_rec.resource_type = FND_API.G_MISS_NUM THEN
294 l_Res_rec.resource_type := NULL;
295 END IF;
296
297 IF l_Res_rec.source_code = FND_API.G_MISS_CHAR THEN
298 l_Res_rec.source_code := NULL;
299 END IF;
300
301 IF l_Res_rec.source_line_id = FND_API.G_MISS_NUM THEN
302 l_Res_rec.source_line_id := NULL;
303 END IF;
304
305 IF l_Res_rec.standard_rate_flag = FND_API.G_MISS_NUM THEN
306 l_Res_rec.standard_rate_flag := NULL;
307 END IF;
308
309 IF l_Res_rec.task_id = FND_API.G_MISS_NUM THEN
310 l_Res_rec.task_id := NULL;
311 END IF;
312
313 IF l_Res_rec.transaction_date = FND_API.G_MISS_DATE THEN
314 l_Res_rec.transaction_date := NULL;
315 END IF;
316
317 IF l_Res_rec.transaction_id = FND_API.G_MISS_NUM THEN
318 l_Res_rec.transaction_id := NULL;
319 END IF;
320
321 IF l_Res_rec.transaction_quantity = FND_API.G_MISS_NUM THEN
322 l_Res_rec.transaction_quantity := NULL;
323 END IF;
324
325 IF l_Res_rec.transaction_type = FND_API.G_MISS_NUM THEN
326 l_Res_rec.transaction_type := NULL;
327 END IF;
328
329 IF l_Res_rec.transaction_uom = FND_API.G_MISS_CHAR THEN
330 l_Res_rec.transaction_uom := NULL;
331 END IF;
332
333 IF l_Res_rec.usage_rate_or_amount = FND_API.G_MISS_NUM THEN
334 l_Res_rec.usage_rate_or_amount := NULL;
335 END IF;
336
337 IF l_Res_rec.wip_entity_id = FND_API.G_MISS_NUM THEN
338 l_Res_rec.wip_entity_id := NULL;
339 END IF;
340
341 IF l_Res_rec.wip_entity_name = FND_API.G_MISS_CHAR THEN
342 l_Res_rec.wip_entity_name := NULL;
343 END IF;
344
345 /*Fix bug 9356683*/
346 IF l_Res_rec.encumbrance_type_id = FND_API.G_MISS_NUM THEN
347 l_Res_rec.encumbrance_type_id := NULL;
348 END IF;
349
350 IF l_Res_rec.encumbrance_amount = FND_API.G_MISS_NUM THEN
351 l_Res_rec.encumbrance_amount := NULL;
352 END IF;
353
354 IF l_Res_rec.encumbrance_quantity = FND_API.G_MISS_NUM THEN
355 l_Res_rec.encumbrance_quantity := NULL;
356 END IF;
357
358 IF l_Res_rec.encumbrance_ccid = FND_API.G_MISS_NUM THEN
359 l_Res_rec.encumbrance_ccid := NULL;
360 END IF;
361 /*End of Fix Bug 9356683*/
362
363 RETURN l_Res_rec;
364
365 END Convert_Miss_To_Null;
366
367 -- Procedure Update_Row
368
369 PROCEDURE Update_Row
370 ( p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type
371 )
372 IS
373 BEGIN
374
375 UPDATE WIP_COST_TXN_INTERFACE
376 SET ACCT_PERIOD_ID = p_Res_rec.acct_period_id
377 , ACTIVITY_ID = p_Res_rec.activity_id
378 , ACTIVITY_NAME = p_Res_rec.activity_name
379 , ACTUAL_RESOURCE_RATE = p_Res_rec.actual_resource_rate
380 , ATTRIBUTE1 = p_Res_rec.attribute1
381 , ATTRIBUTE10 = p_Res_rec.attribute10
382 , ATTRIBUTE11 = p_Res_rec.attribute11
383 , ATTRIBUTE12 = p_Res_rec.attribute12
384 , ATTRIBUTE13 = p_Res_rec.attribute13
385 , ATTRIBUTE14 = p_Res_rec.attribute14
386 , ATTRIBUTE15 = p_Res_rec.attribute15
387 , ATTRIBUTE2 = p_Res_rec.attribute2
388 , ATTRIBUTE3 = p_Res_rec.attribute3
389 , ATTRIBUTE4 = p_Res_rec.attribute4
390 , ATTRIBUTE5 = p_Res_rec.attribute5
391 , ATTRIBUTE6 = p_Res_rec.attribute6
392 , ATTRIBUTE7 = p_Res_rec.attribute7
393 , ATTRIBUTE8 = p_Res_rec.attribute8
394 , ATTRIBUTE9 = p_Res_rec.attribute9
395 , ATTRIBUTE_CATEGORY = p_Res_rec.attribute_category
396 , AUTOCHARGE_TYPE = p_Res_rec.autocharge_type
397 , BASIS_TYPE = p_Res_rec.basis_type
398 , COMPLETION_TRANSACTION_ID = p_Res_rec.completion_transaction_id
399 , CREATED_BY = p_Res_rec.created_by
400 , CREATED_BY_NAME = p_Res_rec.created_by_name
401 , CREATION_DATE = p_Res_rec.creation_date
402 , CURRENCY_ACTUAL_RESOURCE_RATE = p_Res_rec.currency_actual_rsc_rate
403 , CURRENCY_CODE = p_Res_rec.currency_code
404 , CURRENCY_CONVERSION_DATE = p_Res_rec.currency_conversion_date
405 , CURRENCY_CONVERSION_RATE = p_Res_rec.currency_conversion_rate
406 , CURRENCY_CONVERSION_TYPE = p_Res_rec.currency_conversion_type
407 , DEPARTMENT_CODE = p_Res_rec.department_code
408 , DEPARTMENT_ID = p_Res_rec.department_id
409 , EMPLOYEE_ID = p_Res_rec.employee_id
410 , EMPLOYEE_NUM = p_Res_rec.employee_num
411 , ENTITY_TYPE = p_Res_rec.entity_type
412 , GROUP_ID = p_Res_rec.group_id
413 , LAST_UPDATED_BY = p_Res_rec.last_updated_by
414 , LAST_UPDATED_BY_NAME = p_Res_rec.last_updated_by_name
415 , LAST_UPDATE_DATE = p_Res_rec.last_update_date
416 , LAST_UPDATE_LOGIN = p_Res_rec.last_update_login
417 , LINE_CODE = p_Res_rec.line_code
418 , LINE_ID = p_Res_rec.line_id
419 , MOVE_TRANSACTION_ID = p_Res_rec.move_transaction_id
420 , OPERATION_SEQ_NUM = p_Res_rec.operation_seq_num
421 , ORGANIZATION_CODE = p_Res_rec.organization_code
422 , ORGANIZATION_ID = p_Res_rec.organization_id
423 , PO_HEADER_ID = p_Res_rec.po_header_id
424 , PO_LINE_ID = p_Res_rec.po_line_id
425 , PRIMARY_ITEM_ID = p_Res_rec.primary_item_id
426 , PRIMARY_QUANTITY = p_Res_rec.primary_quantity
427 , PRIMARY_UOM = p_Res_rec.primary_uom
428 , PRIMARY_UOM_CLASS = p_Res_rec.primary_uom_class
429 , PROCESS_PHASE = p_Res_rec.process_phase
430 , PROCESS_STATUS = p_Res_rec.process_status
431 , PROGRAM_APPLICATION_ID = p_Res_rec.program_application_id
432 , PROGRAM_ID = p_Res_rec.program_id
433 , PROGRAM_UPDATE_DATE = p_Res_rec.program_update_date
434 , PROJECT_ID = p_Res_rec.project_id
435 , RCV_TRANSACTION_ID = p_Res_rec.rcv_transaction_id
436 , REASON_ID = p_Res_rec.reason_id
437 , REASON_NAME = p_Res_rec.reason_name
438 , RECEIVING_ACCOUNT_ID = p_Res_rec.receiving_account_id
439 , REFERENCE = p_Res_rec.reference
440 , REPETITIVE_SCHEDULE_ID = p_Res_rec.repetitive_schedule_id
441 , REQUEST_ID = p_Res_rec.request_id
442 , RESOURCE_CODE = p_Res_rec.resource_code
443 , RESOURCE_ID = p_Res_rec.resource_id
444 , RESOURCE_SEQ_NUM = p_Res_rec.resource_seq_num
445 , RESOURCE_TYPE = p_Res_rec.resource_type
446 , SOURCE_CODE = p_Res_rec.source_code
447 , SOURCE_LINE_ID = p_Res_rec.source_line_id
448 , STANDARD_RATE_FLAG = p_Res_rec.standard_rate_flag
449 , TASK_ID = p_Res_rec.task_id
450 , TRANSACTION_DATE = p_Res_rec.transaction_date
451 , TRANSACTION_ID = p_Res_rec.transaction_id
452 , TRANSACTION_QUANTITY = p_Res_rec.transaction_quantity
453 , TRANSACTION_TYPE = p_Res_rec.transaction_type
454 , TRANSACTION_UOM = p_Res_rec.transaction_uom
455 , USAGE_RATE_OR_AMOUNT = p_Res_rec.usage_rate_or_amount
456 , WIP_ENTITY_ID = p_Res_rec.wip_entity_id
457 /* Fix for bug 3427769. Removed WIP_ENTITY_NAME from update statement.
458 , WIP_ENTITY_NAME = p_Res_rec.wip_entity_name
459 */
460 WHERE PO_HEADER_ID = p_Res_rec.po_header_id
461 ;
462
463 EXCEPTION
464
465 WHEN OTHERS THEN
466
467 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
468 THEN
469 FND_MSG_PUB.Add_Exc_Msg
470 ( G_PKG_NAME
471 , 'Update_Row'
472 );
473 END IF;
474
475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
476
477 END Update_Row;
478
479 -- Procedure Insert_Row
480
481 PROCEDURE Insert_Row
482 ( p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type
483 )
484 IS
485 BEGIN
486
487 INSERT INTO WIP_COST_TXN_INTERFACE
488 ( ACCT_PERIOD_ID
489 , ACTIVITY_ID
490 , ACTIVITY_NAME
491 , ACTUAL_RESOURCE_RATE
492 , ATTRIBUTE1
493 , ATTRIBUTE10
494 , ATTRIBUTE11
495 , ATTRIBUTE12
496 , ATTRIBUTE13
497 , ATTRIBUTE14
498 , ATTRIBUTE15
499 , ATTRIBUTE2
500 , ATTRIBUTE3
501 , ATTRIBUTE4
502 , ATTRIBUTE5
503 , ATTRIBUTE6
504 , ATTRIBUTE7
505 , ATTRIBUTE8
506 , ATTRIBUTE9
507 , ATTRIBUTE_CATEGORY
508 , AUTOCHARGE_TYPE
509 , BASIS_TYPE
510 , COMPLETION_TRANSACTION_ID
511 , CREATED_BY
512 , CREATED_BY_NAME
513 , CREATION_DATE
514 , CURRENCY_ACTUAL_RESOURCE_RATE
515 , CURRENCY_CODE
516 , CURRENCY_CONVERSION_DATE
517 , CURRENCY_CONVERSION_RATE
518 , CURRENCY_CONVERSION_TYPE
519 , DEPARTMENT_CODE
520 , DEPARTMENT_ID
521 , EMPLOYEE_ID
522 , EMPLOYEE_NUM
523 , ENTITY_TYPE
524 , GROUP_ID
525 , LAST_UPDATED_BY
526 , LAST_UPDATED_BY_NAME
527 , LAST_UPDATE_DATE
528 , LAST_UPDATE_LOGIN
529 , LINE_CODE
530 , LINE_ID
531 , MOVE_TRANSACTION_ID
532 , OPERATION_SEQ_NUM
533 , ORGANIZATION_CODE
534 , ORGANIZATION_ID
535 , PO_HEADER_ID
536 , PO_LINE_ID
537 , PRIMARY_ITEM_ID
538 , PRIMARY_QUANTITY
539 , PRIMARY_UOM
540 , PRIMARY_UOM_CLASS
541 , PROCESS_PHASE
542 , PROCESS_STATUS
543 , PROGRAM_APPLICATION_ID
544 , PROGRAM_ID
545 , PROGRAM_UPDATE_DATE
546 , PROJECT_ID
547 , RCV_TRANSACTION_ID
548 , REASON_ID
549 , REASON_NAME
550 , RECEIVING_ACCOUNT_ID
551 , REFERENCE
552 , REPETITIVE_SCHEDULE_ID
553 , REQUEST_ID
554 , RESOURCE_CODE
555 , RESOURCE_ID
556 , RESOURCE_SEQ_NUM
557 , RESOURCE_TYPE
558 , SOURCE_CODE
559 , SOURCE_LINE_ID
560 , STANDARD_RATE_FLAG
561 , TASK_ID
562 , TRANSACTION_DATE
563 , TRANSACTION_ID
564 , TRANSACTION_QUANTITY
565 , TRANSACTION_TYPE
566 , TRANSACTION_UOM
567 , USAGE_RATE_OR_AMOUNT
568 , WIP_ENTITY_ID
569 /* Fix for bug 3427769. Removed WIP_ENTITY_NAME from insert statement.
570 , WIP_ENTITY_NAME
571 */
572 /* Fix bug 9356683, for costing encumbrance project*/
573 , ENCUMBRANCE_TYPE_ID
574 , ENCUMBRANCE_AMOUNT
575 , ENCUMBRANCE_QUANTITY
576 , ENCUMBRANCE_CCID
577 )
578 VALUES
579 ( p_Res_rec.acct_period_id
580 , p_Res_rec.activity_id
581 , p_Res_rec.activity_name
582 , p_Res_rec.actual_resource_rate
583 , p_Res_rec.attribute1
584 , p_Res_rec.attribute10
585 , p_Res_rec.attribute11
586 , p_Res_rec.attribute12
587 , p_Res_rec.attribute13
588 , p_Res_rec.attribute14
589 , p_Res_rec.attribute15
590 , p_Res_rec.attribute2
591 , p_Res_rec.attribute3
592 , p_Res_rec.attribute4
593 , p_Res_rec.attribute5
594 , p_Res_rec.attribute6
595 , p_Res_rec.attribute7
596 , p_Res_rec.attribute8
597 , p_Res_rec.attribute9
598 , p_Res_rec.attribute_category
599 , p_Res_rec.autocharge_type
600 , p_Res_rec.basis_type
601 , p_Res_rec.completion_transaction_id
602 , p_Res_rec.created_by
603 , p_Res_rec.created_by_name
604 , p_Res_rec.creation_date
605 , p_Res_rec.currency_actual_rsc_rate
606 , p_Res_rec.currency_code
607 , p_Res_rec.currency_conversion_date
608 , p_Res_rec.currency_conversion_rate
609 , p_Res_rec.currency_conversion_type
610 , p_Res_rec.department_code
611 , p_Res_rec.department_id
612 , p_Res_rec.employee_id
613 , p_Res_rec.employee_num
614 , p_Res_rec.entity_type
615 , p_Res_rec.group_id
616 , p_Res_rec.last_updated_by
617 , p_Res_rec.last_updated_by_name
618 , p_Res_rec.last_update_date
619 , p_Res_rec.last_update_login
620 , p_Res_rec.line_code
621 , p_Res_rec.line_id
622 , p_Res_rec.move_transaction_id
623 , p_Res_rec.operation_seq_num
624 , p_Res_rec.organization_code
625 , p_Res_rec.organization_id
626 , p_Res_rec.po_header_id
627 , p_Res_rec.po_line_id
628 , p_Res_rec.primary_item_id
629 , p_Res_rec.primary_quantity
630 , p_Res_rec.primary_uom
631 , p_Res_rec.primary_uom_class
632 , p_Res_rec.process_phase
633 , p_Res_rec.process_status
634 , p_Res_rec.program_application_id
635 , p_Res_rec.program_id
636 , p_Res_rec.program_update_date
637 , p_Res_rec.project_id
638 , p_Res_rec.rcv_transaction_id
639 , p_Res_rec.reason_id
640 , p_Res_rec.reason_name
641 , p_Res_rec.receiving_account_id
642 , p_Res_rec.reference
643 , p_Res_rec.repetitive_schedule_id
644 , p_Res_rec.request_id
645 , p_Res_rec.resource_code
646 , p_Res_rec.resource_id
647 , p_Res_rec.resource_seq_num
648 , p_Res_rec.resource_type
649 , p_Res_rec.source_code
650 , p_Res_rec.source_line_id
651 , p_Res_rec.standard_rate_flag
652 , p_Res_rec.task_id
653 , p_Res_rec.transaction_date
654 , p_Res_rec.transaction_id
655 , p_Res_rec.transaction_quantity
656 , p_Res_rec.transaction_type
657 , p_Res_rec.transaction_uom
658 , p_Res_rec.usage_rate_or_amount
659 , p_Res_rec.wip_entity_id
660 /* Fix for bug 3427769. Removed WIP_ENTITY_NAME from insert statement.
661 , p_Res_rec.wip_entity_name
662 */
663 /* Fix bug 9356683, for costing encumbrance project*/
664 , p_Res_rec.encumbrance_type_id
665 , p_Res_rec.encumbrance_amount
666 , p_Res_rec.encumbrance_quantity
667 , p_Res_rec.encumbrance_ccid
668 );
669
670 EXCEPTION
671
672 WHEN OTHERS THEN
673
674 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
675 THEN
676 FND_MSG_PUB.Add_Exc_Msg
677 ( G_PKG_NAME
678 , 'Insert_Row'
679 );
680 END IF;
681
682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
683
684 END Insert_Row;
685
686 -- Procedure Delete_Row
687
688 PROCEDURE Delete_Row
689 ( p_po_header_id IN NUMBER
690 )
691 IS
692 BEGIN
693
694 DELETE FROM WIP_COST_TXN_INTERFACE
695 WHERE PO_HEADER_ID = p_po_header_id
696 ;
697
698 EXCEPTION
699
700 WHEN OTHERS THEN
701
702 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
703 THEN
704 FND_MSG_PUB.Add_Exc_Msg
705 ( G_PKG_NAME
706 , 'Delete_Row'
707 );
708 END IF;
709
710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
711
712 END Delete_Row;
713
714 -- Function Query_Row
715
716 FUNCTION Query_Row
717 ( p_po_header_id IN NUMBER
718 ) RETURN WIP_Transaction_PUB.Res_Rec_Type
719 IS
720 BEGIN
721
722 RETURN Query_Rows
723 ( p_po_header_id => p_po_header_id
724 )(1);
725
726 END Query_Row;
727
728 -- Function Query_Rows
729
730 --
731
732 FUNCTION Query_Rows
733 ( p_po_header_id IN NUMBER :=
734 NULL
735 , p_dummy IN VARCHAR2 :=
736 NULL
737 ) RETURN WIP_Transaction_PUB.Res_Tbl_Type
738 IS
739 l_Res_rec WIP_Transaction_PUB.Res_Rec_Type;
740 l_Res_tbl WIP_Transaction_PUB.Res_Tbl_Type;
741
742 CURSOR l_Res_csr IS
743 SELECT ACCT_PERIOD_ID
744 , ACTIVITY_ID
745 , ACTIVITY_NAME
746 , ACTUAL_RESOURCE_RATE
747 , ATTRIBUTE1
748 , ATTRIBUTE10
749 , ATTRIBUTE11
750 , ATTRIBUTE12
751 , ATTRIBUTE13
752 , ATTRIBUTE14
753 , ATTRIBUTE15
754 , ATTRIBUTE2
755 , ATTRIBUTE3
756 , ATTRIBUTE4
757 , ATTRIBUTE5
758 , ATTRIBUTE6
759 , ATTRIBUTE7
760 , ATTRIBUTE8
761 , ATTRIBUTE9
762 , ATTRIBUTE_CATEGORY
763 , AUTOCHARGE_TYPE
764 , BASIS_TYPE
765 , COMPLETION_TRANSACTION_ID
766 , CREATED_BY
767 , CREATED_BY_NAME
768 , CREATION_DATE
769 , CURRENCY_ACTUAL_RESOURCE_RATE
770 , CURRENCY_CODE
771 , CURRENCY_CONVERSION_DATE
772 , CURRENCY_CONVERSION_RATE
773 , CURRENCY_CONVERSION_TYPE
774 , DEPARTMENT_CODE
775 , DEPARTMENT_ID
776 , EMPLOYEE_ID
777 , EMPLOYEE_NUM
778 , ENTITY_TYPE
779 , GROUP_ID
780 , LAST_UPDATED_BY
781 , LAST_UPDATED_BY_NAME
782 , LAST_UPDATE_DATE
783 , LAST_UPDATE_LOGIN
784 , LINE_CODE
785 , LINE_ID
786 , MOVE_TRANSACTION_ID
787 , OPERATION_SEQ_NUM
788 , ORGANIZATION_CODE
789 , ORGANIZATION_ID
790 , PO_HEADER_ID
791 , PO_LINE_ID
792 , PRIMARY_ITEM_ID
793 , PRIMARY_QUANTITY
794 , PRIMARY_UOM
795 , PRIMARY_UOM_CLASS
796 , PROCESS_PHASE
797 , PROCESS_STATUS
798 , PROGRAM_APPLICATION_ID
799 , PROGRAM_ID
800 , PROGRAM_UPDATE_DATE
801 , PROJECT_ID
802 , RCV_TRANSACTION_ID
803 , REASON_ID
804 , REASON_NAME
805 , RECEIVING_ACCOUNT_ID
806 , REFERENCE
807 , REPETITIVE_SCHEDULE_ID
808 , REQUEST_ID
809 , RESOURCE_CODE
810 , RESOURCE_ID
811 , RESOURCE_SEQ_NUM
812 , RESOURCE_TYPE
813 , SOURCE_CODE
814 , SOURCE_LINE_ID
815 , STANDARD_RATE_FLAG
816 , TASK_ID
817 , TRANSACTION_DATE
818 , TRANSACTION_ID
819 , TRANSACTION_QUANTITY
820 , TRANSACTION_TYPE
821 , TRANSACTION_UOM
822 , USAGE_RATE_OR_AMOUNT
823 , WIP_ENTITY_ID
824 , WIP_ENTITY_NAME
825 FROM WIP_COST_TXN_INTERFACE
826 WHERE ( PO_HEADER_ID = nvl(p_po_header_id,FND_API.G_MISS_NUM)
827 );
828
829 BEGIN
830
831
832 -- Loop over fetched records
833
834 FOR l_implicit_rec IN l_Res_csr LOOP
835
836 l_Res_rec.acct_period_id := l_implicit_rec.ACCT_PERIOD_ID;
837 l_Res_rec.activity_id := l_implicit_rec.ACTIVITY_ID;
838 l_Res_rec.activity_name := l_implicit_rec.ACTIVITY_NAME;
839 l_Res_rec.actual_resource_rate := l_implicit_rec.ACTUAL_RESOURCE_RATE;
840 l_Res_rec.attribute1 := l_implicit_rec.ATTRIBUTE1;
841 l_Res_rec.attribute10 := l_implicit_rec.ATTRIBUTE10;
842 l_Res_rec.attribute11 := l_implicit_rec.ATTRIBUTE11;
843 l_Res_rec.attribute12 := l_implicit_rec.ATTRIBUTE12;
844 l_Res_rec.attribute13 := l_implicit_rec.ATTRIBUTE13;
845 l_Res_rec.attribute14 := l_implicit_rec.ATTRIBUTE14;
846 l_Res_rec.attribute15 := l_implicit_rec.ATTRIBUTE15;
847 l_Res_rec.attribute2 := l_implicit_rec.ATTRIBUTE2;
848 l_Res_rec.attribute3 := l_implicit_rec.ATTRIBUTE3;
849 l_Res_rec.attribute4 := l_implicit_rec.ATTRIBUTE4;
850 l_Res_rec.attribute5 := l_implicit_rec.ATTRIBUTE5;
851 l_Res_rec.attribute6 := l_implicit_rec.ATTRIBUTE6;
852 l_Res_rec.attribute7 := l_implicit_rec.ATTRIBUTE7;
853 l_Res_rec.attribute8 := l_implicit_rec.ATTRIBUTE8;
854 l_Res_rec.attribute9 := l_implicit_rec.ATTRIBUTE9;
855 l_Res_rec.attribute_category := l_implicit_rec.ATTRIBUTE_CATEGORY;
856 l_Res_rec.autocharge_type := l_implicit_rec.AUTOCHARGE_TYPE;
857 l_Res_rec.basis_type := l_implicit_rec.BASIS_TYPE;
858 l_Res_rec.completion_transaction_id := l_implicit_rec.COMPLETION_TRANSACTION_ID;
859 l_Res_rec.created_by := l_implicit_rec.CREATED_BY;
860 l_Res_rec.created_by_name := l_implicit_rec.CREATED_BY_NAME;
861 l_Res_rec.creation_date := l_implicit_rec.CREATION_DATE;
862 l_Res_rec.currency_actual_rsc_rate := l_implicit_rec.CURRENCY_ACTUAL_RESOURCE_RATE;
863 l_Res_rec.currency_code := l_implicit_rec.CURRENCY_CODE;
864 l_Res_rec.currency_conversion_date := l_implicit_rec.CURRENCY_CONVERSION_DATE;
865 l_Res_rec.currency_conversion_rate := l_implicit_rec.CURRENCY_CONVERSION_RATE;
866 l_Res_rec.currency_conversion_type := l_implicit_rec.CURRENCY_CONVERSION_TYPE;
867 l_Res_rec.department_code := l_implicit_rec.DEPARTMENT_CODE;
868 l_Res_rec.department_id := l_implicit_rec.DEPARTMENT_ID;
869 l_Res_rec.employee_id := l_implicit_rec.EMPLOYEE_ID;
870 l_Res_rec.employee_num := l_implicit_rec.EMPLOYEE_NUM;
871 l_Res_rec.entity_type := l_implicit_rec.ENTITY_TYPE;
872 l_Res_rec.group_id := l_implicit_rec.GROUP_ID;
873 l_Res_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
874 l_Res_rec.last_updated_by_name := l_implicit_rec.LAST_UPDATED_BY_NAME;
875 l_Res_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
876 l_Res_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
877 l_Res_rec.line_code := l_implicit_rec.LINE_CODE;
878 l_Res_rec.line_id := l_implicit_rec.LINE_ID;
879 l_Res_rec.move_transaction_id := l_implicit_rec.MOVE_TRANSACTION_ID;
880 l_Res_rec.operation_seq_num := l_implicit_rec.OPERATION_SEQ_NUM;
881 l_Res_rec.organization_code := l_implicit_rec.ORGANIZATION_CODE;
882 l_Res_rec.organization_id := l_implicit_rec.ORGANIZATION_ID;
883 l_Res_rec.po_header_id := l_implicit_rec.PO_HEADER_ID;
884 l_Res_rec.po_line_id := l_implicit_rec.PO_LINE_ID;
885 l_Res_rec.primary_item_id := l_implicit_rec.PRIMARY_ITEM_ID;
886 l_Res_rec.primary_quantity := l_implicit_rec.PRIMARY_QUANTITY;
887 l_Res_rec.primary_uom := l_implicit_rec.PRIMARY_UOM;
888 l_Res_rec.primary_uom_class := l_implicit_rec.PRIMARY_UOM_CLASS;
889 l_Res_rec.process_phase := l_implicit_rec.PROCESS_PHASE;
890 l_Res_rec.process_status := l_implicit_rec.PROCESS_STATUS;
891 l_Res_rec.program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
892 l_Res_rec.program_id := l_implicit_rec.PROGRAM_ID;
893 l_Res_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
894 l_Res_rec.project_id := l_implicit_rec.PROJECT_ID;
895 l_Res_rec.rcv_transaction_id := l_implicit_rec.RCV_TRANSACTION_ID;
896 l_Res_rec.reason_id := l_implicit_rec.REASON_ID;
897 l_Res_rec.reason_name := l_implicit_rec.REASON_NAME;
898 l_Res_rec.receiving_account_id := l_implicit_rec.RECEIVING_ACCOUNT_ID;
899 l_Res_rec.reference := l_implicit_rec.REFERENCE;
900 l_Res_rec.repetitive_schedule_id := l_implicit_rec.REPETITIVE_SCHEDULE_ID;
901 l_Res_rec.request_id := l_implicit_rec.REQUEST_ID;
902 l_Res_rec.resource_code := l_implicit_rec.RESOURCE_CODE;
903 l_Res_rec.resource_id := l_implicit_rec.RESOURCE_ID;
904 l_Res_rec.resource_seq_num := l_implicit_rec.RESOURCE_SEQ_NUM;
905 l_Res_rec.resource_type := l_implicit_rec.RESOURCE_TYPE;
906 l_Res_rec.source_code := l_implicit_rec.SOURCE_CODE;
907 l_Res_rec.source_line_id := l_implicit_rec.SOURCE_LINE_ID;
908 l_Res_rec.standard_rate_flag := l_implicit_rec.STANDARD_RATE_FLAG;
909 l_Res_rec.task_id := l_implicit_rec.TASK_ID;
910 l_Res_rec.transaction_date := l_implicit_rec.TRANSACTION_DATE;
911 l_Res_rec.transaction_id := l_implicit_rec.TRANSACTION_ID;
912 l_Res_rec.transaction_quantity := l_implicit_rec.TRANSACTION_QUANTITY;
913 l_Res_rec.transaction_type := l_implicit_rec.TRANSACTION_TYPE;
914 l_Res_rec.transaction_uom := l_implicit_rec.TRANSACTION_UOM;
915 l_Res_rec.usage_rate_or_amount := l_implicit_rec.USAGE_RATE_OR_AMOUNT;
916 l_Res_rec.wip_entity_id := l_implicit_rec.WIP_ENTITY_ID;
917 l_Res_rec.wip_entity_name := l_implicit_rec.WIP_ENTITY_NAME;
918
919 l_Res_tbl(l_Res_tbl.COUNT + 1) := l_Res_rec;
920
921 END LOOP;
922
923
924 -- PK sent and no rows found
925
926 IF
927 (p_po_header_id IS NOT NULL
928 AND
929 p_po_header_id <> FND_API.G_MISS_NUM)
930 AND
931 (l_Res_tbl.COUNT = 0)
932 THEN
933 RAISE NO_DATA_FOUND;
934 END IF;
935
936
937 -- Return fetched table
938 RETURN l_Res_tbl;
939
940 EXCEPTION
941
942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
943
944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
945
946 WHEN OTHERS THEN
947
948 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
949 THEN
950 FND_MSG_PUB.Add_Exc_Msg
951 ( G_PKG_NAME
952 , 'Query_Rows'
953 );
954 END IF;
955
956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
957
958 END Query_Rows;
959
960 -- Procedure lock_Row
961 --
962
963 PROCEDURE Lock_Row
964 ( x_return_status OUT NOCOPY VARCHAR2
965 , p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type
966 , x_Res_rec OUT NOCOPY WIP_Transaction_PUB.Res_Rec_Type
967 )
968 IS
969 l_Res_rec WIP_Transaction_PUB.Res_Rec_Type;
970 BEGIN
971
972 SELECT ACCT_PERIOD_ID
973 , ACTIVITY_ID
974 , ACTIVITY_NAME
975 , ACTUAL_RESOURCE_RATE
976 , ATTRIBUTE1
977 , ATTRIBUTE10
978 , ATTRIBUTE11
979 , ATTRIBUTE12
980 , ATTRIBUTE13
981 , ATTRIBUTE14
982 , ATTRIBUTE15
983 , ATTRIBUTE2
984 , ATTRIBUTE3
985 , ATTRIBUTE4
986 , ATTRIBUTE5
987 , ATTRIBUTE6
988 , ATTRIBUTE7
989 , ATTRIBUTE8
990 , ATTRIBUTE9
991 , ATTRIBUTE_CATEGORY
992 , AUTOCHARGE_TYPE
993 , BASIS_TYPE
994 , COMPLETION_TRANSACTION_ID
995 , CREATED_BY
996 , CREATED_BY_NAME
997 , CREATION_DATE
998 , CURRENCY_ACTUAL_RESOURCE_RATE
999 , CURRENCY_CODE
1000 , CURRENCY_CONVERSION_DATE
1001 , CURRENCY_CONVERSION_RATE
1002 , CURRENCY_CONVERSION_TYPE
1003 , DEPARTMENT_CODE
1004 , DEPARTMENT_ID
1005 , EMPLOYEE_ID
1006 , EMPLOYEE_NUM
1007 , ENTITY_TYPE
1008 , GROUP_ID
1009 , LAST_UPDATED_BY
1010 , LAST_UPDATED_BY_NAME
1011 , LAST_UPDATE_DATE
1012 , LAST_UPDATE_LOGIN
1013 , LINE_CODE
1014 , LINE_ID
1015 , MOVE_TRANSACTION_ID
1016 , OPERATION_SEQ_NUM
1017 , ORGANIZATION_CODE
1018 , ORGANIZATION_ID
1019 , PO_HEADER_ID
1020 , PO_LINE_ID
1021 , PRIMARY_ITEM_ID
1022 , PRIMARY_QUANTITY
1023 , PRIMARY_UOM
1024 , PRIMARY_UOM_CLASS
1025 , PROCESS_PHASE
1026 , PROCESS_STATUS
1027 , PROGRAM_APPLICATION_ID
1028 , PROGRAM_ID
1029 , PROGRAM_UPDATE_DATE
1030 , PROJECT_ID
1031 , RCV_TRANSACTION_ID
1032 , REASON_ID
1033 , REASON_NAME
1034 , RECEIVING_ACCOUNT_ID
1035 , REFERENCE
1036 , REPETITIVE_SCHEDULE_ID
1037 , REQUEST_ID
1038 , RESOURCE_CODE
1039 , RESOURCE_ID
1040 , RESOURCE_SEQ_NUM
1041 , RESOURCE_TYPE
1042 , SOURCE_CODE
1043 , SOURCE_LINE_ID
1044 , STANDARD_RATE_FLAG
1045 , TASK_ID
1046 , TRANSACTION_DATE
1047 , TRANSACTION_ID
1048 , TRANSACTION_QUANTITY
1049 , TRANSACTION_TYPE
1050 , TRANSACTION_UOM
1051 , USAGE_RATE_OR_AMOUNT
1052 , WIP_ENTITY_ID
1053 , WIP_ENTITY_NAME
1054 INTO l_Res_rec.acct_period_id
1055 , l_Res_rec.activity_id
1056 , l_Res_rec.activity_name
1057 , l_Res_rec.actual_resource_rate
1058 , l_Res_rec.attribute1
1059 , l_Res_rec.attribute10
1060 , l_Res_rec.attribute11
1061 , l_Res_rec.attribute12
1062 , l_Res_rec.attribute13
1063 , l_Res_rec.attribute14
1064 , l_Res_rec.attribute15
1065 , l_Res_rec.attribute2
1066 , l_Res_rec.attribute3
1067 , l_Res_rec.attribute4
1068 , l_Res_rec.attribute5
1069 , l_Res_rec.attribute6
1070 , l_Res_rec.attribute7
1071 , l_Res_rec.attribute8
1072 , l_Res_rec.attribute9
1073 , l_Res_rec.attribute_category
1074 , l_Res_rec.autocharge_type
1075 , l_Res_rec.basis_type
1076 , l_Res_rec.completion_transaction_id
1077 , l_Res_rec.created_by
1078 , l_Res_rec.created_by_name
1079 , l_Res_rec.creation_date
1080 , l_Res_rec.currency_actual_rsc_rate
1081 , l_Res_rec.currency_code
1082 , l_Res_rec.currency_conversion_date
1083 , l_Res_rec.currency_conversion_rate
1084 , l_Res_rec.currency_conversion_type
1085 , l_Res_rec.department_code
1086 , l_Res_rec.department_id
1087 , l_Res_rec.employee_id
1088 , l_Res_rec.employee_num
1089 , l_Res_rec.entity_type
1090 , l_Res_rec.group_id
1091 , l_Res_rec.last_updated_by
1092 , l_Res_rec.last_updated_by_name
1093 , l_Res_rec.last_update_date
1094 , l_Res_rec.last_update_login
1095 , l_Res_rec.line_code
1096 , l_Res_rec.line_id
1097 , l_Res_rec.move_transaction_id
1098 , l_Res_rec.operation_seq_num
1099 , l_Res_rec.organization_code
1100 , l_Res_rec.organization_id
1101 , l_Res_rec.po_header_id
1102 , l_Res_rec.po_line_id
1103 , l_Res_rec.primary_item_id
1104 , l_Res_rec.primary_quantity
1105 , l_Res_rec.primary_uom
1106 , l_Res_rec.primary_uom_class
1107 , l_Res_rec.process_phase
1108 , l_Res_rec.process_status
1109 , l_Res_rec.program_application_id
1110 , l_Res_rec.program_id
1111 , l_Res_rec.program_update_date
1112 , l_Res_rec.project_id
1113 , l_Res_rec.rcv_transaction_id
1114 , l_Res_rec.reason_id
1115 , l_Res_rec.reason_name
1116 , l_Res_rec.receiving_account_id
1117 , l_Res_rec.reference
1118 , l_Res_rec.repetitive_schedule_id
1119 , l_Res_rec.request_id
1120 , l_Res_rec.resource_code
1121 , l_Res_rec.resource_id
1122 , l_Res_rec.resource_seq_num
1123 , l_Res_rec.resource_type
1124 , l_Res_rec.source_code
1125 , l_Res_rec.source_line_id
1126 , l_Res_rec.standard_rate_flag
1127 , l_Res_rec.task_id
1128 , l_Res_rec.transaction_date
1129 , l_Res_rec.transaction_id
1130 , l_Res_rec.transaction_quantity
1131 , l_Res_rec.transaction_type
1132 , l_Res_rec.transaction_uom
1133 , l_Res_rec.usage_rate_or_amount
1134 , l_Res_rec.wip_entity_id
1135 , l_Res_rec.wip_entity_name
1136 FROM WIP_COST_TXN_INTERFACE
1137 WHERE PO_HEADER_ID = p_Res_rec.po_header_id
1138 FOR UPDATE NOWAIT;
1139
1140 -- Row locked. Compare IN attributes to DB attributes.
1141
1142 IF WIP_GLOBALS.Equal(p_Res_rec.acct_period_id,
1143 l_Res_rec.acct_period_id)
1144 AND WIP_GLOBALS.Equal(p_Res_rec.activity_id,
1145 l_Res_rec.activity_id)
1146 AND WIP_GLOBALS.Equal(p_Res_rec.activity_name,
1147 l_Res_rec.activity_name)
1148 AND WIP_GLOBALS.Equal(p_Res_rec.actual_resource_rate,
1149 l_Res_rec.actual_resource_rate)
1150 AND WIP_GLOBALS.Equal(p_Res_rec.attribute1,
1151 l_Res_rec.attribute1)
1152 AND WIP_GLOBALS.Equal(p_Res_rec.attribute10,
1153 l_Res_rec.attribute10)
1154 AND WIP_GLOBALS.Equal(p_Res_rec.attribute11,
1155 l_Res_rec.attribute11)
1156 AND WIP_GLOBALS.Equal(p_Res_rec.attribute12,
1157 l_Res_rec.attribute12)
1158 AND WIP_GLOBALS.Equal(p_Res_rec.attribute13,
1159 l_Res_rec.attribute13)
1160 AND WIP_GLOBALS.Equal(p_Res_rec.attribute14,
1161 l_Res_rec.attribute14)
1162 AND WIP_GLOBALS.Equal(p_Res_rec.attribute15,
1163 l_Res_rec.attribute15)
1164 AND WIP_GLOBALS.Equal(p_Res_rec.attribute2,
1165 l_Res_rec.attribute2)
1166 AND WIP_GLOBALS.Equal(p_Res_rec.attribute3,
1167 l_Res_rec.attribute3)
1168 AND WIP_GLOBALS.Equal(p_Res_rec.attribute4,
1169 l_Res_rec.attribute4)
1170 AND WIP_GLOBALS.Equal(p_Res_rec.attribute5,
1171 l_Res_rec.attribute5)
1172 AND WIP_GLOBALS.Equal(p_Res_rec.attribute6,
1173 l_Res_rec.attribute6)
1174 AND WIP_GLOBALS.Equal(p_Res_rec.attribute7,
1175 l_Res_rec.attribute7)
1176 AND WIP_GLOBALS.Equal(p_Res_rec.attribute8,
1177 l_Res_rec.attribute8)
1178 AND WIP_GLOBALS.Equal(p_Res_rec.attribute9,
1179 l_Res_rec.attribute9)
1180 AND WIP_GLOBALS.Equal(p_Res_rec.attribute_category,
1181 l_Res_rec.attribute_category)
1182 AND WIP_GLOBALS.Equal(p_Res_rec.autocharge_type,
1183 l_Res_rec.autocharge_type)
1184 AND WIP_GLOBALS.Equal(p_Res_rec.basis_type,
1185 l_Res_rec.basis_type)
1186 AND WIP_GLOBALS.Equal(p_Res_rec.completion_transaction_id,
1187 l_Res_rec.completion_transaction_id)
1188 AND WIP_GLOBALS.Equal(p_Res_rec.created_by,
1189 l_Res_rec.created_by)
1190 AND WIP_GLOBALS.Equal(p_Res_rec.created_by_name,
1191 l_Res_rec.created_by_name)
1192 AND WIP_GLOBALS.Equal(p_Res_rec.creation_date,
1193 l_Res_rec.creation_date)
1194 AND WIP_GLOBALS.Equal(p_Res_rec.currency_actual_rsc_rate,
1195 l_Res_rec.currency_actual_rsc_rate)
1196 AND WIP_GLOBALS.Equal(p_Res_rec.currency_code,
1197 l_Res_rec.currency_code)
1198 AND WIP_GLOBALS.Equal(p_Res_rec.currency_conversion_date,
1199 l_Res_rec.currency_conversion_date)
1200 AND WIP_GLOBALS.Equal(p_Res_rec.currency_conversion_rate,
1201 l_Res_rec.currency_conversion_rate)
1202 AND WIP_GLOBALS.Equal(p_Res_rec.currency_conversion_type,
1203 l_Res_rec.currency_conversion_type)
1204 AND WIP_GLOBALS.Equal(p_Res_rec.department_code,
1205 l_Res_rec.department_code)
1206 AND WIP_GLOBALS.Equal(p_Res_rec.department_id,
1207 l_Res_rec.department_id)
1208 AND WIP_GLOBALS.Equal(p_Res_rec.employee_id,
1209 l_Res_rec.employee_id)
1210 AND WIP_GLOBALS.Equal(p_Res_rec.employee_num,
1211 l_Res_rec.employee_num)
1212 AND WIP_GLOBALS.Equal(p_Res_rec.entity_type,
1213 l_Res_rec.entity_type)
1214 AND WIP_GLOBALS.Equal(p_Res_rec.group_id,
1215 l_Res_rec.group_id)
1216 AND WIP_GLOBALS.Equal(p_Res_rec.last_updated_by,
1217 l_Res_rec.last_updated_by)
1218 AND WIP_GLOBALS.Equal(p_Res_rec.last_updated_by_name,
1219 l_Res_rec.last_updated_by_name)
1220 AND WIP_GLOBALS.Equal(p_Res_rec.last_update_date,
1221 l_Res_rec.last_update_date)
1222 AND WIP_GLOBALS.Equal(p_Res_rec.last_update_login,
1223 l_Res_rec.last_update_login)
1224 AND WIP_GLOBALS.Equal(p_Res_rec.line_code,
1225 l_Res_rec.line_code)
1226 AND WIP_GLOBALS.Equal(p_Res_rec.line_id,
1227 l_Res_rec.line_id)
1228 AND WIP_GLOBALS.Equal(p_Res_rec.move_transaction_id,
1229 l_Res_rec.move_transaction_id)
1230 AND WIP_GLOBALS.Equal(p_Res_rec.operation_seq_num,
1231 l_Res_rec.operation_seq_num)
1232 AND WIP_GLOBALS.Equal(p_Res_rec.organization_code,
1233 l_Res_rec.organization_code)
1234 AND WIP_GLOBALS.Equal(p_Res_rec.organization_id,
1235 l_Res_rec.organization_id)
1236 AND WIP_GLOBALS.Equal(p_Res_rec.po_header_id,
1237 l_Res_rec.po_header_id)
1238 AND WIP_GLOBALS.Equal(p_Res_rec.po_line_id,
1239 l_Res_rec.po_line_id)
1240 AND WIP_GLOBALS.Equal(p_Res_rec.primary_item_id,
1241 l_Res_rec.primary_item_id)
1242 AND WIP_GLOBALS.Equal(p_Res_rec.primary_quantity,
1243 l_Res_rec.primary_quantity)
1244 AND WIP_GLOBALS.Equal(p_Res_rec.primary_uom,
1245 l_Res_rec.primary_uom)
1246 AND WIP_GLOBALS.Equal(p_Res_rec.primary_uom_class,
1247 l_Res_rec.primary_uom_class)
1248 AND WIP_GLOBALS.Equal(p_Res_rec.process_phase,
1249 l_Res_rec.process_phase)
1250 AND WIP_GLOBALS.Equal(p_Res_rec.process_status,
1251 l_Res_rec.process_status)
1252 AND WIP_GLOBALS.Equal(p_Res_rec.program_application_id,
1253 l_Res_rec.program_application_id)
1254 AND WIP_GLOBALS.Equal(p_Res_rec.program_id,
1255 l_Res_rec.program_id)
1256 AND WIP_GLOBALS.Equal(p_Res_rec.program_update_date,
1257 l_Res_rec.program_update_date)
1258 AND WIP_GLOBALS.Equal(p_Res_rec.project_id,
1259 l_Res_rec.project_id)
1260 AND WIP_GLOBALS.Equal(p_Res_rec.rcv_transaction_id,
1261 l_Res_rec.rcv_transaction_id)
1262 AND WIP_GLOBALS.Equal(p_Res_rec.reason_id,
1263 l_Res_rec.reason_id)
1264 AND WIP_GLOBALS.Equal(p_Res_rec.reason_name,
1265 l_Res_rec.reason_name)
1266 AND WIP_GLOBALS.Equal(p_Res_rec.receiving_account_id,
1267 l_Res_rec.receiving_account_id)
1268 AND WIP_GLOBALS.Equal(p_Res_rec.reference,
1269 l_Res_rec.reference)
1270 AND WIP_GLOBALS.Equal(p_Res_rec.repetitive_schedule_id,
1271 l_Res_rec.repetitive_schedule_id)
1272 AND WIP_GLOBALS.Equal(p_Res_rec.request_id,
1273 l_Res_rec.request_id)
1274 AND WIP_GLOBALS.Equal(p_Res_rec.resource_code,
1275 l_Res_rec.resource_code)
1276 AND WIP_GLOBALS.Equal(p_Res_rec.resource_id,
1277 l_Res_rec.resource_id)
1278 AND WIP_GLOBALS.Equal(p_Res_rec.resource_seq_num,
1279 l_Res_rec.resource_seq_num)
1280 AND WIP_GLOBALS.Equal(p_Res_rec.resource_type,
1281 l_Res_rec.resource_type)
1282 AND WIP_GLOBALS.Equal(p_Res_rec.source_code,
1283 l_Res_rec.source_code)
1284 AND WIP_GLOBALS.Equal(p_Res_rec.source_line_id,
1285 l_Res_rec.source_line_id)
1286 AND WIP_GLOBALS.Equal(p_Res_rec.standard_rate_flag,
1287 l_Res_rec.standard_rate_flag)
1288 AND WIP_GLOBALS.Equal(p_Res_rec.task_id,
1289 l_Res_rec.task_id)
1290 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_date,
1291 l_Res_rec.transaction_date)
1292 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_id,
1293 l_Res_rec.transaction_id)
1294 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_quantity,
1295 l_Res_rec.transaction_quantity)
1296 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_type,
1297 l_Res_rec.transaction_type)
1298 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_uom,
1299 l_Res_rec.transaction_uom)
1300 AND WIP_GLOBALS.Equal(p_Res_rec.usage_rate_or_amount,
1301 l_Res_rec.usage_rate_or_amount)
1302 AND WIP_GLOBALS.Equal(p_Res_rec.wip_entity_id,
1303 l_Res_rec.wip_entity_id)
1304 AND WIP_GLOBALS.Equal(p_Res_rec.wip_entity_name,
1305 l_Res_rec.wip_entity_name)
1306 THEN
1307
1308 -- Row has not changed. Set out parameter.
1309
1310 x_Res_rec := l_Res_rec;
1311
1312 -- Set return status
1313
1314 x_return_status := FND_API.G_RET_STS_SUCCESS;
1315 x_Res_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1316
1317 ELSE
1318
1319 -- Row has changed by another user.
1320
1321 x_return_status := FND_API.G_RET_STS_ERROR;
1322 x_Res_rec.return_status := FND_API.G_RET_STS_ERROR;
1323
1324 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1325 THEN
1326
1327 FND_MESSAGE.SET_NAME('WIP','WIP_LOCK_ROW_CHANGED');
1328 FND_MSG_PUB.Add;
1329
1330 END IF;
1331
1332 END IF;
1333 EXCEPTION
1334
1335 WHEN NO_DATA_FOUND THEN
1336
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 x_Res_rec.return_status := FND_API.G_RET_STS_ERROR;
1339
1340 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1341 THEN
1342
1343 FND_MESSAGE.SET_NAME('WIP','WIP_LOCK_ROW_DELETED');
1344 FND_MSG_PUB.Add;
1345
1346 END IF;
1347 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1348
1349 x_return_status := FND_API.G_RET_STS_ERROR;
1350 x_Res_rec.return_status := FND_API.G_RET_STS_ERROR;
1351
1352 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1353 THEN
1354
1355 FND_MESSAGE.SET_NAME('WIP','WIP_LOCK_ROW_ALREADY_LOCKED');
1356 FND_MSG_PUB.Add;
1357
1358 END IF;
1359 WHEN OTHERS THEN
1360
1361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1362 x_Res_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1363
1364 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1365 THEN
1366 FND_MSG_PUB.Add_Exc_Msg
1367 ( G_PKG_NAME
1368 , 'Lock_Row'
1369 );
1370 END IF;
1371
1372 END Lock_Row;
1373
1374
1375 procedure print_record(p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type)
1376 is
1377 begin
1378
1379 null;
1380 -- dbms_output.put_line(' ');
1381 -- dbms_output.put_line('Resource Record*******************************');
1382 -- dbms_output.put_line('acct_period_id '||to_char(p_Res_rec.acct_period_id));
1383 -- dbms_output.put_line('activity_id '||to_char(p_Res_rec.activity_id));
1384 -- dbms_output.put_line('activity_name '||p_Res_rec.activity_name );
1385 -- dbms_output.put_line('actual_resource_rate '||to_char(p_Res_rec.actual_resource_rate ));
1386 -- dbms_output.put_line('autocharge_type '||to_char(p_Res_rec.autocharge_type ));
1387 -- dbms_output.put_line('basis_type '||to_char(p_Res_rec.basis_type ));
1388 -- dbms_output.put_line('completion_transaction_id '||to_char(p_Res_rec.completion_transaction_id ));
1389 -- dbms_output.put_line('created_by '||to_char(p_Res_rec.created_by ));
1390 -- dbms_output.put_line('created_by_name '||p_Res_rec.created_by_name );
1391 -- dbms_output.put_line('creation_date '||p_Res_rec.creation_date );
1392 -- dbms_output.put_line('currency_actual_rsc_rate '||to_char(p_Res_rec.currency_actual_rsc_rate ));
1393 -- dbms_output.put_line('currency_code '||p_Res_rec.currency_code );
1394 -- dbms_output.put_line('currency_conversion_date '||to_char(p_Res_rec.currency_conversion_date,'DD-MON-YY'));
1395 -- dbms_output.put_line('currency_conversion_rate '||to_char(p_Res_rec.currency_conversion_rate ));
1396 -- dbms_output.put_line('currency_conversion_type '||p_Res_rec.currency_conversion_type );
1397 -- dbms_output.put_line('department_code '||p_Res_rec.department_code );
1398 -- dbms_output.put_line('department_id '||to_char(p_Res_rec.department_id));
1399 -- dbms_output.put_line('employee_id '||to_char(p_Res_rec.employee_id ));
1400 -- dbms_output.put_line('employee_num '||p_Res_rec.employee_num );
1401 -- dbms_output.put_line('entity_type '||to_char(p_Res_rec.entity_type));
1402 -- dbms_output.put_line('group_id '||to_char(p_Res_rec.group_id ));
1403 -- dbms_output.put_line('last_updated_by '||to_char(p_Res_rec.last_updated_by));
1404 -- dbms_output.put_line('last_updated_by_name '||p_Res_rec.last_updated_by_name );
1405 -- dbms_output.put_line('last_update_date '||to_char(p_Res_rec.last_update_date ,'DD-MON-YY'));
1406 -- dbms_output.put_line('last_update_login '||to_char(p_Res_rec.last_update_login ));
1407 -- dbms_output.put_line('line_code '||p_Res_rec.line_code );
1408 -- dbms_output.put_line('line_id '||to_char(nvl(p_Res_rec.line_id,-1)));
1409 -- dbms_output.put_line('move_transaction_id '||to_char(p_Res_rec.move_transaction_id ));
1410 -- dbms_output.put_line('operation_seq_num '||to_char(p_Res_rec.operation_seq_num ));
1411 -- dbms_output.put_line('organization_code '||p_Res_rec.organization_code);
1412 -- dbms_output.put_line('organization_id '||to_char(p_Res_rec.organization_id));
1413 -- dbms_output.put_line('po_header_id '||to_char(p_Res_rec.po_header_id));
1414 -- dbms_output.put_line('po_line_id '||to_char(p_Res_rec.po_line_id));
1415 -- dbms_output.put_line('primary_item_id '||to_char(p_Res_rec.primary_item_id));
1416 -- dbms_output.put_line('primary_quantity '||to_char(p_Res_rec.primary_quantity ));
1417 -- dbms_output.put_line('primary_uom' ||p_Res_rec.primary_uom);
1418 -- dbms_output.put_line('primary_uom_class' || p_Res_rec.primary_uom_class);
1419 -- dbms_output.put_line('process_phase '||to_char(p_Res_rec.process_phase));
1420 -- dbms_output.put_line('process_status '||to_char(p_Res_rec.process_status));
1421 -- dbms_output.put_line('program_application_id '||to_char(p_Res_rec.program_application_id));
1422 -- dbms_output.put_line('program_id '||to_char(p_Res_rec.program_id));
1423 -- dbms_output.put_line('program_update_date '||to_char(p_Res_rec.program_update_date,'DD-MON-YY'));
1424 -- dbms_output.put_line('project_id '||to_char(p_Res_rec.project_id ));
1425 -- dbms_output.put_line('rcv_transaction_id '||to_char(nvl(p_Res_rec.rcv_transaction_id,-1)));
1426 -- dbms_output.put_line('reason_id '||to_char(p_Res_rec.reason_id ));
1427 -- dbms_output.put_line('reason_name '|| p_Res_rec.reason_name );
1428 -- dbms_output.put_line('receiving_account_id '||to_char(p_Res_rec.receiving_account_id));
1429 -- dbms_output.put_line('reference '|| p_Res_rec.reference );
1430 -- dbms_output.put_line('repetitive_schedule_id '||to_char(p_Res_rec.repetitive_schedule_id));
1431 -- dbms_output.put_line('request_id '||to_char(p_Res_rec.request_id ));
1432 -- dbms_output.put_line('resource_code'|| p_Res_rec.resource_code );
1433 -- dbms_output.put_line('resource_id '||to_char(p_Res_rec.resource_id));
1434 -- dbms_output.put_line('resource_seq_num '||to_char(p_Res_rec.resource_seq_num));
1435 -- dbms_output.put_line('resource_type '||to_char(p_Res_rec.resource_type));
1436 -- dbms_output.put_line('source_code '||p_Res_rec.source_code );
1437 -- dbms_output.put_line('source_line_id '||to_char(p_Res_rec.source_line_id));
1438 -- dbms_output.put_line('standard_rate_flag '||to_char(p_Res_rec.standard_rate_flag));
1439 -- dbms_output.put_line('task_id '||to_char(p_Res_rec.task_id ));
1440 -- dbms_output.put_line('transaction_date '||to_char(p_Res_rec.transaction_date ,'DD-MON-YY'));
1441 -- dbms_output.put_line('transaction_id '||to_char(p_Res_rec.transaction_id ));
1442 -- dbms_output.put_line('transaction_quantity '||to_char(p_Res_rec.transaction_quantity ));
1443 -- dbms_output.put_line('transaction_type '||to_char(p_Res_rec.transaction_type ));
1444 -- dbms_output.put_line('transaction_uom '||p_Res_rec.transaction_uom );
1445 -- dbms_output.put_line('usage_rate_or_amount '||to_char(p_Res_rec.usage_rate_or_amount));
1446 -- dbms_output.put_line('wip_entity_id '||to_char(p_Res_rec.wip_entity_id));
1447 -- dbms_output.put_line('wip_entity_name '||p_Res_rec.wip_entity_name );
1448
1449 exception
1450 when others then
1451 null;
1452 end print_record;
1453
1454
1455 END WIP_Res_Util;