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