[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure insert_update_Into_Headers ( p_cs_definition_id in number,
p_cs_name in varchar2,
p_instance_id in number,
p_refresh_num in number);
Procedure Insert_Data_Into_Headers ( p_cs_definition_id in number,
p_cs_name in varchar2,
p_instance_id in number,
p_refresh_num in number);
Function Build_SQL_INS_AS_SELECT(
p_cs_definition_id in number,
p_instance_id in varchar2,
p_cs_name in varchar2,
p_source_view in varchar2,
p_db_link in varchar2) return varchar2;
select * from msd_cs_definitions_v1
where
cs_definition_id = p_definition_id and
nvl(valid_flag, 'N') = 'Y';
select DESCRIPTION
into ll_name
from msd_cs_definitions
where cs_definition_id = p_definition_id;
SELECT msd.msd_last_refresh_number_s.nextval into
l_new_refresh_Num from dual;
SELECT msd.msd_last_refresh_number_s.nextval into
l_new_refresh_Num from dual;
/* Delete cs fact rows that are not used by any demand plans */
MSD_TRANSLATE_FACT_DATA.clean_fact_data( errbuf,
retcode,
l_target);
/* Delete Successfully processed Staging rows if the process was Staging to Fact */
/* DWK Don't delete any row with instance = 0 */
/* Also, removed cs_name = p_cs_name condition from WHERE clause */
IF p_process_type = C_STAGE_TO_FACT THEN
delete from MSD_ST_CS_DATA
where
cs_definition_id = p_cs_rec.cs_definition_id and
process_Status = C_LOG_PROCESSED and
attribute_1 <> '0';
select 'Y'
from msd_st_cs_data
where cs_definition_id = p_cs_rec.cs_definition_id
and cs_name = p_cs_name
and attribute_1 = p_instance_id
and attribute_49 = '1'
and rownum < 2;
delete from msd_st_cs_data
where cs_definition_id = p_cs_Rec.cs_definition_id
and cs_name = p_cs_name
and attribute_1 = p_instance_id
and attribute_49 = '2';
insert into msd_st_cs_data (
CS_ST_DATA_ID,
CS_DEFINITION_ID,
CS_NAME,
ATTRIBUTE_1,
ATTRIBUTE_2,
ATTRIBUTE_3,
ATTRIBUTE_6,
ATTRIBUTE_7,
ATTRIBUTE_10,
ATTRIBUTE_11,
ATTRIBUTE_34,
ATTRIBUTE_41,
ATTRIBUTE_43,
ATTRIBUTE_50,
ATTRIBUTE_51,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
select msd_st_cs_data_s.nextval,
to_char(p_cs_rec.cs_definition_id),
'SINGLE_STREAM',
to_char(inv.sr_instance_id),
inv.prd_level_id,
inv.prd_sr_level_pk,
inv.geo_level_id,
inv.geo_sr_level_pk,
inv.org_level_id,
inv.org_sr_level_pk,
inv.time_level_id,
to_char(inv.quantity),
to_char(sysdate, 'YYYY/MM/DD'),
inv.dcs_level_id,
inv.dcs_sr_level_pk,
to_char(sysdate),
to_char(fnd_global.user_id),
to_char(sysdate),
to_char(fnd_global.user_id),
to_char(fnd_global.login_id)
from msd_curr_onhand_inventory_v inv
where inv.sr_instance_id = p_instance_id;
insert erroneous row in staging table with Status "Error"
*/
ins_row_staging(crec_data, p_cs_rec, p_cs_name,
nvl(p_instance_id, crec_data.instance),
C_LOG_ERROR,
p_error_message);
/* i.e. Data is Pulled from Staging to Fact. Then update the staging row
with Status 'invalid'
*/
upd_stage_error(crec_data.pk_id, C_LOG_ERROR, p_error_message);
update msd_st_cs_data
set
error_desc = p_error_mesg,
process_status = p_process_status
where cs_st_data_id = p_pk_id;
/* i.e. Data is Pulled from Staging to Fact. Then update the staging row
with Status PROCESSED
*/
upd_stage_error(crec_data.pk_id, C_LOG_PROCESSED, null);
insert into msd_st_cs_data
(cs_st_data_id, cs_definition_id, cs_name,
attribute_1, attribute_2, attribute_3, attribute_4,
attribute_5, attribute_6, attribute_7, attribute_8, attribute_9,
attribute_10, attribute_11, attribute_12, attribute_13,
attribute_14, attribute_15, attribute_16, attribute_17,
attribute_18, attribute_19, attribute_20, attribute_21, attribute_22,
attribute_23, attribute_24, attribute_25, attribute_26, attribute_27,
attribute_28, attribute_29, attribute_30, attribute_31,
attribute_32, attribute_33, attribute_34, attribute_35, attribute_36,
attribute_37, attribute_38, attribute_39, attribute_40,
attribute_41, attribute_42, attribute_43, attribute_44, attribute_45,
attribute_46, attribute_47, attribute_48, attribute_49,
attribute_50, attribute_51, attribute_52, attribute_53, attribute_54,
attribute_55, attribute_56, attribute_57, attribute_58, attribute_59,
attribute_60,
process_status, error_desc,
created_by, creation_date, last_update_date, last_updated_by, last_update_login
)
values
/* Fix for designator name crec_data.designator instead of p_cs_name */
(msd_st_cs_data_s.nextval, p_cs_rec.cs_definition_id, crec_data.designator,
p_instance_id,
crec_data.prd_level_id, crec_data.prd_sr_level_value_pk, crec_data.prd_level_value, crec_data.prd_level_value_pk,
crec_data.geo_level_id, crec_data.geo_sr_level_value_pk, crec_data.geo_level_value, crec_data.geo_level_value_pk,
crec_data.org_level_id, crec_data.org_sr_level_value_pk, crec_data.org_level_value, crec_data.org_level_value_pk,
crec_data.prd_parent_level_id, crec_data.prd_parent_sr_level_value_pk,
crec_data.prd_parent_level_value, crec_data.prd_parent_level_value_pk,
crec_data.rep_level_id, crec_data.rep_sr_level_value_pk, crec_data.rep_level_value, crec_data.rep_level_value_pk,
crec_data.chn_level_id, crec_data.chn_sr_level_value_pk, crec_data.chn_level_value, crec_data.chn_level_value_pk,
crec_data.ud1_level_id, crec_data.ud1_sr_level_value_pk, crec_data.ud1_level_value, crec_data.ud1_level_value_pk,
crec_data.ud2_level_id, crec_data.ud2_sr_level_value_pk, crec_data.ud2_level_value, crec_data.ud2_level_value_pk,
crec_data.tim_level_id, crec_data.attribute_35, crec_data.attribute_36, crec_data.attribute_37,
crec_data.attribute_38, crec_data.attribute_39, crec_data.attribute_40, crec_data.attribute_41,
crec_data.attribute_42, crec_data.attribute_43, crec_data.attribute_44, crec_data.attribute_45,
crec_data.attribute_46, crec_data.attribute_47, crec_data.attribute_48, crec_data.attribute_49,
crec_data.dcs_level_id, crec_data.dcs_sr_level_value_pk, crec_data.dcs_level_value, crec_data.dcs_level_value_pk,
crec_data.attribute_54, crec_data.attribute_55, crec_data.attribute_56, crec_data.attribute_57,
crec_data.attribute_58, crec_data.attribute_59, crec_data.attribute_60,
p_process_status, p_error_message,
fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.login_id);
insert into msd_cs_data
(cs_data_id, cs_definition_id, cs_name,
attribute_1, attribute_2, attribute_3, attribute_4,
attribute_5, attribute_6, attribute_7, attribute_8, attribute_9,
attribute_10, attribute_11, attribute_12, attribute_13,
attribute_14, attribute_15, attribute_16, attribute_17,
attribute_18, attribute_19, attribute_20, attribute_21, attribute_22,
attribute_23, attribute_24, attribute_25, attribute_26, attribute_27,
attribute_28, attribute_29, attribute_30, attribute_31,
attribute_32, attribute_33, attribute_34, attribute_35, attribute_36,
attribute_37, attribute_38, attribute_39, attribute_40,
attribute_41, attribute_42, attribute_43, attribute_44, attribute_45,
attribute_46, attribute_47, attribute_48, attribute_49,
attribute_50, attribute_51, attribute_52, attribute_53, attribute_54,
attribute_55, attribute_56, attribute_57, attribute_58, attribute_59,
attribute_60,
created_by, creation_date, last_update_date, last_updated_by,last_update_login,
created_by_refresh_num, last_refresh_num, action_code)
values
/* Fix for designator name crec_data.designator instead of p_cs_name */
(msd_cs_data_s.nextval, p_cs_rec.cs_definition_id, crec_data.designator ,
p_instance_id,
crec_data.prd_level_id, crec_data.prd_sr_level_value_pk, crec_data.prd_level_value, crec_data.prd_level_value_pk,
crec_data.geo_level_id, crec_data.geo_sr_level_value_pk, crec_data.geo_level_value, crec_data.geo_level_value_pk,
crec_data.org_level_id, crec_data.org_sr_level_value_pk, crec_data.org_level_value, crec_data.org_level_value_pk,
crec_data.prd_parent_level_id, crec_data.prd_parent_sr_level_value_pk,
crec_data.prd_parent_level_value, crec_data.prd_parent_level_value_pk,
crec_data.rep_level_id, crec_data.rep_sr_level_value_pk, crec_data.rep_level_value, crec_data.rep_level_value_pk,
crec_data.chn_level_id, crec_data.chn_sr_level_value_pk, crec_data.chn_level_value, crec_data.chn_level_value_pk,
crec_data.ud1_level_id, crec_data.ud1_sr_level_value_pk, crec_data.ud1_level_value, crec_data.ud1_level_value_pk,
crec_data.ud2_level_id, crec_data.ud2_sr_level_value_pk, crec_data.ud2_level_value, crec_data.ud2_level_value_pk,
crec_data.tim_level_id, crec_data.attribute_35, crec_data.attribute_36, crec_data.attribute_37,
crec_data.attribute_38, crec_data.attribute_39, crec_data.attribute_40, crec_data.attribute_41,
crec_data.attribute_42, crec_data.attribute_43, crec_data.attribute_44, crec_data.attribute_45,
crec_data.attribute_46, crec_data.attribute_47, crec_data.attribute_48, crec_data.attribute_49,
crec_data.dcs_level_id, crec_data.dcs_sr_level_value_pk, crec_data.dcs_level_value, crec_data.dcs_level_value_pk,
crec_data.attribute_54, crec_data.attribute_55, crec_data.attribute_56, crec_data.attribute_57,
crec_data.attribute_58, crec_data.attribute_59, crec_data.attribute_60,
fnd_global.user_id, sysdate, sysdate, fnd_global.user_id,fnd_global.login_id,
p_new_refresh_num, p_new_refresh_num, 'I');
This Procedure inserts data in staging table from source view
without performing any validation.
*/
l_ins_stmt := Build_SQL_INS_AS_SELECT(
p_cs_definition_id => p_cs_rec.cs_definition_id,
p_instance_id => p_instance_id,
p_cs_name => p_cs_name,
p_source_view => p_source_view,
p_db_link => p_db_link);
l_sql_stmt := 'Select cs_st_data_id PK_ID, ' || l_sql_stmt || ' from ' || p_source_view ;
l_sql_stmt := 'Select null pk_id, ' || l_sql_stmt || ' from ' || p_source_view || p_db_link;
Function Build_SQL_INS_AS_SELECT(
p_cs_definition_id in number,
p_instance_id in varchar2,
p_cs_name in varchar2,
p_source_view in varchar2,
p_db_link in varchar2) return varchar2 is
l_sql_stmt varchar2(32767);
debug_line('In Build_SQL_INS_AS_SELECT');
/* DWK Move cs_name from top to at the bottom of insert statement since
l_sql_stmt will have forecast_designator inside. */
l_sql_stmt := 'Insert into MSD_ST_CS_DATA (cs_st_data_id , cs_definition_id, ' ||
'attribute_1, attribute_2, attribute_3, attribute_4, attribute_5, ' ||
'attribute_6, attribute_7, attribute_8, attribute_9, attribute_10,' ||
'attribute_11, attribute_12, attribute_13, attribute_14, attribute_15, ' ||
'attribute_16, attribute_17, attribute_18, attribute_19, attribute_20, ' ||
'attribute_21, attribute_22, attribute_23, attribute_24, attribute_25, ' ||
'attribute_26, attribute_27, attribute_28, attribute_29, attribute_30,' ||
'attribute_31, attribute_32, attribute_33, attribute_34, attribute_35, ' ||
'attribute_36, attribute_37, attribute_38, attribute_39, attribute_40,' ||
'attribute_41, attribute_42, attribute_43, attribute_44, attribute_45, ' ||
'attribute_46, attribute_47, attribute_48, attribute_49, attribute_50, ' ||
'attribute_51, attribute_52, attribute_53, attribute_54, attribute_55, ' ||
'attribute_56, attribute_57, attribute_58, attribute_59, attribute_60,' ||
'cs_name ) ' || ' select ' || 'msd_st_cs_Data_s.nextval, ' || p_cs_definition_id ||
', ' || l_sql_stmt || ' from ' || p_source_view || p_db_link;
select * from msd_cs_defn_column_dtls_v
where cs_definition_id = p_cs_definition_id;
SELECT multiple_stream_flag FROM msd_cs_definitions
WHERE cs_definition_id = p_cs_definition_id;
select source_view_column_name
from msd_cs_defn_column_dtls_v
where cs_definition_id = p_cs_definition_id and
table_column = 'CS_NAME';
/* p_source_or_stage = 0 menas build select for source view,
p_source_or_stage = non 0 menas build select for staging */
/* Initialize array */
l_struct := l_type_sql_struct_array(null);
/* Fetch source column name from the mappings table and update the array */
for c1_rec in c1 loop
for i IN 1..61 loop
if l_struct(i).tabcol_name = c1_rec.table_column then
if c1_rec.identifier_type = 'INSTANCE' then
if p_instance_id is not null then
l_struct(i).srccol_name := '''' || p_instance_id || '''';
select
decode(l_prd_found, g_level_pk_not_found, 'PRD ', null) ||
/* DWK Check level pk of parent item for dependent demand data */
decode(l_prd_parent_found, g_level_pk_not_found, 'PRD_PARENT ', null) ||
decode(l_org_found, g_level_pk_not_found, 'ORG ', null) ||
decode(l_geo_found, g_level_pk_not_found, 'GEO ', null) ||
decode(l_chn_found, g_level_pk_not_found, 'CHN ', null) ||
decode(l_rep_found, g_level_pk_not_found, 'REP ', null) ||
decode(l_ud1_found, g_level_pk_not_found, 'UD1 ', null) ||
decode(l_ud2_found, g_level_pk_not_found, 'UD2 ', null) ||
decode(l_dcs_found, g_level_pk_not_found, 'DCS ', null)
into
l_comments2
from
dual;
Select
decode(crec_data.prd_level_id,
null, decode(p_cs_rec.prd_level_collect_flag,
'Y', 'PRD ',
null),
p_cs_rec.prd_level_id, null,
'PRD ') ||
/* DWK IF dependent demand data are collected, its parents level id should be 1 */
decode(nvl(crec_data.prd_parent_level_id, '1'), '1', null, 'PRD_PARENT ') ||
decode(crec_data.org_level_id,
null, decode(p_cs_rec.org_level_collect_flag,
'Y', 'ORG ',
null),
p_cs_rec.org_level_id, null,
'ORG ') ||
decode(crec_data.geo_level_id,
null, decode(p_cs_rec.geo_level_collect_flag,
'Y', 'GEO ',
null),
p_cs_rec.geo_level_id, null,
'GEO ') ||
decode(crec_data.rep_level_id,
null, decode(p_cs_rec.rep_level_collect_flag,
'Y', 'REP ',
null),
p_cs_rec.rep_level_id, null,
'REP ') ||
decode(crec_data.chn_level_id,
null, decode(p_cs_rec.chn_level_collect_flag,
'Y', 'CHN ',
null),
p_cs_rec.chn_level_id, null,
'CHN ') ||
decode(crec_data.ud1_level_id,
null, decode(p_cs_rec.ud1_level_collect_flag,
'Y', 'UD1 ',
null),
p_cs_rec.ud1_level_id, null,
'UD1 ') ||
decode(crec_data.ud2_level_id,
null, decode(p_cs_rec.ud2_level_collect_flag,
'Y', 'UD2 ',
null),
p_cs_rec.ud2_level_id, null,
'UD2 ') ||
decode(crec_data.tim_level_id,
null, decode(p_cs_rec.tim_level_collect_flag,
'Y', 'TIM ',
null),
p_cs_rec.tim_level_id, null,
'TIM ') ||
decode(crec_data.dcs_level_id,
null, decode(p_cs_rec.dcs_level_collect_flag,
'Y', 'DCS ',
null),
p_cs_rec.dcs_level_id, null,
'DCS ' )
into
l_comments1
from dual;
select count(*) into l_count
from fnd_lookup_values
where lookup_type = 'MSD_PERIOD_TYPE' and
nvl(crec_data.tim_level_id, '999.99') = lookup_code and
rownum <= 1;
select 'TIM' into l_comments1 from dual;
select decode(l_comments2, null, null, 'MSD_CS_DATALOAD_INVALID_DIM : ' || l_comments2) ||
decode(l_comments1, null, null, 'MSD_CS_DATALOAD_INVALID_LVLID : ' || l_comments1)
into p_err_mesg
from dual;
select to_date(crec_data.attribute_43, 'YYYY/MM/DD')
into l_dummy_date
from dual;
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_value_pk;
select level_pk
from
msd_level_values
where
instance = p_instance and
level_id = p_level_id and
level_value = p_level_value;
select sr_level_pk, level_value
from
msd_level_values
where
instance = p_instance and
level_id = p_level_id and
level_pk = p_level_value_pk;
select count(1) into l_count from msd_cs_coll_parameters
where conc_request_id = p_request_id and
parameter_number = p_para_num;
' (SELECT parameter_code FROM msd_cs_coll_parameters ' ||
' WHERE conc_request_id = ' || p_request_id ||
' AND parameter_number = ' || p_para_num || ' ) ' ||
substr(p_where_cond, end_pos + 1);
select distinct attribute_1 instance, cs_name
from msd_st_cs_data
where cs_definition_id = p_cs_definition_id and
cs_name = nvl(p_cs_name, cs_name);
select distinct attribute_1 instance
from msd_st_cs_data
where cs_definition_id = p_cs_definition_id;
select nvl(multiple_stream_flag,'N')
from msd_cs_definitions
where cs_definition_id = p_cs_definition_id;
delete from msd_cs_data where cs_definition_id = p_cs_definition_id
and cs_name = nvl(p_cs_name, cs_name) and attribute_1 = nvl(p_instance_id, attribute_1);
delete from msd_st_cs_data where cs_definition_id = p_cs_definition_id
and cs_name = nvl(p_cs_name, cs_name) and attribute_1 = nvl(p_instance_id, attribute_1);
UPDATE msd_cs_data
SET Action_code = 'D',
last_refresh_num = p_new_refresh_num
WHERE cs_definition_id = p_cs_definition_id and
cs_name = l_rec.cs_name and
attribute_1 = l_rec.instance and
action_code = 'I';
ELSE /* For single stream, ignore the cs_name in delete stmt */
For l_rec IN c_get_del_crit_single LOOP
UPDATE msd_cs_data
SET Action_code = 'D',
last_refresh_num = p_new_refresh_num
WHERE cs_definition_id = p_cs_definition_id and
attribute_1 = l_rec.instance and
action_code = 'I';
/* Delete data from staging table to avoid double couting when user runs
collection source to stage without complete refresh checkbox checked
This will make custom stream collection behaviour same as other
collection (Bookking/Shipment)
*/
IF p_process_type = C_SOURCE_TO_STAGE then
delete from msd_st_cs_data
where cs_definition_id = p_cs_definition_id and
cs_name = nvl(p_cs_name, cs_name) and
attribute_1 = nvl(p_instance_id, attribute_1);
/* Insert designator into headers talbe when designator get modified. */
IF ( l_rec.designator <> nvl(l_temp_designator,'-99999999~!@') OR
nvl(p_instance_id,l_rec.instance) <> nvl(l_temp_instance_id,-99999999) ) THEN
l_temp_designator := l_rec.designator;
/* DWK Populate MSD_CS_DATA_HEADERS table after inserting rows
into FACT table */
insert_update_Into_Headers ( p_cs_rec.cs_definition_id,
l_rec.designator,
nvl(p_instance_id,l_rec.instance), p_new_refresh_num);
SELECT mod( l_counter, C_BATCH_SIZE)
INTO l_commit_flag
FROM dual;
select source_view_column_name
from msd_cs_defn_column_dtls
where
cs_definition_id = p_cs_rec.cs_definition_id and
table_column = 'CS_NAME';
PROCEDURE Insert_update_Into_Headers
This procedure will decide whether insert cs_definition_id, cs_name, and instance into
msd_cs_data_headers table or not and insert row if necessary.
**************************************************************************************************/
Procedure insert_update_Into_Headers ( p_cs_definition_id in number,
p_cs_name in varchar2,
p_instance_id in number,
p_refresh_num in number) is
p_count NUMBER:=0;
SELECT count(*) INTO p_count FROM msd_cs_data_headers_v1
WHERE instance = p_instance_id AND
cs_definition_id = p_cs_definition_id AND
cs_name = p_cs_name;
Insert_Data_Into_Headers (p_cs_definition_id,
p_cs_name,
p_instance_id,
p_refresh_num);
update msd_cs_data_headers
set last_refresh_num = p_refresh_num
where cs_definition_id = p_cs_definition_id
and instance = p_instance_id
and cs_name = p_cs_name;
END insert_update_Into_Headers;
PROCEDURE Insert_Data_Into_Headers
This procedure will insert cs_definition_id, cs_name, and instance into
msd_cs_data_headers table.
**************************************************************************************************/
Procedure Insert_Data_Into_Headers ( p_cs_definition_id in number,
p_cs_name in varchar2,
p_instance_id in number,
p_refresh_num in number) is
BEGIN
INSERT INTO msd_cs_data_headers
( CS_DATA_HEADER_ID,
INSTANCE,
CS_DEFINITION_ID,
CS_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_REFRESH_NUM
)
VALUES ( msd_cs_data_headers_s.nextval,
p_instance_id,
p_cs_definition_id,
p_cs_name,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
p_refresh_num
);
show_line('Error in inserting into MSD_CS_DATA_HEADERS');
END Insert_Data_Into_Headers;