APPS.MSC_BUDGET_DETAILS SQL Statements

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

Line: 17

select max(detail_date)
from msc_bis_inv_detail
where detail_date <= p_date
and   plan_id = p_plan_id
and   nvl(period_type, -1)  = 1
and   organization_id = p_organization_id
and   sr_instance_id  = p_sr_instance_id
and   nvl(detail_level,3) = nvl(p_bucket_type, 3);

Line: 27

select bkt_start_date, bkt_end_date, decode(bucket_type,2,1,3,3)
from msc_plan_buckets
where plan_id = p_plan_id
and   p_date between bkt_start_date and bkt_end_date;

Line: 35

select bid.inventory_item_id,
       bid.inventory_value,
       bid.inventory_value/l_tot_inv
from  msc_bis_inv_detail bid ,
      msc_system_items   item
where nvl(bid.period_type,-1)=1
and   bid.detail_date = l_date
and   bid.plan_id = p_plan_id
and   nvl(bid.detail_level,3) = nvl(l_bucket_type,3)
and   bid.plan_id = item.plan_id
and   bid.sr_instance_id = item.sr_instance_id
and   bid.organization_id = item.organization_id
and   bid.inventory_item_id = item.inventory_item_id
and   item.budget_constrained =1;

Line: 55

select bid.inventory_item_id,
       bid.inventory_value,
       bid.inventory_value/l_tot_inv
from  msc_bis_inv_detail bid,
      msc_system_items item
where nvl(bid.period_type,-1)=1
and   bid.detail_date = l_date
and   bid.plan_id = p_plan_id
and   bid.organization_id=l_organization_id
and   bid.sr_instance_id =l_sr_instance_id
and   nvl(bid.detail_level,3) = nvl(l_bucket_type,3)
and   bid.plan_id = item.plan_id
and   bid.sr_instance_id = item.sr_instance_id
and   bid.organization_id = item.organization_id
and   bid.inventory_item_id = item.inventory_item_id
and   item.budget_constrained =1;

Line: 77

select bid.inventory_item_id,
       bid.inventory_value,
       bid.inventory_value/l_tot_inv
from  msc_bis_inv_detail bid,
      msc_item_categories mic,
      msc_system_items item
where nvl(bid.period_type,-1)=1
and   bid.detail_date = l_date
and   bid.plan_id = p_plan_id
and   nvl(bid.detail_level,3) = nvl(l_bucket_type,3)
and   mic.inventory_item_id= bid.inventory_item_id
and   mic.sr_instance_id = bid.sr_instance_id
and   mic.organization_id= bid.organization_id
and   mic.category_name = l_cat_name
and   bid.plan_id = item.plan_id
and   bid.sr_instance_id = item.sr_instance_id
and   bid.organization_id = item.organization_id
and   bid.inventory_item_id = item.inventory_item_id
and   item.budget_constrained =1;

Line: 102

select max(period_start_date)
from msc_safety_stocks
where period_start_date <= l_detail_date1
and   inventory_item_id  = l_inventory_item_id1
and   organization_id    = l_organization_id1
and   sr_instance_id     = l_sr_instance_id1
and   plan_id            = l_plan_id1;

Line: 116

select target_safety_stock, safety_stock_quantity
from msc_safety_stocks
where inventory_item_id = l_inventory_item_id
and   organization_id= l_organization_id
and   sr_instance_id = l_sr_instance_id
and   plan_id = l_plan_id
and   period_start_date = l_period_start_date;

Line: 148

delete msc_form_query;

Line: 236

      insert into msc_form_query
      (query_id,
       number1,  --item
       number2,  --budget value
       number3,  --inv value
       number4,  --%total value
       number5,  --target sl
       number6,  --achieved sl
       number7,  --target ss
       number8,  --achieved ss
       number9,  -- org
       number10,  -- inst
       date1 ,   --detail_date
       char1,    --item_name
       char2,    -- bucket type
       last_update_date,
       last_updated_by,
       creation_date,
       created_by)
       values
       (p_query_id,
        l_inv_item,
        p_budget_value,
        round(l_inv_value,0),
        round(l_percent_inv_value * 100,2),
        l_target_sl,
        l_achieved_sl,
        l_target_ss,
        l_achieved_ss,
        p_organization_id,
        p_sr_instance_id,
        l_detail_date,
        l_item_name,
        decode(l_bucket_type,1,'Week','Period'),
        sysdate,
        -1,
        sysdate,
        -1);