[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_UTIL
Source
1 PACKAGE BODY JMF_SHIKYU_UTIL as
2 --$Header: JMFUSHKB.pls 120.17 2007/10/10 06:47:06 kdevadas ship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFUSHKB.pls |
10 --| |
11 --| DESCRIPTION: Package body file for the Utility package |
12 --| of the Charge Based SHIKYU project. |
13 --| |
14 --| HISTORY: |
15 --| 29-APR-2005 vchu Created. |
16 --| 28-SEP-2005 vchu Corrected where clause of the query in the |
17 --| Get_Shikyu_Component_Price procedure |
18 --| 28-SEP-2005 vchu Modified signature of the |
19 --| Get_Shikyu_Component_Price procedure |
20 --| 03-OCT-2005 shu Added the debug_output procedure |
21 --| 21-OCT-2005 vchu Added the Get_Shikyu_Offset_Account |
22 --| procedure |
23 --| 13-DEC-2005 vchu Modified Get_Subcontract_Allocated_Qty to |
24 --| return 0 if there are no existing allocations|
25 --| for the subcontracting order specified |
26 --| 15-FEB-2005 vchu Modified Get_Allocation_Date to return the |
27 --| scheduled_start_date of the WIP job if it |
28 --| has not already started, and the |
29 --| scheduled_completion_date otherwise. |
30 --| 13-JUN-2006 rajkrish Changed the request options for workers |
31 --| so that it willl be visible |
32 --| 26-JUN-2006 nesoni Function Get_Replenish_So_Returned_Qty is |
33 --| modified. |
34 --| 30-AUG-2006 rajkrish Added the new procedure clean_invalid_data |
35 --| 08-SEP-2006 vchu Added the new function To_Xsd_Date_String |
36 --| to convert date values into XSD format so |
37 --| that they can be formatted correctly in XML |
38 --| Publisher Reports. |
39 --| 19-SEP-2006 vchu Modified the function To_Xsd_Date_String |
40 --| to take a second optional parameter that |
41 --| denotes the timezone of the offset to be |
42 --| attached to the DateTime string (either |
43 --| Server or Client). |
44 --| 20-SEP-2006 vchu Modified the function To_Xsd_Date_String |
45 --| to take a second optional parameter that |
46 --| denotes whether the time component of the |
47 --| Oracle Date should be omitted. No need for |
48 --| this function to optionally attach the User |
49 --| Timezone offset since it has been decided |
50 --| that UPTZ would not be supported for R12. |
51 --| 04-OCT-2007 kdevadas 12.1 Buy/Sell Subcontracting changes |
52 --| Reference - GBL_BuySell_TDD.doc |
53 --| Reference - GBL_BuySell_FDD.doc |
54 --+===========================================================================+
55
56 --=============================================
57 -- CONSTANTS
58 --=============================================
59 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'jmf.plsql.' || G_PKG_NAME || '.';
60
61 --=============================================
62 -- GLOBAL VARIABLES
63 --=============================================
64
65 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
66
67 g_submit_failure_exc EXCEPTION;
68
69 --========================================================================
70 -- FUNCTION : Get_Primary_Uom PUBLIC
71 -- PARAMETERS: p_inventory_item_id Item
72 -- p_organization_id Inventory Organization
73 -- COMMENT : This function returns the name of the primary UOM
74 -- of the item specified by the input parameters
75 --========================================================================
76
77 FUNCTION Get_Primary_Uom
78 ( p_inventory_item_id IN NUMBER
79 , p_organization_id IN NUMBER
80 )
81 RETURN VARCHAR2
82 IS
83
84 l_api_name CONSTANT VARCHAR2(30) := 'Get_Primary_Uom';
85 l_uom VARCHAR2(25);
86
87 BEGIN
88
89 IF g_fnd_debug = 'Y' AND
90 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
91
92 THEN
93 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
94 , G_MODULE_PREFIX || l_api_name || '.begin'
95 , NULL);
96 END IF;
97
98 SELECT primary_unit_of_measure
99 INTO l_uom
100 FROM mtl_system_items
101 WHERE inventory_item_id = p_inventory_item_id
102 AND organization_id = p_organization_id;
103
104 IF g_fnd_debug = 'Y' AND
105 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
106 THEN
107 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
108 , G_MODULE_PREFIX || l_api_name || '.end'
109 , NULL);
110 END IF;
111
112 RETURN l_uom;
113
114 END Get_Primary_Uom;
115
116
117 --===================================================================
118 -- PROCEDURE : clean_invalid_data
119 -- PARAMETERS :
120 -- COMMENTS : This procedure will clean/freeze the invalid data in the
121 -- SHIKYu tables if the data is corrupted
122 --=====================================================================
123
124 PROCEDURE clean_invalid_data
125
126 IS
127
128
129 BEGIN
130
131 --- The SCO records are updated to interlock status T of the
132 --- parent PO is cancelled and interlock has not yet processed it
133
134 IF g_fnd_debug = 'Y' AND
135 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
136 THEN
137 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
138 , 'JMFUSHKB: clean_invalid_data INTO '
139 , 'IN');
140 END IF;
141
142
143 UPDATE JMF_SUBCONTRACT_ORDERS sco
144 SET sco.INTERLOCK_STATUS = 'T'
145 WHERE sco.INTERLOCK_STATUS IN ('N','E','U' )
146 AND sco.SUBCONTRACT_PO_SHIPMENT_ID IN
147 ( SELECT poll.line_location_id
148 FROM po_headers_all poh
149 , po_lines_all pol
150 , po_line_locations_all poll
151 WHERE poh.po_header_id = sco.SUBCONTRACT_PO_HEADER_ID
152 AND pol.po_line_id = sco.SUBCONTRACT_PO_LINE_ID
153 AND poll.line_location_id = sco.SUBCONTRACT_PO_SHIPMENT_ID
154 AND poll.po_header_id = sco.SUBCONTRACT_PO_HEADER_ID
155 AND poll.po_line_id = sco.SUBCONTRACT_PO_LINE_ID
156 AND poll.po_line_id = pol.po_line_id
157 AND poll.po_header_id = pol.po_header_id
158 AND pol.po_header_id = poh.po_header_id
159 AND ( pol.cancel_flag = 'Y' OR
160 poh.cancel_flag = 'Y' OR
161 poll.cancel_flag = 'Y'
162 )
163 ) ;
164
165
166
167
168 IF g_fnd_debug = 'Y' AND
169 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
170 THEN
171 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
172 , 'JMFUSHKB: clean_invalid_data OUT '
173 , 'OUT ');
174 END IF;
175
176
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 null ;
180
181 WHEN OTHERS THEN
182 IF g_fnd_debug = 'Y' AND
183 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
184 THEN
185 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
186 , 'JMFUSHKB: clean_invalid_data OUT '
187 , 'INTO OTHERS EXCEPTION' );
188 END IF;
189
190
191 RAISE FND_API.G_EXC_ERROR;
192
193
194 END clean_invalid_data ;
195
196
197 --========================================================================
198 -- FUNCTION : Get_Primary_Uom_Code PUBLIC
199 -- PARAMETERS: p_inventory_item_id Item
200 -- p_organization_id Inventory Organization
201 -- COMMENT : This function returns the code of the primary UOM
202 -- of the item specified by the input parameters
203 --========================================================================
204
205 FUNCTION Get_Primary_Uom_Code
206 ( p_inventory_item_id IN NUMBER
207 , p_organization_id IN NUMBER
208 )
209 RETURN VARCHAR2
210 IS
211
212 l_api_name CONSTANT VARCHAR2(30) := 'Get_Primary_Uom_Code';
213 l_uom_code VARCHAR2(3);
214
215 BEGIN
216
217 IF g_fnd_debug = 'Y' AND
218 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
219 THEN
220 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
221 , G_MODULE_PREFIX || l_api_name || '.begin'
222 , NULL);
223 END IF;
224
225 SELECT primary_uom_code
226 INTO l_uom_code
227 FROM mtl_system_items
228 WHERE inventory_item_id = p_inventory_item_id
229 AND organization_id = p_organization_id;
230
231 IF g_fnd_debug = 'Y' AND
232 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
233 THEN
234 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
235 , G_MODULE_PREFIX || l_api_name || '.end'
236 , NULL);
237 END IF;
238
239 RETURN l_uom_code;
240
241 END Get_Primary_Uom_Code;
242
243 --========================================================================
244 -- FUNCTION : Get_Uom_Code PUBLIC
245 -- PARAMETERS: p_unit_of_measure Unit of Measure
246 -- COMMENT : This function converts an UOM name to the corresponding
247 -- UOM code
248 --========================================================================
249
250 FUNCTION Get_Uom_Code
251 ( p_unit_of_measure IN VARCHAR2
252 )
253 RETURN VARCHAR2
254 IS
255
256 l_api_name CONSTANT VARCHAR2(30) := 'Get_Uom_Code';
257
258 l_uom_code VARCHAR2(25);
259
260 BEGIN
261
262 IF g_fnd_debug = 'Y' AND
263 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
264 THEN
265 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
266 , G_MODULE_PREFIX || l_api_name || '.begin'
267 , NULL);
268 END IF;
269
270 SELECT uom_code
271 INTO l_uom_code
272 FROM mtl_units_of_measure
273 WHERE unit_of_measure = p_unit_of_measure;
274
275 IF g_fnd_debug = 'Y' AND
276 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
277 THEN
278 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
279 , G_MODULE_PREFIX || l_api_name || '.end'
280 , NULL);
281 END IF;
282
283 RETURN l_uom_code;
284
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287 FND_MESSAGE.Set_Name('INV', 'INV_INVALID_UOM_CONV');
288 FND_MSG_PUB.Add;
289 RAISE FND_API.G_EXC_ERROR;
290
291 END Get_Uom_Code;
292
293 --========================================================================
294 -- FUNCTION : Get_Uom_Conversion_Rate PUBLIC
295 -- PARAMETERS: p_inventory_item_id Inventory Item
296 -- p_organization_id Inventory Organization
297 -- COMMENT : This function returns UOM conversion rate
298 --========================================================================
299
300 FUNCTION Get_Uom_Conversion_Rate
301 ( P_from_unit IN VARCHAR2
302 , P_to_unit IN VARCHAR2
303 , P_item_id IN NUMBER
304 )
305 RETURN NUMBER
306 IS
307
308 l_uom_rate NUMBER;
309
310 BEGIN
311
312 l_uom_rate := 0;
313 inv_convert.inv_um_conversion(from_unit => p_from_unit
314 ,to_unit => p_to_unit
315 ,item_id => p_item_id
316 ,uom_rate =>l_uom_rate);
317 RETURN l_uom_rate;
318 EXCEPTION
319 WHEN OTHERS THEN
320 IF g_fnd_debug = 'Y' AND
321 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
322 THEN
323 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
324 , G_MODULE_PREFIX || 'Get_Uom_Conversion_Rate.others_exception'
325 , 'Exception - Item Id: '||p_item_id ||', From Unit: ' ||
326 p_from_unit || ', To Unit: ' || p_to_unit);
327 END IF;
328 END Get_Uom_Conversion_Rate;
329
330 --========================================================================
331 -- FUNCTION : Get_Replenish_So_Returned_Qty PUBLIC
332 -- PARAMETERS: p_replenishment_so_line_id Replenishment Sales Order line
333 -- COMMENT : This function calculates returned quantity in primary
334 -- UOM against Replenishment Sales Order Line
335 --========================================================================
336
337 FUNCTION Get_Replenish_So_Returned_Qty
338 ( p_replenishment_so_line_id IN NUMBER
339 )
340 RETURN NUMBER
341 IS
342
343 l_returned_quantity NUMBER;
344
345 BEGIN
346 SELECT sum(RT1.PRIMARY_QUANTITY)
347 INTO l_returned_quantity
348 FROM RCV_TRANSACTIONS RT1, RCV_TRANSACTIONS RT2
349 WHERE RT1.TRANSACTION_TYPE = 'RETURN TO VENDOR'
350 AND RT1.PARENT_TRANSACTION_ID = RT2.TRANSACTION_ID
351 AND RT2.REPLENISH_ORDER_LINE_ID = p_replenishment_so_line_id;
352
353 IF (l_returned_quantity IS NULL) THEN
354 l_returned_quantity := 0;
355 END IF;
356
357 RETURN l_returned_quantity;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 IF g_fnd_debug = 'Y' AND
362 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
363 THEN
364 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
365 , G_MODULE_PREFIX || 'Get_Replenish_So_Returned_Qty.others_exception'
366 , 'Exception - Sales Order Line Id: ' || p_replenishment_so_line_id);
367 END IF;
368 END Get_Replenish_So_Returned_Qty;
369
370
371 --========================================================================
372 -- FUNCTION : Get_Replenish_So_Received_Qty PUBLIC
373 -- PARAMETERS: p_replenishment_so_line_id Replenishment Sales Order line
374 -- COMMENT : This function calculates received quantity in TP Org in primary
375 -- UOM against Replenishment Sales Order Line
376 --========================================================================
377
378 FUNCTION Get_Replenish_So_Received_Qty
379 ( p_replenishment_so_line_id IN NUMBER
380 )
381 RETURN NUMBER
382 IS
383
384 l_received_quantity NUMBER;
385
386 BEGIN
387 SELECT PRIMARY_QUANTITY
388 INTO l_received_quantity
392 AND ROWNUM = 1;
389 FROM RCV_TRANSACTIONS
390 WHERE TRANSACTION_TYPE IN ('RECEIVE','DELIVER')
391 AND REPLENISH_ORDER_LINE_ID = p_replenishment_so_line_id
393
394 IF (l_received_quantity IS NULL) THEN
395 l_received_quantity := 0;
396 END IF;
397
398 RETURN l_received_quantity;
399
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 RETURN 0 ;
403 WHEN OTHERS THEN
404 IF g_fnd_debug = 'Y' AND
405 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
406 THEN
407 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
408 , G_MODULE_PREFIX || 'Get_Replenish_So_Received_Qty.others_exception'
409 , 'Exception - Sales Order Line Id: ' || p_replenishment_so_line_id);
410 END IF;
411 END Get_Replenish_So_Received_Qty;
412
413 -----------------------------------------------------------------------
414 -- FUNCTION Get_Used_Quantity
415 -- Comments: This utility will return the component quantity that has been
416 -- currently issued for WIP job
417 ------------------------------------------------------------------------
418 FUNCTION Get_Used_Quantity
419 ( p_wip_entity_id IN NUMBER
420 , p_shikyu_component_id IN NUMBER
421 , p_organization_id IN NUMBER
422 )
423 RETURN NUMBER
424 IS
425
426 l_api_name CONSTANT VARCHAR2(30) := 'Get_Used_Quantity';
427
428 l_quantity NUMBER;
429
430 BEGIN
431
432 IF g_fnd_debug = 'Y' AND
433 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
434 THEN
435 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
436 , G_MODULE_PREFIX || l_api_name || 'GET_used_quantity'
437 , 'begin');
438 END IF;
439
440 BEGIN
441 SELECT SUM(QUANTITY_ISSUED)
442 INTO l_quantity
443 FROM WIP_REQUIREMENT_OPERATIONS
444 WHERE INVENTORY_ITEM_ID = p_shikyu_component_id
445 AND ORGANIZATION_ID = p_organization_id
446 AND WIP_ENTITY_ID = p_wip_entity_id ;
447
448 EXCEPTION
449 WHEN NO_DATA_FOUND
450 THEN l_quantity := 0 ;
451
452 END;
453
454 IF g_fnd_debug = 'Y' AND
455 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
456 THEN
457 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
458 , 'GET_used_quantity Return quantity: '
459 , l_quantity);
460 END IF;
461
462 RETURN l_quantity;
463
464 END Get_Used_Quantity;
465
466 -----------------------------------------------------------------
467 --- FUNCTION Get_Primary_Quantity
468 -- Comments: This utility will convert the PO UOM qty into
469 -- Primary qty
470 --------------------------------------------------------------------------------
471 FUNCTION Get_Primary_Quantity
472 ( p_purchasing_UOM IN VARCHAR2
473 , p_quantity IN NUMBER
474 , P_inventory_org_id IN NUMBER
475 , p_inventory_item_id IN NUMBER ) RETURN NUMBER
476 IS
477
478 l_api_name CONSTANT VARCHAR2(30) := 'Get_Primary_Quantity';
479
480 l_to_code VARCHAR2(30);
481 l_from_code VARCHAR2(30);
482 l_primary_quantity NUMBER ;
483
484 BEGIN
485
486 IF g_fnd_debug = 'Y' AND
487 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
488 THEN
489 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
490 , G_MODULE_PREFIX || l_api_name ||
491 'get_prImary_quantity. Invoked'
492 , 'begin');
493 END IF;
494
495 SELECT uom_code
496 INTO l_from_code
497 FROM mtl_units_of_measure
498 WHERE unit_of_measure = p_purchasing_UOM ;
499
500 SELECT mum.uom_code
501 INTO l_to_code
502 FROM mtl_units_of_measure mum
503 , mtl_system_items msi
504 WHERE mum.unit_of_measure = msi.primary_unit_of_measure
505 AND msi.inventory_item_id = p_inventory_item_id
506 AND msi.primary_unit_of_measure = mum.unit_of_measure
507 AND msi.organization_id = p_inventory_org_id;
508
509 l_primary_quantity := INV_CONVERT.inv_um_convert
510 ( item_id => p_inventory_item_id , precision => 2
511 , from_quantity => p_quantity , from_unit => l_from_code
512 , to_unit => l_to_code
513 , from_name => null
514 , to_name => null
515 );
516
517 RETURN l_primary_quantity;
518
519 END Get_Primary_Quantity;
520
521 ----------------------------------------------------------------
522 --FUNCTION Get_Final_Ship_Date
523 --Comments: This utility returns the ship date with the lead intransit
524 -- days included
525 -----------------------------------------------------------------
529 , p_scheduled_ship_date IN DATE
526 FUNCTION Get_Final_Ship_Date
527 ( p_oem_organization IN NUMBER
528 , p_tp_organization IN NUMBER
530 )
531 RETURN DATE
532 IS
533
534 l_api_name CONSTANT VARCHAR2(30) := 'Get_Final_Ship_Date';
535
536 l_days NUMBER;
537 l_date DATE;
538
539 BEGIN
540
541 l_date := NULL;
542
543 IF g_fnd_debug = 'Y' AND
544 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
545 THEN
546 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
547 , G_MODULE_PREFIX || l_api_name ||
548 'get_final_ship_date.Invoked'
549 , 'begin');
550 END IF;
551
552 BEGIN
553
554 SELECT NVL(intransit_time, 0)
555 INTO l_days
556 FROM mtl_interorg_ship_methods
557 WHERE from_organization_id = p_oem_organization
558 AND to_organization_id = p_tp_organization
559 AND default_flag = 1;
560
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 l_date := NULL;
564 END;
565
566 l_date := p_scheduled_ship_date + l_days;
567
568 RETURN l_date;
569
570 END Get_Final_Ship_Date;
571
572 ----------------------------------------------------------
573 -- FUNCTION Get_Allocation_Date
574 -- Comments: This utility returns the allocation need by date
575 -- based on a WIP entity job
576 ---------------------------------------------------------
577 FUNCTION Get_Allocation_Date
578 ( p_wip_entity_id IN NUMBER
579 )
580 RETURN DATE
581 IS
582
583 l_api_name CONSTANT VARCHAR2(30) := 'Get_Allocation_Date';
584
585 l_completion_date DATE;
586 l_start_date DATE ;
587 l_date DATE ;
588
589 BEGIN
590
591 IF g_fnd_debug = 'Y' AND
592 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
593 THEN
594 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
595 , G_MODULE_PREFIX || l_api_name ||
596 'GET_allocation_date.Invoked '
597 , 'begin');
598 END IF;
599
600 SELECT SCHEDULED_COMPLETION_DATE , scheduled_start_date
601 INTO l_completion_date , l_start_date
602 FROM WIP_DISCRETE_JOBS
603 WHERE wip_entity_id = p_wip_entity_id;
604
605 IF l_start_date > SYSDATE
606 THEN
607 l_date := l_start_date;
608 ELSE
609 l_date := l_completion_date;
610 END IF;
611
612 RETURN l_date;
613
614 END Get_Allocation_Date;
615
616 --=============================================================================
617 -- PROCEDURE NAME: To_Xsd_Date_String
618 -- TYPE : PUBLIC
619 -- PARAMETERS :
620 -- p_date Oracle Date to be converted to XSD Date Format
621 -- p_omit_time Denotes whether the time component of the Oracle Date
622 -- should be omitted
623 -- RETURN : A String representing the passed in Date in XSD Date Format
624 -- DESCRIPTION : Convert an Oracle DB Date Object to a date string represented
625 -- in the XSD Date Format. This is mainly for use by the
626 -- XML Publisher Reports.
627 -- EXCEPTIONS :
628 --
629 -- CHANGE HISTORY: 07-SEP-06 VCHU Created.
630 --=============================================================================
631
632 FUNCTION To_Xsd_Date_String
633 ( p_date IN DATE
634 , p_omit_time IN VARCHAR2 DEFAULT 'N'
635 )
636 RETURN VARCHAR2
637 IS
638
639 l_api_name CONSTANT VARCHAR2(30) := 'To_Xsd_Date_String';
640 l_xsd_date_string VARCHAR2(40);
641
642 BEGIN
643
644 IF p_date IS NULL
645 THEN
646 RETURN NULL;
647 END IF;
648
649 IF g_fnd_debug = 'Y' AND
650 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
651
652 THEN
653 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
654 , G_MODULE_PREFIX
655 , G_MODULE_PREFIX || l_api_name || '.begin');
656 END IF;
657
658 IF p_omit_time = 'Y'
659 THEN
660
661 SELECT TO_CHAR(p_date, 'YYYY-MM-DD')
662 INTO l_xsd_date_string
663 FROM DUAL;
664
665 ELSE
666
667 SELECT TO_CHAR(p_date, 'YYYY-MM-DD') || 'T' || TO_CHAR(p_date, 'HH24:MI:SS')
671 END IF;
668 INTO l_xsd_date_string
669 FROM DUAL;
670
672
673 IF g_fnd_debug = 'Y' AND
674 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
675 THEN
676 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
677 , G_MODULE_PREFIX
678 , G_MODULE_PREFIX || l_api_name
679 || '.end: Returning XSD Date = '
680 || l_xsd_date_string);
681 END IF;
682
683 RETURN TRIM(l_xsd_date_string);
684
685 EXCEPTION
686
687 WHEN OTHERS THEN
688 IF g_fnd_debug = 'Y' AND
689 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
690 THEN
691 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
692 , G_MODULE_PREFIX
693 , G_MODULE_PREFIX || l_api_name || ': ' || sqlerrm);
694 END IF;
695
696 RETURN NULL;
697
698 END To_Xsd_Date_String;
699
700 --=============================================================================
701 -- PROCEDURE NAME : Get_Subcontract_Order_Org_Ids
702 -- TYPE : PUBLIC
703 --
704 -- PARAMETERS :
705 -- IN:
706 --
707 -- RETURN:
708 --
709 -- DESCRIPTION :
710 --
711 -- EXCEPTIONS :
712 --
713 -- CHANGE HISTORY: 31-MAY-05 VCHU Created.
714 --=============================================================================
715
716 PROCEDURE Get_Subcontract_Order_Org_Ids
717 ( p_subcontract_po_shipment_id NUMBER
718 , x_oem_organization_id OUT NOCOPY NUMBER
719 , x_tp_organization_id OUT NOCOPY NUMBER
720 )
721 IS
722
723 BEGIN
724
725 SELECT jso.oem_organization_id,
726 jso.tp_organization_id
727 INTO x_oem_organization_id,
728 x_tp_organization_id
729 FROM JMF_SUBCONTRACT_ORDERS jso
730 WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
731
732 EXCEPTION
733 WHEN NO_DATA_FOUND THEN
734 x_oem_organization_id := NULL;
735 x_tp_organization_id := NULL;
736
737 END Get_Subcontract_Order_Org_Ids;
738
739 --=============================================================================
740 -- PROCEDURE NAME : Get_Shikyu_Attributes
741 -- TYPE : PUBLIC
742 --
743 -- PARAMETERS :
744 -- IN:
745 --
746 -- RETURN:
747 --
748 -- DESCRIPTION :
749 --
750 -- EXCEPTIONS :
751 --
752 -- CHANGE HISTORY: 25-APR-05 VCHU Created.
753 --=============================================================================
754
755 PROCEDURE Get_Shikyu_Attributes
756 ( p_organization_id IN NUMBER
757 , p_item_id IN NUMBER
758 , x_outsourced_assembly OUT NOCOPY NUMBER
759 , x_subcontracting_component OUT NOCOPY NUMBER
760 , p_primary_uom_price OUT NOCOPY NUMBER
761 )
762 IS
763
764 BEGIN
765
766 SELECT outsourced_assembly,
767 subcontracting_component
768 INTO x_outsourced_assembly,
769 x_subcontracting_component
770 FROM MTL_SYSTEM_ITEMS_B
771 WHERE organization_id = p_organization_id
772 AND inventory_item_id = p_item_id;
773
774 EXCEPTION
775 WHEN NO_DATA_FOUND THEN
776 x_outsourced_assembly := NULL;
777 x_subcontracting_component := NULL;
778
779 END Get_Shikyu_Attributes;
780
781 --=============================================================================
782 -- FUNCTION NAME : Get_Shikyu_Component_Price
783 -- TYPE : PUBLIC
784 --
785 -- PARAMETERS :
786 -- IN:
787 --
788 -- RETURN:
789 --
790 -- DESCRIPTION :
791 --
792 -- EXCEPTIONS :
793 --
794 -- CHANGE HISTORY: 25-APR-05 VCHU Created.
795 --=============================================================================
796
797 PROCEDURE Get_Shikyu_Component_Price
798 ( p_subcontract_po_shipment_id IN NUMBER
799 , p_shikyu_component_id IN NUMBER
800 , x_component_uom OUT NOCOPY VARCHAR2
801 , x_component_price OUT NOCOPY NUMBER
802 , x_primary_uom OUT NOCOPY VARCHAR2
803 , x_primary_uom_price OUT NOCOPY NUMBER
804 )
805 IS
806
807 BEGIN
808
809 SELECT uom,
810 shikyu_component_price,
811 primary_uom,
812 primary_uom_price
813 INTO x_component_uom,
814 x_component_price,
815 x_primary_uom,
816 x_primary_uom_price
817 FROM JMF_SHIKYU_COMPONENTS
818 WHERE shikyu_component_id = p_shikyu_component_id
819 AND subcontract_po_shipment_id = p_subcontract_po_shipment_id;
820
821
822 EXCEPTION
823 WHEN NO_DATA_FOUND THEN
824 x_component_uom := NULL;
825 x_component_price := NULL;
826 x_primary_uom := NULL;
827 x_primary_uom_price := NULL;
828
829 END Get_Shikyu_Component_Price;
830
831 --=============================================================================
832 -- FUNCTION NAME : Get_Replen_Po_Allocated_Qty
833 -- TYPE : PUBLIC
837 --
834 --
835 -- PARAMETERS :
836 -- IN:
838 -- RETURN:
839 --
840 -- DESCRIPTION :
841 --
842 -- EXCEPTIONS :
843 --
844 -- CHANGE HISTORY: 25-APR-05 VCHU Created.
845 --=============================================================================
846
847 PROCEDURE Get_Replen_Po_Allocated_Qty
848 ( p_replen_po_shipment_id IN NUMBER
849 , x_allocated_primary_uom_qty OUT NOCOPY NUMBER
850 , x_primary_uom OUT NOCOPY VARCHAR2
851 )
852 IS
853
854 BEGIN
855
856 SELECT SUM(allocated_primary_uom_quantity),
857 MAX(uom)
858 INTO x_allocated_primary_uom_qty,
859 x_primary_uom
860 FROM jmf_shikyu_replenishments
861 WHERE replenishment_po_shipment_id = p_replen_po_shipment_id;
862
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN
865 x_allocated_primary_uom_qty := NULL;
866 x_primary_uom := NULL;
867
868 END Get_Replen_Po_Allocated_Qty;
869
870 --=============================================================================
871 -- FUNCTION NAME : Get_Replen_Po_Ordered_Qty
872 -- TYPE : PUBLIC
873 --
874 -- PARAMETERS :
875 -- IN:
876 --
877 -- RETURN:
878 --
879 -- DESCRIPTION :
880 --
881 -- EXCEPTIONS :
882 --
883 -- CHANGE HISTORY: 25-APR-05 VCHU Created.
884 --=============================================================================
885
886 PROCEDURE Get_Replen_Po_Ordered_Qty
887 ( p_replen_po_shipment_id IN NUMBER
888 , x_ordered_primary_uom_qty OUT NOCOPY NUMBER
889 , x_primary_uom OUT NOCOPY VARCHAR2
890 )
891 IS
892
893 BEGIN
894
895 SELECT SUM(ordered_primary_uom_quantity),
896 MAX(primary_uom)
897 INTO x_ordered_primary_uom_qty,
898 x_primary_uom
899 FROM jmf_shikyu_replenishments
900 WHERE replenishment_po_shipment_id = p_replen_po_shipment_id;
901
902 EXCEPTION
903 WHEN NO_DATA_FOUND THEN
904 x_ordered_primary_uom_qty := NULL;
905 x_primary_uom := NULL;
906
907 END Get_Replen_Po_Ordered_Qty;
908
909 --=============================================================================
910 -- FUNCTION NAME : Get_Replen_So_Allocated_Qty
911 -- TYPE : PUBLIC
912 --
913 -- PARAMETERS :
914 -- IN:
915 --
916 -- RETURN:
917 --
918 -- DESCRIPTION :
919 --
920 -- EXCEPTIONS :
921 --
922 -- CHANGE HISTORY: 25-APR-05 VCHU Created.
923 --=============================================================================
924
925 PROCEDURE Get_Replen_So_Allocated_Qty
926 ( p_replen_so_line_id IN NUMBER
927 , x_allocated_qty OUT NOCOPY NUMBER
928 , x_uom OUT NOCOPY VARCHAR2
929 , x_allocated_primary_uom_qty OUT NOCOPY NUMBER
930 , x_primary_uom OUT NOCOPY VARCHAR2
931 )
932 IS
933
934 BEGIN
935
936 SELECT allocated_quantity,
937 uom,
938 allocated_primary_uom_quantity,
939 primary_uom
940 INTO x_allocated_qty,
941 x_uom,
942 x_allocated_primary_uom_qty,
943 x_primary_uom
944 FROM JMF_SHIKYU_REPLENISHMENTS
945 WHERE replenishment_so_line_id = p_replen_so_line_id;
946
947 EXCEPTION
948 WHEN NO_DATA_FOUND THEN
949 x_allocated_qty := NULL;
950 x_uom := NULL;
951 x_allocated_primary_uom_qty := NULL;
952 x_primary_uom := NULL;
953
954 END Get_Replen_So_Allocated_Qty;
955
956 --=============================================================================
957 -- FUNCTION NAME : Get_Subcontract_Allocated_Qty
958 -- TYPE : PUBLIC
959 --
960 -- PARAMETERS :
961 -- IN:
962 --
963 -- RETURN:
964 --
965 -- DESCRIPTION :
966 --
967 -- EXCEPTIONS :
968 --
969 -- CHANGE HISTORY: 25-APR-05 VCHU Created.
970 --=============================================================================
971
972 -- the quantity would be in primary uom
973 FUNCTION Get_Subcontract_Allocated_Qty
974 ( p_subcontract_po_shipment_id IN NUMBER
975 , p_component_id IN NUMBER
976 )
977 RETURN NUMBER
978 IS
979 l_allocated_qty NUMBER := 0;
980 BEGIN
981
982 SELECT NVL(SUM(allocated_quantity), 0)
983 INTO l_allocated_qty
984 FROM jmf_shikyu_allocations
985 WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
986 AND shikyu_component_id = p_component_id;
987
988 RETURN l_allocated_qty;
989
990 EXCEPTION
991 WHEN NO_DATA_FOUND THEN
992 RETURN 0;
993
994 END Get_Subcontract_Allocated_Qty;
995
996 --=============================================================================
997 -- FUNCTION NAME : Get_Replenishment_So_Price
998 -- TYPE : PUBLIC
999 --
1000 -- PARAMETERS :
1001 -- IN:
1002 --
1003 -- RETURN:
1004 --
1005 -- DESCRIPTION :
1006 --
1007 -- EXCEPTIONS :
1008 --
1009 -- CHANGE HISTORY: 25-APR-05 VCHU Created.
1010 --=============================================================================
1011
1012 PROCEDURE Get_Replenishment_So_Price
1013 ( p_replenishment_so_line_id IN NUMBER
1017 IS
1014 , x_uom OUT NOCOPY VARCHAR2
1015 , x_price OUT NOCOPY NUMBER
1016 )
1018 BEGIN
1019
1020 SELECT pricing_quantity_uom,
1021 unit_selling_price
1022 INTO x_uom,
1023 x_price
1024 FROM oe_order_lines_all
1025 WHERE line_id = p_replenishment_so_line_id;
1026
1027 EXCEPTION
1028 WHEN NO_DATA_FOUND THEN
1029 x_uom := NULL;
1030 x_price := NULL;
1031 END Get_Replenishment_So_Price;
1032
1033 --========================================================================
1034 -- PROCEDURE : debug_output PUBLIC
1035 -- PARAMETERS: p_output_to Identifier of where to output to
1036 -- p_api_name the called api name
1037 -- p_message the message that need to be output
1038 -- COMMENT : the debug output, for using in readonly UT environment
1039 -- PRE-COND :
1040 -- EXCEPTIONS:
1041 --========================================================================
1042 PROCEDURE debug_output
1043 ( p_output_to IN VARCHAR2
1044 , p_api_name IN VARCHAR2
1045 , p_message IN VARCHAR2
1046 )
1047 IS
1048 BEGIN
1049
1050 CASE p_output_to
1051 WHEN 'FND_LOG.STRING' THEN
1052 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1053 fnd_log.STRING(FND_LOG.LEVEL_PROCEDURE
1054 ,p_api_name || '.debug_output'
1055 ,p_message);
1056 END IF;
1057 WHEN 'FND_FILE.OUTPUT' THEN
1058 fnd_file.put_line(fnd_file.OUTPUT
1059 ,p_api_name || '.debug_output' || ': ' ||
1060 p_message);
1061 WHEN 'FND_FILE.LOG' THEN
1062 fnd_file.put_line(fnd_file.LOG
1063 ,p_api_name || '.debug_output' || ': ' ||
1064 p_message);
1065 ELSE
1066 NULL;
1067 END CASE;
1068
1069 END debug_output;
1070
1071 --===========================================================================
1072 -- API NAME : Get_Shikyu_Offset_Account
1073 --
1074 -- DESCRIPTION:
1075 --
1076 -- PARAMETERS :
1077 -- IN :
1078 -- OUT :
1079 --
1080 -- CHANGE HISTORY: 21-Oct-05 VCHU Created.
1081 --===========================================================================
1082 PROCEDURE Get_Shikyu_Offset_Account
1083 ( p_po_shipment_id IN NUMBER
1084 , x_offset_account OUT NOCOPY NUMBER
1085 )
1086 IS
1087
1088 l_api_name CONSTANT VARCHAR2(30) := 'Get_Shikyu_Offset_Account';
1089 l_api_version CONSTANT NUMBER := 1.0;
1090
1091 BEGIN
1092
1093 x_offset_account := NULL;
1094
1095 -- Joining the subcontracting orders table with the Shipping Networks
1096 -- table to get the Code Combination ID of the SHIKYU Offset Account.
1097 -- The Shipping Networks table stores relationships from OEM Organizations
1098 -- to MP Organizations
1099 SELECT mip.shikyu_tp_offset_account_id
1100 INTO x_offset_account
1101 FROM mtl_interorg_parameters mip,
1102 jmf_subcontract_orders jso
1103 WHERE jso.subcontract_po_shipment_id = p_po_shipment_id
1104 AND jso.oem_organization_id = mip.from_organization_id
1105 AND jso.tp_organization_id = mip.to_organization_id
1106 --AND mip.shikyu_enabled_flag = 'Y';
1107 AND mip.subcontracting_type in ('B','C') ; -- 12.1 Buy/Sell Subcontracting changes
1108
1109
1110 EXCEPTION
1111 WHEN NO_DATA_FOUND THEN
1112 IF g_fnd_debug = 'Y' AND
1113 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1114 THEN
1115 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1116 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
1117 , 'No relationship exists for the OEM Organization and Manufacturing Organization of the Subcontracting PO');
1118 END IF;
1119
1120 END Get_Shikyu_Offset_Account;
1121
1122 --========================================================================
1123 -- PROCEDURE : Submit_Worker PUBLIC
1124 -- PARAMETERS: p_batch_id IN NUMBER Batch reference that identifies set
1125 -- of rows to be processed
1126 -- p_request_count IN NUMBER Max number of workers allowed
1127 -- p_cp_short_name IN VARCHAR2 Short name of concurrent program
1128 -- p_cp_product_code IN VARCHAR2 Owning product of concurrent program
1129 -- x_workers IN OUT Table (of type g_request_tbl_type) containing the
1130 -- concurrent request IDs of all the active workers
1131 -- x_request_id OUT NUMBER It returns Concurrent Request ID which is
1132 -- submitted recently.
1133 -- x_return_status OUT NUMBER Return Status
1134 -- COMMENT : This generic procedure is called to submit concurrent requests.
1135 -- It returns a table containing list of active workers. This accepts
1136 -- batch id as single argument to concurrent program.
1137 --========================================================================
1138 PROCEDURE Submit_Worker
1139 ( p_batch_id IN NUMBER
1140 , p_request_count IN NUMBER
1141 , p_cp_short_name IN VARCHAR2
1145 , x_return_status OUT NOCOPY VARCHAR2
1142 , p_cp_product_code IN VARCHAR2
1143 , x_workers IN OUT NOCOPY g_request_tbl_type
1144 , x_request_id OUT NOCOPY NUMBER
1146 )
1147 IS
1148
1149 l_worker_idx BINARY_INTEGER;
1150 l_api_name CONSTANT VARCHAR2(30) := 'Submit_Worker';
1151
1152 BEGIN
1153
1154 IF g_fnd_debug = 'Y' AND
1155 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1156 THEN
1157 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1158 , G_MODULE_PREFIX || l_api_name || '.begin'
1159 , p_cp_product_code || '.' ||p_cp_short_name || '(' || p_batch_id ||')');
1160 END IF;
1161
1162 x_return_status := FND_API.G_RET_STS_SUCCESS;
1163
1164 IF x_workers.COUNT < p_request_count THEN
1165 -- number of workers submitted so far does not exceed the maximum
1166 -- number of workers allowed
1167 l_worker_idx := x_workers.COUNT + 1;
1168 ELSE
1169 -- need to wait for a submitted worker to finish
1170 JMF_SHIKYU_UTIL.wait_for_worker
1171 ( p_workers => x_workers
1172 , x_worker_idx => l_worker_idx
1173 );
1174 END IF;
1175
1176 -- Calling FND_REQUEST.Set_Options before submitting request to set request attributes
1177 IF NOT FND_REQUEST.Set_Options
1178 ( implicit => 'NO'
1179 , protected => 'YES'
1180 )
1181 THEN
1182 RAISE g_submit_failure_exc;
1183 END IF;
1184
1185 -- Submits concurrent request to be processed by a concurrent manager
1186 x_workers(l_worker_idx) := FND_REQUEST.submit_request
1187 ( application => p_cp_product_code
1188 , program => p_cp_short_name
1189 , argument1 => p_batch_id
1190 );
1191 x_request_id := x_workers(l_worker_idx);
1192
1193 IF x_workers(l_worker_idx) = 0 THEN
1194 RAISE g_submit_failure_exc;
1195 END IF;
1196
1197 COMMIT;
1198
1199 IF g_fnd_debug = 'Y' AND
1200 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1201 THEN
1202 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1203 , G_MODULE_PREFIX || l_api_name || '.end'
1204 , NULL);
1205 END IF;
1206 EXCEPTION
1207 WHEN g_submit_failure_exc THEN
1208
1209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1210
1211 IF g_fnd_debug = 'Y' AND
1212 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1213 THEN
1214 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1215 , G_MODULE_PREFIX || l_api_name || '.Submit Request'
1216 , 'Exception - ' || p_cp_product_code || '.' ||p_cp_short_name || '(' || p_batch_id ||')');
1217 END IF;
1218 WHEN OTHERS THEN
1219
1220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1221
1222 IF g_fnd_debug = 'Y' AND
1223 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1224 THEN
1225 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1226 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1227 , 'Exception - ' || p_cp_product_code || '.' ||p_cp_short_name || '(' || p_batch_id ||')');
1228 END IF;
1229 END Submit_Worker;
1230
1231 --========================================================================
1232 -- FUNCTION : Has_Worker_Completed PUBLIC
1233 -- PARAMETERS: p_request_id IN NUMBER Unique identifier of a concurrent request.
1234 -- RETURNS : BOOLEAN
1235 -- COMMENT : This function accepts a unique identifier of concurrent request
1236 -- and it returns boolean value. It returns TRUE if the corresponding worker
1237 -- has completed, otherwise FALSE.
1238 --=========================================================================
1239 FUNCTION Has_worker_completed
1240 ( p_request_id IN NUMBER
1241 )
1242 RETURN BOOLEAN
1243 IS
1244
1245 l_count NUMBER;
1246 l_result BOOLEAN;
1247
1248 l_api_name CONSTANT VARCHAR2(30) := 'Has_Worker_Completed';
1249
1250 BEGIN
1251
1252 IF g_fnd_debug = 'Y' AND
1253 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1254 THEN
1255 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1259
1256 , G_MODULE_PREFIX || l_api_name || '.begin'
1257 , 'Request Id: ' || p_request_id);
1258 END IF;
1260 SELECT COUNT(*)
1261 INTO l_count
1262 FROM fnd_concurrent_requests
1263 WHERE request_id = p_request_id
1264 AND phase_code = 'C';
1265
1266 IF l_count = 1 THEN
1267 l_result := TRUE;
1268 ELSE
1269 l_result := FALSE;
1270 END IF;
1271
1272 IF g_fnd_debug = 'Y' AND
1273 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1274 THEN
1275 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1276 , G_MODULE_PREFIX || l_api_name || '.end'
1277 , NULL);
1278 END IF;
1279
1280 RETURN l_result;
1281
1282 EXCEPTION
1283 WHEN NO_DATA_FOUND THEN
1284 IF g_fnd_debug = 'Y' AND
1285 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1286 THEN
1287 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1288 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
1289 , 'No data found for concurrent request Id:' || p_request_id);
1290 END IF;
1291 WHEN OTHERS THEN
1292 IF g_fnd_debug = 'Y' AND
1293 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1294 THEN
1295 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1296 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1297 , 'Exception - Request Id: ' || p_request_id);
1298 END IF;
1299
1300 END Has_worker_completed;
1301
1302 --========================================================================
1303 -- PROCEDURE : Wait_For_Worker PUBLIC
1304 -- PARAMETERS: p_workers IN Required
1305 -- Table (of type g_request_tbl_type) containing the
1306 -- concurrent request IDs of all the active workers
1307 -- x_worker_idx OUT Index of the worker (within the p_workers table)
1308 -- whose current task has completed and can start
1309 -- a new concurrent request.
1310 -- COMMENT : This procedure polls submitted workers and suspend
1311 -- the program till the completion of one of them; it returns
1312 -- the completed worker through x_worker_idx
1313 --=========================================================================
1314 PROCEDURE Wait_for_worker
1315 ( p_workers IN g_request_tbl_type
1316 , x_worker_idx OUT NOCOPY BINARY_INTEGER
1317 )
1318 IS
1319
1320 l_done BOOLEAN;
1321 l_api_name CONSTANT VARCHAR2(30) := 'Wait_for_worker';
1322
1323 BEGIN
1324
1325 IF g_fnd_debug = 'Y' AND
1326 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1327 THEN
1328 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1329 , G_MODULE_PREFIX || l_api_name || '.begin'
1330 , NULL);
1331 END IF;
1332
1333 l_done := FALSE;
1334
1335 WHILE (NOT l_done) LOOP
1336
1337 FOR l_Idx IN 1..p_workers.COUNT LOOP
1338
1339 IF JMF_SHIKYU_UTIL.has_worker_completed(p_workers(l_Idx))
1340 THEN
1341 l_done := TRUE;
1342 x_worker_idx := l_Idx;
1343 EXIT;
1344 END IF;
1345
1346 END LOOP;
1347
1348 IF (NOT l_done) THEN
1349 DBMS_LOCK.sleep(G_SLEEP_TIME);
1350 END IF;
1351
1352 END LOOP;
1353
1354 IF g_fnd_debug = 'Y' AND
1355 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1356 THEN
1357 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1358 , G_MODULE_PREFIX || l_api_name || '.end'
1359 , NULL);
1360 END IF;
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 IF g_fnd_debug = 'Y' AND
1364 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1365 THEN
1366 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1367 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1368 , 'Exception');
1369 END IF;
1370 END Wait_For_Worker;
1371
1372 --========================================================================
1373 -- PROCEDURE : Wait_For_All_Workers PUBLIC
1374 -- PARAMETERS: p_workers IN Required
1375 -- Table (of type g_request_tbl_type) containing the
1376 -- concurrent request IDs of all the active workers
1377 -- COMMENT : This procedure polls submitted workers and suspend
1378 -- the program till completion of all of workers.
1379 --=========================================================================
1380 PROCEDURE wait_for_all_workers
1381 ( p_workers IN g_request_tbl_type
1382 )
1383 IS
1384
1385 l_done BOOLEAN;
1386 l_api_name CONSTANT VARCHAR2(30) := 'Wait_for_all_workers';
1387
1388 BEGIN
1389
1390 IF g_fnd_debug = 'Y' AND
1391 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1392 THEN
1393 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1394 , G_MODULE_PREFIX || l_api_name || '.begin'
1395 , NULL);
1396 END IF;
1397
1398 l_done := FALSE;
1399
1400 WHILE (NOT l_done) LOOP
1401
1402 l_done := TRUE;
1403
1404 FOR l_Idx IN 1..p_workers.COUNT LOOP
1405
1406 IF NOT
1407 JMF_SHIKYU_UTIL.has_worker_completed(p_workers(l_Idx))
1408 THEN
1409 l_done := FALSE;
1410 EXIT;
1411 END IF;
1412
1413 END LOOP;
1414
1415 IF (NOT l_done) THEN
1416 DBMS_LOCK.sleep(G_SLEEP_TIME);
1417 END IF;
1418
1419 END LOOP;
1420
1421 IF g_fnd_debug = 'Y' AND
1422 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1423 THEN
1424 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1425 , G_MODULE_PREFIX || l_api_name || '.end'
1426 , NULL);
1427 END IF;
1428 EXCEPTION
1429 WHEN OTHERS THEN
1430 IF g_fnd_debug = 'Y' AND
1431 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1432 THEN
1433 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
1434 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1435 , 'Exception');
1436 END IF;
1437
1438 END wait_for_all_workers;
1439
1440 END JMF_SHIKYU_UTIL;