APPS.CSTPPCAT SQL Statements

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

Line: 7

  I_LAST_UPDATED_BY      IN  NUMBER,
  I_COST_TYPE_ID         IN  NUMBER,
  I_ITEM_TYPE            IN  NUMBER,
  I_LOT_SIZE             IN  NUMBER,
  I_SHRINKAGE_RATE       IN  NUMBER,

  O_RETURN_CODE          OUT NOCOPY NUMBER,
  O_RETURN_ERR           OUT NOCOPY VARCHAR2
) IS
  row_count              NUMBER;

Line: 30

  select primary_cost_method, avg_rates_cost_type_id
  into p_cost_method, p_avg_rates_cost_type
  from mtl_parameters
  where organization_id = I_ORGANIZATION_ID;

Line: 44

  select count(*)
  into row_count
  from cst_item_cost_details
  where organization_id = I_ORGANIZATION_ID
  and inventory_item_id = I_INVENTORY_ITEM_ID
  and cost_type_id = p_cost_method
  and (level_type = 2
       OR
        cost_element_id <> 2)
   and ROWNUM < 2;   -- Added for 7237799

Line: 65

   select count(*)
   into row_count
   from cst_item_cost_details
   where organization_id = I_ORGANIZATION_ID
   and inventory_item_id = I_INVENTORY_ITEM_ID
   and cost_type_id = p_avg_rates_cost_type
   and NOT (level_type = 1
            AND (cost_element_id = 2
                OR (cost_element_id = 1
                    AND item_cost = 0)))
   and ROWNUM < 2;   -- Added for 7237799

Line: 84

   select count(*)
   into row_count
   from cst_quantity_layers
   where organization_id = I_ORGANIZATION_ID
   and inventory_item_id = I_INVENTORY_ITEM_ID
   and ROWNUM < 2;

Line: 101

  select count(*)
  into row_count
  from mtl_onhand_quantities
  where organization_id = I_ORGANIZATION_ID
  and inventory_item_id = I_INVENTORY_ITEM_ID
  and ROWNUM < 2;

Line: 116

  select count(*)
  into row_count
  from wip_transactions
  where organization_id = I_ORGANIZATION_ID
  and primary_item_id = I_INVENTORY_ITEM_ID;

Line: 126

  select count(*)
  into row_count
  from wip_move_transactions
  where organization_id = I_ORGANIZATION_ID
  and primary_item_id = I_INVENTORY_ITEM_ID;

Line: 140

  select count(*)
  into row_count
  from mtl_material_transactions
  where organization_id = I_ORGANIZATION_ID
  and inventory_item_id = I_INVENTORY_ITEM_ID
  and ROWNUM < 2;  -- Added for 7237799

Line: 153

 * delete all previously existing costs
 */
  delete
  from cst_item_cost_details
  where organization_id = I_ORGANIZATION_ID
  and inventory_item_id = I_INVENTORY_ITEM_ID
  and cost_type_id = p_cost_method;

Line: 162

 * Bug FP 5218221: The previously existing costs were not being deleted from the
 * Summary table - CST_ITEM_COSTS, causing discrepancy between CIC and CICD.
 */
UPDATE cst_item_costs
   SET pl_material = 0, pl_material_overhead = 0,
       pl_resource = 0, pl_outside_processing = 0,
       pl_overhead = 0, tl_material = 0,
       tl_material_overhead = 0, tl_resource = 0,
       tl_outside_processing = 0, tl_overhead = 0,
       material_cost = 0, material_overhead_cost = 0,
       resource_cost = 0, outside_processing_cost = 0,
       overhead_cost = 0, pl_item_cost = 0,
       tl_item_cost = 0, item_cost = 0,
       unburdened_cost = 0, burden_cost = 0,
       last_update_date = SYSDATE,
       last_updated_by = i_last_updated_by
 WHERE inventory_item_id = i_inventory_item_id
   AND organization_id = i_organization_id
   AND cost_type_id = p_cost_method;

Line: 189

           I_LAST_UPDATED_BY,
           I_COST_TYPE_ID,
           I_ITEM_TYPE,
           I_LOT_SIZE,
           I_SHRINKAGE_RATE,
           retval,
           retmsg);