[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into msd_test values ('VM' || to_char(sysdate, 'hh24:mi') || ' ' || a);
p_delete_flag IN VARCHAR2);
PROCEDURE DELETED_ITEM_LIST_PRICE(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN VARCHAR2,
p_seq_num IN NUMBER);
PROCEDURE UPDATE_ITEM_LIST_PRICE(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN VARCHAR2,
p_seq_num IN NUMBER);
p_delete_flag IN VARCHAR2);
PROCEDURE CREATE_DELETED_LEVEL_ASSOCI(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN VARCHAR2,
p_level_id IN NUMBER,
p_parent_level_id IN NUMBER,
p_seq_num IN NUMBER);
PROCEDURE CREATE_DELETED_LEVEL_VALUES(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN VARCHAR2,
p_level_id IN NUMBER,
p_seq_num IN NUMBER );
p_delete_flag IN VARCHAR2);
p_update_lvl_table IN NUMBER,
p_delete_flag IN VARCHAR2,
p_seq_num IN NUMBER ) IS
--,p_launched_from IN NUMBER ) IS --jarorad
v_instance_id varchar2(40);
v_up := p_update_lvl_table;
Savepoint Before_Delete ;
IF (p_update_lvl_table = 1) THEN
/* Insert deleted level values into deleted_level_value table and delete it
from the fact level value table */
/* For Incremental Level Value Collection, p_delete_flag = 'N'
So, we don't delete existing level values */
IF (p_delete_flag = 'Y') THEN
CREATE_DELETED_LEVEL_VALUES( errbuf,
retcode,
p_instance_id,
p_level_id,
p_seq_num);
/* Insert deleted level associations into deleted level association table
and delete it from the existing fact level associations table */
/* For Incremental Level Value Collection, p_delete_flag = 'N'
So, we don't delete existing level values */
IF (p_delete_flag = 'Y') THEN
CREATE_DELETED_LEVEL_ASSOCI( errbuf,
retcode,
p_instance_id,
p_level_id,
p_parent_level_id,
p_seq_num);
/* Delete Staging Table only if delete flag = Yes */
IF (p_delete_flag = 'Y') THEN
/* First time to process this level_id */
IF (p_update_lvl_table = 1) THEN
DELETE FROM msd_st_level_values
WHERE instance = p_instance_id AND level_id = p_level_id;
DELETE FROM msd_st_level_associations
WHERE instance = p_instance_id AND
level_id = p_level_id AND parent_level_id = p_parent_level_id;
select substr(p_source_table,1,decode(instr(p_source_table,'@')-1,-1,length(p_source_table),instr(p_source_table,'@')-1))
INTO v_table_name from dual;
v_sql_stmt3 := ' select count(*) '
||' from sys.all_tab_columns'|| x_dblink ||
' where table_name = '''||v_table_name||''' and column_name = ''SYSTEM_ATTRIBUTE1'' ';
v_sql_stmt4 := ' select count(*) '
||' from sys.all_tab_columns'|| x_dblink ||
' where table_name = '''||v_table_name||''' and column_name = ''DP_ENABLED_FLAG'' ';
/* Insert Level Values into staging table */
v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || p_dest_table || ' ( ' ||
'instance, ' ||
'level_id, ' ||
'level_value, ' ||
'sr_level_pk, ' ||
'level_value_desc, ' ||
'attribute1, ' ||
'attribute2, ' ||
'attribute3, ' ||
'attribute4, ' ||
'attribute5, ' ||
v_sql_stmt1 || --jarorad
'last_update_date, ' ||
'last_updated_by, ' ||
'creation_date, ' ||
'created_by ) ' ||
'select ''' ||
p_instance_id ||''', ' ||
p_level_id || ', ' ||
p_level_value_column||', ' ||
p_level_value_pk_column||', ' ||
p_level_value_desc_column||', ' ||
'attribute1, ' ||
'attribute2, ' ||
'attribute3, ' ||
'attribute4, ' ||
'attribute5, ' ||
v_sql_stmt1 || --jarorad
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ' ' ||
'from ' ||
p_source_table ;
into staging. At the end these will be deleted by delete_duplicate in the
collection program
if (p_update_lvl_table = 0) then
v_sql_stmt := v_sql_stmt ||
' where ' || p_level_value_pk_column || ' not in ' ||
'(select sr_level_pk from ' || p_dest_table ||
' where instance = ' || p_instance_id ||
' and level_id = ' || p_level_id || ')';
/* Insert Level Associations into staging table */
v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || v_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 || ', ' ||
p_level_value_pk_column||', ' ||
p_parent_level_id || ', ' ||
p_parent_value_pk_column ||', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ' ' ||
'from ' ||
p_source_table ;
select level_type_code into v_parent_lvl_type
from msd_levels
where level_id = p_parent_level_id
and plan_type is null; --vinekuma
IF (v_parent_lvl_type = '1' AND p_update_lvl_table = 1) THEN
PROCESS_TOP_LEVEL_VALUES (
errbuf,
retcode,
p_source_table,
p_dest_table,
p_instance_id,
p_parent_level_id,
p_parent_value_column,
p_parent_value_pk_column,
p_parent_value_desc_column,
p_seq_num,
p_delete_flag);
if p_update_lvl_table is set to 1 (i.e. the level_id had not been
processed before) and level_id is the lowest level or intermediate level
in the product dimension */
IF (p_update_lvl_table = 1) THEN /* IF 1 */
select level_type_code, dimension_code, org_relationship_view
into v_lvl_type, v_dim_code, v_org_view
from msd_levels
where level_id = p_level_id
and plan_type is null; --vinekuma
p_delete_flag);
END IF; /* End of p_update_lvl_table = 1 IF 1*/
IF (p_update_lvl_table = 1 AND p_level_id = 7) THEN
pop_org_cal_associations (
errbuf,
retcode,
p_source_table,
p_dest_table,
p_instance_id
);
IF (p_update_lvl_table = 1 AND p_level_id in (1,18,11)) THEN
pop_org_lvl_associations (
errbuf,
retcode,
p_level_id,
p_source_table,
v_org_view,
p_dest_table,
p_instance_id,
p_delete_flag);
/* New Level values will be inserted into fact table
and will get deleted from the staging */
CURSOR c_insert IS
select sr_level_pk
from msd_st_level_values
where instance = p_instance_id and level_id = p_level_id
MINUS
select sr_level_pk
from msd_level_values
where instance = p_instance_id and level_id = p_level_id;
new level values are deleted from the staging table
*/
CURSOR c_update IS
(select sr_level_pk, level_value,
attribute1, attribute2, attribute3,
attribute4, attribute5,
level_value_desc,system_attribute1,system_attribute2, --jarorad
dp_enabled_flag --jarorad
from msd_st_level_values
where instance = p_instance_id and level_id = p_level_id
MINUS
select sr_level_pk, level_value,
attribute1, attribute2, attribute3,
attribute4, attribute5,
level_value_desc,system_attribute1,system_attribute2, --jarorad
dp_enabled_flag --jarorad
from msd_level_values
where instance = p_instance_id and level_id = p_level_id);
OPEN c_insert;
FETCH c_insert BULK COLLECT INTO a_sr_level_pk;
CLOSE c_insert;
/* First Delete fetched rows from staging, and then
Insert them into Fact Table.
*/
FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
DELETE FROM msd_st_level_values
WHERE instance = p_instance_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i)
RETURNING level_value, attribute1,
attribute2, attribute3, attribute4,
attribute5, level_value_desc,
system_attribute1,system_attribute2, --jarorad
dp_enabled_flag --jarorad
BULK COLLECT INTO a_level_value, a_attribute1,
a_attribute2, a_attribute3,
a_attribute4, a_attribute5,
a_level_value_desc,a_system_attribute1, --jarorad
a_system_attribute2,a_dp_enabled_flag; --jarorad
/* Insert new rows into fact table */
FORALL j IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
INSERT INTO msd_level_values(
instance, level_id, level_value,
sr_level_pk, level_pk, level_value_desc,
action_code, created_by_refresh_num, last_refresh_num,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, attribute1, attribute2,
attribute3, attribute4, attribute5,
system_attribute1,system_attribute2, --jarorad
dp_enabled_flag) --jarorad
VALUES( p_instance_id, p_level_id, a_level_value(j),
a_sr_level_pk(j), MSD_COMMON_UTILITIES.get_level_pk(),
a_level_value_desc(j),
'I', p_seq_num, p_seq_num,
sysdate, FND_GLOBAL.USER_ID,
sysdate, FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID, a_attribute1(j), a_attribute2(j),
a_attribute3(j), a_attribute4(j), a_attribute5(j),
a_system_attribute1(j), a_system_attribute2(j), --jarorad
a_dp_enabled_flag(j) ); --jarorad
/* Fetch updated rows from staging */
OPEN c_update;
FETCH c_update BULK COLLECT INTO a_sr_level_pk, a_level_value, a_attribute1,
a_attribute2, a_attribute3,
a_attribute4, a_attribute5, a_level_value_desc,
a_system_attribute1,a_system_attribute2, --jarorad
a_dp_enabled_flag; --jarorad
CLOSE c_update;
UPDATE msd_level_values
SET level_value = a_level_value(i),
attribute1 = a_attribute1(i),
attribute2 = a_attribute2(i),
attribute3 = a_attribute3(i),
attribute4 = a_attribute4(i),
attribute5 = a_attribute5(i),
level_value_desc = a_level_value_desc(i),
system_attribute1 = a_system_attribute1(i), --jarorad
system_attribute2 = a_system_attribute2(i), --jarorad
dp_enabled_flag = a_dp_enabled_flag(i), --jarorad
action_code = 'U',
last_refresh_num = p_seq_num,
last_update_date = sysdate
WHERE instance = p_instance_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i);
PROCEDURE CREATE_DELETED_LEVEL_VALUES
***********************************************************/
PROCEDURE CREATE_DELETED_LEVEL_VALUES(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN VARCHAR2,
p_level_id IN NUMBER,
p_seq_num IN NUMBER) IS
CURSOR c_delete IS
(select sr_level_pk
from msd_level_values
where instance = p_instance_id and level_id = p_level_id
MINUS
select sr_level_pk
from msd_st_level_values
where instance = p_instance_id and level_id = p_level_id);
OPEN c_delete;
FETCH c_delete BULK COLLECT INTO a_sr_level_pk;
CLOSE c_delete;
DELETE FROM msd_level_values
WHERE instance = p_instance_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i)
RETURNING level_pk, created_by_refresh_num
BULK COLLECT INTO a_level_pk, a_crn;
INSERT INTO msd_deleted_level_values(instance, level_id,
sr_level_pk, level_pk,
created_by_refresh_num, last_refresh_num,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
VALUES(p_instance_id, p_level_id,
a_sr_level_pk(j), a_level_pk(j),
a_crn(j) , p_seq_num,
sysdate, FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID);
1. We should mark data deleted from msd_cs_Data for deleted level
values
2. We should delete level associations for level values being deleted.
*/
END IF;
END CREATE_DELETED_LEVEL_VALUES;
/* This cursur will select only new level associations */
CURSOR c_new_rows IS
(select sr_level_pk
from msd_st_level_associations
where instance = p_instance_id and level_id = p_level_id and
parent_level_id = p_parent_level_id
MINUS
select sr_level_pk
from msd_level_associations
where instance = p_instance_id and level_id = p_level_id and
parent_level_id = p_parent_level_id);
/* Cursor for updated level association */
/* This cursor need to be opened only after
new associations are deleted from the staging table */
CURSOR c_update_rows IS
(select sr_level_pk, sr_parent_level_pk
from msd_st_level_associations
where instance = p_instance_id and level_id = p_level_id and
parent_level_id = p_parent_level_id
MINUS
select sr_level_pk, sr_parent_level_pk
from msd_level_associations
where instance = p_instance_id and level_id = p_level_id and
parent_level_id = p_parent_level_id);
/* First Delete fetched rows(new level associations) from staging,
and then Insert them into Fact Table.
*/
FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
DELETE FROM msd_st_level_associations
WHERE instance = p_instance_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i) and
parent_level_id = p_parent_level_id
RETURNING sr_parent_level_pk
BULK COLLECT INTO a_sr_parent_level_pk;
/* Insert new rows into fact table */
IF (a_sr_parent_level_pk.exists(1)) THEN
FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
INSERT INTO msd_level_associations(
instance, level_id, sr_level_pk,
parent_level_id, sr_parent_level_pk,
last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
created_by_refresh_num, last_refresh_num, action_code)
VALUES(p_instance_id, p_level_id, a_sr_level_pk(i),
p_parent_level_id, a_sr_parent_level_pk(i),
sysdate, FND_GLOBAL.USER_ID,
sysdate,FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID,
p_seq_num, p_seq_num, 'I');
OPEN c_update_rows;
FETCH c_update_rows BULK COLLECT INTO a_sr_level_pk, a_sr_parent_level_pk;
CLOSE c_update_rows;
/* For updated level association */
IF (a_sr_level_pk.exists(1) and a_sr_parent_level_pk.exists(1)) THEN
FORALL i IN a_sr_level_pk.FIRST..a_sr_level_pk.LAST
UPDATE msd_level_associations
SET
sr_parent_level_pk = a_sr_parent_level_pk(i),
action_code = 'U',
last_refresh_num = p_seq_num,
last_update_date = sysdate
WHERE instance = p_instance_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i) and
parent_level_id = p_parent_level_id;
PROCEDURE CREATE_DELETED_LEVEL_ASSOCI
***********************************************************/
PROCEDURE CREATE_DELETED_LEVEL_ASSOCI(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN VARCHAR2,
p_level_id IN NUMBER,
p_parent_level_id IN NUMBER,
p_seq_num IN NUMBER) IS
CURSOR c_delete IS
(select sr_level_pk, sr_parent_level_pk
from msd_level_associations
where instance = p_instance_id and level_id = p_level_id and
parent_level_id = p_parent_level_id
MINUS
select sr_level_pk, sr_parent_level_pk
from msd_st_level_associations
where instance = p_instance_id and level_id = p_level_id and
parent_level_id = p_parent_level_id);
OPEN c_delete;
FETCH c_delete BULK COLLECT INTO a_sr_level_pk, a_sr_parent_level_pk;
CLOSE c_delete;
DELETE FROM msd_level_associations
WHERE instance = p_instance_id and
level_id = p_level_id and
sr_level_pk = a_sr_level_pk(i) and
parent_level_id = p_parent_level_id and
sr_parent_level_pk = a_sr_parent_level_pk(i);
END CREATE_DELETED_LEVEL_ASSOCI;
p_delete_flag IN VARCHAR2) IS
v_sql_stmt varchar2(4000);
/* Find deleted top level values, if any */
/* Top Level Values can be modified, but should not be deleted.
comment out this part
IF (p_delete_flag = 'Y') THEN
CREATE_DELETED_LEVEL_VALUES( errbuf,
retcode,
p_instance_id,
p_parent_level_id,
p_seq_num);
/* Update or insert new top level values */
PROCESS_LEVEL_VALUE_PER_ROW( errbuf,
retcode,
p_instance_id,
p_parent_level_id,
p_seq_num);
delete from msd_st_level_values
where instance = p_instance_id
and level_id = p_parent_level_id ;
v_sql_stmt := 'insert /*+ ALL_ROWS */ into ' || p_dest_table || ' ( ' ||
'instance, ' ||
'level_value, ' ||
'sr_level_pk, ' ||
'level_id, ' ||
'level_value_desc, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'creation_date, ' ||
'created_by ) ' ||
'SELECT ''' ||
p_instance_id ||''', ' ||
p_parent_value_column || ', ' ||
p_parent_value_pk_column ||', ' ||
p_parent_level_id || ', ' ||
'parent_desc_alias' ||', ' ||
'sysdate, ' || FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' || FND_GLOBAL.USER_ID || ' ' ||
'FROM ' ||
'(select distinct ' || p_parent_value_column || ', ' ||
p_parent_value_pk_column || ', ' ||
p_parent_level_id || ', '||
p_parent_value_desc_column || ' parent_desc_alias ' || ' from ' ||
p_source_table || ') src ';
p_delete_flag IN VARCHAR2) IS
x_dblink VARCHAR2(128);
IF (p_delete_flag = 'Y' and p_level_id = 1) THEN
DELETED_ITEM_LIST_PRICE( errbuf,
retcode,
p_instance_id,
p_seq_num);
UPDATE_ITEM_LIST_PRICE( errbuf,
retcode,
p_instance_id,
p_seq_num);
delete from msd_st_item_list_price
where instance = p_instance_id;
delete from msd_st_item_list_price
where instance = p_instance_id;
v_sql_stmt:= ' insert into msd_st_item_list_price ( '||
'instance, '||
'item, '||
'sr_item_pk, '||
'list_price, '||
'avg_discount, '||
'base_uom, '||
'item_type_id, ' ||
'forecast_type_id, ' ||
'creation_date, '||
'created_by, '||
'last_update_date, '||
'last_updated_by, '||
'last_update_login) '||
'SELECT ''' || p_instance_id || ''','||
'item,'||
'sr_item_pk, '||
'list_price, '||
'avg_discount, '||
'base_uom, ' ||
'item_type_id, ' ||
'forecast_type_id, ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
'sysdate, ' ||
FND_GLOBAL.USER_ID || ', ' ||
FND_GLOBAL.USER_ID || ' ' ||
'FROM ' ||
' msd_sr_item_list_price_v' || x_dblink ||
' where sr_item_pk in (select to_number(decode(ltrim(sr_level_pk, ''.0123456789''),' ||
' null, sr_level_pk, null)) ' ||
' from msd_st_level_values ' ||
' where level_id = ' || p_level_id || ')' ;
PROCEDURE DELETED_ITEM_LIST_PRICE
***********************************************************/
PROCEDURE DELETED_ITEM_LIST_PRICE(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN VARCHAR2,
p_seq_num IN NUMBER) IS
CURSOR c_delete IS
(select sr_item_pk
from msd_item_list_price
where instance = p_instance_id
MINUS
select sr_item_pk
from msd_st_item_list_price
where instance = p_instance_id);
OPEN c_delete;
FETCH c_delete BULK COLLECT INTO a_sr_item_pk;
CLOSE c_delete;
DELETE FROM msd_item_list_price
WHERE sr_item_pk = a_sr_item_pk(i) and instance = p_instance_id;
INSERT INTO msd_deleted_item_list_price(instance, sr_item_pk, created_by_refresh_num,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
VALUES(p_instance_id, a_sr_item_pk(j), p_seq_num,
sysdate, FND_GLOBAL.USER_ID, sysdate,
FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID);
END DELETED_ITEM_LIST_PRICE;
PROCEDURE UPDATE_ITEM_LIST_PRICE
***********************************************************/
PROCEDURE UPDATE_ITEM_LIST_PRICE(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_instance_id IN VARCHAR2,
p_seq_num IN NUMBER) IS
CURSOR c_st_rows IS
select item, list_price, avg_discount, base_uom,
sr_item_pk, item_type_id, forecast_type_id
from msd_st_item_list_price
where instance = p_instance_id;
select sr_item_pk, item, list_price, avg_discount, base_uom,
item_type_id, forecast_type_id
from msd_item_list_price
where instance = p_instance_id and sr_item_pk = p_item_pk;
/* If this row doesn't exist in fact table then insert */
IF (l_item_pk is null) THEN
INSERT INTO msd_item_list_price( instance, item, list_price,
avg_discount, base_uom,
sr_item_pk, item_type_id, forecast_type_id,
action_code, created_by_refresh_num, last_refresh_num,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login)
VALUES( p_instance_id, rec_st_rows.item, rec_st_rows.list_price,
rec_st_rows.avg_discount, rec_st_rows.base_uom,
rec_st_rows.sr_item_pk, rec_st_rows.item_type_id,
rec_st_rows.forecast_type_id,
'I', p_seq_num, p_seq_num,
sysdate, FND_GLOBAL.USER_ID,
sysdate, FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID);
updated row or not. */
IF ( (nvl(rec_st_rows.item, 'NULL') <> nvl(l_item, 'NULL')) OR
(nvl(rec_st_rows.list_price,-9999) <> nvl(l_list_price, -9999)) OR
(nvl(rec_st_rows.avg_discount,-9999) <> nvl(l_avg_discount,-9999)) OR
(nvl(rec_st_rows.base_uom,'NULL') <> nvl(l_base_uom,'NULL') ) OR
(nvl(rec_st_rows.item_type_id,-9999) <> nvl(l_item_type_id,-9999)) OR
(nvl(rec_st_rows.forecast_type_id,-9999) <> nvl(l_forecast_type_id,-9999)) ) THEN
/* If this row has been modified */
UPDATE msd_item_list_price
SET item = rec_st_rows.item,
list_price = rec_st_rows.list_price,
avg_discount = rec_st_rows.avg_discount,
base_uom = rec_st_rows.base_uom,
item_type_id = rec_st_rows.item_type_id,
forecast_type_id = rec_st_rows.forecast_type_id,
action_code = 'U',
last_refresh_num = p_seq_num,
last_update_date = sysdate
WHERE instance = p_instance_id and
sr_item_pk = rec_st_rows.sr_item_pk;
END UPDATE_ITEM_LIST_PRICE;
This procedure will clean up the MSD_DELETED_LEVEL_VALUES table,
MSD_DELETED_LEVEL_ASSOCIATIONS, MSD_DELETED_ITEM_LIST_PRICE table
-----------------------------------------------------------------*/
PROCEDURE CLEAN_DELETED_LEVEL_VALUES(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2) IS
l_least_refresh_num NUMBER := 0;
SELECT nvl(min(dp_build_refresh_num), 0) INTO l_least_refresh_num
FROM msd_demand_plans;
DELETE FROM msd_deleted_level_values
WHERE LAST_REFRESH_NUM <= l_least_refresh_num;
/* DELETE FROM msd_deleted_level_associations
WHERE CREATED_BY_REFRESH_NUM < l_least_refresh_num;
DELETE FROM msd_deleted_item_list_price
WHERE CREATED_BY_REFRESH_NUM < l_least_refresh_num;
END CLEAN_DELETED_LEVEL_VALUES;
/* Destination table to insert into */
v_dest_table VARCHAR2(1000);
/* The Insert-Select Sql Statement */
v_stmt VARCHAR2(2000);
delete from msd_st_org_calendars
where instance = p_instance_id;
delete from msd_org_calendars
where instance = p_instance_id;
/** Insert Data **/
if (p_source_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
insert into msd_org_calendars
(INSTANCE,
SR_ORG_PK,
CALENDAR_TYPE,
CALENDAR_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
select
a.instance,
a.sr_org_pk,
a.calendar_type,
a.calendar_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
from
(select distinct
instance,
sr_org_pk,
calendar_type,
calendar_code
from msd_st_org_calendars
where instance = p_instance_id) a;
delete from msd_st_org_calendars where instance = p_instance_id;
v_stmt := 'insert into ' || v_dest_table ||
' ( INSTANCE, SR_ORG_PK, CALENDAR_TYPE, CALENDAR_CODE, ' ||
' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) ' ||
' select ' ||
p_instance_id || ', ' ||
' mod.organization_id' || ', ' ||
'''' || p_man_cal_type || '''' || ', ' ||
' mod.calendar_code' || ', ' ||
' sysdate' || ', ' ||
fnd_global.user_id || ', ' ||
' sysdate' || ', ' ||
fnd_global.user_id || ' ' ||
' From MSD_ORGANIZATION_DEFINITIONS' || x_dblink || ' MOD';
v_stmt := v_stmt || ' where exists (select 1 from msd_app_instance_orgs' || x_dblink || ' maio where MOD.organization_id = MAIO.organization_id)';
p_delete_flag IN VARCHAR2) IS
/* Destination table to insert into */
v_dest_table VARCHAR2(1000);
/* The Insert-Select Sql Statement */
v_stmt VARCHAR2(2000);
cursor c_delete is
select instance,
level_id,
sr_level_pk,
org_level_id,
org_sr_level_pk
from msd_st_level_org_asscns
where instance = p_instance_id
and level_id = p_lvl_id;
delete from msd_st_level_org_asscns
where instance = p_instance_id
and level_id = p_lvl_id;
/* Bug # 3745624. Delete all level org asscns only when Complete Refresh = 'y' else delete only those values which
exist in msd_st_level_org_asscns */
elsif (p_delete_flag = 'Y') then
delete from msd_level_org_asscns
where instance = p_instance_id
and level_id = p_lvl_id;
elsif (p_delete_flag = 'N') then
for c_delete_cur in c_delete loop
delete from msd_level_org_asscns
where instance = p_instance_id
and level_id = p_lvl_id
and sr_level_pk = c_delete_cur.sr_level_pk
and org_level_id = c_delete_cur.org_level_id
and org_sr_level_pk = c_delete_cur.org_sr_level_pk;
/** Insert Data **/
if (p_source_table = MSD_COMMON_UTILITIES.LEVEL_VALUES_STAGING_TABLE) then
insert into msd_level_org_asscns
( INSTANCE,
LEVEL_ID,
SR_LEVEL_PK,
ORG_LEVEL_ID,
ORG_SR_LEVEL_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
)
select
a.instance,
a.level_id,
a.sr_level_pk,
a.org_level_id,
a.org_sr_level_pk,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
from
(select distinct
instance,
level_id,
sr_level_pk,
org_level_id,
org_sr_level_pk
from msd_st_level_org_asscns
where instance = p_instance_id
and level_id = p_lvl_id) a;
delete from msd_st_level_org_asscns
where instance = p_instance_id
and level_id = p_lvl_id;
v_stmt := 'insert into ' || v_dest_table ||
' ( INSTANCE, LEVEL_ID, SR_LEVEL_PK, ORG_LEVEL_ID, ORG_SR_LEVEL_PK, ' ||
' CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) ' ||
' select ' ||
p_instance_id || ', ' ||
p_lvl_id || ', ' ||
' src.level_value_pk ' || ', ' ||
' src.org_level_id ' || ', ' ||
' src.org_level_value_pk ' || ', ' ||
' sysdate ' || ', ' ||
fnd_global.user_id || ', ' ||
' sysdate ' || ', ' ||
fnd_global.user_id || ' ' ||
' From ' || p_org_relationship_view || x_dblink || ' src';
SELECT REFRESH_NUM INTO x_temp
FROM MSD_DP_PARAMETERS_DS
WHERE DEMAND_PLAN_ID = -1;
UPDATE MSD_DP_PARAMETERS_DS
SET REFRESH_NUM = p_seq_num
WHERE DEMAND_PLAN_ID = -1;
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
) VALUES
(
-1,
'LEVEL_VALUES',
null,
null,
p_seq_num,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
);
v_sql := 'update msd_level_values lv '||
'set level_pk = nvl((select level_pk '||
'from msd_backup_level_values bak '||
'where bak.level_id = ' || p_level_id ||
' and bak.instance = '''|| p_instance ||
''' and bak.sr_level_pk = lv.sr_level_pk), level_pk) '||
'where lv.instance = ''' || p_instance ||
''' and lv.level_id = ' || p_level_id;