APPS.CSTPLCWP SQL Statements

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

Line: 67

  select
    inventory_item_id,
    organization_id,
    transaction_date,
    transaction_action_id,
    transaction_source_type_id,
    primary_quantity,
    transaction_source_id,
    operation_seq_num,
    nvl(final_completion_flag,'N'),
    acct_period_id
  into
    l_inv_item_id,
    l_org_id,
    l_txn_date,
    l_action_id,
    l_src_type_id,
    l_txn_qty,
    l_wip_entity_id,
    l_op_seq_num,
    l_final_comp_flag,
    l_period_id
  from
    mtl_material_transactions
  where
    transaction_id = l_trx_id;

Line: 101

       UPDATE  cst_comp_snapshot cocd1
        SET
        (cocd1.prior_completion_quantity, cocd1.prior_scrap_quantity) =
       (SELECT
              NVL
                  (SUM(
                       DECODE(mmt.transaction_action_id,
                              30,0,
                              cocd2.primary_quantity)
                      ),
                   0),
               NVL(SUM(
                       DECODE(mmt.transaction_action_id,
                              31,0,
                              32,0,
                              cocd2.primary_quantity)
                      ),
                   0)
            FROM
               cst_comp_snapshot         cocd2,
               mtl_material_transactions mmt
         WHERE cocd2.transaction_id    = mmt.transaction_id
         AND cocd2.wip_entity_id     = cocd1.wip_entity_id
         AND cocd2.operation_seq_num = cocd1.operation_seq_num
         AND mmt.transaction_action_id in (30,31,32)
         AND mmt.organization_id = l_org_id
         AND mmt.transaction_source_id = l_wip_entity_id
         AND mmt.transaction_source_type_id = 5
         AND (mmt.transaction_date < l_txn_date
              OR (mmt.transaction_date = l_txn_date
                  AND mmt.transaction_id < l_trx_id)
             )
         )
         WHERE cocd1.transaction_id = l_trx_id
           AND cocd1.wip_entity_id = l_wip_entity_id;

Line: 441

    update cst_wip_layers CWL
    set
      CWL.relieved_matl_comp_qty =
      ( CWL.applied_matl_qty -
        CWL.relieved_matl_scrap_qty -
        CWL.relieved_matl_final_comp_qty ),
      CWL.temp_relieved_qty =
      ( CWL.applied_matl_qty -
        CWL.relieved_matl_comp_qty -
        CWL.relieved_matl_scrap_qty -
        CWL.relieved_matl_final_comp_qty )
    where
      CWL.wip_entity_id = l_wip_entity_id and
      ( CWL.applied_matl_qty -
        CWL.relieved_matl_comp_qty -
        CWL.relieved_matl_scrap_qty -
        CWL.relieved_matl_final_comp_qty ) >= 0;

Line: 460

    update cst_wip_layers CWL
    set
      CWL.relieved_matl_final_comp_qty =
      ( CWL.applied_matl_qty -
        CWL.relieved_matl_comp_qty -
        CWL.relieved_matl_scrap_qty ),
      CWL.temp_relieved_qty =
      ( CWL.applied_matl_qty -
        CWL.relieved_matl_comp_qty -
        CWL.relieved_matl_scrap_qty -
        CWL.relieved_matl_final_comp_qty )
    where
      CWL.wip_entity_id = l_wip_entity_id and
      ( CWL.applied_matl_qty -
        CWL.relieved_matl_comp_qty -
        CWL.relieved_matl_scrap_qty -
        CWL.relieved_matl_final_comp_qty ) < 0;

Line: 495

    | therefore deliberately refrain from inserting a
    | cost row. For completions, assembly returns and
    | scrap transactions however, in the respective
    | packages, we do not insert  row if the cost is zero.
    | To prevent such transactions from being processed at
    | current average cost, we need to insert a dummy
    | TL materil row into the cost table with zero cost.
    |------------------------------------------------------*/



    stmt_num := 140;

Line: 508

    select count(*)
    into   l_mtl_txn_exists
    from   mtl_cst_txn_cost_details
    where  transaction_id = l_trx_id;

Line: 519

      INSERT INTO mtl_cst_txn_cost_details
      (
        TRANSACTION_ID,
        ORGANIZATION_ID,
        INVENTORY_ITEM_ID,
        COST_ELEMENT_ID,
        LEVEL_TYPE,
        TRANSACTION_COST,
        NEW_AVERAGE_COST,
        PERCENTAGE_CHANGE,
        VALUE_CHANGE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        REQUEST_ID,
        PROGRAM_APPLICATION_ID,
        PROGRAM_ID,
        PROGRAM_UPDATE_DATE
      )
      VALUES
      (
        l_trx_id,
        l_org_id,
        l_inv_item_id,
        1,
        1,
        0,
        NULL,
        NULL,
        NULL,
        SYSDATE,
        l_user_id,
        SYSDATE,
        l_user_id,
        l_login_id,
        l_request_id,
        l_prog_app_id,
        l_prog_id,
        SYSDATE
      );

Line: 674

  | to update wip_period_balances.
  |----------------------------------------------------------*/

  stmt_num := 160;

Line: 679

  select count(*)
  into   l_row_count
  from   mtl_cst_actual_cost_details
  where  transaction_id = l_trx_id;

Line: 714

      /* Check if the job is EAM. If yes, then update material asset cost */
      if (l_err_num = 0) then
        select entity_type
        into l_entity_type
        from wip_entities
        where wip_entity_id = l_wip_entity_id;