APPS.EAM_AUTOMATICEST SQL Statements

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

Line: 10

   CST_eamCost_PUB.validate_for_reestimation. If it does it update
   the estimation_status to reestimate in wip_discrete_jobs.
*----------------------------------------------------------------------------*/


PROCEDURE  Call_Validate_for_Reestimation(
        p_wip_entity_id    IN   NUMBER ,
        x_return_status    OUT NOCOPY  VARCHAR2,
        x_msg_data         OUT NOCOPY  VARCHAR2
        ) IS

        l_est_status          NUMBER := 0;

Line: 33

  SELECT  estimation_status , status_type
    INTO  l_est_status , l_job_status
    FROM  WIP_DISCRETE_JOBS
   WHERE  wip_entity_id  = p_wip_entity_id;

Line: 71

          UPDATE  WIP_DISCRETE_JOBS
             SET  estimation_status = EAM_CONSTANTS.REESTIMATE
           WHERE  wip_entity_id = p_wip_entity_id;

Line: 79

          UPDATE  WIP_DISCRETE_JOBS
             SET  estimation_status = EAM_CONSTANTS.RUNREEST
           WHERE  wip_entity_id = p_wip_entity_id;

Line: 122

  SELECT  wro.wip_entity_id BULK COLLECT
    INTO  l_wip_entity_tbl
    FROM  WIP_REQUIREMENT_OPERATIONS wro , WIP_DISCRETE_JOBS wdj
   WHERE  wdj.wip_entity_id = wro.wip_entity_id
     AND  wdj.status_type
      IN  ( WIP_CONSTANTS.UNRELEASED , WIP_CONSTANTS.RELEASED ,
            WIP_CONSTANTS.COMP_CHRG , WIP_CONSTANTS.HOLD ,
            WIP_CONSTANTS.DRAFT )
     AND  wro.inventory_item_id = p_inv_item_id
     AND  wro.organization_id = p_org_id;

Line: 135

    /* if no data is selected */
  IF ( l_wip_entity_tbl.count = 0 ) THEN

    l_msg_data := ' OR  no data found ' ;

Line: 189

  SELECT  wor.wip_entity_id BULK COLLECT
    INTO  l_wip_entity_tbl
    FROM  WIP_OPERATION_RESOURCES wor , WIP_DISCRETE_JOBS wdj
   WHERE  wdj.wip_entity_id = wor.wip_entity_id
     AND  wdj.status_type
      IN  ( WIP_CONSTANTS.UNRELEASED , WIP_CONSTANTS.RELEASED ,
            WIP_CONSTANTS.COMP_CHRG , WIP_CONSTANTS.HOLD ,
            WIP_CONSTANTS.DRAFT )
     AND  wor.resource_id = p_resource_id
     AND  wor.organization_id = p_org_id;

Line: 202

    /* if no data is selected */
  IF ( l_wip_entity_tbl.count = 0 ) THEN

    l_msg_data := ' OR  no data found ' ;

Line: 279

    SELECT  nvl( prha.authorization_status , 'INCOMPLETE') , prla.destination_type_code,
            nvl( prla.cancel_flag , 'N') , nvl( prla.wip_entity_id , 0) ,
            nvl( plt.outside_operation_flag, 'N' )
      INTO  l_auth_status , l_dest_type ,
            l_cancel_flag , l_wip_entity_id ,
            l_osp
      FROM  PO_REQUISITION_LINES_ALL prla , PO_REQUISITION_HEADERS_ALL prha ,
            PO_LINE_TYPES plt
     WHERE  prla.requisition_line_id = p_req_line_id
       AND  prla.requisition_header_id = prha.requisition_header_id
       AND  prla.line_type_id = plt.line_type_id;

Line: 304

        SELECT  requisition_line_id
          INTO  l_count
          FROM  CST_EAM_WO_ESTIMATE_DETAILS
         WHERE  requisition_line_id = p_req_line_id
           AND  wip_entity_id = l_wip_entity_id;

Line: 324

        SELECT  nvl( required_quantity, 0) , nvl( rate, 0) ,
               nvl( item_cost, 0)
          INTO  l_req_qty , l_rate , l_unit_price
          FROM  CST_EAM_WO_ESTIMATE_DETAILS
         WHERE  requisition_line_id = p_req_line_id
           AND  wip_entity_id = l_wip_entity_id;

Line: 333

        SELECT  quantity , nvl( rate, 0 ) , unit_price
          INTO  l_req_qty_p , l_rate_p , l_unit_price_p
          FROM  PO_REQUISITION_LINES_ALL
         WHERE  requisition_line_id = p_req_line_id
           AND  wip_entity_id = l_wip_entity_id;

Line: 441

    SELECT  nvl( pha.authorization_status , 'INCOMPLETE') , pda.destination_type_code,
            nvl( pla.cancel_flag , 'N') , nvl( pda.wip_entity_id , 0) ,
            nvl( plt.outside_operation_flag, 'N' )
      INTO  l_auth_status , l_dest_type ,
            l_cancel_flag , l_wip_entity_id ,
            l_osp
      FROM  PO_DISTRIBUTIONS_ALL pda , PO_LINE_TYPES plt ,
            PO_LINES_ALL pla , PO_HEADERS_ALL pha
     WHERE  pda.po_line_id = pla.po_line_id
       AND  pla.line_type_id = plt.line_type_id
       AND  pda.po_distribution_id = p_po_dist_id
       AND  pda.po_header_id = pha.po_header_id ;

Line: 467

        SELECT  po_distribution_id , nvl( required_quantity, 0 ) , nvl( rate, 0 ) ,
                nvl( item_cost , 0 )
          INTO  l_count, l_req_qty , l_rate , l_unit_price
          FROM  CST_EAM_WO_ESTIMATE_DETAILS
         WHERE  po_distribution_id = p_po_dist_id
           AND  wip_entity_id = l_wip_entity_id;

Line: 488

       /* SELECT  nvl( required_quantity, 0 ) , nvl( rate, 0 ) ,
                nvl( item_cost , 0 )
          INTO  l_req_qty , l_rate ,
                l_unit_price
          FROM  CST_EAM_WO_ESTIMATE_DETAILS
         WHERE  po_distribution_id = p_po_dist_id
           AND  wip_entity_id = l_wip_entity_id ; */

Line: 505

          SELECT  prla.line_location_id
            INTO  l_line_location_id
            FROM  PO_DISTRIBUTIONS_ALL pda , PO_REQUISITION_LINES_ALL prla
           WHERE  pda.line_location_id = nvl( prla.line_location_id , -999 )
             AND  pda.po_distribution_id = p_po_dist_id ;

Line: 524

          SELECT  requisition_line_id
            INTO  l_req_line_id
            FROM  CST_EAM_WO_ESTIMATE_DETAILS
           WHERE  line_location_id = l_line_location_id
             AND  wip_entity_id = l_wip_entity_id;

Line: 532

          SELECT  nvl( required_quantity , 0 ) , nvl( rate , 0 ) ,
                  nvl( item_cost , 0 )
            INTO  l_req_qty , l_rate ,
                  l_unit_price
            FROM  CST_EAM_WO_ESTIMATE_DETAILS
           WHERE  requisition_line_id = l_req_line_id
             AND  wip_entity_id = l_wip_entity_id;

Line: 564

          SELECT  pda.quantity_ordered , nvl( pda.rate , 0 ) ,
                  nvl( pla.unit_price,0)
            INTO  l_req_qty_p , l_rate_p ,
                  l_unit_price_p
            FROM  PO_DISTRIBUTIONS_ALL pda , PO_LINES_ALL pla
           WHERE  pda.wip_entity_id = l_wip_entity_id
             AND  pda.po_distribution_id = p_po_dist_id
             AND  pda.po_line_id = pla.po_line_id;

Line: 632

    SELECT  pda.po_distribution_id  BULK COLLECT
      INTO  l_po_dist_tbl
      FROM  PO_LINES_ALL pla , PO_DISTRIBUTIONS_ALL pda
     WHERE  pla.po_line_id = p_po_line_id
       AND  pla.po_line_id = pda.po_line_id ;

Line: 640

    /* if no data is selected */
    IF ( l_po_dist_tbl.count = 0 ) THEN

      l_msg_data := ' OR  no data found ' ;