APPS.JMF_SHIKYU_ALLOCATION_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 471

    INSERT INTO jmf_shikyu_allocations
    ( SUBCONTRACT_PO_SHIPMENT_ID
	, SHIKYU_COMPONENT_ID
	, REPLENISHMENT_SO_LINE_ID
	, ALLOCATED_QUANTITY
	, UOM
	, LAST_UPDATE_DATE
	, LAST_UPDATED_BY
	, CREATION_DATE
    , CREATED_BY
    , LAST_UPDATE_LOGIN
    )
    VALUES
    ( p_subcontract_po_shipment_id
    , p_component_id
    , p_replen_so_line_id
    , l_qty_to_allocate
    , l_primary_uom
    , sysdate
    , FND_GLOBAL.user_id
    , sysdate
    , FND_GLOBAL.user_id
    , FND_GLOBAL.login_id
    );

Line: 501

    UPDATE jmf_shikyu_allocations
    SET    allocated_quantity = l_allocation_qty,
           last_update_date = sysdate,
           last_updated_by = FND_GLOBAL.user_id,
           last_update_login = FND_GLOBAL.login_id
    WHERE  subcontract_po_shipment_id  = p_subcontract_po_shipment_id
    AND    replenishment_so_line_id = p_replen_so_line_id
    AND    shikyu_component_id = p_component_id;

Line: 520

      SELECT segment1
      INTO l_order_number
      FROM po_headers_all poh
      WHERE EXISTS
      (SELECT 1 FROM po_line_locations_all poll
       WHERE poll.line_location_id = p_subcontract_po_shipment_id
       AND poll.po_header_id = poh.po_header_id);

Line: 528

      SELECT segment1
      INTO l_sub_comp
      FROM mtl_system_items_b
      WHERE inventory_item_id = p_component_id
      AND organization_id = l_tp_organization_id ;

Line: 576

  UPDATE JMF_SHIKYU_REPLENISHMENTS
  SET    allocated_primary_uom_quantity = l_replen_allocated_primary_qty,
         allocated_quantity = l_replen_allocated_qty,
         last_update_date = sysdate,
         last_updated_by = FND_GLOBAL.user_id,
         last_update_login = FND_GLOBAL.login_id
  WHERE  replenishment_so_line_id = p_replen_so_line_id;

Line: 849

  SELECT DISTINCT jsr.replenishment_so_line_id,
                  jsr.shikyu_component_id,
                  jsr.allocable_quantity - jsr.allocated_quantity,
                  jsr.uom,
                  jsr.allocable_primary_uom_quantity - jsr.allocated_primary_uom_quantity,
                  jsr.primary_uom,
                  oola.schedule_ship_date
  FROM   jmf_shikyu_replenishments jsr,
         jmf_subcontract_orders jso,
         oe_order_lines_all oola
  WHERE  jsr.oem_organization_id = jso.oem_organization_id
  AND    jsr.tp_organization_id = jso.tp_organization_id
  AND    jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
  AND    jsr.shikyu_component_id = p_component_id
  AND    jsr.replenishment_so_line_id = oola.line_id
  AND    ((TRUNC(oola.schedule_ship_date) + l_ship_lead_time <=
           NVL(l_threshold_date, TRUNC(oola.schedule_ship_date) + l_ship_lead_time))
          OR
          (p_arrived_so_lines_only = 'Y'
           AND
           NVL(oola.shipped_quantity, 0) > 0
           AND
           jsr.allocated_primary_uom_quantity <
           INV_CONVERT.inv_um_convert( jsr.shikyu_component_id
                                     , 5
                                     , oola.shipped_quantity
                                     , oola.order_quantity_uom
                                     , JMF_SHIKYU_UTIL.Get_Primary_Uom_Code
                                       ( jsr.shikyu_component_id
                                       , jsr.tp_organization_id)
                                     , null
                                     , null)))
  AND    jsr.allocable_primary_uom_quantity - jsr.allocated_primary_uom_quantity > 0
  AND    DECODE(p_include_additional_supply,
                'Y', NVL(jsr.additional_supply, 'N'),
                'N')
         = NVL(jsr.additional_supply, 'N')
  ORDER BY oola.schedule_ship_date,
           jsr.replenishment_so_line_id;

Line: 891

  SELECT wdj.scheduled_start_date,
         wdj.scheduled_completion_date
  FROM   WIP_DISCRETE_JOBS wdj,
         JMF_SUBCONTRACT_ORDERS jso
  WHERE  wdj.wip_entity_id = jso.wip_entity_id
  AND    wdj.organization_id = jso.tp_organization_id
  AND    jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;

Line: 902

  SELECT NVL(mism.intransit_time, 0)
        , FROM_organization_id
        , to_organization_id
  FROM   MTL_INTERORG_SHIP_METHODS mism,
         JMF_SUBCONTRACT_ORDERS jso
  WHERE  mism.from_organization_id = jso.oem_organization_id
  AND    mism.to_organization_id = jso.tp_organization_id
  AND    mism.default_flag = 1
  AND    jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id;

Line: 922

  x_available_replen_tbl.delete;

Line: 1015

        SELECT organization_code INTO l_oem_organization
        FROM mtl_parameters mip
        WHERE exists
          (SELECT 1 FROM jmf_subcontract_orders jso
           WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
           AND jso.oem_organization_id = mip.organization_id);

Line: 1023

        SELECT organization_code INTO l_tp_organization
        FROM mtl_parameters mip
        WHERE exists
          (SELECT 1 FROM jmf_subcontract_orders jso
           WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
           AND jso.tp_organization_id = mip.organization_id);

Line: 1259

  SELECT DISTINCT plla.line_location_id,
                  pla.item_id,
                  plla.quantity,
                  muomv.uom_code,
                  plla.quantity,
                  muomv.uom_code,
                  NVL(plla.need_by_date, plla.promised_date),
                  pha.segment1,
                  pla.line_num,
                  plla.shipment_num
  FROM  jmf_subcontract_orders jso,
        hr_organization_information hoi,
        po_line_locations_all plla,
        po_lines_all pla,
        po_headers_all pha,
        mtl_units_of_measure_vl muomv
  WHERE jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
  AND   hoi.organization_id = jso.oem_organization_id
  AND   hoi.org_information_context = 'Customer/Supplier Association'
  AND   TO_NUMBER(hoi.org_information3) = pha.vendor_id
  AND   TO_NUMBER(hoi.org_information4) = pha.vendor_site_id
  AND   plla.ship_to_organization_id = jso.tp_organization_id
  AND   plla.po_line_id = pla.po_line_id
  AND   plla.po_header_id = pha.po_header_id
  AND   pla.item_id = p_component_id
  AND   pha.approved_flag = 'Y'
  AND   NVL(pha.cancel_flag, 'N') = 'N'
  AND   NVL(pla.cancel_flag, 'N') = 'N'
  AND   NVL(plla.cancel_flag, 'N') = 'N'
  AND   plla.unit_meas_lookup_code = muomv.unit_of_measure
  AND   NOT EXISTS (SELECT jsr.replenishment_so_line_id
                    FROM   jmf_shikyu_replenishments jsr
                    WHERE  jsr.replenishment_po_shipment_id = plla.line_location_id)
  ORDER BY NVL(plla.need_by_date, plla.promised_date),
           pha.segment1,
           pla.line_num,
           plla.shipment_num;

Line: 1339

  x_available_replen_tbl.DELETE;

Line: 1764

  x_new_replen_tbl.delete;

Line: 1767

  SELECT jso.oem_organization_id,
         jso.tp_organization_id,
         jsc.uom,
         jsc.primary_uom
  INTO   l_oem_organization_id,
         l_tp_organization_id,
         l_component_uom,
         l_primary_uom
  FROM   jmf_subcontract_orders jso,
         jmf_shikyu_components  jsc
  WHERE  jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
  AND    jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
  AND    jsc.shikyu_component_id = p_component_id;

Line: 2076

  SELECT msib.subcontracting_component
  INTO   l_subcontracting_component
  FROM   MTL_SYSTEM_ITEMS_B msib,
         JMF_SUBCONTRACT_ORDERS jso
  WHERE  jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
  AND    msib.inventory_item_id = p_component_id
  AND    msib.organization_id = jso.tp_organization_id;

Line: 2359

      SELECT segment1
      INTO l_order_number
      FROM po_headers_all poh
      WHERE EXISTS
      (SELECT 1 FROM po_line_locations_all poll
       WHERE poll.line_location_id = p_subcontract_po_shipment_id
       AND poll.po_header_id = poh.po_header_id);

Line: 2367

      SELECT segment1
      INTO l_sub_comp
      FROM mtl_system_items_b msi
      WHERE inventory_item_id = p_component_id
      AND exists
      (SELECT 1
       FROM jmf_subcontract_orders jso
       WHERE subcontract_po_shipment_id =  p_subcontract_po_shipment_id
       AND jso.tp_organization_id = msi.organization_id );

Line: 2705

  SELECT DISTINCT jsa.subcontract_po_shipment_id
       , oola.line_id
       , jsa.shikyu_component_id
       , jsa.allocated_quantity
       , jsa.uom
       , oola.schedule_ship_date
       , ooha.order_number
       , oola.line_number
  FROM   JMF_SHIKYU_ALLOCATIONS jsa,
         OE_ORDER_LINES_ALL     oola,
         OE_ORDER_HEADERS_ALL   ooha
  WHERE  jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
  AND    jsa.shikyu_component_id = p_component_id
  AND    oola.line_id = jsa.replenishment_so_line_id
  AND    ooha.header_id = oola.header_id
  ORDER BY oola.schedule_ship_date DESC,
           ooha.order_number DESC,
           oola.line_number DESC;

Line: 2728

  SELECT jsa.allocated_quantity, jsa.uom
  FROM   JMF_SHIKYU_ALLOCATIONS jsa
  WHERE  jsa.subcontract_po_shipment_id = p_subcontract_po_shipment_id
  AND    jsa.replenishment_so_line_id = p_replen_so_line_id;

Line: 2744

  x_reduced_allocations_tbl.DELETE;

Line: 2953

PROCEDURE Delete_Allocations
( p_api_version                IN  NUMBER
, p_init_msg_list              IN  VARCHAR2
, x_return_status              OUT NOCOPY VARCHAR2
, x_msg_count                  OUT NOCOPY NUMBER
, x_msg_data                   OUT NOCOPY VARCHAR2
, p_subcontract_po_shipment_id IN NUMBER
, p_component_id               IN NUMBER
, p_replen_so_line_id          IN NUMBER
, x_deleted_allocations_tbl     OUT NOCOPY g_allocation_qty_tbl_type
)
IS

l_api_name    CONSTANT VARCHAR2(30) := 'Delete_Allocations';

Line: 2970

l_deleted_primary_uom_qty NUMBER;

Line: 2996

        DELETE FROM jmf_shikyu_allocations
        WHERE  subcontract_po_shipment_id = p_subcontract_po_shipment_id
        AND    shikyu_component_id = p_component_id
        RETURNING subcontract_po_shipment_id,
                  replenishment_so_line_id,
                  shikyu_component_id,
                  allocated_quantity,
                  uom
        BULK COLLECT INTO x_deleted_allocations_tbl;

Line: 3008

        DELETE FROM jmf_shikyu_allocations
        WHERE  subcontract_po_shipment_id = p_subcontract_po_shipment_id
        AND    shikyu_component_id = p_component_id
        AND    replenishment_so_line_id = p_replen_so_line_id
        RETURNING subcontract_po_shipment_id,
                  replenishment_so_line_id,
                  shikyu_component_id,
                  allocated_quantity,
                  uom
        BULK COLLECT INTO x_deleted_allocations_tbl;

Line: 3023

      DELETE FROM jmf_shikyu_allocations
      WHERE  subcontract_po_shipment_id = p_subcontract_po_shipment_id
      RETURNING subcontract_po_shipment_id,
                replenishment_so_line_id,
                shikyu_component_id,
                allocated_quantity,
                uom
      BULK COLLECT INTO x_deleted_allocations_tbl;

Line: 3039

      DELETE FROM jmf_shikyu_allocations
      WHERE  replenishment_so_line_id = p_replen_so_line_id
      RETURNING subcontract_po_shipment_id,
                replenishment_so_line_id,
                shikyu_component_id,
                allocated_quantity,
                uom
      BULK COLLECT INTO x_deleted_allocations_tbl;

Line: 3053

  l_tbl_index := x_deleted_allocations_tbl.FIRST;

Line: 3062

                    || ': x_deleted_allocations_tbl.FIRST = ' || NVL(TO_CHAR(l_tbl_index), 'NULL'));

Line: 3069

      dbms_output.put_line('In Loop : IF x_deleted_allocations_tbl IS NOT NULL AND x_deleted_allocations_tbl.COUNT > 0');

Line: 3072

      SELECT jsr.uom
      INTO   l_replen_uom
      FROM   JMF_SHIKYU_REPLENISHMENTS jsr
      WHERE  jsr.replenishment_so_line_id = x_deleted_allocations_tbl(l_tbl_index).replenishment_so_line_id;

Line: 3077

      IF l_replen_uom <> x_deleted_allocations_tbl(l_tbl_index).qty_uom
      THEN

        l_deleted_primary_uom_qty := INV_CONVERT.inv_um_convert
                                     ( item_id       => x_deleted_allocations_tbl(l_tbl_index).component_id
                                     , precision     => 5
                                     , from_quantity => x_deleted_allocations_tbl(l_tbl_index).qty
                                     , from_unit     => x_deleted_allocations_tbl(l_tbl_index).qty_uom
                                     , to_unit       => l_replen_uom
                                     , from_name     => null
                                     , to_name       => null
                                     );

Line: 3091

        l_deleted_primary_uom_qty := x_deleted_allocations_tbl(l_tbl_index).qty;

Line: 3095

      UPDATE jmf_shikyu_replenishments
      SET    allocated_quantity = allocated_quantity - l_deleted_primary_uom_qty,
             allocated_primary_uom_quantity
             = allocated_primary_uom_quantity - x_deleted_allocations_tbl(l_tbl_index).qty,
             last_update_date = sysdate,
             last_updated_by = FND_GLOBAL.user_id,
             last_update_login = FND_GLOBAL.login_id
      WHERE  replenishment_so_line_id = x_deleted_allocations_tbl(l_tbl_index).replenishment_so_line_id;

Line: 3104

      l_tbl_index := x_deleted_allocations_tbl.next(l_tbl_index);

Line: 3133

END Delete_Allocations;

Line: 3164

  SELECT jsr.replenishment_so_line_id,
         jsr.shikyu_component_id,
         oola.shipped_quantity,
         jsr.uom,
         oola.ordered_quantity,
         jsr.primary_uom,
         oola.schedule_ship_date
  FROM   jmf_shikyu_replenishments jsr,
         oe_order_lines_all oola
  WHERE  jsr.replenishment_so_line_id = oola.line_id
  AND    jsr.status <> 'CLOSED'
  AND    jsr.status <> 'CANCELLED'
  AND    oola.open_flag = 'N'
  AND    oola.shipped_quantity <> oola.ordered_quantity
  AND    oola.shipped_quantity <> jsr.allocable_quantity;

Line: 3261

      UPDATE JMF_SHIKYU_REPLENISHMENTS
      SET    allocable_quantity = l_closed_so_line_rec.qty,
             allocable_primary_uom_quantity = l_closed_so_line_rec.primary_uom_qty,
             status = 'CLOSED',
             last_update_date = sysdate,
             last_updated_by = FND_GLOBAL.user_id,
             last_update_login = FND_GLOBAL.login_id
      WHERE  replenishment_so_line_id = l_closed_so_line_rec.replenishment_so_line_id;

Line: 3370

  SELECT jsa.subcontract_po_shipment_id,
         jsa.replenishment_so_line_id,
         jsa.shikyu_component_id,
         jsa.allocated_quantity,
         jsa.uom,
         NVL(plla.need_by_date, plla.promised_date),
         pha.segment1,
         pla.line_num,
         plla.shipment_num
  FROM   JMF_SHIKYU_ALLOCATIONS jsa,
         PO_LINE_LOCATIONS_ALL plla,
         PO_LINES_ALL pla,
         PO_HEADERS_ALL pha
  WHERE  jsa.replenishment_so_line_id = p_replen_order_line_id
  AND    jsa.shikyu_component_id = l_component_id
  AND    jsa.subcontract_po_shipment_id = plla.line_location_id
  AND    plla.po_line_id = pla.po_line_id
  AND    plla.po_header_id = pha.po_header_id
  ORDER BY NVL(plla.need_by_date, plla.promised_date) DESC,
           pha.segment1 DESC,
           pla.line_num DESC,
           plla.shipment_num DESC;

Line: 3422

  Select jsr.shikyu_component_id,
         jsr.primary_uom,
         jsr.allocable_primary_uom_quantity,
         jsr.allocated_primary_uom_quantity,
         oola.shipped_quantity,
         oola.order_quantity_uom
  INTO   l_component_id,
         l_primary_uom,
         l_allocable_primary_qty,
         l_allocated_primary_qty,
         l_shipped_primary_qty,
         l_ordered_uom
  FROM   JMF_SHIKYU_REPLENISHMENTS jsr,
         OE_ORDER_LINES_ALL oola
  WHERE  jsr.REPLENISHMENT_SO_LINE_ID = p_replen_order_line_id
  AND    jsr.replenishment_so_line_id = oola.line_id;

Line: 3471

    JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
    ( P_API_VERSION                => 1.0
    , P_INIT_MSG_LIST              => p_init_msg_list
    , X_RETURN_STATUS              => x_return_status
    , X_MSG_COUNT                  => x_msg_count
    , X_MSG_DATA                   => x_msg_data
    , P_SUBCONTRACT_PO_SHIPMENT_ID => NULL
    , P_COMPONENT_ID               => NULL
    , P_REPLEN_SO_LINE_ID          => p_replen_order_line_id
    , X_DELETED_ALLOCATIONS_TBL    => l_reduced_allocations_tbl
    );

Line: 3486

    DELETE FROM jmf_shikyu_replenishments
    WHERE  replenishment_so_line_id = p_replen_order_line_id;

Line: 3547

    UPDATE JMF_SHIKYU_REPLENISHMENTS
    SET    allocable_quantity = l_allocable_qty,
           allocable_primary_uom_quantity = l_allocable_primary_qty,
           last_update_date = sysdate,
           last_updated_by = FND_GLOBAL.user_id,
           last_update_login = FND_GLOBAL.login_id
    WHERE  replenishment_so_line_id = p_replen_order_line_id;

Line: 3692

l_deleted_qty         NUMBER;

Line: 3710

l_deleted_allocations_tbl
  JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type;

Line: 3714

SELECT jsr.replenishment_so_line_id      replenishment_so_line_id
     , jsr.replenishment_so_header_id    replenishment_so_header_id
     , jsr.schedule_ship_date            schedule_ship_date
     , jsr.replenishment_po_header_id    replenishment_po_header_id
     , jsr.replenishment_po_line_id      replenishment_po_line_id
     , jsr.replenishment_po_shipment_id  replenishment_po_shipment_id
     , jsr.oem_organization_id           oem_organization_id
     , jsr.tp_organization_id            tp_organization_id
     , oeh.cancelled_flag                oeh_cancelled_flag
     , oel.cancelled_flag                oel_cancelled_flag
     , oel.shipped_quantity              oel_shipped_quantity
     , oel.ordered_quantity              oel_ordered_quantity
     , jsr.shikyu_component_id           shikyu_component_id
     , jsr.ORDERED_QUANTITY              jsr_ordered_quantity
     , jsr.ALLOCATED_PRIMARY_UOM_QUANTITY  allocated_primary_UOM_quantity
   --, oel.closed_flag                   closed_flag
     , jsa.subcontract_po_shipment_id    subcontract_po_shipment_id
     , jsr.ALLOCABLE_PRIMARY_UOM_QUANTITY  allocable_primary_UOM_quantity
     , jsr.allocable_quantity            allocable_quantity
     , jsr.allocated_quantity            allocated_quantity
FROM JMF_SHIKYU_REPLENISHMENTS jsr
   , OE_ORDER_LINES_ALL        oel
   , OE_ORDER_HEADERS_ALL      oeh
   , JMF_SHIKYU_ALLOCATIONS    jsa
WHERE oeh.header_id                = jsr.REPLENISHMENT_SO_HEADER_ID
  AND oel.header_id                = oeh.header_id
  AND oel.line_id                  = jsr.replenishment_so_line_id
  AND jsa.shikyu_component_id      = jsr.shikyu_component_id
  AND jsa.replenishment_so_line_id = jsr.replenishment_so_line_id
  AND ( oeh.cancelled_flag  = 'Y'  OR
        oel.cancelled_flag = 'Y'   OR
        oel.ordered_quantity  < jsr.allocable_quantity );

Line: 3751

  SELECT line_id
       , ordered_quantity
       , schedule_ship_date
       , header_id
       , split_from_line_id
       , line_number  FROM (
			  SELECT line_id
			       , ordered_quantity
			       , schedule_ship_date
			       , header_id
			       , split_from_line_id
			       , line_number
			  FROM oe_order_lines_all
			  WHERE header_id = l_header_id  )
       CONNECT BY PRIOR line_id = split_from_line_id
       START WITH line_id       = l_parent_so_line_id;

Line: 3773

  SELECT line_id
       , ordered_quantity
       , schedule_ship_date
       , header_id
       , split_from_line_id
  FROM oe_order_lines_all
 WHERE   header_id = l_header_id
 and   split_from_line_id = l_parent_so_line_id ;

Line: 3852

    l_deleted_qty         := 0 ;

Line: 3961

          , 'JMFVSKAB :Invoke JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations '
                  , C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id);

Line: 3965

      JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations
      ( p_api_version                => 1.0
      , p_init_msg_list              => NULL
      , x_return_status              => l_return_status
      , x_msg_count                  => l_msg_count
      , x_msg_data                   => l_msg_data
      , p_subcontract_po_shipment_id =>
          C_SHIKYU_REPLENISHMENT_rec.subcontract_po_shipment_id
      , p_component_id               =>
          C_SHIKYU_REPLENISHMENT_rec.shikyu_component_id
      , p_replen_so_line_id          =>
          C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
      , x_deleted_allocations_tbl    => l_deleted_allocations_tbl
      );

Line: 3983

          , 'JMFVSKAB :AFter l_deleted_allocations_tbl.count '
                  , l_deleted_allocations_tbl.COUNT );

Line: 3991

      IF l_deleted_allocations_tbl.COUNT > 0
      THEN
        l_deleted_qty  :=  l_deleted_allocations_tbl(1).qty ;

Line: 3995

        l_deleted_qty := 0;

Line: 3996

      END IF; /* IF l_deleted_allocations_tbl.COUNT > 0 */

Line: 3998

      DELETE FROM JMF_SHIKYU_REPLENISHMENTS
      WHERE REPLENISHMENT_SO_LINE_ID =
            C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
      AND   REPLENISHMENT_SO_HEADER_ID =
            C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_header_id  ;

Line: 4007

          , 'JMFVSKAB :AFter DELETE FROM JMF_SHIKYU_REPLENISHMENTS '
                  , C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID );

Line: 4012

      IF l_deleted_qty > 0
      THEN
       IF  FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
       THEN
           FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
          , 'JMFVSKAB :1Cal JMF_SHIKYU_ALLOCATION_PVT.Create_New_Allocations '
                  , l_deleted_qty );

Line: 4031

        , p_qty                        => l_deleted_qty
        , p_skip_po_replen_creation     => 'N'
        );

Line: 4034

      END IF; /* l_deleted_qty > 0 */

Line: 4056

      SELECT UOM,
             PRIMARY_UOM
      INTO   l_uom,
             l_primary_uom
      FROM JMF_SHIKYU_REPLENISHMENTS
      WHERE REPLENISHMENT_SO_LINE_ID  =
            C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;

Line: 4140

      UPDATE JMF_SHIKYU_REPLENISHMENTS
      SET    allocable_quantity =
               C_SHIKYU_REPLENISHMENT_rec.oel_ordered_quantity,
             allocable_primary_uom_quantity = l_primary_uom_qty
      WHERE  REPLENISHMENT_SO_LINE_ID =
             C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID;

Line: 4150

          , 'JMFVSKAB :after UPDATE JMF_SHIKYU_REPLENISHMENTS '
                  , C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID );

Line: 4205

          , 'JMFVSKAB :Before insert after UOM '
                  , l_primary_uom_qty );

Line: 4212

        INSERT INTO JMF_SHIKYU_REPLENISHMENTS
        ( REPLENISHMENT_SO_LINE_ID
        , REPLENISHMENT_SO_HEADER_ID
        , SCHEDULE_SHIP_DATE
        , REPLENISHMENT_PO_HEADER_ID
        , REPLENISHMENT_PO_LINE_ID
        , REPLENISHMENT_PO_SHIPMENT_ID
        , OEM_ORGANIZATION_ID
        , TP_ORGANIZATION_ID
        , TP_SUPPLIER_ID
        , TP_SUPPLIER_SITE_ID
        , SHIKYU_COMPONENT_ID
        , ORDERED_QUANTITY
        , ALLOCATED_QUANTITY
        , ALLOCABLE_QUANTITY
        , ORDERED_PRIMARY_UOM_QUANTITY
        , ALLOCATED_PRIMARY_UOM_QUANTITY
        , ALLOCABLE_PRIMARY_UOM_QUANTITY
        , UOM
        , PRIMARY_UOM
        , ADDITIONAL_SUPPLY
        , ORG_ID
        , LAST_UPDATE_DATE
        , LAST_UPDATED_BY
        , CREATION_DATE
        , CREATED_BY
        , LAST_UPDATE_LOGIN
        , REQUEST_ID
        , PROGRAM_APPLICATION_ID
        , PROGRAM_ID
        , PROGRAM_UPDATE_DATE
        )
        SELECT
          C_child_so_lines_rec.line_id
        , C_child_so_lines_rec.header_id
        , C_child_so_lines_rec.schedule_ship_date
        , REPLENISHMENT_PO_HEADER_ID
        , REPLENISHMENT_PO_LINE_ID
        , REPLENISHMENT_PO_SHIPMENT_ID
        , OEM_ORGANIZATION_ID
        , TP_ORGANIZATION_ID
        , TP_SUPPLIER_ID
        , TP_SUPPLIER_SITE_ID
        , SHIKYU_COMPONENT_ID
        , C_child_so_lines_rec.ordered_quantity  -- ordered qty
        , 0                                      -- allocated qty
        , C_child_so_lines_rec.ordered_quantity  -- allocable qty
        , l_primary_uom_qty                      -- ordered qty in primary UOM
        , 0                                      -- allocated qty in primary UOM
        , l_primary_uom_qty                      -- allocable qty in primary UOM
        , UOM
        , PRIMARY_UOM
        , ADDITIONAL_SUPPLY
        , ORG_ID
        , LAST_UPDATE_DATE
        , LAST_UPDATED_BY
        , CREATION_DATE
        , CREATED_BY
        , LAST_UPDATE_LOGIN
        , REQUEST_ID
        , PROGRAM_APPLICATION_ID
        , PROGRAM_ID
        , PROGRAM_UPDATE_DATE
        FROM JMF_SHIKYU_REPLENISHMENTS  jsr
        WHERE REPLENISHMENT_SO_LINE_ID  =
              C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID
          AND NOT EXISTS (
              SELECT jsr1.REPLENISHMENT_SO_LINE_ID
              FROM JMF_SHIKYU_REPLENISHMENTS jsr1
              WHERE jsr1.REPLENISHMENT_SO_LINE_ID =
                      C_child_so_lines_rec.line_id );

Line: 4290

          , 'JMFVSKAB :after INSERT child = '|| C_child_so_lines_rec.line_id
                  , C_SHIKYU_REPLENISHMENT_rec.REPLENISHMENT_SO_LINE_ID );

Line: 4456

  SELECT oola.header_id,
         jsr.allocable_primary_uom_quantity,
         jsr.allocated_primary_uom_quantity,
         jsr.uom,
         jsr.primary_uom,
         'Y'
  INTO   x_header_id,
         x_allocable_primary_uom_qty,
         x_allocated_primary_uom_qty,
         x_uom,
         x_primary_uom,
         x_replen_so_line_exists
  FROM   JMF_SHIKYU_REPLENISHMENTS jsr,
         OE_ORDER_LINES_ALL oola
  WHERE  jsr.replenishment_so_line_id = p_replen_so_line_id
  AND    jsr.replenishment_so_line_id = oola.line_id;

Line: 4507

  SELECT jsa.allocated_quantity,
         jsa.uom,
         'Y'
  INTO   x_allocated_qty,
         x_uom,
         x_allocation_exists
  FROM   JMF_SHIKYU_ALLOCATIONS jsa
  WHERE  jsa.replenishment_so_line_id = p_replen_so_line_id
  AND    jsa.shikyu_component_id = p_component_id
  AND    jsa.subcontract_po_shipment_id  = p_subcontract_po_shipment_id;

Line: 4587

  SELECT plla.po_header_id,
         plla.po_line_id
  INTO   l_replen_po_header_id,
         l_replen_po_line_id
  FROM   PO_LINE_LOCATIONS_ALL plla
  WHERE  plla.line_location_id = p_replen_po_shipment_id;

Line: 4606

  SELECT oola.header_id,
         oola.ordered_quantity,
         oola.order_quantity_uom,
         oola.schedule_ship_date,
         oola.org_id
  INTO   l_replen_so_header_id,
         l_ordered_qty,
         l_ordered_uom,
         l_schedule_ship_date,
         l_org_id
  FROM   OE_ORDER_LINES_ALL oola
  WHERE  oola.line_id = p_replen_so_line_id;

Line: 4633

  SELECT TO_NUMBER(org_information3),
         TO_NUMBER(org_information4)
  INTO   l_tp_supplier_id,
         l_tp_supplier_site_id
  FROM   hr_organization_information
  WHERE  organization_id = p_tp_organization_id
  AND    org_information_context = 'Customer/Supplier Association';

Line: 4699

  INSERT INTO JMF_SHIKYU_REPLENISHMENTS
  ( replenishment_so_line_id
  , replenishment_so_header_id
  , schedule_ship_date
  , replenishment_po_header_id
  , replenishment_po_line_id
  , replenishment_po_shipment_id
  , oem_organization_id
  , tp_organization_id
  , tp_supplier_id
  , tp_supplier_site_id
  , shikyu_component_id
  , ordered_quantity
  , allocated_quantity
  , allocable_quantity
  , ordered_primary_uom_quantity
  , allocated_primary_uom_quantity
  , allocable_primary_uom_quantity
  , uom
  , primary_uom
  , org_id
  , additional_supply
  , status
  , last_update_date
  , last_updated_by
  , creation_date
  , created_by
  , last_update_login
  )
  VALUES
  ( p_replen_so_line_id
  , l_replen_so_header_id
  , l_schedule_ship_date
  , l_replen_po_header_id
  , l_replen_po_line_id
  , p_replen_po_shipment_id
  , p_oem_organization_id
  , p_tp_organization_id
  , l_tp_supplier_id
  , l_tp_supplier_site_id
  , p_component_id
  , l_ordered_qty
  , 0
  , l_ordered_qty
  , l_primary_uom_qty
  , 0
  , l_primary_uom_qty
  , l_ordered_uom
  , l_primary_uom
  , l_org_id
  , p_additional_supply
  , NULL
  , SYSDATE
  , FND_GLOBAL.user_id
  , SYSDATE
  , FND_GLOBAL.user_id
  , FND_GLOBAL.login_id
  );

Line: 4818

  SELECT
    oem_organization_id, tp_organization_id
  INTO
    l_oem_org_id, l_mp_org_id
  FROM
    JMF_SUBCONTRACT_ORDERS
  WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id;

Line: 4841

    SELECT count(*)
    INTO   l_count
    FROM   jmf_shikyu_components jsc
    WHERE  jsc.shikyu_component_id = p_component_id
    AND    jsc.subcontract_po_shipment_id = p_subcontract_po_shipment_id
    AND    EXISTS (SELECT 'x'
                  FROM   oe_order_lines_all oola,
                          oe_order_headers_all ooha
                  WHERE  oola.line_id = p_replen_so_line_id
                  AND    oola.inventory_item_id = p_component_id
                  AND    oola.price_list_id = jsc.price_list_id
                  AND    oola.header_id = ooha.header_id
                  AND    ooha.transactional_curr_code = jsc.currency
                  AND    oola.unit_selling_price
                          = DECODE(oola.pricing_quantity_uom,
                                  jsc.uom        , jsc.shikyu_component_price,
                                  jsc.primary_uom, jsc.primary_uom_price,
                                  -1));

Line: 4923

  SELECT count(*)
  INTO   l_count
  FROM   mtl_parameters         mtlp,
         jmf_subcontract_orders jso
  WHERE  mtlp.organization_id = jso.tp_organization_id
  AND    jso.subcontract_po_shipment_id = p_subcontract_po_shipment_id
  AND    (  (mtlp.project_control_level = 1
             AND EXISTS (SELECT 'x'
                         FROM    oe_order_lines_all oola
                         WHERE   oola.line_id = p_replen_so_line_id
                         AND     jso.project_id IS NOT NULL
                         AND     jso.project_id = oola.project_id
                         AND     NVL(jso.task_id, -1) = NVL(oola.task_id, -1)))
         OR (mtlp.project_control_level = 2
             AND EXISTS (SELECT 'x'
                         FROM   oe_order_lines_all oola
                         WHERE  oola.line_id = p_replen_so_line_id
                         AND    jso.project_id IS NOT NULL
                         AND    jso.task_id IS NOT NULL
                         AND    jso.project_id = oola.project_id
                         AND    jso.task_id = oola.task_id))
         OR (jso.project_id IS NULL
             AND jso.task_id IS NULL
             AND EXISTS (SELECT 'x'
                         FROM   oe_order_lines_all oola
                         WHERE  oola.line_id = p_replen_so_line_id
                         AND    oola.project_id IS NULL
                         AND    oola.task_id IS NULL))
         );

Line: 5051

    UPDATE    JMF_SHIKYU_ALLOCATIONS
    SET       allocated_quantity = l_new_allocated_qty
    WHERE     subcontract_po_shipment_id = p_subcontract_po_shipment_id
    AND       replenishment_so_line_id = p_replen_so_line_id
    RETURNING subcontract_po_shipment_id,
              replenishment_so_line_id,
              shikyu_component_id,
              allocated_quantity,
              uom
    INTO      x_reduced_allocations_rec;

Line: 5073

                    , l_api_name || ': Updated JMF_SHIKYU_ALLOCATIONS table, '
                      || 'x_reduced_allocations_rec.qty = '
                      || x_reduced_allocations_rec.qty
                    );

Line: 5093

    DELETE FROM JMF_SHIKYU_ALLOCATIONS
    WHERE subcontract_po_shipment_id = p_subcontract_po_shipment_id
    AND   replenishment_so_line_id = p_replen_so_line_id
    RETURNING subcontract_po_shipment_id,
              replenishment_so_line_id,
              shikyu_component_id,
              allocated_quantity,
              uom
    INTO x_reduced_allocations_rec;

Line: 5105

  SELECT uom
  INTO   l_replen_uom
  FROM   JMF_SHIKYU_REPLENISHMENTS
  WHERE  replenishment_so_line_id = p_replen_so_line_id;

Line: 5161

  UPDATE    JMF_SHIKYU_REPLENISHMENTS
  SET       allocated_quantity = allocated_quantity - l_remain_qty_to_reduce,
            allocated_primary_uom_quantity = allocated_primary_uom_quantity - l_reduce_replen_uom_qty
  WHERE     replenishment_so_line_id = p_replen_so_line_id;