[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
2709: , jsa.uom
2710: , oola.schedule_ship_date
2711: , ooha.order_number
2712: , oola.line_number
2713: FROM JMF_SHIKYU_ALLOCATIONS jsa,
2714: OE_ORDER_LINES_ALL oola,
2715: OE_ORDER_HEADERS_ALL ooha
2716: WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
2717: AND jsa.shikyu_component_id = p_component_id
2725: -- the Subcontracting Component and the Replenishment SO Line specified
2726: -- by the IN parameters
2727: CURSOR c_alloc_cur IS
2728: SELECT jsa.allocated_quantity, jsa.uom
2729: FROM JMF_SHIKYU_ALLOCATIONS jsa
2730: WHERE jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
2731: AND jsa.replenishment_so_line_id = p_replen_so_line_id;
2732:
2733: BEGIN
2777:
2778: -- To reduce more than one Replenishment SO Line allocated to the
2779: -- Subcontracting Component specified, in FIFO order of the scheduled
2780: -- ship date
2781: -- i.e. reduce from multiple JMF_SHIKYU_ALLOCATIONS record
2782: IF l_allocations_tbl.COUNT > 0
2783: THEN
2784: -- Loop through the PL/SQL table containing records of g_allocation_qty_tbl_type
2785: -- representing the allocations for the specified Subcontracting Component,
2833:
2834: CLOSE c_subcontract_po_alloc_cur;
2835:
2836: -- To reduce from the Replenishment SO Line specified
2837: -- i.e. reduce from only one JMF_SHIKYU_ALLOCATIONS record
2838: ELSE
2839:
2840: IF (g_fnd_debug = 'Y' AND
2841: FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
2992:
2993: IF p_replen_so_line_id IS NULL
2994: THEN
2995:
2996: DELETE FROM jmf_shikyu_allocations
2997: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
2998: AND shikyu_component_id = p_component_id
2999: RETURNING subcontract_po_shipment_id,
3000: replenishment_so_line_id,
3004: BULK COLLECT INTO x_deleted_allocations_tbl;
3005:
3006: ELSE
3007:
3008: DELETE FROM jmf_shikyu_allocations
3009: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
3010: AND shikyu_component_id = p_component_id
3011: AND replenishment_so_line_id = p_replen_so_line_id
3012: RETURNING subcontract_po_shipment_id,
3019: END IF; /* IF p_replen_so_line_id IS NULL */
3020:
3021: ELSE /* IF p_component_id IS NULL */
3022:
3023: DELETE FROM jmf_shikyu_allocations
3024: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
3025: RETURNING subcontract_po_shipment_id,
3026: replenishment_so_line_id,
3027: shikyu_component_id,
3035:
3036: IF p_replen_so_line_id IS NOT NULL
3037: THEN
3038:
3039: DELETE FROM jmf_shikyu_allocations
3040: WHERE replenishment_so_line_id = p_replen_so_line_id
3041: RETURNING subcontract_po_shipment_id,
3042: replenishment_so_line_id,
3043: shikyu_component_id,
3375: NVL(plla.need_by_date, plla.promised_date),
3376: pha.segment1,
3377: pla.line_num,
3378: plla.shipment_num
3379: FROM JMF_SHIKYU_ALLOCATIONS jsa,
3380: PO_LINE_LOCATIONS_ALL plla,
3381: PO_LINES_ALL pla,
3382: PO_HEADERS_ALL pha
3383: WHERE jsa.replenishment_so_line_id = p_replen_order_line_id
3733: , jsr.allocated_quantity allocated_quantity
3734: FROM JMF_SHIKYU_REPLENISHMENTS jsr
3735: , OE_ORDER_LINES_ALL oel
3736: , OE_ORDER_HEADERS_ALL oeh
3737: , JMF_SHIKYU_ALLOCATIONS jsa
3738: WHERE oeh.header_id = jsr.REPLENISHMENT_SO_HEADER_ID
3739: AND oel.header_id = oeh.header_id
3740: AND oel.line_id = jsr.replenishment_so_line_id
3741: AND jsa.shikyu_component_id = jsr.shikyu_component_id
4509: 'Y'
4510: INTO x_allocated_qty,
4511: x_uom,
4512: x_allocation_exists
4513: FROM JMF_SHIKYU_ALLOCATIONS jsa
4514: WHERE jsa.replenishment_so_line_id = p_replen_so_line_id
4515: AND jsa.shikyu_component_id = p_component_id
4516: AND jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id;
4517:
5047: || '), l_new_allocated_qty = ' || l_new_allocated_qty
5048: );
5049: END IF;
5050:
5051: UPDATE JMF_SHIKYU_ALLOCATIONS
5052: SET allocated_quantity = l_new_allocated_qty
5053: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
5054: AND replenishment_so_line_id = p_replen_so_line_id
5055: RETURNING subcontract_po_shipment_id,
5069: FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE)
5070: THEN
5071: FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
5072: , G_MODULE_PREFIX || l_api_name
5073: , l_api_name || ': Updated JMF_SHIKYU_ALLOCATIONS table, '
5074: || 'x_reduced_allocations_rec.qty = '
5075: || x_reduced_allocations_rec.qty
5076: );
5077: END IF;
5089: || ') >= p_existing_alloc_qty (' || p_existing_alloc_qty || ')'
5090: );
5091: END IF;
5092:
5093: DELETE FROM JMF_SHIKYU_ALLOCATIONS
5094: WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
5095: AND replenishment_so_line_id = p_replen_so_line_id
5096: RETURNING subcontract_po_shipment_id,
5097: replenishment_so_line_id,