eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JMF_SHIKYU_RECONCILIAITON_PVT SQL Statements

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

Line: 82


  SELECT SUBCONTRACT_PO_SHIPMENT_ID
 ,     SHIKYU_COMPONENT_ID
 ,     OEM_ORGANIZATION_ID
 ,     SHIKYU_COMPONENT_PRICE
 ,     PRIMARY_UOM
 FROM  JMF_SHIKYU_COMPONENTS
 WHERE SUBCONTRACT_PO_SHIPMENT_ID  = p_SUBCONTRACT_PO_SHIPMENT_ID
   ;

Line: 281


PROCEDURE update_replenishment_date
( p_subcontract_po_shipment_id IN NUMBER
, p_oem_organization    IN NUMBER
,  p_tp_organization    IN NUMBER
, p_replen_so_line_id   IN NUMBER
, p_replen_so_header_id IN NUMBER
, p_component_id        IN NUMBER
, p_new_ship_date       IN DATE
, p_allocation_date     IN DATE
) IS


l_date            DATE;

Line: 349


CURSOR C_select_replenishments_CSR
IS
SELECT 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
,SHIKYU_COMPONENT_ID
FROM jmf_shikyu_replenishments
WHERE REPLENISHMENT_SO_HEADER_ID = p_replen_so_header_id
AND REPLENISHMENT_SO_LINE_ID = p_replen_so_line_id ;

Line: 366


SELECT poh.segment1
,      poh.revision_num
,      pol.po_line_id
,      pol.line_num
,      poll.line_location_id
,      poll.need_by_date
,     poll.SHIPMENT_NUM
FROM po_headers_all poh
,    po_lines_all pol
,    po_line_locations_all poll
WHERE poh.po_header_id = pol.po_header_id
AND   pol.po_header_id = poll.po_header_id
AND   pol.po_line_id   = poll.po_line_id
AND   poll.line_location_id = l_repl_po_shipment_id
AND   poll.po_header_id     = l_repl_po_header_id
AND   poll.po_line_id       = l_repl_po_line_id
AND   poh.po_header_id      = l_repl_po_header_id
AND   pol.po_line_id        = l_repl_po_line_id ;

Line: 390


        'INTO update_replenishment_date FOR SCO PO SHIPID:  '
                  , p_subcontract_po_shipment_id);

Line: 409


FOR C_select_replenishments_REC IN C_select_replenishments_CSR
LOOP
    IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
    FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
                  , G_MODULE_PREFIX ||
        'Start OM rescheduling : REPLENISHMENT_SO_LINE_ID '
                  , C_select_replenishments_REC.REPLENISHMENT_SO_LINE_ID);

Line: 420


   l_line_rec.operation  := oe_globals.G_OPR_UPDATE;

Line: 423


            C_select_replenishments_REC.REPLENISHMENT_SO_LINE_ID;

Line: 425


         C_select_replenishments_REC.REPLENISHMENT_SO_HEADER_ID ;

Line: 488


                  , C_select_replenishments_REC.REPLENISHMENT_SO_LINE_ID);

Line: 498


      , C_select_replenishments_REC.REPLENISHMENT_PO_SHIPMENT_ID);

Line: 506


         C_select_replenishments_REC.replenishment_po_line_id ;

Line: 508


          C_select_replenishments_REC.replenishment_po_header_id ;

Line: 510


                 C_select_replenishments_REC.replenishment_po_shipment_id ;

Line: 547


        l_return_number := PO_CHANGE_API1_S.update_po
        (
          X_PO_NUMBER                   =>
                  C_PO_details_REC.segment1
        , X_RELEASE_NUMBER              =>
                 NULL
        , X_REVISION_NUMBER             =>
                NVL(C_PO_details_REC.revision_num,0)
        , X_LINE_NUMBER                 =>
               NVL(C_PO_details_REC.line_num,1)
        , X_SHIPMENT_NUMBER             =>
               NVL(C_PO_details_REC.shipment_num,1)
        , NEW_QUANTITY                  => NULL
        , NEW_PRICE                     => NULL
        , NEW_PROMISED_DATE             => NULL
        , NEW_NEED_BY_DATE              => p_allocation_date
        , LAUNCH_APPROVALS_FLAG         => 'Y'
        , UPDATE_SOURCE                 => NULL
        , VERSION                       => 1.0
        , X_OVERRIDE_DATE               => NULL
         -- 
        , X_API_ERRORS                  => l_api_errors
          -- 
       , p_BUYER_NAME                  => null
          -- 
        , p_secondary_quantity          => null
        , p_preferred_grade             => null
           -- 
         ) ;

Line: 609


        'completed  update_replenishment_date'
                  , 'EXit');

Line: 613


END update_replenishment_date ;

Line: 641


    SELECT
      delivery_detail_id
    , released_status
    INTO
      l_id
    , l_release_status
    FROM
     WSH_DELIVERY_DETAILS
    WHERE source_header_id = p_header_id
      AND source_line_id   = p_line_id
      AND NVL(released_status,'R') = 'Y' ;

Line: 784


   SELECT  REPLENISHMENT_PO_LINE_ID
   INTO l_REPLENISHMENT_PO_LINE_ID
   FROM jmf_shikyu_replenishments
   WHERE REPLENISHMENT_SO_LINE_ID   = p_replen_so_line_id
     AND REPLENISHMENT_SO_HEADER_ID =  p_replen_so_header_id ;

Line: 828


      SELECT SUBCONTRACT_PO_SHIPMENT_ID
      INTO   L_SUBCONTRACT_PO_SHIPMENT_ID
      FROM JMF_SHIKYU_ALLOCATIONS
      WHERE SUBCONTRACT_PO_SHIPMENT_ID <>
             p_subcontract_po_shipment_id
       AND REPLENISHMENT_SO_LINE_ID = p_replen_so_line_id ;

Line: 897


  l_deleted_qty              NUMBER ;

Line: 903


   SELECT  alc.SUBCONTRACT_PO_SHIPMENT_ID
         , alc.SHIKYU_COMPONENT_ID
         , alc.REPLENISHMENT_SO_LINE_ID
         , alc.ALLOCATED_QUANTITY
         , alc.UOM
         , oel.line_id
         , oel.header_id
         , oel.schedule_ship_date
         --Bugfix 14246759: Adding actual_shipment_date
         , oel.actual_shipment_date
         , oel.ordered_quantity
         , oel.shipped_quantity
         , oel.invoiced_quantity
         , oel.CANCELLED_FLAG
         , oel.OPEN_FLAG
         , oel.BOOKED_FLAG
         , oel.shipping_interfaced_flag
   FROM    JMF_SHIKYU_ALLOCATIONS alc
         , OE_ORDER_LINES_ALL oel
   WHERE  alc.SUBCONTRACT_PO_SHIPMENT_ID = p_SUBCONTRACT_PO_SHIPMENT_ID
     AND  oel.line_id = alc.REPLENISHMENT_SO_LINE_ID
     AND  oel.open_flag = 'Y'
     AND  alc.SHIKYU_COMPONENT_ID = l_SHIKYU_COMPONENT_ID;

Line: 928


    SELECT SUBCONTRACT_PO_SHIPMENT_ID
         , SHIKYU_COMPONENT_ID
         , OEM_ORGANIZATION_ID
    FROM jmf_shikyu_components
    WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_SUBCONTRACT_PO_SHIPMENT_ID
    order by SHIKYU_COMPONENT_ID ;

Line: 935


  l_deleted_allocations_tbl JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type ;

Line: 1126


                          , 'Call Delete_Allocations for repl SO LINE ID '||
                            C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID);

Line: 1130


           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_NEED_BY_DATE_rec.subcontract_po_shipment_id
           , p_component_id               => C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
           , p_replen_so_line_id          => C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
           , x_deleted_allocations_tbl    => l_deleted_allocations_tbl
           );

Line: 1183


                          , 'Call update_replenishment_date for SO LINE ID:' || C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID);

Line: 1186


           update_replenishment_date
           ( p_subcontract_po_shipment_id => p_subcontract_po_shipment_id
           , p_oem_organization           => p_oem_organization_id
           , p_tp_organization            => p_tp_organization_id
           , p_replen_so_line_id          => C_NEED_BY_DATE_rec.REPLENISHMENT_SO_LINE_ID
           , p_replen_so_header_id        => C_NEED_BY_DATE_rec.header_id
           , p_component_id               => C_NEED_BY_DATE_rec.SHIKYU_COMPONENT_ID
           , p_new_ship_date              => l_reschedule_date
           , p_allocation_date            => l_allocation_date
           );

Line: 1200


                          , 'After update_replenishment_date');

Line: 1318


l_deleted_qty          NUMBER ;

Line: 1322


SELECT  sco.SUBCONTRACT_PO_SHIPMENT_ID  SUBCONTRACT_PO_SHIPMENT_ID
,       sco.SUBCONTRACT_PO_HEADER_ID    SUBCONTRACT_PO_HEADER_ID
,       sco.SUBCONTRACT_PO_LINE_ID      SUBCONTRACT_PO_LINE_ID
,       trunc(sco.NEED_BY_DATE)         sco_NEED_BY_DATE
,       sco.UOM                         UOM
,       sco.CURRENCY                    CURRENCY
,       sco.OEM_ORGANIZATION_ID         OEM_ORGANIZATION_ID
,       sco.TP_ORGANIZATION_ID          TP_ORGANIZATION_ID
,       sco.WIP_ENTITY_ID               WIP_ENTITY_ID
,       sco.OSA_ITEM_ID                 OSA_ITEM_ID
,       wdj.start_quantity              start_quantity
,       NVL(trunc(poll.need_by_date), trunc(poll.promised_date))
        pol_need_by_date
,       poll.quantity                   quantity
,       sco.quantity                    old_ordered_quantity
,       poll.unit_meas_lookup_code      purchasing_uom
,       pol.cancel_flag                 pol_cancel_flag
,       poh.cancel_flag                 poh_cancel_flag
,       poll.cancel_flag                poll_cancel_flag
,      trunc( wdj.SCHEDULED_START_DATE) SCHEDULED_START_DATE
FROM    JMF_SUBCONTRACT_ORDERS  sco
,       po_headers_all          poh
,       po_lines_all            pol
,       po_line_locations_all   poll
,       wip_discrete_jobs       wdj
WHERE   pol.po_header_id         = sco.SUBCONTRACT_PO_HEADER_ID
  and   pol.po_line_id           = sco.SUBCONTRACT_PO_LINE_ID
  and   poll.po_line_id          = pol.po_line_id
  and   poll.line_location_id = sco.SUBCONTRACT_PO_SHIPMENT_ID
  and   wdj.wip_entity_id        = sco.WIP_ENTITY_ID
  and   sco.interlock_status     = 'P'
  and   ( trunc(sco.NEED_BY_DATE)  <>
NVL(trunc(poll.need_by_date), trunc(poll.promised_date)) OR
                sco.quantity <> poll.quantity  OR
                pol.cancel_flag = 'Y' OR
                poh.cancel_flag = 'Y' OR
                poll.cancel_flag = 'Y' )
  and   poh.po_header_id         = sco.SUBCONTRACT_PO_HEADER_ID
  and   pol.po_header_id         = poh.po_header_id
ORDER BY sco.subcontract_po_shipment_id ;

Line: 1366


  SELECT SUBCONTRACT_PO_SHIPMENT_ID
 ,     SHIKYU_COMPONENT_ID
 ,     OEM_ORGANIZATION_ID
 ,     SHIKYU_COMPONENT_PRICE
 ,     PRIMARY_UOM
 FROM  JMF_SHIKYU_COMPONENTS
 WHERE SUBCONTRACT_PO_SHIPMENT_ID  = l_subcontract_po_shipment_id ;

Line: 1376


l_deleted_allocations_tbl
         JMF_SHIKYU_ALLOCATION_PVT.g_allocation_qty_tbl_type ;

Line: 1621


                              , 'Calling JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations.');

Line: 1624


             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 => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
             , p_component_id               => NULL
             , p_replen_so_line_id          => NULL
             , x_deleted_allocations_tbl    => l_deleted_allocations_tbl
             );

Line: 1639


                              , 'Delete_Allocations:: Status => '|| l_return_status );

Line: 1690


                                , 'Calling JMF_SHIKYU_ALLOCATION_PVT.Delete_Allocations:' || C_shikyu_cancel_comp_rec.shikyu_component_id );

Line: 1693


                 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 => SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
                 , p_component_id               => C_shikyu_cancel_comp_rec.shikyu_component_id
                 , p_replen_so_line_id          => NULL
                 , x_deleted_allocations_tbl    => l_deleted_allocations_tbl
                 );

Line: 1711


                         , 'JMFVSKRB: UPDATE and COMMIT for SCO PO CANCEL flow  ' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );

Line: 1715


           UPDATE JMF_SUBCONTRACT_ORDERS
           SET interlock_status = 'T'
           WHERE SUBCONTRACT_PO_SHIPMENT_ID = SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID
           and ( SHIKYU_reconcile_rec.pol_cancel_flag = 'Y' OR
                 SHIKYU_reconcile_rec.poh_cancel_flag = 'Y' OR
                 SHIKYU_reconcile_rec.poll_cancel_flag = 'Y' );

Line: 1889


             UPDATE JMF_SUBCONTRACT_ORDERS
             SET    quantity = SHIKYU_reconcile_rec.quantity
                  , need_by_date = SHIKYU_reconcile_rec.pol_need_by_date
                  , last_update_date = sysdate
                  , last_updated_by = FND_GLOBAL.user_id
                  , last_update_login = FND_GLOBAL.login_id
             WHERE  SUBCONTRACT_PO_HEADER_ID = SHIKYU_reconcile_rec.SUBCONTRACT_PO_HEADER_ID
               and  SUBCONTRACT_PO_LINE_ID = SHIKYU_reconcile_rec.SUBCONTRACT_PO_LINE_ID
               and  SUBCONTRACT_PO_SHIPMENT_ID = SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID ;

Line: 1904


                              , 'AFTER UPDATE JMF_SUBCONTRACT_ORDERS:' || SHIKYU_reconcile_rec.SUBCONTRACT_PO_SHIPMENT_ID );