[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_WIP_PVT
Source
1 PACKAGE BODY JMF_SHIKYU_WIP_PVT AS
2 -- $Header: JMFVSKWB.pls 120.12.12010000.3 2010/03/17 13:34:54 abhissri ship $ --
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;
306 l_end_Date DATE;
307 l_load_type NUMBER;
308 l_group_id NUMBER;
309 l_wip_entity_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 --Debugging for bug 9315131
369 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
370 THEN
371 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
372 , G_PKG_NAME
373 , 'JMFVSKWB: wip_entity_id => ' ||
374 l_subcontract_orders_rec.wip_entity_id
375 );
376 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
377 , G_PKG_NAME
378 , 'JMFVSKWB: p_subcontract_po_shipment_id => ' ||
379 l_subcontract_orders_rec.subcontract_po_shipment_id
380 );
381 END IF;
382
383 IF p_action IN ('U','C')
384 THEN
385 Compute_Start_Date
386 ( p_need_by_date => p_need_by_date
387 , p_item_id => l_subcontract_orders_rec.osa_item_id
388 , p_oem_organization => l_subcontract_orders_rec.oem_organization_id
389 , p_tp_organization => l_subcontract_orders_rec.tp_organization_id
390 , p_quantity => l_quantity
391 , x_start_date => l_start_date
392 );
393
394 Compute_End_Date
395 ( p_start_date => l_start_date
396 , p_item_id => l_subcontract_orders_rec.osa_item_id
397 , p_oem_organization => l_subcontract_orders_rec.oem_organization_id
398 , p_tp_organization => l_subcontract_orders_rec.tp_organization_id
399 , p_quantity => l_quantity
400 , x_end_date => l_end_date
401 );
402
403 ELSE
404 l_start_date := p_need_by_date;
405
406 END IF;
407
408 --Debugging for bug 9315131
409 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
410 THEN
411 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
412 , G_PKG_NAME
413 , 'JMFVSKWB: l_start_date => ' ||
414 l_start_date
415 );
416 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
417 , G_PKG_NAME
418 , 'JMFVSKWB: l_end_date => ' ||
419 l_end_date
420 );
421 END IF;
422
423 SELECT wip_job_schedule_interface_s.nextval
424 INTO l_group_id
425 FROM DUAL;
426
427 SELECT wip_interface_s.nextval
428 INTO l_interface_id
429 FROM DUAL;
430
431 --Debugging for bug 9315131
432 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
433 THEN
434 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
435 , G_PKG_NAME
436 , 'JMFVSKWB: l_group_id => ' ||
437 l_group_id
438 );
439 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
440 , G_PKG_NAME
441 , 'JMFVSKWB: l_interface_id => ' ||
442 l_interface_id
443 );
444 END IF;
445
446 IF p_action IN ('U','D') AND
447 l_subcontract_orders_rec.wip_entity_id IS NOT NULL
448 THEN
449 SELECT scheduled_start_date
450 INTO l_orig_start_date
451 FROM wip_discrete_jobs
452 WHERE wip_entity_id = l_subcontract_orders_rec.wip_entity_id;
453 END IF;
454
455 --Debugging for bug 9315131
456 IF g_log_enabled = 'Y' THEN
457 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
458 THEN
459 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
460 , G_PKG_NAME
461 , 'Start date passed to WIP is '||l_start_date
462 );
463 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
464 , G_PKG_NAME
465 , 'End date passed to WIP is '||l_end_date
466 );
467 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
468 , G_PKG_NAME
469 , 'l_orig_start_date '||l_orig_start_date
470 );
471 END IF;
472 END IF;
473
474 INSERT INTO
475 WIP_JOB_SCHEDULE_INTERFACE
476 ( bom_revision_date
477 , last_update_date
478 , last_updated_by
479 , creation_date
480 , created_by
481 , created_by_name
482 , last_updated_by_name
483 , last_update_login
484 , wip_entity_id
485 , firm_planned_flag
486 , first_unit_start_date
487 , group_id
488 , job_name
489 , load_type
490 , organization_id
491 , primary_item_id
492 , process_phase
493 , process_status
494 , source_code
495 , start_quantity
496 , status_type
497 , project_id
498 , task_id
499 , allow_explosion
500 --, last_unit_completion_date
501 , net_quantity
502 , header_id
503 , interface_id
504 , scheduling_method
505 )
506 VALUES
507 ( DECODE(p_action,'U',to_date(null),'D',to_date(null),l_start_date) -- Bug 9244436. Used to_date for proper conversion
508 , sysdate
509 , FND_GLOBAL.USER_ID
510 , sysdate
511 , FND_GLOBAL.USER_ID
512 , FND_GLOBAL.USER_NAME
513 , FND_GLOBAL.USER_NAME
514 , FND_GLOBAL.USER_ID
515 , DECODE(p_action,'U',l_subcontract_orders_rec.wip_entity_id,
516 'D',l_subcontract_orders_rec.wip_entity_id,null)
517 , 1
518 , DECODE(p_action,'C',l_start_date,l_orig_start_date)
519 , l_group_id
520 , l_group_id||l_subcontract_orders_rec.subcontract_po_shipment_id
521 , DECODE(p_action,'C',1,3)
522 , l_subcontract_orders_rec.tp_organization_id
523 , l_subcontract_orders_rec.osa_item_id
524 , 2
525 , 1
526 , 'INV'
527 , DECODE(p_action,'C',l_quantity,null)
528 , DECODE(p_action,'D',7,'U',1,3)
529 , l_subcontract_orders_rec.project_id
530 , l_subcontract_orders_rec.task_id
531 , 'Y'
532 -- , DECODE(p_action,'C',l_end_date,null)
533 , DECODE(p_action,'C',l_quantity,null)
534 , l_group_id
535 , DECODE(p_action,'C',l_interface_id,null)
536 , 1
537 );
538
539 IF p_action = 'C'
540 THEN
541 WIP_MASSLOAD_PUB.CreateOneJob
542 ( p_interfaceID => l_interface_id
543 , p_validationLevel=> 0
544 , x_wipEntityID => l_wip_entity_id
545 , x_returnStatus => l_return_status
546 , x_errorMsg => l_error
547 );
548
549 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
550 AND l_wip_entity_id IS NOT NULL
551 THEN
552 -- Update JMF table with the wip job # if successful.
553 UPDATE jmf_subcontract_orders
554 SET wip_entity_id = l_wip_entity_id
555 , last_update_date = sysdate
556 , last_updated_by = FND_GLOBAL.user_id
557 , last_update_login = FND_GLOBAL.login_id
558 WHERE subcontract_po_shipment_id =
559 l_subcontract_orders_rec.subcontract_po_shipment_id;
560
561 --Debugging for bug 9315131
562 IF g_log_enabled = 'Y' THEN
563 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
564 THEN
565 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
566 , G_PKG_NAME
567 , 'Updated jso for shipment_id = '|| l_subcontract_orders_rec.subcontract_po_shipment_id
568 ||', wip_entity_id = ' || l_wip_entity_id
569 );
570 END IF;
571 END IF;
572
573 ELSE
574 --Debugging for bug 9315131
575 IF g_log_enabled = 'Y' THEN
576 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
577 THEN
578 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
579 , G_PKG_NAME
580 , 'Error from Mass Load: wip_entity_id = ' || l_wip_entity_id
581 );
582 END IF;
583 END IF;
584
585 FND_MESSAGE.set_name('JMF', 'JMF_SHK_WIP_CREATION_ERR');
586 FND_MSG_PUB.add;
587 END IF;
588 ELSE
589 --Update WIP job
590 WIP_MASSLOAD_PUB.MassLoadJobs
591 ( p_groupid => l_group_id
592 , p_validationlevel => 0
593 , p_commitflag => 0
594 , x_returnStatus => l_return_status
595 , x_errorMsg => l_error
596 );
597 END IF;
598
599 IF p_action IN ('U')
600 THEN
601
602 SELECT wip_job_schedule_interface_s.nextval
603 INTO l_group_id
604 FROM DUAL;
605
606 --Debugging for bug 9315131
607 IF g_log_enabled = 'Y' THEN
608 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
609 THEN
610 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
611 , G_PKG_NAME
612 , 'Came inside p_status U: group_id = ' || l_group_id
613 );
614 END IF;
615 END IF;
616
617 INSERT INTO
618 WIP_JOB_SCHEDULE_INTERFACE
619 ( bom_revision_date
620 , last_update_date
621 , last_updated_by
622 , creation_date
623 , created_by
624 , created_by_name
625 , last_updated_by_name
626 , last_update_login
627 , wip_entity_id
628 , firm_planned_flag
629 , first_unit_start_date
630 , group_id
631 , job_name
632 , load_type
633 , organization_id
634 , primary_item_id
635 , process_phase
636 , process_status
637 , source_code
638 , start_quantity
639 , status_type
640 , project_id
641 , task_id
642 , allow_explosion
643 , last_unit_completion_date
644 , net_quantity
645 , header_id
646 , interface_id
647 , scheduling_method
648 )
649 VALUES
650 ( l_start_date
651 , sysdate
652 , FND_GLOBAL.USER_ID
653 , sysdate
654 , FND_GLOBAL.USER_ID
655 , FND_GLOBAL.USER_NAME
656 , FND_GLOBAL.USER_NAME
657 , FND_GLOBAL.USER_ID
658 , l_subcontract_orders_rec.wip_entity_id
659 , 1
660 , l_start_date
661 , l_group_id
662 , l_group_id||l_subcontract_orders_rec.subcontract_po_shipment_id
663 , 3
664 , l_subcontract_orders_rec.tp_organization_id
665 , l_subcontract_orders_rec.osa_item_id
666 , 2
667 , 1
668 , 'INV'
669 , l_quantity
670 , 3
671 , l_subcontract_orders_rec.project_id
672 , l_subcontract_orders_rec.task_id
673 , 'Y'
674 , l_end_date
675 , l_quantity
676 , l_group_id
677 , null
678 , 1
679 );
680
681 WIP_MASSLOAD_PUB.MassLoadJobs
682 ( p_groupid => l_group_id
683 , p_validationlevel => 0
684 , p_commitflag => 0
685 , x_returnStatus => l_return_status
686 , x_errorMsg => l_error
687 );
688
689 --Debugging for bug 9315131
690 IF g_log_enabled = 'Y' THEN
691 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
692 THEN
693 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
694 , G_PKG_NAME
695 , 'Came inside p_status U: l_return_status = ' || l_return_status
696 ||': l_error = ' || l_error
697 );
698 END IF;
699 END IF;
700
701 END IF;
702
703 x_return_status := l_return_status;
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 x_return_status := FND_API.G_RET_STS_ERROR;
708 FND_MESSAGE.set_name('JMF', 'JMF_SHK_WIP_CREATION_ERR');
709 FND_MSG_PUB.add;
710
711 END Process_WIP_Job;
712
713
714 --========================================================================
715 -- FUNCTION : Get_Component_Quantity PUBLIC
716 -- PARAMETERS:
717 -- p_item_id Item
718 -- p_subcontract_po_shipment_id Shipment Id
719 -- p_organization_id Organization
720 -- COMMENT : This procedure computes the quantity of the component
721 -- as defined in the BOM in primary UOM
722 --========================================================================
723 FUNCTION Get_Component_Quantity
724 ( p_item_id IN NUMBER
725 , p_organization_id IN NUMBER
726 , p_subcontract_po_shipment_id IN NUMBER
727 ) RETURN NUMBER
728 IS
729 l_quantity NUMBER;
730 BEGIN
731
732 IF p_subcontract_po_shipment_id IS NULL
733 THEN
734 SELECT bc.component_quantity
735 INTO l_quantity
736 FROM bom_bill_of_materials bom
737 , bom_components_b bc
738 WHERE bom.bill_sequence_id = bc.bill_sequence_id
739 AND bc.operation_seq_num =1
740 AND bc.component_item_id = p_item_id
741 AND bom.organization_id = p_organization_id
742 AND sysdate BETWEEN (bc.effectivity_date)
743 AND (NVL(bc.disable_date,sysdate+1));
744 ELSE
745 SELECT wro.required_quantity
746 INTO l_quantity
747 FROM wip_requirement_operations wro
748 , jmf_subcontract_orders jso
749 WHERE wro.wip_entity_id = jso.wip_entity_id
750 AND wro.inventory_item_id = p_item_id
751 AND wro.organization_id = jso.tp_organization_id
752 AND wro.organization_id = p_organization_id
753 AND jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
754
755 END IF;
756
757 --Debugging for bug 9315131
758 IF g_log_enabled = 'Y' THEN
759 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
760 THEN
761 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
762 , G_PKG_NAME
763 , 'Get_Component_Quantity: l_quantity = ' || l_quantity
764 );
765 END IF;
766 END IF;
767
768 RETURN l_quantity;
769
770 EXCEPTION
771 WHEN NO_DATA_FOUND THEN
772 RETURN 0;
773
774 END Get_Component_Quantity;
775
776 END JMF_SHIKYU_WIP_PVT;