[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_into_table(
p_plan_id NUMBER,
p_sr_instance_id NUMBER,
p_org_id NUMBER,
p_bucket_index NUMBER,
p_msc_plan_buckets IN OUT NOCOPY msc_plan_buckets_typ,
p_err_mesg OUT NOCOPY VARCHAR2
);
UPDATE msc_snapshot_tasks
SET completion_date = SYSDATE,
program_update_date = SYSDATE
WHERE task = arg_task
AND plan_id = arg_plan_id;
SELECT
Nvl(min_cutoff_bucket,0),
Nvl(hour_cutoff_bucket,0),
Nvl(daily_cutoff_bucket,0),
-- +Nvl(min_cutoff_bucket,0) bug 1226108
-- +Nvl(hour_cutoff_bucket,0), bug 1226108
Nvl(weekly_cutoff_bucket,0)*7,
Nvl(period_cutoff_bucket,0)
INTO l_min_cutoff_bucket, l_hour_cutoff_bucket,
l_daily_cutoff_bucket, l_weekly_cutoff_bucket, l_period_cutoff_bucket
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT /*+ NOREWRITE */
mpl.organization_id,
mpl.sr_instance_id,
TRUNC(sysdate),
mpl.cutoff_date
FROM
msc_trading_partners mtp,
msc_plans mpl
WHERE
mpl.plan_id = p_plan_id
and mpl.organization_id = mtp.sr_tp_id
and mtp.partner_type = 3
and mpl.sr_instance_id = mtp.sr_instance_id;
DELETE FROM msc_plan_buckets
WHERE plan_id = p_plan_id;
SELECT decode(plan_type, 4, trunc(curr_start_date), 9,
trunc(curr_start_date), trunc(sysdate)),
weekly_cutoff_bucket
into l_curr_start_date, l_weekly_buckets
FROM msc_plans
WHERE plan_id = p_plan_id;
select min(cal.week_start_date)
into l_curr_start_date
from msc_cal_week_start_dates cal,
msc_trading_partners tp,
msc_calendar_dates mc
where cal.exception_set_id = tp.calendar_exception_set_id
and mc.exception_set_id = tp.calendar_exception_set_id
and cal.calendar_code = tp.calendar_code
and mc.calendar_code = tp.calendar_code
and cal.sr_instance_id = tp.sr_instance_id
and mc.sr_instance_id = tp.sr_instance_id
and cal.week_start_date >= mc.next_date
and mc.calendar_date = trunc(sysdate)
and tp.sr_tp_id = l_org_id
and tp.sr_instance_id = l_sr_instance_id
and tp.partner_type = 3 ;
select min(cal.week_start_date)
into l_curr_start_date
from msc_cal_week_start_dates cal,
msc_calendar_dates mc
where cal.exception_set_id = mc.exception_set_id
and cal.calendar_code = mc.calendar_code
and cal.sr_instance_id = mc.sr_instance_id
and cal.week_start_date >= mc.next_date
and mc.calendar_date = trunc(sysdate)
and mc.calendar_code = lv_bkt_ref_calendar;
select min(cal.period_start_date)
into l_curr_start_date
from msc_period_start_dates cal,
msc_trading_partners tp
where cal.exception_set_id = tp.calendar_exception_set_id
and cal.calendar_code = tp.calendar_code
and cal.period_start_date >= trunc(sysdate)
and cal.sr_instance_id = tp.sr_instance_id
and tp.sr_tp_id = l_org_id
and tp.sr_instance_id = l_sr_instance_id
and tp.partner_type = 3;
select min(cal.period_start_date)
into l_curr_start_date
from msc_period_start_dates cal
where cal.period_start_date >= trunc(sysdate)
and cal.calendar_code = lv_bkt_ref_calendar;
select sr_instance_id
into lv_bkt_ref_instance
from msc_calendar_dates
where calendar_code = lv_bkt_ref_calendar
and calendar_date = trunc(sysdate)
and exception_set_id = -1;
UPDATE msc_plans
SET curr_cutoff_date = l_plan_cutoff_date,
curr_start_date = l_curr_start_date
WHERE plan_id = p_plan_id;
SELECT Nvl(min_cutoff_bucket,0), Nvl(hour_cutoff_bucket,0)+Nvl(min_cutoff_bucket,0)
INTO p_min_cutoff_bucket, p_hour_cutoff_bucket
FROM msc_plans
WHERE plan_id = p_plan_id;
select nvl(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), tp.calendar_code),
tp.calendar_exception_set_id ,
decode(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), NULL, tp.sr_instance_id, mcd.sr_instance_id)
into m_calendar_code , m_cal_exception_set_id , m_sr_instance_id
from msc_plans mp,
msc_trading_partners tp,
msc_calendar_dates mcd
where mp.plan_id = p_plan_id
and tp.partner_type = 3
and tp.sr_instance_id = mp.sr_instance_id
and mp.organization_id = tp.sr_tp_id
and mcd.exception_set_id = tp.calendar_exception_set_id
and mcd.calendar_date = trunc(sysdate)
and mcd.calendar_code = nvl(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), tp.calendar_code);
SELECT
rownum
,To_char(cal.calendar_date, 'YYYY/MM/DD')
,To_char(cal.calendar_date,'YYYY/MM/DD')
,1 bucket_type
,1 days_in_bucket
BULK COLLECT INTO
l_msc_plan_buckets.bucket_index,
l_msc_plan_buckets.bkt_start_date,
l_msc_plan_buckets.bkt_end_date,
l_msc_plan_buckets.bucket_type,
l_msc_plan_buckets.days_in_bkt
FROM
MSC_CALENDAR_DATES cal
WHERE
cal.sr_instance_id = m_sr_instance_id
AND cal.calendar_code = m_calendar_code
AND cal.exception_set_id = m_cal_exception_set_id
and trunc(cal.calendar_date) <= trunc(l_daily_cutoff_date )
and trunc(cal.calendar_date) >= l_curr_start_date
ORDER BY cal.calendar_date;
insert_into_table(
p_plan_id,
l_sr_instance_id,
l_org_id,
l_bkt_index,
l_msc_plan_buckets,
p_err_mesg);
SELECT
l_bkt_index+rownum
,To_char(cal.week_start_date, 'YYYY/MM/DD')
,To_char(Least(
Greatest(cal.next_date - 1, cal.week_start_date),
-- for last week both are same
l_weekly_cutoff_date),'YYYY/MM/DD')
--min of this and weekly cutoff
,2 bucket_type
,trunc(Least(
Greatest(cal.next_date - 1, cal.week_start_date),
l_weekly_cutoff_date)) - trunc(cal.week_start_date) + 1 days_in_bucket
BULK COLLECT INTO
l_msc_plan_buckets.bucket_index,
l_msc_plan_buckets.bkt_start_date,
l_msc_plan_buckets.bkt_end_date,
l_msc_plan_buckets.bucket_type,
l_msc_plan_buckets.days_in_bkt
FROM
MSC_CAL_WEEK_START_DATES cal
WHERE cal.sr_instance_id = m_sr_instance_id
AND cal.calendar_code = m_calendar_code
AND cal.exception_set_id = m_cal_exception_set_id
and trunc(cal.week_start_date) <= trunc(l_weekly_cutoff_date)
and trunc(cal.week_start_date) >= trunc(Nvl(l_daily_cutoff_date,Sysdate-1))+1
ORDER BY cal.week_start_date ASC;
insert_into_table(
p_plan_id,
l_sr_instance_id,
l_org_id,
l_bkt_index,
l_msc_plan_buckets,
p_err_mesg);
SELECT
l_bkt_index+ROWNUM
,To_char(cal.period_start_date, 'YYYY/MM/DD') bkt_start_date
,To_char(Least(
Greatest(cal.next_date - 1,cal.period_start_date),
l_period_cutoff_date), 'YYYY/MM/DD') bkt_end_date
,3 bucket_type
,trunc(Least(
Greatest(cal.next_date - 1,cal.period_start_date),
l_period_cutoff_date)) - trunc(cal.period_start_date)
+ 1 days_in_bucket
-- days between needs a + 1
BULK COLLECT INTO
l_msc_plan_buckets.bucket_index,
l_msc_plan_buckets.bkt_start_date,
l_msc_plan_buckets.bkt_end_date,
l_msc_plan_buckets.bucket_type,
l_msc_plan_buckets.days_in_bkt
FROM
msc_period_start_dates cal
WHERE
cal.sr_instance_id = m_sr_instance_id
AND cal.calendar_code = m_calendar_code
AND cal.exception_set_id = m_cal_exception_set_id
and trunc(cal.period_start_date) <= trunc(l_period_cutoff_date )
and trunc(cal.period_start_date) >=
trunc(Nvl(l_weekly_cutoff_date, Nvl(l_daily_cutoff_date,Sysdate-1))) + 1
ORDER BY cal.period_start_date;
insert_into_table(
p_plan_id,
l_sr_instance_id,
l_org_id,
l_bkt_index,
l_msc_plan_buckets,
p_err_mesg);
select count(*) into lv_plan_so
from msc_plan_organizations_v
where plan_id = p_plan_id
and nvl(include_salesorder,SYS_NO) = SYS_YES;
select count(*)
into lv_global_forecast
from msc_plan_schedules_v
where plan_id = p_plan_id
and input_organization_id = GLOBAL_ORG;
select nvl(global_fcst_refresh_date, to_date('01-JAN-1900','dd-mon-yyyy'))
into last_gf_refresh_date
from msc_plans
where plan_id = p_plan_id;
select max(last_update_date) into l_latest_change_date
from msc_plan_organizations
where plan_id = p_plan_id;
select max(msa.last_update_date) into l_latest_change_date
from msc_sr_assignments msa,
msc_plans mp
where mp.plan_id = p_plan_id
and mp.FORECAST_ASSIGNMENT_SET_ID = msa.assignment_set_id;
select max(msr.last_update_date) into l_latest_change_date
from msc_sourcing_rules msr,
msc_sr_assignments msra,
msc_plans mp
where mp.plan_id = p_plan_id
and mp.FORECAST_ASSIGNMENT_SET_ID = msra.ASSIGNMENT_SET_ID
and msra.sourcing_rule_id = msr.sourcing_rule_id;
DELETE MSC_ITEM_SO_SR_LEVELS where plan_id = p_plan_id;
DELETE MSC_ITEM_FCST_SR_LEVELS where plan_id = p_plan_id;
' INSERT /*+ APPEND */ INTO MSC_ITEM_SO_SR_LEVELS'
||' (INVENTORY_ITEM_ID ,'
||' ORGANIZATION_ID ,'
||' SR_INSTANCE_ID ,'
||' PLAN_ID ,'
||' ASSIGNMENT_TYPE ,'
||' ASSIGNMENT_SET_ID ,'
||' SOURCING_RULE_TYPE ,'
||' SOURCE_ORGANIZATION_ID ,'
||' SOURCE_ORG_INSTANCE_ID ,'
||' ALLOCATION_PERCENT ,'
||' RANK ,'
||' EFFECTIVE_DATE ,'
||' DISABLE_DATE ,'
||' SOURCING_LEVEL ,'
||' ASSIGNMENT_ID ,'
||' SOURCING_RULE_ID ,'
||' SOURCING_RULE_NAME ,'
||' SOURCE_TYPE ,'
||' SR_DESCRIPTION ,'
||' COMPILE_DESIGNATOR ,'
||' OWNING_ORG_ID ,'
||' COMP_MRP_PLANNING_CODE ,'
||' COMP_BOM_ITEM_TYPE ,'
||' COMP_PLANNING_MAKE_BUY_CODE ,'
||' COMP_PRIMARY_UOM_CODE ,'
||' CUSTOMER_ID ,'
||' CUSTOMER_SITE_ID ,'
||' REGION_ID ,'
||' COMP_DRP_PLANNED )'
||' SELECT '
||' INVENTORY_ITEM_ID ,'
||' ORGANIZATION_ID ,'
||' SR_INSTANCE_ID ,'
||' PLAN_ID ,'
||' ASSIGNMENT_TYPE ,'
||' ASSIGNMENT_SET_ID ,'
||' SOURCING_RULE_TYPE ,'
||' SOURCE_ORGANIZATION_ID ,'
||' SOURCE_ORG_INSTANCE_ID ,'
||' ALLOCATION_PERCENT ,'
||' RANK ,'
||' EFFECTIVE_DATE ,'
||' DISABLE_DATE ,'
||' SOURCING_LEVEL ,'
||' ASSIGNMENT_ID ,'
||' SOURCING_RULE_ID ,'
||' SOURCING_RULE_NAME ,'
||' SOURCE_TYPE ,'
||' SR_DESCRIPTION ,'
||' COMPILE_DESIGNATOR ,'
||' OWNING_ORG_ID ,'
||' COMP_MRP_PLANNING_CODE ,'
||' COMP_BOM_ITEM_TYPE ,'
||' COMP_PLANNING_MAKE_BUY_CODE ,'
||' COMP_PRIMARY_UOM_CODE ,'
||' CUSTOMER_ID ,'
||' CUSTOMER_SITE_ID ,'
||' REGION_ID ,'
||' COMP_DRP_PLANNED '
||' FROM MSC_ITEM_SO_SR_LEVELS_V '
||lv_plan_id;
LOG_MESSAGE('Inserted records into MSC_ITEM_SO_SR_LEVELS');
' INSERT /*+ APPEND */ INTO MSC_ITEM_FCST_SR_LEVELS '
||' (INVENTORY_ITEM_ID ,'
||' SR_INVENTORY_ITEM_ID ,'
||' ORGANIZATION_ID ,'
||' SR_INSTANCE_ID ,'
||' PLAN_ID ,'
||' ASSIGNMENT_TYPE ,'
||' ASSIGNMENT_SET_ID ,'
||' SOURCING_RULE_TYPE ,'
||' SOURCE_ORGANIZATION_ID ,'
||' SOURCE_ORG_INSTANCE_ID ,'
||' VENDOR_ID ,'
||' VENDOR_SITE_ID ,'
||' ALLOCATION_PERCENT ,'
||' RANK ,'
||' EFFECTIVE_DATE ,'
||' DISABLE_DATE ,'
||' CATEGORY_ID ,'
||' SOURCING_LEVEL ,'
||' ASSIGNMENT_ID ,'
||' SOURCING_RULE_ID ,'
||' SOURCING_RULE_NAME ,'
||' SOURCE_TYPE ,'
||' SOURCE_ORG_CODE ,'
||' SR_DESCRIPTION ,'
||' COMPILE_DESIGNATOR ,'
||' OWNING_ORG_ID ,'
||' MRP_PLANNING_CODE ,'
||' BOM_ITEM_TYPE ,'
||' PLANNING_MAKE_BUY_CODE ,'
||' PRIMARY_UOM_CODE ,'
||' COMP_MRP_PLANNING_CODE ,'
||' CUSTOMER_ID ,'
||' CUSTOMER_SITE_ID ,'
||' ZONE_ID ,'
||' ASSY_DRP_PLANNED ,'
||' COMP_DRP_PLANNED ) '
||' SELECT '
||' INVENTORY_ITEM_ID ,'
||' SR_INVENTORY_ITEM_ID ,'
||' ORGANIZATION_ID ,'
||' SR_INSTANCE_ID ,'
||' PLAN_ID ,'
||' ASSIGNMENT_TYPE ,'
||' ASSIGNMENT_SET_ID ,'
||' SOURCING_RULE_TYPE ,'
||' SOURCE_ORGANIZATION_ID ,'
||' SOURCE_ORG_INSTANCE_ID ,'
||' VENDOR_ID ,'
||' VENDOR_SITE_ID ,'
||' ALLOCATION_PERCENT ,'
||' RANK ,'
||' EFFECTIVE_DATE ,'
||' DISABLE_DATE ,'
||' CATEGORY_ID ,'
||' SOURCING_LEVEL ,'
||' ASSIGNMENT_ID ,'
||' SOURCING_RULE_ID ,'
||' SOURCING_RULE_NAME ,'
||' SOURCE_TYPE ,'
||' SOURCE_ORG_CODE ,'
||' SR_DESCRIPTION ,'
||' COMPILE_DESIGNATOR ,'
||' OWNING_ORG_ID ,'
||' MRP_PLANNING_CODE ,'
||' BOM_ITEM_TYPE ,'
||' PLANNING_MAKE_BUY_CODE ,'
||' PRIMARY_UOM_CODE ,'
||' COMP_MRP_PLANNING_CODE ,'
||' CUSTOMER_ID ,'
||' CUSTOMER_SITE_ID ,'
||' ZONE_ID ,'
||' ASSY_DRP_PLANNED ,'
||' COMP_DRP_PLANNED '
||' FROM MSC_ITEM_FCST_SR_LEVELS_V '
||lv_plan_id;
LOG_MESSAGE( 'Inserted records into MSC_ITEM_FCST_SR_LEVELS');
DELETE MSC_ITEM_FCST_BOD_SR_LEVELS where plan_id = p_plan_id;
INSERT /*+APPEND*/ INTO MSC_ITEM_FCST_BOD_SR_LEVELS
(
INVENTORY_ITEM_ID ,
SR_INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
SR_INSTANCE_ID ,
PLAN_ID ,
ASSIGNMENT_TYPE ,
ASSIGNMENT_SET_ID ,
SOURCING_RULE_TYPE ,
SOURCE_ORGANIZATION_ID ,
SOURCE_ORG_INSTANCE_ID ,
VENDOR_ID ,
VENDOR_SITE_ID ,
ALLOCATION_PERCENT ,
RANK ,
SHIP_METHOD ,
EFFECTIVE_DATE ,
DISABLE_DATE ,
CATEGORY_ID ,
SOURCING_LEVEL ,
ASSIGNMENT_ID ,
SOURCING_RULE_ID ,
SOURCING_RULE_NAME ,
SOURCE_TYPE ,
SOURCE_ORG_CODE ,
SR_DESCRIPTION ,
OWNING_ORG_ID ,
CUSTOMER_ID ,
CUSTOMER_SITE_ID ,
ZONE_ID )
SELECT
INVENTORY_ITEM_ID ,
SR_INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
SR_INSTANCE_ID ,
PLAN_ID ,
ASSIGNMENT_TYPE ,
ASSIGNMENT_SET_ID ,
SOURCING_RULE_TYPE ,
SOURCE_ORGANIZATION_ID ,
SOURCE_ORG_INSTANCE_ID ,
VENDOR_ID ,
VENDOR_SITE_ID ,
ALLOCATION_PERCENT ,
RANK ,
SHIP_METHOD ,
EFFECTIVE_DATE ,
DISABLE_DATE ,
CATEGORY_ID ,
SOURCING_LEVEL ,
ASSIGNMENT_ID ,
SOURCING_RULE_ID ,
SOURCING_RULE_NAME ,
SOURCE_TYPE ,
SOURCE_ORG_CODE ,
SR_DESCRIPTION ,
OWNING_ORG_ID ,
CUSTOMER_ID ,
CUSTOMER_SITE_ID ,
ZONE_ID
FROM MSC_ITEM_FCST_BOD_SR_LEVELS_V
WHERE PLAN_ID = p_plan_id;
select decode(curr_plan_type,1,1,2,1,3,1,0)
into lv_p_plan_type
from msc_plans
where plan_id = p_plan_id;
select decode(DAILY_MATERIAL_CONSTRAINTS,1,1,
decode(DAILY_RESOURCE_CONSTRAINTS,1,1,
decode(WEEKLY_MATERIAL_CONSTRAINTS,1,1,
decode(WEEKLY_RESOURCE_CONSTRAINTS,1,1,
decode(PERIOD_MATERIAL_CONSTRAINTS,1,1,
decode(PERIOD_RESOURCE_CONSTRAINTS,1,1,0))))))
into lv_p_plan_so
from msc_plans
where plan_id = p_plan_id;
select decode(optimize_flag,1,1,
decode(nvl(fnd_profile.value('MSO_ENABLE_DECISION_RULES'),'N'),
'Y',1,'Yes',1,'YES',1,0))
into lv_p_plan_so
from msc_plans
where plan_id = p_plan_id;
DELETE MSC_BOD_SO_SR_LEVELS where plan_id = p_plan_id;
INSERT INTO MSC_TEMP_REGION_LOCATIONS(
REGION_ID ,
LOCATION_ID ,
LOCATION_SOURCE ,
REGION_TYPE ,
PARENT_REGION_FLAG ,
SR_INSTANCE_ID,
partner_type)
SELECT REGION_ID ,
LOCATION_ID ,
LOCATION_SOURCE ,
(10 * (10 - region_type)) REGION_TYPE ,
PARENT_REGION_FLAG ,
SR_INSTANCE_ID,
2 partner_type
FROM MSC_REGION_LOCATIONS
WHERE location_source = 'HZ'
and region_id is not null
and region_id in ( select distinct msa.region_id
from msc_sr_assignments msa,
msc_plans mp
where msa.assignment_type in (7,8,9)
and mp.plan_id = p_plan_id
and msa.assignment_set_id = mp.curr_assignment_set_id
)
UNION ALL
select a.REGION_ID,
c.LOCATION_ID,
c.LOCATION_SOURCE,
((10 * (10 - a.zone_level)) + 1) REGION_TYPE,
c.PARENT_REGION_FLAG,
a.SR_INSTANCE_ID,
2 PARTNER_TYPE
FROM MSC_REGIONS a,
MSC_ZONE_REGIONS b,
msc_region_locations c
WHERE a.region_id = b.parent_region_id
AND a.region_type = 10
AND a.zone_level IS NOT NULL
AND a.sr_instance_id = b.sr_instance_id
AND b.region_id = c.region_id
and b.sr_instance_id = c.sr_instance_id
and c.region_id is not null
and c.location_source = 'HZ'
and a.region_id in ( select distinct msa.region_id
from msc_sr_assignments msa,
msc_plans mp
where msa.assignment_type in (7,8,9)
and mp.plan_id = p_plan_id
and msa.assignment_set_id = mp.curr_assignment_set_id
)
;
DELETE MSC_SALES_ORDER_ITEMS where plan_id = p_plan_id;
INSERT /*+APPEND*/ into MSC_SALES_ORDER_ITEMS
(PLAN_ID,
SR_INSTANCE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
CUSTOMER_ID,
SHIP_TO_SITE_USE_ID)
(
SELECT DISTINCT p_plan_id PLAN_ID,
so.sr_instance_id SR_INSTANCE_ID,
so.organization_id ORGANIZATION_ID,
so.inventory_item_id INVENTORY_ITEM_ID,
so.customer_id CUSTOMER_ID,
so.ship_to_site_use_id SHIP_TO_SITE_USE_ID
FROM
MSC_SALES_ORDERS so
WHERE so.customer_id IS NOT NULL
AND so.ship_to_site_use_id IS NOT NULL);
INSERT /*+APPEND*/ into MSC_BOD_SO_SR_LEVELS
( INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
SR_INSTANCE_ID ,
PLAN_ID ,
ASSIGNMENT_TYPE ,
ASSIGNMENT_SET_ID ,
SOURCING_RULE_TYPE ,
SOURCE_ORGANIZATION_ID ,
SOURCE_ORG_INSTANCE_ID ,
ALLOCATION_PERCENT ,
RANK ,
SHIP_METHOD ,
EFFECTIVE_DATE ,
DISABLE_DATE ,
SOURCING_LEVEL ,
ASSIGNMENT_ID ,
SOURCING_RULE_ID ,
SOURCING_RULE_NAME ,
SOURCE_TYPE ,
SR_DESCRIPTION ,
COMPILE_DESIGNATOR ,
OWNING_ORG_ID ,
MRP_PLANNING_CODE ,
BOM_ITEM_TYPE ,
PLANNING_MAKE_BUY_CODE ,
PRIMARY_UOM_CODE ,
CUSTOMER_ID ,
CUSTOMER_SITE_ID ,
REGION_ID ,
REGION_TYPE )
SELECT
INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
SR_INSTANCE_ID ,
PLAN_ID ,
ASSIGNMENT_TYPE ,
ASSIGNMENT_SET_ID ,
SOURCING_RULE_TYPE ,
SOURCE_ORGANIZATION_ID ,
SOURCE_ORG_INSTANCE_ID ,
ALLOCATION_PERCENT ,
RANK ,
SHIP_METHOD ,
EFFECTIVE_DATE ,
DISABLE_DATE ,
SOURCING_LEVEL ,
ASSIGNMENT_ID ,
SOURCING_RULE_ID ,
SOURCING_RULE_NAME ,
SOURCE_TYPE ,
SR_DESCRIPTION ,
COMPILE_DESIGNATOR ,
OWNING_ORG_ID ,
MRP_PLANNING_CODE ,
BOM_ITEM_TYPE ,
PLANNING_MAKE_BUY_CODE ,
PRIMARY_UOM_CODE ,
CUSTOMER_ID ,
CUSTOMER_SITE_ID ,
REGION_ID ,
REGION_TYPE
FROM MSC_BOD_SO_SR_LEVELS_V
WHERE PLAN_ID = p_plan_id;
LOG_MESSAGE('Error in insert_into_table :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
PROCEDURE insert_into_table(
p_plan_id NUMBER,
p_sr_instance_id NUMBER,
p_org_id NUMBER,
p_bucket_index NUMBER,
p_msc_plan_buckets IN OUT NOCOPY msc_plan_buckets_typ,
p_err_mesg OUT NOCOPY VARCHAR2
) IS
j NUMBER;
insert into msc_plan_buckets(
PLAN_ID
,ORGANIZATION_ID
,SR_INSTANCE_ID
,BUCKET_INDEX
,BKT_START_DATE
,BKT_END_DATE
,BUCKET_TYPE
,DAYS_IN_BKT
,CURR_FLAG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY)
VALUES
(
p_plan_id,
p_org_id,
p_sr_instance_id,
p_msc_plan_buckets.bucket_index(k),
To_date(p_msc_plan_buckets.bkt_start_date(k), 'YYYY/MM/DD'),
To_date(p_msc_plan_buckets.bkt_end_date(k), 'YYYY/MM/DD')+(86399/86400),
p_msc_plan_buckets.bucket_type(k),
p_msc_plan_buckets.days_in_bkt(k),
1,
Sysdate,
1,
Sysdate,
1);
LOG_MESSAGE('Error in insert_into_table :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
END insert_into_table;
SELECT Nvl(min_cutoff_bucket,0), Nvl(hour_cutoff_bucket,0)
INTO p_min_cutoff_bucket,p_hour_cutoff_bucket
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT NVL(MIN(bkt_start_date), TRUNC(SYSDATE))
INTO first_date
FROM msc_plan_buckets
WHERE plan_id = p_plan_id
AND bucket_type = 1;
SELECT NVL(max(bkt_end_date), TRUNC(SYSDATE)),
NVL(max(bucket_index), 0)
INTO l_daily_cutoff_date, p_daily_cutoff_bucket
from msc_plan_buckets
where plan_id = p_plan_id
AND bucket_type = 1;
select nvl(curr_start_date-1, TRUNC(SYSDATE-1))
INTO l_daily_cutoff_date
from msc_plans
where plan_id = p_plan_id;
SELECT
Nvl(max(bkt_end_date),l_daily_cutoff_date),
Nvl(max(bucket_index), p_daily_cutoff_bucket)
INTO l_weekly_cutoff_date, p_weekly_cutoff_bucket
from msc_plan_buckets
where plan_id = p_plan_id
AND bucket_type = 2;
SELECT
Nvl(max(bkt_end_date),l_weekly_cutoff_date),
Nvl(max(bucket_index), p_weekly_cutoff_bucket)
INTO l_period_cutoff_date, p_period_cutoff_bucket
from msc_plan_buckets
where plan_id = p_plan_id
AND bucket_type = 3;
select nvl(validation_org_id,-1) into l_org_id
from msc_apps_instances
where instance_id = p_sr_instance_id;
select column_expression into l_longvar
from all_ind_expressions
where table_owner = p_table_owner
and index_owner = p_index_owner
and table_name = p_table_name
and index_name = p_index_name
and column_position = p_column_position;
select own_org_bkt.bucket_type,
own_org_bkt.bucket_index,
to_number(to_char(own_org_bkt.bkt_end_date,'J')),
to_number(to_char(nvl(org_bkt.bkt_end_date,sysdate),'J'))
into l_bucket_type,
l_bucket_index,
l_bkt_end_date,
l_bkt_end_date1
from msc_plan_buckets own_org_bkt,
msc_plan_buckets org_bkt
where own_org_bkt.plan_id = p_plan_id
and own_org_bkt.organization_id = p_owning_org_id
and own_org_bkt.sr_instance_id = p_owning_instance_id
and own_org_bkt.curr_flag = 1
and ((own_org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
own_org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
(own_org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
own_org_bkt.bucket_index = 1))
and org_bkt.plan_id(+) = own_org_bkt.plan_id
and org_bkt.organization_id(+) = p_owning_org_id
and org_bkt.sr_instance_id(+) = p_owning_instance_id
and org_bkt.curr_flag(+) = 1
and org_bkt.bucket_index(+) = own_org_bkt.bucket_index-1;
select to_number(to_char(cal2.calendar_date,'J'))
into l_calendar_date
from msc_calendar_dates cal1,
msc_calendar_dates cal2
where cal1.calendar_code = p_calendar_code
and cal1.calendar_date = to_date(l_bkt_end_date3,'J')
and cal1.exception_set_id = -1
and cal1.sr_instance_id = p_ss_instance_id
and cal2.seq_num = cal1.prior_seq_num
and cal2.calendar_code = cal1.calendar_code
and cal2.sr_instance_id = cal1.sr_instance_id
and cal2.exception_set_id = -1;
select to_number(to_char(cal2.calendar_date,'J'))
into l_calendar_date
from msc_plan_buckets org_bkt,
msc_calendar_dates cal1,
msc_calendar_dates cal2
where org_bkt.plan_id = p_plan_id
and org_bkt.organization_id = p_owning_org_id
and org_bkt.sr_instance_id = p_owning_instance_id
and org_bkt.curr_flag = 1
and ((org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
(org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
org_bkt.bucket_index = 1))
and cal1.calendar_code = p_calendar_code
and cal1.calendar_date = org_bkt.bkt_start_date
and cal1.exception_set_id = -1
and cal1.sr_instance_id = p_ss_instance_id
and cal2.seq_num = cal1.next_seq_num
and cal2.calendar_code = cal1.calendar_code
and cal2.sr_instance_id = cal1.sr_instance_id
and cal2.exception_set_id = -1;
select to_number(to_char(cal1.prior_date,'J'))
into l_calendar_date
from msc_plan_buckets own_org_bkt,
msc_plan_buckets org_bkt,
msc_calendar_dates cal1
where own_org_bkt.plan_id = p_plan_id
and own_org_bkt.organization_id = p_owning_org_id
and own_org_bkt.sr_instance_id = p_owning_instance_id
and own_org_bkt.curr_flag = 1
and ((own_org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
own_org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
(own_org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
own_org_bkt.bucket_index = 1))
and org_bkt.plan_id(+) = own_org_bkt.plan_id
and org_bkt.organization_id(+) = p_owning_org_id
and org_bkt.sr_instance_id(+) = p_owning_instance_id
and org_bkt.curr_flag(+) = 1
and org_bkt.bucket_index(+) = own_org_bkt.bucket_index-1
and cal1.calendar_code = p_calendar_code
and cal1.calendar_date = decode(own_org_bkt.bucket_type,1,trunc(own_org_bkt.bkt_end_date),decode(own_org_bkt.bucket_index,1,trunc(own_org_bkt.bkt_end_date),nvl(trunc(org_bkt.bkt_end_date),trunc(sysdate))))
and cal1.exception_set_id = -1
and cal1.sr_instance_id = p_ss_instance_id ;
select to_number(to_char(cal1.next_date,'J'))
into l_calendar_date
from msc_plan_buckets org_bkt,
msc_calendar_dates cal1
where org_bkt.plan_id = p_plan_id
and org_bkt.organization_id = p_owning_org_id
and org_bkt.sr_instance_id = p_owning_instance_id
and org_bkt.curr_flag = 1
and ((org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
(org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
org_bkt.bucket_index = 1))
and cal1.calendar_code = p_calendar_code
and cal1.calendar_date = org_bkt.bkt_start_date
and cal1.exception_set_id = -1
and cal1.sr_instance_id = p_ss_instance_id;
select nvl(mro.operation_lead_time_percent, 0.0)
into l_op_leadtime_percent
from
msc_routing_operations mro
where mro.plan_id = p_plan_id
and mro.sr_instance_id = p_sr_instance_id
and mro.routing_sequence_id = p_routing_seq_id
and mro.operation_seq_num = p_op_seq_num
and mro.effectivity_date <= sysdate
and (mro.disable_date >= sysdate or
mro.disable_date is NULL);
select min(cal.week_start_date)
into p_weekly_start_date
from msc_cal_week_start_dates cal,
msc_trading_partners tp
where cal.exception_set_id = tp.calendar_exception_set_id
and cal.calendar_code = tp.calendar_code
and cal.week_start_date >= trunc(v_daily_cutoff_date)
and cal.sr_instance_id = tp.sr_instance_id
and tp.sr_tp_id = p_org_id
and tp.partner_type = 3;
select min(cal.period_start_date)
into p_period_start_date
from msc_period_start_dates cal,
msc_trading_partners tp
where cal.exception_set_id = tp.calendar_exception_set_id
and cal.calendar_code = tp.calendar_code
and cal.period_start_date >= nvl(trunc(v_weekly_cutoff_date),
trunc(v_daily_cutoff_date))
and cal.sr_instance_id = tp.sr_instance_id
and tp.sr_tp_id = p_org_id
and tp.partner_type = 3;
select 'MSLD_XML_FILE_PATH_' || p_plan_id
INTO l_dest_dir
from dual;
Select xmlelement("MBPSnapshotSchema",
xmlattributes('12.2.0' "version"),
xmlagg(
xmlelement("FlatFile",
xmlattributes(mxc1.file_name as "name"),
(Select xmlagg(
xmlelement("Column",
xmlattributes(mxc2.column_name as "name",
decode(mxc2.column_type ,
1, 'NUMBER',
2, 'DOUBLE',
3, 'STRING',
4, 'DATE',
5, 'TIME',
6, 'DATE-TIME',
7, 'BOOLEAN',
'INVALID') as "type")
)order by mxc2.order_num
)
from msc_xml_schema mxc2
WHERE mxc2.plan_id =p_plan_id
and mxc2.file_name=mxc1.file_name
and upper(mxc2.column_name) <> 'PLAN_ID'
) as "Columns",
xmlelement("PrimaryKey",
(Select xmlagg(
xmlelement("Column",
xmlattributes(mxc3.column_name as "name")
)order by mxc3.order_num
)
from msc_xml_schema mxc3
WHERE mxc3.plan_id = p_plan_id
and mxc3.file_name=mxc1.file_name
and mxc3.is_primary_key = 1
) /*as "Columns"
) /*xmlelement
) /*xmlelement
) /*xmlagg
) /*xmlelement
INTO v_xml
FROM (select unique file_name
from msc_xml_schema
where plan_id = p_plan_id
order by file_name
) mxc1;
'DELETE FROM MSC_DMD_SCN_METRICS WHERE PLAN_ID = ' || p_plan_id;
INSERT INTO MSC_DMD_SCN_METRICS
(
PLAN_ID,
SCENARIO_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
MAPE_IN_SAMPLE,
MAPE_OUT_SAMPLE,
FORECAST_VOLATILITY,
AVG_DEMAND,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT p_plan_id PLAN_ID,
mdsm.SCENARIO_ID SCENARIO_ID,
mdsm.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
mdsm.ORGANIZATION_ID ORGANIZATION_ID,
mdsm.SR_INSTANCE_ID SR_INSTANCE_ID,
mdsm.MAPE_IN_SAMPLE MAPE_IN_SAMPLE,
mdsm.MAPE_OUT_SAMPLE MAPE_OUT_SAMPLE,
mdsm.FORECAST_VOLATILITY FORCAST_VOLATILITY,
mdsm.AVG_DEMAND AVG_DEMAND,
mdsm.CREATED_BY CREATED_BY,
mdsm.CREATION_DATE CREATION_DATE,
mdsm.LAST_UPDATE_DATE LAST_UPDATE_DATE,
mdsm.LAST_UPDATED_BY LAST_UPDATED_BY,
mdsm.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM
MSC_DMD_SCN_METRICS mdsm,
MSC_PLAN_SCHEDULES mpsv
WHERE
-1 = mdsm.plan_id
AND mpsv.input_schedule_id = mdsm.scenario_id
AND mpsv.organization_id = mdsm.organization_id
AND mpsv.sr_instance_id = mdsm.sr_instance_id
AND mpsv.designator_type = 7 /*SCN_DESIGNATOR_TYPE*/
AND mpsv.plan_id = p_plan_id );
PROCEDURE update_category_info(
p_err_mesg OUT NOCOPY VARCHAR2,
p_plan_id in NUMBER
) IS
lv_fetchComplete Boolean;
CURSOR items_category_update
IS
select items.inventory_item_id,
cat.organization_id,
cat.sr_instance_id,
cat.sr_category_id
from msc_system_items items, msc_item_categories cat, msc_plans plans
where cat.CATEGORY_SET_ID = plans.CATEGORY_SET_ID
and cat.inventory_item_id = items.inventory_item_id
and cat.sr_instance_id = items.sr_instance_id
and cat.organization_id = items.organization_id
and items.plan_id = plans.plan_id
and plans.plan_id = p_plan_id;
SELECT count(*)
INTO lv_plan_partition_exists
FROM MSC_PLAN_PARTITIONS
WHERE plan_id = p_plan_id;
OPEN items_category_update;
IF (items_category_update%ISOPEN) THEN
LOOP
IF (lv_fetchComplete) THEN
EXIT;
FETCH items_category_update
BULK COLLECT
INTO lb_inv_item_id,
lb_org_id,
lb_sr_instance_id,
lb_sr_category_id
LIMIT ln_rows_to_fetch;
IF (items_category_update%NOTFOUND) THEN
lv_fetchComplete := TRUE;
UPDATE MSC_SYSTEM_ITEMS
SET sr_category_id = lb_sr_category_id(j)
WHERE sr_instance_id = lb_sr_instance_id(j)
AND inventory_item_id = lb_inv_item_id(j)
AND organization_id = lb_org_id(j)
AND plan_id = p_plan_id;
IF (items_category_update%ISOPEN) THEN
CLOSE items_category_update;
END update_category_info;
PROCEDURE update_items_info(
p_err_mesg OUT NOCOPY VARCHAR2,
p_plan_id in NUMBER
) IS
lv_fetchComplete Boolean;
CURSOR items_for_update
IS
SELECT /*+ ORDERED USE_NL(ODS, MSC_SYSTEM_ITEMS_U1) */ ODS.INVENTORY_ITEM_ID,
PDS.ORGANIZATION_ID,
ODS.SR_INSTANCE_ID,
ODS.ITEM_NAME,
ODS.DESCRIPTION,
ODS.BUYER_NAME,
ODS.PLANNER_CODE,
ODS.PLANNING_EXCEPTION_SET,
ODS.REVISION,
-1
FROM MSC_SYSTEM_ITEMS PDS,
MSC_SYSTEM_ITEMS_V ODS
WHERE ODS.PLAN_ID = -1
AND ODS.PDS_PLAN_ID = PDS.PLAN_ID
AND ODS.INVENTORY_ITEM_ID = PDS.INVENTORY_ITEM_ID
AND ODS.ORGANIZATION_ID = DECODE(PDS.ORGANIZATION_ID, -1,
msc_snapshot_pk.get_validation_org_id(PDS.SR_INSTANCE_ID), PDS.ORGANIZATION_ID)
AND ODS.SR_INSTANCE_ID = PDS.SR_INSTANCE_ID
AND PDS.PLAN_ID = p_plan_id;
SELECT count(*)
INTO lv_plan_partition_exists
FROM MSC_PLAN_PARTITIONS
WHERE plan_id = p_plan_id;
OPEN items_for_update;
IF (items_for_update%ISOPEN) THEN
LOOP
IF (lv_fetchComplete) THEN
EXIT;
FETCH items_for_update
BULK COLLECT
INTO lb_inv_item_id,
lb_org_id,
lb_sr_instance_id,
lb_item_name,
lb_description,
lb_buyer_name,
lb_planner_code,
lb_plng_excp_set,
lb_revision,
lb_val_org_id
LIMIT ln_rows_to_fetch;
IF (items_for_update%NOTFOUND) THEN
lv_fetchComplete := TRUE;
UPDATE /*+ index (items MSC_SYSTEM_ITEMS_U1) */
MSC_SYSTEM_ITEMS items
SET item_name = lb_item_name(j),
description = lb_description(j),
buyer_name = lb_buyer_name(j),
planner_code = lb_planner_code(j),
planning_exception_set = lb_plng_excp_set(j),
revision = lb_revision(j)
WHERE sr_instance_id = lb_sr_instance_id(j)
AND inventory_item_id = lb_inv_item_id(j)
--AND (organization_id = lb_org_id(j) OR
-- (organization_id = -1 AND lb_val_org_id(j) = lb_org_id(j)
-- ))
AND organization_id = lb_org_id(j)
AND plan_id = p_plan_id;
IF (items_for_update%ISOPEN) THEN
CLOSE items_for_update;
END update_items_info;
SELECT TRUNC(nvl(max(period_start_date),sysdate))
into l_date
FROM msc_safety_stocks
WHERE period_start_date <= TRUNC(SYSDATE)
AND inventory_item_id = p_item_id
AND sr_instance_id = p_instance_id
AND organization_id = p_org_id
AND plan_id = p_plan_id;
SELECT TRUNC(nvl(max(period_start_date),sysdate))
into l_date
FROM msc_safety_stocks
WHERE period_start_date <= TRUNC(SYSDATE)
AND inventory_item_id = p_item_id
AND sr_instance_id = p_instance_id
AND organization_id = p_org_id
AND plan_id = p_plan_id
AND nvl(project_id,-23453) = nvl(p_project_id,-23453)
AND nvl(task_id,-23453) = nvl(p_task_id,-23453);