[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into msc_cp_plan_buckets(
plan_id,
organization_id,
sr_instance_id,
bucket_index,
curr_flag,
bkt_start_date,
bkt_end_date,
days_in_bkt,
bucket_type,
supplier_id,
supplier_site_id,
customer_id,
customer_site_id,
inventory_item_id,
plan_type,
last_update_date,
last_updated_by,
creation_date,
created_by) values
(p_plan_id,
p_org_id,
p_sr_instance_id,
p_bkt_index,
p_curr_flag,
to_date(p_start_date, 'J'),
to_date(p_end_date, 'J'),
p_days_in_bkt,
p_bkt_type,
p_supplier_id,
p_supplier_site_id,
p_customer_id,
p_customer_site_id,
p_inventory_item_id ,
p_plan_type ,
sysdate,
-1,
sysdate,
-1);
insert into msc_plan_buckets(
plan_id,
organization_id,
sr_instance_id,
bucket_index,
curr_flag,
bkt_start_date,
bkt_end_date,
days_in_bkt,
bucket_type,
last_update_date,
last_updated_by,
creation_date,
created_by) values
(p_plan_id,
p_org_id,
p_sr_instance_id,
p_bkt_index,
p_curr_flag,
to_date(p_start_date, 'J'),
to_date(p_end_date, 'J'),
p_days_in_bkt,
p_bkt_type,
sysdate,
-1,
sysdate,
-1);
insert into msc_plan_buckets(
plan_id,
organization_id,
sr_instance_id,
bucket_index,
curr_flag,
bkt_start_date,
bkt_end_date,
days_in_bkt,
bucket_type,
last_update_date,
last_updated_by,
creation_date,
created_by) values
(p_plan_id,
p_org_id,
p_sr_instance_id,
p_bkt_index,
p_curr_flag,
to_date(p_start_date, 'J'),
to_date(p_end_date, 'J'),
p_days_in_bkt,
p_bkt_type,
sysdate,
-1,
sysdate,
-1);
select to_number(to_char(to_date(p_date1, 'j'), 'MM'))
INTO
month1 from dual;
select to_number(to_char(to_date(p_date2, 'j'), 'MM'))
INTO
month2 from dual;
select to_char(to_date(p_date, 'j'), 'D')
into
day_of_week from dual;
select
to_number(to_char(last_day(to_date(the_date, 'J')), 'J'))
into
last_day_of_mth
from dual;
| Starting with the first date keep inserting buckets |
| into msc_plan_buckets until the daily buckets are over |
+--------------------------------------------------------*/
if(p_no_of_days = 0)
then
return;
| Delete the old buckets for the plan. |
+-------------------------------------------------------*/
--delete msc_plan_buckets
--where plan_id = p_plan_id;
select sysdate
into first_date
from dual;
select to_number(to_char(first_date, 'J'))
into jul_first_date
from dual;
select nvl(calendar_code, 1)
into p_cal_code
from msc_plan_organizations
where plan_id = p_plan_id
and rownum = 1;
select distinct to_number(to_char(PERIOD_START_DATE,'j')) PERIOD_START_DATE,
to_number(to_char(NEXT_DATE-1,'j')) period_end_date
from msc_period_start_dates
where CALENDAR_CODE = p_calendar_code
and SR_INSTANCE_ID = p_sr_instance_id
and EXCEPTION_SET_ID = -1
and ( ( PERIOD_START_DATE <= to_date(p_start_date,'j')
and NEXT_DATE-1 >= to_date(p_start_date,'j') )
or ( PERIOD_START_DATE >= to_date(p_start_date,'j')
and NEXT_DATE-1 <= to_date(p_cutoff_date,'j') )
or ( PERIOD_START_DATE <= to_date(p_cutoff_date,'j')
and NEXT_DATE-1 >= to_date(p_cutoff_date,'j') )
);
select distinct to_number(to_char(WEEK_START_DATE,'j')) week_start_date,
to_number(to_char(NEXT_DATE-1,'j')) week_end_date
from msc_cal_week_start_dates
where CALENDAR_CODE = p_calendar_code
and SR_INSTANCE_ID = p_sr_instance_id
and EXCEPTION_SET_ID = -1
and ( (WEEK_START_DATE <= to_date(p_start_date,'j')
and NEXT_DATE-1 >= to_date(p_start_date,'j'))
or (WEEK_START_DATE >= to_date(p_start_date,'j')
and NEXT_DATE-1 <= to_date(p_cutoff_date,'j') )
or (WEEK_START_DATE <= to_date(p_cutoff_date,'j')
and NEXT_DATE-1 >= to_date(p_cutoff_date,'j') )
);
select to_number(to_char(sysdate,'j') )
into l_start_date
from dual;
select nvl(max(bucket_type), NONE)
into l_cust_bkt_type
from
msc_sup_dem_entries sd
where plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id =p_customer_id
and sd.publisher_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = DECODE(p_plan_type,
SUPPLY_PLANNING, 2,
DEMAND_PLANNING, 1)
and sd.supplier_id = p_supplier_id
and sd.supplier_site_id = p_supplier_site_id
and to_number(to_char(sd.key_date, 'j')) = l_curr_date;
select nvl(max(bucket_type), NONE)
into l_supp_bkt_type
from
msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type in (3,14)
and to_number(to_char(sd.key_date, 'j')) = l_curr_date;
select nvl(max(bucket_type), NONE)
into l_supp_bkt_type
from
msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = 1
and to_number(to_char(sd.key_date, 'j')) = l_curr_date;
select to_number(to_char(last_day(to_date(l_curr_date, 'j')), 'j'))
into
l_bkt_end_date
from dual;
select 1
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type in (G_SUPPLY_COMMIT,G_SALES_ORDER)
and nvl(bucket_type,0) = p_bucket_type
and to_number(to_char(sd.key_date, 'j')) between p_start_date and p_end_date
and rownum = 1
UNION
select 1
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_customer_id
and sd.publisher_site_id = p_customer_site_id
and sd.supplier_id = p_supplier_id
and sd.supplier_site_id = p_supplier_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = G_ORDER_FORECAST
and nvl(bucket_type,0) = p_bucket_type
and to_number(to_char(sd.key_date, 'j')) between p_start_date and p_end_date
and rownum = 1;
select 1
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = G_SALES_FORECAST
and nvl(bucket_type,0) = p_bucket_type
and to_number(to_char(sd.key_date, 'j')) between p_start_date and p_end_date
and rownum = 1
UNION
select 1
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_customer_id
and sd.publisher_site_id = p_customer_site_id
and sd.supplier_id = p_supplier_id
and sd.supplier_site_id = p_supplier_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = G_SALES_FORECAST
and nvl(bucket_type,0) = p_bucket_type
and to_number(to_char(sd.key_date, 'j')) between p_start_date and p_end_date
and rownum = 1;
select to_number(to_char(sd.key_date, 'j')) key_date
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
and sd.publisher_order_type in (G_SUPPLY_COMMIT,G_SALES_ORDER)
and nvl(bucket_type,0) = DAY
and to_number(to_char(sd.key_date, 'j'))
between p_m_start_date and p_m_end_date
and to_number(to_char(sd.key_date, 'j')) <= pCutoff_date
and p_plan_type = SUPPLY_PLANNING
UNION
select to_number(to_char(sd.key_date, 'j')) key_date
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_customer_id
and sd.publisher_site_id = p_customer_site_id
and sd.supplier_id = p_supplier_id
and sd.supplier_site_id = p_supplier_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = G_ORDER_FORECAST
and nvl(bucket_type,0) = DAY
and to_number(to_char(sd.key_date, 'j'))
between p_m_start_date and p_m_end_date
and to_number(to_char(sd.key_date, 'j')) <= pCutoff_date
and p_plan_type = SUPPLY_PLANNING
UNION
select to_number(to_char(sd.key_date, 'j')) key_date
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = G_SALES_FORECAST
and nvl(bucket_type,0) = DAY
and to_number(to_char(sd.key_date, 'j'))
between p_m_start_date and p_m_end_date
and to_number(to_char(sd.key_date, 'j')) <= pCutoff_date
and p_plan_type = DEMAND_PLANNING
UNION
select to_number(to_char(sd.key_date, 'j')) key_date
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_customer_id
and sd.publisher_site_id = p_customer_site_id
and sd.supplier_id = p_supplier_id
and sd.supplier_site_id = p_supplier_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = G_SALES_FORECAST
and nvl(bucket_type,0) = DAY
and to_number(to_char(sd.key_date, 'j'))
between p_m_start_date and p_m_end_date
and to_number(to_char(sd.key_date, 'j')) <= pCutoff_date
and p_plan_type = DEMAND_PLANNING
;
select to_number(to_char(PERIOD_START_DATE,'J') ),
to_number(to_char(NEXT_DATE-1,'J') )
into lv_m_start_date ,
lv_m_end_date
from MSC_PERIOD_START_DATES
where SR_INSTANCE_ID = p_instance_id
and CALENDAR_CODE = p_calendar_code
and EXCEPTION_SET_ID = -1
and l_curr_date between to_number(to_char(PERIOD_START_DATE,'J'))
and to_number(to_char(NEXT_DATE-1,'J'));
select to_number(to_char(WEEK_START_DATE,'J') ) ,
to_number(to_char(NEXT_DATE-1,'J') )
into lv_w_start_date,
lv_w_end_date
from MSC_CAL_WEEK_START_DATES
where SR_INSTANCE_ID = p_instance_id
and CALENDAR_CODE = p_calendar_code
and EXCEPTION_SET_ID = -1
and l_curr_date between to_number(to_char(WEEK_START_DATE,'J'))
and to_number(to_char(NEXT_DATE-1,'J'));
select c1.company_name, c2.company_site_name
into l_supplier_name, l_supplier_site_name
from msc_companies c1, msc_company_sites c2
where c1.company_id = p_supplier_id
and c1.company_id = c2.company_id
and c2.company_site_id = p_supplier_site_id;
select c1.company_name, c2.company_site_name
into l_customer_name, l_customer_site_name
from msc_companies c1, msc_company_sites c2
where c1.company_id = p_customer_id
and c1.company_id = c2.company_id
and c2.company_site_id = p_customer_site_id;
| Delete the previous set of data from msc_plan_buckets |
+-------------------------------------------------------*/
--delete msc_plan_buckets
--where plan_id = -1;
select nvl(max(sd.last_refresh_number), -1), max(sd.key_date),min(sd.key_date)
into l_max_ref_cust, l_max_receipt_cust, l_min_receipt_cust
from
msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id =p_customer_id
and sd.publisher_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = DECODE(p_plan_type,
SUPPLY_PLANNING, 2,
DEMAND_PLANNING, 1)
and sd.supplier_id = p_supplier_id
and sd.supplier_site_id = p_supplier_site_id;
select nvl(max(sd.last_refresh_number), -1),
max(sd.key_date),min(sd.key_date)
into l_max_ref_supp, l_max_receipt_supp,l_min_receipt_supp
from
msc_sup_dem_entries sd
where
sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
and sd.publisher_order_type in (3,14);
delete msc_plan_buckets
where plan_id = -1;
select nvl(max(sd.last_refresh_number), -1),
max(sd.key_date),min(sd.key_date)
into l_max_ref_supp, l_max_receipt_supp,l_min_receipt_supp
from
msc_sup_dem_entries sd
where
sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = 1;
select distinct bucket_type
into l_cust_bucket_type
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id =p_customer_id
and sd.publisher_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = DECODE(p_plan_type,
SUPPLY_PLANNING, 2,
DEMAND_PLANNING, 1)
and sd.supplier_id = p_supplier_id
and sd.supplier_site_id = p_supplier_site_id;
| the select here depends on the plan |
+---------------------------------------*/
IF (p_plan_type = SUPPLY_PLANNING) THEN
BEGIN
select distinct bucket_type into l_supp_bucket_type
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type in (3,14);
select distinct bucket_type into l_supp_bucket_type
from msc_sup_dem_entries sd
where sd.plan_id = -1
and sd.sr_instance_id = p_sr_instance_id
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id, sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = 1;
SELECT distinct sd.customer_id,
sd.customer_site_id,
sd.publisher_id supplier_id,
sd.publisher_site_id supplier_site_id,
nvl(sd.base_item_id,sd.inventory_item_id) item_id
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd.publisher_order_type in (msc_x_netting_pkg.SUPPLY_COMMIT, msc_x_netting_pkg.SALES_ORDER)
AND sd.last_refresh_number > p_refresh_number
union
SELECT distinct sd.publisher_id customer_id,
sd.publisher_site_id customer_site_id,
sd.supplier_id,
sd.supplier_site_id,
nvl(sd.base_item_id,sd.inventory_item_id) item_id
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
AND sd.last_refresh_number> p_refresh_number;
select customer_id,customer_site_id,supplier_id,supplier_site_id,item_id,
nvl(max(last_refresh_number), -1),
nvl(max(key_date),sysdate),
nvl(min(key_date),sysdate)
from (
select
sd1.publisher_id customer_id,
sd1.publisher_site_id customer_site_id,
sd1.supplier_id,
sd1.supplier_site_id,
nvl(sd1.base_item_id,sd1.inventory_item_id) item_id,
sd1.last_refresh_number,
sd1.key_date
from msc_sup_dem_entries sd1
where sd1.publisher_order_type = 2
and sd1.plan_id= -1
and sd1.last_refresh_number > p_refresh_number
and exists (select 1
from msc_sup_dem_entries sd2
where sd2.plan_id = sd1.plan_id
and sd2.sr_instance_id = sd1.sr_instance_id
and sd2.publisher_order_type = 3
and sd2.customer_id = sd1.publisher_id
and sd2.customer_site_id = sd1.publisher_site_id
and sd2.publisher_id = sd1.supplier_id
and sd2.publisher_site_id = sd1.supplier_site_id
and nvl(sd2.base_item_id,sd2.inventory_item_id) = nvl(sd1.base_item_id,sd1.inventory_item_id)
and sd2.last_refresh_number > p_refresh_number
)
union all
select
-- distinct
sd1.publisher_id customer_id,
sd1.publisher_site_id customer_site_id,
sd1.supplier_id,
sd1.supplier_site_id,
nvl(sd1.base_item_id,sd1.inventory_item_id) item_id,
sd1.last_refresh_number,
sd1.key_date
from msc_sup_dem_entries sd1
where sd1.publisher_order_type = 2
and sd1.plan_id= -1
and sd1.last_refresh_number > p_refresh_number
and exists (select 1
from msc_sup_dem_entries sd2
where sd2.plan_id = sd1.plan_id
and sd2.sr_instance_id = sd1.sr_instance_id
and sd2.publisher_order_type = 14
and sd2.customer_id = sd1.publisher_id
and sd2.customer_site_id = sd1.publisher_site_id
and sd2.publisher_id = sd1.supplier_id
and sd2.publisher_site_id = sd1.supplier_site_id
and nvl(sd2.base_item_id,sd2.inventory_item_id) = nvl(sd1.base_item_id,sd1.inventory_item_id)
and sd2.last_refresh_number > p_refresh_number
)
) x
group by x.customer_id,x.customer_site_id,x.supplier_id
,x.supplier_site_id ,x.item_id
order by 1,2,3,4,5;
select customer_id,customer_site_id,supplier_id,supplier_site_id,item_id,
nvl(max(last_refresh_number), -1),
nvl(max(key_date),sysdate),
nvl(min(key_date),sysdate)
from (
select
sd1.customer_id,
sd1.customer_site_id,
sd1.publisher_id supplier_id,
sd1.publisher_site_id supplier_site_id,
nvl(sd1.base_item_id,sd1.inventory_item_id) item_id,
sd1.last_refresh_number,
sd1.key_date
from msc_sup_dem_entries sd1
where sd1.publisher_order_type = 3
and sd1.plan_id= -1
and sd1.last_refresh_number > p_refresh_number
and exists (select 1
from msc_sup_dem_entries sd2
where sd2.plan_id = sd1.plan_id
and sd2.sr_instance_id = sd1.sr_instance_id
and sd2.publisher_order_type = 2
and sd2.publisher_id = sd1.customer_id
and sd2.publisher_site_id = sd1.customer_site_id
and sd2.supplier_id = sd1.publisher_id
and sd2.supplier_site_id = sd1.publisher_site_id
and nvl(sd2.base_item_id,sd2.inventory_item_id) = nvl(sd1.base_item_id,sd1.inventory_item_id)
and sd2.last_refresh_number > p_refresh_number
)
union all
select
sd1.customer_id,
sd1.customer_site_id,
sd1.publisher_id supplier_id,
sd1.publisher_site_id supplier_site_id,
nvl(sd1.base_item_id,sd1.inventory_item_id) item_id,
sd1.last_refresh_number,
sd1.key_date
from msc_sup_dem_entries sd1
where sd1.publisher_order_type = 14
and sd1.plan_id= -1
and sd1.last_refresh_number > p_refresh_number
and exists (select 1
from msc_sup_dem_entries sd2
where sd2.plan_id = sd1.plan_id
and sd2.sr_instance_id = sd1.sr_instance_id
and sd2.publisher_order_type = 2
and sd2.publisher_id = sd1.customer_id
and sd2.publisher_site_id = sd1.customer_site_id
and sd2.supplier_id = sd1.publisher_id
and sd2.supplier_site_id = sd1.publisher_site_id
and nvl(sd2.base_item_id,sd2.inventory_item_id) = nvl(sd1.base_item_id,sd1.inventory_item_id)
and sd2.last_refresh_number > p_refresh_number
)
) x
group by x.customer_id,x.customer_site_id,x.supplier_id
,x.supplier_site_id ,x.item_id
order by 1,2,3,4,5;
lv_sql_stmt := 'delete msc_cp_plan_buckets';
select nvl(max(last_refresh_number),0)
into lv_cutoff_ref_num
from msc_sup_dem_entries;