DBA Data[Home] [Help]

APPS.GMP_WPS_WRITER SQL Statements

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

Line: 9

*     update_batch_header
*
*   DESCRIPTION
*     This procedure will update the batch plan start and end date after
*     once the WPS scheduling engine has completed.
*   HISTORY
*     M Craig  created
*     Rajesh Patangya Removed Materail Update R12.0.
************************************************************************/
PROCEDURE update_batch_header(
  pbatch_id      IN  NUMBER,
  pstart_date    IN  NUMBER,
  pend_date      IN  NUMBER,
  plast_update   IN  NUMBER,
  phorizon       IN  NUMBER,
  puser_id       IN  NUMBER,
  plogin_id      IN  NUMBER,
  return_status  OUT NOCOPY NUMBER)

IS

  v_batch_id       NUMBER;
Line: 31

  last_update_date DATE;
Line: 36

    SELECT
      gbh.batch_status,
      gbh.last_update_date
    FROM
      gme_batch_header gbh
    WHERE
      batch_id = v_batch_id;
Line: 45

  v_last_update_date DATE;
Line: 56

  FETCH validate_batch_header INTO v_batch_status, v_last_update_date;
Line: 61

    last_update_date := wip_datetimes.float_to_DT(plast_update/1440+phorizon+1);
Line: 63

    IF v_last_update_date > last_update_date THEN
      return_status := -2;
Line: 69

        UPDATE
          gme_batch_header
        SET
          plan_start_date = v_start_date,
          plan_cmplt_date = v_end_date,
          finite_scheduled_ind = 1,
          last_update_date = SYSDATE,
          last_updated_by = puser_id,
          last_update_login = plogin_id
        WHERE
          batch_id = v_batch_id;
Line: 81

        UPDATE
          gme_batch_header
        SET
          plan_cmplt_date = v_end_date,
          finite_scheduled_ind = 1,
          last_update_date = SYSDATE,
          last_updated_by = puser_id,
          last_update_login = plogin_id
        WHERE
          batch_id = v_batch_id;
Line: 103

      log_message('Failure occured during Batch Header Update: ' || pbatch_id);
Line: 106

END update_batch_header;
Line: 111

*     update_batch_steps
*
*   DESCRIPTION
*     This procedure will update the batch step plan start and end date after
*     the WPS scheduling engine has completed.
*
*   HISTORY
*     M Craig  -- created
*     Rajesh Patangya -- Modified for Release 12.0
*     If the item is associated to step and NOT having release_type of
*     Automatic (0) in the material detail then the step's plan_start_date
*     will be used for all ingredients (line_type= -1) and plan_cmplt_date
*     for all products and byproducts (line_type = 1 or 2).
*     If the item is not associated to step OR Item is associated to step and
*     having release_type of Automatic (0) in the material detail then the
*     batch's plan_start_date will be used for all ingredients (line_type= -1)
*     and plan_cmplt_date for all products and byproducts (line_type = 1 or 2)
*
************************************************************************/
PROCEDURE update_batch_steps(
  pbatch_id      IN  NUMBER,
  pstep_no       IN  NUMBER_TBL_TYPE,
  pstep_id       IN  NUMBER_TBL_TYPE,
  pstart_date    IN  NUMBER_TBL_TYPE,
  pend_date      IN  NUMBER_TBL_TYPE,
  plast_update   IN  NUMBER_TBL_TYPE,
  phorizon       IN  NUMBER,
  puser_id       IN  NUMBER,
  plogin_id      IN  NUMBER,
  pnum_rows      IN  NUMBER,
  return_status  OUT NOCOPY NUMBER)

IS

  v_batch_id   NUMBER ;
Line: 151

  last_update_date DATE;
Line: 154

    SELECT
      gbs.last_update_date,
      gbs.step_status
    FROM
      gme_batch_steps gbs
    WHERE
          gbs.batch_id = v_batch_id
      AND gbs.batchstep_no = v_step_no;
Line: 163

  v_last_update_date   DATE;
Line: 170

    SELECT material_detail_id, line_type
      FROM gme_material_details
    WHERE batch_id = v_batch_id ;
Line: 191

    FETCH validate_step INTO v_last_update_date, v_step_status;
Line: 198

      last_update_date := wip_datetimes.float_to_DT(plast_update(i)/1440+phorizon+1);
Line: 199

      IF v_last_update_date > last_update_date THEN
        return_status := -2;
Line: 205

        UPDATE
          gme_batch_steps
        SET
          plan_start_date  = v_start_date,
          plan_cmplt_date  = v_end_date,
          last_update_date = SYSDATE,
          last_updated_by  = puser_id
        WHERE
              batch_id     = v_batch_id
          AND batchstep_no = v_step_no;
Line: 219

        /* Update to the charges */
        BEGIN
        UPDATE GME_BATCH_STEP_CHARGES
          SET
            PLAN_START_DATE  = v_start_date,
            plan_cmplt_date  = v_end_date,
            last_update_date = SYSDATE,
            last_updated_by  = puser_id
         WHERE
              batch_id     = v_batch_id
           AND batchstep_id = v_step_id;
Line: 238

	     log_message('Failure occured Charge Update: ' || pbatch_id);
Line: 263

          GME_API_GRP.update_material_date(
             v_material_detail_id,  --  p_material_detail_id,
             NULL,                  --  p_material_date
             m_return_status);
Line: 288

	log_message('Failure occured during Batch Step Update: ' || pbatch_id);
Line: 290

END update_batch_steps;
Line: 296

*     update_batch_activities
*
*   DESCRIPTION
*     This procedure will update the batch step activity plan start and
*     end date once the WPS scheduling engine has completed.
*   HISTORY
*     M Craig
*     Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE update_batch_activities(
  pbatch_id      IN  NUMBER,
  pstep_id       IN  NUMBER,
  pactivity_id   IN  NUMBER,
  pstart_date    IN  NUMBER,
  pend_date      IN  NUMBER,
  plast_update   IN  NUMBER,
  phorizon       IN  NUMBER,
  puom_hour      IN  VARCHAR2,
  puser_id       IN  NUMBER,
  plogin_id      IN  NUMBER,
  return_status  OUT NOCOPY NUMBER)

IS

  v_activity_id    NUMBER;
Line: 331

  last_update_date DATE;
Line: 334

    SELECT
      gsa.last_update_date,
      gbs.step_status
    FROM
      gme_batch_steps gbs,
      gme_batch_step_activities gsa
    WHERE
          gbs.batchstep_id = v_step_id
      AND gbs.batchstep_id = gsa.batchstep_id
      AND gsa.batchstep_activity_id = v_activity_id;
Line: 345

  v_last_update_date DATE;
Line: 350

    SELECT
      gsr.batchstep_resource_id,
      DECODE(gsr.plan_rsrc_usage, 0, 0, inv_convert.inv_um_convert(-1,38,
        gsr.plan_rsrc_usage,u2.uom_code,u1.uom_code,NULL,NULL)) plan_rsrc_usage,
      gsr.offset_interval,
      gsr.plan_start_date,
      gsr.plan_cmplt_date
    FROM
      gme_batch_step_resources gsr,
      mtl_units_of_measure u1,
      mtl_units_of_measure u2
    WHERE
          gsr.batchstep_activity_id = v_activity_id
      AND u1.uom_code = gsr.usage_um
      AND u2.uom_code = v_hour_uom
      AND (gsr.plan_rsrc_usage = 0 OR
           u1.uom_class <> u2.uom_class) ;
Line: 390

  FETCH validate_activity INTO v_last_update_date, v_step_status;
Line: 397

    last_update_date := wip_datetimes.float_to_DT(plast_update/1440+phorizon+1);
Line: 398

    IF v_last_update_date > last_update_date THEN
      return_status := -3;
Line: 404

      UPDATE
        gme_batch_step_activities
      SET
        plan_start_date  = v_start_date,
        plan_cmplt_date  = v_end_date,
        last_update_date = SYSDATE,
        last_updated_by  = puser_id
      WHERE
        batchstep_activity_id = v_activity_id;
Line: 433

            UPDATE
              gme_batch_step_resources
            SET
              plan_start_date = v_trn_start_date,
              plan_cmplt_date = v_trn_end_date,
              last_update_date = SYSDATE,
              last_updated_by = puser_id
            WHERE
              batchstep_resource_id = v_zero_res_id;
Line: 452

            UPDATE
              gme_batch_step_resources
            SET
              plan_start_date = v_trn_start_date,
              plan_cmplt_date = v_trn_end_date,
              last_update_date = SYSDATE,
              last_updated_by = puser_id
            WHERE
              batchstep_resource_id = v_zero_res_id;
Line: 470

            UPDATE
              gme_resource_txns
            SET
              start_date = v_trn_start_date,
              end_date = v_trn_end_date,
              last_update_date = SYSDATE,
              last_updated_by = puser_id
            WHERE
                  doc_id = v_batch_id
              AND doc_type = 'PROD'
              AND line_id = v_zero_res_id
              AND completed_ind = 0
              AND delete_mark = 0;
Line: 502

      log_message('Failure occured during Batch Step Activities Update: '
        || pbatch_id);
Line: 506

END update_batch_activities;
Line: 511

*     update_batch_resources
*
*   DESCRIPTION
*     This procedure will update the batch step resource plan start and end date
*     once the WPS scheduling engine has completed.
*   HISTORY
*     M Craig
*     Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE update_batch_resources(
  pbatch_id      IN  NUMBER,
  pstep_id       IN  NUMBER_TBL_TYPE,
  pact_res_id    IN  NUMBER_TBL_TYPE,
  pres_usage     IN  NUMBER_TBL_TYPE,
  presource_id   IN  NUMBER_TBL_TYPE,
  psetup_id      IN  NUMBER_TBL_TYPE,
  pstart_date    IN  NUMBER_TBL_TYPE,
  pend_date      IN  NUMBER_TBL_TYPE,
  plast_update   IN  NUMBER_TBL_TYPE,
  pseq_dep_usage IN  NUMBER_TBL_TYPE,
  phorizon       IN  NUMBER,
  puom_hour      IN  VARCHAR2,
  puser_id       IN  NUMBER,
  plogin_id      IN  NUMBER,
  pres_rows      IN  NUMBER,
  return_status  OUT NOCOPY NUMBER,
  pnew_act_res   IN OUT NOCOPY NUMBER_TBL_TYPE)

IS

  v_batch_id        NUMBER ;
Line: 548

  last_update_date  DATE;
Line: 556

    SELECT
      gsr.organization_id,
      gsr.last_update_date,
      gbs.step_status,
      gsr.resources,
      crd.resources,
      gsr.usage_um,
      gsr.batchstep_activity_id
    FROM
      gme_batch_steps gbs,
      gme_batch_step_resources gsr,
      cr_rsrc_dtl crd
    WHERE
          gbs.batchstep_id = v_step_id
      AND gbs.batchstep_id = gsr.batchstep_id
      AND gsr.batchstep_resource_id = v_act_resource_id
      AND crd.resource_id = v_resource_id
      AND crd.organization_id = gsr.organization_id ;
Line: 581

    SELECT
      gsr.batchstep_resource_id
    FROM
      gme_batch_step_resources gsr
    WHERE
          gsr.batchstep_activity_id = v_activity_id
      AND gsr.resources = v_resources
      AND gsr.organization_id = v_organization_id;
Line: 590

  v_last_update_date DATE;
Line: 630

    FETCH validate_step_resource INTO v_organization_id, v_last_update_date,
       v_step_status,v_o_resources, v_n_resources, v_uom_code, v_step_act_id;
Line: 638

      last_update_date := wip_datetimes.float_to_DT(plast_update(i)/1440+phorizon+1);
Line: 639

      IF v_last_update_date > last_update_date THEN
        return_status := -3;
Line: 651

          UPDATE
            gme_batch_step_resources
          SET
            plan_start_date = v_start_date,
            plan_cmplt_date = v_end_date,
            sequence_dependent_usage = v_seq_dep_usage,
            last_update_date = SYSDATE,
            last_updated_by = puser_id
          WHERE
            batchstep_resource_id = v_act_resource_id;
Line: 683

              DELETE
                gme_batch_step_resources
              WHERE
                batchstep_resource_id = v_act_resource_id;
Line: 700

                IF NOT GME_BATCH_STEP_RESOURCES_DBL.insert_row
                  (v_step_res_row, v_in_step_res_row) THEN
                  return_status := -7;
Line: 718

   /* delete all resource transactions for current step resource */
    DELETE
      gme_resource_txns
    WHERE
          doc_id = v_batch_id
      AND line_id = v_act_resource_id
      AND doc_type = v_doc_prod
      AND organization_id = v_organization_id
      AND completed_ind = 0;
Line: 738

      log_message('Failure occured during Batch Step Resources Update: '
        || pbatch_id);
Line: 742

END update_batch_resources;
Line: 747

*     update_operation_resources
*
*   DESCRIPTION
*     This procedure will update the batch step activities, resource and
*     resource transactions from the operation resource. Sequence
*     dependent usage and transactions are included
*   HISTORY
*     M Craig
*     Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE update_operation_resources(
  pbatch_id       IN  NUMBER,
  pactivity_id    IN  NUMBER,
  pact_start_date IN  NUMBER,
  pact_end_date   IN  NUMBER,
  pact_last_update IN  NUMBER,
  pstep_id        IN  NUMBER_TBL_TYPE,
  pact_res_id     IN  NUMBER_TBL_TYPE,
  presource_id    IN  NUMBER_TBL_TYPE,
  presource_usage IN  NUMBER_TBL_TYPE,
  psetup_id       IN  NUMBER_TBL_TYPE,
  pres_start_date IN  NUMBER_TBL_TYPE,
  pres_end_date   IN  NUMBER_TBL_TYPE,
  plast_update    IN  NUMBER_TBL_TYPE,
  pseq_dep_usage  IN  NUMBER_TBL_TYPE,
  ptrn_act_res_id IN  NUMBER_TBL_TYPE,
  ptrn_resource_id IN  NUMBER_TBL_TYPE,
  ptrn_rsrc_count IN  NUMBER_TBL_TYPE,
  ptrn_seq_dep    IN  NUMBER_TBL_TYPE,
  ptrn_start_date IN  NUMBER_TBL_TYPE,
  ptrn_end_date   IN  NUMBER_TBL_TYPE,
  ptrn_instance_id IN  NUMBER_TBL_TYPE,
  phorizon        IN  NUMBER,
  puom_hour       IN  VARCHAR2,
  puser_id        IN  NUMBER,
  plogin_id       IN  NUMBER,
  pres_rows       IN  NUMBER,
  ptrn_rows       IN  NUMBER,
  return_status   OUT NOCOPY NUMBER)

IS

  areturn_status NUMBER;
Line: 804

  update_batch_activities(
    pbatch_id,
    v_step_id,
    pactivity_id,
    pact_start_date,
    pact_end_date,
    pact_last_update,
    phorizon,
    puom_hour,
    puser_id,
    plogin_id,
    areturn_status);
Line: 821

    update_batch_resources(
      pbatch_id,
      pstep_id,
      pact_res_id,
      presource_usage,
      presource_id,
      psetup_id,
      pres_start_date,
      pres_end_date,
      plast_update,
      pseq_dep_usage,
      phorizon,
      puom_hour,
      puser_id,
      plogin_id,
      pres_rows,
      rreturn_status,
      new_act_res);
Line: 844

      update_resource_transactions(
        pbatch_id,
        ptrn_act_res_id,
        ptrn_resource_id,
        ptrn_instance_id,
        ptrn_rsrc_count,
        ptrn_seq_dep,
        ptrn_start_date,
        ptrn_end_date,
        phorizon,
        puom_hour,
        puser_id,
        plogin_id,
        pres_rows,
        ptrn_rows,
        treturn_status,
        pact_res_id,
        new_act_res);
Line: 874

      log_message('Failure occured during Operation Resource Update: '
        || pbatch_id);
Line: 878

END update_operation_resources;
Line: 883

*     update_resource_transactions
*
*   DESCRIPTION
*     This procedure will update batch resource instance transactions
*     once the WPS scheduling engine has completed.
*   HISTORY
*     M Craig
*     Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE update_resource_transactions(
  pbatch_id      IN  NUMBER,
  pact_res_id    IN  NUMBER_TBL_TYPE,
  presource_id   IN  NUMBER_TBL_TYPE,
  pinstance_id   IN  NUMBER_TBL_TYPE,
  prsrc_count    IN  NUMBER_TBL_TYPE,
  pseq_dep_ind   IN  NUMBER_TBL_TYPE,
  pstart_date    IN  NUMBER_TBL_TYPE,
  pend_date      IN  NUMBER_TBL_TYPE,
  phorizon       IN  NUMBER,
  puom_hour      IN  VARCHAR2,
  puser_id       IN  NUMBER,
  plogin_id      IN  NUMBER,
  pres_rows      IN  NUMBER,
  ptrn_rows      IN  NUMBER,
  return_status  OUT NOCOPY NUMBER,
  porig_act_res  IN  NUMBER_TBL_TYPE,
  pnew_act_res   IN  NUMBER_TBL_TYPE)

IS

  v_batch_id        NUMBER;
Line: 925

    SELECT
      crd.schedule_ind,
      crd.resources,
      gsr.usage_um,
      gbh.ORGANIZATION_ID
    FROM
      cr_rsrc_dtl crd,
      gme_batch_step_resources gsr,
      gme_batch_header gbh
    WHERE
          crd.resource_id = v_resource_id
      AND crd.delete_mark = 0
      AND gsr.batchstep_resource_id = v_act_resource_id
      AND gbh.batch_id = v_batch_id
      AND gbh.ORGANIZATION_ID = gsr.ORGANIZATION_ID
      AND crd.ORGANIZATION_ID = gsr.ORGANIZATION_ID;
Line: 999

      v_in_trans_row.last_update_date := SYSDATE;
Line: 1001

      v_in_trans_row.last_updated_by := puser_id;
Line: 1002

      v_in_trans_row.last_update_login := plogin_id;
Line: 1003

      v_in_trans_row.delete_mark := 0;
Line: 1017

        IF NOT gme_resource_txns_dbl.insert_row
                  (v_in_trans_row, v_trans_row) THEN
          return_status := -2;
Line: 1039

      log_message('Failure occured during Resource Transaction Insert: '
        || pbatch_id);
Line: 1043

END update_resource_transactions;
Line: 1051

*     This procedure will select for update all of the batch details
*     except for the transactions.
*   HISTORY
*     M Craig
*     Rajesh Patangya -- Modified for Release 12.0
************************************************************************/
PROCEDURE lock_batch_details(
  pbatch_id IN NUMBER,
  return_status OUT NOCOPY NUMBER)

IS

  l_batch_id NUMBER;
Line: 1067

  /* lock the batch header being updated */
  CURSOR lock_batch_header IS
    SELECT
      batch_id
    FROM
      gme_batch_header
    WHERE
      batch_id = v_batch_id
    FOR UPDATE NOWAIT;
Line: 1077

  /* lock all of the batch steps for update */
  CURSOR lock_batch_steps IS
    SELECT
      batch_id
    FROM
      gme_batch_steps
    WHERE
      batch_id = v_batch_id
    FOR UPDATE NOWAIT;
Line: 1087

  /* lock all of the batch step activities for update */
  CURSOR lock_batch_activities IS
    SELECT
      batch_id
    FROM
      gme_batch_step_activities
    WHERE
      batch_id = v_batch_id
    FOR UPDATE NOWAIT;
Line: 1097

  /* lock all of the batch step resources for update */
  CURSOR lock_batch_resources IS
    SELECT
      batch_id
    FROM
      gme_batch_step_resources
    WHERE
      batch_id = v_batch_id
    FOR UPDATE NOWAIT;
Line: 1446

         If material status controlled on hand is required, then select on hand
         quantity from here and select all the transactions to come up with the
         desired quantity e.g. if the onhand quantity  only for WIP issue
         enabled will not be returned from this tree */
      /* On hand = ATT + Reserved Quantity */
      /* The items shall be revision or serial or lot controlled */
      /* The items will be restricted to a particular subinventory or loactor */
      /* Subinventory and locator (stock locator) have master detail relationship */
      /* GME looks for most restrictive material status, i.e. if at any one level
       of material status is disallowed means it is disallowed, before allowing the
       item to be used in a batch */

-----      p_tree_mode             := 2 ;   -- in transaction Mode
Line: 1520

	SELECT sum(quantity)
	FROM gmp_nettable_onhands_v
	WHERE organization_id = p_organization_id
	AND inventory_item_id = p_inventory_item_id ;