[Home] [Help]
PACKAGE BODY: APPS.EAM_OTL_TIMECARD_PUB
Source
1 PACKAGE BODY EAM_OTL_TIMECARD_PUB as
2 /* $Header: EAMOTLTB.pls 120.11 2010/08/12 00:05:38 mashah ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):= 'OTL_EAM_TIMECARD_PUB';
5 g_msg VARCHAR2(2000):= null;
6 g_status varchar2(30) :='SUCCESS'; -- if we find any problem we will set this to 'ERRORS'
7 g_exception_description VARCHAR2(2000) :='Resource Transaction Failed'; -- and we will set the exception desc
8 g_debug_sqlerrm VARCHAR2(250);
9
10
11 -- procedure to get attributes for a given building block
12 -- for EAM we need to get all the attributes that are necessary for Resource Transaction
13 -- 1- Work Order;
14 -- 2- Operation
15 -- 3- Resource Code
16 -- 4- Charge Department
17 -- 5- Asset Group Id
18 -- 6- Owning Department Id
19 -- 7- Asset Number
20
21
22 PROCEDURE get_attribute_id (p_att_table IN HXC_USER_TYPE_DEFINITION_GRP.t_time_attribute,
23 p_bb_id IN number,
24 p_last_att_index IN OUT NOCOPY BINARY_INTEGER,
25 x_workorder OUT NOCOPY NUMBER,
26 x_operation OUT NOCOPY NUMBER,
27 x_resource OUT NOCOPY NUMBER,
28 x_charge_department OUT NOCOPY NUMBER,
29 x_asset_group_id OUT NOCOPY NUMBER,
30 x_owning_department OUT NOCOPY NUMBER,
31 x_asset_number OUT NOCOPY VARCHAR2)
32 IS
33
34 bld_block_mismatch EXCEPTION;
35 no_attributes_found EXCEPTION;
36
37 no_workorder_found EXCEPTION;
38 no_operation_found EXCEPTION;
39 no_resource_found EXCEPTION;
40
41 l_att_index BINARY_INTEGER;
42 l_bld_blk_id hxc_time_building_blocks.time_building_block_id%TYPE;
43 l_attribute_id number;
44
45 -- Strings
46 l_work_order VARCHAR2(30) := 'EAMWORKORDER';
47 l_operation VARCHAR2(30) := 'EAMOPERATION';
48 l_resource_code VARCHAR2(30) := 'EAMRESOURCE';
49 l_charge_department VARCHAR2(30) := 'EAMCHARGEDEPT';
50 l_asset_group VARCHAR2(30) := 'EAMASSETGROUP';
51 l_owning_department VARCHAR2(30) := 'EAMDEPARTMENTID';
52 l_asset_number VARCHAR2(30) := 'EAMASSETNUMBER';
53
54 -- Ids
55 l_work_order_id NUMBER;
56 l_operation_id NUMBER;
57 l_resource_id NUMBER;
58 l_charge_department_id NUMBER;
59 l_asset_group_id NUMBER;
60 l_owning_department_id NUMBER;
61 l_asset_number_id VARCHAR2(30);
62
63 -- Boolean
64 l_found_workorder boolean := FALSE;
65 l_found_operation boolean := FALSE;
66 l_found_resource boolean := FALSE;
67 l_found_charge_department boolean := FALSE;
68 l_found_asset_group_id boolean := FALSE;
69 l_found_owning_department boolean := FALSE;
70 l_found_asset_number boolean := FALSE;
71
72 l_attribute VARCHAR2(30);
73
74
75 BEGIN
76
77 -- Get the attributes of the detail record - element name, input values
78
79 g_msg := 'Inside Method get_attribute_id' ;
80 fnd_file.put_line(FND_FILE.LOG, g_msg);
81 -- dbms_output.put_line(g_msg);
82
83 l_att_index := NVL( p_last_att_index, p_att_table.FIRST);
84 l_bld_blk_id := p_att_table(l_att_index).bb_id;
85
86 g_msg := 'Building Block Id : ' || l_bld_blk_id || ' Attribute Index : ' || l_att_index ;
87 fnd_file.put_line(FND_FILE.LOG, g_msg);
88 -- dbms_output.put_line(g_msg);
89
90 -- sanity check to make sure we are in sync
91
92 IF ( l_bld_blk_id <> p_bb_id )
93 THEN
94
95 -- dbms_output.put_line('in sanity check!!!!');
96
97 g_status := 'ERRORS';
98
99 -- define our error mechanism
100
101 raise bld_block_mismatch;
102
103 END IF;
104
105 IF p_att_table.COUNT <> 0 THEN
106 --
107 FOR att IN l_att_index .. p_att_table.LAST
108 LOOP
109
110 -- dbms_output.put_line('AT'||p_att_table(att).field_name||' '||p_att_table(att).value);
111
112 IF ( l_bld_blk_id <> p_att_table(att).bb_id ) THEN
113 p_last_att_index := att;
114
115 g_msg := 'Last Attribute of Building Block Id :' || l_bld_blk_id || ' is ' || p_last_att_index ;
116 fnd_file.put_line(FND_FILE.LOG, g_msg);
117 -- dbms_output.put_line(g_msg);
118 EXIT;
119 END IF;
120
121 -- Check for Workorder
122
123 if( upper(p_att_table(att).field_name) = l_work_order ) then
124 l_found_workorder :=TRUE;
125 l_work_order_id:= to_number (p_att_table(att).value);
126
127 g_msg := 'Work Order is : ' || l_work_order_id;
128 fnd_file.put_line(FND_FILE.LOG, g_msg);
129 -- dbms_output.put_line(g_msg);
130
131 -- Check for Operation
132
133 elsif ( upper(p_att_table(att).field_name) = l_operation ) then
134 l_found_operation :=TRUE;
135 l_operation_id:= to_number (p_att_table(att).value);
136
137 g_msg := 'Operation is : ' || l_operation_id;
138 fnd_file.put_line(FND_FILE.LOG, g_msg);
139 -- dbms_output.put_line(g_msg);
140
141 -- Check for Resource
142
143 elsif ( upper(p_att_table(att).field_name) = l_resource_code ) then
144 l_found_resource :=TRUE;
145 l_resource_id:= to_number (p_att_table(att).value);
146
147 g_msg := 'Resource is : ' || l_resource_id;
148 fnd_file.put_line(FND_FILE.LOG, g_msg);
149 -- dbms_output.put_line(g_msg);
150
151
152 -- Check for Charge Department
153
154 elsif ( upper(p_att_table(att).field_name) = l_charge_department ) then
155 l_found_charge_department :=TRUE;
156 l_charge_department_id:= to_number (p_att_table(att).value);
157
158 g_msg := 'Charge Department is : ' || l_charge_department_id;
159 fnd_file.put_line(FND_FILE.LOG, g_msg);
160 -- dbms_output.put_line(g_msg);
161
162 -- Check for Asset Group Id
163
164 elsif ( upper(p_att_table(att).field_name) = l_asset_group ) then
165 l_found_asset_group_id :=TRUE;
166 l_asset_group_id:= to_number (p_att_table(att).value);
167
168 g_msg := 'Asset Group Id is : ' || l_asset_group_id;
169 fnd_file.put_line(FND_FILE.LOG, g_msg);
170 -- dbms_output.put_line(g_msg);
171
172 -- Check for Owning Department Id
173
174 elsif ( upper(p_att_table(att).field_name) = l_owning_department ) then
175 l_found_owning_department :=TRUE;
176 l_owning_department_id:= to_number (p_att_table(att).value);
177
178 g_msg := 'Owning Department Id is : ' || l_owning_department_id;
179 fnd_file.put_line(FND_FILE.LOG, g_msg);
180 -- dbms_output.put_line(g_msg);
181
182 -- Check for Asset Number
183
184 elsif ( upper(p_att_table(att).field_name) = l_asset_number ) then
185 l_found_asset_number :=TRUE;
186 l_asset_number_id:= p_att_table(att).value;
187
188 g_msg := 'Asset Number is : ' || l_asset_number_id;
189 fnd_file.put_line(FND_FILE.LOG, g_msg);
190 -- dbms_output.put_line(g_msg);
191
192 end if;
193
194 END LOOP;
195 --
196 ELSE
197 --
198 g_status := 'ERRORS';
199 g_msg := 'No Attributes Found ';
200 fnd_file.put_line(FND_FILE.LOG, g_msg);
201 -- dbms_output.put_line(g_msg);
202 g_exception_description := g_msg;
203 raise no_attributes_found;
204
205 --
206 END IF;
207
208 x_workorder := l_work_order_id;
209 x_operation := l_operation_id;
210 x_resource := l_resource_id;
211 x_charge_department := l_charge_department_id;
212 x_asset_group_id := l_asset_group_id;
213 x_owning_department := l_owning_department_id;
214 x_asset_number := l_asset_number_id;
215
216
217 if(l_found_workorder = FALSE) then
218 g_status := 'ERRORS';
219 raise no_workorder_found;
220
221 elsif (l_found_operation = FALSE) then
222 g_status := 'ERRORS';
223 raise no_operation_found;
224
225 elsif (l_found_resource = FALSE) then
226 g_status := 'ERRORS';
227 raise no_resource_found;
228 end if;
229
230 --
231 EXCEPTION
232
233 WHEN no_workorder_found then
234
235 g_msg := 'Work Order not found';
236 fnd_file.put_line(FND_FILE.LOG, g_msg);
237 -- dbms_output.put_line(g_msg);
238 g_exception_description := g_msg;
239
240 WHEN no_operation_found then
241
242 g_msg := 'Operation not found';
243 fnd_file.put_line(FND_FILE.LOG, g_msg);
244 -- dbms_output.put_line(g_msg);
245 g_exception_description := g_msg;
246
247 WHEN no_resource_found then
248
249 g_msg := 'Resource not found';
250 fnd_file.put_line(FND_FILE.LOG, g_msg);
251 -- dbms_output.put_line(g_msg);
252 g_exception_description := g_msg;
253
254 WHEN bld_block_mismatch then
255
256 g_msg := 'Mismatch of Building Block Id' ;
257 fnd_file.put_line(FND_FILE.LOG, g_msg);
258 -- dbms_output.put_line(g_msg);
259
260 WHEN others then
261
262 g_msg := 'UNEXPECTED ERROR: ' || SQLERRM;
263 fnd_file.put_line(FND_FILE.LOG, g_msg);
264 -- dbms_output.put_line(g_msg);
265
266
267 END get_attribute_id;
268
269 -- End of fetching all attributes
270
271
272
273
274 -- Procedure for performing Resource Transaction
275 -- This procedure will insert data into WIP_COST_TXN_INTERFACE table for Cost Manager to process
276 -- Besides the process will also insert or delete data from WIP_OPERATION_RESOURCES and
277 -- WIP_OPERATION_RES_INSTANCES, in case we are trying to reverse certain transactions
278
279 PROCEDURE perform_res_txn (p_wip_entity_id IN NUMBER,
280 p_operation_seq_num IN NUMBER,
281 p_resource_id IN NUMBER,
282 p_instance_id IN NUMBER,
283 p_charge_department_id IN NUMBER,
284 p_bb_id IN NUMBER,
285 p_transaction_qty IN NUMBER,
286 p_start_time IN DATE) IS
287
288 invalid_resource EXCEPTION;
289 invalid_uom EXCEPTION;
290 invalid_employee EXCEPTION;
291 invalid_equipment EXCEPTION;
292 invalid_charge_department EXCEPTION;
293 invalid_machine EXCEPTION;
294 invalid_person EXCEPTION;
295 operation_res_combination EXCEPTION;
296 invalid_wo EXCEPTION;
297
298
299
300 l_st number;
301 l_rs_st number;
302 l_u_st number;
303 l_em_st number;
304 l_eq_st number;
305 l_re_st number;
306 l_d_st number;
307 l_m_st number;
308 l_p_st number;
309 l_rt_st varchar2(10);
310 l_emp_id number;
311 l_emp_no varchar2(30);
312 l_wo_st number;
313
314 l_actual_resource number;
315 l_instance_id number;
316 l_charge_dept_id number;
317
318 l_msg_count number;
319 l_msg_data varchar2(100);
320 l_workorder number;
321 l_operation number;
322 l_resource number;
323 l_charge_department number;
324
325 l_asset_group number;
326 l_owning_department number;
327 l_asset_number varchar2(30);
328 l_resource_seq_num number;
329 l_person_id number;
330 l_employee_name varchar2(80);
331 l_organization_id number;
332 l_resource_code varchar2(80);
333 l_uom varchar2(3);
334 l_charge_department_code varchar2(80);
335 l_bb_id number;
336
337 l_return_status1 varchar2(10);
338 l_msg_count1 number;
339 l_msg_data1 varchar2(100);
340 l_measure NUMBER;
341 l_start_time DATE;
342
343
344
345 begin
346
347 l_workorder := p_wip_entity_id;
348 l_operation := p_operation_seq_num;
349 l_resource := p_resource_id;
350 l_charge_department := p_charge_department_id;
351 l_bb_id := p_bb_id ;
352 l_measure := p_transaction_qty;
353 l_start_time := p_start_time;
354
355 g_msg := 'Start of perform_res_txn' ;
356 fnd_file.put_line(FND_FILE.LOG, g_msg);
357 -- dbms_output.put_line(g_msg);
358
359 g_msg := 'Work Order is : ' || l_workorder || ' and Operation is : ' || l_operation || ' and Resource is : ' || l_resource || ' and Employee is :' || p_instance_id;
360 fnd_file.put_line(FND_FILE.LOG, g_msg);
361 -- dbms_output.put_line(g_msg);
362
363
364 -- Get Resource Sequence Number
365
366 begin
367
368 if (l_workorder is not null AND l_operation is not null) then
369
370 select nvl((max(resource_seq_num) + 10),10)
371 into l_resource_seq_num
372 from wip_operation_resources
373 where wip_entity_id = l_workorder
374 and operation_seq_num = l_operation;
375
376 end if;
377
378 exception
379
380 when others then
381
382 l_resource_seq_num := 10;
383
384 end;
385
386 g_msg := 'Resource Sequence Number is : ' || l_resource_seq_num;
387 fnd_file.put_line(FND_FILE.LOG, g_msg);
388 -- dbms_output.put_line(g_msg);
389
390
391
392 -- Get Employee Name
393
394 begin
395
396 if (p_instance_id is not null) then
397
398 --fix for 3823899.use table per_all_people_f and check effectivity dates
399 --Fix for 6808173. Modified check for effectivity dates. It was causing errors for name changes made in HR.
400
401 select distinct full_name
402 into l_employee_name
403 from PER_ALL_PEOPLE_F
404 where person_id = p_instance_id
405 and l_start_time BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
406 --and NVL(current_employee_flag,'N')='Y';
407
408 end if;
409
410 exception
411
412 when others then
413
414 null;
415
416 end;
417
418 g_msg := 'Employee Name is : ' || l_employee_name;
419 fnd_file.put_line(FND_FILE.LOG, g_msg);
420 -- dbms_output.put_line(g_msg);
421
422 -- Get Organization Id
423
424 begin
425
426 if (l_workorder is not null) then
427
428 select organization_id
429 into l_organization_id
430 from wip_entities
431 where wip_entity_id = l_workorder;
432
433 end if;
434
435 exception
436
437 when others then
438
439 null;
440
441 end;
442
443 g_msg := 'Organization Id is : ' || l_organization_id;
444 fnd_file.put_line(FND_FILE.LOG, g_msg);
445 -- dbms_output.put_line(g_msg);
446
447 -- Get Resource Code
448
449 begin
450
451 if (l_resource is not null) then
452
453 select resource_code , unit_of_measure
454 into l_resource_code , l_uom
455 from bom_resources
456 where resource_id = l_resource
457 and organization_id = l_organization_id ;
458
459 end if;
460
461 exception
462
463 when others then
464
465 null;
466
467 end;
468
469 g_msg := 'Resource Code is : ' || l_resource_code;
470 fnd_file.put_line(FND_FILE.LOG, g_msg);
471 -- dbms_output.put_line(g_msg);
472
473
474 -- Get Charge Department
475
476 begin
477
478 if (l_charge_department is not null) then
479
480 select distinct department_code
481 into l_charge_department_code
482 from bom_departments
483 where organization_id = l_organization_id
484 and department_id = l_charge_department;
485
486 end if;
487
488 exception
489
490 when others then
491
492 null;
493
494 end;
495
496 g_msg := 'Charge Department is : ' || l_charge_department_code;
497 fnd_file.put_line(FND_FILE.LOG, g_msg);
498 -- dbms_output.put_line(g_msg);
499
500 g_msg := 'Start of Resource Validation in WIP_EAM_RESOURCE_TRANSACTION.resource_validate';
501 fnd_file.put_line(FND_FILE.LOG, g_msg);
502 -- dbms_output.put_line(g_msg);
503
504 if (l_workorder is not null) then
505
506
507
508 WIP_EAM_RESOURCE_TRANSACTION.resource_validate (
509 p_api_version => 1.0
510 ,p_init_msg_list => fnd_api.g_false
511 ,p_commit => fnd_api.g_false
512 ,p_validation_level => fnd_api.g_valid_level_full
513 ,p_wip_entity_id => l_workorder
514 ,p_operation_seq_num => l_operation
515 ,p_organization_id => l_organization_id
516 ,p_resource_seq_num => l_resource_seq_num
517 ,p_resource_code => l_resource_code
518 ,p_uom_code => l_uom
519 ,p_employee_name => l_employee_name
520 ,p_equipment_name => null
521 ,p_reason => null
522 ,p_charge_dept => l_charge_department_code
523 ,p_start_time => l_start_time --for bug 6808173
524 ,x_actual_resource_rate => l_actual_resource
525 ,x_status => l_st
526 ,x_res_status => l_rs_st
527 ,x_uom_status => l_u_st
528 ,x_employee_status => l_em_st
529 ,x_employee_id => l_emp_id
530 ,x_employee_number => l_emp_no
531 ,x_equipment_status => l_eq_st
532 ,x_reason_status => l_re_st
533 ,x_charge_dept_status => l_d_st
534 ,x_machine_status => l_m_st
535 ,x_person_status => l_p_st
536 ,x_work_order_status => l_wo_st
537 ,x_instance_id => l_instance_id
538 ,x_charge_dept_id => l_charge_dept_id
539 ,x_resource_seq_num => l_resource_seq_num
540 ,x_return_status => l_rt_st
541 ,x_msg_count => l_msg_count
542 ,x_msg_data => l_msg_data);
543
544
545 if (l_rs_st = 1) then
546
547 g_status := 'ERRORS';
548 raise invalid_resource;
549 end if;
550
551 if (l_u_st = 1) then
552
553 g_status := 'ERRORS';
554 raise invalid_uom;
555 end if;
556
557 if (l_em_st = 1) then
558
559 g_status := 'ERRORS';
560 raise invalid_employee;
561
562 end if;
563
564 if (l_eq_st = 1) then
565
566 g_status := 'ERRORS';
567 raise invalid_equipment;
568 end if;
569
570
571 if (l_d_st = 1) then
572
573 g_status := 'ERRORS';
574 raise invalid_charge_department;
575 end if;
576
577
578 if (l_m_st = 1) then
579
580 g_status := 'ERRORS';
581 raise invalid_machine;
582 end if;
583
584
585 if (l_p_st = 1) then
586
587 g_status := 'ERRORS';
588 raise invalid_person;
589 end if;
590
591
592 if (l_st = 1) then
593
594 g_status := 'ERRORS';
595 raise operation_res_combination;
596 end if;
597
598 if (l_wo_st = 1) then
599 g_status := 'ERRORS';
600 raise invalid_wo;
601 end if;
602
603
604
605 -- dbms_output.put_line ('Resource Seq Num is :' || l_resource_seq_num);
606 -- Added check (l_wo_st =0) in if condition
607 if ((l_rs_st = 0) and (l_st = 0) and (l_u_st = 0) and (l_em_st = 0)and (l_eq_st = 0)) then
608 if ((l_re_st = 0) and (l_d_st = 0) and (l_m_st = 0) and (l_p_st = 0) and (l_rt_st = 'S') and (l_wo_st =0)) then
609
610 -- dbms_output.put_line ('Inside the loop');
611 WIP_EAM_RESOURCE_TRANSACTION.insert_into_wcti(
612 p_api_version => 1.0
613 ,p_init_msg_list => fnd_api.g_false
614 ,p_commit => fnd_api.g_false
615 ,p_validation_level => fnd_api.g_valid_level_full
616 ,p_wip_entity_id => l_workorder
617 ,p_operation_seq_num => l_operation
618 ,p_organization_id => l_organization_id
619 ,p_transaction_qty => l_measure
620 ,p_transaction_date => l_start_time
621 ,p_resource_seq_num => l_resource_seq_num
622 ,p_uom => l_uom
623 ,p_resource_code => l_resource_code
624 ,p_reason_name => null
625 ,p_reference => null
626 ,p_instance_id => l_instance_id
627 ,p_serial_number => null
628 ,p_charge_dept_id => l_charge_dept_id
629 ,p_actual_resource_rate => l_actual_resource
630 ,p_employee_id => l_emp_id
631 ,p_employee_number => l_emp_no
632 ,x_return_status => l_return_status1
633 ,x_msg_count => l_msg_count1
634 ,x_msg_data => l_msg_data1);
635 -- dbms_output.put_line ('After insert into wcti');
636 end if;
637
638 end if;
639 -- dbms_output.put_line ('End of insert into wcti');
640
641 if (l_return_status1 = 'S') then
642
643 -- set the transaction status for the block we have processed
644
645 g_msg := 'Insert into WCTI - SUCCESS';
646 fnd_file.put_line(FND_FILE.LOG, g_msg);
647 -- dbms_output.put_line(g_msg);
648
649 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(l_bb_id) := 'SUCCESS';
650 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(l_bb_id) := 'Resource Transaction Processed Successfully';
651
652 else
653
654 g_msg := 'Insert into WCTI failed';
655 fnd_file.put_line(FND_FILE.LOG, g_msg);
656 -- dbms_output.put_line(g_msg);
657
658 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(l_bb_id) := 'ERRORS';
659 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(l_bb_id) := 'Resource Transaction Failed';
660
661 end if;
662
663 else
664
665 g_msg := 'Work order details no found - skip resource insertion';
666 fnd_file.put_line(FND_FILE.LOG, g_msg);
667
668 end if;
669
670 EXCEPTION
671
672 WHEN invalid_resource THEN
673
674 fnd_message.set_name('EAM', 'EAM_WO_INVALID_RES_SELECTED');
675 g_exception_description := SUBSTR(fnd_message.get,1,2000);
676 g_msg := g_exception_description;
677 fnd_file.put_line(FND_FILE.LOG, g_msg);
678 -- dbms_output.put_line(g_msg);
679
680
681 WHEN invalid_uom THEN
682
683 fnd_message.set_name('EAM', 'EAM_WO_INVALID_UOM_ENTRY');
684 g_exception_description := SUBSTR(fnd_message.get,1,2000);
685 g_msg := g_exception_description;
686 fnd_file.put_line(FND_FILE.LOG, g_msg);
687 -- dbms_output.put_line(g_msg);
688
689 WHEN invalid_employee THEN
690
691 fnd_message.set_name('EAM', 'EAM_WO_INVALID_EMPLOYEE_ENTRY');
692 g_exception_description := SUBSTR(fnd_message.get,1,2000);
693 g_msg := g_exception_description;
694 fnd_file.put_line(FND_FILE.LOG, g_msg);
695 -- dbms_output.put_line(g_msg);
696
697 WHEN invalid_equipment THEN
698
699 fnd_message.set_name('EAM', 'EAM_WO_INVALID_EQUIPMENT_ENTRY');
700 g_exception_description := SUBSTR(fnd_message.get,1,2000);
701 g_msg := g_exception_description;
702 fnd_file.put_line(FND_FILE.LOG, g_msg);
703 -- dbms_output.put_line(g_msg);
704
705 WHEN invalid_charge_department THEN
706
707 fnd_message.set_name('EAM', 'EAM_WO_INVALID_CHARGE_DEPT_ENT');
708 g_exception_description := SUBSTR(fnd_message.get,1,2000);
709 g_msg := g_exception_description;
710 fnd_file.put_line(FND_FILE.LOG, g_msg);
711 -- dbms_output.put_line(g_msg);
712
713 WHEN invalid_machine THEN
714
715 fnd_message.set_name('EAM', 'EAM_WO_RES_NOT_A_MACHINE');
716 g_exception_description := SUBSTR(fnd_message.get,1,2000);
717 g_msg := g_exception_description;
718 fnd_file.put_line(FND_FILE.LOG, g_msg);
719 -- dbms_output.put_line(g_msg);
720
721 WHEN invalid_person THEN
722
723 fnd_message.set_name('EAM', 'EAM_WO_RES_NOT_AN_EMPLOYEE');
724 g_exception_description := SUBSTR(fnd_message.get,1,2000);
725 g_msg := g_exception_description;
726 fnd_file.put_line(FND_FILE.LOG, g_msg);
727 -- dbms_output.put_line(g_msg);
728
729 WHEN operation_res_combination THEN
730
731 fnd_message.set_name('EAM', 'EAM_WO_INVALID_RES_SEQ_COMB');
732 g_exception_description := SUBSTR(fnd_message.get,1,2000);
733 g_msg := g_exception_description;
734 fnd_file.put_line(FND_FILE.LOG, g_msg);
735 -- dbms_output.put_line(g_msg);
736
737 WHEN invalid_wo THEN
738 fnd_message.set_name('EAM', 'EAM_NO_CHARGES_ALLOWED');
739 g_exception_description := SUBSTR(fnd_message.get,1,2000);
740 g_msg := g_exception_description;
741 fnd_file.put_line(FND_FILE.LOG, g_msg);
742
743 WHEN others then
744
745 g_msg := 'UNEXPECTED ERROR: ' || SQLERRM;
746 fnd_file.put_line(FND_FILE.LOG, g_msg);
747 -- dbms_output.put_line(g_msg);
748
749 end;
750
751
752 -- Function for forming the where clause
753
754 FUNCTION where_clause (p_asset_group_id IN NUMBER,
755 p_asset_number IN VARCHAR2,
756 p_owning_department IN NUMBER,
757 p_charge_department IN NUMBER,
758 p_resource_id IN NUMBER,
759 p_wip_entity_id IN NUMBER,
760 p_operation_seq_num IN NUMBER,
761 p_organization_id IN NUMBER,
762 p_person_id IN NUMBER,
763 --p_project_id IN NUMBER,
764 --p_task_id IN NUMBER,
765 p_where_clause IN OUT NOCOPY VARCHAR2) RETURN VARCHAR2
766
767 IS
768
769 l_where_clause VARCHAR2(25000);
770 l_count NUMBER;
771
772 BEGIN
773
774 l_count := 0;
775 g_msg := 'Inside Method where_clause';
776 fnd_file.put_line(FND_FILE.LOG, g_msg);
777 -- dbms_output.put_line(g_msg);
778
779 g_msg := 'Input Parameters are : Asset Group Id : ' || p_asset_group_id || ' Asset Number : ' || p_asset_number || ' Owning Department : ' || p_owning_department || ' Charge Department : ' || p_charge_department ;
780 fnd_file.put_line(FND_FILE.LOG, g_msg);
781 -- dbms_output.put_line(g_msg);
782
783 g_msg := 'Resource Id : ' || p_resource_id || ' Wip Entity Id : ' || p_wip_entity_id || ' Operation Seq Num: ' || p_operation_seq_num || ' Organization Id : ' || p_organization_id || ' Person Id :' || p_person_id ;
784 fnd_file.put_line(FND_FILE.LOG, g_msg);
785 -- dbms_output.put_line(g_msg);
786
787 l_where_clause := p_where_clause ;
788
789 -- Append Inventory Item Id if it is NOT NULL
790
791 if (p_asset_group_id is not null) then
792
793 l_count := l_count + 1;
794
795 l_where_clause := l_where_clause || '[EAMASSETGROUP] {= '''|| to_char(p_asset_group_id) || '''}';
796
797 end if;
798
799 g_msg := 'where_clause after Inventory Item Id ' || l_where_clause ;
800 fnd_file.put_line(FND_FILE.LOG, g_msg);
801 -- dbms_output.put_line(g_msg);
802
803 -- Append Asset Number if it is NOT NULL
804
805 if (p_asset_number is not null) then
806
807 l_count := l_count + 1;
808
809 if (l_count > 1) then
810
811 l_where_clause := l_where_clause || ' AND ' || '[EAMASSETNUMBER] {= '''|| p_asset_number || '''}';
812
813 else
814
815 l_where_clause := l_where_clause || '[EAMASSETNUMBER] {= '''|| p_asset_number || '''}';
816
817 end if;
818
819 end if;
820
821 g_msg := 'where_clause after Asset Number ' || l_where_clause;
822 fnd_file.put_line(FND_FILE.LOG, g_msg);
823 -- dbms_output.put_line(g_msg);
824
825 -- Append Owning Department if it is NOT NULL
826
827 if (p_owning_department is not null) then
828
829 l_count := l_count + 1;
830
831 if (l_count > 1) then
832
833 l_where_clause := l_where_clause || ' AND ' || '[EAMDEPARTMENTID] {= '''|| to_char(p_owning_department) || '''}';
834
835 else
836
837 l_where_clause := l_where_clause || '[EAMDEPARTMENTID] {= '''|| to_char(p_owning_department) || '''}';
838
839 end if;
840
841 end if;
842
843 g_msg := 'where_clause after Owning Department ' || l_where_clause;
844 fnd_file.put_line(FND_FILE.LOG, g_msg);
845 -- dbms_output.put_line(g_msg);
846
847 -- Append Resource Id if it is NOT NULL
848
849 if (p_resource_id is not null) then
850
851 l_count := l_count + 1;
852
853 if (l_count > 1) then
854
855 l_where_clause := l_where_clause || ' AND ' || '[EAMRESOURCE] {= '''|| to_char(p_resource_id) || '''}';
856
857 else
858
859 l_where_clause := l_where_clause || '[EAMRESOURCE] {= '''|| to_char(p_resource_id) || '''}';
860
861 end if;
862
863 end if;
864
865 g_msg := 'where_clause after Resource Id ' || l_where_clause;
866 fnd_file.put_line(FND_FILE.LOG, g_msg);
867 -- dbms_output.put_line(g_msg);
868
869 -- Append Wip Entity Id if it is NOT NULL
870
871 if (p_wip_entity_id is not null) then
872
873 l_count := l_count + 1;
874
875 if (l_count > 1) then
876
877 l_where_clause := l_where_clause || ' AND ' || '[EAMWORKORDER] {= '''|| to_char(p_wip_entity_id) || '''}';
878
879 else
880
881 l_where_clause := l_where_clause || '[EAMWORKORDER] {= '''|| to_char(p_wip_entity_id) || '''}';
882
883 end if;
884
885 end if;
886
887 g_msg := 'where_clause after Wip Entity Id ' || l_where_clause;
888 fnd_file.put_line(FND_FILE.LOG, g_msg);
889 -- dbms_output.put_line(g_msg);
890
891 -- Append Operation if it is NOT NULL
892
893 if (p_operation_seq_num is not null) then
894
895 l_count := l_count + 1;
896
897 if (l_count > 1) then
898
899 l_where_clause := l_where_clause || ' AND ' || '[EAMOPERATION] {= '''|| to_char(p_operation_seq_num) || '''}';
900
901 else
902
903 l_where_clause := l_where_clause || '[EAMOPERATION] {= '''|| to_char(p_operation_seq_num) || '''}';
904
905 end if;
906
907 end if;
908
909 g_msg := 'where_clause after Operation ' || l_where_clause;
910 fnd_file.put_line(FND_FILE.LOG, g_msg);
911 -- dbms_output.put_line(g_msg);
912
913 -- Append Organization Id if it is NOT NULL
914
915 if (p_organization_id is not null) then
916
917 l_count := l_count + 1;
918
919 if (l_count > 1) then
920
921 l_where_clause := l_where_clause || ' AND ' || '[EAMORGANIZATIONID] {= '''|| to_char(p_organization_id) || '''}';
922
923 else
924
925 l_where_clause := l_where_clause || '[EAMORGANIZATIONID] {= '''|| to_char(p_organization_id) || '''}';
926
927 end if;
928
929 end if;
930
931 g_msg := 'where_clause after Organization Id ' || l_where_clause;
932 fnd_file.put_line(FND_FILE.LOG, g_msg);
933 -- dbms_output.put_line(g_msg);
934
935 -- Append Charge Department if it is NOT NULL
936
937 if (p_charge_department is not null) then
938
939 l_count := l_count + 1;
940
941 if (l_count > 1) then
942
943 l_where_clause := l_where_clause || ' AND ' || '[EAMCHARGEDEPT] {= '''|| to_char(p_charge_department) || '''}';
944
945 else
946
947 l_where_clause := l_where_clause || '[EAMCHARGEDEPT] {= '''|| to_char(p_charge_department) || '''}';
948
949 end if;
950
951 end if;
952
953 g_msg := 'where_clause after Charge Department ' || l_where_clause;
954 fnd_file.put_line(FND_FILE.LOG, g_msg);
955 -- dbms_output.put_line(g_msg);
956
957 -- Append Project Id if it is NOT NULL
958
959 /*if (p_project_id is not null) then
960
961 l_count := l_count + 1;
962
963 if (l_count > 1) then
964
965 l_where_clause := l_where_clause || ' AND ' || '[Project_Id] {= '''|| to_char(p_project_id) || '''}';
966
967 else
968
969 l_where_clause := l_where_clause || '[Project_Id] {= '''|| to_char(p_project_id) || '''}';
970
971 end if;
972
973 end if;
974
975 g_msg := 'where_clause after Project Id ' || l_where_clause; */
976 fnd_file.put_line(FND_FILE.LOG, g_msg);
977 -- dbms_output.put_line(g_msg);
978
979 -- Append Task Id if it is NOT NULL
980
981 /*if (p_task_id is not null) then
982
983 l_count := l_count + 1;
984
985 if (l_count > 1) then
986
987 l_where_clause := l_where_clause || ' AND ' || '[Task_Id] {= '''|| to_char(p_task_id) || '''}';
988
989 else
990
991 l_where_clause := l_where_clause || '[Task_Id] {= '''|| to_char(p_task_id) || '''}';
992
993 end if;
994
995 end if;*/
996
997 g_msg := 'where_clause after Task Id ' || l_where_clause;
998 fnd_file.put_line(FND_FILE.LOG, g_msg);
999 -- dbms_output.put_line(g_msg);
1000
1001
1002 -- Append Person Id if it is NOT NULL
1003
1004 if (p_person_id is not null) then
1005
1006 l_count := l_count + 1;
1007
1008 if (l_count > 1) then
1009
1010 l_where_clause := l_where_clause || ' AND ' || '[TIMECARD_BLOCK.RESOURCE_ID] {= '''|| to_char(p_person_id) || '''}';
1011
1012 else
1013
1014 l_where_clause := l_where_clause || '[TIMECARD_BLOCK.RESOURCE_ID] {= '''|| to_char(p_person_id) || '''}';
1015
1016 end if;
1017
1018 end if;
1019
1020 g_msg := 'Final where_clause ' || l_where_clause;
1021 fnd_file.put_line(FND_FILE.LOG, g_msg);
1022 -- dbms_output.put_line(g_msg);
1023
1024 return l_where_clause;
1025 END;
1026
1027 -- End of Function for forming the where clause
1028
1029
1030
1031 -- Main Retrieval Procedure
1032
1033
1034
1035 PROCEDURE retrieve_process (
1036 errbuf out nocopy varchar2,
1037 retcode out nocopy varchar2,
1038 p_start_date IN varchar2,
1039 p_end_date IN varchar2,
1040 p_organization_id IN NUMBER,
1041 p_asset_group_id IN NUMBER,
1042 p_asset_number IN VARCHAR2,
1043 --p_project_id IN NUMBER,
1044 --p_task_id IN NUMBER,
1045 p_resource_id IN NUMBER,
1046 p_person_id IN NUMBER,
1047 p_owning_department IN NUMBER,
1048 p_wip_entity_id IN NUMBER,
1049 p_operation_seq_num IN NUMBER,
1050 p_charge_department IN NUMBER,
1051 p_transaction_code IN VARCHAR2
1052 ) IS
1053
1054 l_last_att_index number;
1055 l_bb_id NUMBER(15);
1056 l_bb_index BINARY_INTEGER;
1057 l_type VARCHAR2(30);
1058 l_measure NUMBER;
1059 l_start_time DATE;
1060 l_stop_time DATE;
1061 l_parent_bb_id NUMBER(15);
1062 l_scope VARCHAR2(30);
1063 l_resource_id NUMBER(15);
1064 l_resource_type VARCHAR2(30);
1065 l_comment_text VARCHAR2(2000);
1066 l_changed VARCHAR2(1);
1067 l_deleted VARCHAR2(1);
1068 l_index NUMBER;
1069
1070 l_old_measure NUMBER;
1071 l_old_start_time DATE;
1072 l_old_count NUMBER;
1073
1074
1075 -- Specific Variables
1076 l_workorder number;
1077 l_operation number;
1078 l_resource number;
1079 l_charge_department number;
1080 l_asset_group number;
1081 l_owning_department number;
1082 l_asset_number varchar2(30);
1083 l_resource_seq_num number := 10;
1084 l_person_id number;
1085 l_employee_name varchar2(80);
1086 l_organization_id number;
1087 l_resource_code varchar2(80);
1088 l_charge_department_code varchar2(80);
1089
1090 l_actual_resource number;
1091 l_status number;
1092 l_res_status number;
1093 l_uom_status number;
1094 l_employee_status number;
1095 l_equipment_status number;
1096 l_reason_status number;
1097 l_charge_dept_status number;
1098 l_machine_status number;
1099 l_person_status number;
1100 l_instance_id number;
1101 l_charge_dept_id number;
1102 l_return_status varchar2(10);
1103 l_msg_count number;
1104 l_msg_data varchar2(100);
1105 l_return_status1 varchar2(10);
1106 l_msg_count1 number;
1107 l_msg_data1 varchar2(100);
1108
1109 -- Old Details
1110 l_old_workorder number;
1111 l_old_operation number;
1112 l_old_resource number;
1113 l_old_charge_department number;
1114 l_old_asset_group number;
1115 l_old_owning_department number;
1116 l_old_asset_number varchar2(30);
1117
1118 t_temp_attr_index number;
1119
1120 l_stmt_num NUMBER;
1121 l_where_clause VARCHAR2(25000) := '';
1122
1123 l_conc_status BOOLEAN;
1124 l_error_message VARCHAR2(2000);
1125 l_error_code NUMBER;
1126
1127
1128
1129
1130 begin
1131
1132 g_msg := 'Entering retrieve_process method';
1133 fnd_file.put_line(FND_FILE.LOG, g_msg);
1134 -- dbms_output.put_line(g_msg);
1135
1136 l_stmt_num := 10;
1137 SAVEPOINT retrieve_process_pub;
1138
1139 -- Initialize message list
1140 fnd_msg_pub.initialize;
1141
1142 -- Initialize API return status to success
1143 --l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', l_error_message);
1144
1145 l_stmt_num := 20;
1146
1147 -- API starts
1148
1149 l_error_code := 9999;
1150 l_error_message := 'Unknown Exception';
1151
1152 g_msg := '';
1153 fnd_file.put_line(FND_FILE.LOG, g_msg);
1154 -- dbms_output.put_line(g_msg);
1155
1156 fnd_file.put_line(FND_FILE.LOG, 'Start Retrieval Process. Time now is ' || to_char(sysdate, 'Month DD, YYYY HH24:MI:SS'));
1157
1158 fnd_file.put_line(FND_FILE.LOG, g_msg);
1159
1160 fnd_file.new_line(FND_FILE.LOG,1);
1161
1162 g_msg := 'Before Building Where Clause in Retrieve Process';
1163 fnd_file.put_line(FND_FILE.LOG, g_msg);
1164 -- dbms_output.put_line(g_msg);
1165
1166 l_where_clause := where_clause (p_asset_group_id,p_asset_number,p_owning_department,p_charge_department,
1167 p_resource_id,p_wip_entity_id,p_operation_seq_num,p_organization_id,p_person_id,l_where_clause);
1168
1169 g_msg := 'After Building Where Clause in Retrieve Process, Where Clause is : ' || l_where_clause;
1170 fnd_file.put_line(FND_FILE.LOG, g_msg);
1171
1172 -- call generic retrieval
1173
1174
1175
1176 HXC_INTEGRATION_LAYER_V1_GRP.execute_retrieval_process (
1177 p_process => 'Maintenance Retrieval Process'
1178 , p_transaction_code => p_transaction_code
1179 , p_start_date => to_date(p_start_date,'DD-MM-YYYY')
1180 , p_end_date => to_date(p_end_date,'DD-MM-YYYY')
1181 , p_incremental => 'Y'
1182 , p_rerun_flag => 'N'
1183 , p_where_clause => l_where_clause
1184 , p_scope => 'DAY'
1185 , p_clusive => 'EX'
1186 );
1187
1188
1189 -- process results of generic retrieval
1190
1191 g_msg := 'After HXC_INTEGRATION_LAYER_V1_GRP.execute_retrieval_process ';
1192 fnd_file.put_line(FND_FILE.LOG, g_msg);
1193 -- dbms_output.put_line(g_msg);
1194
1195 g_msg := 'Start Processing of Building Blocks start now ';
1196 fnd_file.put_line(FND_FILE.LOG, g_msg);
1197 -- dbms_output.put_line(g_msg);
1198
1199 -- dbms_output.put_line(HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.COUNT);
1200
1201
1202
1203 IF HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.COUNT <> 0 THEN
1204
1205 l_old_count := HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_bld_blks.first;
1206
1207 FOR l_cnt in HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.first ..
1208 HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.last
1209
1210 LOOP
1211
1212 g_msg := 'Processing Building Blocks : ' || l_cnt;
1213 fnd_file.put_line(FND_FILE.LOG, g_msg);
1214 -- dbms_output.put_line(g_msg);
1215
1216 g_status := 'SUCCESS';
1217
1218 BEGIN
1219 l_bb_id := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).bb_id;
1220 l_bb_index := l_cnt;
1221 l_type := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).type;
1222 l_measure := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).measure;
1223 l_start_time := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).start_time;
1224 l_stop_time := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).stop_time;
1225 l_scope := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).scope;
1226 l_resource_id := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).resource_id;
1227 l_resource_type := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).resource_type;
1228 l_changed := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).changed;
1229 l_deleted := HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks(l_cnt).deleted;
1230
1231
1232 g_msg := 'Building Block Id : ' || l_bb_id || 'Measure : ' || l_measure || ' Resource Id : '|| l_resource_id || ' Start Time : ' || l_start_time;
1233 fnd_file.put_line(FND_FILE.LOG, g_msg);
1234 -- dbms_output.put_line(g_msg);
1235
1236 g_msg := 'Building Block Status : Changed ' || l_changed || ' Deleted : ' || l_deleted;
1237 fnd_file.put_line(FND_FILE.LOG, g_msg);
1238 -- dbms_output.put_line(g_msg);
1239
1240
1241 -- only need to process detail building blocks for performing resource transactions
1242
1243 IF l_scope = 'DETAIL' THEN
1244
1245
1246
1247 -- Get the attributes for this building block
1248
1249 /*******************************************************************************/
1250
1251 -- Record is new and has not been deleted.So we need to perform the transaction
1252
1253 /*******************************************************************************/
1254
1255
1256 if( l_deleted = 'N') and (l_changed = 'N') then
1257
1258 g_msg := 'Before get_attribute_id - NEW (N,N)-> (Delete,Change)';
1259 fnd_file.put_line(FND_FILE.LOG, g_msg);
1260 -- dbms_output.put_line(g_msg);
1261
1262
1263 get_attribute_id (p_att_table => HXC_USER_TYPE_DEFINITION_GRP.t_detail_attributes,
1264 p_bb_id => l_bb_id,
1265 p_last_att_index => l_last_att_index,
1266 x_workorder => l_workorder,
1267 x_operation => l_operation,
1268 x_resource => l_resource,
1269 x_charge_department => l_charge_department,
1270 x_asset_group_id => l_asset_group,
1271 x_owning_department => l_owning_department,
1272 x_asset_number => l_asset_number);
1273
1274 g_msg := 'After get_attribute_id - NEW (N,N)-> (Delete,Change)';
1275 fnd_file.put_line(FND_FILE.LOG, g_msg);
1276 -- dbms_output.put_line(g_msg);
1277
1278 g_msg := 'l_workorder:' || l_workorder;
1279 fnd_file.put_line(FND_FILE.LOG, g_msg);
1280
1281 perform_res_txn (p_wip_entity_id => l_workorder,
1282 p_operation_seq_num => l_operation,
1283 p_resource_id => l_resource,
1284 p_instance_id => l_resource_id,
1285 p_charge_department_id => l_charge_department,
1286 p_bb_id => l_bb_id,
1287 p_transaction_qty => l_measure,
1288 p_start_time => l_start_time);
1289
1290 g_msg := 'After Resource Txn - (N,N)-> (Delete,Change)';
1291 fnd_file.put_line(FND_FILE.LOG, g_msg);
1292 -- dbms_output.put_line(g_msg);
1293
1294 end if;
1295
1296
1297 /*******************************************************************************/
1298
1299 -- Record has been changed and has been deleted.So we need to reverse
1300 -- the previous transaction. Do not need to take care of the changes
1301
1302 /*******************************************************************************/
1303
1304 /********************************************************************************/
1305 -- Bug 3427426
1306 -- For now, Once an OTL time card has been approved, no deletes will be allowed
1307 -- Bug 3753728 -- Corrections allowed for EAM timecard
1308 /********************************************************************************/
1309
1310 if( l_deleted = 'Y') and (l_changed = 'Y') then
1311
1312 -- t_temp_attr_index := l_last_att_index;
1313
1314 g_msg := 'Before get_attribute_id - NEW (Y,Y)-> (Delete,Change)';
1315 fnd_file.put_line(FND_FILE.LOG, g_msg);
1316
1317 g_msg := 'Update OTL Timecard. Resource Transaction reversed.';
1318 fnd_file.put_line(FND_FILE.LOG, g_msg);
1319 -- dbms_output.put_line(g_msg);
1320
1321 get_attribute_id (p_att_table => HXC_USER_TYPE_DEFINITION_GRP.t_detail_attributes,
1322 p_bb_id => l_bb_id,
1323 p_last_att_index => l_last_att_index,
1324 x_workorder => l_workorder,
1325 x_operation => l_operation,
1326 x_resource => l_resource,
1327 x_charge_department => l_charge_department,
1328 x_asset_group_id => l_asset_group,
1329 x_owning_department => l_owning_department,
1330 x_asset_number => l_asset_number);
1331
1332 g_msg := 'After get_attribute_id - NEW (Y,Y)-> (Delete,Change)';
1333 fnd_file.put_line(FND_FILE.LOG, g_msg);
1334 -- dbms_output.put_line(g_msg);
1335
1336 g_msg := 'Before get_attribute_id - OLD (Y,Y)-> (Delete,Change)';
1337 fnd_file.put_line(FND_FILE.LOG, g_msg);
1338 -- dbms_output.put_line(g_msg);
1339
1340
1341 get_attribute_id (p_att_table => HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_attributes,
1342 p_bb_id => l_bb_id,
1343 p_last_att_index => t_temp_attr_index,
1344 x_workorder => l_old_workorder,
1345 x_operation => l_old_operation,
1346 x_resource => l_old_resource,
1347 x_charge_department => l_old_charge_department,
1348 x_asset_group_id => l_old_asset_group,
1349 x_owning_department => l_old_owning_department,
1350 x_asset_number => l_old_asset_number);
1351
1352 g_msg := 'After get_attribute_id - OLD (Y,Y)-> (Delete,Change)';
1353 fnd_file.put_line(FND_FILE.LOG, g_msg);
1354 -- dbms_output.put_line(g_msg);
1355
1356
1357 -- t_temp_attr_index := l_last_att_index;
1358 l_old_measure := HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_bld_blks(l_old_count).measure;
1359 l_old_start_time := HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_bld_blks(l_old_count).start_time;
1360 l_old_count := l_old_count + 1;
1361
1362 perform_res_txn (p_wip_entity_id => l_old_workorder,
1363 p_operation_seq_num => l_old_operation,
1364 p_resource_id => l_old_resource,
1365 p_instance_id => l_resource_id,
1366 p_charge_department_id => l_old_charge_department,
1367 p_bb_id => l_bb_id,
1368 p_transaction_qty => -(l_old_measure),
1369 p_start_time => l_old_start_time);
1370
1371 g_msg := 'After Reversing Resource Txn - (Y,Y)-> (Delete,Change)';
1372 fnd_file.put_line(FND_FILE.LOG, g_msg);
1373 -- dbms_output.put_line(g_msg);
1374
1375
1376
1377 end if;
1378
1379
1380 /*******************************************************************************/
1381
1382 -- Record is new but has been deleted.So do not need to perform any transaction
1383 -- only need to set the index for the new attributes table
1384
1385 /*******************************************************************************/
1386
1387
1388 if( l_deleted = 'Y') and (l_changed = 'N') then
1389
1390 g_msg := 'Before get_attribute_id - NEW (Y,N)-> (Delete,Change)';
1391 fnd_file.put_line(FND_FILE.LOG, g_msg);
1392 -- dbms_output.put_line(g_msg);
1393
1394 get_attribute_id (p_att_table => HXC_USER_TYPE_DEFINITION_GRP.t_detail_attributes,
1395 p_bb_id => l_bb_id,
1396 p_last_att_index => l_last_att_index,
1397 x_workorder => l_workorder,
1398 x_operation => l_operation,
1399 x_resource => l_resource,
1400 x_charge_department => l_charge_department,
1401 x_asset_group_id => l_asset_group,
1402 x_owning_department => l_owning_department,
1403 x_asset_number => l_asset_number);
1404
1405 g_msg := 'After get_attribute_id - NEW (Y,N) -> (Delete,Change)';
1406 fnd_file.put_line(FND_FILE.LOG, g_msg);
1407 -- dbms_output.put_line(g_msg);
1408
1409 end if;
1410
1411
1412 /*******************************************************************************/
1413
1414 -- Record has been changed but has not been deleted.So we need to reverse the
1415 -- old resource transaction and create the new resource transaction
1416
1417 -- Bug 3427426
1418 -- Updates to OTL Time cards are not being supported at this time
1419 -- Bug 3753728 -- Corrections allowed for EAM timecard
1420
1421 /*******************************************************************************/
1422
1423 if( l_deleted = 'N') and (l_changed = 'Y') then
1424
1425 -- t_temp_attr_index := l_last_att_index;
1426
1427 g_msg := 'Before get_attribute_id - NEW (N,Y)-> (Delete,Change)';
1428 fnd_file.put_line(FND_FILE.LOG, g_msg);
1429 -- dbms_output.put_line(g_msg);
1430
1431 g_msg := 'Updating OTL Timecard';
1432 fnd_file.put_line(FND_FILE.LOG, g_msg);
1433
1434 get_attribute_id (p_att_table => HXC_USER_TYPE_DEFINITION_GRP.t_detail_attributes,
1435 p_bb_id => l_bb_id,
1436 p_last_att_index => l_last_att_index,
1437 x_workorder => l_workorder,
1438 x_operation => l_operation,
1439 x_resource => l_resource,
1440 x_charge_department => l_charge_department,
1441 x_asset_group_id => l_asset_group,
1442 x_owning_department => l_owning_department,
1443 x_asset_number => l_asset_number);
1444
1445 g_msg := 'After get_attribute_id - NEW (N,Y) -> (Delete,Change)';
1446 fnd_file.put_line(FND_FILE.LOG, g_msg);
1447 -- dbms_output.put_line(g_msg);
1448
1449 g_msg := 'Before get_attribute_id - OLD (N,Y)-> (Delete,Change)';
1450 fnd_file.put_line(FND_FILE.LOG, g_msg);
1451 -- dbms_output.put_line(g_msg);
1452
1453
1454
1455 get_attribute_id (p_att_table => HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_attributes,
1456 p_bb_id => l_bb_id,
1457 p_last_att_index => t_temp_attr_index,
1458 x_workorder => l_old_workorder,
1459 x_operation => l_old_operation,
1460 x_resource => l_old_resource,
1461 x_charge_department => l_old_charge_department,
1462 x_asset_group_id => l_old_asset_group,
1463 x_owning_department => l_old_owning_department,
1464 x_asset_number => l_old_asset_number);
1465
1466 g_msg := 'After get_attribute_id - OLD (N,Y)-> (Delete,Change)';
1467 fnd_file.put_line(FND_FILE.LOG, g_msg);
1468 -- dbms_output.put_line(g_msg);
1469
1470 -- t_temp_attr_index := l_last_att_index;
1471
1472 l_old_measure := HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_bld_blks(l_old_count).measure;
1473 l_old_start_time := HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_bld_blks(l_old_count).start_time;
1474 l_old_count := l_old_count + 1;
1475
1476 perform_res_txn (p_wip_entity_id => l_old_workorder,
1477 p_operation_seq_num => l_old_operation,
1478 p_resource_id => l_old_resource,
1479 p_instance_id => l_resource_id,
1480 p_charge_department_id => l_old_charge_department,
1481 p_bb_id => l_bb_id,
1482 p_transaction_qty => -(l_old_measure),
1483 p_start_time => l_old_start_time);
1484
1485 g_msg := 'After Reversing Resource Txn - (N,Y)-> (Delete,Change)';
1486 fnd_file.put_line(FND_FILE.LOG, g_msg);
1487 -- dbms_output.put_line(g_msg);
1488
1489 perform_res_txn (p_wip_entity_id => l_workorder,
1490 p_operation_seq_num => l_operation,
1491 p_resource_id => l_resource,
1492 p_instance_id => l_resource_id,
1493 p_charge_department_id => l_charge_department,
1494 p_bb_id => l_bb_id,
1495 p_transaction_qty => l_measure,
1496 p_start_time => l_start_time);
1497
1498 g_msg := 'After Resource Txn - (N,Y)-> (Delete,Change)';
1499 fnd_file.put_line(FND_FILE.LOG, g_msg);
1500 -- dbms_output.put_line(g_msg);
1501
1502 end if;
1503
1504
1505 end if;
1506
1507 end;
1508
1509 -- set the transaction status for the block we have processed
1510
1511 /**changed for bug#3949853
1512 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(l_cnt) := 'SUCCESS';
1513 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(l_cnt) := 'Building Block Processed Successfully';
1514
1515 g_msg := 'SUCCESS -- Building Block Processed Successfully';
1516 fnd_file.put_line(FND_FILE.LOG, g_msg);
1517 **/
1518
1519 if g_status <> 'ERRORS' then
1520 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(l_cnt) := 'SUCCESS';
1521 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(l_cnt) := 'Building Block Processed Successfully';
1522 g_msg := 'SUCCESS -- Building Block Processed Successfully';
1523 fnd_file.put_line(FND_FILE.LOG, g_msg);
1524 COMMIT WORK;
1525 else
1526 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(l_cnt) := g_status;
1527 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(l_cnt) := g_exception_description;
1528 g_msg := 'FAILURE -- Building Block Processing Failed';
1529 fnd_file.put_line(FND_FILE.LOG, g_msg);
1530 end if;
1531
1532
1533 end loop;
1534
1535 end if;
1536
1537 -- set overall transaction status
1538
1539 HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
1540
1541 -- tell the generic retrieval to update the transactions statuses
1542
1543 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status
1544 (p_process => 'Maintenance Retrieval Process'
1545 ,p_status => 'SUCCESS'
1546 ,p_exception_description => 'Building Block Processed Successfully'
1547 ,p_rollback => FALSE);
1548
1549 g_msg := 'SUCCESS -- Resource Transaction Completed Successfully';
1550 fnd_file.put_line(FND_FILE.LOG, g_msg);
1551
1552
1553
1554 exception
1555 -- if there was any problem in the recipient application processing then set the overall transaction
1556 -- status to be a failure. If we know what went wrong in more detail then this will be noted in the
1557 -- exception description for the transation
1558
1559 -- utility that propergates the status of processed blocks up the timecard hierarchy. Useful since
1560 -- we only processed the detail blocks
1561
1562 when others then
1563
1564
1565 HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
1566
1567 -- tell the generic retrieval to update the transactions statuses
1568
1569 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status
1570 (p_process => 'Maintenance Retrieval Process'
1571 ,p_status => 'ERRORS'
1572 ,p_exception_description => SUBSTR(SQLERRM,1,200)
1573 ,p_rollback => FALSE);
1574
1575 g_msg := 'FAILURE -- Resource Transaction encountered errors; Please look into hxc_transactions table for details';
1576 fnd_file.put_line(FND_FILE.LOG, g_msg);
1577 /*Added for Bug 7559044*/
1578 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', g_msg); -- Bug 7559044
1579
1580
1581 END retrieve_process;
1582
1583 FUNCTION get_person_id RETURN VARCHAR2 IS
1584
1585 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1586 l_person_id VARCHAR2(30) := '';
1587
1588 BEGIN
1589 l_user_id := FND_GLOBAL.USER_ID;
1590 begin
1591 select to_char(employee_id)
1592 into l_person_id
1593 from fnd_user
1594 where user_id = l_user_id;
1595
1596 exception
1597 when others then
1598 null;
1599
1600 end;
1601
1602 return l_person_id;
1603 END;
1604
1605 FUNCTION get_retrieval_function RETURN VARCHAR2 IS
1606
1607 l_function_name VARCHAR2(50);
1608 BEGIN
1609
1610 l_function_name := 'Maintenance Retrieval Process';
1611
1612
1613 return l_function_name;
1614 END;
1615
1616
1617 procedure validate_work_day
1618 (p_date IN DATE,
1619 p_organization_id IN NUMBER,
1620 x_status OUT NOCOPY NUMBER) IS
1621
1622 l_calendar_code VARCHAR2(40);
1623 l_day_block NUMBER;
1624 l_start_date DATE;
1625 l_end_date DATE;
1626 l_exception_type NUMBER;
1627 rem_days NUMBER;
1628 block_no NUMBER;
1629 l_days_off NUMBER;
1630 l_days_on NUMBER;
1631 l_total_days NUMBER;
1632 l_total NUMBER;
1633 l_stmt_num NUMBER;
1634
1635 CURSOR seq_num_calendar IS
1636 select days_off,days_on, (days_off+days_on) as total_days
1637 from bom_workday_patterns
1638 where calendar_code = l_calendar_code
1639 and shift_num is null
1640 and seq_num is not null
1641 order by seq_num;
1642
1643
1644 BEGIN
1645
1646 select calendar_code
1647 into l_calendar_code
1648 from mtl_parameters
1649 where organization_id = p_organization_id;
1650
1651
1652 select SUM(days_off + days_on)
1653 into l_day_block
1654 from bom_workday_patterns
1655 where calendar_code = l_calendar_code
1656 and shift_num is null
1657 and seq_num is not null
1658 group by calendar_code;
1659
1660
1661 select calendar_start_date,
1662 calendar_end_date
1663 into l_start_date,
1664 l_end_date
1665 from bom_calendars
1666 where calendar_code = l_calendar_code;
1667
1668 begin
1669
1670 select nvl(exception_type,2)
1671 into l_exception_type
1672 from bom_calendar_exceptions
1673 where calendar_code = l_calendar_code
1674 and exception_date = p_date;
1675
1676 if (l_exception_type = 1) then
1677 x_status := 0;
1678 else
1679 x_status := 1;
1680 end if;
1681
1682 exception
1683
1684 when others then
1685 if (p_date <= l_end_date) then
1686
1687 rem_days := MOD((p_date - l_start_date),l_day_block);
1688
1689 end if;
1690
1691 l_total := 0;
1692
1693 OPEN seq_num_calendar;
1694 LOOP
1695 fetch seq_num_calendar into l_days_off, l_days_on, l_total_days;
1696 exit when seq_num_calendar%notfound;
1697
1698 if ((l_total + l_total_days) >= rem_days) then
1699
1700 if ((l_total + l_days_on) >= rem_days) then
1701 x_status := 0;
1702 else
1703 x_status := 1;
1704 end if;
1705
1706 end if;
1707
1708 l_total := l_total + l_total_days;
1709
1710
1711 END LOOP;
1712 CLOSE seq_num_calendar;
1713
1714 end;
1715
1716
1717 END validate_work_day;
1718
1719
1720
1721
1722
1723 procedure validate_process(p_operation IN varchar2) IS
1724
1725 l_blocks HXC_USER_TYPE_DEFINITION_GRP.timecard_info;
1726 l_attributes HXC_USER_TYPE_DEFINITION_GRP.app_attributes_info;
1727 l_messages HXC_USER_TYPE_DEFINITION_GRP.message_table;
1728
1729 begin
1730
1731 ---- get time information
1732 HXC_INTEGRATION_LAYER_V1_GRP.get_app_hook_params(
1733 p_building_blocks => l_blocks,
1734 p_app_attributes => l_attributes,
1735 p_messages => l_messages);
1736
1737 --- EAM will have its own application specific validation
1738 eam_validate_timecard (p_operation => p_operation
1739 ,p_time_building_blocks => l_blocks
1740 ,p_time_attributes => l_attributes
1741 ,p_messages => l_messages );
1742
1743 --- set time information
1744 HXC_INTEGRATION_LAYER_V1_GRP.set_app_hook_params(
1745 p_building_blocks => l_blocks,
1746 p_app_attributes => l_attributes,
1747 p_messages => l_messages);
1748
1749 END validate_process;
1750
1751
1752
1753 procedure eam_validate_timecard( p_operation IN varchar2,
1754 p_time_building_blocks IN HXC_USER_TYPE_DEFINITION_GRP.timecard_info,
1755 p_time_attributes IN HXC_USER_TYPE_DEFINITION_GRP.app_attributes_info,
1756 p_messages IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.message_table)
1757 IS
1758
1759 -- Define the variables that form part of the Deposit Mapping
1760 l_asset_number VARCHAR2(30);
1761 l_asset_group NUMBER;
1762 l_charge_dept NUMBER;
1763 l_dept_id NUMBER;
1764 l_operation_number NUMBER;
1765 l_org_id wip_discrete_jobs.organization_id%type;
1766 l_resource_id NUMBER;
1767 l_wip_entity_id NUMBER;
1768 -- End of deposit mapping variable declarations
1769
1770 -- Other variables
1771 l_job_type NUMBER := 3;
1772 l_count NUMBER :=-1;
1773 l_attrib_count NUMBER :=-1;
1774 l_bb_count NUMBER :=-1;
1775 l_counter NUMBER :=1;
1776 l_time_bb_id NUMBER :=-1;
1777 l_wip_entity_name VARCHAR2(300);
1778 l_asset_group_name VARCHAR2(300);
1779 l_dept_name VARCHAR2(100);
1780 l_charge_dept_name VARCHAR2(100);
1781 l_resource_name VARCHAR2(100);
1782 msg_tkn VARCHAR2(2000) := '';
1783 l_min_acct_period_date DATE := SYSDATE;
1784 l_max_acct_period_date DATE := SYSDATE;
1785 l_wo_released_date DATE := SYSDATE;
1786 l_stop_time DATE := SYSDATE - 100000;
1787 l_start_time DATE := SYSDATE - 100000;
1788 l_exact_start_time DATE := SYSDATE - 100000;
1789 l_current_week_tc HXC_USER_TYPE_DEFINITION_GRP.timecard_info;
1790 l_parent_bb_id NUMBER := -1;
1791 l_return_status NUMBER := 1;
1792
1793 l_eam_card NUMBER :=0;
1794 i NUMBER;
1795 j NUMBER;
1796 k NUMBER;
1797 d NUMBER;
1798
1799 /* OTL has introduced an integrated time card structure, that lets users enter
1800 time for EAM, Projects and Payroll applications on a single layout. This brings about a need to change the validation from EAM side such that it does not fail
1801 when data for Projects/Payroll is also included in the building blocks submitted */
1802
1803 begin
1804
1805 -- Define the current week temporary PL/SQL timecard table and clear it
1806 -- of any previously filled values
1807 if l_current_week_tc.COUNT <> 0 then
1808 l_current_week_tc.DELETE;
1809 end if;
1810
1811 l_counter := 1;
1812
1813 -- Loop through the list of blocks and perform block level
1814 -- validations on each of them
1815
1816 --OTL ELP bug
1817
1818 if (p_time_building_blocks.count <> 0) then
1819
1820 i := p_time_building_blocks.FIRST;
1821
1822 while i is not null loop
1823
1824 l_eam_card := 0;
1825
1826 if p_time_building_blocks(i).date_to > SYSDATE then
1827
1828 l_bb_count := i;
1829 l_time_bb_id := p_time_building_blocks(i).time_building_block_id;
1830
1831 -- loop through the attributes table and find out the attribute records for
1832 -- the current building block.
1833 -- ELP OTL Bug
1834 if (p_time_attributes.count <> 0) then
1835 j := p_time_attributes.first;
1836
1837 LOOP
1838 EXIT when not p_time_attributes.exists(j);
1839
1840 If p_time_attributes(j).Building_Block_Id = P_time_building_Blocks(i).Time_Building_Block_Id Then
1841
1842 -- find out the attribute name and assign the value to the correct variable
1843 if upper(p_time_attributes(j).attribute_name) = 'EAMASSETGROUP' then
1844 l_asset_group := nvl(p_time_attributes(j).attribute_value,-9999);
1845 l_eam_card := 1;
1846 end if;
1847
1848 if upper(p_time_attributes(j).attribute_name) = 'EAMASSETNUMBER' then
1849 l_asset_number := nvl(p_time_attributes(j).attribute_value,' ');
1850 l_eam_card := 1;
1851
1852 if l_asset_number = 'null' then
1853 l_asset_number := ' ';
1854 end if;
1855 end if;
1856
1857 if upper(p_time_attributes(j).attribute_name) = 'EAMCHARGEDEPT' then
1858 l_charge_dept := nvl(p_time_attributes(j).attribute_value,-9999);
1859 l_eam_card := 1;
1860 end if;
1861
1862 if upper(p_time_attributes(j).attribute_name) = 'EAMDEPARTMENTID' then
1863 l_dept_id := nvl(p_time_attributes(j).attribute_value,-9999);
1864 l_eam_card := 1;
1865 end if;
1866
1867 if upper(p_time_attributes(j).attribute_name) = 'EAMOPERATION' then
1868 l_operation_number := nvl(p_time_attributes(j).attribute_value,-9999);
1869 l_eam_card := 1;
1870 end if;
1871
1872 if upper(p_time_attributes(j).attribute_name) = 'EAMORGANIZATIONID' then
1873 l_org_id := nvl(p_time_attributes(j).attribute_value,-9999);
1874 end if;
1875
1876 if upper(p_time_attributes(j).attribute_name) = 'EAMRESOURCE' then
1877 l_resource_id := nvl(p_time_attributes(j).attribute_value,-9999);
1878 l_eam_card := 1;
1879 end if;
1880
1881 if upper(p_time_attributes(j).attribute_name) = 'EAMWORKORDER' then
1882 l_eam_card := 1;
1883 l_wip_entity_id := nvl(p_time_attributes(j).attribute_value,-9999);
1884 end if;
1885
1886 l_attrib_count := j;
1887
1888 end if; -- end of check p_time_attributes(j).Building_Block_Id
1889
1890 j := p_time_attributes.next(j);
1891
1892 end loop;
1893
1894 end if; -- end of check p_time_attributes.count <> 0
1895
1896 -- Get some commonly used variables for later use.
1897
1898 -- If it is a DAY scope BB and is 'near' the SYSDATE, then store it's BB Id,
1899 -- Start and stop times in l_current_week_tc for later use in Validation 10
1900
1901 if p_time_building_blocks(i).scope = 'DAY' and ( p_time_building_blocks(i).start_time > SYSDATE - 10 OR
1902 p_time_building_blocks(i).stop_time < SYSDATE + 10) then
1903 l_current_week_tc(l_counter).time_building_block_id := p_time_building_blocks(i).time_building_block_id;
1904 l_current_week_tc(l_counter).start_time := p_time_building_blocks(i).start_time;
1905 l_current_week_tc(l_counter).stop_time := p_time_building_blocks(i).stop_time;
1906 end if;
1907
1908 l_counter := l_counter + 1;
1909
1910 -- Derive start and end time outside detail block; Change for timekeeper
1911
1912
1913 if p_time_building_blocks(i).scope = 'TIMECARD' then
1914 l_start_time := p_time_building_blocks(i).start_time;
1915 l_stop_time := p_time_building_blocks(i).stop_time;
1916 end if;
1917
1918 if upper(p_time_building_blocks(i).scope) = 'DETAIL' then
1919
1920 -- Deriving attributes only for detail scope building blocks.Code change for timekeeper
1921 -- Added checks for null value
1922
1923 if (l_eam_card = 1) then
1924
1925 if l_wip_entity_id <> -9999 and l_org_id <> -9999 and l_wip_entity_id is not null and l_org_id is not null then
1926 select wip_entity_name into l_wip_entity_name
1927 from wip_entities
1928 where wip_entity_id = l_wip_entity_id
1929 and organization_id = l_org_id;
1930 end if;
1931
1932 if l_org_id <> -9999 and l_asset_group <> -9999 and l_asset_group is not null and l_org_id is not null then
1933 select distinct msik.concatenated_segments into l_asset_group_name
1934 from mtl_system_items_b_kfv msik, mtl_parameters mp
1935 where msik.inventory_item_id = l_asset_group
1936 and mp.maint_organization_id = l_org_id
1937 and mp.organization_id = msik.organization_id;
1938 end if;
1939
1940 if l_dept_id <> -9999 and l_org_id <> -9999 and l_dept_id is not null and l_org_id is not null then
1941 select department_code into l_dept_name from
1942 bom_departments where
1943 department_id = l_dept_id
1944 and organization_id = l_org_id;
1945 end if;
1946
1947
1948 if l_charge_dept <> -9999 and l_org_id <> -9999 and l_charge_dept is not null and l_org_id is not null then
1949 select department_code into l_charge_dept_name from
1950 bom_departments where
1951 department_id = l_charge_dept
1952 and organization_id = l_org_id;
1953 end if;
1954
1955
1956
1957 if l_resource_id <> -9999 and l_org_id <> -9999 and l_resource_id is not null and l_org_id is not null then
1958 select resource_code into l_resource_name from
1959 bom_resources where
1960 resource_id = l_resource_id and
1961 organization_id = l_org_id;
1962 end if;
1963
1964 end if; -- end of check for l_eam_card = 1
1965
1966
1967
1968 -- Perform the block attribute validations one by one
1969
1970 -- 1. Check whether the work order is a maintenance work order or not.
1971
1972 if l_wip_entity_id <> -9999 and l_org_id <> -9999 then
1973 select job_type into l_job_type from wip_discrete_jobs where
1974 wip_entity_id = l_wip_entity_id
1975 and organization_id = l_org_id;
1976 if l_job_type <> 3 then -- not a maintenance work order
1977 -- Add the corresponding error message to the message table
1978 msg_tkn := 'WO_NAME&'||l_wip_entity_name;
1979 Add_Error_To_Table( p_message_table => p_messages ,
1980 p_message_name => 'EAM_OTL_NOT_MNT_WO',
1981 p_message_token => msg_tkn,
1982 P_Message_Level => 'ERROR',
1983 P_Message_Field => NULL,
1984 p_application_short_name => 'EAM',
1985 P_Timecard_bb_Id =>
1986 P_time_Building_Blocks(i).Time_Building_Block_Id,
1987 P_Time_Attribute_Id => l_attrib_count);
1988 msg_tkn := '';
1989 end if;
1990 end if;
1991
1992 -- 2. Asset group - work order association is correct.
1993 if l_wip_entity_id <> -9999 and l_org_id <> -9999 and l_asset_group <> -9999 then
1994 -- bug 4146481. added NVL in query
1995 select count(*) into l_count from wip_discrete_jobs where
1996 wip_entity_id = l_wip_entity_id and
1997 organization_id = l_org_id and
1998 nvl(asset_group_id,rebuild_item_id) = l_asset_group;
1999 if l_count = 0 then
2000 -- Add the corresponding error message to the message table
2001 msg_tkn := 'WO_NAME&'||l_wip_entity_name||'&' || 'AG_NAME&'||l_asset_group_name;
2002 Add_Error_To_Table( p_message_table => p_messages ,
2003 p_message_name => 'EAM_OTL_AG_WO_INCORRECT',
2004 p_message_token => msg_tkn,
2005 P_Message_Level => 'ERROR',
2006 P_Message_Field => NULL,
2007 p_application_short_name => 'EAM',
2008 P_Timecard_bb_Id =>
2009 P_time_Building_Blocks(i).Time_Building_Block_Id,
2010 P_Time_Attribute_Id => l_attrib_count);
2011 msg_tkn := '';
2012 end if;
2013 end if;
2014
2015 -- 3. Asset number belongs to the correct asset group.
2016 if l_asset_number <> ' ' and l_asset_group <> -9999 then
2017 -- Changed as part of CAR impact
2018 select count(*) into l_count from csi_item_instances where
2019 serial_number = l_asset_number and
2020 inventory_item_id = l_asset_group;
2021 if l_count = 0 then
2022 -- Add the corresponding error message to the message table
2023 msg_tkn := 'AN_NAME&'||l_asset_number||'&' || 'AG_NAME&'||l_asset_group_name;
2024 Add_Error_To_Table( p_message_table => p_messages ,
2025 p_message_name => 'EAM_OTL_AG_AN_INCORRECT',
2026 p_message_token => msg_tkn,
2027 P_Message_Level => 'ERROR',
2028 P_Message_Field => NULL,
2029 p_application_short_name => 'EAM',
2030 P_Timecard_bb_Id =>
2031 P_time_Building_Blocks(i).Time_Building_Block_Id,
2032 P_Time_Attribute_Id => l_attrib_count);
2033 msg_tkn := '';
2034 end if;
2035 end if;
2036
2037 -- 4. asset number and work order association is correct.
2038 if l_asset_number <> ' ' and l_org_id <> -9999 and l_wip_entity_id <> -9999 then
2039 -- bug 4146481. added NVL in query
2040 select count(*) into l_count from wip_discrete_jobs where
2041 nvl(asset_number,rebuild_serial_number) = l_asset_number and
2042 wip_entity_id = l_wip_entity_id and
2043 organization_id = l_org_id;
2044 if l_count = 0 then
2045 -- Add the corresponding error message to the message table
2046 msg_tkn := 'WO_NAME&'||l_wip_entity_name||'&' ||'AN_NAME&'||l_asset_number;
2047 Add_Error_To_Table( p_message_table => p_messages ,
2048 p_message_name => 'EAM_OTL_AN_WO_INCORRECT',
2049 p_message_token => msg_tkn,
2050 P_Message_Level => 'ERROR',
2051 P_Message_Field => NULL,
2052 p_application_short_name => 'EAM',
2053 P_Timecard_bb_Id =>
2054 P_time_Building_Blocks(i).Time_Building_Block_Id,
2055 P_Time_Attribute_Id => l_attrib_count);
2056 msg_tkn := '';
2057 end if;
2058 end if;
2059
2060 -- 5. operations do belong to the work order
2061 if l_wip_entity_id <> -9999 and l_org_id <> -9999 and l_operation_number <> -9999 then
2062 select count(*) into l_count from wip_operations where
2063 wip_entity_id = l_wip_entity_id and
2064 organization_id = l_org_id and
2065 operation_seq_num = l_operation_number;
2066 if l_count = 0 then
2067 -- Add the corresponding error message to the message table
2068 msg_tkn := 'OP_NAME&'||l_operation_number||'&' ||'WO_NAME&'||l_wip_entity_name;
2069 Add_Error_To_Table( p_message_table => p_messages ,
2070 p_message_name => 'EAM_OTL_OP_WO_INCORRECT',
2071 p_message_token => msg_tkn,
2072 P_Message_Level => 'ERROR',
2073 P_Message_Field => NULL,
2074 p_application_short_name => 'EAM',
2075 P_Timecard_bb_Id =>
2076 P_time_Building_Blocks(i).Time_Building_Block_Id,
2077 P_Time_Attribute_Id => l_attrib_count);
2078 msg_tkn := '';
2079 end if;
2080 end if;
2081
2082 -- 6. Check whether department and operation association is correct.
2083 if l_wip_entity_id <> -9999 and l_org_id <> -9999 and l_operation_number <> -9999
2084 and l_dept_id <> -9999 then
2085 select count(*) into l_count from wip_operations where
2086 wip_entity_id = l_wip_entity_id and
2087 operation_seq_num = l_operation_number and
2088 organization_id = l_org_id and
2089 department_id = l_dept_id;
2090 if l_count = 0 then
2091 -- Add the corresponding error message to the message table
2092 msg_tkn := 'OP_NAME&'||l_operation_number||'&' ||'DP_NAME&'||l_dept_name;
2093 Add_Error_To_Table( p_message_table => p_messages ,
2094 p_message_name => 'EAM_OTL_OP_DEPT_INCORRECT',
2095 p_message_token => msg_tkn,
2096 P_Message_Level => 'ERROR',
2097 P_Message_Field => NULL,
2098 p_application_short_name => 'EAM',
2099 P_Timecard_bb_Id =>
2100 P_time_Building_Blocks(i).Time_Building_Block_Id,
2101 P_Time_Attribute_Id => l_attrib_count);
2102 msg_tkn := '';
2103 end if;
2104 end if;
2105
2106 -- 7. Check whether the resources belong to the correct department and
2107 -- are either assigned or shared resources for the department
2108 if l_resource_id <> -9999 and l_dept_id <> -9999 then
2109 select count(*) into l_count from (select br.resource_code,
2110 br.description,
2111 br.resource_type,
2112 br.functional_currency_flag,
2113 2 autocharge_type,
2114 br.unit_of_measure uom_code,
2115 br.unit_of_measure uom,
2116 br.default_basis_type basis_type, ca.activity_id,
2117 ca.activity,
2118 br.standard_rate_flag,
2119 br.organization_id,
2120 to_char(bdr.department_id) as department_id,
2121 (select meaning from mfg_lookups m1 where m1.lookup_type like 'BOM_RESOURCE_TYPE' and m1.lookup_code = 2) meaning,
2122 to_char(br.resource_id) as res_id
2123 from cst_activities ca,
2124 bom_department_resources bdr,
2125 bom_resources br
2126 where br.resource_id = bdr.resource_id and
2127 br.default_activity_id = ca.activity_id (+) and
2128 nvl(ca.disable_date(+),sysdate+1) > sysdate and
2129 nvl(br.disable_date,sysdate+1) > sysdate and (ca.organization_id is
2130 null or ca.organization_id = br.organization_id)
2131 order by br.resource_code
2132 )
2133 where res_id = l_resource_id
2134 and department_id = l_dept_id;
2135 if l_count = 0 then
2136 -- Add the corresponding error message to the message table
2137 msg_tkn := 'RS_NAME&'||l_resource_name||'&' ||'DP_NAME&'||l_dept_name;
2138 Add_Error_To_Table( p_message_table => p_messages ,
2139 p_message_name => 'EAM_OTL_RES_DEPT_INCORRECT',
2140 p_message_token => msg_tkn,
2141 P_Message_Level => 'ERROR',
2142 P_Message_Field => NULL,
2143 p_application_short_name => 'EAM',
2144 P_Timecard_bb_Id =>
2145 P_time_Building_Blocks(i).Time_Building_Block_Id,
2146 P_Time_Attribute_Id => l_attrib_count);
2147 msg_tkn := '';
2148 end if;
2149 end if;
2150
2151 -- 8. Charge department is correct for the resource, where in the charge
2152 -- department is the one where the resource is either owned or shared.
2153 -- Added union clause to take care of the shared resources too - Bug 3873717
2154 if l_charge_dept <> -9999 and l_resource_id <> -9999 then
2155 select count(*) into l_count from (select distinct bd.department_code,
2156 bd.organization_id,
2157 bd.description,
2158 to_char(bdri.department_id) as department_id,
2159 to_char(ppf.person_id) as person_id,
2160 bdri.resource_id as resource_id
2161 from bom_dept_res_instances bdri,
2162 bom_departments bd,
2163 bom_resource_employees bre,
2164 per_people_f ppf
2165 where bdri.instance_id = bre.instance_id
2166 and ppf.person_id = bre.person_id
2167 and bd.department_id = bdri.department_id
2168 and sysdate >= ppf.effective_start_date
2169 and sysdate <= ppf.effective_end_date
2170 union
2171 select distinct bd.department_code,
2172 bd.organization_id,
2173 bd.description,
2174 to_char(bdr.department_id) as department_id,
2175 to_char(ppf.person_id) as person_id,
2176 bdri.resource_id as resource_id
2177 from bom_dept_res_instances bdri,
2178 bom_departments bd,
2179 bom_resource_employees bre,
2180 bom_department_resources bdr,
2181 per_people_f ppf
2182 where bdri.instance_id = bre.instance_id
2183 and ppf.person_id = bre.person_id
2184 and sysdate >= ppf.effective_start_date and sysdate <= ppf.effective_end_date
2185 and bd.department_id = bdr.department_id
2186 and bdr.share_from_dept_id = bdri.department_id
2187 and bdr.resource_id = bdri.resource_id
2188 )
2189
2190 where department_id = l_charge_dept
2191 and resource_id = l_resource_id;
2192 if l_count = 0 then
2193 -- Add the corresponding error message to the message table
2194 msg_tkn := 'CH_DEPT&'||l_charge_dept_name||'&' ||'RS_NAME&'||l_resource_name;
2195 Add_Error_To_Table( p_message_table => p_messages ,
2196 p_message_name => 'EAM_OTL_CHRG_DEPT_INCORRECT',
2197 p_message_token => msg_tkn,
2198 P_Message_Level => 'ERROR',
2199 P_Message_Field => NULL,
2200 p_application_short_name => 'EAM',
2201 P_Timecard_bb_Id =>
2202 P_time_Building_Blocks(i).Time_Building_Block_Id,
2203 P_Time_Attribute_Id => l_attrib_count);
2204 msg_tkn := '';
2205 end if;
2206 end if;
2207
2208 if p_time_building_blocks(i).scope = 'TIMECARD' then
2209 l_start_time := p_time_building_blocks(i).start_time;
2210 l_stop_time := p_time_building_blocks(i).stop_time;
2211 end if;
2212
2213
2214 -- 9. Check whether the start and end dates are within the accounting
2215 -- periods or not
2216 if l_start_time <> SYSDATE - 100000 and
2217 l_stop_time <> SYSDATE - 100000 and
2218 l_org_id <> -9999 and
2219 p_time_building_blocks(i).scope = 'TIMECARD' then
2220 select nvl(min(period_start_date), (sysdate - 200000)),
2221 nvl(max(schedule_close_date),(sysdate + 200000))
2222 into l_min_acct_period_date,l_max_acct_period_date
2223 from org_acct_periods
2224 where organization_id = l_org_id
2225 and upper(open_flag) = 'Y';
2226 if l_start_time <= l_min_acct_period_date OR
2227 l_stop_time >= l_max_acct_period_date then
2228 -- Add the corresponding error message to the message table
2229 msg_tkn := 'ST_TIME&'||l_start_time||'&' || 'EN_TIME&'||l_stop_time;
2230 Add_Error_To_Table( p_message_table => p_messages ,
2231 p_message_name => 'EAM_OTL_NOT_IN_ACCT_PER',
2232 p_message_token => msg_tkn,
2233 P_Message_Level => 'ERROR',
2234 P_Message_Field => NULL,
2235 p_application_short_name => 'EAM',
2236 P_Timecard_bb_Id =>
2237 P_time_Building_Blocks(i).Time_Building_Block_Id,
2238 P_Time_Attribute_Id => l_attrib_count);
2239 msg_tkn := '';
2240 end if;
2241 end if;
2242
2243 -- Code changes for supporting Timekeeper functionality;
2244 -- Robust check on dates
2245
2246 -- 10. Check whether the start date and end date are less than the
2247 -- sysdate for a day scope building block. Also checking if the
2248 -- start time is less than released date of the workorder
2249 if upper(p_time_building_blocks(i).scope) = 'DETAIL' and
2250 p_time_building_blocks(i).parent_building_block_id is not null then
2251
2252 l_parent_bb_id := p_time_building_blocks(i).parent_building_block_id;
2253 -- Loop through the l_current_week_tc to find the parent BB and
2254 -- subsequently the parent's start time and stop times
2255
2256 d := 0;
2257
2258 k:= l_current_week_tc.FIRST;
2259
2260 LOOP
2261 EXIT when not l_current_week_tc.exists(k);
2262
2263
2264 if l_current_week_tc(k).time_building_block_id = l_parent_bb_id then
2265
2266 d := 1;
2267 l_exact_start_time := p_time_building_blocks(i).start_time;
2268 if(l_exact_start_time is null) then
2269 l_exact_start_time := l_current_week_tc(k).start_time;
2270 end if;
2271
2272
2273 if l_current_week_tc(k).start_time >= SYSDATE then
2274
2275 msg_tkn := 'ST_TIME&'||l_current_week_tc(k).start_time;
2276 Add_Error_To_Table( p_message_table => p_messages ,
2277 p_message_name => 'EAM_OTL_FUTURE_DATE_ERR',
2278 p_message_token => msg_tkn,
2279 P_Message_Level => 'ERROR',
2280 P_Message_Field => NULL,
2281 p_application_short_name => 'EAM',
2282 P_Timecard_bb_Id =>
2283 P_time_Building_Blocks(i).Time_Building_Block_Id,
2284 P_Time_Attribute_Id => l_attrib_count);
2285 msg_tkn := '';
2286
2287 end if;
2288 select date_released into l_wo_released_date
2289 from wip_discrete_jobs
2290 where wip_entity_id = l_wip_entity_id
2291 and organization_id = l_org_id;
2292
2293
2294 if l_exact_start_time < l_wo_released_date then
2295
2296 msg_tkn := 'ST_TIME&'||l_exact_start_time;
2297 Add_Error_To_Table( p_message_table => p_messages ,
2298 p_message_name => 'EAM_OTL_DATE_LT_REL_ERR',
2299 p_message_token => msg_tkn,
2300 P_Message_Level => 'ERROR',
2301 P_Message_Field => NULL,
2302 p_application_short_name => 'EAM',
2303 P_Timecard_bb_Id =>
2304 P_time_Building_Blocks(i).Time_Building_Block_Id,
2305 P_Time_Attribute_Id => l_attrib_count);
2306 msg_tkn := '';
2307
2308 end if;
2309
2310
2311 end if; -- end of check for time building block id
2312
2313
2314 EXIT when d =1;
2315 k := l_current_week_tc.next(k);
2316
2317 END LOOP;
2318
2319 end if;
2320
2321 -- 11. Check whether the user has entered atleast a workorder, resource
2322 -- or operation
2323 if upper(p_time_building_blocks(i).scope) = 'DETAIL' and
2324 l_wip_entity_id = -9999 and l_resource_id = -9999 and
2325 l_operation_number = -9999 then
2326 Add_Error_To_Table( p_message_table => p_messages ,
2327 p_message_name => 'EAM_OTL_INSUFFICIENT_INFO',
2328 p_message_token => msg_tkn,
2329 P_Message_Level => 'ERROR',
2330 P_Message_Field => NULL,
2331 p_application_short_name => 'EAM',
2332 P_Timecard_bb_Id =>
2333 P_time_Building_Blocks(i).Time_Building_Block_Id,
2334 P_Time_Attribute_Id => l_attrib_count);
2335 end if;
2336
2337 /* As per decision of upper management, it was decided not to
2338 check for the dates being in MFG holiday. Hence commenting
2339 out this validation.
2340 -- 12. Check whether the day is in a MFG calendar work day or not
2341
2342 if upper(p_time_building_blocks(i).scope) = 'DETAIL' and
2343 p_time_building_blocks(i).parent_building_block_id is not null then
2344
2345 l_parent_bb_id := p_time_building_blocks(i).parent_building_block_id;
2346 -- Loop through the l_current_week_tc to find the parent BB and
2347 -- subsequently the parent's start time and stop times
2348 for k in l_current_week_tc.FIRST .. l_current_week_tc.last loop
2349 if l_current_week_tc(k).time_building_block_id = l_parent_bb_id then
2350 validate_work_day(p_date => l_current_week_tc(k).start_time,
2351 p_organization_id => l_org_id,
2352 x_status => l_return_status);
2353 if l_return_status = 1 then
2354 msg_tkn := 'ST_TIME&'||l_current_week_tc(k).start_time;
2355 Add_Error_To_Table( p_message_table => p_messages ,
2356 p_message_name => 'EAM_OTL_MFG_HOLIDAY_ERR',
2357 p_message_token => msg_tkn,
2358 P_Message_Level => 'ERROR',
2359 P_Message_Field => NULL,
2360 p_application_short_name => 'EAM',
2361 P_Timecard_bb_Id =>
2362 P_time_Building_Blocks(i).Time_Building_Block_Id,
2363 P_Time_Attribute_Id => l_attrib_count);
2364 msg_tkn := '';
2365 end if;
2366 end if;
2367 end loop;
2368 end if;
2369 */ --End of comment for validation 12.
2370
2371 end if; -- End of check attributes for detail time building blocks
2372
2373 end if ;
2374
2375 i:= p_time_building_blocks.next(i);
2376
2377 end loop; -- end of check for : i is not null loop
2378
2379 end if; -- end of check for p_time_building_blocks.count <> 0
2380
2381 -- end of time bldg blk processing loop
2382
2383 EXCEPTION
2384 WHEN OTHERS THEN
2385 Add_Error_To_Table( p_message_table => p_messages ,
2386 p_message_name => 'EAM_OTL_GEN_ERR',
2387 p_message_token => msg_tkn,
2388 P_Message_Level => 'ERROR',
2389 P_Message_Field => NULL,
2390 p_application_short_name => 'EAM',
2391 P_Timecard_bb_Id => l_time_bb_id,
2392 P_Time_Attribute_Id => l_attrib_count);
2393
2394 END eam_validate_timecard;
2395
2396
2397
2398
2399 -- public procedure
2400 -- add_error_to_table
2401 --
2402 -- description
2403 -- adds error to the TCO message stack
2404 PROCEDURE add_error_to_table (
2405 p_message_table IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.MESSAGE_TABLE
2406 ,p_message_name IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
2407 ,p_message_token IN VARCHAR2
2408 ,p_message_level IN VARCHAR2
2409 ,p_message_field IN VARCHAR2
2410 ,p_application_short_name IN VARCHAR2 -- default 'EAM'
2411 ,p_timecard_bb_id IN NUMBER
2412 ,p_time_attribute_id IN NUMBER) is
2413
2414 l_last_index BINARY_INTEGER;
2415
2416 BEGIN
2417
2418 l_last_index := NVL(p_message_table.last,0);
2419
2420 p_message_table(l_last_index+1).message_name := p_message_name;
2421 p_message_table(l_last_index+1).message_level := p_message_level;
2422 p_message_table(l_last_index+1).message_field := p_message_field;
2423 p_message_table(l_last_index+1).message_tokens:= p_message_token;
2424 p_message_table(l_last_index+1).application_short_name := p_application_short_name;
2425 p_message_table(l_last_index+1).time_building_block_id := p_timecard_bb_id;
2426 p_message_table(l_last_index+1).time_attribute_id := p_time_attribute_id;
2427
2428 END add_error_to_table;
2429
2430
2431
2432 END EAM_OTL_TIMECARD_PUB; -- package body