[Home] [Help]
PACKAGE BODY: APPS.WIP_RES_UTIL
Source
1 PACKAGE BODY WIP_Res_Util AS
2 /* $Header: WIPURESB.pls 120.1 2005/05/25 16:08:38 appldev $ */
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 RETURN l_Res_rec;
346
347 END Convert_Miss_To_Null;
348
349 -- Procedure Update_Row
350
351 PROCEDURE Update_Row
352 ( p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type
353 )
354 IS
355 BEGIN
356
357 UPDATE WIP_COST_TXN_INTERFACE
358 SET ACCT_PERIOD_ID = p_Res_rec.acct_period_id
359 , ACTIVITY_ID = p_Res_rec.activity_id
360 , ACTIVITY_NAME = p_Res_rec.activity_name
361 , ACTUAL_RESOURCE_RATE = p_Res_rec.actual_resource_rate
362 , ATTRIBUTE1 = p_Res_rec.attribute1
363 , ATTRIBUTE10 = p_Res_rec.attribute10
364 , ATTRIBUTE11 = p_Res_rec.attribute11
365 , ATTRIBUTE12 = p_Res_rec.attribute12
366 , ATTRIBUTE13 = p_Res_rec.attribute13
367 , ATTRIBUTE14 = p_Res_rec.attribute14
368 , ATTRIBUTE15 = p_Res_rec.attribute15
369 , ATTRIBUTE2 = p_Res_rec.attribute2
370 , ATTRIBUTE3 = p_Res_rec.attribute3
371 , ATTRIBUTE4 = p_Res_rec.attribute4
372 , ATTRIBUTE5 = p_Res_rec.attribute5
373 , ATTRIBUTE6 = p_Res_rec.attribute6
374 , ATTRIBUTE7 = p_Res_rec.attribute7
375 , ATTRIBUTE8 = p_Res_rec.attribute8
376 , ATTRIBUTE9 = p_Res_rec.attribute9
377 , ATTRIBUTE_CATEGORY = p_Res_rec.attribute_category
378 , AUTOCHARGE_TYPE = p_Res_rec.autocharge_type
379 , BASIS_TYPE = p_Res_rec.basis_type
380 , COMPLETION_TRANSACTION_ID = p_Res_rec.completion_transaction_id
381 , CREATED_BY = p_Res_rec.created_by
382 , CREATED_BY_NAME = p_Res_rec.created_by_name
383 , CREATION_DATE = p_Res_rec.creation_date
384 , CURRENCY_ACTUAL_RESOURCE_RATE = p_Res_rec.currency_actual_rsc_rate
385 , CURRENCY_CODE = p_Res_rec.currency_code
386 , CURRENCY_CONVERSION_DATE = p_Res_rec.currency_conversion_date
387 , CURRENCY_CONVERSION_RATE = p_Res_rec.currency_conversion_rate
388 , CURRENCY_CONVERSION_TYPE = p_Res_rec.currency_conversion_type
389 , DEPARTMENT_CODE = p_Res_rec.department_code
390 , DEPARTMENT_ID = p_Res_rec.department_id
391 , EMPLOYEE_ID = p_Res_rec.employee_id
392 , EMPLOYEE_NUM = p_Res_rec.employee_num
393 , ENTITY_TYPE = p_Res_rec.entity_type
394 , GROUP_ID = p_Res_rec.group_id
395 , LAST_UPDATED_BY = p_Res_rec.last_updated_by
396 , LAST_UPDATED_BY_NAME = p_Res_rec.last_updated_by_name
397 , LAST_UPDATE_DATE = p_Res_rec.last_update_date
398 , LAST_UPDATE_LOGIN = p_Res_rec.last_update_login
399 , LINE_CODE = p_Res_rec.line_code
400 , LINE_ID = p_Res_rec.line_id
401 , MOVE_TRANSACTION_ID = p_Res_rec.move_transaction_id
402 , OPERATION_SEQ_NUM = p_Res_rec.operation_seq_num
403 , ORGANIZATION_CODE = p_Res_rec.organization_code
404 , ORGANIZATION_ID = p_Res_rec.organization_id
405 , PO_HEADER_ID = p_Res_rec.po_header_id
406 , PO_LINE_ID = p_Res_rec.po_line_id
407 , PRIMARY_ITEM_ID = p_Res_rec.primary_item_id
408 , PRIMARY_QUANTITY = p_Res_rec.primary_quantity
409 , PRIMARY_UOM = p_Res_rec.primary_uom
410 , PRIMARY_UOM_CLASS = p_Res_rec.primary_uom_class
411 , PROCESS_PHASE = p_Res_rec.process_phase
412 , PROCESS_STATUS = p_Res_rec.process_status
413 , PROGRAM_APPLICATION_ID = p_Res_rec.program_application_id
414 , PROGRAM_ID = p_Res_rec.program_id
415 , PROGRAM_UPDATE_DATE = p_Res_rec.program_update_date
416 , PROJECT_ID = p_Res_rec.project_id
417 , RCV_TRANSACTION_ID = p_Res_rec.rcv_transaction_id
418 , REASON_ID = p_Res_rec.reason_id
422 , REPETITIVE_SCHEDULE_ID = p_Res_rec.repetitive_schedule_id
419 , REASON_NAME = p_Res_rec.reason_name
420 , RECEIVING_ACCOUNT_ID = p_Res_rec.receiving_account_id
421 , REFERENCE = p_Res_rec.reference
423 , REQUEST_ID = p_Res_rec.request_id
424 , RESOURCE_CODE = p_Res_rec.resource_code
425 , RESOURCE_ID = p_Res_rec.resource_id
426 , RESOURCE_SEQ_NUM = p_Res_rec.resource_seq_num
427 , RESOURCE_TYPE = p_Res_rec.resource_type
428 , SOURCE_CODE = p_Res_rec.source_code
429 , SOURCE_LINE_ID = p_Res_rec.source_line_id
430 , STANDARD_RATE_FLAG = p_Res_rec.standard_rate_flag
431 , TASK_ID = p_Res_rec.task_id
432 , TRANSACTION_DATE = p_Res_rec.transaction_date
433 , TRANSACTION_ID = p_Res_rec.transaction_id
434 , TRANSACTION_QUANTITY = p_Res_rec.transaction_quantity
435 , TRANSACTION_TYPE = p_Res_rec.transaction_type
436 , TRANSACTION_UOM = p_Res_rec.transaction_uom
437 , USAGE_RATE_OR_AMOUNT = p_Res_rec.usage_rate_or_amount
438 , WIP_ENTITY_ID = p_Res_rec.wip_entity_id
439 /* Fix for bug 3427769. Removed WIP_ENTITY_NAME from update statement.
440 , WIP_ENTITY_NAME = p_Res_rec.wip_entity_name
441 */
442 WHERE PO_HEADER_ID = p_Res_rec.po_header_id
443 ;
444
445 EXCEPTION
446
447 WHEN OTHERS THEN
448
449 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
450 THEN
451 FND_MSG_PUB.Add_Exc_Msg
452 ( G_PKG_NAME
453 , 'Update_Row'
454 );
455 END IF;
456
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458
459 END Update_Row;
460
461 -- Procedure Insert_Row
462
463 PROCEDURE Insert_Row
464 ( p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type
465 )
466 IS
467 BEGIN
468
469 INSERT INTO WIP_COST_TXN_INTERFACE
470 ( ACCT_PERIOD_ID
471 , ACTIVITY_ID
472 , ACTIVITY_NAME
473 , ACTUAL_RESOURCE_RATE
474 , ATTRIBUTE1
475 , ATTRIBUTE10
476 , ATTRIBUTE11
477 , ATTRIBUTE12
478 , ATTRIBUTE13
479 , ATTRIBUTE14
480 , ATTRIBUTE15
481 , ATTRIBUTE2
482 , ATTRIBUTE3
483 , ATTRIBUTE4
484 , ATTRIBUTE5
485 , ATTRIBUTE6
486 , ATTRIBUTE7
487 , ATTRIBUTE8
488 , ATTRIBUTE9
489 , ATTRIBUTE_CATEGORY
490 , AUTOCHARGE_TYPE
491 , BASIS_TYPE
492 , COMPLETION_TRANSACTION_ID
493 , CREATED_BY
494 , CREATED_BY_NAME
495 , CREATION_DATE
496 , CURRENCY_ACTUAL_RESOURCE_RATE
497 , CURRENCY_CODE
498 , CURRENCY_CONVERSION_DATE
499 , CURRENCY_CONVERSION_RATE
500 , CURRENCY_CONVERSION_TYPE
501 , DEPARTMENT_CODE
502 , DEPARTMENT_ID
503 , EMPLOYEE_ID
504 , EMPLOYEE_NUM
505 , ENTITY_TYPE
506 , GROUP_ID
507 , LAST_UPDATED_BY
508 , LAST_UPDATED_BY_NAME
509 , LAST_UPDATE_DATE
510 , LAST_UPDATE_LOGIN
511 , LINE_CODE
512 , LINE_ID
513 , MOVE_TRANSACTION_ID
514 , OPERATION_SEQ_NUM
515 , ORGANIZATION_CODE
516 , ORGANIZATION_ID
517 , PO_HEADER_ID
518 , PO_LINE_ID
519 , PRIMARY_ITEM_ID
520 , PRIMARY_QUANTITY
521 , PRIMARY_UOM
522 , PRIMARY_UOM_CLASS
523 , PROCESS_PHASE
524 , PROCESS_STATUS
525 , PROGRAM_APPLICATION_ID
526 , PROGRAM_ID
527 , PROGRAM_UPDATE_DATE
528 , PROJECT_ID
529 , RCV_TRANSACTION_ID
530 , REASON_ID
531 , REASON_NAME
532 , RECEIVING_ACCOUNT_ID
533 , REFERENCE
534 , REPETITIVE_SCHEDULE_ID
535 , REQUEST_ID
536 , RESOURCE_CODE
537 , RESOURCE_ID
538 , RESOURCE_SEQ_NUM
539 , RESOURCE_TYPE
540 , SOURCE_CODE
541 , SOURCE_LINE_ID
542 , STANDARD_RATE_FLAG
543 , TASK_ID
544 , TRANSACTION_DATE
545 , TRANSACTION_ID
546 , TRANSACTION_QUANTITY
547 , TRANSACTION_TYPE
548 , TRANSACTION_UOM
549 , USAGE_RATE_OR_AMOUNT
550 , WIP_ENTITY_ID
551 /* Fix for bug 3427769. Removed WIP_ENTITY_NAME from insert statement.
552 , WIP_ENTITY_NAME
553 */
554 )
555 VALUES
556 ( p_Res_rec.acct_period_id
557 , p_Res_rec.activity_id
558 , p_Res_rec.activity_name
559 , p_Res_rec.actual_resource_rate
563 , p_Res_rec.attribute12
560 , p_Res_rec.attribute1
561 , p_Res_rec.attribute10
562 , p_Res_rec.attribute11
564 , p_Res_rec.attribute13
565 , p_Res_rec.attribute14
566 , p_Res_rec.attribute15
567 , p_Res_rec.attribute2
568 , p_Res_rec.attribute3
569 , p_Res_rec.attribute4
570 , p_Res_rec.attribute5
571 , p_Res_rec.attribute6
572 , p_Res_rec.attribute7
573 , p_Res_rec.attribute8
574 , p_Res_rec.attribute9
575 , p_Res_rec.attribute_category
576 , p_Res_rec.autocharge_type
577 , p_Res_rec.basis_type
578 , p_Res_rec.completion_transaction_id
579 , p_Res_rec.created_by
580 , p_Res_rec.created_by_name
581 , p_Res_rec.creation_date
582 , p_Res_rec.currency_actual_rsc_rate
583 , p_Res_rec.currency_code
584 , p_Res_rec.currency_conversion_date
585 , p_Res_rec.currency_conversion_rate
586 , p_Res_rec.currency_conversion_type
587 , p_Res_rec.department_code
588 , p_Res_rec.department_id
589 , p_Res_rec.employee_id
590 , p_Res_rec.employee_num
591 , p_Res_rec.entity_type
592 , p_Res_rec.group_id
593 , p_Res_rec.last_updated_by
594 , p_Res_rec.last_updated_by_name
595 , p_Res_rec.last_update_date
596 , p_Res_rec.last_update_login
597 , p_Res_rec.line_code
598 , p_Res_rec.line_id
599 , p_Res_rec.move_transaction_id
600 , p_Res_rec.operation_seq_num
601 , p_Res_rec.organization_code
602 , p_Res_rec.organization_id
603 , p_Res_rec.po_header_id
604 , p_Res_rec.po_line_id
605 , p_Res_rec.primary_item_id
606 , p_Res_rec.primary_quantity
607 , p_Res_rec.primary_uom
608 , p_Res_rec.primary_uom_class
609 , p_Res_rec.process_phase
610 , p_Res_rec.process_status
611 , p_Res_rec.program_application_id
612 , p_Res_rec.program_id
613 , p_Res_rec.program_update_date
614 , p_Res_rec.project_id
615 , p_Res_rec.rcv_transaction_id
616 , p_Res_rec.reason_id
617 , p_Res_rec.reason_name
618 , p_Res_rec.receiving_account_id
619 , p_Res_rec.reference
620 , p_Res_rec.repetitive_schedule_id
621 , p_Res_rec.request_id
622 , p_Res_rec.resource_code
623 , p_Res_rec.resource_id
624 , p_Res_rec.resource_seq_num
625 , p_Res_rec.resource_type
626 , p_Res_rec.source_code
627 , p_Res_rec.source_line_id
628 , p_Res_rec.standard_rate_flag
629 , p_Res_rec.task_id
630 , p_Res_rec.transaction_date
631 , p_Res_rec.transaction_id
632 , p_Res_rec.transaction_quantity
633 , p_Res_rec.transaction_type
634 , p_Res_rec.transaction_uom
635 , p_Res_rec.usage_rate_or_amount
636 , p_Res_rec.wip_entity_id
637 /* Fix for bug 3427769. Removed WIP_ENTITY_NAME from insert statement.
638 , p_Res_rec.wip_entity_name
639 */
640 );
641
642 EXCEPTION
643
644 WHEN OTHERS THEN
645
646 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
647 THEN
648 FND_MSG_PUB.Add_Exc_Msg
649 ( G_PKG_NAME
650 , 'Insert_Row'
651 );
652 END IF;
653
654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
655
656 END Insert_Row;
657
658 -- Procedure Delete_Row
659
660 PROCEDURE Delete_Row
661 ( p_po_header_id IN NUMBER
662 )
663 IS
664 BEGIN
665
666 DELETE FROM WIP_COST_TXN_INTERFACE
667 WHERE PO_HEADER_ID = p_po_header_id
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 , 'Delete_Row'
679 );
680 END IF;
681
682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
683
684 END Delete_Row;
685
686 -- Function Query_Row
687
688 FUNCTION Query_Row
689 ( p_po_header_id IN NUMBER
690 ) RETURN WIP_Transaction_PUB.Res_Rec_Type
691 IS
692 BEGIN
693
694 RETURN Query_Rows
695 ( p_po_header_id => p_po_header_id
696 )(1);
697
698 END Query_Row;
699
700 -- Function Query_Rows
701
702 --
703
704 FUNCTION Query_Rows
705 ( p_po_header_id IN NUMBER :=
706 NULL
707 , p_dummy IN VARCHAR2 :=
708 NULL
709 ) RETURN WIP_Transaction_PUB.Res_Tbl_Type
710 IS
711 l_Res_rec WIP_Transaction_PUB.Res_Rec_Type;
712 l_Res_tbl WIP_Transaction_PUB.Res_Tbl_Type;
713
714 CURSOR l_Res_csr IS
718 , ACTUAL_RESOURCE_RATE
715 SELECT ACCT_PERIOD_ID
716 , ACTIVITY_ID
717 , ACTIVITY_NAME
719 , ATTRIBUTE1
720 , ATTRIBUTE10
721 , ATTRIBUTE11
722 , ATTRIBUTE12
723 , ATTRIBUTE13
724 , ATTRIBUTE14
725 , ATTRIBUTE15
726 , ATTRIBUTE2
727 , ATTRIBUTE3
728 , ATTRIBUTE4
729 , ATTRIBUTE5
730 , ATTRIBUTE6
731 , ATTRIBUTE7
732 , ATTRIBUTE8
733 , ATTRIBUTE9
734 , ATTRIBUTE_CATEGORY
735 , AUTOCHARGE_TYPE
736 , BASIS_TYPE
737 , COMPLETION_TRANSACTION_ID
738 , CREATED_BY
739 , CREATED_BY_NAME
740 , CREATION_DATE
741 , CURRENCY_ACTUAL_RESOURCE_RATE
742 , CURRENCY_CODE
743 , CURRENCY_CONVERSION_DATE
744 , CURRENCY_CONVERSION_RATE
745 , CURRENCY_CONVERSION_TYPE
746 , DEPARTMENT_CODE
747 , DEPARTMENT_ID
748 , EMPLOYEE_ID
749 , EMPLOYEE_NUM
750 , ENTITY_TYPE
751 , GROUP_ID
752 , LAST_UPDATED_BY
753 , LAST_UPDATED_BY_NAME
754 , LAST_UPDATE_DATE
755 , LAST_UPDATE_LOGIN
756 , LINE_CODE
757 , LINE_ID
758 , MOVE_TRANSACTION_ID
759 , OPERATION_SEQ_NUM
760 , ORGANIZATION_CODE
761 , ORGANIZATION_ID
762 , PO_HEADER_ID
763 , PO_LINE_ID
764 , PRIMARY_ITEM_ID
765 , PRIMARY_QUANTITY
766 , PRIMARY_UOM
767 , PRIMARY_UOM_CLASS
768 , PROCESS_PHASE
769 , PROCESS_STATUS
770 , PROGRAM_APPLICATION_ID
771 , PROGRAM_ID
772 , PROGRAM_UPDATE_DATE
773 , PROJECT_ID
774 , RCV_TRANSACTION_ID
775 , REASON_ID
776 , REASON_NAME
777 , RECEIVING_ACCOUNT_ID
778 , REFERENCE
779 , REPETITIVE_SCHEDULE_ID
780 , REQUEST_ID
781 , RESOURCE_CODE
782 , RESOURCE_ID
783 , RESOURCE_SEQ_NUM
784 , RESOURCE_TYPE
785 , SOURCE_CODE
786 , SOURCE_LINE_ID
787 , STANDARD_RATE_FLAG
788 , TASK_ID
789 , TRANSACTION_DATE
790 , TRANSACTION_ID
791 , TRANSACTION_QUANTITY
792 , TRANSACTION_TYPE
793 , TRANSACTION_UOM
794 , USAGE_RATE_OR_AMOUNT
795 , WIP_ENTITY_ID
796 , WIP_ENTITY_NAME
797 FROM WIP_COST_TXN_INTERFACE
798 WHERE ( PO_HEADER_ID = nvl(p_po_header_id,FND_API.G_MISS_NUM)
799 );
800
801 BEGIN
802
803
804 -- Loop over fetched records
805
806 FOR l_implicit_rec IN l_Res_csr LOOP
807
808 l_Res_rec.acct_period_id := l_implicit_rec.ACCT_PERIOD_ID;
809 l_Res_rec.activity_id := l_implicit_rec.ACTIVITY_ID;
810 l_Res_rec.activity_name := l_implicit_rec.ACTIVITY_NAME;
811 l_Res_rec.actual_resource_rate := l_implicit_rec.ACTUAL_RESOURCE_RATE;
812 l_Res_rec.attribute1 := l_implicit_rec.ATTRIBUTE1;
813 l_Res_rec.attribute10 := l_implicit_rec.ATTRIBUTE10;
814 l_Res_rec.attribute11 := l_implicit_rec.ATTRIBUTE11;
815 l_Res_rec.attribute12 := l_implicit_rec.ATTRIBUTE12;
816 l_Res_rec.attribute13 := l_implicit_rec.ATTRIBUTE13;
817 l_Res_rec.attribute14 := l_implicit_rec.ATTRIBUTE14;
818 l_Res_rec.attribute15 := l_implicit_rec.ATTRIBUTE15;
819 l_Res_rec.attribute2 := l_implicit_rec.ATTRIBUTE2;
820 l_Res_rec.attribute3 := l_implicit_rec.ATTRIBUTE3;
821 l_Res_rec.attribute4 := l_implicit_rec.ATTRIBUTE4;
822 l_Res_rec.attribute5 := l_implicit_rec.ATTRIBUTE5;
823 l_Res_rec.attribute6 := l_implicit_rec.ATTRIBUTE6;
824 l_Res_rec.attribute7 := l_implicit_rec.ATTRIBUTE7;
825 l_Res_rec.attribute8 := l_implicit_rec.ATTRIBUTE8;
826 l_Res_rec.attribute9 := l_implicit_rec.ATTRIBUTE9;
827 l_Res_rec.attribute_category := l_implicit_rec.ATTRIBUTE_CATEGORY;
828 l_Res_rec.autocharge_type := l_implicit_rec.AUTOCHARGE_TYPE;
829 l_Res_rec.basis_type := l_implicit_rec.BASIS_TYPE;
830 l_Res_rec.completion_transaction_id := l_implicit_rec.COMPLETION_TRANSACTION_ID;
831 l_Res_rec.created_by := l_implicit_rec.CREATED_BY;
832 l_Res_rec.created_by_name := l_implicit_rec.CREATED_BY_NAME;
833 l_Res_rec.creation_date := l_implicit_rec.CREATION_DATE;
834 l_Res_rec.currency_actual_rsc_rate := l_implicit_rec.CURRENCY_ACTUAL_RESOURCE_RATE;
835 l_Res_rec.currency_code := l_implicit_rec.CURRENCY_CODE;
836 l_Res_rec.currency_conversion_date := l_implicit_rec.CURRENCY_CONVERSION_DATE;
837 l_Res_rec.currency_conversion_rate := l_implicit_rec.CURRENCY_CONVERSION_RATE;
838 l_Res_rec.currency_conversion_type := l_implicit_rec.CURRENCY_CONVERSION_TYPE;
839 l_Res_rec.department_code := l_implicit_rec.DEPARTMENT_CODE;
843 l_Res_rec.entity_type := l_implicit_rec.ENTITY_TYPE;
840 l_Res_rec.department_id := l_implicit_rec.DEPARTMENT_ID;
841 l_Res_rec.employee_id := l_implicit_rec.EMPLOYEE_ID;
842 l_Res_rec.employee_num := l_implicit_rec.EMPLOYEE_NUM;
844 l_Res_rec.group_id := l_implicit_rec.GROUP_ID;
845 l_Res_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
846 l_Res_rec.last_updated_by_name := l_implicit_rec.LAST_UPDATED_BY_NAME;
847 l_Res_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
848 l_Res_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
849 l_Res_rec.line_code := l_implicit_rec.LINE_CODE;
850 l_Res_rec.line_id := l_implicit_rec.LINE_ID;
851 l_Res_rec.move_transaction_id := l_implicit_rec.MOVE_TRANSACTION_ID;
852 l_Res_rec.operation_seq_num := l_implicit_rec.OPERATION_SEQ_NUM;
853 l_Res_rec.organization_code := l_implicit_rec.ORGANIZATION_CODE;
854 l_Res_rec.organization_id := l_implicit_rec.ORGANIZATION_ID;
855 l_Res_rec.po_header_id := l_implicit_rec.PO_HEADER_ID;
856 l_Res_rec.po_line_id := l_implicit_rec.PO_LINE_ID;
857 l_Res_rec.primary_item_id := l_implicit_rec.PRIMARY_ITEM_ID;
858 l_Res_rec.primary_quantity := l_implicit_rec.PRIMARY_QUANTITY;
859 l_Res_rec.primary_uom := l_implicit_rec.PRIMARY_UOM;
860 l_Res_rec.primary_uom_class := l_implicit_rec.PRIMARY_UOM_CLASS;
861 l_Res_rec.process_phase := l_implicit_rec.PROCESS_PHASE;
862 l_Res_rec.process_status := l_implicit_rec.PROCESS_STATUS;
863 l_Res_rec.program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
864 l_Res_rec.program_id := l_implicit_rec.PROGRAM_ID;
865 l_Res_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
866 l_Res_rec.project_id := l_implicit_rec.PROJECT_ID;
867 l_Res_rec.rcv_transaction_id := l_implicit_rec.RCV_TRANSACTION_ID;
868 l_Res_rec.reason_id := l_implicit_rec.REASON_ID;
869 l_Res_rec.reason_name := l_implicit_rec.REASON_NAME;
870 l_Res_rec.receiving_account_id := l_implicit_rec.RECEIVING_ACCOUNT_ID;
871 l_Res_rec.reference := l_implicit_rec.REFERENCE;
872 l_Res_rec.repetitive_schedule_id := l_implicit_rec.REPETITIVE_SCHEDULE_ID;
873 l_Res_rec.request_id := l_implicit_rec.REQUEST_ID;
874 l_Res_rec.resource_code := l_implicit_rec.RESOURCE_CODE;
875 l_Res_rec.resource_id := l_implicit_rec.RESOURCE_ID;
876 l_Res_rec.resource_seq_num := l_implicit_rec.RESOURCE_SEQ_NUM;
877 l_Res_rec.resource_type := l_implicit_rec.RESOURCE_TYPE;
878 l_Res_rec.source_code := l_implicit_rec.SOURCE_CODE;
879 l_Res_rec.source_line_id := l_implicit_rec.SOURCE_LINE_ID;
880 l_Res_rec.standard_rate_flag := l_implicit_rec.STANDARD_RATE_FLAG;
881 l_Res_rec.task_id := l_implicit_rec.TASK_ID;
882 l_Res_rec.transaction_date := l_implicit_rec.TRANSACTION_DATE;
883 l_Res_rec.transaction_id := l_implicit_rec.TRANSACTION_ID;
884 l_Res_rec.transaction_quantity := l_implicit_rec.TRANSACTION_QUANTITY;
885 l_Res_rec.transaction_type := l_implicit_rec.TRANSACTION_TYPE;
886 l_Res_rec.transaction_uom := l_implicit_rec.TRANSACTION_UOM;
887 l_Res_rec.usage_rate_or_amount := l_implicit_rec.USAGE_RATE_OR_AMOUNT;
888 l_Res_rec.wip_entity_id := l_implicit_rec.WIP_ENTITY_ID;
889 l_Res_rec.wip_entity_name := l_implicit_rec.WIP_ENTITY_NAME;
890
891 l_Res_tbl(l_Res_tbl.COUNT + 1) := l_Res_rec;
892
893 END LOOP;
894
895
896 -- PK sent and no rows found
897
898 IF
899 (p_po_header_id IS NOT NULL
900 AND
901 p_po_header_id <> FND_API.G_MISS_NUM)
902 AND
903 (l_Res_tbl.COUNT = 0)
904 THEN
905 RAISE NO_DATA_FOUND;
906 END IF;
907
908
909 -- Return fetched table
910 RETURN l_Res_tbl;
911
912 EXCEPTION
913
914 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
915
916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
917
918 WHEN OTHERS THEN
919
920 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
921 THEN
922 FND_MSG_PUB.Add_Exc_Msg
923 ( G_PKG_NAME
924 , 'Query_Rows'
925 );
926 END IF;
927
928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
929
930 END Query_Rows;
931
932 -- Procedure lock_Row
933 --
934
935 PROCEDURE Lock_Row
936 ( x_return_status OUT NOCOPY VARCHAR2
937 , p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type
938 , x_Res_rec OUT NOCOPY WIP_Transaction_PUB.Res_Rec_Type
939 )
940 IS
941 l_Res_rec WIP_Transaction_PUB.Res_Rec_Type;
942 BEGIN
943
944 SELECT ACCT_PERIOD_ID
945 , ACTIVITY_ID
946 , ACTIVITY_NAME
947 , ACTUAL_RESOURCE_RATE
948 , ATTRIBUTE1
949 , ATTRIBUTE10
950 , ATTRIBUTE11
951 , ATTRIBUTE12
955 , ATTRIBUTE2
952 , ATTRIBUTE13
953 , ATTRIBUTE14
954 , ATTRIBUTE15
956 , ATTRIBUTE3
957 , ATTRIBUTE4
958 , ATTRIBUTE5
959 , ATTRIBUTE6
960 , ATTRIBUTE7
961 , ATTRIBUTE8
962 , ATTRIBUTE9
963 , ATTRIBUTE_CATEGORY
964 , AUTOCHARGE_TYPE
965 , BASIS_TYPE
966 , COMPLETION_TRANSACTION_ID
967 , CREATED_BY
968 , CREATED_BY_NAME
969 , CREATION_DATE
970 , CURRENCY_ACTUAL_RESOURCE_RATE
971 , CURRENCY_CODE
972 , CURRENCY_CONVERSION_DATE
973 , CURRENCY_CONVERSION_RATE
974 , CURRENCY_CONVERSION_TYPE
975 , DEPARTMENT_CODE
976 , DEPARTMENT_ID
977 , EMPLOYEE_ID
978 , EMPLOYEE_NUM
979 , ENTITY_TYPE
980 , GROUP_ID
981 , LAST_UPDATED_BY
982 , LAST_UPDATED_BY_NAME
983 , LAST_UPDATE_DATE
984 , LAST_UPDATE_LOGIN
985 , LINE_CODE
986 , LINE_ID
987 , MOVE_TRANSACTION_ID
988 , OPERATION_SEQ_NUM
989 , ORGANIZATION_CODE
990 , ORGANIZATION_ID
991 , PO_HEADER_ID
992 , PO_LINE_ID
993 , PRIMARY_ITEM_ID
994 , PRIMARY_QUANTITY
995 , PRIMARY_UOM
996 , PRIMARY_UOM_CLASS
997 , PROCESS_PHASE
998 , PROCESS_STATUS
999 , PROGRAM_APPLICATION_ID
1000 , PROGRAM_ID
1001 , PROGRAM_UPDATE_DATE
1002 , PROJECT_ID
1003 , RCV_TRANSACTION_ID
1004 , REASON_ID
1005 , REASON_NAME
1006 , RECEIVING_ACCOUNT_ID
1007 , REFERENCE
1008 , REPETITIVE_SCHEDULE_ID
1009 , REQUEST_ID
1010 , RESOURCE_CODE
1011 , RESOURCE_ID
1012 , RESOURCE_SEQ_NUM
1013 , RESOURCE_TYPE
1014 , SOURCE_CODE
1015 , SOURCE_LINE_ID
1016 , STANDARD_RATE_FLAG
1017 , TASK_ID
1018 , TRANSACTION_DATE
1019 , TRANSACTION_ID
1020 , TRANSACTION_QUANTITY
1021 , TRANSACTION_TYPE
1022 , TRANSACTION_UOM
1023 , USAGE_RATE_OR_AMOUNT
1024 , WIP_ENTITY_ID
1025 , WIP_ENTITY_NAME
1026 INTO l_Res_rec.acct_period_id
1027 , l_Res_rec.activity_id
1028 , l_Res_rec.activity_name
1029 , l_Res_rec.actual_resource_rate
1030 , l_Res_rec.attribute1
1031 , l_Res_rec.attribute10
1032 , l_Res_rec.attribute11
1033 , l_Res_rec.attribute12
1034 , l_Res_rec.attribute13
1035 , l_Res_rec.attribute14
1036 , l_Res_rec.attribute15
1037 , l_Res_rec.attribute2
1038 , l_Res_rec.attribute3
1039 , l_Res_rec.attribute4
1040 , l_Res_rec.attribute5
1041 , l_Res_rec.attribute6
1042 , l_Res_rec.attribute7
1043 , l_Res_rec.attribute8
1044 , l_Res_rec.attribute9
1045 , l_Res_rec.attribute_category
1046 , l_Res_rec.autocharge_type
1047 , l_Res_rec.basis_type
1048 , l_Res_rec.completion_transaction_id
1049 , l_Res_rec.created_by
1050 , l_Res_rec.created_by_name
1051 , l_Res_rec.creation_date
1052 , l_Res_rec.currency_actual_rsc_rate
1053 , l_Res_rec.currency_code
1054 , l_Res_rec.currency_conversion_date
1055 , l_Res_rec.currency_conversion_rate
1056 , l_Res_rec.currency_conversion_type
1057 , l_Res_rec.department_code
1058 , l_Res_rec.department_id
1059 , l_Res_rec.employee_id
1060 , l_Res_rec.employee_num
1061 , l_Res_rec.entity_type
1062 , l_Res_rec.group_id
1063 , l_Res_rec.last_updated_by
1064 , l_Res_rec.last_updated_by_name
1065 , l_Res_rec.last_update_date
1066 , l_Res_rec.last_update_login
1067 , l_Res_rec.line_code
1068 , l_Res_rec.line_id
1069 , l_Res_rec.move_transaction_id
1070 , l_Res_rec.operation_seq_num
1071 , l_Res_rec.organization_code
1072 , l_Res_rec.organization_id
1073 , l_Res_rec.po_header_id
1074 , l_Res_rec.po_line_id
1075 , l_Res_rec.primary_item_id
1076 , l_Res_rec.primary_quantity
1077 , l_Res_rec.primary_uom
1078 , l_Res_rec.primary_uom_class
1079 , l_Res_rec.process_phase
1080 , l_Res_rec.process_status
1081 , l_Res_rec.program_application_id
1082 , l_Res_rec.program_id
1083 , l_Res_rec.program_update_date
1084 , l_Res_rec.project_id
1085 , l_Res_rec.rcv_transaction_id
1086 , l_Res_rec.reason_id
1087 , l_Res_rec.reason_name
1088 , l_Res_rec.receiving_account_id
1089 , l_Res_rec.reference
1090 , l_Res_rec.repetitive_schedule_id
1091 , l_Res_rec.request_id
1092 , l_Res_rec.resource_code
1093 , l_Res_rec.resource_id
1097 , l_Res_rec.source_line_id
1094 , l_Res_rec.resource_seq_num
1095 , l_Res_rec.resource_type
1096 , l_Res_rec.source_code
1098 , l_Res_rec.standard_rate_flag
1099 , l_Res_rec.task_id
1100 , l_Res_rec.transaction_date
1101 , l_Res_rec.transaction_id
1102 , l_Res_rec.transaction_quantity
1103 , l_Res_rec.transaction_type
1104 , l_Res_rec.transaction_uom
1105 , l_Res_rec.usage_rate_or_amount
1106 , l_Res_rec.wip_entity_id
1107 , l_Res_rec.wip_entity_name
1108 FROM WIP_COST_TXN_INTERFACE
1109 WHERE PO_HEADER_ID = p_Res_rec.po_header_id
1110 FOR UPDATE NOWAIT;
1111
1112 -- Row locked. Compare IN attributes to DB attributes.
1113
1114 IF WIP_GLOBALS.Equal(p_Res_rec.acct_period_id,
1115 l_Res_rec.acct_period_id)
1116 AND WIP_GLOBALS.Equal(p_Res_rec.activity_id,
1117 l_Res_rec.activity_id)
1118 AND WIP_GLOBALS.Equal(p_Res_rec.activity_name,
1119 l_Res_rec.activity_name)
1120 AND WIP_GLOBALS.Equal(p_Res_rec.actual_resource_rate,
1121 l_Res_rec.actual_resource_rate)
1122 AND WIP_GLOBALS.Equal(p_Res_rec.attribute1,
1123 l_Res_rec.attribute1)
1124 AND WIP_GLOBALS.Equal(p_Res_rec.attribute10,
1125 l_Res_rec.attribute10)
1126 AND WIP_GLOBALS.Equal(p_Res_rec.attribute11,
1127 l_Res_rec.attribute11)
1128 AND WIP_GLOBALS.Equal(p_Res_rec.attribute12,
1129 l_Res_rec.attribute12)
1130 AND WIP_GLOBALS.Equal(p_Res_rec.attribute13,
1131 l_Res_rec.attribute13)
1132 AND WIP_GLOBALS.Equal(p_Res_rec.attribute14,
1133 l_Res_rec.attribute14)
1134 AND WIP_GLOBALS.Equal(p_Res_rec.attribute15,
1135 l_Res_rec.attribute15)
1136 AND WIP_GLOBALS.Equal(p_Res_rec.attribute2,
1137 l_Res_rec.attribute2)
1138 AND WIP_GLOBALS.Equal(p_Res_rec.attribute3,
1139 l_Res_rec.attribute3)
1140 AND WIP_GLOBALS.Equal(p_Res_rec.attribute4,
1141 l_Res_rec.attribute4)
1142 AND WIP_GLOBALS.Equal(p_Res_rec.attribute5,
1143 l_Res_rec.attribute5)
1144 AND WIP_GLOBALS.Equal(p_Res_rec.attribute6,
1145 l_Res_rec.attribute6)
1146 AND WIP_GLOBALS.Equal(p_Res_rec.attribute7,
1147 l_Res_rec.attribute7)
1148 AND WIP_GLOBALS.Equal(p_Res_rec.attribute8,
1149 l_Res_rec.attribute8)
1150 AND WIP_GLOBALS.Equal(p_Res_rec.attribute9,
1151 l_Res_rec.attribute9)
1152 AND WIP_GLOBALS.Equal(p_Res_rec.attribute_category,
1153 l_Res_rec.attribute_category)
1154 AND WIP_GLOBALS.Equal(p_Res_rec.autocharge_type,
1155 l_Res_rec.autocharge_type)
1156 AND WIP_GLOBALS.Equal(p_Res_rec.basis_type,
1157 l_Res_rec.basis_type)
1158 AND WIP_GLOBALS.Equal(p_Res_rec.completion_transaction_id,
1159 l_Res_rec.completion_transaction_id)
1160 AND WIP_GLOBALS.Equal(p_Res_rec.created_by,
1161 l_Res_rec.created_by)
1162 AND WIP_GLOBALS.Equal(p_Res_rec.created_by_name,
1163 l_Res_rec.created_by_name)
1164 AND WIP_GLOBALS.Equal(p_Res_rec.creation_date,
1165 l_Res_rec.creation_date)
1166 AND WIP_GLOBALS.Equal(p_Res_rec.currency_actual_rsc_rate,
1167 l_Res_rec.currency_actual_rsc_rate)
1168 AND WIP_GLOBALS.Equal(p_Res_rec.currency_code,
1169 l_Res_rec.currency_code)
1170 AND WIP_GLOBALS.Equal(p_Res_rec.currency_conversion_date,
1171 l_Res_rec.currency_conversion_date)
1172 AND WIP_GLOBALS.Equal(p_Res_rec.currency_conversion_rate,
1173 l_Res_rec.currency_conversion_rate)
1174 AND WIP_GLOBALS.Equal(p_Res_rec.currency_conversion_type,
1175 l_Res_rec.currency_conversion_type)
1176 AND WIP_GLOBALS.Equal(p_Res_rec.department_code,
1177 l_Res_rec.department_code)
1178 AND WIP_GLOBALS.Equal(p_Res_rec.department_id,
1179 l_Res_rec.department_id)
1180 AND WIP_GLOBALS.Equal(p_Res_rec.employee_id,
1181 l_Res_rec.employee_id)
1182 AND WIP_GLOBALS.Equal(p_Res_rec.employee_num,
1183 l_Res_rec.employee_num)
1184 AND WIP_GLOBALS.Equal(p_Res_rec.entity_type,
1185 l_Res_rec.entity_type)
1186 AND WIP_GLOBALS.Equal(p_Res_rec.group_id,
1187 l_Res_rec.group_id)
1188 AND WIP_GLOBALS.Equal(p_Res_rec.last_updated_by,
1189 l_Res_rec.last_updated_by)
1190 AND WIP_GLOBALS.Equal(p_Res_rec.last_updated_by_name,
1191 l_Res_rec.last_updated_by_name)
1192 AND WIP_GLOBALS.Equal(p_Res_rec.last_update_date,
1193 l_Res_rec.last_update_date)
1194 AND WIP_GLOBALS.Equal(p_Res_rec.last_update_login,
1195 l_Res_rec.last_update_login)
1196 AND WIP_GLOBALS.Equal(p_Res_rec.line_code,
1197 l_Res_rec.line_code)
1198 AND WIP_GLOBALS.Equal(p_Res_rec.line_id,
1199 l_Res_rec.line_id)
1200 AND WIP_GLOBALS.Equal(p_Res_rec.move_transaction_id,
1201 l_Res_rec.move_transaction_id)
1205 l_Res_rec.organization_code)
1202 AND WIP_GLOBALS.Equal(p_Res_rec.operation_seq_num,
1203 l_Res_rec.operation_seq_num)
1204 AND WIP_GLOBALS.Equal(p_Res_rec.organization_code,
1206 AND WIP_GLOBALS.Equal(p_Res_rec.organization_id,
1207 l_Res_rec.organization_id)
1208 AND WIP_GLOBALS.Equal(p_Res_rec.po_header_id,
1209 l_Res_rec.po_header_id)
1210 AND WIP_GLOBALS.Equal(p_Res_rec.po_line_id,
1211 l_Res_rec.po_line_id)
1212 AND WIP_GLOBALS.Equal(p_Res_rec.primary_item_id,
1213 l_Res_rec.primary_item_id)
1214 AND WIP_GLOBALS.Equal(p_Res_rec.primary_quantity,
1215 l_Res_rec.primary_quantity)
1216 AND WIP_GLOBALS.Equal(p_Res_rec.primary_uom,
1217 l_Res_rec.primary_uom)
1218 AND WIP_GLOBALS.Equal(p_Res_rec.primary_uom_class,
1219 l_Res_rec.primary_uom_class)
1220 AND WIP_GLOBALS.Equal(p_Res_rec.process_phase,
1221 l_Res_rec.process_phase)
1222 AND WIP_GLOBALS.Equal(p_Res_rec.process_status,
1223 l_Res_rec.process_status)
1224 AND WIP_GLOBALS.Equal(p_Res_rec.program_application_id,
1225 l_Res_rec.program_application_id)
1226 AND WIP_GLOBALS.Equal(p_Res_rec.program_id,
1227 l_Res_rec.program_id)
1228 AND WIP_GLOBALS.Equal(p_Res_rec.program_update_date,
1229 l_Res_rec.program_update_date)
1230 AND WIP_GLOBALS.Equal(p_Res_rec.project_id,
1231 l_Res_rec.project_id)
1232 AND WIP_GLOBALS.Equal(p_Res_rec.rcv_transaction_id,
1233 l_Res_rec.rcv_transaction_id)
1234 AND WIP_GLOBALS.Equal(p_Res_rec.reason_id,
1235 l_Res_rec.reason_id)
1236 AND WIP_GLOBALS.Equal(p_Res_rec.reason_name,
1237 l_Res_rec.reason_name)
1238 AND WIP_GLOBALS.Equal(p_Res_rec.receiving_account_id,
1239 l_Res_rec.receiving_account_id)
1240 AND WIP_GLOBALS.Equal(p_Res_rec.reference,
1241 l_Res_rec.reference)
1242 AND WIP_GLOBALS.Equal(p_Res_rec.repetitive_schedule_id,
1243 l_Res_rec.repetitive_schedule_id)
1244 AND WIP_GLOBALS.Equal(p_Res_rec.request_id,
1245 l_Res_rec.request_id)
1246 AND WIP_GLOBALS.Equal(p_Res_rec.resource_code,
1247 l_Res_rec.resource_code)
1248 AND WIP_GLOBALS.Equal(p_Res_rec.resource_id,
1249 l_Res_rec.resource_id)
1250 AND WIP_GLOBALS.Equal(p_Res_rec.resource_seq_num,
1251 l_Res_rec.resource_seq_num)
1252 AND WIP_GLOBALS.Equal(p_Res_rec.resource_type,
1253 l_Res_rec.resource_type)
1254 AND WIP_GLOBALS.Equal(p_Res_rec.source_code,
1255 l_Res_rec.source_code)
1256 AND WIP_GLOBALS.Equal(p_Res_rec.source_line_id,
1257 l_Res_rec.source_line_id)
1258 AND WIP_GLOBALS.Equal(p_Res_rec.standard_rate_flag,
1259 l_Res_rec.standard_rate_flag)
1260 AND WIP_GLOBALS.Equal(p_Res_rec.task_id,
1261 l_Res_rec.task_id)
1262 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_date,
1263 l_Res_rec.transaction_date)
1264 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_id,
1265 l_Res_rec.transaction_id)
1266 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_quantity,
1267 l_Res_rec.transaction_quantity)
1268 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_type,
1269 l_Res_rec.transaction_type)
1270 AND WIP_GLOBALS.Equal(p_Res_rec.transaction_uom,
1271 l_Res_rec.transaction_uom)
1272 AND WIP_GLOBALS.Equal(p_Res_rec.usage_rate_or_amount,
1273 l_Res_rec.usage_rate_or_amount)
1274 AND WIP_GLOBALS.Equal(p_Res_rec.wip_entity_id,
1275 l_Res_rec.wip_entity_id)
1276 AND WIP_GLOBALS.Equal(p_Res_rec.wip_entity_name,
1277 l_Res_rec.wip_entity_name)
1278 THEN
1279
1280 -- Row has not changed. Set out parameter.
1281
1282 x_Res_rec := l_Res_rec;
1283
1284 -- Set return status
1285
1286 x_return_status := FND_API.G_RET_STS_SUCCESS;
1287 x_Res_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1288
1289 ELSE
1290
1291 -- Row has changed by another user.
1292
1293 x_return_status := FND_API.G_RET_STS_ERROR;
1294 x_Res_rec.return_status := FND_API.G_RET_STS_ERROR;
1295
1296 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1297 THEN
1298
1299 FND_MESSAGE.SET_NAME('WIP','WIP_LOCK_ROW_CHANGED');
1300 FND_MSG_PUB.Add;
1301
1302 END IF;
1303
1304 END IF;
1305 EXCEPTION
1306
1307 WHEN NO_DATA_FOUND THEN
1308
1309 x_return_status := FND_API.G_RET_STS_ERROR;
1310 x_Res_rec.return_status := FND_API.G_RET_STS_ERROR;
1311
1312 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1313 THEN
1314
1318 END IF;
1315 FND_MESSAGE.SET_NAME('WIP','WIP_LOCK_ROW_DELETED');
1316 FND_MSG_PUB.Add;
1317
1319 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
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_ALREADY_LOCKED');
1328 FND_MSG_PUB.Add;
1329
1330 END IF;
1331 WHEN OTHERS THEN
1332
1333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1334 x_Res_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1335
1336 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1337 THEN
1338 FND_MSG_PUB.Add_Exc_Msg
1339 ( G_PKG_NAME
1340 , 'Lock_Row'
1341 );
1342 END IF;
1343
1344 END Lock_Row;
1345
1346
1347 procedure print_record(p_Res_rec IN WIP_Transaction_PUB.Res_Rec_Type)
1348 is
1349 begin
1350
1351 null;
1352 -- dbms_output.put_line(' ');
1353 -- dbms_output.put_line('Resource Record*******************************');
1354 -- dbms_output.put_line('acct_period_id '||to_char(p_Res_rec.acct_period_id));
1355 -- dbms_output.put_line('activity_id '||to_char(p_Res_rec.activity_id));
1356 -- dbms_output.put_line('activity_name '||p_Res_rec.activity_name );
1357 -- dbms_output.put_line('actual_resource_rate '||to_char(p_Res_rec.actual_resource_rate ));
1358 -- dbms_output.put_line('autocharge_type '||to_char(p_Res_rec.autocharge_type ));
1359 -- dbms_output.put_line('basis_type '||to_char(p_Res_rec.basis_type ));
1360 -- dbms_output.put_line('completion_transaction_id '||to_char(p_Res_rec.completion_transaction_id ));
1361 -- dbms_output.put_line('created_by '||to_char(p_Res_rec.created_by ));
1362 -- dbms_output.put_line('created_by_name '||p_Res_rec.created_by_name );
1363 -- dbms_output.put_line('creation_date '||p_Res_rec.creation_date );
1364 -- dbms_output.put_line('currency_actual_rsc_rate '||to_char(p_Res_rec.currency_actual_rsc_rate ));
1365 -- dbms_output.put_line('currency_code '||p_Res_rec.currency_code );
1366 -- dbms_output.put_line('currency_conversion_date '||to_char(p_Res_rec.currency_conversion_date,'DD-MON-YY'));
1367 -- dbms_output.put_line('currency_conversion_rate '||to_char(p_Res_rec.currency_conversion_rate ));
1368 -- dbms_output.put_line('currency_conversion_type '||p_Res_rec.currency_conversion_type );
1369 -- dbms_output.put_line('department_code '||p_Res_rec.department_code );
1370 -- dbms_output.put_line('department_id '||to_char(p_Res_rec.department_id));
1371 -- dbms_output.put_line('employee_id '||to_char(p_Res_rec.employee_id ));
1372 -- dbms_output.put_line('employee_num '||p_Res_rec.employee_num );
1373 -- dbms_output.put_line('entity_type '||to_char(p_Res_rec.entity_type));
1374 -- dbms_output.put_line('group_id '||to_char(p_Res_rec.group_id ));
1375 -- dbms_output.put_line('last_updated_by '||to_char(p_Res_rec.last_updated_by));
1376 -- dbms_output.put_line('last_updated_by_name '||p_Res_rec.last_updated_by_name );
1377 -- dbms_output.put_line('last_update_date '||to_char(p_Res_rec.last_update_date ,'DD-MON-YY'));
1378 -- dbms_output.put_line('last_update_login '||to_char(p_Res_rec.last_update_login ));
1379 -- dbms_output.put_line('line_code '||p_Res_rec.line_code );
1380 -- dbms_output.put_line('line_id '||to_char(nvl(p_Res_rec.line_id,-1)));
1381 -- dbms_output.put_line('move_transaction_id '||to_char(p_Res_rec.move_transaction_id ));
1382 -- dbms_output.put_line('operation_seq_num '||to_char(p_Res_rec.operation_seq_num ));
1383 -- dbms_output.put_line('organization_code '||p_Res_rec.organization_code);
1384 -- dbms_output.put_line('organization_id '||to_char(p_Res_rec.organization_id));
1385 -- dbms_output.put_line('po_header_id '||to_char(p_Res_rec.po_header_id));
1386 -- dbms_output.put_line('po_line_id '||to_char(p_Res_rec.po_line_id));
1387 -- dbms_output.put_line('primary_item_id '||to_char(p_Res_rec.primary_item_id));
1388 -- dbms_output.put_line('primary_quantity '||to_char(p_Res_rec.primary_quantity ));
1389 -- dbms_output.put_line('primary_uom' ||p_Res_rec.primary_uom);
1390 -- dbms_output.put_line('primary_uom_class' || p_Res_rec.primary_uom_class);
1391 -- dbms_output.put_line('process_phase '||to_char(p_Res_rec.process_phase));
1392 -- dbms_output.put_line('process_status '||to_char(p_Res_rec.process_status));
1393 -- dbms_output.put_line('program_application_id '||to_char(p_Res_rec.program_application_id));
1394 -- dbms_output.put_line('program_id '||to_char(p_Res_rec.program_id));
1395 -- dbms_output.put_line('program_update_date '||to_char(p_Res_rec.program_update_date,'DD-MON-YY'));
1396 -- dbms_output.put_line('project_id '||to_char(p_Res_rec.project_id ));
1397 -- dbms_output.put_line('rcv_transaction_id '||to_char(nvl(p_Res_rec.rcv_transaction_id,-1)));
1398 -- dbms_output.put_line('reason_id '||to_char(p_Res_rec.reason_id ));
1399 -- dbms_output.put_line('reason_name '|| p_Res_rec.reason_name );
1400 -- dbms_output.put_line('receiving_account_id '||to_char(p_Res_rec.receiving_account_id));
1401 -- dbms_output.put_line('reference '|| p_Res_rec.reference );
1402 -- dbms_output.put_line('repetitive_schedule_id '||to_char(p_Res_rec.repetitive_schedule_id));
1403 -- dbms_output.put_line('request_id '||to_char(p_Res_rec.request_id ));
1404 -- dbms_output.put_line('resource_code'|| p_Res_rec.resource_code );
1405 -- dbms_output.put_line('resource_id '||to_char(p_Res_rec.resource_id));
1406 -- dbms_output.put_line('resource_seq_num '||to_char(p_Res_rec.resource_seq_num));
1407 -- dbms_output.put_line('resource_type '||to_char(p_Res_rec.resource_type));
1408 -- dbms_output.put_line('source_code '||p_Res_rec.source_code );
1409 -- dbms_output.put_line('source_line_id '||to_char(p_Res_rec.source_line_id));
1410 -- dbms_output.put_line('standard_rate_flag '||to_char(p_Res_rec.standard_rate_flag));
1411 -- dbms_output.put_line('task_id '||to_char(p_Res_rec.task_id ));
1412 -- dbms_output.put_line('transaction_date '||to_char(p_Res_rec.transaction_date ,'DD-MON-YY'));
1413 -- dbms_output.put_line('transaction_id '||to_char(p_Res_rec.transaction_id ));
1414 -- dbms_output.put_line('transaction_quantity '||to_char(p_Res_rec.transaction_quantity ));
1415 -- dbms_output.put_line('transaction_type '||to_char(p_Res_rec.transaction_type ));
1416 -- dbms_output.put_line('transaction_uom '||p_Res_rec.transaction_uom );
1417 -- dbms_output.put_line('usage_rate_or_amount '||to_char(p_Res_rec.usage_rate_or_amount));
1418 -- dbms_output.put_line('wip_entity_id '||to_char(p_Res_rec.wip_entity_id));
1419 -- dbms_output.put_line('wip_entity_name '||p_Res_rec.wip_entity_name );
1420
1421 exception
1422 when others then
1423 null;
1424 end print_record;
1425
1426
1427 END WIP_Res_Util;