APPS.CSTPLCIR SQL Statements

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

Line: 30

    select
      MCLACD.inv_layer_id          inv_layer_id,
      min( MCLACD.layer_quantity ) layer_quantity
    from
      mtl_cst_layer_act_cost_details MCLACD
    where
      MCLACD.transaction_id = i_txn_id
    group by
      MCLACD.inv_layer_id;

Line: 60

  select decode(inventory_asset_flag,'Y', 0, 1)
  into l_item_exp_flag
  from mtl_system_items
  where inventory_item_id = i_inv_item_id
  and organization_id     = i_org_id;

Line: 68

  select decode( l_item_exp_flag, 1, 1,
                 decode(asset_inventory, 1, 0, 1) )
  into l_exp_flag
  from mtl_secondary_inventories msi,
       mtl_material_transactions mmt
  where msi.secondary_inventory_name = mmt.subinventory_code
  and msi.organization_id = i_org_id
  and mmt.transaction_id  = i_txn_id
  and mmt.organization_id = i_org_id;

Line: 230

    update wip_req_operation_cost_details WROCD
    set    applied_matl_value
    =
    (
      select
        nvl(WROCD.applied_matl_value, 0) +  -- add nvl for bug13523172
          sum( CWL.applied_matl_qty * CWLCD.layer_cost )
      from
        cst_wip_layers CWL,
        cst_wip_layer_cost_details CWLCD
      where
        CWL.wip_layer_id      = l_wip_layer_id        and
        CWLCD.wip_layer_id    = CWL.wip_layer_id      and
        CWLCD.inv_layer_id    = CWL.inv_layer_id      and
        CWLCD.cost_element_id = WROCD.cost_element_id and
        CWLCD.level_type in (1, 2)
    )
    where
      WROCD.wip_entity_id     = i_wip_entity_id and
      WROCD.operation_seq_num = i_op_seq_num and
      WROCD.inventory_item_id = i_inv_item_id;

Line: 389

    update cst_wip_layers CWL
    set
      applied_matl_qty  = applied_matl_qty  - l_consumed_qty,
      temp_relieved_qty = temp_relieved_qty + l_consumed_qty
    where
      wip_layer_id = l_layer.wip_layer_id and
      inv_layer_id = l_layer.inv_layer_id;

Line: 430

  update wip_req_operation_cost_details WROCD
  set
  (
    WROCD.applied_matl_value,
    WROCD.temp_relieved_value
  )
  =
  (
    select
      NVL( WROCD.applied_matl_value, 0 ) -
        sum( CWL.temp_relieved_qty *
	     decode( l_zero_cost_flag, 1, 0, CWLCD.layer_cost ) ),
      sum( CWL.temp_relieved_qty *
	   decode( l_zero_cost_flag, 1, 0, CWLCD.layer_cost ) )
    from
      cst_wip_layers CWL,
      cst_wip_layer_cost_details CWLCD
    where
      CWL.wip_entity_id     =  WROCD.wip_entity_id     and
      CWL.operation_seq_num =  WROCD.operation_seq_num and
      CWL.inventory_item_id =  WROCD.inventory_item_id and
      CWL.temp_relieved_qty <> 0                     and
      CWLCD.wip_layer_id    =  CWL.wip_layer_id      and
      CWLCD.inv_layer_id    =  CWL.inv_layer_id      and
      CWLCD.cost_element_id =  WROCD.cost_element_id and
      CWLCD.level_type in (1, 2)
  )
  where
    WROCD.wip_entity_id     = i_wip_entity_id and
    WROCD.operation_seq_num = i_op_seq_num and
    WROCD.inventory_item_id = i_inv_item_id;

Line: 466

  INSERT INTO mtl_cst_txn_cost_details
  (
    TRANSACTION_ID,
    ORGANIZATION_ID,
    INVENTORY_ITEM_ID,
    COST_ELEMENT_ID,
    LEVEL_TYPE,
    TRANSACTION_COST,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    REQUEST_ID,
    PROGRAM_APPLICATION_ID,
    PROGRAM_ID,
    PROGRAM_UPDATE_DATE
  )
  SELECT
    i_txn_id,               -- TRANSACTION_ID,
    i_org_id,               -- ORGANIZATION_ID,
    i_inv_item_id,          -- INVENTORY_ITEM_ID,
    CWLCD.cost_element_id,  -- COST_ELEMENT_ID,
    CWLCD.level_type,       -- LEVEL_TYPE,
    sum( decode( l_zero_cost_flag, 1, 0, CWLCD.layer_cost ) *
	 CWL.temp_relieved_qty ) / i_txn_qty,
                            -- TRANSACTION_COST,
    sysdate,        -- LAST_UPDATE_DATE,
    i_user_id,      -- LAST_UPDATED_BY,
    sysdate,        -- CREATION_DATE,
    i_user_id,      -- CREATED_BY,
    i_login_id,     -- LAST_UPDATE_LOGIN,
    i_request_id,   -- REQUEST_ID,
    i_prog_appl_id, -- PROGRAM_APPLICATION_ID,
    i_prog_id,      -- PROGRAM_ID,
    sysdate         -- PROGRAM_UPDATE_DATE
  from
    cst_wip_layers CWL,
    cst_wip_layer_cost_details CWLCD
  where
    CWL.wip_entity_id     =  i_wip_entity_id  and
    CWL.operation_seq_num =  i_op_seq_num     and
    CWL.inventory_item_id =  i_inv_item_id    and
    CWL.temp_relieved_qty <> 0                and
    CWLCD.wip_layer_id    =  CWL.wip_layer_id and
    CWLCD.inv_layer_id    =  CWL.inv_layer_id and
    CWLCD.level_type in (1,2)
  group by
    CWLCD.cost_element_id,
    CWLCD.level_type;