[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Delete_duplicate(p_instance_id in number, p_dest_table in varchar2);
Procedure Delete_Childless_Parent_All ( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance in VARCHAR2);
Procedure Delete_Childless_Parent (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR,
p_instance_id in number,
p_level_id in number);
-- p_dest_table in varchar2); Bug# 4919130 - Always delete childless parents from staging table.
/* Added logic to delete duplicate data */
Delete_duplicate(p_instance_id, MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE);
/* Delete duplicate level association from staging table */
Delete_duplicate_lvl_assoc(errbuf, retcode, p_instance_id);
Delete_duplicate(p_instance_id ,
MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE);
Delete_duplicate_lvl_assoc(errbuf, retcode, p_instance_id);
Delete_duplicate(p_instance_id ,
MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE);
Delete_duplicate_lvl_assoc(errbuf, retcode, p_instance_id);
p_update_lvl_table IN NUMBER) IS
x_statement VARCHAR2(2000) := NULL ;
x_delete_flag varchar2(1) := 'Y' ;
select distinct
relationship_view,
level_value_column,
level_value_pk_column,
nvl(level_value_desc_column,level_value_column) level_value_desc_column,
parent_value_column,
parent_value_pk_column,
nvl(parent_value_desc_column, parent_value_column) parent_value_desc_column
from msd_hierarchy_levels
where level_id = p_level_id
and parent_level_id = p_parent_level_id
and plan_type is null --vinekuma
and relationship_view is not null;
select distinct
hierarchy_id
from msd_hierarchy_levels
where level_id = p_level_id
and parent_level_id = p_parent_level_id
and relationship_view is null;
* which table to insert into - whether the staging
* table or the fact table
* 3. Get the view that hold the association and the
* corresponding column names.
* 4. Set the Save Point and delete the already existing
* data in the level values are the staging table.
* 5. Insert the new values from the association views
* 6. Commit
************************************************************/
retcode :=0;
p_update_lvl_table => p_update_lvl_table,
/* OPM Comment Rajesh Patangya */
p_delete_flag => x_delete_flag,
p_seq_num => p_seq_num );
x_delete_flag := 'N' ;
p_update_lvl_table => p_update_lvl_table,
/* OPM Comment Rajesh Patangya */
p_delete_flag => x_delete_flag,
p_seq_num => p_seq_num );
select hierarchy_name INTO p_hierarchy_name
FROM msd_hierarchies
WHERE hierarchy_id = Null_Relationship_Rec.hierarchy_id;
/* lvl_table needs to be updated only once - this is the indicator */
x_update_lvl_table NUMBER := 1;
select distinct level_id, parent_level_id, level_type_code
from msd_hierarchy_levels_v
where level_id = p_level_id
order by level_type_code, level_id;
p_update_lvl_table => x_update_lvl_table);
x_update_lvl_table := 0;
/* Bug# 5530511 - Delete_Childless_Parent_All should be called after
* the call to fix_orphans
*/
Delete_Childless_Parent_All ( errbuf, retcode, p_instance_id);
select level_id, parent_level_id, level_type_code
from msd_hierarchy_levels_v
where hierarchy_id = p_hierarchy_id
order by level_type_code, level_id;
select distinct level_id, level_type_code
from msd_hierarchy_levels_v
where hierarchy_id = p_hierarchy_id
order by level_type_code, level_id;
select distinct OWNING_DIMENSION_CODE
from msd_hierarchy_levels_v
where hierarchy_id = p_hierarchy_id;
p_update_lvl_table => 0);
p_update_lvl_table => 1);
/* Bug# 5530511 - Delete_Childless_Parent_All should be called after
* the call to fix_orphans
*/
Delete_Childless_Parent_ALL ( errbuf, retcode, p_instance_id);
select distinct level_id, parent_level_id, level_type_code
from msd_hierarchy_levels_v
where owning_dimension_code = p_dimension_code
order by level_type_code, level_id;
select distinct level_id, level_type_code
from msd_hierarchy_levels_v
where owning_dimension_code = p_dimension_code
order by level_type_code, level_id;
SELECT COUNT(*)
FROM
(SELECT DISTINCT level_id,
parent_level_id
FROM msd_hierarchy_levels_v
WHERE owning_dimension_code = p_dimension_code
AND level_id = p_level_id);
p_update_lvl_table => 0);
p_update_lvl_table => 1);
/* Bug# 5530511 - Delete_Childless_Parent_All should be called after
* the call to fix_orphans
*/
Delete_Childless_Parent_ALL ( errbuf, retcode, p_instance_id);
select distinct owning_dimension_code
from msd_dp_hierarchies_v
where demand_plan_id = p_demand_plan_id
and dp_dimension_code = p_dimension_code ;
select distinct dp_dimension_code
from msd_dp_hierarchies_v
where demand_plan_id = p_demand_plan_id ;
select lookup_code
from fnd_lookup_values_vl
where lookup_type = 'MSD_DIMENSIONS' ;
been inserted in the desination table.
***************************************************************/
procedure fix_orphans(p_instance_id in number,
p_level_id in number,
p_dest_table in varchar2,
p_dest_ass_table in varchar2,
p_hierarchy_id in number) IS
Cursor Parent_Levels(p_lvl_id IN NUMBER) is
select distinct parent_level_id
from msd_hierarchy_levels
where level_id = p_lvl_id
and hierarchy_id = nvl(p_hierarchy_id, hierarchy_id);
Note that even though this level value has not yet been inserted
into the destination table, it is okay to add an association
because the value itself will certainly be added in the next
pass over the parent level.
*/
v_other_pk := to_char(msd_sr_util.get_null_pk);
/* Insert association for orphans */
/* VM Logic : Find orphans using MINUS set between
records in level_values for Level in consideration and
records in level_Association for level and parent level in consideration
*/
v_sql_stmt := 'insert into ' || p_dest_ass_table || ' (' ||
'instance, ' ||
'level_id, ' ||
'sr_level_pk, ' ||
'parent_level_id, ' ||
'sr_parent_level_pk, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'creation_date, ' ||
'created_by ) ' ||
'select ''' ||
p_instance_id ||''', ' ||
p_level_id || ', ' ||
'sr_level_pk, ' ||
v_parent_level_id || ', ''' ||
v_other_pk || ''', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID ||
' from ' || p_dest_table || ' mlv ' ||
' where level_id = ' || p_level_id ||
' and instance = ' || p_instance_id ||
' minus ' ||
'select ''' ||
p_instance_id ||''', ' ||
p_level_id || ', ' ||
'sr_level_pk, ' ||
v_parent_level_id || ', ''' ||
v_other_pk || ''', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID ||
' from ' || p_dest_ass_table || ' amlv ' ||
' where level_id = ' || p_level_id ||
' and instance = ' || p_instance_id ||
' and parent_level_id = ' || v_parent_level_id;
-- insert into msd_test values(v_sql_stmt) ;
Procedure Delete_duplicate(p_instance_id in number, p_dest_table in varchar2) is
lb_FetchComplete BOOLEAN := FALSE;
Cursor c_Update_Level_Values
is
select level_id, sr_level_pk,system_attribute1,system_attribute2,dp_enabled_flag
from msd_st_level_values a
where a.instance = p_instance_id
and rowid = ( select max(rowid)
from msd_st_level_values b
where a.instance = b.instance
and a.level_id = b.level_id
and a.sr_level_pk = b.sr_level_pk
and b.system_attribute1 is not null); -- assuming here that if there exist more than one record
OPEN c_Update_Level_Values;
IF (c_Update_Level_Values%ISOPEN) THEN
LOOP
IF (lb_FetchComplete) THEN
EXIT;
FETCH c_Update_Level_Values BULK COLLECT INTO
lb_level_id,
lb_sr_level_pk,
lb_system_attribute1,
lb_system_attribute2,
lb_dp_enabled_flag
LIMIT ln_rows_to_fetch;
IF (c_Update_Level_Values%NOTFOUND) THEN
lb_FetchComplete := TRUE;
if c_Update_Level_Values%ROWCOUNT > 0 then
FORALL j IN lb_level_id.FIRST..lb_level_id.LAST
update msd_st_level_values
set system_attribute1 = lb_system_attribute1(j),
system_attribute2 = lb_system_attribute2(j),
dp_enabled_flag = lb_dp_enabled_flag(j)
where instance = p_instance_id
and level_id = lb_level_id(j)
and sr_level_pk = lb_sr_level_pk(j)
and system_attribute1 is null;
END IF; --IF (c_Update_Level_Values%ISOPEN) THEN
CLOSE c_Update_Level_Values;
/* This procedure deletes duplicate records from staging level_values
Key - Instance + Level_Id + SR_LEVEL_PK
*/
if p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE then
delete from msd_st_level_values a where
a.instance = p_instance_id and
rowid <> (select max(rowid) from msd_st_level_values b
where a.instance = b.instance
and a.level_id = b.level_id
and a.sr_level_pk = b.sr_level_pk);
Procedure Delete_duplicate_lvl_assoc( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id in number) is
cursor c_duplicate is
select level_id, sr_level_pk, parent_level_id
from msd_st_level_associations
where instance = p_instance_id
group by level_id, sr_level_pk, parent_level_id
having count(*) > 1;
/* This procedure deletes duplicate records from staging level association
Key - Instance + Child_Level_Id + SR_LEVEL_PK + Parent_Level_ID
*/
OPEN c_duplicate;
delete from msd_st_level_associations a where
a.instance = p_instance_id and
a.level_id = a_child_level_id(i) and
a.sr_level_pk = a_sr_level_pk(i) and
a.parent_level_id = a_parent_level_id(i) and
rowid <> (select rowid from msd_st_level_associations b
where b.instance = p_instance_id and
b.level_id = a_child_level_id(i) and
b.sr_level_pk = a_sr_level_pk(i) and
b.parent_level_id = a_parent_level_id(i) and
rownum < 2);
END Delete_duplicate_lvl_assoc;
Procedure Delete_Childless_Parent_ALL
This procedure will call delete_childless_parent for all level_id and
instance.
******************************************************************************************/
Procedure Delete_Childless_Parent_ALL ( errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance in VARCHAR2) IS
/* Cursor for staging table */
CURSOR c_st_level is
select distinct a.instance, a.level_id
from msd_st_level_values a, msd_levels b
where a.level_id = b.level_id and
a.instance <> 0 and
b.level_type_code = 3 and
a.instance = p_instance;
/* Bug# 4919130 - Always delete childless parents from staging table.
CURSOR c_level is
select distinct a.instance, a.level_id
from msd_level_values a, msd_levels b
where a.level_id = b.level_id and
a.instance <> 0 and
b.level_type_code = 3 and
a.instance = p_instance;
/* Bug# 4919130 - Always delete childless parents from staging table.
IF (fnd_profile.value('MSD_ONE_STEP_COLLECTION') = 'Y') THEN
l_dest_table := MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE;
Delete_Childless_Parent ( errbuf,
retcode,
Level_Rec.instance,
Level_Rec.level_id,
l_dest_table);
/* Bug# 4919130 - Always delete childless parents from staging table.
ELSE
l_dest_table := MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE ;
Delete_Childless_Parent ( errbuf,
retcode,
Level_Rec.instance,
Level_Rec.level_id);
-- l_dest_table); Bug# 4919130 - Always delete childless parents from staging table.
/* Bug# 4919130 - Always delete childless parents from staging table.
END IF;
END Delete_Childless_Parent_ALL;
Procedure Delete_Childless_Parent
This procedure will delete any childless parent level value.
First, We will determine whether destination talbe is Fact or Staging, then
Navigate level_value from either (msd_st_level_values or msd_level_values).
Check whether that level_id exist in level association table as
parent level id.
If it does, then navigate next level id, otherwise, delete it.
******************************************************************************************/
Procedure Delete_Childless_Parent (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR,
p_instance_id in number,
p_level_id in number) IS
-- p_dest_table in varchar2) IS Bug# 4919130 - Always delete childless parents from staging table.
CURSOR c_childless_parent is
select level_id, sr_level_pk
from msd_backup_level_values
where instance = '-999' and level_pk = -999;
delete from msd_backup_level_values
where instance = '-999' and level_pk = -999;
/* Bug# 4919130 - Always delete childless parents from staging table.
IF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) THEN
*/
insert into msd_backup_level_values (instance , level_id, sr_level_pk, level_pk )
select '-999' , level_id, sr_level_pk, -999
from msd_st_level_values
where instance = p_instance_id and level_id = p_level_id
minus
select '-999' , parent_level_id, sr_parent_level_pk, -999
from msd_st_level_associations
where instance = p_instance_id and parent_level_id = p_level_id ;
delete from msd_st_level_values a
where
instance = p_instance_id and
level_id = p_level_id and
exists (select 1 from msd_backup_level_values b
where b.instance = '-999' and b.level_id = a.level_id and
b.sr_level_pk = a.sr_level_pk and level_pk = -999);
delete from msd_st_level_associations st_a
where
instance = p_instance_id and
level_id = p_level_id and
exists (select 1 from msd_backup_level_values st_b
where st_b.instance = '-999' and st_b.level_id = st_a.level_id and
st_b.sr_level_pk = st_a.sr_level_pk and level_pk = -999);
/* Bug# 4919130 - Always delete childless parents from staging table.
ELSIF (p_dest_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_FACT_TABLE) THEN
insert into msd_backup_level_values (instance , level_id, sr_level_pk, level_pk )
select '-999' , level_id, sr_level_pk, -999
from msd_level_values
where instance = p_instance_id and level_id = p_level_id
minus
select '-999' , parent_level_id, sr_parent_level_pk, -999
from msd_level_associations
where instance = p_instance_id and parent_level_id = p_level_id ;
delete from msd_level_values a
where
instance = p_instance_id and
level_id = p_level_id and
exists (select 1 from msd_backup_level_values b
where b.instance = '-999' and b.level_id = a.level_id and
b.sr_level_pk = a.sr_level_pk and level_pk = -999);
Childless_rec.level_id || ' were deleted.');
fnd_file.put_line(fnd_file.log, l_count ||' childless level values deleted.' );
delete from msd_backup_level_values
where level_pk = -999 and instance = '-999';
fnd_file.put_line(fnd_file.log, 'Error in Delete_Childless_Parent.');
END Delete_Childless_Parent;