1 PACKAGE BODY WIP_SFCB_UTILITIES AS
2 /* $Header: wipsfcbb.pls 120.1 2006/05/18 12:44:19 shkalyan noship $ */
3
4 /* Private Global variables for Linearity */
5
6 g_Linearity_Date_From DATE ;
7 g_Linearity_Date_To DATE ;
8 g_Linearity_Line NUMBER ;
9 g_Linearity_Org NUMBER ;
10
11 g_userid NUMBER;
12 g_applicationid NUMBER;
13 g_debug NUMBER := 0 ;
14 g_uom_code VARCHAR2(10);
15
16 /* Wip Contants for identifying the type */
17 WIP_LINEARITY CONSTANT INTEGER := 6 ;
18 WIP_LINE_LOAD CONSTANT INTEGER := 7 ;
19 WIP_LINE_RL CONSTANT INTEGER := 8 ;
20
21 /*Wip Process Phase Constants */
22 WIP_LINEARITY_PHASE_ONE CONSTANT INTEGER := 1 ;
23 WIP_LINEARITY_PHASE_TWO CONSTANT INTEGER := 2 ;
24 WIP_LINE_LOAD_PHASE_ONE CONSTANT INTEGER := 1 ;
25 WIP_LINE_LOAD_PHASE_TWO CONSTANT INTEGER := 2 ;
26 WIP_LINE_RL_PHASE_ONE CONSTANT INTEGER := 1 ;
27 WIP_LINE_RL_PHASE_TWO CONSTANT INTEGER := 2 ;
28 WIP_LINE_RL_PHASE_THREE CONSTANT INTEGER := 3 ;
29
30 /* *************************************************************
31 Cursor to get all unique department, resource combination
32 *************************************************************/
33 CURSOR Dept_Res(
34 p_org_id number,
35 p_res_id number) is
36 SELECT distinct
37 organization_id,
38 resource_id,
39 department_id
40 FROM bom_department_resources_v
41 WHERE organization_id = p_org_id
42 AND resource_id = p_res_id ;
43
44
45
46 Procedure Update_Group_Id(
47 p_temp_group_id NUMBER,
48 p_main_group_id NUMBER );
49
50
51 /*
52 Procedure that populates the efficiency information into
53 the temp table
54 */
55
56 PROCEDURE Populate_Efficiency(
57 p_group_id IN NUMBER,
58 p_organization_id IN NUMBER,
59 p_date_from IN DATE,
60 p_date_to IN DATE,
61 p_department_id IN NUMBER,
62 p_resource_id IN NUMBER,
63 p_userid IN NUMBER,
64 p_applicationid IN NUMBER,
65 p_errnum OUT NOCOPY NUMBER,
66 p_errmesg OUT NOCOPY VARCHAR2)
67
68 IS
69 x_main_group_id NUMBER ;
70 x_phase VARCHAR2(10) ;
71 x_temp_group_id NUMBER;
72 BEGIN
73
74
75 x_phase := 'I' ;
76 If p_organization_id is null then
77 p_errnum := -1 ;
78 p_errmesg := 'Failed in Efficiency Phase : '||x_phase||' Organization Id is NULL' ;
79 return ;
80 End if ;
81
82 x_phase := 'II' ;
83 If p_resource_id is null AND p_department_id IS NULL then
84 p_errnum := -1 ;
85 p_errmesg := 'Failed in Efficiency Phase : '||x_phase||
86 ' Resource Id is NULL and department_id is NULL' ;
87 return ;
88 End if ;
89
90
91 IF p_group_id IS NULL THEN
92 select wip_indicators_temp_s.nextval into x_main_group_id
93 from sys.dual ;
94 ELSE
95 x_main_group_id := p_group_id ;
96 END IF;
97
98
99
100 x_phase := 'III' ;
101 IF p_resource_id IS NOT NULL THEN
102
103 FOR Dept_Res_Rec IN Dept_Res(
104 p_organization_id,
105 p_resource_id) LOOP
106
107 -- Generate the new Sequence for this
108 begin
109 select wip_indicators_temp_s.nextval into x_temp_group_id
110 from sys.dual ;
111 exception
112 when others then
113 x_phase := 'IV';
114 p_errnum := -1 ;
115 p_errmesg := 'Failed in Efficiency Phase : '||x_phase|| substr(SQLERRM,1,125);
116 return ;
117 end ;
118
119 WIP_PROD_INDICATORS.Populate_Efficiency(
120 p_group_id => x_temp_group_id,
121 p_organization_id => p_organization_id,
122 p_date_from => p_date_from,
123 p_date_to => p_date_to,
124 p_department_id => Dept_Res_Rec.department_id,
125 p_resource_id => p_resource_id,
126 p_userid => p_userid,
127 p_applicationid => p_applicationid,
128 p_errnum => p_errnum,
129 p_errmesg => p_errmesg );
130
131
132 Update_Group_Id(
133 p_temp_group_id => x_temp_group_id,
134 p_main_group_id => x_main_group_id);
135
136
137 END LOOP ;
138
139 /* The new section added for the department production indicators
140 */
141 ELSIF p_department_id IS NOT NULL AND p_resource_id IS NULL THEN
142
143 WIP_PROD_INDICATORS.Populate_Efficiency(
144 p_group_id => x_main_group_id,
145 p_organization_id => p_organization_id,
146 p_date_from => p_date_from,
147 p_date_to => p_date_to,
148 p_department_id => p_department_id,
149 p_resource_id => p_resource_id,
150 p_userid => p_userid,
151 p_applicationid => p_applicationid,
152 p_errnum => p_errnum,
153 p_errmesg => p_errmesg );
154
155
156 END IF ;
157 p_errnum := 1 ;
158 p_errmesg := null ;
159 return ;
160
161
162 Exception
163
164 when others then
165
166 p_errnum := -1 ;
167 p_errmesg := 'Failed in Efficiency Phase : '||x_phase|| substr(SQLERRM,1,125);
168 return ;
169
170 END Populate_Efficiency;
171
172
173
174 PROCEDURE Populate_Utilization (
175 p_group_id IN NUMBER,
176 p_organization_id IN NUMBER,
177 p_date_from IN DATE,
178 p_date_to IN DATE,
179 p_department_id IN NUMBER,
180 p_resource_id IN NUMBER,
181 p_userid IN NUMBER,
182 p_applicationid IN NUMBER,
183 p_errnum OUT NOCOPY NUMBER,
184 p_errmesg OUT NOCOPY VARCHAR2)
185 IS
186 x_main_group_id NUMBER ;
187 x_phase VARCHAR2(10) ;
188 x_temp_group_id NUMBER;
189 BEGIN
190
191
192 x_phase := 'I' ;
193 If p_organization_id is null then
194 p_errnum := -1 ;
195 p_errmesg := 'Failed in Utilization Phase : '||x_phase||' Organization Id is NULL' ;
196 return ;
197 End if ;
198
199 x_phase := 'II' ;
200 If p_resource_id is null AND p_department_id IS NULL then
201 p_errnum := -1 ;
202 p_errmesg := 'Failed in Utilization Phase : '||x_phase||
203 ' Resource Id is NULL and department_id is NULL' ;
204 return ;
205 End if ;
206
207
208 IF p_group_id IS NULL THEN
209 select wip_indicators_temp_s.nextval into x_main_group_id
210 from sys.dual ;
211 ELSE
212 x_main_group_id := p_group_id ;
213 END IF;
214
215
216
217 x_phase := 'III' ;
218
219 IF p_resource_id IS NOT NULL then
220
221 FOR Dept_Res_Rec IN Dept_Res(
222 p_organization_id,
223 p_resource_id) LOOP
224
225 -- Generate the new Sequence for this
226 begin
227 select wip_indicators_temp_s.nextval into x_temp_group_id
228 from sys.dual ;
229 exception
230 when others then
231 x_phase := 'IV';
232 p_errnum := -1 ;
233 p_errmesg := 'Failed in Utilization Phase : '||x_phase|| substr(SQLERRM,1,125);
234 return ;
235 end ;
236
237 WIP_PROD_INDICATORS.Populate_Utilization(
238 p_group_id => x_temp_group_id,
239 p_organization_id => p_organization_id,
240 p_date_from => p_date_from,
241 p_date_to => p_date_to,
242 p_department_id => Dept_Res_Rec.department_id,
243 p_resource_id => p_resource_id,
244 p_userid => p_userid,
245 p_applicationid => p_applicationid,
246 p_errnum => p_errnum,
247 p_errmesg => p_errmesg,
248 p_sfcb => 1 );
249
250
251 Update_Group_Id(
252 p_temp_group_id => x_temp_group_id,
253 p_main_group_id => x_main_group_id);
254
255
256 END LOOP ;
257
258
259 ELSIF p_department_id IS NOT NULL AND p_resource_id IS NULL then
260
261
262 WIP_PROD_INDICATORS.Populate_Utilization(
263 p_group_id => x_main_group_id,
264 p_organization_id => p_organization_id,
265 p_date_from => p_date_from,
266 p_date_to => p_date_to,
267 p_department_id => p_department_id,
268 p_resource_id => p_resource_id,
269 p_userid => p_userid,
270 p_applicationid => p_applicationid,
271 p_errnum => p_errnum,
272 p_errmesg => p_errmesg,
273 p_sfcb => 1 );
274
275
276 END IF ;
277 p_errnum := 1 ;
278 p_errmesg := null ;
279 return ;
280
281
282 Exception
283
284 when others then
285
286 p_errnum := -1 ;
287 p_errmesg := 'Failed in Utilization Phase : '||x_phase|| substr(SQLERRM,1,125);
288 return ;
289
290 END Populate_Utilization;
291
292
293 PROCEDURE Populate_Productivity(
294 p_group_id IN NUMBER,
295 p_organization_id IN NUMBER,
296 p_date_from IN DATE,
297 p_date_to IN DATE,
298 p_department_id IN NUMBER,
299 p_resource_id IN NUMBER,
300 p_userid IN NUMBER,
301 p_applicationid IN NUMBER,
302 p_errnum OUT NOCOPY NUMBER,
303 p_errmesg OUT NOCOPY VARCHAR2)
304
305 IS
306 x_main_group_id NUMBER ;
307 x_phase VARCHAR2(10) ;
308 x_temp_group_id NUMBER;
309 BEGIN
310
311
312 x_phase := 'I' ;
313 If p_organization_id is null then
314 p_errnum := -1 ;
315 p_errmesg := 'Failed in Productivity Phase : '||x_phase||' Organization Id is NULL' ;
316 return ;
317 End if ;
318
319 x_phase := 'II' ;
320 If p_resource_id is null AND p_department_id IS NULL then
321 p_errnum := -1 ;
322 p_errmesg := 'Failed in Productivity Phase : '||x_phase||
323 ' Resource Id is NULL and department_id is NULL' ;
324 return ;
325 End if ;
326
327
328
329 IF p_group_id IS NULL THEN
330 select wip_indicators_temp_s.nextval into x_main_group_id
331 from sys.dual ;
332 ELSE
333 x_main_group_id := p_group_id ;
334 END IF;
335
336
337
338 x_phase := 'III' ;
339 IF p_resource_id IS NOT NULL then
340
341 FOR Dept_Res_Rec IN Dept_Res(
342 p_organization_id,
343 p_resource_id) LOOP
344
345 -- Generate the new Sequence for this
346 begin
347 select wip_indicators_temp_s.nextval into x_temp_group_id
348 from sys.dual ;
349 exception
350 when others then
351 x_phase := 'IV';
352 p_errnum := -1 ;
353 p_errmesg := 'Failed in Productivity Phase : '||x_phase|| substr(SQLERRM,1,125);
354 return ;
355 end ;
356
357 WIP_PROD_INDICATORS.Populate_Productivity(
358 p_group_id => x_temp_group_id,
359 p_organization_id => p_organization_id,
360 p_date_from => p_date_from,
361 p_date_to => p_date_to,
362 p_department_id => Dept_Res_Rec.department_id,
363 p_resource_id => p_resource_id,
364 p_userid => p_userid,
365 p_applicationid => p_applicationid,
366 p_errnum => p_errnum,
367 p_errmesg => p_errmesg) ;
368
369
370 Update_Group_Id(
371 p_temp_group_id => x_temp_group_id,
372 p_main_group_id => x_main_group_id);
373
374
375 END LOOP ;
376
377
378 ELSIF p_department_id IS NOT NULL AND p_resource_id IS NULL THEN
379
380 WIP_PROD_INDICATORS.Populate_Productivity(
381 p_group_id => x_main_group_id,
382 p_organization_id => p_organization_id,
383 p_date_from => p_date_from,
384 p_date_to => p_date_to,
385 p_department_id => p_department_id,
386 p_resource_id => p_resource_id,
387 p_userid => p_userid,
388 p_applicationid => p_applicationid,
389 p_errnum => p_errnum,
390 p_errmesg => p_errmesg) ;
391
392
393 END IF ;
394 p_errnum := 1 ;
395 p_errmesg := null ;
396 return ;
397
398
399 Exception
400
401 when others then
402
403 p_errnum := -1 ;
404 p_errmesg := 'Failed in Productivity Phase : '||x_phase|| substr(SQLERRM,1,125);
405 return ;
406
407 END Populate_Productivity;
408
409
410
411 PROCEDURE Populate_Resource_Load (
412 p_group_id IN NUMBER,
413 p_organization_id IN NUMBER,
414 p_date_from IN DATE,
415 p_date_to IN DATE,
416 p_department_id IN NUMBER,
417 p_resource_id IN NUMBER,
418 p_userid IN NUMBER,
419 p_applicationid IN NUMBER,
420 p_errnum OUT NOCOPY NUMBER,
421 p_errmesg OUT NOCOPY VARCHAR2)
422 IS
423 x_main_group_id NUMBER ;
424 x_phase VARCHAR2(10) ;
425 x_temp_group_id NUMBER;
426 BEGIN
427
428
429 /************************************************
430 * Check for the set of required parameters
431 * 1. Organization Id
432 * 2. Resource ID
433 *************************************************/
434
435 x_phase := 'I' ;
436 If p_organization_id is null then
437 p_errnum := -1 ;
438 p_errmesg := 'Failed in Resource Load Phase : '||x_phase||' Organization Id is NULL' ;
439 return ;
440 End if ;
441
442 x_phase := 'II' ;
443 If p_resource_id is null AND p_department_id IS NULL then
444 p_errnum := -1 ;
445 p_errmesg := 'Failed in Productivity Phase : '||x_phase||
446 ' Resource Id is NULL and department_id is NULL' ;
447 return ;
448 End if ;
449
450
451
452 /***********************************************
453 * If the Group ID is null then we would generate
454 * a new group id from the sequence
455 ***********************************************/
456
457
458 IF p_group_id IS NULL THEN
459 select wip_indicators_temp_s.nextval into x_main_group_id
460 from sys.dual ;
461 ELSE
462 x_main_group_id := p_group_id ;
463 END IF;
464
465
466
467 x_phase := 'III' ;
468 IF p_resource_id IS NOT NULL then
469
470 FOR Dept_Res_Rec IN Dept_Res(
471 p_organization_id,
472 p_resource_id) LOOP
473
474 -- Generate the new Sequence for this
475 begin
476 select wip_indicators_temp_s.nextval into x_temp_group_id
477 from sys.dual ;
478 exception
479 when others then
480 x_phase := 'IV';
481 p_errnum := -1 ;
482 p_errmesg := 'Failed in Resource Load Phase : '||x_phase|| substr(SQLERRM,1,125);
483 return ;
484 end ;
485
486 WIP_PROD_INDICATORS.Populate_Resource_Load(
487 p_group_id => x_temp_group_id,
488 p_organization_id => p_organization_id,
489 p_date_from => p_date_from,
490 p_date_to => p_date_to,
491 p_department_id => Dept_Res_Rec.department_id,
492 p_resource_id => p_resource_id,
493 p_userid => p_userid,
494 p_applicationid => p_applicationid,
495 p_errnum => p_errnum,
496 p_errmesg => p_errmesg) ;
497
498
499 Update_Group_Id(
500 p_temp_group_id => x_temp_group_id,
501 p_main_group_id => x_main_group_id);
502
503
504 END LOOP ;
505
506 ELSIF p_department_id IS NOT NULL AND p_resource_id IS NULL THEN
507
508 WIP_PROD_INDICATORS.Populate_Resource_Load(
509 p_group_id => x_main_group_id,
510 p_organization_id => p_organization_id,
511 p_date_from => p_date_from,
512 p_date_to => p_date_to,
513 p_department_id => p_department_id,
514 p_resource_id => p_resource_id,
515 p_userid => p_userid,
516 p_applicationid => p_applicationid,
517 p_errnum => p_errnum,
518 p_errmesg => p_errmesg) ;
519
520
521 END IF ;
522 p_errnum := 1 ;
523 p_errmesg := null ;
524 return ;
525
526
527 Exception
528
529 when others then
530
531 p_errnum := -1 ;
532 p_errmesg := 'Failed in Resource Load Phase : '||x_phase|| substr(SQLERRM,1,125);
533 return ;
534
535 END Populate_Resource_Load;
536
537
538 Procedure Update_Group_Id(
539 p_temp_group_id NUMBER,
540 p_main_group_id NUMBER) IS
541
542 Begin
543
544 UPDATE Wip_Indicators_Temp
545 SET group_id = p_main_group_id
546 WHERE
547 group_id = p_temp_group_id ;
548
549 End Update_Group_Id ;
550
551
552
553 /***********************************************************
554 * This is the API that gets called from the PCB - department
555 * supervisor/ department operator for resource charging.
556 * This API inserts the resource transaction data into the
557 * Wip_Resource_Txn_Interface and uses the Resource_Txn
558 * API written by bbaby and rbankar
559 *********************************************************/
560
561
562 PROCEDURE Resource_Txn (
563 p_DEPARTMENT_ID IN NUMBER,
564 p_EMPLOYEE_ID IN NUMBER,
565 p_EMPLOYEE_NUM IN NUMBER,
566 p_LINE_ID IN NUMBER,
567 p_OPERATION_SEQ_NUM IN NUMBER,
568 p_ORGANIZATION_ID IN NUMBER,
569 p_PRIMARY_QUANTITY IN NUMBER,
570 p_PROJECT_ID IN NUMBER,
571 p_REASON_ID IN NUMBER,
572 p_REFERENCE IN VARCHAR2,
573 p_RESOURCE_ID IN NUMBER,
574 p_RESOURCE_SEQ_NUM IN NUMBER,
575 p_REPETITIVE_SCHEDULE_ID IN NUMBER,
576 p_SOURCE_CODE IN VARCHAR2,
577 p_TASK_ID IN NUMBER,
578 p_TRANSACTION_DATE IN DATE,
579 p_TRANSACTION_QUANTITY IN NUMBER,
580 p_WIP_ENTITY_ID IN NUMBER,
581 p_ACCT_PERIOD_ID IN NUMBER DEFAULT NULL,
582 p_ACTIVITY_ID IN NUMBER DEFAULT NULL,
583 p_ACTIVITY_NAME IN VARCHAR2 DEFAULT NULL,
584 p_ACTUAL_RESOURCE_RATE IN NUMBER DEFAULT NULL,
585 p_CREATED_BY IN NUMBER DEFAULT NULL,
586 p_CREATED_BY_NAME IN VARCHAR2 DEFAULT NULL,
587 p_LAST_UPDATED_BY IN NUMBER,
588 p_LAST_UPDATED_BY_NAME IN VARCHAR2 DEFAULT NULL,
589 p_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
590 p_LAST_UPDATE_LOGIN IN NUMBER,
591 p_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
592 p_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
593 p_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
594 p_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
595 p_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
596 p_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
597 p_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
598 p_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
599 p_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
600 p_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
601 p_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
602 p_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
603 p_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
604 p_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
605 p_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
606 p_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
607 p_AUTOCHARGE_TYPE IN NUMBER DEFAULT NULL,
608 p_BASIS_TYPE IN NUMBER DEFAULT NULL,
609 p_COMPLETION_TRANSACTION_ID IN NUMBER DEFAULT NULL,
610 p_CREATION_DATE IN DATE DEFAULT NULL,
611 p_CURRENCY_ACTUAL_RSC_RATE IN NUMBER DEFAULT NULL,
612 p_CURRENCY_CODE IN VARCHAR2 DEFAULT NULL,
613 p_CURRENCY_CONVERSION_DATE IN DATE DEFAULT NULL,
614 p_CURRENCY_CONVERSION_RATE IN NUMBER DEFAULT NULL,
615 p_CURRENCY_CONVERSION_TYPE IN VARCHAR2 DEFAULT NULL,
616 p_DEPARTMENT_CODE IN VARCHAR2 DEFAULT NULL,
617 p_ENTITY_TYPE IN NUMBER DEFAULT NULL,
618 p_GROUP_ID IN NUMBER DEFAULT NULL,
619 p_LINE_CODE IN VARCHAR2 DEFAULT NULL,
620 p_MOVE_TRANSACTION_ID IN NUMBER DEFAULT NULL,
621 p_ORGANIZATION_CODE IN VARCHAR2 DEFAULT NULL,
622 p_PO_HEADER_ID IN NUMBER DEFAULT NULL,
623 p_PO_LINE_ID IN NUMBER DEFAULT NULL,
624 p_PRIMARY_ITEM_ID IN NUMBER DEFAULT NULL,
625 p_PRIMARY_UOM IN VARCHAR2 DEFAULT NULL,
626 p_PRIMARY_UOM_CLASS IN VARCHAR2 DEFAULT NULL,
627 p_PROCESS_PHASE IN NUMBER DEFAULT NULL,
628 p_PROCESS_STATUS IN NUMBER DEFAULT NULL,
629 p_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL,
630 p_PROGRAM_ID IN NUMBER DEFAULT NULL,
631 p_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL,
632 p_RCV_TRANSACTION_ID IN NUMBER DEFAULT NULL,
633 p_REASON_NAME IN VARCHAR2 DEFAULT NULL,
634 p_RECEIVING_ACCOUNT_ID IN NUMBER DEFAULT NULL,
635 p_REQUEST_ID IN NUMBER DEFAULT NULL,
636 p_RESOURCE_CODE IN VARCHAR2 DEFAULT NULL,
637 p_RESOURCE_TYPE IN NUMBER DEFAULT NULL,
638 p_SOURCE_LINE_ID IN NUMBER DEFAULT NULL,
639 p_STANDARD_RATE_FLAG IN NUMBER DEFAULT NULL,
640 p_TRANSACTION_ID IN NUMBER DEFAULT NULL,
641 p_TRANSACTION_TYPE IN NUMBER DEFAULT NULL,
642 p_TRANSACTION_UOM IN VARCHAR2 DEFAULT NULL,
643 p_USAGE_RATE_OR_AMOUNT IN NUMBER DEFAULT NULL,
644 p_WIP_ENTITY_NAME IN VARCHAR2 DEFAULT NULL,
645 p_ret_status OUT NOCOPY VARCHAR2
646 ) is
647 l_res_txn_rec Wip_Transaction_PUB.Res_rec_Type ;
648 l_msg_count NUMBER;
649 l_msg_data VARCHAR2(240);
650 x_txn_date DATE ;
651 Begin
652
653 if (p_Transaction_Date is null) then
654 x_txn_date := sysdate ;
655 else
656 x_txn_date := p_TRANSACTION_DATE ;
657 end if ;
658
659
660
661 l_res_txn_rec.acct_period_id := p_acct_period_id ;
662 l_res_txn_rec.activity_id := p_activity_id ;
663 l_res_txn_rec.activity_name := p_activity_name ;
664 l_res_txn_rec.actual_resource_rate := p_actual_resource_rate ;
665 l_res_txn_rec.attribute1 := p_attribute1 ;
666 l_res_txn_rec.attribute10 := p_attribute10 ;
667 l_res_txn_rec.attribute11 := p_attribute11 ;
668 l_res_txn_rec.attribute12 := p_attribute12 ;
669 l_res_txn_rec.attribute13 := p_attribute13 ;
670 l_res_txn_rec.attribute14 := p_attribute14 ;
671 l_res_txn_rec.attribute15 := p_attribute15 ;
672 l_res_txn_rec.attribute2 := p_attribute2 ;
673 l_res_txn_rec.attribute3 := p_attribute3 ;
674 l_res_txn_rec.attribute4 := p_attribute4 ;
675 l_res_txn_rec.attribute5 := p_attribute5 ;
676 l_res_txn_rec.attribute6 := p_attribute6 ;
677 l_res_txn_rec.attribute7 := p_attribute7 ;
678 l_res_txn_rec.attribute8 := p_attribute8 ;
679 l_res_txn_rec.attribute9 := p_attribute9 ;
680 l_res_txn_rec.attribute_category := p_attribute_category ;
681 l_res_txn_rec.autocharge_type := p_autocharge_type ;
682 l_res_txn_rec.basis_type := p_basis_type ;
683 l_res_txn_rec.completion_transaction_id := p_completion_transaction_id ;
684 l_res_txn_rec.created_by := p_created_by ;
685 l_res_txn_rec.created_by_name := p_created_by_name ;
686 l_res_txn_rec.creation_date := p_creation_date ;
687 l_res_txn_rec.currency_actual_rsc_rate := p_currency_actual_rsc_rate ;
688 l_res_txn_rec.currency_code := p_currency_code ;
689 l_res_txn_rec.currency_conversion_date := p_currency_conversion_date ;
690 l_res_txn_rec.currency_conversion_rate := p_currency_conversion_rate ;
691 l_res_txn_rec.currency_conversion_type := p_currency_conversion_type ;
692 l_res_txn_rec.department_code := p_department_code ;
693 l_res_txn_rec.department_id := p_department_id ;
694 l_res_txn_rec.employee_id := p_employee_id ;
695 l_res_txn_rec.employee_num := p_employee_num ;
696 l_res_txn_rec.entity_type := p_entity_type ;
697 l_res_txn_rec.group_id := p_group_id ;
698 l_res_txn_rec.last_updated_by := p_last_updated_by ;
699 l_res_txn_rec.last_updated_by_name := p_last_updated_by_name ;
700 l_res_txn_rec.last_update_date := p_last_update_date ;
701 l_res_txn_rec.last_update_login := p_last_update_login ;
702 l_res_txn_rec.line_code := p_line_code ;
703 l_res_txn_rec.line_id := p_line_id ;
704 l_res_txn_rec.move_transaction_id := p_move_transaction_id ;
705 l_res_txn_rec.operation_seq_num := p_operation_seq_num ;
706 l_res_txn_rec.organization_code := p_organization_code ;
707 l_res_txn_rec.organization_id := p_organization_id ;
708 l_res_txn_rec.po_header_id := p_po_header_id ;
709 l_res_txn_rec.po_line_id := p_po_line_id ;
710 l_res_txn_rec.primary_item_id := p_primary_item_id ;
711 l_res_txn_rec.primary_quantity := p_primary_quantity ;
712 l_res_txn_rec.primary_uom := p_primary_uom ;
713 l_res_txn_rec.primary_uom_class := p_primary_uom_class ;
714 l_res_txn_rec.process_phase := p_process_phase ;
715 l_res_txn_rec.process_status := p_process_status ;
716 l_res_txn_rec.program_application_id := p_program_application_id ;
717 l_res_txn_rec.program_id := p_program_id ;
718 l_res_txn_rec.program_update_date := p_program_update_date ;
719 l_res_txn_rec.project_id := p_project_id ;
720 l_res_txn_rec.rcv_transaction_id := p_rcv_transaction_id ;
721 l_res_txn_rec.reason_id := p_reason_id ;
722 l_res_txn_rec.reason_name := p_reason_name ;
723 l_res_txn_rec.receiving_account_id := p_receiving_account_id ;
724 l_res_txn_rec.reference := p_reference ;
725 l_res_txn_rec.repetitive_schedule_id := p_repetitive_schedule_id ;
726 l_res_txn_rec.request_id := p_request_id ;
727 l_res_txn_rec.resource_code := p_resource_code ;
728 l_res_txn_rec.resource_id := p_resource_id ;
729 l_res_txn_rec.resource_seq_num := p_resource_seq_num ;
730 l_res_txn_rec.resource_type := p_resource_type ;
731 l_res_txn_rec.source_code := p_source_code ;
732 l_res_txn_rec.source_line_id := p_source_line_id ;
733 l_res_txn_rec.standard_rate_flag := p_standard_rate_flag ;
734 l_res_txn_rec.task_id := p_task_id ;
735 l_res_txn_rec.transaction_date := x_txn_date ;
736 l_res_txn_rec.transaction_id := p_transaction_id ;
737 l_res_txn_rec.transaction_quantity := p_transaction_quantity ;
738 l_res_txn_rec.transaction_type := p_transaction_type ;
739 l_res_txn_rec.transaction_uom := p_transaction_uom ;
740 l_res_txn_rec.usage_rate_or_amount := p_usage_rate_or_amount ;
741 l_res_txn_rec.wip_entity_id := p_wip_entity_id ;
742 l_res_txn_rec.wip_entity_name := p_wip_entity_name ;
743
744
745 WIP_Transaction_PVT.Process_Resource_Transaction(
746 p_res_txn_rec => l_res_txn_rec,
747 p_return_status => p_ret_status,
748 p_msg_count => l_msg_count,
749 p_msg_data => l_msg_data ) ;
750
751 -- commit regardless of status..
752 --if (p_ret_status <> FND_API.G_RET_STS_SUCCESS) then
753 -- rollback;
754 --else
755
756 -- this was to fix the bug#845918
757 commit ;
758
759 --end if;
760
761 End Resource_Txn ;
762
763
764 --------------------------------------------------
765 -- This procedure updates the Current Line Operation
766 -- in the Wip_Flow_Schedules for a particular
767 -- flow schedule.
768 --------------------------------------------------
769 PROCEDURE Update_Line_Operation (
770 p_line_operation IN NUMBER,
771 p_wip_entity_id IN NUMBER,
772 p_organization_id IN NUMBER )
773 IS
774
775 BEGIN
776
777 Update Wip_Flow_Schedules
778 SET current_Line_Operation = p_line_operation
779 WHERE
780 Wip_Entity_Id = p_wip_entity_id
781 AND Organization_Id = p_organization_id ;
782
783
784 END Update_Line_Operation ;
785
786
787
788
789 /**************************************************
790 * This procedure calculates the Line Load for a
791 * particular line and this is called from the
792 * flow operator Work bench
793 *
794 * yulin, to support oracle time zone
795 * p_date_from and p_date_to should be date only
796 * to represent a whole day in client timezone
797 *************************************************/
798
799 Procedure Populate_Line_Load(
800 p_group_id IN NUMBER,
801 p_date_from IN DATE,
802 p_date_to IN DATE,
803 p_line_id IN NUMBER,
804 p_userid IN NUMBER,
805 p_applicationid IN NUMBER,
806 p_errnum OUT NOCOPY NUMBER,
807 p_errmesg OUT NOCOPY VARCHAR2)
808 IS
809 x_phase VARCHAR2(10) ;
810 x_group_id NUMBER ;
811 x_date_to DATE ;
812 x_available_quantity NUMBER ;
813 x_userid NUMBER;
814 x_appl_id NUMBER;
815 x_org_id NUMBER;
816 BEGIN
817
818 /************************************************
819 * Check for the set of required parameters
820 * 1. Line Id
821 * 2. Date From
822 *************************************************/
823
824
825 x_phase := 'I';
826 If p_line_id is null then
827 p_errnum := -1 ;
828 p_errmesg := 'Failed in Line Load Phase : '||x_phase||' Line Id is NULL' ;
829 return ;
830 End if ;
831
832
833 If p_date_from is null then
834 p_errnum := -1 ;
835 p_errmesg := 'Failed in Line Load Phase : '||x_phase||' Date From is NULL' ;
836 return ;
837 End if ;
838
839
840 /***********************************************
841 * If the To Date is null, default the sysdate
842 * to be the To Date
843 ***********************************************/
844
845 If p_date_to is null then
846 x_date_to := trunc(wip_sfcb_utilities.sdate_to_cdate(sysdate)) ;
847 ELSE
848 x_date_to := p_date_to ;
849 End if ;
850
851
852 /***********************************************
853 * If the Group ID is null then we would generate
854 * a new group id from the sequence
855 ***********************************************/
856
857 IF p_group_id IS NULL THEN
858 select wip_indicators_temp_s.nextval into x_group_id
859 from sys.dual ;
860 ELSE
861 x_group_id := p_group_id ;
862 END IF;
863
864
865 -- Defaulting the User Id, if it is not send in
866
867 if p_userid is null then
868 -- This is an Error Condition
869 x_userid := fnd_global.user_id ;
870 else
871 x_userid := p_userid ;
872 end if;
873
874 -- Defaulting the Application Id, if it is not send in
875
876 if p_applicationid is null then
877 -- This is an Error Condition
878 x_appl_id := fnd_global.prog_appl_id ;
879 else
880 x_appl_id := p_applicationid ;
881 end if;
882
883 g_userid := x_userid ;
884 g_applicationid := x_appl_id ;
885
886
887
888 -- Get the line rate, the quantity that can be
889 -- produced by the line every day.
890
891 x_phase := 'II';
892 select
893 ((stop_time - start_time)*maximum_rate)/3600
894 into
895 x_available_quantity
896 from
897 wip_lines
898 where
899 line_id = p_line_id ;
900
901
902 x_phase := 'III';
903
904 --dbms_output.put_line('Before the Insert statement');
905 --dbms_output.put_line(to_char(p_date_from,'DD-Mon-YYYY'));
906 --dbms_output.put_line(to_char(x_date_to,'DD-Mon-YYYY'));
907
908 -- Insert the planned quantity and the available quantity for
909 -- every day based on the WFS table. Note this will make the
910 -- assumption that the line will be working on non working days
911 -- also if, a flow schedule is required on a non working day.
912 -- To correct this hack we actually perform a update at the end
913 -- of this procedure (that is a hack, you can do a join in this
914 -- sql statement and actually perform the whole intelligent insert
915 -- in this statement itself).
916
917 insert into wip_indicators_temp(
918 group_id,
919 line_id,
920 transaction_date,
921 required_quantity,
922 available_quantity,
923 indicator_type,
924 process_phase,
925 last_update_date,
926 last_updated_by,
927 creation_date,
928 created_by,
929 program_application_id )
930 select
931 x_group_id,
932 p_line_id,
933 trunc(wip_sfcb_utilities.sdate_to_cdate(wfs.scheduled_completion_date)),
934 sum(wfs.planned_quantity),
935 x_available_quantity,
936 WIP_LINE_LOAD,
937 WIP_LINE_LOAD_PHASE_ONE,
938 sysdate,
939 x_userid,
940 sysdate,
941 x_userid,
942 x_appl_id
943 from
944 wip_flow_schedules wfs
945 where
946 wfs.line_id = p_line_id
947 and trunc(wip_sfcb_utilities.sdate_to_cdate(wfs.scheduled_completion_date))
948 between p_date_from and x_date_to
949 group by trunc(wip_sfcb_utilities.sdate_to_cdate(wfs.scheduled_completion_date)) ;
950
951
952 /* bug 2644622 - from meeting with richard and adrian, should only display
953 days with a schedule. So commenting this section out.
954 -- Insert tha line availability for all the days when the line
955 -- is available, but there is no load on the line. I.e., there
956 -- are no records in WFS.
957
958 -- insert into wip_indicators_temp(
959 -- group_id,
960 -- line_id,
961 -- transaction_date,
962 -- required_quantity,
963 -- available_quantity,
964 -- indicator_type,
965 -- process_phase,
966 -- last_update_date,
967 -- last_updated_by,
968 -- creation_date,
969 -- created_by,
970 -- program_application_id )
971 --select
972 -- x_group_id,
973 -- p_line_id,
974 -- bcd.calendar_date,
975 -- null,
976 -- x_available_quantity,
977 -- WIP_LINE_LOAD,
978 -- WIP_LINE_LOAD_PHASE_ONE,
979 -- sysdate,
980 -- x_userid,
981 -- sysdate,
982 -- x_userid,
983 -- x_appl_id
984 --from
985 -- bom_calendar_dates bcd,
986 -- mtl_parameters mp,
987 -- wip_lines wl
988 --where
989 -- wl.line_id = p_line_id
990 --and mp.organization_id = wl.organization_id
991 --and bcd.calendar_code = mp.calendar_code
992 --and bcd.exception_set_id = mp.calendar_exception_set_id
993 --and bcd.calendar_date between p_date_from and p_date_to
994 --and bcd.seq_num is not null
995 --and bcd.calendar_date not in
996 -- ( Select distinct transaction_date
997 -- from wip_indicators_temp
998 -- where group_id = x_group_id
999 -- and indicator_type = WIP_LINE_LOAD
1000 -- and process_phase = WIP_LINE_LOAD_PHASE_ONE
1001 -- ) ;
1002 */
1003
1004
1005 -- This is a hack and is used to update the availability of
1006 -- the line to be null on the non working days as per
1007 -- the decision by jgu and dsoosai
1008
1009 -- add flm_timezone call to support timezone
1010 SELECT organization_id
1011 INTO x_org_id
1012 FROM wip_lines
1013 WHERE line_id = p_line_id;
1014 flm_timezone.init_timezone(x_org_id);
1015 UPDATE wip_indicators_temp wit
1016 SET wit.available_quantity = 0
1017 WHERE wit.group_id = x_group_id
1018 and flm_timezone.client_to_calendar(wit.transaction_date) NOT IN (
1019 SELECT bcd.calendar_date
1020 FROM bom_calendar_dates bcd,
1021 mtl_parameters mp,
1022 wip_lines wl
1023 where
1024 wl.line_id = p_line_id
1025 and mp.organization_id = wl.organization_id
1026 and bcd.calendar_code = mp.calendar_code
1027 and bcd.exception_set_id = mp.calendar_exception_set_id
1028 and bcd.calendar_date between
1029 flm_timezone.client_to_calendar(p_date_from) and
1030 flm_timezone.client_to_calendar(p_date_to)
1031 and bcd.seq_num is not null
1032 ) ;
1033
1034
1035 Exception
1036
1037 When others then
1038 --dbms_output.put_line('In the exception region');
1039
1040 p_errnum := -1 ;
1041 p_errmesg := 'Failed in Line Load Phase : '||x_phase|| substr(SQLERRM,1,125);
1042 return ;
1043
1044
1045
1046 End Populate_Line_Load ;
1047
1048
1049
1050 /* ---------------------------------
1051 -- Set of procedures required for setting
1052 -- the variables for calculating linearity
1053 -------------------------------------- */
1054
1055 Procedure Set_Organization(p_org_Id IN NUMBER) is
1056 Begin
1057
1058 g_Linearity_Org := p_org_id ;
1059
1060 End Set_Organization;
1061
1062
1063 Procedure Set_Linearity_Dates(
1064 p_from_date IN DATE DEFAULT NULL,
1065 p_to_date IN DATE DEFAULT NULL ) IS
1066 x_from_date DATE := sysdate - 7;
1067 x_to_date DATE := sysdate ;
1068 Begin
1069
1070 If p_from_date is not null then
1071 x_from_date := p_from_date ;
1072 End if ;
1073
1074 If p_to_date is not null then
1075 x_to_date := p_to_date ;
1076 End if ;
1077
1078 g_Linearity_Date_From := x_from_date ;
1079 g_Linearity_Date_To := x_to_date ;
1080
1081 End Set_Linearity_Dates ;
1082
1083
1084 Procedure Set_Line(p_line_id IN NUMBER) is
1085 Begin
1086
1087 g_Linearity_Line := p_line_id ;
1088
1089 End Set_Line;
1090
1091
1092 Function get_Organization RETURN NUMBER Is
1093 Begin
1094 return g_Linearity_Org ;
1095 End get_Organization ;
1096
1097
1098 Function get_Linearity_From_Date RETURN DATE is
1099 Begin
1100
1101 return g_Linearity_Date_From;
1102
1103 End get_Linearity_From_Date ;
1104
1105
1106 Function get_Linearity_To_Date RETURN DATE is
1107 Begin
1108
1109 return g_Linearity_Date_To;
1110
1111 End get_Linearity_To_Date ;
1112
1113
1114 Function get_Line RETURN NUMBER IS
1115 Begin
1116
1117 return g_Linearity_Line ;
1118
1119 End get_Line;
1120
1121
1122 -- Checks whether or not the line operation is pending based on the current line operation which
1123 -- is to be worked on (column in WFS). The line operation and current line operation passed in
1124 -- are the actual operation sequence numbers and not the ID's!
1125 function line_op_is_pending (
1126 p_line_op in number,
1127 p_rtg_seq_id in number,
1128 p_assy_item_id IN NUMBER,
1129 p_org_id IN NUMBER,
1130 p_alt_rtg_designator IN VARCHAR2,
1131 p_current_line_op in NUMBER DEFAULT NULL
1132 ) return number
1133 IS
1134
1135 ops_table bom_rtg_network_api.op_tbl_type ;
1136 i BINARY_INTEGER;
1137
1138 begin
1139
1140 -- If no current line op is specified, get the list of all line ops
1141 -- for the routing. This is when just starting out..
1142 if (p_current_line_op IS NULL) then
1143
1144 bom_rtg_network_api.get_all_line_ops(
1145 P_RTG_SEQUENCE_ID => p_rtg_seq_id,
1146 P_ASSY_ITEM_ID => p_assy_item_id,
1147 P_ORG_ID => p_org_id,
1148 P_ALT_RTG_DESIG => p_alt_rtg_designator,
1149 X_OP_TBL => ops_table
1150 );
1151
1152 -- If the current line op is -1, then we have completed the last line operation for
1153 -- the schedule. We will know immediately that the line op isn't pending.
1154 ELSIF (p_current_line_op = -1) THEN
1155
1156 RETURN 2 ;
1157
1158 -- In the special case that the line op is the same as the current line op,
1159 -- we can immediately tell that the line op is pending.
1160 elsif (p_line_op = p_current_line_op) then
1161
1162 return 1 ;
1163
1164 -- If the current line op is non-null and different from the specified
1165 -- line op, get the list of all line ops that appear _after_ the current
1166 -- op in the routing.
1167 else
1168
1169 bom_rtg_network_api.get_all_next_line_ops(
1170 p_rtg_sequence_id => p_rtg_seq_id,
1171 p_assy_item_id => p_assy_item_id,
1172 p_org_id => p_org_id,
1173 p_alt_rtg_desig => p_alt_rtg_designator,
1174 p_curr_line_op => p_current_line_op,
1175 x_op_tbl => ops_table
1176 );
1177
1178 end if ;
1179
1180 -- The specified line op is pending iff it is in the list of line ops
1181 -- we retrieved.
1182
1183 IF (ops_table.COUNT > 0) then
1184
1185 FOR i IN ops_table.first..ops_table.last LOOP
1186
1187 IF ops_table(i).operation_seq_num = p_line_op THEN
1188 RETURN 1;
1189 END IF;
1190
1191 END LOOP;
1192
1193 END IF;
1194
1195
1196 return 2 ;
1197
1198
1199 EXCEPTION
1200 WHEN OTHERS THEN RETURN 1;
1201
1202 end line_op_is_pending ;
1203
1204
1205 /* This is called from the Flow Operator
1206 Work Bench. This is done in the following
1207 4 steps :
1208
1209 1. Get the list Open Schedules from the
1210 Wip_Open_Schedules_V for that particular
1211 Line and Line Operation.
1212
1213 1. Get the Maximum and Minimum Simulation
1214 Dates from wip_open_schedules_v view.
1215
1216 2. Call the Resource Availability Routine
1217
1218 3. Either open the cursor, and for every
1219 flow schedule and get the resource requirements
1220 or get the resource requirements in one
1221 step.
1222
1223 4. Sum the resource requirements across the
1224 schedules.
1225
1226 5. Update the available hours.
1227
1228 *** to support oracle timez zone
1229 p_from_date, p_to_date should be date only, to represent
1230 a whole day in client time zone
1231
1232 */
1233
1234 PROCEDURE Populate_Line_Resource_Load (
1235 p_group_id IN NUMBER,
1236 p_organization_id IN NUMBER,
1237 p_date_from IN DATE,
1238 p_date_to IN DATE,
1239 p_line_id IN NUMBER,
1240 p_line_op_id IN NUMBER,
1241 p_userid IN NUMBER,
1242 p_applicationid IN NUMBER,
1243 p_errnum OUT NOCOPY NUMBER,
1244 p_errmesg OUT NOCOPY VARCHAR2) IS
1245 x_date_from DATE;
1246 x_date_to DATE;
1247 x_sim_date_from DATE;
1248 x_sim_date_to DATE;
1249 x_group_id NUMBER;
1250 x_phase VARCHAR2(10);
1251 x_userid NUMBER;
1252 x_appl_id NUMBER;
1253
1254 -- server time for the p_date_from and p_date_to
1255 s_time_from DATE;
1256 s_time_to DATE;
1257 Begin
1258
1259
1260 /************************************************
1261 * Check for the set of required parameters
1262 * 1. Line Id
1263 * 2. Line Op Id
1264 * 3. Date From
1265 *************************************************/
1266
1267 x_phase := 'I';
1268 If p_line_id is null then
1269 p_errnum := -1 ;
1270 p_errmesg := 'Failed in Line Resource Load Phase : '||x_phase||' Line Id is NULL' ;
1271 return ;
1272 End if ;
1273
1274 If p_line_op_id is null then
1275 p_errnum := -1 ;
1276 p_errmesg := 'Failed in Line Resource Load Phase : '||x_phase||' Line Id is NULL' ;
1277 return ;
1278 End if ;
1279
1280
1281 If p_date_from is null then
1282 p_errnum := -1 ;
1283 p_errmesg := 'Failed in Line Resource Load Phase : '||x_phase||' Date From is NULL' ;
1284 return ;
1285 End if ;
1286
1287
1288 /***********************************************
1289 * If the To Date is null, default the sysdate
1290 * to be the To Date
1291 ***********************************************/
1292
1293 If p_date_to is null then
1294 x_date_to := trunc(wip_sfcb_utilities.sdate_to_cdate(sysdate)) ;
1295 ELSE
1296 x_date_to := p_date_to ;
1297 End if ;
1298
1299
1300
1301 /***********************************************
1302 * If the Group ID is null then we would generate
1303 * a new group id from the sequence
1304 ***********************************************/
1305
1306 IF p_group_id IS NULL THEN
1307 select wip_indicators_temp_s.nextval into x_group_id
1308 from sys.dual ;
1309 ELSE
1310 x_group_id := p_group_id ;
1311 END IF;
1312
1313
1314 -- Defaulting the User Id, if it is not send in
1315 if p_userid is null then
1316 -- This is an Error Condition
1317 x_userid := fnd_global.user_id ;
1318 else
1319 x_userid := p_userid ;
1320 end if;
1321
1322 -- Defaulting the Application Id, if it is not send in
1323 if p_applicationid is null then
1324 -- This is an Error Condition
1325 x_appl_id := fnd_global.prog_appl_id ;
1326 else
1327 x_appl_id := p_applicationid ;
1328 end if;
1329
1330 g_userid := x_userid ;
1331 g_applicationid := x_appl_id ;
1332
1333
1334
1335
1336 x_phase := 'II';
1337
1338 x_date_from := p_date_from ;
1339 x_date_to := p_date_to ;
1340
1341 -- compute the corresponding server times before hand
1342 s_time_from := wip_sfcb_utilities.cdate_to_sdate(x_date_from);
1343 s_time_to := wip_sfcb_utilities.cdate_to_sdate(x_date_to) + 1;
1344
1345 -- Gets the Minimum Start Date and Maximum End Date to run
1346 -- the simulation for the line resource load.
1347
1348
1349 begin
1350
1351 -- Bug 4890953
1352 -- Replacing view wip_open_flow_schedules_v
1353 -- With the view query to avoid redundant joins
1354 -- and improve performance.
1355 /*
1356 select min(scheduled_start_date), max(scheduled_completion_date)
1357 into x_sim_date_from, x_sim_date_to
1358 from wip_open_flow_schedules_v
1359 where line_id = p_line_id
1360 and standard_operation_id = p_line_op_id
1361 -- for the sake of performance
1362 and
1363 (
1364 ( scheduled_start_date >= s_time_from
1365 and scheduled_start_date < s_time_to)
1366 or
1367 ( scheduled_completion_date >= s_time_from
1368 and scheduled_completion_date >= s_time_to )
1369 );
1370 */
1371
1372 SELECT
1373 min(wfs.scheduled_start_date),
1374 max(wfs.scheduled_completion_date)
1375 INTO
1376 x_sim_date_from,
1377 x_sim_date_to
1378 FROM
1379 wip_lines wl,
1380 bom_operation_sequences_v bos,
1381 bom_operational_routings bor,
1382 wip_flow_schedules wfs
1383 WHERE
1384 wfs.scheduled_flag = 1
1385 and bor.organization_id = wfs.organization_id
1386 and bor.assembly_item_id = wfs.primary_item_id
1387 and bor.line_id = wfs.line_id
1388 and bor.cfm_routing_flag = 1
1389 and decode(bor.alternate_routing_designator, null,'@@@@@@@',bor.alternate_routing_designator) =
1390 decode(wfs.alternate_routing_designator, null, '@@@@@@@', wfs.alternate_routing_designator)
1391 and bos.operation_type = 3 /* line operation */
1392 and bos.routing_sequence_id = bor.common_routing_sequence_id
1393 and wl.line_id = wfs.line_id
1394 and wl.organization_id = wfs.organization_id
1395 and wfs.status <> 2
1396 and WIP_SFCB_Utilities.line_op_is_pending (
1397 BOS.operation_seq_num,
1398 BOR.common_routing_sequence_id,
1399 WFS.primary_item_id,
1400 WFS.organization_id,
1401 WFS.alternate_routing_designator,
1402 WFS.current_line_operation
1403 ) = 1
1404 and wfs.line_id = p_line_id
1405 and bos.standard_operation_id = p_line_op_id
1406 and
1407 (
1408 ( wfs.scheduled_start_date >= s_time_from
1409 and wfs.scheduled_start_date < s_time_to)
1410 or
1411 ( wfs.scheduled_completion_date >= s_time_from
1412 and wfs.scheduled_completion_date >= s_time_to )
1413 ) ;
1414
1415 x_sim_date_from := trunc(wip_sfcb_utilities.sdate_to_cdate(x_sim_date_from));
1416 x_sim_date_to := trunc(wip_sfcb_utilities.sdate_to_cdate(x_sim_date_to));
1417
1418 exception
1419 when others then
1420 --dbms_output.put_line('Exception in the simulation date calculation section');
1421 x_sim_date_from := x_date_from ;
1422 x_sim_date_to := x_date_to ;
1423
1424 end ;
1425
1426
1427 x_phase := 'III';
1428 if g_debug = 1 then
1429 fnd_file.put_line(fnd_file.log, 'Before Stage LRL Phase III');
1430 end if ;
1431
1432
1433 -- Calculate the resource availability and populate the
1434 -- information into MRP_NET_RESOURCE_AVAIL
1435
1436 Wip_Prod_Indicators.Calculate_Resource_Avail(
1437 p_organization_id => p_organization_id,
1438 p_date_from => x_sim_date_from,
1439 p_date_to => x_sim_date_to,
1440 p_department_id => null,
1441 p_resource_id => null,
1442 p_errnum => p_errnum,
1443 p_errmesg => p_errmesg
1444 ) ;
1445
1446
1447 x_phase := 'IV';
1448 if g_debug = 1 then
1449 fnd_file.put_line(fnd_file.log, 'Before Stage LRL Phase IV');
1450 end if ;
1451
1452 -- Insert the required hours for each resource in the line operation
1453 -- This will insert a unique row for each one of shift in each day
1454 -- for which the resource was loaded. The left over resource load
1455 -- will be equally allocated across each of these unique rows.
1456
1457 insert into wip_indicators_temp (
1458 group_id,
1459 wip_entity_id,
1460 organization_id,
1461 resource_id,
1462 resource_code,
1463 department_id,
1464 department_code,
1465 transaction_date,
1466 required_hours,
1467 indicator_type,
1468 process_phase,
1469 last_update_date,
1470 last_updated_by,
1471 creation_date,
1472 created_by,
1473 program_application_id )
1474 select
1475 x_group_id,
1476 wofsv.wip_entity_id,
1477 wofsv.organization_id,
1478 bors.resource_id,
1479 br.resource_code,
1480 bos.department_id,
1481 bd.department_code,
1482 null,
1483 decode(bors.basis_type,
1484 1,
1485 (NVL( bors.usage_rate_or_amount *
1486 (wofsv.planned_quantity-wofsv.quantity_completed),0
1487 )*
1488 WIP_SFCB_UTILITIES.get_Workday_factor
1489 (trunc(wip_sfcb_utilities.sdate_to_cdate(wofsv.scheduled_start_date)),
1490 trunc(wip_sfcb_utilities.sdate_to_cdate(wofsv.scheduled_completion_date)),
1491 trunc(x_date_from),
1492 trunc(x_date_to),
1493 bors.resource_id,
1494 wofsv.organization_id)),
1495 2,
1496 DECODE(sign(trunc(wip_sfcb_utilities.sdate_to_cdate(wofsv.scheduled_completion_date)) -
1497 x_date_to),
1498 1,
1499 0,
1500 bors.usage_rate_or_amount
1501 )
1502 ),
1503 WIP_LINE_RL, -- Indicator Type
1504 WIP_LINE_RL_PHASE_ONE, -- process phase
1505 sysdate,
1506 g_userid,
1507 sysdate,
1508 g_userid,
1509 g_applicationid
1510 from
1511 bom_departments bd,
1512 bom_resources br,
1513 bom_operation_resources bors,
1514 bom_operation_sequences bos2, /* event seqs */
1515 bom_operation_sequences bos, /* line operations */
1516 bom_operational_routings bor,
1517 wip_open_flow_schedules_v wofsv
1518 where
1519 wofsv.organization_id = p_organization_id
1520 and wofsv.line_id = p_line_id
1521 and wofsv.standard_operation_id = p_line_op_id
1522 and ( ( wofsv.scheduled_start_date >= s_time_from
1523 and wofsv.scheduled_start_date < s_time_to)
1524 or
1525 ( wofsv.scheduled_completion_date >= s_time_from
1526 and wofsv.scheduled_completion_date < s_time_to)
1527 )
1528 and bor.organization_id = wofsv.organization_id
1529 and bor.assembly_item_id = wofsv.primary_item_id
1530 and bor.line_id = wofsv.line_id
1531 and nvl(bor.alternate_routing_designator,'@@@') =
1532 nvl(wofsv.alternate_routing_designator,'@@@')
1533 and bos.operation_type = 3
1534 and bos.routing_sequence_id = bor.common_routing_sequence_id
1535 and bos.standard_operation_id = p_line_op_id
1536 and bos2.line_op_seq_id = bos.operation_sequence_id
1537 and bors.operation_sequence_id = bos2.operation_sequence_id
1538 and br.resource_id = bors.resource_id
1539 and bd.department_id = bos.department_id ;
1540
1541
1542
1543 x_phase := 'V';
1544 if g_debug = 1 then
1545 fnd_file.put_line(fnd_file.log, 'Before Stage LRL Phase V');
1546 end if ;
1547
1548
1549 -- Summarize the information inserted in the previous statement
1550 -- across the various days, as we do not have show the resource
1551 -- load by day, but we aggregate the information across the days
1552 -- for each resource.
1553 insert into wip_indicators_temp (
1554 group_id,
1555 organization_id,
1556 resource_id,
1557 resource_code,
1558 department_id,
1559 department_code,
1560 transaction_date,
1561 required_hours,
1562 indicator_type,
1563 process_phase,
1564 last_update_date,
1565 last_updated_by,
1566 creation_date,
1567 created_by,
1568 program_application_id )
1569 select
1570 wit.group_id,
1571 wit.organization_id,
1572 wit.resource_id,
1573 wit.resource_code,
1574 wit.department_id,
1575 wit.department_code,
1576 wit.transaction_date,
1577 sum(wit.required_hours),
1578 WIP_LINE_RL, -- Indicator Type
1579 WIP_LINE_RL_PHASE_TWO, -- process phase
1580 wit.last_update_date,
1581 wit.last_updated_by,
1582 wit.creation_date,
1583 wit.created_by,
1584 wit.program_application_id
1585 from
1586 wip_indicators_temp wit
1587 where
1588 wit.group_id = x_group_id
1589 and wit.indicator_type = WIP_LINE_RL
1590 and wit.process_phase = WIP_LINE_RL_PHASE_ONE
1591 group by
1592 wit.group_id,
1593 wit.organization_id,
1594 wit.resource_id,
1595 wit.resource_code,
1596 wit.department_id,
1597 wit.department_code,
1598 wit.transaction_date,
1599 WIP_LINE_RL, -- Indicator Type
1600 WIP_LINE_RL_PHASE_TWO, -- process phase
1601 wit.last_update_date,
1602 wit.last_updated_by,
1603 wit.creation_date,
1604 wit.created_by,
1605 wit.program_application_id ;
1606
1607
1608
1609 x_phase := 'VI';
1610 if g_debug = 1 then
1611 fnd_file.put_line(fnd_file.log, 'Before Stage LRL Phase VI');
1612 end if ;
1613
1614
1615 -- Delete the non-aggregated resource load information that
1616 -- was inserted with the process phase = 1.
1617 delete from wip_indicators_temp
1618 where group_id = x_group_id
1619 and indicator_type = WIP_LINE_RL
1620 and process_phase = WIP_LINE_RL_PHASE_ONE ;
1621
1622
1623
1624 x_phase := 'VII';
1625 if g_debug = 1 then
1626 fnd_file.put_line(fnd_file.log, 'Before Stage LRL Phase VII');
1627 end if ;
1628
1629
1630 -- Update the gross availaibility for
1631 -- the various resources in the line operation
1632 -- across the various days that falls in the range that
1633 -- are specified as the parameters.
1634 UPDATE wip_indicators_temp wit
1635 SET wit.available_units = (
1636 select
1637 nvl(sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units),0)
1638 from
1639 mrp_net_resource_avail mnra
1640 where
1641 mnra.organization_id = wit.organization_id
1642 and mnra.resource_id = wit.resource_id
1643 and mnra.department_id = wit.department_id
1644 and trunc(mnra.shift_date) between x_date_from and x_date_to
1645 and simulation_set is null
1646 )
1647 where wit.group_id = x_group_id
1648 and wit.indicator_type = WIP_LINE_RL
1649 and process_phase = WIP_LINE_RL_PHASE_TWO ;
1650
1651
1652
1653 Exception
1654
1655 When others then
1656
1657 p_errnum := -1 ;
1658 p_errmesg := 'Failed in Line RL Phase : '||x_phase|| substr(SQLERRM,1,125);
1659 return ;
1660
1661 End Populate_Line_Resource_Load ;
1662
1663
1664 function get_Workday_factor
1665 (p_sched_start_date IN DATE,
1666 p_sched_completion_date IN DATE,
1667 p_date_from IN DATE,
1668 p_date_to IN DATE,
1669 p_resource_id IN NUMBER,
1670 p_organization_id IN NUMBER )
1671 return NUMBER IS
1672 x_total_days NUMBER ;
1673 x_sched_days NUMBER;
1674 x_workday_factor NUMBER;
1675 BEGIN
1676
1677
1678 /******************************************************************
1679
1680 The design can be thought of as shown below :
1681
1682 p_date_from p_date_to
1683 |------------------------------|
1684 (1)
1685 |----------|
1686 sch_start sch_end
1687 (2) (3)
1688 |-------------| |--------------------|
1689 sch_start sch_end sch_start sch_end
1690 (4)
1691 |--------------------------------------------|
1692 sch_start sch_end
1693
1694
1695 The Workday Factor for the various cases should be :
1696
1697 1. In this case it should be the fraction returned should
1698 be 1, so that we get the whole range.
1699
1700 2. In this case, the fraction returned should still be 1,
1701 eventhough the scheduled_start_date is less than the
1702 p_date_from, because the completed quantities are handled
1703 by the quantity_completed column in the wip_flow_schedules
1704
1705 3. For this case, the fraction returned is actually =
1706
1707 Number of calendar working days for the resource
1708 between sch_start and p_date_to
1709 ------------------------------------------------------
1710 Number of calendar working days for the resource
1711 between sch_start and sch_end
1712
1713 4. For this case, the fraction returned is actually =
1714
1715 Number of calendar working days for the resource
1716 between p_date_from and p_date_to
1717 ------------------------------------------------------
1718 Number of calendar working days for the resource
1719 between p_date_from and sch_end
1720
1721 As the fraction of the schedule before the p_date_from
1722 is actually handled by quantity_completed column in the
1723 wip_flow_schedules
1724
1725
1726 *********************************************************************/
1727
1728
1729 if (p_sched_start_date >= p_date_from) and (p_sched_completion_date <= p_date_to) then
1730
1731 x_workday_factor := 1 ;
1732
1733 elsif (p_sched_start_date <= p_date_from) and (p_sched_completion_date <= p_date_to) then
1734
1735 x_workday_factor := 1 ;
1736
1737 elsif (p_sched_start_date >= p_date_from ) and (p_sched_completion_date >= p_date_to) then
1738
1739 begin
1740
1741 select
1742 nvl(count(distinct shift_date),0)
1743 into
1744 x_sched_days
1745 from
1746 mrp_net_resource_avail
1747 where resource_id = p_resource_id
1748 and organization_id = p_organization_id
1749 and simulation_set is null
1750 and shift_date between p_sched_start_date and p_date_to ;
1751
1752 exception
1753 when others then
1754 x_sched_days := 0 ;
1755
1756 end ;
1757
1758 begin
1759
1760 select
1761 nvl(count(distinct shift_date),0)
1762 into
1763 x_total_days
1764 from
1765 mrp_net_resource_avail
1766 where resource_id = p_resource_id
1767 and organization_id = p_organization_id
1768 and simulation_set is null
1769 and shift_date between p_sched_start_date and p_sched_completion_date ;
1770
1771 exception
1772 when others then
1773 x_total_days := 0 ;
1774
1775 end ;
1776
1777
1778 x_workday_factor := x_sched_days/x_total_days ;
1779
1780
1781 elsif (p_sched_start_date < p_date_from ) and (p_sched_completion_date > p_date_to) then
1782
1783 begin
1784
1785 select
1786 nvl(count(distinct shift_date),0)
1787 into
1788 x_sched_days
1789 from
1790 mrp_net_resource_avail
1791 where resource_id = p_resource_id
1792 and organization_id = p_organization_id
1793 and simulation_set is null
1794 and shift_date between p_date_from and p_date_to ;
1795
1796 exception
1797 when others then
1798 x_sched_days := 0 ;
1799
1800 end ;
1801
1802 begin
1803
1804 select
1805 nvl(count(distinct shift_date),0)
1806 into
1807 x_total_days
1808 from
1809 mrp_net_resource_avail
1810 where resource_id = p_resource_id
1811 and organization_id = p_organization_id
1812 and simulation_set is null
1813 and shift_date between p_date_from and p_sched_completion_date ;
1814
1815 exception
1816 when others then
1817 x_total_days := 0 ;
1818
1819 end ;
1820
1821
1822 x_workday_factor := x_sched_days/x_total_days ;
1823
1824 end if ;
1825
1826
1827
1828 return x_workday_factor ;
1829
1830
1831 exception
1832
1833 when others then
1834 return 1 ;
1835
1836 End Get_Workday_Factor ;
1837
1838
1839 FUNCTION get_all_line_ops (
1840 p_rtg_sequence_id IN NUMBER,
1841 p_assy_item_id IN NUMBER,
1842 p_org_id IN NUMBER,
1843 p_alt_rtg_desig IN VARCHAR2 )
1844 RETURN VARCHAR2 IS
1845 ops_table bom_rtg_network_api.op_tbl_type ;
1846 i BINARY_INTEGER ;
1847 opcode VARCHAR2(4);
1848
1849 lineops VARCHAR2(30000);
1850 BEGIN
1851
1852 bom_rtg_network_api.get_all_line_ops(
1853 p_rtg_sequence_id => p_rtg_sequence_id,
1854 p_assy_item_id => p_assy_item_id,
1855 p_org_id => p_org_id,
1856 p_alt_rtg_desig => p_alt_rtg_desig,
1857 x_op_tbl => ops_table );
1858
1859
1860 IF (ops_table.COUNT > 0) then
1861
1862 FOR i IN ops_table.first..ops_table.last LOOP
1863
1864 select bso.operation_code into opcode
1865 from bom_standard_operations bso, bom_operation_sequences bos
1866 where bso.organization_id = p_org_id
1867 and bso.standard_operation_id = bos.standard_operation_id
1868 and bos.operation_sequence_id = ops_table(i).operation_sequence_id;
1869
1870 lineops := lineops || ops_table(i).operation_seq_num || ' ' ||
1871 opcode;
1872
1873 IF (i <> ops_table.last) THEN
1874 lineops := lineops || ',' ;
1875 END IF;
1876
1877 END LOOP;
1878
1879 END IF;
1880
1881
1882 RETURN lineops;
1883
1884 END get_all_line_ops;
1885
1886
1887 ---------------------------------------------------------------------
1888 FUNCTION check_last_line_op (
1889 p_rtg_sequence_id IN NUMBER,
1890 p_assy_item_id IN NUMBER,
1891 p_org_id IN NUMBER,
1892 p_alt_rtg_desig IN VARCHAR2,
1893 p_curr_line_op IN NUMBER )
1894 RETURN NUMBER IS
1895
1896 islast NUMBER;
1897 bislast BOOLEAN;
1898
1899 BEGIN
1900
1901 bislast := bom_rtg_network_api.check_last_line_op (
1902 p_rtg_sequence_id,
1903 p_assy_item_id,
1904 p_org_id,
1905 p_alt_rtg_desig,
1906 p_curr_line_op );
1907
1908 IF (bislast = TRUE) THEN
1909 islast := 1;
1910 ELSE
1911 islast := 2;
1912 END IF;
1913
1914 RETURN islast;
1915
1916 END check_last_line_op ;
1917
1918 ----------------------------------------------------------
1919 /* added for oracle timezone support in the workstation */
1920
1921 /* date only string to datetime string, assuming time component is 00:00:00 */
1922 function displaydate_to_displayDT(p_displaydate IN VARCHAR2) return VARCHAR2
1923 IS
1924 BEGIN
1925 return to_char(to_date(p_displaydate, fnd_date.output_mask), fnd_date.outputdt_mask);
1926 EXCEPTION WHEN OTHERS THEN
1927 return null;
1928 END;
1929
1930 /* take a client date only string and convert it to sever time, assuming time is 0 */
1931 function displaydate_to_date_tz(p_displaydate IN VARCHAR2) return DATE
1932 IS
1933 BEGIN
1934 return fnd_date.displaydt_to_date(displaydate_to_displayDT(p_displaydate ));
1935 EXCEPTION WHEN OTHERS THEN
1936 return null;
1937 END;
1938
1939 /* same as fnd_daet.displaydt_to_date, but caches exception so it
1940 can be used to validate the format too */
1941 function displaydt_to_date_tz(p_displaydt IN VARCHAR2) return DATE
1942 IS
1943 BEGIN
1944 return fnd_date.displaydt_to_date(p_displaydt);
1945 EXCEPTION WHEN OTHERS THEN
1946 return null;
1947 END;
1948
1949 /* given a server datetime, convert it to client time zone, and then
1950 return the date only part of the string, this is useful for
1951 bucketing the data according to client date */
1952 function date_to_displaydate_tz(p_date IN DATE) return VARCHAR2
1953 IS
1954 tmp VARCHAR2(255);
1955 ret VARCHAR2(255);
1956 BEGIN
1957 tmp := fnd_date.outputdt_mask;
1958 -- using date only mask to to the converstion
1959 fnd_date.outputdt_mask := fnd_date.output_mask;
1960 ret := fnd_date.date_to_displaydt(p_date);
1961 fnd_date.outputdt_mask := tmp;
1962
1963 return ret;
1964 END;
1965
1966 /* given a server datetime, convert it to client time zone and return it.
1967 * the return value contains the time portion */
1968 function date_to_displaydt_tz(p_date IN DATE) return VARCHAR2
1969 IS
1970 BEGIN
1971 return fnd_date.date_to_displayDT(p_date);
1972 EXCEPTION WHEN OTHERS THEN
1973 return null;
1974 END;
1975
1976
1977 /* for validating the display date */
1978 function is_validate_displaydate(p_date IN VARCHAR2) return VARCHAR2
1979 IS
1980 t_date DATE;
1981 ret VARCHAR2(4) := FND_API.G_TRUE;
1982 BEGIN
1983 BEGIN
1984 select to_date(p_date, fnd_date.output_mask)
1985 into t_date
1986 from dual;
1987 EXCEPTION WHEN OTHERS THEN
1988 ret := FND_API.G_FALSE;
1989 END;
1990 return ret;
1991 END;
1992
1993 /* for validating display datetime */
1994 function is_validate_displayDT(p_date IN VARCHAR2) return VARCHAR2
1995 IS
1996 t_date DATE;
1997 ret VARCHAR2(4) := FND_API.G_TRUE;
1998 BEGIN
1999 BEGIN
2000 select to_date(p_date, fnd_date.outputdt_mask)
2001 into t_date
2002 from dual;
2003 EXCEPTION WHEN OTHERS THEN
2004 ret := FND_API.G_FALSE;
2005 END;
2006 return ret;
2007 END;
2008
2009 /* convert server date to client date, by faking it. */
2010 function sdate_to_cdate(p_sdate IN DATE) return DATE
2011 IS
2012 BEGIN
2013 return to_date(fnd_date.date_to_displaydt(p_sdate), fnd_date.outputdt_mask);
2014 END;
2015
2016 /* convert a client date back to server date. use it carefully */
2017 function cdate_to_sdate(p_cdate IN DATE) return DATE
2018 IS
2019 BEGIN
2020 return fnd_date.displaydt_to_date( to_char(p_cdate, fnd_date.outputdt_mask) );
2021 END;
2022
2023
2024 /* returns the number of hours between the given datetimes */
2025 function calculate_dt_range(p_from_dt IN VARCHAR2,
2026 p_to_dt IN VARCHAR2) return VARCHAR2
2027 IS
2028 diff NUMBER; --difference in dt's, in days
2029 BEGIN
2030 BEGIN
2031 select to_date(p_to_dt,fnd_date.outputdt_mask) - to_date(p_from_dt,fnd_date.outputdt_mask)
2032 into diff
2033 from dual;
2034 EXCEPTION WHEN OTHERS THEN
2035 return FND_API.G_RET_STS_ERROR;
2036 END;
2037 return to_char(diff * 24); -- return in hours
2038 END;
2039
2040
2041 /* intialize time zone variables for workstation, TCF or fnd_global.initialize
2042 is supposed to initialize these because it makes more sense. However,
2043 they are not going to make the changes very soon so we have to do it ourselves. */
2044
2045 /* These code explicitly depends on fnd timezone patch. As a result, it cannot be
2046 comiled in an evnrionment that the fnd timezone patch has not been applied.
2047 This is a problem since we want the code to work whether timezone patch is applied
2048 or not.
2049
2050 So this procedure ends up not being used. Instead, the sql has been moved to java side,
2051 where sql is dynanmic. Though it will throw an exception if timezone patch is not there,
2052 but the exception can be catched and ignored.
2053 */
2054 procedure init_timezone(p_output_mask IN VARCHAR2, p_outputdt_mask IN VARCHAR2)
2055 IS
2056 BEGIN
2057 fnd_date.initialize(p_output_mask, p_outputdt_mask);
2058
2059 -- comment out the code because it depends on the fnd timezone patch.
2060 /*
2061 fnd_date.client_timezone_code := fnd_timezones.get_code( fnd_profile.value('CLIENT_TIMEZONE_ID'));
2062 fnd_date.server_timezone_code := fnd_timezones.get_code( fnd_profile.value('SERVER_TIMEZONE_ID'));
2063
2064 if( fnd_timezones.timezones_enabled = 'Y') then
2065 fnd_date.timezones_enabled := true;
2066 else
2067 fnd_date.timezones_enabled := false;
2068 end if;
2069 */
2070
2071 END;
2072
2073
2074 procedure check_attachment_and_contract(p_pkey1 in VARCHAR2,
2075 p_pkey2 in VARCHAR2,
2076 p_pkey3 in VARCHAR2,
2077 p_jobID in number,
2078 x_hasAttachement out nocopy VARCHAR2,
2079 x_hasContract out nocopy VARCHAR2) is
2080 l_result boolean;
2081 l_status varchar2(1);
2082 l_industry varchar2(1);
2083 l_num number := null;
2084 begin
2085 x_hasAttachement := 'N';
2086 x_hasContract := 'N';
2087
2088 x_hasAttachement := fnd_attachment_util_pkg.get_atchmt_exists(
2089 l_entity_name => 'WIP_DISCRETE_OPERATIONS',
2090 l_pkey1 => p_pkey1,
2091 l_pkey2 => p_pkey2,
2092 l_pkey3 => p_pkey3,
2093 l_pkey4 => NULL,
2094 l_pkey5 => NULL,
2095 l_function_name => 'WIP_WIPOPMDF',
2096 l_function_type => 'F');
2097
2098 l_result := fnd_installation.get(777, 777, l_status, l_industry);
2099
2100 if ( l_status <> 'I' ) then
2101 return;
2102 end if;
2103
2104 begin
2105 select count(*) into l_num
2106 from wip_discrete_jobs wdj,
2107 oke_k_deliverables_b okd,
2108 (select k_header_id,
2109 oke_k_security_pkg.get_k_access(k_header_id) acc
2110 from oke_k_deliverables_b) okh
2111 where wdj.wip_entity_id = p_jobID
2112 and okd.project_id = wdj.project_id
2113 and nvl(okd.task_id, -1) = nvl(wdj.task_id, -1)
2114 and okh.k_header_id = okd.k_header_id
2115 and okh.acc <> 'NONE';
2116 exception
2117 when others then
2118 return;
2119 end;
2120
2121 if ( l_num > 0 ) then
2122 x_hasContract := 'Y';
2123 end if;
2124
2125 end check_attachment_and_contract;
2126
2127
2128 END WIP_SFCB_UTILITIES;
[Home] [Help]