DBA Data[Home] [Help]

APPS.INV_CONSIGNED_DIAGNOSTICS_PROC SQL Statements

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

Line: 61

     SELECT uom_code
     INTO l_uom_code
     FROM   MTL_units_of_measure
     WHERE  unit_of_measure = p_uom ;
Line: 92

PROCEDURE Update_Consumption_Date
IS


BEGIN

  IF g_debug = 1
  THEN
   INV_LOG_UTIL.trace
   ( '>> Update_Consumption_Date',null,9);
Line: 104

  UPDATE mtl_consigned_diag_errors mcde
  SET   mcde.consumption_date =
      ( select MIN(mmt.transaction_date)
         FROM  mtl_material_transactions mmt
          WHERE mmt.inventory_item_id      = mcde.inventory_item_id
            and NVL(mmt.revision, -980980)  = NVL(mcde.revision,-980980)
            and mmt.organization_id        = mcde.organization_id
            and mmt.owning_organization_id = mcde.owning_organization_id
       )
   WHERE mcde.consumption_date is NULL
     and  mcde.record_type = 2 ;
Line: 120

   ( '>> Update_Consumption_Date',null,9);
Line: 135

      'Update_Consumption_Date',9);
Line: 140

END Update_Consumption_Date ;
Line: 157

 DELETE FROM
 mtl_consigned_diag_errors
 WHERE ( request_id <> g_request_id) OR (
  request_id is NULL ) ;
Line: 208

    INSERT INTO
       mtl_consigned_diag_errors
      ( RECORD_ID
      , ORGANIZATION_ID
      , INVENTORY_ITEM_ID
      , REVISION
      , OWNING_TP_TYPE
      , OWNING_ORGANIZATION_ID
      , PLANNING_TP_TYPE
      , PLANNING_ORGANIZATION_ID
      , PO_HEADER_ID
      , AGENT_ID
      , RECORD_TYPE
      , ERROR_CODE
      , ACTION_CODE
      , LAST_NOTIFICATION_DATE
      , NOTIFICATION_ID
      , CREATION_DATE
      , CREATED_BY
      , LAST_UPDATE_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_LOGIN
      , REQUEST_ID
      , PROGRAM_APPLICATION_ID
      , PROGRAM_ID
      , PROGRAM_UPDATE_DATE
      , error_type
      , primary_uom
      , purchasing_uom
      ) SELECT
          mtl_consigned_diag_errors_s.NEXTVAL
        , mcdet.ORGANIZATION_ID
        , mcdet.INVENTORY_ITEM_ID
        , mcdet.REVISION
        , 1
        , mcdet.OWNING_ORGANIZATION_ID
        , null --- PLANNING_ORGANIZATION_TYPE
        , null --- PLANNING_ORGANIZATION_ID
        , mcdet.PO_HEADER_ID
        , mcdet.AGENT_ID
        , mcdet.RECORD_TYPE
        , mcdet.mcde_ERROR_CODE
        , mcdet.mcde_ACTION_CODE
        , null               -- LAST_NOTIFICATION_DATE
        , null               -- NOTIFICATION_ID
        , sysdate            -- CREATION_DATE
        , g_user_id -- Created_by
        , sysdate            -- LAST_UPDATE_DATE
        , g_user_id
        , g_user_id
        , g_request_id
        , g_program_application_id
        , g_program_id
        , sysdate
        , error_type
        , primary_uom
        , purchasing_uom
        FROM mtl_consigned_diag_temp mcdet
        WHERE mcdet.error_code is not null
          AND NOT EXISTS
              ( SELECT 1
                FROM  mtl_consigned_diag_errors mcde
                WHERE mcde.organization_id        =
                      mcdet.organization_id
                  and mcde.owning_organization_id =
                      mcdet.owning_organization_id
                  and mcde.error_code        = mcdet.mcde_error_code
                  and mcde.inventory_item_id = mcdet.inventory_item_id
                  and mcde.record_type       = mcdet.record_type
                  and NVL(mcde.revision,-9876321)
                      = NVL(mcdet.revision,-9876321)
              );
Line: 287

           UPDATE mtl_consigned_diag_errors mcde
           SET mcde.request_id =
             ( SELECT g_request_id
               FROM mtl_consigned_diag_temp mcdet
               WHERE MCDE.organization_id   = mcdet.organization_id
                 AND MCDE.owning_organization_id   =
                       mcdet.owning_organization_id
                 AND MCDE.error_code        = mcdet.mcde_error_code
                 AND MCDE.inventory_item_id = mcdet.inventory_item_id
                 AND mcde.record_type       = mcdet.record_type
                 AND NVL(mcde.revision,-98763245 )
                    = NVL(mcdet.revision , -98763245 )
             )
       WHERE ( MCDE.request_id <>  g_request_id)  OR
          ( MCDE.request_id IS NULL ) ;
Line: 305

      INSERT INTO
       mtl_consigned_diag_errors
      ( RECORD_ID
      , ORGANIZATION_ID
      , INVENTORY_ITEM_ID
      , REVISION
      , OWNING_TP_TYPE
      , OWNING_ORGANIZATION_ID
      , PLANNING_TP_TYPE
      , PLANNING_ORGANIZATION_ID
      , PO_HEADER_ID
      , AGENT_ID
      , RECORD_TYPE
      , ERROR_CODE
      , ACTION_CODE
      , LAST_NOTIFICATION_DATE
      , NOTIFICATION_ID
      , CREATION_DATE
      , CREATED_BY
      , LAST_UPDATE_DATE
      , LAST_UPDATED_BY
      , LAST_UPDATE_LOGIN
      , REQUEST_ID
      , PROGRAM_APPLICATION_ID
      , PROGRAM_ID
      , PROGRAM_UPDATE_DATE
      , error_type
      , primary_uom
      , purchasing_uom
      ) SELECT
          mtl_consigned_diag_errors_s.NEXTVAL
        , mcdet.ORGANIZATION_ID
        , mcdet.INVENTORY_ITEM_ID
        , mcdet.REVISION
        , 1
        , mcdet.OWNING_ORGANIZATION_ID
        , null --- PLANNING_ORGANIZATION_TYPE
        , null --- PLANNING_ORGANIZATION_ID
        , mcdet.PO_HEADER_ID
        , mcdet.AGENT_ID
        , mcdet.RECORD_TYPE
        , mcdet.mcde_ERROR_CODE
        , mcdet.mcde_ACTION_CODE
        , null               -- LAST_NOTIFICATION_DATE
        , null               -- NOTIFICATION_ID
        , sysdate            -- CREATION_DATE
        , g_user_id -- Created_by
        , sysdate            -- LAST_UPDATE_DATE
        , g_user_id
        , g_user_id
        , g_request_id
        , g_program_application_id
        , g_program_id
        , sysdate
        , error_type
        , primary_uom
        , purchasing_uom
        FROM mtl_consigned_diag_temp mcdet
        WHERE mcdet.error_code is not null
          AND NOT EXISTS
              ( SELECT 1
                FROM  mtl_consigned_diag_errors mcde
                WHERE mcde.organization_id        =
                      mcdet.organization_id
                  and mcde.owning_organization_id =
                      mcdet.owning_organization_id
                  and mcde.error_code        = mcdet.mcde_error_code
                  and mcde.inventory_item_id = mcdet.inventory_item_id
                  and mcde.record_type       = mcdet.record_type
                  and mcde.po_header_id      = mcdet.po_header_id
                  and NVL(mcde.revision,-9876321)
                      = NVL(mcdet.revision,-9876321)
              )
          and mcdet.record_type = 2;
Line: 387

        UPDATE mtl_consigned_diag_errors mcde
           SET mcde.request_id =
             ( SELECT g_request_id
               FROM mtl_consigned_diag_temp mcdet
               WHERE MCDE.organization_id   = mcdet.organization_id
                 AND MCDE.owning_organization_id   =
                       mcdet.owning_organization_id
                 AND MCDE.error_code        = mcdet.mcde_error_code
                 AND MCDE.inventory_item_id = mcdet.inventory_item_id
                 AND mcde.record_type       = mcdet.record_type
                 AND mcde.po_header_id      = mcdet.po_header_id
                 AND NVL(mcde.revision,-98763245 )
                    = NVL(mcdet.revision , -98763245 )
             )
       WHERE ( MCDE.request_id <>  g_request_id)  OR
          ( MCDE.request_id IS NULL ) ;
Line: 538

      SELECT
         poh.PO_HEADER_ID
      ,  poh.AGENT_ID
      ,  poh.SEGMENT1
      FROM
        po_headers_all poh
      , po_lines_all pol
      , po_line_locations_all poll
     WHERE poh.po_header_id = pol.po_header_id
       AND poh.po_header_id = poll.po_header_id
       AND pol.po_header_id = poll.po_header_id
       AND pol.po_line_id   = poll.PO_LINE_ID
       AND poll.CONSIGNED_FLAG   = 'Y'
       AND pol.ITEM_ID          =   p_inventory_item_id
       AND poh.vendor_id        = l_vendor_id
       AND poh.vendor_site_id   = p_vendor_site_id
       AND ( pol.item_revision = p_revision
               OR pol.item_revision IS NULL ) ;
Line: 566

      SELECT agent_id
      INTO    l_buyer_id
      FROM po_headers_all
      WHERE po_header_id = p_po_header_id ;
Line: 572

       SELECT vendor_id
       INTO l_vendor_id
       FROM  po_vendor_sites_all
       WHERE vendor_site_id = p_vendor_site_id ;
Line: 647

IS  SELECT
      Poh.blanket_total_amount
    FROM
      po_headers_all poh
    , po_lines_all pol
    WHERE poh.po_header_id = pol.po_header_id
      AND NVL(poh.approved_flag,'Y')  = 'Y'
      AND (TRUNC(NVL(poh.start_date,sysdate -1)) <= TRUNC(sysdate))
      AND (TRUNC(NVL(poh.end_date,sysdate +1)) >= TRUNC(sysdate))
      AND (TRUNC(NVL(pol.expiration_date,sysdate )) >= TRUNC(sysdate))
      AND (NVL(poh.cancel_flag,'N') = 'N'
           OR NVL(pol.cancel_flag,'N') = 'N')
      AND (NVL(poh.cancel_flag,'N') = 'N'
           OR NVL(pol.cancel_flag,'N') = 'N')
      AND NVL(pol.closed_code,'OPEN') = 'OPEN'
      AND poh.po_header_id     = p_po_header_id
      AND pol.item_id          = p_inventory_item_id
      AND ( pol.item_revision = p_revision
          OR pol.item_revision IS NULL );
Line: 767

SELECT
    ORGANIZATION_ID
  , INVENTORY_ITEM_ID
  , REVISION
  , OWNING_ORGANIZATION_ID
  , po_header_id
FROM mtl_consigned_diag_temp ;
Line: 800

   DELETE FROM mtl_consigned_diag_temp ;
Line: 807

       ( 'REGULAR - Before Insert into mtl_consigned_diag_temp' ,
        'Consumption_Advice_diagnostics',9);
Line: 811

     INSERT into mtl_consigned_diag_temp (
        PO_header_id
      , INVENTORY_ITEM_ID
      , REVISION
      , organization_id
      , OWNING_ORGANIZATION_ID
      , RECORD_TYPE )
      SELECT
       DISTINCT
         mmt.transaction_source_id
        , mmt.inventory_item_id
        , mmt.revision
        , mmt.organization_id
        , mmt.owning_organization_id
        , 2
      FROM
        mtl_consumption_transactions mct
      , mtl_material_transactions mmt
      WHERE mct.transaction_id = mmt.transaction_id
        AND mct.consumption_processed_flag <> 'Y'
        AND mmt.transaction_type_id = 74 ;
Line: 837

        ( 'REVALIDATE - Before Insert into mtl_consigned_diag_temp' ,
           'Consumption_Advice_diagnostics',9);
Line: 841

     INSERT into mtl_consigned_diag_temp (
        PO_header_id
      , INVENTORY_ITEM_ID
      , REVISION
      , organization_id
      , OWNING_ORGANIZATION_ID
      , RECORD_TYPE )
      SELECT
         PO_header_id
        , inventory_item_id
        , revision
        , organization_id
        , owning_organization_id
        , 2
      FROM
        MTL_CONSIGNED_DIAG_ERRORS
      WHERE record_id = p_error_record_id ;
Line: 864

     ( 'after Insert into mtl_consigned_diag_temp' ,
      'Consumption_Advice_diagnostics',9);
Line: 943

     UPDATE mtl_consigned_diag_temp
     SET    error_code       = l_error_code
     ,    mcde_error_code    = l_cad_error_code
     ,    mcde_action_code   = l_cad_action_code
     , agent_id              = l_buyer_id
     , error_type            = l_error_type
     WHERE ORGANIZATION_ID         = l_cad_temp_rec.ORGANIZATION_ID
       AND  INVENTORY_ITEM_ID      = l_cad_temp_rec.INVENTORY_ITEM_ID
       AND  OWNING_ORGANIZATION_ID = l_cad_temp_rec.OWNING_ORGANIZATION_ID
       AND  nvl(revision,-98765432) = nvl(l_cad_temp_rec.revision, -98765432 )
       AND  po_header_id            = l_cad_temp_rec.po_header_id ;
Line: 966

         ( 'after  UPDATE mtl_consigned_diag_temp '
            ,'Consumption_Advice_diagnostics'
        , 9
        );
Line: 1035

SELECT
    ORGANIZATION_ID
  , INVENTORY_ITEM_ID
  , REVISION
  , OWNING_ORGANIZATION_ID
FROM mtl_consigned_diag_temp ;
Line: 1084

  DELETE FROM mtl_consigned_diag_temp ;
Line: 1091

      ( ' REgular - Before Insert into Temp ',
      'Ownership_transfer_diagnostics' ,9);
Line: 1095

    INSERT INTO mtl_consigned_diag_temp (
      ORGANIZATION_ID
    , INVENTORY_ITEM_ID
    , REVISION
    , OWNING_ORGANIZATION_ID
    , RECORD_TYPE
    ) SELECT DISTINCT
      ORGANIZATION_ID
    , INVENTORY_ITEM_ID
    , REVISION
    , OWNING_ORGANIZATION_ID
    , 1
    FROM MTL_ONHAND_QUANTITIES_DETAIL
    WHERE OWNING_TP_TYPE = 1 ;
Line: 1114

      ( ' REvalidate - Before Insert into Temp ',
      'Ownership_transfer_diagnostics' ,9);
Line: 1118

    INSERT INTO mtl_consigned_diag_temp (
      ORGANIZATION_ID
    , INVENTORY_ITEM_ID
    , REVISION
    , OWNING_ORGANIZATION_ID
    , RECORD_TYPE
    ) SELECT
      ORGANIZATION_ID
    , INVENTORY_ITEM_ID
    , REVISION
    , OWNING_ORGANIZATION_ID
    , 1
    FROM
      MTL_consigned_diag_errors
    WHERE record_id = p_error_record_id ;
Line: 1139

    ( 'after Insert into MCDET TEMP table ',
      'Ownership_transfer_diagnostics' ,9);
Line: 1288

     UPDATE mtl_consigned_diag_temp
     SET    error_code       = l_error_code
     ,    mcde_error_code    = l_mcde_error_code
     ,    mcde_action_code   = l_mcde_action_code
     , agent_id              = l_buyer_id
     , po_header_id          = l_error_po_id
     , error_type            = l_error_type
     , primary_uom           = l_primary_uom_code
     , purchasing_uom        = l_purchasing_uom_code
     WHERE ORGANIZATION_ID         = l_moqd_temp_rec.ORGANIZATION_ID
       AND  INVENTORY_ITEM_ID      = l_moqd_temp_rec.INVENTORY_ITEM_ID
       AND  OWNING_ORGANIZATION_ID = l_moqd_temp_rec.OWNING_ORGANIZATION_ID
      AND  nvl(revision,-98765439) = nvl(l_moqd_temp_rec.revision, -98765439 );
Line: 1318

         ( 'after  UPDATE mtl_consigned_diag_temp '
            ,'Ownership_transfer_diagnostics'
        , 9
        );
Line: 1417

   SELECT
    record_type
   , request_id
   , error_code
   INTO
     l_record_type
    , l_request_id
    , l_error_code
   FROM
    mtl_consigned_diag_errors
   WHERE record_id = p_error_record_id ;
Line: 1480

       DELETE from mtl_consigned_diag_errors
       WHERE record_id = p_error_record_id ;
Line: 1486

         DELETE from mtl_consigned_diag_errors
         WHERE record_id = p_error_record_id ;
Line: 1490

         INV_CONSIGNED_DIAGNOSTICS_PROC.Update_Consumption_Date ;
Line: 1591

  INV_CONSIGNED_DIAGNOSTICS_PROC.Update_Consumption_Date ;