[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_WIP_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_WIP_PVT AS
2 -- $Header: JMFVSKWB.pls 120.12 2006/05/18 13:46 rajkrish noship $ --
3 --+=======================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JMFVSKWB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package contains WIP related calls that the Interlock |
13 --| accesses when processing SHIKYU transactions |
14 --| HISTORY |
15 --| 05/09/2005 pseshadr Created |
16 --| 12/14/2005 vchu Modified Get_Component_Quantity to return |
17 --| required_quantity from |
18 --| wip_requirement_operations instead of |
19 --| wro.required_quantity - wro.quantity_issued |
20 --| 03/27/2006 vchu Fixed bug 5090721: Set last_update_date, |
21 --| last_updated_by and last_update_login in the |
22 --| update statements. |
23 --| 05/12/2006 vchu Fixed bug 5199024: Modified Compute_Start_Date |
24 --| to skip non working days to the previous |
25 --| working day if the initial calculation for |
26 --| x_start_date (need by date - intransit lead time|
27 --| - item lead time - number of off days between |
28 --| the estimated start date and the need by date) |
29 --| actually landed on a non working day. |
30 --+=======================================================================+
31
32 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JMF_SHIKYU_WIP_PVT';
33 g_log_enabled VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
34
35 --===================
36 -- PROCEDURES AND FUNCTIONS
37 --===================
38 --========================================================================
39 -- PROCEDURE : Compute_Start_Date PUBLIC
40 -- PARAMETERS:
41 -- p_need_by_Date Need By Date
42 -- p_item_id Item
43 -- p_organization Organization
44 -- p_quantity Quantity
45 -- COMMENT : This procedure computes the planned start date for the WIP job
46 -- based on the need_by_date
47 --========================================================================
48 PROCEDURE Compute_Start_Date
49 ( p_need_by_date IN DATE
50 , p_item_id IN NUMBER
51 , p_oem_organization IN NUMBER
52 , p_tp_organization IN NUMBER
53 , p_quantity IN NUMBER
54 , x_start_date OUT NOCOPY DATE
55 )
56 IS
57 l_fixed_time NUMBER;
58 l_var_time NUMBER;
59 l_intransit_time NUMBER;
60 l_program CONSTANT VARCHAR2(30) := 'Compute_Start_Date';
61 l_start_date DATE;
62 l_off_days NUMBER;
63 l_ct_off_days NUMBER;
64 l_cal_date DATE;
65
66 l_seq_num NUMBER;
67 l_prior_date DATE;
68
69 CURSOR c_interorg IS
70 SELECT NVL(intransit_time,0)
71 FROM mtl_interorg_ship_methods
72 WHERE from_organization_id = p_tp_organization
73 AND to_organization_id = p_oem_organization
74 AND default_flag =1;
75
76 BEGIN
77
78 IF g_log_enabled = 'Y' AND
79 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
80 THEN
81 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
82 , G_PKG_NAME
83 , '>> ' || G_PKG_NAME || '.' || l_program || ': Start'
84 );
85 END IF;
86
87 IF g_log_enabled = 'Y' AND
88 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
89 THEN
90 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
91 , G_PKG_NAME
92 , '>> ' || l_program || ': p_oem_organization = '
93 || p_oem_organization
94 || ', p_tp_organization = ' || p_tp_organization
95 || ', p_item_id = ' || p_item_id
96 );
97 END IF;
98
99 SELECT fixed_lead_time
100 , variable_lead_time
101 INTO
102 l_fixed_time
103 , l_var_time
104 FROM mtl_system_items_b
105 WHERE inventory_item_id = p_item_id
106 AND organization_id = p_tp_organization;
107
108 OPEN c_interorg;
109 FETCH c_interorg
110 INTO l_intransit_time;
111 IF c_interorg%NOTFOUND
112 THEN
113 l_intransit_time :=0;
114 END IF;
115 CLOSE c_interorg;
116
117 l_start_date := p_need_by_date - l_intransit_time
118 - ROUND(NVL(l_fixed_time,0) + (p_quantity*NVL(l_var_time,0)));
119
120 IF g_log_enabled = 'Y' AND
121 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
122 THEN
123 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
124 , G_PKG_NAME
125 , '>> '||l_program||': l_start_date (before considering off days) = '
126 || TO_CHAR(l_start_date, 'YYYY-MON-DD HH24:MI:SS')
127 );
128 END IF;
129
130 -- To consider the workday calendars when computing WIP start dates
131 -- since on/off days should be considered.
132
133 SELECT count(1)
134 INTO l_off_days
135 FROM bom_calendar_dates bcd
136 , mtl_parameters mp
137 WHERE bcd.calendar_code = mp.calendar_code
138 AND mp.organization_id = p_tp_organization
139 AND bcd.calendar_date BETWEEN TRUNC(l_start_date) AND TRUNC(p_need_by_date)
140 AND seq_num IS NULL;
141
142 IF g_log_enabled = 'Y' AND
143 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
144 THEN
145 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
146 , G_PKG_NAME
147 , '>> '||l_program||': l_off_days = '|| l_off_days
148 );
149 END IF;
150
151 l_start_date := p_need_by_date - l_off_days - l_intransit_time
152 - ROUND(NVL(l_fixed_time,0) + (p_quantity*NVL(l_var_time,0)));
153
154 IF g_log_enabled = 'Y' AND
155 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
156 THEN
157 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
158 , G_PKG_NAME
159 , '>> '||l_program||': l_start_date = '
160 || TO_CHAR(l_start_date, 'YYYY-MON-DD HH24:MI:SS')
161 );
162 END IF;
163
164 SELECT bcd.seq_num,
165 bcd.prior_date
166 INTO l_seq_num,
167 l_prior_date
168 FROM bom_calendar_dates bcd,
169 mtl_parameters mp
170 WHERE bcd.calendar_code = mp.calendar_code
171 AND mp.organization_id = p_tp_organization
172 AND TRUNC(bcd.calendar_date) = TRUNC(l_start_date);
173
174 IF g_log_enabled = 'Y' AND
175 (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
176 THEN
177 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
178 , G_PKG_NAME
179 , '>> '||l_program||': From bom_calendar_dates: l_seq_num = '|| NVL(TO_CHAR(l_seq_num), 'NULL')
180 || ', l_prior_date = ' || l_prior_date
181 );
182 END IF;
183
184 -- l_start_date is an off date if its seq_num is NULL
185 IF l_seq_num IS NULL
186 THEN
187
188 l_start_date := l_prior_date;
189
190 END IF;
191
192 -- May need to skip off days if sysdate happens to be an off day?
193 IF l_start_date < sysdate
194 THEN
195 l_start_date := sysdate;
196 END IF;
197
198 x_start_date := l_start_date;
199
200 IF g_log_enabled = 'Y' THEN
201 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
202 THEN
203 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
204 , G_PKG_NAME
205 , '>> '||l_program||'Planned Date is '||x_start_date
206 );
207 END IF;
208 END IF;
209
210 END Compute_Start_Date;
211
212
213 --========================================================================
214 -- PROCEDURE : Compute_End_Date PUBLIC
215 -- PARAMETERS:
216 -- p_need_by_Date Need By Date
217 -- p_item_id Item
218 -- p_organization Organization
219 -- p_quantity Quantity
220 -- COMMENT : This procedure computes the planned completion date for the WIP job
221 -- based on the need_by_date
222 --========================================================================
223 PROCEDURE Compute_End_Date
224 ( p_start_date IN DATE
225 , p_item_id IN NUMBER
226 , p_oem_organization IN NUMBER
227 , p_tp_organization IN NUMBER
228 , p_quantity IN NUMBER
229 , x_end_date OUT NOCOPY DATE
230 )
231 IS
232 l_fixed_time NUMBER;
233 l_var_time NUMBER;
234 l_intransit_time NUMBER;
235 l_program CONSTANT VARCHAR2(30) := 'Compute_End_Date';
236
237 CURSOR c_interorg IS
238 SELECT NVL(intransit_time,0)
239 FROM mtl_interorg_ship_methods
240 WHERE from_organization_id = p_tp_organization
241 AND to_organization_id = p_oem_organization
242 AND default_flag =1;
243
244 BEGIN
245 SELECT fixed_lead_time
246 , variable_lead_time
247 INTO
248 l_fixed_time
249 , l_var_time
250 FROM mtl_system_items_b
251 WHERE inventory_item_id = p_item_id
252 AND organization_id = p_tp_organization;
253
254 OPEN c_interorg;
255 FETCH c_interorg
256 INTO l_intransit_time;
257 IF c_interorg%NOTFOUND
258 THEN
259 l_intransit_time :=0;
260 END IF;
261 CLOSE c_interorg;
262
263 x_end_date := p_start_date
264 + (ROUND(NVL(l_fixed_time,0) + (p_quantity*NVL(l_var_time,0))));
265 IF x_end_date < sysdate
266 THEN
267 x_end_date := sysdate;
268 END IF;
269
270 IF g_log_enabled = 'Y' THEN
271 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
272 THEN
273 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
274 , G_PKG_NAME
275 , '>> '||l_program||'Planned end Date is '||x_end_date
276 );
277 END IF;
278 END IF;
279
280 END Compute_End_Date;
281
282 --========================================================================
283 -- PROCEDURE : Process_WIP_Job PUBLIC
284 -- PARAMETERS: p_action Action
285 -- 'C'- Create new job
286 -- 'D'- Delete Job
287 -- 'U'- Update Job
288 -- 'R'- Assembly Return
289 -- x_return_status Return Status
290 -- COMMENT : This procedure populates data in the interface table
291 -- to process the WIP job. The WIP load procedure is invoked
292 -- which creates the WIP job.
293 --========================================================================
294 PROCEDURE Process_WIP_Job
295 ( p_action IN VARCHAR2
296 , p_subcontract_po_shipment_id IN NUMBER
297 , p_need_by_date IN DATE
298 , p_quantity IN NUMBER
299 , x_return_status OUT NOCOPY VARCHAR2
300 )
301 IS
302 l_project_id NUMBER;
303 l_task_id NUMBER;
304 l_quantity NUMBER;
305 l_start_date DATE;
309 l_wip_entity_id NUMBER;
306 l_end_Date DATE;
307 l_load_type NUMBER;
308 l_group_id NUMBER;
310 l_return_status VARCHAR2(1);
311 l_error VARCHAR2(2000);
312 l_subcontract_orders_rec JMF_SUBCONTRACT_ORDERS%ROWTYPE;
313 l_need_by_date DATE;
314 l_component_id NUMBER;
315 l_total_qty NUMBER;
316 l_issued_qty NUMBER;
317 l_interface_id NUMBER;
318 l_orig_start_date DATE;
319
320
321 CURSOR c_rec IS
322 SELECT *
323 FROM jmf_subcontract_orders
324 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id;
325
326 CURSOR c_comp_rec IS
327 SELECT shikyu_component_id
328 , quantity
329 FROM jmf_shikyu_components
330 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id;
331 BEGIN
332
333 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
334 THEN
335 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
336 , G_PKG_NAME
337 , 'JMFVSKWB: Into process_wip_job package'
338 );
339 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
340 , G_PKG_NAME
341 , 'JMFVSKWB: p_subcontract_po_shipment_id => ' ||
342 p_subcontract_po_shipment_id
343 );
344 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
345 , G_PKG_NAME
346 , 'JMFVSKWB: p_action => ' ||
347 p_action
348 );
349 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
350 , G_PKG_NAME
351 , 'JMFVSKWB: p_need_by_date => '||
352 p_need_by_date
353 );
354 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
355 , G_PKG_NAME
356 , 'JMFVSKWB: p_quantity => '||
357 p_quantity
358 );
359 END IF;
360
361
362 OPEN c_rec;
363 FETCH c_rec INTO l_subcontract_orders_rec;
364 CLOSE c_rec;
365
366 l_quantity := p_quantity;
367
368 IF p_action IN ('U','C')
369 THEN
370 Compute_Start_Date
371 ( p_need_by_date => p_need_by_date
372 , p_item_id => l_subcontract_orders_rec.osa_item_id
373 , p_oem_organization => l_subcontract_orders_rec.oem_organization_id
374 , p_tp_organization => l_subcontract_orders_rec.tp_organization_id
375 , p_quantity => l_quantity
376 , x_start_date => l_start_date
377 );
378
379 Compute_End_Date
380 ( p_start_date => l_start_date
381 , p_item_id => l_subcontract_orders_rec.osa_item_id
382 , p_oem_organization => l_subcontract_orders_rec.oem_organization_id
383 , p_tp_organization => l_subcontract_orders_rec.tp_organization_id
384 , p_quantity => l_quantity
385 , x_end_date => l_end_date
386 );
387
388 ELSE
389 l_start_date := p_need_by_date;
390
391 END IF;
392
393 SELECT wip_job_schedule_interface_s.nextval
394 INTO l_group_id
395 FROM DUAL;
396
397 SELECT wip_interface_s.nextval
398 INTO l_interface_id
399 FROM DUAL;
400
401 IF p_action IN ('U','D') AND
402 l_subcontract_orders_rec.wip_entity_id IS NOT NULL
403 THEN
404 SELECT scheduled_start_date
405 INTO l_orig_start_date
406 FROM wip_discrete_jobs
407 WHERE wip_entity_id = l_subcontract_orders_rec.wip_entity_id;
408 END IF;
409
410 IF g_log_enabled = 'Y' THEN
411 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
412 THEN
413 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
414 , G_PKG_NAME
415 , 'Start date passed to WIP is '||l_start_date
416 );
417 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
418 , G_PKG_NAME
419 , 'End date passed to WIP is '||l_end_date
420 );
421 END IF;
422 END IF;
423
424 INSERT INTO
425 WIP_JOB_SCHEDULE_INTERFACE
426 ( bom_revision_date
427 , last_update_date
428 , last_updated_by
429 , creation_date
430 , created_by
431 , created_by_name
432 , last_updated_by_name
433 , last_update_login
434 , wip_entity_id
435 , firm_planned_flag
436 , first_unit_start_date
437 , group_id
438 , job_name
439 , load_type
440 , organization_id
441 , primary_item_id
442 , process_phase
443 , process_status
444 , source_code
445 , start_quantity
446 , status_type
447 , project_id
448 , task_id
449 , allow_explosion
450 --, last_unit_completion_date
451 , net_quantity
452 , header_id
453 , interface_id
454 , scheduling_method
455 )
456 VALUES
457 ( DECODE(p_action,'U',null,'D',null,l_start_date)
458 , sysdate
459 , FND_GLOBAL.USER_ID
460 , sysdate
461 , FND_GLOBAL.USER_ID
462 , FND_GLOBAL.USER_NAME
463 , FND_GLOBAL.USER_NAME
464 , FND_GLOBAL.USER_ID
465 , DECODE(p_action,'U',l_subcontract_orders_rec.wip_entity_id,
466 'D',l_subcontract_orders_rec.wip_entity_id,null)
467 , 1
468 , DECODE(p_action,'C',l_start_date,l_orig_start_date)
469 , l_group_id
470 , l_group_id||l_subcontract_orders_rec.subcontract_po_shipment_id
471 , DECODE(p_action,'C',1,3)
472 , l_subcontract_orders_rec.tp_organization_id
473 , l_subcontract_orders_rec.osa_item_id
474 , 2
475 , 1
476 , 'INV'
480 , l_subcontract_orders_rec.task_id
477 , DECODE(p_action,'C',l_quantity,null)
478 , DECODE(p_action,'D',7,'U',1,3)
479 , l_subcontract_orders_rec.project_id
481 , 'Y'
482 -- , DECODE(p_action,'C',l_end_date,null)
483 , DECODE(p_action,'C',l_quantity,null)
484 , l_group_id
485 , DECODE(p_action,'C',l_interface_id,null)
486 , 1
487 );
488
489 IF p_action = 'C'
490 THEN
491 WIP_MASSLOAD_PUB.CreateOneJob
492 ( p_interfaceID => l_interface_id
493 , p_validationLevel=> 0
494 , x_wipEntityID => l_wip_entity_id
495 , x_returnStatus => l_return_status
496 , x_errorMsg => l_error
497 );
498
499 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
500 AND l_wip_entity_id IS NOT NULL
501 THEN
502 -- Update JMF table with the wip job # if successful.
503 UPDATE jmf_subcontract_orders
504 SET wip_entity_id = l_wip_entity_id
505 , last_update_date = sysdate
506 , last_updated_by = FND_GLOBAL.user_id
507 , last_update_login = FND_GLOBAL.login_id
508 WHERE subcontract_po_shipment_id =
509 l_subcontract_orders_rec.subcontract_po_shipment_id;
510 ELSE
511 FND_MESSAGE.set_name('JMF', 'JMF_SHK_WIP_CREATION_ERR');
512 FND_MSG_PUB.add;
513 END IF;
514 ELSE
515 --Update WIP job
516 WIP_MASSLOAD_PUB.MassLoadJobs
517 ( p_groupid => l_group_id
518 , p_validationlevel => 0
519 , p_commitflag => 0
520 , x_returnStatus => l_return_status
521 , x_errorMsg => l_error
522 );
523 END IF;
524
525 IF p_action IN ('U')
526 THEN
527
528 SELECT wip_job_schedule_interface_s.nextval
529 INTO l_group_id
530 FROM DUAL;
531
532 INSERT INTO
533 WIP_JOB_SCHEDULE_INTERFACE
534 ( bom_revision_date
535 , last_update_date
536 , last_updated_by
537 , creation_date
538 , created_by
539 , created_by_name
540 , last_updated_by_name
541 , last_update_login
542 , wip_entity_id
543 , firm_planned_flag
544 , first_unit_start_date
545 , group_id
546 , job_name
547 , load_type
548 , organization_id
549 , primary_item_id
550 , process_phase
551 , process_status
552 , source_code
553 , start_quantity
554 , status_type
555 , project_id
556 , task_id
557 , allow_explosion
558 , last_unit_completion_date
559 , net_quantity
560 , header_id
561 , interface_id
562 , scheduling_method
563 )
564 VALUES
565 ( l_start_date
566 , sysdate
567 , FND_GLOBAL.USER_ID
568 , sysdate
569 , FND_GLOBAL.USER_ID
570 , FND_GLOBAL.USER_NAME
571 , FND_GLOBAL.USER_NAME
572 , FND_GLOBAL.USER_ID
573 , l_subcontract_orders_rec.wip_entity_id
574 , 1
575 , l_start_date
576 , l_group_id
577 , l_group_id||l_subcontract_orders_rec.subcontract_po_shipment_id
578 , 3
579 , l_subcontract_orders_rec.tp_organization_id
580 , l_subcontract_orders_rec.osa_item_id
581 , 2
582 , 1
583 , 'INV'
584 , l_quantity
585 , 3
586 , l_subcontract_orders_rec.project_id
587 , l_subcontract_orders_rec.task_id
588 , 'Y'
589 , l_end_date
590 , l_quantity
591 , l_group_id
592 , null
593 , 1
594 );
595
596 WIP_MASSLOAD_PUB.MassLoadJobs
597 ( p_groupid => l_group_id
598 , p_validationlevel => 0
599 , p_commitflag => 0
600 , x_returnStatus => l_return_status
601 , x_errorMsg => l_error
602 );
603 END IF;
604
605 x_return_status := l_return_status;
606
607 EXCEPTION
608 WHEN OTHERS THEN
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 FND_MESSAGE.set_name('JMF', 'JMF_SHK_WIP_CREATION_ERR');
611 FND_MSG_PUB.add;
612
613 END Process_WIP_Job;
614
615
616 --========================================================================
617 -- FUNCTION : Get_Component_Quantity PUBLIC
618 -- PARAMETERS:
619 -- p_item_id Item
620 -- p_subcontract_po_shipment_id Shipment Id
621 -- p_organization_id Organization
622 -- COMMENT : This procedure computes the quantity of the component
623 -- as defined in the BOM in primary UOM
624 --========================================================================
625 FUNCTION Get_Component_Quantity
626 ( p_item_id IN NUMBER
627 , p_organization_id IN NUMBER
628 , p_subcontract_po_shipment_id IN NUMBER
629 ) RETURN NUMBER
630 IS
631 l_quantity NUMBER;
632 BEGIN
633
634 IF p_subcontract_po_shipment_id IS NULL
635 THEN
636 SELECT bc.component_quantity
637 INTO l_quantity
638 FROM bom_bill_of_materials bom
639 , bom_components_b bc
640 WHERE bom.bill_sequence_id = bc.bill_sequence_id
641 AND bc.operation_seq_num =1
642 AND bc.component_item_id = p_item_id
643 AND bom.organization_id = p_organization_id
644 AND sysdate BETWEEN (bc.effectivity_date)
645 AND (NVL(bc.disable_date,sysdate+1));
646 ELSE
647 SELECT wro.required_quantity
648 INTO l_quantity
649 FROM wip_requirement_operations wro
650 , jmf_subcontract_orders jso
651 WHERE wro.wip_entity_id = jso.wip_entity_id
652 AND wro.inventory_item_id = p_item_id
653 AND wro.organization_id = jso.tp_organization_id
654 AND wro.organization_id = p_organization_id
655 AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
656
657 END IF;
658
659 RETURN l_quantity;
660
661 EXCEPTION
662 WHEN NO_DATA_FOUND THEN
663 RETURN 0;
664
665 END Get_Component_Quantity;
666
667 END JMF_SHIKYU_WIP_PVT;