1 PACKAGE MSD_DEM_UPLOAD_FORECAST AS
2 /* $Header: msddemufs.pls 120.0.12000000.2 2007/09/24 11:42:11 nallkuma noship $ */
3
4 /*** CONSTANTS ***/
5
6 C_IMPORT_DATA_PROFILE NUMBER := 1;
7 C_EXPORT_DATA_PROFILE NUMBER := 2;
8 C_IMPORT_EXPORT_DATA_PROFILE NUMBER := 3;
9
10 C_DEMAND_PLAN_ID NUMBER := 5555555;
11 C_SCENARIO_ID_OFFSET NUMBER := 5555555;
12
13 C_BUCKET_TYPE_DAY NUMBER := 1;
14 C_BUCKET_TYPE_WEEK NUMBER := 2;
15 C_BUCKET_TYPE_MONTH NUMBER := 3;
16
17 C_PSNT_TYPE_CODE NUMBER := 1;
18 C_PSNT_TYPE_DESC NUMBER := 0;
19
20 C_EXPORT_TYPE_FULL NUMBER := 1;
21 C_EXPORT_TYPE_INCR NUMBER := 2;
22
23 C_TIME_LEVEL_DAY NUMBER := 7;
24 C_TIME_LEVEL_WEEK NUMBER := 6;
25 C_TIME_LEVEL_MONTH NUMBER := 5;
26
27
28 C_ITEM VARCHAR2(50) := 'ITEM';
29 C_PRODUCT_FAMILY VARCHAR2(50) := 'PRODUCT FAMILY';
30 C_ORGANIZATION VARCHAR2(50) := 'ORGANIZATION';
31 C_SITE VARCHAR2(50) := 'SITE';
32 C_CUSTOMER VARCHAR2(50) := 'ACCOUNT';
33 C_CUSTOMER_ZONE VARCHAR2(50) := 'TRADING PARTER ZONE';
34 C_ZONE VARCHAR2(50) := 'ZONE';
35 C_DEMAND_CLASS VARCHAR2(50) := 'DEMAND CLASS';
36
37 C_ROUNDOFF_PLACES NUMBER := 6;
38
39 C_FORECAST_SERIES_PREFIX VARCHAR2(10) := 'FCST_';
40 C_FCST_ACRY_SERIES_PREFIX VARCHAR2(10) := 'ACRY_';
41 C_DEMAND_PRTY_SERIES_PREFIX VARCHAR2(10) := 'PRTY_';
42 C_DKEY_SERIES_PREFIX VARCHAR2(10) := 'DKEY_';
43
44 C_DKEY_ITEM VARCHAR2(30) := 'ITEM';
45 C_DKEY_SITE VARCHAR2(30) := 'SITE';
46 C_DKEY_ORG VARCHAR2(30) := 'ORG';
47
48
49 /*** FUNCTIONS ***
50 * GET_SR_INSTANCE_ID_FOR_GLOBAL
51 * IS_VALID_SCENARIO
52 * GET_SR_INSTANCE_ID_FOR_PROFILE
53 * GET_ERROR_TYPE
54 * IS_GLOBAL_SCENARIO
55 * GET_CUSTOMER_FROM_TPZONE
56 * GET_ZONE_FROM_TPZONE
57 */
58
59 /*
60 * This function returns the sr_instance_id to be used for a global forecast
61 */
62 FUNCTION GET_SR_INSTANCE_ID_FOR_GLOBAL
63 RETURN NUMBER;
64
65 /* This function returns 1 if the data profile is fit for upload to ASCP
66 * Current check only includes that a forecast series with internal name
67 * starting 'FCST_' must be present.
68 */
69 FUNCTION IS_VALID_SCENARIO (
70 p_data_profile_id IN NUMBER)
71 RETURN NUMBER;
72
73 /* This function returns -23453 if the data profile contains non-global
74 * forecast, else it returns the id of the source instance for which
75 * global forecasting is being done.
76 */
77 FUNCTION GET_SR_INSTANCE_ID_FOR_PROFILE (
78 p_data_profile_id IN NUMBER)
79 RETURN NUMBER;
80
81 /* This function gets the error type 'MAD' or 'MAPE' given the data
82 * profile id
83 */
84 FUNCTION GET_ERROR_TYPE (
85 p_data_profile_id IN NUMBER)
86 RETURN VARCHAR2;
87
88 /* This function return 'Y' if the data profile contains global forecast
89 * else returns 'N'.
90 */
91 FUNCTION IS_GLOBAL_SCENARIO (
92 p_data_profile_id IN NUMBER)
93 RETURN VARCHAR2;
94
95 /* This function returns the source key of the customer, given the customer
96 * zone
97 */
98 FUNCTION GET_CUSTOMER_FROM_TPZONE (
99 p_tp_zone IN VARCHAR2,
100 p_sr_instance_id IN NUMBER)
101 RETURN NUMBER;
102
103 /* This function returns the source key of the zone, given the customer zone
104 */
105 FUNCTION GET_ZONE_FROM_TPZONE (
106 p_tp_zone IN VARCHAR2,
107 p_sr_instance_id IN NUMBER)
108 RETURN NUMBER;
109
110 /*** PROCEDURES ***/
111
112 /*
113 * This procedure, given the export integration interface name, pushes the
114 * forecast data along with forecast accuracy and demand priority from the
115 * export view to table MSD_DP_SCN_ENTRIES_DENORM. The member codes are
116 * transformed to the corresponding source identifiers. The 'Organization'
117 * level member is used to find out the source instance to which the record
118 * belongs.
119 * The internal names of the series will be used to get the semantic of the
120 * series. They are as follows -
121 * 1. Forecast Series - The internal name should start with 'FCST_'
122 * 2. Demand Priority Series - The internal name should start with 'PRTY_'
123 * 3. Forecast Accuracy Series - The internal name should start with 'ACRY_'
124 * 4. Destination Key Series - The internal name should start with 'DKEY_'
125 */
126 PROCEDURE UPLOAD_FORECAST (
127 p_export_data_profile IN VARCHAR2);
128
129
130
131 PROCEDURE GET_TIME_STRINGS (
132 p_bucket_type OUT NOCOPY NUMBER,
133 p_start_time OUT NOCOPY VARCHAR2,
134 p_end_time OUT NOCOPY VARCHAR2,
135 p_res_type OUT NOCOPY NUMBER,
136 p_time_from_clause OUT NOCOPY VARCHAR2,
137 p_time_res IN NUMBER);
138
139 END MSD_DEM_UPLOAD_FORECAST;
[Home] [Help]