[Home] [Help]
64: --| uom parameters from most procedures. |
65: --| 18-AUG-2005 vchu Modified the where clause of the |
66: --| c_avail_replen_so_cur cursor to remove the |
67: --| calls to to_date. |
68: --| 29-SEP-2005 vchu Modified INSERT INTO JMF_SHIKYU_ALLOCATIONS |
69: --| statement in Allocate_Quantity to populate the |
70: --| UOM column with l_primary_uom instead of |
71: --| l_allocated_uom. |
72: --| 12-OCT-2005 vchu Modified the order by clause of the query for |
362: l_replen_allocable_primary_qty
363: JMF_SHIKYU_REPLENISHMENTS.allocable_primary_uom_quantity%TYPE;
364: l_replen_allocated_qty JMF_SHIKYU_REPLENISHMENTS.allocated_quantity%TYPE;
365: l_replen_allocable_qty JMF_SHIKYU_REPLENISHMENTS.allocable_quantity%TYPE;
366: l_allocated_uom JMF_SHIKYU_ALLOCATIONS.uom%TYPE;
367: l_allocation_qty JMF_SHIKYU_ALLOCATIONS.allocated_quantity%TYPE;
368: l_replen_so_header_id OE_ORDER_LINES_ALL.header_id%TYPE := NULL;
369: l_sub_comp MTL_SYSTEM_ITEMS_B.segment1%TYPE;
370: l_order_number PO_HEADERS_ALL.SEGMENT1%TYPE;
363: JMF_SHIKYU_REPLENISHMENTS.allocable_primary_uom_quantity%TYPE;
364: l_replen_allocated_qty JMF_SHIKYU_REPLENISHMENTS.allocated_quantity%TYPE;
365: l_replen_allocable_qty JMF_SHIKYU_REPLENISHMENTS.allocable_quantity%TYPE;
366: l_allocated_uom JMF_SHIKYU_ALLOCATIONS.uom%TYPE;
367: l_allocation_qty JMF_SHIKYU_ALLOCATIONS.allocated_quantity%TYPE;
368: l_replen_so_header_id OE_ORDER_LINES_ALL.header_id%TYPE := NULL;
369: l_sub_comp MTL_SYSTEM_ITEMS_B.segment1%TYPE;
370: l_order_number PO_HEADERS_ALL.SEGMENT1%TYPE;
371: l_message VARCHAR(2000);
467: -- If an allocation record does not already exist between the
468: -- Subcontracting Order Shipment and the Replenishment Order Line
469: IF l_exists = 'N'
470: THEN
471: INSERT INTO jmf_shikyu_allocations
472: ( SUBCONTRACT_PO_SHIPMENT_ID
473: , SHIKYU_COMPONENT_ID
474: , REPLENISHMENT_SO_LINE_ID
475: , ALLOCATED_QUANTITY
497: -- Order Shipment and the Replenishment Order Line
498: ELSE
499: l_allocation_qty := l_allocation_qty + l_qty_to_allocate;
500:
501: UPDATE jmf_shikyu_allocations
502: SET allocated_quantity = l_allocation_qty,
503: last_update_date = sysdate,
504: last_updated_by = FND_GLOBAL.user_id,
505: last_update_login = FND_GLOBAL.login_id
2719: , oola.actual_shipment_date
2720: , oola.schedule_ship_date
2721: , ooha.order_number
2722: , oola.line_number
2723: FROM JMF_SHIKYU_ALLOCATIONS jsa,
2724: OE_ORDER_LINES_ALL oola,
2725: OE_ORDER_HEADERS_ALL ooha
2726: WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
2727: AND jsa.shikyu_component_id = p_component_id
2736: -- the Subcontracting Component and the Replenishment SO Line specified
2737: -- by the IN parameters
2738: CURSOR c_alloc_cur IS
2739: SELECT jsa.allocated_quantity, jsa.uom
2740: FROM JMF_SHIKYU_ALLOCATIONS jsa
2741: WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
2742: AND jsa.replenishment_so_line_id = p_replen_so_line_id;
2743:
2744: BEGIN
2788:
2789: -- To reduce more than one Replenishment SO Line allocated to the
2790: -- Subcontracting Component specified, in FIFO order of the scheduled
2791: -- ship date
2792: -- i.e. reduce from multiple JMF_SHIKYU_ALLOCATIONS record
2793: IF l_allocations_tbl.COUNT > 0
2794: THEN
2795: -- Loop through the PL/SQL table containing records of g_allocation_qty_tbl_type
2796: -- representing the allocations for the specified Subcontracting Component,
2844:
2845: CLOSE c_subcontract_po_alloc_cur;
2846:
2847: -- To reduce from the Replenishment SO Line specified
2848: -- i.e. reduce from only one JMF_SHIKYU_ALLOCATIONS record
2849: ELSE
2850:
2851: IF (g_fnd_debug = 'Y' AND
2852: FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
3003:
3004: IF p_replen_so_line_id IS NULL
3005: THEN
3006:
3007: DELETE FROM jmf_shikyu_allocations
3008: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
3009: AND shikyu_component_id = p_component_id
3010: RETURNING subcontract_po_shipment_id,
3011: replenishment_so_line_id,
3015: BULK COLLECT INTO x_deleted_allocations_tbl;
3016:
3017: ELSE
3018:
3019: DELETE FROM jmf_shikyu_allocations
3020: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
3021: AND shikyu_component_id = p_component_id
3022: AND replenishment_so_line_id = p_replen_so_line_id
3023: RETURNING subcontract_po_shipment_id,
3030: END IF; /* IF p_replen_so_line_id IS NULL */
3031:
3032: ELSE /* IF p_component_id IS NULL */
3033:
3034: DELETE FROM jmf_shikyu_allocations
3035: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
3036: RETURNING subcontract_po_shipment_id,
3037: replenishment_so_line_id,
3038: shikyu_component_id,
3046:
3047: IF p_replen_so_line_id IS NOT NULL
3048: THEN
3049:
3050: DELETE FROM jmf_shikyu_allocations
3051: WHERE replenishment_so_line_id = p_replen_so_line_id
3052: RETURNING subcontract_po_shipment_id,
3053: replenishment_so_line_id,
3054: shikyu_component_id,
3529: NVL(plla.need_by_date, plla.promised_date),
3530: pha.segment1,
3531: pla.line_num,
3532: plla.shipment_num
3533: FROM JMF_SHIKYU_ALLOCATIONS jsa,
3534: PO_LINE_LOCATIONS_ALL plla,
3535: PO_LINES_ALL pla,
3536: PO_HEADERS_ALL pha
3537: WHERE jsa.replenishment_so_line_id = p_replen_order_line_id
4059: , jsr.allocated_quantity allocated_quantity
4060: FROM JMF_SHIKYU_REPLENISHMENTS jsr
4061: , OE_ORDER_LINES_ALL oel
4062: , OE_ORDER_HEADERS_ALL oeh
4063: , JMF_SHIKYU_ALLOCATIONS jsa
4064: WHERE oeh.header_id = jsr.REPLENISHMENT_SO_HEADER_ID
4065: AND oel.header_id = oeh.header_id
4066: AND oel.line_id = jsr.replenishment_so_line_id
4067: AND jsa.shikyu_component_id(+) = jsr.shikyu_component_id
4953: 'Y'
4954: INTO x_allocated_qty,
4955: x_uom,
4956: x_allocation_exists
4957: FROM JMF_SHIKYU_ALLOCATIONS jsa
4958: WHERE jsa.replenishment_so_line_id = p_replen_so_line_id
4959: AND jsa.shikyu_component_id = p_component_id
4960: AND jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
4961:
5491: || '), l_new_allocated_qty = ' || l_new_allocated_qty
5492: );
5493: END IF;
5494:
5495: UPDATE JMF_SHIKYU_ALLOCATIONS
5496: SET allocated_quantity = l_new_allocated_qty
5497: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
5498: AND replenishment_so_line_id = p_replen_so_line_id
5499: RETURNING subcontract_po_shipment_id,
5513: FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5514: THEN
5515: FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5516: , G_MODULE_PREFIX || l_api_name
5517: , l_api_name || ': Updated JMF_SHIKYU_ALLOCATIONS table, '
5518: || 'x_reduced_allocations_rec.qty = '
5519: || x_reduced_allocations_rec.qty
5520: );
5521: END IF;
5533: || ') >= p_existing_alloc_qty (' || p_existing_alloc_qty || ')'
5534: );
5535: END IF;
5536:
5537: DELETE FROM JMF_SHIKYU_ALLOCATIONS
5538: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
5539: AND replenishment_so_line_id = p_replen_so_line_id
5540: RETURNING subcontract_po_shipment_id,
5541: replenishment_so_line_id,
5716: cursor c_alloc_cur is
5717: select jsa.shikyu_component_id shikyu_component_id,
5718: jsa.subcontract_po_shipment_id subcontract_po_shipment_id,
5719: jso.wip_entity_id wip_entity_id
5720: from jmf_shikyu_allocations jsa,
5721: jmf_subcontract_orders jso
5722: where jsa.replenishment_so_line_id = l_line_id
5723: and jsa.subcontract_po_shipment_id = jso.subcontract_po_shipment_id;
5724: