CREATE OR REPLACE
PACKAGE XXDM_FORECAST_TO_ASCP_PKG
IS
/*---------------------------------------------------------
Current Version : 1.00
Create By :
Create Date : 2009-02-28
Purpose : push Demantra data to ASCP.
Logicality :
---------------------------------------------------------*/
C_USERNAME CONSTANT VARCHAR2 (10) := 'DM';
C_TABLELABEL_1 CONSTANT VARCHAR2 (50) := 'PART NUMBER (SP)';
C_TABLELABEL_2 CONSTANT VARCHAR2 (50) := 'RO ORG';
C_COMPUTED_TITLE_1 CONSTANT VARCHAR2 (50) := 'DEMAND PRIORITY';
C_COMPUTED_TITLE_2 CONSTANT VARCHAR2 (50) := 'FINAL FORECAST';
C_COMPUTED_TITLE_3 CONSTANT VARCHAR2 (50) := 'ITEM DESTINATION KEY';
C_COMPUTED_TITLE_4 CONSTANT VARCHAR2 (50)
:= 'ORGANIZATION DESTINATION KEY';
C_COMPUTED_TITLE_5 CONSTANT VARCHAR2 (50) := 'LTB 1 YEAR AGG';
C_COMPUTED_TITLE_6 CONSTANT VARCHAR2 (50) := 'LTB 3 YEAR AGG';
C_MONTHS_IRP CONSTANT NUMBER := 6;
C_MONTHS_ROLLING CONSTANT NUMBER := 6;
C_MONTHS_LTB CONSTANT NUMBER := 38; --36+2 Sean: Do EOL notice before 2 months
C_SUCC_FLAG CONSTANT VARCHAR2 (10) := 'SUCC';
g_err_position VARCHAR2 (10);
g_err_desc VARCHAR2 (400);
g_sql VARCHAR2 (8000);
g_ltb_b_exist BOOLEAN;
g_additional_filter VARCHAR2 (400);
g_user_id NUMBER;
g_max_salles_date DATE;
g_min_forcast_date DATE;
g_eol_date_c VARCHAR2(20); --Sean
g_eol_date_format VARCHAR2(20) := 'YYYY/MM/DD'; --Sean
PROCEDURE xxdm_upload_forecast_to_ascp
( p_level_id IN VARCHAR2, p_member_id IN VARCHAR2);
PROCEDURE xxdm_upload_fc_by_noticeid
( p_level_id IN VARCHAR2,
p_member_id IN VARCHAR2,
p_notice_id IN VARCHAR2,
p_ro IN VARCHAR2,
p_ppp IN VARCHAR2,
p_ppp_new IN VARCHAR2,
p_user_id IN NUMBER,
p_additional_filter IN VARCHAR2,
p_max_salles_date IN DATE);
PROCEDURE xxdm_insert_init_data (p_notice_id IN VARCHAR2,
p_ro IN VARCHAR2,
p_ppp IN VARCHAR2,
p_user_id IN NUMBER,
p_transfer_id OUT VARCHAR2,
p_transfer_q_id OUT VARCHAR2);
PROCEDURE xxdm_create_int_comb_view
( p_ro in VARCHAR2,
p_view_name in VARCHAR2,
p_additional_filter in VARCHAR2);
PROCEDURE xxdm_create_int_date_table (p_table_name IN VARCHAR2);
PROCEDURE xxdm_insert_int_date_table (p_ppp IN VARCHAR2,p_table_name IN VARCHAR2);
PROCEDURE xxdm_create_export_view (p_notice_id IN VARCHAR2,
p_ro IN VARCHAR2,
p_ppp_new IN VARCHAR2,
p_int_comb_viewname IN VARCHAR2,
p_int_date_tablename IN VARCHAR2,
p_additional_filter IN VARCHAR2,
p_max_salles_date IN DATE);
FUNCTION xxdm_check_ltb_b_exist(p_additional_filter VARCHAR2) RETURN BOOLEAN;
END;
/
CREATE OR REPLACE
PACKAGE BODY XXDM_FORECAST_TO_ASCP_PKG
IS
/*------------------------------------------------------------------------------
Created by
Function:
Create view for tranfering demantra data to ASCP.
Logicality:
this procedure use to create view
Input: p_level_id -- demantra data model items level id
p_member_id -- demantra data model items member id
Output:
null
------------------------------------------------------------------------------*/
PROCEDURE xxdm_upload_forecast_to_ascp
( p_level_id IN VARCHAR2, p_member_id IN VARCHAR2)
IS
v_level_id NUMBER;
v_member_id NUMBER;
v_transfer_id NUMBER;
v_transfer_q_id NUMBER;
v_count NUMBER;
v_notice_id VARCHAR2 (100);
v_sysdate DATE;
v_id_field VARCHAR2 (100);
v_yyyy_mm_dd VARCHAR2 (10);
v_product_line VARCHAR2 (10);
v_ro VARCHAR2 (10);
v_ppp VARCHAR2 (10);
v_ppp_new VARCHAR2 (10);
v_tempname VARCHAR2 (100);
v_left_notice_id VARCHAR2 (100);
v_int_comb_viewname VARCHAR2 (100);
v_int_date_tablename VARCHAR2 (100);
BEGIN
apps.xxascp_util_pkg.dbms_output_lines('p_level_id='||p_level_id); --for test
apps.xxascp_util_pkg.dbms_output_lines('p_member_id='||p_member_id); --for test
--check parameter is correct
g_err_position:='E0001-01';
g_err_desc:='level_id and member_id must be number type. level_id='||p_level_id||',member_id='||p_member_id;
v_level_id:=TO_NUMBER(p_level_id);
v_member_id:=TO_NUMBER(p_member_id);
--get additional filter
g_err_position:='E0001-02';
g_err_desc:='error occurs in invoking function [xxdm_util_pkg.xxdm_Get_Additional_Filter]';
g_additional_filter :=
xxdm_util_pkg.xxdm_get_additional_filter ('mdp_matrix',
v_level_id,
v_member_id
);
apps.xxascp_util_pkg.dbms_output_lines('g_additional_filter=' || g_additional_filter);
--check LTB-B exist
g_err_position:='E0001-03';
g_err_desc:='error occurs in invoking function [xxdm_check_ltb_b_exist]';
g_ltb_b_exist := xxdm_check_ltb_b_exist(g_additional_filter);
--get user_id by user_name
g_err_position:='E0001-04';
g_err_desc:='data not exist in table [user_id],column UPPER(user_name)='||C_USERNAME;
SELECT user_id
INTO g_user_id
FROM demantra.user_id
WHERE UPPER(user_name) = C_USERNAME;
apps.xxascp_util_pkg.dbms_output_lines('g_user_id='||g_user_id); --for test
g_err_position:='E0001-05';
g_err_desc:='parameter max_sales_date not exits.';
SELECT TO_DATE (s.pval, d.pval)
INTO g_max_salles_date
FROM db_params d, sys_params s
WHERE d.pname = 'nls_date_format' AND s.pname = 'max_sales_date';
g_err_position:='E0001-06';
g_err_desc:='parameter min_fore_sales_date not exits.';
SELECT TO_DATE (s.pval, d.pval)
INTO g_min_forcast_date
FROM demantra.db_params d, demantra.sys_params s
WHERE d.pname = 'nls_date_format' AND s.pname = 'min_fore_sales_date';
apps.xxascp_util_pkg.dbms_output_lines('g_max_salles_date=' || g_max_salles_date);
apps.xxascp_util_pkg.dbms_output_lines('g_min_forcast_date=' || g_min_forcast_date);
--get notice_id by level_id and member_id
g_err_position:='E0002';
g_err_desc:='error occurs in invoking function [xxdm_util_pkg.xxdm_get_notice_id] when getting notice_id,parameter level_id='||p_level_id||',member_id='||p_member_id;
v_notice_id := demantra.xxdm_util_pkg.xxdm_get_notice_id (v_level_id, v_member_id);
apps.xxascp_util_pkg.dbms_output_lines('v_notice_id=' || v_notice_id); --for test
--v_notice_id :='20090525-PRJ-BQP-LTB-006'; -- for test
--g_ltb_b_exist:=TRUE; -- for test
IF g_ltb_b_exist THEN
g_err_position:='E0001-07';
g_err_desc:='eol_date is null in table [t_ro_ltb].t_ro_ltb_desc='||v_notice_id;
SELECT TO_CHAR (TRUNC (eol_date, 'MM'), g_eol_date_format)
INTO g_eol_date_c
FROM demantra.t_ro_ltb
WHERE t_ro_ltb_desc = v_notice_id;
apps.xxascp_util_pkg.dbms_output_lines('g_eol_date_c=' || g_eol_date_c); --for test
END IF;
--get yyyy-mm-dd of notice id
g_err_position:='E0002-01';
g_err_desc:='error occurs when get yyyy-mm-dd of notice id ';
v_left_notice_id:=v_notice_id;
v_yyyy_mm_dd:=demantra.xxdm_util_pkg.xxdm_get_substr_before_split(v_left_notice_id,'-');
v_left_notice_id:=demantra.xxdm_util_pkg.xxdm_get_substr_after_split(v_left_notice_id,'-');
apps.xxascp_util_pkg.dbms_output_lines('v_yyyy_mm_dd=' || v_yyyy_mm_dd); -- for test
--get product line of notice id
g_err_position:='E0002-02';
g_err_desc:='error occurs when get product line of notice id ';
v_product_line:=demantra.xxdm_util_pkg.xxdm_get_substr_before_split(v_left_notice_id,'-');
v_left_notice_id:=demantra.xxdm_util_pkg.xxdm_get_substr_after_split(v_left_notice_id,'-');
apps.xxascp_util_pkg.dbms_output_lines('v_product_line=' || v_product_line); -- for test
--get ro of notice id
g_err_position:='E0002-03';
g_err_desc:='error occurs when get ro of notice id ';
v_ro:=UPPER(demantra.xxdm_util_pkg.xxdm_get_substr_before_split(v_left_notice_id,'-'));
v_left_notice_id:=demantra.xxdm_util_pkg.xxdm_get_substr_after_split(v_left_notice_id,'-');
apps.xxascp_util_pkg.dbms_output_lines('v_ro=' || v_ro); -- for test
--get ppp of notice id
g_err_position:='E0002-04';
g_err_desc:='error occurs when get ppp of notice id ';
v_ppp:=UPPER(demantra.xxdm_util_pkg.xxdm_get_substr_before_split(v_left_notice_id,'-'));
v_left_notice_id:=demantra.xxdm_util_pkg.xxdm_get_substr_after_split(v_left_notice_id,'-');
apps.xxascp_util_pkg.dbms_output_lines('v_ppp=' || v_ppp); -- for test
v_ppp_new := v_ppp;
apps.xxascp_util_pkg.dbms_output_lines('v_left_notice_id=' || v_left_notice_id); -- for test
IF g_ltb_b_exist THEN
--execute notice id of LB1
v_ppp_new :='LB1';
v_notice_id := v_yyyy_mm_dd || '_' || v_product_line || '_' || v_ro || '_' || v_ppp_new || '_' || v_left_notice_id;
apps.xxascp_util_pkg.dbms_output_lines('v_notice_id=' || v_notice_id); -- for test
xxdm_upload_fc_by_noticeid(v_level_id,v_member_id,v_notice_id,v_ro,v_ppp,v_ppp_new,g_user_id,g_additional_filter,g_max_salles_date); --upload forcast by notice id
--execute notice id of LB3
v_ppp_new :='LB3';
v_notice_id := v_yyyy_mm_dd || '_' || v_product_line || '_' || v_ro || '_' || v_ppp_new || '_' || v_left_notice_id;
apps.xxascp_util_pkg.dbms_output_lines('v_notice_id=' || v_notice_id); -- for test
xxdm_upload_fc_by_noticeid(v_level_id,v_member_id,v_notice_id,v_ro,v_ppp,v_ppp_new,g_user_id,g_additional_filter,g_max_salles_date); --upload forcast by notice id
ELSE
v_notice_id := REPLACE (v_notice_id, '-', '_');
apps.xxascp_util_pkg.dbms_output_lines('v_notice_id=' || v_notice_id); -- for test
xxdm_upload_fc_by_noticeid(v_level_id,v_member_id,v_notice_id,v_ro,v_ppp,v_ppp_new,g_user_id,g_additional_filter,g_max_salles_date); --upload forcast by notice id
END IF;
g_err_position:=C_SUCC_FLAG;
g_err_desc:='program end.';
apps.xxascp_util_pkg.dbms_output_lines('g_err_position=' || g_err_position); -- for test
apps.xxascp_util_pkg.dbms_output_lines('g_err_desc=' || g_err_desc); -- for test */
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
g_err_desc := g_err_desc || 'SQLERRM:' || substr(SQLERRM,1,3000);
apps.xxascp_util_pkg.dbms_output_lines('g_err_position=' || g_err_position); -- for test
apps.xxascp_util_pkg.dbms_output_lines('g_err_desc=' || g_err_desc); -- for test
apps.xxascp_util_pkg.insert_log('Demantra',NULL,NULL,NULL,g_err_position,
g_err_desc,0,NULL,NULL,SYSDATE,
NULL,NULL,NULL,NULL,NULL);
END;
/*------------------------------------------------------------------------------
Created by
Function:
Create view for tranfering demantra data to ASCP.
Logicality:
this procedure use to create view
Input: p_level_id -- demantra data model items level id
p_member_id -- demantra data model items member id
p_notice_id -- notice id
p_ro -- ro in notice id
p_ppp -- ppp in notice id
p_ppp_new -- replace ppp with LB1 or LB3 in notice id
p_user_id -- user id
p_additional_filter -- addtional filter
p_max_salles_date -- max sales date
Output:
null
------------------------------------------------------------------------------*/
PROCEDURE xxdm_upload_fc_by_noticeid
( p_level_id IN VARCHAR2,
p_member_id IN VARCHAR2,
p_notice_id IN VARCHAR2,
p_ro IN VARCHAR2,
p_ppp IN VARCHAR2,
p_ppp_new IN VARCHAR2,
p_user_id IN NUMBER,
p_additional_filter IN VARCHAR2,
p_max_salles_date IN DATE)
IS
v_transfer_id NUMBER;
v_transfer_q_id NUMBER;
v_count NUMBER;
v_sysdate DATE;
v_id_field VARCHAR2 (100);
v_yyyy_mm_dd VARCHAR2 (10);
v_product_line VARCHAR2 (10);
v_tempname VARCHAR2 (100);
v_left_notice_id VARCHAR2 (100);
v_int_comb_viewname VARCHAR2 (100);
v_int_date_tablename VARCHAR2 (100);
--Add by ken bu 20090616
v_error_code NUMBER;
v_error_msg VARCHAR2(200);
BEGIN
SELECT count(*)
INTO v_count
FROM demantra.transfer_list
WHERE NAME = p_notice_id;
g_err_position:='E0004';
g_err_desc:='E0004';
IF NOT v_count > 0
THEN
-- begin: INSERT DATA and get v_transfer_id and v_transfer_q_id
xxdm_insert_init_data(p_notice_id,p_ro,p_ppp,p_user_id,v_transfer_id,v_transfer_q_id);
-- end: INSERT DATA
ELSE
SELECT ID
INTO v_transfer_id
FROM demantra.transfer_list
WHERE NAME = p_notice_id;
g_err_position:='E0021-000';
g_err_desc:='data not exist in table [transfer_query],column transfer_id='||v_transfer_id;
SELECT MAX(ID)
INTO v_transfer_q_id
FROM transfer_query
WHERE transfer_id = v_transfer_id;
g_err_position:='E0021-001';
g_err_desc:='error ocurrs when update transfer_query collumn query_name.';
UPDATE transfer_query
SET query_name = REPLACE (p_notice_id, '_', '-')
WHERE ID = v_transfer_q_id;
commit;
END IF;
apps.xxascp_util_pkg.dbms_output_lines('v_transfer_id=' || v_transfer_id);
apps.xxascp_util_pkg.dbms_output_lines('v_transfer_q_id=' || v_transfer_q_id);
--begin: Create view int_comb_
g_err_position:='E0024';
g_err_desc:='E0024';
v_int_comb_viewname :=
'INT_COMB_' || v_transfer_q_id || '_' || p_user_id;
xxdm_create_int_comb_view(p_ro,v_int_comb_viewname,p_additional_filter);
--end: Create view int_comb_
--begin: Create table int_date_
g_err_position:='E0028';
g_err_desc:='E0028';
v_int_date_tablename :=
'INT_DATE_' || v_transfer_q_id || '_' || p_user_id;
apps.xxascp_util_pkg.dbms_output_lines('v_int_date_tablename=' || v_int_date_tablename);
xxdm_create_int_date_table(v_int_date_tablename);
--end: Create table int_date_
--begin: insert into int_date_ table
xxdm_insert_int_date_table(p_ppp,v_int_date_tablename);
--end: insert into int_date_ table
--begin: Create export view
xxdm_create_export_view(p_notice_id,p_ro,p_ppp_new,v_int_comb_viewname,v_int_date_tablename,
p_additional_filter,p_max_salles_date);
--end: Create export view
g_err_position:='E0040';
g_err_desc:='error occurs when calling r12 function[apps.msd_dem_upload_forecast.upload_forecast('||p_notice_id||')] upload forcast';
--call r12 function upload forcast
--apps.msd_dem_upload_fc_test.upload_forecast(p_notice_id); --for test
apps.msd_dem_upload_forecast.upload_forecast(REPLACE(p_notice_id, '_', '-'));
--Begin Add by ken bu 20090616
apps.xxascp_cns_item_pkg.insert_base_data
(
p_notice_id => p_notice_id,
o_error_code => v_error_code,
o_error_msg => v_error_msg
);
IF v_error_code = 2 THEN
g_err_position:='E0050';
g_err_desc:='error occurs when execute xxascp_cns_item_pkg:'||v_error_msg;
RAISE_APPLICATION_ERROR(-20001,g_err_desc );
ELSE
--End Add by ken bu 20090616
g_err_position:=C_SUCC_FLAG;
g_err_desc:='execute xxdm_upload_fc_by_noticeid successfully,notice id='||p_notice_id;
apps.xxascp_util_pkg.dbms_output_lines('g_err_position=' || g_err_position); -- for test
apps.xxascp_util_pkg.dbms_output_lines('g_err_desc=' || g_err_desc); -- for test
END IF;
-- remove the mark by Terry Lee on 2009/6/17 from Steven's request
--insert step log
xxdm_util_pkg.insert_step_log_common(p_notice_id,'O','','','','','006');
END;
/*---------------------------------------------------------
Created by
PROCEDURE:
insert init data
LOGICALITY:
<_____Optional_____Internal_Logic_Description>
INPUT:
p_notice_id notice_id
p_ppp ppp of notice id
p_user_id user id
OUTPUT:
p_transfer_id transfer_id
p_transfer_q_id transfer_q_id
---------------------------------------------------------*/
PROCEDURE xxdm_insert_init_data (p_notice_id IN VARCHAR2,
p_ro IN VARCHAR2,
p_ppp IN VARCHAR2,
p_user_id IN NUMBER,
p_transfer_id OUT VARCHAR2,
p_transfer_q_id OUT VARCHAR2
)
IS
v_transfer_id NUMBER;
v_transfer_q_id NUMBER;
v_level1_id NUMBER;
v_level2_id NUMBER;
v_series_id_1 NUMBER;
v_series_id_2 NUMBER;
v_series_id_3 NUMBER;
v_series_id_4 NUMBER;
v_series_id_5 NUMBER;
v_series_id_6 NUMBER;
BEGIN
--INSERT INTO transfer_list
g_err_position:='E0005';
g_err_desc:='error occurs in invoking function [get_seq_nextval],column transfer_id='||v_transfer_id;
demantra.get_seq_nextval ('TRANSFER_LIST',
'ID',
'TRANSFER_ID_SEQ',
v_transfer_id
);
apps.xxascp_util_pkg.dbms_output_lines('v_transfer_id=' || v_transfer_id); -- for test
g_err_position:='E0006';
g_err_desc:='error occurs when inserting data to table [transfer_list],column transfer_id='||v_transfer_id;
INSERT INTO demantra.transfer_list
(ID, NAME, description, user_id, use_external_schema
)
VALUES (v_transfer_id, p_notice_id, p_notice_id, p_user_id, 0
);
--INSERT INTO transfer_query
g_err_position:='E0007';
g_err_desc:='error occurs in invoking function [get_seq_nextval],column transfer_q_id='||v_transfer_q_id;
demantra.get_seq_nextval ('TRANSFER_QUERY',
'ID',
'TRANSFER_QUERY_SEQ',
v_transfer_q_id
);
apps.xxascp_util_pkg.dbms_output_lines('v_transfer_q_id=' || v_transfer_q_id); -- for test
g_err_position:='E0008';
g_err_desc:='error occurs when inserting data to table [transfer_query],column transfer_q_id='||v_transfer_q_id;
g_sql :=
'INSERT INTO demantra.transfer_query(id,transfer_id,presentation_type,view_name,table_name,
file_name,delimiter,is_fixed_width,view_type,use_real_proportion,
insertnewcombinations,insertforecasthorizon,query_name,description,time_res_id,
from_date,until_date,relative_date,relative_from_date,relative_until_date,
unit_id,index_id,data_scale,dm_id,schema_id,
query_id,integration_type,export_type,batch_file,import_from_file,
last_export_date,filter_sd_by_gl)
VALUES('
|| v_transfer_q_id
|| ','
|| v_transfer_id
|| ',1,''BIEO_'
|| p_notice_id
|| ''',''BIIO_'
|| p_notice_id
|| ''',
Null,Null,Null,0,0,
0,0,'''
|| replace(p_notice_id, '_', '-')
|| ''',Null,5,
SYSDATE,SYSDATE,1,1,1,
1,Null,1,Null,Null,
Null,2,1,Null,0,
Null,0)';
apps.xxascp_util_pkg.dbms_output_lines('g_sql=' || g_sql); -- for test
EXECUTE IMMEDIATE g_sql;
g_err_position:='E0009';
g_err_desc:='data not exist in table [group_tables],column UPPER(table_label)='||C_TABLELABEL_1;
SELECT group_table_id
INTO v_level1_id
FROM demantra.group_tables
WHERE UPPER(table_label) = C_TABLELABEL_1; --Part Number (SP)
g_err_position:='E0010';
g_err_desc:='data not exist in table [group_tables],column UPPER(table_label)='||C_TABLELABEL_2;
SELECT group_table_id
INTO v_level2_id
FROM demantra.group_tables
WHERE UPPER(table_label) = C_TABLELABEL_2; --RO Org
apps.xxascp_util_pkg.dbms_output_lines('v_level1_id=' || v_level1_id); -- for test
apps.xxascp_util_pkg.dbms_output_lines('v_level2_id=' || v_level2_id); -- for test
--Insert into transfer_query_levels
-- Level 1 Level_id of 'Part Number (SP)' --"Primary Item"
g_err_position:='E0011';
g_err_desc:='error occurs when insert data to table [transfer_query_levels],column [ID]='||v_transfer_q_id||',level_id='||v_level1_id;
INSERT INTO demantra.transfer_query_levels
(ID, level_id, lorder
)
VALUES (v_transfer_q_id, v_level1_id, 1
);
-- Level 2 Level_id of "RO Org"
g_err_position:='E0012';
g_err_desc:='error occurs when insert data to table [transfer_query_levels],column [ID]='||v_transfer_q_id||',level_id='||v_level2_id;
INSERT INTO demantra.transfer_query_levels
(ID, level_id, lorder
)
VALUES (v_transfer_q_id, v_level2_id, 2
);
--Get series_id
g_err_position:='E0013';
g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_1;
v_series_id_1:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_1); --Demand Priority
g_err_position:='E0014';
g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_2;
v_series_id_2:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_2); --Final Forecast
g_err_position:='E0015';
g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_3;
v_series_id_3:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_3); --Item Destination Key
g_err_position:='E0016';
g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_4;
v_series_id_4:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_4); --Organization Destination Key
g_err_position:='E0016-01';
g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_5;
v_series_id_5:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_5); --LTB 1 year Agg
g_err_position:='E0016-02';
g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_6;
v_series_id_6:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_6); --LTB 3 year Agg
apps.xxascp_util_pkg.dbms_output_lines('v_series_id_1=' || v_series_id_1);
apps.xxascp_util_pkg.dbms_output_lines('v_series_id_2=' || v_series_id_2);
apps.xxascp_util_pkg.dbms_output_lines('v_series_id_3=' || v_series_id_3);
apps.xxascp_util_pkg.dbms_output_lines('v_series_id_4=' || v_series_id_4);
apps.xxascp_util_pkg.dbms_output_lines('v_series_id_5=' || v_series_id_5);
apps.xxascp_util_pkg.dbms_output_lines('v_series_id_6=' || v_series_id_6);
--Insert into transfer_query_series
g_err_position:='E0017';
g_err_desc:='error occurs when insert data to table [transfer_query_series],column [ID]='||v_transfer_q_id||',series_id='||v_series_id_1;
INSERT INTO demantra.transfer_query_series
(ID, series_id, load_option, purge_option
)
VALUES (v_transfer_q_id, v_series_id_1, 0, 0
); -- series_id of "Demand Priority"
g_err_position:='E0018';
g_err_desc:='error occurs when insert data to table [transfer_query_series],column ID='||v_transfer_q_id||',series_id='||v_series_id_2;
INSERT INTO demantra.transfer_query_series
(ID, series_id, load_option, purge_option
)
VALUES (v_transfer_q_id, v_series_id_2, 0, 0
); -- series_id of "Final Forecast"
/*
IF g_ltb_b_exist THEN
g_err_position:='E0017-01';
g_err_desc:='error occurs when insert data to table [transfer_query_series],column [ID]='||v_transfer_q_id||',series_id='||v_series_id_5;
INSERT INTO demantra.transfer_query_series
(ID, series_id, load_option, purge_option
)
VALUES (v_transfer_q_id, v_series_id_5, 0, 0
); -- series_id of "LTB 1 year Agg"
g_err_position:='E0017-02';
g_err_desc:='error occurs when insert data to table [transfer_query_series],column [ID]='||v_transfer_q_id||',series_id='||v_series_id_6;
INSERT INTO demantra.transfer_query_series
(ID, series_id, load_option, purge_option
)
VALUES (v_transfer_q_id, v_series_id_6, 0, 0
); -- series_id of "LTB 3 year Agg"
ELSE
g_err_position:='E0018';
g_err_desc:='error occurs when insert data to table [transfer_query_series],column ID='||v_transfer_q_id||',series_id='||v_series_id_2;
INSERT INTO demantra.transfer_query_series
(ID, series_id, load_option, purge_option
)
VALUES (v_transfer_q_id, v_series_id_2, 0, 0
); -- series_id of "Final Forecast"
END IF;
*/
g_err_position:='E0019';
g_err_desc:='error occurs when insert data to table [transfer_query_series],column ID='||v_transfer_q_id||',series_id='||v_series_id_3;
INSERT INTO demantra.transfer_query_series
(ID, series_id, load_option, purge_option
)
VALUES (v_transfer_q_id, v_series_id_3, 0, 0
); -- series_id of "Item Destination Key"
g_err_position:='E0020';
g_err_desc:='error occurs when insert data to table [transfer_query_series],column ID='||v_transfer_q_id||',series_id='||v_series_id_4;
INSERT INTO demantra.transfer_query_series
(ID, series_id, load_option, purge_option
)
VALUES (v_transfer_q_id, v_series_id_4, 0, 0
); -- series_id of "Organization Destination Key"
p_transfer_id:=v_transfer_id;
p_transfer_q_id:=v_transfer_q_id;
COMMIT;
END;
/*---------------------------------------------------------
Created by
PROCEDURE:
Create init comb view
LOGICALITY:
<_____Optional_____Internal_Logic_Description>
INPUT:
p_ro ro value
p_view_name view name
p_additional_filter additional filter string
OUTPUT:
null
---------------------------------------------------------*/
/* Formatted on 2009/03/11 13:05 (Formatter Plus v4.8.7) */
PROCEDURE xxdm_create_int_comb_view (
p_ro IN VARCHAR2,
p_view_name IN VARCHAR2,
p_additional_filter IN VARCHAR2
)
IS
v_viewname VARCHAR2 (100);
BEGIN
v_viewname := 'demantra.' || p_view_name;
/*IF p_ro = 'TPC'
THEN
g_err_position := 'E0026';
g_err_desc :=
'TPC error occurs in create view,view name=' || v_viewname;
g_sql :=
'CREATE OR REPLACE VIEW '
|| v_viewname
|| ' AS
SELECT (nvl(rownum, 0)) AS comb_id,
t_comb_list.level1,
t_comb_list.level_id1
FROM (SELECT DISTINCT t_ep_item.item AS level1,
t_ep_item.t_ep_item_ep_id AS level_id1
FROM mdp_matrix, t_ep_item
WHERE mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
AND '
|| p_additional_filter
|| ') t_comb_list';
ELSE
g_err_position := 'E0027';
g_err_desc := 'error occurs in create view,view name=' || v_viewname;
g_sql :=
'CREATE OR REPLACE VIEW '
|| v_viewname
|| ' AS
SELECT (nvl(rownum, 0)) AS comb_id,
t_comb_list.level1,
t_comb_list.level_id1,
t_comb_list.level2,
t_comb_list.level_id2
FROM (SELECT DISTINCT t_ep_item.item AS level1,
t_ep_item.t_ep_item_ep_id AS level_id1,
t_ep_ro_org.ro_org AS level2,
t_ep_ro_org.t_ep_ro_org_ep_id AS level_id2
FROM mdp_matrix, t_ep_item, t_ep_ro_org
WHERE mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
AND mdp_matrix.t_ep_ro_org_ep_id = t_ep_ro_org.t_ep_ro_org_ep_id
AND '
|| p_additional_filter
|| ') t_comb_list';
END IF;*/
g_err_position := 'E0027';
g_err_desc := 'error occurs in create view,view name=' || v_viewname;
g_sql :=
'CREATE OR REPLACE VIEW '
|| v_viewname
|| ' AS
SELECT (nvl(rownum, 0)) AS comb_id,
t_comb_list.level1,
t_comb_list.level_id1,
t_comb_list.level2,
t_comb_list.level_id2
FROM (SELECT DISTINCT t_ep_item.item AS level1,
t_ep_item.t_ep_item_ep_id AS level_id1,
t_ep_ro_org.ro_org AS level2,
t_ep_ro_org.t_ep_ro_org_ep_id AS level_id2
FROM mdp_matrix, t_ep_item, t_ep_ro_org
WHERE mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
AND mdp_matrix.t_ep_ro_org_ep_id = t_ep_ro_org.t_ep_ro_org_ep_id
AND '
|| p_additional_filter
|| ') t_comb_list';
apps.xxascp_util_pkg.dbms_output_lines ('g_sql=' || g_sql);
EXECUTE IMMEDIATE g_sql;
END;
/*---------------------------------------------------------
Created by
PPROCEDURE:
Create init date table
LOGICALITY:
<_____Optional_____Internal_Logic_Description>
INPUT:
p_table_name table name
OUTPUT:
null
---------------------------------------------------------*/
/* Formatted on 2009/03/11 13:04 (Formatter Plus v4.8.7) */
PROCEDURE xxdm_create_int_date_table (p_table_name IN VARCHAR2)
IS
v_tablename VARCHAR2 (100);
v_tempname VARCHAR2 (100);
v_count NUMBER;
BEGIN
v_tablename := 'demantra.' || p_table_name;
SELECT COUNT (*)
INTO v_count
FROM user_tables
WHERE table_name = p_table_name;
g_err_position := 'E0029';
g_err_desc := 'table ' || p_table_name || ' not exists.';
IF v_count > 0
THEN
g_err_position := 'E0030';
g_err_desc :=
'error occurs when deleteing data,table name=' || v_tablename;
g_sql := 'DELETE FROM ' || v_tablename;
apps.xxascp_util_pkg.dbms_output_lines ('g_sql=' || g_sql);
EXECUTE IMMEDIATE g_sql;
COMMIT;
ELSE
g_err_position := 'E0031';
g_err_desc := 'error occurs when creating table ' || v_tablename;
g_sql :=
'CREATE TABLE '
|| v_tablename
|| '
(
FROM_SALES_DATE DATE NOT NULL,
TO_SALES_DATE DATE,
MADAD NUMBER
)';
apps.xxascp_util_pkg.dbms_output_lines ('g_sql=' || g_sql);
EXECUTE IMMEDIATE g_sql;
END IF;
--end: Create table int_date_
--begin: Create/Recreate indexes
v_tempname := p_table_name || '_X';
g_err_position := 'E0032';
g_err_desc :=
'index data not exists in table [user_indexes],column [index_name]='
|| v_tempname;
SELECT COUNT (*)
INTO v_count
FROM user_indexes
WHERE index_name = v_tempname;
apps.xxascp_util_pkg.dbms_output_lines ('v_count=' || v_count);
v_tempname:='demantra.'||v_tempname;
IF NOT v_count > 0
THEN
g_err_position := 'E0033';
g_err_desc :=
'error occurs when creating index ['
|| v_tempname
|| '] on table ['
|| v_tablename
|| ']';
g_sql :=
'CREATE UNIQUE INDEX '
|| v_tempname
|| ' on '
|| v_tablename
|| ' (FROM_SALES_DATE,TO_SALES_DATE)';
apps.xxascp_util_pkg.dbms_output_lines ('g_sql=' || g_sql);
EXECUTE IMMEDIATE g_sql;
END IF;
END;
/*---------------------------------------------------------
Created by
PPROCEDURE:
insert data to int date table
LOGICALITY:
<_____Optional_____Internal_Logic_Description>
INPUT:
p_ppp ppp value
p_table_name table name
OUTPUT:
null
---------------------------------------------------------*/
PROCEDURE xxdm_insert_int_date_table (p_ppp IN VARCHAR2,
p_table_name IN VARCHAR2)
IS
v_tablename VARCHAR2 (100);
v_count NUMBER;
v_month_first_day DATE;
v_month_last_day DATE;
v_sysdate DATE;
exp_common_raise exception;
BEGIN
v_tablename := 'demantra.' || p_table_name;
IF p_ppp = 'IRP'
THEN
v_count := C_MONTHS_IRP;
ELSIF p_ppp = 'LTB'
THEN
v_count := C_MONTHS_LTB;
ELSIF p_ppp = 'ROL'
THEN
v_count := C_MONTHS_ROLLING;
ELSE
v_count :=0; --occurs error
g_err_position:='E0034';
g_err_desc:='ppp not in(LTB,ROL,IRP)v_ppp='||p_ppp;
raise exp_common_raise;
END IF;
g_err_position:='E0035';
g_err_desc:='E0035';
--v_sysdate :=sysdate;
--v_sysdate :=sysdate; crp2 remark
select to_date(pval,'mm-dd-yyyy hh24:mi:ss') into v_sysdate from demantra.SYS_PARAMS
where pname='max_sales_date';
FOR i IN 0 .. v_count - 1
LOOP
/*IF p_ppp = 'IRP' OR p_ppp = 'LTB'
THEN
v_month_first_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i),'mm'); -- first day of month
v_month_last_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i),'mm'); -- last day of month
ELSIF p_ppp = 'MP'
THEN
v_month_first_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i+1),'mm'); -- first day of month
v_month_last_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i+1),'mm'); -- last day of month
END IF;*/
v_month_first_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i+1),'mm'); -- first day of month
v_month_last_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i+1),'mm'); -- last day of month
g_err_position:='E0036';
g_err_desc:='E0036';
g_sql :=
'INSERT INTO '
|| v_tablename
|| ' (FROM_SALES_DATE,TO_SALES_DATE,MADAD)
VALUES(to_date('''
|| TO_CHAR (v_month_first_day, 'yyyy-MM-dd')
|| ''',''yyyy-MM-dd HH24:mi:ss''),
to_date('''
|| TO_CHAR (v_month_last_day, 'yyyy-MM-dd')
|| ''',''yyyy-MM-dd HH24:mi:ss''),1)';
apps.xxascp_util_pkg.dbms_output_lines('g_sql=' || g_sql);
g_err_position:='E0037';
g_err_desc:='error occurs when inserting data to table ['||v_tablename||']';
EXECUTE IMMEDIATE g_sql;
END LOOP;
IF v_count > 0
THEN
g_err_position:='E0038';
g_err_desc:='error occurs when commit data';
COMMIT;
END IF;
END;
/*---------------------------------------------------------
Created by
PPROCEDURE:
Create export view
LOGICALITY:
<_____Optional_____Internal_Logic_Description>
INPUT:
p_notice_id notice id
p_ro ro of notiec id
p_ppp ppp of notice id
p_int_comb_viewname int comb view name
p_int_date_tablename int date table name
p_additional_filter additional fiter
p_max_salles_date max sales date
OUTPUT:
null
---------------------------------------------------------*/
PROCEDURE xxdm_create_export_view (p_notice_id IN VARCHAR2,
p_ro IN VARCHAR2,
p_ppp_new IN VARCHAR2,
p_int_comb_viewname IN VARCHAR2,
p_int_date_tablename IN VARCHAR2,
p_additional_filter IN VARCHAR2,
p_max_salles_date IN DATE)
IS
v_view_name VARCHAR2(100);
v_int_comb_viewname VARCHAR2(100);
v_int_date_tablename VARCHAR2(100);
v_replace_string_1 VARCHAR2 (2000);
v_replace_string_2 VARCHAR2 (2000);
v_replace_string_3 VARCHAR2 (2000);
BEGIN
v_int_comb_viewname:='demantra.'||p_int_comb_viewname;
v_int_date_tablename:='demantra.'||p_int_date_tablename;
v_view_name:='demantra.BIEO_'||p_notice_id;
g_err_position:='E0038-01';
g_err_desc:='E0038-01';
/*IF p_ppp_new='LB1' THEN
v_replace_string_1:='SUM(branch_data.FCST_LTB_1Y_AGG) AS c_pred';
ELSIF p_ppp_new='LB3' THEN
v_replace_string_1:='SUM(branch_data.FCST_LTB_3Y_AGG) AS c_pred';
ELSE */
g_err_position:='E0038-02';
g_err_desc:='E0038-02';
SELECT replace(replace(exp_template, '#FORE@0#', 'REPLACE_STRING_2'), '#UNIT#', '1') || 'AS c_pred ' INTO v_replace_string_1
FROM computed_fields WHERE forecast_type_id=1;
g_err_position:='E0038-02';
g_err_desc:='E0038-02';
apps.xxascp_util_pkg.dbms_output_lines('v_replace_string_1=' || v_replace_string_1);
SELECT 'branch_data.'||FORE_COLUMN_NAME INTO v_replace_string_2 FROM forecast_history WHERE TIME_SIG = (SELECT MAX(TIME_SIG) FROM forecast_history);
apps.xxascp_util_pkg.dbms_output_lines('v_replace_string_2=' || v_replace_string_2);
v_replace_string_1:=REPLACE(v_replace_string_1,'REPLACE_STRING_2',v_replace_string_2);
-- END IF;
g_err_position:='E0038-03';
g_err_desc:='E0038-03';
IF p_ppp_new='LB1' THEN
v_replace_string_3
:=' AND branch_data.sales_date >= to_date('''||g_eol_date_c||''','''||g_eol_date_format||''')'||
' AND branch_data.sales_date <= add_months(to_date('''||g_eol_date_c||''','''||g_eol_date_format||''')'||',11)';
ELSIF p_ppp_new='LB3' THEN
v_replace_string_3
:=' AND branch_data.sales_date >= add_months(to_date('''||g_eol_date_c||''','''||g_eol_date_format||''')'||',12)' ||
' AND branch_data.sales_date <= add_months(to_date('''||g_eol_date_c||''','''||g_eol_date_format||''')'||',35)';
ELSE
v_replace_string_3
:='';
END IF;
apps.xxascp_util_pkg.dbms_output_lines('v_replace_string_3=' || v_replace_string_3);
-- apps.xxascp_util_pkg.dbms_output_lines('v_replace_string_1=' || v_replace_string_1);
g_sql := 'CREATE OR REPLACE VIEW '
|| v_view_name
||' AS
SELECT sdate,
level1,
level2,
c_pred,
prty_demand,
dkey_item,
dkey_org,
record_type
FROM (SELECT MIN(t_dates_list.from_sales_date) AS sdate,
(CASE
WHEN (MIN(t_dates_list.from_sales_date) - to_date('''
|| TO_CHAR (p_max_salles_date, 'yyyy-MM-dd HH24:mi:ss')
|| ''',''yyyy-MM-dd HH24:mi:ss'') <= 0) THEN
(CASE
WHEN (MAX(t_dates_list.from_sales_date) - to_date('''
|| TO_CHAR (p_max_salles_date, 'yyyy-MM-dd HH24:mi:ss')
|| ''',''yyyy-MM-dd HH24:mi:ss'') <= 0) THEN
1
ELSE
3
END) ELSE 2 END) AS record_type,
MAX(comb_id) AS comb_id,
t_comb_list.level_id1,
t_comb_list.level_id2,
REPLACE_STRING_1,
MIN(branch_data.demand_priority) AS prty_demand,
MIN(t_ep_item.ebs_item_dest_key) AS dkey_item,
MIN(t_ep_ro_org.org_dest_key) AS dkey_org,
(CASE
WHEN MAX(branch_data.note_id) > 0 THEN
1
ELSE
0
END) AS note_id,
(CASE
WHEN MAX(branch_data.is_promotion) > 0 THEN
1
ELSE
0
END) AS is_promotion,
(CASE
WHEN MAX(branch_data.is_supply_plan) > 0 THEN
1
ELSE
0
END) AS is_supply_plan,
(CASE
WHEN MAX(branch_data.is_scenario_resource) > 0 THEN
1
ELSE
0
END) AS is_scenario_resource,
1 AS is_base_0
FROM sales_data branch_data,
mdp_matrix,
t_ep_item,
t_ep_ro_org,
'|| v_int_comb_viewname||' t_comb_list,
'||v_int_date_tablename||' t_dates_list
WHERE branch_data.sales_date >= t_dates_list.from_sales_date
AND branch_data.sales_date <= t_dates_list.to_sales_date' ||
v_replace_string_3||
' AND branch_data.item_id = mdp_matrix.item_id
AND branch_data.location_id = mdp_matrix.location_id
AND mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
AND mdp_matrix.t_ep_ro_org_ep_id = t_ep_ro_org.t_ep_ro_org_ep_id
AND '||p_additional_filter||'
AND t_comb_list.level_id1 = mdp_matrix.t_ep_item_ep_id
AND t_comb_list.level_id2 = mdp_matrix.t_ep_ro_org_ep_id
GROUP BY t_comb_list.level_id1,
t_comb_list.level_id2,
t_dates_list.from_sales_date) int_table,
'||v_int_comb_viewname||'
WHERE '||v_int_comb_viewname||'.level_id1 = int_table.level_id1
AND '||v_int_comb_viewname||'.level_id2 = int_table.level_id2
';
g_sql:=REPLACE(g_sql,'REPLACE_STRING_1',v_replace_string_1);
apps.xxascp_util_pkg.dbms_output_lines('g_sql=' || g_sql);
g_err_position:='E0039';
g_err_desc:='error occurs when creating export view';
EXECUTE IMMEDIATE g_sql;
END;
/*---------------------------------------------------------
Created by
FUNCTION :
Check LTB-B exists
LOGICALITY:
<_____Optional_____Internal_Logic_Description>
INPUT:
p_additional_filter additional_filter
OUTPUT:
True or False
---------------------------------------------------------*/
FUNCTION xxdm_check_ltb_b_exist(p_additional_filter VARCHAR2)
RETURN BOOLEAN IS
RESULT BOOLEAN;
v_count NUMBER;
BEGIN
g_sql :='SELECT COUNT (*)
FROM mdp_matrix, t_ep_item, t_ep_ro_org, t_ep_i_att_6
WHERE mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
AND mdp_matrix.t_ep_ro_org_ep_id = t_ep_ro_org.t_ep_ro_org_ep_id
AND mdp_matrix.t_ep_i_att_6_ep_id = t_ep_i_att_6.t_ep_i_att_6_ep_id
AND t_ep_i_att_6.i_att_6 = ''EOL-B''
AND '|| p_additional_filter;
apps.xxascp_util_pkg.dbms_output_lines('g_sql=' || g_sql);
EXECUTE IMMEDIATE g_sql INTO v_count;
IF v_count>0 THEN
RESULT := TRUE;
ELSE
RESULT := FALSE;
END IF;
RETURN RESULT;
END;
END;
/
1730
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
