[Home] [Help]
PACKAGE BODY: APPS.WIP_EAM_RESOURCE_TRANSACTION
Source
1 PACKAGE BODY WIP_EAM_RESOURCE_TRANSACTION as
2 /* $Header: wiprstxb.pls 120.22.12020000.4 2013/01/02 10:13:20 vboddapa ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):= 'WIP_EAM_RESOURCE_TRANSACTION';
5
6 PROCEDURE resource_validate (
7 p_api_version IN NUMBER
8 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
9 ,p_commit IN VARCHAR2 := fnd_api.g_false
10 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
11 ,p_wip_entity_id IN NUMBER
12 ,p_operation_seq_num IN NUMBER
13 ,p_organization_id IN NUMBER
14 ,p_resource_seq_num IN NUMBER
15 ,p_resource_code IN VARCHAR2
16 ,p_uom_code IN VARCHAR2
17 ,p_employee_name IN VARCHAR2
18 ,p_equipment_name IN VARCHAR2
19 ,p_reason IN VARCHAR2
20 ,p_charge_dept IN VARCHAR2
21 ,p_start_time IN DATE DEFAULT TRUNC(SYSDATE) --for bug 8532793
22 ,x_resource_seq_num OUT NOCOPY NUMBER
23 ,x_actual_resource_rate OUT NOCOPY NUMBER
24 ,x_status OUT NOCOPY NUMBER
25 ,x_res_status OUT NOCOPY NUMBER
26 ,x_uom_status OUT NOCOPY NUMBER
27 ,x_employee_status OUT NOCOPY NUMBER
28 ,x_employee_id OUT NOCOPY NUMBER
29 ,x_employee_number OUT NOCOPY VARCHAR2
30 ,x_equipment_status OUT NOCOPY NUMBER
31 ,x_reason_status OUT NOCOPY NUMBER
32 ,x_charge_dept_status OUT NOCOPY NUMBER
33 ,x_machine_status OUT NOCOPY NUMBER
34 ,x_person_status OUT NOCOPY NUMBER
35 ,x_work_order_status OUT NOCOPY NUMBER
36 ,x_instance_id OUT NOCOPY NUMBER
37 ,x_charge_dept_id OUT NOCOPY NUMBER
38 ,x_return_status OUT NOCOPY VARCHAR2
39 ,x_msg_count OUT NOCOPY NUMBER
40 ,x_msg_data OUT NOCOPY VARCHAR2)
41
42 IS
43 l_api_name CONSTANT VARCHAR2(30) := 'resource_validate';
44 l_api_version CONSTANT NUMBER := 1.0;
45 l_wip_entity_id NUMBER := 0;
46 l_operation_seq_num NUMBER := 0;
47 l_stmt_num NUMBER := 0;
48 l_resource_id NUMBER := 0;
49 l_resource_id1 NUMBER := 0;
50 l_resource_seq_num NUMBER := 0;
51 l_invalid_combo_status NUMBER := 0;
52 l_temp_resource_id NUMBER := 0;
53 l_res_exists_status NUMBER := 0;
54 res_exists NUMBER := 0;
55 l_res_seq_num NUMBER := 0;
56 l_res_code VARCHAR2(80) ;
57 l_res_id NUMBER := 0;
58 l_temp_res_id NUMBER := 0;
59 l_invalid_res_combo NUMBER := 0;
60 l_temp_resource_code VARCHAR2(80) ;
61 l_temp_resource_type NUMBER;
62 l_resource_type NUMBER;
63 l_resource_code1 VARCHAR2(80) ;
64 resource_exists NUMBER := 0;
65 l_uom_code VARCHAR2(30) ;
66 uom_exists NUMBER := 0;
67 uom_status NUMBER := 0;
68 employee_exists NUMBER := 0;
69 employee_status NUMBER := 0;
70 l_empl_full_name VARCHAR2(240) ;
71 equipment_exists NUMBER := 0;
72 equipment_status NUMBER := 0;
73 l_equip_serial_no VARCHAR2(80) ;
74 reason_exists NUMBER := 0;
75 reason_status NUMBER := 0;
76 l_reason VARCHAR2(240) ;
77 charge_dept_exists NUMBER := 0;
78 charge_dept_status NUMBER := 0;
79 l_charge_dept VARCHAR2(240) ;
80 l_person_status NUMBER := 0;
81 l_machine_status NUMBER := 0;
82 l_person_id NUMBER := 0;
83 l_employee_number VARCHAR2(30) ;
84 l_actual_employee_id NUMBER := 0;
85 l_actual_employee_number VARCHAR2(30) ;
86 l_charge_dept_id NUMBER := 0;
87 l_actual_charge_dept_id NUMBER := 0;
88 l_owning_department_id NUMBER := 0;
89 l_instance_id NUMBER := 0;
90
91
92 l_status_type NUMBER ;
93 l_we_entity_type NUMBER ;
94
95
96 v_resource_code BOM_RESOURCES.RESOURCE_CODE%TYPE;
97
98 --added the cursor in the declare section instead of putting inline cursors
99 CURSOR c_res_in_bom_cur IS --rhshriva
100 Select resource_id,resource_code
101 from bom_resources
102 where organization_id =p_organization_id;
103
104 CURSOR c_res_cur IS --rhshriva
105 select br.resource_id,br.resource_code,br.resource_type
106 from
107 cst_activities ca,
108 bom_department_resources bdr,
109 bom_resources br
110 where br.organization_id = p_organization_id
111 and bdr.department_id = (select department_id
112 from wip_operations
113 where organization_id = p_organization_id
114 and wip_entity_id =p_wip_entity_id
115 and operation_seq_num = p_operation_seq_num )
116 and br.resource_id = bdr.resource_id
117 and br.default_activity_id = ca.activity_id (+)
118 and nvl(ca.disable_date(+),sysdate+1) > sysdate
119 and nvl(br.disable_date,sysdate+1) > sysdate
120 and (ca.organization_id is null or ca.organization_id =p_organization_id );
121
122 CURSOR c_res_seq_num IS --rhshriva
123 select wor.resource_seq_num,
124 br.resource_code,
125 br.resource_id
126 from cst_activities ca,
127 bom_resources br,
128 wip_operation_resources wor
129 where wor.repetitive_schedule_id is null
130 and br.organization_id = wor.organization_id
131 and wor.resource_id = br.resource_id
132 and wor.activity_id = ca.activity_id (+)
133 and nvl(ca.disable_date(+),sysdate+1)> sysdate
134 and wor.organization_id = p_organization_id
135 and wor.wip_entity_id =p_wip_entity_id
136 and wor.operation_seq_num = p_operation_seq_num ;
137
138
139
140 CURSOR c_uom_cur(l_temp_res_id1 NUMBER) IS --rhshriva
141 select distinct muc.uom_code
142 from mtl_uom_conversions muc,mtl_units_of_measure muom
143 where muom.uom_code = muc.uom_code
144 and muc.inventory_item_id = 0
145 and nvl(muc.disable_date,sysdate+1) >sysdate
146 and muc.uom_class in (select muc2.uom_class
147 from mtl_uom_conversions muc2,bom_resources br
148 where muc2.inventory_item_id = 0
149 and muc2.uom_code = br.unit_of_measure
150 and br.resource_id = l_temp_res_id1);
151
152
153 CURSOR c_empl_cur(l_temp_res_id2 NUMBER) IS --rhshriva
154 select distinct full_name,person_id,employee_number
155 from per_people_f
156 where
157 p_start_time BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE --for bug 8532793
158 and person_id in (select person_id
159 from bom_resource_employees
160 where organization_id =p_organization_id
161 and resource_id = l_temp_res_id2 );
162
163 CURSOR c_equip_cur(l_temp_res_id3 NUMBER) IS --for rhshriva
164 select distinct serial_number
165 from bom_dept_res_instances
166 where instance_id in (select instance_id
167 from bom_resource_equipments
168 where organization_id =p_organization_id
169 and resource_id =l_temp_res_id3 );
170
171 /* Changed query for cursor to take care of the shared resources too - Bug 3873717 */
172 /* Bug 5262052 .Changed query to validate the resource code , irresepective of whether employee is entered or not.
173 Resources can be charged w/o an employee.Proper employee are fetched in Employees LOV in JSP */
174
175 CURSOR c_charge_dept_cur(l_temp_res_id4 NUMBER) IS
176 select distinct bd.department_code, bd.department_id
177 from bom_department_resources bdr, bom_departments bd
178 where bdr.department_id = bd.department_id
179 and bdr.resource_id = l_temp_res_id4 ;
180
181
182 CURSOR c_reason_cur IS -- rhshriva
183 select reason_name
184 from mtl_transaction_reasons
185 where nvl(disable_date, sysdate + 1) > sysdate;
186
187 -- Cursor to fetch if resource seq num corresponding to resource_id.
188 CURSOR c_resource_seq_num IS
189 select resource_seq_num
190 from wip_operation_resources
191 where wip_entity_id = p_wip_entity_id
192 and operation_seq_num = p_operation_seq_num
193 and organization_id = p_organization_id
194 and resource_id = l_resource_id;
195
196 -- Cursor to fetch maximum resource sequence number attached to operation.
197 CURSOR c_cur_resource_seq_num IS
198 select resource_seq_num
199 from wip_operation_resources
200 where wip_entity_id = p_wip_entity_id
201 and operation_seq_num = p_operation_seq_num
202 and organization_id = p_organization_id
203 order by resource_seq_num desc;
204
205 BEGIN
206 -- Standard Start of API savepoint
207 l_stmt_num := 10;
208 SAVEPOINT get_resource_validate_pvt;
209
210 l_stmt_num := 20;
211 -- Standard call to check for call compatibility.
212 IF NOT fnd_api.compatible_api_call(
213 l_api_version
214 ,p_api_version
215 ,l_api_name
216 ,g_pkg_name) THEN
217 RAISE fnd_api.g_exc_unexpected_error;
218 END IF;
219
220 l_stmt_num := 30;
221 -- Initialize message list if p_init_msg_list is set to TRUE.
222 IF fnd_api.to_boolean(p_init_msg_list) THEN
223 fnd_msg_pub.initialize;
224 END IF;
225
226 l_stmt_num := 40;
227 -- Initialize API return status to success
228 x_return_status := fnd_api.g_ret_sts_success;
229
230 l_stmt_num := 50;
231 -- API body
232
233 l_wip_entity_id := p_wip_entity_id;
234 l_operation_seq_num := p_operation_seq_num;
235 l_resource_seq_num := p_resource_seq_num;
236
237 v_resource_code := p_resource_code ;
238
239 l_stmt_num := 60;
240
241
242
243 open c_res_in_bom_cur ;--for --rhshriva
244
245 LOOP
246 fetch c_res_in_bom_cur into l_resource_id1, l_resource_code1;
247 exit when c_res_in_bom_cur%NOTFOUND;
248
249 if (l_resource_code1 = v_resource_code) then
250 resource_exists := 1;
251
252 l_stmt_num := 70;
253
254 -- Check whether the resource code entered is valid and matches with a resource in BOM_RESOURCES
255 open c_res_cur ;
256
257 l_stmt_num := 75;
258
259 LOOP
260
261 fetch c_res_cur into l_temp_resource_id,l_temp_resource_code,l_temp_resource_type ;
262 Exit when c_res_cur%NOTFOUND;
263
264 l_stmt_num := 80;
265
266 if (l_temp_resource_code = l_resource_code1) then
267 res_exists := 1;
268 l_temp_res_id := l_temp_resource_id; -- actual checking of the resource
269 l_resource_type := l_temp_resource_type;
270 end if;
271 end loop;
272 close c_res_cur;
273 end if;
274 end loop;
275 close c_res_in_bom_cur;
276
277 l_stmt_num := 90;
278
279 --Set the status to show that the resource exists.. resource_exists = 0 means it does not exist else it exists
280
281 if(resource_exists = 0) then
282 l_res_exists_status := 1;
283 end if;
284
285 if (res_exists = 0) then
286 l_res_exists_status := 1;
287 end if;
288
289 l_stmt_num := 100;
290
291 if (p_employee_name is not null) then
292
293 if (l_resource_type <> 2) then
294 l_person_status := 1;
295
296 end if;
297 end if;
298
299 if (p_equipment_name is not null) then
300
301 if (l_resource_type <> 1) then
302 l_machine_status := 1;
303 end if;
304 end if;
305
306 x_machine_status := l_machine_status;
307 x_person_status := l_person_status;
308
309 -- Check the resource code - resource sequence combination, in order to verify any illegal entry throgh the JSP
310
311 if (p_resource_code is not null) then
312 select resource_id into l_resource_id from bom_resources where resource_code = p_resource_code and organization_id = p_organization_id ;
313 end if;
314
315 if(l_resource_id <> 0 and l_resource_seq_num is null) then
316
317 open c_resource_seq_num;
318 fetch c_resource_seq_num into l_resource_seq_num;
319 if (c_resource_seq_num%NOTFOUND) then
320 open c_cur_resource_seq_num;
321 fetch c_cur_resource_seq_num into l_resource_seq_num;
322 if (c_cur_resource_seq_num%NOTFOUND) then
323 l_resource_seq_num := 10;
324 else
325 l_resource_seq_num := l_resource_seq_num + 1;
326 end if;
327 close c_cur_resource_seq_num;
328 end if;
329 end if;
330 x_resource_seq_num := l_resource_seq_num;
331
332
333 if (l_temp_res_id <> 0) then
334 open c_res_seq_num;
335
336 l_stmt_num := 110;
337
338 loop
339
340 fetch c_res_seq_num into l_res_seq_num, l_res_code, l_res_id ;
341 exit when c_res_seq_num % NOTFOUND;
342
343 l_stmt_num := 120;
344
345 -- Check for resource_code and resource_sequence combination
346
347 if (l_res_seq_num = l_resource_seq_num ) and (l_res_id <> l_resource_id) then
348 l_invalid_res_combo := 1;
349 end if;
350 end loop;
351 close c_res_seq_num;
352
353 end if;
354
355
356 if (l_invalid_res_combo = 1) then
357 l_invalid_combo_status := 1;
358 end if;
359
360 x_status := l_invalid_combo_status;
361 x_res_status := l_res_exists_status;
362
363 -- Check for UOM entered through the JSP
364
365 open c_uom_cur(l_temp_res_id1=> l_temp_res_id);
366 loop
367 fetch c_uom_cur into l_uom_code;
368 EXIT WHEN c_uom_cur%NOTFOUND;
369
370 -- Actual check for UOM value entered with the valid values of UOM
371
372 if (upper(l_uom_code) = upper(p_uom_code)) then
373 uom_exists := 1;
374 end if;
375 end loop;
376 close c_uom_cur;
377
378 if (uom_exists = 0) then
379 uom_status := 1;
380 end if;
381
382 x_uom_status := uom_status;
383
384 -- Check for employee name entered through the JSP
385
386 if (p_employee_name is not null) then
387
388 open c_empl_cur(l_temp_res_id2=>l_temp_res_id);
389 loop
390 fetch c_empl_cur into l_empl_full_name,l_person_id,l_employee_number;
391 EXIT WHEN c_empl_cur%NOTFOUND;
392
393 -- Check for the employee name within the valid values of employee name
394 if (l_empl_full_name = p_employee_name) then
395 employee_exists := 1;
396 l_actual_employee_id := l_person_id;
397 l_actual_employee_number := l_employee_number;
398 x_employee_id := l_actual_employee_id;
399 x_employee_number := l_actual_employee_number;
400 select instance_id into l_instance_id from bom_resource_employees where resource_id = l_temp_res_id and person_id = l_actual_employee_id;
401 end if;
402 end loop;
403 close c_empl_cur;
404
405 if (employee_exists = 0) then
406 employee_status := 1;
407 end if;
408
409 --Bug 2182515: populate actual labor rate if employee instance given
410 if (employee_exists = 1) then
411 BEGIN
412 SELECT hourly_labor_rate
413 INTO x_actual_resource_rate
414 FROM wip_employee_labor_rates
415 WHERE employee_id = l_actual_employee_id
416 AND organization_id = p_organization_id
417 AND effective_date = (
418 SELECT MAX(effective_date)
419 FROM wip_employee_labor_rates
420 WHERE employee_id = l_actual_employee_id
421 AND organization_id = p_organization_id
422 AND effective_date <= sysdate
423 );
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 --OK: will happen if no labor rate is defined .
427 x_actual_resource_rate := null;
428 END;
429 end if; -- employee_exists = 1
430
431 end if; -- p_employee_name is not null
432
433 x_employee_status := employee_status;
434
435 -- Check for equipment name entered through JSP
436
437 if (p_equipment_name is not null) then
438
439 open c_equip_cur(l_temp_res_id3=>l_temp_res_id) ;
440 loop
441 fetch c_equip_cur into l_equip_serial_no;
442 EXIT WHEN c_equip_cur%NOTFOUND;
443
444 -- Check for equipment name to find out that the value entered does indeed exist in the valid list of values
445
446 if (l_equip_serial_no = p_equipment_name ) then
447 equipment_exists := 1;
448 select instance_id into l_instance_id from bom_dept_res_instances where resource_id = l_temp_res_id and serial_number = l_equip_serial_no ;
449 end if;
450 end loop;
451 close c_equip_cur;
452
453 if (equipment_exists = 0) then
454 equipment_status := 1;
455 end if;
456
457 end if;
458
459 x_equipment_status := equipment_status;
460
461 -- Check for reason entered through the JSP
462
463 if (p_reason is not null) then
464 open c_reason_cur ;
465 loop
466 fetch c_reason_cur into l_reason;
467 EXIT WHEN c_reason_cur%NOTFOUND;
468
469 -- Check to see that the value entered does indeed match with any valid value from the table
470
471 if (l_reason = p_reason ) then
472 reason_exists := 1;
473 end if;
474 end loop;
475 close c_reason_cur;
476
477 if (reason_exists = 0) then
478 reason_status := 1;
479 end if;
480
481 end if;
482
483 x_reason_status := reason_status;
484
485 if (p_wip_entity_id is not null) then
486 select owning_department into l_owning_department_id from wip_discrete_jobs where wip_entity_id = p_wip_entity_id;
487 end if;
488
489
490 -- Check for Charge Department entered through the JSP
491
492 if (p_charge_dept is not null) then
493
494 open c_charge_dept_cur(l_temp_res_id4=>l_temp_res_id);
495 loop
496 fetch c_charge_dept_cur into l_charge_dept, l_charge_dept_id;
497 EXIT WHEN c_charge_dept_cur%NOTFOUND;
498
499 -- Check to see that the value entered does indeed match with the valid List Of Values
500
501 if (l_charge_dept = p_charge_dept) then
502 charge_dept_exists := 1;
503 l_actual_charge_dept_id := l_charge_dept_id;
504 end if;
505 end loop;
506 close c_charge_dept_cur;
507
508 if (charge_dept_exists = 0) then
509 charge_dept_status := 1;
510 end if;
511
512
513
514
515 end if;
516
517 --start of fix for 3949853
518
519 select wdj.status_type, we.entity_type
520 into l_status_type, l_we_entity_type
521 from wip_discrete_jobs wdj, wip_entities we
522 where wdj.wip_entity_id = we.wip_entity_id
523 and wdj.wip_entity_id = l_wip_entity_id;
524
525 if ( ((l_status_type = 3) or (l_status_type = 4)) and l_we_entity_type = 6) then
526 x_work_order_status := 0;
527 else
528 x_work_order_status := 1;
529 end if;
530 --end of fix for 3949853
531
532 x_charge_dept_status := charge_dept_status;
533 x_charge_dept_id := l_actual_charge_dept_id;
534 x_instance_id := l_instance_id;
535
536 -- End of API body.
537 -- Standard check of p_commit.
538 IF fnd_api.to_boolean(p_commit) THEN
539 COMMIT WORK;
540 END IF;
541
542 l_stmt_num := 999;
543 -- Standard call to get message count and if count is 1, get message info.
544 fnd_msg_pub.count_and_get(
545 p_count => x_msg_count
546 ,p_data => x_msg_data);
547 EXCEPTION
548 WHEN fnd_api.g_exc_error THEN
549 ROLLBACK TO get_resource_validate_pvt
550 ;
551 x_return_status := fnd_api.g_ret_sts_error;
552 fnd_msg_pub.count_and_get(
553 -- p_encoded => FND_API.g_false
554 p_count => x_msg_count
555 ,p_data => x_msg_data);
556 WHEN fnd_api.g_exc_unexpected_error THEN
557 ROLLBACK TO get_resource_validate_pvt;
558 x_return_status := fnd_api.g_ret_sts_unexp_error;
559
560 fnd_msg_pub.count_and_get(
561 p_count => x_msg_count
562 ,p_data => x_msg_data);
563 WHEN OTHERS THEN
564 ROLLBACK TO get_resource_validate_pvt;
565 x_return_status := fnd_api.g_ret_sts_unexp_error;
566 IF fnd_msg_pub.check_msg_level(
567 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
568 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
569 END IF;
570
571 fnd_msg_pub.count_and_get(
572 p_count => x_msg_count
573 ,p_data => x_msg_data);
574
575 END resource_validate;
576
577
578
579
580
581 PROCEDURE insert_into_wcti(
582 p_api_version IN NUMBER
583 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
584 ,p_commit IN VARCHAR2 := fnd_api.g_false
585 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
586 ,p_wip_entity_id IN NUMBER
587 ,p_operation_seq_num IN NUMBER
588 ,p_organization_id IN NUMBER
589 ,p_transaction_qty IN NUMBER
590 ,p_transaction_date IN DATE
591 ,p_resource_seq_num IN NUMBER
592 ,p_uom IN VARCHAR2
593 ,p_resource_code IN VARCHAR2
594 ,p_reason_name IN VARCHAR2
595 ,p_reference IN VARCHAR2
596 ,p_instance_id IN NUMBER
597 ,p_serial_number IN VARCHAR2
598 ,p_charge_dept_id IN NUMBER
599 ,p_attribute_category IN VARCHAR2
600 ,p_attribute1 IN VARCHAR2
601 ,p_attribute2 IN VARCHAR2
602 ,p_attribute3 IN VARCHAR2
603 ,p_attribute4 IN VARCHAR2
604 ,p_attribute5 IN VARCHAR2
605 ,p_attribute6 IN VARCHAR2
606 ,p_attribute7 IN VARCHAR2
607 ,p_attribute8 IN VARCHAR2
608 ,p_attribute9 IN VARCHAR2
609 ,p_attribute10 IN VARCHAR2
610 ,p_attribute11 IN VARCHAR2
611 ,p_attribute12 IN VARCHAR2
612 ,p_attribute13 IN VARCHAR2
613 ,p_attribute14 IN VARCHAR2
614 ,p_attribute15 IN VARCHAR2
615 ,p_actual_resource_rate IN NUMBER
616 ,p_employee_id IN NUMBER
617 ,p_employee_number IN VARCHAR2
618 ,x_return_status OUT NOCOPY VARCHAR2
619 ,x_msg_count OUT NOCOPY NUMBER
620 ,x_msg_data OUT NOCOPY VARCHAR2)
621
622 IS
623 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wcti';
624 l_api_version CONSTANT NUMBER := 1.0;
625 l_wip_entity_id NUMBER := 0;
626 l_operation_seq_num NUMBER := 0;
627 l_organization_id NUMBER := 0;
628 l_stmt_num NUMBER := 0;
629 l_groupid NUMBER := 0;
630 l_source_code VARCHAR2(30) ;
631 l_organization_code VARCHAR2(30) ;
632 l_wip_entity_name VARCHAR2(240) ;
633 l_entity_type NUMBER := 0;
634 l_primary_item_id NUMBER := 0;
635 l_project_id NUMBER := 0;
636 l_task_id NUMBER := 0;
637 l_resource_code VARCHAR2(10) ;
638 l_resource_id NUMBER := 0;
639 l_resource_type NUMBER := 0;
640 l_uom VARCHAR2(10) ;
641 l_txn_uom VARCHAR2(10) ;
642 l_usage_rate_or_amount NUMBER := 0;
643 l_basis_type NUMBER := 0;
644 l_activity_id NUMBER := 0;
645 l_activity_name VARCHAR2(10) ;
646 l_standard_rate_flag NUMBER := 0;
647 l_acct_period_id NUMBER := 0;
648 l_department_id NUMBER := 0;
649 l_department_code VARCHAR2(10) ;
650 l_reason_id NUMBER := 0;
651 l_conversion_rate NUMBER := 0;
652 l_primary_qty NUMBER := 0;
653 l_res_seq_num_temp NUMBER := 0;
654 old_res_status NUMBER := 0;
655 l_start_date DATE;
656 l_completion_date DATE;
657 l_return_status VARCHAR2(80) ;
658 l_msg_count NUMBER := 0;
659 l_msg_data varchar2(80) ;
660 l_transaction_date DATE := p_transaction_date;
661 l_open BOOLEAN := false;
662 l_project_task_chargeable VARCHAR2(1):='N';
663
664 --Added for 9880442
665 l_attribute_category VARCHAR2(30) := p_attribute_category ;
666 l_attribute1 VARCHAR2(150) := p_attribute1 ;
667 l_attribute2 VARCHAR2(150) := p_attribute2 ;
668 l_attribute3 VARCHAR2(150) := p_attribute3 ;
669 l_attribute4 VARCHAR2(150) := p_attribute4 ;
670 l_attribute5 VARCHAR2(150) := p_attribute5 ;
671 l_attribute6 VARCHAR2(150) := p_attribute6 ;
672 l_attribute7 VARCHAR2(150) := p_attribute7 ;
673 l_attribute8 VARCHAR2(150) := p_attribute8 ;
674 l_attribute9 VARCHAR2(150) := p_attribute9 ;
675 l_attribute10 VARCHAR2(150) := p_attribute10 ;
676 l_attribute11 VARCHAR2(150) := p_attribute11 ;
677 l_attribute12 VARCHAR2(150) := p_attribute12 ;
678 l_attribute13 VARCHAR2(150) := p_attribute13 ;
679 l_attribute14 VARCHAR2(150) := p_attribute14 ;
680 l_attribute15 VARCHAR2(150) := p_attribute15 ;
681
682
683 CURSOR c_cur_wor(l_wip_entity_id1 NUMBER,l_organization_id1 NUMBER,
684 l_operation_seq_num1 NUMBER ) IS --for --rhshriva
685 select resource_seq_num
686 from wip_operation_resources
687 where wip_entity_id =l_wip_entity_id1 and
688 organization_id = l_organization_id1 and
689 operation_seq_num = l_operation_seq_num1 ;
690
691
692 BEGIN
693 -- Standard Start of API savepoint
694 l_stmt_num := 10;
695 SAVEPOINT get_insert_into_wcti_pvt;
696
697 l_stmt_num := 20;
698 -- Standard call to check for call compatibility.
699 IF NOT fnd_api.compatible_api_call(
700 l_api_version
701 ,p_api_version
702 ,l_api_name
703 ,g_pkg_name) THEN
704 RAISE fnd_api.g_exc_unexpected_error;
705 END IF;
706
707 l_stmt_num := 30;
708 -- Initialize message list if p_init_msg_list is set to TRUE.
709 IF fnd_api.to_boolean(p_init_msg_list) THEN
710 fnd_msg_pub.initialize;
711 END IF;
712
713 l_stmt_num := 40;
714 -- Initialize API return status to success
715 x_return_status := fnd_api.g_ret_sts_success;
716
717 l_stmt_num := 50;
718 -- API body
719 l_wip_entity_id := p_wip_entity_id;
720 l_operation_seq_num := p_operation_seq_num;
721 l_organization_id := p_organization_id;
722
723 l_stmt_num := 60;
724
725 -- Get the Group Id
726
727 select wip_transactions_s.nextval into l_groupid from dual;
728
729 l_stmt_num := 70;
730
731 l_stmt_num := 80;
732
733
734
735 l_stmt_num := 90;
736
737 -- Get Organization_Code
738
739 select organization_code into l_organization_code from mtl_parameters where organization_id = l_organization_id;
740
741 l_stmt_num := 100;
742
743 -- Get Wip_Enttity_Name and Primary Item Id
744
745 select wip_entity_name,entity_type,primary_item_id
746 into l_wip_entity_name, l_entity_type , l_primary_item_id
747 from wip_entities where organization_id = l_organization_id and wip_entity_id = l_wip_entity_id;
748
749 l_stmt_num := 110;
750
751 -- Get Project Id and Task Id
752
753 select project_id,task_id into l_project_id, l_task_id from wip_discrete_jobs
754 where organization_id = l_organization_id and wip_entity_id = l_wip_entity_id;
755
756 -- Get the chargeable flag .Changes for bug 9524124
757
758
759 IF (l_project_id IS NOT NULL AND l_task_id IS NOT NULL AND l_project_id <> 0 AND l_task_id <>0 )THEN
760
761 BEGIN
762
763 SELECT
764 chargeable_flag
765 INTO
766 l_project_task_chargeable
767 FROM pjm_tasks_v
768 WHERE
769 project_id=l_project_id
770 AND task_id=l_task_id;
771
772 IF l_project_task_chargeable <> 'Y' THEN
773
774 FND_MESSAGE.SET_NAME('EAM','EAM_RES_TXN_PJM_TASK_N_CHARGE');
775 FND_MSG_PUB.Add;
776 RAISE FND_API.G_EXC_ERROR;
777
778 END IF ;
779
780
781 EXCEPTION
782 WHEN NO_DATA_FOUND THEN
783 NULL;
784 WHEN OTHERS THEN
785 NULL;
786
787 END ; --END OF the begin block
788
789 END IF; --end of if
790
791 l_stmt_num := 120;
792
793
794 -- check transaction_date, if null, default to sysdate
795 if (l_transaction_date is null) then
796 l_transaction_date := sysdate;
797 end if;
798
799
800 -- Get Account Period Id corresponding to sysdate
801
802 INVTTMTX.tdatechk(org_id => l_organization_id,
803 transaction_date => sysdate,
804 period_id => l_acct_period_id,
805 open_past_period => l_open);
806
807 select min(acct_period_id) into l_acct_period_id from org_acct_periods
808 where trunc(l_transaction_date) >= trunc(period_start_date)
809 and trunc(l_transaction_date) <= trunc(schedule_close_date)
810 and organization_id = l_organization_id
811 and period_close_date is null;
812
813 l_stmt_num := 130;
814
815 -- Get Department Id and Department Code
816
817 select bd.department_id,bd.department_code
818 into l_department_id, l_department_code
819 from wip_operations wo, bom_departments bd
820 where wo.wip_entity_id = l_wip_entity_id and wo.organization_id = l_organization_id
821 and wo.operation_seq_num = l_operation_seq_num and wo.department_id = bd.department_id(+);
822
823 l_stmt_num := 140;
824
825 -- Get Reason Id
826
827 if (p_reason_name is not null) then
828 select reason_id into l_reason_id from mtl_transaction_reasons where reason_name = p_reason_name;
829 end if;
830
831 -- Get resource_id, resource_code, resource_type, uom, basis_type, activity_id, activity and standard_rate_flag
832
833 if (p_resource_code is not null) then
834 l_stmt_num := 150;
835 select br.resource_id,
836 br.resource_code,
837 br.resource_type,
838 br.unit_of_measure uom_code,
839 br.default_basis_type basis_type,
840 ca.activity_id,
841 ca.activity,
842 br.standard_rate_flag
843 into
844 l_resource_id,
845 l_resource_code,
846 l_resource_type,
847 l_uom,
848 l_basis_type,
849 l_activity_id,
850 l_activity_name,
851 l_standard_rate_flag
852 from
853 cst_activities ca,
854 bom_department_resources bdr,
855 bom_resources br
856 where br.organization_id = l_organization_id
857 and bdr.department_id = (select department_id
858 from wip_operations
859 where organization_id = l_organization_id
860 and wip_entity_id = l_wip_entity_id
861 and operation_seq_num = l_operation_seq_num)
862 and br.resource_id = bdr.resource_id
863 and br.default_activity_id = ca.activity_id (+)
864 and nvl(ca.disable_date(+),sysdate+1) > sysdate
865 and nvl(br.disable_date,sysdate+1) > sysdate
866 and (ca.organization_id is null or ca.organization_id = l_organization_id)
867 and resource_code = p_resource_code;
868
869 end if;
870
871 l_stmt_num := 160;
872
873 if p_uom is not null then
874 l_txn_uom := p_uom;
875 end if;
876
877 l_stmt_num := 170;
878
879 -- Get the conversion_rate corresponding to primary uom of resource and transaction uom
880
881 l_conversion_rate :=
882 inv_convert.inv_um_convert(
883 item_id => 0,
884 precision => 38,
885 from_quantity => 1,
886 from_unit => l_uom,
887 to_unit => l_txn_uom,
888 from_name => NULL,
889 to_name => NULL);
890
891 l_stmt_num := 180;
892
893 --Get the primary quantity based on the conversion rate
894
895 l_primary_qty := round((p_transaction_qty / l_conversion_rate),6) ;
896
897 l_stmt_num := 190;
898
899 -- If the resource_sequence number entry does not exist in WIP_OPERATION_RESOURCES, then place a new entry
900
901 open c_cur_wor(l_wip_entity_id1 =>l_wip_entity_id,
902 l_organization_id1=>l_organization_id,
903 l_operation_seq_num1=>l_operation_seq_num) ;
904
905 LOOP
906 fetch c_cur_wor into l_res_seq_num_temp;
907 EXIT WHEN c_cur_wor%NOTFOUND;
908
909 -- Check whether the entry for resource_sequnece_num exists in WIP_OPERATION_RESOURCES
910
911 if (l_res_seq_num_temp = p_resource_seq_num) then
912 old_res_status := 1;
913
914 BEGIN
915 select nvl(usage_rate_or_amount,0)
916 into l_usage_rate_or_amount
917 from wip_operation_resources
918 where wip_entity_id = l_wip_entity_id
919 and operation_seq_num = l_operation_seq_num
920 and resource_seq_num = l_res_seq_num_temp
921 and organization_id = l_organization_id;
922 EXCEPTION
923 WHEN NO_DATA_FOUND THEN
924 raise fnd_api.g_exc_unexpected_error;
925 END;
926
927 end if;
928 end loop;
929 close c_cur_wor ;
930
931 l_stmt_num := 200;
932
933 select scheduled_start_date, scheduled_completion_date
934 into l_start_date, l_completion_date
935 from wip_discrete_jobs where wip_entity_id = l_wip_entity_id;
936
937 l_stmt_num := 210;
938
939 if (old_res_status = 0) then
940
941 -- If entry does not exists in WIP_OPERATION_RESOURCES then place a new entry into WIP_OPERATION_RESOURCES
942
943 insert into wip_operation_resources(
944 WIP_ENTITY_ID
945 ,OPERATION_SEQ_NUM
946 ,RESOURCE_SEQ_NUM
947 ,ORGANIZATION_ID
948 ,REPETITIVE_SCHEDULE_ID
949 ,LAST_UPDATE_DATE
950 ,LAST_UPDATED_BY
951 ,CREATION_DATE
952 ,CREATED_BY
953 ,LAST_UPDATE_LOGIN
954 ,REQUEST_ID
955 ,PROGRAM_APPLICATION_ID
956 ,PROGRAM_ID
957 ,PROGRAM_UPDATE_DATE
958 ,RESOURCE_ID
959 ,UOM_CODE
960 ,BASIS_TYPE
961 ,USAGE_RATE_OR_AMOUNT
962 ,ACTIVITY_ID
963 ,SCHEDULED_FLAG
964 ,ASSIGNED_UNITS
965 ,AUTOCHARGE_TYPE
966 ,STANDARD_RATE_FLAG
967 ,APPLIED_RESOURCE_UNITS
968 ,APPLIED_RESOURCE_VALUE
969 ,START_DATE
970 ,COMPLETION_DATE
971 ,ATTRIBUTE_CATEGORY
972 ,ATTRIBUTE1
973 ,ATTRIBUTE2
974 ,ATTRIBUTE3
975 ,ATTRIBUTE4
976 ,ATTRIBUTE5
977 ,ATTRIBUTE6
978 ,ATTRIBUTE7
979 ,ATTRIBUTE8
980 ,ATTRIBUTE9
981 ,ATTRIBUTE10
982 ,ATTRIBUTE11
983 ,ATTRIBUTE12
984 ,ATTRIBUTE13
985 ,ATTRIBUTE14
986 ,ATTRIBUTE15
987 ,RELIEVED_RES_COMPLETION_UNITS
988 ,RELIEVED_RES_SCRAP_UNITS
989 ,RELIEVED_RES_COMPLETION_VALUE
990 ,RELIEVED_RES_SCRAP_VALUE
991 ,RELIEVED_VARIANCE_VALUE
992 ,TEMP_RELIEVED_VALUE
993 ,RELIEVED_RES_FINAL_COMP_UNITS
994 ,DEPARTMENT_ID
995 ,PHANTOM_FLAG
996 ,PHANTOM_OP_SEQ_NUM
997 ,PHANTOM_ITEM_ID
998 ,SCHEDULE_SEQ_NUM
999 ,SUBSTITUTE_GROUP_NUM
1000 ,REPLACEMENT_GROUP_NUM
1001 ,PRINCIPLE_FLAG
1002 ,SETUP_ID
1003 ,PARENT_RESOURCE_SEQ )
1004
1005 values(
1006 l_wip_entity_id
1007 ,l_operation_seq_num
1008 ,p_resource_seq_num
1009 ,l_organization_id
1010 ,null
1011 ,sysdate
1012 ,FND_GLOBAL.user_id
1013 ,sysdate
1014 ,FND_GLOBAL.user_id
1015 ,null
1016 ,null
1017 ,null
1018 ,null
1019 ,null
1020 ,l_resource_id
1021 ,l_uom
1022 ,l_basis_type
1023 ,0 -- usage rate or amount
1024 ,null -- activity id
1025 ,2 -- scheduled flag
1026 ,1 -- assigned units
1027 ,2 --autocharge type
1028 ,2 -- standard rate flag
1029 ,0 -- applied resource units
1030 ,0 -- applied resource value
1031 ,l_start_date
1032 ,l_completion_date
1033 ,null
1034 ,null
1035 ,null
1036 ,null
1037 ,null
1038 ,null
1039 ,null
1040 ,null
1041 ,null
1042 ,null
1043 ,null
1044 ,null
1045 ,null
1046 ,null
1047 ,null
1048 ,null
1049 ,null
1050 ,null
1051 ,null
1052 ,null
1053 ,null
1054 ,null
1055 ,null
1056 ,l_department_id --populate operations dept. id
1057 ,null
1058 ,null
1059 ,null
1060 ,null
1061 ,null
1062 ,null
1063 ,null
1064 ,null
1065 ,null
1066 );
1067 end if;
1068
1069 l_stmt_num := 220;
1070
1071
1072
1073 --Insert into WIP_COST_TXN_INTERFACE
1074
1075 insert into wip_cost_txn_interface(
1076 transaction_id,
1077 last_update_date,
1078 last_updated_by,
1079 last_updated_by_name,
1080 creation_date,
1081 created_by,
1082 created_by_name,
1083 last_update_login,
1084 request_id,
1085 program_application_id,
1086 program_id,
1087 program_update_date,
1088 group_id,
1089 source_code,
1090 source_line_id,
1091 process_phase,
1092 process_status,
1093 transaction_type,
1094 organization_id,
1095 organization_code,
1096 wip_entity_id,
1097 wip_entity_name,
1098 entity_type,
1099 line_id,
1100 line_code,
1101 primary_item_id,
1102 repetitive_schedule_id,
1103 transaction_date,
1104 acct_period_id,
1105 operation_seq_num,
1106 resource_seq_num,
1107 department_id,
1108 department_code,
1109 employee_id,
1110 employee_num,
1111 resource_id,
1112 resource_code,
1113 resource_type,
1114 usage_rate_or_amount,
1115 basis_type,
1116 autocharge_type,
1117 standard_rate_flag,
1118 transaction_quantity,
1119 transaction_uom,
1120 primary_quantity,
1121 primary_uom,
1122 primary_uom_class,
1123 actual_resource_rate,
1124 currency_code,
1125 currency_conversion_date,
1126 currency_conversion_type,
1127 currency_conversion_rate,
1128 currency_actual_resource_rate,
1129 activity_id,
1130 activity_name,
1131 reason_id,
1132 reason_name,
1133 reference,
1134 move_transaction_id,
1135 rcv_transaction_id,
1136 po_header_id,
1137 po_line_id,
1138 receiving_account_id,
1139 project_id,
1140 task_id,
1141 attribute_category,
1142 attribute1,
1143 attribute2,
1144 attribute3,
1145 attribute4,
1146 attribute5,
1147 attribute6,
1148 attribute7,
1149 attribute8,
1150 attribute9,
1151 attribute10,
1152 attribute11,
1153 attribute12,
1154 attribute13,
1155 attribute14,
1156 attribute15,
1157 completion_transaction_id,
1158 phantom_flag,
1159 xml_document_id,
1160 charge_department_id,
1161 instance_id
1162 ) values (
1163 NULL, -- transaction_id
1164 SYSDATE, -- last_update_date
1165 FND_GLOBAL.user_id, -- last_updated_by
1166 FND_GLOBAL.user_name, -- last_updated_by_name
1167 SYSDATE, -- creation_date
1168 FND_GLOBAL.user_id, -- created_by
1169 FND_GLOBAL.user_name, -- created_by_name
1170 NULL, -- last_update_login
1171 NULL, -- request_id
1172 NULL, -- program_application_id
1173 NULL, -- program_id
1174 NULL, -- program_update_date
1175 NULL, -- groupid
1176 null, --source_code
1177 NULL, -- source_line_id
1178 2, -- process_phase
1179 1, -- process_status
1180 1, -- transaction_type
1181 l_organization_id,
1182 l_organization_code,
1183 l_wip_entity_id,
1184 l_wip_entity_name,
1185 l_entity_type,
1186 null,
1187 null,
1188 l_primary_item_id,
1189 null, --x_first_schedule_id
1190 l_transaction_date,
1191 l_acct_period_id,
1192 l_operation_seq_num,
1193 p_resource_seq_num,
1194 l_department_id,
1195 l_department_code,
1196 decode(p_employee_id,0,null,p_employee_id),
1197 p_employee_number, -- employee number
1198 l_resource_id,
1199 l_resource_code,
1200 l_resource_type,
1201 decode(l_usage_rate_or_amount,0,null,l_usage_rate_or_amount), --usage_rate_or_amount
1202 l_basis_type,
1203 2, -- autocharge_type
1204 l_standard_rate_flag,
1205 p_transaction_qty,
1206 l_txn_uom,
1207 l_primary_qty,
1208 l_uom,
1209 NULL, -- primary_uom_class
1210 p_actual_resource_rate,
1211 null, -- currency_code
1212 null, -- currency_conversion_date
1213 null, -- currency_conversion_type
1214 null, -- currency_conversion_rate
1215 null, -- currency_actual_resource_rate
1216 l_activity_id,
1217 l_activity_name,
1218 decode(l_reason_id,0,null,l_reason_id),
1219 p_reason_name,
1220 p_reference,
1221 null, -- move_transaction_id
1222 null, -- rcv_transaction_id
1223 null, -- po_header_id
1224 null, -- po_line_id
1225 null, -- receiving_account_id
1226 l_project_id,
1227 l_task_id,
1228 l_attribute_category,
1229 l_attribute1,
1230 l_attribute2,
1231 l_attribute3,
1232 l_attribute4,
1233 l_attribute5,
1234 l_attribute6,
1235 l_attribute7,
1236 l_attribute8,
1237 l_attribute9,
1238 l_attribute10,
1239 l_attribute11,
1240 l_attribute12,
1241 l_attribute13,
1242 l_attribute14,
1243 l_attribute15,
1244 null,
1245 null,
1246 null,
1247 decode(p_charge_dept_id,0,null,p_charge_dept_id),
1248 decode(p_instance_id,0,null,p_instance_id)
1249 );
1250
1251 l_stmt_num := 230;
1252
1253 if (p_instance_id is not null and p_instance_id <> 0) then
1254
1255 WIP_EAM_RESOURCE_TRANSACTION.insert_into_wori (
1256 p_api_version => 1.0
1257 ,p_init_msg_list => fnd_api.g_false
1258 ,p_commit => fnd_api.g_false
1259 ,p_validation_level => fnd_api.g_valid_level_full
1260 ,p_wip_entity_id => l_wip_entity_id
1261 ,p_operation_seq_num => l_operation_seq_num
1262 ,p_organization_id => l_organization_id
1263 ,p_resource_seq_num => p_resource_seq_num
1264 ,p_instance_id => p_instance_id
1265 ,p_serial_number => p_serial_number
1266 ,p_start_date => l_start_date
1267 ,p_completion_date => l_completion_date
1268 ,x_return_status => l_return_status
1269 ,x_msg_count => l_msg_count
1270 ,x_msg_data => l_msg_data );
1271
1272 end if;
1273
1274
1275
1276
1277 -- End of API body.
1278 -- Standard check of p_commit.
1279 IF fnd_api.to_boolean(p_commit) THEN
1280 COMMIT WORK;
1281 END IF;
1282
1283 l_stmt_num := 999;
1284 -- Standard call to get message count and if count is 1, get message info.
1285 fnd_msg_pub.count_and_get(
1286 p_count => x_msg_count
1287 ,p_data => x_msg_data);
1288 EXCEPTION
1289 WHEN fnd_api.g_exc_error THEN
1290 ROLLBACK TO get_insert_into_wcti_pvt;
1291 x_return_status := fnd_api.g_ret_sts_error;
1292 fnd_msg_pub.count_and_get(
1293 -- p_encoded => FND_API.g_false
1294 p_count => x_msg_count
1295 ,p_data => x_msg_data);
1296 WHEN fnd_api.g_exc_unexpected_error THEN
1297 ROLLBACK TO get_insert_into_wcti_pvt;
1298 x_return_status := fnd_api.g_ret_sts_unexp_error;
1299
1300 fnd_msg_pub.count_and_get(
1301 p_count => x_msg_count
1302 ,p_data => x_msg_data);
1303 WHEN OTHERS THEN
1304 ROLLBACK TO get_insert_into_wcti_pvt;
1305 x_return_status := fnd_api.g_ret_sts_unexp_error;
1306 IF fnd_msg_pub.check_msg_level(
1307 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1308 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1309 END IF;
1310
1311 fnd_msg_pub.count_and_get(
1312 p_count => x_msg_count
1313 ,p_data => x_msg_data);
1314 END insert_into_wcti;
1315
1316
1317
1318 -- Procedure for inserting the instances into WIP_OP_RESOURCE_INSTANCES
1319
1320 PROCEDURE insert_into_wori(
1321 p_api_version IN NUMBER
1322 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1323 ,p_commit IN VARCHAR2 := fnd_api.g_false
1324 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1325 ,p_wip_entity_id IN NUMBER
1326 ,p_operation_seq_num IN NUMBER
1327 ,p_organization_id IN NUMBER
1328 ,p_resource_seq_num IN NUMBER
1329 ,p_instance_id IN NUMBER
1330 ,p_serial_number IN VARCHAR2
1331 ,p_start_date IN DATE
1332 ,p_completion_date IN DATE
1333 ,x_return_status OUT NOCOPY VARCHAR2
1334 ,x_msg_count OUT NOCOPY NUMBER
1335 ,x_msg_data OUT NOCOPY VARCHAR2)
1336
1337 IS
1338 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wori';
1339 l_api_version CONSTANT NUMBER := 1.0;
1340 l_instance_id NUMBER := 0;
1341 row_exists NUMBER := 0;
1342 l_stmt_num NUMBER := 0;
1343
1344 CURSOR c_cur_wori IS --rhshriva
1345 select instance_id
1346 from wip_op_resource_instances
1347 where wip_entity_id = p_wip_entity_id and
1348 organization_id = p_organization_id and
1349 operation_seq_num = p_operation_seq_num and
1350 resource_seq_num = p_resource_seq_num and
1351 instance_id = p_instance_id ;
1352
1353
1354 BEGIN
1355 -- Standard Start of API savepoint
1356 l_stmt_num := 10;
1357 SAVEPOINT get_insert_into_wori_pvt;
1358
1359 l_stmt_num := 20;
1360 -- Standard call to check for call compatibility.
1361 IF NOT fnd_api.compatible_api_call(
1362 l_api_version
1363 ,p_api_version
1364 ,l_api_name
1365 ,g_pkg_name) THEN
1366 RAISE fnd_api.g_exc_unexpected_error;
1367 END IF;
1368
1369 l_stmt_num := 30;
1370 -- Initialize message list if p_init_msg_list is set to TRUE.
1371 IF fnd_api.to_boolean(p_init_msg_list) THEN
1372 fnd_msg_pub.initialize;
1373 END IF;
1374
1375 l_stmt_num := 40;
1376 -- Initialize API return status to success
1377 x_return_status := fnd_api.g_ret_sts_success;
1378
1379 l_stmt_num := 50;
1380 -- API body
1381
1382 -- Cursor to check whether the entry for the particular instance
1383 -- already exists in the table WIP_OP_RESOURCE_INSTANCES
1384
1385 open c_cur_wori;
1386
1387 LOOP
1388 fetch c_cur_wori into l_instance_id;
1389 EXIT WHEN c_cur_wori%NOTFOUND;
1390
1391 l_stmt_num := 60;
1392
1393 -- Check whether the entry for instance_id exists in WIP_OP_RESOURCE_INSTANCES
1394
1395 if (l_instance_id = p_instance_id) then
1396 row_exists := 1;
1397 end if;
1398 end loop;
1399 close c_cur_wori ;
1400
1401
1402 l_stmt_num := 70;
1403
1404 -- If the entry does not exist in the WIP_OP_RESOURCE_INSTANCES
1405 -- Insert into the table
1406
1407
1408
1409 if (row_exists = 0) then
1410
1411
1412 insert into wip_op_resource_instances (
1413 wip_entity_id,
1414 operation_seq_num,
1415 resource_seq_num,
1416 organization_id,
1417 last_update_date,
1418 last_updated_by,
1419 creation_date,
1420 created_by,
1421 last_update_login,
1422 instance_id,
1423 serial_number,
1424 start_date,
1425 completion_date,
1426 batch_id )
1427
1428 values
1429
1430 (p_wip_entity_id,
1431 p_operation_seq_num,
1432 p_resource_seq_num,
1433 p_organization_id,
1434 sysdate,
1435 FND_GLOBAL.user_id,
1436 sysdate,
1437 FND_GLOBAL.user_id,
1438 null,
1439 p_instance_id,
1440 p_serial_number,
1441 p_start_date,
1442 p_completion_date,
1443 null);
1444
1445
1446 l_stmt_num := 80;
1447
1448 end if;
1449
1450 l_stmt_num := 800;
1451
1452 -- End of API body.
1453 -- Standard check of p_commit.
1454 IF fnd_api.to_boolean(p_commit) THEN
1455 COMMIT WORK;
1456 END IF;
1457
1458 l_stmt_num := 999;
1459 -- Standard call to get message count and if count is 1, get message info.
1460 fnd_msg_pub.count_and_get(
1461 p_count => x_msg_count
1462 ,p_data => x_msg_data);
1463 EXCEPTION
1464 WHEN fnd_api.g_exc_error THEN
1465 ROLLBACK TO get_insert_into_wori_pvt;
1466 x_return_status := fnd_api.g_ret_sts_error;
1467 fnd_msg_pub.count_and_get(
1468 -- p_encoded => FND_API.g_false
1469 p_count => x_msg_count
1470 ,p_data => x_msg_data);
1471 WHEN fnd_api.g_exc_unexpected_error THEN
1472 ROLLBACK TO get_insert_into_wori_pvt;
1473 x_return_status := fnd_api.g_ret_sts_unexp_error;
1474
1475 fnd_msg_pub.count_and_get(
1476 p_count => x_msg_count
1477 ,p_data => x_msg_data);
1478 WHEN OTHERS THEN
1479 ROLLBACK TO get_insert_into_wori_pvt;
1480 x_return_status := fnd_api.g_ret_sts_unexp_error;
1481 IF fnd_msg_pub.check_msg_level(
1482 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1483 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1484 END IF;
1485
1486 fnd_msg_pub.count_and_get(
1487 p_count => x_msg_count
1488 ,p_data => x_msg_data);
1489 END insert_into_wori;
1490
1491 -- API called by Costing to insert into WED and WRO during receiving
1492 -- Anju Gupta: Modifed for PO Service Line Types Enhancement
1493
1494 procedure WIP_EAMRCVDIRECTITEM_HOOK
1495 ( p_api_version IN NUMBER
1496 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1497 ,p_commit IN VARCHAR2 := fnd_api.g_false
1498 ,p_rcv_txn_id IN NUMBER
1499 ,p_primary_qty IN NUMBER
1500 ,p_primary_uom IN VARCHAR2
1501 ,p_unit_price IN NUMBER
1502 ,x_return_status OUT NOCOPY VARCHAR2
1503 ,x_msg_count OUT NOCOPY NUMBER
1504 ,x_msg_data OUT NOCOPY VARCHAR2 )
1505 IS
1506
1507 l_api_name CONSTANT VARCHAR2(30) := 'create_requisition';
1508 l_api_version CONSTANT NUMBER := 1.0;
1509 l_organization_id NUMBER;
1510 l_wip_entity_id NUMBER;
1511 l_operation_seq_num NUMBER;
1512 l_resource_seq_num NUMBER;
1513 l_department_id NUMBER;
1514 l_primary_quantity NUMBER;
1515 l_primary_uom VARCHAR2(3);
1516 l_po_header_id NUMBER;
1517 l_po_line_id NUMBER;
1518 l_vendor_id NUMBER;
1519 l_vendor_site_id NUMBER;
1520 l_item_id NUMBER;
1521 l_category_id NUMBER;
1522 l_item_description VARCHAR2(240);
1523 l_unit_price NUMBER;
1524 l_quantity NUMBER;
1525 l_need_by_date DATE;
1526 l_stmt_num NUMBER;
1527 l_status NUMBER;
1528 l_required_quantity NUMBER;
1529 l_quantity_received NUMBER;
1530 l_direct_item_id NUMBER;
1531 l_uom VARCHAR2(3);
1532 l_conversion_rate NUMBER := 0;
1533 l_order_type_lookup_code po_lines_all.order_type_lookup_code%TYPE;
1534
1535
1536 BEGIN
1537
1538 -- Standard Start of API savepoint
1539 l_stmt_num := 10;
1540 SAVEPOINT wip_eamrcvdirectitem_pvt;
1541
1542 l_stmt_num := 20;
1543 -- Standard call to check for call compatibility.
1544 IF NOT fnd_api.compatible_api_call(
1545 l_api_version
1546 ,p_api_version
1547 ,l_api_name
1548 ,g_pkg_name) THEN
1549 RAISE fnd_api.g_exc_unexpected_error;
1550 END IF;
1551
1552 l_stmt_num := 30;
1553 -- Initialize message list if p_init_msg_list is set to TRUE.
1554 IF fnd_api.to_boolean(p_init_msg_list) THEN
1555 fnd_msg_pub.initialize;
1556 END IF;
1557
1558 l_stmt_num := 40;
1559 -- Initialize API return status to success
1560 x_return_status := fnd_api.g_ret_sts_success;
1561
1562 l_stmt_num := 50;
1563
1564 -- API body
1565
1566 -- Get values from WCTI
1567
1568 if p_rcv_txn_id is not null then
1569
1570 begin
1571
1572 select rct.organization_id,
1573 rct.wip_entity_id,
1574 rct.wip_operation_seq_num,
1575 rct.wip_resource_seq_num,
1576 wo.department_id,
1577 rct.po_header_id,
1578 rct.po_line_id,
1579 rct.vendor_id,
1580 rct.vendor_site_id,
1581 pla.item_id,
1582 pla.category_id,
1583 pla.item_description,
1584 nvl(plla.need_by_date,wo.first_unit_start_date),
1585 pla.order_type_lookup_code
1586
1587
1588
1589 into l_organization_id,
1590 l_wip_entity_id,
1591 l_operation_seq_num,
1592 l_resource_seq_num,
1593 l_department_id,
1594 l_po_header_id,
1595 l_po_line_id,
1596 l_vendor_id,
1597 l_vendor_site_id,
1598 l_item_id,
1599 l_category_id,
1600 l_item_description,
1601 l_need_by_date,
1602 l_order_type_lookup_code
1603
1604 from rcv_transactions rct, po_lines_all pla, po_line_types plt,
1605 po_line_locations_all plla, wip_operations wo
1606 where pla.po_header_id (+) = rct.po_header_id
1607 and pla.po_line_id (+) = rct.po_line_id
1608 and rct.po_line_location_id = plla.line_location_id (+)
1609 and pla.line_type_id = plt.line_type_id (+)
1610 and plt.outside_operation_flag = 'N'
1611 and rct.wip_entity_id = wo.wip_entity_id (+)
1612 and rct.organization_id = wo.organization_id (+)
1613 and rct.wip_operation_seq_num = wo.operation_seq_num (+)
1614 and rct.transaction_id = p_rcv_txn_id;
1615
1616 l_primary_quantity := p_primary_qty;
1617 l_primary_uom := p_primary_uom;
1618 l_unit_price := p_unit_price;
1619
1620 if ((l_wip_entity_id is not null) and (l_operation_seq_num is not null) and (l_organization_id is not null)) then
1621 if (l_item_id is not null) then
1622
1623 begin
1624
1625 select sum( nvl(pd.quantity_delivered, 0) )
1626 into l_quantity_received
1627 from
1628 po_lines_all pol,
1629 po_distributions_all pd,
1630 po_line_types plt
1631 where
1632 pol.po_line_id = pd.po_line_id
1633 AND pol.line_type_id = plt.line_type_id
1634 AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
1635 AND pd.destination_type_code = 'SHOP FLOOR'
1636 AND( pd.PO_RELEASE_ID IS NOT NULL OR pd.DISTRIBUTION_TYPE = 'STANDARD')
1637 AND upper(nvl(pol.cancel_flag, 'N')) <> 'Y'
1638 AND pd.wip_entity_id = l_wip_entity_id
1639 AND pd.wip_operation_seq_num = l_operation_seq_num
1640 AND pd.destination_organization_id = l_organization_id
1641 AND pol.item_id = l_item_id;
1642
1643
1644 exception
1645
1646 WHEN NO_DATA_FOUND then
1647
1648 RAISE fnd_api.g_exc_unexpected_error;
1649
1650 end;
1651
1652
1653 begin
1654
1655 select 1, nvl(required_quantity,0)
1656 into l_status, l_required_quantity
1657 from wip_requirement_operations
1658 where wip_entity_id = l_wip_entity_id
1659 and organization_id = l_organization_id
1660 and operation_seq_num = l_operation_seq_num
1661 and inventory_item_id = l_item_id;
1662
1663 if (l_quantity_received > l_required_quantity ) then
1664
1665 update wip_requirement_operations
1666 set required_quantity = nvl(l_quantity_received,0)
1667 where wip_entity_id = l_wip_entity_id
1668 and organization_id = l_organization_id
1669 and operation_seq_num = l_operation_seq_num
1670 and inventory_item_id = l_item_id;
1671
1672 end if;
1673
1674 if (l_order_type_lookup_code = 'AMOUNT') THEN
1675
1676 update wip_requirement_operations
1677 set required_quantity = nvl(l_quantity_received,0)
1678 where wip_entity_id = l_wip_entity_id
1679 and organization_id = l_organization_id
1680 and operation_seq_num = l_operation_seq_num
1681 and inventory_item_id = l_item_id;
1682
1683 end if;
1684
1685 exception
1686
1687 WHEN NO_DATA_FOUND then
1688
1689 insert into WIP_REQUIREMENT_OPERATIONS
1690 (INVENTORY_ITEM_ID,
1691 ORGANIZATION_ID,
1692 WIP_ENTITY_ID,
1693 OPERATION_SEQ_NUM,
1694 LAST_UPDATE_DATE,
1695 LAST_UPDATED_BY,
1696 CREATION_DATE,
1697 CREATED_BY,
1698 DEPARTMENT_ID,
1699 WIP_SUPPLY_TYPE,
1700 DATE_REQUIRED,
1701 REQUIRED_QUANTITY,
1702 QUANTITY_ISSUED,
1703 QUANTITY_PER_ASSEMBLY,
1704 MRP_NET_FLAG,
1705 AUTO_REQUEST_MATERIAL,
1706 VENDOR_ID,
1707 UNIT_PRICE)
1708 values
1709 ( l_item_id,
1710 l_organization_id,
1711 l_wip_entity_id,
1712 nvl(l_operation_seq_num,1),
1713 sysdate,
1714 FND_GLOBAL.USER_ID,
1715 sysdate,
1716 FND_GLOBAL.USER_ID,
1717 l_department_id,
1718 1,
1719 l_need_by_date,
1720 nvl(l_primary_quantity,0),
1721 0,
1722 nvl(l_primary_quantity,0),
1723 1,
1724 'Y',
1725 l_vendor_id,
1726 l_unit_price
1727 );
1728
1729 END ;
1730
1731
1732 else
1733
1734 if (l_resource_seq_num is null) then
1735
1736 begin
1737
1738 select 1, max (direct_item_sequence_id), sum(nvl(required_quantity,0))
1739 into l_status , l_direct_item_id, l_required_quantity
1740 from wip_eam_direct_items
1741 where wip_entity_id = l_wip_entity_id
1742 and organization_id = l_organization_id
1743 and operation_seq_num = l_operation_seq_num
1744 and description = l_item_description;
1745
1746 select uom
1747 into l_uom
1748 from wip_eam_direct_items
1749 where wip_entity_id = l_wip_entity_id
1750 and organization_id = l_organization_id
1751 and operation_seq_num = l_operation_seq_num
1752 and description = l_item_description
1753 and direct_item_sequence_id = l_direct_item_id;
1754
1755
1756 begin
1757
1758 /* Bug # 4890934 : Replace view by base tables */
1759
1760 select sum(round(inv_convert.inv_um_convert(0,38,quantity_received,
1761 uom_code,l_uom, NULL,NULL),3))
1762 into l_quantity_received
1763 from (SELECT uom.uom_code, sum(pda.quantity_delivered) quantity_received
1764 FROM po_line_types plt, mtl_units_of_measure uom, po_lines_all pla, po_distributions_all pda
1765 WHERE pda.destination_type_code = 'SHOP FLOOR' AND (pda.PO_RELEASE_ID IS NOT NULL OR pda.DISTRIBUTION_TYPE = 'STANDARD')
1766 AND pla.line_type_id = plt.line_type_id
1767 AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N' AND pla.po_line_id = pda.po_line_id
1768 AND pla.unit_meas_lookup_code = uom.unit_of_measure (+)
1769 AND upper(nvl(pla.cancel_flag, 'N')) <> 'Y' AND pla.item_description = l_item_description
1770 AND pda.wip_entity_id = l_wip_entity_id AND pda.wip_operation_seq_num = l_operation_seq_num
1771 AND pda.destination_organization_id = l_organization_id
1772 GROUP BY uom.uom_code);
1773
1774 exception
1775
1776 WHEN NO_DATA_FOUND then
1777
1778 RAISE fnd_api.g_exc_unexpected_error;
1779
1780 end;
1781
1782
1783 if (l_quantity_received > l_required_quantity ) then
1784
1785 update wip_eam_direct_items
1786 set required_quantity = l_quantity_received
1787 where wip_entity_id = l_wip_entity_id
1788 and organization_id = l_organization_id
1789 and operation_seq_num = l_operation_seq_num
1790 and direct_item_sequence_id = l_direct_item_id;
1791
1792 end if;
1793
1794 if ( l_order_type_lookup_code = 'AMOUNT') then
1795 update wip_eam_direct_items
1796 set required_quantity = nvl(l_quantity_received,0),
1797 amount = amount + p_unit_price
1798 where wip_entity_id = l_wip_entity_id
1799 and organization_id = l_organization_id
1800 and operation_seq_num = l_operation_seq_num
1801 and direct_item_sequence_id = l_direct_item_id;
1802
1803 end if;
1804
1805 exception
1806
1807 WHEN NO_DATA_FOUND then
1808
1809
1810
1811 insert into WIP_EAM_DIRECT_ITEMS
1812 (DESCRIPTION,
1813 PURCHASING_CATEGORY_ID,
1814 DIRECT_ITEM_SEQUENCE_ID,
1815 OPERATION_SEQ_NUM,
1816 DEPARTMENT_ID,
1817 WIP_ENTITY_ID,
1818 ORGANIZATION_ID,
1819 SUGGESTED_VENDOR_ID,
1820 SUGGESTED_VENDOR_SITE_ID,
1821 SUGGESTED_VENDOR_CONTACT_ID,
1822 UNIT_PRICE,
1823 AUTO_REQUEST_MATERIAL,
1824 REQUIRED_QUANTITY,
1825 UOM,
1826 NEED_BY_DATE,
1827 CREATED_BY,
1828 CREATION_DATE,
1829 LAST_UPDATE_DATE,
1830 LAST_UPDATED_BY,
1831 order_type_lookup_code,
1832 amount
1833 )
1834 values
1835 ( l_item_description,
1836 l_category_id,
1837 wip_eam_di_seq_id_s.nextval,
1838 nvl(l_operation_seq_num,1),
1839 l_department_id,
1840 l_wip_entity_id,
1841 l_organization_id,
1842 l_vendor_id,
1843 l_vendor_site_id,
1844 null,
1845 decode(l_order_type_lookup_code, 'AMOUNT', 0, l_unit_price),
1846 'Y',
1847 l_primary_quantity,
1848 l_primary_uom,
1849 l_need_by_date,
1850 FND_GLOBAL.USER_ID,
1851 sysdate,
1852 sysdate,
1853 FND_GLOBAL.USER_ID,
1854 l_order_type_lookup_code,
1855 l_unit_price );
1856
1857 end; -- end of insertion into WIP_EAM_DIRECT_ITEMS table
1858
1859 end if; -- end of l_resource_seq_num
1860
1861 end if; -- end of l_item_id, l_operation_seq_num, l_organization_id not null
1862
1863 end if; -- end of l_wip_entity_id,
1864
1865 exception
1866
1867 when NO_DATA_FOUND then
1868
1869 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'OSP item received');
1870
1871 end;
1872
1873 end if; -- end of p_rcv_txn_id not null
1874
1875
1876 -- End of API body.
1877 -- Standard check of p_commit.
1878 IF fnd_api.to_boolean(p_commit) THEN
1879 COMMIT WORK;
1880 END IF;
1881
1882 l_stmt_num := 999;
1883 -- Standard call to get message count and if count is 1, get message info.
1884 fnd_msg_pub.count_and_get(
1885 p_encoded => fnd_api.g_false
1886 ,p_count => x_msg_count
1887 ,p_data => x_msg_data);
1888
1889 EXCEPTION
1890 WHEN fnd_api.g_exc_error THEN
1891 ROLLBACK TO wip_eamrcvdirectitem_pvt;
1892 x_return_status := fnd_api.g_ret_sts_error;
1893 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1894 fnd_msg_pub.count_and_get(
1895 p_encoded => fnd_api.g_false
1896 ,p_count => x_msg_count
1897 ,p_data => x_msg_data);
1898 WHEN fnd_api.g_exc_unexpected_error THEN
1899 ROLLBACK TO wip_eamrcvdirectitem_pvt;
1900 x_return_status := fnd_api.g_ret_sts_unexp_error;
1901 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1902 fnd_msg_pub.count_and_get(
1903 p_encoded => fnd_api.g_false
1904 ,p_count => x_msg_count
1905 ,p_data => x_msg_data);
1906 WHEN OTHERS THEN
1907 ROLLBACK TO wip_eamrcvdirectitem_pvt;
1908 x_return_status := fnd_api.g_ret_sts_unexp_error;
1909 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
1910 IF fnd_msg_pub.check_msg_level(
1911 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1912 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1913 END IF;
1914
1915 fnd_msg_pub.count_and_get(
1916 p_encoded => fnd_api.g_false
1917 ,p_count => x_msg_count
1918 ,p_data => x_msg_data);
1919
1920 END WIP_EAMRCVDIRECTITEM_HOOK;
1921
1922
1923 END WIP_EAM_RESOURCE_TRANSACTION;