The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Walk up a hierarchy and insert parents into temporary table.
*/
procedure walk_up_hierarchy (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number);
* Walk down a hierarchy and insert children into temporary table.
*/
procedure walk_down_hierarchy (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number);
* Insert orgs into stripe which are related to level values in
* stripe.
*/
procedure insert_related_orgs (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number);
* Insert level values into stripe which are related to orgs in
* stripe.
*/
procedure insert_related_level_values (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number);
* Insert level values for non-striped dimensions.
*/
procedure insert_non_stripe_level_values (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_insert_rep in varchar2,
p_insert_geo in varchar2);
* Insert Fact Data into Stripe.
*/
procedure insert_fact_data (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_fast_refresh in varchar2);
* Delete Fact Data from Stripe.
*/
procedure delete_fact_data (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number);
* Update Parameters Stripe Information.
*/
procedure update_dp_parameters_ds (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number);
function chk_insert_no_associations (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number) return varchar2;
procedure chk_insert_org_no_associations (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number);
procedure insert_stream_items(errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_stripe_stream_name in varchar2,
p_stripe_stream_desig in varchar2,
p_dim_code in varchar2);
procedure insert_supercession_items (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number);
SELECT demand_plan_id,
demand_plan_name,
stripe_instance,
stripe_level_id,
stripe_sr_level_pk,
build_stripe_level_pk,
stripe_stream_name,
stripe_stream_desig,
build_stripe_stream_name,
build_stripe_stream_desig,
build_stripe_stream_ref_num
FROM msd_demand_plans
WHERE demand_plan_id = p_demand_plan_id;
select last_refresh_num
from msd_cs_data_headers
where cs_definition_id = (select cs_definition_id
from msd_cs_definitions
where name = p_name)
and cs_name = nvl(p_desig, cs_name)
order by last_refresh_num desc;
delete from msd_dp_session;
insert into msd_dp_session
( demand_plan_id )
values
( p_demand_plan_id );
delete from msd_level_values_ds
where demand_plan_id = p_demand_plan_id;
delete from msd_cs_Data_ds
where demand_plan_id = p_demand_plan_id;
delete from msd_dp_parameters_ds
where demand_plan_id = p_demand_plan_id;
update msd_demand_plans
set build_stripe_level_pk = null,
build_stripe_stream_name = null,
build_stripe_stream_desig = null,
build_stripe_stream_ref_num = null
where demand_plan_id = p_demand_plan_id;
select dimension_code
from msd_levels ml
where level_id = p_level_id;
select mhl.level_id
from msd_hierarchy_levels mhl
where mhl.hierarchy_id = p_hierarchy_id
and mhl.parent_level_id = p_parent_level_id;
select hierarchy_id
from msd_hierarchies
where dimension_code = p_dimension_code;
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select distinct
p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv,
msd_level_values_ds_temp mld,
msd_level_associations mla
where mla.level_id = x_child_level
and mla.parent_level_id = x_current_parent_level_id
and mla.parent_level_id = mld.level_id
and mla.sr_parent_level_pk = mld.sr_level_pk
and mlv.instance = mla.instance
and mla.instance = mld.instance
and mlv.level_id = mla.level_id
and mlv.sr_level_pk = mla.sr_level_pk
and mld.demand_plan_id = p_demand_plan_id
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
select dimension_code
from msd_levels ml
where level_id = p_level_id;
select mhl.parent_level_id
from msd_hierarchy_levels mhl
where mhl.hierarchy_id = p_hierarchy_id
and mhl.level_id = p_child_level_id;
select hierarchy_id
from msd_hierarchies
where dimension_code = p_dimension_code;
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select distinct
p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv,
msd_level_values_ds_temp mld,
msd_level_associations mla
where mla.parent_level_id = x_parent_level
and mla.level_id = x_current_child_level_id
and mla.level_id = mld.level_id
and mla.sr_level_pk = mld.sr_level_pk
and mlv.instance = mla.instance
and mla.instance = mld.instance
and mlv.level_id = mla.parent_level_id
and mlv.sr_level_pk = mla.sr_parent_level_pk
and mld.demand_plan_id = p_demand_plan_id
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
procedure insert_related_orgs (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number) is
begin
if l_debug = C_YES_FLAG then
debug_out( 'Entering insert_related_orgs ' || to_char(sysdate, 'hh24:mi:ss'));
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_org_asscns mlo,
msd_level_values mlv,
msd_level_values_ds_temp mld
where mld.demand_plan_id = p_demand_plan_id
and mlo.instance = mld.instance
and mlo.org_level_id = p_level_id
and mlo.org_sr_level_pk = mlv.sr_level_pk
and mlo.instance = mlv.instance
and mlo.org_level_id = mlv.level_id
and mlo.level_id = mld.level_id
and mlo.sr_level_pk = mld.sr_level_pk
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
chk_insert_org_no_associations (errbuf,
retcode,
p_demand_plan_id,
C_ORGS_LEVEL_ID);
debug_out( 'Exiting insert_related_orgs ' || to_char(sysdate, 'hh24:mi:ss'));
procedure insert_related_level_values (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number) is
cursor chk_dim (p_dim_code in varchar2) is
select C_TRUE
from msd_dp_dimensions
where demand_plan_id = p_demand_plan_id
and dimension_code = p_dim_code;
SELECT meaning
FROM fnd_lookup_values_vl
WHERE
lookup_type = 'MSD_LEVEL_VALUE_DESC'
AND lookup_code = 'I';
debug_out( 'Entering insert_related_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_org_asscns mlo,
msd_level_values mlv,
msd_level_values_ds_temp mld
where mld.demand_plan_id = p_demand_plan_id
and mlo.instance = mld.instance
-- and mlo.org_level_id = mld.level_id Bug# 4929528
and mlo.org_level_id = decode(p_level_id,C_ITEM_LEVEL_ID, 7, 8) -- Bug# 4929528
and mld.level_id = decode(p_level_id,C_ITEM_LEVEL_ID, 7, 8) -- Bug# 4929528
and mlo.org_sr_level_pk = mld.sr_level_pk
and mlo.instance = mlv.instance
and mlo.level_id = mlv.level_id
and mlo.level_id = p_level_id
and mlo.sr_level_pk = mlv.sr_level_pk
and nvl(mlv.system_attribute1, '123') <> v_internal_desc -- ISO Code Change
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
/* ISO Code Change - Only for GEO dimension - Insert the internal sites for the
* ISO orgs attached to the plan
*/
IF (p_level_id in (C_SHIP_LEVEL_ID)) THEN
INSERT INTO msd_level_values_ds_temp
( DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG )
SELECT p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
FROM msd_dp_iso_organizations mdio,
msd_level_org_asscns mloa,
msd_level_values mlv
WHERE
mdio.demand_plan_id = p_demand_plan_id
AND mloa.instance = mdio.sr_instance_id
AND mloa.level_id = 11
AND mloa.org_level_id = 7
AND mloa.org_sr_level_pk = mdio.sr_organization_id
AND mlv.instance = mloa.instance
AND mlv.level_id = 11
AND mlv.sr_level_pk = mloa.sr_level_pk
AND mlv.system_attribute1 = v_internal_desc
MINUS
SELECT p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
FROM msd_level_values_ds_temp
WHERE demand_plan_id = p_demand_plan_id;
debug_out( 'Exiting insert_related_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
procedure insert_non_stripe_level_values (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_insert_rep in varchar2,
p_insert_geo in varchar2) is
begin
if l_debug = C_YES_FLAG then
debug_out( 'Entering insert_non_stripe_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
if ((p_insert_rep is null) and (p_insert_geo is null)) then
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv,
msd_levels ml
where ml.level_id = mlv.level_id
and ml.dimension_code not in (C_PRD_DIM_CODE,C_ORG_DIM_CODE,C_REP_DIM_CODE,C_GEO_DIM_CODE)
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
elsif ((p_insert_rep is not null) and (p_insert_geo is null)) then
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv,
msd_levels ml
where ml.level_id = mlv.level_id
and ml.dimension_code not in (C_PRD_DIM_CODE,C_ORG_DIM_CODE,C_GEO_DIM_CODE)
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
elsif ((p_insert_rep is null) and (p_insert_geo is not null)) then
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv,
msd_levels ml
where ml.level_id = mlv.level_id
and ml.dimension_code not in (C_PRD_DIM_CODE,C_GEO_DIM_CODE,C_REP_DIM_CODE)
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
elsif ((p_insert_rep is not null) and (p_insert_geo is not null)) then
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv,
msd_levels ml
where ml.level_id = mlv.level_id
and ml.dimension_code not in (C_PRD_DIM_CODE,C_ORG_DIM_CODE)
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
debug_out( 'Exiting insert_non_stripe_level_values ' || to_char(sysdate, 'hh24:mi:ss'));
insert_related_level_values(errbuf, retcode, p_demand_plan_id, C_REPS_LEVEL_ID);
if (chk_insert_no_associations (errbuf,
retcode,
p_demand_plan_id,
C_REPS_LEVEL_ID) = C_TRUE) then
x_rep_dim_code := C_REP_DIM_CODE;
insert_related_level_values(errbuf, retcode, p_demand_plan_id, C_SHIP_LEVEL_ID);
if (chk_insert_no_associations (errbuf,
retcode,
p_demand_plan_id,
C_SHIP_LEVEL_ID) = C_TRUE) then
x_geo_dim_code := C_GEO_DIM_CODE;
insert_non_stripe_level_values( errbuf, retcode, p_demand_plan_id, x_rep_dim_code, x_geo_dim_code);
procedure insert_fact_data (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_fast_refresh in varchar2) is
cursor c0 is
select distinct
parameter_type,
parameter_name
from msd_dp_parameters
where demand_plan_id = p_demand_plan_id;
select cs_definition_id,
multiple_stream_flag,
stripe_flag
from msd_cs_definitions
where name = p_cs_defn_name;
select dimension_code
from msd_cs_defn_dim_dtls
where cs_definition_id = p_cs_id
and collect_flag = C_YES_FLAG
and dimension_code <> 'TIM';
select last_refresh_num
from msd_cs_data_headers
where cs_definition_id = p_cs_id
and cs_name = nvl(p_cs_name, cs_name)
order by last_refresh_num desc;
select refresh_num
from msd_dp_parameters_ds
where demand_plan_id = p_demand_plan_id
and parameter_type = p_parameter_type
and nvl(parameter_name, '&*') = nvl(p_parameter_name, nvl(parameter_name, '&*'))
and data_type = C_FACT;
debug_out( 'Entering insert_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
/* append the following sql to the insert statement */
/* This will only insert records which have last_refresh num > the current one */
if (stripe_refresh < fact_refresh) then
v_sql_refresh_stmt := ' and created_by_refresh_num > ' || stripe_refresh;
/* check if delete by parameter_name is necesary */
if (c1_rec.multiple_stream_flag = C_YES_FLAG) then
delete from msd_cs_data_ds
where demand_plan_id = p_demand_plan_id
and cs_definition_id = c1_rec.cs_definition_id
and cs_name = c0_rec.parameter_name;
delete from msd_cs_data_ds
where demand_plan_id = p_demand_plan_id
and cs_definition_id = c1_rec.cs_definition_id;
v_sql_stmt := v_sql_stmt || ' insert into msd_cs_data_ds ';
v_sql_stmt := v_sql_stmt || ' last_update_date, last_updated_by, creation_date, created_by) ';
v_sql_stmt := v_sql_stmt || ' select /*+ ORDERED */ mds.demand_plan_id, cdv.cs_data_id, cdv.cs_definition_id, cdv.cs_name, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id ';
v_sql_stmt := v_sql_stmt || ' from (select demand_plan_id from msd_dp_session) mds, ';
debug_out( 'Exiting insert_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
select dimension_code
from msd_levels ml
where level_id = p_level_id;
* should also be inserted.
*/
cursor c1(p_instance in varchar2,
p_level_id in number,
p_sr_level_pk in varchar2) is
select level_pk, level_value, system_attribute1, system_attribute2, dp_enabled_flag
from msd_level_values
where instance = p_instance
and level_id = p_level_id
and sr_level_pk = p_sr_level_pk;
delete from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1, -- Bug# 4937978
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
) VALUES
(
p_demand_plan_id,
p_stripe_instance,
p_stripe_level_id,
p_stripe_sr_level_pk,
l_c1_rec.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
l_c1_rec.system_attribute1, -- Bug# 4937978
l_c1_rec.system_attribute2,
l_c1_rec.dp_enabled_flag
);
update msd_demand_plans
set build_stripe_level_pk = l_c1_rec.level_pk,
build_stripe_stream_name = null,
build_stripe_stream_desig = null,
build_stripe_stream_ref_num = null
where demand_plan_id = p_demand_plan_id;
insert_related_orgs(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
insert_related_level_values(errbuf, retcode, p_demand_plan_id, C_ITEM_LEVEL_ID);
/* insert supercession items */
insert_supercession_items(errbuf, retcode, p_demand_plan_id);
select demand_plan_id, instance, level_id, sr_level_pk
from msd_level_values_ds
where demand_plan_id = p_demand_plan_id
minus
select demand_plan_id, instance, level_id, sr_level_pk
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
select demand_plan_id, instance, level_id, sr_level_pk
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id
minus
select demand_plan_id, instance, level_id, sr_level_pk
from msd_level_values_ds
where demand_plan_id = p_demand_plan_id;
delete from msd_level_values_ds
where demand_plan_id = p_demand_plan_id;
insert into msd_level_values_ds
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
delete from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
are upto date when insert into msd_cs_data_ds is done.
*/
commit;
select refresh_num
from msd_dp_parameters_ds
where demand_plan_id = C_LEVEL_PLAN
and data_type = C_LVL_VAL;
select refresh_num
from msd_dp_parameters_ds
where demand_plan_id = p_demand_plan_id
and data_type = C_LVL_VAL;
select dimension_code
from msd_dp_dimensions
where demand_plan_id = p_demand_plan_id
and dimension_code in (C_REP_DIM_CODE,C_GEO_DIM_CODE)
minus
select parameter_type
from msd_dp_parameters_ds
where demand_plan_id = p_demand_plan_id
and data_type = C_DIM
and parameter_type in (C_REP_DIM_CODE,C_GEO_DIM_CODE);
select parameter_type
from msd_dp_parameters_ds
where demand_plan_id = p_demand_plan_id
and data_type = C_DIM
and parameter_type in (C_REP_DIM_CODE,C_GEO_DIM_CODE)
minus
select dimension_code
from msd_dp_dimensions
where demand_plan_id = p_demand_plan_id
and dimension_code in (C_REP_DIM_CODE,C_GEO_DIM_CODE);
select mlv.instance,
mlv.level_id,
mlv.sr_level_pk
from msd_events me,
msd_dp_events mde,
msd_event_products mep,
msd_evt_prod_relationships mepr,
msd_level_values_ds mlvd,
msd_level_values mlv
where mepr.instance = mlvd.instance
and mepr.product_lvl_id = mlvd.level_id
and mlvd.level_id = 1
and mepr.sr_product_lvl_pk = mlvd.sr_level_pk
and mep.event_id = mepr.event_id
and mep.seq_id = mepr.seq_id
and mepr.npi_prod_relationship = C_NPI_BASE_PRODUCT
and me.event_id = mep.event_id
and me.event_type = 3
and me.event_id = mde.event_id
and mde.demand_plan_id = p_demand_plan_id
and mlv.instance = mep.instance
and mlv.sr_level_pk = mep.sr_product_lvl_pk
and mlv.level_id = mep.product_lvl_id
and mlvd.demand_plan_id = p_demand_plan_id
minus
select instance,
level_id,
sr_level_pk
from msd_level_values_ds
where demand_plan_id = p_demand_plan_id;
select level_pk, level_value
from msd_level_values
where instance = p_instance
and level_id = p_level_id
and sr_level_pk = p_sr_level_pk;
procedure delete_fact_data (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number) is
cursor c1 is
select parameter_type, parameter_name
from msd_dp_parameters_ds
where demand_plan_id = p_demand_plan_id
and data_type = C_FACT
minus
select parameter_type, parameter_name
from msd_dp_parameters
where demand_plan_id = p_demand_plan_id;
debug_out( 'Entering delete_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
delete from msd_cs_data_ds
where cs_definition_id = (select cs_definition_id
from msd_cs_definitions
where name = c1_rec.parameter_type)
and cs_name = c1_rec.parameter_name
and demand_plan_id = p_demand_plan_id;
delete from msd_cs_data_ds
where cs_definition_id = (select cs_definition_id
from msd_cs_definitions
where name = c1_rec.parameter_type)
and demand_plan_id = p_demand_plan_id;
debug_out( 'Exiting delete_fact_data ' || to_char(sysdate, 'hh24:mi:ss'));
procedure update_dp_parameters_ds (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number) is
begin
if l_debug = C_YES_FLAG then
debug_out( 'Entering update_dp_parameters_ds ' || to_char(sysdate, 'hh24:mi:ss'));
delete from msd_dp_parameters_ds
where demand_plan_id = p_demand_plan_id;
insert into msd_dp_parameters_ds
( DEMAND_PLAN_ID,
DATA_TYPE,
PARAMETER_TYPE,
PARAMETER_NAME,
REFRESH_NUM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
)
select p_demand_plan_id,
C_LVL_VAL,
null,
null,
refresh_num,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
from msd_dp_parameters_ds
where demand_plan_id = C_LEVEL_PLAN
and data_type = C_LVL_VAL
union all
select p_demand_plan_id,
C_FACT,
mdp.parameter_type,
mdp.parameter_name,
max(csh.last_refresh_num),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
from msd_dp_parameters mdp,
msd_cs_data_headers csh,
msd_cs_definitions csd
where mdp.parameter_type = csd.name
and csd.cs_definition_id = csh.cs_definition_id
and csh.cs_name = nvl(mdp.parameter_name, csh.cs_name)
and mdp.demand_plan_id = p_demand_plan_id
group by mdp.parameter_type, mdp.parameter_name
union all
select p_demand_plan_id,
C_DIM,
mdd.dimension_code,
null,
null,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
from msd_dp_dimensions mdd
where mdd.demand_plan_id = p_demand_plan_id;
debug_out( 'Exiting update_dp_parameters_ds ' || to_char(sysdate, 'hh24:mi:ss'));
insert_fact_data (errbuf, retcode, p_demand_plan_id, l_fast_refresh_fact);
delete_fact_data (errbuf, retcode, p_demand_plan_id);
update_dp_parameters_ds (errbuf, retcode, p_demand_plan_id);
function chk_insert_no_associations (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number) return varchar2 IS
cursor chk_row_count is
select 1
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id
and level_id = p_level_id
and rownum = 1;
end chk_insert_no_associations;
select dimension_code
from msd_levels ml
where level_id = p_level_id;
delete from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
insert_stream_items(errbuf,
retcode,
p_demand_plan_id,
p_stripe_stream_name,
p_stripe_stream_desig,
C_PRD_DIM_CODE);
/* insert supercession items */
insert_supercession_items(errbuf, retcode, p_demand_plan_id);
update msd_demand_plans
set build_stripe_stream_name = p_stripe_stream_name,
build_stripe_stream_desig = p_stripe_stream_desig,
build_stripe_stream_ref_num = x_refresh_num,
build_stripe_level_pk = null
where demand_plan_id = p_demand_plan_id;
insert_related_orgs(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
select dimension_code
from msd_levels ml
where level_id = p_level_id;
* should also be inserted.
*/
cursor c1(p_instance in varchar2,
p_level_id in number,
p_sr_level_pk in varchar2) is
select level_pk, level_value, system_attribute1, system_attribute2, dp_enabled_flag
from msd_level_values
where instance = p_instance
and level_id = p_level_id
and sr_level_pk = p_sr_level_pk;
delete from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1, -- Bug# 4937978
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
) VALUES
(
p_demand_plan_id,
p_stripe_instance,
p_stripe_level_id,
p_stripe_sr_level_pk,
l_c1_rec.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
l_c1_rec.system_attribute1, -- Bug# 4937978
l_c1_rec.system_attribute2,
l_c1_rec.dp_enabled_flag
);
update msd_demand_plans
set build_stripe_level_pk = l_c1_rec.level_pk,
build_stripe_stream_name = p_stripe_stream_name,
build_stripe_stream_desig = p_stripe_stream_desig,
build_stripe_stream_ref_num = x_refresh_num
where demand_plan_id = p_demand_plan_id;
insert_related_level_values(errbuf, retcode, p_demand_plan_id, C_ITEM_LEVEL_ID);
/* insert supercession items */
insert_supercession_items(errbuf, retcode, p_demand_plan_id);
insert_related_orgs(errbuf, retcode, p_demand_plan_id, C_ORGS_LEVEL_ID);
procedure insert_stream_items(errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_stripe_stream_name in varchar2,
p_stripe_stream_desig in varchar2,
p_dim_code in varchar2) IS
cursor get_stream_defn(p_name in varchar2) is
select mcd.cs_definition_id,
mcd.name,
mcd.multiple_stream_flag,
mcd.stripe_flag,
nvl(mcd.planning_server_view_name, 'MSD_CS_DATA_V')
from msd_cs_definitions mcd
where name = p_name;
SELECT collect_level_id
FROM msd_cs_defn_dim_dtls
where dimension_code = p_dim_code
and collect_flag = 'Y'
and cs_definition_id = p_cs_id;
SELECT planning_view_column_name
FROM msd_cs_defn_column_dtls_v
WHERE column_identifier = upper(p_dim_code)||'_LEVEL_ID'
AND identifier_type = 'DIMENSION_ID'
AND cs_definition_id = p_cs_id;
debug_out( 'Entering insert_stream_items ' || to_char(sysdate, 'hh24:mi:ss'));
l_sql_stmt := ' insert into msd_level_values_ds_temp ' ||
' ( ' ||
' DEMAND_PLAN_ID, ' ||
' INSTANCE, ' ||
' LEVEL_ID, ' ||
' SR_LEVEL_PK, ' ||
' LEVEL_PK, ' ||
' LAST_UPDATE_DATE, ' ||
' LAST_UPDATED_BY, ' ||
' CREATION_DATE, ' ||
' CREATED_BY, ' ||
' SYSTEM_ATTRIBUTE1, ' ||
' SYSTEM_ATTRIBUTE2, ' ||
' DP_ENABLED_FLAG ' ||
' ) ' ||
' select distinct ' || p_demand_plan_id || ' , ' ||
' mlv.instance, ' ||
' mlv.level_id, ' ||
' mlv.sr_level_pk, ' ||
' mlv.level_pk, ' ||
' sysdate, ' ||
' fnd_global.user_id, ' ||
' sysdate, ' ||
' fnd_global.user_id, ' ||
' mlv.system_attribute1, ' ||
' mlv.system_attribute2, ' ||
' mlv.dp_enabled_flag ' ||
' from msd_level_values mlv, ' ||
x_planning_server_view_name || ' fact ' ||
' where mlv.level_pk = fact.' || p_dim_code || '_LEVEL_VALUE_PK ' ||
' and mlv.level_id = fact.' || x_dim_lvl_clmn_name ||
' and fact.action_code = ''I''';
debug_out( 'Exiting insert_stream_items ' || to_char(sysdate, 'hh24:mi:ss'));
select level_pk, level_value
from msd_level_values
where instance = p_instance
and level_id = p_level_id
and sr_level_pk = p_sr_level_pk;
select mcd.cs_definition_id,
mcd.name,
mcd.multiple_stream_flag,
mcd.stripe_flag,
nvl(mcd.planning_server_view_name,'MSD_CS_DATA_V')
from msd_cs_definitions mcd
where name = p_name;
SELECT collect_level_id
FROM msd_cs_defn_dim_dtls
where dimension_code = p_dim_code
and collect_flag = 'Y'
and cs_definition_id = p_cs_id;
SELECT planning_view_column_name
FROM msd_cs_defn_column_dtls_v
WHERE column_identifier = upper(p_dim_code)||'_LEVEL_ID'
AND identifier_type = 'DIMENSION_ID'
AND cs_definition_id = p_cs_id;
l_sql_stmt := ' delete from msd_level_values_ds_temp ' ||
' where level_id = ' || C_ITEM_LEVEL_ID ||
' and demand_plan_id = ' || p_demand_plan_id ||
' and level_pk in ( ' ||
' select to_char(level_pk) from msd_level_values_ds_temp ' ||
' where demand_plan_id = ' || p_demand_plan_id ||
' minus select to_char(' ||
' fact.' || p_dim_code || '_LEVEL_VALUE_PK) ' ||
' from ' || x_planning_server_view_name || ' fact ' ||
' where 1 = 1 ';
procedure insert_supercession_items (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number) IS
begin
if l_debug = C_YES_FLAG then
debug_out( 'Entering insert_supercession_items ' || to_char(sysdate, 'hh24:mi:ss'));
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_dp_events mde,
msd_events me,
msd_event_products mep,
msd_evt_prod_relationships mepr,
msd_level_values_ds_temp mlvd,
msd_level_values mlv
where mepr.instance = mlvd.instance
and mepr.product_lvl_id = mlvd.level_id
and mlvd.level_id = 1
and mepr.sr_product_lvl_pk = mlvd.sr_level_pk
and mep.event_id = mepr.event_id
and mep.seq_id = mepr.seq_id
and mepr.npi_prod_relationship = C_NPI_BASE_PRODUCT
and me.event_id = mep.event_id
and me.event_type = 3
and mde.event_id = me.event_id
and mde.demand_plan_id = p_demand_plan_id
and mlv.instance = mep.instance
and mlv.sr_level_pk = mep.sr_product_lvl_pk
and mlv.level_id = mep.product_lvl_id
and mlvd.demand_plan_id = p_demand_plan_id
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
debug_out( 'Exiting insert_supercession_items ' || to_char(sysdate, 'hh24:mi:ss'));
SELECT planning_view_column_name
FROM msd_cs_defn_column_dtls_v
WHERE cs_definition_id = p_id
AND identifier_type = 'CSIDEN';
insert_fact_data (errbuf, retcode, p_demand_plan_id, l_fast_refresh_fact);
delete_fact_data (errbuf, retcode, p_demand_plan_id);
update_dp_parameters_ds (errbuf, retcode, p_demand_plan_id);
select 1
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id
and level_pk = C_PSEUDO_PK;
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
) VALUES
(
p_demand_plan_id,
C_PSEUDO_PK,
C_PSEUDO_PK,
C_PSEUDO_PK,
C_PSEUDO_PK,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
);
debug_out( 'Error Insert Pseudo Level Value into Stripe.');
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv
where mlv.sr_level_pk = x_other_sr_level_pk
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv
where (mlv.level_id, mlv.sr_level_pk) IN (select level_id, sr_level_pk from msd_level_values where level_id in
(select level_id from msd_levels where level_type_code = 1))
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
select C_TRUE
from msd_dp_dimensions
where dimension_code = p_dim_code
and demand_plan_id = p_demand_plan_id;
procedure chk_insert_org_no_associations (errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_demand_plan_id in number,
p_level_id in number) IS
cursor chk_row_count is
select 1
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id
and level_id = p_level_id
and rownum = 1;
insert into msd_level_values_ds_temp
(
DEMAND_PLAN_ID,
INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SYSTEM_ATTRIBUTE1,
SYSTEM_ATTRIBUTE2,
DP_ENABLED_FLAG
)
select p_demand_plan_id,
mlv.instance,
mlv.level_id,
mlv.sr_level_pk,
mlv.level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mlv.system_attribute1,
mlv.system_attribute2,
mlv.dp_enabled_flag
from msd_level_values mlv
where mlv.level_id = p_level_id
minus
select p_demand_plan_id,
instance,
level_id,
sr_level_pk,
level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
system_attribute1,
system_attribute2,
dp_enabled_flag
from msd_level_values_ds_temp
where demand_plan_id = p_demand_plan_id;
end chk_insert_org_no_associations;
SELECT 1
FROM dual
WHERE EXISTS (SELECT mloa.sr_level_pk
FROM msd_dp_iso_organizations mdio,
msd_level_org_asscns mloa
WHERE
mdio.demand_plan_id = p_demand_plan_id
AND mloa.instance = mdio.sr_instance_id
AND mloa.level_id = 11
AND mloa.org_level_id = 7
AND mloa.org_sr_level_pk = mdio.sr_organization_id
MINUS
SELECT sr_level_pk
FROM msd_level_values_ds
WHERE
demand_plan_id = p_demand_plan_id
AND level_id = 11);
* This cursor checks whether any existing internal orgs/sites have been deleted
*/
CURSOR c_is_deleted_iso_orgs
IS
SELECT 1
FROM dual
WHERE EXISTS (SELECT sr_level_pk
FROM msd_level_values_ds
WHERE
demand_plan_id = p_demand_plan_id
AND level_id = 11
MINUS
SELECT mloa.sr_level_pk
FROM msd_dp_iso_organizations mdio,
msd_level_org_asscns mloa
WHERE
mdio.demand_plan_id = p_demand_plan_id
AND mloa.instance = mdio.sr_instance_id
AND mloa.level_id = 11
AND mloa.org_level_id = 7
AND mloa.org_sr_level_pk = mdio.sr_organization_id);
/* Check if any existing internal orgs/sites have been deleted */
OPEN c_is_deleted_iso_orgs;
FETCH c_is_deleted_iso_orgs INTO x_is_present;
CLOSE c_is_deleted_iso_orgs;