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